APPS.IGS_AD_IMP_007 dependencies on IGS_PE_CITIZEN_INT

Line 2624: FROM igs_pe_citizen_int hii, igs_ad_interface_all i

2620: AS
2621: --cursor to select records from interface records
2622: CURSOR c_pcz(cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE) IS
2623: SELECT hii.*, i.person_id
2624: FROM igs_pe_citizen_int hii, igs_ad_interface_all i
2625: WHERE hii.interface_run_id = cp_interface_run_id
2626: AND i.interface_id = hii.interface_id
2627: AND i.interface_run_id = cp_interface_run_id
2628: AND hii.status = '2';

Line 2822: igs_ad_imp_001.logerrormessage(pcz_rec.interface_citizenship_id,'E127','IGS_PE_CITIZEN_INT');

2818: NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
2819: END IF;
2820:
2821: IF l_enable_log = 'Y' THEN
2822: igs_ad_imp_001.logerrormessage(pcz_rec.interface_citizenship_id,'E127','IGS_PE_CITIZEN_INT');
2823: END IF;
2824:
2825: ELSE
2826: status := '1';

Line 2827: UPDATE igs_pe_citizen_int

2823: END IF;
2824:
2825: ELSE
2826: status := '1';
2827: UPDATE igs_pe_citizen_int
2828: SET status='1'
2829: WHERE interface_citizenship_id= pcz_rec.interface_citizenship_id;
2830: END IF;
2831:

Line 2864: igs_ad_imp_001.logerrormessage(pcz_rec.interface_citizenship_id,l_error,'IGS_PE_CITIZEN_INT');

2860: error_code :='E127' ;
2861: END IF;
2862:
2863: IF l_enable_log = 'Y' THEN
2864: igs_ad_imp_001.logerrormessage(pcz_rec.interface_citizenship_id,l_error,'IGS_PE_CITIZEN_INT');
2865: END IF;
2866:
2867: UPDATE igs_pe_citizen_int
2868: SET error_code = l_error, status ='3'

Line 2867: UPDATE igs_pe_citizen_int

2863: IF l_enable_log = 'Y' THEN
2864: igs_ad_imp_001.logerrormessage(pcz_rec.interface_citizenship_id,l_error,'IGS_PE_CITIZEN_INT');
2865: END IF;
2866:
2867: UPDATE igs_pe_citizen_int
2868: SET error_code = l_error, status ='3'
2869: WHERE interface_citizenship_id= pcz_rec.interface_citizenship_id;
2870: END crt_pe_citizenship; -- end local proc to create new record
2871:

Line 3070: igs_ad_imp_001.logerrormessage(pcz_rec.interface_citizenship_id,'E128','IGS_PE_CITIZEN_INT');

3066: NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
3067: END IF;
3068:
3069: IF l_enable_log = 'Y' THEN
3070: igs_ad_imp_001.logerrormessage(pcz_rec.interface_citizenship_id,'E128','IGS_PE_CITIZEN_INT');
3071: END IF;
3072:
3073: ELSE
3074: status := '1';

Line 3106: igs_ad_imp_001.logerrormessage(pcz_rec.interface_citizenship_id,l_error,'IGS_PE_CITIZEN_INT');

3102: error_Code:= 'E128';
3103: END IF;
3104:
3105: IF l_enable_log = 'Y' THEN
3106: igs_ad_imp_001.logerrormessage(pcz_rec.interface_citizenship_id,l_error,'IGS_PE_CITIZEN_INT');
3107: END IF;
3108:
3109: IF c_valid_country%ISOPEN THEN
3110: CLOSE c_valid_country;

Line 3128: UPDATE igs_pe_citizen_int

3124: l_default_date := igs_ge_date.igsdate('9999/01/01');
3125:
3126: --1. If rule is E or I, then if the match_ind is not null, the combination is invalid
3127: IF l_rule IN ('E','I') THEN
3128: UPDATE igs_pe_citizen_int
3129: SET status = cst_stat_val_3,
3130: ERROR_CODE = cst_err_val_695 -- Error code depicting incorrect combination
3131: WHERE match_ind IS NOT NULL
3132: AND interface_run_id = l_interface_run_id

Line 3139: UPDATE igs_pe_citizen_int mi

3135:
3136:
3137: --2. If rule is E and duplicate exists, update match_ind to 19 and status to 1
3138: IF l_rule = 'E' THEN
3139: UPDATE igs_pe_citizen_int mi
3140: SET status = cst_stat_val_1,
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

Line 3156: UPDATE igs_pe_citizen_int

3152:
3153: --3. If rule is R and there match_ind is 18,19,22 or 23 then the records must have been
3154: -- processed in prior runs and didn't get updated .. update to status 1
3155: IF l_rule = 'R' THEN
3156: UPDATE igs_pe_citizen_int
3157: SET status = cst_stat_val_1
3158: WHERE interface_run_id = l_interface_run_id
3159: AND match_ind IN (cst_mi_val_18,cst_mi_val_19,cst_mi_val_22,cst_mi_val_23)
3160: AND status=cst_stat_val_2;

Line 3165: UPDATE igs_pe_citizen_int

3161: END IF;
3162:
3163: --4. If rule is R and match_ind is neither 21 nor 25 then error
3164: IF l_rule = 'R' THEN
3165: UPDATE igs_pe_citizen_int
3166: SET status = cst_stat_val_3,
3167: ERROR_CODE = cst_err_val_695
3168: WHERE interface_run_id = l_interface_run_id
3169: AND (match_ind IS NOT NULL AND match_ind NOT IN (cst_mi_val_21,cst_mi_val_25))

Line 3175: UPDATE igs_pe_citizen_int mi

3171: END IF;
3172:
3173: --5. If rule is R, set duplicated records with no discrepancy to status 1 and match_ind 23
3174: IF l_rule = 'R' THEN
3175: UPDATE igs_pe_citizen_int mi
3176: SET status = cst_stat_val_1,
3177: match_ind = cst_mi_val_23
3178: WHERE mi.interface_run_id = l_interface_run_id
3179: AND mi.match_ind IS NULL

Line 3197: UPDATE igs_pe_citizen_int mi

3193: END IF;
3194:
3195: --6. If rule in R records still exist, they are duplicates and have discrepancy .. update status=3,match_ind=20
3196: IF l_rule = 'R' THEN
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

Line 3242: UPDATE igs_pe_citizen_int

3238:
3239: upd_pe_citizenship(pcz_rec => pcz_rec,
3240: error_code => l_error_code,
3241: status => l_status);
3242: UPDATE igs_pe_citizen_int
3243: SET match_ind = cst_mi_val_18, status = l_status ,error_code = l_error_code
3244: WHERE interface_citizenship_id= pcz_rec.interface_citizenship_id;
3245:
3246: ELSIF l_rule = 'R' THEN

Line 3253: UPDATE igs_pe_citizen_int

3249: upd_pe_citizenship(pcz_rec => pcz_rec,
3250: error_code => l_error_code,
3251: status => l_status );
3252:
3253: UPDATE igs_pe_citizen_int
3254: SET status = l_status , error_code = l_error_code
3255: WHERE interface_citizenship_id = pcz_rec.interface_citizenship_id;
3256:
3257: END IF;