Payroll‎ > ‎

Summary



Payroll does not deal with a lot of tables, in comparison with other modules like Benefits / Core-HR. So the idea was to go through the functional set ups first and then look at the technical aspects, and peep through the tables.

 

PAY_ELEMENT_TYPES_F: No need to say, that this is a date track enabled table. This table stores the details about all the elements in the system. The Primary key is ELEMENT_TYPE_ID and the two date fields. This is usually used to get the name of the element, as ELEMENT_TYPE_ID is used in a lot of places to refer to the element.

 

PAY_ELEMENT_LINKS_F: This one is date track enabled as well. This one stores the details on the links. The primary key is: ELEMENT_LINK_ID and the two date tracked columns, stores the ELEMENT_TYPE_ID as the foreign key. 

 

PAY_INPUT_VALUES_F: This table stores the Input values for each element. This is the Date track enabled table. The primary key is INPUT_VALUE_ID and the two date tracked columns. This table also holds the ELEMENT_TYPE_ID as a foreign key to PAY_ELEMENT_TYPES_F. This can be used to pull in the Element input value name.

 

PAY_ELEMENT_ENTRIES_F: This is another Date tracked table. This one stores the details about the element entries. The table stores the Entries with the ASSIGNMENT_ID and the ELEMENT_LINK_ID as foreign key. The Primary key is ELEMENT_ENTRY_ID. This table also links itself to PAY_ELEMENT_TYPES_F with storing ELEMENT_TYPE_ID as a foreign key.

 

PAY_ELEMENT_ENTRY_VALUES_F: This date track enabled table stores the values for each entry. This table has only 6 columns. Out of which,  the Primary key is: ELEMENT_ENTRY_VALUE_ID and the two date tracked columns, it stores the ELEMENT_ENTRY_ID as the foreign key to PAY_ELEMENT_ENTRIES_F and the SCREEN_ENTRY_VALUE stores the actual value of the Input Value. The INPUT_VALUE_ID column links the table to the Input values table (PAY_INPUT_VALUES_F).

 

PAY_PAYROLL_ACTIONS: This table logs all the actions taken by the Payroll Engine. Primary key is PAYROLL_ACTION_ID, and it logs in each and every activity. The Table is capable enough to store a lot of information as it has got columns to store all kind of data used in Payroll; although it does not populate all the columns / row. However it logs in only the ones those are needed.

 

PAY_RUN_RESULTS: This table stores the status related to the elements against the assignment actions. The primary key is RUN_RESULT_ID. ELEMENT_TYPE_ID and ASSIGNMENT_ACTION_ID are the two other important foreign keys.

 

PAY_RUN_RESULT_VALUES: This table takes the RUN_RESULT_ID and the INPUT_VALUE_ID and stores the Value obtained by the Payroll Engine.

So these were the important tables in Payroll. However here is a list of few others, which are use very frequently. 

  • In the table below, if the Date tracked column is marked as Yes, assume the Primary key to be Composite. The given Primary with bind with the two date tracked columns to make the Composite Primary key.
  • The below table is in Alphabetical order.
  • Some of the values in the column Table could be a view / synonym. However they pull data. That's what we want right? :)

 

 Table Name

 Date Tracked?

 Primary Key

 Description

 PAY_ACCRUAL_PLANS

 No

 ACCRUAL_PLAN_ID

Stores details on the Accrual Plans. ACCRUAL_PLAN_ELEMENT_TYPE_ID can be used for Element value links.
Total Compensation -> Basic -> Accrual Plans 

 PAY_PAYROLLS_F

 Yes

 PAYROLL_ID

Stores the Payroll related data.
Payroll-> Description

 PAY_ASSIGNMENT_ACTIONS

 No

 ASSIGNMENT_ACTION_ID

Stores the Payroll actions against Assignments with action status and sequence. Links with PAYROLL_ACTION_ID. 

 PAY_ASSIGNMENT_LATEST_BALANCES

 No

 

Logs the Balances with DEFINED_BALANCE_ID with each and every assignment action, related to ASSIGNMENT_ACTION_ID.

 PAY_ASSIGNMENT_LINK_USAGES_F

 Yes

 ASSIGNMENT_LINK_USAGE_ID

Logs the assignment and element links with date tracking capabilities. ELEMENT_LINK_ID and ASSIGNMENT_ID are the two foreign keys.

 PAY_BALANCE_ATTRIBUTES

 No

 BALANCE_ATTRIBUTE_ID

Stores the Balance attributes. 
Total Compensation -> Basic -> Balance-> Attributes(B)

 PAY_BALANCE_CATEGORIES_F

 Yes

 BALANCE_CATEGORY_ID

Stores the Balance Categories that can be used while defining Balances. 

 PAY_BALANCE_DIMENSIONS

 No

 BALANCE_DIMENSION_ID

Stores the Balance Dimensions.

 PAY_BALANCE_FEEDS_F

 Yes

 BALANCE_FEED_ID

Stores the Balance Feeds. 

 PAY_BALANCE_TYPES

 No

 BALANCE_TYPE_ID

Stores the Balances. 

 PAY_BATCH_LINES

 No

 BATCH_LINE_ID

Logs the Batch lines at the Batch element entries. Linking the BATCH_ID, ASSIGNMENT_ID and ELEMENT_TYPE_ID. Logs the BATCH_LINE_STATUS.

 PAY_COSTS

 No

 COST_ID

Logs the Cost and the balances as Debits or Credits with respect to each RUN_RESULT_ID and ASSIGNMENT_ACTION_ID.

 PAY_COST_ALLOCATIONS_F

 Yes

 COST_ALLOCATION_ID

Logs the Cost against a COST_ALLOCATION_KEYFLEX_ID, with reference to assignment Ids. This is where costing is logged.

 PAY_COST_ALLOCATION_KEYFLEX

 No

 COST_ALLOCATION_KEYFLEX_ID

The Cost allocation KFF table.

 PAY_DEFINED_BALANCES

 No

 DEFINED_BALANCE_ID

Clubs the Balance with its Dimensions, using BALANCE_TYPE_ID and BALANCE_DIMENSION_ID.

 PAY_ELEMENT_CLASSIFICATIONS

 No

 CLASSIFICATION_ID

Stores the Classifications.
Total Compensation -> Basic -> Classification

 PAY_ELEMENT_SETS

 No

 ELEMENT_SET_ID

Stores details on Element Sets.
Payroll-> Element Set

 PAY_ENTRY_PROCESS_DETAILS

 No

 

Logs the details of Run results with RUN_RESULT_ID and ELEMENT_ENTRY_ID.

 PAY_LINK_INPUT_VALUES_F

 Yes

 LINK_INPUT_VALUE_ID

Clubs Input Values with the Element links.

 PAY_MESSAGE_LINES

 No

 

Logs Payroll Messages

 PAY_PAYMENT_TYPES

 No

 PAYMENT_TYPE_ID

Stores the Payment Methods.
Payroll-> Payment Methods

 PAY_PEOPLE_GROUPS

 No

 PEOPLE_GROUP_ID

 The People Group KFF table.

 PAY_PRE_PAYMENTS

 No

 PRE_PAYMENT_ID

 Logs the Prepayment information against ASSIGNMENT_ACTION_ID with the Monetary Values.

 PAY_QUICKPAY_INCLUSIONS

 No

 

Logs quickpay actions against ASSIGNMENT_ACTION_ID and ELEMENT_ENTRY_ID

 PAY_RETRO_DEFINITIONS

 No

 RETRO_DEFINITION_ID

Stores Retro Definitions.

 PAY_RUN_BALANCES

 No

 RUN_BALANCE_ID

Stores the Balance Updates against ASSIGNMENT_ID and DEFINED_BALANCE_ID.

 PAY_RUN_TYPES_F

 Yes

 RUN_TYPE_ID

Stores the different Payroll Run Types.