[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
2340: --
2341: cursor c_elpept_look is
2342: select elp.eligy_prfl_id,
2343: elp.business_group_id
2344: from ben_eligy_prfl_f elp
2345: where p_effective_date
2346: between elp.effective_start_date
2347: and elp.effective_end_date
2348: and exists(select null
2592: --
2593: cursor c_elpean_look is
2594: select elp.eligy_prfl_id,
2595: elp.business_group_id
2596: from ben_eligy_prfl_f elp
2597: where p_effective_date
2598: between elp.effective_start_date
2599: and elp.effective_end_date
2600: and exists(select null
2839: --
2840: cursor c_elperl_look is
2841: select elp.eligy_prfl_id,
2842: elp.business_group_id
2843: from ben_eligy_prfl_f elp
2844: where p_effective_date
2845: between elp.effective_start_date
2846: and elp.effective_end_date
2847: and exists(select null
3081: --
3082: cursor c_elpees_look is
3083: select elp.eligy_prfl_id,
3084: elp.business_group_id
3085: from ben_eligy_prfl_f elp
3086: where p_effective_date
3087: between elp.effective_start_date
3088: and elp.effective_end_date
3089: and exists(select null
3325: l_id number;
3326: --
3327: cursor c_elpesa_look is
3328: select elp.eligy_prfl_id, elp.business_group_id
3329: from ben_eligy_prfl_f elp
3330: where p_effective_date
3331: between elp.effective_start_date
3332: and elp.effective_end_date
3333: and exists (select null
3590: --
3591: cursor c_elpels_look is
3592: select elp.eligy_prfl_id,
3593: elp.business_group_id
3594: from ben_eligy_prfl_f elp
3595: where p_effective_date
3596: between elp.effective_start_date
3597: and elp.effective_end_date
3598: and exists(select null
3853: --
3854: cursor c_elpecp_look is
3855: select elp.eligy_prfl_id,
3856: elp.business_group_id
3857: from ben_eligy_prfl_f elp
3858: where p_effective_date
3859: between elp.effective_start_date
3860: and elp.effective_end_date
3861: and exists(select null
4112: --
4113: cursor c_elpewl_look is
4114: select elp.eligy_prfl_id,
4115: elp.business_group_id
4116: from ben_eligy_prfl_f elp
4117: where p_effective_date
4118: between elp.effective_start_date
4119: and elp.effective_end_date
4120: and exists(select null
4357: --
4358: cursor c_elpeou_look is
4359: select elp.eligy_prfl_id,
4360: elp.business_group_id
4361: from ben_eligy_prfl_f elp
4362: where p_effective_date
4363: between elp.effective_start_date
4364: and elp.effective_end_date
4365: and exists(select null
4601: l_id number;
4602: --
4603: cursor c_elpehs_look is
4604: select elp.eligy_prfl_id, elp.business_group_id
4605: from ben_eligy_prfl_f elp
4606: where p_effective_date
4607: between elp.effective_start_date
4608: and elp.effective_end_date
4609: and exists(select null
4850: l_id number;
4851: --
4852: cursor c_elpefp_look is
4853: select elp.eligy_prfl_id, elp.business_group_id
4854: from ben_eligy_prfl_f elp
4855: where p_effective_date
4856: between elp.effective_start_date
4857: and elp.effective_end_date
4858: and exists(select null
5095: --
5096: cursor c_elpesh_look is
5097: select elp.eligy_prfl_id,
5098: elp.business_group_id
5099: from ben_eligy_prfl_f elp
5100: where p_effective_date
5101: between elp.effective_start_date
5102: and elp.effective_end_date
5103: and exists(select null
5353: l_id number;
5354: --
5355: cursor c_elpehw_look is
5356: select elp.eligy_prfl_id, elp.business_group_id
5357: from ben_eligy_prfl_f elp
5358: where p_effective_date
5359: between elp.effective_start_date
5360: and elp.effective_end_date
5361: and exists(select null
5618: l_id number;
5619: --
5620: cursor c_elpecl_look is
5621: select elp.eligy_prfl_id, elp.business_group_id
5622: from ben_eligy_prfl_f elp
5623: where p_effective_date
5624: between elp.effective_start_date
5625: and elp.effective_end_date
5626: and exists(select null
5884: --
5885: cursor c_elpepf_look is
5886: select epl.eligy_prfl_id,
5887: epl.business_group_id
5888: from ben_eligy_prfl_f epl
5889: where p_effective_date
5890: between epl.effective_start_date
5891: and epl.effective_end_date
5892: and exists(select null
6142: --
6143: cursor c_elpegr_look is
6144: select elp.eligy_prfl_id,
6145: elp.business_group_id
6146: from ben_eligy_prfl_f elp
6147: where p_effective_date
6148: between elp.effective_start_date
6149: and elp.effective_end_date
6150: and exists(select null
6387: --
6388: cursor c_elpegn_look is
6389: select elp.eligy_prfl_id,
6390: elp.business_group_id
6391: from ben_eligy_prfl_f elp
6392: where p_effective_date
6393: between elp.effective_start_date
6394: and elp.effective_end_date
6395: and exists(select null
6631: l_id number;
6632: --
6633: cursor c_elpejp_look is
6634: select elp.eligy_prfl_id, elp.business_group_id
6635: from ben_eligy_prfl_f elp
6636: where p_effective_date
6637: between elp.effective_start_date
6638: and elp.effective_end_date
6639: and exists(select null
6874: --
6875: cursor c_elpepb_look is
6876: select elp.eligy_prfl_id,
6877: elp.business_group_id
6878: from ben_eligy_prfl_f elp
6879: where p_effective_date
6880: between elp.effective_start_date
6881: and elp.effective_end_date
6882: and exists(select null
7118: l_id number;
7119: --
7120: cursor c_elpepy_look is
7121: select elp.eligy_prfl_id, elp.business_group_id
7122: from ben_eligy_prfl_f elp
7123: where p_effective_date
7124: between elp.effective_start_date
7125: and elp.effective_end_date
7126: and exists(select null
7363: --
7364: cursor c_elpebu_look is
7365: select elp.eligy_prfl_id,
7366: elp.business_group_id
7367: from ben_eligy_prfl_f elp
7368: where p_effective_date
7369: between elp.effective_start_date
7370: and elp.effective_end_date
7371: and exists(select null
7608: --
7609: cursor c_elpelu_look is
7610: select elp.eligy_prfl_id,
7611: elp.business_group_id
7612: from ben_eligy_prfl_f elp
7613: where p_effective_date
7614: between elp.effective_start_date
7615: and elp.effective_end_date
7616: and exists(select null
7853: --
7854: cursor c_elpelr_look is
7855: select elp.eligy_prfl_id,
7856: elp.business_group_id
7857: from ben_eligy_prfl_f elp
7858: where p_effective_date
7859: between elp.effective_start_date
7860: and elp.effective_end_date
7861: and exists(select null
8099: l_id number;
8100: --
8101: cursor c_elpeap_look is
8102: select elp.eligy_prfl_id, elp.business_group_id
8103: from ben_eligy_prfl_f elp
8104: where p_effective_date
8105: between elp.effective_start_date
8106: and elp.effective_end_date
8107: and exists(select null
8358: --
8359: cursor c_elpepz_look is
8360: select elp.eligy_prfl_id,
8361: elp.business_group_id
8362: from ben_eligy_prfl_f elp
8363: where p_effective_date
8364: between elp.effective_start_date
8365: and elp.effective_end_date
8366: and exists(select null
8617: --
8618: cursor c_elpebn_look is
8619: select elp.eligy_prfl_id,
8620: elp.business_group_id
8621: from ben_eligy_prfl_f elp
8622: where p_effective_date
8623: between elp.effective_start_date
8624: and elp.effective_end_date
8625: and exists(select null
8862: --
8863: cursor c_elpeln_look is
8864: select elp.eligy_prfl_id,
8865: elp.business_group_id
8866: from ben_eligy_prfl_f elp
8867: where p_effective_date
8868: between elp.effective_start_date
8869: and elp.effective_end_date
8870: and exists(select null
9113: --
9114: cursor c_elpepp_look is
9115: select elp.eligy_prfl_id,
9116: elp.business_group_id
9117: from ben_eligy_prfl_f elp
9118: where p_effective_date
9119: between elp.effective_start_date
9120: and elp.effective_end_date
9121: and exists(select null
9350: --
9351: cursor c_elpeoy_look is
9352: select elp.eligy_prfl_id,
9353: elp.business_group_id
9354: from ben_eligy_prfl_f elp
9355: where p_effective_date
9356: between elp.effective_start_date
9357: and elp.effective_end_date
9358: and exists(select null
9591: --
9592: cursor c_elpetd_look is
9593: select elp.eligy_prfl_id,
9594: elp.business_group_id
9595: from ben_eligy_prfl_f elp
9596: where p_effective_date
9597: between elp.effective_start_date
9598: and elp.effective_end_date
9599: and exists(select null
9833: --
9834: cursor c_elpeno_look is
9835: select elp.eligy_prfl_id,
9836: elp.business_group_id
9837: from ben_eligy_prfl_f elp
9838: where p_effective_date
9839: between elp.effective_start_date
9840: and elp.effective_end_date
9841: and exists(select null
10067: --
10068: cursor c_elpeep_look is
10069: select elp.eligy_prfl_id,
10070: elp.business_group_id
10071: from ben_eligy_prfl_f elp
10072: where p_effective_date
10073: between elp.effective_start_date
10074: and elp.effective_end_date
10075: and exists(select null
10306: --
10307: cursor c_elpeei_look is
10308: select elp.eligy_prfl_id,
10309: elp.business_group_id
10310: from ben_eligy_prfl_f elp
10311: where p_effective_date
10312: between elp.effective_start_date
10313: and elp.effective_end_date
10314: and exists(select null
10545: --
10546: cursor c_elpeeg_look is
10547: select elp.eligy_prfl_id,
10548: elp.business_group_id
10549: from ben_eligy_prfl_f elp
10550: where p_effective_date
10551: between elp.effective_start_date
10552: and elp.effective_end_date
10553: and exists(select null
10784: --
10785: cursor c_elpedp_look is
10786: select elp.eligy_prfl_id,
10787: elp.business_group_id
10788: from ben_eligy_prfl_f elp
10789: where p_effective_date
10790: between elp.effective_start_date
10791: and elp.effective_end_date
10792: and exists(select null
11023: --
11024: cursor c_elpelv_look is
11025: select elp.eligy_prfl_id,
11026: elp.business_group_id
11027: from ben_eligy_prfl_f elp
11028: where p_effective_date
11029: between elp.effective_start_date
11030: and elp.effective_end_date
11031: and exists(select null
11268: --
11269: cursor c_elpeom_look is
11270: select elp.eligy_prfl_id,
11271: elp.business_group_id
11272: from ben_eligy_prfl_f elp
11273: where p_effective_date
11274: between elp.effective_start_date
11275: and elp.effective_end_date
11276: and exists(select null
11504: --
11505: cursor c_elpeai_look is
11506: select elp.eligy_prfl_id,
11507: elp.business_group_id
11508: from ben_eligy_prfl_f elp
11509: where p_effective_date
11510: between elp.effective_start_date
11511: and elp.effective_end_date
11512: and exists(select null
11742: --
11743: cursor c_elpedi_look is
11744: select elp.eligy_prfl_id,
11745: elp.business_group_id
11746: from ben_eligy_prfl_f elp
11747: where p_effective_date
11748: between elp.effective_start_date
11749: and elp.effective_end_date
11750: and exists(select null
11980: --
11981: cursor c_elpeet_look is
11982: select elp.eligy_prfl_id,
11983: elp.business_group_id
11984: from ben_eligy_prfl_f elp
11985: where p_effective_date
11986: between elp.effective_start_date
11987: and elp.effective_end_date
11988: and exists(select null
12220: --
12221: cursor c_elpedt_look is
12222: select elp.eligy_prfl_id,
12223: elp.business_group_id
12224: from ben_eligy_prfl_f elp
12225: where p_effective_date
12226: between elp.effective_start_date
12227: and elp.effective_end_date
12228: and exists(select null
12460: --
12461: cursor c_elpedg_look is
12462: select elp.eligy_prfl_id,
12463: elp.business_group_id
12464: from ben_eligy_prfl_f elp
12465: where p_effective_date
12466: between elp.effective_start_date
12467: and elp.effective_end_date
12468: and exists(select null
12698: --
12699: cursor c_elpecq_look is
12700: select elp.eligy_prfl_id,
12701: elp.business_group_id
12702: from ben_eligy_prfl_f elp
12703: where p_effective_date
12704: between elp.effective_start_date
12705: and elp.effective_end_date
12706: and exists(select null