[Home] [Help]
297: SELECT '1'
298: INTO l_dummy
299: FROM dual
300: WHERE exists (SELECT '1'
301: FROM wms_lpn_contents wlc
302: , wms_license_plate_numbers wlpn
303: , mtl_system_items msi
304: WHERE wlc.parent_lpn_id = wlpn.lpn_id
305: AND wlc.inventory_item_id = msi.inventory_item_id
2219: wmslpnci.attribute13 attribute13,
2220: wmslpnci.attribute14 attribute14,
2221: wmslpnci.attribute15 attribute15
2222: FROM
2223: wms_lpn_contents_interface wmslpnci,
2224: wms_license_plate_numbers wmslpn
2225: WHERE
2226: wmslpnci.interface_transaction_id = l_intf_txn_id AND
2227: wmslpn.license_plate_number (+)= wmslpnci.license_plate_number;
2423: ,organization_id = l_rti_rec.to_organization_id
2424: WHERE source_group_id = p_group_id
2425: AND EXISTS (SELECT 1 --Only for EDI TXN we are doing this
2426: --And EDI TXN will have WLPNCI
2427: FROM wms_lpn_contents_interface
2428: WHERE group_id = p_group_id);
2429: EXCEPTION
2430: WHEN OTHERS THEN
2431: IF (l_debug = 1) THEN
2931: WHERE rti.lpn_group_id IS NULL
2932: AND ( ( (rti.lpn_id IS NOT NULL
2933: OR rti.license_plate_number IS NOT NULL)
2934: ) or
2935: (exists ( select 'x' from wms_lpn_contents_interface wlc
2936: where wlc.interface_transaction_id =
2937: rti.interface_transaction_id
2938: )
2939: )
8483:
8484: -- We should not be doing full unpack. Instead if one of
8485: -- the conditions used to set l_full_unpack to true is
8486: -- met then we should null out the lot_number on
8487: -- wms_lpn_contents and null out the lpn_id on mtl_serial_numbers
8488:
8489: UPDATE wms_lpn_contents
8490: SET lot_number = NULL,
8491: revision = NULL
8485: -- the conditions used to set l_full_unpack to true is
8486: -- met then we should null out the lot_number on
8487: -- wms_lpn_contents and null out the lpn_id on mtl_serial_numbers
8488:
8489: UPDATE wms_lpn_contents
8490: SET lot_number = NULL,
8491: revision = NULL
8492: WHERE parent_lpn_id = l_rti_rec.lpn_id
8493: AND inventory_item_id = l_rti_rec.item_id;
11009:
11010: For l_lot_rec in ( select lot_number,
11011: uom_code,
11012: sum(quantity) quantity
11013: from wms_lpn_contents wlc
11014: where wlc.inventory_item_id = p_item_id
11015: and wlc.organization_id = p_org_id
11016: and wlc.parent_lpn_id = p_lpn_id
11017: group by lot_number, uom_code )
12346: ,NULL task_id
12347: ,0 mmtt_quantity
12348: ,sum(nvl(wlc.quantity,0)) mol_quantity
12349: from wms_license_plate_numbers wlpn,
12350: wms_lpn_contents wlc
12351: where wlc.parent_lpn_id = wlpn.lpn_id
12352: and wlpn.lpn_id = p_child_lpn_id1
12353: group by
12354: wlpn.lpn_id
12944: ,wlc.organization_id organization_id
12945: --,wlc.item_description item_description
12946: ,sum(get_primary_qty(wlc.organization_id, wlc.inventory_item_id, wlc.uom_code,nvl(wlc.quantity,0)))
12947: primary_quantity
12948: from wms_lpn_contents wlc
12949: where wlc.parent_lpn_id = l_wln_csr.lpn_id
12950: -- 4507808
12951: -- Commented the exists statement below
12952: -- by the l_wln_csr above.
13046: --,wlc.item_description item_description
13047: ,wlc.lot_number lot_number
13048: ,sum(get_primary_qty(wlc.organization_id, wlc.inventory_item_id, wlc.uom_code,nvl(wlc.quantity,0)))
13049: primary_quantity
13050: from wms_lpn_contents wlc
13051: where wlc.parent_lpn_id = l_wln_csr.lpn_id
13052: -- 4507808
13053: -- Commented the exists statement below
13054: -- and replaced by the outer cursor l_wln_csr
13267: cursor c_lpn_contents(p_child_lpn_id varchar2) is
13268: select parent_lpn_id
13269: ,inventory_item_id
13270: ,quantity
13271: from wms_lpn_contents
13272: where parent_lpn_id = p_child_lpn_id;
13273:
13274: l_lpn_contents_rec c_lpn_contents%rowtype;
13275:
15763: FROM wms_license_plate_numbers wlpn2
15764: CONNECT BY PRIOR wlpn2.lpn_id = wlpn2.parent_lpn_id
15765: START WITH wlpn2.lpn_id = c_wlpni_rec.LPN_ID
15766: )
15767: AND NOT exists ( SELECT 'x' FROM wms_lpn_contents
15768: WHERE parent_lpn_id = c_wlpni_rec.LPN_ID
15769: )
15770: AND ROWNUM = 1
15771: ;