• Post category:BI Publisher
  • Post comments:0 Comments
  • Post last modified:June 16, 2022
  • Reading time:2 mins read
You are currently viewing How to retrieve Payroll balance Initialization Errors?
How to retrieve Payroll balance Initialization Errors?
- How to retrieve Payroll balance Initialization Errors?
Total Visits: 7

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 LinkedInFacebook, and Twitter to get updated with the latest content.