[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
08-Jun-02 pabodla 115.64 Do not select the contingent worker
assignment when assignment data is
fetched.
04-Sep-02 kmahendr 115.65 New acty_ref_perd_cd added.
16 Dec 02 hnarayan 115.66 Added NOCOPY hint
9-JAN-2002 glingapp 115.67 Bug 2519393
Created new message 93298 to make message more informative.
Changed the cursor 'c_opt_typ_cd'.
28-APR-2003 rpgupta 115.69 Bug 2924077
Added a check on effective dates while
picking up details of the person's spouse
The same chk has been added to all cursors
using per_contact_relationships
14-JUL-2003 glingapp 115.70 added outer join on per_periods_of_service in
cursor c_person of determine_los
24-Sep-2003 ikasire 115.71 Bug 3151737 - made the join to pay_all_payrolls_f
as outer join and added the effective date
clause for the same table.
25-Nov-2003 bmanyam 115.72 Bug:3265142. Changed the cursor c_stated_salary.
Fetching salary from per_assignment_extra_info.
aei_information6 column for 'Benefit Assignment'
records (ie. assignment_type = 'B')
16-Dec-2003 ikasire 115.73 Bug: 3315997 When salary is not defined return with NULL
17-Dec-03 vvprabhu 115.74 Added the assignment for g_debug at the start
08-Apr-04 pbodla 115.75 FONM : use cvg start date or rate start
date from processing.
p_effective_date is overloaded.
18-Apr-04 mmudigon 115.76 Universal Eligibility
13-Jul-04 rpgupta 115.77 3752107: If no salary/ benefit balance is found on
the determine date, 1st look at the closest assignment
after the determine date
13-aug-04 tjesumic 115,78 fonm parameter added
28-sep-04 kmahendr 115.79 Bug#3899510 - cursor c_stated_salary modified
to take assignment_id
13-Oct-04 mmudigon 115.80 Forward port from 115.74.11510.4
Bug 3818453. Added call to get_latest_paa_id()
17-Feb-05 ssarkar 115.81 Bug 4120426--Called load_warnings in proc determine_compensation.
23-feb-05 ssarkar 115.82 changed to_char(p_effective_date, 'DD-MON-RRRR')
to fnd_date.date_to_displaydate(p_effective_date).
07-apr-05 nhunur 115.83 apply fnd_number on what FF returns in run_rule.
27-Apr-05 mmudigon 115.84 OIC integration. Addition of the
codes 'OICAMTEARNED' and'OICAMTPAID'
02-May-05 bmanyam 115.85 Bug 4343063. Fixed a Typo..
07-Jul-05 Tmathers 115.86 Bug 4455689. changed
asg.assignment_id = nvl(p_assignment_id,asg.assignment_id)
into
((asg.assignment_id = p_assignment_id)
or (p_assignment_id is null))
to fixe performance issue in 9.2.0.5.0
19-jul-05 ssarkar 115.87 Bug : 4500760 : determine_date.main should be bypassed for OIC.
21-jul-05 ssarkar 115.88 Bug : 4500760 : l_clf.proration_flag mapped to 'T'/'F' for OIC evaluation.
27-jul-05 pbodla 115.89 Bug : 4509422 : p_init_msg_list is
passed oic procedure to clear message
stack.
08-sep-05 pbodla 115.90 Bug 4509422 : Even if the oic code
errors still we need to continue with
0 values populated to l_comp_earned, l_comp_paid
This is temp fix, once iic code is changed
to handle no person data or setup not found cases
then this error can be un commented again.
06-Mar-2005 bmanyam 115.91 5075001 - To calculate Hourly Compensation (PHR)
divide the ANNUAL_VALUE by profile BEN_HRLY_ANAL_FCTR.
27-Mar-2006 abparekh 115.92 Bug 5118063 : CWB : Fixed issue : when there is single pay
proposal with salary as zero, then p_value
remains unassigned.
23-May-2006 nhunur 115.93 5187379 : avoid using secure views.
18-Aug-2006 kmahendr 115.94 5473471 - Output parameter is assigned
a value before return in determine_compensation
29-Mar-2007 rtagarra 115.95 Bug 5931412 : To take care of short months case.
09-Apr-2007 rtagarra 115.96 Bug 5931412 : Leap Year Case.
08-Jun-2007 sshetty 115.97 Bug 6067726. Annualization factor will be
derived from per_time_periods based on
the payroll info if the
Pay Annualization Factor value on Salary
basis is null.
31-Oct-2007 rtagarra 115.98 Bug 6601294: Fixed cursor c_stated_salary.
19-Nov-2007 rtagarra 115.99 Bug 6627329 : Fixed cursor c_stated_salary for Perform Issue.
23-Sep-2008 velvanop 115.100 Bug 7313778 : For determining the compensation of a rehired employee,
rehire date should be used instead of the hire date.
*/
--------------------------------------------------------------------------------
--
g_package varchar2(30) := 'ben_derive_factors.';
select clf.comp_lvl_uom,
clf.comp_src_cd,
clf.comp_lvl_det_cd,
clf.comp_lvl_det_rl,
clf.rndg_cd,
clf.rndg_rl,
clf.bnfts_bal_id,
clf.defined_balance_id,
clf.sttd_sal_prdcty_cd,
clf.comp_calc_rl,
clf.start_day_mo,
clf.end_day_mo,
clf.start_year,
clf.end_year,
clf.proration_flag
from ben_comp_lvl_fctr clf
where p_comp_lvl_fctr_id = clf.comp_lvl_fctr_id;
select ppp.proposed_salary_n proposed_salary,
ppb.pay_basis,
ppb.pay_annualization_factor,
paf.period_type payroll,
asg.normal_hours,
asg.payroll_id,
asg.frequency,
asg.assignment_id,
ppp.change_date -- Bug:3265142. Added this for order-by clause
from per_pay_proposals ppp,
-- per_assignments_f asg,
per_all_assignments_f asg,
per_pay_bases ppb,
pay_all_payrolls_f paf,
per_all_people_f per
where per.person_id = p_person_id
/* Bug:3265142 Start: Fetching salary from per_pay_proposals for assignment_type = 'E'
and per_assignment_extra_info.aei_information6 assignment_type = 'B' (Refer UNIONed-query).
*/
--and asg.assignment_type <> 'C'
and asg.assignment_type = 'E'
-- Bug:3265142 End
and asg.person_id = per.person_id
-- 4455689
and ((asg.assignment_id = p_assignment_id)
or (p_assignment_id is null))
-- and asg.primary_flag = 'Y'
and ((asg.primary_flag = p_primary_flag)
or ( p_primary_flag is null))
-- End of 4455689
and ppb.pay_basis_id = asg.pay_basis_id
and asg.payroll_id = paf.payroll_id(+) -- Bug 3151737 Why do we need payroll here???
and l_date
between nvl(paf.effective_start_date,l_date)
and nvl(paf.effective_end_date,l_date)
AND nvl(ppp.approved,'N') = 'Y'
-- and l_effective_date
and l_date
between asg.effective_start_date
and asg.effective_end_date
and l_date -- l_effective_date
between per.effective_start_date
and per.effective_end_date
and asg.assignment_id = ppp.assignment_id
and ppp.change_date <= l_date
/* Bug:3265142 Start: Fetching salary from per_pay_proposals for assignment_type = 'E'
and per_assignment_extra_info.aei_information6 assignment_type = 'B' (Refer UNIONed-query).
*/
UNION
select fnd_number.canonical_to_number(aei.aei_information6) proposed_salary,
ppb.pay_basis,
ppb.pay_annualization_factor,
paf.period_type payroll,
asg.normal_hours,
asg.payroll_id,
asg.frequency,
asg.assignment_id assignment_id,
fnd_date.canonical_to_date(aei.aei_information8) change_date
from --per_assignments_f asg,
per_all_assignments_f asg,
per_assignment_extra_info aei,
per_pay_bases ppb,
pay_all_payrolls_f paf,
per_all_people_f per
where per.person_id = p_person_id
and asg.assignment_type = 'B'
and asg.person_id = per.person_id
-- 4455689
and ((asg.assignment_id = p_assignment_id)
or (p_assignment_id is null))
and ((asg.primary_flag = p_primary_flag)
or ( p_primary_flag is null))
-- End of 4455689
and ppb.pay_basis_id = asg.pay_basis_id
and asg.payroll_id = paf.payroll_id(+)
and l_date between nvl(paf.effective_start_date,l_date)and nvl(paf.effective_end_date,l_date)
and l_date between asg.effective_start_date and asg.effective_end_date -- 3752107
--and l_date <= asg.effective_end_date
and l_date between per.effective_start_date and per.effective_end_date
and asg.assignment_id = aei.assignment_id
and not exists (select 1
from per_all_assignments_f asg,
per_all_people_f per
where per.person_id = p_person_id
and asg.assignment_type = 'E'
and asg.person_id = per.person_id
and l_date between asg.effective_start_date and asg.effective_end_date
and l_date between per.effective_start_date and per.effective_end_date)
order by 8, 9 desc; -- Bug 6601294
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 l_date
between pln.effective_start_date
and pln.effective_end_date
and l_effective_date
between opt.effective_start_date
and opt.effective_end_date;*/
select distinct ptp.OPT_TYP_CD
from BEN_PL_TYP_f ptp
where ( p_pl_id is null
or exists ( select 1
from ben_pl_f pl1
where pl1.pl_id = p_pl_id
and ptp.OPT_TYP_CD = 'CWB'
and pl1.pl_typ_id = ptp.pl_typ_id
and pl1.business_group_id = p_business_group_id
and l_effective_date between pl1.effective_start_date and pl1.effective_end_date))
and ( p_oipl_id is null
or exists ( select 1
from ben_pl_f pl2 , ben_oipl_f oipl2
where oipl2.oipl_id = p_oipl_id
and ptp.OPT_TYP_CD = 'CWB'
and pl2.pl_id = oipl2.pl_id
and pl2.pl_typ_id = ptp.pl_typ_id
and pl2.business_group_id = p_business_group_id
and oipl2.business_group_id = p_business_group_id
and l_effective_date between oipl2.effective_start_date and oipl2.effective_end_date
and l_effective_date between pl2.effective_start_date and pl2.effective_end_date) )
and ( p_pgm_id is null
or exists ( select 1
from ben_ptip_f ptip
where ptip.pgm_id = p_pgm_id
and ptp.OPT_TYP_CD = 'CWB'
and ptip.pl_typ_id = ptp.pl_typ_id
and ptip.business_group_id = p_business_group_id
and l_effective_date between ptip.effective_start_date and ptip.effective_end_date) )
and ptp.business_group_id = p_business_group_id
and l_effective_date between ptp.effective_start_date and ptp.effective_end_date;
select pbb.val, bnb.name
from ben_per_bnfts_bal_f pbb,
ben_bnfts_bal_f bnb
where pbb.person_id = p_person_id
and pbb.business_group_id = p_business_group_id
and pbb.bnfts_bal_id = bnb.bnfts_bal_id
and p_date
between bnb.effective_start_date
and bnb.effective_end_date
and p_date
between pbb.effective_start_date
and pbb.effective_end_date
and pbb.bnfts_bal_id = l_clf.bnfts_bal_id;
select assignment_id
from per_all_assignments_f paf
where primary_flag = 'Y'
and person_id = p_person_id
and paf.assignment_type <> 'C'
and business_group_id = p_business_group_id
and l_effective_date
between paf.effective_start_date
and paf.effective_end_date
order by decode(paf.assignment_type, 'E',1,2);
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'
and effective_start_Date >= l_date;
select uses_all_asmts_for_rts_flag
from ben_pgm_f pgm
where pgm.pgm_id = p_pgm_id
and l_effective_date between
pgm.effective_start_date and pgm.effective_end_date;
select use_all_asnts_for_rt_flag
from ben_pl_f pln
where pln.pl_id = p_pl_id
and p_effective_date between
pln.effective_start_date and pln.effective_end_date;
select ptp.period_num period_num
from per_time_periods ptp
where payroll_id=cp_payroll_id
and to_char(cut_off_date,'RRRR')= to_char(cp_effective_date,'RRRR')
order by 1 desc;
select per.date_of_birth
from per_all_people_f per
where per.person_id = p_person_id
and per.business_group_id = p_business_group_id
and l_effective_date
between per.effective_start_date
and per.effective_end_date;
select per.date_of_birth
from per_contact_relationships ctr,
per_all_people_f per
where ctr.person_id = l_person_id
and per.person_id = ctr.contact_person_id
and per.business_group_id = p_business_group_id
and ctr.personal_flag = 'Y'
and ctr.contact_type = 'S'
and l_effective_date
between per.effective_start_date
and per.effective_end_date
/* bug 2924077 */
and l_effective_date
between nvl(ctr.date_start, hr_api.g_sot)
and nvl(ctr.date_end, hr_api.g_eot);
select per.date_of_birth
from per_contact_relationships ctr,
per_all_people_f per
where ctr.person_id = l_person_id
and per.person_id = ctr.contact_person_id
and per.business_group_id = p_business_group_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
/* bug 2924077 */
and l_effective_date
between nvl(ctr.date_start, hr_api.g_sot)
and nvl(ctr.date_end, hr_api.g_eot);
select per.date_of_birth
from per_contact_relationships ctr,
per_all_people_f per
where ctr.person_id = l_person_id
and per.person_id = ctr.contact_person_id
and per.business_group_id = p_business_group_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
/* bug 2924077 */
and l_effective_date
between nvl(ctr.date_start, hr_api.g_sot)
and nvl(ctr.date_end, hr_api.g_eot);
select min(per.date_of_birth)
from per_contact_relationships ctr,
per_all_people_f per
where ctr.person_id = l_person_id
and per.person_id = ctr.contact_person_id
and per.business_group_id = p_business_group_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
/* bug 2924077 */
and l_effective_date
between nvl(ctr.date_start, hr_api.g_sot)
and nvl(ctr.date_end, hr_api.g_eot)
order by per.date_of_birth;
select min(per.date_of_birth)
from per_contact_relationships ctr,
per_all_people_f per
where ctr.person_id = l_person_id
and per.person_id = ctr.contact_person_id
and per.business_group_id = p_business_group_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
/* bug 2924077 */
and l_effective_date
between nvl(ctr.date_start, hr_api.g_sot)
and nvl(ctr.date_end, hr_api.g_eot)
order by per.date_of_birth;
select max(per.date_of_birth)
from per_contact_relationships ctr,
per_all_people_f per
where ctr.person_id = l_person_id
and per.person_id = ctr.contact_person_id
and per.business_group_id = p_business_group_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
/* bug 2924077 */
and l_effective_date
between nvl(ctr.date_start, hr_api.g_sot)
and nvl(ctr.date_end, hr_api.g_eot)
order by per.date_of_birth;
select max(per.date_of_birth)
from per_contact_relationships ctr,
per_all_people_f per
where ctr.person_id = l_person_id
and per.business_group_id = p_business_group_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
/* bug 2924077 */
and l_effective_date
between nvl(ctr.date_start, hr_api.g_sot)
and nvl(ctr.date_end, hr_api.g_eot)
order by per.date_of_birth;
select aei.aei_information1
from per_all_assignments_f asg,
per_assignment_extra_info aei
where asg.person_id = p_person_id
and asg.assignment_id = aei.assignment_id
and asg.primary_flag = 'Y'
and asg.assignment_type = 'B'
and asg.business_group_id = p_business_group_id
and aei.information_type = 'BEN_DERIVED'
and l_effective_date
between asg.effective_start_date
and asg.effective_end_date;
select agf.age_det_rl,
agf.age_det_cd,
agf.age_to_use_cd,
agf.age_uom,
agf.rndg_cd,
agf.rndg_rl,
agf.age_calc_rl
from ben_age_fctr agf
where agf.age_fctr_id = p_age_fctr_id;
select pps.date_start,
pps.adjusted_svc_date,
ppf.original_date_of_hire
from per_all_people_f ppf,
per_periods_of_service pps
where pps.person_id(+) = ppf.person_id
and ppf.person_id = p_person_id
and ppf.business_group_id = p_business_group_id
and l_effective_date
between ppf.effective_start_date
and ppf.effective_end_date
--Bug2168233 to get the right record from per_periods_of_service
and l_effective_date >= pps.date_start(+) /*Bug 2973791 outer join added*/
order by pps.date_start desc ;
select pep.ovrid_svc_dt
from ben_elig_per_f pep,
ben_per_in_ler pil
where pep.person_id = p_person_id
and pep.business_group_id = p_business_group_id
and nvl(pep.pl_id,-1) = nvl(p_pl_id,-1)
and nvl(pep.pgm_id,-1) = nvl(p_pgm_id,-1)
and l_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') -- found row condition
or pil.per_in_ler_stat_cd is null -- outer join condition
)
;
select lsf.los_det_rl,
lsf.los_det_cd,
lsf.los_uom,
lsf.rndg_cd,
lsf.rndg_rl,
lsf.use_overid_svc_dt_flag,
lsf.los_dt_to_use_cd,
lsf.los_dt_to_use_rl,
lsf.los_calc_rl
from ben_los_fctr lsf
where lsf.los_fctr_id = p_los_fctr_id
and lsf.business_group_id = p_business_group_id;
select aei.aei_information2 iasd,
aei.aei_information13 idoh,
aei.aei_information3 iohd
from per_all_assignments_f asg,
per_assignment_extra_info aei
where asg.person_id = p_person_id
and asg.assignment_id = aei.assignment_id
and asg.primary_flag = 'Y'
and asg.assignment_type = 'B'
and asg.business_group_id = p_business_group_id
and aei.information_type = 'BEN_DERIVED'
and l_effective_date
between asg.effective_start_date
and asg.effective_end_date;
select cla.los_fctr_id ,
cla.age_fctr_id
from ben_cmbn_age_los_fctr cla
where cla.cmbn_age_los_fctr_id = p_cmbn_age_los_fctr_id
and cla.business_group_id = p_business_group_id;
select min(effective_start_date)
from per_all_assignments_f ass
where person_id = p_person_id
and (assignment_id = p_assignment_id or
(p_assignment_id is null and
ass.primary_flag = 'Y' and
ass.assignment_type <> 'C'));
select assignment_id
from per_all_assignments_f paf
where person_id = p_person_id
and (assignment_id = p_assignment_id or
(p_assignment_id is null and
primary_flag = 'Y' and
paf.assignment_type <> 'C'))
and business_group_id = p_business_group_id
and l_effective_date
between paf.effective_start_date
and paf.effective_end_date;
select hwf.hrs_wkd_in_perd_fctr_id
,hwf.hrs_src_cd
,hwf.hrs_wkd_det_cd
,hwf.hrs_wkd_det_rl
,hwf.rndg_cd
,hwf.rndg_rl
,hwf.defined_balance_id
,hwf.bnfts_bal_id
,hwf.mn_hrs_num
,hwf.mx_hrs_num
,hwf.once_r_cntug_cd
,hwf.hrs_wkd_calc_rl
from ben_hrs_wkd_in_perd_fctr hwf
where hwf.hrs_wkd_in_perd_fctr_id = p_hrs_wkd_in_perd_fctr_id
and hwf.business_group_id = p_business_group_id;
select assignment_id
from per_all_assignments_f paf
where primary_flag = 'Y'
and person_id = p_person_id
and paf.assignment_type <> 'C'
and business_group_id = p_business_group_id
and l_effective_date
between paf.effective_start_date
and paf.effective_end_date;
select pff.pct_fl_tm_fctr_id
,pff.use_prmry_asnt_only_flag
,pff.use_sum_of_all_asnts_flag
,pff.rndg_cd
,pff.rndg_rl
,pff.mn_pct_val
,pff.mx_pct_val
from ben_pct_fl_tm_fctr pff
where pff.pct_fl_tm_fctr_id = p_pct_fl_tm_fctr_id
and pff.business_group_id = p_business_group_id;