[Home] [Help]
557: l_progress := '160';
558:
559:
560: FORALL i IN 1 ..l_txn_temp_id_tab.COUNT
561: UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP
562: SET transaction_header_id = l_txn_header_id
563: WHERE transaction_temp_id = l_txn_temp_id_tab(i);
564: l_progress := '170';
565:
1945: SELECT COUNT(DISTINCT NVL(transfer_to_location,locator_id))
1946: ,COUNT(DISTINCT cartonization_id)
1947: INTO l_loc_count
1948: ,l_to_lpn_count
1949: FROM mtl_material_transactions_temp mmtt
1950: ,mtl_txn_request_lines mtrl
1951: WHERE mmtt.move_order_line_id = mtrl.line_id
1952: AND mtrl.lpn_id = l_lpn_id_tab(i);
1953: l_progress := '150';
2043: AND mtrl.lpn_id = l_lpn_id_tab(i)
2044: AND mtrl.line_status = 7 --BUG 3352572
2045: AND ((NOT exists
2046: (SELECT 1
2047: FROM mtl_material_transactions_temp mmtt1
2048: WHERE mtrl.line_status = 7
2049: AND mmtt1.move_order_line_id = mtrl.line_id)
2050: ) OR
2051: (( (NVL(quantity,0) - NVL(quantity_delivered,0) ) )
2049: AND mmtt1.move_order_line_id = mtrl.line_id)
2050: ) OR
2051: (( (NVL(quantity,0) - NVL(quantity_delivered,0) ) )
2052: > (SELECT SUM(transaction_quantity)
2053: FROM mtl_material_transactions_temp mmtt
2054: WHERE mtrl.line_status = 7
2055: AND mmtt.move_order_line_id = mtrl.line_id
2056: ))
2057: )
2342:
2343:
2344: --R12: Revert MDC suggestions for all tasks that is flagged as item drop
2345: BEGIN
2346: UPDATE mtl_material_transactions_temp
2347: SET cartonization_id = NULL
2348: WHERE transaction_temp_id IN (SELECT gtmp.transaction_temp_id
2349: FROM wms_putaway_group_tasks_gtmp gtmp
2350: WHERE gtmp.consolidation_method_id = 1 --???MDC
2503: UNION ALL
2504: SELECT wln.outermost_lpn_id
2505: FROM wms_dispatched_tasks wdt
2506: ,mtl_txn_request_lines mtrl
2507: ,mtl_material_transactions_temp mmtt
2508: ,mtl_txn_request_headers mtrh
2509: ,wms_license_plate_numbers wln
2510: WHERE p_drop_type = G_DT_DROP_ALL
2511: AND mtrh.header_id = mtrl.header_id
2757: Decode(wln.locator_id,NVL(mmtt.transfer_to_location, mmtt.locator_id),1,0) show_message,
2758: mmtt.error_code,
2759: mmtt.error_explanation
2760: --R12 End
2761: FROM mtl_material_transactions_temp mmtt
2762: ,mtl_txn_request_lines mtrl
2763: ,wms_dispatched_tasks wdt
2764: --,mtl_item_locations milk
2765: ,mtl_system_items_kfv msik
3138: FROM
3139: ( SELECT LPN_ID
3140: FROM WMS_LICENSE_PLATE_NUMBERS START WITH LPN_ID = p_lpn_id CONNECT BY PRIOR LPN_ID = PARENT_LPN_ID ) WLPN,
3141: MTL_TXN_REQUEST_LINES MTRL,
3142: MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
3143: WHERE MTRL.LINE_ID = MMTT.MOVE_ORDER_LINE_ID
3144: AND LINE_STATUS = 7
3145: AND MTRL.LPN_ID = WLPN.LPN_ID ;
3146:
3321: FROM
3322: ( SELECT LPN_ID
3323: FROM WMS_LICENSE_PLATE_NUMBERS START WITH LPN_ID = p_lpn_id CONNECT BY PRIOR LPN_ID = PARENT_LPN_ID ) WLPN,
3324: MTL_TXN_REQUEST_LINES MTRL,
3325: MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
3326: WHERE MTRL.LINE_ID = MMTT.MOVE_ORDER_LINE_ID
3327: AND LINE_STATUS = 7
3328: AND MTRL.LPN_ID = WLPN.LPN_ID ;
3329:
3659: ,wlpn.license_plate_number into_lpn_name
3660: ,msi.dropping_order sub_dropping_order
3661: ,milk.dropping_order loc_dropping_order
3662: ,Nvl(msi.lpn_controlled_flag,2) lpn_controlled_flag
3663: FROM mtl_material_transactions_temp mmtt
3664: ,wms_license_plate_numbers wlpn
3665: ,mtl_secondary_inventories msi
3666: ,mtl_item_locations_kfv milk
3667: WHERE mmtt.transaction_temp_id = v_transaction_temp_id
4593: FROM
4594: ( SELECT LPN_ID
4595: FROM WMS_LICENSE_PLATE_NUMBERS START WITH LPN_ID = p_lpn_id CONNECT BY PRIOR LPN_ID = PARENT_LPN_ID ) WLPN,
4596: MTL_TXN_REQUEST_LINES MTRL,
4597: MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
4598: WHERE MTRL.LINE_ID = MMTT.MOVE_ORDER_LINE_ID
4599: AND LINE_STATUS = 7
4600: AND MTRL.LPN_ID = WLPN.LPN_ID ;
4601:
4837:
4838: -- This cursor will get the MMTTs assoicated with the move order line passed
4839: CURSOR c_mol_mmtt_cursor IS
4840: SELECT mmtt.transaction_temp_id
4841: FROM mtl_material_transactions_temp mmtt
4842: ,mtl_txn_request_lines mtrl
4843: WHERE mtrl.line_id = mmtt.move_order_line_id
4844: AND mtrl.line_id = p_mo_line_id
4845: AND mtrl.line_status = 7
5079: -- This cursor will get the all the MMTTs assoicated with the lpn_id passed.
5080: -- Cleanup shouldn't be called if there is not WDT and hence added a join with WDT.
5081: CURSOR c_mol_mmtt_cursor IS
5082: SELECT mmtt.transaction_temp_id
5083: FROM mtl_material_transactions_temp mmtt
5084: ,mtl_txn_request_lines mtrl
5085: ,wms_dispatched_tasks wdt
5086: ,(SELECT lpn_id FROM wms_license_plate_numbers /*Bug5723418.*/
5087: START WITH lpn_id = p_lpn_id
5116: CURSOR c_all_mmtt_cursor IS
5117: SELECT mmtt.transaction_temp_id
5118: FROM wms_dispatched_tasks wdt
5119: ,mtl_txn_request_lines mtrl
5120: ,mtl_material_transactions_temp mmtt
5121: ,mtl_txn_request_headers mtrh
5122: WHERE mtrh.header_id = mtrl.header_id
5123: AND mtrh.move_order_type = 6
5124: AND mtrl.line_status = 7
5149: UNION ALL
5150: SELECT mmtt.transaction_temp_id
5151: FROM wms_dispatched_tasks wdt
5152: ,mtl_txn_request_lines mtrl
5153: ,mtl_material_transactions_temp mmtt
5154: ,mtl_txn_request_headers mtrh
5155: WHERE mtrh.header_id = mtrl.header_id
5156: AND mtrh.move_order_type = 6
5157: AND mtrl.line_status = 7
5363: , l_mol_id
5364: , l_mmtt_item_id
5365: , l_mol_uom
5366: , l_wms_process_flag
5367: FROM mtl_material_transactions_temp mmtt
5368: , mtl_txn_request_lines mtrl
5369: WHERE mmtt.transaction_temp_id = l_tempid_tab(i)
5370: AND mmtt.move_order_line_id = mtrl.line_id;
5371: EXCEPTION
5473: END IF;
5474:
5475: BEGIN
5476: --delete MMTT
5477: DELETE FROM mtl_material_transactions_temp
5478: WHERE transaction_temp_id = l_tempid_tab(i);
5479: EXCEPTION
5480: WHEN OTHERS THEN
5481: IF (l_debug = 1) THEN
5583: BEGIN
5584: UPDATE mtl_txn_request_lines
5585: SET wms_process_flag = 1
5586: WHERE line_id IN (SELECT move_order_line_id
5587: FROM mtl_material_transactions_temp
5588: WHERE transaction_temp_id = l_tempid_tab(i))
5589: AND wms_process_flag = 2;
5590: EXCEPTION
5591: WHEN OTHERS THEN
5829: , wooi.last_updated_by
5830: INTO l_drop_active
5831: , l_userid
5832: FROM mtl_txn_request_lines mtrl,
5833: mtl_material_transactions_temp mmtt,
5834: wms_op_operation_instances wooi ,
5835: (SELECT wlpn.lpn_id /*5723418*/
5836: FROM wms_license_plate_numbers wlpn
5837: START WITH wlpn.lpn_id = p_lpn_id
5917: BEGIN
5918: SELECT 'Y' INTO x_loaded_status FROM dual where exists
5919: (SELECT 1 from
5920: wms_dispatched_tasks W,
5921: mtl_material_transactions_temp t,
5922: mtl_txn_request_lines M
5923: WHERE m.lpn_id = p_lpn_id
5924: AND m.organization_id = p_org_id
5925: AND m.line_status <> 5
5939: begin
5940: if (x_lpn_context =1 and x_loaded_status = 'N' ) then
5941: -- SELECT 'Y' INTO x_loaded_status FROM dual where exists
5942: SELECT 'Z' INTO x_loaded_status FROM dual where exists -- End of changes for the bug 5768339.
5943: (SELECT 1 FROM mtl_material_transactions_temp mmtt, wms_dispatched_tasks wdt
5944: WHERE mmtt.transaction_temp_id = wdt.transaction_temp_id
5945: AND mmtt.organization_id = wdt.organization_id
5946: AND wdt.status = 4
5947: AND wdt.organization_id = p_org_id
6113: IF l_entire_lpn_empty THEN
6114: BEGIN
6115: SELECT 'Y' INTO x_loaded_status FROM dual where exists
6116: (select 1 from
6117: WMS_DISPATCHED_TASKS W, MTL_MATERIAL_TRANSACTIONS_TEMP T
6118: WHERE T.lpn_id = l_lpn_id
6119: AND t.organization_id = p_org_id
6120: AND w.organization_id = p_org_id
6121: AND T.transaction_temp_id = W.transaction_temp_id
6451: END IF;
6452:
6453: -- Update batch id and seq id
6454: IF p_batch_id IS NOT NULL AND p_batch_seq IS NOT NULL THEN
6455: UPDATE mtl_material_transactions_temp
6456: SET transaction_header_id = p_trx_header_id
6457: , transaction_batch_id = p_batch_id
6458: , transaction_batch_seq = p_batch_seq
6459: WHERE transaction_temp_id = l_txn_temp_id;
6649: RAISE fnd_api.g_exc_error;
6650: END IF;
6651:
6652: BEGIN
6653: UPDATE mtl_material_transactions_temp
6654: SET transaction_header_id = l_trx_header_id
6655: , transaction_batch_id = l_trx_header_id
6656: , transaction_batch_seq = 2
6657: WHERE transaction_temp_id = l_txn_temp_id;
6954: --BUG 3359835: Must call activate_operation_instance
6955: l_progress := 40;
6956: SELECT mmtt.transaction_temp_id
6957: bulk collect INTO l_mmtt_ids
6958: FROM mtl_material_transactions_temp mmtt
6959: WHERE mmtt.organization_id = p_org_id
6960: AND mmtt.move_order_line_id IN
6961: ( SELECT mtrl.line_id
6962: FROM mtl_txn_request_lines mtrl ,
8169: SELECT
8170: mmtt.transaction_temp_id
8171: BULK COLLECT INTO
8172: l_txn_tmp_id_tb
8173: FROM mtl_material_transactions_temp mmtt
8174: WHERE -- suggestion mmtts?
8175: ( ( mmtt.transaction_source_type_id = 1 AND
8176: mmtt.transaction_action_id = 27) OR
8177: ( mmtt.transaction_source_type_id = 7 AND
8221: SELECT
8222: mmtt.transaction_temp_id
8223: BULK COLLECT INTO
8224: l_txn_tmp_id_tb
8225: FROM mtl_material_transactions_temp mmtt
8226: WHERE -- suggestion mmtts?
8227: mmtt.organization_id = l_org_id AND
8228: mmtt.move_order_line_id --BUG 3435079: use org_id for performace reason
8229: IN ( SELECT mtrl.line_id
8816: --BUG 3435079: The query doesn't seem to be picking up the lpn_id
8817: --index on MMTT. So replace it with the following, and also
8818: --Move it BEFORE updating MOL
8819: UPDATE
8820: mtl_material_transactions_temp
8821: SET
8822: lpn_id = p_into_lpn_id
8823: WHERE
8824: lpn_id = p_from_lpn_id;
8892: RAISE fnd_api.g_exc_error;
8893: END IF;
8894:
8895: BEGIN
8896: UPDATE mtl_material_transactions_temp
8897: SET transaction_header_id = l_hdr_id
8898: , transaction_batch_id = l_hdr_id
8899: , transaction_batch_seq = l_batch_seq
8900: WHERE transaction_temp_id = l_txn_tmp_id;
8963: RAISE fnd_api.g_exc_error;
8964: END IF;
8965:
8966: BEGIN
8967: UPDATE mtl_material_transactions_temp
8968: SET transaction_header_id = l_hdr_id
8969: , transaction_batch_id = l_hdr_id
8970: , transaction_batch_seq = l_batch_seq
8971: WHERE transaction_temp_id = l_txn_tmp_id;
9029: RAISE fnd_api.g_exc_error;
9030: END IF;
9031:
9032: BEGIN
9033: UPDATE mtl_material_transactions_temp
9034: SET transaction_header_id = l_hdr_id
9035: , transaction_batch_id = l_hdr_id
9036: , transaction_batch_seq = l_batch_seq
9037: WHERE transaction_temp_id = l_txn_tmp_id;
9159: --BUG 3435079: The query doesn't seem to be picking up the lpn_id
9160: --index on MMTT. So replace it with the following, and also
9161: --Move it BEFORE updating MOL
9162: UPDATE
9163: mtl_material_transactions_temp
9164: SET
9165: lpn_id = p_into_lpn_id
9166: WHERE
9167: lpn_id = p_from_lpn_id;
9396: , mmtt.wip_supply_type
9397: , mmtt.secondary_transaction_quantity --OPM Convergence
9398: , mtrl.inspection_status
9399: , msi.primary_uom_code
9400: FROM mtl_material_transactions_temp mmtt
9401: , mtl_txn_request_lines mtrl
9402: , mtl_txn_request_headers mtrh
9403: , mtl_system_items_kfv msi
9404: , ( /*5723418*/
10061: --suggested for the current group of tasks
10062: BEGIN
10063: SELECT SUM(mmtt.primary_quantity)
10064: INTO l_prim_qty_consumable
10065: FROM mtl_material_transactions_temp mmtt
10066: , mtl_txn_request_lines mtrl
10067: WHERE mmtt.transaction_header_id = p_txn_header_id
10068: AND mtrl.line_id = l_mol_line_id
10069: AND mtrl.line_id = mmtt.move_order_line_id;
10804: --If there is only one MMTT for the current mol, then p_entire_flag
10805: --should be 'N' else it should be 'Y'
10806: SELECT count(1)
10807: INTO l_mmtt_count
10808: FROM mtl_material_transactions_temp
10809: WHERE move_order_line_id = l_mol_line_id_tbl(i);
10810:
10811: IF l_mmtt_count > 1 THEN
10812: l_entire_lpn := 'N';
10867:
10868: --For INV/WIP LPNs, we would be calling the Inventory TM for the current
10869: --group. So, should update the MMTT with the new txn_header_id
10870: IF (p_lpn_context <> G_LPN_CONTEXT_RCV) THEN
10871: UPDATE mtl_material_transactions_temp
10872: SET transaction_header_id = l_new_txn_header_id
10873: , transaction_batch_id = l_new_txn_header_id
10874: , transaction_batch_seq = l_batch_seq --BUG 3306988
10875: WHERE transaction_temp_id = l_mmtt_temp_id;
11000: END IF;
11001:
11002:
11003: SELECT count(DISTINCT operation_plan_id) INTO l_lms_rec_count
11004: FROM mtl_material_transactions_temp mmtt
11005: WHERE transaction_header_id = p_txn_header_id;
11006:
11007: IF (l_debug = 1) THEN
11008: debug('complete_putaway_wrapper: LMS Code, l_lms_rec_count: ' || l_lms_rec_count , l_proc_name);
11011: IF l_lms_rec_count = 1 THEN
11012: -- since same OperationPlanId stamped for all the MMTTs of the group
11013: -- return its value for LMS data capture
11014: SELECT operation_plan_id INTO x_lms_operation_plan_id
11015: FROM mtl_material_transactions_temp
11016: WHERE transaction_header_id = p_txn_header_id
11017: AND ROWNUM = 1;
11018:
11019: IF (l_debug = 1) THEN
11530: So, modified mmmt.lpn_id to mtrl.lpn_id and introduced join
11531: with mmmt and mtrl table.
11532: */
11533: (EXISTS (SELECT 'LOADED_BY_SAME_USER'
11534: FROM mtl_material_transactions_temp mmtt,
11535: wms_dispatched_tasks wdt,--5650113
11536: mtl_txn_request_lines mtrl--5650113
11537: WHERE mmtt.organization_id = p_organization_id
11538: AND mmtt.transaction_temp_id = wdt.transaction_temp_id
11773: ((wlpn.lpn_context = 1 AND l_lpn_context IN (1,2) ) OR
11774: wlpn.lpn_context IN (1,3) AND l_lpn_context = 3)--BUG 3463634
11775: AND (p_lpn_id = wlpn.lpn_id OR --BUG 3368408
11776: (NOT EXISTS (SELECT 'LOADED'
11777: FROM mtl_material_transactions_temp mmtt,
11778: wms_dispatched_tasks wdt
11779: WHERE mmtt.organization_id = p_organization_id
11780: AND mmtt.transaction_temp_id = wdt.transaction_temp_id
11781: AND wdt.organization_id = p_organization_id
12198: l_crossdock_types,
12199: l_wip_supply_types
12200: FROM
12201: mtl_txn_request_lines mol,
12202: mtl_material_transactions_temp mmtt ,
12203: (SELECT wlpn.lpn_id /*5723418*/
12204: FROM wms_license_plate_numbers wlpn
12205: START WITH wlpn.lpn_id = p_lpn_id
12206: CONNECT BY PRIOR wlpn.lpn_id = wlpn.parent_lpn_id ) wlpn
12291: SELECT mmtt.inventory_item_id
12292: , mmtt.locator_id
12293: , mmtt.transaction_quantity
12294: , mmtt.transaction_uom
12295: FROM mtl_material_transactions_temp mmtt,
12296: mtl_txn_request_lines mtrl ,
12297: (SELECT lpn_id /*5723418*/
12298: FROM wms_license_plate_numbers
12299: START WITH lpn_id = p_lpn_id
12637: mmtt.primary_quantity,
12638: mmtt.inventory_item_id,
12639: mol.crossdock_type,
12640: mol.backorder_delivery_detail_id
12641: FROM mtl_material_transactions_temp mmtt,
12642: mtl_txn_request_lines mol
12643: WHERE mmtt.move_order_line_id(+) = mol.line_id
12644: AND mmtt.wms_task_type=2
12645: AND mol.organization_id = mmtt.organization_id(+)