Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 14 Oracle Analytics Lounge
- 211 Oracle Analytics News
- 41 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 77 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
PAY_RUN_BALANCES missing the last_updated_column
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.
Answers
-
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 actionsSELECT
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:
You could also try posting it in the product specific analytics forum and there are better chances of getting the right guidance.
Thanks,
Riyaz Ali0 -
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
0 -
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.
0
