Oracle Payroll Tables in Oracle Apps

PAY_ELEMENT_TYPES_F:-
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 is also a date track enabled table. This table 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 is also Date track enabled table. This table stores the Input values for each element.  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 also 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 is also Date tracked table. It 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. 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.

Few More tables:-

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.

Item Master Item Types in Oracle Inventory

Item Master Item Types are Lookup Type which is held in the table fnd_common_lookups. Use the below SQL Query to see the entries: 

SELECT lookup_type, lookup_code, meaning
FROM fnd_common_lookups 
WHERE lookup_type = 'ITEM_TYPE' 

order by lookup_type, lookup_code; 

Join Condition:- 
mtl_system_items_b.item_type = fnd_common_lookups.lookup_code

Multi-Org Access Control(MOAC) in Oracle APPS R12-- mo_global

Multi Org Access Control(MOAC):- – Multi Org Access Control(MOAC) enables us to access multiple operating units without changing responsibilities. From a single responsibility user can access multiple operating units. This is now a feature in Release 12 of the Oracle eBusiness Suite, and it changes in some fundamental ways, how the data is accessed.

How does this affect Developers and SQL queries?

Let us understand how things were under the original multi-org structure first introduced in Release 10. Forms generally accessed data through a view that would check an organization parameter.

Example: po_headers_v. This would offer a reasonably well-tuned view that looked up many of the values in related tables, without having to join a lot of lookup tables. The problem for developers was that a query on this view in a SQL session would show no rows(0 rows).

To see the data for a given organization, we had to set an Organization Context for our current session:

BEGIN
    DBMS_APPLICATION_INFO.SET_CLIENT_INFO(204);
END;

This PLSQL block would make these views show all data for organization_id = 204(operating Unit).

If you wanted multi-org access (and many of us do), you would have the option of querying the _ALL table(Multi Org tables), which would offer unrestricted access to all of the data for all organizations, without resolving the lookup values in related tables, so you would end up joining a lot of the other tables, but would end up with a quite specific, hopefully well-tuned query that showed data for all of the operating units.

In Oracle EBS R12, the security is often set at the table level using policies. This can be baffling at first, as you can see you are querying a table, you know there is data there, but you cannot see it. To see what package is controlling the access to a table, you can query the all_policies data dictionary view:

select * from all_policies where object_name = 'PO_HEADERS'

In R12 provides a new package that offers access to a single or all organizations in one call. This package, mo_global is called below for a single organization:

BEGIN
  mo_global.set_policy_context('S',204);
END;

The 1st parameter, ‘S’, means Single Organization(Single Operating Unit), the 2nd parameter is organization_id. Queries on this table will yield a view of all rows for organization_id=204. If you want access to all organization data, you can change the first parameter to ‘M’ (for multiple Operating Units), and no second parameter:

BEGIN
   mo_global.set_policy_context('M');
END;