1 PACKAGE BODY IGS_AD_IMP_012 AS
2 /* $Header: IGSAD90B.pls 120.2 2006/01/25 09:23:00 skpandey noship $ */
3 /*
4 || Change History :
5 || Who When What
6
7 || ssaleem 13_OCT_2003 Bug : 3130316
8 || Logging is modified to include logging mechanism
9 || asbala 28-SEP-2003 Bug 3130316. Import Process Source Category Rule processing changes,
10 lookup caching related changes, and cursor parameterization.
11 || npalanis 6-JAN-2003 Bug : 2734697
12 || code added to commit after import of every
13 || 100 records .New variable l_processed_records added
14 || gmuralid 26-NOV-2002 BUG 2466674 - V2API UPTAKE
15 changed reference of HZ_PER_INFO_PUB to HZ_PERSON_INFO_V2PUB AND
16 HZ_CONTACT_POINT_PUB TO HZ_CONTACT_POINT_V2PUB for create and update of
17 person language and contact points
18
19 ssawhney 27 may BUG - 2377751, error codes modified from E008 for contacts.
20 || npalanis 9-may-2002 BUG - 2352725
21 || Dupcontact point id value is set
22 || and also bug - 2338473 for messages is changed.
23 || npalanis 6-may-2002 Bug - 2352725
24 || * The contact point type in the interface table is made not
25 || null and check is added that the contact point type must be PHONE or
26 || EMAIL.
27 || * IF check is added to see that whether the contact point type
28 || is PHONE or EMAIL before and the respective attributes are populated
29 || before creating or updating contact points.
30 || * In dup check cursor contact point type = 'PHONE' check is
31 || made.
32 || * Validate procedure is added to validate contact point type,
33 || phone line type,phone country code , email format from fnd lookups.
34 || * If check is added in validate proc to check that email address,
35 || email format cannot be null when contact point type is 'EMAIl' and
36 || phone number , phone line type cannot be null when contact point type is
37 || 'PHONE'.
38 || * The contact point type check and phone line type check in
39 || create and update contact point proc is removed.
40 || * Cursor C1 fetches records based on contact point ID =
41 || igs_ad_contacts_int.interface_contacts_id it is changed to
42 || contact point ID = l_contact_point_id from hz_contact_points.
43 || ssawhney 15 nov Bug no.2103692:Person Interface DLD
44 || prc_pe_citizenship code is removed from here and added to
45 || IGS_AD_IMP_007.
46 || gmaheswa 11 Nov 2003 Bug 3223043 HZ.K Impact Changes
47 */
48
49 cst_mi_val_18 CONSTANT VARCHAR2(2) := '18';
50 cst_mi_val_19 CONSTANT VARCHAR2(2) := '19';
51 cst_mi_val_20 CONSTANT VARCHAR2(2) := '20';
52 cst_mi_val_21 CONSTANT VARCHAR2(2) := '21';
53 cst_mi_val_22 CONSTANT VARCHAR2(2) := '22';
54 cst_mi_val_23 CONSTANT VARCHAR2(2) := '23';
55 cst_mi_val_24 CONSTANT VARCHAR2(2) := '24';
56 cst_mi_val_25 CONSTANT VARCHAR2(2) := '25';
57
58 cst_stat_val_1 CONSTANT VARCHAR2(1) := '1';
59 cst_stat_val_2 CONSTANT VARCHAR2(1) := '2';
60 cst_stat_val_3 CONSTANT VARCHAR2(1) := '3';
61
62 cst_err_val_246 CONSTANT VARCHAR2(4) := 'E246';
63 cst_err_val_695 CONSTANT VARCHAR2(4) := 'E695';
64 cst_err_val_014 CONSTANT VARCHAR2(4) := 'E014';
65
66 PROCEDURE prc_pe_cntct_dtls (
67 p_source_type_id IN NUMBER,
68 p_batch_id IN NUMBER )
69 AS
70
71 l_prog_label VARCHAR2(100);
72 l_label VARCHAR2(100);
73 l_debug_str VARCHAR2(2000);
74 l_enable_log VARCHAR2(1);
75 l_request_id NUMBER;
76
77
78 CURSOR c_pc(cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE) IS
79 SELECT ai.interface_contacts_id,
80 ai.interface_id ai_interface_id,
81 UPPER(ai.contact_point_type) contact_point_type,
82 ai.email_address,
83 UPPER(ai.email_format) email_format,
84 UPPER(ai.primary_flag) primary_flag,
85 UPPER(ai.phone_line_type) phone_line_type,
86 ai.phone_country_code,
87 ai.phone_area_code,
88 ai.phone_number,
89 ai.phone_extension,
90 ai.status ai_status,
91 ai.match_ind ai_match_ind,
92 ai.error_code ai_error_code,
93 ai.dup_contact_point_id,
94 ai.created_by,
95 ai.creation_date,
96 ai.last_updated_by,
97 ai.last_update_date,
98 ai.last_update_login,
99 ai.request_id,
100 ai.program_application_id,
101 ai.program_id,
102 ai.program_update_date,
103 i.interface_id i_interface_id,
104 i.person_id i_person_id,
105 i.match_ind i_match_ind
106 FROM igs_ad_contacts_int_all ai, igs_ad_interface_all i
107 WHERE ai.interface_run_id = cp_interface_run_id
108 AND i.interface_id = ai.interface_id
109 AND i.interface_run_id = cp_interface_run_id
110 AND ai.status = '2';
111
112 l_var VARCHAR2(1);
113 l_rule VARCHAR2(1);
114 l_error_code VARCHAR2(25);
115 l_status VARCHAR2(25);
116 l_dup_var BOOLEAN;
117 l_check VARCHAR2(10);
118 l_contact_point_id igs_ad_contacts_int.dup_contact_point_id%TYPE;
119 rec_pc c_pc%ROWTYPE;
120 l_processed_records NUMBER(5) := 0 ;
121 -- local variable to store the value of global variable igs_ad_imp_001.g_interface_run_id
122 l_interface_run_id igs_ad_interface_all.interface_run_id%TYPE;
123 PROCEDURE crt_prsn_contacts(rec_pc c_pc%ROWTYPE ,
124 error_code OUT NOCOPY VARCHAR2,
125 status OUT NOCOPY VARCHAR2) AS
126 l_update_date1 DATE;
127 l_return_status VARCHAR2(25);
128 l_msg_count NUMBER;
129 l_msg_data VARCHAR2(4000);
130 l_smp VARCHAR2(25);
131 l_smp1 VARCHAR2(25);
132 p_error_code VARCHAR2(25);
133 p_status VARCHAR2(25);
134 p_contact_points_rec HZ_CONTACT_POINT_V2PUB.contact_point_rec_type;
135 p_email_rec HZ_CONTACT_POINT_V2PUB.email_rec_type;
136 p_phone_rec HZ_CONTACT_POINT_V2PUB.phone_rec_type;
137
138 l_tmp_var1 VARCHAR2(500);
139 l_tmp_var VARCHAR2(500);
140 BEGIN
141
142 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
143
144 IF (l_request_id IS NULL) THEN
145 l_request_id := fnd_global.conc_request_id;
146 END IF;
147
148 l_label := 'igs.plsql.igs_ad_imp_012.prc_pe_cntct_dtls.begin_crt_prsn_contacts';
149 l_debug_str := 'Igs_Ad_Imp_012.crt_prsn_contacts';
150
151 fnd_log.string_with_context( fnd_log.level_procedure,
152 l_label,
153 l_debug_str, NULL,
154 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
155 END IF;
156
157 p_contact_points_rec.contact_point_type := rec_pc.contact_point_type;
158 -- commented for bug fix for bug#1606314
159 -- p_contact_points_rec.status := rec_pc.ai_status;
160 p_contact_points_rec.owner_table_name := 'HZ_PARTIES';
161 p_contact_points_rec.owner_table_id := rec_pc.i_person_id;
162 p_contact_points_rec.primary_flag := nvl(rec_pc.primary_flag,'N');
163 p_contact_points_rec.content_source_type := 'USER_ENTERED';
164 p_contact_points_rec.created_by_module := 'IGS';
165
166 IF rec_pc.contact_point_type = 'EMAIL' THEN
167 p_email_rec.email_format := rec_pc.email_format;
168 p_email_rec.email_address := rec_pc.email_address;
169 END IF;
170
171 IF rec_pc.contact_point_type = 'PHONE' THEN
172 p_phone_rec.phone_area_code := rec_pc.phone_area_code;
173 p_phone_rec.phone_country_code := rec_pc.phone_country_code;
174 p_phone_rec.phone_number := rec_pc.phone_number;
175 p_phone_rec.phone_extension := rec_pc.phone_extension;
176 p_phone_rec.phone_line_type := rec_pc.phone_line_type;
177 END IF;
178
179 HZ_CONTACT_POINT_V2PUB.create_contact_point(
180 p_init_msg_list => FND_API.G_FALSE,
181 p_contact_point_rec => p_contact_points_rec,
182 p_email_rec => p_email_rec,
183 p_phone_rec => p_phone_rec,
184 x_return_status => l_return_status,
185 x_msg_count => l_msg_count,
186 x_msg_data => l_msg_data,
187 x_contact_point_id => l_contact_point_id
188 );
189 IF l_return_status IN ('E','U') THEN
190
191 IF l_msg_count > 1 THEN
192 FOR i IN 1..l_msg_count
193 LOOP
194 l_tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
195 l_tmp_var1 := l_tmp_var1 || ' '|| l_tmp_var;
196 END LOOP;
197 l_msg_data := l_tmp_var1;
198 END IF;
199
200 l_error_code := 'E322';
201 l_status := '3'; ---check with the existinf error codes
202
203
204 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
205
206 IF (l_request_id IS NULL) THEN
207 l_request_id := fnd_global.conc_request_id;
208 END IF;
209
210 l_label := 'igs.plsql.igs_ad_imp_012.prc_pe_cntct_dtls.exception_crt_prsn_contacts';
211
212 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
213 fnd_message.set_token('INTERFACE_ID',rec_pc.interface_contacts_id);
214 fnd_message.set_token('ERROR_CD','E322');
215
216 l_debug_str := fnd_message.get || ' ' || l_msg_data;
217
218 fnd_log.string_with_context( fnd_log.level_exception,
219 l_label,
220 l_debug_str, NULL,
221 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
222 END IF;
223
224 IF l_enable_log = 'Y' THEN
225 igs_ad_imp_001.logerrormessage(rec_pc.interface_contacts_id,'E322');
226 END IF;
227
228 UPDATE igs_ad_contacts_int_all
229 SET error_code='E322',status='3'
230 WHERE interface_contacts_id=rec_pc.interface_contacts_id;
231
232 ELSE
233
234 l_status := '1';
235 UPDATE igs_ad_contacts_int_all
236 SET status='1'
237 WHERE interface_contacts_id=rec_pc.interface_contacts_id;
238
239 END IF;
240 EXCEPTION
241 WHEN OTHERS THEN
242
243 p_error_Code:= 'E322';
244 p_status:= '3';
245
246 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
247
248 IF (l_request_id IS NULL) THEN
249 l_request_id := fnd_global.conc_request_id;
250 END IF;
251
252 l_label := 'igs.plsql.igs_ad_imp_012.prc_pe_cntct_dtls.exception_crt_prsn_contacts';
253
254 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
255 fnd_message.set_token('INTERFACE_ID',rec_pc.interface_contacts_id);
256 fnd_message.set_token('ERROR_CD','E322');
257
258 l_debug_str := fnd_message.get || ' ' || l_msg_data;
259
260 fnd_log.string_with_context( fnd_log.level_exception,
261 l_label,
262 l_debug_str, NULL,
263 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
264 END IF;
265
266 IF l_enable_log = 'Y' THEN
267 igs_ad_imp_001.logerrormessage(rec_pc.interface_contacts_id,'E322');
268 END IF;
269
270 UPDATE igs_ad_contacts_int_all
271 SET error_code='E322',status='3'
272 WHERE interface_contacts_id=rec_pc.interface_contacts_id;
273
274 END crt_prsn_contacts;
275
276 PROCEDURE validate_prsn_contacts(c_pc_rec c_pc%ROWTYPE,l_Check OUT NOCOPY VARCHAR2 ) AS
277
278 -- 4. phone country code is now to be validated against
279 -- HZ_PHONE_COUNTRY_CODEs : HZ F validations -- ssawhney bug 2203778
280 CURSOR c_ph_cntry_cd (p_phone_country_code VARCHAR2) IS
281 SELECT 'X'
282 FROM HZ_PHONE_COUNTRY_CODES
283 WHERE PHONE_COUNTRY_CODE = p_phone_country_code;
284
285 l_dummy VARCHAR2(1);
286
287 BEGIN
288
289 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
290
291 IF (l_request_id IS NULL) THEN
292 l_request_id := fnd_global.conc_request_id;
293 END IF;
294
295 l_label := 'igs.plsql.igs_ad_imp_012.prc_pe_cntct_dtls.begin_validate_prsn_contacts';
296 l_debug_str := 'Igs_Ad_Imp_012.validate_prsn_contacts';
297
298 fnd_log.string_with_context( fnd_log.level_procedure,
299 l_label,
300 l_debug_str, NULL,
301 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
302 END IF;
303
304 IF NOT
305 (igs_pe_pers_imp_001.validate_lookup_type_code('COMMUNICATION_TYPE',c_pc_rec.contact_point_type,222))
306 THEN
307 -- If the validation is not successful.
308
309 IF l_enable_log = 'Y' THEN
310 igs_ad_imp_001.logerrormessage(c_pc_rec.interface_contacts_id,'E246');
311 END IF;
312
313 UPDATE igs_ad_contacts_int_all
314 SET status = cst_stat_val_3,
315 error_code = cst_err_val_246,
316 match_ind = cst_mi_val_18
317 WHERE interface_contacts_id = c_pc_rec.interface_contacts_id;
318 l_Check := 'TRUE';
319 RETURN;
320 END IF;
321
322 IF c_pc_rec.primary_flag IS NOT NULL THEN
323 IF c_pc_rec.primary_flag NOT IN ('N','Y') THEN
324
325 IF l_enable_log = 'Y' THEN
326 igs_ad_imp_001.logerrormessage(c_pc_rec.interface_contacts_id,'E450');
327 END IF;
328
329 UPDATE igs_ad_contacts_int_all
330 SET error_code = 'E450',
331 status = '3'
332 WHERE interface_contacts_id = c_pc_rec.interface_contacts_id;
333 l_Check := 'TRUE';
334 RETURN;
335 END IF;
336 END IF;
337
338 IF c_pc_rec.contact_point_type = 'PHONE' THEN
339 -- Validation to check whether phone line type or phone number are null
340 IF c_pc_rec.phone_number IS NULL OR c_pc_rec.phone_line_type IS NULL THEN
341
342 IF l_enable_log = 'Y' THEN
343 igs_ad_imp_001.logerrormessage(c_pc_rec.interface_contacts_id,'E250');
344 END IF;
345
346 UPDATE igs_ad_contacts_int_all
347 SET error_code = 'E250',
348 status = '3'
349 WHERE interface_contacts_id = c_pc_rec.interface_contacts_id;
350 l_Check := 'TRUE';
351 RETURN;
352 END IF;
353
354 IF NOT
355 (igs_pe_pers_imp_001.validate_lookup_type_code('PHONE_LINE_TYPE',c_pc_rec.phone_line_type,222))
356 THEN
357 -- If the validation is not successful.
358
359 IF l_enable_log = 'Y' THEN
360 igs_ad_imp_001.logerrormessage(c_pc_rec.interface_contacts_id,'E247');
361 END IF;
362
363 UPDATE igs_ad_contacts_int_all
364 SET status = '3',
365 error_code = 'E247'
366 WHERE interface_contacts_id = c_pc_rec.interface_contacts_id;
367 l_Check := 'TRUE';
368 RETURN;
369 END IF;
370
371 -- Validate the PHONE_COUNTRY_CODE
372 IF c_pc_rec.phone_country_code IS NOT NULL THEN
373 OPEN c_ph_cntry_cd(c_pc_rec.phone_country_code);
374 FETCH c_ph_cntry_cd INTO l_dummy;
375 IF c_ph_cntry_cd%NOTFOUND THEN
376 -- If the validation is not successful.
377
378 IF l_enable_log = 'Y' THEN
379 igs_ad_imp_001.logerrormessage(c_pc_rec.interface_contacts_id,'E173');
380 END IF;
381
382 UPDATE igs_ad_contacts_int_all
383 SET status = '3',
384 error_code = 'E173'
385 WHERE interface_contacts_id = c_pc_rec.interface_contacts_id;
386 CLOSE c_ph_cntry_cd;
387 l_Check := 'TRUE';
388 RETURN;
389 END IF;
390 CLOSE c_ph_cntry_cd;
391 END IF;
392
393 END IF;
394
395 IF c_pc_rec.contact_point_type = 'EMAIL' THEN
396 -- Validation to check whether email address is null
397 IF c_pc_rec.email_address IS NULL THEN
398
399 IF l_enable_log = 'Y' THEN
400 igs_ad_imp_001.logerrormessage(c_pc_rec.interface_contacts_id,'E251');
401 END IF;
402
403 UPDATE igs_ad_contacts_int_all
404 SET error_code = 'E251',
405 status = '3'
406 WHERE interface_contacts_id = c_pc_rec.interface_contacts_id;
407 l_Check := 'TRUE';
408 RETURN;
409 END IF;
410
411 IF NOT
412 (igs_pe_pers_imp_001.validate_lookup_type_code('EMAIL_FORMAT',c_pc_rec.email_format,222))
413 THEN
414
415 IF l_enable_log = 'Y' THEN
416 igs_ad_imp_001.logerrormessage(c_pc_rec.interface_contacts_id,'E248');
417 END IF;
418
419 UPDATE igs_ad_contacts_int_all
420 SET error_code = 'E248',
421 status = '3'
422 WHERE interface_contacts_id = c_pc_rec.interface_contacts_id;
423 l_Check := 'TRUE';
424 RETURN;
425 END IF;
426 END IF;
427 l_check := 'FALSE';
428 END validate_prsn_contacts;
429
430 -- local procedure ends;
431
432 -- main procedure begins;
433 BEGIN
434
435 l_interface_run_id := igs_ad_imp_001.g_interface_run_id;
436 l_prog_label := 'igs.plsql.igs_ad_imp_012.prc_pe_cntct_dtls';
437 l_label := 'igs.plsql.igs_ad_imp_012.prc_pe_cntct_dtls.';
438 l_enable_log := igs_ad_imp_001.g_enable_log;
439
440 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
441
442 IF (l_request_id IS NULL) THEN
443 l_request_id := fnd_global.conc_request_id;
444 END IF;
445
446 l_label := 'igs.plsql.igs_ad_imp_012.prc_pe_cntct_dtls.begin';
447 l_debug_str := 'Igs_Ad_Imp_012.prc_pe_cntct_dtls';
448
449 fnd_log.string_with_context( fnd_log.level_procedure,
450 l_label,
451 l_debug_str, NULL,
452 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
453 END IF;
454
455 l_rule :=igs_ad_imp_001.find_source_cat_rule(p_source_type_id,'PERSON_CONTACTS');
456
457 -- 1.If rule is E or I, then if the match_ind is not null, the combination is invalid
458 IF l_rule IN ('E','I') THEN
459 UPDATE igs_ad_contacts_int_all
460 SET status = cst_stat_val_3,
461 ERROR_CODE = cst_err_val_695 -- Error code depicting incorrect combination
462 WHERE match_ind IS NOT NULL
463 AND interface_run_id = l_interface_run_id
464 AND status = cst_stat_val_2;
465 END IF;
466
467 --2. If rule is E and duplicate exists, update match_ind to 19 and status to 1
468 IF l_rule = 'E' THEN
469 UPDATE igs_ad_contacts_int_all ai
470 SET status = cst_stat_val_1,
471 match_ind = cst_mi_val_19
472 WHERE ai.interface_run_id = l_interface_run_id
473 AND ai.status = cst_stat_val_2
474 AND EXISTS( SELECT '1'
475 FROM hz_contact_points pe, igs_ad_interface_all i
476 WHERE i.interface_run_id = l_interface_run_id
477 AND i.interface_id = ai.interface_id
478 AND pe.owner_table_id = i.person_id
479 AND UPPER(ai.contact_point_type) = pe.contact_point_type
480 AND pe.owner_table_name = 'HZ_PARTIES'
481 AND ((pe.email_format= UPPER(ai.email_format)
482 AND UPPER(pe.email_address) = UPPER(ai.email_address)
483 AND pe.contact_point_type='EMAIL')
484 OR (pe.phone_line_type = UPPER(ai.phone_line_type)
485 AND (pe.phone_country_code = ai.phone_country_code OR (pe.phone_country_code IS NULL AND ai.phone_country_code IS NULL))
486 AND (UPPER(pe.phone_Area_code) = UPPER(ai.phone_area_code) OR (pe.phone_Area_code IS NULL AND ai.phone_area_code IS NULL ) )
487 AND pe.phone_number=ai.phone_number
488 AND pe.contact_point_type='PHONE'))
489 );
490 END IF;
491
492 -- 3.If rule is R and there match_ind is 18,19,22 or 23 then the records must have been
493 -- processed in prior runs and didn't get updated .. update to status 1
494 IF l_rule = 'R' THEN
495 UPDATE igs_ad_contacts_int_all
496 SET status = cst_stat_val_1
497 WHERE interface_run_id = l_interface_run_id
498 AND match_ind IN (cst_mi_val_18,cst_mi_val_19,cst_mi_val_22,cst_mi_val_23)
499 AND status=cst_stat_val_2;
500 END IF;
501
502 -- 4.If rule is R and match_ind is neither 21 nor 25 then error
503 IF l_rule = 'R' THEN
504 UPDATE igs_ad_contacts_int_all
505 SET status = cst_stat_val_3,
506 ERROR_CODE = cst_err_val_695
507 WHERE interface_run_id = l_interface_run_id
508 AND (match_ind IS NOT NULL AND match_ind NOT IN (cst_mi_val_21,cst_mi_val_25))
509 AND status=cst_stat_val_2;
510 END IF;
511
512 -- 5.If rule is R, set duplicated records with no discrepancy to status 1 and match_ind 23
513 IF l_rule = 'R' THEN
514 UPDATE igs_ad_contacts_int_all ai
515 SET status = cst_stat_val_1,
516 match_ind = cst_mi_val_23
517 WHERE ai.interface_run_id = l_interface_run_id
518 AND ai.match_ind IS NULL
519 AND ai.status = cst_stat_val_2
520 AND EXISTS ( SELECT '1'
521 FROM hz_contact_points pe, igs_ad_interface_all i
522 WHERE i.interface_run_id = l_interface_run_id
523 AND i.interface_id = ai.interface_id
524 AND pe.owner_table_id = i.person_id
525 AND UPPER(ai.contact_point_type) = pe.contact_point_type
526 AND pe.owner_table_name = 'HZ_PARTIES'
527 AND ((pe.email_format= UPPER(ai.email_format)
528 AND UPPER(pe.email_address) = UPPER(ai.email_address)
529 AND pe.contact_point_type='EMAIL')
530 OR (pe.phone_line_type = UPPER(ai.phone_line_type)
531 AND (pe.phone_country_code = ai.phone_country_code OR (pe.phone_country_code IS NULL AND ai.phone_country_code IS NULL))
532 AND (UPPER(pe.phone_Area_code) = UPPER(ai.phone_area_code) OR (pe.phone_Area_code IS NULL AND ai.phone_area_code IS NULL ) )
533 AND pe.phone_number=ai.phone_number
534 AND (pe.phone_extension = ai.phone_extension
535 OR (pe.phone_extension IS NULL AND ai.phone_extension IS NULL))
536 AND pe.contact_point_type='PHONE'))
537 AND pe.primary_flag = NVL(ai.primary_flag,'N')
538 AND pe.content_source_type = 'USER_ENTERED'
539 );
540 END IF;
541
542 -- 6.If rule in R records still exist, they are duplicates and have discrepancy .. update status=3,match_ind=20
543 IF l_rule = 'R' THEN
544 UPDATE igs_ad_contacts_int_all ai
545 SET status = cst_stat_val_3,
546 match_ind = cst_mi_val_20,
547 dup_contact_point_id = (SELECT contact_point_id
548 FROM hz_contact_points pe, igs_ad_interface_all i
549 WHERE i.interface_run_id = l_interface_run_id
550 AND i.interface_id = ai.interface_id
551 AND rownum = 1
552 AND pe.owner_table_id = i.person_id
553 AND UPPER(ai.contact_point_type) = pe.contact_point_type
554 AND pe.owner_table_name = 'HZ_PARTIES'
555 AND ((pe.email_format= UPPER(ai.email_format)
556 AND UPPER(pe.email_address) = UPPER(ai.email_address)
557 AND pe.contact_point_type='EMAIL')
558 OR (pe.phone_line_type = UPPER(ai.phone_line_type)
559 AND (pe.phone_country_code = ai.phone_country_code OR (pe.phone_country_code IS NULL AND ai.phone_country_code IS NULL))
560 AND (UPPER(pe.phone_Area_code) = UPPER(ai.phone_area_code) OR (pe.phone_Area_code IS NULL AND ai.phone_area_code IS NULL ) )
561 AND pe.phone_number=ai.phone_number
562 AND pe.contact_point_type='PHONE')))
563 WHERE ai.interface_run_id = l_interface_run_id
564 AND ai.match_ind IS NULL
565 AND ai.status = cst_stat_val_2
566 AND EXISTS (SELECT '1'
567 FROM hz_contact_points pe, igs_ad_interface_all i
568 WHERE i.interface_run_id = l_interface_run_id
569 AND i.interface_id = ai.interface_id
570 AND pe.owner_table_id = i.person_id
571 AND UPPER(ai.contact_point_type) = pe.contact_point_type
572 AND pe.owner_table_name = 'HZ_PARTIES'
573 AND ((pe.email_format= UPPER(ai.email_format)
574 AND UPPER(pe.email_address) = UPPER(ai.email_address)
575 AND pe.contact_point_type='EMAIL')
576 OR (pe.phone_line_type = UPPER(ai.phone_line_type)
577 AND (pe.phone_country_code = ai.phone_country_code OR (pe.phone_country_code IS NULL AND ai.phone_country_code IS NULL))
578 AND (UPPER(pe.phone_Area_code) = UPPER(ai.phone_area_code) OR (pe.phone_Area_code IS NULL AND ai.phone_area_code IS NULL ) )
579 AND pe.phone_number=ai.phone_number
580 AND pe.contact_point_type='PHONE')));
581 END IF;
582
583 FOR rec_pc1 IN c_pc(l_interface_run_id) LOOP
584
585 l_processed_records := l_processed_records + 1;
586 l_check := 'FALSE' ;
587 Validate_Prsn_Contacts(rec_pc1,l_check);
588
589 IF l_check = 'FALSE' THEN
590 DECLARE
591 CURSOR check_dup_contact( p_owner_table_id NUMBER,
592 p_contact_point_type VARCHAR2,
593 p_email_format VARCHAR2,
594 p_email_address VARCHAR2,
595 p_phone_line_type VARCHAR2,
596 p_phone_country_code VARCHAR2,
597 p_phone_area_code VARCHAR2,
598 p_phone_number VARCHAR2
599 ) IS
600 SELECT ROWID, hi.*
601 FROM hz_contact_points hi
602 WHERE hi.owner_table_id=p_owner_table_id
603 AND UPPER(hi.contact_point_type)=UPPER(p_contact_point_type)
604 AND UPPER(hi.owner_table_name)='HZ_PARTIES'
605 AND ((UPPER(hi.email_format)=UPPER(p_email_format)
606 AND UPPER(hi.email_address)=UPPER(p_email_address)
607 AND UPPER(hi.contact_point_type)='EMAIL')
608 OR (UPPER(hi.phone_line_type)=UPPER(p_phone_line_type)
609 AND (UPPER(hi.phone_country_code)=UPPER(p_phone_country_code) OR (hi.phone_country_code IS NULL AND p_phone_country_code IS NULL ) )
610 AND (UPPER(hi.phone_Area_code)=UPPER(p_phone_area_code) OR (hi.phone_Area_code IS NULL AND p_phone_area_code IS NULL ) )
611 AND UPPER(hi.phone_number)=UPPER(p_phone_number)
612 AND UPPER(hi.contact_point_type)='PHONE'));
613
614 check_dup_contact_rec check_dup_contact%ROWTYPE;
615 BEGIN
616 check_dup_contact_rec.contact_point_type := NULL;
617 OPEN check_dup_contact( rec_pc1.i_person_id,
618 rec_pc1.contact_point_type,
619 rec_pc1.email_format,
620 rec_pc1.email_address,
621 rec_pc1.phone_line_type,
622 rec_pc1.phone_country_code,
623 rec_pc1.phone_area_code,
624 rec_pc1.phone_number );
625 FETCH check_dup_contact INTO check_dup_contact_rec;
626 CLOSE check_dup_contact;
627 l_contact_point_id := check_dup_contact_rec.contact_point_id;
628 IF check_dup_contact_rec.contact_point_type IS NOT NULL THEN
629 IF l_rule = 'I' THEN
630 DECLARE
631 l_tmp_var1 VARCHAR2(500);
632 l_tmp_var VARCHAR2(500);
633 l_rowid VARCHAR2(25);
634 l_last_update DATE;
635 l_return_status VARCHAR2(25);
636 l_msg_count NUMBER;
637 l_msg_data VARCHAR2(4000);
638 l_smp VARCHAR2(25);
639 l_smp1 VARCHAR2(25);
640 l_obj_ver hz_contact_points.object_version_number%TYPE;
641 p_contact_points_rec HZ_CONTACT_POINT_V2PUB.contact_point_rec_type;
642 p_email_rec HZ_CONTACT_POINT_V2PUB.email_rec_type;
643 p_phone_rec HZ_CONTACT_POINT_V2PUB.phone_rec_type;
644
645 BEGIN
646 SELECT object_version_number
647 INTO l_obj_ver
648 FROM hz_contact_points
649 WHERE contact_point_id = check_dup_contact_rec.contact_point_id;
650
651 p_contact_points_rec.contact_point_id := check_dup_contact_rec.contact_point_id;
652 p_contact_points_rec.contact_point_type := rec_pc1.contact_point_type;
653 p_contact_points_rec.owner_table_name := 'HZ_PARTIES';
654 p_contact_points_rec.owner_table_id := rec_pc1.i_person_id;
655 p_contact_points_rec.primary_flag := NVL((NVL(rec_pc1.primary_flag,check_dup_contact_rec.primary_flag)),FND_API.G_MISS_CHAR);
656 -- p_contact_points_rec.content_source_type := 'USER_ENTERED';
657 -- p_contact_points_rec.created_by_module := 'IGS';
658 IF rec_pc1.contact_point_type = 'EMAIL' THEN
659 p_email_rec.email_format := NVL(rec_pc1.email_format,FND_API.G_MISS_CHAR);
660 p_email_rec.email_address :=NVL(rec_pc1.email_address,FND_API.G_MISS_CHAR);
661 END IF;
662 IF rec_pc1.contact_point_type = 'PHONE' THEN
663 p_phone_rec.phone_country_code := NVL((NVL(rec_pc1.phone_country_code,check_dup_contact_rec.phone_country_code)),FND_API.G_MISS_CHAR); --
664 p_phone_rec.phone_line_type :=NVL(rec_pc1.phone_line_type,FND_API.G_MISS_CHAR);
665 p_phone_rec.phone_area_code := NVL((NVL(rec_pc1.phone_area_code,check_dup_contact_rec.phone_area_code)),FND_API.G_MISS_CHAR); --
666 p_phone_rec.phone_number := NVL(rec_pc1.phone_number,FND_API.G_MISS_CHAR);
667 p_phone_rec.phone_extension := NVL((NVL(rec_pc1.phone_extension,check_dup_contact_rec.phone_extension)),FND_API.G_MISS_CHAR); --
668 END IF;
669
670 HZ_CONTACT_POINT_V2PUB.update_contact_point(
671 p_init_msg_list => FND_API.G_FALSE,
672 p_contact_point_rec => p_contact_points_rec,
673 p_email_rec => p_email_rec ,
674 p_phone_rec => p_phone_rec,
675 p_object_version_number => l_obj_ver,
676 x_return_status => l_return_status,
677 x_msg_count => l_msg_count,
678 x_msg_data => l_msg_data
679 );
680
681 IF l_return_status IN ('E','U') THEN
682 IF l_msg_count > 1 THEN
683 FOR i IN 1..l_msg_count LOOP -- loop thro the various error msgs and display
684 l_tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
685 l_tmp_var1 := l_tmp_var1 || ' '|| l_tmp_var;
686 END LOOP;
687 l_msg_data := l_tmp_var1;
688 END IF;
689 l_error_code := 'E014';
690 l_status := '3';
691 --error code to be defined for the updation failure
692
693 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
694
695 IF (l_request_id IS NULL) THEN
696 l_request_id := fnd_global.conc_request_id;
697 END IF;
698
699 l_label := 'igs.plsql.igs_ad_imp_012.prc_pe_cntct_dtls.exception1';
700
701 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
702 fnd_message.set_token('INTERFACE_ID',rec_pc1.interface_contacts_id);
703 fnd_message.set_token('ERROR_CD','E014');
704
705 l_debug_str := fnd_message.get || ' ' || l_msg_data;
706
707 fnd_log.string_with_context( fnd_log.level_exception,
708 l_label,
709 l_debug_str, NULL,
710 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
711 END IF;
712
713 IF l_enable_log = 'Y' THEN
714 igs_ad_imp_001.logerrormessage(rec_pc1.interface_contacts_id,'E014');
715 END IF;
716
717
718 UPDATE igs_ad_contacts_int_all
719 SET ERROR_CODE = 'E014',
720 status='3'
721 WHERE interface_contacts_id = rec_pc1.interface_contacts_id;
722 ELSE
723 l_status := '1';
724 UPDATE igs_ad_contacts_int_all
725 SET status=cst_stat_val_1,
726 match_ind =cst_mi_val_18
727 WHERE interface_contacts_id = rec_pc1.interface_contacts_id;
728 END IF; -- if l_return_status
729
730 EXCEPTION
731 WHEN OTHERS THEN
732
733 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
734
735 IF (l_request_id IS NULL) THEN
736 l_request_id := fnd_global.conc_request_id;
737 END IF;
738
739 l_label := 'igs.plsql.igs_ad_imp_012.prc_pe_cntct_dtls.exception2';
740
741 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
742 fnd_message.set_token('INTERFACE_ID',rec_pc1.interface_contacts_id);
743 fnd_message.set_token('ERROR_CD','E014');
744
745 l_debug_str := fnd_message.get || ' ' || SQLERRM;
746
747 fnd_log.string_with_context( fnd_log.level_exception,
748 l_label,
749 l_debug_str, NULL,
750 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
751 END IF;
752
753 IF l_enable_log = 'Y' THEN
754 igs_ad_imp_001.logerrormessage(rec_pc1.interface_contacts_id,'E014');
755 END IF;
756
757 UPDATE igs_ad_contacts_int_all
758 SET match_ind = cst_mi_val_18,
759 status = cst_stat_val_3
760 WHERE interface_contacts_id = rec_pc1.interface_contacts_id;
761 END; -- begin
762 ELSIF l_rule = 'R' THEN
763 IF rec_pc1.ai_match_ind = '21' THEN
764 DECLARE
765 l_tmp_var1 VARCHAR2(500);
766 l_tmp_var VARCHAR2(500);
767 l_rowid VARCHAR2(25);
768 l_last_update DATE;
769 l_smp VARCHAR2(25);
770 l_smp1 VARCHAR2(25);
771 l_return_status VARCHAR2(25);
772 l_msg_count NUMBER;
773 l_msg_data VARCHAR2(4000);
774 p_contact_points_rec HZ_CONTACT_POINT_V2PUB.contact_point_rec_type;
775 p_email_rec HZ_CONTACT_POINT_V2PUB.email_rec_type;
776 p_phone_rec HZ_CONTACT_POINT_V2PUB.phone_rec_type;
777 l_obj_ver hz_contact_points.object_version_number%TYPE;
778
779 BEGIN
780 SELECT object_version_number
781 INTO l_obj_ver
782 FROM hz_contact_points
783 WHERE contact_point_id = l_contact_point_id;
784 p_contact_points_rec.contact_point_id := l_contact_point_id;
785 p_contact_points_rec.contact_point_type := rec_pc1.contact_point_type;
786 p_contact_points_rec.owner_table_name := 'HZ_PARTIES';
787 p_contact_points_rec.owner_table_id := rec_pc1.i_person_id;
788 p_contact_points_rec.primary_flag := NVL((NVL(rec_pc1.primary_flag,check_dup_contact_rec.primary_flag)),FND_API.G_MISS_CHAR);
789
790 -- p_contact_points_rec.content_source_type := 'USER_ENTERED';
791 -- p_contact_points_rec.created_by_module := 'IGS';
792
793 IF rec_pc1.contact_point_type = 'EMAIL' THEN
794 p_email_rec.email_format := NVL(rec_pc1.email_format,FND_API.G_MISS_CHAR);
795 p_email_rec.email_address :=NVL(rec_pc1.email_address,FND_API.G_MISS_CHAR);
796 END IF;
797
798 IF rec_pc1.contact_point_type = 'PHONE' THEN
799 p_phone_rec.phone_country_code := NVL((NVL(rec_pc1.phone_country_code,check_dup_contact_rec.phone_country_code)),FND_API.G_MISS_CHAR);
800 p_phone_rec.phone_line_type := NVL(rec_pc1.phone_line_type,FND_API.G_MISS_CHAR);
801 p_phone_rec.phone_area_code := NVL((NVL(rec_pc1.phone_area_code,check_dup_contact_rec.phone_area_code)),FND_API.G_MISS_CHAR);
802 p_phone_rec.phone_number := NVL(rec_pc1.phone_number,FND_API.G_MISS_CHAR);
803 p_phone_rec.phone_extension := NVL((NVL(rec_pc1.phone_extension,check_dup_contact_rec.phone_extension)),FND_API.G_MISS_CHAR) ;
804 END IF;
805 HZ_CONTACT_POINT_V2PUB.update_contact_point(
806 p_init_msg_list => FND_API.G_FALSE,
807 p_contact_point_rec => p_contact_points_rec,
808 p_email_rec => p_email_rec ,
809 p_phone_rec => p_phone_rec,
810 p_object_version_number => l_obj_ver,
811 x_return_status => l_return_status,
812 x_msg_count => l_msg_count,
813 x_msg_data => l_msg_data
814 );
815
816
817 IF l_return_status IN ('E','U') THEN
818 IF l_msg_count > 1 THEN
819 FOR i IN 1..l_msg_count
820 LOOP
821 l_tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
822 l_tmp_var1 := l_tmp_var1 || ' '|| l_tmp_var;
823 END LOOP;
824 l_msg_data := l_tmp_var1;
825 END IF;
826 l_error_code := 'E014';
827 --error code to be defined for the updation failure
828
829 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
830
831 IF (l_request_id IS NULL) THEN
832 l_request_id := fnd_global.conc_request_id;
833 END IF;
834
835 l_label := 'igs.plsql.igs_ad_imp_012.prc_pe_cntct_dtls.exception3';
836
837 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
838 fnd_message.set_token('INTERFACE_ID',rec_pc1.interface_contacts_id);
839 fnd_message.set_token('ERROR_CD',l_error_code);
840
841 l_debug_str := fnd_message.get || ' ' || l_msg_data;
842
843 fnd_log.string_with_context( fnd_log.level_exception,
844 l_label,
845 l_debug_str, NULL,
846 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
847 END IF;
848
849 IF l_enable_log = 'Y' THEN
850 igs_ad_imp_001.logerrormessage(rec_pc1.interface_contacts_id,'E014');
851 END IF;
852
853 UPDATE igs_ad_contacts_int_all
854 SET error_code = 'E014',
855 status='3'
856 WHERE interface_contacts_id = rec_pc1.interface_contacts_id;
857 ELSE
858 UPDATE igs_ad_contacts_int_all
859 SET status = cst_stat_val_1,
860 match_ind = cst_mi_val_18
861 WHERE interface_contacts_id = rec_pc1.interface_contacts_id;
862 END IF;
863 EXCEPTION
864 WHEN OTHERS THEN
865
866 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
867
868 IF (l_request_id IS NULL) THEN
869 l_request_id := fnd_global.conc_request_id;
870 END IF;
871
872 l_label := 'igs.plsql.igs_ad_imp_012.prc_pe_cntct_dtls.exception4';
873
874 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
875 fnd_message.set_token('INTERFACE_ID',rec_pc1.interface_contacts_id);
876 fnd_message.set_token('ERROR_CD','E014');
877
878 l_debug_str := fnd_message.get || ' ' || SQLERRM;
879
880 fnd_log.string_with_context( fnd_log.level_exception,
881 l_label,
882 l_debug_str, NULL,
883 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
884 END IF;
885
886 IF l_enable_log = 'Y' THEN
887 igs_ad_imp_001.logerrormessage(rec_pc1.interface_contacts_id,'E014');
888 END IF;
889
890 UPDATE igs_ad_contacts_int_all
891 SET status = '3'
892 WHERE interface_contacts_id = rec_pc1.interface_contacts_id;
893 END; -- begin
894 END IF; -- if match_ind
895 END IF; -- if l_rule
896 ELSE -- l_dup = FALSE
897 BEGIN
898 crt_prsn_contacts(rec_pc => rec_pc1, error_code => l_error_code, status => l_status ) ;
899 EXCEPTION
900 WHEN OTHERS THEN
901
902 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
903
904 IF (l_request_id IS NULL) THEN
905 l_request_id := fnd_global.conc_request_id;
906 END IF;
907
908 l_label := 'igs.plsql.igs_ad_imp_012.prc_pe_cntct_dtls.exception5';
909
910 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
911 fnd_message.set_token('INTERFACE_ID',rec_pc1.interface_contacts_id);
912 fnd_message.set_token('ERROR_CD','E518');
913
914 l_debug_str := fnd_message.get || ' ' || SQLERRM;
915
916 fnd_log.string_with_context( fnd_log.level_exception,
917 l_label,
918 l_debug_str, NULL,
919 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
920 END IF;
921
922 IF l_enable_log = 'Y' THEN
923 igs_ad_imp_001.logerrormessage(rec_pc1.interface_contacts_id,'E518');
924 END IF;
925
926 UPDATE igs_ad_contacts_int_all
927 SET status = '3',error_code='E518'
928 WHERE interface_contacts_id = rec_pc1.interface_contacts_id;
929 END;
930 END IF; -- if chk_dup_contact
931 END; -- outer begin
932 END IF; -- l_check is true
933 -- nothing is done here as the final update has happened inside validate person only.
934 IF l_processed_records = 100 THEN
935 COMMIT;
936 l_processed_records := 0;
937 END IF;
938 END LOOP;
939 END prc_pe_cntct_dtls;
940
941 PROCEDURE prc_pe_language (
942 p_source_type_id IN NUMBER,
943 p_batch_id IN NUMBER )
944 AS
945 /*
946 || Created By : pkpatel
947 || Created On : 10-JUN-2002
948 || Purpose : Bug No:2402077 Validate the Person ID type and Format mask for Alternate ID
949 || Known limitations, enhancements or remarks :
950 || Change History :
951 || Who When What
952 || (reverse chronological order - newest change first)
953 || pkpatel 15-JAN-2003 Bug NO: 2397876
954 || Added all the missing validations and replaced E008 with proper error codes
955 */
956
957 l_prog_label VARCHAR2(100);
958 l_label VARCHAR2(100);
959 l_debug_str VARCHAR2(2000);
960 l_enable_log VARCHAR2(1);
961 l_request_id NUMBER;
962 l_interface_run_id igs_ad_interface_all.interface_run_id%TYPE;
963
964 CURSOR person_language_cur(cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE) IS
965 SELECT hii.*, i.person_id
966 FROM igs_ad_language_int_all hii, igs_ad_interface_all i
967 WHERE hii.interface_run_id = cp_interface_run_id
968 AND i.interface_id = hii.interface_id
969 AND i.interface_run_id = cp_interface_run_id
970 AND hii.status = '2';
971
972 l_var VARCHAR2(1);
973 l_rule VARCHAR2(1);
974 l_error_code VARCHAR2(25);
975 l_status VARCHAR2(25);
976 l_return_status VARCHAR2(25);
977 l_dup_var BOOLEAN;
978 l_msg_count NUMBER;
979 l_msg_data VARCHAR2(4000);
980 p_person_language_rec person_language_cur%ROWTYPE;
981 person_language_rec person_language_cur%ROWTYPE;
982 l_processed_records NUMBER(5) := 0;
983
984 FUNCTION validate_lang(p_person_language_rec IN person_language_cur%ROWTYPE)
985 RETURN BOOLEAN IS
986
987 CURSOR lang_name_cur(cp_language_code p_person_language_rec.language_name%TYPE) IS
988 SELECT 'X'
989 FROM fnd_languages_vl
990 WHERE language_code = cp_language_code;
991
992 l_exists VARCHAR2(1);
993 l_error_code igs_ad_interface_all.ERROR_CODE%TYPE;
994 BEGIN
995 OPEN lang_name_cur(p_person_language_rec.language_name);
996 FETCH lang_name_cur INTO l_exists;
997 IF lang_name_cur%NOTFOUND THEN
998 CLOSE lang_name_cur;
999 l_error_code := 'E551';
1000 RAISE NO_DATA_FOUND;
1001 END IF;
1002 CLOSE lang_name_cur;
1003
1004 IF p_person_language_rec.READS_LEVEL IS NOT NULL AND NOT (igs_pe_pers_imp_001.validate_lookup_type_code('HZ_LANGUAGE_PROFICIENCY',p_person_language_rec.READS_LEVEL,222)) THEN
1005 l_error_code := 'E750';
1006 RAISE NO_DATA_FOUND;
1007 END IF;
1008
1009 IF p_person_language_rec.SPEAKS_LEVEL IS NOT NULL AND NOT (igs_pe_pers_imp_001.validate_lookup_type_code('HZ_LANGUAGE_PROFICIENCY',p_person_language_rec.SPEAKS_LEVEL,222)) THEN
1010 l_error_code := 'E751';
1011 RAISE NO_DATA_FOUND;
1012 END IF;
1013
1014 IF p_person_language_rec.WRITES_LEVEL IS NOT NULL AND NOT (igs_pe_pers_imp_001.validate_lookup_type_code('HZ_LANGUAGE_PROFICIENCY',p_person_language_rec.WRITES_LEVEL,222)) THEN
1015 l_error_code := 'E752';
1016 RAISE NO_DATA_FOUND;
1017 END IF;
1018
1019 IF p_person_language_rec.UNDERSTANDS_LEVEL IS NOT NULL AND NOT (igs_pe_pers_imp_001.validate_lookup_type_code('HZ_LANGUAGE_PROFICIENCY',p_person_language_rec.UNDERSTANDS_LEVEL,222)) THEN
1020 l_error_code := 'E753';
1021 RAISE NO_DATA_FOUND;
1022 END IF;
1023
1024 IF p_person_language_rec.LANG_STATUS <> 'A' AND p_person_language_rec.LANG_STATUS <> 'I' THEN
1025 l_error_code := 'E754';
1026 RAISE NO_DATA_FOUND;
1027 END IF;
1028
1029 RETURN TRUE;
1030
1031 EXCEPTION
1032 WHEN NO_DATA_FOUND THEN
1033
1034 UPDATE igs_ad_language_int_all
1035 SET status = '3',
1036 error_code = l_error_code
1037 WHERE interface_language_id = p_person_language_rec.interface_language_id;
1038
1039 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
1040
1041 IF (l_request_id IS NULL) THEN
1042 l_request_id := fnd_global.conc_request_id;
1043 END IF;
1044
1045 l_label := 'igs.plsql.igs_ad_imp_012.prc_pe_language.exception_validate_lang';
1046
1047 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
1048 fnd_message.set_token('INTERFACE_ID',p_person_language_rec.interface_language_id);
1049 fnd_message.set_token('ERROR_CD',l_error_code);
1050
1051 l_debug_str := fnd_message.get || ' ' || SQLERRM;
1052
1053 fnd_log.string_with_context( fnd_log.level_exception,
1054 l_label,
1055 l_debug_str, NULL,
1056 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1057 END IF;
1058
1059 IF l_enable_log = 'Y' THEN
1060 igs_ad_imp_001.logerrormessage(p_person_language_rec.interface_language_id,l_error_code);
1061 END IF;
1062
1063 RETURN FALSE;
1064 END validate_lang;
1065
1066
1067 PROCEDURE crt_prsn_language(p_person_language_rec IN person_language_cur%ROWTYPE) AS
1068 l_return_status VARCHAR2(25);
1069 l_msg_count NUMBER;
1070 l_msg_data VARCHAR2(4000);
1071 l_language_use_reference_id NUMBER;
1072 l_language_id3 NUMBER;
1073
1074 --V2 API UPTAKE BY GMURALID
1075 p_per_language_rec HZ_PERSON_INFO_V2PUB.person_language_rec_type;
1076 l_tmp_var1 VARCHAR2(500);
1077 l_tmp_var VARCHAR2(500);
1078 l_object_version_number NUMBER;
1079 l_last_update_date DATE;
1080 BEGIN
1081
1082 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
1083
1084 IF (l_request_id IS NULL) THEN
1085 l_request_id := fnd_global.conc_request_id;
1086 END IF;
1087
1088 l_label := 'igs.plsql.igs_ad_imp_012.prc_pe_language.begin_crt_prsn_language';
1089 l_debug_str := 'Igs_Ad_Imp_012.crt_prsn_language';
1090
1091 fnd_log.string_with_context( fnd_log.level_procedure,
1092 l_label,
1093 l_debug_str, NULL,
1094 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1095 END IF;
1096
1097
1098 IF validate_lang(p_person_language_rec) THEN
1099
1100 igs_pe_languages_pkg.Languages(
1101 p_action => 'INSERT',
1102 P_LANGUAGE_NAME => p_person_language_rec.language_name,
1103 p_DESCRIPTION => null,
1104 p_PARTY_ID => p_person_language_rec.person_id,
1105 p_native_language => p_person_language_rec.native_language,
1106 p_primary_language_indicator => p_person_language_rec.primary_language_indicator,
1107 P_READS_LEVEL => p_person_language_rec.reads_level,
1108 P_SPEAKS_LEVEL => p_person_language_rec.speaks_level,
1109 P_WRITES_LEVEL => p_person_language_rec.writes_level,
1110 p_END_DATE => null,
1111 p_status => p_person_language_rec.lang_status,
1112 p_understand_level => p_person_language_rec.understands_level,
1113 p_last_update_date => l_last_update_date,
1114 p_return_status => l_return_status,
1115 p_msg_count => l_msg_count,
1116 p_msg_data => l_msg_data,
1117 P_language_use_reference_id => l_language_use_reference_id,
1118 p_language_ovn => l_object_version_number
1119 );
1120
1121 IF l_return_status IN ('E','U') THEN
1122 IF l_msg_count > 1 THEN
1123 FOR i IN 1..l_msg_count
1124 LOOP
1125 l_tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
1126 l_tmp_var1 := l_tmp_var1 || ' '|| l_tmp_var;
1127 END LOOP;
1128 l_msg_data := l_tmp_var1;
1129 END IF;
1130
1131 UPDATE igs_ad_language_int_all
1132 SET status = '3',
1133 error_code = 'E322'
1134 WHERE interface_language_id = p_person_language_rec.interface_language_id;
1135
1136 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
1137
1138 IF (l_request_id IS NULL) THEN
1139 l_request_id := fnd_global.conc_request_id;
1140 END IF;
1141
1142 l_label := 'igs.plsql.igs_ad_imp_012.prc_pe_language.exception_crt_prsn_language1';
1143
1144 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
1145 fnd_message.set_token('INTERFACE_ID',p_person_language_rec.interface_language_id);
1146 fnd_message.set_token('ERROR_CD','E322');
1147
1148 l_debug_str := fnd_message.get || ' ' || l_msg_data;
1149
1150 fnd_log.string_with_context( fnd_log.level_exception,
1151 l_label,
1152 l_debug_str, NULL,
1153 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1154 END IF;
1155
1156 IF l_enable_log = 'Y' THEN
1157 igs_ad_imp_001.logerrormessage(p_person_language_rec.interface_language_id,'E322');
1158 END IF;
1159
1160 ELSE
1161
1162 UPDATE igs_ad_language_int_all
1163 SET status = '1',
1164 error_code = NULL
1165 WHERE interface_language_id = p_person_language_rec.interface_language_id;
1166
1167 END IF;
1168 END IF;
1169
1170 EXCEPTION
1171 WHEN OTHERS THEN
1172 UPDATE igs_ad_language_int_all
1173 SET status = '3',
1174 error_code = 'E322'
1175 WHERE interface_language_id = p_person_language_rec.interface_language_id;
1176
1177 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
1178
1179 IF (l_request_id IS NULL) THEN
1180 l_request_id := fnd_global.conc_request_id;
1181 END IF;
1182
1183 l_label := 'igs.plsql.igs_ad_imp_012.prc_pe_language.exception_crt_prsn_language2';
1184
1185 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
1186 fnd_message.set_token('INTERFACE_ID',p_person_language_rec.interface_language_id);
1187 fnd_message.set_token('ERROR_CD','E322');
1188
1189 l_debug_str := fnd_message.get || ' ' || SQLERRM;
1190
1191 fnd_log.string_with_context( fnd_log.level_exception,
1192 l_label,
1193 l_debug_str, NULL,
1194 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1195 END IF;
1196
1197 IF l_enable_log = 'Y' THEN
1198 igs_ad_imp_001.logerrormessage(p_person_language_rec.interface_language_id,'E322');
1199 END IF;
1200
1201 END crt_prsn_language;
1202 -- end of local procedure crt_prsn_lang
1203 -- start of main procedure prc_pe_lang
1204 BEGIN
1205
1206 l_interface_run_id := igs_ad_imp_001.g_interface_run_id;
1207 l_prog_label := 'igs.plsql.igs_ad_imp_012.prc_pe_language';
1208 l_label := 'igs.plsql.igs_ad_imp_012.prc_pe_language.';
1209 l_enable_log := igs_ad_imp_001.g_enable_log;
1210
1211 l_rule :=igs_ad_imp_001.find_source_cat_rule(p_source_type_id,'PERSON_LANGUAGES');
1212
1213 -- 1.If rule is E or I, then if the match_ind is not null, the combination is invalid
1214 IF l_rule IN ('E','I') THEN
1215 UPDATE igs_ad_language_int_all
1216 SET status = cst_stat_val_3,
1217 ERROR_CODE = cst_err_val_695 -- Error code depicting incorrect combination
1218 WHERE match_ind IS NOT NULL
1219 AND interface_run_id = l_interface_run_id
1220 AND status = cst_stat_val_2;
1221 END IF;
1222
1223 --2. If rule is E and duplicate exists, update match_ind to 19 and status to 1
1224 IF l_rule = 'E' THEN
1225 UPDATE igs_ad_language_int_all ai
1226 SET status = cst_stat_val_1,
1227 match_ind = cst_mi_val_19
1228 WHERE ai.interface_run_id = l_interface_run_id
1229 AND ai.status = cst_stat_val_2
1230 AND EXISTS( SELECT '1'
1231 FROM hz_person_language pe, igs_ad_interface_all i
1232 WHERE i.interface_run_id = l_interface_run_id
1233 AND i.interface_id = ai.interface_id
1234 AND pe.party_id = i.person_id
1235 AND pe.language_name = UPPER(ai.language_name)
1236 );
1237 END IF;
1238
1239 -- 3.If rule is R and there match_ind is 18,19,22 or 23 then the records must have been
1240 -- processed in prior runs and didn't get updated .. update to status 1
1241 IF l_rule = 'R' THEN
1242 UPDATE igs_ad_language_int_all
1243 SET status = cst_stat_val_1
1244 WHERE interface_run_id = l_interface_run_id
1245 AND match_ind IN (cst_mi_val_18,cst_mi_val_19,cst_mi_val_22,cst_mi_val_23)
1246 AND status=cst_stat_val_2;
1247 END IF;
1248
1249 -- 4.If rule is R and match_ind is neither 21 nor 25 then error
1250 IF l_rule = 'R' THEN
1251 UPDATE igs_ad_language_int_all
1252 SET status = cst_stat_val_3,
1253 ERROR_CODE = cst_err_val_695
1254 WHERE interface_run_id = l_interface_run_id
1255 AND (match_ind IS NOT NULL AND match_ind NOT IN (cst_mi_val_21,cst_mi_val_25))
1256 AND status=cst_stat_val_2;
1257 END IF;
1258
1259 -- 5.If rule is R, set duplicated records with no discrepancy to status 1 and match_ind 23
1260 IF l_rule = 'R' THEN
1261 UPDATE igs_ad_language_int_all mi
1262 SET status = cst_stat_val_1,
1263 match_ind = cst_mi_val_23
1264 WHERE mi.interface_run_id = l_interface_run_id
1265 AND mi.match_ind IS NULL
1266 AND mi.status = cst_stat_val_2
1267 AND EXISTS ( SELECT '1'
1268 FROM hz_person_language pe, igs_ad_interface_all i
1269 WHERE i.interface_run_id = l_interface_run_id
1270 AND i.interface_id = mi.interface_id
1271 AND pe.language_name = UPPER(mi.language_name)
1272 AND pe.party_id = i.person_id
1273 AND NVL(UPPER(pe.native_language),'N') = NVL(UPPER(mi.native_language),'N')
1274 AND NVL(UPPER(pe.primary_language_indicator),'N') = NVL(UPPER(mi.primary_language_indicator),'N')
1275 AND NVL(UPPER(pe.reads_level),'*!*') = NVL(UPPER(mi.reads_level),'*!*')
1276 AND NVL(UPPER(pe.speaks_level),'*!*') = NVL(UPPER(mi.speaks_level),'*!*')
1277 AND NVL(UPPER(pe.writes_level),'*!*') = NVL(UPPER(mi.writes_level),'*!*')
1278 AND NVL(UPPER(pe.spoken_comprehension_level),'*!*') = NVL(UPPER(mi.understands_level),'*!*')
1279 AND NVL(UPPER(pe.status),'*!*') = NVL(UPPER(mi.lang_status),'*!*')
1280 );
1281 END IF;
1282
1283 -- 6.If rule in R records still exist, they are duplicates and have discrepancy .. update status=3,match_ind=20
1284 IF l_rule = 'R' THEN
1285 UPDATE igs_ad_language_int_all mi
1286 SET status = cst_stat_val_3,
1287 match_ind = cst_mi_val_20
1288 WHERE mi.interface_run_id = l_interface_run_id
1289 AND mi.match_ind IS NULL
1290 AND mi.status = cst_stat_val_2
1291 AND EXISTS (SELECT '1'
1292 FROM hz_person_language pe, igs_ad_interface_all i
1293 WHERE i.interface_run_id = l_interface_run_id
1294 AND i.interface_id = mi.interface_id
1295 AND pe.party_id = i.person_id
1296 AND pe.language_name = UPPER(mi.language_name));
1297 END IF;
1298
1299 FOR person_language_rec IN person_language_cur(l_interface_run_id) LOOP
1300 DECLARE
1301 CURSOR check_dup_language(p_person_id NUMBER, p_language_name VARCHAR2 ) IS
1302 SELECT rowid, hi.*
1303 FROM hz_person_language hi
1304 WHERE hi.party_id = p_person_id
1305 AND hi.language_name = p_language_name;
1306 check_dup_language_rec check_dup_language%ROWTYPE;
1307 BEGIN
1308 person_language_rec.language_name := UPPER(person_language_rec.language_name);
1309 person_language_rec.native_language := UPPER(person_language_rec.native_language);
1310 person_language_rec.primary_language_indicator := UPPER(person_language_rec.primary_language_indicator);
1311 person_language_rec.reads_level := UPPER(person_language_rec.reads_level);
1312 person_language_rec.speaks_level := UPPER(person_language_rec.speaks_level);
1313 person_language_rec.writes_level := UPPER(person_language_rec.writes_level);
1314 person_language_rec.understands_level := UPPER(person_language_rec.understands_level);
1315 person_language_rec.lang_status := UPPER(person_language_rec.lang_status);
1316
1317 l_processed_records := l_processed_records + 1 ;
1318
1319 check_dup_language_rec.language_name := NULL;
1320 OPEN check_dup_language(person_language_rec.person_id,person_language_rec.language_name);
1321 FETCH check_dup_language INTO check_dup_language_rec;
1322 CLOSE check_dup_language;
1323 IF check_dup_language_rec.language_name IS NOT NULL THEN
1324 IF l_rule = 'I' THEN
1325 IF validate_lang(person_language_rec) THEN
1326 DECLARE
1327 l_rowid VARCHAR2(25);
1328 l_return_status VARCHAR2(25);
1329 l_msg_count NUMBER;
1330 l_msg_data VARCHAR2(2000);
1331 l_language_use_reference_id NUMBER;
1332 p_per_language_rec HZ_PERSON_INFO_V2PUB.person_language_rec_type;
1333
1334 l_tmp_var1 VARCHAR2(500);
1335 l_tmp_var VARCHAR2(500);
1336 l_object_version_number hz_person_language.OBJECT_VERSION_NUMBER%TYPE;
1337
1338
1339 BEGIN
1340
1341 igs_pe_languages_pkg.Languages(
1342 p_action => 'UPDATE',
1343 P_LANGUAGE_NAME => person_language_rec.language_name,
1344 p_DESCRIPTION => NULL,
1345 p_PARTY_ID => person_language_rec.person_id,
1346 p_native_language => NVL(person_language_rec.native_language,check_dup_language_rec.native_language),
1347 p_primary_language_indicator => NVL(person_language_rec.primary_language_indicator,check_dup_language_rec.primary_language_indicator),
1348 P_READS_LEVEL => NVL(person_language_rec.reads_level,check_dup_language_rec.reads_level),
1349 P_SPEAKS_LEVEL => NVL(person_language_rec.speaks_level,check_dup_language_rec.speaks_level),
1350 P_WRITES_LEVEL => NVL(person_language_rec.writes_level,check_dup_language_rec.writes_level),
1351 p_END_DATE => NULL,
1352 p_status => NVL(person_language_rec.lang_status,check_dup_language_rec.status),
1353 p_understand_level => NVL(person_language_rec.understands_level,check_dup_language_rec.spoken_comprehension_level),
1354 p_last_update_date => person_language_rec.last_update_date,
1355 p_return_status => l_return_status,
1356 p_msg_count => l_msg_count,
1357 p_msg_data => l_msg_data,
1358 P_language_use_reference_id => check_dup_language_rec.language_use_reference_id,
1359 p_language_ovn => check_dup_language_rec.object_version_number
1360 );
1361
1362 IF l_return_status IN ('E','U') THEN
1363 IF l_msg_count > 1 THEN
1364 FOR i IN 1..l_msg_count
1365 LOOP
1366 l_tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
1367 l_tmp_var1 := l_tmp_var1 || ' '|| l_tmp_var;
1368 END LOOP;
1369 l_msg_data := l_tmp_var1;
1370 END IF;
1371
1372 UPDATE igs_ad_language_int_all
1373 SET error_code = 'E014',
1374 status='3'
1375 WHERE interface_language_id = person_language_rec.interface_language_id;
1376
1377 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
1378
1379 IF (l_request_id IS NULL) THEN
1380 l_request_id := fnd_global.conc_request_id;
1381 END IF;
1382
1383 l_label := 'igs.plsql.igs_ad_imp_012.prc_pe_language.exception1';
1384
1385 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
1386 fnd_message.set_token('INTERFACE_ID',person_language_rec.interface_language_id);
1387 fnd_message.set_token('ERROR_CD','E014');
1388
1389 l_debug_str := fnd_message.get || ' ' || l_msg_data;
1390
1391 fnd_log.string_with_context( fnd_log.level_exception,
1392 l_label,
1393 l_debug_str, NULL,
1394 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1395 END IF;
1396
1397 IF l_enable_log = 'Y' THEN
1398 igs_ad_imp_001.logerrormessage(person_language_rec.interface_language_id,'E014');
1399 END IF;
1400
1401
1402 ELSE
1403
1404 UPDATE igs_ad_language_int_all
1405 SET status=cst_stat_val_1,
1406 match_ind = cst_mi_val_18
1407 WHERE interface_language_id = person_language_rec.interface_language_id;
1408 END IF;
1409
1410 EXCEPTION
1411 WHEN OTHERS THEN
1412
1413 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
1414
1415 IF (l_request_id IS NULL) THEN
1416 l_request_id := fnd_global.conc_request_id;
1417 END IF;
1418
1419 l_label := 'igs.plsql.igs_ad_imp_012.prc_pe_language.exception2';
1420
1421 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
1422 fnd_message.set_token('INTERFACE_ID',person_language_rec.interface_language_id);
1423 fnd_message.set_token('ERROR_CD','E014');
1424
1425 l_debug_str := fnd_message.get || ' ' || SQLERRM;
1426
1427 fnd_log.string_with_context( fnd_log.level_exception,
1428 l_label,
1429 l_debug_str, NULL,
1430 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1431 END IF;
1432
1433 IF l_enable_log = 'Y' THEN
1434 igs_ad_imp_001.logerrormessage(person_language_rec.interface_language_id,'E014');
1435 END IF;
1436
1437 UPDATE igs_ad_language_int_all
1438 SET match_ind = NULL,
1439 status = cst_stat_val_3,
1440 error_code = cst_err_val_014
1441 WHERE interface_language_id = person_language_rec.interface_language_id;
1442 END; -- inner begin
1443 END IF; -- if validate_lang
1444
1445 ELSIF l_rule = 'R' THEN
1446 IF person_language_rec.match_ind = '21' THEN
1447 IF validate_lang(person_language_rec) THEN
1448 DECLARE
1449 l_rowid VARCHAR2(25);
1450 l_return_status VARCHAR2(25);
1451 l_msg_count NUMBER;
1452 l_msg_data VARCHAR2(4000);
1453 l_language_use_reference_id NUMBER;
1454 p_per_language_rec HZ_PERSON_INFO_V2PUB.person_language_rec_type;
1455 l_tmp_var1 VARCHAR2(500);
1456 l_tmp_var VARCHAR2(500);
1457 l_object_version_number hz_person_language.OBJECT_VERSION_NUMBER%TYPE;
1458
1459
1460 BEGIN
1461
1462 igs_pe_languages_pkg.Languages(
1463 p_action => 'UPDATE',
1464 P_LANGUAGE_NAME => person_language_rec.language_name,
1465 p_DESCRIPTION => NULL,
1466 p_PARTY_ID => person_language_rec.person_id,
1467 p_native_language => NVL(person_language_rec.native_language,check_dup_language_rec.native_language),
1468 p_primary_language_indicator => NVL(person_language_rec.primary_language_indicator,check_dup_language_rec.primary_language_indicator),
1469 P_READS_LEVEL => NVL(person_language_rec.reads_level,check_dup_language_rec.reads_level),
1470 P_SPEAKS_LEVEL => NVL(person_language_rec.speaks_level,check_dup_language_rec.speaks_level),
1471 P_WRITES_LEVEL => NVL(person_language_rec.writes_level,check_dup_language_rec.writes_level),
1472 p_END_DATE => NULL,
1473 p_status => NVL(person_language_rec.lang_status,check_dup_language_rec.status),
1474 p_understand_level => NVL(person_language_rec.understands_level,check_dup_language_rec.spoken_comprehension_level),
1475 p_last_update_date => person_language_rec.last_update_date,
1476 p_return_status => l_return_status,
1477 p_msg_count => l_msg_count,
1478 p_msg_data => l_msg_data,
1479 P_language_use_reference_id => check_dup_language_rec.language_use_reference_id,
1480 p_language_ovn => check_dup_language_rec.object_version_number
1481 );
1482
1483 IF l_return_status IN ('E','U') THEN
1484
1485 IF l_msg_count > 1 THEN
1486 FOR i IN 1..l_msg_count
1487 LOOP
1488 l_tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
1489 l_tmp_var1 := l_tmp_var1 || ' '|| l_tmp_var;
1490 END LOOP;
1491 l_msg_data := l_tmp_var1;
1492 END IF;
1493
1494 UPDATE igs_ad_language_int_all
1495 SET error_code = 'E014',
1496 status='3'
1497 WHERE interface_language_id = person_language_rec.interface_language_id;
1498
1499 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
1500
1501 IF (l_request_id IS NULL) THEN
1502 l_request_id := fnd_global.conc_request_id;
1503 END IF;
1504
1505 l_label := 'igs.plsql.igs_ad_imp_012.prc_pe_language.exception3';
1506
1507 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
1508 fnd_message.set_token('INTERFACE_ID',person_language_rec.interface_language_id);
1509 fnd_message.set_token('ERROR_CD','E014');
1510
1511 l_debug_str := fnd_message.get || ' ' || l_msg_data;
1512
1513 fnd_log.string_with_context( fnd_log.level_exception,
1514 l_label,
1515 l_debug_str, NULL,
1516 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1517 END IF;
1518
1519 IF l_enable_log = 'Y' THEN
1520 igs_ad_imp_001.logerrormessage(person_language_rec.interface_language_id,'E014');
1521 END IF;
1522
1523 ELSE
1524 UPDATE igs_ad_language_int_all
1525 SET status=cst_stat_val_1,
1526 match_ind = cst_mi_val_18
1527 WHERE interface_language_id = person_language_rec.interface_language_id;
1528 END IF;
1529
1530 EXCEPTION
1531 WHEN OTHERS THEN
1532
1533 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
1534
1535 IF (l_request_id IS NULL) THEN
1536 l_request_id := fnd_global.conc_request_id;
1537 END IF;
1538
1539 l_label := 'igs.plsql.igs_ad_imp_012.prc_pe_language.exception4';
1540
1541 fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
1542 fnd_message.set_token('INTERFACE_ID',person_language_rec.interface_language_id);
1543 fnd_message.set_token('ERROR_CD','E014');
1544
1545 l_debug_str := fnd_message.get || ' ' || SQLERRM;
1546
1547 fnd_log.string_with_context( fnd_log.level_exception,
1548 l_label,
1549 l_debug_str, NULL,
1550 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1551 END IF;
1552
1553 IF l_enable_log = 'Y' THEN
1554 igs_ad_imp_001.logerrormessage(person_language_rec.interface_language_id,'E014');
1555 END IF;
1556
1557 UPDATE igs_ad_language_int_all
1558 SET match_ind = NULL,
1559 status = cst_stat_val_3,
1560 error_code = cst_err_val_014
1561 WHERE interface_language_id = person_language_rec.interface_language_id;
1562 END;
1563 END IF; -- if validate_lang
1564 END IF; -- if match_ind
1565 END IF; -- if l_rule
1566 ELSE
1567
1568 crt_prsn_language(p_person_language_rec => person_language_rec) ;
1569
1570 END IF; -- if check_dup_lang
1571 IF l_processed_records = 100 THEN
1572 COMMIT;
1573 l_processed_records := 0;
1574 END IF;
1575 END;
1576 END LOOP;
1577 END prc_pe_language ;
1578
1579 -- Starts procedure PRC_APCNT_ATH_DTLS
1580 --
1581 PROCEDURE prc_apcnt_ath_dtls
1582 (
1583 p_source_type_id IN NUMBER,
1584 p_batch_id IN NUMBER )
1585 AS
1586 /*
1587 || Created By : [email protected]
1588 || Created On : 15-NOV-2001
1589 || Purpose : This is a private procedure. This procedure is for importing person Athletic details.
1590 || DLD: Person Interface DLD. Enh Bug# 2103692.
1591 || Known limitations, enhancements or remarks :
1592 || Change History :
1593 || Who When What
1594 || (reverse chronological order - newest change first)
1595 || npalanis 6-JAN-2003 Bug : 2734697
1596 || code added to commit after import of every
1597 || 100 records .New variable l_processed_records added
1598 || npalanis 23-JUL-2002 Bug - 2421865
1599 || Validation code writtem for gpa value if negative.
1600 || Date validations added.
1601 || Lookup code columns are made upper before inserting.
1602 || asbala 16-OCT-2003 Bug 3130316. Import Process Source Category Rule processing changes,
1603 lookup caching related changes, and cursor parameterization.
1604 */
1605
1606 l_rule VARCHAR2(1);
1607 l_error_code igs_pe_ath_dtl_int.error_code%TYPE;
1608 l_status igs_pe_ath_dtl_int.status%TYPE;
1609 l_default_date DATE := IGS_GE_DATE.IGSDATE('4712/12/31');
1610 l_processed_records NUMBER(5) := 0;
1611 -- variables for logging
1612 l_prog_label VARCHAR2(4000);
1613 l_label VARCHAR2(4000);
1614 l_debug_str VARCHAR2(4000);
1615 l_enable_log VARCHAR2(1);
1616 l_request_id NUMBER(10);
1617
1618 l_interface_run_id igs_ad_interface_all.interface_run_id%TYPE;
1619
1620
1621 --Pick up the records for processing from the Athletic Details Interface Table
1622 CURSOR ath_dtl_cur(cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE) IS
1623 SELECT ai.*, i.person_id
1624 FROM igs_pe_ath_dtl_int ai,
1625 igs_ad_interface_all i
1626 WHERE ai.interface_run_id = cp_interface_run_id
1627 AND i.interface_id = ai.interface_id
1628 AND i.interface_run_id = cp_interface_run_id
1629 AND ai.status = '2';
1630
1631
1632 --Cursor to provide Duplicate check and Null handling while Updating.
1633 CURSOR dup_chk_ath_dtl_cur(cp_person_id igs_pe_athletic_dtl.person_id%TYPE) IS
1634 SELECT ROWID, ad.*
1635 FROM igs_pe_athletic_dtl ad
1636 WHERE person_id = cp_person_id;
1637
1638 --Cursor to check for Discrepancy
1639 dup_chk_ath_dtl_rec dup_chk_ath_dtl_cur%ROWTYPE;
1640 ath_dtl_rec ath_dtl_cur%ROWTYPE;
1641
1642
1643 -- Start Local Procedure crt_apcnt_ath_dtl
1644 PROCEDURE crt_apcnt_ath_dtl(
1645 p_ath_dtl_rec IN ath_dtl_cur%ROWTYPE
1646 )
1647 AS
1648 l_rowid VARCHAR2(25);
1649 l_athletic_details_id igs_pe_athletic_dtl.athletic_details_id%TYPE;
1650 l_error_code igs_pe_ath_dtl_int.error_code%TYPE;
1651 BEGIN
1652 -- Call Log header
1653 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
1654
1655 IF (l_request_id IS NULL) THEN
1656 l_request_id := fnd_global.conc_request_id;
1657 END IF;
1658
1659 l_label := 'igs.plsql.igs_ad_imp_003.crt_apcnt_ath_dtls.begin';
1660 l_debug_str := 'Interface ATHLETIC DTLS Id : ' || p_ath_dtl_rec.interface_athletic_dtls_id;
1661
1662 fnd_log.string_with_context( fnd_log.level_procedure,
1663 l_label,
1664 l_debug_str, NULL,
1665 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1666 END IF;
1667
1668 igs_pe_athletic_dtl_pkg.insert_row (
1669 x_rowid => l_rowid,
1670 x_athletic_details_id => l_athletic_details_id,
1671 x_person_id => p_ath_dtl_rec.person_id,
1672 x_athletic_gpa => p_ath_dtl_rec.athletic_gpa,
1673 x_eligibility_status_cd => p_ath_dtl_rec.eligibility_status_cd,
1674 x_predict_elig_code => p_ath_dtl_rec.predict_elig_code,
1675 x_tentative_adm_code => p_ath_dtl_rec.tentative_adm_code,
1676 x_review_date => p_ath_dtl_rec.review_date,
1677 x_comments => p_ath_dtl_rec.comments,
1678 x_mode => 'R'
1679 );
1680 l_error_code:=NULL;
1681 UPDATE igs_pe_ath_dtl_int
1682 SET status = '1',
1683 error_code = l_error_code
1684 WHERE interface_athletic_dtls_id = p_ath_dtl_rec.interface_athletic_dtls_id;
1685
1686 EXCEPTION
1687 WHEN OTHERS THEN
1688 l_error_code := 'E093'; -- Athletics Details Insertion Failed
1689
1690 UPDATE igs_pe_ath_dtl_int
1691 SET status = '3',
1692 error_code = l_error_code
1693 WHERE interface_athletic_dtls_id = p_ath_dtl_rec.interface_athletic_dtls_id;
1694
1695 -- Call Log detail
1696 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
1697
1698 IF (l_request_id IS NULL) THEN
1699 l_request_id := fnd_global.conc_request_id;
1700 END IF;
1701
1702 l_label := 'igs.plsql.igs_ad_imp_003.crt_apcnt_ath_dtl.exception '||'E093';
1703
1704 l_debug_str := 'igs_ad_imp_003.prc_apcnt_ath_dtls.crt_apcnt_ath_dtl'
1705 ||' Exception from igs_pe_athletic_dtl_Pkg.Insert_Row '
1706 || ' INTERFACE_ATHLETIC_DTLS_ID : ' ||
1707 (p_ath_dtl_rec.interface_athletic_dtls_id) ||
1708 ' Status : ' || '3' || ' ErrorCode : ' || l_error_code
1709 ||' SQLERRM:' || SQLERRM;
1710
1711 fnd_log.string_with_context( fnd_log.level_exception,
1712 l_label,
1713 l_debug_str, NULL,
1714 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1715 END IF;
1716
1717 IF l_enable_log = 'Y' THEN
1718 igs_ad_imp_001.logerrormessage(p_ath_dtl_rec.interface_athletic_dtls_id,'E093','IGS_PE_ATH_DTL_INT');
1719 END IF;
1720
1721 END crt_apcnt_ath_dtl;
1722 -- END OF LOCAL PROCEDURE crt_apcnt_ath_dtl
1723
1724
1725 -- Start Local function Validate_Record
1726
1727 FUNCTION validate_record(p_ath_dtl_rec IN ath_dtl_cur%ROWTYPE)
1728 RETURN BOOLEAN IS
1729 l_error_code igs_pe_ath_dtl_int.error_code%TYPE;
1730 l_birth_dt IGS_AD_INTERFACE.BIRTH_DT%TYPE;
1731 l_person_id IGS_AD_INTERFACE.PERSON_ID%TYPE;
1732
1733 CURSOR birth_dt_cur(p_person_id IGS_AD_INTERFACE.PERSON_ID%TYPE) IS
1734 SELECT Birth_date
1735 FROM IGS_PE_PERSON_BASE_V
1736 WHERE person_id= p_person_id;
1737
1738 BEGIN
1739
1740 -- ELIGIBILITY_STATUS_ID
1741 -- kumma, 2608360 replaced the igs_ad_code_classes with igs_lookup_values
1742
1743 IF p_ath_dtl_rec.eligibility_status_cd IS NOT NULL THEN
1744 IF NOT
1745 (igs_pe_pers_imp_001.validate_lookup_type_code('PE_ATH_ELG_STATUS',p_ath_dtl_rec.eligibility_status_cd,8405))
1746 THEN
1747 l_error_code := 'E095'; -- Person Athletics Details Validation Failed - Eligibility Status
1748 RAISE no_data_found;
1749 END IF;
1750 ELSE
1751 l_error_code := NULL;
1752 END IF;
1753
1754 -- TENTATIVE_ADM_CD
1755
1756 IF p_ath_dtl_rec.tentative_adm_code IS NOT NULL AND
1757 NOT
1758 (igs_pe_pers_imp_001.validate_lookup_type_code('PE_TENTATIVE_ADM_TYPE',p_ath_dtl_rec.tentative_adm_code,8405))
1759 THEN
1760 l_error_code := 'E096'; -- Person Athletics Details Validation Failed - Tentative Admission Code
1761 RAISE no_data_found;
1762 ELSE
1763 l_error_code := NULL;
1764 END IF;
1765
1766 -- PREDICT_ELIG_CODE
1767
1768 IF p_ath_dtl_rec.predict_elig_code IS NOT NULL AND
1769 NOT
1770 (igs_pe_pers_imp_001.validate_lookup_type_code('PE_PRE_ELIG_TYPE',p_ath_dtl_rec.predict_elig_code,8405))
1771 THEN
1772 l_error_code := 'E097'; -- Person Athletics Details Validation Failed - Predicted Eligibility
1773 RAISE no_data_found;
1774 ELSE
1775 l_error_code := NULL;
1776 END IF;
1777
1778 IF p_ath_dtl_rec.ATHLETIC_GPA IS NOT NULL AND p_ath_dtl_rec.ATHLETIC_GPA < 0 THEN
1779 l_error_code := 'E283';
1780 RAISE no_data_found;
1781 ELSE
1782 l_error_code := NULL;
1783 END IF;
1784
1785 IF p_ath_dtl_rec.review_date IS NOT NULL THEN
1786
1787 OPEN Birth_dt_cur(p_ath_dtl_rec.person_id) ;
1788 FETCH Birth_dt_cur INTO l_birth_dt;
1789 IF l_birth_dt IS NOT NULL AND l_birth_dt > p_ath_dtl_rec.review_date THEN
1790 l_error_code := 'E284';
1791 CLOSE Birth_dt_cur;
1792 RAISE no_data_found;
1793 ELSE
1794 l_error_code := NULL;
1795 END IF;
1796 CLOSE Birth_dt_cur;
1797 END IF;
1798 RETURN TRUE;
1799 EXCEPTION
1800 WHEN OTHERS THEN
1801
1802 UPDATE igs_pe_ath_dtl_int
1803 SET status = '3',
1804 error_code = l_error_code
1805 WHERE interface_athletic_dtls_id = p_ath_dtl_rec.interface_athletic_dtls_id;
1806
1807 -- Call Log detail
1808 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
1809
1810 IF (l_request_id IS NULL) THEN
1811 l_request_id := fnd_global.conc_request_id;
1812 END IF;
1813
1814 l_label := 'igs.plsql.igs_ad_imp_003.prc_apcnt_ath_dtls.exception '||l_error_code;
1815
1816 l_debug_str := 'igs_ad_imp_003.prc_apcnt_ath_dtls.validate_record '
1817 || ' Validation Failed for '
1818 || ' INTERFACE_ATHLETIC_DTLS_ID : ' ||
1819 (p_ath_dtl_rec.interface_athletic_dtls_id) ||
1820 ' Status : ' || '3' || ' ErrorCode : ' ||
1821 l_error_code||' SQLERRM:' || SQLERRM;
1822
1823 fnd_log.string_with_context( fnd_log.level_exception,
1824 l_label,
1825 l_debug_str, NULL,
1826 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1827 END IF;
1828
1829 IF l_enable_log = 'Y' THEN
1830 igs_ad_imp_001.logerrormessage(p_ath_dtl_rec.interface_athletic_dtls_id,l_error_code,'IGS_PE_ATH_DTL_INT');
1831 END IF;
1832 RETURN FALSE;
1833 END validate_record;
1834 -- End Local function Validate_Record
1835
1836 BEGIN
1837
1838 -- Call Log header
1839 l_enable_log := igs_ad_imp_001.g_enable_log;
1840 l_prog_label := 'igs.plsql.igs_ad_imp_003.prc_apcnt_ath_dtls';
1841 l_label := 'igs.plsql.igs_ad_imp_003.prc_apcnt_ath_dtls.';
1842
1843 l_interface_run_id := igs_ad_imp_001.g_interface_run_id;
1844
1845 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
1846
1847 IF (l_request_id IS NULL) THEN
1848 l_request_id := fnd_global.conc_request_id;
1849 END IF;
1850
1851 l_label := 'igs.plsql.igs_ad_imp_003.prc_apcnt_ath_dtls.begin';
1852 l_debug_str := 'Batch Id : ' || p_batch_id ;
1853
1854 fnd_log.string_with_context( fnd_log.level_procedure,
1855 l_label,
1856 l_debug_str, NULL,
1857 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1858 END IF;
1859
1860 l_rule := igs_ad_imp_001.find_source_cat_rule(
1861 p_source_type_id => P_SOURCE_TYPE_ID,
1862 p_category => 'PERSON_ATHLETICS');
1863
1864
1865 -- 1.If rule is E or I, then if the match_ind is not null, the combination is invalid
1866 IF l_rule IN ('E','I') THEN
1867
1868 UPDATE igs_pe_ath_dtl_int
1869 SET status = cst_stat_val_3,
1870 ERROR_CODE = cst_err_val_695 -- Error code depicting incorrect combination
1871 WHERE match_ind IS NOT NULL
1872 AND interface_run_id = l_interface_run_id
1873 AND status = cst_stat_val_2;
1874 END IF;
1875
1876 --2. If rule is E and duplicate exists, update match_ind to 19 and status to 1
1877 IF l_rule = 'E' THEN
1878 UPDATE igs_pe_ath_dtl_int ai
1879 SET status = cst_stat_val_1,
1880 match_ind = cst_mi_val_19
1881 WHERE ai.interface_run_id = l_interface_run_id
1882 AND ai.status = cst_stat_val_2
1883 AND EXISTS( SELECT '1'
1884 FROM igs_pe_athletic_dtl pe, igs_ad_interface_all i
1885 WHERE i.interface_id = ai.interface_id
1886 AND i.interface_run_id = l_interface_run_id
1887 AND pe.person_id = NVL(i.person_id, -99)
1888 );
1889 END IF;
1890
1891 -- 3.If rule is R and there match_ind is 18,19,22 or 23 then the records must have been
1892 -- processed in prior runs and didn't get updated .. update to status 1
1893 IF l_rule = 'R' THEN
1894 UPDATE igs_pe_ath_dtl_int
1895 SET status = cst_stat_val_1
1896 WHERE interface_run_id = l_interface_run_id
1897 AND match_ind IN (cst_mi_val_18,cst_mi_val_19,cst_mi_val_22,cst_mi_val_23)
1898 AND status=cst_stat_val_2;
1899 END IF;
1900
1901 -- 4.If rule is R and match_ind is neither 21 nor 25 then error
1902 IF l_rule = 'R' THEN
1903 UPDATE igs_pe_ath_dtl_int
1904 SET status = cst_stat_val_3,
1905 ERROR_CODE = cst_err_val_695
1906 WHERE interface_run_id = l_interface_run_id
1907 AND (match_ind IS NOT NULL AND match_ind NOT IN (cst_mi_val_21,cst_mi_val_25))
1908 AND status=cst_stat_val_2;
1909 END IF;
1910
1911 -- 5.If rule is R, set duplicated records with no discrepancy to status 1 and match_ind 23
1912 IF l_rule = 'R' THEN
1913 UPDATE igs_pe_ath_dtl_int mi
1914 SET status = cst_stat_val_1,
1915 match_ind = cst_mi_val_23
1916 WHERE mi.interface_run_id = l_interface_run_id
1917 AND mi.match_ind IS NULL
1918 AND mi.status = cst_stat_val_2
1919 AND EXISTS ( SELECT '1'
1920 FROM igs_pe_athletic_dtl pe, igs_ad_interface_all i
1921 WHERE i.interface_id = mi.interface_id
1922 AND i.interface_run_id = l_interface_run_id
1923 AND pe.person_id = NVL(i.person_id, -99) AND
1924 NVL(pe.athletic_gpa, -99) = NVL(mi.athletic_gpa,-99) AND
1925 NVL(UPPER(pe.eligibility_status_cd), '~') = NVL(UPPER(mi.eligibility_status_cd),'~') AND
1926 NVL(UPPER(pe.predict_elig_code), '~') = NVL(UPPER(mi.predict_elig_code),'~') AND
1927 NVL(UPPER(pe.tentative_adm_code), '~') = NVL(UPPER(mi.tentative_adm_code),'~') AND
1928 NVL(TRUNC(pe.review_date),l_default_date)= NVL(TRUNC(mi.review_date),l_default_date) AND
1929 NVL(UPPER(pe.comments), '~') = NVL(UPPER(mi.comments), '~'));
1930 END IF;
1931
1932 -- 6.If rule in R records still exist, they are duplicates and have discrepancy .. update status=3,match_ind=20
1933 IF l_rule = 'R' THEN
1934 UPDATE igs_pe_ath_dtl_int ai
1935 SET status = cst_stat_val_3,
1936 match_ind = cst_mi_val_18,
1937 dup_athletic_details_id = (SELECT athletic_details_id
1938 FROM igs_pe_athletic_dtl pe, igs_ad_interface_all i
1939 WHERE i.interface_id = ai.interface_id
1940 AND i.interface_run_id = l_interface_run_id
1941 AND pe.person_id = NVL(i.person_id, -99))
1942 WHERE ai.interface_run_id = l_interface_run_id
1943 AND ai.match_ind IS NULL
1944 AND ai.status = cst_stat_val_2
1945 AND EXISTS (SELECT '1'
1946 FROM igs_pe_athletic_dtl pe, igs_ad_interface_all i
1947 WHERE i.interface_id = ai.interface_id
1948 AND i.interface_run_id = l_interface_run_id
1949 AND pe.person_id = NVL(i.person_id, -99));
1950 END IF;
1951
1952 FOR ath_dtl_rec IN ath_dtl_cur(l_interface_run_id) LOOP
1953
1954 l_processed_records := l_processed_records + 1 ;
1955
1956 ath_dtl_rec.eligibility_status_cd := UPPER(ath_dtl_rec.eligibility_status_cd);
1957 ath_dtl_rec.tentative_adm_code := UPPER(ath_dtl_rec.tentative_adm_code);
1958 ath_dtl_rec.predict_elig_code := UPPER(ath_dtl_rec.predict_elig_code);
1959 ath_dtl_rec.review_date := TRUNC(ath_dtl_rec.review_date);
1960
1961 IF validate_record(ath_dtl_rec) THEN
1962 -- For each record picked up do the following :
1963 -- Check to see if the record already exists.
1964 dup_chk_ath_dtl_rec.athletic_details_id := NULL;
1965 OPEN dup_chk_ath_dtl_cur(ath_dtl_rec.person_id);
1966 FETCH dup_chk_ath_dtl_cur INTO dup_chk_ath_dtl_rec;
1967 CLOSE dup_chk_ath_dtl_cur;
1968
1969 --If its a duplicate record find the source category rule for that Source Category.
1970 IF dup_chk_ath_dtl_rec.athletic_details_id IS NOT NULL THEN
1971
1972 IF l_rule = 'I' THEN
1973
1974 BEGIN
1975 igs_pe_athletic_dtl_pkg.update_row (
1976 x_rowid => dup_chk_ath_dtl_rec.rowid,
1977 x_athletic_details_id => dup_chk_ath_dtl_rec.athletic_details_id,
1978 x_person_id => NVL(ath_dtl_rec.person_id,dup_chk_ath_dtl_rec.person_id),
1979 x_athletic_gpa => NVL(ath_dtl_rec.athletic_gpa,dup_chk_ath_dtl_rec.athletic_gpa),
1980 x_eligibility_status_cd => NVL(ath_dtl_rec.eligibility_status_cd,dup_chk_ath_dtl_rec.eligibility_status_cd),
1981 x_predict_elig_code => NVL(ath_dtl_rec.predict_elig_code,dup_chk_ath_dtl_rec.predict_elig_code),
1982 x_tentative_adm_code => NVL(ath_dtl_rec.tentative_adm_code,dup_chk_ath_dtl_rec.tentative_adm_code),
1983 x_review_date => NVL(ath_dtl_rec.review_date,dup_chk_ath_dtl_rec.review_date),
1984 x_comments => NVL(ath_dtl_rec.comments,dup_chk_ath_dtl_rec.comments),
1985 x_mode => 'R'
1986 );
1987 l_error_code := NULL;
1988 l_status := '1';
1989
1990 UPDATE igs_pe_ath_dtl_int
1991 SET status = l_status,
1992 error_code = l_error_code,
1993 match_ind = cst_mi_val_18 -- '18' Match occured and used import values
1994 WHERE interface_athletic_dtls_id = ath_dtl_rec.interface_athletic_dtls_id;
1995
1996 EXCEPTION
1997 WHEN OTHERS THEN
1998 l_error_code := 'E094'; -- Could not update Person Athletics Details
1999 l_status := '3';
2000
2001 UPDATE igs_pe_ath_dtl_int
2002 SET status = l_status,
2003 error_code = l_error_code
2004 WHERE interface_athletic_dtls_id = ath_dtl_rec.interface_athletic_dtls_id;
2005
2006 -- Call Log detail
2007 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
2008
2009 IF (l_request_id IS NULL) THEN
2010 l_request_id := fnd_global.conc_request_id;
2011 END IF;
2012
2013 l_label := 'igs.plsql.igs_ad_imp_003.prc_apcnt_ath_dtls.exception2 '||l_error_code;
2014
2015 l_debug_str := 'igs_ad_imp_003.prc_apcnt_ath_dtls'
2016 ||' Exception from igs_pe_athletic_dtl_Pkg.Update_Row '
2017 || ' INTERFACE_ATHLETIC_DTLS_ID : ' ||
2018 (ath_dtl_rec.interface_athletic_dtls_id) ||
2019 ' Status : ' || '3' || ' ErrorCode : ' ||
2020 l_error_code ||' SQLERRM:' || SQLERRM;
2021
2022 fnd_log.string_with_context( fnd_log.level_exception,
2023 l_label,
2024 l_debug_str, NULL,
2025 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
2026 END IF;
2027
2028 IF l_enable_log = 'Y' THEN
2029 igs_ad_imp_001.logerrormessage(ath_dtl_rec.interface_athletic_dtls_id,l_error_code,'IGS_PE_ATH_DTL_INT');
2030 END IF;
2031
2032 END;
2033
2034 ELSIF l_rule = 'R' THEN
2035 IF ath_dtl_rec.match_ind = '21' THEN -- '21' Match reviewed and to be imported
2036 BEGIN
2037 igs_pe_athletic_dtl_pkg.update_row (
2038 x_rowid => dup_chk_ath_dtl_rec.rowid,
2039 x_athletic_details_id => dup_chk_ath_dtl_rec.athletic_details_id,
2040 x_person_id => NVL(ath_dtl_rec.person_id,dup_chk_ath_dtl_rec.person_id),
2041 x_athletic_gpa => NVL(ath_dtl_rec.athletic_gpa,dup_chk_ath_dtl_rec.athletic_gpa),
2042 x_eligibility_status_cd => NVL(ath_dtl_rec.eligibility_status_cd,dup_chk_ath_dtl_rec.eligibility_status_cd),
2043 x_predict_elig_code => NVL(ath_dtl_rec.predict_elig_code,dup_chk_ath_dtl_rec.predict_elig_code),
2044 x_tentative_adm_code => NVL(ath_dtl_rec.tentative_adm_code,dup_chk_ath_dtl_rec.tentative_adm_code),
2045 x_review_date => NVL(ath_dtl_rec.review_date,dup_chk_ath_dtl_rec.review_date),
2046 x_comments => NVL(ath_dtl_rec.comments,dup_chk_ath_dtl_rec.comments),
2047 x_mode => 'R'
2048 );
2049 l_error_code := NULL;
2050 l_status := '1';
2051
2052 UPDATE igs_pe_ath_dtl_int
2053 SET status = l_status,
2054 error_code = l_error_code,
2055 match_ind = cst_mi_val_18
2056 WHERE interface_athletic_dtls_id = ath_dtl_rec.interface_athletic_dtls_id;
2057
2058 EXCEPTION
2059 WHEN OTHERS THEN
2060 l_error_code := 'E094'; -- Could not update Person Athletics Details
2061 l_status := '3';
2062
2063 UPDATE igs_pe_ath_dtl_int
2064 SET status = l_status,
2065 error_code = l_error_code
2066 WHERE interface_athletic_dtls_id = ath_dtl_rec.interface_athletic_dtls_id;
2067
2068 -- Call Log detail
2069 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
2070
2071 IF (l_request_id IS NULL) THEN
2072 l_request_id := fnd_global.conc_request_id;
2073 END IF;
2074
2075 l_label := 'igs.plsql.igs_ad_imp_003.prc_apcnt_ath_dtls.exception1 '||l_error_code;
2076
2077 l_debug_str := 'igs_ad_imp_003.prc_apcnt_ath_dtls'
2078 ||' Exception from igs_pe_athletic_dtl_Pkg.Update_Row '
2079 || ' INTERFACE_ATHLETIC_DTLS_ID : ' ||
2080 (ath_dtl_rec.interface_athletic_dtls_id) ||
2081 ' Status : ' || '3' || ' ErrorCode : ' ||
2082 l_error_code ||' SQLERRM:' || SQLERRM;
2083
2084 fnd_log.string_with_context( fnd_log.level_exception,
2085 l_label,
2086 l_debug_str, NULL,
2087 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
2088 END IF;
2089
2090 IF l_enable_log = 'Y' THEN
2091 igs_ad_imp_001.logerrormessage(ath_dtl_rec.interface_athletic_dtls_id,l_error_code,'IGS_PE_ATH_DTL_INT');
2092 END IF;
2093
2094 END;
2095 END IF; -- ath_dtl_rec.MATCH_IND check
2096
2097 END IF;-- l_rule check for 'I','R' or 'E'.
2098
2099 ELSE -- If its not a duplicate record then Create a new record in OSS
2100 crt_apcnt_ath_dtl (p_ath_dtl_rec => ath_dtl_rec);
2101
2102 END IF; -- Record existance in IGS_PE_ATHLETIC_DTL check
2103 END IF; -- Check for Validate Record
2104
2105 IF l_processed_records = 100 THEN
2106 COMMIT;
2107 l_processed_records := 0;
2108 END IF;
2109
2110 END LOOP;
2111 END prc_apcnt_ath_dtls;
2112
2113 --
2114 -- Starts procedure PRC_APCNT_ATH_PRG
2115 --
2116 PROCEDURE prc_apcnt_ath_prg
2117 (
2118 p_source_type_id IN NUMBER,
2119 p_batch_id IN NUMBER )
2120 AS
2121 /*
2122 || Created By : [email protected]
2123 || Created On : 14-NOV-2001
2124 || Purpose : This is a private procedure. This procedure is for importing person Athletic Program Information.
2125 || DLD: Person Interface DLD. Enh Bug# 2103692.
2126 || Known limitations, enhancements or remarks :
2127 || Change History :
2128 || Who When What
2129 || (reverse chronological order - newest change first)
2130 || npalanis 6-JAN-2003 Bug : 2734697
2131 || code added to commit after import of every
2132 || 100 records .New variable l_processed_records added
2133 || npalanis 23-JUL-2002 Bug - 2421865
2134 || Date validations added.
2135 || asbala 16-OCT-2003 Bug 3130316. Import Process Source Category Rule processing changes,
2136 lookup caching related changes, and cursor parameterization.
2137 */
2138
2139 l_rule VARCHAR2(1);
2140 l_error_code igs_pe_ath_prg_int.error_code%TYPE;
2141 l_status igs_pe_ath_prg_int.status%TYPE;
2142 l_default_date DATE := IGS_GE_DATE.IGSDATE('4712/12/31');
2143 l_processed_records NUMBER(5) := 0;
2144 -- variables for logging
2145 l_prog_label VARCHAR2(4000);
2146 l_label VARCHAR2(4000);
2147 l_debug_str VARCHAR2(4000);
2148 l_enable_log VARCHAR2(1);
2149 l_request_id NUMBER(10);
2150
2151 l_interface_run_id igs_ad_interface_all.interface_run_id%TYPE;
2152
2153 --Pick up the records for processing from the Athletic Programs Interface Table
2154 CURSOR ath_prg_cur(cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE) IS
2155 SELECT ai.*, i.person_id
2156 FROM igs_pe_ath_prg_int ai, igs_ad_interface_all i
2157 WHERE ai.interface_run_id = cp_interface_run_id
2158 AND i.interface_id = ai.interface_id
2159 AND i.interface_run_id = cp_interface_run_id
2160 AND ai.status = '2';
2161
2162
2163 --Cursor to check for duplicates and provide Null handling while Updating.
2164 CURSOR dup_chk_ath_prg_cur(cp_person_id igs_pe_athletic_prg.person_id%TYPE,
2165 cp_athletic_prg_code igs_pe_athletic_prg.athletic_prg_code%TYPE,
2166 cp_start_date igs_pe_athletic_prg.start_date%TYPE) IS
2167 SELECT ROWID, ap.*
2168 FROM igs_pe_athletic_prg ap
2169 WHERE person_id = cp_person_id AND
2170 UPPER(athletic_prg_code) = UPPER(cp_athletic_prg_code) AND
2171 start_date = cp_start_date;
2172
2173
2174 dup_chk_ath_prg_rec dup_chk_ath_prg_cur%ROWTYPE;
2175 ath_prg_rec ath_prg_cur%ROWTYPE;
2176
2177
2178 -- Start Local Procedure crt_apcnt_ath_prg
2179 PROCEDURE crt_apcnt_ath_prg(
2180 p_ath_prg_rec IN ath_prg_cur%ROWTYPE
2181 )
2182 AS
2183 l_rowid VARCHAR2(25);
2184 l_athletic_prg_id igs_pe_athletic_prg.athletic_prg_id%TYPE;
2185 l_error_code igs_pe_ath_prg_int.error_code%TYPE;
2186 BEGIN
2187 -- Call Log header
2188 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
2189
2190 IF (l_request_id IS NULL) THEN
2191 l_request_id := fnd_global.conc_request_id;
2192 END IF;
2193
2194 l_label := 'igs.plsql.igs_ad_imp_003.crt_apcnt_ath_prg.begin';
2195 l_debug_str := 'Interface athletic prg Id : ' || p_ath_prg_rec.interface_athletic_prg_id;
2196
2197 fnd_log.string_with_context( fnd_log.level_procedure,
2198 l_label,
2199 l_debug_str, NULL,
2200 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
2201 END IF;
2202
2203
2204 igs_pe_athletic_prg_pkg.insert_row (
2205 x_rowid => l_rowid,
2206 x_athletic_prg_id => l_athletic_prg_id,
2207 x_person_id => p_ath_prg_rec.person_id,
2208 x_athletic_prg_code => p_ath_prg_rec.athletic_prg_code,
2209 x_rating => p_ath_prg_rec.rating,
2210 x_start_date => p_ath_prg_rec.start_date,
2211 x_end_date => p_ath_prg_rec.end_date,
2212 x_recruited_ind => p_ath_prg_rec.recruited_ind,
2213 x_participating_ind => p_ath_prg_rec.participating_ind,
2214 x_last_update_dt => p_ath_prg_rec.last_update_date,
2215 x_mode => 'R'
2216 );
2217 l_error_code:=NULL;
2218 UPDATE igs_pe_ath_prg_int
2219 SET status = '1',
2220 error_code = l_error_code
2221 WHERE interface_athletic_prg_id = p_ath_prg_rec.interface_athletic_prg_id;
2222
2223 EXCEPTION
2224 WHEN OTHERS THEN
2225 l_error_code := 'E099'; -- Athletics Program Insertion Failed
2226
2227 UPDATE igs_pe_ath_prg_int
2228 SET status = '3',
2229 error_code = l_error_code
2230 WHERE interface_athletic_prg_id = p_ath_prg_rec.interface_athletic_prg_id;
2231
2232 -- Call Log detail
2233 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
2234
2235 IF (l_request_id IS NULL) THEN
2236 l_request_id := fnd_global.conc_request_id;
2237 END IF;
2238
2239 l_label := 'igs.plsql.igs_ad_imp_003.crt_apcnt_ath_dtl.exception '||l_error_code;
2240
2241 l_debug_str := 'igs_ad_imp_003.prc_apcnt_ath_prg.crt_apcnt_ath_prg'
2242 ||' Exception from igs_pe_athletic_prg_Pkg.Insert_Row '
2243 || ' INTERFACE_ATHLETIC_PRG_ID : ' ||
2244 (p_ath_prg_rec.interface_athletic_prg_id) ||
2245 ' Status : ' || '3' || ' ErrorCode : ' || l_error_code
2246 ||' SQLERRM:' || SQLERRM;
2247
2248 fnd_log.string_with_context( fnd_log.level_exception,
2249 l_label,
2250 l_debug_str, NULL,
2251 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
2252 END IF;
2253
2254 IF l_enable_log = 'Y' THEN
2255 igs_ad_imp_001.logerrormessage(p_ath_prg_rec.interface_athletic_prg_id,l_error_code,'IGS_PE_ATH_PRG_INT');
2256 END IF;
2257 END crt_apcnt_ath_prg;
2258 -- END OF LOCAL PROCEDURE crt_apcnt_ath_prg
2259
2260 -- Start Local function Validate_Record
2261 FUNCTION validate_record(p_ath_prg_rec IN ath_prg_cur%ROWTYPE)
2262 RETURN BOOLEAN IS
2263 l_error_code igs_pe_ath_prg_int.error_code%TYPE;
2264 l_birth_dt IGS_AD_INTERFACE.BIRTH_DT%TYPE;
2265
2266 CURSOR birth_dt_cur(p_person_id IGS_AD_INTERFACE.PERSON_ID%TYPE) IS
2267 SELECT Birth_date
2268 FROM IGS_PE_PERSON_BASE_V
2269 WHERE person_id= p_person_id;
2270 BEGIN
2271
2272 -- ATHLETIC_PRG_CD
2273 --kumma, 2608360 replaced the igs_ad_code_classes code with igs_lookup_values
2274
2275 /*
2276 FROM igs_ad_code_classes cc
2277 WHERE cc.class='SPORTS_TYPES' AND
2278 NVL(closed_ind,'N') = 'N' AND
2279 code_id = p_ath_prg_rec.athletic_prg_code;
2280 */
2281
2282
2283 IF (p_ath_prg_rec.athletic_prg_code IS NOT NULL AND
2284 NOT
2285 (igs_pe_pers_imp_001.validate_lookup_type_code('PE_ATH_PRG_TYPE',p_ath_prg_rec.athletic_prg_code,8405)))
2286 OR(p_ath_prg_rec.athletic_prg_code IS NULL) THEN
2287 l_error_code := 'E101'; -- Person Athletics Program Validation Failed - Athletic Program Code
2288 RAISE no_data_found;
2289 ELSE
2290 l_error_code := NULL;
2291 END IF;
2292
2293 OPEN Birth_dt_cur(p_ath_prg_rec.person_id) ;
2294 FETCH Birth_dt_cur INTO l_birth_dt;
2295 IF l_birth_dt IS NOT NULL AND l_birth_dt > p_ath_prg_rec.start_date THEN
2296 l_error_code := 'E222';
2297 CLOSE Birth_dt_cur;
2298 RAISE no_data_found;
2299 END IF;
2300 CLOSE Birth_dt_cur;
2301
2302
2303 -- START_DATE and END_DATE
2304 IF p_ath_prg_rec.end_date IS NOT NULL THEN
2305 IF p_ath_prg_rec.end_date < p_ath_prg_rec.start_date THEN
2306 l_error_code := 'E208'; -- Person Athletics Program Validation Failed - End Date
2307 RAISE no_data_found;
2308 END IF;
2309 END IF;
2310
2311 -- RECRUITED_IND
2312 IF p_ath_prg_rec.recruited_ind NOT IN ('Y','N')THEN
2313 l_error_code := 'E103'; -- Person Athletics Program Validation Failed - Recruited Indicator
2314 RAISE no_data_found;
2315 END IF;
2316
2317 -- PARTICIPATING_IND
2318 IF p_ath_prg_rec.participating_ind NOT IN ('Y', 'N') THEN
2319 l_error_code := 'E104'; -- Person Athletics Program Validation Failed - Participating Indicator
2320 RAISE no_data_found;
2321 END IF;
2322
2323 RETURN TRUE;
2324 EXCEPTION
2325 WHEN OTHERS THEN
2326
2327 UPDATE igs_pe_ath_prg_int
2328 SET status = '3',
2329 error_code = l_error_code
2330 WHERE interface_athletic_prg_id = p_ath_prg_rec.interface_athletic_prg_id;
2331
2332 -- Call Log detail
2333 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
2334
2335 IF (l_request_id IS NULL) THEN
2336 l_request_id := fnd_global.conc_request_id;
2337 END IF;
2338
2339 l_label := 'igs.plsql.igs_ad_imp_003.prc_apcnt_ath_prg.val_exception '||l_error_code;
2340
2341 l_debug_str := 'igs_ad_imp_003.prc_apcnt_ath_prg.validate_record'
2342 || ' Validation Failed for'
2343 || ' INTERFACE_ATHLETIC_PRG_ID : ' ||
2344 (p_ath_prg_rec.interface_athletic_prg_id) ||
2345 ' Status : ' || '3' || ' ErrorCode : ' ||
2346 l_error_code||' SQLERRM:' || SQLERRM;
2347
2348 fnd_log.string_with_context( fnd_log.level_exception,
2349 l_label,
2350 l_debug_str, NULL,
2351 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
2352 END IF;
2353
2354 IF l_enable_log = 'Y' THEN
2355 igs_ad_imp_001.logerrormessage(p_ath_prg_rec.interface_athletic_prg_id,l_error_code,'IGS_PE_ATH_PRG_INT');
2356 END IF;
2357
2358 RETURN FALSE;
2359 END validate_record;
2360 -- End Local function Validate_Record
2361 -- Start of main procedure
2362 BEGIN
2363
2364 l_enable_log := igs_ad_imp_001.g_enable_log;
2365 l_prog_label := 'igs.plsql.igs_ad_imp_003.prc_apcnt_ath_prg';
2366 l_label := 'igs.plsql.igs_ad_imp_003.prc_apcnt_ath_prg.';
2367
2368 l_interface_run_id := igs_ad_imp_001.g_interface_run_id;
2369
2370 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
2371
2372 IF (l_request_id IS NULL) THEN
2373 l_request_id := fnd_global.conc_request_id;
2374 END IF;
2375
2376 l_label := 'igs.plsql.igs_ad_imp_003.prc_apcnt_ath_prg.begin';
2377 l_debug_str := 'Batch Id : ' || p_batch_id ;
2378
2379 fnd_log.string_with_context( fnd_log.level_procedure,
2380 l_label,
2381 l_debug_str, NULL,
2382 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
2383 END IF;
2384
2385 l_rule := igs_ad_imp_001.find_source_cat_rule(
2386 p_source_type_id => P_SOURCE_TYPE_ID,
2387 p_category => 'PERSON_ATHLETICS');
2388
2389
2390 -- 1.If rule is E or I, then if the match_ind is not null, the combination is invalid
2391 IF l_rule IN ('E','I') THEN
2392 UPDATE igs_pe_ath_prg_int
2393 SET status = cst_stat_val_3,
2394 ERROR_CODE = cst_err_val_695 -- Error code depicting incorrect combination
2395 WHERE match_ind IS NOT NULL
2396 AND interface_run_id = l_interface_run_id
2397 AND status = cst_stat_val_2;
2398 END IF;
2399
2400 --2. If rule is E and duplicate exists, update match_ind to 19 and status to 1
2401 IF l_rule = 'E' THEN
2402 --skpandey, Bug#3702782, Changed select statement for optimization
2403 UPDATE igs_pe_ath_prg_int ai
2404 SET status = cst_stat_val_1,
2405 match_ind = cst_mi_val_19
2406 WHERE ai.interface_run_id = l_interface_run_id
2407 AND ai.status = cst_stat_val_2
2408 AND EXISTS( SELECT '1'
2409 FROM igs_pe_athletic_prg pe, igs_ad_interface_all i
2410 WHERE i.interface_id = ai.interface_id
2411 AND i.interface_run_id = l_interface_run_id
2412 AND pe.person_id = NVL(i.person_id, -99) AND
2413 pe.athletic_prg_code = UPPER(ai.athletic_prg_code) AND
2414 pe.start_date = TRUNC(ai.start_date)
2415 );
2416 END IF;
2417
2418 -- 3.If rule is R and there match_ind is 18,19,22 or 23 then the records must have been
2419 -- processed in prior runs and didn't get updated .. update to status 1
2420 IF l_rule = 'R' THEN
2421 UPDATE igs_pe_ath_prg_int
2422 SET status = cst_stat_val_1
2423 WHERE interface_run_id = l_interface_run_id
2424 AND match_ind IN (cst_mi_val_18,cst_mi_val_19,cst_mi_val_22,cst_mi_val_23)
2425 AND status=cst_stat_val_2;
2426 END IF;
2427
2428 -- 4.If rule is R and match_ind is neither 21 nor 25 then error
2429 IF l_rule = 'R' THEN
2430 UPDATE igs_pe_ath_prg_int
2431 SET status = cst_stat_val_3,
2432 ERROR_CODE = cst_err_val_695
2433 WHERE interface_run_id = l_interface_run_id
2434 AND (match_ind IS NOT NULL AND match_ind NOT IN (cst_mi_val_21,cst_mi_val_25))
2435 AND status=cst_stat_val_2;
2436 END IF;
2437
2438 -- 5.If rule is R, set duplicated records with no discrepancy to status 1 and match_ind 23
2439 IF l_rule = 'R' THEN
2440 --skpandey, Bug#3702782, Changed select statement for optimization
2441 UPDATE igs_pe_ath_prg_int ai
2442 SET status = cst_stat_val_1,
2443 match_ind = cst_mi_val_23
2444 WHERE ai.interface_run_id = l_interface_run_id
2445 AND ai.match_ind IS NULL
2446 AND ai.status = cst_stat_val_2
2447 AND EXISTS ( SELECT '1'
2448 FROM igs_pe_athletic_prg pe, igs_ad_interface_all i
2449 WHERE i.interface_id = ai.interface_id
2450 AND i.interface_run_id = l_interface_run_id
2451 AND pe.person_id = NVL(i.person_id, -99) AND
2452 pe.athletic_prg_code = UPPER(ai.athletic_prg_code) AND
2453 pe.start_date = TRUNC(ai.start_date) AND
2454 NVL(UPPER(pe.rating), '~') = NVL(UPPER(ai.rating),'~') AND
2455 NVL(pe.end_date, l_default_date ) = NVL(TRUNC(ai.end_date),l_default_date) AND
2456 NVL(UPPER(pe.recruited_ind),'N') = NVL(UPPER(ai.recruited_ind),'N') AND
2457 NVL(UPPER(pe.participating_ind),'N') = NVL(UPPER(ai.participating_ind),'N'));
2458 END IF;
2459
2460 -- 6.If rule in R records still exist, they are duplicates and have discrepancy .. update status=3,match_ind=20
2461 IF l_rule = 'R' THEN
2462 --skpandey, Bug#3702782, Changed select statement for optimization
2463 UPDATE igs_pe_ath_prg_int ai
2464 SET status = cst_stat_val_3,
2465 match_ind = cst_mi_val_20,
2466 dup_athletic_prg_id = (SELECT athletic_prg_id
2467 FROM igs_pe_athletic_prg pe, igs_ad_interface_all i
2468 WHERE i.interface_id = ai.interface_id
2469 AND i.interface_run_id = l_interface_run_id
2470 AND pe.person_id = NVL(i.person_id, -99) AND
2471 pe.athletic_prg_code = UPPER(ai.athletic_prg_code) AND
2472 pe.start_date = TRUNC(ai.start_date))
2473 WHERE ai.interface_run_id = l_interface_run_id
2474 AND ai.match_ind IS NULL
2475 AND ai.status = cst_stat_val_2
2476 AND EXISTS (SELECT '1'
2477 FROM igs_pe_athletic_prg pe, igs_ad_interface_all i
2478 WHERE i.interface_id = ai.interface_id
2479 AND i.interface_run_id = l_interface_run_id
2480 AND pe.person_id = NVL(i.person_id, -99) AND
2481 pe.athletic_prg_code = UPPER(ai.athletic_prg_code) AND
2482 pe.start_date = TRUNC(ai.start_date));
2483 END IF;
2484
2485 FOR ath_prg_rec IN ath_prg_cur(l_interface_run_id) LOOP
2486
2487 l_processed_records := l_processed_records + 1 ;
2488
2489 ath_prg_rec.athletic_prg_code := UPPER(ath_prg_rec.athletic_prg_code);
2490 ath_prg_rec.recruited_ind := UPPER(ath_prg_rec.recruited_ind);
2491 ath_prg_rec.participating_ind := UPPER(ath_prg_rec.participating_ind);
2492 ath_prg_rec.end_date := TRUNC(ath_prg_rec.end_date);
2493 ath_prg_rec.start_date := TRUNC(ath_prg_rec.start_date);
2494
2495 IF validate_record(ath_prg_rec) THEN
2496 -- For each record picked up do the following :
2497 -- Check to see if the record already exists.
2498 dup_chk_ath_prg_rec.athletic_prg_id := NULL;
2499 OPEN dup_chk_ath_prg_cur(ath_prg_rec.person_id,
2500 ath_prg_rec.athletic_prg_code,
2501 ath_prg_rec.start_date );
2502 FETCH dup_chk_ath_prg_cur INTO dup_chk_ath_prg_rec;
2503 CLOSE dup_chk_ath_prg_cur;
2504
2505 --If its a duplicate record find the source category rule for that Source Category.
2506 IF dup_chk_ath_prg_rec.athletic_prg_id IS NOT NULL THEN
2507 IF l_rule = 'I' THEN
2508 BEGIN
2509 igs_pe_athletic_prg_pkg.update_row (
2510 x_rowid => dup_chk_ath_prg_rec.rowid,
2511 x_athletic_prg_id => dup_chk_ath_prg_rec.athletic_prg_id,
2512 x_person_id => NVL(ath_prg_rec.person_id,dup_chk_ath_prg_rec.person_id),
2513 x_athletic_prg_code => ath_prg_rec.athletic_prg_code,
2514 x_rating => NVL(ath_prg_rec.rating,dup_chk_ath_prg_rec.rating),
2515 x_start_date => ath_prg_rec.start_date,
2516 x_end_date => NVL(ath_prg_rec.end_date,dup_chk_ath_prg_rec.end_date),
2517 x_recruited_ind => ath_prg_rec.recruited_ind,
2518 x_participating_ind => ath_prg_rec.participating_ind,
2519 x_last_update_dt =>nvl(ath_prg_rec.last_update_date,dup_chk_ath_prg_rec.last_update_dt),
2520 x_mode => 'R'
2521 );
2522 l_error_code := NULL;
2523 l_status := '1';
2524
2525 UPDATE igs_pe_ath_prg_int
2526 SET status = l_status,
2527 error_code = l_error_code,
2528 match_ind = cst_mi_val_18 -- '18' Match occured and used import values
2529 WHERE interface_athletic_prg_id = ath_prg_rec.interface_athletic_prg_id;
2530
2531 EXCEPTION
2532 WHEN OTHERS THEN
2533 l_error_code := 'E100'; -- Could not update Person Athletics Program Details
2534 l_status := '3';
2535
2536 UPDATE igs_pe_ath_prg_int
2537 SET status = l_status,
2538 error_code = l_error_code
2539 WHERE interface_athletic_prg_id = ath_prg_rec.interface_athletic_prg_id;
2540
2541 -- Call Log detail
2542 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
2543
2544 IF (l_request_id IS NULL) THEN
2545 l_request_id := fnd_global.conc_request_id;
2546 END IF;
2547
2548 l_label := 'igs.plsql.igs_ad_imp_003.prc_apcnt_ath_prg.exception1 '||l_error_code;
2549
2550 l_debug_str := 'igs_ad_imp_003.prc_apcnt_ath_prg'
2551 ||' Exception from igs_pe_athletic_prg_Pkg.Update_Row '
2552 || ' INTERFACE_ATHLETIC_PRG_ID : ' ||
2553 (ath_prg_rec.interface_athletic_prg_id) ||
2554 ' Status : ' || '3' || ' ErrorCode : ' ||
2555 l_error_code||' SQLERRM:' || SQLERRM;
2556
2557 fnd_log.string_with_context( fnd_log.level_exception,
2558 l_label,
2559 l_debug_str, NULL,
2560 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
2561 END IF;
2562
2563 IF l_enable_log = 'Y' THEN
2564 igs_ad_imp_001.logerrormessage(ath_prg_rec.interface_athletic_prg_id,l_error_code,'IGS_PE_ATH_PRG_INT');
2565 END IF;
2566
2567 END;
2568
2569 ELSIF l_rule = 'R' THEN
2570 IF ath_prg_rec.match_ind = '21' THEN -- '21' Match reviewed and to be imported
2571 BEGIN
2572 igs_pe_athletic_prg_pkg.update_row (
2573 x_rowid => dup_chk_ath_prg_rec.rowid,
2574 x_athletic_prg_id => dup_chk_ath_prg_rec.athletic_prg_id,
2575 x_person_id => NVL(ath_prg_rec.person_id,dup_chk_ath_prg_rec.person_id),
2576 x_athletic_prg_code => ath_prg_rec.athletic_prg_code,
2577 x_rating => NVL(ath_prg_rec.rating,dup_chk_ath_prg_rec.rating),
2578 x_start_date => ath_prg_rec.start_date,
2579 x_end_date => NVL(ath_prg_rec.end_date,dup_chk_ath_prg_rec.end_date),
2580 x_recruited_ind => ath_prg_rec.recruited_ind,
2581 x_participating_ind => ath_prg_rec.participating_ind,
2582 x_last_update_dt => nvl(ath_prg_rec.last_update_date,dup_chk_ath_prg_rec.last_update_dt),
2583 x_mode => 'R'
2584 );
2585 l_error_code := NULL;
2586 l_status := '1';
2587
2588 UPDATE igs_pe_ath_prg_int
2589 SET status = l_status,
2590 error_code = l_error_code,
2591 match_ind = cst_mi_val_18
2592 WHERE interface_athletic_prg_id = ath_prg_rec.interface_athletic_prg_id;
2593
2594 EXCEPTION
2595 WHEN OTHERS THEN
2596 l_error_code := 'E100'; -- Could not update Person Athletics Program Details
2597 l_status := '3';
2598
2599 UPDATE igs_pe_ath_prg_int
2600 SET status = l_status,
2601 error_code = l_error_code
2602 WHERE interface_athletic_prg_id = ath_prg_rec.interface_athletic_prg_id;
2603
2604 -- Call Log detail
2605 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
2606
2607 IF (l_request_id IS NULL) THEN
2608 l_request_id := fnd_global.conc_request_id;
2609 END IF;
2610
2611 l_label := 'igs.plsql.igs_ad_imp_003.prc_apcnt_ath_prg.exception2 '||l_error_code;
2612
2613 l_debug_str := 'igs_ad_imp_003.prc_apcnt_ath_prg'
2614 ||' Exception from igs_pe_athletic_prg_Pkg.Update_Row '
2615 || ' INTERFACE_ATHLETIC_PRG_ID : ' ||
2616 (ath_prg_rec.interface_athletic_prg_id) ||
2617 ' Status : ' || '3' || ' ErrorCode : ' ||
2618 l_error_code||' SQLERRM:' || SQLERRM;
2619
2620 fnd_log.string_with_context( fnd_log.level_exception,
2621 l_label,
2622 l_debug_str, NULL,
2623 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
2624 END IF;
2625
2626 IF l_enable_log = 'Y' THEN
2627 igs_ad_imp_001.logerrormessage(ath_prg_rec.interface_athletic_prg_id,l_error_code,'IGS_PE_ATH_PRG_INT');
2628 END IF;
2629
2630 END;
2631
2632 END IF; -- ath_prg_rec.MATCH_IND check
2633
2634 END IF;-- l_rule check for 'I','R' or 'E'.
2635
2636 ELSE -- If its not a duplicate record then Create a new record in OSS
2637 crt_apcnt_ath_prg (p_ath_prg_rec => ath_prg_rec);
2638 END IF; -- Record existance in IGS_PE_ATHLETIC_PRG check
2639 END IF; -- Check for Validate Record
2640
2641 IF l_processed_records = 100 THEN
2642 COMMIT;
2643 l_processed_records := 0;
2644 END IF;
2645
2646 END LOOP;
2647 END prc_apcnt_ath_prg;
2648
2649 PROCEDURE prc_apcnt_ath
2650 (
2651 p_source_type_id IN NUMBER,
2652 p_batch_id IN NUMBER )
2653
2654 AS
2655 /*
2656 || Created By : pkpatel
2657 || Created On : 11-NOV-2001
2658 || Purpose : This procedure process the Athletic Details of a Person
2659 || Known limitations, enhancements or remarks :
2660 || Change History :
2661 || Who When What
2662 || samaresh 24-JAN-2002 The table Igs_ad_appl_int has been obsoleted
2663 || new table igs_ad_apl_int has been created
2664 || as a part of build ADI - Import Prc Changes
2665 || bug# 2191058
2666 || pkpatel 11-NOV-2001 Bug no.2103692 :For Person Interface DLD
2667 || Created new Procedure to process the Athletic Information of the person
2668 || This is the driving procedure in which both the procedures for processing
2669 || Athletic Details and Athletic Programs are called.
2670 || (reverse chronological order - newest change first)
2671 */
2672 BEGIN
2673 prc_apcnt_ath_dtls(p_source_type_id, p_batch_id);
2674 prc_apcnt_ath_prg (p_source_type_id, p_batch_id);
2675 END prc_apcnt_ath;
2676
2677 END IGS_AD_IMP_012;
[Home] [Help]