[Home] [Help]
1154: , mol.to_locator_id mol_loc_id
1155: , mol.backorder_delivery_detail_id
1156: , mmtt.operation_plan_id
1157: , mol.inspection_status
1158: FROM mtl_material_transactions_temp mmtt, mtl_txn_request_lines mol
1159: WHERE mmtt.lpn_id = l_lpn_id
1160: -- Added following line for ATF_J
1161: AND mmtt.move_order_line_id = NVL(p_move_order_line_id, mmtt.move_order_line_id)
1162: AND mmtt.organization_id = p_org_id
1192: , task_priority
1193: , NVL(wms_task_type, 2)
1194: , operation_plan_id
1195: , move_order_line_id
1196: FROM mtl_material_transactions_temp
1197: WHERE move_order_line_id = l_line_id
1198: AND transaction_action_id NOT IN (50, 51, 52) --ATF_J3: to make sure dummy packing MMTT lines not selected
1199:
1200: ;
1476: --Also delete corresponding WDT and MMTTs. bug # 2503594
1477: DELETE FROM wms_dispatched_tasks
1478: WHERE transaction_temp_id IN(
1479: SELECT transaction_temp_id
1480: FROM mtl_material_transactions_temp
1481: WHERE move_order_line_id IN(
1482: SELECT mol.line_id
1483: FROM mtl_txn_request_lines mol
1484: WHERE mol.lpn_id = l_lpn_id
1498:
1499: DELETE FROM mtl_transaction_lots_temp mtlt
1500: WHERE EXISTS(
1501: SELECT 1
1502: FROM mtl_material_transactions_temp mmtt
1503: WHERE mtlt.transaction_temp_id = mmtt.transaction_temp_id
1504: AND mmtt.move_order_line_id IN(
1505: SELECT mol.line_id
1506: FROM mtl_txn_request_lines mol
1513: FROM mtl_txn_request_headers moh
1514: WHERE mol.header_id = moh.header_id
1515: AND moh.move_order_type = inv_globals.g_move_order_put_away)));
1516:
1517: DELETE FROM mtl_material_transactions_temp
1518: WHERE move_order_line_id IN(
1519: SELECT mol.line_id
1520: FROM mtl_txn_request_lines mol
1521: WHERE mol.lpn_id = l_lpn_id
2263: -- Need to consider quantity_delivered
2264:
2265: UPDATE mtl_txn_request_lines mol
2266: SET mol.quantity_detailed = (SELECT NVL(mol.quantity_delivered, 0) + NVL(SUM(mmtt.transaction_quantity), 0)
2267: FROM mtl_material_transactions_temp mmtt
2268: WHERE mmtt.move_order_line_id = l_line_id
2269: AND transaction_action_id NOT IN (50, 51, 52)
2270: AND NOT (transaction_action_id = 2
2271: AND transaction_source_type_id = 13) -- this is to make sure the dummy MMTT erwin created does not contribute this calculation
2270: AND NOT (transaction_action_id = 2
2271: AND transaction_source_type_id = 13) -- this is to make sure the dummy MMTT erwin created does not contribute this calculation
2272: ),
2273: mol.secondary_quantity_detailed = (SELECT NVL(mol.secondary_quantity_delivered, 0) + NVL(SUM(mmtt.secondary_transaction_quantity), 0)
2274: FROM mtl_material_transactions_temp mmtt
2275: WHERE mmtt.move_order_line_id = l_line_id
2276: AND transaction_action_id NOT IN (50, 51, 52)
2277: AND NOT (transaction_action_id = 2
2278: AND transaction_source_type_id = 13) -- this is to make sure the dummy MMTT erwin created does not contribute this calculation
2371: , l_transaction_source_id
2372: FROM wip_lpn_completions
2373: WHERE header_id = l_ref_id;
2374:
2375: UPDATE mtl_material_transactions_temp
2376: SET completion_transaction_id = l_completion_txn_id
2377: , flow_schedule = l_flow_schedule
2378: , transaction_source_id = l_transaction_source_id
2379: WHERE transaction_temp_id = l_transaction_temp_id;
2384: SELECT txn_source_id INTO l_transaction_source_id
2385: FROM mtl_txn_request_lines
2386: WHERE line_id = l_line_id;
2387:
2388: UPDATE mtl_material_transactions_temp
2389: SET flow_schedule = 'N'
2390: , transaction_source_id = l_transaction_source_id
2391: WHERE transaction_temp_id = l_transaction_temp_id;
2392: END IF;
2479:
2480: --inv_debug.message('number of rows = ' || l_num_of_rows);
2481:
2482: IF l_transaction_quantity < 0 THEN
2483: UPDATE mtl_material_transactions_temp
2484: SET transaction_quantity = ABS(transaction_quantity)
2485: , primary_quantity = ABS(primary_quantity)
2486: WHERE transaction_temp_id = l_transaction_temp_id;
2487: END IF;
2496: -- set output variables
2497: -- Get total number of rows detailed
2498: SELECT COUNT(t.transaction_temp_id)
2499: INTO l_rows_detailed
2500: FROM mtl_material_transactions_temp t, mtl_txn_request_lines l
2501: WHERE l.lpn_id = l_lpn_id
2502: AND l.organization_id = p_org_id
2503: AND l.line_id = NVL(p_move_order_line_id, l.line_id) -- Added for ATF_J
2504: AND l.line_id = t.move_order_line_id;
3433: l_serial_transaction_temp_id NUMBER;
3434: l_txn_type_id NUMBER;--Added bug 3978111
3435: -- End variables added for ATF_J3
3436:
3437: l_secondary_uom_code mtl_material_transactions_temp.secondary_uom_code%TYPE;
3438: l_secondary_qty NUMBER;
3439:
3440: l_exist_msnt NUMBER:=0 ;--Added for bug 5213359
3441:
3915: , l_cost_group_id -- BUG 4134432
3916: , l_secondary_uom_code --OPM Convergence
3917:
3918: FROM mtl_txn_request_lines mol
3919: , mtl_material_transactions_temp mmtt
3920: WHERE mmtt.transaction_temp_id = p_temp_id
3921: AND mmtt.move_order_line_id = mol.line_id;
3922:
3923: IF (l_debug = 1) THEN
4021: , from_name => NULL
4022: , to_name => NULL
4023: );
4024:
4025: UPDATE mtl_material_transactions_temp
4026: SET transaction_uom = p_uom
4027: , transaction_quantity = l_qty
4028: WHERE transaction_temp_id = p_temp_id;
4029: END IF;
4039: IF (l_debug = 1) THEN
4040: mydebug('complete_putaway: Updating transfer sub and loc');
4041: END IF;
4042:
4043: UPDATE mtl_material_transactions_temp
4044: SET transfer_subinventory = p_sub
4045: , transfer_to_location = p_loc
4046: WHERE transaction_temp_id = p_temp_id;
4047: END IF;
4062: mydebug('complete_putaway: RCV LPN, No updates of sub and loc for receving lpn');
4063: END IF;
4064: ELSE
4065:
4066: UPDATE mtl_material_transactions_temp
4067: SET subinventory_code = p_sub
4068: , locator_id = p_loc
4069: WHERE transaction_temp_id = p_temp_id;
4070: END IF; -- END IF l_lpn_context = 3 THEN
4311: IF (l_debug = 1) THEN
4312: mydebug('l_flow_schedule => ' || l_flow_schedule);
4313: END IF;
4314:
4315: UPDATE mtl_material_transactions_temp
4316: SET flow_schedule = l_flow_schedule
4317: WHERE transaction_temp_id = p_temp_id;
4318: --End Bug 6356147
4319: ELSE
4974: );
4975: END IF;
4976:
4977: --Bug 3989684 Start
4978: UPDATE mtl_material_transactions_temp
4979: SET transaction_header_id = l_txn_header_id
4980: WHERE transaction_temp_id = p_temp_id;
4981: --Bug 3989684 End
4982:
5088: --This is Receiving LPN, we have to update header_id as we need
5089: -- one for each txn
5090: --We always will generate new txn header id for a receiving lpn due to the
5091: --problem in join between WDTH and MMT for the deliver transaction
5092: UPDATE mtl_material_transactions_temp
5093: SET transaction_header_id = l_txn_header_id
5094: WHERE transaction_temp_id = p_temp_id;
5095: ELSE
5096: IF (l_debug = 1) THEN
5100: IF (l_debug =1 ) THEN
5101: mydebug('Update only transfer LPN ID to NULL');
5102: END IF;
5103:
5104: UPDATE mtl_material_transactions_temp
5105: SET transaction_header_id = l_txn_header_id
5106: WHERE transaction_temp_id = p_temp_id;
5107: END IF; --IF ((l_del_detail_id IS NOT NULL) AND(l_crossdock_type = 2) AND(l_wip_supply_type = 1))
5108: ELSE
5114: IF l_debug = 1 THEN
5115: mydebug('Updating content LPN ID for inventory/WIP LPNs ');
5116: END IF;
5117:
5118: UPDATE mtl_material_transactions_temp
5119: SET transaction_header_id = l_txn_header_id
5120: WHERE transaction_temp_id = p_temp_id;
5121:
5122: IF (l_debug = 1) THEN
5211: l_wf := 0;
5212:
5213: IF (p_loc_reason_id > 0) THEN
5214: -- update MMTT with reason id = p_loc_reason_id. (bugfix 4294713)
5215: update mtl_material_transactions_temp
5216: set reason_id = p_loc_reason_id
5217: where transaction_temp_id = p_temp_id;
5218:
5219: BEGIN
5299: -- shown in the putaway drop page again.
5300: -- In putaway drop page, we make sure those MMTT
5301: -- with wms_task_type of -1 not selected.
5302:
5303: UPDATE mtl_material_transactions_temp
5304: SET wms_task_type = -1
5305: WHERE transaction_temp_id = p_temp_id;
5306:
5307: IF (l_debug = 1) THEN
5494: SELECT 1
5495: INTO cnt
5496: FROM DUAL
5497: WHERE EXISTS (SELECT 1
5498: FROM mtl_material_transactions_temp
5499: WHERE lpn_id = p_lpn_id
5500: AND organization_id = p_org_id
5501: AND inventory_item_id = p_item_id)
5502: OR EXISTS (SELECT 1
5499: WHERE lpn_id = p_lpn_id
5500: AND organization_id = p_org_id
5501: AND inventory_item_id = p_item_id)
5502: OR EXISTS (SELECT 1
5503: FROM mtl_material_transactions_temp
5504: WHERE content_lpn_id = p_lpn_id
5505: AND organization_id = p_org_id
5506: AND inventory_item_id = p_item_id);
5507: EXCEPTION
5562: mydebug(' Update MMTT contet LPN_ID WITH ' || p_lpn_id);
5563: mydebug(' Update MMTT cost_group_id with ' || l_cost_group_id);
5564: END IF;
5565:
5566: UPDATE mtl_material_transactions_temp
5567: SET transaction_header_id = l_txn_header_id
5568: , content_lpn_id = p_lpn_id
5569: , lpn_id = NULL
5570: , transaction_status = 3
5581: mydebug(' Update MMTT to lpn_id with ' || l_to_lpn_id);
5582: mydebug(' Update MMTT cost_group_id with ' || l_cost_group_id);
5583: END IF;
5584:
5585: UPDATE mtl_material_transactions_temp
5586: SET transaction_header_id = l_txn_header_id
5587: , lpn_id = p_lpn_id
5588: , content_lpn_id = NULL
5589: , transfer_lpn_id = l_to_lpn_id
5685:
5686:
5687: IF (p_loc_reason_id > 0) THEN
5688: -- update MMTT with reason id = p_loc_reason_id.(bugfix 4294713)
5689: update mtl_material_transactions_temp
5690: set reason_id = p_loc_reason_id
5691: where transaction_temp_id = p_temp_id;
5692:
5693: BEGIN
5955: INTO l_orig_qty
5956: , l_orig_uom
5957: , l_item_id
5958: , l_line_id
5959: FROM mtl_material_transactions_temp
5960: WHERE transaction_temp_id = l_temp_id
5961: AND organization_id = l_org_id;
5962:
5963: IF (l_uom <> l_orig_uom) THEN
5987: , p_from_unit => l_orig_uom);
5988:
5989: -- Update MMTT with new values for txn and primary qty
5990:
5991: UPDATE mtl_material_transactions_temp
5992: SET transaction_quantity = transaction_quantity - l_qty_diff
5993: , primary_quantity = primary_quantity - l_qty_diff_prim
5994: WHERE transaction_temp_id = l_temp_id
5995: AND organization_id = l_org_id;
6083:
6084: -- This cursor will get the MMTTs assoicated with the LPN passed
6085: CURSOR c_lpn_mmtt_cursor IS
6086: SELECT mmtt.transaction_temp_id
6087: FROM mtl_material_transactions_temp mmtt
6088: ,mtl_txn_request_lines mtrl
6089: WHERE mtrl.line_id = mmtt.move_order_line_id
6090: AND mtrl.line_status = 7
6091: AND mtrl.lpn_id = p_lpn_id
6493: FROM DUAL
6494: WHERE EXISTS(
6495: SELECT 1
6496: FROM wms_dispatched_tasks wdt
6497: , mtl_material_transactions_temp mmtt
6498: , mtl_txn_request_lines mtrl
6499: WHERE mtrl.lpn_id = l_lpn_id
6500: AND mtrl.organization_id = l_org_id
6501: AND mtrl.line_id = mmtt.move_order_line_id
6507: /*
6508: SELECT 1 INTO l_mo_cnt2 FROM DUAL WHERE exists
6509: (SELECT 1
6510: FROM mtl_txn_request_lines mol,
6511: mtl_material_transactions_temp mmtt
6512: , wms_dispatched_tasks wdt
6513: WHERE mol.lpn_id=l_lpn_id
6514: AND mol.organization_id=l_org_id
6515: AND mol.line_id=mmtt.move_order_line_id
6584: , l_mmtt_qty
6585: , l_txn_header_id
6586: , l_transaction_batch_id
6587: , l_transaction_batch_seq
6588: FROM mtl_material_transactions_temp
6589: WHERE transaction_temp_id = l_temp_id
6590: AND organization_id = l_org_id;
6591:
6592: IF (l_debug = 1) THEN
6649: NULL;
6650: END;
6651:
6652: -- Delete this row from mmtt
6653: DELETE mtl_material_transactions_temp
6654: WHERE transaction_temp_id = l_temp_id;
6655:
6656: x_return_status := fnd_api.g_ret_sts_success;
6657:
6702: transaction_batch_seq
6703: --End of fix for Bug 3961107
6704: INTO l_transaction_batch_id
6705: , l_transaction_batch_seq
6706: FROM mtl_material_transactions_temp
6707: WHERE transaction_temp_id = l_temp_id
6708: AND organization_id = l_org_id;
6709:
6710: IF (l_debug = 1) THEN
6773: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6774: END;
6775:
6776: BEGIN
6777: DELETE mtl_material_transactions_temp
6778: WHERE transaction_temp_id = l_temp_id;
6779: EXCEPTION
6780: WHEN OTHERS THEN
6781: mydebug('archive_task: Error deleting MMTT: ' || sqlerrm);
6895: , l_demand_source_header_id
6896: , l_repetitive_line_id
6897: , l_operation_seq_num
6898: , l_secondary_quantity --OPM Convergence
6899: FROM mtl_material_transactions_temp t, wms_dispatched_tasks w
6900: WHERE t.transaction_temp_id = l_temp_id
6901: AND t.organization_id = l_org_id
6902: AND t.transaction_temp_id = w.transaction_temp_id;
6903:
7225: INTO l_count
7226: FROM DUAL
7227: WHERE EXISTS(
7228: SELECT 1
7229: FROM mtl_material_transactions_temp mmtt
7230: WHERE (
7231: NVL(mmtt.subinventory_code, '@') <> NVL(p_sub, NVL(mmtt.subinventory_code, '@'))
7232: OR NVL(mmtt.locator_id, '0') <> NVL(p_loc_id, NVL(mmtt.locator_id, '0'))
7233: )
7337: WHERE mol.lpn_id = l_to_lpn_id
7338: AND mol.line_status <> inv_globals.g_to_status_closed
7339: AND (
7340: (mol.quantity - NVL(mol.quantity_delivered, 0)) > (SELECT SUM(mmtt.transaction_quantity)
7341: FROM mtl_material_transactions_temp mmtt
7342: WHERE mmtt.move_order_line_id = mol.line_id)
7343: OR(mol.quantity - NVL(mol.quantity_delivered, 0) > 0
7344: AND NOT EXISTS(SELECT 1
7345: FROM mtl_material_transactions_temp mmtt
7341: FROM mtl_material_transactions_temp mmtt
7342: WHERE mmtt.move_order_line_id = mol.line_id)
7343: OR(mol.quantity - NVL(mol.quantity_delivered, 0) > 0
7344: AND NOT EXISTS(SELECT 1
7345: FROM mtl_material_transactions_temp mmtt
7346: WHERE mmtt.move_order_line_id = mol.line_id))
7347: );
7348:
7349: IF (l_debug = 1) THEN
7492: , l_item_id
7493: , l_sub
7494: , l_loc
7495: , l_lpn_id
7496: FROM mtl_system_items msi, mtl_material_transactions_temp mmtt
7497: WHERE mmtt.organization_id = l_org_id
7498: AND mmtt.transaction_temp_id = l_temp_id
7499: AND msi.organization_id = mmtt.organization_id
7500: AND msi.inventory_item_id = mmtt.inventory_item_id;
7965:
7966: CURSOR qty_check_cursor IS
7967: SELECT mtrl.primary_quantity
7968: , mmtt.primary_quantity
7969: FROM mtl_txn_request_lines mtrl, mtl_material_transactions_temp mmtt
7970: WHERE mtrl.organization_id = p_organization_id
7971: AND mtrl.lpn_id = l_lpn_id
7972: AND mmtt.move_order_line_id = mtrl.line_id;
7973:
8070: DELETE FROM wms_dispatched_tasks
8071: WHERE task_type = 2
8072: AND transaction_temp_id IN
8073: (SELECT transaction_temp_id
8074: FROM mtl_material_transactions_temp
8075: WHERE move_order_line_id IN
8076: (SELECT line_id
8077: FROM mtl_txn_request_lines
8078: WHERE organization_id = p_organization_id
8081:
8082: DELETE FROM mtl_transaction_lots_temp
8083: WHERE transaction_temp_id IN
8084: (SELECT transaction_temp_id
8085: FROM mtl_material_transactions_temp
8086: WHERE move_order_line_id IN
8087: (SELECT line_id
8088: FROM mtl_txn_request_lines
8089: WHERE organization_id = p_organization_id
8088: FROM mtl_txn_request_lines
8089: WHERE organization_id = p_organization_id
8090: AND lpn_id = l_lpn_id));
8091:
8092: DELETE FROM mtl_material_transactions_temp
8093: WHERE move_order_line_id IN
8094: (SELECT line_id
8095: FROM mtl_txn_request_lines
8096: WHERE organization_id = p_organization_id
8180:
8181: -- Check to see if MMTT allocations were created
8182: SELECT COUNT(mmtt.transaction_temp_id)
8183: INTO l_mmtt_lines_count
8184: FROM mtl_material_transactions_temp mmtt, mtl_txn_request_lines mtrl
8185: WHERE mtrl.lpn_id = l_lpn_id
8186: AND mtrl.organization_id = p_organization_id
8187: AND NVL(mmtt.wms_task_type, 0) <> -1
8188: AND mtrl.line_id = mmtt.move_order_line_id
8565: l_mmtt_uom_tab,
8566: l_mmtt_mol_id_tab,
8567: l_mmtt_sec_qty_tab, --OPM Convergence
8568: l_mmtt_sec_uom_tab --OPM Convergence
8569: FROM mtl_material_transactions_temp mmtt,
8570: mtl_txn_request_lines mtrl
8571: WHERE mmtt.organization_id = p_organization_id
8572: AND mmtt.move_order_line_id = mtrl.line_id
8573: AND mtrl.lpn_id IN (SELECT lpn_id
8690: l_progress := '52';
8691: BEGIN
8692: SELECT DISTINCT operation_seq_num, repetitive_line_id
8693: INTO l_operation_seq_num,l_repetitive_line_id
8694: FROM mtl_material_transactions_temp
8695: WHERE move_order_line_id = v_mo_line.line_id;
8696: IF (l_debug = 1) THEN
8697: mydebug(' operation_seq_num ===> '|| l_operation_seq_num);
8698: mydebug(' repetive_line_id ====> '|| l_repetitive_line_id);
8843: -- Update the MMTT record with the move order line,
8844: -- completion transaction ID in the case of WIP completion,
8845: -- item primary UOM code, transaction status (2 for suggestions),
8846: -- and wms task type (2 for putaway).
8847: UPDATE mtl_material_transactions_temp
8848: SET move_order_line_id = v_mo_line.line_id
8849: , completion_transaction_id = l_completion_txn_id
8850: , item_primary_uom_code = l_primary_uom_code
8851: , transaction_status = 2
8944: END LOOP;
8945:
8946: -- Delete MMTT.
8947: FORALL i in 1..l_mmtt_id_tab.COUNT
8948: DELETE FROM mtl_material_transactions_temp
8949: WHERE transaction_temp_id = l_mmtt_id_tab(i);
8950:
8951: IF (l_debug = 1) THEN
8952: mydebug('Finished cleaning up old WDT, MTLT, and MMTT records');
9277: , mmtt.transaction_quantity
9278: , mmtt.transaction_uom
9279: , mmtt.parent_line_id --6962664
9280: , mmtt.transaction_header_id --6962664
9281: FROM mtl_material_transactions_temp mmtt,
9282: mtl_txn_request_lines mtrl
9283: WHERE mmtt.move_order_line_id = mtrl.line_id
9284: AND mmtt.organization_id = p_organization_id
9285: AND mmtt.lpn_id = l_current_lpn_id
9316: IF l_locator_id IS NULL THEN
9317: BEGIN
9318: mydebug('Getting locator value using parent mmtt as locator value is null');
9319: SELECT locator_id INTO l_locator_id
9320: FROM mtl_material_transactions_temp
9321: WHERE transaction_temp_id = l_parent_line_id
9322: AND transaction_header_id = l_txn_header_id;
9323:
9324: EXCEPTION