161: pap.person_id person_id
162: FROM
163: per_all_people_f pap
164: ,per_all_assignments_f paa
165: ,PER_ASSIGNMENT_EXTRA_INFO pae_iza
166: ,PER_ASSIGNMENT_EXTRA_INFO pae_sii
167: WHERE pap.business_group_id = v_business_group_id
168: and pap.person_id = paa.person_id
169: and paa.primary_flag='Y'
162: FROM
163: per_all_people_f pap
164: ,per_all_assignments_f paa
165: ,PER_ASSIGNMENT_EXTRA_INFO pae_iza
166: ,PER_ASSIGNMENT_EXTRA_INFO pae_sii
167: WHERE pap.business_group_id = v_business_group_id
168: and pap.person_id = paa.person_id
169: and paa.primary_flag='Y'
170: and v_period_end_date between pap.effective_start_date and pap.effective_end_date
1429:
1430:
1431: cursor csr_iza_insured(v_assignment_id number,v_period_start_date date,v_period_end_date date) IS
1432: select pae_iza.AEI_INFORMATION3
1433: from PER_ASSIGNMENT_EXTRA_INFO pae_iza
1434: ,PER_ASSIGNMENT_EXTRA_INFO pae_sii
1435: where pae_iza.AEI_INFORMATION_CATEGORY = 'NL_IZA_INFO'
1436: and pae_iza.assignment_id = v_assignment_id
1437: and v_period_end_date >= fnd_date.canonical_to_date(pae_iza.AEI_INFORMATION1)
1430:
1431: cursor csr_iza_insured(v_assignment_id number,v_period_start_date date,v_period_end_date date) IS
1432: select pae_iza.AEI_INFORMATION3
1433: from PER_ASSIGNMENT_EXTRA_INFO pae_iza
1434: ,PER_ASSIGNMENT_EXTRA_INFO pae_sii
1435: where pae_iza.AEI_INFORMATION_CATEGORY = 'NL_IZA_INFO'
1436: and pae_iza.assignment_id = v_assignment_id
1437: and v_period_end_date >= fnd_date.canonical_to_date(pae_iza.AEI_INFORMATION1)
1438: and v_period_start_date <= NVL(fnd_date.canonical_to_date(pae_iza.AEI_INFORMATION2),v_period_start_date)