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;

Record in Oracle

Record gives you a way to store and access values as a group.

• It is composed of one or more fields, each with its own value.
• These fields can be of different datatypes. 
• You can use records to hold related items and pass them to subprograms as a single parameter. 
• When you are creating a record, you are just creating your own datatype. 

PL/SQL supports three different kinds of records:-

       1. Table-based record (table_name % ROWTYPE):-

Based on a table's column structure.Record will have same number of fields as columns in table and each field in record has the same name as a column in a table

      2. Cursor-based record (cursor_name % ROWTYPE):-

Based on the cursor's SELECT statement.Record will have same number of fields as column or expression in the cursor SELECT statement and each field in record has the same name as column or expression in the cursor SELECT statement.

       3. Programmer-defined record :- 

Based on the structure defined by programmer .Each field is defined explicitly (its name and datatype) in the TYPE statement.

Programmer defined composite records are used when

• A field of record needs to be a PL/SQL-specific type, such as BOOLEAN, PLS_INTEGER
• Record type is not dependent on a table or cursor result 
• You want to have a control on number, name and datatype of fields in a record

Cursors in Oracle

There are two types of cursors present in PL/SQL:-

Implicit Cursors:-

• Automatically created whenever a SQL statement (DML) is executed.
• For SELECT statement this implicit cursor consists of rows that will be retrieved.
• For INSERT -->it will be the rows that are going to be inserted
• For UPDATE--> it will be the rows that are going to be affected
• For DELETE--> it will be the rows that will be deleted
• You can find if any row is affected or not and how many rows are affected by using SQL%ATTRIBUTES.

Explicit Cursors:-

• Explicit cursors are explicitly declared in the PL/SQL declare section
• You can access this cursor in the PL/SQL code using cursor name
• Explicit cursors can be defined only for the select statements
• You can find if any row is affected or not and how many rows are affected by using <cursor_name>%ATTRIBUTES

Triggers in Oracle

A Trigger is a stored PL/SQL program unit, which gets implicitly fired by Oracle Database when a triggering event occurs. A Trigger defines an action, which should get executed, when some database related event occurs.

There are different types of triggers.

• Row Triggers and Statement Triggers
• BEFORE and AFTER Triggers
• Compound Triggers
• INSTEAD OF Triggers
• Triggers on System Events and User Events

DML Triggers are associated with a specific database table. The purpose of this trigger is to perform a specific service when a specified DML operation (INSERT, UPDATE, DELETE) occurs on a table.

Unlike the stored procedures or functions which have to be explicitly invoked, these triggers implicitly gets fired or executed whenever the table is affected by any DML operation.

A  Trigger has 3 basic parts:-

Triggering event or Statement:- An SQL statement which causes a trigger to be fired. Every triggering event is associated with trigger timing i.e., when a trigger should execute. According to trigger timing, triggers can be either before or after triggers.

Triggering events can be 

• Before INSERT or After INSERT
• Before UPDATE or After UPDATE
• Before DELETE or After DELETE

Trigger constraint:-This is optional. A Boolean expression which must be TRUE for the trigger to be fired. We can add triggering constraint by using a WHEN clause in trigger.

Trigger action:- Is a PL/SQL code which contains what needs to be done when a trigger is fired.

Structure of Trigger:-
CREATE [OR REPLACE] TRIGGER  <trigger_name> BEFORE | AFTER | INSTEAD OF
DELETE | [OR] INSERT | [OR] UPDATE [ OF <column> [, <column>...]] ON <table>

-- This section is called Triggering event

[ FOR EACH ROW [ WHEN  <condition>] ]

-- When clause will be triggering constraint
BEGIN

-- This PL/SQL block is Triggering action

/*
 PL/SQL Block
 */
...
END;

Row Trigger:- In Row Trigger, the trigger body is executed depending on how many rows were affected by the DML statement. If the DML statement did not affect any rows, the trigger body executes 0 (ZERO) times.

These row triggers will have two pseudo records to get data from triggering event (INSERT, UPDATE, DELETE).

OLD:- references the OLD row before UPDATE or DELETE

NEW:- references the NEW row after INSERT or UPDATE

Row triggers will be used if trigger action depends on the data provided by triggering statement.

Before Trigger:- Before Trigger executes trigger action i.e. PL/SQL code before executing triggering statement INSERT,  UPDATE or  DELETE.

Triggering Constraint:-
If you want any trigger to get fired only on a specific condition, then we can give that condition as Triggering Constraint.Triggering constraint is achieved by adding a WHEN clause in the trigger.

Statement Trigger:- In a statement trigger, the trigger body is executed only once for the statement, irrespective of how many number of rows are affected by the DML statement. 

• FOR EACH ROW clause decides whether it is a row level or statement trigger.
• Statement triggers are useful in complex security check.
• Unlike row triggers, there are no pseudo records associated with statement triggers.

To View the trigger details use below query:

SELECT trigger_name, trigger_type, triggering_event,

    table_name, referencing_names,

    status, trigger_body

FROM   user_triggers

WHERE  trigger_name = 'XX_TRG_NAME';

Drop Trigger using below command:-

DROP TRIGGER <trigger_name>;

Sequence in Oracle

• Sequence is a database object that is used to generate unique numbers. It can be used for generation of primary key values.

• Sequence numbers are generated independently of tables, so the same sequence can be used for one or multiple tables.

Syntax for creating a sequence:-

create sequence <sequence_name>
  [START WITH <integer>]
  [INCREMENT BY <integer>]
  [MAXVALUE <integer>]
  [MINVALUE <integer>]
  [CACHE <integer> | NO CACHE]
  [CYCLE | NO CYCLE];


Sequence Parameters:-

START WITH:- Specifies the first sequence number to be generated.

INCREMENT BY:- Specifies the interval between sequence numbers.

MAXVALUE:- Specifies the maximum value the sequence can generate.

MINVALUE:- Specifies the minimum value of the sequence.

Ex:-

CREATE SEQUENCE xx_seq_id 

 START WITH 1 

 INCREMENT BY 1 

 MAXVALUE 9999;

Once a sequence is created, to retrieve the next value of the sequence use NEXTVAL 

Ex:-

SELECT xx_seq_id.NEXTVAL FROM dual;

To retrieve the current value of the sequence use CURRVAL.

Ex:-
SELECT xx_seq_id.CURRVAL from dual;


Altering a Sequence:-
Syntax:-

ALTER SEQUENCE <sequence_name> <parameter name> <new value>;
Execute the below SQL code to alter the increment value of the sequence xx_seq_id

ALTER SEQUENCE xx_seq_id INCREMENT BY 5;

You cannot alter start value of a sequence.

Deleting a Sequence:-
Syntax:-

DROP SEQUENCE <sequence name>;
Execute the below code to drop the sequence xx_seq_id.

DROP SEQUENCE xx_seq_id.;

How many ways a Function can be invoked in Oracle SQL Statement?

Oracle Function can be invoked from the below clauses of SQL statements
• SELECT clause of SQL statement
• WHERE clause of a SELECT, UPDATE or DELETE statements and HAVING clauses of a SELECT statement
• VALUES clause of an INSERT statement
• SET clause of an UPDATE statement