[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
*/
-- 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 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';