Dear Folks,
One of our customer had an experience with program "Deduction Report" which taking long time to complete. Investigated AWR report during the time period and didn't have anything to drill down. Hence we lodged an SR with oracle and they give simple SQL which simply change VIEW on problematic table.
But before execute this SQL in prod. Make sure it is tested in non-prod instance.
Fix :
===
OWNER OBJECT_NAME CREATED OBJECT_TYPE LAST_DDL_TIME STATUS
--------------- -------------------- --------------- -------------------- --------------- -------
APPS PAY_US_DEDUCTIONS_RE 25-NOV-08 VIEW 15-AUG-20 VALID
PORT_RBR_V
SQL> select name from v$database;
NAME
---------
KTMI
SQL> @PAY_US_DEDUCTIONS_REPORT_RBR_V1.sql
View created.
SQL>
OWNER OBJECT_NAME CREATED OBJECT_TYPE LAST_DDL_TIME STATUS
--------------- ------------------------------ --------------- -------------------- --------------- -------
APPS PAY_US_DEDUCTIONS_REPORT_RBR_V 25-NOV-08 VIEW 03-DEC-20 VALID
SQL : PAY_US_DEDUCTIONS_REPORT_RBR_V1.sql
====
CREATE OR REPLACE FORCE VIEW "APPS"."PAY_US_DEDUCTIONS_REPORT_RBR_V" ("CONSOLIDATION_SET_ID", "CONSOLIDATION_SET_NAME", "PAYROLL_ID", "PAYROLL_NAME", "PAYROLL_EFFECTIVE_START_DATE", "PAYROLL_EFFECTIVE_END_DATE", "PAYROLL_ACTION_ID", "PAYROLL_ACTION_EFFECTIVE_DATE", "PAYROLL_ACTION_DATE_EARNED", "TIME_PERIOD_ID", "PERIOD_NAME", "PERIOD_NUM", "PERIOD_TYPE", "PERIOD_START_DATE", "PERIOD_END_DATE", "ASSIGNMENT_ACTION_ID", "ASSIGNMENT_ID", "TAX_UNIT_ID", "GRE", "PERSON_ID", "EMPLOYEE_NUMBER", "FULL_NAME", "ORGANIZATION_ID", "ORGANIZATION_NAME", "LOCATION_ID", "LOCATION_CODE", "LOCATION_DESCRIPTION", "ASSIGNMENT_SEQUENCE", "ASSIGNMENT_NUMBER", "ELEMENT_TYPE_ID", "ELEMENT_NAME", "ELEMENT_DESCRIPTION", "BUSINESS_GROUP_ID", "PRIMARY_BALANCE_ID", "HOURS_BALANCE_ID", "CLASSIFICATION_ID", "CLASSIFICATION_NAME", "PRIMARY_BALANCE", "NOT_TAKEN_BALANCE", "ARREARS_BALANCE", "ACCRUED_BALANCE", "TOTAL_OWED") AS
SELECT /*+ ordered no_concat index(paa PAY_ASSIGNMENT_ACTIONS_N50) use_nl(ppa pap)
use_nl(ppa pcs) use_nl(ppa ptp) use_nl(ppa paa paas papp) use_nl(paa prb)
use_nl(paa hou1) use_nl(paas hou) use_nl(paas hrl) use_hash(pet)
use_hash(pba) use_nl(pdb) use_nl(pbad) */
ppa.consolidation_set_id consolidation_set_id
,pcs.consolidation_set_name consolidation_set_name
,ppa.payroll_id payroll_id
,pap.payroll_name payroll_name
,pap.effective_start_date payroll_effective_start_date
,pap.effective_end_date payroll_effective_end_date
,ppa.payroll_action_id payroll_action_id
,ppa.effective_date payroll_action_effective_date
,ppa.date_earned payroll_action_date_earned
,ptp.time_period_id time_period_id
,ptp.period_name period_name
,ptp.period_num period_num
,ptp.period_type period_type
,ptp.start_date period_start_date
,ptp.end_date period_end_date
,paa.assignment_action_id assignment_action_id
,paa.assignment_id assignment_id
,paa.tax_unit_id tax_unit_id
,hou1.name gre
,paas.person_id person_id
,papp.employee_number employee_number
,papp.full_name full_name
,paas.organization_id organization_id
,hou.name organization_name
,paas.location_id location_id
,hrl.location_code location_code
,hrl.description location_description
,paas.assignment_sequence assignment_sequence
,paas.assignment_number assignment_number
,pet.element_type_id element_type_id
,pet.element_name element_name
,pet.description element_description
,pba.business_group_id business_group_id
,pet.element_information10 primary_balance_id
,pet.element_information11 hours_balance_id
,pec.classification_id classification_id
,pec.classification_name classification_name
,pay_us_taxbal_view_pkg.us_named_balance_vm (upper (pet.element_name)
,'ASG_GRE_RUN'
,paa.assignment_action_id
,paa.assignment_id
,NULL
,paa.tax_unit_id
,pba.business_group_id
,NULL) primary_balance
,pay_us_taxbal_view_pkg.us_named_balance_vm (upper (pet.element_name)
|| ' NOT TAKEN'
,'ASG_GRE_RUN'
,paa.assignment_action_id
,paa.assignment_id
,NULL
,paa.tax_unit_id
,pba.business_group_id
,NULL) not_taken_balance
,pay_us_taxbal_view_pkg.us_named_balance_vm (upper (pet.element_name)
|| ' ARREARS'
,'ASG_GRE_RUN'
,paa.assignment_action_id
,paa.assignment_id
,NULL
,paa.tax_unit_id
,pba.business_group_id
,NULL) arrears_balance
,decode (sign (to_number (decode (pet.element_information11
,NULL
,NULL
,pay_us_dedn_pkg.pay_us_tot_owed (paa.assignment_id
,pet.element_type_id
,ppa.effective_date
,ppa.date_earned))))
,NULL
,NULL
,- 1
,NULL
,0
,NULL
,pay_us_taxbal_view_pkg.us_named_balance_vm (upper (pet.element_name)
|| ' ACCRUED'
,'ASG_GRE_ITD'
,paa.assignment_action_id
,paa.assignment_id
,NULL
,paa.tax_unit_id
,pba.business_group_id
,NULL
,pec.classification_name
,'ENTRY_ITD'
,NULL
,pet.element_type_id)) accrued_balance
,to_number (decode (pet.element_information11
,NULL
,NULL
,pay_us_dedn_pkg.pay_us_tot_owed (paa.assignment_id
,pet.element_type_id
,ppa.effective_date
,ppa.date_earned))) total_owed
FROM pay_payroll_actions ppa
,pay_consolidation_sets pcs
,pay_all_payrolls_f pap
,per_time_periods ptp
,pay_assignment_actions paa
,hr_all_organization_units hou1
,per_all_assignments_f paas
,hr_organization_units hou
,hr_locations_all hrl
,per_all_people_f papp
,pay_run_balances prb
,pay_balance_attributes pba
,pay_bal_attribute_definitions pbad
,pay_defined_balances pdb
,pay_element_types_f pet
,pay_element_classifications pec
WHERE ppa.action_type IN ('Q','R','V'
,'B','I')
AND ppa.action_status = 'C'
AND ppa.consolidation_set_id = pcs.consolidation_set_id
AND ppa.payroll_id IS NOT NULL
AND ppa.payroll_id = pap.payroll_id
AND nvl (ppa.date_earned
,ppa.effective_date) BETWEEN pap.effective_start_date
AND pap.effective_end_date
AND ppa.time_period_id = ptp.time_period_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND paa.action_status = 'C'
AND paa.tax_unit_id = hou1.organization_id
AND paas.assignment_id = paa.assignment_id
AND nvl (ppa.date_earned
,ppa.effective_date) BETWEEN paas.effective_start_date
AND paas.effective_end_date
AND paas.organization_id = hou.organization_id
AND paas.location_id = hrl.location_id
AND pbad.attribute_name IN ('PAY_US_PRE_TAX_DEDUCTIONS','PAY_US_AFTER_TAX_DEDUCTIONS','PAY_US_EMPLOYER_LIABILITY')
AND pbad.legislation_code = 'US'
AND pba.attribute_id = pbad.attribute_id
AND prb.assignment_action_id = paa.assignment_action_id
AND prb.defined_balance_id = pba.defined_balance_id
AND pba.defined_balance_id = pdb.defined_balance_id
AND pba.business_group_id = pet.business_group_id
AND (
nvl (translate (pet.element_information10
,'0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'
,'01234567890000000000000000000000000000000000000000000000000000')
,'0') = pdb.balance_type_id
OR nvl (translate (pet.element_information11
,'0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'
,'01234567890000000000000000000000000000000000000000000000000000')
,'0') = pdb.balance_type_id
OR nvl (translate (pet.element_information12
,'0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'
,'01234567890000000000000000000000000000000000000000000000000000')
,'0') = pdb.balance_type_id
OR nvl (translate (pet.element_information13
,'0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'
,'01234567890000000000000000000000000000000000000000000000000000')
,'0') = pdb.balance_type_id
)
AND nvl (ppa.date_earned
,ppa.effective_date) BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND pec.legislation_code = 'US'
AND pec.classification_name IN ('Pre-Tax Deductions','Involuntary Deductions','Voluntary Deductions'
,'Employer Liabilities')
AND pet.classification_id = pec.classification_id
AND paas.person_id = papp.person_id
AND nvl (ppa.date_earned
,ppa.effective_date) BETWEEN papp.effective_start_date
AND papp.effective_end_date
GROUP BY ppa.consolidation_set_id
,pcs.consolidation_set_name
,ppa.payroll_id
,pap.payroll_name
,pap.effective_start_date
,pap.effective_end_date
,ppa.payroll_action_id
,ppa.effective_date
,ppa.date_earned
,ptp.time_period_id
,ptp.period_name
,ptp.period_num
,ptp.period_type
,ptp.start_date
,ptp.end_date
,paa.assignment_action_id
,paa.assignment_id
,paa.tax_unit_id
,hou1.name
,paas.person_id
,papp.employee_number
,papp.full_name
,paas.organization_id
,hou.name
,paas.location_id
,hrl.location_code
,hrl.description
,paas.assignment_sequence
,paas.assignment_number
,pet.element_type_id
,pet.element_name
,pet.description
,pba.business_group_id
,pet.element_information10
,pet.element_information11
,pec.classification_id
,pec.classification_name;
Thanks.
No comments:
Post a Comment