[Home] [Help]
27: SELECT DISTINCT
28: wda.delivery_id,
29: mil.inventory_location_id
30: FROM
31: wms_license_plate_numbers wlpn,
32: wsh_delivery_details wdd,
33: wsh_delivery_assignments_v wda,
34: mtl_item_locations mil
35: WHERE wlpn.organization_id = p_org_id
80:
81: SELECT COUNT(DISTINCT wlpn.outermost_lpn_id)
82: INTO x_total_no_of_lpns
83: FROM
84: wms_license_plate_numbers wlpn,
85: mtl_item_locations mil
86: WHERE wlpn.lpn_context in (11,12)
87: AND wlpn.organization_id = p_org_id
88: AND wlpn.subinventory_code = p_sub
160: ml.meaning
161: FROM
162: mtl_item_locations_kfv milk,
163: mtl_item_locations mil,
164: wms_license_plate_numbers wlpn,
165: mfg_lookups ml
166: WHERE milk.organization_id = p_org_id
167: AND milk.subinventory_code = p_sub
168: AND milk.concatenated_segments LIKE l_loc
189: ml.meaning
190: FROM
191: mtl_item_locations_kfv milk,
192: mtl_item_locations mil,
193: wms_license_plate_numbers wlpn,
194: wsh_delivery_details wdd2,
195: wsh_delivery_assignments_v wda,
196: mfg_lookups ml
197: WHERE milk.organization_id = p_org_id
379: /* lms code end */
380: nvl(milk.inventory_location_type, 3),
381: mil.picking_order
382: FROM
383: wms_license_plate_numbers wlpn,
384: mtl_item_locations_kfv milk,
385: mtl_item_locations mil
386: WHERE wlpn.lpn_context in (11, 12)
387: AND wlpn.organization_id = p_org_id
423: FROM
424: wsh_new_deliveries wnd,
425: wsh_delivery_assignments_v wda,
426: wsh_delivery_details wdd,
427: wms_license_plate_numbers wlpn
428: WHERE wda.parent_delivery_detail_id = wdd.delivery_detail_id
429: AND wda.delivery_id = wnd.delivery_id
430: AND wnd.name LIKE p_delivery_name
431: AND wnd.organization_id = p_organization_id
486: wsh_delivery_assignments_v wda,
487: -- ra_customers c, -- Commented for Bug# 4579790
488: hz_parties party, -- Added for Bug# 4579790
489: hz_cust_accounts cust_acct, -- Added for Bug# 4579790
490: wms_license_plate_numbers wlpn,
491: wsh_delivery_details wdd2
492: WHERE wdd.customer_id = cust_acct.cust_account_id -- Added for Bug# 4579790
493: --wdd.customer_id = c.customer_id -- Commented for Bug# 4579790
494: AND cust_acct.party_id = party.party_id -- Added for Bug# 4579790
550: wsh_delivery_assignments_v wda,
551: -- ra_customers c, -- Commented for Bug# 4579790
552: hz_parties party, -- Added for Bug# 4579790
553: hz_cust_accounts cust_acct, -- Added for Bug# 4579790
554: wms_license_plate_numbers wlpn,
555: wsh_delivery_details wdd2
556: WHERE wdd.customer_id = cust_acct.cust_account_id -- Added for Bug# 4579790
557: --wdd.customer_id = c.customer_id -- Commented for Bug# 4579790
558: AND cust_acct.party_id = party.party_id -- Added for Bug# 4579790
665: mil.picking_order
666: FROM
667: mtl_item_locations_kfv milk,
668: mtl_item_locations mil,
669: wms_license_plate_numbers wlpn,
670: mfg_lookups ml
671: WHERE milk.organization_id = p_org_id
672: AND milk.alias = p_alias
673: AND milk.subinventory_code = p_sub
697: mil.picking_order
698: FROM
699: mtl_item_locations_kfv milk,
700: mtl_item_locations mil,
701: wms_license_plate_numbers wlpn,
702: wsh_delivery_details wdd2,
703: wsh_delivery_assignments_v wda,
704: mfg_lookups ml
705: WHERE milk.organization_id = p_org_id
753: mil.picking_order
754: FROM
755: mtl_item_locations_kfv milk,
756: mtl_item_locations mil,
757: wms_license_plate_numbers wlpn,
758: mfg_lookups ml
759: WHERE milk.organization_id = p_org_id
760: AND INV_PROJECT.GET_LOCSEGS(milk.inventory_location_id, milk.organization_id) LIKE p_loc -- bug 2769126
761: AND milk.subinventory_code = p_sub
785: mil.picking_order
786: FROM
787: mtl_item_locations_kfv milk,
788: mtl_item_locations mil,
789: wms_license_plate_numbers wlpn,
790: wsh_delivery_details wdd2,
791: wsh_delivery_assignments_v wda,
792: mfg_lookups ml
793: WHERE milk.organization_id = p_org_id
846: FROM
847: wsh_delivery_assignments_v wda,
848: wsh_delivery_details wdd,
849: wsh_delivery_details wdd2,
850: wms_license_plate_numbers wlpn
851: WHERE wlpn.outermost_lpn_id = p_lpn_id
852: AND wlpn.organization_id = p_org_id
853: AND wlpn.lpn_context in ( 11,12)
854: AND wlpn.lpn_id = wdd.lpn_id
865: FROM
866: wsh_delivery_assignments_v wda,
867: wsh_delivery_details wdd,
868: wsh_delivery_details wdd2,
869: wms_license_plate_numbers wlpn
870: WHERE wlpn.outermost_lpn_id = p_lpn_id
871: AND wlpn.organization_id = p_org_id
872: AND wlpn.lpn_context in ( 11,12)
873: AND wlpn.lpn_id = wdd.lpn_id
911: x_task
912: FROM
913: wsh_delivery_assignments_v wda,
914: wsh_delivery_details wdd,
915: wms_license_plate_numbers wlpn,
916: mtl_item_locations mil,
917: wms_license_plate_numbers wlpno
918: WHERE wlpn.organization_id = p_org_id
919: AND wlpn.lpn_context in ( 11,12)
913: wsh_delivery_assignments_v wda,
914: wsh_delivery_details wdd,
915: wms_license_plate_numbers wlpn,
916: mtl_item_locations mil,
917: wms_license_plate_numbers wlpno
918: WHERE wlpn.organization_id = p_org_id
919: AND wlpn.lpn_context in ( 11,12)
920: AND wlpn.outermost_lpn_id = p_lpn_id
921: AND wlpno.lpn_id = p_lpn_id -- bug 2764736
979: x_loc,
980: x_project,
981: x_task
982: FROM
983: wms_license_plate_numbers wlpn,
984: wms_license_plate_numbers wlpno,
985: wsh_delivery_assignments_v wda,
986: wsh_delivery_details wdd,
987: wsh_delivery_details wdd2,
980: x_project,
981: x_task
982: FROM
983: wms_license_plate_numbers wlpn,
984: wms_license_plate_numbers wlpno,
985: wsh_delivery_assignments_v wda,
986: wsh_delivery_details wdd,
987: wsh_delivery_details wdd2,
988: mtl_item_locations mil
1023: x_sub,
1024: x_loc,
1025: x_project,
1026: x_task
1027: FROM wms_license_plate_numbers wlpn,
1028: mtl_item_locations mil
1029: WHERE wlpn.lpn_id = p_lpn_id
1030: AND wlpn.organization_id = p_org_id
1031: AND wlpn.locator_id = mil.inventory_location_id
1058: x_sub,
1059: x_loc,
1060: x_project,
1061: x_task
1062: FROM wms_license_plate_numbers wlpn,
1063: mtl_item_locations mil
1064: WHERE wlpn.lpn_id = p_lpn_id
1065: AND wlpn.organization_id = p_org_id
1066: AND wlpn.locator_id = mil.inventory_location_id
1111: x_loc,
1112: x_project,
1113: x_task
1114: FROM
1115: wms_license_plate_numbers wlpn,
1116: wms_license_plate_numbers wlpno,
1117: wsh_delivery_assignments_v wda,
1118: wsh_delivery_details wdd,
1119: mtl_item_locations mil
1112: x_project,
1113: x_task
1114: FROM
1115: wms_license_plate_numbers wlpn,
1116: wms_license_plate_numbers wlpno,
1117: wsh_delivery_assignments_v wda,
1118: wsh_delivery_details wdd,
1119: mtl_item_locations mil
1120: WHERE wlpn.outermost_lpn_id = p_lpn_id
1218:
1219: SELECT DISTINCT
1220: wlpn.outermost_lpn_id
1221: FROM
1222: wms_license_plate_numbers wlpn,
1223: mtl_item_locations_kfv milk
1224: WHERE wlpn.lpn_context in (11,12)
1225: AND wlpn.subinventory_code = x_sub
1226: AND wlpn.organization_id = p_org_id
1235:
1236: SELECT DISTINCT wlpn.outermost_lpn_id
1237: FROM
1238: wsh_delivery_details wdd,
1239: wms_license_plate_numbers wlpn,
1240: wsh_delivery_assignments_v wda,
1241: wsh_delivery_details wdd2
1242: WHERE wdd.source_header_number = x_order_number
1243: AND wdd.organization_id = p_org_id
1257:
1258: SELECT DISTINCT wlpn.outermost_lpn_id
1259: FROM
1260: wsh_delivery_details wdd,
1261: wms_license_plate_numbers wlpn,
1262: wsh_delivery_assignments_v wda
1263: WHERE wda.delivery_id = x_delivery_id
1264: AND wdd.delivery_detail_id = wda.parent_delivery_detail_id
1265: AND wdd.organization_id = p_org_id
1305: IF x_lpn IS NOT NULL THEN
1306:
1307: SELECT wlpn.lpn_id
1308: INTO l_lpn1_id
1309: FROM wms_license_plate_numbers wlpn
1310: WHERE wlpn.license_plate_number = x_lpn
1311: AND wlpn.lpn_context in ( 11,12)
1312: AND wlpn.organization_id = p_org_id;
1313:
1448:
1449: SELECT DISTINCT wlpn.outermost_lpn_id
1450: FROM
1451: wsh_delivery_details wdd,
1452: wms_license_plate_numbers wlpn,
1453: wsh_delivery_assignments_v wda
1454: WHERE wda.delivery_id = x_delivery_id
1455: AND wdd.delivery_detail_id = wda.parent_delivery_detail_id
1456: AND wdd.organization_id = p_org_id
1615:
1616: CURSOR lpn_csr
1617: IS
1618: SELECT wlpn.lpn_id
1619: FROM wms_license_plate_numbers wlpn
1620: WHERE wlpn.lpn_context = 11
1621: AND wlpn.subinventory_code = p_from_sub
1622: AND wlpn.locator_id = p_from_loc_id
1623: AND wlpn.organization_id = p_org_id
1768: SELECT wlc.inventory_item_id
1769: INTO l_item_id
1770: FROM
1771: wms_lpn_contents wlc,
1772: wms_license_plate_numbers wlpn
1773: WHERE wlc.parent_lpn_id = wlpn.lpn_id
1774: AND wlc.organization_id = wlpn.organization_id
1775: AND wlpn.outermost_lpn_id = l_lpn_id
1776: AND wlpn.organization_id = p_org_id
1984: FROM
1985: mtl_item_locations_kfv milk,
1986: mtl_item_locations mil,
1987: mfg_lookups ml,
1988: wms_license_plate_numbers wlpn
1989: WHERE milk.organization_id = p_org_id
1990: AND milk.alias = p_alias
1991: AND milk.subinventory_code = p_sub
1992: AND milk.project_id IS NULL
2005: AND NOT exists
2006: (
2007: SELECT 1
2008: FROM
2009: wms_license_plate_numbers wlpn2
2010: WHERE wlpn2.lpn_context <> 11
2011: AND wlpn2.organization_id = mil.organization_id
2012: AND wlpn2.subinventory_code = mil.subinventory_code
2013: AND wlpn2.locator_id = mil.inventory_location_id
2068: AND NOT exists
2069: (
2070: SELECT 1
2071: FROM
2072: wms_license_plate_numbers wlpn2
2073: WHERE wlpn2.lpn_context <> 11
2074: AND wlpn2.organization_id = mil.organization_id
2075: AND wlpn2.subinventory_code = mil.subinventory_code
2076: AND wlpn2.locator_id = mil.inventory_location_id
2118: FROM
2119: mtl_item_locations_kfv milk,
2120: mtl_item_locations mil,
2121: mfg_lookups ml,
2122: wms_license_plate_numbers wlpn
2123: WHERE milk.organization_id = p_org_id
2124: AND INV_PROJECT.GET_LOCSEGS(milk.inventory_location_id, milk.organization_id) LIKE p_loc
2125: AND milk.subinventory_code = p_sub
2126: AND milk.project_id IS NULL
2139: AND NOT exists
2140: (
2141: SELECT 1
2142: FROM
2143: wms_license_plate_numbers wlpn2
2144: WHERE wlpn2.lpn_context <> 11
2145: AND wlpn2.organization_id = mil.organization_id
2146: AND wlpn2.subinventory_code = mil.subinventory_code
2147: AND wlpn2.locator_id = mil.inventory_location_id
2202: AND NOT exists
2203: (
2204: SELECT 1
2205: FROM
2206: wms_license_plate_numbers wlpn2
2207: WHERE wlpn2.lpn_context <> 11
2208: AND wlpn2.organization_id = mil.organization_id
2209: AND wlpn2.subinventory_code = mil.subinventory_code
2210: AND wlpn2.locator_id = mil.inventory_location_id
2244: inv_project.get_locsegs(milk.inventory_location_id, milk.organization_id) concatenated_segments,
2245: milk.subinventory_code,
2246: 11 lpn_context
2247: FROM
2248: wms_license_plate_numbers wlpn,
2249: mtl_item_locations_kfv milk
2250: WHERE wlpn.license_plate_number LIKE p_lpn
2251: AND wlpn.locator_id <> p_from_loc_id -- Make sure that we don't choose the TO lpn FROM the FROM locator
2252: AND milk.organization_id = p_org_id
2266: NULL,
2267: NULL,
2268: 5 lpn_context
2269: FROM
2270: wms_license_plate_numbers wlpn
2271: WHERE wlpn.license_plate_number LIKE p_lpn
2272: AND wlpn.lpn_context = 5 -- Defined but not used
2273: AND wlpn.organization_id = p_org_id)
2274: ORDER BY license_plate_number;
2461:
2462: SELECT 1
2463: INTO l_count
2464: FROM
2465: wms_license_plate_numbers wlpn,
2466: -- wms_dispatched_tasks_history wdth, Commented by Bug#4337112.
2467: wsh_delivery_details wdd2,
2468: wsh_delivery_assignments_v wda
2469: WHERE wda.delivery_id = p_delivery_id
2704: l_from_sub,
2705: l_from_loc
2706: FROM
2707: wms_dispatched_tasks_history wdth,
2708: wms_license_plate_numbers wlpn
2709: WHERE wlpn.organization_id = p_org_id
2710: AND wlpn.outermost_lpn_id = p_lpn_id
2711: AND wdth.organization_id = p_org_id
2712: AND wdth.transfer_lpn_id = wlpn.lpn_id
2730: Wms_Dispatched_Tasks_History Wdth,
2731: Wsh_Delivery_Details_Ob_Grp_v Wdd,
2732: Wsh_Delivery_Assignments Wda,
2733: Wsh_Delivery_Details_Ob_Grp_v Wddc,
2734: Wms_License_Plate_Numbers Wlpn
2735: Where 1 = 1
2736: And Decode(Mmt.Transfer_Transaction_Id,
2737: Null,
2738: 0,
2787: --In case of Staging move for a splitted LPN, we will not have WDTH.
2788: BEGIN
2789: SELECT wlpn.subinventory_code, wlpn.locator_id
2790: INTO l_from_sub, l_from_loc
2791: FROM wms_license_plate_numbers wlpn
2792: WHERE wlpn.lpn_id = p_lpn_id; --Bug#4337112. Changed outermost_lpn_id to lpn_id
2793:
2794: --Populate the suggested sub and loc
2795: l_orig_sub := l_from_sub;
2831: SELECT wlc.inventory_item_id
2832: INTO l_item_id
2833: FROM
2834: wms_lpn_contents wlc,
2835: wms_license_plate_numbers wlpn
2836: WHERE wlc.parent_lpn_id = wlpn.lpn_id
2837: AND wlc.organization_id = wlpn.organization_id
2838: AND wlpn.outermost_lpn_id = p_lpn_id
2839: AND wlpn.organization_id = p_org_id
2957:
2958: /* bug 3424353
2959: * change the staus of the inner ones also to packing
2960:
2961: UPDATE wms_license_plate_numbers
2962: SET lpn_context = wms_container_pub.lpn_context_packing
2963: WHERE lpn_id = p_lpn_id;
2964: */
2965: