[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Description : This package is called from the AFTER INSERT/UPDATE/DELETE
User Hooks. The following are the functionalities present
in User Hook
1. Create/Update/Delete Recurring Element Entries for
Augment Elements
2. Create Tax Records for the Employee if Jurisdiction
code is entered.
3. Create/Update/Delete Premium Recalc Element Entries for
Premium Elements
Change List
-----------
Name Date Version Bug Text
-------------- ----------- ------- ------- -----------------------------
rdhingra 22-Mar-2006 115.13 5042715 R12 Performance Fixes on cursors
c_get_nrec_mop_up_dates
c_get_rec_mop_up_dates
sackumar 17-Nov-2005 115.12 4728252 Introduced a check for jurisdiction_code < 11
in Create_tax_record procedure.
kvsankar 20-SEP-2005 115.11 FLSA Modified
update_premium_mop_up_element
and
create_premium_mop_up_element
to set the Mulitple input
value of the Adjustment
element.
rdhingra 16-SEP-2005 115.10 FLSA Correcting the file version in
history and arcs
rdhingra 16-SEP-2005 115.9 FLSA Changed update_premium_mop_up_element
and create_premium_mop_up_element to
take of creation/deletion of mopup
depending on date earned given
Changed message text in
create_premium_mop_up_element
asasthan 06-SEP-2005 115.7 FLSA Changed c_get_rec_mop_up_dates
kvsankar 30-AUG-2005 115.6 FLSA Corrected GSCC Errors
kvsankar 30-AUG-2005 115.6 FLSA Added code for creating
' for FLSA Period Adjustment'
Recalc Element Entry
kvsankar 11-AUG-2005 115.5 FLSA Modified the Creator Type
to 'FL'
kvsankar 28-JUL-2005 115.4 FLSA Modified CHECK_AUGMENT_ELEM
procedure
kvsankar 27-JUL-2005 115.3 FLSA Removed code giving warning
message for Payroll change
kvsankar 27-JUL-2005 115.2 FLSA Incorporated Changes for
Penny Issue for Augments.
kvsankar 20-JUL-2005 115.1 Corrected GSCC errors and
warnings.
kvsankar 19-JUL-2005 115.0 FLSA Created
*****************************************************************************/
/******************************************************************************
Name : GET_DAILY_AMOUNT
Scope : LOCAL
Description : This function is called to get the daily amount that will
be entered in the recurring element entry created for the
Augment element.
******************************************************************************/
FUNCTION GET_DAILY_AMOUNT(
p_assignment_id in number
,p_start_date in date
,p_end_date in date
,p_inp_value_name in varchar2
,p_inp_value in number
) RETURN number IS
ln_daily_rate number;
select petf.element_name
,petf.business_group_id
,pivf.name
,peevf.screen_entry_value
from pay_element_entries_f peef
,pay_element_types_f petf
,pay_element_entry_values_f peevf
,pay_input_values_f pivf
,pay_status_processing_rules_f psprf
,ff_formulas_f fff
,pay_element_classifications pec
where peef.element_entry_id = p_element_entry_id
and petf.element_type_id = peef.element_type_id
and petf.processing_type = 'N'
and psprf.element_type_id = petf.element_type_id
and fff.formula_id = psprf.formula_id
and fff.formula_name like '%_FLAT_AMOUNT_NRRWOSI'
and peevf.element_entry_id = peef.element_entry_id
and pivf.element_type_id = peef.element_type_id
and peevf.input_value_id = pivf.input_value_id
and upper(pivf.name) in ('EARNED START DATE',
'EARNED END DATE')
and pec.classification_id = petf.classification_id
and pec.classification_name = 'Supplemental Earnings'
and pec.legislation_code = 'US'
and p_effective_start_date between peef.effective_start_date
and peef.effective_end_date
and p_effective_start_date between petf.effective_start_date
and petf.effective_end_date
and p_effective_start_date between peevf.effective_start_date
and peevf.effective_end_date
and p_effective_start_date between pivf.effective_start_date
and pivf.effective_end_date
and p_effective_start_date between psprf.effective_start_date
and psprf.effective_end_date;
select 1
from per_assignments_f paf
where paf.assignment_id = c_assignment_id
and paf.effective_start_date <= c_start_date;
select ptp.start_date
,ptp.end_date
from per_assignments_f paf,
per_time_periods ptp
where paf.assignment_id = c_assignment_id
and ptp.payroll_id = paf.payroll_id
and c_effective_start_date between paf.effective_start_date
and paf.effective_end_date
and c_effective_start_date between ptp.start_date
and ptp.end_date;
update pay_element_entry_values_f peev
set screen_entry_value = fnd_date.date_to_canonical
(ld_rec_ele_start_date)
where element_entry_value_id =
(select distinct peev1.element_entry_value_id
from pay_element_entry_values_f peev1,
pay_element_entries_f peef,
pay_input_values_f pivf
where peef.element_entry_id = p_element_entry_id
and pivf.element_type_id = peef.element_type_id
and upper(pivf.name) = 'EARNED START DATE'
and peev1.element_entry_id = peef.element_entry_id
and peev1.input_value_id = pivf.input_value_id);
update pay_element_entry_values_f peev
set screen_entry_value = fnd_date.date_to_canonical
(ld_rec_ele_end_date)
where element_entry_value_id =
(select distinct peev1.element_entry_value_id
from pay_element_entry_values_f peev1,
pay_element_entries_f peef,
pay_input_values_f pivf
where peef.element_entry_id = p_element_entry_id
and pivf.element_type_id = peef.element_type_id
and upper(pivf.name) = 'EARNED END DATE'
and peev1.element_entry_id = peef.element_entry_id
and peev1.input_value_id = pivf.input_value_id);
select petf.element_name
,petf.business_group_id
,petf.processing_type
from pay_element_entries_f peef
,pay_element_types_f petf
,pay_status_processing_rules_f psprf
,ff_formulas_f fff
where peef.element_entry_id = c_element_entry_id
and petf.element_type_id = peef.element_type_id
and psprf.element_type_id = petf.element_type_id
and fff.formula_id = psprf.formula_id
and fff.formula_name like '%_PREMIUM'
and c_effective_start_date between peef.effective_start_date
and peef.effective_end_date
and c_effective_start_date between petf.effective_start_date
and petf.effective_end_date
and c_effective_start_date between psprf.effective_start_date
and psprf.effective_end_date;
Name : DELETE_DEPENDENT_ENTRIES
Scope : LOCAL
Description : This procedure is used to delete the element entry dependent
on current element entry.
******************************************************************************/
PROCEDURE DELETE_DEPENDENT_ENTRIES(
p_element_entry_id in number
,p_assignment_id in number) IS
-- Cursor to get the Recurring element entry id using
-- Cretor ID
cursor c_get_ele_entry_id(c_element_entry_id varchar2
,c_assignment_id number) is
select max(peef.element_entry_id)
,min(peef.effective_start_date)
from pay_element_entries_f peef
where peef.creator_id = c_element_entry_id
and peef.assignment_id = c_assignment_id
and peef.creator_type = 'FL'
group by peef.element_entry_id
order by peef.element_entry_id;
hr_utility.trace('Entering PAY_US_ELEMENT_ENTRY_HOOK.DELETE_DEPENDENT_ENTRIES');
hr_entry_api.delete_element_entry (
p_dt_delete_mode => 'ZAP',
p_session_date => ld_del_start_date,
p_element_entry_id => ln_ele_entry_id);
hr_utility.trace('Leaving PAY_US_ELEMENT_ENTRY_HOOK.DELETE_DEPENDENT_ENTRIES');
END DELETE_DEPENDENT_ENTRIES;
select paf.effective_start_date Asgt_Start_Date
,paf.effective_end_date Asgt_End_Date
,pelf.effective_start_date Link_Start_Date
,pelf.effective_end_date Link_End_Date
,pelf.element_link_id Element_Link_Id
from pay_element_types_f petf,
pay_element_links_f pelf,
per_assignments_f paf
where petf.element_type_id = c_element_type_id
and petf.element_type_id = pelf.element_type_id
and paf.assignment_id = c_assignment_id
and c_effective_start_date <= paf.effective_end_date
and c_effective_end_date >= paf.effective_start_date
and c_effective_start_date <= pelf.effective_end_date
and c_effective_end_date >= pelf.effective_start_date
and (
(pelf.effective_start_date between paf.effective_start_date
and paf.effective_end_date)
or
(pelf.effective_end_date between paf.effective_start_date
and paf.effective_end_date)
or
(
pelf.effective_start_date < paf.effective_start_date
and
pelf.effective_end_date > paf.effective_end_date
)
)
and (
(pelf.payroll_id is not null and pelf.payroll_id = paf.payroll_id)
or
(pelf.link_to_all_payrolls_flag = 'Y' and paf.payroll_id is not null)
or
(pelf.payroll_id is null and pelf.link_to_all_payrolls_flag = 'N')
)
and (
pelf.organization_id = paf.organization_id
or
pelf.organization_id is null
)
and (
pelf.position_id = paf.position_id
or
pelf.position_id is null
)
and (
pelf.job_id = paf.job_id
or
pelf.job_id is null
)
and (
pelf.grade_id = paf.grade_id
or
pelf.grade_id is null
)
and (
pelf.location_id = paf.location_id
or
pelf.location_id is null
)
and (
pelf.pay_basis_id = paf.pay_basis_id
or
pelf.pay_basis_id is null
)
and (
pelf.employment_category = paf.employment_category
or
pelf.employment_category is null
)
and (
pelf.people_group_id is null
or
exists (
select 1
from pay_assignment_link_usages_f usage
where usage.assignment_id = paf.assignment_id
and usage.element_link_id = pelf.element_link_id
)
)
order by Asgt_Start_Date, Link_Start_Date;
select pivf.name
,peev.screen_entry_value
,pivf.lookup_type
from pay_input_values_f pivf
,pay_element_entry_values_f peev
where peev.element_entry_id = c_element_entry_id
and peev.input_value_id = pivf.input_value_id
and c_effective_date between peev.effective_start_date
and peev.effective_end_date
and c_effective_date between pivf.effective_start_date
and pivf.effective_end_date
order by pivf.name;
select petf.element_type_id
,pivf.input_value_id
,pivf.name
from pay_element_types_f petf
,pay_input_values_f pivf
where petf.element_name like c_element_name || ' for FLSA Calc'
and petf.business_group_id = c_business_grp_id
and pivf.element_type_id = petf.element_type_id
and c_efective_start_date between pivf.effective_start_date
and pivf.effective_end_date
and c_efective_start_date between petf.effective_start_date
and petf.effective_end_date;
select 1
from per_jobs perj
,per_jobs_tl perjtl
,per_all_assignments_f paa
where paa.assignment_id = c_assignment_id
and c_start_date <= paa.effective_end_date
and c_end_date >= paa.effective_start_date
and nvl(perj.job_information3, 'EX') = 'NEX'
and paa.job_id = perj.job_id
and paa.job_id = perjtl.job_id
and userenv('lang') = perjtl.language;
select meaning
from hr_lookups
where lookup_type = c_lookup_type
and lookup_code = c_lookup_code
and application_id = 800;
hr_entry_api.insert_element_entry (
p_effective_start_date => gd_start_date_tbl(lvr)
,p_effective_end_date => ld_dummy_end_date
,p_element_entry_id => ln_element_entry_id
,p_original_entry_id => ln_original_entry_id
,p_assignment_id => p_assignment_id
,p_element_link_id => gn_link_id_tbl(lvr)
,p_creator_type => 'FL'
,p_creator_id => p_element_entry_id
,p_entry_type => 'E' -- Normal Entry
,p_entry_information_category => null
--
-- Element Entry Values Table
--
,p_num_entry_values => l_entry_value_tbl.count()
,p_input_value_id_tbl => l_input_value_id_tbl
,p_entry_value_tbl => l_entry_value_tbl
);
hr_entry_api.delete_element_entry (
p_dt_delete_mode => 'DELETE',
p_session_date => gd_end_date_tbl(lvr),
p_element_entry_id => ln_element_entry_id);
hr_entry_api.update_element_entry
(p_dt_update_mode => 'CORRECTION'
,p_session_date => gd_end_date_tbl(lvr)
,p_element_entry_id => ln_element_entry_id
,p_num_entry_values => l_entry_value_tbl.count()
,p_input_value_id_tbl => l_input_value_id_tbl
,p_entry_value_tbl => l_entry_value_tbl);
hr_entry_api.update_element_entry
(p_dt_update_mode => 'UPDATE'
,p_session_date => gd_end_date_tbl(lvr)
,p_element_entry_id => ln_element_entry_id
,p_num_entry_values => l_entry_value_tbl.count()
,p_input_value_id_tbl => l_input_value_id_tbl
,p_entry_value_tbl => l_entry_value_tbl);
gd_start_date_tbl.delete;
gd_end_date_tbl.delete;
gn_link_id_tbl.delete;
select rtrim(ltrim(peev.screen_entry_value))
from pay_element_entry_values_f peev
,pay_element_entries_f peef
,pay_input_values_f pivf
where peef.element_entry_id = c_element_entry_id
and pivf.element_type_id = peef.element_type_id
and upper(pivf.name) = 'JURISDICTION'
and peev.element_entry_id = peef.element_entry_id
and peev.input_value_id = pivf.input_value_id
and c_effective_date between peef.effective_start_date
and peef.effective_end_date
and c_effective_date between pivf.effective_start_date
and pivf.effective_end_date
and c_effective_date between peev.effective_start_date
and peev.effective_end_date;
select 1
from pay_us_emp_state_tax_rules_f sta
where sta.assignment_id = c_assignment_id
and sta.state_code = c_state_code;
select 1
from pay_us_emp_county_tax_rules_f cnt
where cnt.assignment_id = c_assignment_id
and cnt.state_code = c_state_code
and cnt.county_code = c_county_code;
select 1
from pay_us_emp_city_tax_rules_f cty
where cty.assignment_id = p_assignment_id
and cty.state_code = c_state_code
and cty.county_code = c_county_code
and cty.city_code = c_city_code;
select time_definition_id
from pay_time_definitions
where definition_name = c_time_def_name
and legislation_code = c_legislation_code
and business_group_id IS NULL;
select /*+ use_nl(paf ptpp)*/
ptpp.end_date + 1,
ptpt.end_date
from per_assignments_f paf
,per_time_periods ptpp
,per_time_periods ptpt
where paf.assignment_id = c_assignment_id
and NVL(c_date_earned,c_effective_start_date) between paf.effective_start_date
and paf.effective_end_date
and ptpp.payroll_id = paf.payroll_id
and NVL(c_date_earned,c_effective_start_date) between ptpp.start_date
and ptpp.end_date
and NVL(c_date_earned,ptpp.end_date) between ptpt.start_date
and ptpt.end_date
and ptpt.time_definition_id = c_time_def_id
and ptpp.end_date between ptpt.start_date
and ptpt.end_date
and ptpp.end_date <> ptpt.end_date
and ptpt.time_definition_id is not null
and ptpt.payroll_id is null
and ptpp.time_definition_id is null
and ptpp.payroll_id is not null;
select /*+ use_nl(paf ptpp)*/
ptpp.end_date + 1,
ptpt.end_date
from per_assignments_f paf
,per_time_periods ptpp
,per_time_periods ptpt
where paf.assignment_id = c_assignment_id
and ptpp.payroll_id = paf.payroll_id
and ptpp.start_date <= (select max(peef.effective_end_date)
from pay_element_entries_f peef
where peef.element_entry_id = c_element_entry_id)
and ptpp.end_date >= (select max(peef.effective_end_date)
from pay_element_entries_f peef
where peef.element_entry_id = c_element_entry_id)
and ptpt.time_definition_id = c_time_def_id
and ptpp.end_date between ptpt.start_date
and ptpt.end_date
and ptpp.end_date <> ptpt.end_date
and NOT(ptpt.start_date between ptpp.start_date
and ptpp.end_date
AND
ptpt.end_date between ptpp.start_date
and ptpp.end_date)
and ptpt.time_definition_id is not null
and ptpt.payroll_id is null
and ptpp.time_definition_id is null
and ptpp.payroll_id is not null;
select count(peev.screen_entry_value)
from pay_element_entries_f peef,
pay_input_values_f pivf,
pay_element_entry_values_f peev
where peef.element_entry_id = c_element_entry_id
and pivf.element_type_id = peef.element_type_id
and pivf.name in ('Rate', 'Rate Code')
and peev.element_entry_id = peef.element_entry_id
and peev.input_value_id = pivf.input_value_id
and c_effective_date between peef.effective_start_date
and peef.effective_end_date
and c_effective_date between pivf.effective_start_date
and pivf.effective_end_date
and c_effective_date between pivf.effective_start_date
and pivf.effective_end_date
and peev.screen_entry_value is not null;
select petf.element_type_id
from pay_element_types_f petf
where petf.element_name = c_element_name
and petf.business_group_id = c_business_group_id;
select paf.effective_start_date Asgt_Start_Date
,paf.effective_end_date Asgt_End_Date
,pelf.effective_start_date Link_Start_Date
,pelf.effective_end_date Link_End_Date
,pelf.element_link_id Element_Link_Id
from pay_element_types_f petf,
pay_element_links_f pelf,
per_assignments_f paf
where petf.element_type_id = c_element_type_id
and petf.element_type_id = pelf.element_type_id
and paf.assignment_id = c_assignment_id
and c_effective_start_date <= paf.effective_end_date
and c_effective_end_date >= paf.effective_start_date
and c_effective_start_date <= pelf.effective_end_date
and c_effective_end_date >= pelf.effective_start_date
and (
(pelf.effective_start_date between paf.effective_start_date
and paf.effective_end_date)
or
(pelf.effective_end_date between paf.effective_start_date
and paf.effective_end_date)
or
(
pelf.effective_start_date < paf.effective_start_date
and
pelf.effective_end_date > paf.effective_end_date
)
)
and (
(pelf.payroll_id is not null and pelf.payroll_id = paf.payroll_id)
or
(pelf.link_to_all_payrolls_flag = 'Y' and paf.payroll_id is not null)
or
(pelf.payroll_id is null and pelf.link_to_all_payrolls_flag = 'N')
)
and (
pelf.organization_id = paf.organization_id
or
pelf.organization_id is null
)
and (
pelf.position_id = paf.position_id
or
pelf.position_id is null
)
and (
pelf.job_id = paf.job_id
or
pelf.job_id is null
)
and (
pelf.grade_id = paf.grade_id
or
pelf.grade_id is null
)
and (
pelf.location_id = paf.location_id
or
pelf.location_id is null
)
and (
pelf.pay_basis_id = paf.pay_basis_id
or
pelf.pay_basis_id is null
)
and (
pelf.employment_category = paf.employment_category
or
pelf.employment_category is null
)
and (
pelf.people_group_id is null
or
exists (
select 1
from pay_assignment_link_usages_f usage
where usage.assignment_id = paf.assignment_id
and usage.element_link_id = pelf.element_link_id
)
)
order by Asgt_Start_Date, Link_Start_Date;
SELECT date_earned
FROM pay_element_entries_f
WHERE element_entry_id = c_element_entry_id;
select peev.screen_entry_value
from pay_element_entry_values_f peev
,pay_element_entries_f peef
,pay_input_values_f pivf
where peef.element_entry_id = c_element_entry_id
and peev.element_entry_id = peef.element_entry_id
and pivf.element_type_id = peef.element_type_id
and upper(pivf.name) = upper(c_inp_value_name)
and peev.input_value_id = pivf.input_value_id
and c_effective_date between peef.effective_start_date
and peef.effective_end_date
and c_effective_date between peev.effective_start_date
and peev.effective_end_date
and c_effective_date between pivf.effective_start_date
and pivf.effective_end_date;
select distinct
pivf.input_value_id
from pay_element_types_f petf
,pay_input_values_f pivf
where petf.element_name = c_element_name
and petf.business_group_id = c_business_group_id
and pivf.element_type_id = petf.element_type_id
and pivf.name = c_inp_val_name;
hr_entry_api.insert_element_entry (
p_effective_start_date => ld_start_date_tbl(lvr)
,p_effective_end_date => ld_dummy_end_date
,p_element_entry_id => ln_element_entry_id
,p_original_entry_id => ln_original_entry_id
,p_assignment_id => p_assignment_id
,p_element_link_id => ln_link_id_tbl(lvr)
,p_creator_type => 'FL'
,p_creator_id => p_element_entry_id
,p_entry_type => 'E' -- Normal Entry
,p_entry_information_category => null
--
-- Element Entry Values Table
--
,p_num_entry_values => lv_entry_value_tbl.count()
,p_input_value_id_tbl => ln_input_value_id_tbl
,p_entry_value_tbl => lv_entry_value_tbl
);
hr_entry_api.delete_element_entry (
p_dt_delete_mode => 'DELETE',
p_session_date => ld_end_date_tbl(lvr),
p_element_entry_id => ln_element_entry_id);
update pay_element_entries_f
set original_entry_id = p_element_entry_id
where element_entry_id = ln_element_entry_id;
Name : INSERT_USER_HOOK
Scope : GLOBAL
Description : This procedure is called by AFTER INSERT Row Level handler
User Hook.
******************************************************************************/
PROCEDURE INSERT_USER_HOOK(
p_element_entry_id in number
,p_effective_start_date in date
,p_effective_end_date in date
,p_assignment_id in number
,p_element_link_id in number
,p_original_entry_id in number
,p_creator_type in varchar2
,p_entry_type in varchar2
,p_entry_information_category in varchar2) IS
BEGIN
hr_utility.trace('Entering PAY_US_ELEMENT_ENTRY_HOOK.INSERT_USER_HOOK');
hr_utility.trace('Leaving PAY_US_ELEMENT_ENTRY_HOOK.INSERT_USER_HOOK');
END INSERT_USER_HOOK;
Name : UPDATE_RECUR_ELEM_ENTRY
Scope : LOCAL
Description : This procedure is used to Update the Recurring Element Entry
associated with Non recurring Augment element.
******************************************************************************/
PROCEDURE UPDATE_RECUR_ELEM_ENTRY(
p_element_entry_id in number
,p_effective_start_date in date
,p_effective_end_date in date
,p_assignment_id_o in number
,p_element_link_id_o in number
,p_original_entry_id_o in number
,p_creator_type_o in varchar2
,p_entry_type_o in varchar2
,p_entry_information_category_o in varchar2) IS
-- Cursor to get the Recurring Element entries
-- using the Creator ID
cursor c_get_rec_elem_details(c_element_entry_id number
,c_assignment_id number) is
select distinct
peef.element_entry_id
,peef.effective_start_date
,peef.effective_end_date
,peev.screen_entry_value
from pay_element_entries_f peef,
pay_element_entry_values_f peev,
pay_input_values_f pivf
where peef.creator_id = c_element_entry_id
and peef.assignment_id = c_assignment_id
and peef.creator_type = 'FL'
and peev.element_entry_id = peef.element_entry_id
and pivf.element_type_id = peef.element_type_id
and upper(pivf.name) = 'DAILY AMOUNT'
and peev.input_value_id = pivf.input_value_id
and peev.effective_start_date between peef.effective_start_date
and peef.effective_end_date
order by peef.effective_start_date;
select pivf.name
,peevf.screen_entry_value
from pay_element_entries_f peef
,pay_element_entry_values_f peevf
,pay_input_values_f pivf
where peef.element_entry_id = c_element_entry_id
and peevf.element_entry_id = peef.element_entry_id
and pivf.element_type_id = peef.element_type_id
and peevf.input_value_id = pivf.input_value_id
and upper(pivf.name) in ('EARNED START DATE',
'EARNED END DATE',
'AMOUNT')
and c_effective_start_date between peef.effective_start_date
and peef.effective_end_date
and c_effective_start_date between peevf.effective_start_date
and peevf.effective_end_date
and c_effective_start_date between pivf.effective_start_date
and pivf.effective_end_date;
select pivf.input_value_id
from pay_element_types_f petf,
pay_input_values_f pivf
where petf.element_name = c_element_name || ' for FLSA Calc'
and petf.business_group_id = c_business_group_id
and pivf.element_type_id = petf.element_type_id
and upper(pivf.name) = 'DAILY AMOUNT';
hr_utility.trace('Entering PAY_US_ELEMENT_ENTRY_HOOK.UPDATE_RECUR_ELEM_ENTRY');
hr_entry_api.delete_element_entry (
p_dt_delete_mode => 'ZAP',
p_session_date => l_del_start_date_tbl(lvar),
p_element_entry_id => l_elem_entry_id_tbl(lvar));
hr_entry_api.update_element_entry
(p_dt_update_mode => 'CORRECTION'
,p_session_date => l_del_start_date_tbl(lvar)
,p_element_entry_id => l_elem_entry_id_tbl(lvar)
,p_num_entry_values => 1
,p_input_value_id_tbl => l_input_value_id_tbl
,p_entry_value_tbl => l_entry_value_tbl);
hr_utility.trace('Leaving PAY_US_ELEMENT_ENTRY_HOOK.UPDATE_RECUR_ELEM_ENTRY');
END UPDATE_RECUR_ELEM_ENTRY;
Name : UPDATE_PREMIUM_MOP_UP_ELEMENT
Scope : LOCAL
Description : This function is used for updating the MOP UP element
for Premium. This procedure creates a MOP UP element
only if the FLSA Period crossed over Payroll Period in
question and the input values Rate/Rate Code both are not
specified.
******************************************************************************/
PROCEDURE UPDATE_PREMIUM_MOP_UP_ELEMENT(
p_element_entry_id in number
,p_effective_start_date in date
,p_effective_end_date in date
,p_assignment_id in number
,p_element_link_id in number
,p_original_entry_id in number
,p_creator_type in varchar2
,p_entry_type in varchar2
,p_entry_information_category in varchar2) IS
cursor c_get_rate_entry_count(c_element_entry_id number
,c_effective_date date) is
select count(peev.screen_entry_value)
from pay_element_entries_f peef,
pay_input_values_f pivf,
pay_element_entry_values_f peev
where peef.element_entry_id = c_element_entry_id
and pivf.element_type_id = peef.element_type_id
and pivf.name in ('Rate', 'Rate Code')
and peev.element_entry_id = peef.element_entry_id
and peev.input_value_id = pivf.input_value_id
and c_effective_date between peef.effective_start_date
and peef.effective_end_date
and c_effective_date between pivf.effective_start_date
and pivf.effective_end_date
and c_effective_date between peev.effective_start_date
and peev.effective_end_date
and peev.screen_entry_value is not null;
select 'Exist'
from pay_element_entries_f peef
where peef.creator_id = c_element_entry_id
and peef.assignment_id = c_assignment_id
and peef.creator_type = 'FL';
select nvl(max(peef.effective_end_date),
fnd_date.canonical_to_date('4712/12/31'))
from pay_element_entries_f peef
where peef.element_entry_id = c_element_entry_id
and peef.assignment_id = c_assignment_id;
SELECT date_earned
FROM pay_element_entries_f
WHERE element_entry_id = c_element_entry_id;
select /*+ use_nl(paf ptpp)*/
ptpp.end_date + 1,
ptpt.end_date
from per_assignments_f paf
,per_time_periods ptpp
,per_time_periods ptpt
where paf.assignment_id = c_assignment_id
and NVL(c_date_earned,c_effective_start_date) between paf.effective_start_date
and paf.effective_end_date
and ptpp.payroll_id = paf.payroll_id
and NVL(c_date_earned,c_effective_start_date) between ptpp.start_date
and ptpp.end_date
and NVL(c_date_earned,ptpp.end_date) between ptpt.start_date
and ptpt.end_date
and ptpt.time_definition_id = c_time_def_id
and ptpp.end_date between ptpt.start_date
and ptpt.end_date
and ptpp.end_date <> ptpt.end_date
and ptpt.time_definition_id is not null
and ptpt.payroll_id is null
and ptpp.time_definition_id is null
and ptpp.payroll_id is not null;
select peev.screen_entry_value
from pay_element_entry_values_f peev
,pay_element_entries_f peef
,pay_input_values_f pivf
where peef.element_entry_id = c_element_entry_id
and peev.element_entry_id = peef.element_entry_id
and pivf.element_type_id = peef.element_type_id
and upper(pivf.name) = upper(c_inp_value_name)
and peev.input_value_id = pivf.input_value_id
and c_effective_date between peef.effective_start_date
and peef.effective_end_date
and c_effective_date between peev.effective_start_date
and peev.effective_end_date
and c_effective_date between pivf.effective_start_date
and pivf.effective_end_date;
select distinct
peef.element_entry_id
,peef.effective_start_date
from pay_element_entries_f peef
where peef.creator_id = c_element_entry_id
and peef.assignment_id = c_assignment_id
and peef.creator_type = 'FL'
order by peef.effective_start_date;
select distinct
pivf.input_value_id
from pay_element_types_f petf
,pay_input_values_f pivf
where petf.element_name = c_element_name
and petf.business_group_id = c_business_group_id
and pivf.element_type_id = petf.element_type_id
and pivf.name = c_inp_val_name;
lb_delete_mopup boolean;
hr_utility.trace('Entering PAY_US_ELEMENT_ENTRY_HOOK.UPDATE_PREMIUM_MOP_UP_ELEMENT');
lb_delete_mopup := FALSE;
lb_delete_mopup := FALSE;
lb_delete_mopup := TRUE;
if ((ln_count > 0) OR (lb_delete_mopup)) then
-- Delete the Mop up as we do not require it now
hr_utility.trace('Deleting Mop Up Element');
DELETE_DEPENDENT_ENTRIES(p_element_entry_id
,p_assignment_id);
hr_entry_api.update_element_entry
(p_dt_update_mode => 'CORRECTION'
,p_session_date => l_del_start_date_tbl(lvar)
,p_element_entry_id => l_elem_entry_id_tbl(lvar)
,p_num_entry_values => l_entry_value_tbl.count()
,p_input_value_id_tbl => l_input_value_id_tbl
,p_entry_value_tbl => l_entry_value_tbl);
hr_utility.trace('Leaving PAY_US_ELEMENT_ENTRY_HOOK.UPDATE_PREMIUM_MOP_UP_ELEMENT');
END UPDATE_PREMIUM_MOP_UP_ELEMENT;
Name : UPDATE_USER_HOOK
Scope : GLOBAL
Description : This procedure is called by AFTER UPDATE Row Level handler
User Hook.
******************************************************************************/
PROCEDURE UPDATE_USER_HOOK(
p_element_entry_id in number
,p_effective_start_date in date
,p_effective_end_date in date
,p_assignment_id_o in number
,p_element_link_id_o in number
,p_original_entry_id_o in number
,p_creator_type_o in varchar2
,p_entry_type_o in varchar2
,p_entry_information_category_o in varchar2) IS
BEGIN
hr_utility.trace('Entering PAY_US_ELEMENT_ENTRY_HOOK.UPDATE_USER_HOOK');
UPDATE_RECUR_ELEM_ENTRY(p_element_entry_id
,p_effective_start_date
,p_effective_end_date
,p_assignment_id_o
,p_element_link_id_o
,p_original_entry_id_o
,p_creator_type_o
,p_entry_type_o
,p_entry_information_category_o);
UPDATE_PREMIUM_MOP_UP_ELEMENT(p_element_entry_id
,p_effective_start_date
,p_effective_end_date
,p_assignment_id_o
,p_element_link_id_o
,p_original_entry_id_o
,p_creator_type_o
,p_entry_type_o
,p_entry_information_category_o);
hr_utility.trace('Leaving PAY_US_ELEMENT_ENTRY_HOOK.UPDATE_USER_HOOK');
END UPDATE_USER_HOOK;
Name : DELETE_DEPENDENT_ENTRIES
Scope : LOCAL
Description : This procedure is called by AFTER DELETE Row Level handler
User Hook.
******************************************************************************/
PROCEDURE DELETE_PREM_MOP_UP_ELE_ENTRY(
p_element_entry_id in number
,p_effective_start_date in date
,p_effective_end_date in date
,p_assignment_id in number
,p_element_link_id in number
,p_original_entry_id in number
,p_creator_type in varchar2
,p_entry_type in varchar2
,p_entry_information_category in varchar2) IS
-- Get the End date of the recurring element.
cursor c_get_entry_end_date(c_element_entry_id number
,c_assignment_id number) is
select max(peef.effective_end_date)
from pay_element_entries_f peef
where peef.element_entry_id = c_element_entry_id
and peef.assignment_id = c_assignment_id;
('Entering PAY_US_ELEMENT_ENTRY_HOOK.DELETE_PREM_MOP_UP_ELE_ENTRY');
DELETE_DEPENDENT_ENTRIES(p_element_entry_id
,p_assignment_id);
('Leaving PAY_US_ELEMENT_ENTRY_HOOK.DELETE_PREM_MOP_UP_ELE_ENTRY');
END DELETE_PREM_MOP_UP_ELE_ENTRY;
Name : DELETE_USER_HOOK
Scope : GLOBAL
Description : This procedure is called by AFTER DELETE Row Level handler
User Hook.
******************************************************************************/
PROCEDURE DELETE_USER_HOOK(
p_element_entry_id in number
,p_effective_start_date in date
,p_effective_end_date in date
,p_assignment_id_o in number
,p_element_link_id_o in number
,p_original_entry_id_o in number
,p_creator_type_o in varchar2
,p_entry_type_o in varchar2
,p_entry_information_category_o in varchar2) IS
-- Check if the element entry Exists
cursor c_chk_elem_entry_exists(c_element_entry_id number) is
select 'Exist'
from pay_element_entries_f
where element_entry_id = c_element_entry_id;
hr_utility.trace('Entering PAY_US_ELEMENT_ENTRY_HOOK.DELETE_USER_HOOK');
DELETE_DEPENDENT_ENTRIES(p_element_entry_id
,p_assignment_id_o);
DELETE_PREM_MOP_UP_ELE_ENTRY(p_element_entry_id
,p_effective_start_date
,p_effective_end_date
,p_assignment_id_o
,p_element_link_id_o
,p_original_entry_id_o
,p_creator_type_o
,p_entry_type_o
,p_entry_information_category_o);
hr_utility.trace('Leaving PAY_US_ELEMENT_ENTRY_HOOK.DELETE_USER_HOOK');
END DELETE_USER_HOOK;