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.
|