Balance initialization is used primarily to set up balances from legacy systems in case of mid year payroll implementations. If a payroll is implemented at the beginning of a tax year, it may not require initial balances. However, if a payroll is implemented in the middle of a tax year, balance initialization is important to ensure accurate calculations and payroll reporting.
When we initialize balances using HDL, some of the lines get transferred and some lines get errored out and there is no proper error mechanism in place to get the list of errors and get them corrected.
The header information will be stored in PAY_BALANCE_BATCH_HEADERS table and line information will be stored in PAY_BALANCE_BATCH_LINES table.
SQL Query to extract errors:
SELECT pbh.batch_name ,pbh.batch_status ,pbl.LINE_SEQUENCE ,pbl.PAYROLL_RELATIONSHIP_NUMBER ,pbl.ASSIGNMENT_NUMBER ,pbl.BALANCE_NAME ,pbl.DIMENSION_NAME ,pbl.batch_line_status ,pbl.value ,LISTAGG(pml.line_text, '; ') WITHIN GROUP ( ORDER BY pbl.LINE_SEQUENCE ) "Error_list" FROM pay_bal_batch_headers pbh ,pay_bal_batch_lines pbl ,pay_message_lines pml WHERE pbh.batch_name = nvl(:Batch_Name, pbh.Batch_Name) AND pbl.batch_id = pbh.batch_id AND pbl.batch_line_status = 'E' AND pml.source_type = 'L' AND pml.source_id = pbl.batch_line_id GROUP BY pbh.batch_name ,pbh.batch_status ,pbl.LINE_SEQUENCE ,pbl.PAYROLL_RELATIONSHIP_NUMBER ,pbl.ASSIGNMENT_NUMBER ,pbl.BALANCE_NAME ,pbl.DIMENSION_NAME ,pbl.batch_line_status ,pbl.value
Once we extract this information, we can prepare new HDL files correcting the data and then load a new batch.If you like the content, please follow us on LinkedIn, Facebook, and Twitter to get updated with the latest content.
Top 10 Popular Posts from our Knowledgebase
- DataFusing FREE Cloud based SQL Developer like tool for Oracle Cloud
- Core HR Entity Relationship Diagram (ERD) for HCM Cloud
- Most Frequently used Document IDs from Oracle Support
- FREE Oracle Training & Accreditation with Oracle Learning Explorer
- How HDL Transformation Formula works with ElementEntry Sample
- FREE Oracle Fusion Demo Instance (VISION) Access
- Top 10 FREE useful tools for Fusion Consultants
- Learning Fusion HCM Technical Skillset
- Sample HCM Data Loader (HDL) files for all Business Objects
- Most frequently used Tables list in Fusion HCM