[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
H Parichabutr 24-JUL-1995 Updated to handle "PAYMENTS" and "PAYMENTS_JD"
time types - required for displaying tax bals
on SOE.
S Desai 27-Nov-1995 Added PYDATE time dimension.
gpaytonm 09-JAN-1995 333594 payments_jd dimension is defunct
gpaytonm 01-FEB-1996 337641 performance fixes to SQL statements (two)
and more control over calling bal user exit.
S Desai 18-Mar-1996 Ensure that get_dummy_asg_id returns an assignment
with a payroll_id and is effective on the virtual
date in order to pass the core.
T Grisco 23-Apr-1996 360669 put PAYMENTS_JD back.
S Desai 20-Aug-1996 371351: Head Tax mis-classified as an employer
liability.
L Thompson30-SEP-1996 40.15 395029: Must execute db item if dimension is
_PAYMENTS
L Thompson03-NOV-1996 40.16 378594: WC_EE previously not accessible.
nbristow 08-NOV-1996 40.17 420465: Added several performance fixes.
Major changes to improve
the handling of latest balances.
arcsed in by lwthomps.
nbristow 14-NOV-1996 40.18 Removed hard coded path, no longer forced
to use latest balances.
nbristow 20-NOV-1996 40.19 Created overload functions and
us_tax_balance_rep functions.
nbristow 06-DEC-1996 40.20 Fixed get_virtual_date, now also checks
the payroll as well as the assignment.
nbristow 18-DEC-1996 40.21 Changed calls to get_value (date mode) to
get_value_lock.
lwthomps 27-May-1997 40.22 489769, WC2_EE previously not accessible.
Similiar to change for 40.16.
tbattoo 16-JAN-1998 40.23(110.0) changed date format to DD-MON-YYYY -
bug 612696.
tbattoo 11-MAY-1998 40.24(110.1) dual mantained changes in view so
GRE PYDATE routes work over a range
djoshi 08-APR-1999 Verfied and converted for Canonical
Complience of Date
skutteti 14-SEP-1999 115.4 Pre-tax enhancements. Added categories 403B
and 457 wherever required.
hzhao 10-DEC-1999 115.5 Added support of pre-tax for EIC
JARTHURT 24-JUL-2000 115.6 Added legislation_code check in
get_defined_balance
skutteti 15-SEP-2000 115.8 Currently there is no balance for FIT gross,
instead 'Gross Earnings' is used. Changed code
to subtract Alien earnings from FIT Gross.
skutteti 23-NOV-2000 115.9 Pre tax for Alien expat earnings has to be
reported in 1042s. Added code to subtract the
Alien portion of Pre-tax for SIT/FIT purposes.
tmehra 16-AUG-2001 115.10 Removed above code to subtract Non W2 protion
of Pre-Tax for SIT as new balance feeds have
been added to achive this.
kthirmiy 01-OCT-2001 115.11 Added code for balance extract with the
tax_balance_category of 'OTHER_PRETAX_REDNS'
to show in the Pretax Details block for
other pre-tax enhancements
meshah 13-JUN-2002 115.18 changed the function call_balance_user_exit
to remove the call to get_grp_value because
from July 2002 we should be using the Balance
Reporting Arch. and that does not require the
call.
for new TRR checking REDUCED_SUBJ_WHABLE and
session_var of W2.
meshah 11-FEB-2003 115.21 Now checking for a session var of PAYUSNFR
to set the assignment_action_id before making
the balance call.
meshah 13-FEB-2003 115.22 nocopy.
meshah 17-APR-2003 115.23 changed the name of the session var from
PAYUSNFR to GROUP_RB_REPORT and added a new
cursor c_get_max_aaid.
meshah 29-MAY-2003 115.24 changed cursor c_get_max_aaid to c_get_min_aaid
GRE Totals, 940 and 941 reports are now
setting GROUP_RB_SDATE and GROUP_RB_EDATE
session variables to get the minimum
assignment_action_id. In c_get_min_aaid we
are using nvl in the select to return a -1
for cases where there are no runs.
meshah 04-JUN-2003 115.25 changed cursor c_get_min_aaid to work with
business_group_id and added a new cursor
c_get_bg_id.
sdahiya 12-JAN-2004 115.26 Modified query for performance enhancement
(Bug 3343974).
kvsankar 16-JAN-2004 115.27 Modified query for performance enhancement
(Bug 3290396).
tlcewis 17-MAR-2004 115.28 added coding for STEIC.
fusman 10-JAN-2005 115.29 Added JD_dimension String for NY FUTA Taxable.
fusman 12-JAN-2005 115.30 Changed the l_test value to 0 to make FUTA a state tax.
pragupta 14-APR-2005 115.31 Increased the size of l_tax_type
sackumar 13-SEP-2005 115.32 (Bug 4347453) Modified the g_dim_tbl_crs(3) query.
Introduced Index Hint in the query.
rdhingra 23-SEP-2005 115.33 Bug 4583560: Performance changes done
rdhingra 27-SEP-2005 115.34 Bug 4583560: Performance changes done
Reverting changes of ver 32 as it was putting a full index scan
tclewis 04-DEC-2008 115.35 Added validaton for SUI1 EE and SDI1 EE
emunisek 03-JUN-2010 115.38 Modified US_TAX_BALANCE function to change the way Federal Tax
Balances are fetched.Replaced the derived approach with estimated
values from Earnings with Run Results.The new changes are dependant
on a profile value set at site level.If the profile value is not
set or set as No, the balances will be fetched as it was before.
Only when Profile value is made Yes, the new changes will be effective.
emunisek 18-OCT-2011 115.39 Modified US_TAX_BALANCE function to support the value fetching for
FUTA CREDIT Balance
nkjaladi 23-NOV-2011 115.40 Bug #11926304 Modified US_TAX_BALANCE function
to support the value fetching for PSD taxes.
emunisek 01-NOV-2012 115.41 Bug#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.
*/
-- Global declarations
type num_array is table of number(15) index by binary_integer;
SELECT creator_id
INTO l_defined_balance_id
FROM ff_user_entities
WHERE user_entity_name like
translate(p_balance_name||'_'||p_dimension_suffix,' ','_')
AND legislation_code = 'US';
select ''
into l_dummy
from per_assignments_f paf
where paf.assignment_id = p_assignment_id
and p_virtual_date between paf.effective_start_date
and paf.effective_end_date
and paf.payroll_id is not null;
select max(ppf.effective_end_date)
into l_virtual_date2
from per_assignments_f paf,
pay_payrolls_f ppf
where paf.assignment_id = p_assignment_id
and paf.payroll_id = ppf.payroll_id
and ppf.effective_end_date between
trunc(p_virtual_date, p_balance_time)
and p_virtual_date;
select parameter_value
into l_run_route
from PAY_ACTION_PARAMETERS
where parameter_name = 'RUN_ROUTE';
select ''
into l_dummy
from dual
where exists (
select ''
from pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_assignment_latest_balances palb
where palb.assignment_id = p_assignment_id
and palb.defined_balance_id = l_defined_balance_id
and palb.assignment_action_id =
paa.assignment_action_id
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.action_type in ('R','Q','I','B','V')
and p_virtual_date >= ppa.effective_date);
select balance_type_id
into l_balance_type_id
from pay_defined_balances
where defined_balance_id = l_defined_balance_id;
select parameter_value
into l_run_route
from PAY_ACTION_PARAMETERS
where parameter_name = 'RUN_ROUTE';
select parameter_value
into l_run_route
from PAY_ACTION_PARAMETERS
where parameter_name = 'RUN_ROUTE';
select assignment_action_id
from pay_assignment_actions
where payroll_action_id = p_payroll_action_id;
select nvl(min(assignment_action_id),-1)
from pay_assignment_actions paa,pay_payroll_actions ppa
where paa.tax_unit_id = p_gre_id_context
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.effective_date between p_start_date and p_end_date
and ppa.action_type in ('R','Q','I','B','V') ;
select nvl(min(assignment_action_id),-1)
from pay_assignment_actions paa,pay_payroll_actions ppa,pay_payrolls_f ppf
where ppa.business_group_id +0 = p_bg_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.effective_date between p_start_date and p_end_date
and ppa.action_type in ('R','Q','I','B','V')
and ppf.payroll_id = ppa.payroll_id
and ppa.business_group_id +0 = ppf.business_group_id;
select business_group_id
from hr_organization_units
where organization_id = p_gre_id_context;
SELECT TO_CHAR(effective_date,'YYYY')
FROM pay_assignment_actions paa,pay_payroll_actions ppa
WHERE ppa.payroll_action_id = paa.payroll_action_id
AND paa.assignment_action_id = p_assign_action_id;
SELECT count(0)
INTO l_valid
FROM hr_lookups
WHERE lookup_type = 'US_TAX_BALANCE_CATEGORY'
AND lookup_code = p_tax_balance_category;
SELECT count(0)
INTO l_valid
FROM hr_lookups
WHERE lookup_type = 'US_TAX_TYPE'
AND lookup_code = p_tax_type;
SELECT count(0)
INTO l_valid
FROM dual
WHERE p_asg_type in ('ASG','PER','GRE');
SELECT count(0)
INTO l_valid
FROM dual
WHERE p_time_type in ('RUN','PTD','MONTH','QTD','YTD', 'PAYMENTS', 'PYDATE');
select count(0)
into l_asg_exists
from per_assignments_f
where assignment_id = l_assignment_id
and l_virtual_date between effective_start_date and effective_end_date;
select max(effective_end_date)
into l_max_date
from per_assignments_f
where assignment_id = l_assignment_id;
select tp.start_date
into l_bal_start_date
from per_time_periods tp,
per_assignments_f asg
where asg.assignment_id = l_assignment_id
and l_max_date between asg.effective_start_date and effective_end_date
and asg.payroll_id = tp.payroll_id
and l_virtual_date between tp.start_date and tp.end_date;
select business_group_id
into l_bg_id
from hr_organization_units
where organization_id = p_gre_id_context;
select 1
into check_asg
from per_assignments_f paf
where paf.assignment_id = g_asgid_tbl_id(l_count)
and l_virtual_date between paf.effective_start_date
and paf.effective_end_date;
select min(paa.assignment_id)
into l_assignment_id
from pay_assignment_actions paa,
pay_payroll_actions pact,
pay_payrolls_f ppf
where pact.effective_date <= l_virtual_date
and pact.payroll_action_id=paa.payroll_action_id
and pact.action_type in ('R', 'Q', 'I', 'V', 'B')
and paa.tax_unit_id = p_gre_id_context
and ppf.payroll_id = pact.payroll_id
and ppf.business_group_id = l_bg_id;
SELECT count(0)
INTO l_test
FROM sys.dual
WHERE l_tax_type in ('FIT','FUTA','MEDICARE','SS','EIC');
g_dim_tbl_crs(0) := 'select distinct PAA.assignment_id ' ||
'from pay_assignment_actions PAA, ' ||
' pay_payroll_actions PPA ' ||
'where PAA.tax_unit_id = :TAX_UNIT_ID ' ||
'and PPA.payroll_action_id = ' ||
' PAA.payroll_action_id ' ||
'and PPA.effective_date >= ' ||
'trunc(:DATE_EARNED,''Q'') ' ||
'and PPA.effective_date <= ' ||
':DATE2_EARNED ' ||
'and PPA.action_type in (''R'',''Q'',''I'',''B'',''V'') ';
g_dim_tbl_vtd(0) := 'select max(PAF.effective_end_date) ' ||
'from per_assignments_f PAF ' ||
'where PAF.assignment_id = :ASSIGNMENT_ID ' ||
'and PAF.payroll_id is not null ' ||
'and PAF.effective_end_date between ' ||
' trunc(:DATE_EARNED,''Q'') and ' ||
' :DATE2_EARNED';
g_dim_tbl_crs(1) := 'select distinct PAA.assignment_id ' ||
'from pay_assignment_actions PAA, ' ||
' pay_payroll_actions PPA ' ||
'where PAA.tax_unit_id = :TAX_UNIT_ID ' ||
'and PPA.payroll_action_id = ' ||
' PAA.payroll_action_id ' ||
'and PPA.effective_date >= ' ||
'trunc(:DATE_EARNED,''Y'') ' ||
'and PPA.effective_date <= ' ||
':DATE2_EARNED ' ||
'and PPA.action_type in (''R'',''Q'',''I'',''B'',''V'') ';
g_dim_tbl_vtd(1) := 'select max(PAF.effective_end_date) ' ||
'from per_assignments_f PAF ' ||
'where PAF.assignment_id = :ASSIGNMENT_ID ' ||
'and PAF.payroll_id is not null ' ||
'and PAF.effective_end_date between ' ||
' trunc(:DATE_EARNED,''Y'') and ' ||
' :DATE2_EARNED';
g_dim_tbl_crs(2) := 'select distinct PAA.assignment_id ' ||
'from pay_assignment_actions PAA, ' ||
' pay_payroll_actions PPA ' ||
'where PAA.tax_unit_id = :TAX_UNIT_ID ' ||
'and PPA.payroll_action_id = ' ||
' PAA.payroll_action_id ' ||
'and PPA.effective_date >= ' ||
'trunc(:DATE_EARNED,''Y'') ' ||
'and PPA.effective_date <= ' ||
':DATE2_EARNED ' ||
'and PPA.action_type in (''R'',''Q'',''I'',''B'',''V'') ';
g_dim_tbl_vtd(2) := 'select max(PAF.effective_end_date) ' ||
'from per_assignments_f PAF ' ||
'where PAF.assignment_id = :ASSIGNMENT_ID ' ||
'and PAF.payroll_id is not null ' ||
'and PAF.effective_end_date between ' ||
' trunc(:DATE_EARNED,''Y'') and ' ||
' :DATE2_EARNED';
g_dim_tbl_crs(3) := 'select distinct PAR.assignment_id ' ||
'from pay_balance_types PBT, ' ||
' pay_us_asg_reporting PAR ' ||
'where PAR.tax_unit_id = :TAX_UNIT_ID ' ||
'and PBT.balance_type_id = :BALANCE_TYPE_ID ' ||
'and PBT.jurisdiction_level <> 0 ' ||
'and substr(PAR.jurisdiction_code, 1, ' ||
'PBT.jurisdiction_level) = ' ||
'substr(:JURISDICTION_CODE, 1, ' ||
'PBT.jurisdiction_level) ' ||
'and exists (select 1 ' ||
' from pay_payroll_actions PPA, ' ||
' pay_assignment_actions PAA ' ||
' where PAA.assignment_id = ' ||
'PAR.assignment_id ' ||
' and PAA.tax_unit_id = ' ||
'PAR.tax_unit_id ' ||
' and PPA.payroll_action_id = ' ||
'PAA.payroll_action_id ' ||
' and PPA.effective_date >= ' ||
'trunc(:DATE_EARNED,''Y'') ' ||
' and PPA.effective_date <= ' ||
':DATE2_EARNED ' ||
' and PPA.action_type in (''R'',''Q'',''I'',''B'',''V'') )';
g_dim_tbl_vtd(3) := 'select max(PAF.effective_end_date) ' ||
'from per_assignments_f PAF ' ||
'where PAF.assignment_id = :ASSIGNMENT_ID ' ||
'and PAF.payroll_id is not null ' ||
'and PAF.effective_end_date between ' ||
' trunc(:DATE_EARNED,''Y'') and ' ||
' :DATE2_EARNED';