[Home] [Help]
1134:
1135: IF l_return_status <> fnd_api.g_ret_sts_success THEN
1136: fnd_message.set_name ('CSP', 'CSP_EXEC_FAILED_IN_TBL');
1137: fnd_message.set_token ('ROUTINE', l_api_name, TRUE);
1138: fnd_message.set_token ('TABLE', 'Mtl_Serial_Numbers_Temp', TRUE);
1139: fnd_msg_pub.add;
1140: RAISE EXCP_USER_DEFINED;
1141: END IF;
1142: END IF;
1174:
1175: IF l_return_status <> fnd_api.g_ret_sts_success THEN
1176: fnd_message.set_name ('CSP', 'CSP_EXEC_FAILED_IN_TBL');
1177: fnd_message.set_token ('ROUTINE', l_api_name, TRUE);
1178: fnd_message.set_token ('TABLE', 'Mtl_Serial_Numbers_Temp', TRUE);
1179: fnd_msg_pub.add;
1180: RAISE EXCP_USER_DEFINED;
1181: END IF;
1182: ELSE NULL; -- already taken care.
2442: Loop
2443: Fetch l_Get_Serial_Lot_id_Csr Into l_Serial_Temp_Id_Del;
2444: Exit When l_Get_Serial_Lot_id_Csr%NotFound;
2445:
2446: delete from mtl_serial_numbers_temp
2447: where transaction_temp_id = l_Serial_Temp_Id_Del;
2448: End Loop;
2449: If l_Get_Serial_Lot_id_Csr%ROWCOUNT = 0 Then
2450: FND_MESSAGE.SET_NAME('CSP', 'CSP_RECEIPT_SERIAL_LOT_FAILURE');
2464: -- case 3: if serial control, delete the record in the mmtt table and the msnt table
2465: Elsif nvl(l_csp_mtltxn_rec.item_lot_control_code, 1) = 1 And
2466: nvl(l_csp_mtltxn_rec.item_serial_control_code, 1) in (2,5) Then
2467:
2468: delete from mtl_serial_numbers_temp
2469: where transaction_temp_id = l_temp_id_to_be_processed;
2470:
2471: delete from mtl_material_transactions_temp
2472: where transaction_temp_id = l_temp_id_to_be_processed;
2972: p_new_temp_id IN NUMBER
2973: ) --return boolean
2974: is
2975: CURSOR l_Get_Msnt(l_transaction_temp_id NUMBER) IS
2976: SELECT * FROM mtl_serial_numbers_temp
2977: WHERE transaction_temp_id = l_transaction_temp_id; --p_old_transaction_temp_id;
2978: l_new_temp_id NUMBER := p_new_temp_id;
2979: begin
2980:
3020: -- In any cases, we can recycle the l_msnt_tbl table.
3021:
3022: -- First msnt record: just need to update the existing record.
3023: l_new_to_num := to_number(l_num) - 1; --- to_number(l_temp_fm_num);
3024: update mtl_serial_numbers_temp
3025: set to_serial_number = l_prefix||lpad(to_char(l_new_to_num),l_number_length, '0'),
3026: last_update_date = sysdate,
3027: serial_prefix = l_new_to_num - to_number(l_temp_fm_num) + 1
3028: where transaction_temp_id = l_msnt_tbl(l_index).transaction_temp_id
3031:
3032: If (SQL%NOTFOUND) then
3033: fnd_message.set_name ('CSP', 'CSP_EXEC_FAILED_IN_TBL');
3034: fnd_message.set_token ('ROUTINE', l_api_name, FALSE);
3035: fnd_message.set_token ('TABLE', 'Mtl_Serial_Numbers_Temp', FALSE);
3036: fnd_msg_pub.add;
3037: CLOSE l_Get_Msnt;
3038: RAISE EXCP_USER_DEFINED;
3039: End If;
3055:
3056: IF l_return_status <> fnd_api.g_ret_sts_success THEN
3057: fnd_message.set_name ('CSP', 'CSP_EXEC_FAILED_IN_TBL');
3058: fnd_message.set_token ('ROUTINE', l_api_name, FALSE);
3059: fnd_message.set_token ('TABLE', 'Mtl_Serial_Numbers_Temp', FALSE);
3060: fnd_msg_pub.add;
3061: CLOSE l_Get_Msnt;
3062: RAISE EXCP_USER_DEFINED;
3063: END IF;
3078:
3079: IF l_return_status <> fnd_api.g_ret_sts_success THEN
3080: fnd_message.set_name ('CSP', 'CSP_EXEC_FAILED_IN_TBL');
3081: fnd_message.set_token ('ROUTINE', l_api_name, FALSE);
3082: fnd_message.set_token ('TABLE', 'Mtl_Serial_Numbers_Temp', FALSE);
3083: fnd_msg_pub.add;
3084: CLOSE l_Get_Msnt;
3085: RAISE EXCP_USER_DEFINED;
3086: END IF;
3098: -- The second record: create a new msnt record having the l_new_transation_temp_id and the p_serial_number.
3099:
3100: -- First record: update the existing record such that the fm_serial_number = p_serial_number + 1.
3101: l_new_fm_num := to_number(l_num) + 1;
3102: update mtl_serial_numbers_temp
3103: set fm_serial_number = l_prefix||lpad(to_char(l_new_fm_num),l_number_length, '0'),
3104: last_update_date = sysdate,
3105: serial_prefix = to_number(l_temp_to_num) - l_new_fm_num +1
3106: where transaction_temp_id = l_msnt_tbl(l_index).transaction_temp_id
3109:
3110: If (SQL%NOTFOUND) then
3111: fnd_message.set_name ('CSP', 'CSP_EXEC_FAILED_IN_TBL');
3112: fnd_message.set_token ('ROUTINE', l_api_name, FALSE);
3113: fnd_message.set_token ('TABLE', 'Mtl_Serial_Numbers_Temp', FALSE);
3114: fnd_msg_pub.add;
3115: CLOSE l_Get_Msnt;
3116: RAISE EXCP_USER_DEFINED;
3117: End If;
3132:
3133: IF l_return_status <> fnd_api.g_ret_sts_success THEN
3134: fnd_message.set_name ('CSP', 'CSP_EXEC_FAILED_IN_TBL');
3135: fnd_message.set_token ('ROUTINE', l_api_name, FALSE);
3136: fnd_message.set_token ('TABLE', 'Mtl_Serial_Numbers_Temp', FALSE);
3137: fnd_msg_pub.add;
3138: CLOSE l_Get_Msnt;
3139: RAISE EXCP_USER_DEFINED;
3140: END IF;
3152: -- The second record: create a new msnt record having the l_new_transation_temp_id and the p_serial_number.
3153:
3154: -- First record
3155: l_new_to_num := to_number(l_num) - 1;
3156: update mtl_serial_numbers_temp
3157: set to_serial_number = l_prefix||lpad(to_char(l_new_to_num),l_number_length, '0'),
3158: last_update_date = sysdate,
3159: serial_prefix = l_new_to_num - to_number(l_temp_fm_num) + 1
3160: where transaction_temp_id = l_msnt_tbl(l_index).transaction_temp_id
3163:
3164: If (SQL%NOTFOUND) then
3165: fnd_message.set_name ('CSP', 'CSP_EXEC_FAILED_IN_TBL');
3166: fnd_message.set_token ('ROUTINE', l_api_name, FALSE);
3167: fnd_message.set_token ('TABLE', 'Mtl_Serial_Numbers_Temp', FALSE);
3168: fnd_msg_pub.add;
3169: CLOSE l_Get_Msnt;
3170: RAISE EXCP_USER_DEFINED;
3171: End If;
3186:
3187: IF l_return_status <> fnd_api.g_ret_sts_success THEN
3188: fnd_message.set_name ('CSP', 'CSP_EXEC_FAILED_IN_TBL');
3189: fnd_message.set_token ('ROUTINE', l_api_name, FALSE);
3190: fnd_message.set_token ('TABLE', 'Mtl_Serial_Numbers_Temp', FALSE);
3191: fnd_msg_pub.add;
3192: CLOSE l_Get_Msnt;
3193: RAISE EXCP_USER_DEFINED;
3194: END IF;
3205: -- In this case, we just need to update the transaction_temp_id to the l_new_temp_id.
3206: l_done_flag := fnd_api.g_true;
3207: l_serial_case := 1;
3208:
3209: update mtl_serial_numbers_temp
3210: set transaction_temp_id = l_new_temp_id
3211: where transaction_temp_id = l_msnt_tbl(l_index).transaction_temp_id
3212: and fm_serial_number = l_msnt_tbl(l_index).fm_serial_number
3213: and to_serial_number = l_msnt_tbl(l_index).to_serial_number;
3214:
3215: If (SQL%NOTFOUND) then
3216: fnd_message.set_name ('CSP', 'CSP_EXEC_FAILED_IN_TBL');
3217: fnd_message.set_token ('ROUTINE', l_api_name, FALSE);
3218: fnd_message.set_token ('TABLE', 'Mtl_Serial_Numbers_Temp', FALSE);
3219: fnd_msg_pub.add;
3220: CLOSE l_Get_Msnt;
3221: RAISE EXCP_USER_DEFINED;
3222: End If;
3240: -- This is a case which fm_serial_number = to_serial_number.
3241: -- In this case, we just need to update the transaction_temp_id to the l_new_temp_id.
3242: l_done_flag := fnd_api.g_true;
3243: l_serial_case := 1;
3244: update mtl_serial_numbers_temp
3245: set transaction_temp_id = l_new_temp_id
3246: where transaction_temp_id = l_msnt_tbl(l_index).transaction_temp_id
3247: and fm_serial_number = l_msnt_tbl(l_index).fm_serial_number
3248: and to_serial_number = l_msnt_tbl(l_index).to_serial_number;
3249:
3250: If (SQL%NOTFOUND) then
3251: fnd_message.set_name ('CSP', 'CSP_EXEC_FAILED_IN_TBL');
3252: fnd_message.set_token ('ROUTINE', l_api_name, FALSE);
3253: fnd_message.set_token ('TABLE', 'Mtl_Serial_Numbers_Temp', FALSE);
3254: fnd_msg_pub.add;
3255: CLOSE l_Get_Msnt;
3256: RAISE EXCP_USER_DEFINED;
3257: End If;