[Home] [Help]
20: -- the most optimal task based on priority, locator picking
21: -- sequence, coordinates approximation, etc.
22: -- or reservation input parameters and creates recommendations
23: -- Pre-reqs : 1. For each record in MTL_MATERIAL_TRANSACTIONS_TEMP, user
24: -- defined task type (standard_operation_id column ) has been
25: -- assigned,
26: -- 2. System task type (wms_task_type column) has been assigned
27: -- 3. Pick slip
28: -- number (pick_slip_number column) has been assigned
27: -- 3. Pick slip
28: -- number (pick_slip_number column) has been assigned
29: --
30: -- Parameters :
31: -- p_api_version Standard Input Parameter
32: -- p_init_msg_list Standard Input Parameter
33: -- p_commit Standard Input Parameter
34: -- p_validation_level Standard Input Parameter
35: -- p_sign_on_emp_id NUMBER, sign on emplployee ID, mandatory
28: -- number (pick_slip_number column) has been assigned
29: --
30: -- Parameters :
31: -- p_api_version Standard Input Parameter
32: -- p_init_msg_list Standard Input Parameter
33: -- p_commit Standard Input Parameter
34: -- p_validation_level Standard Input Parameter
35: -- p_sign_on_emp_id NUMBER, sign on emplployee ID, mandatory
36: -- p_sign_on_org_id NUMBER, org ID, mandatory
29: --
30: -- Parameters :
31: -- p_api_version Standard Input Parameter
32: -- p_init_msg_list Standard Input Parameter
33: -- p_commit Standard Input Parameter
34: -- p_validation_level Standard Input Parameter
35: -- p_sign_on_emp_id NUMBER, sign on emplployee ID, mandatory
36: -- p_sign_on_org_id NUMBER, org ID, mandatory
37: -- p_sign_on_zone VARCHAR2, sign on sub ID, optional
30: -- Parameters :
31: -- p_api_version Standard Input Parameter
32: -- p_init_msg_list Standard Input Parameter
33: -- p_commit Standard Input Parameter
34: -- p_validation_level Standard Input Parameter
35: -- p_sign_on_emp_id NUMBER, sign on emplployee ID, mandatory
36: -- p_sign_on_org_id NUMBER, org ID, mandatory
37: -- p_sign_on_zone VARCHAR2, sign on sub ID, optional
38: -- p_sign_on_equipment_id NUMBER, sign on equipment item ID, optional,
45: -- can be 'PICKING', 'ALL', 'DISPLAY'
46: --
47: --
48: -- Output Parameters
49: -- x_return_status Standard Output Parameter
50: -- x_msg_count Standard Output Parameter
51: -- x_msg_data Standard Output Parameter
52: -- x_task_cur Reference Cursor to deliver the queried tasks
53: -- It includes following fields:
46: --
47: --
48: -- Output Parameters
49: -- x_return_status Standard Output Parameter
50: -- x_msg_count Standard Output Parameter
51: -- x_msg_data Standard Output Parameter
52: -- x_task_cur Reference Cursor to deliver the queried tasks
53: -- It includes following fields:
54: -- mmtt.transaction_temp_id NUMBER
47: --
48: -- Output Parameters
49: -- x_return_status Standard Output Parameter
50: -- x_msg_count Standard Output Parameter
51: -- x_msg_data Standard Output Parameter
52: -- x_task_cur Reference Cursor to deliver the queried tasks
53: -- It includes following fields:
54: -- mmtt.transaction_temp_id NUMBER
55: -- mmtt.subinventory_code VARCHAR2
284: l_lot_number VARCHAR2(80);
285: l_revision VARCHAR2(3);
286: l_operation_plan_id NUMBER;
287: l_move_order_line_id NUMBER;
288: l_standard_operation_id NUMBER;
289: l_effective_start_date DATE;
290: l_effective_end_date DATE;
291: l_person_resource_id NUMBER;
292: l_machine_resource_id NUMBER;
390: mtl_txn_request_lines mol,
391: mtl_secondary_inventories sub,
392: mtl_item_locations loc,
393: (SELECT
394: bsor.standard_operation_id,
395: bre.resource_id,
396: bre.inventory_item_id equipment_id
397: FROM bom_std_op_resources bsor, bom_resources br, bom_resource_equipments bre
398: WHERE bsor.resource_id = bre.resource_id
403: AND wdt.person_id = p_sign_on_emp_id
404: AND wdt.organization_id = p_sign_on_org_id
405: AND NVL(qt.cartonization_id, -999) = NVL(l_cartonization_id, NVL(qt.cartonization_id, -999))
406: AND NVL(e.equipment_id, -999) = NVL(l_sign_on_equipment_id, NVL(e.equipment_id, -999))
407: AND wdt.user_task_type = e.standard_operation_id(+)
408: AND qt.move_order_line_id = mol.line_id(+)
409: AND qt.subinventory_code = p_sign_on_zone
410: AND qt.organization_id = sub.organization_id
411: AND qt.subinventory_code = sub.secondary_inventory_name
431: FROM
432: -- inlined wms_dispatchable_tasks_v, bug 2648133
433: (SELECT
434: transaction_temp_id task_id,
435: standard_operation_id user_task_type_id,
436: wms_task_type wms_task_type_id,
437: organization_id,
438: subinventory_code zone,
439: locator_id,
478:
479: UNION ALL
480: SELECT
481: MIN(mcce.cycle_count_entry_id) task_id,
482: MIN(mcce.standard_operation_id) user_task_type_id,
483: 3 wms_task_type_id,
484: mcce.organization_id,
485: mcce.subinventory zone,
486: mcce.locator_id,
514: AND NVL(mcch.disable_date,sysdate+1)> sysdate
515: GROUP BY mcce.cycle_count_header_id, mcce.organization_id, mcce.subinventory, mcce.locator_id, mcce.inventory_item_id, mcce.revision) wdtv,
516: -- inlined wms_person_resource_utt_v, bug 2648133
517: (SELECT
518: utt_emp.standard_operation_id standard_operation_id,
519: utt_emp.resource_id role,
520: utt_eqp.resource_id equipment,
521: utt_emp.person_id emp_id,
522: utt_eqp.inventory_item_id eqp_id,
522: utt_eqp.inventory_item_id eqp_id,
523: NULL eqp_srl /* removed for bug 2095237 */
524: FROM
525: (SELECT
526: x_utt_res1.standard_operation_id standard_operation_id,
527: x_utt_res1.resource_id resource_id,
528: x_emp_r.person_id
529: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
530: WHERE x_utt_res1.resource_id = r1.resource_id
530: WHERE x_utt_res1.resource_id = r1.resource_id
531: AND r1.resource_type = 2
532: AND x_utt_res1.resource_id = x_emp_r.resource_id) utt_emp,
533: (SELECT
534: x_utt_res2.standard_operation_id standard_operation_id,
535: x_utt_res2.resource_id,
536: x_eqp_r.inventory_item_id
537: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
538: WHERE x_utt_res2.resource_id = r2.resource_id
537: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
538: WHERE x_utt_res2.resource_id = r2.resource_id
539: AND r2.resource_type = 1
540: AND x_utt_res2.resource_id = x_eqp_r.resource_id) utt_eqp
541: WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)) v,
542: mtl_item_locations loc,
543: mtl_secondary_inventories sub,
544: mtl_txn_request_lines mol,
545: mtl_txn_request_headers moh
544: mtl_txn_request_lines mol,
545: mtl_txn_request_headers moh
546: WHERE v.emp_id = p_sign_on_emp_id -- restrict to sign on employee
547: AND wdtv.organization_id = p_sign_on_org_id
548: AND wdtv.user_task_type_id = v.standard_operation_id -- join task to resource view, check if user defined task type match
549: AND wdtv.zone = p_sign_on_zone -- removed NVL, bug 2648133
550: AND NVL(wdtv.cartonization_id, -999) = NVL(l_cartonization_id, NVL(wdtv.cartonization_id, -999))
551: AND NVL(v.eqp_id, -999) = NVL(l_sign_on_equipment_id, NVL(v.eqp_id, -999))
552: AND wdtv.organization_id = loc.organization_id(+) -- join task to loc, outer join for tasks do not have locator
693: mtl_txn_request_lines mol,
694: mtl_secondary_inventories sub,
695: mtl_item_locations loc,
696: (SELECT
697: bsor.standard_operation_id,
698: bre.resource_id,
699: bre.inventory_item_id equipment_id
700: FROM bom_std_op_resources bsor, bom_resources br, bom_resource_equipments bre
701: WHERE bsor.resource_id = bre.resource_id
706: AND wdt.person_id = p_sign_on_emp_id
707: AND wdt.organization_id = p_sign_on_org_id
708: AND NVL(qt.cartonization_id, -999) = NVL(l_cartonization_id, NVL(qt.cartonization_id, -999))
709: AND NVL(e.equipment_id, -999) = NVL(l_q_sign_on_equipment_id, NVL(e.equipment_id, -999))--bug 6326482
710: AND wdt.user_task_type = e.standard_operation_id(+)
711: AND qt.move_order_line_id = mol.line_id(+)
712: AND qt.subinventory_code = p_sign_on_zone
713: AND qt.organization_id = sub.organization_id
714: AND qt.subinventory_code = sub.secondary_inventory_name
771: mtl_txn_request_lines mol,
772: mtl_secondary_inventories sub,
773: mtl_item_locations loc,
774: (SELECT
775: bsor.standard_operation_id,
776: bre.resource_id,
777: bre.inventory_item_id equipment_id
778: FROM bom_std_op_resources bsor, bom_resources br, bom_resource_equipments bre
779: WHERE bsor.resource_id = bre.resource_id
789: AND NVL(e.equipment_id, -999) = NVL(l_q_sign_on_equipment_id, NVL(e.equipment_id, -999)))
790: or
791: (qt.task_status = 1
792: AND NVL(e.equipment_id, -999) = NVL(l_sign_on_equipment_id, NVL(e.equipment_id, -999))))--bug 6326482
793: AND wdt.user_task_type = e.standard_operation_id(+)
794: AND qt.move_order_line_id = mol.line_id(+)
795: AND qt.subinventory_code = p_sign_on_zone
796: AND qt.organization_id = sub.organization_id
797: AND qt.subinventory_code = sub.secondary_inventory_name
817: FROM
818: -- inlined wms_dispatchable_tasks_v, bug 2648133
819: (SELECT
820: transaction_temp_id task_id,
821: standard_operation_id user_task_type_id,
822: wms_task_type wms_task_type_id,
823: organization_id,
824: subinventory_code zone,
825: locator_id,
863: )
864: ) wdtv,
865: -- inlined wms_person_resource_utt_v, bug 2648133
866: (SELECT
867: utt_emp.standard_operation_id standard_operation_id,
868: utt_emp.resource_id role,
869: utt_eqp.resource_id equipment,
870: utt_emp.person_id emp_id,
871: utt_eqp.inventory_item_id eqp_id,
871: utt_eqp.inventory_item_id eqp_id,
872: NULL eqp_srl /* removed for bug 2095237 */
873: FROM
874: (SELECT
875: x_utt_res1.standard_operation_id standard_operation_id,
876: x_utt_res1.resource_id resource_id,
877: x_emp_r.person_id
878: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
879: WHERE x_utt_res1.resource_id = r1.resource_id
879: WHERE x_utt_res1.resource_id = r1.resource_id
880: AND r1.resource_type = 2
881: AND x_utt_res1.resource_id = x_emp_r.resource_id) utt_emp,
882: (SELECT
883: x_utt_res2.standard_operation_id standard_operation_id,
884: x_utt_res2.resource_id,
885: x_eqp_r.inventory_item_id
886: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
887: WHERE x_utt_res2.resource_id = r2.resource_id
886: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
887: WHERE x_utt_res2.resource_id = r2.resource_id
888: AND r2.resource_type = 1
889: AND x_utt_res2.resource_id = x_eqp_r.resource_id) utt_eqp
890: WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)) v,
891: mtl_item_locations loc,
892: mtl_secondary_inventories sub,
893: mtl_txn_request_lines mol,
894: mtl_txn_request_headers moh
893: mtl_txn_request_lines mol,
894: mtl_txn_request_headers moh
895: WHERE v.emp_id = p_sign_on_emp_id -- restrict to sign on employee
896: AND wdtv.organization_id = p_sign_on_org_id
897: AND wdtv.user_task_type_id = v.standard_operation_id -- join task to resource view, check if user defined task type match
898: AND wdtv.zone = p_sign_on_zone -- removed NVL, bug 2648133
899: AND NVL(wdtv.cartonization_id, -999) = NVL(l_cartonization_id, NVL(wdtv.cartonization_id, -999))
900: AND NVL(v.eqp_id, -999) = NVL(l_sign_on_equipment_id, NVL(v.eqp_id, -999))
901: AND wdtv.organization_id = loc.organization_id(+) -- join task to loc, outer join for tasks do not have locator
1034: GROUP BY mcce.cycle_count_header_id, mcce.organization_id, mcce.subinventory, mcce.locator_id, mcce.inventory_item_id, mcce.revision) qt,
1035: mtl_secondary_inventories sub,
1036: mtl_item_locations loc,
1037: (SELECT
1038: bsor.standard_operation_id,
1039: bre.resource_id,
1040: bre.inventory_item_id equipment_id
1041: FROM bom_std_op_resources bsor, bom_resources br, bom_resource_equipments bre
1042: WHERE bsor.resource_id = bre.resource_id
1047: AND wdt.person_id = p_sign_on_emp_id
1048: AND wdt.organization_id = p_sign_on_org_id
1049: AND NVL(qt.cartonization_id, -999) = NVL(l_cartonization_id, NVL(qt.cartonization_id, -999))
1050: AND NVL(e.equipment_id, -999) = NVL(l_sign_on_equipment_id, NVL(e.equipment_id, -999))
1051: AND wdt.user_task_type = e.standard_operation_id(+)
1052: AND qt.organization_id = sub.organization_id
1053: AND qt.subinventory_code = p_sign_on_zone
1054: AND qt.subinventory_code = sub.secondary_inventory_name
1055: AND qt.organization_id = loc.organization_id
1074: FROM
1075: -- inlined wms_dispatchable_tasks_v, bug 2648133
1076: (SELECT
1077: MIN(mcce.cycle_count_entry_id) task_id,
1078: MIN(mcce.standard_operation_id) user_task_type_id,
1079: 3 wms_task_type_id,
1080: mcce.organization_id,
1081: mcce.subinventory zone,
1082: mcce.locator_id,
1109: AND NVL(mcch.disable_date,sysdate+1)> sysdate
1110: GROUP BY mcce.cycle_count_header_id, mcce.organization_id, mcce.subinventory, mcce.locator_id, mcce.inventory_item_id, mcce.revision) wdtv,
1111: -- inlined wms_person_resource_utt_v, bug 2648133
1112: (SELECT
1113: utt_emp.standard_operation_id standard_operation_id,
1114: utt_emp.resource_id role,
1115: utt_eqp.resource_id equipment,
1116: utt_emp.person_id emp_id,
1117: utt_eqp.inventory_item_id eqp_id,
1117: utt_eqp.inventory_item_id eqp_id,
1118: NULL eqp_srl /* removed for bug 2095237 */
1119: FROM
1120: (SELECT
1121: x_utt_res1.standard_operation_id standard_operation_id,
1122: x_utt_res1.resource_id resource_id,
1123: x_emp_r.person_id
1124: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
1125: WHERE x_utt_res1.resource_id = r1.resource_id
1125: WHERE x_utt_res1.resource_id = r1.resource_id
1126: AND r1.resource_type = 2
1127: AND x_utt_res1.resource_id = x_emp_r.resource_id) utt_emp,
1128: (SELECT
1129: x_utt_res2.standard_operation_id standard_operation_id,
1130: x_utt_res2.resource_id,
1131: x_eqp_r.inventory_item_id
1132: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
1133: WHERE x_utt_res2.resource_id = r2.resource_id
1132: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
1133: WHERE x_utt_res2.resource_id = r2.resource_id
1134: AND r2.resource_type = 1
1135: AND x_utt_res2.resource_id = x_eqp_r.resource_id) utt_eqp
1136: WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)) v,
1137: mtl_item_locations loc,
1138: mtl_secondary_inventories sub
1139: WHERE v.emp_id = p_sign_on_emp_id -- restrict to sign on employee
1140: AND wdtv.organization_id = p_sign_on_org_id
1137: mtl_item_locations loc,
1138: mtl_secondary_inventories sub
1139: WHERE v.emp_id = p_sign_on_emp_id -- restrict to sign on employee
1140: AND wdtv.organization_id = p_sign_on_org_id
1141: AND wdtv.user_task_type_id = v.standard_operation_id -- join task to resource view, check if user defined task type match
1142: AND wdtv.zone = p_sign_on_zone -- removed NVL, bug 2648133
1143: AND NVL(wdtv.cartonization_id, -999) = NVL(l_cartonization_id, NVL(wdtv.cartonization_id, -999))
1144: AND NVL(v.eqp_id, -999) = NVL(l_sign_on_equipment_id, NVL(v.eqp_id, -999))
1145: AND wdtv.organization_id = loc.organization_id(+) -- join task to loc, outer join for tasks do not have locator
1298: mtl_txn_request_lines mol,
1299: mtl_secondary_inventories sub,
1300: mtl_item_locations loc,
1301: (SELECT
1302: bsor.standard_operation_id,
1303: bre.resource_id,
1304: bre.inventory_item_id equipment_id
1305: FROM bom_std_op_resources bsor, bom_resources br, bom_resource_equipments bre
1306: WHERE bsor.resource_id = bre.resource_id
1311: AND wdt.person_id = p_sign_on_emp_id
1312: AND wdt.organization_id = p_sign_on_org_id
1313: AND NVL(qt.cartonization_id, -999) = NVL(l_cartonization_id, NVL(qt.cartonization_id, -999))
1314: AND NVL(e.equipment_id, -999) = NVL(l_sign_on_equipment_id, NVL(e.equipment_id, -999))
1315: AND wdt.user_task_type = e.standard_operation_id(+)
1316: AND qt.move_order_line_id = mol.line_id(+)
1317: AND qt.organization_id = sub.organization_id
1318: AND qt.subinventory_code = sub.secondary_inventory_name
1319: AND qt.organization_id = loc.organization_id
1338: FROM
1339: -- inlined wms_dispatchable_tasks_v, bug 2648133
1340: (SELECT
1341: transaction_temp_id task_id,
1342: standard_operation_id user_task_type_id,
1343: wms_task_type wms_task_type_id,
1344: organization_id,
1345: subinventory_code zone,
1346: locator_id,
1385:
1386: UNION ALL
1387: SELECT
1388: MIN(mcce.cycle_count_entry_id) task_id,
1389: MIN(mcce.standard_operation_id) user_task_type_id,
1390: 3 wms_task_type_id,
1391: mcce.organization_id,
1392: mcce.subinventory zone,
1393: mcce.locator_id,
1421:
1422: GROUP BY mcce.cycle_count_header_id, mcce.organization_id, mcce.subinventory, mcce.locator_id, mcce.inventory_item_id, mcce.revision) wdtv,
1423: -- inlined wms_person_resource_utt_v, bug 2648133
1424: (SELECT
1425: utt_emp.standard_operation_id standard_operation_id,
1426: utt_emp.resource_id role,
1427: utt_eqp.resource_id equipment,
1428: utt_emp.person_id emp_id,
1429: utt_eqp.inventory_item_id eqp_id,
1429: utt_eqp.inventory_item_id eqp_id,
1430: NULL eqp_srl /* removed for bug 2095237 */
1431: FROM
1432: (SELECT
1433: x_utt_res1.standard_operation_id standard_operation_id,
1434: x_utt_res1.resource_id resource_id,
1435: x_emp_r.person_id
1436: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
1437: WHERE x_utt_res1.resource_id = r1.resource_id
1437: WHERE x_utt_res1.resource_id = r1.resource_id
1438: AND r1.resource_type = 2
1439: AND x_utt_res1.resource_id = x_emp_r.resource_id) utt_emp,
1440: (SELECT
1441: x_utt_res2.standard_operation_id standard_operation_id,
1442: x_utt_res2.resource_id,
1443: x_eqp_r.inventory_item_id
1444: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
1445: WHERE x_utt_res2.resource_id = r2.resource_id
1444: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
1445: WHERE x_utt_res2.resource_id = r2.resource_id
1446: AND r2.resource_type = 1
1447: AND x_utt_res2.resource_id = x_eqp_r.resource_id) utt_eqp
1448: WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)) v,
1449: mtl_item_locations loc,
1450: mtl_secondary_inventories sub,
1451: mtl_txn_request_lines mol,
1452: mtl_txn_request_headers moh
1451: mtl_txn_request_lines mol,
1452: mtl_txn_request_headers moh
1453: WHERE v.emp_id = p_sign_on_emp_id -- restrict to sign on employee
1454: AND wdtv.organization_id = p_sign_on_org_id
1455: AND wdtv.user_task_type_id = v.standard_operation_id -- join task to resource view, check if user defined task type match
1456: AND NVL(wdtv.cartonization_id, -999) = NVL(l_cartonization_id, NVL(wdtv.cartonization_id, -999))
1457: AND NVL(v.eqp_id, -999) = NVL(l_sign_on_equipment_id, NVL(v.eqp_id, -999))
1458: AND wdtv.organization_id = loc.organization_id(+) -- join task to loc, outer join for tasks do not have locator
1459: AND wdtv.locator_id = loc.inventory_location_id(+)
1600: mtl_txn_request_lines mol,
1601: mtl_secondary_inventories sub,
1602: mtl_item_locations loc,
1603: (SELECT
1604: bsor.standard_operation_id,
1605: bre.resource_id,
1606: bre.inventory_item_id equipment_id
1607: FROM bom_std_op_resources bsor, bom_resources br, bom_resource_equipments bre
1608: WHERE bsor.resource_id = bre.resource_id
1613: AND wdt.person_id = p_sign_on_emp_id
1614: AND wdt.organization_id = p_sign_on_org_id
1615: AND NVL(qt.cartonization_id, -999) = NVL(l_cartonization_id, NVL(qt.cartonization_id, -999))
1616: AND NVL(e.equipment_id, -999) = NVL(l_q_sign_on_equipment_id, NVL(e.equipment_id, -999))
1617: AND wdt.user_task_type = e.standard_operation_id(+)
1618: AND qt.move_order_line_id = mol.line_id(+)
1619: AND qt.organization_id = sub.organization_id
1620: AND qt.subinventory_code = sub.secondary_inventory_name
1621: AND qt.organization_id = loc.organization_id
1677: mtl_txn_request_lines mol,
1678: mtl_secondary_inventories sub,
1679: mtl_item_locations loc,
1680: (SELECT
1681: bsor.standard_operation_id,
1682: bre.resource_id,
1683: bre.inventory_item_id equipment_id
1684: FROM bom_std_op_resources bsor, bom_resources br, bom_resource_equipments bre
1685: WHERE bsor.resource_id = bre.resource_id
1695: AND NVL(e.equipment_id, -999) = NVL(l_q_sign_on_equipment_id, NVL(e.equipment_id, -999)))
1696: or
1697: (qt.task_status = 1
1698: AND NVL(e.equipment_id, -999) = NVL(l_sign_on_equipment_id, NVL(e.equipment_id, -999))))--bug 6326482
1699: AND wdt.user_task_type = e.standard_operation_id(+)
1700: AND qt.move_order_line_id = mol.line_id(+)
1701: AND qt.organization_id = sub.organization_id
1702: AND qt.subinventory_code = sub.secondary_inventory_name
1703: AND qt.organization_id = loc.organization_id
1722: FROM
1723: -- inlined wms_dispatchable_tasks_v, bug 2648133
1724: (SELECT
1725: transaction_temp_id task_id,
1726: standard_operation_id user_task_type_id,
1727: wms_task_type wms_task_type_id,
1728: organization_id,
1729: subinventory_code zone,
1730: locator_id,
1768: )
1769: ) wdtv,
1770: -- inlined wms_person_resource_utt_v, bug 2648133
1771: (SELECT
1772: utt_emp.standard_operation_id standard_operation_id,
1773: utt_emp.resource_id role,
1774: utt_eqp.resource_id equipment,
1775: utt_emp.person_id emp_id,
1776: utt_eqp.inventory_item_id eqp_id,
1776: utt_eqp.inventory_item_id eqp_id,
1777: NULL eqp_srl /* removed for bug 2095237 */
1778: FROM
1779: (SELECT
1780: x_utt_res1.standard_operation_id standard_operation_id,
1781: x_utt_res1.resource_id resource_id,
1782: x_emp_r.person_id
1783: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
1784: WHERE x_utt_res1.resource_id = r1.resource_id
1784: WHERE x_utt_res1.resource_id = r1.resource_id
1785: AND r1.resource_type = 2
1786: AND x_utt_res1.resource_id = x_emp_r.resource_id) utt_emp,
1787: (SELECT
1788: x_utt_res2.standard_operation_id standard_operation_id,
1789: x_utt_res2.resource_id,
1790: x_eqp_r.inventory_item_id
1791: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
1792: WHERE x_utt_res2.resource_id = r2.resource_id
1791: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
1792: WHERE x_utt_res2.resource_id = r2.resource_id
1793: AND r2.resource_type = 1
1794: AND x_utt_res2.resource_id = x_eqp_r.resource_id) utt_eqp
1795: WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)) v,
1796: mtl_item_locations loc,
1797: mtl_secondary_inventories sub,
1798: mtl_txn_request_lines mol,
1799: mtl_txn_request_headers moh
1798: mtl_txn_request_lines mol,
1799: mtl_txn_request_headers moh
1800: WHERE v.emp_id = p_sign_on_emp_id -- restrict to sign on employee
1801: AND wdtv.organization_id = p_sign_on_org_id
1802: AND wdtv.user_task_type_id = v.standard_operation_id -- join task to resource view, check if user defined task type match
1803: AND NVL(wdtv.cartonization_id, -999) = NVL(l_cartonization_id, NVL(wdtv.cartonization_id, -999))
1804: AND NVL(v.eqp_id, -999) = NVL(l_sign_on_equipment_id, NVL(v.eqp_id, -999))
1805: AND wdtv.organization_id = loc.organization_id(+) -- join task to loc, outer join for tasks do not have locator
1806: AND wdtv.locator_id = loc.inventory_location_id(+)
1933: GROUP BY mcce.cycle_count_header_id, mcce.organization_id, mcce.subinventory, mcce.locator_id, mcce.inventory_item_id, mcce.revision) qt,
1934: mtl_secondary_inventories sub,
1935: mtl_item_locations loc,
1936: (SELECT
1937: bsor.standard_operation_id,
1938: bre.resource_id,
1939: bre.inventory_item_id equipment_id
1940: FROM bom_std_op_resources bsor, bom_resources br, bom_resource_equipments bre
1941: WHERE bsor.resource_id = bre.resource_id
1946: AND wdt.person_id = p_sign_on_emp_id
1947: AND wdt.organization_id = p_sign_on_org_id
1948: AND NVL(qt.cartonization_id, -999) = NVL(l_cartonization_id, NVL(qt.cartonization_id, -999))
1949: AND NVL(e.equipment_id, -999) = NVL(l_sign_on_equipment_id, NVL(e.equipment_id, -999))
1950: AND wdt.user_task_type = e.standard_operation_id(+)
1951: AND qt.organization_id = sub.organization_id
1952: AND qt.subinventory_code = sub.secondary_inventory_name
1953: AND qt.organization_id = loc.organization_id
1954: AND qt.locator_id = loc.inventory_location_id
1972: FROM
1973: -- inlined wms_dispatchable_tasks_v, bug 2648133
1974: (SELECT
1975: MIN(mcce.cycle_count_entry_id) task_id,
1976: MIN(mcce.standard_operation_id) user_task_type_id,
1977: 3 wms_task_type_id,
1978: mcce.organization_id,
1979: mcce.subinventory zone,
1980: mcce.locator_id,
2008: AND NVL(mcch.disable_date,sysdate+1)> sysdate
2009: GROUP BY mcce.cycle_count_header_id, mcce.organization_id, mcce.subinventory, mcce.locator_id, mcce.inventory_item_id, mcce.revision) wdtv,
2010: -- inlined wms_person_resource_utt_v, bug 2648133
2011: (SELECT
2012: utt_emp.standard_operation_id standard_operation_id,
2013: utt_emp.resource_id role,
2014: utt_eqp.resource_id equipment,
2015: utt_emp.person_id emp_id,
2016: utt_eqp.inventory_item_id eqp_id,
2016: utt_eqp.inventory_item_id eqp_id,
2017: NULL eqp_srl /* removed for bug 2095237 */
2018: FROM
2019: (SELECT
2020: x_utt_res1.standard_operation_id standard_operation_id,
2021: x_utt_res1.resource_id resource_id,
2022: x_emp_r.person_id
2023: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
2024: WHERE x_utt_res1.resource_id = r1.resource_id
2024: WHERE x_utt_res1.resource_id = r1.resource_id
2025: AND r1.resource_type = 2
2026: AND x_utt_res1.resource_id = x_emp_r.resource_id) utt_emp,
2027: (SELECT
2028: x_utt_res2.standard_operation_id standard_operation_id,
2029: x_utt_res2.resource_id,
2030: x_eqp_r.inventory_item_id
2031: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
2032: WHERE x_utt_res2.resource_id = r2.resource_id
2031: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
2032: WHERE x_utt_res2.resource_id = r2.resource_id
2033: AND r2.resource_type = 1
2034: AND x_utt_res2.resource_id = x_eqp_r.resource_id) utt_eqp
2035: WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)) v,
2036: mtl_item_locations loc,
2037: mtl_secondary_inventories sub
2038: WHERE v.emp_id = p_sign_on_emp_id -- restrict to sign on employee
2039: AND wdtv.organization_id = p_sign_on_org_id
2036: mtl_item_locations loc,
2037: mtl_secondary_inventories sub
2038: WHERE v.emp_id = p_sign_on_emp_id -- restrict to sign on employee
2039: AND wdtv.organization_id = p_sign_on_org_id
2040: AND wdtv.user_task_type_id = v.standard_operation_id -- join task to resource view, check if user defined task type match
2041: AND NVL(wdtv.cartonization_id, -999) = NVL(l_cartonization_id, NVL(wdtv.cartonization_id, -999))
2042: AND NVL(v.eqp_id, -999) = NVL(l_sign_on_equipment_id, NVL(v.eqp_id, -999))
2043: AND wdtv.organization_id = loc.organization_id(+) -- join task to loc, outer join for tasks do not have locator
2044: AND wdtv.locator_id = loc.inventory_location_id(+)
2128: qt.transaction_uom,
2129: qt.transaction_quantity,
2130: qt.lot_number,
2131: qt.operation_plan_id,
2132: qt.standard_operation_id,
2133: wdt.effective_start_date,
2134: wdt.effective_end_date,
2135: wdt.person_resource_id,
2136: wdt.machine_resource_id,
2161: transaction_uom,
2162: transaction_quantity,
2163: lot_number,
2164: operation_plan_id,
2165: standard_operation_id,
2166: move_order_line_id,
2167: transaction_type_id,
2168: transaction_action_id,
2169: transaction_source_id,
2205: To_char(NULL) transaction_uom,
2206: To_number(NULL) transaction_quantity,
2207: MIN(mcce.lot_number) lot_number,
2208: To_number(NULL) operation_plan_id,
2209: MIN(mcce.standard_operation_id) standard_operation_id,
2210: To_number(NULL) move_order_line_id,
2211: 4 transaction_type_id,
2212: 4 transaction_action_id,
2213: mcce.cycle_count_header_id transaction_source_id,
2230: mtl_txn_request_lines mol,
2231: mtl_secondary_inventories sub,
2232: mtl_item_locations loc,
2233: (SELECT
2234: bsor.standard_operation_id,
2235: bre.resource_id,
2236: bre.inventory_item_id equipment_id
2237: FROM bom_std_op_resources bsor, bom_resources br, bom_resource_equipments bre
2238: WHERE bsor.resource_id = bre.resource_id
2273: --Added for Case Picking Project end
2274: )
2275: -- Bug: 7254397
2276: AND NVL(e.equipment_id, -999) = NVL(l_sign_on_equipment_id, NVL(e.equipment_id, -999))
2277: AND wdt.user_task_type = e.standard_operation_id(+)
2278: AND qt.move_order_line_id = mol.line_id(+)
2279: AND NVL(qt.subinventory_code, '@@@') = NVL(p_sign_on_zone, NVL(qt.subinventory_code, '@@@'))
2280: AND qt.organization_id = sub.organization_id
2281: AND qt.subinventory_code = sub.secondary_inventory_name
2290: wdtv.transaction_uom,
2291: wdtv.transaction_quantity,
2292: wdtv.lot_number,
2293: wdtv.operation_plan_id,
2294: wdtv.user_task_type_id standard_operation_id,
2295: v.effective_start_date,
2296: v.effective_end_date,
2297: v.role person_resource_id,
2298: v.equipment machine_resource_id,
2313: FROM
2314: -- inlined wms_dispatchable_tasks_v, bug 2648133
2315: (SELECT
2316: transaction_temp_id task_id,
2317: standard_operation_id user_task_type_id,
2318: wms_task_type wms_task_type_id,
2319: organization_id,
2320: subinventory_code,
2321: locator_id,
2362:
2363: UNION ALL
2364: SELECT
2365: MIN(mcce.cycle_count_entry_id) task_id,
2366: MIN(mcce.standard_operation_id) user_task_type_id,
2367: 3 wms_task_type_id,
2368: mcce.organization_id,
2369: mcce.subinventory subinventory_code,
2370: mcce.locator_id,
2399: AND NVL(mcch.disable_date,sysdate+1)> sysdate
2400: GROUP BY mcce.cycle_count_header_id, mcce.organization_id, mcce.subinventory, mcce.locator_id, mcce.inventory_item_id, mcce.revision) wdtv,
2401: -- inlined wms_person_resource_utt_v, bug 2648133
2402: (SELECT
2403: utt_emp.standard_operation_id standard_operation_id,
2404: min(utt_emp.resource_id) role, --Modified for Case Picking Project + Picking ER (FP 7709357)
2405: min(utt_eqp.resource_id) equipment, --Modified for Case Picking Project + Picking ER (FP 7709357)
2406: utt_emp.person_id emp_id,
2407: utt_emp.effective_start_date,
2409: --utt_eqp.inventory_item_id eqp_id, --Modified for Case Picking Project + Picking ER (FP 7709357)
2410: NULL eqp_srl /* removed for bug 2095237 */
2411: FROM
2412: (SELECT
2413: x_utt_res1.standard_operation_id standard_operation_id,
2414: x_utt_res1.resource_id resource_id,
2415: x_emp_r.person_id,
2416: x_emp_r.effective_start_date,
2417: x_emp_r.effective_end_date
2419: WHERE x_utt_res1.resource_id = r1.resource_id
2420: AND r1.resource_type = 2
2421: AND x_utt_res1.resource_id = x_emp_r.resource_id) utt_emp,
2422: (SELECT
2423: x_utt_res2.standard_operation_id standard_operation_id,
2424: x_utt_res2.resource_id,
2425: x_eqp_r.inventory_item_id
2426: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
2427: WHERE x_utt_res2.resource_id = r2.resource_id
2426: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
2427: WHERE x_utt_res2.resource_id = r2.resource_id
2428: AND r2.resource_type = 1
2429: AND x_utt_res2.resource_id = x_eqp_r.resource_id) utt_eqp
2430: WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)
2431: AND NVL(utt_eqp.inventory_item_id, -999) = NVL(l_sign_on_equipment_id, NVL(utt_eqp.inventory_item_id, -999)) --Modified for Case Picking Project + Picking ER (FP 7709357)
2432: GROUP BY utt_emp.standard_operation_id, utt_emp.person_id, utt_emp.effective_start_date, utt_emp.effective_end_date) v,
2433: mtl_item_locations loc,
2434: mtl_secondary_inventories sub,
2428: AND r2.resource_type = 1
2429: AND x_utt_res2.resource_id = x_eqp_r.resource_id) utt_eqp
2430: WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)
2431: AND NVL(utt_eqp.inventory_item_id, -999) = NVL(l_sign_on_equipment_id, NVL(utt_eqp.inventory_item_id, -999)) --Modified for Case Picking Project + Picking ER (FP 7709357)
2432: GROUP BY utt_emp.standard_operation_id, utt_emp.person_id, utt_emp.effective_start_date, utt_emp.effective_end_date) v,
2433: mtl_item_locations loc,
2434: mtl_secondary_inventories sub,
2435: mtl_txn_request_lines mol,
2436: mtl_txn_request_headers moh
2465: )
2466: --Added for Case Picking Project end
2467: )
2468: AND wdtv.organization_id = p_sign_on_org_id
2469: AND wdtv.user_task_type_id = v.standard_operation_id -- join task to resource view, check if user defined task type match
2470: AND NVL(wdtv.subinventory_code, '@@@') = NVL(p_sign_on_zone, NVL(wdtv.subinventory_code, '@@@')) AND NVL(wdtv.cartonization_id, -999) = NVL(l_cartonization_id, NVL(wdtv.cartonization_id, -999))
2471: --AND NVL(v.eqp_id, -999) = NVL(l_sign_on_equipment_id, NVL(v.eqp_id, -999)) --Modified above for Case Picking Project + Picking ER (FP 7709357)
2472: AND wdtv.organization_id = loc.organization_id(+) -- join task to loc, outer join for tasks do not have locator
2473: AND wdtv.locator_id = loc.inventory_location_id(+)
2560: qt.transaction_uom,
2561: qt.transaction_quantity,
2562: qt.lot_number,
2563: qt.operation_plan_id,
2564: qt.standard_operation_id,
2565: wdt.effective_start_date,
2566: wdt.effective_end_date,
2567: wdt.person_resource_id,
2568: wdt.machine_resource_id,
2593: transaction_uom,
2594: transaction_quantity,
2595: lot_number,
2596: operation_plan_id,
2597: standard_operation_id,
2598: move_order_line_id,
2599: transaction_type_id,
2600: transaction_action_id,
2601: transaction_source_id,
2626: mtl_txn_request_lines mol,
2627: mtl_secondary_inventories sub,
2628: mtl_item_locations loc,
2629: (SELECT
2630: bsor.standard_operation_id,
2631: bre.resource_id,
2632: bre.inventory_item_id equipment_id
2633: FROM bom_std_op_resources bsor, bom_resources br, bom_resource_equipments bre
2634: WHERE bsor.resource_id = bre.resource_id
2668: )
2669: --Added for Case Picking Project end
2670: )
2671: AND NVL(e.equipment_id, -999) = NVL(l_q_sign_on_equipment_id, NVL(e.equipment_id, -999))--bug 6326482
2672: AND wdt.user_task_type = e.standard_operation_id(+)
2673: AND qt.move_order_line_id = mol.line_id(+)
2674: AND NVL(qt.subinventory_code, '@@@') = NVL(p_sign_on_zone, NVL(qt.subinventory_code, '@@@'))
2675: AND qt.organization_id = sub.organization_id
2676: AND qt.subinventory_code = sub.secondary_inventory_name
2686: qt.transaction_uom,
2687: qt.transaction_quantity,
2688: qt.lot_number,
2689: qt.operation_plan_id,
2690: qt.standard_operation_id,
2691: wdt.effective_start_date,
2692: wdt.effective_end_date,
2693: wdt.person_resource_id,
2694: wdt.machine_resource_id,
2720: transaction_uom,
2721: transaction_quantity,
2722: lot_number,
2723: operation_plan_id,
2724: standard_operation_id,
2725: move_order_line_id,
2726: transaction_type_id,
2727: transaction_action_id,
2728: transaction_source_id,
2753: mtl_txn_request_lines mol,
2754: mtl_secondary_inventories sub,
2755: mtl_item_locations loc,
2756: (SELECT
2757: bsor.standard_operation_id,
2758: bre.resource_id,
2759: bre.inventory_item_id equipment_id
2760: FROM bom_std_op_resources bsor, bom_resources br, bom_resource_equipments bre
2761: WHERE bsor.resource_id = bre.resource_id
2800: AND NVL(e.equipment_id, -999) = NVL(l_q_sign_on_equipment_id, NVL(e.equipment_id, -999)))
2801: or
2802: (qt.task_status = 1
2803: AND NVL(e.equipment_id, -999) = NVL(l_sign_on_equipment_id, NVL(e.equipment_id, -999))))--bug 6326482
2804: AND wdt.user_task_type = e.standard_operation_id(+)
2805: AND qt.move_order_line_id = mol.line_id(+)
2806: AND NVL(qt.subinventory_code, '@@@') = NVL(p_sign_on_zone, NVL(qt.subinventory_code, '@@@'))
2807: AND qt.organization_id = sub.organization_id
2808: AND qt.subinventory_code = sub.secondary_inventory_name
2817: wdtv.transaction_uom,
2818: wdtv.transaction_quantity,
2819: wdtv.lot_number,
2820: wdtv.operation_plan_id,
2821: wdtv.user_task_type_id standard_operation_id,
2822: v.effective_start_date,
2823: v.effective_end_date,
2824: v.role person_resource_id,
2825: v.equipment machine_resource_id,
2840: FROM
2841: -- inlined wms_dispatchable_tasks_v, bug 2648133
2842: (SELECT
2843: transaction_temp_id task_id,
2844: standard_operation_id user_task_type_id,
2845: wms_task_type wms_task_type_id,
2846: organization_id,
2847: subinventory_code,
2848: locator_id,
2888: )
2889: ) wdtv,
2890: -- inlined wms_person_resource_utt_v, bug 2648133
2891: (SELECT
2892: utt_emp.standard_operation_id standard_operation_id,
2893: min(utt_emp.resource_id) role, --Modified for Case Picking Project + Picking ER (FP 7709357)
2894: min(utt_eqp.resource_id) equipment, --Modified for Case Picking Project + Picking ER (FP 7709357)
2895: utt_emp.person_id emp_id,
2896: utt_emp.effective_start_date,
2898: -- utt_eqp.inventory_item_id eqp_id, --Modified for Case Picking Project + Picking ER (FP 7709357)
2899: NULL eqp_srl /* removed for bug 2095237 */
2900: FROM
2901: (SELECT
2902: x_utt_res1.standard_operation_id standard_operation_id,
2903: x_utt_res1.resource_id resource_id,
2904: x_emp_r.person_id,
2905: x_emp_r.effective_start_date,
2906: x_emp_r.effective_end_date
2908: WHERE x_utt_res1.resource_id = r1.resource_id
2909: AND r1.resource_type = 2
2910: AND x_utt_res1.resource_id = x_emp_r.resource_id) utt_emp,
2911: (SELECT
2912: x_utt_res2.standard_operation_id standard_operation_id,
2913: x_utt_res2.resource_id,
2914: x_eqp_r.inventory_item_id
2915: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
2916: WHERE x_utt_res2.resource_id = r2.resource_id
2915: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
2916: WHERE x_utt_res2.resource_id = r2.resource_id
2917: AND r2.resource_type = 1
2918: AND x_utt_res2.resource_id = x_eqp_r.resource_id) utt_eqp
2919: WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)
2920: AND NVL(utt_eqp.inventory_item_id, -999) = NVL(l_sign_on_equipment_id, NVL(utt_eqp.inventory_item_id, -999)) --Modified for Case Picking Project + Picking ER (FP 7709357)
2921: GROUP BY utt_emp.standard_operation_id, utt_emp.person_id, utt_emp.effective_start_date, utt_emp.effective_end_date) v,
2922: mtl_item_locations loc,
2923: mtl_secondary_inventories sub,
2917: AND r2.resource_type = 1
2918: AND x_utt_res2.resource_id = x_eqp_r.resource_id) utt_eqp
2919: WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)
2920: AND NVL(utt_eqp.inventory_item_id, -999) = NVL(l_sign_on_equipment_id, NVL(utt_eqp.inventory_item_id, -999)) --Modified for Case Picking Project + Picking ER (FP 7709357)
2921: GROUP BY utt_emp.standard_operation_id, utt_emp.person_id, utt_emp.effective_start_date, utt_emp.effective_end_date) v,
2922: mtl_item_locations loc,
2923: mtl_secondary_inventories sub,
2924: mtl_txn_request_lines mol,
2925: mtl_txn_request_headers moh
2954: )
2955: --Added for Case Picking Project end
2956: )
2957: AND wdtv.organization_id = p_sign_on_org_id
2958: AND wdtv.user_task_type_id = v.standard_operation_id -- join task to resource view, check if user defined task type match
2959: AND NVL(wdtv.subinventory_code, '@@@') = NVL(p_sign_on_zone, NVL(wdtv.subinventory_code, '@@@'))
2960: --AND NVL(v.eqp_id, -999) = NVL(l_sign_on_equipment_id, NVL(v.eqp_id, -999)) --Modified for Case Picking Project + Picking ER (FP 7709357)
2961: AND wdtv.organization_id = loc.organization_id(+) -- join task to loc, outer join for tasks do not have locator
2962: AND wdtv.locator_id = loc.inventory_location_id(+)
3046: qt.transaction_uom,
3047: qt.transaction_quantity,
3048: qt.lot_number,
3049: qt.operation_plan_id,
3050: qt.standard_operation_id,
3051: wdt.effective_start_date,
3052: wdt.effective_end_date,
3053: wdt.person_resource_id,
3054: wdt.machine_resource_id,
3079: To_char(NULL) transaction_uom,
3080: To_number(NULL) transaction_quantity,
3081: MIN(mcce.lot_number) lot_number,
3082: To_number(NULL) operation_plan_id,
3083: MIN(mcce.standard_operation_id) standard_operation_id,
3084: To_number(NULL) move_order_line_id,
3085: 4 transaction_type_id,
3086: 4 transaction_action_id,
3087: mcce.cycle_count_header_id transaction_source_id,
3102: GROUP BY mcce.cycle_count_header_id, mcce.organization_id, mcce.subinventory, mcce.locator_id, mcce.inventory_item_id, mcce.revision) qt,
3103: mtl_secondary_inventories sub,
3104: mtl_item_locations loc,
3105: (SELECT
3106: bsor.standard_operation_id,
3107: bre.resource_id,
3108: bre.inventory_item_id equipment_id
3109: FROM bom_std_op_resources bsor, bom_resources br, bom_resource_equipments bre
3110: WHERE bsor.resource_id = bre.resource_id
3115: AND wdt.person_id = p_sign_on_emp_id
3116: AND wdt.organization_id = p_sign_on_org_id
3117: AND (qt.task_id = v_task_id AND wdt.task_type = v_task_type)
3118: AND NVL(e.equipment_id, -999) = NVL(l_sign_on_equipment_id, NVL(e.equipment_id, -999))
3119: AND wdt.user_task_type = e.standard_operation_id(+)
3120: AND NVL(qt.subinventory_code, '@@@') = NVL(p_sign_on_zone, NVL(qt.subinventory_code, '@@@'))
3121: AND qt.organization_id = sub.organization_id
3122: AND qt.subinventory_code = sub.secondary_inventory_name
3123: AND qt.organization_id = loc.organization_id
3131: wdtv.transaction_uom,
3132: wdtv.transaction_quantity,
3133: wdtv.lot_number,
3134: wdtv.operation_plan_id,
3135: wdtv.user_task_type_id standard_operation_id,
3136: v.effective_start_date,
3137: v.effective_end_date,
3138: v.role person_resource_id,
3139: v.equipment machine_resource_id,
3154: FROM
3155: -- inlined wms_dispatchable_tasks_v, bug 2648133
3156: (SELECT
3157: MIN(mcce.cycle_count_entry_id) task_id,
3158: MIN(mcce.standard_operation_id) user_task_type_id,
3159: 3 wms_task_type_id,
3160: mcce.organization_id,
3161: mcce.subinventory subinventory_code,
3162: mcce.locator_id,
3191: AND NVL(mcch.disable_date,sysdate+1)> sysdate
3192: GROUP BY mcce.cycle_count_header_id, mcce.organization_id, mcce.subinventory, mcce.locator_id, mcce.inventory_item_id, mcce.revision) wdtv,
3193: -- inlined wms_person_resource_utt_v, bug 2648133
3194: (SELECT
3195: utt_emp.standard_operation_id standard_operation_id,
3196: min(utt_emp.resource_id) role, --Modified for Case Picking Project + Picking ER (FP 7709357)
3197: min(utt_eqp.resource_id) equipment, --Modified for Case Picking Project + Picking ER (FP 7709357)
3198: utt_emp.person_id emp_id,
3199: utt_emp.effective_start_date,
3201: --utt_eqp.inventory_item_id eqp_id, --Modified for Case Picking Project + Picking ER (FP 7709357)
3202: NULL eqp_srl /* removed for bug 2095237 */
3203: FROM
3204: (SELECT
3205: x_utt_res1.standard_operation_id standard_operation_id,
3206: x_utt_res1.resource_id resource_id,
3207: x_emp_r.person_id,
3208: x_emp_r.effective_start_date,
3209: x_emp_r.effective_end_date
3211: WHERE x_utt_res1.resource_id = r1.resource_id
3212: AND r1.resource_type = 2
3213: AND x_utt_res1.resource_id = x_emp_r.resource_id) utt_emp,
3214: (SELECT
3215: x_utt_res2.standard_operation_id standard_operation_id,
3216: x_utt_res2.resource_id,
3217: x_eqp_r.inventory_item_id
3218: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
3219: WHERE x_utt_res2.resource_id = r2.resource_id
3218: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
3219: WHERE x_utt_res2.resource_id = r2.resource_id
3220: AND r2.resource_type = 1
3221: AND x_utt_res2.resource_id = x_eqp_r.resource_id) utt_eqp
3222: WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)
3223: AND NVL(utt_eqp.inventory_item_id, -999) = NVL(l_sign_on_equipment_id, NVL(utt_eqp.inventory_item_id, -999)) --Modified for Case Picking Project + Picking ER (FP 7709357)
3224: GROUP BY utt_emp.standard_operation_id, utt_emp.person_id, utt_emp.effective_start_date, utt_emp.effective_end_date) v,
3225: mtl_item_locations loc,
3226: mtl_secondary_inventories sub
3220: AND r2.resource_type = 1
3221: AND x_utt_res2.resource_id = x_eqp_r.resource_id) utt_eqp
3222: WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)
3223: AND NVL(utt_eqp.inventory_item_id, -999) = NVL(l_sign_on_equipment_id, NVL(utt_eqp.inventory_item_id, -999)) --Modified for Case Picking Project + Picking ER (FP 7709357)
3224: GROUP BY utt_emp.standard_operation_id, utt_emp.person_id, utt_emp.effective_start_date, utt_emp.effective_end_date) v,
3225: mtl_item_locations loc,
3226: mtl_secondary_inventories sub
3227: WHERE v.emp_id = p_sign_on_emp_id -- restrict to sign on employee
3228: AND (wdtv.task_id = v_task_id AND wdtv.wms_task_type_id = v_task_type)
3226: mtl_secondary_inventories sub
3227: WHERE v.emp_id = p_sign_on_emp_id -- restrict to sign on employee
3228: AND (wdtv.task_id = v_task_id AND wdtv.wms_task_type_id = v_task_type)
3229: AND wdtv.organization_id = p_sign_on_org_id
3230: AND wdtv.user_task_type_id = v.standard_operation_id -- join task to resource view, check if user defined task type match
3231: AND NVL(wdtv.subinventory_code, '@@@') = NVL(p_sign_on_zone, NVL(wdtv.subinventory_code, '@@@'))
3232: -- AND NVL(v.eqp_id, -999) = NVL(l_sign_on_equipment_id, NVL(v.eqp_id, -999)) --Modified for Case Picking Project + Picking ER (FP 7709357)
3233: AND wdtv.organization_id = loc.organization_id(+) -- join task to loc, outer join for tasks do not have locator
3234: AND wdtv.locator_id = loc.inventory_location_id(+)
3561: INTO l_cur_x, l_cur_y, l_cur_z
3562: FROM mtl_item_locations loc,
3563: (SELECT
3564: transaction_temp_id task_id,
3565: standard_operation_id user_task_type_id,
3566: wms_task_type wms_task_type_id,
3567: organization_id organization_id,
3568: subinventory_code zone,
3569: locator_id locator_id,
3582: AND transaction_status = 2
3583: UNION ALL
3584: SELECT
3585: MIN(cycle_count_entry_id) task_id,
3586: MIN(standard_operation_id) user_task_type_id,
3587: 3 wms_task_type_id,
3588: organization_id organization_id,
3589: subinventory zone,
3590: locator_id locator_id,
4360: l_transaction_uom,
4361: l_transaction_quantity,
4362: l_lot_number,
4363: l_operation_plan_id,
4364: l_standard_operation_id,
4365: l_effective_start_date,
4366: l_effective_end_date,
4367: l_person_resource_id,
4368: l_machine_resource_id,
4396: l_transaction_uom,
4397: l_transaction_quantity,
4398: l_lot_number,
4399: l_operation_plan_id,
4400: l_standard_operation_id,
4401: l_effective_start_date,
4402: l_effective_end_date,
4403: l_person_resource_id,
4404: l_machine_resource_id,
4432: l_transaction_uom,
4433: l_transaction_quantity,
4434: l_lot_number,
4435: l_operation_plan_id,
4436: l_standard_operation_id,
4437: l_effective_start_date,
4438: l_effective_end_date,
4439: l_person_resource_id,
4440: l_machine_resource_id,
4484: transaction_quantity,
4485: lot_number,
4486: priority,
4487: operation_plan_id,
4488: standard_operation_id,
4489: effective_start_date,
4490: effective_end_date,
4491: person_resource_id,
4492: machine_resource_id,
4502: l_transaction_quantity,
4503: l_lot_number,
4504: l_task_priority,
4505: l_operation_plan_id,
4506: l_standard_operation_id,
4507: l_effective_start_date,
4508: l_effective_end_date,
4509: l_person_resource_id,
4510: l_machine_resource_id,
4605: lot_number,
4606: wms_task_type,
4607: priority,
4608: operation_plan_id,
4609: standard_operation_id,
4610: effective_start_date,
4611: effective_end_date,
4612: person_resource_id,
4613: machine_resource_id,
4761: , nvl(loc.x_coordinate, 0)
4762: , nvl(loc.y_coordinate, 0)
4763: , nvl(loc.z_coordinate, 0)
4764: FROM (SELECT transaction_temp_id task_id
4765: , standard_operation_id user_task_type_id
4766: , wms_task_type wms_task_type_id
4767: , organization_id organization_id
4768: , subinventory_code ZONE
4769: , locator_id locator_id
4783: AND(wms_task_status IS NULL
4784: OR wms_task_status = 1) --Added for task planning WB. bug#2651318
4785: UNION ALL
4786: SELECT MIN(mcce.cycle_count_entry_id) task_id
4787: , MIN(mcce.standard_operation_id) user_task_type_id
4788: , 3 wms_task_type_id
4789: , mcce.organization_id organization_id
4790: , mcce.subinventory ZONE
4791: , mcce.locator_id locator_id
4807: AND mcce.cycle_count_header_id = mcch.cycle_count_header_id
4808: AND NVL(mcch.disable_date,sysdate+1)> sysdate
4809: GROUP BY mcce.cycle_count_header_id, mcce.organization_id, mcce.subinventory, mcce.locator_id, mcce.inventory_item_id, mcce.revision) wdtv
4810: , -- inlined wms_dispatchable_tasks_v, bug 2648133
4811: (SELECT utt_emp.standard_operation_id standard_operation_id
4812: , utt_emp.resource_id ROLE
4813: , utt_eqp.resource_id equipment
4814: , utt_emp.person_id emp_id
4815: , utt_eqp.inventory_item_id eqp_id
4813: , utt_eqp.resource_id equipment
4814: , utt_emp.person_id emp_id
4815: , utt_eqp.inventory_item_id eqp_id
4816: , NULL eqp_srl /* removed for bug 2095237 */
4817: FROM (SELECT x_utt_res1.standard_operation_id standard_operation_id
4818: , x_utt_res1.resource_id resource_id
4819: , x_emp_r.person_id
4820: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
4821: WHERE x_utt_res1.resource_id = r1.resource_id
4820: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
4821: WHERE x_utt_res1.resource_id = r1.resource_id
4822: AND r1.resource_type = 2
4823: AND x_utt_res1.resource_id = x_emp_r.resource_id) utt_emp
4824: , (SELECT x_utt_res2.standard_operation_id standard_operation_id
4825: , x_utt_res2.resource_id resource_id
4826: , x_eqp_r.inventory_item_id inventory_item_id
4827: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
4828: WHERE x_utt_res2.resource_id = r2.resource_id
4827: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
4828: WHERE x_utt_res2.resource_id = r2.resource_id
4829: AND r2.resource_type = 1
4830: AND x_utt_res2.resource_id = x_eqp_r.resource_id) utt_eqp
4831: WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)) v
4832: , -- inlined wms_person_resource_utt_v, bug 2648133
4833: mtl_item_locations loc
4834: , mtl_secondary_inventories sub
4835: , mtl_txn_request_lines mol
4835: , mtl_txn_request_lines mol
4836: WHERE v.emp_id = p_sign_on_emp_id -- restrict to sign on employee
4837: AND wdtv.organization_id = p_sign_on_org_id
4838: AND wdtv.wms_task_type_id = NVL(l_sys_task_type, wdtv.wms_task_type_id) -- restrict to picking tasks or all tasks
4839: AND wdtv.user_task_type_id = v.standard_operation_id -- join task to resource view, check if user defined task type match
4840: AND wdtv.ZONE = p_sign_on_zone -- removed NVL, bug 2648133
4841: AND NVL(wdtv.cartonization_id, -999) = NVL(p_cartonization_id, NVL(wdtv.cartonization_id, -999))
4842: AND NVL(v.eqp_id, -999) = NVL(l_sign_on_equipment_id, NVL(v.eqp_id, -999))
4843: AND wdtv.organization_id = loc.organization_id(+) -- join task to loc, outer join for tasks do not have locator
4915: , nvl(loc.x_coordinate, 0)
4916: , nvl(loc.y_coordinate, 0)
4917: , nvl(loc.z_coordinate, 0)
4918: FROM (SELECT transaction_temp_id task_id
4919: , standard_operation_id user_task_type_id
4920: , wms_task_type wms_task_type_id
4921: , organization_id organization_id
4922: , subinventory_code ZONE
4923: , locator_id locator_id
4937: AND(wms_task_status IS NULL
4938: OR wms_task_status = 1) --Added for task planning WB. bug#2651318
4939: UNION ALL
4940: SELECT MIN(mcce.cycle_count_entry_id) task_id
4941: , MIN(mcce.standard_operation_id) user_task_type_id
4942: , 3 wms_task_type_id
4943: , mcce.organization_id organization_id
4944: , mcce.subinventory ZONE
4945: , mcce.locator_id locator_id
4961: AND mcce.cycle_count_header_id = mcch.cycle_count_header_id
4962: AND NVL(mcch.disable_date,sysdate+1)> sysdate
4963: GROUP BY mcce.cycle_count_header_id, mcce.organization_id, mcce.subinventory, mcce.locator_id, mcce.inventory_item_id, mcce.revision) wdtv
4964: , -- inlined wms_dispatchable_tasks_v, bug 2648133
4965: (SELECT utt_emp.standard_operation_id standard_operation_id
4966: , utt_emp.resource_id ROLE
4967: , utt_eqp.resource_id equipment
4968: , utt_emp.person_id emp_id
4969: , utt_eqp.inventory_item_id eqp_id
4967: , utt_eqp.resource_id equipment
4968: , utt_emp.person_id emp_id
4969: , utt_eqp.inventory_item_id eqp_id
4970: , NULL eqp_srl /* removed for bug 2095237 */
4971: FROM (SELECT x_utt_res1.standard_operation_id standard_operation_id
4972: , x_utt_res1.resource_id resource_id
4973: , x_emp_r.person_id
4974: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
4975: WHERE x_utt_res1.resource_id = r1.resource_id
4974: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
4975: WHERE x_utt_res1.resource_id = r1.resource_id
4976: AND r1.resource_type = 2
4977: AND x_utt_res1.resource_id = x_emp_r.resource_id) utt_emp
4978: , (SELECT x_utt_res2.standard_operation_id standard_operation_id
4979: , x_utt_res2.resource_id resource_id
4980: , x_eqp_r.inventory_item_id inventory_item_id
4981: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
4982: WHERE x_utt_res2.resource_id = r2.resource_id
4981: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
4982: WHERE x_utt_res2.resource_id = r2.resource_id
4983: AND r2.resource_type = 1
4984: AND x_utt_res2.resource_id = x_eqp_r.resource_id) utt_eqp
4985: WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)) v
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
4995: AND wdtv.ZONE = p_sign_on_zone -- removed NVL, bug 2648133
4996: AND NVL(wdtv.cartonization_id, -999) = NVL(p_cartonization_id, NVL(wdtv.cartonization_id, -999))
4997: AND NVL(v.eqp_id, -999) = NVL(l_sign_on_equipment_id, NVL(v.eqp_id, -999))
4998: AND wdtv.organization_id = loc.organization_id(+) -- join task to loc, outer join for tasks do not have locator
5071: , nvl(loc.x_coordinate, 0)
5072: , nvl(loc.y_coordinate, 0)
5073: , nvl(loc.z_coordinate, 0)
5074: FROM (SELECT transaction_temp_id task_id
5075: , standard_operation_id user_task_type_id
5076: , wms_task_type wms_task_type_id
5077: , organization_id organization_id
5078: , subinventory_code ZONE
5079: , locator_id locator_id
5093: AND(wms_task_status IS NULL
5094: OR wms_task_status = 1) --Added for task planning WB. bug#2651318
5095: UNION ALL
5096: SELECT MIN(mcce.cycle_count_entry_id) task_id
5097: , MIN(mcce.standard_operation_id) user_task_type_id
5098: , 3 wms_task_type_id
5099: , mcce.organization_id organization_id
5100: , mcce.subinventory ZONE
5101: , mcce.locator_id locator_id
5117: AND mcce.cycle_count_header_id = mcch.cycle_count_header_id
5118: AND NVL(mcch.disable_date,sysdate+1)> sysdate
5119: GROUP BY mcce.cycle_count_header_id, mcce.organization_id, mcce.subinventory, mcce.locator_id, mcce.inventory_item_id, mcce.revision) wdtv
5120: , -- inlined wms_dispatchable_tasks_v, bug 2648133
5121: (SELECT utt_emp.standard_operation_id standard_operation_id
5122: , utt_emp.resource_id ROLE
5123: , utt_eqp.resource_id equipment
5124: , utt_emp.person_id emp_id
5125: , utt_eqp.inventory_item_id eqp_id
5123: , utt_eqp.resource_id equipment
5124: , utt_emp.person_id emp_id
5125: , utt_eqp.inventory_item_id eqp_id
5126: , NULL eqp_srl /* removed for bug 2095237 */
5127: FROM (SELECT x_utt_res1.standard_operation_id standard_operation_id
5128: , x_utt_res1.resource_id resource_id
5129: , x_emp_r.person_id
5130: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
5131: WHERE x_utt_res1.resource_id = r1.resource_id
5130: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
5131: WHERE x_utt_res1.resource_id = r1.resource_id
5132: AND r1.resource_type = 2
5133: AND x_utt_res1.resource_id = x_emp_r.resource_id) utt_emp
5134: , (SELECT x_utt_res2.standard_operation_id standard_operation_id
5135: , x_utt_res2.resource_id resource_id
5136: , x_eqp_r.inventory_item_id inventory_item_id
5137: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
5138: WHERE x_utt_res2.resource_id = r2.resource_id
5137: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
5138: WHERE x_utt_res2.resource_id = r2.resource_id
5139: AND r2.resource_type = 1
5140: AND x_utt_res2.resource_id = x_eqp_r.resource_id) utt_eqp
5141: WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)) v
5142: , -- inlined wms_person_resource_utt_v, bug 2648133
5143: mtl_item_locations loc
5144: , mtl_secondary_inventories sub
5145: , mtl_txn_request_lines mol
5145: , mtl_txn_request_lines mol
5146: WHERE v.emp_id = p_sign_on_emp_id -- restrict to sign on employee
5147: AND wdtv.organization_id = p_sign_on_org_id
5148: AND wdtv.wms_task_type_id = NVL(l_sys_task_type, wdtv.wms_task_type_id) -- restrict to picking tasks or all tasks
5149: AND wdtv.user_task_type_id = v.standard_operation_id -- join task to resource view, check if user defined task type match
5150: AND NVL(wdtv.cartonization_id, -999) = NVL(p_cartonization_id, NVL(wdtv.cartonization_id, -999))
5151: AND NVL(v.eqp_id, -999) = NVL(l_sign_on_equipment_id, NVL(v.eqp_id, -999))
5152: AND wdtv.organization_id = loc.organization_id(+) -- join task to loc, outer join for tasks do not have locator
5153: AND wdtv.locator_id = loc.inventory_location_id(+)
5224: , nvl(loc.x_coordinate, 0)
5225: , nvl(loc.y_coordinate, 0)
5226: , nvl(loc.z_coordinate, 0)
5227: FROM (SELECT transaction_temp_id task_id
5228: , standard_operation_id user_task_type_id
5229: , wms_task_type wms_task_type_id
5230: , organization_id organization_id
5231: , subinventory_code ZONE
5232: , locator_id locator_id
5246: AND(wms_task_status IS NULL
5247: OR wms_task_status = 1) --Added for task planning WB. bug#2651318
5248: UNION ALL
5249: SELECT MIN(mcce.cycle_count_entry_id) task_id
5250: , MIN(mcce.standard_operation_id) user_task_type_id
5251: , 3 wms_task_type_id
5252: , mcce.organization_id organization_id
5253: , mcce.subinventory ZONE
5254: , mcce.locator_id locator_id
5270: AND mcce.cycle_count_header_id = mcch.cycle_count_header_id
5271: AND NVL(mcch.disable_date,sysdate+1)> sysdate
5272: GROUP BY mcce.cycle_count_header_id, mcce.organization_id, mcce.subinventory, mcce.locator_id, mcce.inventory_item_id, mcce.revision) wdtv
5273: , -- inlined wms_dispatchable_tasks_v, bug 2648133
5274: (SELECT utt_emp.standard_operation_id standard_operation_id
5275: , utt_emp.resource_id ROLE
5276: , utt_eqp.resource_id equipment
5277: , utt_emp.person_id emp_id
5278: , utt_eqp.inventory_item_id eqp_id
5276: , utt_eqp.resource_id equipment
5277: , utt_emp.person_id emp_id
5278: , utt_eqp.inventory_item_id eqp_id
5279: , NULL eqp_srl /* removed for bug 2095237 */
5280: FROM (SELECT x_utt_res1.standard_operation_id standard_operation_id
5281: , x_utt_res1.resource_id resource_id
5282: , x_emp_r.person_id
5283: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
5284: WHERE x_utt_res1.resource_id = r1.resource_id
5283: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
5284: WHERE x_utt_res1.resource_id = r1.resource_id
5285: AND r1.resource_type = 2
5286: AND x_utt_res1.resource_id = x_emp_r.resource_id) utt_emp
5287: , (SELECT x_utt_res2.standard_operation_id standard_operation_id
5288: , x_utt_res2.resource_id resource_id
5289: , x_eqp_r.inventory_item_id inventory_item_id
5290: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
5291: WHERE x_utt_res2.resource_id = r2.resource_id
5290: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
5291: WHERE x_utt_res2.resource_id = r2.resource_id
5292: AND r2.resource_type = 1
5293: AND x_utt_res2.resource_id = x_eqp_r.resource_id) utt_eqp
5294: WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)) v
5295: , -- inlined wms_person_resource_utt_v, bug 2648133
5296: mtl_item_locations loc
5297: , mtl_secondary_inventories sub
5298: , mtl_txn_request_lines mol
5298: , mtl_txn_request_lines mol
5299: WHERE v.emp_id = p_sign_on_emp_id -- restrict to sign on employee
5300: AND wdtv.organization_id = p_sign_on_org_id
5301: AND wdtv.wms_task_type_id = NVL(l_sys_task_type, wdtv.wms_task_type_id) -- restrict to picking tasks or all tasks
5302: AND wdtv.user_task_type_id = v.standard_operation_id -- join task to resource view, check if user defined task type match
5303: AND NVL(wdtv.cartonization_id, -999) = NVL(p_cartonization_id, NVL(wdtv.cartonization_id, -999))
5304: AND NVL(v.eqp_id, -999) = NVL(l_sign_on_equipment_id, NVL(v.eqp_id, -999))
5305: AND wdtv.organization_id = loc.organization_id(+) -- join task to loc, outer join for tasks do not have locator
5306: AND wdtv.locator_id = loc.inventory_location_id(+)
5377: , nvl(loc.x_coordinate, 0)
5378: , nvl(loc.y_coordinate, 0)
5379: , nvl(loc.z_coordinate, 0)
5380: FROM (SELECT transaction_temp_id task_id
5381: , standard_operation_id user_task_type_id
5382: , wms_task_type wms_task_type_id
5383: , organization_id organization_id
5384: , subinventory_code ZONE
5385: , locator_id locator_id
5399: AND(wms_task_status IS NULL
5400: OR wms_task_status = 1) --Added for task planning WB. bug#2651318
5401: UNION ALL
5402: SELECT MIN(mcce.cycle_count_entry_id) task_id
5403: , MIN(mcce.standard_operation_id) user_task_type_id
5404: , 3 wms_task_type_id
5405: , mcce.organization_id organization_id
5406: , mcce.subinventory ZONE
5407: , mcce.locator_id locator_id
5423: AND mcce.cycle_count_header_id = mcch.cycle_count_header_id
5424: AND NVL(mcch.disable_date,sysdate+1)> sysdate
5425: GROUP BY mcce.cycle_count_header_id, mcce.organization_id, mcce.subinventory, mcce.locator_id, mcce.inventory_item_id, mcce.revision) wdtv
5426: , -- inlined wms_dispatchable_tasks_v, bug 2648133
5427: (SELECT utt_emp.standard_operation_id standard_operation_id
5428: , utt_emp.resource_id ROLE
5429: , utt_eqp.resource_id equipment
5430: , utt_emp.person_id emp_id
5431: , utt_eqp.inventory_item_id eqp_id
5429: , utt_eqp.resource_id equipment
5430: , utt_emp.person_id emp_id
5431: , utt_eqp.inventory_item_id eqp_id
5432: , NULL eqp_srl /* removed for bug 2095237 */
5433: FROM (SELECT x_utt_res1.standard_operation_id standard_operation_id
5434: , x_utt_res1.resource_id resource_id
5435: , x_emp_r.person_id
5436: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
5437: WHERE x_utt_res1.resource_id = r1.resource_id
5436: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
5437: WHERE x_utt_res1.resource_id = r1.resource_id
5438: AND r1.resource_type = 2
5439: AND x_utt_res1.resource_id = x_emp_r.resource_id) utt_emp
5440: , (SELECT x_utt_res2.standard_operation_id standard_operation_id
5441: , x_utt_res2.resource_id resource_id
5442: , x_eqp_r.inventory_item_id inventory_item_id
5443: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
5444: WHERE x_utt_res2.resource_id = r2.resource_id
5443: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
5444: WHERE x_utt_res2.resource_id = r2.resource_id
5445: AND r2.resource_type = 1
5446: AND x_utt_res2.resource_id = x_eqp_r.resource_id) utt_eqp
5447: WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)) v
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
5457: AND wdtv.ZONE = p_sign_on_zone
5458: AND NVL(wdtv.cartonization_id, -999) = NVL(p_cartonization_id, NVL(wdtv.cartonization_id, -999))
5459: -- AND Nvl(v.eqp_srl, '@@@') = Nvl(l_sign_on_equipment_srl, Nvl(v.eqp_srl, '@@@')) removed for bug 2095237
5460: AND NVL(v.eqp_id, -999) = NVL(l_sign_on_equipment_id, NVL(v.eqp_id, -999))
5531: , nvl(loc.x_coordinate, 0)
5532: , nvl(loc.y_coordinate, 0)
5533: , nvl(loc.z_coordinate, 0)
5534: FROM (SELECT transaction_temp_id task_id
5535: , standard_operation_id user_task_type_id
5536: , wms_task_type wms_task_type_id
5537: , organization_id organization_id
5538: , subinventory_code ZONE
5539: , locator_id locator_id
5553: AND(wms_task_status IS NULL
5554: OR wms_task_status = 1) --Added for task planning WB. bug#2651318
5555: UNION ALL
5556: SELECT MIN(mcce.cycle_count_entry_id) task_id
5557: , MIN(mcce.standard_operation_id) user_task_type_id
5558: , 3 wms_task_type_id
5559: , mcce.organization_id organization_id
5560: , mcce.subinventory ZONE
5561: , mcce.locator_id locator_id
5577: AND mcce.cycle_count_header_id = mcch.cycle_count_header_id
5578: AND NVL(mcch.disable_date,sysdate+1)> sysdate
5579: GROUP BY mcce.cycle_count_header_id, mcce.organization_id, mcce.subinventory, mcce.locator_id, mcce.inventory_item_id, mcce.revision) wdtv
5580: , -- inlined wms_dispatchable_tasks_v, bug 2648133
5581: (SELECT utt_emp.standard_operation_id standard_operation_id
5582: , utt_emp.resource_id ROLE
5583: , utt_eqp.resource_id equipment
5584: , utt_emp.person_id emp_id
5585: , utt_eqp.inventory_item_id eqp_id
5583: , utt_eqp.resource_id equipment
5584: , utt_emp.person_id emp_id
5585: , utt_eqp.inventory_item_id eqp_id
5586: , NULL eqp_srl /* removed for bug 2095237 */
5587: FROM (SELECT x_utt_res1.standard_operation_id standard_operation_id
5588: , x_utt_res1.resource_id resource_id
5589: , x_emp_r.person_id
5590: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
5591: WHERE x_utt_res1.resource_id = r1.resource_id
5590: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
5591: WHERE x_utt_res1.resource_id = r1.resource_id
5592: AND r1.resource_type = 2
5593: AND x_utt_res1.resource_id = x_emp_r.resource_id) utt_emp
5594: , (SELECT x_utt_res2.standard_operation_id standard_operation_id
5595: , x_utt_res2.resource_id resource_id
5596: , x_eqp_r.inventory_item_id inventory_item_id
5597: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
5598: WHERE x_utt_res2.resource_id = r2.resource_id
5597: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
5598: WHERE x_utt_res2.resource_id = r2.resource_id
5599: AND r2.resource_type = 1
5600: AND x_utt_res2.resource_id = x_eqp_r.resource_id) utt_eqp
5601: WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)) v
5602: , -- inlined wms_person_resource_utt_v, bug 2648133
5603: mtl_item_locations loc
5604: , mtl_secondary_inventories sub
5605: , mtl_txn_request_lines mol
5605: , mtl_txn_request_lines mol
5606: WHERE v.emp_id = p_sign_on_emp_id -- restrict to sign on employee
5607: AND wdtv.organization_id = p_sign_on_org_id
5608: AND wdtv.wms_task_type_id = NVL(l_sys_task_type, wdtv.wms_task_type_id) -- restrict to picking tasks or all tasks
5609: AND wdtv.user_task_type_id = v.standard_operation_id -- join task to resource view, check if user defined task type match
5610: AND wdtv.ZONE = p_sign_on_zone
5611: AND NVL(wdtv.cartonization_id, -999) = NVL(p_cartonization_id, NVL(wdtv.cartonization_id, -999))
5612: AND NVL(v.eqp_id, -999) = NVL(l_sign_on_equipment_id, NVL(v.eqp_id, -999))
5613: AND wdtv.organization_id = loc.organization_id(+) -- join task to loc, outer join for tasks do not have locator
5684: , nvl(loc.x_coordinate, 0)
5685: , nvl(loc.y_coordinate, 0)
5686: , nvl(loc.z_coordinate, 0)
5687: FROM (SELECT transaction_temp_id task_id
5688: , standard_operation_id user_task_type_id
5689: , wms_task_type wms_task_type_id
5690: , organization_id organization_id
5691: , subinventory_code ZONE
5692: , locator_id locator_id
5706: AND(wms_task_status IS NULL
5707: OR wms_task_status = 1) --Added for task planning WB. bug#2651318
5708: UNION ALL
5709: SELECT MIN(mcce.cycle_count_entry_id) task_id
5710: , MIN(mcce.standard_operation_id) user_task_type_id
5711: , 3 wms_task_type_id
5712: , mcce.organization_id organization_id
5713: , mcce.subinventory ZONE
5714: , mcce.locator_id locator_id
5730: AND mcce.cycle_count_header_id = mcch.cycle_count_header_id
5731: AND NVL(mcch.disable_date,sysdate+1)> sysdate
5732: GROUP BY mcce.cycle_count_header_id, mcce.organization_id, mcce.subinventory, mcce.locator_id, mcce.inventory_item_id, mcce.revision) wdtv
5733: , -- inlined wms_dispatchable_tasks_v, bug 2648133
5734: (SELECT utt_emp.standard_operation_id standard_operation_id
5735: , utt_emp.resource_id ROLE
5736: , utt_eqp.resource_id equipment
5737: , utt_emp.person_id emp_id
5738: , utt_eqp.inventory_item_id eqp_id
5736: , utt_eqp.resource_id equipment
5737: , utt_emp.person_id emp_id
5738: , utt_eqp.inventory_item_id eqp_id
5739: , NULL eqp_srl /* removed for bug 2095237 */
5740: FROM (SELECT x_utt_res1.standard_operation_id standard_operation_id
5741: , x_utt_res1.resource_id resource_id
5742: , x_emp_r.person_id
5743: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
5744: WHERE x_utt_res1.resource_id = r1.resource_id
5743: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
5744: WHERE x_utt_res1.resource_id = r1.resource_id
5745: AND r1.resource_type = 2
5746: AND x_utt_res1.resource_id = x_emp_r.resource_id) utt_emp
5747: , (SELECT x_utt_res2.standard_operation_id standard_operation_id
5748: , x_utt_res2.resource_id resource_id
5749: , x_eqp_r.inventory_item_id inventory_item_id
5750: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
5751: WHERE x_utt_res2.resource_id = r2.resource_id
5750: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
5751: WHERE x_utt_res2.resource_id = r2.resource_id
5752: AND r2.resource_type = 1
5753: AND x_utt_res2.resource_id = x_eqp_r.resource_id) utt_eqp
5754: WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)) v
5755: , -- inlined wms_person_resource_utt_v, bug 2648133
5756: mtl_item_locations loc
5757: , mtl_secondary_inventories sub
5758: , mtl_txn_request_lines mol
5758: , mtl_txn_request_lines mol
5759: WHERE v.emp_id = p_sign_on_emp_id -- restrict to sign on employee
5760: AND wdtv.organization_id = p_sign_on_org_id
5761: AND wdtv.wms_task_type_id = NVL(l_sys_task_type, wdtv.wms_task_type_id) -- restrict to picking tasks or all tasks
5762: AND wdtv.user_task_type_id = v.standard_operation_id -- join task to resource view, check if user defined task type match
5763: AND NVL(wdtv.cartonization_id, -999) = NVL(p_cartonization_id, NVL(wdtv.cartonization_id, -999))
5764: AND NVL(v.eqp_id, -999) = NVL(l_sign_on_equipment_id, NVL(v.eqp_id, -999))
5765: AND wdtv.organization_id = loc.organization_id(+) -- join task to loc, outer join for tasks do not have locator
5766: AND wdtv.locator_id = loc.inventory_location_id(+)
5835: , nvl(loc.x_coordinate, 0)
5836: , nvl(loc.y_coordinate, 0)
5837: , nvl(loc.z_coordinate, 0)
5838: FROM (SELECT transaction_temp_id task_id
5839: , standard_operation_id user_task_type_id
5840: , wms_task_type wms_task_type_id
5841: , organization_id organization_id
5842: , subinventory_code ZONE
5843: , locator_id locator_id
5857: AND(wms_task_status IS NULL
5858: OR wms_task_status = 1) --Added for task planning WB. bug#2651318
5859: UNION ALL
5860: SELECT MIN(mcce.cycle_count_entry_id) task_id
5861: , MIN(mcce.standard_operation_id) user_task_type_id
5862: , 3 wms_task_type_id
5863: , mcce.organization_id organization_id
5864: , mcce.subinventory ZONE
5865: , mcce.locator_id locator_id
5881: AND mcce.cycle_count_header_id = mcch.cycle_count_header_id
5882: AND NVL(mcch.disable_date,sysdate+1)> sysdate
5883: GROUP BY mcce.cycle_count_header_id, mcce.organization_id, mcce.subinventory, mcce.locator_id, mcce.inventory_item_id, mcce.revision) wdtv
5884: , -- inlined wms_dispatchable_tasks_v, bug 2648133
5885: (SELECT utt_emp.standard_operation_id standard_operation_id
5886: , utt_emp.resource_id ROLE
5887: , utt_eqp.resource_id equipment
5888: , utt_emp.person_id emp_id
5889: , utt_eqp.inventory_item_id eqp_id
5887: , utt_eqp.resource_id equipment
5888: , utt_emp.person_id emp_id
5889: , utt_eqp.inventory_item_id eqp_id
5890: , NULL eqp_srl /* removed for bug 2095237 */
5891: FROM (SELECT x_utt_res1.standard_operation_id standard_operation_id
5892: , x_utt_res1.resource_id resource_id
5893: , x_emp_r.person_id
5894: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
5895: WHERE x_utt_res1.resource_id = r1.resource_id
5894: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
5895: WHERE x_utt_res1.resource_id = r1.resource_id
5896: AND r1.resource_type = 2
5897: AND x_utt_res1.resource_id = x_emp_r.resource_id) utt_emp
5898: , (SELECT x_utt_res2.standard_operation_id standard_operation_id
5899: , x_utt_res2.resource_id resource_id
5900: , x_eqp_r.inventory_item_id inventory_item_id
5901: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
5902: WHERE x_utt_res2.resource_id = r2.resource_id
5901: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
5902: WHERE x_utt_res2.resource_id = r2.resource_id
5903: AND r2.resource_type = 1
5904: AND x_utt_res2.resource_id = x_eqp_r.resource_id) utt_eqp
5905: WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)) v
5906: , -- inlined wms_person_resource_utt_v, bug 2648133
5907: mtl_item_locations loc
5908: , mtl_secondary_inventories sub
5909: , mtl_txn_request_lines mol
5909: , mtl_txn_request_lines mol
5910: WHERE v.emp_id = p_sign_on_emp_id -- restrict to sign on employee
5911: AND wdtv.organization_id = p_sign_on_org_id
5912: AND wdtv.wms_task_type_id = NVL(l_sys_task_type, wdtv.wms_task_type_id) -- restrict to picking tasks or all tasks
5913: AND wdtv.user_task_type_id = v.standard_operation_id -- join task to resource view, check if user defined task type match
5914: AND NVL(wdtv.cartonization_id, -999) = NVL(p_cartonization_id, NVL(wdtv.cartonization_id, -999))
5915: AND NVL(v.eqp_id, -999) = NVL(l_sign_on_equipment_id, NVL(v.eqp_id, -999))
5916: AND wdtv.organization_id = loc.organization_id(+) -- join task to loc, outer join for tasks do not have locator
5917: AND wdtv.locator_id = loc.inventory_location_id(+)
5983: , nvl(loc.x_coordinate, 0)
5984: , nvl(loc.y_coordinate, 0)
5985: , nvl(loc.z_coordinate, 0)
5986: FROM (SELECT transaction_temp_id task_id
5987: , standard_operation_id user_task_type_id
5988: , wms_task_type wms_task_type_id
5989: , organization_id organization_id
5990: , subinventory_code ZONE
5991: , locator_id locator_id
6005: AND(wms_task_status IS NULL
6006: OR wms_task_status = 1) --Added for task planning WB. bug#2651318
6007: UNION ALL
6008: SELECT MIN(mcce.cycle_count_entry_id) task_id
6009: , MIN(mcce.standard_operation_id) user_task_type_id
6010: , 3 wms_task_type_id
6011: , mcce.organization_id organization_id
6012: , mcce.subinventory ZONE
6013: , mcce.locator_id locator_id
6029: AND mcce.cycle_count_header_id = mcch.cycle_count_header_id
6030: AND NVL(mcch.disable_date,sysdate+1)> sysdate
6031: GROUP BY mcce.cycle_count_header_id, mcce.organization_id, mcce.subinventory, mcce.locator_id, mcce.inventory_item_id, mcce.revision) wdtv
6032: , -- inlined wms_dispatchable_tasks_v, bug 2648133
6033: (SELECT utt_emp.standard_operation_id standard_operation_id
6034: , utt_emp.resource_id ROLE
6035: , utt_eqp.resource_id equipment
6036: , utt_emp.person_id emp_id
6037: , utt_eqp.inventory_item_id eqp_id
6035: , utt_eqp.resource_id equipment
6036: , utt_emp.person_id emp_id
6037: , utt_eqp.inventory_item_id eqp_id
6038: , NULL eqp_srl /* removed for bug 2095237 */
6039: FROM (SELECT x_utt_res1.standard_operation_id standard_operation_id
6040: , x_utt_res1.resource_id resource_id
6041: , x_emp_r.person_id
6042: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
6043: WHERE x_utt_res1.resource_id = r1.resource_id
6042: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
6043: WHERE x_utt_res1.resource_id = r1.resource_id
6044: AND r1.resource_type = 2
6045: AND x_utt_res1.resource_id = x_emp_r.resource_id) utt_emp
6046: , (SELECT x_utt_res2.standard_operation_id standard_operation_id
6047: , x_utt_res2.resource_id resource_id
6048: , x_eqp_r.inventory_item_id inventory_item_id
6049: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
6050: WHERE x_utt_res2.resource_id = r2.resource_id
6049: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
6050: WHERE x_utt_res2.resource_id = r2.resource_id
6051: AND r2.resource_type = 1
6052: AND x_utt_res2.resource_id = x_eqp_r.resource_id) utt_eqp
6053: WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)) v
6054: , -- inlined wms_person_resource_utt_v, bug 2648133
6055: mtl_item_locations loc
6056: , mtl_secondary_inventories sub
6057: , mtl_txn_request_lines mol
6060: OR(wdtv.task_id = v_task_id
6061: AND wdtv.wms_task_type_id = v_task_type))
6062: AND wdtv.organization_id = p_sign_on_org_id
6063: AND wdtv.wms_task_type_id = NVL(l_sys_task_type, wdtv.wms_task_type_id) -- restrict to picking tasks or all tasks
6064: AND wdtv.user_task_type_id = v.standard_operation_id -- join task to resource view, check if user defined task type match
6065: AND NVL(wdtv.ZONE, '@@@') = NVL(p_sign_on_zone, NVL(wdtv.ZONE, '@@@'))
6066: AND NVL(wdtv.cartonization_id, -999) = NVL(p_cartonization_id, NVL(wdtv.cartonization_id, -999))
6067: AND NVL(v.eqp_id, -999) = NVL(l_sign_on_equipment_id, NVL(v.eqp_id, -999))
6068: AND wdtv.organization_id = loc.organization_id(+) -- join task to loc, outer join for tasks do not have locator
6134: , nvl(loc.x_coordinate, 0)
6135: , nvl(loc.y_coordinate, 0)
6136: , nvl(loc.z_coordinate, 0)
6137: FROM (SELECT transaction_temp_id task_id
6138: , standard_operation_id user_task_type_id
6139: , wms_task_type wms_task_type_id
6140: , organization_id organization_id
6141: , subinventory_code ZONE
6142: , locator_id locator_id
6156: AND(wms_task_status IS NULL
6157: OR wms_task_status = 1) --Added for task planning WB. bug#2651318
6158: UNION ALL
6159: SELECT MIN(mcce.cycle_count_entry_id) task_id
6160: , MIN(mcce.standard_operation_id) user_task_type_id
6161: , 3 wms_task_type_id
6162: , mcce.organization_id organization_id
6163: , mcce.subinventory ZONE
6164: , mcce.locator_id locator_id
6180: AND mcce.cycle_count_header_id = mcch.cycle_count_header_id
6181: AND NVL(mcch.disable_date,sysdate+1)> sysdate
6182: GROUP BY mcce.cycle_count_header_id, mcce.organization_id, mcce.subinventory, mcce.locator_id, mcce.inventory_item_id, mcce.revision) wdtv
6183: , -- inlined wms_dispatchable_tasks_v, bug 2648133
6184: (SELECT utt_emp.standard_operation_id standard_operation_id
6185: , utt_emp.resource_id ROLE
6186: , utt_eqp.resource_id equipment
6187: , utt_emp.person_id emp_id
6188: , utt_eqp.inventory_item_id eqp_id
6186: , utt_eqp.resource_id equipment
6187: , utt_emp.person_id emp_id
6188: , utt_eqp.inventory_item_id eqp_id
6189: , NULL eqp_srl /* removed for bug 2095237 */
6190: FROM (SELECT x_utt_res1.standard_operation_id standard_operation_id
6191: , x_utt_res1.resource_id resource_id
6192: , x_emp_r.person_id
6193: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
6194: WHERE x_utt_res1.resource_id = r1.resource_id
6193: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
6194: WHERE x_utt_res1.resource_id = r1.resource_id
6195: AND r1.resource_type = 2
6196: AND x_utt_res1.resource_id = x_emp_r.resource_id) utt_emp
6197: , (SELECT x_utt_res2.standard_operation_id standard_operation_id
6198: , x_utt_res2.resource_id resource_id
6199: , x_eqp_r.inventory_item_id inventory_item_id
6200: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
6201: WHERE x_utt_res2.resource_id = r2.resource_id
6200: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
6201: WHERE x_utt_res2.resource_id = r2.resource_id
6202: AND r2.resource_type = 1
6203: AND x_utt_res2.resource_id = x_eqp_r.resource_id) utt_eqp
6204: WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)) v
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
6212: OR(wdtv.task_id = v_task_id
6213: AND wdtv.wms_task_type_id = v_task_type))
6214: AND wdtv.organization_id = p_sign_on_org_id
6215: AND wdtv.wms_task_type_id = NVL(l_sys_task_type, wdtv.wms_task_type_id) -- restrict to picking tasks or all tasks
6216: AND wdtv.user_task_type_id = v.standard_operation_id -- join task to resource view, check if user defined task type match
6217: AND NVL(wdtv.ZONE, '@@@') = NVL(p_sign_on_zone, NVL(wdtv.ZONE, '@@@'))
6218: AND NVL(wdtv.cartonization_id, -999) = NVL(p_cartonization_id, NVL(wdtv.cartonization_id, -999))
6219: -- AND Nvl(v.eqp_srl, '@@@') = Nvl(l_sign_on_equipment_srl, Nvl(v.eqp_srl, '@@@')) removed for bug 2095237
6220: AND NVL(v.eqp_id, -999) = NVL(l_sign_on_equipment_id, NVL(v.eqp_id, -999))
6287: , nvl(loc.x_coordinate, 0)
6288: , nvl(loc.y_coordinate, 0)
6289: , nvl(loc.z_coordinate, 0)
6290: FROM (SELECT transaction_temp_id task_id
6291: , standard_operation_id user_task_type_id
6292: , wms_task_type wms_task_type_id
6293: , organization_id organization_id
6294: , subinventory_code ZONE
6295: , locator_id locator_id
6309: AND(wms_task_status IS NULL
6310: OR wms_task_status = 1) --Added for task planning WB. bug#2651318
6311: UNION ALL
6312: SELECT MIN(mcce.cycle_count_entry_id) task_id
6313: , MIN(mcce.standard_operation_id) user_task_type_id
6314: , 3 wms_task_type_id
6315: , mcce.organization_id organization_id
6316: , mcce.subinventory ZONE
6317: , mcce.locator_id locator_id
6331: -- bug 3972076
6332: --AND NVL(TRUNC(mcce.count_due_date, 'DD'), TRUNC(SYSDATE, 'DD')) >= TRUNC(SYSDATE, 'DD')
6333: GROUP BY mcce.cycle_count_header_id, mcce.organization_id, mcce.subinventory, mcce.locator_id, mcce.inventory_item_id, mcce.revision) wdtv
6334: , -- inlined wms_dispatchable_tasks_v, bug 2648133
6335: (SELECT utt_emp.standard_operation_id standard_operation_id
6336: , utt_emp.resource_id ROLE
6337: , utt_eqp.resource_id equipment
6338: , utt_emp.person_id emp_id
6339: , utt_eqp.inventory_item_id eqp_id
6337: , utt_eqp.resource_id equipment
6338: , utt_emp.person_id emp_id
6339: , utt_eqp.inventory_item_id eqp_id
6340: , NULL eqp_srl /* removed for bug 2095237 */
6341: FROM (SELECT x_utt_res1.standard_operation_id standard_operation_id
6342: , x_utt_res1.resource_id resource_id
6343: , x_emp_r.person_id
6344: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
6345: WHERE x_utt_res1.resource_id = r1.resource_id
6344: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
6345: WHERE x_utt_res1.resource_id = r1.resource_id
6346: AND r1.resource_type = 2
6347: AND x_utt_res1.resource_id = x_emp_r.resource_id) utt_emp
6348: , (SELECT x_utt_res2.standard_operation_id standard_operation_id
6349: , x_utt_res2.resource_id resource_id
6350: , x_eqp_r.inventory_item_id inventory_item_id
6351: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
6352: WHERE x_utt_res2.resource_id = r2.resource_id
6351: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
6352: WHERE x_utt_res2.resource_id = r2.resource_id
6353: AND r2.resource_type = 1
6354: AND x_utt_res2.resource_id = x_eqp_r.resource_id) utt_eqp
6355: WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)) v
6356: , -- inlined wms_person_resource_utt_v, bug 2648133
6357: mtl_item_locations loc
6358: , mtl_secondary_inventories sub
6359: , mtl_txn_request_lines mol
6362: OR(wdtv.task_id = v_task_id
6363: AND wdtv.wms_task_type_id = v_task_type))
6364: AND wdtv.organization_id = p_sign_on_org_id
6365: AND wdtv.wms_task_type_id = NVL(l_sys_task_type, wdtv.wms_task_type_id) -- restrict to picking tasks or all tasks
6366: AND wdtv.user_task_type_id = v.standard_operation_id -- join task to resource view, check if user defined task type match
6367: AND NVL(wdtv.ZONE, '@@@') = NVL(p_sign_on_zone, NVL(wdtv.ZONE, '@@@'))
6368: AND NVL(wdtv.cartonization_id, -999) = NVL(p_cartonization_id, NVL(wdtv.cartonization_id, -999))
6369: AND NVL(v.eqp_id, -999) = NVL(l_sign_on_equipment_id, NVL(v.eqp_id, -999))
6370: AND wdtv.organization_id = loc.organization_id(+) -- join task to loc, outer join for tasks do not have locator
6435: , nvl(loc.x_coordinate, 0)
6436: , nvl(loc.y_coordinate, 0)
6437: , nvl(loc.z_coordinate, 0)
6438: FROM (SELECT transaction_temp_id task_id
6439: , standard_operation_id user_task_type_id
6440: , wms_task_type wms_task_type_id
6441: , organization_id organization_id
6442: , subinventory_code ZONE
6443: , locator_id locator_id
6457: AND(wms_task_status IS NULL
6458: OR wms_task_status = 1) --Added for task planning WB. bug#2651318
6459: UNION ALL
6460: SELECT MIN(mcce.cycle_count_entry_id) task_id
6461: , MIN(mcce.standard_operation_id) user_task_type_id
6462: , 3 wms_task_type_id
6463: , mcce.organization_id organization_id
6464: , mcce.subinventory ZONE
6465: , mcce.locator_id locator_id
6481: AND mcce.cycle_count_header_id = mcch.cycle_count_header_id
6482: AND NVL(mcch.disable_date,sysdate+1)> sysdate
6483: GROUP BY mcce.cycle_count_header_id, mcce.organization_id, mcce.subinventory, mcce.locator_id, mcce.inventory_item_id, mcce.revision) wdtv
6484: , -- inlined wms_dispatchable_tasks_v, bug 2648133
6485: (SELECT utt_emp.standard_operation_id standard_operation_id
6486: , utt_emp.resource_id ROLE
6487: , utt_eqp.resource_id equipment
6488: , utt_emp.person_id emp_id
6489: , utt_eqp.inventory_item_id eqp_id
6487: , utt_eqp.resource_id equipment
6488: , utt_emp.person_id emp_id
6489: , utt_eqp.inventory_item_id eqp_id
6490: , NULL eqp_srl /* removed for bug 2095237 */
6491: FROM (SELECT x_utt_res1.standard_operation_id standard_operation_id
6492: , x_utt_res1.resource_id resource_id
6493: , x_emp_r.person_id
6494: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
6495: WHERE x_utt_res1.resource_id = r1.resource_id
6494: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
6495: WHERE x_utt_res1.resource_id = r1.resource_id
6496: AND r1.resource_type = 2
6497: AND x_utt_res1.resource_id = x_emp_r.resource_id) utt_emp
6498: , (SELECT x_utt_res2.standard_operation_id standard_operation_id
6499: , x_utt_res2.resource_id resource_id
6500: , x_eqp_r.inventory_item_id inventory_item_id
6501: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
6502: WHERE x_utt_res2.resource_id = r2.resource_id
6501: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
6502: WHERE x_utt_res2.resource_id = r2.resource_id
6503: AND r2.resource_type = 1
6504: AND x_utt_res2.resource_id = x_eqp_r.resource_id) utt_eqp
6505: WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)) v
6506: , -- inlined wms_person_resource_utt_v, bug 2648133
6507: mtl_item_locations loc
6508: , mtl_secondary_inventories sub
6509: , mtl_txn_request_lines mol
6512: OR(wdtv.task_id = v_task_id
6513: AND wdtv.wms_task_type_id = v_task_type))
6514: AND wdtv.organization_id = p_sign_on_org_id
6515: AND wdtv.wms_task_type_id = NVL(l_sys_task_type, wdtv.wms_task_type_id) -- restrict to picking tasks or all tasks
6516: AND wdtv.user_task_type_id = v.standard_operation_id -- join task to resource view, check if user defined task type match
6517: AND NVL(wdtv.ZONE, '@@@') = NVL(p_sign_on_zone, NVL(wdtv.ZONE, '@@@')) -- bug 2648133
6518: AND NVL(wdtv.cartonization_id, -999) = NVL(p_cartonization_id, NVL(wdtv.cartonization_id, -999))
6519: AND NVL(v.eqp_id, -999) = NVL(l_sign_on_equipment_id, NVL(v.eqp_id, -999))
6520: AND wdtv.organization_id = loc.organization_id(+) -- join task to loc, outer join for tasks do not have locator
6653: wdtv.organization_id = ' || p_sign_on_org_id ||'
6654: AND Nvl(wdtv.zone, ''@@@'') = Nvl('''|| p_sign_on_zone || ''', Nvl(wdtv.zone, ''@@@''))
6655: AND wdtv.user_task_type_id IN
6656: (
6657: SELECT standard_operation_id
6658: FROM wms_person_resource_utt_v v
6659: WHERE v.emp_id = ' || p_sign_on_emp_id ||'
6660: AND Nvl(v.eqp_srl, ''@@@'') = Nvl(''' || l_sign_on_equipment_srl || ''', Nvl(v.eqp_srl, ''@@@''))
6661: AND Nvl(v.eqp_id, -999) = Nvl(' || l_equipment_id_str ||', Nvl(v.eqp_id, -999))
6789: , l_cur_y
6790: , l_cur_z
6791: FROM mtl_item_locations loc
6792: , (SELECT transaction_temp_id task_id
6793: , standard_operation_id user_task_type_id
6794: , wms_task_type wms_task_type_id
6795: , organization_id organization_id
6796: , subinventory_code ZONE
6797: , locator_id locator_id
6809: WHERE wms_task_type IS NOT NULL
6810: AND transaction_status = 2
6811: UNION ALL
6812: SELECT MIN(cycle_count_entry_id) task_id
6813: , MIN(standard_operation_id) user_task_type_id
6814: , 3 wms_task_type_id
6815: , organization_id organization_id
6816: , subinventory ZONE
6817: , locator_id locator_id
7661: ) xyz_distance
7662: , loc.concatenated_segments loc_concat_segs
7663: FROM --wms_dispatchable_tasks_v wdtv,
7664: (SELECT transaction_temp_id task_id
7665: , standard_operation_id user_task_type_id
7666: , wms_task_type wms_task_type_id
7667: , organization_id organization_id
7668: , subinventory_code ZONE
7669: , locator_id locator_id
7686: ) --Added for task planning WB. bug#2651318
7687: -- Commented out the following lines because we won't consider cycle counting taks for cluster pick
7688: /*UNION ALL
7689: SELECT MIN(cycle_count_entry_id) task_id,
7690: MIN(standard_operation_id) user_task_type_id,
7691: 3 wms_task_type_id,
7692: organization_id organization_id,
7693: subinventory zone,
7694: locator_id locator_id,
7711: */
7712: ) wdtv
7713: , -- inlined wms_dispatchable_tasks_v, bug 2648133
7714: --wms_person_resource_utt_v v,
7715: (SELECT utt_emp.standard_operation_id standard_operation_id
7716: , utt_emp.resource_id ROLE
7717: , utt_eqp.resource_id equipment
7718: , utt_emp.person_id emp_id
7719: , utt_eqp.inventory_item_id eqp_id
7717: , utt_eqp.resource_id equipment
7718: , utt_emp.person_id emp_id
7719: , utt_eqp.inventory_item_id eqp_id
7720: , NULL eqp_srl /* removed for bug 2095237 */
7721: FROM (SELECT x_utt_res1.standard_operation_id standard_operation_id
7722: , x_utt_res1.resource_id resource_id
7723: , x_emp_r.person_id
7724: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
7725: WHERE x_utt_res1.resource_id = r1.resource_id
7724: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
7725: WHERE x_utt_res1.resource_id = r1.resource_id
7726: AND r1.resource_type = 2
7727: AND x_utt_res1.resource_id = x_emp_r.resource_id) utt_emp
7728: , (SELECT x_utt_res2.standard_operation_id standard_operation_id
7729: , x_utt_res2.resource_id resource_id
7730: , x_eqp_r.inventory_item_id inventory_item_id
7731: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
7732: WHERE x_utt_res2.resource_id = r2.resource_id
7731: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
7732: WHERE x_utt_res2.resource_id = r2.resource_id
7733: AND r2.resource_type = 1
7734: AND x_utt_res2.resource_id = x_eqp_r.resource_id) utt_eqp
7735: WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)) v
7736: , -- inlined wms_person_resource_utt_v, bug 2648133
7737: mtl_item_locations_kfv loc
7738: , --changed to kfv bug#2742611
7739: mtl_secondary_inventories sub
7748: AND wdtv.wms_task_type_id = 1 -- restrict to picking tasks
7749: AND mol.header_id = moh.header_id
7750: AND moh.move_order_type = 3 -- only pick wave move orders are considered
7751: AND wdtv.user_task_type_id =
7752: v.standard_operation_id -- join task to resource view, check if user defined task type match
7753: --AND Nvl(wdtv.zone, '@@@') = Nvl(p_sign_on_zone, Nvl(wdtv.zone, '@@@'))
7754: AND wdtv.ZONE = p_sign_on_zone -- removed NVL, bug 2648133
7755: AND NVL(wdtv.cartonization_id, -999) = NVL(p_cartonization_id, NVL(wdtv.cartonization_id, -999))
7756: AND NVL(v.eqp_id, -999) = NVL(l_sign_on_equipment_id, NVL(v.eqp_id, -999))
7822: ) xyz_distance
7823: , loc.concatenated_segments loc_concat_segs
7824: FROM --wms_dispatchable_tasks_v wdtv,
7825: (SELECT transaction_temp_id task_id
7826: , standard_operation_id user_task_type_id
7827: , wms_task_type wms_task_type_id
7828: , organization_id organization_id
7829: , subinventory_code ZONE
7830: , locator_id locator_id
7847: ) --Added for task planning WB. bug#2651318
7848: -- Commented out the following lines because we won't consider cycle counting taks for cluster pick
7849: /*UNION ALL
7850: SELECT MIN(cycle_count_entry_id) task_id,
7851: MIN(standard_operation_id) user_task_type_id,
7852: 3 wms_task_type_id,
7853: organization_id organization_id,
7854: subinventory zone,
7855: locator_id locator_id,
7872: */
7873: ) wdtv
7874: , -- inlined wms_dispatchable_tasks_v, bug 2648133
7875: --wms_person_resource_utt_v v,
7876: (SELECT utt_emp.standard_operation_id standard_operation_id
7877: , utt_emp.resource_id ROLE
7878: , utt_eqp.resource_id equipment
7879: , utt_emp.person_id emp_id
7880: , utt_eqp.inventory_item_id eqp_id
7878: , utt_eqp.resource_id equipment
7879: , utt_emp.person_id emp_id
7880: , utt_eqp.inventory_item_id eqp_id
7881: , NULL eqp_srl /* removed for bug 2095237 */
7882: FROM (SELECT x_utt_res1.standard_operation_id standard_operation_id
7883: , x_utt_res1.resource_id resource_id
7884: , x_emp_r.person_id
7885: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
7886: WHERE x_utt_res1.resource_id = r1.resource_id
7885: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
7886: WHERE x_utt_res1.resource_id = r1.resource_id
7887: AND r1.resource_type = 2
7888: AND x_utt_res1.resource_id = x_emp_r.resource_id) utt_emp
7889: , (SELECT x_utt_res2.standard_operation_id standard_operation_id
7890: , x_utt_res2.resource_id resource_id
7891: , x_eqp_r.inventory_item_id inventory_item_id
7892: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
7893: WHERE x_utt_res2.resource_id = r2.resource_id
7892: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
7893: WHERE x_utt_res2.resource_id = r2.resource_id
7894: AND r2.resource_type = 1
7895: AND x_utt_res2.resource_id = x_eqp_r.resource_id) utt_eqp
7896: WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)) v
7897: , -- inlined wms_person_resource_utt_v, bug 2648133
7898: mtl_item_locations_kfv loc
7899: , --changed to kfv bug#2742611
7900: mtl_secondary_inventories sub
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
7908: AND moh.move_order_type = 5 -- only WIP jobs are considered : Bug 2666620 BackFlush Removed
7909: AND wdtv.user_task_type_id = v.standard_operation_id -- join task to resource view, check if user defined task type match
7910: --AND Nvl(wdtv.zone, '@@@') = Nvl(p_sign_on_zone, Nvl(wdtv.zone, '@@@'))
7911: AND wdtv.ZONE = p_sign_on_zone -- removed NVL, bug 2648133
7912: AND NVL(wdtv.cartonization_id, -999) = NVL(p_cartonization_id, NVL(wdtv.cartonization_id, -999))
7913: AND NVL(v.eqp_id, -999) = NVL(l_sign_on_equipment_id, NVL(v.eqp_id, -999))
7982: ) xyz_distance
7983: , loc.concatenated_segments loc_concat_segs
7984: FROM --wms_dispatchable_tasks_v wdtv,
7985: (SELECT transaction_temp_id task_id
7986: , standard_operation_id user_task_type_id
7987: , wms_task_type wms_task_type_id
7988: , organization_id organization_id
7989: , subinventory_code ZONE
7990: , locator_id locator_id
8007: ) --Added for task planning WB. bug#2651318
8008: -- Commented out the following lines because we won't consider cycle counting taks for cluster pick
8009: /*UNION ALL
8010: SELECT MIN(cycle_count_entry_id) task_id,
8011: MIN(standard_operation_id) user_task_type_id,
8012: 3 wms_task_type_id,
8013: organization_id organization_id,
8014: subinventory zone,
8015: locator_id locator_id,
8032: */
8033: ) wdtv
8034: , -- inlined wms_dispatchable_tasks_v, bug 2648133
8035: --wms_person_resource_utt_v v,
8036: (SELECT utt_emp.standard_operation_id standard_operation_id
8037: , utt_emp.resource_id ROLE
8038: , utt_eqp.resource_id equipment
8039: , utt_emp.person_id emp_id
8040: , utt_eqp.inventory_item_id eqp_id
8038: , utt_eqp.resource_id equipment
8039: , utt_emp.person_id emp_id
8040: , utt_eqp.inventory_item_id eqp_id
8041: , NULL eqp_srl /* removed for bug 2095237 */
8042: FROM (SELECT x_utt_res1.standard_operation_id standard_operation_id
8043: , x_utt_res1.resource_id resource_id
8044: , x_emp_r.person_id
8045: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
8046: WHERE x_utt_res1.resource_id = r1.resource_id
8045: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
8046: WHERE x_utt_res1.resource_id = r1.resource_id
8047: AND r1.resource_type = 2
8048: AND x_utt_res1.resource_id = x_emp_r.resource_id) utt_emp
8049: , (SELECT x_utt_res2.standard_operation_id standard_operation_id
8050: , x_utt_res2.resource_id resource_id
8051: , x_eqp_r.inventory_item_id inventory_item_id
8052: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
8053: WHERE x_utt_res2.resource_id = r2.resource_id
8052: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
8053: WHERE x_utt_res2.resource_id = r2.resource_id
8054: AND r2.resource_type = 1
8055: AND x_utt_res2.resource_id = x_eqp_r.resource_id) utt_eqp
8056: WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)) v
8057: , -- inlined wms_person_resource_utt_v, bug 2648133
8058: mtl_item_locations_kfv loc
8059: , --changed to kfv bug#2742611
8060: mtl_secondary_inventories sub
8069: AND wdtv.wms_task_type_id = 1 -- restrict to picking tasks
8070: AND mol.header_id = moh.header_id
8071: AND moh.move_order_type = 3 -- only pick wave move orders are considered
8072: AND wdtv.user_task_type_id =
8073: v.standard_operation_id -- join task to resource view, check if user defined task type match
8074: --AND Nvl(wdtv.zone, '@@@') = Nvl(p_sign_on_zone, Nvl(wdtv.zone, '@@@'))
8075: AND NVL(wdtv.cartonization_id, -999) = NVL(p_cartonization_id, NVL(wdtv.cartonization_id, -999))
8076: AND NVL(v.eqp_id, -999) = NVL(l_sign_on_equipment_id, NVL(v.eqp_id, -999))
8077: AND wdtv.organization_id = loc.organization_id(+) -- join task to loc, outer join for tasks do not have locator
8142: ) xyz_distance
8143: , loc.concatenated_segments loc_concat_segs
8144: FROM --wms_dispatchable_tasks_v wdtv,
8145: (SELECT transaction_temp_id task_id
8146: , standard_operation_id user_task_type_id
8147: , wms_task_type wms_task_type_id
8148: , organization_id organization_id
8149: , subinventory_code ZONE
8150: , locator_id locator_id
8167: ) --Added for task planning WB. bug#2651318
8168: -- Commented out the following lines because we won't consider cycle counting taks for cluster pick
8169: /*UNION ALL
8170: SELECT MIN(cycle_count_entry_id) task_id,
8171: MIN(standard_operation_id) user_task_type_id,
8172: 3 wms_task_type_id,
8173: organization_id organization_id,
8174: subinventory zone,
8175: locator_id locator_id,
8192: */
8193: ) wdtv
8194: , -- inlined wms_dispatchable_tasks_v, bug 2648133
8195: --wms_person_resource_utt_v v,
8196: (SELECT utt_emp.standard_operation_id standard_operation_id
8197: , utt_emp.resource_id ROLE
8198: , utt_eqp.resource_id equipment
8199: , utt_emp.person_id emp_id
8200: , utt_eqp.inventory_item_id eqp_id
8198: , utt_eqp.resource_id equipment
8199: , utt_emp.person_id emp_id
8200: , utt_eqp.inventory_item_id eqp_id
8201: , NULL eqp_srl /* removed for bug 2095237 */
8202: FROM (SELECT x_utt_res1.standard_operation_id standard_operation_id
8203: , x_utt_res1.resource_id resource_id
8204: , x_emp_r.person_id
8205: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
8206: WHERE x_utt_res1.resource_id = r1.resource_id
8205: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
8206: WHERE x_utt_res1.resource_id = r1.resource_id
8207: AND r1.resource_type = 2
8208: AND x_utt_res1.resource_id = x_emp_r.resource_id) utt_emp
8209: , (SELECT x_utt_res2.standard_operation_id standard_operation_id
8210: , x_utt_res2.resource_id resource_id
8211: , x_eqp_r.inventory_item_id inventory_item_id
8212: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
8213: WHERE x_utt_res2.resource_id = r2.resource_id
8212: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
8213: WHERE x_utt_res2.resource_id = r2.resource_id
8214: AND r2.resource_type = 1
8215: AND x_utt_res2.resource_id = x_eqp_r.resource_id) utt_eqp
8216: WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)) v
8217: , -- inlined wms_person_resource_utt_v, bug 2648133
8218: mtl_item_locations_kfv loc
8219: , --changed to kfv bug#2742611
8220: mtl_secondary_inventories sub
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
8228: AND moh.move_order_type = 5 -- only WIP jobs are considered : Bug 2666620 BackFlush Removed
8229: AND wdtv.user_task_type_id = v.standard_operation_id -- join task to resource view, check if user defined task type match
8230: --AND Nvl(wdtv.zone, '@@@') = Nvl(p_sign_on_zone, Nvl(wdtv.zone, '@@@'))
8231: AND NVL(wdtv.cartonization_id, -999) = NVL(p_cartonization_id, NVL(wdtv.cartonization_id, -999))
8232: AND NVL(v.eqp_id, -999) = NVL(l_sign_on_equipment_id, NVL(v.eqp_id, -999))
8233: AND wdtv.organization_id = loc.organization_id(+) -- join task to loc, outer join for tasks do not have locator
8310: , wdt.person_resource_id
8311: , wdt.machine_resource_id
8312: FROM wms_dispatched_tasks wdt,
8313: (SELECT transaction_temp_id task_id
8314: , standard_operation_id user_task_type_id
8315: , wms_task_type wms_task_type_id
8316: , organization_id organization_id
8317: , subinventory_code ZONE
8318: , locator_id locator_id
8336: OR wms_task_status = 1
8337: ) --Added for task planning WB. bug#2651318
8338: ) qt
8339: , (SELECT
8340: bsor.standard_operation_id,
8341: bre.resource_id,
8342: bre.inventory_item_id equipment_id
8343: FROM bom_std_op_resources bsor, bom_resources br, bom_resource_equipments bre
8344: WHERE bsor.resource_id = bre.resource_id
8360: AND qt.wms_task_type_id = 1 -- restrict to picking tasks
8361: AND mol.header_id = moh.header_id
8362: AND moh.move_order_type = 3 -- only pick wave move orders are considered
8363: AND qt.user_task_type_id =
8364: e.standard_operation_id(+) -- join task to resource view, check if user defined task type match
8365: --AND Nvl(wdtv.zone, '@@@') = Nvl(p_sign_on_zone, Nvl(wdtv.zone, '@@@'))
8366: AND qt.ZONE = p_sign_on_zone -- removed NVL, bug 2648133
8367: AND NVL(qt.cartonization_id, -999) = NVL(p_cartonization_id, NVL(qt.cartonization_id, -999))
8368: AND NVL(e.equipment_id, -999) = NVL(l_sign_on_equipment_id, NVL(e.equipment_id, -999))
8397: ,v.role person_resource_id
8398: ,v.equipment machine_resource_id
8399: FROM --wms_dispatchable_tasks_v wdtv,
8400: (SELECT transaction_temp_id task_id
8401: , standard_operation_id user_task_type_id
8402: , wms_task_type wms_task_type_id
8403: , organization_id organization_id
8404: , subinventory_code ZONE
8405: , locator_id locator_id
8424: ) --Added for task planning WB. bug#2651318
8425: ) wdtv
8426: , -- inlined wms_dispatchable_tasks_v, bug 2648133
8427: --wms_person_resource_utt_v v,
8428: (SELECT utt_emp.standard_operation_id standard_operation_id
8429: , utt_emp.resource_id ROLE
8430: , utt_eqp.resource_id equipment
8431: , utt_emp.person_id emp_id
8432: , utt_eqp.inventory_item_id eqp_id
8432: , utt_eqp.inventory_item_id eqp_id
8433: , NULL eqp_srl /* removed for bug 2095237 */
8434: , utt_emp.effective_start_date
8435: , utt_emp.effective_end_date
8436: FROM (SELECT x_utt_res1.standard_operation_id standard_operation_id
8437: , x_utt_res1.resource_id resource_id
8438: , x_emp_r.person_id
8439: , x_emp_r.effective_start_date
8440: , x_emp_r.effective_end_date
8441: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
8442: WHERE x_utt_res1.resource_id = r1.resource_id
8443: AND r1.resource_type = 2
8444: AND x_utt_res1.resource_id = x_emp_r.resource_id) utt_emp
8445: , (SELECT x_utt_res2.standard_operation_id standard_operation_id
8446: , x_utt_res2.resource_id resource_id
8447: , x_eqp_r.inventory_item_id inventory_item_id
8448: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
8449: WHERE x_utt_res2.resource_id = r2.resource_id
8448: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
8449: WHERE x_utt_res2.resource_id = r2.resource_id
8450: AND r2.resource_type = 1
8451: AND x_utt_res2.resource_id = x_eqp_r.resource_id) utt_eqp
8452: WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)) v
8453: , -- inlined wms_person_resource_utt_v, bug 2648133
8454: mtl_item_locations_kfv loc
8455: , --changed to kfv bug#2742611
8456: mtl_secondary_inventories sub
8465: AND wdtv.wms_task_type_id = 1 -- restrict to picking tasks
8466: AND mol.header_id = moh.header_id
8467: AND moh.move_order_type = 3 -- only pick wave move orders are considered
8468: AND wdtv.user_task_type_id =
8469: v.standard_operation_id -- join task to resource view, check if user defined task type match
8470: --AND Nvl(wdtv.zone, '@@@') = Nvl(p_sign_on_zone, Nvl(wdtv.zone, '@@@'))
8471: AND wdtv.ZONE = p_sign_on_zone -- removed NVL, bug 2648133
8472: AND NVL(wdtv.cartonization_id, -999) = NVL(p_cartonization_id, NVL(wdtv.cartonization_id, -999))
8473: AND NVL(v.eqp_id, -999) = NVL(l_sign_on_equipment_id, NVL(v.eqp_id, -999))
8554: , wdt.person_resource_id
8555: , wdt.machine_resource_id
8556: FROM wms_dispatched_tasks wdt,
8557: (SELECT transaction_temp_id task_id
8558: , standard_operation_id user_task_type_id
8559: , wms_task_type wms_task_type_id
8560: , organization_id organization_id
8561: , subinventory_code ZONE
8562: , locator_id locator_id
8578: OR wms_task_status = 1
8579: ) --Added for task planning WB. bug#2651318
8580: ) wdtv
8581: , (SELECT
8582: bsor.standard_operation_id,
8583: bre.resource_id,
8584: bre.inventory_item_id equipment_id
8585: FROM bom_std_op_resources bsor, bom_resources br, bom_resource_equipments bre
8586: WHERE bsor.resource_id = bre.resource_id
8598: AND wdt.organization_id = p_sign_on_org_id
8599: AND wdtv.wms_task_type_id = 1 -- restrict to picking tasks
8600: AND mol.header_id = moh.header_id
8601: AND moh.move_order_type = 5 -- only WIP jobs are considered : Bug 2666620 BackFlush Removed
8602: AND wdtv.user_task_type_id = e.standard_operation_id(+) -- join task to resource view, check if user defined task type match
8603: AND wdtv.ZONE = p_sign_on_zone -- removed NVL, bug 2648133
8604: AND NVL(wdtv.cartonization_id, -999) = NVL(p_cartonization_id, NVL(wdtv.cartonization_id, -999))
8605: AND NVL(e.equipment_id, -999) = NVL(l_sign_on_equipment_id, NVL(e.equipment_id, -999))
8606: AND wdtv.organization_id = loc.organization_id
8631: ,v.role person_resource_id
8632: ,v.equipment machine_resource_id
8633: FROM --wms_dispatchable_tasks_v wdtv,
8634: (SELECT transaction_temp_id task_id
8635: , standard_operation_id user_task_type_id
8636: , wms_task_type wms_task_type_id
8637: , organization_id organization_id
8638: , subinventory_code ZONE
8639: , locator_id locator_id
8656: ) --Added for task planning WB. bug#2651318
8657: ) wdtv
8658: , -- inlined wms_dispatchable_tasks_v, bug 2648133
8659: --wms_person_resource_utt_v v,
8660: (SELECT utt_emp.standard_operation_id standard_operation_id
8661: , utt_emp.resource_id ROLE
8662: , utt_eqp.resource_id equipment
8663: , utt_emp.person_id emp_id
8664: , utt_eqp.inventory_item_id eqp_id
8664: , utt_eqp.inventory_item_id eqp_id
8665: , NULL eqp_srl /* removed for bug 2095237 */
8666: , utt_emp.effective_start_date
8667: , utt_emp.effective_end_date
8668: FROM (SELECT x_utt_res1.standard_operation_id standard_operation_id
8669: , x_utt_res1.resource_id resource_id
8670: , x_emp_r.person_id
8671: , x_emp_r.effective_start_date
8672: , x_emp_r.effective_end_date
8673: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
8674: WHERE x_utt_res1.resource_id = r1.resource_id
8675: AND r1.resource_type = 2
8676: AND x_utt_res1.resource_id = x_emp_r.resource_id) utt_emp
8677: , (SELECT x_utt_res2.standard_operation_id standard_operation_id
8678: , x_utt_res2.resource_id resource_id
8679: , x_eqp_r.inventory_item_id inventory_item_id
8680: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
8681: WHERE x_utt_res2.resource_id = r2.resource_id
8680: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
8681: WHERE x_utt_res2.resource_id = r2.resource_id
8682: AND r2.resource_type = 1
8683: AND x_utt_res2.resource_id = x_eqp_r.resource_id) utt_eqp
8684: WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)) v
8685: , -- inlined wms_person_resource_utt_v, bug 2648133
8686: mtl_item_locations_kfv loc
8687: , --changed to kfv bug#2742611
8688: mtl_secondary_inventories sub
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
8696: AND moh.move_order_type = 5 -- only WIP jobs are considered : Bug 2666620 BackFlush Removed
8697: AND wdtv.user_task_type_id = v.standard_operation_id -- join task to resource view, check if user defined task type match
8698: --AND Nvl(wdtv.zone, '@@@') = Nvl(p_sign_on_zone, Nvl(wdtv.zone, '@@@'))
8699: AND wdtv.ZONE = p_sign_on_zone -- removed NVL, bug 2648133
8700: AND NVL(wdtv.cartonization_id, -999) = NVL(p_cartonization_id, NVL(wdtv.cartonization_id, -999))
8701: AND NVL(v.eqp_id, -999) = NVL(l_sign_on_equipment_id, NVL(v.eqp_id, -999))
8782: , wdt.person_resource_id
8783: , wdt.machine_resource_id
8784: FROM wms_dispatched_tasks wdt,
8785: (SELECT transaction_temp_id task_id
8786: , standard_operation_id user_task_type_id
8787: , wms_task_type wms_task_type_id
8788: , organization_id organization_id
8789: , subinventory_code ZONE
8790: , locator_id locator_id
8808: OR wms_task_status = 1
8809: ) --Added for task planning WB. bug#2651318
8810: ) qt
8811: , (SELECT
8812: bsor.standard_operation_id,
8813: bre.resource_id,
8814: bre.inventory_item_id equipment_id
8815: FROM bom_std_op_resources bsor, bom_resources br, bom_resource_equipments bre
8816: WHERE bsor.resource_id = bre.resource_id
8832: AND qt.wms_task_type_id = 1 -- restrict to picking tasks
8833: AND mol.header_id = moh.header_id
8834: AND moh.move_order_type = 3 -- only pick wave move orders are considered
8835: AND qt.user_task_type_id =
8836: e.standard_operation_id(+) -- join task to resource view, check if user defined task type match
8837: --AND Nvl(wdtv.zone, '@@@') = Nvl(p_sign_on_zone, Nvl(wdtv.zone, '@@@'))
8838: AND qt.ZONE = p_sign_on_zone -- removed NVL, bug 2648133
8839: AND NVL(qt.cartonization_id, -999) = NVL(p_cartonization_id, NVL(qt.cartonization_id, -999))
8840: AND NVL(e.equipment_id, -999) = NVL(l_sign_on_equipment_id, NVL(e.equipment_id, -999))
8869: ,v.role person_resource_id
8870: ,v.equipment machine_resource_id
8871: FROM --wms_dispatchable_tasks_v wdtv,
8872: (SELECT transaction_temp_id task_id
8873: , standard_operation_id user_task_type_id
8874: , wms_task_type wms_task_type_id
8875: , organization_id organization_id
8876: , subinventory_code ZONE
8877: , locator_id locator_id
8896: ) --Added for task planning WB. bug#2651318
8897: ) wdtv
8898: , -- inlined wms_dispatchable_tasks_v, bug 2648133
8899: --wms_person_resource_utt_v v,
8900: (SELECT utt_emp.standard_operation_id standard_operation_id
8901: , utt_emp.resource_id ROLE
8902: , utt_eqp.resource_id equipment
8903: , utt_emp.person_id emp_id
8904: , utt_eqp.inventory_item_id eqp_id
8904: , utt_eqp.inventory_item_id eqp_id
8905: , NULL eqp_srl /* removed for bug 2095237 */
8906: , utt_emp.effective_start_date
8907: , utt_emp.effective_end_date
8908: FROM (SELECT x_utt_res1.standard_operation_id standard_operation_id
8909: , x_utt_res1.resource_id resource_id
8910: , x_emp_r.person_id
8911: , x_emp_r.effective_start_date
8912: , x_emp_r.effective_end_date
8913: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
8914: WHERE x_utt_res1.resource_id = r1.resource_id
8915: AND r1.resource_type = 2
8916: AND x_utt_res1.resource_id = x_emp_r.resource_id) utt_emp
8917: , (SELECT x_utt_res2.standard_operation_id standard_operation_id
8918: , x_utt_res2.resource_id resource_id
8919: , x_eqp_r.inventory_item_id inventory_item_id
8920: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
8921: WHERE x_utt_res2.resource_id = r2.resource_id
8920: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
8921: WHERE x_utt_res2.resource_id = r2.resource_id
8922: AND r2.resource_type = 1
8923: AND x_utt_res2.resource_id = x_eqp_r.resource_id) utt_eqp
8924: WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)) v
8925: , -- inlined wms_person_resource_utt_v, bug 2648133
8926: mtl_item_locations_kfv loc
8927: , --changed to kfv bug#2742611
8928: mtl_secondary_inventories sub
8937: AND wdtv.wms_task_type_id = 1 -- restrict to picking tasks
8938: AND mol.header_id = moh.header_id
8939: AND moh.move_order_type = 3 -- only pick wave move orders are considered
8940: AND wdtv.user_task_type_id =
8941: v.standard_operation_id -- join task to resource view, check if user defined task type match
8942: --AND Nvl(wdtv.zone, '@@@') = Nvl(p_sign_on_zone, Nvl(wdtv.zone, '@@@'))
8943: AND wdtv.ZONE = p_sign_on_zone -- removed NVL, bug 2648133
8944: AND NVL(wdtv.cartonization_id, -999) = NVL(p_cartonization_id, NVL(wdtv.cartonization_id, -999))
8945: AND NVL(v.eqp_id, -999) = NVL(l_sign_on_equipment_id, NVL(v.eqp_id, -999))
9016: , wdt.person_resource_id
9017: , wdt.machine_resource_id
9018: FROM wms_dispatched_tasks wdt,
9019: (SELECT transaction_temp_id task_id
9020: , standard_operation_id user_task_type_id
9021: , wms_task_type wms_task_type_id
9022: , organization_id organization_id
9023: , subinventory_code ZONE
9024: , locator_id locator_id
9040: OR wms_task_status = 1
9041: ) --Added for task planning WB. bug#2651318
9042: ) wdtv
9043: , (SELECT
9044: bsor.standard_operation_id,
9045: bre.resource_id,
9046: bre.inventory_item_id equipment_id
9047: FROM bom_std_op_resources bsor, bom_resources br, bom_resource_equipments bre
9048: WHERE bsor.resource_id = bre.resource_id
9060: AND wdt.organization_id = p_sign_on_org_id
9061: AND wdtv.wms_task_type_id = 1 -- restrict to picking tasks
9062: AND mol.header_id = moh.header_id
9063: AND moh.move_order_type = 5 -- only WIP jobs are considered : Bug 2666620 BackFlush Removed
9064: AND wdtv.user_task_type_id = e.standard_operation_id(+) -- join task to resource view, check if user defined task type match
9065: AND wdtv.ZONE = p_sign_on_zone -- removed NVL, bug 2648133
9066: AND NVL(wdtv.cartonization_id, -999) = NVL(p_cartonization_id, NVL(wdtv.cartonization_id, -999))
9067: AND NVL(e.equipment_id, -999) = NVL(l_sign_on_equipment_id, NVL(e.equipment_id, -999))
9068: AND wdtv.organization_id = loc.organization_id
9095: ,v.role person_resource_id
9096: ,v.equipment machine_resource_id
9097: FROM --wms_dispatchable_tasks_v wdtv,
9098: (SELECT transaction_temp_id task_id
9099: , standard_operation_id user_task_type_id
9100: , wms_task_type wms_task_type_id
9101: , organization_id organization_id
9102: , subinventory_code ZONE
9103: , locator_id locator_id
9120: ) --Added for task planning WB. bug#2651318
9121: ) wdtv
9122: , -- inlined wms_dispatchable_tasks_v, bug 2648133
9123: --wms_person_resource_utt_v v,
9124: (SELECT utt_emp.standard_operation_id standard_operation_id
9125: , utt_emp.resource_id ROLE
9126: , utt_eqp.resource_id equipment
9127: , utt_emp.person_id emp_id
9128: , utt_eqp.inventory_item_id eqp_id
9128: , utt_eqp.inventory_item_id eqp_id
9129: , NULL eqp_srl /* removed for bug 2095237 */
9130: , utt_emp.effective_start_date
9131: , utt_emp.effective_end_date
9132: FROM (SELECT x_utt_res1.standard_operation_id standard_operation_id
9133: , x_utt_res1.resource_id resource_id
9134: , x_emp_r.person_id
9135: , x_emp_r.effective_start_date
9136: , x_emp_r.effective_end_date
9137: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
9138: WHERE x_utt_res1.resource_id = r1.resource_id
9139: AND r1.resource_type = 2
9140: AND x_utt_res1.resource_id = x_emp_r.resource_id) utt_emp
9141: , (SELECT x_utt_res2.standard_operation_id standard_operation_id
9142: , x_utt_res2.resource_id resource_id
9143: , x_eqp_r.inventory_item_id inventory_item_id
9144: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
9145: WHERE x_utt_res2.resource_id = r2.resource_id
9144: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
9145: WHERE x_utt_res2.resource_id = r2.resource_id
9146: AND r2.resource_type = 1
9147: AND x_utt_res2.resource_id = x_eqp_r.resource_id) utt_eqp
9148: WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)) v
9149: , -- inlined wms_person_resource_utt_v, bug 2648133
9150: mtl_item_locations_kfv loc
9151: , --changed to kfv bug#2742611
9152: mtl_secondary_inventories sub
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
9160: AND moh.move_order_type = 5 -- only WIP jobs are considered : Bug 2666620 BackFlush Removed
9161: AND wdtv.user_task_type_id = v.standard_operation_id -- join task to resource view, check if user defined task type match
9162: --AND Nvl(wdtv.zone, '@@@') = Nvl(p_sign_on_zone, Nvl(wdtv.zone, '@@@'))
9163: AND wdtv.ZONE = p_sign_on_zone -- removed NVL, bug 2648133
9164: AND NVL(wdtv.cartonization_id, -999) = NVL(p_cartonization_id, NVL(wdtv.cartonization_id, -999))
9165: AND NVL(v.eqp_id, -999) = NVL(l_sign_on_equipment_id, NVL(v.eqp_id, -999))
9246: , wdt.person_resource_id
9247: , wdt.machine_resource_id
9248: FROM wms_dispatched_tasks wdt,
9249: (SELECT transaction_temp_id task_id
9250: , standard_operation_id user_task_type_id
9251: , wms_task_type wms_task_type_id
9252: , organization_id organization_id
9253: , subinventory_code ZONE
9254: , locator_id locator_id
9272: OR wms_task_status = 1
9273: ) --Added for task planning WB. bug#2651318
9274: ) qt
9275: , (SELECT
9276: bsor.standard_operation_id,
9277: bre.resource_id,
9278: bre.inventory_item_id equipment_id
9279: FROM bom_std_op_resources bsor, bom_resources br, bom_resource_equipments bre
9280: WHERE bsor.resource_id = bre.resource_id
9296: AND qt.wms_task_type_id = 1 -- restrict to picking tasks
9297: AND mol.header_id = moh.header_id
9298: AND moh.move_order_type = 3 -- only pick wave move orders are considered
9299: AND qt.user_task_type_id =
9300: e.standard_operation_id(+)
9301: AND NVL(qt.cartonization_id, -999) = NVL(p_cartonization_id, NVL(qt.cartonization_id, -999))
9302: AND NVL(e.equipment_id, -999) = NVL(l_sign_on_equipment_id, NVL(e.equipment_id, -999))
9303: AND qt.organization_id = loc.organization_id
9304: AND qt.locator_id = loc.inventory_location_id
9331: ,v.role person_resource_id
9332: ,v.equipment machine_resource_id
9333: FROM --wms_dispatchable_tasks_v wdtv,
9334: (SELECT transaction_temp_id task_id
9335: , standard_operation_id user_task_type_id
9336: , wms_task_type wms_task_type_id
9337: , organization_id organization_id
9338: , subinventory_code ZONE
9339: , locator_id locator_id
9358: ) --Added for task planning WB. bug#2651318
9359: ) wdtv
9360: , -- inlined wms_dispatchable_tasks_v, bug 2648133
9361: --wms_person_resource_utt_v v,
9362: (SELECT utt_emp.standard_operation_id standard_operation_id
9363: , utt_emp.resource_id ROLE
9364: , utt_eqp.resource_id equipment
9365: , utt_emp.person_id emp_id
9366: , utt_eqp.inventory_item_id eqp_id
9366: , utt_eqp.inventory_item_id eqp_id
9367: , NULL eqp_srl /* removed for bug 2095237 */
9368: , utt_emp.effective_start_date
9369: , utt_emp.effective_end_date
9370: FROM (SELECT x_utt_res1.standard_operation_id standard_operation_id
9371: , x_utt_res1.resource_id resource_id
9372: , x_emp_r.person_id
9373: , x_emp_r.effective_start_date
9374: , x_emp_r.effective_end_date
9375: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
9376: WHERE x_utt_res1.resource_id = r1.resource_id
9377: AND r1.resource_type = 2
9378: AND x_utt_res1.resource_id = x_emp_r.resource_id) utt_emp
9379: , (SELECT x_utt_res2.standard_operation_id standard_operation_id
9380: , x_utt_res2.resource_id resource_id
9381: , x_eqp_r.inventory_item_id inventory_item_id
9382: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
9383: WHERE x_utt_res2.resource_id = r2.resource_id
9382: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
9383: WHERE x_utt_res2.resource_id = r2.resource_id
9384: AND r2.resource_type = 1
9385: AND x_utt_res2.resource_id = x_eqp_r.resource_id) utt_eqp
9386: WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)) v
9387: , -- inlined wms_person_resource_utt_v, bug 2648133
9388: mtl_item_locations_kfv loc
9389: , --changed to kfv bug#2742611
9390: mtl_secondary_inventories sub
9399: AND wdtv.wms_task_type_id = 1 -- restrict to picking tasks
9400: AND mol.header_id = moh.header_id
9401: AND moh.move_order_type = 3 -- only pick wave move orders are considered
9402: AND wdtv.user_task_type_id =
9403: v.standard_operation_id -- join task to resource view, check if user defined task type match
9404: AND NVL(wdtv.cartonization_id, -999) = NVL(p_cartonization_id, NVL(wdtv.cartonization_id, -999))
9405: AND NVL(v.eqp_id, -999) = NVL(l_sign_on_equipment_id, NVL(v.eqp_id, -999))
9406: AND wdtv.organization_id = loc.organization_id(+) -- join task to loc, outer join for tasks do not have locator
9407: AND wdtv.locator_id = loc.inventory_location_id(+)
9485: , wdt.person_resource_id
9486: , wdt.machine_resource_id
9487: FROM wms_dispatched_tasks wdt,
9488: (SELECT transaction_temp_id task_id
9489: , standard_operation_id user_task_type_id
9490: , wms_task_type wms_task_type_id
9491: , organization_id organization_id
9492: , subinventory_code ZONE
9493: , locator_id locator_id
9509: OR wms_task_status = 1
9510: ) --Added for task planning WB. bug#2651318
9511: ) wdtv
9512: , (SELECT
9513: bsor.standard_operation_id,
9514: bre.resource_id,
9515: bre.inventory_item_id equipment_id
9516: FROM bom_std_op_resources bsor, bom_resources br, bom_resource_equipments bre
9517: WHERE bsor.resource_id = bre.resource_id
9529: AND wdt.organization_id = p_sign_on_org_id
9530: AND wdtv.wms_task_type_id = 1 -- restrict to picking tasks
9531: AND mol.header_id = moh.header_id
9532: AND moh.move_order_type = 5 -- only WIP jobs are considered : Bug 2666620 BackFlush Removed
9533: AND wdtv.user_task_type_id = e.standard_operation_id(+) -- join task to resource view, check if user defined task type match
9534: AND NVL(wdtv.cartonization_id, -999) = NVL(p_cartonization_id, NVL(wdtv.cartonization_id, -999))
9535: AND NVL(e.equipment_id, -999) = NVL(l_sign_on_equipment_id, NVL(e.equipment_id, -999))
9536: AND wdtv.organization_id = loc.organization_id
9537: AND wdtv.locator_id = loc.inventory_location_id
9563: ,v.role person_resource_id
9564: ,v.equipment machine_resource_id
9565: FROM --wms_dispatchable_tasks_v wdtv,
9566: (SELECT transaction_temp_id task_id
9567: , standard_operation_id user_task_type_id
9568: , wms_task_type wms_task_type_id
9569: , organization_id organization_id
9570: , subinventory_code ZONE
9571: , locator_id locator_id
9588: ) --Added for task planning WB. bug#2651318
9589: ) wdtv
9590: , -- inlined wms_dispatchable_tasks_v, bug 2648133
9591: --wms_person_resource_utt_v v,
9592: (SELECT utt_emp.standard_operation_id standard_operation_id
9593: , utt_emp.resource_id ROLE
9594: , utt_eqp.resource_id equipment
9595: , utt_emp.person_id emp_id
9596: , utt_eqp.inventory_item_id eqp_id
9596: , utt_eqp.inventory_item_id eqp_id
9597: , NULL eqp_srl /* removed for bug 2095237 */
9598: , utt_emp.effective_start_date
9599: , utt_emp.effective_end_date
9600: FROM (SELECT x_utt_res1.standard_operation_id standard_operation_id
9601: , x_utt_res1.resource_id resource_id
9602: , x_emp_r.person_id
9603: , x_emp_r.effective_start_date
9604: , x_emp_r.effective_end_date
9605: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
9606: WHERE x_utt_res1.resource_id = r1.resource_id
9607: AND r1.resource_type = 2
9608: AND x_utt_res1.resource_id = x_emp_r.resource_id) utt_emp
9609: , (SELECT x_utt_res2.standard_operation_id standard_operation_id
9610: , x_utt_res2.resource_id resource_id
9611: , x_eqp_r.inventory_item_id inventory_item_id
9612: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
9613: WHERE x_utt_res2.resource_id = r2.resource_id
9612: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
9613: WHERE x_utt_res2.resource_id = r2.resource_id
9614: AND r2.resource_type = 1
9615: AND x_utt_res2.resource_id = x_eqp_r.resource_id) utt_eqp
9616: WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)) v
9617: , -- inlined wms_person_resource_utt_v, bug 2648133
9618: mtl_item_locations_kfv loc
9619: , --changed to kfv bug#2742611
9620: mtl_secondary_inventories sub
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
9628: AND moh.move_order_type = 5 -- only WIP jobs are considered : Bug 2666620 BackFlush Removed
9629: AND wdtv.user_task_type_id = v.standard_operation_id -- join task to resource view, check if user defined task type match
9630: --AND Nvl(wdtv.zone, '@@@') = Nvl(p_sign_on_zone, Nvl(wdtv.zone, '@@@'))
9631: AND NVL(wdtv.cartonization_id, -999) = NVL(p_cartonization_id, NVL(wdtv.cartonization_id, -999))
9632: AND NVL(v.eqp_id, -999) = NVL(l_sign_on_equipment_id, NVL(v.eqp_id, -999))
9633: AND wdtv.organization_id = loc.organization_id(+) -- join task to loc, outer join for tasks do not have locator
9710: , wdt.person_resource_id
9711: , wdt.machine_resource_id
9712: FROM wms_dispatched_tasks wdt,
9713: (SELECT transaction_temp_id task_id
9714: , standard_operation_id user_task_type_id
9715: , wms_task_type wms_task_type_id
9716: , organization_id organization_id
9717: , subinventory_code ZONE
9718: , locator_id locator_id
9736: OR wms_task_status = 1
9737: ) --Added for task planning WB. bug#2651318
9738: ) qt
9739: , (SELECT
9740: bsor.standard_operation_id,
9741: bre.resource_id,
9742: bre.inventory_item_id equipment_id
9743: FROM bom_std_op_resources bsor, bom_resources br, bom_resource_equipments bre
9744: WHERE bsor.resource_id = bre.resource_id
9760: AND qt.wms_task_type_id = 1 -- restrict to picking tasks
9761: AND mol.header_id = moh.header_id
9762: AND moh.move_order_type = 3 -- only pick wave move orders are considered
9763: AND qt.user_task_type_id =
9764: e.standard_operation_id(+)
9765: AND NVL(qt.cartonization_id, -999) = NVL(p_cartonization_id, NVL(qt.cartonization_id, -999))
9766: AND NVL(e.equipment_id, -999) = NVL(l_sign_on_equipment_id, NVL(e.equipment_id, -999))
9767: AND qt.organization_id = loc.organization_id
9768: AND qt.locator_id = loc.inventory_location_id
9795: ,v.role person_resource_id
9796: ,v.equipment machine_resource_id
9797: FROM --wms_dispatchable_tasks_v wdtv,
9798: (SELECT transaction_temp_id task_id
9799: , standard_operation_id user_task_type_id
9800: , wms_task_type wms_task_type_id
9801: , organization_id organization_id
9802: , subinventory_code ZONE
9803: , locator_id locator_id
9822: ) --Added for task planning WB. bug#2651318
9823: ) wdtv
9824: , -- inlined wms_dispatchable_tasks_v, bug 2648133
9825: --wms_person_resource_utt_v v,
9826: (SELECT utt_emp.standard_operation_id standard_operation_id
9827: , utt_emp.resource_id ROLE
9828: , utt_eqp.resource_id equipment
9829: , utt_emp.person_id emp_id
9830: , utt_eqp.inventory_item_id eqp_id
9830: , utt_eqp.inventory_item_id eqp_id
9831: , NULL eqp_srl /* removed for bug 2095237 */
9832: , utt_emp.effective_start_date
9833: , utt_emp.effective_end_date
9834: FROM (SELECT x_utt_res1.standard_operation_id standard_operation_id
9835: , x_utt_res1.resource_id resource_id
9836: , x_emp_r.person_id
9837: , x_emp_r.effective_start_date
9838: , x_emp_r.effective_end_date
9839: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
9840: WHERE x_utt_res1.resource_id = r1.resource_id
9841: AND r1.resource_type = 2
9842: AND x_utt_res1.resource_id = x_emp_r.resource_id) utt_emp
9843: , (SELECT x_utt_res2.standard_operation_id standard_operation_id
9844: , x_utt_res2.resource_id resource_id
9845: , x_eqp_r.inventory_item_id inventory_item_id
9846: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
9847: WHERE x_utt_res2.resource_id = r2.resource_id
9846: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
9847: WHERE x_utt_res2.resource_id = r2.resource_id
9848: AND r2.resource_type = 1
9849: AND x_utt_res2.resource_id = x_eqp_r.resource_id) utt_eqp
9850: WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)) v
9851: , -- inlined wms_person_resource_utt_v, bug 2648133
9852: mtl_item_locations_kfv loc
9853: , --changed to kfv bug#2742611
9854: mtl_secondary_inventories sub
9863: AND wdtv.wms_task_type_id = 1 -- restrict to picking tasks
9864: AND mol.header_id = moh.header_id
9865: AND moh.move_order_type = 3 -- only pick wave move orders are considered
9866: AND wdtv.user_task_type_id =
9867: v.standard_operation_id -- join task to resource view, check if user defined task type match
9868: --AND Nvl(wdtv.zone, '@@@') = Nvl(p_sign_on_zone, Nvl(wdtv.zone, '@@@'))
9869: AND NVL(wdtv.cartonization_id, -999) = NVL(p_cartonization_id, NVL(wdtv.cartonization_id, -999))
9870: AND NVL(v.eqp_id, -999) = NVL(l_sign_on_equipment_id, NVL(v.eqp_id, -999))
9871: AND wdtv.organization_id = loc.organization_id(+) -- join task to loc, outer join for tasks do not have locator
9941: , wdt.person_resource_id
9942: , wdt.machine_resource_id
9943: FROM wms_dispatched_tasks wdt,
9944: (SELECT transaction_temp_id task_id
9945: , standard_operation_id user_task_type_id
9946: , wms_task_type wms_task_type_id
9947: , organization_id organization_id
9948: , subinventory_code ZONE
9949: , locator_id locator_id
9965: OR wms_task_status = 1
9966: ) --Added for task planning WB. bug#2651318
9967: ) wdtv
9968: , (SELECT
9969: bsor.standard_operation_id,
9970: bre.resource_id,
9971: bre.inventory_item_id equipment_id
9972: FROM bom_std_op_resources bsor, bom_resources br, bom_resource_equipments bre
9973: WHERE bsor.resource_id = bre.resource_id
9985: AND wdt.organization_id = p_sign_on_org_id
9986: AND wdtv.wms_task_type_id = 1 -- restrict to picking tasks
9987: AND mol.header_id = moh.header_id
9988: AND moh.move_order_type = 5 -- only WIP jobs are considered : Bug 2666620 BackFlush Removed
9989: AND wdtv.user_task_type_id = e.standard_operation_id(+) -- join task to resource view, check if user defined task type match
9990: AND NVL(wdtv.cartonization_id, -999) = NVL(p_cartonization_id, NVL(wdtv.cartonization_id, -999))
9991: AND NVL(e.equipment_id, -999) = NVL(l_sign_on_equipment_id, NVL(e.equipment_id, -999))
9992: AND wdtv.organization_id = loc.organization_id
9993: AND wdtv.locator_id = loc.inventory_location_id
10019: ,v.role person_resource_id
10020: ,v.equipment machine_resource_id
10021: FROM --wms_dispatchable_tasks_v wdtv,
10022: (SELECT transaction_temp_id task_id
10023: , standard_operation_id user_task_type_id
10024: , wms_task_type wms_task_type_id
10025: , organization_id organization_id
10026: , subinventory_code ZONE
10027: , locator_id locator_id
10044: ) --Added for task planning WB. bug#2651318
10045: ) wdtv
10046: , -- inlined wms_dispatchable_tasks_v, bug 2648133
10047: --wms_person_resource_utt_v v,
10048: (SELECT utt_emp.standard_operation_id standard_operation_id
10049: , utt_emp.resource_id ROLE
10050: , utt_eqp.resource_id equipment
10051: , utt_emp.person_id emp_id
10052: , utt_eqp.inventory_item_id eqp_id
10052: , utt_eqp.inventory_item_id eqp_id
10053: , NULL eqp_srl /* removed for bug 2095237 */
10054: , utt_emp.effective_start_date
10055: , utt_emp.effective_end_date
10056: FROM (SELECT x_utt_res1.standard_operation_id standard_operation_id
10057: , x_utt_res1.resource_id resource_id
10058: , x_emp_r.person_id
10059: , x_emp_r.effective_start_date
10060: , x_emp_r.effective_end_date
10061: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
10062: WHERE x_utt_res1.resource_id = r1.resource_id
10063: AND r1.resource_type = 2
10064: AND x_utt_res1.resource_id = x_emp_r.resource_id) utt_emp
10065: , (SELECT x_utt_res2.standard_operation_id standard_operation_id
10066: , x_utt_res2.resource_id resource_id
10067: , x_eqp_r.inventory_item_id inventory_item_id
10068: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
10069: WHERE x_utt_res2.resource_id = r2.resource_id
10068: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
10069: WHERE x_utt_res2.resource_id = r2.resource_id
10070: AND r2.resource_type = 1
10071: AND x_utt_res2.resource_id = x_eqp_r.resource_id) utt_eqp
10072: WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)) v
10073: , -- inlined wms_person_resource_utt_v, bug 2648133
10074: mtl_item_locations_kfv loc
10075: , --changed to kfv bug#2742611
10076: mtl_secondary_inventories sub
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
10084: AND moh.move_order_type = 5 -- only WIP jobs are considered : Bug 2666620 BackFlush Removed
10085: AND wdtv.user_task_type_id = v.standard_operation_id -- join task to resource view, check if user defined task type match
10086: --AND Nvl(wdtv.zone, '@@@') = Nvl(p_sign_on_zone, Nvl(wdtv.zone, '@@@'))
10087: AND NVL(wdtv.cartonization_id, -999) = NVL(p_cartonization_id, NVL(wdtv.cartonization_id, -999))
10088: AND NVL(v.eqp_id, -999) = NVL(l_sign_on_equipment_id, NVL(v.eqp_id, -999))
10089: AND wdtv.organization_id = loc.organization_id(+) -- join task to loc, outer join for tasks do not have locator
10211: WHERE wdtv.organization_id = ' || p_sign_on_org_id ||'
10212: AND Nvl(wdtv.zone, ''@@@'') = Nvl('''|| p_sign_on_zone || ''', Nvl(wdtv.zone, ''@@@''))
10213: AND wdtv.user_task_type_id IN
10214: (
10215: SELECT standard_operation_id
10216: FROM wms_person_resource_utt_v v
10217: WHERE v.emp_id = ' || p_sign_on_emp_id ||'
10218: AND Nvl(v.eqp_srl, ''@@@'') = Nvl(''' || l_sign_on_equipment_srl || ''', Nvl(v.eqp_srl, ''@@@''))
10219: AND Nvl(v.eqp_id, -999) = Nvl(' || l_equipment_id_str ||', Nvl(v.eqp_id, -999))
10994: , '' lot_number
10995: , mmtt.wms_task_type wms_task_type_id
10996: , mmtt.task_priority task_priority
10997: , mmtt.operation_plan_id,
10998: mmtt.standard_operation_id,
10999: wot.effective_start_date,
11000: wot.effective_end_date,
11001: wot.person_resource_id,
11002: wot.machine_resource_id,
11227: END IF;
11228: END split_tasks;
11229:
11230: FUNCTION is_equipment_cap_exceeded(
11231: p_standard_operation_id IN NUMBER
11232: , p_item_id IN NUMBER
11233: , p_organization_id IN NUMBER
11234: , p_txn_qty IN NUMBER
11235: , p_txn_uom_code IN VARCHAR2
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
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)
11530: l_to_org_id NUMBER;
11531: l_to_sub VARCHAR2(30);
11532: l_to_loc_id NUMBER;
11533: l_wms_task_type NUMBER;
11534: l_standard_operation_id NUMBER;
11535: l_task_priority NUMBER;
11536: l_cost_group_id NUMBER;
11537: l_transaction_header_id NUMBER;
11538: l_container_item_id NUMBER;
11574: , mmtt.transfer_organization
11575: , mmtt.transfer_subinventory
11576: , mmtt.transfer_to_location
11577: , mmtt.wms_task_type
11578: , mmtt.standard_operation_id
11579: , MAX(mmtt.task_priority)
11580: , mmtt.cost_group_id
11581: , MAX(mmtt.transaction_header_id)
11582: , mmtt.container_item_id
11626: , mmtt.transfer_organization
11627: , mmtt.transfer_subinventory
11628: , mmtt.transfer_to_location
11629: , mmtt.wms_task_type
11630: , mmtt.standard_operation_id
11631: , mmtt.cost_group_id
11632: , mmtt.container_item_id
11633: , mmtt.cartonization_id
11634: , mmtt.operation_plan_id
11642: , mmtt.serial_allocated_flag
11643: -- Bug 4584538
11644: HAVING SUM(mmtt.transaction_quantity) <> MIN(mmtt.transaction_quantity) -- make sure one line will not get consolidated
11645: AND 'Y' <> is_equipment_cap_exceeded(
11646: mmtt.standard_operation_id
11647: , mmtt.inventory_item_id
11648: , mmtt.organization_id
11649: , SUM(mmtt.transaction_quantity)
11650: , mmtt.transaction_uom
11674: , mmtt.transfer_organization
11675: , ''
11676: , NULL
11677: , mmtt.wms_task_type
11678: , mmtt.standard_operation_id
11679: , MAX(mmtt.task_priority)
11680: , mmtt.cost_group_id
11681: , MAX(mmtt.transaction_header_id)
11682: , mmtt.container_item_id
11724: , mmtt.transaction_action_id
11725: , mmtt.transaction_source_type_id
11726: , mmtt.transfer_organization
11727: , mmtt.wms_task_type
11728: , mmtt.standard_operation_id
11729: , mmtt.cost_group_id
11730: , mmtt.container_item_id
11731: , mmtt.cartonization_id
11732: , mmtt.operation_plan_id
11739: , mmtt.serial_allocated_flag
11740: -- Bug 4584538
11741: HAVING SUM(mmtt.transaction_quantity) <> MIN(mmtt.transaction_quantity) -- make sure one line will not get consolidated
11742: AND 'Y' <> is_equipment_cap_exceeded(
11743: mmtt.standard_operation_id
11744: , mmtt.inventory_item_id
11745: , mmtt.organization_id
11746: , SUM(mmtt.transaction_quantity)
11747: , mmtt.transaction_uom
11794: , l_to_org_id
11795: , l_to_sub
11796: , l_to_loc_id
11797: , l_wms_task_type
11798: , l_standard_operation_id
11799: , l_task_priority
11800: , l_cost_group_id
11801: , l_transaction_header_id
11802: , l_container_item_id
11836: , l_to_org_id
11837: , l_to_sub
11838: , l_to_loc_id
11839: , l_wms_task_type
11840: , l_standard_operation_id
11841: , l_task_priority
11842: , l_cost_group_id
11843: , l_transaction_header_id
11844: , l_container_item_id
11922: , acct_period_id
11923: , cost_group_id
11924: -- , move_order_line_id keep same as patchset J
11925: , pick_slip_number
11926: , standard_operation_id
11927: , wms_task_type
11928: , task_priority
11929: , container_item_id
11930: , cartonization_id
11968: , l_acct_period_id
11969: , l_cost_group_id
11970: -- , l_move_order_line_id keep same as patchset J
11971: , l_pick_slip_number
11972: , l_standard_operation_id
11973: , l_wms_task_type
11974: , l_task_priority
11975: , l_container_item_id
11976: , l_cartonization_id
12083: l_to_org_id NUMBER;
12084: l_to_sub VARCHAR2(30);
12085: l_to_loc_id NUMBER;
12086: l_wms_task_type NUMBER;
12087: l_standard_operation_id NUMBER;
12088: l_task_priority NUMBER;
12089: l_cost_group_id NUMBER;
12090: l_transaction_header_id NUMBER;
12091: l_container_item_id NUMBER;
13052: , bom_resource_equipments res_equip
13053: , bom_resources res
13054: , bom_std_op_resources tt_x_res
13055: WHERE mmtt.transaction_temp_id = p_task_id -- the task in question
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)
13089: , bom_resource_equipments res_equip
13090: , bom_resources res
13091: , bom_std_op_resources tt_x_res
13092: WHERE mmtt.transaction_temp_id = p_task_id -- the task in question
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)
14089: , common_routing_seq_id
14090: , org_cost_group_id
14091: , cost_type_id
14092: , transaction_status
14093: , standard_operation_id
14094: , task_priority
14095: , wms_task_type
14096: , parent_line_id
14097: , lpn_id
14322: , l_mmtt_rec.common_routing_seq_id
14323: , l_mmtt_rec.org_cost_group_id
14324: , l_mmtt_rec.cost_type_id
14325: , l_mmtt_rec.transaction_status
14326: , l_mmtt_rec.standard_operation_id
14327: , l_mmtt_rec.task_priority
14328: , l_mmtt_rec.wms_task_type
14329: , l_mmtt_rec.parent_line_id
14330: , l_mmtt_rec.lpn_id
14561: , common_routing_seq_id
14562: , org_cost_group_id
14563: , cost_type_id
14564: , transaction_status
14565: , standard_operation_id
14566: , task_priority
14567: , wms_task_type
14568: , parent_line_id
14569: , lpn_id
14794: , l_wct_rec.common_routing_seq_id
14795: , l_wct_rec.org_cost_group_id
14796: , l_wct_rec.cost_type_id
14797: , l_wct_rec.transaction_status
14798: , l_wct_rec.standard_operation_id
14799: , l_wct_rec.task_priority
14800: , l_wct_rec.wms_task_type
14801: , l_wct_rec.parent_line_id
14802: , l_wct_rec.lpn_id