[Home] [Help]
1016: , mol.to_locator_id mol_loc_id
1017: , mol.backorder_delivery_detail_id
1018: , mmtt.operation_plan_id
1019: , mol.inspection_status
1020: FROM mtl_material_transactions_temp mmtt, mtl_txn_request_lines mol
1021: WHERE mmtt.lpn_id = l_lpn_id
1022: -- Added following line for ATF_J
1023: AND mmtt.move_order_line_id = NVL(p_move_order_line_id, mmtt.move_order_line_id)
1024: AND mmtt.organization_id = p_org_id
1054: , task_priority
1055: , NVL(wms_task_type, 2)
1056: , operation_plan_id
1057: , move_order_line_id
1058: FROM mtl_material_transactions_temp
1059: WHERE move_order_line_id = l_line_id
1060: AND transaction_action_id NOT IN (50, 51, 52) --ATF_J3: to make sure dummy packing MMTT lines not selected
1061:
1062: ;
1331: --Also delete corresponding WDT and MMTTs. bug # 2503594
1332: DELETE FROM wms_dispatched_tasks
1333: WHERE transaction_temp_id IN(
1334: SELECT transaction_temp_id
1335: FROM mtl_material_transactions_temp
1336: WHERE move_order_line_id IN(
1337: SELECT mol.line_id
1338: FROM mtl_txn_request_lines mol
1339: WHERE mol.lpn_id = l_lpn_id
1353:
1354: DELETE FROM mtl_transaction_lots_temp mtlt
1355: WHERE EXISTS(
1356: SELECT 1
1357: FROM mtl_material_transactions_temp mmtt
1358: WHERE mtlt.transaction_temp_id = mmtt.transaction_temp_id
1359: AND mmtt.move_order_line_id IN(
1360: SELECT mol.line_id
1361: FROM mtl_txn_request_lines mol
1368: FROM mtl_txn_request_headers moh
1369: WHERE mol.header_id = moh.header_id
1370: AND moh.move_order_type = inv_globals.g_move_order_put_away)));
1371:
1372: DELETE FROM mtl_material_transactions_temp
1373: WHERE move_order_line_id IN(
1374: SELECT mol.line_id
1375: FROM mtl_txn_request_lines mol
1376: WHERE mol.lpn_id = l_lpn_id
2099: -- Need to consider quantity_delivered
2100:
2101: UPDATE mtl_txn_request_lines mol
2102: SET mol.quantity_detailed = (SELECT NVL(mol.quantity_delivered, 0) + NVL(SUM(mmtt.transaction_quantity), 0)
2103: FROM mtl_material_transactions_temp mmtt
2104: WHERE mmtt.move_order_line_id = l_line_id
2105: AND transaction_action_id NOT IN (50, 51, 52)
2106: AND NOT (transaction_action_id = 2
2107: AND transaction_source_type_id = 13) -- this is to make sure the dummy MMTT erwin created does not contribute this calculation
2199: , l_transaction_source_id
2200: FROM wip_lpn_completions
2201: WHERE header_id = l_ref_id;
2202:
2203: UPDATE mtl_material_transactions_temp
2204: SET completion_transaction_id = l_completion_txn_id
2205: , flow_schedule = l_flow_schedule
2206: , transaction_source_id = l_transaction_source_id
2207: WHERE transaction_temp_id = l_transaction_temp_id;
2212: SELECT txn_source_id INTO l_transaction_source_id
2213: FROM mtl_txn_request_lines
2214: WHERE line_id = l_line_id;
2215:
2216: UPDATE mtl_material_transactions_temp
2217: SET flow_schedule = 'N'
2218: , transaction_source_id = l_transaction_source_id
2219: WHERE transaction_temp_id = l_transaction_temp_id;
2220: END IF;
2307:
2308: --inv_debug.message('number of rows = ' || l_num_of_rows);
2309:
2310: IF l_transaction_quantity < 0 THEN
2311: UPDATE mtl_material_transactions_temp
2312: SET transaction_quantity = ABS(transaction_quantity)
2313: , primary_quantity = ABS(primary_quantity)
2314: WHERE transaction_temp_id = l_transaction_temp_id;
2315: END IF;
2324: -- set output variables
2325: -- Get total number of rows detailed
2326: SELECT COUNT(t.transaction_temp_id)
2327: INTO l_rows_detailed
2328: FROM mtl_material_transactions_temp t, mtl_txn_request_lines l
2329: WHERE l.lpn_id = l_lpn_id
2330: AND l.organization_id = p_org_id
2331: AND l.line_id = NVL(p_move_order_line_id, l.line_id) -- Added for ATF_J
2332: AND l.line_id = t.move_order_line_id;
3229: l_serial_transaction_temp_id NUMBER;
3230: l_txn_type_id NUMBER;--Added bug 3978111
3231: -- End variables added for ATF_J3
3232:
3233: l_secondary_uom_code mtl_material_transactions_temp.secondary_uom_code%TYPE;
3234: l_secondary_qty NUMBER;
3235:
3236: l_exist_msnt NUMBER:=0 ;--Added for bug 5213359
3237:
3710: , l_cost_group_id -- BUG 4134432
3711: , l_secondary_uom_code --OPM Convergence
3712:
3713: FROM mtl_txn_request_lines mol
3714: , mtl_material_transactions_temp mmtt
3715: WHERE mmtt.transaction_temp_id = p_temp_id
3716: AND mmtt.move_order_line_id = mol.line_id;
3717:
3718: IF (l_debug = 1) THEN
3816: , from_name => NULL
3817: , to_name => NULL
3818: );
3819:
3820: UPDATE mtl_material_transactions_temp
3821: SET transaction_uom = p_uom
3822: , transaction_quantity = l_qty
3823: WHERE transaction_temp_id = p_temp_id;
3824: END IF;
3834: IF (l_debug = 1) THEN
3835: mydebug('complete_putaway: Updating transfer sub and loc');
3836: END IF;
3837:
3838: UPDATE mtl_material_transactions_temp
3839: SET transfer_subinventory = p_sub
3840: , transfer_to_location = p_loc
3841: WHERE transaction_temp_id = p_temp_id;
3842: END IF;
3857: mydebug('complete_putaway: RCV LPN, No updates of sub and loc for receving lpn');
3858: END IF;
3859: ELSE
3860:
3861: UPDATE mtl_material_transactions_temp
3862: SET subinventory_code = p_sub
3863: , locator_id = p_loc
3864: WHERE transaction_temp_id = p_temp_id;
3865: END IF; -- END IF l_lpn_context = 3 THEN
4096: IF (l_debug = 1) THEN
4097: mydebug('l_flow_schedule => ' || l_flow_schedule);
4098: END IF;
4099:
4100: UPDATE mtl_material_transactions_temp
4101: SET flow_schedule = l_flow_schedule
4102: WHERE transaction_temp_id = p_temp_id;
4103: --End Bug 6356147
4104: ELSE
4748: );
4749: END IF;
4750:
4751: --Bug 3989684 Start
4752: UPDATE mtl_material_transactions_temp
4753: SET transaction_header_id = l_txn_header_id
4754: WHERE transaction_temp_id = p_temp_id;
4755: --Bug 3989684 End
4756:
4862: --This is Receiving LPN, we have to update header_id as we need
4863: -- one for each txn
4864: --We always will generate new txn header id for a receiving lpn due to the
4865: --problem in join between WDTH and MMT for the deliver transaction
4866: UPDATE mtl_material_transactions_temp
4867: SET transaction_header_id = l_txn_header_id
4868: WHERE transaction_temp_id = p_temp_id;
4869: ELSE
4870: IF (l_debug = 1) THEN
4874: IF (l_debug =1 ) THEN
4875: mydebug('Update only transfer LPN ID to NULL');
4876: END IF;
4877:
4878: UPDATE mtl_material_transactions_temp
4879: SET transaction_header_id = l_txn_header_id
4880: WHERE transaction_temp_id = p_temp_id;
4881: END IF; --IF ((l_del_detail_id IS NOT NULL) AND(l_crossdock_type = 2) AND(l_wip_supply_type = 1))
4882: ELSE
4888: IF l_debug = 1 THEN
4889: mydebug('Updating content LPN ID for inventory/WIP LPNs ');
4890: END IF;
4891:
4892: UPDATE mtl_material_transactions_temp
4893: SET transaction_header_id = l_txn_header_id
4894: WHERE transaction_temp_id = p_temp_id;
4895:
4896: IF (l_debug = 1) THEN
4985: l_wf := 0;
4986:
4987: IF (p_loc_reason_id > 0) THEN
4988: -- update MMTT with reason id = p_loc_reason_id. (bugfix 4294713)
4989: update mtl_material_transactions_temp
4990: set reason_id = p_loc_reason_id
4991: where transaction_temp_id = p_temp_id;
4992:
4993: BEGIN
5073: -- shown in the putaway drop page again.
5074: -- In putaway drop page, we make sure those MMTT
5075: -- with wms_task_type of -1 not selected.
5076:
5077: UPDATE mtl_material_transactions_temp
5078: SET wms_task_type = -1
5079: WHERE transaction_temp_id = p_temp_id;
5080:
5081: IF (l_debug = 1) THEN
5248: SELECT 1
5249: INTO cnt
5250: FROM DUAL
5251: WHERE EXISTS (SELECT 1
5252: FROM mtl_material_transactions_temp
5253: WHERE lpn_id = p_lpn_id
5254: AND organization_id = p_org_id
5255: AND inventory_item_id = p_item_id)
5256: OR EXISTS (SELECT 1
5253: WHERE lpn_id = p_lpn_id
5254: AND organization_id = p_org_id
5255: AND inventory_item_id = p_item_id)
5256: OR EXISTS (SELECT 1
5257: FROM mtl_material_transactions_temp
5258: WHERE content_lpn_id = p_lpn_id
5259: AND organization_id = p_org_id
5260: AND inventory_item_id = p_item_id);
5261: EXCEPTION
5316: mydebug(' Update MMTT contet LPN_ID WITH ' || p_lpn_id);
5317: mydebug(' Update MMTT cost_group_id with ' || l_cost_group_id);
5318: END IF;
5319:
5320: UPDATE mtl_material_transactions_temp
5321: SET transaction_header_id = l_txn_header_id
5322: , content_lpn_id = p_lpn_id
5323: , lpn_id = NULL
5324: , transaction_status = 3
5335: mydebug(' Update MMTT to lpn_id with ' || l_to_lpn_id);
5336: mydebug(' Update MMTT cost_group_id with ' || l_cost_group_id);
5337: END IF;
5338:
5339: UPDATE mtl_material_transactions_temp
5340: SET transaction_header_id = l_txn_header_id
5341: , lpn_id = p_lpn_id
5342: , content_lpn_id = NULL
5343: , transfer_lpn_id = l_to_lpn_id
5439:
5440:
5441: IF (p_loc_reason_id > 0) THEN
5442: -- update MMTT with reason id = p_loc_reason_id.(bugfix 4294713)
5443: update mtl_material_transactions_temp
5444: set reason_id = p_loc_reason_id
5445: where transaction_temp_id = p_temp_id;
5446:
5447: BEGIN
5678: INTO l_orig_qty
5679: , l_orig_uom
5680: , l_item_id
5681: , l_line_id
5682: FROM mtl_material_transactions_temp
5683: WHERE transaction_temp_id = l_temp_id
5684: AND organization_id = l_org_id;
5685:
5686: IF (l_uom <> l_orig_uom) THEN
5710: , p_from_unit => l_orig_uom);
5711:
5712: -- Update MMTT with new values for txn and primary qty
5713:
5714: UPDATE mtl_material_transactions_temp
5715: SET transaction_quantity = transaction_quantity - l_qty_diff
5716: , primary_quantity = primary_quantity - l_qty_diff_prim
5717: WHERE transaction_temp_id = l_temp_id
5718: AND organization_id = l_org_id;
5805:
5806: -- This cursor will get the MMTTs assoicated with the LPN passed
5807: CURSOR c_lpn_mmtt_cursor IS
5808: SELECT mmtt.transaction_temp_id
5809: FROM mtl_material_transactions_temp mmtt
5810: ,mtl_txn_request_lines mtrl
5811: WHERE mtrl.line_id = mmtt.move_order_line_id
5812: AND mtrl.line_status = 7
5813: AND mtrl.lpn_id = p_lpn_id
6166: FROM DUAL
6167: WHERE EXISTS(
6168: SELECT 1
6169: FROM wms_dispatched_tasks wdt
6170: , mtl_material_transactions_temp mmtt
6171: , mtl_txn_request_lines mtrl
6172: WHERE mtrl.lpn_id = l_lpn_id
6173: AND mtrl.organization_id = l_org_id
6174: AND mtrl.line_id = mmtt.move_order_line_id
6180: /*
6181: SELECT 1 INTO l_mo_cnt2 FROM DUAL WHERE exists
6182: (SELECT 1
6183: FROM mtl_txn_request_lines mol,
6184: mtl_material_transactions_temp mmtt
6185: , wms_dispatched_tasks wdt
6186: WHERE mol.lpn_id=l_lpn_id
6187: AND mol.organization_id=l_org_id
6188: AND mol.line_id=mmtt.move_order_line_id
6257: , l_mmtt_qty
6258: , l_txn_header_id
6259: , l_transaction_batch_id
6260: , l_transaction_batch_seq
6261: FROM mtl_material_transactions_temp
6262: WHERE transaction_temp_id = l_temp_id
6263: AND organization_id = l_org_id;
6264:
6265: IF (l_debug = 1) THEN
6322: NULL;
6323: END;
6324:
6325: -- Delete this row from mmtt
6326: DELETE mtl_material_transactions_temp
6327: WHERE transaction_temp_id = l_temp_id;
6328:
6329: x_return_status := fnd_api.g_ret_sts_success;
6330:
6375: transaction_batch_seq
6376: --End of fix for Bug 3961107
6377: INTO l_transaction_batch_id
6378: , l_transaction_batch_seq
6379: FROM mtl_material_transactions_temp
6380: WHERE transaction_temp_id = l_temp_id
6381: AND organization_id = l_org_id;
6382:
6383: IF (l_debug = 1) THEN
6446: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6447: END;
6448:
6449: BEGIN
6450: DELETE mtl_material_transactions_temp
6451: WHERE transaction_temp_id = l_temp_id;
6452: EXCEPTION
6453: WHEN OTHERS THEN
6454: mydebug('archive_task: Error deleting MMTT: ' || sqlerrm);
6568: , l_demand_source_header_id
6569: , l_repetitive_line_id
6570: , l_operation_seq_num
6571: , l_secondary_quantity --OPM Convergence
6572: FROM mtl_material_transactions_temp t, wms_dispatched_tasks w
6573: WHERE t.transaction_temp_id = l_temp_id
6574: AND t.organization_id = l_org_id
6575: AND t.transaction_temp_id = w.transaction_temp_id;
6576:
6898: INTO l_count
6899: FROM DUAL
6900: WHERE EXISTS(
6901: SELECT 1
6902: FROM mtl_material_transactions_temp mmtt
6903: WHERE (
6904: NVL(mmtt.subinventory_code, '@') <> NVL(p_sub, NVL(mmtt.subinventory_code, '@'))
6905: OR NVL(mmtt.locator_id, '0') <> NVL(p_loc_id, NVL(mmtt.locator_id, '0'))
6906: )
7010: WHERE mol.lpn_id = l_to_lpn_id
7011: AND mol.line_status <> inv_globals.g_to_status_closed
7012: AND (
7013: (mol.quantity - NVL(mol.quantity_delivered, 0)) > (SELECT SUM(mmtt.transaction_quantity)
7014: FROM mtl_material_transactions_temp mmtt
7015: WHERE mmtt.move_order_line_id = mol.line_id)
7016: OR(mol.quantity - NVL(mol.quantity_delivered, 0) > 0
7017: AND NOT EXISTS(SELECT 1
7018: FROM mtl_material_transactions_temp mmtt
7014: FROM mtl_material_transactions_temp mmtt
7015: WHERE mmtt.move_order_line_id = mol.line_id)
7016: OR(mol.quantity - NVL(mol.quantity_delivered, 0) > 0
7017: AND NOT EXISTS(SELECT 1
7018: FROM mtl_material_transactions_temp mmtt
7019: WHERE mmtt.move_order_line_id = mol.line_id))
7020: );
7021:
7022: IF (l_debug = 1) THEN
7165: , l_item_id
7166: , l_sub
7167: , l_loc
7168: , l_lpn_id
7169: FROM mtl_system_items msi, mtl_material_transactions_temp mmtt
7170: WHERE mmtt.organization_id = l_org_id
7171: AND mmtt.transaction_temp_id = l_temp_id
7172: AND msi.organization_id = mmtt.organization_id
7173: AND msi.inventory_item_id = mmtt.inventory_item_id;
7628:
7629: CURSOR qty_check_cursor IS
7630: SELECT mtrl.primary_quantity
7631: , mmtt.primary_quantity
7632: FROM mtl_txn_request_lines mtrl, mtl_material_transactions_temp mmtt
7633: WHERE mtrl.organization_id = p_organization_id
7634: AND mtrl.lpn_id = l_lpn_id
7635: AND mmtt.move_order_line_id = mtrl.line_id;
7636:
7733: DELETE FROM wms_dispatched_tasks
7734: WHERE task_type = 2
7735: AND transaction_temp_id IN
7736: (SELECT transaction_temp_id
7737: FROM mtl_material_transactions_temp
7738: WHERE move_order_line_id IN
7739: (SELECT line_id
7740: FROM mtl_txn_request_lines
7741: WHERE organization_id = p_organization_id
7744:
7745: DELETE FROM mtl_transaction_lots_temp
7746: WHERE transaction_temp_id IN
7747: (SELECT transaction_temp_id
7748: FROM mtl_material_transactions_temp
7749: WHERE move_order_line_id IN
7750: (SELECT line_id
7751: FROM mtl_txn_request_lines
7752: WHERE organization_id = p_organization_id
7751: FROM mtl_txn_request_lines
7752: WHERE organization_id = p_organization_id
7753: AND lpn_id = l_lpn_id));
7754:
7755: DELETE FROM mtl_material_transactions_temp
7756: WHERE move_order_line_id IN
7757: (SELECT line_id
7758: FROM mtl_txn_request_lines
7759: WHERE organization_id = p_organization_id
7842:
7843: -- Check to see if MMTT allocations were created
7844: SELECT COUNT(mmtt.transaction_temp_id)
7845: INTO l_mmtt_lines_count
7846: FROM mtl_material_transactions_temp mmtt, mtl_txn_request_lines mtrl
7847: WHERE mtrl.lpn_id = l_lpn_id
7848: AND mtrl.organization_id = p_organization_id
7849: AND NVL(mmtt.wms_task_type, 0) <> -1
7850: AND mtrl.line_id = mmtt.move_order_line_id
8224: l_mmtt_uom_tab,
8225: l_mmtt_mol_id_tab,
8226: l_mmtt_sec_qty_tab, --OPM Convergence
8227: l_mmtt_sec_uom_tab --OPM Convergence
8228: FROM mtl_material_transactions_temp mmtt,
8229: mtl_txn_request_lines mtrl
8230: WHERE mmtt.organization_id = p_organization_id
8231: AND mmtt.move_order_line_id = mtrl.line_id
8232: AND mtrl.lpn_id IN (SELECT lpn_id
8343: l_progress := '52';
8344: BEGIN
8345: SELECT DISTINCT operation_seq_num, repetitive_line_id
8346: INTO l_operation_seq_num,l_repetitive_line_id
8347: FROM mtl_material_transactions_temp
8348: WHERE move_order_line_id = v_mo_line.line_id;
8349: IF (l_debug = 1) THEN
8350: mydebug(' operation_seq_num ===> '|| l_operation_seq_num);
8351: mydebug(' repetive_line_id ====> '|| l_repetitive_line_id);
8489: -- Update the MMTT record with the move order line,
8490: -- completion transaction ID in the case of WIP completion,
8491: -- item primary UOM code, transaction status (2 for suggestions),
8492: -- and wms task type (2 for putaway).
8493: UPDATE mtl_material_transactions_temp
8494: SET move_order_line_id = v_mo_line.line_id
8495: , completion_transaction_id = l_completion_txn_id
8496: , item_primary_uom_code = l_primary_uom_code
8497: , transaction_status = 2
8587: END LOOP;
8588:
8589: -- Delete MMTT.
8590: FORALL i in 1..l_mmtt_id_tab.COUNT
8591: DELETE FROM mtl_material_transactions_temp
8592: WHERE transaction_temp_id = l_mmtt_id_tab(i);
8593:
8594: IF (l_debug = 1) THEN
8595: mydebug('Finished cleaning up old WDT, MTLT, and MMTT records');
8920: , mmtt.transaction_quantity
8921: , mmtt.transaction_uom
8922: , mmtt.parent_line_id --6962664
8923: , mmtt.transaction_header_id --6962664
8924: FROM mtl_material_transactions_temp mmtt,
8925: mtl_txn_request_lines mtrl
8926: WHERE mmtt.move_order_line_id = mtrl.line_id
8927: AND mmtt.organization_id = p_organization_id
8928: AND mmtt.lpn_id = l_current_lpn_id
8959: IF l_locator_id IS NULL THEN
8960: BEGIN
8961: mydebug('Getting locator value using parent mmtt as locator value is null');
8962: SELECT locator_id INTO l_locator_id
8963: FROM mtl_material_transactions_temp
8964: WHERE transaction_temp_id = l_parent_line_id
8965: AND transaction_header_id = l_txn_header_id;
8966:
8967: EXCEPTION