[Home] [Help]
224: msnt.to_serial_number
225: FROM mtl_serial_numbers_temp msnt
226: WHERE msnt.transaction_temp_id = p_transaction_temp_id;
227: --
228: l_serial_alloc_flag mtl_material_transactions_temp.serial_allocated_flag%TYPE := 'N';
229: l_user_id NUMBER;
230: l_fm_serial_number VARCHAR2(30);
231: l_to_serial_number VARCHAR2(30);
232: --
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))
450: END IF; -- cluster pick check
451:
452: -- Bug# 4185621: update child line posting flag to 'N' for bulking picking task
453: IF (l_is_bulk_picked_task = 'Y') THEN
454: UPDATE mtl_material_transactions_temp mmtt
455: SET posting_flag = 'N'
456: WHERE parent_line_id = p_transaction_temp_id
457: AND parent_line_id <> transaction_temp_id;
458: END IF;
837: SELECT
838: wda.delivery_id,mtrl.carton_grouping_id
839: INTO l_delivery_id,l_carton_grouping_id
840: FROM
841: mtl_material_transactions_temp mmtt
842: , mtl_txn_request_lines mtrl
843: , wsh_delivery_details wdd
844: , wsh_delivery_assignments_v wda
845: WHERE mmtt.transaction_temp_id = l_transaction_temp_id
851: SELECT
852: mtrl.carton_grouping_id
853: INTO l_carton_grouping_id
854: FROM
855: mtl_material_transactions_temp mmtt
856: , mtl_txn_request_lines mtrl
857: WHERE mmtt.transaction_temp_id = l_transaction_temp_id
858: AND mmtt.move_order_line_id = mtrl.line_id;
859: END IF;
877: --Check if it is a bulk picked task
878: /*
879: SELECT DECODE (parent_line_id, transaction_temp_id, 'Y', 'N')
880: INTO l_is_bulk_picked_task
881: FROM mtl_material_transactions_temp mmtt
882: WHERE transaction_temp_id = l_transaction_temp_id;
883: */
884:
885: -- If Yes then set l_check_overpick_passed to 'Y'
912: SELECT mtrh.move_order_type
913: INTO l_move_order_type
914: FROM mtl_txn_request_headers mtrh,
915: mtl_txn_request_lines mtrl,
916: mtl_material_transactions_temp mmtt
917: WHERE mmtt.move_order_line_id = mtrl.line_id
918: AND mtrl.header_id = mtrh.header_id
919: AND mmtt.transaction_temp_id = l_transaction_temp_id;
920:
973:
974: BEGIN
975: SELECT 'C'
976: INTO l_item_type
977: FROM mtl_material_transactions_temp mmtt,
978: mtl_cross_references mcr
979: WHERE mmtt.transaction_temp_id = l_transaction_temp_id
980: AND mmtt.inventory_item_id = mcr.inventory_item_id
981: AND mcr.cross_reference_type = g_gtin_cross_ref_type
999: END IF;
1000:
1001: BEGIN
1002: SELECT wutta.honor_case_pick_flag, pgvl.template_name into l_honor_case_pick_flag, l_template_name
1003: FROM mtl_material_transactions_temp mmtt, wms_user_task_type_attributes wutta , wms_page_templates_tl pgtl, WMS_PAGE_TEMPLATES_VL pgvl
1004: WHERE mmtt.transaction_temp_id = l_transaction_temp_id
1005: AND mmtt.standard_operation_id = wutta.user_task_type_id
1006: AND mmtt.organization_id = wutta.organization_id
1007: AND pgtl.template_id = wutta.pick_load_page_template_id
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
1114: , mtl_secondary_inventories msi
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
1221: , mtl_secondary_inventories msi
1285: , p_transfer_lpn_id NUMBER
1286: ) IS
1287: CURSOR c_get_bulk_txn(p_org_id IN NUMBER, p_lpn_id IN NUMBER) IS
1288: SELECT transaction_temp_id, transaction_header_id, lpn_id, transfer_lpn_id
1289: FROM mtl_material_transactions_temp t1
1290: WHERE transfer_lpn_id = p_lpn_id
1291: AND organization_id = p_org_id
1292: AND parent_line_id IS NULL
1293: AND EXISTS(
1291: AND organization_id = p_org_id
1292: AND parent_line_id IS NULL
1293: AND EXISTS(
1294: SELECT 1
1295: FROM mtl_material_transactions_temp t2
1296: WHERE t2.parent_line_id = t1.transaction_temp_id
1297: AND t2.organization_id = t1.organization_id
1298: );
1299: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1301: x_return_status := fnd_api.g_ret_sts_success;
1302: -- For each transaction returned by the cursor update the Child Records and delete the Parent.
1303: FOR v_rec IN c_get_bulk_txn(p_organization_id, p_transfer_lpn_id) LOOP
1304: -- Updating the Child Records.
1305: UPDATE mtl_material_transactions_temp
1306: SET transaction_header_id = v_rec.transaction_header_id
1307: , transfer_lpn_id = v_rec.transfer_lpn_id
1308: , lpn_id = v_rec.lpn_id
1309: , parent_line_id = NULL
1310: WHERE parent_line_id = v_rec.transaction_temp_id
1311: AND organization_id = p_organization_id;
1312:
1313: -- Deleting the Parent Record.
1314: DELETE FROM mtl_material_transactions_temp
1315: WHERE transaction_temp_id = v_rec.transaction_temp_id;
1316: END LOOP;
1317: EXCEPTION
1318: WHEN OTHERS THEN
1359: CURSOR c_det_pick_type(p_org_id IN NUMBER, p_lpn_id IN NUMBER) IS
1360: SELECT DECODE (mtrh.move_order_type
1361: , inv_globals.g_move_order_mfg_pick, 'MFG'
1362: , 'OTHERS')
1363: FROM mtl_material_transactions_temp mmtt
1364: , mtl_txn_request_lines mtrl
1365: , mtl_txn_request_headers mtrh
1366: WHERE mmtt.organization_id = p_org_id
1367: AND mmtt.transfer_lpn_id = p_lpn_id
1375: , mmtt.transaction_type_id
1376: , mtrl.txn_source_id
1377: , mtrl.txn_source_line_id
1378: , mtrl.reference_id
1379: FROM mtl_material_transactions_temp mmtt
1380: , mtl_txn_request_lines mtrl
1381: WHERE mmtt.organization_id = p_org_id
1382: AND mmtt.transfer_lpn_id = p_lpn_id
1383: AND mtrl.line_id = mmtt.move_order_line_id;
1484: , 0 wip_entity_id
1485: , 0 repetitive_schedule_id
1486: , 0 operation_seq_num
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 */
1534: , mtrl.txn_source_id wip_entity_id
1535: , mtrl.reference_id repetitive_schedule_id
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
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
1918: SELECT 1
1919: INTO l_mmtt_rowcnt
1920: FROM dual
1921: WHERE exists (SELECT 1
1922: FROM mtl_material_transactions_temp
1923: WHERE transaction_temp_id = p_transaction_temp_id
1924: AND organization_id = p_organization_id
1925: AND (parent_line_id is NULL -- regular task
1926: OR parent_line_id = transaction_temp_id)); -- bulk task
1928: SELECT 1
1929: INTO l_mmtt_rowcnt
1930: FROM dual
1931: WHERE exists (SELECT 1
1932: FROM mtl_material_transactions_temp
1933: WHERE transaction_temp_id = p_transaction_temp_id
1934: AND organization_id = p_organization_id
1935: AND wms_task_status <> 8 -- unreleased
1936: AND (
2008: SELECT mtl_material_transactions_s.NEXTVAL txnhdrid
2009: INTO l_txn_hdr_id
2010: FROM DUAL;
2011:
2012: UPDATE mtl_material_transactions_temp
2013: SET transaction_header_id = l_txn_hdr_id ,
2014: last_update_date = Sysdate,
2015: last_updated_by = l_user_id,
2016: creation_date = Sysdate,
2051: l_standard_operation_id,
2052: l_operation_plan_id,
2053: l_move_order_line_id,
2054: l_item_id
2055: FROM mtl_material_transactions_temp t, bom_std_op_resources bsor, bom_resources bremp
2056: WHERE t.transaction_temp_id = p_transaction_temp_id
2057: AND t.standard_operation_id = bsor.standard_operation_id
2058: AND bsor.resource_id = bremp.resource_id
2059: AND bremp.resource_type = 2
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
2270: AND wdt.status = 3
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
2287: AND wdt.status = 3
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
2305: AND wdt.status = 3
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
2322: AND wdt.status = 3
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
2340: AND wdt.organization_id = p_organization_id
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
2625: mydebug('Tempid count ' || l_count ,l_next_transaction_temp_id);
2626: mydebug ('First Index is ' , g_start_over_tempid.FIRST);
2627: END IF;
2628:
2629: UPDATE mtl_material_transactions_temp
2630: SET transaction_header_id = mtl_material_transactions_s.NEXTVAL,
2631: last_update_date = Sysdate,
2632: last_updated_by = l_user_id
2633: WHERE transaction_temp_id = l_next_transaction_temp_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
3139: IF (l_debug = 1) THEN
3140: mydebug('Device Request ID: ' || l_request_id, l_api_name);
3141: END IF;
3142:
3143: UPDATE mtl_material_transactions_temp
3144: SET transaction_header_id = mtl_material_transactions_s.NEXTVAL
3145: , last_update_date = Sysdate
3146: , last_updated_by = l_user_id
3147: , posting_flag = 'Y' -- Bug4185621: this will change the parent posting flag to 'Y' for bulking picking
3513: mydebug('l_resultant_tasks(1).task_id ' || l_resultant_tasks(1).task_id, 'split_mmtt_lpn');
3514: l_transaction_temp_id := l_resultant_tasks(1).task_id;
3515:
3516: --Modified for bug 6717052
3517: update mtl_material_transactions_temp
3518: set transfer_lpn_id = p_lpn_id
3519: where transaction_temp_id = l_resultant_tasks(1).task_id;
3520:
3521: x_return_status := fnd_api.g_ret_sts_success;
3740: END IF;
3741: END IF;
3742: -- Update the original row
3743: BEGIN
3744: UPDATE mtl_material_transactions_temp
3745: SET primary_quantity = primary_quantity - l_split_uom_quantities(i).primary_quantity ,
3746: transaction_quantity = transaction_quantity - l_split_uom_quantities(i).transaction_quantity ,
3747: last_updated_by = FND_GLOBAL.USER_ID
3748: WHERE transaction_temp_id = p_source_transaction_number;
3932: l_mmtt_transaction_quantity ,
3933: l_mmtt_item_primary_uom_code ,
3934: l_mmtt_transaction_uom ,
3935: l_mmtt_organization_id
3936: FROM mtl_material_transactions_temp
3937: WHERE transaction_temp_id = p_transaction_temp_id;
3938: SELECT lot_control_code ,
3939: serial_number_control_code
3940: INTO l_lot_control_code ,
4192: IF g_debug = 1 THEN
4193: mydebug( ' Entered ', 'split_mmtt');
4194: END IF;
4195: INSERT
4196: INTO mtl_material_transactions_temp
4197: (
4198: currency_conversion_date ,
4199: shipment_number ,
4200: org_cost_group_id ,
4673: reservation_id ,
4674: common_bom_seq_id ,
4675: common_routing_seq_id ,
4676: ussgl_transaction_code
4677: FROM mtl_material_transactions_temp
4678: WHERE transaction_temp_id = p_orig_transaction_temp_id;
4679: x_return_status := 'S';
4680: EXCEPTION
4681: WHEN OTHERS THEN