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

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.