[Home] [Help]
386: msnt.to_serial_number
387: FROM mtl_serial_numbers_temp msnt
388: WHERE msnt.transaction_temp_id = p_transaction_temp_id;
389: --
390: l_serial_alloc_flag mtl_material_transactions_temp.serial_allocated_flag%TYPE := 'N';
391: l_user_id NUMBER;
392: l_fm_serial_number VARCHAR2(30);
393: l_to_serial_number VARCHAR2(30);
394: --
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))
612: END IF; -- cluster pick check
613:
614: -- Bug# 4185621: update child line posting flag to 'N' for bulking picking task
615: IF (l_is_bulk_picked_task = 'Y') THEN
616: UPDATE mtl_material_transactions_temp mmtt
617: SET posting_flag = 'N'
618: WHERE parent_line_id = p_transaction_temp_id
619: AND parent_line_id <> transaction_temp_id;
620: END IF;
653: ELSE -- lot controlled
654: --Bug 9473783 Modified the where clause
655: SELECT msnt.transaction_temp_id
656: INTO l_serial_temp_id
657: FROM mtl_serial_numbers_temp msnt, mtl_transaction_lots_temp mtlt, mtl_material_transactions_temp mmtt
658: WHERE ((msnt.transaction_temp_id = NVL(mtlt.serial_transaction_temp_id, -1)
659: AND mtlt.transaction_temp_id = l_transaction_temp_id)
660: OR (msnt.transaction_temp_id = l_transaction_temp_id
661: AND mmtt.transaction_temp_id = l_transaction_temp_id AND mmtt.lot_number IS NOT NULL ))
1002: SELECT
1003: wda.delivery_id,mtrl.carton_grouping_id
1004: INTO l_delivery_id,l_carton_grouping_id
1005: FROM
1006: mtl_material_transactions_temp mmtt
1007: , mtl_txn_request_lines mtrl
1008: , wsh_delivery_details wdd
1009: , wsh_delivery_assignments_v wda
1010: WHERE mmtt.transaction_temp_id = l_transaction_temp_id
1016: SELECT
1017: mtrl.carton_grouping_id
1018: INTO l_carton_grouping_id
1019: FROM
1020: mtl_material_transactions_temp mmtt
1021: , mtl_txn_request_lines mtrl
1022: WHERE mmtt.transaction_temp_id = l_transaction_temp_id
1023: AND mmtt.move_order_line_id = mtrl.line_id;
1024: END IF;
1043: IF l_transaction_action_id = 28 THEN -- SO/IO
1044: BEGIN
1045: SELECT wda.delivery_id,mtrl.carton_grouping_id
1046: INTO l_delivery_id,l_carton_grouping_id
1047: FROM mtl_material_transactions_temp mmtt
1048: , mtl_txn_request_lines mtrl
1049: , wsh_delivery_details wdd
1050: , wsh_delivery_assignments_v wda
1051: WHERE mmtt.transaction_temp_id = l_transaction_temp_id
1059: mydebug('In exception block of OTHERS', l_api_name);
1060: END IF;
1061: SELECT mtrl.carton_grouping_id
1062: INTO l_carton_grouping_id
1063: FROM mtl_material_transactions_temp mmtt
1064: , mtl_txn_request_lines mtrl
1065: WHERE mmtt.transaction_temp_id = l_transaction_temp_id
1066: AND mmtt.move_order_line_id = mtrl.line_id;
1067: END;
1094: --Check if it is a bulk picked task
1095: /*
1096: SELECT DECODE (parent_line_id, transaction_temp_id, 'Y', 'N')
1097: INTO l_is_bulk_picked_task
1098: FROM mtl_material_transactions_temp mmtt
1099: WHERE transaction_temp_id = l_transaction_temp_id;
1100: */
1101:
1102: -- If Yes then set l_check_overpick_passed to 'Y'
1129: SELECT mtrh.move_order_type
1130: INTO l_move_order_type
1131: FROM mtl_txn_request_headers mtrh,
1132: mtl_txn_request_lines mtrl,
1133: mtl_material_transactions_temp mmtt
1134: WHERE mmtt.move_order_line_id = mtrl.line_id
1135: AND mtrl.header_id = mtrh.header_id
1136: AND mmtt.transaction_temp_id = l_transaction_temp_id;
1137:
1190:
1191: BEGIN
1192: SELECT 'C'
1193: INTO l_item_type
1194: FROM mtl_material_transactions_temp mmtt,
1195: mtl_cross_references mcr
1196: WHERE mmtt.transaction_temp_id = l_transaction_temp_id
1197: AND mmtt.inventory_item_id = mcr.inventory_item_id
1198: AND mcr.cross_reference_type = g_gtin_cross_ref_type
1216: END IF;
1217:
1218: BEGIN
1219: SELECT wutta.honor_case_pick_flag, pgvl.template_name into l_honor_case_pick_flag, l_template_name
1220: FROM mtl_material_transactions_temp mmtt, wms_user_task_type_attributes wutta , wms_page_templates_tl pgtl, WMS_PAGE_TEMPLATES_VL pgvl
1221: WHERE mmtt.transaction_temp_id = l_transaction_temp_id
1222: AND mmtt.standard_operation_id = wutta.user_task_type_id
1223: AND mmtt.organization_id = wutta.organization_id
1224: AND pgtl.template_id = wutta.pick_load_page_template_id
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
1359: , mtl_secondary_inventories msi
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
1481: , mtl_secondary_inventories msi
1545: , p_transfer_lpn_id NUMBER
1546: ) IS
1547: CURSOR c_get_bulk_txn(p_org_id IN NUMBER, p_lpn_id IN NUMBER) IS
1548: SELECT transaction_temp_id, transaction_header_id, lpn_id, transfer_lpn_id
1549: FROM mtl_material_transactions_temp t1
1550: WHERE transfer_lpn_id = p_lpn_id
1551: AND organization_id = p_org_id
1552: AND parent_line_id IS NULL
1553: AND EXISTS(
1551: AND organization_id = p_org_id
1552: AND parent_line_id IS NULL
1553: AND EXISTS(
1554: SELECT 1
1555: FROM mtl_material_transactions_temp t2
1556: WHERE t2.parent_line_id = t1.transaction_temp_id
1557: AND t2.organization_id = t1.organization_id
1558: );
1559: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1561: x_return_status := fnd_api.g_ret_sts_success;
1562: -- For each transaction returned by the cursor update the Child Records and delete the Parent.
1563: FOR v_rec IN c_get_bulk_txn(p_organization_id, p_transfer_lpn_id) LOOP
1564: -- Updating the Child Records.
1565: UPDATE mtl_material_transactions_temp
1566: SET transaction_header_id = v_rec.transaction_header_id
1567: , transfer_lpn_id = v_rec.transfer_lpn_id
1568: , lpn_id = v_rec.lpn_id
1569: , parent_line_id = NULL
1570: WHERE parent_line_id = v_rec.transaction_temp_id
1571: AND organization_id = p_organization_id;
1572:
1573: -- Deleting the Parent Record.
1574: DELETE FROM mtl_material_transactions_temp
1575: WHERE transaction_temp_id = v_rec.transaction_temp_id;
1576: END LOOP;
1577: EXCEPTION
1578: WHEN OTHERS THEN
1619: CURSOR c_det_pick_type(p_org_id IN NUMBER, p_lpn_id IN NUMBER) IS
1620: SELECT DECODE (mtrh.move_order_type
1621: , inv_globals.g_move_order_mfg_pick, 'MFG'
1622: , 'OTHERS')
1623: FROM mtl_material_transactions_temp mmtt
1624: , mtl_txn_request_lines mtrl
1625: , mtl_txn_request_headers mtrh
1626: WHERE mmtt.organization_id = p_org_id
1627: AND mmtt.transfer_lpn_id = p_lpn_id
1635: , mmtt.transaction_type_id
1636: , mtrl.txn_source_id
1637: , mtrl.txn_source_line_id
1638: , mtrl.reference_id
1639: FROM mtl_material_transactions_temp mmtt
1640: , mtl_txn_request_lines mtrl
1641: WHERE mmtt.organization_id = p_org_id
1642: AND mmtt.transfer_lpn_id = p_lpn_id
1643: AND mtrl.line_id = mmtt.move_order_line_id;
1744: , 0 wip_entity_id
1745: , 0 repetitive_schedule_id
1746: , 0 operation_seq_num
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 */
1794: , mtrl.txn_source_id wip_entity_id
1795: , mtrl.reference_id repetitive_schedule_id
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
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
2178: SELECT 1
2179: INTO l_mmtt_rowcnt
2180: FROM dual
2181: WHERE exists (SELECT 1
2182: FROM mtl_material_transactions_temp
2183: WHERE transaction_temp_id = p_transaction_temp_id
2184: AND organization_id = p_organization_id
2185: AND (parent_line_id is NULL -- regular task
2186: OR parent_line_id = transaction_temp_id)); -- bulk task
2188: SELECT 1
2189: INTO l_mmtt_rowcnt
2190: FROM dual
2191: WHERE exists (SELECT 1
2192: FROM mtl_material_transactions_temp
2193: WHERE transaction_temp_id = p_transaction_temp_id
2194: AND organization_id = p_organization_id
2195: AND wms_task_status <> 8 -- unreleased
2196: AND (
2268: SELECT mtl_material_transactions_s.NEXTVAL txnhdrid
2269: INTO l_txn_hdr_id
2270: FROM DUAL;
2271:
2272: UPDATE mtl_material_transactions_temp
2273: SET transaction_header_id = l_txn_hdr_id ,
2274: last_update_date = Sysdate,
2275: last_updated_by = l_user_id,
2276: creation_date = Sysdate,
2311: l_standard_operation_id,
2312: l_operation_plan_id,
2313: l_move_order_line_id,
2314: l_item_id
2315: FROM mtl_material_transactions_temp t, bom_std_op_resources bsor, bom_resources bremp
2316: WHERE t.transaction_temp_id = p_transaction_temp_id
2317: AND t.standard_operation_id = bsor.standard_operation_id
2318: AND bsor.resource_id = bremp.resource_id
2319: AND bremp.resource_type = 2
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
2530: AND wdt.status = 3
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
2547: AND wdt.status = 3
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
2566: AND wdt.organization_id = p_organization_id
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
2593: AND wdt.status = 3
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
2611: AND wdt.organization_id = p_organization_id
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
2647: AND wdt.organization_id = p_organization_id
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
2694: AND wdt.organization_id = p_organization_id
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
2998: mydebug('Tempid count ' || l_count ,l_next_transaction_temp_id);
2999: mydebug ('First Index is ' , g_start_over_tempid.FIRST);
3000: END IF;
3001:
3002: UPDATE mtl_material_transactions_temp
3003: SET transaction_header_id = mtl_material_transactions_s.NEXTVAL,
3004: last_update_date = Sysdate,
3005: last_updated_by = l_user_id
3006: WHERE transaction_temp_id = l_next_transaction_temp_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
3515: IF (l_debug = 1) THEN
3516: mydebug('Device Request ID: ' || l_request_id, l_api_name);
3517: END IF; */
3518:
3519: UPDATE mtl_material_transactions_temp
3520: SET transaction_header_id = mtl_material_transactions_s.NEXTVAL
3521: , last_update_date = Sysdate
3522: , last_updated_by = l_user_id
3523: , posting_flag = 'Y' -- Bug4185621: this will change the parent posting flag to 'Y' for bulking picking
3941: mydebug('l_resultant_tasks(1).task_id ' || l_resultant_tasks(1).task_id, 'split_mmtt_lpn');
3942: l_transaction_temp_id := l_resultant_tasks(1).task_id;
3943:
3944: --Modified for bug 6717052
3945: update mtl_material_transactions_temp
3946: set transfer_lpn_id = p_lpn_id
3947: where transaction_temp_id = l_resultant_tasks(1).task_id;
3948:
3949: x_return_status := fnd_api.g_ret_sts_success;
4168: END IF;
4169: END IF;
4170: -- Update the original row
4171: BEGIN
4172: UPDATE mtl_material_transactions_temp
4173: SET primary_quantity = primary_quantity - l_split_uom_quantities(i).primary_quantity ,
4174: transaction_quantity = transaction_quantity - l_split_uom_quantities(i).transaction_quantity ,
4175: last_updated_by = FND_GLOBAL.USER_ID
4176: WHERE transaction_temp_id = p_source_transaction_number;
4360: l_mmtt_transaction_quantity ,
4361: l_mmtt_item_primary_uom_code ,
4362: l_mmtt_transaction_uom ,
4363: l_mmtt_organization_id
4364: FROM mtl_material_transactions_temp
4365: WHERE transaction_temp_id = p_transaction_temp_id;
4366: SELECT lot_control_code ,
4367: serial_number_control_code
4368: INTO l_lot_control_code ,
4620: IF g_debug = 1 THEN
4621: mydebug( ' Entered ', 'split_mmtt');
4622: END IF;
4623: INSERT
4624: INTO mtl_material_transactions_temp
4625: (
4626: currency_conversion_date ,
4627: shipment_number ,
4628: org_cost_group_id ,
5103: common_bom_seq_id ,
5104: common_routing_seq_id ,
5105: ussgl_transaction_code ,
5106: fulfillment_base --16574325
5107: FROM mtl_material_transactions_temp
5108: WHERE transaction_temp_id = p_orig_transaction_temp_id;
5109: x_return_status := 'S';
5110: EXCEPTION
5111: WHEN OTHERS THEN