[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
01-AUG-94 hparicha 40.4 G1185 Updated Check_Dedn_Freq to
check PERIOD num in month/year
instead of Run in month/year.
05-AUG-94 hparicha 40.5 G1188 Updated Separate_Check_Skip to
verify when a not-null 'Dedn
Proc' RRV is found that 'Sep
Check' or 'Tax Separately'
is also in fact marked as 'Y'.
Ie. the scenario could exist
where Dedn Proc is 'PTT' but
Tax Sep/Sep Check both = 'N' -
in which case the deduction
should process as normal.
26-OCT-94 hparicha 40.6 G1342 Included as part of this STU
bug. Added "addr_val" for
use by VERTEX formulae and
"get_geocode".
14-NOV-94 hparicha 40.7 G1668 Optimization of addr_val.
15-NOV-94 hparicha 40.8 G1679 More optimization of fns -
proration, arrearage, period
type conversion.
24-NOV-94 rfine 40.9 G1725 Suppressed index on
business_group_id
01-DEC-94 hparicha 40.10 G1529 We longer require TaxSep earns
G1601 to be paid on sepchecks.
This is a reversal of G1188.
Dedn Proc is now independant
of Sep Check and Tax Sep
processing.
09-DEC-94 hparicha 40.11 G1530 Fixup of us_jurisdiction_val.
04-JAN-95 hparicha 40.12 G1565 Vacation/Sick correlation
to Regular Pay - changes to
Calc Period Earns.
Also need separate fn to calc
Vac/Sick Pay as well as a fn
to check for entry of Vac/Sick
Hours against an accrual plan.
25-APR-95 hparicha 40.13 Fixed addr_val -aka GET_GEOCODE
such that INITCAP is used
instead of UPPER when comparing
City/County/State names.
Latest Vertex data has been
converted to initcaps.
Actually, just UPPER both sides
of compare to remove any doubt.
Also needed
FND_NUMBER.CANONICAL_TO_NUMBER
on zip code for "between"
comparisons.
- not sure why this is needed,
- but the sql would not work
- otherwise.
26-APR-95 hparicha 40.14 No, idiot, UPPER on both sides
trashes the index...back to
INITCAP on literal in addr_val.
01-MAY-95 gpaytonm 40.15 addr_val select only doesn't
look at canada a.state_code
!= 70. This fixes the invalid
number error
01-MAY-95 gpaytonm 40.16 Removed prior fix and removed
FND_NUMBER.CANONICAL_TO_NUMBER
from addr_val select instead
09-MAY-95 gpaytonm 40.17 Removed remaining
FND_NUMBER.CANONICAL_TO_NUMBER's from addr_Val
15-MAY-95 hparicha 40.18 Added UPPER to PERIOD_TYPE
comparison in Convert_Period_
Type.
23-JUN-95 gpaytonm 40.19 Changed references to
pay_us_cities to
pay_us_city_names and
pay_us_zip_codes where
appropriate
29-JUN-95 spanwar 40.20 Changed addr_val to convert
'UNKN' city codes to '0000'
07-JUL-95 hparicha 40.21 290249 Fixed arrearage function to
correctly handle partial dedns
when clearing arrears.
08-JUL-95 hparicha 40.22 264781 Proration function needs mod
to algo for semi-monthly and
monthly employees.
14-AUG-95 tgrisco 40.23 Changed addr_val to convert
'U' city codes to '0000'.
22-Nov-95 ssdesai 40.24 substr zipcode to 5 chars
in addr_val allowing for
zip code extension.
10-JAN-96 hparicha 40.27 Cleaned up all references to
specific period types
and pay bases in relation to
proration, regular salary,
regular wages, calculations of
hourly rates. Affected
functions: calculate_period_earn
convert_period_type,
ot_base_rate.
Bug 334245 - Add check for
primary flag
on pay_us_city_names select.
??? ??? 40.28 ??? ???
4th July 1996 hparicha 40.29 360549, 366215, 378753
Removed consumed_entry fn.
Package too large.
Moved into pay_consumed_entry
package. Replaced by function
of same name in new package.
353434, 368242
Fixed number width for
total hours variables in
convert_period_type,
work_schedule_total_hours,
and standard_hours_worked.
25-Jul-96 P Jones 40.30 Removed user_errors
1st Nov 1996 hparicha 40.31 408507 Removing vacation and sick
accrual pay and hours
calculation from regular
salary and wages proration
calc - ie.
Calculate_Period_Earnings
14th Nov 1996 hparicha 40.32 408507 Undoing 40.31 - see README
for 408507 for explanation.
8th Dec 1997 kmundair 40.33(110.1) 509120 Overloaded addr_val.
24th Feb 1998 tbattoo 40.33 572081 Changed date rng prorate EEV.
26th Feb 1998 arashid 40.35 504970 Fix unhandled divide-by-zero
exception. The change made
is to consolidate processing
if arrearage is on, but the
whole amount cannot be cleared.
3rd Mar 1998 arashid 40.36 504970 Fixed the 504970 fix to take
the current deduction if
PARTIAL_FLAG = 'N' and (net -
guaranteed) >= current
deduction.
4th Jun 1998 jarthurt 110.2 408507 Alter the Vacation and Sick Pay
cursors to pick up absences not
tied to an accrual plan as well
as those that are.
17-AUG-1998 ekim 110.4 716066 Length has been changed
from (10,7) to (15,7)
for v_hrs_per_wk,
v_hrs_per_range
v_asst_std_hrs,
v_hours_in_range.
23-NOV-1998 tbattoo Added frequency rules
functionality to core
10-MAR-1999 ahanda 115.5 803662 Modified function
Calculate_Period_Earnings.
Modified select statments
which checks for mid
change in asgn to use Exists,
as it
will return multiple records
if the assgn id
has been changed more than
once in a Pay Period.
21-Apr-1999 scgrant 115.6 Multi-radix changes.
Description: User-Defined
Functions required for US
implementations.
19-MAY-99 gpaytonm 115.12 Removed hr_utility.trace
20-MAY-99 VMehta 115.13 Converted p_tax_unit_id to
character while comparing it to segment1 in hr_soft_coding_
keyflex table to overcome
'invali number' error.
Bug 894503.
25-MAY-1999 mmukherj 115.14 868824 Changes made to include
legislation code in selecting
elements, otherwise it might
fetch multiple row, because of
same element present for canada
28-Sep-1999 djoshi added function for
pay_us_country
28-OCT-1999 mreid 115.17 Changed territory short name
retrieve to come from VL view.
09-may-2000 vmehta 115.18 863771 Modified function
work_schedule_total_hours to
check for business_group_id and
user_table_id while obtaining
user_column_name from
pay_user_columns
01-JUL-2000 vmehta 115.19 Added a check of the last 3 characters
in the city name. This is to fix a
problem where Air Force Bases (in the
City_name would error during processing
of a payroll run. Example "Elgin AFB",
this is how the name appears in the
pay_us_city_names table. however the
addr_val function would perform an
initcap to the p_city_name parameter.
This caused no data found. Now if the
last 3 characters are "AFB" do not
perform the Initcap. Bug 1266054
21-DEC-2000 ekim 115.22 ******* LEAP FROGGED *******
DO NOT USED v115.21.
This is leap frogged from 115.19.
Bug 1541873. Moved city_name check
right after the main begin of addr_val
as when skip_flag of 'Y' is passed
the city_name check is skipped.
23-JAN-2001 ahanda 115.23 Added the Calculation Type functoinality
to Package. The Hourly Calculation will
be done depending on the calculation
rule given on the payroll define screen.
The default calculation rule is
Annulization, and the other available
rule is Standard.
Also added ORDERED hint in function
addr_val(Bug 1484707).
09-MAR-2001 ssarma 115.24 Added a new parameter
p_hour_calc_override to
convert_period_type function.
17-SEP-2001 ptitoren 115.25 Changed insert_session_row procedures
to call dt_fndate.set_effective_date
to shield our code from future
FND_SESSION table changes.
07-FEB-2002 tclewis 115.26 Modified the code around dt_fndate
above to first check for the
existence of a row in the FND_Sessions
table before making the call.
13-FEB-2002 rsirigir 115.27 Bug 2196352
changed datatype/datalengths
for three variables
from FUNCTION Convert_Period_Type,from
v_from_stnd_factor NUMBER(10)
v_from_annualizing_factor NUMBER(10)
v_to_annualizing_factor NUMBER(10)
to
v_from_stnd_factor NUMBER(30,7)
v_from_annualizing_factor NUMBER(30,7)
v_to_annualizing_factor NUMBER(30,7)
05-APR-2001 rsirigir 115.28 Bug 1877889
changed to select the work
schedule defined
at the business group level instead of
hardcoding the default work schedule
(COMPANY WORK SCHEDULES ) to the
variable c_ws_tab_name
13-AUG-2002 ahanda 115.30 Changed get_flat_amounts, get_rates and
get_percentages for performance.
13-NOV-2002 tclewis 115.31 2666118 Changed work_schedule_total_hours
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 115.31 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 115.32 Fixed GSCC compliance warning with
default parameter values.
19-nov-2002 tclewis 115.33 changed nvl(p_period_start_date, sysdate)
to use fnd_sessions.
07-Jan-2003 ekim 115.35 Made performance change in function
OT_Base_Rate as :
Added date joins and business_group_id
and legislation_code join. Also added
+ ORDERED to avoid merge join.
Did no change to the queries which gets
the following variables since the cost
is already low (cost=9)
v_rate_multiple, v_rate_mult_count,
v_rate_code
Added element_type_id join in a query
which gets v_dedn_proc for function
separate_check_skip.
09-Jan-2003 ekim 115.36 GSCC warning fix for nocopy.
09-Jan-2003 meshah 115.37 changed the sql for Rate, Rate Code and
Monthly Salary in
Calculate_Period_Earnings.
25-MAR-2003 tclewis 115.38 Modified the query at the
beginning of the
Calculate_Period_earnings which
looks for
l_eev_info_changes (element entry value)
change for the salary element.
It now also
looks for a salary element starting
in the
middle of the pay period.
25-Jul-2003 vmehta 115.39 Modified get_annulization_factor (within
convert_period_type) to use fnd_sessions
to get the effective date instead of
using p_effecive_end_date. (Bug 3067262)
07-JAN-2004 trugless 115.40 Commented out the following code for bug
3271413 in the SELECT (ASG1.2) section
"AND EEV.effective_end_date
< p_period_end;"
06-DEC-2005 sackumar 115.51 4750302 Modified a Select statement in Calculate_Period_Earnings.
30-DEC-2005 schauhan 115.52 4868637 Modified the query.
21-AUG-2006 rpasumar 115.54 5343679 Modified the query.
31-OCT-2006 rpasumar 115.55 5629688 Removed the ORDERED hint.
13-NOV-2007 svannian 115.56 6319565 Modified the deduction amt
calculation during Negative Net Salary.
30-MAR-2011 sjawid 115.62 11787061 Modified function addr_val
10-Aug-2011 emunisek 115.63 12588037 Added function catchup_type_details, which
will be used to store the catchup_type selected
for 403/457 Catchup Elements. The stored value
will be used in Roth Formulas.
10-Aug-2011 emunisek 115.64 12588037 Corrected GSCC Errors
01-Nov-2012 emunisek 115.66 14385437 Added changes to check the value set for Profile
Option 'PAY_US_DIRECT_BALANCE_START_YEAR' to use
the Direct US Federal Balances approach.
*/
--
-- **********************************************************************
-- CALCULATE_PERIOD_EARNINGS
-- Description: This fn 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(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 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(business_group_id, p_bg_id) = p_bg_id
AND NVL(legislation_code,'US') = 'US';
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;
hr_utility.trace('Select EEV3');
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 fnd_number.canonical_to_number(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 fnd_number.canonical_to_number(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 PYB.input_value_id,
FCL.meaning
INTO v_inpval_id,
v_pay_basis
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 ELT.element_name = 'Regular Wages'
and p_period_start BETWEEN ELT.effective_start_date
AND ELT.effective_end_date
and ELT.element_type_id = IPV.element_type_id
and p_period_start BETWEEN IPV.effective_start_date
AND IPV.effective_end_date
and IPV.name = 'Rate'
and ELT.legislation_code = 'US';
SELECT IPV.input_value_id
INTO v_inpval_id
FROM pay_input_values_f IPV,
pay_element_types_f ELT
WHERE ELT.element_name = 'Regular Wages'
and p_period_start BETWEEN ELT.effective_start_date
AND ELT.effective_end_date
and ELT.element_type_id = IPV.element_type_id
and p_period_start BETWEEN IPV.effective_start_date
AND IPV.effective_end_date
and IPV.name = 'Rate Code'
and ELT.legislation_code = 'US';
SELECT IPV.input_value_id
INTO v_inpval_id
FROM pay_input_values_f IPV,
pay_element_types_f ELT
WHERE ELT.element_name = 'Regular Salary'
and p_period_start BETWEEN ELT.effective_start_date
AND ELT.effective_end_date
and ELT.element_type_id = IPV.element_type_id
and p_period_start BETWEEN IPV.effective_start_date
AND IPV.effective_end_date
and IPV.name = 'Monthly Salary'
and ELT.legislation_code = 'US';
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,'US') = 'US';
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 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 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_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 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(SCL.segment4, 'NOT ENTERED')
INTO v_range_start,
v_range_end,
v_asst_std_hrs,
v_asst_std_freq,
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 SCL.segment1 = TO_CHAR(p_tax_unit_id)
AND SCL.enabled_flag = 'Y'
AND HRL.lookup_code(+) = ASG.frequency
AND HRL.lookup_type(+) = 'FREQUENCY';
hr_utility.trace('ONLY ASG , select MULTIASG');
OPEN get_asst_chgs; -- SELECT (ASG2 MULTIASG)
hr_utility.trace('ONLY ASG , select END_SPAN_RECORD');
SELECT ASG.effective_start_date,
LEAST(ASG.effective_end_date, p_period_end),
NVL(ASG.normal_hours, 0),
NVL(HRL.meaning, 'NOT ENTERED'),
NVL(SCL.segment4, 'NOT ENTERED')
INTO v_range_start,
v_range_end,
v_asst_std_hrs,
v_asst_std_freq,
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 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 fnd_number.canonical_to_number(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 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(SCL.segment4, 'NOT ENTERED')
INTO v_range_start,
v_range_end,
v_asst_std_hrs,
v_asst_std_freq,
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 SCL.segment1 = TO_CHAR(p_tax_unit_id)
AND SCL.enabled_flag = 'Y'
AND HRL.lookup_code(+) = ASG.frequency
AND HRL.lookup_type(+) = 'FREQUENCY';
hr_utility.trace('Select app. EEVMPE again after range is determined');
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 fnd_number.canonical_to_number(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;
hr_utility.trace(' BOTH ASG - SELECT ASG_MULTI_WITHIN');
OPEN get_asst_chgs; -- SELECT ( ASG_MULTI_WITHIN)
hr_utility.trace('BOTH ASG MULTI select app. EEVMPE again after range det.');
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 fnd_number.canonical_to_number(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;
hr_utility.trace('BOTH ASG SELECT END_SPAN_RECORD');
SELECT ASG.effective_start_date,
LEAST(ASG.effective_end_date, p_period_end),
NVL(ASG.normal_hours, 0),
NVL(HRL.meaning, 'NOT ENTERED'),
NVL(SCL.segment4, 'NOT ENTERED')
INTO v_range_start,
v_range_end,
v_asst_std_hrs,
v_asst_std_freq,
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 SCL.segment1 = TO_CHAR(p_tax_unit_id)
AND SCL.enabled_flag = 'Y'
AND HRL.lookup_code(+) = ASG.frequency
AND HRL.lookup_type(+) = 'FREQUENCY';
hr_utility.trace('SELECT EEVMPE');
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;
hr_utility.trace('BOTH ASG SPAN - SELECT EEV_FOR_CURR_RANGE_END');
SELECT fnd_number.canonical_to_number(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.meaning = 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,'US') = 'US';
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;
END; /* SELECT LOOKUP CODE */
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;
hr_utility.trace(' selecting from per_time_period_types');
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,'US') = 'US';
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 nvl(ppf.prl_information2,'NOT ENTERED')
into v_calc_type
from pay_payrolls_f ppf
where payroll_id = p_payroll_id
and v_stnd_start_date between ppf.effective_start_date
and ppf.effective_end_Date;
select put.user_table_name
into c_ws_tab_name
from hr_organization_information hoi
,pay_user_tables put
where hoi.organization_id = p_bg_id
and hoi.org_information_context ='Work Schedule'
and hoi.org_information1 = put.user_table_id ;
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 NVL(PUC.business_group_id, p_bg_id) = p_bg_id
and NVL(PUC.legislation_code,'US') = 'US'
and PUC.user_table_id = PUT.user_table_id
and PUT.user_table_name = c_ws_tab_name;
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 NVL(PUC.business_group_id, p_bg_id) = p_bg_id
and NVL(PUC.legislation_code,'US') = 'US'
and PUC.user_table_id = PUT.user_table_id
and PUT.user_table_name = c_ws_tab_name;
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' -- Bug 3703863-- Commented out as this flag is 'N' for user defined cities.
AND ROWNUM < 2;
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 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 ipv.element_type_id = p_element_type_id
AND UPPER(IPV.name) = 'DEDUCTION PROCESSING'
AND IPV.business_group_id + 0 = p_bg_id
AND prr.element_type_id = ipv.element_type_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 /*+ ORDERED */ NVL(fnd_number.canonical_to_number(EEV.screen_entry_value), 0),
EEV.element_entry_id
FROM pay_element_entries_f ELE,
pay_element_entry_values_f EEV,
pay_input_values_f IPV,
pay_element_types_f ELT
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 EEV.input_value_id = IPV.input_value_id
AND p_date_earned BETWEEN IPV.effective_start_date
AND IPV.effective_end_date
AND IPV.element_type_id = ELT.element_type_id
AND p_date_earned BETWEEN ELT.effective_start_date
AND ElT.effective_end_date
and ipv.business_group_id = elt.business_group_id
and ipv.legislation_code = elt.legislation_code
AND ELT.element_name = 'Time Entry Wages'
AND IPV.name = 'Rate';
SELECT NVL(EEV.screen_entry_value, 'NOT ENTERED')
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 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 EEV.input_value_id = IPV.input_value_id
AND p_date_earned BETWEEN IPV.effective_start_date
AND IPV.effective_end_date
AND ELT.element_name = 'Time Entry Wages'
AND p_date_earned BETWEEN ELT.effective_start_date
AND ElT.effective_end_date
AND ELT.element_type_id = IPV.element_type_id
AND ipv.business_group_id = elt.business_group_id
AND ipv.legislation_code = elt.legislation_code
AND IPV.name = 'Rate Code';
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 ( 'US_EARNINGS',
'US_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 fnd_number.canonical_to_number(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 /*+ ORDERED */ NVL(EEV.screen_entry_value, 'NOT ENTERED')
INTO v_tew_rcode
FROM pay_element_entries_f ELE,
pay_element_entry_values_f EEV,
pay_input_values_f IPV,
pay_element_types_f ELT
WHERE ELE.assignment_id = p_ass_id
AND EEV.element_entry_id = v_entry_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 p_date_earned BETWEEN IPV.effective_start_date
AND IPV.effective_end_date
AND ELT.element_name = 'Time Entry Wages'
and p_date_earned BETWEEN elt.effective_start_date
AND elt.effective_end_date
AND ELT.element_type_id = IPV.element_type_id
AND ipv.business_group_id = elt.business_group_id
AND ipv.legislation_code = elt.legislation_code
AND IPV.name = 'Rate Code';
SELECT /*+ ORDERED */ COUNT(IPV.input_value_id)
INTO v_use_regwage
FROM pay_element_entries_f ELE,
pay_element_entry_values_f EEV,
pay_input_values_f IPV,
pay_element_types_f ELT
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 EEV.input_value_id = IPV.input_value_id
AND p_date_earned BETWEEN IPV.effective_start_date
AND ipv.effective_end_date
AND ELT.element_name = 'Regular Wages'
AND p_date_earned BETWEEN elt.effective_start_date
AND elt.effective_end_date
AND ELT.element_type_id = IPV.element_type_id
AND IPV.business_group_id = ELT.business_group_id
AND IPV.legislation_code = ELT.legislation_code
AND IPV.name = 'Rate';
SELECT /*+ ORDERED */ 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_entries_f ELE,
pay_element_entry_values_f EEV,
pay_input_values_f IPV,
pay_element_types_f ELT
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 EEV.input_value_id = IPV.input_value_id
AND p_date_earned BETWEEN ipv.effective_start_date
AND ipv.effective_end_date
AND ELT.element_name = 'Regular Wages'
AND p_date_earned BETWEEN elt.effective_start_date
AND elt.effective_end_date
AND ELT.element_type_id = IPV.element_type_id
AND IPV.name = 'Rate'
AND IPV.business_group_id = ELT.business_group_id
AND IPV.legislation_code = ELT.legislation_code;
SELECT /*+ ORDERED */ NVL(EEV.screen_entry_value, 'NOT ENTERED')
INTO v_regwage_rcode
FROM pay_element_entries_f ELE,
pay_element_entry_values_f EEV,
pay_input_values_f IPV,
pay_element_types_f ELT
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 EEV.element_entry_id = v_entry_id
AND EEV.input_value_id = IPV.input_value_id
AND p_date_earned BETWEEN IPV.effective_start_date
AND IPV.effective_end_date
AND ELT.element_name = 'Regular Wages'
ANd p_date_earned BETWEEN ELT.effective_start_date
AND ELT.effective_end_date
AND ELT.element_type_id = IPV.element_type_id
AND IPV.name = 'Rate Code'
AND IPV.business_group_id = ELT.business_group_id
AND IPV.legislation_code = ELT.legislation_code;
SELECT /*+ ORDERED */ COUNT(IPV.input_value_id)
INTO v_use_regsal
FROM pay_element_entries_f ELE,
pay_element_entry_values_f EEV,
pay_input_values_f IPV,
pay_element_types_f ELT
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 EEV.input_value_id = IPV.input_value_id
AND p_date_earned BETWEEN IPV.effective_start_date
AND IPV.effective_end_date
AND ELT.element_name = 'Regular Salary'
AND p_date_earned BETWEEN ELT.effective_start_date
AND ELT.effective_end_date
AND ELT.element_type_id = IPV.element_type_id
AND IPV.name = 'Monthly Salary'
AND IPV.business_group_id = ELT.business_group_id
AND IPV.legislation_code = ELT.legislation_code;
SELECT /*+ ORDERED */ NVL(fnd_number.canonical_to_number(EEV.screen_entry_value), 0)
INTO v_regsal_mosal
FROM pay_element_entries_f ELE,
pay_element_entry_values_f EEV,
pay_input_values_f IPV,
pay_element_types_f ELT
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 EEV.input_value_id = IPV.input_value_id
AND p_date_earned BETWEEN IPV.effective_start_date
AND IPV.effective_end_date
AND ELT.element_name = 'Regular Salary'
AND p_date_earned BETWEEN ELT.effective_start_date
AND ELT.effective_end_date
AND ELT.element_type_id = IPV.element_type_id
AND IPV.name = 'Monthly Salary'
AND IPV.business_group_id = ELT.business_group_id
AND IPV.legislation_code = ELT.legislation_code;
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(fnd_number.canonical_to_number(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 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 p_date_earned BETWEEN PPF.effective_start_date
AND PPF.effective_end_date
AND PPF.payroll_id = p_payroll_id;
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 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_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 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;
select to_number(nvl(element_information19,0)) into l_arr_eletype_id /*Element Type id of Spl features */
from pay_element_types_f
where element_type_id = p_eletype_id ;
select nvl(rule_mode,'N') into l_rule_mode from pay_legislation_rules where
rule_type = 'ADVANCED_RETRO' AND
legislation_code = 'US';
/* select pes.retro_element_type_id into l_retro_arr_eletype_id
from pay_retro_component_usages rcu , pay_element_span_usages pes
where rcu.creator_id = l_arr_eletype_id
and rcu.creator_type = 'ET'
and rcu.retro_component_usage_id = pes.retro_component_usage_id
and pes.legislation_code is null ; */
select (sum(to_number(nvl(screen_entry_value,'0')))) into l_arrear_contr_value
from PAY_ELEMENT_ENTRY_VALUES_F eev , PAY_INPUT_VALUES_F ip
where eev.input_value_id = ip.input_value_id
and ip.name like 'Arrears Contr'
and element_entry_id in ( select element_entry_id -- element entry for arrear contr
from pay_element_entries_f
where assignment_id = p_assignment_id
and p_date_earned between effective_start_date and effective_end_date
and element_type_id = l_arr_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 - l_arrear_contr_value - (p_net_asg_run - p_guaranteed_net)) +
(-1 * (p_arrears_itd + l_arrear_contr_value )) ;
SELECT /*+ ORDERED */ a.state_code||'-'||a.county_code||'-'||a.city_code
INTO l_geocode
FROM pay_us_city_names a,
pay_us_zip_codes z
WHERE a.city_name = l_query_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 a.state_code||'-'||a.county_code||'-'||a.city_code
INTO l_geocode
FROM pay_us_city_names a,
pay_us_zip_codes z
WHERE a.city_name = l_query_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 = l_query_city_name
AND a.county_code = b.county_code
AND UPPER(b.county_name) = UPPER(p_county_name) --Bug3783309-Changed Initcap to Upper.
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||'' ; --Bug 4868637
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
UPPER(b.county_name) = UPPER(p_county_name)AND --Bug3783309-Changed Initcap to Upper.
b.state_code = c.state_code
AND
a.city_name = l_query_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 territory_short_name
INTO r_territory_short_name
FROM FND_TERRITORIES_VL
WHERE territory_code = p_territory_code;
/*Delete the record if 457 details are not present */
catchup_type_table.DELETE(p_assignment_action_id);
/*Delete the record if 403 details are not present */
catchup_type_table.DELETE(p_assignment_action_id);
SELECT 'Y'
FROM PAY_PAYROLL_ACTIONS PPA,
PAY_ASSIGNMENT_ACTIONS PAA,
FF_ARCHIVE_ITEMS FAI,
FF_DATABASE_ITEMS FDI
WHERE PPA.REPORT_TYPE = 'YREND'
AND TO_CHAR(PPA.EFFECTIVE_DATE,'YYYY') = p_reporting_year
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND PAA.ASSIGNMENT_ACTION_ID= FAI.CONTEXT1
AND FAI.USER_ENTITY_ID = FDI.USER_ENTITY_ID
AND FDI.USER_NAME IN
('A_FIT_SUBJ_WHABLE_PER_GRE_YTD',
'A_FIT_SUBJ_NWHABLE_PER_GRE_YTD',
'A_FIT_PRE_TAX_REDNS_PER_GRE_YTD')
AND ROWNUM = 1;