[Home] [Help]
195: BEGIN
196: --Get Max line number
197: SELECT MAX(line_number)
198: INTO l_line_num
199: FROM mtl_txn_request_lines
200: WHERE header_id = p_header_id;
201:
202: l_header_id := p_header_id;
203: l_org_id := p_org_id;
349: , to_cost_group_id
350: INTO l1
351: , l2
352: , l3
353: FROM mtl_txn_request_lines
354: WHERE line_id = l_cg_line;
355:
356: IF (l_debug = 1) THEN
357: mydebug('create_mo_line: Act Sub: ' || l1);
581: INTO l_project_comingle
582: FROM DUAL
583: WHERE EXISTS(
584: SELECT 1
585: FROM mtl_txn_request_lines
586: WHERE lpn_id = p_lpn
587: AND organization_id = p_org_id
588: AND line_status <> inv_globals.g_to_status_closed
589: AND NVL(project_id, -1) <> NVL(p_project_id, -1)
867: , to_cost_group_id
868: INTO l1
869: , l2
870: , l3
871: FROM mtl_txn_request_lines
872: WHERE line_id = l_cg_line;
873:
874: IF (l_debug = 1) THEN
875: mydebug('create_mo: Act Sub: ' || l1);
1106: , mtrl.txn_source_id
1107: , mtrl.txn_source_line_id
1108: , mtrl.txn_source_line_detail_id
1109: , mtrl.transaction_type_id
1110: FROM mtl_txn_request_lines mtrl, mtl_txn_request_headers mtrh
1111: WHERE mtrl.lpn_id = p_lpn_id
1112: AND mtrl.organization_id = p_org_id
1113: AND mtrl.header_id = mtrh.header_id
1114: AND mtrl.line_status <> inv_globals.g_to_status_closed
1131: , mtrl.txn_source_line_id
1132: , mtrl.txn_source_line_detail_id
1133: , mtrl.transaction_type_id
1134:
1135: FROM mtl_txn_request_lines mtrl, mtl_txn_request_headers mtrh
1136: WHERE mtrl.lpn_id = p_lpn_id
1137: AND mtrl.organization_id = p_org_id
1138: AND mtrl.header_id = mtrh.header_id
1139: AND mtrl.line_status <> inv_globals.g_to_status_closed
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
1353: INTO l_rcount
1354: FROM DUAL
1355: WHERE EXISTS(
1356: SELECT 1
1357: FROM mtl_txn_request_lines l, mtl_txn_request_headers h
1358: WHERE l.lpn_id = l_lpn_id
1359: AND l.line_id = NVL(p_move_order_line_id, l.line_id) -- added for ATF_J
1360: AND NVL(l.quantity_delivered, 0) < l.quantity -- added for ATF_J
1361: AND l.organization_id = p_org_id
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
1485: AND mol.line_id = NVL(p_move_order_line_id, mol.line_id) -- added for ATF_J
1486: AND mol.organization_id = p_org_id
1487: AND mol.quantity_detailed > 0
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
1507: WHERE mol.lpn_id = l_lpn_id
1508: AND mol.line_id = NVL(p_move_order_line_id, mol.line_id) -- added for ATF_J
1509: AND mol.organization_id = p_org_id
1510: AND mol.quantity_detailed > 0
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
1522: AND mol.line_id = NVL(p_move_order_line_id, mol.line_id) -- added for ATF_J
1523: AND mol.organization_id = p_org_id
1524: AND mol.quantity_detailed > 0
1526: FROM mtl_txn_request_headers moh
1527: WHERE mol.header_id = moh.header_id
1528: AND moh.move_order_type = inv_globals.g_move_order_put_away));
1529:
1530: UPDATE mtl_txn_request_lines mol
1531: SET mol.line_status = inv_globals.g_to_status_closed
1532: WHERE mol.lpn_id = l_lpn_id
1533: AND mol.line_id = NVL(p_move_order_line_id, mol.line_id) -- added for ATF_J
1534: AND mol.organization_id = p_org_id
1670: IF (l_debug = 1) THEN
1671: mydebug('Manual Drop: Updating Move orders to : Sub: ' || p_subinventory || ' loc : '||p_locator_id );
1672: END IF;
1673:
1674: UPDATE mtl_txn_request_lines mol
1675: SET to_subinventory_code = p_subinventory
1676: , to_locator_id = p_locator_id
1677: , quantity_detailed = NULL
1678: WHERE organization_id = p_org_id
1968: FROM
1969: wms_lpn_contents
1970: GROUP BY parent_lpn_id, inventory_item_id, revision, lot_number,uom_code,organization_id--BUG 4607833
1971: ) wlc, -- sub-query is necessary because there could be more than one wlc record for the same inventory_item_id, revision, lot_number
1972: mtl_txn_request_lines mol
1973: WHERE wlc.parent_lpn_id = mol.lpn_id
1974: AND wlc.inventory_item_id = mol.inventory_item_id
1975: -- AND wlc.uom_code = mol.uom_code -- Bug fix 3200526
1976: AND wlc.organization_id = mol.organization_id --Bug 4607833
2163:
2164:
2165: IF (l_ref_detail_id IS NOT NULL) THEN
2166: BEGIN
2167: UPDATE mtl_txn_request_lines
2168: SET reference_detail_id = NULL
2169: WHERE line_id = l_line_id;
2170: EXCEPTION
2171: WHEN OTHERS THEN
2257: mydebug('suggestions_pub: After calling inv_ppengine_pvt.create_suggestions');
2258: mydebug('suggestions_pub: l_return_status = ' || l_return_status);
2259: END IF;
2260:
2261: -- Update Qty detailed in Mtl_txn_request_lines
2262: -- Bug fix 2271470
2263: -- Need to consider quantity_delivered
2264:
2265: UPDATE mtl_txn_request_lines mol
2261: -- Update Qty detailed in Mtl_txn_request_lines
2262: -- Bug fix 2271470
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)
2381:
2382: /* nsinghi - added the If statement to update transaction_source_id in MMTT for Process Orgs. */
2383: IF (l_txn_source_type_id = 5 AND l_process_flag = 'Y') THEN
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'
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;
3914:
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:
5338: END IF;
5339:
5340: --Update the wms_process_flag to 2 so that one can work with
5341: --with this move order line from elsewhere
5342: UPDATE mtl_txn_request_lines
5343: SET wms_process_flag = 2
5344: WHERE line_id = l_mo_line_id;
5345:
5346: --Storage subinventory - Call Deliver API
5798:
5799: BEGIN
5800: l_xdock_tasks_exists := 'N';
5801: SELECT 'Y' INTO l_xdock_tasks_exists
5802: FROM MTL_TXN_REQUEST_LINES mtrl
5803: WHERE lpn_id= p_lpn_id
5804: AND line_id <> l_mo_line_id
5805: AND backorder_delivery_detail_id IS NOT NULL
5806: AND line_status <> 5
6011: END;*/
6012:
6013: --mydebug('After updating mmtt');
6014: -- Update MTL_TXN_REQUESTS_TABLE
6015: -- Since the uom code in mtl_txn_request_lines will always
6016: -- be the same as the transaction_uom in mmtt, no need to do the
6017: -- conversion again
6018: UPDATE mtl_txn_request_lines
6019: SET quantity_detailed = quantity_detailed - l_qty_diff
6014: -- Update MTL_TXN_REQUESTS_TABLE
6015: -- Since the uom code in mtl_txn_request_lines will always
6016: -- be the same as the transaction_uom in mmtt, no need to do the
6017: -- conversion again
6018: UPDATE mtl_txn_request_lines
6019: SET quantity_detailed = quantity_detailed - l_qty_diff
6020: WHERE line_id = l_line_id
6021: AND organization_id = l_org_id;
6022: --mydebug('After updating mol');
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
6092: AND mtrl.organization_id = p_org_id;
6238: SELECT 1
6239: INTO l_mo_cnt
6240: FROM DUAL
6241: WHERE EXISTS(SELECT 1
6242: FROM mtl_txn_request_lines
6243: WHERE lpn_id = l_lpn_id
6244: AND organization_id = l_org_id
6245: );
6246: EXCEPTION
6265: SELECT 1
6266: INTO l_process_flag_cnt
6267: FROM DUAL
6268: WHERE EXISTS(SELECT 1
6269: FROM mtl_txn_request_lines
6270: WHERE lpn_id = l_lpn_id
6271: AND organization_id = l_org_id
6272: AND NVL(wms_process_flag, 1) = 2
6273: AND line_status <> 5); -- 3773255
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
6502: AND wdt.transaction_temp_id = mmtt.transaction_temp_id
6506: -- AND wdt.person_id = p_user_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
7053: AND lot_number = l_lot
7054: AND current_status = 4
7055: AND current_organization_id = l_org_id
7056: AND EXISTS (SELECT 1
7057: FROM mtl_txn_request_lines mol
7058: WHERE mol.line_id = l_mmtt_line_id
7059: AND mol.REFERENCE = 'ORDER_LINE_ID');
7060: END LOOP;
7061: CLOSE c_serial_number;
7082: AND inventory_item_id = l_item_id
7083: AND current_status = 4
7084: AND current_organization_id = l_org_id
7085: AND EXISTS (SELECT 1
7086: FROM mtl_txn_request_lines mol
7087: WHERE mol.line_id = l_mmtt_line_id
7088: AND mol.REFERENCE = 'ORDER_LINE_ID');
7089: END LOOP;
7090: CLOSE c_serial_number;
7332: ELSE -- p_from_lpn = p_to_lpn
7333: BEGIN
7334: SELECT COUNT(1)
7335: INTO l_count
7336: FROM mtl_txn_request_lines mol
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)
7677: -- Cursor added to retrieve valid MO header IDs so we can avoid
7678: -- the hash join problem.
7679: CURSOR c_header_id IS
7680: SELECT moh.header_id
7681: FROM mtl_txn_request_headers moh, mtl_txn_request_lines mol
7682: WHERE moh.move_order_type = inv_globals.g_move_order_put_away
7683: AND moh.header_id = mol.header_id
7684: AND mol.lpn_id = p_lpn_id;
7685: l_header_id NUMBER;
7801:
7802: EXIT WHEN lpn_contents_csr%NOTFOUND;
7803:
7804: -- Close existing move orders detailed.
7805: UPDATE mtl_txn_request_lines mol
7806: SET mol.line_status = inv_globals.g_to_status_closed
7807: WHERE mol.lpn_id = l_lpn_id
7808: AND mol.organization_id = p_org_id
7809: AND mol.quantity_detailed > 0
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:
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
8079: AND lpn_id = l_lpn_id)
8080: );
8081:
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
8090: AND lpn_id = l_lpn_id));
8091:
8092: DELETE FROM mtl_material_transactions_temp
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
8097: AND lpn_id = l_lpn_id);
8098:
8099: IF (l_debug = 1) THEN
8105: -- Update the move order lines with the user inputted sub/loc.
8106: -- Also null out the quantity detailed in case the user enters
8107: -- a loc, validates it against rules, but then goes back and changes
8108: -- the sub/loc before processing it.
8109: UPDATE mtl_txn_request_lines
8110: SET to_subinventory_code = p_subinventory
8111: , to_locator_id = p_locator_id
8112: , quantity_detailed = NULL
8113: , SECONDARY_QUANTITY_DETAILED = NULL --added for bug 16527809
8166:
8167: -- Get the number of move order lines for this given LPN
8168: SELECT COUNT(1)
8169: INTO l_mo_lines_count
8170: FROM mtl_txn_request_lines
8171: WHERE organization_id = p_organization_id
8172: AND lpn_id = l_lpn_id
8173: AND line_status = 7;
8174:
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
8448: , mol.transaction_source_type_id
8449: , mol.to_cost_group_id
8450: , mol.SECONDARY_QUANTITY --BUG12796808
8451: , mol.SECONDARY_UOM_CODE --BUG12796808
8452: FROM mtl_txn_request_lines mol
8453: WHERE mol.organization_id = p_organization_id
8454: AND mol.header_id IN (SELECT moh.header_id
8455: FROM mtl_txn_request_headers moh
8456: WHERE moh.move_order_type = inv_globals.g_move_order_put_away
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
8574: FROM wms_license_plate_numbers
8835:
8836: --Need to update MOL.quantity_detailed
8837: --Because in WMSOPIBB.COMPLETE, it will deduct
8838: --MMTT.TRANSACTION_QUANTITY from MOL.QUANITY_DETIALED
8839: UPDATE mtl_txn_request_lines
8840: SET quantity_detailed = Nvl(quantity_detailed,0)+quantity
8841: WHERE line_id = v_mo_line.line_id;
8842:
8843: -- Update the MMTT record with the move order line,
8925: DELETE FROM mtl_transaction_lots_temp
8926: WHERE transaction_temp_id = l_mmtt_id_tab(i);
8927:
8928: FOR i IN 1..l_mmtt_id_tab.COUNT LOOP
8929: UPDATE mtl_txn_request_lines
8930: SET quantity_detailed = Nvl(quantity_detailed,0)
8931: -Decode(uom_code
8932: ,l_mmtt_uom_tab(i)
8933: ,l_mmtt_qty_tab(i)
9030: CURSOR l_item_txn_cursor IS
9031: SELECT inventory_item_id
9032: , lot_number
9033: , transaction_type_id
9034: FROM mtl_txn_request_lines
9035: WHERE organization_id = p_organization_id
9036: AND lpn_id = p_lpn_id;
9037:
9038: CURSOR l_serial_cursor IS
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
9286: AND NVL(mmtt.wms_task_type, 0) <> -1;
9458: INTO x_crossdock
9459: FROM dual
9460: WHERE exists
9461: (SELECT 1
9462: FROM mtl_txn_request_lines
9463: WHERE lpn_id = p_lpn_id
9464: AND organization_id = p_organization_id
9465: AND backorder_delivery_detail_id IS NOT NULL);
9466: EXCEPTION