[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
14-Feb-2000 stee 115.69 - Added check when selecting
cobra qualified beneficiary
to ignore backed out nocopy event.
bug# 1178633.
18-Feb-2000 mhoyes 115.70 - Fixed bugs 4707 and 4708.
Synched up nullification
of LOS and AGE values and
UOMs.
22-Feb-2000 gperry 115.71 Fixed WWBUG 1118118.
23-Feb-00 tguy 115.72 Fixed WWBUG 1178659,1161287,1120685
23-Feb-00 gperry 115.73 Fixed WWBUG 1118113.
26-Feb-00 mhoyes 115.74 - Added p_comp_obj_tree_row parameter
to derive_rates_and_factors.
- Phased out nocopy ben_env_object for comp
object values.
28-Feb-00 stee 115.75 - Added p_cbr_tmprl_evt_flag
parameter.
28-Feb-00 tguy 115.76 Fixed WWBUG 1179545.
03-Mar-00 gperry 115.77 Fixed bugs caused by 115.76
04-Mar-00 stee 115.78 Added ptip_id to
determine_cobra_eligibility.
COBRA by plan type.
07-Mar-00 tguy 115.79 Fixed Inherited codes in LOS
determination
07-Mar-00 gperry 115.80 Fixed WWBUG 1195803.
09-Mar-00 gperry 115.81 Added flag bit val for
performance.
23-Mar-00 gperry 115.82 Added in rate derivation for
coverages and premiums.
24-Mar-00 gperry 115.83 Added in handling for min and
max cases where the min is null
or max is null i.e. no max or
no min flag has been set.
Fix for WWBUG 1173013.
31-Mar-00 gperry 115.84 Added oiplip support.
04-Apr-00 mmogel 115.85 Added a token to message
BEN_91340_CREATE_PTNL_LER
05-Apr-00 stee 115.86 COBRA: get program type
is program id is passed in.
06-Apr-00 lmcdonal 115.87 debugging messages.
06-Apr-00 gperry 115.88 Return the comp_rec and the
oiplip_rec when no derivable
factors exists. (1169423)
13-Apr-00 pbodla 115.89 - Bug 5093 : p_ntfn_dt populated
when the potential le is created.
17-Apr-00 stee 115.90 - Trigger a period of
enrollment change event
when person is disabled at the
time of the qualifying event.
wwbug(1274212).
03-May-00 stee 115.91 - Trigger the voluntary end
of coverage event 2 days later
if an event exist on the day
after the cobra eligibiliy end
date wwbug(1274211).
22-May-00 mhoyes 115.92 - Added profiling messages.
06-Jun-00 stee 115.93 - Trigger non-late payment event
for cobra by plan type.
Bug 5261.
14-Jun-00 stee 115.94 - Use the system date to Trigger
the cobra ineligible to
participate event if this
process is run ahead of time.
bug #5263.
20-Jun-00 stee 115.95 - Check derived factors based on
a code selected by the user.
Split cobra events into
payments and non-payment events.
27-Jun-00 mhoyes 115.96 - Removed nvls from c_elig_per_opt.
- Reduced sysdate references.
- Cached c_elig_per_opt for oiplips
and plan in programs.
- Cached c_elig_per for plips
and plan in programs.
27-Jun-00 gperry 115.97 Added age_calc_rl
28-Jun-00 stee 115.98 COBRA: for first payment,
check that the person paid
within 45 days i.e. the due
date is >= the date earned
(date payment made).
29-Jun-00 mhoyes 115.99 - Fixed numeric or value error
problem for contacts who have no
assignments.
- Added context parameters.
06-Jul-00 mhoyes 115.100 - Fixed null assignment id
problem from 115.99.
10-Jul-00 mhoyes 115.101 - p_oiplip_rec problem in
cache_data_structures.
- Passed in person context row.
03-Aug-00 dharris 115.102 - modified create_ptl_ler to
get the g_temp_ler_id instead of
calling the fuction
get_temporal_ler_id
- Removed get_temporal_ler_id.
18-Aug-00 jcarpent 115.103 - Fixed formula context to
los_dt_to_use_rl.
Tar 1052406.996.
19-Aug-00 jcarpent 115.104 - 1385506. (same as above bug)
but ptip level was not working.
added cursor to run_rule.
05-Sep-00 rchase 115.105 - Included person_id as an input
to formual calls. This resolves
issues when processing dependents
without assignment_ids. 1396949.
14-SEP-00 gperry 115.106 Fixed bug 1237211 where combo
age and los was not working.
14-SEP-00 gperry 115.107 Fixed comp error.
11-OCT-00 rchase 115.108 - Added the parameter pl typ id
for context passing to underlying
formula calls.
06-NOV-00 rchase 115.109 - Added parameters to cvg and prem cache
calls to trigger lf_evts.
Bug 1433338 + 1350957.
17-jan-01 tilak 115.110 derived facor calidation changed from
< max to < max +1
06-Apr-01 mhoyes 115.111 - Added p_calculate_only_mode for EFC.
27-Aug-01 ikasire 115.113 Bug 1949361 fixes
05-Sep-01 ikasire 115.114 Bug 1927010 Fixes
1. calculate_age is modified completely.
The existing process was checking
the following execution order for
derived factors in the rt_age_val
calculation process.
Rate->Coverage->Premium.
When we found a df at rate and even if
doesn't cross the boundary, we are then
ignoring the dfs defined at Coverage and
Premiums. Similary if there is one
defined at coverage level, the process
ignores the dfs defined at premium level.
-- This is now fixed.
2. age determination Code AFDCM.
We were adding a month for the derived date
in certain cases. Now that condition has
been removed as it is not correct.
3.We need to fix other procedures also,
see bug for more details.
10-Sep-01 ikasire 115.115 Bug 1977901 fixing the process as noted in
115.114 modifications for the following
derived factors.
1. Length of Service
2. Compensation Level
3. Combined age and los
4. Percent Full time
5. Hours Worked
Added the following new private procedures
comp_level_min_max, percent_fulltime_min_max and
hours_worked_min_max
13-Sep-01 ikasire 115.116 Bug 1977901 to avoid getting the salary from
the future records chages are made to the
cursor in procedure - get_persons_salary
18-Sep-01 ikasire 115.117 Bug 1977901 fixed the percent full time parttime
09-Oct-01 kmahendr 115.118 Bug#2034617 - wrong assignment value fixed at line
p_comp_rec.rt_age_uom - l_rate_prem_rec.age_uom
17-Nov-01 ikasire 115.119 Bug2101937 fixed the calls to coverage
and premium routines in calculate_age
procedures.
03-Dec-2001 ikasire 115.120 Bug 2101937 changed the group function min to max
in the four cursors of get_salary_date function,
as we always want only the record changed recently.
Also added ppp.change_date <= p_effective_date
to avoid getting the future dated salary rows.
06-dec-01 tjesumic 115.121 Salary calcualtion date determination changed ,
bug 2124453, first look for date of code
then for join date then effective date
07-dec-01 tjesumic 115.122 dbdrv fixed
12-dec-01 tjesumic 115.123 changed the condition to ppp.approved='Y'
in cursor c1 to fetch approved salary
16-dec-01 tjesumic 115.124 cwb changes
20-dec-01 ikasire 115.125 Bug 2145966 formula type hours worked not
triggering temporal life event
added code for rule in calculate_hours_worked
09-jan-02 tjesumic 115.126 bug 2169319 Salary calcualtion date determination changed
30-jan-02 tjesumic 115.127 bug 2180602 new procedure added to set the tax_unit_id
context before calling get_value
01-feb-02 tjesumic 115.128 dbdrv fixed
14-Mar-02 pabodla 115.129 UTF8 Changes Bug 2254683
03-Jun-02 pabodla 115.130 Bug 2367556 : Changed STANDARD.bitand to just bitand
08-Jun-02 pabodla 115.131 Do not select the contingent worker
assignment when assignment data is
fetched.
19-Jun-02 ikasire 115.132 In call to to comp_level_min_max for Coverage
l_rate_result was passed instead of
passing l_rate_cvg_result
which results in passing null to new_val
08-Oct-02 kmahendr 115.133 Bug#2613307-added codes in calculate_los proc.
18-Oct-02 kmahendr 115.134 Added to codes in other calculate factors
22-Oct-02 ikasire 115.135 Bug 2502763 Changes to get_salary_date function
to compute with right boundaries.
Added parameter to comp_level_min_max.
Removed the calls to hr_ and
using the call to
BEN_DERIVE_FACTORS.determine_compensation to
determine compensation.
31-Mar-02 pbodla/ 115.138 Bug 2881136 Pass formula id in
ikasire min_max_breach routine. Also
pass correct rule id while
calling min_max_breach.
14-Apr-03 kmahendra 115.139 Bug#2507053 - the condition to check min_max breach
for only Person is removed in age_calculation.
08-may-2003 nhunur 115.40 Bug - 2946985 passed the oipl_id retrieved from the oiplip record
structure to ben_derive_factors.determine_compensation call.
01-jul-03 pabodla 115.141 Grade/Step Added code, variables
to support grade/step life event
triggering.
28-aug-03 rpillay 115.142 Bug 3097501 - Cobra - Changed
cursors getting payment and
amount due to sum up values
in determine_cobra_payments
02-sep-03 rpillay 115.143 Bug 3097501- changed l_pymt_amt
to data type number in
determine_cobra_payments
03-sep-03 rpillay 115.144 Bug 3125085 - check if all dues
upto previous month have been paid
25-Sep-03 rpillay 115.145 Bug 3097501 - Changes to make
NOLP work for all payrolls
03-Oct-03 ikasire 115.146 Bug 3174453 we need to pass pgm/pl/oipl
to ben_derive_factors.determine_compensation
13-Oct-03 rpillay 115.147 Bug 3097501 - Changes to handle FSA rates
and rounding issues
15-Oct-03 rpillay 115.148 Bug 3097501 - Changes to handle enrollment
and rate changes
22-Oct-03 rpillay 115.149 Bug 3097501 - Added p_element_entry_value_id
in call to get_amount_due
29-Oct-03 rpillay 115.150 Bug 3097501 - Changes to
determine_cobra_payments to not
check for payments not yet due
04-Nov-03 rpillay 115.151 Bug 3235738 - Undo changes made
for Bug 3097501 for PF.G
11-Nov-03 ikasire 115.152 Using filter g_no_ptnl_ler_id for
not to trigger potentials as part of
Unrestricted U,W,M,I,P,A BUG 3243960
11-Nov-03 rpillay 115.153 Added back changes for Bug 3097501
(from v115.150)
13-Nov-03 rpillay 115.154 Bug 3097501 - Changes for insignificant
underpayments
01-Dec-03 rpillay 115.155 Bug 3097501 -Changed DFF context
to 'BENEFIT UNDERPAY' in cursor
c_allwd_underpymt
01-Dec-03 kmahendr 115.156 Bug#3274130 - added date condition to
cursor c_per_spouse.
02-Dec-03 ikasire 115.157 Bug 3291639 temporal not detected for
combined LOS and Age derived factor
05-Dec-03 ikasire 115.158 Bug 3275501 New Code introduced to supress
firing of temporals - IGNRALL
19-Jan-04 rpillay 115.159 Bug 3097501 - Set LE Ocrd Date to COBRA
Due Date when triggering NOLP for first
payment
17-Dec-03 vvprabhu 115.32 Added the assignment for g_debug at the start
of each public procedure
05-Jan-04 ikasire 115.161 Bug 3275501 Added new Code IGNRTHIS
Never to detect a potential for
potential life event
11-mar-04 nhunur 115.162 added business_group_id clause for c_get_gsp_ler
09-apr-04 ikasire 115.163 fonm changes
16-apr-04 ikasire 115.164 more fonm changes
05-Aug-04 tjesumic 115.165 fonm changes
16-Aug-04 tjesumic 115.167 fonm changes
27-Sep-04 tjesumic 115.168 new param p_cvrd_today added in chk_enrld_or_cvrd
12-Oct04 nhunur 115.169 pl_id needs to be passed as context for derived factor rules
based elpros set at plip,oipl levels. Bug - 3944795
21-Oct-04 bmanyam 115.170 Bug: 3962514, In los_calculation
for coverages l_rate_cvg_result is
passed to min_max_breach() as parameter
[ previously l_rate_result was
passed, as a result temporal was not getting deducted ].
26-oct-04 pbodla 115.171 Merging the code from version
115.161.11510.5. As pkh have the function
get_latest_paa_id
mmudigon Bug 3818453. Added funcion
get_latest_paa_id()
27-oct-04 nhunur 115.173 moved get_latest_paa_id() to the top.
07-apr-05 nhunur 115.174 apply fnd_number on what FF returns in run_rule.
24-May-2004 bmanyam 115.175 BUG: 4380180. IF l_lf_evt_ocrd_dt IS NULL,
avoid determining the date
08-Jun-05 kmahendr 115.176 Bug#4393676 - nvl added to old value in
hoursworked min/max breach
12-Dec-05 stee 115.177 Bug#4338471 - COBRA: get the most recent
enrollment period when evaluating
loss of eligibility event.
28-Mar-06 kmahendr 115.178 Bug#5044005 - recompute lf_evt_ocr_dt
if the code is AFDECY
24-Apr-06 abparekh 115.179 No changes - ver same as 178
17-Jul-06 abparekh 115.180 Bug 5392019 : For LOS Date to use codes like 'Inherited%'
use benefits assignment
25-Aug-06 swjain 115.181 Bug 5478918 : Added function skip_min_max_le_calc and called
it from different calculate procedures. If true, then all the min
max calculations and LE creation would be skipped.
09-Jan-07 stee 115.182 Bug 5731828: If date determination
code is 'End of Calendar Year'(ALDECLY').
Trigger the event as of January 1.
28-Apr-09 stee 115.183 ARRA COBRA changes.
*/
--------------------------------------------------------------------------------
--
--
g_package VARCHAR2(80) := 'ben_derive_part_and_rate_facts';
select paa.assignment_action_id
from pay_assignment_actions paa,
per_all_assignments_f paf,
pay_payroll_actions ppa,
pay_action_classifications pac
where paf.person_id = p_person_id
and paa.assignment_id = paf.assignment_id
and paa.tax_unit_id = l_tax_unit_id
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.action_type = pac.action_type
and pac.classification_name = 'SEQUENCED'
and ppa.effective_date between paf.effective_start_date
and paf.effective_end_date
and ppa.effective_date <= p_effective_date
and ((nvl(paa.run_type_id, ppa.run_type_id) is null
and paa.source_action_id is null)
or (nvl(paa.run_type_id, ppa.run_type_id) is not null
and paa.source_action_id is not null )
or (ppa.action_type = 'V' and ppa.run_type_id is null
and paa.run_type_id is not null
and paa.source_action_id is null))
order by ppa.effective_date desc,paa.action_sequence desc;
SELECT ler.name,
ler.ptnl_ler_trtmt_cd
FROM ben_ler_f ler
WHERE ler.ler_id = p_ler_id
AND ler.business_group_id = p_business_group_id
AND p_effective_date BETWEEN ler.effective_start_date
AND ler.effective_end_date;
select pl_typ_id
from ben_ptip_f
where ptip_id=p_ptip_id and
business_group_id=p_business_group_id and
p_effective_date between
effective_start_date and effective_end_date;
select pl.pl_typ_id , pl.pl_id
from ben_plip_f plip,
ben_pl_f pl
where plip.plip_id=p_plip_id and
plip.business_group_id=p_business_group_id and
p_effective_date between
plip.effective_start_date and plip.effective_end_date
and pl.pl_id = plip.pl_id and
pl.business_group_id=p_business_group_id and
p_effective_date between
pl.effective_start_date and pl.effective_end_date;
select pl.pl_typ_id , pl.pl_id
from ben_oipl_f oipl,
ben_pl_f pl
where oipl.oipl_id=p_oipl_id and
oipl.business_group_id=p_business_group_id and
p_effective_date between
oipl.effective_start_date and oipl.effective_end_date
and pl.pl_id = oipl.pl_id and
pl.business_group_id=p_business_group_id and
p_effective_date between
pl.effective_start_date and pl.effective_end_date;
select pl.pl_typ_id , pl.pl_id
from ben_oiplip_f oiplip,
ben_oipl_f oipl,
ben_pl_f pl
where oiplip.oiplip_id=p_oiplip_id and
oiplip.business_group_id=p_business_group_id and
p_effective_date between
oiplip.effective_start_date and oiplip.effective_end_date
and oipl.oipl_id = oiplip.oipl_id and
oipl.business_group_id=p_business_group_id and
p_effective_date between
oipl.effective_start_date and oipl.effective_end_date
and pl.pl_id = oipl.pl_id and
pl.business_group_id=p_business_group_id and
p_effective_date between
pl.effective_start_date and pl.effective_end_date;
select pl_typ_id
from ben_pl_f
where pl_id=p_pl_id and
business_group_id=p_business_group_id and
p_effective_date between
effective_start_date and effective_end_date;
SELECT epo.los_val
,epo.age_val
,epo.comp_ref_amt
,epo.hrs_wkd_val
,epo.pct_fl_tm_val
,epo.cmbn_age_n_los_val
,epo.age_uom
,epo.los_uom
,epo.comp_ref_uom
,epo.hrs_wkd_bndry_perd_cd
,epo.frz_los_flag
,epo.frz_age_flag
,epo.frz_hrs_wkd_flag
,epo.frz_cmp_lvl_flag
,epo.frz_pct_fl_tm_flag
,epo.frz_comb_age_and_los_flag
,epo.rt_los_val
,epo.rt_age_val
,epo.rt_comp_ref_amt
,epo.rt_hrs_wkd_val
,epo.rt_pct_fl_tm_val
,epo.rt_cmbn_age_n_los_val
,epo.rt_age_uom
,epo.rt_los_uom
,epo.rt_comp_ref_uom
,epo.rt_hrs_wkd_bndry_perd_cd
,epo.rt_frz_los_flag
,epo.rt_frz_age_flag
,epo.rt_frz_hrs_wkd_flag
,epo.rt_frz_cmp_lvl_flag
,epo.rt_frz_pct_fl_tm_flag
,epo.rt_frz_comb_age_and_los_flag
,epo.ovrid_svc_dt
,epo.prtn_ovridn_flag
,epo.prtn_ovridn_thru_dt
,NULL
,NULL
,NULL
,NULL
,epo.once_r_cntug_cd
,epo.elig_flag
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
FROM ben_elig_per_opt_f epo, ben_elig_per_f pep, ben_per_in_ler pil
WHERE epo.elig_per_id = pep.elig_per_id
AND pep.person_id = c_person_id
AND NVL(pep.pl_id
,-1) = c_pl_id
AND NVL(pep.plip_id
,-1) = c_plip_id
AND NVL(pep.pgm_id
,-1) = c_pgm_id
AND c_effective_date BETWEEN pep.effective_start_date
AND pep.effective_end_date
AND epo.opt_id = c_opt_id
AND c_effective_date BETWEEN epo.effective_start_date
AND epo.effective_end_date
AND pil.per_in_ler_id (+) = epo.per_in_ler_id
-- AND pil.business_group_id (+) = epo.business_group_id
AND (
pil.per_in_ler_stat_cd NOT IN ('VOIDD', 'BCKDT')
OR pil.per_in_ler_stat_cd IS NULL);
SELECT pep.los_val
,pep.age_val
,pep.comp_ref_amt
,pep.hrs_wkd_val
,pep.pct_fl_tm_val
,pep.cmbn_age_n_los_val
,pep.age_uom
,pep.los_uom
,pep.comp_ref_uom
,pep.hrs_wkd_bndry_perd_cd
,pep.frz_los_flag
,pep.frz_age_flag
,pep.frz_hrs_wkd_flag
,pep.frz_cmp_lvl_flag
,pep.frz_pct_fl_tm_flag
,pep.frz_comb_age_and_los_flag
,pep.rt_los_val
,pep.rt_age_val
,pep.rt_comp_ref_amt
,pep.rt_hrs_wkd_val
,pep.rt_pct_fl_tm_val
,pep.rt_cmbn_age_n_los_val
,pep.rt_age_uom
,pep.rt_los_uom
,pep.rt_comp_ref_uom
,pep.rt_hrs_wkd_bndry_perd_cd
,pep.rt_frz_los_flag
,pep.rt_frz_age_flag
,pep.rt_frz_hrs_wkd_flag
,pep.rt_frz_cmp_lvl_flag
,pep.rt_frz_pct_fl_tm_flag
,pep.rt_frz_comb_age_and_los_flag
,pep.ovrid_svc_dt
,pep.prtn_ovridn_flag
,pep.prtn_ovridn_thru_dt
,NULL
,NULL
,NULL
,NULL
,pep.once_r_cntug_cd
,pep.elig_flag
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
FROM ben_elig_per_f pep, ben_per_in_ler pil
WHERE pep.person_id = c_person_id
AND NVL(pep.pl_id
,-1) = c_pl_id
AND NVL(pep.plip_id
,-1) = c_plip_id
AND pep.ptip_id IS NULL
AND NVL(pep.pgm_id
,-1) = c_pgm_id
AND c_effective_date BETWEEN pep.effective_start_date
AND pep.effective_end_date
AND pil.per_in_ler_id (+) = pep.per_in_ler_id
-- AND pil.business_group_id (+) = pep.business_group_id
AND (
pil.per_in_ler_stat_cd NOT IN ('VOIDD', 'BCKDT')
OR pil.per_in_ler_stat_cd IS NULL);
SELECT MIN(pbb.effective_start_date)
FROM ben_per_bnfts_bal_f pbb
WHERE pbb.val >= p_min
AND pbb.bnfts_bal_id = p_bnfts_bal_id
AND p_effective_date BETWEEN pbb.effective_start_date
AND pbb.effective_end_date
AND pbb.person_id = p_person_id;
SELECT MIN(pbb.effective_start_date)
FROM ben_per_bnfts_bal_f pbb
WHERE pbb.val > p_max
AND pbb.bnfts_bal_id = p_bnfts_bal_id
AND p_effective_date BETWEEN pbb.effective_start_date
AND pbb.effective_end_date
AND pbb.person_id = p_person_id;
SELECT MIN(pbb.effective_start_date)
FROM ben_per_bnfts_bal_f pbb
WHERE pbb.val <= p_max
AND pbb.bnfts_bal_id = p_bnfts_bal_id
AND p_effective_date BETWEEN pbb.effective_start_date
AND pbb.effective_end_date
AND pbb.person_id = p_person_id;
SELECT MIN(pbb.effective_start_date)
FROM ben_per_bnfts_bal_f pbb
WHERE pbb.val < p_min
AND pbb.bnfts_bal_id = p_bnfts_bal_id
AND p_effective_date BETWEEN pbb.effective_start_date
AND pbb.effective_end_date
AND pbb.person_id = p_person_id;
select ppb.pay_basis,
ppb.pay_annualization_factor,
asg.normal_hours,
asg.frequency,
asg.assignment_id
from per_all_assignments_f asg,
per_pay_bases ppb
where asg.assignment_type <> 'C'
and asg.assignment_id = v_assignment_id
and ppb.pay_basis_id = asg.pay_basis_id
and v_effective_date
between asg.effective_start_date
and asg.effective_end_date
order by asg.assignment_id;
select opt.OPT_TYP_CD
from BEN_PL_F pln, BEN_PL_TYP_f opt
where opt.pl_typ_id = pln.pl_typ_id
and opt.OPT_TYP_CD = 'CWB'
and v_effective_date
between pln.effective_start_date
and pln.effective_end_date
and v_effective_date
between opt.effective_start_date
and opt.effective_end_date;
SELECT MAX(ppp.change_date)
FROM per_pay_proposals ppp, per_all_assignments_f paf
WHERE paf.assignment_id = l_ass_rec.assignment_id
and paf.assignment_type <> 'C'
AND p_effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND paf.assignment_id = ppp.assignment_id
AND ppp.change_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND ppp.proposed_salary_n >= l_output -- p_min
AND ppp.change_date <= p_effective_date ;
SELECT MAX(ppp.change_date)
FROM per_pay_proposals ppp, per_all_assignments_f paf
WHERE paf.assignment_id = l_ass_rec.assignment_id
and paf.assignment_type <> 'C'
AND p_effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND paf.assignment_id = ppp.assignment_id
AND ppp.change_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND ppp.proposed_salary_n > l_output -- p_max
AND ppp.change_date <= p_effective_date;
SELECT MAX(ppp.change_date)
FROM per_pay_proposals ppp, per_all_assignments_f paf
WHERE paf.assignment_id = l_ass_rec.assignment_id
and paf.assignment_type <> 'C'
AND p_effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND paf.assignment_id = ppp.assignment_id
AND ppp.change_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND ppp.proposed_salary_n <= l_output -- p_max
AND ppp.change_date <= p_effective_date;
SELECT MAX(ppp.change_date)
FROM per_pay_proposals ppp, per_all_assignments_f paf
WHERE paf.assignment_id = l_ass_rec.assignment_id
and paf.assignment_type <> 'C'
AND p_effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND paf.assignment_id = ppp.assignment_id
AND ppp.change_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND ppp.proposed_salary_n < l_output -- p_min
AND ppp.change_date <= p_effective_date;
SELECT MIN(pab.effective_start_date)
FROM per_assignment_budget_values_f pab
WHERE pab.assignment_id = l_ass_rec.assignment_id
AND pab.effective_start_date BETWEEN l_ass_rec.effective_start_date
AND l_ass_rec.effective_end_date
AND pab.unit = 'FTE'
AND pab.VALUE >= p_min;
SELECT MIN(pab.effective_start_date)
FROM per_assignment_budget_values_f pab
WHERE pab.assignment_id = l_ass_rec.assignment_id
AND pab.effective_start_date BETWEEN l_ass_rec.effective_start_date
AND l_ass_rec.effective_end_date
AND pab.unit = 'FTE'
AND pab.VALUE > p_max;
SELECT MIN(pab.effective_start_date)
FROM per_assignment_budget_values_f pab
WHERE pab.assignment_id = l_ass_rec.assignment_id
AND pab.effective_start_date BETWEEN l_ass_rec.effective_start_date
AND l_ass_rec.effective_end_date
AND pab.unit = 'FTE'
AND pab.VALUE <= p_max;
SELECT MIN(pab.effective_start_date)
FROM per_assignment_budget_values_f pab
WHERE pab.assignment_id = l_ass_rec.assignment_id
AND pab.effective_start_date BETWEEN l_ass_rec.effective_start_date
AND l_ass_rec.effective_end_date
AND pab.unit = 'FTE'
AND pab.VALUE < p_min;
SELECT ppp.proposed_salary_n
FROM per_pay_proposals ppp, per_all_assignments_f paf
WHERE paf.assignment_id = l_ass_rec.assignment_id
and paf.assignment_type <> 'C'
AND paf.business_group_id = p_business_group_id
AND p_effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND paf.assignment_id = ppp.assignment_id
AND paf.business_group_id = ppp.business_group_id
AND ppp.change_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
--AND ppp.approved IN ('Y', 'A', 'P')
--approved is check box accpet Y/N
and nvl(ppp.approved,'N') = 'Y'
-- Bug 1977901 added the following condition
-- otherwise we get the future salaries also which we should not do
AND ppp.change_date <= p_effective_date
ORDER BY ppp.change_date DESC;
SELECT ler.name,
ler.ptnl_ler_trtmt_cd
FROM ben_ler_f ler
WHERE ler.ler_id = p_ler_id
AND ler.business_group_id = p_business_group_id
AND p_effective_date BETWEEN ler.effective_start_date
AND ler.effective_end_date;
,p_program_update_date => l_sysdate
,p_ntfn_dt => TRUNC(l_sysdate)
,p_dtctd_dt => p_effective_date
);
SELECT NULL
FROM ben_ptnl_ler_for_per pil
WHERE pil.person_id = p_person_id
AND pil.ler_id = l_ler_id
AND pil.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt;
SELECT per.person_id
,per.date_of_birth
FROM per_contact_relationships ctr, per_all_people_f per
WHERE ctr.person_id = p_person_id
AND per.person_id = ctr.contact_person_id
AND ctr.personal_flag = 'Y'
AND ctr.contact_type = 'S'
and l_effective_date between nvl(ctr.date_start, hr_api.g_sot)
and nvl(ctr.date_end, hr_api.g_eot)
AND l_effective_date BETWEEN per.effective_start_date
AND per.effective_end_date;
SELECT per.person_id
,per.date_of_birth
FROM per_contact_relationships ctr, per_all_people_f per
WHERE ctr.person_id = p_person_id
AND per.person_id = ctr.contact_person_id
AND ctr.personal_flag = 'Y'
AND ctr.dependent_flag = 'Y'
AND l_effective_date BETWEEN per.effective_start_date
AND per.effective_end_date;
SELECT per.person_id
,per.date_of_birth
FROM per_contact_relationships ctr, per_all_people_f per
WHERE ctr.person_id = p_person_id
AND per.person_id = ctr.contact_person_id
AND ctr.personal_flag = 'Y'
AND ctr.contact_type IN ('C', 'O', 'A', 'T')
AND l_effective_date BETWEEN per.effective_start_date
AND per.effective_end_date;
SELECT per.person_id
,per.date_of_birth
FROM per_contact_relationships ctr, per_all_people_f per
WHERE ctr.person_id = p_person_id
AND per.person_id = ctr.contact_person_id
AND ctr.personal_flag = 'Y'
AND ctr.dependent_flag = 'Y'
AND l_effective_date BETWEEN per.effective_start_date
AND per.effective_end_date
ORDER BY per.date_of_birth;
SELECT per.person_id
,per.date_of_birth
FROM per_contact_relationships ctr, per_all_people_f per
WHERE ctr.person_id = p_person_id
AND per.person_id = ctr.contact_person_id
AND ctr.personal_flag = 'Y'
AND ctr.contact_type IN ('C', 'O', 'A', 'T')
AND l_effective_date BETWEEN per.effective_start_date
AND per.effective_end_date
ORDER BY per.date_of_birth;
SELECT per.person_id
,per.date_of_birth
FROM per_contact_relationships ctr, per_all_people_f per
WHERE ctr.person_id = p_person_id
AND per.person_id = ctr.contact_person_id
AND ctr.personal_flag = 'Y'
AND ctr.dependent_flag = 'Y'
AND l_effective_date BETWEEN per.effective_start_date
AND per.effective_end_date
ORDER BY per.date_of_birth DESC;
SELECT per.person_id
,per.date_of_birth
FROM per_contact_relationships ctr, per_all_people_f per
WHERE ctr.person_id = p_person_id
AND per.person_id = ctr.contact_person_id
AND ctr.personal_flag = 'Y'
AND ctr.contact_type IN ('C', 'O', 'A', 'T')
AND l_effective_date BETWEEN per.effective_start_date
AND per.effective_end_date
ORDER BY per.date_of_birth DESC;
select min(effective_start_date)
From per_all_assignments_f ass
where person_id = p_person_id
and ass.assignment_type <> 'C'
and primary_flag = 'Y' ;
select min(effective_start_date)
From per_all_assignments_f ass
where person_id = p_person_id
and ass.assignment_type <> 'C'
and primary_flag = 'Y' ;
select pgm_id
from ben_ptip_f ptip
where ptip.ptip_id = p_ptip_id
and nvl(g_fonm_cvg_strt_dt,p_effective_date)
between ptip.effective_start_date and ptip.effective_end_date ;
select pl_id
from ben_plip_f plip
where plip.plip_id = p_plip_id
and nvl(g_fonm_cvg_strt_dt,p_effective_date)
between plip.effective_start_date
and plip.effective_end_date;
select oipl_id
from ben_oiplip_f oiplip
where oiplip.oiplip_id = p_oiplip_id
and nvl(g_fonm_cvg_strt_dt,p_effective_date)
between oiplip.effective_start_date
and oiplip.effective_end_date;
SELECT NVL(SUM(a.result_value),0) result_value
FROM pay_run_result_values a
,pay_element_types_f b
,pay_assignment_actions d
,pay_payroll_actions e
,per_time_periods g
,pay_run_results h
,ben_acty_base_rt_f i
,pay_input_values_f j
WHERE d.assignment_id = c_assignment_id
AND d.payroll_action_id = e.payroll_action_id
AND i.input_value_id = j.input_value_id
AND i.element_type_id = b.element_type_id
AND i.acty_base_rt_id = c_acty_base_rt_id
AND c_effective_date BETWEEN i.effective_start_date
AND i.effective_end_date
AND i.business_group_id = c_business_group_id
AND g.payroll_id = c_payroll_id
AND b.element_type_id = h.element_type_id
AND d.assignment_action_id = h.assignment_action_id
AND e.date_earned BETWEEN g.start_date AND g.end_date
AND e.date_earned BETWEEN c_from_date AND c_to_date
AND a.input_value_id = j.input_value_id
AND a.run_result_id = h.run_result_id
AND j.element_type_id = b.element_type_id
AND c_effective_date BETWEEN b.effective_start_date
AND b.effective_end_date
AND c_effective_date BETWEEN j.effective_start_date
AND j.effective_end_date;
select NVL(to_number(fti.FED_INFORMATION1),0) allwd_underpymt_value
,NVL(to_number(fti.FED_INFORMATION2),0) allwd_underpymt_pct
from pay_us_federal_tax_info_f fti
where fti.fed_information_category = 'BENEFIT UNDERPAY'
and c_effective_date between fti.effective_start_date
and fti.effective_end_date;
SELECT pln.cobra_pymt_due_dy_num
FROM ben_pl_f pln
WHERE pln.pl_id = p_pl_id
AND nvl(g_fonm_cvg_strt_dt,p_effective_date) BETWEEN pln.effective_start_date
AND pln.effective_end_date
AND pln.business_group_id = p_business_group_id;
SELECT a.result_value
FROM pay_run_result_values a
,pay_element_types_f b
,pay_assignment_actions d
,pay_payroll_actions e
,per_time_periods g
,pay_run_results h
,ben_acty_base_rt_f i
,pay_input_values_f j
WHERE d.assignment_id = p_assignment_id
AND d.payroll_action_id = e.payroll_action_id
AND i.input_value_id = j.input_value_id
AND i.element_type_id = b.element_type_id
AND i.acty_base_rt_id = p_acty_base_rt_id
AND nvl(g_fonm_cvg_strt_dt,p_effective_date) BETWEEN i.effective_start_date
AND i.effective_end_date
AND i.business_group_id = p_business_group_id
AND g.payroll_id = p_payroll_id
AND l_due_date BETWEEN g.start_date AND g.end_date
AND b.element_type_id = h.element_type_id
AND d.assignment_action_id = h.assignment_action_id
AND e.date_earned BETWEEN g.start_date AND g.end_date
AND (
( p_first_pymt = 'Y'
AND l_due_date >= e.date_earned)
OR p_first_pymt = 'N')
AND a.input_value_id = j.input_value_id
AND a.run_result_id = h.run_result_id
AND j.element_type_id = b.element_type_id
AND nvl(g_fonm_cvg_strt_dt,p_effective_date) BETWEEN b.effective_start_date
AND b.effective_end_date
AND p_effective_date BETWEEN j.effective_start_date
AND j.effective_end_date;
SELECT eev.screen_entry_value
FROM pay_element_entry_values_f eev
WHERE eev.element_entry_value_id = p_element_entry_value_id
AND nvl(g_fonm_cvg_strt_dt,p_effective_date) BETWEEN eev.effective_start_date
AND eev.effective_end_date;
SELECT pen.pl_id
,prv.element_entry_value_id
,prv.acty_base_rt_id
,prv.rt_strt_dt
,prv.rt_end_dt
,prv.ann_rt_val
,pen.prtt_enrt_rslt_id
,prv.mlt_cd
,prv.per_in_ler_id
,pen.enrt_cvg_strt_dt
FROM ben_prtt_enrt_rslt_f pen
,ben_prtt_rt_val prv
WHERE pen.person_id = p_person_id
AND pen.pgm_id = p_pgm_id
AND pen.prtt_enrt_rslt_stat_cd IS NULL
AND pen.sspndd_flag = 'N'
AND pen.business_group_id = p_business_group_id
AND pen.prtt_enrt_rslt_id = prv.prtt_enrt_rslt_id
AND prv.business_group_id = pen.business_group_id
AND prv.prtt_rt_val_stat_cd IS NULL
AND prv.acty_typ_cd LIKE 'PBC%'
AND pen.effective_end_date = hr_api.g_eot
ORDER BY prv.rt_strt_dt;
SELECT pel.elcns_made_dt
,crp.per_in_ler_id
FROM ben_cbr_per_in_ler crp, ben_pil_elctbl_chc_popl pel
WHERE crp.cbr_quald_bnf_id = p_cbr_quald_bnf_id
AND crp.init_evt_flag = 'Y'
AND crp.business_group_id = p_business_group_id
AND crp.per_in_ler_id = pel.per_in_ler_id
AND pel.pgm_id = p_pgm_id
AND pel.business_group_id = crp.business_group_id;
SELECT cqb.*
,crp.per_in_ler_id
,pil.ler_id
FROM ben_cbr_quald_bnf cqb
,ben_cbr_per_in_ler crp
,ben_per_in_ler pil
WHERE cqb.quald_bnf_person_id = p_person_id
AND cqb.quald_bnf_flag = 'Y'
AND cqb.pgm_id = NVL(p_pgm_id
,cqb.pgm_id)
AND NVL(cqb.ptip_id
,NVL(p_ptip_id
,-1)) = NVL(p_ptip_id
,-1)
AND cqb.business_group_id = p_business_group_id
AND cqb.cbr_quald_bnf_id = crp.cbr_quald_bnf_id
AND crp.init_evt_flag = 'Y'
AND crp.per_in_ler_id = pil.per_in_ler_id
AND crp.business_group_id = cqb.business_group_id
-- AND crp.business_group_id = pil.business_group_id
AND pil.per_in_ler_stat_cd NOT IN ('VOIDD', 'BCKDT');
SELECT NULL
FROM ben_cbr_per_in_ler crp, ben_per_in_ler pil
WHERE crp.cbr_quald_bnf_id = p_cbr_quald_bnf_id
AND crp.per_in_ler_id = pil.per_in_ler_id
AND pil.per_in_ler_stat_cd NOT IN ('VOIDD', 'BCKDT')
AND crp.business_group_id = pil.business_group_id
AND crp.business_group_id = p_business_group_id
AND crp.init_evt_flag = 'N';
SELECT crp.per_in_ler_id
FROM ben_cbr_per_in_ler crp, ben_per_in_ler pil
WHERE crp.cbr_quald_bnf_id = p_cbr_quald_bnf_id
AND crp.per_in_ler_id = pil.per_in_ler_id
AND pil.per_in_ler_stat_cd NOT IN ('VOIDD', 'BCKDT')
-- AND crp.business_group_id = pil.business_group_id
AND crp.business_group_id = p_business_group_id
AND crp.init_evt_flag = 'Y';
SELECT pel.*
FROM ben_pil_elctbl_chc_popl pel
,ben_per_in_ler pil
WHERE pel.pgm_id = p_pgm_id
AND pel.per_in_ler_id = pil.per_in_ler_id
and pil.person_id = p_person_id
AND pel.business_group_id = p_business_group_id
AND pel.business_group_id = pil.business_group_id
AND pil.per_in_ler_stat_cd NOT IN ('VOIDD', 'BCKDT')
ORDER BY pel.enrt_perd_end_dt desc;
SELECT len.dys_no_enrl_not_elig_num
FROM ben_lee_rsn_f len
WHERE len.lee_rsn_id = p_lee_rsn_id
AND len.business_group_id = p_business_group_id
AND NVL(p_lf_evt_ocrd_dt
,p_effective_date) BETWEEN len.effective_start_date
AND len.effective_end_date;
SELECT crp.*
FROM ben_cbr_per_in_ler crp, ben_ler_f ler, ben_per_in_ler pil
WHERE crp.cbr_quald_bnf_id = p_cbr_quald_bnf_id
AND crp.per_in_ler_id = pil.per_in_ler_id
AND pil.ler_id = ler.ler_id
AND ler.typ_cd = 'DSBLTY'
AND ler.qualg_evt_flag = 'Y'
AND NVL(p_lf_evt_ocrd_dt
,p_effective_date) BETWEEN ler.effective_start_date
AND ler.effective_end_date
AND ler.business_group_id = p_business_group_id
AND ler.business_group_id = pil.business_group_id
AND pil.per_in_ler_stat_cd NOT IN ('VOIDD', 'BCKDT')
AND crp.cnt_num =
(SELECT MAX(cnt_num)
FROM ben_cbr_per_in_ler crp2, ben_per_in_ler pil2
WHERE crp2.cbr_quald_bnf_id = p_cbr_quald_bnf_id
AND crp2.per_in_ler_id = pil2.per_in_ler_id
AND pil2.per_in_ler_stat_cd NOT IN ('VOIDD', 'BCKDT')
AND crp2.business_group_id = p_business_group_id
-- AND crp2.business_group_id = pil2.business_group_id
);
SELECT cqb.*
FROM ben_cbr_quald_bnf cqb
,ben_cbr_per_in_ler crp
,ben_per_in_ler pil
WHERE cqb.cvrd_emp_person_id = p_cvrd_emp_person_id
AND cqb.quald_bnf_flag = 'Y'
AND l_effective_date BETWEEN cqb.cbr_elig_perd_strt_dt
AND cqb.cbr_elig_perd_end_dt
AND cqb.business_group_id = p_business_group_id
AND cqb.cbr_quald_bnf_id = crp.cbr_quald_bnf_id
AND cqb.pgm_id = p_pgm_id
AND NVL(cqb.ptip_id
,-1) = NVL(p_ptip_id
,-1)
AND crp.per_in_ler_id = pil.per_in_ler_id
AND crp.business_group_id = cqb.business_group_id
--AND pil.business_group_id = crp.business_group_id
AND crp.init_evt_flag = 'Y'
AND pil.per_in_ler_stat_cd NOT IN ('VOIDD', 'BCKDT');
SELECT peo.*
FROM ben_elig_to_prte_rsn_f peo
WHERE peo.ler_id = p_ler_id
AND peo.business_group_id = p_business_group_id
AND NVL(p_lf_evt_ocrd_dt
,p_effective_date) BETWEEN peo.effective_start_date
AND peo.effective_end_date
AND NVL(peo.pgm_id
,p_pgm_id) = p_pgm_id
AND NVL(peo.ptip_id
,-1) = NVL(p_ptip_id
,-1)
AND ( peo.mx_poe_val IS NOT NULL
OR peo.mx_poe_rl IS NOT NULL);
SELECT NULL
FROM ben_per_in_ler pil
WHERE pil.person_id = p_person_id
AND pil.business_group_id = p_business_group_id
AND pil.lf_evt_ocrd_dt = l_lf_evt_ocrd_dt
AND pil.ler_id <> l_ler_id
AND pil.per_in_ler_stat_cd NOT IN ('VOIDD', 'BCKDT');
SELECT cqb.cbr_quald_bnf_id
,cqb.pgm_id
,cqb.ptip_id
,crp.per_in_ler_id
,pil.ler_id
FROM ben_cbr_quald_bnf cqb
,ben_cbr_per_in_ler crp
,ben_per_in_ler pil
WHERE cqb.quald_bnf_person_id = p_person_id
AND cqb.quald_bnf_flag = 'Y'
AND cqb.pgm_id = p_pgm_id
AND cqb.business_group_id = p_business_group_id
AND cqb.cbr_quald_bnf_id = crp.cbr_quald_bnf_id
AND crp.init_evt_flag = 'Y'
AND crp.per_in_ler_id = pil.per_in_ler_id
AND crp.business_group_id = cqb.business_group_id
AND pil.per_in_ler_stat_cd NOT IN ('VOIDD', 'BCKDT');
SELECT ler.*, pil.lf_evt_ocrd_dt
FROM ben_per_in_ler pil
,ben_ler_f ler
WHERE pil.ler_id = ler.ler_id
AND pil.business_group_id = p_business_group_id
AND ler.name = 'Assistance Eligible Individual'
AND pil.per_in_ler_stat_cd NOT IN ('VOIDD', 'BCKDT')
AND pil.lf_evt_ocrd_dt
between p_cbr_elig_strt_dt
and p_cbr_elig_end_dt
AND p_effective_date
between ler.effective_start_date
and ler.effective_end_date;
ben_cbr_quald_bnf_api.update_cbr_quald_bnf
(p_cbr_quald_bnf_id=> l_cqb_rec.cbr_quald_bnf_id
,p_quald_bnf_flag => 'N'
,p_business_group_id => p_business_group_id
,p_object_version_number => l_object_version_number
,p_effective_date => p_effective_date);
select pgm_typ_cd
from ben_pgm_f
where pgm_id = cv_pgm_id
and cv_effective_date between effective_start_date
and effective_end_date;
select ler_id, name
from ben_ler_f
where typ_cd = 'GSP'
and business_group_id = p_business_group_id
and cv_effective_date between effective_start_date
and effective_end_date;
select cfk.segment1
from per_all_assignments_f asg, hr_soft_coding_keyflex cfk
where asg.person_id = p_person_id
and asg.assignment_type <> 'C'
and asg.primary_flag = 'Y'
AND p_effective_date BETWEEN asg.effective_start_date
AND asg.effective_end_date
and asg.soft_coding_keyflex_id = cfk.soft_coding_keyflex_id
order by asg.effective_start_date ;