[Home] [Help]
PACKAGE BODY: APPS.IGS_AD_IMP_007
Source
1 PACKAGE BODY IGS_AD_IMP_007 AS
2 /* $Header: IGSAD85B.pls 120.3 2006/02/01 02:30:00 pfotedar noship $ */
3
4 /*
5 || Created By :
6 || Created On :
7 || Purpose : This procedure process the Application
8 || Known limitations, enhancements or remarks :
9 || Change History :
10 || Who When What
11
12 || asbala 12-OCT-2003 Bug 3130316. Import Process Logging Framework Related changes.
13
14 || asbala 28-SEP-2003 Bug 3130316. Import Process Source Category Rule processing changes,
15 lookup caching related changes, and cursor parameterization.
16
17 || pkpatel 25-Jul-2003 3045079 : TRUNC of start_dt for API insert/update
18 || gmuralid 4-DEC-2002 SEVIS BUILD - Changed validation for country in
19 procedure prc_pe_hz_citizenship to validate against fnd territories.
20 Also made calls to the import processes in package igs_ad_imp_026
21 in the procedure prc_pe_intl_dtls
22
23
24 gmuralid 29-NOV-2002 SEVIS BUILD removed procedures prc_pe_visa_pass and prc_pe_fund_dep
25 from both spec and body
26 Also modified validation for country in procedure prc_pe_hz_citizenship
27 gmaheswa 10-NOV-2003 Bug 3223043 HZ.K Impact changes
28 nsidana 6/21/2004 Bug 3541714 : Added validtion to check that the date disowned > date recognized
29 for citizenship details.
30 || gmaheswa 29-Sep-2004 BUG 3787210 Added Closed indicator check for the Alternate Person Id type.
31
32 */
33
34 cst_mi_val_18 CONSTANT VARCHAR2(2) := '18';
35 cst_mi_val_19 CONSTANT VARCHAR2(2) := '19';
36 cst_mi_val_20 CONSTANT VARCHAR2(2) := '20';
37 cst_mi_val_21 CONSTANT VARCHAR2(2) := '21';
38 cst_mi_val_22 CONSTANT VARCHAR2(2) := '22';
39 cst_mi_val_23 CONSTANT VARCHAR2(2) := '23';
40 cst_mi_val_24 CONSTANT VARCHAR2(2) := '24';
41 cst_mi_val_25 CONSTANT VARCHAR2(2) := '25';
42
43 cst_err_val_695 CONSTANT VARCHAR2(4) := 'E695';
44 cst_err_val_14 CONSTANT VARCHAR2(4) := 'E014';
45
46 cst_stat_val_1 CONSTANT VARCHAR2(2) := '1';
47 cst_stat_val_2 CONSTANT VARCHAR2(2) := '2';
48 cst_stat_val_3 CONSTANT VARCHAR2(2) := '3';
49
50
51
52
53 PROCEDURE prc_pe_mltry_dtls (
54 P_SOURCE_TYPE_ID IN NUMBER,
55 P_BATCH_ID IN VARCHAR2
56 ) AS
57 /*
58 || Created By :
59 || Created On :
60 || Purpose : This procedure process the Application
61 || Known limitations, enhancements or remarks :
62 || Change History :
63 || Who When What
64 || npalanis 6-JAN-2003 Bug : 2734697
65 || code added to commit after import of every
66 || 100 records .New variable l_processed_records added
67 || masehgal 04-SEP-2002 # 2512906 Separation type id and corresponding validations added
68 || npalanis 23-JUL-2002 Bug - 2421897
69 || Validate procedure added .
70 || sarakshi 12-Nov-2001 Bug no.2103692:Person Interface DLD
71 || Added the DFF validation before insert/update to the oss table, also in
72 || the call to insert_row/update_row to the oss table adding the dff columns
73 || kumma 23-OCT-2002 Added the parameters for DFF columns to the calls of insert_row and update_row on
74 || igs_pe_hlth_ins_pkg and igs_pe_immu_dtls_pkg, #2608360
75 || kumma 28-OCT-2002 Replaced MILITARY_TYPE_ID with MILITARY_TYPE_CD in validate_military procedure, #2608360
76 || Changed the data type of parameter p_MILITARY_TYPE_CD to VARCHAR2 in procedure CHK_DUP_MILIT
77 || kumma 30-OCT-2002 Added the call to igs_ad_imp_018.validate_desc_flex for new flex fields added in health
78 || insurance and immunization details
79 || pkpatel 6-JAN-2003 Bug No: 2729633
80 || Added the UPPER for all VARCHAR2 fileds. Add additional columns for discrepancy. Add NOT NULL
81 || check for separation type.
82 || (reverse chronological order - newest change first)
83 */
84
85 CURSOR milt_cur(cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE) IS
86 SELECT mi.*,i.person_id
87 FROM igs_ad_military_int_all mi, igs_ad_interface_all i
88 WHERE mi.interface_run_id = cp_interface_run_id
89 AND mi.interface_id = i.interface_id
90 AND mi.interface_run_id = cp_interface_run_id
91 AND mi.status = '2';
92 l_var VARCHAR2(1);
93 l_rowid VARCHAR2(25);
94 l_milt_id NUMBER;
95 p_dup_var BOOLEAN;
96 l_rule VARCHAR2(1);
97 l_error_code VARCHAR2(10);
98 l_status VARCHAR2(10);
99 l_check VARCHAR2(10);
100 l_MILIT_SERVICE_ID igs_pe_mil_services_all.milit_service_id%TYPE;
101 l_processed_records NUMBER(5) := 0 ;
102 l_prog_label VARCHAR2(4000);
103 l_label VARCHAR2(4000);
104 l_debug_str VARCHAR2(4000);
105 l_enable_log VARCHAR2(1);
106 l_request_id NUMBER(10);
107 l_interface_run_id igs_ad_interface_all.interface_run_id%TYPE;
108
109 PROCEDURE crt_pr_mil(
110 MILITARY_REC milt_cur%ROWTYPE ,
111 p_error_code OUT NOCOPY VARCHAR2,
112 p_status OUT NOCOPY VARCHAR2
113 ) AS
114
115 l_org_id NUMBER(15);
116 BEGIN
117
118 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
119
120 IF (l_request_id IS NULL) THEN
121 l_request_id := fnd_global.conc_request_id;
122 END IF;
123
124 l_label := 'igs.plsql.igs_ad_imp_007.crt_pr_mil.begin';
125 l_debug_str := 'Interface military Id : ' || MILITARY_REC.Interface_military_Id;
126
127 fnd_log.string_with_context( fnd_log.level_procedure,
128 l_label,
129 l_debug_str, NULL,
130 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
131 END IF;
132
133 l_org_id := igs_ge_gen_003.get_org_id;
134 IGS_PE_MIL_SERVICES_pkg.INSERT_ROW (
135 X_ROWID => l_rowid,
136 X_Org_Id => l_org_id,
137 x_MILIT_SERVICE_ID => l_milt_id,
138 x_PERSON_ID => MILITARY_REC.PERSON_ID ,
139 x_START_DATE => MILITARY_REC.START_DATE ,
140 x_END_DATE => MILITARY_REC.END_DATE ,
141 x_ATTRIBUTE_CATEGORY => MILITARY_REC.ATTRIBUTE_CATEGORY ,
142 x_ATTRIBUTE1 => MILITARY_REC.ATTRIBUTE1 ,
143 x_ATTRIBUTE2 => MILITARY_REC.ATTRIBUTE2 ,
144 x_ATTRIBUTE3 => MILITARY_REC.ATTRIBUTE3 ,
145 x_ATTRIBUTE4 => MILITARY_REC.ATTRIBUTE4 ,
146 x_ATTRIBUTE5 => MILITARY_REC.ATTRIBUTE5 ,
147 x_ATTRIBUTE6 => MILITARY_REC.ATTRIBUTE6 ,
148 x_ATTRIBUTE7 => MILITARY_REC.ATTRIBUTE7 ,
149 x_ATTRIBUTE8 => MILITARY_REC.ATTRIBUTE8 ,
150 x_ATTRIBUTE9 => MILITARY_REC.ATTRIBUTE9 ,
151 x_ATTRIBUTE10 => MILITARY_REC.ATTRIBUTE10,
152 x_ATTRIBUTE11 => MILITARY_REC.ATTRIBUTE11,
153 x_ATTRIBUTE12 => MILITARY_REC.ATTRIBUTE12,
154 x_ATTRIBUTE13 => MILITARY_REC.ATTRIBUTE13,
155 x_ATTRIBUTE14 => MILITARY_REC.ATTRIBUTE14,
156 x_ATTRIBUTE15 => MILITARY_REC.ATTRIBUTE15,
157 x_ATTRIBUTE16 => MILITARY_REC.ATTRIBUTE16,
158 x_ATTRIBUTE17 => MILITARY_REC.ATTRIBUTE17,
159 x_ATTRIBUTE18 => MILITARY_REC.ATTRIBUTE18,
160 x_ATTRIBUTE19 => MILITARY_REC.ATTRIBUTE19,
161 x_ATTRIBUTE20 => MILITARY_REC.ATTRIBUTE20,
162 x_MILITARY_TYPE_CD => MILITARY_REC.MILITARY_TYPE_CD ,
163 x_SEPARATION_TYPE_CD => MILITARY_REC.SEPARATION_TYPE_CD ,
164 x_ASSISTANCE_TYPE_CD => MILITARY_REC.ASSISTANCE_TYPE_CD ,
165 x_ASSISTANCE_STATUS_CD => MILITARY_REC.ASSISTANCE_STATUS_CD ,
166 X_MODE => 'R'
167 );
168 p_error_Code:= NULL;
169 p_status :='1';
170 EXCEPTION
171 WHEN OTHERS THEN
172 p_error_Code:= 'E322';
173 p_status :='3';
174
175 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
176
177 IF (l_request_id IS NULL) THEN
178 l_request_id := fnd_global.conc_request_id;
179 END IF;
180
181 l_label := 'igs.plsql.igs_ad_imp_007.crt_pr_mil.exception';
182
183 l_debug_str := 'IGS_AD_IMP_007.PRC_PE_MLTRY_DTLS.crt_pr_mil ' ||
184 'Interface_military_Id: ' ||military_rec.Interface_military_Id
185 || 'Status : 3' || 'ErrorCode : E322' || SQLERRM;
186
187 fnd_log.string_with_context( fnd_log.level_exception,
188 l_label,
189 l_debug_str, NULL,
190 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
191 END IF;
192
193 IF l_enable_log = 'Y' THEN
194 igs_ad_imp_001.logerrormessage(military_rec.Interface_military_Id,'E322');
195 END IF;
196
197 END crt_pr_mil;
198
199 PROCEDURE Validate_Military(
200 military_rec IN milt_cur%ROWTYPE ,
201 l_check OUT NOCOPY VARCHAR2
202 ) AS
203
204 CURSOR birth_dt_cur(p_person_id IGS_AD_INTERFACE.PERSON_ID%TYPE) IS
205 SELECT Birth_date
206 FROM IGS_PE_PERSON_BASE_V
207 WHERE person_id= p_person_id;
208
209
210 l_var VARCHAR2(1);
211 l_birth_dt IGS_AD_INTERFACE.BIRTH_DT%TYPE;
212 p_error_code igs_ad_military_int_all.ERROR_CODE%TYPE := NULL;
213
214 BEGIN
215 IF NOT
216 (igs_pe_pers_imp_001.validate_lookup_type_code('PE_MIL_SEV_TYPE',military_rec.military_type_cd,8405))
217 THEN
218 p_error_code := 'E278';
219 l_check := 'TRUE';
220 RAISE NO_DATA_FOUND;
221 END IF;
222
223 IF military_rec.ASSISTANCE_TYPE_CD IS NOT NULL THEN
224 IF NOT
225 (igs_pe_pers_imp_001.validate_lookup_type_code('PE_MIL_ASS_TYPE',military_rec.assistance_type_cd,8405))
226 THEN
227 p_error_code := 'E279';
228 l_check := 'TRUE';
229 RAISE NO_DATA_FOUND;
230 END IF;
231 END IF;
232
233 IF military_rec.ASSISTANCE_STATUS_CD IS NOT NULL THEN
234 IF NOT
235 (igs_pe_pers_imp_001.validate_lookup_type_code('PE_MIL_ASS_STATUS',military_rec.assistance_status_cd,8405))
236 THEN
237 p_error_code := 'E280';
238 l_check := 'TRUE';
239 RAISE NO_DATA_FOUND;
240 END IF;
241 END IF;
242
243 OPEN Birth_dt_cur(military_rec.person_id) ;
244 FETCH Birth_dt_cur INTO l_birth_dt;
245 IF l_birth_dt IS NOT NULL AND l_birth_dt > military_rec.start_date THEN
246 p_error_code := 'E222';
247 CLOSE Birth_dt_cur;
248 l_check := 'TRUE';
249 RAISE NO_DATA_FOUND;
250 ELSE
251 p_error_code := NULL;
252 END IF;
253 CLOSE Birth_dt_cur;
254 IF military_rec.end_date IS NOT NULL THEN
255 IF military_rec.start_date > military_rec.end_date THEN
256 p_error_code := 'E208';
257 l_check := 'TRUE';
258 RAISE NO_DATA_FOUND;
259 END IF;
260 END IF;
261
262 IF military_rec.separation_type_cd IS NOT NULL THEN
263 IF NOT
264 (igs_pe_pers_imp_001.validate_lookup_type_code('PE_MIL_SEP_TYPE',military_rec.separation_type_cd,8405))
265 THEN
266 p_error_code := 'E286';
267 l_check := 'TRUE';
268 RAISE NO_DATA_FOUND;
269 END IF;
270 END IF;
271
272 IF NOT igs_ad_imp_018.validate_desc_flex(
273 p_attribute_category =>MILITARY_REC.attribute_category,
274 p_attribute1 =>MILITARY_REC.attribute1 ,
275 p_attribute2 =>MILITARY_REC.attribute2 ,
276 p_attribute3 =>MILITARY_REC.attribute3 ,
277 p_attribute4 =>MILITARY_REC.attribute4 ,
278 p_attribute5 =>MILITARY_REC.attribute5 ,
279 p_attribute6 =>MILITARY_REC.attribute6 ,
280 p_attribute7 =>MILITARY_REC.attribute7 ,
281 p_attribute8 =>MILITARY_REC.attribute8 ,
282 p_attribute9 =>MILITARY_REC.attribute9 ,
283 p_attribute10 =>MILITARY_REC.attribute10 ,
284 p_attribute11 =>MILITARY_REC.attribute11 ,
285 p_attribute12 =>MILITARY_REC.attribute12 ,
286 p_attribute13 =>MILITARY_REC.attribute13 ,
287 p_attribute14 =>MILITARY_REC.attribute14 ,
288 p_attribute15 =>MILITARY_REC.attribute15 ,
289 p_attribute16 =>MILITARY_REC.attribute16 ,
290 p_attribute17 =>MILITARY_REC.attribute17 ,
291 p_attribute18 =>MILITARY_REC.attribute18 ,
292 p_attribute19 =>MILITARY_REC.attribute19 ,
293 p_attribute20 =>MILITARY_REC.attribute20 ,
294 p_desc_flex_name =>'IGS_PE_MIL_SERVICE_FLEX' ) THEN
295
296 p_error_code := 'E255';
297 l_check := 'TRUE';
298 RAISE NO_DATA_FOUND;
299 END IF;
300 l_check := 'FALSE' ;
301 p_error_code := NULL ;
302
303 EXCEPTION
304 WHEN OTHERS THEN
305 UPDATE igs_ad_military_int_all
306 SET error_code = p_error_code,
307 status = '3'
308 WHERE interface_military_id = military_rec.interface_military_id ;
309
310 IF l_enable_log = 'Y' THEN
311 igs_ad_imp_001.logerrormessage(military_rec.Interface_military_Id,p_error_code);
312 END IF;
313
314 END;
315
316 BEGIN
317 l_interface_run_id := igs_ad_imp_001.g_interface_run_id;
318 l_enable_log := igs_ad_imp_001.g_enable_log;
319 l_prog_label := 'igs.plsql.igs_ad_imp_007.prc_pe_mltry_dtls';
320 l_label := 'igs.plsql.igs_ad_imp_007.prc_pe_mltry_dtls.';
321 l_check := 'FALSE' ;
322
323
324 l_rule :=Igs_Ad_Imp_001.FIND_SOURCE_CAT_RULE(P_SOURCE_TYPE_ID,'PERSON_MILITARY_DETAILS');
325
326 -- If rule is E or I, then if the match_ind is not null, the combination is invalid
327 IF l_rule IN ('E','I') THEN
328 UPDATE igs_ad_military_int_all
329 SET status = cst_stat_val_3,
330 ERROR_CODE = cst_err_val_695 -- Error code depicting incorrect combination
331 WHERE match_ind IS NOT NULL
332 AND interface_run_id = l_interface_run_id
333 AND status = cst_stat_val_2;
334 END IF;
335
336 -- If rule is E and duplicate exists, update match_ind to 19 and status to 1
337 IF l_rule = 'E' THEN
338 UPDATE igs_ad_military_int_all mi
339 SET status = cst_stat_val_1,
340 match_ind = cst_mi_val_19
341 WHERE mi.interface_run_id = l_interface_run_id
342 AND mi.status = cst_stat_val_2
343 AND EXISTS ( SELECT '1'
344 FROM igs_pe_mil_services_all pe, igs_ad_interface_all ii
345 WHERE ii.interface_run_id = l_interface_run_id
346 AND ii.interface_id = mi.interface_id
347 AND ii.person_id = pe.person_id
348 AND pe.military_type_cd = UPPER(mi.military_type_cd)
349 AND TRUNC(pe.start_date) = TRUNC(mi.start_date) );
350 END IF;
351
352 -- If rule is R and there match_ind is 18,19,22 or 23 then the records must have been
353 -- processed in prior runs and didn't get updated .. update to status 1
354 IF l_rule = 'R' THEN
355 UPDATE igs_ad_military_int_all
356 SET status = cst_stat_val_1
357 WHERE interface_run_id = l_interface_run_id
358 AND match_ind IN (cst_mi_val_18,cst_mi_val_19,cst_mi_val_22,cst_mi_val_23)
359 AND status = cst_stat_val_2;
360 END IF;
361
362 -- If rule is R and match_ind is neither 21 nor 25 then error
363 IF l_rule = 'R' THEN
364 UPDATE igs_ad_military_int_all
365 SET status = cst_stat_val_3,
366 ERROR_CODE = cst_err_val_695
367 WHERE interface_run_id = l_interface_run_id
368 AND (match_ind IS NOT NULL AND match_ind NOT IN (cst_mi_val_21,cst_mi_val_25))
369 AND status = cst_stat_val_2;
370 END IF;
371
372 -- If rule is R, set duplicated records with no discrepancy to status 1 and match_ind 23
373 IF l_rule = 'R' THEN
374 UPDATE igs_ad_military_int_all mi
375 SET status = cst_stat_val_1,
376 match_ind = cst_mi_val_23
377 WHERE mi.interface_run_id = l_interface_run_id
378 AND mi.match_ind IS NULL
379 AND mi.status = cst_stat_val_2
380 AND EXISTS ( SELECT '1'
381 FROM igs_pe_mil_services_all pe, igs_ad_interface_all ii
382 WHERE ii.interface_run_id = l_interface_run_id
383 AND ii.interface_id = mi.interface_id
384 AND ii.person_id = pe.person_id
385 AND pe.military_type_cd = UPPER(mi.military_type_cd)
386 AND TRUNC(pe.start_date) = TRUNC(mi.start_date)
387 AND NVL(TRUNC(pe.end_date),igs_ge_date.igsdate('9999/01/01'))=NVL(TRUNC(mi.end_date),igs_ge_date.igsdate('9999/01/01'))
388 AND NVL(UPPER(pe.assistance_type_cd),'*!*')= NVL(UPPER(mi.assistance_type_cd),'*!*')
389 AND NVL(UPPER(pe.assistance_status_cd),'*!*') = NVL(UPPER(mi.assistance_status_cd),'*!*')
390 AND NVL(UPPER(pe.separation_type_cd),'*!*') = NVL(UPPER(mi.separation_type_cd),'*!*')
391 AND NVL(pe.attribute1,'*!*') = NVL(mi.attribute1,'*!*')
392 AND NVL(pe.attribute2,'*!*') = NVL(mi.attribute2,'*!*')
393 AND NVL(pe.attribute3,'*!*') = NVL(mi.attribute3,'*!*')
394 AND NVL(pe.attribute4,'*!*') = NVL(mi.attribute4,'*!*')
395 AND NVL(pe.attribute5,'*!*') = NVL(mi.attribute5,'*!*')
396 AND NVL(pe.attribute6,'*!*') = NVL(mi.attribute6,'*!*')
397 AND NVL(pe.attribute7,'*!*') = NVL(mi.attribute7,'*!*')
398 AND NVL(pe.attribute8,'*!*') = NVL(mi.attribute8,'*!*')
399 AND NVL(pe.attribute9,'*!*') = NVL(mi.attribute9,'*!*')
400 AND NVL(pe.attribute10,'*!*') = NVL(mi.attribute10,'*!*')
401 AND NVL(pe.attribute11,'*!*') = NVL(mi.attribute11,'*!*')
402 AND NVL(pe.attribute12,'*!*') = NVL(mi.attribute12,'*!*')
403 AND NVL(pe.attribute13,'*!*') = NVL(mi.attribute13,'*!*')
404 AND NVL(pe.attribute14,'*!*') = NVL(mi.attribute14,'*!*')
405 AND NVL(pe.attribute15,'*!*') = NVL(mi.attribute15,'*!*')
406 AND NVL(pe.attribute16,'*!*') = NVL(mi.attribute16,'*!*')
407 AND NVL(pe.attribute17,'*!*') = NVL(mi.attribute17,'*!*')
408 AND NVL(pe.attribute18,'*!*') = NVL(mi.attribute18,'*!*')
409 AND NVL(pe.attribute19,'*!*') = NVL(mi.attribute19,'*!*')
410 AND NVL(pe.attribute20,'*!*') = NVL(mi.attribute20,'*!*')
411 );
412 END IF;
413 -- If rule in R records still exist, they are duplicates and have discrepancy .. update status=3,match_ind=20
414 IF l_rule = 'R' THEN
415 UPDATE igs_ad_military_int_all mi
416 SET status = cst_stat_val_3,
417 match_ind = cst_mi_val_20,
418 dup_milit_service_id = (SELECT milit_service_id
419 FROM igs_pe_mil_services_all pe, igs_ad_interface_all ii
420 WHERE mi.interface_run_id = l_interface_run_id
421 AND ii.interface_id = mi.interface_id
422 AND ii.person_id = pe.person_id
423 AND pe.military_type_cd = UPPER(mi.military_type_cd)
424 AND TRUNC(pe.start_date) = TRUNC(mi.start_date))
425 WHERE mi.interface_run_id = l_interface_run_id
426 AND mi.match_ind IS NULL
427 AND mi.status = cst_stat_val_2
428 AND EXISTS (SELECT '1'
429 FROM igs_pe_mil_services_all pe, igs_ad_interface_all ii
430 WHERE ii.interface_run_id = l_interface_run_id
431 AND ii.interface_id = mi.interface_id
432 AND ii.person_id = pe.person_id
433 AND pe.military_type_cd = UPPER(mi.military_type_cd)
434 AND TRUNC(pe.start_date) = TRUNC(mi.start_date));
435 END IF;
436
437 FOR military_rec IN milt_cur(l_interface_run_id) LOOP
438 l_processed_records := l_processed_records + 1 ;
439
440 l_MILIT_SERVICE_ID := NULL;
441 MILITARY_REC.START_DATE := TRUNC(MILITARY_REC.START_DATE);
442 MILITARY_REC.END_DATE := TRUNC(MILITARY_REC.END_DATE);
443 military_rec.separation_type_cd := UPPER(military_rec.separation_type_cd);
444 military_rec.military_type_cd := UPPER(military_rec.military_type_cd);
445 military_rec.assistance_type_cd := UPPER(military_rec.assistance_type_cd);
446 military_rec.assistance_status_cd := UPPER(military_rec.assistance_status_cd);
447
448 l_check := 'FALSE';
449 Validate_military(military_rec, l_check);
450
451 IF l_check = 'FALSE' THEN
452 DECLARE
453 CURSOR chk_dup_milit(cp_military_type_cd VARCHAR2,
454 cp_person_id VARCHAR2,
455 cp_start_date igs_ad_military_int_all.start_date%TYPE) IS
456 SELECT rowid,mi.*
457 FROM igs_pe_mil_services mi
458 WHERE UPPER(military_type_cd) = UPPER(cp_military_type_cd)
459 AND person_id = cp_person_id
460 AND TRUNC(start_date) = TRUNC(cp_start_date);
461 dup_milit_rec chk_dup_milit%ROWTYPE;
462 BEGIN
463 OPEN chk_dup_milit(military_rec.military_type_cd,
464 military_rec.person_id ,
465 military_rec.start_date);
466 FETCH chk_dup_milit INTO dup_milit_rec;
467 CLOSE chk_dup_milit;
468 IF dup_milit_rec.military_type_cd IS NOT NULL THEN
469 IF l_rule = 'I' THEN
470 BEGIN
471 igs_pe_mil_services_pkg.update_row(
472 x_rowid=> dup_milit_rec.rowid,
473 x_milit_service_id=> dup_milit_rec.milit_service_id,
474 x_person_id=> dup_milit_rec.person_id,
475 x_start_date=> NVL(TRUNC(military_rec.start_date),dup_milit_rec.start_date),
476 x_end_date=> NVL(TRUNC(military_rec.end_date),dup_milit_rec.end_date),
477 x_attribute_category=>NVL(military_rec.attribute_category,dup_milit_rec.attribute_category),
478 x_attribute1=>NVL(military_rec.attribute1,dup_milit_rec.attribute1),
479 x_attribute2=>NVL(military_rec.attribute2, dup_milit_rec.attribute2),
480 x_attribute3=>NVL(military_rec.attribute3,dup_milit_rec.attribute3),
481 x_attribute4=>NVL(military_rec.attribute4,dup_milit_rec.attribute4),
482 x_attribute5=>NVL(military_rec.attribute5,dup_milit_rec.attribute5),
483 x_attribute6=>NVL(military_rec.attribute6,dup_milit_rec.attribute6),
484 x_attribute7=>NVL(military_rec.attribute7,dup_milit_rec.attribute7),
485 x_attribute8=>NVL(military_rec.attribute8,dup_milit_rec.attribute8),
486 x_attribute9=>NVL(military_rec.attribute9,dup_milit_rec.attribute9),
487 x_attribute10=>NVL(military_rec.attribute10,dup_milit_rec.attribute10),
488 x_attribute11=>NVL(military_rec.attribute11,dup_milit_rec.attribute11),
489 x_attribute12=>NVL(military_rec.attribute12,dup_milit_rec.attribute12),
490 x_attribute13=>NVL(military_rec.attribute13,dup_milit_rec.attribute13),
491 x_attribute14=>NVL(military_rec.attribute14,dup_milit_rec.attribute14),
492 x_attribute15=>NVL(military_rec.attribute15,dup_milit_rec.attribute15),
493 x_attribute16=>NVL(military_rec.attribute16,dup_milit_rec.attribute16),
494 x_attribute17=>NVL(military_rec.attribute17,dup_milit_rec.attribute17),
495 x_attribute18=>NVL(military_rec.attribute18,dup_milit_rec.attribute18),
496 x_attribute19=>NVL(military_rec.attribute19,dup_milit_rec.attribute19),
497 x_attribute20=> NVL(military_rec.attribute20,dup_milit_rec.attribute20),
498 x_military_type_cd=>NVL(military_rec.military_type_cd,dup_milit_rec.military_type_cd),
499 x_separation_type_cd=>NVL(military_rec.separation_type_cd,dup_milit_rec.separation_type_cd),
500 x_assistance_type_cd=> NVL(military_rec.assistance_type_cd,dup_milit_rec.assistance_type_cd),
501 x_assistance_status_cd => NVL(military_rec.assistance_status_cd,dup_milit_rec.assistance_status_cd),
502 x_mode=>'R');
503 UPDATE igs_ad_military_int_all
504 SET match_ind =cst_mi_val_18,
505 status = cst_stat_val_1
506 WHERE interface_military_id = military_rec.interface_military_id;
507 EXCEPTION
508 WHEN OTHERS THEN
509 UPDATE igs_ad_military_int_all
510 SET ERROR_CODE = cst_err_val_14,
511 status = cst_stat_val_3
512 WHERE interface_military_id= military_rec.interface_military_id;
513
514 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
515
516 IF (l_request_id IS NULL) THEN
517 l_request_id := fnd_global.conc_request_id;
518 END IF;
519
520 l_label := 'igs.plsql.igs_ad_imp_007.prc_pe_mltry_dtls.exception: '|| 'E014';
521
522 l_debug_str := 'IGS_AD_IMP_007.PRC_PE_MLTRY_DTLS ' ||
523 'Interface Military Id : ' || (MILITARY_REC.INTERFACE_MILITARY_ID)
524 || 'Status : 3' || 'ErrorCode : E014' || SQLERRM;
525
526 fnd_log.string_with_context( fnd_log.level_exception,
527 l_label,
528 l_debug_str, NULL,
529 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
530 END IF;
531
532 IF l_enable_log = 'Y' THEN
533 igs_ad_imp_001.logerrormessage(MILITARY_REC.INTERFACE_MILITARY_ID,'E014');
534 END IF;
535
536 END;
537 ELSIF l_rule = 'R' THEN
538 IF MILITARY_REC.match_ind = cst_mi_val_21 THEN
539 BEGIN
540 igs_pe_mil_services_pkg.update_row(
541 x_rowid=> dup_milit_rec.rowid,
542 x_milit_service_id=> dup_milit_rec.milit_service_id,
543 x_person_id=>dup_milit_rec.person_id,
544 x_start_date=> NVL(TRUNC(military_rec.start_date),dup_milit_rec.start_date),
545 x_end_date=> NVL(TRUNC(military_rec.end_date),dup_milit_rec.end_date),
546 x_attribute_category=>NVL(military_rec.attribute_category,dup_milit_rec.attribute_category),
547 x_attribute1=>NVL(military_rec.attribute1,dup_milit_rec.attribute1),
548 x_attribute2=>NVL(military_rec.attribute2, dup_milit_rec.attribute2),
549 x_attribute3=>NVL(military_rec.attribute3,dup_milit_rec.attribute3),
550 x_attribute4=>NVL(military_rec.attribute4,dup_milit_rec.attribute4),
551 x_attribute5=>NVL(military_rec.attribute5,dup_milit_rec.attribute5),
552 x_attribute6=>NVL(military_rec.attribute6,dup_milit_rec.attribute6),
553 x_attribute7=>NVL(military_rec.attribute7,dup_milit_rec.attribute7),
554 x_attribute8=>NVL(military_rec.attribute8,dup_milit_rec.attribute8),
555 x_attribute9=>NVL(military_rec.attribute9,dup_milit_rec.attribute9),
556 x_attribute10=>NVL(military_rec.attribute10,dup_milit_rec.attribute10),
557 x_attribute11=>NVL(military_rec.attribute11,dup_milit_rec.attribute11),
558 x_attribute12=>NVL(military_rec.attribute12,dup_milit_rec.attribute12),
559 x_attribute13=>NVL(military_rec.attribute13,dup_milit_rec.attribute13),
560 x_attribute14=>NVL(military_rec.attribute14,dup_milit_rec.attribute14),
561 x_attribute15=>NVL(military_rec.attribute15,dup_milit_rec.attribute15),
562 x_attribute16=>NVL(military_rec.attribute16,dup_milit_rec.attribute16),
563 x_attribute17=>NVL(military_rec.attribute17,dup_milit_rec.attribute17),
564 x_attribute18=>NVL(military_rec.attribute18,dup_milit_rec.attribute18),
565 x_attribute19=>NVL(military_rec.attribute19,dup_milit_rec.attribute19),
566 x_attribute20=> NVL(military_rec.attribute20,dup_milit_rec.attribute20),
567 x_military_type_cd=>NVL(military_rec.military_type_cd,dup_milit_rec.military_type_cd),
568 x_separation_type_cd=>NVL(military_rec.separation_type_cd,dup_milit_rec.separation_type_cd),
569 x_assistance_type_cd=> NVL(military_rec.assistance_type_cd,dup_milit_rec.assistance_type_cd),
570 x_assistance_status_cd => NVL(military_rec.assistance_status_cd,dup_milit_rec.assistance_status_cd),
571 x_mode=>'R');
572
573 UPDATE igs_ad_military_int_all
574 SET match_ind =cst_mi_val_18,
575 status = cst_stat_val_1
576 WHERE interface_military_id = military_rec.interface_military_id;
577
578 EXCEPTION
579 WHEN OTHERS THEN
580 UPDATE igs_ad_military_int_all
581 SET ERROR_CODE = 'E014',
582 status = '3'
583 WHERE interface_military_id = military_rec.interface_military_id;
584
585 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
586
587 IF (l_request_id IS NULL) THEN
588 l_request_id := fnd_global.conc_request_id;
589 END IF;
590
591 l_label := 'igs.plsql.igs_ad_imp_007.prc_pe_mltry_dtls.exception: '|| 'E014';
592
593 l_debug_str := 'IGS_AD_IMP_007.PRC_PE_MLTRY_DTLS ' ||
594 'Military Type Cd : ' || MILITARY_REC.MILITARY_TYPE_CD
595 || ' Status : 3 ' || 'ErrorCode : E014' || SQLERRM;
596
597 fnd_log.string_with_context( fnd_log.level_exception,
598 l_label,
599 l_debug_str, NULL,
600 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
601 END IF;
602
603 IF l_enable_log = 'Y' THEN
604 igs_ad_imp_001.logerrormessage(MILITARY_REC.INTERFACE_MILITARY_ID,'E014');
605 END IF;
606
607 END;
608 END IF;
609 END IF;
610 ELSE
611 crt_pr_mil(MILITARY_REC,
612 l_error_code,
613 l_status );
614 UPDATE igs_ad_military_int_all
615 SET status = l_status,
616 ERROR_CODE = l_error_code
617 WHERE interface_military_id= military_rec.interface_military_id;
618 END IF;
619 END;
620 END IF;
621
622 IF l_processed_records = 100 THEN
623 COMMIT;
624 l_processed_records := 0;
625 END IF;
626
627 END LOOP;
628 END prc_pe_mltry_dtls;
629
630 PROCEDURE prc_pe_immu_dtls
631 ( P_SOURCE_TYPE_ID IN NUMBER,
632 P_BATCH_ID IN NUMBER
633 )
634 AS
635 /*
636 || Created By : adhawan
637 || Created On :19-nov-2001F
638 || Purpose : This procedure process the Immunization Details
639 || Known limitations, enhancements or remarks :
640 || Change History :
641 || Who When What
642 || npalanis 6-JAN-2003 Bug : 2734697
643 || code added to commit after import of every
644 || 100 records .New variable l_processed_records added
645 || npalanis 25-JUL-2002 Bug - 2425734
646 || validation for start date cannot be less than birth date of person added
647 || adhawan 12-Nov-2001 Bug no.2103692:Person Interface DLD
648 || New procedure created for processing the immunization details of the person
649 ||
650 || (reverse chronological order - newest change first)
651 */
652 CURSOR c_immu_dtls_cur(cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE) IS
653 SELECT ai.*, i.person_id
654 FROM igs_pe_immu_dtl_int ai, igs_ad_interface_all i
655 WHERE ai.interface_run_id = cp_interface_run_id
656 AND ai.interface_id = i.interface_id
657 AND ai.interface_run_id = cp_interface_run_id
658 AND ai.status = '2';
659
660 CURSOR dup_chk_health_cur(cp_person_id igs_pe_immu_dtls.person_id%TYPE,
661 cp_immu_code igs_pe_immu_dtls.immunization_code%TYPE,
662 cp_start_date igs_pe_immu_dtls.start_date%TYPE ) IS
663 SELECT ROWID, mi.*
664 FROM igs_pe_immu_dtls mi
665 WHERE person_id =cp_person_id
666 AND immunization_code = cp_immu_code
667 AND TRUNC(start_date) =TRUNC(cp_start_date);
668
669 dup_chk_health_rec dup_chk_health_cur%ROWTYPE;
670 health_insur_rec c_immu_dtls_cur%ROWTYPE;
671 l_dup_var BOOLEAN;
672 l_immu_details_id igs_pe_immu_dtls.immu_details_id%TYPE;
673 l_var VARCHAR2(1);
674 l_rule VARCHAR2(1);
675 l_error_code igs_pe_immu_dtl_int.error_code%TYPE;
676 l_status igs_pe_immu_dtl_int.status%TYPE;
677 l_count NUMBER(10);
678 lv_rowid VARCHAR2(25);
679 l_processed_records NUMBER(5) := 0;
680 l_prog_label VARCHAR2(4000);
681 l_label VARCHAR2(4000);
682 l_debug_str VARCHAR2(4000);
683 l_enable_log VARCHAR2(1);
684 l_request_id NUMBER(10);
685 l_interface_run_id igs_ad_interface_all.interface_run_id%TYPE;
686
687 PROCEDURE validate_record_health(p_health_insur_rec IN c_immu_dtls_cur%ROWTYPE,
688 p_error_code OUT NOCOPY VARCHAR2)
689 AS
690 /*
691 || Created By : adhawan
692 || Created On :19-nov-2001
693 || Purpose : This procedure process the Immunization Details
694 || Known limitations, enhancements or remarks :
695 || Change History :
696 || Who When What
697 || adhawan 12-Nov-2001 Bug no.2103692:Person Interface DLD
698 || New procedure created for processing the validations for immunization
699 || details of the person
700 ||
701 || (reverse chronological order - newest change first)
702 */
703 CURSOR birth_dt_cur(p_person_id IGS_AD_INTERFACE.PERSON_ID%TYPE) IS
704 SELECT birth_date
705 FROM igs_pe_person_base_v
706 WHERE person_id = p_person_id;
707
708 l_birth_date igs_ad_interface.birth_dt%TYPE;
709 l_rec VARCHAR2(1);
710 TYPE Validatecur IS REF CURSOR;
711 Validate_cur Validatecur;
712
713
714 BEGIN
715 -- Call Log header
716 -- Perform validations for the following columns. If any validation fails further comparisions
717 -- do not happen i.e. first validation failure returns the control back without executing other
718 -- subsequent validations. The error_code field is updated with the corresponding error code.
719 --Immunization code
720 -- modified to new lookup igs_lookup_values by gmuralid
721 IF NOT
722 (igs_pe_pers_imp_001.validate_lookup_type_code('PE_IMM_TYPE',p_health_insur_rec.immunization_code,8405))
723 THEN
724 p_error_code := 'E156';
725 RAISE no_data_found;
726 ELSE
727 p_error_code := NULL;
728 END IF;
729 --Status code
730 --Cursor modified by gmuralid by migrating to new look up igs_lookups_view
731
732 IF
733 (igs_pe_pers_imp_001.validate_lookup_type_code('PE_IMM_STATUS',p_health_insur_rec.status_code,8405))
734 THEN
735 p_error_code := NULL;
736 ELSE
737 p_error_code := 'E157'; -- Status code Validation Failed
738 RAISE no_data_found;
739 END IF;
740
741 OPEN birth_dt_cur(p_health_insur_rec.person_id);
742 FETCH birth_dt_cur INTO l_birth_date;
743 IF l_birth_date IS NOT NULL AND l_birth_date > p_health_insur_rec.start_date THEN
744 p_error_code := 'E222';
745 CLOSE birth_dt_cur;
746 RAISE NO_DATA_FOUND;
747 ELSE
748 p_error_code := NULL;
749 END IF;
750 CLOSE birth_dt_cur;
751
752 --Start date and End Date validation
753 IF p_health_insur_rec.start_date <= NVL(p_health_insur_rec.end_date,IGS_GE_DATE.IGSDATE('4712/12/31')) THEN
754 p_error_code := NULL;
755 ELSE
756 p_error_code := 'E158'; -- Start Date and End Date Validation Failed
757 RAISE no_data_found;
758 END IF;
759
760 EXCEPTION
761 WHEN NO_DATA_FOUND THEN
762 -- Validation Unsuccessful
763 UPDATE igs_pe_immu_dtl_int
764 SET status = '3',
765 error_code = p_error_code
766 WHERE interface_immu_dtls_id = p_health_insur_rec.interface_immu_dtls_id;
767
768 IF l_enable_log = 'Y' THEN
769 igs_ad_imp_001.logerrormessage(p_health_insur_rec.interface_immu_dtls_id,p_error_code,'IGS_PE_IMMU_DTL_INT');
770 END IF;
771
772 END validate_record_health; -- End of Local Procedure validate_record_health
773
774
775 PROCEDURE crt_health_ins (
776 health_insur_rec IN c_immu_dtls_cur%ROWTYPE,
777 p_error_code OUT NOCOPY VARCHAR2,
778 p_status OUT NOCOPY VARCHAR2 )
779 AS
780 /*
781 || Created By : adhawan
782 || Created On :19-nov-2001
783 || Purpose : This procedure process the Immunization Details
784 || Known limitations, enhancements or remarks :
785 || Change History :
786 || Who When What
787 || adhawan 12-Nov-2001 Bug no.2103692:Person Interface DLD
788 || New procedure created for processing the immunization details of the person
789 || for creation of records in the OSS table
790 ||
791 || (reverse chronological order - newest change first)
792 */
793 l_dummy VARCHAR2(1);
794 l_rowid VARCHAR2(25);
795 l_immu_id igs_pe_immu_dtls.immu_details_id%TYPE;
796
797 BEGIN
798
799 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
800
801 IF (l_request_id IS NULL) THEN
802 l_request_id := fnd_global.conc_request_id;
803 END IF;
804
805 l_label := 'igs.plsql.igs_ad_imp_007.crt_health_ins.begin';
806 l_debug_str := 'INTERFACE_IMMU_DTLS_ID:'||health_insur_rec.INTERFACE_IMMU_DTLS_ID;
807
808 fnd_log.string_with_context( fnd_log.level_procedure,
809 l_label,
810 l_debug_str, NULL,
811 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
812 END IF;
813
814 --validate record values
815 validate_record_health (health_insur_rec, l_error_code);
816 IF l_error_code IS NULL THEN
817 IF NOT igs_ad_imp_018.validate_desc_flex(
818 p_attribute_category => health_insur_rec.attribute_category,
819 p_attribute1 => health_insur_rec.attribute1 ,
820 p_attribute2 => health_insur_rec.attribute2 ,
821 p_attribute3 => health_insur_rec.attribute3 ,
822 p_attribute4 => health_insur_rec.attribute4 ,
823 p_attribute5 => health_insur_rec.attribute5 ,
824 p_attribute6 => health_insur_rec.attribute6 ,
825 p_attribute7 => health_insur_rec.attribute7 ,
826 p_attribute8 => health_insur_rec.attribute8 ,
827 p_attribute9 => health_insur_rec.attribute9 ,
828 p_attribute10 => health_insur_rec.attribute10 ,
829 p_attribute11 => health_insur_rec.attribute11 ,
830 p_attribute12 => health_insur_rec.attribute12 ,
831 p_attribute13 => health_insur_rec.attribute13 ,
832 p_attribute14 => health_insur_rec.attribute14 ,
833 p_attribute15 => health_insur_rec.attribute15 ,
834 p_attribute16 => health_insur_rec.attribute16 ,
835 p_attribute17 => health_insur_rec.attribute17 ,
836 p_attribute18 => health_insur_rec.attribute18 ,
837 p_attribute19 => health_insur_rec.attribute19 ,
838 p_attribute20 => health_insur_rec.attribute20 ,
839 p_desc_flex_name => 'IGS_PE_IMMU_DTLS_FLEX' ) THEN
840
841 p_status:='3';
842 p_error_code:='E255';
843 IF l_enable_log = 'Y' THEN
844 igs_ad_imp_001.logerrormessage(health_insur_rec.INTERFACE_IMMU_DTLS_ID,'E255','IGS_PE_IMMU_DTL_INT');
845 END IF;
846 ELSE
847 igs_pe_immu_dtls_pkg.INSERT_ROW (
848 x_rowid => l_rowid,
849 x_immu_details_id => l_immu_details_id,
850 x_person_id => health_insur_rec.person_id,
851 x_immunization_code => health_insur_rec.immunization_code,
852 x_status_code => health_insur_rec.status_code,
853 x_start_date => health_insur_rec.start_date,
854 x_end_date => health_insur_rec.end_date,
855 X_ATTRIBUTE_CATEGORY => health_insur_rec.ATTRIBUTE_CATEGORY,
856 X_ATTRIBUTE1 => health_insur_rec.ATTRIBUTE1,
857 X_ATTRIBUTE2 => health_insur_rec.ATTRIBUTE2,
858 X_ATTRIBUTE3 => health_insur_rec.ATTRIBUTE3,
859 X_ATTRIBUTE4 => health_insur_rec.ATTRIBUTE4,
860 X_ATTRIBUTE5 => health_insur_rec.ATTRIBUTE5,
861 X_ATTRIBUTE6 => health_insur_rec.ATTRIBUTE6,
862 X_ATTRIBUTE7 => health_insur_rec.ATTRIBUTE7,
863 X_ATTRIBUTE8 => health_insur_rec.ATTRIBUTE8,
864 X_ATTRIBUTE9 => health_insur_rec.ATTRIBUTE9,
865 X_ATTRIBUTE10 => health_insur_rec.ATTRIBUTE10,
866 X_ATTRIBUTE11 => health_insur_rec.ATTRIBUTE11,
867 X_ATTRIBUTE12 => health_insur_rec.ATTRIBUTE12,
868 X_ATTRIBUTE13 => health_insur_rec.ATTRIBUTE13,
869 X_ATTRIBUTE14 => health_insur_rec.ATTRIBUTE14,
870 X_ATTRIBUTE15 => health_insur_rec.ATTRIBUTE15,
871 X_ATTRIBUTE16 => health_insur_rec.ATTRIBUTE16,
872 X_ATTRIBUTE17 => health_insur_rec.ATTRIBUTE17,
873 X_ATTRIBUTE18 => health_insur_rec.ATTRIBUTE18,
874 X_ATTRIBUTE19 => health_insur_rec.ATTRIBUTE19,
875 X_ATTRIBUTE20 => health_insur_rec.ATTRIBUTE20,
876 x_MODE => 'R');
877
878 p_error_code := NULL;
879 p_status := '1';
880
881 UPDATE igs_pe_immu_dtl_int
882 SET status = '1',
883 ERROR_CODE = p_error_code
884 WHERE interface_immu_dtls_id = health_insur_rec.interface_immu_dtls_id;
885 END IF;
886 END IF;
887 EXCEPTION
888 WHEN OTHERS THEN
889 p_STATUS := '3';
890 p_ERROR_CODE := 'E159';
891
892 UPDATE igs_pe_immu_dtl_int
893 SET status = p_status,
894 ERROR_CODE = p_error_code
895 WHERE interface_immu_dtls_id = health_insur_rec.interface_immu_dtls_id;
896 -- Call Log detail
897
898 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
899
900 IF (l_request_id IS NULL) THEN
901 l_request_id := fnd_global.conc_request_id;
902 END IF;
903
904 l_label := 'igs.plsql.igs_ad_imp_007.crt_health_ins.exception';
905
906 l_debug_str := 'Igs_Ad_Imp_007.PRC_PE_IMMU_DLTS.CRT_HEALTH_INS'
907 || ' Exception from IGS_PE_IMMU_DTLS_PKG.INSERT_ROW '
908 || ' Interface Id : '
909 || (health_insur_rec.interface_immu_dtls_id)
910 || ' Status : 3'|| ' ErrorCode : E159' ||SQLERRM;
911
912 fnd_log.string_with_context( fnd_log.level_exception,
913 l_label,
914 l_debug_str, NULL,
915 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
916 END IF;
917
918 IF l_enable_log = 'Y' THEN
919 igs_ad_imp_001.logerrormessage(health_insur_rec.interface_immu_dtls_id,'E159','IGS_PE_IMMU_DTL_INT');
920 END IF;
921
922 END crt_health_ins;
923
924 BEGIN
925 -- Initialize variables for logging (as per logging framework)
926 l_interface_run_id := igs_ad_imp_001.g_interface_run_id;
927 l_enable_log := igs_ad_imp_001.g_enable_log;
928 l_prog_label := 'igs.plsql.igs_ad_imp_007.prc_pe_immu_dtls';
929 l_label := 'igs.plsql.igs_ad_imp_007.prc_pe_immu_dtls.';
930
931 -- Pick up all the records in the table for the P_INTERFACE_ID and
932 -- store them into a Record variable pe_health_rec.
933 -- Perform validations for the columns
934 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
935
936 IF (l_request_id IS NULL) THEN
937 l_request_id := fnd_global.conc_request_id;
938 END IF;
939
940 l_label := 'igs.plsql.igs_ad_imp_007.prc_pe_immu_dtls.begin';
941 l_debug_str := 'igs_ad_imp_007.prc_pe_immu_dtls.begin';
942
943 fnd_log.string_with_context( fnd_log.level_procedure,
944 l_label,
945 l_debug_str, NULL,
946 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
947 END IF;
948
949 l_rule := igs_ad_imp_001.find_source_cat_rule(
950 p_source_type_id => P_SOURCE_TYPE_ID,
951 p_category => 'PERSON_HEALTH_INSURANCE');
952
953 -- If rule is E or I, then if the match_ind is not null, the combination is invalid
954 IF l_rule IN ('E','I') THEN
955 UPDATE igs_pe_immu_dtl_int
956 SET status = cst_stat_val_3,
957 ERROR_CODE = cst_err_val_695 -- Error code depicting incorrect combination
958 WHERE match_ind IS NOT NULL
959 AND status = cst_stat_val_2
960 AND interface_run_id = l_interface_run_id;
961 END IF;
962
963 -- If rule is E and duplicate exists, update match_ind to 19 and status to 1
964 IF l_rule = 'E' THEN
965 UPDATE igs_pe_immu_dtl_int mi
966 SET status = cst_stat_val_1,
967 match_ind = cst_mi_val_19
968 WHERE mi.interface_run_id = l_interface_run_id
969 AND mi.status = cst_stat_val_2
970 AND EXISTS ( SELECT '1'
971 FROM igs_pe_immu_dtls pe, igs_ad_interface_all ii
972 WHERE ii.interface_run_id = l_interface_run_id
973 AND ii.interface_id = mi.interface_id
974 AND ii.person_id = pe.person_id
975 AND pe.immunization_code = UPPER(mi.immunization_code)
976 AND TRUNC(pe.start_date) = TRUNC(mi.start_date));
977 END IF;
978
979 -- If rule is R and there match_ind is 18,19,22 or 23 then the records must have been
980 -- processed in prior runs and didn't get updated .. update to status 1
981 IF l_rule = 'R' THEN
982 UPDATE igs_pe_immu_dtl_int
983 SET status = cst_stat_val_1
984 WHERE interface_run_id = l_interface_run_id
985 AND match_ind IN (cst_mi_val_18,cst_mi_val_19,cst_mi_val_22,cst_mi_val_23)
986 AND status = cst_stat_val_2;
987 END IF;
988
989 -- If rule is R and match_ind is neither 21 nor 25 then error
990 IF l_rule = 'R' THEN
991 UPDATE igs_pe_immu_dtl_int
992 SET status = cst_stat_val_3,
993 ERROR_CODE = cst_err_val_695
994 WHERE interface_run_id = l_interface_run_id
995 AND status = cst_stat_val_2
996 AND (match_ind IS NOT NULL AND match_ind NOT IN (cst_mi_val_21,cst_mi_val_25));
997 END IF;
998
999 -- If rule is R, set duplicated records with no discrepancy to status 1 and match_ind 23
1000 IF l_rule = 'R' THEN
1001 UPDATE igs_pe_immu_dtl_int mi
1002 SET status = cst_stat_val_1,
1003 match_ind = cst_mi_val_23
1004 WHERE mi.interface_run_id = l_interface_run_id
1005 AND mi.match_ind IS NULL
1006 AND mi.status = cst_stat_val_2
1007 AND EXISTS ( SELECT '1'
1008 FROM igs_pe_immu_dtls pe, igs_ad_interface_all ii
1009 WHERE ii.interface_run_id = l_interface_run_id
1010 AND ii.interface_id = mi.interface_id
1011 AND ii.person_id = pe.person_id
1012 AND pe.immunization_code = UPPER(mi.immunization_code)
1013 AND pe.status_code = UPPER(mi.status_code)
1014 AND TRUNC(pe.start_date) = TRUNC(mi.start_date)
1015 AND NVL(TRUNC(pe.end_date),igs_ge_date.igsdate('9999/01/01')) = NVL(TRUNC(mi.end_date),igs_ge_date.igsdate('9999/01/01'))
1016 );
1017 END IF;
1018
1019 -- If rule is R records still exist, they are duplicates and have discrepancy .. update status=3,match_ind=20
1020 IF l_rule = 'R' THEN
1021 UPDATE igs_pe_immu_dtl_int mi
1022 SET status = cst_stat_val_3,
1023 match_ind = cst_mi_val_20,
1024 dup_immu_details_id = (SELECT pe.immu_details_id
1025 FROM igs_pe_immu_dtls pe, igs_ad_interface_all ii
1026 WHERE mi.interface_run_id = l_interface_run_id
1027 AND ii.interface_id = mi.interface_id
1028 AND ii.person_id = pe.person_id
1029 AND pe.immunization_code = UPPER(mi.immunization_code)
1030 AND TRUNC(pe.start_date) = TRUNC(mi.start_date)
1031 )
1032 WHERE mi.interface_run_id = l_interface_run_id
1033 AND mi.match_ind IS NULL
1034 AND mi.status = cst_stat_val_2
1035 AND EXISTS (SELECT '1'
1036 FROM igs_pe_immu_dtls pe, igs_ad_interface_all ii
1037 WHERE ii.interface_run_id = l_interface_run_id
1038 AND ii.interface_id = mi.interface_id
1039 AND ii.person_id = pe.person_id
1040 AND pe.immunization_code = UPPER(mi.immunization_code)
1041 AND TRUNC(pe.start_date) = TRUNC(mi.start_date));
1042 END IF;
1043
1044 FOR pe_health_rec IN c_immu_dtls_cur(l_interface_run_id) LOOP
1045
1046 l_processed_records := l_processed_records + 1;
1047
1048 pe_health_rec.immunization_code := UPPER(pe_health_rec.immunization_code);
1049 pe_health_rec.status_code := UPPER(pe_health_rec.status_code);
1050 pe_health_rec.start_date := TRUNC(pe_health_rec.start_date);
1051 pe_health_rec.end_date := TRUNC(pe_health_rec.end_date);
1052
1053 dup_chk_health_rec.immu_details_id := NULL;
1054 OPEN dup_chk_health_cur(pe_health_rec.person_id,pe_health_rec.immunization_code,pe_health_rec.start_date);
1055 FETCH dup_chk_health_cur INTO dup_chk_health_rec;
1056 CLOSE dup_chk_health_cur;
1057
1058 IF dup_chk_health_rec.immu_details_id IS NOT NULL THEN
1059 IF l_rule = 'I' THEN
1060 validate_record_health(pe_health_rec, l_error_code);
1061 IF l_error_code IS NULL THEN
1062 BEGIN
1063 IF NOT igs_ad_imp_018.validate_desc_flex(
1064 p_attribute_category =>pe_health_rec.attribute_category,
1065 p_attribute1 =>pe_health_rec.attribute1 ,
1066 p_attribute2 =>pe_health_rec.attribute2 ,
1067 p_attribute3 =>pe_health_rec.attribute3 ,
1068 p_attribute4 =>pe_health_rec.attribute4 ,
1069 p_attribute5 =>pe_health_rec.attribute5 ,
1070 p_attribute6 =>pe_health_rec.attribute6 ,
1071 p_attribute7 =>pe_health_rec.attribute7 ,
1072 p_attribute8 =>pe_health_rec.attribute8 ,
1073 p_attribute9 =>pe_health_rec.attribute9 ,
1074 p_attribute10 =>pe_health_rec.attribute10 ,
1075 p_attribute11 =>pe_health_rec.attribute11 ,
1076 p_attribute12 =>pe_health_rec.attribute12 ,
1077 p_attribute13 =>pe_health_rec.attribute13 ,
1078 p_attribute14 =>pe_health_rec.attribute14 ,
1079 p_attribute15 =>pe_health_rec.attribute15 ,
1080 p_attribute16 =>pe_health_rec.attribute16 ,
1081 p_attribute17 =>pe_health_rec.attribute17 ,
1082 p_attribute18 =>pe_health_rec.attribute18 ,
1083 p_attribute19 =>pe_health_rec.attribute19 ,
1084 p_attribute20 =>pe_health_rec.attribute20 ,
1085 p_desc_flex_name =>'IGS_PE_IMMU_DTLS_FLEX' ) THEN
1086
1087 IF l_enable_log = 'Y' THEN
1088 igs_ad_imp_001.logerrormessage(pe_health_rec.interface_immu_dtls_id,'E255','IGS_PE_IMMU_DTL_INT');
1089 END IF;
1090
1091 UPDATE igs_pe_immu_dtl_int
1092 SET ERROR_CODE ='E255',
1093 status = '3'
1094 WHERE INTERFACE_IMMU_DTLS_ID = pe_health_rec.interface_immu_dtls_id;
1095
1096 ELSE
1097 igs_pe_immu_dtls_pkg.UPDATE_ROW
1098 (
1099 x_rowid => dup_chk_health_rec.ROWID,
1100 x_start_date => NVL(pe_health_rec.start_date,dup_chk_health_rec.start_date),
1101 x_end_date => NVL(pe_health_rec.end_date,dup_chk_health_rec.start_date),
1102 X_ATTRIBUTE_CATEGORY => NVL(pe_health_rec.ATTRIBUTE_CATEGORY, dup_chk_health_rec.ATTRIBUTE_CATEGORY),
1103 X_ATTRIBUTE1 => NVL(pe_health_rec.ATTRIBUTE1, dup_chk_health_rec.ATTRIBUTE1),
1104 X_ATTRIBUTE2 => NVL(pe_health_rec.ATTRIBUTE2, dup_chk_health_rec.ATTRIBUTE2),
1105 X_ATTRIBUTE3 => NVL(pe_health_rec.ATTRIBUTE3, dup_chk_health_rec.ATTRIBUTE3),
1106 X_ATTRIBUTE4 => NVL(pe_health_rec.ATTRIBUTE4, dup_chk_health_rec.ATTRIBUTE4),
1107 X_ATTRIBUTE5 => NVL(pe_health_rec.ATTRIBUTE5, dup_chk_health_rec.ATTRIBUTE5),
1108 X_ATTRIBUTE6 => NVL(pe_health_rec.ATTRIBUTE6, dup_chk_health_rec.ATTRIBUTE6),
1109 X_ATTRIBUTE7 => NVL(pe_health_rec.ATTRIBUTE7, dup_chk_health_rec.ATTRIBUTE7),
1110 X_ATTRIBUTE8 => NVL(pe_health_rec.ATTRIBUTE8, dup_chk_health_rec.ATTRIBUTE8),
1111 X_ATTRIBUTE9 => NVL(pe_health_rec.ATTRIBUTE9, dup_chk_health_rec.ATTRIBUTE9),
1112 X_ATTRIBUTE10 => NVL(pe_health_rec.ATTRIBUTE10, dup_chk_health_rec.ATTRIBUTE10),
1113 X_ATTRIBUTE11 => NVL(pe_health_rec.ATTRIBUTE11, dup_chk_health_rec.ATTRIBUTE11),
1114 X_ATTRIBUTE12 => NVL(pe_health_rec.ATTRIBUTE12, dup_chk_health_rec.ATTRIBUTE12),
1115 X_ATTRIBUTE13 => NVL(pe_health_rec.ATTRIBUTE13, dup_chk_health_rec.ATTRIBUTE13),
1116 X_ATTRIBUTE14 => NVL(pe_health_rec.ATTRIBUTE14, dup_chk_health_rec.ATTRIBUTE14),
1117 X_ATTRIBUTE15 => NVL(pe_health_rec.ATTRIBUTE15, dup_chk_health_rec.ATTRIBUTE15),
1118 X_ATTRIBUTE16 => NVL(pe_health_rec.ATTRIBUTE16, dup_chk_health_rec.ATTRIBUTE16),
1119 X_ATTRIBUTE17 => NVL(pe_health_rec.ATTRIBUTE17, dup_chk_health_rec.ATTRIBUTE17),
1120 X_ATTRIBUTE18 => NVL(pe_health_rec.ATTRIBUTE18, dup_chk_health_rec.ATTRIBUTE18),
1121 X_ATTRIBUTE19 => NVL(pe_health_rec.ATTRIBUTE19, dup_chk_health_rec.ATTRIBUTE19),
1122 X_ATTRIBUTE20 => NVL(pe_health_rec.ATTRIBUTE20, dup_chk_health_rec.ATTRIBUTE20),
1123 x_status_code => NVL(pe_health_rec.status_code, dup_chk_health_rec.status_code),
1124 x_immunization_code => NVL(pe_health_rec.immunization_code,dup_chk_health_rec.immunization_code),
1125 x_IMMU_DETAILS_ID => dup_chk_health_rec.IMMU_DETAILS_ID,
1126 x_PERSON_ID => NVL(pe_health_rec.PERSON_ID,dup_chk_health_rec.PERSON_ID),
1127 x_mode =>'R'
1128 );
1129 l_error_code := NULL;
1130 l_status := '1';
1131 UPDATE igs_pe_immu_dtl_int
1132 SET match_ind = cst_mi_val_18,
1133 status = l_status,
1134 ERROR_CODE = l_error_code
1135 WHERE interface_immu_dtls_id = pe_health_rec.interface_immu_dtls_id;
1136 END IF;
1137
1138 EXCEPTION
1139 WHEN OTHERS THEN
1140 l_error_code := 'E160'; -- Could not update Immunization details
1141 l_status := '3';
1142 UPDATE igs_pe_immu_dtl_int
1143 SET status = l_status,
1144 ERROR_CODE = l_error_code
1145 WHERE interface_immu_dtls_id = pe_health_rec.interface_immu_dtls_id;
1146
1147 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
1148
1149 IF (l_request_id IS NULL) THEN
1150 l_request_id := fnd_global.conc_request_id;
1151 END IF;
1152
1153 l_label := 'igs.plsql.igs_ad_imp_007.prc_pe_immu_dtls.exception';
1154
1155 l_debug_str := 'IGS_AD_IMP_007.PRC_PE_IMMU_DTLS'
1156 || 'INTERFACE_IMMU_DTLS_ID : ' ||
1157 pe_health_rec.interface_immu_dtls_id ||
1158 'Status : ' || l_status || 'ErrorCode : ' || l_error_code || SQLERRM;
1159
1160 fnd_log.string_with_context( fnd_log.level_exception,
1161 l_label,
1162 l_debug_str, NULL,
1163 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1164 END IF;
1165
1166 IF l_enable_log = 'Y' THEN
1167 igs_ad_imp_001.logerrormessage(pe_health_rec.interface_immu_dtls_id,l_error_code,'IGS_PE_IMMU_DTL_INT');
1168 END IF;
1169
1170 END;
1171 END IF;
1172
1173 ELSIF l_rule = 'R' THEN
1174 IF pe_health_rec.match_ind = '21' THEN
1175 -- call the validation process
1176 validate_record_health(pe_health_rec, l_error_code);
1177 IF l_error_code IS NULL THEN
1178 BEGIN
1179 IF NOT igs_ad_imp_018.validate_desc_flex(
1180 p_attribute_category =>pe_health_rec.attribute_category,
1181 p_attribute1 =>pe_health_rec.attribute1 ,
1182 p_attribute2 =>pe_health_rec.attribute2 ,
1183 p_attribute3 =>pe_health_rec.attribute3 ,
1184 p_attribute4 =>pe_health_rec.attribute4 ,
1185 p_attribute5 =>pe_health_rec.attribute5 ,
1186 p_attribute6 =>pe_health_rec.attribute6 ,
1187 p_attribute7 =>pe_health_rec.attribute7 ,
1188 p_attribute8 =>pe_health_rec.attribute8 ,
1189 p_attribute9 =>pe_health_rec.attribute9 ,
1190 p_attribute10 =>pe_health_rec.attribute10 ,
1191 p_attribute11 =>pe_health_rec.attribute11 ,
1192 p_attribute12 =>pe_health_rec.attribute12 ,
1193 p_attribute13 =>pe_health_rec.attribute13 ,
1194 p_attribute14 =>pe_health_rec.attribute14 ,
1195 p_attribute15 =>pe_health_rec.attribute15 ,
1196 p_attribute16 =>pe_health_rec.attribute16 ,
1197 p_attribute17 =>pe_health_rec.attribute17 ,
1198 p_attribute18 =>pe_health_rec.attribute18 ,
1199 p_attribute19 =>pe_health_rec.attribute19 ,
1200 p_attribute20 =>pe_health_rec.attribute20 ,
1201 p_desc_flex_name =>'IGS_PE_IMMU_DTLS_FLEX' ) THEN
1202
1203 IF l_enable_log = 'Y' THEN
1204 igs_ad_imp_001.logerrormessage(pe_health_rec.INTERFACE_IMMU_DTLS_ID,'E255','IGS_PE_IMMU_DTL_INT');
1205 END IF;
1206
1207 UPDATE igs_pe_immu_dtl_int
1208 SET ERROR_CODE ='E255',
1209 status = '3'
1210 WHERE INTERFACE_IMMU_DTLS_ID = pe_health_rec.INTERFACE_IMMU_DTLS_ID;
1211 ELSE
1212 igs_pe_immu_dtls_pkg.UPDATE_ROW
1213 (
1214 x_rowid => dup_chk_health_rec.ROWID,
1215 x_start_date => NVL(pe_health_rec.start_date,dup_chk_health_rec.start_date),
1216 x_end_date => NVL(pe_health_rec.end_date,dup_chk_health_rec.end_date),
1217 X_ATTRIBUTE_CATEGORY => NVL(pe_health_rec.ATTRIBUTE_CATEGORY, dup_chk_health_rec.ATTRIBUTE_CATEGORY),
1218 X_ATTRIBUTE1 => NVL(pe_health_rec.ATTRIBUTE1, dup_chk_health_rec.ATTRIBUTE1),
1219 X_ATTRIBUTE2 => NVL(pe_health_rec.ATTRIBUTE2, dup_chk_health_rec.ATTRIBUTE2),
1220 X_ATTRIBUTE3 => NVL(pe_health_rec.ATTRIBUTE3, dup_chk_health_rec.ATTRIBUTE3),
1221 X_ATTRIBUTE4 => NVL(pe_health_rec.ATTRIBUTE4, dup_chk_health_rec.ATTRIBUTE4),
1222 X_ATTRIBUTE5 => NVL(pe_health_rec.ATTRIBUTE5, dup_chk_health_rec.ATTRIBUTE5),
1223 X_ATTRIBUTE6 => NVL(pe_health_rec.ATTRIBUTE6, dup_chk_health_rec.ATTRIBUTE6),
1224 X_ATTRIBUTE7 => NVL(pe_health_rec.ATTRIBUTE7, dup_chk_health_rec.ATTRIBUTE7),
1225 X_ATTRIBUTE8 => NVL(pe_health_rec.ATTRIBUTE8, dup_chk_health_rec.ATTRIBUTE8),
1226 X_ATTRIBUTE9 => NVL(pe_health_rec.ATTRIBUTE9, dup_chk_health_rec.ATTRIBUTE9),
1227 X_ATTRIBUTE10 => NVL(pe_health_rec.ATTRIBUTE10, dup_chk_health_rec.ATTRIBUTE10),
1228 X_ATTRIBUTE11 => NVL(pe_health_rec.ATTRIBUTE11, dup_chk_health_rec.ATTRIBUTE11),
1229 X_ATTRIBUTE12 => NVL(pe_health_rec.ATTRIBUTE12, dup_chk_health_rec.ATTRIBUTE12),
1230 X_ATTRIBUTE13 => NVL(pe_health_rec.ATTRIBUTE13, dup_chk_health_rec.ATTRIBUTE13),
1231 X_ATTRIBUTE14 => NVL(pe_health_rec.ATTRIBUTE14, dup_chk_health_rec.ATTRIBUTE14),
1232 X_ATTRIBUTE15 => NVL(pe_health_rec.ATTRIBUTE15, dup_chk_health_rec.ATTRIBUTE15),
1233 X_ATTRIBUTE16 => NVL(pe_health_rec.ATTRIBUTE16, dup_chk_health_rec.ATTRIBUTE16),
1234 X_ATTRIBUTE17 => NVL(pe_health_rec.ATTRIBUTE17, dup_chk_health_rec.ATTRIBUTE17),
1235 X_ATTRIBUTE18 => NVL(pe_health_rec.ATTRIBUTE18, dup_chk_health_rec.ATTRIBUTE18),
1236 X_ATTRIBUTE19 => NVL(pe_health_rec.ATTRIBUTE19, dup_chk_health_rec.ATTRIBUTE19),
1237 X_ATTRIBUTE20 => NVL(pe_health_rec.ATTRIBUTE20, dup_chk_health_rec.ATTRIBUTE20),
1238 x_status_code => NVL(pe_health_rec.status_code,dup_chk_health_rec.status_code),
1239 x_immunization_code => NVL(pe_health_rec.immunization_code,dup_chk_health_rec.immunization_code),
1240 x_IMMU_DETAILS_ID => dup_chk_health_rec.immu_details_id,
1241 x_PERSON_ID => NVL(pe_health_rec.PERSON_ID,dup_chk_health_rec.person_id),
1242 x_mode =>'R'
1243 );
1244 l_error_code := NULL;
1245 l_status := '1';
1246 UPDATE igs_pe_immu_dtl_int
1247 SET match_ind = cst_mi_val_18,
1248 status = l_status,
1249 ERROR_CODE = l_error_code
1250 WHERE interface_immu_dtls_id = pe_health_rec.interface_immu_dtls_id;
1251 END IF;
1252 EXCEPTION
1253 WHEN OTHERS THEN
1254 l_error_code := 'E160'; -- Could not update Immunization details
1255 l_status := '3';
1256 UPDATE igs_pe_immu_dtl_int
1257 SET status = l_status,
1258 ERROR_CODE = l_error_code
1259 WHERE interface_immu_dtls_id = pe_health_rec.interface_immu_dtls_id;
1260
1261 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
1262
1263 IF (l_request_id IS NULL) THEN
1264 l_request_id := fnd_global.conc_request_id;
1265 END IF;
1266
1267 l_label := 'igs.plsql.igs_ad_imp_007.prc_pe_immu_dtls.exception: '|| l_error_code;
1268
1269 l_debug_str := 'IGS_AD_IMP_007.PRC_PE_IMMU_DTLS'
1270 || 'INTERFACE_IMMU_DTLS_ID : ' ||
1271 IGS_GE_NUMBER.TO_CANN(pe_health_rec.interface_immu_dtls_id) ||
1272 'Status : ' || l_status || 'ErrorCode : ' || l_error_code || SQLERRM;
1273
1274 fnd_log.string_with_context( fnd_log.level_exception,
1275 l_label,
1276 l_debug_str, NULL,
1277 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1278 END IF;
1279
1280 IF l_enable_log = 'Y' THEN
1281 igs_ad_imp_001.logerrormessage(pe_health_rec.interface_immu_dtls_id,l_error_code,'IGS_PE_IMMU_DTL_INT');
1282 END IF;
1283 END;
1284 END IF; -- if error_code = null (of validate record)
1285 END IF; -- pe_health_rec.MATCH_IND check
1286 END IF; -- l_rule check for 'I' or 'R'.
1287 ELSE
1288 -- Make a call to Create health Details
1289 --with the following parameters.
1290 crt_health_ins(
1291 pe_health_rec,
1292 l_error_code,
1293 l_status);
1294 END IF; -- record existance in Ad_health check
1295 IF l_processed_records = 100 THEN
1296 COMMIT;
1297 l_processed_records := 0;
1298 END IF;
1299 END LOOP;
1300 END prc_pe_immu_dtls;
1301
1302
1303 PROCEDURE PRC_PE_HLTH_INS_DTLS
1304 ( P_SOURCE_TYPE_ID IN NUMBER,
1305 P_BATCH_ID IN NUMBER
1306 )
1307 AS
1308 /*
1309 || Created By : npalanis
1310 || Created On :23-Jul-2002
1311 || Purpose : This procedure process the Health Insurance Details
1312 || Known limitations, enhancements or remarks :
1313 || Change History :
1314 || Who When What
1315 || pkpatel 15-JAN-2003 Bug NO: 2397876
1316 || Added all the missing validations and replaced E008 with proper error codes
1317 || npalanis 6-JAN-2003 Bug : 2734697
1318 || code added to commit after import of every
1319 || 100 records .New variable l_processed_records added
1320 || npalanis 25-JUL-2002 Bug - 2425734
1321 || Validate_health_Ins procedure added,parameter added in dup check to get the
1322 || primary key id into the DUP_HLTH_INS_ID field in interfce table
1323 ||
1324 || (reverse chronological order - newest change first)
1325 */
1326 CURSOR hlth_ins(cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE) IS
1327 SELECT hii.*, i.person_id
1328 FROM igs_ad_hlth_ins_int_all hii, igs_ad_interface_all i
1329 WHERE hii.interface_run_id = cp_interface_run_id
1330 AND i.interface_id = hii.interface_id
1331 AND i.interface_run_id = cp_interface_run_id
1332 AND hii.status = '2';
1333
1334 l_dup_var BOOLEAN;
1335 p_health_ins_id NUMBER(15);
1336 l_var VARCHAR2(1);
1337 L_RULE VARCHAR2(1);
1338 L_ERROR_cODE VARCHAR2(10);
1339 L_STATUS VARCHAR2(10);
1340 l_check VARCHAR2(10);
1341 l_dup_hlth_ins_id IGS_AD_HLTH_INS_INT.DUP_HLTH_INS_ID%TYPE;
1342 l_processed_records NUMBER(5) := 0;
1343 l_prog_label VARCHAR2(4000);
1344 l_label VARCHAR2(4000);
1345 l_debug_str VARCHAR2(4000);
1346 l_enable_log VARCHAR2(1);
1347 l_request_id NUMBER(10);
1348 l_interface_run_id igs_ad_interface_all.interface_run_id%TYPE;
1349 PROCEDURE Crt_Pe_hlth_ins(hlth_ins_rec IN HLTH_INS%ROWTYPE,
1350 p_error_code OUT NOCOPY VARCHAR2,
1351 p_status OUT NOCOPY VARCHAR2) AS
1352
1353 l_rowid VARCHAR2(25);
1354 l_hlth_id NUMBER;
1355 l_org_id NUMBER(15);
1356 BEGIN
1357
1358 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
1359
1360 IF (l_request_id IS NULL) THEN
1361 l_request_id := fnd_global.conc_request_id;
1362 END IF;
1363
1364 l_label := 'igs.plsql.igs_ad_imp_007.crt_pe_hlth_ins.begin';
1365 l_debug_str := 'Interface_hlth_Id: '||(hlth_ins_rec.Interface_hlth_Id);
1366
1367 fnd_log.string_with_context( fnd_log.level_procedure,
1368 l_label,
1369 l_debug_str, NULL,
1370 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1371 END IF;
1372
1373 l_org_id := igs_ge_gen_003.get_org_id;
1374
1375 IGS_PE_HLTH_INS_PKG.INSERT_ROW (
1376 x_ROWID => l_rowid,
1377 x_HEALTH_INS_ID => l_hlth_id ,
1378 x_PERSON_ID => HLTH_INS_REC.PERSON_ID ,
1379 x_INSURANCE_CD => HLTH_INS_REC.INSURANCE_CD ,
1380 x_INSURANCE_PROVIDER => HLTH_INS_REC.INSURANCE_PROVIDER ,
1381 x_POLICY_NUMBER => HLTH_INS_REC.POLICY_NUMBER ,
1382 x_START_DATE => HLTH_INS_REC.START_DATE ,
1383 x_END_DATE => HLTH_INS_REC.END_DATE ,
1384 X_ATTRIBUTE_CATEGORY => HLTH_INS_REC.ATTRIBUTE_CATEGORY,
1385 X_ATTRIBUTE1 => HLTH_INS_REC.ATTRIBUTE1,
1386 X_ATTRIBUTE2 => HLTH_INS_REC.ATTRIBUTE2,
1387 X_ATTRIBUTE3 => HLTH_INS_REC.ATTRIBUTE3,
1388 X_ATTRIBUTE4 => HLTH_INS_REC.ATTRIBUTE4,
1389 X_ATTRIBUTE5 => HLTH_INS_REC.ATTRIBUTE5,
1390 X_ATTRIBUTE6 => HLTH_INS_REC.ATTRIBUTE6,
1391 X_ATTRIBUTE7 => HLTH_INS_REC.ATTRIBUTE7,
1392 X_ATTRIBUTE8 => HLTH_INS_REC.ATTRIBUTE8,
1393 X_ATTRIBUTE9 => HLTH_INS_REC.ATTRIBUTE9,
1394 X_ATTRIBUTE10 => HLTH_INS_REC.ATTRIBUTE10,
1395 X_ATTRIBUTE11 => HLTH_INS_REC.ATTRIBUTE11,
1396 X_ATTRIBUTE12 => HLTH_INS_REC.ATTRIBUTE12,
1397 X_ATTRIBUTE13 => HLTH_INS_REC.ATTRIBUTE13,
1398 X_ATTRIBUTE14 => HLTH_INS_REC.ATTRIBUTE14,
1399 X_ATTRIBUTE15 => HLTH_INS_REC.ATTRIBUTE15,
1400 X_ATTRIBUTE16 => HLTH_INS_REC.ATTRIBUTE16,
1401 X_ATTRIBUTE17 => HLTH_INS_REC.ATTRIBUTE17,
1402 X_ATTRIBUTE18 => HLTH_INS_REC.ATTRIBUTE18,
1403 X_ATTRIBUTE19 => HLTH_INS_REC.ATTRIBUTE19,
1404 X_ATTRIBUTE20 => HLTH_INS_REC.ATTRIBUTE20,
1405 X_MODE => 'R',
1406 X_org_id => l_org_id );
1407
1408 p_error_code := NULL;
1409 p_status := '1';
1410
1411 EXCEPTION
1412 WHEN OTHERS THEN
1413
1414 p_error_code := 'E322';
1415 p_status := '3';
1416
1417 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
1418
1419 IF (l_request_id IS NULL) THEN
1420 l_request_id := fnd_global.conc_request_id;
1421 END IF;
1422
1423 l_label := 'igs.plsql.igs_ad_imp_007.crt_pe_hlth_ins.exception';
1424
1425 l_debug_str := 'IGS_AD_IMP_007.PRC_PE_HLTH_INS_DTLS.Crt_Pe_hlth_ins ' ||
1426 'Status : 3' || 'ErrorCode : E322 insert failed ' || SQLERRM;
1427
1428 fnd_log.string_with_context( fnd_log.level_exception,
1429 l_label,
1430 l_debug_str, NULL,
1431 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1432 END IF;
1433
1434 IF l_enable_log = 'Y' THEN
1435 igs_ad_imp_001.logerrormessage(HLTH_INS_REC.Interface_hlth_Id,'E322','IGS_AD_HLTH_INS_INT_ALL');
1436 END IF;
1437
1438 END Crt_Pe_hlth_ins;
1439
1440 PROCEDURE Validate_health_Ins(hlth_ins_rec IN HLTH_INS%ROWTYPE, l_check OUT NOCOPY VARCHAR2) AS
1441
1442 CURSOR birth_dt_cur(p_person_id IGS_AD_INTERFACE.PERSON_ID%TYPE) IS
1443 SELECT birth_date
1444 FROM igs_pe_person_base_v
1445 WHERE person_id= p_person_id;
1446
1447 l_var VARCHAR2(1);
1448 l_birth_dt IGS_AD_INTERFACE.BIRTH_DT%TYPE;
1449 p_error_code IGS_AD_INTERFACE.ERROR_CODE%TYPE;
1450
1451 BEGIN
1452
1453 IF NOT
1454 (igs_pe_pers_imp_001.validate_lookup_type_code('PE_INS_TYPE',hlth_ins_rec.insurance_cd,8405))
1455 THEN
1456 p_error_code := 'E552';
1457 RAISE NO_DATA_FOUND;
1458 END IF;
1459
1460 OPEN birth_dt_cur(hlth_ins_rec.person_id);
1461 FETCH birth_dt_cur INTO l_birth_dt;
1462 IF l_birth_dt IS NOT NULL AND hlth_ins_rec.start_date < l_birth_dt THEN
1463 p_error_code := 'E222';
1464 CLOSE birth_dt_cur;
1465 RAISE NO_DATA_FOUND;
1466 END IF;
1467 CLOSE birth_dt_cur;
1468
1469 IF hlth_ins_rec.end_date IS NOT NULL THEN
1470 IF hlth_ins_rec.end_date < hlth_ins_rec.start_date THEN
1471 p_error_code := 'E208';
1472 RAISE NO_DATA_FOUND;
1473 END IF;
1474 END IF;
1475
1476 IF NOT igs_ad_imp_018.validate_desc_flex(
1477 p_attribute_category =>HLTH_INS_REC.attribute_category,
1478 p_attribute1 =>HLTH_INS_REC.attribute1 ,
1479 p_attribute2 =>HLTH_INS_REC.attribute2 ,
1480 p_attribute3 =>HLTH_INS_REC.attribute3 ,
1481 p_attribute4 =>HLTH_INS_REC.attribute4 ,
1482 p_attribute5 =>HLTH_INS_REC.attribute5 ,
1483 p_attribute6 =>HLTH_INS_REC.attribute6 ,
1484 p_attribute7 =>HLTH_INS_REC.attribute7 ,
1485 p_attribute8 =>HLTH_INS_REC.attribute8 ,
1486 p_attribute9 =>HLTH_INS_REC.attribute9 ,
1487 p_attribute10 =>HLTH_INS_REC.attribute10 ,
1488 p_attribute11 =>HLTH_INS_REC.attribute11 ,
1489 p_attribute12 =>HLTH_INS_REC.attribute12 ,
1490 p_attribute13 =>HLTH_INS_REC.attribute13 ,
1491 p_attribute14 =>HLTH_INS_REC.attribute14 ,
1492 p_attribute15 =>HLTH_INS_REC.attribute15 ,
1493 p_attribute16 =>HLTH_INS_REC.attribute16 ,
1494 p_attribute17 =>HLTH_INS_REC.attribute17 ,
1495 p_attribute18 =>HLTH_INS_REC.attribute18 ,
1496 p_attribute19 =>HLTH_INS_REC.attribute19 ,
1497 p_attribute20 =>HLTH_INS_REC.attribute20 ,
1498 p_desc_flex_name =>'IGS_PE_HLTH_INS_ALL_FLEX' ) THEN
1499
1500 p_error_code := 'E255';
1501 RAISE NO_DATA_FOUND;
1502 END IF;
1503
1504 p_error_code := NULL;
1505 l_check := 'FALSE';
1506
1507 EXCEPTION
1508 WHEN OTHERS THEN
1509 UPDATE igs_ad_hlth_ins_int
1510 SET error_code = p_error_code,
1511 status = '3'
1512 WHERE interface_hlth_id = hlth_ins_rec.interface_hlth_id;
1513
1514 IF l_enable_log = 'Y' THEN
1515 igs_ad_imp_001.logerrormessage(hlth_ins_rec.Interface_hlth_Id,p_error_code,'IGS_AD_HLTH_INS_INT_ALL');
1516 END IF;
1517
1518 l_check := 'TRUE';
1519 END;
1520
1521 BEGIN
1522 l_interface_run_id := igs_ad_imp_001.g_interface_run_id;
1523 l_enable_log := igs_ad_imp_001.g_enable_log;
1524 l_prog_label := 'igs.plsql.igs_ad_imp_007.prc_pe_hlth_ins_dtls';
1525 l_label := 'igs.plsql.igs_ad_imp_007.prc_pe_hlth_ins_dtls.';
1526
1527 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
1528
1529 IF (l_request_id IS NULL) THEN
1530 l_request_id := fnd_global.conc_request_id;
1531 END IF;
1532
1533 l_label := 'igs.plsql.igs_ad_imp_007.prc_pe_hlth_ins_dtls.begin';
1534 l_debug_str := 'igs_ad_imp_007.prc_pe_hlth_ins_dtls.begin';
1535
1536 fnd_log.string_with_context( fnd_log.level_procedure,
1537 l_label,
1538 l_debug_str, NULL,
1539 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1540 END IF;
1541
1542 l_rule := Igs_Ad_Imp_001.FIND_SOURCE_CAT_RULE(p_source_type_id, 'PERSON_HEALTH_INSURANCE');
1543
1544 -- If rule is E or I, then if the match_ind is not null, the combination is invalid
1545 IF l_rule IN ('E','I') THEN
1546 UPDATE igs_ad_hlth_ins_int_all
1547 SET status = cst_stat_val_3,
1548 ERROR_CODE = cst_err_val_695 -- Error code depicting incorrect combination
1549 WHERE match_ind IS NOT NULL
1550 AND status = cst_stat_val_2
1551 AND interface_run_id = l_interface_run_id;
1552 END IF;
1553
1554 -- If rule is E and duplicate exists, update match_ind to 19 and status to 1
1555 IF l_rule = 'E' THEN
1556 UPDATE igs_ad_hlth_ins_int_all mi
1557 SET status = cst_stat_val_1,
1558 match_ind = cst_mi_val_19
1559 WHERE mi.interface_run_id = l_interface_run_id
1560 AND mi.status = cst_stat_val_2
1561 AND EXISTS ( SELECT '1'
1562 FROM igs_pe_hlth_ins_all pe, igs_ad_interface_all ii
1563 WHERE ii.interface_run_id = l_interface_run_id
1564 AND ii.interface_id = mi.interface_id
1565 AND ii.person_id = pe.person_id
1566 AND pe.insurance_cd = UPPER(mi.insurance_cd)
1567 AND TRUNC(pe.start_date) = TRUNC(mi.start_date) );
1568 END IF;
1569
1570 -- If rule is R and there match_ind is 18,19,22 or 23 then the records must have been
1571 -- processed in prior runs and didn't get updated .. update to status 1
1572 IF l_rule = 'R' THEN
1573 UPDATE igs_ad_hlth_ins_int_all
1574 SET status = cst_stat_val_1
1575 WHERE interface_run_id = l_interface_run_id
1576 AND match_ind IN (cst_mi_val_18,cst_mi_val_19,cst_mi_val_22,cst_mi_val_23)
1577 AND status=cst_stat_val_2;
1578 END IF;
1579
1580 -- If rule is R and match_ind is neither 21 nor 25 then error
1581 IF l_rule = 'R' THEN
1582 UPDATE igs_ad_hlth_ins_int_all
1583 SET status = cst_stat_val_3,
1584 ERROR_CODE = cst_err_val_695
1585 WHERE interface_run_id = l_interface_run_id
1586 AND (match_ind IS NOT NULL AND match_ind NOT IN (cst_mi_val_21,cst_mi_val_25))
1587 AND status=cst_stat_val_2;
1588 END IF;
1589
1590 -- If rule is R, set duplicated records with no discrepancy to status 1 and match_ind 23
1591 IF l_rule = 'R' THEN
1592 UPDATE igs_ad_hlth_ins_int_all mi
1593 SET status = cst_stat_val_1,
1594 match_ind = cst_mi_val_23
1595 WHERE mi.interface_run_id = l_interface_run_id
1596 AND mi.match_ind IS NULL
1597 AND mi.status = cst_stat_val_2
1598 AND EXISTS ( SELECT '1'
1599 FROM igs_pe_hlth_ins_all pe, igs_ad_interface_all ii
1600 WHERE ii.interface_run_id = l_interface_run_id
1601 AND ii.interface_id = mi.interface_id
1602 AND ii.person_id = pe.person_id
1603 AND pe.insurance_cd = UPPER(mi.insurance_cd)
1604 AND TRUNC(pe.start_date) = TRUNC(mi.start_date)
1605 AND UPPER(pe.insurance_provider) = UPPER(mi.insurance_provider)
1606 AND UPPER(pe.policy_number) = UPPER(mi.policy_number)
1607 AND NVL(TRUNC(pe.end_date),igs_ge_date.igsdate('9999/01/01')) = NVL(TRUNC(mi.end_date),igs_ge_date.igsdate('9999/01/01'))
1608 AND NVL(UPPER(pe.attribute1),'*!*') = NVL(UPPER(mi.attribute1),'*!*')
1609 AND NVL(UPPER(pe.attribute2),'*!*') = NVL(UPPER(mi.attribute2),'*!*')
1610 AND NVL(UPPER(pe.attribute3),'*!*') = NVL(UPPER(mi.attribute3),'*!*')
1611 AND NVL(UPPER(pe.attribute4),'*!*') = NVL(UPPER(mi.attribute4),'*!*')
1612 AND NVL(UPPER(pe.attribute5),'*!*') = NVL(UPPER(mi.attribute5),'*!*')
1613 AND NVL(UPPER(pe.attribute6),'*!*') = NVL(UPPER(mi.attribute6),'*!*')
1614 AND NVL(UPPER(pe.attribute7),'*!*') = NVL(UPPER(mi.attribute7),'*!*')
1615 AND NVL(UPPER(pe.attribute8),'*!*') = NVL(UPPER(mi.attribute8),'*!*')
1616 AND NVL(UPPER(pe.attribute9),'*!*') = NVL(UPPER(mi.attribute9),'*!*')
1617 AND NVL(UPPER(pe.attribute10),'*!*') = NVL(UPPER(mi.attribute10),'*!*')
1618 AND NVL(UPPER(pe.attribute11),'*!*') = NVL(UPPER(mi.attribute11),'*!*')
1619 AND NVL(UPPER(pe.attribute12),'*!*') = NVL(UPPER(mi.attribute12),'*!*')
1620 AND NVL(UPPER(pe.attribute13),'*!*') = NVL(UPPER(mi.attribute13),'*!*')
1621 AND NVL(UPPER(pe.attribute14),'*!*') = NVL(UPPER(mi.attribute14),'*!*')
1622 AND NVL(UPPER(pe.attribute15),'*!*') = NVL(UPPER(mi.attribute15),'*!*')
1623 AND NVL(UPPER(pe.attribute16),'*!*') = NVL(UPPER(mi.attribute16),'*!*')
1624 AND NVL(UPPER(pe.attribute17),'*!*') = NVL(UPPER(mi.attribute17),'*!*')
1625 AND NVL(UPPER(pe.attribute18),'*!*') = NVL(UPPER(mi.attribute18),'*!*')
1626 AND NVL(UPPER(pe.attribute19),'*!*') = NVL(UPPER(mi.attribute19),'*!*')
1627 AND NVL(UPPER(pe.attribute20),'*!*') = NVL(UPPER(mi.attribute20),'*!*')
1628 );
1629 END IF;
1630
1631 -- If rule in R records still exist, they are duplicates and have discrepancy .. update status=3,match_ind=20
1632 IF l_rule = 'R' THEN
1633 UPDATE igs_ad_hlth_ins_int_all mi
1634 SET status = cst_stat_val_3,
1635 match_ind = cst_mi_val_20,
1636 dup_hlth_ins_id = (SELECT health_ins_id
1637 FROM igs_pe_hlth_ins_all pe, igs_ad_interface_all ii
1638 WHERE ii.interface_run_id = l_interface_run_id
1639 AND ii.interface_id = mi.interface_id
1640 AND ii.person_id = pe.person_id
1641 AND pe.insurance_cd = UPPER(mi.insurance_cd)
1642 AND TRUNC(pe.start_date) = TRUNC(mi.start_date))
1643 WHERE mi.interface_run_id = l_interface_run_id
1644 AND mi.match_ind IS NULL
1645 AND mi.status = cst_stat_val_2
1646 AND EXISTS (SELECT '1'
1647 FROM igs_pe_hlth_ins_all pe, igs_ad_interface_all ii
1648 WHERE ii.interface_run_id = l_interface_run_id
1649 AND ii.interface_id = mi.interface_id
1650 AND ii.person_id = pe.person_id
1651 AND pe.insurance_cd = UPPER(mi.insurance_cd)
1652 AND TRUNC(pe.start_date) = TRUNC(mi.start_date));
1653 END IF;
1654
1655 FOR hlth_ins_rec IN hlth_ins(l_interface_run_id) LOOP
1656
1657 l_processed_records := l_processed_records + 1;
1658
1659 l_check := 'FALSE';
1660 hlth_ins_rec.start_date := TRUNC(hlth_ins_rec.start_date);
1661 hlth_ins_rec.end_date := TRUNC(hlth_ins_rec.end_date);
1662 hlth_ins_rec.INSURANCE_CD := UPPER(hlth_ins_rec.INSURANCE_CD);
1663
1664 Validate_health_ins(hlth_ins_rec,l_check);
1665
1666 IF l_check <> 'TRUE' THEN
1667 DECLARE
1668 CURSOR chk_dup_pe_hlthins(cp_insurance_cd VARCHAR2,
1669 cp_person_id NUMBER,
1670 cp_start_date IGS_AD_HLTH_INS_INT.START_DATE%TYPE) IS
1671 SELECT rowid, hi.*
1672 FROM igs_pe_hlth_ins hi
1673 WHERE hi.person_id = cp_person_id
1674 AND UPPER(hi.insurance_cd) = UPPER(cp_insurance_cd)
1675 AND TRUNC(hi.start_date) = TRUNC(cp_start_date);
1676 dup_pe_hlthins_rec chk_dup_pe_hlthins%ROWTYPE;
1677 BEGIN
1678 dup_pe_hlthins_rec.insurance_cd := NULL;
1679 OPEN chk_dup_pe_hlthins(hlth_ins_rec.insurance_cd,
1680 hlth_ins_rec.person_id,
1681 hlth_ins_rec.start_date);
1682 FETCH chk_dup_pe_hlthins INTO dup_pe_hlthins_rec;
1683 CLOSE chk_dup_pe_hlthins;
1684 IF dup_pe_hlthins_rec.insurance_cd IS NOT NULL THEN
1685 IF l_rule = 'I' THEN
1686 BEGIN
1687 igs_pe_hlth_ins_pkg.update_row(
1688 x_rowid=>dup_pe_hlthins_rec.rowid,
1689 x_health_ins_id=>dup_pe_hlthins_rec.health_ins_id,
1690 x_person_id=>dup_pe_hlthins_rec.person_id,
1691 x_insurance_provider=> NVL(hlth_ins_rec.insurance_provider,dup_pe_hlthins_rec.insurance_provider),
1692 x_policy_number=> NVL(hlth_ins_rec.policy_number,dup_pe_hlthins_rec.policy_number),
1693 x_start_date=> NVL(hlth_ins_rec.start_date,dup_pe_hlthins_rec.start_date),
1694 x_end_date=> NVL(hlth_ins_rec.end_date,dup_pe_hlthins_rec.end_date),
1695 X_ATTRIBUTE_CATEGORY => NVL(HLTH_INS_REC.ATTRIBUTE_CATEGORY, dup_pe_hlthins_rec.ATTRIBUTE_CATEGORY),
1696 X_ATTRIBUTE1 => NVL(HLTH_INS_REC.ATTRIBUTE1, dup_pe_hlthins_rec.ATTRIBUTE1),
1697 X_ATTRIBUTE2 => NVL(HLTH_INS_REC.ATTRIBUTE2, dup_pe_hlthins_rec.ATTRIBUTE2),
1698 X_ATTRIBUTE3 => NVL(HLTH_INS_REC.ATTRIBUTE3, dup_pe_hlthins_rec.ATTRIBUTE3),
1699 X_ATTRIBUTE4 => NVL(HLTH_INS_REC.ATTRIBUTE4, dup_pe_hlthins_rec.ATTRIBUTE4),
1700 X_ATTRIBUTE5 => NVL(HLTH_INS_REC.ATTRIBUTE5, dup_pe_hlthins_rec.ATTRIBUTE5),
1701 X_ATTRIBUTE6 => NVL(HLTH_INS_REC.ATTRIBUTE6, dup_pe_hlthins_rec.ATTRIBUTE6),
1702 X_ATTRIBUTE7 => NVL(HLTH_INS_REC.ATTRIBUTE7, dup_pe_hlthins_rec.ATTRIBUTE7),
1703 X_ATTRIBUTE8 => NVL(HLTH_INS_REC.ATTRIBUTE8, dup_pe_hlthins_rec.ATTRIBUTE8),
1704 X_ATTRIBUTE9 => NVL(HLTH_INS_REC.ATTRIBUTE9, dup_pe_hlthins_rec.ATTRIBUTE9),
1705 X_ATTRIBUTE10 => NVL(HLTH_INS_REC.ATTRIBUTE10, dup_pe_hlthins_rec.ATTRIBUTE10),
1706 X_ATTRIBUTE11 => NVL(HLTH_INS_REC.ATTRIBUTE11, dup_pe_hlthins_rec.ATTRIBUTE11),
1707 X_ATTRIBUTE12 => NVL(HLTH_INS_REC.ATTRIBUTE12, dup_pe_hlthins_rec.ATTRIBUTE12),
1708 X_ATTRIBUTE13 => NVL(HLTH_INS_REC.ATTRIBUTE13, dup_pe_hlthins_rec.ATTRIBUTE13),
1709 X_ATTRIBUTE14 => NVL(HLTH_INS_REC.ATTRIBUTE14, dup_pe_hlthins_rec.ATTRIBUTE14),
1710 X_ATTRIBUTE15 => NVL(HLTH_INS_REC.ATTRIBUTE15, dup_pe_hlthins_rec.ATTRIBUTE15),
1711 X_ATTRIBUTE16 => NVL(HLTH_INS_REC.ATTRIBUTE16, dup_pe_hlthins_rec.ATTRIBUTE16),
1712 X_ATTRIBUTE17 => NVL(HLTH_INS_REC.ATTRIBUTE17, dup_pe_hlthins_rec.ATTRIBUTE17),
1713 X_ATTRIBUTE18 => NVL(HLTH_INS_REC.ATTRIBUTE18, dup_pe_hlthins_rec.ATTRIBUTE18),
1714 X_ATTRIBUTE19 => NVL(HLTH_INS_REC.ATTRIBUTE19, dup_pe_hlthins_rec.ATTRIBUTE19),
1715 X_ATTRIBUTE20 => NVL(HLTH_INS_REC.ATTRIBUTE20, dup_pe_hlthins_rec.ATTRIBUTE20),
1716 x_mode =>'R',
1717 x_insurance_cd=> NVL(hlth_ins_rec.insurance_cd,dup_pe_hlthins_rec.insurance_cd));
1718
1719 UPDATE igs_ad_hlth_ins_int
1720 SET error_code = NULL,
1721 match_ind = cst_mi_val_18,
1722 status = cst_stat_val_1
1723 WHERE interface_hlth_id = hlth_ins_rec.interface_hlth_id;
1724 EXCEPTION
1725 WHEN OTHERS THEN
1726 UPDATE igs_ad_hlth_ins_int
1727 SET error_code = 'E014',
1728 status = '3'
1729 WHERE interface_hlth_id = hlth_ins_rec.interface_hlth_id;
1730 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
1731
1732 IF (l_request_id IS NULL) THEN
1733 l_request_id := fnd_global.conc_request_id;
1734 END IF;
1735
1736 l_label := 'igs.plsql.igs_ad_imp_007.prc_pe_hlth_ins_dtls.exception1';
1737
1738 l_debug_str := 'IGS_AD_IMP_007.PRC_PE_HLTH_INS_DTLS ' ||
1739 'Interface Health Id : ' || hlth_ins_rec.INTERFACE_HLTH_ID ||
1740 ' Status : 3 ' || 'ErrorCode : E014 Update Failed ' || SQLERRM;
1741
1742 fnd_log.string_with_context( fnd_log.level_exception,
1743 l_label,
1744 l_debug_str, NULL,
1745 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1746 END IF;
1747
1748 IF l_enable_log = 'Y' THEN
1749 igs_ad_imp_001.logerrormessage(hlth_ins_rec.INTERFACE_HLTH_ID,'E014','IGS_AD_HLTH_INS_INT_ALL');
1750 END IF;
1751
1752
1753 END;
1754 ELSIF l_rule = 'R' THEN
1755 IF hlth_ins_rec.MATCH_IND = '21' THEN
1756 BEGIN
1757 igs_pe_hlth_ins_pkg.update_row(
1758 x_rowid=>dup_pe_hlthins_rec.rowid,
1759 x_health_ins_id=>dup_pe_hlthins_rec.health_ins_id,
1760 x_person_id=>dup_pe_hlthins_rec.person_id,
1761 x_insurance_cd=> NVL(hlth_ins_rec.insurance_cd,dup_pe_hlthins_rec.insurance_cd),
1762 x_insurance_provider=> NVL(hlth_ins_rec.insurance_provider,dup_pe_hlthins_rec.insurance_provider),
1763 x_policy_number=> NVL(hlth_ins_rec.policy_number,dup_pe_hlthins_rec.policy_number),
1764 x_start_date=> NVL(hlth_ins_rec.start_date,dup_pe_hlthins_rec.start_date),
1765 x_end_date=> NVL(hlth_ins_rec.end_date,dup_pe_hlthins_rec.end_date),
1766 X_ATTRIBUTE_CATEGORY => NVL(HLTH_INS_REC.ATTRIBUTE_CATEGORY, dup_pe_hlthins_rec.ATTRIBUTE_CATEGORY),
1767 X_ATTRIBUTE1 => NVL(HLTH_INS_REC.ATTRIBUTE1, dup_pe_hlthins_rec.ATTRIBUTE1),
1768 X_ATTRIBUTE2 => NVL(HLTH_INS_REC.ATTRIBUTE2, dup_pe_hlthins_rec.ATTRIBUTE2),
1769 X_ATTRIBUTE3 => NVL(HLTH_INS_REC.ATTRIBUTE3, dup_pe_hlthins_rec.ATTRIBUTE3),
1770 X_ATTRIBUTE4 => NVL(HLTH_INS_REC.ATTRIBUTE4, dup_pe_hlthins_rec.ATTRIBUTE4),
1771 X_ATTRIBUTE5 => NVL(HLTH_INS_REC.ATTRIBUTE5, dup_pe_hlthins_rec.ATTRIBUTE5),
1772 X_ATTRIBUTE6 => NVL(HLTH_INS_REC.ATTRIBUTE6, dup_pe_hlthins_rec.ATTRIBUTE6),
1773 X_ATTRIBUTE7 => NVL(HLTH_INS_REC.ATTRIBUTE7, dup_pe_hlthins_rec.ATTRIBUTE7),
1774 X_ATTRIBUTE8 => NVL(HLTH_INS_REC.ATTRIBUTE8, dup_pe_hlthins_rec.ATTRIBUTE8),
1775 X_ATTRIBUTE9 => NVL(HLTH_INS_REC.ATTRIBUTE9, dup_pe_hlthins_rec.ATTRIBUTE9),
1776 X_ATTRIBUTE10 => NVL(HLTH_INS_REC.ATTRIBUTE10, dup_pe_hlthins_rec.ATTRIBUTE10),
1777 X_ATTRIBUTE11 => NVL(HLTH_INS_REC.ATTRIBUTE11, dup_pe_hlthins_rec.ATTRIBUTE11),
1778 X_ATTRIBUTE12 => NVL(HLTH_INS_REC.ATTRIBUTE12, dup_pe_hlthins_rec.ATTRIBUTE12),
1779 X_ATTRIBUTE13 => NVL(HLTH_INS_REC.ATTRIBUTE13, dup_pe_hlthins_rec.ATTRIBUTE13),
1780 X_ATTRIBUTE14 => NVL(HLTH_INS_REC.ATTRIBUTE14, dup_pe_hlthins_rec.ATTRIBUTE14),
1781 X_ATTRIBUTE15 => NVL(HLTH_INS_REC.ATTRIBUTE15, dup_pe_hlthins_rec.ATTRIBUTE15),
1782 X_ATTRIBUTE16 => NVL(HLTH_INS_REC.ATTRIBUTE16, dup_pe_hlthins_rec.ATTRIBUTE16),
1783 X_ATTRIBUTE17 => NVL(HLTH_INS_REC.ATTRIBUTE17, dup_pe_hlthins_rec.ATTRIBUTE17),
1784 X_ATTRIBUTE18 => NVL(HLTH_INS_REC.ATTRIBUTE18, dup_pe_hlthins_rec.ATTRIBUTE18),
1785 X_ATTRIBUTE19 => NVL(HLTH_INS_REC.ATTRIBUTE19, dup_pe_hlthins_rec.ATTRIBUTE19),
1786 X_ATTRIBUTE20 => NVL(HLTH_INS_REC.ATTRIBUTE20, dup_pe_hlthins_rec.ATTRIBUTE20),
1787 x_mode =>'R');
1788 UPDATE igs_ad_hlth_ins_int
1789 SET error_code = NULL,
1790 match_ind = cst_mi_val_18,
1791 status = cst_stat_val_1
1792 WHERE interface_hlth_id = hlth_ins_rec.interface_hlth_id;
1793 EXCEPTION
1794 WHEN OTHERS THEN
1795 UPDATE igs_ad_hlth_ins_int
1796 SET error_code = 'E014',
1797 status = '3'
1798 WHERE interface_hlth_id = hlth_ins_rec.interface_hlth_id;
1799 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
1800
1801 IF (l_request_id IS NULL) THEN
1802 l_request_id := fnd_global.conc_request_id;
1803 END IF;
1804
1805 l_label := 'igs.plsql.igs_ad_imp_007.prc_pe_hlth_ins_dtls.exception2';
1806
1807 l_debug_str := 'IGS_AD_IMP_007.PRC_PE_HLTH_INS_DTLS ' ||
1808 'Interface Health Id : ' || (hlth_ins_rec.INTERFACE_HLTH_ID) ||
1809 'Status : 3' || 'ErrorCode : E014 update Failed ' || SQLERRM;
1810
1811 fnd_log.string_with_context( fnd_log.level_exception,
1812 l_label,
1813 l_debug_str, NULL,
1814 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1815 END IF;
1816
1817 IF l_enable_log = 'Y' THEN
1818 igs_ad_imp_001.logerrormessage(hlth_ins_rec.INTERFACE_HLTH_ID,'E014','IGS_AD_HLTH_INS_INT_ALL');
1819 END IF;
1820 END;
1821 END IF; -- if match_ind
1822 END IF; -- if rule
1823 ELSE
1824 crt_pe_hlth_ins(hlth_ins_rec,
1825 l_error_code,
1826 l_status);
1827
1828 UPDATE IGS_AD_HLTH_INS_INT
1829 SET status = l_status,
1830 error_code = l_error_code
1831 WHERE INTERFACE_HLTH_ID = hlth_ins_rec.INTERFACE_HLTH_ID;
1832 END IF; -- if dup
1833 END;
1834 END IF; -- if l_check
1835 IF l_processed_records = 100 THEN
1836 COMMIT;
1837 l_processed_records := 0;
1838 END IF;
1839 END LOOP; -- end for
1840 END prc_pe_hlth_ins_dtls;
1841 --Health and insurance details
1842
1843 PROCEDURE prc_pe_hlth_dtls
1844 ( P_SOURCE_TYPE_ID IN NUMBER,
1845 P_BATCH_ID IN NUMBER )
1846 AS
1847 /*
1848 || Created By : adhawan
1849 || Created On :19-nov-2001
1850 || Purpose : This procedure process the Immunization Details
1851 || Known limitations, enhancements or remarks :
1852 || Change History :
1853 || Who When What
1854 || npalanis 6-JAN-2003 Bug : 2734697
1855 || code added to commit after import of every
1856 || 100 records .New variable l_processed_records added
1857 || adhawan 12-Nov-2001 Bug no.2103692:Person Interface DLD
1858 || New procedure created for processing the immunization details, health details
1859 || of the person
1860 ||
1861 || (reverse chronological order - newest change first)
1862 */
1863 BEGIN
1864 prc_pe_immu_dtls(P_SOURCE_TYPE_ID, P_BATCH_ID);
1865 prc_pe_hlth_ins_dtls (P_SOURCE_TYPE_ID, P_BATCH_ID);
1866 END prc_pe_hlth_dtls;
1867
1868 PROCEDURE prc_pe_id_types
1869 (
1870 P_SOURCE_TYPE_ID IN NUMBER,
1871 P_BATCH_ID IN NUMBER ) AS
1872 /*
1873 || Created By :
1874 || Created On :
1875 || Purpose : This procedure process the Application
1876 || Known limitations, enhancements or remarks :
1877 || Change History :
1878 || Who When What
1879 || pkpatel 15-JAN-2003 Bug NO: 2397876
1880 || Added all the missing validations and corresponding error codes
1881 || npalanis 6-JAN-2003 Bug : 2734697
1882 || code added to commit after import of every
1883 || 100 records .New variable l_processed_records added
1884 || pkpatel 01-DEC-2002 Bug NO: 2599109 (Sevis DLD)
1885 || Added the validation for REGION_CODE
1886 || npalanis 26-May-2002 Bug no - 2377751
1887 || New error codes registered and added
1888 || sarakshi 12-Nov-2001 Bug no.2103692:Person Interface DLD
1889 || Added the DFF validation before insert/update to the oss table, also in
1890 || the call to insert_row/update_row to the oss table adding the dff columns
1891 || (reverse chronological order - newest change first)
1892 */
1893
1894
1895 -- Logic for IGS_AD_IMPORT_PERSON_ID_TYPES
1896 -- Create the cursor using the following select statement.
1897
1898 CURSOR API(cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE) IS
1899 SELECT mi.*, i.person_id
1900 FROM igs_ad_api_int_all mi, igs_ad_interface_all I
1901 WHERE mi.interface_run_id = cp_interface_run_id
1902 AND mi.interface_id = i.interface_id
1903 AND i.interface_run_id = cp_interface_run_id
1904 AND mi.status = '2'
1905 AND i.status = '1';
1906
1907 CURSOR check_dur_cur(api_rec api%ROWTYPE) IS
1908 SELECT ROWID,pi.*
1909 FROM igs_pe_alt_pers_id pi
1910 WHERE pe_person_id = api_rec.person_id
1911 AND api_person_id = api_rec.alternate_id
1912 AND UPPER(person_id_type) = UPPER(api_rec.person_id_type)
1913 AND TRUNC(start_dt) = TRUNC(api_rec.start_dt);
1914
1915 CURSOR source_type_cur(cp_source_type igs_pe_src_types_all.source_type%TYPE) Is
1916 SELECT source_type_id
1917 FROM igs_pe_src_types_all
1918 WHERE source_type = cp_source_type;
1919
1920 check_dur_rec check_dur_cur%ROWTYPE;
1921 lnDupExist VARCHAR2(1);
1922 l_exists VARCHAR2(1);
1923 l_rule VARCHAR2(1);
1924 lvcRecordExist VARCHAR2(1);
1925 l_error_code VARCHAR2(10);
1926 l_status VARCHAR2(10);
1927 l_processed_records NUMBER(5) := 0;
1928 l_prog_label VARCHAR2(4000);
1929 l_label VARCHAR2(4000);
1930 l_debug_str VARCHAR2(4000);
1931 l_enable_log VARCHAR2(1);
1932 l_request_id NUMBER(10);
1933 l_message_name VARCHAR2(30);
1934 l_app VARCHAR2(50);
1935 l_interface_run_id igs_ad_interface_all.interface_run_id%TYPE;
1936 l_ucas_action VARCHAR2(1);
1937 l_ucas_error_code VARCHAR2(10);
1938 l_call_ucas_user_hook BOOLEAN;
1939 l_source_type_id1 NUMBER;
1940 l_source_type_id2 NUMBER;
1941
1942 FUNCTION validate_api(p_api_rec IN api%ROWTYPE )
1943 RETURN BOOLEAN AS
1944 /*
1945 || Created By : pkpatel
1946 || Created On : 10-JUN-2002
1947 || Purpose : Bug No:2402077 Validate the Person ID type and Format mask for Alternate ID
1948 || Known limitations, enhancements or remarks :
1949 || Change History :
1950 || Who When What
1951 || skpandey 09-Jan-2006 Bug#4178224
1952 || Changed the definition of region_cd_cur cursor as a part of New Geography Model
1953 || gmaheswa 29-Sep-2004 BUG 3787210 Added Closed indicator check for the Alternate Person Id type.
1954 || (reverse chronological order - newest change first)
1955 */
1956 l_error_code VARCHAR2(30);
1957 l_exists VARCHAR2(1);
1958
1959 CURSOR api_type_cur(cp_person_id_type igs_pe_person_id_typ.person_id_type%TYPE) IS
1960 SELECT format_mask, region_ind
1961 FROM igs_pe_person_id_typ
1962 WHERE person_id_type = cp_person_id_type
1963 AND closed_ind = 'N';
1964
1965 CURSOR region_cd_cur(cp_geography_type hz_geographies.geography_type%TYPE, cp_geography_cd hz_geographies.geography_code%TYPE, cp_country_cd hz_geographies.country_code%TYPE) IS
1966 SELECT 'X'
1967 FROM hz_geographies
1968 WHERE GEOGRAPHY_TYPE = cp_geography_type
1969 AND geography_code = cp_geography_cd
1970 AND COUNTRY_CODE = cp_country_cd;
1971
1972 api_type_rec api_type_cur%ROWTYPE;
1973
1974 BEGIN
1975
1976 --validate Alternate Person ID descriptive Flex field
1977 IF NOT igs_ad_imp_018.validate_desc_flex(
1978 p_attribute_category =>p_api_rec.attribute_category,
1979 p_attribute1 =>p_api_rec.attribute1 ,
1980 p_attribute2 =>p_api_rec.attribute2 ,
1981 p_attribute3 =>p_api_rec.attribute3 ,
1982 p_attribute4 =>p_api_rec.attribute4 ,
1983 p_attribute5 =>p_api_rec.attribute5 ,
1984 p_attribute6 =>p_api_rec.attribute6 ,
1985 p_attribute7 =>p_api_rec.attribute7 ,
1986 p_attribute8 =>p_api_rec.attribute8 ,
1987 p_attribute9 =>p_api_rec.attribute9 ,
1988 p_attribute10 =>p_api_rec.attribute10 ,
1989 p_attribute11 =>p_api_rec.attribute11 ,
1990 p_attribute12 =>p_api_rec.attribute12 ,
1991 p_attribute13 =>p_api_rec.attribute13 ,
1992 p_attribute14 =>p_api_rec.attribute14 ,
1993 p_attribute15 =>p_api_rec.attribute15 ,
1994 p_attribute16 =>p_api_rec.attribute16 ,
1995 p_attribute17 =>p_api_rec.attribute17 ,
1996 p_attribute18 =>p_api_rec.attribute18 ,
1997 p_attribute19 =>p_api_rec.attribute19 ,
1998 p_attribute20 =>p_api_rec.attribute20 ,
1999 p_desc_flex_name =>'IGS_PE_ALT_PERS_ID_FLEX' ) THEN
2000
2001 l_error_code:='E255';
2002 RAISE NO_DATA_FOUND;
2003 END IF;
2004 --validate Person ID type
2005 OPEN api_type_cur(p_api_rec.person_id_type);
2006 FETCH api_type_cur INTO api_type_rec;
2007 IF api_type_cur%NOTFOUND THEN
2008 CLOSE api_type_cur;
2009 l_error_code:='E258';
2010 RAISE NO_DATA_FOUND;
2011 ELSE
2012 CLOSE api_type_cur;
2013 END IF;
2014
2015 -- Validate the format mask
2016 IF api_type_rec.format_mask IS NOT NULL THEN
2017 IF NOT igs_en_val_api.fm_equal(p_api_rec.alternate_id,api_type_rec.format_mask) THEN
2018 l_error_code:='E268';
2019 RAISE NO_DATA_FOUND;
2020 END IF;
2021 END IF;
2022
2023 -- Validation for Region Code
2024 IF api_type_rec.region_ind IS NULL OR api_type_rec.region_ind = 'N' THEN
2025 IF p_api_rec.region_cd IS NOT NULL THEN
2026 l_error_code:='E573';
2027 RAISE NO_DATA_FOUND;
2028 END IF;
2029 ELSE
2030 IF p_api_rec.region_cd IS NULL THEN
2031 l_error_code:='E574';
2032 RAISE NO_DATA_FOUND;
2033 ELSE
2034 OPEN region_cd_cur('STATE',p_api_rec.region_cd, FND_PROFILE.VALUE('OSS_COUNTRY_CODE'));
2035 FETCH region_cd_cur INTO l_exists;
2036 IF region_cd_cur%NOTFOUND THEN
2037 CLOSE region_cd_cur;
2038 l_error_code:='E575';
2039 RAISE NO_DATA_FOUND;
2040 END IF;
2041 CLOSE region_cd_cur;
2042 END IF;
2043 END IF;
2044
2045 RETURN TRUE;
2046
2047 EXCEPTION
2048 WHEN OTHERS THEN
2049 UPDATE igs_ad_api_int_all
2050 SET status = '3',
2051 error_code = l_error_code
2052 WHERE interface_api_id = p_api_rec.interface_api_id;
2053
2054 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
2055
2056 IF (l_request_id IS NULL) THEN
2057 l_request_id := fnd_global.conc_request_id;
2058 END IF;
2059
2060 l_label := 'igs.plsql.igs_ad_imp_007.validate_api.exception';
2061
2062 l_debug_str := 'Igs_Ad_Imp_007.PRC_PE_ID_TYPES.validate_api '
2063 ||'Validation Failed '
2064 ||'Interface_Api_Id:'
2065 ||IGS_GE_NUMBER.TO_CANN(p_api_rec.Interface_api_Id)
2066 ||' Status:3 '
2067 ||'Error Code:'||l_error_code||' ' || SQLERRM;
2068
2069 fnd_log.string_with_context( fnd_log.level_exception,
2070 l_label,
2071 l_debug_str, NULL,
2072 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
2073 END IF;
2074
2075 IF l_enable_log = 'Y' THEN
2076 igs_ad_imp_001.logerrormessage(p_api_rec.Interface_api_Id,l_error_code);
2077 END IF;
2078
2079 RETURN FALSE;
2080 END validate_api;
2081
2082 PROCEDURE crt_person_id_types
2083 (p_api_rec IN API%ROWTYPE,
2084 p_error_code OUT NOCOPY VARCHAR2,
2085 p_status OUT NOCOPY VARCHAR2) AS
2086 /*
2087 || Created By : nsinha
2088 || Created On : 22-JUN-2001
2089 || Purpose : This procedure process the Application
2090 || Known limitations, enhancements or remarks :
2091 || Change History :
2092 || Who When What
2093 || sarakshi 12-Nov-2001 Bug no.2103692:Person Interface DLD
2094 || Added the DFF validation before insert to the oss table, also in
2095 || the call to insert_row to the oss table adding the dff columns
2096 || pkpatel 25-JUN-2001 Bug no.1834307 :Modeling and Forecasting SDQ DLD
2097 || Modified code to refer igs_ad_interface_dtl_dscp_v instead of
2098 || igs_ad_interface due to change in signature of Igs_Ad_Imp_002.Update_Person .
2099 || (reverse chronological order - newest change first)
2100 */
2101
2102 l_rowid VARCHAR2(25);
2103 l_message_name VARCHAR2(30);
2104 l_app VARCHAR2(50);
2105 BEGIN
2106
2107 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
2108
2109 IF (l_request_id IS NULL) THEN
2110 l_request_id := fnd_global.conc_request_id;
2111 END IF;
2112
2113 l_label := 'igs.plsql.igs_ad_imp_007.crt_person_id_types.begin';
2114 l_debug_str := 'Interface Api Id : '|| p_api_rec.interface_api_id ;
2115
2116 fnd_log.string_with_context( fnd_log.level_procedure,
2117 l_label,
2118 l_debug_str, NULL,
2119 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
2120 END IF;
2121
2122 SAVEPOINT before_api_insert;
2123 igs_pe_alt_pers_id_pkg.insert_row(
2124 X_ROWID =>l_rowid,
2125 X_PE_PERSON_ID =>p_api_rec.person_id,
2126 X_API_PERSON_ID =>p_api_rec.alternate_id,
2127 X_PERSON_ID_TYPE =>p_api_rec.person_id_type,
2128 X_START_DT =>p_api_rec.start_dt,
2129 X_END_DT =>p_api_rec.end_dt,
2130 X_MODE =>'R',
2131 X_ATTRIBUTE_CATEGORY =>p_api_rec.attribute_category ,
2132 X_ATTRIBUTE1 =>p_api_rec.attribute1 ,
2133 X_ATTRIBUTE2 =>p_api_rec.attribute2 ,
2134 X_ATTRIBUTE3 =>p_api_rec.attribute3 ,
2135 X_ATTRIBUTE4 =>p_api_rec.attribute4 ,
2136 X_ATTRIBUTE5 =>p_api_rec.attribute5 ,
2137 X_ATTRIBUTE6 =>p_api_rec.attribute6 ,
2138 X_ATTRIBUTE7 =>p_api_rec.attribute7 ,
2139 X_ATTRIBUTE8 =>p_api_rec.attribute8 ,
2140 X_ATTRIBUTE9 =>p_api_rec.attribute9 ,
2141 X_ATTRIBUTE10 =>p_api_rec.attribute10 ,
2142 X_ATTRIBUTE11 =>p_api_rec.attribute11 ,
2143 X_ATTRIBUTE12 =>p_api_rec.attribute12 ,
2144 X_ATTRIBUTE13 =>p_api_rec.attribute13 ,
2145 X_ATTRIBUTE14 =>p_api_rec.attribute14 ,
2146 X_ATTRIBUTE15 =>p_api_rec.attribute15 ,
2147 X_ATTRIBUTE16 =>p_api_rec.attribute16 ,
2148 X_ATTRIBUTE17 =>p_api_rec.attribute17 ,
2149 X_ATTRIBUTE18 =>p_api_rec.attribute18 ,
2150 X_ATTRIBUTE19 =>p_api_rec.attribute19 ,
2151 X_ATTRIBUTE20 =>p_api_rec.attribute20 ,
2152 X_REGION_CD =>p_api_rec.region_cd );
2153
2154 p_error_code := NULL;
2155 p_status := '1';
2156
2157 EXCEPTION
2158 WHEN OTHERS THEN
2159 ROLLBACK TO before_api_insert;
2160 -- To find the message name raised from the TBH
2161 FND_MESSAGE.PARSE_ENCODED(FND_MESSAGE.GET_ENCODED, l_app, l_message_name);
2162 IF l_message_name IN ('IGS_PE_PERS_ID_PRD_OVRLP', 'IGS_PE_SSN_PERS_ID_PRD_OVRLP') THEN
2163 p_error_code := 'E560';
2164 p_status := '3';
2165 ELSIF l_message_name = 'IGS_PE_UNIQUE_PID' THEN
2166 p_error_code := 'E567';
2167 p_status := '3';
2168 ELSIF l_message_name = 'IGS_AD_STRT_DT_LESS_BIRTH_DT' THEN
2169 p_error_code := 'E222';
2170 p_status := '3';
2171 ELSIF l_message_name = 'IGS_GE_INVALID_DATE' THEN
2172 p_error_code := 'E208';
2173 p_status := '3';
2174 ELSE
2175 p_error_code := 'E007';
2176 p_status := '3';
2177 END IF;
2178
2179 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
2180
2181 IF (l_request_id IS NULL) THEN
2182 l_request_id := fnd_global.conc_request_id;
2183 END IF;
2184
2185 l_label := 'igs.plsql.igs_ad_imp_007.crt_person_id_types.exception ' || p_error_code;
2186
2187 l_debug_str := 'IGS_AD_IMP_007.PRC_PE_ID_TYPES.crt_person_id_types, Interface Api Id : '
2188 || p_api_rec.interface_api_id ||' Status : '|| p_status || ' ErrorCode : '||
2189 p_error_code|| ' SQLERRM: '||SQLERRM;
2190
2191 fnd_log.string_with_context( fnd_log.level_exception,
2192 l_label,
2193 l_debug_str, NULL,
2194 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
2195 END IF;
2196 IF l_enable_log = 'Y' THEN
2197 igs_ad_imp_001.logerrormessage(p_api_rec.interface_api_id,p_error_code);
2198 END IF;
2199
2200 END crt_person_id_types;
2201
2202
2203 BEGIN
2204 l_interface_run_id := igs_ad_imp_001.g_interface_run_id;
2205 l_enable_log := igs_ad_imp_001.g_enable_log;
2206 l_prog_label := 'igs.plsql.igs_ad_imp_007.prc_pe_id_types';
2207 l_label := 'igs.plsql.igs_ad_imp_007.prc_pe_id_types.';
2208
2209 -- Check whether UCAS user hook needs to be called. It is to be called only for UCAS PER and UCAS APPL source categories.
2210 OPEN source_type_cur('UCAS PER');
2211 FETCH source_type_cur INTO l_source_type_id1;
2212 CLOSE source_type_cur;
2213
2214 OPEN source_type_cur('UCAS APPL');
2215 FETCH source_type_cur INTO l_source_type_id2;
2216 CLOSE source_type_cur;
2217
2218 IF ((l_source_type_id1 = p_source_type_id) OR (l_source_type_id2 = p_source_type_id ))THEN
2219 l_call_ucas_user_hook := TRUE;
2220 END IF;
2221
2222 l_rule := IGS_AD_IMP_001.FIND_SOURCE_CAT_RULE(
2223 P_SOURCE_TYPE_ID => P_SOURCE_TYPE_ID,
2224 P_CATEGORY => 'PERSON_ID_TYPES');
2225
2226 -- If rule is E or I, then if the match_ind is not null, the combination is invalid
2227 IF l_rule IN ('E','I') THEN
2228 UPDATE igs_ad_api_int_all
2229 SET status = cst_stat_val_3,
2230 ERROR_CODE = cst_err_val_695 -- Error code depicting incorrect combination
2231 WHERE match_ind IS NOT NULL
2232 AND interface_run_id = l_interface_run_id
2233 AND status = cst_stat_val_2;
2234 END IF;
2235
2236 -- If rule is E and duplicate exists, update match_ind to 19 and status to 1
2237 IF l_rule = 'E' THEN
2238 UPDATE igs_ad_api_int_all mi
2239 SET status = cst_stat_val_1,
2240 match_ind = cst_mi_val_19
2241 WHERE mi.interface_run_id = l_interface_run_id
2242 AND mi.status = cst_stat_val_2
2243 AND EXISTS ( SELECT '1'
2244 FROM igs_pe_alt_pers_id pe, igs_ad_interface_all ii
2245 WHERE ii.interface_run_id = l_interface_run_id
2246 AND ii.interface_id = mi.interface_id
2247 AND ii.person_id = pe.pe_person_id
2248 AND UPPER(pe.api_person_id) = UPPER(mi.alternate_id)
2249 AND UPPER(pe.person_id_type) = UPPER(mi.person_id_type)
2250 AND TRUNC(pe.start_dt) = TRUNC(mi.start_dt) );
2251 END IF;
2252
2253 -- If rule is R and there match_ind is 18,19,22 or 23 then the records must have been
2254 -- processed in prior runs and didn't get updated .. update to status 1
2255 IF l_rule = 'R' THEN
2256 UPDATE igs_ad_api_int_all
2257 SET status = cst_stat_val_1
2258 WHERE interface_run_id = l_interface_run_id
2259 AND match_ind IN (cst_mi_val_18,cst_mi_val_19,cst_mi_val_22,cst_mi_val_23)
2260 AND status = cst_stat_val_2;
2261 END IF;
2262
2263 -- If rule is R and match_ind is neither 21 nor 25 then error
2264 IF l_rule = 'R' THEN
2265 UPDATE igs_ad_api_int_all
2266 SET status = cst_stat_val_3,
2267 ERROR_CODE = cst_err_val_695
2268 WHERE interface_run_id = l_interface_run_id
2269 AND (match_ind IS NOT NULL AND match_ind NOT IN (cst_mi_val_21,cst_mi_val_25))
2270 AND status = cst_stat_val_2;
2271 END IF;
2272
2273 -- If rule is R, set duplicated records with no discrepancy to status 1 and match_ind 23
2274 IF l_rule = 'R' THEN
2275 UPDATE igs_ad_api_int_all mi
2276 SET status = cst_stat_val_1,
2277 match_ind = cst_mi_val_23
2278 WHERE mi.interface_run_id = l_interface_run_id
2279 AND mi.match_ind IS NULL
2280 AND mi.status = cst_stat_val_2
2281 AND EXISTS ( SELECT '1'
2282 FROM igs_pe_alt_pers_id pe, igs_ad_interface_all ii
2283 WHERE ii.interface_run_id = l_interface_run_id
2284 AND ii.interface_id = mi.interface_id
2285 AND ii.person_id = pe.pe_person_id
2286 AND UPPER(pe.api_person_id) = UPPER(mi.alternate_id)
2287 AND UPPER(pe.person_id_type) = UPPER(mi.person_id_type)
2288 AND TRUNC(pe.start_dt) = TRUNC(mi.start_dt)
2289 AND NVL(TRUNC(pe.end_dt),igs_ge_date.igsdate('9999/01/01'))=NVL(TRUNC(mi.end_dt),igs_ge_date.igsdate('9999/01/01'))
2290 AND NVL(UPPER(pe.attribute_category), '*') = NVL(UPPER(mi.attribute_category), '*')
2291 AND NVL(UPPER(pe.region_cd), '*') = NVL(UPPER(mi.region_cd), '*')
2292 AND NVL(UPPER(pe.attribute1),'*!*') = NVL(UPPER(mi.attribute1),'*!*')
2293 AND NVL(UPPER(pe.attribute2),'*!*') = NVL(UPPER(mi.attribute2),'*!*')
2294 AND NVL(UPPER(pe.attribute3),'*!*') = NVL(UPPER(mi.attribute3),'*!*')
2295 AND NVL(UPPER(pe.attribute4),'*!*') = NVL(UPPER(mi.attribute4),'*!*')
2296 AND NVL(UPPER(pe.attribute5),'*!*') = NVL(UPPER(mi.attribute5),'*!*')
2297 AND NVL(UPPER(pe.attribute6),'*!*') = NVL(UPPER(mi.attribute6),'*!*')
2298 AND NVL(UPPER(pe.attribute7),'*!*') = NVL(UPPER(mi.attribute7),'*!*')
2299 AND NVL(UPPER(pe.attribute8),'*!*') = NVL(UPPER(mi.attribute8),'*!*')
2300 AND NVL(UPPER(pe.attribute9),'*!*') = NVL(UPPER(mi.attribute9),'*!*')
2301 AND NVL(UPPER(pe.attribute10),'*!*') = NVL(UPPER(mi.attribute10),'*!*')
2302 AND NVL(UPPER(pe.attribute11),'*!*') = NVL(UPPER(mi.attribute11),'*!*')
2303 AND NVL(UPPER(pe.attribute12),'*!*') = NVL(UPPER(mi.attribute12),'*!*')
2304 AND NVL(UPPER(pe.attribute13),'*!*') = NVL(UPPER(mi.attribute13),'*!*')
2305 AND NVL(UPPER(pe.attribute14),'*!*') = NVL(UPPER(mi.attribute14),'*!*')
2306 AND NVL(UPPER(pe.attribute15),'*!*') = NVL(UPPER(mi.attribute15),'*!*')
2307 AND NVL(UPPER(pe.attribute16),'*!*') = NVL(UPPER(mi.attribute16),'*!*')
2308 AND NVL(UPPER(pe.attribute17),'*!*') = NVL(UPPER(mi.attribute17),'*!*')
2309 AND NVL(UPPER(pe.attribute18),'*!*') = NVL(UPPER(mi.attribute18),'*!*')
2310 AND NVL(UPPER(pe.attribute19),'*!*') = NVL(UPPER(mi.attribute19),'*!*')
2311 AND NVL(UPPER(pe.attribute20),'*!*') = NVL(UPPER(mi.attribute20),'*!*')
2312 );
2313 END IF;
2314 -- If rule in R records still exist, they are duplicates and have discrepancy .. update status=3,match_ind=20
2315 IF l_rule = 'R' THEN
2316 UPDATE igs_ad_api_int_all mi
2317 SET status = cst_stat_val_3,
2318 match_ind = cst_mi_val_20
2319 WHERE mi.interface_run_id = l_interface_run_id
2320 AND mi.match_ind IS NULL
2321 AND mi.status = cst_stat_val_2
2322 AND EXISTS (SELECT '1'
2323 FROM igs_pe_alt_pers_id pe, igs_ad_interface_all ii
2324 WHERE mi.interface_run_id = l_interface_run_id
2325 AND ii.interface_id = mi.interface_id
2326 AND ii.person_id = pe.pe_person_id
2327 AND UPPER(pe.api_person_id) = UPPER(mi.alternate_id)
2328 AND UPPER(pe.person_id_type) = UPPER(mi.person_id_type)
2329 AND TRUNC(pe.start_dt) = TRUNC(mi.start_dt) );
2330 END IF;
2331
2332 FOR api_rec IN api(l_interface_run_id) LOOP
2333 l_processed_records := l_processed_records + 1 ;
2334 api_rec.person_id_type := UPPER(api_rec.person_id_type);
2335 api_rec.alternate_id := UPPER(api_rec.alternate_id);
2336 api_rec.start_dt := TRUNC(api_rec.start_dt);
2337 api_rec.end_dt := TRUNC(api_rec.end_dt);
2338 IF api_rec.start_dt IS NULL THEN
2339 api_rec.start_dt := TRUNC(SYSDATE);
2340 ELSE
2341 api_rec.start_dt := TRUNC(api_rec.start_dt);
2342 END IF;
2343 -- Validate the record. If successful then process.
2344 IF validate_api(api_rec) THEN
2345 -- Fetch this into a record called API_REC.
2346 -- Find the duplicate alternate person id for a person using the following SQL statement.
2347 check_dur_rec.person_id_type := NULL;
2348
2349 IF ((l_call_ucas_user_hook) AND (api_rec.person_id_type IN ('UCASID','NMASID','SWASID','GTTRID','UCASREGNO'))) THEN
2350
2351 igs_pe_pers_imp_001.validate_ucas_id(api_rec.alternate_id,api_rec.person_id,api_rec.person_id_type,l_ucas_action,l_ucas_error_code);
2352
2353 /* S - Skip the record.
2354 P - Proceed with the record.
2355 E - Error out the record.
2356 */
2357 IF (l_ucas_action = 'S')
2358 THEN
2359 -- Skip the record, no action reqd. Just mark it as processed.
2360 UPDATE IGS_AD_API_INT_ALL
2361 SET ERROR_CODE = NULL,
2362 STATUS = '1'
2363 WHERE INTERFACE_API_ID = API_REC.INTERFACE_API_ID;
2364 ELSIF (l_ucas_action = 'E')
2365 THEN
2366 -- Skip the record and set the error code.
2367 UPDATE IGS_AD_API_INT_ALL
2368 SET error_code = l_ucas_error_code,
2369 STATUS = '3'
2370 WHERE interface_api_id = api_rec.interface_api_id;
2371 ELSE
2372 -- Process the record in case of 'P'. Create a new record.
2373 crt_person_id_types(p_api_rec=>api_rec,p_error_code=>l_error_code,p_status=>l_status);
2374
2375 UPDATE IGS_AD_API_INT_ALL
2376 SET ERROR_CODE = l_error_code,
2377 STATUS = l_status
2378 WHERE INTERFACE_API_ID = API_REC.INTERFACE_API_ID;
2379
2380 END IF;
2381 ELSE -- Either source category is not UCAS PER / UCAS APPL or the ID being passed is not from the 4 UCAS IDs, do the normal processing.
2382
2383 OPEN check_dur_cur(api_rec);
2384 FETCH check_dur_cur INTO check_dur_rec;
2385 CLOSE check_dur_cur;
2386 IF check_dur_rec.person_id_type IS NOT NULL THEN
2387 --The person id type already exits. In this case find out NOCOPY the discrepancy action
2388 -- using the function. Call FIND_SOURCE_CAT_RULE with the following values.
2389 -- And the returned action is obtained in a variable lvcAction.
2390 IF l_rule = 'R' THEN
2391 IF api_rec.match_ind = '21' THEN
2392 BEGIN
2393 SAVEPOINT before_api_update;
2394
2395 igs_pe_alt_pers_id_pkg.update_row(
2396 x_rowid =>check_dur_rec.rowid,
2397 x_pe_person_id =>check_dur_rec.pe_person_id,
2398 x_api_person_id =>check_dur_rec.api_person_id,
2399 x_person_id_type =>NVL(api_rec.person_id_type,check_dur_rec.person_id_type),
2400 x_start_dt => NVL(api_rec.start_dt,check_dur_rec.start_dt),
2401 x_end_dt => NVL(api_rec.end_dt,check_dur_rec.end_dt),
2402 x_mode => 'R',
2403 X_ATTRIBUTE_CATEGORY =>NVL(api_rec.attribute_category ,check_dur_rec.attribute_category),
2404 X_ATTRIBUTE1 =>NVL(api_rec.attribute1 ,check_dur_rec.attribute1),
2405 X_ATTRIBUTE2 =>NVL(api_rec.attribute2 ,check_dur_rec.attribute2),
2406 X_ATTRIBUTE3 =>NVL(api_rec.attribute3 ,check_dur_rec.attribute3),
2407 X_ATTRIBUTE4 =>NVL(api_rec.attribute4 ,check_dur_rec.attribute4),
2408 X_ATTRIBUTE5 =>NVL(api_rec.attribute5 ,check_dur_rec.attribute5),
2409 X_ATTRIBUTE6 =>NVL(api_rec.attribute6 ,check_dur_rec.attribute6),
2410 X_ATTRIBUTE7 =>NVL(api_rec.attribute7 ,check_dur_rec.attribute7),
2411 X_ATTRIBUTE8 =>NVL(api_rec.attribute8 ,check_dur_rec.attribute8),
2412 X_ATTRIBUTE9 =>NVL(api_rec.attribute9 ,check_dur_rec.attribute9),
2413 X_ATTRIBUTE10 =>NVL(api_rec.attribute10 ,check_dur_rec.attribute10),
2414 X_ATTRIBUTE11 =>NVL(api_rec.attribute11 ,check_dur_rec.attribute11),
2415 X_ATTRIBUTE12 =>NVL(api_rec.attribute12 ,check_dur_rec.attribute12),
2416 X_ATTRIBUTE13 =>NVL(api_rec.attribute13 ,check_dur_rec.attribute13),
2417 X_ATTRIBUTE14 =>NVL(api_rec.attribute14 ,check_dur_rec.attribute14),
2418 X_ATTRIBUTE15 =>NVL(api_rec.attribute15 ,check_dur_rec.attribute15),
2419 X_ATTRIBUTE16 =>NVL(api_rec.attribute16 ,check_dur_rec.attribute16),
2420 X_ATTRIBUTE17 =>NVL(api_rec.attribute17 ,check_dur_rec.attribute17),
2421 X_ATTRIBUTE18 =>NVL(api_rec.attribute18 ,check_dur_rec.attribute18),
2422 X_ATTRIBUTE19 =>NVL(api_rec.attribute19 ,check_dur_rec.attribute19),
2423 X_ATTRIBUTE20 =>NVL(api_rec.attribute20 ,check_dur_rec.attribute20),
2424 X_REGION_CD =>NVL(api_rec.region_cd ,check_dur_rec.region_cd));
2425
2426 UPDATE IGS_AD_API_INT_ALL
2427 SET ERROR_CODE = NULL,
2428 MATCH_IND = cst_mi_val_18,
2429 STATUS = cst_stat_val_1
2430 WHERE INTERFACE_API_ID = API_REC.INTERFACE_API_ID;
2431
2432 EXCEPTION
2433 WHEN OTHERS THEN
2434
2435 ROLLBACK TO before_api_update;
2436 -- To find the message name raised from the TBH
2437 FND_MESSAGE.PARSE_ENCODED(FND_MESSAGE.GET_ENCODED, l_app, l_message_name);
2438
2439 IF l_message_name IN ('IGS_PE_PERS_ID_PRD_OVRLP', 'IGS_PE_SSN_PERS_ID_PRD_OVRLP') THEN
2440 l_error_code := 'E560';
2441
2442 ELSIF l_message_name = 'IGS_PE_ALT_END_DT_VAL' THEN
2443 l_error_code := 'E581';
2444
2445 ELSIF l_message_name = 'IGS_AD_STRT_DT_LESS_BIRTH_DT' THEN
2446 l_error_code := 'E222';
2447
2448 ELSIF l_message_name = 'IGS_GE_INVALID_DATE' THEN
2449 l_error_code := 'E208';
2450 ELSE
2451 l_error_code := 'E014';
2452 END IF;
2453
2454 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
2455
2456 IF (l_request_id IS NULL) THEN
2457 l_request_id := fnd_global.conc_request_id;
2458 END IF;
2459
2460 l_label := 'igs.plsql.igs_ad_imp_007.prc_pe_id_types.exception ' || l_error_code;
2461
2462 l_debug_str := 'IGS_AD_IMP_007.PRC_PE_ID_TYPES ' || 'Interface Api Id : '
2463 || api_rec.interface_api_id || 'SQLERRM '||SQLERRM ||' Status : 3 ' ||
2464 'ErrorCode : '|| l_error_code;
2465
2466 fnd_log.string_with_context( fnd_log.level_exception,
2467 l_label,
2468 l_debug_str, NULL,
2469 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
2470 END IF;
2471
2472 IF l_enable_log = 'Y' THEN
2473 igs_ad_imp_001.logerrormessage(api_rec.interface_api_id,l_error_code);
2474 END IF;
2475
2476 UPDATE IGS_AD_API_INT_ALL
2477 SET ERROR_CODE = l_error_code,
2478 STATUS = '3'
2479 WHERE INTERFACE_API_ID = API_REC.INTERFACE_API_ID;
2480
2481 END;
2482 END IF;
2483 ELSIF l_rule = 'I' THEN
2484 BEGIN
2485 SAVEPOINT before_api_update;
2486
2487 igs_pe_alt_pers_id_pkg.update_row(
2488 x_rowid =>check_dur_rec.rowid,
2489 x_pe_person_id =>check_dur_rec.pe_person_id,
2490 x_api_person_id =>check_dur_rec.api_person_id,
2491 x_person_id_type =>NVL(api_rec.person_id_type,check_dur_rec.person_id_type),
2492 x_start_dt => NVL(api_rec.start_dt,check_dur_rec.start_dt),
2493 x_end_dt => NVL(api_rec.end_dt,check_dur_rec.end_dt),
2494 x_mode => 'R',
2495 X_ATTRIBUTE_CATEGORY =>NVL(api_rec.attribute_category ,check_dur_rec.attribute_category),
2496 X_ATTRIBUTE1 =>NVL(api_rec.attribute1 ,check_dur_rec.attribute1),
2497 X_ATTRIBUTE2 =>NVL(api_rec.attribute2 ,check_dur_rec.attribute2),
2498 X_ATTRIBUTE3 =>NVL(api_rec.attribute3 ,check_dur_rec.attribute3),
2499 X_ATTRIBUTE4 =>NVL(api_rec.attribute4 ,check_dur_rec.attribute4),
2500 X_ATTRIBUTE5 =>NVL(api_rec.attribute5 ,check_dur_rec.attribute5),
2501 X_ATTRIBUTE6 =>NVL(api_rec.attribute6 ,check_dur_rec.attribute6),
2502 X_ATTRIBUTE7 =>NVL(api_rec.attribute7 ,check_dur_rec.attribute7),
2503 X_ATTRIBUTE8 =>NVL(api_rec.attribute8 ,check_dur_rec.attribute8),
2504 X_ATTRIBUTE9 =>NVL(api_rec.attribute9 ,check_dur_rec.attribute9),
2505 X_ATTRIBUTE10 =>NVL(api_rec.attribute10 ,check_dur_rec.attribute10),
2506 X_ATTRIBUTE11 =>NVL(api_rec.attribute11 ,check_dur_rec.attribute11),
2507 X_ATTRIBUTE12 =>NVL(api_rec.attribute12 ,check_dur_rec.attribute12),
2508 X_ATTRIBUTE13 =>NVL(api_rec.attribute13 ,check_dur_rec.attribute13),
2509 X_ATTRIBUTE14 =>NVL(api_rec.attribute14 ,check_dur_rec.attribute14),
2510 X_ATTRIBUTE15 =>NVL(api_rec.attribute15 ,check_dur_rec.attribute15),
2511 X_ATTRIBUTE16 =>NVL(api_rec.attribute16 ,check_dur_rec.attribute16),
2512 X_ATTRIBUTE17 =>NVL(api_rec.attribute17 ,check_dur_rec.attribute17),
2513 X_ATTRIBUTE18 =>NVL(api_rec.attribute18 ,check_dur_rec.attribute18),
2514 X_ATTRIBUTE19 =>NVL(api_rec.attribute19 ,check_dur_rec.attribute19),
2515 X_ATTRIBUTE20 =>NVL(api_rec.attribute20 ,check_dur_rec.attribute20),
2516 X_REGION_CD =>NVL(api_rec.region_cd ,check_dur_rec.region_cd));
2517
2518 UPDATE IGS_AD_API_INT_ALL
2519 SET ERROR_CODE = NULL,
2520 MATCH_IND = cst_mi_val_18,
2521 STATUS = cst_stat_val_1
2522 WHERE INTERFACE_API_ID = API_REC.INTERFACE_API_ID;
2523
2524 EXCEPTION
2525 WHEN OTHERS THEN
2526
2527 ROLLBACK TO before_api_update;
2528 -- To find the message name raised from the TBH
2529 FND_MESSAGE.PARSE_ENCODED(FND_MESSAGE.GET_ENCODED, l_app, l_message_name);
2530
2531 IF l_message_name IN ('IGS_PE_PERS_ID_PRD_OVRLP', 'IGS_PE_SSN_PERS_ID_PRD_OVRLP') THEN
2532 l_error_code := 'E560';
2533 ELSIF l_message_name = 'IGS_PE_ALT_END_DT_VAL' THEN
2534 l_error_code := 'E581';
2535 ELSIF l_message_name = 'IGS_AD_STRT_DT_LESS_BIRTH_DT' THEN
2536 l_error_code := 'E222';
2537 ELSIF l_message_name = 'IGS_GE_INVALID_DATE' THEN
2538 l_error_code := 'E208';
2539 ELSE
2540 l_error_code := 'E014';
2541 END IF;
2542
2543 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
2544
2545 IF (l_request_id IS NULL) THEN
2546 l_request_id := fnd_global.conc_request_id;
2547 END IF;
2548
2549 l_label := 'igs.plsql.igs_ad_imp_007.prc_pe_id_types.exception ' || l_error_code;
2550
2551 l_debug_str := 'IGS_AD_IMP_007.PRC_PE_ID_TYPES ' || 'Interface Api Id : '
2552 || api_rec.interface_api_id || ' SQLERRM '||SQLERRM ||' Status : 3 ' ||
2553 'ErrorCode : '|| l_error_code;
2554
2555 fnd_log.string_with_context( fnd_log.level_exception,
2556 l_label,
2557 l_debug_str, NULL,
2558 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
2559 END IF;
2560
2561 IF l_enable_log = 'Y' THEN
2562 igs_ad_imp_001.logerrormessage(api_rec.interface_api_id,l_error_code);
2563 END IF;
2564
2565 UPDATE IGS_AD_API_INT_ALL
2566 SET ERROR_CODE = l_error_code,
2567 STATUS = '3'
2568 WHERE INTERFACE_API_ID = API_REC.INTERFACE_API_ID;
2569
2570 END;
2571
2572 END IF;
2573 ELSE --If the record is not a duplicate one
2574 --Make a call to CREATE_PERSON_ID_TYPES
2575 --with the following parameters.
2576 crt_person_id_types
2577 (p_api_rec=>api_rec,
2578 p_error_code=>l_error_code,
2579 p_status=>l_status);
2580
2581 UPDATE IGS_AD_API_INT_ALL
2582 SET ERROR_CODE = l_error_code,
2583 STATUS = l_status
2584 WHERE INTERFACE_API_ID = API_REC.INTERFACE_API_ID;
2585
2586 END IF;
2587
2588 END IF; -- End of validation
2589 END IF;
2590 IF l_processed_records = 100 THEN
2591 COMMIT;
2592 l_processed_records := 0;
2593 END IF;
2594
2595 END LOOP;
2596
2597 END prc_pe_id_types;
2598
2599 -- PERSON INTERFACE DLD changes start here
2600 -- prc_pe_citizenship from IGSAD90 is moved here and renamed.
2601
2602 PROCEDURE prc_pe_hz_citizenship
2603 ( p_source_type_id IN NUMBER,
2604 p_batch_id IN NUMBER
2605 )
2606 /*
2607 || Created By : ssawhney
2608 || Created On : 15 november
2609 || Purpose : This procedure process the Internation Dtls, Fund Dep part
2610 || Known limitations, enhancements or remarks :
2611 || Change History :
2612 || Who When What
2613 || npalanis 6-JAN-2003 Bug : 2734697
2614 || code added to commit after import of every
2615 || 100 records .New variable l_processed_records added
2616 || npalanis 6-JUN-2002 Bug - 2391172
2617 || Reference to igs_pe_code_classes changed to
2618 || fnd or igs lookups,Date validations added
2619 */
2620 AS
2621 --cursor to select records from interface records
2622 CURSOR c_pcz(cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE) IS
2623 SELECT hii.*, i.person_id
2624 FROM igs_pe_citizen_int hii, igs_ad_interface_all i
2625 WHERE hii.interface_run_id = cp_interface_run_id
2626 AND i.interface_id = hii.interface_id
2627 AND i.interface_run_id = cp_interface_run_id
2628 AND hii.status = '2';
2629
2630 l_var VARCHAR2(1);
2631 l_rule VARCHAR2(1);
2632 l_error_code VARCHAR2(25);
2633 l_status VARCHAR2(25);
2634 l_dup_var BOOLEAN;
2635 l_last_update_date DATE;
2636 pcz_rec c_pcz%ROWTYPE;
2637 l_processed_records NUMBER(5) := 0;
2638 l_prog_label VARCHAR2(4000);
2639 l_label VARCHAR2(4000);
2640 l_debug_str VARCHAR2(4000);
2641 l_enable_log VARCHAR2(1);
2642 l_request_id NUMBER(10);
2643 l_interface_run_id igs_ad_interface_all.interface_run_id%TYPE;
2644 l_default_date DATE;
2645
2646 -- Local Procedure to create new records .
2647 PROCEDURE crt_pe_citizenship( pcz_rec IN c_pcz%ROWTYPE ,
2648 error_code OUT NOCOPY VARCHAR2,
2649 status OUT NOCOPY VARCHAR2) AS
2650 l_update_date1 DATE;
2651 l_return_status VARCHAR2(25);
2652 l_msg_count NUMBER;
2653 l_msg_data VARCHAR2(4000);
2654 l_smp VARCHAR2(25);
2655 l_smp1 VARCHAR2(25);
2656 p_error_code VARCHAR2(25);
2657 p_status VARCHAR2(25);
2658 l_p_last_update_date DATE;
2659 l_citizenship_id NUMBER;
2660
2661 -- gmuralid validation for country changed - SEVIS
2662 CURSOR c_valid_country(cp_territory_code VARCHAR2) IS
2663 SELECT territory_short_name
2664 FROM fnd_territories_vl
2665 WHERE territory_code = cp_territory_code;
2666
2667 CURSOR birth_dt_cur(cp_person_id igs_pe_person_base_v.person_id%TYPE) IS
2668 SELECT birth_date FROM
2669 igs_pe_person_base_v WHERE
2670 person_id = cp_person_id;
2671
2672 CURSOR date_overlap(PCZ_REC c_pcz%ROWTYPE) IS
2673 SELECT count(1) FROM HZ_CITIZENSHIP
2674 WHERE
2675 party_id = PCZ_REC.PERSON_ID AND
2676 UPPER(country_code) = UPPER(PCZ_REC.Country_code) AND
2677 ( NVL(TRUNC(PCZ_REC.end_date),IGS_GE_DATE.igsdate('9999/01/01')) BETWEEN TRUNC(date_recognized) AND NVL(TRUNC(end_date),IGS_GE_DATE.igsdate('9999/01/01'))
2678 OR
2679 TRUNC(PCZ_REC.date_recognized) BETWEEN TRUNC(date_recognized) AND NVL(TRUNC(end_date),IGS_GE_DATE.igsdate('9999/01/01'))
2680 OR
2681 ( TRUNC(PCZ_REC.date_recognized) < TRUNC(date_recognized) AND
2682 NVL(TRUNC(end_date),IGS_GE_DATE.igsdate('9999/01/01'))< NVL(TRUNC(PCZ_REC.end_date),IGS_GE_DATE.igsdate('9999/01/01')) ) );
2683
2684 l_birth_dt IGS_PE_PERSON.BIRTH_DT%TYPE;
2685 valid_country_rec c_valid_country%ROWTYPE;
2686 l_count NUMBER(3);
2687 l_last_update DATE;
2688 l_error VARCHAR2(30);
2689 l_object_version_number NUMBER;
2690 BEGIN
2691
2692 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
2693
2694 IF (l_request_id IS NULL) THEN
2695 l_request_id := fnd_global.conc_request_id;
2696 END IF;
2697
2698 l_label := 'igs.plsql.igs_ad_imp_007.crt_pe_citizenship.begin';
2699 l_debug_str := 'Interface Citizen Id : ' ||(pcz_rec.interface_citizenship_id) ;
2700
2701 fnd_log.string_with_context( fnd_log.level_procedure,
2702 l_label,
2703 l_debug_str, NULL,
2704 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
2705 END IF;
2706
2707 -- Start Validations
2708
2709 OPEN c_valid_country(pcz_rec.country_code);
2710 FETCH c_valid_country INTO valid_country_rec;
2711 IF c_valid_country%NOTFOUND THEN
2712 l_error := 'E125';
2713 RAISE no_data_found;
2714 ELSE
2715 l_error := null;
2716 END IF;
2717 IF pcz_rec.document_type IS NOT NULL THEN
2718 IF NOT
2719 (igs_pe_pers_imp_001.validate_lookup_type_code('CITIZENSHIP_DOC_TYPE',pcz_rec.document_type,8405))
2720 THEN
2721 l_error := 'E126';
2722 RAISE no_data_found;
2723 ELSE
2724 l_error := null;
2725 END IF;
2726 END IF;
2727 IF pcz_rec.date_recognized IS NULL THEN
2728 l_error := 'E257';
2729 RAISE no_data_found;
2730 ELSE
2731 IF pcz_rec.date_recognized > pcz_rec.end_date THEN
2732 l_error := 'E208';
2733 RAISE no_data_found;
2734 ELSE
2735 l_error := null;
2736 END IF;
2737
2738 -- nsidana Bug 3541714 : Added the validation to check that the date disowned in greater than the date recognized.
2739 IF NOT (pcz_rec.date_disowned BETWEEN pcz_rec.date_recognized AND NVL(PCZ_REC.end_date,IGS_GE_DATE.igsdate('9999/01/01'))) THEN
2740 l_error := 'E267';
2741 RAISE no_data_found;
2742 END IF;
2743
2744 OPEN birth_dt_cur(pcz_rec.person_id);
2745 FETCH birth_dt_cur INTO l_birth_dt;
2746 IF l_birth_dt IS NOT NULL THEN
2747 IF pcz_rec.date_recognized < l_birth_dt THEN
2748 l_error := 'E222';
2749 RAISE no_data_found;
2750 ELSE
2751 l_error :=null;
2752 END IF;
2753 END IF;
2754 CLOSE birth_dt_cur;
2755 END IF;
2756 IF pcz_rec.date_disowned IS NOT NULL THEN
2757 OPEN birth_dt_cur(pcz_rec.person_id);
2758 FETCH birth_dt_cur INTO l_birth_dt;
2759 IF l_birth_dt IS NOT NULL THEN
2760 IF pcz_rec.date_disowned < l_birth_dt THEN
2761 l_error := 'E258';
2762 RAISE no_data_found;
2763 ELSE
2764 l_error :=null;
2765 END IF;
2766 END IF;
2767 CLOSE birth_dt_cur;
2768 END IF;
2769 OPEN date_overlap(PCZ_REC);
2770 FETCH date_overlap INTO l_count;
2771 CLOSE date_overlap;
2772
2773 IF l_count > 0 THEN
2774 l_error := 'E228';
2775 Raise no_data_found;
2776 END IF;
2777
2778 -- all validations are ok. --insert
2779 -- Object version number is added to the signature of IGS_PE_CITIZENSHIP_PKG
2780 IGS_PE_CITIZENSHIPS_PKG.Citizenship(
2781 p_ACTION => 'INSERT',
2782 p_BIRTH_OR_SELECTED => null,
2783 p_COUNTRY_CODE => pcz_rec.country_code,
2784 p_DATE_DISOWNED => pcz_rec.DATE_DISOWNED,
2785 p_DATE_RECOGNIZED => pcz_rec.DATE_RECOGNIZED,
2786 p_DOCUMENT_REFERENCE => pcz_rec.DOCUMENT_REFERENCE,
2787 p_DOCUMENT_TYPE => pcz_rec.DOCUMENT_TYPE,
2788 p_PARTY_ID => pcz_rec.person_ID,
2789 p_END_DATE => pcz_rec.END_DATE,
2790 p_TERRITORY_SHORT_NAME => valid_country_rec.territory_short_name,
2791 p_LAST_UPDATE_DATE => l_last_update_date,
2792 p_CITIZENSHIP_ID => l_citizenship_id,
2793 p_RETURN_STATUS => l_return_status,
2794 p_MSG_COUNT => l_msg_count,
2795 p_MSG_DATA => l_msg_data,
2796 p_object_version_number => l_object_version_number
2797 );
2798
2799 IF l_return_status IN ('E','U') THEN -- error returned by HZ API
2800 error_code := 'E127'; -- failed in HZ insert
2801 status := '3';
2802 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
2803
2804 IF (l_request_id IS NULL) THEN
2805 l_request_id := fnd_global.conc_request_id;
2806 END IF;
2807
2808 l_label := 'igs.plsql.IGS_AD_IMP_007.crt_pe_citizenship.exception: '||'E127';
2809
2810 l_debug_str := 'IGS_AD_IMP_007.crt_pe_citizenship Insert into HZ table failed. '
2811 || 'Interface Citizen Id : '
2812 || (pcz_rec.interface_citizenship_id)
2813 || 'Status : 3' || 'ErrorCode : E127'|| l_msg_data;
2814
2815 fnd_log.string_with_context( fnd_log.level_exception,
2816 l_label,
2817 l_debug_str, NULL,
2818 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
2819 END IF;
2820
2821 IF l_enable_log = 'Y' THEN
2822 igs_ad_imp_001.logerrormessage(pcz_rec.interface_citizenship_id,'E127','IGS_PE_CITIZEN_INT');
2823 END IF;
2824
2825 ELSE
2826 status := '1';
2827 UPDATE igs_pe_citizen_int
2828 SET status='1'
2829 WHERE interface_citizenship_id= pcz_rec.interface_citizenship_id;
2830 END IF;
2831
2832 EXCEPTION
2833 WHEN OTHERS THEN
2834 IF c_valid_country%ISOPEN THEN
2835 CLOSE c_valid_country;
2836 END IF;
2837 error_code:= l_error;
2838 status:= '3';
2839 -- there can be a case when unhandled exception is raised in HZ package then the l_error will not be set
2840
2841 IF l_error IS NULL THEN
2842 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
2843
2844 IF (l_request_id IS NULL) THEN
2845 l_request_id := fnd_global.conc_request_id;
2846 END IF;
2847
2848 l_label := 'igs.plsql.igs_ad_imp_007.crt_pe_citizenship.exception';
2849
2850 l_debug_str := 'IGS_AD_IMP_007.crt_pe_citizenship Create Row failed'
2851 || 'Interface Citizen Id : '
2852 || (pcz_rec.interface_citizenship_id)
2853 || 'Status : 3' || ' SQLERRM:' || SQLERRM;
2854
2855 fnd_log.string_with_context( fnd_log.level_exception,
2856 l_label,
2857 l_debug_str, NULL,
2858 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
2859 END IF;
2860 error_code :='E127' ;
2861 END IF;
2862
2863 IF l_enable_log = 'Y' THEN
2864 igs_ad_imp_001.logerrormessage(pcz_rec.interface_citizenship_id,l_error,'IGS_PE_CITIZEN_INT');
2865 END IF;
2866
2867 UPDATE igs_pe_citizen_int
2868 SET error_code = l_error, status ='3'
2869 WHERE interface_citizenship_id= pcz_rec.interface_citizenship_id;
2870 END crt_pe_citizenship; -- end local proc to create new record
2871
2872
2873 --start local proc to check if record already exists in system table , duplicate check
2874 PROCEDURE check_dup_citizenship(p_dup_var OUT NOCOPY BOOLEAN,
2875 p_person_id IN NUMBER,
2876 p_country_code IN VARCHAR2,
2877 p_date_recognized IN HZ_CITIZENSHIP.DATE_RECOGNIZED%TYPE) AS
2878 l_count VARCHAR2(1);
2879 BEGIN
2880
2881
2882 SELECT 'X'
2883 INTO l_count
2884 FROM hz_citizenship
2885 WHERE party_id = p_person_id
2886 AND country_code = p_country_code
2887 AND TRUNC(date_recognized) = TRUNC(p_date_recognized) ; -- end_date IS NULL check removed.
2888
2889 p_dup_var := TRUE;
2890
2891 EXCEPTION
2892 WHEN OTHERS THEN
2893 p_dup_var:=FALSE;
2894 END check_dup_citizenship;
2895 --end local proc to check if record already exists in system table , duplicate check
2896
2897 --start local proc for updating existing records based on discrepancy rule
2898 PROCEDURE upd_pe_citizenship(pcz_rec IN c_pcz%ROWTYPE,
2899 error_code OUT NOCOPY VARCHAR2,
2900 status OUT NOCOPY VARCHAR2) AS
2901 l_update_date1 DATE;
2902 l_return_status VARCHAR2(25);
2903 l_msg_count VARCHAR2(30);
2904 l_msg_data VARCHAR2(2000);
2905 p_error_code VARCHAR2(25);
2906 p_status VARCHAR2(25);
2907 l_citizenship_id NUMBER;
2908 l_error VARCHAR2(25);
2909 l_last_update_date DATE;
2910
2911 CURSOR c_valid_country(cp_territory_code VARCHAR2) IS
2912 SELECT territory_short_name
2913 FROM fnd_territories_vl
2914 WHERE territory_code = cp_territory_code;
2915
2916 CURSOR c_null_hndlg (pcz_rec IN c_pcz%ROWTYPE) IS
2917 SELECT *
2918 FROM hz_citizenship
2919 WHERE party_id = pcz_rec.person_id
2920 AND country_code =pcz_rec.country_code
2921 AND date_recognized = pcz_rec.date_recognized;
2922
2923 CURSOR birth_dt_cur(cp_person_id igs_pe_person_base_v.person_id%TYPE) IS
2924 SELECT birth_date
2925 FROM igs_pe_person_base_v
2926 WHERE person_id = pcz_rec.person_id;
2927
2928 CURSOR date_overlap(PCZ_REC c_pcz%ROWTYPE) IS
2929 SELECT COUNT(1)
2930 FROM HZ_CITIZENSHIP
2931 WHERE
2932 party_id = PCZ_REC.PERSON_ID AND
2933 UPPER(country_code) = UPPER(PCZ_REC.Country_code) AND
2934 TRUNC(date_recognized) <> TRUNC(PCZ_REC.date_recognized) AND
2935 ( NVL(TRUNC(PCZ_REC.end_date),IGS_GE_DATE.igsdate('9999/01/01')) BETWEEN TRUNC(date_recognized) AND NVL(TRUNC(end_date),IGS_GE_DATE.igsdate('9999/01/01'))
2936 OR
2937 TRUNC(PCZ_REC.date_recognized) BETWEEN TRUNC(date_recognized) AND NVL(TRUNC(end_date),IGS_GE_DATE.igsdate('9999/01/01'))
2938 OR
2939 ( TRUNC(PCZ_REC.date_recognized) < TRUNC(date_recognized) AND
2940 NVL(TRUNC(end_date),IGS_GE_DATE.igsdate('9999/01/01'))< NVL(TRUNC(PCZ_REC.end_date),IGS_GE_DATE.igsdate('9999/01/01')) ) );
2941
2942 l_count NUMBER(3);
2943 l_birth_dt IGS_PE_PERSON_V.BIRTH_DT%TYPE;
2944 null_hndlg_rec c_null_hndlg%ROWTYPE;
2945 valid_country_rec c_valid_country%ROWTYPE;
2946
2947 BEGIN
2948
2949 OPEN c_null_hndlg (pcz_rec);
2950 FETCH c_null_hndlg INTO null_hndlg_rec;
2951 CLOSE c_null_hndlg;
2952
2953 -- Start Validations
2954
2955 OPEN c_valid_country(pcz_rec.country_code);
2956 FETCH c_valid_country INTO valid_country_rec;
2957 IF c_valid_country%NOTFOUND THEN
2958 l_error := 'E125';
2959 RAISE no_data_found;
2960 ELSE
2961 l_error := null;
2962 CLOSE c_valid_country;
2963 END IF;
2964
2965
2966 IF pcz_rec.document_type IS NOT NULL THEN
2967 IF NOT
2968 (igs_pe_pers_imp_001.validate_lookup_type_code('CITIZENSHIP_DOC_TYPE',pcz_rec.document_type,8405))
2969 THEN
2970 l_error := 'E126';
2971 RAISE no_data_found;
2972 ELSE
2973 l_error := null;
2974 END IF;
2975 END IF;
2976
2977 IF pcz_rec.date_recognized IS NULL THEN
2978 l_error := 'E257';
2979 RAISE no_data_found;
2980 ELSE
2981 IF pcz_rec.date_recognized > pcz_rec.end_date THEN
2982 l_error := 'E208';
2983 RAISE no_data_found;
2984 END IF;
2985
2986 OPEN birth_dt_cur(pcz_rec.person_id);
2987 FETCH birth_dt_cur INTO l_birth_dt;
2988 IF l_birth_dt IS NOT NULL THEN
2989 IF pcz_rec.date_recognized < l_birth_dt THEN
2990 l_error := 'E222';
2991 RAISE no_data_found;
2992 END IF;
2993 END IF;
2994 CLOSE birth_dt_cur;
2995 END IF;
2996
2997 IF pcz_rec.date_disowned IS NOT NULL THEN
2998
2999 OPEN birth_dt_cur(pcz_rec.person_id);
3000 FETCH birth_dt_cur INTO l_birth_dt;
3001 IF l_birth_dt IS NOT NULL THEN
3002 IF pcz_rec.date_disowned < l_birth_dt THEN
3003 l_error := 'E258';
3004 RAISE no_data_found;
3005 ELSE
3006 l_error :=null;
3007 END IF;
3008 END IF;
3009 CLOSE birth_dt_cur;
3010
3011 IF NOT (pcz_rec.date_disowned BETWEEN pcz_rec.date_recognized AND NVL(PCZ_REC.end_date,IGS_GE_DATE.igsdate('9999/01/01'))) THEN
3012 l_error := 'E267';
3013 RAISE no_data_found;
3014 END IF;
3015 END IF;
3016
3017 OPEN date_overlap(pcz_rec) ;
3018 FETCH date_overlap INTO l_count;
3019 CLOSE date_overlap;
3020
3021 IF l_count > 0 THEN
3022 l_error := 'E228';
3023 Raise no_data_found;
3024 END IF;
3025
3026
3027 -- Object version number is added to the signature of IGS_PE_CITIZENSHIP_PKG
3028 IGS_PE_CITIZENSHIPS_PKG.Citizenship(
3029 p_ACTION => 'UPDATE',
3030 p_BIRTH_OR_SELECTED => null,
3031 p_COUNTRY_CODE => NVL( pcz_rec.country_code,null_hndlg_rec.country_code),
3032 p_DATE_DISOWNED => NVL(pcz_rec.date_disowned,null_hndlg_rec.date_disowned),
3033 p_DATE_RECOGNIZED => NVL(pcz_rec.date_recognized,null_hndlg_rec.date_recognized),
3034 p_DOCUMENT_REFERENCE => NVL(pcz_rec.document_reference,null_hndlg_rec.document_reference),
3035 p_DOCUMENT_TYPE => NVL(pcz_rec.document_type,null_hndlg_rec.document_type),
3036 p_PARTY_ID => NVL(pcz_rec.person_id,null_hndlg_rec.party_id),
3037 p_END_DATE => NVL(pcz_rec.end_date,null_hndlg_rec.end_date),
3038 p_TERRITORY_SHORT_NAME => valid_country_rec.territory_short_name,
3039 p_LAST_UPDATE_DATE => l_last_update_date,
3040 p_CITIZENSHIP_ID => null_hndlg_rec.citizenship_id,
3041 p_RETURN_STATUS => l_return_status,
3042 p_MSG_COUNT => l_msg_count,
3043 p_MSG_DATA => l_msg_data,
3044 p_OBJECT_VERSION_NUMBER => null_hndlg_rec.object_version_number
3045 );
3046
3047 IF l_return_status IN ('E','U') THEN
3048 error_code := 'E128'; -- updation failed in HZ
3049 status := '3';
3050 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
3051
3052 IF (l_request_id IS NULL) THEN
3053 l_request_id := fnd_global.conc_request_id;
3054 END IF;
3055
3056 l_label := 'igs.plsql.IGS_AD_IMP_007.upd_pe_citizenship.exception: '||'E128';
3057
3058 l_debug_str := 'IGS_AD_IMP_007.upd_pe_citizenship Update into HZ table failed. '
3059 || 'Interface Citizen Id : '
3060 || (pcz_rec.interface_citizenship_id)
3061 || ' Status : 3 ' || 'ErrorCode : E128 msg_data: ' || l_msg_data;
3062
3063 fnd_log.string_with_context( fnd_log.level_exception,
3064 l_label,
3065 l_debug_str, NULL,
3066 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
3067 END IF;
3068
3069 IF l_enable_log = 'Y' THEN
3070 igs_ad_imp_001.logerrormessage(pcz_rec.interface_citizenship_id,'E128','IGS_PE_CITIZEN_INT');
3071 END IF;
3072
3073 ELSE
3074 status := '1';
3075 error_code :=NULL;
3076 END IF;
3077 EXCEPTION
3078 WHEN OTHERS THEN
3079
3080 error_Code:= l_error; -- discrepency rule check failed
3081 status:= '3';
3082
3083 IF l_error IS NULL THEN
3084 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
3085
3086 IF (l_request_id IS NULL) THEN
3087 l_request_id := fnd_global.conc_request_id;
3088 END IF;
3089
3090 l_label := 'igs.plsql.igs_ad_imp_007.upd_pe_citizenship.exception';
3091
3092 l_debug_str := 'IGS_AD_IMP_007.upd_pe_citizenship '
3093 || 'Interface Citizen Id : '
3094 || (pcz_rec.interface_citizenship_id)
3095 || 'Status : 3' || ' SQLERRM:' || SQLERRM;
3096
3097 fnd_log.string_with_context( fnd_log.level_exception,
3098 l_label,
3099 l_debug_str, NULL,
3100 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
3101 END IF;
3102 error_Code:= 'E128';
3103 END IF;
3104
3105 IF l_enable_log = 'Y' THEN
3106 igs_ad_imp_001.logerrormessage(pcz_rec.interface_citizenship_id,l_error,'IGS_PE_CITIZEN_INT');
3107 END IF;
3108
3109 IF c_valid_country%ISOPEN THEN
3110 CLOSE c_valid_country;
3111 END IF;
3112
3113 END upd_pe_citizenship;
3114 --end local proc for updating existing records based on discrepancy rule
3115
3116 BEGIN
3117 l_interface_run_id := igs_ad_imp_001.g_interface_run_id;
3118 l_enable_log := igs_ad_imp_001.g_enable_log;
3119 l_prog_label := 'igs.plsql.igs_ad_imp_007.prc_pe_hz_citizenship';
3120 l_label := 'igs.plsql.igs_ad_imp_007.prc_pe_hz_citizenship.';
3121
3122 l_rule :=igs_ad_imp_001.find_source_cat_rule(p_source_type_id,'PERSON_INTERNATIONAL_DETAILS');
3123
3124 l_default_date := igs_ge_date.igsdate('9999/01/01');
3125
3126 --1. If rule is E or I, then if the match_ind is not null, the combination is invalid
3127 IF l_rule IN ('E','I') THEN
3128 UPDATE igs_pe_citizen_int
3129 SET status = cst_stat_val_3,
3130 ERROR_CODE = cst_err_val_695 -- Error code depicting incorrect combination
3131 WHERE match_ind IS NOT NULL
3132 AND interface_run_id = l_interface_run_id
3133 AND status = cst_stat_val_2;
3134 END IF;
3135
3136
3137 --2. If rule is E and duplicate exists, update match_ind to 19 and status to 1
3138 IF l_rule = 'E' THEN
3139 UPDATE igs_pe_citizen_int mi
3140 SET status = cst_stat_val_1,
3141 match_ind = cst_mi_val_19
3142 WHERE mi.interface_run_id = l_interface_run_id
3143 AND mi.status = cst_stat_val_2
3144 AND EXISTS ( SELECT '1'
3145 FROM hz_citizenship pe, igs_ad_interface_all ii
3146 WHERE ii.interface_run_id = l_interface_run_id
3147 AND ii.interface_id = mi.interface_id
3148 AND ii.person_id = pe.party_id
3149 AND UPPER(pe.country_code) = UPPER(mi.country_code)
3150 AND NVL(TRUNC(pe.date_recognized),l_default_date) = NVL(TRUNC(mi.date_recognized),l_default_date) );
3151 END IF;
3152
3153 --3. If rule is R and there match_ind is 18,19,22 or 23 then the records must have been
3154 -- processed in prior runs and didn't get updated .. update to status 1
3155 IF l_rule = 'R' THEN
3156 UPDATE igs_pe_citizen_int
3157 SET status = cst_stat_val_1
3158 WHERE interface_run_id = l_interface_run_id
3159 AND match_ind IN (cst_mi_val_18,cst_mi_val_19,cst_mi_val_22,cst_mi_val_23)
3160 AND status=cst_stat_val_2;
3161 END IF;
3162
3163 --4. If rule is R and match_ind is neither 21 nor 25 then error
3164 IF l_rule = 'R' THEN
3165 UPDATE igs_pe_citizen_int
3166 SET status = cst_stat_val_3,
3167 ERROR_CODE = cst_err_val_695
3168 WHERE interface_run_id = l_interface_run_id
3169 AND (match_ind IS NOT NULL AND match_ind NOT IN (cst_mi_val_21,cst_mi_val_25))
3170 AND status=cst_stat_val_2;
3171 END IF;
3172
3173 --5. If rule is R, set duplicated records with no discrepancy to status 1 and match_ind 23
3174 IF l_rule = 'R' THEN
3175 UPDATE igs_pe_citizen_int mi
3176 SET status = cst_stat_val_1,
3177 match_ind = cst_mi_val_23
3178 WHERE mi.interface_run_id = l_interface_run_id
3179 AND mi.match_ind IS NULL
3180 AND mi.status = cst_stat_val_2
3181 AND EXISTS ( SELECT '1'
3182 FROM hz_citizenship pe, igs_ad_interface_all ii
3183 WHERE ii.interface_run_id = l_interface_run_id
3184 AND ii.interface_id = mi.interface_id
3185 AND ii.person_id = pe.party_id
3186 AND pe.country_code = UPPER(mi.country_code)
3187 AND NVL(TRUNC(pe.date_recognized),l_default_date) = NVL(TRUNC(mi.date_recognized),l_default_date)
3188 AND NVL(UPPER(pe.document_reference),'N') = NVL(UPPER(mi.document_reference),'N')
3189 AND NVL(TRUNC(pe.date_disowned),l_default_date) = NVL(TRUNC(mi.date_disowned),l_default_date)
3190 AND NVL(TRUNC(pe.end_date),l_default_date) = NVL(TRUNC(mi.end_date),l_default_date)
3191 AND NVL(UPPER(pe.document_type),'N') =NVL(UPPER(mi.document_type),'N')
3192 );
3193 END IF;
3194
3195 --6. If rule in R records still exist, they are duplicates and have discrepancy .. update status=3,match_ind=20
3196 IF l_rule = 'R' THEN
3197 UPDATE igs_pe_citizen_int mi
3198 SET status = cst_stat_val_3,
3199 match_ind = cst_mi_val_20,
3200 dup_citizenship_id = (SELECT citizenship_id
3201 FROM hz_citizenship pe, igs_ad_interface_all ii
3202 WHERE ii.interface_run_id = l_interface_run_id
3203 AND ii.interface_id = mi.interface_id
3204 AND ii.person_id = pe.party_id
3205 AND pe.country_code = UPPER(mi.country_code)
3206 AND NVL(TRUNC(pe.date_recognized),l_default_date) = NVL(TRUNC(mi.date_recognized),l_default_date))
3207 WHERE mi.interface_run_id = l_interface_run_id
3208 AND mi.match_ind IS NULL
3209 AND mi.status = cst_stat_val_2
3210 AND EXISTS (SELECT '1'
3211 FROM hz_citizenship pe, igs_ad_interface_all ii
3212 WHERE ii.interface_run_id = l_interface_run_id
3213 AND ii.interface_id = mi.interface_id
3214 AND ii.person_id = pe.party_id
3215 AND pe.country_code = UPPER(mi.country_code)
3216 AND NVL(TRUNC(pe.date_recognized),l_default_date) = NVL(TRUNC(mi.date_recognized),l_default_date));
3217 END IF;
3218
3219 FOR pcz_rec IN c_pcz(l_interface_run_id) LOOP
3220
3221 l_processed_records := l_processed_records + 1;
3222
3223 pcz_rec.document_type := UPPER(pcz_rec.document_type);
3224 pcz_rec.country_code := UPPER(pcz_rec.country_code);
3225 pcz_rec.date_recognized := TRUNC(pcz_rec.date_recognized);
3226 pcz_rec.end_date := TRUNC(pcz_rec.end_date);
3227 pcz_rec.date_disowned := TRUNC(pcz_rec.date_disowned);
3228
3229 check_dup_citizenship( p_dup_var => l_dup_var,
3230 p_person_id => pcz_rec.person_id,
3231 p_country_code => pcz_rec.country_code,
3232 p_date_recognized => pcz_rec.date_recognized);
3233
3234 IF l_dup_var THEN
3235
3236 -- incase dup records are found, get the disc rule to be followed
3237 IF l_rule = 'I' THEN
3238
3239 upd_pe_citizenship(pcz_rec => pcz_rec,
3240 error_code => l_error_code,
3241 status => l_status);
3242 UPDATE igs_pe_citizen_int
3243 SET match_ind = cst_mi_val_18, status = l_status ,error_code = l_error_code
3244 WHERE interface_citizenship_id= pcz_rec.interface_citizenship_id;
3245
3246 ELSIF l_rule = 'R' THEN
3247 IF pcz_rec.match_ind = '21' THEN
3248
3249 upd_pe_citizenship(pcz_rec => pcz_rec,
3250 error_code => l_error_code,
3251 status => l_status );
3252
3253 UPDATE igs_pe_citizen_int
3254 SET status = l_status , error_code = l_error_code
3255 WHERE interface_citizenship_id = pcz_rec.interface_citizenship_id;
3256
3257 END IF;
3258 END IF;
3259 ELSE -- ie not a dup record. first IF check
3260 crt_pe_citizenship(pcz_rec => pcz_rec ,
3261 error_code => l_error_code,
3262 status => l_status );
3263 END IF;
3264
3265 IF l_processed_records = 100 THEN
3266 COMMIT;
3267 l_processed_records := 0;
3268 END IF;
3269
3270 END LOOP;
3271 END prc_pe_hz_citizenship ;
3272
3273 PROCEDURE prc_pe_fund_source
3274 ( p_source_type_id IN NUMBER,
3275 p_batch_id IN NUMBER
3276 )
3277 /*
3278 || Created By : ssawhney
3279 || Created On : 15 november
3280 || Purpose : This procedure process the Internation Dtls, Fund Dep part
3281 || Known limitations, enhancements or remarks :
3282 || Change History :
3283 || Who When What
3284 || npalanis 6-JAN-2003 Bug : 2734697
3285 || code added to commit after import of every
3286 || 100 records .New variable l_processed_records added
3287 || npalanis 6-JUN-2002 Bug - 2391172
3288 || Reference to igs_pe_code_classes changed to
3289 || fnd or igs lookups , null handling cursor
3290 || made to retrieve value using fund dep id
3291 */
3292 AS
3293
3294 --cursor to select records from interface records
3295 CURSOR c_pfs(cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE) IS
3296 SELECT ai.*,
3297 i.person_id
3298 FROM igs_pe_fund_src_int ai, igs_ad_interface_all i
3299 WHERE ai.interface_run_id = cp_interface_run_id
3300 AND i.interface_id = ai.interface_id
3301 AND i.interface_run_id = cp_interface_run_id
3302 AND ai.status = '2';
3303
3304 l_var VARCHAR2(1);
3305 l_rowid VARCHAR2(30);
3306 l_dup_var BOOLEAN;
3307 l_rule VARCHAR2(1);
3308
3309 pfs_rec c_pfs%ROWTYPE;
3310 l_fund_source_id igs_pe_fund_source.fund_source_id%TYPE;
3311 l_status pfs_rec.status%TYPE;
3312 l_error_code pfs_rec.error_code%TYPE;
3313 l_match_ind pfs_rec.match_ind%TYPE;
3314 l_processed_records NUMBER(5) := 0;
3315 l_prog_label VARCHAR2(4000);
3316 l_label VARCHAR2(4000);
3317 l_debug_str VARCHAR2(4000);
3318 l_enable_log VARCHAR2(1);
3319 l_request_id NUMBER(10);
3320 l_interface_run_id igs_ad_interface_all.interface_run_id%TYPE;
3321 -- Local Procedure to create new records .
3322
3323 PROCEDURE crt_pe_fund_source( pfs_rec IN c_pfs%ROWTYPE ,
3324 error_code OUT NOCOPY VARCHAR2,
3325 status OUT NOCOPY VARCHAR2)
3326 /*
3327 || Created By : ssawhney
3328 || Created On : 15 november
3329 || Purpose : Local procedure for insert
3330 ||
3331 */
3332 AS
3333
3334 BEGIN
3335 igs_pe_fund_source_pkg.insert_row(
3336 x_rowid => l_rowid,
3337 x_fund_source_id => l_fund_source_id,
3338 x_person_id => pfs_rec.person_id,
3339 x_fund_source_code => pfs_rec.fund_source_code,
3340 x_name => pfs_rec.name,
3341 x_amount => pfs_rec.amount,
3342 x_relationship_code => pfs_rec.relationship_code,
3343 x_document_ind => NVL(pfs_rec.document_ind,'N'),
3344 x_notes => pfs_rec.notes,
3345 x_mode => 'R'
3346 );
3347 error_code:=NULL;
3348 status :='1';
3349 EXCEPTION
3350 WHEN OTHERS THEN
3351 status := '3';
3352 error_code := 'E133';
3353 -- Call Log detail
3354 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
3355
3356 IF (l_request_id IS NULL) THEN
3357 l_request_id := fnd_global.conc_request_id;
3358 END IF;
3359
3360 l_label := 'igs.plsql.igs_ad_imp_007.crt_pe_fund_source.exception '||'E133';
3361
3362 l_debug_str := 'IGS_AD_IMP_007.crt_pe_fund_source '
3363 || ' Exception from Igs_Pe_Fund_Source_Pkg.Insert_Row '
3364 || 'Interface Fund Source Id : '
3365 || (pfs_rec.interface_fund_source_id)
3366 || 'Status : 3' || 'ErrorCode : E133 SQLERRM:' || SQLERRM;
3367
3368 fnd_log.string_with_context( fnd_log.level_exception,
3369 l_label,
3370 l_debug_str, NULL,
3371 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
3372 END IF;
3373
3374 IF l_enable_log = 'Y' THEN
3375 igs_ad_imp_001.logerrormessage(pfs_rec.interface_fund_source_id,'E133','IGS_PE_FUND_SRC_INT');
3376 END IF;
3377
3378 END crt_pe_fund_source; -- end local proc to create new record
3379
3380 -- local function for validating the records.
3381 FUNCTION Validate_Record(pfs_rec IN c_pfs%ROWTYPE) RETURN BOOLEAN
3382 /*
3383 || Created By : ssawhney
3384 || Created On : 15 november
3385 || Purpose : Local function for validations
3386 ||
3387 */
3388 IS
3389 l_var VARCHAR2(1);
3390 --validation cursors
3391
3392 l_error VARCHAR2(30);
3393 l_rowid VARCHAR2(25);
3394 BEGIN
3395
3396 -- Start Validations
3397
3398 IF NOT
3399 (igs_pe_pers_imp_001.validate_lookup_type_code('PE_FUND_TYPE',pfs_rec.fund_source_code,8405))
3400 THEN
3401 l_error := 'E124';
3402 RAISE no_data_found;
3403 ELSE
3404 l_error := NULL;
3405 END IF;
3406
3407 IF pfs_rec.relationship_code IS NOT NULL THEN
3408 IF NOT
3409 (igs_pe_pers_imp_001.validate_lookup_type_code('PARTY_RELATIONS_TYPE',pfs_rec.relationship_code,222))
3410 THEN
3411 l_error := 'E135';
3412 RAISE no_data_found;
3413 ELSE
3414 l_error := null;
3415 END IF;
3416 END IF;
3417
3418 IF pfs_rec.document_ind NOT IN ('N','Y') THEN
3419 l_error := 'E132';
3420 RAISE no_data_found;
3421 END IF;
3422
3423 -- all validations are ok. --insert
3424 RETURN TRUE;
3425
3426 EXCEPTION
3427 -- search for NO_DATA_FOUND, as its not trapped, OTHERS will be raised
3428 WHEN OTHERS THEN
3429 -- update for failure
3430 UPDATE igs_pe_fund_src_int
3431 SET status = '3',
3432 error_code = l_error
3433 WHERE interface_fund_source_id = pfs_rec.interface_fund_source_id;
3434
3435 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
3436
3437 IF (l_request_id IS NULL) THEN
3438 l_request_id := fnd_global.conc_request_id;
3439 END IF;
3440
3441 l_label := 'igs.plsql.igs_ad_imp_007.prc_pe_fund_source.exception '||l_error;
3442
3443 l_debug_str := 'Igs_Ad_Imp_007.prc_pe_fund_source.Validate_Record '
3444 ||' Interface Fund Source Id : ' || (pfs_rec.interface_fund_source_id) ||'Status : 3'
3445 || 'ErrorCode :' || l_error || ' SQLERRM: ' || SQLERRM;
3446
3447 fnd_log.string_with_context( fnd_log.level_exception,
3448 l_label,
3449 l_debug_str, NULL,
3450 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
3451 END IF;
3452
3453 IF l_enable_log = 'Y' THEN
3454 igs_ad_imp_001.logerrormessage(pfs_rec.interface_fund_source_id,l_error,'IGS_PE_FUND_SRC_INT');
3455 END IF;
3456 RETURN FALSE ;
3457
3458 END Validate_Record; -- End Local function Validate_Record
3459
3460 -- start the main processing from HERE.
3461 BEGIN -- Start the prc_pe_fund_source Now.
3462 l_interface_run_id := igs_ad_imp_001.g_interface_run_id;
3463 l_enable_log := igs_ad_imp_001.g_enable_log;
3464 l_prog_label := 'igs.plsql.igs_ad_imp_007.prc_pe_fund_source';
3465 l_label := 'igs.plsql.igs_ad_imp_007.prc_pe_fund_source.';
3466
3467 -- Logic of Fund Source is different.
3468 -- There will BE DUPLICATE RECORDS, so no check for duplicacy and
3469 -- no check for discrepency rule.
3470
3471 -- Call Log header
3472
3473 --
3474 FOR pfs_rec IN c_pfs(l_interface_run_id) LOOP -- LOOP Started
3475 BEGIN
3476
3477 l_processed_records := l_processed_records + 1;
3478 --
3479 -- Set the status, match_ind, error_code of the interface record
3480 --
3481 l_status := pfs_rec.status;
3482 l_error_code := pfs_rec.error_code;
3483 l_match_ind := pfs_rec.match_ind;
3484
3485 pfs_rec.fund_source_code := UPPER(pfs_rec.fund_source_code);
3486 pfs_rec.relationship_code := UPPER(pfs_rec.relationship_code);
3487 pfs_rec.document_ind := UPPER(pfs_rec.document_ind);
3488
3489 -- validate the current record
3490 IF validate_record( pfs_rec => pfs_rec ) THEN --
3491
3492
3493 crt_pe_fund_source (pfs_rec => pfs_rec,
3494 error_code => l_error_code,
3495 status => l_status );
3496
3497 UPDATE igs_pe_fund_src_int
3498 SET status = l_status,
3499 error_code = l_error_code
3500 WHERE interface_fund_source_id = pfs_rec.interface_fund_source_id;
3501
3502 END IF;
3503
3504 IF l_processed_records = 100 THEN
3505 COMMIT;
3506 l_processed_records := 0;
3507 END IF;
3508
3509 END ;
3510 END LOOP;
3511 END prc_pe_fund_source ;
3512
3513
3514 PROCEDURE prc_pe_intl_dtls
3515 ( p_source_type_id IN NUMBER,
3516 p_batch_id IN NUMBER
3517 )
3518 /*
3519 || Created By : 15 november
3520 || Created On :
3521 || Purpose : This procedure process the Internation Dtls, Main procedure
3522 || Known limitations, enhancements or remarks :
3523 || Change History :
3524 || Who When What
3525 || npalanis 27-MAy-2002 Bug no - 2377751
3526 || New error codes registered and added
3527 || ssawhney 15 nov Bug no.2103692:Person Interface DLD
3528 || Internation Dtls structure is completly changed.
3529 */
3530 AS
3531 l_prog_label VARCHAR2(4000);
3532 l_label VARCHAR2(4000);
3533 l_debug_str VARCHAR2(4000);
3534 l_request_id NUMBER(10);
3535
3536 BEGIN
3537 l_prog_label := 'igs.plsql.igs_ad_imp_007.prc_pe_intl_dtls';
3538 l_label := 'igs.plsql.igs_ad_imp_007.prc_pe_intl_dtls.';
3539
3540 -- start the main parent import.
3541 prc_pe_hz_citizenship ( p_source_type_id, p_batch_id) ;
3542
3543 -- start with the childs
3544 prc_pe_fund_source ( p_source_type_id, p_batch_id) ;
3545
3546 IGS_AD_IMP_026.prc_pe_visa(p_source_type_id, p_batch_id);
3547 IGS_AD_IMP_026.prc_pe_visit_histry(p_source_type_id, p_batch_id);
3548 IGS_AD_IMP_026.prc_pe_passport(p_source_type_id, p_batch_id);
3549 IGS_AD_IMP_026.prc_pe_eit(p_source_type_id, p_batch_id);
3550
3551 EXCEPTION
3552 WHEN OTHERS THEN
3553
3554 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
3555
3556 IF (l_request_id IS NULL) THEN
3557 l_request_id := fnd_global.conc_request_id;
3558 END IF;
3559
3560 l_label := 'igs.plsql.igs_ad_imp_007.prc_pe_int_dtls.exception ';
3561
3562 l_debug_str := 'Igs_Ad_Imp_007.prc_pe_int_dtls Unhandled Exception'
3563 || ' Source Id : '
3564 || (p_source_type_id)
3565 || 'Batch Id : ' || IGS_GE_NUMBER.TO_CANN(p_batch_id)|| ' SQLERRM: ' || SQLERRM;
3566
3567 fnd_log.string_with_context( fnd_log.level_exception,
3568 l_label,
3569 l_debug_str, NULL,
3570 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
3571 END IF;
3572 END prc_pe_intl_dtls ;
3573
3574
3575
3576 END IGS_AD_IMP_007;