[Home] [Help]
264: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
265: BEGIN
266: x_return_status := fnd_api.g_ret_sts_success;
267:
268: UPDATE wms_dispatched_tasks
269: SET status = 9
270: WHERE transaction_temp_id = p_transaction_temp_id;
271:
272: COMMIT;
443: , l_allocated_lpn_id
444: , l_transaction_action_id
445: , l_is_bulk_picked_task
446: , l_serial_alloc_flag -- Bug 4434111
447: FROM mtl_material_transactions_temp mmtt, wms_dispatched_tasks wdt
448: WHERE wdt.person_id = p_sign_on_emp_id
449: AND wdt.organization_id = p_sign_on_org_id
450: AND wdt.status <= 3
451: AND wdt.task_type IN (1, 4, 5, 6)
485: , mmtt.inventory_item_id inventory_item_id
486: , mmtt.cartonization_id cartonization_id
487: , mmtt.allocated_lpn_id allocated_lpn_id
488: , mmtt.serial_allocated_flag serial_allocated_flag
489: FROM mtl_material_transactions_temp mmtt, wms_dispatched_tasks wdt, mtl_item_locations loc, mtl_secondary_inventories sub
490: WHERE wdt.person_id = p_sign_on_emp_id
491: AND wdt.organization_id = p_sign_on_org_id
492: AND wdt.status <= 3
493: AND (wdt.task_type IN (1, 4, 5, 6))
536: , mmtt.inventory_item_id inventory_item_id
537: , mmtt.cartonization_id cartonization_id
538: , mmtt.allocated_lpn_id allocated_lpn_id
539: , mmtt.serial_allocated_flag serial_allocated_flag
540: FROM mtl_material_transactions_temp mmtt, wms_dispatched_tasks wdt, mtl_item_locations loc, mtl_secondary_inventories sub
541: WHERE wdt.person_id = p_sign_on_emp_id
542: AND wdt.organization_id = p_sign_on_org_id
543: AND wdt.status <= 3
544: AND (wdt.task_type IN (1, 4, 5, 6))
587: , mmtt.inventory_item_id inventory_item_id
588: , mmtt.cartonization_id cartonization_id
589: , mmtt.allocated_lpn_id allocated_lpn_id
590: , mmtt.serial_allocated_flag serial_allocated_flag
591: FROM mtl_material_transactions_temp mmtt, wms_dispatched_tasks wdt, mtl_item_locations loc, mtl_secondary_inventories sub
592: WHERE wdt.person_id = p_sign_on_emp_id
593: AND wdt.organization_id = p_sign_on_org_id
594: AND wdt.status <= 3
595: AND (wdt.task_type IN (1, 4, 5, 6))
1350: , l_honor_case_pick_flag --69
1351: , l_template_name --70
1352: , l_allocate_lot_flag --71 -- Added for 14699845 (Flexible Lot Allocation)
1353: , NVL(mmtt.fulfillment_base, 'P') -- MUOM:SK (72)
1354: FROM wms_dispatched_tasks wdt
1355: , mtl_material_transactions_temp mmtt
1356: , mtl_system_items_vl msik /* Bug 5581528 */
1357: , mtl_parameters mp
1358: , mtl_txn_request_lines mtrl
1472: /* Added for LMS project */
1473: , wdt.user_task_type
1474: , mmtt.operation_plan_id
1475: /* end for LMS project */
1476: FROM wms_dispatched_tasks wdt
1477: , mtl_material_transactions_temp mmtt
1478: , mtl_system_items_vl msik /* Bug 5581528 */
1479: , mtl_parameters mp
1480: , mtl_txn_request_lines mtrl
1747: , mmtt.transfer_to_location --Bug#2756609
1748: FROM mtl_material_transactions_temp mmtt
1749: , mtl_secondary_inventories msi
1750: , mtl_item_locations mil
1751: , wms_dispatched_tasks wdt
1752: , mtl_system_items_vl msik /* Bug 5581528 */
1753: WHERE mmtt.organization_id = p_organization_id
1754: AND mmtt.transfer_lpn_id = p_transfer_lpn_id
1755: AND mmtt.transaction_source_type_id = inv_globals.g_sourcetype_inventory
1796: , mtrl.txn_source_line_id operation_seq_num
1797: , to_number(NULL) transfer_to_location --Bug#2756609
1798: FROM mtl_material_transactions_temp mmtt
1799: , mtl_txn_request_lines mtrl
1800: , wms_dispatched_tasks wdt
1801: , mtl_system_items_vl msik /* Bug 5581528 */
1802: WHERE mmtt.organization_id = p_organization_id
1803: AND mmtt.transfer_lpn_id = p_transfer_lpn_id
1804: AND mmtt.transaction_source_type_id = inv_globals.g_sourcetype_wip
1848: , inv_project.get_project_id
1849: , inv_project.get_project_number
1850: , inv_project.get_task_id
1851: , inv_project.get_task_number
1852: FROM wms_dispatched_tasks wdt, mtl_material_transactions_temp mmtt, mtl_system_items_vl msik /* Bug 5581528 */
1853: WHERE wdt.organization_id = p_organization_id
1854: AND wdt.status IN (3, 4)
1855: AND wdt.task_type IN (1, 4, 5, 7)
1856: AND wdt.transaction_temp_id = mmtt.transaction_temp_id
2116: l_user_id NUMBER;
2117: l_mmtt_rowcnt NUMBER;
2118: l_wdt_rowcnt NUMBER;
2119: l_undispatched_picks NUMBER;
2120: l_equipment_serial WMS_DISPATCHED_TASKS.EQUIPMENT_INSTANCE%TYPE;
2121: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
2122: l_status NUMBER ; --Bug#5157839.
2123:
2124: /*6009436 Begin */
2153: mydebug('MANUAL_PICK: In Manual Pick API', 'MANUAL_PICK');
2154: mydebug('p_allow_unreleased_task is : ' || p_allow_unreleased_task, 'MANUAL_PICK');
2155: END IF;
2156:
2157: -- Bug #4090630 - inserting NULL into WMS_DISPATCHED_TASKS.equipement_instance
2158: -- if p_equipment_serial is NONE
2159: l_equipment_serial := p_equipment_serial;
2160: IF (p_equipment_serial = 'NONE') then
2161: IF (l_debug = 1) THEN
2221: SELECT 1
2222: INTO l_wdt_rowcnt
2223: FROM dual
2224: WHERE exists (SELECT 1
2225: FROM wms_dispatched_tasks t
2226: WHERE t.transaction_temp_id = p_transaction_temp_id
2227: AND person_id <> p_employee_id);
2228: EXCEPTION
2229: WHEN NO_DATA_FOUND THEN
2239: fnd_msg_pub.ADD;
2240: RAISE fnd_api.g_exc_error;
2241: ELSE
2242: BEGIN --bug#5157839.Start fix
2243: SELECT wdt.status INTO l_status FROM wms_dispatched_tasks wdt
2244: WHERE wdt.transaction_temp_id = p_transaction_temp_id;
2245:
2246: IF l_status NOT IN (1,2,3) THEN
2247: IF (l_debug = 1) THEN
2287: SELECT 1
2288: INTO l_wdt_rowcnt
2289: FROM dual
2290: WHERE exists (SELECT 1
2291: FROM wms_dispatched_tasks t
2292: WHERE t.transaction_temp_id = p_transaction_temp_id);
2293:
2294: g_previous_task_status(p_transaction_temp_id) := 2;
2295: EXCEPTION
2424: END ;
2425: END IF;
2426: --Bug6009436.End
2427:
2428: -- Insert into WMS_DISPATCHED_TASKS for this user
2429: INSERT INTO wms_dispatched_tasks
2430: (task_id,
2431: transaction_temp_id,
2432: organization_id,
2425: END IF;
2426: --Bug6009436.End
2427:
2428: -- Insert into WMS_DISPATCHED_TASKS for this user
2429: INSERT INTO wms_dispatched_tasks
2430: (task_id,
2431: transaction_temp_id,
2432: organization_id,
2433: user_task_type,
2447: task_type,
2448: operation_plan_id,
2449: move_order_line_id)
2450: VALUES
2451: (wms_dispatched_tasks_s.NEXTVAL,
2452: p_transaction_temp_id,
2453: p_organization_id,
2454: l_standard_operation_id,
2455: p_employee_id,
2521: IF p_grouping_document_type = 'PICK_SLIP' THEN
2522:
2523: SELECT wdt.task_id, mmtt.transaction_temp_id, wdt.task_type
2524: INTO x_task_id, x_transaction_temp_id, x_task_type_id
2525: FROM wms_dispatched_tasks wdt,
2526: mtl_material_transactions_temp mmtt
2527: WHERE wdt.person_id = p_employee_id
2528: AND wdt.organization_id = p_organization_id
2529: AND wdt.task_type in (1,4,5,6) -- Picking,Replenishment bug#8770642 BUG13985098
2538: ELSIF p_grouping_document_type = 'ORDER' THEN
2539:
2540: SELECT wdt.task_id, mmtt.transaction_temp_id, wdt.task_type
2541: INTO x_task_id, x_transaction_temp_id, x_task_type_id
2542: FROM wms_dispatched_tasks wdt,
2543: mtl_material_transactions_temp mmtt
2544: WHERE wdt.person_id = p_employee_id
2545: AND wdt.organization_id = p_organization_id
2546: AND wdt.task_type = 1 -- Picking
2557: /* bug9507152 */
2558: SELECT task_id, transaction_temp_id, task_type
2559: INTO x_task_id, x_transaction_temp_id, x_task_type_id
2560: FROM (SELECT wdt.task_id, mmtt.transaction_temp_id, wdt.task_type
2561: FROM wms_dispatched_tasks wdt,
2562: mtl_material_transactions_temp mmtt,
2563: mtl_secondary_inventories msi,
2564: mtl_item_locations mil
2565: WHERE wdt.person_id = p_employee_id
2584: ELSIF p_grouping_document_type = 'CLUSTERPICKBYLABEL' THEN
2585:
2586: SELECT wdt.task_id, mmtt.transaction_temp_id, wdt.task_type
2587: INTO x_task_id, x_transaction_temp_id, x_task_type_id
2588: FROM wms_dispatched_tasks wdt,
2589: mtl_material_transactions_temp mmtt
2590: WHERE wdt.person_id = p_employee_id
2591: AND wdt.organization_id = p_organization_id
2592: AND wdt.task_type = 1 -- Picking
2602:
2603: SELECT task_id, transaction_temp_id, task_type
2604: INTO x_task_id, x_transaction_temp_id, x_task_type_id
2605: FROM (select wdt.task_id, mmtt.transaction_temp_id, wdt.task_type
2606: FROM wms_dispatched_tasks wdt,
2607: mtl_material_transactions_temp mmtt,
2608: mtl_secondary_inventories msi,
2609: mtl_item_locations mil
2610: WHERE wdt.person_id = p_employee_id
2638: FROM (
2639: SELECT wdt.task_id ,
2640: mmtt.transaction_temp_id,
2641: wdt.task_type
2642: FROM wms_dispatched_tasks wdt ,
2643: mtl_material_transactions_temp mmtt,
2644: mtl_secondary_inventories msi ,
2645: mtl_item_locations mil
2646: WHERE wdt.person_id = p_employee_id
2685: FROM (
2686: SELECT wdt.task_id ,
2687: mmtt.transaction_temp_id,
2688: wdt.task_type
2689: FROM wms_dispatched_tasks wdt ,
2690: mtl_material_transactions_temp mmtt,
2691: mtl_secondary_inventories msi ,
2692: mtl_item_locations mil
2693: WHERE wdt.person_id = p_employee_id
2757: IS
2758: l_cartonization_id NUMBER;
2759: l_device_id NUMBER := p_device_id;
2760: l_equipment_id NUMBER := p_equipment_id;
2761: l_equipment_serial WMS_DISPATCHED_TASKS.EQUIPMENT_INSTANCE%TYPE;
2762: l_subinventory_code VARCHAR2(10) := p_subinventory_code;
2763: l_assignment_temp_id NUMBER;
2764: l_loop_device_id NUMBER;
2765:
2824: ORDER BY wdat.assignment_temp_id;
2825:
2826: -- Following cursor is for 4507435, to count the dispatched cluster size
2827: CURSOR cluster_size_wdt IS SELECT mmtt.transaction_source_id, count (*)
2828: FROM wms_dispatched_tasks wdt, mtl_material_transactions_temp mmtt
2829: WHERE mmtt.transaction_temp_id = wdt.transaction_temp_id
2830: AND wdt.status = 3
2831: AND wdt.person_id = p_employee_id
2832: AND wdt.organization_id = p_organization_id
3051:
3052: -- Bug#5185031 Fetched the value for l_max_seq_number.
3053: select wdt.task_method, count(*), max(wdt.task_group_id)
3054: into l_task_method_wdt, l_count_wdt ,l_max_seq_number
3055: from wms_dispatched_tasks wdt, mtl_material_transactions_temp mmtt
3056: where mmtt.transaction_temp_id = wdt.transaction_temp_id
3057: and wdt.status = 3
3058: and wdt.person_id = p_employee_id
3059: and wdt.organization_id = p_organization_id
3121: From
3122: ( select mmtt.transaction_source_id, mmtt.transaction_source_type_id, wdt.device_id,
3123: wdt.task_group_id, mmtt.pick_slip_number, mmtt.cartonization_id
3124:
3125: from mtl_material_transactions_temp mmtt, wms_dispatched_tasks wdt
3126: where mmtt.transaction_temp_id = wdt.transaction_temp_id
3127: and wdt.status = 3
3128: and wdt.person_id = p_employee_id
3129: and wdt.organization_id = p_organization_id
3304: SELECT transaction_temp_id, task_type,
3305: device_invoked, device_request_id
3306: INTO l_transaction_temp_id, x_task_type_id,
3307: l_device_invoked, l_request_id
3308: FROM wms_dispatched_tasks
3309: WHERE person_id = p_employee_id
3310: AND organization_id = p_organization_id
3311: AND device_id = l_loop_device_id
3312: AND task_type IN(1, 3, 4, 5, 6)
3333:
3334: -- always dispatch the task whether invoking device successfully or not
3335: -- So update the table always
3336:
3337: UPDATE wms_dispatched_tasks
3338: SET device_invoked = 'Y',
3339: device_request_id = l_request_id
3340: WHERE transaction_temp_id = l_transaction_temp_id;
3341:
3449: x_num_of_tasks := x_num_of_tasks + 1;
3450:
3451: --Bug#5599049: We need to make sure that the task is not being performed by other users.
3452: SELECT count(1) INTO l_wdt_count
3453: FROM wms_dispatched_tasks WDT
3454: WHERE WDT.transaction_temp_id = task_record.transaction_temp_id
3455: AND WDT.person_id <> p_employee_id
3456: AND WDT.status in (3,9);
3457:
3528: -- Check if this record already exists in WDD
3529: -- and the task status
3530: SELECT STATUS
3531: INTO l_task_Status
3532: FROM wms_dispatched_tasks
3533: WHERE transaction_temp_id = task_record.transaction_temp_id;
3534:
3535: -- Bug 4507435, if retain_dispatch_Task is null.
3536: IF ((l_task_status <> 3) OR ( l_task_status = 3 AND p_retain_dispatch_task = 'Y')) THEN
3534:
3535: -- Bug 4507435, if retain_dispatch_Task is null.
3536: IF ((l_task_status <> 3) OR ( l_task_status = 3 AND p_retain_dispatch_task = 'Y')) THEN
3537: mydebug('Updating Temp ID in WDT: ' || task_record.transaction_temp_id, l_api_name);
3538: UPDATE wms_dispatched_tasks
3539: SET status = 3, -- Dispatched
3540: task_group_id = l_group_sequence_number,
3541: device_id = l_loop_device_id,
3542: device_invoked = Decode(l_loop_device_id, NULL, To_char(NULL),
3566: g_previous_task_status(task_record.transaction_temp_id) := 1;
3567:
3568: BEGIN --Bug#8322661 Kept the INSERT in a BEGIN-END block
3569:
3570: -- Insert into WMS_DISPATCHED_TASKS for this user
3571: INSERT INTO wms_dispatched_tasks
3572: (task_id,
3573: transaction_temp_id,
3574: organization_id,
3567:
3568: BEGIN --Bug#8322661 Kept the INSERT in a BEGIN-END block
3569:
3570: -- Insert into WMS_DISPATCHED_TASKS for this user
3571: INSERT INTO wms_dispatched_tasks
3572: (task_id,
3573: transaction_temp_id,
3574: organization_id,
3575: user_task_type,
3595: device_request_id,
3596: task_group_id,
3597: task_method) -- add for cluster picking but others can use it too
3598: VALUES
3599: (wms_dispatched_tasks_s.NEXTVAL,
3600: task_record.transaction_temp_id,
3601: p_organization_id,
3602: NVL(task_record.standard_operation_id, 2),
3603: p_employee_id,
3824: END IF;
3825: END IF;
3826: END IF;
3827:
3828: UPDATE wms_dispatched_tasks
3829: SET status = 9, -- Active
3830: device_id = x_avail_device_id,
3831: device_invoked = Decode(x_avail_device_id, NULL, To_char(NULL), 'Y'),
3832: device_request_id = x_device_request_id,
3834: last_updated_by = l_user_id
3835: WHERE transaction_temp_id = l_next_transaction_temp_id;
3836: ELSE-- cycle count task
3837: mydebug('Updated Cycle count task to active: '||l_next_transaction_temp_id, l_api_name);
3838: UPDATE wms_dispatched_tasks
3839: SET status = 9, -- Active
3840: last_update_date = Sysdate,
3841: last_updated_by = l_user_id
3842: WHERE transaction_temp_id = l_next_transaction_temp_id;
4068: FROM dual;
4069: SELECT mtl_material_transactions_s.NEXTVAL
4070: INTO l_new_transaction_temp_id
4071: FROM dual;
4072: SELECT wms_dispatched_tasks_s.NEXTVAL INTO l_new_task_id FROM dual;
4073: IF g_debug = 1 THEN
4074: mydebug( ' Calling split_mmtt for Txn. temp id : '||p_source_transaction_number,'split_task');
4075: END IF;
4076: split_mmtt(
4094: END IF;
4095: BEGIN
4096: SELECT status
4097: INTO l_mmtt_task_status
4098: FROM wms_dispatched_tasks
4099: WHERE transaction_temp_id = p_source_transaction_number;
4100: EXCEPTION
4101: WHEN NO_DATA_FOUND THEN
4102: l_mmtt_task_status := -9999;
5131: IF g_debug = 1 THEN
5132: mydebug( ' Entered ','SPLIT_WDT');
5133: END IF;
5134: INSERT
5135: INTO wms_dispatched_tasks
5136: (
5137: op_plan_instance_id ,
5138: task_method ,
5139: task_id ,
5236: suggested_dest_locator_id ,
5237: operation_plan_id ,
5238: p_new_mol_id ,
5239: transfer_lpn_id
5240: FROM wms_dispatched_tasks
5241: WHERE transaction_temp_id = p_orig_transaction_temp_id;
5242: x_return_status := 'S';
5243: EXCEPTION
5244: WHEN OTHERS THEN