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:

If you like the content, please follow us on LinkedIn, Facebook, and Twitter to get updated with the latest content.