Oracle Applications Techno Functional Resources Shared: Oracle Apps Technical-Full Review

Oracle Apps Technical Notes:

Oracle apps is basically standard ‘Oracle Financials’ in the initial stages and well popular for the Financial modules around the World.

After successful market growth in ‘Oracle Financials’ based in has standard the complete ‘E.R.P System’ and adding one by one module to the old “Oracle Financials”.

Up to 10.6 versions this E.R.P System is character based which support on Unix/Linux platforms and the later version are G.U.I Based supporting Windows.

From 11i the ‘Oracle Applications’ is called as ‘E-Business Suite ‘ From this it is completely designed on the basis of internet architecture.

The different Database, Forms and Reports versions if this software is follow:

Version	Forms	Reports	Database	Programming Language
10.6	2.3	1.5	7.1 / 7.2	Pro*c
10.7 Smart Child	4.5	2.5	7.3	Pl/sql , pro*c
10.7 Network Computing Architecture	4.5	2.5	7.3	Pl/sql, pro*c
11.0.x to 11.0.83	4.5	2.7(Not Released)	8.0	Pl/sql , pro*c
11i Internet Architecture 11.5.x to 11.5.8	6i	6i	8i / 9i	Pl/sql , pro*c
11.5.9	6i	6i	8i	Pl/sql , pro*c
11.5.10	6i	6i	9i	Pl/sql , pro*c

There are 3 types of instances on Oracle Apps, They are:

PROD (Production)- Used in Real time and contain no data.
TEST- Used for R&D and contain no data.
VIS (VISION) – Used for training time it has all testing data useful for all trails.

To find out the version and installation details following table will give:

V$INSTANCE

The details available in this table are as follows:

INSTANCE_NUMBER NUMBER

INSTANCE_NAME VARCHAR2 (16)

HOST_NAME VARCHAR2 (64)

VERSION VARCHAR2 (17)

STARTUP_TIME DATE

STATUS VARCHAR2 (22)

PARALLEL VARCHAR2 (3)

THREAD# NUMBER

ARCHIVER VARCHAR2 (7)

LOG_SWITCH_WAIT VARCHAR2 (11)

LOGINS VARCHAR2 (10)

SHUTDOWN_PENDING VARCHAR2 (3)

DATABASE_STATUS VARCHAR2 (17)

INSTANCE_ROLE VARCHAR2 (18)

ACTIVE_STATE VARCHAR2 (10)

The software can be installed in both UNIX and Windows operating systems. The installation directories will be as follow:

For UNIX -/V1/ORACLE

For WINDOWS -D: \ORACLE

The detailed installed directory’s directory structure is explained in the separate MS-EXCEL File Name: Oracle Apps1.xls

Different product families of oracle 11i are as follows

- Oracle Financials

- Oracle H.R.M.S.

- Oracle Manufacturing

- Oracle Marketing

- Oracle Administration

- (SSWA) Self Service Web Applications.

All SSWA are stored in VISCOM /HTML directory all the command based utilities in VISCOM/ADMIN.

Patch - basically is a solution application for the bugs or product up gradations/

.cmd for Windows .sh for UNIX

ADCMCTL is used or start the control of the concurrent manager/

ADFRMCTL - for FORMS server

ADREPCTL - for REPORTS server

CCMSVC.EXE drop / to recruit cll services

Fnd represents Application Object Library (AOL)

Fnd is a data dictionary for Oracle Applications.

All the FMB files of all modules are stored in

D:\oracle\prodappl\au\11.5.0\forms\us directory.

All the fmx files of all modules are stored in module specific forms directory for example ‘General Ledger’ related fmx files stores in

D:\oracle\prodappl\gl\11.5.0\forms\us directory.

In installation directory there are two environmental variables which stores all the path of the directory structures etc. details. They are ‘PROD.env’ and ‘APPSORA.env’.

Every application have an application Name and application short name. Some of the examples are as follows:

Short Name Application Name

GL General Ledger

AP Accounts Payable

AR Accounts Receivable

OE Order Entry

PO Purchase Orders

Oracle Applications have 3 important database schema users which are as follows:

APPLSYS APPLSYSPUB APPS

All the database objects of AOL in APPLSYS and all these files will have FND as prefix.

All fnd files user is APPLSYS

APPLSYSPUB has the public synonyms of fnd objects.

Apps contain the public synonym of all the objects of all the modules. APPS is the top most user in ORACLE APPLICATIONS.

When ever the first connection is made to Oracle Applications it will be connected to APPLSYSPUB which internally calls APPLSYS and checks fnd files, User name and password which are stored in FND_USER table.

Users & responsibilities

Responsibility is represents a subset of a module.

A collection of forms is called as a Menu.

A collection of Reports & Programs are called as Request Group.

A Menu and request group is get together called as responsibility. A responsibility represents partial functionality of a module.

TO CREATE A RESPONSIBILITY NEEDS A MODULE, A MENU, A DATA GROUP AND A REQUEST GROUP.

A Responsibility can’t represent multiple modules; it only takes a partial responsibility of a single module.

Out of the 4 major components of a responsibility, Request group is an optional component and the other 3 are mandatory.

To create user / responsibility the responsibility: System Administratory.

The Navigation for user creation: Security > user > Define

The navigation for responsibility creation:

Security > Responsibility > define

All registration details will be captured by AOL module.

AOL is a data dictionary for Oracle Applications.

Some seeded responsibilities of the Oracle important application modules are as follows:

Seeded Application Name Responsibility.

Oracle General Ledger - General Ledger Super User

Inventory - INV_NAVIGATE

Purchasing - Purchasing Super User GUI

OM - ADS_OM_SUPERMENU

AP - AP_NAVIGATE_GUI 12

AR - AR_NAVIGATE_GUI

HRMS - UK HRMS Navigator

There is another System Administration Responsibility which deals which all the java based Applications.

All the created responsibility information are stored in

FND_RESPONSIBILITY table.

All the created user main information are stored in

Fnd_user table.

All the created user detail information are stored in

Fnd_user_groups table. (-- Table or view doesn’t exist)

CROSS MODULE INTEGRATION:

Transfer of data information from one module to another module is called as ‘Cross Module Data Transfer’.

If any report is designed by using two or more module data base tables are called as ‘Cross Module Reporting’.

DATA GROUP

It is a collection of data base objects which binds both the front end and back end .

Standard and MRC (Multi Reporting Currency) data group are the two data groups provided by Oracle Applications.

Apart from those two data groups, user can create the data groups as per the necessity.

A user defined data groups over rides the functionality of the standard data group.

The responsibility is System Administrator

The navigation is Security > Oracle > Data group.

TABLE REGISTRATION PROCESS:

All the tables used in Oracle Applications needs registration.

All the modules in Oracle Applications will have File Objects like .fmx, .rdf etc. Files which are stored in their modules specific TOP directories. And the database objects like database files, sequences etc. are stored in different user schemas, which are specific to the modules.

Example: GL, AP, AR, INV, PO, OM etc.

All the database objects should create in module specific schema and a public synonym should be created in APPS schema.

The registration of a table is mandatory in Oracle Applications in the following two scenarios:

1. When a flex field is associated with the table.

2. When an alert is attached to a table.

There are 4 types of tables in Oracle Applications. They are:

1. Transaction Data Tables.

2. Interim Tables.

3. Seed data tables.

4. Special Tables.

A table which are used to save the data are called ‘Transaction Data Tables’.

A table which are used while converting the data from the other modules or from any legacy system to Oracle applications by using interfaces are called ‘Interim Tables’. These are also called as ‘Staging Tables’.

A table provided by Oracle with their seeded data relates the Oracle Application software are called as ‘seed data tables’.

And special tables are not yet started using and designed for future version purpose.

The Important steps to register a table in Oracle Applications:

Create a table in the module specific schema.
Create a public synonym in the APPS schema.
Register the table

a) Log on to Oracle Applications using Application Developer Responsibility.

b) Navigation: Application > Database > Table.

The information given in ‘user table name’ will be displayed in the Record history where ever this table is used.
The form used to register table is ‘FNDADDTC.fmb’.

The tables used to save the information of registered tables are:

FND_TABLES

FND_COLUMNS

By default the Oracle Applications have made this form as query mode only. The registration process of the tables and columns will be done from the back end only for which Oracle Applications has provided a package called AD_DD.

This Package has the following procedures:

Register_table --- To register a table.

Register_column---To register columns of a register table.

Register_primary_key----To register primary key.

Register_primary_key_columns----To register primary key columns.

Register_Foreign_key---- To register foreign key.

Register_Foreign_key_columns--- To register foreign key columns.

Dta_column ----- Unregister Columns

Delete_primarykey--- Unregister primary keys.

Delete_foreignkey-----Unregister foreign keys.

The register_table package will take 3 mandatory parameters to register a table, they are

- Application short name

- Table name

- Table Type

Apart from the above, the following are the other parameters which are non mandatory.

- Next Extent defaulted with 512

- PCT Free defaulted with 10

- PCT Used defaulted with 70

In the similar way the register_column will take 8 parameters to register columns, they are:

- Application Short Name

- Table Name

- Column sequence

- Column Data Type

- Column Length

- Nullable Y/N

- Transalatable Y/N

Apart from the above, the following are the other parameters which are non manadatory.

- Presicision Defaulted with null

- Scale defaulted with null

A Simple example to illustrate the table with column registration is as follows:

Connect to gl/gl@prod;

Create table demo_table (id number (2),

Name varchar2 (20),

Sal number (9, 2));

Connect to apps/apps@prod;

Create public synonym demo_table for gl.demo_table;

To find the Application short name, we can give the following select query at apps schema.

Select application_short_name, application_name from fnd_application

Where application_short_name like (‘%gl %’) or application_name_like (‘%general%ledger %’);

After getting the application name,

Execute ad_dd.register_table (‘gl’,’demo_table’,’t’, 3, 10, 90);

Please note: The application short name is a case sensitive.

Commit;

Execute ad_dd.register_column (‘gl’,’demo_table’,’id’, 1, number, 10,’y’,’n’);

We have to register all the columns and finally commit;

Please note: For date field the data length should be as 11 because the Oracle default date format is ‘DD-MON-YYYY’.

Now you can log on to application Developer and query this table registration.

REGISTRATION OF VIEW AND SEQUENSES:

The form to register view is ‘FNDADDVW’

The table to save this information is ‘FND_VIEW’

The package to register through back end is

The form to register sequence is ‘FNDADDSQ’

The table to save this information is ‘FND_SEQUENSE’

The package to register through back end is

FORMS REGISTRATION PROCESS:

All the forms using in Oracle Applications are needs registration and can be called through a menu prompt which internally calls a form function or through another form which internally calls a non form function.

All the form FMB files are saved in module specific form directory. An ideal AU_TOP forms directory will be

D:\oracle\prodappl\au\11.5.0\forms\US

All the form FMX files are saved in module specific form directory. An ideal directory for GL module is as follows:

D:\oracle\prodappl\GL\11.5.0\forms\US

D:\oracle\prodappl\PO\11.5.0\forms\US (For Purchasing)

A menu is nothing but a collection of form functions.

A form function represents the complete functionality of form.

A nom form function represents the partial functionality of form.

The steps to register the form:

Create the form using form builder.
Place the FMB file in AU_TOP forms folder.
Place the FMX file in module specific forms folder.
Register the form in AOL.

Navigation is Application > form
Create a form function for the form being registered.

The Navigation is Application > Function.

Attach the form function to a menu. The navigation is

Application > menu.

While creating the form function, in properties tab, the type is ‘FORM’. The two last tabs i.e. Maintenance mode and context dependency are java based.

For compiling the form in windows D2K environment press ctrl + T and in UNIX environment use F60GEN.

The registered information saves in the following tables:

Forms - FND_FORM

Functions - FND_FORM_FUNCTIONS

Menus - FND_MENU_ENTRIES.

Oracle Applications does not accept the long file names.

File name should always be in Capitals.

Extension name should be in small letters.

Generally the 1^st 30 characters of the file name represents the module name, the remaining 5 characters represents the functionality of the file.

(Any custom object will prefix XX followed by two characters code of the business entity. This is not a rule but industry standard)

REPORT REGISTRATION PROCESS:

All the reports or SQL*LOADER files and script files are need to be registered in Oracle Applications.

The steps for Registration:

Create a report using Report Builder.
Place the RDF file in both AU_TOP and module TOP specific reports folder.
Register the Report in AOL.

Create an executable for the report.
Create a concurrent program for this executable.
Attach the concurrent program to a Request group using the system Administrator Responsibility.

The Navigation for Executable Creation is

Concurrent > Executable

While creating the Executable for any Report the following mandatory information to be given,

Executable Name.

Short Name

Application Name

Execution Method & Executable File Name.

Please note that the short name given in this menu is to link this executable while creating a program.

Application Name in which this Report should run.

Execution methods this is to indicate the Oracle Application in the report is designed. There are following types:

1. Host

2. Immediate

3. Java Concurrent program

4. Multi language function

5. Oracle Reports

6. PL/SQL stored procedure

7. Request set stage function

8. Spawaned

9. SQL * LOADER

10. SQL * PLUS

The Executable file name is the actual file name of the report saved in the module specific reports directory without giving the extension name.

The information of the created executable is saved in

FND_EXECUTABLES Table.

The navigation for program creation is

Concurrent > Define.

While creating the program for any Executable, the following mandatory information to be given,

Program Name

Short Name

Application Name

Executable Name

Print Style

The program name is the name, which appears to the user while the user select the report in SRS Window.

SRS - Standard Request Submission.

Application Name in which the report should appear.

Executable Name is the name of the executable created in previous window.

Print style is the style in which the report should print, if the user opts for printing like landscape/portrait ctc.

The information of the created programs is saved in

FND_CONCURRENT_PROGRAMS Table.

The Navigation for Request Group attaching / Creation is through System Administration responsibility.

Security > Responsibility > Request.

Query a request group in which you want to add the report and add a record in the detail block to add the report.

You can create a new request group also in this screen which you need to attach the required responsibility to enable the user to access the report. As the responsibility can have only one request group, it is good practice to add the report to the existing request group.

The information of the Request Groups is saved in

FND_REQUEST_GROUPS & FND_REQUEST_GROUP_UNITS Tables.

The creation of Executable, Define of program and adding to request set can also be done from back end using the following procedures.

1. Executable >> FND_PROGRAM.EXECUTABLE

2. Define program >> FND_PROGRAM.REGISTER

3. Add parameter in program >> FND_PROGRAM.PARAMETER

4. Add to request group >> FND_PROGRAM.ADD_TO_GROUP.

VALUE SETS:

These are a set of values, used to validate/input certain data while passing parameters to flex fields or Reports.

Attribute columns are used to add additional information.

Value sets is an object in Oracle Applications used to validate the data, which comes into the additional columns of the second tables.

Steps to create a value set:

Log on to Oracle Applications using Application Developer Responsibility.
The Navigation is Application > Validation > set.
While creating the value set the following information is need to be given

Value Set Name
List Type
Format Type
Maximum Size
Minimum Value & Maximum value for number types
Validation Type

Different list types are :

List of values

Long List of values

Pop List

The major difference in List of values and Long list of values is providing the data filters. Data filters are mandatory in long list of values.

Different Format Types are:

Char

Date

Date Time

Number

Standard Date

Standard Date time

Time

Oracle Advised to user standard date and standard date time as it is withdrawing the Date & Date types in future versions.

There are 3 optional check boxes after the format types, They are

Numbers Only - Allow only numeric data

Uppercase only - Allow character with upper case only.

Right Justify & Zero fill numbers - It left the value with Zeros to the maximum size.

Different Validation Types are:

Dependent - It dependants another value set.

Independent - It provides a list of values

None - Doesn’t provide any list of values

Pair - It provides two values like from date & to date

Special -

Table - Provides the list of values from a database table.

Translatable Independent - For Multi language reports.

Translatable Dependent - For multi language reports.

All the value sets information are saved in

FND_FLEX_VALUE_SETS Table.

We can provide any static values for independent value set apart from the table values also.

Only when we attach the value set to a parameter then it gets executed.

For Table value sets:

When validation type is only Table, the edit information button gets activated. By clicking this button a new window opens where you can select the table from which you need the values for which you need to select the application name and then table name. After select the both, the columns of this table are available for selection.

You can give any where clause, order by clause while getting the values from column as mandatory.

For FND_RESPONSIBILITY the sequence for the id generation is FND_RESPONSIBILITY_S.

PARAMETRIC REPORT REGISTRATION:

For all the parametric reports in Oracle Applications, the value is mandatory for the parameter.

Except the following additional step all the steps are common for parametric report registration as a normal report registration.

While creating the program, you need to register the parameters for which there is a button called ‘PARAMETERS’ in the bottom of the screen.

In the parameters option, you need to give the following information mandatory:

1. Sequence of the parameter.

2. Parameter name (This will display while the report is running at SRS window).

3. Attach a value set.

4. Token (This is the actual parameter defined in the SQL query of the report).

PL/SQL Procedure Registration:

All the navigation and procedure are same as Reports registration except the Execution method in creation of Executable. You need to give PL/SQL program instead of Oracle Reports.

Please note there are two mandatory out parameters for any PL/SQL program used in Oracle Applications, They are,

Errbuf - Varchar2 Data type.

Retcode - Number Data type.

And these two parameters are need not to register while registration of program in parameter form.

Any other parameters used in the PL/SQL program should be followed these two parameters and the other parameters need to be mentioned in parameter option of program creation.

Please note that the token is neednot to mention for any Pl/SQL program.

DATA MIGRATION IN ORACLE APPLICATIONS:

Gap analysis is the identification of the gap between the legacy system columns with Apps system columns.

GAP may be data, reports and forms.

GAP is the variation between the data and programs within the legacy system & ERP application.

In general, the mapping document will be designed by Functional Consultants where it will be defined which column in legacy should be mapped to APPS column.

The technical consultant needs to bring the data in to technology independent flat file which can read by Oracle.

To transfer data from the flat files to staging tables the program will be called as loader program. For this there are two important utilities,

They are

1. SQL * LOADER

2. UTL_FILE

The major difference between the SQL * LOADER and the UTL_FILE are as follows:

SL NO.	SQL * LOADER	UTL_FILE
01	It is a tool of Oracle	A DBMS utility package
02	Use ‘c’ Language	Pl/SQL Language
03	Can be executed only on server	Server & Client side also executed
04	Can not validate data	Data can validate
05	not directory specific	Directory specific
06	Command based utility	Can executed at SQL prompt
07	Performance efficient	Performance inefficient
08	Doesn’t need DBA permission	Needs DBA permission

Data migration is of two types, they are

1. Data conversion which is one time activity.

2. Interfaces which is a recurring activity.

SQL * LOADER:

A Simple example to convert the following flat file data to Oracle in Oracle Receivables Module.

C:\sample.dat (Name)

1, rajasekhar, 4000

2, swatantra, 6000

3, nanda, 8000

4, pranay, 9000

5, reddy, 12000

Steps to convert this data to APPS:

Connect to database ar/ar@prod.
Create table load_demo (id number,name varchar2(20),sal number(9,2));
Connect to apps/apps@prod.
Create public synonym load_demo for ar.load_demo.
Create a control( .ctl ) file with the following lines

Load data infile ‘c: \sample.dat’

Insert into table load_demo

Fields terminated by ‘,’

Optionally enclosed by ‘”’

trailing nullcols

(Id, name, sal)

OR We can give the data also here instead of save ‘C: \sample.dat’

Load data infile * insert into table load_demo

Fields terminated by ‘.’ Optionally enclosed by ‘”’

trailing nullcols

(Id, name, sal)

Begindata

1, rajasekhar, 4000

2, swatantra, 6000

3, nanda, 8000

4, pranay, 9000

5, reddy, 12000

6. saves this file with the name ‘sample.ctl’ in D:\oracle\prodappl\ar\11.5.0\bin directory.

7. Register the script in AOL with execution method as SQL * LOADER script.

(Please note there will not be any parameters allowed)

8. Create a program.

9. Attach to a request group.

10. Run from the specified Responsibility in SRS Window.

11. Check in ar schema for the converted data..

INTERFACES.

These are the utilities provided by Oracle Applications to convert the data from one module to the or from any legacy system to Oracle Applications.

It is very important that, one should not update any Interface tables under any situation which Oracle treats as a crime.

A Technical consultant will converts the data from technology independent legacy system files to technology dependant Oracle tables (Interim/Staging Tables) as a first step in converting the data.

All the validations will be done before converting the data from the staging tables to interface tables. A successful code generated by the technical consultant will transfer all the records from the staging table to interface table.

Once the data is transferred to interface tables, Oracle Applications has provided an important program to convert the data from this interface tables to actual module specific tables.

AFTER THE DATA IS INSERTED TO THE ‘PO ‘INTERFACE TABLES THEY ARE:

1.PO_HEADERS_INTERFACE.

2.PO_LINES_INTERFACE

3.PO_DISTRIBUTIONS_INTERFACE

‘IMPORT STANDARD PURCHASE ORDER ‘program from front for which the following are the parameters to be given.

1. DEFAULT BUYER

2. CREATE OR UPDATE ITEMS

3. APPROVAL STATUS (APPROVED, INCOMPLETE, INITIATE APPROVAL)

4. BATCH ID.

CUSTOM MODULE DEVELOPMENT:

For creating any new module/application in Oracle Applications the following are the major mandatory things to be created.

USER, RESPONSIBILITY, DATA GROUP & MENU.

Steps to create the custom Module:

1.Log on to Application Developer and Register the application name. The navigation is Application > Register.

2.Give the application name as ‘Custom Application’, Short name ‘CUST’, Base path ‘CUST_TOP’.

3.Create an user for cust schema by giving the following command : Create user cust identified by cust ; Grant connect, resource to cust;

4.In general the table space for a schema is 2 GB and index table space is 1/5^th of the data base.

5.Create a directory structure in APPL_TOP folders as follows:

D:\oracle\prodappl\cust\11.5.0\forms\US

Reports\US.

Bin

Out

Log

Sql

Install

6.Create the user of the custom application in system Administration Responsibility.

7.Create the tables in cust schema.

8.Create the public synonym in apps schema.

9.Repeat the same two steps for views/sequences.

10. Create forms.

11. Place the .fmb files in AU_TOP form folder.

12. Place the .fmx files in CUST_TOP forms folder.

13. Register the forms in AOL. (Application > form).

14. Create the form function for forms.(Application > function ).

15. Create menu (Application > Menu).

16. Attach the form function to menu.

17. Create the reports.

18. Place the .rdf files in both AU_TOP and CUST_TOP reports folder.

19. Create an executable for reports.

20. Create the concurrent program for the executables.

21. Create request group and attach the programs.

22. Attach the custom application to the standard data group.

23. Create the responsibility for the custom application.

24. Create a user and associates the custom responsibility to this user.

25. Modify the PROD.env file in Oracle Directory with the CUST_TOP directory details.

26. Execute the PROD.cmd to make changes in windows registry also.

27. Log on to Oracle Apps and access the custom application through the custom responsibility.

While Doing the above steps the following points to be noted:

1.When registering the application database in Oracle Applications

(N > Oracle > Register), It is good practice that, the database user name and password should be same. The privilege is ‘Enabled’.

2.There are 4 Types of privileges for any database , they are as follows:

a. Enabled Allow , Insert , Delete , Update , Select

b. Disabled No DML, NO Select.

c. Restricted only Select.

d. Externally can access data from external data base.

3.Install group is 0 or 1 , If you give install group as zero the Oracle Applications will allow you to create one set of books for this application. It is always (n+1).

4.All the data group information is stored in FND_DATA_GROUPS and FND_DATA_GROUP_UNITS tables.

Example:

Steps for create new TOP.

1. Create one table in Scott or any application (AP, AR, GL,).

2. Grant permissions on table to apps.

3. Create a new TOP (Directory).

4. Register your own top.

5. Copy .fmb and .pll files.

6. Building custom form.

7. Define Responsibility, Form, Function & Menu.

Grant permissions on a table:

Enter to SQL with apps user.

SQL> conn scott/tiger@prod.

SQL>Grant all on DEPT to apps;

SQL>Conn apps/apps@prod.

SQL>Create public synonym dept for scott.dept;

SQL>select * from dept;

SQL>commit;

Create a new (your own) TOP (Directory):

D:\oracle\prodappl

Right click & select a new folder ---NEW_TOP.

NEW_TOP > 11.5.0 > forms, reports, log, bin, sql, resource, java, admin, patch

NEW_TOP > 11.5.0 > forms > US / reports > US.

Register your own top (NEW_TOP):

A. Edit PROD.cmd (windows) -> oracle \ prodappl.

Prod.env (Linux)

Set NEW_TOP in D:\oracle\prodappl\new_top \11.5.0

Run the script

D:\oracle\prodappl\prod.cmd (At Windows RUN).

D:\oracle\prodappl\set new_top.

regedit

B. Regedit Run

HKey > Local Machine > software > oracle > applications > 11.5.0 > prod >

DEV35 (System Name).

Here Right click, New > String Value

Double click on NEW_TOP & Give path D:\oracle\prodappl\new_top\11.5.0 & close.

Copy .fmb & .pll files:

a. Copy .fmb files

D:\oracle\prodappl\AU\11.5.0\Resource

Copy Template.fmb, Apstand.fmb & Paste to your top.

a. Copy .pll files.

D:\oracle\prodappl\Au\11.5.0\Resource

Copy all .pll files & paste in D:\oracle\prodora\8.0.6\forms

Building Custom form:

Open your forms template.fmb & apstand.fmb

Select Template.fmb & save as new form.fmb

In Data blocks delete all items (Data Block & Detailed Block)

In Triggers PRE_FORM -à NEW_TOP in place of new_form

Then Compile & Save.

Click on Program Units à select first one (package body) if (wnd=’block name’)

Compile & Save through (Program à Compileàall).

(File à Administrationà Compile file) & Save.

Define Responsibility:

Switch Responsibility to Application Developer.

è Application à Register.

Application = New_appl; Short Name= new_appl; Base path = New_top; Desc =Sample

è Application àForm.

Form=new_form; Application=new_appl; user form name=new_form;

èApplication à Function.

Function=new_function; user function name=new_function; form=new_form;

èApplicationàMenu.

Menu=new_menu; user menu name=new_menu.

Seq=1; Prompt=cust_form; sub menu=’xxx’; function=new_function; Grant=Enabled.

Switch to SYSADMIN:

è SecurityàResponsibilityàDefine.

Here Fill Application – new_appl; Data group= Standard Group;

Create User & Add Responsibility to This User. //////

UTL_FILE:

This is also another utility to convert the data from flat file to table.

Some Important Commands are,

FOPENà TO OPEN FILE.

FCLOSEà TO CLOSE FILE.

GETLINEà TO READ A LINE FROM FLAT FILE.

PUTLINEà WRITE A LINE TO DATA FILE.

A generic structure of a UTL_FILE is as follows:

File utl_file.file-type;

Rec varchar2 (80);

FILE: = UTL_FILE.FOPEN (‘C: \TEMP’, ‘SAMP.DAT’, ‘R’);

UTL_FILE.GET_LINE (FILE, REC);

DBMS_OUTPUT.PUT_LINE (REC);

EXCEPTION

WHEN OTHERS THEN

UTL_FILE.FCLOSE (FILE);

Saves the above information in a file as C:\utl.sql and run the same from SQL prompt as @c: \utl;

The data will be displayed on the screen.

/* sample program to load into .csv file */

Function dump (p_dir varchar2 default ‘c: \temp’,

P_separator varchar2 default ‘,’,

P_filename varchar2)

Return number is

L_output utl_file.file_type;

L_separator varchar2 (10) default ‘’;

Cursor c is select * from EMP;

X c%rowtype;

Begin

L_output:=utl_file.fopen (p_dir, p_filename,’w’);

Open c;

Loop

Fetch c into x;

Exit when c%notfound;

Utl_file.put (l_output, l_separator||x.empno);

L_separator:=p_separator;

Utl_file.new_line (l_output);

End loop;

Close c;

Utl_file.fclose (l_output);

Return (‘successes);

End;

/* Procedure to dump the values into to data base using UTL File */

Procedure dump2 (p_dir varchar2 default ‘c:\temp’,

p_filename varchar2,

p_separator varchar2 default ‘,’)

L utl_file.line_type;

record varchar2(1000);

X emp.ename%type;

Y emp.empno%type;

Z emp.sal%type;

Begin

L: =utl_file.fopen (p_dir, p_filename, and ‘r’);

For I in 1. . 14 loop

utl_file.get_line (L, record);

Select to number (substr (record, 1, instr (record,’’1, l)-1) ;

Into y from dual;

x:= substr(record, instr(record,’,’1,1) + 1, instr(record,’,’ , 1,2) INSTR(RECORD, ‘ , ‘ , 2,1) -1));

select to_number(substr(record,instr(record,’ ,’ 1,2 ) + 1 , instr(record , ‘ , ‘ -1,1))) into z from dual;

dbms_output.put_line(y || ‘ ‘ || x||’ ‘ ||z);

Insert into sample values (y, x, z);

end loop;

utl_file.fclose(L);

end;

PARAMETRIC VIEWS:

A View created based on a parameter passed to the view is called parametric views. All the views used in Oracle Applications are only parametric views.

A Basic construction of a parametric view is as follows:

Create view view_name (parameter data type) as select * from Table_name where col.name = parameter;

TO SET THE ORGANIZATION ID ANY VIEW IS:

Most of the Oracle Applications are developed using D2K forms. To make the total software in similar look like font sizes, LOV Positions, colors , properties of the different objects, Oracle has provided the following files using which , we can develop any new forms also which resembles completely as the forms supplied by Oracle Applications.

For this, Oracle Applications has provided a TEMPLATE.fmb file which is already attached with required .PLL files and all Visual attributes and property class files.

Some important .PLL files are:

1.APPCORE

2.APPDAYPK

3.CUSTOM

4.FNDSQF

5.GLOBE

6.JL

7.JE

8.JA

9.FOH_GEN

10. PSAC

11. VERT

Some important object groups are:

1.STANDARD_PC_AND_VA

2.STANDARD_CALENDER

3.STANDARD_TOOLBAR

Steps to Build a new form using TEMPLATE.fmb:

1. Take a copy if TEMPLATE.fmb from AU_TOP forms folder/ Resource folder (Recommended).

2. Please note that the size of TEMPLATE.fmb in 11.5.9 Version is 360 KB, In 11.5.10.2 is 376 KB.

3. Delete the Blocks with the names BLOCKNAME and DETAILBLOCK.

4. Delete the canvas with BLOCKNAME.

5. Delete the Window with the BLOCKNAME.

6. Please note that, Window name, Canvas name , Procedure or Function should be as block name. And package name as form name.

7. Create a window and assign ‘Window Property Class’ to this:

a). Sub Class information à Window.

8. Create a Canvas and assign ‘ Canvas Property Class ‘ to this :

a). Subclass information à Canvas.

9. Assign window to canvas and canvas to window.

10. Create a Block in the Custom Canvas . Subclass information à Block,

11. Assign TEXT_ITEM Property Class to all text items.

12. Modify the ‘ APP_CUSTOM’( Package Body) as follows: If ( wnd = ‘win1’) then

Here ‘Win1’ is your own Window name.

13. Modify Two Triggers:

a). PRE_FORM à Console Window name --- Our Block name.

b). WHEN-NEW-FORM-INSTANCEà TEMPLATE--- NEW_FORM ( Your Form Name)

14. Save the form and Compile the form .

15. Place the form FMB file in AU_TOP forms folder.

16. Place the form FMX file in Module specific TOP form folder.

17. Register the form in AOL.

Go to Application Developer.

a) Register Form (Application > Form ).

b) Attach Form to Function .( Application > Function ).

c) Attach Function to Menu ( Application > Menu ).

d) Attach Menu to Responsibility.

e) Attach Responsibility to User.

FLEX FIELDS:

A Construction of different identifying factors are called as “ FLEX FIELDS”.

A Flex field is a Collection of Flex field structure.

A Flex field structure is a collection of segments.

A Segment can take up set of Values.

For Each Value there is an associated code combination.

The Business Rules are defined by identifying the valid code combinations.

The Maximum code combinations are the factorial of segments number.

All the code combinations of flex fields in GL Module are stored in GL_CODE_COMBINATION_KFV Table.

For Every structure has one structure id will be created in FND_ID_FLEX_STRUCTURES Table.

In this file the definition of segments will be defined.

Every Code Combination will have an ID.

These Flex fields are of two types, They are :

1.KEY FLEX FIELDS.

2.DESCRIPTIVE FLEX FIELDS

à To capture the key business information key Flex fields are used.

à To Capture the additional business information Descriptive Flex fields are used.

à KFF are made up of segment prefix columns.

à DFF are made up of attribute prefix columns.

à For each segment the value set is mandatory.

à For DFF Value set is optional.

à A KFF maximum can have 9 segments.

à A DFF maximum can have 15 attributes.

à A value of KFF segment is structured id, code combination id, coded value of segments.

à A value of DFF attribute is Attribute category, value for attribute.

GENERAL LEDGER MODULE:

Accounting Key Flex field is available in GL Module.

To enter any Transaction in GL Module , we use the Journal Entry .

It has two modes Journal mode / Batch Mode.

Each Journal has multiple lines.

A Journal line entry either has a debit / Credit amount.

All the Debit amount totals should tally with Credit amount totals.

All the Journal information is stored in the following tables.

GL_JE_BATCHES

GL_JE_HEADERS

GL_JE_LINES

General Ledger is implemented by a set of books.

A Set of Books is a collection of 3 C’s . They are,

CALENDAR

CURRENCY

CHART OF ACCOUNTS.

To implement chart of accounts we use Key Flex field.

The setup information of GL is stored in GL_SET_OF_BOOKS Table.

To set up the calendar the Navigation is:

SETUP > FINANCIALS > CALENDAR > TYPES.

While Defining a Calendar , We need to make the following points.

1.Name of the calendar is mandatory.

2.Year types are of two types i.e. Calendar Year ( Jan - Dec ) / Fiscal Year ( Ape - Mar ).

3.Periods may be a month, quarter or ever a day.

4.Always periods must be actual number + 1, Which is for adjustment period.

5.For Example , if you take month as a period type, we should define 13 periods. 13^th is for adjustments.

All the calendar period information are stored in GL_PERIODS and

GL_PERIOD_SETS table.

All the Journals enter into Oracle Applications are called as UNPOSTED Journals.

There is a GL_BALANCES master table in GL Module , Which will maintain the opening and closing if a open period.

All financial statements will come from this open period.

A ‘ POST PROGRAM ‘ will update the GL_BALANCES by posting the unposted Journals.

When a current period is closed the ‘ POST PROGRAM ‘ is automatically run to update the GL_BALANCES.

All the left out entries of the closed period will be entered in the adjustment period.

To Define CURRENCY the Navigation is:

SETUP > CURRENCIES > DEFINE.

To Define the Flex field structure for chart of accounts :

SETUP > FLEX FIELDS > KEY > SEGMENTS.

Create segments for Accounting Flex Fields and attach the value sets or each segment. Freeze the structure and compile the structure.

To Define the set of books:

SETUP > FINANCIALS > BOOKS > DEFINE.

Create the set of books with the created chart of accounts flex field.

While creating the set of books , future periods is the number how many periods, we can enter entries even without ‘ Opening the period ‘.

The set of books information is saved in:

GL_SETS_BOOKS Table.

ACCOUNTS RECEIVABLE MODULE

All the setup information of the Oracle Accounts Receivable module are stores in

AR_SYSTEM_PARAMETERS Table.

All Organization information is stored in

HR_OPERATING_UNITS

To Know the current operating unit organization id from backend.

SELECT FND_PROFILE_VALUE(‘ ORG_ID ‘) FROM DUAL;

To Know the Current operating unit organization id from front end.

GO TO HELP > DIAGNOSTICS > EXAMINE.

BLOCK NAME > PROFILES.

FIELD > ORG_ID.

The Operating units defined to which responsibility is defined by PROFILE OPTIONS.

The profile options is a set of parameters which define the access to various components of Oracle Applications. These are of two types.

SYSTEM PROFILES – Module independent.

USER PROFILES - Module dependent.

User Profiles are having module short name as prefix.

You can assign the profile option values at 4 levels.

1. SITE LEVEL.

2. APPLICATION LEVEL

3. RESPONSIBILITY LEVEL

4. USER LEVEL

A Business entity must go for multiple set of books when the operating units within a business entity have different functional currency or different accounting calendars or different chart of accounts.

Profile option settings can be set with the following navigation in SYSTEM ADMINISTRATION Responsibility.

PROFILE > SYSTEM.

A Profile Option is created by application developer responsibility and it is assigned a value by system Administrator Responsibility.

A User profile option can not be assigned a value at user level.

A Site level profile option will be over rights by Applied on level profile option.

An Application level profile option will over rights by Responsibility level profile option.

All the profile option information is saved in

FND_PROFILE_OPTIONS

FND_PROFILE_OPTION_VALUES

Note: All the General Ledger modules tables do not have ORG_ID.

An ideal flow of the Accounts receivable module is as follows:

Create a Customer ( Customer > Standard ).

Create Customer Transaction.

Receipt.

Transfer to GL.

In GL Post program to update balances.

Customer > Standard.

It will give a query find window first, if the given code’s customer is not found , it will prompts to create a new one and asks all the required information.

All the Customer related information are saved in :

HZ_CONTACT_POINTS

HZ_CUST_ACCOUNT_RELATE_ALL

HZ_CUST_ACCT_ROLES

HZ_CUST_ACCOUNTS

HZ_CUST_PROFILE_AMTS

HZ_CUST_RECEIPTS_METHODS

HZ_CUST_SITES_USES_ALL

HZ_LOCATIONS

HZ_ORG_CONTANTS

HZ_PARTIES

HZ_PARTY_SITES

HZ_PERSON_PROFILES

TCA: Trade Community Architecture.

All the parties created for Order Management , Shipping, Inventory, Accounts Receivables will be in above Tables.

A Party can be an Employee, Employee dependents or Organization .

The Basic table is HZ_CUST_ACCOUNTS.

Customer Name is Party name in HZ_PARTIES.

Party_id is Customer_id.

Customer_No is Account_Number in HZ_PARTIES.

Party residing Places will be stored in HZ_CUST_SITE_USES_ALL.

All the profiles attached to the customers are defined in HZ_CUSTOMER_PROFILES.

The Credit limit of a Customer is saved in HZ_CUST_PROFILE_AMTS.

RA_HCUSTOMERS is a View which derive the date from HZ_PARTIES, HZ_CUST_ACCOUNT Tables.

A Detailed flow of information linked with Accounts Receivables and Order Management is as follows:

Create Customer.

Enter Order.

Book Order.

Pick Release the Order.

Drop Shipment.

Internal Sales Order.

Work Flow Back Ground Process.

Transfer the Released Order to RA_INTERFACE_LINES.

Invoice auto import.

Create Invoice.

Receipt.

Transfer to GL.

Update GL Balances.

To Raise Invoice in Accounts Receivables Module the Navigation:

Transaction > Transactions.

The Information of the transactions will be stored in

RA_CUSTOMER_TRX_ALL.

RA_CUSTOMER_TRX_LINES_ALL.

Invoice_no is Identified by TRX_NUMBER Column.

There are Three Types of Invoices:

1. Invoice à Standard.

2. Credit Memo à For excess value billed / Return goods.

3. Debit Memo à For Less Value Billed.

4. Charge Back à Create a new Invoice for Remaining Balance After the Due Date.

5. Guarantee à It is an Agreement without deposit for fixed amount.

6. Deposit à Advance amount paid by Customers as Security.

CUSTOMER_TRX_ID is used to link RA_CUSTOMER_TRX_ALL With other Transaction related views.

To pick up the Customer details you can link Party_id in HZ_CUST_ACCOUNTS and HZ_PARTIES with sold to cust_id of RA_CUSTOMER_TRX_ALL.

In Transaction lines they are 3 types :

Line,

Freight

Tax.

The Values distributed to General Ledger accounts are stored in

RA_CUST_TRX_LINE_GL_DIST.

There are two status in AR_PAYMENTS_SCHEDULE_ALL, They are,

GL_OPEN

GL_CLOSE

To Know on how many invoices the receipt is saved in:

AR_RECEIVABLES_APPLICATIONS_ALL

AR_CASH_RECEIPTS_ALL

All the suppliers details are generally defined in PURCHASING Module and the information will be saved in

PO_VENDORS

PO_VENDOR_SITES_ALL

PO_VENDORS_CONTACTS_ALL

Purchasing module do not have any interface tables.

PO_VENDORS are not ORGANIZATION specific, Where as sites and contacts are ORGANIZATION Specific.

A Supplier type is identified by VENDOR_TYPE_LOOKUP_CODE.

Stocks receive matching is of two types.

1. PURCHASE ORDER.

2. PURCHASE ORDER RECEIPT.

Vendor number is stored in SEGMENT1

A Supplier site payment terms always over rights the supplier payment terms.

The interface tables in Accounts payable module are:

AP_INVOICE_INTERFACE

AP_INVOICE_LINES_INTERFACE

After the import is done using OPEN INVOICES IMPORT program, the data will be entered into the following tables.

AP_INVOICE_ALL.

AP_INVOICE_LINES_ALL.

AP_INVOICE_DISTRIBUTIONS_ALL

INVOICE_ID is the common column between the above 3 tables to join.

All the invoices entered should be validated in through the validate option from actions button.

FND_FLEX_EXT.GET_CCID is used to return code combination id of flex field combination if exists. Otherwise it creates the id and returns the id , For this, we need:

Application Short Name.

Flex field code.

Structure id.

USAGE OF CUSTOM.PLL IN APPS FORMS:

There is a custom.pll in Oracle Applications through which we can change the functionality lf a form with out changing anything in the from .

Different procedures available in CUSTOM.PLL are:

ZOOM AVAILABLE.

STYLE.

EVENT.

Steps to use the CUSTOM.PLL:

1.Open the required form in D2K.

2.Open the CUSTOM.PLL in the PL/SQL library of the form.

3.Change the code in CUSTOM.PLL as per the required changes of the functionality.

4.Compile the CUSTOM.PLL.

5.Delete already attached CUSTOM.PLL in the form.

6.Re attach the newly changed and compiled CUSTOM.PLL to the form.

7.Compile the form .

8.Copy the Revised .fmb file to AU_TOP Forms folder.

9.Copy the Revised .fmx file to Module specific Forms folder.

10. Check the changed functionality of the form front end.

ZOOM AVAILABLE in the Procedure used in the CUSTOM.PLL to open another form from the current form.

Oracle Applications Techno Functional Resources Shared

July 29, 2015

Oracle Apps Technical-Full Review

1 comment:

COALESCE-SQL