[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
DESCRIPTION
,PURCHASING_CATEGORY_ID
,DIRECT_ITEM_SEQUENCE_ID
,OPERATION_SEQ_NUM
,DEPARTMENT_ID
,WIP_ENTITY_ID
,ORGANIZATION_ID
,SUGGESTED_VENDOR_NAME
,SUGGESTED_VENDOR_ID
,SUGGESTED_VENDOR_SITE
,SUGGESTED_VENDOR_SITE_ID
,SUGGESTED_VENDOR_CONTACT
,SUGGESTED_VENDOR_CONTACT_ID
,SUGGESTED_VENDOR_PHONE
,SUGGESTED_VENDOR_ITEM_NUM
,UNIT_PRICE
,AUTO_REQUEST_MATERIAL
,REQUIRED_QUANTITY
,UOM
,NEED_BY_DATE
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
INTO
l_eam_direct_items_rec.DESCRIPTION
,l_eam_direct_items_rec.PURCHASING_CATEGORY_ID
,l_eam_direct_items_rec.Direct_Item_Sequence_Id
,l_eam_direct_items_rec.Operation_Seq_Num
,l_eam_direct_items_rec.Department_id
,l_eam_direct_items_rec.Wip_entity_id
,l_eam_direct_items_rec.Organization_id
,l_eam_direct_items_rec.Suggested_Vendor_Name
,l_eam_direct_items_rec.Suggested_Vendor_Id
,l_eam_direct_items_rec.Suggested_Vendor_Site
,l_eam_direct_items_rec.Suggested_Vendor_Site_Id
,l_eam_direct_items_rec.Suggested_Vendor_Contact
,l_eam_direct_items_rec.Suggested_Vendor_Contact_Id
,l_eam_direct_items_rec.Suggested_Vendor_Phone
,l_eam_direct_items_rec.Suggested_Vendor_Item_Num
,l_eam_direct_items_rec.Unit_Price
,l_eam_direct_items_rec.Auto_request_Material
,l_eam_direct_items_rec.Required_Quantity
,l_eam_direct_items_rec.UOM
,l_eam_direct_items_rec.Need_By_Date
,l_eam_direct_items_rec.ATTRIBUTE_CATEGORY
,l_eam_direct_items_rec.ATTRIBUTE1
,l_eam_direct_items_rec.ATTRIBUTE2
,l_eam_direct_items_rec.ATTRIBUTE3
,l_eam_direct_items_rec.ATTRIBUTE4
,l_eam_direct_items_rec.ATTRIBUTE5
,l_eam_direct_items_rec.ATTRIBUTE6
,l_eam_direct_items_rec.ATTRIBUTE7
,l_eam_direct_items_rec.ATTRIBUTE8
,l_eam_direct_items_rec.ATTRIBUTE9
,l_eam_direct_items_rec.ATTRIBUTE10
,l_eam_direct_items_rec.ATTRIBUTE11
,l_eam_direct_items_rec.ATTRIBUTE12
,l_eam_direct_items_rec.ATTRIBUTE13
,l_eam_direct_items_rec.ATTRIBUTE14
,l_eam_direct_items_rec.ATTRIBUTE15
FROM wip_eam_direct_items wedi
WHERE wedi.wip_entity_id = p_wip_entity_id
AND wedi.organization_id = p_organization_id
AND wedi.operation_seq_num = p_operation_seq_num
AND wedi.direct_item_sequence_id = p_direct_item_sequence_id;
* Procedure : Insert_Row
* Parameters IN : Direct Items column record
* Parameters OUT NOCOPY: Message Token Table
* Return Status
* Purpose : Procedure will perfrom an insert into the
* wip_eam_direct_items table.
*********************************************************************/
PROCEDURE Insert_Row
( p_eam_direct_items_rec IN EAM_PROCESS_WO_PUB.eam_direct_items_rec_type
, x_mesg_token_Tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type
, x_return_Status OUT NOCOPY VARCHAR2
)
IS
l_return_status VARCHAR2(30) := '';
INSERT INTO WIP_EAM_DIRECT_ITEMS
(
DESCRIPTION
,PURCHASING_CATEGORY_ID
,DIRECT_ITEM_SEQUENCE_ID
,OPERATION_SEQ_NUM
,DEPARTMENT_ID
,WIP_ENTITY_ID
,ORGANIZATION_ID
,SUGGESTED_VENDOR_NAME
,SUGGESTED_VENDOR_ID
,SUGGESTED_VENDOR_SITE
,SUGGESTED_VENDOR_SITE_ID
,SUGGESTED_VENDOR_CONTACT
,SUGGESTED_VENDOR_CONTACT_ID
,SUGGESTED_VENDOR_PHONE
,SUGGESTED_VENDOR_ITEM_NUM
,UNIT_PRICE
,AUTO_REQUEST_MATERIAL
,REQUIRED_QUANTITY
,UOM
,NEED_BY_DATE
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, request_id
, program_application_id
, program_id
, program_update_date)
VALUES (
p_eam_direct_items_rec.DESCRIPTION
,p_eam_direct_items_rec.PURCHASING_CATEGORY_ID
,p_eam_direct_items_rec.Direct_Item_Sequence_Id
,p_eam_direct_items_rec.Operation_Seq_Num
,p_eam_direct_items_rec.Department_id
,p_eam_direct_items_rec.Wip_entity_id
,p_eam_direct_items_rec.Organization_id
,p_eam_direct_items_rec.Suggested_Vendor_Name
,p_eam_direct_items_rec.Suggested_Vendor_Id
,p_eam_direct_items_rec.Suggested_Vendor_Site
,p_eam_direct_items_rec.Suggested_Vendor_Site_Id
,p_eam_direct_items_rec.Suggested_Vendor_Contact
,p_eam_direct_items_rec.Suggested_Vendor_Contact_Id
,p_eam_direct_items_rec.Suggested_Vendor_Phone
,p_eam_direct_items_rec.Suggested_Vendor_Item_Num
,p_eam_direct_items_rec.Unit_Price
,p_eam_direct_items_rec.Auto_request_Material
,p_eam_direct_items_rec.Required_Quantity
,p_eam_direct_items_rec.UOM
,p_eam_direct_items_rec.Need_By_Date
,p_eam_direct_items_rec.ATTRIBUTE_CATEGORY
,p_eam_direct_items_rec.ATTRIBUTE1
,p_eam_direct_items_rec.ATTRIBUTE2
,p_eam_direct_items_rec.ATTRIBUTE3
,p_eam_direct_items_rec.ATTRIBUTE4
,p_eam_direct_items_rec.ATTRIBUTE5
,p_eam_direct_items_rec.ATTRIBUTE6
,p_eam_direct_items_rec.ATTRIBUTE7
,p_eam_direct_items_rec.ATTRIBUTE8
,p_eam_direct_items_rec.ATTRIBUTE9
,p_eam_direct_items_rec.ATTRIBUTE10
,p_eam_direct_items_rec.ATTRIBUTE11
,p_eam_direct_items_rec.ATTRIBUTE12
,p_eam_direct_items_rec.ATTRIBUTE13
,p_eam_direct_items_rec.ATTRIBUTE14
,p_eam_direct_items_rec.ATTRIBUTE15
, SYSDATE
, FND_GLOBAL.user_id
, SYSDATE
, FND_GLOBAL.user_id
, FND_GLOBAL.login_id
, p_eam_direct_items_rec.request_id
, p_eam_direct_items_rec.program_application_id
, p_eam_direct_items_rec.program_id
, SYSDATE);
, p_message_text => G_PKG_NAME ||' :Inserting Record ' || SQLERRM
, x_mesg_token_Tbl => x_mesg_token_tbl
);
END Insert_Row;
* Procedure : Update_Row
* Parameters IN : Direct Items column record
* Parameters OUT NOCOPY: Message Token Table
* Return Status
* Purpose : Procedure will perfrom an Update on the
* wip_eam_direct_items
*********************************************************************/
PROCEDURE Update_Row
( p_eam_direct_items_rec IN EAM_PROCESS_WO_PUB.eam_direct_items_rec_type
, x_mesg_token_Tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type
, x_return_Status OUT NOCOPY VARCHAR2
)
IS
l_return_status VARCHAR2(30) := '';
SELECT required_quantity
INTO l_req_qty
FROM WIP_EAM_DIRECT_ITEMS
WHERE organization_id = p_eam_direct_items_rec.organization_id
AND wip_entity_id = p_eam_direct_items_rec.wip_entity_id
AND ( operation_seq_num = p_eam_direct_items_rec.operation_seq_num
OR operation_seq_num = 1 ) /* Added the check operation_seq_num = 1 so that op seq num can be changed if it is 1 */
AND direct_item_sequence_id = p_eam_direct_items_rec.direct_item_sequence_id;
UPDATE WIP_EAM_DIRECT_ITEMS
SET
DESCRIPTION = p_eam_direct_items_rec.DESCRIPTION
,PURCHASING_CATEGORY_ID = p_eam_direct_items_rec.PURCHASING_CATEGORY_ID
,Direct_Item_Sequence_Id = p_eam_direct_items_rec.Direct_Item_Sequence_Id
,Operation_Seq_Num = p_eam_direct_items_rec.Operation_Seq_Num
,Department_id = p_eam_direct_items_rec.Department_id
,Wip_entity_id = p_eam_direct_items_rec.Wip_entity_id
,Organization_id = p_eam_direct_items_rec.Organization_id
,Suggested_Vendor_Name = p_eam_direct_items_rec.Suggested_Vendor_Name
,Suggested_Vendor_Id = p_eam_direct_items_rec.Suggested_Vendor_Id
,Suggested_Vendor_Site = p_eam_direct_items_rec.Suggested_Vendor_Site
,Suggested_Vendor_Site_Id = p_eam_direct_items_rec.Suggested_Vendor_Site_Id
,Suggested_Vendor_Contact = p_eam_direct_items_rec.Suggested_Vendor_Contact
,Suggested_Vendor_Contact_Id = p_eam_direct_items_rec.Suggested_Vendor_Contact_Id
,Suggested_Vendor_Phone = p_eam_direct_items_rec.Suggested_Vendor_Phone
,Suggested_Vendor_Item_Num = p_eam_direct_items_rec.Suggested_Vendor_Item_Num
,Unit_Price = p_eam_direct_items_rec.Unit_Price
,Auto_request_Material = p_eam_direct_items_rec.Auto_request_Material
,Required_Quantity = p_eam_direct_items_rec.Required_Quantity
,UOM = p_eam_direct_items_rec.UOM
,Need_By_Date = p_eam_direct_items_rec.Need_By_Date
, attribute_category = p_eam_direct_items_rec.attribute_category
, attribute1 = p_eam_direct_items_rec.attribute1
, attribute2 = p_eam_direct_items_rec.attribute2
, attribute3 = p_eam_direct_items_rec.attribute3
, attribute4 = p_eam_direct_items_rec.attribute4
, attribute5 = p_eam_direct_items_rec.attribute5
, attribute6 = p_eam_direct_items_rec.attribute6
, attribute7 = p_eam_direct_items_rec.attribute7
, attribute8 = p_eam_direct_items_rec.attribute8
, attribute9 = p_eam_direct_items_rec.attribute9
, attribute10 = p_eam_direct_items_rec.attribute10
, attribute11 = p_eam_direct_items_rec.attribute11
, attribute12 = p_eam_direct_items_rec.attribute12
, attribute13 = p_eam_direct_items_rec.attribute13
, attribute14 = p_eam_direct_items_rec.attribute14
, attribute15 = p_eam_direct_items_rec.attribute15
, last_update_date = SYSDATE
, last_updated_by = FND_GLOBAL.user_id
, last_update_login = FND_GLOBAL.login_id
, request_id = p_eam_direct_items_rec.request_id
, program_application_id = p_eam_direct_items_rec.program_application_id
, program_id = p_eam_direct_items_rec.program_id
, program_update_date = SYSDATE
WHERE organization_id = p_eam_direct_items_rec.organization_id
AND wip_entity_id = p_eam_direct_items_rec.wip_entity_id
AND ( operation_seq_num = p_eam_direct_items_rec.operation_seq_num
OR operation_seq_num = 1 ) /* Added the check operation_seq_num = 1 so that op seq num can be changed if it is 1 */
AND direct_item_sequence_id = p_eam_direct_items_rec.direct_item_sequence_id;
END Update_Row;
* Procedure : Delete_Row
* Parameters IN : Direct Items column record
* Parameters OUT NOCOPY: Message Token Table
* Return Status
* Purpose : Procedure will perfrom an Delete on the
* wip_eam_direct_items
*********************************************************************/
PROCEDURE Delete_Row
( p_eam_direct_items_rec IN EAM_PROCESS_WO_PUB.eam_direct_items_rec_type
, x_mesg_token_Tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type
, x_return_Status OUT NOCOPY VARCHAR2
)
IS
l_return_status VARCHAR2(30) := '';
DELETE FROM WIP_EAM_DIRECT_ITEMS
WHERE organization_id = p_eam_direct_items_rec.organization_id
AND wip_entity_id = p_eam_direct_items_rec.wip_entity_id
AND operation_seq_num = p_eam_direct_items_rec.operation_seq_num
AND direct_item_sequence_id = p_eam_direct_items_rec.direct_item_sequence_id;
END Delete_Row;
Insert_Row
( p_eam_direct_items_rec => p_eam_direct_items_rec
, x_mesg_token_Tbl => l_mesg_token_tbl
, x_return_Status => l_return_status
);
ELSIF p_eam_direct_items_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_UPDATE
THEN
Update_Row
( p_eam_direct_items_rec => p_eam_direct_items_rec
, x_mesg_token_Tbl => l_mesg_token_tbl
, x_return_Status => l_return_status
);
ELSIF p_eam_direct_items_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_DELETE
THEN
Delete_Row
( p_eam_direct_items_rec => p_eam_direct_items_rec
, x_mesg_token_Tbl => l_mesg_token_tbl
, x_return_Status => l_return_status
);
SELECT count(*) into l_count
FROM dual
WHERE exists (
SELECT 1
FROM wip_eam_direct_items
WHERE operation_seq_num = p_operation_seq_num
and wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id
) ;