The following lines contain the word 'select', 'insert', 'update' or 'delete':
* transaction type is Update or Delete and the record
* does not exist then the return status would be an
* error as well. Mesg_Token_Table will carry the
* error messsage and the tokens associated with the
* message.
*********************************************************************/
PROCEDURE Check_Existence
( p_rtg_revision_rec IN Bom_Rtg_Pub.Rtg_Revision_Rec_Type
, p_rtg_rev_unexp_rec IN Bom_Rtg_Pub.Rtg_Rev_Unexposed_Rec_Type
, x_old_rtg_revision_rec IN OUT NOCOPY Bom_Rtg_Pub.Rtg_Revision_Rec_Type
, x_old_rtg_rev_unexp_rec IN OUT NOCOPY Bom_Rtg_Pub.Rtg_Rev_Unexposed_Rec_Type
, x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
, x_return_status IN OUT NOCOPY VARCHAR2
)
IS
l_token_tbl Error_Handler.Token_Tbl_Type;
(BOM_RTG_Globals.G_OPR_UPDATE, BOM_Rtg_Globals.G_OPR_DELETE)
THEN
l_token_tbl(1).token_name := 'ASSEMBLY_ITEM_NAME';
SELECT change_notice
FROM mtl_rtg_item_revisions
WHERE inventory_item_id = p_rtg_rev_unexp_rec.assembly_item_id
AND organization_id = p_rtg_rev_unexp_rec.organization_id
AND process_revision = p_rtg_revision_rec.revision;
IF p_rtg_revision_rec.transaction_type= BOM_Rtg_Globals.G_OPR_UPDATE
THEN
open c_created_by_eco;
SELECT process_revision
FROM mtl_rtg_item_revisions
WHERE inventory_item_id = p_rtg_rev_unexp_rec.assembly_item_id
AND organization_id = p_rtg_rev_unexp_rec.organization_id
AND effectivity_date <= sysdate --added by arudresh, bug: 3756380
AND IMPLEMENTATION_DATE IS NOT NULL
ORDER BY effectivity_date desc, process_revision desc;
l_token_tbl.delete(3);
SELECT routing_sequence_id
INTO l_dummy
FROM bom_operational_routings
WHERE assembly_item_id = p_rtg_rev_Unexp_Rec.assembly_item_id
AND organization_id = p_rtg_rev_Unexp_Rec.organization_id
AND nvl(alternate_routing_designator,'A') =
nvl(p_rtg_revision_rec.alternate_routing_code,'A');
l_token_tbl.delete(2) ;
SELECT 1
INTO l_dummy
FROM dual
WHERE not exists
(SELECT 1
FROM mtl_rtg_item_revisions
WHERE organization_id = p_rtg_rev_Unexp_Rec.Organization_Id
AND inventory_item_id = p_rtg_rev_Unexp_Rec.assembly_Item_Id
AND process_revision = p_rtg_revision_Rec.revision
);
BOM_Rtg_Globals.G_OPR_UPDATE AND
p_old_rtg_revision_Rec.start_effective_date <>
p_rtg_revision_rec.start_effective_date AND
TRUNC(NVL(p_rtg_revision_rec.start_effective_date,SYSDATE))
< TRUNC(SYSDATE)
)
THEN
x_return_status := FND_API.G_RET_STS_ERROR;
l_token_tbl.delete(3) ;
IF p_rtg_revision_rec.transaction_type <> BOM_Rtg_Globals.G_OPR_DELETE
THEN
BEGIN
SELECT 1
INTO l_dummy
FROM dual
WHERE p_rtg_revision_rec.start_effective_date >
(SELECT nvl(max(effectivity_date),
p_rtg_revision_rec.start_effective_Date-1)
FROM mtl_rtg_item_revisions
WHERE inventory_item_id = p_rtg_rev_Unexp_Rec.assembly_Item_Id
AND organization_id = p_rtg_rev_Unexp_Rec.Organization_Id
AND process_revision < p_rtg_revision_rec.Revision
)
AND p_rtg_revision_rec.start_effective_date <
(SELECT nvl(min(effectivity_date),
p_rtg_revision_rec.start_effective_Date+1)
FROM mtl_rtg_item_revisions
WHERE inventory_item_id =p_rtg_rev_Unexp_Rec.assembly_Item_Id
AND organization_id = p_rtg_rev_Unexp_Rec.Organization_Id
AND process_revision > p_rtg_revision_rec.Revision
);
l_token_tbl.delete(3) ;
IF p_rtg_revision_rec.transaction_type = BOM_Rtg_Globals.G_OPR_DELETE
THEN
SELECT effectivity_date into l_effectivity_date
FROM mtl_rtg_item_revisions
WHERE inventory_item_id =p_rtg_rev_Unexp_Rec.assembly_Item_Id
AND organization_id = p_rtg_rev_Unexp_Rec.Organization_Id
AND process_revision = p_rtg_revision_rec.Revision;
( p_message_name => 'BOM_RTG_REV_CANNOT_DELETE'
, p_mesg_token_tbl => l_mesg_token_tbl
, x_mesg_token_tbl => l_mesg_token_tbl
, p_token_tbl => l_token_tbl
);