[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select ecc.crit_typ_cd,
ecc.oper_cd,
ecc.val_1,
ecc.val_2
from ben_ext_crit_typ ect,
ben_ext_crit_val ecv,
ben_ext_crit_cmbn ecc
where ect.crit_typ_cd = 'ADV'
and ect.ext_crit_typ_id = ecv.ext_crit_typ_id
and ect.ext_crit_prfl_id = p_ext_crit_prfl_id
and ecv.ext_crit_val_id = ecc.ext_crit_val_id
and ecc.crit_typ_cd in ('CAD','CED')
order by 1
;
select assignment_id
from per_all_assignments_f
where person_id = p_person_id
and p_effective_date between effective_start_date
and effective_end_date
and primary_flag = 'Y'
and (p_assignment_id is null or p_assignment_id = assignment_id )
and assignment_type = nvl(p_assignment_type,assignment_type) -- for any null will be sent
order by effective_start_date desc ; -- for any take the latest
select assignment_id
from per_all_assignments_f
where person_id = p_person_id
and p_effective_date between effective_start_date
and effective_end_date
and (p_assignment_id is null or p_assignment_id = assignment_id )
and assignment_type = nvl(p_assignment_type,assignment_type) -- for any null will be sent
order by effective_start_date desc ; -- for any take the latest
select
p.last_name
, p.date_of_birth
, p.employee_number
, p.first_name
, p.full_name
, p.marital_status
, p.middle_names
, p.national_identifier
, p.registered_disabled_flag
, p.sex
, p.student_status
, p.suffix
, p.pre_name_adjunct
, p.title
, p.date_of_death
, p.benefit_group_id
, p.applicant_number
, p.correspondence_language
, p.email_address
, p.known_as
, p.mailstop
, p.nationality
, p.pre_name_adjunct
, p.previous_last_name
, p.original_date_of_hire
, p.uses_tobacco_flag
, p.office_number
, p.date_employee_data_verified
, p.last_update_date
, p.last_updated_by
, p.last_update_login
, p.created_by
, p.creation_date
, p.attribute1
, p.attribute2
, p.attribute3
, p.attribute4
, p.attribute5
, p.attribute6
, p.attribute7
, p.attribute8
, p.attribute9
, p.attribute10
, p.person_type_id
,ppt.user_person_type
,p.per_information1
,p.per_information2
,p.per_information3
,p.per_information4
,p.per_information5
,p.per_information6
,p.per_information7
,p.per_information8
,p.per_information9
,p.per_information10
,p.per_information11
,p.per_information12
,p.per_information13
,p.per_information14
,p.per_information15
,p.per_information16
,p.per_information17
,p.per_information18
,p.per_information19
,p.per_information20
,p.per_information21
,p.per_information22
,p.per_information23
,p.per_information24
,p.per_information25
,p.per_information26
,p.per_information27
,p.per_information28
,p.per_information29
,p.per_information30
,p.business_group_id
from per_all_people_f p,
per_person_types ppt
where
p.person_id = p_person_id
and p_effective_date between p.effective_start_date
and p.effective_end_date
and p.business_group_id = ppt.business_group_id
and p.person_type_id = ppt.person_type_id
;
select name
from per_business_groups_perf
where business_group_id = p_id
;
g_last_update_date,
g_last_updated_by,
g_last_update_login,
g_created_by,
g_creation_date,
g_per_attr_1,
g_per_attr_2,
g_per_attr_3,
g_per_attr_4,
g_per_attr_5,
g_per_attr_6,
g_per_attr_7,
g_per_attr_8,
g_per_attr_9,
g_per_attr_10,
g_person_type_id,
g_person_types,
g_per_information1,
g_per_information2,
g_per_information3,
g_per_information4,
g_per_information5,
g_per_information6,
g_per_information7,
g_per_information8,
g_per_information9,
g_per_information10,
g_per_information11,
g_per_information12,
g_per_information13,
g_per_information14,
g_per_information15,
g_per_information16,
g_per_information17,
g_per_information18,
g_per_information19,
g_per_information20,
g_per_information21,
g_per_information22,
g_per_information23,
g_per_information24,
g_per_information25,
g_per_information26,
g_per_information27,
g_per_information28,
g_per_information29,
g_per_information30,
l_business_group_id
;
select
pos.name,
pos.attribute1,
pos.attribute2,
pos.attribute3,
pos.attribute4,
pos.attribute5,
pos.attribute6,
pos.attribute7,
pos.attribute8,
pos.attribute9,
pos.attribute10
into
g_position,
g_pos_flex_01,
g_pos_flex_02,
g_pos_flex_03,
g_pos_flex_04,
g_pos_flex_05,
g_pos_flex_06,
g_pos_flex_07,
g_pos_flex_08,
g_pos_flex_09,
g_pos_flex_10
from HR_ALL_POSITIONS_F pos
where pos.position_id = p_position_id
and p_effective_date between pos.EFFECTIVE_START_DATE and pos.EFFECTIVE_END_DATE ;
select
j.name,
j.attribute1,
j.attribute2,
j.attribute3,
j.attribute4,
j.attribute5,
j.attribute6,
j.attribute7,
j.attribute8,
j.attribute9,
j.attribute10
into
g_job,
g_job_flex_01,
g_job_flex_02,
g_job_flex_03,
g_job_flex_04,
g_job_flex_05,
g_job_flex_06,
g_job_flex_07,
g_job_flex_08,
g_job_flex_09,
g_job_flex_10
from per_jobs_vl j
where j.job_id = p_job_id;
select
pay.payroll_name,
pay.period_type,
pay.attribute1,
pay.attribute2,
pay.attribute3,
pay.attribute4,
pay.attribute5,
pay.attribute6,
pay.attribute7,
pay.attribute8,
pay.attribute9,
pay.attribute10,
tmpr.period_num,
tmpr.start_date,
tmpr.end_date,
k.concatenated_segments,
k.cost_allocation_keyflex_id,
c.consolidation_set_name,
c.consolidation_set_id
into
g_payroll,
g_payroll_period_type,
g_prl_flex_01,
g_prl_flex_02,
g_prl_flex_03,
g_prl_flex_04,
g_prl_flex_05,
g_prl_flex_06,
g_prl_flex_07,
g_prl_flex_08,
g_prl_flex_09,
g_prl_flex_10,
g_payroll_period_number,
g_payroll_period_strtdt,
g_payroll_period_enddt,
g_payroll_costing,
g_payroll_costing_id,
g_payroll_consolidation_set,
g_payroll_consolidation_set_id
from pay_payrolls_f pay,
per_time_periods tmpr,
pay_cost_allocation_keyflex k,
pay_consolidation_sets c
where pay.payroll_id = p_payroll_id
and p_effective_date between
nvl(pay.effective_start_date, p_effective_date)
and nvl(pay.effective_end_date, p_effective_date)
and pay.payroll_id = tmpr.payroll_id
and pay.period_type = tmpr.period_type
and p_effective_date between nvl(tmpr.start_date, p_effective_date)
and nvl(tmpr.end_date, p_effective_date)
and pay.cost_allocation_keyflex_id = k.cost_allocation_keyflex_id (+)
and pay.consolidation_set_id = c.consolidation_set_id;
select
g.name,
g.attribute1,
g.attribute2,
g.attribute3,
g.attribute4,
g.attribute5,
g.attribute6,
g.attribute7,
g.attribute8,
g.attribute9,
g.attribute10
into
g_employee_grade,
g_grd_flex_01,
g_grd_flex_02,
g_grd_flex_03,
g_grd_flex_04,
g_grd_flex_05,
g_grd_flex_06,
g_grd_flex_07,
g_grd_flex_08,
g_grd_flex_09,
g_grd_flex_10
from per_grades_vl g
where g.grade_id = p_grade_id;
select location_id
from hr_all_organization_units
where organization_id = p_org_id ;
select l.address_line_1,
l.address_line_2,
l.address_line_3,
l.town_or_city,
l.country,
l.postal_code,
l.region_1,
l.region_2,
l.region_3,
l.Telephone_number_1
from hr_locations_all l
where l.location_id = p_location_id;
select
l.location_code,
l.address_line_1,
l.address_line_2,
l.address_line_3,
l.town_or_city,
l.country,
l.postal_code,
l.region_1,
l.region_2,
l.region_3,
l.attribute1,
l.attribute2,
l.attribute3,
l.attribute4,
l.attribute5,
l.attribute6,
l.attribute7,
l.attribute8,
l.attribute9,
l.attribute10
into
g_location_code,
g_location_addr1,
g_location_addr2,
g_location_addr3,
g_location_city ,
g_location_country,
g_location_zip,
g_location_region1 ,
g_location_region2,
g_location_region3,
g_alc_flex_01,
g_alc_flex_02,
g_alc_flex_03,
g_alc_flex_04,
g_alc_flex_05,
g_alc_flex_06,
g_alc_flex_07,
g_alc_flex_08,
g_alc_flex_09,
g_alc_flex_10
from hr_locations_all l
where l.location_id = p_location_id;
select
a.bargaining_unit_code,
a.grade_id,
a.organization_id,
a.location_id,
a.assignment_status_type_id,
a.title,
a.position_id,
a.job_id,
a.payroll_id,
a.people_group_id,
a.pay_basis_id,
a.hourly_salaried_code,
a.labour_union_member_flag,
a.manager_flag,
a.employment_category,
a.last_update_date ,
a.last_updated_by ,
a.last_update_login,
a.created_by ,
a.creation_date ,
o.name,
s.user_status,
grp.group_name,
b.name,
b.attribute1,
b.attribute2,
b.attribute3,
b.attribute4,
b.attribute5,
b.attribute6,
b.attribute7,
b.attribute8,
b.attribute9,
b.attribute10,
a.ass_attribute1,
a.ass_attribute2,
a.ass_attribute3,
a.ass_attribute4,
a.ass_attribute5,
a.ass_attribute6,
a.ass_attribute7,
a.ass_attribute8,
a.ass_attribute9,
a.ass_attribute10,
a.normal_hours,
a.frequency,
a.time_normal_start,
a.time_normal_finish,
a.supervisor_id ,
a.assignment_type,
b.pay_basis
from per_all_assignments_f a,
hr_all_organization_units_vl o,
per_assignment_status_types s,
pay_people_groups grp,
per_pay_bases b
where
a.person_id = p_person_id
and p_effective_date between a.effective_start_date
and a.effective_end_date
and a.assignment_id = p_assignment_id
and a.organization_id = o.organization_id
and a.assignment_status_type_id = s.assignment_status_type_id
and a.people_group_id = grp.people_group_id (+)
and a.pay_basis_id = b.pay_basis_id (+)
;
g_asg_last_update_date,
g_asg_last_updated_by,
g_asg_last_update_login,
g_asg_created_by,
g_asg_creation_date,
g_employee_organization,
g_employee_status,
g_people_group,
g_pay_basis,
g_pbs_flex_01,
g_pbs_flex_02,
g_pbs_flex_03,
g_pbs_flex_04,
g_pbs_flex_05,
g_pbs_flex_06,
g_pbs_flex_07,
g_pbs_flex_08,
g_pbs_flex_09,
g_pbs_flex_10,
g_asg_attr_1,
g_asg_attr_2,
g_asg_attr_3,
g_asg_attr_4,
g_asg_attr_5,
g_asg_attr_6,
g_asg_attr_7,
g_asg_attr_8,
g_asg_attr_9,
g_asg_attr_10,
g_asg_normal_hours,
g_asg_frequency,
g_asg_time_normal_start,
g_asg_time_normal_finish,
g_asg_supervisor_id,
g_asg_type,
g_pay_basis_type
;
is select
est.name
from PER_ESTABLISHMENTS EST ,
PER_ESTABLISHMENT_ATTENDANCES esa
where esa.person_id = p_person_id
and est.ESTABLISHMENT_id = esa.ESTABLISHMENT_id
and p_effective_date between attended_start_date and nvl(attended_end_date,p_effective_date);
cursor c (l_person_id number ) is select
a.pay_annualization_factor,b.proposed_salary_n
from per_pay_bases a,
per_pay_proposals b,
per_all_assignments_f c
where
c.person_id = l_person_id and
p_effective_date between c.effective_start_date and c.effective_end_date and
c.assignment_id= g_assignment_id and
c.assignment_id = b.assignment_id and
c.pay_basis_id = a.pay_basis_id
and b.change_date =
(select max(d.change_date)
from per_pay_proposals d
where d.assignment_id = c.assignment_id
and d.change_date <= p_effective_date
and approved = 'Y' )
;
select
sum(bpl.prvdd_val) credit_provided
, sum(bpl.frftd_val) credit_forfited
, sum(bpl.used_val) credit_used
from ben_prtt_enrt_rslt_f pen
,ben_per_in_ler pil
,ben_bnft_prvdd_ldgr_f bpl
,ben_pl_f pl
where
pen.person_id = p_person_id
and pen.prtt_enrt_rslt_id = bpl.prtt_enrt_rslt_id
and p_effective_date between nvl(pen.effective_start_date, p_effective_date)
and nvl(pen.effective_end_date, p_effective_date)
and p_effective_date between nvl(bpl.effective_start_date, p_effective_date)
and nvl(bpl.effective_end_date, p_effective_date)
and pil.per_in_ler_id=bpl.per_in_ler_id
and pil.business_group_id+0=bpl.business_group_id+0
and pil.per_in_ler_stat_cd not in ('BCKDT','VOIDD')
and pen.pl_id = pl.pl_id
and pl.invk_flx_cr_pl_flag = 'Y'
and pl.imptd_incm_calc_cd is null
and p_effective_date between nvl(pl.effective_start_date, p_effective_date)
and nvl(pl.effective_end_date, p_effective_date)
;
select
full_name,
employee_number
from per_all_people_f
where person_id = p_supervisor_id
and p_effective_date between effective_start_date
and effective_end_date ;
select
a.address_line1
, a.address_line2
, a.address_line3
, a.town_or_city
, a.region_2
-- if the address style is CA or CA GLB then get the state from region_1
, decode(a.style ,'CA_GLB',a.region_1,'CA',a.region_1 ,
'AT_GLB',a.region_1,'AU_GLB',a.region_1 ,'CH_GBL',a.region_1,'DE',a.region_1 ,
'DE_GBL',a.region_1,'ES',a.region_1 ,'ES_GBL',a.region_1,'FR',a.region_1 ,
'FR_GBL',a.region_1,'IT_GLB',a.region_1 ,'MX',a.region_1,'MX_GLB',a.region_1 ,
'MY_GLB',a.region_1,'NL_GLB',a.region_1 ,'PL',a.region_1,'ZA',a.region_1 ,
'ZA_GLB ',a.region_1,'BZ_GLB',a.region_2,'NL',a.region_2 ,'US',a.region_2,'US_GLB',a.region_2 ,
'US_GLB_FED',a.region_2,'NL',a.region_2 ,'AE',a.ADDRESS_LINE3,
'IN',ADD_INFORMATION15,
NULL) state_ansi
, a.postal_code
, a.country
, a.region_1
, a.region_3
, a.date_from
, a.last_update_date
, a.last_updated_by
, a.last_update_login
, a.created_by
, a.creation_date
from per_addresses a
where
a.person_id = p_person_id
and p_effective_date between nvl(a.date_from, p_effective_date)
and nvl(a.date_to, p_effective_date)
and a.primary_flag = 'Y'
;
select
a.address_line1
, a.address_line2
, a.address_line3
, a.town_or_city
, a.region_2 -- 9710319
, decode(a.style ,'CA_GLB',a.region_1,'CA',a.region_1 ,
'AT_GLB',a.region_1,'AU_GLB',a.region_1 ,'CH_GBL',a.region_1,'DE',a.region_1 ,
'DE_GBL',a.region_1,'ES',a.region_1 ,'ES_GBL',a.region_1,'FR',a.region_1 ,
'FR_GBL',a.region_1,'IT_GLB',a.region_1 ,'MX',a.region_1,'MX_GLB',a.region_1 ,
'MY_GLB',a.region_1,'NL_GLB',a.region_1 ,'PL',a.region_1,'ZA',a.region_1 ,
'ZA_GLB ',a.region_1,'BZ_GLB',a.region_2,'NL',a.region_2 ,'US',a.region_2,'US_GLB',a.region_2 ,
'US_GLB_FED',a.region_2,'NL',a.region_2 ,'AE',a.ADDRESS_LINE3,
'IN',ADD_INFORMATION15,
NULL) state_ansi
, a.postal_code
, a.country
, a.region_1
, a.region_3
, a.date_from
from per_addresses a,
per_contact_relationships c,
per_all_people_f p
where
c.contact_person_id = p_person_id
and c.person_id = p.person_id
and a.person_id = p.person_id
and a.primary_flag = 'Y'
and c.rltd_per_rsds_w_dsgntr_flag = 'Y'
and p_effective_date between nvl(p.effective_start_date, p_effective_date)
and nvl(p.effective_end_date, p_effective_date)
and p_effective_date between nvl(a.date_from, p_effective_date)
and nvl(a.date_to, p_effective_date);
g_addr_last_update_date,
g_addr_last_updated_by,
g_addr_last_update_login,
g_addr_created_by,
g_addr_creation_date
;
select
a.address_line1
, a.address_line2
, a.address_line3
, a.town_or_city
, a.region_2
,decode(a.style ,'CA_GLB',a.region_1,'CA',a.region_1 ,
'AT_GLB',a.region_1,'AU_GLB',a.region_1 ,'CH_GBL',a.region_1,'DE',a.region_1 ,
'DE_GBL',a.region_1,'ES',a.region_1 ,'ES_GBL',a.region_1,'FR',a.region_1 ,
'FR_GBL',a.region_1,'IT_GLB',a.region_1 ,'MX',a.region_1,'MX_GLB',a.region_1 ,
'MY_GLB',a.region_1,'NL_GLB',a.region_1 ,'PL',a.region_1,'ZA',a.region_1 ,
'ZA_GLB ',a.region_1,'BZ_GLB',a.region_2,'NL',a.region_2 ,'US',a.region_2,'US_GLB',a.region_2 ,
'US_GLB_FED',a.region_2,'NL',a.region_2 ,'AE',a.ADDRESS_LINE3,
'IN',ADD_INFORMATION15,
NULL) state_ansi
, a.postal_code
, a.country
, a.region_1
, a.region_3
, a.date_from
from per_addresses a
where
a.person_id = p_person_id
and p_effective_date between nvl(a.date_from, p_effective_date)
and nvl(a.date_to, p_effective_date)
and a.primary_flag = 'N'
and a.address_type = 'M'
;
select
a.address_line1
, a.address_line2
, a.address_line3
, a.town_or_city
, a.region_2
, a.postal_code
, a.country
, a.region_1
, a.region_3
, a.date_from
from per_addresses a
where
a.address_id = p_address_id;
select
a.address_line1
, a.address_line2
, a.address_line3
, a.town_or_city
, a.region_2
, a.postal_code
, a.country
, a.region_1
, a.region_3
, a.date_from
from per_addresses a,
per_contact_relationships r
where
r.contact_person_id = p_person_id
and r.person_id = a.person_id
and a.town_or_city is not null
and p_effective_date between nvl(a.date_from, p_effective_date)
and nvl(a.date_to, p_effective_date)
and a.primary_flag = 'Y'
and r.rltd_per_rsds_w_dsgntr_flag = 'Y'
;
select
h.phone_number phone_home
, w.phone_number phone_work
, f.phone_number phone_fax
, m.phone_number phone_mobile
, b.phone_number phone_pager
from per_all_people_f p
, per_phones h
, per_phones w
, per_phones f
, per_phones m
, per_phones b
where p.person_id = p_person_id
and p_effective_date between nvl(p.effective_start_date, p_effective_date)
and nvl(p.effective_end_date, p_effective_date)
and h.parent_id (+) = p.person_id
and w.parent_id (+) = p.person_id
and f.parent_id (+) = p.person_id
and m.parent_id (+) = p.person_id
and b.parent_id (+) = p.person_id
and h.parent_table (+) = 'PER_ALL_PEOPLE_F'
and w.parent_table (+) = 'PER_ALL_PEOPLE_F'
and f.parent_table (+) = 'PER_ALL_PEOPLE_F'
and m.parent_table (+) = 'PER_ALL_PEOPLE_F'
and b.parent_table (+) = 'PER_ALL_PEOPLE_F'
and h.phone_type (+) = 'H1'
and w.phone_type (+) = 'W1'
and f.phone_type (+) = 'WF'
and m.phone_type (+) = 'M'
and b.phone_type (+) = 'P'
and p_effective_date between nvl(h.date_from(+), p_effective_date)
and nvl(h.date_to(+), p_effective_date)
and p_effective_date between nvl(w.date_from(+), p_effective_date)
and nvl(w.date_to(+), p_effective_date)
and p_effective_date between nvl(f.date_from(+), p_effective_date)
and nvl(f.date_to(+), p_effective_date)
and p_effective_date between nvl(m.date_from(+), p_effective_date)
and nvl(m.date_to(+), p_effective_date)
and p_effective_date between nvl(b.date_from(+), p_effective_date)
and nvl(b.date_to(+), p_effective_date)
;
select
h.phone_number phone_home
, w.phone_number phone_work
, f.phone_number phone_fax
, m.phone_number phone_mobile
from per_all_people_f p
, per_phones h
, per_phones w
, per_phones f
, per_phones m
,per_contact_relationships r
where r.contact_person_id = p_person_id
and r.rltd_per_rsds_w_dsgntr_flag = 'Y'
and p.person_id = r.person_id
and p_effective_date between nvl(p.effective_start_date, p_effective_date)
and nvl(p.effective_end_date, p_effective_date)
and h.parent_id (+) = p.person_id
and w.parent_id (+) = p.person_id
and f.parent_id (+) = p.person_id
and m.parent_id (+) = p.person_id
and h.parent_table (+) = 'PER_ALL_PEOPLE_F'
and w.parent_table (+) = 'PER_ALL_PEOPLE_F'
and f.parent_table (+) = 'PER_ALL_PEOPLE_F'
and m.parent_table (+) = 'PER_ALL_PEOPLE_F'
and h.phone_type (+) = 'H1'
and w.phone_type (+) = 'W1'
and f.phone_type (+) = 'WF'
and m.phone_type (+) = 'M'
and p_effective_date between nvl(h.date_from(+), p_effective_date)
and nvl(h.date_to(+), p_effective_date)
and p_effective_date between nvl(w.date_from(+), p_effective_date)
and nvl(w.date_to(+), p_effective_date)
and p_effective_date between nvl(f.date_from(+), p_effective_date)
and nvl(f.date_to(+), p_effective_date)
and p_effective_date between nvl(m.date_from(+), p_effective_date)
and nvl(m.date_to(+), p_effective_date)
;
select date_start
, actual_termination_date
, adjusted_svc_date
, leaving_reason
, last_update_date
, last_updated_by
, last_update_login
, created_by
, creation_date
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
from per_periods_of_service pps
where pps.person_id = p_person_id
and pps.date_start = (select max(pps1.date_start) -- this gets most recent
from per_periods_of_service pps1
where pps1.person_id = p_person_id
and pps1.date_start <= p_effective_date);
g_pos_last_update_date,
g_pos_last_updated_by,
g_pos_last_update_login,
g_pos_created_by,
g_pos_creation_date,
g_prs_flex_01,
g_prs_flex_02,
g_prs_flex_03,
g_prs_flex_04,
g_prs_flex_05,
g_prs_flex_06,
g_prs_flex_07,
g_prs_flex_08,
g_prs_flex_09,
g_prs_flex_10
;
select svc.svc_area_id,
svc.name
from ben_svc_area_f svc
, ben_svc_area_pstl_zip_rng_f svps
, ben_pstl_zip_rng_f pszip
where p_postal_code between nvl(pszip.from_value, p_postal_code)
and nvl(pszip.to_value, p_postal_code)
and pszip.pstl_zip_rng_id = svps.pstl_zip_rng_id
and svps.svc_area_id = svc.svc_area_id
and p_effective_date between nvl(svps.effective_start_date, p_effective_date)
and nvl(svps.effective_end_date, p_effective_date)
and p_effective_date between nvl(svc.effective_start_date, p_effective_date)
and nvl(svc.effective_end_date, p_effective_date)
and p_effective_date between nvl(pszip.effective_start_date, p_effective_date)
and nvl(pszip.effective_end_date, p_effective_date);
select /*+ leading(PLER) */
pler.per_in_ler_id per_in_ler_id
, pler.lf_evt_ocrd_dt lf_evt_ocrd_dt
, pler.ntfn_dt lf_evt_note_dt
, ler.ler_id ler_id
, ler.name ler_name
, ler.ler_attribute1
, ler.ler_attribute2
, ler.ler_attribute3
, ler.ler_attribute4
, ler.ler_attribute5
, ler.ler_attribute6
, ler.ler_attribute7
, ler.ler_attribute8
, ler.ler_attribute9
, ler.ler_attribute10
from
ben_per_in_ler pler,
ben_ler_f ler
where
pler.person_id = p_person_id
and pler.ler_id = ler.ler_id
and pler.per_in_ler_stat_cd = 'STRTD'
and p_effective_date between ler.effective_start_date and ler.effective_end_date
;
select bgr.name
, bgr.bng_attribute1
, bgr.bng_attribute2
, bgr.bng_attribute3
, bgr.bng_attribute4
, bgr.bng_attribute5
, bgr.bng_attribute6
, bgr.bng_attribute7
, bgr.bng_attribute8
, bgr.bng_attribute9
, bgr.bng_attribute10
from ben_benfts_grp bgr
where bgr.benfts_grp_id = p_benfts_grp_id;
select abs.abs_attendance_reason_id
, abs.absence_attendance_type_id
, abs.date_start
, abs.date_end
, abs.absence_days
, abs.last_update_date
, abs.last_updated_by
, abs.last_update_login
, abs.created_by
, abs.creation_date
, abs.attribute1
, abs.attribute2
, abs.attribute3
, abs.attribute4
, abs.attribute5
, abs.attribute6
, abs.attribute7
, abs.attribute8
, abs.attribute9
, abs.attribute10
from per_absence_attendances abs
where abs.person_id = p_person_id
and p_effective_date between nvl(abs.date_start,p_effective_date)
and nvl(abs.date_end, p_effective_date);
SELECT abt.absence_category
, abt.name abs_type
, luk.meaning abs_category
FROM per_absence_attendance_types abt
, hr_lookups luk
WHERE abt.absence_attendance_type_id = p_absence_attendance_type_id
AND abt.absence_category = luk.lookup_code
AND luk.lookup_type = 'ABSENCE_CATEGORY';
SELECT lkp.meaning abs_reason ,
abr.name -- Bug 2841958, getting the reason code
FROM per_abs_attendance_reasons abr
, hr_lookups lkp
WHERE abr.abs_attendance_reason_id = p_abs_attendance_reason_id
AND abr.name = lkp.lookup_code
AND lkp.lookup_type = 'ABSENCE_REASON';
, g_abs_last_update_date
, g_abs_last_updated_by
, g_abs_last_update_login
, g_abs_created_by
, g_abs_creation_date
, g_abs_flex_01
, g_abs_flex_02
, g_abs_flex_03
, g_abs_flex_04
, g_abs_flex_05
, g_abs_flex_06
, g_abs_flex_07
, g_abs_flex_08
, g_abs_flex_09
, g_abs_flex_10;
select ler.ler_id event_id,
ler.name event_name,
cqb.cbr_elig_perd_strt_dt strt_dt,
cqb.cbr_elig_perd_end_dt end_dt
from ben_cbr_quald_bnf cqb,
ben_cbr_per_in_ler cpl,
ben_ler_f ler,
ben_per_in_ler pil
where
cqb.quald_bnf_person_id = p_person_id
and quald_bnf_flag = 'Y'
and p_effective_date between nvl(cqb.cbr_elig_perd_strt_dt,p_effective_date)
and nvl(cqb.cbr_elig_perd_end_dt,p_effective_date)
and cqb.cbr_quald_bnf_id = cpl.cbr_quald_bnf_id
and cpl.per_in_ler_id = pil.per_in_ler_id
and pil.ler_id = ler.ler_id
and p_effective_date between nvl(ler.effective_start_date,p_effective_date)
and nvl(ler.effective_end_date ,p_effective_date)
and pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT')
;
SELECT 'x'
FROM per_person_type_usages_f ptu ,
per_person_types ppt
WHERE ptu.person_id = p_person_id
and ptu.person_type_id = ppt.person_type_id
and ppt.system_person_type in ('SRVNG_FMLY_MMBR','SRVNG_SPS')
AND p_effective_date between ptu.effective_start_date and
ptu.effective_end_date;
select sum(val)
from ben_per_bnfts_bal_f a,
ben_bnfts_bal_f b
where a.person_id = p_person_id
and a.bnfts_bal_id = b.bnfts_bal_id
and b.bnfts_bal_usg_cd = p_bnfts_bal_usg_cd
and p_effective_date between nvl(a.effective_start_date,p_effective_date)
and nvl(a.effective_end_date,p_effective_date)
and p_effective_date between nvl(b.effective_start_date,p_effective_date)
and nvl(b.effective_end_date,p_effective_date );
select a.ext_chg_evt_log_id
, a.chg_evt_cd
, a.chg_eff_dt
, trunc(a.chg_actl_dt)
, a.last_update_login
, a.prmtr_01
, a.prmtr_02
, a.prmtr_03
, a.prmtr_04
, a.prmtr_05
, a.prmtr_06
, a.old_val1
, a.old_val2
, a.old_val3
, a.old_val4
, a.old_val5
, a.old_val6
, a.new_val1
, a.new_val2
, a.new_val3
, a.new_val4
, a.new_val5
, a.new_val6
, 'BEN' chg_evt_source
from ben_ext_chg_evt_log a
where
a.person_id = p_person_id
and trunc(a.chg_actl_dt) between nvl(p_chg_actl_strt_dt, hr_api.g_sot)
and nvl(p_chg_actl_end_dt, hr_api.g_eot)
and a.chg_eff_dt between nvl(p_chg_eff_strt_dt, hr_api.g_sot)
and nvl(p_chg_eff_end_dt, hr_api.g_eot)
order by a.chg_eff_dt;
select xcv.val_1 event_group_id
from ben_ext_crit_typ xct
,ben_ext_crit_val xcv
where xct.ext_crit_prfl_id = p_ext_crit_prfl_id
and xct.ext_crit_typ_id = xcv.ext_crit_typ_id
and xct.CRIT_TYP_CD = 'CPE'
;
l_dated_table_id pay_event_updates.dated_table_id%type ;
select table_name
from pay_dated_tables
where dated_table_id = p_dated_table_id
;
select e.name
, e.cm_typ_id
, e.shrt_name
, e.pc_kit_cd
, a.per_cm_id
, a.per_in_ler_id
, a.prtt_enrt_actn_id
, nvl(b.effective_start_date,a.effective_start_date) effective_start_date
, d.proc_cd
, b.to_be_sent_dt
, b.sent_dt
, a.last_update_date
, b.last_update_date
, b.dlvry_instn_txt
, b.inspn_rqd_flag
, b.address_id
, b.per_cm_prvdd_id
, b.object_version_number
, b.effective_start_date
, c.effective_start_date
, l.ler_id
, l.name
, p.per_in_ler_stat_cd
, nvl(p.lf_evt_ocrd_dt,a.effective_start_date) lf_evt_ocrd_dt
, nvl(p.ntfn_dt,a.effective_start_date) ntfn_dt
from ben_per_cm_f a,
ben_per_cm_prvdd_f b,
ben_per_cm_trgr_f c,
ben_cm_trgr d,
ben_cm_typ_f e,
ben_per_in_ler p,
ben_ler_f l
where
a.person_id = p_person_id
and a.per_cm_id = b.per_cm_id
and a.cm_typ_id = e.cm_typ_id
and a.per_cm_id = c.per_cm_id(+)
and c.cm_trgr_id = d.cm_trgr_id(+)
and a.per_in_ler_id = p.per_in_ler_id(+)
and p.ler_id = l.ler_id(+)
and b.per_cm_prvdd_stat_cd = 'ACTIVE' -- this should be inclusion criteria.
-- the following line of code was put here for performance.
and nvl(b.to_be_sent_dt,hr_api.g_sot) between nvl(p_to_be_sent_strt_dt, hr_api.g_sot)
and nvl(p_to_be_sent_end_dt, hr_api.g_eot)
and p_effective_date between b.effective_start_date
and b.effective_end_date
and b.effective_start_date between a.effective_start_date
and a.effective_end_date
and b.effective_start_date
between nvl(c.effective_start_date,b.effective_start_date)
and nvl(c.effective_end_date,b.effective_start_date)
and b.effective_start_date between e.effective_start_date
and e.effective_end_date
and b.effective_start_date
between nvl(l.effective_start_date,b.effective_start_date)
and nvl(l.effective_end_date,b.effective_start_date)
order by b.to_be_sent_dt , b.per_cm_prvdd_id;
l_per_cm_last_update_date_va t_date ;
l_last_update_date_va t_date ;
select a.per_cm_id
, a.per_in_ler_id
, a.prtt_enrt_actn_id
, b.effective_start_date
, a.effective_start_date per_cm_eff_start_date
, b.to_be_sent_dt
, b.sent_dt
, a.last_update_date per_cm_last_update_date
, b.last_update_date
, b.dlvry_instn_txt
, b.inspn_rqd_flag
, b.address_id
, b.per_cm_prvdd_id
, b.object_version_number
, a.cm_typ_id
from ben_per_cm_f a,
ben_per_cm_prvdd_f b
where
a.person_id = p_person_id
and a.per_cm_id = b.per_cm_id
and b.per_cm_prvdd_stat_cd = 'ACTIVE' -- this should be inclusion criteria.
-- the following line of code was put here for performance.
and nvl(b.to_be_sent_dt,hr_api.g_sot) between nvl(p_to_be_sent_strt_dt, hr_api.g_sot)
and nvl(p_to_be_sent_end_dt, hr_api.g_eot)
and p_effective_date between b.effective_start_date
and b.effective_end_date
and b.effective_start_date between a.effective_start_date
and a.effective_end_date
order by b.to_be_sent_dt , b.per_cm_prvdd_id;
select c.effective_start_date ,
c.cm_trgr_id
from ben_per_cm_trgr_f c
where p_per_cm_id = c.per_cm_id
and p_effective_date
between c.effective_start_date and c.effective_end_date
;
select d.proc_cd
from ben_cm_trgr d
where p_cm_trgr_id = d.cm_trgr_id ;
select e.name
, e.shrt_name
, e.pc_kit_cd
from ben_cm_typ_f e
where p_cm_typ_id = e.cm_typ_id
and p_effective_date between e.effective_start_date
and e.effective_end_date ;
select l.ler_id
,l.name
,p.per_in_ler_stat_cd
,p.lf_evt_ocrd_dt
,p.ntfn_dt
from ben_per_in_ler p,
ben_ler_f l
where p_per_in_ler_id = p.per_in_ler_id
and p.ler_id = l.ler_id
and p_effective_date
between l.effective_start_date and l.effective_end_date
;
select ee.creator_id
from pay_element_entries_f ee
where ee.element_entry_id = p_element_entry_id
and p_effective_date between ee.effective_start_date and ee.effective_end_date;
select cpi.GROUP_PER_IN_LER_ID
,cpi.ASSIGNMENT_ID
,cpi.PERSON_ID
,cpi.SUPERVISOR_ID
,cpi.EFFECTIVE_DATE
,cpi.FULL_NAME
,cpi.BRIEF_NAME
,cpi.CUSTOM_NAME
,cpi.SUPERVISOR_FULL_NAME
,cpi.SUPERVISOR_BRIEF_NAME
,cpi.SUPERVISOR_CUSTOM_NAME
,cpi.LEGISLATION_CODE
,cpi.YEARS_EMPLOYED
,cpi.YEARS_IN_JOB
,cpi.YEARS_IN_POSITION
,cpi.YEARS_IN_GRADE
,cpi.EMPLOYEE_NUMBER
,cpi.START_DATE
,cpi.ORIGINAL_START_DATE
,cpi.ADJUSTED_SVC_DATE
,cpi.BASE_SALARY
,cpi.BASE_SALARY_CHANGE_DATE
,cpi.PAYROLL_NAME
,cpi.PERFORMANCE_RATING
,cpi.PERFORMANCE_RATING_TYPE
,cpi.PERFORMANCE_RATING_DATE
,cpi.BUSINESS_GROUP_ID
,cpi.ORGANIZATION_ID
,cpi.JOB_ID
,cpi.GRADE_ID
,cpi.POSITION_ID
,cpi.PEOPLE_GROUP_ID
,cpi.SOFT_CODING_KEYFLEX_ID
,cpi.LOCATION_ID
,cpi.PAY_RATE_ID
,cpi.ASSIGNMENT_STATUS_TYPE_ID
,cpi.FREQUENCY
,cpi.GRADE_ANNULIZATION_FACTOR
,cpi.PAY_ANNULIZATION_FACTOR
,cpi.GRD_MIN_VAL
,cpi.GRD_MAX_VAL
,cpi.GRD_MID_POINT
,cpi.GRD_QUARTILE
,cpi.GRD_COMPARATIO
,cpi.EMP_CATEGORY
,cpi.CHANGE_REASON
,cpi.NORMAL_HOURS
,cpi.EMAIL_ADDRESS
,cpi.BASE_SALARY_FREQUENCY
,cpi.NEW_ASSGN_OVN
,cpi.NEW_PERF_EVENT_ID
,cpi.NEW_PERF_REVIEW_ID
,cpi.POST_PROCESS_STAT_CD
,cpi.FEEDBACK_RATING
,cpi.OBJECT_VERSION_NUMBER
,cpi.CUSTOM_SEGMENT1
,cpi.CUSTOM_SEGMENT2
,cpi.CUSTOM_SEGMENT3
,cpi.CUSTOM_SEGMENT4
,cpi.CUSTOM_SEGMENT5
,cpi.CUSTOM_SEGMENT6
,cpi.CUSTOM_SEGMENT7
,cpi.CUSTOM_SEGMENT8
,cpi.CUSTOM_SEGMENT9
,cpi.CUSTOM_SEGMENT10
,cpi.CUSTOM_SEGMENT11
,cpi.CUSTOM_SEGMENT12
,cpi.CUSTOM_SEGMENT13
,cpi.CUSTOM_SEGMENT14
,cpi.CUSTOM_SEGMENT15
,cpi.PEOPLE_GROUP_NAME
,cpi.PEOPLE_GROUP_SEGMENT1
,cpi.PEOPLE_GROUP_SEGMENT2
,cpi.PEOPLE_GROUP_SEGMENT3
,cpi.PEOPLE_GROUP_SEGMENT4
,cpi.PEOPLE_GROUP_SEGMENT5
,cpi.PEOPLE_GROUP_SEGMENT6
,cpi.PEOPLE_GROUP_SEGMENT7
,cpi.PEOPLE_GROUP_SEGMENT8
,cpi.PEOPLE_GROUP_SEGMENT9
,cpi.PEOPLE_GROUP_SEGMENT10
,cpi.PEOPLE_GROUP_SEGMENT11
,cpi.ASS_ATTRIBUTE_CATEGORY
,cpi.ASS_ATTRIBUTE1
,cpi.ASS_ATTRIBUTE2
,cpi.ASS_ATTRIBUTE3
,cpi.ASS_ATTRIBUTE4
,cpi.ASS_ATTRIBUTE5
,cpi.ASS_ATTRIBUTE6
,cpi.ASS_ATTRIBUTE7
,cpi.ASS_ATTRIBUTE8
,cpi.ASS_ATTRIBUTE9
,cpi.ASS_ATTRIBUTE10
,cpi.ASS_ATTRIBUTE11
,cpi.ASS_ATTRIBUTE12
,cpi.ASS_ATTRIBUTE13
,cpi.ASS_ATTRIBUTE14
,cpi.ASS_ATTRIBUTE15
,cpi.ASS_ATTRIBUTE16
,cpi.ASS_ATTRIBUTE17
,cpi.ASS_ATTRIBUTE18
,cpi.ASS_ATTRIBUTE19
,cpi.ASS_ATTRIBUTE20
,cpi.ASS_ATTRIBUTE21
,cpi.ASS_ATTRIBUTE22
,cpi.ASS_ATTRIBUTE23
,cpi.ASS_ATTRIBUTE24
,cpi.ASS_ATTRIBUTE25
,cpi.ASS_ATTRIBUTE26
,cpi.ASS_ATTRIBUTE27
,cpi.ASS_ATTRIBUTE28
,cpi.ASS_ATTRIBUTE29
,cpi.ASS_ATTRIBUTE30
,cpi.CPI_ATTRIBUTE_CATEGORY
,cpi.CPI_ATTRIBUTE1
,cpi.CPI_ATTRIBUTE2
,cpi.CPI_ATTRIBUTE3
,cpi.CPI_ATTRIBUTE4
,cpi.CPI_ATTRIBUTE5
,cpi.CPI_ATTRIBUTE6
,cpi.CPI_ATTRIBUTE7
,cpi.CPI_ATTRIBUTE8
,cpi.CPI_ATTRIBUTE9
,cpi.CPI_ATTRIBUTE10
,cpi.CPI_ATTRIBUTE11
,cpi.CPI_ATTRIBUTE12
,cpi.CPI_ATTRIBUTE13
,cpi.CPI_ATTRIBUTE14
,cpi.CPI_ATTRIBUTE15
,cpi.CPI_ATTRIBUTE16
,cpi.CPI_ATTRIBUTE17
,cpi.CPI_ATTRIBUTE18
,cpi.CPI_ATTRIBUTE19
,cpi.CPI_ATTRIBUTE20
,cpi.CPI_ATTRIBUTE21
,cpi.CPI_ATTRIBUTE22
,cpi.CPI_ATTRIBUTE23
,cpi.CPI_ATTRIBUTE24
,cpi.CPI_ATTRIBUTE25
,cpi.CPI_ATTRIBUTE26
,cpi.CPI_ATTRIBUTE27
,cpi.CPI_ATTRIBUTE28
,cpi.CPI_ATTRIBUTE29
,cpi.CPI_ATTRIBUTE30
,cpi.LAST_UPDATE_DATE
,cpi.LAST_UPDATED_BY
,cpi.LAST_UPDATE_LOGIN
,cpi.CREATED_BY
,cpi.CREATION_DATE
,cpi.FEEDBACK_DATE
,pil.lf_evt_ocrd_dt
,pil.group_pl_id
,pil.PER_IN_LER_STAT_CD
,ler.name LER_NAME
,pl.name group_pl_name
,pl.PERF_REVW_STRT_DT
,pl.EMP_INTERVIEW_TYP_CD
,pl.ASG_UPDT_EFF_DATE
from ben_cwb_person_info cpi ,
ben_per_in_ler pil ,
ben_ler_f ler ,
ben_cwb_pl_dsgn pl
where cpi.person_id = p_person_id
and cpi.group_per_in_ler_id = pil.per_in_ler_id
and pil.ler_id = ler.ler_id
and pil.group_pl_id = pl.pl_id
and pl.oipl_id = -1
and pil.lf_evt_ocrd_dt = pl.lf_evt_ocrd_dt
and cpi.effective_date
between ler.effective_start_date and ler.effective_end_date
;
select name
from per_business_groups_perf bg
where business_group_id = p_business_group_id ;
select name
from hr_all_organization_units_vl org
where org.organization_id = p_org_id ;
select name
from per_positions
where position_id = p_pos_id
;
select name
from per_jobs_vl
where job_id = p_job_id
;
select name
from per_grades_vl
where grade_id = p_grade_id
;
select location_code
from hr_locations_all
where location_id = p_loc_id
;
select name
from pay_rates
where rate_id = p_rate_id
;
select name
from ben_cwb_pl_dsgn pl
where p_pl_id = pl.PL_ID
and pl.oipl_id = -1
and p_dt = pl.lf_evt_ocrd_dt
;
select group_name
from pay_people_groups
where PEOPLE_GROUP_ID = p_grp_id
;
select user_status
from PER_ASSIGNMENT_STATUS_TYPES
where ASSIGNMENT_STATUS_TYPE_ID = p_asg_stat_id
;
select meaning
from hr_lookups
where lookup_type = p_lkup_type
and lookup_code = p_lkup_code
;
select ATTRIBUTE3,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8
from ben_transaction
where TRANSACTION_ID = p_trn_id
and TRANSACTION_TYPE = p_trn_type
;
select distinct assignment_id
from per_all_assignments_f
where person_id = p_person_id
and ( primary_flag = 'Y' OR (ASSIGNMENT_TYPE ='A' and p_penserv_mode = 'N') ) -- vkodedal fix for 6798915, 9181637
and effective_start_date <= p_to_date and
effective_end_date >= p_from_date
;
select effective_start_date
from per_all_assignments_f
where Assignment_id = p_Assignment_id
and ( primary_flag = 'Y' OR (ASSIGNMENT_TYPE ='A' and p_penserv_mode = 'N') ) -- vkodedal fix for 6798915,9181637
order by effective_start_date desc ;
g_chg_last_update_login,
g_chg_prmtr_01,
g_chg_prmtr_02,
g_chg_prmtr_03,
g_chg_prmtr_04,
g_chg_prmtr_05,
g_chg_prmtr_06,
g_chg_old_val1,
g_chg_old_val2,
g_chg_old_val3,
g_chg_old_val4,
g_chg_old_val5,
g_chg_old_val6,
g_chg_new_val1,
g_chg_new_val2,
g_chg_new_val3,
g_chg_new_val4,
g_chg_new_val5,
g_chg_new_val6,
g_chg_evt_source
;
p_last_update_login => g_chg_last_update_login,
p_effective_date => g_effective_date,
p_include => l_include);
l_pay_detail_tab.delete ;
l_pay_proration_dates.delete ;
l_pay_proration_changes.delete ;
l_pay_pro_type_tab.delete ;
hr_utility.set_location(' insertining tot '|| l_pay_tot_Srno|| ' / '
||l_pay_detail_tab(l_pay).column_name,99) ;
l_pay_detail_tot_tab(l_pay_tot_Srno).update_type
:= l_pay_detail_tab(l_pay).update_type ;
l_pay_detail_tab.delete ;
and l_pay_detail_tot_tab(l_g_c).update_type
= l_pay_detail_tab(l_pay).update_type
and l_pay_detail_tot_tab(l_g_c).effective_date
= l_pay_detail_tab(l_pay).effective_date
and l_pay_detail_tot_tab(l_g_c).actual_Date
= l_pay_detail_tab(l_pay).creation_date
and nvl(l_pay_detail_tot_tab(l_g_c).column_name,'-1')
= nvl(l_pay_detail_tab(l_pay).column_name,'-1')
and nvl(l_pay_detail_tot_tab(l_g_c).datetracked_event,'-1')
= nvl(l_pay_detail_tab(l_pay).datetracked_event,'-1')
and nvl(l_pay_detail_tot_tab(l_g_c).proration_type,'-1')
= nvl(l_pay_detail_tab(l_pay).proration_type,'-1')
and nvl(l_pay_detail_tot_tab(l_g_c).change_mode,'-1')
= nvl(l_pay_detail_tab(l_pay).change_mode,'-1')
and nvl(l_pay_detail_tot_tab(l_g_c).change_values,'-1')
= nvl(l_pay_detail_tab(l_pay).change_values,'-1')
and nvl(l_pay_detail_tot_tab(l_g_c).old_value,'-1')
= nvl(l_pay_detail_tab(l_pay).old_value,'-1')
and nvl(l_pay_detail_tot_tab(l_g_c).new_value,'-1')
= nvl(l_pay_detail_tab(l_pay).new_value,'-1')
then
l_g_c_found := 'Y' ;
hr_utility.set_location(' insertining tot '|| l_pay_tot_Srno|| ' / '
||l_pay_detail_tab(l_pay).column_name,99) ;
l_pay_detail_tot_tab(l_pay_tot_Srno).update_type
:= l_pay_detail_tab(l_pay).update_type ;
hr_utility.set_location(' insertining tot '|| l_pay_tot_Srno|| ' / ' ||
l_pay_detail_tab(l_pay).column_name,99) ;
l_pay_detail_tot_tab(l_pay_tot_Srno).update_type := l_pay_detail_tab(l_pay).update_type ;
hr_utility.set_location('insertining GL '||l_pay_evt_srno||' / '||
l_pay_detail_tab(l_pay).column_name,99) ;
g_chg_update_type := g_pay_proc_evt_tab(l_pay).update_type ;
p_last_update_login => null ,
p_effective_date => g_effective_date,
p_include => l_include);
l_pay_detail_tot_tab.delete ;
g_pay_evt_group_tab.delete ;
l_per_cm_last_update_date_va ,
l_last_update_date_va ,
l_dlvry_instn_txt_va ,
l_inspn_rqd_flag_va ,
l_address_id_va ,
l_per_cm_prvdd_id_va ,
l_object_version_number_va ,
l_cm_typ_id_va
;
g_cm_last_update_date := l_per_cm_last_update_date_va(i) ;
g_cm_pvdd_last_update_date := l_last_update_date_va(i) ;
p_last_update_date => g_cm_last_update_date,
p_pvdd_last_update_date => g_cm_pvdd_last_update_date,
p_sent_dt => g_cm_sent_dt,
p_to_be_sent_dt => g_cm_to_be_sent_dt,
p_effective_date => g_effective_date,
p_include => l_include);
ben_PER_CM_PRVDD_api.update_PER_CM_PRVDD
(p_validate => null,
p_per_cm_prvdd_id => ben_ext_person.g_per_cm_prvdd_id,
p_effective_start_date=> l_dummy_start_date,
p_effective_end_date => l_dummy_end_date,
p_sent_dt => trunc(sysdate),
p_object_version_number=>ben_ext_person.g_per_cm_object_version_number,
p_effective_date => ben_ext_person.g_cm_prvdd_eff_dt,
p_datetrack_mode => 'CORRECTION');
select
pl.name pl_name,
-- opt.opt_id opt_id,
-- opt.name opt_name,
-- enrt.enrt_cvg_strt_dt cvg_strt_dt,
-- enrt.enrt_cvg_thru_dt cvg_thru_dt,
-- enrt.bnft_amt bnft_amt,
-- enrt.pgm_id pgm_id,
-- pgm.name pgm_name,
pl.pl_typ_id pl_typ_id,
ptp.name pl_typ_name
from ben_pl_f pl,
-- ben_oipl_f oipl,
-- ben_opt_f opt,
-- ben_pgm_f pgm,
ben_pl_typ_f ptp
where
pl.pl_id = l_pl_id
and g_effective_date between pl.effective_start_date
and pl.effective_end_date
--
and pl.pl_typ_id = ptp.pl_typ_id
and g_effective_date between nvl(ptp.effective_start_date, g_effective_date)
and nvl(ptp.effective_end_date, g_effective_date)
;
g_chg_update_type := null;
g_abs_last_update_date := null;
g_abs_last_updated_by := null;
g_abs_last_update_login := null;
select count(*) from ben_ext_rslt_err
where ext_rslt_id = p_ext_rslt_id --request_id = p_request_id
and typ_cd <> 'W';