[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
17-SEP-2001 SSouresr Changed the insert into the
table fnd_sessions to use the
function set_effective_date
instead.
10-Jan-2002 vpandya Converted p_tax_unit_id to
character while comparing it
to segment1 in
hr_soft_coding_keyflex table
to overcome 'invali number' &
also added dbdrv lines for gscc
30-OCT-2002 pganguly 2647412 Changed Convert_Period_Type.
Previously it used to get the
day of the week via 'DT' and
used to pass this value to get
the value from
hruserdt.get_table_value. But
this was not working in the
Psedo translated env as
user_tables/row/columns are
not translated. Now it is
getting the day number and then
based upon the number it deter
mines the day. Also changed
standard_hours_worked to do the
same.
18-NOV-2002 tclewis 2666118 Changed convert_period_type
where we are querring number per
fiscal year. to change
where sysdate between ...
to nvl(p_period_start_date,
sysdate).
19-NOV-2002 tclewis changed nvl(p_period_start_date
,sysdate) to use session date.
02-JAN-2003 mmukherj 115.9 2710358 get_flat_amounts,get_rates and
get_percentage cursors have
been changed to improve the
performance.
22-Jan-2003 vpandya 115.10 For Multi GRE functionality,
tax unit id will be stored in
segment1, segment11 and
segment12 of
hr_soft_coding_keyflex
depending on the gre type.
Changed all conditions wherever
segment1 is used for tax unit
id.
22-Jan-2003 vpandya 115.11 Added nocopy with out parameter.
09-JAN-2003 pganguly 115.12 Changed the select statements
in OT_Base_Rate which was
flagged in the Perf Repository
with cost more than 150. This
fixes bug# 3358735.
12-JUN-2004 trugless 115.13 3650170 Changed format of
v_from_annualizing_factor and
v_to_annualizing_factor from
number(10) to number(30,7) in
the Convert_Period_Type
function.
21-FEB-2005 pganguly 115.14 4118082 Added OR Condition in the select
which sets 1 to
l_eev_info_changes in the
calculate_period_earnings
procedure.
28-OCT-2005 mmukherj 115.15 Added extra parameters in
calculate_period_earnings
and convert_period_type
These parameters are coming
from contexts and will be used
to use the new core work schedule.
31-OCT-2005 mmukherj 115.16 Added calls to the core function
to calculate the actual hours
worked. This will make sure that
while calculating the hours it
looks into the core work pattern
information.
12-APR-2006 meshah 115.17 5155854 changed the select for the
condition UPPER(p_freq) <> 'HOURLY'
also changed the exception
21-NOV-2006 saikrish 115.18 5097793 Added get_earnings_and_type
14-DEC-2006 ssouresr 115.19 Corrected main cursor in
get_earnings_and_type to remove dups
*/
--
-- **********************************************************************
-- CALCULATE_PERIOD_EARNINGS
-- Description: This function performs proration for the startup elements
-- Regular Salary and Regular Wages. Proration occurs in the following
-- scenarios:
-- 1. Change of assignment status to a status which is unpaid
-- ie. unpaid leave, termination;
SELECT ASG.effective_start_date,
ASG.effective_end_date,
NVL(ASG.normal_hours, 0),
NVL(HRL.meaning, 'NOT ENTERED'),
NVL(HRL.lookup_code, 'NOT ENTERED'),
NVL(SCL.segment4, 'NOT ENTERED')
FROM per_assignments_f ASG,
per_assignment_status_types AST,
hr_soft_coding_keyflex SCL,
hr_lookups HRL
WHERE ASG.assignment_id = p_asst_id
AND ASG.business_group_id + 0 = p_bus_grp_id
AND ASG.effective_start_date > p_period_start
AND ASG.effective_end_date <= p_period_end
AND AST.assignment_status_type_id = ASG.assignment_status_type_id
AND AST.per_system_status = 'ACTIVE_ASSIGN'
AND SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
AND decode(lv_gre_type, 'T4A/RL1', segment11, 'T4A/RL2', SCL.segment12,
SCL.segment1) = to_char(p_tax_unit_id)
AND SCL.enabled_flag = 'Y'
AND HRL.lookup_code(+) = ASG.frequency
AND HRL.lookup_type(+) = 'FREQUENCY';
SELECT user_column_name
INTO v_ws_name
FROM pay_user_columns
WHERE user_column_id = v_ws_id
AND NVL(legislation_code,'CA') = 'CA';
SELECT EEV.screen_entry_value,
EEV.effective_start_date,
EEV.effective_end_date
FROM pay_element_entry_values_f EEV
WHERE EEV.element_entry_id = p_element_entry_id
AND EEV.input_value_id = p_inpval_id
AND EEV.effective_start_date > p_range_start
AND EEV.effective_end_date <= p_range_end
ORDER BY EEV.effective_start_date;
SELECT EEV.screen_entry_value,
GREATEST(EEV.effective_start_date, p_range_start_date),
EEV.effective_end_date
INTO v_earnings_entry,
v_entry_start,
v_entry_end
FROM pay_element_entry_values_f EEV
WHERE EEV.element_entry_id = p_element_entry_id
AND EEV.input_value_id = p_inpval_id
AND EEV.effective_start_date <= p_range_start_date
AND EEV.effective_end_date >= p_range_start_date
AND EEV.effective_end_date < p_range_end_date;
SELECT EEV.screen_entry_value,
EEV.effective_start_date,
LEAST(EEV.effective_end_date, p_range_end_date)
INTO v_earnings_entry,
v_entry_start,
v_entry_end
FROM pay_element_entry_values_f EEV
WHERE EEV.element_entry_id = p_element_entry_id
AND EEV.input_value_id = p_inpval_id
AND EEV.effective_start_date > p_range_start_date
AND EEV.effective_start_date <= p_range_end_date
AND EEV.effective_end_date > p_range_end_date;
select pev.screen_entry_value
from per_absence_attendance_types abt,
pay_element_entries_f pee,
pay_element_entry_values_f pev
where pev.input_value_id = abt.input_value_id
and abt.absence_category = 'V'
and v_eff_date between pev.effective_start_date
and pev.effective_end_date
and pee.element_entry_id = pev.element_entry_id
and pee.assignment_id = v_asg_id
and v_eff_date between pee.effective_start_date
and pee.effective_end_date;
select pev.screen_entry_value
from per_absence_attendance_types abt,
pay_element_entries_f pee,
pay_element_entry_values_f pev
where pev.input_value_id = abt.input_value_id
and abt.absence_category = 'S'
and v_eff_date between pev.effective_start_date
and pev.effective_end_date
and pee.element_entry_id = pev.element_entry_id
and pee.assignment_id = v_asg_id
and v_eff_date between pee.effective_start_date
and pee.effective_end_date;
select org_information5
into lv_gre_type
from hr_organization_information hoi
where hoi.org_information_context = 'Canada Employer Identification'
and hoi.organization_id = p_tax_unit_id;
SELECT PYB.input_value_id,
FCL.meaning,
FCL.lookup_code
INTO v_inpval_id,
v_pay_basis,
v_pay_basis_code
FROM per_assignments_f ASG,
per_pay_bases PYB,
hr_lookups FCL
WHERE FCL.lookup_code = PYB.pay_basis
AND FCL.lookup_type = 'PAY_BASIS'
AND FCL.application_id = 800
AND PYB.pay_basis_id = ASG.pay_basis_id
AND ASG.assignment_id = p_asst_id
AND p_date_earned
BETWEEN ASG.effective_start_date
AND ASG.effective_end_date;
SELECT IPV.input_value_id
INTO v_inpval_id
FROM pay_input_values_f IPV,
pay_element_types_f ELT
WHERE UPPER(ELT.element_name) = 'REGULAR WAGES'
AND ELT.element_type_id = IPV.element_type_id
AND p_period_start BETWEEN IPV.effective_start_date
AND IPV.effective_end_date
AND UPPER(IPV.name) = 'RATE';
SELECT IPV.input_value_id
INTO v_inpval_id
FROM pay_input_values_f IPV,
pay_element_types_f ELT
WHERE UPPER(ELT.element_name) = 'REGULAR WAGES'
AND ELT.element_type_id = IPV.element_type_id
AND p_period_start BETWEEN IPV.effective_start_date
AND IPV.effective_end_date
AND UPPER(IPV.name) = 'RATE CODE';
SELECT IPV.input_value_id
INTO v_inpval_id
FROM pay_input_values_f IPV,
pay_element_types_f ELT
WHERE UPPER(ELT.element_name) = 'REGULAR SALARY'
AND ELT.element_type_id = IPV.element_type_id
AND p_period_start BETWEEN IPV.effective_start_date
AND IPV.effective_end_date
AND UPPER(IPV.name) = 'MONTHLY SALARY';
SELECT TPT.number_per_fiscal_year
INTO v_pay_periods_per_year
FROM pay_payrolls_f PRL,
per_time_period_types TPT
WHERE TPT.period_type = PRL.period_type
AND p_period_end between PRL.effective_start_date
and PRL.effective_end_date
AND PRL.payroll_id = p_payroll_id
AND PRL.business_group_id + 0 = p_bus_grp_id;
SELECT user_column_name
INTO v_work_sched_name
FROM pay_user_columns
WHERE user_column_id = v_ws_id
AND NVL(business_group_id, p_bus_grp_id) = p_bus_grp_id
AND NVL(legislation_code,'CA') = 'CA';
select 1 INTO l_asg_info_changes
from dual
where exists (
SELECT 1
FROM per_assignments_f ASG,
per_assignment_status_types AST,
hr_soft_coding_keyflex SCL
WHERE ASG.assignment_id = p_asst_id
AND ASG.effective_start_date <= p_period_start
AND ASG.effective_end_date >= p_period_start
AND ASG.effective_end_date < p_period_end
AND AST.assignment_status_type_id = ASG.assignment_status_type_id
AND AST.per_system_status = 'ACTIVE_ASSIGN'
AND SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
AND decode(lv_gre_type, 'T4A/RL1', segment11, 'T4A/RL2', segment12,
SCL.segment1) = to_char(p_tax_unit_id)
AND SCL.enabled_flag = 'Y');
select 1 INTO l_eev_info_changes
from dual
where exists (
SELECT 1
FROM pay_element_entry_values_f EEV
WHERE EEV.element_entry_id = p_ele_entry_id
AND EEV.input_value_id+0 = v_inpval_id
AND (
(EEV.effective_start_date <= p_period_start
AND EEV.effective_end_date >= p_period_start
AND EEV.effective_end_date < p_period_end)
OR (EEV.effective_start_date between p_period_start and p_period_end)
)
);
select 1 INTO l_asg_info_changes
from dual
where exists (
SELECT 1
FROM per_assignments_f ASG,
per_assignment_status_types AST,
hr_soft_coding_keyflex SCL
WHERE ASG.assignment_id = p_asst_id
AND ASG.effective_start_date > p_period_start
AND ASG.effective_start_date <= p_period_end
AND AST.assignment_status_type_id = ASG.assignment_status_type_id
AND AST.per_system_status = 'ACTIVE_ASSIGN'
AND SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
AND decode(lv_gre_type, 'T4A/RL1', SCL.segment11, 'T4A/RL2', SCL.segment12,
SCL.segment1) = to_char(p_tax_unit_id)
AND SCL.enabled_flag = 'Y');
select 1 INTO l_eev_info_changes
from dual
where exists (
SELECT 1
FROM pay_element_entry_values_f EEV
WHERE EEV.element_entry_id = p_ele_entry_id
AND EEV.input_value_id+0 = v_inpval_id
AND EEV.effective_start_date <= p_period_start
AND EEV.effective_end_date >= p_period_start
AND EEV.effective_end_date < p_period_end);
select 1 INTO l_eev_info_changes
from dual
where exists (
SELECT 1
FROM pay_element_entry_values_f EEV
WHERE EEV.element_entry_id = p_ele_entry_id
AND EEV.input_value_id+0 = v_inpval_id
AND EEV.effective_start_date <= p_period_start
AND EEV.effective_end_date >= p_period_start
AND EEV.effective_end_date < p_period_end);
SELECT AST.per_system_status
INTO v_asg_status
FROM per_assignments_f ASG,
per_assignment_status_types AST,
hr_soft_coding_keyflex SCL
WHERE ASG.assignment_id = p_asst_id
AND p_period_start BETWEEN ASG.effective_start_date
AND ASG.effective_end_date
AND AST.assignment_status_type_id = ASG.assignment_status_type_id
AND SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
AND decode(lv_gre_type, 'T4A/RL1', segment11, 'T4A/RL2', SCL.segment12,
SCL.segment1) = to_char(p_tax_unit_id)
AND SCL.enabled_flag = 'Y';
SELECT GREATEST(ASG.effective_start_date, p_period_start),
ASG.effective_end_date,
NVL(ASG.NORMAL_HOURS, 0),
NVL(HRL.meaning, 'NOT ENTERED'),
NVL(HRL.lookup_code, 'NOT ENTERED'),
NVL(SCL.segment4, 'NOT ENTERED')
INTO v_range_start,
v_range_end,
v_asst_std_hrs,
v_asst_std_freq,
v_asst_std_freq_code,
v_work_schedule
FROM per_assignments_f ASG,
per_assignment_status_types AST,
hr_soft_coding_keyflex SCL,
hr_lookups HRL
WHERE ASG.assignment_id = p_asst_id
AND ASG.business_group_id + 0 = p_bus_grp_id
AND ASG.effective_start_date <= p_period_start
AND ASG.effective_end_date >= p_period_start
AND ASG.effective_end_date < p_period_end
AND AST.assignment_status_type_id = ASG.assignment_status_type_id
AND AST.per_system_status = 'ACTIVE_ASSIGN'
AND SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
AND decode(lv_gre_type, 'T4A/RL1', segment11, 'T4A/RL2', SCL.segment12,
SCL.segment1) = to_char(p_tax_unit_id)
AND SCL.enabled_flag = 'Y'
AND HRL.lookup_code(+) = ASG.frequency
AND HRL.lookup_type(+) = 'FREQUENCY';
OPEN get_asst_chgs; -- SELECT (ASG2)
SELECT ASG.effective_start_date,
LEAST(ASG.effective_end_date, p_period_end),
NVL(ASG.normal_hours, 0),
NVL(HRL.meaning, 'NOT ENTERED'),
NVL(HRL.lookup_code, 'NOT ENTERED'),
NVL(SCL.segment4, 'NOT ENTERED')
INTO v_range_start,
v_range_end,
v_asst_std_hrs,
v_asst_std_freq,
v_asst_std_freq_code,
v_work_schedule
FROM hr_soft_coding_keyflex SCL,
per_assignment_status_types AST,
per_assignments_f ASG,
hr_lookups HRL
WHERE ASG.assignment_id = p_asst_id
AND ASG.business_group_id + 0 = p_bus_grp_id
AND ASG.effective_start_date > p_period_start
AND ASG.effective_start_date <= p_period_end
AND ASG.effective_end_date > p_period_end
AND AST.assignment_status_type_id = ASG.assignment_status_type_id
AND AST.per_system_status = 'ACTIVE_ASSIGN'
AND SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
AND decode(lv_gre_type, 'T4A/RL1', segment11, 'T4A/RL2', SCL.segment12,
SCL.segment1) = to_char(p_tax_unit_id)
AND SCL.enabled_flag = 'Y'
AND HRL.lookup_code(+) = ASG.frequency
AND HRL.lookup_type(+) = 'FREQUENCY';
SELECT EEV.screen_entry_value
INTO v_earnings_entry
FROM pay_element_entry_values_f EEV
WHERE EEV.element_entry_id = p_ele_entry_id
AND EEV.input_value_id = v_inpval_id
--bug 5617540 starts
AND p_period_start between EEV.effective_start_date
AND EEV.effective_end_date;
SELECT GREATEST(ASG.effective_start_date, p_period_start),
ASG.effective_end_date,
NVL(ASG.NORMAL_HOURS, 0),
NVL(HRL.meaning, 'NOT ENTERED'),
NVL(HRL.lookup_code, 'NOT ENTERED'),
NVL(SCL.segment4, 'NOT ENTERED')
INTO v_range_start,
v_range_end,
v_asst_std_hrs,
v_asst_std_freq,
v_asst_std_freq_code,
v_work_schedule
FROM per_assignments_f ASG,
per_assignment_status_types AST,
hr_soft_coding_keyflex SCL,
hr_lookups HRL
WHERE ASG.assignment_id = p_asst_id
AND ASG.business_group_id + 0 = p_bus_grp_id
AND ASG.effective_start_date <= p_period_start
AND ASG.effective_end_date >= p_period_start
AND ASG.effective_end_date < p_period_end
AND AST.assignment_status_type_id = ASG.assignment_status_type_id
AND AST.per_system_status = 'ACTIVE_ASSIGN'
AND SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
AND decode(lv_gre_type, 'T4A/RL1', segment11, 'T4A/RL2', SCL.segment12,
SCL.segment1) = to_char(p_tax_unit_id)
AND SCL.enabled_flag = 'Y'
AND HRL.lookup_code(+) = ASG.frequency
AND HRL.lookup_type(+) = 'FREQUENCY';
SELECT COUNT(EEV.element_entry_value_id)
INTO l_eev_info_changes
FROM pay_element_entry_values_f EEV
WHERE EEV.element_entry_id = p_ele_entry_id
AND EEV.input_value_id = v_inpval_id
AND EEV.effective_start_date <= v_range_start
AND EEV.effective_end_date >= v_range_start
AND EEV.effective_end_date < v_range_end;
SELECT EEV.screen_entry_value
INTO v_earnings_entry
FROM pay_element_entry_values_f EEV
WHERE EEV.element_entry_id = p_ele_entry_id
AND EEV.input_value_id = v_inpval_id
AND v_range_end BETWEEN EEV.effective_start_date
AND EEV.effective_end_date;
OPEN get_asst_chgs; -- SELECT (ASG2)
SELECT COUNT(EEV.element_entry_value_id)
INTO l_eev_info_changes
FROM pay_element_entry_values_f EEV
WHERE EEV.element_entry_id = p_ele_entry_id
AND EEV.input_value_id = v_inpval_id
AND EEV.effective_start_date <= v_range_start
AND EEV.effective_end_date >= v_range_start
AND EEV.effective_end_date < v_range_end;
SELECT EEV.screen_entry_value
INTO v_earnings_entry
FROM pay_element_entry_values_f EEV
WHERE EEV.element_entry_id = p_ele_entry_id
AND EEV.input_value_id = v_inpval_id
AND v_range_end BETWEEN EEV.effective_start_date
AND EEV.effective_end_date;
SELECT ASG.effective_start_date,
LEAST(ASG.effective_end_date, p_period_end),
NVL(ASG.normal_hours, 0),
NVL(HRL.meaning, 'NOT ENTERED'),
NVL(HRL.lookup_code, 'NOT ENTERED'),
NVL(SCL.segment4, 'NOT ENTERED')
INTO v_range_start,
v_range_end,
v_asst_std_hrs,
v_asst_std_freq,
v_asst_std_freq_code,
v_work_schedule
FROM hr_soft_coding_keyflex SCL,
per_assignment_status_types AST,
per_assignments_f ASG,
hr_lookups HRL
WHERE ASG.assignment_id = p_asst_id
AND ASG.business_group_id + 0 = p_bus_grp_id
AND ASG.effective_start_date > p_period_start
AND ASG.effective_start_date <= p_period_end
AND ASG.effective_end_date > p_period_end
AND AST.assignment_status_type_id = ASG.assignment_status_type_id
AND AST.per_system_status = 'ACTIVE_ASSIGN'
AND SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
AND decode(lv_gre_type, 'T4A/RL1', segment11, 'T4A/RL2', SCL.segment12,
SCL.segment1) = to_char(p_tax_unit_id)
AND SCL.enabled_flag = 'Y'
AND HRL.lookup_code(+) = ASG.frequency
AND HRL.lookup_type(+) = 'FREQUENCY';
SELECT COUNT(EEV.element_entry_value_id)
INTO l_eev_info_changes
FROM pay_element_entry_values_f EEV
WHERE EEV.element_entry_id = p_ele_entry_id
AND EEV.input_value_id = v_inpval_id
AND EEV.effective_start_date <= v_range_start
AND EEV.effective_end_date >= v_range_start
AND EEV.effective_end_date < v_range_end;
SELECT EEV.screen_entry_value
INTO v_earnings_entry
FROM pay_element_entry_values_f EEV
WHERE EEV.element_entry_id = p_ele_entry_id
AND EEV.input_value_id = v_inpval_id
AND v_range_end BETWEEN EEV.effective_start_date
AND EEV.effective_end_date;
SELECT lookup_code
INTO v_pay_basis
FROM hr_lookups lkp
WHERE lkp.application_id = 800
AND lkp.lookup_type = 'PAY_BASIS'
AND lkp.lookup_code = p_freq;
SELECT user_column_name
INTO v_work_sched_name
FROM pay_user_columns
WHERE user_column_id = v_ws_id
AND NVL(business_group_id, p_bg) = p_bg
AND NVL(legislation_code,'CA') = 'CA';
select TPT.number_per_fiscal_year
into v_periods_per_fiscal_yr
from pay_payrolls_f PPF,
per_time_period_types TPT,
fnd_sessions fs
where PPF.payroll_id = p_payroll
and fs.session_id = USERENV('SESSIONID')
and fs.effective_date between PPF.effective_start_date and PPF.effective_end_date
and TPT.period_type = PPF.period_type;
SELECT TPT.number_per_fiscal_year
INTO v_annualizing_factor
FROM pay_payrolls_f PRL,
per_time_period_types TPT,
fnd_sessions fs
WHERE TPT.period_type = PRL.period_type
and fs.session_id = USERENV('SESSIONID')
and fs.effective_date BETWEEN PRL.effective_start_date
AND PRL.effective_end_date
AND PRL.payroll_id = p_payroll
AND PRL.business_group_id + 0 = p_bg;
SELECT TPT.number_per_fiscal_year
INTO v_annualizing_factor
FROM pay_payrolls_f PRL,
per_time_period_types TPT,
fnd_sessions fs
WHERE TPT.period_type = PRL.period_type
and fs.session_id = USERENV('SESSIONID')
and fs.effective_date BETWEEN PRL.effective_start_date
AND PRL.effective_end_date
AND PRL.payroll_id = p_payroll
AND PRL.business_group_id + 0 = p_bg;
SELECT PT.number_per_fiscal_year
INTO v_annualizing_factor
FROM per_time_period_types PT
WHERE UPPER(PT.period_type) = UPPER(p_freq);
SELECT user_column_name
INTO v_work_sched_name
FROM pay_user_columns
WHERE user_column_id = v_ws_id
AND NVL(business_group_id, p_bg) = p_bg
AND NVL(legislation_code,'CA') = 'CA';
select TPT.number_per_fiscal_year
into v_periods_per_fiscal_yr
from pay_payrolls_f PPF,
per_time_period_types TPT,
fnd_sessions fs
where PPF.payroll_id = p_payroll
and fs.session_id = USERENV('SESSIONID')
and fs.effective_date between PPF.effective_start_date and PPF.effective_end_date
and TPT.period_type = PPF.period_type;
SELECT DECODE(COUNT(session_id), 0, 'N', 'Y')
INTO v_fnd_sess_row
FROM fnd_sessions
WHERE session_id = userenv('sessionid');
select 'Y'
into l_exists
from pay_user_tables PUT,
pay_user_columns PUC
where PUC.USER_COLUMN_NAME = p_ws_name
and PUC.user_table_id = PUT.user_table_id
and PUT.user_table_name = c_ws_tab_name
and NVL(PUC.business_group_id, p_bg_id) = p_bg_id
and NVL(PUC.legislation_code,'CA') = 'CA';
select PUC.USER_COLUMN_NAME
into v_ws_name
from pay_user_tables PUT,
pay_user_columns PUC
where PUC.USER_COLUMN_ID = p_ws_name
and PUT.user_table_name = c_ws_tab_name
and PUC.user_table_id = PUT.user_table_id
and NVL(PUC.business_group_id, p_bg_id) = p_bg_id
and NVL(PUC.legislation_code,'CA') = 'CA';
SELECT decode(v_day_no,1,'SUN',2,'MON',3,'TUE',
4,'WED',5,'THU',6,'FRI',7,'SAT')
INTO v_curr_day
FROM DUAL;
SELECT DECODE(COUNT(0), 0, 'N', 'Y')
INTO v_ele_exists
FROM PAY_ELEMENT_ENTRIES_F ELE,
PAY_ELEMENT_LINKS_F ELI,
PAY_ELEMENT_TYPES_F ELT
WHERE p_date_earned
BETWEEN ELE.effective_start_date
AND ELE.effective_end_date
AND ELE.assignment_id = p_asst_id
AND ELE.element_link_id = ELI.element_link_id
AND ELI.business_group_id + 0 = p_bg_id
AND ELI.element_type_id = ELT.element_type_id
AND NVL(ELT.business_group_id, p_bg_id) = p_bg_id
AND UPPER(ELT.element_name) = UPPER(p_ele_name);
SELECT 'S'
INTO v_valid_jurisdiction
FROM PAY_US_STATES
WHERE STATE_CODE = substr(p_jurisdiction_code, 1,2);
SELECT 'S'
INTO v_valid_jurisdiction
FROM PAY_US_COUNTIES
WHERE STATE_CODE = substr(p_jurisdiction_code, 1,2)
AND COUNTY_CODE = substr(p_jurisdiction_code, 4,3);
SELECT 'S'
INTO v_valid_jurisdiction
FROM PAY_US_CITY_NAMES
WHERE STATE_CODE = substr(p_jurisdiction_code, 1,2)
AND COUNTY_CODE = substr(p_jurisdiction_code, 4,3)
AND CITY_CODE = substr(p_jurisdiction_code, 8,4)
AND PRIMARY_FLAG ='Y';
SELECT element_information3
INTO v_proc_run_type
FROM pay_element_types_f
WHERE p_date_earned
BETWEEN effective_start_date
AND effective_end_date
AND element_type_id = p_ele_type_id;
SELECT element_information3
INTO v_proc_run_type
FROM pay_element_types_f
WHERE p_date_earned
BETWEEN effective_start_date
AND effective_end_date
AND element_type_id = p_ele_type_id;
SELECT COUNT(0)
INTO v_freq_rule_exists
FROM pay_ele_payroll_freq_rules EPF
WHERE element_type_id = p_ele_type_id
AND payroll_id = p_payroll_id
AND business_group_id + 0 = p_bg_id;
SELECT end_date
INTO v_period_end_date
FROM per_time_periods
WHERE p_date_earned BETWEEN start_date AND end_date
AND payroll_id = p_payroll_id;
SELECT TPT.number_per_fiscal_year
INTO v_number_per_fy
FROM per_time_period_types TPT,
pay_payrolls_f PRL
WHERE TPT.period_type = PRL.period_type
AND PRL.business_group_id + 0 = p_bg_id
AND PRL.payroll_id = p_payroll_id;
SELECT COUNT(0)
INTO v_run_number
FROM per_time_periods PTP
WHERE PTP.end_date
BETWEEN TRUNC(p_date_earned,'YEAR')
AND v_period_end_date
AND PTP.payroll_id = p_payroll_id;
SELECT COUNT(0)
INTO v_run_number
FROM per_time_periods PTP
WHERE PTP.end_date
BETWEEN TRUNC(p_date_earned,'MONTH')
AND v_period_end_date
AND PTP.payroll_id = p_payroll_id;
SELECT 'N'
INTO v_skip_element
FROM pay_ele_payroll_freq_rules EPF,
pay_freq_rule_periods FRP
WHERE FRP.period_no_in_reset_period = v_run_number
AND FRP.ele_payroll_freq_rule_id = EPF.ele_payroll_freq_rule_id
AND EPF.business_group_id + 0 = p_bg_id
AND EPF.payroll_id = p_payroll_id
AND EPF.element_type_id = p_ele_type_id;
SELECT RRV.result_value
INTO v_dedn_proc
FROM pay_run_result_values RRV,
pay_run_results PRR,
pay_input_values_f IPV
WHERE PRR.assignment_action_id = p_assact_id
AND RRV.result_value <> 'A'
AND RRV.run_result_id = PRR.run_result_id
AND IPV.input_value_id = RRV.input_value_id
AND p_date_earned
BETWEEN IPV.effective_start_date
AND IPV.effective_end_date
AND UPPER(IPV.name) = 'DEDUCTION PROCESSING'
AND IPV.business_group_id + 0 = p_bg_id;
SELECT ECL.classification_name
INTO v_ele_class_name
FROM pay_element_types_f ELT,
pay_element_classifications ECL
WHERE ECL.classification_id = ELT.classification_id
AND ELT.business_group_id + 0 = p_bg_id
AND p_date_earned
BETWEEN ELT.effective_start_date
AND ELT.effective_end_date
AND ELT.element_type_id = p_element_type_id;
SELECT DECODE(COUNT(IPV.input_value_id), 0, 'N', 'Y')
INTO sepcheck_flag
FROM pay_element_entry_values_f EEV,
pay_element_entries_f ELE,
pay_input_values_f IPV
WHERE ELE.assignment_id = p_ass_id
AND p_date_earned
BETWEEN ELE.effective_start_date AND ELE.effective_end_date
AND ELE.element_entry_id = EEV.element_entry_id
AND p_date_earned
BETWEEN EEV.effective_start_date AND EEV.effective_end_date
AND nvl(EEV.screen_entry_value,'N') = 'N'
AND EEV.input_value_id = IPV.input_value_id
AND UPPER(IPV.name) = 'SEPARATE CHECK';
SELECT DECODE(COUNT(ELE.element_entry_id), 0, 'N', 'Y')
INTO sepcheck_flag
FROM pay_element_entries_f ELE,
pay_element_links_f ELL,
pay_element_types_f ELT,
pay_element_classifications ECL
WHERE ELE.assignment_id = p_ass_id
AND p_date_earned BETWEEN
ELE.effective_start_date and ELE.effective_end_date
AND ELE.element_link_id = ELL.element_link_id
AND p_date_earned BETWEEN
ELL.effective_start_date and ELL.effective_end_date
AND ELL.element_type_id = ELT.element_type_id
AND p_date_earned BETWEEN
ELT.effective_start_date and ELT.effective_end_date
AND ECL.classification_id = ELT.classification_id
AND UPPER(ECL.classification_name) IN ( 'EARNINGS',
'SUPPLEMENTAL EARNINGS',
'IMPUTED EARNINGS',
'NON-PAYROLL PAYMENTS')
AND NOT EXISTS
(SELECT 'X'
FROM pay_input_values_f IPV
WHERE IPV.element_type_id = ELT.element_type_id
AND p_date_earned BETWEEN
IPV.effective_start_date and IPV.effective_end_date
AND UPPER(IPV.name) = 'SEPARATE CHECK');
SELECT element_type_id
FROM pay_element_types_f
WHERE element_name = p_element_name
AND legislation_code = 'CA';
SELECT NVL(fnd_number.canonical_to_number(EEV.screen_entry_value), 0),
EEV.element_entry_id
FROM pay_element_entry_values_f EEV,
pay_element_entries_f ELE,
pay_input_values_f IPV
WHERE ELE.assignment_id = p_ass_id
AND ELE.element_entry_id = EEV.element_entry_id
AND p_date_earned
BETWEEN EEV.effective_start_date
AND EEV.effective_end_date
AND EEV.input_value_id = IPV.input_value_id
AND IPV.element_type_id = l_time_entry_ele_id
AND UPPER(IPV.name) = 'RATE';
SELECT ELT.element_type_id,
ELT.element_name,
FRA.formula_name
FROM pay_element_entries_f ELE,
pay_element_links_f ELI,
pay_element_types_f ELT,
pay_status_processing_rules_f SPR,
ff_formulas_f FRA
WHERE FRA.formula_id = SPR.formula_id
AND p_date_earned
BETWEEN SPR.effective_start_date
AND SPR.effective_end_date
AND SPR.assignment_status_type_id IS NULL
AND SPR.element_type_id = ELT.element_type_id
AND p_date_earned
BETWEEN ELE.effective_start_date
AND ELE.effective_end_date
AND ELE.assignment_id = p_ass_id
AND ELE.element_link_id = ELI.element_link_id
AND p_date_earned
BETWEEN ELI.effective_start_date
AND ELI.effective_end_date
AND ELI.element_type_id = ELT.element_type_id
AND p_date_earned
BETWEEN ELT.effective_start_date
AND ELT.effective_end_date
AND ELT.element_information8 = 'Y'
AND ELT.element_information_category IN ( 'CA_EARNINGS',
'CA_SUPPLEMENTAL EARNINGS');
SELECT fnd_number.canonical_to_number(EEV.screen_entry_value)
FROM pay_element_links_f pel,
pay_element_entries_f ele,
pay_element_entry_values_f eev,
pay_input_values_f ipv
WHERE pel.element_type_id = v_eletype_id
AND p_date_earned BETWEEN pel.effective_start_date
AND pel.effective_end_date
AND ele.element_link_id = pel.element_link_id
AND ele.assignment_id = p_ass_id
AND ele.element_entry_id = eev.element_entry_id
AND p_date_earned BETWEEN eev.effective_start_date
AND eev.effective_end_date
AND EEV.input_value_id = ipv.input_value_id
AND IPV.element_type_id = pel.element_type_id --v_eletype_id
AND IPV.name = 'Amount';
SELECT fnd_number.canonical_to_number(EEV.screen_entry_value),
EEV.element_entry_id
FROM pay_element_links_f pel,
pay_element_entries_f ele,
pay_element_entry_values_f eev,
pay_input_values_f ipv
WHERE pel.element_type_id = v_eletype_id
AND p_date_earned BETWEEN pel.effective_start_date
AND pel.effective_end_date
AND ele.element_link_id = pel.element_link_id
AND ELE.assignment_id = p_ass_id
AND ELE.element_entry_id = EEV.element_entry_id
AND p_date_earned BETWEEN EEV.effective_start_date
AND EEV.effective_end_date
AND EEV.input_value_id = IPV.input_value_id
AND IPV.element_type_id = pel.element_type_id --v_eletype_id
AND IPV.name = 'Rate';
SELECT fnd_number.canonical_to_number(EEV.screen_entry_value)
FROM pay_element_links_f pel,
pay_element_entries_f ele,
pay_element_entry_values_f eev,
pay_input_values_f ipv
WHERE pel.element_type_id = v_eletype_id
AND p_date_earned BETWEEN pel.effective_start_date
AND pel.effective_end_date
AND ele.element_link_id = pel.element_link_id
AND ele.assignment_id = p_ass_id
AND ele.element_entry_id = EEV.element_entry_id
AND p_date_earned BETWEEN EEV.effective_start_date
AND EEV.effective_end_date
AND eev.input_value_id = IPV.input_value_id
AND ipv.element_type_id = pel.element_type_id --v_eletype_id
AND ipv.name = 'Percentage';
SELECT RRV.result_value
FROM pay_run_result_values RRV,
pay_run_results RRS,
pay_input_values_f IPV,
pay_element_types_f ELT
WHERE RRV.input_value_id = IPV.input_value_id
AND RRV.run_result_id = RRS.run_result_id
AND RRS.element_type_id = ELT.element_type_id
AND RRS.assignment_action_id = p_ass_action_id
AND p_date_earned
BETWEEN IPV.effective_start_date
AND IPV.effective_end_date
AND IPV.name = 'Pay Value'
AND IPV.element_type_id = ELT.element_type_id
AND p_date_earned
BETWEEN ELT.effective_start_date
AND ELT.effective_end_date
AND ELT.element_name = 'Vertex ' || v_ele_name || ' Gross';
select start_date,
end_date
into v_range_start,
v_range_end
from per_time_periods
where payroll_id = p_pay_id
and p_date_earned between start_date and end_date;
SELECT NVL(EEV.screen_entry_value, 'NOT ENTERED')
INTO v_tew_rcode
FROM pay_element_entry_values_f EEV,
pay_element_entries_f ELE,
pay_element_types_f ELT,
pay_input_values_f IPV
WHERE ELE.assignment_id = p_ass_id
AND ELE.element_entry_id = EEV.element_entry_id
AND p_date_earned
BETWEEN EEV.effective_start_date
AND EEV.effective_end_date
AND EEV.element_entry_id = v_entry_id
AND EEV.input_value_id = IPV.input_value_id
AND UPPER(ELT.element_name) = 'TIME ENTRY WAGES'
AND ELT.element_type_id = IPV.element_type_id
AND UPPER(IPV.name) = 'RATE CODE';
SELECT COUNT(IPV.input_value_id)
INTO v_use_regwage
FROM pay_element_entry_values_f EEV,
pay_element_entries_f ELE,
pay_input_values_f IPV
WHERE ELE.assignment_id = p_ass_id
AND ELE.element_entry_id = EEV.element_entry_id
AND p_date_earned
BETWEEN EEV.effective_start_date
AND EEV.effective_end_date
AND EEV.input_value_id = IPV.input_value_id
AND IPV.element_type_id = l_reg_wages_ele_id
AND UPPER(IPV.name) = 'RATE';
SELECT NVL(fnd_number.canonical_to_number(EEV.screen_entry_value), 0),
EEV.element_entry_id
INTO v_regwage_rate,
v_entry_id
FROM pay_element_entry_values_f EEV,
pay_element_entries_f ELE,
pay_input_values_f IPV
WHERE ELE.assignment_id = p_ass_id
AND ELE.element_entry_id = EEV.element_entry_id
AND p_date_earned
BETWEEN EEV.effective_start_date
AND EEV.effective_end_date
AND EEV.input_value_id = IPV.input_value_id
AND IPV.element_type_id = l_reg_wages_ele_id
AND UPPER(IPV.name) = 'RATE';
SELECT NVL(EEV.screen_entry_value, 'NOT ENTERED')
INTO v_regwage_rcode
FROM pay_element_entry_values_f EEV,
pay_element_entries_f ELE,
pay_element_types_f ELT,
pay_input_values_f IPV
WHERE ELE.assignment_id = p_ass_id
AND ELE.element_entry_id = EEV.element_entry_id
AND p_date_earned
BETWEEN EEV.effective_start_date
AND EEV.effective_end_date
AND EEV.element_entry_id = v_entry_id
AND EEV.input_value_id = IPV.input_value_id
AND UPPER(ELT.element_name) = 'REGULAR WAGES'
AND ELT.element_type_id = IPV.element_type_id
AND UPPER(IPV.name) = 'RATE CODE';
SELECT COUNT(IPV.input_value_id)
INTO v_use_regsal
FROM pay_element_entry_values_f EEV,
pay_element_entries_f ELE,
pay_input_values_f IPV
WHERE ELE.assignment_id = p_ass_id
AND ELE.element_entry_id = EEV.element_entry_id
AND p_date_earned
BETWEEN EEV.effective_start_date
AND EEV.effective_end_date
AND EEV.input_value_id = IPV.input_value_id
AND IPV.element_type_id = l_reg_sal_ele_id
AND UPPER(IPV.name) = 'MONTHLY SALARY';
SELECT NVL(fnd_number.canonical_to_number(EEV.screen_entry_value), 0)
INTO v_regsal_mosal
FROM pay_element_entry_values_f EEV,
pay_element_entries_f ELE,
pay_input_values_f IPV
WHERE ELE.assignment_id = p_ass_id
AND ELE.element_entry_id = EEV.element_entry_id
AND p_date_earned
BETWEEN EEV.effective_start_date
AND EEV.effective_end_date
AND EEV.input_value_id = IPV.input_value_id
AND IPV.element_type_id = l_reg_sal_ele_id
AND UPPER(IPV.name) = 'MONTHLY SALARY';
SELECT NVL(EEV.screen_entry_value, 'NOT ENTERED')
INTO v_rate_rcode
FROM pay_element_entry_values_f EEV,
pay_element_entries_f ELE,
pay_element_types_f ELT,
pay_input_values_f IPV
WHERE ELE.assignment_id = p_ass_id
AND ELE.element_entry_id = EEV.element_entry_id
AND p_date_earned
BETWEEN EEV.effective_start_date
AND EEV.effective_end_date
AND EEV.element_entry_id = v_entry_id
AND EEV.input_value_id = IPV.input_value_id
AND UPPER(ELT.element_name) = UPPER(v_ele_name)
AND ELT.element_type_id = IPV.element_type_id
AND UPPER(IPV.name) = 'RATE CODE';
SELECT COUNT(0)
INTO v_rate_mult_count
FROM pay_element_entry_values_f EEV,
pay_element_entries_f ELE,
pay_element_types_f ELT,
pay_input_values_f IPV
WHERE ELE.assignment_id = p_ass_id
AND ELE.element_entry_id = EEV.element_entry_id
AND p_date_earned
BETWEEN EEV.effective_start_date
AND EEV.effective_end_date
AND EEV.element_entry_id = v_entry_id
AND EEV.input_value_id = IPV.input_value_id
AND UPPER(ELT.element_name) = UPPER(v_ele_name)
AND ELT.element_type_id = IPV.element_type_id
AND UPPER(IPV.name) = 'MULTIPLE';
SELECT NVL(EEV.screen_entry_value, 0)
INTO v_rate_multiple
FROM pay_element_entry_values_f EEV,
pay_element_entries_f ELE,
pay_element_types_f ELT,
pay_input_values_f IPV
WHERE ELE.assignment_id = p_ass_id
AND ELE.element_entry_id = EEV.element_entry_id
AND p_date_earned
BETWEEN EEV.effective_start_date
AND EEV.effective_end_date
AND EEV.element_entry_id = v_entry_id
AND EEV.input_value_id = IPV.input_value_id
AND UPPER(ELT.element_name) = UPPER(v_ele_name)
AND ELT.element_type_id = IPV.element_type_id
AND UPPER(IPV.name) = 'MULTIPLE';
SELECT DECODE(COUNT(FRP.freq_rule_period_id), 0, 'N', 'Y')
INTO v_freq_rules_exist
FROM pay_freq_rule_periods FRP,
pay_ele_payroll_freq_rules EPF
WHERE FRP.ele_payroll_freq_rule_id = EPF.ele_payroll_freq_rule_id
AND EPF.element_type_id = p_element_type_id
AND EPF.payroll_id = p_payroll_id
AND EPF.start_date <= p_date_earned;
SELECT number_per_fiscal_year
INTO v_ele_period_num_per_fy
FROM per_time_period_types TPT
WHERE UPPER(period_type) = UPPER(p_ele_period_type);
SELECT TPT.number_per_fiscal_year
INTO v_pay_period_num_per_fy
FROM per_time_period_types TPT,
pay_payrolls_f PPF
WHERE TPT.period_type = PPF.period_type
AND p_date_earned
BETWEEN PPF.effective_start_date
AND PPF.effective_end_date
AND PPF.payroll_id = p_payroll_id;
SELECT number_per_fiscal_year
INTO v_ele_period_num_per_fy
FROM per_time_period_types TPT
WHERE UPPER(period_type) = UPPER(p_ele_period_type);
SELECT TPT.number_per_fiscal_year
INTO v_pay_period_num_per_fy
FROM pay_payrolls_f PPF,
per_time_period_types TPT
WHERE TPT.period_type = PPF.period_type
AND PPF.payroll_id = p_payroll_id
AND p_date_earned
BETWEEN PPF.effective_start_date
AND PPF.effective_end_date;
SELECT COUNT(0)
INTO v_pay_periods_in_month
FROM per_time_periods PTP
WHERE PTP.end_date
BETWEEN TRUNC(p_date_earned, 'MONTH')
AND LAST_DAY(p_date_earned)
AND PTP.payroll_id = p_payroll_id;
SELECT COUNT(0)
INTO v_pay_periods_in_reset
FROM pay_ele_payroll_freq_rules EPF,
pay_freq_rule_periods FRP
WHERE FRP.period_no_in_reset_period <= v_pay_periods_in_month
AND FRP.ele_payroll_freq_rule_id = EPF.ele_payroll_freq_rule_id
AND EPF.payroll_id = p_payroll_id
AND EPF.element_type_id = p_element_type_id;
SELECT COUNT(0)
INTO v_pay_periods_in_year
FROM per_time_periods PTP
WHERE PTP.end_date
BETWEEN TRUNC(p_date_earned,'YEAR')
AND LAST_DAY(ADD_MONTHS(TRUNC(p_date_earned,'YEAR'), 11))
AND PTP.payroll_id = p_payroll_id;
SELECT DECODE(COUNT(0), 0, 1, COUNT(0))
INTO v_pay_periods_in_reset
FROM pay_ele_payroll_freq_rules EPF,
pay_freq_rule_periods FRP
WHERE FRP.period_no_in_reset_period <= v_pay_periods_in_year
AND FRP.ele_payroll_freq_rule_id = EPF.ele_payroll_freq_rule_id
AND EPF.payroll_id = p_payroll_id
AND EPF.element_type_id = p_element_type_id;
select 'Y'
into v_arrears_flag
from pay_input_values_f ipv
where ipv.name = 'Clear Arrears'
and p_date_earned BETWEEN ipv.effective_start_date
AND ipv.effective_end_date
and ipv.element_type_id = p_eletype_id;
/* Deleted a load of code above to fix 504970. If partial_flag = Y, then
try and take as much of the total deduction amount (current dedn +
arrears) and leave the rest in arrears. */
ELSIF p_partial_flag = 'Y' THEN
-- Going into arrears, not enough Net to take curr p_dedn_amt
--
p_to_arrears := (l_total_dedn - (p_net_asg_run - p_guaranteed_net)) +
(-1 * p_arrears_itd);
SELECT a.state_code||'-'||a.county_code||'-'||a.city_code
INTO l_geocode
FROM pay_us_zip_codes z,
pay_us_city_names a
WHERE a.city_name = INITCAP(p_city_name)
AND
z.state_code = a.state_code AND
z.county_code = a.county_code AND
z.city_code = a.city_code AND
l_zip_code BETWEEN z.zip_start AND z.zip_end;
SELECT state_code
INTO l_state_code
FROM pay_us_states
WHERE state_abbrev = p_state_abbrev;
SELECT a.state_code||'-'||a.county_code||'-'||a.city_code
INTO l_geocode
FROM pay_us_zip_codes z,
pay_us_city_names a,
pay_us_counties b
WHERE a.city_name = INITCAP(p_city_name)
AND a.county_code = b.county_code
AND b.county_name = INITCAP(p_county_name)
AND b.state_code = l_state_code
AND z.state_code = a.state_code AND
z.county_code = a.county_code AND
z.city_code = a.city_code AND
l_zip_code BETWEEN z.zip_start AND z.zip_end;
SELECT a.state_code||'-'||a.county_code||'-'||a.city_code
INTO l_geocode
FROM pay_us_zip_codes z,
pay_us_city_names a,
pay_us_counties b,
pay_us_states c
WHERE c.state_code = a.state_code AND
c.state_abbrev = UPPER(p_state_abbrev)
AND
b.county_name = INITCAP(p_county_name)AND
b.state_code = c.state_code
AND
a.city_name = INITCAP(p_city_name) AND
a.state_code = c.state_code AND
a.county_code = b.county_code
AND
z.state_code = c.state_code AND
z.county_code = b.county_code AND
z.city_code = a.city_code AND
l_zip_code BETWEEN z.zip_start AND z.zip_end;
SELECT peev.screen_entry_value
,piv.name
FROM pay_element_entry_values_f peev
,pay_element_entries_f pee
,pay_element_links_f pel
,pay_element_types_f pet
,pay_input_values_f piv
,per_pay_bases ppb
,per_all_assignments_f paa
WHERE paa.assignment_id = p_assignment_id
AND paa.business_group_id = p_bus_grp_id
AND p_date_earned BETWEEN paa.effective_start_date AND paa.effective_end_date
AND paa.pay_basis_id = ppb.pay_basis_id
AND ppb.input_value_id = piv.input_value_id
AND p_date_earned BETWEEN piv.effective_start_date AND piv.effective_end_date
AND piv.element_type_id = pet.element_type_id
AND p_date_earned BETWEEN pet.effective_start_date AND pet.effective_end_date
AND pet.element_type_id = pel.element_type_id
AND p_date_earned BETWEEN pel.effective_start_date AND pel.effective_end_date
AND pel.element_link_id = pee.element_link_id
AND pee.assignment_id = p_assignment_id
AND p_date_earned BETWEEN pee.effective_start_date AND pee.effective_end_date
AND pee.element_entry_id = peev.element_entry_id
AND p_date_earned BETWEEN peev.effective_start_date AND peev.effective_end_date
AND pee.element_type_id = pet.element_type_id
AND peev.input_value_id = ppb.input_value_id;