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 IGS_AD_INTERFACE_ALL

Line 85: CURSOR milt_cur(cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE) IS

81: || check for separation type.
82: || (reverse chronological order - newest change first)
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

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 107: l_interface_run_id igs_ad_interface_all.interface_run_id%TYPE;

103: l_label VARCHAR2(4000);
104: l_debug_str VARCHAR2(4000);
105: l_enable_log VARCHAR2(1);
106: l_request_id NUMBER(10);
107: l_interface_run_id igs_ad_interface_all.interface_run_id%TYPE;
108:
109: PROCEDURE crt_pr_mil(
110: MILITARY_REC milt_cur%ROWTYPE ,
111: p_error_code OUT NOCOPY VARCHAR2,

Line 344: FROM igs_pe_mil_services_all pe, igs_ad_interface_all ii

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
343: AND EXISTS ( SELECT '1'
344: FROM igs_pe_mil_services_all pe, igs_ad_interface_all ii
345: WHERE ii.interface_run_id = l_interface_run_id
346: AND ii.interface_id = mi.interface_id
347: AND ii.person_id = pe.person_id
348: AND pe.military_type_cd = UPPER(mi.military_type_cd)

Line 381: FROM igs_pe_mil_services_all pe, igs_ad_interface_all ii

377: WHERE mi.interface_run_id = l_interface_run_id
378: AND mi.match_ind IS NULL
379: AND mi.status = cst_stat_val_2
380: AND EXISTS ( SELECT '1'
381: FROM igs_pe_mil_services_all pe, igs_ad_interface_all ii
382: WHERE ii.interface_run_id = l_interface_run_id
383: AND ii.interface_id = mi.interface_id
384: AND ii.person_id = pe.person_id
385: AND pe.military_type_cd = UPPER(mi.military_type_cd)

Line 419: FROM igs_pe_mil_services_all pe, igs_ad_interface_all ii

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
420: WHERE mi.interface_run_id = l_interface_run_id
421: AND ii.interface_id = mi.interface_id
422: AND ii.person_id = pe.person_id
423: AND pe.military_type_cd = UPPER(mi.military_type_cd)

Line 429: FROM igs_pe_mil_services_all pe, igs_ad_interface_all ii

425: WHERE mi.interface_run_id = l_interface_run_id
426: AND mi.match_ind IS NULL
427: AND mi.status = cst_stat_val_2
428: AND EXISTS (SELECT '1'
429: FROM igs_pe_mil_services_all pe, igs_ad_interface_all ii
430: WHERE ii.interface_run_id = l_interface_run_id
431: AND ii.interface_id = mi.interface_id
432: AND ii.person_id = pe.person_id
433: AND pe.military_type_cd = UPPER(mi.military_type_cd)

Line 652: CURSOR c_immu_dtls_cur(cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE) IS

648: || New procedure created for processing the immunization details of the person
649: ||
650: || (reverse chronological order - newest change first)
651: */
652: CURSOR c_immu_dtls_cur(cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE) IS
653: SELECT ai.*, i.person_id
654: FROM igs_pe_immu_dtl_int ai, igs_ad_interface_all i
655: WHERE ai.interface_run_id = cp_interface_run_id
656: AND ai.interface_id = i.interface_id

Line 654: FROM igs_pe_immu_dtl_int ai, igs_ad_interface_all i

650: || (reverse chronological order - newest change first)
651: */
652: CURSOR c_immu_dtls_cur(cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE) IS
653: SELECT ai.*, i.person_id
654: FROM igs_pe_immu_dtl_int ai, igs_ad_interface_all i
655: WHERE ai.interface_run_id = cp_interface_run_id
656: AND ai.interface_id = i.interface_id
657: AND ai.interface_run_id = cp_interface_run_id
658: AND ai.status = '2';

Line 685: l_interface_run_id igs_ad_interface_all.interface_run_id%TYPE;

681: l_label VARCHAR2(4000);
682: l_debug_str VARCHAR2(4000);
683: l_enable_log VARCHAR2(1);
684: l_request_id NUMBER(10);
685: l_interface_run_id igs_ad_interface_all.interface_run_id%TYPE;
686:
687: PROCEDURE validate_record_health(p_health_insur_rec IN c_immu_dtls_cur%ROWTYPE,
688: p_error_code OUT NOCOPY VARCHAR2)
689: AS

Line 971: FROM igs_pe_immu_dtls pe, igs_ad_interface_all ii

967: match_ind = cst_mi_val_19
968: WHERE mi.interface_run_id = l_interface_run_id
969: AND mi.status = cst_stat_val_2
970: AND EXISTS ( SELECT '1'
971: FROM igs_pe_immu_dtls pe, igs_ad_interface_all ii
972: WHERE ii.interface_run_id = l_interface_run_id
973: AND ii.interface_id = mi.interface_id
974: AND ii.person_id = pe.person_id
975: AND pe.immunization_code = UPPER(mi.immunization_code)

Line 1008: FROM igs_pe_immu_dtls pe, igs_ad_interface_all ii

1004: WHERE mi.interface_run_id = l_interface_run_id
1005: AND mi.match_ind IS NULL
1006: AND mi.status = cst_stat_val_2
1007: AND EXISTS ( SELECT '1'
1008: FROM igs_pe_immu_dtls pe, igs_ad_interface_all ii
1009: WHERE ii.interface_run_id = l_interface_run_id
1010: AND ii.interface_id = mi.interface_id
1011: AND ii.person_id = pe.person_id
1012: AND pe.immunization_code = UPPER(mi.immunization_code)

Line 1025: FROM igs_pe_immu_dtls pe, igs_ad_interface_all ii

1021: UPDATE igs_pe_immu_dtl_int mi
1022: SET status = cst_stat_val_3,
1023: match_ind = cst_mi_val_20,
1024: dup_immu_details_id = (SELECT pe.immu_details_id
1025: FROM igs_pe_immu_dtls pe, igs_ad_interface_all ii
1026: WHERE mi.interface_run_id = l_interface_run_id
1027: AND ii.interface_id = mi.interface_id
1028: AND ii.person_id = pe.person_id
1029: AND pe.immunization_code = UPPER(mi.immunization_code)

Line 1036: FROM igs_pe_immu_dtls pe, igs_ad_interface_all ii

1032: WHERE mi.interface_run_id = l_interface_run_id
1033: AND mi.match_ind IS NULL
1034: AND mi.status = cst_stat_val_2
1035: AND EXISTS (SELECT '1'
1036: FROM igs_pe_immu_dtls pe, igs_ad_interface_all ii
1037: WHERE ii.interface_run_id = l_interface_run_id
1038: AND ii.interface_id = mi.interface_id
1039: AND ii.person_id = pe.person_id
1040: AND pe.immunization_code = UPPER(mi.immunization_code)

Line 1326: CURSOR hlth_ins(cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE) IS

1322: || primary key id into the DUP_HLTH_INS_ID field in interfce table
1323: ||
1324: || (reverse chronological order - newest change first)
1325: */
1326: CURSOR hlth_ins(cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE) IS
1327: SELECT hii.*, i.person_id
1328: FROM igs_ad_hlth_ins_int_all hii, igs_ad_interface_all i
1329: WHERE hii.interface_run_id = cp_interface_run_id
1330: AND i.interface_id = hii.interface_id

Line 1328: FROM igs_ad_hlth_ins_int_all hii, igs_ad_interface_all i

1324: || (reverse chronological order - newest change first)
1325: */
1326: CURSOR hlth_ins(cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE) IS
1327: SELECT hii.*, i.person_id
1328: FROM igs_ad_hlth_ins_int_all hii, igs_ad_interface_all i
1329: WHERE hii.interface_run_id = cp_interface_run_id
1330: AND i.interface_id = hii.interface_id
1331: AND i.interface_run_id = cp_interface_run_id
1332: AND hii.status = '2';

Line 1348: l_interface_run_id igs_ad_interface_all.interface_run_id%TYPE;

1344: l_label VARCHAR2(4000);
1345: l_debug_str VARCHAR2(4000);
1346: l_enable_log VARCHAR2(1);
1347: l_request_id NUMBER(10);
1348: l_interface_run_id igs_ad_interface_all.interface_run_id%TYPE;
1349: PROCEDURE Crt_Pe_hlth_ins(hlth_ins_rec IN HLTH_INS%ROWTYPE,
1350: p_error_code OUT NOCOPY VARCHAR2,
1351: p_status OUT NOCOPY VARCHAR2) AS
1352:

Line 1562: FROM igs_pe_hlth_ins_all pe, igs_ad_interface_all ii

1558: match_ind = cst_mi_val_19
1559: WHERE mi.interface_run_id = l_interface_run_id
1560: AND mi.status = cst_stat_val_2
1561: AND EXISTS ( SELECT '1'
1562: FROM igs_pe_hlth_ins_all pe, igs_ad_interface_all ii
1563: WHERE ii.interface_run_id = l_interface_run_id
1564: AND ii.interface_id = mi.interface_id
1565: AND ii.person_id = pe.person_id
1566: AND pe.insurance_cd = UPPER(mi.insurance_cd)

Line 1599: FROM igs_pe_hlth_ins_all pe, igs_ad_interface_all ii

1595: WHERE mi.interface_run_id = l_interface_run_id
1596: AND mi.match_ind IS NULL
1597: AND mi.status = cst_stat_val_2
1598: AND EXISTS ( SELECT '1'
1599: FROM igs_pe_hlth_ins_all pe, igs_ad_interface_all ii
1600: WHERE ii.interface_run_id = l_interface_run_id
1601: AND ii.interface_id = mi.interface_id
1602: AND ii.person_id = pe.person_id
1603: AND pe.insurance_cd = UPPER(mi.insurance_cd)

Line 1637: FROM igs_pe_hlth_ins_all pe, igs_ad_interface_all ii

1633: UPDATE igs_ad_hlth_ins_int_all mi
1634: SET status = cst_stat_val_3,
1635: match_ind = cst_mi_val_20,
1636: dup_hlth_ins_id = (SELECT health_ins_id
1637: FROM igs_pe_hlth_ins_all pe, igs_ad_interface_all ii
1638: WHERE ii.interface_run_id = l_interface_run_id
1639: AND ii.interface_id = mi.interface_id
1640: AND ii.person_id = pe.person_id
1641: AND pe.insurance_cd = UPPER(mi.insurance_cd)

Line 1647: FROM igs_pe_hlth_ins_all pe, igs_ad_interface_all ii

1643: WHERE mi.interface_run_id = l_interface_run_id
1644: AND mi.match_ind IS NULL
1645: AND mi.status = cst_stat_val_2
1646: AND EXISTS (SELECT '1'
1647: FROM igs_pe_hlth_ins_all pe, igs_ad_interface_all ii
1648: WHERE ii.interface_run_id = l_interface_run_id
1649: AND ii.interface_id = mi.interface_id
1650: AND ii.person_id = pe.person_id
1651: AND pe.insurance_cd = UPPER(mi.insurance_cd)

Line 1898: CURSOR API(cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE) IS

1894:
1895: -- Logic for IGS_AD_IMPORT_PERSON_ID_TYPES
1896: -- Create the cursor using the following select statement.
1897:
1898: CURSOR API(cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE) IS
1899: SELECT mi.*, i.person_id
1900: FROM igs_ad_api_int_all mi, igs_ad_interface_all I
1901: WHERE mi.interface_run_id = cp_interface_run_id
1902: AND mi.interface_id = i.interface_id

Line 1900: FROM igs_ad_api_int_all mi, igs_ad_interface_all I

1896: -- Create the cursor using the following select statement.
1897:
1898: CURSOR API(cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE) IS
1899: SELECT mi.*, i.person_id
1900: FROM igs_ad_api_int_all mi, igs_ad_interface_all I
1901: WHERE mi.interface_run_id = cp_interface_run_id
1902: AND mi.interface_id = i.interface_id
1903: AND i.interface_run_id = cp_interface_run_id
1904: AND mi.status = '2'

Line 1935: l_interface_run_id igs_ad_interface_all.interface_run_id%TYPE;

1931: l_enable_log VARCHAR2(1);
1932: l_request_id NUMBER(10);
1933: l_message_name VARCHAR2(30);
1934: l_app VARCHAR2(50);
1935: l_interface_run_id igs_ad_interface_all.interface_run_id%TYPE;
1936: l_ucas_action VARCHAR2(1);
1937: l_ucas_error_code VARCHAR2(10);
1938: l_call_ucas_user_hook BOOLEAN;
1939: l_source_type_id1 NUMBER;

Line 2244: FROM igs_pe_alt_pers_id pe, igs_ad_interface_all ii

2240: match_ind = cst_mi_val_19
2241: WHERE mi.interface_run_id = l_interface_run_id
2242: AND mi.status = cst_stat_val_2
2243: AND EXISTS ( SELECT '1'
2244: FROM igs_pe_alt_pers_id pe, igs_ad_interface_all ii
2245: WHERE ii.interface_run_id = l_interface_run_id
2246: AND ii.interface_id = mi.interface_id
2247: AND ii.person_id = pe.pe_person_id
2248: AND UPPER(pe.api_person_id) = UPPER(mi.alternate_id)

Line 2282: FROM igs_pe_alt_pers_id pe, igs_ad_interface_all ii

2278: WHERE mi.interface_run_id = l_interface_run_id
2279: AND mi.match_ind IS NULL
2280: AND mi.status = cst_stat_val_2
2281: AND EXISTS ( SELECT '1'
2282: FROM igs_pe_alt_pers_id pe, igs_ad_interface_all ii
2283: WHERE ii.interface_run_id = l_interface_run_id
2284: AND ii.interface_id = mi.interface_id
2285: AND ii.person_id = pe.pe_person_id
2286: AND UPPER(pe.api_person_id) = UPPER(mi.alternate_id)

Line 2323: FROM igs_pe_alt_pers_id pe, igs_ad_interface_all ii

2319: WHERE mi.interface_run_id = l_interface_run_id
2320: AND mi.match_ind IS NULL
2321: AND mi.status = cst_stat_val_2
2322: AND EXISTS (SELECT '1'
2323: FROM igs_pe_alt_pers_id pe, igs_ad_interface_all ii
2324: WHERE mi.interface_run_id = l_interface_run_id
2325: AND ii.interface_id = mi.interface_id
2326: AND ii.person_id = pe.pe_person_id
2327: AND UPPER(pe.api_person_id) = UPPER(mi.alternate_id)

Line 2622: CURSOR c_pcz(cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE) IS

2618: || fnd or igs lookups,Date validations added
2619: */
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

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 2643: l_interface_run_id igs_ad_interface_all.interface_run_id%TYPE;

2639: l_label VARCHAR2(4000);
2640: l_debug_str VARCHAR2(4000);
2641: l_enable_log VARCHAR2(1);
2642: l_request_id NUMBER(10);
2643: l_interface_run_id igs_ad_interface_all.interface_run_id%TYPE;
2644: l_default_date DATE;
2645:
2646: -- Local Procedure to create new records .
2647: PROCEDURE crt_pe_citizenship( pcz_rec IN c_pcz%ROWTYPE ,

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 3295: CURSOR c_pfs(cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE) IS

3291: */
3292: AS
3293:
3294: --cursor to select records from interface records
3295: CURSOR c_pfs(cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE) IS
3296: SELECT ai.*,
3297: i.person_id
3298: FROM igs_pe_fund_src_int ai, igs_ad_interface_all i
3299: WHERE ai.interface_run_id = cp_interface_run_id

Line 3298: FROM igs_pe_fund_src_int ai, igs_ad_interface_all i

3294: --cursor to select records from interface records
3295: CURSOR c_pfs(cp_interface_run_id igs_ad_interface_all.interface_run_id%TYPE) IS
3296: SELECT ai.*,
3297: i.person_id
3298: FROM igs_pe_fund_src_int ai, igs_ad_interface_all i
3299: WHERE ai.interface_run_id = cp_interface_run_id
3300: AND i.interface_id = ai.interface_id
3301: AND i.interface_run_id = cp_interface_run_id
3302: AND ai.status = '2';

Line 3320: l_interface_run_id igs_ad_interface_all.interface_run_id%TYPE;

3316: l_label VARCHAR2(4000);
3317: l_debug_str VARCHAR2(4000);
3318: l_enable_log VARCHAR2(1);
3319: l_request_id NUMBER(10);
3320: l_interface_run_id igs_ad_interface_all.interface_run_id%TYPE;
3321: -- Local Procedure to create new records .
3322:
3323: PROCEDURE crt_pe_fund_source( pfs_rec IN c_pfs%ROWTYPE ,
3324: error_code OUT NOCOPY VARCHAR2,