228: CURSOR c29 (
229: p_person_id NUMBER
230: ) IS
231: SELECT 1
232: FROM ben_elig_cvrd_dpnt_f
233: WHERE dpnt_person_id = p_person_id;
234: CURSOR c30 (
235: p_person_id NUMBER
236: ) IS
971:
972: --
973: CLOSE c28;
974: --
975: -- Testing for values in BEN_ELIG_CVRD_DPNT_F
976: --
977: OPEN c29 (
978: p_person_id
979: );
987: 'BEN_94121_DT_CHILD_EXISTS'
988: );
989: fnd_message.set_token (
990: 'TABLE_NAME',
991: 'BEN_ELIG_CVRD_DPNT_F'
992: );
993: fnd_message.raise_error;
994: END IF;
995:
1145: l_proc varchar2(100):= g_package||'delete_dependent_information';
1146: --
1147: CURSOR c_ecd IS
1148: SELECT elig_cvrd_dpnt_id, dpnt_person_id
1149: FROM ben_elig_cvrd_dpnt_f
1150: WHERE dpnt_person_id = p_person_id
1151: and per_in_ler_id = nvl(p_per_in_ler_id,per_in_ler_id)
1152: FOR UPDATE OF elig_cvrd_dpnt_id,dpnt_person_id;
1153:
1200: CURSOR c4 (
1201: p_elig_cvrd_dpnt_id NUMBER
1202: ) IS
1203: SELECT elig_cvrd_dpnt_id
1204: FROM ben_elig_cvrd_dpnt_f
1205: WHERE elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id
1206: FOR UPDATE OF elig_cvrd_dpnt_id;
1207:
1208: --
1299: hr_utility.set_location(l_proc, 35);
1300: /* OPEN c4 (
1301: l_ecd.elig_cvrd_dpnt_id
1302: );
1303: <
1305: FETCH c4 INTO l_id;
1306: EXIT WHEN c4%NOTFOUND;
1307: DELETE ben_elig_cvrd_dpnt_f
1303: <
1304: LOOP
1305: FETCH c4 INTO l_id;
1306: EXIT WHEN c4%NOTFOUND;
1307: DELETE ben_elig_cvrd_dpnt_f
1308: WHERE CURRENT OF c4;
1309: END LOOP ben_elig_cvrd_dpnt_f;
1310: CLOSE c4; */
1311: hr_utility.set_location(l_proc, 40);
1305: FETCH c4 INTO l_id;
1306: EXIT WHEN c4%NOTFOUND;
1307: DELETE ben_elig_cvrd_dpnt_f
1308: WHERE CURRENT OF c4;
1309: END LOOP ben_elig_cvrd_dpnt_f;
1310: CLOSE c4; */
1311: hr_utility.set_location(l_proc, 40);
1312: OPEN c5 (
1313: l_ecd.elig_cvrd_dpnt_id
1343: DELETE ben_elig_dpnt
1344: WHERE CURRENT OF c7;
1345: END LOOP ben_elig_dpnt;
1346: CLOSE c7;
1347: DELETE ben_elig_cvrd_dpnt_f
1348: WHERE CURRENT OF c_ecd;
1349: END LOOP;
1350: hr_utility.set_location('Leaving: '||l_proc,999);
1351: END delete_dependent_information;
1491: CURSOR c7 (
1492: p_prtt_enrt_rslt_id NUMBER
1493: ) IS
1494: SELECT elig_cvrd_dpnt_id
1495: FROM ben_elig_cvrd_dpnt_f
1496: WHERE prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
1497: FOR UPDATE OF elig_cvrd_dpnt_id;
1498:
1499: --
1620: FOR UPDATE OF elctbl_chc_ctfn_id;
1621:
1622: --
1623: --** C|c22|p_per_in_ler_id in number
1624: --** CN|c22|Fetch the elig_cvrd_dpnt_id from ben_elig_cvrd_dpnt associated with a per_in_ler_id and prtt_enrt_rslt_id.
1625: CURSOR c22 (
1626: p_per_in_ler_id IN NUMBER
1627: ) IS
1628: SELECT DISTINCT elig_cvrd_dpnt_id
1625: CURSOR c22 (
1626: p_per_in_ler_id IN NUMBER
1627: ) IS
1628: SELECT DISTINCT elig_cvrd_dpnt_id
1629: FROM ben_prtt_enrt_rslt_f b1, ben_elig_cvrd_dpnt b2
1630: WHERE b1.per_in_ler_id = p_per_in_ler_id
1631: AND b2.prtt_enrt_rslt_id = b1.prtt_enrt_rslt_id;
1632:
1633: --
1758: CLOSE c6;
1759: OPEN c7 (
1760: r4.prtt_enrt_rslt_id
1761: );
1762: <
1764: FETCH c7 INTO l_id;
1765: EXIT WHEN c7%NOTFOUND;
1766: DELETE FROM ben_elig_cvrd_dpnt_f
1762: <
1763: LOOP
1764: FETCH c7 INTO l_id;
1765: EXIT WHEN c7%NOTFOUND;
1766: DELETE FROM ben_elig_cvrd_dpnt_f
1767: WHERE CURRENT OF c7;
1768: END LOOP ben_elig_cvrd_dpnt_f;
1769: CLOSE c7;
1770: OPEN c8 (
1764: FETCH c7 INTO l_id;
1765: EXIT WHEN c7%NOTFOUND;
1766: DELETE FROM ben_elig_cvrd_dpnt_f
1767: WHERE CURRENT OF c7;
1768: END LOOP ben_elig_cvrd_dpnt_f;
1769: CLOSE c7;
1770: OPEN c8 (
1771: r4.prtt_enrt_rslt_id
1772: );
2058: CURSOR c4 (
2059: p_prtt_enrt_rslt_id NUMBER
2060: ) IS
2061: SELECT elig_cvrd_dpnt_id
2062: FROM ben_elig_cvrd_dpnt_f
2063: WHERE prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
2064: FOR UPDATE OF elig_cvrd_dpnt_id;
2065:
2066: --
2127: CLOSE c3;
2128: OPEN c4 (
2129: r1.prtt_enrt_rslt_id
2130: );
2131: <
2133: FETCH c4 INTO l_id;
2134: EXIT WHEN c4%NOTFOUND;
2135: DELETE FROM ben_elig_cvrd_dpnt_f
2131: <
2132: LOOP
2133: FETCH c4 INTO l_id;
2134: EXIT WHEN c4%NOTFOUND;
2135: DELETE FROM ben_elig_cvrd_dpnt_f
2136: WHERE CURRENT OF c4;
2137: END LOOP ben_elig_cvrd_dpnt_f;
2138: CLOSE c4;
2139: OPEN c5 (
2133: FETCH c4 INTO l_id;
2134: EXIT WHEN c4%NOTFOUND;
2135: DELETE FROM ben_elig_cvrd_dpnt_f
2136: WHERE CURRENT OF c4;
2137: END LOOP ben_elig_cvrd_dpnt_f;
2138: CLOSE c4;
2139: OPEN c5 (
2140: r1.prtt_enrt_rslt_id
2141: );
3190: CURSOR c_ecd( p_person_id number ,
3191: p_effective_date date
3192: ) is
3193: SELECT 'Y'
3194: FROM ben_elig_cvrd_dpnt_f ecd,
3195: ben_per_in_ler pil,
3196: ben_ler_f ler,
3197: ben_prtt_enrt_rslt_f pen
3198: WHERE
3214: CURSOR c_ecdpn( p_person_id number ,
3215: p_effective_date date
3216: ) is
3217: SELECT 'Y'
3218: FROM ben_elig_cvrd_dpnt_f ecd,
3219: ben_per_in_ler pil,
3220: ben_ler_f ler,
3221: ben_prtt_enrt_rslt_f pen
3222: WHERE