1 PACKAGE BODY Igs_Ad_Imp_006 AS
2 /* $Header: IGSAD84B.pls 120.4 2006/06/06 09:37:34 skpandey ship $ */
3 /*
4 /* Change History
5 Who When What
6
7 asbala 7-OCT-2003 Bug 3130316. Import Process Logging Framework Related changes.
8 asbala 28-SEP-2003 Bug 3130316. Import Process Source Category Rule processing changes,
9 lookup caching related changes, and cursor parameterization.
10 npalanis 6-JAN-2003 Bug : 2734697
11 code added to commit after import of every
12 100 records .New variable l_processed_records added
13 pkpatel 6-JAn-2003 Bug : 2735909
14 Added the validation for Birth date in Employment Details
15 npalanis 23-OCT-2002 Bug : 2608360
16 validation for alias is done from lookups
17 pathipat 08-JUL-2002 Introduced UPPER validation for Type_Of_Employment and Tenure_Of_Employment fields for Bug:2425608
18 pathipat 18-JUL-2002 Validation for Date Overlap included before Updation also (previously present for Insertion only)
19 npalanis 16-JUN-2002 Bug - 2409967
20 Level of interest lookup type not present in fnd_lookup_values
21 Level of interest validation removed
22 npalanis 14-JUN-2002 Bug - 2409967
23 the cursor check is put inside the check for error code.
24 gmaheswa 10-NOV-2003 Bug - 3223043 HZ.K impact changes
25 gmaheswa 15-DEC-2003 Bug 3316838 Removed code related to date overlap under same employer or employer party number.
26 pkpatel 23-Feb-2006 Bug 4937960 (Used the table HZ_EMPLOYMENT_HISTORY directly instead of the view IGS_AD_EMP_DTL)
27 skpandey 16-May-2006 Bug - 5205911 added comments column to IGS_AD_EMP_INT_ALL
28 */
29 --1
30
31 cst_mi_val_18 CONSTANT VARCHAR2(2) := '18';
32 cst_stat_val_1 CONSTANT VARCHAR2(2) := '1';
33
34
35 PROCEDURE Prc_Pe_Alias
36 ( P_SOURCE_TYPE_ID IN NUMBER,
37 P_BATCH_ID IN NUMBER )
38 AS
39 CURSOR alias_cur(cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE) IS
40 SELECT hii.*, i.person_id
41 FROM igs_ad_alias_int_all hii, igs_ad_interface_all i
42 WHERE hii.interface_run_id = cp_interface_run_id
43 AND i.interface_id = hii.interface_id
44 AND i.interface_run_id = cp_interface_run_id
45 AND hii.status = '2';
46
47 PERSON_ALIAS_REC alias_cur%ROWTYPE;
48
49 l_var VARCHAR2(1);
50 l_seq_number NUMBER;
51 l_rule VARCHAR2(1);
52 l_status VARCHAR2(10);
53 l_dup_var BOOLEAN;
54 l_error_code IGS_AD_EMP_INT.Error_Code%TYPE;
55 l_sequence_number IGS_PE_PERSON_ALIAS.SEQUENCE_NUMBER%TYPE;
56 l_processed_records NUMBER(5) := 0;
57 l_prog_label VARCHAR2(4000);
58 l_label VARCHAR2(4000);
59 l_debug_str VARCHAR2(4000);
60 l_enable_log VARCHAR2(1);
61 l_request_id NUMBER(10);
62 l_interface_run_id igs_ad_interface_all.interface_run_id%TYPE;
63
64 --------Start of local Validate_pe_alias ------
65 PROCEDURE Validate_pe_alias(PERSON_ALIAS_REC alias_cur%ROWTYPE,
66 l_error_code OUT NOCOPY VARCHAR2) AS
67 L_VAR VARCHAR2(1);
68 CURSOR birth_dt_cur(cp_person_id igs_pe_person_base_v.person_id%TYPE) IS
69 SELECT birth_date
70 FROM igs_pe_person_base_v
71 WHERE person_id= cp_person_id;
72
73 l_birth_date IGS_AD_INTERFACE.BIRTH_DT%TYPE;
74
75 BEGIN
76
77 --ALIAS_TYPE
78 --SQL for validation:
79 IF NOT
80 (igs_pe_pers_imp_001.validate_lookup_type_code('PE_ALIAS_TYPE',person_alias_rec.alias_type,8405))
81 THEN
82 l_error_code := 'E221'; -- Validation Unsuccessful
83 UPDATE igs_ad_alias_int_all
84 SET STATUS = '3',
85 ERROR_CODE = l_error_code
86 WHERE interface_alias_id = person_alias_rec.interface_alias_id;
87 -- Call Log detail
88
89 IF l_enable_log = 'Y' THEN
90 igs_ad_imp_001.logerrormessage(person_alias_rec.interface_alias_id,l_error_code);
91 END IF;
92 RETURN;
93 ELSE
94 l_error_code := NULL; --Validation successful
95 END IF;
96 OPEN birth_dt_cur(person_alias_rec.person_id);
97 FETCH birth_dt_cur INTO l_birth_date;
98 CLOSE birth_dt_cur;
99
100 IF l_birth_date IS NOT NULL THEN
101 IF PERSON_ALIAS_REC.START_DT < l_birth_date THEN
102 l_error_code := 'E222';
103 UPDATE IGS_AD_ALIAS_INT_ALL
104 SET STATUS = '3',
105 ERROR_CODE = l_error_code
106 WHERE INTERFACE_ALIAS_ID = PERSON_ALIAS_REC.INTERFACE_ALIAS_ID;
107
108 IF l_enable_log = 'Y' THEN
109 igs_ad_imp_001.logerrormessage(PERSON_ALIAS_REC.INTERFACE_ALIAS_ID,l_error_code);
110 END IF;
111 RETURN;
112 END IF;
113 END IF;
114 --END_DATE
115 IF (PERSON_ALIAS_REC.END_DT < PERSON_ALIAS_REC.START_DT) OR
116 (PERSON_ALIAS_REC.START_DT IS NULL AND PERSON_ALIAS_REC.END_DT IS NULL) THEN
117 -- Validation Unsuccessful
118 l_error_code := 'E208';
119 UPDATE IGS_AD_ALIAS_INT_ALL
120 SET STATUS = '3',
121 ERROR_CODE = l_error_code
122 WHERE INTERFACE_ALIAS_ID = PERSON_ALIAS_REC.INTERFACE_ALIAS_ID;
123 IF l_enable_log = 'Y' THEN
124 igs_ad_imp_001.logerrormessage(PERSON_ALIAS_REC.INTERFACE_ALIAS_ID,l_error_code);
125 END IF;
126 RETURN;
127 END IF;
128 l_error_code := null;
129 END Validate_pe_alias;
130 --------End of local Validate_pe_alias ------
131
132 PROCEDURE Crt_Pe_Alias(PERSON_ALIAS_REC IN alias_cur%ROWTYPE)
133 AS
134
135 -- Code added by Nshee as part of import process testing after verifying from DLDv1.8 on 27-FEB-01
136 CURSOR c_person_alias_seq_number_s IS
137 SELECT IGS_PE_PERSON_ALIAS_SEQ_NUM_S.NEXTVAL FROM dual;
138 l_person_alias_seq_number IGS_PE_PERSON_ALIAS.SEQUENCE_NUMBER%TYPE;
139 --End of code addition by nshee
140
141 l_var VARCHAR2(1);
142 l_rowid VARCHAR2(25);
143 l_error_code IGS_AD_EMP_INT.Error_Code%TYPE;
144 BEGIN
145
146 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
147
148 IF (l_request_id IS NULL) THEN
149 l_request_id := fnd_global.conc_request_id;
150 END IF;
151
152 l_label := 'igs.plsql.igs_ad_imp_006.crt_pe_alias.begin';
153 l_debug_str := 'Interface Alias Id : ' || person_alias_rec.INTERFACE_ALIAS_ID;
154
155 fnd_log.string_with_context( fnd_log.level_procedure,
156 l_label,
157 l_debug_str, NULL,
158 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
159 END IF;
160 Validate_pe_alias(PERSON_ALIAS_REC, l_error_code);
161
162 -- Code added by Nshee as part of import process testing after verifying from DLDv1.8 on 27-FEB-01
163 OPEN c_person_alias_seq_number_s;
164 FETCH c_person_alias_seq_number_s INTO l_person_alias_seq_number;
165 IF c_person_alias_seq_number_s%NOTFOUND THEN
166 RAISE NO_DATA_FOUND;
167 END IF;
168 --End of code addition by nshee
169 IF l_error_code IS NULL THEN
170
171 Igs_Pe_Person_Alias_Pkg.INSERT_ROW (
172 X_ROWID => l_rowid,
173 X_PERSON_ID => PERSON_ALIAS_REC.PERSON_ID,
174 X_ALIAS_TYPE => PERSON_ALIAS_REC.ALIAS_TYPE,
175 -- X_SEQUENCE_NUMBER => NULL,--PERSON_ALIAS_REC.SEQUENCE_NUMBER,--commented by nshee
176 X_SEQUENCE_NUMBER => l_person_alias_seq_number,
177 X_TITLE => PERSON_ALIAS_REC.TITLE,
178 X_ALIAS_COMMENT => PERSON_ALIAS_REC.ALIAS_COMMENT,
179 X_START_DT => PERSON_ALIAS_REC.START_DT,
180 X_END_DT => PERSON_ALIAS_REC.END_DT,
181 X_SURNAME => PERSON_ALIAS_REC.SURNAME,
182 X_GIVEN_NAMES => PERSON_ALIAS_REC.GIVEN_NAMES,
183 X_MODE => 'R'
184 );
185
186 UPDATE IGS_AD_ALIAS_INT_ALL
187 SET STATUS = '1',
188 ERROR_CODE = NULL
189 WHERE INTERFACE_ALIAS_ID = person_alias_rec.INTERFACE_ALIAS_ID;
190 END IF;
191 EXCEPTION
192 WHEN OTHERS THEN
193 UPDATE IGS_AD_ALIAS_INT_ALL
194 SET ERROR_CODE = 'E322',
195 STATUS = '3'
196 WHERE INTERFACE_ALIAS_ID = person_alias_rec.INTERFACE_ALIAS_ID;
197
198 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
199
200 IF (l_request_id IS NULL) THEN
201 l_request_id := fnd_global.conc_request_id;
202 END IF;
203
204 l_label := 'igs.plsql.igs_ad_imp_006.crt_pe_alias.exception';
205
206 l_debug_str := 'IGS_AD_IMP_006.Prc_Pe_Alias.Crt_Pe_Alias ' ||
207 'Interface Alias Id : ' || person_alias_rec.INTERFACE_ALIAS_ID ||
208 ' Status : 3 ' || 'ErrorCode : E322 ' || SQLERRM;
209
210 fnd_log.string_with_context( fnd_log.level_exception,
211 l_label,
212 l_debug_str, NULL,
213 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
214 END IF;
215
216 IF l_enable_log = 'Y' THEN
217 igs_ad_imp_001.logerrormessage(person_alias_rec.INTERFACE_ALIAS_ID,'E322');
218 END IF;
219 END Crt_Pe_Alias;
220 -- END OF LOCAL PROCEDURE
221
222 BEGIN
223 -- For every record check whether a corresponding row already exists
224 -- in the table IGS_PE_PERSON_ALIAS
225 -- Update of person alias is removed because there is no primary key based on which the record
226 -- present can be obtained because duplicate records can be created in form.
227 l_enable_log := igs_ad_imp_001.g_enable_log;
228 l_prog_label := 'igs.plsql.igs_ad_imp_006.prc_pe_alias';
229 l_label := 'igs.plsql.igs_ad_imp_006.prc_pe_alias.';
230 l_interface_run_id := igs_ad_imp_001.g_interface_run_id;
231 FOR person_alias_rec IN alias_cur(l_interface_run_id)
232 LOOP
233 l_processed_records := l_processed_records + 1 ;
234 person_alias_rec.start_dt := TRUNC(person_alias_rec.start_dt);
235 person_alias_rec.end_dt := TRUNC(person_alias_rec.end_dt);
236 person_alias_rec.alias_type := UPPER(person_alias_rec.alias_type);
237
238 Crt_Pe_Alias(person_alias_rec);
239 IF l_processed_records = 100 THEN
240 COMMIT;
241 l_processed_records := 0;
242 END IF;
243 END LOOP;
244 END Prc_Pe_Alias;
245
246
247 -- 3
248 PROCEDURE Prc_Pe_Empnt_Dtls (
249 P_SOURCE_TYPE_ID IN NUMBER,
250 P_BATCH_ID IN VARCHAR2 )
251 AS
252 CURSOR emp_dtls(cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE) IS
253 SELECT hii.*, i.person_id
254 FROM igs_ad_emp_int_all hii, igs_ad_interface_all i
255 WHERE hii.interface_run_id = cp_interface_run_id
256 AND i.interface_id = hii.interface_id
257 AND i.interface_run_id = cp_interface_run_id
258 AND hii.status = '2';
259
260 L_MEANING VARCHAR2(80);
261 L_PARTY_TYPE VARCHAR2(30);
262 L_VAR VARCHAR2(1);
263 p_dup_var BOOLEAN;
264 l_rule VARCHAR2(1);
265 l_status VARCHAR2(25);
266 l_Employment_History_Id NUMBER;
267 lDupExists VARCHAR2(1);
268 l_Msg_Data VARCHAR2(2000);
269 l_Return_Status VARCHAR2(1);
270 l_RowId VARCHAR2(25);
271 l_error_code IGS_AD_EMP_INT.Error_Code%TYPE;
272 l_processed_records NUMBER(5) := 0;
273 l_prog_label VARCHAR2(4000);
274 l_label VARCHAR2(4000);
275 l_debug_str VARCHAR2(4000);
276 l_enable_log VARCHAR2(1);
277 l_request_id NUMBER(10);
278 l_interface_run_id igs_ad_interface_all.interface_run_id%TYPE;
279 l_object_version_number NUMBER;
280 ------ Local Procedure validate_emp_dtls---
281 PROCEDURE validate_emp_dtls (PERSON_EMP_REC IN emp_dtls%ROWTYPE,
282 P_EMPLOYER_PARTY_ID IN OUT NOCOPY NUMBER,
283 p_error_code OUT NOCOPY VARCHAR2) AS
284
285 CURSOR Validate_Occup_Title(cp_occ_t_code igs_ps_dic_occ_titls.occupational_title_code%TYPE) IS
286 SELECT 'Y'
287 FROM igs_ps_dic_occ_titls
288 WHERE occupational_title_code = cp_occ_t_code;
289
290 CURSOR birth_date_cur(cp_person_id igs_pe_person_base_v.person_id%TYPE) IS
291 SELECT birth_date
292 FROM igs_pe_person_base_v
293 WHERE person_id = cp_person_id;
294
295 CURSOR employer_party_number_cur(cp_employer_party_number igs_ad_emp_int_all.employer_party_number%TYPE) IS
296 SELECT PARTY_ID
297 FROM HZ_PARTIES
298 WHERE party_type = 'ORGANIZATION' AND
299 party_number = cp_employer_party_number AND
300 status <> 'M';
301
302
303 l_var VARCHAR2(1);
304 l_birth_date igs_pe_person_base_v.birth_date%TYPE;
305 l_employer_party_number VARCHAR2(1);
306
307 BEGIN
308
309 --3. Perform validations for the following columns
310 -- Occupational Title Code
311
312 IF PERSON_EMP_REC.OCCUPATIONAL_TITLE_CODE IS NOT NULL THEN
313 OPEN Validate_Occup_Title(person_emp_rec.occupational_title_code);
314 FETCH Validate_Occup_Title INTO l_var;
315 IF Validate_Occup_Title%NOTFOUND THEN
316 p_error_code := 'E223';
317 UPDATE IGS_AD_EMP_INT_ALL
318 SET Error_Code = p_error_code,
319 Status = '3'
320 WHERE Interface_Emp_Id = Person_Emp_Rec.Interface_Emp_Id;
321
322 IF l_enable_log = 'Y' THEN
323 igs_ad_imp_001.logerrormessage(Person_Emp_Rec.Interface_Emp_Id,'E223');
324 END IF;
325 CLOSE Validate_Occup_Title;
326 RETURN;
327 END IF;
328 CLOSE Validate_Occup_Title;
329 END IF;
330
331 --START_DATE This field is mandatory.
332 IF PERSON_EMP_REC.START_DATE IS NULL THEN
333 --Validation Unsuccessful
334 p_error_code := 'E212';
335 UPDATE IGS_AD_EMP_INT_ALL
336 SET Error_Code = p_error_code,
337 Status = '3'
338 WHERE Interface_Emp_Id = Person_Emp_Rec.Interface_Emp_Id;
339 IF l_enable_log = 'Y' THEN
340 igs_ad_imp_001.logerrormessage(Person_Emp_Rec.Interface_Emp_Id,'E212');
341 END IF;
342 RETURN;
343 END IF;
344
345 --END_DATE
346 IF PERSON_EMP_REC.END_DATE IS NOT NULL THEN
347 IF PERSON_EMP_REC.END_DATE < PERSON_EMP_REC.START_DATE THEN
348 --Validation Unsuccessful
349 p_error_code := 'E208';
350 UPDATE IGS_AD_EMP_INT_ALL
351 SET Error_Code = p_error_code,
352 Status = '3'
353 WHERE Interface_Emp_Id = Person_Emp_Rec.Interface_Emp_Id;
354 IF l_enable_log = 'Y' THEN
355 igs_ad_imp_001.logerrormessage(Person_Emp_Rec.Interface_Emp_Id,'E208');
356 END IF;
357
358 RETURN;
359 END IF;
360 END IF;
361
362 --TYPE_OF_EMPLOYMENT
363 -- Modified to validate type_of_employment from lookup values
364 IF PERSON_EMP_REC.TYPE_OF_EMPLOYMENT IS NOT NULL THEN
365 IF NOT (igs_pe_pers_imp_001.validate_lookup_type_code('HZ_EMPLOYMENT_TYPE',PERSON_EMP_REC.TYPE_OF_EMPLOYMENT,222)) THEN
366 p_error_code := 'E224';
367
368 UPDATE IGS_AD_EMP_INT_ALL
369 SET Error_Code = p_error_code,
370 Status = '3'
371 WHERE Interface_Emp_Id = Person_Emp_Rec.Interface_Emp_Id;
372 IF l_enable_log = 'Y' THEN
373 igs_ad_imp_001.logerrormessage(Person_Emp_Rec.Interface_Emp_Id,p_error_code);
374 END IF;
375 RETURN;
376 END IF;
377 END IF;
378
379 --FRACTION OF EMPLOYMENT
380 IF PERSON_EMP_REC.FRACTION_OF_EMPLOYMENT IS NOT NULL THEN
381 IF PERSON_EMP_REC.FRACTION_OF_EMPLOYMENT NOT BETWEEN 0.01 AND 100.00 THEN
382 --Validation Unsuccessful
383 p_error_code := 'E225';
384 UPDATE IGS_AD_EMP_INT_ALL
385 SET Error_Code = p_error_code,
386 Status = '3'
387 WHERE Interface_Emp_Id = Person_Emp_Rec.Interface_Emp_Id;
388 IF l_enable_log = 'Y' THEN
389 igs_ad_imp_001.logerrormessage(Person_Emp_Rec.Interface_Emp_Id,p_error_code);
390 END IF;
391 RETURN;
392 END IF;
393 END IF;
394
395 --TENURE_OF_EMPLOYMENT
396 --Modified to validate tenure_of_employment from lookup values
397 IF PERSON_EMP_REC.TENURE_OF_EMPLOYMENT IS NOT NULL THEN
398 IF NOT (igs_pe_pers_imp_001.validate_lookup_type_code('HZ_TENURE_CODE',PERSON_EMP_REC.TENURE_OF_EMPLOYMENT,222))THEN
399 --Validation Unsuccessful
400 p_error_code := 'E226';
401 UPDATE IGS_AD_EMP_INT_ALL
402 SET Error_Code = p_error_code,
403 Status = '3'
404 WHERE Interface_Emp_Id = Person_Emp_Rec.Interface_Emp_Id;
405 IF l_enable_log = 'Y' THEN
406 igs_ad_imp_001.logerrormessage(Person_Emp_Rec.Interface_Emp_Id,p_error_code);
407 END IF;
408 RETURN;
409 END IF;
410 END IF ;
411
412 --POSITION
413 --No validation checks. Free text.
414 --WEEKLY_WORK_HOURS
415 IF PERSON_EMP_REC.WEEKLY_WORK_HRS IS NOT NULL THEN
416 IF PERSON_EMP_REC.WEEKLY_WORK_HRS < 0 OR PERSON_EMP_REC.WEEKLY_WORK_HRS > 168 THEN
417 --Validation Successful
418 p_error_code := 'E227';
419 UPDATE IGS_AD_EMP_INT_ALL
420 SET Error_Code = p_error_code,
421 Status = '3'
422 WHERE Interface_Emp_Id = Person_Emp_Rec.Interface_Emp_Id;
423 IF l_enable_log = 'Y' THEN
424 igs_ad_imp_001.logerrormessage(Person_Emp_Rec.Interface_Emp_Id,p_error_code);
425 END IF;
426 RETURN;
427 END IF;
428 END IF;
429
430 OPEN birth_date_cur(person_emp_rec.person_id);
431 FETCH birth_date_cur INTO l_birth_date;
432 CLOSE birth_date_cur;
433 -- start date must be greater than birth date
434 IF l_birth_date IS NOT NULL THEN
435 IF person_emp_rec.start_date < l_birth_date THEN
436 p_error_code := 'E222';
437 UPDATE IGS_AD_EMP_INT_ALL
438 SET Error_Code = p_error_code,
439 Status = '3'
440 WHERE Interface_Emp_Id = Person_Emp_Rec.Interface_Emp_Id;
441 IF l_enable_log = 'Y' THEN
442 igs_ad_imp_001.logerrormessage(Person_Emp_Rec.Interface_Emp_Id,p_error_code);
443 END IF;
444 RETURN;
445 END IF;
446 END IF;
447
448 --Employer and Employer_party_number are mutually exclusive
449 IF PERSON_EMP_REC.employer_party_number IS NOT NULL AND PERSON_EMP_REC.EMPLOYER IS NOT NULL THEN
450 p_error_code := 'E755';
451 UPDATE IGS_AD_EMP_INT_ALL
452 SET Error_Code = p_error_code,
453 Status = '3'
454 WHERE Interface_Emp_Id = Person_Emp_Rec.Interface_Emp_Id;
455 IF l_enable_log = 'Y' THEN
456 igs_ad_imp_001.logerrormessage(Person_Emp_Rec.Interface_Emp_Id,p_error_code);
457 END IF;
458 RETURN;
459 END IF;
460
461 --Validate employer party id from the list of values
462 IF PERSON_EMP_REC.employer_party_number IS NOT NULL THEN
463 OPEN employer_party_number_cur(PERSON_EMP_REC.employer_party_number);
464 FETCH employer_party_number_cur INTO p_employer_party_id;
465 IF employer_party_number_cur%NOTFOUND THEN
466 p_error_code := 'E756';
467 UPDATE IGS_AD_EMP_INT_ALL
468 SET Error_Code = p_error_code,
469 Status = '3'
470 WHERE Interface_Emp_Id = Person_Emp_Rec.Interface_Emp_Id;
471 IF l_enable_log = 'Y' THEN
472 igs_ad_imp_001.logerrormessage(Person_Emp_Rec.Interface_Emp_Id,p_error_code);
473 END IF;
474 RETURN;
475 END IF;
476 CLOSE employer_party_number_cur;
477 END IF;
478
479 p_error_code := NULL;
480 UPDATE IGS_AD_EMP_INT_ALL
481 SET Error_Code = p_error_code,
482 Status = '1'
483 WHERE Interface_Emp_Id = Person_Emp_Rec.Interface_Emp_Id;
484
485 END validate_emp_dtls;
486 ------ End of Local Procedure validate_emp_dtls---
487 ------ Local Procedure crt_emp_dtls ---
488 PROCEDURE crt_emp_dtls( PERSON_EMP_REC emp_dtls%ROWTYPE) AS
489 l_rowid VARCHAR2(25);
490 l_Employment_History_Id NUMBER;
491 l_last_update_date DATE;
492 l_return_status VARCHAR2(1);
493 l_msg_count NUMBER;
494 l_msg_data VARCHAR2(2000);
495 P_Emp_History_Id NUMBER;
496 l_Row_Id VARCHAR2(25);
497 l_error_code IGS_AD_EMP_INT.Error_Code%TYPE;
498
499 l_employer_party_id NUMBER;
500 BEGIN
501
502 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
503 IF (l_request_id IS NULL) THEN
504 l_request_id := fnd_global.conc_request_id;
505 END IF;
506 l_label := 'igs.plsql.igs_ad_imp_006.crt_emp_dtls.begin';
507 l_debug_str := 'INTERFACE Emp Id : ' || Person_Emp_Rec.Interface_Emp_Id;
508 fnd_log.string_with_context( fnd_log.level_procedure,
509 l_label,
510 l_debug_str, NULL,
511 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id)
512 );
513 END IF;
514
515 -- Validate the values of PERSON_EMP_REC.
516 validate_emp_dtls(PERSON_EMP_REC,l_employer_party_id,l_error_code);
517
518 IF l_error_code IS NULL THEN
519 --signature of Igs_Ad_Emp_Dtl_Pkg is changed and columns branch,military rank,served,station are obsoleted
520 Igs_Ad_Emp_Dtl_Pkg.INSERT_ROW (
521 X_ROWID => l_RowId,
522 x_employment_history_id => l_Employment_History_Id,
523 x_PERSON_ID => PERSON_EMP_REC.person_id,
524 x_START_DT => PERSON_EMP_REC.Start_Date,
525 x_END_DT => PERSON_EMP_REC.End_Date,
526 x_TYPE_OF_EMPLOYMENT => PERSON_EMP_REC.Type_Of_Employment,
527 x_FRACTION_OF_EMPLOYMENT => PERSON_EMP_REC.Fraction_Of_Employment,
528 x_TENURE_OF_EMPLOYMENT => PERSON_EMP_REC.Tenure_Of_Employment,
529 x_POSITION => PERSON_EMP_REC.Position,
530 x_OCCUPATIONAL_TITLE_CODE => PERSON_EMP_REC.OCCUPATIONAL_TITLE_CODE,
531 x_OCCUPATIONAL_TITLE => NULL, --PERSON_EMP_REC.TITLE,
532 x_WEEKLY_WORK_HOURS => PERSON_EMP_REC.WEEKLY_WORK_HRS,
533 x_COMMENTS => PERSON_EMP_REC.Comments,
534 x_EMPLOYER => PERSON_EMP_REC.Employer,
535 x_EMPLOYED_BY_DIVISION_NAME => PERSON_EMP_REC.Employed_by_division_name,
536 x_BRANCH => null,
537 x_MILITARY_RANK => null,
538 x_SERVED => null,
539 x_STATION => null,
540 x_CONTACT => PERSON_EMP_REC.Contact, --Bug : 2037512
541 x_msg_data => l_msg_data,
542 x_return_status => l_return_status,
543 x_object_version_number => l_object_version_number,
544 x_employed_by_party_id => l_Employer_party_id,
545 x_reason_for_leaving => PERSON_EMP_REC.Reason_for_leaving,
546 X_MODE => 'R'
547 );
548 IF l_return_Status IN ('E','U') THEN
549 UPDATE IGS_AD_EMP_INT_all
550 SET status = '3',
551 error_code = 'E322'
552 WHERE INTERFACE_EMP_ID = PERSON_EMP_REC.INTERFACE_EMP_ID;
553
554 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
555 IF (l_request_id IS NULL) THEN
556 l_request_id := fnd_global.conc_request_id;
557 END IF;
558
559 l_label := 'igs.plsql.igs_ad_imp_006.crt_emp_dtls.exception';
560
561 l_debug_str := 'IGS_AD_IMP_006.Prc_Pe_Empnt_Dtls.crt_emp_dtls ' ||
562 'INTERFACE Emp Id : ' || IGS_GE_NUMBER.TO_CANN(Person_Emp_Rec.Interface_Emp_Id) ||
563 ' Status : 3 ' || 'ErrorCode : E322 '|| l_msg_data;
564
565 fnd_log.string_with_context( fnd_log.level_exception,
566 l_label,
567 l_debug_str, NULL,
568 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
569 END IF;
570
571 IF l_enable_log = 'Y' THEN
572 igs_ad_imp_001.logerrormessage(Person_Emp_Rec.Interface_Emp_Id,'E322');
573 END IF;
574 ELSE
575 UPDATE IGS_AD_EMP_INT_all
576 SET status = '1'
577 WHERE INTERFACE_EMP_ID = PERSON_EMP_REC.INTERFACE_EMP_ID;
578 END IF;
579 END IF;
580 END crt_emp_dtls;
581 ------ End of Local Procedure crt_emp_dtls----
582 BEGIN
583 l_interface_run_id := igs_ad_imp_001.g_interface_run_id;
584 l_enable_log := igs_ad_imp_001.g_enable_log;
585 l_prog_label := 'igs.plsql.igs_ad_imp_006.prc_pe_empnt_dtls';
586 l_label := 'igs.plsql.igs_ad_imp_006.prc_pe_empnt_dtls.';
587
588 l_rule :=Igs_Ad_Imp_001.FIND_SOURCE_CAT_RULE(P_SOURCE_TYPE_ID,'PERSON_EMPLOYMENT_DETAILS');
589
590 -- 1.If rule is E or I, then if the match_ind is not null, the combination is invalid
591 IF l_rule IN ('E','I') THEN
592 UPDATE igs_ad_emp_int_all
593 SET status = '3',
594 ERROR_CODE = 'E695' -- Error code depicting incorrect combination
595 WHERE match_ind IS NOT NULL
596 AND interface_run_id = l_interface_run_id
597 AND status = '2';
598 END IF;
599
600 --2. If rule is E and duplicate exists, update match_ind to 19 and status to 1
601 IF l_rule = 'E' THEN
602 UPDATE igs_ad_emp_int_all mi
603 SET status = '1',
604 match_ind = '19'
605 WHERE mi.interface_run_id = l_interface_run_id
606 AND mi.status = '2'
607 AND EXISTS ( SELECT '1'
608 FROM hz_employment_history pe, igs_ad_interface_all ii, hz_parties hz
609 WHERE ii.interface_run_id = l_interface_run_id
610 AND ii.interface_id = mi.interface_id
611 AND pe.employed_by_party_id = hz.party_id(+)
612 AND ii.person_id = pe.party_id
613 AND (( NVL(UPPER(mi.employer),'*!') = NVL(UPPER(pe.employed_by_name_company),'*'))
614 OR (NVL(mi.employer_party_number,'*!') = NVL(hz.party_number,'*')))
615 AND pe.begin_date = TRUNC(mi.start_date) );
616 END IF;
617
618 -- 3.If rule is R and there match_ind is 18,19,22 or 23 then the records must have been
619 -- processed in prior runs and didn't get updated .. update to status 1
620 IF l_rule = 'R' THEN
621 UPDATE igs_ad_emp_int_all
622 SET status = '1'
623 WHERE interface_run_id = l_interface_run_id
624 AND match_ind IN ('18','19','22','23')
625 AND status='2';
626 END IF;
627
628 -- 4.If rule is R and match_ind is neither 21 nor 25 then error
629 IF l_rule = 'R' THEN
630 UPDATE igs_ad_emp_int_all
631 SET status = '3',
632 ERROR_CODE = 'E695'
633 WHERE interface_run_id = l_interface_run_id
634 AND (match_ind IS NOT NULL AND match_ind NOT IN ('21','25'))
635 AND status='2';
636 END IF;
637
638 -- 5.If rule is R, set duplicated records with no discrepancy to status 1 and match_ind 23
639 IF l_rule = 'R' THEN
640 UPDATE igs_ad_emp_int_all mi
641 SET status = '1',
642 match_ind = '23'
643 WHERE mi.interface_run_id = l_interface_run_id
644 AND mi.match_ind IS NULL
645 AND mi.status = '2'
646 AND EXISTS ( SELECT '1'
647 FROM hz_employment_history pe, igs_ad_interface_all ii, igs_ad_hz_emp_dtl ahed, hz_parties hz
648 WHERE ii.interface_run_id = l_interface_run_id
649 AND ii.interface_id = mi.interface_id
650 AND ii.person_id = pe.party_id
651 AND pe.employment_history_id = ahed.employment_history_id (+)
652 AND pe.employed_by_party_id = hz.party_id (+)
653 AND ((NVL(UPPER(mi.employer),'*!') = NVL(UPPER(pe.employed_by_name_company),'*'))
654 OR (NVL(hz.party_number,'*!') = NVL(mi.employer_party_number,'*')))
655 AND pe.begin_date = TRUNC(mi.start_date)
656 AND NVL(pe.end_date,igs_ge_date.igsdate('9999/01/01')) = NVL(TRUNC(mi.end_date),igs_ge_date.igsdate('9999/01/01'))
657 AND NVL(pe.supervisor_name,'*') = NVL(mi.contact,'*')
658 AND NVL(pe.employment_type_code,'*') = NVL(UPPER(mi.type_of_employment),'*')
659 AND NVL(pe.fraction_of_tenure,0) = NVL(mi.fraction_of_employment,0)
660 AND NVL(pe.tenure_code,'*') = NVL(UPPER(mi.tenure_of_employment),'*')
661 AND NVL(pe.employed_as_title,'*') = NVL(mi.position,'*')
662 AND NVL(ahed.occupational_title_code,'*') = NVL(mi.occupational_title_code,'*')
663 AND NVL(pe.weekly_work_hours,0) = NVL(mi.weekly_work_hrs,0)
664 AND NVL(pe.employed_by_division_name,'*') = NVL(mi.employed_by_division_name,'*')
665 AND NVL(pe.reason_for_leaving,'*') = NVL(mi.reason_for_leaving,'*')
666 AND NVL(pe.comments,'*') = NVL(mi.comments,'*')
667 );
668 END IF;
669
670 -- 6.If rule in R records still exist, they are duplicates and have discrepancy .. update status=3,match_ind=20
671 IF l_rule = 'R' THEN
672 UPDATE igs_ad_emp_int_all mi
673 SET status = '3',
674 match_ind = '20',
675 dup_employment_number = (SELECT employment_history_id
676 FROM hz_employment_history pe, igs_ad_interface_all ii, hz_parties hz
677 WHERE ii.interface_run_id = l_interface_run_id
678 AND ii.interface_id = mi.interface_id
679 AND ii.person_id = pe.party_id
680 AND pe.employed_by_party_id = hz.party_id (+)
681 AND ((NVL(UPPER(mi.employer),'*!') = NVL(UPPER(pe.employed_by_name_company),'*'))
682 OR (NVL(mi.employer_party_number,'*!') = NVL(hz.party_number,'*')))
683 AND pe.begin_date = TRUNC(mi.start_date)
684 AND ROWNUM = 1)
685 WHERE mi.interface_run_id = l_interface_run_id
686 AND mi.match_ind IS NULL
687 AND mi.status = '2'
688 AND EXISTS (SELECT '1'
689 FROM hz_employment_history pe, igs_ad_interface_all ii, hz_parties hz
690 WHERE ii.interface_run_id = l_interface_run_id
691 AND ii.interface_id = mi.interface_id
692 AND ii.person_id = pe.party_id
693 AND pe.employed_by_party_id = hz.party_id (+)
694 AND ((NVL(UPPER(mi.employer),'*!') = NVL(UPPER(pe.employed_by_name_company),'*'))
695 OR (NVL(mi.employer_party_number,'*!') = NVL(hz.party_number,'*')))
696 AND pe.begin_date = TRUNC(mi.start_date));
697 END IF;
698
699 FOR person_emp_rec IN emp_dtls(l_interface_run_id) LOOP
700 l_processed_records := l_processed_records + 1;
701
702 DECLARE
703 CURSOR chk_dup_emp_dtls(cp_employer VARCHAR2,
704 cp_employer_party_number VARCHAR2,
705 cp_person_id NUMBER,
706 cp_start_date igs_ad_emp_dtl.start_dt%TYPE) IS
707 SELECT heh.rowid row_id,
708 heh.employment_history_id,
709 heh.party_id person_id,
710 heh.begin_date start_dt,
711 heh.end_date end_dt,
712 heh.supervisor_name contact,
713 heh.employment_type_code type_of_employment,
714 heh.fraction_of_tenure fraction_of_employment,
715 heh.tenure_code tenure_of_employment,
716 heh.employed_as_title position,
717 ahed.occupational_title_code,
718 heh.weekly_work_hours,
719 heh.comments,
720 heh.employed_by_name_company employer,
721 heh.employed_by_division_name,
722 heh.branch,
723 heh.military_rank,
724 heh.served,
725 heh.station,
726 heh.object_version_number,
727 heh.employed_by_party_id,
728 heh.reason_for_leaving reason_for_leaving,
729 null occupational_title
730 FROM hz_employment_history heh, igs_ad_hz_emp_dtl ahed, hz_parties hz
731 WHERE heh.party_id = cp_person_id
732 AND heh.employment_history_id = ahed.employment_history_id (+)
733 AND heh.employed_by_party_id = hz.party_id (+)
734 AND ( NVL(UPPER(heh.employed_by_name_company),'!*!') = NVL(UPPER(cp_employer),'!*!')
735 OR
736 NVL(hz.party_number,'!*!') = NVL(cp_employer_party_number,'!*!'))
737 AND
738 TRUNC(heh.begin_date) = TRUNC(cp_start_date);
739 dup_emp_dtlsc_rec chk_dup_emp_dtls%ROWTYPE;
740
741 BEGIN
742 -- Upper validation for type_of_employment and tenure_of_employment Bug: 2425608
743 person_emp_rec.type_of_employment := UPPER(person_emp_rec.Type_Of_Employment);
744 person_emp_rec.tenure_of_employment := UPPER(person_emp_rec.Tenure_Of_Employment) ;
745 person_emp_rec.start_date := TRUNC(person_emp_rec.Start_Date); -- Time is truncated
746 person_emp_rec.end_date := TRUNC(person_emp_rec.end_date);
747 dup_emp_dtlsc_rec.employment_history_id := NULL;
748
749 OPEN chk_dup_emp_dtls(person_emp_rec.employer,person_emp_rec.employer_party_number,person_emp_rec.person_id,person_emp_rec.start_date);
750 FETCH chk_dup_emp_dtls INTO dup_emp_dtlsc_rec;
751 CLOSE chk_dup_emp_dtls;
752 IF dup_emp_dtlsc_rec.employment_history_id IS NOT NULL THEN
753 IF l_rule = 'I' THEN
754 DECLARE
755 l_employer_party_id NUMBER;
756 BEGIN
757 -- Validate the values of person_emp_rec.
758 validate_emp_dtls(PERSON_EMP_REC,l_employer_party_id,l_error_code);
759 IF l_error_code IS NULL THEN -- nsidana Bug 3541735 : Corrected the check from not null --> null.
760 igs_ad_emp_dtl_pkg.update_row (
761 x_rowid => dup_emp_dtlsc_rec.row_id,
762 x_employment_history_id => dup_emp_dtlsc_rec.employment_history_id,
763 x_person_id => NVL(person_emp_rec.person_id,dup_emp_dtlsc_rec.person_id),
764 x_start_dt => NVL(person_emp_rec.start_date,dup_emp_dtlsc_rec.start_dt),
765 x_end_dt => NVL(person_emp_rec.end_date,dup_emp_dtlsc_rec.end_dt),
766 x_type_of_employment => NVL(person_emp_rec.type_of_employment,dup_emp_dtlsc_rec.type_of_employment),
767 x_fraction_of_employment => NVL(person_emp_rec.fraction_of_employment,dup_emp_dtlsc_rec.fraction_of_employment),
768 x_tenure_of_employment => NVL(person_emp_rec.tenure_of_employment,dup_emp_dtlsc_rec.tenure_of_employment),
769 x_position => NVL(person_emp_rec.position,dup_emp_dtlsc_rec.position),
770 x_occupational_title_code => NVL(person_emp_rec.occupational_title_code,dup_emp_dtlsc_rec.occupational_title_code),
771 x_occupational_title => dup_emp_dtlsc_rec.occupational_title,
772 x_weekly_work_hours => NVL(person_emp_rec.weekly_work_hrs,dup_emp_dtlsc_rec.weekly_work_hours),
773 x_comments => NVL(person_emp_rec.comments,dup_emp_dtlsc_rec.comments),
774 x_employer => NVL(person_emp_rec.employer,dup_emp_dtlsc_rec.employer),
775 x_employed_by_division_name => NVL(person_emp_rec.employed_by_division_name,dup_emp_dtlsc_rec.employed_by_division_name),
776 x_branch => NVL(person_emp_rec.branch,dup_emp_dtlsc_rec.branch),
777 x_military_rank => NVL(person_emp_rec.military_rank,dup_emp_dtlsc_rec.military_rank),
778 x_served => NVL(person_emp_rec.served,dup_emp_dtlsc_rec.served),
779 x_station => NVL(person_emp_rec.station,dup_emp_dtlsc_rec.station),
780 x_contact => NVL(person_emp_rec.contact,dup_emp_dtlsc_rec.contact), -- Bug : 2037512
781 x_msg_data => l_msg_data,
782 x_return_status => l_return_status,
783 x_object_version_number => dup_emp_dtlsc_rec.object_version_number,
784 x_employed_by_party_id => NVL(l_employer_party_id,dup_emp_dtlsc_rec.employed_by_party_id),
785 x_reason_for_leaving => NVL(person_emp_rec.reason_for_leaving,dup_emp_dtlsc_rec.reason_for_leaving),
786 x_mode => 'R'
787 );
788
789 IF l_return_Status IN ('E','U') THEN
790 UPDATE IGS_AD_EMP_INT_all
791 SET error_code = 'E014',
792 status = '3'
793 WHERE interface_emp_id = person_emp_rec.interface_emp_id;
794
795 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
796
797 IF (l_request_id IS NULL) THEN
798 l_request_id := fnd_global.conc_request_id;
799 END IF;
800
801 l_label := 'igs.plsql.igs_ad_imp_006.prc_pe_empnt_dtls.exception: ' || 'e014';
802
803 l_debug_str := 'IGS_AD_IMP_006.Prc_Pe_Empnt_Dtls ' ||
804 'INTERFACE Emp Id : ' || IGS_GE_NUMBER.TO_CANN(Person_Emp_Rec.Interface_Emp_Id) ||
805 ' Status : 3 ' || 'ErrorCode : E014 '||l_msg_data;
806
807 fnd_log.string_with_context( fnd_log.level_exception,
808 l_label,
809 l_debug_str, NULL,
810 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
811 END IF;
812
813 IF l_enable_log = 'Y' THEN
814 igs_ad_imp_001.logerrormessage(Person_Emp_Rec.Interface_Emp_Id,'E014');
815 END IF;
816
817 ELSE
818 UPDATE igs_ad_emp_int_all
819 SET match_ind = cst_mi_val_18 ,
820 STATUS = cst_stat_val_1, ERROR_CODE = NULL
821 WHERE interface_emp_id = person_emp_rec.interface_emp_id;
822 END IF;
823 END IF; -- if lerror_code is NOT null
824 EXCEPTION
825 WHEN OTHERS THEN
826 UPDATE igs_ad_emp_int_all
827 SET ERROR_CODE = 'E014',
828 STATUS = '3'
829 WHERE interface_emp_id = person_emp_rec.interface_emp_id;
830
831 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
832
833 IF (l_request_id IS NULL) THEN
834 l_request_id := fnd_global.conc_request_id;
835 END IF;
836
837 l_label := 'igs.plsql.igs_ad_imp_006.prc_pe_empnt_dtls.exception: ' || 'E014';
838
839 l_debug_str := 'IGS_AD_IMP_006.Prc_Pe_Empnt_Dtls ' ||
840 'INTERFACE Emp Id : ' || Person_Emp_Rec.Interface_Emp_Id ||
841 ' Status : 3 ' || 'ErrorCode : E014 '||SQLERRM;
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 );
848 END IF;
849
850 IF l_enable_log = 'Y' THEN
851 igs_ad_imp_001.logerrormessage(Person_Emp_Rec.Interface_Emp_Id,'E014');
852 END IF;
853 END;
854 ELSIF l_rule = 'R' THEN
855 IF PERSON_EMP_REC.match_ind = '21' THEN
856 DECLARE
857 l_employer_party_id NUMBER;
858
859 BEGIN
860 -- Validate the values of person_emp_rec.
861 validate_emp_dtls(PERSON_EMP_REC,l_employer_party_id,l_error_code);
862 IF l_error_code IS NULL THEN -- nsidana Bug 3541735 : Corrected the check from not null --> null.
863 igs_ad_emp_dtl_pkg.update_row (
864 x_rowid => dup_emp_dtlsc_rec.row_id,
865 x_employment_history_id => dup_emp_dtlsc_rec.employment_history_id,
866 x_person_id => NVL(person_emp_rec.person_id,dup_emp_dtlsc_rec.person_id),
867 x_start_dt => NVL(person_emp_rec.start_date,dup_emp_dtlsc_rec.start_dt),
868 x_end_dt => NVL(person_emp_rec.end_date,dup_emp_dtlsc_rec.end_dt),
869 x_type_of_employment => NVL(person_emp_rec.type_of_employment,dup_emp_dtlsc_rec.type_of_employment),
870 x_fraction_of_employment => NVL(person_emp_rec.fraction_of_employment,dup_emp_dtlsc_rec.fraction_of_employment),
871 x_tenure_of_employment => NVL(person_emp_rec.tenure_of_employment,dup_emp_dtlsc_rec.tenure_of_employment),
872 x_position => NVL(person_emp_rec.position,dup_emp_dtlsc_rec.position),
873 x_occupational_title_code => NVL(person_emp_rec.occupational_title_code,dup_emp_dtlsc_rec.occupational_title_code),
874 x_occupational_title => dup_emp_dtlsc_rec.occupational_title,
875 x_weekly_work_hours => NVL(person_emp_rec.weekly_work_hrs,dup_emp_dtlsc_rec.weekly_work_hours),
876 x_comments => NVL(person_emp_rec.comments,dup_emp_dtlsc_rec.comments),
877 x_employer => NVL(person_emp_rec.employer,dup_emp_dtlsc_rec.employer),
878 x_employed_by_division_name => NVL(person_emp_rec.employed_by_division_name,dup_emp_dtlsc_rec.employed_by_division_name),
879 x_branch => NVL(person_emp_rec.branch,dup_emp_dtlsc_rec.branch),
880 x_military_rank => NVL(person_emp_rec.military_rank,dup_emp_dtlsc_rec.military_rank),
881 x_served => NVL(person_emp_rec.served,dup_emp_dtlsc_rec.served),
882 x_station => NVL(person_emp_rec.station,dup_emp_dtlsc_rec.station),
883 x_contact => NVL(person_emp_rec.contact,dup_emp_dtlsc_rec.contact), ---Bug : 2037512
884 x_msg_data => l_msg_data,
885 x_return_status => l_return_status,
886 x_object_version_number => dup_emp_dtlsc_rec.object_version_number,
887 x_employed_by_party_id => NVL(l_employer_party_id,dup_emp_dtlsc_rec.employed_by_party_id),
888 x_reason_for_leaving => NVL(person_emp_rec.reason_for_leaving,dup_emp_dtlsc_rec.reason_for_leaving),
889 x_mode => 'R'
890 );
891
892
893 IF l_return_Status IN ('E','U') THEN
894 UPDATE IGS_AD_EMP_INT_all
895 SET error_code = 'E014',
896 status = '3'
897 WHERE interface_emp_id = person_emp_rec.interface_emp_id;
898
899 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
900 IF (l_request_id IS NULL) THEN
901 l_request_id := fnd_global.conc_request_id;
902 END IF;
903
904 l_label := 'igs.plsql.igs_ad_imp_006.prc_pe_empnt_dtls.exception: '|| 'E014';
905
906 l_debug_str := 'IGS_AD_IMP_006.Prc_Pe_Empnt_Dtls ' ||
907 'INTERFACE Emp Id : ' || Person_Emp_Rec.Interface_Emp_Id ||
908 ' Status : 3 ' || 'ErrorCode : E014 '|| l_msg_data;
909
910 fnd_log.string_with_context( fnd_log.level_exception,
911 l_label,
912 l_debug_str, NULL,
913 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
914 END IF;
915
916 IF l_enable_log = 'Y' THEN
917 igs_ad_imp_001.logerrormessage(Person_Emp_Rec.Interface_Emp_Id,'E014');
918 END IF;
919
920 ELSE
921 UPDATE igs_ad_emp_int_all
922 SET match_ind = cst_mi_val_18 ,
923 STATUS = cst_stat_val_1, ERROR_CODE = NULL
924 WHERE interface_emp_id = person_emp_rec.interface_emp_id;
925 END IF; -- if l_ret_status
926 END IF; -- if l_err_code
927 END; -- inner begin
928 END IF; -- if match_ind
929 END IF; -- if l_rule
930 ELSE -- Duplicate Not exist -- so create new history details
931 crt_emp_dtls(PERSON_EMP_REC);
932 END IF; -- if chk_dup
933 END; -- begin
934 IF l_processed_records = 100 THEN
935 COMMIT;
936 l_processed_records := 0 ;
937 END IF;
938 END LOOP;
939 END Prc_Pe_Empnt_Dtls;
940
941 PROCEDURE Prc_Pe_Extclr_Dtls(
942 P_SOURCE_TYPE_ID IN NUMBER,
943 P_BATCH_ID IN VARCHAR2
944 ) AS
945
946 l_dup_person_interest_id IGS_AD_EXCURR_INT.DUP_PERSON_INTEREST_ID%TYPE;
947 l_last_update_date IGS_AD_EXTRACURR_ACT_V.LAST_UPDATE_DATE%TYPE;
948 l_interface_run_id igs_ad_interface_all.interface_run_id%TYPE;
949 CURSOR extracurr(cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE) IS
950 SELECT hii.*, i.person_id
951 FROM igs_ad_excurr_int_all hii, igs_ad_interface_all i
952 WHERE hii.interface_run_id = cp_interface_run_id
953 AND i.interface_id = hii.interface_id
954 AND i.interface_run_id = cp_interface_run_id
955 AND hii.status = '2';
956
957 extracurr_rec extracurr%ROWTYPE;
958 l_Var VARCHAR2(1);
959 l_error_code VARCHAR2(10);
960 l_msg_data VARCHAR2(2000);
961 l_return_status VARCHAR2(1);
962 l_rule VARCHAR2(1);
963 l_DUP_VAR BOOLEAN;
964 l_RowId VARCHAR2(25);
965 l_person_interest_id NUMBER;
966 l_processed_records NUMBER(5) := 0 ;
967 l_prog_label VARCHAR2(4000);
968 l_label VARCHAR2(4000);
969 l_debug_str VARCHAR2(4000);
970 l_enable_log VARCHAR2(1);
971 l_request_id NUMBER(10);
972
973 -------------Start of local procedure validate_pe_excurr ------------
974 PROCEDURE validate_pe_excurr (EXTRACURR_REC IN extracurr%ROWTYPE, p_error_code OUT NOCOPY VARCHAR2) AS
975 l_var VARCHAR2(1);
976
977 CURSOR birth_date_cur(cp_person_id igs_pe_person_base_v.person_id%TYPE) IS
978 SELECT birth_date
979 FROM igs_pe_person_base_v
980 WHERE person_id = cp_person_id;
981
982 l_birth_date igs_pe_person_base_v.birth_date%TYPE;
983
984 BEGIN
985
986 --3. Perform validations for the following columns
987 --INTEREST_TYPE
988
989 --LEVEL_OF_PARTICIPATION
990 IF EXTRACURR_REC.LEVEL_OF_PARTICIPATION IS NOT NULL THEN
991 IF NOT
992 (igs_pe_pers_imp_001.validate_lookup_type_code('PARTICIPATION_LEVEL',extracurr_rec.level_of_participation,222))
993 THEN
994 p_error_code := 'E233';
995 RAISE no_data_found;
996 END IF;
997 END IF;
998
999 --HOURS_PER_WEEK
1000 IF EXTRACURR_REC.HOURS_PER_WEEK IS NOT NULL THEN
1001 IF EXTRACURR_REC.HOURS_PER_WEEK > 0 AND EXTRACURR_REC.HOURS_PER_WEEK <= 168 THEN
1002 --Validation Successful
1003 NULL;
1004 ELSE
1005 --Validation Unsuccessful
1006 p_error_code := 'E227';
1007 RAISE NO_DATA_FOUND;
1008 END IF;
1009 END IF;
1010
1011 --WEEKS_PER_YEAR
1012 IF EXTRACURR_REC.WEEKS_PER_YEAR IS NOT NULL THEN
1013 IF EXTRACURR_REC.WEEKS_PER_YEAR > 0 AND EXTRACURR_REC.WEEKS_PER_YEAR <= 52 THEN
1014 --Validation Successful
1015 NULL;
1016 ELSE
1017 --Validation Unsuccessful
1018 p_error_code := 'E219';
1019 RAISE NO_DATA_FOUND;
1020 END IF;
1021 END IF;
1022
1023 --COMMENTS
1024 --No Validation for this field
1025 --START_DATE
1026
1027 --No Validation for this field.
1028 --END_DATE
1029
1030 OPEN birth_date_cur(extracurr_rec.person_id);
1031 FETCH birth_date_cur INTO l_birth_date;
1032 CLOSE birth_date_cur;
1033
1034 IF l_birth_date IS NOT NULL AND EXTRACURR_REC.START_DATE IS NOT NULL THEN
1035 IF EXTRACURR_REC.START_DATE < l_birth_date THEN
1036 p_error_code := 'E222';
1037 RAISE NO_DATA_FOUND;
1038 ELSE
1039 NULL;
1040 END IF;
1041 END IF;
1042
1043
1044 IF EXTRACURR_REC.END_DATE IS NOT NULL AND EXTRACURR_REC.START_DATE IS NULL THEN
1045 --Validation Unsuccessful
1046 p_error_code := 'E212';
1047 RAISE NO_DATA_FOUND;
1048 ELSIF ( EXTRACURR_REC.START_DATE IS NOT NULL
1049 AND EXTRACURR_REC.END_DATE IS NOT NULL
1050 AND TRUNC(EXTRACURR_REC.END_DATE) >= TRUNC(EXTRACURR_REC.START_DATE)
1051 ) OR EXTRACURR_REC.END_DATE IS NULL THEN
1052 --Validation Successful
1053 NULL;
1054 ELSE
1055 p_error_code := 'E208';
1056 RAISE NO_DATA_FOUND;
1057 END IF;
1058
1059 --SPORT_INDICATOR
1060 IF EXTRACURR_REC.SPORT_INDICATOR IS NOT NULL THEN
1061 IF EXTRACURR_REC.SPORT_INDICATOR IN ('Y','N' ) THEN
1062 --Validation Successful
1063 NULL;
1064 ELSE
1065 --Validation Unsuccessful
1066 p_error_code := 'E213';
1067 RAISE NO_DATA_FOUND;
1068 END IF;
1069 END IF;
1070
1071 --SUB_INTEREST_TYPE_CODE
1072 -- IF EXTRACURR_REC.SUB_INTEREST_TYPE_CODE IS NOT NULL THEN
1073 -- IF EXTRACURR_REC.SUB_INTEREST_TYPE_CODE IN ('INTEREST_TYPE','ENTERTAINMENT') THEN
1074 --Validation Successful
1075 -- now validate the INTEREST_TYPE code whether it belongs to the lookup_type as
1076 -- per the SUB_INTEREST_TYPE_CODE
1077 -- In the form the SUB_INTEREST_TYPE_CODE is populated internally when a value for the
1078 -- INTEREST_TYPE Code is selected.
1079 IF EXTRACURR_REC.SUB_INTEREST_TYPE_CODE IS NOT NULL THEN
1080 IF EXTRACURR_REC.SUB_INTEREST_TYPE_CODE NOT IN ('INTEREST_TYPE','ENTERTAINMENT') THEN
1081 p_error_code := 'E231';
1082 RAISE NO_DATA_FOUND;
1083 ELSIF EXTRACURR_REC.INTEREST_TYPE_CODE IS NULL THEN
1084 p_error_code := 'E216';
1085 RAISE NO_DATA_FOUND;
1086 ELSE
1087 IF NOT
1088 (igs_pe_pers_imp_001.validate_lookup_type_code(extracurr_rec.sub_interest_type_code,extracurr_rec.interest_type_code,222))
1089 THEN
1090 p_error_code := 'E254';
1091 RAISE NO_DATA_FOUND;
1092 END IF;
1093 END IF;
1094
1095 ELSE
1096 IF EXTRACURR_REC.INTEREST_TYPE_CODE IS NOT NULL THEN
1097 IF NOT
1098 (igs_pe_pers_imp_001.validate_lookup_type_code('INTEREST_TYPE',extracurr_rec.interest_type_code,222))
1099 OR
1100 (igs_pe_pers_imp_001.validate_lookup_type_code('ENTERTAINMENT',extracurr_rec.interest_type_code,222))
1101 THEN
1102 p_error_code := 'E232';
1103 RAISE no_data_found;
1104 END IF;
1105 END IF;
1106 END IF;
1107
1108 -- added Activity Source CD as part of ID prospective applicant part 2 of 1
1109 --ACTIVITY SOURCE CD
1110 IF EXTRACURR_REC.ACTIVITY_SOURCE_CD IS NOT NULL THEN
1111 IF NOT
1112 (igs_pe_pers_imp_001.validate_lookup_type_code('ACTIVITY_SOURCE',EXTRACURR_REC.ACTIVITY_SOURCE_CD,8405))
1113 THEN
1114 p_error_code := 'E230';
1115 RAISE NO_DATA_FOUND;
1116 END IF;
1117
1118 ELSE -- This column has been newly added to the existing table
1119 -- it cannot be made not null at the data base level.
1120 -- checking for it programatically and giving an error when
1121 -- the column is null
1122 p_error_code := 'E215';
1123 RAISE NO_DATA_FOUND;
1124 END IF;
1125
1126
1127 --Validation successful
1128 p_error_code := NULL;
1129 UPDATE igs_ad_excurr_int_all
1130 SET STATUS = '1'
1131 WHERE INTERFACE_EXCURR_ID = EXTRACURR_REC.INTERFACE_EXCURR_ID;
1132 EXCEPTION
1133 WHEN NO_DATA_FOUND THEN
1134 -- Validation Unsuccessful
1135 UPDATE igs_ad_excurr_int_all
1136 SET STATUS = '3',
1137 ERROR_CODE = p_error_code
1138 WHERE INTERFACE_EXCURR_ID = EXTRACURR_REC.INTERFACE_EXCURR_ID;
1139
1140 IF l_enable_log = 'Y' THEN
1141 igs_ad_imp_001.logerrormessage(EXTRACURR_REC.INTERFACE_EXCURR_ID,p_error_code);
1142 END IF;
1143
1144 END validate_pe_excurr;
1145 -------------End of local procedure validate_pe_excurr ------------
1146 -- Local Procedure crt_extra_cur
1147 PROCEDURE crt_extra_cur(EXTRACURR_REC extracurr%ROWTYPE) AS
1148 l_rowid VARCHAR2(25);
1149 l_person_interest_id NUMBER;
1150 l_return_status VARCHAR2(1);
1151 l_msg_count NUMBER;
1152 l_msg_data VARCHAR2(2000);
1153 l_extracurr_act_id NUMBER;
1154 l_error_code VARCHAR2(10);
1155 l_sub_interest_type_code EXTRACURR_REC.SUB_INTEREST_TYPE_CODE%TYPE;
1156 l_object_version_number NUMBER;
1157
1158 BEGIN
1159
1160 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
1161
1162 IF (l_request_id IS NULL) THEN
1163 l_request_id := fnd_global.conc_request_id;
1164 END IF;
1165
1166 l_label := 'igs.plsql.igs_ad_imp_006.crt_extra_cur.Begin';
1167 l_debug_str := 'INTERFACE Excurr Id : ' || extracurr_rec.interface_excurr_id;
1168
1169 fnd_log.string_with_context( fnd_log.level_procedure,
1170 l_label,
1171 l_debug_str, NULL,
1172 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1173 END IF;
1174
1175 validate_pe_excurr(EXTRACURR_REC, l_error_code);
1176
1177 IF l_error_code IS NULL THEN
1178 IF EXTRACURR_REC.INTEREST_TYPE_CODE IS NOT NULL AND EXTRACURR_REC.SUB_INTEREST_TYPE_CODE IS NULL THEN
1179 IF NOT
1180 (igs_pe_pers_imp_001.validate_lookup_type_code('INTEREST_TYPE',extracurr_rec.interest_type_code,222))
1181 OR
1182 (igs_pe_pers_imp_001.validate_lookup_type_code('ENTERTAINMENT',extracurr_rec.interest_type_code,222))
1183 THEN
1184 RAISE NO_DATA_FOUND;
1185 END IF;
1186 ELSE
1187 l_sub_interest_type_code := EXTRACURR_REC.SUB_INTEREST_TYPE_CODE;
1188 END IF;
1189
1190 --Igs_Ad_Extracurr_Act_Pkg signature is modified to include HZ.K impact changes
1191 Igs_Ad_Extracurr_Act_Pkg.Insert_Row(
1192 x_rowid => l_RowId,
1193 x_person_interest_id => l_Person_Interest_Id,
1194 x_person_id => extracurr_rec.person_id,
1195 x_interest_type_code => extracurr_rec.interest_type_code,
1196 x_comments => extracurr_rec.comments,
1197 x_start_date => EXTRACURR_REC.Start_Date,
1198 x_end_date => EXTRACURR_REC.End_Date,
1199 x_hours_per_week => EXTRACURR_REC.hours_per_week,
1200 x_weeks_per_year => EXTRACURR_REC.weeks_per_year,
1201 x_level_of_interest => EXTRACURR_REC.level_of_interest,
1202 x_level_of_participation => EXTRACURR_REC.level_Of_Participation,
1203 x_sport_indicator => EXTRACURR_REC.sport_indicator,
1204 x_sub_interest_type_code => l_sub_interest_type_code,
1205 x_interest_name => EXTRACURR_REC.Interest_name,
1206 x_team => EXTRACURR_REC.team,
1207 x_wh_update_date => NULL,
1208 -- added Activity Source CD as part of ID prospective applicant part 2 of 1
1209 X_ACTIVITY_SOURCE_CD => extracurr_rec.activity_source_cd,
1210 x_last_update_date => l_last_update_date,
1211 x_msg_Data=> l_msg_Data,
1212 x_return_Status => l_return_status,
1213 x_object_version_number => l_object_version_number,
1214 x_mode => 'R');
1215
1216 IF l_return_Status IN ('E','U') THEN
1217 UPDATE
1218 igs_ad_excurr_int_all
1219 SET
1220 ERROR_CODE = 'E322',
1221 STATUS = '3'
1222 WHERE
1223 INTERFACE_EXCURR_ID = extracurr_rec.INTERFACE_EXCURR_ID;
1224
1225
1226 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
1227
1228 IF (l_request_id IS NULL) THEN
1229 l_request_id := fnd_global.conc_request_id;
1230 END IF;
1231
1232 l_label := 'igs.plsql.igs_ad_imp_006.crt_extra_cur.exception';
1233
1234 l_debug_str := 'IGS_AD_IMP_006.Prc_Pe_Extclr_Dtls ' ||
1235 'INTERFACE Excurr Id : ' || EXTRACURR_REC.INTERFACE_EXCURR_ID ||
1236 ' Status : 3 ' || 'ErrorCode : E322 '|| l_msg_data;
1237
1238 fnd_log.string_with_context( fnd_log.level_exception,
1239 l_label,
1240 l_debug_str, NULL,
1241 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1242 END IF;
1243
1244 IF l_enable_log = 'Y' THEN
1245 igs_ad_imp_001.logerrormessage(EXTRACURR_REC.INTERFACE_EXCURR_ID,'E322');
1246 END IF;
1247
1248 ELSE
1249 UPDATE
1250 igs_ad_excurr_int_all
1251 SET
1252 STATUS = '1'
1253 WHERE
1254 INTERFACE_EXCURR_ID = extracurr_rec.INTERFACE_EXCURR_ID;
1255 END IF;
1256 END IF;
1257 EXCEPTION
1258 WHEN OTHERS THEN
1259 -- Validation Unsuccessful
1260 UPDATE igs_ad_excurr_int_all
1261 SET STATUS = '3',
1262 ERROR_CODE = 'E322'
1263 WHERE INTERFACE_EXCURR_ID = EXTRACURR_REC.INTERFACE_EXCURR_ID;
1264
1265 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
1266
1267 IF (l_request_id IS NULL) THEN
1268 l_request_id := fnd_global.conc_request_id;
1269 END IF;
1270
1271 l_label := 'igs.plsql.Igs_Ad_Imp_006.crt_extra_cur.exception';
1272
1273 l_debug_str := 'Igs_Ad_Imp_006.Prc_Pe_Extclr_Dtls ' ||
1274 'INTERFACE Excurr Id : ' || IGS_GE_NUMBER.TO_CANN(EXTRACURR_REC.INTERFACE_EXCURR_ID) ||
1275 ' Status : 3 ' || 'ErrorCode : E322 ' || SQLERRM;
1276
1277 fnd_log.string_with_context( fnd_log.level_exception,
1278 l_label,
1279 l_debug_str, NULL,
1280 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1281 END IF;
1282
1283 IF l_enable_log = 'Y' THEN
1284 igs_ad_imp_001.logerrormessage(EXTRACURR_REC.INTERFACE_EXCURR_ID,'E322');
1285 END IF;
1286
1287 END crt_extra_cur;
1288 -- End Local crt_extra_cur
1289
1290 BEGIN
1291
1292 l_enable_log := igs_ad_imp_001.g_enable_log;
1293 l_prog_label := 'igs.plsql.igs_ad_imp_006.prc_pe_extclr_dtls';
1294 l_label := 'igs.plsql.igs_ad_imp_006.prc_pe_extclr_dtls.';
1295 l_interface_run_id := igs_ad_imp_001.g_interface_run_id;
1296 -- No duplicate check!! Hence, different logic
1297 FOR extracurr_rec IN extracurr(l_interface_run_id) LOOP
1298 l_processed_records := l_processed_records + 1;
1299
1300 -- Find out NOCOPY the duplicate check from HQ ..sine dup_extracurr_act_id is removed from the table
1301 extracurr_rec.interest_type_code := UPPER(extracurr_rec.interest_type_code);
1302 extracurr_rec.sub_interest_type_code := UPPER(extracurr_rec.sub_interest_type_code);
1303 extracurr_rec.activity_source_cd := UPPER(extracurr_rec.activity_source_cd);
1304 extracurr_rec.level_of_interest := UPPER(extracurr_rec.level_of_interest);
1305 extracurr_rec.level_of_participation := UPPER(extracurr_rec.level_of_participation);
1306 extracurr_rec.start_date := TRUNC(extracurr_rec.start_date);
1307 extracurr_rec.end_date := TRUNC(extracurr_rec.end_date);
1308 crt_extra_cur(extracurr_rec);
1309 IF l_processed_records = 100 THEN
1310 COMMIT;
1311 l_processed_records := 0 ;
1312 END IF;
1313
1314 END LOOP;
1315 END Prc_Pe_Extclr_Dtls;
1316 END Igs_Ad_Imp_006;
[Home] [Help]