[Home] [Help]
4986: , -- inlined wms_person_resource_utt_v, bug 2648133
4987: mtl_item_locations loc
4988: , mtl_secondary_inventories sub
4989: , mtl_txn_request_lines mol
4990: -- mtl_system_items msi -- bug 2648133
4991: WHERE v.emp_id = p_sign_on_emp_id -- restrict to sign on employee
4992: AND wdtv.organization_id = p_sign_on_org_id
4993: AND wdtv.wms_task_type_id = NVL(l_sys_task_type, wdtv.wms_task_type_id) -- restrict to picking tasks or all tasks
4994: AND wdtv.user_task_type_id = v.standard_operation_id -- join task to resource view, check if user defined task type match
5448: , -- inlined wms_person_resource_utt_v, bug 2648133
5449: mtl_item_locations loc
5450: , mtl_secondary_inventories sub
5451: , mtl_txn_request_lines mol
5452: -- mtl_system_items msi -- bug 2648133
5453: WHERE v.emp_id = p_sign_on_emp_id -- restrict to sign on employee
5454: AND wdtv.organization_id = p_sign_on_org_id
5455: AND wdtv.wms_task_type_id = NVL(l_sys_task_type, wdtv.wms_task_type_id) -- restrict to picking tasks or all tasks
5456: AND wdtv.user_task_type_id = v.standard_operation_id -- join task to resource view, check if user defined task type match
6205: , -- inlined wms_person_resource_utt_v, bug 2648133
6206: mtl_item_locations loc
6207: , mtl_secondary_inventories sub
6208: , mtl_txn_request_lines mol
6209: -- mtl_system_items msi -- bug 2648133
6210: WHERE v.emp_id = p_sign_on_emp_id -- restrict to sign on employee
6211: AND(wdtv.pick_slip_number = v_pick_slip_number
6212: OR(wdtv.task_id = v_task_id
6213: AND wdtv.wms_task_type_id = v_task_type))
7738: , --changed to kfv bug#2742611
7739: mtl_secondary_inventories sub
7740: , mtl_txn_request_lines mol
7741: , mtl_txn_request_headers moh
7742: , -- mtl_system_items msi -- bug 2648133
7743: wsh_delivery_details_ob_grp_v wdd
7744: , -- added
7745: wsh_delivery_assignments_v wda --added
7746: WHERE v.emp_id = p_sign_on_emp_id -- restrict to sign on employee
7899: , --changed to kfv bug#2742611
7900: mtl_secondary_inventories sub
7901: , mtl_txn_request_lines mol
7902: , mtl_txn_request_headers moh
7903: -- mtl_system_items msi -- bug 2648133
7904: WHERE v.emp_id = p_sign_on_emp_id -- restrict to sign on employee
7905: AND wdtv.organization_id = p_sign_on_org_id
7906: AND wdtv.wms_task_type_id = 1 -- restrict to picking tasks
7907: AND mol.header_id = moh.header_id
8059: , --changed to kfv bug#2742611
8060: mtl_secondary_inventories sub
8061: , mtl_txn_request_lines mol
8062: , mtl_txn_request_headers moh
8063: , -- mtl_system_items msi -- bug 2648133
8064: wsh_delivery_details_ob_grp_v wdd
8065: , -- added
8066: wsh_delivery_assignments_v wda --added
8067: WHERE v.emp_id = p_sign_on_emp_id -- restrict to sign on employee
8219: , --changed to kfv bug#2742611
8220: mtl_secondary_inventories sub
8221: , mtl_txn_request_lines mol
8222: , mtl_txn_request_headers moh
8223: -- mtl_system_items msi -- bug 2648133
8224: WHERE v.emp_id = p_sign_on_emp_id -- restrict to sign on employee
8225: AND wdtv.organization_id = p_sign_on_org_id
8226: AND wdtv.wms_task_type_id = 1 -- restrict to picking tasks
8227: AND mol.header_id = moh.header_id
8348: , --changed to kfv bug#2742611
8349: mtl_secondary_inventories sub
8350: , mtl_txn_request_lines mol
8351: , mtl_txn_request_headers moh
8352: , -- mtl_system_items msi -- bug 2648133
8353: wsh_delivery_details_ob_grp_v wdd
8354: , -- added
8355: wsh_delivery_assignments_v wda --added
8356: WHERE wdt.transaction_temp_id = qt.task_id
8455: , --changed to kfv bug#2742611
8456: mtl_secondary_inventories sub
8457: , mtl_txn_request_lines mol
8458: , mtl_txn_request_headers moh
8459: , -- mtl_system_items msi -- bug 2648133
8460: wsh_delivery_details_ob_grp_v wdd
8461: , -- added
8462: wsh_delivery_assignments_v wda --added
8463: WHERE v.emp_id = p_sign_on_emp_id -- restrict to sign on employee
8590: , --changed to kfv bug#2742611
8591: mtl_secondary_inventories sub
8592: , mtl_txn_request_lines mol
8593: , mtl_txn_request_headers moh
8594: -- mtl_system_items msi -- bug 2648133
8595: WHERE wdt.transaction_temp_id = wdtv.task_id
8596: AND wdt.status in ( 2,3) -- Queued and dispatched tasks only bug 4310093
8597: AND wdt.person_id = p_sign_on_emp_id
8598: AND wdt.organization_id = p_sign_on_org_id
8687: , --changed to kfv bug#2742611
8688: mtl_secondary_inventories sub
8689: , mtl_txn_request_lines mol
8690: , mtl_txn_request_headers moh
8691: -- mtl_system_items msi -- bug 2648133
8692: WHERE v.emp_id = p_sign_on_emp_id -- restrict to sign on employee
8693: AND wdtv.organization_id = p_sign_on_org_id
8694: AND wdtv.wms_task_type_id = 1 -- restrict to picking tasks
8695: AND mol.header_id = moh.header_id
8820: , --changed to kfv bug#2742611
8821: mtl_secondary_inventories sub
8822: , mtl_txn_request_lines mol
8823: , mtl_txn_request_headers moh
8824: , -- mtl_system_items msi -- bug 2648133
8825: wsh_delivery_details_ob_grp_v wdd
8826: , -- added
8827: wsh_delivery_assignments_v wda --added
8828: WHERE wdt.transaction_temp_id = qt.task_id
8927: , --changed to kfv bug#2742611
8928: mtl_secondary_inventories sub
8929: , mtl_txn_request_lines mol
8930: , mtl_txn_request_headers moh
8931: , -- mtl_system_items msi -- bug 2648133
8932: wsh_delivery_details_ob_grp_v wdd
8933: , -- added
8934: wsh_delivery_assignments_v wda --added
8935: WHERE v.emp_id = p_sign_on_emp_id -- restrict to sign on employee
9052: , --changed to kfv bug#2742611
9053: mtl_secondary_inventories sub
9054: , mtl_txn_request_lines mol
9055: , mtl_txn_request_headers moh
9056: -- mtl_system_items msi -- bug 2648133
9057: WHERE wdt.transaction_temp_id = wdtv.task_id
9058: AND wdt.status = 2 -- Queued tasks only
9059: AND wdt.person_id = p_sign_on_emp_id
9060: AND wdt.organization_id = p_sign_on_org_id
9151: , --changed to kfv bug#2742611
9152: mtl_secondary_inventories sub
9153: , mtl_txn_request_lines mol
9154: , mtl_txn_request_headers moh
9155: -- mtl_system_items msi -- bug 2648133
9156: WHERE v.emp_id = p_sign_on_emp_id -- restrict to sign on employee
9157: AND wdtv.organization_id = p_sign_on_org_id
9158: AND wdtv.wms_task_type_id = 1 -- restrict to picking tasks
9159: AND mol.header_id = moh.header_id
9284: , --changed to kfv bug#2742611
9285: mtl_secondary_inventories sub
9286: , mtl_txn_request_lines mol
9287: , mtl_txn_request_headers moh
9288: , -- mtl_system_items msi -- bug 2648133
9289: wsh_delivery_details_ob_grp_v wdd
9290: , -- added
9291: wsh_delivery_assignments_v wda --added
9292: WHERE wdt.transaction_temp_id = qt.task_id
9389: , --changed to kfv bug#2742611
9390: mtl_secondary_inventories sub
9391: , mtl_txn_request_lines mol
9392: , mtl_txn_request_headers moh
9393: , -- mtl_system_items msi -- bug 2648133
9394: wsh_delivery_details_ob_grp_v wdd
9395: , -- added
9396: wsh_delivery_assignments_v wda --added
9397: WHERE v.emp_id = p_sign_on_emp_id -- restrict to sign on employee
9521: , --changed to kfv bug#2742611
9522: mtl_secondary_inventories sub
9523: , mtl_txn_request_lines mol
9524: , mtl_txn_request_headers moh
9525: -- mtl_system_items msi -- bug 2648133
9526: WHERE wdt.transaction_temp_id = wdtv.task_id
9527: AND wdt.status in ( 2,3) -- Queued and dispatched tasks only bug 4310093
9528: AND wdt.person_id = p_sign_on_emp_id
9529: AND wdt.organization_id = p_sign_on_org_id
9619: , --changed to kfv bug#2742611
9620: mtl_secondary_inventories sub
9621: , mtl_txn_request_lines mol
9622: , mtl_txn_request_headers moh
9623: -- mtl_system_items msi -- bug 2648133
9624: WHERE v.emp_id = p_sign_on_emp_id -- restrict to sign on employee
9625: AND wdtv.organization_id = p_sign_on_org_id
9626: AND wdtv.wms_task_type_id = 1 -- restrict to picking tasks
9627: AND mol.header_id = moh.header_id
9748: , --changed to kfv bug#2742611
9749: mtl_secondary_inventories sub
9750: , mtl_txn_request_lines mol
9751: , mtl_txn_request_headers moh
9752: , -- mtl_system_items msi -- bug 2648133
9753: wsh_delivery_details_ob_grp_v wdd
9754: , -- added
9755: wsh_delivery_assignments_v wda --added
9756: WHERE wdt.transaction_temp_id = qt.task_id
9853: , --changed to kfv bug#2742611
9854: mtl_secondary_inventories sub
9855: , mtl_txn_request_lines mol
9856: , mtl_txn_request_headers moh
9857: , -- mtl_system_items msi -- bug 2648133
9858: wsh_delivery_details_ob_grp_v wdd
9859: , -- added
9860: wsh_delivery_assignments_v wda --added
9861: WHERE v.emp_id = p_sign_on_emp_id -- restrict to sign on employee
9977: , --changed to kfv bug#2742611
9978: mtl_secondary_inventories sub
9979: , mtl_txn_request_lines mol
9980: , mtl_txn_request_headers moh
9981: -- mtl_system_items msi -- bug 2648133
9982: WHERE wdt.transaction_temp_id = wdtv.task_id
9983: AND wdt.status = 2 -- Queued tasks only
9984: AND wdt.person_id = p_sign_on_emp_id
9985: AND wdt.organization_id = p_sign_on_org_id
10075: , --changed to kfv bug#2742611
10076: mtl_secondary_inventories sub
10077: , mtl_txn_request_lines mol
10078: , mtl_txn_request_headers moh
10079: -- mtl_system_items msi -- bug 2648133
10080: WHERE v.emp_id = p_sign_on_emp_id -- restrict to sign on employee
10081: AND wdtv.organization_id = p_sign_on_org_id
10082: AND wdtv.wms_task_type_id = 1 -- restrict to picking tasks
10083: AND mol.header_id = moh.header_id
11266: , -- equipment weight UOM code
11267: item.volume_uom_code item_v_uom
11268: , -- item volume UOM code
11269: item.weight_uom_code item_w_uom -- item weight UOM code
11270: FROM mtl_system_items equip
11271: , mtl_system_items item
11272: , bom_resource_equipments res_equip
11273: , bom_resources res
11274: , bom_std_op_resources tt_x_res
11267: item.volume_uom_code item_v_uom
11268: , -- item volume UOM code
11269: item.weight_uom_code item_w_uom -- item weight UOM code
11270: FROM mtl_system_items equip
11271: , mtl_system_items item
11272: , bom_resource_equipments res_equip
11273: , bom_resources res
11274: , bom_std_op_resources tt_x_res
11275: WHERE tt_x_res.standard_operation_id = p_standard_operation_id --join task with task_type-resource x-ref
11275: WHERE tt_x_res.standard_operation_id = p_standard_operation_id --join task with task_type-resource x-ref
11276: AND tt_x_res.resource_id = res.resource_id -- join with resource
11277: AND res.resource_type = 1 -- resource type for equipment
11278: AND res_equip.resource_id = tt_x_res.resource_id -- join with resource-equip x-ref
11279: AND equip.inventory_item_id = res_equip.inventory_item_id -- join with equipment (mtl_system_items)
11280: AND equip.organization_id = res_equip.organization_id
11281: AND item.inventory_item_id = p_item_id -- join with item for the item that is transfered
11282: AND item.organization_id = p_organization_id;
11283:
11290: l_progress := '10';
11291:
11292: SELECT primary_uom_code
11293: INTO l_item_prim_uom_code
11294: FROM mtl_system_items
11295: WHERE inventory_item_id = p_item_id
11296: AND organization_id = p_organization_id;
11297:
11298: -- compute conversion rate between transaction UOM and item primary UOM
11601: WHERE spg.pick_grouping_rule_id = moh.grouping_rule_id
11602: AND spg.pick_method = '4'
11603: AND moh.header_id = mol.header_id)
11604: OR EXISTS(SELECT 1
11605: FROM mtl_system_items msi
11606: WHERE msi.inventory_item_id = mmtt.inventory_item_id
11607: AND msi.organization_id = mmtt.organization_id --8715667
11608: AND msi.bulk_picked_flag = 'Y')
11609: )
11607: AND msi.organization_id = mmtt.organization_id --8715667
11608: AND msi.bulk_picked_flag = 'Y')
11609: )
11610: AND EXISTS ( SELECT 1 -- Only Consolidate Tasks for Plain item
11611: FROM mtl_system_items msi2
11612: WHERE msi2.inventory_item_id = mmtt.inventory_item_id
11613: AND msi2.organization_id = mmtt.organization_id --8715667
11614: AND msi2.lot_control_code = 1
11615: AND(msi2.serial_number_control_code = 1
11701: WHERE spg.pick_grouping_rule_id = moh.grouping_rule_id
11702: AND spg.pick_method = '4'
11703: AND moh.header_id = mol.header_id)
11704: OR EXISTS(SELECT 1
11705: FROM mtl_system_items msi
11706: WHERE msi.inventory_item_id = mmtt.inventory_item_id
11707: AND msi.organization_id = mmtt.organization_id --8715667
11708: AND msi.bulk_picked_flag = 'Y')
11709: )
11707: AND msi.organization_id = mmtt.organization_id --8715667
11708: AND msi.bulk_picked_flag = 'Y')
11709: )
11710: AND EXISTS( SELECT 1 -- Only Consolidate Tasks for Plain item
11711: FROM mtl_system_items msi2
11712: WHERE msi2.inventory_item_id = mmtt.inventory_item_id
11713: AND msi2.organization_id = mmtt.organization_id --8715667
11714: AND msi2.lot_control_code = 1
11715: AND(msi2.serial_number_control_code = 1
12159: WHERE msi.secondary_inventory_name = mmtt.subinventory_code
12160: AND msi.organization_id = mmtt.organization_id
12161: AND msi.enable_bulk_pick= 'Y')
12162: OR EXISTS(SELECT 1 -- item is bulk picking enabled
12163: FROM mtl_system_items msi
12164: WHERE msi.inventory_item_id = mmtt.inventory_item_id
12165: AND msi.organization_id = mmtt.organization_id --8715667
12166: AND msi.bulk_picked_flag = 'Y')
12167: )
12240: WHERE msi.secondary_inventory_name = mmtt.subinventory_code
12241: AND msi.organization_id = mmtt.organization_id
12242: AND msi.enable_bulk_pick= 'Y')
12243: OR EXISTS(SELECT 1 -- item is bulk picking enabled
12244: FROM mtl_system_items msi
12245: WHERE msi.inventory_item_id = mmtt.inventory_item_id
12246: AND msi.organization_id = mmtt.organization_id --8715667
12247: AND msi.bulk_picked_flag = 'Y')
12248: )
12914:
12915: -- to get the serial control code
12916: select serial_number_control_code,lot_control_code
12917: into l_serial_number_control_code,l_lot_control_code
12918: from mtl_system_items_b msi,mtl_material_transactions_temp mmtt
12919: where mmtt.transaction_temp_id = p_parent_transaction_temp_id
12920: and mmtt.inventory_item_id = msi.inventory_item_id
12921: and mmtt.organization_id = msi.organization_id;
12922:
13045: , -- equipment weight UOM code
13046: item.volume_uom_code item_v_uom
13047: , -- item volume UOM code
13048: item.weight_uom_code item_w_uom -- item weight UOM code
13049: FROM mtl_system_items equip
13050: , mtl_system_items item
13051: , wms_cartonization_temp mmtt
13052: , bom_resource_equipments res_equip
13053: , bom_resources res
13046: item.volume_uom_code item_v_uom
13047: , -- item volume UOM code
13048: item.weight_uom_code item_w_uom -- item weight UOM code
13049: FROM mtl_system_items equip
13050: , mtl_system_items item
13051: , wms_cartonization_temp mmtt
13052: , bom_resource_equipments res_equip
13053: , bom_resources res
13054: , bom_std_op_resources tt_x_res
13056: AND mmtt.standard_operation_id = tt_x_res.standard_operation_id --join task with task_type-resource x-ref
13057: AND tt_x_res.resource_id = res.resource_id -- join with resource
13058: AND res.resource_type = 1 -- resource type for equipment
13059: AND res_equip.resource_id = tt_x_res.resource_id -- join with resource-equip x-ref
13060: AND equip.inventory_item_id = res_equip.inventory_item_id -- join with equipment (mtl_system_items)
13061: AND equip.organization_id = res_equip.organization_id
13062: AND item.inventory_item_id = mmtt.inventory_item_id -- join with item for the item that is transfered
13063: AND item.organization_id = mmtt.organization_id;
13064:
13082: , -- equipment weight UOM code
13083: item.volume_uom_code item_v_uom
13084: , -- item volume UOM code
13085: item.weight_uom_code item_w_uom -- item weight UOM code
13086: FROM mtl_system_items equip
13087: , mtl_system_items item
13088: , mtl_material_transactions_temp mmtt
13089: , bom_resource_equipments res_equip
13090: , bom_resources res
13083: item.volume_uom_code item_v_uom
13084: , -- item volume UOM code
13085: item.weight_uom_code item_w_uom -- item weight UOM code
13086: FROM mtl_system_items equip
13087: , mtl_system_items item
13088: , mtl_material_transactions_temp mmtt
13089: , bom_resource_equipments res_equip
13090: , bom_resources res
13091: , bom_std_op_resources tt_x_res
13093: AND mmtt.standard_operation_id = tt_x_res.standard_operation_id --join task with task_type-resource x-ref
13094: AND tt_x_res.resource_id = res.resource_id -- join with resource
13095: AND res.resource_type = 1 -- resource type for equipment
13096: AND res_equip.resource_id = tt_x_res.resource_id -- join with resource-equip x-ref
13097: AND equip.inventory_item_id = res_equip.inventory_item_id -- join with equipment (mtl_system_items)
13098: AND equip.organization_id = res_equip.organization_id
13099: AND item.inventory_item_id = mmtt.inventory_item_id -- join with item for the item that is transfered
13100: AND item.organization_id = mmtt.organization_id;
13101:
13187: , l_item_prim_uom_code
13188: , l_lot_control_code
13189: , l_serial_number_control_code
13190: , l_item_sec_uom_code
13191: FROM wms_cartonization_temp mmtt, mtl_item_locations mil, mtl_secondary_inventories msi, mtl_system_items item
13192: WHERE mmtt.transaction_temp_id = p_task_id
13193: AND mmtt.locator_id = mil.inventory_location_id(+)
13194: AND mmtt.organization_id = mil.organization_id(+)
13195: AND mmtt.subinventory_code = msi.secondary_inventory_name