[Home] [Help]
27: -- klou 01-Jan-2000 created.
28: --
29: -- NOTES: If validations have been done in the precedent procedure from which this one is being called, doing a
30: -- full validation here is unnecessary. To avoid repeating the same validations, you can set the
31: -- p_validation_level to fnd_api.g_valid_level_none when making the procedure call. However, it is your
32: -- responsibility to make sure all proper validations have been done before calling this procedure.
33: -- You are recommended to let this procedure handle the validations if you are not sure.
34: --
35: -- CAUTIONS: This procedure *ALWAYS* calls other procedures with validation_level set to FND_API.G_VALID_LEVEL_NONE.
31: -- p_validation_level to fnd_api.g_valid_level_none when making the procedure call. However, it is your
32: -- responsibility to make sure all proper validations have been done before calling this procedure.
33: -- You are recommended to let this procedure handle the validations if you are not sure.
34: --
35: -- CAUTIONS: This procedure *ALWAYS* calls other procedures with validation_level set to FND_API.G_VALID_LEVEL_NONE.
36: -- If you do not do your own validations before calling this procedure, you should set the p_validation_level
37: -- to FND_API.G_VALID_LEVEL_FULL when making the call.
38: -- End of Comments
39:
33: -- You are recommended to let this procedure handle the validations if you are not sure.
34: --
35: -- CAUTIONS: This procedure *ALWAYS* calls other procedures with validation_level set to FND_API.G_VALID_LEVEL_NONE.
36: -- If you do not do your own validations before calling this procedure, you should set the p_validation_level
37: -- to FND_API.G_VALID_LEVEL_FULL when making the call.
38: -- End of Comments
39:
40: P_Api_Version_Number IN NUMBER,
41: P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
37: -- to FND_API.G_VALID_LEVEL_FULL when making the call.
38: -- End of Comments
39:
40: P_Api_Version_Number IN NUMBER,
41: P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
42: P_Commit IN VARCHAR2 := FND_API.G_FALSE,
43: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
44: p_organization_id IN NUMBER,
45: p_move_order_line_id IN NUMBER,
38: -- End of Comments
39:
40: P_Api_Version_Number IN NUMBER,
41: P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
42: P_Commit IN VARCHAR2 := FND_API.G_FALSE,
43: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
44: p_organization_id IN NUMBER,
45: p_move_order_line_id IN NUMBER,
46: X_Return_Status OUT NOCOPY VARCHAR2,
39:
40: P_Api_Version_Number IN NUMBER,
41: P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
42: P_Commit IN VARCHAR2 := FND_API.G_FALSE,
43: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
44: p_organization_id IN NUMBER,
45: p_move_order_line_id IN NUMBER,
46: X_Return_Status OUT NOCOPY VARCHAR2,
47: X_Msg_Count OUT NOCOPY NUMBER,
54: l_msg_data VARCHAR2(300);
55: l_check_existence NUMBER := 0;
56: l_return_status VARCHAR2(1);
57: l_msg_count NUMBER := 0;
58: l_commit VARCHAR2(1) := FND_API.G_FALSE;
59: l_creation_date DATE;
60: l_last_update_date DATE;
61: l_validation_level NUMBER := FND_API.G_VALID_LEVEL_NONE;
62: l_header_id NUMBER;
57: l_msg_count NUMBER := 0;
58: l_commit VARCHAR2(1) := FND_API.G_FALSE;
59: l_creation_date DATE;
60: l_last_update_date DATE;
61: l_validation_level NUMBER := FND_API.G_VALID_LEVEL_NONE;
62: l_header_id NUMBER;
63: EXCP_USER_DEFINED EXCEPTION;
64:
65: BEGIN
63: EXCP_USER_DEFINED EXCEPTION;
64:
65: BEGIN
66: SAVEPOINT Update_Order_Line_Sts_PUB;
67: IF fnd_api.to_boolean(P_Init_Msg_List) THEN
68: -- initialize message list
69: FND_MSG_PUB.initialize;
70: END IF;
71: -- Standard call to check for call compatibility.
68: -- initialize message list
69: FND_MSG_PUB.initialize;
70: END IF;
71: -- Standard call to check for call compatibility.
72: IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
73: p_api_version_number,
74: l_api_name,
75: G_PKG_NAME)
76: THEN
73: p_api_version_number,
74: l_api_name,
75: G_PKG_NAME)
76: THEN
77: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
78: END IF;
79:
80: IF p_validation_level = fnd_api.g_valid_level_full THEN
81: -- Notes: if validations have been done in the precedence procedure which this one is being from, doing a full
76: THEN
77: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
78: END IF;
79:
80: IF p_validation_level = fnd_api.g_valid_level_full THEN
81: -- Notes: if validations have been done in the precedence procedure which this one is being from, doing a full
82: -- validation here is not necessary. The users can set the p_validation_level to fnd_api.g_valid_level_none
83: -- if they do not want to repeat the same validations. However, it is their responsibility to make sure
84: -- all proper validations have been done before calling this procedure. It is recommended that they let
78: END IF;
79:
80: IF p_validation_level = fnd_api.g_valid_level_full THEN
81: -- Notes: if validations have been done in the precedence procedure which this one is being from, doing a full
82: -- validation here is not necessary. The users can set the p_validation_level to fnd_api.g_valid_level_none
83: -- if they do not want to repeat the same validations. However, it is their responsibility to make sure
84: -- all proper validations have been done before calling this procedure. It is recommended that they let
85: -- this procedure handle the validations except that they know what they are doing.
86:
176: update mtl_txn_request_lines
177: set line_status = 5
178: where line_id = p_move_order_line_id;
179:
180: IF fnd_api.to_boolean(p_commit) THEN
181: commit work;
182: END IF;
183: */
184: x_return_status := FND_API.G_RET_STS_SUCCESS;
180: IF fnd_api.to_boolean(p_commit) THEN
181: commit work;
182: END IF;
183: */
184: x_return_status := FND_API.G_RET_STS_SUCCESS;
185:
186: EXCEPTION
187: WHEN OTHERS THEN
188: RAISE FND_API.G_EXC_ERROR;
184: x_return_status := FND_API.G_RET_STS_SUCCESS;
185:
186: EXCEPTION
187: WHEN OTHERS THEN
188: RAISE FND_API.G_EXC_ERROR;
189: END;
190:
191: IF fnd_api.to_boolean(p_commit) THEN
192: commit work;
187: WHEN OTHERS THEN
188: RAISE FND_API.G_EXC_ERROR;
189: END;
190:
191: IF fnd_api.to_boolean(p_commit) THEN
192: commit work;
193: END IF;
194: x_return_status := FND_API.G_RET_STS_SUCCESS;
195: EXCEPTION
190:
191: IF fnd_api.to_boolean(p_commit) THEN
192: commit work;
193: END IF;
194: x_return_status := FND_API.G_RET_STS_SUCCESS;
195: EXCEPTION
196: WHEN EXCP_USER_DEFINED THEN
197: Rollback to Update_Order_Line_Sts_PUB;
198: x_return_status := FND_API.G_RET_STS_ERROR;
194: x_return_status := FND_API.G_RET_STS_SUCCESS;
195: EXCEPTION
196: WHEN EXCP_USER_DEFINED THEN
197: Rollback to Update_Order_Line_Sts_PUB;
198: x_return_status := FND_API.G_RET_STS_ERROR;
199: fnd_msg_pub.count_and_get
200: ( p_count => x_msg_count
201: , p_data => x_msg_data);
202: WHEN FND_API.G_EXC_ERROR THEN
198: x_return_status := FND_API.G_RET_STS_ERROR;
199: fnd_msg_pub.count_and_get
200: ( p_count => x_msg_count
201: , p_data => x_msg_data);
202: WHEN FND_API.G_EXC_ERROR THEN
203: JTF_PLSQL_API.HANDLE_EXCEPTIONS(
204: P_API_NAME => L_API_NAME
205: ,P_PKG_NAME => G_PKG_NAME
206: ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
207: ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
208: ,X_MSG_COUNT => X_MSG_COUNT
209: ,X_MSG_DATA => X_MSG_DATA
210: ,X_RETURN_STATUS => X_RETURN_STATUS);
211: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
212: JTF_PLSQL_API.HANDLE_EXCEPTIONS(
213: P_API_NAME => L_API_NAME
214: ,P_PKG_NAME => G_PKG_NAME
215: ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
225: fnd_msg_pub.add;
226: fnd_msg_pub.count_and_get
227: ( p_count => x_msg_count
228: , p_data => x_msg_data);
229: x_return_status := fnd_api.g_ret_sts_error;
230: END Update_Order_Line_Status;
231:
232:
233: FUNCTION validate_mo_line_status (
234: /* $Header: cspgtmub.pls 120.1 2006/07/20 05:39:33 hhaugeru noship $ */
235: -- Start of Comments
236: -- Function name : validate_mo_line_status
237: -- Purpose : This function checks whether the status of all order lines of a move order has been closed.
238: -- It returns fnd_api.g_true if the status of all order lines has been closed.
239: -- Otherwise, it returns fnd_api.g_false.
240: -- History :
241: -- Person Date Descriptions
242: -- ------ ---- --------------
235: -- Start of Comments
236: -- Function name : validate_mo_line_status
237: -- Purpose : This function checks whether the status of all order lines of a move order has been closed.
238: -- It returns fnd_api.g_true if the status of all order lines has been closed.
239: -- Otherwise, it returns fnd_api.g_false.
240: -- History :
241: -- Person Date Descriptions
242: -- ------ ---- --------------
243: -- klou 03-Jan-2000 Add options for NONE or FULL validations.
268: WHERE line_id = l_line_id;
269:
270: IF l_line_status <> p_status_to_be_validated THEN
271: CLOSE l_mo_header_csr;
272: RETURN fnd_api.g_false;
273: END IF;
274: EXCEPTION
275: WHEN OTHERS THEN
276: CLOSE l_mo_header_csr;
273: END IF;
274: EXCEPTION
275: WHEN OTHERS THEN
276: CLOSE l_mo_header_csr;
277: RETURN fnd_api.g_false;
278: END;
279:
280: END LOOP;
281:
283: IF l_mo_header_csr%rowcount = 0 THEN
284: IF l_mo_header_csr%ISOPEN THEN
285: CLOSE l_mo_header_csr;
286: END IF;
287: RETURN fnd_api.g_false;
288: END IF;
289:
290: IF l_mo_header_csr%ISOPEN THEN
291: CLOSE l_mo_header_csr;
290: IF l_mo_header_csr%ISOPEN THEN
291: CLOSE l_mo_header_csr;
292: END IF;
293:
294: RETURN fnd_api.g_true;
295:
296: END validate_mo_line_status;
297:
298:
321: -- klou 01-Jan-2000 created.
322: --
323: -- NOTES: If validations have been done in the precedent procedure from which this one is being called, doing a
324: -- full validation here is unnecessary. To avoid repeating the same validations, you can set the
325: -- p_validation_level to fnd_api.g_valid_level_none when making the procedure call. However, it is your
326: -- responsibility to make sure all proper validations have been done before calling this procedure.
327: -- You are recommended to let this procedure handle the validations if you are not sure.
328: --
329: -- CAUTIONS: This procedure *ALWAYS* calls other procedures with validation_level set to FND_API.G_VALID_LEVEL_NONE.
325: -- p_validation_level to fnd_api.g_valid_level_none when making the procedure call. However, it is your
326: -- responsibility to make sure all proper validations have been done before calling this procedure.
327: -- You are recommended to let this procedure handle the validations if you are not sure.
328: --
329: -- CAUTIONS: This procedure *ALWAYS* calls other procedures with validation_level set to FND_API.G_VALID_LEVEL_NONE.
330: -- If you do not do your own validations before calling this procedure, you should set the p_validation_level
331: -- to FND_API.G_VALID_LEVEL_FULL when making the call.
332: -- End of Comments
333:
327: -- You are recommended to let this procedure handle the validations if you are not sure.
328: --
329: -- CAUTIONS: This procedure *ALWAYS* calls other procedures with validation_level set to FND_API.G_VALID_LEVEL_NONE.
330: -- If you do not do your own validations before calling this procedure, you should set the p_validation_level
331: -- to FND_API.G_VALID_LEVEL_FULL when making the call.
332: -- End of Comments
333:
334: P_Api_Version_Number IN NUMBER,
335: P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
331: -- to FND_API.G_VALID_LEVEL_FULL when making the call.
332: -- End of Comments
333:
334: P_Api_Version_Number IN NUMBER,
335: P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
336: P_Commit IN VARCHAR2 := FND_API.G_FALSE,
337: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
338: p_packlist_line_id IN NUMBER,
339: p_organization_id IN NUMBER,
332: -- End of Comments
333:
334: P_Api_Version_Number IN NUMBER,
335: P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
336: P_Commit IN VARCHAR2 := FND_API.G_FALSE,
337: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
338: p_packlist_line_id IN NUMBER,
339: p_organization_id IN NUMBER,
340: p_transaction_temp_id IN NUMBER,
333:
334: P_Api_Version_Number IN NUMBER,
335: P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
336: P_Commit IN VARCHAR2 := FND_API.G_FALSE,
337: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
338: p_packlist_line_id IN NUMBER,
339: p_organization_id IN NUMBER,
340: p_transaction_temp_id IN NUMBER,
341: p_quantity_received IN NUMBER,
345: p_lot_number IN VARCHAR2 := NULL,
346: p_revision IN VARCHAR2 := NULL,
347: p_receiving_option IN NUMBER := 0, --0 = receiving normal, 1 = receipt short, 2 = over receipt (but do not close the packlist and move order, 3 = over receipt (close everything)
348: px_transaction_header_id IN OUT NOCOPY NUMBER,
349: p_process_flag IN VARCHAR2 := fnd_api.g_false,
350: X_Return_Status OUT NOCOPY VARCHAR2,
351: X_Msg_Count OUT NOCOPY NUMBER,
352: X_Msg_Data OUT NOCOPY VARCHAR2)
353:
358: l_msg_data VARCHAR2(300);
359: l_check_existence NUMBER := 0;
360: l_return_status VARCHAR2(1);
361: l_msg_count NUMBER := 0;
362: l_commit VARCHAR2(1) := FND_API.G_FALSE;
363: l_creation_date DATE;
364: l_last_update_date DATE;
365: l_header_id NUMBER;
366: l_packlist_header_id NUMBER;
369: l_count NUMBER;
370:
371: -- for inserting data, the validation_level should be none
372: -- because we do not want to call core apps standard validations.
373: l_validation_level NUMBER := FND_API.G_VALID_LEVEL_NONE;
374: l_move_order_line_id NUMBER;
375: l_csp_mtltxn_rec CSP_MATERIAL_TRANSACTIONS_PVT.CSP_Rec_Type;
376: l_csp_mtltxn_over_rec CSP_MATERIAL_TRANSACTIONS_PVT.CSP_Rec_Type;
377: l_csp_mtltxn_bak_rec CSP_MATERIAL_TRANSACTIONS_PVT.CSP_Rec_Type;
395: l_temp_id_to_be_processed NUMBER;
396: l_quantity_shipped NUMBER := 0;
397: l_quantity_received NUMBER := 0;
398: l_avail_qty NUMBER := 0;
399: l_recv_less_than_txn VARCHAR2(1) := fnd_api.g_false;
400: l_org_received_qty NUMBER;
401: EXCP_USER_DEFINED EXCEPTION;
402:
403: CURSOR l_ml_records(l_transaction_temp_id NUMBER) IS
674: And organization_id = p_organization_id;
675: BEGIN
676: SAVEPOINT confirm_receipt_PUB;
677:
678: IF fnd_api.to_boolean(P_Init_Msg_List) THEN
679: -- initialize message list
680: FND_MSG_PUB.initialize;
681: END IF;
682: -- Standard call to check for call compatibility.
679: -- initialize message list
680: FND_MSG_PUB.initialize;
681: END IF;
682: -- Standard call to check for call compatibility.
683: IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
684: p_api_version_number,
685: l_api_name,
686: G_PKG_NAME)
687: THEN
684: p_api_version_number,
685: l_api_name,
686: G_PKG_NAME)
687: THEN
688: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
689: END IF;
690:
691: IF p_validation_level = fnd_api.g_valid_level_full THEN
692: -- Notes: if validations have been done in the precedence procedure which this one is being called, doing a full
687: THEN
688: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
689: END IF;
690:
691: IF p_validation_level = fnd_api.g_valid_level_full THEN
692: -- Notes: if validations have been done in the precedence procedure which this one is being called, doing a full
693: -- validation here is not necessary. You can set the p_validation_level to fnd_api.g_valid_level_none
694: -- if you do not want to repeat the same validations. However, it is your responsibility to make sure
695: -- all proper validations have been done before calling this procedure. It is recommended that you let
689: END IF;
690:
691: IF p_validation_level = fnd_api.g_valid_level_full THEN
692: -- Notes: if validations have been done in the precedence procedure which this one is being called, doing a full
693: -- validation here is not necessary. You can set the p_validation_level to fnd_api.g_valid_level_none
694: -- if you do not want to repeat the same validations. However, it is your responsibility to make sure
695: -- all proper validations have been done before calling this procedure. It is recommended that you let
696: -- this procedure handle the validations except that you know what you are doing.
697:
723:
724: END IF; -- end full validations
725:
726: -- Validate the p_packlist_line_id
727: IF nvl(p_packlist_line_id, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
728: FND_MESSAGE.SET_NAME ('CSP', 'CSP_MISSING_PARAMETERS');
729: FND_MESSAGE.SET_TOKEN ('PARAMETER', 'p_packlist_line_id', FALSE);
730: FND_MSG_PUB.ADD;
731: RAISE EXCP_USER_DEFINED;
772: fnd_msg_pub.add;
773: RAISE EXCP_USER_DEFINED;
774: END IF;
775:
776: IF p_validation_level = fnd_api.g_valid_level_full THEN
777: BEGIN
778: select packlist_line_id into l_check_existence
779: from csp_packlist_lines
780: where packlist_line_id = p_packlist_line_id
796: END IF;
797: END IF;
798:
799: -- Validate the quantity received
800: IF nvl(p_quantity_received, fnd_api.g_miss_num) = fnd_api.g_miss_num OR p_quantity_received < 0 THEN
801: /* IF p_quantity_received = 0 AND p_receiving_option = 1 THEN
802: -- close the move order and ship order because the receiving option is receiving short.
803: -- update quantity_received in the csp_packlist_lines table
804: CSP_PL_SHIP_UTIL.Update_Packlist_Sts_Qty (
802: -- close the move order and ship order because the receiving option is receiving short.
803: -- update quantity_received in the csp_packlist_lines table
804: CSP_PL_SHIP_UTIL.Update_Packlist_Sts_Qty (
805: P_Api_Version_Number => l_api_version_number,
806: P_Init_Msg_List => FND_API.G_true,
807: P_Commit => l_commit,
808: p_validation_level => l_validation_level,
809: p_organization_id => p_organization_id,
810: p_packlist_line_id => p_packlist_line_id,
816: x_msg_count => l_msg_count,
817: x_msg_data => l_msg_data
818: );
819:
820: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
821: RAISE FND_API.G_EXC_ERROR;
822: END IF;
823:
824: IF fnd_api.to_boolean(CSP_PL_SHIP_UTIL.validate_pl_line_status(l_packlist_header_id, '3', true)) THEN
817: x_msg_data => l_msg_data
818: );
819:
820: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
821: RAISE FND_API.G_EXC_ERROR;
822: END IF;
823:
824: IF fnd_api.to_boolean(CSP_PL_SHIP_UTIL.validate_pl_line_status(l_packlist_header_id, '3', true)) THEN
825: -- update the packlist_header_status
820: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
821: RAISE FND_API.G_EXC_ERROR;
822: END IF;
823:
824: IF fnd_api.to_boolean(CSP_PL_SHIP_UTIL.validate_pl_line_status(l_packlist_header_id, '3', true)) THEN
825: -- update the packlist_header_status
826: CSP_PL_SHIP_UTIL.update_packlist_header_sts (
827: P_Api_Version_Number => l_api_version_number,
828: P_Init_Msg_List => FND_API.G_true,
824: IF fnd_api.to_boolean(CSP_PL_SHIP_UTIL.validate_pl_line_status(l_packlist_header_id, '3', true)) THEN
825: -- update the packlist_header_status
826: CSP_PL_SHIP_UTIL.update_packlist_header_sts (
827: P_Api_Version_Number => l_api_version_number,
828: P_Init_Msg_List => FND_API.G_true,
829: P_Commit => l_commit,
830: p_validation_level => l_validation_level,
831: p_packlist_header_id => l_packlist_header_id,
832: p_organization_id => p_organization_id,
835: x_msg_count => l_msg_count,
836: x_msg_data => l_msg_data );
837: END IF;
838:
839: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
840: RAISE FND_API.G_EXC_ERROR;
841: END IF;
842:
843: OPEN l_ml_records(p_transaction_temp_id);
836: x_msg_data => l_msg_data );
837: END IF;
838:
839: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
840: RAISE FND_API.G_EXC_ERROR;
841: END IF;
842:
843: OPEN l_ml_records(p_transaction_temp_id);
844: FETCH l_ml_records INTO l_csp_mtltxn_rec;
904: CLOSE l_Get_Shipped_Received_Qty;
905: END IF;
906:
907: -- Validate the new to_subinventory and to_locator.
908: IF p_validation_level = fnd_api.g_valid_level_full THEN
909: IF p_to_subinventory_code IS NOT NULL THEN
910: DECLARE
911: l_subinventory_code VARCHAR2(10);
912: BEGIN
1015:
1016: -- change the revision to the specific revision when p_reivison is not null, 05/31/00 klou.
1017: l_csp_mtltxn_rec.revision := nvl(p_revision, l_csp_mtltxn_rec.revision);
1018:
1019: IF NOT fnd_api.to_boolean(Convert_Temp_UOM(l_csp_mtltxn_rec, p_quantity_received)) THEN
1020: -- Messages are set in the Convert_Temp_UOM function.
1021: RAISE EXCP_USER_DEFINED;
1022: END IF;
1023:
1032: X_Msg_Count => l_msg_count,
1033: X_Msg_Data => l_msg_data
1034: );
1035:
1036: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1037: RAISE FND_API.G_EXC_ERROR;
1038: END IF;
1039:
1040: -- Now we have to check whether we need to insert into the mtlt and msnt tables.
1033: X_Msg_Data => l_msg_data
1034: );
1035:
1036: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1037: RAISE FND_API.G_EXC_ERROR;
1038: END IF;
1039:
1040: -- Now we have to check whether we need to insert into the mtlt and msnt tables.
1041: IF nvl(l_csp_mtltxn_rec.item_lot_control_code, 1) <> 1 THEN
1038: END IF;
1039:
1040: -- Now we have to check whether we need to insert into the mtlt and msnt tables.
1041: IF nvl(l_csp_mtltxn_rec.item_lot_control_code, 1) <> 1 THEN
1042: IF nvl(p_lot_number, fnd_api.g_miss_char) = fnd_api.g_miss_char THEN
1043: fnd_message.set_name('CSP', 'CSP_OVER_RECEIPT_LOT_MISSED');
1044: fnd_message.set_token('ITEM_NAME', csp_pp_util.get_item_name(l_csp_mtltxn_rec.inventory_item_id), FALSE);
1045: fnd_msg_pub.add;
1046: RAISE EXCP_USER_DEFINED;
1069: ,p_mtlt_tbl => l_mtlt_tbl
1070: ,p_mtlt_tbl_size => 1
1071: );
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;
1080:
1081: -- If the item is also under serial control, we need to insert into the msnt.
1082: IF nvl(l_csp_mtltxn_rec.item_serial_control_code, 1) in (2,5) THEN
1083:
1084: IF nvl(p_serial_number, fnd_api.g_miss_char) = fnd_api.g_miss_char THEN
1085: fnd_message.set_name ('CSP', 'CSP_OVER_RECEIPT_SERIAL_MISSED');
1086: fnd_message.set_token('ITEM_NAME', csp_pp_util.get_item_name(l_csp_mtltxn_rec.inventory_item_id), FALSE);
1087: fnd_msg_pub.add;
1088: RAISE EXCP_USER_DEFINED;
1131: ,p_msnt_tbl => l_msnt_tbl
1132: ,p_msnt_tbl_size => 1
1133: );
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;
1142: END IF;
1143: ELSIF nvl(l_csp_mtltxn_rec.item_lot_control_code, 1) = 1 AND
1144: nvl(l_csp_mtltxn_rec.item_serial_control_code, 1) in (2, 5) THEN
1145: -- Serial control only
1146: IF nvl(p_serial_number, fnd_api.g_miss_char) = fnd_api.g_miss_char THEN
1147: fnd_message.set_name ('CSP', 'CSP_OVER_RECEIPT_SERIAL_MISSED');
1148: fnd_message.set_token('ITEM_NAME', csp_pp_util.get_item_name(l_csp_mtltxn_rec.inventory_item_id), FALSE);
1149: fnd_msg_pub.add;
1150: RAISE EXCP_USER_DEFINED;
1171: ,p_msnt_tbl => l_msnt_tbl
1172: ,p_msnt_tbl_size => 1
1173: );
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;
1217:
1218: -- Check whether the item is subinventory restricted.
1219: -- If yes, we have to make sure the p_to_subinventory_code is in the restricted list.
1220: -- The process_online procedure will take care of the validations so it can be optional to do them here.
1221: IF p_validation_level = fnd_api.g_valid_level_full THEN
1222: IF p_to_subinventory_code IS NOT NULL THEN
1223: DECLARE
1224: l_restrict_sub_code NUMBER;
1225: l_inventory_item_id NUMBER := l_csp_mtltxn_rec.inventory_item_id;
1338:
1339: -- Set the transaction_quantity = l_org_received_qty.
1340: l_csp_mtltxn_rec.transaction_quantity := l_org_received_qty;
1341:
1342: IF NOT fnd_api.to_boolean(Convert_Temp_UOM(l_csp_mtltxn_rec,
1343: (l_org_received_qty))) THEN
1344: -- Messages are set in the Convert_Temp_UOM function.
1345: RAISE EXCP_USER_DEFINED;
1346: ELSE
1361: X_Return_Status => l_return_status,
1362: X_Msg_Count => l_msg_count,
1363: X_Msg_Data => l_msg_data);
1364:
1365: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1366: RAISE FND_API.G_EXC_ERROR;
1367: END IF;
1368: l_csp_mtltxn_rec.transaction_temp_id := l_transaction_temp_id;
1369:
1362: X_Msg_Count => l_msg_count,
1363: X_Msg_Data => l_msg_data);
1364:
1365: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1366: RAISE FND_API.G_EXC_ERROR;
1367: END IF;
1368: l_csp_mtltxn_rec.transaction_temp_id := l_transaction_temp_id;
1369:
1370: l_temp_id_to_be_processed := l_csp_mtltxn_rec.transaction_temp_id;
1371:
1372: -- Update the existing mmtt record.
1373: -- This mmtt record is intended to stay in the temp table for the next shipment.
1374: l_csp_mtltxn_bak_rec.transaction_quantity := l_csp_mtltxn_bak_rec.transaction_quantity - l_org_received_qty;
1375: IF NOT fnd_api.to_boolean(Convert_Temp_UOM(l_csp_mtltxn_rec,
1376: (l_csp_mtltxn_bak_rec.transaction_quantity))) THEN
1377: -- Messages are set in the Convert_Temp_UOM function.
1378: RAISE EXCP_USER_DEFINED;
1379: ELSE
1389: X_Return_Status => l_return_status,
1390: X_Msg_Count => l_msg_count,
1391: X_Msg_Data => l_msg_data);
1392:
1393: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1394: RAISE FND_API.G_EXC_ERROR;
1395: END IF;
1396:
1397: Elsif l_org_received_qty = l_csp_mtltxn_rec.transaction_quantity Then
1390: X_Msg_Count => l_msg_count,
1391: X_Msg_Data => l_msg_data);
1392:
1393: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1394: RAISE FND_API.G_EXC_ERROR;
1395: END IF;
1396:
1397: Elsif l_org_received_qty = l_csp_mtltxn_rec.transaction_quantity Then
1398:
1408:
1409: CSP_Material_Transactions_PVT.Update_material_transactions(
1410: P_Api_Version_Number => p_api_version_number,
1411: P_Init_Msg_List => p_init_msg_list,
1412: P_Commit => fnd_api.g_false,
1413: p_validation_level => l_validation_level,
1414: P_CSP_Rec => l_csp_mtltxn_rec,
1415: X_Return_Status => l_return_status,
1416: X_Msg_Count => l_msg_count,
1416: X_Msg_Count => l_msg_count,
1417: X_Msg_Data => l_msg_data
1418: );
1419:
1420: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1421: RAISE FND_API.G_EXC_ERROR;
1422: END IF;
1423: l_temp_id_to_be_processed := l_csp_mtltxn_rec.transaction_temp_id;
1424:
1417: X_Msg_Data => l_msg_data
1418: );
1419:
1420: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1421: RAISE FND_API.G_EXC_ERROR;
1422: END IF;
1423: l_temp_id_to_be_processed := l_csp_mtltxn_rec.transaction_temp_id;
1424:
1425: Else -- this is an error.
1449: l_csp_mtltxn_over_rec.locator_id := l_csp_mtltxn_over_rec.transfer_to_location;
1450:
1451: END IF;
1452:
1453: IF NOT fnd_api.to_boolean(Convert_Temp_UOM(l_csp_mtltxn_over_rec,
1454: (l_csp_mtltxn_over_rec.transaction_quantity))) THEN
1455: -- Messages are set in the Convert_Temp_UOM function.
1456: RAISE EXCP_USER_DEFINED;
1457: ELSE
1470: X_Return_Status => l_return_status,
1471: X_Msg_Count => l_msg_count,
1472: X_Msg_Data => l_msg_data);
1473:
1474: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1475: RAISE FND_API.G_EXC_ERROR;
1476: END IF;
1477:
1478: l_csp_mtltxn_over_rec.transaction_temp_id :=l_transaction_temp_id;
1471: X_Msg_Count => l_msg_count,
1472: X_Msg_Data => l_msg_data);
1473:
1474: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1475: RAISE FND_API.G_EXC_ERROR;
1476: END IF;
1477:
1478: l_csp_mtltxn_over_rec.transaction_temp_id :=l_transaction_temp_id;
1479:
1486: fnd_msg_pub.add;
1487: RAISE EXCP_USER_DEFINED;
1488: END IF;
1489: IF nvl(l_csp_mtltxn_over_rec.item_lot_control_code, 1) <> 1 THEN
1490: IF nvl(p_lot_number, fnd_api.g_miss_char) = fnd_api.g_miss_char THEN
1491: fnd_message.set_name('CSP', 'CSP_OVER_RECEIPT_LOT_MISSED');
1492: fnd_message.set_token('ITEM_NAME', csp_pp_util.get_item_name(l_csp_mtltxn_over_rec.inventory_item_id), FALSE);
1493: fnd_msg_pub.add;
1494: RAISE EXCP_USER_DEFINED;
1523: ,p_mtlt_tbl => l_mtlt_tbl
1524: ,p_mtlt_tbl_size => 1
1525: );
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;
1590:
1591: END IF; -- end the update_line_status block
1592:
1593: ELSIF p_quantity_received = l_csp_mtltxn_rec.transaction_quantity THEN
1594: IF NOT fnd_api.to_boolean(Convert_Temp_UOM(l_csp_mtltxn_rec, p_quantity_received)) THEN
1595: -- Messages are set at the Convert_Temp_UOM function.
1596: RAISE EXCP_USER_DEFINED;
1597: ELSE
1598: l_csp_mtltxn_rec.primary_quantity := l_csp_mtltxn_rec.transaction_quantity;
1608:
1609: CSP_Material_Transactions_PVT.Update_material_transactions(
1610: P_Api_Version_Number => p_api_version_number,
1611: P_Init_Msg_List => p_init_msg_list,
1612: P_Commit => fnd_api.g_false,
1613: p_validation_level => l_validation_level,
1614: P_CSP_Rec => l_csp_mtltxn_rec,
1615: X_Return_Status => l_return_status,
1616: X_Msg_Count => l_msg_count,
1616: X_Msg_Count => l_msg_count,
1617: X_Msg_Data => l_msg_data
1618: );
1619:
1620: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1621: RAISE FND_API.G_EXC_ERROR;
1622: END IF;
1623:
1624: l_temp_id_to_be_processed := l_csp_mtltxn_rec.transaction_temp_id;
1617: X_Msg_Data => l_msg_data
1618: );
1619:
1620: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1621: RAISE FND_API.G_EXC_ERROR;
1622: END IF;
1623:
1624: l_temp_id_to_be_processed := l_csp_mtltxn_rec.transaction_temp_id;
1625:
1648: p_status => 5 ); -- update status to 5 = closed
1649:
1650: -- check whether all the line statuses of the order have been closed.
1651: -- If they are all closed, update the header status.
1652: IF FND_API.to_boolean(validate_mo_line_status (l_header_id, 5)) THEN
1653: -- update the header status in the mtl_txn_request_headers
1654: -- call a core apps api to update the line status.
1655: -- Since the core apps api does not return a status, we have to catch the exception
1656: -- it may throw.
1658: INV_Trohdr_Util.Update_Row_Status
1659: ( p_header_id => l_header_id,
1660: p_status => 5); -- update status to 5 = closed
1661:
1662: x_return_status := FND_API.G_RET_STS_SUCCESS;
1663: EXCEPTION
1664: WHEN OTHERS THEN
1665: RAISE FND_API.G_EXC_ERROR;
1666: END;
1661:
1662: x_return_status := FND_API.G_RET_STS_SUCCESS;
1663: EXCEPTION
1664: WHEN OTHERS THEN
1665: RAISE FND_API.G_EXC_ERROR;
1666: END;
1667: END IF;
1668: END IF; -- end the update_line_status block
1669:
1670: ELSE -- p_quantity_received less than transaction_quantity
1671: -- 1. create a new mmtt record having transaction_quantity = p_quantity_received
1672: -- and process_flag = 'Y' and transaction_status = 3.
1673: -- 2. update the transaction_quantity of the existing one to the remaining quantity.
1674: l_recv_less_than_txn := fnd_api.g_true;
1675:
1676: IF p_receiving_option NOT IN (0, 1) THEN
1677: fnd_message.set_name('CSP', 'CSP_INVALID_OVER_RECEIPT_QTY');
1678: fnd_msg_pub.add;
1699: AND organization_id = p_organization_id;
1700: l_packlist_sl_rec CSP_Pack_Serial_Lots_PVT.plsl_Rec_Type;
1701: l_serial_number VARCHAR2(30):= NULL;
1702: l_lot_number VARCHAR2(80) := NULL;
1703: l_serial_lot_flag VARCHAR2(1) := fnd_api.g_false;
1704:
1705: BEGIN
1706: If p_quantity_received > 0 Then
1707: l_csp_mtltxn_rec.transaction_quantity := p_quantity_received;
1708: l_csp_mtltxn_rec.primary_quantity := p_quantity_received;
1709: l_csp_mtltxn_rec.transaction_temp_id := NULL;
1710: l_move_order_line_id := l_csp_mtltxn_rec.move_order_line_id;
1711:
1712: IF NOT fnd_api.to_boolean(Convert_Temp_UOM(l_csp_mtltxn_rec, p_quantity_received)) THEN
1713: -- Messages are set in the Convert_Temp_UOM function.
1714: RAISE EXCP_USER_DEFINED;
1715: ELSE
1716: l_csp_mtltxn_rec.primary_quantity := l_csp_mtltxn_rec.transaction_quantity;
1734: X_Return_Status => l_return_status,
1735: X_Msg_Count => l_msg_count,
1736: X_Msg_Data => l_msg_data);
1737:
1738: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1739: RAISE FND_API.G_EXC_ERROR;
1740: END IF;
1741: l_temp_id_to_be_processed := l_transaction_temp_id;
1742: -- update the existing mmtt record
1735: X_Msg_Count => l_msg_count,
1736: X_Msg_Data => l_msg_data);
1737:
1738: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1739: RAISE FND_API.G_EXC_ERROR;
1740: END IF;
1741: l_temp_id_to_be_processed := l_transaction_temp_id;
1742: -- update the existing mmtt record
1743: l_csp_mtltxn_bak_rec.transaction_quantity := l_csp_mtltxn_bak_rec.transaction_quantity - p_quantity_received;
1743: l_csp_mtltxn_bak_rec.transaction_quantity := l_csp_mtltxn_bak_rec.transaction_quantity - p_quantity_received;
1744: -- l_csp_mtltxn_bak_rec.primary_quantity := l_csp_mtltxn_bak_rec.primary_quantity - p_quantity_received;
1745: l_csp_mtltxn_bak_rec.last_update_date := sysdate;
1746:
1747: IF NOT fnd_api.to_boolean(Convert_Temp_UOM(l_csp_mtltxn_bak_rec, l_csp_mtltxn_bak_rec.transaction_quantity)) THEN
1748: -- Messages are set in the Convert_Temp_UOM function.
1749: RAISE EXCP_USER_DEFINED;
1750: ELSE
1751: l_csp_mtltxn_bak_rec.primary_quantity := l_csp_mtltxn_bak_rec.transaction_quantity;
1753:
1754: CSP_Material_Transactions_PVT.Update_material_transactions(
1755: P_Api_Version_Number => p_api_version_number,
1756: P_Init_Msg_List => p_init_msg_list,
1757: P_Commit => fnd_api.g_false,
1758: p_validation_level => l_validation_level,
1759: P_CSP_Rec => l_csp_mtltxn_bak_rec,
1760: X_Return_Status => l_return_status,
1761: X_Msg_Count => l_msg_count,
1761: X_Msg_Count => l_msg_count,
1762: X_Msg_Data => l_msg_data
1763: );
1764:
1765: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1766: RAISE FND_API.G_EXC_ERROR;
1767: END IF;
1768:
1769: -- Check whether we need to update the msnt and the mtlt tables.
1762: X_Msg_Data => l_msg_data
1763: );
1764:
1765: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1766: RAISE FND_API.G_EXC_ERROR;
1767: END IF;
1768:
1769: -- Check whether we need to update the msnt and the mtlt tables.
1770: OPEN l_Get_Packlist_SL;
1789: p_qty_received => p_quantity_received,
1790: X_Return_Status => l_return_status,
1791: X_Msg_Count => l_msg_count,
1792: X_Msg_Data => l_msg_data);
1793: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1794: CLOSE l_Get_Packlist_SL;
1795: RAISE FND_API.G_EXC_ERROR;
1796: END IF;
1797: END IF;
1791: X_Msg_Count => l_msg_count,
1792: X_Msg_Data => l_msg_data);
1793: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1794: CLOSE l_Get_Packlist_SL;
1795: RAISE FND_API.G_EXC_ERROR;
1796: END IF;
1797: END IF;
1798:
1799: l_serial_lot_flag := fnd_api.g_true;
1795: RAISE FND_API.G_EXC_ERROR;
1796: END IF;
1797: END IF;
1798:
1799: l_serial_lot_flag := fnd_api.g_true;
1800: ELSE
1801: l_serial_number := p_serial_number;
1802: IF p_lot_number IS NULL THEN
1803: l_lot_number := l_packlist_sl_rec.lot_number;
1815: p_qty_received => p_quantity_received,
1816: X_Return_Status => l_return_status,
1817: X_Msg_Count => l_msg_count,
1818: X_Msg_Data => l_msg_data);
1819: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1820: CLOSE l_Get_Packlist_SL;
1821: RAISE FND_API.G_EXC_ERROR;
1822: END IF;
1823: l_serial_lot_flag := fnd_api.g_true;
1817: X_Msg_Count => l_msg_count,
1818: X_Msg_Data => l_msg_data);
1819: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1820: CLOSE l_Get_Packlist_SL;
1821: RAISE FND_API.G_EXC_ERROR;
1822: END IF;
1823: l_serial_lot_flag := fnd_api.g_true;
1824: EXIT;
1825: END IF;
1819: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1820: CLOSE l_Get_Packlist_SL;
1821: RAISE FND_API.G_EXC_ERROR;
1822: END IF;
1823: l_serial_lot_flag := fnd_api.g_true;
1824: EXIT;
1825: END IF;
1826: END IF;
1827: END LOOP;
1824: EXIT;
1825: END IF;
1826: END IF;
1827: END LOOP;
1828: IF NOT fnd_api.to_boolean(l_serial_lot_flag) AND
1829: l_Get_Packlist_SL%rowcount <> 0 THEN
1830: fnd_message.set_name ('CSP', 'CSP_NO_SERIAL_LOT_PACKLIST');
1831: fnd_message.set_token ('ROUTINE', l_api_name, FALSE);
1832: fnd_msg_pub.add;
1872: l_csp_mtltxn_misc_issue_rec := l_csp_mtltxn_bak_rec;
1873: l_csp_mtltxn_misc_issue_rec.transaction_quantity := l_misc_issue_qty;
1874: l_csp_mtltxn_misc_issue_rec.creation_date := sysdate;
1875:
1876: IF NOT fnd_api.to_boolean(Convert_Temp_UOM(l_csp_mtltxn_misc_issue_rec,
1877: (l_csp_mtltxn_misc_issue_rec.transaction_quantity))) THEN
1878: -- Messages are set in the Convert_Temp_UOM function.
1879: RAISE EXCP_USER_DEFINED;
1880: ELSE
1893: X_Return_Status => l_return_status,
1894: X_Msg_Count => l_msg_count,
1895: X_Msg_Data => l_msg_data);
1896:
1897: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1898: RAISE FND_API.G_EXC_ERROR;
1899: END IF;
1900:
1901: l_csp_mtltxn_misc_issue_rec.transaction_temp_id :=l_transaction_temp_id;
1894: X_Msg_Count => l_msg_count,
1895: X_Msg_Data => l_msg_data);
1896:
1897: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1898: RAISE FND_API.G_EXC_ERROR;
1899: END IF;
1900:
1901: l_csp_mtltxn_misc_issue_rec.transaction_temp_id :=l_transaction_temp_id;
1902: --//// Take care of the msnt and mtlt records when the whole packlist is closed in short, i.e. p_quantity_received=0
1919: p_qty_received => l_misc_issue_qty,
1920: X_Return_Status => l_return_status,
1921: X_Msg_Count => l_msg_count,
1922: X_Msg_Data => l_msg_data);
1923: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1924: CLOSE l_Get_Packlist_SL;
1925: RAISE FND_API.G_EXC_ERROR;
1926: END IF;
1927: l_serial_lot_flag := fnd_api.g_true;
1921: X_Msg_Count => l_msg_count,
1922: X_Msg_Data => l_msg_data);
1923: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1924: CLOSE l_Get_Packlist_SL;
1925: RAISE FND_API.G_EXC_ERROR;
1926: END IF;
1927: l_serial_lot_flag := fnd_api.g_true;
1928: ELSIF l_serial_number IS NOT NULL THEN
1929: -- The item is under serial control. It may be or may not be under lot control.
1923: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1924: CLOSE l_Get_Packlist_SL;
1925: RAISE FND_API.G_EXC_ERROR;
1926: END IF;
1927: l_serial_lot_flag := fnd_api.g_true;
1928: ELSIF l_serial_number IS NOT NULL THEN
1929: -- The item is under serial control. It may be or may not be under lot control.
1930: -- However, whether it is under lot control or not, it is not important to us
1931: -- because we have to transact one serial number at a time.
1937: p_qty_received => 1,
1938: X_Return_Status => l_return_status,
1939: X_Msg_Count => l_msg_count,
1940: X_Msg_Data => l_msg_data);
1941: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1942: CLOSE l_Get_Packlist_SL;
1943: RAISE FND_API.G_EXC_ERROR;
1944: END IF;
1945: l_serial_lot_flag := fnd_api.g_true;
1939: X_Msg_Count => l_msg_count,
1940: X_Msg_Data => l_msg_data);
1941: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1942: CLOSE l_Get_Packlist_SL;
1943: RAISE FND_API.G_EXC_ERROR;
1944: END IF;
1945: l_serial_lot_flag := fnd_api.g_true;
1946: ELSE
1947: -- This is an error. The csp_packlist_serial_lots table should not contain any error
1941: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1942: CLOSE l_Get_Packlist_SL;
1943: RAISE FND_API.G_EXC_ERROR;
1944: END IF;
1945: l_serial_lot_flag := fnd_api.g_true;
1946: ELSE
1947: -- This is an error. The csp_packlist_serial_lots table should not contain any error
1948: -- that has neither serial number nor lot number.
1949: l_serial_lot_flag := fnd_api.g_false;
1945: l_serial_lot_flag := fnd_api.g_true;
1946: ELSE
1947: -- This is an error. The csp_packlist_serial_lots table should not contain any error
1948: -- that has neither serial number nor lot number.
1949: l_serial_lot_flag := fnd_api.g_false;
1950: exit;
1951: END IF;
1952: END LOOP;
1953: IF NOT fnd_api.to_boolean(l_serial_lot_flag) AND
1949: l_serial_lot_flag := fnd_api.g_false;
1950: exit;
1951: END IF;
1952: END LOOP;
1953: IF NOT fnd_api.to_boolean(l_serial_lot_flag) AND
1954: l_Get_Packlist_SL%rowcount <> 0 THEN
1955: fnd_message.set_name ('CSP', 'CSP_NO_SERIAL_LOT_PACKLIST');
1956: fnd_message.set_token ('ROUTINE', l_api_name, FALSE);
1957: fnd_msg_pub.add;
1967: -- update the existing record
1968: l_csp_mtltxn_bak_rec.transaction_quantity := l_csp_mtltxn_bak_rec.transaction_quantity - l_misc_issue_qty;
1969: l_csp_mtltxn_bak_rec.last_update_date := sysdate;
1970:
1971: IF NOT fnd_api.to_boolean(Convert_Temp_UOM(l_csp_mtltxn_bak_rec, l_csp_mtltxn_bak_rec.transaction_quantity)) THEN
1972: -- Messages are set in the Convert_Temp_UOM function.
1973: RAISE EXCP_USER_DEFINED;
1974: ELSE
1975: l_csp_mtltxn_bak_rec.primary_quantity := l_csp_mtltxn_bak_rec.transaction_quantity;
1977:
1978: CSP_Material_Transactions_PVT.Update_material_transactions(
1979: P_Api_Version_Number => p_api_version_number,
1980: P_Init_Msg_List => p_init_msg_list,
1981: P_Commit => fnd_api.g_false,
1982: p_validation_level => l_validation_level,
1983: P_CSP_Rec => l_csp_mtltxn_bak_rec,
1984: X_Return_Status => l_return_status,
1985: X_Msg_Count => l_msg_count,
1985: X_Msg_Count => l_msg_count,
1986: X_Msg_Data => l_msg_data
1987: );
1988:
1989: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1990: RAISE FND_API.G_EXC_ERROR;
1991: END IF;
1992: End If;
1993:
1986: X_Msg_Data => l_msg_data
1987: );
1988:
1989: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1990: RAISE FND_API.G_EXC_ERROR;
1991: END IF;
1992: End If;
1993:
1994: -- Initialize the misc. issue.
2026:
2027: -- Update quantity_received in the csp_packlist_lines table
2028: CSP_PL_SHIP_UTIL.Update_Packlist_Sts_Qty (
2029: P_Api_Version_Number => l_api_version_number,
2030: P_Init_Msg_List => FND_API.G_true,
2031: P_Commit => l_commit,
2032: p_validation_level => l_validation_level,
2033: p_organization_id => p_organization_id,
2034: p_packlist_line_id => p_packlist_line_id,
2040: x_msg_count => l_msg_count,
2041: x_msg_data => l_msg_data
2042: );
2043:
2044: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2045: RAISE FND_API.G_EXC_ERROR;
2046: END IF;
2047:
2048: -- Define the packlist header status
2041: x_msg_data => l_msg_data
2042: );
2043:
2044: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2045: RAISE FND_API.G_EXC_ERROR;
2046: END IF;
2047:
2048: -- Define the packlist header status
2049: IF p_receiving_option = 0 OR p_receiving_option = 3 THEN
2057: WHEN no_data_found THEN
2058: null;
2059: END;
2060:
2061: IF fnd_api.to_boolean(CSP_PL_SHIP_UTIL.validate_pl_line_status(l_packlist_header_id, '3', true)) THEN
2062: IF (l_count > 0) THEN
2063: l_packlist_header_status := '4';
2064: ELSE
2065: l_packlist_header_status := '3';
2064: ELSE
2065: l_packlist_header_status := '3';
2066: END IF;
2067: ELSE
2068: l_packlist_header_status := fnd_api.g_miss_char; --fnd_api.g_miss_char = not to update the status
2069: END IF;
2070: ELSIF p_receiving_option = 1 THEN
2071: IF fnd_api.to_boolean(CSP_PL_SHIP_UTIL.validate_pl_line_status(l_packlist_header_id, '3', true)) THEN
2072: l_packlist_header_status := '4';
2067: ELSE
2068: l_packlist_header_status := fnd_api.g_miss_char; --fnd_api.g_miss_char = not to update the status
2069: END IF;
2070: ELSIF p_receiving_option = 1 THEN
2071: IF fnd_api.to_boolean(CSP_PL_SHIP_UTIL.validate_pl_line_status(l_packlist_header_id, '3', true)) THEN
2072: l_packlist_header_status := '4';
2073: ELSE
2074: l_packlist_header_status := fnd_api.g_miss_char; --fnd_api.g_miss_char = not to update the status
2075: END IF;
2070: ELSIF p_receiving_option = 1 THEN
2071: IF fnd_api.to_boolean(CSP_PL_SHIP_UTIL.validate_pl_line_status(l_packlist_header_id, '3', true)) THEN
2072: l_packlist_header_status := '4';
2073: ELSE
2074: l_packlist_header_status := fnd_api.g_miss_char; --fnd_api.g_miss_char = not to update the status
2075: END IF;
2076: ELSE
2077: l_packlist_header_status := fnd_api.g_miss_char; --fnd_api.g_miss_char = not to update the status
2078: END IF;
2073: ELSE
2074: l_packlist_header_status := fnd_api.g_miss_char; --fnd_api.g_miss_char = not to update the status
2075: END IF;
2076: ELSE
2077: l_packlist_header_status := fnd_api.g_miss_char; --fnd_api.g_miss_char = not to update the status
2078: END IF;
2079:
2080: -- Update the date_received and the packlist_status in the packlist. klou 5/17/00.
2081: Open l_Get_Packlist_Csr(l_packlist_header_id);
2097: -- Call the CSP_Packlist_Headers_PVT.Update_packlist_headers to update the packlist_status.
2098: CSP_Packlist_Headers_PVT.Update_packlist_headers(
2099: P_Api_Version_Number => l_api_version_number,
2100: P_Init_Msg_List => p_init_msg_list,
2101: P_Commit => FND_API.G_FALSE,
2102: p_validation_level => l_validation_level,
2103: P_Identity_Salesforce_Id => NULL,
2104: P_PLH_Rec => l_packlist_headers_rec,
2105: X_Return_Status => l_return_status,
2105: X_Return_Status => l_return_status,
2106: X_Msg_Count => l_msg_count,
2107: X_Msg_Data => l_msg_data);
2108:
2109: IF l_return_status <> fnd_api.g_ret_sts_success THEN
2110: RAISE FND_API.G_EXC_ERROR;
2111: END IF;
2112:
2113: If l_temp_id_to_be_processed IS NOT NULL THEN
2106: X_Msg_Count => l_msg_count,
2107: X_Msg_Data => l_msg_data);
2108:
2109: IF l_return_status <> fnd_api.g_ret_sts_success THEN
2110: RAISE FND_API.G_EXC_ERROR;
2111: END IF;
2112:
2113: If l_temp_id_to_be_processed IS NOT NULL THEN
2114: -- We are now ready to insert the data into interface table and delete the record in the temp table
2123: END IF;
2124: -- Call the csp_transactions_pub.transact_temp_record
2125: csp_transactions_pub.transact_temp_record (
2126: P_Api_Version_Number => l_api_version_number,
2127: P_Init_Msg_List => FND_API.G_true,
2128: P_Commit => l_commit,
2129: p_validation_level => l_validation_level,
2130: p_transaction_temp_id => l_temp_id_to_be_processed,
2131: px_transaction_header_id => l_transaction_header_id,
2133: X_Return_Status => l_return_status,
2134: X_Msg_Count => l_msg_count,
2135: X_Msg_Data => l_msg_data );
2136:
2137: IF l_return_status <> fnd_api.g_ret_sts_success THEN
2138: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2139: END IF;
2140:
2141: -- before calling the online process manager, we need to remove the temp record
2134: X_Msg_Count => l_msg_count,
2135: X_Msg_Data => l_msg_data );
2136:
2137: IF l_return_status <> fnd_api.g_ret_sts_success THEN
2138: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2139: END IF;
2140:
2141: -- before calling the online process manager, we need to remove the temp record
2142: IF not fnd_api.to_boolean(Clean_Up (l_temp_id_to_be_processed)) THEN
2138: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2139: END IF;
2140:
2141: -- before calling the online process manager, we need to remove the temp record
2142: IF not fnd_api.to_boolean(Clean_Up (l_temp_id_to_be_processed)) THEN
2143: RAISE EXCP_USER_DEFINED;
2144: END IF;
2145: End If;
2146: <
2144: END IF;
2145: End If;
2146: <
2147: -- Call the process_online to submit a concurrent request.
2148: IF fnd_api.to_boolean(p_process_flag) THEN
2149: IF NOT Call_Online ( p_transaction_header_id => l_transaction_header_id) THEN
2150: l_outcome := FALSE;
2151: x_return_status := FND_API.G_RET_STS_ERROR;
2152: GOTO END_ALL;
2147: -- Call the process_online to submit a concurrent request.
2148: IF fnd_api.to_boolean(p_process_flag) THEN
2149: IF NOT Call_Online ( p_transaction_header_id => l_transaction_header_id) THEN
2150: l_outcome := FALSE;
2151: x_return_status := FND_API.G_RET_STS_ERROR;
2152: GOTO END_ALL;
2153: END IF;
2154: END IF;
2155:
2154: END IF;
2155:
2156: px_transaction_header_id := l_transaction_header_id;
2157:
2158: IF fnd_api.to_boolean(p_commit) THEN
2159: commit work;
2160: END IF;
2161: x_return_status := FND_API.G_RET_STS_SUCCESS;
2162:
2157:
2158: IF fnd_api.to_boolean(p_commit) THEN
2159: commit work;
2160: END IF;
2161: x_return_status := FND_API.G_RET_STS_SUCCESS;
2162:
2163: <
2164: fnd_msg_pub.count_and_get
2165: ( p_count => x_msg_count
2166: , p_data => x_msg_data);
2167: EXCEPTION
2168: WHEN EXCP_USER_DEFINED THEN
2169: Rollback to confirm_receipt_PUB;
2170: x_return_status := FND_API.G_RET_STS_ERROR;
2171: fnd_msg_pub.count_and_get
2172: ( p_count => x_msg_count
2173: , p_data => x_msg_data);
2174: WHEN FND_API.G_EXC_ERROR THEN
2170: x_return_status := FND_API.G_RET_STS_ERROR;
2171: fnd_msg_pub.count_and_get
2172: ( p_count => x_msg_count
2173: , p_data => x_msg_data);
2174: WHEN FND_API.G_EXC_ERROR THEN
2175: JTF_PLSQL_API.HANDLE_EXCEPTIONS(
2176: P_API_NAME => L_API_NAME
2177: ,P_PKG_NAME => G_PKG_NAME
2178: ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
2179: ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
2180: ,X_MSG_COUNT => X_MSG_COUNT
2181: ,X_MSG_DATA => X_MSG_DATA
2182: ,X_RETURN_STATUS => X_RETURN_STATUS);
2183: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2184: JTF_PLSQL_API.HANDLE_EXCEPTIONS(
2185: P_API_NAME => L_API_NAME
2186: ,P_PKG_NAME => G_PKG_NAME
2187: ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
2197: fnd_msg_pub.add;
2198: fnd_msg_pub.count_and_get
2199: ( p_count => x_msg_count
2200: , p_data => x_msg_data);
2201: x_return_status := fnd_api.g_ret_sts_error;
2202: END confirm_receipt;
2203:
2204: Function Clean_Up (p_transaction_temp_id IN NUMBER)
2205: Return VARCHAR2
2416: IF l_ml_records%NOTFOUND THEN
2417: fnd_message.set_name ('CSP', 'CSP_NO_MO_TXN_RECORD');
2418: fnd_msg_pub.add;
2419: CLOSE l_ml_records;
2420: RAISE FND_API.G_EXC_ERROR;
2421: END IF;
2422: CLOSE l_ml_records;
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
2449: If l_Get_Serial_Lot_id_Csr%ROWCOUNT = 0 Then
2450: FND_MESSAGE.SET_NAME('CSP', 'CSP_RECEIPT_SERIAL_LOT_FAILURE');
2451: FND_MSG_PUB.ADD;
2452: Close l_Get_Serial_Lot_id_Csr;
2453: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2454: End If;
2455:
2456: Close l_Get_Serial_Lot_id_Csr;
2457: End;
2475: delete from mtl_material_transactions_temp
2476: where transaction_temp_id = l_temp_id_to_be_processed;
2477: End If;
2478:
2479: Return fnd_api.g_true;
2480: EXCEPTION
2481: WHEN OTHERS THEN
2482: fnd_message.set_name('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
2483: fnd_message.set_token ('ROUTINE', l_api_name, FALSE);
2482: fnd_message.set_name('CSP', 'CSP_UNEXPECTED_EXEC_ERRORS');
2483: fnd_message.set_token ('ROUTINE', l_api_name, FALSE);
2484: fnd_message.set_token ('SQLERRM', sqlerrm, TRUE);
2485: fnd_msg_pub.add;
2486: Return fnd_api.g_false;
2487: END Clean_Up;
2488:
2489: Function Call_Online (p_transaction_header_id NUMBER)
2490: Return Boolean
2584: IS
2585: l_api_version_number CONSTANT NUMBER := 1.0;
2586: l_api_name CONSTANT VARCHAR2(50) := 'Under_Over_Receipt';
2587: l_msg_data VARCHAR2(300);
2588: l_commit VARCHAR2(1) := fnd_api.g_false;
2589: l_check_existence NUMBER := 0;
2590: l_return_status VARCHAR2(1);
2591: l_msg_count NUMBER := 0;
2592: l_mtlt_tbl csp_pp_util.g_mtlt_tbl_type;
2592: l_mtlt_tbl csp_pp_util.g_mtlt_tbl_type;
2593: l_index NUMBER := 1;
2594: l_transaction_header_id NUMBER := NULL;
2595: l_account_id NUMBER := NULL;
2596: l_validation_level NUMBER := FND_API.G_VALID_LEVEL_NONE;
2597: --l_trolin_rec INV_Move_Order_PUB.Trolin_Rec_Type;
2598: l_csp_mtltxn_rec CSP_MATERIAL_TRANSACTIONS_PVT.CSP_Rec_Type;
2599: EXCP_USER_DEFINED EXCEPTION;
2600: Cursor l_Get_txn_header_id_csr IS
2814: IF l_ml_records%NOTFOUND THEN
2815: fnd_message.set_name ('CSP', 'CSP_NO_MO_TXN_RECORD');
2816: fnd_msg_pub.add;
2817: CLOSE l_ml_records;
2818: RAISE FND_API.G_EXC_ERROR;
2819: END IF;
2820: CLOSE l_ml_records;
2821:
2822: l_csp_mtltxn_rec.distribution_account_id := Get_CSP_Acccount_ID (l_csp_mtltxn_rec.organization_id);
2823:
2824: IF l_csp_mtltxn_rec.distribution_account_id IS NULL THEN
2825: fnd_message.set_name('CSP', 'CSP_RECEIPT_ACCOUNT_NOT_FOUND');
2826: fnd_msg_pub.add;
2827: RAISE FND_API.G_EXC_ERROR;
2828: END IF;
2829:
2830: IF p_receiving_option = 1 THEN -- receiving short
2831: l_csp_mtltxn_rec.transfer_subinventory := NULL;
2854: */
2855:
2856: CSP_Material_Transactions_PVT.Update_material_transactions(
2857: P_Api_Version_Number => l_api_version_number,
2858: P_Init_Msg_List => FND_API.G_TRUE,
2859: P_Commit => FND_API.G_FALSE,
2860: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2861: P_CSP_Rec => l_csp_mtltxn_rec,
2862: X_Return_Status => l_return_status ,
2855:
2856: CSP_Material_Transactions_PVT.Update_material_transactions(
2857: P_Api_Version_Number => l_api_version_number,
2858: P_Init_Msg_List => FND_API.G_TRUE,
2859: P_Commit => FND_API.G_FALSE,
2860: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2861: P_CSP_Rec => l_csp_mtltxn_rec,
2862: X_Return_Status => l_return_status ,
2863: X_Msg_Count => l_msg_count,
2856: CSP_Material_Transactions_PVT.Update_material_transactions(
2857: P_Api_Version_Number => l_api_version_number,
2858: P_Init_Msg_List => FND_API.G_TRUE,
2859: P_Commit => FND_API.G_FALSE,
2860: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2861: P_CSP_Rec => l_csp_mtltxn_rec,
2862: X_Return_Status => l_return_status ,
2863: X_Msg_Count => l_msg_count,
2864: X_Msg_Data => l_msg_data);
2862: X_Return_Status => l_return_status ,
2863: X_Msg_Count => l_msg_count,
2864: X_Msg_Data => l_msg_data);
2865:
2866: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2867: RAISE FND_API.G_EXC_ERROR;
2868: END IF;
2869:
2870: -- get the transaction header id
2863: X_Msg_Count => l_msg_count,
2864: X_Msg_Data => l_msg_data);
2865:
2866: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2867: RAISE FND_API.G_EXC_ERROR;
2868: END IF;
2869:
2870: -- get the transaction header id
2871: Open l_Get_txn_header_id_csr;
2874:
2875: -- Call the csp_transactions_pub.transact_temp_record
2876: csp_transactions_pub.transact_temp_record (
2877: P_Api_Version_Number => l_api_version_number,
2878: P_Init_Msg_List => FND_API.G_true,
2879: P_Commit => l_commit,
2880: p_validation_level => l_validation_level,
2881: p_transaction_temp_id => p_transaction_temp_id,
2882: px_transaction_header_id => l_transaction_header_id,
2884: X_Return_Status => l_return_status,
2885: X_Msg_Count => l_msg_count,
2886: X_Msg_Data => l_msg_data );
2887:
2888: IF l_return_status <> fnd_api.g_ret_sts_success THEN
2889: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2890: END IF;
2891:
2892: If NOT fnd_api.to_boolean(clean_up(p_transaction_temp_id)) THEN
2885: X_Msg_Count => l_msg_count,
2886: X_Msg_Data => l_msg_data );
2887:
2888: IF l_return_status <> fnd_api.g_ret_sts_success THEN
2889: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2890: END IF;
2891:
2892: If NOT fnd_api.to_boolean(clean_up(p_transaction_temp_id)) THEN
2893: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2888: IF l_return_status <> fnd_api.g_ret_sts_success THEN
2889: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2890: END IF;
2891:
2892: If NOT fnd_api.to_boolean(clean_up(p_transaction_temp_id)) THEN
2893: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2894: END IF;
2895: px_transaction_header_id := l_transaction_header_id;
2896: EXCEPTION
2889: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2890: END IF;
2891:
2892: If NOT fnd_api.to_boolean(clean_up(p_transaction_temp_id)) THEN
2893: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2894: END IF;
2895: px_transaction_header_id := l_transaction_header_id;
2896: EXCEPTION
2897: WHEN OTHERS THEN
2894: END IF;
2895: px_transaction_header_id := l_transaction_header_id;
2896: EXCEPTION
2897: WHEN OTHERS THEN
2898: x_return_status := FND_API.G_RET_STS_ERROR;
2899: px_transaction_header_id := NULL;
2900: END Under_Over_Receipt;
2901:
2902:
2949: l_temp_to_num VARCHAR2(30);
2950: l_number_length NUMBER; -- the length of the number section of the serial numbers
2951: l_new_fm_num NUMBER;
2952: l_new_to_num NUMBER;
2953: l_done_flag VARCHAR2(1) := fnd_api.g_true;
2954: l_serial_case NUMBER := NULL; -- 0 = requires to create a new mtlt record.
2955: l_quantity_remained NUMBER := p_qty_received;
2956: l_old_serial_temp_id NUMBER;
2957: l_new_serial_temp_id NUMBER;
3052: ,p_msnt_tbl => l_msnt_tbl
3053: ,p_msnt_tbl_size => 1
3054: );
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;
3075: ,p_msnt_tbl => l_msnt_tbl
3076: ,p_msnt_tbl_size => 1
3077: );
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;
3087:
3088: l_done_flag := fnd_api.g_true;
3089: l_serial_case := 0;
3090: exit;
3091:
3092: ELSIF l_temp_fm_num = l_num AND to_number(l_num) < to_number(l_temp_to_num) THEN
3129: ,p_msnt_tbl => l_msnt_tbl
3130: ,p_msnt_tbl_size => 1
3131: );
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;
3141:
3142: l_done_flag := fnd_api.g_true;
3143: l_serial_case := 0;
3144: exit;
3145:
3146: ELSIF l_temp_to_num = l_num AND to_number(l_temp_fm_num) < to_number(l_num) THEN
3183: ,p_msnt_tbl => l_msnt_tbl
3184: ,p_msnt_tbl_size => 1
3185: );
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;
3195:
3196: l_done_flag := fnd_api.g_true;
3197: l_serial_case := 0;
3198: exit;
3199:
3200: ELSIF l_num = l_temp_to_num AND l_num = l_temp_fm_num THEN
3202: -- fm_serial_number = serial00, to_serial_number = serial00
3203: -- p_serial_number = serial00.
3204: -- This is a case which fm_serial_number = to_serial_number.
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
3227: -- fm_serial_number = serial00, to_serial_number = serial09
3228: -- p_serial_number = serial11. Not in the record being examined, can be in next one.
3229: -- start another loop
3230: l_serial_case := null;
3231: l_done_flag := fnd_api.g_false;
3232: END IF;
3233:
3234: ELSIF l_prefix = l_temp_fm_prefix AND l_num IS NULL
3235: AND l_temp_to_num IS NULL AND l_temp_fm_num IS NULL THEN
3238: -- p_serial_number = serial.
3239: -- There is no serial number range in this case.
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
3258: exit;
3259: ELSE
3260: -- start another loop
3261: l_serial_case := null;
3262: l_done_flag := fnd_api.g_false;
3263: END IF;
3264: END LOOP;
3265:
3266: IF (l_Get_Msnt%rowcount = 0 AND p_serial_number IS NOT NULL) OR
3263: END IF;
3264: END LOOP;
3265:
3266: IF (l_Get_Msnt%rowcount = 0 AND p_serial_number IS NOT NULL) OR
3267: (l_Get_Msnt%rowcount <> 0 AND NOT fnd_api.to_boolean(l_done_flag)) THEN
3268: fnd_message.set_name ('CSP', 'CSP_NO_SERIAL_TXN_RECORD');
3269: fnd_msg_pub.add;
3270: IF l_Get_Msnt%ISOPEN THEN
3271: CLOSE l_Get_Msnt;
3353: ,p_mtlt_tbl => l_mtlt_tbl
3354: ,p_mtlt_tbl_size => 1
3355: );
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;
3428: ,p_mtlt_tbl => l_mtlt_tbl
3429: ,p_mtlt_tbl_size => 1
3430: );
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;
3474: END;
3475: Else
3476: NULL;
3477: End if;
3478: x_return_status := FND_API.G_RET_STS_SUCCESS;
3479: fnd_msg_pub.count_and_get
3480: ( p_count => x_msg_count
3481: , p_data => x_msg_data);
3482: EXCEPTION
3481: , p_data => x_msg_data);
3482: EXCEPTION
3483: WHEN EXCP_USER_DEFINED THEN
3484: Rollback to Transact_Serial_Lots_PUB;
3485: x_return_status := FND_API.G_RET_STS_ERROR;
3486: fnd_msg_pub.count_and_get
3487: ( p_count => x_msg_count
3488: , p_data => x_msg_data);
3489: WHEN FND_API.G_EXC_ERROR THEN
3485: x_return_status := FND_API.G_RET_STS_ERROR;
3486: fnd_msg_pub.count_and_get
3487: ( p_count => x_msg_count
3488: , p_data => x_msg_data);
3489: WHEN FND_API.G_EXC_ERROR THEN
3490: JTF_PLSQL_API.HANDLE_EXCEPTIONS(
3491: P_API_NAME => L_API_NAME
3492: ,P_PKG_NAME => G_PKG_NAME
3493: ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
3494: ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PUB
3495: ,X_MSG_COUNT => X_MSG_COUNT
3496: ,X_MSG_DATA => X_MSG_DATA
3497: ,X_RETURN_STATUS => X_RETURN_STATUS);
3498: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3499: JTF_PLSQL_API.HANDLE_EXCEPTIONS(
3500: P_API_NAME => L_API_NAME
3501: ,P_PKG_NAME => G_PKG_NAME
3502: ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
3512: fnd_msg_pub.add;
3513: fnd_msg_pub.count_and_get
3514: ( p_count => x_msg_count
3515: , p_data => x_msg_data);
3516: x_return_status := fnd_api.g_ret_sts_error;
3517: END Transact_Serial_Lots;
3518:
3519: FUNCTION Convert_Temp_UOM (p_csp_mtltxn_rec IN OUT NOCOPY CSP_MATERIAL_TRANSACTIONS_PVT.CSP_Rec_Type,
3520: p_quantity_convert IN NUMBER)
3538: fnd_message.set_name ('INV', 'INV_INVALID_UOM_CONV');
3539: fnd_message.set_token ('VALUE1', p_csp_mtltxn_rec.transaction_uom, TRUE);
3540: fnd_message.set_token ('VALUE2', p_csp_mtltxn_rec.item_primary_uom_code, TRUE);
3541: fnd_msg_pub.add;
3542: RETURN fnd_api.g_false;
3543: END IF;
3544: END IF;
3545: RETURN fnd_api.g_true;
3546: END Convert_Temp_UOM;
3541: fnd_msg_pub.add;
3542: RETURN fnd_api.g_false;
3543: END IF;
3544: END IF;
3545: RETURN fnd_api.g_true;
3546: END Convert_Temp_UOM;
3547:
3548: END CSP_MO_MTLTXNS_UTIL;