be happy and make others to be happy: ERP1

ORACLE APPLICATIONS(11.5.9)

SELECT * FROM CONTENTS;

SQL CONCEPTS ...........................

_ CLASSIFICATION OF SQL COMMANDS................ 1

_ TABLES................................2

_ VIEWS...................................3

_ SEQUENCES.........................3

_ EXPLAIN PLAN.....................4

_ SQLTRACE ...........................5

_ TKPROF ................................5

PL SQL CONCEPTS ......................

_ BLOCK STRUCTURE............6

_ CURSORS.............................7

_ CURSOR ATTRIBUTES ........ 8

_ EXCEPTIONS......................... 9

_ PROCEDURES..................... 10

_ FUNCTIONS........................ 11

_ PACKAGES......................... 12

_ TRIGGERS.......................... 13

ORACLE APPLICATIONS..............

_ ERP & AIM METHODOLOGY14

_ INTRODUCTION.................. 15

_ DATABASE STRUCTURE... 16

_ FILE ARCHITECTURE .........17

_ ENTERING ORACLE APPLICATIONS 11.5.9........ 18

_ USER CREATION................ 19

_ STEPS TO REGISTER A REPORT............ 20

_ PL/SQL PROCEDURE REGISTRATION STEPS ............27

_ XML PUBLISHER REPORT REGISTRATION STEPS......34

_ TABLE REGISTRATION STEPS IN APPS 39

_ VALUSETS EXPLANATION41

_ WORKING WITH USER EXITS................45

_ PROFILES...........................49

_ DIFFERENT API'S WITH SYNTAX............ 51

_ MULTI ORG.........................53

_ FLEX FEILDS.......................55

_ INTERFACES ......................56

_ SQL LOADER...................... 58

_ CONVERSIONS...................60

_ ITEM CONVERSION CONVERSION SCRIPT ..................62

_ ITEM CATEGORIES CONVERSION SCRIPT..................70

_ ITEM ATTACHMENTS CONVERSION SCRIPT.............. 77

_ JOINS OF THE TABLES MODULE WISE.. 82

_ PURCHASE ORDER WORKFLOW........... 88

_ ORDER MANAGEMENT WORKFLOW.........

_ BOOKING ORDER....... 115

_ CREATING INVOICE .....119

_ MAKING THE RECEIPT .................... 121

_ TRANSFERING TO GL ……122

_ RETURNING THE ORDER................ 123

_ OM FLOWDIAGRAM.... 126

_ OM TABLE RELATION DIAGRAM.... 128

_ FLOW HOW TABLE EFFECTED WHILE BOOKING ORDER .......... 129

_ TRADING COMMUNITY ARCHITECTURE(TCA).................. 131

_ CREATION OF ITEM.......... 135

_ FREQUENTLY ASKED QUERIES........... 140

SQL CONCEPTS

Structured Query Language(SQL) is the standard language designed to access

relational databases. SQL is pronounced either as the word Sequel or as the letters SQL.

SQL uses simple syntax that is easy to learn and use there are five types of SQL

statements given below.

Query Statements: Allow you to retrieve the rows stored in the database tables. You

write a query using the SQL SELECT statement.

Data Manipulation Language (DML) Statements: Allows you to modify the contents

of the tables. There are three DML statements.

_ INSERT - Allows you to Insert row(s) into the Table.

_ UPDATE - Allows you to change a Row.

_ DELETE - Allows you delete the rows from the Table.

Data Definition Language (DDL): Allows you to define the Data structures, such as

tables that make up a database. There are five basic types of DDL statements.

_ CREATE – Allows you to Create Database structure.

Ex: Create Table – Used to create a Table in the Database.

Create User – Used to create the database user.

_ ALTER – Allows you to modify a database structure.

_ DROP – Allows you to remove a database structure.

_ RENAME – Allows you to change the name of the table.

_ TRUNCATE – Allows you to delete the entire contents of the table.

Transaction Control (TC) Statements: Allows you to permanently record the changes

made to rows stored in a table or undo the changes. There are three TC Statements.

_ COMMIT – Allows you to permanently record the changes made to the rows.

_ ROLLBACK – Allows you to undo the changes made to the rows.

_ SAVEPOINT – Allows you to set a SAVEPOINT to which you can rollback changes

you made.

Data Control Language (DCL): Allows you to change the permissions on the database

structures. There are two DCL statements.

_ GRANT – Allows you to give another user access to your database structures,

such as tables.

_ REVOKE – Allows you to prevent another user from accessing to your database

structures, such as tables.

Tables: Table is a database object which holds the data and contains one or more

columns associated with its datatypes

Creating a Table: we use the create table statement to create a table. The simple

syntax for the creation of table.

Syntax: CREATE Table table_name

(column_name type [CONSTRAINT constraint_def DEFAULT default_exp],

column_name type [CONSTRAINT constraint_def DEFAULT default_exp],

column_name type [CONSTRAINT constraint_def DEFAULT default_exp]…

)

[ON COMMIT {DELETE | PRESERVE} ROWS]

TABLESPACE tab_space;

Ex: SQL> Create table hariha_0016(ename varchar2(10),

Eno number unique);

Table Created.

Altering a Table: we can alter the table using the alter statement. The alter table

statement perform such tasks given below.

_ Add, modify, or drop a column.

_ Add or drop a constraint.

_ Enable or Disable a constraint.

Ex: SQL> Alter table hariha_0016

Add address varchar2(10);

Modifying a Column:

Ex: SQL> Alter table hariha_0016

Modify address varchar2(20);

Dropping a Column:

Ex: SQL> Alter table hariha_0016

Drop address;

Renaming a Table: If we want to change the name of the table then we use this

RENAME statement.

Ex: SQL> Rename table hariha_0016

Hariha_0015;

Truncating a Table: If we want to delete all the rows existing in the table then we use

the TRUNCATE Statement.

Ex: SQL> Truncate table hariha_0015;

Dropping a Table: If we want to drop the total structure along with the records existing

in the table we use this DROP statement.

Ex: SQL> Drop table hariha_0015;

Views: A view is basically a predefined query on one or more tables. Retrieving

information from the view is done in the same manner as retrieving from the table.

Creating a View: by using the create view statement we can create a view.

Syntax: Create [Or Replace]

View view_name

[(allias_name[,allias_name….])]

AS subquery

[WITH {CHECK OPTION | READ ONLY} CONSTRAINT

Constraint_name];

Ex: SQL> Create view medha_0016_view AS

Select ename, eno, address from

Hariha_0016;

View Created.

Performing an Insert using a View: we can also perform DML operations using the

views. Given below is the example for that.

Ex: SQL> Insert into medha_0016_view(

Ename, eno, address) Values (HARIHA, 0016, HYD);

1 Row Created.

Modifying a View: we can modify the view using the REPLACE. If there any view

existing with that name then it was modified with the current one.

Ex: SQL> Create or Replace

view medha_0016_view

Select a.ename, a.eno, a.address, b.city from

Hariha_0016 a, hariha_0011 b

Where a.eno = b.eno;

Dropping a View: when want to drop the view we use this statement. Only the view

will be dropped from the database the table was not effected.

Ex: SQL> Drop view hariha_0016_view;

Sequence: A sequence is a database item that generates a sequence of integers. We

create the sequence using the CREATE SEQUENCE statement.

Syntax: Create Sequence Sequence_name

[Start with Start_num]

[Increment by increment_num]

[ {MAXVALUE max_num | NOMAXVALUE} ]

[ {MINVALUE min_num | NOMINVALUE} ]

[ {CYCLE | NOCYCLE} ]

[ {ORDER | NOORDER} ];

Ex: SQL> Create Sequence medha_seq_0016

Start with 100 Increment by 1;

Sequence Created.

Using the Sequence:

Ex: SQL> Select medha_seq_0016.currval ”Currval”,

medha_seq_0016.nextval “Nextval”

From

Dual;

Output: Currval Nextval

--------- ----------

101 101

Modifying the Sequence: If we want to modify the sequence by using the ALTER

SEQUENCE we can do that.

Ex: SQL> Alter Sequence medha_seq_0016

Start with 1000 Increment by 2;

Sequence Altered.

Dropping a Sequence: If we want to drop the sequence then we use this DROP

STATEMENT.

Ex: SQL> Drop Sequence medha_seq_0016;

Sequence Dropped.

Explain Plan: Explain plan gives the execution plan of the statement. PLAN_TABLE is

necessary for explain plan.

_ If there is no PLAN_TABLE in your system then go with UTLXPLAN.SQL from the

SQL Prompt.

Syntax:

SQL> Explain plan

Set statement_id = ‘hariha_0016’

[into PLAN_TABLE ]

for

select * from scott.emp

where empno = 7369;

Plan Explained.

_ In TOAD(Tools for Oracle Application Design) write the SQL statement and press

CTRL+E then it automatically shows the explain plan. It is the simple way to get the

explain plan instead of writing the explain plan in SQL.

SQL Trace: SQL Trace gives a wide range of information & statistics that used to tune a

group of SQL operations. We do the Sequel Trace at three levels.

1. SQL

2. Reports

3. Forms

_ Trace the Sequel Statements. How much time it was taking, how many rows it was

fetching, all the information was given from SQL Trace.

Steps for generating Trace file:

_ Enable the Trace.

_ Run the DML statements.

_ Disable the Trace.

_ Get the Trace file.

_ Convert the Trace File to Readable Format.

_ The Trace file was generated with he extension .TRC. Oracle has give specified

directory for trace files. To get the path use the query below.

EX: SQL> Select value from

V$PARAMETER

Where name = ‘USER_DUMP_DEST’;

_ To get the name of the Trace file also we have to use the Query.

SQL> Select c.value || ‘\ORA’ || TO_CHAR(a.spid, ‘FM00000’) || ‘.TRC’

From

V$PROCESS a,

V$SESSION b,

V$PARAMETER c

Where

a.addr = b.paddr

and b.ausid = userenv(‘sessionid’)

and c.name = ‘USER_DUMP_DEST’;

TKPROF: Copy the trace file, which was generated earlier, and paste it in your custom

directory.

Syntax: CMD> TKPROF d:\siri_0016.trc siri_0016.txt

_ The Hariha_0016.txt was created in the same drive where the Trace file was located.

Ex: --Query to Print the Cumulative Salary of the Employee table order by DEPTNO

SQL> Select deptno, ename, sal, sum(sal) over(partition by

deptno order by deptno, ename) “CUM_SAL”

from

scott.emp;

PL/SQL CONCEPTS

Oracle added a procedural programming language known, as PL/SQL (Procedural

Language/SQL). PL/SQL is a third generation language and contains the standard

programming constructs.

Anonymous Block: An Unnamed block which will not store any where in the database is

know as Anonymous block.

Block Structure: PL/SQL programs are divided up into structures known as blocks, with

each block containing the PL/SQL and SQL statements. The syntax for the structure is

given below.

Syntax: [DECLARE

Variable declaration

Begin

Executable_statements

[Exception

Exception_handling_statements]

End;

Ex: SQL> SET SERVEROUT ON;

SQL> Declare

v_1 number;

v_2 number;

v_3 number;

Begin

v_1:=&v_1;

v_2:=&v_2;

v_3:=&v_3;

if v_1 < v_2 and v_1 < v_3 then

dbms_output.put_line('1st is small');

elsif v_2 < v_3 then

dbms_output.put_line('2nd is small');

else

dbms_output.put_line('3rd is small');

end if;

Exception

When zero_divide then

DBMS_OUTPUT.PUT_LINE(‘Division by ZERO’);

End;

O/p: It will ask for the three input values and will display the smallest number in that.

Difference between PL/SQL and SQL: In PL/SQL there is no buffer to hold the values

so we pass into variables. Here in SQL there is buffer to hold the data temporarily.

Ex: SQL> Select ename from emp where is empno = 7788;

SQL> Declare

V_name varchar2(10);

Begin

Select ename into v_name from emp where empno = 7788;

End;

Cursors: Cursor is a private SQL area provided by the oracle engine. It is mainly used to

retrieve the data from more than one column. There are two types of cursors they are

given below.

_ Implicit Cursors

_ Explicit Cursors

Implicit Cursors: Implicit cursor raises implicitly when we use INSERT, UPDATE,

DELETE and SELECT..INTO statements. Because the Implicit cursor is opened and closed

by the PL/SQL engine.

Ex: SQL> declare

v_edata emp%ROWTYPE;

begin

select * into v_edata from emp where empno=7788;

if sql%notfound then

dbms_output.put_line('Record Not Found');

else

dbms_output.put_line('Record Found');

end if;

end;

Processing Explicit Cursors: The four PL/SQL steps necessary for explicit cursor

processing are as follows.

_ Declare the cursor.

_ Open the cursor for a Query.

_ Fetch the results into PL/SQL variables.

_ Close the Cursor.

Declaration of Cursor: The declaration of the cursor is associated with the select

statement. The syntax for the cursor is given below.

Syntax: CURSOR cursor_name IS select_statement;

Ex: -- To print the Cumulative SAL of the Employee table order by empno

SQL>declare

v_sal number;

v_dno number;

l_count number;

cursor test_cursor is select * from scott.emp order by empno;

begin

v_sal:=0;

v_dno:=0;

for i in test_cursor

loop

if(i.deptno!=v_dno) then

v_sal:= 0;

end if;

v_sal:=v_sal+i.sal;

dbms_output.put_line('Ename: ' || i.ename||' Sal: '|| i.sal || '_

Cum_sal: '|| v_sal || 'Deptno: ' || i.deptno);

l_count:=test_cursor%rowcount;

end loop;

dbms_output.put_line(l_count||' Row(s) Fetched by the Cursor');

end test_cursor;

Cursor Attributes: There are four cursors available in PL/SQL that can be applied to

cursors. Those are given below.

_ %FOUND

_ %NOTFOUND

_ %ISOPEN

_ %ROWCOUNT

%FOUND: %FOUND is a Boolean attribute. It returns TRUE if the previous FETCH

returned a row and FALSE if it didn’t.

%NOTFOUND: %NOTFOUND is also a Boolean attribute. It returns FALSE if the

previous FETCH returned a row and TRUE if it didn’t. It behaves opposite to the

%FOUND.

%ISOPEN: %ISOPEN is a Boolean attribute. It returns TRUE if the associated cursor is

open other wise it will return FALSE.

%ROWCOUNT: %ROWCOUNT is a Numeric attribute. It returns number of rows

returned by the cursor so far.

Declaring, Opening Fetching and Closing the Cursor:

Ex: SQL> declare

v_eno number;

v_ename varchar2(20);

l_count number;

cursor razia_cursor is select empno,ename from scott.emp;

begin

open razia_cursor;

loop

fetch razia_cursor into v_eno, v_ename;

exit when razia_cursor%notfound;

l_count:=razia_cursor%rowcount;

dbms_output.put_line('Ename: ' || v_ename||' ENUM: '|| v_eno);

end loop;

close razia_cursor;

dbms_output.put_line(l_count||' Row(s) Fetched by the Cursor');

end;

Ex: SQL> -- Example to print the odd rows in the Table EMP

declare

n number;

l_count number;

cursor razia_cursor is select empno,ename,rownum from scott.emp;

begin

for i in razia_cursor

loop

n:=mod(i.rownum,2);

if (n>0) then

dbms_output.put_line('Empno: ' || i.empno||' NAME: '|| i.ename||'

ROWNUM: '|| i.Rownum);

end if;

l_count:=razia_cursor%rowcount;

end loop;

dbms_output.put_line(l_count||' Number of Row(s) Fetched by the Cursor');

end razia_cursor;

Exceptions: An Exception is a error handling statement. It is used when ever our

program terminates abnormally by using the exception we can execute the program

from abnormal termination to normal termination. There are mainly two types of

exceptions they are given below.

_ Predefined Exceptions.

_ User defined Exceptions.

Predefined Exceptions: Oracle has predefined several exceptions that correspond to

the most common oracle errors. Some predefined exceptions are given below.

LOGIN_DENIED

NO_DATA_FOUND

ZERO_DIVIDE

TOO_MANY_ROWS

STORAGE_ERROR

ROWTYPE_MISMATCH

INVALID_CURSOR

CURSOR_ALREADY_OPEN

Ex: SQL> declare

cursor test_cursor is select e.ename, d.deptno,

d. dname from scott.emp e, scott.dept d

where e.deptno=20 and d.dname='RESEARCH';

begin

for i in test_cursor

loop

dbms_output.put_line(i.ename || ' ' || i.deptno ||' '|| i.dname);

end loop;

exception

when no_data_found then

dbms_output.put_line('NO DATA FOUND EXCEPTION RAISED');

when others then

dbms_output.put_line(' An Error Raised ' || sqlerrm);

end test_cursor;

User Defined Exceptions: A user defined exception is an error defined by the

programmer. User defined exceptions are declared in the declarative section of the

PL/SQL block just like variables.

Syntax: DECLARE

E_myexception EXCEPTION;

Ex: SQL> create or replace procedure razia_proc(v_sal in number) as

v_sl number;

razia_excep exception;

begin

if(v_sal >= 5000)

then

raise razia_excep;

else

select sal into v_sl from emp order by deptno;

end if;

exception

when razia_excep then

dbms_output.put_line( 'YOU HAVE PERFORMED AN ILLEGAL OPERATION');

end razia_proc;

Procedures: Procedures are also known as subprograms. Given below is the syntax for

the creation of the procedure.

Syntax: CREATE [OR REPLACE] PROCEDURE procedure_name

[(argument [{IN | OUT | IN OUT}] type,

…

[(argument [{IN | OUT | IN OUT}] type) ] {IS | AS}

BEGIN

procedure_body

END procedure_name;

Ex: SQL> create or replace procedure razia_proc as

v_sal number;

cursor razia_cursor is select * from emp order by deptno;

begin

v_sal := &v_s;

for i in razia_cursor

loop

if(i.sal > v_sal) then

dbms_output.put_line( 'Employee Name: ' || i.ename);

end if;

end loop;

exception

when others then

dbms_output.put_line( 'YOU HAVE PERFORMED AN ILLEGAL OPERATION');

dbms_output.put_line( 'THE PROGRAM MAY TERMINATE NOW');

end razia_proc;

Execution: exec razia_proc;

Passing IN Parameter to the Procedures:

Ex: SQL> create or replace procedure kanthi_proc(p_inpar in number) as

v_name varchar2(30);

begin

select ename into v_name from emp where empno=p_inpar;

dbms_output.put_line( 'Employee Name: ' || v_name);

exception

when others then

dbms_output.put_line( 'YOU HAVE PERFORMED ILLEGAL OPERATION');

end kanthi_proc;

Using IN and OUT parameters to Procedures:

Ex: SQL> create or replace procedure

shabbir_proc(p_outpar out varchar2,p_inpar in number) as

begin

select ename into p_outpar from emp where empno=p_inpar;

end;

--To Catch the Output variable out side the procedure in the SQL

declare

v_name varchar2(20);

v_num number;

begin

mypro(v_name,&v_num);

dbms_output.put_line( 'Employee Name: ' || v_name);

end shabbir_proc;

Functions: Function is similar to a procedure except that a function must return a value

to the statement from which it is called. The syntax for creating a function is given

below.

Syntax: SQL> CREATE [OR REPLACE] FUNCTION function_name

[(parameter_name[IN | OUT | IN OUT] type [,….])]

RETURN type;

{IS | AS}

BEGIN

Function_body

END function_name;

Ex: create or replace function haritha_func(p_empno number)

return varchar2 is

v_ename varchar2(20);

v_sal number;

begin

select ename,sal into v_ename, v_sal from emp where empno=p_empno;

if v_sal>=6000 then

return 'TRUE';

else

return 'FALSE';

end if;

end haritha_func;

Execution: SQL> select haritha_func(7788) from dual;

Ex: SQL> create or replace function haritha_fun(p_radius number)

return number as

v_pi number := 3.141;

v_area number;

begin

v_area := v_pi * POWER(p_radius,2);

return v_area;

end haritha_fun;

Execution: SQL> select haritha_fun(7) from dual;

Dropping Function and Procedure: When we want to drop the function or procedure

then we use this DROP statement the syntax for the DROP is given below.

Syntax for Dropping Function:

DROP FUNCTION function_name;

Syntax for Dropping Procedure:

DROP PROCEDURE procedure_name;

Packages: Package is declarative part of the functions and procedures which are stored

in that package. There are two blocks in defining a package.

_ Package Specification

_ Package body

Package Specification: The package specification contains information about the

contents of the package. It does not contain code for any subprograms.

Syntax: CREATE [OR REPLACE] PACKAGE package_name {IS | AS}

Type_definition |

Procedure_specification |

Function_specification |

Variable_declaration |

Exception_declaration |

Cursor_declaration |

Pragma_declaration

END [package_name];

Package Body: The package body is separate data dictionary object from the package

header. It cannot be successfully compiled unless the package header is already been

successfully compiled.

Syntax: CREATE [OR REPLACE] PACKAGE BODY package_name {IS | AS}

….

BEGIN

Initialization_code;

END package_name;

Ex: SQL> create or replace package vamsi_pack as

procedure razia_proc(mynum in number);

end vamsi_pack;

-- Declaration of Package Body and passing value to the procedure

create or replace package body vamsi_pack as

procedure razia_proc(mynum in number) as

cursor mycursor is select ename,empno,sal from emp where empno=mynum;

begin

dbms_output.put_line(' NAME ' || ' NUMBER ' || ' SALARY ');

for i in mycursor

loop

dbms_output.put_line(' '||i.ename||' '|| i.empno ||' '|| i.sal);

end loop;

exception

when others then

dbms_output.put_line('YOU HAVE DONE AN ILLEGAL OPERATION ');

end myproc;

end vamsi_pack;

Execution: SQL> exec vamsi_pack.razia_proc(7788);

Triggers: A Trigger is a procedure that will fire automatically by the database. When a

specified DML statement is run against the specified table. Triggers are useful for doing

things like advanced auditing of changes made to a column values in a table. The syntax

for creating the trigger is given below.

Syntax: CREATE [OR REPLACE] TRIGGER trigger_name

{BEFORE | AFTER | INSTEAD OF} trigger_event

ON table_name

[FOR EACH ROW [WHEN trigger_condition]]

BEGIN

Trigger_body;

END;

Ex: This example is when we insert into a table t1 then the same data has to be inserted

into the table t2. For that we require two tables of one same column.

SQL> CREATE TABLE t1(

Eno number);

SQL> CREATE TABLE t2(

Eno number,

Ins_date date);

SQL> CREATE OR REPLACE TRIGGER razia_trigger

BEFORE INSERT ON TABLE t1

FOR EACH ROW

BEGIN

INSERT INTO TABLE t2

VALUES(:new.eno,sysdate);

END;

Note: For every insert operation on t1 it will insert the eno col values to t2 along with the current

sysdate in the other column.

Disabling and Enabling Trigger: We can stop the trigger from firing by disabling the

trigger by using the ALTER TRIGGER statement. The syntax for enabling the trigger and

disabling the trigger is given below.

Syntax: ALTER TRIGGER trigger_name DISABLE;

ALTER TRIGGER trigger_name ENABLE;

Dropping a Trigger: If we want to drop the trigger from the database then we have to

use DROP TRIGGER statement. The syntax for dropping the trigger is given below.

Syntax: DROP TRIGGER trigger_name;

ERP: Enterprise Resource Planning

Here Resource means 5 m’s

1. Money

2. Man power

3. Machinery

4. Material and

5. Management

ERP Purpose: Planning for proper utilization of resources for a business organization .

ERP as a Product : all objects(i.e. forms, reports )and all other programs are

developed and throwly tested and those will be provided with these ERP.

Methodology : the process used to implement ERP Product for a business Organization.

AIM (APPLICATION IMPLEMENTATION METHODOLOGY):

Methodology for Oracle Apps is AIM(Applications Implementation Methodology)

Phases in Aim Methodology:

1. Definition Phase

2. Operational Analysis Phase

3. Solution Design Phase

4. Build Phase

5. Transition Phase

6. Production Phase

1. Definition Phase: Here the functional consultants prepare the sample structure

(Blue Print) for the real business by gathering the information.

2. Operational Analysis phase: In this the functional consultant prepare BR100

(Business Requirement).

3. Solution Design Phase: here the functional consultant will prepare MD50 by using

BR100. MD50 is the functional document.

4. Build Phase: here the technical consultant converts MD50 to MD70. MD70 is the

technical document. Then the technical consultant prepare the final object and go

with testing, i.e. Unit Testing.

5. Transition Phase: here SIT(System Integration Testing) and UAT(User

Acceptance Testing) is done. Functional consultant does SIT. After this the

functional consultant will train the user in oracle apps product and user does UAT.

If it is satisfied by the user, it is sign off and product will be given to the user.

6. Production phase: loading the project in the client place. Where the server of

oracle apps in the client place.

Introduction: The Oracle Applications is an ERP, which was developed by the

Oracle Corp and was used by the several companies to utilize several resources

contained with them.

Business Flow of Oracle Applications:

Oracle Applications is designed for the Financial and Manufacturing clients. The

categories in the modules are given below.

Financial Modules: Account Payables, Account Receivables, General Ledger and Fixed

Assets.

Manufacturing Modules: Purchasing, Inventory, Bill Of Materials, Work in Progress and

Order Management.

In HRMS we maintain all the details of the employee as well as the organization details.

Module: It is a collection of forms and reports which are related to particular business

process.

Purchase order module has the forms and reports which are related to the purchasing

business process. It contains nearly 50,000 tables they are accessed by only purchase

order module.

Oracle Applications Database Structure:

Schema: Schema is a place in database which contains the database objects like tables,

indexes, sequences, views and synonyms.

In oracle applications database we have individual schemas for each module

application. These schemas contain the database objects of only that particular module.

When we connect any database schema we can access only that schema database

objects. We can’t access other database objects. For data integrity purpose the oracle

has deigned one more schema called Apps schema which contains only synonyms and

this schema have the rights to access any other module database objects.

Note: Apps Schema will not contain the tables it contain only synonyms.

Projects in Oracle Applications:

_ Implementation Projects: Implementation means we will develop the forms,

reports from the scratch. In this project we will be work in the areas of the forms,

reports, interface, customizations and interfaces.

_ Customization Projects: In this we customize some standard reports.

_ Migration / Up gradation Projects: This will be moving from the Old version to the

New version.

_ Support / Maintenance Projects: Solving the issues which are raised by the end

user while entering the data.

_ Post Implementation Projects: This will be done at free of cost. It’s a real time

testing.

Types of Docs in Oracle Apps:

_ MD 050 -- Module design by the functional consultant.

_ MD 070 -- Technical Document designed by the technical consultant.

_ MD 020 -- Technical document designed by the functional document.

_ MD 0120 -- Migration and user training document by the technical consultant.

_ CV 040 -- Conversion of the functional document by the functional consultant.

_ CV 060 -- Conversion of the technical document by the technical consultant.

Note: Conversion means moving the data from the legacy system to the apps system.

Oracle Apps File Architecture:

We will have Linux/Unix server for oracle apps we will be connecting to the server

by using the FTP/TOAD/FILEZILLA/WINSEP3

File Architecture:

The Server first top will be application top “Appl-top” we can change the name

according to the client wish. Under that we have the product top. Each module has go it

individual top.

Note: We will move the (download/upload) forms and reports from the local machine to the server

always in binary mode. We save any information only on the server not in the local machine.

Entering the Oracle Applications: First it will prompt for the username and password

and then click on the connect button then it will give us responsibilities form. Select to

which responsibility you want to login then you will enter to the oracle applications.

We get the screen as given below when we enter into the system administrator

responsibility.

User Creation: Connect to the oracle applications 11i as we shown above and enter into

the system administrator responsibility. The navigation to create the user is

Security _ User _ Define. The window is given below. Enter the username, password

and assign responsibilities what ever you want and save the user will be created.

Note: when ever we login next time we can enter the user name and password that what we

created now.

Steps to Register a Report:

1. Develop the report in 6i according to the client requirements.

2. Transfer the object from the local system to the server and placing that in the

appropriate directory.

3. Creating the executable and set the execution method as oracle reports as we are

registering the report. Set the executable file name as the object name which we

modified or developed.

4. Create the concurrent program and set the executable to the concurrent program.

If your program has any parameters then go with the parameters button and

submit the parameter tokens.

5. Set the concurrent program to the request group and the request group to the

responsibilities.

6. User having that responsibility will only can run that request or submit the

request of the report.

Note: Every form contains 3 types of fields those are

1. Yellow Colored fields --- Mandatory. With out entering these mandatory fields we

can’t save the form.

2. Green Colored fields --- Read only.

3. White Colored fields --- Optional.

4. Blue colored indicates the form in the query mode. We enter into the query mode by

pressing F11 and enter data which was already saved to retrieve the record press

CTRL+F11.

Creating the Concurrent Program Executable: Before we start the registration

process we have to create a report according to the client requirements and place the

report in the appropriate directory (Custom top). The navigation for this form is

Concurrent _ Program _ Executable. The given below is the executable window.

Window help:

Executable: Set the executable name as we like here we entered the name as per the

program.

Short Name: Set the short name of the executable as related to the executable because

we have to remember them.

Application: Select the Appropriate Application from the list of applications here we

selected the Oracle Receivables because we saved our report (valueset.RDF) in the AR

(Receivables) folder only.

Description: This field is not a mandatory field if we want to describe the concurrent

program executable we use this field.

Execution Method: There are eleven execution methods we can choose what ever we

want as per the requirements. Those are stated below.

Host: The execution file is a host script. When ever we have an UNIX scripting

programs then we use this execution method.

Immediate: The execution file is a program written to run as a subroutine of the

concurrent manager. We recommend against defining new immediate concurrent

programs, and suggest you use either a PL/SQL Stored Procedure or a Spawned C

Program instead.

Java Stored Procedure: The execution file is a Java stored procedure.

Java Concurrent Program: The execution file is a program written in Java.

Multi Language Function: The execution file is a function (MLS function) that supports

running concurrent programs in multiple languages.

Oracle Reports: The execution file is an Oracle Reports file. To register a report which

we done in report6i we use this method.

PL/SQL Stored Procedure: The execution file is a PL/SQL stored procedure.

Spawned: The execution file is a C or Pro*C program.

SQL * Loader: The execution file is a SQL script.

SQL * Plus: The execution file is a SQL*Plus script.

Note:

1. According to the requirement of the user and the type of the report we created

the execution method will depend.

Execution File Name: We have to set the executable filename as our report name. Here

we create the report name as valueset so; we set the name as valueset.

Subroutine Name: Only immediate programs or spawned programs using the Unified C

API use the subroutine field.

Staging function Parameters (Button): The Stage Function Parameters button opens a

window that allows you to enter parameters for the Request Set Stage Function. This

button is only enabled when you select Request Set Stage Function as your Execution

Method.

After entering all the fields save the form and go to the Concurrent Programs Form. The

Navigation for this is Concurrent _ Program _ Define.

Window Help:

Program: Enter the Program name as we like it is not standard we gave here as

cs_value_prg as per our program.

Enabled (Check Box): Disabled programs do not show up in users' lists, and do not

appear in any concurrent manager queues. You cannot delete a concurrent program

because its information helps to provide an audit trail.

Short Name: The short name is also like the short name in the executable form. We

have to give the name as per the program name.

Application Name: Select the application name as Oracle Receivables. We have to select

the application as what we select in the Executable form.

Description: This field is not a mandatory field if we want to describe the concurrent

program we use this field.

Executable:

Name: Set the executable name as the short name of the executable which we give in

the previous Executable form.

Method: when we enter the executable name there in the name field it was automatically

set which we saved in the executable form.

Priority: you can assign this program it own priority. The concurrent managers process

requests for this program at the priority you assign here.

Request:

Type: If you want to associate your program with a predefined request type, enter the

name of the request type here.

Incrementor: For use by Oracle Applications internal developers only. The Incrementor

function is shown here.

MLS Function: The Multilingual Concurrent Request feature allows a user to submit a

request once to be run multiple times, each time in a different language. If this program

utilizes this feature the MLS function determines which installed languages are needed

for the request

Use in SRS (Check Box): Check this box to indicate that users can submit a request to

run this program from a Standard Request Submission window.

Run Alone (Check Box): If you check the Use in SRS box, you can also check this box to

allow a user to enter disabled or outdated values as parameter values.

Enable Trace (Check Box): Turns on SQL tracing when program runs.

Allow Disabled Values (Check Box): If you check the Use in SRS box, you can also check

this box to allow a user to enter disabled or outdated values as parameter values.

Restart on system failure Run Alone (Check Box): Use this option to indicate that this

concurrent program should automatically be restarted when the concurrent manager is

restored after a system failure.

NLS Complaint (Check Box): This box is checked if the program allows for a user to

submit a request of this program that will reflect a language and territory that are

different from the language and territory that the users are operating in.

Output:

Format: There are several formats as per the requirements in the report we use this

format here we use the TEXT format.

Save (Check Box): Indicate whether to automatically save the output from this program

to an operating system file when it is run. This value becomes the default for all requests

submitted for this program.

Print (Check Box): If you enter No, your concurrent program's output is never sent to

the printer.

Columns/Rows: Enter the minimum column and row length for this program's report

output. Oracle Applications uses this information to determine which print styles can

accommodate your report.

Style: There are several styles which we can use A4, Landscape, BACS and other styles.

Style Required (Check Box): If your program requires a specific print style (for example,

a check writing report), use this check box to enforce that print style.

Printer: If you want to restrict your program's output to a single printer, enter the name

of the printer to which you want to send your output.

Buttons:

Copy to (Button): Create another concurrent program using the same executable,

request and report information as the current program. You can optionally copy the

incompatibility and parameter details information as well.

Session Control (Button): Use this window to specify options for the database session of

the concurrent program when it is executed.

Incompatibilities (Button): Identify programs that should not run simultaneously with

your concurrent program because they might interfere with its execution. You can

specify your program as being incompatible with itself.

Parameters (Button): If there are any parameters for our report then we go with the

parameters button. We get the window called parameters when we go with the button.

Note: The window help of the Parameter window will be given in the next steps.

Setting the Concurrent Program to the Request Group: To set the concurrent

program to the request group we have to go to the responsibilities screen. The

navigation is Security _ Responsibility _ Define. Then we get the window as shown

below.

Note: Here if we want we can create new responsibility or we can use the existing one. Here we

use the existing one retrieved the existing responsibility name using query mode (F11) as we

discussed earlier.

Now copy the request group name and go to the navigation

Security _ Responsibility _ Request. Then Request Groups window will be opened

as shown below. By entering the query mode we have to paste the Request Group name

at the Group field and press CTRL+F11. This form is case sensitive. After that click on

new button and enter the concurrent program name at the name column and go with

save button.

Submitting the Request: Now switch to the appropriate responsibility then only we

can run the request. Here as per our example we have to enter into the

Receivable Vision Operations. After enter into the responsibility go with the navigation.

Menu _ View _ Requests then we get the window find request go with

Submit a New Request button. It will prompt for the Single Request or Request Set. As

our program is the single we go with the Single option. After that we get another

window.

Enter the concurrent program name at the name field and go with the submit button.

Then we get another window as shown below.

To view the outputs of your program then go with the output button. To view any error

messages you go with the View log button.

Process to Register a PL/SQL Stored Procedure: This is the PL/SQL Stored

Procedure using the Table Valuesets.

Creating the Procedure:

EX:

SQL> create or replace procedure chaitu_proc(errbuf out varchar2,

retcode out varchar2, p_dno in number, p_dname in varchar2)

cursor test_cursor is select e.ename, d.deptno, d.dname from scott.emp e,

scott.dept d where e.deptno=p_dno and d.dname=p_dname;

begin

for i in test_cursor

loop

dbms_output.put_line(i.ename || ' ' || i.deptno ||' '|| i.dname);

end loop;

end chaitu_proc;

Procedure Created.

Note: Here we have to use fnd_file.put_line to view the output in Oracle apps, because

dbms_output.put_line will not work in Oracle Apps

Here in the Procedure there are two mandatory out parameters those are errbuf and retcode.

Errbuf: Used to write the error message to log or request file.

Retcode: if the RETCODE is set to 0 - Then concurrent program will Completed NORMAL.

1 - Then concurrent program will Completed WARNINGS.

2 - Then concurrent program will Completed ERROR.

Creating the Concurrent Program Executable: Before we start the registration

process we have to create a package or Procedure according to the client requirements.

The navigation for this form is Concurrent _ Program _ Executable. The given below

is the Navigator window.

When we go with the above navigation the Executable form will be open then we have

to enter the mandatory fields.

Executable: Set the executable name as we like here we entered the name as per the

program.

Short Name: Set the short name of the executable as related to the executable because

we have to remember them.

Application: Select the application name from the list of applications here we selected

the Oracle Receivables as per our requirements.

Description: This field is not a mandatory field if we want to describe the program we

use this field.

Execution Method: We have to select the PL/SQL Stored Procedure as execution method

to register the myproc1 procedure.

Execution File Name: We have to set the executable filename as procedure name. Here

we our procedure name is mrproc1 so; we set the name as myproc1.

After entering all the fields save the form and go to the Concurrent Programs Form.

The Navigation for this is Concurrent _ Program _ Define.

Enter all the mandatory fields and save the form.

Program: Enter the Program name as we like it is not standard we gave here as

cs_value_prg as per our program.

Short Name: The short name is also like the short name in the executable form. We

have to give the name as per the program name.

Application Name: Select the application name as Oracle Receivables. We have to select

the application as what we select in the Executable form.

Executable Name: Set the executable name as the short name of the executable which

we give in the previous Executable form.

Format: There are several formats as per the requirements in the report we use this

format here we use the TEXT format.

Style: There are several styles which we can use A4, Landscape, BACS and other styles.

Here we are having the two parameters go with Parameters button in the Screen. The

Concurrent Program Parameters window will show below.

Note: There is no token required where we are working with the PL/SQL STORED PROCEDURE and

observe that the token field in the above window is grayed it won’t allow any values into that field.

Window Help:

Seq: Choose the sequence numbers that specify the order in which your program

receives parameter values from the concurrent manager.

Parameter: We enter the parameter name that we are passing. Same was passed to the

prompt when we press tab.

Description: This is the optional field to describe the input parameter.

Enabled (Check Box): Disabled parameters do not display at request submission time

and are not passed to your execution file.

Validation:

Valueset: Enter the name of the value set you want your parameter to use for validation.

You can only select from independent, table, and non-validated value sets. As per our

example we defined two valuesets for two parameters and set the two valuesets

respectively.

Default Type: If you want to set a default value for this parameter, identify the type of

value you need.

Required (Check Box): If the program executable file requires an argument, you should

require it for your concurrent program.

Enable Security (Check Box): If the value set for this parameter does not allow security

rules, then this field is display only. Otherwise you can elect to apply any security rules

defined for this value set to affect your parameter list.

Display:

Display Size: Enter the field length in characters for this parameter. The user sees and

fills in the field in the Parameters window of the Submit Requests window.

Concatenated Description Size: Enter the display length in characters for the parameter

value description. The user sees the parameter value in the Parameter Description field

of the Submit Requests and View Requests forms. The Parameter Description field

concatenates all the parameter values for the concurrent program.

Note: We recommend that you set the Concatenated Description Size for each of your

parameters so that the total Concatenated Description Size for your program is 80 or

less, since most video screens are 80 characters wide.

Prompt: Enter the message that will prompt for the input at the submit request form.

Token: For a parameter in an Oracle Reports program, the keyword or parameter

appears here. The value is case insensitive. For other types of programs, you can skip

this field.

After modifying the parameter form, save the program. Now go the

Request Group form and the set the concurrent program to the appropriate request

group. The navigation for this is Security _ Responsibility _ Request. Then Request

Groups form will open as shown below. Enter into Query mode by pressing F11 then

enter the Request Group Name and press CTRL+F11. This form is case sensitive. After

that click on new button and enter the concurrent program name at the name column

and go with save button.

Ex:-- Some Request Group Names

For Receivables : Receivables All

For Inventory : All Inclusive GUI

For Order Management : OM Concurrent Programs

For Purchase Order : All Reports

If we don’t know the Request group name then we have to go for the navigation

Security _ Responsibility _ Define we already explained in the above example.

Switch to the appropriate responsibility and run the request. When we enter the

name of the program in the run request window then parameter window will be open.

Then you will get the parameter screen enter the required parameters and go with ok

button.

After entering both the parameters then press ok and then submit the request.

After that submit the request to and go with find button to view the status of the

request. After it completed normal we can view the output from the output button. If the

Status of the program completed error then go with View Log button.

Out Put:

XML Publisher Registration Steps:

Purpose: For CFD(Customer Facing Document)

1. Develop a report according to the client requirements and register the concurrent

program.

Ex: The Concurrent program name is CS_XML_REP

Note: The only difference is we have to set the format type as XML in the

concurrent program window while registering

2. Create a Template with specified formats like rtf, pdf… using the appropriate

tools.

3. Then go with the XML Publisher Administrator Responsibility and click on the Data

definition.

4. Then you will get the screen now go with the Create Data definition button in the

screen.

5. The Create Data Definition screen will appear enter all the fields and click on

apply button.

Window Help

Name: This name is your data definition name, which you are going to create now.

Application: We have to select the appropriate application, which our concurrent

program has been registered

Code: The code must match the concurrent program short name, which we registered

earlier.

Start Date: It specifies the start date of your application.

Description: To describe your Data Definition enter the data.

6. Now go with Template tab in the same screen.

7. Click on the Create template button then the following screen will appear.

Window Help:

Name: This is the Template Name, which we are going to create.

Application: We have to select the appropriate application, which our concurrent

program has been registered

Type: Select the format type of your template, which you created earlier.

Code: Enter the code for the template that is short name.

Data Definition: Enter the data definition name, which was created earlier.

Start Date: It specifies the start date of your application.

Description: To describe your Template enter the data.

File: Click on Browse and select the template file, which you have created.

Language: Choose a specific language according to the Customer Specifications

Territory: Territory specifies the language belongs to which nation.

Then go with Apply button.

8. Now Switch to the appropriate responsibility and run the CS_XML_REP concurrent

program, which we created earlier.

9. Then copy the Request_id in the Requests window.

10. Switch the responsibility to the XML Publisher Responsibility and run the XML

Publisher Report. You will get the following Input Screen.

Output: After completion of the request go with view output window Button.

Report Code: -- This code will appear on the After parameter form in our report

function AfterReport return boolean is

l_request number;

l_appl NUMBER;

begin

SELECT FCP.APPLICATION_ID

INTO l_appl

FROM FND_CONCURRENT_PROGRAMS FCP,FND_CONCURRENT_REQUESTS R

WHERE FCP.CONCURRENT_PROGRAM_ID = R.CONCURRENT_PROGRAM_ID

AND R.REQUEST_ID = :P_CONC_REQUEST_ID;

l_request := fnd_request.submit_request( 'XDO', 'XDOREPPB', null, null, FALSE,

:P_CONC_REQUEST_ID,'xmlreporttemp',l_appl,'US','N',NULL,NULL,NULL,NULL,

NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,

NULL,NULL,NULL,NULL,NULL,NULL,NULL);

srw.message(100, ' Request submitted ' || l_request);

SRW.USER_EXIT('FND SRWEXIT');

return (TRUE);

EXCEPTION

WHEN OTHERS THEN

RETURN NULL;

end;

Table Registration in APPS:

1. Create a table .

Ex: Sql> Crete table wip_item(ITEM VARCHAR2(15),

ITEMDESCRIPTION VARCHAR2(100),

ITEMCOST NUMBER(8),

CATEGORYK VARCHAR2(100),

CREATION_DATE DATE,

CREATED_BY NUMBER(7),

LAST_UPDATE_DATE DATE,

LAST_UPDATED_BY NUMBER(7),

ATTRIBUTE_CAT VARCHAR2(100),

ATTRIBUTE1 VARCHAR2(100),

ATTRIBUTE2 VARCHAR2(100),

ATTRIBUTE3 VARCHAR2(100),

ATTRIBUTE4 VARCHAR2(100),

ATTRIBUTE5 VARCHAR2(100));

2. If you are in different user for Ex: scott/tiger then you have to grant permission to

APPS.

Ex: Grant all on wip_item to apps;

3. Connect apps/apps@PROD.

4. Create synonym for the table which we are going to register in APPS.

Ex: Create public synonym wip_item for wip.wip_item.

5. Registering the table by using AD_DD Package

Syntax: Exec AD_DD.REGISTER_TABLE

(p_appl_short_name,

p_tab_name, --table name

p_tab_type,

p_next_extent, -- Enter the initial and next extent sizes in kilobytes for your

table. You must enter values greater than 0.

p_pct_free,--free storage.

p_pct_used);-- used storage.

EXEC AD_DD.REGISTER_TABLE('WIP','WIP_ITEM','T',8,10,90);

Registering the Columns:

Syntax: EXEC AD_DD.REGISTER_COLUMN p_appl_short_name,

p_tab_name,

p_col_name,

p_col_seq,

p_col_type,

p_col_width,

p_nullable,

p_translate,

p_precision default null,

p_scale default null);

Ex:

EXEC AD_DD.REGISTER_COLUMN('WIP','WIP_ITEM','ITEM',1,'VARCHAR2',15,'N','Y');

EXEC AD_DD.REGISTER_COLUMN('WIP','WIP_ITEM','ITEMDESCRIPTION',2,'VARCHAR2',100,'N','Y');

EXEC AD_DD.REGISTER_COLUMN('WIP','WIP_ITEM','ITEMCOST',3,'NUMBER',8,'N','Y');

EXEC AD_DD.REGISTER_COLUMN('WIP','WIP_ITEM','CATEGORYK',4,'VARCHAR2',100,'N','Y');

EXEC AD_DD.REGISTER_COLUMN('WIP','WIP_ITEM','CREATION_DATE',5,'DATE',11,'N','Y');

EXEC AD_DD.REGISTER_COLUMN('WIP','WIP_ITEM','CREATED_BY',6,'NUMBER',7,'N','Y');

EXEC AD_DD.REGISTER_COLUMN('WIP','WIP_ITEM','LAST_UPDATE_DATE',7,'DATE',11,'N','Y');

EXEC AD_DD.REGISTER_COLUMN('WIP','WIP_ITEM','LAST_UPDATED_BY',8,'NUMBER',7,'N','Y');

EXEC AD_DD.REGISTER_COLUMN('WIP','WIP_ITEM','ATTRIBUTE_CAT',9,'VARCHAR2',100,'N','Y');

EXEC AD_DD.REGISTER_COLUMN('WIP','WIP_ITEM','ATTRIBUTE1',10,'VARCHAR2',100,'N','Y');

EXEC AD_DD.REGISTER_COLUMN('WIP','WIP_ITEM','ATTRIBUTE2',11,'VARCHAR2',100,'N','Y');

EXEC AD_DD.REGISTER_COLUMN('WIP','WIP_ITEM','ATTRIBUTE3',12,'VARCHAR2',100,'N','Y');

EXEC AD_DD.REGISTER_COLUMN('WIP','WIP_ITEM','ATTRIBUTE4',13,'VARCHAR2',100,'N','Y');

EXEC AD_DD.REGISTER_COLUMN('WIP','WIP_ITEM','ATTRIBUTE5',14,'VARCHAR2',100,'N','Y');

6. Check in APPS go to Application Developer Responsibility

Application _ Database _ Table

Then we will get Tables Screen as Shown below and press F11 then enter the

table_name and press CTRL+F11 then we will get the data as shown below.

Valuesets: Valueset is nothing but the list of values with validation. We will be using the

valuesets whenever the concurrent programs have parameters and while defining the

flex fields we attach this flex fields. The given below are the types of valuesets.

None

Independent

Dependent

Table

Translatable Dependent

Translatable Independent

Special and

Pair.

None: It is used whenever we want to apply some format condition like max.length,

numbers, and capital letters so on. There wont be any list of values in none valueset.

Independent: This valueset is used whenever we want to show some hard coded values

to the end user that time we will create. Navigation to get the Valueset screen in

Application _ Validation _ Set

After that go with the Navigation Application _ Validation _ Values then we will get

the screen given below. Enter the valueset name, which we created earlier.

Dependent: It is a list of values, which are depending on the Independent valueset. To

create dependent valueset go with same process we mentioned above and set the

validation type as Dependent and click on the Edit Information button then we will get

the Dependent valueset information screen enter the mandatory fields like independent

valueset name which we created earlier.

Table: It will be used whenever we want to show the list of values from the table

column name. When we select this option then the Edit Information in this window will

become active. When we go with that Edit Information button we get the Validation

information window. That is shown below.

To define the valueset the Navigation is Application _ Validation _ Set. The

Valueset form is shown below.

Window Help:

Value Set Name: Define your own value set name.

Format Type: Select the appropriate datatype that what we are passing as parameter

through this valueset. This field is mandatory.

Maximum Size: Enter the maximum size of the parameter that what we are passing to

the report. This field is mandatory.

Validation Type: Select validation type as Table and go with the edit information button.

then the Validation table information window will be open. Enter the mandatory fields as

required.

Now go with the edit information button then we get the Validation Table Information

window will appear as shown below.

Table Name: Here we can enter the table name or we can write a query. Query must be

written in braces only.

Ex: (select distinct (empno) from scott.emp)

Value: Enter the column name which we want to display in the parameter window.

Meaning: Meaning is to provide the additional information along with the list of values.

ID: To column is used to pass a value to the parameter. The scenario of using this id will

be if we want to pass a value to the parameter, which is associated with, the list of

values select by the user.

Where/Order By: To incorporate any filters in the list of values we can do that in where

clause and order by is used to sort the list of values in ascending or descending order.

Additional Columns: when we want to display any other Additional columns in the

output form we use this. We use these two as per the requirements.

Syntax: Column name “Alias Name” (Width)

Note: When we are working with table valueset only the edit information button is active. For

other valuesets it is not active.

Using $FLEX$: $FLEX$ is used to hold the data which was selected by the previous

valueset for that we have to define another table valueset with different name as per the

above process. In the new valuset we used the $FLEX$ as given below screen.

Note:

Whenever you assign the valueset to any concurrent program then you are not allowed to

modify the valueset. We have to remove from the valueset from the concurrent program

to modify the valueset, which was already created.

For more information you go to the example given below with detailed explanation of table

valueset.

When ever we select the validation type as table then the edit information button in the

valuesets window will be enabled. It will be disabled for the other valuesets only for the

table valueset.

Translatable Dependent & Translatable Independent: These two valuesets are

used whenever you are working with multi-lingual implementation.

Special: Special valueset is used for display flexfields data as parameter values.

Pair: This valueset is used to display parameters in pairs.

EX: Date from & Date To…etc. (To specify a range in pair we use such type of valuesets).

Working with User Exits in Reports 6i: User Exits are 3GL programs used to transfer

the control from the report builder to Oracle Applications or any and it will perform some

actions and return to the report builder. There are five types of user exits those are

given below.

_ FND SRWINIT

_ FND SRWEXIT

_ FND FORMAT_CURRENCY

_ FND FLEXSQL

_ FND FLEXIDVAL

FND SRWINIT: Used to write in before report trigger. It is used to fetch the concurrent

request information and also used to set the profile options.

FND SRWEXIT: We will write this in after report trigger. Used to free the memory which

has been allocated by the other user exits

FND FORMAT_CURRENCY: This is used to convert amounts or currency from one

currency to other currency values and also used to display currency amounts in formats.

EX: SRW.USER_EXIT(‘FND FORMAT_CURRENCY’,

Code = ‘currency_code’,

Display_width=’15’,

Amount = ‘:cf_feb’,

Display = ‘:cd_feb’);

Return (:cd_feb);

FND FLEXSQL: This is used to get the data from flex fields. We will use in formula

columns.

FND FLEXIDVAL: This is used to get the data from flex fields. We will use them in

formula columns.

Note:

1. We use the User Exits in the Report triggers like before report, after report, before

parameter form, after parameter form.

2. We have to create a mandatory parameter called P_CONC_REQUEST_ID when we work

with FND SRWINIT, FND SRWEXIT.

3. The user exits are case sensitive we have to write them in capital letters only otherwise

they will raise an error.

Designing the Report: Create a new report manually. Go to data model and click on

the SQL Query then a dialog box SQL Query Statement will open write your query in

that.

Data Model:

Ex:

SELECT E.EMPNO,E.ENAME,E.SAL,D.DNAME FROM

SCOTT.EMP E,SCOTT.DEPT D

WHERE

D.DEPTNO=E.DEPTNO order by e.deptno

Layout Model: Now go to the layout model and design your report view as per the

requirements. See here in the below diagram we created P_CONC_REQUEST_ID in the

user parameters.

Report Triggers: Report triggers execute PL/SQL functions at specific times during the

execution and formatting of your report. The types of report triggers are given below.

Before Parameter Form: If we want to pass any initial values to the parameter we use

this trigger.

After Parameter Form: This trigger will fire after the entering the parameter values. To

validate the parameters and use to populate the lexical parameters, we use this trigger.

Before Report: In oracle 11i we use this trigger to initialize profile values.

Between Pages: This trigger is used to format the output and fires once per each page

except for the first page.

After Report: This trigger is used to free the memory or drop the temporary objects that

are created in the begining of the report or to send the output as mail.

Note:

1. In the report triggers we cant use any SQL statements directly there is package called

srw(Standard Report Writer) using this we can write any SQL statements.

Syntax: srw.do_sql(‘create sequence myseq

Increment by 1

Start with 1’);

2. To display any message box in the triggers or at any stage in reports6i we use another

procedure in srw package.

Syntax: srw.message(1001,’Hai this is a Simple Message from CHAITU’);

Go to the before report trigger and use the FND SRWINIT which is used to fetch the

concurrent request information and also to set the profile options.

Ex: Code in the before report trigger

function BeforeReport return boolean is

begin

SRW.USER_EXIT('FND SRWINIT');

return (TRUE);

end;

After writing the code then click on the compile button if there is no error then we will

get the successfully compiled message at the down right corner.

Now go after report trigger and write the code as given below and compile it after

successfully completion then close the window.

EX:

function AfterReport return boolean is

begin

SRW.USER_EXIT('FND SRWEXIT');

SRW.message(100,'P_CONC_ID: '||:P_CONC_REQUEST_ID);

return (TRUE);

end;

Note: Now our report is ready, save it and register the report in the oracle applications. Then

we will get the output as given below.

Output Window:

Profiles: Profiles are the changeable options which affect the way application runs. To

get these profiles we use three API’s those are given below.

1. FND_PROFILE.GET(‘Name of the Profile’, variable name);

2. variable name := FND_PROFILE.VALUE(‘Name of the profile’);

3. FND_PROFILE.SET(‘Name of the profile’, value of the profile);

The 1st and 2nd are same but, the only difference is FND_PROFILE.GET is the

procedure and FND_PROFILE.VALUE is the function so, it return a value. The Profile

values will be set in different levels those are given below.

_ Site

_ Application

_ Responsibility

_ User

_ Server

_ Organization

Site: This field displays the current value, if set, for all users at the installation site.

Application: This field displays the current value, if set, for all users working under

responsibilities owned by the application identified in the Find Profile Values block.

Responsibility: This field displays the current value, if set, for all users working under the

responsibility identified in the Find Profile Values block.

User: This field displays the current value, if set, for the application user identified in the

Find Profile Values block.

Profile: Enter the name of the profile option whose values you wish to display. You may

search for profile options using character strings and the wildcard symbol (%). For

example, to find all the profile options prefixed by "Concurrent:” you could enter

"Conc%" and press the Find button.

The navigation to get the system profile values information is Profile _ System.

The profiles are of two types those are given below.

1. System Profile and

2. User Profile.

The above given window is the System Profile window and the user profile window is

given below and the navigation is Profile _ Personal.

Note: To view personal profiles enter into the query mode and enter the profile name which we

have already then we get profile value details.

Some important Profiles:

ORG_ID

MFG_ORGANIZATION_ID

GL_SET_OF_BKS_ID

USER_ID

RESP_ID

RESP_APPL_ID

Note: To get the RESP_ID or RESP_APPL_ID…. Go with the following navigation.

Menu _ Help _ Diagnostics _ Examine. Then it will prompt for the oracle password,

enter that then we get the Examine and Variable Values window as shown below.

In the same way we can get the ORG_ID, RESP_APPL_ID, USER_ID…., by entering

what ever we want at the Field column in the above Examine window.

API’s To Register Concurrent Program:

FND_PROGRAM.EXECUTABLE: To Create executable and set the appropriate execution

method

Syntax:

FND_PROGRAM.EXECUTABLE(EXECUTABLE, APPLICATION, DESCRIPTION,

EXECUTION_METHOD, EXECUTION_FILE_NAME);

FND_PROGRAM.REGISTER: To create concurrent program

Syntax:

FND_PROGRAM.REGISTER(PROGRAM, APPLICATION, ENABLED, SHORT_NAME,

DESCRIPTION,….);

FND_PROGRAM.PARAMETERS: To set the parameter and to pass appropriate tokens

through the concurrent program

Syntax:

FND_PROGRAM.PARAMETER(PARAMETERNAME, VAlUESET, TOKEN, PROMPT,

SIZE);

FND_PROGRAM.REQUEST_GROUP: To set the parameter and to pass appropriate

tokens through the concurrent program

Syntax:

FND_PROGRAM.REQUEST_GROUP(REQUEST_GROUP_NAME, APPLICATION,

CODE, DESCRIPTION,..);

FND_GLOBAL_APPS_INITIALIZE: When ever we are working with profiles in PL/SQL

and submitting through the SQL we need to use this API the syntax for this API is given

below.

Syntax: FND_GLOBAL_APPS_INITIALIZE(user_id, resp_id, resp_appl_id,

[Security_group_id, server_id]);

Note: In this FND_GLOBAL_APPS_INITIALIZE the parameters user_id… and others we have to get

them manually from apps the process we discussed above see. The navigation is

Menu _ Help _ Diagnostics _ Examine.

EX: Declare

i number;

begin

FND_GLOBAL_APPS_INITIALIZE(1007899,20420,1);

i:=FND_PROFILE.VALUE(‘ORG_ID’);

dbms_output.put_line(‘Operation ID: ‘|| i);

end;

O/p: Operation ID: 204

FND_CLIENT_INFO.SET_ORG_CONTEXT: This is the API used to set the ORG_ID

value from the backend, so that system will retrieve the data from the database which is

related to mention the organization.

Syntax: Begin

FND_CLIENT_INFO.SET_ORG_CONTEXT(ORG_ID);

end;

EX: Begin

FND_CLIENT_INFO.SET_ORG_CONTEXT(204);

end;

FND_REQUEST.SUBMIT_REQUEST: When ever we want to submit the request to the

apps through the SQL we use this API. The syntax and a simple example were given

below.

Syntax: FND_REQUEST.SUBMIT_REQUEST(

Application, _ we have to pass the short name of the application concurrent

Program in which we registered (Ex: Oracle Receivables = AR).

Program, _ Pass the short name of the concurrent program

Description, _ description of the concurrent program

Start-time, _ start time of the concurrent program

Sub-request, _ if there any other request, pass true other wise false.

Arg-1,

- - _ These arg1 to arg100 are used to pass the parameters to the

concurrent program.

Arg100); _ Totally FND_REQUEST.SUBMIT_REQUEST API is having the 105 parameters.

EX: Declare

id number;

begin

FND_GLOBAL_APPS_INITIALIZE(1007899,20420,1);

Id:=FND_REQUEST.SUBMIT_REQUEST(‘AR’,CSPROC,NULL, NULL, FALSE, 20, RESEARCH);

dbms_output.put_line(‘Request ID:’ || id);

commit;

end;

O/P: Request ID: 2725363

FND_FILE.PUT_LINE: This API is used to send messages to output file or log file while

we are working with PL/SQL Stored procedures

_ For sending messages to output file we use this syntax

Syntax: FND_FILE.PUT_LINE(FND_FILE.OUTPUT,’OUTPUT MESSAGE’);

_ For sending messages to log file we use this syntax

Syntax: FND_FILE.PUT_LINE(FND_FILE.LOG,’LOG MESSAGE’);

Difference between ORG_ID & ORGANIZATION_ID: ORG_ID will come under

operating unit in Multiorg. ORGANIZATION_ID will come under inventory organization.

ORG_ID is used to distinguish the data of different business organizations.

ORGANIZATION_ID is used to identify the items belongs to which inventory organization

it has assigned.

MULTI ORGANIZATION (MULTIORG): Multiorg is nothing but under single oracle

application implementation maintaining multiple organizations and multiple set of books.

We will have Multiorg tables (table which is ending with _ALL). When ever we enter the

data, which will be stored in the Multiorg tables only.

Ex: OE_ORDER_HEADERS_ALL

OE_ORDER_LINES_ALL

OE_ORDER_HOLDS_ALL

PO_HEADER_ALL

PO_LINES_ALL

Multiorg Structure:

Business Groups: The business groups represent the highest level in the organization

structure like major divisions etc. business group secures human resources information

for example when we request a list of employees we can see the employees assign to

the business groups.

Set of books: It is a financial reporting entity uses a particular chart of accounts

functional currency and accounting calendar. Oracle GL secures transaction information

like general entries balances by set of books. When we assign to the oracle GL

responsibilities there will be a set of books assignment. We can see the information for

that set of book only. The navigation in that responsibility is

Setup _ Financials _ Books _ Define.

Legal Entities: It is a legal company for which we prepare physical or tax reports. We

will assign tax identifiers and other legal information.

Operating Units: It is one of the organizations where exactly company is being sales,

purchasing payments and receiving and so on. It may be sales office or a division or a

department. At operating unit level PO, AP, AR, OM modules will be implemented.

Inventory Organization: It is an organization where we are going to maintain

manufacturing information and items information. It captures inventory transactions,

balances. Oracle inventory, WIP, BOM will be implemented at inventory organization

level.

Sub Inventory: Sub inventory is nothing but stocks or goudons or warehouses, which

will be define under inventory organization.

Locators: Stock Locators is nothing but the specific location inside the sub inventory

where we will place the item.

Note:

· When ever we write a statement like SELECT * FROM OE_ORDER_HEADERS then

it shows “NO ROWS RETURN” as an output.

· When ever we are working with the Multiorg views if we want to get the data

from those views we have to set the ORG_ID of which Operating unit it is belongs

by using the API (FND_CLIENT_INFO.SET_ORG_CONTEXT(ORG_ID)). Then we

get the required information from the view.

· We found the list of ORG_ID’s in the HR_OPERATING_UNITS.

· To find the system is in Multiorg or not the query is

EX: SELECT MULTI_ORG_FLAG FROM FND_PRODUCT_GROUPS;

O/p: Y _ If it is a Multiorg system otherwise it is N.

Flex Fields: A flex field is a flexible data field that your organization can customize to

your business needs without programming. Oracle Applications uses two types of

flexfields, those are given below.

1. Key flexfields(KFF) and

2. Descriptive flexfields(DFF).

Key Flexfields: Key Flexfeilds are used to store the key information of data in

segments. We can call these segments as intelligent keys. For KFF we define flexfeild

qualifiers and segment qualifiers. The data of this key flexfeilds will store in segment cols

in a table.

EX:

1. Accounting Flexfeild (GL)

2. System Item(INV)

3. Item Catalog(INV)

4. Item Category(INV)

5. - -

Descriptive Flexfields: The descriptive flexfeilds are used to add the additional

information to the existing data. These DFF values will store in attributes column in

table. There is no need to define Flexfeild qualifiers and Segment qualifiers in DFF.

· The table FND_FLEX_VALUES will help us to retrieve the flexfields data in our

programs.

Existing DFF Customization:

_ Go with the Application developer responsibility and in that go through the

navigation Flexfeilds _ Descriptive _ Registers. Query the records as per the

table name and retrieve.

_ Copy the Title and go to the Segments form the navigation is

Flexfeilds _ Descriptive _ Segments. Query the records as per the title.

_ Uncheck the Check box Freeze Flexfeild Definition.

_ Now go with the Segments button and attach new column and we can attach

valueset.

_ Click the open button and make that as field as required (or) optional by

checking / unchecking Required box.

_ Even we can select default value type. We can also change the size and save it.

_ Now check the Freeze Field Definition.

_ Click compile button. Save it and close.

Note: When ever we click compile button system will submit Flexfield View Generator

concurrent program internally to create descriptive flexfeild view.

be happy and make others to be happy

Infolinks

Thursday, 24 May 2012

ERP1

1 comment: