[Home] [Help]
178: ' milk.concatenated_segments, ' ||
179: ' wlpn.lpn_context ' ||
180: 'FROM wms_license_plate_numbers wlpn, ' ||
181: ' mtl_item_locations_kfv milk, ' ||
182: ' wms_lpn_contents wlc ' ||
183: 'WHERE wlpn.organization_id = milk.organization_id (+) ' ||
184: ' AND wlpn.locator_id = milk.inventory_location_id(+) ' ||
185: ' AND wlc.parent_lpn_id (+) = wlpn.lpn_id ';
186: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
231: ' INV_PROJECT.GET_TASK_NUMBER, ' ||
232: ' wlpn.lpn_context ' ||
233: 'FROM wms_license_plate_numbers wlpn, ' ||
234: ' mtl_item_locations milk, ' ||
235: ' wms_lpn_contents wlc ' ||
236: 'WHERE wlpn.organization_id = milk.organization_id (+) ' ||
237: ' AND wlpn.locator_id = milk.inventory_location_id(+) ' ||
238: ' AND wlc.parent_lpn_id (+) = wlpn.lpn_id ';
239: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
282: milk.concatenated_segments,
283: wlpn.lpn_context
284: FROM wms_license_plate_numbers wlpn,
285: mtl_item_locations_kfv milk,
286: wms_lpn_contents wlc
287: WHERE wlpn.organization_id = To_number(p_organization_id)
288: AND wlpn.organization_id = milk.organization_id (+)
289: AND wlpn.locator_id = milk.inventory_location_id(+)
290: AND wlc.parent_lpn_id (+) = wlpn.lpn_id
327: milk.concatenated_segments,
328: wlpn.lpn_context
329: FROM wms_license_plate_numbers wlpn,
330: mtl_item_locations_kfv milk,
331: wms_lpn_contents wlc
332: WHERE wlpn.organization_id = milk.organization_id (+)
333: AND wlpn.locator_id = milk.inventory_location_id(+)
334: AND wlc.parent_lpn_id (+) = wlpn.lpn_id
335: AND (lpn_context = 8 OR lpn_context = 5)
430: milk.concatenated_segments,
431: wlpn.lpn_context
432: FROM wms_license_plate_numbers wlpn,
433: mtl_item_locations_kfv milk,
434: wms_lpn_contents wlc
435: WHERE wlpn.organization_id = milk.organization_id (+)
436: AND wlpn.locator_id = milk.inventory_location_id(+)
437: AND wlc.parent_lpn_id (+) = wlpn.lpn_id
438: AND wlpn.license_plate_number LIKE l_lpn
494: milk.concatenated_segments,
495: wlpn.lpn_context
496: FROM wms_license_plate_numbers wlpn,
497: mtl_item_locations_kfv milk,
498: wms_lpn_contents wlc
499: WHERE wlpn.organization_id = milk.organization_id (+)
500: AND wlpn.locator_id = milk.inventory_location_id(+)
501: AND wlc.parent_lpn_id (+) = wlpn.lpn_id
502: AND wlpn.license_plate_number LIKE l_lpn
555: milk.concatenated_segments,
556: wlpn.lpn_context
557: FROM wms_license_plate_numbers wlpn,
558: mtl_item_locations_kfv milk,
559: wms_lpn_contents wlc
560: WHERE wlpn.organization_id = milk.organization_id (+)
561: AND wlpn.locator_id = milk.inventory_location_id(+)
562: AND wlc.parent_lpn_id (+) = wlpn.lpn_id
563: AND wlpn.license_plate_number LIKE l_lpn
771: ' milk.concatenated_segments, ' ||
772: ' wlpn.lpn_context ' ||
773: 'FROM wms_license_plate_numbers wlpn, ' ||
774: ' mtl_item_locations_kfv milk, ' ||
775: ' wms_lpn_contents wlc, ' ||
776: ' mtl_serial_numbers msn, ' ||
777: ' mtl_serial_numbers_temp msnt ' ||
778: 'WHERE wlpn.organization_id = milk.organization_id (+) ' ||
779: ' AND wlpn.locator_id = milk.inventory_location_id(+) ' ||
829: milk.concatenated_segments,
830: wlpn.lpn_context
831: FROM wms_license_plate_numbers wlpn,
832: mtl_item_locations_kfv milk,
833: wms_lpn_contents wlc,
834: mtl_serial_numbers msn,
835: mtl_serial_numbers_temp msnt
836: WHERE wlpn.organization_id = milk.organization_id (+)
837: AND wlpn.locator_id = milk.inventory_location_id(+)
866: milk.concatenated_segments,
867: wlpn.lpn_context
868: FROM wms_license_plate_numbers wlpn,
869: mtl_item_locations_kfv milk,
870: wms_lpn_contents wlc,
871: mtl_serial_numbers msn,
872: mtl_serial_numbers_temp msnt,
873: mtl_transaction_lots_temp mtlt,
874: mtl_material_transactions_temp mmtt
932: milk.concatenated_segments,
933: wlpn.lpn_context
934: FROM wms_license_plate_numbers wlpn,
935: mtl_item_locations_kfv milk,
936: wms_lpn_contents wlc,
937: mtl_serial_numbers msn,
938: mtl_serial_numbers_temp msnt
939: WHERE wlpn.organization_id = milk.organization_id (+)
940: AND wlpn.locator_id = milk.inventory_location_id(+)
966: milk.concatenated_segments,
967: wlpn.lpn_context
968: FROM wms_license_plate_numbers wlpn,
969: mtl_item_locations_kfv milk,
970: wms_lpn_contents wlc,
971: mtl_serial_numbers msn,
972: mtl_serial_numbers_temp msnt,
973: mtl_transaction_lots_temp mtlt,
974: mtl_material_transactions_temp mmtt
1029: milk.concatenated_segments,
1030: wlpn.lpn_context
1031: FROM wms_license_plate_numbers wlpn,
1032: mtl_item_locations_kfv milk,
1033: wms_lpn_contents wlc,
1034: mtl_serial_numbers msn,
1035: mtl_serial_numbers_temp msnt
1036: WHERE wlpn.organization_id = milk.organization_id (+)
1037: AND wlpn.locator_id = milk.inventory_location_id(+)
1064: milk.concatenated_segments,
1065: wlpn.lpn_context
1066: FROM wms_license_plate_numbers wlpn,
1067: mtl_item_locations_kfv milk,
1068: wms_lpn_contents wlc,
1069: mtl_serial_numbers msn,
1070: mtl_serial_numbers_temp msnt,
1071: mtl_transaction_lots_temp mtlt,
1072: mtl_material_transactions_temp mmtt
2727: OPEN x_lpn_lov FOR
2728: select lpnc.parent_lpn_id lpn_id,
2729: lpn.license_plate_number lpn,
2730: sum(lpnc.quantity) quantity
2731: from wms_lpn_contents lpnc, wms_license_plate_numbers lpn
2732: where lpn.organization_id = p_organization_id
2733: and lpnc.inventory_item_id = p_inv_item_id
2734: and lpnc.parent_lpn_id = lpn.lpn_id
2735: and nvl(lpn.SUBINVENTORY_CODE,'@@@') = nvl(p_subinventory_code,'@@@')
2736: and nvl(lpn.LOCATOR_ID, 0) = nvl(p_locator_id, 0)
2737: and lpn.license_plate_number like (p_lpn)
2738: and lpn.lpn_context = WMS_Container_PUB.LPN_CONTEXT_INV
2739: and not exists (select null
2740: from wms_lpn_contents
2741: where parent_lpn_id = lpnc.parent_lpn_id
2742: and inventory_item_id <> lpnc.inventory_item_id)
2743: group by lpnc.parent_lpn_id, lpn.license_plate_number
2744: having sum(lpnc.quantity) <= p_qty;
2939: WHERE lpn.lpn_context IN (6, 7) -- context for vendor LPN
2940: AND rsh.shipment_header_id = p_shipment_header_id
2941: AND (lpn.source_header_id = rsh.shipment_header_id
2942: OR lpn.source_name = rsh.shipment_num)
2943: AND EXISTS (SELECT parent_lpn_id FROM wms_lpn_contents WHERE parent_lpn_id = lpn.lpn_id)
2944: ) count_row
2945: WHERE lpn.license_plate_number LIKE (p_lpn)
2946: AND lpn.lpn_context IN (6, 7) -- context for vendor LPN
2947: AND rsh.shipment_header_id = p_shipment_header_id
2946: AND lpn.lpn_context IN (6, 7) -- context for vendor LPN
2947: AND rsh.shipment_header_id = p_shipment_header_id
2948: AND (lpn.source_header_id = rsh.shipment_header_id
2949: OR lpn.source_name = rsh.shipment_num)
2950: AND EXISTS (SELECT parent_lpn_id FROM wms_lpn_contents WHERE parent_lpn_id = lpn.lpn_id);
2951:
2952: ELSE -- for if p_shipment_header_id is not null
2953: OPEN x_lpn_lov FOR
2954: SELECT
2978: WHERE lpn.lpn_context IN (6, 7) -- context for vendor LPN
2979: --AND rsh.shipment_header_id = Nvl(To_number(p_shipment_header_id), rsh.shipment_header_id)
2980: AND (lpn.source_header_id = rsh.shipment_header_id
2981: OR lpn.source_name = rsh.shipment_num)
2982: AND EXISTS (SELECT parent_lpn_id FROM wms_lpn_contents WHERE parent_lpn_id = lpn.lpn_id)
2983: ) count_row
2984: WHERE lpn.license_plate_number LIKE (p_lpn)
2985: AND lpn.lpn_context IN (6, 7) -- context for vendor LPN
2986: --AND rsh.shipment_header_id = Nvl(To_number(p_shipment_header_id), rsh.shipment_header_id)
2985: AND lpn.lpn_context IN (6, 7) -- context for vendor LPN
2986: --AND rsh.shipment_header_id = Nvl(To_number(p_shipment_header_id), rsh.shipment_header_id)
2987: AND (lpn.source_header_id = rsh.shipment_header_id
2988: OR lpn.source_name = rsh.shipment_num)
2989: AND EXISTS (SELECT parent_lpn_id FROM wms_lpn_contents WHERE parent_lpn_id = lpn.lpn_id);
2990:
2991: END IF; -- end if for if p_shipment_header_id is not null
2992: ELSE
2993: IF p_shipment_header_id IS NOT NULL THEN
3019: WHERE lpn.lpn_context IN (6, 7) -- context for vendor LPN
3020: AND rsh.shipment_header_id = p_shipment_header_id
3021: AND (lpn.source_header_id = rsh.shipment_header_id
3022: OR lpn.source_name = rsh.shipment_num)
3023: AND EXISTS (SELECT parent_lpn_id FROM wms_lpn_contents WHERE parent_lpn_id = lpn.lpn_id)
3024: ) count_row
3025: WHERE lpn.license_plate_number LIKE (p_lpn)
3026: AND lpn.lpn_context IN (6, 7) -- context for vendor LPN
3027: AND rsh.shipment_header_id = p_shipment_header_id
3026: AND lpn.lpn_context IN (6, 7) -- context for vendor LPN
3027: AND rsh.shipment_header_id = p_shipment_header_id
3028: AND (lpn.source_header_id = rsh.shipment_header_id
3029: OR lpn.source_name = rsh.shipment_num)
3030: AND EXISTS (SELECT parent_lpn_id FROM wms_lpn_contents WHERE parent_lpn_id = lpn.lpn_id
3031: and inventory_item_id = p_inventory_item_id );
3032:
3033: ELSE -- if p_shipment_header_id is null
3034: OPEN x_lpn_lov FOR
3059: WHERE lpn.lpn_context IN (6, 7) -- context for vendor LPN
3060: --AND rsh.shipment_header_id = Nvl(To_number(p_shipment_header_id), rsh.shipment_header_id)
3061: AND (lpn.source_header_id = rsh.shipment_header_id
3062: OR lpn.source_name = rsh.shipment_num)
3063: AND EXISTS (SELECT parent_lpn_id FROM wms_lpn_contents WHERE parent_lpn_id = lpn.lpn_id)
3064: ) count_row
3065: WHERE lpn.license_plate_number LIKE (p_lpn)
3066: AND lpn.lpn_context IN (6, 7) -- context for vendor LPN
3067: --AND rsh.shipment_header_id = Nvl(To_number(p_shipment_header_id), rsh.shipment_header_id)
3066: AND lpn.lpn_context IN (6, 7) -- context for vendor LPN
3067: --AND rsh.shipment_header_id = Nvl(To_number(p_shipment_header_id), rsh.shipment_header_id)
3068: AND (lpn.source_header_id = rsh.shipment_header_id
3069: OR lpn.source_name = rsh.shipment_num)
3070: AND EXISTS (SELECT parent_lpn_id FROM wms_lpn_contents WHERE parent_lpn_id = lpn.lpn_id
3071: and inventory_item_id = p_inventory_item_id );
3072: END IF; -- else part of if p_shipment_header_id is not null
3073: END IF; -- else part of if p_inventory_item_id is null
3074: END IF;
3114: INV_PROJECT.GET_PROJECT_NUMBER,
3115: INV_PROJECT.GET_TASK_ID,
3116: INV_PROJECT.GET_TASK_NUMBER
3117: FROM wms_license_plate_numbers wlpn,
3118: wms_lpn_contents wlc,
3119: mtl_item_locations mil
3120: WHERE (mil.inventory_location_id = wlpn.locator_id
3121: AND wlpn.locator_id IS NOT NULL)
3122: AND ( (wlc.revision = p_revision
4189: AND wlpn.lpn_context = 6
4190: AND wlpn.organization_id = wdd.organization_id
4191: -- Nested LPN changes
4192: AND EXISTS (SELECT parent_lpn_id
4193: FROM wms_lpn_contents wlc
4194: WHERE parent_lpn_id = wlpn.lpn_id)
4195: AND wlpn.lpn_id = NVL(wdd.lpn_id, -9999)) count_row
4196: WHERE wdd.source_header_id = l_order_header_id
4197: AND wdd.delivery_detail_id = wda.delivery_detail_id
4200: AND wlpn.organization_id = wdd1.organization_id
4201: AND wlpn.lpn_id = NVL(wdd1.lpn_id, -9999)
4202: AND wlpn.license_plate_number LIKE (p_lpn)
4203: -- Nested LPN changes
4204: AND EXISTS (SELECT parent_lpn_id FROM wms_lpn_contents wlc WHERE parent_lpn_id = wlpn.lpn_id)
4205: ORDER BY wlpn.license_plate_number;
4206: Else
4207: OPEN x_lpn_lov FOR
4208: SELECT distinct wlpn.license_plate_number
4224: AND wlpn.lpn_context = 6
4225: AND wlpn.organization_id = wdd.organization_id
4226: -- Nested LPN changes
4227: AND EXISTS (SELECT parent_lpn_id
4228: FROM wms_lpn_contents wlc
4229: WHERE parent_lpn_id = wlpn.lpn_id)
4230: AND wlpn.lpn_id = NVL(wdd.lpn_id, -9999)) count_row
4231: WHERE wdd.source_header_id = l_order_header_id
4232: AND wdd.delivery_detail_id = wda.delivery_detail_id
4235: AND wlpn.organization_id = wdd1.organization_id
4236: AND wlpn.lpn_id = NVL(wdd1.lpn_id, -9999)
4237: AND wlpn.license_plate_number LIKE (p_lpn)
4238: -- Nested LPN changes
4239: AND EXISTS (SELECT parent_lpn_id FROM wms_lpn_contents wlc WHERE parent_lpn_id = wlpn.lpn_id
4240: and wlc.inventory_item_id = p_inventory_item_id)
4241: ORDER BY wlpn.license_plate_number;
4242: End if;
4243: END IF;
4454: AND wlpn.lpn_id <> p_lpn_id
4455: AND (wlpn.lpn_context = WMS_CONTAINER_PUB.LPN_CONTEXT_PREGENERATED
4456: OR (wlpn.lpn_context = p_lpn_context
4457: AND ( (NOT EXISTS (SELECT 'LPN_HAS_MATERIAL'
4458: FROM wms_lpn_contents
4459: WHERE parent_lpn_id IN (SELECT wlpn1.lpn_id
4460: FROM
4461: wms_license_plate_numbers wlpn1
4462: START WITH