[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
539: -- Bug 3668603
540: -- Before deleting the routing header, check if it is referenced,
541: -- For each routing_sequence_id referenced in the cancelled revised items ,
542: -- Check using cursor c_check_rtg_header_del, if it is referenced.
543: -- If referenced, the header is not deleted and bom_operational_routings.pending_for_ecn is
544: -- set to null for the routing header header if value is current eco change_notice.
545: -- If not referenced, then delete the routing revisions if the header is a primary routing
546: -- Delete the header and unset the routing_sequence_id on the revised items
547: -- using the routing_sequence_id.
561: IF (l_del_rtg_header = 1)
562: THEN
563: DELETE FROM MTL_RTG_ITEM_REVISIONS rev
564: WHERE EXISTS (SELECT 1
565: FROM BOM_OPERATIONAL_ROUTINGS bor
566: WHERE bor.routing_sequence_id = l_routing_sequence_id
567: AND bor.alternate_routing_designator IS NULL
568: AND bor.assembly_item_id = rev.INVENTORY_ITEM_ID
569: AND bor.organization_id = rev.organization_id);
567: AND bor.alternate_routing_designator IS NULL
568: AND bor.assembly_item_id = rev.INVENTORY_ITEM_ID
569: AND bor.organization_id = rev.organization_id);
570:
571: DELETE FROM BOM_OPERATIONAL_ROUTINGS
572: WHERE routing_sequence_id = l_routing_sequence_id;
573:
574: -- If routing was deleted, then unset the routing_sequence_id on the revised items
575: UPDATE ENG_REVISED_ITEMS ri
580: AND ri.change_notice = change_order
581: AND ri.status_type = 5 -- Cancelled
582: AND ri.routing_sequence_id = l_routing_sequence_id
583: AND NOT EXISTS (SELECT 'No Rtg Header'
584: FROM BOM_OPERATIONAL_ROUTINGS bor
585: WHERE bor.routing_sequence_id = ri.routing_sequence_id
586: ) ;
587: ELSE
588:
585: WHERE bor.routing_sequence_id = ri.routing_sequence_id
586: ) ;
587: ELSE
588:
589: UPDATE BOM_OPERATIONAL_ROUTINGS
590: SET last_update_date = SYSDATE
591: , last_updated_by = user_id
592: , last_update_login = login
593: , pending_from_ecn = null
597:
598: END LOOP;
599:
600:
601: /*DELETE FROM BOM_OPERATIONAL_ROUTINGS bor
602: WHERE EXISTS ( SELECT NULL
603: FROM ENG_REVISED_ITEMS ri
604: WHERE
605: bor.routing_sequence_id = ri.routing_sequence_id
615: OR bos.change_notice <> change_notice)
616: )
617: AND (( bor.alternate_routing_designator IS NULL
618: AND NOT EXISTS( SELECT NULL
619: FROM BOM_OPERATIONAL_ROUTINGS bor2
620: WHERE bor2.organization_id = bor.organization_id
621: AND bor2.assembly_item_id = bor.assembly_item_id
622: AND bor2.alternate_routing_designator IS NOT NULL )
623: )
639: WHERE ri.organization_id = org_id
640: AND ri.change_notice = change_notice
641: AND ri.status_type = 5 -- Cancelled
642: AND NOT EXISTS (SELECT 'No Rtg Header'
643: FROM BOM_OPERATIONAL_ROUTINGS bor
644: WHERE bor.routing_sequence_id = ri.routing_sequence_id
645: ) ;
646: END IF;*/
647: