• Post category:BI Publisher
  • Post comments:0 Comments
  • Post last modified:August 5, 2021
  • Reading time:3 mins read
You are currently viewing SQL Query to get the Payroll Definition Config Information
SQL Query to get the Payroll Definition Config Information

In this article we will try to extract the Payroll Definition Configuration information.

Navigate to Setup and Maintenance > Payroll Definitions

image 8 1024x427 - SQL Query to get the Payroll Definition Config Information
image 9 1024x458 - SQL Query to get the Payroll Definition Config Information

We can extract all of this information from backend tables.

SQL Query:

SELECT PAY.PAYROLL_ID,
	(
		SELECT NAME
		FROM PER_LEGISLATIVE_DATA_GROUPS_VL
		WHERE LEGISLATIVE_DATA_GROUP_ID = PAY.LEGISLATIVE_DATA_GROUP_ID
		) LEGISLATIVE_DATA_GROUP,
	PAY.PAYROLL_NAME,
	PAY.REPORTING_NAME,
	TO_CHAR(PAY.EFFECTIVE_START_DATE, 'YYYY/MM/DD') EFFECTIVE_START_DATE,
	TO_CHAR(PAY.EFFECTIVE_END_DATE, 'YYYY/MM/DD') EFFECTIVE_END_DATE,
	(
		SELECT DISPLAY_PERIOD_TYPE
		FROM PAY_TIME_PERIOD_TYPES
		WHERE PERIOD_TYPE = PAY.PERIOD_TYPE
		) PERIOD_TYPE,
	(
		SELECT NUMBER_PER_FISCAL_YEAR
		FROM PAY_TIME_PERIOD_TYPES
		WHERE PERIOD_TYPE = PAY.PERIOD_TYPE
		) PERIODS_PER_FISCAL_YEAR,
	(
		SELECT CONSOLIDATION_SET_NAME
		FROM PAY_CONSOLIDATION_SETS
		WHERE CONSOLIDATION_SET_ID = PAY.CONSOLIDATION_SET_ID
		) CONSOLIDATION_SET_NAME,
	(
		SELECT BASE_ORG_PAY_METHOD_NAME
		FROM PAY_ORG_PAY_METHODS_VL
		WHERE ORG_PAYMENT_METHOD_ID = PAY.DEFAULT_PAYMENT_METHOD_ID
			AND SYSDATE BETWEEN EFFECTIVE_START_DATE
				AND EFFECTIVE_END_DATE
		) DEFAULT_PAYMENT_METHOD_CODE,
	PAY.NEGATIVE_PAY_ALLOWED_FLAG,
	PAY.BASE_OFFSETS,
	PAY.CUTOFF_BASE_DATE,
	PAY.CUTOFF_FALLS,
	TO_CHAR(PAY.CUTOFF_FIXED_DATE, 'YYYY/MM/DD') CUTOFF_FIXED_DATE,
	PAY.CUTOFF_OFFSET,
	PAY.EARN_BASE_DATE,
	PAY.EARN_FALLS,
	TO_CHAR(PAY.EARN_FIXED_DATE, 'YYYY/MM/DD') EARN_FIXED_DATE,
	PAY.EARN_OFFSET,
	PAY.PAYDATE_BASE_DATE,
	PAY.PAYDATE_FALLS,
	TO_CHAR(PAY.PAYDATE_FIXED_DATE, 'YYYY/MM/DD') PAYDATE_FIXED_DATE,
	PAY.PAYDATE_OFFSET,
	TO_CHAR(PAY.FIRST_PERIOD_END_DATE, 'YYYY/MM/DD') FIRST_PERIOD_END_DATE,
	PAY.FIXED_DATE,
	PAY.NUMBER_OF_YEARS,
	PAY.PROCESS_BASE_DATE,
	PAY.PROCESS_FALLS,
	TO_CHAR(PAY.PROCESS_FIXED_DATE, 'YYYY/MM/DD') PROCESS_FIXED_DATE,
	PAY.PROCESS_OFFSET,
	PAY.PAYSLIP_BASE_DATE,
	PAY.PAYSLIP_FALLS,
	TO_CHAR(PAY.PAYSLIP_FIXED_DATE, 'YYYY/MM/DD') PAYSLIP_FIXED_DATE,
	PAY.PAYSLIP_OFFSET,
	PAY.PERIOD_RESET_YEARS
FROM PAY_ALL_PAYROLLS_F PAY
WHERE SYSDATE BETWEEN PAY.EFFECTIVE_START_DATE AND PAY.EFFECTIVE_END_DATE

Sample Output will look like:

image 10 1024x451 - SQL Query to get the Payroll Definition Config Information
If you like the content, please follow us on LinkedInFacebook, and Twitter to get updated with the latest content.