Oracle PL/SQL(Advanced)

Topics

• Sub Programs (Procedure and Functions)
• Stored Packages
• Database Triggers
• PL/SQL File I/O
• Collections
• Autonomous Transactions
• SQL Loader


Sub Programs in PL/SQL
• Subprograms are named PL/SQL blocks that can take
Parameters and be invoked.
• PL/SQL has two types of subprograms called procedure and
Function
• PL/SQL Programs can be stored in the database as stored
Procedure and can be invoked whenever required.
• Like Unnamed or anonymous PL/SQL block , subprograms
have a declarative part, an executable part, and an optional
Exception handling part.


Procedure
A procedure is a subprogram that perform a specific action
A procedure can be called from any PL/SQL program
Procedures can also be invoked from the SQL prompt
A procedure has two parts:
1 Specification.
2 Body.
Procedure specification begins with the key word Procedure
Followed by the procedure name and an optional list of Arguments,
enclosed within parenthesis.Procedure that take no parameters are
written without paranthesis
Procedure body begins with the Keyword IS or AS and ends with an
END followed by an optional procedure name.


The Procedure body has three parts:
1.Declarative part.
2.Executable part.
3.Exception Handling Part.

1.Declarative Part Contains local declaration.
2.Executable Part contains statements, which are placed between
The keyword BEGIN and Exception (or END).
3Exception Handling Part contains exception handlers, which are placed between the keyword Exception and End. This part is optional.

Syntax
Create [ OR REPLACE] PROCEDURE
( [mode] ,….)
IS AS
[Local declaration]
Begin
PL/SQL executable statements
[Exception
Exception handlers]
End [Procedure_Name];
Where mode indicates the type of the argument (Such as IN,OUT or
IN OUT) and Pl/sql executable statements represents the PL/SQL
Code, enclosed within Begin and End.
The OR REPLACE option is used in case the user wants to modify
a previously stored procedure or function.


Example: Procedure
The Employee Number and the amount to be incremented is
Passed as parameter
Create or Replace Procedure incr_proc (pempno number, amt number) IS
vsalary NUMBER;
Salary_miss Exception;
Begin
Select sal into vsalary from emp where empno= pempno;
If vsalary is NULL THEN
Raise salary_miss;
Else
Update emp set sal = sal + amt
Where empno= pempno;
End if;
Exception
When salary_miss then
Dbms_output.put_line (pempno' has salary as Null');
When no_data_found then
Dbms_output.put_line(pempno' No such Employee Number');
End incr_proc;
/


Calling a Procedure
A procedure is called as a PL/SQL statement. A procedure
can be called from any PL/SQL program by giving their name
followed by the parameters.
To call the procedure named “incr_proc” from a block , the command is
incr_proc (v_empno, v_amount);
v_empno : Local variable storing the employee Number to be incremented.
v_amount : Local variable storing the amount to be incremented.
Procedure can also be invoked from the SQL prompt using the
EXECUTE command
SQL> EXECUTE incr_proc(,);


Functions
A function is a subprogram that returns a value.
A function must have a RETURN clause.
Functions and procedures have a similar structure , except that
The function have a Return Clause.
Create [ OR Replace] Function
([mode],……..)
RETURN datatype IS
[Local Declaration]
BEGIN
PL/SQL executable statements
[EXCEPTION
exception handler]
END [FUNCATION_NAME];


Like a Procedure , a function has two parts:
• The Specification and the Body.
• The function specification begins with the Keyword FUNCTION
And ends with the RETURN Clause, which specifies the datatype
of the result value.
• The function body is exactly same as procedure body.
Return Statement.
• Return Statement immediately returns control to the caller
Environment. Execution then resumes with the statement
following the subprogram call.
• A subprogram can contain several RETURN statements. Executing
Any of them terminates the subprogram immediately. The Return
Statement used in functions must contain an expression, which is
Evaluated when the RETURN statement is executed . The resulting
Value is assigned to the function identifier. Therefore a function must contain
at least one RETURN statement.
• Note: RETURN statement can also be used in procedures. But it should not
contain any expression. The statement simply returns control to the caller
before normal End of the procedure is reached.


Example: Function
CREATE OR REPLACE FUNCTION review_func (pempno Number)
RETURN Number IS
vIncr emp.sal%type;
vNet emp.sal%type;
vsal emp.sal%type;
vcomm emp.comm%type;
vempno emp.empno%type;
BEGIN
SELECT empno,sal ,nvl(comm,0) INTO vempno,vsal,vcomm FROM emp
WHERE empno =pempno;
vNet:=vsal+vcomm;
If vsal<=3000 then vIncr:=0.20* vNet; Elsif vsal>3000 and vsal<=6000 then vIncr:=0.40* vNet ; End if; Return (vincr); End review_func ; / Calling of function named “review_func” from a pl/sql block. DECLARE Incr_sal number; BEGIN Incr_sal:= review_func(7698); Dbms_output.put_line (incr_sal); END; / Actual versus Formal Parameters
• The variables or expressions referenced in the parameter list of a
subprogram call are actual parameters.
• The variables declared in a subprogram specification and referenced
in the Subprogram body are formal parameters
• The following procedure call has two actual parameters pempno and amt.
Incr_proc (pempno, amt)
• The following procedure declaration has two formal parameters
named p_empno and amount
Create or replace procedure increment (p_empno Number ,amount Number) IS
----
--
BEGIN
--
--
END INCREMENT;

Argument Modes
Argument Modes are used to define the behavior of formal parameters
There are 3 argument modes to be used with any subprograms
1. IN: The IN Parameter lets the user pass values to the called
subprogram. Inside the subprogram, the IN Parameter acts
like a constant, therefore, it cannot be Modified.
2 OUT: The Out Mode parameter lets the user return values to the
calling block. Inside the subprogram, the Out Parameter acts like
an uninitialized variable.
3 IN OUT : The IN OUT Parameter lets the user pass initial values
to the called Subprogram and returns updated value to the
calling block.
The three parameters modes IN (the default), OUT, and IN OUT, can be used
with any Subprograms. A procedure or function can change the value of the
argument, which could be used for further processing.


Stored Packages
• A package is a database object that groups logically related
PL/SQL objects.
• Packages encapsulate related procedures, functions,associated
cursors and variables together as logical unit in the database.
• Packages are made of two components: The specifications and
the body.
• The specification is the interface to applications and has
declarative statements.
• The body of a package contains different procedures and
functions.
• Packages are groups of procedures, functions, variables and
SQL statements grouped into a single unit.
• The entire package is loaded into the memory when a procedure,
within the package is called for the first time. This reduces the
unnecessary disk I/O and network traffic.


Packages usually have two parts, a specification and a body.
Package specification
• The specification is the interface to the applications, it declares the
types, variables, constants, exceptions, cursors and subprograms.
• The specification holds public declarations, which are visible to the
applications.
• The scope of these declarations is local to your database schema and
global to the package.So, the declared objects are accessible from
your application and from anywhere in the package.
The Package Body
• The body fully defines cursors and subprograms, and so implements
• The body holds implementation details and private declarations, which
are hidden from the application.
• The scope of these declarations is local to the package body.
• Unlike a package specification, the declarative part of a package body
can contain subprogram bodies.


Main Advantages of Packages
• Better Performance
• Overloading
A package is created interactively with SQL*Plus
using the CREATE PACKAGE and CREATE PACKAGE BODY
commands.
The syntax is:
Package Specification Syntax
CREATE[OR REPLACE] PACKAGE as
/*
DECLARATIONS OF GLOBAL VARIABLES AND CURSORS (IF ANY);
PROCEDURES AND FUNCTIONS;
*/
END [];

Package Body Syntax

CREATE [OR REPLACE] PACKAGE BODY AS
/*
PRIVATE TYPE AND OBJECT DECLARATION,
SUBPROGRAM BODIES;
*/
[BEGIN
--ACTION STATEMENTS;]
END [PKG-NAME];


Example of Package
CREATE OR REPLACE PACKAGE EMP_PACK AS
PROCEDURE EMP_PROC (PEMPNO IN EMP.EMPNO%TYPE);
FUNCTION INCR_FUNC (PEID NUMBER, amt NUMBER)
RETURN NUMBER;
END EMP_PACK;
/
CREATE OR REPLACE PACKAGE BODY EMP_PACK
AS
PROCEDURE EMP_PROC (PEMPNO IN EMP. EMPNO%TYPE)
IS
VTEMPNAME EMP.ENAME%TYPE;
VTESAL EMP.SAL%TYPE;
BEGIN
SELECT ENAME,SAL INTO VTEMPNAME,VTESAL FROM EMP WHERE

EMPNO=PEMPNO;
DBMS_OUTPUT.PUT_LINE (PEMPNO VTEMPNAMEVTESAL);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE (PEMPNO 'NOT FOUND');
END EMP_PROC;
FUNCTION INCR_FUNC (PEID NUMBER, amt NUMBER)
RETURN NUMBER IS
vsalary NUMBER;
salary_miss EXCEPTION;
vtemp NUMBER;
BEGIN
SELECT sal INTO vsalary FROM emp WHERE empno=PEID;
IF vsalary IS NULL THEN
RAISE salary_miss;
/*If the vsalary is NULL an exception salary_miss is raised*/

ELSE
UPDATE emp set sal = sal+ amt where empno =PEID;
vtemp:=vsalary + amt;
RETURN (vtemp);
END IF;
EXCEPTION
WHEN salary_miss THEN
dbms_output.put_line (PEID ' has salary as NULL');
/* If the employee number is not found the exception NO_DATA FOUND
is raised*/
WHEN NO_DATA_FOUND THEN
dbms_output.put_line (PEID 'No such number');
END INCR_FUNC;
END EMP_PACK;
/


Calling a Procedure and Function of a Package

• Calling a Procedure of a Package
EXECUTE EMP_PACK.EMP_PROC(7499);

• Calling a Function of a Package
DECLARE
vsalary NUMBER;
BEGIN
vsalary:=EMP_PACK.INCR_FUNC(7499,100);
dbms_output.put_line (vsalary);
END;
/



Database Triggers
• A database trigger is a stored PL/SQL Program unit
associated with a specific database table
• Unlike the stored procedures or functions
which have to be explicitly invoked, these triggers implicitly
gets fired (executed) Whenever the table is affected by any
SQL operation

Parts of Trigger
--Trigger event
--Trigger Constraints (Optional)
--Trigger action

Trigger Events A triggering event or statement is the sql statement that
Causes a trigger to be fired. A triggering event can be INSER,UPDATE or DELETE
statement for a specific table
Trigger Restriction
Trigger restriction specifies a Boolean expression that must be True for a Trigger
to fire. The Trigger action is not executed if the trigger restriction evaluates to
FALSE. A trigger restriction is an option available for trigger that are fired for each
row. Its function is to conditionally control the execution of the trigger
A trigger restriction is specified using a WHEN clause.
Trigger Action
A trigger action is the procedure (PL/SQL block) that contain the SQL statements
and PLSQL Code to be executed when a triggering Statement is issued and the
trigger restriction evaluates to True

Syntax
Create [or Replace] TRIGGER
BEFORE AFTER INSTEAD OF
DELETE [OR] INSERT [OR] UPDATE [ OF [, …]]
ON
[REFERENCING [OLD [AS] ] [NEW [AS] ]]
[FOR EACH ROW [ WHEN ]]
BEGIN
---
/* PL/SQL Block */
----
END;


Description of Syntax of Database Trigger

Before Option
Oracle Fires the trigger before modifying each row affected by
The triggering statement

After Option
Oracle fires the trigger after modifying each row affected
by the triggering statement

Instead of Option
Oracle fires the trigger for each row , to do something else
Instead of performing the action that executed the trigger.

WHEN Specifies the trigger restriction . This condition has to be satisfied to
fire trigger. The condition can be specified for the ROW TRIGGER.


Statement Level Trigger:
Statement level trigger executes once for each transaction.
For example if a single Transaction inserted 1000 rows into the
EMP table, then a statement level trigger on that table
would only be executed once.


Example:
To create a trigger for the EMP Table,
which makes the entry in the Ename column in Upper case
Create or replace trigger upper_trig1
Before insert or update of ename on emp for each row
Begin
:new.ename:=upper(:new.ename);
End;
/


RAISE_APPLICATION_ERROR
The raise application error is a built in Oracle Procedure which lets user issue the
user defined error messages
Range varies from (-20000 to -20999)
CREATE OR REPLACE TRIGGER Test_Trig BEFORE INSERT on emp FOR EACH ROW
BEGIN
IF inserting then
IF :NEW.sal=1500 then
RAISE_APPLICATION_ERROR(-20231,'Insertion Not Allowed');
END IF;
END IF;
END;
/

Instead of Trigger tells oracle what to do instead of performing
the action that executed the trigger
• Except instead of trigger , all trigger can be used only on tables.
Instead of triggers can be used only on view
Example
• View Create
create or replace view v_instead1 as
select empno,ename,emp.deptno,job,sal,dname,loc from emp,dept
where emp.deptno= dept.deptno
• Trigger Create
create or replace trigger Tv_instead1 instead of delete on v_instead1 for each row begin
delete from emp where deptno=:old.deptno;
delete from dept where deptno=:old.deptno;
end;

PL/SQL File I/O (Input/Output)

File I/O is done through the supplied package UTL_FILE.
• The Oracle 8i Server adds file Input/Output capabilities to PL/SQL.
• This is done through the supplied package UTL_FILE.
• This package has some procedures and functions which add power to
interact with a file.
• The file I/O capabilities are similar to those of the standard operating
system stream file I/O (OPEN,GET,PUT,CLOSE), similar to the C
programming file functions with some limitations.
• For example, you call the FOPEN function to return a file handle,
which you then use in subsequent calls to GET_LINE or PUT to
perform stream I/O to a file.
•After performing I/O on the file FCLOSE can be used to close the file.

Syntax and use of the UTL_FILE package Functions and Procedures:
1. FUNCTION FOPEN
FUNCTION FOPEN (LOCATION, FILENAME, OPEN_MODE)
RETURN UTL_FILE.FILE_TYPE;
LOCATION is the operating system-specific string that specifies the
directory or area in which to open the file
FILENAME is the name of the file, including extension, without any
directory information.
OPEN_MODE is a string that specifies how the file is to be opened.
Options allowed are “R” for Read, “W” for Write and “A” for Append.
Function FOPEN returns a file handle that is used in subsequent file
Operations.
Example:
V_FILENAME:=UTL_FILE.FOPEN(P_FILEDIR, P_FILENAME, ‘R’);

2. FUNCTION IS_OPEN
FUNCTION IS_OPEN (FILE)
RETURN BOOLEAN;
where
FILE is the value returned by FOPEN.
Function IS_OPEN tests a file handle to determine if it identifies an open
file.
Example:
BEGIN
IF UTL_FILE.IS_OPEN (‘P_FILE’) THEN

….
….
END IF;
END;

3. PROCEDURE FCLOSE
PROCEDURE FCLOSE (FILE)
where
FILE is the value returned by an FOPEN operation.
PROCEDURE FCLOSE closes the open file identified by FILE.
Example:
UTL_FILE.FCLOSE(V_FILEHANDLE);

4. PROCEDURE GET_LINE
PROCEDURE GET_LINE (FILE, BUFFER);
where
FILE is the value returned by an FOPEN operation.
BUFFER holds the read text.

5. PROCEDURE GET_LINE reads a line of text from the open file
identified by FILE and places the text in the output BUFFER.
Example:
UTL_FILE.GET_LINE (V_FILEHANDLE, V_NEWLINE);

6. PROCEDURE PUT_LINE
PROCEDURE PUT_LINE (FILE, BUFFER);
where
FILE is the value returned by an FOPEN operation.
BUFFER is the text to write.
PROCEDURE PUT_LINE writes text string BUFFER to the file
identified by FILE, then writes a line terminator
Example:
UTL_FILE.PUT_LINE (V_FILEHANDLE, ‘ROLLNO NAME SUBJECT ’);
UTL_FILE.PUT_LINE (V_FILEHANDLE, ‘--------- ----- ------ ’);
The file will be written with the following format:
ROLLNO NAME SUBJECT
--------- ---- ------

Collections

• Nested Tables
• VARYING Arrays (VARRAY)

Nested Tables
A nested tables is useful for data models requiring referential integrity
and is suitable for master-detail and one to many relationships.
A nested table is a database table which stores data in it, that cannot be

A Nested Table can be included in a table definition as one of the
columns. That is why, they are known as Nested Tables.
Nested Tables can be manipulated directly using SQL.

To create a nested table the syntax is as follows:
CREATE TYPE TABLE_NAME IS TABLE OF TABLE_TYPE [NOT NULL];
where:
TABLE_NAME The name of new type
TABLE_TYPE The type of each element in a nested table. It can be
built-in type, a user defined type or a reference to
object type.
Example:
CREATE TYPE BOOKS_TYPE AS OBJECT
(BOOK_NO NUMBER (4),
BOOK_TITLE VARCHAR (20),
AUTHOR VARCHAR2(20));
/
Type created.
CREATE TYPE BOOKS AS TABLE OF BOOKS_TYPE;
/
Type created.

Example:
CREATE TABLE STUDENT (
STUDENT_NO NUMBER(4) NOT NULL,
STUDENT_NAME VARCHAR2(25),
BOOKS_ISSUED BOOKS )
NESTED TABLE BOOKS_ISSUED STORE AS BOOK_TABLE;
Table created.
Inserting Records
INSERT INTO STUDENT VALUES
(1001, 'AMIT KUMAR',
BOOKS (BOOKS_TYPE(3211, 'ORACLE 8 UNLEASHED', 'KAIT'),
BOOKS_TYPE(3922, 'PL/SQL PROG','J J')));
accessed directly.

Update is used to modify the store table
• The Operator allows nested tables to be manipulated using
DML when it is stored in a table
• The table takes the sub query as argument and returns the
Nested table to be used in DML.
• The Sub Query must return single nested columns
Example:
UPDATE THE (SELECT BOOKS_ISSUED FROM STUDENT WHERE
STUDENT_NO=1001)
SET BOOK_TITLE = 'ORACLE UNLEASHED'
WHERE BOOK_NO=3211;
Inserting records in nested Table
Example:
Insert into the (select books_issued from student where student_no=1001)
values (books_type(5111, 'Visual Basic', 'Ken'));

Selecting a records from the Nested Table
Example:
select * FROM THE (SELECT BOOKS_ISSUED FROM STUDENT WHERE
STUDENT_NO=1001) WHERE BOOK_NO=3211
/
Deleting a records from the Nested Table
For deleting a row from the nested table using the condition from the
nested table
DELETE FROM THE (SELECT BOOKS_ISSUED FROM STUDENT WHERE
STUDENT_NO=1001) WHERE BOOK_NO=3211;


VARRAYS

Varray is a datatype similar to an array in C or PASCAL. Elements are
inserted into a VARRAY, starting at index 1 up to the maximum length
declared in the VARRAY type.

A VARRAY has the following:
• COUNT Current Number of elements
• LIMIT Maximum number of elements the VARRAY can
contain. The LIMIT is user defined.
Each element of the array has the position indicated by an index which
can range from one to the COUNT value.
A VARRAY can be stored in a database column. A VARRAY can only
be manipulated as a whole. Individual elements of stored VARRAYS
cannot be modified.


Creating VARRAYs
Example:
CREATE TYPE BOOKS_TYPE1 AS OBJECT
(BOOK_NO NUMBER (4),
BOOK_TITLE VARCHAR (20),
AUTHOR VARCHAR2(20));
/
Type created.
CREATE OR REPLACE TYPE BOOKS_ARRAY AS VARRAY(10)
OF BOOKS_TYPE1;
/
Type created.
CREATE TABLE STUDENTS1(
STUDENT_NO NUMBER(4),
BOOKS_ISSUED BOOKS_ARRAY);
Table created.


Inserting Rows
The values can be inserted in a VARRAY as a whole, we can insert rows Into the table using
the constructor as follows:
INSERT INTO STUDENTS1
VALUES
(2355, BOOKS_ARRAY(
BOOKS_TYPE1(1020,'ORACLE BEGINNERS', 'ORACLE PRESS'),
BOOKS_TYPE1(1111,'TUNING ORACLE','CONEY')));
This row contains a VARRAY BOOKS_ARRAY having two elements

Updating Rows
The VARRAYs can be updated as a whole. Individual elements cannot
be accessed.
UPDATE STUDENTS1
SET BOOKS_ISSUED=BOOKS_ARRAY(
BOOKS_TYPE1(1020,'ORACLE BEGINNERS','ORACLE PRESS'))
WHERE STUDENT_NO=2355;
The row for STUDENT_NO 2355, will have only one element in the
VARRAY column.

To modify a stored VARRY, it has to be selected into a PLSQL variable and then inserted back into the table .
The Technique is as below:
Note:
LIST_OF_BOOKS.COUNT gives the number of elements in the VARRAY
LIST_OF_BOOK.EXTEND appends one element ot the array i.e increase the array limit by 1
Example
DECLARE
LIST_OF_BOOKS BOOKS_ARRAY;
BEGIN
SELECT BOOKS_ISSUED INTO LIST_OF_BOOKS
FROM STUDENTS1 WHERE STUDENT_NO=2355;
DBMS_OUTPUT.PUT_LINE('NO OF BOOKS ISSUED' TO_CHAR(LIST_OF_BOOKS.COUNT));
DBMS_OUTPUT.PUT_LINE('ARRAY LIMIT' LIST_OF_BOOKS.LIMIT);
LIST_OF_BOOKS.EXTEND;
LIST_OF_BOOKS(LIST_OF_BOOKS.COUNT):=BOOKS_TYPE(2334,'DBA HANDBOOK','KEVIN');
UPDATE STUDENTS1 SET BOOKS_ISSUED=LIST_OF_BOOKS WHERE STUDENT_NO=2355;
COMMIT;
END;
/

Autonomous Transactions
An Autonomous Transaction (AT) is an independent transaction started by another
Transaction, The main transaction (MT). At the time of execution it lets you
suspend the main transaction, do SQL Operation, Commit, or
rollback those operation , then resume the main transaction. It always
Executes with in an autonomous scope.
An autonomous Block is a routine marked with the
PRAGMA AUTONOMOUS_TRANSACTION

Example of Autonomous Transaction
Main Transaction
CREATE OR REPLACE Procedure PROC1_TRAN is
EMP_ID NUMBER:=7499;
BEGIN
UPDATE EMP SET SAL =SAL+100
WHERE EMPNO=EMP_ID;
PROC2_TRAN(EMP_ID);
DELETE FROM EMP
WHERE EMPNO=EMP_ID;
ROLLBACK;
END;
/
Autonomous Transaction
CREATE OR REPLACE Procedure PROC2_TRAN(P_EMP_ID NUMBER) is
PRAGMA AUTONOMOUS_TRANSACTION;
DEPT_ID NUMBER;
BEGIN
SELECT DEPTNO INTO DEPT_ID FROM EMP WHERE EMPNO=P_EMP_ID;
UPDATE DEPT SET DNAME='ACCOUNTING1'
WHERE DEPTNO=30;
COMMIT;
END;
/


SQL loader
This tool is used to move the data from Non –oracle standard source into the oracle
Database Using sql loader a user can
Load data from multiple data files
Two types on input have to be provided to SQL*Loader.
The data file containing the actual data
The control file containing the specification which drive the sql*loader session

SQL Loader generates three files
The log file (Summary of the execution of the Load)
The bad File (Rejected by Oracle)
The discard File (Doesn't meet the condition)

Create a Data File EMPLOYEE.TXT with following contents :
3000,HARPREET,ENGG,10000
3001,PREET1, ,20000
Create a Control File TEST.CTL with following contents :
LOAD DATA
INFILE 'EMPLOYEE.TXT'
APPEND
INTO TABLE EMP
WHEN JOB != ' '
Fields Terminated By ","
(
EMPNO
,ENAME
,JOB
,SAL
)

Calling a Control File TEST.CTL which in turn refers to EMPLOYEE.TXT file for Data are:
SQLLDR CONTROL=TEST.CTL

AutoInvoice

Auto Invoice??

Auto Invoice is a tool that can be used to import and validate transaction data from other financial systems from which one can create invoices, debit memos, credit memos, and on-account credits. It rejects transactions with invalid information to insure the integrity of the data. This fits well with in Oracle ERP or to integrate with any third party application.

What Module data can be integrated?

Oracle Order Management
Oracle Project Accounting
Oracle services


To make fully functional what else required?

Loader program
Validation program

Top 10 reasons for using Auto Invoice

1. Powerful Interface Tool
2. Supports Oracle & Non-Oracle Systems
3. Import Large Amount of Data
4. Calculate or Import Tax
5. Group Lines & Invoices
6. Online Error Correction
7 .Lines Validation
8. Derive GL Date
9 .Import Flex fields
10.Import or Derive Accounting Info

What is inside AutoInvoice

AutoInvoice is a tool consists of 3 main programs. Each program will have unique nature of work to do and they are called internally except Purge program whose execution is derived on the setup otherwise ready to execute stand alone.

Master (RAXMTR)
Import (RAXTRX)
Purge (RAXDEL)

1. Auto Invoice Master program RAXMTR

Selects and marks records in the interface tables to be processed based on the parameters the user entered and then calls the AutoInvoice Import program. Auto Invoice Master program has no report output.

•Gathers statistics, it means it gathers the stats on interface tables and set the stats on certain indices on interface tables
•Marks interface records for processing by marking request_id
•Submits multiple workers for Parallel Processing by creating instances for request.

2. Auto Invoice Import Program Validates the selected record and creates transaction if it passes validation. Any record that fails validation is left in the interface table with an error code. Depending on the setup, related records may be rejected as well. This program has an output file called Auto Invoice Execution report, which you can view by clicking the View Report button in the Requests window.

Working of Auto invoice
Validates data
Inserts records
Deletes interface data
Only when system option purge set to ‘Y’

3. Auto Invoice Purge Program Deletes records from the interface tables. If you set the Purge Interface Table system option to No in Define System Option window, Auto Invoice does not delete processed records from the interface tables after each run,and we must submit Auto Invoice Purge Program periodically to clean up the interface tables. This program only deletes transaction lines that have been successfully imported.

•Deletes all rows where interface_status =‘P’

•Ra_interface_lines

•Ra_interface_distributions

•Ra_interface_salescredits


Oracle Receivable’s Auto Invoice program will be used to import and validate Invoices.

A custom feeder program is required to transfer data from the Advantage extract files and populate the Auto Invoice interface tables (RA_INTERFACE_LINES_ALL and RA_INTERFACE_DISTRIBUTIONS_ALL).If there is need to run populate sales credit into RA_INTERFACE_SALESCREDITS_ALL table.


When run, AutoInvoice produces the AutoInvoice Execution Report and the AutoInvoice Validation Report.

Any entries which failed validation can be reviewed in Oracle Receivables’ AutoInvoice Interface Exceptions window. Depending on the error, changes may need to be made in Receivables, the feeder program or the imported records in the interface tables.


How Autoinvoice Execution works

Normally, Auto Invoice can be divided into three major phases

Pre-grouping: here the validates all of the line level data takes place
Grouping: groups lines based on the grouping rules and validates header level data
Transfer :validates information that exists in Receivables tables

What happen when AutoInvoice run?

Once the Auto invoice Program gets called, the following activity takes place is part of execution process. This can be analyzed by debug options.

Line, accounting, and sales credit information for each line populates 3 interface tables
Lines are ordered and grouped
Tax is calculated
GL date is determined
GL accounts are assigned using Auto Accounting
Tax, freight, commitments, and credit memos are linked to transaction lines
All transactions are batched
Validated lines are used to create the transaction

How Data is flowing?

Select, insert and update and delete take place on certain tables once it is logged out.

Selects

– RA_INTERFACE_LINES_ALL
– RA_INTERFACE_DISTRIBUTIONS_ALL
– RA_INTERFACE_SALESCREDITS_ALL


Updates/Insert

– RA_INTERFACE_ERRORS_ALL
– RA_CUSTOMER_TRX_ALL
– RA_CUSTOMER_TRX_LINES_ALL
– AR_PAYMENT_SCHEDULES_ALL
– AR_RECEIVABLE_APPLICATIONS_ALL

Inserts

– RA_INTERFACE_ERRORS_ALL


AutoInvoice Exception Handling

Records that fail validation are called ‘Exceptions’

Exceptions stay in Interface Tables which is RA_INTERFACE_ERRORS_ALL
Errors can be corrected in the Exception Handling window
Once corrections are made, Auto invoice must be resubmitted
Records that pass validation get transferred to Receivables tables

AutoInvoice Exception Handling Windows

-Interface Exception window displays exception messages associated with all invalid records
-Interface Lines window displays records that fail validation, provides an error message and can be used to correct the errors
-The Line Errors windows displays errors associated with a specific line, and can only be opened from Interface Lines window
-Interface Exceptions window displays Interface Id, Exception Type, Error Message and Invalid Value associated to the error
-Data cannot be edited in this window, but error can be viewed and corrected by clicking the Details button
-Error Message and Column name with invalid data are displayed in the Message column, and the invalid value that needs to be corrected is displayed in the Invalid Value column

Oracle PL/SQL (Fundamentals)

• PL/SQL
• Advantages of PL/SQL
• PL/SQL Block Structure
• Fundamentals of PLSQL
• Variables and Constants
• Bind Variables
• Built in Functions
• Conditional and Iterative Control
• Sql within PL/SQL
• Composite Datatypes (PL/SQL Records and PLSQL Tables)
• Cursors
• Exceptions



• PL/SQL is the procedural extension to the non-procedural SQL
• Combines data manipulation of SQL and procedural power of
standard procedural languages.
• Supports sub-programming features such as Procedures and
Functions.


The Advantages of PL/SQL

• Support for SQL
• Block Structure
• Control Structures
• Better Performance
• Higher Productivity


PL/SQL Block Structure


Anonymous Blocks

Anonymous block is block without a name. These blocks are
declared at the point in an application where they are to be run,
and passed to the PL/SQL engine for execution at run-time.

The structure anonymous block is as follows:
DECLARE

BEGIN

EXCEPTION

END;



Fundamentals of PL/SQL


• Character Set
• Reserved Words
• Lexical Units
• Delimiters
• Identifiers
• Literals


Variables and Constants

• Variables are used to store the result of a query or calculation.
• Variables must be declared before use.
• DEFAULT reserve word is used to initialize variables and
constants.
• Variables can also be declared using the row attributes of a
table %ROWTYPE and %TYPE.


Declaring Variables and Constants

• Variables are declared in the DECLARE section of the PL/SQL
block.
• Declaration involves the name of the variable followed by its
data type.
• All statements must end with a semicolon.
• Initial values can also be assigned to a variable at the time of
declaration.
• To assign a value to a variable, the assignment operator := is used.
• They can also specify initial value and specify NOT NULL
constraints.



Using DEFAULT

The reserved word DEFAULT can be used instead of the assignment operator to initialize variables and constants. For e.g.., the declarations
DEPTNO1 NUMBER (4):=40;
PIN_CODE1 CONSTANT NUMBER (6):= 110005;
can be rewritten as follows:
DEPTNO1 NUMBER (4) DEFAULT 40;
PIN_CODE1 CONSTANT NUMBER (6) DEFAULT 110005;


Using %TYPE

To avoid type and size conflict between a variable and the column of a
table, the attribute %TYPE is used. Advantage of this method of defining a variable is that, whenever the type and/or size of a column in the table is changed, it is automatically reflected in the variable declaration.
TMP_NAME EMP.ENAME%TYPE;
Here, the variable TMP_NAME will be of the same size and type as that of the same ename column of emp table.


Using %ROWTYPE

The %ROWTYPE attribute provides a record type that represents a row in a table. The record can store an entire row of data selected
from the table. In case, variables for the entire row of a table need to declared, then instead of declaring them individually, the attribute %ROWTYPE is used:
EMP_ROW_VAR1 EMP%ROWTYPE;
Here, the variable EMP_ROW_VAR1 will be a composite variable, consisting of the column names of the table as its members. To refer to a specific Variable, say sal, the following syntax will be used:
EMP_ROW_VAR1.SAL:= 5500;



Scope and Visibility of a Variable

• The scope of a variable is the portion of the program in which
the variable can be accessed.
• The visibility of a variable is the portion of the program where the
variable can be accessed without having to qualify the reference.



Bind Variables

• A bind variable is a variable that you declare in a host environment.
• Bind variables can be used to pass run-time values, either number or
character, into or out of one or more PL/SQL programs.
• The PL/SQL programs use bind variables as they would use any
other variable.
Creating Bind Variables
• VARIABLE return_code NUMBER
• VARIABLE return_msg VARCHAR2(33)
• VARIABLE RESULT NUMBER


Displaying Bind Variables

To display the current value of bind variables in the SQL*Plus environment, use the PRINT command.
An example of using a host variable in a PL/SQL block:
BEGIN
SELECT (SAL*12) + NVL (COMM,0) INTO :RESULT
FROM emp WHERE empno =7369;
END;
/
PRINT RESULT
To reference a bind variable in PL/SQL, you must prefix its name with a colon(:) .



Built-in-Functions

PL/SQL provides many powerful functions to enable easy data
manipulation.
• The built-in-functions fall into the following categories:
• Error reporting Functions.
• Single-row number Functions.
• Single-row character Functions.
• Datatype conversion Functions.
• Date Functions.



Conditional and Iterative Control

The conditional control available with PL/SQL are

• IF THEN-ELSE Statement
The types of loops available with PL/SQL are:

• LOOP-END LOOP
• FOR-LOOP
• WHILE-LOOP


IF-THEN-ELSE statement

The IF clause can be used for the conditional processing of statements.
If the condition specified after the IF clause evaluates to true, the
statements following the THEN clause are executed until one of the
following is encountered: ELSIF,ELSE or END IF.
The syntax for an IF-THEN-ELSE statement is
IF THEN

[ELSIF THEN
]
ELSE

END IF;



ELSE Clause

The ELSE clauses is optional. It should always be attached to IF clause.
Example
IF trans_type=‘CR’ THEN
UPDATE accounts SET bal=bal+credit WHERE………..
ELSE
UPDATE accounts SET bal=bal+credit WHERE………..
END IF;



ELSIF Clause

The ELSIF clause is also optional as ELSE clause. If the first condition
evaluates to FALSE the ELSIF tests another condition. An if statement
can have any number of ELSIF clauses.
Example:
IF sales>5000 THEN
bonus:=1500;
ELSIF sales<3500 THEN
bonus:=500;
ELSE
bonus:=1000;
END IF;




Simple Loop (LOOP-ENDLOOP)

The syntax is:
LOOP

END LOOP;
Each time the flow of execution reaches the END LOOP statement,
control is returned to the corresponding LOOP statement above it.
This LOOP is endless without EXIT statement.



The EXIT Statement

To control the termination of above mentioned loop EXIT statement is
used. EXIT statement allows control to be passed to the next statement
beyond END LOOP, thus ending the loop immediately.
Example:
LOOP
ctr:=ctr+1;
IF ctr =10 THEN
EXIT;
END IF;
END LOOP;




EXIT WHEN Statement

EXIT-WHEN statement allows a loop to complete conditionally. When
the EXIT statement is encountered, the condition in the WHEN clause
is evaluated. If the condition evaluates to TRUE, the loop completes and
the control passes to the next statement after the loop.
The EXIT-WHEN statement replaces a simple If statement.
The syntax is:
EXIT [loop-label] [WHEN condition];
Example:
LOOP
ctr:=ctr+1;
EXIT WHEN ctr =10;
END LOOP;




FOR LOOP

FOR loops iterate over a specified range of integers. The range is part
of an iteration scheme, which is enclosed by the keywords FOR and
LOOP.
The syntax is
FOR IN .. LOOP

END LOOP;
is the name of variable whose value will be
incremented/decremented automatically on each iteration of the loop.
The index variable has the following properties:
• It is of datatype NUMBER and need not be declared
• It’s Scope is only within the FOR loop.
• Within the FOR loop, the index variable can be referenced, but not
modified.
and are integer expressions which determine the range
of values for the control variable.
By default, the control variable begins with the value of and is
Incremented by +1 on each iteration until is reached. The loop
is terminated at the end of this iteration.
Example:
FOR ctr IN 1..20
LOOP
INSERT INTO temp values (ctr);
……….
……….
END LOOP:
NOTE: If ctr is required beyond the end of the loop, then it must be
copied to a declared variable before the loop ends.




WHILE-LOOP

The WHILE loop tests the condition provided, and if evaluates to true,
then the statements within the LOOP and END LOOP are executed. The
loop continues as long as the condition is true.
The syntax is
WHILE LOOP

END LOOP;
Example
WHILE total<=2000 LOOP
………………..
SELECT sal into salary FROM emp WHERE
total := total+salary;
END LOOP;




SQL Within PL/SQL

• DML in PL/SQL
• The INTO Clause.
Points to remember while using SQL commands within PL/SQL
• SELECT statements which do not return a single row will cause an
exception to be raised.
• DML commands can process multiple rows.
DML in PL/SQL
The allowable DML statements are SELECT, INSERT, UPDATE and
DELETE.
The INTO Clause
The INTO clause is used with SELECT, to store values from the table
into variables.




Writing PL/SQL Code

PL/SQL Code is written using any text editor. The PL/SQL program is
compiled and executed using the command @.

• Inserting Comments in PL/SQL Program
Comments can be placed in PL/SQL with a double minus(-) preceding
the comment or within/*….*/.
• dbms_output.put_line( )
The procedure dbms_output.put_line will produce the output on the
screen. It accepts only one argument.
Hence, the different variables are concatenated with double pipe()
symbol.
To enable the server output, the SET SERVER OUTPUT ON command
must be given at the SQL*Plus prompt, prior to the execution of the
dbms_output.put_line function.


Example: A.

Write a PL/SQL code to update salary of employees number is 7499 to 5000 if salary is less than 5000.
DECLARE
x NUMBER;
BEGIN
SELECT sal INTO x FROM emp WHERE empno =7499;

IF x<5000 THEN

UPDATE emp SET sal=5000 WHERE empno=7499;
END IF;
END;
/



Example: B.

Write a PL/SQL code to insert all the details of employee no 7499 to a new table emp1 which has same structure as emp table.
DECLARE
v_newrec emp%ROWTYPE;
BEGIN
SELECT * into v_newrec FROM emp WHERE empno=7499;
INSERT into emp1
VALUES (v_newrec.empno, v_newrec.ename, v_newrec.job,
v_newrec.mgr, v_newrec.hiredate, v_newrec.sal, v_newrec.comm,
v_newrec.deptno);
END;
/




Composite Datatypes

PL/SQL Records
• PL/SQL records provides a way to deal with separate but related
variables as a unit.
• PL/SQL record is a variable that may contain a collection of separate
values, each individually addressable.
• The record type has to be defined before its record can be declared.
• In case one of the record component is a record, then it is called a
nested record.
The syntax for creating a record is
TYPE IS RECORD
({field-typevariable%TYPEtable.column%TYPE
table%ROWTYPE},{field-typevariable%TYPE
table.column%TYPEtable%ROWTYPE} ……);



PL/SQL Tables

• PL/SQL tables are modeled as database tables, but are actually not
• Primary keys can be associated with them to have array-like access to
rows
• The size can be dynamically increased by adding more rows when
required. However no rows can be deleted
• PL/SQL tables can have one column and a primary key, neither of
which can be named
• Column can belong to any scalar type, but the primary key must
belong to BINARY_INTEGER
TYPEIS TABLE OF
[NOT NULL]
INDEX BY BINARY_INTEGER;


Example: PL/SQL Records
The following PL/SQL program displays the total salary which
includes commission of empno 7369. It should also display
employees name, his department details and his old and new salary.

Declare
TYPE Deptrec is record
(dno dept.deptno%TYPE,
vdname dept.dname %TYPE,
vloc dept.loc%TYPE,
name emp.ename%TYPE,
vsal emp.sal%TYPE,
vcom emp.comm%TYPE,
newsal emp.sal%TYPE);
dept_det deptrec;

BEGIN

SELECT ename, sal, comm, dept.deptno, dname, loc into dept_det.name,
dept_det.vsal, dept_det.vcom, dept_det.dno, dept_det.vdname, dept_det.
vloc FROM emp, dept
WHERE emp.deptno=dept.deptno
and empno=7369;
dept_det.newsal:=dept_det.vsal+NVL(dept_det.vcom,0);
DBMS_OUTPUT.PUT_LINE
(dept_det.dnodept_det.vdnamedept_det.vloc
dept_det.namedept_det.vsaldept_det.vcomdept_det.newsal);
END;
/



Examples: PL/SQL Tables
To load the employees names and salaries into PL/SQL tables and then
display the contents of the table.

DECLARE
TYPE EMPNAMETYPE IS TABLE OF EMP.ENAME%TYPE NOT NULL
INDEX BY BINARY_INTEGER;
TYPE EMPSALTYPE IS TABLE OF EMP.SAL%TYPE
INDEX BY BINARY_INTEGER;
ENAMELIST EMPNAMETYPE;
SALARYLIST EMPSALTYPE;
SUBSCRIPT BINARY_INTEGER:=1;
CTR NUMBER:=1;
BEGIN
FOR EMPREC IN (SELECT ENAME, SAL FROM EMP) LOOP
ENAMELIST (SUBSCRIPT):=EMPREC.ENAME;
SALARYLIST(SUBSCRIPT):=EMPREC.SAL;
SUBSCRIPT:=SUBSCRIPT+1;
END LOOP;

WHILE ctr LOOP
DBMS_OUTPUT.PUT_LINE (ENAMELIST(ctr));
DBMS_OUTPUT.PUT_LINE (SALARYLIST(ctr));
CTR:=CTR+1;
END LOOP;
END;
/




Cursors

Cursors: Oracle Uses work area called Private SQL areas to execute SQL Statements and store information. A Cursor is a PL/SQL construct that allows you to name these work areas, and to access their stored information
Types of Cursor
• Implicit Cursors
• Explicit Cursors


Implicit Cursor: Implicit Cursor are declared by PL/SQL implicitly for all DML Statements and for single row queries
Example: Select Statement issued directly within the BEGIN .. END part of a block opens up an implicit cursor.

Explicit Cursors : Declared and named by the programmer
Explicit Cursors allow multiple rows to be processed from the query.


Implicit Cursor

DECLARE
v_x_sal number;
BEGIN
SELECT sal INTO v_x_sal FROM emp where empno=7499;
DBMS_OUTPUT.PUT_LINE(v_x_sal);
END;
/


Implicit Cursor has four attributes
• SQL%NOTFOUND
• SQL%FOUND
• SQL%ISOPEN
• SQL%ROWCOUNT



Explicit Cursors


Active Set: The Set of rows returned by a multiple row query
Its Size is the number of rows that meets your search criteria
Explicit cursor points to the current row in the active set. This allows your program to process the rows one at a time.

Explicit Cursors

Declaring a Cursor
• Cursor Name
• Structure of the Query
Syntax: CURSOR IS
: It includes most of the usual clauses,
but INTO Clause is not allowed

Example:
DECLARE

CURSOR c1 is SELECT ename, deptno FROM emp
WHERE sal>2100;
………………………………
BEGIN
………………………………
END;


Opening a Cursor

Here Query execution is done. After Opening the Cursor the rows returned by the query are available for fetching.
Syntax: Open ;
This statement is used within the executable section of the block.
It also establishes an active set of the rows

Example:
OPEN c1;
The cursor will now point to the first row in the active set.



Retrieving Individual rows

After the cursor is opened the current row is loaded
Into variables. The current row is the row at which
The cursor is currently pointing
The retrieval of data into PL/SQL variable or host
Variable is done through FETCH statement
Syntax:
FETCH INTO ;
• For each column value returned by the query
Associated with the cursor, there must be a
Corresponding variable in the INTO list.
• ALSO their Datatypes must be compatible


Example of Using Fetch Command
DECLARE
v_sal number;
CURSOR c1 is SELECT sal FROM emp WHERE job='CLERK';
BEGIN
OPEN C1;
LOOP
FETCH C1 into v_sal;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE (v_sal);
END LOOP;
CLOSE C1;
END;
/



CLOSING A CURSOR
It explicitly closes the cursor, allowing it to be
Reopened , if required.
Syntax:
CLOSE
Example: CLOSE c1
Example of Using CLOSE
DECLARE
v_name emp.ename%TYPE;
CURSOR c1 is select ename from emp;
BEGIN
OPEN c1;
LOOP
FETCH c1 into v_name;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_name);
END LOOP;
CLOSE C1;
END;
/


Explicit Cursor Attributes


Attribute - Type - Description
%ISOPEN - Boolean - Evaluates to TRUE if the cursor is open
%NOTFOUND - Boolean - Evaluates to TRUE if the most recent fetch does not return a row
%FOUND - Boolean - Evaluates to TRUE if the most recent fetch returns a row
%ROWCOUNT - NUMBER - Evaluates to the total number of rows returned so far


Cursor and Records

Process the rows of the active set by fetching values into a PL/SQL Record
DECLARE
CURSOR emp_cursor is Select empno,ename FROM emp;
Emp_record emp_cursor%rowtype;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO emp_record;
EXIT WHEN emp_cursor%notfound;
DBMS_OUTPUT.PUT_LINE(emp_record.ename);
END LOOP;
CLOSE emp_cursor;
END;
/


Cursor FOR Loops

Syntax:
FOR record_name IN cursor_name LOOP
statement1;
statement2;
. . .
END LOOP;
•The cursor FOR loop is a shortcut to process
Explicit cursors
•Implicit Open,fetch,exit and close occurs
•The record is implicitly declared


Example: Cursor FOR Loop
DECLARE
CURSOR emp_cursor IS
SELECT ename,deptno FROM Emp;
BEGIN
FOR emp_record IN emp_cursor
LOOP
IF emp_record.deptno = 20 THEN
DBMS_OUTPUT.PUT_LINE ('Employee ' emp_record.ename
'works in the Research Dept.');
END IF;
END LOOP;
END;
/



Cursor For Loops Using Sub queries


• No need to declare the cursor
Example:
Begin
FOR emp_record in (SELECT ename, deptno from emp)
LOOP
IF emp_record.deptno =20 THEN
DBMS_OUTPUT.PUT_LINE ('Employee '
emp_record.ename ' works in the Research Dept. ');
END IF;
END LOOP;
END;
/



Difference between Implicit and Explicit Cursor

Implicit Cursors : Used for all DML Statements and
Single row queries.
Explicit Cursors : Used for queries of zero, one or
More rows.



Cursor with Parameters

Syntax:
CURSOR cursor_name(parameter_name datatype, )
IS
Select_statement;
• Pass parameter values to a cursor when the cursor is opened and the query is executed
• Open an explicit cursor several times with different active set each time
Open cursor_name(parameter_value , ……);


Example Cursor with Parameters
DECLARE
CURSOR c1(p1 number) is
Select empno,ename,sal,deptno from emp where deptno=p1;
Crec c1%rowtype;
Vdeptno number:=20;
BEGIN
OPEN c1(10);
LOOP
FETCH c1 into crec;
IF c1%FOUND THEN
IF crec.sal >5000 THEN
Dbms_output.put_line(crec.empno' 'crec.sal ' ' crec.deptno);
End if;
ELSE
Exit;
END IF;
END LOOP;
CLOSE C1;
OPEN C1(Vdeptno);
LOOP
FETCH c1 into crec;
IF c1%FOUND THEN
IF crec.sal >5000 then
Insert into emp1(empno,ename,sal,deptno) values (crec.empno,crec.ename,crec.sal,crec.deptno);
END IF;
ELSE
Exit;
END IF;
END LOOP;
CLOSE C1;
END;
/



The For Update Clause

Syntax:
SELECT …..
FROM ……
FOR UPDATE [OF column_reference][NOWAIT];
• For Update Clause can be used within the cursor query. This means that rows returned by the query are locked exclusively when the OPEN statement is processed. Since locks are released at the end of the transaction. Commit command should not be given across fetches from an explicit cursor if FOR UPDATE is used.
• The For Update clause is the last clause in a select statement, even after the ORDER BY.
• Lock only those records which are satisfied by condition.. • NOWAIT : Returns an Oracle error if the rows are locked by another session.



The WHERE CURRENT of clause

• This allows you to apply update and deletes to the row
Currently being addressed , without the need to explicitly reference the ROWID.
• Must Include the FOR UPDATE clause in the cursor query
So that the rows are locked on Open


Example : For Update and WHERE CURRENT of clause
DECLARE
Cursor c1 is SELECT empno, ename, dept.deptno,sal
FROM dept,emp where emp.deptno=dept.deptno
AND emp.deptno=20 FOR UPDATE OF sal NOWAIT;
BEGIN
FOR emp_record in c1
LOOP
IF emp_record.sal <5000 then
Update emp set sal= emp_record.sal*1.10
Where Current of c1;
END IF;
END LOOP;
END;
/
The slide example loops through each employee in Department 20 and checks whether the salary is Less than 5000. If the salary is less than 5000, the Salary is raised by 10%. The Where Current of Clause in the Update Statement refers to the currently fetched record.



Exceptions Handling in PL/SQL

• In PL/SQL, a warning or error condition is called an exception
• When an error occurs, an exception is raised, i.e. the normal execution
stops and the control transfers to the exception handling part of the
PL/SQL block.
• Exceptions can either be of two types:
1. Predefined Exceptions
2. User Defined Exceptions
Exceptions are identifiers in PL/SQL which may be ‘raised’ during the
execution of a block to terminate its main body of actions. A block will
always terminate when an exception is raised, but you may specify an
‘Exception Handler’ to perform final actions before the block terminates.



Predefined Exceptions

• Predefined exceptions are internally defined by runtime system
• A predefined exceptions are raised implicitly
Predefined exceptions are raised whenever PL/SQL program violates an
ORACLE rule .
Every ORACLE error has a number, but exceptions must be handled by
name.



Exception Handlers

If any type of exception is raised, control is passed to the EXCEPTION
section of the block in which the exception occurred. If the exception is
not handled there, or of there is no EXCEPTION section at all, then the
Block terminates with an unhandled exception, which may effect the
enclosing environment. The same exception cannot be declared more
than once in the same PL/SQL block.
The syntax of defining exception handler is
WHEN THEN ;
Where ‘actions’ may be one or more PL/SQL or SQL statements, each
Terminated by semi-colons.



Example Pre Defined Exceptions
DECLARE
vename emp.ename%TYPE;
vjob emp.job%TYPE;
BEGIN
SELECT ename, job INTO vename, vjob FROM emp WHERE hiredate
BETWEEN '01-JAN-97' AND '31-DEC-97';
DBMS_OUTPUT.PUT_LINE (vename' 'vjob);
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line ('No Employees hired in 97');
WHEN TOO_MANY_ROWS THEN
dbms_output.put_line ('More than one Manager has joined in 97');
END;
/



Some of the predefined exceptions are:
• CURSOR_ALREADY_OPEN
• DUP_VAL_ON_INDEX
• INVALID_CURSOR
• TOO_MANY_ERROR
• INVALID_NUMBER
• NO_DATA_FOUND
• ZERO_DIVIDE


When Others Exception

• Although the Exception section in the previous example would trap the two exception, specified other types of exception are ignored
• Rather than defining a separate handler for every exception type, When others exception handler is defined which handles all errors not already handled in the block
• When others exception handler should always be the last exception handler in the block.



When Other Exception Handler

DECLARE

BEGIN

EXCEPTION
When no_data_found then
Dbms_output.put_line ('No employee hired in 97');
When too_many_rows then
Dbms_output.put_line ('More than one manager has
joined in 97');
When others then
Dbms_output.put_line ('Error during execution of the block');
END;
/


When an exception has occurred, one wants to evaluate the
associated error
Pl/sql provides two functions for this purpose

SQLCODE: It returns the error number associated with the
exception that has occurred.
SQLERRM: It returns character data. It returns the complete
error message associated with the exception including the
error number.


Example : SQLCODE And SQLERRM
DECLARE
Error_message varchar2(100);
Error_code Number;
BEGIN

EXCEPTION
When others then
Error_message:=substr(sqlerrm,1,100);
Error_code:=sqlcode;
Dbms_output.put_line (Error_message' ' Error_code);
END;
/


User Defined Exception

• User defined exceptions are declared and defined by the user.
• User defined exceptions must be raised explicitly using the Raise
Statement.
Plsql allows to define user defined exception. Unlike predefined
Exception, user defined exceptions must be declared and must be
raised explicitly by Raise statements.
Declaring an Exception
Exceptions can be declared only in the declarative part of PL/SQL
Block, subprograms, or package


Syntax of Declaring exception

Identifier EXCEPTION;
Note: Exception and variable declaration are similar. But an
Exception is an error condition, not an object. Unlike variables,
Exceptions cannot appear in assignment statements or SQL
Statements


Example:
The following pieces of code illustrate the declaration and use
of user defined exceptions.
DECLARE
------
OUT_OF_STOCK EXCEPTION;----User defined exception
QTY_ONHAND NUMBER(5);
BEGIN
------
IF QTY_ONHAND < 1 THEN
RAISE OUT_OF_STOCK; ----Raise the User Defined Exception
END IF;
-----
----
EXCEPTION
WHEN OUT_OF_STOCK THEN----Handle the User Defined Exception
/* Write the Exception Handling codes Here */
END;



How Exceptions are Raised
• Implicitly in case of Pre defined Exceptions
• Explicitly using a Raise command in
Case of User Defined Exception.
Notes:
• Exception cannot be declared twice in the same block . However,
Same exception can be declared in two different blocks
• Exceptions declared in a Block are considered local to that block
and global to all its sub block. Because a block can reference only
Local or global exceptions , enclosing blocks cannot reference
Exception declared in a sub block.

How Exceptions are Raised

• Implicitly in case of Pre defined Exceptions
• Explicitly using a Raise command in
Case of User Defined Exception.
Notes:
• Exception cannot be declared twice in the same block . However,
Same exception can be declared in two different blocks
• Exceptions declared in a Block are considered local to that block
and global to all its sub block. Because a block can reference only
Local or global exceptions , enclosing blocks cannot reference
Exception declared in a sub block.


Example A: Raising and Handling User Defined Exceptions
Declare
My_exception Exception;
-----
Begin
----------
Begin
---------------
----------------
Raise My_exception;
Exception
When My_Exception Then
---------
---------
End;
--
End;



Example B: Raising and Handling User Defined Exceptions


Declare
My_Exception1 Exception;
My_Exception2 Exception;
BEGIN
-----
BEGIN
----
IF THEN
RAISE MY_EXCEPTION2;
EXCEPTION
WHEN MY_EXCEPTION1 THEN
------
------
END;
-----
Exception
When MY_EXCEPTION2 THEN
----
---
END;
---

Fixed Assets

Setup Steps [36]

1. System Controls
2. Define Fiscal Years
3. Define Calendars
4. Account Generator
5. Define Prorate Conventions
6. Define QuickCodes
7. Define Category Key Flexfield Value Sets
8. Define Location Key Flexfield Value Sets
9. Define Asset Key Flexfield Value Set
10. Define Category Key Flexfield Segments
11. Define Location Key Flexfield Segments
12. Define Asset Key Flexfield Segments
13. Define Category Key Flexfield Segment Values
14. Define Location Key Flexfield Segment Values
15. Define Asset Key Flexfield Segment Values
16. Establish Security by Book – Create Assets Hierarchy
17. Define Book Controls using Security by Book
18. Establish Security by Book – Add site specific OU to Assets Hierarchy
19. Establish Security by Book – Security Profile
20. Establish Security by Book – Run Security List Maintenance program
21. Assign Profile Option Values
22. Define Value Set Security
23. Assign Value Set Security
24. Define Asset Categories
25. Define Location Codes
26. Define Asset Keys
27. Define Descriptive Flexfields
28. Define Depreciation Methods
29. Define Depreciation Ceilings
30. Define Investment Tax Credit Rates
31. Define Investment Tax Credit Recapture Rates
32. Define Price Indexes
33. Define Leases/Lease Payment Schedules
34. Define Warranties
35. Define Distribution Sets
36. Define Bonus Depreciation Rules




Step 1 [Oracle Required/ERP Required]

System Controls

Level: Enterprise

As Responsibility Fixed Assets Manager

Navigator: Setup->Asset System->System Controls
Purpose: Use this form to set up or review the asset system controls.


Step 2 [Oracle Required/Required]

Define Fiscal Years

Level: Enterprise
Navigator: Setup->Asset System-> Fiscal Years
Purpose: Use this form to set up or review fiscal years.


Step 3 [Oracle Required/ERP Required]

Define Calendar(s)

Level: Enterprise
Navigator: Setup->Asset System-> Calendars
Purpose: Use this form to set up or review calendars.



Step 4 [Oracle Required/ERP Required] - with defaults

Account Generator

Level: Enterprise
Navigator: Setup->Financials->Flexfields->Key->Accounts
Purpose: Use this form to customize the Account Generator. Oracle Assets uses the Account Generator to generate accounting flexfield combinations for journal entries. You must review the default process that Oracle Assets uses to see if it meets your accounting requirements. You can optionally customize the Account Generator for each set of books that you have defined. Oracle Workflow must be set up in order to use the Account Generator.


Step 5 [Oracle Required/ERP Required]

Define Prorate Conventions

Level: Enterprise
Navigator: Setup->Asset System->Prorate Conventions
Purpose: Use this form to setup or review Prorate conventions and Retirement conventions.


Step 6 [Oracle Required/ERP Required] - with Defaults
Define QuickCodes

Level: Enterprise
Navigator: Setup->Asset System-> QuickCodes
Purpose: Use this form to set up or review the list of values as needed. During asset entry and maintenence, use the List of Values to choose the QuickCode values created via this form.


Step 7 [Oracle Required/ERP Required]
Define Category Key Flexfield Value Set

Level: Enterprise

Change Responsibility to System Administrator

Navigator: Application->Validation->Set
Purpose: Use this form to define your value sets.


Step 8 [Oracle Required/ERP Required]

Define Location Key Flexfield Value Set

Level: Enterprise

Change Responsibility to System Administrator

Navigator: Application->Validation->Set
Purpose: Use this form to define your value set.


Step 9 [ Oracle Required/ERP Required]
Define Asset Key Flexfield Value Set

Level: Enterprise

Change Responsibility to System Administrator

Navigator: Application->Validation->Set
Purpose: This screen is used to define the Value Sets for each segment used in the Asset key flexfield. There are seven possible segments and Oracle recommends that no more than three segments be used. This is a Global and site specific variable.


Step 10 [Oracle Required/ERP Required]

Define Category Key Flexfield Segments

Level: Enterprise

Change Responsibility to Fixed Assets Manager

Navigator: Setup->Financials->Flexfields->Key->Segments
Purpose: Use key Flexfields to customize your application to fit unique business needs.


Step 11 [Oracle Required/ERP Required]

Define Location Key Flexfield Segments

Level: Enterprise
Navigator: Setup->Financials->Flexfields->Key->Segments
Purpose: Use this form to define the descriptive information, validation information, appearance of the pop-up window, number and order of the segments, and the segments descriptions and default values.


Step 12 [Oracle Required/ERP Required]

Define Asset Key Flexfield Segments

Level: Enterprise
Navigator: Setup->Financials->Flexfields->Key->Segments
Purpose: Use key Flexfields to customize your application to fit unique business needs.


Step 13 [Oracle Required/ERP Required]

Define Category Key Flexfield Segment Values

Level: Enterprise
Navigator: Setup->Financials->Flexfields->Key->Values
Purpose: Use this form to define valid values for the Category key flexfield segment.


Step 14 [Oracle Required/ERP Required]

Define Location Key Flexfield Segment Values

Level: Enterprise
Navigator: Setup->Financials->Flexfields->Key->Values
Purpose: Use this form to define valid values for the Category key flexfield segment.


Step 15 [Oracle Required/ERP Required]

Define Asset Key Flexfield Segment Values

Level: Enterprise
Navigator: Setup->Financials->Flexfields->Key->Values
Purpose: Use this form to define valid values for the asset key flexfield segment.


Step 16 [Oracle Mandatory/ERP Mandatory]

Establish Security by Book – Create Assets Hierarchy

Level: Enterprise
Navigator: Setup -> Security -> Organization -> Hierarchy
Purpose: Use this form to restrict access to data in a specific book by responsibility.
Restricts access to data in a specific Corporate Fixed Asset Book by responsibility.


Step 17 [Oracle Required/ERP Required]

Define Book Controls using Security by Book

Level: Operating Unit
Navigator: Setup -> Security -> Organization -> Description
Purpose: Use this form to setup the corporate, tax, and budget depreciation books. books must be set up before assets can be assigned.
You must set up your book via the Organization security form in order to establish Security by Book.


Step 18 [ Oracle Mandatory/ERP Mandatory]

Establish Security by Book – Add site specific OU to Assets Hierarchy

Level: Enterprise (For each new Operating Unit this will need to be completed)
Navigator: Setup -> Security -> Organization -> Hierarchy
Purpose: Use this form to restrict access to data in a specific book by responsibility.


Step 19 [Oracle Mandatory/ERP Manadatory]

Establish Security by Book – Security Profile

Level: Enterprise
Navigator: Setup -> Security -> Security
Purpose: Use this form to restrict access to data in a specific book by responsibility. Verify that the Business Group is created correctly


Step 20 [Oracle Mandatory/ERP Mandatory]

Establish Security by Book – Run Security List Maintenance program

Level: Operating Unit

Change Responsibility to Global HR Manager

Navigator: Processes and Reports -> Submit Processes and Reports
Purpose: Use this form to define the LOVs with the appropriate books.
Updates the LOVs with the appropriate books. This program is not a report, so there will not be any output produced. This is a critical step. It completes the organization and security profile setup.


Step 21 [ Oracle Mandatory/ERP Mandatory]

Assign Profile Option Values

Level: Enterprise

Change Responsibility to System Administrator

Navigator: Profile > System
Purpose: Assign application level and responsibility level profile options.
settings assigned at the responsibility level effectively complete the security by book setup by tying the security profile to the responsibility.


Step 22 [ Oracle Required/ERP Required]

Define Value Set Security

Level: Enterprise (For each new Operating Unit, this will need to be completed)
Navigator: Security -> Responsibility -> ValueSet -> Define
Purpose: Define security for all country defined value sets created. Restricts access to allowed values for a particular value set


Step 23 [Oracle Required/ERP Required]

Assign Value Set Security Rules to Responsibilities

Level: Enterprise (For each new Operating Unit, this will need to be completed)
Navigator: Security > Responsibility > ValueSet > Assign
Purpose: Assigns previously defined security rules for each applicable responsibility. Restricts access to allowed values for a particular value sets by assigning the predefined rules to responsibilities


Step 24 [Oracle Required/ERP Required]

Define Asset Categories

Level: Operating Unit

Change Responsibility to Fixed Assets Manager

Navigator: Setup->Asset System-> Asset Categories
Purpose: Use this form to set up or review asset category information. Category information is common for a group of assets. Oracle Assets defaults this information when you add an asset, to help you add assets quickly. If the default does not apply, you can override many of the defaults for an individual asset in the Additions or Depreciation Books forms. You set up default values for each category in each book. The default depreciation rules that you set up for a category also depend upon the date placed in service ranges you specify.


Step 25 [Oracle Optional/ERP Required]

Define Location Codes

Level: Operating Unit
Navigator: Setup->Asset System->Locations
Purpose: Use this form to set up or review the location flexfield combinations that are valid for your site. Location information is used for Responsibility and Property Tax reports.


Step 26 [Oracle Required/ERP Required]

Define Asset Keys

Level: Operating Unit
Navigator: Setup->Asset System->Locations
Purpose: At least one Asset Key (default) must be setup for each OU. Use this form to set up or review the asset key flexfield combinations that are valid for your site. Asset Key information is used for CAR Reporting.


Step 27 [ Oracle Optional/ERP Optional]

Define Descriptive Flexfields

Level: Enterprise
Navigator: Setup->Financials->Flexfields->Descriptive->Segments
Purpose: Use this form to define your descriptive flexfield.


Step 28 [Oracle Required/ERP Required] - with Defaults

Define Depreciation Methods

Level: Operating Unit
Navigator: Setup->Depreciation->Methods
Purpose: Use this form to setup or define depreciation methods. . Oracle Assets includes many standard depreciation methods, and you can define additional methods in this window, if necessary. For Corporate purposes, you must use Straight Line method.
The life of a previously set up method cannot be changed; so a new method would have to be entered.


Step 29 [Oracle Optional/ERP Optional]

Define Depreciation Ceilings

Level: Enterprise
Navigator: Setup->Depreciation->Ceiling
Purpose: Use this form to setup or review depreciation ceilings. Depreciation ceilings limit the annual depreciation expense taken on an asset.


Step 30 [Oracle Optional/ERP Optional]

Define Investment Tax Credit Rates

Level: Enterprise
Navigator: Setup->Depreciation->ITC Rates
Purpose: Use this form to setup or review ITC Rates and recapture rates.


Step 31 [Oracle Optional/ERP Optional]

Define Investment Tax Credit Recapture Rates

Level: Enterprise
Navigator: Setup->Depreciation->ITC Recapture Rates
Purpose: Use this form to setup or review ITC recapture rates.


Step 32 [Oracle Optional/ERP Optional]

Define Price Indexes

Level: Enterprise
Navigator: Setup->Asset System->Price Indexes
Purpose: Use this form to setup or review Price Indexes.


Step 33 [Oracle Optional/ERP Optional]

Define Leases/Lease Payment Schedules

Level: Enterprise
Navigator: Setup->Asset System->Lease->Lease Details
Purpose: Use the Lease Details window and the Lease Payments window to define new leases.
You cannot update or delete leases that are in use.


Step 34 [Oracle Optional/ERP Optional]

Define Warranties

Level: Enterprise
Navigator: Setup->Asset System->Warranties
Purpose: Use this form to define Warranties for tracking. Define and track descriptive information on manufacturer and vendor warranties.


Step 35 [Oracle Optional/ERP Optional]

Define Distribution Sets

Level: Enterprise
Navigator: Setup->Asset System->Distribution Sets
Purpose: Use this form to define default distribution sets. Define default distribution sets. These previously defined distribution sets can be selected via a poplist in the Assignments window to quickly assign the appropriate distributions to new assets.


Step 36 [Oracle Optional/ERP Optional]

Define Bonus Depreciation Rules

Level: Enterprise
Navigator: Setup->Depreciation->Bonus Rules
Purpose: Use this form to define bonus depreciation rules. Bonus rates let you increase the annual depreciation expense for assets using flat-rate depreciation methods.

Setting up Multi-Org

Setup Steps [21]

1. Develop the Organization Structure
2. Define Set of Books
3.1 Define Locations for the Legal Entity, Operating Unit and Inventory Organization
3.2 Define Locations for Bill-To Sites
4. Define Business Groups
5. Assign Responsibilities with Business Groups
6. Define Legal Entities
7. Define Operating Units
8. Define Inventory Organization
9. Define Auditable Units
10. Set MO: Operating Unit & GL Set of Books Name Profile Option
11. Conversion to Multi-Org
12. Define Responsibilities
13.1 Set Profile Options for Operating Unit Responsibilities
13.2 Set Profile Options for Operating Unit Responsibilities
14. Define Inventory Organization Security
15. Implement the Applications Products
16. Assign Key Flexfield Security Rule and Assign Security Rule
17. Run the Multi-Org Setup Validation Report
18. Implement Document Sequencing
19. Define Intercompany Relations
20. Set Top Reporting Level Profile Options
21. Set Up Conflicts Domains




Step 1 [Oracle Required/ ERP Required]

Develop the Organization Structure

Level: Enterprise
Navigator: None
Purpose: A successful implementation of Multiple Organization support in Oracle Applications depends primarily on defining your organizational structure in the multi-level hierarchy used by Oracle Applications.


Step 2 [Oracle Required/ ERP Required]

Define Set of Books

Level: Enterprise
Navigator: Setup > Financial> Books> Define
Purpose: Use the Define Set of Books window to enter your sets of books


Step 3.1 [Oracle Required/ ERP Required]

Define Locations for the Legal Entity, Operating Unit and Inventory Organization

Change Responsibility to Inventory

Level: Enterprise (For each new Operating Unit, this will need to be completed) (including Manufacturing)
Navigator: Setup > Organizations > Locations



Step 3.2 [Oracle Required/ ERP Required]

Define Locations for Bill-To Sites

Navigator: Setup > Organizations > Locations
Level: Enterprise (For each new Operating Unit, this will need to be completed)
Purpose: Location used for AP Financial Options.


Step 4 [Oracle Required / ERP Required]

Define Business Groups

Level: Enterprise
Navigator: Setup > Organizations > Organizations
Purpose: Oracle Applications provides default Business Group: Setup Business Group. Create additional Business Group(s) associated with the planned Organization Structure.


Step 5 [Oracle Optional / ERP Required]

Assign Responsibilities with Business Groups

Change Responsibility to System Administrator

Level: Enterprise
Navigator: Profile> System


Step 6 [Oracle Required / ERP Required]

Define Legal Entities

Change Responsibility to Inventory

Level: Enterprise (For each new Operating Unit, this will need to be completed)
Navigator: Setup > Organizations > Organizations
Purpose: Define organizational relationships by assigning classifications to each organization. You can classify an organization as any combination of legal entity, operating unit and inventory organization. Specifications can only be done in the following order: 1.Legal Entity, 2.Operation Units, 3. Inventory Organizations.


Step 7 [Oracle Required / ERP Required]

Define Operating Units

Level: Enterprise (For each new Operating Unit, this will need to be completed)
Navigator: Setup > Organization > Organizations
Purpose: Define organizational relationships by assigning classifications to each organization. You can classify an organization as any combination of legal entity, operating unit and inventory organization. Specifications can only be done in the following order: 1.Legal Entity, 2.Operation Units, 3. Inventory Organizations.


Step 8 [Oracle Required / ERP Required]

Define Inventory Organization

Level: Enterprise (For each new Operating Unit, this will need to be completed) (including Manufacturing)
Navigator: Setup > Organizations > Organizations
Purpose: Define organizational relationships by assigning classifications to each organization. You can classify an organization as any combination of legal entity, operating unit and inventory organization. Specifications can only be done in the following order: 1.Legal Entity, 2.Operation Units, 3. Inventory Organizations.


Step 9 [ Oracle Required / ERP Required]

Define Auditable Units

Level: Enterprise
Navigator: Setup > Organization > Organizations
Purpose: Define organizational relationships by assigning classifications to each organization. You can classify an organization as many classifications


Step 10 [Oracle Optional / ERP Required]

Set MO: Operating Unit & GL Set of Books Name Profile Option

Change responsibility to System Administrator

Level: Enterprise (For each new Set of Books and Operating Unit, this will need to be completed) (including Manufacturing)

Navigator: Profile> System


Step 11 [Oracle Required / ERP Required]

Conversion to Multi-Org

Level: Enterprise (For each new Operating Unit, this will need to be completed)
Navigator: Requests > Run (this is to run the 'Replicate Seed Data' job)


Step 12 [Oracle Required / ERP Required]

Define Responsibilities

Level: Enterprise

Change responsibility to System Administrator

Navigator: Security > Responsibility > Define



Step 13.1 [Oracle Optional / ERP Required]

Set Profile Options for Operating Unit Responsibilities

Level: Enterprise
Navigator: Profile> System
Purpose: The following profile options need to be set for each custom responsibility for appropriate operating unit:


Step 13.2 [Oracle Optional / ERP Required]

Set Profile Options for Operating Unit Responsibilities

Level: Enterprise
Navigator: Profile> System
Purpose: The following profile options need to be set for each custom responsibility for appropriate operating unit:


Step 14 [Oracle Optional / ERP Required]

Define Inventory Organization Security

Purpose: With inventory organization security you can restrict inventory organization access to specific responsibilities. You may want to restrict your manufacturing users to certain organizations according to your organizational hierarchy. This feature has the following characteristics.
· Until you assign an organization to a responsibility in this window, all responsibilities have access to all organizations. Once you have restricted any responsibilities to certain organizations, you must then explicitly define the organizations that all responsibilities can access.
· This feature does not restrict access once the user is in he product. Users with access to functions that cross multiple organizations (such as ATP, Inter-Organization Transfers, Item Search, Multi-Org Quantity Report, and so forth) can still specify any valid organization when running these functions.


Step 15 [Oracle Required / ERP Required]

Implement the Applications Products

Level: Operating Unit




Step 16 [Oracle Optional / ERP Required]

Assign Key Flexfield Security Rule and Assign Security Rule

Level: Enterprise
Navigator: Setup > Financials > Flexfields > Key > Security > Assign
Purpose: Assign flexfield security rules to an application responsibility.


Step 17 [Oracle Recommended / ERP Required]

Run the Multi-Org Setup Validation Report

Change responsibility to System Administrator

Navigator: Requests > Run
Level: Enterprise (For each new Operating Unit, this will need to be completed)
Purpose: Verify the current multi-org setup and identify any problems with the setup.


Step 18 [Oracle Optional/ ERP Optional]

Implement Document Sequencing

Level: Enterprise
Navigator: Application > Document > Define and Assign
Purpose: After you have implemented Multiple Organizations and run the Multi-Org Setup Validation Report, you have the option to set up document sequencing (a common requirement for European countries) for each application that supports document sequencing. Sequence assignments are controlled at the set of books level


Step 19 [Oracle Optional / ERP Optional]

Define Intercompany Relations

Navigator: Application > Document > Define and Assign


Step 20 [Oracle Optional / ERP Optional]

Set Top Reporting Level Profile Options

Level: Enterprise
Navigator: Profile> System
Purpose: You need to define your reporting levels by setting the MO: Top Reporting Level profile option. To do this, you set your top reporting level to one of the following: Set of books, Legal Entity or Operating Unit. You will be able to report on anything that is within the reporting level. The default value is Operating Unit.


Step 21 [Oracle Optional / ERP Optional]

Set Up Conflicts Domains

Level: Enterprise
Navigator: Concurrent > Conflict Domains
Purpose: A conflict domain is an abstract representation of the groupings used to partition your data. When a program is listed as incompatible with another, the two programs cannot run simultaneously in the same conflict domain. Two incompatible concurrent programs may run simultaneously if they are submitted in different conflict domains. To maximize the concurrency in a multiple organization environment, you can set up conflict domains for your operating units. All programs are assigned a conflict domain when they are submitted. The following determines the conflict domain you use when you submit a concurrent request: