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.
