[Home] [Help]
1: PACKAGE BODY igs_or_inst_imp_001 AS
2: /* $Header: IGSOR14B.pls 120.7 2006/06/28 13:10:48 gmaheswa ship $ */
3: -------------------------------------------------------------------------------------------
4: --Change History:
5: --Who When What
50: FND_MESSAGE.SET_NAME('IGS','IGS_OR_INST_IMP_FAIL');
51: FND_MESSAGE.SET_TOKEN('INT_ID',p_which_rec);
52: FND_MESSAGE.SET_TOKEN('ERROR_CODE',v_error_log.linex);
53: FND_LOG.STRING_WITH_CONTEXT (fnd_log.level_exception,
54: 'igs.plsql.igs_or_inst_imp_001.imp_or_institution.' || p_error_code,
55: fnd_message.get || '-' || p_error_text,NULL,NULL,NULL,NULL,NULL,g_request_id);
56:
57: END log_writer;
58:
226: l_owner VARCHAR2(30);
227:
228: BEGIN
229:
230: IF FND_LOG.test(FND_LOG.LEVEL_EXCEPTION,'igs.plsql.igs_or_inst_imp_001') THEN
231: gb_write_exception_log1 := TRUE;
232: ELSE
233: gb_write_exception_log1 := FALSE;
234: END IF;
301: );
302: EXCEPTION
303: WHEN OTHERS THEN
304: retcode := 2;
305: IF FND_LOG.test(FND_LOG.LEVEL_EXCEPTION,'igs.plsql.igs_or_inst_imp_001') THEN
306: fnd_log.string_with_context (fnd_log.level_exception,
307: 'igs.plsql.igs_or_inst_imp_001.imp_or_institution.MainProc',
308: ' ' || SQLERRM,NULL,NULL,NULL,NULL,NULL,g_request_id);
309: END IF;
303: WHEN OTHERS THEN
304: retcode := 2;
305: IF FND_LOG.test(FND_LOG.LEVEL_EXCEPTION,'igs.plsql.igs_or_inst_imp_001') THEN
306: fnd_log.string_with_context (fnd_log.level_exception,
307: 'igs.plsql.igs_or_inst_imp_001.imp_or_institution.MainProc',
308: ' ' || SQLERRM,NULL,NULL,NULL,NULL,NULL,g_request_id);
309: END IF;
310:
311: --Enable Business Event before quiting Bulk Process
426:
427: --Log a message to the Log File in the Conc Manager that the INSTITUTION CODE in the cwlk table is incorrect
428:
429: IF gb_write_exception_log1 = TRUE THEN
430: igs_or_inst_imp_001.log_writer(v_inst_rec.interface_id,'E001');
431: END IF;
432:
433: -- Update error_code for this record
434: -- Update Status of the Record to 3 to indicate Error
449: ROLLBACK TO s_point;
450:
451: --Log a message to the Log File that the Create of table failed
452: IF gb_write_exception_log1 = TRUE THEN
453: igs_or_inst_imp_001.log_writer(v_inst_rec.interface_id,l_error_code,l_error_text);
454: END IF;
455: -- Set error_code/error_text
456: UPDATE IGS_OR_INST_INT
457: SET error_code = l_error_code, error_text= l_error_text, STATUS = '3'
466: ROLLBACK TO s_point;
467:
468: --Log a message to the Log File that the Create of Alternate Id failed
469: IF gb_write_exception_log1 = TRUE THEN
470: igs_or_inst_imp_001.log_writer(v_inst_rec.interface_id,l_error_code);
471: END IF;
472:
473: UPDATE IGS_OR_INST_INT
474: SET error_code = l_error_code,
495: ELSE
496:
497: --Log a message to the Log File that the validation of Institution failed
498: IF gb_write_exception_log1 = TRUE THEN
499: igs_or_inst_imp_001.log_writer(v_inst_rec.interface_id,l_val_err);
500: END IF;
501:
502: --Update Error_code field
503: UPDATE IGS_OR_INST_INT
519: ROLLBACK TO s_point;
520:
521: --Log a message to the Log File that the Create of inst failed with reason
522: IF gb_write_exception_log1 = TRUE THEN
523: igs_or_inst_imp_001.log_writer(v_inst_rec.interface_id,l_error_code,l_error_text);
524: END IF;
525: -- Set error_code/error_text
526: UPDATE IGS_OR_INST_INT
527: SET error_code = l_error_code, error_text= l_error_text , STATUS = '3'
536: ROLLBACK TO s_point;
537: --Log a message to the Log File that the Update of Crosswalk Master failed
538:
539: IF gb_write_exception_log1 = TRUE THEN
540: igs_or_inst_imp_001.log_writer(v_inst_rec.interface_id,'E040');
541: END IF;
542:
543: --Update Error_code field
544: UPDATE IGS_OR_INST_INT
557: ROLLBACK TO s_point;
558:
559: --Log a message to the Log File that the Create of Alternate Id failed
560: IF gb_write_exception_log1 = TRUE THEN
561: igs_or_inst_imp_001.log_writer(v_inst_rec.interface_id,l_error_code);
562: END IF;
563:
564: -- Update the error_code field
565: UPDATE IGS_OR_INST_INT
590:
591: --Log a message to the Log File that the validation of
592:
593: IF gb_write_exception_log1 = TRUE THEN
594: igs_or_inst_imp_001.log_writer(v_inst_rec.interface_id,l_val_err);
595: END IF;
596:
597: --Update Error_code field
598: UPDATE IGS_OR_INST_INT
610: --Error Out
611: --Log a message to the Log File in the Conc Manager that the INSTITUTION CODE in the cwlk table is incorrect
612:
613: IF gb_write_exception_log1 = TRUE THEN
614: igs_or_inst_imp_001.log_writer(v_inst_rec.interface_id,'E006');
615: END IF;
616:
617: -- Update ERROR_CODE/ERROR_TEXT
618: UPDATE IGS_OR_INST_INT
629: ROLLBACK TO s_point;
630: -- ssawhney moved all together
631: --Log a message to the Log File that the Create of table failed
632: IF gb_write_exception_log1 = TRUE THEN
633: igs_or_inst_imp_001.log_writer(v_inst_rec.interface_id,l_error_code,l_error_text);
634: END IF;
635: -- Set error_code/error_text
636: UPDATE IGS_OR_INST_INT
637: SET error_code = l_error_code, error_text= l_error_text, STATUS = '3'
650: -- log the message that the data is not perfect, more than one crosswalk ids exists for the given
651: -- alternater_id and alternater_id_value
652:
653: IF gb_write_exception_log1 = TRUE THEN
654: igs_or_inst_imp_001.log_writer(v_inst_rec.interface_id, 'E051');
655: END IF;
656:
657: -- Set error_code/error_text
658: UPDATE IGS_OR_INST_INT
677: --Log a message to the Log File that the Update of Crosswalk Master failed
678: IF l_error_code <> 'E051' THEN
679:
680: IF gb_write_exception_log1 = TRUE THEN
681: igs_or_inst_imp_001.log_writer(v_inst_rec.interface_id,'E040');
682: END IF;
683:
684: --Update error_code/error_text field
685: UPDATE IGS_OR_INST_INT
695: ROLLBACK TO s_point;
696: --Log a message to the Log File that the Create of Alternate Id failed
697:
698: IF gb_write_exception_log1 = TRUE THEN
699: igs_or_inst_imp_001.log_writer(v_inst_rec.interface_id,l_error_code);
700: END IF;
701:
702: -- Update the error_code field
703: UPDATE IGS_OR_INST_INT
725: END IF;
726: ELSE
727: --Log a message to the Log File that the Update of Crosswalk Master failed
728: IF gb_write_exception_log1 = TRUE THEN
729: igs_or_inst_imp_001.log_writer(v_inst_rec.interface_id,l_val_err);
730: END IF;
731: --Update Error_code field
732: -- ssawhney moved all together
733: UPDATE IGS_OR_INST_INT
748:
749: IF l_Errind = 'Y' THEN
750: ROLLBACK TO s_point;
751: IF gb_write_exception_log1 = TRUE THEN
752: igs_or_inst_imp_001.log_writer(v_inst_rec.interface_id,l_error_code,l_error_text);
753: END IF;
754: -- Set error_code/error_text
755: UPDATE IGS_OR_INST_INT
756: SET error_code = l_error_code, error_text= l_error_text , STATUS = '3'
778: ROLLBACK TO s_point;
779: --Log a message to the Log File that the Create of Crosswalk Master failed
780:
781: IF gb_write_exception_log1 = TRUE THEN
782: igs_or_inst_imp_001.log_writer(v_inst_rec.interface_id,'E004');
783: END IF;
784:
785: UPDATE IGS_OR_INST_INT
786: SET ERROR_CODE = 'E004',error_text =NULL, STATUS = '3'
792: IF l_Errind = 'Y' THEN
793: ROLLBACK TO s_point;
794: --Log a message to the Log File that the Update of Crosswalk Master failed
795: IF gb_write_exception_log1 = TRUE THEN
796: igs_or_inst_imp_001.log_writer(v_inst_rec.interface_id,'E007');
797: END IF;
798:
799: UPDATE IGS_OR_INST_INT
800: SET error_code = 'E007',error_text=NULL , STATUS = '3'
806: IF l_Errind = 'Y' THEN
807: ROLLBACK TO s_point;
808: --Log a message to the Log File that the Create of Alternate Id failed
809: IF gb_write_exception_log1 = TRUE THEN
810: igs_or_inst_imp_001.log_writer(v_inst_rec.interface_id,l_error_code);
811: END IF;
812:
813: UPDATE IGS_OR_INST_INT
814: SET error_code = l_error_code,
838: END IF;
839: ELSE
840: --Log a message to the Log File that the Update of Crosswalk Master failed
841: IF gb_write_exception_log1 = TRUE THEN
842: igs_or_inst_imp_001.log_writer(v_inst_rec.interface_id,l_val_err);
843: END IF;
844: --Update Error_code field
845: -- ssawhney moved all together
846: UPDATE IGS_OR_INST_INT
856: IF l_exists IS NULL THEN -- Error Has occured, as the institution code is not Present in the OSS
857: --Error Out
858: --Log a message to the Log File in the Conc Manager that the INSTITUTION CODE in the cwlk table is incorrect
859: IF gb_write_exception_log1 = TRUE THEN
860: igs_or_inst_imp_001.log_writer(v_inst_rec.interface_id,'E006');
861: END IF;
862: -- Update error_code/error_text
863: UPDATE IGS_OR_INST_INT
864: SET error_code = 'E006', error_text=NULL , STATUS = '3'
873: IF l_Errind = 'Y' THEN
874: ROLLBACK TO s_point;
875: --Log a message to the Log File that the Create of table failed
876: IF gb_write_exception_log1 = TRUE THEN
877: igs_or_inst_imp_001.log_writer(v_inst_rec.interface_id,l_error_code,l_error_text);
878: END IF;
879: -- Set error_code/error_text
880: UPDATE IGS_OR_INST_INT
881: SET error_code = l_error_code, error_text= l_error_text, STATUS = '3'
898: IF l_Errind = 'Y' THEN
899: ROLLBACK TO s_point;
900: --Log a message to the Log File that the Creation of Crosswalk Master failed
901: IF gb_write_exception_log1 = TRUE THEN
902: igs_or_inst_imp_001.log_writer(v_inst_rec.interface_id,'E004');
903: END IF;
904:
905: UPDATE IGS_OR_INST_INT
906: SET error_code = 'E004' , error_text =NULL, STATUS = '3'
912: IF l_Errind = 'Y' THEN
913: ROLLBACK TO s_point;
914: --Log a message to the Log File that the Creation of Crosswalk detail failed
915: IF gb_write_exception_log1 = TRUE THEN
916: igs_or_inst_imp_001.log_writer(v_inst_rec.interface_id,'E007');
917: END IF;
918: --update error_code/error_text -- ssawhney moved all together
919: UPDATE IGS_OR_INST_INT
920: SET ERROR_code = 'E007', error_text = NULL, STATUS = '3'
928: IF l_Errind = 'Y' THEN
929: ROLLBACK TO s_point;
930: --Log a message to the Log File that the Creation of Alternate Id failed
931: IF gb_write_exception_log1 = TRUE THEN
932: igs_or_inst_imp_001.log_writer(v_inst_rec.interface_id,l_error_code);
933: END IF;
934: --set error_code/error_text -- ssawhney moved all together
935: UPDATE IGS_OR_INST_INT
936: SET error_code = l_error_code,
959: END IF;
960: ELSE
961: --Log a message to the Log File that the Update of Crosswalk Master failed
962: IF gb_write_exception_log1 = TRUE THEN
963: igs_or_inst_imp_001.log_writer(v_inst_rec.interface_id,l_val_err);
964: END IF;
965: --Update Error_code field -- ssawhney moved all together
966: UPDATE IGS_OR_INST_INT
967: SET error_code = l_val_err, error_text= NULL, STATUS = '3'
984: EXCEPTION
985: WHEN OTHERS THEN
986: IF gb_write_exception_log1 THEN
987: FND_LOG.STRING_WITH_CONTEXT (FND_LOG.LEVEL_EXCEPTION,
988: 'igs.plsql.igs_or_inst_imp_001.simplealtidcomp.others',
989: SQLERRM, NULL,NULL,NULL,NULL,NULL, IGS_OR_INST_IMP_001.G_REQUEST_ID);
990: END IF;
991: APP_EXCEPTION.Raise_Exception;
992: END simpleAltidcomp;
985: WHEN OTHERS THEN
986: IF gb_write_exception_log1 THEN
987: FND_LOG.STRING_WITH_CONTEXT (FND_LOG.LEVEL_EXCEPTION,
988: 'igs.plsql.igs_or_inst_imp_001.simplealtidcomp.others',
989: SQLERRM, NULL,NULL,NULL,NULL,NULL, IGS_OR_INST_IMP_001.G_REQUEST_ID);
990: END IF;
991: APP_EXCEPTION.Raise_Exception;
992: END simpleAltidcomp;
993:
1114: IF l_exists IS NULL THEN -- Error Has occured, as the institution code is not Present in the OSS
1115:
1116: --Log a message to the Log File in the Conc Manager that the INSTITUTION CODE in the cwlk table is incorrect
1117: IF gb_write_exception_log1 = TRUE THEN
1118: igs_or_inst_imp_001.log_writer(v_inst_rec.interface_id,'E001');
1119: END IF;
1120: -- Update Error_code/error_text field -- ssawhney moved all together
1121: UPDATE IGS_OR_INST_INT
1122: SET error_code = 'E001', error_text=NULL, STATUS = '3'
1138: ROLLBACK TO s_point;
1139:
1140: --Log a message to the Log File that the Create of table failed
1141: IF gb_write_exception_log1 = TRUE THEN
1142: igs_or_inst_imp_001.log_writer(v_inst_rec.interface_id,l_error_code,l_error_text);
1143: END IF;
1144:
1145: -- Set error_code/error_text
1146: UPDATE IGS_OR_INST_INT
1154: IF l_Errind = 'Y' THEN
1155: ROLLBACK TO s_point;
1156: --Log a message to the Log File that the Create of Crosswalk Detail failed
1157: IF gb_write_exception_log1 = TRUE THEN
1158: igs_or_inst_imp_001.log_writer(v_inst_rec.interface_id,'E007');
1159: END IF;
1160: --Update the error_code/error_text field -- ssawhney moved all together
1161: UPDATE IGS_OR_INST_INT
1162: SET error_code = 'E007' , error_text=NULL, STATUS = '3'
1167: IF l_Errind = 'Y' THEN
1168: ROLLBACK TO s_point;
1169: --Log a message to the Log File that the Create of Alternate Id failed
1170: IF gb_write_exception_log1 = TRUE THEN
1171: igs_or_inst_imp_001.log_writer(v_inst_rec.interface_id,l_error_code);
1172: END IF;
1173: --Update error_code/text
1174: UPDATE IGS_OR_INST_INT
1175: SET error_code = l_error_code,error_text=NULL, STATUS = '3'
1196: END IF;
1197: ELSE
1198: --Log a message to the Log File that the Update of Crosswalk Master failed
1199: IF gb_write_exception_log1 = TRUE THEN
1200: igs_or_inst_imp_001.log_writer(v_inst_rec.interface_id,l_val_err);
1201: END IF;
1202: --Update Error_code field -- ssawhney moved all together
1203: UPDATE IGS_OR_INST_INT
1204: SET error_code = l_val_err, error_text= NULL, STATUS = '3'
1218: ROLLBACK TO s_point;
1219:
1220: --Log a message to the Log File that the Create of inst failed
1221: IF gb_write_exception_log1 = TRUE THEN
1222: igs_or_inst_imp_001.log_writer(v_inst_rec.interface_id,l_error_code,l_error_text);
1223: END IF;
1224: -- Set error_code/error_text -- ssawhney moved all together
1225: UPDATE IGS_OR_INST_INT
1226: SET error_code = l_error_code, error_text= l_error_text, STATUS = '3'
1235: IF l_Errind = 'Y' THEN
1236: ROLLBACK TO s_point;
1237: --Log a message to the Log File that the Update of Crosswalk Master failed
1238: IF gb_write_exception_log1 = TRUE THEN
1239: igs_or_inst_imp_001.log_writer(v_inst_rec.interface_id,'E040');
1240: END IF;
1241: --update error_code/error_text -- ssawhney moved all together
1242: UPDATE IGS_OR_INST_INT
1243: SET error_code = 'E040', error_text=NULL, STATUS = '3'
1251: IF l_Errind = 'Y' THEN
1252: ROLLBACK TO s_point;
1253: --Log a message to the Log File that the Create of Crosswalk Detail failed
1254: IF gb_write_exception_log1 = TRUE THEN
1255: igs_or_inst_imp_001.log_writer(v_inst_rec.interface_id,'E007');
1256: END IF;
1257: --Update error_code/error_text -- ssawhney moved all together
1258: UPDATE IGS_OR_INST_INT
1259: SET error_code = 'E007',error_text=NULL, STATUS = '3'
1264: IF l_Errind = 'Y' THEN
1265: ROLLBACK TO s_point;
1266: --Log a message to the Log File that the Create of Alternate Id failed
1267: IF gb_write_exception_log1 = TRUE THEN
1268: igs_or_inst_imp_001.log_writer(v_inst_rec.interface_id,l_error_code);
1269: END IF;
1270: --update error_code/error_text -- ssawhney moved all together
1271: UPDATE IGS_OR_INST_INT
1272: SET error_code = l_error_code, error_text=NULL, STATUS = '3'
1294: END IF;
1295: ELSE
1296: --Log a message to the Log File that the Update of Crosswalk Master failed
1297: IF gb_write_exception_log1 = TRUE THEN
1298: igs_or_inst_imp_001.log_writer(v_inst_rec.interface_id,l_val_err);
1299: END IF;
1300: --Update Error_code field
1301: UPDATE IGS_OR_INST_INT
1302: SET error_code = l_val_err, error_text= NULL, STATUS = '3'
1310: CLOSE c_inst_present ;
1311: IF l_exists IS NULL THEN -- Error Has occured, as the institution code is not Present in the OSS
1312: --Log a message to the Log File in the Conc Manager that the INSTITUTION CODE in the cwlk table is incorrect
1313: IF gb_write_exception_log1 = TRUE THEN
1314: igs_or_inst_imp_001.log_writer(v_inst_rec.interface_id,'E006');
1315: END IF;
1316: -- Update error_code/error_text -- ssawhney moved all together
1317: UPDATE IGS_OR_INST_INT
1318: SET error_code = 'E006',error_text=NULL, STATUS = '3'
1326: IF l_Errind = 'Y' THEN
1327: ROLLBACK TO s_point;
1328:
1329: IF gb_write_exception_log1 = TRUE THEN
1330: igs_or_inst_imp_001.log_writer(v_inst_rec.interface_id,l_error_code,l_error_text);
1331: END IF;
1332:
1333: -- Set error_code/error_text -- ssawhney moved all together
1334: UPDATE IGS_OR_INST_INT
1348: IF l_cwlkinst_rec.CROSSWALK_ID <> l_cwlk_master_present.crosswalk_id THEN
1349: -- log the message that the data is not perfect, more than one crosswalk ids exists for the given
1350: -- alternater_id and alternater_id_value
1351: IF gb_write_exception_log1 = TRUE THEN
1352: igs_or_inst_imp_001.log_writer(v_inst_rec.interface_id, 'E051');
1353: END IF;
1354: -- Set error_code/error_text
1355: UPDATE IGS_OR_INST_INT
1356: SET error_code = 'E051', error_text= 'crosswalk_id of crosswalk details table does not match with the master record', status = 3
1371: ROLLBACK TO s_point;
1372: IF l_error_code <> 'E051' THEN
1373: --Log a message to the Log File that the Update of Crosswalk master failed
1374: IF gb_write_exception_log1 = TRUE THEN
1375: igs_or_inst_imp_001.log_writer(v_inst_rec.interface_id,'E040');
1376: END IF;
1377: --Update error_code/error_text -- ssawhney moved all together
1378: UPDATE IGS_OR_INST_INT
1379: SET error_code = 'E040', error_text=NULL, STATUS = '3'
1388: IF l_Errind = 'Y' THEN
1389: ROLLBACK TO s_point;
1390: --Log a message to the Log File that the Create Crosswalk detail failed
1391: IF gb_write_exception_log1 = TRUE THEN
1392: igs_or_inst_imp_001.log_writer(v_inst_rec.interface_id,'E007');
1393: END IF;
1394:
1395: --Update error_code/error_text -- ssawhney moved all together
1396: UPDATE IGS_OR_INST_INT
1402: IF l_Errind = 'Y' THEN
1403: ROLLBACK TO s_point;
1404: --Log a message to the Log File that the Create Alternate Id failed
1405: IF gb_write_exception_log1 = TRUE THEN
1406: igs_or_inst_imp_001.log_writer(v_inst_rec.interface_id,l_error_code);
1407: END IF;
1408: --update status
1409: UPDATE IGS_OR_INST_INT
1410: SET error_code = l_error_code, error_text=NULL, STATUS = '3'
1431: END IF;
1432: ELSE
1433: --Log a message to the Log File that the Update of Crosswalk Master failed
1434: IF gb_write_exception_log1 = TRUE THEN
1435: igs_or_inst_imp_001.log_writer(v_inst_rec.interface_id,l_val_err);
1436: END IF;
1437: --Update Error_code field -- ssawhney moved all together
1438: UPDATE IGS_OR_INST_INT
1439: SET error_code = l_val_err, error_text= NULL, STATUS = '3'
1446: END IF; -- The Institution Code in the Crosswalk table is present
1447: ELSE -- l_count = 0
1448: --Log a message to the Log File in the Conc Manager that the record for exact match is not found in the crosswalk dtl table
1449: IF gb_write_exception_log1 = TRUE THEN
1450: igs_or_inst_imp_001.log_writer(v_inst_rec.interface_id,'E041');
1451: END IF;
1452: --Update error_code/error_text
1453:
1454: UPDATE IGS_OR_INST_INT
1459: ELSE -- The Alternate Id value is Null
1460:
1461: --Log a message to the Log File in the Conc Manager that the Alternate Id Value cannot be Null for Exact Match
1462: IF gb_write_exception_log1 = TRUE THEN
1463: igs_or_inst_imp_001.log_writer(v_inst_rec.interface_id,'E042');
1464: END IF;
1465:
1466: -- Update error_code/error_text
1467: UPDATE IGS_OR_INST_INT
1483: EXCEPTION
1484: WHEN OTHERS THEN
1485: IF gb_write_exception_log1 THEN
1486: FND_LOG.STRING_WITH_CONTEXT (FND_LOG.LEVEL_EXCEPTION,
1487: 'igs.plsql.igs_or_inst_imp_001.exactAltidcomp.others',
1488: SQLERRM, NULL,NULL,NULL,NULL,NULL, IGS_OR_INST_IMP_001.G_REQUEST_ID);
1489: END IF;
1490: APP_EXCEPTION.Raise_Exception;
1491: END exactAltidcomp;
1484: WHEN OTHERS THEN
1485: IF gb_write_exception_log1 THEN
1486: FND_LOG.STRING_WITH_CONTEXT (FND_LOG.LEVEL_EXCEPTION,
1487: 'igs.plsql.igs_or_inst_imp_001.exactAltidcomp.others',
1488: SQLERRM, NULL,NULL,NULL,NULL,NULL, IGS_OR_INST_IMP_001.G_REQUEST_ID);
1489: END IF;
1490: APP_EXCEPTION.Raise_Exception;
1491: END exactAltidcomp;
1492:
1624: -- kumma, 2446007
1625: l_rec_count := l_rec_count + 1;
1626: IF l_rec_count > 2 THEN
1627: IF gb_write_exception_log1 = TRUE THEN
1628: igs_or_inst_imp_001.log_writer(v_inst_rec.interface_id, 'E051');
1629: END IF;
1630: -- Set error_code/error_text
1631: UPDATE IGS_OR_INST_INT
1632: SET error_code = 'E051', error_text= 'crosswalk_id of crosswalk details table does not match with the master record', status = 3
1643: EXCEPTION
1644: WHEN INVALID_NUMBER THEN
1645: --Log a message to the Log File that for neumeric match Alternater Id should be neumeric value.
1646: IF gb_write_exception_log1 = TRUE THEN
1647: igs_or_inst_imp_001.log_writer(v_inst_record.interface_id,'E052');
1648: END IF;
1649: -- Update Error_code/error_text
1650: IF c_neumeric_test%ISOPEN THEN
1651: CLOSE c_neumeric_test;
1685:
1686: IF l_exists IS NULL THEN -- Error Has occured, as the institution code is not Present in the OSS
1687: --Log a message to the Log File in the Conc Manager that the INSTITUTION CODE in the cwlk table is incorrect
1688: IF gb_write_exception_log1 = TRUE THEN
1689: igs_or_inst_imp_001.log_writer(v_inst_rec.interface_id,'E001');
1690: END IF;
1691: -- Update error_code/error_text -- ssawhney moved all together
1692: UPDATE IGS_OR_INST_INT
1693: SET error_code = 'E001',error_text=NULL, STATUS = '3'
1703: ROLLBACK TO s_point;
1704:
1705: --Log a message to the Log File that the Create of table failed
1706: IF gb_write_exception_log1 = TRUE THEN
1707: igs_or_inst_imp_001.log_writer(v_inst_rec.interface_id,l_error_code,l_error_text);
1708: END IF;
1709: -- Set error_code/error_text -- ssawhney moved all together
1710: UPDATE IGS_OR_INST_INT
1711: SET error_code = l_error_code, error_text= l_error_text, STATUS = '3'
1721: IF l_Errind = 'Y' THEN
1722: ROLLBACK TO s_point;
1723: --Log a message to the Log File that the Create of Crosswalk Detail failed
1724: IF gb_write_exception_log1 = TRUE THEN
1725: igs_or_inst_imp_001.log_writer(v_inst_rec.interface_id,'E007');
1726: END IF;
1727: --Update error_code/error_text -- ssawhney moved all together
1728: UPDATE IGS_OR_INST_INT
1729: SET error_code = 'E007', error_text=NULL, STATUS = '3'
1734: IF l_Errind = 'Y' THEN
1735: ROLLBACK TO s_point;
1736: --Log a message to the Log File that the Create of Alternate Id failed
1737: IF gb_write_exception_log1 = TRUE THEN
1738: igs_or_inst_imp_001.log_writer(v_inst_rec.interface_id,l_error_code);
1739: END IF;
1740: --update error_code/error_text
1741: UPDATE IGS_OR_INST_INT
1742: SET error_code = l_error_code, error_text=NULL, STATUS = '3'
1761: END IF;
1762: ELSE
1763: --Log a message to the Log File that the Update of Crosswalk Master failed
1764: IF gb_write_exception_log1 = TRUE THEN
1765: igs_or_inst_imp_001.log_writer(v_inst_rec.interface_id,l_val_err);
1766: END IF;
1767: --Update Error_code field -- ssawhney moved all together
1768: UPDATE IGS_OR_INST_INT
1769: SET error_code = l_val_err, error_text= NULL, STATUS = '3'
1782: ROLLBACK TO s_point;
1783:
1784: --Log a message to the Log File that the Create of inst failed
1785: IF gb_write_exception_log1 = TRUE THEN
1786: igs_or_inst_imp_001.log_writer(v_inst_rec.interface_id,l_error_code,l_error_text);
1787: END IF;
1788: -- Set error_code/error_text
1789: UPDATE IGS_OR_INST_INT
1790: SET error_code = l_error_code, error_text= l_error_text, STATUS = '3'
1797: IF l_Errind = 'Y' THEN
1798: ROLLBACK TO s_point;
1799: --Log a message to the Log File that the Update of Crosswalk master failed
1800: IF gb_write_exception_log1 = TRUE THEN
1801: igs_or_inst_imp_001.log_writer(v_inst_rec.interface_id,'E040');
1802: END IF;
1803: --Update error_code/error_text -- ssawhney moved all together
1804: UPDATE IGS_OR_INST_INT
1805: SET error_code = 'E040' ,error_text=NULL, STATUS = '3'
1815: IF l_Errind = 'Y' THEN
1816: ROLLBACK TO s_point;
1817: --Log a message to the Log File that the Create of Crosswalk Detail failed
1818: IF gb_write_exception_log1 = TRUE THEN
1819: igs_or_inst_imp_001.log_writer(v_inst_rec.interface_id,'E007');
1820: END IF;
1821: --Update error_code/error_text -- ssawhney moved all together
1822: UPDATE IGS_OR_INST_INT
1823: SET error_code = 'E007',error_text=NULL, STATUS = '3'
1828: IF l_Errind = 'Y' THEN
1829: ROLLBACK TO s_point;
1830: --Log a message to the Log File that the Create of Alternate Id failed
1831: IF gb_write_exception_log1 = TRUE THEN
1832: igs_or_inst_imp_001.log_writer(v_inst_rec.interface_id,l_error_code);
1833: END IF;
1834: --Update error_code/error_text -- ssawhney moved all together
1835: UPDATE IGS_OR_INST_INT
1836: SET error_code = l_error_code,error_text=NULL, STATUS = '3'
1856: END IF;
1857: ELSE
1858: --Log a message to the Log File that the Update of Crosswalk Master failed
1859: IF gb_write_exception_log1 = TRUE THEN
1860: igs_or_inst_imp_001.log_writer(v_inst_rec.interface_id,l_val_err);
1861: END IF;
1862: --Update Error_code field -- ssawhney moved all together
1863: UPDATE IGS_OR_INST_INT
1864: SET error_code = l_val_err, error_text= NULL, STATUS = '3'
1873: CLOSE c_inst_present ;
1874: IF l_exists IS NULL THEN -- Error Has occured, as the institution code is not Present in the OSS
1875: --Log a message to the Log File in the Conc Manager that the INSTITUTION CODE in the cwlk table is incorrect
1876: IF gb_write_exception_log1 = TRUE THEN
1877: igs_or_inst_imp_001.log_writer(v_inst_rec.interface_id,'E001');
1878: END IF;
1879: -- Update Error_code/error_text
1880: UPDATE IGS_OR_INST_INT
1881: SET error_code = 'E001',error_text=NULL, STATUS = '3'
1889: IF l_Errind = 'Y' THEN
1890: ROLLBACK TO s_point;
1891:
1892: IF gb_write_exception_log1 = TRUE THEN
1893: igs_or_inst_imp_001.log_writer(v_inst_rec.interface_id,l_error_code,l_error_text);
1894: END IF;
1895: -- Set error_code/error_text
1896: UPDATE IGS_OR_INST_INT
1897: SET error_code = l_error_code , error_text= l_error_text,STATUS = '3'
1912: -- log the message that the data is not perfect, more than one crosswalk ids exists for the given
1913: -- alternater_id and alternater_id_value
1914: ROLLBACK TO s_point;
1915: IF gb_write_exception_log1 = TRUE THEN
1916: igs_or_inst_imp_001.log_writer(v_inst_rec.interface_id, 'E051');
1917: END IF;
1918: -- Set error_code/error_text
1919: UPDATE IGS_OR_INST_INT
1920: SET error_code = 'E051', error_text= 'crosswalk_id of crosswalk details table does not match with the master record', status = 3
1937: IF l_error_code <> 'E051' THEN
1938: ROLLBACK TO s_point;
1939: --Log a message to the Log File that the Update of Crosswalk Master failed
1940: IF gb_write_exception_log1 = TRUE THEN
1941: igs_or_inst_imp_001.log_writer(v_inst_rec.interface_id,'E040');
1942: END IF;
1943: --update error_code/error_text
1944: UPDATE IGS_OR_INST_INT
1945: SET error_code = 'E040',error_text=NULL, STATUS = '3'
1955: IF l_Errind = 'Y' THEN
1956: ROLLBACK TO s_point;
1957: --Log a message to the Log File that the Create of Crosswalk Detail failed
1958: IF gb_write_exception_log1 = TRUE THEN
1959: igs_or_inst_imp_001.log_writer(v_inst_rec.interface_id,'E007');
1960: END IF;
1961: --Update error_code/error_text
1962: UPDATE IGS_OR_INST_INT
1963: SET error_code = 'E007',error_text=NULL, STATUS = '3'
1968: IF l_Errind = 'Y' THEN
1969: ROLLBACK TO s_point;
1970: --Log a message to the Log File that the Create of Alternate Id failed
1971: IF gb_write_exception_log1 = TRUE THEN
1972: igs_or_inst_imp_001.log_writer(v_inst_rec.interface_id,l_error_code);
1973: END IF;
1974: --update error_code/error_text
1975: UPDATE IGS_OR_INST_INT
1976: SET error_code = l_error_code, error_text=NULL, STATUS = '3'
1996: END IF;
1997: ELSE
1998: --Log a message to the Log File that the Update of Crosswalk Master failed
1999: IF gb_write_exception_log1 = TRUE THEN
2000: igs_or_inst_imp_001.log_writer(v_inst_rec.interface_id,l_val_err);
2001: END IF;
2002: --Update Error_code field
2003: UPDATE IGS_OR_INST_INT
2004: SET error_code = l_val_err, error_text= NULL, STATUS = '3'
2013: ELSE -- l_count = 0
2014: IF l_error_code IS NULL THEN -- gmaheswa 3370808 Update interface record only when error code is null.
2015: --Log a message to the Log File in the Conc Manager that the record for numeric match is not found in the crosswalk dtl table
2016: IF gb_write_exception_log1 = TRUE THEN
2017: igs_or_inst_imp_001.log_writer(v_inst_rec.interface_id,'E043');
2018: END IF;
2019:
2020: -- Update Error_code/error_text
2021: UPDATE IGS_OR_INST_INT
2045: EXCEPTION
2046: WHEN OTHERS THEN
2047: IF gb_write_exception_log1 THEN
2048: FND_LOG.STRING_WITH_CONTEXT (FND_LOG.LEVEL_EXCEPTION,
2049: 'igs.plsql.igs_or_inst_imp_001.numericAltidcomp.others',
2050: SQLERRM, NULL,NULL,NULL,NULL,NULL, IGS_OR_INST_IMP_001.G_REQUEST_ID);
2051: END IF;
2052: APP_EXCEPTION.Raise_Exception;
2053: -- CLOSE c_inst_cur;
2046: WHEN OTHERS THEN
2047: IF gb_write_exception_log1 THEN
2048: FND_LOG.STRING_WITH_CONTEXT (FND_LOG.LEVEL_EXCEPTION,
2049: 'igs.plsql.igs_or_inst_imp_001.numericAltidcomp.others',
2050: SQLERRM, NULL,NULL,NULL,NULL,NULL, IGS_OR_INST_IMP_001.G_REQUEST_ID);
2051: END IF;
2052: APP_EXCEPTION.Raise_Exception;
2053: -- CLOSE c_inst_cur;
2054: END numericAltidcomp;
2605: EXCEPTION
2606: WHEN OTHERS THEN
2607: IF gb_write_exception_log1 THEN
2608: FND_LOG.STRING_WITH_CONTEXT (FND_LOG.LEVEL_EXCEPTION,
2609: 'igs.plsql.igs_or_inst_imp_001.delete_log_int_rec.others',
2610: SQLERRM, NULL,NULL,NULL,NULL,NULL, IGS_OR_INST_IMP_001.G_REQUEST_ID);
2611: END IF;
2612: APP_EXCEPTION.Raise_Exception;
2613: END delete_log_int_rec;
2606: WHEN OTHERS THEN
2607: IF gb_write_exception_log1 THEN
2608: FND_LOG.STRING_WITH_CONTEXT (FND_LOG.LEVEL_EXCEPTION,
2609: 'igs.plsql.igs_or_inst_imp_001.delete_log_int_rec.others',
2610: SQLERRM, NULL,NULL,NULL,NULL,NULL, IGS_OR_INST_IMP_001.G_REQUEST_ID);
2611: END IF;
2612: APP_EXCEPTION.Raise_Exception;
2613: END delete_log_int_rec;
2614:
2644: RETURN return_value;
2645:
2646: END validate_inst_code;
2647:
2648: END igs_or_inst_imp_001;