PAY_RUN_BALANCES missing the last_updated_column

Question

I'm a newbie to Fusion. I'm looking for a date column to pull the data from the pay_run_balances table for a quart-yearly HR payroll plans vs actuals report.

The table is so huge and has tens of millions of rows that I can't pull it entirely into the report. However, I need the data only for a quarter in the report (only the rows where the status is closed).

But unfortunately I couldn't find a date column to pull the runs. I'm primarily looking for a system-generated date column (especially Last_updated_date). Is there a table that I can use for a join and take the appropriate date column from there? I'm sifting through the vast documentations with no luck so far.

Looking for help from the community.

MandeepGupta · Answer

https://community.oracle.com/products/oracleanalytics/discussion/26427/pay-run-balances-missing-the-last-updated-column

You can check the sample query on below link and try to choose and effective date between your period:

Oracle HCM: Query to fetch Payroll Balance details in Oracle HCM - Cloud

Thanks.

Syamantak Saha · Answer

Hi Dinesh,

I understand your problem, however, usually a functional value such as running balances do not have any last_updated_date. The reason is that it is expected to be different every time you run the query, so the value should really be the date and time you are extracting this data - for your purposes.

Cheers!

Syamantak Saha

Riyaz Ali-Oracle · Answer

Hi @Dinesh T ,

You can use a WHERE clause like this in your BI Publisher SQL:

WHERE prb.effective_date BETWEEN TO_DATE(:start_date, 'YYYY-MM-DD') AND TO_DATE(:end_date, 'YYYY-MM-DD')

AND ppa.action_status = 'C' -- Only closed payroll actions

SELECT

prb.balance_value,

prb.effective_date,

pdb.balance_name,

ppa.action_status

FROM

pay_run_balances prb

JOIN

pay_defined_balances pdb ON prb.defined_balance_id = pdb.defined_balance_id

JOIN

pay_payroll_actions ppa ON prb.payroll_action_id = ppa.payroll_action_id

WHERE

prb.effective_date BETWEEN TO_DATE(:start_date, 'YYYY-MM-DD') AND TO_DATE(:end_date, 'YYYY-MM-DD')

AND ppa.action_status = 'C'

Review the below docs as it might be helpful:

Tables and Views for HCM

You could also try posting it in the product specific analytics forum and there are better chances of getting the right guidance.

https://community.oracle.com/products/oracleanalytics/categories/fusion-hcm-analytics

Thanks,

Riyaz Ali