[Home] [Help]
214: , a.bnf_mn_dsgntbl_pct_val
215: , a.bnf_pct_incrmt_val
216: , b.oipl_id
217: from ben_pl_f a
218: , ben_prtt_enrt_rslt_f b
219: where b.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
220: -- and b.prtt_enrt_rslt_stat_cd is null -- bug 8901277
221: and p_effective_date between b.effective_start_date -- bug 8901277
222: and b.effective_end_date
239: and nvl(l_lf_evt_ocrd_dt,p_effective_date) between opt.effective_start_date and opt.effective_end_date;
240: --
241: CURSOR c_pil is
242: SELECT pil.lf_evt_ocrd_dt
243: FROM ben_prtt_enrt_rslt_f pen,
244: ben_per_in_ler pil
245: WHERE prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
246: -- AND pen.prtt_enrt_rslt_stat_cd is NULL -- bug 8901277
247: AND p_effective_date BETWEEN pen.effective_start_date
552: --
553: --
554: cursor c1 is
555: select bnft_amt
556: from ben_prtt_enrt_rslt_f a
557: where a.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
558: and a.prtt_enrt_rslt_stat_cd is null
559: and a.business_group_id + 0 = p_business_group_id
560: and p_validation_start_date <= effective_end_date
586: and nvl(p_amt_dsgd_val,hr_api.g_number)
587: <> nvl(ben_pbn_shd.g_old_rec.amt_dsgd_val, hr_api.g_number)
588: or not l_api_updating) then
589: --
590: -- check if sum of amt_dsgd_val is less or = bnft_amt value on ben_prtt_enrt_rslt_f table
591: --
592: open c1;
593: --
594: fetch c1 into l_amt;
595: if c1%notfound then
596: --
597: close c1;
598: --
599: -- raise error as corresponding rslt does not exist on ben_prtt_enrt_rslt_f
600: -- table.
601: --
602: ben_pbn_shd.constraint_error('BEN_PL_BNF_FK4');
603: else
662: l_uom varchar2(30);
663: --
664: cursor c1 is
665: select uom
666: from ben_prtt_enrt_rslt_f a
667: where a.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
668: and a.prtt_enrt_rslt_stat_cd is null
669: and a.business_group_id + 0 = p_business_group_id
670: and p_effective_date between effective_start_date
696: if c1%notfound then
697: --
698: close c1;
699: --
700: -- raise error as corresponding rslt does not exist on ben_prtt_enrt_rslt_f
701: -- table.
702: --
703: ben_pbn_shd.constraint_error('BEN_PL_BNF_FK4');
704: elsif p_amt_dsgd_uom <> l_uom then
995: from ben_dsgn_rqmt_rlshp_typ drt
996: , ben_dsgn_rqmt_f drm
997: , ben_opt_f opt
998: , ben_oipl_f oipl
999: , ben_prtt_enrt_rslt_f pen
1000: where drt.dsgn_rqmt_id = drm.dsgn_rqmt_id
1001: and drt.business_group_id = p_business_group_id
1002: and drm.dsgn_typ_cd = 'BNF'
1003: and drm.business_group_id = p_business_group_id
1018: select distinct drt.rlshp_typ_cd
1019: from ben_dsgn_rqmt_rlshp_typ drt
1020: , ben_dsgn_rqmt_f drm
1021: , ben_oipl_f oipl
1022: , ben_prtt_enrt_rslt_f pen
1023: where drt.dsgn_rqmt_id = drm.dsgn_rqmt_id
1024: and drt.business_group_id = p_business_group_id
1025: and drm.dsgn_typ_cd = 'BNF'
1026: and drm.business_group_id = p_business_group_id
1038: select distinct drt.rlshp_typ_cd
1039: from ben_dsgn_rqmt_rlshp_typ drt
1040: , ben_dsgn_rqmt_f drm
1041: , ben_pl_f pln
1042: , ben_prtt_enrt_rslt_f pen
1043: where drt.dsgn_rqmt_id = drm.dsgn_rqmt_id
1044: and drt.business_group_id = p_business_group_id
1045: and drm.dsgn_typ_cd = 'BNF'
1046: and drm.business_group_id = p_business_group_id
1085: and pcr.contact_type = hll.lookup_code ;
1086: --
1087: cursor c_pl_oipl is
1088: select pl_id, oipl_id
1089: from ben_prtt_enrt_rslt_f pen
1090: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
1091: and pen.prtt_enrt_rslt_stat_cd is null
1092: and pen.business_group_id = p_business_group_id
1093: and p_effective_date between pen.effective_start_date and pen.effective_end_date ;
1375: from ben_dsgn_rqmt_rlshp_typ drt
1376: , ben_dsgn_rqmt_f drm
1377: , ben_opt_f opt
1378: , ben_oipl_f oipl
1379: , ben_prtt_enrt_rslt_f pen
1380: where drt.dsgn_rqmt_id = drm.dsgn_rqmt_id
1381: and drt.business_group_id = p_business_group_id
1382: and drm.dsgn_typ_cd = 'BNF'
1383: and drm.business_group_id = p_business_group_id
1409: (select distinct drt.rlshp_typ_cd
1410: from ben_dsgn_rqmt_rlshp_typ drt
1411: , ben_dsgn_rqmt_f drm
1412: , ben_oipl_f oipl
1413: , ben_prtt_enrt_rslt_f pen
1414: where drt.dsgn_rqmt_id = drm.dsgn_rqmt_id
1415: and drt.business_group_id = p_business_group_id
1416: and drm.dsgn_typ_cd = 'BNF'
1417: and drm.business_group_id = p_business_group_id
1440: (select distinct drt.rlshp_typ_cd
1441: from ben_dsgn_rqmt_rlshp_typ drt
1442: , ben_dsgn_rqmt_f drm
1443: , ben_pl_f pln
1444: , ben_prtt_enrt_rslt_f pen
1445: where drt.dsgn_rqmt_id = drm.dsgn_rqmt_id
1446: and drt.business_group_id = p_business_group_id
1447: and drm.dsgn_typ_cd = 'BNF'
1448: and drm.business_group_id = p_business_group_id
1483: and pcr.contact_type = hll.lookup_code ;
1484: --
1485: cursor c_pl_oipl is
1486: select pl_id, oipl_id
1487: from ben_prtt_enrt_rslt_f pen
1488: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
1489: and pen.business_group_id = p_business_group_id
1490: and p_effective_date between pen.effective_start_date and pen.effective_end_date ;
1491: --
1886: p_argument_value => p_validation_end_date);
1887: --
1888: If ((nvl(p_prtt_enrt_rslt_id, hr_api.g_number) <> hr_api.g_number) and
1889: NOT (dt_api.check_min_max_dates
1890: (p_base_table_name => 'ben_prtt_enrt_rslt_f',
1891: p_base_key_column => 'prtt_enrt_rslt_id',
1892: p_base_key_value => p_prtt_enrt_rslt_id,
1893: p_from_date => p_validation_start_date,
1894: p_to_date => p_validation_end_date))) Then
1891: p_base_key_column => 'prtt_enrt_rslt_id',
1892: p_base_key_value => p_prtt_enrt_rslt_id,
1893: p_from_date => p_validation_start_date,
1894: p_to_date => p_validation_end_date))) Then
1895: l_table_name := 'ben_prtt_enrt_rslt_f';
1896: Raise l_integrity_error;
1897: End If;
1898: --
1899: --