[Home] [Help]
104: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
105: BEGIN
106: x_return_status := fnd_api.g_ret_sts_success;
107:
108: UPDATE wms_dispatched_tasks
109: SET status = 9
110: WHERE transaction_temp_id = p_transaction_temp_id;
111:
112: COMMIT;
281: , l_allocated_lpn_id
282: , l_transaction_action_id
283: , l_is_bulk_picked_task
284: , l_serial_alloc_flag -- Bug 4434111
285: FROM mtl_material_transactions_temp mmtt, wms_dispatched_tasks wdt
286: WHERE wdt.person_id = p_sign_on_emp_id
287: AND wdt.organization_id = p_sign_on_org_id
288: AND wdt.status <= 3
289: AND wdt.task_type IN (1, 4, 5, 6)
323: , mmtt.inventory_item_id inventory_item_id
324: , mmtt.cartonization_id cartonization_id
325: , mmtt.allocated_lpn_id allocated_lpn_id
326: , mmtt.serial_allocated_flag serial_allocated_flag
327: FROM mtl_material_transactions_temp mmtt, wms_dispatched_tasks wdt, mtl_item_locations loc, mtl_secondary_inventories sub
328: WHERE wdt.person_id = p_sign_on_emp_id
329: AND wdt.organization_id = p_sign_on_org_id
330: AND wdt.status <= 3
331: AND (wdt.task_type IN (1, 4, 5, 6))
374: , mmtt.inventory_item_id inventory_item_id
375: , mmtt.cartonization_id cartonization_id
376: , mmtt.allocated_lpn_id allocated_lpn_id
377: , mmtt.serial_allocated_flag serial_allocated_flag
378: FROM mtl_material_transactions_temp mmtt, wms_dispatched_tasks wdt, mtl_item_locations loc, mtl_secondary_inventories sub
379: WHERE wdt.person_id = p_sign_on_emp_id
380: AND wdt.organization_id = p_sign_on_org_id
381: AND wdt.status <= 3
382: AND (wdt.task_type IN (1, 4, 5, 6))
425: , mmtt.inventory_item_id inventory_item_id
426: , mmtt.cartonization_id cartonization_id
427: , mmtt.allocated_lpn_id allocated_lpn_id
428: , mmtt.serial_allocated_flag serial_allocated_flag
429: FROM mtl_material_transactions_temp mmtt, wms_dispatched_tasks wdt, mtl_item_locations loc, mtl_secondary_inventories sub
430: WHERE wdt.person_id = p_sign_on_emp_id
431: AND wdt.organization_id = p_sign_on_org_id
432: AND wdt.status <= 3
433: AND (wdt.task_type IN (1, 4, 5, 6))
1105:
1106: , l_honor_case_pick_flag --69
1107: , l_template_name --70
1108:
1109: FROM wms_dispatched_tasks wdt
1110: , mtl_material_transactions_temp mmtt
1111: , mtl_system_items_vl msik /* Bug 5581528 */
1112: , mtl_parameters mp
1113: , mtl_txn_request_lines mtrl
1212: /* Added for LMS project */
1213: , wdt.user_task_type
1214: , mmtt.operation_plan_id
1215: /* end for LMS project */
1216: FROM wms_dispatched_tasks wdt
1217: , mtl_material_transactions_temp mmtt
1218: , mtl_system_items_vl msik /* Bug 5581528 */
1219: , mtl_parameters mp
1220: , mtl_txn_request_lines mtrl
1487: , mmtt.transfer_to_location --Bug#2756609
1488: FROM mtl_material_transactions_temp mmtt
1489: , mtl_secondary_inventories msi
1490: , mtl_item_locations mil
1491: , wms_dispatched_tasks wdt
1492: , mtl_system_items_vl msik /* Bug 5581528 */
1493: WHERE mmtt.organization_id = p_organization_id
1494: AND mmtt.transfer_lpn_id = p_transfer_lpn_id
1495: AND mmtt.transaction_source_type_id = inv_globals.g_sourcetype_inventory
1536: , mtrl.txn_source_line_id operation_seq_num
1537: , to_number(NULL) transfer_to_location --Bug#2756609
1538: FROM mtl_material_transactions_temp mmtt
1539: , mtl_txn_request_lines mtrl
1540: , wms_dispatched_tasks wdt
1541: , mtl_system_items_vl msik /* Bug 5581528 */
1542: WHERE mmtt.organization_id = p_organization_id
1543: AND mmtt.transfer_lpn_id = p_transfer_lpn_id
1544: AND mmtt.transaction_source_type_id = inv_globals.g_sourcetype_wip
1588: , inv_project.get_project_id
1589: , inv_project.get_project_number
1590: , inv_project.get_task_id
1591: , inv_project.get_task_number
1592: FROM wms_dispatched_tasks wdt, mtl_material_transactions_temp mmtt, mtl_system_items_vl msik /* Bug 5581528 */
1593: WHERE wdt.organization_id = p_organization_id
1594: AND wdt.status IN (3, 4)
1595: AND wdt.task_type IN (1, 4, 5, 7)
1596: AND wdt.transaction_temp_id = mmtt.transaction_temp_id
1856: l_user_id NUMBER;
1857: l_mmtt_rowcnt NUMBER;
1858: l_wdt_rowcnt NUMBER;
1859: l_undispatched_picks NUMBER;
1860: l_equipment_serial WMS_DISPATCHED_TASKS.EQUIPMENT_INSTANCE%TYPE;
1861: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
1862: l_status NUMBER ; --Bug#5157839.
1863:
1864: /*6009436 Begin */
1893: mydebug('MANUAL_PICK: In Manual Pick API', 'MANUAL_PICK');
1894: mydebug('p_allow_unreleased_task is : ' || p_allow_unreleased_task, 'MANUAL_PICK');
1895: END IF;
1896:
1897: -- Bug #4090630 - inserting NULL into WMS_DISPATCHED_TASKS.equipement_instance
1898: -- if p_equipment_serial is NONE
1899: l_equipment_serial := p_equipment_serial;
1900: IF (p_equipment_serial = 'NONE') then
1901: IF (l_debug = 1) THEN
1961: SELECT 1
1962: INTO l_wdt_rowcnt
1963: FROM dual
1964: WHERE exists (SELECT 1
1965: FROM wms_dispatched_tasks t
1966: WHERE t.transaction_temp_id = p_transaction_temp_id
1967: AND person_id <> p_employee_id);
1968: EXCEPTION
1969: WHEN NO_DATA_FOUND THEN
1979: fnd_msg_pub.ADD;
1980: RAISE fnd_api.g_exc_error;
1981: ELSE
1982: BEGIN --bug#5157839.Start fix
1983: SELECT wdt.status INTO l_status FROM wms_dispatched_tasks wdt
1984: WHERE wdt.transaction_temp_id = p_transaction_temp_id;
1985:
1986: IF l_status NOT IN (1,2,3) THEN
1987: IF (l_debug = 1) THEN
2027: SELECT 1
2028: INTO l_wdt_rowcnt
2029: FROM dual
2030: WHERE exists (SELECT 1
2031: FROM wms_dispatched_tasks t
2032: WHERE t.transaction_temp_id = p_transaction_temp_id);
2033:
2034: g_previous_task_status(p_transaction_temp_id) := 2;
2035: EXCEPTION
2164: END ;
2165: END IF;
2166: --Bug6009436.End
2167:
2168: -- Insert into WMS_DISPATCHED_TASKS for this user
2169: INSERT INTO wms_dispatched_tasks
2170: (task_id,
2171: transaction_temp_id,
2172: organization_id,
2165: END IF;
2166: --Bug6009436.End
2167:
2168: -- Insert into WMS_DISPATCHED_TASKS for this user
2169: INSERT INTO wms_dispatched_tasks
2170: (task_id,
2171: transaction_temp_id,
2172: organization_id,
2173: user_task_type,
2187: task_type,
2188: operation_plan_id,
2189: move_order_line_id)
2190: VALUES
2191: (wms_dispatched_tasks_s.NEXTVAL,
2192: p_transaction_temp_id,
2193: p_organization_id,
2194: l_standard_operation_id,
2195: p_employee_id,
2261: IF p_grouping_document_type = 'PICK_SLIP' THEN
2262:
2263: SELECT wdt.task_id, mmtt.transaction_temp_id, wdt.task_type
2264: INTO x_task_id, x_transaction_temp_id, x_task_type_id
2265: FROM wms_dispatched_tasks wdt,
2266: mtl_material_transactions_temp mmtt
2267: WHERE wdt.person_id = p_employee_id
2268: AND wdt.organization_id = p_organization_id
2269: AND wdt.task_type = 1 -- Picking
2278: ELSIF p_grouping_document_type = 'ORDER' THEN
2279:
2280: SELECT wdt.task_id, mmtt.transaction_temp_id, wdt.task_type
2281: INTO x_task_id, x_transaction_temp_id, x_task_type_id
2282: FROM wms_dispatched_tasks wdt,
2283: mtl_material_transactions_temp mmtt
2284: WHERE wdt.person_id = p_employee_id
2285: AND wdt.organization_id = p_organization_id
2286: AND wdt.task_type = 1 -- Picking
2296: ELSIF p_grouping_document_type = 'CARTON' THEN
2297:
2298: SELECT wdt.task_id, mmtt.transaction_temp_id, wdt.task_type
2299: INTO x_task_id, x_transaction_temp_id, x_task_type_id
2300: FROM wms_dispatched_tasks wdt,
2301: mtl_material_transactions_temp mmtt
2302: WHERE wdt.person_id = p_employee_id
2303: AND wdt.organization_id = p_organization_id
2304: AND wdt.task_type = 1 -- Picking
2313: ELSIF p_grouping_document_type = 'CLUSTERPICKBYLABEL' THEN
2314:
2315: SELECT wdt.task_id, mmtt.transaction_temp_id, wdt.task_type
2316: INTO x_task_id, x_transaction_temp_id, x_task_type_id
2317: FROM wms_dispatched_tasks wdt,
2318: mtl_material_transactions_temp mmtt
2319: WHERE wdt.person_id = p_employee_id
2320: AND wdt.organization_id = p_organization_id
2321: AND wdt.task_type = 1 -- Picking
2331:
2332: SELECT task_id, transaction_temp_id, task_type
2333: INTO x_task_id, x_transaction_temp_id, x_task_type_id
2334: FROM (select wdt.task_id, mmtt.transaction_temp_id, wdt.task_type
2335: FROM wms_dispatched_tasks wdt,
2336: mtl_material_transactions_temp mmtt,
2337: mtl_secondary_inventories msi,
2338: mtl_item_locations mil
2339: WHERE wdt.person_id = p_employee_id
2392: IS
2393: l_cartonization_id NUMBER;
2394: l_device_id NUMBER := p_device_id;
2395: l_equipment_id NUMBER := p_equipment_id;
2396: l_equipment_serial WMS_DISPATCHED_TASKS.EQUIPMENT_INSTANCE%TYPE;
2397: l_subinventory_code VARCHAR2(10) := p_subinventory_code;
2398: l_assignment_temp_id NUMBER;
2399: l_loop_device_id NUMBER;
2400:
2456: ORDER BY wdat.assignment_temp_id;
2457:
2458: -- Following cursor is for 4507435, to count the dispatched cluster size
2459: CURSOR cluster_size_wdt IS SELECT mmtt.transaction_source_id, count (*)
2460: FROM wms_dispatched_tasks wdt, mtl_material_transactions_temp mmtt
2461: WHERE mmtt.transaction_temp_id = wdt.transaction_temp_id
2462: AND wdt.status = 3
2463: AND wdt.person_id = p_employee_id
2464: AND wdt.organization_id = p_organization_id
2678:
2679: -- Bug#5185031 Fetched the value for l_max_seq_number.
2680: select wdt.task_method, count(*), max(wdt.task_group_id)
2681: into l_task_method_wdt, l_count_wdt ,l_max_seq_number
2682: from wms_dispatched_tasks wdt, mtl_material_transactions_temp mmtt
2683: where mmtt.transaction_temp_id = wdt.transaction_temp_id
2684: and wdt.status = 3
2685: and wdt.person_id = p_employee_id
2686: and wdt.organization_id = p_organization_id
2748: From
2749: ( select mmtt.transaction_source_id, mmtt.transaction_source_type_id, wdt.device_id,
2750: wdt.task_group_id, mmtt.pick_slip_number, mmtt.cartonization_id
2751:
2752: from mtl_material_transactions_temp mmtt, wms_dispatched_tasks wdt
2753: where mmtt.transaction_temp_id = wdt.transaction_temp_id
2754: and wdt.status = 3
2755: and wdt.person_id = p_employee_id
2756: and wdt.organization_id = p_organization_id
2931: SELECT transaction_temp_id, task_type,
2932: device_invoked, device_request_id
2933: INTO l_transaction_temp_id, x_task_type_id,
2934: l_device_invoked, l_request_id
2935: FROM wms_dispatched_tasks
2936: WHERE person_id = p_employee_id
2937: AND organization_id = p_organization_id
2938: AND device_id = l_loop_device_id
2939: AND task_type IN(1, 3, 4, 5, 6)
2960:
2961: -- always dispatch the task whether invoking device successfully or not
2962: -- So update the table always
2963:
2964: UPDATE wms_dispatched_tasks
2965: SET device_invoked = 'Y',
2966: device_request_id = l_request_id
2967: WHERE transaction_temp_id = l_transaction_temp_id;
2968:
3073: x_num_of_tasks := x_num_of_tasks + 1;
3074:
3075: --Bug#5599049: We need to make sure that the task is not being performed by other users.
3076: SELECT count(1) INTO l_wdt_count
3077: FROM wms_dispatched_tasks WDT
3078: WHERE WDT.transaction_temp_id = task_record.transaction_temp_id
3079: AND WDT.person_id <> p_employee_id
3080: AND WDT.status in (3,9);
3081:
3152: -- Check if this record already exists in WDD
3153: -- and the task status
3154: SELECT STATUS
3155: INTO l_task_Status
3156: FROM wms_dispatched_tasks
3157: WHERE transaction_temp_id = task_record.transaction_temp_id;
3158:
3159: -- Bug 4507435, if retain_dispatch_Task is null.
3160: IF ((l_task_status <> 3) OR ( l_task_status = 3 AND p_retain_dispatch_task = 'Y')) THEN
3158:
3159: -- Bug 4507435, if retain_dispatch_Task is null.
3160: IF ((l_task_status <> 3) OR ( l_task_status = 3 AND p_retain_dispatch_task = 'Y')) THEN
3161: mydebug('Updating Temp ID in WDT: ' || task_record.transaction_temp_id, l_api_name);
3162: UPDATE wms_dispatched_tasks
3163: SET status = 3, -- Dispatched
3164: task_group_id = l_group_sequence_number,
3165: device_id = l_loop_device_id,
3166: device_invoked = Decode(l_loop_device_id, NULL, To_char(NULL),
3187: WHEN NO_DATA_FOUND THEN
3188:
3189: g_previous_task_status(task_record.transaction_temp_id) := 1;
3190:
3191: -- Insert into WMS_DISPATCHED_TASKS for this user
3192: INSERT INTO wms_dispatched_tasks
3193: (task_id,
3194: transaction_temp_id,
3195: organization_id,
3188:
3189: g_previous_task_status(task_record.transaction_temp_id) := 1;
3190:
3191: -- Insert into WMS_DISPATCHED_TASKS for this user
3192: INSERT INTO wms_dispatched_tasks
3193: (task_id,
3194: transaction_temp_id,
3195: organization_id,
3196: user_task_type,
3216: device_request_id,
3217: task_group_id,
3218: task_method) -- add for cluster picking but others can use it too
3219: VALUES
3220: (wms_dispatched_tasks_s.NEXTVAL,
3221: task_record.transaction_temp_id,
3222: p_organization_id,
3223: NVL(task_record.standard_operation_id, 2),
3224: p_employee_id,
3396: END IF;
3397: END IF;
3398: END IF;
3399:
3400: UPDATE wms_dispatched_tasks
3401: SET status = 9, -- Active
3402: device_id = x_avail_device_id,
3403: device_invoked = Decode(x_avail_device_id, NULL, To_char(NULL), 'Y'),
3404: device_request_id = x_device_request_id,
3406: last_updated_by = l_user_id
3407: WHERE transaction_temp_id = l_next_transaction_temp_id;
3408: ELSE-- cycle count task
3409: mydebug('Updated Cycle count task to active: '||l_next_transaction_temp_id, l_api_name);
3410: UPDATE wms_dispatched_tasks
3411: SET status = 9, -- Active
3412: last_update_date = Sysdate,
3413: last_updated_by = l_user_id
3414: WHERE transaction_temp_id = l_next_transaction_temp_id;
3640: FROM dual;
3641: SELECT mtl_material_transactions_s.NEXTVAL
3642: INTO l_new_transaction_temp_id
3643: FROM dual;
3644: SELECT wms_dispatched_tasks_s.NEXTVAL INTO l_new_task_id FROM dual;
3645: IF g_debug = 1 THEN
3646: mydebug( ' Calling split_mmtt for Txn. temp id : '||p_source_transaction_number,'split_task');
3647: END IF;
3648: split_mmtt(
3666: END IF;
3667: BEGIN
3668: SELECT status
3669: INTO l_mmtt_task_status
3670: FROM wms_dispatched_tasks
3671: WHERE transaction_temp_id = p_source_transaction_number;
3672: EXCEPTION
3673: WHEN NO_DATA_FOUND THEN
3674: l_mmtt_task_status := -9999;
4701: IF g_debug = 1 THEN
4702: mydebug( ' Entered ','SPLIT_WDT');
4703: END IF;
4704: INSERT
4705: INTO wms_dispatched_tasks
4706: (
4707: op_plan_instance_id ,
4708: task_method ,
4709: task_id ,
4806: suggested_dest_locator_id ,
4807: operation_plan_id ,
4808: p_new_mol_id ,
4809: transfer_lpn_id
4810: FROM wms_dispatched_tasks
4811: WHERE transaction_temp_id = p_orig_transaction_temp_id;
4812: x_return_status := 'S';
4813: EXCEPTION
4814: WHEN OTHERS THEN