[Home] [Help]
354: tab3.ELIG_HLTH_CVG_FLAG,
355: tab3.ELIG_ANTHR_PL_FLAG
356: from ben_prtn_elig_f tab1,
357: ben_prtn_elig_prfl_f tab2,
358: ben_eligy_prfl_f tab3
359: where tab1.business_group_id = c_business_group_id
360: and tab1.prtn_elig_id = tab2.prtn_elig_id
361: and c_effective_date
362: between tab1.effective_start_date
742: tab3.ELIG_HLTH_CVG_FLAG,
743: tab3.ELIG_ANTHR_PL_FLAG
744: from ben_prtn_elig_f tab1,
745: ben_prtn_elig_prfl_f tab2,
746: ben_eligy_prfl_f tab3
747: where tab1.pgm_id = c_pgm_id
748: and tab1.prtn_elig_id = tab2.prtn_elig_id
749: and c_effective_date
750: between tab1.effective_start_date and tab1.effective_end_date
828: tab3.ELIG_HLTH_CVG_FLAG,
829: tab3.ELIG_ANTHR_PL_FLAG
830: from ben_prtn_elig_f tab1,
831: ben_prtn_elig_prfl_f tab2,
832: ben_eligy_prfl_f tab3
833: where tab1.ptip_id = c_ptip_id
834: and tab1.prtn_elig_id = tab2.prtn_elig_id
835: and c_effective_date
836: between tab1.effective_start_date and tab1.effective_end_date
912: tab3.ELIG_HLTH_CVG_FLAG,
913: tab3.ELIG_ANTHR_PL_FLAG
914: from ben_prtn_elig_f tab1,
915: ben_prtn_elig_prfl_f tab2,
916: ben_eligy_prfl_f tab3
917: where tab1.plip_id = c_plip_id
918: and tab1.prtn_elig_id = tab2.prtn_elig_id
919: and c_effective_date
920: between tab1.effective_start_date and tab1.effective_end_date
996: tab3.ELIG_HLTH_CVG_FLAG,
997: tab3.ELIG_ANTHR_PL_FLAG
998: from ben_prtn_elig_f tab1,
999: ben_prtn_elig_prfl_f tab2,
1000: ben_eligy_prfl_f tab3
1001: where tab1.pl_id = c_pl_id
1002: and tab1.prtn_elig_id = tab2.prtn_elig_id
1003: and c_effective_date
1004: between tab1.effective_start_date and tab1.effective_end_date
1080: tab3.ELIG_HLTH_CVG_FLAG,
1081: tab3.ELIG_ANTHR_PL_FLAG
1082: from ben_prtn_elig_f tab1,
1083: ben_prtn_elig_prfl_f tab2,
1084: ben_eligy_prfl_f tab3
1085: where tab1.oipl_id = c_oipl_id
1086: and tab1.prtn_elig_id = tab2.prtn_elig_id
1087: and c_effective_date
1088: between tab1.effective_start_date and tab1.effective_end_date
1524: -- Column and cache details
1525: --
1526: if p_cache_code = 'ELPEPT' then
1527: --
1528: l_mastertab_name := 'ben_eligy_prfl_f';
1529: l_masterpkcol_name := 'eligy_prfl_id';
1530: l_lkup_name := 'ben_elp_cache.g_elpept_lookup';
1531: l_inst_name := 'ben_elp_cache.g_elpept_inst';
1532: l_tabdet_set(0).tab_name := 'ben_elig_per_typ_prte_f';
1532: l_tabdet_set(0).tab_name := 'ben_elig_per_typ_prte_f';
1533: --
1534: elsif p_cache_code = 'ELPEES' then
1535: --
1536: l_mastertab_name := 'ben_eligy_prfl_f';
1537: l_masterpkcol_name := 'eligy_prfl_id';
1538: l_lkup_name := 'ben_elp_cache.g_elpees_lookup';
1539: l_inst_name := 'ben_elp_cache.g_elpees_inst';
1540: l_tabdet_set(0).tab_name := 'ben_elig_ee_stat_prte_f';
1540: l_tabdet_set(0).tab_name := 'ben_elig_ee_stat_prte_f';
1541: --
1542: elsif p_cache_code = 'ELPESA' then
1543: --
1544: l_mastertab_name := 'ben_eligy_prfl_f';
1545: l_masterpkcol_name := 'eligy_prfl_id';
1546: l_lkup_name := 'ben_elp_cache.g_elpesa_lookup';
1547: l_inst_name := 'ben_elp_cache.g_elpesa_inst';
1548: l_tabdet_set(0).tab_name := 'ben_elig_svc_area_prte_f';
1552: l_tabdet_set(2).tab_name := 'ben_pstl_zip_rng_f';
1553: --
1554: elsif p_cache_code = 'ELPEHS' then
1555: --
1556: l_mastertab_name := 'ben_eligy_prfl_f';
1557: l_masterpkcol_name := 'eligy_prfl_id';
1558: l_lkup_name := 'ben_elp_cache.g_elpehs_lookup';
1559: l_inst_name := 'ben_elp_cache.g_elpehs_inst';
1560: l_tabdet_set(0).tab_name := 'ben_elig_hrly_slrd_prte_f';
1560: l_tabdet_set(0).tab_name := 'ben_elig_hrly_slrd_prte_f';
1561: --
1562: elsif p_cache_code = 'ELPELS' then
1563: --
1564: l_mastertab_name := 'ben_eligy_prfl_f';
1565: l_masterpkcol_name := 'eligy_prfl_id';
1566: l_lkup_name := 'ben_elp_cache.g_elpels_lookup';
1567: l_inst_name := 'ben_elp_cache.g_elpels_inst';
1568: l_tabdet_set(0).tab_name := 'ben_elig_los_prte_f';
1571: l_tabdet_set(1).tab_datetype := 'nondt';
1572: --
1573: elsif p_cache_code = 'ELPECP' then
1574: --
1575: l_mastertab_name := 'ben_eligy_prfl_f';
1576: l_masterpkcol_name := 'eligy_prfl_id';
1577: l_lkup_name := 'ben_elp_cache.g_elpecp_lookup';
1578: l_inst_name := 'ben_elp_cache.g_elpecp_inst';
1579: l_tabdet_set(0).tab_name := 'ben_elig_cmbn_age_los_prte_f';
2033: --
2034: cursor c_elpepg_look is
2035: select elp.eligy_prfl_id,
2036: elp.business_group_id
2037: from ben_eligy_prfl_f elp
2038: where p_effective_date
2039: between elp.effective_start_date
2040: and elp.effective_end_date
2041: and exists(select null
2349: --
2350: cursor c_elpept_look is
2351: select elp.eligy_prfl_id,
2352: elp.business_group_id
2353: from ben_eligy_prfl_f elp
2354: where p_effective_date
2355: between elp.effective_start_date
2356: and elp.effective_end_date
2357: and exists(select null
2609: --
2610: cursor c_elpean_look is
2611: select elp.eligy_prfl_id,
2612: elp.business_group_id
2613: from ben_eligy_prfl_f elp
2614: where p_effective_date
2615: between elp.effective_start_date
2616: and elp.effective_end_date
2617: and exists(select null
2864: --
2865: cursor c_elperl_look is
2866: select elp.eligy_prfl_id,
2867: elp.business_group_id
2868: from ben_eligy_prfl_f elp
2869: where p_effective_date
2870: between elp.effective_start_date
2871: and elp.effective_end_date
2872: and exists(select null
3115: --
3116: cursor c_elpees_look is
3117: select elp.eligy_prfl_id,
3118: elp.business_group_id
3119: from ben_eligy_prfl_f elp
3120: where p_effective_date
3121: between elp.effective_start_date
3122: and elp.effective_end_date
3123: and exists(select null
3368: l_id number;
3369: --
3370: cursor c_elpesa_look is
3371: select elp.eligy_prfl_id, elp.business_group_id
3372: from ben_eligy_prfl_f elp
3373: where p_effective_date
3374: between elp.effective_start_date
3375: and elp.effective_end_date
3376: and exists (select null
3642: --
3643: cursor c_elpels_look is
3644: select elp.eligy_prfl_id,
3645: elp.business_group_id
3646: from ben_eligy_prfl_f elp
3647: where p_effective_date
3648: between elp.effective_start_date
3649: and elp.effective_end_date
3650: and exists(select null
3914: --
3915: cursor c_elpecp_look is
3916: select elp.eligy_prfl_id,
3917: elp.business_group_id
3918: from ben_eligy_prfl_f elp
3919: where p_effective_date
3920: between elp.effective_start_date
3921: and elp.effective_end_date
3922: and exists(select null
4182: --
4183: cursor c_elpewl_look is
4184: select elp.eligy_prfl_id,
4185: elp.business_group_id
4186: from ben_eligy_prfl_f elp
4187: where p_effective_date
4188: between elp.effective_start_date
4189: and elp.effective_end_date
4190: and exists(select null
4436: --
4437: cursor c_elpeou_look is
4438: select elp.eligy_prfl_id,
4439: elp.business_group_id
4440: from ben_eligy_prfl_f elp
4441: where p_effective_date
4442: between elp.effective_start_date
4443: and elp.effective_end_date
4444: and exists(select null
4689: l_id number;
4690: --
4691: cursor c_elpehs_look is
4692: select elp.eligy_prfl_id, elp.business_group_id
4693: from ben_eligy_prfl_f elp
4694: where p_effective_date
4695: between elp.effective_start_date
4696: and elp.effective_end_date
4697: and exists(select null
4947: l_id number;
4948: --
4949: cursor c_elpefp_look is
4950: select elp.eligy_prfl_id, elp.business_group_id
4951: from ben_eligy_prfl_f elp
4952: where p_effective_date
4953: between elp.effective_start_date
4954: and elp.effective_end_date
4955: and exists(select null
5201: --
5202: cursor c_elpesh_look is
5203: select elp.eligy_prfl_id,
5204: elp.business_group_id
5205: from ben_eligy_prfl_f elp
5206: where p_effective_date
5207: between elp.effective_start_date
5208: and elp.effective_end_date
5209: and exists(select null
5468: l_id number;
5469: --
5470: cursor c_elpehw_look is
5471: select elp.eligy_prfl_id, elp.business_group_id
5472: from ben_eligy_prfl_f elp
5473: where p_effective_date
5474: between elp.effective_start_date
5475: and elp.effective_end_date
5476: and exists(select null
5742: l_id number;
5743: --
5744: cursor c_elpecl_look is
5745: select elp.eligy_prfl_id, elp.business_group_id
5746: from ben_eligy_prfl_f elp
5747: where p_effective_date
5748: between elp.effective_start_date
5749: and elp.effective_end_date
5750: and exists(select null
6017: --
6018: cursor c_elpepf_look is
6019: select epl.eligy_prfl_id,
6020: epl.business_group_id
6021: from ben_eligy_prfl_f epl
6022: where p_effective_date
6023: between epl.effective_start_date
6024: and epl.effective_end_date
6025: and exists(select null
6284: --
6285: cursor c_elpegr_look is
6286: select elp.eligy_prfl_id,
6287: elp.business_group_id
6288: from ben_eligy_prfl_f elp
6289: where p_effective_date
6290: between elp.effective_start_date
6291: and elp.effective_end_date
6292: and exists(select null
6538: --
6539: cursor c_elpegn_look is
6540: select elp.eligy_prfl_id,
6541: elp.business_group_id
6542: from ben_eligy_prfl_f elp
6543: where p_effective_date
6544: between elp.effective_start_date
6545: and elp.effective_end_date
6546: and exists(select null
6791: l_id number;
6792: --
6793: cursor c_elpejp_look is
6794: select elp.eligy_prfl_id, elp.business_group_id
6795: from ben_eligy_prfl_f elp
6796: where p_effective_date
6797: between elp.effective_start_date
6798: and elp.effective_end_date
6799: and exists(select null
7043: --
7044: cursor c_elpepb_look is
7045: select elp.eligy_prfl_id,
7046: elp.business_group_id
7047: from ben_eligy_prfl_f elp
7048: where p_effective_date
7049: between elp.effective_start_date
7050: and elp.effective_end_date
7051: and exists(select null
7296: l_id number;
7297: --
7298: cursor c_elpepy_look is
7299: select elp.eligy_prfl_id, elp.business_group_id
7300: from ben_eligy_prfl_f elp
7301: where p_effective_date
7302: between elp.effective_start_date
7303: and elp.effective_end_date
7304: and exists(select null
7550: --
7551: cursor c_elpebu_look is
7552: select elp.eligy_prfl_id,
7553: elp.business_group_id
7554: from ben_eligy_prfl_f elp
7555: where p_effective_date
7556: between elp.effective_start_date
7557: and elp.effective_end_date
7558: and exists(select null
7804: --
7805: cursor c_elpelu_look is
7806: select elp.eligy_prfl_id,
7807: elp.business_group_id
7808: from ben_eligy_prfl_f elp
7809: where p_effective_date
7810: between elp.effective_start_date
7811: and elp.effective_end_date
7812: and exists(select null
8058: --
8059: cursor c_elpelr_look is
8060: select elp.eligy_prfl_id,
8061: elp.business_group_id
8062: from ben_eligy_prfl_f elp
8063: where p_effective_date
8064: between elp.effective_start_date
8065: and elp.effective_end_date
8066: and exists(select null
8313: l_id number;
8314: --
8315: cursor c_elpeap_look is
8316: select elp.eligy_prfl_id, elp.business_group_id
8317: from ben_eligy_prfl_f elp
8318: where p_effective_date
8319: between elp.effective_start_date
8320: and elp.effective_end_date
8321: and exists(select null
8581: --
8582: cursor c_elpepz_look is
8583: select elp.eligy_prfl_id,
8584: elp.business_group_id
8585: from ben_eligy_prfl_f elp
8586: where p_effective_date
8587: between elp.effective_start_date
8588: and elp.effective_end_date
8589: and exists(select null
8849: --
8850: cursor c_elpebn_look is
8851: select elp.eligy_prfl_id,
8852: elp.business_group_id
8853: from ben_eligy_prfl_f elp
8854: where p_effective_date
8855: between elp.effective_start_date
8856: and elp.effective_end_date
8857: and exists(select null
9103: --
9104: cursor c_elpeln_look is
9105: select elp.eligy_prfl_id,
9106: elp.business_group_id
9107: from ben_eligy_prfl_f elp
9108: where p_effective_date
9109: between elp.effective_start_date
9110: and elp.effective_end_date
9111: and exists(select null
9363: --
9364: cursor c_elpepp_look is
9365: select elp.eligy_prfl_id,
9366: elp.business_group_id
9367: from ben_eligy_prfl_f elp
9368: where p_effective_date
9369: between elp.effective_start_date
9370: and elp.effective_end_date
9371: and exists(select null
9609: --
9610: cursor c_elpeoy_look is
9611: select elp.eligy_prfl_id,
9612: elp.business_group_id
9613: from ben_eligy_prfl_f elp
9614: where p_effective_date
9615: between elp.effective_start_date
9616: and elp.effective_end_date
9617: and exists(select null
9859: --
9860: cursor c_elpetd_look is
9861: select elp.eligy_prfl_id,
9862: elp.business_group_id
9863: from ben_eligy_prfl_f elp
9864: where p_effective_date
9865: between elp.effective_start_date
9866: and elp.effective_end_date
9867: and exists(select null
10110: --
10111: cursor c_elpeno_look is
10112: select elp.eligy_prfl_id,
10113: elp.business_group_id
10114: from ben_eligy_prfl_f elp
10115: where p_effective_date
10116: between elp.effective_start_date
10117: and elp.effective_end_date
10118: and exists(select null
10353: --
10354: cursor c_elpeep_look is
10355: select elp.eligy_prfl_id,
10356: elp.business_group_id
10357: from ben_eligy_prfl_f elp
10358: where p_effective_date
10359: between elp.effective_start_date
10360: and elp.effective_end_date
10361: and exists(select null
10601: --
10602: cursor c_elpeei_look is
10603: select elp.eligy_prfl_id,
10604: elp.business_group_id
10605: from ben_eligy_prfl_f elp
10606: where p_effective_date
10607: between elp.effective_start_date
10608: and elp.effective_end_date
10609: and exists(select null
10849: --
10850: cursor c_elpeeg_look is
10851: select elp.eligy_prfl_id,
10852: elp.business_group_id
10853: from ben_eligy_prfl_f elp
10854: where p_effective_date
10855: between elp.effective_start_date
10856: and elp.effective_end_date
10857: and exists(select null
11097: --
11098: cursor c_elpedp_look is
11099: select elp.eligy_prfl_id,
11100: elp.business_group_id
11101: from ben_eligy_prfl_f elp
11102: where p_effective_date
11103: between elp.effective_start_date
11104: and elp.effective_end_date
11105: and exists(select null
11345: --
11346: cursor c_elpelv_look is
11347: select elp.eligy_prfl_id,
11348: elp.business_group_id
11349: from ben_eligy_prfl_f elp
11350: where p_effective_date
11351: between elp.effective_start_date
11352: and elp.effective_end_date
11353: and exists(select null
11599: --
11600: cursor c_elpeom_look is
11601: select elp.eligy_prfl_id,
11602: elp.business_group_id
11603: from ben_eligy_prfl_f elp
11604: where p_effective_date
11605: between elp.effective_start_date
11606: and elp.effective_end_date
11607: and exists(select null
11844: --
11845: cursor c_elpeai_look is
11846: select elp.eligy_prfl_id,
11847: elp.business_group_id
11848: from ben_eligy_prfl_f elp
11849: where p_effective_date
11850: between elp.effective_start_date
11851: and elp.effective_end_date
11852: and exists(select null
12091: --
12092: cursor c_elpedi_look is
12093: select elp.eligy_prfl_id,
12094: elp.business_group_id
12095: from ben_eligy_prfl_f elp
12096: where p_effective_date
12097: between elp.effective_start_date
12098: and elp.effective_end_date
12099: and exists(select null
12338: --
12339: cursor c_elpeet_look is
12340: select elp.eligy_prfl_id,
12341: elp.business_group_id
12342: from ben_eligy_prfl_f elp
12343: where p_effective_date
12344: between elp.effective_start_date
12345: and elp.effective_end_date
12346: and exists(select null
12587: --
12588: cursor c_elpedt_look is
12589: select elp.eligy_prfl_id,
12590: elp.business_group_id
12591: from ben_eligy_prfl_f elp
12592: where p_effective_date
12593: between elp.effective_start_date
12594: and elp.effective_end_date
12595: and exists(select null
12836: --
12837: cursor c_elpedg_look is
12838: select elp.eligy_prfl_id,
12839: elp.business_group_id
12840: from ben_eligy_prfl_f elp
12841: where p_effective_date
12842: between elp.effective_start_date
12843: and elp.effective_end_date
12844: and exists(select null
13083: --
13084: cursor c_elpecq_look is
13085: select elp.eligy_prfl_id,
13086: elp.business_group_id
13087: from ben_eligy_prfl_f elp
13088: where p_effective_date
13089: between elp.effective_start_date
13090: and elp.effective_end_date
13091: and exists(select null