[Home] [Help]
52: and revised_item_id = p_revised_item_id
53: and revised_item_sequence_Id = p_revised_item_sequence_id
54: and change_notice = p_change_notice ;
55:
56: DELETE FROM BOM_OPERATIONAL_ROUTINGS bor
57: where bor.routing_sequence_id = p_routing_sequence_id
58: and bor.pending_from_ecn = p_change_notice
59: and not exists (select null
60: from BOM_OPERATION_SEQUENCES bos
66: (bos.change_notice = p_change_notice
67: and bos.revised_item_sequence_id <> p_revised_item_sequence_id)))
68: and ((bor.alternate_routing_designator is null
69: and not exists (select null
70: from BOM_OPERATIONAL_ROUTINGS bor2
71: where bor2.organization_id = bor.organization_id
72: and bor2.assembly_item_id = bor.assembly_item_id
73: and bor2.alternate_routing_designator is not null))
74: or
307: -- Changes for Bug 3668603
308: -- Cursor to check if the routing header used in the revised item has any references
309: Cursor c_check_rtg_header_del is
310: select routing_sequence_id
311: from bom_operational_routings bor
312: where bor.routing_sequence_id = routing_seq_id
313: and bor.pending_from_ecn = change_order
314: and not exists (select null
315: from BOM_OPERATION_SEQUENCES bos
321: (bos.change_notice = change_order
322: and bos.revised_item_sequence_id <> rev_item_seq)))
323: and ((bor.alternate_routing_designator is null
324: and not exists (select null
325: from BOM_OPERATIONAL_ROUTINGS bor2
326: where bor2.organization_id = bor.organization_id
327: and bor2.assembly_item_id = bor.assembly_item_id
328: and bor2.alternate_routing_designator is not null)
329: and not exists (select null
453: -- Delete the routing header if routing was created by this revised item and
454: -- nothing else references this
455: -- Bug 3668603
456: -- Before deleting the routing header, Check using cursor c_check_rtg_header_del, if it is referenced.
457: -- If referenced, the header is not deleted and bom_operational_routings.pending_for_ecn is
458: -- set to null for the routing header header if pending_form_eco =change_order
459: -- If not referenced, then delete the routing revisions if the header is a primary routing
460: -- Delete the header and unset the routing_sequence_id on the revised items
461: -- using the routing_sequence_id.
469: IF (l_del_rtg_header = 1)
470: THEN
471: DELETE FROM MTL_RTG_ITEM_REVISIONS rev
472: WHERE EXISTS (SELECT 1
473: FROM BOM_OPERATIONAL_ROUTINGS bor
474: WHERE bor.routing_sequence_id = routing_seq_id
475: AND bor.alternate_routing_designator IS NULL
476: AND bor.assembly_item_id = rev.INVENTORY_ITEM_ID
477: AND bor.organization_id = rev.organization_id);
475: AND bor.alternate_routing_designator IS NULL
476: AND bor.assembly_item_id = rev.INVENTORY_ITEM_ID
477: AND bor.organization_id = rev.organization_id);
478:
479: DELETE FROM BOM_OPERATIONAL_ROUTINGS
480: WHERE routing_sequence_id = routing_seq_id;
481: ELSE
482: UPDATE BOM_OPERATIONAL_ROUTINGS
483: SET last_update_date = SYSDATE,
478:
479: DELETE FROM BOM_OPERATIONAL_ROUTINGS
480: WHERE routing_sequence_id = routing_seq_id;
481: ELSE
482: UPDATE BOM_OPERATIONAL_ROUTINGS
483: SET last_update_date = SYSDATE,
484: last_updated_by = user_id,
485: last_update_login = login,
486: pending_from_ecn = null
488: AND pending_from_ecn = change_order;
489: END IF;
490:
491:
492: /* DELETE FROM BOM_OPERATIONAL_ROUTINGS bor1
493: WHERE bor1.routing_sequence_id = routing_seq_id
494: AND bor1.pending_from_ecn = change_order
495: AND NOT EXISTS (SELECT NULL
496: FROM BOM_OPERATION_SEQUENCES bos
499: OR bos.CHANGE_NOTICE <> change_order)
500: )
501: AND ((bor1.ALTERNATE_ROUTING_DESIGNATOR IS NULL
502: AND NOT EXISTS (SELECT NULL
503: FROM BOM_OPERATIONAL_ROUTINGS bor2
504: WHERE bor2.ORGANIZATION_ID = bor1.ORGANIZATION_ID
505: AND bor2.ASSEMBLY_ITEM_ID = bor1.ASSEMBLY_ITEM_ID
506: AND bor2.ALTERNATE_ROUTING_DESIGNATOR IS NOT NULL))
507: OR