1: PACKAGE BODY csd_receive_pvt AS
2: /* $Header: csdvrcvb.pls 120.2.12010000.2 2008/10/30 08:00:37 subhat ship $ */
3:
4: -- ---------------------------------------------------------
5: -- Define global variables
3:
4: -- ---------------------------------------------------------
5: -- Define global variables
6: -- ---------------------------------------------------------
7: g_pkg_name CONSTANT VARCHAR2 (30) := 'CSD_RECEIVE_PVT';
8: g_file_name CONSTANT VARCHAR2 (12) := 'csdvrcvb.pls';
9: g_debug_level CONSTANT NUMBER := csd_gen_utility_pvt.g_debug_level;
10: g_prcess_sts_pending CONSTANT VARCHAR2(10) := 'PENDING';
11: g_rcpt_source_customer CONSTANT VARCHAR2(10) := 'CUSTOMER';
76:
77: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
78: THEN
79: fnd_log.STRING (fnd_log.level_procedure,
80: 'CSD.PLSQL.CSD_RECEIVE_PVT.RECEIVE_ITEM.BEGIN',
81: 'Entered RECEIVE_ITEM'
82: );
83: END IF;
84:
83: END IF;
84:
85: dump_receive_tbl ( p_receive_tbl,
86: fnd_log.level_statement,
87: 'CSD.PLSQL.CSD_RECEIVE_PVT.RECEIVE_ITEM.BEGIN'
88: );
89: IF fnd_api.to_boolean (p_init_msg_list)
90: THEN
91: -- initialize message list
110: --Validate all the records in the input table.
111: IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
112: THEN
113: fnd_log.STRING (fnd_log.level_event,
114: 'CSD.PLSQL.CSD_RECEIVE_PVT.RECEIVE_ITEM',
115: 'Validating Input'
116: );
117: END IF;
118: -------------Validate Input data.
148: --------POpulate the interface tables.
149: IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
150: THEN
151: fnd_log.STRING (fnd_log.level_event,
152: 'CSD.PLSQL.CSD_RECEIVE_PVT.RECEIVE_ITEM',
153: 'Populating Interface tables'
154: );
155: END IF;
156:
173: -- Call request online to invoke receiving processsor in online mode.
174: IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
175: THEN
176: fnd_log.STRING (fnd_log.level_event,
177: 'CSD.PLSQL.CSD_RECEIVE_PVT.RECEIVE_ITEM',
178: 'Calling the receive api online'
179: );
180: END IF;
181:
197: IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
198: THEN
199: fnd_log.STRING
200: (fnd_log.level_event,
201: 'CSD.PLSQL.CSD_RECEIVE_PVT.RECEIVE_ITEM',
202: 'Checking the errors in interface tables after the receive process'
203: );
204: END IF;
205:
236:
237: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
238: THEN
239: fnd_log.STRING (fnd_log.level_procedure,
240: 'CSD.PLSQL.CSD_RECEIVE_PVT.RECEIVE_ITEM.END',
241: 'Leaving RECEIVE_ITEM'
242: );
243: END IF;
244:
267:
268: IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)
269: THEN
270: fnd_log.STRING (fnd_log.level_error,
271: 'CSD.PLSQL.CSD_RECEIVE_PVT.RECEIVE_ITEM',
272: 'EXC_ERROR in Receive_Item[' || x_msg_data || ']'
273: );
274: END IF;
275: WHEN fnd_api.g_exc_unexpected_error
281:
282: IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
283: THEN
284: fnd_log.STRING (fnd_log.level_exception,
285: 'CSD.PLSQL.CSD_RECEIVE_PVT.RECEIVE_ITEM',
286: 'EXC_UNEXPECTED_ERROR in Receive_Item[' || x_msg_data || ']'
287: );
288: END IF;
289: WHEN OTHERS
301:
302: IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)
303: THEN
304: fnd_log.STRING (fnd_log.level_unexpected,
305: 'CSD.PLSQL.CSD_RECEIVE_PVT.RECEIVE_ITEM',
306: 'SQL Message in Receive_Item[' || SQLERRM || ']'
307: );
308: END IF;
309: END receive_item;
377: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
378: THEN
379: fnd_log.STRING
380: (fnd_log.level_procedure,
381: 'CSD.PLSQL.CSD_RECEIVE_PVT.POPULATE_RCV_INTF_TBLS.BEGIN',
382: 'Entered Populate_Rcv_Intf_Tbls'
383: );
384: END IF;
385:
407: ---Program logic.......
408: IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
409: THEN
410: fnd_log.STRING (fnd_log.level_event,
411: 'CSD.PLSQL.CSD_RECEIVE_PVT.POPULATE_RCV_INTF_TBLS',
412: 'Inserting header interface table data'
413: );
414: END IF;
415:
486: IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
487: THEN
488: fnd_log.STRING
489: (fnd_log.level_event,
490: 'CSD.PLSQL.CSD_RECEIVE_PVT.POPULATE_RCV_INTF_TBLS',
491: 'Calling execute immediate with sql['
492: || sql_str
493: || ']'
494: );
500:
501: IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
502: THEN
503: fnd_log.STRING (fnd_log.level_event,
504: 'CSD.PLSQL.CSD_RECEIVE_PVT.POPULATE_RCV_INTF_TBLS',
505: 'Inserting transactions interface table data'
506: );
507: END IF;
508:
662: IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
663: THEN
664: fnd_log.STRING
665: (fnd_log.level_event,
666: 'CSD.PLSQL.CSD_RECEIVE_PVT.POPULATE_RCV_INTF_TBLS',
667: 'Calling execute immediate with sql['
668: || sql_str
669: || ']'
670: );
679: IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
680: THEN
681: fnd_log.STRING
682: (fnd_log.level_event,
683: 'CSD.PLSQL.CSD_RECEIVE_PVT.POPULATE_RCV_INTF_TBLS',
684: 'Calling execute immediate with sql['
685: || sql_str
686: || ']'
687: );
705: IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
706: THEN
707: fnd_log.STRING
708: (fnd_log.level_event,
709: 'CSD.PLSQL.CSD_RECEIVE_PVT.POPULATE_RCV_INTF_TBLS',
710: 'Inserting lot interface table data for ['
711: || l_intf_txn_id
712: || ']lot number['
713: || p_receive_rec.lot_number
746: IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
747: THEN
748: fnd_log.STRING
749: (fnd_log.level_event,
750: 'CSD.PLSQL.CSD_RECEIVE_PVT.POPULATE_RCV_INTF_TBLS',
751: 'Calling execute immediate with sql['
752: || sql_str
753: || ']'
754: );
772: IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
773: THEN
774: fnd_log.STRING
775: (fnd_log.level_event,
776: 'CSD.PLSQL.CSD_RECEIVE_PVT.POPULATE_RCV_INTF_TBLS',
777: 'Inserting serial interface table data for ['
778: || l_intf_txn_id
779: || ']serial number['
780: || p_receive_rec.serial_number
813: IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
814: THEN
815: fnd_log.STRING
816: (fnd_log.level_event,
817: 'CSD.PLSQL.CSD_RECEIVE_PVT.POPULATE_RCV_INTF_TBLS',
818: 'Calling execute immediate with sql['
819: || sql_str
820: || ']'
821: );
835:
836: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
837: THEN
838: fnd_log.STRING (fnd_log.level_procedure,
839: 'CSD.PLSQL.CSD_RECEIVE_PVT.POPULATE_RCV_INTF_TBLS',
840: 'Leaving POPULATE_RCV_INTF_TBLS'
841: );
842: END IF;
843:
855: IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)
856: THEN
857: fnd_log.STRING
858: (fnd_log.level_error,
859: 'CSD.PLSQL.CSD_RECEIVE_PVT.POPULATE_RCV_INTF_TBLS',
860: 'EXC_EXC_ERROR in populate rcv intf tbls [' || x_msg_data || ']'
861: );
862: END IF;
863: WHEN fnd_api.g_exc_unexpected_error
870: IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
871: THEN
872: fnd_log.STRING
873: (fnd_log.level_exception,
874: 'CSD.PLSQL.CSD_RECEIVE_PVT.POPULATE_RCV_INTF_TBLS',
875: 'EXC_UNEXPECTED_ERROR in populate rcv intf tbls[' || x_msg_data || ']'
876: );
877: END IF;
878: WHEN OTHERS
891: IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)
892: THEN
893: fnd_log.STRING
894: (fnd_log.level_unexpected,
895: 'CSD.PLSQL.CSD_RECEIVE_PVT.POPULATE_RCV_INTF_TBLS',
896: 'SQL Message in populate rcv intf tbls[' || SQLERRM || ']'
897: );
898: END IF;
899: END populate_rcv_intf_tbls;
975:
976: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
977: THEN
978: fnd_log.STRING (fnd_log.level_procedure,
979: 'CSD.PLSQL.CSD_RECEIVE_PVT.RCV_REQ_ONLINE.BEGIN',
980: 'Entered RCV_REQ_ONLINE'
981: );
982: END IF;
983: x_progress := '000';
1012:
1013: IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
1014: THEN
1015: fnd_log.STRING (fnd_log.level_event,
1016: 'CSD.PLSQL.CSD_RECEIVE_PVT.RCV_REQ_ONLINE',
1017: 'Calling receiving processor with req group id['
1018: || p_request_group_id
1019: || ']'
1020: );
1035:
1036: IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
1037: THEN
1038: fnd_log.STRING (fnd_log.level_event,
1039: 'CSD.PLSQL.CSD_RECEIVE_PVT.RCV_REQ_ONLINE',
1040: 'receiving processor, rc=['
1041: || l_ret_code
1042: || '],message['
1043: || l_MESSAGE
1057: p_data => l_MESSAGE,
1058: p_msg_index_out => l_msg_index_out
1059: );
1060: fnd_log.STRING (fnd_log.level_error,
1061: 'CSD.PLSQL.CSD_RECEIVE_PVT.RCV_REQ_ONLINE',
1062: 'receiving processor, error[' || l_MESSAGE || ']'
1063: );
1064: END LOOP;
1065: END IF;
1084: THEN
1085: IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
1086: THEN
1087: fnd_log.STRING (fnd_log.level_event,
1088: 'CSD.PLSQL.CSD_RECEIVE_PVT.RCV_REQ_ONLINE',
1089: 'fnd_trnasaction.synchronous TIMED OUT'
1090: );
1091: END IF;
1092:
1112: IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
1113: THEN
1114: fnd_log.STRING
1115: (fnd_log.level_event,
1116: 'CSD.PLSQL.CSD_RECEIVE_PVT.RCV_REQ_ONLINE',
1117: 'fnd_trnasaction.synchronous: no concurrent manager available,groupid['
1118: || TO_CHAR (p_request_group_id)
1119: || ']'
1120: );
1124: THEN
1125: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1126: THEN
1127: fnd_log.STRING (fnd_log.level_statement,
1128: 'CSD.PLSQL.CSD_RECEIVE_PVT.RCV_REQ_ONLINE',
1129: 'Adding FND message, groupid['
1130: || TO_CHAR (p_request_group_id)
1131: || ']'
1132: );
1145:
1146: IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)
1147: THEN
1148: fnd_log.MESSAGE (fnd_log.level_error,
1149: 'CSD.PLSQL.CSD_RECEIVE_PVT.RCV_REQ_ONLINE');
1150: END IF;
1151:
1152: fnd_msg_pub.ADD;
1153: x_return_status := fnd_api.g_ret_sts_error;
1156: THEN
1157: IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
1158: THEN
1159: fnd_log.STRING (fnd_log.level_event,
1160: 'CSD.PLSQL.CSD_RECEIVE_PVT.RCV_REQ_ONLINE',
1161: 'fnd_synschronous:rc['
1162: || l_Ret_code
1163: || '],outcome['
1164: || l_outcome
1296:
1297: IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
1298: THEN
1299: fnd_log.STRING (fnd_log.level_event,
1300: 'CSD.PLSQL.CSD_RECEIVE_PVT.RCV_REQ_ONLINE',
1301: 'fnd_trnasaction.synchronous:[' || l_str1 || ']'
1302: );
1303: END IF;
1304:
1312:
1313: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1314: THEN
1315: fnd_log.STRING (fnd_log.level_procedure,
1316: 'CSD.PLSQL.CSD_RECEIVE_PVT.RCV_REQ_ONLINE.END',
1317: 'Leaving RCV_REQ_ONLINE'
1318: );
1319: END IF;
1320: EXCEPTION
1327:
1328: IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)
1329: THEN
1330: fnd_log.STRING (fnd_log.level_error,
1331: 'CSD.PLSQL.CSD_RECEIVE_PVT.RCV_REQ_ONLINE',
1332: 'EXC_ERROR[' || x_msg_data || ']'
1333: );
1334: END IF;
1335: WHEN fnd_api.g_exc_unexpected_error
1341:
1342: IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
1343: THEN
1344: fnd_log.STRING (fnd_log.level_exception,
1345: 'CSD.PLSQL.CSD_RECEIVE_PVT.RCV_REQ_ONLINE',
1346: 'EXC_UNEXPECTED_ERROR in RCV_REQ_ONLINE[' || x_msg_data || ']'
1347: );
1348: END IF;
1349: WHEN OTHERS
1361:
1362: IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)
1363: THEN
1364: fnd_log.STRING (fnd_log.level_unexpected,
1365: 'CSD.PLSQL.CSD_RECEIVE_PVT.RCV_REQ_ONLINE',
1366: 'SQL Error MEssage in RCV_REQ_ONLINE[' || SQLERRM || ']'
1367: );
1368: END IF;
1369: END rcv_req_online;
1406:
1407: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1408: THEN
1409: fnd_log.STRING (fnd_log.level_procedure,
1410: 'CSD.PLSQL.CSD_RECEIVE_PVT.DELETE_INTF_TBLS.BEGIN',
1411: 'Entered Delete_Intf_Tbls'
1412: );
1413: END IF;
1414:
1471: ----------------------------------------------Delete from headers table.
1472: IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
1473: THEN
1474: fnd_log.STRING (fnd_log.level_event,
1475: 'CSD.PLSQL.CSD_RECEIVE_PVT.DELETE_INTF_TBLS',
1476: 'Deleting from the headers table'
1477: );
1478: END IF;
1479:
1491: ----------------------------------------------Delete from the detail txn records.
1492: IF (fnd_log.level_event >= fnd_log.g_current_runtime_level)
1493: THEN
1494: fnd_log.STRING (fnd_log.level_event,
1495: 'CSD.PLSQL.CSD_RECEIVE_PVT.DELETE_INTF_TBLS',
1496: 'Deleting from the detail table'
1497: );
1498: END IF;
1499:
1514:
1515: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1516: THEN
1517: fnd_log.STRING (fnd_log.level_procedure,
1518: 'CSD.PLSQL.CSD_RECEIVE_PVT.DELETE_INTF_TBLS.END',
1519: 'Leaving DELETE_INTF_TBLS'
1520: );
1521: END IF;
1522: EXCEPTION
1526: ROLLBACK ;
1527: IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level)
1528: THEN
1529: fnd_log.STRING (fnd_log.level_exception,
1530: 'CSD.PLSQL.CSD_RECEIVE_PVT.DELETE_INTF_TBLS',
1531: 'EXC_UNEXPECTED_ERROR in delete_intf_tbls'
1532: );
1533: END IF;
1534: WHEN OTHERS
1543:
1544: IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)
1545: THEN
1546: fnd_log.STRING (fnd_log.level_unexpected,
1547: 'CSD.PLSQL.CSD_RECEIVE_PVT.DELETE_INTF_TBLS',
1548: 'SQL Message in delete_intf_tbls[' || SQLERRM || ']'
1549: );
1550: END IF;
1551: END delete_intf_tbls;
1565: BEGIN
1566: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1567: THEN
1568: fnd_log.STRING (fnd_log.level_procedure,
1569: 'CSD.PLSQL.CSD_RECEIVE_PVT.CHECK_GROUP_ID.BEGIN',
1570: 'Entered check_group_id, groupid[' || p_group_id
1571: || ']'
1572: );
1573: END IF;
1581: THEN
1582: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1583: THEN
1584: fnd_log.STRING (fnd_log.level_procedure,
1585: 'CSD.PLSQL.CSD_RECEIVE_PVT.CHECK_GROUP_ID.END',
1586: 'returning false from check_group_id'
1587: );
1588: END IF;
1589:
1591: ELSE
1592: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1593: THEN
1594: fnd_log.STRING (fnd_log.level_procedure,
1595: 'CSD.PLSQL.CSD_RECEIVE_PVT.CHECK_GROUP_ID.END',
1596: 'returning true from check_group_id'
1597: );
1598: END IF;
1599:
1770:
1771: IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)
1772: THEN
1773: fnd_log.STRING (fnd_log.level_error,
1774: 'CSD.PLSQL.CSD_RECEIVE_PVT.log_error_stack',
1775: 'error[' || l_msg || ']'
1776: );
1777: END IF;
1778: END LOOP;
1777: END IF;
1778: END LOOP;
1779: END IF;
1780: END log_error_stack;
1781: END csd_receive_pvt;