[Home] [Help]
504: CURSOR c_mmtt_cart_lpn IS
505: SELECT lpn_id, package_id, content_volume_uom_code, content_volume, gross_weight_uom_code,
506: gross_weight, inventory_item_id, parent_package_id, pack_level, parent_lpn_id,
507: header_id, packaging_mode
508: FROM wms_packaging_hist
509: WHERE lpn_id is not null
510: OR package_id is not null
511: START WITH parent_lpn_id = p_transaction_id
512: CONNECT BY PARENT_PACKAGE_ID = PRIOR PACKAGE_ID;
763: , lpn.attribute15 lpn_attribute15
764: , nvl(wph.parent_package_id, l_parent_package_id) parent_package
765: , nvl(wph.pack_level, l_pack_level) pack_level
766: FROM WMS_LICENSE_PLATE_NUMBERS lpn
767: , WMS_PACKAGING_HIST wph
768: , WMS_LICENSE_PLATE_NUMBERS plpn
769: , WMS_LICENSE_PLATE_NUMBERS olpn
770: , MTL_SYSTEM_ITEMS_KFV msik
771: /*Commented for bug# 6334460 start
895: -- Since this is for non-cartonization flow
896: -- Removed the following information
897: -- 1. Removed input parameter p_package_id
898: -- 2. Removed the reference to l_packaging_mode because it is only relavent for cartonization
899: -- 3. Removed the union all of wms_packaging_hist part
900: CURSOR c_lpn_item_content(p_lpn_id NUMBER, p_item_id NUMBER) IS
901: SELECT
902: nvl(p_organization_id, plpn.organization_id) organization_id
903: , nvl(p_inventory_item_id, wlc.inventory_item_id) inventory_item_id
920: where 1=1
921: -- Bug 4137707
922: --start with lpn_id in (select nvl(p_lpn_id, -99) from dual
923: --union all
924: --select lpn_id from wms_packaging_hist
925: --where pack_level = 0
926: --and lpn_id IS not null
927: --start with parent_package_id = p_package_id
928: --connect by PARENT_PACKAGE_ID = PRIOR PACKAGE_ID)
1037: WHERE plpn.lpn_id in (select lpn_id from wms_license_plate_numbers
1038: where 1=1
1039: start with lpn_id in (select nvl(p_lpn_id, -99) from dual
1040: union all
1041: select lpn_id from wms_packaging_hist
1042: where pack_level = 0
1043: and lpn_id IS not null
1044: start with parent_package_id = p_package_id
1045: connect by PARENT_PACKAGE_ID = PRIOR PACKAGE_ID)
1097: , INV_PROJECT.GET_LOCSEGS(milkfv.inventory_location_id,milkfv.organization_id) locator
1098: , l_secondary_quantity secondary_quantity -- invconv fabdi
1099: , l_secondary_uom secondary_uom -- invconv fabdi
1100:
1101: FROM wms_packaging_hist wpc
1102: , mtl_material_transactions_temp mmtt
1103: , mtl_system_items msi
1104: , cst_cost_groups ccg
1105: , mtl_item_locations milkfv
1105: , mtl_item_locations milkfv
1106: -- Bug 4137707, Do not need to include this where clause,
1107: -- This will be controlled when opening this cursor
1108: -- WHERE cartonization_flag = 1 --Cartonization Flow
1109: WHERE wpc.rowid in (select rowid from wms_packaging_hist
1110: where pack_level = 0
1111: AND header_id = l_header_id
1112: AND l_packaging_mode in (WMS_CARTNZN_WRAP.PR_PKG_MODE, WMS_CARTNZN_WRAP.mfg_pr_pkg_mode)
1113: -- Added the new mode (WMS_CARTNZN_WRAP.mfg_pr_pkg_mode) for fix to Bug 2764074..
1114: AND lpn_id is null
1115: start with parent_lpn_id = p_lpn_id
1116: connect by PARENT_PACKAGE_ID = PRIOR PACKAGE_ID
1117: union all
1118: select rowid from wms_packaging_hist
1119: where pack_level = 0
1120: AND lpn_id is null
1121: start with parent_package_id = p_package_id
1122: connect by PARENT_PACKAGE_ID = PRIOR PACKAGE_ID)
1458: -- if no records found, should not try to access wph, so set flag to 0
1459: Begin
1460: SELECT DISTINCT header_id, packaging_mode , pack_level
1461: INTO l_header_id, l_packaging_mode,l_pack_level
1462: FROM WMS_PACKAGING_HIST
1463: WHERE parent_lpn_id = p_transaction_id;
1464: EXCEPTION
1465: WHEN no_data_found THEN
1466: IF (l_debug = 1) THEN