[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_last_update_date DATE,
p_last_updated_by NUMBER,
p_creation_date DATE,
p_created_by NUMBER,
p_last_update_login NUMBER
);
SELECT 1 into dummy from dual
WHERE NOT EXISTS
(SELECT 1 from bom_reference_designators
WHERE component_sequence_id = X_component_sequence_id
AND component_reference_designator = X_designator
AND ((acd_type is null) OR (acd_type <> 3))
AND ((X_rowid is NULL) OR (rowid <> X_rowid))
);
SELECT Count(1) INTO rec_exist
FROM Bom_Inventory_Components bic,
bom_reference_designators brd
WHERE Nvl(bic.Old_Component_Sequence_Id,bic.Component_Sequence_Id) = X_Old_Component_Sequence_Id
AND Nvl(bic.Change_Notice,'*') <> X_Change_Notice
AND brd.component_sequence_id = bic.component_sequence_id
AND brd.component_reference_designator = X_Designator
AND ((brd.acd_type is NULL) or (brd.acd_type <> 3));
SELECT Acd_Type INTO rec_exist
FROM Bom_Reference_Designators
WHERE Component_Sequence_Id = ( SELECT Max(bic.Component_Sequence_Id)
FROM Bom_Inventory_Components bic,
bom_reference_designators brd
WHERE bic.Old_Component_Sequence_Id = X_Old_Component_Sequence_Id
AND bic.Change_Notice <> X_Change_Notice
AND bic.Implementation_Date IS NULL
AND brd.component_sequence_id = bic.component_sequence_id
AND brd.component_reference_designator = X_Designator )
AND Component_Reference_Designator = X_Designator
AND Rownum < 2
ORDER BY Acd_Type;
SELECT 0 INTO disable_exist
FROM dual
WHERE NOT EXISTS
(SELECT 1 FROM bom_reference_designators
WHERE component_sequence_id = X_Component_Sequence_Id
AND component_reference_designator = X_Designator
AND acd_type = 3);
select count(*)
into X_Total_Records
from bom_ref_designators_view
where component_sequence_id = X_Component_Sequence_Id
and nvl(acd_type,1) <> 3;
PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
X_Component_Ref_Desig VARCHAR2,
X_Last_Update_Date DATE,
X_Last_Updated_By NUMBER,
X_Creation_Date DATE,
X_Created_By NUMBER,
X_Last_Update_Login NUMBER,
X_Ref_Designator_Comment VARCHAR2,
X_Change_Notice VARCHAR2,
X_Component_Sequence_Id NUMBER,
X_Acd_Type NUMBER,
X_Attribute_Category VARCHAR2,
X_Attribute1 VARCHAR2,
X_Attribute2 VARCHAR2,
X_Attribute3 VARCHAR2,
X_Attribute4 VARCHAR2,
X_Attribute5 VARCHAR2,
X_Attribute6 VARCHAR2,
X_Attribute7 VARCHAR2,
X_Attribute8 VARCHAR2,
X_Attribute9 VARCHAR2,
X_Attribute10 VARCHAR2,
X_Attribute11 VARCHAR2,
X_Attribute12 VARCHAR2,
X_Attribute13 VARCHAR2,
X_Attribute14 VARCHAR2,
X_Attribute15 VARCHAR2
) IS
CURSOR C IS SELECT rowid FROM BOM_REFERENCE_DESIGNATORS
WHERE component_sequence_id = X_Component_Sequence_Id
AND ( (acd_type = X_Acd_Type)
or (acd_type is NULL and X_Acd_Type is NULL));
INSERT INTO BOM_REFERENCE_DESIGNATORS(
component_reference_designator,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
ref_designator_comment,
change_notice,
component_sequence_id,
acd_type,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
) VALUES (
X_Component_Ref_Desig,
X_Last_Update_Date,
X_Last_Updated_By,
X_Creation_Date,
X_Created_By,
X_Last_Update_Login,
X_Ref_Designator_Comment,
X_Change_Notice,
X_Component_Sequence_Id,
X_Acd_Type,
X_Attribute_Category,
X_Attribute1,
X_Attribute2,
X_Attribute3,
X_Attribute4,
X_Attribute5,
X_Attribute6,
X_Attribute7,
X_Attribute8,
X_Attribute9,
X_Attribute10,
X_Attribute11,
X_Attribute12,
X_Attribute13,
X_Attribute14,
X_Attribute15
);
BOMPCMBM.Insert_Related_Ref_Desg(p_component_sequence_id => X_Component_Sequence_Id
, p_ref_desg => X_Component_Ref_Desig
, x_Mesg_Token_Tbl => l_err_tbl
, x_Return_Status => l_return_status);
Raise_Business_Event(X_Component_Sequence_Id,X_Last_Update_Date,X_Last_Updated_By,
X_Creation_Date,X_Created_By,X_Last_Update_Login);
END Insert_Row;
SELECT *
FROM BOM_REFERENCE_DESIGNATORS
WHERE rowid = X_Rowid
FOR UPDATE of Component_Sequence_Id NOWAIT;
FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
PROCEDURE Update_Row(X_Rowid VARCHAR2,
X_Component_Ref_Desig VARCHAR2,
X_Last_Update_Date DATE,
X_Last_Updated_By NUMBER,
X_Last_Update_Login NUMBER,
X_Ref_Designator_Comment VARCHAR2,
X_Change_Notice VARCHAR2,
X_Component_Sequence_Id NUMBER,
X_Acd_Type NUMBER,
X_Attribute_Category VARCHAR2,
X_Attribute1 VARCHAR2,
X_Attribute2 VARCHAR2,
X_Attribute3 VARCHAR2,
X_Attribute4 VARCHAR2,
X_Attribute5 VARCHAR2,
X_Attribute6 VARCHAR2,
X_Attribute7 VARCHAR2,
X_Attribute8 VARCHAR2,
X_Attribute9 VARCHAR2,
X_Attribute10 VARCHAR2,
X_Attribute11 VARCHAR2,
X_Attribute12 VARCHAR2,
X_Attribute13 VARCHAR2,
X_Attribute14 VARCHAR2,
X_Attribute15 VARCHAR2
) IS
l_err_tbl Error_Handler.Mesg_Token_Tbl_Type;
SELECT COMPONENT_REFERENCE_DESIGNATOR, ACD_TYPE
INTO l_old_ref_desg, l_acd_type
FROM BOM_REFERENCE_DESIGNATORS
WHERE rowid = X_Rowid;
UPDATE BOM_REFERENCE_DESIGNATORS
SET
component_reference_designator = X_Component_Ref_Desig,
last_update_date = X_Last_Update_Date,
last_updated_by = X_Last_Updated_By,
last_update_login = X_Last_Update_Login,
ref_designator_comment = X_Ref_Designator_Comment,
change_notice = X_Change_Notice,
component_sequence_id = X_Component_Sequence_Id,
acd_type = X_Acd_Type,
attribute_category = X_Attribute_Category,
attribute1 = X_Attribute1,
attribute2 = X_Attribute2,
attribute3 = X_Attribute3,
attribute4 = X_Attribute4,
attribute5 = X_Attribute5,
attribute6 = X_Attribute6,
attribute7 = X_Attribute7,
attribute8 = X_Attribute8,
attribute9 = X_Attribute9,
attribute10 = X_Attribute10,
attribute11 = X_Attribute11,
attribute12 = X_Attribute12,
attribute13 = X_Attribute13,
attribute14 = X_Attribute14,
attribute15 = X_Attribute15
WHERE rowid = X_Rowid;
BOMPCMBM.Update_Related_Ref_Desg(p_component_sequence_id => X_Component_Sequence_Id
, p_old_ref_desg => l_old_ref_desg
, p_new_ref_desg => X_Component_Ref_Desig
, p_acd_type => l_acd_type
, x_Mesg_Token_Tbl => l_err_tbl
, x_Return_Status => l_return_status);
Raise_Business_Event(X_Component_Sequence_Id,X_Last_Update_Date,X_Last_Updated_By,
NULL,NULL,X_Last_Update_Login);
END Update_Row;
PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
l_common_component_sequence_id NUMBER;
Select component_sequence_id, component_reference_designator
into l_common_component_sequence_id, l_ref_desg
From BOM_REFERENCE_DESIGNATORS
WHERE rowid = X_Rowid;
DELETE FROM BOM_REFERENCE_DESIGNATORS
WHERE rowid = X_Rowid;
BOMPCMBM.Delete_Related_Ref_Desg(p_src_comp_seq => l_common_component_sequence_id
, p_ref_desg => l_ref_desg
, x_return_status => l_return_status);
END Delete_Row;
p_last_update_date DATE,
p_last_updated_by NUMBER,
p_creation_date DATE,
p_created_by NUMBER,
p_last_update_login NUMBER
) IS --4306013
l_Component_Item_Name VARCHAR2(512);
SELECT bic.Bill_Sequence_Id, bbm.Organization_Id, bic.Component_Item_Id,
bic.Component_Remarks, msi.Concatenated_Segments
INTO l_Bill_Sequence_Id, l_Organization_Id, l_Component_Item_Id,
l_Component_Remarks, l_Component_Item_Name
FROM Bom_Bill_Of_Materials bbm, Bom_Inventory_Components bic, Mtl_System_Items_Kfv msi
WHERE bbm.Bill_Sequence_Id = bic.Bill_Sequence_Id
And msi.Inventory_Item_Id = bic.Component_Item_Id
And msi.Organization_Id = bbm.Organization_Id
And bic.Component_Sequence_Id = p_Component_Sequence_Id;
, p_last_update_date => p_last_update_date
, p_last_updated_by => p_last_updated_by
, p_creation_date => p_creation_date
, p_created_by => p_created_by
, p_last_update_login => p_last_update_login
);