1 PACKAGE BODY IGS_AD_IMP_025 AS
2 /* $Header: IGSADB6B.pls 120.2 2005/07/15 06:32:06 appldev ship $ */
3 /*
4 || Created By : pkpatel
5 || Created On : 12-NOV-2001
6 || Purpose :
7 || Known limitations, enhancements or remarks :
8 || Change History :
9 || Who When What
10 ||
11 || ssaleem 13_OCT_2003 Bug : 3130316
12 || Logging is modified to include logging mechanism
13 || npalanis 6-JAN-2003 Bug : 2734697
14 || code added to commit after import of every
15 || 100 records .New variable l_processed_records added
16 || sarakshi 12-Nov-2001 Added procedure prc_pe_felony_dtls,prc_pe_hearing_dtls,prc_pe_disciplinary_dtls
17 || kumma 21-OCT-2002 Added one more parameter for disp_action_info to the Igs_Pe_Felony_Dtls_Pkg.insert_row
18 || and update_row in PROCEDURE crt_pe_felony_dtls , #2608360
19 || npalanis 30_OCT-2002 Bug : 2608360
20 || Trunc function added to crime date and nvl added for
21 || disp_action_info in prc_pe_felony_dtls.
22 || gmaheswa 1-Nov-2004 Bug : 3770362 removed code related to the effective dates(start_date , end_date)of housing status as they are obsoleted
23 || pkpatel 29-Nov-204 Bug : 3770362 In the Load Cal validation of Housing status modified to TRUNC of sysdate
24 || (reverse chronological order - newest change first)
25 || skpandey 08-JUL-2005 Bug : 4327807
26 || Added a condition in exception section of crt_pe_felony_dtls after calling igs_pe_felony_dtls_pkg.update_row
27 || and igs_pe_felony_dtls_pkg.insert_row to set status and error code
28 */
29 --
30 -- Starts procedure PRC_PE_HOUSE_STATUS
31 --
32 l_interface_run_id NUMBER;
33 l_var VARCHAR2(1000);
34
35 PROCEDURE prc_pe_house_status
36 (
37 P_SOURCE_TYPE_ID IN NUMBER,
38 P_BATCH_ID IN NUMBER )
39 AS
40 /*
41 || Created By : [email protected]
42 || Created On : 06-Jul-2001
43 || Purpose : This procedure is for importing person Houseing Status Information.
44 || DLD: Person Interface DLD. Enh Bug# 2103692.
45 || Known limitations, enhancements or remarks :
46 || Change History :
47 || Who When What
48 || (reverse chronological order - newest change first)
49 || npalanis 6-JAN-2003 Bug : 2734697
50 || code added to commit after import of every
51 || 100 records .New variable l_processed_records added
52 || npalanis 11-OCT-2002 bug - 2608360
53 || igs_pe_code_classes is
54 || removed due to transition of code
55 || class to lookups , new columns added
56 || for codes. the tbh call are modified accordingly
57 || kumma 25-JUN-2002 In function validate_record replaced the class 'RESIDENCY_STTAUS'
58 || with 'TEACH_PEPRIOD_RESIDENCE' in cursor validate_teach_cur bug # 2423988
59 || kumma Replaced the ref cursor type validatecur with 2 normal cursors
60 || validate_teach_cur and validate_cal , bug # 2423670
61 || kumma 16-JUN-2002 Added validations for START_DATE to be not null, bug # 2423988
62 || gmaheswa 1-nov-2004 calender type and sequence number can be of active/future load calender.
63 || Obsoleted start_date and end_date columns of igs_pe_housing_int and igs_pe_teach_periods_all
64 || Modified the duplicated record check to be based on cal_type and sequence_number of a person.
65 */
66
67 l_rule VARCHAR2(1);
68 l_error_code igs_pe_housing_int.error_code%TYPE;
69 l_status igs_pe_housing_int.status%TYPE;
70 l_processed_records NUMBER(5) := 0;
71
72
73 l_prog_label VARCHAR2(100);
74 l_label VARCHAR2(100);
75 l_debug_str VARCHAR2(2000);
76 l_enable_log VARCHAR2(1);
77 l_request_id NUMBER;
78
79
80 --Pick up the records for processing from the Housing Status Interface Table
81 CURSOR housing_cur(cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE) IS
82 SELECT ai.*, i.person_id
83 FROM igs_pe_housing_int ai, igs_ad_interface_all i
84 WHERE ai.interface_id = i.interface_id AND
85 ai.status = '2' AND
86 i.interface_run_id = cp_interface_run_id AND
87 ai.interface_run_id = cp_interface_run_id;
88
89 --Cursor to check whether the Record in Interface Table already exists in OSS table
90 CURSOR dup_chk_housing_cur(cp_person_id igs_pe_teach_periods_all.person_id%TYPE,
91 cp_cal_type igs_pe_teach_periods_all.cal_type%TYPE,
92 cp_sequence_number igs_pe_teach_periods_all.sequence_number%TYPE) IS
93 SELECT p.rowid,p.* -- selecting all fields of the interface table...
94 FROM igs_pe_teach_periods_all p
95 WHERE p.person_id = cp_person_id AND
96 p.cal_type = cp_cal_type AND
97 p.sequence_number = cp_sequence_number;
98
99 dup_chk_housing_rec dup_chk_housing_cur%ROWTYPE;
100 housing_rec housing_cur%ROWTYPE;
101
102 -- Start Local Procedure crt_pe_house_status
103 PROCEDURE crt_pe_house_status(
104 p_housing_rec IN housing_cur%ROWTYPE
105 ) AS
106 l_rowid VARCHAR2(25);
107 l_teaching_period_id igs_pe_teach_periods_all.teaching_period_id%TYPE;
108 l_error_code igs_pe_housing_int.error_code%TYPE;
109 l_org_id NUMBER(15);
110 BEGIN
111 -- Call Log header
112 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
113
114 IF (l_request_id IS NULL) THEN
115 l_request_id := fnd_global.conc_request_id;
116 END IF;
117
118 l_label := 'igs.plsql.igs_ad_imp_025.prc_pe_house_status.begin_crt_pe_house_status';
119 l_debug_str := 'igs_ad_imp_025.prc_pe_house_status.crt_pe_house_status';
120
121 fnd_log.string_with_context( fnd_log.level_procedure,
122 l_label,
123 l_debug_str, NULL,
124 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
125 END IF;
126
127
128 l_org_id := igs_ge_gen_003.get_org_id;
129 igs_pe_teach_periods_pkg.insert_row (
130 x_rowid => l_rowid,
131 x_teaching_period_id => l_teaching_period_id,
132 x_person_id => p_housing_rec.person_id,
133 x_teach_period_resid_stat_cd => p_housing_rec.teach_period_resid_stat_cd,
134 x_cal_type => p_housing_rec.cal_type,
135 x_sequence_number => p_housing_rec.sequence_number,
136 x_mode => 'R',
137 x_org_id => l_org_id
138 );
139 l_error_code := NULL;
140 UPDATE igs_pe_housing_int
141 SET status = '1',
142 error_code = l_error_code
143 WHERE interface_housing_id = p_housing_rec.interface_housing_id;
144
145 EXCEPTION
146 WHEN OTHERS THEN
147 l_error_code := 'E109'; -- Person Housing Status Insertion Failed
148
149 UPDATE igs_pe_housing_int
150 SET status = '3',
151 error_code = l_error_code
152 WHERE interface_housing_id = p_housing_rec.interface_housing_id;
153
154 -- Call Log detail
155 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
156 IF (l_request_id IS NULL) THEN
157 l_request_id := fnd_global.conc_request_id;
158 END IF;
159 l_label := 'igs.plsql.igs_ad_imp_025.crt_pe_house_status.exception';
160
161 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
162 fnd_message.set_token('INTERFACE_ID',p_housing_rec.interface_housing_id);
163 fnd_message.set_token('ERROR_CD',l_error_code);
164
165 l_debug_str := fnd_message.get || ' ' || SQLERRM;
166
167 fnd_log.string_with_context( fnd_log.level_exception,
168 l_label,
169 l_debug_str, NULL,
170 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
171 END IF;
172
173 IF l_enable_log = 'Y' THEN
174 igs_ad_imp_001.logerrormessage(p_housing_rec.interface_housing_id,l_error_code);
175 END IF;
176
177 END crt_pe_house_status;
178 -- END OF LOCAL PROCEDURE crt_pe_house_status
179
180 -- Local procedure to update a record in the OSS table.
181 PROCEDURE upd_pe_house_status(p_dup_rec IN dup_chk_housing_cur%ROWTYPE,
182 p_housing_rec IN housing_cur%ROWTYPE)
183 /*
184 || Created By : gmaheswa
185 || Created On : 2/11/2004
186 || Purpose : Local procedure to update an existing housing record.
187 || Known limitations, enhancements or remarks :
188 || Change History :
189 || Who When What
190 || (reverse chronological order - newest change first)
191 || gmaheswa 2/11/2004 Created
192 */
193 AS
194
195 BEGIN
196 igs_pe_teach_periods_pkg.update_row (
197 x_rowid => p_dup_rec.rowid,
198 x_teaching_period_id => p_dup_rec.teaching_period_id,
199 x_person_id => NVL(p_dup_rec.person_id,housing_rec.person_id),
200 x_teach_period_resid_stat_cd => NVL(p_housing_rec.teach_period_resid_stat_cd,p_dup_rec.teach_period_resid_stat_cd),
201 x_cal_type => NVL(p_dup_rec.cal_type,housing_rec.cal_type),
202 x_sequence_number => NVL(p_dup_rec.sequence_number,housing_rec.sequence_number),
203 x_mode => 'R'
204 );
205
206 UPDATE igs_pe_housing_int
207 SET status = '1',
208 error_code = NULL,
209 match_ind = '18' -- '18' Match occured and used import values
210 WHERE interface_housing_id = p_housing_rec.interface_housing_id;
211
212 EXCEPTION
213 WHEN OTHERS THEN
214
215 UPDATE igs_pe_housing_int
216 SET status = '3',
217 error_code = 'E114'
218 WHERE interface_housing_id = p_housing_rec.interface_housing_id;
219
220 -- Call Log detail
221 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
222 IF (l_request_id IS NULL) THEN
223 l_request_id := fnd_global.conc_request_id;
224 END IF;
225 l_label := 'igs.plsql.igs_ad_imp_025.upd_pe_house_status.exception';
226
227 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
228 fnd_message.set_token('INTERFACE_ID',p_housing_rec.interface_housing_id);
229 fnd_message.set_token('ERROR_CD','E114');
230
231 l_debug_str := fnd_message.get || ' ' || SQLERRM;
232 fnd_log.string_with_context( fnd_log.level_exception,
233 l_label,
234 l_debug_str, NULL,
235 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
236 END IF;
237
238 IF l_enable_log = 'Y' THEN
239 igs_ad_imp_001.logerrormessage(p_housing_rec.interface_housing_id,'E114');
240 END IF;
241 END upd_pe_house_status; -- END OF LOCAL PROCEDURE upd_pe_priv_dtls
242
243 -- Start Local function Validate_Record
244 FUNCTION validate_record(p_housing_rec IN housing_cur%ROWTYPE,P_ACTION VARCHAR2)
245 RETURN BOOLEAN IS
246
247 CURSOR validate_cal(c_person_id igs_pe_person.person_id%TYPE,
248 c_cal_type igs_ca_inst.cal_type%TYPE,
249 c_seq_number igs_ca_inst.sequence_number%TYPE) IS
250 SELECT 'X'
251 FROM igs_en_su_attempt_all sa
252 WHERE sa.person_id = c_person_id AND
253 sa.unit_attempt_status IN ('ENROLLED','UNCONFIRM') AND
254 sa.cal_type = c_cal_type AND
255 sa.ci_sequence_number = c_seq_number;
256
257 CURSOR validate_load_cal(c_cal_type igs_ca_inst.cal_type%TYPE,
258 c_seq_number igs_ca_inst.sequence_number%TYPE) IS
259 SELECT 'X'
260 FROM IGS_CA_INST_ALL CA,
261 IGS_CA_TYPE TYP,
262 IGS_CA_STAT STAT
263 WHERE
264 TYP.CAL_TYPE = CA.CAL_TYPE AND
265 TYP.S_CAL_CAT = 'LOAD' AND
266 CA.END_DT >= TRUNC(SYSDATE) AND
267 CA.CAL_STATUS = STAT.CAL_STATUS AND
268 STAT.S_CAL_STATUS = 'ACTIVE' AND
269 CA.CAL_TYPE = c_cal_type AND
270 CA.SEQUENCE_NUMBER = c_seq_number;
271
272
273 l_error_code igs_pe_housing_int.error_code%TYPE;
274 l_rec VARCHAR2(1);
275 BEGIN
276 -- TEACH_PERIOD_RESID_STAT_CD
277 IF NOT(igs_pe_pers_imp_001.validate_lookup_type_code('PE_TEA_PER_RES',p_housing_rec.teach_period_resid_stat_cd,8405))
278 THEN
279 l_error_code := 'E110'; -- Person Housing Status Validation Failed - Teaching Period Housing Status
280 RAISE NO_DATA_FOUND;
281 END IF;
282
283 IF (P_ACTION = 'I') THEN
284
285 -- CAL_TYPE and SEQUENCE_NUMBER
286 IF (p_housing_rec.cal_type IS NULL OR p_housing_rec.sequence_number IS NULL) THEN
287 l_error_code := 'E112'; -- Person Housing Status Validation Failed - Calandar Type and Sequence Number are madatory
288 RAISE NO_DATA_FOUND;
289 END IF;
290
291 -- CAL_TYPE and SEQUENCE_NUMBER. Removed the NULL check, since NULL is alos an invalid value
292 --calender type and sequence number must be a teaching calender in which the student has enrolled in or a active/future load calender
293 OPEN validate_cal(p_housing_rec.person_id,p_housing_rec.cal_type,p_housing_rec.sequence_number);
294 FETCH validate_cal INTO l_rec;
295
296 IF validate_cal%NOTFOUND THEN
297 OPEN validate_load_cal(p_housing_rec.cal_type,p_housing_rec.sequence_number);
298 FETCH validate_load_cal INTO l_rec;
299 IF validate_load_cal%NOTFOUND THEN
300 l_error_code := 'E113'; -- Person Housing Status Validation Failed - Calandar Type and Sequence Number
301 CLOSE validate_load_cal;
302 CLOSE validate_cal;
303 RAISE NO_DATA_FOUND;
304 ELSE
305 l_error_code := NULL;
306 CLOSE validate_load_cal;
307 CLOSE validate_cal;
308 END IF;
309 ELSE
310 l_error_code := NULL;
311 CLOSE validate_cal;
312 END IF;
313
314 END IF;
315
316 RETURN TRUE;
317 EXCEPTION
318 WHEN OTHERS THEN
319 UPDATE igs_pe_housing_int
320 SET status = '3',
321 error_code = l_error_code
322 WHERE interface_housing_id = p_housing_rec.interface_housing_id;
323
324 -- Call Log detail
325
326 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
327
328 IF (l_request_id IS NULL) THEN
329 l_request_id := fnd_global.conc_request_id;
330 END IF;
331
332 l_label := 'igs.plsql.igs_ad_imp_025.prc_pe_house_status.exception_validate_record';
333
334 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
335 fnd_message.set_token('INTERFACE_ID',p_housing_rec.interface_housing_id);
336 fnd_message.set_token('ERROR_CD',l_error_code);
337
338 l_debug_str := fnd_message.get || ' ' || SQLERRM;
339
340 fnd_log.string_with_context( fnd_log.level_exception,
341 l_label,
342 l_debug_str, NULL,
343 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
344 END IF;
345
346 IF l_enable_log = 'Y' THEN
347 igs_ad_imp_001.logerrormessage(p_housing_rec.interface_housing_id,l_error_code);
348 END IF;
349
350 RETURN FALSE;
351 END Validate_Record;
352 -- End Local function Validate_Record
353
354 BEGIN
355 l_prog_label := 'igs.plsql.igs_ad_imp_025.prc_pe_house_status';
356 l_label := 'igs.plsql.igs_ad_imp_025.prc_pe_house_status.';
357 l_enable_log := igs_ad_imp_001.g_enable_log;
358 l_interface_run_id :=igs_ad_imp_001.g_interface_run_id; -- fetching the interface run ID from the AD imp process.
359 -- Every child records needs to be updated with this value.
360 -- <nsidana 9/25/2003 Import process enhancements>
361 -- fetch the rule before the loop.
362
363 l_rule := igs_ad_imp_001.find_source_cat_rule(p_source_type_id=>P_SOURCE_TYPE_ID,p_category=>'PERSON_HOUSING_STATUS');
364
365 -- 1. If the rule is E or I, and the match ind column is not null, update all the records to status 3 as they are invalids.
366
367 IF ((l_rule='E') OR (l_rule='I')) THEN
368 UPDATE igs_pe_housing_int phi
369 SET status = '3',
370 error_code = 'E695'
371 WHERE phi.status = '2' AND
372 phi.interface_run_id = l_interface_run_id AND
373 phi.match_ind IS NOT NULL;
374 END IF;
375
376 -- 2 . If rule is E and the match ind is null, we update the interface table for all duplicate records with status 1 and match ind 19.
377
378 IF (l_rule = 'E') THEN
379 UPDATE igs_pe_housing_int phi
380 SET status = '1',
381 match_ind = '19'
382 WHERE phi.status = '2' AND
383 phi.interface_run_id = l_interface_run_id AND
384 EXISTS
385 (SELECT 1
386 FROM igs_pe_teach_periods_all pi, igs_ad_interface_all ai
387 WHERE phi.interface_id = ai.interface_id AND
388 ai.interface_run_id = l_interface_run_id AND
389 ai.person_id = pi.person_id AND
390 UPPER(phi.cal_type) = pi.cal_type AND
391 phi.sequence_number = pi.sequence_number);
392 END IF;
393
394 -- 3. If rule is R and the record status is 18,19,22,23 these records have been processed, but didn't get updated. Update them to 1
395
396 IF (l_rule='R') THEN
397 UPDATE igs_pe_housing_int phi
398 SET status = '1'
399 WHERE phi.status = '2' AND
400 phi.interface_run_id = l_interface_run_id AND
401 phi.match_ind IN ('18','19','22','23');
402 END IF;
403
404 -- 4. If rule is R and the match ind is not null and is neither 21 nor 25, update it to errored record.
405
406 IF (l_rule = 'R') THEN
407 UPDATE igs_pe_housing_int phi
408 SET status = '3', error_code = 'E695'
409 WHERE phi.status = '2' AND
410 phi.interface_run_id = l_interface_run_id AND
411 (phi.match_ind IS NOT NULL AND phi.match_ind NOT IN ('21','25'));
412 END IF;
413
414 -- 5. If rule = 'R' and there is no discprepency in duplicate records, update them to status 1 and match ind 23.
415
416 IF (l_rule ='R') THEN
417 UPDATE igs_pe_housing_int phi
418 SET status = '1', match_ind = '23'
419 WHERE phi.status = '2' AND
420 phi.interface_run_id = l_interface_run_id AND
421 phi.match_ind IS NULL AND
422 EXISTS
423 (SELECT 1
424 FROM igs_pe_teach_periods_all pi, igs_ad_interface_all ai
425 WHERE phi.interface_id = ai.interface_id AND
426 ai.interface_run_id = l_interface_run_id AND
427 pi.person_id = ai.person_id AND
428 pi.TEACH_PERIOD_RESID_STAT_CD = UPPER(phi.TEACH_PERIOD_RESID_STAT_CD) AND
429 pi.cal_type = UPPER(phi.cal_type) AND
430 pi.sequence_number = phi.sequence_number
431 ) ;
432 END IF;
433
434 -- 6. If rule is R and there are still some records, they are the ones for which there is some discrepency existing. Update them to status 3
435 -- and value from the OSS table.
436
437 IF (l_rule ='R') THEN
438 UPDATE igs_pe_housing_int phi
439 SET status = 3,
440 match_ind = 20,
441 dup_teaching_period_id = (SELECT pi.teaching_period_id
442 FROM igs_pe_teach_periods_all pi, igs_ad_interface_all ai
443 WHERE ai.interface_id = phi.interface_id AND
444 ai.interface_run_id = l_interface_run_id AND
445 ai.person_id = pi.person_id AND
446 UPPER(phi.cal_type) = pi.cal_type AND
447 phi.sequence_number = pi.sequence_number AND
448 ROWNUM < 2)
449 WHERE phi.status='2' AND
450 phi.interface_run_id = l_interface_run_id AND
451 phi.match_ind IS NULL AND
452 EXISTS
453 (SELECT 1
454 FROM igs_pe_teach_periods_all pi, igs_ad_interface_all ai
455 WHERE ai.interface_run_id = l_interface_run_id AND
456 ai.interface_id = phi.interface_id AND
457 ai.person_id = pi.person_id AND
458 UPPER(phi.cal_type) = pi.cal_type AND
459 phi.sequence_number = pi.sequence_number
460 );
461 END IF;
462
463 -- process the remanining records.
464 FOR housing_rec IN housing_cur(l_interface_run_id) LOOP
465 housing_rec.teach_period_resid_stat_cd := UPPER(housing_rec.teach_period_resid_stat_cd);
466 housing_rec.cal_type := UPPER(housing_rec.cal_type);
467
468 l_processed_records := l_processed_records + 1;
469
470 -- For each record picked up do the following :
471 -- Check to see if the record already exists.
472 dup_chk_housing_rec.teaching_period_id := NULL;
473 OPEN dup_chk_housing_cur(housing_rec.person_id,
474 housing_rec.cal_type,
475 housing_rec.sequence_number
476 );
477 FETCH dup_chk_housing_cur INTO dup_chk_housing_rec;
478 CLOSE dup_chk_housing_cur;
479
480 --If its a duplicate record find the source category rule for that Source Category.
481 IF dup_chk_housing_rec.teaching_period_id IS NOT NULL THEN
482 IF ((l_rule = 'I') OR ((l_rule = 'R') AND (housing_rec.match_ind = '21')))THEN
483 IF validate_record(housing_rec,'U') THEN
484 upd_pe_house_status(dup_chk_housing_rec,housing_rec);
485 END IF;
486 END IF;
487 ELSE -- If its not a duplicate record then Create a new record in OSS
488 IF validate_record(housing_rec,'I') THEN
489 crt_pe_house_status(p_housing_rec => housing_rec);
490 END IF;
491 END IF; -- Record existance in IGS_PE_TEACH_PERIODS check
492
493 IF l_processed_records = 100 THEN
494 COMMIT;
495 l_processed_records := 0;
496 END IF;
497 END LOOP;
498 END prc_pe_house_status;
499
500
501 PROCEDURE Prc_Pe_Felony_Dtls(
502 p_source_type_id IN NUMBER,
503 p_batch_id IN NUMBER ) AS
504 /*
505 || Created By :Sarakshi
506 || Created On :12-Nov-2001
507 || Purpose : This procedure is for importing Person Felony Details Information.
508 || DLD: Person Interface DLD. Enh Bug# 2103692.
509 || Known limitations, enhancements or remarks :
510 || Change History :
511 || Who When What
512 || (reverse chronological order - newest change first)
513 || npalanis 22-JAN-2003 Bug : 2735882
514 || Validation for birth date added
515 || npalanis 6-JAN-2003 Bug : 2734697
516 || code added to commit after import of every
517 || 100 records .New variable l_processed_records added
518 || npalanis 23-DEC-2002 Bug : 2523488
519 || check added to validate that crime date is not greater than sysdate
520 || npalanis 30_OCT-2002 Bug : 2608360
521 || Trunc function added to crime date and nvl added for
522 || disp_action_info
523 */
524
525 l_prog_label VARCHAR2(100);
526 l_label VARCHAR2(100);
527 l_debug_str VARCHAR2(2000);
528 l_enable_log VARCHAR2(1);
529 l_request_id NUMBER;
530
531 --Pick up the records for processing from the Felony Details Interface Table
532 CURSOR felony_dtls_cur(cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE) IS
533 SELECT ai.*, i.person_id
534 FROM igs_pe_flny_dtl_int ai,
535 igs_ad_interface_all i
536 WHERE ai.interface_id = i.interface_id
537 AND ai.status = '2'
538 AND ai.interface_run_id=cp_interface_run_id
539 AND i.interface_run_id = cp_interface_run_id;
540
541 --Cursor to check whether the Record in Interface Table already exists in OSS table
542 CURSOR dup_chk_cur ( felony_dtls_rec felony_dtls_cur%ROWTYPE)IS
543 SELECT pf.rowid,pf.* -- select all the feilds from the OSS table to avoid opening the cursor below.
544 FROM igs_pe_felony_dtls pf
545 WHERE person_id = felony_dtls_rec.person_id
546 AND UPPER(crime_nature) = UPPER(felony_dtls_rec.crime_nature)
547 AND TRUNC(crime_date) = TRUNC(felony_dtls_rec.crime_date);
548
549 dup_chk_rec dup_chk_cur%ROWTYPE;
550
551 l_rule VARCHAR2(1);
552 l_status IGS_PE_FLNY_DTL_INT.status%TYPE;
553 l_error_code IGS_PE_FLNY_DTL_INT.error_code%TYPE;
554 l_processed_records NUMBER(5) := 0;
555 l_message_name VARCHAR2(30) := NULL;
556 l_app VARCHAR2(50) := NULL;
557
558 -- Start of Local Procedure validate_felony_dtls
559 FUNCTION validate_felony_dtls(p_felony_dtls_cur felony_dtls_cur%ROWTYPE) RETURN BOOLEAN IS
560 l_error_code IGS_PE_FLNY_DTL_INT.error_code%TYPE;
561 BEGIN
562
563 -- Convict_Indicator Validation
564 IF p_felony_dtls_cur.convict_ind NOT IN('Y','N') THEN
565 l_error_code :='E115';
566 RAISE NO_DATA_FOUND;
567 END IF;
568
569 -- Bug : 2523488
570 -- check added to validate that crime date is not greater than sysdate
571 IF p_felony_dtls_cur.crime_date > TRUNC(SYSDATE) THEN
572 l_error_code :='E578';
573 RAISE NO_DATA_FOUND;
574 END IF;
575
576 RETURN TRUE;
577 EXCEPTION
578 WHEN NO_DATA_FOUND THEN
579 IF l_enable_log = 'Y' THEN
580 igs_ad_imp_001.logerrormessage(p_felony_dtls_cur.interface_felony_dtls_id,l_error_code,'IGS_PE_FLNY_DTL_INT');
581 END IF;
582
583 UPDATE igs_pe_flny_dtl_int
584 SET status = '3',
585 error_code = l_error_code
586 WHERE interface_felony_dtls_id = p_felony_dtls_cur.interface_felony_dtls_id;
587 RETURN FALSE;
588 END validate_felony_dtls;
589 -- End Local Validate_Felony_Dtls
590
591 -- Start of local procedure crt_pe_felony_dtls
592 -- kumma, added one more parameter to the Igs_Pe_Felony_Dtls_Pkg.insert_row, #2608360
593
594 PROCEDURE crt_pe_felony_dtls( p_felony_dtls_rec felony_dtls_cur%ROWTYPE,
595 p_status OUT NOCOPY VARCHAR2,
596 p_error_code OUT NOCOPY VARCHAR2) AS
597
598 l_rowid VARCHAR2(25);
599 l_felony_dtls_id IGS_PE_FELONY_DTLS.felony_details_id%TYPE;
600
601 BEGIN
602 -- Call Log header
603
604 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
605
606 IF (l_request_id IS NULL) THEN
607 l_request_id := fnd_global.conc_request_id;
608 END IF;
609
610 l_label := 'igs.plsql.igs_ad_imp_025.prc_pe_felony_dtls.begin_crt_pe_felony_dtls';
611 l_debug_str := 'igs_ad_imp_025.crt_pe_felony_dtls';
612
613 fnd_log.string_with_context( fnd_log.level_procedure,
614 l_label,
615 l_debug_str, NULL,
616 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
617 END IF;
618 Igs_Pe_Felony_Dtls_Pkg.insert_row (
619 x_rowid => l_rowid ,
620 x_felony_details_id => l_felony_dtls_id,
621 x_person_id => p_felony_dtls_rec.person_id,
622 x_crime_nature => p_felony_dtls_rec.crime_nature,
623 x_crime_date => p_felony_dtls_rec.crime_date,
624 x_convict_ind => p_felony_dtls_rec.convict_ind,
625 x_disp_action_info => p_felony_dtls_rec.disp_action_info,
626 x_mode => 'R');
627
628 p_status :='1';
629 p_error_code :=NULL;
630
631 EXCEPTION
632 WHEN OTHERS THEN
633
634 FND_MESSAGE.PARSE_ENCODED(FND_MESSAGE.GET_ENCODED, l_app, l_message_name);
635
636 IF l_message_name = 'IGS_PE_INT_DT_LT_BRDT' THEN
637 p_status :='3';
638 p_error_code := 'E579';
639
640 ELSIF l_message_name = 'IGS_PE_SS_FLNY_CANT_INSERT' THEN
641 p_status :='3';
642 p_error_code := 'E167';
643 ELSIF l_message_name = 'IGS_PE_SS_NO_CRMNL_CONVICT' THEN
644 p_status := '3';
645 p_error_code := 'E166';
646 ELSE
647 p_status :='3';
648 p_error_code := 'E120';
649
650 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
651
652 IF (l_request_id IS NULL) THEN
653 l_request_id := fnd_global.conc_request_id;
654 END IF;
655
656 l_label := 'igs.plsql.igs_ad_imp_025.prc_pe_felony_dtls.exception_crt_pe_felony_dtls';
657
658 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
659 fnd_message.set_token('INTERFACE_ID',p_felony_dtls_rec.interface_felony_dtls_id);
660 fnd_message.set_token('ERROR_CD',p_error_code);
661
662 l_debug_str := fnd_message.get || ' ' || SQLERRM;
663
664 fnd_log.string_with_context( fnd_log.level_exception,
665 l_label,
666 l_debug_str, NULL,
667 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
668 END IF;
669 END IF;
670 IF l_enable_log = 'Y' THEN
671 igs_ad_imp_001.logerrormessage(p_felony_dtls_rec.interface_felony_dtls_id,p_error_code,'IGS_PE_FLNY_DTL_INT');
672 END IF;
673 END crt_pe_felony_dtls;
674 --
675 -- End of Local Procedure crt_pe_felony_dtls
676 --Start of main procedure
677 BEGIN
678 -- Call Log header
679 l_prog_label := 'igs.plsql.igs_ad_imp_025.prc_pe_felony_dtls';
680 l_label := 'igs.plsql.igs_ad_imp_025.prc_pe_felony_dtls.';
681 l_enable_log := igs_ad_imp_001.g_enable_log;
682 l_interface_run_id:=igs_ad_imp_001.g_interface_run_id; -- fetching the interface run ID from the AD imp process.
683 -- Every child records needs to be updated with this value.
684
685 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
686
687 IF (l_request_id IS NULL) THEN
688 l_request_id := fnd_global.conc_request_id;
689 END IF;
690
691 l_label := 'igs.plsql.igs_ad_imp_025.prc_pe_felony_dtls.begin';
692 l_debug_str := 'igs_ad_imp_025.prc_pe_felony_dtls';
693
694 fnd_log.string_with_context( fnd_log.level_procedure,
695 l_label,
696 l_debug_str, NULL,
697 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
698 END IF;
699
700 --<nsidana 9/25/2003 Import process enhancements>
701
702 l_rule :=Igs_Ad_Imp_001.find_source_cat_rule(p_source_type_id,'PERSON_DISCIPLINARY_DTLS');
703
704
705 -- 1. If the rule is E or I, and the match ind column is not null, update all the records to status 3 as they are invalids.
706
707 IF ((l_rule='E') OR (l_rule='I')) THEN
708 UPDATE igs_pe_flny_dtl_int pfi
709 SET status = '3',
710 error_code = 'E695'
711 WHERE pfi.status = '2' AND
712 pfi.interface_run_id = l_interface_run_id AND
713 pfi.match_ind IS NOT NULL;
714 END IF;
715
716 -- 2 . If rule is E and the match ind is null, we update the interface table for all duplicate records with status 1 and match ind 19.
717
718 IF (l_rule = 'E') THEN
719 UPDATE igs_pe_flny_dtl_int pfi
720 SET status = '1',
721 match_ind = '19'
722 WHERE pfi.status = '2' AND
723 pfi.interface_run_id = l_interface_run_id AND
724 pfi.match_ind IS NULL AND
725 EXISTS (SELECT 1
726 FROM igs_pe_felony_dtls pi,
727 igs_ad_interface_all aii
728 WHERE pfi.interface_id = aii.interface_id
729 AND aii.interface_run_id = l_interface_run_id
730 AND aii.person_id = pi.person_id
731 AND UPPER(pfi.crime_nature) = UPPER(pi.crime_nature)
732 AND TRUNC(pfi.crime_date) = TRUNC(pi.crime_date));
733 END IF;
734
735 -- 3. If rule is R and the record status is 18,19,22,23 these records have been processed, but didn't get updated. Update them to 1
736
737 IF (l_rule='R') THEN
738 UPDATE igs_pe_flny_dtl_int pfi
739 SET status = 1
740 WHERE pfi.status = '2' AND
741 pfi.interface_run_id = l_interface_run_id AND
742 pfi.match_ind IN ('18','19','22','23');
743 END IF;
744
745 -- 4. If rule is R and the match ind is not null and is neither 21 nor 25, update it to errored record.
746
747 IF (l_rule = 'R') THEN
748 UPDATE igs_pe_flny_dtl_int pfi
749 SET status = 3,
750 error_code = 'E695'
751 WHERE pfi.status = '2' AND
752 pfi.interface_run_id = l_interface_run_id AND
753 (pfi.match_ind IS NOT NULL AND pfi.match_ind NOT IN ('21','25'));
754 END IF;
755
756 -- 5. If rule = 'R' and there is no discprepency in duplicate records, update them to status 1 and match ind 23.
757
758 IF (l_rule ='R') THEN
759 UPDATE igs_pe_flny_dtl_int pfi
760 SET status = '1',
761 match_ind = '23'
762 WHERE pfi.status = '2' AND
763 pfi.interface_run_id = l_interface_run_id AND
764 pfi.match_ind IS NULL AND
765 EXISTS
766 (SELECT 1
767 FROM igs_pe_felony_dtls pi,
768 igs_ad_interface_all aii
769 WHERE pfi.interface_id = aii.interface_id
770 AND aii.interface_run_id = l_interface_run_id
771 AND NVL(aii.person_id,-99)= NVL(pi.person_id,-99)
772 AND UPPER(pfi.crime_nature) = UPPER(pi.crime_nature)
773 AND TRUNC(pfi.crime_date) = TRUNC(pi.crime_date)
774 AND UPPER(pfi.convict_ind) = UPPER(pi.convict_ind)
775 AND NVL(UPPER(pfi.disp_action_info),'*!*') = NVL(UPPER(pi.disp_action_info),'*!*')
776 );
777 END IF;
778
779 -- 6. If rule is R and there are still some records, they are the ones for which there is some discrepency existing. Update them to status 3
780 -- and value from the OSS table.
781
782 IF (l_rule ='R') THEN
783 UPDATE igs_pe_flny_dtl_int pfi
784 SET status='3',
785 match_ind='20',
786 dup_felony_details_id=(SELECT pi.FELONY_DETAILS_ID
787 FROM igs_pe_felony_dtls pi,
788 igs_ad_interface_all aii
789 WHERE pfi.interface_id = aii.interface_id
790 AND aii.interface_run_id = l_interface_run_id
791 AND aii.person_id = pi.person_id
792 AND UPPER(pfi.crime_nature) = UPPER(pi.crime_nature)
793 AND TRUNC(pfi.crime_date) = TRUNC(pi.crime_date))
794 WHERE pfi.status='2' AND
795 pfi.interface_run_id = l_interface_run_id AND
796 pfi.match_ind IS NULL AND
797 EXISTS
798 (SELECT 1
799 FROM igs_pe_felony_dtls pi,
800 igs_ad_interface_all aii
801 WHERE pfi.interface_id = aii.interface_id
802 AND aii.interface_run_id = l_interface_run_id
803 AND aii.person_id = pi.person_id
804 AND UPPER(pfi.crime_nature) = UPPER(pi.crime_nature)
805 AND TRUNC(pfi.crime_date) = TRUNC(pi.crime_date));
806
807 END IF;
808
809 -- Process the remaining records now...
810
811 FOR felony_dtls_rec IN felony_dtls_cur(l_interface_run_id) LOOP
812
813 l_processed_records := l_processed_records + 1;
814
815 felony_dtls_rec.crime_date := TRUNC(felony_dtls_rec.crime_date);
816 --Validate the record picked up from interface table
817 IF validate_felony_dtls( felony_dtls_rec) THEN
818 -- for every record check whether a corresponding row
819 -- already exists in the table igs_pe_felony_dtls.
820 dup_chk_rec.felony_details_id :=NULL;
821 OPEN dup_chk_cur ( felony_dtls_rec );
822 FETCH dup_chk_cur INTO dup_chk_rec;
823 CLOSE dup_chk_cur;
824
825 IF dup_chk_rec.felony_details_id IS NOT NULL THEN
826 --If its a duplicate record find the source category rule for that Source Category.
827
828 IF l_rule = 'I' THEN
829 BEGIN
830 igs_pe_felony_dtls_pkg.update_row(
831 x_rowid => dup_chk_rec.rowid,
832 x_felony_details_id=> dup_chk_rec.felony_details_id,
833 x_person_id =>NVL( felony_dtls_rec.person_id,dup_chk_rec.person_id),
834 x_crime_nature => dup_chk_rec.crime_nature,
835 x_crime_date => dup_chk_rec.crime_date,
836 x_convict_ind => felony_dtls_rec.convict_ind,
837 x_disp_action_info => nvl(felony_dtls_rec.disp_action_info,dup_chk_rec.disp_action_info),
838 x_mode => 'R'
839 );
840 UPDATE igs_pe_flny_dtl_int
841 SET status = '1',
842 error_code=NULL,
843 match_ind='18'
844 WHERE interface_felony_dtls_id = felony_dtls_rec.interface_felony_dtls_id;
845 EXCEPTION
846 WHEN OTHERS THEN
847 FND_MESSAGE.PARSE_ENCODED(FND_MESSAGE.GET_ENCODED, l_app, l_message_name);
848 IF l_message_name = 'IGS_PE_INT_DT_LT_BRDT' THEN
849 l_status :='3';
850 l_error_code := 'E579';
851
852 ELSIF l_message_name = 'IGS_PE_SS_NO_CRMNL_CONVICT' THEN
853 l_status :='3';
854 l_error_code := 'E166';
855
856 ELSE
857 l_status :='3';
858 l_error_code := 'E121';
859 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
860
861 IF (l_request_id IS NULL) THEN
862 l_request_id := fnd_global.conc_request_id;
863 END IF;
864
865 l_label := 'igs.plsql.igs_ad_imp_025.prc_pe_felony_dtls.exception';
866
867 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
868 fnd_message.set_token('INTERFACE_ID',felony_dtls_rec.interface_felony_dtls_id);
869 fnd_message.set_token('ERROR_CD',l_error_code);
870
871 l_debug_str := fnd_message.get || ' ' || SQLERRM;
872
873 fnd_log.string_with_context( fnd_log.level_exception,
874 l_label,
875 l_debug_str, NULL,
876 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
877 END IF;
878 END IF;
879 IF l_enable_log = 'Y' THEN
880 igs_ad_imp_001.logerrormessage(felony_dtls_rec.interface_felony_dtls_id,l_error_code,'IGS_PE_FLNY_DTL_INT');
881 END IF;
882
883 UPDATE igs_pe_flny_dtl_int
884 SET status = l_status ,
885 error_code = l_error_code
886 WHERE interface_felony_dtls_id = felony_dtls_rec.interface_felony_dtls_id;
887 END;
888 ELSIF l_rule = 'R' THEN
889 IF felony_dtls_rec.match_ind = '21' THEN
890 BEGIN
891 igs_pe_felony_dtls_pkg.update_row(
892 x_rowid => dup_chk_rec.rowid,
893 x_felony_details_id=> dup_chk_rec.felony_details_id,
894 x_person_id =>NVL( dup_chk_rec.person_id,dup_chk_rec.person_id),
895 x_crime_nature =>felony_dtls_rec.crime_nature,
896 x_crime_date => felony_dtls_rec.crime_date,
897 x_convict_ind => felony_dtls_rec.convict_ind,
898 x_disp_action_info => NVL(felony_dtls_rec.disp_action_info,dup_chk_rec.disp_action_info),
899 x_mode => 'R'
900 );
901
902 UPDATE igs_pe_flny_dtl_int
903 SET status = '1',
904 match_ind='18',
905 error_code=NULL
906 WHERE interface_felony_dtls_id = felony_dtls_rec.interface_felony_dtls_id;
907 EXCEPTION
908 WHEN OTHERS THEN
909
910 FND_MESSAGE.PARSE_ENCODED(FND_MESSAGE.GET_ENCODED, l_app, l_message_name);
911
912 IF l_message_name = 'IGS_PE_INT_DT_LT_BRDT' THEN
913 l_status :='3';
914 l_error_code := 'E579';
915
916 ELSIF l_message_name = 'IGS_PE_SS_NO_CRMNL_CONVICT' THEN
917 l_status :='3';
918 l_error_code := 'E166';
919 ELSE
920 l_status :='3';
921 l_error_code := 'E121';
922
923 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
924
925 IF (l_request_id IS NULL) THEN
926 l_request_id := fnd_global.conc_request_id;
927 END IF;
928
929 l_label := 'igs.plsql.igs_ad_imp_025.prc_pe_felony_dtls.exception1';
930
931 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
932 fnd_message.set_token('INTERFACE_ID',felony_dtls_rec.interface_felony_dtls_id);
933 fnd_message.set_token('ERROR_CD',l_error_code);
934
935 l_debug_str := fnd_message.get || ' ' || SQLERRM;
936
937 fnd_log.string_with_context( fnd_log.level_exception,
938 l_label,
939 l_debug_str, NULL,
940 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
941 END IF;
942 END IF;
943
944 IF l_enable_log = 'Y' THEN
945 igs_ad_imp_001.logerrormessage(felony_dtls_rec.interface_felony_dtls_id,l_error_code,'IGS_PE_FLNY_DTL_INT');
946 END IF;
947
948 UPDATE igs_pe_flny_dtl_int
949 SET status = l_status ,
950 error_code = l_error_code
951 WHERE interface_felony_dtls_id = felony_dtls_rec.interface_felony_dtls_id;
952 END;
953 END IF; -- end if for match_ind = '21'
954 END IF; -- end if for l_rule
955 ELSE--Duplicate record does not exists
956 --Insert the record in the oss table
957 crt_pe_felony_dtls(
958 p_felony_dtls_rec => felony_dtls_rec,
959 p_status =>l_status,
960 p_error_code =>l_error_code );
961 UPDATE igs_pe_flny_dtl_int
962 SET status = l_status,
963 error_code = l_error_code
964 WHERE interface_felony_dtls_id = felony_dtls_rec.interface_felony_dtls_id;
965 END IF;-- For Dup_cur
966 END IF;--validate record
967
968 IF l_processed_records = 100 THEN
969 COMMIT;
970 l_processed_records := 0;
971 END IF;
972 END LOOP;
973 END prc_pe_felony_dtls;
974 --
975 -- End of Main Procedure PRC_PE_FELONY_DTLS
976 --
977
978 PROCEDURE Prc_Pe_Hearing_Dtls(
979 p_source_type_id IN NUMBER,
980 p_batch_id IN NUMBER ) AS
981 /*
982 || Created By :Sarakshi
983 || Created On :12-Nov-2001
984 || Purpose : This procedure imports Person Hearing Details
985 || Bug no.2103692:Person Interface DLD
986 || Known limitations, enhancements or remarks :
987 || Change History :
988 || Who When What
989 || (reverse chronological order - newest change first)
990 || npalanis 22-JAN-2003 Bug : 2735882
991 || Validation for birth date added
992 || npalanis 6-JAN-2003 Bug : 2734697
993 || code added to commit after import of every
994 || 100 records .New variable l_processed_records added
995 */
996
997 l_default_date DATE;
998
999 l_prog_label VARCHAR2(100);
1000 l_label VARCHAR2(100);
1001 l_debug_str VARCHAR2(2000);
1002 l_enable_log VARCHAR2(1);
1003 l_request_id NUMBER;
1004
1005 --Pick up the records for processing from the Hearing Details Interface Table
1006 CURSOR hearing_dtls_cur(cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE) IS
1007 SELECT ai.*, i.person_id
1008 FROM igs_pe_hear_dtl_int ai,
1009 igs_ad_interface_all i
1010 WHERE ai.interface_id = i.interface_id
1011 AND ai.status = '2'
1012 AND ai.interface_run_id=cp_interface_run_id
1013 AND i.interface_run_id = cp_interface_run_id;
1014
1015 --Cursor to check whether the Record in Interface Table already exists in OSS table
1016 CURSOR dup_chk_cur ( hearing_dtls_rec hearing_dtls_cur%ROWTYPE)IS
1017 SELECT pd.rowid,pd.* -- <nsidana 9/25/2003 Import process enhancements. Fetching all values here to avoid calling the cursor below.>
1018 FROM igs_pe_hearing_dtls pd
1019 WHERE person_id = hearing_dtls_rec.person_id
1020 AND UPPER(description) = UPPER(hearing_dtls_rec.description)
1021 AND NVL(TRUNC(start_date),l_default_date)= NVL(TRUNC(hearing_dtls_rec.start_date),l_default_date);
1022 dup_chk_rec dup_chk_cur%ROWTYPE;
1023
1024 l_rule VARCHAR2(1);
1025 l_status IGS_PE_HEAR_DTL_INT.status%TYPE;
1026 l_error_code IGS_PE_HEAR_DTL_INT.error_code%TYPE;
1027 l_processed_records NUMBER(5) := 0;
1028 l_app VARCHAR2(50) := NULL;
1029 l_message_name VARCHAR2(30) := NULL;
1030 --
1031 -- Start of Local Procedure validate_hearing_dtls
1032 --
1033 FUNCTION validate_hearing_dtls(p_hearing_dtls_cur hearing_dtls_cur%ROWTYPE) RETURN BOOLEAN IS
1034 l_error_code IGS_PE_HEAR_DTL_INT.error_code%TYPE;
1035 BEGIN
1036
1037 -- disp_file_ind Validation
1038 IF p_hearing_dtls_cur.dspl_file_ind NOT IN('Y','N') THEN
1039 l_error_code :='E116';
1040 RAISE NO_DATA_FOUND;
1041 END IF;
1042 -- acad_dism_ind Validation
1043 IF p_hearing_dtls_cur.acad_dism_ind NOT IN('Y','N') THEN
1044 l_error_code :='E117';
1045 RAISE NO_DATA_FOUND;
1046 END IF;
1047 -- non_acad_dism_ind Validation
1048 IF p_hearing_dtls_cur.non_acad_dism_ind NOT IN('Y','N') THEN
1049 l_error_code :='E118';
1050 RAISE NO_DATA_FOUND;
1051 END IF;
1052 --start_date / end_date validation
1053 IF (p_hearing_dtls_cur.start_date IS NOT NULL) AND ( p_hearing_dtls_cur.end_date IS NOT NULL) THEN
1054 IF p_hearing_dtls_cur.start_date > p_hearing_dtls_cur.end_date THEN
1055 l_error_code :='E119';
1056 RAISE NO_DATA_FOUND;
1057 END IF;
1058 END IF;
1059
1060 RETURN TRUE;
1061 EXCEPTION
1062 WHEN NO_DATA_FOUND THEN
1063 IF l_enable_log = 'Y' THEN
1064 igs_ad_imp_001.logerrormessage(p_hearing_dtls_cur.interface_hearing_dtls_id,l_error_code,'IGS_PE_HEAR_DTL_INT');
1065 END IF;
1066
1067 UPDATE igs_pe_hear_dtl_int
1068 SET status = '3',
1069 error_code = l_error_code
1070 WHERE interface_hearing_dtls_id = p_hearing_dtls_cur.interface_hearing_dtls_id;
1071 RETURN FALSE;
1072 END validate_hearing_dtls;
1073 -- End Local Validate_Hearing_Dtls
1074
1075 -- Start of local procedure crt_pe_hearing_dtls
1076 PROCEDURE crt_pe_hearing_dtls
1077 ( p_hearing_dtls_rec hearing_dtls_cur%ROWTYPE,
1078 p_status OUT NOCOPY VARCHAR2,
1079 p_error_code OUT NOCOPY VARCHAR2) AS
1080
1081 l_rowid VARCHAR2(25);
1082 l_hearing_details_id IGS_PE_HEARING_DTLS.hearing_details_id%TYPE;
1083 BEGIN
1084
1085 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
1086
1087 IF (l_request_id IS NULL) THEN
1088 l_request_id := fnd_global.conc_request_id;
1089 END IF;
1090
1091 l_label := 'igs.plsql.igs_ad_imp_025.prc_pe_hearing_dtls.begin_crt_pe_hearing_dtls';
1092 l_debug_str := 'Igs_Ad_Imp_025.Prc_Pe_Hearing_Dtls.Crt_Pe_hearing_dtls';
1093
1094 fnd_log.string_with_context( fnd_log.level_procedure,
1095 l_label,
1096 l_debug_str, NULL,
1097 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1098 END IF;
1099
1100 Igs_Pe_Hearing_Dtls_Pkg.insert_row (
1101 x_rowid => l_rowid ,
1102 x_hearing_details_id=> l_hearing_details_id,
1103 x_person_id => p_hearing_dtls_rec.person_id,
1104 x_description => p_hearing_dtls_rec.description,
1105 x_start_date => p_hearing_dtls_rec.start_date,
1106 x_end_date => p_hearing_dtls_rec.end_date,
1107 x_dspl_file_ind => p_hearing_dtls_rec.dspl_file_ind,
1108 x_acad_dism_ind => p_hearing_dtls_rec.acad_dism_ind,
1109 x_non_acad_dism_ind => p_hearing_dtls_rec.non_acad_dism_ind,
1110 x_mode => 'R'
1111 );
1112 p_status :='1';
1113 p_error_code :=NULL;
1114
1115 EXCEPTION
1116 WHEN OTHERS THEN
1117
1118 FND_MESSAGE.PARSE_ENCODED(FND_MESSAGE.GET_ENCODED,l_app,l_message_name);
1119
1120 IF l_message_name = 'IGS_AD_STRT_DT_LESS_BIRTH_DT' THEN
1121 p_status :='3';
1122 p_error_code := 'E222';
1123 ELSIF l_message_name = 'IGS_PE_CANT_SPECIFY_FROM_DATE' THEN
1124 p_status :='3';
1125 p_error_code := 'E582';
1126 ELSE
1127 p_status :='3';
1128 p_error_code := 'E122';
1129 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
1130
1131 IF (l_request_id IS NULL) THEN
1132 l_request_id := fnd_global.conc_request_id;
1133 END IF;
1134
1135 l_label := 'igs.plsql.igs_ad_imp_025.prc_pe_hearing_dtls.exception_crt_pe_hearing_dtls';
1136
1137 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
1138 fnd_message.set_token('INTERFACE_ID',p_hearing_dtls_rec.interface_hearing_dtls_id);
1139 fnd_message.set_token('ERROR_CD',p_error_code);
1140
1141 l_debug_str := fnd_message.get || ' ' || SQLERRM;
1142
1143 fnd_log.string_with_context( fnd_log.level_exception,
1144 l_label,
1145 l_debug_str, NULL,
1146 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1147 END IF;
1148 END IF;
1149 IF l_enable_log = 'Y' THEN
1150 igs_ad_imp_001.logerrormessage(p_hearing_dtls_rec.interface_hearing_dtls_id,p_error_code,'IGS_PE_HEAR_DTL_INT');
1151 END IF;
1152
1153 END crt_pe_hearing_dtls;
1154 --
1155 -- End of Local Procedure
1156 --
1157 BEGIN
1158 l_default_date := IGS_GE_DATE.IGSDATE('4712/12/31');
1159 l_prog_label := 'igs.plsql.igs_ad_imp_025.prc_pe_hearing_dtls';
1160 l_label := 'igs.plsql.igs_ad_imp_025.prc_pe_hearing_dtls.';
1161 l_enable_log := igs_ad_imp_001.g_enable_log;
1162 l_interface_run_id := igs_ad_imp_001.g_interface_run_id; -- fetching the interface run ID from the AD imp process.
1163 -- Every child records needs to be updated with this value.
1164
1165 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
1166
1167 IF (l_request_id IS NULL) THEN
1168 l_request_id := fnd_global.conc_request_id;
1169 END IF;
1170
1171 l_label := 'igs.plsql.igs_ad_imp_025.prc_pe_hearing_dtls.begin';
1172 l_debug_str := 'Igs_Ad_Imp_025.Prc_Pe_Hearing_Dtls';
1173
1174 fnd_log.string_with_context( fnd_log.level_procedure,
1175 l_label,
1176 l_debug_str, NULL,
1177 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1178 END IF;
1179
1180
1181 -- Fetching the Rule outside the loop.
1182
1183 l_rule :=Igs_Ad_Imp_001.find_source_cat_rule(p_source_type_id,'PERSON_DISCIPLINARY_DTLS');
1184
1185
1186 -- 1. If the rule is E or I, and the match ind column is not null, update all the records to status 3 as they are invalids.
1187
1188 IF ((l_rule='E') OR (l_rule='I')) THEN
1189 UPDATE igs_pe_hear_dtl_int pdi
1190 SET status = '3',
1191 error_code = 'E695'
1192 WHERE pdi.status = '2' AND
1193 pdi.interface_run_id = l_interface_run_id AND
1194 pdi.match_ind IS NOT NULL;
1195 END IF;
1196
1197 -- 2 . If rule is E and the match ind is null, we update the interface table for all duplicate records with status 1 and match ind 19.
1198
1199 IF (l_rule = 'E') THEN
1200 UPDATE igs_pe_hear_dtl_int pdi
1201 SET status = '1',
1202 match_ind = '19'
1203 WHERE pdi.status = '2' AND
1204 pdi.interface_run_id = l_interface_run_id AND
1205 EXISTS (SELECT 1
1206 FROM igs_pe_hearing_dtls pi,
1207 igs_ad_interface_all aiii
1208 WHERE pdi.interface_id = aiii.interface_id
1209 AND aiii.interface_run_id = l_interface_run_id
1210 AND aiii.person_id = pi.person_id
1211 AND UPPER(pdi.description) = UPPER(pi.description)
1212 AND NVL(TRUNC(pdi.start_date),l_default_date) = NVL(TRUNC(pi.start_date),l_default_date)
1213 );
1214 END IF;
1215
1216 -- 3. If rule is R and the record status is 18,19,22,23 these records have been processed, but didn't get updated. Update them to 1
1217
1218 IF (l_rule='R') THEN
1219 UPDATE igs_pe_hear_dtl_int pdi
1220 SET status = '1'
1221 WHERE pdi.status = '2' AND
1222 pdi.interface_run_id = l_interface_run_id AND
1223 pdi.match_ind IN ('18','19','22','23');
1224 END IF;
1225
1226
1227 -- 4. If rule is R and the match ind is not null and is neither 21 nor 25, update it to errored record.
1228
1229 IF (l_rule = 'R') THEN
1230 UPDATE igs_pe_hear_dtl_int pdi
1231 SET status = '3',
1232 error_code = 'E695'
1233 WHERE pdi.status = '2' AND
1234 pdi.interface_run_id = l_interface_run_id AND
1235 (pdi.match_ind IS NOT NULL AND pdi.match_ind NOT IN ('21','25'));
1236 END IF;
1237
1238 -- 5. If rule = 'R' and there is no discprepency in duplicate records, update them to status 1 and match ind 23.
1239
1240 IF (l_rule ='R') THEN
1241 UPDATE igs_pe_hear_dtl_int pdi
1242 SET status = '1',
1243 match_ind = '23'
1244 WHERE pdi.status = '2' AND
1245 pdi.interface_run_id = l_interface_run_id AND
1246 pdi.match_ind IS NULL AND
1247 EXISTS (SELECT 1
1248 FROM igs_pe_hearing_dtls pi,
1249 igs_ad_interface_all aiii
1250 WHERE NVL(pi.person_id,-99) = NVL(aiii.person_id,-99)
1251 AND pdi.interface_id = aiii.interface_id
1252 AND aiii.interface_run_id = l_interface_run_id
1253 AND UPPER(pi.description) = UPPER(pdi.description)
1254 AND NVL(TRUNC(pi.start_date),l_default_date)= NVL(TRUNC(pdi.start_date),l_default_date)
1255 AND NVL(TRUNC(pi.end_date),l_default_date) = NVL(TRUNC(pdi.end_date),l_default_date)
1256 AND UPPER(pi.dspl_file_ind) = UPPER(pdi.dspl_file_ind)
1257 AND UPPER(pi.acad_dism_ind) = UPPER(pdi.acad_dism_ind)
1258 AND UPPER(pi.non_acad_dism_ind) = UPPER(pdi.non_acad_dism_ind));
1259 END IF;
1260
1261 -- 6. If rule is R and there are still some records, they are the ones for which there is some discrepency existing. Update them to status 3
1262 -- and value from the OSS table.
1263
1264 IF (l_rule ='R') THEN
1265 UPDATE igs_pe_hear_dtl_int pdi
1266 SET status = '3',
1267 match_ind = '20',
1268 dup_hearing_details_id=(SELECT pi.hearing_details_id
1269 FROM igs_pe_hearing_dtls pi,
1270 igs_ad_interface_all aiii
1271 WHERE pdi.interface_id = aiii.interface_id
1272 AND aiii.interface_run_id = l_interface_run_id
1273 AND aiii.person_id = pi.person_id
1274 AND UPPER(pdi.description) = UPPER(pi.description)
1275 AND NVL(TRUNC(pdi.start_date),l_default_date)= NVL(TRUNC(pi.start_date),l_default_date)
1276 AND ROWNUM = 1)
1277 WHERE
1278 pdi.status='2' AND
1279 pdi.interface_run_id = l_interface_run_id AND
1280 pdi.match_ind IS NULL AND
1281 EXISTS (SELECT 1
1282 FROM igs_pe_hearing_dtls pi,
1283 igs_ad_interface_all aiii
1284 WHERE pdi.interface_id = aiii.interface_id
1285 AND aiii.interface_run_id = l_interface_run_id
1286 AND aiii.person_id = pi.person_id
1287 AND UPPER(pdi.description) = UPPER(pi.description)
1288 AND NVL(TRUNC(pdi.start_date),l_default_date)
1289 = NVL(TRUNC(pi.start_date),l_default_date));
1290 END IF;
1291
1292 -- Process the rest of the records now...
1293
1294 FOR hearing_dtls_rec IN hearing_dtls_cur(l_interface_run_id) LOOP
1295
1296 l_processed_records := l_processed_records + 1;
1297 hearing_dtls_rec.start_date := TRUNC(hearing_dtls_rec.start_date);
1298 hearing_dtls_rec.end_date := TRUNC(hearing_dtls_rec.end_date);
1299 hearing_dtls_rec.non_acad_dism_ind := UPPER(hearing_dtls_rec.non_acad_dism_ind);
1300 hearing_dtls_rec.acad_dism_ind := UPPER(hearing_dtls_rec.acad_dism_ind);
1301 hearing_dtls_rec.dspl_file_ind := UPPER(hearing_dtls_rec.dspl_file_ind);
1302
1303 IF validate_hearing_dtls(hearing_dtls_rec) THEN
1304 dup_chk_rec.hearing_details_id := NULL;
1305 OPEN dup_chk_cur (hearing_dtls_rec);
1306 FETCH dup_chk_cur INTO dup_chk_rec;
1307 CLOSE dup_chk_cur;
1308 IF dup_chk_rec.hearing_details_id IS NOT NULL THEN
1309 --If its a duplicate record find the source category rule for that Source Category.
1310 IF l_rule = 'I' THEN
1311 BEGIN
1312 igs_pe_hearing_dtls_pkg.update_row(
1313 x_rowid => dup_chk_rec.rowid,
1314 x_hearing_details_id=>dup_chk_rec.hearing_details_id,
1315 x_person_id => NVL(hearing_dtls_rec.person_id,dup_chk_rec.person_id),
1316 x_description => hearing_dtls_rec.description,
1317 x_start_date =>NVL( hearing_dtls_rec.start_date,dup_chk_rec.start_date),
1318 x_end_date => NVL(hearing_dtls_rec.end_date,dup_chk_rec.end_date),
1319 x_dspl_file_ind => hearing_dtls_rec.dspl_file_ind,
1320 x_acad_dism_ind => hearing_dtls_rec.acad_dism_ind,
1321 x_non_acad_dism_ind => hearing_dtls_rec.non_acad_dism_ind,
1322 x_mode => 'R'
1323 );
1324 UPDATE igs_pe_hear_dtl_int
1325 SET status = '1',
1326 error_code=NULL,
1327 match_ind='18'
1328 WHERE interface_hearing_dtls_id = hearing_dtls_rec.interface_hearing_dtls_id;
1329 EXCEPTION
1330 WHEN OTHERS THEN
1331
1332 FND_MESSAGE.PARSE_ENCODED(FND_MESSAGE.GET_ENCODED,l_app,l_message_name);
1333
1334 IF l_message_name = 'IGS_AD_STRT_DT_LESS_BIRTH_DT' THEN
1335 l_status := '3' ;
1336 l_error_code := 'E222';
1337 ELSIF l_message_name = 'IGS_PE_CANT_SPECIFY_FROM_DATE' THEN
1338 l_status := '3' ;
1339 l_error_code := 'E582';
1340 ELSE
1341 l_status := '3' ;
1342 l_error_code := 'E123';
1343
1344 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
1345 IF (l_request_id IS NULL) THEN
1346 l_request_id := fnd_global.conc_request_id;
1347 END IF;
1348
1349 l_label := 'igs.plsql.igs_ad_imp_025.prc_pe_hearing_dtls.exception1';
1350
1351 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
1352 fnd_message.set_token('INTERFACE_ID',hearing_dtls_rec.interface_hearing_dtls_id);
1353 fnd_message.set_token('ERROR_CD',l_error_code);
1354
1355 l_debug_str := fnd_message.get || ' ' || SQLERRM;
1356
1357 fnd_log.string_with_context( fnd_log.level_exception,
1358 l_label,
1359 l_debug_str, NULL,
1360 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1361 END IF;
1362 END IF;
1363
1364 IF l_enable_log = 'Y' THEN
1365 igs_ad_imp_001.logerrormessage(hearing_dtls_rec.interface_hearing_dtls_id,l_error_code,'IGS_PE_HEAR_DTL_INT');
1366 END IF;
1367
1368 UPDATE igs_pe_hear_dtl_int
1369 SET status = l_status,
1370 error_code = l_error_code
1371 WHERE interface_hearing_dtls_id = hearing_dtls_rec.interface_hearing_dtls_id;
1372 END;
1373 ELSIF l_rule = 'R' THEN
1374 IF hearing_dtls_rec.match_ind = '21' THEN
1375 BEGIN
1376 igs_pe_hearing_dtls_pkg.update_row(
1377 x_rowid => dup_chk_rec.rowid,
1378 x_hearing_details_id=>dup_chk_rec.hearing_details_id,
1379 x_person_id => NVL(hearing_dtls_rec.person_id,dup_chk_rec.person_id),
1380 x_description => hearing_dtls_rec.description,
1381 x_start_date =>NVL( hearing_dtls_rec.start_date,dup_chk_rec.start_date),
1382 x_end_date => NVL(hearing_dtls_rec.end_date,dup_chk_rec.end_date),
1383 x_dspl_file_ind => hearing_dtls_rec.dspl_file_ind,
1384 x_acad_dism_ind => hearing_dtls_rec.acad_dism_ind,
1385 x_non_acad_dism_ind => hearing_dtls_rec.non_acad_dism_ind,
1386 x_mode => 'R'
1387 );
1388
1389 UPDATE igs_pe_hear_dtl_int
1390 SET status = '1',
1391 match_ind = '18',
1392 error_code = NULL
1393 WHERE interface_hearing_dtls_id = hearing_dtls_rec.interface_hearing_dtls_id;
1394 EXCEPTION
1395 WHEN OTHERS THEN
1396
1397 FND_MESSAGE.PARSE_ENCODED(FND_MESSAGE.GET_ENCODED,l_app,l_message_name);
1398
1399 IF l_message_name = 'IGS_AD_STRT_DT_LESS_BIRTH_DT' THEN
1400 l_status := '3' ;
1401 l_error_code := 'E222';
1402 ELSIF l_message_name = 'IGS_PE_CANT_SPECIFY_FROM_DATE' THEN
1403 l_status := '3' ;
1404 l_error_code := 'E582';
1405 ELSE
1406 l_status := '3' ;
1407 l_error_code := 'E123';
1408
1409 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
1410
1411 IF (l_request_id IS NULL) THEN
1412 l_request_id := fnd_global.conc_request_id;
1413 END IF;
1414
1415 l_label := 'igs.plsql.igs_ad_imp_025.prc_pe_hearing_dtls.exception2';
1416
1417 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
1418 fnd_message.set_token('INTERFACE_ID',hearing_dtls_rec.interface_hearing_dtls_id);
1419 fnd_message.set_token('ERROR_CD',l_error_code);
1420
1421 l_debug_str := fnd_message.get || ' ' || SQLERRM;
1422
1423 fnd_log.string_with_context( fnd_log.level_exception,
1424 l_label,
1425 l_debug_str, NULL,
1426 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1427 END IF;
1428
1429 END IF;
1430
1431 IF l_enable_log = 'Y' THEN
1432 igs_ad_imp_001.logerrormessage(hearing_dtls_rec.interface_hearing_dtls_id,l_error_code,'IGS_PE_HEAR_DTL_INT');
1433 END IF;
1434
1435
1436 UPDATE igs_pe_hear_dtl_int
1437 SET status = l_status,
1438 error_code = l_error_code
1439 WHERE interface_hearing_dtls_id = hearing_dtls_rec.interface_hearing_dtls_id;
1440 END;
1441 END IF; -- end if for match_ind = '21'
1442 END IF; -- end if for l_rule
1443 ELSE --Not a duplicate record
1444 --Insert the record in the oss table
1445 crt_pe_hearing_dtls(
1446 p_hearing_dtls_rec => hearing_dtls_rec,
1447 p_status =>l_status,
1448 p_error_code =>l_error_code );
1449 UPDATE igs_pe_hear_dtl_int
1450 SET status = l_status,
1451 error_code = l_error_code
1452 WHERE interface_hearing_dtls_id = hearing_dtls_rec.interface_hearing_dtls_id;
1453 END IF; -- End If for Dup_cur
1454 END IF;--end of validate record
1455
1456 IF l_processed_records = 100 THEN
1457 COMMIT;
1458 l_processed_records := 0;
1459 END IF;
1460
1461 END LOOP;
1462 END prc_pe_hearing_dtls;
1463 --
1464 -- End of Main Procedure PRC_PE_HEARING_DTLS
1465
1466 PROCEDURE Prc_Pe_Disciplinary_Dtls(
1467 p_source_type_id IN NUMBER,
1468 p_batch_id IN NUMBER ) AS
1469 /*
1470 || Created By :Sarakshi
1471 || Created On :12-Nov-2001
1472 || Purpose : This procedure invokes two procedure for importing felony details and hearing details
1473 || Bug no.2103692:Person Interface DLD
1474 || Known limitations, enhancements or remarks :
1475 || Change History :
1476 || Who When What
1477 || (reverse chronological order - newest change first)
1478 */
1479 BEGIN
1480 Prc_Pe_Felony_Dtls(p_source_type_id,p_batch_id);
1481 Prc_Pe_Hearing_Dtls(p_source_type_id,p_batch_id);
1482 END prc_pe_disciplinary_dtls;
1483
1484
1485 PROCEDURE prc_pe_race(
1486 p_source_type_id IN NUMBER,
1487 p_batch_id IN NUMBER ) AS
1488 /*
1489 || Created By :pkpatel
1490 || Created On :5-FEB-2003
1491 || Purpose : Multiple Races TD (This procedure is to import data from interface table IGS_PE_RACE_INT to IGS_PE_RACE)
1492 ||
1493 || Known limitations, enhancements or remarks :
1494 || Change History :
1495 || Who When What
1496 || (reverse chronological order - newest change first)
1497 */
1498
1499 l_prog_label VARCHAR2(100);
1500 l_label VARCHAR2(100);
1501 l_debug_str VARCHAR2(2000);
1502 l_enable_log VARCHAR2(1);
1503 l_request_id NUMBER;
1504 l_dup_race_cd VARCHAR2(30);
1505 l_processed_records NUMBER(5) := 0;
1506 l_dup_exists VARCHAR2(1);
1507 l_rule VARCHAR2(1);
1508
1509 --Pick up the records for processing from the Races Interface Table
1510 CURSOR race_cur(cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE) IS
1511 SELECT ai.*,i.person_id
1512 FROM igs_pe_race_int ai, igs_ad_interface_all i
1513 WHERE ai.interface_id = i.interface_id AND
1514 ai.status = '2' AND
1515 ai.interface_run_id=cp_interface_run_id AND
1516 i.interface_run_id = cp_interface_run_id;
1517
1518 --Cursor to check whether the Record in Interface Table already exists in OSS table
1519 CURSOR dup_chk_race_cur(cp_person_id igs_pe_race.person_id%TYPE, cp_race_cd igs_pe_race.race_cd%TYPE) IS
1520 SELECT pr.race_cd
1521 FROM igs_pe_race pr
1522 WHERE pr.person_id = cp_person_id AND
1523 pr.race_cd = cp_race_cd;
1524
1525 -- Start Local Procedure crt_pe_race
1526 PROCEDURE crt_pe_race(p_race_rec IN race_cur%ROWTYPE) AS
1527 l_rowid VARCHAR2(25);
1528 BEGIN
1529 -- Call Log header
1530 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
1531
1532 IF (l_request_id IS NULL) THEN
1533 l_request_id := fnd_global.conc_request_id;
1534 END IF;
1535
1536 l_label := 'igs.plsql.igs_ad_imp_025.prc_pe_race.begin_crt_pe_race';
1537 l_debug_str := 'igs_ad_imp_025.prc_pe_race.crt_pe_race';
1538
1539 fnd_log.string_with_context( fnd_log.level_procedure,
1540 l_label,
1541 l_debug_str, NULL,
1542 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1543 END IF;
1544
1545 igs_pe_race_pkg.insert_row (
1546 x_rowid => l_rowid,
1547 x_person_id => p_race_rec.person_id,
1548 x_race_cd => p_race_rec.race_cd,
1549 x_mode => 'R'
1550 );
1551 UPDATE igs_pe_race_int
1552 SET status = '1',
1553 error_code = NULL
1554 WHERE interface_race_id = p_race_rec.interface_race_id;
1555
1556 EXCEPTION
1557 WHEN OTHERS THEN
1558 -- Person Race Insertion Failed
1559 UPDATE igs_pe_race_int
1560 SET status = '3',
1561 error_code = 'E322'
1562 WHERE interface_race_id = p_race_rec.interface_race_id;
1563 -- Call Log detail
1564 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
1565
1566 IF (l_request_id IS NULL) THEN
1567 l_request_id := fnd_global.conc_request_id;
1568 END IF;
1569
1570 l_label := 'igs.plsql.igs_ad_imp_025.prc_pe_race.exception_crt_pe_race';
1571
1572 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
1573 fnd_message.set_token('INTERFACE_ID',p_race_rec.interface_race_id);
1574 fnd_message.set_token('ERROR_CD','E322');
1575
1576 l_debug_str := fnd_message.get || ' ' || SQLERRM;
1577
1578 fnd_log.string_with_context( fnd_log.level_exception,
1579 l_label,
1580 l_debug_str, NULL,
1581 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1582 END IF;
1583
1584 IF l_enable_log = 'Y' THEN
1585 igs_ad_imp_001.logerrormessage(p_race_rec.interface_race_id,'E322','IGS_PE_RACE_INT');
1586 END IF;
1587
1588 END crt_pe_race;
1589
1590 -- Local function validate_race_record
1591 FUNCTION validate_race_record(p_race_rec IN race_cur%ROWTYPE)
1592 RETURN BOOLEAN IS
1593
1594 l_exists VARCHAR2(1);
1595 l_error_code igs_pe_race_int.error_code%TYPE;
1596 BEGIN
1597 -- Call Log header
1598
1599 IF NOT(igs_pe_pers_imp_001.validate_lookup_type_code('PE_RACE',p_race_rec.race_cd,8405))
1600 THEN
1601 l_error_code := 'E580';
1602 RAISE NO_DATA_FOUND;
1603 END IF;
1604 -- CLOSE check_race;
1605
1606 --</nsidana 9/24/2003>
1607
1608 RETURN TRUE;
1609 EXCEPTION
1610 WHEN OTHERS THEN
1611
1612 UPDATE igs_pe_race_int
1613 SET status = '3',
1614 error_code = l_error_code
1615 WHERE interface_race_id = p_race_rec.interface_race_id;
1616
1617 -- Call Log detail
1618
1619 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
1620
1621 IF (l_request_id IS NULL) THEN
1622 l_request_id := fnd_global.conc_request_id;
1623 END IF;
1624
1625 l_label := 'igs.plsql.igs_ad_imp_025.prc_pe_race.exception_validate_race_record';
1626
1627 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
1628 fnd_message.set_token('INTERFACE_ID',p_race_rec.interface_race_id);
1629 fnd_message.set_token('ERROR_CD',l_error_code);
1630
1631 l_debug_str := fnd_message.get || ' ' || SQLERRM;
1632
1633 fnd_log.string_with_context( fnd_log.level_exception,
1634 l_label,
1635 l_debug_str, NULL,
1636 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1637 END IF;
1638
1639 IF l_enable_log = 'Y' THEN
1640 igs_ad_imp_001.logerrormessage(p_race_rec.interface_race_id,l_error_code,'IGS_PE_RACE_INT');
1641 END IF;
1642
1643 RETURN FALSE;
1644 END validate_race_record;
1645 -- end of local procedure
1646 -- start of main procedure prc_race
1647 BEGIN
1648
1649 l_prog_label := 'igs.plsql.igs_ad_imp_025.p_race_rec';
1650 l_label := 'igs.plsql.igs_ad_imp_025.p_race_rec.';
1651 l_enable_log := igs_ad_imp_001.g_enable_log;
1652 l_interface_run_id:=igs_ad_imp_001.g_interface_run_id; -- fetching the interface run ID from the AD imp process.
1653 -- Every child records needs to be updated with this value.
1654
1655 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
1656
1657 IF (l_request_id IS NULL) THEN
1658 l_request_id := fnd_global.conc_request_id;
1659 END IF;
1660
1661 l_label := 'igs.plsql.igs_ad_imp_025.prc_pe_race.begin';
1662 l_debug_str := 'igs_ad_imp_025.prc_pe_race';
1663
1664 fnd_log.string_with_context( fnd_log.level_procedure,
1665 l_label,
1666 l_debug_str, NULL,
1667 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1668 END IF;
1669
1670 -- <nsidana 9/25/2003 Import process enhancements.>
1671 -- Fetching the discrepency rule before the loop.
1672
1673 l_rule := igs_ad_imp_001.find_source_cat_rule(p_source_type_id, 'PERSON_TYPE');
1674
1675 -- Update all the duplicate records with status 1 and match_ind 18
1676
1677 UPDATE igs_pe_race_int pri
1678 SET status='1',
1679 match_ind='18'
1680 WHERE pri.status='2'
1681 AND pri.interface_run_id = l_interface_run_id
1682 AND EXISTS
1683 (SELECT 1
1684 FROM igs_pe_race pr,
1685 igs_ad_interface_all ai
1686 WHERE pri.interface_id=ai.interface_id
1687 AND ai.interface_run_id = l_interface_run_id
1688 AND pr.person_id=ai.person_id
1689 AND UPPER(pri.race_cd)=UPPER(pr.race_cd)
1690 );
1691
1692
1693 FOR race_rec IN race_cur(l_interface_run_id) LOOP
1694
1695 l_processed_records := l_processed_records + 1;
1696 race_rec.race_cd := UPPER(race_rec.race_cd);
1697
1698 -- duplicate check is required to ensure that two duplicate records from
1699 -- the interface table donot get inserted into the OSS table
1700 l_dup_race_cd := NULL;
1701
1702 OPEN dup_chk_race_cur(race_rec.person_id, race_rec.race_cd);
1703 FETCH dup_chk_race_cur INTO l_dup_race_cd;
1704 CLOSE dup_chk_race_cur;
1705
1706 IF l_dup_race_cd IS NULL THEN
1707 IF validate_race_record(race_rec) THEN
1708 crt_pe_race(race_rec);
1709 END IF;
1710 ELSE
1711 UPDATE igs_pe_race_int
1712 SET status = '1',
1713 match_ind = '18'
1714 WHERE interface_race_id = race_rec.interface_race_id;
1715 END IF;
1716
1717 IF l_processed_records = 100 THEN
1718 COMMIT;
1719 l_processed_records := 0;
1720 END IF;
1721 END LOOP;
1722
1723 END prc_pe_race;
1724
1725 PROCEDURE prc_priv_dtls (
1726 P_SOURCE_TYPE_ID IN NUMBER,
1727 P_BATCH_ID IN NUMBER )
1728 /*
1729 || Created By : nsidana
1730 || Created On : 9/7/2004
1731 || Purpose : This procedure is for importing person privacy details.
1732 || Known limitations, enhancements or remarks :
1733 || Change History :
1734 || Who When What
1735 || (reverse chronological order - newest change first)
1736 || nsidana 9/7/2004 Created
1737 */
1738
1739 IS
1740 l_rule VARCHAR2(1);
1741 l_error_code igs_pe_privacy_int.error_code%TYPE;
1742 l_status igs_pe_privacy_int.status%TYPE;
1743 l_default_date DATE;
1744 l_processed_records NUMBER(5) := 0;
1745
1746 l_prog_label VARCHAR2(100);
1747 l_label VARCHAR2(100);
1748 l_debug_str VARCHAR2(2000);
1749 l_enable_log VARCHAR2(1);
1750 l_request_id NUMBER;
1751 l_app VARCHAR2(50);
1752 l_message_name VARCHAR2(30);
1753 l_grp_id igs_pe_priv_level.data_group_id%TYPE;
1754 l_lvl igs_pe_priv_level.lvl%TYPE;
1755
1756 --Pick up the records for processing from the privacy details interface table.
1757 CURSOR privacy_cur(cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE)
1758 IS
1759 SELECT ai.*, i.person_id
1760 FROM igs_pe_privacy_int ai,
1761 igs_ad_interface_all i
1762 WHERE ai.interface_id = i.interface_id AND
1763 ai.status = '2' AND
1764 i.interface_run_id = cp_interface_run_id AND
1765 ai.interface_run_id = cp_interface_run_id;
1766
1767 --Cursor to check whether the record in interface table already exists in OSS table
1768 CURSOR chk_dup_privacy_cur(cp_person_id igs_pe_priv_level.person_id%TYPE,
1769 cp_data_group igs_pe_priv_level.data_group%TYPE,
1770 cp_start_dt igs_pe_priv_level.start_date%TYPE)
1771 IS
1772 SELECT p.rowid, p.*
1773 FROM igs_pe_priv_level p
1774 WHERE p.person_id = cp_person_id AND
1775 p.data_group = cp_data_group AND
1776 p.start_date = cp_start_dt;
1777
1778 privacy_cur_rec privacy_cur%ROWTYPE;
1779 chk_dup_privacy_cur_rec chk_dup_privacy_cur%ROWTYPE;
1780
1781 -- Local procedure crt_pe_priv_dtls for inserting new records in the OSS table.
1782 PROCEDURE crt_pe_priv_dtls(p_priv_rec IN privacy_cur%ROWTYPE,
1783 p_grp_id IN igs_pe_priv_level.data_group_id%TYPE)
1784 /*
1785 || Created By : nsidana
1786 || Created On : 9/7/2004
1787 || Purpose : Local procedure to create a new privacy record.
1788 || Known limitations, enhancements or remarks :
1789 || Change History :
1790 || Who When What
1791 || (reverse chronological order - newest change first)
1792 || nsidana 9/7/2004 Created
1793 */
1794 AS
1795 l_rowid VARCHAR2(25);
1796 l_privacy_level_id igs_pe_priv_level.privacy_level_id%TYPE;
1797 l_error_code igs_pe_privacy_int.error_code%TYPE;
1798 l_org_id NUMBER(15);
1799
1800 BEGIN
1801 SAVEPOINT before_insert;
1802 -- Call log header
1803 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
1804
1805 IF (l_request_id IS NULL) THEN
1806 l_request_id := fnd_global.conc_request_id;
1807 END IF;
1808
1809 l_label := 'igs.plsql.igs_ad_imp_025.prc_priv_dtls.begin_crt_pe_priv_dtls';
1810 l_debug_str := 'igs_ad_imp_025.prc_priv_dtls.crt_pe_priv_dtls';
1811
1812 fnd_log.string_with_context( fnd_log.level_procedure,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1813 END IF;
1814
1815 igs_pe_priv_level_pkg.insert_row(
1816 x_rowid => l_rowid,
1817 x_privacy_level_id => l_privacy_level_id,
1818 x_person_id => p_priv_rec.person_id,
1819 x_data_group => p_priv_rec.data_group,
1820 x_data_group_id => p_grp_id,
1821 x_lvl => null,
1822 x_action => p_priv_rec.action_code,
1823 x_whom => p_priv_rec.to_whom_code,
1824 x_ref_notes_id => null,
1825 x_start_date => p_priv_rec.start_date,
1826 x_end_date => p_priv_rec.end_date,
1827 x_mode => 'R'
1828 );
1829 -- Update interface table for successful insertion.
1830 l_error_code := NULL;
1831 UPDATE igs_pe_privacy_int
1832 SET status = '1',
1833 error_code = l_error_code
1834 WHERE interface_privacy_id = p_priv_rec.interface_privacy_id;
1835 EXCEPTION
1836 WHEN OTHERS THEN
1837 ROLLBACK TO before_insert;
1838 -- Catch the exceptions from TBH if any.
1839 FND_MESSAGE.PARSE_ENCODED(FND_MESSAGE.GET_ENCODED, l_app, l_message_name);
1840
1841 IF (l_message_name = 'IGS_PE_FROM_DT_GRT_TO_DATE')
1842 THEN
1843 l_error_code := 'E406';
1844 ELSIF (l_message_name = 'IGS_FI_ST_NOT_LT_CURRDT')
1845 THEN
1846 l_error_code := 'E352';
1847 ELSIF (l_message_name = 'IGS_PE_PRIV_DT_OVERLAP')
1848 THEN
1849 l_error_code := 'E228';
1850 ELSE
1851 l_error_code := 'E322'; -- Person privacy details record insertion failed.
1852 END IF;
1853 UPDATE igs_pe_privacy_int
1854 SET status = '3',
1855 error_code = l_error_code
1856 WHERE interface_privacy_id = p_priv_rec.interface_privacy_id;
1857
1858 -- Call Log detail
1859 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
1860
1861 IF (l_request_id IS NULL) THEN
1862 l_request_id := fnd_global.conc_request_id;
1863 END IF;
1864
1865 l_label := 'igs.plsql.igs_ad_imp_025.crt_pe_priv_dtls.exception';
1866
1867 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
1868 fnd_message.set_token('INTERFACE_ID',p_priv_rec.interface_privacy_id);
1869 fnd_message.set_token('ERROR_CD',l_error_code);
1870
1871 l_debug_str := fnd_message.get || ' ' || SQLERRM;
1872
1873 fnd_log.string_with_context( fnd_log.level_exception,l_label,l_debug_str,NULL,NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1874 END IF;
1875
1876 IF l_enable_log = 'Y' THEN
1877 igs_ad_imp_001.logerrormessage(p_priv_rec.interface_privacy_id,l_error_code);
1878 END IF;
1879
1880 END crt_pe_priv_dtls; -- End of local procedure to create privacy record.
1881
1882 -- Local procedure to update a record in the OSS table.
1883 PROCEDURE upd_pe_priv_dtls(p_oss_rec IN chk_dup_privacy_cur%ROWTYPE,
1884 p_int_rec IN privacy_cur%ROWTYPE)
1885 /*
1886 || Created By : nsidana
1887 || Created On : 9/7/2004
1888 || Purpose : Local procedure to update an existing privacy record.
1889 || Known limitations, enhancements or remarks :
1890 || Change History :
1891 || Who When What
1892 || (reverse chronological order - newest change first)
1893 || nsidana 9/7/2004 Created
1894 */
1895 AS
1896
1897 BEGIN
1898 SAVEPOINT before_update;
1899
1900 igs_pe_priv_level_pkg.update_row (x_rowid => p_oss_rec.rowid,
1901 x_privacy_level_id => p_oss_rec.privacy_level_id,
1902 x_person_id => p_oss_rec.person_id,
1903 x_data_group => p_oss_rec.data_group,
1904 x_data_group_id => p_oss_rec.data_group_id,
1905 x_lvl => p_oss_rec.lvl,
1906 x_action => p_int_rec.action_code,
1907 x_whom => p_int_rec.to_whom_code,
1908 x_ref_notes_id => p_oss_rec.ref_notes_id,
1909 x_start_date => p_oss_rec.start_date,
1910 x_end_date => NVL(p_int_rec.end_date,p_oss_rec.end_date),
1911 x_mode => 'R'
1912 );
1913 l_error_code := NULL;
1914 l_status := '1';
1915 UPDATE igs_pe_privacy_int
1916 SET status = l_status,
1917 error_code = l_error_code,
1918 match_ind = '18' -- '18' Match occured and used import values
1919 WHERE interface_privacy_id = p_int_rec.interface_privacy_id;
1920 EXCEPTION
1921 WHEN OTHERS THEN
1922 ROLLBACK TO before_update;
1923
1924 -- Catch the exceptions from TBH if any.
1925 FND_MESSAGE.PARSE_ENCODED(FND_MESSAGE.GET_ENCODED, l_app, l_message_name);
1926
1927 IF (l_message_name = 'IGS_PE_FROM_DT_GRT_TO_DATE')
1928 THEN
1929 l_error_code := 'E406';
1930 ELSIF (l_message_name = 'IGS_FI_ST_NOT_LT_CURRDT')
1931 THEN
1932 l_error_code := 'E352';
1933 ELSIF (l_message_name = 'IGS_PE_PRIV_DT_OVERLAP')
1934 THEN
1935 l_error_code := 'E228';
1936 ELSE
1937 l_error_code := 'E014';
1938 END IF;
1939 UPDATE igs_pe_privacy_int
1940 SET status = '3',
1941 error_code = l_error_code
1942 WHERE interface_id = p_int_rec.interface_privacy_id;
1943 -- Call Log detail
1944
1945 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
1946
1947 IF (l_request_id IS NULL) THEN
1948 l_request_id := fnd_global.conc_request_id;
1949 END IF;
1950
1951 l_label := 'igs.plsql.igs_ad_imp_025.prc_priv_dtls.exception1';
1952
1953 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
1954 fnd_message.set_token('INTERFACE_ID',p_int_rec.interface_privacy_id);
1955 fnd_message.set_token('ERROR_CD',l_error_code);
1956
1957 l_debug_str := fnd_message.get || ' ' || SQLERRM;
1958
1959 fnd_log.string_with_context( fnd_log.level_exception,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1960 END IF;
1961
1962 IF l_enable_log = 'Y' THEN
1963 igs_ad_imp_001.logerrormessage(p_int_rec.interface_privacy_id,l_error_code);
1964 END IF;
1965 END upd_pe_priv_dtls;
1966
1967 -- Local function to validate the record in the interfce table.
1968 FUNCTION validate_record(p_priv_rec IN privacy_cur%ROWTYPE,
1969 p_mode IN VARCHAR2,
1970 p_group_id OUT NOCOPY igs_pe_priv_level.data_group_id%TYPE)
1971 RETURN BOOLEAN
1972 /*
1973 || Created By : nsidana
1974 || Created On : 9/7/2004
1975 || Purpose : Local function to validate the privacy record.
1976 || Known limitations, enhancements or remarks :
1977 || Change History :
1978 || Who When What
1979 || (reverse chronological order - newest change first)
1980 || nsidana 9/7/2004 Created
1981 */
1982 IS
1983 CURSOR chk_data_group_for_ins(cp_data_group igs_pe_priv_level.data_group%TYPE)
1984 IS
1985 SELECT lvl, data_group_id
1986 FROM igs_pe_data_groups
1987 WHERE data_group = cp_data_group AND
1988 closed_ind = 'N';
1989
1990 CURSOR chk_data_group_for_upd(cp_data_group igs_pe_priv_level.data_group%TYPE)
1991 IS
1992 SELECT lvl, data_group_id
1993 FROM igs_pe_data_groups
1994 WHERE data_group = cp_data_group;
1995
1996 CURSOR chk_to_whom_code(cp_person_id privacy_cur_rec.person_id%TYPE,cp_to_whom_code privacy_cur_rec.to_whom_code%TYPE)
1997 IS
1998 SELECT 1
1999 FROM FND_LOOKUP_VALUES L,
2000 HZ_RELATIONSHIPS R
2001 WHERE L.LOOKUP_CODE = R.RELATIONSHIP_CODE AND
2002 L.LOOKUP_TYPE = 'PARTY_RELATIONS_TYPE' AND
2003 L.LANGUAGE = USERENV('LANG') AND
2004 L.VIEW_APPLICATION_ID = 222 AND
2005 L.SECURITY_GROUP_ID = 0 AND
2006 R.STATUS ='A' AND
2007 R.RELATIONSHIP_CODE = cp_to_whom_code AND
2008 R.SUBJECT_ID = cp_person_id;
2009
2010 l_error_code igs_pe_privacy_int.error_code%TYPE;
2011 l_rec VARCHAR2(1);
2012 l_exists NUMBER;
2013 chk_data_group_for_ins_rec chk_data_group_for_ins%ROWTYPE;
2014 chk_data_group_for_upd_rec chk_data_group_for_upd%ROWTYPE;
2015
2016 BEGIN
2017
2018 --1.) Check a valid data group. Consider closed ones as invalid for Insert mode and valid for update mode.
2019 IF ( p_mode = 'I') THEN
2020
2021 OPEN chk_data_group_for_ins(p_priv_rec.data_group);
2022 FETCH chk_data_group_for_ins INTO chk_data_group_for_ins_rec;
2023
2024 IF (chk_data_group_for_ins%NOTFOUND) THEN
2025 l_error_code := 'E351'; -- not a valid data group code.
2026 CLOSE chk_data_group_for_ins;
2027 RAISE NO_DATA_FOUND;
2028 ELSE
2029 p_group_id := chk_data_group_for_ins_rec.data_group_id;
2030 END IF;
2031 CLOSE chk_data_group_for_ins;
2032
2033 ELSIF (p_mode = 'U' ) THEN
2034 OPEN chk_data_group_for_upd(p_priv_rec.data_group);
2035 FETCH chk_data_group_for_upd INTO chk_data_group_for_upd_rec;
2036
2037 IF (chk_data_group_for_upd%NOTFOUND) THEN
2038 l_error_code := 'E351'; -- not a valid data group code.
2039 CLOSE chk_data_group_for_upd;
2040 RAISE NO_DATA_FOUND;
2041 END IF;
2042 CLOSE chk_data_group_for_upd;
2043
2044 END IF;
2045
2046 --2.) Check the action code be a valid lookup code.
2047 IF (NOT igs_pe_pers_imp_001.validate_lookup_type_code('PERSON_PRIVACY_ACTION',p_priv_rec.action_code,8405)) THEN
2048 l_error_code := 'E353'; -- not a valid ACTION_CODE
2049 RAISE NO_DATA_FOUND;
2050 END IF;
2051
2052 --3.) Validate TO_WHOM_CODE column in the interface table. Call igs_pe_pers_imp_001.validate_lookup_type_code. If not validated, check in HZ_RELATIONSHIPS.
2053
2054 IF (NOT igs_pe_pers_imp_001.validate_lookup_type_code('PERSON_PRIVACY_RELEASE',p_priv_rec.to_whom_code,8405))
2055 THEN
2056 l_exists := null;
2057 OPEN chk_to_whom_code(p_priv_rec.person_id,p_priv_rec.to_whom_code);
2058 FETCH chk_to_whom_code INTO l_exists;
2059 CLOSE chk_to_whom_code;
2060 IF (l_exists IS NULL)
2061 THEN
2062 l_error_code := 'E354'; -- not a valid TO_WHOM relation.
2063 RAISE NO_DATA_FOUND;
2064 END IF;
2065 END IF;
2066
2067 --4.) Need to handle the following in the EXCPETION section of insert_row and update_row. These will be caught in the exception secion of the insert_row and update_row calls.
2068 --E406 : IGS_PE_FROM_DT_GRT_TO_DATE :Start date not greater than end date.
2069 --E352 : IGS_FI_ST_NOT_LT_CURRDT : Start date not less than current date.
2070 --E228 : IGS_PE_PRIV_DT_OVERLAP : Overlap validation.
2071 RETURN TRUE;
2072
2073 EXCEPTION
2074 WHEN OTHERS THEN
2075 UPDATE igs_pe_privacy_int
2076 SET status = '3',
2077 error_code = l_error_code
2078 WHERE interface_privacy_id = p_priv_rec.interface_privacy_id;
2079
2080 -- Call Log detail
2081
2082 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
2083
2084 IF (l_request_id IS NULL) THEN
2085 l_request_id := fnd_global.conc_request_id;
2086 END IF;
2087
2088 l_label := 'igs.plsql.igs_ad_imp_025.prc_priv_dtls.exception_validate_record';
2089 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
2090 fnd_message.set_token('INTERFACE_ID',p_priv_rec.interface_privacy_id);
2091 fnd_message.set_token('ERROR_CD',l_error_code);
2092 l_debug_str := fnd_message.get || ' ' || SQLERRM;
2093 fnd_log.string_with_context( fnd_log.level_exception,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
2094
2095 END IF;
2096
2097 IF l_enable_log = 'Y' THEN
2098 igs_ad_imp_001.logerrormessage(p_priv_rec.interface_privacy_id,l_error_code);
2099 END IF;
2100 RETURN FALSE;
2101
2102 END validate_record; -- End of local function.
2103
2104 BEGIN -- Main procedure for importing privacy details.
2105
2106 l_default_date := IGS_GE_DATE.IGSDATE('4712/12/31');
2107 l_prog_label := 'igs.plsql.igs_ad_imp_025.prc_priv_dtls';
2108 l_label := 'igs.plsql.igs_ad_imp_025.prc_priv_dtls.';
2109 l_enable_log := igs_ad_imp_001.g_enable_log;
2110 l_interface_run_id:=igs_ad_imp_001.g_interface_run_id; -- fetching the interface run ID from the AD imp process.
2111
2112 l_rule := igs_ad_imp_001.find_source_cat_rule(p_source_type_id=>P_SOURCE_TYPE_ID,p_category=>'PRIVACY_DETAILS');
2113
2114 -- 1. If the rule is E or I, and the match ind column is not null, update all the records to status 3 as they are invalids.
2115
2116 IF ((l_rule='E') OR (l_rule='I')) THEN
2117 UPDATE igs_pe_privacy_int phi
2118 SET status = '3',
2119 error_code = 'E695'
2120 WHERE phi.status = '2' AND
2121 phi.interface_run_id = l_interface_run_id AND
2122 phi.match_ind IS NOT NULL;
2123 END IF;
2124
2125 -- 2 . If rule is E and the match ind is null, we update the interface table for all duplicate records with status 1 and match ind 19.
2126
2127 IF (l_rule = 'E') THEN
2128 UPDATE igs_pe_privacy_int phi
2129 SET status = '1',
2130 match_ind = '19'
2131 WHERE phi.status = '2' AND
2132 phi.interface_run_id = l_interface_run_id AND
2133 EXISTS
2134 (SELECT 1
2135 FROM igs_pe_priv_level pi, igs_ad_interface_all ai
2136 WHERE phi.interface_id = ai.interface_id AND
2137 ai.interface_run_id = l_interface_run_id AND
2138 ai.person_id = pi.person_id AND
2139 pi.data_group = UPPER(phi.data_group) AND
2140 TRUNC(phi.start_date) = pi.start_date);
2141 END IF;
2142
2143 -- 3. If rule is R and the record status is 18,19,22,23 these records have been processed, but didn't get updated. Update them to 1
2144
2145 IF (l_rule='R') THEN
2146 UPDATE igs_pe_privacy_int phi
2147 SET status = '1'
2148 WHERE phi.status = '2' AND
2149 phi.interface_run_id = l_interface_run_id AND
2150 phi.match_ind IN ('18','19','22','23');
2151 END IF;
2152
2153 -- 4. If rule is R and the match ind is not null and is neither 21 nor 25, update it to errored record.
2154
2155 IF (l_rule = 'R') THEN
2156 UPDATE igs_pe_privacy_int phi
2157 SET status = '3', error_code = 'E695'
2158 WHERE phi.status = '2' AND
2159 phi.interface_run_id = l_interface_run_id AND
2160 (phi.match_ind IS NOT NULL AND phi.match_ind NOT IN ('21','25'));
2161 END IF;
2162
2163 -- 5. If rule = 'R' and there is no discprepency in duplicate records, update them to status 1 and match ind 23.
2164
2165 IF (l_rule ='R') THEN
2166 UPDATE igs_pe_privacy_int phi
2167 SET status = '1', match_ind = '23'
2168 WHERE phi.status = '2' AND
2169 phi.interface_run_id = l_interface_run_id AND
2170 phi.match_ind IS NULL AND
2171 EXISTS
2172 (SELECT 1
2173 FROM igs_pe_priv_level pi, igs_ad_interface_all ai
2174 WHERE phi.interface_id = ai.interface_id AND
2175 ai.interface_run_id = l_interface_run_id AND
2176 pi.person_id = ai.person_id AND
2177 pi.data_group = UPPER(phi.data_group) AND
2178 pi.action = UPPER(phi.action_code) AND
2179 pi.whom = UPPER(phi.to_whom_code) AND
2180 pi.start_date = TRUNC(phi.start_date) AND
2181 NVL(TRUNC(pi.end_date), l_default_date) = NVL(TRUNC(phi.end_date),l_default_date)
2182 ) ;
2183 END IF;
2184
2185 -- 6. If rule is R and there are still some records, they are the ones for which there is some discrepency existing. Update them to status 3
2186 -- and value from the OSS table.
2187
2188 IF (l_rule ='R') THEN
2189 UPDATE igs_pe_privacy_int phi
2190 SET status = 3,
2191 match_ind = 20,
2192 dup_privacy_level_id = (SELECT pi.privacy_level_id
2193 FROM igs_pe_priv_level pi, igs_ad_interface_all ai
2194 WHERE ai.interface_id = phi.interface_id AND
2195 ai.interface_run_id = l_interface_run_id AND
2196 ai.person_id = pi.person_id AND
2197 UPPER(phi.data_group) = pi.data_group AND
2198 TRUNC(phi.start_date) = pi.start_date)
2199 WHERE phi.status='2' AND
2200 phi.interface_run_id = l_interface_run_id AND
2201 phi.match_ind IS NULL AND
2202 EXISTS
2203 (SELECT 1
2204 FROM igs_pe_priv_level pi, igs_ad_interface_all ai
2205 WHERE ai.interface_run_id = l_interface_run_id AND
2206 ai.interface_id = phi.interface_id AND
2207 ai.person_id = pi.person_id AND
2208 UPPER(phi.data_group) = pi.data_group AND
2209 TRUNC(phi.start_date) = pi.start_date
2210 );
2211 END IF;
2212
2213 -- process the remanining records.
2214 FOR privacy_cur_rec IN privacy_cur(l_interface_run_id)
2215 LOOP
2216 privacy_cur_rec.start_date := TRUNC(privacy_cur_rec.start_date);
2217 privacy_cur_rec.end_date := TRUNC(privacy_cur_rec.end_date);
2218 privacy_cur_rec.data_group := UPPER(privacy_cur_rec.data_group);
2219 privacy_cur_rec.action_code := UPPER(privacy_cur_rec.action_code);
2220 privacy_cur_rec.to_whom_code := UPPER(privacy_cur_rec.to_whom_code);
2221
2222 l_processed_records := l_processed_records + 1;
2223
2224 chk_dup_privacy_cur_rec.privacy_level_id := NULL;
2225
2226 OPEN chk_dup_privacy_cur(privacy_cur_rec.person_id,privacy_cur_rec.data_group,privacy_cur_rec.start_date);
2227 FETCH chk_dup_privacy_cur INTO chk_dup_privacy_cur_rec;
2228 CLOSE chk_dup_privacy_cur;
2229
2230 IF (chk_dup_privacy_cur_rec.privacy_level_id IS NOT NULL) -- Matching record exists.
2231 THEN
2232 IF ((l_rule = 'I') OR ((l_rule = 'R') AND (privacy_cur_rec.match_ind = '21')))
2233 THEN
2234 IF validate_record(privacy_cur_rec,'U',l_grp_id) THEN
2235 upd_pe_priv_dtls(p_oss_rec => chk_dup_privacy_cur_rec,p_int_rec => privacy_cur_rec);
2236 END IF; -- end for validate for update
2237 END IF;
2238
2239 ELSE -- matching record does not exists.
2240
2241 -- validate and insert new.
2242 IF validate_record(privacy_cur_rec,'I',l_grp_id) THEN
2243 crt_pe_priv_dtls(p_priv_rec => privacy_cur_rec, p_grp_id => l_grp_id);
2244 END IF;
2245
2246 END IF; -- for duplicate record check.
2247
2248 IF l_processed_records = 100 THEN
2249 COMMIT;
2250 l_processed_records := 0;
2251 END IF;
2252 END LOOP;
2253 END prc_priv_dtls;
2254
2255 END igs_ad_imp_025;
[Home] [Help]