[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
418: FROM
419: wsh_new_deliveries wnd,
420: wsh_delivery_assignments_v wda,
421: wsh_delivery_details wdd,
422: wms_license_plate_numbers wlpn
423: WHERE wda.parent_delivery_detail_id = wdd.delivery_detail_id
424: AND wda.delivery_id = wnd.delivery_id
425: AND wnd.name LIKE p_delivery_name
426: AND wnd.organization_id = p_organization_id
481: wsh_delivery_assignments_v wda,
482: -- ra_customers c, -- Commented for Bug# 4579790
483: hz_parties party, -- Added for Bug# 4579790
484: hz_cust_accounts cust_acct, -- Added for Bug# 4579790
485: wms_license_plate_numbers wlpn,
486: wsh_delivery_details wdd2
487: WHERE wdd.customer_id = cust_acct.cust_account_id -- Added for Bug# 4579790
488: --wdd.customer_id = c.customer_id -- Commented for Bug# 4579790
489: AND cust_acct.party_id = party.party_id -- Added for Bug# 4579790
545: wsh_delivery_assignments_v wda,
546: -- ra_customers c, -- Commented for Bug# 4579790
547: hz_parties party, -- Added for Bug# 4579790
548: hz_cust_accounts cust_acct, -- Added for Bug# 4579790
549: wms_license_plate_numbers wlpn,
550: wsh_delivery_details wdd2
551: WHERE wdd.customer_id = cust_acct.cust_account_id -- Added for Bug# 4579790
552: --wdd.customer_id = c.customer_id -- Commented for Bug# 4579790
553: AND cust_acct.party_id = party.party_id -- Added for Bug# 4579790
660: mil.picking_order
661: FROM
662: mtl_item_locations_kfv milk,
663: mtl_item_locations mil,
664: wms_license_plate_numbers wlpn,
665: mfg_lookups ml
666: WHERE milk.organization_id = p_org_id
667: AND milk.alias = p_alias
668: AND milk.subinventory_code = p_sub
692: mil.picking_order
693: FROM
694: mtl_item_locations_kfv milk,
695: mtl_item_locations mil,
696: wms_license_plate_numbers wlpn,
697: wsh_delivery_details wdd2,
698: wsh_delivery_assignments_v wda,
699: mfg_lookups ml
700: WHERE milk.organization_id = p_org_id
748: mil.picking_order
749: FROM
750: mtl_item_locations_kfv milk,
751: mtl_item_locations mil,
752: wms_license_plate_numbers wlpn,
753: mfg_lookups ml
754: WHERE milk.organization_id = p_org_id
755: AND INV_PROJECT.GET_LOCSEGS(milk.inventory_location_id, milk.organization_id) LIKE p_loc -- bug 2769126
756: AND milk.subinventory_code = p_sub
780: mil.picking_order
781: FROM
782: mtl_item_locations_kfv milk,
783: mtl_item_locations mil,
784: wms_license_plate_numbers wlpn,
785: wsh_delivery_details wdd2,
786: wsh_delivery_assignments_v wda,
787: mfg_lookups ml
788: WHERE milk.organization_id = p_org_id
841: FROM
842: wsh_delivery_assignments_v wda,
843: wsh_delivery_details wdd,
844: wsh_delivery_details wdd2,
845: wms_license_plate_numbers wlpn
846: WHERE wlpn.outermost_lpn_id = p_lpn_id
847: AND wlpn.organization_id = p_org_id
848: AND wlpn.lpn_context in ( 11,12)
849: AND wlpn.lpn_id = wdd.lpn_id
860: FROM
861: wsh_delivery_assignments_v wda,
862: wsh_delivery_details wdd,
863: wsh_delivery_details wdd2,
864: wms_license_plate_numbers wlpn
865: WHERE wlpn.outermost_lpn_id = p_lpn_id
866: AND wlpn.organization_id = p_org_id
867: AND wlpn.lpn_context in ( 11,12)
868: AND wlpn.lpn_id = wdd.lpn_id
906: x_task
907: FROM
908: wsh_delivery_assignments_v wda,
909: wsh_delivery_details wdd,
910: wms_license_plate_numbers wlpn,
911: mtl_item_locations mil,
912: wms_license_plate_numbers wlpno
913: WHERE wlpn.organization_id = p_org_id
914: AND wlpn.lpn_context in ( 11,12)
908: wsh_delivery_assignments_v wda,
909: wsh_delivery_details wdd,
910: wms_license_plate_numbers wlpn,
911: mtl_item_locations mil,
912: wms_license_plate_numbers wlpno
913: WHERE wlpn.organization_id = p_org_id
914: AND wlpn.lpn_context in ( 11,12)
915: AND wlpn.outermost_lpn_id = p_lpn_id
916: AND wlpno.lpn_id = p_lpn_id -- bug 2764736
974: x_loc,
975: x_project,
976: x_task
977: FROM
978: wms_license_plate_numbers wlpn,
979: wms_license_plate_numbers wlpno,
980: wsh_delivery_assignments_v wda,
981: wsh_delivery_details wdd,
982: wsh_delivery_details wdd2,
975: x_project,
976: x_task
977: FROM
978: wms_license_plate_numbers wlpn,
979: wms_license_plate_numbers wlpno,
980: wsh_delivery_assignments_v wda,
981: wsh_delivery_details wdd,
982: wsh_delivery_details wdd2,
983: mtl_item_locations mil
1018: x_sub,
1019: x_loc,
1020: x_project,
1021: x_task
1022: FROM wms_license_plate_numbers wlpn,
1023: mtl_item_locations mil
1024: WHERE wlpn.lpn_id = p_lpn_id
1025: AND wlpn.organization_id = p_org_id
1026: AND wlpn.locator_id = mil.inventory_location_id
1053: x_sub,
1054: x_loc,
1055: x_project,
1056: x_task
1057: FROM wms_license_plate_numbers wlpn,
1058: mtl_item_locations mil
1059: WHERE wlpn.lpn_id = p_lpn_id
1060: AND wlpn.organization_id = p_org_id
1061: AND wlpn.locator_id = mil.inventory_location_id
1106: x_loc,
1107: x_project,
1108: x_task
1109: FROM
1110: wms_license_plate_numbers wlpn,
1111: wms_license_plate_numbers wlpno,
1112: wsh_delivery_assignments_v wda,
1113: wsh_delivery_details wdd,
1114: mtl_item_locations mil
1107: x_project,
1108: x_task
1109: FROM
1110: wms_license_plate_numbers wlpn,
1111: wms_license_plate_numbers wlpno,
1112: wsh_delivery_assignments_v wda,
1113: wsh_delivery_details wdd,
1114: mtl_item_locations mil
1115: WHERE wlpn.outermost_lpn_id = p_lpn_id
1213:
1214: SELECT DISTINCT
1215: wlpn.outermost_lpn_id
1216: FROM
1217: wms_license_plate_numbers wlpn,
1218: mtl_item_locations_kfv milk
1219: WHERE wlpn.lpn_context in (11,12)
1220: AND wlpn.subinventory_code = x_sub
1221: AND wlpn.organization_id = p_org_id
1230:
1231: SELECT DISTINCT wlpn.outermost_lpn_id
1232: FROM
1233: wsh_delivery_details wdd,
1234: wms_license_plate_numbers wlpn,
1235: wsh_delivery_assignments_v wda,
1236: wsh_delivery_details wdd2
1237: WHERE wdd.source_header_number = x_order_number
1238: AND wdd.organization_id = p_org_id
1252:
1253: SELECT DISTINCT wlpn.outermost_lpn_id
1254: FROM
1255: wsh_delivery_details wdd,
1256: wms_license_plate_numbers wlpn,
1257: wsh_delivery_assignments_v wda
1258: WHERE wda.delivery_id = x_delivery_id
1259: AND wdd.delivery_detail_id = wda.parent_delivery_detail_id
1260: AND wdd.organization_id = p_org_id
1300: IF x_lpn IS NOT NULL THEN
1301:
1302: SELECT wlpn.lpn_id
1303: INTO l_lpn1_id
1304: FROM wms_license_plate_numbers wlpn
1305: WHERE wlpn.license_plate_number = x_lpn
1306: AND wlpn.lpn_context in ( 11,12)
1307: AND wlpn.organization_id = p_org_id;
1308:
1443:
1444: SELECT DISTINCT wlpn.outermost_lpn_id
1445: FROM
1446: wsh_delivery_details wdd,
1447: wms_license_plate_numbers wlpn,
1448: wsh_delivery_assignments_v wda
1449: WHERE wda.delivery_id = x_delivery_id
1450: AND wdd.delivery_detail_id = wda.parent_delivery_detail_id
1451: AND wdd.organization_id = p_org_id
1610:
1611: CURSOR lpn_csr
1612: IS
1613: SELECT wlpn.lpn_id
1614: FROM wms_license_plate_numbers wlpn
1615: WHERE wlpn.lpn_context = 11
1616: AND wlpn.subinventory_code = p_from_sub
1617: AND wlpn.locator_id = p_from_loc_id
1618: AND wlpn.organization_id = p_org_id
1763: SELECT wlc.inventory_item_id
1764: INTO l_item_id
1765: FROM
1766: wms_lpn_contents wlc,
1767: wms_license_plate_numbers wlpn
1768: WHERE wlc.parent_lpn_id = wlpn.lpn_id
1769: AND wlc.organization_id = wlpn.organization_id
1770: AND wlpn.outermost_lpn_id = l_lpn_id
1771: AND wlpn.organization_id = p_org_id
1979: FROM
1980: mtl_item_locations_kfv milk,
1981: mtl_item_locations mil,
1982: mfg_lookups ml,
1983: wms_license_plate_numbers wlpn
1984: WHERE milk.organization_id = p_org_id
1985: AND milk.alias = p_alias
1986: AND milk.subinventory_code = p_sub
1987: AND milk.project_id IS NULL
2000: AND NOT exists
2001: (
2002: SELECT 1
2003: FROM
2004: wms_license_plate_numbers wlpn2
2005: WHERE wlpn2.lpn_context <> 11
2006: AND wlpn2.organization_id = mil.organization_id
2007: AND wlpn2.subinventory_code = mil.subinventory_code
2008: AND wlpn2.locator_id = mil.inventory_location_id
2063: AND NOT exists
2064: (
2065: SELECT 1
2066: FROM
2067: wms_license_plate_numbers wlpn2
2068: WHERE wlpn2.lpn_context <> 11
2069: AND wlpn2.organization_id = mil.organization_id
2070: AND wlpn2.subinventory_code = mil.subinventory_code
2071: AND wlpn2.locator_id = mil.inventory_location_id
2113: FROM
2114: mtl_item_locations_kfv milk,
2115: mtl_item_locations mil,
2116: mfg_lookups ml,
2117: wms_license_plate_numbers wlpn
2118: WHERE milk.organization_id = p_org_id
2119: AND INV_PROJECT.GET_LOCSEGS(milk.inventory_location_id, milk.organization_id) LIKE p_loc
2120: AND milk.subinventory_code = p_sub
2121: AND milk.project_id IS NULL
2134: AND NOT exists
2135: (
2136: SELECT 1
2137: FROM
2138: wms_license_plate_numbers wlpn2
2139: WHERE wlpn2.lpn_context <> 11
2140: AND wlpn2.organization_id = mil.organization_id
2141: AND wlpn2.subinventory_code = mil.subinventory_code
2142: AND wlpn2.locator_id = mil.inventory_location_id
2197: AND NOT exists
2198: (
2199: SELECT 1
2200: FROM
2201: wms_license_plate_numbers wlpn2
2202: WHERE wlpn2.lpn_context <> 11
2203: AND wlpn2.organization_id = mil.organization_id
2204: AND wlpn2.subinventory_code = mil.subinventory_code
2205: AND wlpn2.locator_id = mil.inventory_location_id
2239: inv_project.get_locsegs(milk.inventory_location_id, milk.organization_id) concatenated_segments,
2240: milk.subinventory_code,
2241: 11 lpn_context
2242: FROM
2243: wms_license_plate_numbers wlpn,
2244: mtl_item_locations_kfv milk
2245: WHERE wlpn.license_plate_number LIKE p_lpn
2246: AND wlpn.locator_id <> p_from_loc_id -- Make sure that we don't choose the TO lpn FROM the FROM locator
2247: AND milk.organization_id = p_org_id
2261: NULL,
2262: NULL,
2263: 5 lpn_context
2264: FROM
2265: wms_license_plate_numbers wlpn
2266: WHERE wlpn.license_plate_number LIKE p_lpn
2267: AND wlpn.lpn_context = 5 -- Defined but not used
2268: AND wlpn.organization_id = p_org_id)
2269: ORDER BY license_plate_number;
2456:
2457: SELECT 1
2458: INTO l_count
2459: FROM
2460: wms_license_plate_numbers wlpn,
2461: -- wms_dispatched_tasks_history wdth, Commented by Bug#4337112.
2462: wsh_delivery_details wdd2,
2463: wsh_delivery_assignments_v wda
2464: WHERE wda.delivery_id = p_delivery_id
2695: l_from_sub,
2696: l_from_loc
2697: FROM
2698: wms_dispatched_tasks_history wdth,
2699: wms_license_plate_numbers wlpn
2700: WHERE wlpn.organization_id = p_org_id
2701: AND wlpn.outermost_lpn_id = p_lpn_id
2702: AND wdth.organization_id = p_org_id
2703: AND wdth.transfer_lpn_id = wlpn.lpn_id
2716: --In case of Staging move for a splitted LPN, we will not have WDTH.
2717: BEGIN
2718: SELECT wlpn.subinventory_code, wlpn.locator_id
2719: INTO l_from_sub, l_from_loc
2720: FROM wms_license_plate_numbers wlpn
2721: WHERE wlpn.lpn_id = p_lpn_id; --Bug#4337112. Changed outermost_lpn_id to lpn_id
2722:
2723: --Populate the suggested sub and loc
2724: l_orig_sub := l_from_sub;
2737: SELECT wlc.inventory_item_id
2738: INTO l_item_id
2739: FROM
2740: wms_lpn_contents wlc,
2741: wms_license_plate_numbers wlpn
2742: WHERE wlc.parent_lpn_id = wlpn.lpn_id
2743: AND wlc.organization_id = wlpn.organization_id
2744: AND wlpn.outermost_lpn_id = p_lpn_id
2745: AND wlpn.organization_id = p_org_id
2863:
2864: /* bug 3424353
2865: * change the staus of the inner ones also to packing
2866:
2867: UPDATE wms_license_plate_numbers
2868: SET lpn_context = wms_container_pub.lpn_context_packing
2869: WHERE lpn_id = p_lpn_id;
2870: */
2871: