[Home] [Help]
318: --
319: /*
320: CURSOR get_all_items_in_lpn(p_lpn_id NUMBER) IS
321: SELECT wlc.inventory_item_id
322: FROM wms_lpn_contents wlc, wms_license_plate_numbers wln
323: WHERE wln.lpn_id = wlc.parent_lpn_id
324: AND lpn_id IN(SELECT lpn_id
325: FROM wms_license_plate_numbers
326: START WITH lpn_id = p_lpn_id
328: */
329:
330: CURSOR get_all_items_in_lpn(p_lpn_id NUMBER) IS
331: SELECT wlc.inventory_item_id
332: FROM wms_lpn_contents wlc
333: WHERE wlc.parent_lpn_id
334: IN ( SELECT lpn_id
335: FROM wms_license_plate_numbers
336: START WITH lpn_id = p_lpn_id
2251: FROM rcv_shipment_lines rsl
2252: WHERE rsl.shipment_header_id = p_shipment_header_id
2253: AND rsl.po_header_id = NVL(p_po_header_id, rsl.po_header_id)
2254: AND(EXISTS(SELECT 1
2255: FROM wms_lpn_contents wlc
2256: WHERE wlc.source_line_id = rsl.po_line_id
2257: AND wlc.parent_lpn_id = p_lpn_id)
2258: OR p_lpn_id IS NULL);
2259:
2275: )
2276: )
2277: OR (rsl.asn_lpn_id IS NULL
2278: AND exists (SELECT 1
2279: FROM wms_lpn_contents wlc
2280: WHERE wlc.source_line_id = rsl.po_line_id
2281: AND wlc.parent_lpn_id = p_lpn_id)
2282: )
2283: )
3751:
3752:
3753: --added for Bug 5928199
3754: select count(*) into l_count_lpn
3755: from wms_lpn_contents
3756: WHERE parent_lpn_id = p_lpn_id;
3757:
3758: --Set the out variable for lpn_context
3759: x_lpn_context := l_lpn_context;
5798: SELECT DISTINCT rsl.shipment_header_id
5799: , rsl.from_organization_id
5800: INTO x_shipment_header_id
5801: , x_from_org_id
5802: FROM rcv_shipment_lines rsl, wms_lpn_contents wlc,wms_license_plate_numbers wln
5803: WHERE rsl.item_id = wlc.inventory_item_id
5804: AND to_organization_id = p_organization_id
5805: AND wln.lpn_id IN ( SELECT lpn_id
5806: FROM wms_license_plate_numbers
5823: SELECT DISTINCT rsl.shipment_header_id
5824: , rsl.from_organization_id
5825: INTO x_shipment_header_id
5826: , x_from_org_id
5827: FROM rcv_shipment_lines rsl, wms_lpn_contents wlc
5828: WHERE rsl.item_id = wlc.inventory_item_id
5829: AND to_organization_id = p_organization_id
5830: AND wlc.parent_lpn_id IN ( SELECT lpn_id
5831: FROM wms_license_plate_numbers
6905: -- Bug 3440456
6906: -- The following is changed because the join with WLPN is unnecessary
6907: /*SELECT 'Y'
6908: INTO x_lpn_flag
6909: FROM wms_license_plate_numbers lpn, wms_lpn_contents wlc
6910: WHERE lpn.lpn_id = wlc.parent_lpn_id
6911: AND lpn_id IN (SELECT lpn_id
6912: FROM wms_license_plate_numbers wln
6913: START WITH lpn_id = p_lpn_id
6916: -- Bug# 3633708: Performance Fixes
6917: -- The following query is bad because it does a full table scan on WLC
6918: /*SELECT 'Y'
6919: INTO x_lpn_flag
6920: FROM wms_lpn_contents wlc
6921: WHERE wlc.parent_lpn_id IN (SELECT lpn_id
6922: FROM wms_license_plate_numbers wln
6923: START WITH lpn_id = p_lpn_id
6924: CONNECT BY parent_lpn_id = PRIOR lpn_id);*/
6928: SELECT 'Y'
6929: INTO x_lpn_flag
6930: FROM wms_license_plate_numbers wln
6931: WHERE EXISTS (SELECT '1'
6932: FROM wms_lpn_contents wlc
6933: WHERE wlc.parent_lpn_id = wln.lpn_id)
6934: START WITH wln.lpn_id = p_lpn_id
6935: CONNECT BY wln.parent_lpn_id = PRIOR wln.lpn_id;
6936:
6953: -- Retrieval of x_lpn_flag is changed
6954: --
6955: SELECT 'Y'
6956: INTO x_lpn_flag
6957: FROM wms_license_plate_numbers lpn, wms_lpn_contents wlc, rcv_transactions_interface rti
6958: WHERE lpn.lpn_id = wlc.parent_lpn_id
6959: AND lpn.lpn_id = rti.lpn_id
6960: AND rti.transaction_status_code = 'PENDING'
6961: AND lpn.lpn_id IN(SELECT lpn_id