APPS.IGS_AD_IMP_008 dependencies on IGS_AD_EMP_DTL

Line 3266: --Signature of Igs_Ad_Emp_Dtl_Pkg is changed to include HZ.K impact changes

3262: SET status = '3', error_code = l_error_code
3263: WHERE interface_relemp_id = p_relemp_rec.interface_relemp_id;
3264: ELSE
3265: BEGIN
3266: --Signature of Igs_Ad_Emp_Dtl_Pkg is changed to include HZ.K impact changes
3267: Igs_Ad_Emp_Dtl_Pkg.Insert_Row (
3268: X_ROWID => l_RowId,
3269: x_employment_history_id => l_Employment_History_Id,
3270: x_PERSON_ID => p_person_id,

Line 3267: Igs_Ad_Emp_Dtl_Pkg.Insert_Row (

3263: WHERE interface_relemp_id = p_relemp_rec.interface_relemp_id;
3264: ELSE
3265: BEGIN
3266: --Signature of Igs_Ad_Emp_Dtl_Pkg is changed to include HZ.K impact changes
3267: Igs_Ad_Emp_Dtl_Pkg.Insert_Row (
3268: X_ROWID => l_RowId,
3269: x_employment_history_id => l_Employment_History_Id,
3270: x_PERSON_ID => p_person_id,
3271: x_START_DT => P_RELEMP_REC.Start_Dt,

Line 3594: FROM IGS_AD_EMP_DTL pe

3590: match_ind = '19'
3591: WHERE mi.interface_relations_id = p_interface_relations_id
3592: AND mi.status = '2'
3593: AND EXISTS ( SELECT 1
3594: FROM IGS_AD_EMP_DTL pe
3595: WHERE PERSON_ID = P_REL_PERSON_ID AND
3596: ((NVL(UPPER(pe.EMPLOYER),'*!') = NVL(UPPER(mi.employer),'*')) OR
3597: (NVL(mi.employer_party_number,'*!') = NVL(pe.employed_by_party_number,'*'))) AND
3598: NVL(TRUNC(pe.START_DT),TO_DATE('4712/12/31','YYYY/MM/DD')) = NVL(TRUNC(mi.start_dt),TO_DATE('4712/12/31','YYYY/MM/DD'))

Line 3631: FROM IGS_AD_EMP_DTL pe

3627: WHERE mi.interface_relations_id = p_interface_relations_id
3628: AND mi.match_ind IS NULL
3629: AND mi.status = '2'
3630: AND EXISTS ( SELECT '1'
3631: FROM IGS_AD_EMP_DTL pe
3632: WHERE pe.PERSON_ID = p_rel_person_id AND
3633: ((TRUNC(pe.START_DT) = TRUNC(mi.Start_Dt) )OR (pe.start_dt IS NULL AND mi.Start_Dt IS NULL ))AND
3634: ((TRUNC(pe.END_DT) = TRUNC(mi.End_Dt))OR ( pe.end_dt IS NULL AND mi.End_Dt IS NULL )) AND
3635: ((pe.TYPE_OF_EMPLOYMENT = UPPER(mi.Type_Of_Employment)) OR (pe.TYPE_OF_EMPLOYMENT IS NULL AND mi.Type_Of_Employment IS NULL )) AND

Line 3654: FROM IGS_AD_EMP_DTL pe

3650: UPDATE igs_ad_relemp_int_all mi
3651: SET status = '3',
3652: match_ind = '20',
3653: dup_employment_history_id = (SELECT pe.employment_history_id
3654: FROM IGS_AD_EMP_DTL pe
3655: WHERE pe.person_id = P_REL_PERSON_ID AND
3656: ((NVL(UPPER(pe.EMPLOYER),'*!') = NVL(UPPER(mi.EMPLOYER),'*')) OR
3657: (NVL(pe.EMPLOYED_BY_PARTY_NUMBER,'*!') = NVL(mi.EMPLOYER_PARTY_NUMBER,'*') ))AND
3658: NVL(TRUNC(pe.START_DT),TO_DATE('4712/12/31','YYYY/MM/DD')) = NVL(TRUNC(mi.start_dt),TO_DATE('4712/12/31','YYYY/MM/DD'))

Line 3664: FROM IGS_AD_EMP_DTL pe

3660: WHERE mi.interface_relations_id = p_interface_relations_id
3661: AND mi.match_ind IS NULL
3662: AND mi.status = '2'
3663: AND EXISTS (SELECT '1'
3664: FROM IGS_AD_EMP_DTL pe
3665: WHERE pe.PERSON_ID = P_REL_PERSON_ID AND
3666: ((NVL(UPPER(pe.EMPLOYER),'*!') = NVL(UPPER(mi.EMPLOYER),'*')) OR
3667: (NVL(pe.EMPLOYED_BY_PARTY_NUMBER,'*!') = NVL(mi.EMPLOYER_PARTY_NUMBER,'*'))) AND
3668: NVL(TRUNC(pe.START_DT),TO_DATE('4712/12/31','YYYY/MM/DD')) = NVL(TRUNC(mi.start_dt),TO_DATE('4712/12/31','YYYY/MM/DD'))

Line 3678: FROM igs_ad_emp_dtl pe

3674: DECLARE
3675: CURSOR chk_dup_cur(cp_Relns_Emp_Dtls_Rec Relns_Emp_Dtls%ROWTYPE,
3676: cp_person_id NUMBER) IS
3677: SELECT pe.*
3678: FROM igs_ad_emp_dtl pe
3679: WHERE pe.person_id = cp_person_id
3680: AND (((NVL(UPPER(pe.employer),'*!')) = NVL(UPPER(cp_Relns_Emp_Dtls_Rec.employer),'*'))
3681: OR (NVL(pe.employed_by_party_number,'*!') = NVL(cp_Relns_Emp_Dtls_Rec.employer_party_number,'*')))
3682: AND NVL(TRUNC(pe.START_DT),TO_DATE('4712/12/31','YYYY/MM/DD')) = NVL(TRUNC(cp_Relns_Emp_Dtls_Rec.start_dt),TO_DATE('4712/12/31','YYYY/MM/DD'));

Line 3712: --Signature of Igs_Ad_Emp_Dtl_Pkg is changed to include HZ.K impact changes

3708: UPDATE igs_ad_relemp_int_all
3709: SET status = '3', error_code = l_error_code
3710: WHERE interface_relemp_id = relns_emp_dtls_rec.interface_relemp_id;
3711: ELSE
3712: --Signature of Igs_Ad_Emp_Dtl_Pkg is changed to include HZ.K impact changes
3713: Igs_Ad_Emp_Dtl_Pkg.Update_Row (
3714: x_rowid => chk_dup_rec.Row_Id,
3715: x_employment_history_id => chk_dup_rec.Employment_History_Id,
3716: x_person_id => NVL(p_rel_person_id,chk_dup_rec.person_id),

Line 3713: Igs_Ad_Emp_Dtl_Pkg.Update_Row (

3709: SET status = '3', error_code = l_error_code
3710: WHERE interface_relemp_id = relns_emp_dtls_rec.interface_relemp_id;
3711: ELSE
3712: --Signature of Igs_Ad_Emp_Dtl_Pkg is changed to include HZ.K impact changes
3713: Igs_Ad_Emp_Dtl_Pkg.Update_Row (
3714: x_rowid => chk_dup_rec.Row_Id,
3715: x_employment_history_id => chk_dup_rec.Employment_History_Id,
3716: x_person_id => NVL(p_rel_person_id,chk_dup_rec.person_id),
3717: x_start_dt => NVL(relns_emp_dtls_rec.start_dt,chk_dup_rec.start_dt),

Line 3790: --Signature of Igs_Ad_Emp_Dtl_Pkg is changed to include HZ.K impact changes

3786: UPDATE igs_ad_relemp_int_all
3787: SET status = '3', error_code = l_error_code
3788: WHERE interface_relemp_id = relns_emp_dtls_rec.interface_relemp_id;
3789: ELSE
3790: --Signature of Igs_Ad_Emp_Dtl_Pkg is changed to include HZ.K impact changes
3791: Igs_Ad_Emp_Dtl_Pkg.Update_Row (
3792: x_rowid => chk_dup_rec.Row_Id,
3793: x_employment_history_id => chk_dup_rec.Employment_History_Id,
3794: x_person_id => NVL(p_rel_person_id,chk_dup_rec.person_id),

Line 3791: Igs_Ad_Emp_Dtl_Pkg.Update_Row (

3787: SET status = '3', error_code = l_error_code
3788: WHERE interface_relemp_id = relns_emp_dtls_rec.interface_relemp_id;
3789: ELSE
3790: --Signature of Igs_Ad_Emp_Dtl_Pkg is changed to include HZ.K impact changes
3791: Igs_Ad_Emp_Dtl_Pkg.Update_Row (
3792: x_rowid => chk_dup_rec.Row_Id,
3793: x_employment_history_id => chk_dup_rec.Employment_History_Id,
3794: x_person_id => NVL(p_rel_person_id,chk_dup_rec.person_id),
3795: x_start_dt => NVL(relns_emp_dtls_rec.start_dt,chk_dup_rec.start_dt),