[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
2368: FROM rcv_shipment_lines rsl
2369: WHERE rsl.shipment_header_id = p_shipment_header_id
2370: AND rsl.po_header_id = NVL(p_po_header_id, rsl.po_header_id)
2371: AND(EXISTS(SELECT 1
2372: FROM wms_lpn_contents wlc
2373: WHERE wlc.source_line_id = rsl.po_line_id
2374: AND wlc.parent_lpn_id = p_lpn_id)
2375: OR p_lpn_id IS NULL);
2376:
2393: )
2394: )
2395: OR (rsl.asn_lpn_id IS NULL
2396: AND exists (SELECT 1
2397: FROM wms_lpn_contents wlc
2398: WHERE wlc.source_line_id = rsl.po_line_id
2399: AND wlc.parent_lpn_id = p_lpn_id)
2400: )
2401: )
4234:
4235:
4236: --added for Bug 5928199
4237: select count(*) into l_count_lpn
4238: from wms_lpn_contents
4239: WHERE parent_lpn_id = p_lpn_id;
4240:
4241: --Set the out variable for lpn_context
4242: x_lpn_context := l_lpn_context;
6330: SELECT DISTINCT rsl.shipment_header_id
6331: , rsl.from_organization_id
6332: INTO x_shipment_header_id
6333: , x_from_org_id
6334: FROM rcv_shipment_lines rsl, wms_lpn_contents wlc,wms_license_plate_numbers wln
6335: WHERE rsl.item_id = wlc.inventory_item_id
6336: AND to_organization_id = p_organization_id
6337: AND wln.lpn_id IN ( SELECT lpn_id
6338: FROM wms_license_plate_numbers
6355: SELECT DISTINCT rsl.shipment_header_id
6356: , rsl.from_organization_id
6357: INTO x_shipment_header_id
6358: , x_from_org_id
6359: FROM rcv_shipment_lines rsl, wms_lpn_contents wlc
6360: WHERE rsl.item_id = wlc.inventory_item_id
6361: AND to_organization_id = p_organization_id
6362: AND wlc.parent_lpn_id IN ( SELECT lpn_id
6363: FROM wms_license_plate_numbers
7437: -- Bug 3440456
7438: -- The following is changed because the join with WLPN is unnecessary
7439: /*SELECT 'Y'
7440: INTO x_lpn_flag
7441: FROM wms_license_plate_numbers lpn, wms_lpn_contents wlc
7442: WHERE lpn.lpn_id = wlc.parent_lpn_id
7443: AND lpn_id IN (SELECT lpn_id
7444: FROM wms_license_plate_numbers wln
7445: START WITH lpn_id = p_lpn_id
7448: -- Bug# 3633708: Performance Fixes
7449: -- The following query is bad because it does a full table scan on WLC
7450: /*SELECT 'Y'
7451: INTO x_lpn_flag
7452: FROM wms_lpn_contents wlc
7453: WHERE wlc.parent_lpn_id IN (SELECT lpn_id
7454: FROM wms_license_plate_numbers wln
7455: START WITH lpn_id = p_lpn_id
7456: CONNECT BY parent_lpn_id = PRIOR lpn_id);*/
7460: SELECT 'Y'
7461: INTO x_lpn_flag
7462: FROM wms_license_plate_numbers wln
7463: WHERE EXISTS (SELECT '1'
7464: FROM wms_lpn_contents wlc
7465: WHERE wlc.parent_lpn_id = wln.lpn_id)
7466: START WITH wln.lpn_id = p_lpn_id
7467: CONNECT BY wln.parent_lpn_id = PRIOR wln.lpn_id;
7468:
7485: -- Retrieval of x_lpn_flag is changed
7486: --
7487: SELECT 'Y'
7488: INTO x_lpn_flag
7489: FROM wms_license_plate_numbers lpn, wms_lpn_contents wlc, rcv_transactions_interface rti
7490: WHERE lpn.lpn_id = wlc.parent_lpn_id
7491: AND lpn.lpn_id = rti.lpn_id
7492: AND rti.transaction_status_code = 'PENDING'
7493: AND lpn.lpn_id IN(SELECT lpn_id