[Home] [Help]
35: and revised_item_id = p_revised_item_id
36: and revised_item_sequence_Id = p_revised_item_sequence_id
37: and change_notice = p_change_notice ;
38:
39: DELETE FROM BOM_OPERATIONAL_ROUTINGS bor
40: where bor.routing_sequence_id = p_routing_sequence_id
41: and bor.pending_from_ecn = p_change_notice
42: and not exists (select null
43: from BOM_OPERATION_SEQUENCES bos
49: (bos.change_notice = p_change_notice
50: and bos.revised_item_sequence_id <> p_revised_item_sequence_id)))
51: and ((bor.alternate_routing_designator is null
52: and not exists (select null
53: from BOM_OPERATIONAL_ROUTINGS bor2
54: where bor2.organization_id = bor.organization_id
55: and bor2.assembly_item_id = bor.assembly_item_id
56: and bor2.alternate_routing_designator is not null))
57: or
290: -- Changes for Bug 3668603
291: -- Cursor to check if the routing header used in the revised item has any references
292: Cursor c_check_rtg_header_del is
293: select routing_sequence_id
294: from bom_operational_routings bor
295: where bor.routing_sequence_id = routing_seq_id
296: and bor.pending_from_ecn = change_order
297: and not exists (select null
298: from BOM_OPERATION_SEQUENCES bos
304: (bos.change_notice = change_order
305: and bos.revised_item_sequence_id <> rev_item_seq)))
306: and ((bor.alternate_routing_designator is null
307: and not exists (select null
308: from BOM_OPERATIONAL_ROUTINGS bor2
309: where bor2.organization_id = bor.organization_id
310: and bor2.assembly_item_id = bor.assembly_item_id
311: and bor2.alternate_routing_designator is not null)
312: and not exists (select null
436: -- Delete the routing header if routing was created by this revised item and
437: -- nothing else references this
438: -- Bug 3668603
439: -- Before deleting the routing header, Check using cursor c_check_rtg_header_del, if it is referenced.
440: -- If referenced, the header is not deleted and bom_operational_routings.pending_for_ecn is
441: -- set to null for the routing header header if pending_form_eco =change_order
442: -- If not referenced, then delete the routing revisions if the header is a primary routing
443: -- Delete the header and unset the routing_sequence_id on the revised items
444: -- using the routing_sequence_id.
452: IF (l_del_rtg_header = 1)
453: THEN
454: DELETE FROM MTL_RTG_ITEM_REVISIONS rev
455: WHERE EXISTS (SELECT 1
456: FROM BOM_OPERATIONAL_ROUTINGS bor
457: WHERE bor.routing_sequence_id = routing_seq_id
458: AND bor.alternate_routing_designator IS NULL
459: AND bor.assembly_item_id = rev.INVENTORY_ITEM_ID
460: AND bor.organization_id = rev.organization_id);
458: AND bor.alternate_routing_designator IS NULL
459: AND bor.assembly_item_id = rev.INVENTORY_ITEM_ID
460: AND bor.organization_id = rev.organization_id);
461:
462: DELETE FROM BOM_OPERATIONAL_ROUTINGS
463: WHERE routing_sequence_id = routing_seq_id;
464: ELSE
465: UPDATE BOM_OPERATIONAL_ROUTINGS
466: SET last_update_date = SYSDATE,
461:
462: DELETE FROM BOM_OPERATIONAL_ROUTINGS
463: WHERE routing_sequence_id = routing_seq_id;
464: ELSE
465: UPDATE BOM_OPERATIONAL_ROUTINGS
466: SET last_update_date = SYSDATE,
467: last_updated_by = user_id,
468: last_update_login = login,
469: pending_from_ecn = null
471: AND pending_from_ecn = change_order;
472: END IF;
473:
474:
475: /* DELETE FROM BOM_OPERATIONAL_ROUTINGS bor1
476: WHERE bor1.routing_sequence_id = routing_seq_id
477: AND bor1.pending_from_ecn = change_order
478: AND NOT EXISTS (SELECT NULL
479: FROM BOM_OPERATION_SEQUENCES bos
482: OR bos.CHANGE_NOTICE <> change_order)
483: )
484: AND ((bor1.ALTERNATE_ROUTING_DESIGNATOR IS NULL
485: AND NOT EXISTS (SELECT NULL
486: FROM BOM_OPERATIONAL_ROUTINGS bor2
487: WHERE bor2.ORGANIZATION_ID = bor1.ORGANIZATION_ID
488: AND bor2.ASSEMBLY_ITEM_ID = bor1.ASSEMBLY_ITEM_ID
489: AND bor2.ALTERNATE_ROUTING_DESIGNATOR IS NOT NULL))
490: OR