[Home] [Help]
47:
48: -- Cursor to check if the routing header used in the revised item has any references
49: Cursor c_check_rtg_header_del(p_routing_sequence_id NUMBER) is
50: select routing_sequence_id
51: from bom_operational_routings bor
52: WHERE bor.pending_from_ecn IS NOT NULL
53: And bor.routing_sequence_id = p_routing_sequence_id
54: and not exists (select null
55: from BOM_OPERATION_SEQUENCES bos
58: or
59: bos.change_notice <> change_order))
60: and ((bor.alternate_routing_designator is null
61: and not exists (select null
62: from BOM_OPERATIONAL_ROUTINGS bor2
63: where bor2.organization_id = bor.organization_id
64: and bor2.assembly_item_id = bor.assembly_item_id
65: and bor2.alternate_routing_designator is not null)
66: and not exists (select null
523: -- Bug 3668603
524: -- Before deleting the routing header, check if it is referenced,
525: -- For each routing_sequence_id referenced in the cancelled revised items ,
526: -- Check using cursor c_check_rtg_header_del, if it is referenced.
527: -- If referenced, the header is not deleted and bom_operational_routings.pending_for_ecn is
528: -- set to null for the routing header header if value is current eco change_notice.
529: -- If not referenced, then delete the routing revisions if the header is a primary routing
530: -- Delete the header and unset the routing_sequence_id on the revised items
531: -- using the routing_sequence_id.
545: IF (l_del_rtg_header = 1)
546: THEN
547: DELETE FROM MTL_RTG_ITEM_REVISIONS rev
548: WHERE EXISTS (SELECT 1
549: FROM BOM_OPERATIONAL_ROUTINGS bor
550: WHERE bor.routing_sequence_id = l_routing_sequence_id
551: AND bor.alternate_routing_designator IS NULL
552: AND bor.assembly_item_id = rev.INVENTORY_ITEM_ID
553: AND bor.organization_id = rev.organization_id);
551: AND bor.alternate_routing_designator IS NULL
552: AND bor.assembly_item_id = rev.INVENTORY_ITEM_ID
553: AND bor.organization_id = rev.organization_id);
554:
555: DELETE FROM BOM_OPERATIONAL_ROUTINGS
556: WHERE routing_sequence_id = l_routing_sequence_id;
557:
558: -- If routing was deleted, then unset the routing_sequence_id on the revised items
559: UPDATE ENG_REVISED_ITEMS ri
564: AND ri.change_notice = change_order
565: AND ri.status_type = 5 -- Cancelled
566: AND ri.routing_sequence_id = l_routing_sequence_id
567: AND NOT EXISTS (SELECT 'No Rtg Header'
568: FROM BOM_OPERATIONAL_ROUTINGS bor
569: WHERE bor.routing_sequence_id = ri.routing_sequence_id
570: ) ;
571: ELSE
572:
569: WHERE bor.routing_sequence_id = ri.routing_sequence_id
570: ) ;
571: ELSE
572:
573: UPDATE BOM_OPERATIONAL_ROUTINGS
574: SET last_update_date = SYSDATE
575: , last_updated_by = user_id
576: , last_update_login = login
577: , pending_from_ecn = null
581:
582: END LOOP;
583:
584:
585: /*DELETE FROM BOM_OPERATIONAL_ROUTINGS bor
586: WHERE EXISTS ( SELECT NULL
587: FROM ENG_REVISED_ITEMS ri
588: WHERE
589: bor.routing_sequence_id = ri.routing_sequence_id
599: OR bos.change_notice <> change_notice)
600: )
601: AND (( bor.alternate_routing_designator IS NULL
602: AND NOT EXISTS( SELECT NULL
603: FROM BOM_OPERATIONAL_ROUTINGS bor2
604: WHERE bor2.organization_id = bor.organization_id
605: AND bor2.assembly_item_id = bor.assembly_item_id
606: AND bor2.alternate_routing_designator IS NOT NULL )
607: )
623: WHERE ri.organization_id = org_id
624: AND ri.change_notice = change_notice
625: AND ri.status_type = 5 -- Cancelled
626: AND NOT EXISTS (SELECT 'No Rtg Header'
627: FROM BOM_OPERATIONAL_ROUTINGS bor
628: WHERE bor.routing_sequence_id = ri.routing_sequence_id
629: ) ;
630: END IF;*/
631: