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_007 dependencies on HZ_CITIZENSHIP

Line 19: procedure prc_pe_hz_citizenship to validate against fnd territories.

15: lookup caching related changes, and cursor parameterization.
16:
17: || pkpatel 25-Jul-2003 3045079 : TRUNC of start_dt for API insert/update
18: || gmuralid 4-DEC-2002 SEVIS BUILD - Changed validation for country in
19: procedure prc_pe_hz_citizenship to validate against fnd territories.
20: Also made calls to the import processes in package igs_ad_imp_026
21: in the procedure prc_pe_intl_dtls
22:
23:

Line 26: Also modified validation for country in procedure prc_pe_hz_citizenship

22:
23:
24: gmuralid 29-NOV-2002 SEVIS BUILD removed procedures prc_pe_visa_pass and prc_pe_fund_dep
25: from both spec and body
26: Also modified validation for country in procedure prc_pe_hz_citizenship
27: gmaheswa 10-NOV-2003 Bug 3223043 HZ.K Impact changes
28: nsidana 6/21/2004 Bug 3541714 : Added validtion to check that the date disowned > date recognized
29: for citizenship details.
30: || gmaheswa 29-Sep-2004 BUG 3787210 Added Closed indicator check for the Alternate Person Id type.

Line 2602: PROCEDURE prc_pe_hz_citizenship

2598:
2599: -- PERSON INTERFACE DLD changes start here
2600: -- prc_pe_citizenship from IGSAD90 is moved here and renamed.
2601:
2602: PROCEDURE prc_pe_hz_citizenship
2603: ( p_source_type_id IN NUMBER,
2604: p_batch_id IN NUMBER
2605: )
2606: /*

Line 2673: SELECT count(1) FROM HZ_CITIZENSHIP

2669: igs_pe_person_base_v WHERE
2670: person_id = cp_person_id;
2671:
2672: CURSOR date_overlap(PCZ_REC c_pcz%ROWTYPE) IS
2673: SELECT count(1) FROM HZ_CITIZENSHIP
2674: WHERE
2675: party_id = PCZ_REC.PERSON_ID AND
2676: UPPER(country_code) = UPPER(PCZ_REC.Country_code) AND
2677: ( NVL(TRUNC(PCZ_REC.end_date),IGS_GE_DATE.igsdate('9999/01/01')) BETWEEN TRUNC(date_recognized) AND NVL(TRUNC(end_date),IGS_GE_DATE.igsdate('9999/01/01'))

Line 2877: p_date_recognized IN HZ_CITIZENSHIP.DATE_RECOGNIZED%TYPE) AS

2873: --start local proc to check if record already exists in system table , duplicate check
2874: PROCEDURE check_dup_citizenship(p_dup_var OUT NOCOPY BOOLEAN,
2875: p_person_id IN NUMBER,
2876: p_country_code IN VARCHAR2,
2877: p_date_recognized IN HZ_CITIZENSHIP.DATE_RECOGNIZED%TYPE) AS
2878: l_count VARCHAR2(1);
2879: BEGIN
2880:
2881:

Line 2884: FROM hz_citizenship

2880:
2881:
2882: SELECT 'X'
2883: INTO l_count
2884: FROM hz_citizenship
2885: WHERE party_id = p_person_id
2886: AND country_code = p_country_code
2887: AND TRUNC(date_recognized) = TRUNC(p_date_recognized) ; -- end_date IS NULL check removed.
2888:

Line 2918: FROM hz_citizenship

2914: WHERE territory_code = cp_territory_code;
2915:
2916: CURSOR c_null_hndlg (pcz_rec IN c_pcz%ROWTYPE) IS
2917: SELECT *
2918: FROM hz_citizenship
2919: WHERE party_id = pcz_rec.person_id
2920: AND country_code =pcz_rec.country_code
2921: AND date_recognized = pcz_rec.date_recognized;
2922:

Line 2930: FROM HZ_CITIZENSHIP

2926: WHERE person_id = pcz_rec.person_id;
2927:
2928: CURSOR date_overlap(PCZ_REC c_pcz%ROWTYPE) IS
2929: SELECT COUNT(1)
2930: FROM HZ_CITIZENSHIP
2931: WHERE
2932: party_id = PCZ_REC.PERSON_ID AND
2933: UPPER(country_code) = UPPER(PCZ_REC.Country_code) AND
2934: TRUNC(date_recognized) <> TRUNC(PCZ_REC.date_recognized) AND

Line 3119: l_prog_label := 'igs.plsql.igs_ad_imp_007.prc_pe_hz_citizenship';

3115:
3116: BEGIN
3117: l_interface_run_id := igs_ad_imp_001.g_interface_run_id;
3118: l_enable_log := igs_ad_imp_001.g_enable_log;
3119: l_prog_label := 'igs.plsql.igs_ad_imp_007.prc_pe_hz_citizenship';
3120: l_label := 'igs.plsql.igs_ad_imp_007.prc_pe_hz_citizenship.';
3121:
3122: l_rule :=igs_ad_imp_001.find_source_cat_rule(p_source_type_id,'PERSON_INTERNATIONAL_DETAILS');
3123:

Line 3120: l_label := 'igs.plsql.igs_ad_imp_007.prc_pe_hz_citizenship.';

3116: BEGIN
3117: l_interface_run_id := igs_ad_imp_001.g_interface_run_id;
3118: l_enable_log := igs_ad_imp_001.g_enable_log;
3119: l_prog_label := 'igs.plsql.igs_ad_imp_007.prc_pe_hz_citizenship';
3120: l_label := 'igs.plsql.igs_ad_imp_007.prc_pe_hz_citizenship.';
3121:
3122: l_rule :=igs_ad_imp_001.find_source_cat_rule(p_source_type_id,'PERSON_INTERNATIONAL_DETAILS');
3123:
3124: l_default_date := igs_ge_date.igsdate('9999/01/01');

Line 3145: FROM hz_citizenship pe, igs_ad_interface_all ii

3141: match_ind = cst_mi_val_19
3142: WHERE mi.interface_run_id = l_interface_run_id
3143: AND mi.status = cst_stat_val_2
3144: AND EXISTS ( SELECT '1'
3145: FROM hz_citizenship pe, igs_ad_interface_all ii
3146: WHERE ii.interface_run_id = l_interface_run_id
3147: AND ii.interface_id = mi.interface_id
3148: AND ii.person_id = pe.party_id
3149: AND UPPER(pe.country_code) = UPPER(mi.country_code)

Line 3182: FROM hz_citizenship pe, igs_ad_interface_all ii

3178: WHERE mi.interface_run_id = l_interface_run_id
3179: AND mi.match_ind IS NULL
3180: AND mi.status = cst_stat_val_2
3181: AND EXISTS ( SELECT '1'
3182: FROM hz_citizenship pe, igs_ad_interface_all ii
3183: WHERE ii.interface_run_id = l_interface_run_id
3184: AND ii.interface_id = mi.interface_id
3185: AND ii.person_id = pe.party_id
3186: AND pe.country_code = UPPER(mi.country_code)

Line 3201: FROM hz_citizenship pe, igs_ad_interface_all ii

3197: UPDATE igs_pe_citizen_int mi
3198: SET status = cst_stat_val_3,
3199: match_ind = cst_mi_val_20,
3200: dup_citizenship_id = (SELECT citizenship_id
3201: FROM hz_citizenship pe, igs_ad_interface_all ii
3202: WHERE ii.interface_run_id = l_interface_run_id
3203: AND ii.interface_id = mi.interface_id
3204: AND ii.person_id = pe.party_id
3205: AND pe.country_code = UPPER(mi.country_code)

Line 3211: FROM hz_citizenship pe, igs_ad_interface_all ii

3207: WHERE mi.interface_run_id = l_interface_run_id
3208: AND mi.match_ind IS NULL
3209: AND mi.status = cst_stat_val_2
3210: AND EXISTS (SELECT '1'
3211: FROM hz_citizenship pe, igs_ad_interface_all ii
3212: WHERE ii.interface_run_id = l_interface_run_id
3213: AND ii.interface_id = mi.interface_id
3214: AND ii.person_id = pe.party_id
3215: AND pe.country_code = UPPER(mi.country_code)

Line 3271: END prc_pe_hz_citizenship ;

3267: l_processed_records := 0;
3268: END IF;
3269:
3270: END LOOP;
3271: END prc_pe_hz_citizenship ;
3272:
3273: PROCEDURE prc_pe_fund_source
3274: ( p_source_type_id IN NUMBER,
3275: p_batch_id IN NUMBER

Line 3541: prc_pe_hz_citizenship ( p_source_type_id, p_batch_id) ;

3537: l_prog_label := 'igs.plsql.igs_ad_imp_007.prc_pe_intl_dtls';
3538: l_label := 'igs.plsql.igs_ad_imp_007.prc_pe_intl_dtls.';
3539:
3540: -- start the main parent import.
3541: prc_pe_hz_citizenship ( p_source_type_id, p_batch_id) ;
3542:
3543: -- start with the childs
3544: prc_pe_fund_source ( p_source_type_id, p_batch_id) ;
3545: