In this article, we will look into extracting the Payslip information from the Document of Records tables. Once the generate payslip process in run, the payslips will be generated and they will be added to the Document of Records and the employees can view them in employee self service.
If we need to extract the list of payslips that were generated, we can join all the payroll tables and get the list of we can simply get the list from DOR tables by providing the pay period start date.
The documents generated on the DOR will be available to employees for viewing only after the payslip availability date set on the manage payroll definitions for each pay period. Unless if this date is overridden at the flow level while running the generate payslips process.
SQL Query to extract the Payslip DOR information:
SELECT
A.DOCUMENT_CODE
,B.LEGISLATION_CODE
,C.PERSON_NUMBER
,A.ISSUING_COUNTRY AS Country
,D.DOCUMENT_TYPE
,A.DOCUMENT_NAME
,A.DOCUMENT_NUMBER
,TO_CHAR(A.DATE_FROM,'YYYY-MM-DD') AS DATE_FROM
,TO_CHAR(A.DATE_TO,'YYYY-MM-DD') AS DATE_TO
,A.ISSUING_AUTHORITY
,TO_CHAR(A.ISSUED_DATE,'YYYY-MM-DD') AS ISSUES_DATE
,A.ISSUING_COUNTRY AS ISSUING_COUNTRY_NAME
,A.ISSUING_LOCATION
,A.COMMENTS
,A.PUBLISH
,TO_CHAR(A.PUBLISH_DATE,'YYYY-MM-DD') AS PUBLISH_DATE
,A.RELATED_OBJECT_NAME
,A.STATUS
,A.DEI_ATTRIBUTE_CATEGORY AS ATTRIBUTE_CATEGORY
,A.DEI_ATTRIBUTE1 AS ATTRIBUTE1
,A.DEI_ATTRIBUTE2 AS ATTRIBUTE2
,A.DEI_ATTRIBUTE3 AS ATTRIBUTE3
,A.DEI_ATTRIBUTE4 AS ATTRIBUTE4
,A.DEI_ATTRIBUTE5 AS ATTRIBUTE5
,A.DEI_ATTRIBUTE6 AS ATTRIBUTE6
,A.DEI_ATTRIBUTE7 AS ATTRIBUTE7
,A.DEI_ATTRIBUTE8 AS ATTRIBUTE8
,A.DEI_ATTRIBUTE9 AS ATTRIBUTE9
,A.DEI_ATTRIBUTE10 AS ATTRIBUTE10
FROM HR_DOCUMENTS_OF_RECORD A
,HR_DOCUMENT_TYPES_B B
,PER_ALL_PEOPLE_F C
,HR_DOCUMENT_TYPES_TL D
WHERE A.DOCUMENT_TYPE_ID = B.DOCUMENT_TYPE_ID
AND A.PERSON_ID = C.PERSON_ID
AND A.DOCUMENT_TYPE_ID = D.DOCUMENT_TYPE_ID
AND D.DOCUMENT_TYPE = 'Payslip'
AND D.LANGUAGE = SYS_CONTEXT('USERENV', 'LANG')
and to_char(a.date_from,'YYYY-MM-DD') = '2023-08-01'
AND SYSDATE BETWEEN C.EFFECTIVE_START_DATE AND C.EFFECTIVE_END_DATE
