DBA Data[Home] [Help]

APPS.IGS_SV_NI_BATCH_PROCESS_PKG dependencies on HZ_RELATIONSHIPS

Line 3649: HZ_RELATIONSHIPS rel

3645: dep.REMARKS comments,
3646: rel.COMMENTS rel_remarks,
3647: decode (RELATIONSHIP_CODE,'PARENT_OF','02','SPOUSE_OF','01','XX' ) relationship
3648: FROM igs_pe_depd_active dep,
3649: HZ_RELATIONSHIPS rel
3650: WHERE subject_id = p_person_rec.person_id
3651: AND rel.relationship_id = dep.relationship_id
3652: AND (p_person_rec.record_status = 'C' OR dep.action_code ='A') --In the new mode report only about active dependents
3653: AND RELATIONSHIP_CODE IN ('PARENT_OF','SPOUSE_OF' )

Line 10406: fr.person_id IN (SELECT rel.object_id FROM hz_relationships rel, igs_pe_alt_pers_id alt

10402: AND pr.record_status <> 'E' AND prs.person_id = fr.person_id)
10403: ) AND fr.sevis_school_identifier = p_org_party_id AND
10404: (
10405: p_dso_id IS NULL OR
10406: fr.person_id IN (SELECT rel.object_id FROM hz_relationships rel, igs_pe_alt_pers_id alt
10407: WHERE rel.subject_id = alt.pe_person_id and
10408: alt.api_person_id = p_dso_id and
10409: alt.person_id_type IN (SELECT person_id_type FROM igs_pe_person_id_typ WHERE s_person_id_type = 'SEVIS_ID')
10410: AND rel.DIRECTIONAL_FLAG = 'F' AND sysdate between rel.start_date AND nvl(end_date, sysdate)

Line 10433: FROM hz_relationships rel, igs_pe_alt_pers_id alt

10429: ) )
10430: AND fr.sevis_school_identifier = p_org_party_id AND
10431: ( p_dso_id IS NULL OR
10432: fr.person_id IN (SELECT rel.object_id
10433: FROM hz_relationships rel, igs_pe_alt_pers_id alt
10434: WHERE rel.subject_id = alt.pe_person_id and
10435: alt.api_person_id = p_dso_id and
10436: alt.person_id_type IN (SELECT person_id_type FROM igs_pe_person_id_typ
10437: WHERE s_person_id_type = 'SEVIS_ID') AND

Line 10495: FROM hz_relationships rel, igs_pe_alt_pers_id alt

10491:
10492: CURSOR c_get_dso_id(c_person_id igs_pe_ev_form.person_id%TYPE)
10493: IS
10494: SELECT alt.api_person_id
10495: FROM hz_relationships rel, igs_pe_alt_pers_id alt
10496: WHERE rel.subject_id = c_person_id and
10497: rel.object_id = alt.pe_person_id AND
10498: sysdate between rel.start_date AND nvl(end_date, sysdate) AND
10499: rel.RELATIONSHIP_CODE = 'NEXT_OF_KIN_FOR' AND