We'd love to hear from you! Provide  feedback  to earn a badge today. Take our quick survey
You're almost there! Please answer a few more questions for access to the Applications content. Complete registration
Interested in joining? Complete your registration by providing Areas of Interest here. Register

YTD Salary information sql takes very long time

Accepted answer
9
Views
7
Comments
edited Aug 19, 2021 5:07PM in Payroll and Global Payroll Interface (GPI) 7 comments

Summary

YTD Salary information SQL query takes almost 17 hours to complete

Content

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
   

Howdy, Stranger!

Log In

To view full details, sign in.

Register

Don't have an account? Click here to get started!