APPS.IGS_AD_IMP_007 dependencies on IGS_AD_MILITARY_INT_ALL

Line 87: FROM igs_ad_military_int_all mi, igs_ad_interface_all i

83: */
84:
85: CURSOR milt_cur(cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE) IS
86: SELECT mi.*,i.person_id
87: FROM igs_ad_military_int_all mi, igs_ad_interface_all i
88: WHERE mi.interface_run_id = cp_interface_run_id
89: AND mi.interface_id = i.interface_id
90: AND mi.interface_run_id = cp_interface_run_id
91: AND mi.status = '2';

Line 212: p_error_code igs_ad_military_int_all.ERROR_CODE%TYPE := NULL;

208:
209:
210: l_var VARCHAR2(1);
211: l_birth_dt IGS_AD_INTERFACE.BIRTH_DT%TYPE;
212: p_error_code igs_ad_military_int_all.ERROR_CODE%TYPE := NULL;
213:
214: BEGIN
215: IF NOT
216: (igs_pe_pers_imp_001.validate_lookup_type_code('PE_MIL_SEV_TYPE',military_rec.military_type_cd,8405))

Line 305: UPDATE igs_ad_military_int_all

301: p_error_code := NULL ;
302:
303: EXCEPTION
304: WHEN OTHERS THEN
305: UPDATE igs_ad_military_int_all
306: SET error_code = p_error_code,
307: status = '3'
308: WHERE interface_military_id = military_rec.interface_military_id ;
309:

Line 328: UPDATE igs_ad_military_int_all

324: l_rule :=Igs_Ad_Imp_001.FIND_SOURCE_CAT_RULE(P_SOURCE_TYPE_ID,'PERSON_MILITARY_DETAILS');
325:
326: -- If rule is E or I, then if the match_ind is not null, the combination is invalid
327: IF l_rule IN ('E','I') THEN
328: UPDATE igs_ad_military_int_all
329: SET status = cst_stat_val_3,
330: ERROR_CODE = cst_err_val_695 -- Error code depicting incorrect combination
331: WHERE match_ind IS NOT NULL
332: AND interface_run_id = l_interface_run_id

Line 338: UPDATE igs_ad_military_int_all mi

334: END IF;
335:
336: -- If rule is E and duplicate exists, update match_ind to 19 and status to 1
337: IF l_rule = 'E' THEN
338: UPDATE igs_ad_military_int_all mi
339: SET status = cst_stat_val_1,
340: match_ind = cst_mi_val_19
341: WHERE mi.interface_run_id = l_interface_run_id
342: AND mi.status = cst_stat_val_2

Line 355: UPDATE igs_ad_military_int_all

351:
352: -- If rule is R and there match_ind is 18,19,22 or 23 then the records must have been
353: -- processed in prior runs and didn't get updated .. update to status 1
354: IF l_rule = 'R' THEN
355: UPDATE igs_ad_military_int_all
356: SET status = cst_stat_val_1
357: WHERE interface_run_id = l_interface_run_id
358: AND match_ind IN (cst_mi_val_18,cst_mi_val_19,cst_mi_val_22,cst_mi_val_23)
359: AND status = cst_stat_val_2;

Line 364: UPDATE igs_ad_military_int_all

360: END IF;
361:
362: -- If rule is R and match_ind is neither 21 nor 25 then error
363: IF l_rule = 'R' THEN
364: UPDATE igs_ad_military_int_all
365: SET status = cst_stat_val_3,
366: ERROR_CODE = cst_err_val_695
367: WHERE interface_run_id = l_interface_run_id
368: AND (match_ind IS NOT NULL AND match_ind NOT IN (cst_mi_val_21,cst_mi_val_25))

Line 374: UPDATE igs_ad_military_int_all mi

370: END IF;
371:
372: -- If rule is R, set duplicated records with no discrepancy to status 1 and match_ind 23
373: IF l_rule = 'R' THEN
374: UPDATE igs_ad_military_int_all mi
375: SET status = cst_stat_val_1,
376: match_ind = cst_mi_val_23
377: WHERE mi.interface_run_id = l_interface_run_id
378: AND mi.match_ind IS NULL

Line 415: UPDATE igs_ad_military_int_all mi

411: );
412: END IF;
413: -- If rule in R records still exist, they are duplicates and have discrepancy .. update status=3,match_ind=20
414: IF l_rule = 'R' THEN
415: UPDATE igs_ad_military_int_all mi
416: SET status = cst_stat_val_3,
417: match_ind = cst_mi_val_20,
418: dup_milit_service_id = (SELECT milit_service_id
419: FROM igs_pe_mil_services_all pe, igs_ad_interface_all ii

Line 455: cp_start_date igs_ad_military_int_all.start_date%TYPE) IS

451: IF l_check = 'FALSE' THEN
452: DECLARE
453: CURSOR chk_dup_milit(cp_military_type_cd VARCHAR2,
454: cp_person_id VARCHAR2,
455: cp_start_date igs_ad_military_int_all.start_date%TYPE) IS
456: SELECT rowid,mi.*
457: FROM igs_pe_mil_services mi
458: WHERE UPPER(military_type_cd) = UPPER(cp_military_type_cd)
459: AND person_id = cp_person_id

Line 503: UPDATE igs_ad_military_int_all

499: x_separation_type_cd=>NVL(military_rec.separation_type_cd,dup_milit_rec.separation_type_cd),
500: x_assistance_type_cd=> NVL(military_rec.assistance_type_cd,dup_milit_rec.assistance_type_cd),
501: x_assistance_status_cd => NVL(military_rec.assistance_status_cd,dup_milit_rec.assistance_status_cd),
502: x_mode=>'R');
503: UPDATE igs_ad_military_int_all
504: SET match_ind =cst_mi_val_18,
505: status = cst_stat_val_1
506: WHERE interface_military_id = military_rec.interface_military_id;
507: EXCEPTION

Line 509: UPDATE igs_ad_military_int_all

505: status = cst_stat_val_1
506: WHERE interface_military_id = military_rec.interface_military_id;
507: EXCEPTION
508: WHEN OTHERS THEN
509: UPDATE igs_ad_military_int_all
510: SET ERROR_CODE = cst_err_val_14,
511: status = cst_stat_val_3
512: WHERE interface_military_id= military_rec.interface_military_id;
513:

Line 573: UPDATE igs_ad_military_int_all

569: x_assistance_type_cd=> NVL(military_rec.assistance_type_cd,dup_milit_rec.assistance_type_cd),
570: x_assistance_status_cd => NVL(military_rec.assistance_status_cd,dup_milit_rec.assistance_status_cd),
571: x_mode=>'R');
572:
573: UPDATE igs_ad_military_int_all
574: SET match_ind =cst_mi_val_18,
575: status = cst_stat_val_1
576: WHERE interface_military_id = military_rec.interface_military_id;
577:

Line 580: UPDATE igs_ad_military_int_all

576: WHERE interface_military_id = military_rec.interface_military_id;
577:
578: EXCEPTION
579: WHEN OTHERS THEN
580: UPDATE igs_ad_military_int_all
581: SET ERROR_CODE = 'E014',
582: status = '3'
583: WHERE interface_military_id = military_rec.interface_military_id;
584:

Line 614: UPDATE igs_ad_military_int_all

610: ELSE
611: crt_pr_mil(MILITARY_REC,
612: l_error_code,
613: l_status );
614: UPDATE igs_ad_military_int_all
615: SET status = l_status,
616: ERROR_CODE = l_error_code
617: WHERE interface_military_id= military_rec.interface_military_id;
618: END IF;