[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,
547: --lpn status project end
548:
549: FROM mtl_material_transactions_temp mmtt,
550: mtl_transaction_lots_temp mtlt,
551: wms_license_plate_numbers lpn,
552: wms_license_plate_numbers lpn3,
553: mtl_parameters mp,
554: cst_cost_groups ccg,
555: mtl_item_locations_kfv mlc ,
548:
549: FROM mtl_material_transactions_temp mmtt,
550: mtl_transaction_lots_temp mtlt,
551: wms_license_plate_numbers lpn,
552: wms_license_plate_numbers lpn3,
553: mtl_parameters mp,
554: cst_cost_groups ccg,
555: mtl_item_locations_kfv mlc ,
556: mtl_secondary_inventories msub,
615: --lpn status project end
616:
617: FROM
618: wms_lpn_contents wlc,
619: wms_license_plate_numbers lpn,
620: wms_license_plate_numbers lpn3,
621: mtl_parameters mp,
622: cst_cost_groups ccg,
623: mtl_item_locations mlc ,
616:
617: FROM
618: wms_lpn_contents wlc,
619: wms_license_plate_numbers lpn,
620: wms_license_plate_numbers lpn3,
621: mtl_parameters mp,
622: cst_cost_groups ccg,
623: mtl_item_locations mlc ,
624: mtl_secondary_inventories msub,
681: --lpn status project start
682: wlc.parent_lpn_id
683: --lpn status project end
684: FROM wms_lpn_contents wlc,
685: wms_license_plate_numbers lpn,
686: mtl_parameters mp,
687: wms_license_plate_numbers lpn3,
688: cst_cost_groups ccg,
689: mtl_item_locations_kfv mlc ,
683: --lpn status project end
684: FROM wms_lpn_contents wlc,
685: wms_license_plate_numbers lpn,
686: mtl_parameters mp,
687: wms_license_plate_numbers lpn3,
688: cst_cost_groups ccg,
689: mtl_item_locations_kfv mlc ,
690: mtl_secondary_inventories msub,
691: mtl_item_locations_kfv milk,
785: wlc.serial_number,
786: nvl(wlc.cost_group_id, 0),
787: wlc.source_name
788: ) wwlc,
789: wms_license_plate_numbers lpn,
790: wms_license_plate_numbers lpn3,
791: mtl_parameters mp,
792: cst_cost_groups ccg,
793: mtl_item_locations mlc ,
786: nvl(wlc.cost_group_id, 0),
787: wlc.source_name
788: ) wwlc,
789: wms_license_plate_numbers lpn,
790: wms_license_plate_numbers lpn3,
791: mtl_parameters mp,
792: cst_cost_groups ccg,
793: mtl_item_locations mlc ,
794: mtl_secondary_inventories msub,
888: wlc.parent_lpn_id
889: --lpn status project end
890:
891: FROM wms_lpn_contents wlc,
892: wms_license_plate_numbers lpn,
893: mtl_parameters mp,
894: wms_license_plate_numbers lpn3,
895: cst_cost_groups ccg,
896: mtl_item_locations_kfv mlc ,
890:
891: FROM wms_lpn_contents wlc,
892: wms_license_plate_numbers lpn,
893: mtl_parameters mp,
894: wms_license_plate_numbers lpn3,
895: cst_cost_groups ccg,
896: mtl_item_locations_kfv mlc ,
897: mtl_secondary_inventories msub,
898: mtl_item_locations_kfv milk,
975: , nvl(wlc.secondary_quantity, 0)
976: , msiv.secondary_uom_code
977: -- INVCONV end
978: FROM WMS_LPN_CONTENTS wlc
979: , WMS_LICENSE_PLATE_NUMBERS lpn
980: , mtl_system_items_vl msiv -- Modified for Bug # 5472330
981: , mtl_parameters mp
982: , wms_license_plate_numbers lpn3
983: , cst_cost_groups ccg
978: FROM WMS_LPN_CONTENTS wlc
979: , WMS_LICENSE_PLATE_NUMBERS lpn
980: , mtl_system_items_vl msiv -- Modified for Bug # 5472330
981: , mtl_parameters mp
982: , wms_license_plate_numbers lpn3
983: , cst_cost_groups ccg
984: , mtl_item_locations mlc
985: , mtl_secondary_inventories msub
986: , mtl_lot_numbers mln
1034:
1035: and mlk.lookup_type = 'WMS_LPN_CONTEXT'
1036: and mlk.lookup_code = lpn.lpn_context(+)
1037: and not exists (select wlpn.lpn_id
1038: from wms_license_plate_numbers wlpn,
1039: mtl_material_transactions_temp t,
1040: wms_dispatched_tasks w
1041: where w.status = 4
1042: and w.task_type <> 2