51: p_prtt_reimbmt_rqst_id => p_prtt_reimbmt_rqst_id,
52: p_object_version_number => p_object_version_number);
53: --
54: if (l_api_updating
55: and nvl(p_prtt_reimbmt_rqst_id,hr_api.g_number)
56: <> ben_prc_shd.g_old_rec.prtt_reimbmt_rqst_id) then
57: --
58: -- raise error as PK has changed
59: --
334: and prc.prtt_reimbmt_rqst_stat_cd not in ('DND','VOIDED','DPLICT')
335: and pl.pl_id = p_pl_id
336: and pl.pl_id = prc.pl_id
337: and p_effective_date between pl.effective_start_date and pl.effective_end_date
338: and prc.effective_end_date = hr_api.g_eot --future created entry to be taken for calc
339: and (p_prtt_reimbmt_rqst_id is null
340: or prc.prtt_reimbmt_rqst_id <> p_prtt_reimbmt_rqst_id)
341: and pl.pl_id = pyr.pl_id
342: and pyr.yr_perd_id = yr.yr_perd_id
356: from ben_prtt_reimbmt_rqst_f prc
357: where prc.submitter_person_id = p_person_id
358: and prc.prtt_reimbmt_rqst_stat_cd not in ('DND','VOIDED','DPLICT')
359: and prc.pl_id = p_pl_id
360: and prc.effective_end_date = hr_api.g_eot
361: and (p_prtt_reimbmt_rqst_id is null
362: or prc.prtt_reimbmt_rqst_id <> p_prtt_reimbmt_rqst_id)
363: and prc.popl_yr_perd_id_1 = p_popl_yr_perd;
364: --
367: from ben_prtt_reimbmt_rqst_f prc
368: where prc.submitter_person_id = p_person_id
369: and prc.prtt_reimbmt_rqst_stat_cd not in ('DND','VOIDED','DPLICT')
370: and prc.pl_id = p_pl_id
371: and prc.effective_end_date = hr_api.g_eot
372: and (p_prtt_reimbmt_rqst_id is null
373: or prc.prtt_reimbmt_rqst_id <> p_prtt_reimbmt_rqst_id)
374: and prc.popl_yr_perd_id_2 = p_popl_yr_perd;
375:
471: p_effective_date => p_effective_date,
472: p_object_version_number => p_object_version_number);
473: --
474: if (l_api_updating
475: and nvl(p_gd_or_svc_typ_id,hr_api.g_number)
476: <> nvl(ben_prc_shd.g_old_rec.gd_or_svc_typ_id,hr_api.g_number)
477: or not l_api_updating) and
478: p_gd_or_svc_typ_id is not null then
479: --
472: p_object_version_number => p_object_version_number);
473: --
474: if (l_api_updating
475: and nvl(p_gd_or_svc_typ_id,hr_api.g_number)
476: <> nvl(ben_prc_shd.g_old_rec.gd_or_svc_typ_id,hr_api.g_number)
477: or not l_api_updating) and
478: p_gd_or_svc_typ_id is not null then
479: --
480: -- check if gd_or_svc_typ_id value exists in ben_gd_or_svc_typ table
559: p_effective_date => p_effective_date,
560: p_object_version_number => p_object_version_number);
561: --
562: if (l_api_updating
563: and nvl(p_provider_person_id,hr_api.g_number)
564: <> nvl(ben_prc_shd.g_old_rec.provider_person_id,hr_api.g_number)
565: or not l_api_updating) and
566: p_provider_person_id is not null then
567: --
560: p_object_version_number => p_object_version_number);
561: --
562: if (l_api_updating
563: and nvl(p_provider_person_id,hr_api.g_number)
564: <> nvl(ben_prc_shd.g_old_rec.provider_person_id,hr_api.g_number)
565: or not l_api_updating) and
566: p_provider_person_id is not null then
567: --
568: -- check if provider_person_id value exists in hr_all_organization_units table
643: p_object_version_number => p_object_version_number);
644: --
645: if (l_api_updating
646: and p_rcrrg_cd
647: <> nvl(ben_prc_shd.g_old_rec.rcrrg_cd,hr_api.g_varchar2)
648: or not l_api_updating)
649: and p_rcrrg_cd is not null then
650: --
651: -- check if value of lookup falls within lookup type.
649: and p_rcrrg_cd is not null then
650: --
651: -- check if value of lookup falls within lookup type.
652: --
653: if hr_api.not_exists_in_hr_lookups
654: (p_lookup_type => 'BEN_REIMBMT_RQST_RCRG',
655: p_lookup_code => p_rcrrg_cd,
656: p_effective_date => p_effective_date) then
657: --
716: p_object_version_number => p_object_version_number);
717: --
718: if (l_api_updating
719: and p_reimbmt_ctfn_typ_prvdd_cd
720: <> nvl(ben_prc_shd.g_old_rec.reimbmt_ctfn_typ_prvdd_cd,hr_api.g_varchar2)
721: or not l_api_updating)
722: and p_reimbmt_ctfn_typ_prvdd_cd is not null then
723: --
724: -- check if value of lookup falls within lookup type.
722: and p_reimbmt_ctfn_typ_prvdd_cd is not null then
723: --
724: -- check if value of lookup falls within lookup type.
725: --
726: if hr_api.not_exists_in_hr_lookups
727: (p_lookup_type => 'BEN_RMBMT_CTFN_TYP',
728: p_lookup_code => p_reimbmt_ctfn_typ_prvdd_cd,
729: p_effective_date => p_effective_date) then
730: --
840: p_object_version_number => p_object_version_number);
841: --
842: if (l_api_updating
843: and p_stat_rsn_cd
844: <> nvl(ben_prc_shd.g_old_rec.stat_rsn_cd,hr_api.g_varchar2)
845: or not l_api_updating)
846: and p_stat_rsn_cd is not null then
847: --
848: -- check if value of lookup falls within lookup type.
846: and p_stat_rsn_cd is not null then
847: --
848: -- check if value of lookup falls within lookup type.
849: --
850: if hr_api.not_exists_in_hr_lookups
851: (p_lookup_type => 'BEN_STAT_RSN',
852: p_lookup_code => p_stat_rsn_cd,
853: p_effective_date => p_effective_date) then
854: --
895: p_object_version_number => p_object_version_number);
896: --
897: if (l_api_updating
898: and p_pymt_stat_rsn_cd
899: <> nvl(ben_prc_shd.g_old_rec.pymt_stat_rsn_cd,hr_api.g_varchar2)
900: or not l_api_updating)
901: and p_pymt_stat_rsn_cd is not null then
902: --
903: -- check if value of lookup falls within lookup type.
901: and p_pymt_stat_rsn_cd is not null then
902: --
903: -- check if value of lookup falls within lookup type.
904: --
905: if hr_api.not_exists_in_hr_lookups
906: (p_lookup_type => 'BEN_PYMT_STAT_RSN',
907: p_lookup_code => p_pymt_stat_rsn_cd,
908: p_effective_date => p_effective_date) then
909: --
949: p_object_version_number => p_object_version_number);
950: --
951: if (l_api_updating
952: and p_pymt_stat_cd
953: <> nvl(ben_prc_shd.g_old_rec.pymt_stat_cd,hr_api.g_varchar2)
954: or not l_api_updating)
955: and p_pymt_stat_cd is not null then
956: --
957: -- check if value of lookup falls within lookup type.
955: and p_pymt_stat_cd is not null then
956: --
957: -- check if value of lookup falls within lookup type.
958: --
959: if hr_api.not_exists_in_hr_lookups
960: (p_lookup_type => 'BEN_PYMT_STAT',
961: p_lookup_code => p_pymt_stat_cd,
962: p_effective_date => p_effective_date) then
963: --
1061: end if ;
1062:
1063: if (l_api_updating
1064: and p_prtt_reimbmt_rqst_stat_cd
1065: <> nvl(ben_prc_shd.g_old_rec.prtt_reimbmt_rqst_stat_cd,hr_api.g_varchar2)
1066: or not l_api_updating)
1067: and p_prtt_reimbmt_rqst_stat_cd is not null then
1068: --
1069: -- check if value of lookup falls within lookup type.
1067: and p_prtt_reimbmt_rqst_stat_cd is not null then
1068: --
1069: -- check if value of lookup falls within lookup type.
1070: --
1071: if hr_api.not_exists_in_hr_lookups
1072: (p_lookup_type => 'BEN_REIMBMT_RQST_STAT',
1073: p_lookup_code => p_prtt_reimbmt_rqst_stat_cd,
1074: p_effective_date => p_effective_date) then
1075: --
1213:
1214: -- Check the UOM value
1215: if (l_api_updating
1216: and p_rqst_amt_uom
1217: <> nvl(ben_prc_shd.g_old_rec.rqst_amt_uom,hr_api.g_varchar2)
1218: or not l_api_updating)
1219: and p_rqst_amt_uom is not null then
1220: --
1221: -- check if value of lookup falls within lookup type.
1275: -- not sufficient then the status is changed but in case of balance
1276: --- payment status is changed
1277: --if (l_api_updating
1278: -- and p_rqst_amt
1279: -- <> nvl(ben_prc_shd.g_old_rec.rqst_amt,hr_api.g_number)
1280: -- or not l_api_updating)
1281: -- and p_rqst_amt is not null then
1282:
1283: -- if the plan's CMPR_CLMS_TO_CVG_OR_BAL_CD is cvg, the amt
1734: and pen.person_id = p_submitter_person_id
1735: and cpy.yr_perd_id = yrp.yr_perd_id
1736: and pen.prtt_enrt_rslt_stat_cd is null
1737: and pen_l.prtt_enrt_rslt_stat_cd is null
1738: and pen.effective_end_date = hr_api.g_eot
1739: and pen_l.effective_end_date = hr_api.g_eot
1740: and p_exp_incurd_dt >= yrp.start_date
1741: and p_exp_incurd_dt <= nvl(cpy.PY_CLMS_THRU_DT, yrp.end_date)
1742: and pen.pl_id = pen_l.pl_id
1735: and cpy.yr_perd_id = yrp.yr_perd_id
1736: and pen.prtt_enrt_rslt_stat_cd is null
1737: and pen_l.prtt_enrt_rslt_stat_cd is null
1738: and pen.effective_end_date = hr_api.g_eot
1739: and pen_l.effective_end_date = hr_api.g_eot
1740: and p_exp_incurd_dt >= yrp.start_date
1741: and p_exp_incurd_dt <= nvl(cpy.PY_CLMS_THRU_DT, yrp.end_date)
1742: and pen.pl_id = pen_l.pl_id
1743: and pen.person_id = pen_l.person_id
1765: and pen2.prtt_enrt_rslt_stat_cd is null
1766: and pen2.SSPNDD_FLAG = 'N'
1767: and pen2.enrt_cvg_strt_dt <= yrp.end_date
1768: and pen2.enrt_cvg_thru_dt >= yrp.start_date
1769: and pen2.effective_end_date = hr_api.g_eot
1770: )
1771: and pen_l.prtt_enrt_rslt_id =
1772: (select min(pen_l2.prtt_enrt_rslt_id)
1773: from ben_prtt_enrt_rslt_f pen_l2
1776: and pen_l2.SSPNDD_FLAG = 'N'
1777: and pen_l2.prtt_enrt_rslt_stat_cd is null
1778: and pen_l2.enrt_cvg_strt_dt <= yrp.end_date
1779: and pen_l2.enrt_cvg_thru_dt >= yrp.start_date
1780: and pen_l.effective_end_date = hr_api.g_eot
1781: )
1782: ;
1783: --
1784: -- This cursor will check if the incrd_to_dt is less than the earliest submission date for
1862: p_effective_date => p_effective_date,
1863: p_object_version_number => p_object_version_number);
1864: --
1865: if ((l_api_updating
1866: and (nvl(p_incrd_from_dt, hr_api.g_date)
1867: <> nvl(ben_prc_shd.g_old_rec.incrd_from_dt, hr_api.g_date)
1868: or nvl(p_incrd_to_dt, hr_api.g_date)
1869: <> nvl(ben_prc_shd.g_old_rec.incrd_to_dt, hr_api.g_date)
1870: or nvl(p_exp_incurd_dt, hr_api.g_date)
1863: p_object_version_number => p_object_version_number);
1864: --
1865: if ((l_api_updating
1866: and (nvl(p_incrd_from_dt, hr_api.g_date)
1867: <> nvl(ben_prc_shd.g_old_rec.incrd_from_dt, hr_api.g_date)
1868: or nvl(p_incrd_to_dt, hr_api.g_date)
1869: <> nvl(ben_prc_shd.g_old_rec.incrd_to_dt, hr_api.g_date)
1870: or nvl(p_exp_incurd_dt, hr_api.g_date)
1871: <> nvl(ben_prc_shd.g_old_rec.exp_incurd_dt, hr_api.g_date)
1864: --
1865: if ((l_api_updating
1866: and (nvl(p_incrd_from_dt, hr_api.g_date)
1867: <> nvl(ben_prc_shd.g_old_rec.incrd_from_dt, hr_api.g_date)
1868: or nvl(p_incrd_to_dt, hr_api.g_date)
1869: <> nvl(ben_prc_shd.g_old_rec.incrd_to_dt, hr_api.g_date)
1870: or nvl(p_exp_incurd_dt, hr_api.g_date)
1871: <> nvl(ben_prc_shd.g_old_rec.exp_incurd_dt, hr_api.g_date)
1872: or nvl(p_rqst_amt,0) <> nvl(ben_prc_shd.g_old_rec.rqst_amt,0)
1865: if ((l_api_updating
1866: and (nvl(p_incrd_from_dt, hr_api.g_date)
1867: <> nvl(ben_prc_shd.g_old_rec.incrd_from_dt, hr_api.g_date)
1868: or nvl(p_incrd_to_dt, hr_api.g_date)
1869: <> nvl(ben_prc_shd.g_old_rec.incrd_to_dt, hr_api.g_date)
1870: or nvl(p_exp_incurd_dt, hr_api.g_date)
1871: <> nvl(ben_prc_shd.g_old_rec.exp_incurd_dt, hr_api.g_date)
1872: or nvl(p_rqst_amt,0) <> nvl(ben_prc_shd.g_old_rec.rqst_amt,0)
1873: or nvl(p_prtt_reimbmt_rqst_stat_cd ,'-1')<>
1866: and (nvl(p_incrd_from_dt, hr_api.g_date)
1867: <> nvl(ben_prc_shd.g_old_rec.incrd_from_dt, hr_api.g_date)
1868: or nvl(p_incrd_to_dt, hr_api.g_date)
1869: <> nvl(ben_prc_shd.g_old_rec.incrd_to_dt, hr_api.g_date)
1870: or nvl(p_exp_incurd_dt, hr_api.g_date)
1871: <> nvl(ben_prc_shd.g_old_rec.exp_incurd_dt, hr_api.g_date)
1872: or nvl(p_rqst_amt,0) <> nvl(ben_prc_shd.g_old_rec.rqst_amt,0)
1873: or nvl(p_prtt_reimbmt_rqst_stat_cd ,'-1')<>
1874: nvl(ben_prc_shd.g_old_rec.prtt_reimbmt_rqst_stat_cd ,'-1')
1867: <> nvl(ben_prc_shd.g_old_rec.incrd_from_dt, hr_api.g_date)
1868: or nvl(p_incrd_to_dt, hr_api.g_date)
1869: <> nvl(ben_prc_shd.g_old_rec.incrd_to_dt, hr_api.g_date)
1870: or nvl(p_exp_incurd_dt, hr_api.g_date)
1871: <> nvl(ben_prc_shd.g_old_rec.exp_incurd_dt, hr_api.g_date)
1872: or nvl(p_rqst_amt,0) <> nvl(ben_prc_shd.g_old_rec.rqst_amt,0)
1873: or nvl(p_prtt_reimbmt_rqst_stat_cd ,'-1')<>
1874: nvl(ben_prc_shd.g_old_rec.prtt_reimbmt_rqst_stat_cd ,'-1')
1875: or nvl(p_pl_id,-1) <> nvl(ben_prc_shd.g_old_rec.pl_id,-1) ))
2071: hr_utility.set_location('Entering:'||l_proc, 5);
2072: --
2073: -- Ensure that the p_datetrack_mode argument is not null
2074: --
2075: hr_api.mandatory_arg_error
2076: (p_api_name => l_proc,
2077: p_argument => 'datetrack_mode',
2078: p_argument_value => p_datetrack_mode);
2079: --
2083: --
2084: --
2085: -- Ensure the arguments are not null
2086: --
2087: hr_api.mandatory_arg_error
2088: (p_api_name => l_proc,
2089: p_argument => 'validation_start_date',
2090: p_argument_value => p_validation_start_date);
2091: --
2088: (p_api_name => l_proc,
2089: p_argument => 'validation_start_date',
2090: p_argument_value => p_validation_start_date);
2091: --
2092: hr_api.mandatory_arg_error
2093: (p_api_name => l_proc,
2094: p_argument => 'validation_end_date',
2095: p_argument_value => p_validation_end_date);
2096: --
2093: (p_api_name => l_proc,
2094: p_argument => 'validation_end_date',
2095: p_argument_value => p_validation_end_date);
2096: --
2097: If ((nvl(p_pl_id, hr_api.g_number) <> hr_api.g_number) and
2098: NOT (dt_api.check_min_max_dates
2099: (p_base_table_name => 'ben_pl_f',
2100: p_base_key_column => 'pl_id',
2101: p_base_key_value => p_pl_id,
2210: hr_utility.set_location('Entering:'||l_proc, 5);
2211: --
2212: -- Ensure that the p_datetrack_mode argument is not null
2213: --
2214: hr_api.mandatory_arg_error
2215: (p_api_name => l_proc,
2216: p_argument => 'datetrack_mode',
2217: p_argument_value => p_datetrack_mode);
2218: --
2224: --
2225: --
2226: -- Ensure the arguments are not null
2227: --
2228: hr_api.mandatory_arg_error
2229: (p_api_name => l_proc,
2230: p_argument => 'validation_start_date',
2231: p_argument_value => p_validation_start_date);
2232: --
2229: (p_api_name => l_proc,
2230: p_argument => 'validation_start_date',
2231: p_argument_value => p_validation_start_date);
2232: --
2233: hr_api.mandatory_arg_error
2234: (p_api_name => l_proc,
2235: p_argument => 'validation_end_date',
2236: p_argument_value => p_validation_end_date);
2237: --
2234: (p_api_name => l_proc,
2235: p_argument => 'validation_end_date',
2236: p_argument_value => p_validation_end_date);
2237: --
2238: hr_api.mandatory_arg_error
2239: (p_api_name => l_proc,
2240: p_argument => 'prtt_reimbmt_rqst_id',
2241: p_argument_value => p_prtt_reimbmt_rqst_id);
2242: --
2280: --
2281: -- Call all supporting business operations
2282: --
2283: --
2284: hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
2285: --
2286: chk_prtt_reimbmt_rqst_id
2287: (p_prtt_reimbmt_rqst_id => p_rec.prtt_reimbmt_rqst_id,
2288: p_effective_date => p_effective_date,
2412: -- Call all supporting business operations
2413: --
2414: --
2415:
2416: hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
2417: --
2418: chk_prtt_reimbmt_rqst_id
2419: (p_prtt_reimbmt_rqst_id => p_rec.prtt_reimbmt_rqst_id,
2420: p_effective_date => p_effective_date,
2610: hr_utility.set_location('Entering:'|| l_proc, 10);
2611: --
2612: -- Ensure that all the mandatory parameter are not null
2613: --
2614: hr_api.mandatory_arg_error(p_api_name => l_proc,
2615: p_argument => 'prtt_reimbmt_rqst_id',
2616: p_argument_value => p_prtt_reimbmt_rqst_id);
2617: --
2618: open csr_leg_code;