[Home] [Help]
4647: , -- inlined wms_person_resource_utt_v, bug 2648133
4648: mtl_item_locations loc
4649: , mtl_secondary_inventories sub
4650: , mtl_txn_request_lines mol
4651: -- mtl_system_items msi -- bug 2648133
4652: WHERE v.emp_id = p_sign_on_emp_id -- restrict to sign on employee
4653: AND wdtv.organization_id = p_sign_on_org_id
4654: AND wdtv.wms_task_type_id = NVL(l_sys_task_type, wdtv.wms_task_type_id) -- restrict to picking tasks or all tasks
4655: AND wdtv.user_task_type_id = v.standard_operation_id -- join task to resource view, check if user defined task type match
5109: , -- inlined wms_person_resource_utt_v, bug 2648133
5110: mtl_item_locations loc
5111: , mtl_secondary_inventories sub
5112: , mtl_txn_request_lines mol
5113: -- mtl_system_items msi -- bug 2648133
5114: WHERE v.emp_id = p_sign_on_emp_id -- restrict to sign on employee
5115: AND wdtv.organization_id = p_sign_on_org_id
5116: AND wdtv.wms_task_type_id = NVL(l_sys_task_type, wdtv.wms_task_type_id) -- restrict to picking tasks or all tasks
5117: AND wdtv.user_task_type_id = v.standard_operation_id -- join task to resource view, check if user defined task type match
5866: , -- inlined wms_person_resource_utt_v, bug 2648133
5867: mtl_item_locations loc
5868: , mtl_secondary_inventories sub
5869: , mtl_txn_request_lines mol
5870: -- mtl_system_items msi -- bug 2648133
5871: WHERE v.emp_id = p_sign_on_emp_id -- restrict to sign on employee
5872: AND(wdtv.pick_slip_number = v_pick_slip_number
5873: OR(wdtv.task_id = v_task_id
5874: AND wdtv.wms_task_type_id = v_task_type))
7399: , --changed to kfv bug#2742611
7400: mtl_secondary_inventories sub
7401: , mtl_txn_request_lines mol
7402: , mtl_txn_request_headers moh
7403: , -- mtl_system_items msi -- bug 2648133
7404: wsh_delivery_details_ob_grp_v wdd
7405: , -- added
7406: wsh_delivery_assignments_v wda --added
7407: WHERE v.emp_id = p_sign_on_emp_id -- restrict to sign on employee
7560: , --changed to kfv bug#2742611
7561: mtl_secondary_inventories sub
7562: , mtl_txn_request_lines mol
7563: , mtl_txn_request_headers moh
7564: -- mtl_system_items msi -- bug 2648133
7565: WHERE v.emp_id = p_sign_on_emp_id -- restrict to sign on employee
7566: AND wdtv.organization_id = p_sign_on_org_id
7567: AND wdtv.wms_task_type_id = 1 -- restrict to picking tasks
7568: AND mol.header_id = moh.header_id
7720: , --changed to kfv bug#2742611
7721: mtl_secondary_inventories sub
7722: , mtl_txn_request_lines mol
7723: , mtl_txn_request_headers moh
7724: , -- mtl_system_items msi -- bug 2648133
7725: wsh_delivery_details_ob_grp_v wdd
7726: , -- added
7727: wsh_delivery_assignments_v wda --added
7728: WHERE v.emp_id = p_sign_on_emp_id -- restrict to sign on employee
7880: , --changed to kfv bug#2742611
7881: mtl_secondary_inventories sub
7882: , mtl_txn_request_lines mol
7883: , mtl_txn_request_headers moh
7884: -- mtl_system_items msi -- bug 2648133
7885: WHERE v.emp_id = p_sign_on_emp_id -- restrict to sign on employee
7886: AND wdtv.organization_id = p_sign_on_org_id
7887: AND wdtv.wms_task_type_id = 1 -- restrict to picking tasks
7888: AND mol.header_id = moh.header_id
8009: , --changed to kfv bug#2742611
8010: mtl_secondary_inventories sub
8011: , mtl_txn_request_lines mol
8012: , mtl_txn_request_headers moh
8013: , -- mtl_system_items msi -- bug 2648133
8014: wsh_delivery_details_ob_grp_v wdd
8015: , -- added
8016: wsh_delivery_assignments_v wda --added
8017: WHERE wdt.transaction_temp_id = qt.task_id
8116: , --changed to kfv bug#2742611
8117: mtl_secondary_inventories sub
8118: , mtl_txn_request_lines mol
8119: , mtl_txn_request_headers moh
8120: , -- mtl_system_items msi -- bug 2648133
8121: wsh_delivery_details_ob_grp_v wdd
8122: , -- added
8123: wsh_delivery_assignments_v wda --added
8124: WHERE v.emp_id = p_sign_on_emp_id -- restrict to sign on employee
8251: , --changed to kfv bug#2742611
8252: mtl_secondary_inventories sub
8253: , mtl_txn_request_lines mol
8254: , mtl_txn_request_headers moh
8255: -- mtl_system_items msi -- bug 2648133
8256: WHERE wdt.transaction_temp_id = wdtv.task_id
8257: AND wdt.status in ( 2,3) -- Queued and dispatched tasks only bug 4310093
8258: AND wdt.person_id = p_sign_on_emp_id
8259: AND wdt.organization_id = p_sign_on_org_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: WHERE v.emp_id = p_sign_on_emp_id -- restrict to sign on employee
8354: AND wdtv.organization_id = p_sign_on_org_id
8355: AND wdtv.wms_task_type_id = 1 -- restrict to picking tasks
8356: AND mol.header_id = moh.header_id
8481: , --changed to kfv bug#2742611
8482: mtl_secondary_inventories sub
8483: , mtl_txn_request_lines mol
8484: , mtl_txn_request_headers moh
8485: , -- mtl_system_items msi -- bug 2648133
8486: wsh_delivery_details_ob_grp_v wdd
8487: , -- added
8488: wsh_delivery_assignments_v wda --added
8489: WHERE wdt.transaction_temp_id = qt.task_id
8588: , --changed to kfv bug#2742611
8589: mtl_secondary_inventories sub
8590: , mtl_txn_request_lines mol
8591: , mtl_txn_request_headers moh
8592: , -- mtl_system_items msi -- bug 2648133
8593: wsh_delivery_details_ob_grp_v wdd
8594: , -- added
8595: wsh_delivery_assignments_v wda --added
8596: WHERE v.emp_id = p_sign_on_emp_id -- restrict to sign on employee
8713: , --changed to kfv bug#2742611
8714: mtl_secondary_inventories sub
8715: , mtl_txn_request_lines mol
8716: , mtl_txn_request_headers moh
8717: -- mtl_system_items msi -- bug 2648133
8718: WHERE wdt.transaction_temp_id = wdtv.task_id
8719: AND wdt.status = 2 -- Queued tasks only
8720: AND wdt.person_id = p_sign_on_emp_id
8721: AND wdt.organization_id = p_sign_on_org_id
8812: , --changed to kfv bug#2742611
8813: mtl_secondary_inventories sub
8814: , mtl_txn_request_lines mol
8815: , mtl_txn_request_headers moh
8816: -- mtl_system_items msi -- bug 2648133
8817: WHERE v.emp_id = p_sign_on_emp_id -- restrict to sign on employee
8818: AND wdtv.organization_id = p_sign_on_org_id
8819: AND wdtv.wms_task_type_id = 1 -- restrict to picking tasks
8820: AND mol.header_id = moh.header_id
8945: , --changed to kfv bug#2742611
8946: mtl_secondary_inventories sub
8947: , mtl_txn_request_lines mol
8948: , mtl_txn_request_headers moh
8949: , -- mtl_system_items msi -- bug 2648133
8950: wsh_delivery_details_ob_grp_v wdd
8951: , -- added
8952: wsh_delivery_assignments_v wda --added
8953: WHERE wdt.transaction_temp_id = qt.task_id
9050: , --changed to kfv bug#2742611
9051: mtl_secondary_inventories sub
9052: , mtl_txn_request_lines mol
9053: , mtl_txn_request_headers moh
9054: , -- mtl_system_items msi -- bug 2648133
9055: wsh_delivery_details_ob_grp_v wdd
9056: , -- added
9057: wsh_delivery_assignments_v wda --added
9058: WHERE v.emp_id = p_sign_on_emp_id -- restrict to sign on employee
9182: , --changed to kfv bug#2742611
9183: mtl_secondary_inventories sub
9184: , mtl_txn_request_lines mol
9185: , mtl_txn_request_headers moh
9186: -- mtl_system_items msi -- bug 2648133
9187: WHERE wdt.transaction_temp_id = wdtv.task_id
9188: AND wdt.status in ( 2,3) -- Queued and dispatched tasks only bug 4310093
9189: AND wdt.person_id = p_sign_on_emp_id
9190: AND wdt.organization_id = p_sign_on_org_id
9280: , --changed to kfv bug#2742611
9281: mtl_secondary_inventories sub
9282: , mtl_txn_request_lines mol
9283: , mtl_txn_request_headers moh
9284: -- mtl_system_items msi -- bug 2648133
9285: WHERE v.emp_id = p_sign_on_emp_id -- restrict to sign on employee
9286: AND wdtv.organization_id = p_sign_on_org_id
9287: AND wdtv.wms_task_type_id = 1 -- restrict to picking tasks
9288: AND mol.header_id = moh.header_id
9409: , --changed to kfv bug#2742611
9410: mtl_secondary_inventories sub
9411: , mtl_txn_request_lines mol
9412: , mtl_txn_request_headers moh
9413: , -- mtl_system_items msi -- bug 2648133
9414: wsh_delivery_details_ob_grp_v wdd
9415: , -- added
9416: wsh_delivery_assignments_v wda --added
9417: WHERE wdt.transaction_temp_id = qt.task_id
9514: , --changed to kfv bug#2742611
9515: mtl_secondary_inventories sub
9516: , mtl_txn_request_lines mol
9517: , mtl_txn_request_headers moh
9518: , -- mtl_system_items msi -- bug 2648133
9519: wsh_delivery_details_ob_grp_v wdd
9520: , -- added
9521: wsh_delivery_assignments_v wda --added
9522: WHERE v.emp_id = p_sign_on_emp_id -- restrict to sign on employee
9638: , --changed to kfv bug#2742611
9639: mtl_secondary_inventories sub
9640: , mtl_txn_request_lines mol
9641: , mtl_txn_request_headers moh
9642: -- mtl_system_items msi -- bug 2648133
9643: WHERE wdt.transaction_temp_id = wdtv.task_id
9644: AND wdt.status = 2 -- Queued tasks only
9645: AND wdt.person_id = p_sign_on_emp_id
9646: AND wdt.organization_id = p_sign_on_org_id
9736: , --changed to kfv bug#2742611
9737: mtl_secondary_inventories sub
9738: , mtl_txn_request_lines mol
9739: , mtl_txn_request_headers moh
9740: -- mtl_system_items msi -- bug 2648133
9741: WHERE v.emp_id = p_sign_on_emp_id -- restrict to sign on employee
9742: AND wdtv.organization_id = p_sign_on_org_id
9743: AND wdtv.wms_task_type_id = 1 -- restrict to picking tasks
9744: AND mol.header_id = moh.header_id
10927: , -- equipment weight UOM code
10928: item.volume_uom_code item_v_uom
10929: , -- item volume UOM code
10930: item.weight_uom_code item_w_uom -- item weight UOM code
10931: FROM mtl_system_items equip
10932: , mtl_system_items item
10933: , bom_resource_equipments res_equip
10934: , bom_resources res
10935: , bom_std_op_resources tt_x_res
10928: item.volume_uom_code item_v_uom
10929: , -- item volume UOM code
10930: item.weight_uom_code item_w_uom -- item weight UOM code
10931: FROM mtl_system_items equip
10932: , mtl_system_items item
10933: , bom_resource_equipments res_equip
10934: , bom_resources res
10935: , bom_std_op_resources tt_x_res
10936: WHERE tt_x_res.standard_operation_id = p_standard_operation_id --join task with task_type-resource x-ref
10936: WHERE tt_x_res.standard_operation_id = p_standard_operation_id --join task with task_type-resource x-ref
10937: AND tt_x_res.resource_id = res.resource_id -- join with resource
10938: AND res.resource_type = 1 -- resource type for equipment
10939: AND res_equip.resource_id = tt_x_res.resource_id -- join with resource-equip x-ref
10940: AND equip.inventory_item_id = res_equip.inventory_item_id -- join with equipment (mtl_system_items)
10941: AND equip.organization_id = res_equip.organization_id
10942: AND item.inventory_item_id = p_item_id -- join with item for the item that is transfered
10943: AND item.organization_id = p_organization_id;
10944:
10951: l_progress := '10';
10952:
10953: SELECT primary_uom_code
10954: INTO l_item_prim_uom_code
10955: FROM mtl_system_items
10956: WHERE inventory_item_id = p_item_id
10957: AND organization_id = p_organization_id;
10958:
10959: -- compute conversion rate between transaction UOM and item primary UOM
11186: WHERE spg.pick_grouping_rule_id = moh.grouping_rule_id
11187: AND spg.pick_method = '4'
11188: AND moh.header_id = mol.header_id)
11189: OR EXISTS(SELECT 1
11190: FROM mtl_system_items msi
11191: WHERE msi.inventory_item_id = mmtt.inventory_item_id
11192: AND msi.bulk_picked_flag = 'Y')
11193: )
11194: AND EXISTS ( SELECT 1 -- Only Consolidate Tasks for Plain item
11191: WHERE msi.inventory_item_id = mmtt.inventory_item_id
11192: AND msi.bulk_picked_flag = 'Y')
11193: )
11194: AND EXISTS ( SELECT 1 -- Only Consolidate Tasks for Plain item
11195: FROM mtl_system_items msi2
11196: WHERE msi2.inventory_item_id = mmtt.inventory_item_id
11197: AND msi2.lot_control_code = 1
11198: AND(msi2.serial_number_control_code = 1
11199: OR msi2.serial_number_control_code = 6))
11281: WHERE spg.pick_grouping_rule_id = moh.grouping_rule_id
11282: AND spg.pick_method = '4'
11283: AND moh.header_id = mol.header_id)
11284: OR EXISTS(SELECT 1
11285: FROM mtl_system_items msi
11286: WHERE msi.inventory_item_id = mmtt.inventory_item_id
11287: AND msi.bulk_picked_flag = 'Y')
11288: )
11289: AND EXISTS( SELECT 1 -- Only Consolidate Tasks for Plain item
11286: WHERE msi.inventory_item_id = mmtt.inventory_item_id
11287: AND msi.bulk_picked_flag = 'Y')
11288: )
11289: AND EXISTS( SELECT 1 -- Only Consolidate Tasks for Plain item
11290: FROM mtl_system_items msi2
11291: WHERE msi2.inventory_item_id = mmtt.inventory_item_id
11292: AND msi2.lot_control_code = 1
11293: AND(msi2.serial_number_control_code = 1
11294: OR msi2.serial_number_control_code = 6))
11721: WHERE msi.secondary_inventory_name = mmtt.subinventory_code
11722: AND msi.organization_id = mmtt.organization_id
11723: AND msi.enable_bulk_pick= 'Y')
11724: OR EXISTS(SELECT 1 -- item is bulk picking enabled
11725: FROM mtl_system_items msi
11726: WHERE msi.inventory_item_id = mmtt.inventory_item_id
11727: AND msi.bulk_picked_flag = 'Y')
11728: )
11729: GROUP BY mmtt.inventory_item_id
11793: WHERE msi.secondary_inventory_name = mmtt.subinventory_code
11794: AND msi.organization_id = mmtt.organization_id
11795: AND msi.enable_bulk_pick= 'Y')
11796: OR EXISTS(SELECT 1 -- item is bulk picking enabled
11797: FROM mtl_system_items msi
11798: WHERE msi.inventory_item_id = mmtt.inventory_item_id
11799: AND msi.bulk_picked_flag = 'Y')
11800: )
11801: GROUP BY mmtt.inventory_item_id
12398:
12399: -- to get the serial control code
12400: select serial_number_control_code,lot_control_code
12401: into l_serial_number_control_code,l_lot_control_code
12402: from mtl_system_items_b msi,mtl_material_transactions_temp mmtt
12403: where mmtt.transaction_temp_id = p_parent_transaction_temp_id
12404: and mmtt.inventory_item_id = msi.inventory_item_id
12405: and mmtt.organization_id = msi.organization_id;
12406:
12520: , -- equipment weight UOM code
12521: item.volume_uom_code item_v_uom
12522: , -- item volume UOM code
12523: item.weight_uom_code item_w_uom -- item weight UOM code
12524: FROM mtl_system_items equip
12525: , mtl_system_items item
12526: , wms_cartonization_temp mmtt
12527: , bom_resource_equipments res_equip
12528: , bom_resources res
12521: item.volume_uom_code item_v_uom
12522: , -- item volume UOM code
12523: item.weight_uom_code item_w_uom -- item weight UOM code
12524: FROM mtl_system_items equip
12525: , mtl_system_items item
12526: , wms_cartonization_temp mmtt
12527: , bom_resource_equipments res_equip
12528: , bom_resources res
12529: , bom_std_op_resources tt_x_res
12531: AND mmtt.standard_operation_id = tt_x_res.standard_operation_id --join task with task_type-resource x-ref
12532: AND tt_x_res.resource_id = res.resource_id -- join with resource
12533: AND res.resource_type = 1 -- resource type for equipment
12534: AND res_equip.resource_id = tt_x_res.resource_id -- join with resource-equip x-ref
12535: AND equip.inventory_item_id = res_equip.inventory_item_id -- join with equipment (mtl_system_items)
12536: AND equip.organization_id = res_equip.organization_id
12537: AND item.inventory_item_id = mmtt.inventory_item_id -- join with item for the item that is transfered
12538: AND item.organization_id = mmtt.organization_id;
12539:
12557: , -- equipment weight UOM code
12558: item.volume_uom_code item_v_uom
12559: , -- item volume UOM code
12560: item.weight_uom_code item_w_uom -- item weight UOM code
12561: FROM mtl_system_items equip
12562: , mtl_system_items item
12563: , mtl_material_transactions_temp mmtt
12564: , bom_resource_equipments res_equip
12565: , bom_resources res
12558: item.volume_uom_code item_v_uom
12559: , -- item volume UOM code
12560: item.weight_uom_code item_w_uom -- item weight UOM code
12561: FROM mtl_system_items equip
12562: , mtl_system_items item
12563: , mtl_material_transactions_temp mmtt
12564: , bom_resource_equipments res_equip
12565: , bom_resources res
12566: , bom_std_op_resources tt_x_res
12568: AND mmtt.standard_operation_id = tt_x_res.standard_operation_id --join task with task_type-resource x-ref
12569: AND tt_x_res.resource_id = res.resource_id -- join with resource
12570: AND res.resource_type = 1 -- resource type for equipment
12571: AND res_equip.resource_id = tt_x_res.resource_id -- join with resource-equip x-ref
12572: AND equip.inventory_item_id = res_equip.inventory_item_id -- join with equipment (mtl_system_items)
12573: AND equip.organization_id = res_equip.organization_id
12574: AND item.inventory_item_id = mmtt.inventory_item_id -- join with item for the item that is transfered
12575: AND item.organization_id = mmtt.organization_id;
12576:
12658: , l_init_qty
12659: , l_item_prim_uom_code
12660: , l_lot_control_code
12661: , l_serial_number_control_code
12662: FROM wms_cartonization_temp mmtt, mtl_item_locations mil, mtl_secondary_inventories msi, mtl_system_items item
12663: WHERE mmtt.transaction_temp_id = p_task_id
12664: AND mmtt.locator_id = mil.inventory_location_id(+)
12665: AND mmtt.organization_id = mil.organization_id(+)
12666: AND mmtt.subinventory_code = msi.secondary_inventory_name