[Home] [Help]
313: , rt.shipment_header_id, rt.po_line_location_id
314: , rt.vendor_id, rt.vendor_site_id
315: , rt.deliver_to_person_id, rt.deliver_to_location_id deliver_to_location_id
316: , rt.location_id location_id
317: from wms_license_plate_numbers lpn,
318: rcv_transactions rt
319: where lpn.lpn_id = rt.lpn_id
320: and lpn.parent_lpn_id <> rt.lpn_id
321: and lpn.parent_lpn_id is not null
337: , rt.shipment_header_id, rt.po_line_location_id
338: , rt.vendor_id, rt.vendor_site_id
339: , rt.deliver_to_person_id, rt.deliver_to_location_id deliver_to_location_id
340: , rt.location_id location_id
341: from wms_license_plate_numbers lpn, rcv_transactions rt
342: where lpn.lpn_id = rt.lpn_id
343: and lpn.outermost_lpn_id <> lpn.lpn_id
344: and lpn.outermost_lpn_id <> lpn.parent_lpn_id
345: and rt.group_id = p_transaction_id
381: , rt.shipment_header_id, rt.po_line_location_id
382: , rt.vendor_id, rt.vendor_site_id
383: , rt.deliver_to_person_id, rt.deliver_to_location_id deliver_to_location_id
384: , rt.location_id location_id
385: from wms_license_plate_numbers lpn, rcv_transactions rt
386: where lpn.lpn_id = rt.transfer_lpn_id
387: and rt.transfer_lpn_id <> rt.lpn_id
388: and lpn.parent_lpn_id is not null
389: and lpn.parent_lpn_id <> lpn.lpn_id
405: , rt.shipment_header_id, rt.po_line_location_id
406: , rt.vendor_id, rt.vendor_site_id
407: , rt.deliver_to_person_id, rt.deliver_to_location_id deliver_to_location_id
408: , rt.location_id location_id
409: from wms_license_plate_numbers lpn, rcv_transactions rt
410: where lpn.lpn_id = rt.transfer_lpn_id
411: and rt.transfer_lpn_id <> rt.lpn_id
412: and lpn.outermost_lpn_id <> lpn.lpn_id
413: and lpn.outermost_lpn_id <> lpn.parent_lpn_id
433: -- , hr_locations_all hrl2
434: -- MOAC : changed po_vendor_sites to po_vendor_sites_all
435: , po_vendor_sites_all pvs
436: , per_people_f ppf
437: , wms_license_plate_numbers wlpn -- Bug 3836623
438: WHERE pha.po_header_id(+) = all_lpn.po_header_id
439: AND rsh.shipment_header_id(+) = all_lpn.shipment_header_id
440: AND rsh.shipment_header_id = rsl.shipment_header_id
441: /* Bug 5241400, Add where clause for rsl and appl_lpn location_id */
552: AND wdd2.delivery_detail_id = wda.parent_delivery_detail_id;
553: */
554: CURSOR c_wdd_lpn IS
555: SELECT wdd.lpn_id, wlpn.organization_id, wlpn.subinventory_code, wlpn.locator_id-- Added locator for Conf Label ER
556: FROM wsh_delivery_details wdd, wms_license_plate_numbers wlpn
557: WHERE wdd.delivery_detail_id = p_transaction_id
558: AND wdd.lpn_id = wlpn.lpn_id;
559:
560: CURSOR c_wnd_lpn IS
645: , rsl.truck_num
646: , rsl.COUNTRY_OF_ORIGIN_CODE
647: , rsl.comments
648: , rsl.vendor_item_num
649: from wms_license_plate_numbers lpn,
650: rcv_shipment_headers rsh,
651: rcv_shipment_lines rsl
652: where lpn.source_name = rsh.shipment_num
653: AND lpn.lpn_context = 7
667: , rsl.truck_num
668: , rsl.COUNTRY_OF_ORIGIN_CODE
669: , rsl.comments
670: , rsl.vendor_item_num
671: from wms_license_plate_numbers lpn,
672: rcv_shipment_headers rsh,
673: rcv_shipment_lines rsl
674: where lpn.source_name = rsh.shipment_num
675: AND lpn.lpn_context = 7
689: , rsl.truck_num
690: , rsl.COUNTRY_OF_ORIGIN_CODE
691: , rsl.comments
692: , rsl.vendor_item_num
693: from wms_license_plate_numbers lpn,
694: rcv_shipment_headers rsh,
695: rcv_shipment_lines rsl
696: where lpn.source_name = rsh.shipment_num
697: AND lpn.lpn_context = 7
791: , lpn.attribute14 lpn_attribute14
792: , lpn.attribute15 lpn_attribute15
793: , nvl(wph.parent_package_id, l_parent_package_id) parent_package
794: , nvl(wph.pack_level, l_pack_level) pack_level
795: FROM WMS_LICENSE_PLATE_NUMBERS lpn
796: , WMS_PACKAGING_HIST wph
797: , WMS_LICENSE_PLATE_NUMBERS plpn
798: , WMS_LICENSE_PLATE_NUMBERS olpn
799: , MTL_SYSTEM_ITEMS_KFV msik
793: , nvl(wph.parent_package_id, l_parent_package_id) parent_package
794: , nvl(wph.pack_level, l_pack_level) pack_level
795: FROM WMS_LICENSE_PLATE_NUMBERS lpn
796: , WMS_PACKAGING_HIST wph
797: , WMS_LICENSE_PLATE_NUMBERS plpn
798: , WMS_LICENSE_PLATE_NUMBERS olpn
799: , MTL_SYSTEM_ITEMS_KFV msik
800: /*Commented for bug# 6334460 start
801: , DUAL d
794: , nvl(wph.pack_level, l_pack_level) pack_level
795: FROM WMS_LICENSE_PLATE_NUMBERS lpn
796: , WMS_PACKAGING_HIST wph
797: , WMS_LICENSE_PLATE_NUMBERS plpn
798: , WMS_LICENSE_PLATE_NUMBERS olpn
799: , MTL_SYSTEM_ITEMS_KFV msik
800: /*Commented for bug# 6334460 start
801: , DUAL d
802: WHERE d.dummy = 'X'
1057: , INV_PROJECT.GET_LOCSEGS(milkfv.inventory_location_id,milkfv.organization_id) locator
1058: , sum(nvl(l_secondary_quantity,wlc.secondary_quantity)) secondary_quantity -- invconv fabdi
1059: , wlc.secondary_uom_code secondary_uom -- invconv fabdi
1060: FROM wms_lpn_contents wlc
1061: , wms_license_plate_numbers plpn
1062: , cst_cost_groups ccg
1063: , mtl_item_locations milkfv
1064: WHERE plpn.lpn_id in (select /*+ cardinality(1) */ lpn_id from wms_license_plate_numbers
1065: where 1=1
1060: FROM wms_lpn_contents wlc
1061: , wms_license_plate_numbers plpn
1062: , cst_cost_groups ccg
1063: , mtl_item_locations milkfv
1064: WHERE plpn.lpn_id in (select /*+ cardinality(1) */ lpn_id from wms_license_plate_numbers
1065: where 1=1
1066: -- Bug 4137707
1067: --start with lpn_id in (select nvl(p_lpn_id, -99) from dual
1068: --union all
1161: -- remove nvl(.) for those parameters
1162: -- Remove p_item_id because it is only used for receiving transactions
1163: --Bug#8366557 Added hints to following cursor
1164: CURSOR c_lpn_item_content_cart(p_lpn_id NUMBER, p_package_id NUMBER) IS
1165: SELECT /*+ ORDERED index(PLPN WMS_LICENSE_PLATE_NUMBERS_U1) use_nl(WLC MILKFV CCG) */
1166: plpn.organization_id organization_id
1167: , wlc.inventory_item_id inventory_item_id
1168: , wlc.revision revision
1169: , wlc.lot_number lot_number
1176: , INV_PROJECT.GET_LOCSEGS(milkfv.inventory_location_id,milkfv.organization_id) locator
1177: , sum(nvl(l_secondary_quantity,wlc.secondary_quantity)) secondary_quantity -- invconv fabdi
1178: , wlc.secondary_uom_code secondary_uom -- invconv fabdi
1179: FROM wms_lpn_contents wlc
1180: , wms_license_plate_numbers plpn
1181: , cst_cost_groups ccg
1182: , mtl_item_locations milkfv
1183: WHERE plpn.lpn_id in (
1184: select /*+ cardinality(1) */ id from
1181: , cst_cost_groups ccg
1182: , mtl_item_locations milkfv
1183: WHERE plpn.lpn_id in (
1184: select /*+ cardinality(1) */ id from
1185: ((select lpn_id id from wms_license_plate_numbers
1186: where 1=1
1187: start with lpn_id in (select nvl(p_lpn_id, -99) from dual
1188: union all
1189: select /*+ cardinality(1) */ lpn_id from wms_packaging_hist
1491: SELECT lpn_id
1492: , license_plate_number
1493: , parent_lpn_id
1494: , outermost_lpn_id
1495: FROM wms_license_plate_numbers
1496: START WITH lpn_id = p_parent_lpn_id
1497: CONNECT BY lpn_id = PRIOR parent_lpn_id;
1498:
1499: CURSOR nested_child_lpn_cursor (p_lpn_id NUMBER) IS
1500: SELECT lpn_id
1501: , license_plate_number
1502: , parent_lpn_id
1503: , outermost_lpn_id
1504: FROM wms_license_plate_numbers
1505: START WITH parent_lpn_id = p_lpn_id
1506: CONNECT BY parent_lpn_id = PRIOR lpn_id;
1507: --END Conf Label ER
1508: