[Home] [Help]
1072:
1073: IF l_return_status <> fnd_api.g_ret_sts_success THEN
1074: fnd_message.set_name ('CSP', 'CSP_EXEC_FAILED_IN_TBL');
1075: fnd_message.set_token ('ROUTINE', l_api_name, FALSE);
1076: fnd_message.set_token ('TABLE', 'MTL_TRANSACTION_LOTS_TEMP', FALSE);
1077: fnd_msg_pub.add;
1078: RAISE EXCP_USER_DEFINED;
1079: END IF;
1080:
1101: FETCH l_Get_Serial_Temp_id_Csr INTO l_mtlt_tbl(l_index).serial_transaction_temp_id;
1102: CLOSE l_Get_Serial_Temp_id_Csr;
1103:
1104: -- update the mtlt record
1105: update mtl_transaction_lots_temp
1106: set serial_transaction_temp_id = l_mtlt_tbl(l_index).serial_transaction_temp_id
1107: where transaction_temp_id = l_mtlt_tbl(l_index).transaction_temp_id
1108: and lot_number = l_mtlt_tbl(l_index).lot_number;
1109:
1109:
1110: If (SQL%NOTFOUND) then
1111: fnd_message.set_name ('CSP', 'CSP_EXEC_FAILED_IN_TBL');
1112: fnd_message.set_token ('ROUTINE', l_api_name, TRUE);
1113: fnd_message.set_token ('TABLE', 'Mtl_Transaction_Lots_Temp', TRUE);
1114: fnd_msg_pub.add;
1115: RAISE EXCP_USER_DEFINED;
1116: End If;
1117:
1497: DECLARE
1498: l_mtlt_tbl csp_pp_util.g_mtlt_tbl_type;
1499: l_index NUMBER := 1;
1500: CURSOR l_Get_Mtlt IS
1501: SELECT * FROM mtl_transaction_lots_temp
1502: WHERE transaction_temp_id = l_csp_mtltxn_rec.transaction_temp_id -- Get the lot record form the original temp id
1503: ORDER BY TRANSACTION_QUANTITY DESC;
1504: -- Verify whether there is lot record in the mtlt table.
1505: BEGIN
1526:
1527: IF l_return_status <> fnd_api.g_ret_sts_success THEN
1528: fnd_message.set_name ('CSP', 'CSP_EXEC_FAILED_IN_TBL');
1529: fnd_message.set_token ('ROUTINE', l_api_name, FALSE);
1530: fnd_message.set_token ('TABLE', 'Mtl_Transaction_Lots_Temp', FALSE);
1531: fnd_msg_pub.add;
1532: RAISE EXCP_USER_DEFINED;
1533: END IF;
1534:
2423: -- case 1: if lot control , delete the record in mmtt table and mtlt table.
2424: If nvl(l_csp_mtltxn_rec.item_lot_control_code, 1) <> 1 And
2425: nvl(l_csp_mtltxn_rec.item_serial_control_code, 1) in (1, 6) Then
2426:
2427: delete from mtl_transaction_lots_temp
2428: where transaction_temp_id = l_temp_id_to_be_processed;
2429:
2430: delete from mtl_material_transactions_temp
2431: where transaction_temp_id = l_temp_id_to_be_processed;
2433: Elsif nvl(l_csp_mtltxn_rec.item_lot_control_code, 1) <> 1 And
2434: nvl(l_csp_mtltxn_rec.item_serial_control_code, 1) in (2, 5) Then
2435: Declare
2436: Cursor l_Get_Serial_Lot_id_Csr Is
2437: select serial_transaction_temp_id from mtl_transaction_lots_temp
2438: where transaction_temp_id = l_temp_id_to_be_processed;
2439: l_serial_temp_id_del NUMBER;
2440: Begin
2441: Open l_Get_Serial_Lot_id_Csr;
2455:
2456: Close l_Get_Serial_Lot_id_Csr;
2457: End;
2458:
2459: delete from mtl_transaction_lots_temp
2460: where transaction_temp_id = l_temp_id_to_be_processed;
2461:
2462: delete from mtl_material_transactions_temp
2463: where transaction_temp_id = l_temp_id_to_be_processed;
2798: FROM mtl_material_transactions_temp
2799: WHERE transaction_temp_id = p_transaction_temp_id;
2800:
2801: CURSOR l_Get_Mtlt IS
2802: SELECT * FROM mtl_transaction_lots_temp
2803: WHERE transaction_temp_id = p_transaction_temp_id
2804: ORDER BY TRANSACTION_QUANTITY DESC;
2805: BEGIN
2806: IF p_receiving_option NOT IN (1, 2, 3) THEN
2957: l_new_serial_temp_id NUMBER;
2958: EXCP_USER_DEFINED EXCEPTION;
2959:
2960: CURSOR l_Get_Mtlt IS
2961: SELECT * FROM mtl_transaction_lots_temp
2962: WHERE transaction_temp_id = p_old_transaction_temp_id
2963: AND lot_number = p_lot_number
2964: ORDER BY TRANSACTION_QUANTITY DESC;
2965: CURSOR l_Get_Serial_Temp_id_Csr IS SELECT MTL_MATERIAL_TRANSACTIONS_S.nextval FROM dual;
3293: ELSIF p_lot_number IS NOT NULL
3294: AND p_serial_number IS NOT NULL THEN
3295: DECLARE
3296: Cursor l_Get_Mtlt_Lot_No IS
3297: SELECT * FROM mtl_transaction_lots_temp
3298: WHERE transaction_temp_id = p_old_transaction_temp_id
3299: AND lot_number = p_lot_number;
3300: BEGIN
3301:
3319:
3320: IF l_quantity_remained > 0 THEN
3321: -- update the existing transaction_quantity to l_quantity_remained.
3322: -- create a new record.
3323: update mtl_transaction_lots_temp
3324: set transaction_quantity = l_quantity_remained,
3325: primary_quantity = l_quantity_remained
3326: where transaction_temp_id = l_mtlt_tbl(l_index).transaction_temp_id
3327: and serial_transaction_temp_id = l_mtlt_tbl(l_index).serial_transaction_temp_id
3329:
3330: If (SQL%NOTFOUND) then
3331: fnd_message.set_name ('CSP', 'CSP_EXEC_FAILED_IN_TBL');
3332: fnd_message.set_token ('ROUTINE', l_api_name, FALSE);
3333: fnd_message.set_token ('TABLE', 'Mtl_Transaction_Lots_Temp', FALSE);
3334: fnd_msg_pub.add;
3335: CLOSE l_Get_Mtlt;
3336: RAISE EXCP_USER_DEFINED;
3337: End If;
3356:
3357: IF l_return_status <> fnd_api.g_ret_sts_success THEN
3358: fnd_message.set_name ('CSP', 'CSP_EXEC_FAILED_IN_TBL');
3359: fnd_message.set_token ('ROUTINE', l_api_name, FALSE);
3360: fnd_message.set_token ('TABLE', 'Mtl_Transaction_Lots_Temp', FALSE);
3361: fnd_msg_pub.add;
3362: CLOSE l_Get_Mtlt;
3363: RAISE EXCP_USER_DEFINED;
3364: ELSE
3402: l_quantity_remained := l_mtlt_tbl(l_index).transaction_quantity - l_qty_received;
3403: IF l_quantity_remained > 0 THEN
3404: -- update the existing transaction_quantity to l_quantity_remained.
3405: -- create a new record.
3406: update mtl_transaction_lots_temp
3407: set transaction_quantity = l_quantity_remained,
3408: primary_quantity = l_quantity_remained
3409: where transaction_temp_id = l_mtlt_tbl(l_index).transaction_temp_id
3410: and lot_number = l_mtlt_tbl(l_index).lot_number;
3411:
3412: If (SQL%NOTFOUND) then
3413: fnd_message.set_name ('CSP', 'CSP_EXEC_FAILED_IN_TBL');
3414: fnd_message.set_token ('ROUTINE', l_api_name, FALSE);
3415: fnd_message.set_token ('TABLE', 'Mtl_Transaction_Lots_Temp', FALSE);
3416: fnd_msg_pub.add;
3417: CLOSE l_Get_Mtlt;
3418: RAISE EXCP_USER_DEFINED;
3419: End If;
3431:
3432: IF l_return_status <> fnd_api.g_ret_sts_success THEN
3433: fnd_message.set_name ('CSP', 'CSP_EXEC_FAILED_IN_TBL');
3434: fnd_message.set_token ('ROUTINE', l_api_name, FALSE);
3435: fnd_message.set_token ('TABLE', 'Mtl_Transaction_Lots_Temp', FALSE);
3436: fnd_msg_pub.add;
3437: CLOSE l_Get_Mtlt;
3438: RAISE EXCP_USER_DEFINED;
3439: END IF;
3444: -- l_quantity_remained := abs(l_quantity_remained);
3445: l_qty_received := abs(l_quantity_remained);
3446: ELSE -- l_quantity_remained = 0 because the quantity_received = transaction_quantity
3447: -- update the existing record to the p_new_transaction_temp_id
3448: update mtl_transaction_lots_temp
3449: set transaction_temp_id = p_new_transaction_temp_id
3450: where transaction_temp_id = l_mtlt_tbl(l_index).transaction_temp_id
3451: and lot_number = l_mtlt_tbl(l_index).lot_number;
3452: If (SQL%NOTFOUND) then
3451: and lot_number = l_mtlt_tbl(l_index).lot_number;
3452: If (SQL%NOTFOUND) then
3453: fnd_message.set_name ('CSP', 'CSP_EXEC_FAILED_IN_TBL');
3454: fnd_message.set_token ('ROUTINE', l_api_name, FALSE);
3455: fnd_message.set_token ('TABLE', 'Mtl_Transaction_Lots_Temp', FALSE);
3456: fnd_msg_pub.add;
3457: CLOSE l_Get_Mtlt;
3458: RAISE EXCP_USER_DEFINED;
3459: End If;
3462:
3463: IF l_Get_Mtlt%rowcount = 0 THEN
3464: fnd_message.set_name ('CSP', 'CSP_NO_LOT_TXN_RECORD');
3465: fnd_msg_pub.add;
3466: --l_msg_data := 'Could not find any records in the mtl_transaction_lots_temp table.';
3467: CLOSE l_Get_Mtlt;
3468: RAISE EXCP_USER_DEFINED;
3469: END IF;
3470: