Check Reversals Performance Issue (Doc ID 2471908.1)

SELECT /*+ ORDERED INDEX(ASSACT PAY_ASSIGNMENT_ACTIONS_PK, PACT PAY_PAYROLL_ACTIONS_PK, RR PAY_RUN_RESULTS_N50, ET PAY_ELEMENT_TYPES_F_PK, TR PAY_TAXABILITY_RULES_FK1, TARGET PAY_RUN_RESULT_VALUES_N50, FEED PAY_BALANCE_FEEDS_F_N2, BT PAY_BALANCE_TYPES_PK) USE_NL(PACT ASSACT RR ET TR TARGET FEED BT) */ fnd_number.number_to_canonical(nvl(sum(fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale),0)), FEED.balance_type_id FROM /* Assignment within Government Reporting Entity For Run */
pay_assignment_actions ASSACT
,pay_payroll_actions PACT
,pay_run_results RR
,pay_element_types_f ET
,pay_taxability_rules TR
,pay_run_result_values TARGET
,pay_balance_feeds_f FEED
,pay_balance_types BT
where ASSACT.assignment_action_id = :ASSIGNMENT_ACTION_ID
and ASSACT.payroll_action_id = PACT.payroll_action_id
and nvl(TARGET.result_value,'0') != '0'
and FEED.input_value_id = TARGET.input_value_id
and TARGET.run_result_id = RR.run_result_id
and RR.assignment_action_id = ASSACT.assignment_action_id
and PACT.effective_date between
FEED.effective_start_date and FEED.effective_end_date
and RR.status in ('P','PA')
and ASSACT.tax_unit_id = :TAX_UNIT_ID
/* Subject to Tax in JD */
and ET.element_type_id = RR.element_type_id
and PACT.effective_date BETWEEN
ET.effective_start_date and ET.effective_end_date
and bt.jurisdiction_level IN ( 2,6,8,11 )
and BT.balance_type_id = FEED.balance_type_id
and nvl(TR.status,'VALID') <> 'D'
and ( TR.jurisdiction_code, TR.classification_id,
TR.tax_category ) =
(
/*
1. State level subject balance is required: jd level = 2 and
taxability rule will always exist for the state.
2. Local level subject balance is required: use the state level
subject balance if no taxability rules exist.
*/
SELECT decode(count(*), 0,
SUBSTR(:JURISDICTION_CODE,1,2) || '-000-0000' ,
DECODE(BT.JURISDICTION_LEVEL,6, SUBSTR(:JURISDICTION_CODE,1,6) || '-0000' ,
8, SUBSTR(:JURISDICTION_CODE,1,8),
11,SUBSTR(:JURISDICTION_CODE,1,2) || '-000-' || SUBSTR(:JURISDICTION_CODE,8,4) )),
ET.CLASSIFICATION_ID,
ET.ELEMENT_INFORMATION1
FROM PAY_TAXABILITY_RULES
WHERE JURISDICTION_CODE =
DECODE(BT.JURISDICTION_LEVEL,6,SUBSTR(:JURISDICTION_CODE,1,6) || '-0000' ,
8,SUBSTR(:JURISDICTION_CODE,1,8),
11,SUBSTR(:JURISDICTION_CODE,1,2) || '-000-' || SUBSTR(:JURISDICTION_CODE,8,4) )
AND nvl(status,'VALID') <> 'D'
AND classification_id = ET.classification_id
and rownum < 2 )
and TR.tax_type =
(SELECT
CASE
WHEN pec.classification_name = 'Pre-Tax Deductions' AND pec.legislation_code = 'US'
THEN
(SELECT decode(count(*), 0,
DECODE(BT.TAX_TYPE, 'CITY','DEFCITY', 'COUNTY','DEFCOUNTY', 'SCHOOL','DEFCITY',
'NW_CITY','NW_SIT', 'NW_COUNTY','NW_SIT',
'NW_SCHOOL','NW_SIT', BT.TAX_TYPE) , BT.TAX_TYPE)
FROM PAY_TAXABILITY_RULES
WHERE JURISDICTION_CODE =
DECODE(BT.JURISDICTION_LEVEL,6,SUBSTR(:JURISDICTION_CODE,1,6) || '-0000' ,
8,SUBSTR(:JURISDICTION_CODE,1,8),
11,SUBSTR(:JURISDICTION_CODE,1,2) || '-000-' || SUBSTR(:JURISDICTION_CODE,8,4) )
AND nvl(status,'VALID') <> 'D'
AND classification_id = ET.classification_id
and rownum < 2 )
ELSE
(SELECT decode(count(*), 0,
DECODE(BT.TAX_TYPE, 'CITY','SIT', 'COUNTY','SIT', 'SCHOOL','SIT',
'NW_CITY','NW_SIT', 'NW_COUNTY','NW_SIT',
'NW_SCHOOL','NW_SIT', BT.TAX_TYPE) , BT.TAX_TYPE)
FROM PAY_TAXABILITY_RULES
WHERE JURISDICTION_CODE =
DECODE(BT.JURISDICTION_LEVEL,6,SUBSTR(:JURISDICTION_CODE,1,6) || '-0000' ,
8,SUBSTR(:JURISDICTION_CODE,1,8),
11,SUBSTR(:JURISDICTION_CODE,1,2) || '-000-' || SUBSTR(:JURISDICTION_CODE,8,4) )
AND nvl(status,'VALID') <> 'D'
AND classification_id = ET.classification_id
and rownum < 2 )
END
FROM pay_element_classifications pec
WHERE pec.classification_id = ET.classification_id) and FEED.balance_type_id in (<BALANCE_TYPE_ID>, <BALANCE_TYPE_ID>... ) group by FEED.balance_type_id