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
6 -- kumma 14-JUN-2002 Uncommented call to message IGS_OR_INST_IMP_SUCC , 2410165
7 -- kumma 08-JUL-2002 Bug 2446067, In all the three procedures numericAltidcomp, simpleAltidcomp and exactAltidcomp,
8 -- Created two cursors c_cwlk_master_present and c_cwlk_detail_id.
9 -- Used these cursor before every call to IGS_OR_INST_IMP_002.Update_Crosswalk_master which includes
10 -- existing institution cd
11 -- and IGS_OR_INST_IMP_002.Create_Crosswalk_master.
12 -- In the Exception section of numericAltidcomp procedure, checked for the invalid_number exception,
13 -- as the alternate_id_value for this procedure should be neumeric, if it is not so then
14 -- we need to log a message stating that it should be neumeric only
15 --pkpatel 26-OCT-2002 Bug No: 2613704
16 -- Modified the validation for INST_PRIORITY_CODE_ID, GOVT_INSTITUTION_CD, INST_CONTROL_TYPE to refer
17 -- the new lookups instead of the tables.
18 -- ssawhney 2nd jan No of updates reduced, performance tuning done, gather stats function used.
19 -- ssaleem 24-SEP-03 The following changes have been done for IGS.L
20 -- 1. Logging mechanism introduced, FND_FILE.PUT_LINE replaced with methods in FND_LOG package
21 -- log writer procedure modified for this purpose and package level variables declared, intialised
22 -- to control logging.
23 -- 2. Cursors that utilise variables in SELECT statements are replaced with cursor parameters
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 -------------------------------------------------------------------------------------------
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;
42
43 v_error_log c_error_log%ROWTYPE;
44 BEGIN
45
46 open c_error_log(p_error_code,'IMPORT_INST_ERROR_CODE');
47 fetch c_error_log into v_error_log;
48 close c_error_log;
49
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
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.
67 || Known limitations, enhancements or remarks :
68 || Change History :
69 || Who When What
70 || pkpatel 25-OCT-2002 Bug No: 2613704
71 || Modified the validation for INST_PRIORITY_CODE_ID, GOVT_INSTITUTION_CD, INST_CONTROL_TYPE to refer
72 || the new lookups instead of the tables.
73 || gmaheswa 12-SEP-2003 Bug No: 2863933
74 || Modified local institution indicator to be equal to N
75 || (reverse chronological order - newest change first)
76 */
77 return boolean is
78
79 CURSOR c_lookup_type(cp_lookup_code igs_lookup_values.lookup_code%TYPE,
80 cp_lookup_type igs_lookup_values.lookup_type%TYPE,
81 cp_enabled_flag igs_lookup_values.enabled_flag%TYPE) IS
82 SELECT 'X'
83 FROM igs_lookup_values where
84 lookup_code = cp_lookup_code AND
85 lookup_type = cp_lookup_type AND
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
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;
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
112 where class = cp_class and
113 NVL(closed_ind,cp_closed_ind)=cp_closed_ind and
114 code_id= cp_sec_school_location_id;
115
116 c_sec_school_loc_id_rec c_sec_school_loc_id%ROWTYPE;
117
118 BEGIN
119 --Validation for LOCAL_INSTITUTION_IND field
120 if p_inst_rec.local_institution_ind is NOT NULL THEN
121 if p_inst_rec.local_institution_ind <>'N' THEN
122 ret_err_cd:='E010';
123 return false;
124 end if;
125 end if;
126
127 --Validation for OS_IND field
128 if p_inst_rec.os_ind is NOT NULL THEN
129 if p_inst_rec.os_ind <> 'N' THEN
130 ret_err_cd:='E011';
131 return false;
132 end if;
133 end if;
134
135 --Validation for GOVT_INSTITUTION_CD field
136 IF p_inst_rec.govt_institution_cd IS NOT NULL THEN
137 open c_lookup_type(p_inst_rec.govt_institution_cd,'OR_INST_GOV_CD','Y');
138 fetch c_lookup_type into c_lookup_type_rec;
139 if c_lookup_type%NOTFOUND THEN
140 ret_err_cd:='E012';
141 return false;
142 end if;
143 close c_lookup_type;
144 END IF;
145
146 --Validation for INST_CONTROL_TYPE field
147 open c_lookup_type(p_inst_rec.inst_control_type,'OR_INST_CTL_TYPE','Y');
148 fetch c_lookup_type into c_lookup_type_rec;
149 if c_lookup_type%NOTFOUND THEN
150 ret_err_cd:='E013';
151 return false;
152 end if;
153 close c_lookup_type;
154
155 --Validation for INSTITUTION_TYPE field
156 open c_institution_type(p_inst_rec.institution_type,'N');
157 fetch c_institution_type into c_institution_type_rec;
158 if c_institution_type%NOTFOUND THEN
159 ret_err_cd:='E014';
160 return false;
161 end if;
162 close c_institution_type;
163
164 --Validation for INSTITUTION_STATUS field
165 open c_institution_stat(p_inst_rec.institution_status,'N');
166 fetch c_institution_stat into c_institution_stat_rec;
167 if c_institution_stat%NOTFOUND THEN
168 ret_err_cd:='E015';
169 return false;
170 end if;
171 close c_institution_stat;
172
173 --Validation for PRIORITY_CD field
174 IF p_inst_rec.INST_PRIORITY_CD IS NOT NULL THEN
175 open c_lookup_type(p_inst_rec.INST_PRIORITY_CD,'OR_INST_PRIORITY_CD','Y');
176 fetch c_lookup_type into c_lookup_type_rec;
177 if c_lookup_type%NOTFOUND THEN
178 ret_err_cd:='E016';
179 return false;
180 end if;
181 close c_lookup_type;
182 END IF;
183
184 --Validation for SEC_SCHOOL_LOCATION_ID field
185 IF p_inst_rec.sec_school_location_id IS NOT NULL THEN
186 open c_sec_school_loc_id(p_inst_rec.sec_school_location_id,'SEC_SCHOOL_LOCATION','N');
187 fetch c_sec_school_loc_id into c_sec_school_loc_id_rec;
188 if c_sec_school_loc_id%NOTFOUND THEN
189 ret_err_cd:='E017';
190 return false;
191 end if;
192 close c_sec_school_loc_id;
193 END IF;
194
195 return true;
196 END validate_field_level_data;
197
198 PROCEDURE imp_or_institution(
199 ERRBUF OUT NOCOPY VARCHAR2,
200 RETCODE OUT NOCOPY NUMBER,
201 P_DATE IN VARCHAR2,
202 P_BATCH_ID IN NUMBER,
203 P_DATA_SOURCE IN VARCHAR2,
204 P_DS_MATCH IN VARCHAR2,
205 P_NUMERIC IN VARCHAR2,
206 P_ADDR_USAGE IN VARCHAR2,
207 P_PERSON_TYPE IN VARCHAR2,
208 P_ORG_ID IN NUMBER)
209 AS
210 /*************************************************************
211 Created By :samaresh
212 Date Created By : 13-JUL-2001
213 Purpose : This Procedure calls the required procedure depending
214 on the parameters passed to the job
215 Know limitations, enhancements or remarks
216 Change History
217 Who When What
218 gmaheswa 17-Jan-06 4938278: disable Business Events before starting bulk import process and enable after import.
219 Call IGS_PE_WF_GEN. ADDR_BULK_SYNCHRONIZATION to raise bulk address change notification.
220 ***************************************************************/
221
222 l_status VARCHAR2(5);
223 l_industry VARCHAR2(5);
224 l_schema VARCHAR2(30);
225 l_return BOOLEAN;
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;
235
236 IF FND_LOG.test(FND_LOG.LEVEL_EXCEPTION,'igs.plsql.igs_or_inst_imp_002') THEN
237 gb_write_exception_log2 := TRUE;
238 ELSE
239 gb_write_exception_log2 := FALSE;
240 END IF;
241
242 IF FND_LOG.test(FND_LOG.LEVEL_EXCEPTION,'igs.plsql.igs_or_inst_imp_003') THEN
243 gb_write_exception_log3 := TRUE;
244 ELSE
245 gb_write_exception_log3 := FALSE;
246 END IF;
247
248 g_request_id := FND_GLOBAL.conc_request_id;
249
250 IGS_GE_GEN_003.Set_org_id(p_org_id);
251
252 --Disable Business Event before running Bulk Process
253 IGS_PE_GEN_003.TURNOFF_TCA_BE (
254 P_TURNOFF => 'Y'
255 );
256
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);
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);
274 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,tabname => 'IGS_OR_CWLK_DTL_INT',cascade => TRUE);
275 END IF;
276
277 --
278 IF p_ds_match IS NULL AND p_numeric = 'N' THEN
279 FND_MESSAGE.Set_Name('IGS','IGS_OR_SIMPLE_ALT_ID');
280 FND_FILE.Put_Line(FND_FILE.Log,FND_MESSAGE.Get);
281 simpleAltidcomp(p_batch_id,p_data_source,p_addr_usage,p_person_type);
282 ELSIF p_ds_match IS NOT NULL AND p_numeric = 'N' THEN
283 FND_MESSAGE.Set_Name('IGS','IGS_OR_EXACT_ALT_ID');
284 FND_FILE.Put_Line(FND_FILE.Log,FND_MESSAGE.Get);
285 exactAltidcomp(p_batch_id,p_data_source,p_ds_match,p_addr_usage,p_person_type);
286 ELSIF p_ds_match IS NOT NULL AND p_numeric = 'Y' THEN
287 FND_MESSAGE.Set_Name('IGS','IGS_OR_NUMERIC_ALT_ID');
288 FND_FILE.Put_Line(FND_FILE.Log,FND_MESSAGE.Get);
289 numericAltidcomp(p_batch_id,p_data_source,p_ds_match,p_addr_usage,p_person_type);
290 ELSE
291 FND_MESSAGE.Set_Name('IGS','IGS_AD_INVALID_PARAM_COMB');
292 FND_FILE.Put_Line(FND_FILE.Log,FND_MESSAGE.Get);
293 END IF;
294
295 --Raise Bulk address process notification
296 IGS_PE_WF_GEN.ADDR_BULK_SYNCHRONIZATION(IGS_PE_WF_GEN.TI_ADDR_CHG_PERSONS);
297
298 --Enable Business Event before quiting Bulk Process
299 IGS_PE_GEN_003.TURNOFF_TCA_BE (
300 P_TURNOFF => 'N'
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;
310
311 --Enable Business Event before quiting Bulk Process
312 IGS_PE_GEN_003.TURNOFF_TCA_BE (
313 P_TURNOFF => 'N'
314 );
315 IGS_GE_MSG_STACK.Conc_Exception_Hndl;
316 END imp_or_institution;
317
318 PROCEDURE simpleAltidcomp(
319 p_batch_id IN NUMBER,
320 p_data_source IN VARCHAR2,
321 p_addr_usage IN VARCHAR2,
322 p_person_type IN VARCHAR2 )
323 AS
324 /*************************************************************
325 Created By :samaresh
326 Date Created By : 13-JUL-2001
327 Purpose : This Procedure imports records from the Institution
328 Interface Table to the institutions table if the user
329 has choosen Simple Alternate Id comparison
330 Know limitations, enhancements or remarks
331 Change History
332 Who When What
333 pkpatel 6-JAN-2003 Bug No: 2528605
334 Added the create_alternate_id procedures for the all the conditions.
335 Made the data_source_value as UPPER at the beginning and initialize the record varuable to NULL
336 kumma 08-JUL-2002 Bug 2446067, Created two cursors c_cwlk_master_present and c_cwlk_detail_id.
337 Used these cursor before every call to IGS_OR_INST_IMP_002.Update_Crosswalk_master
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
347 IO.BATCH_ID = TO_NUMBER(cp_batch_id) ;
348
349 p_inst_rec c_inst_cur%ROWTYPE;
350
351 CURSOR c_inst_code ( cp_data_source VARCHAR2 , cp_data_src_val VARCHAR2 ) IS
352 SELECT orcv.crosswalk_id, orcv.crosswalk_dtl_id, orcv.inst_code
353 FROM IGS_OR_CWLK_V ORCV
354 WHERE ORCV.ALT_ID_TYPE = cp_data_source AND
355 ORCV.ALT_ID_VALUE = cp_data_src_val ;
356
357 --mmkumar, party number impact, changed the folllowing cursor to verify from igs_pe_hz_parties instead of from hz_parties
358 CURSOR c_inst_present ( cp_inst_code VARCHAR2 ) IS
359 SELECT 'Y'
360 FROM igs_pe_hz_parties
361 WHERE oss_org_unit_cd = cp_inst_code;
362
363 --mmkumar, party number impact, changed the folllowing cursor to pick party_id from igs_pe_hz_parties instead of from hz_parties
364 CURSOR c_party_id ( cp_inst_code VARCHAR2 ) IS
365 SELECT party_id
366 FROM igs_pe_hz_parties
367 WHERE oss_org_unit_cd = cp_inst_code;
368
369 -- kumma, 2446067
370 -- Created the following cursor to check whether the code already exists in the cross walk master
371 CURSOR c_cwlk_master_present (cp_inst_code VARCHAR2) IS
372 SELECT institution_code, crosswalk_id
373 FROM IGS_OR_CWLK
374 WHERE institution_code = cp_inst_code;
375
376 l_Count NUMBER;
377 l_Instcount NUMBER;
378 l_Newinstcd igs_or_institution.institution_cd%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
388 l_cwlk_master_present c_cwlk_master_present%ROWTYPE;
389
390
391 BEGIN
392 FOR v_inst_rec IN c_inst_cur(p_data_source,p_batch_id,'2') LOOP
393
394 g_records_processed := g_records_processed + 1;
395
396 l_cwlkinst_rec.crosswalk_id := NULL;
397 l_cwlkinst_rec.crosswalk_dtl_id := NULL;
398 l_cwlkinst_rec.inst_code := NULL;
399 v_inst_rec.data_source_value := UPPER(v_inst_rec.data_source_value);
400
401 v_inst_rec.local_institution_ind := UPPER(v_inst_rec.local_institution_ind);
402 v_inst_rec.os_ind := UPPER(v_inst_rec.os_ind);
403 v_inst_rec.govt_institution_cd := UPPER(v_inst_rec.govt_institution_cd);
404 v_inst_rec.inst_control_type := UPPER(v_inst_rec.inst_control_type);
405 v_inst_rec.inst_priority_cd := UPPER(v_inst_rec.inst_priority_cd);
406
407 OPEN c_inst_code (v_inst_rec.data_source_id, v_inst_rec.data_source_value);
408 FETCH c_inst_code INTO l_cwlkinst_rec;
409 CLOSE c_inst_code ;
410
411 IF l_cwlkinst_rec.crosswalk_dtl_id IS NOT NULL THEN -- Record is found in the Crosswalk detail table
412
413 IF l_cwlkinst_rec.inst_code IS NOT NULL THEN -- The Institution Code in the Crosswalk table is present.
414
415 IF validate_inst_code(v_inst_rec.new_institution_cd,
416 v_inst_rec.exst_institution_cd,
417 l_cwlkinst_rec.inst_code,
418 v_inst_rec.interface_id) THEN
419 l_exists := NULL;
420
421 OPEN c_inst_present(l_cwlkinst_rec.inst_code);
422 FETCH c_inst_present INTO l_exists;
423 CLOSE c_inst_present ;
424
425 IF l_exists IS NULL THEN -- Error Has occured, as the institution code is not Present in the OSS
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
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
440 ELSE
441 IF validate_field_level_data(v_inst_rec,l_val_err) then
442 SAVEPOINT s_point;
443 -- Update the existing Institution
444
445 IGS_OR_INST_IMP_002.Update_Institution(l_cwlkinst_rec.inst_code, v_inst_rec,l_Errind,l_error_code,l_error_text);
446 -- No Records need to be either inserted or Updated in the Crosswalk Master and Crosswalk Detail Tables
447 /* Rollback if there was an error during Update Institutions */
448 IF l_Errind = 'Y' THEN
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'
458 WHERE INTERFACE_ID = v_inst_rec.interface_id;
459
460 ELSE
461
462 IGS_OR_INST_IMP_002.Create_Alternate_Id(l_cwlkinst_rec.inst_code,v_inst_rec.data_source_id,v_inst_rec.data_source_value,l_error_code,l_Errind);
463
464 IF l_errind = 'Y' THEN
465
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,
475 status = '3'
476 WHERE interface_id = v_inst_rec.interface_id;
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
486 OPEN c_party_id(l_cwlkinst_rec.inst_code);
487 FETCH c_party_id INTO l_party_id;
488 CLOSE c_party_id;
489 IGS_OR_INST_IMP_003_PKG.Process_Institution_Notes(v_inst_rec.interface_id,l_party_id,l_cwlkinst_rec.inst_code);
490 IGS_OR_INST_IMP_003_PKG.Process_Institution_Statistics(v_inst_rec.interface_id,l_party_id);
491 IGS_OR_INST_IMP_003_PKG.Process_Institution_Address(v_inst_rec.interface_id,p_addr_usage,l_party_id);
492 IGS_OR_INST_IMP_003_PKG.Process_Institution_Contacts(v_inst_rec.interface_id,p_person_type,l_party_id);
493 END IF;
494 END IF;
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
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;
508 END IF;
509 END IF;
510 ELSE -- The Institution Code in the Crosswalk table is NULL
511 IF v_inst_rec.exst_institution_cd IS NULL THEN -- If the exst_inst_code of the interface rec is null, then create
512 IF validate_field_level_data(v_inst_rec,l_val_err) THEN
513 SAVEPOINT s_point;
514
515 IGS_OR_INST_IMP_002.Create_Institution(v_inst_rec,l_Newinstcd,l_Errind,l_error_code,l_error_text); -- Create a new institution
516 /* Update the Crosswalk Master with the newly created Institution Code if no error has occured
517 during creation of new institution. If an error has occured then rollback to savepoint s_point */
518 IF l_Errind = 'Y' THEN
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'
528 WHERE INTERFACE_ID = v_inst_rec.interface_id;
529
530 ELSE
531
532 IGS_OR_INST_IMP_002.Update_Crosswalk_master (l_cwlkinst_rec.crosswalk_id,l_Newinstcd,l_Errind);
533
534
535 IF l_Errind = 'Y' THEN
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
545 SET error_code = 'E040', error_text= NULL , STATUS = '3'
546 WHERE INTERFACE_ID = v_inst_rec.interface_id;
547
548 ELSE
549 -- No records needs to be inserted to the Crosswalk Detail as the record is already found
550 /* Create a New Record in the table IGS_OR_ORG_ALT_ID if no error has occured during
551 updation of Crosswalk Master. If an error has occured then rollback to savepoint s_point */
552
553 IGS_OR_INST_IMP_002.Create_Alternate_Id(l_Newinstcd,v_inst_rec.data_source_id,v_inst_rec.data_source_value,l_error_code,l_Errind);
554
555 /* Rollback if there is an error during the create in the above step else call the import of child records */
556 IF l_Errind = 'Y' THEN -- STEP A
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
566 SET ERROR_CODE = l_error_code,
567 ERROR_TEXT =NULL,
568 STATUS = '3'
569 WHERE INTERFACE_ID = v_inst_rec.interface_id;
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
579 OPEN c_party_id(l_Newinstcd);
580 FETCH c_party_id INTO l_party_id;
581 CLOSE c_party_id;
582 IGS_OR_INST_IMP_003_PKG.Process_Institution_Notes(v_inst_rec.interface_id,l_party_id,l_Newinstcd);
583 IGS_OR_INST_IMP_003_PKG.Process_Institution_Statistics(v_inst_rec.interface_id,l_party_id);
584 IGS_OR_INST_IMP_003_PKG.Process_Institution_Address(v_inst_rec.interface_id,p_addr_usage,l_party_id);
585 IGS_OR_INST_IMP_003_PKG.Process_Institution_Contacts(v_inst_rec.interface_id,p_person_type,l_party_id);
586 END IF; -- STEP A
587 END IF;
588 END IF;
589 ELSE
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
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;
603 ELSE -- If the exst_inst_code of the interface rec is NOT NULL
604 l_exists := NULL;
605 OPEN c_inst_present(v_inst_rec.exst_institution_cd);
606 FETCH c_inst_present INTO l_exists;
607 CLOSE c_inst_present ;
608
609 IF l_exists IS NULL THEN -- Error Has occured, as the institution code is not Present in the OSS
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
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
623 IF validate_field_level_data(v_inst_rec,l_val_err) THEN
624 SAVEPOINT s_point;
625
626 IGS_OR_INST_IMP_002.Update_Institution(v_inst_rec.exst_institution_cd, v_inst_rec,l_Errind,l_error_code,l_error_text);
627 /* Update the Crosswalk Table if the Previous update is successful, else rollback to savepoint */
628 IF l_Errind = 'Y' THEN
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'
638 WHERE INTERFACE_ID = v_inst_rec.interface_id;
639
640 ELSE
641 -- kumma, 2446067
642 -- this code checks whether the v_inst_rec.exst_institution_cd already exists in the crosswalk master, and if it exists then does it
643 -- exits for the same l_cwlkinst_rec.crosswalk_id..if the corresponding crosswalk_id is not same then data is wrong
644 OPEN c_cwlk_master_present(v_inst_rec.exst_institution_cd);
645 FETCH c_cwlk_master_present INTO l_cwlk_master_present;
646 CLOSE c_cwlk_master_present;
647
648 IF l_cwlk_master_present.institution_code IS NOT NULL then
649 IF l_cwlkinst_rec.CROSSWALK_ID <> l_cwlk_master_present.crosswalk_id THEN
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
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';
663 ELSE
664 IGS_OR_INST_IMP_002.Update_Crosswalk_master(l_cwlkinst_rec.crosswalk_id,v_inst_rec.exst_institution_cd,l_Errind);
665 END IF;
666 ELSE
667 IGS_OR_INST_IMP_002.Update_Crosswalk_master(l_cwlkinst_rec.crosswalk_id,v_inst_rec.exst_institution_cd,l_Errind);
668 END IF;
669
670 -- additition of code ends here, kumma
671
672
673 -- No records needs to be inserted to the Crosswalk Detail
674 /* Rollback if there is an error during the create in the above step else call the import of child records */
675 IF l_Errind = 'Y' THEN
676 ROLLBACK TO s_point;
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
686 SET ERROR_CODE = 'E040', ERROR_TEXT=NULL, STATUS = '3'
687 WHERE INTERFACE_ID = v_inst_rec.interface_id;
688
689 END IF;
690 ELSE
691
692 IGS_OR_INST_IMP_002.Create_Alternate_Id(v_inst_rec.exst_institution_cd,v_inst_rec.data_source_id,v_inst_rec.data_source_value,l_error_code,l_Errind);
693
694 IF l_Errind = 'Y' THEN
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
704 SET ERROR_CODE = l_error_code,
705 ERROR_TEXT =NULL,
706 STATUS = '3'
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
716 OPEN c_party_id(v_inst_rec.exst_institution_cd);
717 FETCH c_party_id INTO l_party_id;
718 CLOSE c_party_id;
719 IGS_OR_INST_IMP_003_PKG.Process_Institution_Notes(v_inst_rec.interface_id,l_party_id,v_inst_rec.exst_institution_cd);
720 IGS_OR_INST_IMP_003_PKG.Process_Institution_Statistics(v_inst_rec.interface_id,l_party_id);
721 IGS_OR_INST_IMP_003_PKG.Process_Institution_Address(v_inst_rec.interface_id,p_addr_usage,l_party_id);
722 IGS_OR_INST_IMP_003_PKG.Process_Institution_Contacts(v_inst_rec.interface_id,p_person_type,l_party_id);
723 END IF;
724 END IF;
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
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;
738 END IF ;
739 END IF;
740 END IF;
741 ELSE -- l_exists = 'Y', implies that record was not found in the crosswalk table
742 IF v_inst_rec.exst_institution_cd IS NULL THEN -- Institution code is null
743 IF validate_field_level_data(v_inst_rec,l_val_err) THEN
744 SAVEPOINT s_point;
745 p_inst_rec := v_inst_rec;
746
747 IGS_OR_INST_IMP_002.Create_Institution(p_inst_rec,l_Newinstcd,l_Errind,l_error_code,l_error_text);
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'
757 WHERE INTERFACE_ID = v_inst_rec.interface_id;
758
759 ELSE
760 /* Create a Record in Crosswalk Master if the Previous Create was successful else rollback to the savepoint */
761
762
763 -- kumma, 2446067
764 -- Added the following code to check that the institution_code already exists in the cross walk
765 -- master table , if it exists then update it else create the new
766
767 OPEN c_cwlk_master_present(l_Newinstcd);
768 FETCH c_cwlk_master_present INTO l_cwlk_master_present;
769 CLOSE c_cwlk_master_present;
770 IF l_cwlk_master_present.institution_code IS NULL THEN
771 IGS_OR_INST_IMP_002.Create_Crosswalk_Master(l_Newinstcd,v_inst_rec.NAME,l_Errind,l_Crswlkid);
772 ELSE
773 l_Crswlkid := l_cwlk_master_present.crosswalk_id;
774 END IF;
775
776 /* Create a Record in the Crosswalk Detail if the Previous Create is successful else rollback to the savepoint */
777 IF l_Errind = 'Y' THEN
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'
787 WHERE INTERFACE_ID = v_inst_rec.interface_id;
788
789 ELSE
790 IGS_OR_INST_IMP_002.Create_Crosswalk_Detail(l_Crswlkid,v_inst_rec.data_source_id,v_inst_rec.data_source_value,l_Errind);
791 /* Create a Record in the Alternate Ids table IGS_OR_ORG_ALT_IDS if the Previous Create is successful else rollback to the savepoint */
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'
801 WHERE INTERFACE_ID = v_inst_rec.interface_id;
802
803 ELSE
804 IGS_OR_INST_IMP_002.Create_Alternate_Id(l_Newinstcd,v_inst_rec.data_source_id,v_inst_rec.data_source_value,l_error_code,l_Errind);
805 /* Call the Child process if the Above Create is successful else rollback */
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,
815 error_text=NULL ,
816 status = '3'
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
826 -- Child Process
827 OPEN c_party_id(l_Newinstcd);
828 FETCH c_party_id INTO l_party_id;
829 CLOSE c_party_id;
830
831 IGS_OR_INST_IMP_003_PKG.Process_Institution_Notes(v_inst_rec.interface_id,l_party_id,l_Newinstcd);
832 IGS_OR_INST_IMP_003_PKG.Process_Institution_Statistics(v_inst_rec.interface_id,l_party_id);
833 IGS_OR_INST_IMP_003_PKG.Process_Institution_Address(v_inst_rec.interface_id,p_addr_usage,l_party_id);
834 IGS_OR_INST_IMP_003_PKG.Process_Institution_Contacts(v_inst_rec.interface_id,p_person_type,l_party_id);
835 END IF;
836 END IF;
837 END IF;
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
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
851 ELSE -- Institution code is not null
852 l_exists := NULL;
853 OPEN c_inst_present(v_inst_rec.exst_institution_cd);
854 FETCH c_inst_present INTO l_exists;
855 CLOSE c_inst_present ;
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'
865 WHERE INTERFACE_ID = v_inst_rec.interface_id;
866
867 ELSE -- Institution is existing in the OSS system
868 IF validate_field_level_data(v_inst_rec,l_val_err) THEN
869 SAVEPOINT s_point ;
870
871 IGS_OR_INST_IMP_002.Update_Institution(v_inst_rec.exst_institution_cd, v_inst_rec,l_Errind,l_error_code,l_error_text);
872 /* Create a Record in Crosswalk Master if the Previous Create was successful else rollback to the savepoint */
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'
882 WHERE INTERFACE_ID = v_inst_rec.interface_id;
883 ELSE
884 -- kumma, 2446067
885 -- Added the following code to check that the institution_code already exists in the cross walk
886 -- master table , if it exists then update it else create the new
887
888 OPEN c_cwlk_master_present(v_inst_rec.exst_institution_cd);
889 FETCH c_cwlk_master_present INTO l_cwlk_master_present;
890 CLOSE c_cwlk_master_present;
891 IF l_cwlk_master_present.institution_code IS NULL THEN
892 IGS_OR_INST_IMP_002.Create_Crosswalk_Master(v_inst_rec.exst_institution_cd,v_inst_rec.name,l_Errind,l_Crswlkid);
893 ELSE
894 l_Crswlkid := l_cwlk_master_present.crosswalk_id;
895 END IF;
896
897 /* Create a Record in the Crosswalk Detail if the Previous Create is successful else rollback to the savepoint */
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'
907 WHERE INTERFACE_ID = v_inst_rec.interface_id;
908
909 ELSE
910 IGS_OR_INST_IMP_002.Create_Crosswalk_Detail(l_Crswlkid,v_inst_rec.data_source_id,v_inst_rec.data_source_value,l_Errind);
911 /* Create a Record in the Alternate Ids table IGS_OR_ORG_ALT_IDS if the Previous Create is successful else rollback to the savepoint */
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'
921 WHERE INTERFACE_ID = v_inst_rec.interface_id;
922
923 ELSE
924
925 IGS_OR_INST_IMP_002.Create_Alternate_Id(v_inst_rec.exst_institution_cd,v_inst_rec.data_source_id,v_inst_rec.data_source_value,l_error_code,l_Errind);
926
927 /* Call the Child process if the Above Create is successful else rollback */
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,
937 error_text = NULL,
938 status = '3'
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
948 OPEN c_party_id(v_inst_rec.exst_institution_cd);
949 FETCH c_party_id INTO l_party_id;
950 CLOSE c_party_id;
951 IGS_OR_INST_IMP_003_PKG.Process_Institution_Notes(v_inst_rec.interface_id,l_party_id,v_inst_rec.exst_institution_cd);
952 IGS_OR_INST_IMP_003_PKG.Process_Institution_Statistics(v_inst_rec.interface_id,l_party_id);
953 IGS_OR_INST_IMP_003_PKG.Process_Institution_Address(v_inst_rec.interface_id,p_addr_usage,l_party_id);
954 IGS_OR_INST_IMP_003_PKG.Process_Institution_Contacts(v_inst_rec.interface_id,p_person_type,l_party_id);
955
956 END IF;
957 END IF;
958 END IF;
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'
968 WHERE INTERFACE_ID = v_inst_rec.interface_id;
969
970 END IF;
971 END IF;
972 END IF;
973 END IF;
974
975 IF g_records_processed = 100 THEN
976 COMMIT;
977 g_records_processed := 0;
978 END IF;
979 END LOOP;
980
981 delete_log_int_rec(p_batch_id);
982 commit;
983
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;
993
994
995 PROCEDURE exactAltidcomp(
996 p_batch_id IN NUMBER,
997 p_data_source IN VARCHAR2,
998 p_ds_match IN VARCHAR2,
999 p_addr_usage IN VARCHAR2,
1000 p_person_type IN VARCHAR2 )
1001 AS
1002 /*************************************************************
1003 Created By :samaresh
1004 Date Created By : 13-JUL-2001
1005 Purpose : This Procedure imports records from the Institution
1006 Interface Table to the institutions table if the user
1007 has choosen Exact Alternate Id comparison
1008 Know limitations, enhancements or remarks
1009 Change History
1010 Who When What
1011 pkpatel 6-JAN-2003 Bug No: 2528605
1012 Made the data_source_value as UPPER at the beginning and initialize the record varuable to NULL
1013 kumma 08-JUL-2002 Bug 2446067, Created two cursors c_cwlk_master_present and c_cwlk_detail_id.
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 *
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 ;
1028
1029 CURSOR c_inst_code ( p_data_source VARCHAR2 , p_data_src_val VARCHAR2 ) IS
1030 SELECT crosswalk_id, crosswalk_dtl_id, inst_code
1031 FROM IGS_OR_CWLK_V ORCV
1032 WHERE ORCV.ALT_ID_TYPE = p_data_source AND
1033 ORCV.ALT_ID_VALUE = p_data_src_val ;
1034
1035 --mmkumar, party number impact, changed the folllowing cursor to verify from igs_pe_hz_parties instead of from hz_parties
1036 CURSOR c_inst_present ( cp_inst_code VARCHAR2 ) IS
1037 SELECT 'Y'
1038 FROM igs_pe_hz_parties
1039 WHERE oss_org_unit_cd = cp_inst_code;
1040
1041 CURSOR c_cwlk_id(cp_inst_cd VARCHAR2 ) IS
1042 SELECT crosswalk_id
1043 FROM IGS_OR_CWLK
1044 WHERE institution_code = cp_inst_cd;
1045
1046 --mmkumar, party number impact, changed the folllowing cursor to pick party_id from igs_pe_hz_parties instead of from hz_parties
1047 CURSOR c_party_id ( cp_inst_code VARCHAR2 ) IS
1048 SELECT party_id
1049 FROM igs_pe_hz_parties
1050 WHERE oss_org_unit_cd = cp_inst_code;
1051
1052 -- kumma, 2446067
1053 -- Created the following cursor to check whether the code already exists in the cross walk master
1054 CURSOR c_cwlk_master_present (cp_inst_code VARCHAR2) IS
1055 SELECT institution_code, crosswalk_id
1056 FROM IGS_OR_CWLK
1057 WHERE institution_code = cp_inst_code;
1058
1059
1060 l_Count NUMBER;
1061 l_Instcount NUMBER;
1062 l_Cwlkid igs_or_cwlk.crosswalk_id%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;
1072
1073
1074 BEGIN
1075
1076
1077 FOR v_inst_rec IN c_inst_cur('2',p_data_source,p_ds_match,p_batch_id) LOOP
1078
1079 g_records_processed := g_records_processed + 1;
1080
1081 v_inst_rec.data_source_value := UPPER(v_inst_rec.data_source_value);
1082
1083 v_inst_rec.local_institution_ind := UPPER(v_inst_rec.local_institution_ind);
1084 v_inst_rec.os_ind := UPPER(v_inst_rec.os_ind);
1085 v_inst_rec.govt_institution_cd := UPPER(v_inst_rec.govt_institution_cd);
1086 v_inst_rec.inst_control_type := UPPER(v_inst_rec.inst_control_type);
1087 v_inst_rec.inst_priority_cd := UPPER(v_inst_rec.inst_priority_cd);
1088
1089 v_inst_rec.alt_id_value := UPPER(v_inst_rec.alt_id_value);
1090 l_cwlkinst_rec.crosswalk_id := NULL;
1091 l_cwlkinst_rec.crosswalk_dtl_id := NULL;
1092 l_cwlkinst_rec.inst_code := NULL;
1093
1094 IF v_inst_rec.alt_id_value IS NOT NULL THEN
1095
1096 OPEN c_inst_code (p_ds_match, v_inst_rec.alt_id_value);
1097 FETCH c_inst_code INTO l_cwlkinst_rec;
1098 CLOSE c_inst_code ;
1099
1100 IF l_cwlkinst_rec.crosswalk_dtl_id IS NOT NULL THEN -- Record is found in the Crosswalk detail table with Alt id Value and Match Data source
1101
1102
1103 IF l_cwlkinst_rec.inst_code IS NOT NULL THEN -- The Institution Code in the Crosswalk table is present.
1104
1105 IF validate_inst_code(v_inst_rec.new_institution_cd,
1106 v_inst_rec.exst_institution_cd,
1107 l_cwlkinst_rec.inst_code,
1108 v_inst_rec.interface_id) THEN
1109
1110 l_exists := NULL;
1111 OPEN c_inst_present(l_cwlkinst_rec.inst_code);
1112 FETCH c_inst_present INTO l_exists;
1113 CLOSE c_inst_present ;
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'
1123 WHERE INTERFACE_ID = v_inst_rec.interface_id;
1124
1125
1126 ELSE -- The Institution Code in the Crosswalk is present in the OSS System
1127 IF validate_field_level_data(v_inst_rec,l_val_err) then
1128 SAVEPOINT s_point;
1129
1130 IGS_OR_INST_IMP_002.Update_Institution(l_cwlkinst_rec.inst_code, v_inst_rec,l_Errind,l_error_code,l_error_text);
1131 -- No Record Needs to Be created in Crosswalk Master
1132 -- Fetch the Crosswalk Id from the Master
1133 OPEN c_cwlk_id(l_cwlkinst_rec.inst_code);
1134 FETCH c_cwlk_id INTO l_Cwlkid;
1135 CLOSE c_cwlk_id;
1136 /* Create a Record if the above Update is Successful , else rollback to the savepoint */
1137 IF l_Errind = 'Y' THEN
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
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
1151 ELSE
1152 IGS_OR_INST_IMP_002.Create_Crosswalk_Detail(l_Cwlkid,v_inst_rec.data_source_id,v_inst_rec.data_source_value,l_Errind); -- create an entry for data_source, data_source_value
1153 /* Create a Record in the Alternate Ids table IGS_OR_ORG_ALT_IDS if the Previous Create is successful else rollback to the savepoint */
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'
1163 WHERE INTERFACE_ID = v_inst_rec.interface_id;
1164
1165 ELSE
1166 IGS_OR_INST_IMP_002.Create_Alternate_Id(l_cwlkinst_rec.inst_code,v_inst_rec.data_source_id,v_inst_rec.data_source_value,l_error_code,l_Errind);
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'
1176 WHERE INTERFACE_ID = v_inst_rec.interface_id;
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
1186 OPEN c_party_id(l_cwlkinst_rec.inst_code);
1187 FETCH c_party_id INTO l_party_id;
1188 CLOSE c_party_id;
1189
1190 IGS_OR_INST_IMP_003_PKG.Process_Institution_Notes(v_inst_rec.interface_id,l_party_id,l_cwlkinst_rec.inst_code);
1191 IGS_OR_INST_IMP_003_PKG.Process_Institution_Statistics(v_inst_rec.interface_id,l_party_id);
1192 IGS_OR_INST_IMP_003_PKG.Process_Institution_Address(v_inst_rec.interface_id,p_addr_usage,l_party_id);
1193 IGS_OR_INST_IMP_003_PKG.Process_Institution_Contacts(v_inst_rec.interface_id,p_person_type,l_party_id);
1194 END IF;
1195 END IF;
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'
1205 WHERE INTERFACE_ID = v_inst_rec.interface_id;
1206 --update status field
1207
1208 END IF;
1209 END IF;
1210 END IF;
1211 ELSE -- The Institution Code in the Crosswalk table is NULL
1212 IF v_inst_rec.exst_institution_cd IS NULL THEN -- If the exst_inst_code of the interface rec is null, then create
1213 IF validate_field_level_data(v_inst_rec,l_val_err) THEN
1214 SAVEPOINT s_point;
1215
1216 IGS_OR_INST_IMP_002.Create_Institution(v_inst_rec,l_Newinstcd,l_Errind,l_error_code,l_error_text);
1217 IF l_Errind = 'Y' THEN
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'
1227 WHERE INTERFACE_ID = v_inst_rec.interface_id;
1228
1229
1230
1231 ELSE
1232
1233 IGS_OR_INST_IMP_002.Update_Crosswalk_master (l_cwlkinst_rec.crosswalk_id,l_Newinstcd,l_Errind);
1234
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'
1244 WHERE INTERFACE_ID = v_inst_rec.interface_id;
1245
1246 ELSE
1247 OPEN c_cwlk_id(l_Newinstcd);
1248 FETCH c_cwlk_id INTO l_Cwlkid;
1249 CLOSE c_cwlk_id;
1250 IGS_OR_INST_IMP_002.Create_Crosswalk_Detail(l_Cwlkid,v_inst_rec.data_source_id,v_inst_rec.data_source_value,l_Errind); -- create an entry for data_source, data_source_value
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'
1260 WHERE INTERFACE_ID = v_inst_rec.interface_id;
1261
1262 ELSE
1263 IGS_OR_INST_IMP_002.Create_Alternate_Id(l_Newinstcd,v_inst_rec.data_source_id,v_inst_rec.data_source_value,l_error_code,l_Errind);
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'
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
1282 -- Call Child Process
1283 OPEN c_party_id(l_Newinstcd);
1284 FETCH c_party_id INTO l_party_id;
1285 CLOSE c_party_id;
1286
1287 IGS_OR_INST_IMP_003_PKG.Process_Institution_Notes(v_inst_rec.interface_id,l_party_id,l_Newinstcd);
1288 IGS_OR_INST_IMP_003_PKG.Process_Institution_Statistics(v_inst_rec.interface_id,l_party_id);
1289 IGS_OR_INST_IMP_003_PKG.Process_Institution_Address(v_inst_rec.interface_id,p_addr_usage,l_party_id);
1290 IGS_OR_INST_IMP_003_PKG.Process_Institution_Contacts(v_inst_rec.interface_id,p_person_type,l_party_id);
1291 END IF;
1292 END IF;
1293 END IF;
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'
1303 WHERE INTERFACE_ID = v_inst_rec.interface_id;
1304
1305 END IF;
1306 ELSE -- -- If the exst_inst_code of the interface rec is NOT null,
1307 l_exists := NULL;
1308 OPEN c_inst_present(v_inst_rec.exst_institution_cd);
1309 FETCH c_inst_present INTO l_exists;
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'
1319 WHERE INTERFACE_ID = v_inst_rec.interface_id;
1320 -- Update Status of the Record to 3 to indicate Error
1321
1322 ELSE -- Institution is existing in the OSS system
1323 IF validate_field_level_data(v_inst_rec,l_val_err) then
1324 SAVEPOINT s_point;
1325 IGS_OR_INST_IMP_002.Update_Institution(v_inst_rec.exst_institution_cd, v_inst_rec,l_Errind,l_error_code,l_error_text);
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
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
1339 ELSE
1340 ----------
1341 --kumma,2446067
1342 -- this code checks whether the l_newinstcd already exists in the crosswalk master, and if it exists then does it
1343 -- exits for the same l_cwlkinst_rec.crosswalk_id..if the corresponding crosswalk_id is not same then data is wrong
1344 OPEN c_cwlk_master_present(v_inst_rec.exst_institution_cd);
1345 FETCH c_cwlk_master_present INTO l_cwlk_master_present;
1346 CLOSE c_cwlk_master_present;
1347 IF l_cwlk_master_present.institution_code IS NOT NULL THEN
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
1357 WHERE INTERFACE_ID = v_inst_rec.interface_id;
1358 l_Errind := 'Y';
1359 l_error_code := 'E051';
1360
1361 ELSE
1362 IGS_OR_INST_IMP_002.Update_Crosswalk_master (l_cwlkinst_rec.crosswalk_id,v_inst_rec.exst_institution_cd,l_Errind);
1363 END IF;
1364 ELSE
1365 IGS_OR_INST_IMP_002.Update_Crosswalk_master (l_cwlkinst_rec.crosswalk_id,v_inst_rec.exst_institution_cd,l_Errind);
1366 END IF;
1367 -- additition of code ends here, kumma
1368 -----------
1369
1370 IF l_Errind = 'Y' THEN
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'
1380 WHERE INTERFACE_ID = v_inst_rec.interface_id;
1381
1382 END IF;
1383 ELSE
1384 OPEN c_cwlk_id(v_inst_rec.exst_institution_cd);
1385 FETCH c_cwlk_id INTO l_Cwlkid;
1386 CLOSE c_cwlk_id;
1387 IGS_OR_INST_IMP_002.Create_Crosswalk_Detail(l_Cwlkid,v_inst_rec.data_source_id,v_inst_rec.data_source_value,l_Errind); -- create an entry for data_source, data_source_value
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
1397 SET error_code = 'E007', error_text=NULL, STATUS = '3'
1398 WHERE INTERFACE_ID = v_inst_rec.interface_id;
1399
1400 ELSE
1401 IGS_OR_INST_IMP_002.Create_Alternate_Id(v_inst_rec.exst_institution_cd,v_inst_rec.data_source_id,v_inst_rec.data_source_value,l_error_code,l_Errind);
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'
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
1420 OPEN c_party_id(v_inst_rec.exst_institution_cd);
1421 FETCH c_party_id INTO l_party_id;
1422 CLOSE c_party_id;
1423
1424 IGS_OR_INST_IMP_003_PKG.Process_Institution_Notes(v_inst_rec.interface_id,l_party_id,v_inst_rec.exst_institution_cd);
1425 IGS_OR_INST_IMP_003_PKG.Process_Institution_Statistics(v_inst_rec.interface_id,l_party_id);
1426 IGS_OR_INST_IMP_003_PKG.Process_Institution_Address(v_inst_rec.interface_id,p_addr_usage,l_party_id);
1427 IGS_OR_INST_IMP_003_PKG.Process_Institution_Contacts(v_inst_rec.interface_id,p_person_type,l_party_id);
1428 END IF;
1429 END IF;
1430 END IF;
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'
1440 WHERE INTERFACE_ID = v_inst_rec.interface_id;
1441
1442 END IF;
1443
1444 END IF ;
1445 END IF;
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
1455 SET error_code = 'E041', error_text=NULL, STATUS = '3'
1456 WHERE INTERFACE_ID = v_inst_rec.interface_id;
1457
1458 END IF;
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
1468 SET error_code = 'E042', error_text=NULL, STATUS = '3'
1469 WHERE INTERFACE_ID = v_inst_rec.interface_id;
1470
1471 END IF;
1472
1473 IF g_records_processed = 100 THEN
1474 COMMIT;
1475 g_records_processed := 0;
1476 END IF;
1477
1478 END LOOP;
1479
1480 delete_log_int_rec(p_batch_id);
1481 commit;
1482
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;
1492
1493
1494 PROCEDURE numericAltidcomp(
1495 p_batch_id IN NUMBER,
1496 p_data_source IN VARCHAR2,
1497 p_ds_match IN VARCHAR2,
1498 p_addr_usage IN VARCHAR2,
1499 p_person_type IN VARCHAR2 )
1500 AS
1501 /*************************************************************
1502 Created By :samaresh
1503 Date Created By : 13-JUL-2001
1504 Purpose : This Procedure imports records from the Institution
1505 Interface Table to the institutions table if the user
1506 has choosen Numeric Alternate Id comparison
1507 Know limitations, enhancements or remarks
1508 Change History
1509 Who When What
1510 pkpatel 6-JAN-2003 Bug No: 2528605
1511 Made the data_source_value as UPPER at the beginning and initialize the record varuable to NULL
1512 kumma 08-JUL-2002 Bug 2446067, Created two cursors c_cwlk_master_present and c_cwlk_detail_id.
1513 Used these cursor before every call to IGS_OR_INST_IMP_002.Update_Crosswalk_master
1514 which includes the existing institution cd and IGS_OR_INST_IMP_002.Create_Crosswalk_master.
1515 In the Exception section checked for the invalid_number exception, as if the alternate_id_value
1516 is not neumeric we need to log a message stating that it should be neumeric only.
1517 Created a new cursor c_neumeric_test to check whether the Alternate Id value is neumeric or not.
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 *
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;
1532
1533
1534 -- KUMMA, 2446007
1535 -- removed the to_number function from cp_data_src_val and put the like instead of =
1536 -- and added one more column ALT_ID_VALUE in the select query
1537 CURSOR c_inst_code ( cp_data_source VARCHAR2 , cp_data_src_val VARCHAR2 ) IS
1538 SELECT crosswalk_id, crosswalk_dtl_id,inst_code, ALT_ID_VALUE
1539 FROM IGS_OR_CWLK_V ORCV
1540 WHERE ORCV.ALT_ID_TYPE = cp_data_source AND
1541 ORCV.ALT_ID_VALUE like '%' || cp_data_src_val;
1542
1543 --mmkumar, party number impact, changed the folllowing cursor to verify from igs_pe_hz_parties instead of from hz_parties
1544 CURSOR c_inst_present ( cp_inst_code VARCHAR2 ) IS
1545 SELECT 'Y'
1546 FROM igs_pe_hz_parties
1547 WHERE oss_org_unit_cd = cp_inst_code;
1548
1549 CURSOR c_cwlk_id (cp_inst_cd VARCHAR2 ) IS
1550 SELECT crosswalk_id
1551 FROM IGS_OR_CWLK
1552 WHERE institution_code = cp_inst_cd;
1553
1554 --mmkumar, party number impact, changed the folllowing cursor to pick party_id from igs_pe_hz_parties instead of from hz_parties
1555 CURSOR c_party_id ( cp_inst_code VARCHAR2 ) IS
1556 SELECT party_id
1557 FROM igs_pe_hz_parties
1558 WHERE oss_org_unit_cd = cp_inst_code;
1559
1560
1561 -- kumma, 2446067
1562 -- Created the following cursor to check whether the code already exists in the cross walk master
1563 CURSOR c_cwlk_master_present (cp_inst_code VARCHAR2) IS
1564 SELECT institution_code, crosswalk_id
1565 FROM IGS_OR_CWLK
1566 WHERE institution_code = cp_inst_code;
1567
1568 -- kumma, 2446007
1569 CURSOR c_neumeric_test (cp_data_src_val VARCHAR2) IS
1570 SELECT to_number(cp_data_src_val) FROM DUAL;
1571
1572 l_Count NUMBER;
1573 l_Instcount NUMBER;
1574 l_Cwlkid NUMBER;
1575 l_Newinstcd VARCHAR2(30);
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
1585 l_cwlk_master_present c_cwlk_master_present%ROWTYPE;
1586 v_inst_record c_inst_cur%ROWTYPE;
1587 l_neumeric_test c_neumeric_test%ROWTYPE;
1588 l_rec_count number := 1;
1589 BEGIN
1590 FOR v_inst_rec IN c_inst_cur('2',p_data_source,p_ds_match,p_batch_id) LOOP
1591
1592 g_records_processed := g_records_processed + 1;
1593
1594 v_inst_rec.data_source_value := UPPER(v_inst_rec.data_source_value);
1595
1596 v_inst_rec.local_institution_ind := UPPER(v_inst_rec.local_institution_ind);
1597 v_inst_rec.os_ind := UPPER(v_inst_rec.os_ind);
1598 v_inst_rec.govt_institution_cd := UPPER(v_inst_rec.govt_institution_cd);
1599 v_inst_rec.inst_control_type := UPPER(v_inst_rec.inst_control_type);
1600 v_inst_rec.inst_priority_cd := UPPER(v_inst_rec.inst_priority_cd);
1601
1602 v_inst_rec.alt_id_value := UPPER(v_inst_rec.alt_id_value);
1603 l_cwlkinst_rec.crosswalk_id := NULL;
1604 l_cwlkinst_rec.crosswalk_dtl_id := NULL;
1605 l_cwlkinst_rec.inst_code := NULL;
1606
1607 --kumma, 2446007, starting the annonymous procedure
1608 BEGIN
1609 -- kumma, 2446067
1610 -- copied the record into another record, as the variable v_inst_rec is not accessible inside the exception block
1611 -- setting the default value of the error indicator to 'Y'
1612 v_inst_record := v_inst_rec;
1613 l_Errind := 'Y';
1614
1615 FOR cwlkinst_rec IN c_inst_code (p_ds_match, v_inst_rec.data_source_value) LOOP
1616 BEGIN
1617
1618 OPEN c_neumeric_test(cwlkinst_rec.ALT_ID_VALUE);
1619 FETCH c_neumeric_test INTO l_neumeric_test;
1620 CLOSE c_neumeric_test;
1621
1622 IF to_number(cwlkinst_rec.ALT_ID_VALUE) = to_number(v_inst_rec.data_source_value) THEN -- if both are equal
1623
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
1633 WHERE INTERFACE_ID = v_inst_rec.interface_id;
1634 l_Errind := 'Y';
1635 l_error_code := 'E051';
1636 EXIT;
1637 ELSE
1638 l_Errind := 'N';
1639 l_error_code := '';
1640 END IF;
1641 l_cwlkinst_rec := cwlkinst_rec;
1642 END IF; -- if l_cwlkinst_rec.ALT_ID_VALUE = v_inst_rec.data_source_value
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;
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;
1659 WHEN OTHERS THEN
1660 IF c_neumeric_test%ISOPEN THEN
1661 CLOSE c_neumeric_test;
1662 END IF;
1663 END; -- end of annanomyous procedure
1664 END LOOP;
1665 l_rec_count := 1; -- setting back to 1
1666
1667 --kumma, 2446007, added this condition
1668 IF l_Errind = 'N' THEN
1669
1670 IF l_cwlkinst_rec.crosswalk_dtl_id IS NOT NULL THEN
1671
1672 IF l_cwlkinst_rec.inst_code IS NOT NULL THEN -- The Institution Code in the Crosswalk table not null fnd_file.put_line(fnd_file.log,'fould in dtl');
1673
1674 IF validate_inst_code(v_inst_rec.new_institution_cd,
1675 v_inst_rec.exst_institution_cd,
1676 l_cwlkinst_rec.inst_code,
1677 v_inst_rec.interface_id) THEN
1678
1679
1680 l_exists := NULL;
1681 OPEN c_inst_present(l_cwlkinst_rec.inst_code);
1682 FETCH c_inst_present INTO l_exists;
1683 CLOSE c_inst_present ;
1684
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'
1694 WHERE INTERFACE_ID = v_inst_rec.interface_id;
1695 -- Update Status of the Record to 3 to indicate Error
1696
1697 ELSE -- The Institution Code in the Crosswalk is present in the OSS System
1698 IF validate_field_level_data(v_inst_rec,l_val_err) THEN
1699 SAVEPOINT s_point;
1700 IGS_OR_INST_IMP_002.Update_Institution(l_cwlkinst_rec.inst_code, v_inst_rec,l_Errind,l_error_code,l_error_text);
1701 -- No Record Needs to Be created in Crosswalk Master
1702 IF l_Errind = 'Y' THEN
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'
1712 WHERE INTERFACE_ID = v_inst_rec.interface_id;
1713
1714 ELSE
1715
1716 OPEN c_cwlk_id(l_cwlkinst_rec.inst_code);
1717 FETCH c_cwlk_id INTO l_Cwlkid;
1718 CLOSE c_cwlk_id;
1719
1720 IGS_OR_INST_IMP_002.Create_Crosswalk_Detail(l_Cwlkid,v_inst_rec.data_source_id,v_inst_rec.data_source_value,l_Errind); -- create an entry for data_source, data_source_value
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'
1730 WHERE INTERFACE_ID = v_inst_rec.interface_id;
1731
1732 ELSE
1733 IGS_OR_INST_IMP_002.Create_Alternate_Id(l_cwlkinst_rec.inst_code,v_inst_rec.data_source_id,v_inst_rec.data_source_value,l_error_code,l_Errind);
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'
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
1752 OPEN c_party_id(l_cwlkinst_rec.inst_code);
1753 FETCH c_party_id INTO l_party_id;
1754 CLOSE c_party_id;
1755 IGS_OR_INST_IMP_003_PKG.Process_Institution_Notes(v_inst_rec.interface_id,l_party_id,l_cwlkinst_rec.inst_code);
1756 IGS_OR_INST_IMP_003_PKG.Process_Institution_Statistics(v_inst_rec.interface_id,l_party_id);
1757 IGS_OR_INST_IMP_003_PKG.Process_Institution_Address(v_inst_rec.interface_id,p_addr_usage,l_party_id);
1758 IGS_OR_INST_IMP_003_PKG.Process_Institution_Contacts(v_inst_rec.interface_id,p_person_type,l_party_id);
1759 END IF;
1760 END IF;
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'
1770 WHERE INTERFACE_ID = v_inst_rec.interface_id;
1771
1772 END IF;
1773 END IF;
1774 END IF;
1775 ELSE -- The Institution Code in the Crosswalk table is NULL
1776 IF v_inst_rec.exst_institution_cd IS NULL THEN -- If the exst_inst_code of the interface rec is null, then create
1777 IF validate_field_level_data(v_inst_rec,l_val_err) THEN
1778 SAVEPOINT s_point;
1779 IGS_OR_INST_IMP_002.Create_Institution(v_inst_rec,l_Newinstcd,l_Errind,l_error_code,l_error_text);
1780
1781 IF l_Errind = 'Y' THEN
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'
1791 WHERE INTERFACE_ID = v_inst_rec.interface_id;
1792
1793 ELSE
1794
1795 IGS_OR_INST_IMP_002.Update_Crosswalk_master (l_cwlkinst_rec.crosswalk_id,l_Newinstcd,l_Errind);
1796
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'
1806 WHERE INTERFACE_ID = v_inst_rec.interface_id;
1807
1808 ELSE
1809 OPEN c_cwlk_id(l_Newinstcd);
1810 FETCH c_cwlk_id INTO l_Cwlkid;
1811 CLOSE c_cwlk_id;
1812
1813 IGS_OR_INST_IMP_002.Create_Crosswalk_Detail(l_Cwlkid,v_inst_rec.data_source_id,v_inst_rec.data_source_value,l_Errind); -- create an entry for data_source, data_source_value
1814
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'
1824 WHERE INTERFACE_ID = v_inst_rec.interface_id;
1825
1826 ELSE
1827 IGS_OR_INST_IMP_002.Create_Alternate_Id(l_Newinstcd,v_inst_rec.data_source_id,v_inst_rec.data_source_value,l_error_code,l_Errind);
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'
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
1846 OPEN c_party_id(l_Newinstcd);
1847 FETCH c_party_id INTO l_party_id;
1848 CLOSE c_party_id;
1849 IGS_OR_INST_IMP_003_PKG.Process_Institution_Notes(v_inst_rec.interface_id,l_party_id,l_Newinstcd);
1850 IGS_OR_INST_IMP_003_PKG.Process_Institution_Statistics(v_inst_rec.interface_id,l_party_id);
1851 IGS_OR_INST_IMP_003_PKG.Process_Institution_Address(v_inst_rec.interface_id,p_addr_usage,l_party_id);
1852 IGS_OR_INST_IMP_003_PKG.Process_Institution_Contacts(v_inst_rec.interface_id,p_person_type,l_party_id);
1853 END IF;
1854 END IF;
1855 END IF;
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'
1865 WHERE INTERFACE_ID = v_inst_rec.interface_id;
1866
1867 END IF;
1868
1869 ELSE -- -- If the exst_inst_code of the interface rec is NOT null,
1870 l_exists := NULL;
1871 OPEN c_inst_present(v_inst_rec.exst_institution_cd);
1872 FETCH c_inst_present INTO l_exists;
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'
1882 WHERE INTERFACE_ID = v_inst_rec.interface_id;
1883
1884 ELSE -- Institution is existing in the OSS system
1885 IF validate_field_level_data(v_inst_rec,l_val_err) THEN
1886 SAVEPOINT s_point;
1887
1888 IGS_OR_INST_IMP_002.Update_Institution(v_inst_rec.exst_institution_cd, v_inst_rec,l_Errind,l_error_code,l_error_text);
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'
1898 WHERE INTERFACE_ID = v_inst_rec.interface_id;
1899
1900 ELSE
1901 ----
1902 --kumma,2446067..l_cwlkinst_rec
1903 -- this code checks whether the l_newinstcd already exists in the crosswalk master, and if it exists then does it
1904 -- exits for the same l_cwlkinst_rec.crosswalk_id..if the corresponding crosswalk_id is not same then data is wrong
1905 OPEN c_cwlk_master_present(v_inst_rec.exst_institution_cd);
1906 FETCH c_cwlk_master_present INTO l_cwlk_master_present;
1907 CLOSE c_cwlk_master_present;
1908 IF l_cwlk_master_present.institution_code IS NOT NULL then
1909
1910 IF l_cwlkinst_rec.CROSSWALK_ID <> l_cwlk_master_present.crosswalk_id THEN
1911
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
1921 WHERE INTERFACE_ID = v_inst_rec.interface_id;
1922 l_Errind := 'Y';
1923 l_error_code := 'E051';
1924
1925 ELSE
1926
1927 IGS_OR_INST_IMP_002.Update_Crosswalk_master (l_cwlkinst_rec.crosswalk_id,v_inst_rec.exst_institution_cd,l_Errind);
1928 END IF;
1929 ELSE
1930 IGS_OR_INST_IMP_002.Update_Crosswalk_master (l_cwlkinst_rec.crosswalk_id,v_inst_rec.exst_institution_cd,l_Errind);
1931 END IF;
1932 -- additition of code ends here, kumma
1933 ----
1934
1935 IF l_Errind = 'Y' THEN
1936
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'
1946 WHERE INTERFACE_ID = v_inst_rec.interface_id;
1947
1948 END IF;
1949 ELSE
1950 OPEN c_cwlk_id(v_inst_rec.exst_institution_cd);
1951 FETCH c_cwlk_id INTO l_Cwlkid;
1952 CLOSE c_cwlk_id;
1953
1954 IGS_OR_INST_IMP_002.Create_Crosswalk_Detail(l_Cwlkid,v_inst_rec.data_source_id,v_inst_rec.data_source_value,l_Errind); -- create an entry for data_source, data_source_value
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'
1964 WHERE INTERFACE_ID = v_inst_rec.interface_id;
1965
1966 ELSE
1967 IGS_OR_INST_IMP_002.Create_Alternate_Id(v_inst_rec.exst_institution_cd,v_inst_rec.data_source_id,v_inst_rec.data_source_value,l_error_code,l_Errind);
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'
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
1986 OPEN c_party_id(v_inst_rec.exst_institution_cd);
1987 FETCH c_party_id INTO l_party_id;
1988 CLOSE c_party_id;
1989 IGS_OR_INST_IMP_003_PKG.Process_Institution_Notes(v_inst_rec.interface_id,l_party_id,v_inst_rec.exst_institution_cd);
1990 IGS_OR_INST_IMP_003_PKG.Process_Institution_Statistics(v_inst_rec.interface_id,l_party_id);
1991 IGS_OR_INST_IMP_003_PKG.Process_Institution_Address(v_inst_rec.interface_id,p_addr_usage,l_party_id);
1992 IGS_OR_INST_IMP_003_PKG.Process_Institution_Contacts(v_inst_rec.interface_id,p_person_type,l_party_id);
1993 END IF;
1994 END IF;
1995 END IF;
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'
2005 WHERE INTERFACE_ID = v_inst_rec.interface_id;
2006
2007 END IF;
2008 END IF ;
2009 END IF;
2010 END IF;
2011
2012 END IF; -- kumma , 2446007 added this new condition,
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
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
2026 END IF;
2027
2028 --kumma,2446007
2029 EXCEPTION
2030 WHEN INVALID_NUMBER THEN
2031 -- Handling of Invalid Number exception is done in the anonymous block before, so
2032 -- no code is written here.
2033 NULL;
2034 END; -- annonymous procedure ends
2035
2036 IF g_records_processed = 100 THEN
2037 COMMIT;
2038 g_records_processed := 0;
2039 END IF;
2040
2041 END LOOP;
2042 delete_log_int_rec(p_batch_id);
2043 commit;
2044
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;
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
2061 from the INT tables and updates the status
2062 of master table appropriately. Also it takes
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,
2072 In the above case it will be E055.
2073
2074 Change History
2075 Who When What
2076 ***************************************************************/
2077
2078 CURSOR inst_lookup_cur(cp_lookup_type IGS_LOOKUP_VALUES.LOOKUP_TYPE%TYPE) IS
2079 SELECT lookup_code,meaning
2080 FROM IGS_LOOKUP_VALUES
2081 WHERE
2082 LOOKUP_TYPE = cp_lookup_type;
2083
2084 l_lookup_rec inst_lookup_cur%ROWTYPE;
2085
2086 l_inst_meaning IGS_LOOKUP_VALUES.MEANING%TYPE;
2087 l_inst_note_meaning IGS_LOOKUP_VALUES.MEANING%TYPE;
2088 l_inst_stat_meaning IGS_LOOKUP_VALUES.MEANING%TYPE;
2089 l_inst_stat_dtl_meaning IGS_LOOKUP_VALUES.MEANING%TYPE;
2090 l_inst_cont_meaning IGS_LOOKUP_VALUES.MEANING%TYPE;
2091 l_inst_cont_phone_meaning IGS_LOOKUP_VALUES.MEANING%TYPE;
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
2101 (STATUS = cp_status_error OR STATUS = cp_status_warn);
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;
2111
2112 l_inst_rec log_inst_rcount_cur%ROWTYPE;
2113 l_tot_inst NUMBER(6);
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
2123 INST.INTERFACE_ID = NT.INTERFACE_ID
2124 GROUP BY NT.STATUS;
2125
2126 l_inst_note_rec log_inst_note_rcount_cur%ROWTYPE;
2127 l_tot_inst_note NUMBER(6);
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
2137 INST.INTERFACE_ID = STAT.INTERFACE_ID
2138 GROUP BY STAT.STATUS;
2139
2140 l_inst_stat_rec log_inst_stat_rcount_cur%ROWTYPE;
2141 l_tot_inst_stat NUMBER(6);
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
2151 WHERE INST.BATCH_ID = cp_batch_id AND
2152 INST.INTERFACE_ID = STAT.INTERFACE_ID AND
2153 STAT.INTERFACE_INST_STAT_ID = SDTL.INTERFACE_INST_STAT_ID
2154 GROUP BY SDTL.STATUS;
2155
2156 l_inst_sdtl_rec log_inst_sdtl_rcount_cur%ROWTYPE;
2157 l_tot_inst_sdtl NUMBER(6);
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
2167 INST.INTERFACE_ID = CON.INTERFACE_ID
2168 GROUP BY CON.STATUS;
2169
2170 l_inst_con_rec log_inst_con_rcount_cur%ROWTYPE;
2171 l_tot_inst_con NUMBER(6);
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
2181 WHERE INST.BATCH_ID = cp_batch_id AND
2182 INST.INTERFACE_ID = CON.INTERFACE_ID AND
2183 CON.INTERFACE_CONTACTS_ID = CPHN.INTERFACE_CONT_ID
2184 GROUP BY CPHN.STATUS;
2185
2186 l_inst_cphn_rec log_inst_cphn_rcount_cur%ROWTYPE;
2187 l_tot_inst_cphn NUMBER(6);
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
2197 INST.INTERFACE_ID = ADR.INTERFACE_ID
2198 GROUP BY ADR.STATUS;
2199
2200 l_inst_adr_rec log_inst_adr_rcount_cur%ROWTYPE;
2201 l_tot_inst_adr NUMBER(6);
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
2211 WHERE INST.BATCH_ID = cp_batch_id AND
2212 INST.INTERFACE_ID = ADR.INTERFACE_ID AND
2213 ADR.INTERFACE_ADDR_ID = ADRU.INTERFACE_ADDR_ID
2214 GROUP BY ADRU.STATUS;
2215
2216 l_inst_adru_rec log_inst_adru_rcount_cur%ROWTYPE;
2217 l_tot_inst_adru NUMBER(6);
2218 l_comp_inst_adru NUMBER(6);
2219 l_err_inst_adru NUMBER(6);
2220 l_warn_inst_adru NUMBER(6);
2221
2222 BEGIN
2223 l_tot_inst := 0;
2224 l_comp_inst := 0;
2225 l_err_inst := 0;
2226 l_warn_inst := 0;
2227
2228 l_tot_inst_stat := 0;
2229 l_comp_inst_stat := 0;
2230 l_err_inst_stat := 0;
2231 l_warn_inst_stat := 0;
2232
2233 l_tot_inst_sdtl := 0;
2234 l_comp_inst_sdtl := 0;
2235 l_err_inst_sdtl := 0;
2236 l_warn_inst_sdtl := 0;
2237
2238 l_tot_inst_con := 0;
2239 l_comp_inst_con := 0;
2240 l_err_inst_con := 0;
2241 l_warn_inst_con := 0;
2242
2243 l_tot_inst_cphn := 0;
2244 l_comp_inst_cphn := 0;
2245 l_err_inst_cphn := 0;
2246 l_warn_inst_cphn := 0;
2247
2248 l_tot_inst_adr := 0;
2249 l_comp_inst_adr := 0;
2250 l_err_inst_adr := 0;
2251 l_warn_inst_adr := 0;
2252
2253 l_tot_inst_adru := 0;
2254 l_comp_inst_adru := 0;
2255 l_err_inst_adru := 0;
2256 l_warn_inst_adru := 0;
2257
2258 l_tot_inst_note := 0;
2259 l_comp_inst_note := 0;
2260 l_err_inst_note := 0;
2261 l_warn_inst_note := 0;
2262
2263 FOR l_lookup_rec IN inst_lookup_cur('OR_INST_IMPORT_LOG') LOOP
2264
2265 IF l_lookup_rec.lookup_code = 'INST' THEN
2266 l_inst_meaning := l_lookup_rec.meaning;
2267 ELSIF l_lookup_rec.lookup_code = 'INST_NOTE' THEN
2268 l_inst_note_meaning := l_lookup_rec.meaning;
2269 ELSIF l_lookup_rec.lookup_code = 'INST_STAT' THEN
2270 l_inst_stat_meaning := l_lookup_rec.meaning;
2271 ELSIF l_lookup_rec.lookup_code = 'INST_STAT_DTL' THEN
2272 l_inst_stat_dtl_meaning := l_lookup_rec.meaning;
2273 ELSIF l_lookup_rec.lookup_code = 'INST_CONT' THEN
2274 l_inst_cont_meaning := l_lookup_rec.meaning;
2275 ELSIF l_lookup_rec.lookup_code = 'INST_CONT_PHONE' THEN
2276 l_inst_cont_phone_meaning := l_lookup_rec.meaning;
2277 ELSIF l_lookup_rec.lookup_code = 'INST_ADDR' THEN
2278 l_inst_addr_meaning := l_lookup_rec.meaning;
2279 ELSIF l_lookup_rec.lookup_code = 'INST_ADDR_USAGE' THEN
2280 l_inst_addr_usage_meaning := l_lookup_rec.meaning;
2281 ELSIF l_lookup_rec.lookup_code = 'INST_REC_ERR_WARN' THEN
2282 l_inst_rec_err_meaning := l_lookup_rec.meaning;
2283 END IF;
2284
2285 END LOOP;
2286
2287
2288 FOR l_inst_note_rec IN log_inst_note_rcount_cur(p_batch_id) LOOP
2289 IF l_inst_note_rec.STAT = '1' THEN
2290 l_comp_inst_note := l_inst_note_rec.CNT;
2291 ELSIF l_inst_note_rec.STAT = '3' THEN
2292 l_err_inst_note := l_inst_note_rec.CNT;
2293 ELSIF l_inst_note_rec.STAT = '4' THEN
2294 l_warn_inst_note := l_inst_note_rec.CNT;
2295 END IF;
2296 END LOOP;
2297 l_tot_inst_note := l_comp_inst_note + l_err_inst_note + l_warn_inst_note;
2298
2299 FOR l_inst_stat_rec IN log_inst_stat_rcount_cur(p_batch_id) LOOP
2300 IF l_inst_stat_rec.STAT = '1' THEN
2301 l_comp_inst_stat := l_inst_stat_rec.CNT;
2302 ELSIF l_inst_stat_rec.STAT = '3' THEN
2303 l_err_inst_stat := l_inst_stat_rec.CNT;
2304 ELSIF l_inst_stat_rec.STAT = '4' THEN
2305 l_warn_inst_stat := l_inst_stat_rec.CNT;
2306 END IF;
2307 END LOOP;
2308 l_tot_inst_stat := l_comp_inst_stat + l_err_inst_stat + l_warn_inst_stat;
2309
2310 FOR l_inst_sdtl_rec IN log_inst_sdtl_rcount_cur(p_batch_id) LOOP
2311 IF l_inst_sdtl_rec.STAT = '1' THEN
2312 l_comp_inst_sdtl := l_inst_sdtl_rec.CNT;
2313 ELSIF l_inst_sdtl_rec.STAT = '3' THEN
2314 l_err_inst_sdtl := l_inst_sdtl_rec.CNT;
2315 ELSIF l_inst_sdtl_rec.STAT = '4' THEN
2316 l_warn_inst_sdtl := l_inst_sdtl_rec.CNT;
2317 END IF;
2318 END LOOP;
2319 l_tot_inst_sdtl := l_comp_inst_sdtl + l_err_inst_sdtl + l_warn_inst_sdtl;
2320
2321 FOR l_inst_con_rec IN log_inst_con_rcount_cur(p_batch_id) LOOP
2322 IF l_inst_con_rec.STAT = '1' THEN
2323 l_comp_inst_con := l_inst_con_rec.CNT;
2324 ELSIF l_inst_con_rec.STAT = '3' THEN
2325 l_err_inst_con := l_inst_con_rec.CNT;
2326 ELSIF l_inst_con_rec.STAT = '4' THEN
2327 l_warn_inst_con := l_inst_con_rec.CNT;
2328 END IF;
2329 END LOOP;
2330 l_tot_inst_con := l_comp_inst_con + l_err_inst_con + l_warn_inst_con;
2331
2332 FOR l_inst_cphn_rec IN log_inst_cphn_rcount_cur(p_batch_id) LOOP
2333 IF l_inst_cphn_rec.STAT = '1' THEN
2334 l_comp_inst_cphn := l_inst_cphn_rec.CNT;
2335 ELSIF l_inst_cphn_rec.STAT = '3' THEN
2336 l_err_inst_cphn := l_inst_cphn_rec.CNT;
2337 ELSIF l_inst_cphn_rec.STAT = '4' THEN
2338 l_warn_inst_cphn := l_inst_cphn_rec.CNT;
2339 END IF;
2340 END LOOP;
2341 l_tot_inst_cphn := l_comp_inst_cphn + l_err_inst_cphn + l_warn_inst_cphn;
2342
2343 FOR l_inst_adr_rec IN log_inst_adr_rcount_cur(p_batch_id) LOOP
2344 IF l_inst_adr_rec.STAT = '1' THEN
2345 l_comp_inst_adr := l_inst_adr_rec.CNT;
2346 ELSIF l_inst_adr_rec.STAT = '3' THEN
2347 l_err_inst_adr := l_inst_adr_rec.CNT;
2348 ELSIF l_inst_adr_rec.STAT = '4' THEN
2349 l_warn_inst_adr := l_inst_adr_rec.CNT;
2350 END IF;
2351 END LOOP;
2352 l_tot_inst_adr := l_comp_inst_adr + l_err_inst_adr + l_warn_inst_adr;
2353
2354 FOR l_inst_adru_rec IN log_inst_adru_rcount_cur(p_batch_id) LOOP
2355 IF l_inst_adru_rec.STAT = '1' THEN
2356 l_comp_inst_adru := l_inst_adru_rec.CNT;
2357 ELSIF l_inst_adru_rec.STAT = '3' THEN
2358 l_err_inst_adru := l_inst_adru_rec.CNT;
2359 ELSIF l_inst_adru_rec.STAT = '4' THEN
2360 l_warn_inst_adru := l_inst_adru_rec.CNT;
2361 END IF;
2362 END LOOP;
2363 l_tot_inst_adru := l_comp_inst_adru + l_err_inst_adru + l_warn_inst_adru;
2364
2365
2366 -- Table deletion logic for 2 level Childs -- Contact and Contact Phone
2367 DELETE FROM IGS_OR_INST_CPHN_INT WHERE STATUS = '1';
2368
2369 DELETE FROM IGS_OR_INST_CON_INT CON
2370 WHERE STATUS = '1' AND
2371 NOT EXISTS (SELECT 1
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
2381 WHERE CON.INTERFACE_ID = INST.INTERFACE_ID);
2382
2383 -- Table deletion logic for 2 level Childs -- Statistics and Statistics Details
2384
2385 DELETE FROM IGS_OR_INST_SDTL_INT WHERE STATUS = '1';
2386
2387 DELETE FROM IGS_OR_INST_STAT_INT STAT
2388 WHERE STATUS = '1' AND
2389 NOT EXISTS (SELECT 1
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
2399 WHERE STAT.INTERFACE_ID = INST.INTERFACE_ID);
2400
2401 -- Table deletion logic for 2 level Childs -- Address and Address Usage
2402
2403 DELETE FROM IGS_OR_ADRUSGE_INT WHERE STATUS = '1';
2404
2405 DELETE FROM IGS_OR_ADR_INT ADR
2406 WHERE STATUS = '1' AND
2407 NOT EXISTS (SELECT 1
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
2417 WHERE ADR.INTERFACE_ID = INST.INTERFACE_ID);
2418
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
2428 WHERE NTS.INTERFACE_ID = INST.INTERFACE_ID);
2429
2430 FOR l_inst_rec IN log_inst_rcount_cur(p_batch_id) LOOP
2431 IF l_inst_rec.STAT = '1' THEN
2432 l_comp_inst := l_inst_rec.CNT;
2433 ELSIF l_inst_rec.STAT = '3' THEN
2434 l_err_inst := l_inst_rec.CNT;
2435 ELSIF l_inst_rec.STAT = '4' THEN
2436 l_warn_inst := l_inst_rec.CNT;
2437 END IF;
2438 END LOOP;
2439 l_tot_inst := l_comp_inst + l_err_inst + l_warn_inst;
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);
2449 FND_FILE.Put_Line(FND_FILE.Log,'-------------------------------------------');
2450 FND_FILE.Put_Line(FND_FILE.Log,'');
2451
2452 FND_MESSAGE.Set_Name('IGS','IGS_OR_INST_IMP_HEADER');
2453 FND_FILE.Put_Line(FND_FILE.Log,FND_MESSAGE.Get);
2454 FND_FILE.Put_Line(FND_FILE.Log,'-------------------------------------------');
2455
2456 FOR l_inst_err_rec IN log_inst_err_cur(p_batch_id,'3','4') LOOP
2457 FND_FILE.Put_Line(FND_FILE.Log,l_inst_err_rec.EREC);
2458 END LOOP;
2459 FND_FILE.Put_Line(FND_FILE.Log,'');
2460
2461 FND_FILE.Put_Line(FND_FILE.Log,l_inst_meaning);
2462 FND_MESSAGE.Set_Name('IGS','IGS_AD_TOT_REC_PRC');
2463 FND_MESSAGE.Set_Token('RCOUNT',l_tot_inst);
2464 FND_FILE.Put_Line(FND_FILE.Log,FND_MESSAGE.Get);
2465
2466 FND_MESSAGE.Set_Name('IGS','IGS_AD_TOT_REC_SUCC');
2467 FND_MESSAGE.Set_Token('RCOUNT',l_comp_inst);
2468 FND_FILE.Put_Line(FND_FILE.Log,FND_MESSAGE.Get);
2469
2470 FND_MESSAGE.Set_Name('IGS','IGS_AD_TOT_REC_FAIL');
2471 FND_MESSAGE.Set_Token('RCOUNT',l_err_inst);
2472 FND_FILE.Put_Line(FND_FILE.Log,FND_MESSAGE.Get);
2473
2474 FND_MESSAGE.Set_Name('IGS','IGS_OR_TOT_REC_WARN');
2475 FND_MESSAGE.Set_Token('RCOUNT',l_warn_inst);
2476 FND_FILE.Put_Line(FND_FILE.Log,FND_MESSAGE.Get);
2477 FND_FILE.Put_Line(FND_FILE.Log,'');
2478
2479 FND_FILE.Put_Line(FND_FILE.Log,l_inst_note_meaning);
2480 FND_MESSAGE.Set_Name('IGS','IGS_AD_TOT_REC_PRC');
2481 FND_MESSAGE.Set_Token('RCOUNT',l_tot_inst_note);
2482 FND_FILE.Put_Line(FND_FILE.Log,FND_MESSAGE.Get);
2483
2484 FND_MESSAGE.Set_Name('IGS','IGS_AD_TOT_REC_SUCC');
2485 FND_MESSAGE.Set_Token('RCOUNT',l_comp_inst_note);
2486 FND_FILE.Put_Line(FND_FILE.Log,FND_MESSAGE.Get);
2487
2488 FND_MESSAGE.Set_Name('IGS','IGS_AD_TOT_REC_FAIL');
2489 FND_MESSAGE.Set_Token('RCOUNT',l_err_inst_note);
2490 FND_FILE.Put_Line(FND_FILE.Log,FND_MESSAGE.Get);
2491
2492 FND_MESSAGE.Set_Name('IGS','IGS_OR_TOT_REC_WARN');
2493 FND_MESSAGE.Set_Token('RCOUNT',l_warn_inst_note);
2494 FND_FILE.Put_Line(FND_FILE.Log,FND_MESSAGE.Get);
2495 FND_FILE.Put_Line(FND_FILE.Log,'');
2496
2497 FND_FILE.Put_Line(FND_FILE.Log,l_inst_stat_meaning);
2498 FND_MESSAGE.Set_Name('IGS','IGS_AD_TOT_REC_PRC');
2499 FND_MESSAGE.Set_Token('RCOUNT',l_tot_inst_stat);
2500 FND_FILE.Put_Line(FND_FILE.Log,FND_MESSAGE.Get);
2501
2502 FND_MESSAGE.Set_Name('IGS','IGS_AD_TOT_REC_SUCC');
2503 FND_MESSAGE.Set_Token('RCOUNT',l_comp_inst_stat);
2504 FND_FILE.Put_Line(FND_FILE.Log,FND_MESSAGE.Get);
2505
2506 FND_MESSAGE.Set_Name('IGS','IGS_AD_TOT_REC_FAIL');
2507 FND_MESSAGE.Set_Token('RCOUNT',l_err_inst_stat);
2508 FND_FILE.Put_Line(FND_FILE.Log,FND_MESSAGE.Get);
2509
2510 FND_MESSAGE.Set_Name('IGS','IGS_OR_TOT_REC_WARN');
2511 FND_MESSAGE.Set_Token('RCOUNT',l_warn_inst_stat);
2512 FND_FILE.Put_Line(FND_FILE.Log,FND_MESSAGE.Get);
2513 FND_FILE.Put_Line(FND_FILE.Log,'');
2514
2515 FND_FILE.Put_Line(FND_FILE.Log,l_inst_stat_dtl_meaning);
2516 FND_MESSAGE.Set_Name('IGS','IGS_AD_TOT_REC_PRC');
2517 FND_MESSAGE.Set_Token('RCOUNT',l_tot_inst_sdtl);
2518 FND_FILE.Put_Line(FND_FILE.Log,FND_MESSAGE.Get);
2519
2520 FND_MESSAGE.Set_Name('IGS','IGS_AD_TOT_REC_SUCC');
2521 FND_MESSAGE.Set_Token('RCOUNT',l_comp_inst_sdtl);
2522 FND_FILE.Put_Line(FND_FILE.Log,FND_MESSAGE.Get);
2523
2524 FND_MESSAGE.Set_Name('IGS','IGS_AD_TOT_REC_FAIL');
2525 FND_MESSAGE.Set_Token('RCOUNT',l_err_inst_sdtl);
2526 FND_FILE.Put_Line(FND_FILE.Log,FND_MESSAGE.Get);
2527
2528 FND_MESSAGE.Set_Name('IGS','IGS_OR_TOT_REC_WARN');
2529 FND_MESSAGE.Set_Token('RCOUNT',l_warn_inst_sdtl);
2530 FND_FILE.Put_Line(FND_FILE.Log,FND_MESSAGE.Get);
2531 FND_FILE.Put_Line(FND_FILE.Log,'');
2532
2533 FND_FILE.Put_Line(FND_FILE.Log,l_inst_cont_meaning);
2534 FND_MESSAGE.Set_Name('IGS','IGS_AD_TOT_REC_PRC');
2535 FND_MESSAGE.Set_Token('RCOUNT',l_tot_inst_con);
2536 FND_FILE.Put_Line(FND_FILE.Log,FND_MESSAGE.Get);
2537
2538 FND_MESSAGE.Set_Name('IGS','IGS_AD_TOT_REC_SUCC');
2539 FND_MESSAGE.Set_Token('RCOUNT',l_comp_inst_con);
2540 FND_FILE.Put_Line(FND_FILE.Log,FND_MESSAGE.Get);
2541
2542 FND_MESSAGE.Set_Name('IGS','IGS_AD_TOT_REC_FAIL');
2543 FND_MESSAGE.Set_Token('RCOUNT',l_err_inst_con);
2544 FND_FILE.Put_Line(FND_FILE.Log,FND_MESSAGE.Get);
2545
2546 FND_MESSAGE.Set_Name('IGS','IGS_OR_TOT_REC_WARN');
2547 FND_MESSAGE.Set_Token('RCOUNT',l_warn_inst_con);
2548 FND_FILE.Put_Line(FND_FILE.Log,FND_MESSAGE.Get);
2549 FND_FILE.Put_Line(FND_FILE.Log,'');
2550
2551 FND_FILE.Put_Line(FND_FILE.Log,l_inst_cont_phone_meaning);
2552 FND_MESSAGE.Set_Name('IGS','IGS_AD_TOT_REC_PRC');
2553 FND_MESSAGE.Set_Token('RCOUNT',l_tot_inst_cphn);
2554 FND_FILE.Put_Line(FND_FILE.Log,FND_MESSAGE.Get);
2555
2556 FND_MESSAGE.Set_Name('IGS','IGS_AD_TOT_REC_SUCC');
2557 FND_MESSAGE.Set_Token('RCOUNT',l_comp_inst_cphn);
2558 FND_FILE.Put_Line(FND_FILE.Log,FND_MESSAGE.Get);
2559
2560 FND_MESSAGE.Set_Name('IGS','IGS_AD_TOT_REC_FAIL');
2561 FND_MESSAGE.Set_Token('RCOUNT',l_err_inst_cphn);
2562 FND_FILE.Put_Line(FND_FILE.Log,FND_MESSAGE.Get);
2563
2564 FND_MESSAGE.Set_Name('IGS','IGS_OR_TOT_REC_WARN');
2565 FND_MESSAGE.Set_Token('RCOUNT',l_warn_inst_cphn);
2566 FND_FILE.Put_Line(FND_FILE.Log,FND_MESSAGE.Get);
2567 FND_FILE.Put_Line(FND_FILE.Log,'');
2568
2569 FND_FILE.Put_Line(FND_FILE.Log,l_inst_addr_meaning);
2570 FND_MESSAGE.Set_Name('IGS','IGS_AD_TOT_REC_PRC');
2571 FND_MESSAGE.Set_Token('RCOUNT',l_tot_inst_adr);
2572 FND_FILE.Put_Line(FND_FILE.Log,FND_MESSAGE.Get);
2573
2574 FND_MESSAGE.Set_Name('IGS','IGS_AD_TOT_REC_SUCC');
2575 FND_MESSAGE.Set_Token('RCOUNT',l_comp_inst_adr);
2576 FND_FILE.Put_Line(FND_FILE.Log,FND_MESSAGE.Get);
2577
2578 FND_MESSAGE.Set_Name('IGS','IGS_AD_TOT_REC_FAIL');
2579 FND_MESSAGE.Set_Token('RCOUNT',l_err_inst_adr);
2580 FND_FILE.Put_Line(FND_FILE.Log,FND_MESSAGE.Get);
2581
2582 FND_MESSAGE.Set_Name('IGS','IGS_OR_TOT_REC_WARN');
2583 FND_MESSAGE.Set_Token('RCOUNT',l_warn_inst_adr);
2584 FND_FILE.Put_Line(FND_FILE.Log,FND_MESSAGE.Get);
2585 FND_FILE.Put_Line(FND_FILE.Log,'');
2586
2587 FND_FILE.Put_Line(FND_FILE.Log,l_inst_addr_usage_meaning);
2588 FND_MESSAGE.Set_Name('IGS','IGS_AD_TOT_REC_PRC');
2589 FND_MESSAGE.Set_Token('RCOUNT',l_tot_inst_adru);
2590 FND_FILE.Put_Line(FND_FILE.Log,FND_MESSAGE.Get);
2591
2592 FND_MESSAGE.Set_Name('IGS','IGS_AD_TOT_REC_SUCC');
2593 FND_MESSAGE.Set_Token('RCOUNT',l_comp_inst_adru);
2594 FND_FILE.Put_Line(FND_FILE.Log,FND_MESSAGE.Get);
2595
2596 FND_MESSAGE.Set_Name('IGS','IGS_AD_TOT_REC_FAIL');
2597 FND_MESSAGE.Set_Token('RCOUNT',l_err_inst_adru);
2598 FND_FILE.Put_Line(FND_FILE.Log,FND_MESSAGE.Get);
2599
2600 FND_MESSAGE.Set_Name('IGS','IGS_OR_TOT_REC_WARN');
2601 FND_MESSAGE.Set_Token('RCOUNT',l_warn_inst_adru);
2602 FND_FILE.Put_Line(FND_FILE.Log,FND_MESSAGE.Get);
2603 FND_FILE.Put_Line(FND_FILE.Log,'');
2604
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;
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 /*
2622 || Created By : ssaleem
2623 || Created On : 22-SEP-2003
2624 || Purpose : Compares crosswalk inst code with interface table and updates the status accordingly.
2625 || Known limitations, enhancements or remarks :
2626 || Change History :
2627 || Who When What
2628 || (reverse chronological order - newest change first)
2629 */
2630 return_value BOOLEAN;
2631 BEGIN
2632 return_value := TRUE;
2633
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;
2643
2644 RETURN return_value;
2645
2646 END validate_inst_code;
2647
2648 END igs_or_inst_imp_001;
[Home] [Help]