Get Started with Redwood for Oracle Cloud HCM Begin Now
To ensure that questions get required attention from community members and are NOT left unanswered, it’s important for the author to indicate (by selecting “Yes” or “No” when prompted) whether the question was answered. (newly added) Please note that it is also important to respond to EACH comment your question receives. Your Yes or No response ensures an accurate status for your question.
For more information, please refer to this announcement explaining best practices for getting answers to questions.
For more information, please refer to this announcement explaining best practices for getting answers to questions.
YTD Salary information sql takes very long time
Summary
YTD Salary information SQL query takes almost 17 hours to completeContent
Hi,
I am running the attached SQL to generate the YTD salary for the employee from the payroll between the effective start date and end date. The sql to retrieve the data from balance and dimensions take around 17 hours to complete and fetch the report. is it possible to tune the query to fetch the data in a shorter tiime frame?
Code Snippet
SELECT distinct
PAPF.PERSON_NUMBER
,b.balance_name
,LPAD(TRIM(REPLACE((BAL.BALANCE_VALUE),'.')),13,0) YTDSAL
,P.EFFECTIVE_DATE
,c.hr_assignment_id
,rpad(pni.national_identifier_number, 11, ' ') ssn
FROM PAY_PAYROLL_REL_ACTIONS A
,PAY_PAYROLL_ACTIONS P
,pay_payroll_assignments C
,PAY_BALANCE_TYPES_VL B
,PAY_DIMENSION_USAGES_VL D
,PER_ALL_PEOPLE_F PAPF
,per_periods_of_service ppos
,pay_pay_relationships_dn pay_rel
,per_legislative_data_groups_tl tl
,TABLE (pay_balance_view_pkg.get_balance_dimensions(B.BALANCE_TYPE_ID
, A.PAYROLL_REL_ACTION_ID
, NULL
0