eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.IGS_AD_IMP_006 dependencies on IGS_AD_EMP_INT

Line 27: skpandey 16-May-2006 Bug - 5205911 added comments column to IGS_AD_EMP_INT_ALL

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';

Line 54: l_error_code IGS_AD_EMP_INT.Error_Code%TYPE;

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);

Line 143: l_error_code IGS_AD_EMP_INT.Error_Code%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:

Line 254: FROM igs_ad_emp_int_all hii, igs_ad_interface_all i

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';

Line 271: l_error_code IGS_AD_EMP_INT.Error_Code%TYPE;

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);

Line 295: CURSOR employer_party_number_cur(cp_employer_party_number igs_ad_emp_int_all.employer_party_number%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

Line 317: UPDATE IGS_AD_EMP_INT_ALL

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:

Line 335: UPDATE IGS_AD_EMP_INT_ALL

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

Line 350: UPDATE IGS_AD_EMP_INT_ALL

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

Line 368: UPDATE IGS_AD_EMP_INT_ALL

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

Line 384: UPDATE IGS_AD_EMP_INT_ALL

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

Line 401: UPDATE IGS_AD_EMP_INT_ALL

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

Line 419: UPDATE IGS_AD_EMP_INT_ALL

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

Line 437: UPDATE IGS_AD_EMP_INT_ALL

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

Line 451: UPDATE IGS_AD_EMP_INT_ALL

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

Line 467: UPDATE IGS_AD_EMP_INT_ALL

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

Line 480: UPDATE IGS_AD_EMP_INT_ALL

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:

Line 497: l_error_code IGS_AD_EMP_INT.Error_Code%TYPE;

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:

Line 549: UPDATE IGS_AD_EMP_INT_all

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:

Line 575: UPDATE IGS_AD_EMP_INT_all

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;

Line 592: UPDATE igs_ad_emp_int_all

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

Line 602: UPDATE igs_ad_emp_int_all mi

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'

Line 621: UPDATE igs_ad_emp_int_all

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';

Line 630: UPDATE igs_ad_emp_int_all

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'))

Line 640: UPDATE igs_ad_emp_int_all mi

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

Line 672: UPDATE igs_ad_emp_int_all mi

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

Line 790: UPDATE IGS_AD_EMP_INT_all

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:

Line 818: UPDATE igs_ad_emp_int_all

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;

Line 826: UPDATE igs_ad_emp_int_all

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:

Line 894: UPDATE IGS_AD_EMP_INT_all

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:

Line 921: UPDATE igs_ad_emp_int_all

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