[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
irgonzal 13-DEC-2000 115.16 Modified SELECT statements due to poor
performance on GRE Totals Report (1542061).
Added rule-based hint.
irgonzal 15-DEC-2000 115.19 Added rule-based hint to SELECT stmt. in
us_gp_subject_to_tax_gre_ytd procedure.
ahanda 27-DEC-2000 115.20 Did the change done in 115.19 version of
package to 115.16 ver. 115.17 and 115.19
should not be send to clients as it has
the Winstar Changes.
tclewis 6-SEP-2001 115.22 Modified the PAYMENTS_BALANCE_REQUIRED
function to work correctly with the umbrella
process
tclewis 7-SEP-2001 115.24 Added check for session variable PTD in the
procedure US_NAMED_BALANCE_VM. Now if the
session variable PTD is FALSE the procedure
will return null and not attempt to calculate
the value.
tclewis 11-27-2001 115.25 Added dbdrv command.
tclewis 12-05-2001 115.27 Added the following procedures
us_gp_multiple_gre_mtd
us_gp_multiple_gre_ctd
us_gp_subject_to_tax_gre_mtd
us_gp_subject_to_tax_gre_ctd
us_gp_gre_jd_mtd
us_gp_gre_jd_ctd
meshah 12-05-2001 115.28 Added set verify off
tmehra 12-16-2001 115.29 Currently there is no balance for FIT gross,
instead 'Gross Earnings' is used. Changed code
to subtract Alien earnings from FIT Gross.
ahanda 05-JAN-2002 115.31 Changed the following function to work with
umbrella process:
get_prepayment_aaid
reversal_exists_check
reversal_exists_check_tax
us_named_balance_vm
ahanda 08-JAN-2002 115.32 Changed function reversal_exists_check
to pass the business_group_id so that
it is set properly. Also changed the
default for BG ID to -1 from 0 (2175134).
meshah 22-JAN-2002 115.34 added checkfile command. leap frogged 115.33
ahanda 23-APR-2002 115.35 Checking session variable for CURRENT
in us_named_balance_vm and CURRENT, RUN, PTD,
PYDATE, MONTH in us_tax_balance_vm.
tclewis 1-may-2002 115.36 Modified the cursors c_run_actions in the
procedures
reversal_exists_check
reversal_exists_check_tax
to return data for only the assignment_id
processed in the run payroll actions.
Eliminate a second join to pay_action_interlocks
in the procedure get_prepayment_aaid, as it
was not needed.
ekim 25-Nov-2002 115.38 Changed like to = in function get_defined_balance
in query that gets l_defined_balance_id.
ekim 25-Nov-2002 115.39 GSCC warning fix for default value.
ekim 02-Dec-2002 115.40 GSCC warning fix for nocopy.
tclewis 13-MAR-2003 115.41 Modified US_NAMED_BALANCE_VM and US_TAX_BALANCE_VM
with respect to the CURRENT Dimension, removed the
REV_CHK work around and implemented the ASG_PAYMENTS
balance dimension. I also modified US_TAX_BALANCE
to accept ASG_PAYMENTS as a vaild time_type.
kaverma 19-NOV-2003 115.43 Added status <> 'D' for pay_taxability_rules
kaverma 21-NOV-2003 115.44 Corrected join for pay_taxability_rules as
nvl(status,'X') <> 'D'
sdahiya 12-JAN-2004 115.45 Modified query for performance (Bug 3343982).
tclewis 14-JAN-2004 115.46 Added STEIC to check of Taxable and excess bal.
djoshi 29-JAN-2004 115.47 Changed the function payments_balance_required
to make sure asg_payments route is executed
when current ...
sdahiya 17-FEB-2004 115.48 Removed RULE hint from queries. Bug 3331031.
pragupta 14-APR-2005 115.50 The us_gp_multiple_gre_qtd changed to support 12
instead of 10 balance calls.
pragupta 20-APR-2005 115.51 us_gp_multiple_gre_qtd procedure overloaded to
support 12 instead of 10 balances
sackumar 15-SEP-2005 115.53 Revert back the changes done in 115.52.
rdhingra 23-SEP-2005 115.54 Bug 4583566: Performance changes done
rdhingra 23-SEP-2005 115.55 Bug 4583566: Performance changes done
rdhingra 27-SEP-2005 115.56 Bug 4583566: Performance changes done
rnestor 09-SEP-2008 115.58 Bug 6989549: TAX SUMMARY & TAX BALANCE SCREEN SHOWS
DIFFERENT VALUES FOR EIC SUBJECT
*/
-- Global declarations
type num_array is table of number(15) index by binary_integer;
select max(PAF.effective_end_date)
from per_assignments_f PAF
where PAF.assignment_id = c_assignment_id
and PAF.payroll_id is not null
and PAF.effective_end_date
between trunc(c_date_earned,'Q') and c_date2_earned;
select max(PAF.effective_end_date)
from per_assignments_f PAF
where PAF.assignment_id = c_assignment_id
and PAF.payroll_id is not null
and PAF.effective_end_date
between trunc(c_date_earned,'Y') and c_date2_earned;
select paa.assignment_action_id
from pay_action_interlocks pai,
pay_assignment_actions paa,
pay_payroll_actions ppa
where pai.locked_action_id = cp_run_action_id
and paa.assignment_action_id = pai.locking_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.action_type in ('P', 'U');
select ppp.source_action_id
from pay_pre_payments ppp
where ppp.assignment_action_id = cp_pre_pymt_action_id
and ppp.source_action_id = cp_assignment_action_id;
select count(pai.locked_action_id)
from pay_action_interlocks pai,
pay_pre_payments ppp,
pay_assignment_actions paa
where ppp.assignment_action_id = cp_pre_pymt_action_id
and nvl(ppp.source_action_id,0) <> pai.locked_action_id
and pai.locking_action_id = ppp.assignment_action_id
and pai.locked_action_id = paa.assignment_action_id
and paa.source_action_id is not null;
select count(pai.locked_action_id)
from pay_action_interlocks pai,
pay_assignment_actions paa
where pai.locking_action_id = cp_pre_pymt_action_id
and pai.locked_action_id = paa.assignment_action_id
and nvl(paa.run_type_id,cp_run_type_id) <> cp_run_type_id
and paa.source_action_id is not null;
select prt.run_type_id
from pay_run_types_f prt
where prt.shortname = 'SEPCHECK'
and prt.legislation_code = 'US';
select balance_type_id
from pay_balance_types
where balance_name = c_balance_name
and legislation_code = 'US';
SELECT creator_id
INTO l_defined_balance_id
FROM ff_user_entities
WHERE user_entity_name = translate(p_balance_name||'_'||p_dimension_suffix,' ','_')
AND (legislation_code = 'US'
OR business_group_id = l_business_group_id);
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 distinct PAA.assignment_id
from pay_assignment_actions PAA,
pay_payroll_actions PPA
where PAA.tax_unit_id = c_tax_unit_id
and PPA.payroll_action_id = PAA.payroll_action_id
and PPA.effective_date >= trunc(c_date_earned,'Q')
and PPA.effective_date <= c_date2_earned
and PPA.action_type in ('R','Q','I','B','V');
select distinct PAA.assignment_id
from pay_assignment_actions PAA,
pay_payroll_actions PPA
where PAA.tax_unit_id = c_tax_unit_id
and PPA.payroll_action_id = PAA.payroll_action_id
and PPA.effective_date >= trunc(c_date_earned,'Y')
and PPA.effective_date <= c_date2_earned
and PPA.action_type in ('R','Q','I','B','V');
select distinct PAR.assignment_id
from pay_balance_types PBT,
pay_us_asg_reporting PAR
where PAR.tax_unit_id = c_tax_unit_id
and PBT.balance_type_id = c_balance_type_id
and PBT.jurisdiction_level <> 0
and substr(PAR.jurisdiction_code, 1, PBT.jurisdiction_level) =
substr(c_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(c_date_earned,'Y')
and PPA.effective_date <= c_date2_earned
and PPA.action_type in ('R','Q','I','B','V'));
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 p_virtual_date >= ppa.effective_date
and ppa.action_type in ('R','Q','I','B','V'));
select balance_type_id
into l_balance_type_id
from pay_defined_balances
where defined_balance_id = l_defined_balance_id;
select pai.locking_action_id
from pay_assignment_actions paa2,
pay_payroll_actions ppa2,
pay_action_interlocks pai,
pay_assignment_actions paa,
pay_payroll_actions ppa
where pai.locked_Action_id = param_aaid
and pai.locking_action_id = pai.locking_action_id
and pai.locking_action_id = paa.assignment_action_id
and paa.payroll_Action_id = ppa.payroll_action_id
and ppa.action_type in ('P','U')
and pai.locked_action_id = paa2.assignment_action_id
and paa2.payroll_Action_id = ppa2.payroll_Action_id
and ppa2.action_type in ('R','Q')
/*
and exists ( select locked_action_id,locking_action_id
from pay_action_interlocks paie,
pay_assignment_Actions paae,
pay_payroll_Actions ppae
where paie.locked_action_id = pai2.locked_action_id
and paie.locking_action_id = paae.assignment_action_id
and paae.payroll_Action_id = ppae.payroll_Action_id
and ppae.action_type = 'V' )
*/;
select prt.run_type_id
from pay_run_types_f prt
where prt.shortname = 'SEPCHECK'
and prt.legislation_code = 'US';
select run_type_id,
assignment_id
from pay_assignment_actions paa
where paa.assignment_action_id = cp_assignment_action_id;
select pai.locked_action_id
from pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_action_interlocks pai
where pai.locking_action_id = cp_pre_pay_action_id
and pai.locked_action_id = paa.assignment_action_id
and paa.assignment_id = cp_run_assignment_id
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.action_type in ('R','Q')
/* The condition below is to take care of Payroll Processes
which have been run with Umbrella process and before that.
Run Type Id will be not null in case of umbrella process
and Source action ID will be not null for Child Actions
*/
and ((paa.source_action_id is not null and ppa.run_type_id is not null
and paa.run_type_id <> cp_sep_check_run_type_id) or
(paa.source_action_id is null and ppa.run_type_id is null));
select prt.run_type_id
from pay_run_types_f prt
where prt.shortname = 'SEPCHECK'
and prt.legislation_code = 'US';
select run_type_id,
assignment_id
from pay_assignment_actions paa
where paa.assignment_action_id = cp_assignment_action_id;
select pai.locked_action_id
from pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_action_interlocks pai
where pai.locking_action_id = cp_pre_pay_action_id
and pai.locked_action_id = paa.assignment_action_id
and paa.assignment_id = cp_run_assignment_id
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.action_type in ('R','Q')
/* The condition below is to take care of Payroll Processes
which have been run with Umbrella process and before that.
Run Type Id will be not null in case of umbrella process
and Source action ID will be not null for Child Actions
*/
and ((paa.source_action_id is not null and ppa.run_type_id is not null
and paa.run_type_id <> cp_sep_check_run_type_id) or
(paa.source_action_id is null and ppa.run_type_id is null));
select max(paa.assignment_action_id)
from pay_assignment_actions paa, -- assignment_action for master payroll run
pay_action_interlocks pai
where pai.locking_action_id = cp_prepay_action_id
and pai.locked_action_id = paa.assignment_action_id
and paa.assignment_id = cp_assignment_id
and paa.source_action_id is null -- master assignment_action
group by assignment_id;
SELECT DECODE(prt.shortname,'SEPCHECK','Y','N'),
paa.assignment_id
INTO l_sep_check,
l_assignment_id
FROM pay_assignment_actions paa
,pay_run_types_f prt
WHERE paa.assignment_action_id = l_assignment_action_id
AND prt.run_type_id = paa.run_type_id
AND prt.legislation_code = 'US';
select paa.assignment_action_id
into l_pre_pay_aaid
from pay_action_interlocks pai,
pay_assignment_actions paa,
pay_payroll_actions ppa
where pai.locked_action_id = l_assignment_action_id
and paa.assignment_action_id = pai.locking_action_id
and paa.source_action_id is NULL -- master pre-payment action.
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.action_type in ('P', 'U');
select max(paa.assignment_action_id)
from pay_assignment_actions paa, -- assignment_action for master payroll run
pay_action_interlocks pai
where pai.locking_action_id = cp_prepay_action_id
and pai.locked_action_id = paa.assignment_action_id
and paa.assignment_id = cp_assignment_id
and paa.source_action_id is null -- master assignment_action
group by assignment_id;
SELECT DECODE(prt.shortname,'SEPCHECK','Y','N'),
paa.assignment_id
INTO l_sep_check,
l_assignment_id
FROM pay_assignment_actions paa
,pay_run_types_f prt
WHERE paa.assignment_action_id = l_assignment_action_id
AND prt.run_type_id = paa.run_type_id
AND prt.legislation_code = 'US';
select paa.assignment_action_id
into l_pre_pay_aaid
from pay_action_interlocks pai,
pay_assignment_actions paa,
pay_payroll_actions ppa
where pai.locked_action_id = l_assignment_action_id
and paa.assignment_action_id = pai.locking_action_id
and paa.source_action_id is NULL -- master pre-payment action.
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.action_type in ('P', 'U');
select assignment_action_id
from pay_assignment_actions
where payroll_action_id = p_payroll_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', 'ASG_PAYMENTS');
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 p_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 <= p_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');
SELECT /* Removed RULE hint. Bug 3331031 */
nvl(sum(decode(FEED.balance_type_id,c_balance_type_id1,TARGET.result_value * FEED.scale,0)),0) ,
nvl(sum(decode(FEED.balance_type_id,c_balance_type_id2,TARGET.result_value * FEED.scale,0)),0) ,
nvl(sum(decode(FEED.balance_type_id,c_balance_type_id3,TARGET.result_value * FEED.scale,0)),0) ,
nvl(sum(decode(FEED.balance_type_id,c_balance_type_id4,TARGET.result_value * FEED.scale,0)),0) ,
nvl(sum(decode(FEED.balance_type_id,c_balance_type_id5,TARGET.result_value * FEED.scale,0)),0),
nvl(sum(decode(FEED.balance_type_id,c_balance_type_id6,TARGET.result_value * FEED.scale,0)),0),
nvl(sum(decode(FEED.balance_type_id,c_balance_type_id7,TARGET.result_value * FEED.scale,0)),0),
nvl(sum(decode(FEED.balance_type_id,c_balance_type_id8,TARGET.result_value * FEED.scale,0)),0),
nvl(sum(decode(FEED.balance_type_id,c_balance_type_id9,TARGET.result_value * FEED.scale,0)),0),
nvl(sum(decode(FEED.balance_type_id,c_balance_type_id10,TARGET.result_value * FEED.scale,0)),0)
FROM pay_balance_feeds_f FEED
, pay_run_result_values TARGET
, pay_run_results RR
, pay_assignment_actions ASSACT
, pay_payroll_actions PACT
where PACT.effective_date between trunc(p_effective_date,'Y')
and p_effective_date
and PACT.action_type in ('R','Q','I','B','V')
/*
and PACT.action_status = 'C'
*/
and ASSACT.payroll_action_id = PACT.payroll_action_id
and ASSACT.action_status = 'C'
and ASSACT.tax_unit_id = p_tax_unit_id
and RR.assignment_action_id = ASSACT.assignment_action_id
and RR.status in ('P','PA')
and TARGET.run_result_id = RR.run_result_id
and nvl(TARGET.result_value,'0') <> '0'
and FEED.input_value_id = TARGET.input_value_id
and FEED.balance_type_id in (c_balance_type_id1,c_balance_type_id2,
c_balance_type_id3,c_balance_type_id4,
c_balance_type_id5,c_balance_type_id6,
c_balance_type_id7,c_balance_type_id8,
c_balance_type_id9,c_balance_type_id10)
and PACT.effective_date between FEED.effective_start_date
and FEED.effective_end_date;
SELECT /* Removed RULE hint. Bug 3331031 */
nvl(sum(decode(FEED.balance_type_id,c_balance_type_id1,TARGET.result_value * FEED.scale,0)),0) ,
nvl(sum(decode(FEED.balance_type_id,c_balance_type_id2,TARGET.result_value * FEED.scale,0)),0) ,
nvl(sum(decode(FEED.balance_type_id,c_balance_type_id3,TARGET.result_value * FEED.scale,0)),0) ,
nvl(sum(decode(FEED.balance_type_id,c_balance_type_id4,TARGET.result_value * FEED.scale,0)),0) ,
nvl(sum(decode(FEED.balance_type_id,c_balance_type_id5,TARGET.result_value * FEED.scale,0)),0),
nvl(sum(decode(FEED.balance_type_id,c_balance_type_id6,TARGET.result_value * FEED.scale,0)),0),
nvl(sum(decode(FEED.balance_type_id,c_balance_type_id7,TARGET.result_value * FEED.scale,0)),0),
nvl(sum(decode(FEED.balance_type_id,c_balance_type_id8,TARGET.result_value * FEED.scale,0)),0),
nvl(sum(decode(FEED.balance_type_id,c_balance_type_id9,TARGET.result_value * FEED.scale,0)),0),
nvl(sum(decode(FEED.balance_type_id,c_balance_type_id10,TARGET.result_value * FEED.scale,0)),0),
nvl(sum(decode(FEED.balance_type_id,c_balance_type_id11,TARGET.result_value * FEED.scale,0)),0),
nvl(sum(decode(FEED.balance_type_id,c_balance_type_id12,TARGET.result_value * FEED.scale,0)),0)
FROM pay_balance_feeds_f FEED
, pay_run_result_values TARGET
, pay_run_results RR
, pay_assignment_actions ASSACT
, pay_payroll_actions PACT
where PACT.effective_date between trunc(p_effective_date,'Q')
and p_effective_date
and PACT.action_type in ('R','Q','I','B','V')
/*
and PACT.action_status = 'C'
*/
and ASSACT.payroll_action_id = PACT.payroll_action_id
and ASSACT.action_status = 'C'
and ASSACT.tax_unit_id = p_tax_unit_id
and RR.assignment_action_id = ASSACT.assignment_action_id
and RR.status in ('P','PA')
and TARGET.run_result_id = RR.run_result_id
and nvl(TARGET.result_value,'0') <> '0'
and FEED.input_value_id = TARGET.input_value_id
and FEED.balance_type_id in (c_balance_type_id1,c_balance_type_id2,
c_balance_type_id3,c_balance_type_id4,
c_balance_type_id5,c_balance_type_id6,
c_balance_type_id7,c_balance_type_id8,
c_balance_type_id9,c_balance_type_id10,
c_balance_type_id11, c_balance_type_id12)
and PACT.effective_date between FEED.effective_start_date
and FEED.effective_end_date;
SELECT /* Removed RULE hint. Bug 3331031 */
nvl(sum(decode(feed.balance_type_id,c_balance_type_id1,TARGET.result_value * FEED.scale,0)),0),
nvl(sum(decode(feed.balance_type_id,c_balance_type_id2,TARGET.result_value * FEED.scale,0)),0),
nvl(sum(decode(feed.balance_type_id,c_balance_type_id3,TARGET.result_value * FEED.scale,0)),0),
nvl(sum(decode(feed.balance_type_id,c_balance_type_id4,TARGET.result_value * FEED.scale,0)),0),
nvl(sum(decode(feed.balance_type_id,c_balance_type_id5,TARGET.result_value * FEED.scale,0)),0)
FROM
pay_balance_feeds_f FEED
, pay_run_result_values TARGET
, pay_run_results RR
, pay_assignment_actions ASSACT
, pay_payroll_actions PACT
where PACT.effective_date between trunc(c_effective_date,'Y')
and c_effective_date
and PACT.action_type in ('R','Q','I','B','V')
/*
and PACT.action_status = 'C'
*/
and ASSACT.payroll_action_id = PACT.payroll_action_id
and ASSACT.tax_unit_id = c_tax_unit_id /* Subject to Tax */
and ASSACT.action_status = 'C'
and RR.assignment_action_id = ASSACT.assignment_action_id
and RR.status in ('P','PA')
and TARGET.run_result_id = RR.run_result_id
and FEED.input_value_id = TARGET.input_value_id
and nvl(TARGET.result_value,'0') <> '0'
and FEED.balance_type_id in (c_balance_type_id1,c_balance_type_id2,
c_balance_type_id3,c_balance_type_id4,
c_balance_type_id5)
and PACT.effective_date between FEED.effective_start_date
and FEED.effective_end_date
and EXISTS ( select 'x'
from pay_taxability_rules TR,
pay_element_types_f ET
where ET.element_type_id = RR.element_type_id
and PACT.date_earned between ET.effective_start_date
and ET.effective_end_date
and TR.classification_id = ET.classification_id + 0
and TR.tax_category = ET.element_information1
and TR.tax_type = (select bt.tax_type from pay_balance_types bt
where bt.balance_type_id = FEED.balance_type_id)
and TR.jurisdiction_code = '00-000-0000'||decode(RR.run_result_id,null,', ')
and nvl(TR.status,'X') <>'D'); -- Bug 3251672
SELECT /* Removed RULE hint. Bug 3331031 */
nvl(sum(decode(feed.balance_type_id,c_balance_type_id1,TARGET.result_value * FEED.scale,0)),0),
nvl(sum(decode(feed.balance_type_id,c_balance_type_id2,TARGET.result_value * FEED.scale,0)),0),
nvl(sum(decode(feed.balance_type_id,c_balance_type_id3,TARGET.result_value * FEED.scale,0)),0),
nvl(sum(decode(feed.balance_type_id,c_balance_type_id4,TARGET.result_value * FEED.scale,0)),0),
nvl(sum(decode(feed.balance_type_id,c_balance_type_id5,TARGET.result_value * FEED.scale,0)),0)
FROM
pay_balance_feeds_f FEED
, pay_run_result_values TARGET
, pay_run_results RR
, pay_assignment_actions ASSACT
, pay_payroll_actions PACT
where PACT.effective_date between trunc(c_effective_date,'Q')
and c_effective_date
and PACT.action_type in ('R','Q','I','B','V')
/*
and PACT.action_status = 'C'
*/
and ASSACT.payroll_action_id = PACT.payroll_action_id
and ASSACT.tax_unit_id = c_tax_unit_id /* Subject to Tax */
and ASSACT.action_status = 'C'
and RR.assignment_action_id = ASSACT.assignment_action_id
and RR.status in ('P','PA')
and TARGET.run_result_id = RR.run_result_id
and FEED.input_value_id = TARGET.input_value_id
and nvl(TARGET.result_value,'0') <> '0'
and FEED.balance_type_id in (c_balance_type_id1,c_balance_type_id2,
c_balance_type_id3,c_balance_type_id4,
c_balance_type_id5)
and PACT.effective_date between FEED.effective_start_date
and FEED.effective_end_date
and EXISTS ( select 'x'
from pay_taxability_rules TR,
pay_element_types_f ET
where ET.element_type_id = RR.element_type_id
and PACT.date_earned between ET.effective_start_date
and ET.effective_end_date
and TR.classification_id = ET.classification_id + 0
and TR.tax_category = ET.element_information1
and TR.tax_type = (select bt.tax_type from pay_balance_types bt
where bt.balance_type_id = FEED.balance_type_id)
and TR.jurisdiction_code = '00-000-0000'||decode(RR.run_result_id,null,', ')
and nvl(TR.status,'X') <>'D'); -- Bug 3251672
SELECT /* Removed RULE hint. Bug 3331031 */
nvl(sum(decode(FEED.balance_type_id,c_balance_type_id1,TARGET.result_value * FEED.scale,0)),0) ,
nvl(sum(decode(FEED.balance_type_id,c_balance_type_id2,TARGET.result_value * FEED.scale,0)),0) ,
nvl(sum(decode(FEED.balance_type_id,c_balance_type_id3,TARGET.result_value * FEED.scale,0)),0) ,
nvl(sum(decode(FEED.balance_type_id,c_balance_type_id4,TARGET.result_value * FEED.scale,0)),0) ,
nvl(sum(decode(FEED.balance_type_id,c_balance_type_id5,TARGET.result_value * FEED.scale,0)),0) ,
nvl(sum(decode(FEED.balance_type_id,c_balance_type_id6,TARGET.result_value * FEED.scale,0)),0) ,
nvl(sum(decode(FEED.balance_type_id,c_balance_type_id7,TARGET.result_value * FEED.scale,0)),0)
FROM
pay_balance_feeds_f FEED
, pay_run_result_values TARGET
, pay_run_results RR
, pay_assignment_actions ASSACT
, pay_payroll_actions PACT
, (select distinct puar.assignment_id assignment_id
from pay_us_asg_reporting puar
where puar.tax_unit_id = c_tax_unit_id
and puar.jurisdiction_code like substr(c_state_code,1,2)||'%') ASGRPT
--
where PACT.effective_date between trunc(c_effective_date,'Y')
and c_effective_date
and PACT.action_type in ('R','Q','I','B','V')
/*
and PACT.action_status = 'C'
*/
and FEED.balance_type_id in ( c_balance_type_id1 , c_balance_type_id2 ,
c_balance_type_id3 , c_balance_type_id4 ,
c_balance_type_id5 , c_balance_type_id6 ,
c_balance_type_id7 )
and PACT.effective_date between FEED.effective_start_date
and FEED.effective_end_date
and ASSACT.payroll_action_id = PACT.payroll_action_id
and ASSACT.assignment_id = ASGRPT.assignment_id
and ASSACT.tax_unit_id = c_tax_unit_id
and ASSACT.action_status = 'C'
and RR.assignment_action_id = ASSACT.assignment_action_id
and RR.status in ('P','PA')
and RR.jurisdiction_code like substr ( c_state_code, 1, 2)||'%'
and TARGET.run_result_id = RR.run_result_id
and FEED.input_value_id = TARGET.input_value_id
and nvl(TARGET.result_value,'0') <> '0';
SELECT /* Removed RULE hint. Bug 3331031 */
nvl(sum(decode(FEED.balance_type_id,c_balance_type_id1,TARGET.result_value * FEED.scale,0)),0) ,
nvl(sum(decode(FEED.balance_type_id,c_balance_type_id2,TARGET.result_value * FEED.scale,0)),0) ,
nvl(sum(decode(FEED.balance_type_id,c_balance_type_id3,TARGET.result_value * FEED.scale,0)),0) ,
nvl(sum(decode(FEED.balance_type_id,c_balance_type_id4,TARGET.result_value * FEED.scale,0)),0) ,
nvl(sum(decode(FEED.balance_type_id,c_balance_type_id5,TARGET.result_value * FEED.scale,0)),0) ,
nvl(sum(decode(FEED.balance_type_id,c_balance_type_id6,TARGET.result_value * FEED.scale,0)),0) ,
nvl(sum(decode(FEED.balance_type_id,c_balance_type_id7,TARGET.result_value * FEED.scale,0)),0)
FROM
pay_balance_feeds_f FEED
, pay_run_result_values TARGET
, pay_run_results RR
, pay_assignment_actions ASSACT
, pay_payroll_actions PACT
, (select distinct puar.assignment_id assignment_id
from pay_us_asg_reporting puar
where puar.tax_unit_id = c_tax_unit_id
and puar.jurisdiction_code like substr(c_state_code,1,2)||'%') ASGRPT
--
where PACT.effective_date between trunc(c_effective_date,'Q')
and c_effective_date
and PACT.action_type in ('R','Q','I','B','V')
/*
and PACT.action_status = 'C'
*/
and FEED.balance_type_id in ( c_balance_type_id1 , c_balance_type_id2 ,
c_balance_type_id3 , c_balance_type_id4 ,
c_balance_type_id5 , c_balance_type_id6 ,
c_balance_type_id7 )
and PACT.effective_date between FEED.effective_start_date
and FEED.effective_end_date
and ASSACT.payroll_action_id = PACT.payroll_action_id
and ASSACT.assignment_id = ASGRPT.assignment_id
and ASSACT.tax_unit_id = c_tax_unit_id
and ASSACT.action_status = 'C'
and RR.assignment_action_id = ASSACT.assignment_action_id
and RR.status in ('P','PA')
and RR.jurisdiction_code like substr ( c_state_code, 1, 2)||'%'
and TARGET.run_result_id = RR.run_result_id
and FEED.input_value_id = TARGET.input_value_id
and nvl(TARGET.result_value,'0') <> '0';
SELECT /* Removed RULE hint. Bug 3331031 */
nvl(sum(decode(FEED.balance_type_id,c_balance_type_id1,TARGET.result_value * FEED.scale,0)),0) ,
nvl(sum(decode(FEED.balance_type_id,c_balance_type_id2,TARGET.result_value * FEED.scale,0)),0) ,
nvl(sum(decode(FEED.balance_type_id,c_balance_type_id3,TARGET.result_value * FEED.scale,0)),0) ,
nvl(sum(decode(FEED.balance_type_id,c_balance_type_id4,TARGET.result_value * FEED.scale,0)),0) ,
nvl(sum(decode(FEED.balance_type_id,c_balance_type_id5,TARGET.result_value * FEED.scale,0)),0),
nvl(sum(decode(FEED.balance_type_id,c_balance_type_id6,TARGET.result_value * FEED.scale,0)),0),
nvl(sum(decode(FEED.balance_type_id,c_balance_type_id7,TARGET.result_value * FEED.scale,0)),0),
nvl(sum(decode(FEED.balance_type_id,c_balance_type_id8,TARGET.result_value * FEED.scale,0)),0),
nvl(sum(decode(FEED.balance_type_id,c_balance_type_id9,TARGET.result_value * FEED.scale,0)),0),
nvl(sum(decode(FEED.balance_type_id,c_balance_type_id10,TARGET.result_value * FEED.scale,0)),0)
FROM pay_balance_feeds_f FEED
, pay_run_result_values TARGET
, pay_run_results RR
, pay_assignment_actions ASSACT
, pay_payroll_actions PACT
where PACT.effective_date between trunc(p_effective_date,'MON')
and p_effective_date
and PACT.action_type in ('R','Q','I','B','V')
/*
and PACT.action_status = 'C'
*/
and ASSACT.payroll_action_id = PACT.payroll_action_id
and ASSACT.action_status = 'C'
and ASSACT.tax_unit_id = p_tax_unit_id
and RR.assignment_action_id = ASSACT.assignment_action_id
and RR.status in ('P','PA')
and TARGET.run_result_id = RR.run_result_id
and nvl(TARGET.result_value,'0') <> '0'
and FEED.input_value_id = TARGET.input_value_id
and FEED.balance_type_id in (c_balance_type_id1,c_balance_type_id2,
c_balance_type_id3,c_balance_type_id4,
c_balance_type_id5,c_balance_type_id6,
c_balance_type_id7,c_balance_type_id8,
c_balance_type_id9,c_balance_type_id10)
and PACT.effective_date between FEED.effective_start_date
and FEED.effective_end_date;
SELECT /* Removed RULE hint. Bug 3331031 */
nvl(sum(decode(FEED.balance_type_id,c_balance_type_id1,TARGET.result_value * FEED.scale,0)),0) ,
nvl(sum(decode(FEED.balance_type_id,c_balance_type_id2,TARGET.result_value * FEED.scale,0)),0) ,
nvl(sum(decode(FEED.balance_type_id,c_balance_type_id3,TARGET.result_value * FEED.scale,0)),0) ,
nvl(sum(decode(FEED.balance_type_id,c_balance_type_id4,TARGET.result_value * FEED.scale,0)),0) ,
nvl(sum(decode(FEED.balance_type_id,c_balance_type_id5,TARGET.result_value * FEED.scale,0)),0),
nvl(sum(decode(FEED.balance_type_id,c_balance_type_id6,TARGET.result_value * FEED.scale,0)),0),
nvl(sum(decode(FEED.balance_type_id,c_balance_type_id7,TARGET.result_value * FEED.scale,0)),0),
nvl(sum(decode(FEED.balance_type_id,c_balance_type_id8,TARGET.result_value * FEED.scale,0)),0),
nvl(sum(decode(FEED.balance_type_id,c_balance_type_id9,TARGET.result_value * FEED.scale,0)),0),
nvl(sum(decode(FEED.balance_type_id,c_balance_type_id10,TARGET.result_value * FEED.scale,0)),0)
FROM pay_balance_feeds_f FEED
, pay_run_result_values TARGET
, pay_run_results RR
, pay_assignment_actions ASSACT
, pay_payroll_actions PACT
where PACT.effective_date between p_start_date
and p_effective_date
and PACT.action_type in ('R','Q','I','B','V')
/*
and PACT.action_status = 'C'
*/
and ASSACT.payroll_action_id = PACT.payroll_action_id
and ASSACT.action_status = 'C'
and ASSACT.tax_unit_id = p_tax_unit_id
and RR.assignment_action_id = ASSACT.assignment_action_id
and RR.status in ('P','PA')
and TARGET.run_result_id = RR.run_result_id
and nvl(TARGET.result_value,'0') <> '0'
and FEED.input_value_id = TARGET.input_value_id
and FEED.balance_type_id in (c_balance_type_id1,c_balance_type_id2,
c_balance_type_id3,c_balance_type_id4,
c_balance_type_id5,c_balance_type_id6,
c_balance_type_id7,c_balance_type_id8,
c_balance_type_id9,c_balance_type_id10)
and PACT.effective_date between FEED.effective_start_date
and FEED.effective_end_date;
SELECT /* Removed RULE hint. Bug 3331031 */
nvl(sum(decode(feed.balance_type_id,c_balance_type_id1,TARGET.result_value * FEED.scale,0)),0),
nvl(sum(decode(feed.balance_type_id,c_balance_type_id2,TARGET.result_value * FEED.scale,0)),0),
nvl(sum(decode(feed.balance_type_id,c_balance_type_id3,TARGET.result_value * FEED.scale,0)),0),
nvl(sum(decode(feed.balance_type_id,c_balance_type_id4,TARGET.result_value * FEED.scale,0)),0),
nvl(sum(decode(feed.balance_type_id,c_balance_type_id5,TARGET.result_value * FEED.scale,0)),0)
FROM
pay_balance_feeds_f FEED
, pay_run_result_values TARGET
, pay_run_results RR
, pay_assignment_actions ASSACT
, pay_payroll_actions PACT
where PACT.effective_date between trunc(c_effective_date,'MON')
and c_effective_date
and PACT.action_type in ('R','Q','I','B','V')
/*
and PACT.action_status = 'C'
*/
and ASSACT.payroll_action_id = PACT.payroll_action_id
and ASSACT.tax_unit_id = c_tax_unit_id /* Subject to Tax */
and ASSACT.action_status = 'C'
and RR.assignment_action_id = ASSACT.assignment_action_id
and RR.status in ('P','PA')
and TARGET.run_result_id = RR.run_result_id
and FEED.input_value_id = TARGET.input_value_id
and nvl(TARGET.result_value,'0') <> '0'
and FEED.balance_type_id in (c_balance_type_id1,c_balance_type_id2,
c_balance_type_id3,c_balance_type_id4,
c_balance_type_id5)
and PACT.effective_date between FEED.effective_start_date
and FEED.effective_end_date
and EXISTS ( select 'x'
from pay_taxability_rules TR,
pay_element_types_f ET
where ET.element_type_id = RR.element_type_id
and PACT.date_earned between ET.effective_start_date
and ET.effective_end_date
and TR.classification_id = ET.classification_id + 0
and TR.tax_category = ET.element_information1
and TR.tax_type = (select bt.tax_type from pay_balance_types bt
where bt.balance_type_id = FEED.balance_type_id)
and TR.jurisdiction_code = '00-000-0000'||decode(RR.run_result_id,null,', ')
and nvl(TR.status,'X') <>'D') ; -- Bug 3251672
SELECT /* Removed RULE hint. Bug 3331031 */
nvl(sum(decode(feed.balance_type_id,c_balance_type_id1,TARGET.result_value * FEED.scale,0)),0),
nvl(sum(decode(feed.balance_type_id,c_balance_type_id2,TARGET.result_value * FEED.scale,0)),0),
nvl(sum(decode(feed.balance_type_id,c_balance_type_id3,TARGET.result_value * FEED.scale,0)),0),
nvl(sum(decode(feed.balance_type_id,c_balance_type_id4,TARGET.result_value * FEED.scale,0)),0),
nvl(sum(decode(feed.balance_type_id,c_balance_type_id5,TARGET.result_value * FEED.scale,0)),0)
FROM
pay_balance_feeds_f FEED
, pay_run_result_values TARGET
, pay_run_results RR
, pay_assignment_actions ASSACT
, pay_payroll_actions PACT
where PACT.effective_date between c_start_date
and c_effective_date
and PACT.action_type in ('R','Q','I','B','V')
/*
and PACT.action_status = 'C'
*/
and ASSACT.payroll_action_id = PACT.payroll_action_id
and ASSACT.tax_unit_id = c_tax_unit_id /* Subject to Tax */
and ASSACT.action_status = 'C'
and RR.assignment_action_id = ASSACT.assignment_action_id
and RR.status in ('P','PA')
and TARGET.run_result_id = RR.run_result_id
and FEED.input_value_id = TARGET.input_value_id
and nvl(TARGET.result_value,'0') <> '0'
and FEED.balance_type_id in (c_balance_type_id1,c_balance_type_id2,
c_balance_type_id3,c_balance_type_id4,
c_balance_type_id5)
and PACT.effective_date between FEED.effective_start_date
and FEED.effective_end_date
and EXISTS ( select 'x'
from pay_taxability_rules TR,
pay_element_types_f ET
where ET.element_type_id = RR.element_type_id
and PACT.date_earned between ET.effective_start_date
and ET.effective_end_date
and TR.classification_id = ET.classification_id + 0
and TR.tax_category = ET.element_information1
and TR.tax_type = (select bt.tax_type from pay_balance_types bt
where bt.balance_type_id = FEED.balance_type_id)
and TR.jurisdiction_code = '00-000-0000'||decode(RR.run_result_id,null,', ')
and nvl(TR.status,'X') <>'D') ; -- Bug 3251672
SELECT /* Removed RULE hint. Bug 3331031 */
nvl(sum(decode(FEED.balance_type_id,c_balance_type_id1,TARGET.result_value * FEED.scale,0)),0) ,
nvl(sum(decode(FEED.balance_type_id,c_balance_type_id2,TARGET.result_value * FEED.scale,0)),0) ,
nvl(sum(decode(FEED.balance_type_id,c_balance_type_id3,TARGET.result_value * FEED.scale,0)),0) ,
nvl(sum(decode(FEED.balance_type_id,c_balance_type_id4,TARGET.result_value * FEED.scale,0)),0) ,
nvl(sum(decode(FEED.balance_type_id,c_balance_type_id5,TARGET.result_value * FEED.scale,0)),0) ,
nvl(sum(decode(FEED.balance_type_id,c_balance_type_id6,TARGET.result_value * FEED.scale,0)),0) ,
nvl(sum(decode(FEED.balance_type_id,c_balance_type_id7,TARGET.result_value * FEED.scale,0)),0)
FROM
pay_balance_feeds_f FEED
, pay_run_result_values TARGET
, pay_run_results RR
, pay_assignment_actions ASSACT
, pay_payroll_actions PACT
, (select distinct puar.assignment_id assignment_id
from pay_us_asg_reporting puar
where puar.tax_unit_id = c_tax_unit_id
and puar.jurisdiction_code like substr(c_state_code,1,2)||'%') ASGRPT
--
where PACT.effective_date between trunc(c_effective_date,'MON')
and c_effective_date
and PACT.action_type in ('R','Q','I','B','V')
/*
and PACT.action_status = 'C'
*/
and FEED.balance_type_id in ( c_balance_type_id1 , c_balance_type_id2 ,
c_balance_type_id3 , c_balance_type_id4 ,
c_balance_type_id5 , c_balance_type_id6 ,
c_balance_type_id7 )
and PACT.effective_date between FEED.effective_start_date
and FEED.effective_end_date
and ASSACT.payroll_action_id = PACT.payroll_action_id
and ASSACT.assignment_id = ASGRPT.assignment_id
and ASSACT.tax_unit_id = c_tax_unit_id
and ASSACT.action_status = 'C'
and RR.assignment_action_id = ASSACT.assignment_action_id
and RR.status in ('P','PA')
and RR.jurisdiction_code like substr ( c_state_code, 1, 2)||'%'
and TARGET.run_result_id = RR.run_result_id
and FEED.input_value_id = TARGET.input_value_id
and nvl(TARGET.result_value,'0') <> '0';
SELECT /* Removed RULE hint. Bug 3331031 */
nvl(sum(decode(FEED.balance_type_id,c_balance_type_id1,TARGET.result_value * FEED.scale,0)),0) ,
nvl(sum(decode(FEED.balance_type_id,c_balance_type_id2,TARGET.result_value * FEED.scale,0)),0) ,
nvl(sum(decode(FEED.balance_type_id,c_balance_type_id3,TARGET.result_value * FEED.scale,0)),0) ,
nvl(sum(decode(FEED.balance_type_id,c_balance_type_id4,TARGET.result_value * FEED.scale,0)),0) ,
nvl(sum(decode(FEED.balance_type_id,c_balance_type_id5,TARGET.result_value * FEED.scale,0)),0) ,
nvl(sum(decode(FEED.balance_type_id,c_balance_type_id6,TARGET.result_value * FEED.scale,0)),0) ,
nvl(sum(decode(FEED.balance_type_id,c_balance_type_id7,TARGET.result_value * FEED.scale,0)),0)
FROM
pay_balance_feeds_f FEED
, pay_run_result_values TARGET
, pay_run_results RR
, pay_assignment_actions ASSACT
, pay_payroll_actions PACT
, (select distinct puar.assignment_id assignment_id
from pay_us_asg_reporting puar
where puar.tax_unit_id = c_tax_unit_id
and puar.jurisdiction_code like substr(c_state_code,1,2)||'%') ASGRPT
--
where PACT.effective_date between c_start_date
and c_effective_date
and PACT.action_type in ('R','Q','I','B','V')
/*
and PACT.action_status = 'C'
*/
and FEED.balance_type_id in ( c_balance_type_id1 , c_balance_type_id2 ,
c_balance_type_id3 , c_balance_type_id4 ,
c_balance_type_id5 , c_balance_type_id6 ,
c_balance_type_id7 )
and PACT.effective_date between FEED.effective_start_date
and FEED.effective_end_date
and ASSACT.payroll_action_id = PACT.payroll_action_id
and ASSACT.assignment_id = ASGRPT.assignment_id
and ASSACT.tax_unit_id = c_tax_unit_id
and ASSACT.action_status = 'C'
and RR.assignment_action_id = ASSACT.assignment_action_id
and RR.status in ('P','PA')
and RR.jurisdiction_code like substr ( c_state_code, 1, 2)||'%'
and TARGET.run_result_id = RR.run_result_id
and FEED.input_value_id = TARGET.input_value_id
and nvl(TARGET.result_value,'0') <> '0';