[Home] [Help]
399: Locator and Sub are fetched. (as part of bug 2314495)
400: ****************************************************************************/
401:
402: /* Changes for Bug #2810546
403: * a) Removed outer join between MFG_LOOKUPS and WMS_LICENSE_PLATE_NUMBERS since
404: * an LPN will always have a context associated
405: * b) Forked the code to fetch the dock door if the LPN context is "Loaded to Dock(9)"
406: * For this join between wms_license_plate_numbers, wms_shipping_transactions_temp
407: * and mtl_item_locations.
402: /* Changes for Bug #2810546
403: * a) Removed outer join between MFG_LOOKUPS and WMS_LICENSE_PLATE_NUMBERS since
404: * an LPN will always have a context associated
405: * b) Forked the code to fetch the dock door if the LPN context is "Loaded to Dock(9)"
406: * For this join between wms_license_plate_numbers, wms_shipping_transactions_temp
407: * and mtl_item_locations.
408: * d) For other LPN contexts, we do not need dock door information. So removed the
409: * join with wms_shipping_transactions_temp and mtl_item_locations (milk2).
410: * e) Use the cached values for PROJECT_NUMBER and TASK_NUMBER instead of fetching
418: l_lpn_context_id NUMBER;
419: BEGIN
420:
421: SELECT lpn_context INTO l_lpn_context_id
422: FROM wms_license_plate_numbers
423: WHERE lpn_id = p_parent_lpn_id;
424:
425: /********************************************************************************
426: * The formation of cursor is different when the LPN Context is Packing Context.
477: --lpn status project start
478: NVL(mmtt.lpn_id,mmtt.content_lpn_id)
479: --lpn status project end
480: FROM mtl_material_transactions_temp mmtt,
481: wms_license_plate_numbers lpn,
482: wms_license_plate_numbers lpn3,
483: mtl_parameters mp,
484: cst_cost_groups ccg,
485: mtl_item_locations_kfv mlc ,
478: NVL(mmtt.lpn_id,mmtt.content_lpn_id)
479: --lpn status project end
480: FROM mtl_material_transactions_temp mmtt,
481: wms_license_plate_numbers lpn,
482: wms_license_plate_numbers lpn3,
483: mtl_parameters mp,
484: cst_cost_groups ccg,
485: mtl_item_locations_kfv mlc ,
486: mtl_secondary_inventories msub,
585: --lpn status project end
586:
587: FROM mtl_material_transactions_temp mmtt,
588: mtl_transaction_lots_temp mtlt,
589: wms_license_plate_numbers lpn,
590: wms_license_plate_numbers lpn3,
591: mtl_parameters mp,
592: cst_cost_groups ccg,
593: mtl_item_locations_kfv mlc ,
586:
587: FROM mtl_material_transactions_temp mmtt,
588: mtl_transaction_lots_temp mtlt,
589: wms_license_plate_numbers lpn,
590: wms_license_plate_numbers lpn3,
591: mtl_parameters mp,
592: cst_cost_groups ccg,
593: mtl_item_locations_kfv mlc ,
594: mtl_secondary_inventories msub,
691: --lpn status project end
692:
693: FROM
694: wms_lpn_contents wlc,
695: wms_license_plate_numbers lpn,
696: wms_license_plate_numbers lpn3,
697: mtl_parameters mp,
698: cst_cost_groups ccg,
699: mtl_item_locations mlc ,
692:
693: FROM
694: wms_lpn_contents wlc,
695: wms_license_plate_numbers lpn,
696: wms_license_plate_numbers lpn3,
697: mtl_parameters mp,
698: cst_cost_groups ccg,
699: mtl_item_locations mlc ,
700: mtl_secondary_inventories msub,
757: --lpn status project start
758: wlc.parent_lpn_id
759: --lpn status project end
760: FROM wms_lpn_contents wlc,
761: wms_license_plate_numbers lpn,
762: mtl_parameters mp,
763: wms_license_plate_numbers lpn3,
764: cst_cost_groups ccg,
765: mtl_item_locations_kfv mlc ,
759: --lpn status project end
760: FROM wms_lpn_contents wlc,
761: wms_license_plate_numbers lpn,
762: mtl_parameters mp,
763: wms_license_plate_numbers lpn3,
764: cst_cost_groups ccg,
765: mtl_item_locations_kfv mlc ,
766: mtl_secondary_inventories msub,
767: mtl_item_locations_kfv milk,
861: wlc.serial_number,
862: nvl(wlc.cost_group_id, 0),
863: wlc.source_name
864: ) wwlc,
865: wms_license_plate_numbers lpn,
866: wms_license_plate_numbers lpn3,
867: mtl_parameters mp,
868: cst_cost_groups ccg,
869: mtl_item_locations mlc ,
862: nvl(wlc.cost_group_id, 0),
863: wlc.source_name
864: ) wwlc,
865: wms_license_plate_numbers lpn,
866: wms_license_plate_numbers lpn3,
867: mtl_parameters mp,
868: cst_cost_groups ccg,
869: mtl_item_locations mlc ,
870: mtl_secondary_inventories msub,
964: wlc.parent_lpn_id
965: --lpn status project end
966:
967: FROM wms_lpn_contents wlc,
968: wms_license_plate_numbers lpn,
969: mtl_parameters mp,
970: wms_license_plate_numbers lpn3,
971: cst_cost_groups ccg,
972: mtl_item_locations_kfv mlc ,
966:
967: FROM wms_lpn_contents wlc,
968: wms_license_plate_numbers lpn,
969: mtl_parameters mp,
970: wms_license_plate_numbers lpn3,
971: cst_cost_groups ccg,
972: mtl_item_locations_kfv mlc ,
973: mtl_secondary_inventories msub,
974: mtl_item_locations_kfv milk,
1051: , nvl(wlc.secondary_quantity, 0)
1052: , msiv.secondary_uom_code
1053: -- INVCONV end
1054: FROM WMS_LPN_CONTENTS wlc
1055: , WMS_LICENSE_PLATE_NUMBERS lpn
1056: , mtl_system_items_vl msiv -- Modified for Bug # 5472330
1057: , mtl_parameters mp
1058: , wms_license_plate_numbers lpn3
1059: , cst_cost_groups ccg
1054: FROM WMS_LPN_CONTENTS wlc
1055: , WMS_LICENSE_PLATE_NUMBERS lpn
1056: , mtl_system_items_vl msiv -- Modified for Bug # 5472330
1057: , mtl_parameters mp
1058: , wms_license_plate_numbers lpn3
1059: , cst_cost_groups ccg
1060: , mtl_item_locations mlc
1061: , mtl_secondary_inventories msub
1062: , mtl_lot_numbers mln
1110:
1111: and mlk.lookup_type = 'WMS_LPN_CONTEXT'
1112: and mlk.lookup_code = lpn.lpn_context(+)
1113: and not exists (select wlpn.lpn_id
1114: from wms_license_plate_numbers wlpn,
1115: mtl_material_transactions_temp t,
1116: wms_dispatched_tasks w
1117: where w.status = 4
1118: and w.task_type <> 2