APPS.IGS_SV_BATCH_PROCESS_PKG dependencies on IGS_PE_DEPD_ACTIVE

Line 4571: FROM igs_pe_depd_active dep,

4567: reason_code,
4568: dep.REMARKS comments,
4569: rel.COMMENTS rel_remarks,
4570: decode (RELATIONSHIP_CODE,'PARENT_OF','02','SPOUSE_OF','01','XX' ) relationship
4571: FROM igs_pe_depd_active dep,
4572: HZ_RELATIONSHIPS rel
4573: WHERE subject_id = p_person_rec.person_id
4574: AND rel.relationship_id = dep.relationship_id
4575: AND (p_person_rec.record_status = 'C' OR dep.action_code ='A') --In the new mode report only about active dependents

Line 4580: FROM igs_pe_depd_active dep1

4576: AND RELATIONSHIP_CODE IN ('PARENT_OF','SPOUSE_OF' )
4577: AND (dep.relationship_id, dep.last_update_date) IN
4578: ( SELECT dep1.relationship_id,
4579: MAX(dep1.last_update_date)
4580: FROM igs_pe_depd_active dep1
4581: WHERE dep1.relationship_id = rel.relationship_id
4582: GROUP BY dep1.relationship_id)
4583: AND rel.status = 'A';
4584:

Line 4596: AND EXISTS (SELECT 1 FROM igs_pe_depd_active pdep

4592: FROM igs_pe_hz_rel_v hz
4593: WHERE hz.subject_id = p_person_rec.person_id AND
4594: hz.relationship_code= 'SPOUSE_OF' and
4595: sysdate BETWEEN hz.start_date AND NVL(hz.end_date, sysdate+1)
4596: AND EXISTS (SELECT 1 FROM igs_pe_depd_active pdep
4597: WHERE pdep.relationship_id = hz.relationship_id AND
4598: pdep.action_code <> 'T' AND
4599: pdep.last_update_date = (SELECT MAX(last_update_date)
4600: FROM igs_pe_depd_active

Line 4600: FROM igs_pe_depd_active

4596: AND EXISTS (SELECT 1 FROM igs_pe_depd_active pdep
4597: WHERE pdep.relationship_id = hz.relationship_id AND
4598: pdep.action_code <> 'T' AND
4599: pdep.last_update_date = (SELECT MAX(last_update_date)
4600: FROM igs_pe_depd_active
4601: WHERE relationship_id =hz.relationship_id)
4602: );
4603:
4604: CURSOR c_get_sevis_user_id(c_dep_id NUMBER)

Line 10585: FROM igs_pe_hz_rel_v hz, igs_pe_depd_active pdep

10581: l_rel_type_count NUMBER := 0;
10582:
10583: /* CURSOR c_spouse_of_rel IS
10584: SELECT COUNT(1)
10585: FROM igs_pe_hz_rel_v hz, igs_pe_depd_active pdep
10586: WHERE hz.subject_id = p_person_rec.person_id AND
10587: hz.relationship_code= 'SPOUSE_OF' and
10588: pdep.relationship_id = hz.relationship_id
10589: and pdep.action_code <> 'T'

Line 10591: FROM igs_pe_depd_active

10587: hz.relationship_code= 'SPOUSE_OF' and
10588: pdep.relationship_id = hz.relationship_id
10589: and pdep.action_code <> 'T'
10590: and pdep.last_update_date = (SELECT MAX(last_update_date)
10591: FROM igs_pe_depd_active
10592: WHERE relationship_id =hz.relationship_id)
10593: AND sysdate BETWEEN hz.start_date AND NVL(hz.end_date, sysdate+1);
10594:
10595: */