[Home] [Help]
515: CURSOR c_mmtt_cart_lpn IS
516: SELECT lpn_id, package_id, content_volume_uom_code, content_volume, gross_weight_uom_code,
517: gross_weight, inventory_item_id, parent_package_id, pack_level, parent_lpn_id,
518: header_id, packaging_mode
519: FROM wms_packaging_hist
520: WHERE lpn_id is not null
521: OR package_id is not null
522: START WITH parent_lpn_id = p_transaction_id
523: CONNECT BY PARENT_PACKAGE_ID = PRIOR PACKAGE_ID;
792: , lpn.attribute15 lpn_attribute15
793: , nvl(wph.parent_package_id, l_parent_package_id) parent_package
794: , nvl(wph.pack_level, l_pack_level) pack_level
795: FROM WMS_LICENSE_PLATE_NUMBERS lpn
796: , WMS_PACKAGING_HIST wph
797: , WMS_LICENSE_PLATE_NUMBERS plpn
798: , WMS_LICENSE_PLATE_NUMBERS olpn
799: , MTL_SYSTEM_ITEMS_KFV msik
800: /*Commented for bug# 6334460 start
1039: -- Since this is for non-cartonization flow
1040: -- Removed the following information
1041: -- 1. Removed input parameter p_package_id
1042: -- 2. Removed the reference to l_packaging_mode because it is only relavent for cartonization
1043: -- 3. Removed the union all of wms_packaging_hist part
1044: --Bug#8366557 Added hints to following cursor
1045: CURSOR c_lpn_item_content(p_lpn_id NUMBER, p_item_id NUMBER) IS
1046: SELECT
1047: nvl(p_organization_id, plpn.organization_id) organization_id
1065: where 1=1
1066: -- Bug 4137707
1067: --start with lpn_id in (select nvl(p_lpn_id, -99) from dual
1068: --union all
1069: --select lpn_id from wms_packaging_hist
1070: --where pack_level = 0
1071: --and lpn_id IS not null
1072: --start with parent_package_id = p_package_id
1073: --connect by PARENT_PACKAGE_ID = PRIOR PACKAGE_ID)
1185: ((select lpn_id id from wms_license_plate_numbers
1186: where 1=1
1187: start with lpn_id in (select nvl(p_lpn_id, -99) from dual
1188: union all
1189: select /*+ cardinality(1) */ lpn_id from wms_packaging_hist
1190: where pack_level = 0
1191: and lpn_id IS not null
1192: start with parent_package_id = p_package_id
1193: connect by PARENT_PACKAGE_ID = PRIOR PACKAGE_ID)
1245: , INV_PROJECT.GET_LOCSEGS(milkfv.inventory_location_id,milkfv.organization_id) locator
1246: , l_secondary_quantity secondary_quantity -- invconv fabdi
1247: , l_secondary_uom secondary_uom -- invconv fabdi
1248:
1249: FROM wms_packaging_hist wpc
1250: , mtl_material_transactions_temp mmtt
1251: , mtl_system_items msi
1252: , cst_cost_groups ccg
1253: , mtl_item_locations milkfv
1253: , mtl_item_locations milkfv
1254: -- Bug 4137707, Do not need to include this where clause,
1255: -- This will be controlled when opening this cursor
1256: -- WHERE cartonization_flag = 1 --Cartonization Flow
1257: WHERE wpc.rowid in ( select /*+ cardinality(1) */ id from ((select rowid id from wms_packaging_hist
1258: where pack_level = 0
1259: AND header_id = l_header_id
1260: AND l_packaging_mode in (WMS_CARTNZN_WRAP.PR_PKG_MODE, WMS_CARTNZN_WRAP.mfg_pr_pkg_mode)
1261: -- Added the new mode (WMS_CARTNZN_WRAP.mfg_pr_pkg_mode) for fix to Bug 2764074..
1262: AND lpn_id is null
1263: start with parent_lpn_id = p_lpn_id
1264: connect by PARENT_PACKAGE_ID = PRIOR PACKAGE_ID
1265: union all
1266: select rowid from wms_packaging_hist
1267: where pack_level = 0
1268: AND lpn_id is null
1269: start with parent_package_id = p_package_id
1270: connect by PARENT_PACKAGE_ID = PRIOR PACKAGE_ID) ) t )
1707: -- if no records found, should not try to access wph, so set flag to 0
1708: Begin
1709: SELECT DISTINCT header_id, packaging_mode , pack_level
1710: INTO l_header_id, l_packaging_mode,l_pack_level
1711: FROM WMS_PACKAGING_HIST
1712: WHERE parent_lpn_id = p_transaction_id;
1713: EXCEPTION
1714: WHEN no_data_found THEN
1715: IF (l_debug = 1) THEN