[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
250: l_lot_number VARCHAR2(80);
251: l_revision VARCHAR2(3);
252: l_operation_plan_id NUMBER;
253: l_move_order_line_id NUMBER;
254: l_standard_operation_id NUMBER;
255: l_effective_start_date DATE;
256: l_effective_end_date DATE;
257: l_person_resource_id NUMBER;
258: l_machine_resource_id NUMBER;
352: mtl_txn_request_lines mol,
353: mtl_secondary_inventories sub,
354: mtl_item_locations loc,
355: (SELECT
356: bsor.standard_operation_id,
357: bre.resource_id,
358: bre.inventory_item_id equipment_id
359: FROM bom_std_op_resources bsor, bom_resources br, bom_resource_equipments bre
360: WHERE bsor.resource_id = bre.resource_id
365: AND wdt.person_id = p_sign_on_emp_id
366: AND wdt.organization_id = p_sign_on_org_id
367: AND NVL(qt.cartonization_id, -999) = NVL(l_cartonization_id, NVL(qt.cartonization_id, -999))
368: AND NVL(e.equipment_id, -999) = NVL(l_sign_on_equipment_id, NVL(e.equipment_id, -999))
369: AND wdt.user_task_type = e.standard_operation_id(+)
370: AND qt.move_order_line_id = mol.line_id(+)
371: AND qt.subinventory_code = p_sign_on_zone
372: AND qt.organization_id = sub.organization_id
373: AND qt.subinventory_code = sub.secondary_inventory_name
393: FROM
394: -- inlined wms_dispatchable_tasks_v, bug 2648133
395: (SELECT
396: transaction_temp_id task_id,
397: standard_operation_id user_task_type_id,
398: wms_task_type wms_task_type_id,
399: organization_id,
400: subinventory_code zone,
401: locator_id,
440:
441: UNION ALL
442: SELECT
443: MIN(mcce.cycle_count_entry_id) task_id,
444: MIN(mcce.standard_operation_id) user_task_type_id,
445: 3 wms_task_type_id,
446: mcce.organization_id,
447: mcce.subinventory zone,
448: mcce.locator_id,
476: AND NVL(mcch.disable_date,sysdate+1)> sysdate
477: GROUP BY mcce.cycle_count_header_id, mcce.organization_id, mcce.subinventory, mcce.locator_id, mcce.inventory_item_id, mcce.revision) wdtv,
478: -- inlined wms_person_resource_utt_v, bug 2648133
479: (SELECT
480: utt_emp.standard_operation_id standard_operation_id,
481: utt_emp.resource_id role,
482: utt_eqp.resource_id equipment,
483: utt_emp.person_id emp_id,
484: utt_eqp.inventory_item_id eqp_id,
484: utt_eqp.inventory_item_id eqp_id,
485: NULL eqp_srl /* removed for bug 2095237 */
486: FROM
487: (SELECT
488: x_utt_res1.standard_operation_id standard_operation_id,
489: x_utt_res1.resource_id resource_id,
490: x_emp_r.person_id
491: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
492: WHERE x_utt_res1.resource_id = r1.resource_id
492: WHERE x_utt_res1.resource_id = r1.resource_id
493: AND r1.resource_type = 2
494: AND x_utt_res1.resource_id = x_emp_r.resource_id) utt_emp,
495: (SELECT
496: x_utt_res2.standard_operation_id standard_operation_id,
497: x_utt_res2.resource_id,
498: x_eqp_r.inventory_item_id
499: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
500: WHERE x_utt_res2.resource_id = r2.resource_id
499: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
500: WHERE x_utt_res2.resource_id = r2.resource_id
501: AND r2.resource_type = 1
502: AND x_utt_res2.resource_id = x_eqp_r.resource_id) utt_eqp
503: WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)) v,
504: mtl_item_locations loc,
505: mtl_secondary_inventories sub,
506: mtl_txn_request_lines mol,
507: mtl_txn_request_headers moh
506: mtl_txn_request_lines mol,
507: mtl_txn_request_headers moh
508: WHERE v.emp_id = p_sign_on_emp_id -- restrict to sign on employee
509: AND wdtv.organization_id = p_sign_on_org_id
510: AND wdtv.user_task_type_id = v.standard_operation_id -- join task to resource view, check if user defined task type match
511: AND wdtv.zone = p_sign_on_zone -- removed NVL, bug 2648133
512: AND NVL(wdtv.cartonization_id, -999) = NVL(l_cartonization_id, NVL(wdtv.cartonization_id, -999))
513: AND NVL(v.eqp_id, -999) = NVL(l_sign_on_equipment_id, NVL(v.eqp_id, -999))
514: AND wdtv.organization_id = loc.organization_id(+) -- join task to loc, outer join for tasks do not have locator
655: mtl_txn_request_lines mol,
656: mtl_secondary_inventories sub,
657: mtl_item_locations loc,
658: (SELECT
659: bsor.standard_operation_id,
660: bre.resource_id,
661: bre.inventory_item_id equipment_id
662: FROM bom_std_op_resources bsor, bom_resources br, bom_resource_equipments bre
663: WHERE bsor.resource_id = bre.resource_id
668: AND wdt.person_id = p_sign_on_emp_id
669: AND wdt.organization_id = p_sign_on_org_id
670: AND NVL(qt.cartonization_id, -999) = NVL(l_cartonization_id, NVL(qt.cartonization_id, -999))
671: AND NVL(e.equipment_id, -999) = NVL(l_q_sign_on_equipment_id, NVL(e.equipment_id, -999))--bug 6326482
672: AND wdt.user_task_type = e.standard_operation_id(+)
673: AND qt.move_order_line_id = mol.line_id(+)
674: AND qt.subinventory_code = p_sign_on_zone
675: AND qt.organization_id = sub.organization_id
676: AND qt.subinventory_code = sub.secondary_inventory_name
733: mtl_txn_request_lines mol,
734: mtl_secondary_inventories sub,
735: mtl_item_locations loc,
736: (SELECT
737: bsor.standard_operation_id,
738: bre.resource_id,
739: bre.inventory_item_id equipment_id
740: FROM bom_std_op_resources bsor, bom_resources br, bom_resource_equipments bre
741: WHERE bsor.resource_id = bre.resource_id
751: AND NVL(e.equipment_id, -999) = NVL(l_q_sign_on_equipment_id, NVL(e.equipment_id, -999)))
752: or
753: (qt.task_status = 1
754: AND NVL(e.equipment_id, -999) = NVL(l_sign_on_equipment_id, NVL(e.equipment_id, -999))))--bug 6326482
755: AND wdt.user_task_type = e.standard_operation_id(+)
756: AND qt.move_order_line_id = mol.line_id(+)
757: AND qt.subinventory_code = p_sign_on_zone
758: AND qt.organization_id = sub.organization_id
759: AND qt.subinventory_code = sub.secondary_inventory_name
779: FROM
780: -- inlined wms_dispatchable_tasks_v, bug 2648133
781: (SELECT
782: transaction_temp_id task_id,
783: standard_operation_id user_task_type_id,
784: wms_task_type wms_task_type_id,
785: organization_id,
786: subinventory_code zone,
787: locator_id,
825: )
826: ) wdtv,
827: -- inlined wms_person_resource_utt_v, bug 2648133
828: (SELECT
829: utt_emp.standard_operation_id standard_operation_id,
830: utt_emp.resource_id role,
831: utt_eqp.resource_id equipment,
832: utt_emp.person_id emp_id,
833: utt_eqp.inventory_item_id eqp_id,
833: utt_eqp.inventory_item_id eqp_id,
834: NULL eqp_srl /* removed for bug 2095237 */
835: FROM
836: (SELECT
837: x_utt_res1.standard_operation_id standard_operation_id,
838: x_utt_res1.resource_id resource_id,
839: x_emp_r.person_id
840: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
841: WHERE x_utt_res1.resource_id = r1.resource_id
841: WHERE x_utt_res1.resource_id = r1.resource_id
842: AND r1.resource_type = 2
843: AND x_utt_res1.resource_id = x_emp_r.resource_id) utt_emp,
844: (SELECT
845: x_utt_res2.standard_operation_id standard_operation_id,
846: x_utt_res2.resource_id,
847: x_eqp_r.inventory_item_id
848: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
849: WHERE x_utt_res2.resource_id = r2.resource_id
848: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
849: WHERE x_utt_res2.resource_id = r2.resource_id
850: AND r2.resource_type = 1
851: AND x_utt_res2.resource_id = x_eqp_r.resource_id) utt_eqp
852: WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)) v,
853: mtl_item_locations loc,
854: mtl_secondary_inventories sub,
855: mtl_txn_request_lines mol,
856: mtl_txn_request_headers moh
855: mtl_txn_request_lines mol,
856: mtl_txn_request_headers moh
857: WHERE v.emp_id = p_sign_on_emp_id -- restrict to sign on employee
858: AND wdtv.organization_id = p_sign_on_org_id
859: AND wdtv.user_task_type_id = v.standard_operation_id -- join task to resource view, check if user defined task type match
860: AND wdtv.zone = p_sign_on_zone -- removed NVL, bug 2648133
861: AND NVL(wdtv.cartonization_id, -999) = NVL(l_cartonization_id, NVL(wdtv.cartonization_id, -999))
862: AND NVL(v.eqp_id, -999) = NVL(l_sign_on_equipment_id, NVL(v.eqp_id, -999))
863: AND wdtv.organization_id = loc.organization_id(+) -- join task to loc, outer join for tasks do not have locator
996: GROUP BY mcce.cycle_count_header_id, mcce.organization_id, mcce.subinventory, mcce.locator_id, mcce.inventory_item_id, mcce.revision) qt,
997: mtl_secondary_inventories sub,
998: mtl_item_locations loc,
999: (SELECT
1000: bsor.standard_operation_id,
1001: bre.resource_id,
1002: bre.inventory_item_id equipment_id
1003: FROM bom_std_op_resources bsor, bom_resources br, bom_resource_equipments bre
1004: WHERE bsor.resource_id = bre.resource_id
1009: AND wdt.person_id = p_sign_on_emp_id
1010: AND wdt.organization_id = p_sign_on_org_id
1011: AND NVL(qt.cartonization_id, -999) = NVL(l_cartonization_id, NVL(qt.cartonization_id, -999))
1012: AND NVL(e.equipment_id, -999) = NVL(l_sign_on_equipment_id, NVL(e.equipment_id, -999))
1013: AND wdt.user_task_type = e.standard_operation_id(+)
1014: AND qt.organization_id = sub.organization_id
1015: AND qt.subinventory_code = p_sign_on_zone
1016: AND qt.subinventory_code = sub.secondary_inventory_name
1017: AND qt.organization_id = loc.organization_id
1036: FROM
1037: -- inlined wms_dispatchable_tasks_v, bug 2648133
1038: (SELECT
1039: MIN(mcce.cycle_count_entry_id) task_id,
1040: MIN(mcce.standard_operation_id) user_task_type_id,
1041: 3 wms_task_type_id,
1042: mcce.organization_id,
1043: mcce.subinventory zone,
1044: mcce.locator_id,
1071: AND NVL(mcch.disable_date,sysdate+1)> sysdate
1072: GROUP BY mcce.cycle_count_header_id, mcce.organization_id, mcce.subinventory, mcce.locator_id, mcce.inventory_item_id, mcce.revision) wdtv,
1073: -- inlined wms_person_resource_utt_v, bug 2648133
1074: (SELECT
1075: utt_emp.standard_operation_id standard_operation_id,
1076: utt_emp.resource_id role,
1077: utt_eqp.resource_id equipment,
1078: utt_emp.person_id emp_id,
1079: utt_eqp.inventory_item_id eqp_id,
1079: utt_eqp.inventory_item_id eqp_id,
1080: NULL eqp_srl /* removed for bug 2095237 */
1081: FROM
1082: (SELECT
1083: x_utt_res1.standard_operation_id standard_operation_id,
1084: x_utt_res1.resource_id resource_id,
1085: x_emp_r.person_id
1086: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
1087: WHERE x_utt_res1.resource_id = r1.resource_id
1087: WHERE x_utt_res1.resource_id = r1.resource_id
1088: AND r1.resource_type = 2
1089: AND x_utt_res1.resource_id = x_emp_r.resource_id) utt_emp,
1090: (SELECT
1091: x_utt_res2.standard_operation_id standard_operation_id,
1092: x_utt_res2.resource_id,
1093: x_eqp_r.inventory_item_id
1094: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
1095: WHERE x_utt_res2.resource_id = r2.resource_id
1094: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
1095: WHERE x_utt_res2.resource_id = r2.resource_id
1096: AND r2.resource_type = 1
1097: AND x_utt_res2.resource_id = x_eqp_r.resource_id) utt_eqp
1098: WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)) v,
1099: mtl_item_locations loc,
1100: mtl_secondary_inventories sub
1101: WHERE v.emp_id = p_sign_on_emp_id -- restrict to sign on employee
1102: AND wdtv.organization_id = p_sign_on_org_id
1099: mtl_item_locations loc,
1100: mtl_secondary_inventories sub
1101: WHERE v.emp_id = p_sign_on_emp_id -- restrict to sign on employee
1102: AND wdtv.organization_id = p_sign_on_org_id
1103: AND wdtv.user_task_type_id = v.standard_operation_id -- join task to resource view, check if user defined task type match
1104: AND wdtv.zone = p_sign_on_zone -- removed NVL, bug 2648133
1105: AND NVL(wdtv.cartonization_id, -999) = NVL(l_cartonization_id, NVL(wdtv.cartonization_id, -999))
1106: AND NVL(v.eqp_id, -999) = NVL(l_sign_on_equipment_id, NVL(v.eqp_id, -999))
1107: AND wdtv.organization_id = loc.organization_id(+) -- join task to loc, outer join for tasks do not have locator
1260: mtl_txn_request_lines mol,
1261: mtl_secondary_inventories sub,
1262: mtl_item_locations loc,
1263: (SELECT
1264: bsor.standard_operation_id,
1265: bre.resource_id,
1266: bre.inventory_item_id equipment_id
1267: FROM bom_std_op_resources bsor, bom_resources br, bom_resource_equipments bre
1268: WHERE bsor.resource_id = bre.resource_id
1273: AND wdt.person_id = p_sign_on_emp_id
1274: AND wdt.organization_id = p_sign_on_org_id
1275: AND NVL(qt.cartonization_id, -999) = NVL(l_cartonization_id, NVL(qt.cartonization_id, -999))
1276: AND NVL(e.equipment_id, -999) = NVL(l_sign_on_equipment_id, NVL(e.equipment_id, -999))
1277: AND wdt.user_task_type = e.standard_operation_id(+)
1278: AND qt.move_order_line_id = mol.line_id(+)
1279: AND qt.organization_id = sub.organization_id
1280: AND qt.subinventory_code = sub.secondary_inventory_name
1281: AND qt.organization_id = loc.organization_id
1300: FROM
1301: -- inlined wms_dispatchable_tasks_v, bug 2648133
1302: (SELECT
1303: transaction_temp_id task_id,
1304: standard_operation_id user_task_type_id,
1305: wms_task_type wms_task_type_id,
1306: organization_id,
1307: subinventory_code zone,
1308: locator_id,
1347:
1348: UNION ALL
1349: SELECT
1350: MIN(mcce.cycle_count_entry_id) task_id,
1351: MIN(mcce.standard_operation_id) user_task_type_id,
1352: 3 wms_task_type_id,
1353: mcce.organization_id,
1354: mcce.subinventory zone,
1355: mcce.locator_id,
1383:
1384: GROUP BY mcce.cycle_count_header_id, mcce.organization_id, mcce.subinventory, mcce.locator_id, mcce.inventory_item_id, mcce.revision) wdtv,
1385: -- inlined wms_person_resource_utt_v, bug 2648133
1386: (SELECT
1387: utt_emp.standard_operation_id standard_operation_id,
1388: utt_emp.resource_id role,
1389: utt_eqp.resource_id equipment,
1390: utt_emp.person_id emp_id,
1391: utt_eqp.inventory_item_id eqp_id,
1391: utt_eqp.inventory_item_id eqp_id,
1392: NULL eqp_srl /* removed for bug 2095237 */
1393: FROM
1394: (SELECT
1395: x_utt_res1.standard_operation_id standard_operation_id,
1396: x_utt_res1.resource_id resource_id,
1397: x_emp_r.person_id
1398: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
1399: WHERE x_utt_res1.resource_id = r1.resource_id
1399: WHERE x_utt_res1.resource_id = r1.resource_id
1400: AND r1.resource_type = 2
1401: AND x_utt_res1.resource_id = x_emp_r.resource_id) utt_emp,
1402: (SELECT
1403: x_utt_res2.standard_operation_id standard_operation_id,
1404: x_utt_res2.resource_id,
1405: x_eqp_r.inventory_item_id
1406: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
1407: WHERE x_utt_res2.resource_id = r2.resource_id
1406: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
1407: WHERE x_utt_res2.resource_id = r2.resource_id
1408: AND r2.resource_type = 1
1409: AND x_utt_res2.resource_id = x_eqp_r.resource_id) utt_eqp
1410: WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)) v,
1411: mtl_item_locations loc,
1412: mtl_secondary_inventories sub,
1413: mtl_txn_request_lines mol,
1414: mtl_txn_request_headers moh
1413: mtl_txn_request_lines mol,
1414: mtl_txn_request_headers moh
1415: WHERE v.emp_id = p_sign_on_emp_id -- restrict to sign on employee
1416: AND wdtv.organization_id = p_sign_on_org_id
1417: AND wdtv.user_task_type_id = v.standard_operation_id -- join task to resource view, check if user defined task type match
1418: AND NVL(wdtv.cartonization_id, -999) = NVL(l_cartonization_id, NVL(wdtv.cartonization_id, -999))
1419: AND NVL(v.eqp_id, -999) = NVL(l_sign_on_equipment_id, NVL(v.eqp_id, -999))
1420: AND wdtv.organization_id = loc.organization_id(+) -- join task to loc, outer join for tasks do not have locator
1421: AND wdtv.locator_id = loc.inventory_location_id(+)
1562: mtl_txn_request_lines mol,
1563: mtl_secondary_inventories sub,
1564: mtl_item_locations loc,
1565: (SELECT
1566: bsor.standard_operation_id,
1567: bre.resource_id,
1568: bre.inventory_item_id equipment_id
1569: FROM bom_std_op_resources bsor, bom_resources br, bom_resource_equipments bre
1570: WHERE bsor.resource_id = bre.resource_id
1575: AND wdt.person_id = p_sign_on_emp_id
1576: AND wdt.organization_id = p_sign_on_org_id
1577: AND NVL(qt.cartonization_id, -999) = NVL(l_cartonization_id, NVL(qt.cartonization_id, -999))
1578: AND NVL(e.equipment_id, -999) = NVL(l_q_sign_on_equipment_id, NVL(e.equipment_id, -999))
1579: AND wdt.user_task_type = e.standard_operation_id(+)
1580: AND qt.move_order_line_id = mol.line_id(+)
1581: AND qt.organization_id = sub.organization_id
1582: AND qt.subinventory_code = sub.secondary_inventory_name
1583: AND qt.organization_id = loc.organization_id
1639: mtl_txn_request_lines mol,
1640: mtl_secondary_inventories sub,
1641: mtl_item_locations loc,
1642: (SELECT
1643: bsor.standard_operation_id,
1644: bre.resource_id,
1645: bre.inventory_item_id equipment_id
1646: FROM bom_std_op_resources bsor, bom_resources br, bom_resource_equipments bre
1647: WHERE bsor.resource_id = bre.resource_id
1657: AND NVL(e.equipment_id, -999) = NVL(l_q_sign_on_equipment_id, NVL(e.equipment_id, -999)))
1658: or
1659: (qt.task_status = 1
1660: AND NVL(e.equipment_id, -999) = NVL(l_sign_on_equipment_id, NVL(e.equipment_id, -999))))--bug 6326482
1661: AND wdt.user_task_type = e.standard_operation_id(+)
1662: AND qt.move_order_line_id = mol.line_id(+)
1663: AND qt.organization_id = sub.organization_id
1664: AND qt.subinventory_code = sub.secondary_inventory_name
1665: AND qt.organization_id = loc.organization_id
1684: FROM
1685: -- inlined wms_dispatchable_tasks_v, bug 2648133
1686: (SELECT
1687: transaction_temp_id task_id,
1688: standard_operation_id user_task_type_id,
1689: wms_task_type wms_task_type_id,
1690: organization_id,
1691: subinventory_code zone,
1692: locator_id,
1730: )
1731: ) wdtv,
1732: -- inlined wms_person_resource_utt_v, bug 2648133
1733: (SELECT
1734: utt_emp.standard_operation_id standard_operation_id,
1735: utt_emp.resource_id role,
1736: utt_eqp.resource_id equipment,
1737: utt_emp.person_id emp_id,
1738: utt_eqp.inventory_item_id eqp_id,
1738: utt_eqp.inventory_item_id eqp_id,
1739: NULL eqp_srl /* removed for bug 2095237 */
1740: FROM
1741: (SELECT
1742: x_utt_res1.standard_operation_id standard_operation_id,
1743: x_utt_res1.resource_id resource_id,
1744: x_emp_r.person_id
1745: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
1746: WHERE x_utt_res1.resource_id = r1.resource_id
1746: WHERE x_utt_res1.resource_id = r1.resource_id
1747: AND r1.resource_type = 2
1748: AND x_utt_res1.resource_id = x_emp_r.resource_id) utt_emp,
1749: (SELECT
1750: x_utt_res2.standard_operation_id standard_operation_id,
1751: x_utt_res2.resource_id,
1752: x_eqp_r.inventory_item_id
1753: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
1754: WHERE x_utt_res2.resource_id = r2.resource_id
1753: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
1754: WHERE x_utt_res2.resource_id = r2.resource_id
1755: AND r2.resource_type = 1
1756: AND x_utt_res2.resource_id = x_eqp_r.resource_id) utt_eqp
1757: WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)) v,
1758: mtl_item_locations loc,
1759: mtl_secondary_inventories sub,
1760: mtl_txn_request_lines mol,
1761: mtl_txn_request_headers moh
1760: mtl_txn_request_lines mol,
1761: mtl_txn_request_headers moh
1762: WHERE v.emp_id = p_sign_on_emp_id -- restrict to sign on employee
1763: AND wdtv.organization_id = p_sign_on_org_id
1764: AND wdtv.user_task_type_id = v.standard_operation_id -- join task to resource view, check if user defined task type match
1765: AND NVL(wdtv.cartonization_id, -999) = NVL(l_cartonization_id, NVL(wdtv.cartonization_id, -999))
1766: AND NVL(v.eqp_id, -999) = NVL(l_sign_on_equipment_id, NVL(v.eqp_id, -999))
1767: AND wdtv.organization_id = loc.organization_id(+) -- join task to loc, outer join for tasks do not have locator
1768: AND wdtv.locator_id = loc.inventory_location_id(+)
1893: GROUP BY mcce.cycle_count_header_id, mcce.organization_id, mcce.subinventory, mcce.locator_id, mcce.inventory_item_id, mcce.revision) qt,
1894: mtl_secondary_inventories sub,
1895: mtl_item_locations loc,
1896: (SELECT
1897: bsor.standard_operation_id,
1898: bre.resource_id,
1899: bre.inventory_item_id equipment_id
1900: FROM bom_std_op_resources bsor, bom_resources br, bom_resource_equipments bre
1901: WHERE bsor.resource_id = bre.resource_id
1906: AND wdt.person_id = p_sign_on_emp_id
1907: AND wdt.organization_id = p_sign_on_org_id
1908: AND NVL(qt.cartonization_id, -999) = NVL(l_cartonization_id, NVL(qt.cartonization_id, -999))
1909: AND NVL(e.equipment_id, -999) = NVL(l_sign_on_equipment_id, NVL(e.equipment_id, -999))
1910: AND wdt.user_task_type = e.standard_operation_id(+)
1911: AND qt.organization_id = sub.organization_id
1912: AND qt.subinventory_code = sub.secondary_inventory_name
1913: AND qt.organization_id = loc.organization_id
1914: AND qt.locator_id = loc.inventory_location_id
1932: FROM
1933: -- inlined wms_dispatchable_tasks_v, bug 2648133
1934: (SELECT
1935: MIN(mcce.cycle_count_entry_id) task_id,
1936: MIN(mcce.standard_operation_id) user_task_type_id,
1937: 3 wms_task_type_id,
1938: mcce.organization_id,
1939: mcce.subinventory zone,
1940: mcce.locator_id,
1968: AND NVL(mcch.disable_date,sysdate+1)> sysdate
1969: GROUP BY mcce.cycle_count_header_id, mcce.organization_id, mcce.subinventory, mcce.locator_id, mcce.inventory_item_id, mcce.revision) wdtv,
1970: -- inlined wms_person_resource_utt_v, bug 2648133
1971: (SELECT
1972: utt_emp.standard_operation_id standard_operation_id,
1973: utt_emp.resource_id role,
1974: utt_eqp.resource_id equipment,
1975: utt_emp.person_id emp_id,
1976: utt_eqp.inventory_item_id eqp_id,
1976: utt_eqp.inventory_item_id eqp_id,
1977: NULL eqp_srl /* removed for bug 2095237 */
1978: FROM
1979: (SELECT
1980: x_utt_res1.standard_operation_id standard_operation_id,
1981: x_utt_res1.resource_id resource_id,
1982: x_emp_r.person_id
1983: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
1984: WHERE x_utt_res1.resource_id = r1.resource_id
1984: WHERE x_utt_res1.resource_id = r1.resource_id
1985: AND r1.resource_type = 2
1986: AND x_utt_res1.resource_id = x_emp_r.resource_id) utt_emp,
1987: (SELECT
1988: x_utt_res2.standard_operation_id standard_operation_id,
1989: x_utt_res2.resource_id,
1990: x_eqp_r.inventory_item_id
1991: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
1992: WHERE x_utt_res2.resource_id = r2.resource_id
1991: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
1992: WHERE x_utt_res2.resource_id = r2.resource_id
1993: AND r2.resource_type = 1
1994: AND x_utt_res2.resource_id = x_eqp_r.resource_id) utt_eqp
1995: WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)) v,
1996: mtl_item_locations loc,
1997: mtl_secondary_inventories sub
1998: WHERE v.emp_id = p_sign_on_emp_id -- restrict to sign on employee
1999: AND wdtv.organization_id = p_sign_on_org_id
1996: mtl_item_locations loc,
1997: mtl_secondary_inventories sub
1998: WHERE v.emp_id = p_sign_on_emp_id -- restrict to sign on employee
1999: AND wdtv.organization_id = p_sign_on_org_id
2000: AND wdtv.user_task_type_id = v.standard_operation_id -- join task to resource view, check if user defined task type match
2001: AND NVL(wdtv.cartonization_id, -999) = NVL(l_cartonization_id, NVL(wdtv.cartonization_id, -999))
2002: AND NVL(v.eqp_id, -999) = NVL(l_sign_on_equipment_id, NVL(v.eqp_id, -999))
2003: AND wdtv.organization_id = loc.organization_id(+) -- join task to loc, outer join for tasks do not have locator
2004: AND wdtv.locator_id = loc.inventory_location_id(+)
2088: qt.transaction_uom,
2089: qt.transaction_quantity,
2090: qt.lot_number,
2091: qt.operation_plan_id,
2092: qt.standard_operation_id,
2093: wdt.effective_start_date,
2094: wdt.effective_end_date,
2095: wdt.person_resource_id,
2096: wdt.machine_resource_id,
2121: transaction_uom,
2122: transaction_quantity,
2123: lot_number,
2124: operation_plan_id,
2125: standard_operation_id,
2126: move_order_line_id,
2127: transaction_type_id,
2128: transaction_action_id,
2129: transaction_source_id,
2164: To_char(NULL) transaction_uom,
2165: To_number(NULL) transaction_quantity,
2166: MIN(mcce.lot_number) lot_number,
2167: To_number(NULL) operation_plan_id,
2168: MIN(mcce.standard_operation_id) standard_operation_id,
2169: To_number(NULL) move_order_line_id,
2170: 4 transaction_type_id,
2171: 4 transaction_action_id,
2172: mcce.cycle_count_header_id transaction_source_id,
2188: mtl_txn_request_lines mol,
2189: mtl_secondary_inventories sub,
2190: mtl_item_locations loc,
2191: (SELECT
2192: bsor.standard_operation_id,
2193: bre.resource_id,
2194: bre.inventory_item_id equipment_id
2195: FROM bom_std_op_resources bsor, bom_resources br, bom_resource_equipments bre
2196: WHERE bsor.resource_id = bre.resource_id
2214: OR (p_task_method = 'CLUSTERPICKBYLABEL' AND
2215: qt.cartonization_id IN (SELECT * FROM TABLE(wms_picking_pkg.list_cartonization_id))))
2216: -- Bug: 7254397
2217: AND NVL(e.equipment_id, -999) = NVL(l_sign_on_equipment_id, NVL(e.equipment_id, -999))
2218: AND wdt.user_task_type = e.standard_operation_id(+)
2219: AND qt.move_order_line_id = mol.line_id(+)
2220: AND NVL(qt.subinventory_code, '@@@') = NVL(p_sign_on_zone, NVL(qt.subinventory_code, '@@@'))
2221: AND qt.organization_id = sub.organization_id
2222: AND qt.subinventory_code = sub.secondary_inventory_name
2231: wdtv.transaction_uom,
2232: wdtv.transaction_quantity,
2233: wdtv.lot_number,
2234: wdtv.operation_plan_id,
2235: wdtv.user_task_type_id standard_operation_id,
2236: v.effective_start_date,
2237: v.effective_end_date,
2238: v.role person_resource_id,
2239: v.equipment machine_resource_id,
2254: FROM
2255: -- inlined wms_dispatchable_tasks_v, bug 2648133
2256: (SELECT
2257: transaction_temp_id task_id,
2258: standard_operation_id user_task_type_id,
2259: wms_task_type wms_task_type_id,
2260: organization_id,
2261: subinventory_code,
2262: locator_id,
2302:
2303: UNION ALL
2304: SELECT
2305: MIN(mcce.cycle_count_entry_id) task_id,
2306: MIN(mcce.standard_operation_id) user_task_type_id,
2307: 3 wms_task_type_id,
2308: mcce.organization_id,
2309: mcce.subinventory subinventory_code,
2310: mcce.locator_id,
2338: AND NVL(mcch.disable_date,sysdate+1)> sysdate
2339: GROUP BY mcce.cycle_count_header_id, mcce.organization_id, mcce.subinventory, mcce.locator_id, mcce.inventory_item_id, mcce.revision) wdtv,
2340: -- inlined wms_person_resource_utt_v, bug 2648133
2341: (SELECT
2342: utt_emp.standard_operation_id standard_operation_id,
2343: utt_emp.resource_id role,
2344: utt_eqp.resource_id equipment,
2345: utt_emp.person_id emp_id,
2346: utt_emp.effective_start_date,
2348: utt_eqp.inventory_item_id eqp_id,
2349: NULL eqp_srl /* removed for bug 2095237 */
2350: FROM
2351: (SELECT
2352: x_utt_res1.standard_operation_id standard_operation_id,
2353: x_utt_res1.resource_id resource_id,
2354: x_emp_r.person_id,
2355: x_emp_r.effective_start_date,
2356: x_emp_r.effective_end_date
2358: WHERE x_utt_res1.resource_id = r1.resource_id
2359: AND r1.resource_type = 2
2360: AND x_utt_res1.resource_id = x_emp_r.resource_id) utt_emp,
2361: (SELECT
2362: x_utt_res2.standard_operation_id standard_operation_id,
2363: x_utt_res2.resource_id,
2364: x_eqp_r.inventory_item_id
2365: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
2366: WHERE x_utt_res2.resource_id = r2.resource_id
2365: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
2366: WHERE x_utt_res2.resource_id = r2.resource_id
2367: AND r2.resource_type = 1
2368: AND x_utt_res2.resource_id = x_eqp_r.resource_id) utt_eqp
2369: WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)) v,
2370: mtl_item_locations loc,
2371: mtl_secondary_inventories sub,
2372: mtl_txn_request_lines mol,
2373: mtl_txn_request_headers moh
2385: OR (p_task_method = 'PICKBYLABEL' AND wdtv.cartonization_id = l_cartonization_id)
2386: OR (p_task_method = 'CLUSTERPICKBYLABEL' AND
2387: wdtv.cartonization_id IN (SELECT * FROM TABLE(wms_picking_pkg.list_cartonization_id))))
2388: AND wdtv.organization_id = p_sign_on_org_id
2389: AND wdtv.user_task_type_id = v.standard_operation_id -- join task to resource view, check if user defined task type match
2390: 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))
2391: AND NVL(v.eqp_id, -999) = NVL(l_sign_on_equipment_id, NVL(v.eqp_id, -999))
2392: AND wdtv.organization_id = loc.organization_id(+) -- join task to loc, outer join for tasks do not have locator
2393: AND wdtv.locator_id = loc.inventory_location_id(+)
2468: qt.transaction_uom,
2469: qt.transaction_quantity,
2470: qt.lot_number,
2471: qt.operation_plan_id,
2472: qt.standard_operation_id,
2473: wdt.effective_start_date,
2474: wdt.effective_end_date,
2475: wdt.person_resource_id,
2476: wdt.machine_resource_id,
2501: transaction_uom,
2502: transaction_quantity,
2503: lot_number,
2504: operation_plan_id,
2505: standard_operation_id,
2506: move_order_line_id,
2507: transaction_type_id,
2508: transaction_action_id,
2509: transaction_source_id,
2533: mtl_txn_request_lines mol,
2534: mtl_secondary_inventories sub,
2535: mtl_item_locations loc,
2536: (SELECT
2537: bsor.standard_operation_id,
2538: bre.resource_id,
2539: bre.inventory_item_id equipment_id
2540: FROM bom_std_op_resources bsor, bom_resources br, bom_resource_equipments bre
2541: WHERE bsor.resource_id = bre.resource_id
2558: OR (p_task_method = 'PICKBYLABEL' AND qt.cartonization_id = l_cartonization_id)
2559: OR (p_task_method = 'CLUSTERPICKBYLABEL' AND
2560: qt.cartonization_id IN (SELECT * FROM TABLE(wms_picking_pkg.list_cartonization_id))))
2561: AND NVL(e.equipment_id, -999) = NVL(l_q_sign_on_equipment_id, NVL(e.equipment_id, -999))--bug 6326482
2562: AND wdt.user_task_type = e.standard_operation_id(+)
2563: AND qt.move_order_line_id = mol.line_id(+)
2564: AND NVL(qt.subinventory_code, '@@@') = NVL(p_sign_on_zone, NVL(qt.subinventory_code, '@@@'))
2565: AND qt.organization_id = sub.organization_id
2566: AND qt.subinventory_code = sub.secondary_inventory_name
2576: qt.transaction_uom,
2577: qt.transaction_quantity,
2578: qt.lot_number,
2579: qt.operation_plan_id,
2580: qt.standard_operation_id,
2581: wdt.effective_start_date,
2582: wdt.effective_end_date,
2583: wdt.person_resource_id,
2584: wdt.machine_resource_id,
2610: transaction_uom,
2611: transaction_quantity,
2612: lot_number,
2613: operation_plan_id,
2614: standard_operation_id,
2615: move_order_line_id,
2616: transaction_type_id,
2617: transaction_action_id,
2618: transaction_source_id,
2642: mtl_txn_request_lines mol,
2643: mtl_secondary_inventories sub,
2644: mtl_item_locations loc,
2645: (SELECT
2646: bsor.standard_operation_id,
2647: bre.resource_id,
2648: bre.inventory_item_id equipment_id
2649: FROM bom_std_op_resources bsor, bom_resources br, bom_resource_equipments bre
2650: WHERE bsor.resource_id = bre.resource_id
2672: AND NVL(e.equipment_id, -999) = NVL(l_q_sign_on_equipment_id, NVL(e.equipment_id, -999)))
2673: or
2674: (qt.task_status = 1
2675: AND NVL(e.equipment_id, -999) = NVL(l_sign_on_equipment_id, NVL(e.equipment_id, -999))))--bug 6326482
2676: AND wdt.user_task_type = e.standard_operation_id(+)
2677: AND qt.move_order_line_id = mol.line_id(+)
2678: AND NVL(qt.subinventory_code, '@@@') = NVL(p_sign_on_zone, NVL(qt.subinventory_code, '@@@'))
2679: AND qt.organization_id = sub.organization_id
2680: AND qt.subinventory_code = sub.secondary_inventory_name
2689: wdtv.transaction_uom,
2690: wdtv.transaction_quantity,
2691: wdtv.lot_number,
2692: wdtv.operation_plan_id,
2693: wdtv.user_task_type_id standard_operation_id,
2694: v.effective_start_date,
2695: v.effective_end_date,
2696: v.role person_resource_id,
2697: v.equipment machine_resource_id,
2712: FROM
2713: -- inlined wms_dispatchable_tasks_v, bug 2648133
2714: (SELECT
2715: transaction_temp_id task_id,
2716: standard_operation_id user_task_type_id,
2717: wms_task_type wms_task_type_id,
2718: organization_id,
2719: subinventory_code,
2720: locator_id,
2759: )
2760: ) wdtv,
2761: -- inlined wms_person_resource_utt_v, bug 2648133
2762: (SELECT
2763: utt_emp.standard_operation_id standard_operation_id,
2764: utt_emp.resource_id role,
2765: utt_eqp.resource_id equipment,
2766: utt_emp.person_id emp_id,
2767: utt_emp.effective_start_date,
2769: utt_eqp.inventory_item_id eqp_id,
2770: NULL eqp_srl /* removed for bug 2095237 */
2771: FROM
2772: (SELECT
2773: x_utt_res1.standard_operation_id standard_operation_id,
2774: x_utt_res1.resource_id resource_id,
2775: x_emp_r.person_id,
2776: x_emp_r.effective_start_date,
2777: x_emp_r.effective_end_date
2779: WHERE x_utt_res1.resource_id = r1.resource_id
2780: AND r1.resource_type = 2
2781: AND x_utt_res1.resource_id = x_emp_r.resource_id) utt_emp,
2782: (SELECT
2783: x_utt_res2.standard_operation_id standard_operation_id,
2784: x_utt_res2.resource_id,
2785: x_eqp_r.inventory_item_id
2786: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
2787: WHERE x_utt_res2.resource_id = r2.resource_id
2786: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
2787: WHERE x_utt_res2.resource_id = r2.resource_id
2788: AND r2.resource_type = 1
2789: AND x_utt_res2.resource_id = x_eqp_r.resource_id) utt_eqp
2790: WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)) v,
2791: mtl_item_locations loc,
2792: mtl_secondary_inventories sub,
2793: mtl_txn_request_lines mol,
2794: mtl_txn_request_headers moh
2806: OR (p_task_method = 'PICKBYLABEL' AND wdtv.cartonization_id = l_cartonization_id)
2807: OR (p_task_method = 'CLUSTERPICKBYLABEL' AND
2808: wdtv.cartonization_id IN (SELECT * FROM TABLE(wms_picking_pkg.list_cartonization_id))))
2809: AND wdtv.organization_id = p_sign_on_org_id
2810: AND wdtv.user_task_type_id = v.standard_operation_id -- join task to resource view, check if user defined task type match
2811: AND NVL(wdtv.subinventory_code, '@@@') = NVL(p_sign_on_zone, NVL(wdtv.subinventory_code, '@@@'))
2812: AND NVL(v.eqp_id, -999) = NVL(l_sign_on_equipment_id, NVL(v.eqp_id, -999))
2813: AND wdtv.organization_id = loc.organization_id(+) -- join task to loc, outer join for tasks do not have locator
2814: AND wdtv.locator_id = loc.inventory_location_id(+)
2886: qt.transaction_uom,
2887: qt.transaction_quantity,
2888: qt.lot_number,
2889: qt.operation_plan_id,
2890: qt.standard_operation_id,
2891: wdt.effective_start_date,
2892: wdt.effective_end_date,
2893: wdt.person_resource_id,
2894: wdt.machine_resource_id,
2919: To_char(NULL) transaction_uom,
2920: To_number(NULL) transaction_quantity,
2921: MIN(mcce.lot_number) lot_number,
2922: To_number(NULL) operation_plan_id,
2923: MIN(mcce.standard_operation_id) standard_operation_id,
2924: To_number(NULL) move_order_line_id,
2925: 4 transaction_type_id,
2926: 4 transaction_action_id,
2927: mcce.cycle_count_header_id transaction_source_id,
2942: GROUP BY mcce.cycle_count_header_id, mcce.organization_id, mcce.subinventory, mcce.locator_id, mcce.inventory_item_id, mcce.revision) qt,
2943: mtl_secondary_inventories sub,
2944: mtl_item_locations loc,
2945: (SELECT
2946: bsor.standard_operation_id,
2947: bre.resource_id,
2948: bre.inventory_item_id equipment_id
2949: FROM bom_std_op_resources bsor, bom_resources br, bom_resource_equipments bre
2950: WHERE bsor.resource_id = bre.resource_id
2955: AND wdt.person_id = p_sign_on_emp_id
2956: AND wdt.organization_id = p_sign_on_org_id
2957: AND (qt.task_id = v_task_id AND wdt.task_type = v_task_type)
2958: AND NVL(e.equipment_id, -999) = NVL(l_sign_on_equipment_id, NVL(e.equipment_id, -999))
2959: AND wdt.user_task_type = e.standard_operation_id(+)
2960: AND NVL(qt.subinventory_code, '@@@') = NVL(p_sign_on_zone, NVL(qt.subinventory_code, '@@@'))
2961: AND qt.organization_id = sub.organization_id
2962: AND qt.subinventory_code = sub.secondary_inventory_name
2963: AND qt.organization_id = loc.organization_id
2971: wdtv.transaction_uom,
2972: wdtv.transaction_quantity,
2973: wdtv.lot_number,
2974: wdtv.operation_plan_id,
2975: wdtv.user_task_type_id standard_operation_id,
2976: v.effective_start_date,
2977: v.effective_end_date,
2978: v.role person_resource_id,
2979: v.equipment machine_resource_id,
2994: FROM
2995: -- inlined wms_dispatchable_tasks_v, bug 2648133
2996: (SELECT
2997: MIN(mcce.cycle_count_entry_id) task_id,
2998: MIN(mcce.standard_operation_id) user_task_type_id,
2999: 3 wms_task_type_id,
3000: mcce.organization_id,
3001: mcce.subinventory subinventory_code,
3002: mcce.locator_id,
3031: AND NVL(mcch.disable_date,sysdate+1)> sysdate
3032: GROUP BY mcce.cycle_count_header_id, mcce.organization_id, mcce.subinventory, mcce.locator_id, mcce.inventory_item_id, mcce.revision) wdtv,
3033: -- inlined wms_person_resource_utt_v, bug 2648133
3034: (SELECT
3035: utt_emp.standard_operation_id standard_operation_id,
3036: utt_emp.resource_id role,
3037: utt_eqp.resource_id equipment,
3038: utt_emp.person_id emp_id,
3039: utt_emp.effective_start_date,
3041: utt_eqp.inventory_item_id eqp_id,
3042: NULL eqp_srl /* removed for bug 2095237 */
3043: FROM
3044: (SELECT
3045: x_utt_res1.standard_operation_id standard_operation_id,
3046: x_utt_res1.resource_id resource_id,
3047: x_emp_r.person_id,
3048: x_emp_r.effective_start_date,
3049: x_emp_r.effective_end_date
3051: WHERE x_utt_res1.resource_id = r1.resource_id
3052: AND r1.resource_type = 2
3053: AND x_utt_res1.resource_id = x_emp_r.resource_id) utt_emp,
3054: (SELECT
3055: x_utt_res2.standard_operation_id standard_operation_id,
3056: x_utt_res2.resource_id,
3057: x_eqp_r.inventory_item_id
3058: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
3059: WHERE x_utt_res2.resource_id = r2.resource_id
3058: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
3059: WHERE x_utt_res2.resource_id = r2.resource_id
3060: AND r2.resource_type = 1
3061: AND x_utt_res2.resource_id = x_eqp_r.resource_id) utt_eqp
3062: WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)) v,
3063: mtl_item_locations loc,
3064: mtl_secondary_inventories sub
3065: WHERE v.emp_id = p_sign_on_emp_id -- restrict to sign on employee
3066: AND (wdtv.task_id = v_task_id AND wdtv.wms_task_type_id = v_task_type)
3064: mtl_secondary_inventories sub
3065: WHERE v.emp_id = p_sign_on_emp_id -- restrict to sign on employee
3066: AND (wdtv.task_id = v_task_id AND wdtv.wms_task_type_id = v_task_type)
3067: AND wdtv.organization_id = p_sign_on_org_id
3068: AND wdtv.user_task_type_id = v.standard_operation_id -- join task to resource view, check if user defined task type match
3069: AND NVL(wdtv.subinventory_code, '@@@') = NVL(p_sign_on_zone, NVL(wdtv.subinventory_code, '@@@'))
3070: AND NVL(v.eqp_id, -999) = NVL(l_sign_on_equipment_id, NVL(v.eqp_id, -999))
3071: AND wdtv.organization_id = loc.organization_id(+) -- join task to loc, outer join for tasks do not have locator
3072: AND wdtv.locator_id = loc.inventory_location_id(+)
3368: INTO l_cur_x, l_cur_y, l_cur_z
3369: FROM mtl_item_locations loc,
3370: (SELECT
3371: transaction_temp_id task_id,
3372: standard_operation_id user_task_type_id,
3373: wms_task_type wms_task_type_id,
3374: organization_id organization_id,
3375: subinventory_code zone,
3376: locator_id locator_id,
3389: AND transaction_status = 2
3390: UNION ALL
3391: SELECT
3392: MIN(cycle_count_entry_id) task_id,
3393: MIN(standard_operation_id) user_task_type_id,
3394: 3 wms_task_type_id,
3395: organization_id organization_id,
3396: subinventory zone,
3397: locator_id locator_id,
4056: l_transaction_uom,
4057: l_transaction_quantity,
4058: l_lot_number,
4059: l_operation_plan_id,
4060: l_standard_operation_id,
4061: l_effective_start_date,
4062: l_effective_end_date,
4063: l_person_resource_id,
4064: l_machine_resource_id,
4092: l_transaction_uom,
4093: l_transaction_quantity,
4094: l_lot_number,
4095: l_operation_plan_id,
4096: l_standard_operation_id,
4097: l_effective_start_date,
4098: l_effective_end_date,
4099: l_person_resource_id,
4100: l_machine_resource_id,
4128: l_transaction_uom,
4129: l_transaction_quantity,
4130: l_lot_number,
4131: l_operation_plan_id,
4132: l_standard_operation_id,
4133: l_effective_start_date,
4134: l_effective_end_date,
4135: l_person_resource_id,
4136: l_machine_resource_id,
4180: transaction_quantity,
4181: lot_number,
4182: priority,
4183: operation_plan_id,
4184: standard_operation_id,
4185: effective_start_date,
4186: effective_end_date,
4187: person_resource_id,
4188: machine_resource_id,
4198: l_transaction_quantity,
4199: l_lot_number,
4200: l_task_priority,
4201: l_operation_plan_id,
4202: l_standard_operation_id,
4203: l_effective_start_date,
4204: l_effective_end_date,
4205: l_person_resource_id,
4206: l_machine_resource_id,
4266: lot_number,
4267: wms_task_type,
4268: priority,
4269: operation_plan_id,
4270: standard_operation_id,
4271: effective_start_date,
4272: effective_end_date,
4273: person_resource_id,
4274: machine_resource_id,
4422: , nvl(loc.x_coordinate, 0)
4423: , nvl(loc.y_coordinate, 0)
4424: , nvl(loc.z_coordinate, 0)
4425: FROM (SELECT transaction_temp_id task_id
4426: , standard_operation_id user_task_type_id
4427: , wms_task_type wms_task_type_id
4428: , organization_id organization_id
4429: , subinventory_code ZONE
4430: , locator_id locator_id
4444: AND(wms_task_status IS NULL
4445: OR wms_task_status = 1) --Added for task planning WB. bug#2651318
4446: UNION ALL
4447: SELECT MIN(mcce.cycle_count_entry_id) task_id
4448: , MIN(mcce.standard_operation_id) user_task_type_id
4449: , 3 wms_task_type_id
4450: , mcce.organization_id organization_id
4451: , mcce.subinventory ZONE
4452: , mcce.locator_id locator_id
4468: AND mcce.cycle_count_header_id = mcch.cycle_count_header_id
4469: AND NVL(mcch.disable_date,sysdate+1)> sysdate
4470: GROUP BY mcce.cycle_count_header_id, mcce.organization_id, mcce.subinventory, mcce.locator_id, mcce.inventory_item_id, mcce.revision) wdtv
4471: , -- inlined wms_dispatchable_tasks_v, bug 2648133
4472: (SELECT utt_emp.standard_operation_id standard_operation_id
4473: , utt_emp.resource_id ROLE
4474: , utt_eqp.resource_id equipment
4475: , utt_emp.person_id emp_id
4476: , utt_eqp.inventory_item_id eqp_id
4474: , utt_eqp.resource_id equipment
4475: , utt_emp.person_id emp_id
4476: , utt_eqp.inventory_item_id eqp_id
4477: , NULL eqp_srl /* removed for bug 2095237 */
4478: FROM (SELECT x_utt_res1.standard_operation_id standard_operation_id
4479: , x_utt_res1.resource_id resource_id
4480: , x_emp_r.person_id
4481: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
4482: WHERE x_utt_res1.resource_id = r1.resource_id
4481: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
4482: WHERE x_utt_res1.resource_id = r1.resource_id
4483: AND r1.resource_type = 2
4484: AND x_utt_res1.resource_id = x_emp_r.resource_id) utt_emp
4485: , (SELECT x_utt_res2.standard_operation_id standard_operation_id
4486: , x_utt_res2.resource_id resource_id
4487: , x_eqp_r.inventory_item_id inventory_item_id
4488: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
4489: WHERE x_utt_res2.resource_id = r2.resource_id
4488: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
4489: WHERE x_utt_res2.resource_id = r2.resource_id
4490: AND r2.resource_type = 1
4491: AND x_utt_res2.resource_id = x_eqp_r.resource_id) utt_eqp
4492: WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)) v
4493: , -- inlined wms_person_resource_utt_v, bug 2648133
4494: mtl_item_locations loc
4495: , mtl_secondary_inventories sub
4496: , mtl_txn_request_lines mol
4496: , mtl_txn_request_lines mol
4497: WHERE v.emp_id = p_sign_on_emp_id -- restrict to sign on employee
4498: AND wdtv.organization_id = p_sign_on_org_id
4499: AND wdtv.wms_task_type_id = NVL(l_sys_task_type, wdtv.wms_task_type_id) -- restrict to picking tasks or all tasks
4500: AND wdtv.user_task_type_id = v.standard_operation_id -- join task to resource view, check if user defined task type match
4501: AND wdtv.ZONE = p_sign_on_zone -- removed NVL, bug 2648133
4502: AND NVL(wdtv.cartonization_id, -999) = NVL(p_cartonization_id, NVL(wdtv.cartonization_id, -999))
4503: AND NVL(v.eqp_id, -999) = NVL(l_sign_on_equipment_id, NVL(v.eqp_id, -999))
4504: AND wdtv.organization_id = loc.organization_id(+) -- join task to loc, outer join for tasks do not have locator
4576: , nvl(loc.x_coordinate, 0)
4577: , nvl(loc.y_coordinate, 0)
4578: , nvl(loc.z_coordinate, 0)
4579: FROM (SELECT transaction_temp_id task_id
4580: , standard_operation_id user_task_type_id
4581: , wms_task_type wms_task_type_id
4582: , organization_id organization_id
4583: , subinventory_code ZONE
4584: , locator_id locator_id
4598: AND(wms_task_status IS NULL
4599: OR wms_task_status = 1) --Added for task planning WB. bug#2651318
4600: UNION ALL
4601: SELECT MIN(mcce.cycle_count_entry_id) task_id
4602: , MIN(mcce.standard_operation_id) user_task_type_id
4603: , 3 wms_task_type_id
4604: , mcce.organization_id organization_id
4605: , mcce.subinventory ZONE
4606: , mcce.locator_id locator_id
4622: AND mcce.cycle_count_header_id = mcch.cycle_count_header_id
4623: AND NVL(mcch.disable_date,sysdate+1)> sysdate
4624: GROUP BY mcce.cycle_count_header_id, mcce.organization_id, mcce.subinventory, mcce.locator_id, mcce.inventory_item_id, mcce.revision) wdtv
4625: , -- inlined wms_dispatchable_tasks_v, bug 2648133
4626: (SELECT utt_emp.standard_operation_id standard_operation_id
4627: , utt_emp.resource_id ROLE
4628: , utt_eqp.resource_id equipment
4629: , utt_emp.person_id emp_id
4630: , utt_eqp.inventory_item_id eqp_id
4628: , utt_eqp.resource_id equipment
4629: , utt_emp.person_id emp_id
4630: , utt_eqp.inventory_item_id eqp_id
4631: , NULL eqp_srl /* removed for bug 2095237 */
4632: FROM (SELECT x_utt_res1.standard_operation_id standard_operation_id
4633: , x_utt_res1.resource_id resource_id
4634: , x_emp_r.person_id
4635: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
4636: WHERE x_utt_res1.resource_id = r1.resource_id
4635: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
4636: WHERE x_utt_res1.resource_id = r1.resource_id
4637: AND r1.resource_type = 2
4638: AND x_utt_res1.resource_id = x_emp_r.resource_id) utt_emp
4639: , (SELECT x_utt_res2.standard_operation_id standard_operation_id
4640: , x_utt_res2.resource_id resource_id
4641: , x_eqp_r.inventory_item_id inventory_item_id
4642: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
4643: WHERE x_utt_res2.resource_id = r2.resource_id
4642: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
4643: WHERE x_utt_res2.resource_id = r2.resource_id
4644: AND r2.resource_type = 1
4645: AND x_utt_res2.resource_id = x_eqp_r.resource_id) utt_eqp
4646: WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)) v
4647: , -- inlined wms_person_resource_utt_v, bug 2648133
4648: mtl_item_locations loc
4649: , mtl_secondary_inventories sub
4650: , mtl_txn_request_lines mol
4651: -- mtl_system_items msi -- bug 2648133
4652: WHERE v.emp_id = p_sign_on_emp_id -- restrict to sign on employee
4653: AND wdtv.organization_id = p_sign_on_org_id
4654: AND wdtv.wms_task_type_id = NVL(l_sys_task_type, wdtv.wms_task_type_id) -- restrict to picking tasks or all tasks
4655: AND wdtv.user_task_type_id = v.standard_operation_id -- join task to resource view, check if user defined task type match
4656: AND wdtv.ZONE = p_sign_on_zone -- removed NVL, bug 2648133
4657: AND NVL(wdtv.cartonization_id, -999) = NVL(p_cartonization_id, NVL(wdtv.cartonization_id, -999))
4658: AND NVL(v.eqp_id, -999) = NVL(l_sign_on_equipment_id, NVL(v.eqp_id, -999))
4659: AND wdtv.organization_id = loc.organization_id(+) -- join task to loc, outer join for tasks do not have locator
4732: , nvl(loc.x_coordinate, 0)
4733: , nvl(loc.y_coordinate, 0)
4734: , nvl(loc.z_coordinate, 0)
4735: FROM (SELECT transaction_temp_id task_id
4736: , standard_operation_id user_task_type_id
4737: , wms_task_type wms_task_type_id
4738: , organization_id organization_id
4739: , subinventory_code ZONE
4740: , locator_id locator_id
4754: AND(wms_task_status IS NULL
4755: OR wms_task_status = 1) --Added for task planning WB. bug#2651318
4756: UNION ALL
4757: SELECT MIN(mcce.cycle_count_entry_id) task_id
4758: , MIN(mcce.standard_operation_id) user_task_type_id
4759: , 3 wms_task_type_id
4760: , mcce.organization_id organization_id
4761: , mcce.subinventory ZONE
4762: , mcce.locator_id locator_id
4778: AND mcce.cycle_count_header_id = mcch.cycle_count_header_id
4779: AND NVL(mcch.disable_date,sysdate+1)> sysdate
4780: GROUP BY mcce.cycle_count_header_id, mcce.organization_id, mcce.subinventory, mcce.locator_id, mcce.inventory_item_id, mcce.revision) wdtv
4781: , -- inlined wms_dispatchable_tasks_v, bug 2648133
4782: (SELECT utt_emp.standard_operation_id standard_operation_id
4783: , utt_emp.resource_id ROLE
4784: , utt_eqp.resource_id equipment
4785: , utt_emp.person_id emp_id
4786: , utt_eqp.inventory_item_id eqp_id
4784: , utt_eqp.resource_id equipment
4785: , utt_emp.person_id emp_id
4786: , utt_eqp.inventory_item_id eqp_id
4787: , NULL eqp_srl /* removed for bug 2095237 */
4788: FROM (SELECT x_utt_res1.standard_operation_id standard_operation_id
4789: , x_utt_res1.resource_id resource_id
4790: , x_emp_r.person_id
4791: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
4792: WHERE x_utt_res1.resource_id = r1.resource_id
4791: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
4792: WHERE x_utt_res1.resource_id = r1.resource_id
4793: AND r1.resource_type = 2
4794: AND x_utt_res1.resource_id = x_emp_r.resource_id) utt_emp
4795: , (SELECT x_utt_res2.standard_operation_id standard_operation_id
4796: , x_utt_res2.resource_id resource_id
4797: , x_eqp_r.inventory_item_id inventory_item_id
4798: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
4799: WHERE x_utt_res2.resource_id = r2.resource_id
4798: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
4799: WHERE x_utt_res2.resource_id = r2.resource_id
4800: AND r2.resource_type = 1
4801: AND x_utt_res2.resource_id = x_eqp_r.resource_id) utt_eqp
4802: WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)) v
4803: , -- inlined wms_person_resource_utt_v, bug 2648133
4804: mtl_item_locations loc
4805: , mtl_secondary_inventories sub
4806: , mtl_txn_request_lines mol
4806: , mtl_txn_request_lines mol
4807: WHERE v.emp_id = p_sign_on_emp_id -- restrict to sign on employee
4808: AND wdtv.organization_id = p_sign_on_org_id
4809: AND wdtv.wms_task_type_id = NVL(l_sys_task_type, wdtv.wms_task_type_id) -- restrict to picking tasks or all tasks
4810: AND wdtv.user_task_type_id = v.standard_operation_id -- join task to resource view, check if user defined task type match
4811: AND NVL(wdtv.cartonization_id, -999) = NVL(p_cartonization_id, NVL(wdtv.cartonization_id, -999))
4812: AND NVL(v.eqp_id, -999) = NVL(l_sign_on_equipment_id, NVL(v.eqp_id, -999))
4813: AND wdtv.organization_id = loc.organization_id(+) -- join task to loc, outer join for tasks do not have locator
4814: AND wdtv.locator_id = loc.inventory_location_id(+)
4885: , nvl(loc.x_coordinate, 0)
4886: , nvl(loc.y_coordinate, 0)
4887: , nvl(loc.z_coordinate, 0)
4888: FROM (SELECT transaction_temp_id task_id
4889: , standard_operation_id user_task_type_id
4890: , wms_task_type wms_task_type_id
4891: , organization_id organization_id
4892: , subinventory_code ZONE
4893: , locator_id locator_id
4907: AND(wms_task_status IS NULL
4908: OR wms_task_status = 1) --Added for task planning WB. bug#2651318
4909: UNION ALL
4910: SELECT MIN(mcce.cycle_count_entry_id) task_id
4911: , MIN(mcce.standard_operation_id) user_task_type_id
4912: , 3 wms_task_type_id
4913: , mcce.organization_id organization_id
4914: , mcce.subinventory ZONE
4915: , mcce.locator_id locator_id
4931: AND mcce.cycle_count_header_id = mcch.cycle_count_header_id
4932: AND NVL(mcch.disable_date,sysdate+1)> sysdate
4933: GROUP BY mcce.cycle_count_header_id, mcce.organization_id, mcce.subinventory, mcce.locator_id, mcce.inventory_item_id, mcce.revision) wdtv
4934: , -- inlined wms_dispatchable_tasks_v, bug 2648133
4935: (SELECT utt_emp.standard_operation_id standard_operation_id
4936: , utt_emp.resource_id ROLE
4937: , utt_eqp.resource_id equipment
4938: , utt_emp.person_id emp_id
4939: , utt_eqp.inventory_item_id eqp_id
4937: , utt_eqp.resource_id equipment
4938: , utt_emp.person_id emp_id
4939: , utt_eqp.inventory_item_id eqp_id
4940: , NULL eqp_srl /* removed for bug 2095237 */
4941: FROM (SELECT x_utt_res1.standard_operation_id standard_operation_id
4942: , x_utt_res1.resource_id resource_id
4943: , x_emp_r.person_id
4944: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
4945: WHERE x_utt_res1.resource_id = r1.resource_id
4944: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
4945: WHERE x_utt_res1.resource_id = r1.resource_id
4946: AND r1.resource_type = 2
4947: AND x_utt_res1.resource_id = x_emp_r.resource_id) utt_emp
4948: , (SELECT x_utt_res2.standard_operation_id standard_operation_id
4949: , x_utt_res2.resource_id resource_id
4950: , x_eqp_r.inventory_item_id inventory_item_id
4951: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
4952: WHERE x_utt_res2.resource_id = r2.resource_id
4951: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
4952: WHERE x_utt_res2.resource_id = r2.resource_id
4953: AND r2.resource_type = 1
4954: AND x_utt_res2.resource_id = x_eqp_r.resource_id) utt_eqp
4955: WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)) v
4956: , -- inlined wms_person_resource_utt_v, bug 2648133
4957: mtl_item_locations loc
4958: , mtl_secondary_inventories sub
4959: , mtl_txn_request_lines mol
4959: , mtl_txn_request_lines mol
4960: WHERE v.emp_id = p_sign_on_emp_id -- restrict to sign on employee
4961: AND wdtv.organization_id = p_sign_on_org_id
4962: AND wdtv.wms_task_type_id = NVL(l_sys_task_type, wdtv.wms_task_type_id) -- restrict to picking tasks or all tasks
4963: AND wdtv.user_task_type_id = v.standard_operation_id -- join task to resource view, check if user defined task type match
4964: AND NVL(wdtv.cartonization_id, -999) = NVL(p_cartonization_id, NVL(wdtv.cartonization_id, -999))
4965: AND NVL(v.eqp_id, -999) = NVL(l_sign_on_equipment_id, NVL(v.eqp_id, -999))
4966: AND wdtv.organization_id = loc.organization_id(+) -- join task to loc, outer join for tasks do not have locator
4967: AND wdtv.locator_id = loc.inventory_location_id(+)
5038: , nvl(loc.x_coordinate, 0)
5039: , nvl(loc.y_coordinate, 0)
5040: , nvl(loc.z_coordinate, 0)
5041: FROM (SELECT transaction_temp_id task_id
5042: , standard_operation_id user_task_type_id
5043: , wms_task_type wms_task_type_id
5044: , organization_id organization_id
5045: , subinventory_code ZONE
5046: , locator_id locator_id
5060: AND(wms_task_status IS NULL
5061: OR wms_task_status = 1) --Added for task planning WB. bug#2651318
5062: UNION ALL
5063: SELECT MIN(mcce.cycle_count_entry_id) task_id
5064: , MIN(mcce.standard_operation_id) user_task_type_id
5065: , 3 wms_task_type_id
5066: , mcce.organization_id organization_id
5067: , mcce.subinventory ZONE
5068: , mcce.locator_id locator_id
5084: AND mcce.cycle_count_header_id = mcch.cycle_count_header_id
5085: AND NVL(mcch.disable_date,sysdate+1)> sysdate
5086: GROUP BY mcce.cycle_count_header_id, mcce.organization_id, mcce.subinventory, mcce.locator_id, mcce.inventory_item_id, mcce.revision) wdtv
5087: , -- inlined wms_dispatchable_tasks_v, bug 2648133
5088: (SELECT utt_emp.standard_operation_id standard_operation_id
5089: , utt_emp.resource_id ROLE
5090: , utt_eqp.resource_id equipment
5091: , utt_emp.person_id emp_id
5092: , utt_eqp.inventory_item_id eqp_id
5090: , utt_eqp.resource_id equipment
5091: , utt_emp.person_id emp_id
5092: , utt_eqp.inventory_item_id eqp_id
5093: , NULL eqp_srl /* removed for bug 2095237 */
5094: FROM (SELECT x_utt_res1.standard_operation_id standard_operation_id
5095: , x_utt_res1.resource_id resource_id
5096: , x_emp_r.person_id
5097: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
5098: WHERE x_utt_res1.resource_id = r1.resource_id
5097: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
5098: WHERE x_utt_res1.resource_id = r1.resource_id
5099: AND r1.resource_type = 2
5100: AND x_utt_res1.resource_id = x_emp_r.resource_id) utt_emp
5101: , (SELECT x_utt_res2.standard_operation_id standard_operation_id
5102: , x_utt_res2.resource_id resource_id
5103: , x_eqp_r.inventory_item_id inventory_item_id
5104: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
5105: WHERE x_utt_res2.resource_id = r2.resource_id
5104: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
5105: WHERE x_utt_res2.resource_id = r2.resource_id
5106: AND r2.resource_type = 1
5107: AND x_utt_res2.resource_id = x_eqp_r.resource_id) utt_eqp
5108: WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)) v
5109: , -- inlined wms_person_resource_utt_v, bug 2648133
5110: mtl_item_locations loc
5111: , mtl_secondary_inventories sub
5112: , mtl_txn_request_lines mol
5113: -- mtl_system_items msi -- bug 2648133
5114: WHERE v.emp_id = p_sign_on_emp_id -- restrict to sign on employee
5115: AND wdtv.organization_id = p_sign_on_org_id
5116: AND wdtv.wms_task_type_id = NVL(l_sys_task_type, wdtv.wms_task_type_id) -- restrict to picking tasks or all tasks
5117: AND wdtv.user_task_type_id = v.standard_operation_id -- join task to resource view, check if user defined task type match
5118: AND wdtv.ZONE = p_sign_on_zone
5119: AND NVL(wdtv.cartonization_id, -999) = NVL(p_cartonization_id, NVL(wdtv.cartonization_id, -999))
5120: -- AND Nvl(v.eqp_srl, '@@@') = Nvl(l_sign_on_equipment_srl, Nvl(v.eqp_srl, '@@@')) removed for bug 2095237
5121: AND NVL(v.eqp_id, -999) = NVL(l_sign_on_equipment_id, NVL(v.eqp_id, -999))
5192: , nvl(loc.x_coordinate, 0)
5193: , nvl(loc.y_coordinate, 0)
5194: , nvl(loc.z_coordinate, 0)
5195: FROM (SELECT transaction_temp_id task_id
5196: , standard_operation_id user_task_type_id
5197: , wms_task_type wms_task_type_id
5198: , organization_id organization_id
5199: , subinventory_code ZONE
5200: , locator_id locator_id
5214: AND(wms_task_status IS NULL
5215: OR wms_task_status = 1) --Added for task planning WB. bug#2651318
5216: UNION ALL
5217: SELECT MIN(mcce.cycle_count_entry_id) task_id
5218: , MIN(mcce.standard_operation_id) user_task_type_id
5219: , 3 wms_task_type_id
5220: , mcce.organization_id organization_id
5221: , mcce.subinventory ZONE
5222: , mcce.locator_id locator_id
5238: AND mcce.cycle_count_header_id = mcch.cycle_count_header_id
5239: AND NVL(mcch.disable_date,sysdate+1)> sysdate
5240: GROUP BY mcce.cycle_count_header_id, mcce.organization_id, mcce.subinventory, mcce.locator_id, mcce.inventory_item_id, mcce.revision) wdtv
5241: , -- inlined wms_dispatchable_tasks_v, bug 2648133
5242: (SELECT utt_emp.standard_operation_id standard_operation_id
5243: , utt_emp.resource_id ROLE
5244: , utt_eqp.resource_id equipment
5245: , utt_emp.person_id emp_id
5246: , utt_eqp.inventory_item_id eqp_id
5244: , utt_eqp.resource_id equipment
5245: , utt_emp.person_id emp_id
5246: , utt_eqp.inventory_item_id eqp_id
5247: , NULL eqp_srl /* removed for bug 2095237 */
5248: FROM (SELECT x_utt_res1.standard_operation_id standard_operation_id
5249: , x_utt_res1.resource_id resource_id
5250: , x_emp_r.person_id
5251: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
5252: WHERE x_utt_res1.resource_id = r1.resource_id
5251: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
5252: WHERE x_utt_res1.resource_id = r1.resource_id
5253: AND r1.resource_type = 2
5254: AND x_utt_res1.resource_id = x_emp_r.resource_id) utt_emp
5255: , (SELECT x_utt_res2.standard_operation_id standard_operation_id
5256: , x_utt_res2.resource_id resource_id
5257: , x_eqp_r.inventory_item_id inventory_item_id
5258: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
5259: WHERE x_utt_res2.resource_id = r2.resource_id
5258: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
5259: WHERE x_utt_res2.resource_id = r2.resource_id
5260: AND r2.resource_type = 1
5261: AND x_utt_res2.resource_id = x_eqp_r.resource_id) utt_eqp
5262: WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)) v
5263: , -- inlined wms_person_resource_utt_v, bug 2648133
5264: mtl_item_locations loc
5265: , mtl_secondary_inventories sub
5266: , mtl_txn_request_lines mol
5266: , mtl_txn_request_lines mol
5267: WHERE v.emp_id = p_sign_on_emp_id -- restrict to sign on employee
5268: AND wdtv.organization_id = p_sign_on_org_id
5269: AND wdtv.wms_task_type_id = NVL(l_sys_task_type, wdtv.wms_task_type_id) -- restrict to picking tasks or all tasks
5270: AND wdtv.user_task_type_id = v.standard_operation_id -- join task to resource view, check if user defined task type match
5271: AND wdtv.ZONE = p_sign_on_zone
5272: AND NVL(wdtv.cartonization_id, -999) = NVL(p_cartonization_id, NVL(wdtv.cartonization_id, -999))
5273: AND NVL(v.eqp_id, -999) = NVL(l_sign_on_equipment_id, NVL(v.eqp_id, -999))
5274: AND wdtv.organization_id = loc.organization_id(+) -- join task to loc, outer join for tasks do not have locator
5345: , nvl(loc.x_coordinate, 0)
5346: , nvl(loc.y_coordinate, 0)
5347: , nvl(loc.z_coordinate, 0)
5348: FROM (SELECT transaction_temp_id task_id
5349: , standard_operation_id user_task_type_id
5350: , wms_task_type wms_task_type_id
5351: , organization_id organization_id
5352: , subinventory_code ZONE
5353: , locator_id locator_id
5367: AND(wms_task_status IS NULL
5368: OR wms_task_status = 1) --Added for task planning WB. bug#2651318
5369: UNION ALL
5370: SELECT MIN(mcce.cycle_count_entry_id) task_id
5371: , MIN(mcce.standard_operation_id) user_task_type_id
5372: , 3 wms_task_type_id
5373: , mcce.organization_id organization_id
5374: , mcce.subinventory ZONE
5375: , mcce.locator_id locator_id
5391: AND mcce.cycle_count_header_id = mcch.cycle_count_header_id
5392: AND NVL(mcch.disable_date,sysdate+1)> sysdate
5393: GROUP BY mcce.cycle_count_header_id, mcce.organization_id, mcce.subinventory, mcce.locator_id, mcce.inventory_item_id, mcce.revision) wdtv
5394: , -- inlined wms_dispatchable_tasks_v, bug 2648133
5395: (SELECT utt_emp.standard_operation_id standard_operation_id
5396: , utt_emp.resource_id ROLE
5397: , utt_eqp.resource_id equipment
5398: , utt_emp.person_id emp_id
5399: , utt_eqp.inventory_item_id eqp_id
5397: , utt_eqp.resource_id equipment
5398: , utt_emp.person_id emp_id
5399: , utt_eqp.inventory_item_id eqp_id
5400: , NULL eqp_srl /* removed for bug 2095237 */
5401: FROM (SELECT x_utt_res1.standard_operation_id standard_operation_id
5402: , x_utt_res1.resource_id resource_id
5403: , x_emp_r.person_id
5404: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
5405: WHERE x_utt_res1.resource_id = r1.resource_id
5404: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
5405: WHERE x_utt_res1.resource_id = r1.resource_id
5406: AND r1.resource_type = 2
5407: AND x_utt_res1.resource_id = x_emp_r.resource_id) utt_emp
5408: , (SELECT x_utt_res2.standard_operation_id standard_operation_id
5409: , x_utt_res2.resource_id resource_id
5410: , x_eqp_r.inventory_item_id inventory_item_id
5411: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
5412: WHERE x_utt_res2.resource_id = r2.resource_id
5411: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
5412: WHERE x_utt_res2.resource_id = r2.resource_id
5413: AND r2.resource_type = 1
5414: AND x_utt_res2.resource_id = x_eqp_r.resource_id) utt_eqp
5415: WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)) v
5416: , -- inlined wms_person_resource_utt_v, bug 2648133
5417: mtl_item_locations loc
5418: , mtl_secondary_inventories sub
5419: , mtl_txn_request_lines mol
5419: , mtl_txn_request_lines mol
5420: WHERE v.emp_id = p_sign_on_emp_id -- restrict to sign on employee
5421: AND wdtv.organization_id = p_sign_on_org_id
5422: AND wdtv.wms_task_type_id = NVL(l_sys_task_type, wdtv.wms_task_type_id) -- restrict to picking tasks or all tasks
5423: AND wdtv.user_task_type_id = v.standard_operation_id -- join task to resource view, check if user defined task type match
5424: AND NVL(wdtv.cartonization_id, -999) = NVL(p_cartonization_id, NVL(wdtv.cartonization_id, -999))
5425: AND NVL(v.eqp_id, -999) = NVL(l_sign_on_equipment_id, NVL(v.eqp_id, -999))
5426: AND wdtv.organization_id = loc.organization_id(+) -- join task to loc, outer join for tasks do not have locator
5427: AND wdtv.locator_id = loc.inventory_location_id(+)
5496: , nvl(loc.x_coordinate, 0)
5497: , nvl(loc.y_coordinate, 0)
5498: , nvl(loc.z_coordinate, 0)
5499: FROM (SELECT transaction_temp_id task_id
5500: , standard_operation_id user_task_type_id
5501: , wms_task_type wms_task_type_id
5502: , organization_id organization_id
5503: , subinventory_code ZONE
5504: , locator_id locator_id
5518: AND(wms_task_status IS NULL
5519: OR wms_task_status = 1) --Added for task planning WB. bug#2651318
5520: UNION ALL
5521: SELECT MIN(mcce.cycle_count_entry_id) task_id
5522: , MIN(mcce.standard_operation_id) user_task_type_id
5523: , 3 wms_task_type_id
5524: , mcce.organization_id organization_id
5525: , mcce.subinventory ZONE
5526: , mcce.locator_id locator_id
5542: AND mcce.cycle_count_header_id = mcch.cycle_count_header_id
5543: AND NVL(mcch.disable_date,sysdate+1)> sysdate
5544: GROUP BY mcce.cycle_count_header_id, mcce.organization_id, mcce.subinventory, mcce.locator_id, mcce.inventory_item_id, mcce.revision) wdtv
5545: , -- inlined wms_dispatchable_tasks_v, bug 2648133
5546: (SELECT utt_emp.standard_operation_id standard_operation_id
5547: , utt_emp.resource_id ROLE
5548: , utt_eqp.resource_id equipment
5549: , utt_emp.person_id emp_id
5550: , utt_eqp.inventory_item_id eqp_id
5548: , utt_eqp.resource_id equipment
5549: , utt_emp.person_id emp_id
5550: , utt_eqp.inventory_item_id eqp_id
5551: , NULL eqp_srl /* removed for bug 2095237 */
5552: FROM (SELECT x_utt_res1.standard_operation_id standard_operation_id
5553: , x_utt_res1.resource_id resource_id
5554: , x_emp_r.person_id
5555: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
5556: WHERE x_utt_res1.resource_id = r1.resource_id
5555: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
5556: WHERE x_utt_res1.resource_id = r1.resource_id
5557: AND r1.resource_type = 2
5558: AND x_utt_res1.resource_id = x_emp_r.resource_id) utt_emp
5559: , (SELECT x_utt_res2.standard_operation_id standard_operation_id
5560: , x_utt_res2.resource_id resource_id
5561: , x_eqp_r.inventory_item_id inventory_item_id
5562: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
5563: WHERE x_utt_res2.resource_id = r2.resource_id
5562: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
5563: WHERE x_utt_res2.resource_id = r2.resource_id
5564: AND r2.resource_type = 1
5565: AND x_utt_res2.resource_id = x_eqp_r.resource_id) utt_eqp
5566: WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)) v
5567: , -- inlined wms_person_resource_utt_v, bug 2648133
5568: mtl_item_locations loc
5569: , mtl_secondary_inventories sub
5570: , mtl_txn_request_lines mol
5570: , mtl_txn_request_lines mol
5571: WHERE v.emp_id = p_sign_on_emp_id -- restrict to sign on employee
5572: AND wdtv.organization_id = p_sign_on_org_id
5573: AND wdtv.wms_task_type_id = NVL(l_sys_task_type, wdtv.wms_task_type_id) -- restrict to picking tasks or all tasks
5574: AND wdtv.user_task_type_id = v.standard_operation_id -- join task to resource view, check if user defined task type match
5575: AND NVL(wdtv.cartonization_id, -999) = NVL(p_cartonization_id, NVL(wdtv.cartonization_id, -999))
5576: AND NVL(v.eqp_id, -999) = NVL(l_sign_on_equipment_id, NVL(v.eqp_id, -999))
5577: AND wdtv.organization_id = loc.organization_id(+) -- join task to loc, outer join for tasks do not have locator
5578: AND wdtv.locator_id = loc.inventory_location_id(+)
5644: , nvl(loc.x_coordinate, 0)
5645: , nvl(loc.y_coordinate, 0)
5646: , nvl(loc.z_coordinate, 0)
5647: FROM (SELECT transaction_temp_id task_id
5648: , standard_operation_id user_task_type_id
5649: , wms_task_type wms_task_type_id
5650: , organization_id organization_id
5651: , subinventory_code ZONE
5652: , locator_id locator_id
5666: AND(wms_task_status IS NULL
5667: OR wms_task_status = 1) --Added for task planning WB. bug#2651318
5668: UNION ALL
5669: SELECT MIN(mcce.cycle_count_entry_id) task_id
5670: , MIN(mcce.standard_operation_id) user_task_type_id
5671: , 3 wms_task_type_id
5672: , mcce.organization_id organization_id
5673: , mcce.subinventory ZONE
5674: , mcce.locator_id locator_id
5690: AND mcce.cycle_count_header_id = mcch.cycle_count_header_id
5691: AND NVL(mcch.disable_date,sysdate+1)> sysdate
5692: GROUP BY mcce.cycle_count_header_id, mcce.organization_id, mcce.subinventory, mcce.locator_id, mcce.inventory_item_id, mcce.revision) wdtv
5693: , -- inlined wms_dispatchable_tasks_v, bug 2648133
5694: (SELECT utt_emp.standard_operation_id standard_operation_id
5695: , utt_emp.resource_id ROLE
5696: , utt_eqp.resource_id equipment
5697: , utt_emp.person_id emp_id
5698: , utt_eqp.inventory_item_id eqp_id
5696: , utt_eqp.resource_id equipment
5697: , utt_emp.person_id emp_id
5698: , utt_eqp.inventory_item_id eqp_id
5699: , NULL eqp_srl /* removed for bug 2095237 */
5700: FROM (SELECT x_utt_res1.standard_operation_id standard_operation_id
5701: , x_utt_res1.resource_id resource_id
5702: , x_emp_r.person_id
5703: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
5704: WHERE x_utt_res1.resource_id = r1.resource_id
5703: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
5704: WHERE x_utt_res1.resource_id = r1.resource_id
5705: AND r1.resource_type = 2
5706: AND x_utt_res1.resource_id = x_emp_r.resource_id) utt_emp
5707: , (SELECT x_utt_res2.standard_operation_id standard_operation_id
5708: , x_utt_res2.resource_id resource_id
5709: , x_eqp_r.inventory_item_id inventory_item_id
5710: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
5711: WHERE x_utt_res2.resource_id = r2.resource_id
5710: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
5711: WHERE x_utt_res2.resource_id = r2.resource_id
5712: AND r2.resource_type = 1
5713: AND x_utt_res2.resource_id = x_eqp_r.resource_id) utt_eqp
5714: WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)) v
5715: , -- inlined wms_person_resource_utt_v, bug 2648133
5716: mtl_item_locations loc
5717: , mtl_secondary_inventories sub
5718: , mtl_txn_request_lines mol
5721: OR(wdtv.task_id = v_task_id
5722: AND wdtv.wms_task_type_id = v_task_type))
5723: AND wdtv.organization_id = p_sign_on_org_id
5724: AND wdtv.wms_task_type_id = NVL(l_sys_task_type, wdtv.wms_task_type_id) -- restrict to picking tasks or all tasks
5725: AND wdtv.user_task_type_id = v.standard_operation_id -- join task to resource view, check if user defined task type match
5726: AND NVL(wdtv.ZONE, '@@@') = NVL(p_sign_on_zone, NVL(wdtv.ZONE, '@@@'))
5727: AND NVL(wdtv.cartonization_id, -999) = NVL(p_cartonization_id, NVL(wdtv.cartonization_id, -999))
5728: AND NVL(v.eqp_id, -999) = NVL(l_sign_on_equipment_id, NVL(v.eqp_id, -999))
5729: AND wdtv.organization_id = loc.organization_id(+) -- join task to loc, outer join for tasks do not have locator
5795: , nvl(loc.x_coordinate, 0)
5796: , nvl(loc.y_coordinate, 0)
5797: , nvl(loc.z_coordinate, 0)
5798: FROM (SELECT transaction_temp_id task_id
5799: , standard_operation_id user_task_type_id
5800: , wms_task_type wms_task_type_id
5801: , organization_id organization_id
5802: , subinventory_code ZONE
5803: , locator_id locator_id
5817: AND(wms_task_status IS NULL
5818: OR wms_task_status = 1) --Added for task planning WB. bug#2651318
5819: UNION ALL
5820: SELECT MIN(mcce.cycle_count_entry_id) task_id
5821: , MIN(mcce.standard_operation_id) user_task_type_id
5822: , 3 wms_task_type_id
5823: , mcce.organization_id organization_id
5824: , mcce.subinventory ZONE
5825: , mcce.locator_id locator_id
5841: AND mcce.cycle_count_header_id = mcch.cycle_count_header_id
5842: AND NVL(mcch.disable_date,sysdate+1)> sysdate
5843: GROUP BY mcce.cycle_count_header_id, mcce.organization_id, mcce.subinventory, mcce.locator_id, mcce.inventory_item_id, mcce.revision) wdtv
5844: , -- inlined wms_dispatchable_tasks_v, bug 2648133
5845: (SELECT utt_emp.standard_operation_id standard_operation_id
5846: , utt_emp.resource_id ROLE
5847: , utt_eqp.resource_id equipment
5848: , utt_emp.person_id emp_id
5849: , utt_eqp.inventory_item_id eqp_id
5847: , utt_eqp.resource_id equipment
5848: , utt_emp.person_id emp_id
5849: , utt_eqp.inventory_item_id eqp_id
5850: , NULL eqp_srl /* removed for bug 2095237 */
5851: FROM (SELECT x_utt_res1.standard_operation_id standard_operation_id
5852: , x_utt_res1.resource_id resource_id
5853: , x_emp_r.person_id
5854: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
5855: WHERE x_utt_res1.resource_id = r1.resource_id
5854: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
5855: WHERE x_utt_res1.resource_id = r1.resource_id
5856: AND r1.resource_type = 2
5857: AND x_utt_res1.resource_id = x_emp_r.resource_id) utt_emp
5858: , (SELECT x_utt_res2.standard_operation_id standard_operation_id
5859: , x_utt_res2.resource_id resource_id
5860: , x_eqp_r.inventory_item_id inventory_item_id
5861: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
5862: WHERE x_utt_res2.resource_id = r2.resource_id
5861: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
5862: WHERE x_utt_res2.resource_id = r2.resource_id
5863: AND r2.resource_type = 1
5864: AND x_utt_res2.resource_id = x_eqp_r.resource_id) utt_eqp
5865: WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)) v
5866: , -- inlined wms_person_resource_utt_v, bug 2648133
5867: mtl_item_locations loc
5868: , mtl_secondary_inventories sub
5869: , mtl_txn_request_lines mol
5873: OR(wdtv.task_id = v_task_id
5874: AND wdtv.wms_task_type_id = v_task_type))
5875: AND wdtv.organization_id = p_sign_on_org_id
5876: AND wdtv.wms_task_type_id = NVL(l_sys_task_type, wdtv.wms_task_type_id) -- restrict to picking tasks or all tasks
5877: AND wdtv.user_task_type_id = v.standard_operation_id -- join task to resource view, check if user defined task type match
5878: AND NVL(wdtv.ZONE, '@@@') = NVL(p_sign_on_zone, NVL(wdtv.ZONE, '@@@'))
5879: AND NVL(wdtv.cartonization_id, -999) = NVL(p_cartonization_id, NVL(wdtv.cartonization_id, -999))
5880: -- AND Nvl(v.eqp_srl, '@@@') = Nvl(l_sign_on_equipment_srl, Nvl(v.eqp_srl, '@@@')) removed for bug 2095237
5881: AND NVL(v.eqp_id, -999) = NVL(l_sign_on_equipment_id, NVL(v.eqp_id, -999))
5948: , nvl(loc.x_coordinate, 0)
5949: , nvl(loc.y_coordinate, 0)
5950: , nvl(loc.z_coordinate, 0)
5951: FROM (SELECT transaction_temp_id task_id
5952: , standard_operation_id user_task_type_id
5953: , wms_task_type wms_task_type_id
5954: , organization_id organization_id
5955: , subinventory_code ZONE
5956: , locator_id locator_id
5970: AND(wms_task_status IS NULL
5971: OR wms_task_status = 1) --Added for task planning WB. bug#2651318
5972: UNION ALL
5973: SELECT MIN(mcce.cycle_count_entry_id) task_id
5974: , MIN(mcce.standard_operation_id) user_task_type_id
5975: , 3 wms_task_type_id
5976: , mcce.organization_id organization_id
5977: , mcce.subinventory ZONE
5978: , mcce.locator_id locator_id
5992: -- bug 3972076
5993: --AND NVL(TRUNC(mcce.count_due_date, 'DD'), TRUNC(SYSDATE, 'DD')) >= TRUNC(SYSDATE, 'DD')
5994: GROUP BY mcce.cycle_count_header_id, mcce.organization_id, mcce.subinventory, mcce.locator_id, mcce.inventory_item_id, mcce.revision) wdtv
5995: , -- inlined wms_dispatchable_tasks_v, bug 2648133
5996: (SELECT utt_emp.standard_operation_id standard_operation_id
5997: , utt_emp.resource_id ROLE
5998: , utt_eqp.resource_id equipment
5999: , utt_emp.person_id emp_id
6000: , utt_eqp.inventory_item_id eqp_id
5998: , utt_eqp.resource_id equipment
5999: , utt_emp.person_id emp_id
6000: , utt_eqp.inventory_item_id eqp_id
6001: , NULL eqp_srl /* removed for bug 2095237 */
6002: FROM (SELECT x_utt_res1.standard_operation_id standard_operation_id
6003: , x_utt_res1.resource_id resource_id
6004: , x_emp_r.person_id
6005: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
6006: WHERE x_utt_res1.resource_id = r1.resource_id
6005: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
6006: WHERE x_utt_res1.resource_id = r1.resource_id
6007: AND r1.resource_type = 2
6008: AND x_utt_res1.resource_id = x_emp_r.resource_id) utt_emp
6009: , (SELECT x_utt_res2.standard_operation_id standard_operation_id
6010: , x_utt_res2.resource_id resource_id
6011: , x_eqp_r.inventory_item_id inventory_item_id
6012: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
6013: WHERE x_utt_res2.resource_id = r2.resource_id
6012: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
6013: WHERE x_utt_res2.resource_id = r2.resource_id
6014: AND r2.resource_type = 1
6015: AND x_utt_res2.resource_id = x_eqp_r.resource_id) utt_eqp
6016: WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)) v
6017: , -- inlined wms_person_resource_utt_v, bug 2648133
6018: mtl_item_locations loc
6019: , mtl_secondary_inventories sub
6020: , mtl_txn_request_lines mol
6023: OR(wdtv.task_id = v_task_id
6024: AND wdtv.wms_task_type_id = v_task_type))
6025: AND wdtv.organization_id = p_sign_on_org_id
6026: AND wdtv.wms_task_type_id = NVL(l_sys_task_type, wdtv.wms_task_type_id) -- restrict to picking tasks or all tasks
6027: AND wdtv.user_task_type_id = v.standard_operation_id -- join task to resource view, check if user defined task type match
6028: AND NVL(wdtv.ZONE, '@@@') = NVL(p_sign_on_zone, NVL(wdtv.ZONE, '@@@'))
6029: AND NVL(wdtv.cartonization_id, -999) = NVL(p_cartonization_id, NVL(wdtv.cartonization_id, -999))
6030: AND NVL(v.eqp_id, -999) = NVL(l_sign_on_equipment_id, NVL(v.eqp_id, -999))
6031: AND wdtv.organization_id = loc.organization_id(+) -- join task to loc, outer join for tasks do not have locator
6096: , nvl(loc.x_coordinate, 0)
6097: , nvl(loc.y_coordinate, 0)
6098: , nvl(loc.z_coordinate, 0)
6099: FROM (SELECT transaction_temp_id task_id
6100: , standard_operation_id user_task_type_id
6101: , wms_task_type wms_task_type_id
6102: , organization_id organization_id
6103: , subinventory_code ZONE
6104: , locator_id locator_id
6118: AND(wms_task_status IS NULL
6119: OR wms_task_status = 1) --Added for task planning WB. bug#2651318
6120: UNION ALL
6121: SELECT MIN(mcce.cycle_count_entry_id) task_id
6122: , MIN(mcce.standard_operation_id) user_task_type_id
6123: , 3 wms_task_type_id
6124: , mcce.organization_id organization_id
6125: , mcce.subinventory ZONE
6126: , mcce.locator_id locator_id
6142: AND mcce.cycle_count_header_id = mcch.cycle_count_header_id
6143: AND NVL(mcch.disable_date,sysdate+1)> sysdate
6144: GROUP BY mcce.cycle_count_header_id, mcce.organization_id, mcce.subinventory, mcce.locator_id, mcce.inventory_item_id, mcce.revision) wdtv
6145: , -- inlined wms_dispatchable_tasks_v, bug 2648133
6146: (SELECT utt_emp.standard_operation_id standard_operation_id
6147: , utt_emp.resource_id ROLE
6148: , utt_eqp.resource_id equipment
6149: , utt_emp.person_id emp_id
6150: , utt_eqp.inventory_item_id eqp_id
6148: , utt_eqp.resource_id equipment
6149: , utt_emp.person_id emp_id
6150: , utt_eqp.inventory_item_id eqp_id
6151: , NULL eqp_srl /* removed for bug 2095237 */
6152: FROM (SELECT x_utt_res1.standard_operation_id standard_operation_id
6153: , x_utt_res1.resource_id resource_id
6154: , x_emp_r.person_id
6155: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
6156: WHERE x_utt_res1.resource_id = r1.resource_id
6155: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
6156: WHERE x_utt_res1.resource_id = r1.resource_id
6157: AND r1.resource_type = 2
6158: AND x_utt_res1.resource_id = x_emp_r.resource_id) utt_emp
6159: , (SELECT x_utt_res2.standard_operation_id standard_operation_id
6160: , x_utt_res2.resource_id resource_id
6161: , x_eqp_r.inventory_item_id inventory_item_id
6162: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
6163: WHERE x_utt_res2.resource_id = r2.resource_id
6162: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
6163: WHERE x_utt_res2.resource_id = r2.resource_id
6164: AND r2.resource_type = 1
6165: AND x_utt_res2.resource_id = x_eqp_r.resource_id) utt_eqp
6166: WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)) v
6167: , -- inlined wms_person_resource_utt_v, bug 2648133
6168: mtl_item_locations loc
6169: , mtl_secondary_inventories sub
6170: , mtl_txn_request_lines mol
6173: OR(wdtv.task_id = v_task_id
6174: AND wdtv.wms_task_type_id = v_task_type))
6175: AND wdtv.organization_id = p_sign_on_org_id
6176: AND wdtv.wms_task_type_id = NVL(l_sys_task_type, wdtv.wms_task_type_id) -- restrict to picking tasks or all tasks
6177: AND wdtv.user_task_type_id = v.standard_operation_id -- join task to resource view, check if user defined task type match
6178: AND NVL(wdtv.ZONE, '@@@') = NVL(p_sign_on_zone, NVL(wdtv.ZONE, '@@@')) -- bug 2648133
6179: AND NVL(wdtv.cartonization_id, -999) = NVL(p_cartonization_id, NVL(wdtv.cartonization_id, -999))
6180: AND NVL(v.eqp_id, -999) = NVL(l_sign_on_equipment_id, NVL(v.eqp_id, -999))
6181: AND wdtv.organization_id = loc.organization_id(+) -- join task to loc, outer join for tasks do not have locator
6314: wdtv.organization_id = ' || p_sign_on_org_id ||'
6315: AND Nvl(wdtv.zone, ''@@@'') = Nvl('''|| p_sign_on_zone || ''', Nvl(wdtv.zone, ''@@@''))
6316: AND wdtv.user_task_type_id IN
6317: (
6318: SELECT standard_operation_id
6319: FROM wms_person_resource_utt_v v
6320: WHERE v.emp_id = ' || p_sign_on_emp_id ||'
6321: AND Nvl(v.eqp_srl, ''@@@'') = Nvl(''' || l_sign_on_equipment_srl || ''', Nvl(v.eqp_srl, ''@@@''))
6322: AND Nvl(v.eqp_id, -999) = Nvl(' || l_equipment_id_str ||', Nvl(v.eqp_id, -999))
6450: , l_cur_y
6451: , l_cur_z
6452: FROM mtl_item_locations loc
6453: , (SELECT transaction_temp_id task_id
6454: , standard_operation_id user_task_type_id
6455: , wms_task_type wms_task_type_id
6456: , organization_id organization_id
6457: , subinventory_code ZONE
6458: , locator_id locator_id
6470: WHERE wms_task_type IS NOT NULL
6471: AND transaction_status = 2
6472: UNION ALL
6473: SELECT MIN(cycle_count_entry_id) task_id
6474: , MIN(standard_operation_id) user_task_type_id
6475: , 3 wms_task_type_id
6476: , organization_id organization_id
6477: , subinventory ZONE
6478: , locator_id locator_id
7322: ) xyz_distance
7323: , loc.concatenated_segments loc_concat_segs
7324: FROM --wms_dispatchable_tasks_v wdtv,
7325: (SELECT transaction_temp_id task_id
7326: , standard_operation_id user_task_type_id
7327: , wms_task_type wms_task_type_id
7328: , organization_id organization_id
7329: , subinventory_code ZONE
7330: , locator_id locator_id
7347: ) --Added for task planning WB. bug#2651318
7348: -- Commented out the following lines because we won't consider cycle counting taks for cluster pick
7349: /*UNION ALL
7350: SELECT MIN(cycle_count_entry_id) task_id,
7351: MIN(standard_operation_id) user_task_type_id,
7352: 3 wms_task_type_id,
7353: organization_id organization_id,
7354: subinventory zone,
7355: locator_id locator_id,
7372: */
7373: ) wdtv
7374: , -- inlined wms_dispatchable_tasks_v, bug 2648133
7375: --wms_person_resource_utt_v v,
7376: (SELECT utt_emp.standard_operation_id standard_operation_id
7377: , utt_emp.resource_id ROLE
7378: , utt_eqp.resource_id equipment
7379: , utt_emp.person_id emp_id
7380: , utt_eqp.inventory_item_id eqp_id
7378: , utt_eqp.resource_id equipment
7379: , utt_emp.person_id emp_id
7380: , utt_eqp.inventory_item_id eqp_id
7381: , NULL eqp_srl /* removed for bug 2095237 */
7382: FROM (SELECT x_utt_res1.standard_operation_id standard_operation_id
7383: , x_utt_res1.resource_id resource_id
7384: , x_emp_r.person_id
7385: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
7386: WHERE x_utt_res1.resource_id = r1.resource_id
7385: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
7386: WHERE x_utt_res1.resource_id = r1.resource_id
7387: AND r1.resource_type = 2
7388: AND x_utt_res1.resource_id = x_emp_r.resource_id) utt_emp
7389: , (SELECT x_utt_res2.standard_operation_id standard_operation_id
7390: , x_utt_res2.resource_id resource_id
7391: , x_eqp_r.inventory_item_id inventory_item_id
7392: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
7393: WHERE x_utt_res2.resource_id = r2.resource_id
7392: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
7393: WHERE x_utt_res2.resource_id = r2.resource_id
7394: AND r2.resource_type = 1
7395: AND x_utt_res2.resource_id = x_eqp_r.resource_id) utt_eqp
7396: WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)) v
7397: , -- inlined wms_person_resource_utt_v, bug 2648133
7398: mtl_item_locations_kfv loc
7399: , --changed to kfv bug#2742611
7400: mtl_secondary_inventories sub
7409: AND wdtv.wms_task_type_id = 1 -- restrict to picking tasks
7410: AND mol.header_id = moh.header_id
7411: AND moh.move_order_type = 3 -- only pick wave move orders are considered
7412: AND wdtv.user_task_type_id =
7413: v.standard_operation_id -- join task to resource view, check if user defined task type match
7414: --AND Nvl(wdtv.zone, '@@@') = Nvl(p_sign_on_zone, Nvl(wdtv.zone, '@@@'))
7415: AND wdtv.ZONE = p_sign_on_zone -- removed NVL, bug 2648133
7416: AND NVL(wdtv.cartonization_id, -999) = NVL(p_cartonization_id, NVL(wdtv.cartonization_id, -999))
7417: AND NVL(v.eqp_id, -999) = NVL(l_sign_on_equipment_id, NVL(v.eqp_id, -999))
7483: ) xyz_distance
7484: , loc.concatenated_segments loc_concat_segs
7485: FROM --wms_dispatchable_tasks_v wdtv,
7486: (SELECT transaction_temp_id task_id
7487: , standard_operation_id user_task_type_id
7488: , wms_task_type wms_task_type_id
7489: , organization_id organization_id
7490: , subinventory_code ZONE
7491: , locator_id locator_id
7508: ) --Added for task planning WB. bug#2651318
7509: -- Commented out the following lines because we won't consider cycle counting taks for cluster pick
7510: /*UNION ALL
7511: SELECT MIN(cycle_count_entry_id) task_id,
7512: MIN(standard_operation_id) user_task_type_id,
7513: 3 wms_task_type_id,
7514: organization_id organization_id,
7515: subinventory zone,
7516: locator_id locator_id,
7533: */
7534: ) wdtv
7535: , -- inlined wms_dispatchable_tasks_v, bug 2648133
7536: --wms_person_resource_utt_v v,
7537: (SELECT utt_emp.standard_operation_id standard_operation_id
7538: , utt_emp.resource_id ROLE
7539: , utt_eqp.resource_id equipment
7540: , utt_emp.person_id emp_id
7541: , utt_eqp.inventory_item_id eqp_id
7539: , utt_eqp.resource_id equipment
7540: , utt_emp.person_id emp_id
7541: , utt_eqp.inventory_item_id eqp_id
7542: , NULL eqp_srl /* removed for bug 2095237 */
7543: FROM (SELECT x_utt_res1.standard_operation_id standard_operation_id
7544: , x_utt_res1.resource_id resource_id
7545: , x_emp_r.person_id
7546: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
7547: WHERE x_utt_res1.resource_id = r1.resource_id
7546: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
7547: WHERE x_utt_res1.resource_id = r1.resource_id
7548: AND r1.resource_type = 2
7549: AND x_utt_res1.resource_id = x_emp_r.resource_id) utt_emp
7550: , (SELECT x_utt_res2.standard_operation_id standard_operation_id
7551: , x_utt_res2.resource_id resource_id
7552: , x_eqp_r.inventory_item_id inventory_item_id
7553: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
7554: WHERE x_utt_res2.resource_id = r2.resource_id
7553: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
7554: WHERE x_utt_res2.resource_id = r2.resource_id
7555: AND r2.resource_type = 1
7556: AND x_utt_res2.resource_id = x_eqp_r.resource_id) utt_eqp
7557: WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)) v
7558: , -- inlined wms_person_resource_utt_v, bug 2648133
7559: mtl_item_locations_kfv loc
7560: , --changed to kfv bug#2742611
7561: mtl_secondary_inventories sub
7566: AND wdtv.organization_id = p_sign_on_org_id
7567: AND wdtv.wms_task_type_id = 1 -- restrict to picking tasks
7568: AND mol.header_id = moh.header_id
7569: AND moh.move_order_type = 5 -- only WIP jobs are considered : Bug 2666620 BackFlush Removed
7570: AND wdtv.user_task_type_id = v.standard_operation_id -- join task to resource view, check if user defined task type match
7571: --AND Nvl(wdtv.zone, '@@@') = Nvl(p_sign_on_zone, Nvl(wdtv.zone, '@@@'))
7572: AND wdtv.ZONE = p_sign_on_zone -- removed NVL, bug 2648133
7573: AND NVL(wdtv.cartonization_id, -999) = NVL(p_cartonization_id, NVL(wdtv.cartonization_id, -999))
7574: AND NVL(v.eqp_id, -999) = NVL(l_sign_on_equipment_id, NVL(v.eqp_id, -999))
7643: ) xyz_distance
7644: , loc.concatenated_segments loc_concat_segs
7645: FROM --wms_dispatchable_tasks_v wdtv,
7646: (SELECT transaction_temp_id task_id
7647: , standard_operation_id user_task_type_id
7648: , wms_task_type wms_task_type_id
7649: , organization_id organization_id
7650: , subinventory_code ZONE
7651: , locator_id locator_id
7668: ) --Added for task planning WB. bug#2651318
7669: -- Commented out the following lines because we won't consider cycle counting taks for cluster pick
7670: /*UNION ALL
7671: SELECT MIN(cycle_count_entry_id) task_id,
7672: MIN(standard_operation_id) user_task_type_id,
7673: 3 wms_task_type_id,
7674: organization_id organization_id,
7675: subinventory zone,
7676: locator_id locator_id,
7693: */
7694: ) wdtv
7695: , -- inlined wms_dispatchable_tasks_v, bug 2648133
7696: --wms_person_resource_utt_v v,
7697: (SELECT utt_emp.standard_operation_id standard_operation_id
7698: , utt_emp.resource_id ROLE
7699: , utt_eqp.resource_id equipment
7700: , utt_emp.person_id emp_id
7701: , utt_eqp.inventory_item_id eqp_id
7699: , utt_eqp.resource_id equipment
7700: , utt_emp.person_id emp_id
7701: , utt_eqp.inventory_item_id eqp_id
7702: , NULL eqp_srl /* removed for bug 2095237 */
7703: FROM (SELECT x_utt_res1.standard_operation_id standard_operation_id
7704: , x_utt_res1.resource_id resource_id
7705: , x_emp_r.person_id
7706: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
7707: WHERE x_utt_res1.resource_id = r1.resource_id
7706: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
7707: WHERE x_utt_res1.resource_id = r1.resource_id
7708: AND r1.resource_type = 2
7709: AND x_utt_res1.resource_id = x_emp_r.resource_id) utt_emp
7710: , (SELECT x_utt_res2.standard_operation_id standard_operation_id
7711: , x_utt_res2.resource_id resource_id
7712: , x_eqp_r.inventory_item_id inventory_item_id
7713: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
7714: WHERE x_utt_res2.resource_id = r2.resource_id
7713: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
7714: WHERE x_utt_res2.resource_id = r2.resource_id
7715: AND r2.resource_type = 1
7716: AND x_utt_res2.resource_id = x_eqp_r.resource_id) utt_eqp
7717: WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)) v
7718: , -- inlined wms_person_resource_utt_v, bug 2648133
7719: mtl_item_locations_kfv loc
7720: , --changed to kfv bug#2742611
7721: mtl_secondary_inventories sub
7730: AND wdtv.wms_task_type_id = 1 -- restrict to picking tasks
7731: AND mol.header_id = moh.header_id
7732: AND moh.move_order_type = 3 -- only pick wave move orders are considered
7733: AND wdtv.user_task_type_id =
7734: v.standard_operation_id -- join task to resource view, check if user defined task type match
7735: --AND Nvl(wdtv.zone, '@@@') = Nvl(p_sign_on_zone, Nvl(wdtv.zone, '@@@'))
7736: AND NVL(wdtv.cartonization_id, -999) = NVL(p_cartonization_id, NVL(wdtv.cartonization_id, -999))
7737: AND NVL(v.eqp_id, -999) = NVL(l_sign_on_equipment_id, NVL(v.eqp_id, -999))
7738: AND wdtv.organization_id = loc.organization_id(+) -- join task to loc, outer join for tasks do not have locator
7803: ) xyz_distance
7804: , loc.concatenated_segments loc_concat_segs
7805: FROM --wms_dispatchable_tasks_v wdtv,
7806: (SELECT transaction_temp_id task_id
7807: , standard_operation_id user_task_type_id
7808: , wms_task_type wms_task_type_id
7809: , organization_id organization_id
7810: , subinventory_code ZONE
7811: , locator_id locator_id
7828: ) --Added for task planning WB. bug#2651318
7829: -- Commented out the following lines because we won't consider cycle counting taks for cluster pick
7830: /*UNION ALL
7831: SELECT MIN(cycle_count_entry_id) task_id,
7832: MIN(standard_operation_id) user_task_type_id,
7833: 3 wms_task_type_id,
7834: organization_id organization_id,
7835: subinventory zone,
7836: locator_id locator_id,
7853: */
7854: ) wdtv
7855: , -- inlined wms_dispatchable_tasks_v, bug 2648133
7856: --wms_person_resource_utt_v v,
7857: (SELECT utt_emp.standard_operation_id standard_operation_id
7858: , utt_emp.resource_id ROLE
7859: , utt_eqp.resource_id equipment
7860: , utt_emp.person_id emp_id
7861: , utt_eqp.inventory_item_id eqp_id
7859: , utt_eqp.resource_id equipment
7860: , utt_emp.person_id emp_id
7861: , utt_eqp.inventory_item_id eqp_id
7862: , NULL eqp_srl /* removed for bug 2095237 */
7863: FROM (SELECT x_utt_res1.standard_operation_id standard_operation_id
7864: , x_utt_res1.resource_id resource_id
7865: , x_emp_r.person_id
7866: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
7867: WHERE x_utt_res1.resource_id = r1.resource_id
7866: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
7867: WHERE x_utt_res1.resource_id = r1.resource_id
7868: AND r1.resource_type = 2
7869: AND x_utt_res1.resource_id = x_emp_r.resource_id) utt_emp
7870: , (SELECT x_utt_res2.standard_operation_id standard_operation_id
7871: , x_utt_res2.resource_id resource_id
7872: , x_eqp_r.inventory_item_id inventory_item_id
7873: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
7874: WHERE x_utt_res2.resource_id = r2.resource_id
7873: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
7874: WHERE x_utt_res2.resource_id = r2.resource_id
7875: AND r2.resource_type = 1
7876: AND x_utt_res2.resource_id = x_eqp_r.resource_id) utt_eqp
7877: WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)) v
7878: , -- inlined wms_person_resource_utt_v, bug 2648133
7879: mtl_item_locations_kfv loc
7880: , --changed to kfv bug#2742611
7881: mtl_secondary_inventories sub
7886: AND wdtv.organization_id = p_sign_on_org_id
7887: AND wdtv.wms_task_type_id = 1 -- restrict to picking tasks
7888: AND mol.header_id = moh.header_id
7889: AND moh.move_order_type = 5 -- only WIP jobs are considered : Bug 2666620 BackFlush Removed
7890: AND wdtv.user_task_type_id = v.standard_operation_id -- join task to resource view, check if user defined task type match
7891: --AND Nvl(wdtv.zone, '@@@') = Nvl(p_sign_on_zone, Nvl(wdtv.zone, '@@@'))
7892: AND NVL(wdtv.cartonization_id, -999) = NVL(p_cartonization_id, NVL(wdtv.cartonization_id, -999))
7893: AND NVL(v.eqp_id, -999) = NVL(l_sign_on_equipment_id, NVL(v.eqp_id, -999))
7894: AND wdtv.organization_id = loc.organization_id(+) -- join task to loc, outer join for tasks do not have locator
7971: , wdt.person_resource_id
7972: , wdt.machine_resource_id
7973: FROM wms_dispatched_tasks wdt,
7974: (SELECT transaction_temp_id task_id
7975: , standard_operation_id user_task_type_id
7976: , wms_task_type wms_task_type_id
7977: , organization_id organization_id
7978: , subinventory_code ZONE
7979: , locator_id locator_id
7997: OR wms_task_status = 1
7998: ) --Added for task planning WB. bug#2651318
7999: ) qt
8000: , (SELECT
8001: bsor.standard_operation_id,
8002: bre.resource_id,
8003: bre.inventory_item_id equipment_id
8004: FROM bom_std_op_resources bsor, bom_resources br, bom_resource_equipments bre
8005: WHERE bsor.resource_id = bre.resource_id
8021: AND qt.wms_task_type_id = 1 -- restrict to picking tasks
8022: AND mol.header_id = moh.header_id
8023: AND moh.move_order_type = 3 -- only pick wave move orders are considered
8024: AND qt.user_task_type_id =
8025: e.standard_operation_id(+) -- join task to resource view, check if user defined task type match
8026: --AND Nvl(wdtv.zone, '@@@') = Nvl(p_sign_on_zone, Nvl(wdtv.zone, '@@@'))
8027: AND qt.ZONE = p_sign_on_zone -- removed NVL, bug 2648133
8028: AND NVL(qt.cartonization_id, -999) = NVL(p_cartonization_id, NVL(qt.cartonization_id, -999))
8029: AND NVL(e.equipment_id, -999) = NVL(l_sign_on_equipment_id, NVL(e.equipment_id, -999))
8058: ,v.role person_resource_id
8059: ,v.equipment machine_resource_id
8060: FROM --wms_dispatchable_tasks_v wdtv,
8061: (SELECT transaction_temp_id task_id
8062: , standard_operation_id user_task_type_id
8063: , wms_task_type wms_task_type_id
8064: , organization_id organization_id
8065: , subinventory_code ZONE
8066: , locator_id locator_id
8085: ) --Added for task planning WB. bug#2651318
8086: ) wdtv
8087: , -- inlined wms_dispatchable_tasks_v, bug 2648133
8088: --wms_person_resource_utt_v v,
8089: (SELECT utt_emp.standard_operation_id standard_operation_id
8090: , utt_emp.resource_id ROLE
8091: , utt_eqp.resource_id equipment
8092: , utt_emp.person_id emp_id
8093: , utt_eqp.inventory_item_id eqp_id
8093: , utt_eqp.inventory_item_id eqp_id
8094: , NULL eqp_srl /* removed for bug 2095237 */
8095: , utt_emp.effective_start_date
8096: , utt_emp.effective_end_date
8097: FROM (SELECT x_utt_res1.standard_operation_id standard_operation_id
8098: , x_utt_res1.resource_id resource_id
8099: , x_emp_r.person_id
8100: , x_emp_r.effective_start_date
8101: , x_emp_r.effective_end_date
8102: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
8103: WHERE x_utt_res1.resource_id = r1.resource_id
8104: AND r1.resource_type = 2
8105: AND x_utt_res1.resource_id = x_emp_r.resource_id) utt_emp
8106: , (SELECT x_utt_res2.standard_operation_id standard_operation_id
8107: , x_utt_res2.resource_id resource_id
8108: , x_eqp_r.inventory_item_id inventory_item_id
8109: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
8110: WHERE x_utt_res2.resource_id = r2.resource_id
8109: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
8110: WHERE x_utt_res2.resource_id = r2.resource_id
8111: AND r2.resource_type = 1
8112: AND x_utt_res2.resource_id = x_eqp_r.resource_id) utt_eqp
8113: WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)) v
8114: , -- inlined wms_person_resource_utt_v, bug 2648133
8115: mtl_item_locations_kfv loc
8116: , --changed to kfv bug#2742611
8117: mtl_secondary_inventories sub
8126: AND wdtv.wms_task_type_id = 1 -- restrict to picking tasks
8127: AND mol.header_id = moh.header_id
8128: AND moh.move_order_type = 3 -- only pick wave move orders are considered
8129: AND wdtv.user_task_type_id =
8130: v.standard_operation_id -- join task to resource view, check if user defined task type match
8131: --AND Nvl(wdtv.zone, '@@@') = Nvl(p_sign_on_zone, Nvl(wdtv.zone, '@@@'))
8132: AND wdtv.ZONE = p_sign_on_zone -- removed NVL, bug 2648133
8133: AND NVL(wdtv.cartonization_id, -999) = NVL(p_cartonization_id, NVL(wdtv.cartonization_id, -999))
8134: AND NVL(v.eqp_id, -999) = NVL(l_sign_on_equipment_id, NVL(v.eqp_id, -999))
8215: , wdt.person_resource_id
8216: , wdt.machine_resource_id
8217: FROM wms_dispatched_tasks wdt,
8218: (SELECT transaction_temp_id task_id
8219: , standard_operation_id user_task_type_id
8220: , wms_task_type wms_task_type_id
8221: , organization_id organization_id
8222: , subinventory_code ZONE
8223: , locator_id locator_id
8239: OR wms_task_status = 1
8240: ) --Added for task planning WB. bug#2651318
8241: ) wdtv
8242: , (SELECT
8243: bsor.standard_operation_id,
8244: bre.resource_id,
8245: bre.inventory_item_id equipment_id
8246: FROM bom_std_op_resources bsor, bom_resources br, bom_resource_equipments bre
8247: WHERE bsor.resource_id = bre.resource_id
8259: AND wdt.organization_id = p_sign_on_org_id
8260: AND wdtv.wms_task_type_id = 1 -- restrict to picking tasks
8261: AND mol.header_id = moh.header_id
8262: AND moh.move_order_type = 5 -- only WIP jobs are considered : Bug 2666620 BackFlush Removed
8263: AND wdtv.user_task_type_id = e.standard_operation_id(+) -- join task to resource view, check if user defined task type match
8264: AND wdtv.ZONE = p_sign_on_zone -- removed NVL, bug 2648133
8265: AND NVL(wdtv.cartonization_id, -999) = NVL(p_cartonization_id, NVL(wdtv.cartonization_id, -999))
8266: AND NVL(e.equipment_id, -999) = NVL(l_sign_on_equipment_id, NVL(e.equipment_id, -999))
8267: AND wdtv.organization_id = loc.organization_id
8292: ,v.role person_resource_id
8293: ,v.equipment machine_resource_id
8294: FROM --wms_dispatchable_tasks_v wdtv,
8295: (SELECT transaction_temp_id task_id
8296: , standard_operation_id user_task_type_id
8297: , wms_task_type wms_task_type_id
8298: , organization_id organization_id
8299: , subinventory_code ZONE
8300: , locator_id locator_id
8317: ) --Added for task planning WB. bug#2651318
8318: ) wdtv
8319: , -- inlined wms_dispatchable_tasks_v, bug 2648133
8320: --wms_person_resource_utt_v v,
8321: (SELECT utt_emp.standard_operation_id standard_operation_id
8322: , utt_emp.resource_id ROLE
8323: , utt_eqp.resource_id equipment
8324: , utt_emp.person_id emp_id
8325: , utt_eqp.inventory_item_id eqp_id
8325: , utt_eqp.inventory_item_id eqp_id
8326: , NULL eqp_srl /* removed for bug 2095237 */
8327: , utt_emp.effective_start_date
8328: , utt_emp.effective_end_date
8329: FROM (SELECT x_utt_res1.standard_operation_id standard_operation_id
8330: , x_utt_res1.resource_id resource_id
8331: , x_emp_r.person_id
8332: , x_emp_r.effective_start_date
8333: , x_emp_r.effective_end_date
8334: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
8335: WHERE x_utt_res1.resource_id = r1.resource_id
8336: AND r1.resource_type = 2
8337: AND x_utt_res1.resource_id = x_emp_r.resource_id) utt_emp
8338: , (SELECT x_utt_res2.standard_operation_id standard_operation_id
8339: , x_utt_res2.resource_id resource_id
8340: , x_eqp_r.inventory_item_id inventory_item_id
8341: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
8342: WHERE x_utt_res2.resource_id = r2.resource_id
8341: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
8342: WHERE x_utt_res2.resource_id = r2.resource_id
8343: AND r2.resource_type = 1
8344: AND x_utt_res2.resource_id = x_eqp_r.resource_id) utt_eqp
8345: WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)) v
8346: , -- inlined wms_person_resource_utt_v, bug 2648133
8347: mtl_item_locations_kfv loc
8348: , --changed to kfv bug#2742611
8349: mtl_secondary_inventories sub
8354: AND wdtv.organization_id = p_sign_on_org_id
8355: AND wdtv.wms_task_type_id = 1 -- restrict to picking tasks
8356: AND mol.header_id = moh.header_id
8357: AND moh.move_order_type = 5 -- only WIP jobs are considered : Bug 2666620 BackFlush Removed
8358: AND wdtv.user_task_type_id = v.standard_operation_id -- join task to resource view, check if user defined task type match
8359: --AND Nvl(wdtv.zone, '@@@') = Nvl(p_sign_on_zone, Nvl(wdtv.zone, '@@@'))
8360: AND wdtv.ZONE = p_sign_on_zone -- removed NVL, bug 2648133
8361: AND NVL(wdtv.cartonization_id, -999) = NVL(p_cartonization_id, NVL(wdtv.cartonization_id, -999))
8362: AND NVL(v.eqp_id, -999) = NVL(l_sign_on_equipment_id, NVL(v.eqp_id, -999))
8443: , wdt.person_resource_id
8444: , wdt.machine_resource_id
8445: FROM wms_dispatched_tasks wdt,
8446: (SELECT transaction_temp_id task_id
8447: , standard_operation_id user_task_type_id
8448: , wms_task_type wms_task_type_id
8449: , organization_id organization_id
8450: , subinventory_code ZONE
8451: , locator_id locator_id
8469: OR wms_task_status = 1
8470: ) --Added for task planning WB. bug#2651318
8471: ) qt
8472: , (SELECT
8473: bsor.standard_operation_id,
8474: bre.resource_id,
8475: bre.inventory_item_id equipment_id
8476: FROM bom_std_op_resources bsor, bom_resources br, bom_resource_equipments bre
8477: WHERE bsor.resource_id = bre.resource_id
8493: AND qt.wms_task_type_id = 1 -- restrict to picking tasks
8494: AND mol.header_id = moh.header_id
8495: AND moh.move_order_type = 3 -- only pick wave move orders are considered
8496: AND qt.user_task_type_id =
8497: e.standard_operation_id(+) -- join task to resource view, check if user defined task type match
8498: --AND Nvl(wdtv.zone, '@@@') = Nvl(p_sign_on_zone, Nvl(wdtv.zone, '@@@'))
8499: AND qt.ZONE = p_sign_on_zone -- removed NVL, bug 2648133
8500: AND NVL(qt.cartonization_id, -999) = NVL(p_cartonization_id, NVL(qt.cartonization_id, -999))
8501: AND NVL(e.equipment_id, -999) = NVL(l_sign_on_equipment_id, NVL(e.equipment_id, -999))
8530: ,v.role person_resource_id
8531: ,v.equipment machine_resource_id
8532: FROM --wms_dispatchable_tasks_v wdtv,
8533: (SELECT transaction_temp_id task_id
8534: , standard_operation_id user_task_type_id
8535: , wms_task_type wms_task_type_id
8536: , organization_id organization_id
8537: , subinventory_code ZONE
8538: , locator_id locator_id
8557: ) --Added for task planning WB. bug#2651318
8558: ) wdtv
8559: , -- inlined wms_dispatchable_tasks_v, bug 2648133
8560: --wms_person_resource_utt_v v,
8561: (SELECT utt_emp.standard_operation_id standard_operation_id
8562: , utt_emp.resource_id ROLE
8563: , utt_eqp.resource_id equipment
8564: , utt_emp.person_id emp_id
8565: , utt_eqp.inventory_item_id eqp_id
8565: , utt_eqp.inventory_item_id eqp_id
8566: , NULL eqp_srl /* removed for bug 2095237 */
8567: , utt_emp.effective_start_date
8568: , utt_emp.effective_end_date
8569: FROM (SELECT x_utt_res1.standard_operation_id standard_operation_id
8570: , x_utt_res1.resource_id resource_id
8571: , x_emp_r.person_id
8572: , x_emp_r.effective_start_date
8573: , x_emp_r.effective_end_date
8574: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
8575: WHERE x_utt_res1.resource_id = r1.resource_id
8576: AND r1.resource_type = 2
8577: AND x_utt_res1.resource_id = x_emp_r.resource_id) utt_emp
8578: , (SELECT x_utt_res2.standard_operation_id standard_operation_id
8579: , x_utt_res2.resource_id resource_id
8580: , x_eqp_r.inventory_item_id inventory_item_id
8581: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
8582: WHERE x_utt_res2.resource_id = r2.resource_id
8581: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
8582: WHERE x_utt_res2.resource_id = r2.resource_id
8583: AND r2.resource_type = 1
8584: AND x_utt_res2.resource_id = x_eqp_r.resource_id) utt_eqp
8585: WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)) v
8586: , -- inlined wms_person_resource_utt_v, bug 2648133
8587: mtl_item_locations_kfv loc
8588: , --changed to kfv bug#2742611
8589: mtl_secondary_inventories sub
8598: AND wdtv.wms_task_type_id = 1 -- restrict to picking tasks
8599: AND mol.header_id = moh.header_id
8600: AND moh.move_order_type = 3 -- only pick wave move orders are considered
8601: AND wdtv.user_task_type_id =
8602: v.standard_operation_id -- join task to resource view, check if user defined task type match
8603: --AND Nvl(wdtv.zone, '@@@') = Nvl(p_sign_on_zone, Nvl(wdtv.zone, '@@@'))
8604: AND wdtv.ZONE = p_sign_on_zone -- removed NVL, bug 2648133
8605: AND NVL(wdtv.cartonization_id, -999) = NVL(p_cartonization_id, NVL(wdtv.cartonization_id, -999))
8606: AND NVL(v.eqp_id, -999) = NVL(l_sign_on_equipment_id, NVL(v.eqp_id, -999))
8677: , wdt.person_resource_id
8678: , wdt.machine_resource_id
8679: FROM wms_dispatched_tasks wdt,
8680: (SELECT transaction_temp_id task_id
8681: , standard_operation_id user_task_type_id
8682: , wms_task_type wms_task_type_id
8683: , organization_id organization_id
8684: , subinventory_code ZONE
8685: , locator_id locator_id
8701: OR wms_task_status = 1
8702: ) --Added for task planning WB. bug#2651318
8703: ) wdtv
8704: , (SELECT
8705: bsor.standard_operation_id,
8706: bre.resource_id,
8707: bre.inventory_item_id equipment_id
8708: FROM bom_std_op_resources bsor, bom_resources br, bom_resource_equipments bre
8709: WHERE bsor.resource_id = bre.resource_id
8721: AND wdt.organization_id = p_sign_on_org_id
8722: AND wdtv.wms_task_type_id = 1 -- restrict to picking tasks
8723: AND mol.header_id = moh.header_id
8724: AND moh.move_order_type = 5 -- only WIP jobs are considered : Bug 2666620 BackFlush Removed
8725: AND wdtv.user_task_type_id = e.standard_operation_id(+) -- join task to resource view, check if user defined task type match
8726: AND wdtv.ZONE = p_sign_on_zone -- removed NVL, bug 2648133
8727: AND NVL(wdtv.cartonization_id, -999) = NVL(p_cartonization_id, NVL(wdtv.cartonization_id, -999))
8728: AND NVL(e.equipment_id, -999) = NVL(l_sign_on_equipment_id, NVL(e.equipment_id, -999))
8729: AND wdtv.organization_id = loc.organization_id
8756: ,v.role person_resource_id
8757: ,v.equipment machine_resource_id
8758: FROM --wms_dispatchable_tasks_v wdtv,
8759: (SELECT transaction_temp_id task_id
8760: , standard_operation_id user_task_type_id
8761: , wms_task_type wms_task_type_id
8762: , organization_id organization_id
8763: , subinventory_code ZONE
8764: , locator_id locator_id
8781: ) --Added for task planning WB. bug#2651318
8782: ) wdtv
8783: , -- inlined wms_dispatchable_tasks_v, bug 2648133
8784: --wms_person_resource_utt_v v,
8785: (SELECT utt_emp.standard_operation_id standard_operation_id
8786: , utt_emp.resource_id ROLE
8787: , utt_eqp.resource_id equipment
8788: , utt_emp.person_id emp_id
8789: , utt_eqp.inventory_item_id eqp_id
8789: , utt_eqp.inventory_item_id eqp_id
8790: , NULL eqp_srl /* removed for bug 2095237 */
8791: , utt_emp.effective_start_date
8792: , utt_emp.effective_end_date
8793: FROM (SELECT x_utt_res1.standard_operation_id standard_operation_id
8794: , x_utt_res1.resource_id resource_id
8795: , x_emp_r.person_id
8796: , x_emp_r.effective_start_date
8797: , x_emp_r.effective_end_date
8798: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
8799: WHERE x_utt_res1.resource_id = r1.resource_id
8800: AND r1.resource_type = 2
8801: AND x_utt_res1.resource_id = x_emp_r.resource_id) utt_emp
8802: , (SELECT x_utt_res2.standard_operation_id standard_operation_id
8803: , x_utt_res2.resource_id resource_id
8804: , x_eqp_r.inventory_item_id inventory_item_id
8805: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
8806: WHERE x_utt_res2.resource_id = r2.resource_id
8805: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
8806: WHERE x_utt_res2.resource_id = r2.resource_id
8807: AND r2.resource_type = 1
8808: AND x_utt_res2.resource_id = x_eqp_r.resource_id) utt_eqp
8809: WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)) v
8810: , -- inlined wms_person_resource_utt_v, bug 2648133
8811: mtl_item_locations_kfv loc
8812: , --changed to kfv bug#2742611
8813: mtl_secondary_inventories sub
8818: AND wdtv.organization_id = p_sign_on_org_id
8819: AND wdtv.wms_task_type_id = 1 -- restrict to picking tasks
8820: AND mol.header_id = moh.header_id
8821: AND moh.move_order_type = 5 -- only WIP jobs are considered : Bug 2666620 BackFlush Removed
8822: AND wdtv.user_task_type_id = v.standard_operation_id -- join task to resource view, check if user defined task type match
8823: --AND Nvl(wdtv.zone, '@@@') = Nvl(p_sign_on_zone, Nvl(wdtv.zone, '@@@'))
8824: AND wdtv.ZONE = p_sign_on_zone -- removed NVL, bug 2648133
8825: AND NVL(wdtv.cartonization_id, -999) = NVL(p_cartonization_id, NVL(wdtv.cartonization_id, -999))
8826: AND NVL(v.eqp_id, -999) = NVL(l_sign_on_equipment_id, NVL(v.eqp_id, -999))
8907: , wdt.person_resource_id
8908: , wdt.machine_resource_id
8909: FROM wms_dispatched_tasks wdt,
8910: (SELECT transaction_temp_id task_id
8911: , standard_operation_id user_task_type_id
8912: , wms_task_type wms_task_type_id
8913: , organization_id organization_id
8914: , subinventory_code ZONE
8915: , locator_id locator_id
8933: OR wms_task_status = 1
8934: ) --Added for task planning WB. bug#2651318
8935: ) qt
8936: , (SELECT
8937: bsor.standard_operation_id,
8938: bre.resource_id,
8939: bre.inventory_item_id equipment_id
8940: FROM bom_std_op_resources bsor, bom_resources br, bom_resource_equipments bre
8941: WHERE bsor.resource_id = bre.resource_id
8957: AND qt.wms_task_type_id = 1 -- restrict to picking tasks
8958: AND mol.header_id = moh.header_id
8959: AND moh.move_order_type = 3 -- only pick wave move orders are considered
8960: AND qt.user_task_type_id =
8961: e.standard_operation_id(+)
8962: AND NVL(qt.cartonization_id, -999) = NVL(p_cartonization_id, NVL(qt.cartonization_id, -999))
8963: AND NVL(e.equipment_id, -999) = NVL(l_sign_on_equipment_id, NVL(e.equipment_id, -999))
8964: AND qt.organization_id = loc.organization_id
8965: AND qt.locator_id = loc.inventory_location_id
8992: ,v.role person_resource_id
8993: ,v.equipment machine_resource_id
8994: FROM --wms_dispatchable_tasks_v wdtv,
8995: (SELECT transaction_temp_id task_id
8996: , standard_operation_id user_task_type_id
8997: , wms_task_type wms_task_type_id
8998: , organization_id organization_id
8999: , subinventory_code ZONE
9000: , locator_id locator_id
9019: ) --Added for task planning WB. bug#2651318
9020: ) wdtv
9021: , -- inlined wms_dispatchable_tasks_v, bug 2648133
9022: --wms_person_resource_utt_v v,
9023: (SELECT utt_emp.standard_operation_id standard_operation_id
9024: , utt_emp.resource_id ROLE
9025: , utt_eqp.resource_id equipment
9026: , utt_emp.person_id emp_id
9027: , utt_eqp.inventory_item_id eqp_id
9027: , utt_eqp.inventory_item_id eqp_id
9028: , NULL eqp_srl /* removed for bug 2095237 */
9029: , utt_emp.effective_start_date
9030: , utt_emp.effective_end_date
9031: FROM (SELECT x_utt_res1.standard_operation_id standard_operation_id
9032: , x_utt_res1.resource_id resource_id
9033: , x_emp_r.person_id
9034: , x_emp_r.effective_start_date
9035: , x_emp_r.effective_end_date
9036: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
9037: WHERE x_utt_res1.resource_id = r1.resource_id
9038: AND r1.resource_type = 2
9039: AND x_utt_res1.resource_id = x_emp_r.resource_id) utt_emp
9040: , (SELECT x_utt_res2.standard_operation_id standard_operation_id
9041: , x_utt_res2.resource_id resource_id
9042: , x_eqp_r.inventory_item_id inventory_item_id
9043: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
9044: WHERE x_utt_res2.resource_id = r2.resource_id
9043: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
9044: WHERE x_utt_res2.resource_id = r2.resource_id
9045: AND r2.resource_type = 1
9046: AND x_utt_res2.resource_id = x_eqp_r.resource_id) utt_eqp
9047: WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)) v
9048: , -- inlined wms_person_resource_utt_v, bug 2648133
9049: mtl_item_locations_kfv loc
9050: , --changed to kfv bug#2742611
9051: mtl_secondary_inventories sub
9060: AND wdtv.wms_task_type_id = 1 -- restrict to picking tasks
9061: AND mol.header_id = moh.header_id
9062: AND moh.move_order_type = 3 -- only pick wave move orders are considered
9063: AND wdtv.user_task_type_id =
9064: v.standard_operation_id -- join task to resource view, check if user defined task type match
9065: AND NVL(wdtv.cartonization_id, -999) = NVL(p_cartonization_id, NVL(wdtv.cartonization_id, -999))
9066: AND NVL(v.eqp_id, -999) = NVL(l_sign_on_equipment_id, NVL(v.eqp_id, -999))
9067: AND wdtv.organization_id = loc.organization_id(+) -- join task to loc, outer join for tasks do not have locator
9068: AND wdtv.locator_id = loc.inventory_location_id(+)
9146: , wdt.person_resource_id
9147: , wdt.machine_resource_id
9148: FROM wms_dispatched_tasks wdt,
9149: (SELECT transaction_temp_id task_id
9150: , standard_operation_id user_task_type_id
9151: , wms_task_type wms_task_type_id
9152: , organization_id organization_id
9153: , subinventory_code ZONE
9154: , locator_id locator_id
9170: OR wms_task_status = 1
9171: ) --Added for task planning WB. bug#2651318
9172: ) wdtv
9173: , (SELECT
9174: bsor.standard_operation_id,
9175: bre.resource_id,
9176: bre.inventory_item_id equipment_id
9177: FROM bom_std_op_resources bsor, bom_resources br, bom_resource_equipments bre
9178: WHERE bsor.resource_id = bre.resource_id
9190: AND wdt.organization_id = p_sign_on_org_id
9191: AND wdtv.wms_task_type_id = 1 -- restrict to picking tasks
9192: AND mol.header_id = moh.header_id
9193: AND moh.move_order_type = 5 -- only WIP jobs are considered : Bug 2666620 BackFlush Removed
9194: AND wdtv.user_task_type_id = e.standard_operation_id(+) -- join task to resource view, check if user defined task type match
9195: AND NVL(wdtv.cartonization_id, -999) = NVL(p_cartonization_id, NVL(wdtv.cartonization_id, -999))
9196: AND NVL(e.equipment_id, -999) = NVL(l_sign_on_equipment_id, NVL(e.equipment_id, -999))
9197: AND wdtv.organization_id = loc.organization_id
9198: AND wdtv.locator_id = loc.inventory_location_id
9224: ,v.role person_resource_id
9225: ,v.equipment machine_resource_id
9226: FROM --wms_dispatchable_tasks_v wdtv,
9227: (SELECT transaction_temp_id task_id
9228: , standard_operation_id user_task_type_id
9229: , wms_task_type wms_task_type_id
9230: , organization_id organization_id
9231: , subinventory_code ZONE
9232: , locator_id locator_id
9249: ) --Added for task planning WB. bug#2651318
9250: ) wdtv
9251: , -- inlined wms_dispatchable_tasks_v, bug 2648133
9252: --wms_person_resource_utt_v v,
9253: (SELECT utt_emp.standard_operation_id standard_operation_id
9254: , utt_emp.resource_id ROLE
9255: , utt_eqp.resource_id equipment
9256: , utt_emp.person_id emp_id
9257: , utt_eqp.inventory_item_id eqp_id
9257: , utt_eqp.inventory_item_id eqp_id
9258: , NULL eqp_srl /* removed for bug 2095237 */
9259: , utt_emp.effective_start_date
9260: , utt_emp.effective_end_date
9261: FROM (SELECT x_utt_res1.standard_operation_id standard_operation_id
9262: , x_utt_res1.resource_id resource_id
9263: , x_emp_r.person_id
9264: , x_emp_r.effective_start_date
9265: , x_emp_r.effective_end_date
9266: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
9267: WHERE x_utt_res1.resource_id = r1.resource_id
9268: AND r1.resource_type = 2
9269: AND x_utt_res1.resource_id = x_emp_r.resource_id) utt_emp
9270: , (SELECT x_utt_res2.standard_operation_id standard_operation_id
9271: , x_utt_res2.resource_id resource_id
9272: , x_eqp_r.inventory_item_id inventory_item_id
9273: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
9274: WHERE x_utt_res2.resource_id = r2.resource_id
9273: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
9274: WHERE x_utt_res2.resource_id = r2.resource_id
9275: AND r2.resource_type = 1
9276: AND x_utt_res2.resource_id = x_eqp_r.resource_id) utt_eqp
9277: WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)) v
9278: , -- inlined wms_person_resource_utt_v, bug 2648133
9279: mtl_item_locations_kfv loc
9280: , --changed to kfv bug#2742611
9281: mtl_secondary_inventories sub
9286: AND wdtv.organization_id = p_sign_on_org_id
9287: AND wdtv.wms_task_type_id = 1 -- restrict to picking tasks
9288: AND mol.header_id = moh.header_id
9289: AND moh.move_order_type = 5 -- only WIP jobs are considered : Bug 2666620 BackFlush Removed
9290: AND wdtv.user_task_type_id = v.standard_operation_id -- join task to resource view, check if user defined task type match
9291: --AND Nvl(wdtv.zone, '@@@') = Nvl(p_sign_on_zone, Nvl(wdtv.zone, '@@@'))
9292: AND NVL(wdtv.cartonization_id, -999) = NVL(p_cartonization_id, NVL(wdtv.cartonization_id, -999))
9293: AND NVL(v.eqp_id, -999) = NVL(l_sign_on_equipment_id, NVL(v.eqp_id, -999))
9294: AND wdtv.organization_id = loc.organization_id(+) -- join task to loc, outer join for tasks do not have locator
9371: , wdt.person_resource_id
9372: , wdt.machine_resource_id
9373: FROM wms_dispatched_tasks wdt,
9374: (SELECT transaction_temp_id task_id
9375: , standard_operation_id user_task_type_id
9376: , wms_task_type wms_task_type_id
9377: , organization_id organization_id
9378: , subinventory_code ZONE
9379: , locator_id locator_id
9397: OR wms_task_status = 1
9398: ) --Added for task planning WB. bug#2651318
9399: ) qt
9400: , (SELECT
9401: bsor.standard_operation_id,
9402: bre.resource_id,
9403: bre.inventory_item_id equipment_id
9404: FROM bom_std_op_resources bsor, bom_resources br, bom_resource_equipments bre
9405: WHERE bsor.resource_id = bre.resource_id
9421: AND qt.wms_task_type_id = 1 -- restrict to picking tasks
9422: AND mol.header_id = moh.header_id
9423: AND moh.move_order_type = 3 -- only pick wave move orders are considered
9424: AND qt.user_task_type_id =
9425: e.standard_operation_id(+)
9426: AND NVL(qt.cartonization_id, -999) = NVL(p_cartonization_id, NVL(qt.cartonization_id, -999))
9427: AND NVL(e.equipment_id, -999) = NVL(l_sign_on_equipment_id, NVL(e.equipment_id, -999))
9428: AND qt.organization_id = loc.organization_id
9429: AND qt.locator_id = loc.inventory_location_id
9456: ,v.role person_resource_id
9457: ,v.equipment machine_resource_id
9458: FROM --wms_dispatchable_tasks_v wdtv,
9459: (SELECT transaction_temp_id task_id
9460: , standard_operation_id user_task_type_id
9461: , wms_task_type wms_task_type_id
9462: , organization_id organization_id
9463: , subinventory_code ZONE
9464: , locator_id locator_id
9483: ) --Added for task planning WB. bug#2651318
9484: ) wdtv
9485: , -- inlined wms_dispatchable_tasks_v, bug 2648133
9486: --wms_person_resource_utt_v v,
9487: (SELECT utt_emp.standard_operation_id standard_operation_id
9488: , utt_emp.resource_id ROLE
9489: , utt_eqp.resource_id equipment
9490: , utt_emp.person_id emp_id
9491: , utt_eqp.inventory_item_id eqp_id
9491: , utt_eqp.inventory_item_id eqp_id
9492: , NULL eqp_srl /* removed for bug 2095237 */
9493: , utt_emp.effective_start_date
9494: , utt_emp.effective_end_date
9495: FROM (SELECT x_utt_res1.standard_operation_id standard_operation_id
9496: , x_utt_res1.resource_id resource_id
9497: , x_emp_r.person_id
9498: , x_emp_r.effective_start_date
9499: , x_emp_r.effective_end_date
9500: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
9501: WHERE x_utt_res1.resource_id = r1.resource_id
9502: AND r1.resource_type = 2
9503: AND x_utt_res1.resource_id = x_emp_r.resource_id) utt_emp
9504: , (SELECT x_utt_res2.standard_operation_id standard_operation_id
9505: , x_utt_res2.resource_id resource_id
9506: , x_eqp_r.inventory_item_id inventory_item_id
9507: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
9508: WHERE x_utt_res2.resource_id = r2.resource_id
9507: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
9508: WHERE x_utt_res2.resource_id = r2.resource_id
9509: AND r2.resource_type = 1
9510: AND x_utt_res2.resource_id = x_eqp_r.resource_id) utt_eqp
9511: WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)) v
9512: , -- inlined wms_person_resource_utt_v, bug 2648133
9513: mtl_item_locations_kfv loc
9514: , --changed to kfv bug#2742611
9515: mtl_secondary_inventories sub
9524: AND wdtv.wms_task_type_id = 1 -- restrict to picking tasks
9525: AND mol.header_id = moh.header_id
9526: AND moh.move_order_type = 3 -- only pick wave move orders are considered
9527: AND wdtv.user_task_type_id =
9528: v.standard_operation_id -- join task to resource view, check if user defined task type match
9529: --AND Nvl(wdtv.zone, '@@@') = Nvl(p_sign_on_zone, Nvl(wdtv.zone, '@@@'))
9530: AND NVL(wdtv.cartonization_id, -999) = NVL(p_cartonization_id, NVL(wdtv.cartonization_id, -999))
9531: AND NVL(v.eqp_id, -999) = NVL(l_sign_on_equipment_id, NVL(v.eqp_id, -999))
9532: AND wdtv.organization_id = loc.organization_id(+) -- join task to loc, outer join for tasks do not have locator
9602: , wdt.person_resource_id
9603: , wdt.machine_resource_id
9604: FROM wms_dispatched_tasks wdt,
9605: (SELECT transaction_temp_id task_id
9606: , standard_operation_id user_task_type_id
9607: , wms_task_type wms_task_type_id
9608: , organization_id organization_id
9609: , subinventory_code ZONE
9610: , locator_id locator_id
9626: OR wms_task_status = 1
9627: ) --Added for task planning WB. bug#2651318
9628: ) wdtv
9629: , (SELECT
9630: bsor.standard_operation_id,
9631: bre.resource_id,
9632: bre.inventory_item_id equipment_id
9633: FROM bom_std_op_resources bsor, bom_resources br, bom_resource_equipments bre
9634: WHERE bsor.resource_id = bre.resource_id
9646: AND wdt.organization_id = p_sign_on_org_id
9647: AND wdtv.wms_task_type_id = 1 -- restrict to picking tasks
9648: AND mol.header_id = moh.header_id
9649: AND moh.move_order_type = 5 -- only WIP jobs are considered : Bug 2666620 BackFlush Removed
9650: AND wdtv.user_task_type_id = e.standard_operation_id(+) -- join task to resource view, check if user defined task type match
9651: AND NVL(wdtv.cartonization_id, -999) = NVL(p_cartonization_id, NVL(wdtv.cartonization_id, -999))
9652: AND NVL(e.equipment_id, -999) = NVL(l_sign_on_equipment_id, NVL(e.equipment_id, -999))
9653: AND wdtv.organization_id = loc.organization_id
9654: AND wdtv.locator_id = loc.inventory_location_id
9680: ,v.role person_resource_id
9681: ,v.equipment machine_resource_id
9682: FROM --wms_dispatchable_tasks_v wdtv,
9683: (SELECT transaction_temp_id task_id
9684: , standard_operation_id user_task_type_id
9685: , wms_task_type wms_task_type_id
9686: , organization_id organization_id
9687: , subinventory_code ZONE
9688: , locator_id locator_id
9705: ) --Added for task planning WB. bug#2651318
9706: ) wdtv
9707: , -- inlined wms_dispatchable_tasks_v, bug 2648133
9708: --wms_person_resource_utt_v v,
9709: (SELECT utt_emp.standard_operation_id standard_operation_id
9710: , utt_emp.resource_id ROLE
9711: , utt_eqp.resource_id equipment
9712: , utt_emp.person_id emp_id
9713: , utt_eqp.inventory_item_id eqp_id
9713: , utt_eqp.inventory_item_id eqp_id
9714: , NULL eqp_srl /* removed for bug 2095237 */
9715: , utt_emp.effective_start_date
9716: , utt_emp.effective_end_date
9717: FROM (SELECT x_utt_res1.standard_operation_id standard_operation_id
9718: , x_utt_res1.resource_id resource_id
9719: , x_emp_r.person_id
9720: , x_emp_r.effective_start_date
9721: , x_emp_r.effective_end_date
9722: FROM bom_std_op_resources x_utt_res1, bom_resources r1, bom_resource_employees x_emp_r
9723: WHERE x_utt_res1.resource_id = r1.resource_id
9724: AND r1.resource_type = 2
9725: AND x_utt_res1.resource_id = x_emp_r.resource_id) utt_emp
9726: , (SELECT x_utt_res2.standard_operation_id standard_operation_id
9727: , x_utt_res2.resource_id resource_id
9728: , x_eqp_r.inventory_item_id inventory_item_id
9729: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
9730: WHERE x_utt_res2.resource_id = r2.resource_id
9729: FROM bom_std_op_resources x_utt_res2, bom_resources r2, bom_resource_equipments x_eqp_r
9730: WHERE x_utt_res2.resource_id = r2.resource_id
9731: AND r2.resource_type = 1
9732: AND x_utt_res2.resource_id = x_eqp_r.resource_id) utt_eqp
9733: WHERE utt_emp.standard_operation_id = utt_eqp.standard_operation_id(+)) v
9734: , -- inlined wms_person_resource_utt_v, bug 2648133
9735: mtl_item_locations_kfv loc
9736: , --changed to kfv bug#2742611
9737: mtl_secondary_inventories sub
9742: AND wdtv.organization_id = p_sign_on_org_id
9743: AND wdtv.wms_task_type_id = 1 -- restrict to picking tasks
9744: AND mol.header_id = moh.header_id
9745: AND moh.move_order_type = 5 -- only WIP jobs are considered : Bug 2666620 BackFlush Removed
9746: AND wdtv.user_task_type_id = v.standard_operation_id -- join task to resource view, check if user defined task type match
9747: --AND Nvl(wdtv.zone, '@@@') = Nvl(p_sign_on_zone, Nvl(wdtv.zone, '@@@'))
9748: AND NVL(wdtv.cartonization_id, -999) = NVL(p_cartonization_id, NVL(wdtv.cartonization_id, -999))
9749: AND NVL(v.eqp_id, -999) = NVL(l_sign_on_equipment_id, NVL(v.eqp_id, -999))
9750: AND wdtv.organization_id = loc.organization_id(+) -- join task to loc, outer join for tasks do not have locator
9872: WHERE wdtv.organization_id = ' || p_sign_on_org_id ||'
9873: AND Nvl(wdtv.zone, ''@@@'') = Nvl('''|| p_sign_on_zone || ''', Nvl(wdtv.zone, ''@@@''))
9874: AND wdtv.user_task_type_id IN
9875: (
9876: SELECT standard_operation_id
9877: FROM wms_person_resource_utt_v v
9878: WHERE v.emp_id = ' || p_sign_on_emp_id ||'
9879: AND Nvl(v.eqp_srl, ''@@@'') = Nvl(''' || l_sign_on_equipment_srl || ''', Nvl(v.eqp_srl, ''@@@''))
9880: AND Nvl(v.eqp_id, -999) = Nvl(' || l_equipment_id_str ||', Nvl(v.eqp_id, -999))
10655: , '' lot_number
10656: , mmtt.wms_task_type wms_task_type_id
10657: , mmtt.task_priority task_priority
10658: , mmtt.operation_plan_id,
10659: mmtt.standard_operation_id,
10660: wot.effective_start_date,
10661: wot.effective_end_date,
10662: wot.person_resource_id,
10663: wot.machine_resource_id,
10888: END IF;
10889: END split_tasks;
10890:
10891: FUNCTION is_equipment_cap_exceeded(
10892: p_standard_operation_id IN NUMBER
10893: , p_item_id IN NUMBER
10894: , p_organization_id IN NUMBER
10895: , p_txn_qty IN NUMBER
10896: , p_txn_uom_code IN VARCHAR2
10932: , mtl_system_items item
10933: , bom_resource_equipments res_equip
10934: , bom_resources res
10935: , bom_std_op_resources tt_x_res
10936: WHERE tt_x_res.standard_operation_id = p_standard_operation_id --join task with task_type-resource x-ref
10937: AND tt_x_res.resource_id = res.resource_id -- join with resource
10938: AND res.resource_type = 1 -- resource type for equipment
10939: AND res_equip.resource_id = tt_x_res.resource_id -- join with resource-equip x-ref
10940: AND equip.inventory_item_id = res_equip.inventory_item_id -- join with equipment (mtl_system_items)
11118: l_to_org_id NUMBER;
11119: l_to_sub VARCHAR2(30);
11120: l_to_loc_id NUMBER;
11121: l_wms_task_type NUMBER;
11122: l_standard_operation_id NUMBER;
11123: l_task_priority NUMBER;
11124: l_cost_group_id NUMBER;
11125: l_transaction_header_id NUMBER;
11126: l_container_item_id NUMBER;
11160: , mmtt.transfer_organization
11161: , mmtt.transfer_subinventory
11162: , mmtt.transfer_to_location
11163: , mmtt.wms_task_type
11164: , mmtt.standard_operation_id
11165: , MAX(mmtt.task_priority)
11166: , mmtt.cost_group_id
11167: , MAX(mmtt.transaction_header_id)
11168: , mmtt.container_item_id
11209: , mmtt.transfer_organization
11210: , mmtt.transfer_subinventory
11211: , mmtt.transfer_to_location
11212: , mmtt.wms_task_type
11213: , mmtt.standard_operation_id
11214: , mmtt.cost_group_id
11215: , mmtt.container_item_id
11216: , mmtt.cartonization_id
11217: , mmtt.operation_plan_id
11224: , mmtt.serial_allocated_flag
11225: -- Bug 4584538
11226: HAVING SUM(mmtt.transaction_quantity) <> MIN(mmtt.transaction_quantity) -- make sure one line will not get consolidated
11227: AND 'Y' <> is_equipment_cap_exceeded(
11228: mmtt.standard_operation_id
11229: , mmtt.inventory_item_id
11230: , mmtt.organization_id
11231: , SUM(mmtt.transaction_quantity)
11232: , mmtt.transaction_uom
11255: , mmtt.transfer_organization
11256: , ''
11257: , NULL
11258: , mmtt.wms_task_type
11259: , mmtt.standard_operation_id
11260: , MAX(mmtt.task_priority)
11261: , mmtt.cost_group_id
11262: , MAX(mmtt.transaction_header_id)
11263: , mmtt.container_item_id
11302: , mmtt.transaction_action_id
11303: , mmtt.transaction_source_type_id
11304: , mmtt.transfer_organization
11305: , mmtt.wms_task_type
11306: , mmtt.standard_operation_id
11307: , mmtt.cost_group_id
11308: , mmtt.container_item_id
11309: , mmtt.cartonization_id
11310: , mmtt.operation_plan_id
11316: , mmtt.serial_allocated_flag
11317: -- Bug 4584538
11318: HAVING SUM(mmtt.transaction_quantity) <> MIN(mmtt.transaction_quantity) -- make sure one line will not get consolidated
11319: AND 'Y' <> is_equipment_cap_exceeded(
11320: mmtt.standard_operation_id
11321: , mmtt.inventory_item_id
11322: , mmtt.organization_id
11323: , SUM(mmtt.transaction_quantity)
11324: , mmtt.transaction_uom
11370: , l_to_org_id
11371: , l_to_sub
11372: , l_to_loc_id
11373: , l_wms_task_type
11374: , l_standard_operation_id
11375: , l_task_priority
11376: , l_cost_group_id
11377: , l_transaction_header_id
11378: , l_container_item_id
11410: , l_to_org_id
11411: , l_to_sub
11412: , l_to_loc_id
11413: , l_wms_task_type
11414: , l_standard_operation_id
11415: , l_task_priority
11416: , l_cost_group_id
11417: , l_transaction_header_id
11418: , l_container_item_id
11494: , acct_period_id
11495: , cost_group_id
11496: -- , move_order_line_id keep same as patchset J
11497: , pick_slip_number
11498: , standard_operation_id
11499: , wms_task_type
11500: , task_priority
11501: , container_item_id
11502: , cartonization_id
11538: , l_acct_period_id
11539: , l_cost_group_id
11540: -- , l_move_order_line_id keep same as patchset J
11541: , l_pick_slip_number
11542: , l_standard_operation_id
11543: , l_wms_task_type
11544: , l_task_priority
11545: , l_container_item_id
11546: , l_cartonization_id
11651: l_to_org_id NUMBER;
11652: l_to_sub VARCHAR2(30);
11653: l_to_loc_id NUMBER;
11654: l_wms_task_type NUMBER;
11655: l_standard_operation_id NUMBER;
11656: l_task_priority NUMBER;
11657: l_cost_group_id NUMBER;
11658: l_transaction_header_id NUMBER;
11659: l_container_item_id NUMBER;
12527: , bom_resource_equipments res_equip
12528: , bom_resources res
12529: , bom_std_op_resources tt_x_res
12530: WHERE mmtt.transaction_temp_id = p_task_id -- the task in question
12531: AND mmtt.standard_operation_id = tt_x_res.standard_operation_id --join task with task_type-resource x-ref
12532: AND tt_x_res.resource_id = res.resource_id -- join with resource
12533: AND res.resource_type = 1 -- resource type for equipment
12534: AND res_equip.resource_id = tt_x_res.resource_id -- join with resource-equip x-ref
12535: AND equip.inventory_item_id = res_equip.inventory_item_id -- join with equipment (mtl_system_items)
12564: , bom_resource_equipments res_equip
12565: , bom_resources res
12566: , bom_std_op_resources tt_x_res
12567: WHERE mmtt.transaction_temp_id = p_task_id -- the task in question
12568: AND mmtt.standard_operation_id = tt_x_res.standard_operation_id --join task with task_type-resource x-ref
12569: AND tt_x_res.resource_id = res.resource_id -- join with resource
12570: AND res.resource_type = 1 -- resource type for equipment
12571: AND res_equip.resource_id = tt_x_res.resource_id -- join with resource-equip x-ref
12572: AND equip.inventory_item_id = res_equip.inventory_item_id -- join with equipment (mtl_system_items)
13435: , common_routing_seq_id
13436: , org_cost_group_id
13437: , cost_type_id
13438: , transaction_status
13439: , standard_operation_id
13440: , task_priority
13441: , wms_task_type
13442: , parent_line_id
13443: , lpn_id
13667: , l_mmtt_rec.common_routing_seq_id
13668: , l_mmtt_rec.org_cost_group_id
13669: , l_mmtt_rec.cost_type_id
13670: , l_mmtt_rec.transaction_status
13671: , l_mmtt_rec.standard_operation_id
13672: , l_mmtt_rec.task_priority
13673: , l_mmtt_rec.wms_task_type
13674: , l_mmtt_rec.parent_line_id
13675: , l_mmtt_rec.lpn_id
13905: , common_routing_seq_id
13906: , org_cost_group_id
13907: , cost_type_id
13908: , transaction_status
13909: , standard_operation_id
13910: , task_priority
13911: , wms_task_type
13912: , parent_line_id
13913: , lpn_id
14137: , l_wct_rec.common_routing_seq_id
14138: , l_wct_rec.org_cost_group_id
14139: , l_wct_rec.cost_type_id
14140: , l_wct_rec.transaction_status
14141: , l_wct_rec.standard_operation_id
14142: , l_wct_rec.task_priority
14143: , l_wct_rec.wms_task_type
14144: , l_wct_rec.parent_line_id
14145: , l_wct_rec.lpn_id