eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.IGS_OR_INST_IMP_001 dependencies on IGS_OR_INST_INT

Line 28: -- records in the interface tables. The IGS_OR_INST_INT table will also be updated be updated with

24: -- 3. Gather statistics done for IGS_OR_ADRUSGE_INT table
25: -- 4. New procedure validate_inst_code written to validate new and exst institution codes with
26: -- crosswalk institution code
27: -- 5. New procedure delete_log_int_rec written to take statistics, log and delete completed
28: -- records in the interface tables. The IGS_OR_INST_INT table will also be updated be updated with
29: -- status 4 if there are any discrepency in the child records
30: --mmkumar 19-JUL-2005 modified cursors c_inst_present, c_party_id to use igs_pe_hz_parties instead of using hz_parties
31: --gmaheswa 22-Jun-06 Bug 5189180: Modified logging logic to log warning records also.
32: -------------------------------------------------------------------------------------------

Line 36: p_error_code IN igs_or_inst_int.error_code%TYPE,

32: -------------------------------------------------------------------------------------------
33: g_records_processed NUMBER(5) := 0;
34:
35: PROCEDURE log_writer(p_which_rec IN varchar2,
36: p_error_code IN igs_or_inst_int.error_code%TYPE,
37: p_error_text igs_or_inst_int.error_text%TYPE) AS
38:
39: cursor c_error_log(p_err_cd igs_lookups_view.lookup_code%TYPE, cp_lookup_type igs_lookups_view.lookup_type%TYPE) is
40: select rpad(lookup_code,10)||meaning LINEX from igs_lookups_view where lookup_code = p_err_cd

Line 37: p_error_text igs_or_inst_int.error_text%TYPE) AS

33: g_records_processed NUMBER(5) := 0;
34:
35: PROCEDURE log_writer(p_which_rec IN varchar2,
36: p_error_code IN igs_or_inst_int.error_code%TYPE,
37: p_error_text igs_or_inst_int.error_text%TYPE) AS
38:
39: cursor c_error_log(p_err_cd igs_lookups_view.lookup_code%TYPE, cp_lookup_type igs_lookups_view.lookup_type%TYPE) is
40: select rpad(lookup_code,10)||meaning LINEX from igs_lookups_view where lookup_code = p_err_cd
41: and lookup_type = cp_lookup_type;

Line 61: FUNCTION validate_field_level_data(p_inst_rec IN IGS_OR_INST_INT%ROWTYPE,

57: END log_writer;
58:
59: -- Function to implement column lebel validation for interface table data.
60:
61: FUNCTION validate_field_level_data(p_inst_rec IN IGS_OR_INST_INT%ROWTYPE,
62: ret_err_cd OUT NOCOPY IGS_OR_INST_INT.ERROR_CODE%TYPE)
63: /*
64: || Created By : [email protected]
65: || Created On : 28-AUG-2000

Line 62: ret_err_cd OUT NOCOPY IGS_OR_INST_INT.ERROR_CODE%TYPE)

58:
59: -- Function to implement column lebel validation for interface table data.
60:
61: FUNCTION validate_field_level_data(p_inst_rec IN IGS_OR_INST_INT%ROWTYPE,
62: ret_err_cd OUT NOCOPY IGS_OR_INST_INT.ERROR_CODE%TYPE)
63: /*
64: || Created By : [email protected]
65: || Created On : 28-AUG-2000
66: || Purpose : Handles the LOCK mechanism for the table.

Line 90: cursor c_institution_type(cp_institution_type igs_or_inst_int.institution_type%TYPE,

86: enabled_flag = cp_enabled_flag;
87:
88: c_lookup_type_rec c_lookup_type%rowtype;
89:
90: cursor c_institution_type(cp_institution_type igs_or_inst_int.institution_type%TYPE,
91: cp_close_ind igs_or_org_inst_type.close_ind%TYPE) is
92: select 'X' from
93: igs_or_org_inst_type where --ssawhney, view to table
94: institution_type = cp_institution_type

Line 99: cursor c_institution_stat(cp_institution_stat igs_or_inst_int.institution_status%TYPE,

95: and close_ind = cp_close_ind;
96:
97: c_institution_type_rec c_institution_type%rowtype;
98:
99: cursor c_institution_stat(cp_institution_stat igs_or_inst_int.institution_status%TYPE,
100: cp_close_ind igs_or_inst_stat.closed_ind%TYPE) is select 'X' from
101: igs_or_inst_stat where
102: institution_status = cp_institution_stat
103: and closed_ind = cp_close_ind;

Line 107: cursor c_sec_school_loc_id(cp_sec_school_location_id IGS_OR_INST_INT.sec_school_location_id%TYPE,

103: and closed_ind = cp_close_ind;
104:
105: c_institution_stat_rec c_institution_stat%rowtype;
106:
107: cursor c_sec_school_loc_id(cp_sec_school_location_id IGS_OR_INST_INT.sec_school_location_id%TYPE,
108: cp_class igs_ad_code_classes.class%TYPE,
109: cp_closed_ind igs_ad_code_classes.closed_ind%TYPE) is
110: select 'X' from
111: igs_ad_code_classes acc --ssawhney, view to table

Line 261: -- 'IGS_OR_ADRUSGE_INT,IGS_OR_ADR_INT,IGS_OR_INST_CON_INT,IGS_OR_INST_CPHN_INT,IGS_OR_INST_INT,IGS_OR_INST_NTS_INT,IGS_OR_INST_SDTL_INT,

257: l_return := fnd_installation.get_app_info('IGS', l_status, l_industry, l_schema);
258:
259: IF l_schema IS NOT NULL THEN
260: -- gather statistics as the new INTERFACE batch program standard.
261: -- 'IGS_OR_ADRUSGE_INT,IGS_OR_ADR_INT,IGS_OR_INST_CON_INT,IGS_OR_INST_CPHN_INT,IGS_OR_INST_INT,IGS_OR_INST_NTS_INT,IGS_OR_INST_SDTL_INT,
262: -- IGS_OR_INST_STAT_INT,IGS_OR_CWLK_INT,IGS_OR_CWLK_DTL_INT,IGS_OR_INST_BTCH_INT';
263: -- IGS.L change, added gather statistics for table IGS_OR_ADR_INT
264:
265: FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,tabname => 'IGS_OR_ADR_INT',cascade => TRUE);

Line 269: FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,tabname => 'IGS_OR_INST_INT',cascade => TRUE);

265: FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,tabname => 'IGS_OR_ADR_INT',cascade => TRUE);
266: FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,tabname => 'IGS_OR_ADRUSGE_INT',cascade => TRUE);
267: FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,tabname => 'IGS_OR_INST_CON_INT',cascade => TRUE);
268: FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,tabname => 'IGS_OR_INST_CPHN_INT',cascade => TRUE);
269: FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,tabname => 'IGS_OR_INST_INT',cascade => TRUE);
270: FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,tabname => 'IGS_OR_INST_NTS_INT',cascade => TRUE);
271: FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,tabname => 'IGS_OR_INST_SDTL_INT',cascade => TRUE);
272: FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,tabname => 'IGS_OR_INST_STAT_INT',cascade => TRUE);
273: FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,tabname => 'IGS_OR_CWLK_INT',cascade => TRUE);

Line 342: cp_status IGS_OR_INST_INT.STATUS%TYPE) IS

338: which includes the existing institution cd and IGS_OR_INST_IMP_002.Create_Crosswalk_master.
339: ***************************************************************/
340: CURSOR c_inst_cur (cp_data_source VARCHAR2,
341: cp_batch_id NUMBER,
342: cp_status IGS_OR_INST_INT.STATUS%TYPE) IS
343: SELECT IO.*
344: FROM IGS_OR_INST_INT IO
345: WHERE IO.STATUS = cp_status AND
346: IO.DATA_SOURCE_ID = cp_data_source AND

Line 344: FROM IGS_OR_INST_INT IO

340: CURSOR c_inst_cur (cp_data_source VARCHAR2,
341: cp_batch_id NUMBER,
342: cp_status IGS_OR_INST_INT.STATUS%TYPE) IS
343: SELECT IO.*
344: FROM IGS_OR_INST_INT IO
345: WHERE IO.STATUS = cp_status AND
346: IO.DATA_SOURCE_ID = cp_data_source AND
347: IO.BATCH_ID = TO_NUMBER(cp_batch_id) ;
348:

Line 383: l_val_err igs_or_inst_int.error_code%TYPE;

379: l_Errind VARCHAR2(1);
380: l_Crswlkid igs_or_cwlk.crosswalk_id%TYPE;
381: l_party_id hz_parties.party_id%TYPE;
382: l_cwlkinst_rec c_inst_code%ROWTYPE;
383: l_val_err igs_or_inst_int.error_code%TYPE;
384: l_error_code igs_or_inst_int.error_code%TYPE := null; --ssawhney initialised
385: l_error_text igs_or_inst_int.error_text%TYPE := null; --ssawhney initialised
386: l_exists VARCHAR2(1);
387: --kumma

Line 384: l_error_code igs_or_inst_int.error_code%TYPE := null; --ssawhney initialised

380: l_Crswlkid igs_or_cwlk.crosswalk_id%TYPE;
381: l_party_id hz_parties.party_id%TYPE;
382: l_cwlkinst_rec c_inst_code%ROWTYPE;
383: l_val_err igs_or_inst_int.error_code%TYPE;
384: l_error_code igs_or_inst_int.error_code%TYPE := null; --ssawhney initialised
385: l_error_text igs_or_inst_int.error_text%TYPE := null; --ssawhney initialised
386: l_exists VARCHAR2(1);
387: --kumma
388: l_cwlk_master_present c_cwlk_master_present%ROWTYPE;

Line 385: l_error_text igs_or_inst_int.error_text%TYPE := null; --ssawhney initialised

381: l_party_id hz_parties.party_id%TYPE;
382: l_cwlkinst_rec c_inst_code%ROWTYPE;
383: l_val_err igs_or_inst_int.error_code%TYPE;
384: l_error_code igs_or_inst_int.error_code%TYPE := null; --ssawhney initialised
385: l_error_text igs_or_inst_int.error_text%TYPE := null; --ssawhney initialised
386: l_exists VARCHAR2(1);
387: --kumma
388: l_cwlk_master_present c_cwlk_master_present%ROWTYPE;
389:

Line 435: UPDATE IGS_OR_INST_INT

431: END IF;
432:
433: -- Update error_code for this record
434: -- Update Status of the Record to 3 to indicate Error
435: UPDATE IGS_OR_INST_INT
436: SET ERROR_CODE = 'E001',ERROR_TEXT = NULL, STATUS = '3'
437: WHERE INTERFACE_ID = v_inst_rec.interface_id;
438:
439:

Line 456: UPDATE IGS_OR_INST_INT

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'
458: WHERE INTERFACE_ID = v_inst_rec.interface_id;
459:
460: ELSE

Line 473: UPDATE IGS_OR_INST_INT

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,
475: status = '3'
476: WHERE interface_id = v_inst_rec.interface_id;
477: ELSE

Line 481: UPDATE IGS_OR_INST_INT

477: ELSE
478:
479: --Import of Institution is successful , import the Child
480: --Update error_code/error_text
481: UPDATE IGS_OR_INST_INT
482: SET error_code = NULL,error_text=NULL , STATUS = '1'
483: WHERE INTERFACE_ID = v_inst_rec.interface_id;
484:
485: -- Call Child Process

Line 503: UPDATE IGS_OR_INST_INT

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
504: SET error_code = l_val_err, error_text= NULL , STATUS = '3'
505: WHERE INTERFACE_ID = v_inst_rec.interface_id;
506:
507: END IF;

Line 526: UPDATE IGS_OR_INST_INT

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'
528: WHERE INTERFACE_ID = v_inst_rec.interface_id;
529:
530: ELSE

Line 544: UPDATE IGS_OR_INST_INT

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
545: SET error_code = 'E040', error_text= NULL , STATUS = '3'
546: WHERE INTERFACE_ID = v_inst_rec.interface_id;
547:
548: ELSE

Line 565: UPDATE IGS_OR_INST_INT

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
566: SET ERROR_CODE = l_error_code,
567: ERROR_TEXT =NULL,
568: STATUS = '3'
569: WHERE INTERFACE_ID = v_inst_rec.interface_id;

Line 574: UPDATE IGS_OR_INST_INT

570: ELSE
571:
572: -- Import of Institution is successful , import the Child
573: --Update error_code/error_text
574: UPDATE IGS_OR_INST_INT
575: SET error_code = NULL,error_text=NULL, STATUS = '1'
576: WHERE INTERFACE_ID = v_inst_rec.interface_id;
577:
578: -- Call Child Process

Line 598: UPDATE IGS_OR_INST_INT

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
599: SET error_code = l_val_err, error_text= NULL, STATUS = '3'
600: WHERE INTERFACE_ID = v_inst_rec.interface_id;
601:
602: END IF;

Line 618: UPDATE IGS_OR_INST_INT

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
619: SET ERROR_CODE = 'E006',error_text = NULL, STATUS = '3'
620: WHERE INTERFACE_ID = v_inst_rec.interface_id;
621:
622: ELSE -- Institution is existing in the OSS system

Line 636: UPDATE IGS_OR_INST_INT

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'
638: WHERE INTERFACE_ID = v_inst_rec.interface_id;
639:
640: ELSE

Line 658: UPDATE IGS_OR_INST_INT

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
659: SET error_code = 'E051', error_text= 'crosswalk_id of crosswalk details table does not match with the master record', status = 3
660: WHERE INTERFACE_ID = v_inst_rec.interface_id;
661: l_Errind := 'Y';
662: l_error_code := 'E051';

Line 685: UPDATE IGS_OR_INST_INT

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
686: SET ERROR_CODE = 'E040', ERROR_TEXT=NULL, STATUS = '3'
687: WHERE INTERFACE_ID = v_inst_rec.interface_id;
688:
689: END IF;

Line 703: UPDATE IGS_OR_INST_INT

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
704: SET ERROR_CODE = l_error_code,
705: ERROR_TEXT =NULL,
706: STATUS = '3'
707: WHERE INTERFACE_ID = v_inst_rec.interface_id;

Line 711: UPDATE IGS_OR_INST_INT

707: WHERE INTERFACE_ID = v_inst_rec.interface_id;
708: ELSE
709: -- Import of Institution is successful , import the Child
710: --Update error_code/error_text
711: UPDATE IGS_OR_INST_INT
712: SET error_code = NULL,error_text=NULL, STATUS = '1'
713: WHERE INTERFACE_ID = v_inst_rec.interface_id;
714:
715: -- Call Child Process

Line 733: UPDATE IGS_OR_INST_INT

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
734: SET error_code = l_val_err, error_text= NULL, STATUS = '3'
735: WHERE INTERFACE_ID = v_inst_rec.interface_id;
736:
737: END IF;

Line 755: UPDATE IGS_OR_INST_INT

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'
757: WHERE INTERFACE_ID = v_inst_rec.interface_id;
758:
759: ELSE

Line 785: UPDATE IGS_OR_INST_INT

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'
787: WHERE INTERFACE_ID = v_inst_rec.interface_id;
788:
789: ELSE

Line 799: UPDATE IGS_OR_INST_INT

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'
801: WHERE INTERFACE_ID = v_inst_rec.interface_id;
802:
803: ELSE

Line 813: UPDATE IGS_OR_INST_INT

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,
815: error_text=NULL ,
816: status = '3'
817: WHERE interface_id = v_inst_rec.interface_id;

Line 821: UPDATE IGS_OR_INST_INT

817: WHERE interface_id = v_inst_rec.interface_id;
818:
819: ELSE
820: -- Import of Institution is successful , import the Child
821: UPDATE IGS_OR_INST_INT
822: SET error_code = NULL,error_text=NULL, STATUS = '1'
823: WHERE INTERFACE_ID = v_inst_rec.interface_id;
824: --Update status to show success
825:

Line 846: UPDATE IGS_OR_INST_INT

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
847: SET error_code = l_val_err, error_text= NULL , STATUS = '3'
848: WHERE INTERFACE_ID = v_inst_rec.interface_id;
849: END IF;
850:

Line 863: UPDATE IGS_OR_INST_INT

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'
865: WHERE INTERFACE_ID = v_inst_rec.interface_id;
866:
867: ELSE -- Institution is existing in the OSS system

Line 880: UPDATE IGS_OR_INST_INT

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'
882: WHERE INTERFACE_ID = v_inst_rec.interface_id;
883: ELSE
884: -- kumma, 2446067

Line 905: UPDATE IGS_OR_INST_INT

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'
907: WHERE INTERFACE_ID = v_inst_rec.interface_id;
908:
909: ELSE

Line 919: UPDATE IGS_OR_INST_INT

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'
921: WHERE INTERFACE_ID = v_inst_rec.interface_id;
922:
923: ELSE

Line 935: UPDATE IGS_OR_INST_INT

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,
937: error_text = NULL,
938: status = '3'
939: WHERE INTERFACE_ID = v_inst_rec.interface_id;

Line 943: UPDATE IGS_OR_INST_INT

939: WHERE INTERFACE_ID = v_inst_rec.interface_id;
940: ELSE
941: -- Import of Institution is successful , import the Child
942: --Update error_code/error_text -- ssawhney moved all together
943: UPDATE IGS_OR_INST_INT
944: SET error_code = NULL,error_text=NULL, STATUS = '1'
945: WHERE INTERFACE_ID = v_inst_rec.interface_id;
946:
947: -- Child Process

Line 966: UPDATE IGS_OR_INST_INT

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'
968: WHERE INTERFACE_ID = v_inst_rec.interface_id;
969:
970: END IF;

Line 1018: CURSOR c_inst_cur(cp_status IGS_OR_INST_INT.STATUS%TYPE,

1014: Used these cursor before every call to IGS_OR_INST_IMP_002.Update_Crosswalk_master
1015: which includes the existing institution cd and IGS_OR_INST_IMP_002.Create_Crosswalk_master.
1016: ***************************************************************/
1017:
1018: CURSOR c_inst_cur(cp_status IGS_OR_INST_INT.STATUS%TYPE,
1019: cp_data_source VARCHAR2,
1020: cp_ds_match VARCHAR2,
1021: cp_batch_id NUMBER) IS
1022: SELECT *

Line 1023: FROM IGS_OR_INST_INT IO

1019: cp_data_source VARCHAR2,
1020: cp_ds_match VARCHAR2,
1021: cp_batch_id NUMBER) IS
1022: SELECT *
1023: FROM IGS_OR_INST_INT IO
1024: WHERE IO.STATUS = cp_status AND
1025: IO.DATA_SOURCE_ID = cp_data_source AND
1026: cp_ds_match = NVL(IO.ALT_ID_TYPE,cp_ds_match) AND
1027: IO.BATCH_ID = cp_batch_id ;

Line 1067: l_val_err igs_or_inst_int.error_code%TYPE;

1063: l_Newinstcd igs_or_institution.institution_cd%TYPE;
1064: l_Errind VARCHAR2(1);
1065: l_party_id hz_parties.party_id%TYPE;
1066: l_cwlkinst_rec c_inst_code%ROWTYPE;
1067: l_val_err igs_or_inst_int.error_code%TYPE;
1068: l_error_code igs_or_inst_int.error_code%TYPE := null; --ssawhney initialised
1069: l_error_text igs_or_inst_int.error_text%TYPE := null; --ssawhney initialised
1070: l_exists VARCHAR2(1);
1071: l_cwlk_master_present c_cwlk_master_present%ROWTYPE;

Line 1068: l_error_code igs_or_inst_int.error_code%TYPE := null; --ssawhney initialised

1064: l_Errind VARCHAR2(1);
1065: l_party_id hz_parties.party_id%TYPE;
1066: l_cwlkinst_rec c_inst_code%ROWTYPE;
1067: l_val_err igs_or_inst_int.error_code%TYPE;
1068: l_error_code igs_or_inst_int.error_code%TYPE := null; --ssawhney initialised
1069: l_error_text igs_or_inst_int.error_text%TYPE := null; --ssawhney initialised
1070: l_exists VARCHAR2(1);
1071: l_cwlk_master_present c_cwlk_master_present%ROWTYPE;
1072:

Line 1069: l_error_text igs_or_inst_int.error_text%TYPE := null; --ssawhney initialised

1065: l_party_id hz_parties.party_id%TYPE;
1066: l_cwlkinst_rec c_inst_code%ROWTYPE;
1067: l_val_err igs_or_inst_int.error_code%TYPE;
1068: l_error_code igs_or_inst_int.error_code%TYPE := null; --ssawhney initialised
1069: l_error_text igs_or_inst_int.error_text%TYPE := null; --ssawhney initialised
1070: l_exists VARCHAR2(1);
1071: l_cwlk_master_present c_cwlk_master_present%ROWTYPE;
1072:
1073:

Line 1121: UPDATE IGS_OR_INST_INT

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'
1123: WHERE INTERFACE_ID = v_inst_rec.interface_id;
1124:
1125:

Line 1146: UPDATE IGS_OR_INST_INT

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
1147: SET error_code = l_error_code, error_text= l_error_text, STATUS = '3'
1148: WHERE INTERFACE_ID = v_inst_rec.interface_id;
1149:
1150:

Line 1161: UPDATE IGS_OR_INST_INT

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'
1163: WHERE INTERFACE_ID = v_inst_rec.interface_id;
1164:
1165: ELSE

Line 1174: UPDATE IGS_OR_INST_INT

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'
1176: WHERE INTERFACE_ID = v_inst_rec.interface_id;
1177:
1178: ELSE

Line 1181: UPDATE IGS_OR_INST_INT

1177:
1178: ELSE
1179: -- Import of Institution is successful , import the Child
1180: --Update error_code/error_text -- ssawhney moved all together
1181: UPDATE IGS_OR_INST_INT
1182: SET error_code = NULL,error_text=NULL, STATUS = '1'
1183: WHERE INTERFACE_ID = v_inst_rec.interface_id;
1184:
1185: -- Call Child Process

Line 1203: UPDATE IGS_OR_INST_INT

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'
1205: WHERE INTERFACE_ID = v_inst_rec.interface_id;
1206: --update status field
1207:

Line 1225: UPDATE IGS_OR_INST_INT

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'
1227: WHERE INTERFACE_ID = v_inst_rec.interface_id;
1228:
1229:

Line 1242: UPDATE IGS_OR_INST_INT

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'
1244: WHERE INTERFACE_ID = v_inst_rec.interface_id;
1245:
1246: ELSE

Line 1258: UPDATE IGS_OR_INST_INT

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'
1260: WHERE INTERFACE_ID = v_inst_rec.interface_id;
1261:
1262: ELSE

Line 1271: UPDATE IGS_OR_INST_INT

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'
1273: WHERE INTERFACE_ID = v_inst_rec.interface_id;
1274: ELSE
1275: -- Import of Institution is successful , import the Child

Line 1277: UPDATE IGS_OR_INST_INT

1273: WHERE INTERFACE_ID = v_inst_rec.interface_id;
1274: ELSE
1275: -- Import of Institution is successful , import the Child
1276: --Update error_code/error_text -- ssawhney moved all together
1277: UPDATE IGS_OR_INST_INT
1278: SET error_code = NULL,error_text=NULL, STATUS = '1'
1279: WHERE INTERFACE_ID = v_inst_rec.interface_id;
1280: --update status to show success
1281:

Line 1301: UPDATE IGS_OR_INST_INT

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'
1303: WHERE INTERFACE_ID = v_inst_rec.interface_id;
1304:
1305: END IF;

Line 1317: UPDATE IGS_OR_INST_INT

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'
1319: WHERE INTERFACE_ID = v_inst_rec.interface_id;
1320: -- Update Status of the Record to 3 to indicate Error
1321:

Line 1334: UPDATE IGS_OR_INST_INT

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
1335: SET error_code = l_error_code, error_text= l_error_text, STATUS = '3'
1336: WHERE INTERFACE_ID = v_inst_rec.interface_id;
1337:
1338:

Line 1355: UPDATE IGS_OR_INST_INT

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
1357: WHERE INTERFACE_ID = v_inst_rec.interface_id;
1358: l_Errind := 'Y';
1359: l_error_code := 'E051';

Line 1378: UPDATE IGS_OR_INST_INT

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'
1380: WHERE INTERFACE_ID = v_inst_rec.interface_id;
1381:
1382: END IF;

Line 1396: UPDATE IGS_OR_INST_INT

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
1397: SET error_code = 'E007', error_text=NULL, STATUS = '3'
1398: WHERE INTERFACE_ID = v_inst_rec.interface_id;
1399:
1400: ELSE

Line 1409: UPDATE IGS_OR_INST_INT

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'
1411: WHERE INTERFACE_ID = v_inst_rec.interface_id;
1412: ELSE
1413: -- Import of Institution is successful , import the Child

Line 1415: UPDATE IGS_OR_INST_INT

1411: WHERE INTERFACE_ID = v_inst_rec.interface_id;
1412: ELSE
1413: -- Import of Institution is successful , import the Child
1414: --Update error_code/error_text
1415: UPDATE IGS_OR_INST_INT
1416: SET error_code = NULL,error_text=NULL, STATUS = '1'
1417: WHERE INTERFACE_ID = v_inst_rec.interface_id;
1418:
1419: -- Call Child Process

Line 1438: UPDATE IGS_OR_INST_INT

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'
1440: WHERE INTERFACE_ID = v_inst_rec.interface_id;
1441:
1442: END IF;

Line 1454: UPDATE IGS_OR_INST_INT

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
1455: SET error_code = 'E041', error_text=NULL, STATUS = '3'
1456: WHERE INTERFACE_ID = v_inst_rec.interface_id;
1457:
1458: END IF;

Line 1467: UPDATE IGS_OR_INST_INT

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
1468: SET error_code = 'E042', error_text=NULL, STATUS = '3'
1469: WHERE INTERFACE_ID = v_inst_rec.interface_id;
1470:
1471: END IF;

Line 1522: CURSOR c_inst_cur(cp_status IGS_OR_INST_INT.STATUS%TYPE,

1518: In cursor c_record_found and c_inst_code removed the to_number function.
1519: gmaheswa 24 March 2006 Bug 3370808 Update interface record with E043 only when error code is null.
1520: ***************************************************************/
1521:
1522: CURSOR c_inst_cur(cp_status IGS_OR_INST_INT.STATUS%TYPE,
1523: cp_data_source VARCHAR2,
1524: cp_ds_match VARCHAR2,
1525: cp_batch_id VARCHAR2) IS
1526: SELECT *

Line 1527: FROM IGS_OR_INST_INT IO

1523: cp_data_source VARCHAR2,
1524: cp_ds_match VARCHAR2,
1525: cp_batch_id VARCHAR2) IS
1526: SELECT *
1527: FROM IGS_OR_INST_INT IO
1528: WHERE IO.STATUS = cp_status AND
1529: IO.DATA_SOURCE_ID = cp_data_source AND
1530: cp_ds_match = NVL(IO.ALT_ID_TYPE,cp_ds_match) AND
1531: IO.BATCH_ID = cp_batch_id;

Line 1580: l_val_err igs_or_inst_int.error_code%TYPE;

1576: l_Errind VARCHAR2(1);
1577: l_party_id NUMBER(15);
1578:
1579: l_cwlkinst_rec c_inst_code%ROWTYPE;
1580: l_val_err igs_or_inst_int.error_code%TYPE;
1581: l_error_code igs_or_inst_int.error_code%TYPE := null; --ssawhney initialised
1582: l_error_text igs_or_inst_int.error_text%TYPE := null; --ssawhney initialised
1583: l_exists VARCHAR2(1);
1584: --kumma

Line 1581: l_error_code igs_or_inst_int.error_code%TYPE := null; --ssawhney initialised

1577: l_party_id NUMBER(15);
1578:
1579: l_cwlkinst_rec c_inst_code%ROWTYPE;
1580: l_val_err igs_or_inst_int.error_code%TYPE;
1581: l_error_code igs_or_inst_int.error_code%TYPE := null; --ssawhney initialised
1582: l_error_text igs_or_inst_int.error_text%TYPE := null; --ssawhney initialised
1583: l_exists VARCHAR2(1);
1584: --kumma
1585: l_cwlk_master_present c_cwlk_master_present%ROWTYPE;

Line 1582: l_error_text igs_or_inst_int.error_text%TYPE := null; --ssawhney initialised

1578:
1579: l_cwlkinst_rec c_inst_code%ROWTYPE;
1580: l_val_err igs_or_inst_int.error_code%TYPE;
1581: l_error_code igs_or_inst_int.error_code%TYPE := null; --ssawhney initialised
1582: l_error_text igs_or_inst_int.error_text%TYPE := null; --ssawhney initialised
1583: l_exists VARCHAR2(1);
1584: --kumma
1585: l_cwlk_master_present c_cwlk_master_present%ROWTYPE;
1586: v_inst_record c_inst_cur%ROWTYPE;

Line 1631: UPDATE IGS_OR_INST_INT

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
1633: WHERE INTERFACE_ID = v_inst_rec.interface_id;
1634: l_Errind := 'Y';
1635: l_error_code := 'E051';

Line 1654: UPDATE IGS_OR_INST_INT

1650: IF c_neumeric_test%ISOPEN THEN
1651: CLOSE c_neumeric_test;
1652: END IF;
1653:
1654: UPDATE IGS_OR_INST_INT
1655: SET error_code = 'E052', error_text=NULL, STATUS =3
1656: WHERE INTERFACE_ID = v_inst_record.interface_id;
1657:
1658: RAISE INVALID_NUMBER;

Line 1692: UPDATE IGS_OR_INST_INT

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'
1694: WHERE INTERFACE_ID = v_inst_rec.interface_id;
1695: -- Update Status of the Record to 3 to indicate Error
1696:

Line 1710: UPDATE IGS_OR_INST_INT

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'
1712: WHERE INTERFACE_ID = v_inst_rec.interface_id;
1713:
1714: ELSE

Line 1728: UPDATE IGS_OR_INST_INT

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'
1730: WHERE INTERFACE_ID = v_inst_rec.interface_id;
1731:
1732: ELSE

Line 1741: UPDATE IGS_OR_INST_INT

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'
1743: WHERE INTERFACE_ID = v_inst_rec.interface_id;
1744: ELSE
1745: -- Import of Institution is successful , import the Child

Line 1747: UPDATE IGS_OR_INST_INT

1743: WHERE INTERFACE_ID = v_inst_rec.interface_id;
1744: ELSE
1745: -- Import of Institution is successful , import the Child
1746: --Update error_code/error_text -- ssawhney moved all together
1747: UPDATE IGS_OR_INST_INT
1748: SET error_code = NULL,error_text=NULL, STATUS = '1'
1749: WHERE INTERFACE_ID = v_inst_rec.interface_id;
1750:
1751: -- Call the Child Process

Line 1768: UPDATE IGS_OR_INST_INT

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'
1770: WHERE INTERFACE_ID = v_inst_rec.interface_id;
1771:
1772: END IF;

Line 1789: UPDATE IGS_OR_INST_INT

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'
1791: WHERE INTERFACE_ID = v_inst_rec.interface_id;
1792:
1793: ELSE

Line 1804: UPDATE IGS_OR_INST_INT

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'
1806: WHERE INTERFACE_ID = v_inst_rec.interface_id;
1807:
1808: ELSE

Line 1822: UPDATE IGS_OR_INST_INT

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'
1824: WHERE INTERFACE_ID = v_inst_rec.interface_id;
1825:
1826: ELSE

Line 1835: UPDATE IGS_OR_INST_INT

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'
1837: WHERE INTERFACE_ID = v_inst_rec.interface_id;
1838: ELSE
1839: -- Import of Institution is successful , import the Child

Line 1841: UPDATE IGS_OR_INST_INT

1837: WHERE INTERFACE_ID = v_inst_rec.interface_id;
1838: ELSE
1839: -- Import of Institution is successful , import the Child
1840: --Update error_code/error_text -- ssawhney moved all together
1841: UPDATE IGS_OR_INST_INT
1842: SET error_code = NULL,error_text=NULL, STATUS = '1'
1843: WHERE INTERFACE_ID = v_inst_rec.interface_id;
1844:
1845: -- Call the Child Process

Line 1863: UPDATE IGS_OR_INST_INT

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'
1865: WHERE INTERFACE_ID = v_inst_rec.interface_id;
1866:
1867: END IF;

Line 1880: UPDATE IGS_OR_INST_INT

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'
1882: WHERE INTERFACE_ID = v_inst_rec.interface_id;
1883:
1884: ELSE -- Institution is existing in the OSS system

Line 1896: UPDATE IGS_OR_INST_INT

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'
1898: WHERE INTERFACE_ID = v_inst_rec.interface_id;
1899:
1900: ELSE

Line 1919: UPDATE IGS_OR_INST_INT

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
1921: WHERE INTERFACE_ID = v_inst_rec.interface_id;
1922: l_Errind := 'Y';
1923: l_error_code := 'E051';

Line 1944: UPDATE IGS_OR_INST_INT

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'
1946: WHERE INTERFACE_ID = v_inst_rec.interface_id;
1947:
1948: END IF;

Line 1962: UPDATE IGS_OR_INST_INT

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'
1964: WHERE INTERFACE_ID = v_inst_rec.interface_id;
1965:
1966: ELSE

Line 1975: UPDATE IGS_OR_INST_INT

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'
1977: WHERE INTERFACE_ID = v_inst_rec.interface_id;
1978: ELSE
1979: -- Import of Institution is successful , import the Child

Line 1981: UPDATE IGS_OR_INST_INT

1977: WHERE INTERFACE_ID = v_inst_rec.interface_id;
1978: ELSE
1979: -- Import of Institution is successful , import the Child
1980: --Update error_code/error_text
1981: UPDATE IGS_OR_INST_INT
1982: SET error_code = NULL,error_text=NULL, STATUS = '1'
1983: WHERE INTERFACE_ID = v_inst_rec.interface_id;
1984:
1985: -- Child Process

Line 2003: UPDATE IGS_OR_INST_INT

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'
2005: WHERE INTERFACE_ID = v_inst_rec.interface_id;
2006:
2007: END IF;

Line 2021: UPDATE IGS_OR_INST_INT

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
2022: SET error_code = 'E043', error_text=NULL, STATUS = '3'
2023: WHERE INTERFACE_ID = v_inst_rec.interface_id;
2024: END IF; --End l_error_code IS NULL
2025:

Line 2056: PROCEDURE delete_log_int_rec(p_batch_id IN IGS_OR_INST_INT.BATCH_ID%TYPE) AS

2052: APP_EXCEPTION.Raise_Exception;
2053: -- CLOSE c_inst_cur;
2054: END numericAltidcomp;
2055:
2056: PROCEDURE delete_log_int_rec(p_batch_id IN IGS_OR_INST_INT.BATCH_ID%TYPE) AS
2057: /*************************************************************
2058: Created By :ssaleem
2059: Date Created By : 19-SEP-2003
2060: Purpose : This procedure deletes all the completed records

Line 2067: * If IGS_OR_INST_INT has more than one error in one record,

2063: statistics of the operations and logs them.
2064: Know limitations, enhancements or remarks
2065:
2066: Remarks:
2067: * If IGS_OR_INST_INT has more than one error in one record,
2068: say for eg one record having both erroneous contact phone
2069: and erroneous statistics details, the record in
2070: IGS_OR_INST_INT will be updated with status 4 - Warning
2071: and with any one of the error code that is first processed,

Line 2070: IGS_OR_INST_INT will be updated with status 4 - Warning

2066: Remarks:
2067: * If IGS_OR_INST_INT has more than one error in one record,
2068: say for eg one record having both erroneous contact phone
2069: and erroneous statistics details, the record in
2070: IGS_OR_INST_INT will be updated with status 4 - Warning
2071: and with any one of the error code that is first processed,
2072: In the above case it will be E055.
2073:
2074: Change History

Line 2096: -- Cursor for taking logging statistics, done after updating the status of IGS_OR_INST_INT

2092: l_inst_addr_meaning IGS_LOOKUP_VALUES.MEANING%TYPE;
2093: l_inst_addr_usage_meaning IGS_LOOKUP_VALUES.MEANING%TYPE;
2094: l_inst_rec_err_meaning IGS_LOOKUP_VALUES.MEANING%TYPE;
2095:
2096: -- Cursor for taking logging statistics, done after updating the status of IGS_OR_INST_INT
2097: CURSOR log_inst_err_cur (cp_batch_id IGS_OR_INST_INT.BATCH_ID%TYPE,cp_status_error IGS_OR_INST_INT.STATUS%TYPE,cp_status_warn IGS_OR_INST_INT.STATUS%TYPE) IS
2098: SELECT RPAD(INTERFACE_ID,12) || ' ' || LPAD(STATUS,7) || ' ' || ERROR_CODE EREC
2099: FROM IGS_OR_INST_INT
2100: WHERE BATCH_ID = cp_batch_id AND

Line 2097: CURSOR log_inst_err_cur (cp_batch_id IGS_OR_INST_INT.BATCH_ID%TYPE,cp_status_error IGS_OR_INST_INT.STATUS%TYPE,cp_status_warn IGS_OR_INST_INT.STATUS%TYPE) IS

2093: l_inst_addr_usage_meaning IGS_LOOKUP_VALUES.MEANING%TYPE;
2094: l_inst_rec_err_meaning IGS_LOOKUP_VALUES.MEANING%TYPE;
2095:
2096: -- Cursor for taking logging statistics, done after updating the status of IGS_OR_INST_INT
2097: CURSOR log_inst_err_cur (cp_batch_id IGS_OR_INST_INT.BATCH_ID%TYPE,cp_status_error IGS_OR_INST_INT.STATUS%TYPE,cp_status_warn IGS_OR_INST_INT.STATUS%TYPE) IS
2098: SELECT RPAD(INTERFACE_ID,12) || ' ' || LPAD(STATUS,7) || ' ' || ERROR_CODE EREC
2099: FROM IGS_OR_INST_INT
2100: WHERE BATCH_ID = cp_batch_id AND
2101: (STATUS = cp_status_error OR STATUS = cp_status_warn);

Line 2099: FROM IGS_OR_INST_INT

2095:
2096: -- Cursor for taking logging statistics, done after updating the status of IGS_OR_INST_INT
2097: CURSOR log_inst_err_cur (cp_batch_id IGS_OR_INST_INT.BATCH_ID%TYPE,cp_status_error IGS_OR_INST_INT.STATUS%TYPE,cp_status_warn IGS_OR_INST_INT.STATUS%TYPE) IS
2098: SELECT RPAD(INTERFACE_ID,12) || ' ' || LPAD(STATUS,7) || ' ' || ERROR_CODE EREC
2099: FROM IGS_OR_INST_INT
2100: WHERE BATCH_ID = cp_batch_id AND
2101: (STATUS = cp_status_error OR STATUS = cp_status_warn);
2102:
2103: l_inst_err_rec log_inst_err_cur%ROWTYPE;

Line 2106: CURSOR log_inst_rcount_cur(cp_batch_id IGS_OR_INST_INT.BATCH_ID%TYPE) IS

2102:
2103: l_inst_err_rec log_inst_err_cur%ROWTYPE;
2104:
2105: -- Cursor for taking statistics, done before deleting completed records
2106: CURSOR log_inst_rcount_cur(cp_batch_id IGS_OR_INST_INT.BATCH_ID%TYPE) IS
2107: SELECT COUNT(1) CNT,STATUS STAT
2108: FROM IGS_OR_INST_INT
2109: WHERE BATCH_ID = cp_batch_id
2110: GROUP BY STATUS;

Line 2108: FROM IGS_OR_INST_INT

2104:
2105: -- Cursor for taking statistics, done before deleting completed records
2106: CURSOR log_inst_rcount_cur(cp_batch_id IGS_OR_INST_INT.BATCH_ID%TYPE) IS
2107: SELECT COUNT(1) CNT,STATUS STAT
2108: FROM IGS_OR_INST_INT
2109: WHERE BATCH_ID = cp_batch_id
2110: GROUP BY STATUS;
2111:
2112: l_inst_rec log_inst_rcount_cur%ROWTYPE;

Line 2118: CURSOR log_inst_note_rcount_cur(cp_batch_id IGS_OR_INST_INT.BATCH_ID%TYPE) IS

2114: l_comp_inst NUMBER(6);
2115: l_err_inst NUMBER(6);
2116: l_warn_inst NUMBER(6);
2117:
2118: CURSOR log_inst_note_rcount_cur(cp_batch_id IGS_OR_INST_INT.BATCH_ID%TYPE) IS
2119: SELECT COUNT(1) CNT,NT.STATUS STAT
2120: FROM IGS_OR_INST_INT INST,
2121: IGS_OR_INST_NTS_INT NT
2122: WHERE INST.BATCH_ID = cp_batch_id AND

Line 2120: FROM IGS_OR_INST_INT INST,

2116: l_warn_inst NUMBER(6);
2117:
2118: CURSOR log_inst_note_rcount_cur(cp_batch_id IGS_OR_INST_INT.BATCH_ID%TYPE) IS
2119: SELECT COUNT(1) CNT,NT.STATUS STAT
2120: FROM IGS_OR_INST_INT INST,
2121: IGS_OR_INST_NTS_INT NT
2122: WHERE INST.BATCH_ID = cp_batch_id AND
2123: INST.INTERFACE_ID = NT.INTERFACE_ID
2124: GROUP BY NT.STATUS;

Line 2132: CURSOR log_inst_stat_rcount_cur(cp_batch_id IGS_OR_INST_INT.BATCH_ID%TYPE) IS

2128: l_comp_inst_note NUMBER(6);
2129: l_err_inst_note NUMBER(6);
2130: l_warn_inst_note NUMBER(6);
2131:
2132: CURSOR log_inst_stat_rcount_cur(cp_batch_id IGS_OR_INST_INT.BATCH_ID%TYPE) IS
2133: SELECT COUNT(1) CNT,STAT.STATUS STAT
2134: FROM IGS_OR_INST_INT INST,
2135: IGS_OR_INST_STAT_INT STAT
2136: WHERE INST.BATCH_ID = cp_batch_id AND

Line 2134: FROM IGS_OR_INST_INT INST,

2130: l_warn_inst_note NUMBER(6);
2131:
2132: CURSOR log_inst_stat_rcount_cur(cp_batch_id IGS_OR_INST_INT.BATCH_ID%TYPE) IS
2133: SELECT COUNT(1) CNT,STAT.STATUS STAT
2134: FROM IGS_OR_INST_INT INST,
2135: IGS_OR_INST_STAT_INT STAT
2136: WHERE INST.BATCH_ID = cp_batch_id AND
2137: INST.INTERFACE_ID = STAT.INTERFACE_ID
2138: GROUP BY STAT.STATUS;

Line 2146: CURSOR log_inst_sdtl_rcount_cur(cp_batch_id IGS_OR_INST_INT.BATCH_ID%TYPE) IS

2142: l_comp_inst_stat NUMBER(6);
2143: l_err_inst_stat NUMBER(6);
2144: l_warn_inst_stat NUMBER(6);
2145:
2146: CURSOR log_inst_sdtl_rcount_cur(cp_batch_id IGS_OR_INST_INT.BATCH_ID%TYPE) IS
2147: SELECT COUNT(1) CNT,SDTL.STATUS STAT
2148: FROM IGS_OR_INST_INT INST,
2149: IGS_OR_INST_STAT_INT STAT,
2150: IGS_OR_INST_SDTL_INT SDTL

Line 2148: FROM IGS_OR_INST_INT INST,

2144: l_warn_inst_stat NUMBER(6);
2145:
2146: CURSOR log_inst_sdtl_rcount_cur(cp_batch_id IGS_OR_INST_INT.BATCH_ID%TYPE) IS
2147: SELECT COUNT(1) CNT,SDTL.STATUS STAT
2148: FROM IGS_OR_INST_INT INST,
2149: IGS_OR_INST_STAT_INT STAT,
2150: IGS_OR_INST_SDTL_INT SDTL
2151: WHERE INST.BATCH_ID = cp_batch_id AND
2152: INST.INTERFACE_ID = STAT.INTERFACE_ID AND

Line 2162: CURSOR log_inst_con_rcount_cur(cp_batch_id IGS_OR_INST_INT.BATCH_ID%TYPE) IS

2158: l_comp_inst_sdtl NUMBER(6);
2159: l_err_inst_sdtl NUMBER(6);
2160: l_warn_inst_sdtl NUMBER(6);
2161:
2162: CURSOR log_inst_con_rcount_cur(cp_batch_id IGS_OR_INST_INT.BATCH_ID%TYPE) IS
2163: SELECT COUNT(1) CNT,CON.STATUS STAT
2164: FROM IGS_OR_INST_INT INST,
2165: IGS_OR_INST_CON_INT CON
2166: WHERE INST.BATCH_ID = cp_batch_id AND

Line 2164: FROM IGS_OR_INST_INT INST,

2160: l_warn_inst_sdtl NUMBER(6);
2161:
2162: CURSOR log_inst_con_rcount_cur(cp_batch_id IGS_OR_INST_INT.BATCH_ID%TYPE) IS
2163: SELECT COUNT(1) CNT,CON.STATUS STAT
2164: FROM IGS_OR_INST_INT INST,
2165: IGS_OR_INST_CON_INT CON
2166: WHERE INST.BATCH_ID = cp_batch_id AND
2167: INST.INTERFACE_ID = CON.INTERFACE_ID
2168: GROUP BY CON.STATUS;

Line 2176: CURSOR log_inst_cphn_rcount_cur(cp_batch_id IGS_OR_INST_INT.BATCH_ID%TYPE) IS

2172: l_comp_inst_con NUMBER(6);
2173: l_err_inst_con NUMBER(6);
2174: l_warn_inst_con NUMBER(6);
2175:
2176: CURSOR log_inst_cphn_rcount_cur(cp_batch_id IGS_OR_INST_INT.BATCH_ID%TYPE) IS
2177: SELECT COUNT(1) CNT,CPHN.STATUS STAT
2178: FROM IGS_OR_INST_INT INST,
2179: IGS_OR_INST_CON_INT CON,
2180: IGS_OR_INST_CPHN_INT CPHN

Line 2178: FROM IGS_OR_INST_INT INST,

2174: l_warn_inst_con NUMBER(6);
2175:
2176: CURSOR log_inst_cphn_rcount_cur(cp_batch_id IGS_OR_INST_INT.BATCH_ID%TYPE) IS
2177: SELECT COUNT(1) CNT,CPHN.STATUS STAT
2178: FROM IGS_OR_INST_INT INST,
2179: IGS_OR_INST_CON_INT CON,
2180: IGS_OR_INST_CPHN_INT CPHN
2181: WHERE INST.BATCH_ID = cp_batch_id AND
2182: INST.INTERFACE_ID = CON.INTERFACE_ID AND

Line 2192: CURSOR log_inst_adr_rcount_cur(cp_batch_id IGS_OR_INST_INT.BATCH_ID%TYPE) IS

2188: l_comp_inst_cphn NUMBER(6);
2189: l_err_inst_cphn NUMBER(6);
2190: l_warn_inst_cphn NUMBER(6);
2191:
2192: CURSOR log_inst_adr_rcount_cur(cp_batch_id IGS_OR_INST_INT.BATCH_ID%TYPE) IS
2193: SELECT COUNT(1) CNT,ADR.STATUS STAT
2194: FROM IGS_OR_INST_INT INST,
2195: IGS_OR_ADR_INT ADR
2196: WHERE INST.BATCH_ID = cp_batch_id AND

Line 2194: FROM IGS_OR_INST_INT INST,

2190: l_warn_inst_cphn NUMBER(6);
2191:
2192: CURSOR log_inst_adr_rcount_cur(cp_batch_id IGS_OR_INST_INT.BATCH_ID%TYPE) IS
2193: SELECT COUNT(1) CNT,ADR.STATUS STAT
2194: FROM IGS_OR_INST_INT INST,
2195: IGS_OR_ADR_INT ADR
2196: WHERE INST.BATCH_ID = cp_batch_id AND
2197: INST.INTERFACE_ID = ADR.INTERFACE_ID
2198: GROUP BY ADR.STATUS;

Line 2206: CURSOR log_inst_adru_rcount_cur(cp_batch_id IGS_OR_INST_INT.BATCH_ID%TYPE) IS

2202: l_comp_inst_adr NUMBER(6);
2203: l_err_inst_adr NUMBER(6);
2204: l_warn_inst_adr NUMBER(6);
2205:
2206: CURSOR log_inst_adru_rcount_cur(cp_batch_id IGS_OR_INST_INT.BATCH_ID%TYPE) IS
2207: SELECT COUNT(1) CNT,ADRU.STATUS STAT
2208: FROM IGS_OR_INST_INT INST,
2209: IGS_OR_ADR_INT ADR,
2210: IGS_OR_ADRUSGE_INT ADRU

Line 2208: FROM IGS_OR_INST_INT INST,

2204: l_warn_inst_adr NUMBER(6);
2205:
2206: CURSOR log_inst_adru_rcount_cur(cp_batch_id IGS_OR_INST_INT.BATCH_ID%TYPE) IS
2207: SELECT COUNT(1) CNT,ADRU.STATUS STAT
2208: FROM IGS_OR_INST_INT INST,
2209: IGS_OR_ADR_INT ADR,
2210: IGS_OR_ADRUSGE_INT ADRU
2211: WHERE INST.BATCH_ID = cp_batch_id AND
2212: INST.INTERFACE_ID = ADR.INTERFACE_ID AND

Line 2376: UPDATE IGS_OR_INST_INT INST

2372: FROM IGS_OR_INST_CPHN_INT CPHN
2373: WHERE CON.INTERFACE_CONTACTS_ID = CPHN.INTERFACE_CONT_ID AND
2374: CPHN.STATUS = '3');
2375:
2376: UPDATE IGS_OR_INST_INT INST
2377: SET STATUS = '4',ERROR_CODE = 'E055'
2378: WHERE STATUS = '1' AND
2379: EXISTS (SELECT 1
2380: FROM IGS_OR_INST_CON_INT CON

Line 2394: UPDATE IGS_OR_INST_INT INST

2390: FROM IGS_OR_INST_SDTL_INT SDTL
2391: WHERE STAT.INTERFACE_INST_STAT_ID = SDTL.INTERFACE_INST_STAT_ID AND
2392: SDTL.STATUS = '3');
2393:
2394: UPDATE IGS_OR_INST_INT INST
2395: SET STATUS = '4',ERROR_CODE = 'E056'
2396: WHERE STATUS = '1' AND
2397: EXISTS (SELECT 1
2398: FROM IGS_OR_INST_STAT_INT STAT

Line 2412: UPDATE IGS_OR_INST_INT INST

2408: FROM IGS_OR_ADRUSGE_INT ADU
2409: WHERE ADR.INTERFACE_ADDR_ID = ADU.INTERFACE_ADDR_ID AND
2410: ADU.STATUS = '3');
2411:
2412: UPDATE IGS_OR_INST_INT INST
2413: SET STATUS = '4',ERROR_CODE = 'E057'
2414: WHERE STATUS = '1' AND
2415: EXISTS (SELECT 1
2416: FROM IGS_OR_ADR_INT ADR

Line 2423: UPDATE IGS_OR_INST_INT INST

2419: -- Table deletion logic for one level child
2420:
2421: DELETE FROM IGS_OR_INST_NTS_INT WHERE STATUS = '1';
2422:
2423: UPDATE IGS_OR_INST_INT INST
2424: SET STATUS = '4',ERROR_CODE='E058'
2425: WHERE STATUS = '1' AND
2426: EXISTS (SELECT 1
2427: FROM IGS_OR_INST_NTS_INT NTS

Line 2444: DELETE FROM IGS_OR_INST_INT WHERE STATUS = '1';

2440:
2441: -- Delete in the main master table since it's status is now set appropriatly in the
2442: -- previous steps.
2443:
2444: DELETE FROM IGS_OR_INST_INT WHERE STATUS = '1';
2445:
2446: FND_FILE.Put_Line(FND_FILE.Log,'');
2447:
2448: FND_FILE.Put_Line(FND_FILE.Log,l_inst_rec_err_meaning);

Line 2616: p_new_inst_code IN igs_or_inst_int.new_institution_cd%TYPE,

2612: APP_EXCEPTION.Raise_Exception;
2613: END delete_log_int_rec;
2614:
2615: FUNCTION validate_inst_code(
2616: p_new_inst_code IN igs_or_inst_int.new_institution_cd%TYPE,
2617: p_exst_inst_code IN igs_or_inst_int.exst_institution_cd%TYPE,
2618: p_cwlk_inst_code IN igs_or_cwlk_v.inst_code%TYPE,
2619: p_interface_id IN igs_or_inst_int.interface_id%TYPE)
2620: RETURN BOOLEAN AS

Line 2617: p_exst_inst_code IN igs_or_inst_int.exst_institution_cd%TYPE,

2613: END delete_log_int_rec;
2614:
2615: FUNCTION validate_inst_code(
2616: p_new_inst_code IN igs_or_inst_int.new_institution_cd%TYPE,
2617: p_exst_inst_code IN igs_or_inst_int.exst_institution_cd%TYPE,
2618: p_cwlk_inst_code IN igs_or_cwlk_v.inst_code%TYPE,
2619: p_interface_id IN igs_or_inst_int.interface_id%TYPE)
2620: RETURN BOOLEAN AS
2621: /*

Line 2619: p_interface_id IN igs_or_inst_int.interface_id%TYPE)

2615: FUNCTION validate_inst_code(
2616: p_new_inst_code IN igs_or_inst_int.new_institution_cd%TYPE,
2617: p_exst_inst_code IN igs_or_inst_int.exst_institution_cd%TYPE,
2618: p_cwlk_inst_code IN igs_or_cwlk_v.inst_code%TYPE,
2619: p_interface_id IN igs_or_inst_int.interface_id%TYPE)
2620: RETURN BOOLEAN AS
2621: /*
2622: || Created By : ssaleem
2623: || Created On : 22-SEP-2003

Line 2638: UPDATE igs_or_inst_int

2634: IF (p_exst_inst_code IS NOT NULL AND p_exst_inst_code <> p_cwlk_inst_code) OR
2635: (p_new_inst_code IS NOT NULL AND p_new_inst_code <> p_cwlk_inst_code) THEN
2636:
2637: return_value := FALSE;
2638: UPDATE igs_or_inst_int
2639: SET status='3',error_code='E059'
2640: WHERE interface_id = p_interface_id;
2641:
2642: END IF;