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_GE_DATE

Line 387: AND NVL(TRUNC(pe.end_date),igs_ge_date.igsdate('9999/01/01'))=NVL(TRUNC(mi.end_date),igs_ge_date.igsdate('9999/01/01'))

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)
386: AND TRUNC(pe.start_date) = TRUNC(mi.start_date)
387: AND NVL(TRUNC(pe.end_date),igs_ge_date.igsdate('9999/01/01'))=NVL(TRUNC(mi.end_date),igs_ge_date.igsdate('9999/01/01'))
388: AND NVL(UPPER(pe.assistance_type_cd),'*!*')= NVL(UPPER(mi.assistance_type_cd),'*!*')
389: AND NVL(UPPER(pe.assistance_status_cd),'*!*') = NVL(UPPER(mi.assistance_status_cd),'*!*')
390: AND NVL(UPPER(pe.separation_type_cd),'*!*') = NVL(UPPER(mi.separation_type_cd),'*!*')
391: AND NVL(pe.attribute1,'*!*') = NVL(mi.attribute1,'*!*')

Line 753: IF p_health_insur_rec.start_date <= NVL(p_health_insur_rec.end_date,IGS_GE_DATE.IGSDATE('4712/12/31')) THEN

749: END IF;
750: CLOSE birth_dt_cur;
751:
752: --Start date and End Date validation
753: IF p_health_insur_rec.start_date <= NVL(p_health_insur_rec.end_date,IGS_GE_DATE.IGSDATE('4712/12/31')) THEN
754: p_error_code := NULL;
755: ELSE
756: p_error_code := 'E158'; -- Start Date and End Date Validation Failed
757: RAISE no_data_found;

Line 1015: AND NVL(TRUNC(pe.end_date),igs_ge_date.igsdate('9999/01/01')) = NVL(TRUNC(mi.end_date),igs_ge_date.igsdate('9999/01/01'))

1011: AND ii.person_id = pe.person_id
1012: AND pe.immunization_code = UPPER(mi.immunization_code)
1013: AND pe.status_code = UPPER(mi.status_code)
1014: AND TRUNC(pe.start_date) = TRUNC(mi.start_date)
1015: AND NVL(TRUNC(pe.end_date),igs_ge_date.igsdate('9999/01/01')) = NVL(TRUNC(mi.end_date),igs_ge_date.igsdate('9999/01/01'))
1016: );
1017: END IF;
1018:
1019: -- If rule is R records still exist, they are duplicates and have discrepancy .. update status=3,match_ind=20

Line 1607: AND NVL(TRUNC(pe.end_date),igs_ge_date.igsdate('9999/01/01')) = NVL(TRUNC(mi.end_date),igs_ge_date.igsdate('9999/01/01'))

1603: AND pe.insurance_cd = UPPER(mi.insurance_cd)
1604: AND TRUNC(pe.start_date) = TRUNC(mi.start_date)
1605: AND UPPER(pe.insurance_provider) = UPPER(mi.insurance_provider)
1606: AND UPPER(pe.policy_number) = UPPER(mi.policy_number)
1607: AND NVL(TRUNC(pe.end_date),igs_ge_date.igsdate('9999/01/01')) = NVL(TRUNC(mi.end_date),igs_ge_date.igsdate('9999/01/01'))
1608: AND NVL(UPPER(pe.attribute1),'*!*') = NVL(UPPER(mi.attribute1),'*!*')
1609: AND NVL(UPPER(pe.attribute2),'*!*') = NVL(UPPER(mi.attribute2),'*!*')
1610: AND NVL(UPPER(pe.attribute3),'*!*') = NVL(UPPER(mi.attribute3),'*!*')
1611: AND NVL(UPPER(pe.attribute4),'*!*') = NVL(UPPER(mi.attribute4),'*!*')

Line 2289: AND NVL(TRUNC(pe.end_dt),igs_ge_date.igsdate('9999/01/01'))=NVL(TRUNC(mi.end_dt),igs_ge_date.igsdate('9999/01/01'))

2285: AND ii.person_id = pe.pe_person_id
2286: AND UPPER(pe.api_person_id) = UPPER(mi.alternate_id)
2287: AND UPPER(pe.person_id_type) = UPPER(mi.person_id_type)
2288: AND TRUNC(pe.start_dt) = TRUNC(mi.start_dt)
2289: AND NVL(TRUNC(pe.end_dt),igs_ge_date.igsdate('9999/01/01'))=NVL(TRUNC(mi.end_dt),igs_ge_date.igsdate('9999/01/01'))
2290: AND NVL(UPPER(pe.attribute_category), '*') = NVL(UPPER(mi.attribute_category), '*')
2291: AND NVL(UPPER(pe.region_cd), '*') = NVL(UPPER(mi.region_cd), '*')
2292: AND NVL(UPPER(pe.attribute1),'*!*') = NVL(UPPER(mi.attribute1),'*!*')
2293: AND NVL(UPPER(pe.attribute2),'*!*') = NVL(UPPER(mi.attribute2),'*!*')

Line 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'))

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'))
2678: OR
2679: TRUNC(PCZ_REC.date_recognized) BETWEEN TRUNC(date_recognized) AND NVL(TRUNC(end_date),IGS_GE_DATE.igsdate('9999/01/01'))
2680: OR
2681: ( TRUNC(PCZ_REC.date_recognized) < TRUNC(date_recognized) AND

Line 2679: TRUNC(PCZ_REC.date_recognized) BETWEEN TRUNC(date_recognized) AND NVL(TRUNC(end_date),IGS_GE_DATE.igsdate('9999/01/01'))

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'))
2678: OR
2679: TRUNC(PCZ_REC.date_recognized) BETWEEN TRUNC(date_recognized) AND NVL(TRUNC(end_date),IGS_GE_DATE.igsdate('9999/01/01'))
2680: OR
2681: ( TRUNC(PCZ_REC.date_recognized) < TRUNC(date_recognized) AND
2682: NVL(TRUNC(end_date),IGS_GE_DATE.igsdate('9999/01/01'))< NVL(TRUNC(PCZ_REC.end_date),IGS_GE_DATE.igsdate('9999/01/01')) ) );
2683:

Line 2682: NVL(TRUNC(end_date),IGS_GE_DATE.igsdate('9999/01/01'))< NVL(TRUNC(PCZ_REC.end_date),IGS_GE_DATE.igsdate('9999/01/01')) ) );

2678: OR
2679: TRUNC(PCZ_REC.date_recognized) BETWEEN TRUNC(date_recognized) AND NVL(TRUNC(end_date),IGS_GE_DATE.igsdate('9999/01/01'))
2680: OR
2681: ( TRUNC(PCZ_REC.date_recognized) < TRUNC(date_recognized) AND
2682: NVL(TRUNC(end_date),IGS_GE_DATE.igsdate('9999/01/01'))< NVL(TRUNC(PCZ_REC.end_date),IGS_GE_DATE.igsdate('9999/01/01')) ) );
2683:
2684: l_birth_dt IGS_PE_PERSON.BIRTH_DT%TYPE;
2685: valid_country_rec c_valid_country%ROWTYPE;
2686: l_count NUMBER(3);

Line 2739: IF NOT (pcz_rec.date_disowned BETWEEN pcz_rec.date_recognized AND NVL(PCZ_REC.end_date,IGS_GE_DATE.igsdate('9999/01/01'))) THEN

2735: l_error := null;
2736: END IF;
2737:
2738: -- nsidana Bug 3541714 : Added the validation to check that the date disowned in greater than the date recognized.
2739: IF NOT (pcz_rec.date_disowned BETWEEN pcz_rec.date_recognized AND NVL(PCZ_REC.end_date,IGS_GE_DATE.igsdate('9999/01/01'))) THEN
2740: l_error := 'E267';
2741: RAISE no_data_found;
2742: END IF;
2743:

Line 2935: ( 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'))

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
2935: ( 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'))
2936: OR
2937: TRUNC(PCZ_REC.date_recognized) BETWEEN TRUNC(date_recognized) AND NVL(TRUNC(end_date),IGS_GE_DATE.igsdate('9999/01/01'))
2938: OR
2939: ( TRUNC(PCZ_REC.date_recognized) < TRUNC(date_recognized) AND

Line 2937: TRUNC(PCZ_REC.date_recognized) BETWEEN TRUNC(date_recognized) AND NVL(TRUNC(end_date),IGS_GE_DATE.igsdate('9999/01/01'))

2933: UPPER(country_code) = UPPER(PCZ_REC.Country_code) AND
2934: TRUNC(date_recognized) <> TRUNC(PCZ_REC.date_recognized) AND
2935: ( 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'))
2936: OR
2937: TRUNC(PCZ_REC.date_recognized) BETWEEN TRUNC(date_recognized) AND NVL(TRUNC(end_date),IGS_GE_DATE.igsdate('9999/01/01'))
2938: OR
2939: ( TRUNC(PCZ_REC.date_recognized) < TRUNC(date_recognized) AND
2940: NVL(TRUNC(end_date),IGS_GE_DATE.igsdate('9999/01/01'))< NVL(TRUNC(PCZ_REC.end_date),IGS_GE_DATE.igsdate('9999/01/01')) ) );
2941:

Line 2940: NVL(TRUNC(end_date),IGS_GE_DATE.igsdate('9999/01/01'))< NVL(TRUNC(PCZ_REC.end_date),IGS_GE_DATE.igsdate('9999/01/01')) ) );

2936: OR
2937: TRUNC(PCZ_REC.date_recognized) BETWEEN TRUNC(date_recognized) AND NVL(TRUNC(end_date),IGS_GE_DATE.igsdate('9999/01/01'))
2938: OR
2939: ( TRUNC(PCZ_REC.date_recognized) < TRUNC(date_recognized) AND
2940: NVL(TRUNC(end_date),IGS_GE_DATE.igsdate('9999/01/01'))< NVL(TRUNC(PCZ_REC.end_date),IGS_GE_DATE.igsdate('9999/01/01')) ) );
2941:
2942: l_count NUMBER(3);
2943: l_birth_dt IGS_PE_PERSON_V.BIRTH_DT%TYPE;
2944: null_hndlg_rec c_null_hndlg%ROWTYPE;

Line 3011: IF NOT (pcz_rec.date_disowned BETWEEN pcz_rec.date_recognized AND NVL(PCZ_REC.end_date,IGS_GE_DATE.igsdate('9999/01/01'))) THEN

3007: END IF;
3008: END IF;
3009: CLOSE birth_dt_cur;
3010:
3011: IF NOT (pcz_rec.date_disowned BETWEEN pcz_rec.date_recognized AND NVL(PCZ_REC.end_date,IGS_GE_DATE.igsdate('9999/01/01'))) THEN
3012: l_error := 'E267';
3013: RAISE no_data_found;
3014: END IF;
3015: END IF;

Line 3124: l_default_date := igs_ge_date.igsdate('9999/01/01');

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');
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