[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
2003: wlpn.content_volume_uom_code,
2004: NVL(wlpn.content_volume, 0),
2005: wlpn.lpn_context
2006: FROM wms_license_plate_numbers wlpn,
2007: wms_lpn_contents wlc -- Bug 14345460
2008: WHERE wlpn.organization_id = p_org_id
2009: AND NOT wlpn.lpn_id = NVL(TO_NUMBER(p_not_lpn_id), -999)
2010: AND NVL(wlpn.parent_lpn_id, 0) = NVL(TO_NUMBER(p_parent_lpn_id), NVL(wlpn.parent_lpn_id, 0))
2011: AND wlpn.license_plate_number LIKE (p_lpn)
2728: OPEN x_lpn_lov FOR
2729: select lpnc.parent_lpn_id lpn_id,
2730: lpn.license_plate_number lpn,
2731: sum(lpnc.quantity) quantity
2732: from wms_lpn_contents lpnc, wms_license_plate_numbers lpn
2733: where lpn.organization_id = p_organization_id
2734: and lpnc.inventory_item_id = p_inv_item_id
2735: and lpnc.parent_lpn_id = lpn.lpn_id
2736: and nvl(lpn.SUBINVENTORY_CODE,'@@@') = nvl(p_subinventory_code,'@@@')
2737: and nvl(lpn.LOCATOR_ID, 0) = nvl(p_locator_id, 0)
2738: and lpn.license_plate_number like (p_lpn)
2739: and lpn.lpn_context = WMS_Container_PUB.LPN_CONTEXT_INV
2740: and not exists (select null
2741: from wms_lpn_contents
2742: where parent_lpn_id = lpnc.parent_lpn_id
2743: and inventory_item_id <> lpnc.inventory_item_id)
2744: group by lpnc.parent_lpn_id, lpn.license_plate_number
2745: having sum(lpnc.quantity) <= p_qty;
2940: WHERE lpn.lpn_context IN (6, 7) -- context for vendor LPN
2941: AND rsh.shipment_header_id = p_shipment_header_id
2942: AND (lpn.source_header_id = rsh.shipment_header_id
2943: OR lpn.source_name = rsh.shipment_num)
2944: AND EXISTS (SELECT parent_lpn_id FROM wms_lpn_contents WHERE parent_lpn_id = lpn.lpn_id)
2945: ) count_row
2946: WHERE lpn.license_plate_number LIKE (p_lpn)
2947: AND lpn.lpn_context IN (6, 7) -- context for vendor LPN
2948: AND rsh.shipment_header_id = p_shipment_header_id
2947: AND lpn.lpn_context IN (6, 7) -- context for vendor LPN
2948: AND rsh.shipment_header_id = p_shipment_header_id
2949: AND (lpn.source_header_id = rsh.shipment_header_id
2950: OR lpn.source_name = rsh.shipment_num)
2951: AND EXISTS (SELECT parent_lpn_id FROM wms_lpn_contents WHERE parent_lpn_id = lpn.lpn_id);
2952:
2953: ELSE -- for if p_shipment_header_id is not null
2954: OPEN x_lpn_lov FOR
2955: SELECT
2979: WHERE lpn.lpn_context IN (6, 7) -- context for vendor LPN
2980: --AND rsh.shipment_header_id = Nvl(To_number(p_shipment_header_id), rsh.shipment_header_id)
2981: AND (lpn.source_header_id = rsh.shipment_header_id
2982: OR lpn.source_name = rsh.shipment_num)
2983: AND EXISTS (SELECT parent_lpn_id FROM wms_lpn_contents WHERE parent_lpn_id = lpn.lpn_id)
2984: ) count_row
2985: WHERE lpn.license_plate_number LIKE (p_lpn)
2986: AND lpn.lpn_context IN (6, 7) -- context for vendor LPN
2987: --AND rsh.shipment_header_id = Nvl(To_number(p_shipment_header_id), rsh.shipment_header_id)
2986: AND lpn.lpn_context IN (6, 7) -- context for vendor LPN
2987: --AND rsh.shipment_header_id = Nvl(To_number(p_shipment_header_id), rsh.shipment_header_id)
2988: AND (lpn.source_header_id = rsh.shipment_header_id
2989: OR lpn.source_name = rsh.shipment_num)
2990: AND EXISTS (SELECT parent_lpn_id FROM wms_lpn_contents WHERE parent_lpn_id = lpn.lpn_id);
2991:
2992: END IF; -- end if for if p_shipment_header_id is not null
2993: ELSE
2994: IF p_shipment_header_id IS NOT NULL THEN
3020: WHERE lpn.lpn_context IN (6, 7) -- context for vendor LPN
3021: AND rsh.shipment_header_id = p_shipment_header_id
3022: AND (lpn.source_header_id = rsh.shipment_header_id
3023: OR lpn.source_name = rsh.shipment_num)
3024: AND EXISTS (SELECT parent_lpn_id FROM wms_lpn_contents WHERE parent_lpn_id = lpn.lpn_id)
3025: ) count_row
3026: WHERE lpn.license_plate_number LIKE (p_lpn)
3027: AND lpn.lpn_context IN (6, 7) -- context for vendor LPN
3028: AND rsh.shipment_header_id = p_shipment_header_id
3027: AND lpn.lpn_context IN (6, 7) -- context for vendor LPN
3028: AND rsh.shipment_header_id = p_shipment_header_id
3029: AND (lpn.source_header_id = rsh.shipment_header_id
3030: OR lpn.source_name = rsh.shipment_num)
3031: AND EXISTS (SELECT parent_lpn_id FROM wms_lpn_contents WHERE parent_lpn_id = lpn.lpn_id
3032: and inventory_item_id = p_inventory_item_id );
3033:
3034: ELSE -- if p_shipment_header_id is null
3035: OPEN x_lpn_lov FOR
3060: WHERE lpn.lpn_context IN (6, 7) -- context for vendor LPN
3061: --AND rsh.shipment_header_id = Nvl(To_number(p_shipment_header_id), rsh.shipment_header_id)
3062: AND (lpn.source_header_id = rsh.shipment_header_id
3063: OR lpn.source_name = rsh.shipment_num)
3064: AND EXISTS (SELECT parent_lpn_id FROM wms_lpn_contents WHERE parent_lpn_id = lpn.lpn_id)
3065: ) count_row
3066: WHERE lpn.license_plate_number LIKE (p_lpn)
3067: AND lpn.lpn_context IN (6, 7) -- context for vendor LPN
3068: --AND rsh.shipment_header_id = Nvl(To_number(p_shipment_header_id), rsh.shipment_header_id)
3067: AND lpn.lpn_context IN (6, 7) -- context for vendor LPN
3068: --AND rsh.shipment_header_id = Nvl(To_number(p_shipment_header_id), rsh.shipment_header_id)
3069: AND (lpn.source_header_id = rsh.shipment_header_id
3070: OR lpn.source_name = rsh.shipment_num)
3071: AND EXISTS (SELECT parent_lpn_id FROM wms_lpn_contents WHERE parent_lpn_id = lpn.lpn_id
3072: and inventory_item_id = p_inventory_item_id );
3073: END IF; -- else part of if p_shipment_header_id is not null
3074: END IF; -- else part of if p_inventory_item_id is null
3075: END IF;
3115: INV_PROJECT.GET_PROJECT_NUMBER,
3116: INV_PROJECT.GET_TASK_ID,
3117: INV_PROJECT.GET_TASK_NUMBER
3118: FROM wms_license_plate_numbers wlpn,
3119: wms_lpn_contents wlc,
3120: mtl_item_locations mil
3121: WHERE (mil.inventory_location_id = wlpn.locator_id
3122: AND wlpn.locator_id IS NOT NULL)
3123: AND ( (wlc.revision = p_revision
4190: AND wlpn.lpn_context = 6
4191: AND wlpn.organization_id = wdd.organization_id
4192: -- Nested LPN changes
4193: AND EXISTS (SELECT parent_lpn_id
4194: FROM wms_lpn_contents wlc
4195: WHERE parent_lpn_id = wlpn.lpn_id)
4196: AND wlpn.lpn_id = NVL(wdd.lpn_id, -9999)) count_row
4197: WHERE wdd.source_header_id = l_order_header_id
4198: AND wdd.delivery_detail_id = wda.delivery_detail_id
4201: AND wlpn.organization_id = wdd1.organization_id
4202: AND wlpn.lpn_id = NVL(wdd1.lpn_id, -9999)
4203: AND wlpn.license_plate_number LIKE (p_lpn)
4204: -- Nested LPN changes
4205: AND EXISTS (SELECT parent_lpn_id FROM wms_lpn_contents wlc WHERE parent_lpn_id = wlpn.lpn_id)
4206: ORDER BY wlpn.license_plate_number;
4207: Else
4208: OPEN x_lpn_lov FOR
4209: SELECT distinct wlpn.license_plate_number
4225: AND wlpn.lpn_context = 6
4226: AND wlpn.organization_id = wdd.organization_id
4227: -- Nested LPN changes
4228: AND EXISTS (SELECT parent_lpn_id
4229: FROM wms_lpn_contents wlc
4230: WHERE parent_lpn_id = wlpn.lpn_id)
4231: AND wlpn.lpn_id = NVL(wdd.lpn_id, -9999)) count_row
4232: WHERE wdd.source_header_id = l_order_header_id
4233: AND wdd.delivery_detail_id = wda.delivery_detail_id
4236: AND wlpn.organization_id = wdd1.organization_id
4237: AND wlpn.lpn_id = NVL(wdd1.lpn_id, -9999)
4238: AND wlpn.license_plate_number LIKE (p_lpn)
4239: -- Nested LPN changes
4240: AND EXISTS (SELECT parent_lpn_id FROM wms_lpn_contents wlc WHERE parent_lpn_id = wlpn.lpn_id
4241: and wlc.inventory_item_id = p_inventory_item_id)
4242: ORDER BY wlpn.license_plate_number;
4243: End if;
4244: END IF;
4455: NVL(wlpn.content_volume, 0),
4456: wdd.delivery_detail_id
4457: FROM wms_license_plate_numbers wlpn,
4458: wsh_delivery_details wdd ,
4459: wms_lpn_contents wlc
4460: WHERE wlpn.organization_id = p_organization_id
4461: AND wlpn.lpn_id = wlc.parent_lpn_id
4462: AND wlpn.lpn_context = WMS_Container_PUB.LPN_CONTEXT_INV
4463: AND wlc.source_name IS NOT null
4557: AND wlpn.lpn_id <> p_lpn_id
4558: AND (wlpn.lpn_context = WMS_CONTAINER_PUB.LPN_CONTEXT_PREGENERATED
4559: OR (wlpn.lpn_context = p_lpn_context
4560: AND ( (NOT EXISTS (SELECT 'LPN_HAS_MATERIAL'
4561: FROM wms_lpn_contents
4562: WHERE parent_lpn_id IN (SELECT wlpn1.lpn_id
4563: FROM
4564: wms_license_plate_numbers wlpn1
4565: START WITH