1 PACKAGE BODY igs_ad_imp_011 AS
2 /* $Header: IGSAD89B.pls 120.5 2006/09/21 08:57:53 gmaheswa ship $ */
3
4 /*
5 || Created By :
6 || Created On :
7 || Purpose :
8 || Known limitations, enhancements or remarks :
9 || Change History :
10 || Who When What
11 asbala 13-OCT-2003 Bug 3130316. Import Process Source Category Rule processing changes,
12 lookup caching related changes, and cursor parameterization.
13 asbala 13-OCT-2003 Bug 3130316. Import Process Logging Framework Related changes.
14
15 || npalanis 6-JAN-2 Bug : 2734697
16 || code added to commit after import of every
17 || 100 records .New variable l_processed_records added
18 || ssawhney 21-oct-2002 Bug no.2630860:SWS104
19 || PRC_PE_RES_DTLS added
20 || pkpatel 23-DEC-2002 Bug No: 2722027
21 || PRC_SPECIAL_NEEDS added and moved the code from IGSAD86B.pls
22 || pkpatel 7-FEB-2003 Bug No: 2765142
23 || Modified to add the UCAS user hook igs_uc_utils.admission_residency_dtls
24 || pkpatel 2-JUN-2003 Bug 2986796(special Needs CCR)
25 || Modified the the select statements to use bind variables.
26 || Modified the logic for NONE special needs record as per jul'03 special need CCR
27 || pkpatel 6-JUN-2003 Bug 2975196
28 || Modified evaluation date validation in prc_pe_res_dtls
29 || skpandey 11-APR-2006 Bug#5110137: Removed call to upd_res_det procedure
30
31 || (reverse chronological order - newest change first)
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_stat_val_1 CONSTANT VARCHAR2(1) := '1';
44 cst_stat_val_2 CONSTANT VARCHAR2(1) := '2';
45 cst_stat_val_3 CONSTANT VARCHAR2(1) := '3';
46
47 cst_err_val_695 CONSTANT VARCHAR2(4) := 'E695';
48
49 PROCEDURE prc_apcnt_acadhnr_dtls (
50 p_source_type_id IN NUMBER,
51 p_batch_id IN NUMBER )
52
53 AS
54 /*----------------------------------------------------------------------------------
55 || Created By : pkpatel
56 || Created On : 22-JUN-2001
57 || Purpose : This procedure process the Application
58 || Known limitations, enhancements or remarks :
59 || Change History :
60 || Who When What
61 || npalanis 6-JAN-2003 Bug : 2734697
62 || code added to commit after import of every
63 || 100 records .New variable l_processed_records added
64 || samaresh 24-JAN-2002 The table Igs_ad_appl_int has been obsoleted
65 || new table igs_ad_apl_int has been created
66 || as a part of build ADI - Import Prc Changes
67 || ssawhney 22-oct SWS104 : 2630860 : AD_ACAD_HONOR moves to PE_ACAD_HONORS and all the other changes.
68 ||--------------------------------------------------------------------------------*/
69
70 l_status igs_ad_acadhonor_int.status%TYPE;
71 l_error_code igs_ad_acadhonor_int.error_code%TYPE;
72 l_match_ind igs_ad_acadhonor_int.match_ind%TYPE;
73 l_validate VARCHAR2(1);
74 l_processed_records NUMBER(5) := 0;
75 l_prog_label VARCHAR2(4000);
76 l_label VARCHAR2(4000);
77 l_debug_str VARCHAR2(4000);
78 l_enable_log VARCHAR2(1);
79 l_request_id NUMBER(10);
80 l_interface_run_id igs_ad_interface_all.interface_run_id%TYPE;
81
82 --
83 -- dld ref 1. Pick up the records from the tables mentioned below :
84 --
85 CURSOR hnr_cur (cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE) IS
86 SELECT mi.*, i.person_id
87 FROM igs_ad_acadhonor_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 i.interface_run_id = cp_interface_run_id
91 AND mi.status = '2';
92
93 acadhonor_rec hnr_cur%ROWTYPE;
94
95 --
96 -- Cursor to check for the duplicate
97 --
98 --
99 -- Modified By : ssawhney
100 -- Date : 1/21/02
101 -- Bug # 2630860
102 -- Removed the appl_no and modified acad_honor_type_id to acad_honor_type
103 --
104 CURSOR chk_dup_cur ( acadhonor_rec hnr_cur%ROWTYPE) IS
105 SELECT rowid,hi.*
106 FROM igs_pe_acad_honors hi
107 WHERE hi.person_id = acadhonor_rec.person_id AND
108 hi.acad_honor_type = acadhonor_rec.acad_honor_type AND
109 NVL(hi.honor_date,TO_DATE('4712/12/31','YYYY/MM/DD')) = NVL(TRUNC(acadhonor_rec.honor_date),TO_DATE('4712/12/31','YYYY/MM/DD'));
110
111 chk_dup_rec chk_dup_cur%ROWTYPE;
112 l_dup_exists NUMBER;
113 --
114 -- Modified the Null Handling Logic
115 --
116 --
117 -- Modified By : ssawhney
118 -- Date : 1/21/02
119 -- Bug # 2630860
120 -- Removed the appl_no and modified acad_honor_type_id to acad_honor_type
121
122 l_rule VARCHAR2(1);
123
124 -- Begin Local Function
125 FUNCTION validate_record ( acadhonor_rec hnr_cur%ROWTYPE) RETURN VARCHAR2
126 AS
127 l_return_val VARCHAR2(1) := 'Y';
128 l_var VARCHAR2(1);
129 CURSOR birth_dt_cur(cp_person_id igs_pe_person_base_v.person_id%TYPE) IS
130 SELECT birth_date birth_dt
131 FROM igs_pe_person_base_v
132 WHERE person_id = cp_person_id;
133 birth_dt_rec birth_dt_cur%ROWTYPE;
134 l_bdate DATE;
135 BEGIN
136
137 IF
138 (igs_pe_pers_imp_001.validate_lookup_type_code('PE_ACAD_HONORS',acadhonor_rec.acad_honor_type,8405))
139 THEN
140 IF acadhonor_rec.honor_date IS NOT NULL THEN
141 -- Get the value of the Birth Date of the person
142 OPEN birth_dt_cur(acadhonor_rec.person_id);
143 FETCH birth_dt_cur INTO birth_dt_rec;
144 CLOSE birth_dt_cur;
145 l_bdate := birth_dt_rec.birth_dt;
146
147 IF acadhonor_rec.honor_date > SYSDATE OR acadhonor_rec.honor_date < l_bdate THEN
148 l_return_val := 'H';
149 IF l_enable_log = 'Y' THEN
150 igs_ad_imp_001.logerrormessage(acadhonor_rec.interface_acadhonor_id,'E052');
151 END IF;
152 END IF;
153
154 END IF;
155 ELSE
156 l_return_val := 'N';
157 IF l_enable_log = 'Y' THEN
158 igs_ad_imp_001.logerrormessage(acadhonor_rec.interface_acadhonor_id,'E421');
159 END IF;
160 END IF;
161
162 RETURN l_return_val;
163 EXCEPTION
164 WHEN OTHERS THEN
165 l_return_val := 'N';
166 --
167 -- Close the cursors
168 --
169 IF birth_dt_cur%ISOPEN THEN
170 CLOSE birth_dt_cur;
171 END IF;
172 RETURN l_return_val;
173 END validate_record;
174 -- End Local Function
175
176 -- Begin Local procedure
177 PROCEDURE crt_apcnt_acad_hnr(
178 acadhonor_rec hnr_cur%ROWTYPE )
179 AS
180 l_rowid VARCHAR2(25);
181 l_acad_hnr_id NUMBER;
182 BEGIN
183 -- Call Log header
184 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
185
186 IF (l_request_id IS NULL) THEN
187 l_request_id := fnd_global.conc_request_id;
188 END IF;
189
190 l_label := 'igs.plsql.igs_ad_imp_011.crt_apcnt_acad_hnr.begin';
191 l_debug_str := 'start of proc crt_apcnt_acad_hnr';
192
193 fnd_log.string_with_context( fnd_log.level_procedure,
194 l_label,
195 l_debug_str, NULL,
196 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
197 END IF;
198
199
200
201 igs_pe_acad_honors_pkg.insert_row (
202 x_rowid => l_rowid,
203 x_acad_honor_id => l_acad_hnr_id,
204 x_person_id => acadhonor_rec.person_id,
205 x_acad_honor_type => acadhonor_rec.acad_honor_type ,
206 x_comments => acadhonor_rec.comments ,
207 x_honor_date => acadhonor_rec.honor_date ,
208 x_mode => 'R'
209 );
210 --
211 -- Insertion Successful
212 --
213 l_status := '1';
214 l_error_code := NULL;
215 EXCEPTION
216 WHEN OTHERS THEN
217 --
218 -- Insertion Not Successful
219 --
220 l_status := '3';
221 l_error_code := 'E322';
222
223 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
224
225 IF (l_request_id IS NULL) THEN
226 l_request_id := fnd_global.conc_request_id;
227 END IF;
228
229 l_label := 'igs.plsql.igs_ad_imp_011.crt_apcnt_acad_hnr.exception'||l_error_code;
230
231 l_debug_str := 'IGS_AD_IMP_011.PRC_APCNT_ACADHNR_DTLS.CRT_APCNT_ACAD_HNR ' ||
232 'STATUS : 3' || 'ERRORCODE : E322 SQLERRM:' || SQLERRM;
233
234 fnd_log.string_with_context( fnd_log.level_exception,
235 l_label,
236 l_debug_str, NULL,
237 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
238 END IF;
239
240 IF l_enable_log = 'Y' THEN
241 igs_ad_imp_001.logerrormessage(acadhonor_rec.INTERFACE_ACADHONOR_ID,l_error_code);
242 END IF;
243
244
245 END crt_apcnt_acad_hnr;
246 -- End Local Procedure
247
248 -- Start of the Main Procedure PRC_APCNT_ACADHNR_DTLS
249 BEGIN
250 l_interface_run_id := igs_ad_imp_001.g_interface_run_id;
251 l_enable_log := igs_ad_imp_001.g_enable_log;
252 l_prog_label := 'igs.plsql.igs_ad_imp_011.prc_apcnt_acadhnr_dtls';
253 l_label := 'igs.plsql.igs_ad_imp_011.prc_apcnt_acadhnr_dtls.';
254
255 --
256 -- dld ref 2. Put them in a record called ACADHONOR_REC.
257 --
258 l_rule := igs_ad_imp_001.find_source_cat_rule(p_source_type_id,'PERSON_ACAD_HONORS');
259
260 -- If rule is E or I, then if the match_ind is not null, the combination is invalid
261 IF l_rule IN ('E','I') THEN
262 UPDATE igs_ad_acadhonor_int_all
263 SET status = cst_stat_val_3,
264 ERROR_CODE = cst_err_val_695 -- Error code depicting incorrect combination
265 WHERE match_ind IS NOT NULL
266 AND status = cst_stat_val_2
267 AND interface_run_id = l_interface_run_id;
268 END IF;
269
270 -- If rule is E and duplicate exists, update match_ind to 19 and status to 1
271 IF l_rule = 'E' THEN
272 UPDATE igs_ad_acadhonor_int_all mi
273 SET status = cst_stat_val_1,
274 match_ind = cst_mi_val_19
275 WHERE mi.interface_run_id = l_interface_run_id
276 AND mi.status = cst_stat_val_2
277 AND EXISTS ( SELECT '1'
278 FROM igs_pe_acad_honors pe, igs_ad_interface_all ii
279 WHERE ii.interface_run_id = l_interface_run_id
280 AND ii.interface_id = mi.interface_id
281 AND ii.person_id = pe.person_id
282 AND pe.acad_honor_type = UPPER(mi.acad_honor_type)
283 AND NVL(pe.honor_date,TO_DATE('4712/12/31','YYYY/MM/DD')) = NVL(TRUNC(mi.honor_date),TO_DATE('4712/12/31','YYYY/MM/DD')));
284 END IF;
285
286 -- If rule is R and there match_ind is 18,19,22 or 23 then the records must have been
287 -- processed in prior runs and didn't get updated .. update to status 1
288 IF l_rule = 'R' THEN
289 UPDATE igs_ad_acadhonor_int_all
290 SET status = cst_stat_val_1
291 WHERE interface_run_id = l_interface_run_id
292 AND match_ind IN (cst_mi_val_18,cst_mi_val_19,cst_mi_val_22,cst_mi_val_23)
293 AND status = cst_stat_val_2;
294 END IF;
295
296 -- If rule is R and match_ind is neither 21 nor 25 then error
297 IF l_rule = 'R' THEN
298 UPDATE igs_ad_acadhonor_int_all
299 SET status = cst_stat_val_3,
300 ERROR_CODE = cst_err_val_695
301 WHERE interface_run_id = l_interface_run_id
302 AND status = cst_stat_val_2
303 AND (match_ind IS NOT NULL AND match_ind NOT IN (cst_mi_val_21,cst_mi_val_25));
304 END IF;
305
306 -- If rule is R, set duplicated records with no discrepancy to status 1 and match_ind 23
307 IF l_rule = 'R' THEN
308 UPDATE igs_ad_acadhonor_int_all mi
309 SET status = cst_stat_val_1,
310 match_ind = cst_mi_val_23
311 WHERE mi.interface_run_id = l_interface_run_id
312 AND mi.match_ind IS NULL
313 AND mi.status = cst_stat_val_2
314 AND EXISTS ( SELECT '1'
315 FROM igs_pe_acad_honors pe, igs_ad_interface_all ii
316 WHERE ii.interface_run_id = l_interface_run_id
317 AND ii.interface_id = mi.interface_id
318 AND ii.person_id = pe.person_id
319 AND pe.acad_honor_type = UPPER(mi.acad_honor_type)
320 AND NVL(pe.honor_date,TO_DATE('4712/12/31','YYYY/MM/DD')) = NVL(TRUNC(mi.honor_date),TO_DATE('4712/12/31','YYYY/MM/DD'))
321 AND NVL(UPPER(pe.comments),'*!*') = NVL(UPPER(mi.comments),'*!*')
322 );
323 END IF;
324
325 -- If rule is R records still exist, they are duplicates and have discrepancy .. update status=3,match_ind=20
326 IF l_rule = 'R' THEN
327 UPDATE igs_ad_acadhonor_int_all mi
328 SET status = cst_stat_val_3,
329 match_ind = cst_mi_val_20,
330 DUP_ACAD_HONOR_ID = (SELECT pe.acad_honor_id
331 FROM igs_pe_acad_honors pe, igs_ad_interface_all ii
332 WHERE mi.interface_run_id = l_interface_run_id
333 AND ii.interface_id = mi.interface_id
334 AND ii.person_id = pe.person_id
335 AND pe.acad_honor_type = UPPER(mi.acad_honor_type)
336 AND NVL(pe.honor_date,TO_DATE('4712/12/31','YYYY/MM/DD')) = NVL(TRUNC(mi.honor_date),TO_DATE('4712/12/31','YYYY/MM/DD')) )
337 WHERE mi.interface_run_id = l_interface_run_id
338 AND mi.match_ind IS NULL
339 AND mi.status = cst_stat_val_2
340 AND EXISTS (SELECT '1'
341 FROM igs_pe_acad_honors pe, igs_ad_interface_all ii
342 WHERE ii.interface_run_id = l_interface_run_id
343 AND ii.interface_id = mi.interface_id
344 AND ii.person_id = pe.person_id
345 AND pe.acad_honor_type = UPPER(mi.acad_honor_type)
346 AND NVL(pe.honor_date,TO_DATE('4712/12/31','YYYY/MM/DD')) = NVL(TRUNC(mi.honor_date),TO_DATE('4712/12/31','YYYY/MM/DD')));
347 END IF;
348
349
350 FOR acadhonor_rec IN hnr_cur(l_interface_run_id)
351 LOOP
352 l_processed_records := l_processed_records + 1 ;
353 --
354 -- Set the status, error_code, match_ind variables to the existing values
355 --
356 l_status := acadhonor_rec.status;
357 l_error_code := acadhonor_rec.error_code;
358 l_match_ind := acadhonor_rec.match_ind;
359 acadhonor_rec.acad_honor_type := UPPER(acadhonor_rec.acad_honor_type);
360 acadhonor_rec.honor_date := TRUNC(acadhonor_rec.honor_date);
361
362 BEGIN
363 --
364 -- dld ref 4. Check to see if the record already exists. Use the following query : Was missing previously
365 --
366 chk_dup_rec.acad_honor_type := NULL;
367 OPEN chk_dup_cur (acadhonor_rec);
368 FETCH chk_dup_cur INTO chk_dup_rec;
369 CLOSE chk_dup_cur;
370 IF chk_dup_rec.acad_honor_type IS NOT NULL THEN
371 -- To be changed as a generic change
372 IF l_rule = 'I' THEN
373 l_match_ind := '18';
374 l_validate := validate_record ( acadhonor_rec);
375 IF l_validate = 'H' THEN
376 l_error_code := 'E052';
377 l_status := '3';
378 ELSIF l_validate = 'N' THEN
379 l_error_code := 'E421';
380 l_status := '3';
381 ELSIF l_validate = 'Y' THEN
382 --
383 -- Validation Successful
384
385 BEGIN
386 igs_pe_acad_honors_pkg.update_row (
387 x_rowid => chk_dup_rec.rowid,
388 x_acad_honor_id => chk_dup_rec.acad_honor_id,
389 x_person_id => acadhonor_rec.person_id,
390 x_acad_honor_type => acadhonor_rec.acad_honor_type ,
391 x_comments => NVL(acadhonor_rec.comments ,chk_dup_rec.comments),
392 x_honor_date => NVL(acadhonor_rec.honor_date,chk_dup_rec.honor_date),
393 x_mode => 'R'
394 );
395 --
396 -- Update is successful the update the status to completed '1'
397 --
398 l_status := '1';
399 EXCEPTION
400 --
401 -- Update Not a Success then update the error_code and the status accordingly
402 --
403 WHEN OTHERS THEN
404 l_status := '3';
405 l_error_code := 'E014';
406 IF l_enable_log = 'Y' THEN
407 igs_ad_imp_001.logerrormessage(acadhonor_rec.interface_acadhonor_id,'E014');
408 END IF;
409
410 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
411
412 IF (l_request_id IS NULL) THEN
413 l_request_id := fnd_global.conc_request_id;
414 END IF;
415
416 l_label := 'igs.plsql.igs_ad_imp_011.prc_apcnt_acadhnr_dtls.exception_update1'||'E014';
417
418 l_debug_str := 'IGS_AD_IMP_011.PRC_APCNT_ACADHNR_DTLS ' ||
419 'INTERFACE ACADHONOR ID : ' || (acadhonor_rec.interface_acadhonor_id) ||
420 'STATUS : 3' || 'ERRORCODE : E014 SQLERRM:' || SQLERRM;
421
422 fnd_log.string_with_context( fnd_log.level_exception,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
423 END IF;
424
425 END;
426 END IF; -- for validation
427 ELSIF l_rule = 'R' THEN
428 IF acadhonor_rec.match_ind = '21' THEN
429 l_validate := validate_record ( acadhonor_rec);
430 IF l_validate = 'H' THEN
431 l_error_code := 'E052';
432 l_status := '3';
433 ELSIF l_validate = 'N' THEN
434 l_error_code := 'E421';
435 l_status := '3';
436 ELSIF l_validate = 'Y' THEN
437 --
438 -- Validation Successful
439 BEGIN
440 igs_pe_acad_honors_pkg.update_row (
441 x_rowid => chk_dup_rec.rowid,
442 x_acad_honor_id => chk_dup_rec.acad_honor_id,
443 x_person_id =>acadhonor_rec.person_id,
444 x_acad_honor_type => acadhonor_rec.acad_honor_type,
445 x_comments => NVL(acadhonor_rec.comments ,chk_dup_rec.comments),
446 x_honor_date => NVL(acadhonor_rec.honor_date,chk_dup_rec.honor_date),
447 x_mode => 'R'
448 );
449 --
450 -- update is success
451 --
452 l_status := '1';
453 l_match_ind := '18';
454 EXCEPTION
455 --
456 -- If update is not successful then update the error_code and status accordingly
457 --
458 WHEN OTHERS THEN
459 l_status := '3';
460 l_error_code := 'E014';
461 IF l_enable_log = 'Y' THEN
462 igs_ad_imp_001.logerrormessage(acadhonor_rec.interface_acadhonor_id,'E014');
463 END IF;
464
465 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
466
467 IF (l_request_id IS NULL) THEN
468 l_request_id := fnd_global.conc_request_id;
469 END IF;
470
471 l_label := 'igs.plsql.igs_ad_imp_011.prc_apcnt_acadhnr_dtls.exception_update2'||'E014';
472
473 l_debug_str := 'IGS_AD_IMP_011.PRC_APCNT_ACADHNR_DTLS ' ||
474 'INTERFACE ACADHONOR ID : ' || (acadhonor_rec.interface_acadhonor_id) ||
475 'STATUS : 3' || 'ERRORCODE : E014 SQLERRM:' || SQLERRM;
476
477 fnd_log.string_with_context( fnd_log.level_exception,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
478 END IF;
479 END;
480 END IF;
481 END IF;
482 END IF;
483 ELSE
484 l_validate := validate_record ( acadhonor_rec);
485 IF l_validate = 'Y' THEN
486 --
487 -- Validation Successful
488 --
489 --Call the procedure Create_Applicant_Acad_Honors (ACADHONOR_REC)
490 crt_apcnt_acad_hnr( acadhonor_rec);
491 ELSIF l_validate = 'H' THEN
492 --
493 -- Honor Date Validation Failed
494 --
495 l_error_code := 'E052';
496 l_status := '3';
497 ELSIF l_validate = 'N' THEN
498 --
499 -- Validation Not Successful
500 --
501 l_error_code := 'E421';
502 l_status := '3';
503 END IF;
504 END IF;
505 --
506 -- Update the interface record with the status, error_code, match_ind
507 --
508 UPDATE
509 igs_ad_acadhonor_int_all
510 SET
511 status = l_status,
512 error_code = l_error_code,
513 match_ind = l_match_ind
514 WHERE
515 interface_acadhonor_id = acadhonor_rec.interface_acadhonor_id;
516
517 EXCEPTION
518 WHEN OTHERS THEN
519 --
520 -- Close the cursors if open
521 --
522 IF chk_dup_cur%ISOPEN THEN
523 CLOSE chk_dup_cur;
524 END IF;
525 UPDATE
526 igs_ad_acadhonor_int_all
527 SET
528 status = '3',
529 error_code = 'E518'
530 WHERE
531 interface_acadhonor_id = acadhonor_rec.interface_acadhonor_id;
532 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
533
534 IF (l_request_id IS NULL) THEN
535 l_request_id := fnd_global.conc_request_id;
536 END IF;
537
538 l_label := 'igs.plsql.igs_ad_imp_011.prc_apcnt_acadhnr_dtls.exception'||'E518';
539
540 l_debug_str := 'IGS_AD_IMP_011.PRC_APCNT_ACADHNR_DTLS ' ||
541 'INTERFACE ACADHONOR ID : ' || (acadhonor_rec.interface_acadhonor_id) ||
542 'STATUS : 3' || 'ERRORCODE : E518 SQLERRM:' || SQLERRM;
543
544 fnd_log.string_with_context( fnd_log.level_exception,
545 l_label,
546 l_debug_str, NULL,
547 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
548 END IF;
549
550 IF l_enable_log = 'Y' THEN
551 igs_ad_imp_001.logerrormessage(acadhonor_rec.interface_acadhonor_id,'E518');
552 END IF;
553
554 END;
555
556 IF l_processed_records = 100 THEN
557 COMMIT;
558 l_processed_records := 0;
559 END IF;
560
561 END LOOP;
562
563 END prc_apcnt_acadhnr_dtls;
564
565
566 PROCEDURE prc_pe_res_dtls (
567 p_source_type_id IN NUMBER,
568 p_batch_id IN NUMBER )
569
570 AS
571 /*----------------------------------------------------------------------------------
572 || Created By : ssawhney
573 || Created On : 21-OCT-2001
574 || Purpose : This procedure process the Application
575 || Known limitations, enhancements or remarks :
576 || Change History :
577 || Who When What
578 || npalanis 6-JAN-2003 Bug : 2734697
579 || code added to commit after import of every
580 || 100 records .New variable l_processed_records added
581 || pkpatel 17-DEC-2002 Bug No: 2695902
582 || Modified the birth date, overlapping validations logic.
583 || Added the Attribute columns in the discrepancy cursor
584 || pkpatel 7-FEB-2003 Bug No: 2765142
585 || Modified to add the UCAS user hook igs_uc_utils.admission_residency_dtls
586 || ssawhney update positioning when NOT coming from UCAS changed
587 || pkpatel 6-JUN-2003 Bug 2975196
588 || Reversed the evaluation date validation. Now it cannot be a future date.
589 || Modified E184 to E203 when evaluation date with Birth date fails
590 || asbala 3-SEP-2003 Build SWCR01,02
591 || Altered parameters of chk_dup_cur and c_null_hdlg_res_cur to reflect the
592 || changes in unique index
593 || pkpatel 9-Nov-2004 Bug 3993967 (Removed Start/End Date. Included Term)
594 ||--------------------------------------------------------------------------------*/
595 l_status igs_pe_res_dtls_int.status%TYPE;
596 p_error_code igs_pe_res_dtls_int.ERROR_CODE%TYPE;
597 l_match_ind igs_pe_res_dtls_int.match_ind%TYPE;
598 l_processed_records NUMBER(5) := 0;
599 l_prog_label VARCHAR2(4000);
600 l_label VARCHAR2(4000);
601 l_debug_str VARCHAR2(4000);
602 l_enable_log VARCHAR2(1);
603 l_request_id NUMBER(10);
604 l_rule VARCHAR2(1);
605 l_interface_run_id igs_ad_interface_all.interface_run_id%TYPE;
606
607 CURSOR res_cur(cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE) IS
608 SELECT mi.*, i.person_id
609 FROM igs_pe_res_dtls_int mi,igs_ad_interface_all i
610 WHERE mi.interface_run_id = cp_interface_run_id
611 AND mi.interface_id = i.interface_id
612 AND i.interface_run_id = cp_interface_run_id
613 AND mi.status = '2';
614
615 res_dtl_rec res_cur%ROWTYPE;
616
617 CURSOR chk_dup_cur ( res_dtls_cur res_cur%ROWTYPE) IS
618 SELECT rowid,hi.*
619 FROM igs_pe_res_dtls_all hi
620 WHERE hi.person_id = res_dtls_cur.person_id AND
621 hi.residency_class_cd = res_dtls_cur.residency_class_cd AND
622 hi.cal_type = res_dtls_cur.cal_type AND
623 hi.sequence_number = res_dtls_cur.sequence_number;
624
625 chk_dup_rec chk_dup_cur%ROWTYPE;
626
627 -- Begin Local procedure
628 PROCEDURE validate_record ( res_dtls_rec res_cur%ROWTYPE,
629 p_error_code OUT NOCOPY VARCHAR2,
630 p_mode VARCHAR2)
631 AS
632
633 CURSOR birth_dt_cur(cp_person_id igs_pe_person_base_v.person_id%TYPE) IS
634 SELECT birth_date birth_dt
635 FROM igs_pe_person_base_v
636 WHERE person_id = cp_person_id;
637
638 CURSOR load_cal_cur (cp_cal_type igs_ca_inst_all.cal_type%TYPE, cp_sequence_number igs_ca_inst_all.sequence_number%TYPE)
639 IS
640 SELECT 1
641 FROM igs_ca_inst_all ca,
642 igs_ca_type typ,
643 igs_ca_stat stat
644 WHERE typ.cal_type=ca.cal_type AND
645 typ.s_cal_cat = 'LOAD' AND
646 ca.cal_status = STAT.CAL_STATUS AND
647 stat.s_cal_status = 'ACTIVE' AND
648 ca.cal_type = cp_cal_type AND
649 ca.sequence_number = cp_sequence_number;
650
651 birth_dt_rec birth_dt_cur%ROWTYPE;
652 l_bdate DATE;
653 l_var VARCHAR2(1);
654 BEGIN
655 p_error_code := NULL;
656
657 -- The Load Calendar and Residency Class should be validated only in the Insert mode. Since the duplicate check is done
658 -- on these columns in the Update mode the validation for these is not required.
659 IF p_mode = 'I' THEN
660 IF
661 (igs_pe_pers_imp_001.validate_lookup_type_code('PE_RES_CLASS',res_dtls_rec.residency_class_cd,8405))
662 THEN
663 p_error_code := NULL;
664 ELSE
665 p_error_code := 'E179'; -- Res code Validation Failed
666 RAISE no_data_found;
667 END IF;
668
669 -- Calendar validation
670 OPEN load_cal_cur(res_dtls_rec.cal_type,res_dtls_rec.sequence_number);
671 FETCH load_cal_cur INTO l_var;
672 IF load_cal_cur%NOTFOUND THEN
673 p_error_code := 'E181';
674 CLOSE load_cal_cur;
675 RAISE no_data_found;
676 END IF;
677 CLOSE load_cal_cur;
678 END IF;
679
680 IF
681 (igs_pe_pers_imp_001.validate_lookup_type_code('PE_RES_STATUS',res_dtls_rec.residency_status_cd,8405))
682 THEN
683 p_error_code := NULL;
684 ELSE
685 p_error_code := 'E180'; -- Res status Validation Failed
686 RAISE no_data_found;
687 END IF;
688
689
690 IF res_dtls_rec.evaluation_date > TRUNC(SYSDATE) THEN
691 p_error_code := 'E184';
692 RAISE no_data_found;
693 END IF;
694 OPEN birth_dt_cur(res_dtls_rec.person_id);
695 FETCH birth_dt_cur INTO birth_dt_rec;
696 CLOSE birth_dt_cur;
697
698 IF birth_dt_rec.birth_dt IS NOT NULL THEN
699 l_bdate := birth_dt_rec.birth_dt;
700 IF res_dtls_rec.evaluation_date < l_bdate THEN
701 p_error_code := 'E203'; -- evaluation date validation failed.
702 RAISE no_data_found;
703 END IF;
704
705 END IF;
706
707 -- validate DFF
708 IF NOT igs_ad_imp_018.validate_desc_flex(
709 p_attribute_category =>res_dtls_rec.attribute_category,
710 p_attribute1 =>res_dtls_rec.attribute1 ,
711 p_attribute2 =>res_dtls_rec.attribute2 ,
712 p_attribute3 =>res_dtls_rec.attribute3 ,
713 p_attribute4 =>res_dtls_rec.attribute4 ,
714 p_attribute5 =>res_dtls_rec.attribute5 ,
715 p_attribute6 =>res_dtls_rec.attribute6 ,
716 p_attribute7 =>res_dtls_rec.attribute7 ,
717 p_attribute8 =>res_dtls_rec.attribute8 ,
718 p_attribute9 =>res_dtls_rec.attribute9 ,
719 p_attribute10 =>res_dtls_rec.attribute10 ,
720 p_attribute11 =>res_dtls_rec.attribute11 ,
721 p_attribute12 =>res_dtls_rec.attribute12 ,
722 p_attribute13 =>res_dtls_rec.attribute13 ,
723 p_attribute14 =>res_dtls_rec.attribute14 ,
724 p_attribute15 =>res_dtls_rec.attribute15 ,
725 p_attribute16 =>res_dtls_rec.attribute16 ,
726 p_attribute17 =>res_dtls_rec.attribute17 ,
727 p_attribute18 =>res_dtls_rec.attribute18 ,
728 p_attribute19 =>res_dtls_rec.attribute19 ,
729 p_attribute20 =>res_dtls_rec.attribute20 ,
730 p_desc_flex_name =>'IGS_PE_PERS_RESIDENCY_FLEX' ) THEN
731
732 p_error_code:='E255';
733 RAISE no_data_found;
734 END IF;
735
736 EXCEPTION
737 WHEN NO_DATA_FOUND THEN
738 -- Validation Unsuccessful
739 UPDATE igs_pe_res_dtls_int
740 SET status = '3',
741 error_code = p_error_code
742 WHERE interface_res_id = res_dtls_rec.interface_res_id;
743 IF l_enable_log = 'Y' THEN
744 igs_ad_imp_001.logerrormessage(res_dtls_rec.Interface_res_Id,p_error_code);
745 END IF;
746
747 END validate_record;
748 -- End Local Function
749
750
751 -- Begin Local procedure
752 PROCEDURE crt_res_dtls(
753 res_dtl_rec res_cur%ROWTYPE)
754 AS
755 l_rowid VARCHAR2(25);
756 l_Resident_Details_Id NUMBER;
757 l_count NUMBER(5);
758
759 BEGIN
760 -- Call Log header
761 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
762
763 IF (l_request_id IS NULL) THEN
764 l_request_id := fnd_global.conc_request_id;
765 END IF;
766
767 l_label := 'igs.plsql.igs_ad_imp_011.crt_res_dtls.begin';
768 l_debug_str := 'Interface Res Id : ' || res_dtl_rec.interface_res_id;
769
770 fnd_log.string_with_context( fnd_log.level_procedure,
771 l_label,
772 l_debug_str, NULL,
773 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
774 END IF;
775
776 -- there is no need to check for date overlap anymore (SWSCR01,02,04)
777
778 Igs_Pe_Res_Dtls_Pkg.Insert_Row (
779 X_Mode => 'R',
780 X_RowId => l_rowid,
781 X_Resident_Details_Id => l_Resident_Details_Id,
782 X_Person_Id => res_dtl_rec.Person_Id,
783 X_Residency_Class_cd => res_dtl_rec.Residency_Class_cd,
784 X_Residency_Status_cd => res_dtl_rec.Residency_Status_cd,
785 X_Evaluation_Date => res_dtl_rec.Evaluation_Date,
786 X_Evaluator => res_dtl_rec.Evaluator,
787 X_Comments => res_dtl_rec.Comments,
788 X_Attribute_Category => res_dtl_rec.Attribute_Category,
789 X_Attribute1 => res_dtl_rec.Attribute1,
790 X_Attribute2 => res_dtl_rec.Attribute2,
791 X_Attribute3 => res_dtl_rec.Attribute3,
792 X_Attribute4 => res_dtl_rec.Attribute4,
793 X_Attribute5 => res_dtl_rec.Attribute5,
794 X_Attribute6 => res_dtl_rec.Attribute6,
795 X_Attribute7 => res_dtl_rec.Attribute7,
796 X_Attribute8 => res_dtl_rec.Attribute8,
797 X_Attribute9 => res_dtl_rec.Attribute9,
798 X_Attribute10 => res_dtl_rec.Attribute10,
799 X_Attribute11 => res_dtl_rec.Attribute11,
800 X_Attribute12 => res_dtl_rec.Attribute12,
801 X_Attribute13 => res_dtl_rec.Attribute13,
802 X_Attribute14 => res_dtl_rec.Attribute14,
803 X_Attribute15 => res_dtl_rec.Attribute15,
804 X_Attribute16 => res_dtl_rec.Attribute16,
805 X_Attribute17 => res_dtl_rec.Attribute17,
806 X_Attribute18 => res_dtl_rec.Attribute18,
807 X_Attribute19 => res_dtl_rec.Attribute19,
808 X_Attribute20 => res_dtl_rec.Attribute20,
809 X_cal_type => res_dtl_rec.cal_type,
810 X_sequence_number => res_dtl_rec.sequence_number,
811 X_ORG_ID => FND_PROFILE.VALUE('ORG_ID')
812 );
813 --
814 -- Insertion Successful
815 --
816 l_status := '1';
817 p_error_code := NULL;
818
819 EXCEPTION
820 WHEN OTHERS THEN
821 --
822 -- Insertion Not Successful
823 --
824 l_status := '3';
825 p_error_code := 'E322';
826
827 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
828
829 IF (l_request_id IS NULL) THEN
830 l_request_id := fnd_global.conc_request_id;
831 END IF;
832
833 l_label := 'igs.plsql.igs_ad_imp_011.crt_apcnt_acad_hnr.exception'||'E322';
834
835 l_debug_str := 'IGS_AD_IMP_011.PRC_APCNT_ACADHNR_DTLS.CRT_APCNT_ACAD_HNR ' ||
836 'STATUS : 3' || 'ERRORCODE : E322 SQLERRM:' || SQLERRM;
837
838 fnd_log.string_with_context( fnd_log.level_exception,
839 l_label,
840 l_debug_str, NULL,
841 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
842 END IF;
843
844 IF l_enable_log = 'Y' THEN
845 igs_ad_imp_001.logerrormessage(res_dtl_rec.interface_res_id,'E322');
846 END IF;
847
848 END crt_res_dtls;
849 -- End Local Procedure
850
851
852 PROCEDURE update_res (c_null_hdlg_res_cur_rec chk_dup_cur%ROWTYPE,
853 res_dtl_rec res_cur%ROWTYPE) AS
854
855 -- there is no need to check for date overlap anymore (SWSCR01,02,04)
856 l_count NUMBER(5);
857
858 BEGIN
859
860
861 igs_pe_res_dtls_pkg.update_row (
862 x_rowid => c_null_hdlg_res_cur_rec.ROWID,
863 X_RESIDENT_DETAILS_ID => c_null_hdlg_res_cur_rec.RESIDENT_DETAILS_ID,
864 x_person_id => c_null_hdlg_res_cur_rec.person_id,
865 X_RESIDENCY_CLASS_CD => res_dtl_rec.RESIDENCY_CLASS_CD ,
866 X_RESIDENCY_STATUS_CD => res_dtl_rec.RESIDENCY_STATUS_CD ,
867 X_EVALUATION_DATE => res_dtl_rec.EVALUATION_DATE,
868 X_EVALUATOR => res_dtl_rec.EVALUATOR ,
869 X_COMMENTS => NVL(res_dtl_rec.COMMENTS ,c_null_hdlg_res_cur_rec.COMMENTS),
870 X_ATTRIBUTE_CATEGORY => NVL(res_dtl_rec.ATTRIBUTE_CATEGORY,c_null_hdlg_res_cur_rec.ATTRIBUTE_CATEGORY),
871 X_ATTRIBUTE1 => NVL(res_dtl_rec.ATTRIBUTE1, c_null_hdlg_res_cur_rec.ATTRIBUTE1),
872 X_ATTRIBUTE2 => NVL(res_dtl_rec.ATTRIBUTE2, c_null_hdlg_res_cur_rec.ATTRIBUTE2),
873 X_ATTRIBUTE3 => NVL(res_dtl_rec.ATTRIBUTE3, c_null_hdlg_res_cur_rec.ATTRIBUTE3),
874 X_ATTRIBUTE4 => NVL(res_dtl_rec.ATTRIBUTE4, c_null_hdlg_res_cur_rec.ATTRIBUTE4),
875 X_ATTRIBUTE5 => NVL(res_dtl_rec.ATTRIBUTE5, c_null_hdlg_res_cur_rec.ATTRIBUTE5),
876 X_ATTRIBUTE6 => NVL(res_dtl_rec.ATTRIBUTE6, c_null_hdlg_res_cur_rec.ATTRIBUTE6),
877 X_ATTRIBUTE7 => NVL(res_dtl_rec.ATTRIBUTE7, c_null_hdlg_res_cur_rec.ATTRIBUTE7),
878 X_ATTRIBUTE8 => NVL(res_dtl_rec.ATTRIBUTE8, c_null_hdlg_res_cur_rec.ATTRIBUTE8),
879 X_ATTRIBUTE9 => NVL(res_dtl_rec.ATTRIBUTE9, c_null_hdlg_res_cur_rec.ATTRIBUTE9),
880 X_ATTRIBUTE10 => NVL(res_dtl_rec.ATTRIBUTE10, c_null_hdlg_res_cur_rec.ATTRIBUTE10),
881 X_ATTRIBUTE11 => NVL(res_dtl_rec.ATTRIBUTE11, c_null_hdlg_res_cur_rec.ATTRIBUTE11),
882 X_ATTRIBUTE12 => NVL(res_dtl_rec.ATTRIBUTE12, c_null_hdlg_res_cur_rec.ATTRIBUTE12),
883 X_ATTRIBUTE13 => NVL(res_dtl_rec.ATTRIBUTE13, c_null_hdlg_res_cur_rec.ATTRIBUTE13),
884 X_ATTRIBUTE14 => NVL(res_dtl_rec.ATTRIBUTE14, c_null_hdlg_res_cur_rec.ATTRIBUTE14),
885 X_ATTRIBUTE15 => NVL(res_dtl_rec.ATTRIBUTE15, c_null_hdlg_res_cur_rec.ATTRIBUTE15),
886 X_ATTRIBUTE16 => NVL(res_dtl_rec.ATTRIBUTE16, c_null_hdlg_res_cur_rec.ATTRIBUTE16),
887 X_ATTRIBUTE17 => NVL(res_dtl_rec.ATTRIBUTE17, c_null_hdlg_res_cur_rec.ATTRIBUTE17),
888 X_ATTRIBUTE18 => NVL(res_dtl_rec.ATTRIBUTE18, c_null_hdlg_res_cur_rec.ATTRIBUTE18),
889 X_ATTRIBUTE19 => NVL(res_dtl_rec.ATTRIBUTE19, c_null_hdlg_res_cur_rec.ATTRIBUTE19),
890 X_ATTRIBUTE20 => NVL(res_dtl_rec.ATTRIBUTE20, c_null_hdlg_res_cur_rec.ATTRIBUTE20),
891 X_cal_type => res_dtl_rec.cal_type,
892 X_sequence_number => res_dtl_rec.sequence_number,
893 x_mode => 'R'
894 );
895
896
897 EXCEPTION
898 WHEN OTHERS THEN
899 l_status := '3';
900 p_error_code := 'E014';
901 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
902
903 IF (l_request_id IS NULL) THEN
904 l_request_id := fnd_global.conc_request_id;
905 END IF;
906
907 l_label := 'igs.plsql.igs_ad_imp_011.update_res.exception'||'E014';
908 l_debug_str := 'IGS_AD_IMP_011.PRC_PE_RES_DTLS.UPDATE_RES ' ||
909 'STATUS : 3' || 'ERROR CODE : E014 SQLERRM:' || SQLERRM;
910
911 fnd_log.string_with_context( fnd_log.level_exception,
912 l_label,
913 l_debug_str, NULL,
914 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
915 END IF;
916
917 IF l_enable_log = 'Y' THEN
918 igs_ad_imp_001.logerrormessage(res_dtl_rec.interface_res_id,'E014');
919 END IF;
920
921 END update_res;
922
923 -- Start of the Main Procedure PRC_PE_RES_DTLS
924
925 BEGIN
926
927 l_interface_run_id := igs_ad_imp_001.g_interface_run_id;
928 l_enable_log := igs_ad_imp_001.g_enable_log;
929 l_prog_label := 'igs.plsql.igs_ad_imp_011.prc_pe_res_dtls';
930 l_label := 'igs.plsql.igs_ad_imp_011.prc_pe_res_dtls.';
931
932 l_rule := igs_ad_imp_001.find_source_cat_rule(p_source_type_id,'PERSON_RESIDENCY_DETAILS');
933
934 -- If rule is E or I, then if the match_ind is not null, the combination is invalid
935 IF l_rule IN ('E','I') THEN
936 UPDATE igs_pe_res_dtls_int
937 SET status = cst_stat_val_3,
938 ERROR_CODE = cst_err_val_695 -- Error code depicting incorrect combination
939 WHERE match_ind IS NOT NULL
940 AND status = cst_stat_val_2
941 AND interface_run_id = l_interface_run_id;
942 END IF;
943
944 -- If rule is E and duplicate exists, update match_ind to 19 and status to 1
945 IF l_rule = 'E' THEN
946 UPDATE igs_pe_res_dtls_int mi
947 SET status = cst_stat_val_1,
948 match_ind = cst_mi_val_19
949 WHERE mi.interface_run_id = l_interface_run_id
950 AND mi.status = cst_stat_val_2
951 AND EXISTS ( SELECT '1'
952 FROM igs_pe_res_dtls_all pe, igs_ad_interface_all ii
953 WHERE ii.interface_run_id = l_interface_run_id
954 AND ii.interface_id = mi.interface_id
955 AND ii.person_id = pe.person_id
956 AND UPPER(mi.residency_class_cd) = pe.residency_class_cd
957 AND UPPER(mi.cal_type) = pe.cal_type
958 AND mi.sequence_number = pe.sequence_number);
959 END IF;
960
961 -- If rule is R and there match_ind is 18,19,22 or 23 then the records must have been
962 -- processed in prior runs and didn't get updated .. update to status 1
963 IF l_rule = 'R' THEN
964 UPDATE igs_pe_res_dtls_int
965 SET status = cst_stat_val_1
966 WHERE interface_run_id = l_interface_run_id
967 AND match_ind IN (cst_mi_val_18,cst_mi_val_19,cst_mi_val_22,cst_mi_val_23)
968 AND status = cst_stat_val_2;
969 END IF;
970
971 -- If rule is R and match_ind is neither 21 nor 25 then error
972 IF l_rule = 'R' THEN
973 UPDATE igs_pe_res_dtls_int
974 SET status = cst_stat_val_3,
975 ERROR_CODE = cst_err_val_695
976 WHERE interface_run_id = l_interface_run_id
977 AND status = cst_stat_val_2
978 AND (match_ind IS NOT NULL AND match_ind NOT IN (cst_mi_val_21,cst_mi_val_25));
979 END IF;
980
981 -- If rule is R, set duplicated records with no discrepancy to status 1 and match_ind 23
982 IF l_rule = 'R' THEN
983 UPDATE igs_pe_res_dtls_int mi
984 SET status = cst_stat_val_1,
985 match_ind = cst_mi_val_23
986 WHERE mi.interface_run_id = l_interface_run_id
987 AND mi.match_ind IS NULL
988 AND mi.status = cst_stat_val_2
989 AND EXISTS ( SELECT '1'
990 FROM igs_pe_res_dtls_all pe, igs_ad_interface_all ii
991 WHERE ii.interface_run_id = l_interface_run_id
992 AND ii.interface_id = mi.interface_id
993 AND ii.person_id = pe.person_id
994 AND pe.residency_class_cd = UPPER(mi.Residency_class_cd)
995 AND pe.cal_type = UPPER(mi.cal_type)
996 AND pe.sequence_number = mi.sequence_number
997 AND pe.residency_status_cd = UPPER(mi.Residency_status_cd)
998 AND UPPER(pe.evaluator) = UPPER(mi.evaluator)
999 AND TRUNC(pe.evaluation_date) = TRUNC(mi.evaluation_date)
1000 AND ((UPPER(pe.attribute1) = UPPER(mi.attribute1)) OR (pe.attribute1 IS NULL AND mi.attribute1 IS NULL))
1001 AND ((UPPER(pe.attribute2) = UPPER(mi.attribute2)) OR (pe.attribute2 IS NULL AND mi.attribute2 IS NULL))
1002 AND ((UPPER(pe.attribute3) = UPPER(mi.attribute3)) OR (pe.attribute3 IS NULL AND mi.attribute3 IS NULL))
1003 AND ((UPPER(pe.attribute4) = UPPER(mi.attribute4)) OR (pe.attribute4 IS NULL AND mi.attribute4 IS NULL))
1004 AND ((UPPER(pe.attribute5) = UPPER(mi.attribute5)) OR (pe.attribute5 IS NULL AND mi.attribute5 IS NULL))
1005 AND ((UPPER(pe.attribute6) = UPPER(mi.attribute6)) OR (pe.attribute6 IS NULL AND mi.attribute6 IS NULL))
1006 AND ((UPPER(pe.attribute7) = UPPER(mi.attribute7)) OR (pe.attribute7 IS NULL AND mi.attribute7 IS NULL))
1007 AND ((UPPER(pe.attribute8) = UPPER(mi.attribute8)) OR (pe.attribute8 IS NULL AND mi.attribute8 IS NULL))
1008 AND ((UPPER(pe.attribute9) = UPPER(mi.attribute9)) OR (pe.attribute9 IS NULL AND mi.attribute9 IS NULL))
1009 AND ((UPPER(pe.attribute10) = UPPER(mi.attribute10)) OR (pe.attribute10 IS NULL AND mi.attribute10 IS NULL))
1010 AND ((UPPER(pe.attribute11) = UPPER(mi.attribute11)) OR (pe.attribute11 IS NULL AND mi.attribute11 IS NULL))
1011 AND ((UPPER(pe.attribute12) = UPPER(mi.attribute12)) OR (pe.attribute12 IS NULL AND mi.attribute12 IS NULL))
1012 AND ((UPPER(pe.attribute13) = UPPER(mi.attribute13)) OR (pe.attribute13 IS NULL AND mi.attribute13 IS NULL))
1013 AND ((UPPER(pe.attribute14) = UPPER(mi.attribute14)) OR (pe.attribute14 IS NULL AND mi.attribute14 IS NULL))
1014 AND ((UPPER(pe.attribute15) = UPPER(mi.attribute15)) OR (pe.attribute15 IS NULL AND mi.attribute15 IS NULL))
1015 AND ((UPPER(pe.attribute16) = UPPER(mi.attribute16)) OR (pe.attribute16 IS NULL AND mi.attribute16 IS NULL))
1016 AND ((UPPER(pe.attribute17) = UPPER(mi.attribute17)) OR (pe.attribute17 IS NULL AND mi.attribute17 IS NULL))
1017 AND ((UPPER(pe.attribute18) = UPPER(mi.attribute18)) OR (pe.attribute18 IS NULL AND mi.attribute18 IS NULL))
1018 AND ((UPPER(pe.attribute19) = UPPER(mi.attribute19)) OR (pe.attribute19 IS NULL AND mi.attribute19 IS NULL))
1019 AND ((UPPER(pe.attribute20) = UPPER(mi.attribute20)) OR (pe.attribute20 IS NULL AND mi.attribute20 IS NULL)));
1020 END IF;
1021
1022 -- If rule is R records still exist, they are duplicates and have discrepancy .. update status=3,match_ind=20
1023 IF l_rule = 'R' THEN
1024 UPDATE igs_pe_res_dtls_int mi
1025 SET status = cst_stat_val_3,
1026 match_ind = cst_mi_val_20
1027 WHERE mi.interface_run_id = l_interface_run_id
1028 AND mi.match_ind IS NULL
1029 AND mi.status = cst_stat_val_2
1030 AND EXISTS (SELECT '1'
1031 FROM igs_pe_res_dtls_all pe, igs_ad_interface_all ii
1032 WHERE ii.interface_run_id = l_interface_run_id
1033 AND ii.interface_id = mi.interface_id
1034 AND ii.person_id = pe.person_id
1035 AND UPPER(mi.residency_class_cd) = pe.residency_class_cd
1036 AND UPPER(mi.cal_type) = pe.cal_type
1037 AND mi.sequence_number = pe.sequence_number);
1038 END IF;
1039
1040 FOR res_dtl_rec IN res_cur(l_interface_run_id)
1041 LOOP
1042 l_processed_records := l_processed_records + 1 ;
1043 --
1044 -- Set the status, error_code, match_ind variables to the existing values
1045 --
1046 l_status := res_dtl_rec.status;
1047 p_error_code := res_dtl_rec.ERROR_CODE;
1048 l_match_ind := res_dtl_rec.match_ind;
1049 res_dtl_rec.residency_class_cd := UPPER(res_dtl_rec.residency_class_cd);
1050 res_dtl_rec.residency_status_cd := UPPER(res_dtl_rec.residency_status_cd);
1051 res_dtl_rec.evaluation_date := TRUNC(res_dtl_rec.evaluation_date);
1052 res_dtl_rec.cal_type := UPPER(res_dtl_rec.cal_type);
1053
1054 BEGIN
1055
1056 chk_dup_rec.residency_class_cd := NULL;
1057 OPEN chk_dup_cur(res_dtl_rec);
1058 FETCH chk_dup_cur INTO chk_dup_rec;
1059 CLOSE chk_dup_cur;
1060
1061 IF chk_dup_rec.residency_class_cd IS NOT NULL THEN
1062 -- To be changed as a generic change
1063 IF l_rule = 'I' THEN
1064 BEGIN
1065 -- validate the record.
1066 validate_record (res_dtl_rec, p_error_code,'U');
1067 IF p_error_code IS NULL THEN
1068 -- call the update.
1069 update_res (chk_dup_rec , res_dtl_rec);
1070 l_match_ind := '18';
1071 l_status := '1';
1072 ELSIF p_error_code IS NOT NULL THEN
1073 l_match_ind := NULL;
1074 l_status := '3';
1075 END IF;
1076 END;
1077 ELSIF l_rule = 'R' THEN
1078 IF res_dtl_rec.match_ind = '21' THEN
1079 BEGIN
1080 validate_record ( res_dtl_rec, p_error_code,'U');
1081 IF p_error_code IS NULL THEN
1082 -- call the update.
1083 update_res (chk_dup_rec , res_dtl_rec);
1084 l_match_ind := '18';
1085 l_status := '1';
1086 ELSIF p_error_code IS NOT NULL THEN
1087 l_match_ind := NULL;
1088 l_status := '3';
1089 END IF;
1090 END;
1091 END IF;
1092 END IF;
1093 ELSE
1094 validate_record ( res_dtl_rec, p_error_code,'I');
1095 IF p_error_code IS NULL THEN
1096 -- Validation Successful, so create record
1097 crt_res_dtls( res_dtl_rec);
1098 ELSIF p_error_code IS NOT NULL THEN
1099 l_status := '3';
1100 END IF;
1101 END IF;
1102 --
1103 -- Update the interface record with the status, error_code, match_ind, only when NOT coming from UCAS.
1104 --
1105 UPDATE igs_pe_res_dtls_int
1106 SET status = l_status,
1107 error_code = p_error_code,
1108 match_ind = l_match_ind
1109 WHERE interface_res_id = res_dtl_rec.interface_res_id;
1110
1111 EXCEPTION
1112 WHEN OTHERS THEN
1113 UPDATE igs_pe_res_dtls_int
1114 SET
1115 status = '3',
1116 error_code = 'E518'
1117 WHERE interface_res_id = res_dtl_rec.interface_res_id;
1118 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
1119
1120 IF (l_request_id IS NULL) THEN
1121 l_request_id := fnd_global.conc_request_id;
1122 END IF;
1123
1124 l_label := 'igs.plsql.igs_ad_imp_011.prc_pe_res_dtls.exception'||'E518';
1125
1126 l_debug_str := 'IGS_AD_IMP_011.PRC_PE_RES_DTLS ' ||
1127 'INTERFACE RES ID : ' || TO_CHAR(res_dtl_rec.interface_res_id) ||
1128 'STATUS : 3' || 'ERRORCODE : E518 SQLERRM:' || SQLERRM;
1129
1130 fnd_log.string_with_context( fnd_log.level_exception,
1131 l_label,
1132 l_debug_str, NULL,
1133 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1134 END IF;
1135
1136 IF l_enable_log = 'Y' THEN
1137 igs_ad_imp_001.logerrormessage(res_dtl_rec.interface_res_id,'E518');
1138 END IF;
1139
1140 END ;
1141 IF l_processed_records = 100 THEN
1142 COMMIT;
1143 l_processed_records := 0 ;
1144 END IF;
1145 END LOOP;
1146 EXCEPTION
1147 WHEN OTHERS THEN
1148 -- Close the cursors if open
1149 IF chk_dup_cur%ISOPEN THEN
1150 CLOSE chk_dup_cur;
1151 END IF;
1152 END prc_pe_res_dtls;
1153
1154
1155 PROCEDURE prc_special_needs (
1156 p_source_type_id IN NUMBER,
1157 p_batch_id IN NUMBER )
1158 AS
1159 /*
1160 || Created By : [email protected]
1161 || Created On : 06-Jul-2001
1162 || Purpose : This procedure is for importing person Special Need Information.
1163 || DLD: Person Interface DLD. Enh Bug# 2103692.
1164 || Known limitations, enhancements or remarks :
1165 || Change History :
1166 || Who When What
1167 || npalanis 6-JAN-2003 Bug : 2734697
1168 || code added to commit after import of every
1169 || 100 records .New variable l_processed_records added
1170 || pkpatel 23-DEC-2002 Bug No: 2722027
1171 || Added NVL in the cursor dup_chk_disability_cur
1172 || pkpatel 22-JUN-2001 Bug no.2466466
1173 || Modified the parent/child processing.
1174 || pkpatel 2-JUN-2003 Bug no.2986796 (special Needs CCR, jul'03)
1175 || MOdified the processing for NONE records. Modified to use bind variables.
1176 || pkpatel 20-Sep-2005 Bug 3716764 (Modified the Update to disability_int table under sp_disability_cur loop)
1177 || (reverse chronological order - newest change first)
1178 */
1179
1180 l_default_date DATE := TO_DATE('4712/12/31','YYYY/MM/DD');
1181
1182 -- Variable to hold the Disability ID of the Parent Person Disability Record
1183 l_disability_id igs_pe_pers_disablty.igs_pe_pers_disablty_id%TYPE;
1184 l_processed_records NUMBER(5) := 0;
1185 -- Variables for logging
1186 l_prog_label VARCHAR2(4000);
1187 l_label VARCHAR2(4000);
1188 l_debug_str VARCHAR2(4000);
1189 l_enable_log VARCHAR2(1);
1190 l_request_id NUMBER(10);
1191 l_interface_run_id igs_ad_interface_all.interface_run_id%TYPE;
1192 l_rule igs_ad_source_cat.discrepancy_rule_cd%TYPE;
1193
1194 --Pick up the records for processing from the Special Needs Disability Interface Table
1195 CURSOR disability_cur(cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE) IS
1196 SELECT mi.*,i.person_id
1197 FROM igs_ad_disablty_int_all mi,igs_ad_interface_all i
1198 WHERE mi.interface_run_id = cp_interface_run_id
1199 AND i.interface_run_id = cp_interface_run_id
1200 AND mi.interface_id = i.interface_id
1201 AND mi.status = '2';
1202
1203 -- Pick up the records processed before the loop from the Disability Interface Table
1204 CURSOR sp_disability_cur(cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE) IS
1205 SELECT mi.*,i.person_id
1206 FROM igs_ad_disablty_int_all mi,igs_ad_interface_all i
1207 WHERE mi.interface_run_id = cp_interface_run_id
1208 AND i.interface_run_id = cp_interface_run_id
1209 AND mi.interface_id = i.interface_id
1210 AND mi.status = '1'
1211 AND mi.match_ind IN (cst_mi_val_23,cst_mi_val_19);
1212
1213 --Pick up the records for processing from the Special Needs Service Interface Table
1214 CURSOR sn_service_cur(cp_interface_disablty_id igs_ad_disablty_int.interface_disablty_id%TYPE,
1215 cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE ) IS
1216 SELECT ai.*
1217 FROM igs_pe_sn_srvce_int ai,
1218 igs_ad_disablty_int_all ad
1219 WHERE ai.interface_run_id = cp_interface_run_id AND
1220 ad.interface_run_id = cp_interface_run_id AND
1221 ai.interface_disablty_id = cp_interface_disablty_id AND
1222 ai.interface_disablty_id = ad.interface_disablty_id AND
1223 ai.status = '2';
1224
1225 --Pick up the records for processing from the Special Needs Contact Interface Table
1226 CURSOR sn_contact_cur(cp_interface_disablty_id igs_ad_disablty_int.interface_disablty_id%TYPE,
1227 cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE ) IS
1228 SELECT ai.*
1229 FROM igs_pe_sn_conct_int ai,
1230 igs_ad_disablty_int_all ad
1231 WHERE ai.interface_run_id = cp_interface_run_id AND
1232 ad.interface_run_id = cp_interface_run_id AND
1233 ai.interface_disablty_id = cp_interface_disablty_id AND
1234 ai.interface_disablty_id = ad.interface_disablty_id AND
1235 ai.status = '2';
1236
1237
1238
1239 --Cursor to check whether the Record in Interface Table already exists in OSS table for Disability
1240 CURSOR dup_chk_disability_cur(cp_disability_rec disability_cur%ROWTYPE) IS
1241 SELECT rowid, pd.*
1242 FROM igs_pe_pers_disablty pd
1243 WHERE pd.disability_type = cp_disability_rec.disability_type AND
1244 pd.person_id = cp_disability_rec.person_id AND
1245 NVL(TRUNC(pd.start_date),l_default_date) = NVL(TRUNC(cp_disability_rec.start_date),l_default_date);
1246
1247 --Cursor to check whether the Record in Interface Table already exists in OSS table for Special Need Service
1248 -- kumma, changed the duplicate check criteria to include the start_dt as a part of the unique key
1249
1250 CURSOR dup_chk_sn_service_cur(cp_disability_id igs_pe_sn_service.disability_id%TYPE,
1251 cp_special_service_cd igs_pe_sn_service.special_service_cd%TYPE,
1252 cp_start_dt igs_pe_sn_service.start_dt%TYPE) IS
1253 SELECT rowid, sn.*
1254 FROM igs_pe_sn_service sn
1255 WHERE sn.disability_id = cp_disability_id AND
1256 sn.special_service_cd = cp_special_service_cd AND
1257 NVL(TRUNC(sn.start_dt),l_default_date) = NVL(TRUNC(cp_start_dt),l_default_date);
1258
1259 --Cursor to check whether the Record in Interface Table already exists in OSS table for Special Need Contact
1260 CURSOR dup_chk_sn_contact_cur(cp_disability_id igs_pe_sn_contact.disability_id%TYPE,
1261 cp_contact_name igs_pe_sn_contact.contact_name%TYPE,
1262 cp_contact_date igs_pe_sn_contact.contact_date%TYPE) IS
1263 SELECT rowid, sn.*
1264 FROM igs_pe_sn_contact sn
1265 WHERE sn.disability_id = cp_disability_id AND
1266 NVL(sn.contact_name,'~') = NVL(cp_contact_name,'~') AND
1267 NVL(TRUNC(sn.contact_date),l_default_date) = NVL(TRUNC(cp_contact_date),l_default_date);
1268
1269
1270 CURSOR check_none_disablity_cur(cp_disability_type igs_ad_disbl_type.disability_type%TYPE,
1271 cp_govt_disability_type igs_ad_disbl_type.govt_disability_type%TYPE) IS
1272 SELECT 'X'
1273 FROM igs_ad_disbl_type
1274 WHERE disability_type = cp_disability_type AND
1275 govt_disability_type = cp_govt_disability_type;
1276
1277 disability_rec disability_cur%ROWTYPE;
1278 sp_disability_rec sp_disability_cur%ROWTYPE;
1279 sn_service_rec sn_service_cur%ROWTYPE;
1280 sn_contact_rec sn_contact_cur%ROWTYPE;
1281 dup_chk_disability_rec dup_chk_disability_cur%ROWTYPE;
1282 dup_chk_sn_service_rec dup_chk_sn_service_cur%ROWTYPE;
1283 dup_chk_sn_contact_rec dup_chk_sn_contact_cur%ROWTYPE;
1284
1285 -- Start of Local Procedure validate_disability
1286 --
1287 PROCEDURE validate_disability(p_disability_rec disability_cur%ROWTYPE,
1288 l_success OUT NOCOPY VARCHAR2,
1289 l_error_code OUT NOCOPY VARCHAR2)
1290 IS
1291 /*
1292 || Created By : [email protected]
1293 || Created On : 22-NOV-2001
1294 || Purpose : This is a private procedure is for validating Person Disability Information.
1295 || DLD: Person Interface DLD. Enh Bug# 2103692.
1296 || Known limitations, enhancements or remarks :
1297 || Change History :
1298 || Who When What
1299 || (reverse chronological order - newest change first)
1300 || npalanis 16-JUN-2002 Bug -2327077
1301 || Validation done to check if the disability type is None or not.
1302 || npalanis 23-JUL-2002 Bug - 2421897
1303 || Error codes E008 changed to valid ones
1304 */
1305 CURSOR validate_disablty_cur(cp_disability_type igs_ad_disbl_type.disability_type%TYPE,
1306 cp_closed_ind igs_ad_disbl_type.closed_ind%TYPE) IS
1307 SELECT 'X'
1308 FROM igs_ad_disbl_type
1309 WHERE disability_type = cp_disability_type AND
1310 closed_ind = cp_closed_ind;
1311
1312 CURSOR validate_interviewer_cur(cp_interviewer_id igs_pe_person_base_v.person_id%TYPE) IS
1313 SELECT birth_date
1314 FROM igs_pe_person_base_v
1315 WHERE person_id = cp_interviewer_id;
1316
1317 CURSOR birth_dt_cur(p_person_id IGS_AD_INTERFACE.PERSON_ID%TYPE) IS
1318 SELECT birth_date
1319 FROM igs_pe_person_base_v
1320 WHERE person_id= p_person_id;
1321
1322 l_birth_date IGS_AD_INTERFACE.BIRTH_DT%TYPE;
1323 l_person_id IGS_AD_INTERFACE.PERSON_ID%TYPE;
1324 l_count NUMBER(5);
1325 l_var VARCHAR2(2);
1326
1327 validate_disablty_rec validate_disablty_cur%ROWTYPE;
1328 validate_interviewer_rec validate_interviewer_cur%ROWTYPE;
1329 BEGIN
1330
1331 -- Disability Validation
1332 OPEN validate_disablty_cur(p_disability_rec.disability_type,'N');
1333 FETCH validate_disablty_cur INTO validate_disablty_rec;
1334 IF validate_disablty_cur%NOTFOUND THEN
1335 CLOSE validate_disablty_cur;
1336 l_error_code := 'E098' ;
1337 RAISE NO_DATA_FOUND;
1338 ELSE
1339 CLOSE validate_disablty_cur;
1340 l_error_code := NULL;
1341 END IF;
1342
1343 -- Early Registration Indicator Validation
1344 IF p_disability_rec.elig_early_reg_ind NOT IN('Y','N') THEN
1345 l_error_code := 'E139' ;
1346 RAISE NO_DATA_FOUND;
1347 END IF;
1348
1349 -- Special Allowance Validation
1350 IF ( p_disability_rec.special_allow_cd IS NOT NULL) THEN
1351 IF NOT
1352 (igs_pe_pers_imp_001.validate_lookup_type_code('PE_SN_ALLOW',p_disability_rec.special_allow_cd,8405))
1353 THEN
1354 l_error_code := 'E140' ;
1355 RAISE NO_DATA_FOUND;
1356 ELSE
1357 l_error_code := NULL;
1358 END IF;
1359 END IF;
1360
1361 -- Support Level Validation
1362 IF ( p_disability_rec.support_level_cd IS NOT NULL) THEN
1363 IF NOT
1364 (igs_pe_pers_imp_001.validate_lookup_type_code('PE_SN_ADD_SUP_LVL',p_disability_rec.support_level_cd,8405))
1365 THEN
1366 l_error_code := 'E141' ;
1367 RAISE NO_DATA_FOUND;
1368 ELSE
1369 l_error_code := NULL;
1370 END IF;
1371 END IF;
1372 --
1373 -- Start Date and End Date validation
1374 --
1375 IF p_disability_rec.start_date > NVL(p_disability_rec.end_date,l_default_date) THEN
1376 l_error_code := 'E142' ;
1377 RAISE NO_DATA_FOUND;
1378 END IF;
1379
1380 -- Validate that birth date , start date and interviewer_date , birth date
1381 OPEN birth_dt_cur(p_disability_rec.person_id) ;
1382 FETCH birth_dt_cur INTO l_birth_date;
1383 IF l_birth_date IS NOT NULL THEN
1384 IF p_disability_rec.start_date < l_birth_date THEN
1385 l_error_code := 'E222' ;
1386 CLOSE birth_dt_cur;
1387 RAISE NO_DATA_FOUND;
1388 END IF;
1389 IF p_disability_rec.interviewer_date IS NOT NULL AND p_disability_rec.interviewer_date < l_birth_date THEN
1390 l_error_code := 'E281' ;
1391 CLOSE birth_dt_cur;
1392 RAISE NO_DATA_FOUND;
1393 END IF;
1394 END IF;
1395 CLOSE birth_dt_cur;
1396
1397
1398 --Validation check of Descriptive Flexfield
1399 --
1400 IF NOT igs_ad_imp_018.validate_desc_flex(
1401 p_attribute_category =>p_disability_rec.attribute_category,
1402 p_attribute1 =>p_disability_rec.attribute1 ,
1403 p_attribute2 =>p_disability_rec.attribute2 ,
1404 p_attribute3 =>p_disability_rec.attribute3 ,
1405 p_attribute4 =>p_disability_rec.attribute4 ,
1406 p_attribute5 =>p_disability_rec.attribute5 ,
1407 p_attribute6 =>p_disability_rec.attribute6 ,
1408 p_attribute7 =>p_disability_rec.attribute7 ,
1409 p_attribute8 =>p_disability_rec.attribute8 ,
1410 p_attribute9 =>p_disability_rec.attribute9 ,
1411 p_attribute10 =>p_disability_rec.attribute10 ,
1412 p_attribute11 =>p_disability_rec.attribute11 ,
1413 p_attribute12 =>p_disability_rec.attribute12 ,
1414 p_attribute13 =>p_disability_rec.attribute13 ,
1415 p_attribute14 =>p_disability_rec.attribute14 ,
1416 p_attribute15 =>p_disability_rec.attribute15 ,
1417 p_attribute16 =>p_disability_rec.attribute16 ,
1418 p_attribute17 =>p_disability_rec.attribute17 ,
1419 p_attribute18 =>p_disability_rec.attribute18 ,
1420 p_attribute19 =>p_disability_rec.attribute19 ,
1421 p_attribute20 =>p_disability_rec.attribute20 ,
1422 p_desc_flex_name =>'IGS_PE_PERS_DISABLTY_FLEX' ) THEN
1423
1424 l_error_code := 'E143' ;
1425 RAISE NO_DATA_FOUND;
1426 END IF;
1427
1428 --
1429 -- Interviewer ID Validation
1430 --
1431 IF ( p_disability_rec.interviewer_id IS NOT NULL) THEN
1432
1433 IF p_disability_rec.person_id = p_disability_rec.interviewer_id THEN
1434 l_error_code := 'E144' ;
1435 RAISE NO_DATA_FOUND;
1436 END IF;
1437
1438 OPEN validate_interviewer_cur(p_disability_rec.interviewer_id);
1439 FETCH validate_interviewer_cur INTO validate_interviewer_rec;
1440
1441 IF validate_interviewer_cur%NOTFOUND THEN
1442 CLOSE validate_interviewer_cur;
1443 l_error_code := 'E144' ;
1444 RAISE NO_DATA_FOUND;
1445 ELSE
1446 CLOSE validate_interviewer_cur;
1447 l_error_code := NULL;
1448 END IF;
1449 END IF;
1450
1451 UPDATE igs_ad_disablty_int_all
1452 SET status = '1',
1453 ERROR_CODE = NULL
1454 WHERE interface_disablty_id = p_disability_rec.interface_disablty_id;
1455
1456 l_success := 'Y';
1457 EXCEPTION
1458 WHEN NO_DATA_FOUND THEN
1459
1460 IF l_enable_log = 'Y' THEN
1461 igs_ad_imp_001.logerrormessage(p_disability_rec.interface_disablty_id,l_error_code,'IGS_AD_DISABLTY_INT_ALL');
1462 END IF;
1463
1464 UPDATE igs_ad_disablty_int_all
1465 SET status = '3',
1466 ERROR_CODE = l_error_code
1467 WHERE interface_disablty_id = p_disability_rec.interface_disablty_id;
1468
1469 l_success := 'N';
1470 END validate_disability;
1471 --
1472 -- End Local Validate_Disability
1473 --
1474
1475 -- Start of Local Procedure validate_sn_service
1476 --
1477 PROCEDURE validate_sn_service(p_sn_service_rec sn_service_cur%ROWTYPE,
1478 p_person_id igs_ad_interface.person_id%type,
1479 l_success OUT NOCOPY VARCHAR2,
1480 l_error_code OUT NOCOPY VARCHAR2)
1481 IS
1482 /*
1483 || Created By : [email protected]
1484 || Created On : 22-NOV-2001
1485 || Purpose : This is a private procedure is for validating Person Special Need Information.
1486 || DLD: Person Interface DLD. Enh Bug# 2103692.
1487 || Known limitations, enhancements or remarks :
1488 || Change History :
1489 || Who When What
1490 || kumma 21-OCT-2002 Added validations for start date and end date
1491 || (reverse chronological order - newest change first)
1492 */
1493
1494 CURSOR birth_dt_cur(cp_person_id igs_pe_person_base_v.person_id%TYPE) IS
1495 SELECT birth_date
1496 FROM igs_pe_person_base_v
1497 WHERE person_id= cp_person_id;
1498
1499 l_birth_date IGS_AD_INTERFACE.BIRTH_DT%TYPE;
1500 BEGIN
1501 --
1502 -- Special Service code
1503 --
1504
1505 IF NOT
1506 (igs_pe_pers_imp_001.validate_lookup_type_code('PE_SN_SERVICE',p_sn_service_rec.special_service_cd,8405))
1507 THEN
1508 l_error_code := 'E149';
1509 RAISE NO_DATA_FOUND;
1510 ELSE
1511 l_error_code := NULL;
1512 END IF;
1513
1514 --
1515 --Documented Indicator
1516 --
1517
1518 IF p_sn_service_rec.documented_ind NOT IN ('N', 'Y') THEN
1519
1520 l_error_code := 'E150';
1521 RAISE NO_DATA_FOUND;
1522 END IF;
1523
1524
1525 -- kumma, 2608360 Added validations for start date and end_date
1526
1527 IF p_sn_service_rec.start_dt IS NULL AND p_sn_service_rec.end_dt IS NOT NULL THEN
1528
1529 l_error_code := 'E326';
1530 RAISE NO_DATA_FOUND;
1531 END IF;
1532
1533
1534
1535 IF p_sn_service_rec.start_dt IS NOT NULL AND p_sn_service_rec.end_dt IS NOT NULL THEN
1536 IF TRUNC(p_sn_service_rec.start_dt) > TRUNC(p_sn_service_rec.end_dt) THEN
1537
1538 l_error_code := 'E208';
1539 RAISE NO_DATA_FOUND;
1540 END IF;
1541 END IF;
1542
1543
1544 IF p_sn_service_rec.start_dt IS NOT NULL THEN
1545 OPEN birth_dt_cur(p_person_id);
1546 FETCH birth_dt_cur INTO l_birth_date;
1547 IF l_birth_date IS NOT NULL AND TRUNC(p_sn_service_rec.start_dt) < TRUNC(l_birth_date) THEN
1548 l_error_code := 'E222';
1549 CLOSE birth_dt_cur;
1550 RAISE NO_DATA_FOUND;
1551 END IF;
1552 CLOSE birth_dt_cur;
1553 END IF;
1554
1555 UPDATE igs_pe_sn_srvce_int
1556 SET status = '1',
1557 ERROR_CODE = NULL
1558 WHERE interface_sn_service_id = p_sn_service_rec.interface_sn_service_id;
1559
1560 l_success := 'Y';
1561
1562 EXCEPTION
1563
1564 WHEN NO_DATA_FOUND THEN
1565 IF l_enable_log = 'Y' THEN
1566 igs_ad_imp_001.logerrormessage(p_sn_service_rec.interface_sn_service_id,l_error_code,'IGS_PE_SN_SRVCE_INT');
1567 END IF;
1568
1569
1570 UPDATE igs_pe_sn_srvce_int
1571 SET status = '3',
1572 ERROR_CODE = l_error_code
1573 WHERE interface_sn_service_id = p_sn_service_rec.interface_sn_service_id;
1574
1575 l_success := 'N';
1576
1577 END validate_sn_service;
1578 -- Start of Local Procedure validate_sn_contact
1579 --
1580 PROCEDURE validate_sn_contact(p_sn_contact_rec sn_contact_cur%ROWTYPE,
1581 p_person_id igs_ad_interface.person_id%TYPE,
1582 l_success OUT NOCOPY VARCHAR2,
1583 l_error_code OUT NOCOPY VARCHAR2)
1584 IS
1585 /*
1586 || Created By : npalanis
1587 || Created On : 21-May-2002
1588 || Purpose : Adding validation to make it consistent with form functions
1589 || Known limitations, enhancements or remarks :
1590 || Change History :
1591 || Who When What
1592 || (reverse chronological order - newest change first)
1593 */
1594 CURSOR birth_dt_cur(cp_person_id igs_pe_person_base_v.person_id%TYPE) IS
1595 SELECT birth_date
1596 FROM igs_pe_person_base_v
1597 WHERE person_id= cp_person_id;
1598
1599 l_birth_date IGS_AD_INTERFACE.BIRTH_DT%TYPE;
1600 BEGIN
1601 IF p_sn_contact_rec.contact_date IS NOT NULL THEN
1602 OPEN birth_dt_cur(p_person_id);
1603 FETCH birth_dt_cur INTO l_birth_date;
1604 IF l_birth_date IS NOT NULL AND p_sn_contact_rec.contact_date < l_birth_date THEN
1605 l_error_code := 'E282';
1606 CLOSE birth_dt_cur;
1607 RAISE NO_DATA_FOUND;
1608 END IF;
1609 CLOSE birth_dt_cur;
1610 END IF;
1611
1612 l_success := 'Y';
1613
1614 EXCEPTION
1615 WHEN NO_DATA_FOUND THEN
1616 IF l_enable_log = 'Y' THEN
1617 igs_ad_imp_001.logerrormessage(p_sn_contact_rec.interface_sn_contact_id,l_error_code,'IGS_PE_SN_CONCT_INT');
1618 END IF;
1619
1620 UPDATE igs_pe_sn_conct_int
1621 SET status = '3',
1622 ERROR_CODE = l_error_code
1623 WHERE interface_sn_contact_id = p_sn_contact_rec.interface_sn_contact_id;
1624
1625 l_success := 'N';
1626
1627 END validate_sn_contact;
1628
1629
1630
1631 --Local Procedure to create a Disability record
1632 PROCEDURE create_disability(p_disability_rec disability_cur%ROWTYPE,
1633 p_error_code OUT NOCOPY igs_ad_disablty_int.ERROR_CODE%TYPE)
1634 AS
1635 /*
1636 || Created By : [email protected]
1637 || Created On : 22-NOV-2001
1638 || Purpose : This is a private procedure is for creating Person Disability Record.
1639 || DLD: Person Interface DLD. Enh Bug# 2103692.
1640 || Known limitations, enhancements or remarks :
1641 || Change History :
1642 || Who When What
1643 || (reverse chronological order - newest change first)
1644 */
1645 l_rowid VARCHAR2(25);
1646 l_success VARCHAR2(1);
1647 l_message_name VARCHAR2(30);
1648 l_app VARCHAR2(50);
1649
1650 BEGIN
1651
1652 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
1653
1654 IF (l_request_id IS NULL) THEN
1655 l_request_id := fnd_global.conc_request_id;
1656 END IF;
1657
1658 l_label := 'igs.plsql.igs_ad_imp_011.create_disability.begin';
1659 l_debug_str := 'Interface Disability Id : ' || p_disability_rec.interface_disablty_id;
1660
1661 fnd_log.string_with_context( fnd_log.level_procedure,
1662 l_label,
1663 l_debug_str, NULL,
1664 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1665 END IF;
1666
1667 validate_disability(p_disability_rec, l_success, p_error_code);
1668 IF l_success = 'Y' THEN -- Successful Validation
1669
1670 igs_pe_pers_disablty_pkg.insert_row (
1671 x_rowid => l_rowid ,
1672 X_IGS_PE_PERS_DISABLTY_ID => l_disability_id,
1673 x_person_id => p_disability_rec.person_id,
1674 x_disability_type => p_disability_rec.disability_type,
1675 x_contact_ind => NULL,
1676 x_special_allow_cd => p_disability_rec.special_allow_cd,
1677 x_support_level_cd => p_disability_rec.support_level_cd,
1678 x_documented => NULL,
1679 x_special_service_id => NULL,
1680 x_attribute_category => p_disability_rec.attribute_category,
1681 x_attribute1 => p_disability_rec.attribute1,
1682 x_attribute2 => p_disability_rec.attribute2,
1683 x_attribute3 => p_disability_rec.attribute3,
1684 x_attribute4 => p_disability_rec.attribute4,
1685 x_attribute5 => p_disability_rec.attribute5,
1686 x_attribute6 => p_disability_rec.attribute6,
1687 x_attribute7 => p_disability_rec.attribute7,
1688 x_attribute8 => p_disability_rec.attribute8,
1689 x_attribute9 => p_disability_rec.attribute9,
1690 x_attribute10 => p_disability_rec.attribute10,
1691 x_attribute11 => p_disability_rec.attribute11,
1692 x_attribute12 => p_disability_rec.attribute12,
1693 x_attribute13 => p_disability_rec.attribute13,
1694 x_attribute14 => p_disability_rec.attribute14,
1695 x_attribute15 => p_disability_rec.attribute15,
1696 x_attribute16 => p_disability_rec.attribute16,
1697 x_attribute17 => p_disability_rec.attribute17,
1698 x_attribute18 => p_disability_rec.attribute18,
1699 x_attribute19 => p_disability_rec.attribute19,
1700 x_attribute20 => p_disability_rec.attribute20,
1701 x_elig_early_reg_ind => NVL(p_disability_rec.elig_early_reg_ind,'N'),
1702 x_start_date => p_disability_rec.start_date,
1703 x_end_date => p_disability_rec.end_date,
1704 x_info_source => p_disability_rec.info_source,
1705 x_interviewer_id => p_disability_rec.interviewer_id,
1706 x_interviewer_date => p_disability_rec.interviewer_date,
1707 x_mode => 'R'
1708 );
1709 p_error_code := NULL;
1710
1711 UPDATE igs_ad_disablty_int_all
1712 SET status ='1',
1713 ERROR_CODE = p_error_code
1714 WHERE interface_disablty_id = p_disability_rec.interface_disablty_id;
1715
1716 --ELSE 'Validation is Unsuccessful. It has been taken care in Validate_Disability Procedure'
1717
1718 END IF;
1719 EXCEPTION
1720 WHEN OTHERS THEN
1721 FND_MESSAGE.PARSE_ENCODED(FND_MESSAGE.GET_ENCODED, l_app, l_message_name);
1722 IF l_message_name = 'IGS_PE_NO_NONE_SN' THEN
1723 p_error_code := 'E269';
1724 ELSIF l_message_name = 'IGS_EN_PRSN_NOTHAVE_DIABREC' THEN
1725 p_error_code := 'E270';
1726 ELSE
1727 p_error_code := 'E145';
1728 END IF;
1729
1730
1731 UPDATE igs_ad_disablty_int_all
1732 SET status ='3',
1733 ERROR_CODE = p_error_code
1734 WHERE interface_disablty_id = p_disability_rec.interface_disablty_id;
1735
1736 IF p_error_code = 'E145' THEN
1737 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
1738 IF (l_request_id IS NULL) THEN
1739 l_request_id := fnd_global.conc_request_id;
1740 END IF;
1741 l_label := 'igs.plsql.igs_ad_imp_011.create_disability.exception'||'E145';
1742 l_debug_str := 'Igs_Ad_Imp_011.Prc_Pe_Spl_Needs.Create_Disability '||'Unhandled Exception'
1743 ||' for INTERFACE DISABLTY ID :'|| p_disability_rec.interface_disablty_id|| ' Status : 3'||
1744 ' ErrorCode :'|| p_error_code||' SQLERRM :'|| SQLERRM;
1745 fnd_log.string_with_context( fnd_log.level_exception,
1746 l_label,
1747 l_debug_str, NULL,
1748 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1749 END IF;
1750 IF l_enable_log = 'Y' THEN
1751 igs_ad_imp_001.logerrormessage(p_disability_rec.interface_disablty_id,'E145','IGS_AD_DISABLTY_INT_ALL');
1752 END IF;
1753 ELSE
1754 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
1755 IF (l_request_id IS NULL) THEN
1756 l_request_id := fnd_global.conc_request_id;
1757 END IF;
1758 l_label := 'igs.plsql.igs_ad_imp_011.create_disability.exception'||p_error_code;
1759 l_debug_str := 'Igs_Ad_Imp_011.Prc_Pe_Spl_Needs.Create_Disability '||'Ovelapping records exist'
1760 ||' for INTERFACE DISABLTY ID :'|| p_disability_rec.interface_disablty_id|| ' Status : 3'
1761 || ' ErrorCode :'|| p_error_code|| SQLERRM;
1762 fnd_log.string_with_context( fnd_log.level_exception,
1763 l_label,
1764 l_debug_str, NULL,
1765 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1766 END IF;
1767 IF l_enable_log = 'Y' THEN
1768 igs_ad_imp_001.logerrormessage(p_disability_rec.interface_disablty_id,p_error_code,'IGS_AD_DISABLTY_INT_ALL');
1769 END IF;
1770 END IF;
1771
1772
1773 END create_disability;
1774
1775 PROCEDURE update_disability(p_disability_rec disability_cur%ROWTYPE,
1776 p_dup_chk_disability_rec dup_chk_disability_cur%ROWTYPE)
1777 AS
1778 /*
1779 || Created By : [email protected]
1780 || Created On : 2-JUN-2003
1781 || Purpose : This is a private procedure is for updating Person Disability Record.
1782 Enh Bug: 2986796.
1783 || Known limitations, enhancements or remarks :
1784 || Change History :
1785 || Who When What
1786 || (reverse chronological order - newest change first)
1787 */
1788 l_rowid VARCHAR2(25);
1789 l_success VARCHAR2(1);
1790 l_message_name VARCHAR2(30);
1791 l_app VARCHAR2(50);
1792 l_error_code VARCHAR2(30);
1793 BEGIN
1794 igs_pe_pers_disablty_pkg.update_row(
1795 x_rowid => p_dup_chk_disability_rec.rowid,
1796 x_igs_pe_pers_disablty_id => p_dup_chk_disability_rec.igs_pe_pers_disablty_id,
1797 x_person_id => p_dup_chk_disability_rec.person_id,
1798 x_disability_type => p_dup_chk_disability_rec.disability_type,
1799 x_contact_ind => p_dup_chk_disability_rec.contact_ind,
1800 x_special_allow_cd => NVL( p_disability_rec.special_allow_cd, p_dup_chk_disability_rec.special_allow_cd),
1801 x_support_level_cd => NVL( p_disability_rec.support_level_cd, p_dup_chk_disability_rec.support_level_cd),
1802 x_documented => p_dup_chk_disability_rec.documented,
1803 x_special_service_id => p_dup_chk_disability_rec.special_service_id,
1804 x_attribute_category => NVL(p_disability_rec.attribute_category,p_dup_chk_disability_rec.attribute_category),
1805 x_attribute1 => NVL(p_disability_rec.attribute1,p_dup_chk_disability_rec.attribute1),
1806 x_attribute2 => NVL(p_disability_rec.attribute2,p_dup_chk_disability_rec.attribute2),
1807 x_attribute3 => NVL(p_disability_rec.attribute3,p_dup_chk_disability_rec.attribute3),
1808 x_attribute4 => NVL(p_disability_rec.attribute4,p_dup_chk_disability_rec.attribute4),
1809 x_attribute5 => NVL(p_disability_rec.attribute5,p_dup_chk_disability_rec.attribute5),
1810 x_attribute6 => NVL(p_disability_rec.attribute6,p_dup_chk_disability_rec.attribute6),
1811 x_attribute7 => NVL(p_disability_rec.attribute7,p_dup_chk_disability_rec.attribute7),
1812 x_attribute8 => NVL(p_disability_rec.attribute8,p_dup_chk_disability_rec.attribute8),
1813 x_attribute9 => NVL(p_disability_rec.attribute9,p_dup_chk_disability_rec.attribute9),
1814 x_attribute10 => NVL(p_disability_rec.attribute10,p_dup_chk_disability_rec.attribute10),
1815 x_attribute11 => NVL(p_disability_rec.attribute11,p_dup_chk_disability_rec.attribute11),
1816 x_attribute12 => NVL(p_disability_rec.attribute12,p_dup_chk_disability_rec.attribute12),
1817 x_attribute13 => NVL(p_disability_rec.attribute13,p_dup_chk_disability_rec.attribute13),
1818 x_attribute14 => NVL(p_disability_rec.attribute14,p_dup_chk_disability_rec.attribute14),
1819 x_attribute15 => NVL(p_disability_rec.attribute15,p_dup_chk_disability_rec.attribute15),
1820 x_attribute16 => NVL(p_disability_rec.attribute16,p_dup_chk_disability_rec.attribute16),
1821 x_attribute17 => NVL(p_disability_rec.attribute17,p_dup_chk_disability_rec.attribute17),
1822 x_attribute18 => NVL(p_disability_rec.attribute18,p_dup_chk_disability_rec.attribute18),
1823 x_attribute19 => NVL(p_disability_rec.attribute19,p_dup_chk_disability_rec.attribute19),
1824 x_attribute20 => NVL(p_disability_rec.attribute20,p_dup_chk_disability_rec.attribute20),
1825 x_elig_early_reg_ind => NVL(p_disability_rec.elig_early_reg_ind,p_dup_chk_disability_rec.elig_early_reg_ind),
1826 x_start_date => NVL(p_disability_rec.start_date,p_dup_chk_disability_rec.start_date),
1827 x_end_date => NVL(p_disability_rec.end_date,p_dup_chk_disability_rec.end_date),
1828 x_info_source => NVL(p_disability_rec.info_source,p_dup_chk_disability_rec.info_source),
1829 x_interviewer_id => NVL(p_disability_rec.interviewer_id,p_dup_chk_disability_rec.interviewer_id),
1830 x_interviewer_date => NVL(p_disability_rec.interviewer_date,p_dup_chk_disability_rec.interviewer_date),
1831 x_mode => 'R'
1832 );
1833 l_error_code := NULL;
1834
1835 UPDATE igs_ad_disablty_int_all
1836 SET status =cst_stat_val_1,
1837 error_code = l_error_code,
1838 match_ind = cst_mi_val_18 -- '18' Match occured and used import values
1839 WHERE interface_disablty_id = p_disability_rec.interface_disablty_id;
1840
1841 EXCEPTION
1842 WHEN OTHERS THEN
1843 FND_MESSAGE.PARSE_ENCODED(FND_MESSAGE.GET_ENCODED, l_app, l_message_name);
1844 IF l_message_name = 'IGS_PE_NO_NONE_SN' THEN
1845 l_error_code := 'E269';
1846 ELSIF l_message_name = 'IGS_EN_PRSN_NOTHAVE_DIABREC' THEN
1847 l_error_code := 'E270';
1848 ELSE
1849 l_error_code := 'E146';
1850 END IF;
1851
1852 UPDATE igs_ad_disablty_int_all
1853 SET status ='3',
1854 error_code = l_error_code
1855 WHERE interface_disablty_id = p_disability_rec.interface_disablty_id;
1856
1857 IF l_error_code = 'E146' THEN
1858 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
1859
1860 IF (l_request_id IS NULL) THEN
1861 l_request_id := fnd_global.conc_request_id;
1862 END IF;
1863
1864 l_label := 'igs.plsql.igs_ad_imp_011.update_disability.exception'||l_error_code;
1865
1866 l_debug_str := 'Igs_Ad_Imp_011.Prc_Pe_Spl_Needs.update_disability '||'Unhandled Exception'
1867 ||' for INTERFACE DISABLTY ID :'
1868 || p_disability_rec.interface_disablty_id|| ' Status : 3'|| ' ErrorCode :' ||
1869 l_error_code||' SQLERRM: '|| SQLERRM;
1870
1871 fnd_log.string_with_context( fnd_log.level_exception,
1872 l_label,
1873 l_debug_str, NULL,
1874 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1875 END IF;
1876
1877 IF l_enable_log = 'Y' THEN
1878 igs_ad_imp_001.logerrormessage(p_disability_rec.interface_disablty_id,l_error_code,'IGS_AD_DISABLTY_INT_ALL');
1879 END IF;
1880
1881 ELSE
1882 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
1883
1884 IF (l_request_id IS NULL) THEN
1885 l_request_id := fnd_global.conc_request_id;
1886 END IF;
1887
1888 l_label := 'igs.plsql.igs_ad_imp_011.update_disability.exception'||l_error_code;
1889
1890 l_debug_str := 'Igs_Ad_Imp_011.Prc_Pe_Spl_Needs.update_disability '||
1891 ' for INTERFACE DISABLTY ID :'
1892 || p_disability_rec.interface_disablty_id|| ' Status : 3'||
1893 ' ErrorCode :' || l_error_code ||' SQLERRM:'|| SQLERRM;
1894
1895 fnd_log.string_with_context( fnd_log.level_exception,
1896 l_label,
1897 l_debug_str, NULL,
1898 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1899 END IF;
1900
1901 IF l_enable_log = 'Y' THEN
1902 igs_ad_imp_001.logerrormessage(p_disability_rec.interface_disablty_id,l_error_code,'IGS_AD_DISABLTY_INT_ALL');
1903 END IF;
1904
1905 END IF;
1906
1907 END update_disability;
1908
1909 --Local Procedure to create a Special Need Service Record
1910 PROCEDURE create_sn_service(p_sn_service_rec sn_service_cur%ROWTYPE,
1911 p_disability_id igs_pe_sn_service.disability_id%TYPE,
1912 p_person_id igs_ad_interface.person_id%TYPE,
1913 p_status OUT NOCOPY VARCHAR2)
1914 AS
1915 /*
1916 || Created By : [email protected]
1917 || Created On : 22-NOV-2001
1918 || Purpose : This is a private procedure is for creating Person Special Need Service Record.
1919 || DLD: Person Interface DLD. Enh Bug# 2103692.
1920 || Known limitations, enhancements or remarks :
1921 || Change History :
1922 || Who When What
1923 || kumma 21-OCT-2002 Added 2 more parameters for start date and end date
1924 || pkpatel 22-JUN-2001 Bug no.2466466
1925 || Added p_status
1926 || (reverse chronological order - newest change first)
1927 */
1928 l_rowid VARCHAR2(25);
1929 l_success VARCHAR2(1);
1930 l_error_code igs_pe_sn_srvce_int.error_code%TYPE;
1931 l_sn_service_id igs_pe_sn_service.sn_service_id%TYPE;
1932
1933 BEGIN
1934
1935 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
1936
1937 IF (l_request_id IS NULL) THEN
1938 l_request_id := fnd_global.conc_request_id;
1939 END IF;
1940
1941 l_label := 'igs.plsql.igs_ad_imp_011.create_sn_service.begin';
1942 l_debug_str := 'Interface sn service Id : ' || p_sn_service_rec.interface_sn_service_id;
1943
1944 fnd_log.string_with_context( fnd_log.level_procedure,
1945 l_label,
1946 l_debug_str, NULL,
1947 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1948 END IF;
1949
1950 validate_sn_service(p_sn_service_rec, p_person_id, l_success, l_error_code);
1951
1952 -- kumma, 2608360 Added two more parameters for start_dt and end_dt
1953
1954 IF l_success = 'Y' THEN -- Successful Validation
1955
1956 igs_pe_sn_service_pkg.insert_row (
1957 x_rowid => l_rowid ,
1958 x_sn_service_id => l_sn_service_id,
1959 x_disability_id => p_disability_id,
1960 x_special_service_cd => p_sn_service_rec.special_service_cd,
1961 x_documented_ind => p_sn_service_rec.documented_ind,
1962 x_start_dt => p_sn_service_rec.start_dt,
1963 x_end_dt => p_sn_service_rec.end_dt,
1964 x_mode => 'R'
1965 );
1966 l_error_code := NULL;
1967
1968 UPDATE igs_pe_sn_srvce_int
1969 SET status ='1',
1970 error_code = l_error_code
1971 WHERE interface_sn_service_id = p_sn_service_rec.interface_sn_service_id;
1972
1973 ELSE --'Validation is Unsuccessful.
1974 p_status := '3';
1975 END IF;
1976 EXCEPTION
1977 WHEN OTHERS THEN
1978 l_error_code := 'E151';
1979 p_status := '3';
1980
1981 UPDATE igs_pe_sn_srvce_int
1982 SET status ='3',
1983 error_code = l_error_code
1984 WHERE interface_sn_service_id = p_sn_service_rec.interface_sn_service_id;
1985
1986 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
1987
1988 IF (l_request_id IS NULL) THEN
1989 l_request_id := fnd_global.conc_request_id;
1990 END IF;
1991
1992 l_label := 'igs.plsql.igs_ad_imp_011.create_sn_service.exception'||l_error_code;
1993
1994 l_debug_str := 'Igs_Ad_Imp_011.Prc_Pe_Spl_Needs.Create_SN_Service '
1995 ||'Unhandled Exception in call to igs_pe_sn_service_pkg.insert_row'
1996 ||' for INTERFACE SN SERVICE ID :'
1997 || p_sn_service_rec.interface_sn_service_id
1998 || ' Status : 3'
1999 || ' ErrorCode :' || l_error_code
2000 ||' SQLERRM '|| SQLERRM ;
2001
2002 fnd_log.string_with_context( fnd_log.level_exception,
2003 l_label,
2004 l_debug_str, NULL,
2005 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
2006 END IF;
2007
2008 IF l_enable_log = 'Y' THEN
2009 igs_ad_imp_001.logerrormessage(p_sn_service_rec.interface_sn_service_id,l_error_code,'IGS_PE_SN_SRVCE_INT');
2010 END IF;
2011
2012 END create_sn_service;
2013
2014
2015 --Local Procedure to create a Special Need Contact Record
2016 PROCEDURE create_sn_contact(p_sn_contact_rec sn_contact_cur%ROWTYPE,
2017 p_disability_id igs_pe_sn_contact.disability_id%TYPE,
2018 p_person_id igs_ad_interface.person_id%TYPE,
2019 p_status OUT NOCOPY VARCHAR2)
2020 AS
2021 /*
2022 || Created By : [email protected]
2023 || Created On : 22-NOV-2001
2024 || Purpose : This is a private procedure is for creating Person Special Need Contact Record.
2025 || DLD: Person Interface DLD. Enh Bug# 2103692.
2026 || Known limitations, enhancements or remarks :
2027 || Change History :
2028 || Who When What
2029 || pkpatel 22-JUN-2001 Bug no.2466466
2030 || Added p_status
2031 || (reverse chronological order - newest change first)
2032 */
2033 l_rowid VARCHAR2(25);
2034 l_success VARCHAR2(1);
2035 l_error_code igs_pe_sn_conct_int.error_code%TYPE;
2036 l_sn_contact_id igs_pe_sn_contact.sn_contact_id%TYPE;
2037
2038 BEGIN
2039
2040 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
2041
2042 IF (l_request_id IS NULL) THEN
2043 l_request_id := fnd_global.conc_request_id;
2044 END IF;
2045
2046 l_label := 'igs.plsql.igs_ad_imp_011.create_sn_contact.begin';
2047 l_debug_str := 'Interface sn contact Id : ' || p_sn_contact_rec.interface_sn_contact_id;
2048
2049 fnd_log.string_with_context( fnd_log.level_procedure,
2050 l_label,
2051 l_debug_str, NULL,
2052 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
2053 END IF;
2054
2055
2056 validate_sn_contact(p_sn_contact_rec, p_person_id, l_success, l_error_code);
2057
2058 IF l_success = 'Y' THEN -- Successful Validation
2059
2060 igs_pe_sn_contact_pkg.insert_row (
2061 x_rowid => l_rowid ,
2062 x_sn_contact_id => l_sn_contact_id,
2063 x_disability_id => p_disability_id,
2064 x_contact_name => p_sn_contact_rec.contact_name,
2065 x_contact_date => TRUNC(p_sn_contact_rec.contact_date),
2066 x_comments => p_sn_contact_rec.comments,
2067 x_mode => 'R'
2068 );
2069 l_error_code := NULL;
2070
2071
2072 UPDATE igs_pe_sn_conct_int
2073 SET status ='1',
2074 error_code = l_error_code
2075 WHERE interface_sn_contact_id = p_sn_contact_rec.interface_sn_contact_id;
2076
2077 ELSE -- validation failed
2078 p_status := '3';
2079 END IF;
2080
2081 EXCEPTION
2082 WHEN OTHERS THEN
2083 l_error_code := 'E153';
2084 p_status := '3';
2085
2086 UPDATE igs_pe_sn_conct_int
2087 SET status ='3',
2088 error_code = l_error_code
2089 WHERE interface_sn_contact_id = p_sn_contact_rec.interface_sn_contact_id;
2090
2091 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
2092
2093 IF (l_request_id IS NULL) THEN
2094 l_request_id := fnd_global.conc_request_id;
2095 END IF;
2096
2097 l_label := 'igs.plsql.igs_ad_imp_011.create_sn_contact.exception'||l_error_code;
2098
2099 l_debug_str := 'Igs_Ad_Imp_011.Prc_Pe_Spl_Needs.Create_SN_Contact '
2100 ||'Unhandled Exception in call to igs_pe_sn_contact_pkg.insert_row'
2101 ||' for INTERFACE SN CONTACT ID :'
2102 || p_sn_contact_rec.interface_sn_contact_id
2103 || 'Status : 3'
2104 || 'ErrorCode :' || l_error_code
2105 ||' SQLERRM '|| SQLERRM;
2106
2107 fnd_log.string_with_context( fnd_log.level_exception,
2108 l_label,
2109 l_debug_str, NULL,
2110 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
2111 END IF;
2112
2113 IF l_enable_log = 'Y' THEN
2114 igs_ad_imp_001.logerrormessage(p_sn_contact_rec.interface_sn_contact_id,l_error_code,'IGS_PE_SN_CONCT_INT');
2115 END IF;
2116
2117 END create_sn_contact;
2118
2119
2120 PROCEDURE process_sn_service(p_interface_disability_id IN igs_ad_disablty_int.interface_disablty_id%TYPE,
2121 p_disability_id IN igs_pe_sn_service.disability_id%TYPE,
2122 p_person_id IN igs_ad_interface.person_id%TYPE,
2123 p_status OUT NOCOPY VARCHAR2)
2124 AS
2125 --------------------------------------------------------------------------
2126 -- Created By : pkpatel
2127 -- Date Created On : 19-NOV-2001
2128 -- Purpose:This is a private procedure for processing Person Special Need Service Records.
2129 -- DLD: Person Interface DLD. Enh Bug# 2103692.
2130 -- Know limitations, enhancements or remarks
2131 -- Change History
2132 -- Who When What
2133 -- kumma 21-OCT-2002 Passed a additional parameter for start date in dup_chk_sn_service_cur to check
2134 -- for duplicate record # 2608360
2135 -- pkpatel 22-JUN-2001 Bug no.2466466
2136 -- Added p_status
2137 -- (reverse chronological order - newest change first)
2138 --------------------------------------------------------------------------
2139 l_success VARCHAR2(1);
2140 l_error_code VARCHAR2(100);
2141
2142 BEGIN
2143
2144 -- Call Log header
2145 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
2146
2147 IF (l_request_id IS NULL) THEN
2148 l_request_id := fnd_global.conc_request_id;
2149 END IF;
2150
2151 l_label := 'igs.plsql.igs_ad_imp_011.prcess_sn_service.begin';
2152 l_debug_str := 'Interface sn service Id : ' || sn_service_rec.interface_sn_service_id;
2153
2154 fnd_log.string_with_context( fnd_log.level_procedure,
2155 l_label,
2156 l_debug_str, NULL,
2157 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
2158 END IF;
2159
2160 --1. If rule is E or I, then if the match_ind is not null, the combination is invalid
2161 IF l_rule IN ('E','I') THEN
2162 UPDATE igs_pe_sn_srvce_int
2163 SET status = cst_stat_val_3,
2164 ERROR_CODE = cst_err_val_695 -- Error code depicting incorrect combination
2165 WHERE match_ind IS NOT NULL
2166 AND status = cst_stat_val_2
2167 AND interface_run_id = l_interface_run_id;
2168 END IF;
2169
2170 --2. If rule is E and duplicate exists, update match_ind to 19 and status to 1
2171 IF l_rule = 'E' THEN
2172 UPDATE igs_pe_sn_srvce_int mi
2173 SET status = cst_stat_val_1,
2174 match_ind = cst_mi_val_19
2175 WHERE mi.interface_run_id = l_interface_run_id
2176 AND mi.status = cst_stat_val_2
2177 AND mi.interface_disablty_id = p_interface_disability_id
2178 AND EXISTS ( SELECT '1'
2179 FROM igs_pe_sn_service pe
2180 WHERE pe.disability_id = p_disability_id AND
2181 mi.special_service_cd = pe.special_service_cd AND
2182 NVL(TRUNC(mi.start_dt),l_default_date) = NVL(pe.start_dt,l_default_date)
2183 );
2184 END IF;
2185
2186 --3. If rule is R and there match_ind is 18,19,22 or 23 then the records must have been
2187 -- processed in prior runs and didn't get updated .. update to status 1
2188 IF l_rule = 'R' THEN
2189 UPDATE igs_pe_sn_srvce_int
2190 SET status = cst_stat_val_1
2191 WHERE interface_run_id = l_interface_run_id
2192 AND match_ind IN (cst_mi_val_18,cst_mi_val_19,cst_mi_val_22,cst_mi_val_23)
2193 AND status = cst_stat_val_2;
2194 END IF;
2195
2196 --4. If rule is R and match_ind is neither 21 nor 25 then error
2197 IF l_rule = 'R' THEN
2198 UPDATE igs_pe_sn_srvce_int
2199 SET status = cst_stat_val_3,
2200 ERROR_CODE = cst_err_val_695
2201 WHERE interface_run_id = l_interface_run_id
2202 AND status = cst_stat_val_2
2203 AND (match_ind IS NOT NULL AND match_ind NOT IN (cst_mi_val_21,cst_mi_val_25));
2204 END IF;
2205
2206 --5. If rule is R, set duplicated records with no discrepancy to status 1 and match_ind 23
2207 IF l_rule = 'R' THEN
2208 UPDATE igs_pe_sn_srvce_int mi
2209 SET status = cst_stat_val_1,
2210 match_ind = cst_mi_val_23
2211 WHERE mi.interface_run_id = l_interface_run_id
2212 AND mi.match_ind IS NULL
2213 AND mi.status = cst_stat_val_2
2214 AND mi.interface_disablty_id = p_interface_disability_id
2215 AND EXISTS ( SELECT '1'
2216 FROM igs_pe_sn_service pe
2217 WHERE pe.disability_id = p_disability_id AND
2218 mi.special_service_cd = pe.special_service_cd AND
2219 UPPER(mi.documented_ind) = UPPER(pe.documented_ind) AND
2220 (TRUNC(mi.start_dt) = TRUNC(pe.start_dt) OR (mi.start_dt IS NULL AND pe.start_dt IS NULL)) AND
2221 (TRUNC(mi.end_dt) = TRUNC(pe.end_dt) OR (mi.end_dt IS NULL AND pe.end_dt IS NULL)));
2222 END IF;
2223
2224
2225
2226 --6. If rule is R records still exist, they are duplicates and have discrepancy .. update status=3,match_ind=20
2227 IF l_rule = 'R' THEN
2228 UPDATE igs_pe_sn_srvce_int mi
2229 SET status = cst_stat_val_3,
2230 match_ind = cst_mi_val_20,
2231 dup_sn_service_id = (SELECT sn_service_id
2232 FROM igs_pe_sn_service pe
2233 WHERE pe.disability_id = p_disability_id AND
2234 mi.special_service_cd = pe.special_service_cd AND
2235 NVL(TRUNC(mi.start_dt),l_default_date) = NVL(TRUNC(pe.start_dt),l_default_date))
2236 WHERE mi.interface_run_id = l_interface_run_id
2237 AND mi.match_ind IS NULL
2238 AND mi.status = cst_stat_val_2
2239 AND mi.interface_disablty_id = p_interface_disability_id
2240 AND EXISTS (SELECT '1'
2241 FROM igs_pe_sn_service pe
2242 WHERE pe.disability_id = p_disability_id AND
2243 mi.special_service_cd = pe.special_service_cd AND
2244 NVL(TRUNC(mi.start_dt),l_default_date) = NVL(TRUNC(pe.start_dt),l_default_date));
2245 END IF;
2246 FOR sn_service_rec IN sn_service_cur(p_interface_disability_id,l_interface_run_id) LOOP
2247 -- For each record picked up do the following :
2248 -- Check to see if the record already exists.
2249 -- commented the following duplicate check code, #2608360, kumma
2250 sn_service_rec.special_service_cd := UPPER(sn_service_rec.special_service_cd);
2251 sn_service_rec.start_dt := TRUNC(sn_service_rec.start_dt);
2252 sn_service_rec.end_dt := TRUNC(sn_service_rec.end_dt);
2253 dup_chk_sn_service_rec.sn_service_id := NULL;
2254 OPEN dup_chk_sn_service_cur(p_disability_id,sn_service_rec.special_service_cd, sn_service_rec.start_dt);
2255 FETCH dup_chk_sn_service_cur INTO dup_chk_sn_service_rec;
2256 CLOSE dup_chk_sn_service_cur;
2257
2258 --If its a duplicate record find the source category rule for that Source Category.
2259 IF dup_chk_sn_service_rec.sn_service_id IS NOT NULL THEN
2260 dup_chk_sn_service_rec.start_dt := TRUNC(dup_chk_sn_service_rec.start_dt);
2261 dup_chk_sn_service_rec.end_dt := TRUNC(dup_chk_sn_service_rec.end_dt);
2262 IF l_rule = 'I' THEN
2263 BEGIN
2264 validate_sn_service(sn_service_rec, p_person_id, l_success, l_error_code);
2265 IF l_success = 'Y' THEN -- Successful Validation
2266 igs_pe_sn_service_pkg.update_row (
2267 x_rowid => dup_chk_sn_service_rec.ROWID,
2268 x_sn_service_id => dup_chk_sn_service_rec.sn_service_id,
2269 x_disability_id => dup_chk_sn_service_rec.disability_id,
2270 x_special_service_cd => sn_service_rec.special_service_cd,
2271 x_documented_ind => sn_service_rec.documented_ind,
2272 x_start_dt => NVL(sn_service_rec.start_dt,dup_chk_sn_service_rec.start_dt),
2273 x_end_dt => NVL(sn_service_rec.end_dt,dup_chk_sn_service_rec.end_dt),
2274 x_mode => 'R'
2275 );
2276 l_error_code := NULL;
2277 UPDATE igs_pe_sn_srvce_int
2278 SET status =cst_stat_val_1,
2279 error_code = l_error_code,
2280 match_ind = cst_mi_val_18 -- '18' Match occured and used import values
2281 WHERE interface_sn_service_id = sn_service_rec.interface_sn_service_id;
2282
2283 ELSE --Validation Failed.
2284 p_status := '3';
2285 END IF; -- End of condition check for successful validation
2286 EXCEPTION
2287 WHEN OTHERS THEN
2288 l_error_code := 'E152';
2289 p_status := '3';
2290
2291 UPDATE igs_pe_sn_srvce_int
2292 SET status ='3',
2293 error_code = l_error_code
2294 WHERE interface_sn_service_id = sn_service_rec.interface_sn_service_id;
2295
2296 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
2297
2298 IF (l_request_id IS NULL) THEN
2299 l_request_id := fnd_global.conc_request_id;
2300 END IF;
2301
2302 l_label := 'igs.plsql.igs_ad_imp_011.process_sn_service.exception'||l_error_code;
2303
2304 l_debug_str := 'Igs_Ad_Imp_011.Prc_Pe_Spl_Needs.Process_SN_Service '
2305 ||'Unhandled Exception in call to igs_pe_sn_service_pkg.update_row'
2306 ||' for INTERFACE SN SERVICE ID :'
2307 || sn_service_rec.interface_sn_service_id
2308 || ' Status : 3'
2309 || ' ErrorCode :' || l_error_code
2310 ||' SQLERRM '|| SQLERRM;
2311
2312 fnd_log.string_with_context( fnd_log.level_exception,
2313 l_label,
2314 l_debug_str, NULL,
2315 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
2316 END IF;
2317
2318 IF l_enable_log = 'Y' THEN
2319 igs_ad_imp_001.logerrormessage(sn_service_rec.interface_sn_service_id,l_error_code,'IGS_PE_SN_SRVCE_INT');
2320 END IF;
2321
2322 END;
2323 ELSIF l_rule = 'R' THEN
2324 IF sn_service_rec.match_ind = '21' THEN
2325 BEGIN
2326
2327 validate_sn_service(sn_service_rec, p_person_id, l_success, l_error_code);
2328
2329 IF l_success = 'Y' THEN -- Successful Validation
2330 igs_pe_sn_service_pkg.update_row (
2331 x_rowid => dup_chk_sn_service_rec.rowid ,
2332 x_sn_service_id => dup_chk_sn_service_rec.sn_service_id,
2333 x_disability_id => p_disability_id,
2334 x_special_service_cd => sn_service_rec.special_service_cd,
2335 x_documented_ind => sn_service_rec.documented_ind,
2336 x_start_dt => nvl(sn_service_rec.start_dt,dup_chk_sn_service_rec.start_dt),
2337 x_end_dt => nvl(sn_service_rec.end_dt,dup_chk_sn_service_rec.end_dt),
2338 x_mode => 'R'
2339 );
2340 l_error_code := NULL;
2341
2342 UPDATE igs_pe_sn_srvce_int
2343 SET status =cst_stat_val_1,
2344 error_code = l_error_code,
2345 match_ind = cst_mi_val_18 -- '18' Match occured and used import values
2346 WHERE interface_sn_service_id = sn_service_rec.interface_sn_service_id;
2347
2348 ELSE -- Validation Failed.
2349 p_status := '3';
2350 END IF; -- End of condition check for successful validation
2351 EXCEPTION
2352 WHEN OTHERS THEN
2353 l_error_code := 'E152';
2354 p_status := '3';
2355
2356 UPDATE igs_pe_sn_srvce_int
2357 SET status ='3',
2358 error_code = l_error_code
2359 WHERE interface_sn_service_id = sn_service_rec.interface_sn_service_id;
2360
2361 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
2362
2363 IF (l_request_id IS NULL) THEN
2364 l_request_id := fnd_global.conc_request_id;
2365 END IF;
2366
2367 l_label := 'igs.plsql.igs_ad_imp_011.process_sn_service.exception'||l_error_code;
2368
2369 l_debug_str := 'Igs_Ad_Imp_011.Prc_Pe_Spl_Needs.Process_SN_Service '
2370 ||'Unhandled Exception in call to igs_pe_sn_service_pkg.update_row'
2371 ||' for INTERFACE SN SERVICE ID :'
2372 || sn_service_rec.interface_sn_service_id
2373 || ' Status : 3'
2374 || ' ErrorCode :' || l_error_code
2375 ||' SQLERRM '|| SQLERRM ;
2376
2377 fnd_log.string_with_context( fnd_log.level_exception,
2378 l_label,
2379 l_debug_str, NULL,
2380 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
2381 END IF;
2382
2383 IF l_enable_log = 'Y' THEN
2384 igs_ad_imp_001.logerrormessage(sn_service_rec.interface_sn_service_id,l_error_code,'IGS_PE_SN_SRVCE_INT');
2385 END IF;
2386
2387 END;
2388 END IF; -- service_rec.MATCH_IND check
2389
2390 END IF;-- l_cat_rule check for 'I','R' or 'E'.
2391 ELSE -- Its not a duplicate record, So create a new record
2392 create_sn_service(sn_service_rec, p_disability_id, p_person_id,p_status);
2393 END IF;
2394 END LOOP;
2395 END process_sn_service;
2396
2397
2398
2399 PROCEDURE process_sn_contact(p_interface_disability_id IN igs_ad_disablty_int.interface_disablty_id%TYPE,
2400 p_disability_id IN igs_pe_sn_contact.disability_id%TYPE,
2401 p_person_id IN igs_ad_interface.person_id%TYPE,
2402 p_status OUT NOCOPY VARCHAR2)
2403 AS
2404 --------------------------------------------------------------------------
2405 -- Created By : pkpatel
2406 -- Date Created On : 19-NOV-2001
2407 -- Purpose:This is a private procedure for processing of Person Special Need Contact Records.
2408 -- DLD: Person Interface DLD. Enh Bug# 2103692.
2409 -- Know limitations, enhancements or remarks
2410 -- Change History
2411 -- Who When What
2412 -- pkpatel 22-JUN-2001 Bug no.2466466
2413 -- Added p_status
2414 -- (reverse chronological order - newest change first)
2415 --------------------------------------------------------------------------
2416
2417 l_success VARCHAR2(1);
2418 l_error_code VARCHAR2(100);
2419
2420 BEGIN
2421 -- Call Log header
2422 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
2423
2424 IF (l_request_id IS NULL) THEN
2425 l_request_id := fnd_global.conc_request_id;
2426 END IF;
2427
2428 l_label := 'igs.plsql.igs_ad_imp_011.process_sn_contact.begin';
2429 l_debug_str := 'Interface Disability Id : ' || p_interface_disability_id;
2430
2431 fnd_log.string_with_context( fnd_log.level_procedure,
2432 l_label,
2433 l_debug_str, NULL,
2434 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
2435 END IF;
2436
2437 --1. If rule is E or I, then if the match_ind is not null, the combination is invalid
2438 IF l_rule IN ('E','I') THEN
2439 UPDATE igs_pe_sn_conct_int
2440 SET status = cst_stat_val_3,
2441 ERROR_CODE = cst_err_val_695 -- Error code depicting incorrect combination
2442 WHERE match_ind IS NOT NULL
2443 AND status = cst_stat_val_2
2444 AND interface_run_id = l_interface_run_id;
2445 END IF;
2446
2447 --2. If rule is E and duplicate exists, update match_ind to 19 and status to 1
2448 IF l_rule = 'E' THEN
2449 UPDATE igs_pe_sn_conct_int mi
2450 SET status = cst_stat_val_1,
2451 match_ind = cst_mi_val_19
2452 WHERE mi.interface_run_id = l_interface_run_id
2453 AND mi.status = cst_stat_val_2
2454 AND mi.interface_disablty_id = p_interface_disability_id
2455 AND EXISTS ( SELECT '1'
2456 FROM igs_pe_sn_contact pe
2457 WHERE pe.disability_id = p_disability_id AND
2458 NVL(UPPER(mi.contact_name),'~') = NVL(UPPER(pe.contact_name),'~') AND
2459 NVL(TRUNC(mi.contact_date),l_default_date) = NVL(TRUNC(pe.contact_date),l_default_date)
2460 );
2461 END IF;
2462
2463 --3. If rule is R and there match_ind is 18,19,22 or 23 then the records must have been
2464 -- processed in prior runs and didn't get updated .. update to status 1
2465 IF l_rule = 'R' THEN
2466 UPDATE igs_pe_sn_conct_int
2467 SET status = cst_stat_val_1
2468 WHERE interface_run_id = l_interface_run_id
2469 AND match_ind IN (cst_mi_val_18,cst_mi_val_19,cst_mi_val_22,cst_mi_val_23)
2470 AND status = cst_stat_val_2;
2471 END IF;
2472
2473 --4. If rule is R and match_ind is neither 21 nor 25 then error
2474 IF l_rule = 'R' THEN
2475 UPDATE igs_pe_sn_conct_int
2476 SET status = cst_stat_val_3,
2477 ERROR_CODE = cst_err_val_695
2478 WHERE interface_run_id = l_interface_run_id
2479 AND status = cst_stat_val_2
2480 AND (match_ind IS NOT NULL AND match_ind NOT IN (cst_mi_val_21,cst_mi_val_25));
2481 END IF;
2482
2483 --5. If rule is R, set duplicated records with no discrepancy to status 1 and match_ind 23
2484 IF l_rule = 'R' THEN
2485 UPDATE igs_pe_sn_conct_int mi
2486 SET status = cst_stat_val_1,
2487 match_ind = cst_mi_val_23
2488 WHERE mi.interface_run_id = l_interface_run_id
2489 AND mi.match_ind IS NULL
2490 AND mi.status = cst_stat_val_2
2491 AND mi.interface_disablty_id = p_interface_disability_id
2492 AND EXISTS ( SELECT '1'
2493 FROM igs_pe_sn_contact pe
2494 WHERE pe.disability_id = p_disability_id AND
2495 NVL(UPPER(mi.contact_name),'*') = NVL(UPPER(pe.contact_name),'*') AND
2496 NVL(TRUNC(mi.contact_date), l_default_date) = NVL(TRUNC(pe.contact_date),l_default_date) AND
2497 NVL(UPPER(mi.comments),'*') = NVL(UPPER(pe.comments), '*'));
2498 END IF;
2499
2500 --6. If rule is R records still exist, they are duplicates and have discrepancy .. update status=3,match_ind=20
2501 IF l_rule = 'R' THEN
2502 UPDATE igs_pe_sn_conct_int mi
2503 SET status = cst_stat_val_3,
2504 match_ind = cst_mi_val_20,
2505 dup_sn_contact_id = (SELECT sn_contact_id
2506 FROM igs_pe_sn_contact pe
2507 WHERE pe.disability_id = p_disability_id AND
2508 NVL(UPPER(mi.contact_name),'*') = NVL(UPPER(pe.contact_name),'*') AND
2509 NVL(TRUNC(mi.contact_date), l_default_date) = NVL(TRUNC(pe.contact_date),l_default_date)
2510 )
2511 WHERE mi.interface_run_id = l_interface_run_id
2512 AND mi.match_ind IS NULL
2513 AND mi.status = cst_stat_val_2
2514 AND mi.interface_disablty_id = p_interface_disability_id
2515 AND EXISTS (SELECT '1'
2516 FROM igs_pe_sn_contact pe
2517 WHERE pe.disability_id = p_disability_id AND
2518 NVL(UPPER(mi.contact_name),'*') = NVL(UPPER(pe.contact_name),'*') AND
2519 NVL(TRUNC(mi.contact_date), l_default_date) = NVL(TRUNC(pe.contact_date),l_default_date));
2520 END IF;
2521
2522 FOR sn_contact_rec IN sn_contact_cur(p_interface_disability_id,l_interface_run_id) LOOP
2523
2524 -- For each record picked up do the following :
2525 -- Check to see if the record already exists.
2526 dup_chk_sn_contact_rec.sn_contact_id := NULL;
2527 OPEN dup_chk_sn_contact_cur(p_disability_id,sn_contact_rec.contact_name,sn_contact_rec.contact_date);
2528 FETCH dup_chk_sn_contact_cur INTO dup_chk_sn_contact_rec;
2529 CLOSE dup_chk_sn_contact_cur;
2530 --If its a duplicate record find the source category rule for that Source Category.
2531 IF dup_chk_sn_contact_rec.sn_contact_id IS NOT NULL THEN
2532 dup_chk_sn_contact_rec.contact_date := TRUNC(dup_chk_sn_contact_rec.contact_date);
2533 IF l_rule = 'I' THEN
2534 BEGIN
2535
2536 validate_sn_contact(sn_contact_rec, p_person_id, l_success, l_error_code);
2537 IF l_success = 'Y' THEN -- Successful Validation
2538 igs_pe_sn_contact_pkg.update_row (
2539 x_rowid => dup_chk_sn_contact_rec.rowid ,
2540 x_sn_contact_id => dup_chk_sn_contact_rec.sn_contact_id,
2541 x_disability_id => dup_chk_sn_contact_rec.disability_id,
2542 x_contact_name => NVL(sn_contact_rec.contact_name,dup_chk_sn_contact_rec.contact_name),
2543 x_contact_date => NVL(sn_contact_rec.contact_date,dup_chk_sn_contact_rec.contact_date),
2544 x_comments => NVL(sn_contact_rec.comments, dup_chk_sn_contact_rec.comments),
2545 x_mode => 'R'
2546 );
2547 l_error_code := NULL;
2548 UPDATE igs_pe_sn_conct_int
2549 SET status =cst_stat_val_1,
2550 error_code = l_error_code,
2551 match_ind = cst_mi_val_18 -- '18' Match occured and used import values
2552 WHERE interface_sn_contact_id = sn_contact_rec.interface_sn_contact_id;
2553 ELSE
2554 p_status := '3';
2555 END IF;
2556
2557 EXCEPTION
2558 WHEN OTHERS THEN
2559 l_error_code := 'E154';
2560 p_status := '3';
2561
2562 UPDATE igs_pe_sn_conct_int
2563 SET status ='3',
2564 error_code = l_error_code
2565 WHERE interface_sn_contact_id = sn_contact_rec.interface_sn_contact_id;
2566
2567 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
2568
2569 IF (l_request_id IS NULL) THEN
2570 l_request_id := fnd_global.conc_request_id;
2571 END IF;
2572
2573 l_label := 'igs.plsql.igs_ad_imp_011.process_sn_contact.exception'||l_error_code;
2574
2575 l_debug_str := 'Igs_Ad_Imp_011.Prc_Pe_Spl_Needs.Process_SN_Contact '
2576 ||'Unhandled Exception in call to igs_pe_sn_contact_pkg.update_row'
2577 ||' for INTERFACE SN CONTACT ID :'
2578 || sn_contact_rec.interface_sn_contact_id
2579 || ' Status : 3'
2580 || ' ErrorCode :' || l_error_code
2581 ||' SQLERRM '|| SQLERRM ;
2582
2583 fnd_log.string_with_context( fnd_log.level_exception,
2584 l_label,
2585 l_debug_str, NULL,
2586 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
2587 END IF;
2588
2589 IF l_enable_log = 'Y' THEN
2590 igs_ad_imp_001.logerrormessage(sn_contact_rec.interface_sn_contact_id,l_error_code,'IGS_PE_SN_CONCT_INT');
2591 END IF;
2592
2593
2594 END;
2595 ELSIF l_rule = 'R' THEN
2596 IF sn_contact_rec.match_ind = '21' THEN
2597 BEGIN
2598
2599 validate_sn_contact(sn_contact_rec, p_person_id, l_success, l_error_code);
2600
2601 IF l_success = 'Y' THEN -- Successful Validation
2602
2603 igs_pe_sn_contact_pkg.update_row (
2604 x_rowid => dup_chk_sn_contact_rec.rowid ,
2605 x_sn_contact_id => dup_chk_sn_contact_rec.sn_contact_id,
2606 x_disability_id => dup_chk_sn_contact_rec.disability_id,
2607 x_contact_name => NVL(sn_contact_rec.contact_name,dup_chk_sn_contact_rec.contact_name),
2608 x_contact_date => NVL(sn_contact_rec.contact_date,dup_chk_sn_contact_rec.contact_date),
2609 x_comments => NVL(sn_contact_rec.comments, dup_chk_sn_contact_rec.comments),
2610 x_mode => 'R'
2611 );
2612 l_error_code := NULL;
2613
2614 UPDATE igs_pe_sn_conct_int
2615 SET status =cst_stat_val_1,
2616 error_code = l_error_code,
2617 match_ind = cst_mi_val_18 -- '18' Match occured and used import values
2618 WHERE interface_sn_contact_id = sn_contact_rec.interface_sn_contact_id;
2619 ELSE
2620 p_status := '3';
2621 END IF;
2622
2623 EXCEPTION
2624 WHEN OTHERS THEN
2625 l_error_code := 'E154';
2626 p_status := '3';
2627
2628 UPDATE igs_pe_sn_conct_int
2629 SET status ='3',
2630 error_code = l_error_code
2631 WHERE interface_sn_contact_id = sn_contact_rec.interface_sn_contact_id;
2632
2633 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
2634
2635 IF (l_request_id IS NULL) THEN
2636 l_request_id := fnd_global.conc_request_id;
2637 END IF;
2638
2639 l_label := 'igs.plsql.igs_ad_imp_011.process_sn_contact.exception'||l_error_code;
2640
2641 l_debug_str := 'Igs_Ad_Imp_011.Prc_Pe_Spl_Needs.Process_SN_Contact '
2642 ||'Unhandled Exception in call to igs_pe_sn_contact_pkg.update_row'
2643 ||' for INTERFACE SN CONTACT ID :'
2644 || sn_contact_rec.interface_sn_contact_id
2645 || ' Status : 3'
2646 || ' ErrorCode :' || l_error_code
2647 ||' SQLERRM '|| SQLERRM ;
2648
2649 fnd_log.string_with_context( fnd_log.level_exception,
2650 l_label,
2651 l_debug_str, NULL,
2652 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
2653 END IF;
2654
2655 IF l_enable_log = 'Y' THEN
2656 igs_ad_imp_001.logerrormessage(sn_contact_rec.interface_sn_contact_id,l_error_code,'IGS_PE_SN_CONCT_INT');
2657 END IF;
2658
2659
2660 END;
2661 END IF; -- discrepancy_sn_contact_rec.MATCH_IND check
2662
2663 END IF;-- l_cat_rule check for 'I','R' or 'E'.
2664 ELSE -- Its not a duplicate record, So create a new record
2665
2666 create_sn_contact(sn_contact_rec, p_disability_id ,p_person_id,p_status);
2667
2668 END IF;
2669
2670 END LOOP;
2671 END process_sn_contact;
2672
2673 --Private Procedure for the Processing of Person Disability Records
2674 PROCEDURE process_disability
2675 AS
2676 --------------------------------------------------------------------------
2677 -- Created By : pkpatel
2678 -- Date Created On : 19-NOV-2001
2679 -- Purpose:This is a private procedure is for processing Person Disability Records.
2680 -- DLD: Person Interface DLD. Enh Bug# 2103692.
2681 -- Know limitations, enhancements or remarks
2682 -- Change History
2683 -- Who When What
2684 -- (reverse chronological order - newest change first)
2685 -- npalanis 16-JUN-2002 Bug -2327077
2686 -- The child records special needs service and contact records
2687 -- are errored out if the disability type is NONE
2688 -- gmaheswa 21-Sep-2006 Modified Update statement in 5. If rule is R, set duplicated records
2689 -- with no discrepancy to status 1 and match_ind 23 case to reduce shared memory.
2690 --------------------------------------------------------------------------
2691
2692
2693 l_person_id IGS_AD_INTERFACE.PERSON_ID%TYPE;
2694 l_var VARCHAR2(2);
2695 l_success VARCHAR2(1);
2696 l_error_code VARCHAR2(100);
2697 l_contact_status VARCHAR2(1);
2698 l_service_status VARCHAR2(1);
2699
2700 BEGIN
2701 -- Call Log header
2702 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
2703
2704 IF (l_request_id IS NULL) THEN
2705 l_request_id := fnd_global.conc_request_id;
2706 END IF;
2707
2708 l_label := 'igs.plsql.igs_ad_imp_011.process_disability.begin';
2709 l_debug_str := 'igs_ad_imp_011.process_disability begin';
2710
2711 fnd_log.string_with_context( fnd_log.level_procedure,
2712 l_label,
2713 l_debug_str, NULL,
2714 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
2715 END IF;
2716
2717 l_rule := igs_ad_imp_001.find_source_cat_rule(
2718 p_source_type_id => p_source_type_id,
2719 p_category => 'PERSON_SPECIAL_NEEDS');
2720
2721
2722
2723 --1. If rule is E or I, then if the match_ind is not null, the combination is invalid
2724 IF l_rule IN ('E','I') THEN
2725 UPDATE igs_ad_disablty_int_all
2726 SET status = cst_stat_val_3,
2727 ERROR_CODE = cst_err_val_695 -- Error code depicting incorrect combination
2728 WHERE match_ind IS NOT NULL
2729 AND status = cst_stat_val_2
2730 AND interface_run_id = l_interface_run_id;
2731 END IF;
2732
2733 --2. If rule is E and duplicate exists, update match_ind to 19 and status to 1
2734 IF l_rule = 'E' THEN
2735 --skpandey, Bug#3702774, Changed select statement for optimization
2736 UPDATE igs_ad_disablty_int_all mi
2737 SET status = cst_stat_val_1,
2738 match_ind = cst_mi_val_19,
2739 dup_disability_id = ( SELECT pe.igs_pe_pers_disablty_id
2740 FROM igs_pe_pers_disablty pe, igs_ad_interface_all ii
2741 WHERE ii.interface_id = mi.interface_id AND
2742 pe.disability_type = UPPER(mi.disability_type) AND
2743 ROWNUM = 1 AND
2744 ii.person_id = pe.person_id AND
2745 ((TRUNC(mi.start_date) = pe.start_date) OR (mi.start_date IS NULL AND pe.start_date IS NULL)))
2746 WHERE mi.interface_run_id = l_interface_run_id
2747 AND mi.status = cst_stat_val_2
2748 AND EXISTS ( SELECT '1'
2749 FROM igs_pe_pers_disablty pe, igs_ad_interface_all ii
2750 WHERE pe.disability_type = UPPER(mi.disability_type) AND
2751 ii.interface_id = mi.interface_id AND
2752 ii.person_id = pe.person_id AND
2753 ((TRUNC(mi.start_date) = pe.start_date) OR (mi.start_date IS NULL AND pe.start_date IS NULL))
2754 );
2755 END IF;
2756
2757 --3. If rule is R and there match_ind is 18,19,22 or 23 then the records must have been
2758 -- processed in prior runs and didn't get updated .. update to status 1
2759 IF l_rule = 'R' THEN
2760 UPDATE igs_ad_disablty_int_all
2761 SET status = cst_stat_val_1
2762 WHERE interface_run_id = l_interface_run_id
2763 AND match_ind IN (cst_mi_val_18,cst_mi_val_19,cst_mi_val_22,cst_mi_val_23)
2764 AND status = cst_stat_val_2;
2765 END IF;
2766
2767 --4. If rule is R and match_ind is neither 21 nor 25 then error
2768 IF l_rule = 'R' THEN
2769 UPDATE igs_ad_disablty_int_all
2770 SET status = cst_stat_val_3,
2771 ERROR_CODE = cst_err_val_695
2772 WHERE interface_run_id = l_interface_run_id
2773 AND status = cst_stat_val_2
2774 AND (match_ind IS NOT NULL AND match_ind NOT IN (cst_mi_val_21,cst_mi_val_25));
2775 END IF;
2776
2777 --5. If rule is R, set duplicated records with no discrepancy to status 1 and match_ind 23
2778 IF l_rule = 'R' THEN
2779 --skpandey, Bug#3702774, Changed select statement for optimization
2780 UPDATE igs_ad_disablty_int_all mi
2781 SET status = cst_stat_val_1,
2782 match_ind = cst_mi_val_23,
2783 dup_disability_id = ( SELECT pe.igs_pe_pers_disablty_id
2784 FROM igs_pe_pers_disablty pe, igs_ad_interface_all ii
2785 WHERE ii.person_id = pe.person_id
2786 AND ii.interface_id = mi.interface_id
2787 AND pe.disability_type = UPPER(mi.disability_type)
2788 AND ((TRUNC(mi.start_date) = pe.start_date) OR (mi.start_date IS NULL AND pe.start_date IS NULL)))
2789 WHERE mi.interface_run_id = l_interface_run_id
2790 AND mi.match_ind IS NULL
2791 AND mi.status = cst_stat_val_2
2792 AND EXISTS ( SELECT 1
2793 FROM igs_pe_pers_disablty pe, igs_AD_interface_all ii
2794 WHERE ii.person_id = pe.person_id
2795 AND ii.interface_id = mi.interface_id
2796 AND UPPER(mi.disability_type) = pe.disability_type
2797 AND NVL(mi.special_allow_cd, -99) = NVL(pe.special_allow_cd, -99) AND
2798 NVL(pe.support_level_cd, -99) = NVL(mi.support_level_cd, -99) AND
2799 NVL(UPPER(pe.elig_early_reg_ind),'N') = NVL(UPPER(mi.elig_early_reg_ind),'N') AND
2800 pe.start_date = TRUNC(mi.start_date) AND
2801 NVL(pe.end_date,l_default_date) = NVL(TRUNC(mi.end_date),l_default_date) AND
2802 NVL(UPPER(pe.info_source),'*') = NVL(UPPER(mi.info_source),'*') AND
2803 NVL(pe.interviewer_id, -99) = NVL(mi.interviewer_id, -99) AND
2804 NVL(TRUNC(pe.interviewer_date), l_default_date) = NVL(TRUNC(mi.interviewer_date), l_default_date)
2805 AND NVL(pe.attribute_category, '*') = NVL(mi.attribute_category, '*')
2806 AND (pe.attribute1||'*'||pe.attribute2||'*'||pe.attribute3||'*'||pe.attribute4||'*'||pe.attribute5||'*'||
2807 pe.attribute6||'*'||pe.attribute7||'*'||pe.attribute8||'*'||pe.attribute9||'*'||pe.attribute10||'*'||pe.attribute11||'*'||
2808 pe.attribute12||'*'||pe.attribute13||'*'||pe.attribute14||'*'||pe.attribute15||'*'||pe.attribute16||'*'||pe.attribute17||'*'||
2809 pe.attribute18||'*'||pe.attribute19||'*'||pe.attribute20||'*') = (mi.attribute1||'*'
2810 ||mi.attribute2||'*'||mi.attribute3||'*'||mi.attribute4||'*'||mi.attribute5||'*'||mi.attribute6||'*'||
2811 mi.attribute7||'*'||mi.attribute8||'*'||mi.attribute9||'*'||mi.attribute10||'*'||mi.attribute11||'*'||
2812 mi.attribute12||'*'||mi.attribute13||'*'||mi.attribute14||'*'||mi.attribute15||'*'||mi.attribute16||
2813 '*'||mi.attribute17||'*'||mi.attribute18||'*'||mi.attribute19||'*'||mi.attribute20||'*'));
2814 END IF;
2815
2816 --6. If rule is R records still exist, they are duplicates and have discrepancy .. update status=3,match_ind=20
2817 IF l_rule = 'R' THEN
2818 --skpandey, Bug#3702774, Changed select statement for optimization
2819 UPDATE igs_ad_disablty_int_all mi
2820 SET status = cst_stat_val_3,
2821 match_ind = cst_mi_val_20
2822 WHERE mi.interface_run_id = l_interface_run_id
2823 AND mi.match_ind IS NULL
2824 AND mi.status = cst_stat_val_2
2825 AND EXISTS (SELECT '1'
2826 FROM igs_pe_pers_disablty pe, igs_Ad_interface_all ii
2827 WHERE pe.disability_type = UPPER(mi.disability_type) AND
2828 ii.person_id = pe.person_id AND
2829 ii.interface_id = mi.interface_id AND
2830 ((TRUNC(mi.start_date) = pe.start_date) OR (mi.start_date IS NULL AND pe.start_date IS NULL))
2831 );
2832
2833 END IF;
2834
2835 FOR disability_rec IN disability_cur(l_interface_run_id) LOOP
2836 l_processed_records := l_processed_records + 1 ;
2837
2838 BEGIN
2839 disability_rec.disability_type := UPPER(disability_rec.disability_type);
2840 disability_rec.special_allow_cd := UPPER(disability_rec.special_allow_cd);
2841 disability_rec.support_level_cd := UPPER(disability_rec.support_level_cd);
2842 disability_rec.start_date := TRUNC(disability_rec.start_date);
2843 disability_rec.end_date := TRUNC(disability_rec.end_date);
2844
2845 l_error_code := NULL;
2846 l_disability_id := NULL;
2847
2848 -- For each record picked up do the following :
2849 -- Check to see if the record already exists.
2850 dup_chk_disability_rec.igs_pe_pers_disablty_id := NULL;
2851
2852 OPEN dup_chk_disability_cur(disability_rec);
2853 FETCH dup_chk_disability_cur INTO dup_chk_disability_rec;
2854 CLOSE dup_chk_disability_cur;
2855 --If its a duplicate record find the source category rule for that Source Category.
2856 IF dup_chk_disability_rec.igs_pe_pers_disablty_id IS NOT NULL THEN
2857 dup_chk_disability_rec.start_date := TRUNC(dup_chk_disability_rec.start_date);
2858 dup_chk_disability_rec.end_date := TRUNC(dup_chk_disability_rec.end_date);
2859
2860 -- Assign the value to the variable l_disability_id which will be passed as a parameter to the processing
2861 -- of the Child Records i.e. for the processing of Special Need Service and Contact
2862 l_disability_id := dup_chk_disability_rec.igs_pe_pers_disablty_id ;
2863 IF l_rule = 'I' THEN
2864 validate_disability(disability_rec, l_success, l_error_code);
2865 IF l_success = 'Y' THEN -- Successful Validation
2866 update_disability(disability_rec,dup_chk_disability_rec);
2867 END IF; -- End of condition check for successful validation
2868
2869 ELSIF l_rule = 'R' THEN
2870 IF disability_rec.match_ind = '21' THEN
2871
2872 validate_disability(disability_rec, l_success, l_error_code);
2873
2874 IF l_success = 'Y' THEN -- Successful Validation
2875
2876 update_disability(disability_rec,dup_chk_disability_rec);
2877
2878 END IF; -- End of condition check for successful validation
2879
2880 END IF; -- discrepancy_disability_rec.MATCH_IND check
2881 END IF;-- l_cat_rule check for 'I','R' or 'E'.
2882
2883 ELSE -- Its not a duplicate record, So create a new record
2884
2885 create_disability(disability_rec,l_error_code);
2886
2887 END IF;
2888 /*****************************************************/
2889 IF l_disability_id IS NOT NULL AND l_error_code IS NULL THEN -- If the disability ID is NOT NULL proceed with the processing of Children
2890 l_var := NULL;
2891 OPEN check_none_disablity_cur(disability_rec.disability_type,'NONE');
2892 FETCH check_none_disablity_cur INTO l_var;
2893 CLOSE check_none_disablity_cur;
2894 IF l_var = 'X' THEN
2895 UPDATE igs_pe_sn_srvce_int
2896 SET status ='3',
2897 error_code = 'E271'
2898 WHERE INTERFACE_DISABLTY_ID = disability_rec.interface_disablty_id;
2899
2900 UPDATE igs_pe_sn_conct_int
2901 SET status ='3',
2902 error_code = 'E272'
2903 WHERE INTERFACE_DISABLTY_ID = disability_rec.interface_disablty_id;
2904
2905 UPDATE igs_ad_disablty_int_all
2906 SET status = '4',
2907 error_code = 'E347'
2908 WHERE interface_disablty_id = disability_rec.interface_disablty_id AND
2909 (EXISTS (SELECT 1 FROM igs_pe_sn_conct_int WHERE interface_disablty_id = disability_rec.interface_disablty_id AND status = '3')
2910 OR EXISTS (SELECT 1 FROM igs_pe_sn_srvce_int WHERE interface_disablty_id = disability_rec.interface_disablty_id AND status = '3'));
2911
2912 IF ((l_enable_log = 'Y') and (SQL%FOUND)) THEN
2913 fnd_message.set_name('IGS','IGS_EN_CONIND_NOTSET_NONE');
2914 fnd_file.put_line(fnd_file.LOG,fnd_message.get);
2915 igs_ad_imp_001.logerrormessage(disability_rec.interface_disablty_id,'E347','IGS_AD_DISABLTY_INT_ALL');
2916 END IF;
2917
2918 ELSE
2919 process_sn_service(disability_rec.interface_disablty_id, l_disability_id, disability_rec.person_id,l_contact_status);
2920 process_sn_contact(disability_rec.interface_disablty_id, l_disability_id, disability_rec.person_id,l_service_status);
2921 IF l_contact_status = '3' AND l_service_status = '3' THEN
2922 UPDATE igs_ad_disablty_int_all
2923 SET status = '4',
2924 error_code = 'E155'
2925 WHERE interface_disablty_id = disability_rec.interface_disablty_id;
2926
2927 IF l_enable_log = 'Y' THEN
2928 igs_ad_imp_001.logerrormessage(disability_rec.interface_disablty_id,'E155','IGS_AD_DISABLTY_INT_ALL');
2929 END IF;
2930
2931 ELSIF l_contact_status = '3' THEN
2932
2933 UPDATE igs_ad_disablty_int_all
2934 SET status = '4',
2935 error_code = 'E148'
2936 WHERE interface_disablty_id = disability_rec.interface_disablty_id;
2937
2938 IF l_enable_log = 'Y' THEN
2939 igs_ad_imp_001.logerrormessage(disability_rec.interface_disablty_id,'E148','IGS_AD_DISABLTY_INT_ALL');
2940 END IF;
2941
2942 ELSIF l_service_status = '3' THEN
2943
2944 UPDATE igs_ad_disablty_int_all
2945 SET status = '4',
2946 error_code = 'E147'
2947 WHERE interface_disablty_id = disability_rec.interface_disablty_id;
2948
2949 IF l_enable_log = 'Y' THEN
2950 igs_ad_imp_001.logerrormessage(disability_rec.interface_disablty_id,'E147','IGS_AD_DISABLTY_INT_ALL');
2951 END IF;
2952
2953 END IF;
2954 END IF;
2955 END IF;
2956 END;
2957 IF l_processed_records = 100 THEN
2958 COMMIT;
2959 l_processed_records := 0 ;
2960 END IF;
2961 END LOOP;
2962 l_processed_records := 0;
2963 -- To call the child processing for records updated to status 1 before the loop
2964 FOR sp_disability_rec IN sp_disability_cur(l_interface_run_id) LOOP
2965 l_processed_records := l_processed_records + 1 ;
2966
2967 BEGIN
2968
2969 l_disability_id := sp_disability_rec.dup_disability_id;
2970
2971 l_var := NULL;
2972 OPEN check_none_disablity_cur(sp_disability_rec.disability_type,'NONE');
2973 FETCH check_none_disablity_cur INTO l_var;
2974 CLOSE check_none_disablity_cur;
2975 IF l_var = 'X' THEN
2976 UPDATE igs_pe_sn_srvce_int
2977 SET status ='3',
2978 error_code = 'E271'
2979 WHERE INTERFACE_DISABLTY_ID = sp_disability_rec.interface_disablty_id;
2980
2981 UPDATE igs_pe_sn_conct_int
2982 SET status ='3',
2983 error_code = 'E272'
2984 WHERE INTERFACE_DISABLTY_ID = sp_disability_rec.interface_disablty_id;
2985
2986 UPDATE igs_ad_disablty_int_all
2987 SET status = '4',
2988 error_code = 'E347'
2989 WHERE interface_disablty_id = sp_disability_rec.interface_disablty_id AND
2990 (
2991 EXISTS(SELECT 1 FROM igs_pe_sn_conct_int WHERE
2992 interface_disablty_id = sp_disability_rec.interface_disablty_id AND status = '3')
2993 OR EXISTS (SELECT 1 FROM igs_pe_sn_srvce_int WHERE
2994 interface_disablty_id = sp_disability_rec.interface_disablty_id AND status = '3')
2995 );
2996
2997 IF ((l_enable_log = 'Y') and (SQL%FOUND)) THEN
2998 fnd_message.set_name('IGS','IGS_EN_CONIND_NOTSET_NONE');
2999 fnd_file.put_line(fnd_file.LOG,fnd_message.get);
3000 igs_ad_imp_001.logerrormessage(sp_disability_rec.interface_disablty_id,'E347','IGS_AD_DISABLTY_INT_ALL');
3001 END IF;
3002
3003 ELSE
3004 process_sn_service(sp_disability_rec.interface_disablty_id, l_disability_id, sp_disability_rec.person_id,l_contact_status);
3005 process_sn_contact(sp_disability_rec.interface_disablty_id, l_disability_id, sp_disability_rec.person_id,l_service_status);
3006 IF l_contact_status = '3' AND l_service_status = '3' THEN
3007 UPDATE igs_ad_disablty_int_all
3008 SET status = '4',
3009 error_code = 'E155'
3010 WHERE interface_disablty_id = sp_disability_rec.interface_disablty_id;
3011
3012 IF l_enable_log = 'Y' THEN
3013 igs_ad_imp_001.logerrormessage(sp_disability_rec.interface_disablty_id,'E155','IGS_AD_DISABLTY_INT_ALL');
3014 END IF;
3015
3016 ELSIF l_contact_status = '3' THEN
3017
3018 UPDATE igs_ad_disablty_int_all
3019 SET status = '4',
3020 error_code = 'E148'
3021 WHERE interface_disablty_id = sp_disability_rec.interface_disablty_id;
3022 IF l_enable_log = 'Y' THEN
3023 igs_ad_imp_001.logerrormessage(sp_disability_rec.interface_disablty_id,'E148','IGS_AD_DISABLTY_INT_ALL');
3024 END IF;
3025
3026 ELSIF l_service_status = '3' THEN
3027
3028 UPDATE igs_ad_disablty_int_all
3029 SET status = '4',
3030 error_code = 'E147'
3031 WHERE interface_disablty_id = sp_disability_rec.interface_disablty_id;
3032 IF l_enable_log = 'Y' THEN
3033 igs_ad_imp_001.logerrormessage(sp_disability_rec.interface_disablty_id,'E147','IGS_AD_DISABLTY_INT_ALL');
3034 END IF;
3035 END IF;
3036 END IF;
3037 END;
3038 IF l_processed_records = 100 THEN
3039 COMMIT;
3040 l_processed_records := 0 ;
3041 END IF;
3042 END LOOP;
3043 END process_disability;
3044
3045 --Start of the Main Processing
3046 BEGIN
3047 l_interface_run_id := igs_ad_imp_001.g_interface_run_id;
3048 l_enable_log := igs_ad_imp_001.g_enable_log;
3049 l_prog_label := 'igs.plsql.igs_ad_imp_011.prc_special_needs';
3050 l_label := 'igs.plsql.igs_ad_imp_011.prc_special_needs.';
3051
3052 process_disability;
3053 END prc_special_needs;
3054
3055 END IGS_AD_IMP_011;
[Home] [Help]