[Home] [Help]
16: dummy NUMBER;
17: BEGIN
18: SELECT 1 into dummy from dual
19: WHERE NOT EXISTS
20: (SELECT 1 from bom_reference_designators
21: WHERE component_sequence_id = X_component_sequence_id
22: AND component_reference_designator = X_designator
23: AND ((acd_type is null) OR (acd_type <> 3))
24: AND ((X_rowid is NULL) OR (rowid <> X_rowid))
43: --* or unimplemented status. If the reference designator is being added
44: --* for the first time, furthur validations will be ignored.
45: SELECT Count(1) INTO rec_exist
46: FROM Bom_Inventory_Components bic,
47: bom_reference_designators brd
48: WHERE --Nvl(bic.Old_Component_Sequence_Id,bic.Component_Sequence_Id) = X_Old_Component_Sequence_Id
49: --commented out previous line and added line below for bug 8719529
50: --Removed nvl function to improve performance. Both lines have same logic
51: ((bic.Old_Component_Sequence_Id = X_Old_Component_Sequence_Id) OR
75: --* to fetch only ADD record's acd type (1). So added rownum condition and
76: --* included order by clause.
77:
78: SELECT Acd_Type INTO rec_exist
79: FROM Bom_Reference_Designators
80: WHERE Component_Sequence_Id = ( SELECT Max(bic.Component_Sequence_Id)
81: FROM Bom_Inventory_Components bic,
82: bom_reference_designators brd
83: WHERE bic.Old_Component_Sequence_Id = X_Old_Component_Sequence_Id
78: SELECT Acd_Type INTO rec_exist
79: FROM Bom_Reference_Designators
80: WHERE Component_Sequence_Id = ( SELECT Max(bic.Component_Sequence_Id)
81: FROM Bom_Inventory_Components bic,
82: bom_reference_designators brd
83: WHERE bic.Old_Component_Sequence_Id = X_Old_Component_Sequence_Id
84: AND bic.Change_Notice <> X_Change_Notice
85: AND bic.Implementation_Date IS NULL
86: AND brd.component_sequence_id = bic.component_sequence_id
98: --* DISABLE record exists in the current block
99: SELECT 0 INTO disable_exist
100: FROM dual
101: WHERE NOT EXISTS
102: (SELECT 1 FROM bom_reference_designators
103: WHERE component_sequence_id = X_Component_Sequence_Id
104: AND component_reference_designator = X_Designator
105: AND acd_type = 3);
106:
161: X_Attribute13 VARCHAR2,
162: X_Attribute14 VARCHAR2,
163: X_Attribute15 VARCHAR2
164: ) IS
165: CURSOR C IS SELECT rowid FROM BOM_REFERENCE_DESIGNATORS
166: WHERE component_sequence_id = X_Component_Sequence_Id
167: AND ( (acd_type = X_Acd_Type)
168: or (acd_type is NULL and X_Acd_Type is NULL));
169: l_err_tbl Error_Handler.Mesg_Token_Tbl_Type;
171:
172: BEGIN
173:
174:
175: INSERT INTO BOM_REFERENCE_DESIGNATORS(
176: component_reference_designator,
177: last_update_date,
178: last_updated_by,
179: creation_date,
276: X_Attribute15 VARCHAR2
277: ) IS
278: CURSOR C IS
279: SELECT *
280: FROM BOM_REFERENCE_DESIGNATORS
281: WHERE rowid = X_Rowid
282: FOR UPDATE of Component_Sequence_Id NOWAIT;
283: Recinfo C%ROWTYPE;
284:
396: BEGIN
397:
398: SELECT COMPONENT_REFERENCE_DESIGNATOR, ACD_TYPE
399: INTO l_old_ref_desg, l_acd_type
400: FROM BOM_REFERENCE_DESIGNATORS
401: WHERE rowid = X_Rowid;
402:
403: UPDATE BOM_REFERENCE_DESIGNATORS
404: SET
399: INTO l_old_ref_desg, l_acd_type
400: FROM BOM_REFERENCE_DESIGNATORS
401: WHERE rowid = X_Rowid;
402:
403: UPDATE BOM_REFERENCE_DESIGNATORS
404: SET
405: component_reference_designator = X_Component_Ref_Desig,
406: last_update_date = X_Last_Update_Date,
407: last_updated_by = X_Last_Updated_By,
457: BEGIN
458:
459: Select component_sequence_id, component_reference_designator
460: into l_common_component_sequence_id, l_ref_desg
461: From BOM_REFERENCE_DESIGNATORS
462: WHERE rowid = X_Rowid;
463:
464:
465: DELETE FROM BOM_REFERENCE_DESIGNATORS
461: From BOM_REFERENCE_DESIGNATORS
462: WHERE rowid = X_Rowid;
463:
464:
465: DELETE FROM BOM_REFERENCE_DESIGNATORS
466: WHERE rowid = X_Rowid;
467:
468: if (SQL%NOTFOUND) then
469: Raise NO_DATA_FOUND;