Oracle Apps Technical Notes
Uploaded by
kunjeshankurripuOracle Apps Technical Notes
Uploaded by
kunjeshankurripuwww.selfonlinetraining.wordpress.
com
1|Page mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Oracle Applications
Oracle Applications/Apps is also known as Oracle ERP (Oracle Enterprise Resource
Planning), Oracle EBS (Oracle's E-Business Suite).
Oracle Apps is ERP Package (Enterprise resource Planning).
Oracle Apps is a collection of modules.
It is used to running a business.
Software which is pre-developed and ready to use is called package.
Oracle apps is pre developed software.
Total 256 modules are available in oracle apps.
Top 15 List of modules from oracle apps.
1) Oracle General Ledger
2) Oracle Accounts Payable
3) Oracle Accounts Receivable Deductions
4) Fixed Assets
5) Oracle Cash Management
6) Purchase order
7) Order Management
8) Oracle Inventory
9) Oracle Bills of Materials
10) Oracle Work in Process
11) Oracle Human Resource Management System (HRMS)
12) Oracle CRM (Customer Relationship Management)
13) Project Accounting
14) Service Contracts
15) Depot Repairs
2|Page mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Examples of ERP Packages: -
1) Oracle Apps
2) SAP
3) JD Edwards
4) PeopleSoft
5) Siebel
6) Microsoft Dynamics
7) Baan
8) Lawson Software
Types of roles in oracle apps: -
1) Functional consultants (FC)
2) Technical consultants (TC)
3) Apps DBA
1) Point of Functional consultants (FC): - They regularly interact with client and
understand the client requirement. After that they create a document (MD-50)
(Functional specification document). They act as a mediator between client and
technical consultants (TC).
2) Point of Technical consultants (TC): -Technical Consultants received (MD-50)
from functional consultants to understand the client requirement and then
they need to create a document (MD-70). MD-70 document contains step by
Step approach followed to achieve the specific task.
3|Page mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
For getting the mediator between client and technical consultant’s benefits is that
functional consultants are having industry experience they easily understand the
customer requirement. Like Client (HR Manager) trying to explain HR polices to
Technical consultants. They are not able to understand the poli7ces for this reason
FC (MBA HR) can better understand the polices because he already study about
that and also having industry experience. So for every Client department oracle
have functional consultants to understand the better requirement of the client.
APPS DBA: -
Responsibility of Apps DBA: -
1. Instances (Server)
2. Support 24*7
3. Applying Patches
4. Cloning
A company need to get minimum 3 servers for the data.
Cloning done by Apps DBA.
Cloning means copy the data from one server to other server.
4|Page mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Financial Modules: -
General Ledger
Accounts Payable
Accounts receivable
Fixed Assets
Cash Management
Supply Chain Management Modules: -
Purchase order
Order Management
Inventory
Manufacture Module: -
Inventory
Bill of material
Work in process
File Structure of Oracle Apps
12.0.0
5|Page mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Point of file Structure of oracle apps: -
The root directory is called application_top.
Under Application top we have modules specific top examples Cust_top, Ap_top, Inv_top,
GL_top.
12.1.3 -- signifies version of oracle Apps.
US --Language specific Directory (Only in Forms and reports)
_________________________________________________________________________
IF we create a form for inventory module we should not save that form in inv_top we save
that from in cust_top.
Cust_top is an additional top. All the custom files created by the developer should be
saved in cust_top. It is frequently used top.
Files created by developer are called custom files & by default files are called standard
files/Predefined files.
___________________________________________________________________________
Schema: - Schema is a memory location associated to a user. It is collection objects
Specific to a user.
Synonyms: - is an alternate name (extra name) given to a table.
For example IF we need to create a form with below mention columns name but all the
columns are from different schema.
Supplier_no Supplier_name Item_no Selling Price
AP AP INV OM
IF we connect to AP Schema Can we access all the Columns Name
Yes Yes NO NO
We just can access only two columns name because AP Schema have only two columns
Oracle Apps Database contains modules specific schema.
6|Page mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
When we connect to module specific schema, we can access data from that module only.
To access data from all modules we have special schema that is Apps Schema.
Apps schema is a collection of Synonyms.
Database Structure
_______________________________________________________________________________________________
Login to Oracle Apps
Open Brower By Default username and password is operations/welcome
7|Page mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Home Page After login
First Page Contains List of responsibilities.
Responsibilities
Responsibility is used to access module.
For example: - if you need to work in inventory module then you need to select
Inventory, Vision Operations (USA). Like this all the modules have their own
responsibilities.
8|Page mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Module Responsibility
Inventory Inventory, Vision Operations(USA)
Payables Payables, Vision operations(USA)
Receivables Receivables, Vision Operations(USA)
Purchase Purchasing, Vision operations(USA)
According to the default setup vision operations is a client name.
Creating a New User (First Level)
Default user: - Operations. Like this we can also create our own user.
We can create a new user with name sample_user and Password 123456 with 2
Modules/Responsibilities.
For Creating the user first we need to login the default user operations/welcome.
Creating a new user need to select system administrator
Security User Define
After click on define a form will open.
9|Page mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
First enter the user Name then password and
after that if you click anywhere it will show you
to Renter the password.
Password Expiration.
Days: It Depend on every company they want you to change the password after how
many days. If we want a user password will expire after 30 days so we need to enter
30.
Accesses: In this we need to mention how many attempt we can use the same
password. Like we want a user can change after one login so we need to enter one.
Now click on Responsibility. Do not type in responsibility use LOV where we can use
LOV in oracle apps.
10 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
After selecting responsibilities Press OK.
And the last thing we need to save the form.
Logout the oracle apps and then login again with new user (SAMPLE_USER).
When we login the new user first thing we will need to change the password.
Now we are able to see two responsibilities which we added while creating new user.
Responsibilities
User Name
Types of Fields: -
In the above pictures where we creating the new user you have seen there are three
types of fields: -
1) Yellow Fields -- Mandatory
2) White Fields -- Optional
3) Grey Fields -- Read only
11 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Create a new user with name: - sample_user1
Confirmation Message After Save
When you enter any information in the form it will affect backend table and the data
is stored in the table.
How to check which table is get affected while creating the new user?
HELP Record History
Table Name: FND_USER
12 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
FND_USER Table is get affected while creating the new user you can also from the
SQL developer by using command:
Select * from fnd_user where user_name='sample_user1';
Primary Key is USER_ID. (FND STANDS FOR FOUNDATION)
Who Columns
WHO Columns: -All the important tables in oracle apps will have who columns and
they are used for debugging purpose: -
S.no Column Name Populated By
1 Creation_Date Sysdate
2 Created_By User_id
3 Last_Update_Date Sysdate
4 Last_Updated_By User_id
5 Last_Update_Login Session_id
With this we can easily track the details.
13 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Forms Query Mode: - Query mode is required to check the details of existing data.
Now we are going to search user created by us (SAMPLE_USER).
Open a Form and then we need to go on to the query mode we need to press F11
After query mode you will field convert into blue colour.
Search criteria is SAM%
Press CTRL + F11 you will get the result if you are not able to see your result then
press down arrow key to get your data.
Levels of Oracle App (Navigation Point of view): -
Level 1: -User Level
Level 2: -Responsibility Level
Level 3: -Menu Level
Level 4: -Form Level
14 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Responsibility
Forms
SUB - Menu
Menu
Relationship between Levels: -
1) Every User will have set of responsibilities
2) Each Responsibility will have a menu.
3) Each Menu will have forms. Menu is collection of Forms.
Indirect Responsibility
User Management Responsibility (Application Object Library Application) helps
system administrators to assign or un-assign a responsibility for multiple users at a
time. Yes, we don’t have to manage individual user accounts for assigning the
responsibilities if you are using User Management Application.
In Oracle Applications, Traditional method of assigning a responsibility to a user is
from System Administrator > Security > User > Define
This approach works well when we have limited number of users and responsibilities
which doesn’t change very often.
Consider a business scenario where you have 3 departments in your company namely
Finance with 10 people
Sales Dept. with 20 People
Purchasing with 10 People
15 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
For each person in Finance department you want to assign 5 different
responsibilities. In this scenario you end up going to User screen 5 different times and
then adding 5 responsibilities for each user. Basically you end up doing 25 tasks /
mouse clicks (assuming you are not using key board shortcuts :))
The same scenario can be implemented very easily with few steps if we go with User
Management Application.
Login With sysadmin user.
User Management Roles Categories.
Click on Update from right Side.
16 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Click on Add another row.
Enter code, meaning & Description
Click on Apply.
Go to Roles & Role Inheritance
Click on Create Role.
Select your category which we have created.
17 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Select Application.
Enter other Details also.
Click on Apply.
Go to System administrator responsibility.
Run the workflow background concurrent program & give process deferred as yes.
About concurrent program you will understand in the upcoming sessions.
18 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Now check the Roles which we have created.
Now in this we need to add responsibility.
Click on add node ( + )
Select the responsibility we want to add.
Press Select.
Check the role.
Add one more responsibility.
19 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Create new user like above we have created but without any responsibility.
Go to user management responsibility.
Go to Users.
Find your user
Click on update.
Click on assign Role.
Find your role.
Provide justification & Press Apply.
Go to system administrator responsibility & run the workflow background concurrent
program.
20 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Login with your user.
Check the user in the user form & go to indirect responsibility.
Securing Attributes
Securing attributes are used by Oracle Self-Service Web Applications to allow rows
(records) of data to be visible to specified users or responsibilities based on the
specific data (attribute values) contained in the row.
You may assign one or more values for any of the securing attributes assigned to the
user. If a securing attribute is assigned to both a responsibility and to a user, but the
user does not have a value for that securing attribute, no information is returned for
that attribute.
For example, to allow a user in the ADMIN responsibility to see rows containing a
CUSTOMER_ID value of 1000, assign the securing attribute of CUSTOMER_ID to the
ADMIN responsibility. Then give the user a security attribute CUSTOMER_ID value of
1000.
When the user logs into the Admin responsibility, the only customer data they have
access to has a CUSTOMER_ID value of 1000.
21 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Create Responsibility Level (Second Level)
Login With default user--Operations/welcome
Creating a new responsibility need to select system administrator
We can create a new responsibility with name sample_Resp for inventory module.
Good Practice to maintain
key as responsibility Name
22 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Click on LOV of Application: -
2) When you are creating your
1) Application is like responsibility sample_resp you need to
module needs to mention define which module you want to use so
which module we need to we use inventory module.
access.
Responsibility Key is same as Responsibility Name.
Then Select Data Group.
Multiple Reporting Currencies is specifically intended for use by organizations
that must regularly and routinely report their transactions and financial results
in multiple currencies otherwise you will select standard.
Each Responsibility will have a menu and mandatory to attach a menu in
responsibility. We are working in inventory module so we select INV_AVAILABILITY.
23 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
And the last thing we need to save the responsibility form.
When you enter any information in the form it will affect backend table and the data
is stored in the table.
How to check which table is get affected while creating the new responsibility?
HELP Record History
Help Record History sometimes show you the View. You can also conform by using
SQL Developer and enter the command select * from fnd_responsibility_vl; and right
click then click on open describe go to details tab.
24 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
General you will see the view name is end with VL, V or VM like this you can easily
guess that it is a view but you can also conform by using SQL Developer.
As a developer we always need to work on table not on views.
Select * from fnd_responsibility where responsibility_key ='SAMPLE_RESP';
Primary Key: - Responsibility_ID
You can also see who columns in this table also.
These FND table are called foundation tables. FND stands for foundation tables.
Just creating the responsibility is not complete your task you need attach the
responsibility to the existing user (sample_user1).
Attach responsibility to the user: -For attaching the responsibility to the user we
need to open a user form. Security User Define
And then go to the query mode by pressing F11. A search criterion is SAM%. Press
Ctrl+F11. Select Responsibility & search Sample_resp.
25 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Press Ok and SAVE the user.Login with SAMPLE_USER1
Now we are able to see three
responsibilities and in sample_resp
you will see directly form not the
menu name because it is custom
defined menu.
Now you are able to create a new responsibility and able to attach with user.
Create Menu Level (Third Level)
Menu is a collection of forms.
Login With default user--Operations/welcome
Creating a new menu need to select system administrator
26 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
We can create a new menu with name sample_menu for inventory module.
User Menu Name is same as Menu
SEQ: -As Per oracle apps standards SEQ number start with 10 and increment by 10.
Prompt: - Prompt is like label. Prompt should have meaning full name.
Function: -Every form is considered as function.
Form = Function. Whenever you see a function in oracle apps it means form.
You can select N number of functions but we select only 3.
After selecting functions SAVE the form. You will get a request Number.
27 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Our Menu Sample_Menu is created with three functions.
When you enter any information in the form it will affect backend table and the data
is stored in the table.
How to check which table is get affected while creating the new menu?
HELP Record History
Now again Help Record History shows you the View. Now we are able to easily guess
the table name. Table name is FND_MENU.
Select * from fnd_menus where menu_name='SAMPLE_MENU';
Primary Key: -Menu_ID
WHO Columns are also available in this table also.
28 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Just creating the menu is not complete your task you need attach the menu to the
new reasonability (SAMPLE_RESP1).
You know all the steps how to create a responsibility.
After creating responsibility we need to attach responsibility with our existing or new
user but we connect with our existing user (SAMPLE_USER1). Go to query mode and
then follow the process as we mention above.
Attaching New Responsibility
Login With existing User SAMPLE_USER1.
29 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
New Responsibility
attached
Sub-Menu: - A menu is having SUB-Menu.
For sub menu we need to create a menu with name sample_menu1 in this we need
to attach our old menu sample_menu and sample_menu is going to sub menu in
sample_menu1.
Attaching sample_menu as sub menu: -
30 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Save the menu form you get the request number.
Just creating the menu is not complete your task you need attach the menu to the
new responsibility (SAMPLE_RESP2).
You know all the steps how to create a responsibility.
After Creating Responsibility sample_resp2 we need to attach that responsibility to
our existing user (SAMPLE_USER1).
31 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Login the user sample_menu1
While creating menu sample_menu2 we add three forms (Tax status, % Discount
offered and 'A' Leads) and a sub menu. But in above screenshot only one form (tax
status) and sub menu is showing because some forms are invalid. It will disappear
when we open the user.
Now you are able to create Menu, Sub_menu, Responsibility and user and able to
connect all the levels of oracle apps.
32 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Concurrent Program
A Concurrent program is an instance of execution file with parameters and
incompatibilities.
In Concurrent program Execution File can be: -
1) PL/SQL Stored Procedure
2) Oracle report (RDF)
3) SQL Loader (ctl)
4) Java Program (.java)
5) Shell program (.sh)
6) SQL Script (.sql)
7) C or C++ Program (.c or .cpp)
Learn Concurrent program with PL/SQL Stored Procedure executable file.
Steps for run procedure through concurrent program.
Create a procedure Create a Concurrent Program Run
Create procedure in package to display Hello world: -
Create or replace package hello_world_pkg
Is
Procedure display (errbuf out varchar2,
retcode out number );
End;
/
Create or replace package body hello_world_pkg
Is
Procedure display (errbuf out varchar2,
retcode out number )
Is
Begin
fnd_file.put_line(fnd_file.output, 'Hello World');
End;
End;
/
33 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
https://selfonlinetraining.wordpress.com/cp-hello-world-pkg/
fnd_file.put_line (fnd_file.output,'----------'); ---- This statement is used to display the
output in oracle apps.
Errbuf and retcode are the mandatory parameters procedure should have to run for
concurrent program.
Errbufis used to return error message.
Retcode returns value- 0 for normal
1 for warning
2 for error
Concurrent program creation steps
1) Create Executable
2) Define the program
3) Find the Request Group
4) Place the program in the Request Group
5) Switch the responsibility and submit the Request
6) View Output
34 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Login with default user operations/welcome
1) Create Executable
Select system administrator
Concurrent Program Executable
Creating executable is just to point out which type of executable file you want to run.
Executable: - Name of the executable it can be any name.
Short Name: - is short name of executable or we can give the same name also.
Application: - Application is like module needs to mention which module we need to
access.
35 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Execution Method: - Need to mention the executable file type.
Execution file Name: - Mention the packagename.procedurename and Save the
form.
Table name: - FND_EXECUTABLES
Primary Key: -EXECUTABLE_ID
2) Define the program
Concurrent Program Define
Program: - Need to give program name.
Short Name: - is short name of program or we can give the same name also.
Application: - Application is like module needs to mention which module we need to
access.
36 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Executable Name: - is linking step one to step two. On step one we create executable
file (SAMPLE_HELLO_WORLD).Linking Happen trough Short Name.
Save the File.
Table name: - FND_Concurrent_programs
Primary Key: -Concurrent_program_id
3) Find the Request Group
Request Group: - Request Group is collection of concurrent programs, associated to a
responsibility.
After creating the program we need to attach the program with request group &
every pre-defined responsibility is having their own request group.
Security Responsibility Define
Same navigation for which we use to create a responsibility.
While creating executable and program we mention application as inventory so we
need to find the request group of inventory.
37 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Go to query mode with F11.
Search Criteria: - INV% then presses CTRL + F11 and then press down arrow to see the
inventory group.
Request Group
Request group of inventory is All Inclusive GUI.
4) Place the program in the Request Group
Security Responsibility Request
Go to Query Mode Press F11
Search Criteria (Case sensitive): - All%GUI
Press CTRL+F11
To add New
Concurrent Concurrent Programs
Program
38 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Press the new Button to add new concurrent program. After pressing the new button
you get the new row select the executable file name (EXEC_HELLO_WORLD).
Save the form and close this form.
Table name: - FND_REQUEST_GROUP_UNITS
Primary Key: -REQUEST_GROUP_ID
5) Switch the responsibility and submit the Request
After close the form you will get this screen and press the black button (Like Hat) to
switch the responsibility to inventory.
Switch Responsibility Button
39 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
After selecting the responsibility press ok.
Now you are in the (inventory, vision operations (USA)) responsibility.
Submit the request: -
40 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Press Submit a New Request: -
Select single request and press Ok.
Find Name of the execution file name: -
41 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Press Submit.
Press yes if you need to add another request. Else No.We Press No because we don't
want to create a new request.
Press Find: -
After Press Find Button you are able to see your executable file
(EXEC_HELLO_WORLD). Sometime phase is showing pending you need to wait for
some time or you can press Refresh Data button on the left side top of this form.
42 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Table name: - FND_CONCURRENT_REQUESTS Primary Key: -REQUEST_ ID
6) View Output
For view output press view output button on bottom right side of the form.
____________________________________________________________________
Concurrent program when submitted is called Request.
Every Request is identified by Request ID.
In the above 6th Step we click on view output to see the output.
We can click on view log to see the log of that executable file.
Every Request will have log file. Log file contains following details: -
1) Application
2) Program Name
3) Start time
4) End time
5) Log Messages: - As of now in below screenshot there are no log messages.
6) Request ID
7) Status of the request
43 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Concurrent Program to Display Data in LOG File
Create a concurrent program to display "Good Morning” in log file
Create or replace package G_M_PKG
Is
Procedure show (errbuf out varchar2,
retcode out number );
End;
/
Create or replace package body G_M_PKG
Is
Procedure show (errbuf out varchar2,
retcode out number )
Is
Begin
fnd_file.put_line (fnd_file.log, 'Good Morning');
End;
End;
/
https://selfonlinetraining.wordpress.com/cp-logs/
44 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
fnd_file.put_line (fnd_file.log, '----------'); ------ If we want to display message in logs
we need to write this statement.
After creating package with one procedure we need to make concurrent program.
For Concurrent program we need to do some steps like we do in above concurrent
program and for this also we need to some steps.
1) Create Executable
2) Define the program First Five Steps are same as we
3) Find the Request Group done in first concurrent program.
4) Place the program in the Request Group
5) Switch the responsibility and submit the Request
6) View Log.
1) Create Executable
45 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
2) Define the program
3) Find the Request Group
Request Group
4) Place the program in the Request Group
5) Switch the responsibility and Submit the Request
46 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
6) View Log
Message in logs
Create a concurrent program which accepts two numbers and display sum.
Create procedure in Package: -
For Above package code open below mention Link: -
https://selfonlinetraining.wordpress.com/cp-add-number/
47 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
1) Create Executable
2) Define the program : - In define the Program we need to attach values. In this
program you will learn about Value Set.
Attaching the values is having some steps
Step one is same of define the program. After creating Step one we need to press
parameters button.
Parameters Form: -
48 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Adding parameters
After adding Parameters
select value set .value set is
like selecting data type and
size of a variable. Select
10/Number
3) Find the Request Group
Request Group
49 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
4) Place the program in the Request Group
5) Switch the responsibility and Submit the Request
Switch the responsibly we already discussed. After select the execution file name new
window will pop up to enter the value.
Enter the any two values. Press Ok.
Press Submit.
50 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
In Parameters we enter 10 and 20. We can write any number in parameters.
Press No.
Press Find.
Press View Output.
Result
Now you are able to create a concurrent program which accepts value.
In the second step we attach the value set (10/Number) this value set is predefined
we can create our own value set for restricting user to enter the values from the list.
51 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Default Type
When it is required to default value of the parameter, the fields default type and
default values can be selected. The possible values of default type field are:
1) Constant
When Constant is selected in Default Type, any constant value can be entered in
Default value field.
Select Constant & give 1000 in Default Value field.
Run the Concurrent program
In the parameter window system will automatically take the default value.
2) SQL Statement
When SQL Statement is selected in Default type, any SQL statement can be written in
the default value field. SQL statement must return exactly one column in all cases.
52 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Select Default Type as SQL Statement.
In Default Value Enter select user_id from fnd_user where user_name =
‘OPERATIONS’
Run the Concurrent program
Now try with multiple columns
If you are using multiple columns system will pick the first column.
Now try with multiple rows.
53 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Now run the concurrent program
If you are using multiple rows system will pick the first value.
3) Segment
The default value is the value entered in a prior segment of the same parameter
window.
Don’t give any default value for A.
Select default type in B
Give default value as A
Run the concurrent program.
Press Tab
54 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Whatever value we will give to first parameter it will automatically assign to next
parameter.
4) Profile
Select profile as default type.
Enter USER_ID in default value.
Run the Concurrent program
System will pick the user_id from which user we are going to submit the request.
Display
If you uncheck this display button then the parameter will not display to the enter
user.
55 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Using Errbuf
Errbuf is used to get the error.
In this example we have created one plsql with two exceptions no_data_found &
too_many_rows.
In this example we are adding errbuf in exception session to get the error message.
We have defined one parameter also.
Errbuf
https://drive.google.com/open?id=1yEYWKae2mFjM36ROzKsgX84KDnbiZwaT
Create Executable.
56 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Define the program
Create parameter
Find the Request Group
Add the program to the request group.
Switch the Responsibility & submit the request.
Enter the wrong department number.
If we enter then no_data_found exception will get raised
Check the log file.
57 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Errbuf
Log Message
Using Errbuf & Retcode
We have updated our code & add retcode in our exception.
Recode numbers are 0 for normal, 1 for warning & 2 for error.
Retcode
If we enter wrong department number then no_data_found will get raised & we have
given retcode as 1.
If we enter correct department number then too_many_rows will get raised & we
have given retcode as 2.
https://drive.google.com/open?id=1RlwgrdPkPgksB0mssr1GKBrXnB01GtcR
Retcode value we can give in single courts also or without single court also.
Submit the concurrent program with the wrong department number.
58 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Concurrent program is coming with warning status because we have given retcode as
1 & 1 is for warning.
Not submit concurrent program with the correct department number.
Concurrent program is coming with error status because we have given retcode as 2
& 2 is for error.
Retcode is used to manage the status of the concurrent program
59 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Concurrent program with SQL * PLUS executable file
SQL * PLUS is used when we want to run multiple SQL Commands or PLSQL Block.
Step 1: Create a File with SQL Commands or a PLSQL with extension needs to be .SQL
We have given to simple select commands and save the file as emp_table.sql
Step 2: Transfer the file into the Server in SQL folder.
We have transfer the file into inventory module but in real time we need to transfer
the file into the custom module.
Step 3: Create Executable
Executable method needs to be SQL * PLUS.
60 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Step 4: Define the Program
Step 5: Find the Request Group
Step 6: Place the Program in Request Group
Step 7: Switch the responsibility and submit the Request
Click on View Output.
61 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
We can give the Page Title and Below Title in our output. Page Title and below title
we will get after every query get executed.
We just need to give Ttitle and Btitle
TTitle 'SQL Commands'
BTitle 'END Of the Report'
Transfer the file into the server in the sample folder and submit the concurrent
program. We don’t need to create a new concurrent program.
Automatically we get the current date also in our output.
62 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
We can change the page size also.
set pagesize 25;
Transfer the file into the server in the sample folder and submit the concurrent
program.
Value Set
63 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
As a name Suggest Value set is set of values.
Types of Value Set
1) Independent
2) Dependent
3) Table
4) None
5) Special
6) Pair
7) Translatable Independent
8) Translatable dependent
Independent: -
Create a concurrent program which accepts two numbers and display Sum.
A will accepts only 10,20,30,40 and B will accept only 1000,2000,3000,4000.
Step 1: - Create a value set: -
Login with default user operations/welcome
Select system administrator
Value Name for A: - SAMPLE_VAL_A
64 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Max Size
of data
type
Select Data type
Be Default Validation type is independent.
Types of value Set
Save the form. Now create for Value B.
Save the form.
Table Name:-FND_FLEX_VALUE_SETS
65 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Primary Key: -FLEX_VALUE_SET_ID
Step 2: - Include values into the value Set
Select system administrator
Application Validation Values
Form for Include values into the value Set.
Select Names we created in
step one. Select
SAMPLE_VAL_A then press
find.
After Press Find this window will open: -
Enter values which we select for sample_val_a
66 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Save the form. Now create same as for sample_val_b with values we select for
sample_val_b.
Save the form.
Table Name: - FND_FLEX_VALUES Primary Key: - FLEX_VALUE _ID
We already created package (add_num_pkg) with one procedure (calc)and
concurrent program
1) Create Executable we already created with name Sample_add_num.
2) Define the program we need to change the parameters forms in value set which
we created.
67 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
For Parameter a Select sample_val_a and for parameter b select sample_val_b.
Save the form.
Now we move to the step 5 of concurrent program because we are using the
concurrent program which we use in last example.
3) Find the Request Group
4) Place the program in the Request Group
5) Switch the responsibility and Submit the Request
Switch the responsibly we already discussed. After select the concurrent program
name new window will pop up with list of values.
Values of A: - Select value for A.
68 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Values of B: - Select value of B.
Press Submit.
Press No.
Press Find.
Click on view
output.
69 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Dependent: -
Create a concurrent program which accepts two parameters
State City
PB Amritsar Ludhiana
HR Hisar Kaithal
MH Mumbai Pune
IF we select PB as state then only Amritsar and Ludhiana not other cities will display
in List of values.
Step 1: -Create a value set for state.
Select system administrator
Application Validation Set
Validation Type of state is
independent
Step 2: - Include values into the value Set for state.
Select system administrator
Application Validation Values
Press Find
70 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Enter the
values
Create value set step one for City : -
Validation Type is dependent
for city. Press Edit Information
Select the independent value
set.
Enter one default
value for city.
Save the form.
Include values into the value Set for City: -
Select Lov from
independent
value
71 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Select HR and Press Find.
Values Of HR and Save the form
Enter the values of MH and PB with same process.
Values Of MH and Save the form
Values Of PB and Save the form
Create a package with a procedure: -
72 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
For Above Package code open below mention link: -
https://selfonlinetraining.wordpress.com/state-city-package/
1) Create Executable
2) Define the program
Click on parameters
73 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Enter 10 as SEQ and parameter as state
Select state value set
Save the form.
Save the form.
3) Find the Request Group
Request Group
4) Place the program in the Request Group
74 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
5) Switch the responsibility and Submit the Request
Switch the responsibility we already discussed.
City is disabled. when we
select state city is
automatically able to
select the values.
75 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Press Submit
Press No
Press Find
6. Click on View Output.
76 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Table
We use this value set to pick up the values from column of the table.
Step 1: -Create a value set for state.
Select system administrator
Application Validation Set
Press Edit Information.
Select table
Name
Value:- we need to select column name.
Meaning: -is like Description of the value.
Where/Order By is used for where and order by clause.
77 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Press Test and Save.
We are not going to make the new concurrent program we will use the concurrent
program which we created to add number.
1) Create Executable we already created with name Sample_add_num.
2) Define the program we need to change the parameters forms in value set which
we created.
Press Parameters.
78 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Select Table Value set for
A and save the program.
3) Find the Request Group
4) Place the program in the Request Group
5) Switch the responsibility and Submit the Request
Switch the responsibly we already discussed. After select the concurrent program
name new window will pop up with list of values.
Value
Meaning
Select the value of A from the LOV.
79 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Press Submit.
Press No.
Press Find.
Sometime phase is showing pending you need to wait for some time or you can press
Refresh Data button on the left side top of this form.
Click View Output.
80 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
None
None value set is restrict the user from minimum value to maximum value.
Step 1: -Create a value set for state.
Select system administrator
Application Validation Set
After selecting validation type as None we need to enter the Min value and Max
Value as per your requirement we enter Min value as 1 and Max value as 99 .
We are not going to make the new concurrent program we will use the concurrent
program which we created to add number.
1) Create Executable we already created with name Sample_add_num.
2) Define the program we need to change the parameters forms in value set which
we created.
81 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Press Parameters.
Change value set of B.
Select Table Value set for
B and save the program.
3) Find the Request Group
4) Place the program in the Request Group
5) Switch the responsibility and Submit the Request
Switch the responsibly we already discussed. After select the concurrent program
name new window will pop up with list of values.
82 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
We select the value of A as 1013707 and for B we enter the value as 99. IF you are
Trying to enter the value above 99 it will not accept we are not able to press the
submit button.
Press Submit.
Press No.
Press Find.
Sometime phase is showing pending you need to wait for some time or you can press
Refresh Data button on the left side top of this form.
Click on view output.
______________________________________________________________________
Pair and Special value sets are related to flex field constraint. We will do these two
types of value sets when we will do about Flex fields.
Translatable Independent and Translatable dependent are same as independent and
dependent but it translatable independent and translatable dependent is used when
we want to use oracle apps in multiple languages.
Phases of a concurrent program
83 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
1) Pending Phase: - When Processor is busy or memory is not available.
2) Running Phase: - When Program is in running stage.
3) Completed Phase: - When Program is completed.
4) Inactive Phase: - When there are problem with concurrent manager.
Status of a concurrent program
1) Normal State
2) Warning State: - Partial Failure and partial successful
3) Error State: - 100% Failure
4) No Manager State: -Problems with concurrent Manager
5) Scheduled State: - When we schedule the concurrent program.
Scheduled State: - We can schedule the concurrent program by using SRS Window
(Standard request submit).
We schedule the concurrent program when we are going to submit the request.
Types of scheduling: -
1) As soon as possible (Default)
Press Schedule button for more options
84 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
2) Once
Select date and time when you want
to run your concurrent program.
3) Periodically
Select Start and end date & time. Select from LOV after how many months, hours or
weeks you want to run your concurrent program.
85 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
4) On Specific days
Select Start & end date and time.
Select Date of every month or days of every week or we can select from both. Select
as per Client requirement.
Request Groups
Request Group is a collection of concurrent programs.
Standard (Predefined) responsibility comes with standard request groups. For custom
responsibility we need to create a request group without a request group a user is
not able to submit a concurrent program from the custom responsibility.
Security Responsibility Define
No Request Group for Custom Responsibility.
86 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Errors you get while submit a request without a request group from custom
responsibility.
Create a Request Group: -
Login with default user operations/welcome
Select system administrator
Security Responsibility Request
Add Concurrent Programs we created earlier.
Group: - Enter the new request group name.
Application: - Select Module.
Add Concurrent Programs as per requirements
Save the Request Group.
Table get affected while creating request group: -
Table Name: - FND_REQUEST_GROUPS
Primary Key: -Request_Group_Id
Table get affected while adding concurrent programs in request group: -
Table Name: - FND_REQUEST_GROUPS_UNITS
Primary Key: - Request_Group_Id
87 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Attaching request group with existing responsibility (SAMPLE_RESP).
Login with default user operations/welcome
Select system administrator
Security Responsibility Define
Go to Query Mode Press F11 and Find existing responsibility then press CRTL + F11.
Attach the created request group and save the form.
Login with User you created (SAMPLE_USER1) try to submit the concurrent program
request: -
Submit the Request
Now you are able to see the concurrent program which we have selected while
creating request group.
88 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Select the concurrent program you want to execute.
Press Submit
Press No
Press Find
__________________________________________________________________
Request Set
Request Set is also the collections of Concurrent programs.
Request set is shortcut process to submit more than one concurrent program in once.
Creating a request Set: -
Login with default user operations/welcome
Select system administrator
Requests Set
Click on Request Set Wizard.
While creating request set you will get some options: -
89 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Option 1: -
Sequentially (One after Another)
In Parallel (All At Once)
Select As Per client requirement.
Click Next.
Option 2: -
Abort Processing: - While processing the Concurrent Program is having error in
concurrent program is stop at that time.
Continue Processing: - While processing the Concurrent Program is having error in
concurrent program still continuing the process.
Select As Per client requirement.
Click Next.
Enter request set name and select
application.
Press Next.
90 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Press Next
Select concurrent program you want to attach with request set.
Press Finish.
Press Ok.
Set Code is same as Request_set_name.
91 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Table get affected while creating request set: -
Table Name: - FND_REQUEST_SETS
Primary Key: - Request_Set_Id
Request_set_name is same as Set code.
Switch the responsibility and Submit the Request
Switch the responsibly we already discussed.
Select Request Set
Concurrent Program which we Enter the parameters
select while creating request set.
92 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Press Submit.
Press No.
Press Find.
You will see request set name and in bracket you will get report set with this you can
easily find it is a single request or a request set.
Request id of request set is going to be parent id of the concurrent program which we
added in request set.
Difference between request group and request set: -
Request Group Request Set
Collection of Concurrent Program Collection of Concurrent Program
Used to Submission of Concurrent
Associate to responsibility
Program
93 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Incompatibilities
As Oracle apps is a multiuser environment multiple users can submit the same
program at once. When the concurrent program invoke the procedure which has
some DML operations. Deadlock may happen.
Deadlock will happen on running procedures with DML commands.
We can avoid deadlocks by making a concurrent program incompatible to itself.
Concurrent Program Define
We are not creating the new concurrent program we are going to use our existing
concurrent program.
Press Incompatibilities.
Select the concurrent program which you want to incompatible. we select the same
concurrent program. You can also select the other concurrent program or select
more than one.
94 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Save the form.
Create Concurrent Program using API
API stands for Application Program Interface.
Standard packages (predefined) are called API.
We created concurrent program through form (front end) now we are going to create
a concurrent program through predefined package (backend).
API is used to create a concurrent program.
For Creating Concurrent Program we need to create a procedure.
For Above Package code open below mention link: -
https://selfonlinetraining.wordpress.com/sample-package-api/
95 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
1) Registering the Executable from back end
Usually we create executable in the front-end, but this can be done from the
database tier i.e. back-end too.
Below is the PL/SQL code to create an executable from back-end.
We just need to change those things we add in frontend other options leave as same
For above executable code open below mention link: -
https://selfonlinetraining.wordpress.com/fnd-program-executable/
Query in the front-end to see whether your executable is created or not.
2) Registering the Concurrent program from back end.
96 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
For Above register program code open below mention link: -
https://selfonlinetraining.wordpress.com/fnd-program-register/
Query in the front-end to see whether your Concurrent program is created or not.
3) Find the Request Group
You need to done manually like we done in above concurrent programs.
4) Attaching the concurrent program to the request group.
For Above program Code open below mention link: -
https://selfonlinetraining.wordpress.com/add-to-group/
97 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Query in the front-end to see whether your Concurrent program is Attached to
Request Group or not.
5) Submitting Concurrent Program from Back-end.
For submitting concurrent program we need User_id, responsibility_id,
application_responsibility_id
For getting user_id: - select * from fnd_user where user_name='OPERATIONS';
User_ID is 1318.
But in real time we need to find our user_id from the fnd_user table.
For responsibility_id & application_responsibility_id: -
Select * from fnd_responsibility_tl where responsibility_name = 'Inventory';
Responsibility_id = 66002 and application_responsibility_id = 401.
Request ID
For Above program Code open below mention link: -
https://selfonlinetraining.wordpress.com/submit-concurrent-program/
After submitting the plsql block you will get the request id. Find the request id from
front end form.
6) Click View Output.
Now you are able to create concurrent program from backend process.
_____________________________________________________________________
Creating Calendar
98 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
For upcoming modules we need calendar. Most of the time you get the calendar in
your oracle apps if you don't have calendar follow the below mention steps.
Create a calendar to define an accounting year and the periods it contains. You
should set up one year at a time, specifying the types of accounting periods to
include in each year. Defining one year at a time helps you be more accurate and
reduces the amount of period maintenance you must do at the start of each
accounting period. You should define your calendar at least one year before your
current fiscal year.
Login with Default user
Select General Ledger, Vision Operations (USA)
Setup ---> Financials ----> Calendars ---> Accounting
Accounting Calendar Form will open
Press F11for Search mode
Search criteria -- Acc% Press - Ctrl + F11
Add Button
Press Add button for add new month but your cursor on the last month (Adj) of the
last year.
In oracle apps we need to create 13 months in year 12 months we already have and
the 13th month as an adjusting period.
99 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Enter the range of dates (From and to) when the accounting period begins and ends.
Use the date format DD-MON-YYYY (for example, 01-OCT-2018).
If you choose to make an accounting period an Adjusting period, it can overlap the
dates of other accounting periods. For example, you can define a period called DEC-
18 that includes 01-DEC-2018 through 31-DEC-2018. You can also define an adjusting
period called DEC31-18 that includes only one day: 31-DEC-2018 through 31-DEC-
2018. Both your adjusting and non-adjusting periods should have the period type
associated with your set of books.
Adjusting Period
Always select adjusting period. For every Year you create the calendar.
Calendar should be on sequence like you have the calendar till adjusting period 2017
and now you need to work on March 2019. You need to create the calendar for 2018
also. You cannot directly for 2019 it will give you the error.
After creating every year -- Save it.
Run the concurrent program.
View --- Request --- Submit new Request -- Single Request --
Name - Other - Calendar Validation Report
Under Parameters - Accounting
100 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
OK -- Submit –Submit another Request -- No -- Find
Program should be Complete and Normal.
Inventory Module
This module deals with items/Stock of organizations.
Step 1.Create an item
Login with default user.
Select Inventory Vision Operations (USA)
Items Master Items
Select V1 as Organisation.
101 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Enter the Name of Item you want to create and the description of the item.
For every item we need to add the properties from all the tabs.
Total we have 17 tabs.
As a technical consultant we don't need to worry about properties. This details you
will get from the functional consultant which property you need to select for the
item. For this example we will select only three properties from inventory tab.
Select Inventory Item, Stockable and Transactable from inventory tab.
Save the form and don't close this form.
Base Table: - mtl_system_items_b
Composite Primary Key:-Inventory_item_id & Organization_id
102 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Samsung Galaxy S8 is in segment 1 column.
Step 2.Assign Item to child organizations
Assign item to child organization means Samsung create model Samsung s8 but they
want to sell this phone in some countries not in the whole world so they want to add
this items on those countries software where they want to sell this mobile model.
So we select only four countries. Total we select 5 org one on company level and 4
county levels.
Save the Form.
Base Table: - mtl_system_items_b
Composite Primary Key: -Inventory_item_id & Organization_id
Step 3.Check for Accounting period
103 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Accounting close cycle Inventory Accounting Period
For Current month you want to work and all the previous months are on open status
otherwise you will get error.
For change status Press Change Status and press save.
Step 4.Raise on hand quantity
Transactions Miscellaneous Transactions
Select Transaction type Miscellaneous Receipt
Press Transaction Lines.
Find the item we have created in step 1.
104 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Select Sub inventory as per the requirement. We Select Mobile B.
Scroll the form to the right enter the quantity to 1000.
Base Table: - mtl_material_transactions
Primary Key: -Transaction_id
Step 5.Sub Inventory Transfer
Transaction Type: - Sub inventory Transfer
105 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Press Transaction Lines.
Select Item Samsung Galaxy S8. Sub inventory we select Mobile B in second step so
select Sub inventory as Mobile B .To Subinv as Stores.
Scroll to right & enter the quantity you want to transfer to stores from mobile b.
Save the Form.
Base Table: - mtl_material_transactions
Primary Key: - Transaction_id
Step 6.Check for on hand Quantity
On hand Availability on hand Quantity
106 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Select the Item.
Press Find.
Now you are table to create item in Inventory Module.
Templates: -Templates are the collections of properties. Used to create item quickly.
While creating item we need to select properties if we go to every tab it will take so
much of time so we can create template with this we can easily select properties that
we want to attach in item as per the customer requirement.
Setup Items Templates
Press New.
107 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Enter the Name for the template and select the Values you want to add into the item
as per the client requirement.For this example we again select 3 values.
Save the Template.
Base table - mtl_item_templates
Primary key:-Template_id
Attach the Template with Item
For attaching the template we need to create one more item.
Items Master Items
Select V1 as Organisation.
Enter the Name of the Item and description.
108 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Go to tools Press Copy from.
Find the custom template.
Press Done.
Save the Form.
You will able to see the values you select while creating template are applied in item.
109 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
AP Module (Payables) & PO Module ( Purchasing )
This module deals with payments of an organisation.
The pre-requisite for this module, is having a supplier.
Step 1.Create a Supplier
Select Purchasing vision Operations (USA)
Supplier Base Supplier
Press Create Supplier.
Enter Organization Name , Alias & Press Apply
Automatically you will get Supplier Number.
110 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Step 2
Base Table: ap_suppliers & hz_parties
Primary Key: Vendor_ID & party_id
HZ stands for human zone.
Step 2.Create Supplier Site
Press Address Book
Press Create.
Select Country.
Enter the address.
Select Address Purpose.
Check Purchasing & payment.
Don’t check RFQ Only. If you select you are not able to create purchase order for
supplier.
111 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Press Continue.
Press Continue.
Select Site Name & Press Apply.
Base Table: ap_supplier_sites_all & hz_party_sites
Primary Key: vendor_site_id & party_site_id
112 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Like this create one more supplier
Click on address book.
113 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Step 3.Create Supplier List
Go to supplier base supplier lists
Enter list name & select your supplier which we have created
Table Name : - po_vendor_list_headers & PO_VENDOR_LIST_ENTRIES
Step 4.Create Requisition
A requisition is used in business as an internal document used to notify stores or the
purchasing department of items that are needed or need to be ordered. It sets out
the quantity and the time frame.
Go to Requisitions Requisitions
114 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Enter the details of item which we want from supplier.
Scroll to right side.
Enter quantity, estimated price & needed date.
Save the form & note down the requisition number.
Click on approve
115 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Press ok.
Table name: po_requisition_headers_all & po_requisition_LINES_all
Step 5.Check the status of requisition
We need to check the status of requisition for approval, pending or rejected.
Go to Requisitions Requisitions Summary
Enter your requisition number & press find.
116 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Step 6. Request for Quotation
Go to Auto create.
Enter requisition number & press find.
117 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Change document type to RFQ & Select your line.
Press Automatic
Select RDQ type & press create.
Note down the REQ number.
Click on terms & select your terms
118 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Click on suppliers
Click on add from list.
Select your list which we have created in above step.
Press apply.
Change the status to active
119 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Save the form.
Step 7.Submit concurrent program
We need to submit the concurrent program (Printed RFQ Report).
This report we will send to our suppliers.
Click on view output.
Now check your REQ status changed to printed
Table name : po_headers_all & PO_RFQ_VENDORS
120 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Step 8. Create Quotation.
On the same page of REQ Click on tools from special menu
Click on copy document
Do this for both the suppliers.
Now the Quotation number for both the supplier.
121 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Do changes as per supplier quotation.
For practice we will only changes the price.
Click on price breaks.
Increase the price.
Change the status to active & press save.
Do the same thing for another supplier & enter the details in quotation form as per
the supplier.
122 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Save the form.
No Separate table to quotation.
123 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Step 9. Quote Analysis
Go to RFQ's and Quotations Quote Analysis
Enter RFQ number & press find.
Check both the quotation & approve the quotation.
124 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Select the quotation & press approve quotation.
Select type & reason.
125 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Step 10.Create Purchase order
Again Go to Auto create.
Find your requisition
Select line & select document type as standard PO.
Press Automatic.
Press Create.
Select your supplier & save the form
126 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Note down your PO number & press approve.
Table name : po_headers_all & po_lines_all
Step 11. Check the status of PO
Go to Purchase Orders Purchase Order Summary
127 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Enter PO number & press find.
Step 12.Check for Accounting period
Switch responsibility to Payables, Vision Operations (USA)
Accounting Control Payables Periods
If Current Month is not open.
128 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Select Open from Lov for Current month or the previous months if they are not open
and Press Ok.
Save the form.
Step 13.Create Invoice
Invoices Entry Invoices
Enter Po number & press tab button.
Enter Invoice date --- Select Current Data for Practice, Invoice Num -- Enter any
number for Practice, Amount.
129 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Save the Form.
Base table - ap_invoices_all
Primary key - Invoice_id
Don't Close this from.
Step 14.Create Invoice Lines
Go to the Lines Tab.
Lines Means need to give the description of the amount of the invoice/ bill.
Enter the amount.
130 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Select any distribution account.
Scroll to right side.
Select PO line & PO shipment.
Save the Form
Base table: ap_invoice_lines_all
Primary Key: Invoice_id
Step 15.Validate the Invoice
Go to General Tab.
131 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Status is Never Validated.
For Validation Press Actions...1 Validate
Select Validate & Press OK.
Step 16. Make the Payment
Again Press Action 1
Select Pay In full & press Ok.
132 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Enter Bank Details, Payment type, Document Type.
Press Save.
Click on invoice overview
Base table: ap_invoice_payments_all / AP_CHECKS_ALL / ap_payment_schedules_all
Primary Key: Invoice_paymnent_id
133 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Split Payment
In the above example we have select pay in full because we want to make the full
payment but we can split the payment also in 2 or 3 instalments.
Create new invoice with your supplier
Create lines
Save this form & validate the invoice
Go to scheduled payments tab.
We are getting the due date & gross amount.
Change the gross amount to 5000 & change the due date.
Select the second line & select the due date & enter the 5000 as gross amount.
134 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Press pay… 1
You will redirect to payment page.
Press save.
Click on invoice overview.
Payment is partially paid.
135 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Pre-payment
Prepayment is when we do some advance payment to supplier.
Open the invoice page & select type as prepayment.
Select the trading partner & other details.
Enter invoice num & enter amount as USD 1000.
Go to Lines
System will automatically distribution account.
Enter the amount as 1000.
Go to general Tab & validate the invoice.
Status will be updated as Unpaid.
Go to actions 1 button & pay in full.
Make the payment of USD 1000.
136 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
After some days we got the material from supplier & the total amount is USD 10000.
We have already done 1000 as advance payment it means we need to pay 9000.
Open the invoice page & select your supplier.
Enter invoice number & enter full amount as 10000.
Create lines
Go to View prepayment application.
We will get nothing.
Go to general tab & press actions 1 button.
137 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Check Apply/Unapply prepayment.
Press ok.
Check apply & prepayment on invoice.
If we want to apply less prepayment amount to apply on this invoice just remove the
1000 to amount you want to apply.
We are selecting full amount.
Press apply.
138 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Go to view prepayment application tab.
Go to Lines
One more lines get added as prepayment & amount is 1000.
Net amount is USD 9000.
Now change the invoice amount to 9000.
Save the form.
Validate the form.
Make the payment in Full or if you want to split the payment you can do that also.
Tables: ap_prepay_history_all , ap_prepay_app_dists
Points to Remember
1) You cannot partially pay a prepayment. You must fully pay it.
2) You can apply prepay on the invoice type is Standard, Mixed, or Expense
Report.
3) You can apply only Item distributions from the prepayment.
4) You must fully pay a prepayment before you can apply the prepayment to an
invoice.
5) Supplier, invoice currency and payment currency must be same on prepay and
to invoice.
139 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
AR Module (Receivables)
This module deals with Receipts of organisation.
The pre-requisite for this module is having a customer.
Step 1.Create a Customer & Bill to and ship to locations
Responsibility: Order Management Super User, Vision Operations (USA)
Press create.
Enter organization name & scroll down to enter the organization address.
140 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Enter the address & scroll down to select the location purpose.
Select bill to first location and press save and add details to create ship to location.
Press Create Site.
Scroll down to select site purpose.
Press Finish.
141 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Press Save.
Base Table for new Organizations: - hz_parties & hz_cust_accounts
Primary Key: - Party_id & Cust_Account_id
Base Table for sites: - hz_party_sites & hz_cust_acct_sites_all
Primary Key: - party_side_id & cust_acc_side_id
Step 2.Create a transaction
Select receivables, Vision operations (USA)
Source - Manual
Class / Type - Invoice
142 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Select customer Ship to & Bill to locations which we created in first step.
Save the form& don't close the form.
Transaction Number
Save the transaction number:- 12107
Base table: -ra_customer_trx_all
Primary key: - customer_trx_id
Step 3.Create transaction lines
Press Line items Button.
Select any two items from the list. Need to enter the quantity & price. For practice
you can give any quantity & price.
Press Save& Close the only lines form.
143 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Step 4.Complete the transaction
Click on Complete.
Complete button change to incomplete. But if you get the GL account error like below
mention screenshot flow the steps to rectify. After rectify the error press complete
button.
Press GL Account.
144 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
You will see there is no department select Department & press Ok.
Press Save & Do the same steps for all the lines you are creating.
Press Complete after selecting department for every lines.
For next Step we need transaction number: - 12107
145 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Step 5.Create a receipt
Receipt Receipts
Select Receipt Method
Enter Receipt Number
Enter Receipt Amount
Select Transaction Number & Press Apply
Select Transaction Number
Press Save.
146 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Customer Pre-payment
Open Transaction page.
Select class & type as deposit.
Select customer only in bill to.
Go to Commitment Tab.
Enter the amount which we have received from customer as advance payment &
select description as generic commitment.
Complete the transaction
If you get GL Error go to distributions button & select any department which we have
done in the above example.
Create the Receipt of the amount which we have received.
Click on apply.
147 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Save the form.
After some days we have given delivery to the customer & we have received the full
payment.
Create the new transaction.
Create the Lines
Save the form.
Select the commitment we will get the advance payment transaction id.
Save the form & complete the form.
Press refresh.
148 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Click on details
We don’t need to create receipt because customer has made the full payment in
advance if he didn’t made the full payment in advance then for the pending amount
received we need to create the receipt.
Task: Try Spilt Payment from the customers.
We just need to create the receipt in two instalments.
149 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Order management Module
This module responsibility accepting the order from the customer ends with delivery
of the goods.
Step 1.Create a Customer & Bill to and ship to locations
We already created Samsung as a customer in AR Module.
Like this for this module we created Reliance Digital
Responsibility: Order Management Super User, Vision Operations (USA)
Customers Standard
Step 2.Create an Item
We already created some Items in Inventory Module.
Switch to Inventory, Vision Operations (USA)
Items Master Items
Select Organization: - V1
Creating two items ANDROID_WATCH&DIGITAL_CAM with Finished Good template.
Finished Good is predefined template.
150 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Step 3.Raise on hand quantity
Transactions Miscellaneous Transactions
Select Transaction type Miscellaneous Receipt
Press Transaction Lines.
Enter the on hand Quantity for both the items & select any account.
Save the form.
Step 4.Add Item to the pricelist.
Switch to Oracle Pricing Manager, Global
Pricelist Add item to the pricelist
Pricelist: - Corporate
Low & High: - ANDROID_WATCH
Press Ok & Submit.
151 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Repeat for DIGITAL_CAM
Step 5.Define the price
Pricelist Adjust pricelist
Select the pricelist & item.
Adjust By: - Amount
Enter the value of the product & Press Submit.
Repeat for DIGITAL_CAM
Base Table: QP_LIST_LINES & QP_pricing_attributes
152 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Step 6.Create Sales Order
Switch to Order Management Super User, Vision Operations (USA)
Orders Returns Sales Orders
Select the customer &Order Type - Standard
Go to other Tab.
Select warehouse: - V1
Save the form.
Base table: - oe_order_headers_all
Primary Key: - Header_id
Step 7.Create Sales Order Lines
Go to Lines tab
Select Item & enter the Quantity for Sales Order.
Press Save.
153 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Step 8.Book the order
Click on Book Order.
Status is changed to Booked.
Step 9.Pick Release the Sales Order
Shipping Transactions
Enter the order number &Press Find.
154 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Select the both line of order
Select Action (Launch pick release) & Press Go.
Pick slip report is generated.
Step 10.Ship Confirm
Shipping Transactions
Enter the order number & Press Find.
Select the both line of order.
Select Action (Auto-Create Deliveries).
Press Go.
155 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Go to Delivery tab.
Select Ship Confirm from action & press Go.
Select Ship Method.
Press OK.
156 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Reports
Reports are used to build the reports in oracle apps. Reports are also used to present
the data for read only purpose.
Reports are files with extension (.rdf).
RDF stands for report definition file.
We can develop the reports in two ways.
Wizard
Manually
Two important components in reports.
Data Model editor -- Used to create reports manually.
Layout editor -- Used to design the report.
Creating reports using wizard
Let us learn how to create a report using wizard.
Let’s us learn the first example to develop the report based on EMP table.
Open the report builder tool Welcome to the Report Builder Select Use the
report wizard OK Welcome to the report wizard Next Create both Web
and Paper Layout Next Title -- EMP Details , Choose the report style as
Tabular Next SQL Query Next Data Source Definition -- Select * from
emp; Next provide the username, password and database details Connect
Move all the fields from Available Fields to Displayed Fields Next Next Next
Select No template (Oracle apps does not support templates) Next Finish.
This is how your report looks like.
157 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Save the report with the name EMP.rdf
Go to Tools Object Navigator (Shortcut F5)
Double click on the Data Model.
Data Model of the report is displayed.
In the data model, we can see the query.
Query results in group.
Group is collection of columns.
In our report, the name of the query is Q_1.
The name of the group is G_EMPNO.
Observer the group contains columns like EMPNO, ENAME, JOB, MGR etc.
Double click on query Q_1, the query used in developing the report is displayed.
158 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
In the Object Navigator, when we expand the Data Model, we can see the Query,
Group and list of the columns in the group.
In the Object Navigator, expand the Paper Layout.
It contains three sections.
1) Header Section
2) Main Section
3) Trailer Section
Expand the Header Section, there is nothing found.
Expand the Trailer Section, there is nothing found.
Expand the Main section;we can see Body and Margin.
Expand Body, we will find M_G_EMPNO_GRPFR. It is called as Group Frame.
It groups all the objects belong the query. Hence it is called Group Frame. It is the
159 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
first frame that is created by the wizard. The name of the frame always ends with
GRPFR (stands for Group Frame).
Under Group frame we find Header frame.
This frame name ends with _HDR.
This frame is used to group the column headings of the report.
The next object is repeating frame. We can identify the repeating frame in two ways.
Repeating alwayshas an arrow on the frame.
The name of the repeating frame always starts with R_
repeating frame represents a group in the query.
There is one-to-one relationship between group and repeating frame.
The last objects are field objects. These objects reside inside the repeating frame.
Field objects display database columns. The field names start with F_ . The fields
must be displayed in the repeating frame that corresponds to the group they are in.
When we are creating the layout manually from the scratch we have to be very
careful that the fields are placed in the correct repeating frames.
160 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Let us look at another similar example.
Let’s us create another report using wizard based on DEPT table
Open the report builder tool
File New Report Use the report Wizard OK Next Create both Web and
Paper Layout Next Title -- Dept Details Style -- Tabular SQL Query Next
Data Source Definition -- Select * from dept Next Enter username, password
and database details Connect move all fields from Available fields to displayed
fields Next Next Next Select No template Next Finish.
This is how our report looks like.
Save the report with the name DEPT.rdf
Similar to our previous example, in the Object Navigator ( F5) , double click on the
Data Model, we find query, query results in group. Group contains fields.
In the Object Navigator, expand paper layout, under main section, we find Group
frame, Header frame and Repeating frame. Repeating frame contains fields. This is
same as our previous example.
161 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
In the Object Navigator, select Paper Layout, Go to Tools à Report Editor, we get the
paper layout of the report. This layout is designed by the wizard.
Lets us understand the layout designed by the wizard.
Select all the fields (ctrl + A) move the layout to the centre by using arrow keys in the
keyboard.
Click on any field and select the outer most frame by clicking "select parent
frame" button(two times)
expand outermost frame to all the directions by using mouse.
162 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Group frame (Outer frame) Select Parent Frame
Select column heading loc , select its parent frame by clicking "Select parent frame"
button.
Click on Flex Off button.
Move the header frame towards up (by using arrow keys).
Group Frame Header Frame
Flex Off
Click on LOC field, select its parent frame and expand to all directions by using
mouse.
Repeating
Frame
163 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Select LOC field and move the field towards right by using arrow key.
Select DEPTNO field and move the field towards left by using arrow key.
Fields
By looking at the above layout, we can understand arrangement of frames done by
the wizard.
All the fields in the repeating frame will be repeated for every row in the query.
The changes which we have done to the layout are just for our understanding
purpose.
Save the changes (File Save)
run the report (Program Run Paper Layout)
Look at the output below
164 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Developing the report manually
Let us learn how to develop the report manually.
Open the Report Builder tool Select Build new Report manually OK
We will be directed to Data Model of the report.
Click on SQL and Click on Work Area. We get SQL Query Statement window.
Provide the Query Select * from dept OK
Provide the connection Details, User name, password and the Database OK
This is how the Data Model looks like.
We have Query i.e. (Q_1), it results in Group (G_DEPTNO).
Group is collection of columns.
165 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Paper Layout
Button
We are done with Data Model. Close the Data Model.
We need to design the report in layout model.
In Object Navigator Double click on Paper Layout or you can also click on paper
layout button on data model form.
This is how Paper Layout looks like
Click on Frame Draw the frame in the work area.
Right Click on the frame Property Inspector Provide the following property
166 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
General Information
Name -- M_GRPFR
General Layout
Vertical Elasticity -- Expand
Create another frame inside M_GRPFR
Right Click on the inner frame Property Inspector Provide the following
property
Name -- M_HDR
Click on Text and place three text_items (Deptno, Dname and loc) in the frame
M_HDR.
167 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Create a repeating frame below the header frame.
Place three fields in the repeating frame.
Right click on the Repeating Frame Property Inspector Provide the following
property.
Repeating Frame
Source -- G_DEPTNO (It is the name of the group in data model).
Similarly, provide the source for the fields.
Right click on the Field (F_1) Property Inspector Provide the following property.
Field
Source -- DEPTNO (It is the column name).
Right click on the Field (F_2) Property Inspector Provide the following property.
168 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Field
Source -- DNAME (It is the column name).
Right click on the Field (F_3) Property Inspector Provide the following property.
Field
Source -- LOC (It is the column name).
Right click on the Field (F_3) Property Inspector Provide the following property.
File Save as DEPT_MANUAL.rdf
In the Object Navigator, Program Compile All
Program Run Paper Layout
We are done developing the report manually.
In the Object Navigator, we can see the all the frames, repeating frames, text and
fields which we have created.
169 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Summary Column
Summary columns are used to perform aggregate functions like sum ( ), max ( ),
Min ( ), count ( ) etc;
Lets us understand with an example
I want to create a report which display Empno, Ename and Sal of the employees
working in deptno =10
I also want to display total sal.
Empno Ename Sal
7782 Clark 2450
7839 King 5000
7934 Miller 1300
Total 8750
To get the value 8750, we need to use Summary column.
Let’s us develop the report using wizard
Open report builder tool Use the report wizard Ok Next Create both Web
and Paper layout Next Tabular SQL Query select empno, ename, sal from
emp where deptno =10; Connect à provide connection details Next Move all
the fields from Available Fields to Displayed Fields Next Select sal from available
Fields , click on sum
Next Next No template Next Finish
You will get the following output
170 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
The value 8750 is the result of the summary column.
Let’s us save the report.
File Save as File Name: SUMMARY_WIZARD
Save as Type: Report Binary (*.rdf)
Let’s us look more in detail, about the summary column.
Open the Data Model of the report.
In the Data Model, Summary Column is created by the wizard.
Double click on the Summary Column; you will get the following information
Name SumsalPerReport
Column Type Summary ( Indicates that it is a Summary Column )
Function Sum
Source Sal
171 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
The above information helps us to understand that it is calculating function sum ( ) on
column sal.
Now, let’s understand the layout of the report.
Select Paper Layout
Select all frames by using Ctrl+A, and move the frames to centre.
Select outermost parent frame, by using select parent frame icon.
Drag the four corners and make the frame bigger.
Right click on in the frame, and make the flex to off.
For better understanding of the layout, give spacing between the frames.
Let us extend all the frames, for better understanding of the layout.
Field
Text
Frame
172 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Observe the source for the field F_SumsalPerReport, it is SumsalPerReport( Summary
Column Name )
As we have now analysed the Data Model and layout Model of the report, we will try
to develop the same report manually in the next example.
Developing the above report manually
Open Report Builder Build a new report manually Ok
You will be directed to Data Model of the report.
Click on SQL and click on Work Area. We get SQL Query Statement window.
Provide the following query
select empno, ename, sal fromemp where deptno =10; OK
Provide the connection Details, User name, password and the Database OK
This is how the Data Model looks like.
173 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Click on summary column and click on work area.
A Summary Column is created.
Observe, the name of the Summary column is CS_1
Double click on CS_1, property Inspector window is opened.
In property inspector, Column Type is Summary.
It means that it is a Summary Column.
Function - Sum (As we need to calculate total)
Change the source to sal, as we want calculate total of sal.
We are done with Data Model. Close the Data Model.
We need to design the report in layout model.
In Object Navigator Double click on Paper Layout
Click on Frame Draw the frame in the work area.
Right Click on the frame Property Inspector Provide the following property
General Layout
Vertical Elasticity -- Expand
174 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Place a frame inside the frame.
Click on text, place three text items (Empno, Ename, sal) in the inner frame.
Place a repeating frame below the header frame.
Place three fields inside the repeating frame.
175 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Provide the source for the repeating frame.
Right click on the Repeating Frame Property Inspector
Repeating Frame
Source -- G_Empno ( It is the name of the group in data model ).
Similarly, provide the source for the fields.
Right click on the Field (F_1) Property Inspector
Field
Source -- Empno ( It is the column name ).
Right click on the Field (F_2) Property Inspector
Field
Source -- Ename ( It is the column name ).
Right click on the Field (F_3) Property Inspector
Field
Source -- Sal (It is the column name).
Place another field below F_3
Place a text before the field as total sal.
176 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
We want the field F_4 to display total Sal.
We need to provide the source to the field F_4
Right click on the field F_4 property inspector
Source -- CS_1 (It the name of the Summary Column)
File à Save as SUMMARY_MANUAL.rdf
In the Object Navigator, Program Compile All
Program Run Paper Layout
We are now able to get sum of sal.
So, Summary column is used to calculate aggregate functions.
Formula Column
Formula column is used to perform calculations.
Let us understand it with an example
I want to create a report which will display the following columns
Empno Ename Sal Annual_sal
7369 SMITH 800 9600
7499 ALLEN 1600 19200
7521 WARD 1250 15000
As we know that Empno, Ename and Sal are available from the EMP table.
For the 4th column ieAnnual_sal, we need to perform calculation.
For Annual_sal, we use Formula column
Open report builder tool Build a new report manually OK
You will be directed to data model of the report.
Click on SQL click on work area We get SQL Query statement window
Provide the query select empno, ename, sal from emp OK
177 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Provide connection details, User Name, Password and Database Connect
Now, our Data Model contains Query (Q_1 )
Query will result in Group ( G_empno ) and Group is collection of columns.
Click on Formula Column icon and click inside the group.
Observe Formula Column (CF_1) is create in the group.
178 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Right click on the formula column Property Inspector
Double click on PL/SQL Formula column, provide the following code
function CF_1Formula return Number is
begin
return :sal*12;
end;
Click on compile. We get message Successfully Compiled.
In the above code :sal refers to the column in the data model. We need to prefix with
: ( colon )
Close MODULE1: Program Unit - CF_1FORMULA window.
Close property inspector.
We are done with Data Model.
Now, lets us design the layout of the report.
Click on Paper Layout symbol
179 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
We will be directed to the paper layout, design the report using Frame, Repeating
Frames, Text and Fields (similar to the example of develop the reports manually).
As we know, we need to provide the source for the repeating frame and the fields.
For the repeating frame, we need to provide source as group ( G_empno ).
For the fields, we need to provide the source as column name.
For the last field ( F_4 ), provide the source as CF_1, as it needs to get the data from
the formula column.
180 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
So, use Formula Column, when you need to perform calculations.
Place holder Column
Place Holder column is a variable.
So, we can store value in Place Holder column.
Code for assigning value to the place holder column, has to be provided in formula
column.
Place holder column can be placed in report layout.
To understand place holder column, practice all the examples.
Ex1:
Create a report manually based on the following query
select * from emp;
This is how the Data Model looks like
We have query ( Q_1 ).
Query results in a group G_EMPNO
Group is collection of columns.
181 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Place a place holder column and formula column in the group.
Rename the place holder column to p1 ( By using property inspector )
Rename the formula column to bonus ( By using property inspector )
Right click on the formula column PL/SQL Editor
Provide the following code
182 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
functionbonusFormula return Number is
net number(16,2);
b number(14,2);
begin
net := :sal+ nvl ( :comm, 0);
:p1 := round ( net *.2);
if :job ='CLERK' then
b := :p1 + 1000;
elsif :job='SALESMAN' then
b := :p1+2000;
elsif :job ='MANAGER' then
b := :p1+3000;
else
b := :p1+5000;
end if;
return (b);
end;
click on compile.
You should get a message "Successfully compiled"
Click on close.
In the above code, we are using place holder column ( p1 )
We are initializing place holder column by using a formula column.
Analyze the above logic.
Formula column bonus, is calculation bonus of the employees.
net and b are local variables.
20% of net salary, is stored in p1 ( p1 is a place holder column )
When initializing place holder column, we need to prefix with : ( colon )
If the employee is CLERK,
bonus is 20% of net salary + 1000
If the employee is SALESMAN,
bonus is 20% of net salary + 2000
If the employee is MANAGER,
bonus is 20% of net salary + 3000
for other jobs,
bonus is 20% of net salary + 5000
183 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
In the Data Model, total no of column in the group G_EMPNO is ten.
Eight columns are returned by the query.
One column is a place holder column.
One column is a formula column.
Invoke the report wizard by click on Tools Report Wizard Create both Web and
Paper Layout Select Tabular SQL Query Data source definition : select * from
emp ( Query is present ) Move all the fields from Available Fields to Displayed
Fields ( Observer we have 10 fields ) Next Next Select No template Finish
In the output, we have total ten fields.
The last two fields are p1 and bonus.
P1 , is a place holder column.
Bonus, is a formula column.
So, we can have the place holder column in the output.
Save the report PLACE_HOLDER_ONE.rdf
Let’s us try the 2nd example
Ex 2:
Create a report manually based on the following query
select * from emp;
This is how the Data Model looks like
We have query ( Q_1 ).
Query results in a group G_EMPNO
Group is collection of columns.
184 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Place a formula column inside the group.
Rename the formula column to bonus ( By using property inspector )
Right click on the formula column PL/SQL Editor
Provide the following code
function bonusFormula return Number is
net number(16,2);
b number(14,2);
p1 number(14, 2);
begin
net := :sal+ nvl ( :comm, 0);
p1 := round ( net *.2);
if :job ='CLERK' then
b := p1 + 1000;
elsif :job='SALESMAN' then
b := p1+2000;
185 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
elsif :job ='MANAGER' then
b := p1+3000;
else
b := p1+5000;
end if;
return (b);
end;
click on compile.
You should get a message "Successfully compiled"
Click on close.
In this example, we have three local variables
net, b and p1
Where as in the Ex 1, p1 is a place holder column.
Invoke the report wizard by click on Tools Report Wizard Create both Web and
Paper Layout Select Tabular SQL Query Data source definition : select * from
emp ( Query is present ) Move all the fields from Available Fields to Displayed
Fields ( Observer we have 9 fields ) Next Next Select No template Finish
In the output, we have total nine fields.
The last field is bonus.
We are unable to display p1, in the report output. Because p1 is a local variable.
186 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
So, we cannot have local variables in the output. But, can have the place holder
column in the output.
Save the report PLACE_HOLDER_TWO.rdf
Group Filter
Group Filter, is used to filter the records in the report.
We can filter the records in three types
1) First
2) Last
3) PL/SQL
Lets us understand the group filter with an example.
Ex:
Open the report builder tool,
develop the report using report wizard using the query - select * from emp;
Save the report with the name GROUP_FILTER.rdf
This is how the report looks like, We get all the 14 records in the report output.
Go to Object Navigator ( F5 ),
Under Data Model a Groups a Select the group G_EMPNO
187 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Right click on the group G_EMPNO, select Property Inspector
Filter type - change to First
No of Records - Change to 5
Close the Property Inspector. Save the report. Run the Report.
Observe, We get only first five records in the output.
188 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Let's make few change to the report
Go to Object Navigator ( F5 ),
Under Data Model à Groups à Select the group G_EMPNO
Right click on the group G_EMPNO, select Property Inspector
Filter type - change to Last
No of Records - Change to 3
Close the Property Inspector. Save the report. Run the Report.
Observe, We get only last three records in the output.
Let's make few change to the report
Go to Object Navigator ( F5 ),
Under Data Model Groups Select the group G_EMPNO
Right click on the group G_EMPNO, select Property Inspector
Filter type - change to PL/SQL
In PL/SQL Filter , provide the following code.
function G_EMPNOGroupFilter return boolean is
begin
if :comm is null then
return (FALSE);
else
return (TRUE);
end if;
end;
189 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Click on compile.
Click on close.
The above code displays the records which have comm.
Save the report. Run the Report.
Observe,we get only the records which have comm.
So, Group Filter is used to filter the records in the output.
We can filter the records in three types. 1) First, 2) Last and 3) PL/SQL
Reports with parameters
Reports have two types of parameters
1) Bind parameters
2) Lexical Parameters
190 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Bind Parameters
Accepts value at run time.
Bind parameter is created by using colon ( : ) in SQL Query.
Example:
We want to create a report which accepts deptno and display employees working for
that deptno.
Open Report Builder tool Use the report wizard OK Next Create both Web
and Paper Layout Next Tabular Next SQL Query
Data Source Definition -- Select * from emp where deptno = :dno; Next Provide
username , password and database details Connect
We get a message - The Query Q_1 has created bind parameter DNO
(As we know, bind parameter is prefixed with colon (: ) , in the SQL Query, we have
used colon, so report builder identified DNO as bind parameter) OK Move all the
fields from Available Fields to Displayed Fields Next Next Next Select No
template next finish.
We get run time parameter form.
191 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Enter the value - 10 Press Enter Key
In the output, we get only the employees working for deptno 10.
Let's run the report one more time.
Click on Program in Menu bar Run Paper Layout
As Usual, We get Runtime parameter form
Enter the Value - 20 Press Enter Key
In the output, we get only the employees working for deptno 20.
Go to File Save as EMP1.rdf
The bind parameter information is also available in Object Navigator (Press F5)
Under Object Navigator Data Model User Parameters DNO
So, we have achieved what we want.
Our report accepts deptno as parameter and display employees working for that
deptno.
192 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Providing list of values to the parameter
We can provide the list of values to the bind parameter DNO.
List of values, can be created in two ways
1) Static Values
2) Select Statement
Let us look at both the ways practically.
Creating list of values - static method
Under Object Navigator Data Model User Parameters DNO , Right click select
property Inspector
Click on List of values
We get parameter list of values window.
193 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Select radio button Static values.
Enter Value - 10 Click on Add button.
Enter Value - 20 Click on Add button.
Enter Value - 30 Click on Add button.
Enter Value - 40 Click on Add button.
Enter Value - 50 Click on Add button.
Enter Value - 60 Click on Add button.
Click on OK
Observer, we have entered six static values (10, 20, 30, 40, 50, 60 )
( Note: We do not have employees working in deptno 40, 50 and 60 )
Save the report.
Run the report.
In runtime parameter form, we get list of values to the bind parameter.
Select the value 10 and Run the report.
We get employees working in deptno 10.
194 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Now, let us learn creating list of values using select statement.
Under Object Navigator Data Model User Parameters DNO, Right click select
property Inspector
Change the data type - Number
Click on List of values
We get parameter list of values window.
Select the radio button SELECT Statement
SQL Query statement - select distinct deptno from emp;
195 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Click on OK
save the report.
Run the report.
In runtime parameter form, we get list of values to the bind parameter.
Observe, we got only three values 10, 20 and 30.
Because the select statement used in creating list of values is returning only thee
values.
196 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Now, we know what is bind parameter? And how to creating list of values to the bind
parameter.
Assignment 1
Create report based on the following query
select * from emp where deptno= :deptno and job = :designation
Assignment 2
select * from emp where to_char(hiredate, 'YYYY') = :year;
197 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Lexical parameter
Lexical parameter accepts string at run.
The string gets embedded into the query at run time.
Lexical parameter is created by using ampersand ( & ) in select statement.
Example:-
We want to create a report to display employee details based on the condition.
Condition is passed as a parameter.
Open the report builder tool Use the report wizard OK Next Create both
Web and paper layout Next Tabular Next SQL Query
Data source definition - select * from emp&a;
Next provide username, password and database Connect we get a message -
The query Q_1 has created the lexical parameter(s) a
198 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
OK Move all the fields from Available Fields to Displayed Fields Next
Next Next Select No template next finish
We get Run time parameter form
Enter the value – wheresal > 2000 -- press Enter Key
In the output, we get only the employees whose salary is greater than 2000
199 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Let's run the report one more time.
Click on Program in Menu bar Run Paper Layout
As Usual, We get Runtime parameter form
Enter the Value - where job ='CLERK' Press Enter Key
In the output, we get only the employees working as Clerks.
Save File as EMP2.rdf
Observe, we have executed the report two times.
For the 1st time, we have passed the value -- wheresal> 2000
For the 2nd time, we have passed the value -- where job = 'CLERK'
We got the appropriate output as per the condition for every execution.
So, our report is displaying the employee details based on the condition passes as
parameter.
Lexical parameter accepts string (whereclause) at runtime and giving the desired
results.
Observe, the query used in developing the report.
Select * from emp&a;
In the query, a - is lexical parameter.
And we know that lexical parameter is prefixed with ampersand (&) operator.
The lexical parameter information is also available in Object Navigator (Press F5)
Under Object Navigator Data Model User Parameters A
200 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Group Above
A group above report also contains multiple groups in its data model, dividing the
rows of a table based on a common value in one of the columns. Use this type of
report to restrict a column from repeating the same value several times while values
of related columns change. The data model for group above and group left reports is
the same, but the layouts differ; group above reports display the master information
at the top while group left reports display break columns to the side.
Open report builder tool Use the report wizard Next Create both Web and
Paper Layout Next select group above button Next SQL Query Data
source definition - select * from emp; Next provide the username, password
and database details Connect Move Job to Group fields
Next Move all the columns
201 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Next Next Next NO template Next Finish.
202 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Master Detail Report
In many reports, the data displayed for one part of the report is determined by the
data displayed for another part.
This is termed a "master/detail," or "parent/child," relationship.
To create a master/detail report, we need to define data link between two queries.
When you run a master/detail report, each row of the master query will cause the
detail query to be executed, retrieving only matching rows.
Let us experience master/detail report with an example.
Open the report builder tool Build a new report manually OK
We will be directed to Data Model
Click on SQL. We get SQL Query Statement window.
Provide the Query Select * from dept; OK
Provide the connection Details, UserName, password and the Database OK
This is how the Data Model looks like.
Again
Click on SQL. We get SQL Query Statement window.
Provide the Query Select * from emp OK
This is how the Data Model looks like.
203 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Let’s us establish relationship between two queries.
This is possible by creating data link.
Select Data Link, drag mouse pointer from Q_1 to Q_2
Observe the relationship between the queries is established.
Q_1 is the master query.
Q_2 is the detail query.
Data link information is also available in Object Navigator (Press F5)
Under Data Model Data Links
204 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Now, Select Tools under Menu bar, select Report Wizard
Select - Create both Web and paper layout Next Style - Group Above Next
Move G_DEPTNO - Across
Move G_EMPNO - Down
205 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Next Move DEPTNO, DNAME, ENAME, JOB, SAL to Displayed Fields
Next Next No Template Finish
Save the report as MASTER_DETAIL.rdf
The report is a four page report.
This is how the report looks like
In Page 1 -- We get the details to employees working in deptno 10
In Page 2 -- We get the details to employees working in deptno 20
206 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
In Page 3 -- We get the details to employees working in deptno 30
In Page 4 -- We get the details to employees working in deptno 40
Output of Page 1
Click on next page
Output of Page 2
Click on next page
Output of Page 3
Click on next page
207 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Output of Page 4 (we have no employees)
The detail query (Q_2 ) retrieves all related records for each record retrieved by the
master query.
Report Triggers
Triggers are PL/SQL code, which gets executed automatically.
We have five types of triggers in Oracle reports.
1) Before Parameter form
2) After Parameter form
3) Before report
4) Between pages
5) After report
We cannot create new type of trigger.
Report triggers must explicitly return TRUE or FALSE
By looking at the names of the trigger, we understand at what point trigger is
executed (fired).
Trigger Name Explanation
Before Parameter
Fires before the Runtime Parameter Form are displayed.
form
After Parameter
Fires after the Runtime Parameter Form are displayed.
form
Before Report Fires before the report runs but after query are parsed.
Fires before each page of the report are formatted, except the
Between Pages
very first page.
After Report Fires after you exit the Paper Design view.
Let us start understanding before report and after report triggers
Open the report builder tool Use the report wizard OK Next Create both
Web and Paper layout Next Style - Tabular Next SQL Query Data Source
208 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
definition - select * from emp; Next provide username, password and
database Connect Move all the columns from Available Fields to Displayed
Fields Next Next Next No Template Next Finish
This is how the report looks like
Go to Tools Object Navigator Report Triggers Right click on BEFORE
REPORT PL/SQL Editor
Provide the following code
209 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
functionBeforeReport return boolean is
begin
srw.message(10, 'Welcome to my Report');
return (TRUE);
end;
Click on compile.
You should get a message "Successfully compiled"
Click on close.
SRW -- is a built-in package
message -- is a procedure, used to display a message with number and text.
We are done with creating BEFORE REPORT trigger.
Now , let us create AFTER REPORT trigger.
Go to Tools Object Navigator Report Triggers Right click on AFTER REPORT
PL/SQL Editor
210 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Provide the following code
functionAfterReport return boolean is
begin
srw.message (15, 'End of the report');
return (TRUE);
end;
Click on compile.
You should get a message "Successfully compiled"
Click on close.
Save the report as EMP3.rdf
Run the report.
211 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
BEFORE REPORT triggers fires, we get the following output
Click on OK
We get the Report output.
Click on Close button.
Observe, AFTER REPORT trigger is not executed.
To experience AFTER REPORT trigger, Go to File Print Preview
As expected, BEFORE REPORT trigger is executed, we get the following output
212 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Click on OK
We get the report in the Previewer window.
Click on Close button in the previewer window.
After report trigger gets executed. We get the following output
213 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Let us now experience BEFORE PARAMETER FORM and AFTER PARAMETER FORM
triggers.
Open the report builder tool Use the report wizard OK Next Create both
Web and Paper layout Next Style - Tabular Next SQL Query Data Source
definition - select * from emp where deptno = :deptno ; Next provide
username, password and database Connect Move all the columns from
Available Fields to Displayed Fields Next Next Next No Template Next
Finish
We get Runtime parameter form, waiting for the user input
Enter the value 10
We get the following output
Go to Tools Object Navigator Report Triggers Right click on AFTER
PARAMETER FORM
PL/SQL Editor
214 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Provide the following code
function AfterPForm return boolean is
begin
if :deptno is null then
:deptno :=10;
srw.message ( 12, 'Parameter not provided - default value assigned');
return (TRUE);
else
return ( TRUE );
end if;
end;
Click on compile.
You should get a message "Successfully compiled"
Click on close.
215 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
As, we know, the above code is executed, after runtime parameter form is displayed.
If the end user, do not pass any value, the value 10 is assigned to the parameter.
Save the report as EMP4.rdf
Run the report.
As expected, Runtime parameter form is opened.
Do not pass any value. Just press enter key.
The code in the after parameter form trigger is executed.
We get the following message
Click on OK
We get the employees working for deptno 10.
216 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Run the report one more time.
This time enter value 20 for the parameter.
We get the employees working for deptno 20
Similarly , try with BEOFRE PARAMETER FORM trigger and experience it.
Note :-srw.message is not support in Oracle Apps.
Bind Parameter Report Registration Process in Oracle Apps
Create a report with Bind parameters with below mention command.
Open Report Builder tool Use the report wizard OK Next Create both Web
and Paper Layout Next Tabular Next SQL Query Data Source Definition --
Select * from emp where deptno = :dno; Next Provide username , password and
database details Connect OK Move all the fields from Available Fields to
Displayed Fields Next Next Next Select No template Next Finish.
Save the file.
Transfer the file into server from client through winscp.
In real time you need to transfer files in custom application but in my system I don't
have custom application so we transfer rdf file into inventory module.
Login Winscp: - Enter hostname, username & password
glo.dev.com, root & oracle
217 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Left Side is client machine & right side is server.
On left side select the path where you the save the rdf file.
On right side select the path of the module in which you want to run the file.
path - /d01/oracle/VIS/apps/apps_st/appl/inv/12.0.0/reports/US
Every server has his own path.
samplereport.rdf file transfer to server.
218 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
After transfer the file need to create a concurrent Program . We already learn how to
create a concurrent program.
In this concurrent program only change is executable file is report (.rdf) file earlier we
learn with PL/SQL Stored Procedure. There is no change in the process to run the
concurrent program.
1) Create Executable
Select system administrator
Concurrent Program Executable
Execution Method: - Need to mention the executable file type. We are running
oracle report through concurrent program so execution method is oracle reports.
Execution file Name: - Mention only report name not extension and Save the form.
2) Define the program
Concurrent Program Define
219 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Select Style as A4.
In this concurrent program we need to create parameter because in oracle report we
create bind parameters.
Note: Token should match with bind parameter of the query.
Save the program.
3) Find the Request Group
Security Responsibility Define
Request group of inventory is All Inclusive GUI.
4) Place the program in the Request Group
Security Responsibility Request
Go to Query Mode Press F11
Search Criteria (Case sensitive): - All%GUI
Press CTRL+F11
220 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Save.
5) Switch the responsibility and Submit the Request
After close the form you will get this screen and press the black button (Like Hat) to
switch the responsibility to inventory.
Submit the request: -
Click on View Output.
This is how you are able to run the report through concurrent program.
221 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Lexical Parameter Report Registration in Oracle Apps
Create a report using lexical Parameters
Select party_name,account_number from hz_parties hp,hz_cust_accounts hca
Where hp.party_id=hca.party_id &lplcn &lphcn;
Below is the image which data is showing with our sql query.
We have created 2 lexical parameters for range as low & high. In our parameters we
will give the range from 1000 to 1010 all the customers are available between in this
range will show in our report.
But in lexical parameters we need to write the where & front end people don’t know
how to write the where clause so we need to create the two bind parameters & we
need to attach the lexical parameters with our bind parameters by using triggers.
222 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Create Bind Parameters
Parameters
223 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Attach Bind Parameters with Lexical Parameters.
For attaching the bind parameter with lexical parameters we need to create trigger in
after parameter form.
https://drive.google.com/open?id=1_Rklfce41owsNAr3KsGwkrzFo0n4ARCW
In this we have written in LCN what value we have given all the data will display is
equal to or greater than.
In this we have written in HCN what value we have given all the data will display is
equal to or Less than.
If we didn’t give any value then system will provide the all the rows available in the
data.
Save the file as RDF & transfer the file in the server.
Create the Concurrent Program
Create Executable
Define the Program
224 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Create Parameters & provide the token name as bind parameter.
Attach Value Set.
Find the Request Group.
225 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Attach the Program with Request Group
Switch the Responsibility & Submit the Request.
Provide the range to 1000 to 1010 & check the output
Try to submit this same concurrent program without any values in parameters. It will
display all the rows which are available in our table.
226 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Standard Report Customization
For customization for need to follow some steps
1) Understand standard report
2) Understand Client requirement
3) Steps Followed
For editing the standard report you need to transfer the rdf file into client machine.
You only have the concurrent program name (Sales Order Acknowledgement). To you
know the rdf file name first find the executable name with concurrent program name
then check the executable you will get the rdf file name. Transfer the rdf file
(OEXOEACK.RDF).
/d01/oracle/VIS/apps/apps_st/appl/ont/12.0.0/reports/US
Open the rdf file in report builder. Create a duplicate file. Don't touch the original.
About the report: -This report gives all the details of the sales order between the
range. (66150 and 66160)
Client requirement: - Report should give all the details of the sales orders which are
entered, booked and closed. This flow status code should be passed as a parameter.
227 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
1) Add a new parameter (P_SALES_ORDER_STATUS) in data model User
parameters
For Add New
Parameter
Right Click on new parameter Property palette Data type – character Size -100
2) Add another new parameter (LP_SALES_ORDER_STATUS) in data model User
parameters.
Property palette --data type – character size -100
Connect with Database.
228 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
3) In after parameter form trigger , include the following code
/*---------Lexical Parameter for Sales Order Status parameter added by Ankit --------*/
if :p_sales_order_status is not null then
:LP_SALES_ORDER_STATUS:= 'and h.flow_status_code = :p_sales_order_status';
else
:LP_SALES_ORDER_STATUS:= 'and h.flow_status_code in(
''ENTERED'',''BOOKED'',''CLOSED'')';
end if;
5) Open the data model, in Q_orders group ( clickon the group name to get the
query)
Enter the below mention line before the order by clause.
For Complete code open below mention Link:-
https://sites.google.com/view/standardreport
&lp_sales_order_status --Added by Ankit
229 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Save the file and transfer the file into server.
In real time you need to transfer file into custom application but we transfer file in
order management module.
5) Create new table based Value set, which contains three values Entered, Booked
and closed.
Click Edit Information
230 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Create New Concurrent Program.
1) Create Executable
Select system administrator
Concurrent Program Executable
2) Define the program
Concurrent Program Define
Don't create the new program open the existing program (Sales Order
Acknowledgement)
Press Copy to create the duplicate program.
231 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Enter the program & short name
If you want to change the application you can also change.
Press Ok.
Change the executable name.
Click on parameters add new parameters.
232 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Note: Token should match with bind parameter of the query.
3) Find the Request Group
Security Responsibility Define
4) Place the program in the Request Group
Security Responsibility Request
233 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
5) Switch the responsibility and Submit the Request
Order Number from 66150 to 66160.
Scroll Down.
Open orders only: - No
Sales Order Status: - Booked.
Press ok Submit No Find.
234 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Interfaces
Interfaces are programs used for extracting or loading the data in oracle apps.
Two Types of interfaces
1) Outbound Interfaces
2) Inbound Interfaces
Outbound Interfaces
It is process of extracting the data from Oracle Apps DB to flat file.
Flat file can be - xls, csv, txt, data
While developing the outbound interface we will use UTL_File to extract the data.
File handling steps
1) Declare the file
<file_handler> UTL_FILE.FILE_TYPE;
2) Open the file
<file_handler>:= UTL_FILE.fopen(<file_path>,<file_name>,<mode>);
3) Write the data into the file
UTL_FILE.PUT_LINE (<file_Handler>,<temp>);
4) Close the file
UTL_FILE.fclose(<file_handler> );
Outbound Interface process:
1) Develop the PL/SQL program (Either procedure or package)
2) Write the cursor to retrieve the data from database tables.
3) Create file or open the file by using UTL_File.fopen().
4) Open the cursor
5) If any validations are there write the validations
6) Transfer the data into file by using UTL_File.Put_line().
7) Close the curser
8) Close the file by using UTL_File.Put_Line();
9) Register the procedure or package as concurrent program and submit from SRS
window.
235 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Query to get the file path
Select * from v$parameter where name = 'utl_file_dir';
Copy the path in the above query result for outbound file destination.
We will use this path in procedure.
(v_fpath := '/d01/oracle/VIS/db/tech_st/11.1.0/appsutil/outbound/VIS_glo';)
Note: Only above query paths are suitable for outbound files in server. Then you can
transfer file from server for client machine
Create outbound package
For PL/SQL Code open below mention link: -
https://sites.google.com/view/outbound-interfaces
We have used the below mention SQL Query to get the data
select header_id,order_number,ordered_date,price_list_id
from oe_order_headers_all where to_char(ordered_date,'yyyy')= 2003;
Run the Package in SQL Developer.
Create a concurrent program
1) Create Executable
Select system administrator
Concurrent Program Executable
2) Define the program
Concurrent Program Define
236 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
3) Find the Request Group
Security Responsibility Define
4) Place the program in the Request Group
Security Responsibility Request
5) Switch the responsibility and Submit the Request
Switch the responsibility to Order Management & Submit the request.
237 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
This is how you can create the concurrent program to get the data in file.
Get the file in client Machine
1) Login the Winscp
2) Go to the folder (right side) where file is generated in server.
3) Move the file into client machine.
Outbound Exceptions
1) utl_file.invalid_operation
2) utl_file.invalid_path
3) utl_file.invalid_mode
4) utl_file.invalid_filehandle
5) utl_file.read_error
6) utl_file.write_error
7) utl_file.internal_error
8) OTHERS
We need to use utl.file.fclose_all in exception section to close all the files if we get
any error.
238 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Outbound interface with Parameter
In the above example we have given the year 2003 but we can do some modification
that when end user will submit the concurrent program that time he will get the
parameter where he can select the year as per requirement.
For this we need some changes in our package. We need to create one more
parameter & define that parameter in our select statement.
https://drive.google.com/open?id=1JzZt96ZkPYlBDHmz-R2MePp1acKCfnna
Save the package in the database.
Create Executable
239 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Define the Program
Create Parameter & Attach the value set 10/ Number.
Save the form.
Find the Request Group
Attach the program in the request group
240 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Switch the responsibility & submit the request
We are able to enter any year in the parameter window.
We can download the file in from the server.
We want to restrict the user to select only those years which are available in the
table.
These are the years are available in the table.
So create the view as per the sql query.
https://drive.google.com/open?id=14_KYrdt7DdxBarPWurVzumaxfP_ryAPk
241 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Create or replace view order_date as SELECT distinct
(to_char(ordered_date,'yyyy'))ordered_date FROM oe_order_headers_all
WHERE ordered_date IS NOT NULL;
Create table value set with our view.
Attach the value set with the program.
242 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Switch the responsibility & submit the request
Inbound Interfaces
It is process of loading the data from flat file to Oracle Apps Base table.
Convert SQL* Loader Staging
XLS File CSV File
Table
Interface
Import Program
Base Table Table
You get the data in xls file from client.
First step convert xls file into csv file from save as option.
Select .csv option from save as option.
Second Step
We load the data from CSV file to the Staging table by using SQL*Loader.
Before Learning about inbound interface we will learn SQL * Loader tool.
243 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
SQL Loader
This tool is used to load the data from the flat file to the tables.
This tool requires control file (.ctl) for loading the data.
It is developer responsibility to create control file.
Create below mention table in database.
Create table
Student (Rollno number (3),
Name varchar2 (10),
Marks number (3));
Download the data file from below mention link for upload data in student table.
https://goo.gl/qACrds
Transfer the data file in server in out folder of module, by using WINSCP.
Save the server path ---Data File Need to Transfer in out Folder
/d01/oracle/VIS/apps/apps_st/appl/inv/12.0.0/out/Sample_SQL.txt
Server Path
Create Control file -- Control File Transfer in Bin Directory
Syntax: -
LOAD DATA
infile '<data file path>'
Insert/append/truncate/ into table <table_name> fields terminated by ','
(col1, col2, col3)
Example:-
LOAD DATA
infile '/d01/oracle/VIS/apps/apps_st/appl/inv/12.0.0/out/Sample_SQL.txt'
Insert into table student fields terminated by ','
(Rollno, Name, Marks)
244 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Put the above code in notepad file and save the file with extension .ctl
Control file is used to give information about source (datafile) and Destination
(table).
Transfer the control file in server in bin directory of module.
Register the control file as concurrent program to load the data.
1) Create Executable
CTL file Name
2) Define the program
3) Find the Request Group
245 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
4)Place the program in the Request Group
5) Switch the responsibility and Submit the Request
Switch the responsibility we already discussed.
Check the table.
246 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
_________________________________________________________________
Insert: - Insert is used to add the rows into the table but only when table is empty.
Append: - Append is used to add the rows in Existing table (In those table that are
having existing data).
Truncate: - Truncate is used to replace the complete old data to new data.
If we want to load just First five rows from the data. We need to do some changes
in Control file.
options(load = 5 )
Load data
infile '/d01/oracle/VIS/apps/apps_st/appl/inv/12.0.0/out/Sample_SQL.txt'
Insert into table student fields terminated by ','
( Rollno, Name, Marks )
If we want to skip some rows from the data. we need to do some changes in
Control file.
options ( skip = 5 )
LOAD DATA
infile '/d01/oracle/VIS/apps/apps_st/appl/inv/12.0.0/out/student_data.txt'
truncate into table student fields terminated by ','
( Rollno, Name, marks )
247 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
If we want to load rows based on some conditions from the data. we need to use
when clause in Control file.
Load those student data those are having marks = 50.
LOAD DATA
infile '/d01/oracle/VIS/apps/apps_st/appl/inv/12.0.0/out/student_data.txt'
truncate into table student when marks = '50' fields terminated by ','
( Rollno, Name, marks )
Reject Rows from SQL Loader:-
Bad Files: - In bad file you will see those data which is rejected from SQL Loader
because of data type and size of the column.
Discard Files: - In discard File you will see those data which is rejected from SQL
Loader because of when clause.
You can see bad file and discard file. After submit the concurrent program you will
get view log in that window you can see the path and open the path in the server
with this you can see the bad file and discard file and send to the client as per their
requirement.
Insert data from multiple data files to a single table.
Create Table student1 (rollno number (10), sname varchar2 (10), marks number (10));
In the First file we have some rows and in second file we have some rows we want to
add all the rows in a table student using one control file.
248 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
First File
https://drive.google.com/open?id=1AWwjE-VxAEELAuBYpkrtZdzwKoTF2ljg
Second File
https://drive.google.com/open?id=1ogSDvBUBoJ_MaRHufAvh0XlRl7-rBXjP
Data File Need to Transfer in out Folder
Create Control File
249 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
https://drive.google.com/open?id=1lcL2HBR5Pfbt0KDHpIWHX_rMgXzq9RK1
In the Control file we just need to mention our other file name also.
Control File Transfer in Bin Directory
Create Concurrent Program.
1) Create Executable
2) Define the program
3) Find the Request Group
4) Place the program in the Request Group
5) Switch the responsibility and Submit the Request
Switch the responsibility we already discussed.
250 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Check the Table
Insert data from a single data file to multiple tables
Create tables
Create Table student2 (rollno number (10), sname varchar2 (10), marks number (10));
Create Table student3 (rollno number (10), sname varchar2 (10), marks number (10));
Data File
https://drive.google.com/open?id=1j80au1GOF_x2FpvbT9_3NjJH4YgwqDZT
Data File Need to Transfer in out Folder
251 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Control File
https://drive.google.com/open?id=1r9Y1AqlQicE4YcH38Kg6TDTabZinW7NR
When we want to transfer the same data into the other table we need to mention
the position on the first columns other columns will automatically come as next.
Control File Transfer in Bin Directory
Create the Concurrent program and submit the concurrent program.
Check both the tables.
252 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Insert data from a single data file to multiple tables SKIP Column
Create Two tables.
Create table student4 (rollno number (10), eng number (10), maths number (10));
Create table student5 (rollno number (10), sname varchar2 (10));
Data File
Our Requirement is we need to load rollno , eng & maths marks upload in student4
table and rollno & sname we need to upload in student5 table.
For this we need to skip sname column from student4 load script. We will use the
keyword filler. With the keyword filler we can skip the colums.
https://drive.google.com/open?id=1xXFTfru98HdipXTfGEv--5EJbBuyxKlM
Control File
253 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
The TRAILING NULLCOLS clause causes SQL*Loader to treat any fields that are
missing at the end of a record as null
https://drive.google.com/open?id=147BILag1oR02lTPqNrNsEdRe52OkLet3
Transfer both the file to server. Create & submit the concurrent program.
Now Check the Tables.
Log File
254 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Loading Data from Fixed Length file
We have a fixed length format file containing student data, as shown below, and
want to load this data into an Oracle table.
Create table
Create table student6 (rollno number (10), sname varchar2 (10), marks number (10));
Data File.
First Open the file in a text editor and count the length of fields, for example in our
fixed length file, student number is from 1st position to 3rd position, student name is
from 6th position to 10th position, marks is from 13th position to 14th position.
Similarly other columns are also located.
https://drive.google.com/open?id=1BXSMPfzxuHg-uofgN_Nrh80Nnzzb9qFU
Control File
https://drive.google.com/open?id=156ESOkFnB5ebOAhsOWF80IsJ_wReNZO0
255 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
The data types (INTEGER EXTERNAL, CHAR) identify the data type of data fields in the
file, not of corresponding columns in the student table.
Transfer both the file to server. Create & submit the concurrent program.
Now Check the Table.
Insert data from multiple files to multiple tables
Now we want to load all the student whose marks is equal to 10 into student7 table
and those student marks is not equal to 90 in student8 table. To do this first create
the table’s student7 and student8 by taking appropriate columns and data types.
Create table student7 (rollno number (10), sname varchar2 (10), marks number (10));
Create table student8 (rollno number (10), sname varchar2 (10), marks number (10));
Data File 1
https://drive.google.com/open?id=1w-fpgBEXnYIY_t9poBsAv0z7i4y7Tl0L
https://drive.google.com/open?id=1u_rUJDBlpk6yx9qnTbVX2OwaxlRJ2Vxd
256 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Data File 2
Control File
https://drive.google.com/open?id=1ltv92_gzF8qg8DAfU0MEOmqzQDaBAzBJ
Transfer both the file to server. Create & submit the concurrent program.
257 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Using One Control file there exists four combinations.
1) Insert data from multiple data files to a single table
2) Insert data from a single data file to multiple tables
3) Insert data from single data file to single table
4) Insert data from multiple files to multiple tables.
The first three are possible for any situation. But the fourth case is possible only when
the structure of all tables is the same. Otherwise we have to go for multiple control
files.
Now we are going to learn Inbound Interface in step by step.
Step 1: Receive the excel file from the client.
Download the excel file using below mention link
https://drive.google.com/open?id=1Nw0xFMD_pgWiy3eu1evSggVKOqqM8sXd
If you want to practice more than one time change Item Number.
Step 2: Convert the excel file to CSV file.
258 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Step 3: Create the staging table using the script.
Download the Tagging table script.
https://drive.google.com/open?id=1L98D1Fswn29L46PfU-1uTFA7QfdhIcsP
In the staging table we have create the table using interface table columns & in the
end we have added two extra columns (transaction_id_stg , status_stg).
transaction_id_stg is used to check the count of the rows & , status_stg is for
validation purpose when you create the table all the rows are having status as N.
With staging table we have also created the Synonyms for the auto generate number
for count of the rows.
Step 4: Develop the control file.
259 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Download the Control file.
https://drive.google.com/open?id=159bHVfR57zsrDuI1iyNzlXHtTGXIfpOM
Step 5: Transfer the CSV file to the OUT folder of INV_TOP.
Step 6: Transfer the ctl file the bin directoryof INV_TOP.
260 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Step 7: Register the ctl file as concurrent program
1) Create Executable
2) Define the program
3) Find the Request Group
261 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
4) Place the program in the Request Group
5) Switch the responsibility and Submit the Request
Switch the responsibility we already discussed.
Now, we have the data loaded into the staging table.
262 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Step 8: Create error log table
As the client needs the invalid data to be loaded into a custom table, we have created
additional table i.e.error_log.
Download the error log table script.
https://drive.google.com/open?id=1EV4nEu9HNLJAu_5DjRpIRUAOuMjzRg8y
263 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Step 9: Create Package to validate data & load data
Validation package is collection of 3 procedures
1) Set_defaults
2) Validate_data
3) load
Set_defaults: This procedure is collection of update commands.
It will update null values to the default values as per the client requirement.
This procedure is optional.
Validate_data: This procedure will validate the staging table data as per the client
requirement.
Load: This procedure is used to load the validate data into the interface table.
Download the Package Specification & body
https://drive.google.com/open?id=1dm90xfQHF6re_HAGuEpKGKtpREh4-veK
Step 10: Execute set_defaults procedure
264 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Step 11: Execute validate_data procedure
Step 12: Check the data in the staging table, to know the records which are valid
and invalid.
Step 13: Also check the no of records in the error_log table.
265 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Step 14: Find the Interface table.
Select * from all_tables where table_name like '%INT%' and owner = 'INV';
Step 15: Delete all the rows in the interface table ( by using delete command) and
make it empty.
Step 16: Execute the load procedure.
Step 17: Check the rows in the interface table.
266 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Step 18: Take a note of the no of rows in the base table (mtl_system_items_b)
Step 19: Login to Inventory, Vision Operations (USA) and submit the import
program“Import Items”
First open the Master Item form then submit the concurrent program.
267 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Step 20: Once the import program is completed, check the no of rows present in
the base table.
So, this is how the data is to be loaded from the flat file to the base table.
Inbound Interface Using API
When we learn inbound interface we transfer the data in base table using import
item program through interface table. There are base tables which are not having
interface table. For those tables oracle have provided pre-defined packages (API).
For this first we will transfer only one row in the base table with this we will get the
idea how to transfer the more than one row in one go.
Convert SQL* Loader
XLS File CSV File Staging
Table
API
Base Table
We want to upload below mention Row in the oracle apps table & this data is related
to customers.
268 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
To download the file click on below mention link
https://drive.google.com/open?id=1f87z6lcpsELPCyeOLmqXfDGjXgqzI5an
For practice more than one time change account name.
Step 1 Creating Customer
When we create a customer two base table get affected is hz_parties &
hz_cust_accounts.
For creating customer we need to create a PL/SQL block.
Customer Name
Organization Name
Predefined Package
In the PL/SQL block we just need to enter the details of customer name &
organization name.
To download above PLSQL block click on below mention link.
https://drive.google.com/open?id=11T8MOBmum0jLSgiCKQSrjp9mBUhLPXdt
For this customer details we need to use
hz_cust_account_v2pub.create_cust_account package.
269 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Before running this package check the count of rows from both the tables (hz_parties
& hz_cust_accounts)
After run this PLSQL block we need to do commit to save the data permanent in the
database.
Note down thex_cust_account_id (124739) &x_party_id (428706).
Party_Id
270 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
X_Cust_Account_Id
Step 2 Creating Customer Location
For creating customer location also we need PLSQL block.
In this block we just need to add addess1, city, country, postal_code& state as per
data file.
In this block also we are using the same package which we used to create customer &
this is very important line in this PLSQL block.
To download above PLSQL block click on below mention link.
https://drive.google.com/open?id=1VHG57YNF4z2brEzBy8Nvi-arGBqNsa5H
Before running this package check the count of rows from the table (hz_locations)
271 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
After run this PLSQL block we need to do commit to save the data permanent in the
database.
Note down the X_Location_ID (26864).
After creating the address we are not able to Track this address is for which party &
customer so in the step 3 & step 4 we need to link the location with party &
customer.
272 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Step 3 Creating Party Sites
This step is link the location & hz_party. With this we can easily track which location
is used to which party.
For creating customer party sites also we need PLSQL block.
In this block we just need to update Party_id & Location_id.
Party_id we get from step 1 & location_id we get from step 2.
To download above PLSQL block click on below mention link.
https://drive.google.com/open?id=1EHgCpEOOLtyQjJw2RLXicqWaTuyOhjpJ
Before running this package check the count of rows from the table (hz_party_sites)
After run this PLSQL block we need to do commit to save the data permanent in the
database.
273 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Note down the X_Party_Site_Id (238639).
Step 4 Creating Customer Sites
This step is link the location &hz_cust_accounts. With this we can easily track which
location is used to which customer.
In this block we just need to update cust_account_id&party_site_id.
274 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
cust_account_id we get from step 1 &party_site_id we get from step 3.
To download above PLSQL block click on below mention link.
https://drive.google.com/open?id=1hTSHUaBxCaeNHTKJOBCev3k4MeKIXrQ7
Before running this package check the count of rows from the table
(hz_cust_acct_sites_all)
After run this PLSQL block we need to do commit to save the data permanent in the
database.
Note down the X_Cust_Acct_Site_Id (11418).
275 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Step 5 Creating Site Used Code (Bill To or Ship to)
In this block we just need to update cust_acc_site_id & Site_use_code.
If we need to use this side for SHIP_TO location need to mention in code but we are
using same BILL_TO location.
cust_acc_site_id we get from step 4.
To download above PLSQL block click on below mention link.
https://drive.google.com/open?id=1ed17G6JSlan5VIyVkm8cuG8VTdfykR3w
Before running this package check the count of rows from the table
(hz_cust_acct_sites_all)
276 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
After run this PLSQL block we need to do commit to save the data permanent in the
database.
Note down the X_Cust_use_id (13993).
With this we have completed how to insert single row in a oracle apps base table
but if we have 100 rows we are not able to do this step so next we are going to
learn if we want to insert more than one row.
Step 1: Receive the excel file from the client.
https://drive.google.com/open?id=1-hnsJgVH-0TQZPrRErUiEx64j3EnRzda
277 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
If you want to practice more than one time change Account name & organization
name
Step 2: Convert the excel file to CSV file.
Step 3: Create the staging table using the script.
In this we pick the columns from the base tables & data available in the csv file.
In the starting we add two columns (Transaction_IDF_STG & Status_STG) for validate
the data. Transaction_IDF_STG is used to check the count how many numbers of
rows updated in staging table. Status_STG is used to validate the data. In the
between also we added some columns from base table to get the all the id’s available
in our stating table so client can pick the id & validate the data. In the end we have
added error message column.
Download Staging table scipt click on below mention link
https://drive.google.com/open?id=1J8ATbHggwXc12phL_DgDHs_n90ngIiI_
278 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Step 4: Develop the control file.
Download the control File click on below mention link.
https://drive.google.com/open?id=18psmJY09vBnI0dok9IpxMxZ-7Uasbtk9
Step 5: Transfer the CSV file to the OUT directory of Receivables top.
Step 6: Transfer the ctl file to the bin directory of Receivables top.
Step 7: Register the ctl file as concurrent program
Create Executable
279 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Define the program
Find the Request Group
Place the program in the Request Group
Switch the responsibility and Submit the Request
280 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Data is loaded into the staging table.
You can see in status_stg column all the rows are having N because with staging table
we have created one trigger.
281 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Step 8: Validation Requirement
Client don’t want Duplicate data
Client don’t want Null values
Step 9: Create Package Specification to validate data & Load data
Download Package Specification
https://drive.google.com/open?id=1jC-A6cW97g597dPLLOHiS5zrnjD43qUQ
Step 10: Create Package body to validate data & Load data
282 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
In this package we have one procedure validate it divide in to three parts.
Alter validation valid rows status change to V & invalid change to VE.
Download Package Body
https://drive.google.com/open?id=17Un27HWwKkQBwH-WEWb7WxGeByiQwj1d
Erroring out duplicate: - In we want to remove all the duplicate rows
We have 3 rows for sample2 customer in the second update command we have
mention the where rownum = 1 from that group update first row again status as N.
Account Name is null: - where account name is null change status to VE.
Organization Name is null: - where organization name is null change status to VE.
283 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Second Procedure is used to Load the data from staging table to oracle apps base
table.
If you see the code is same as above example where we insert the single row. In the
package we take cursor name.column name.
Step 11: Create Concurrent program to validate the data.
Create Executable
Define the program
284 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Find the Request Group
Place the program in the Request Group
Switch the responsibility and Submit the Request
We already discuss this step.
Now check the stagging data.
285 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
You can see the validate rows are update as V & invalid rows are update as VE.
We already have count for all the tables which we have used in previous example.
Step 12: Create Concurrent program to Load the data.
Create Executable
Define the program
In this program we need to create parameters as per client he just want to load
some rows now after some time he want to load some rows . So he can enter row
number& when we are creating the package specification we have mention two
local variables.
286 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Save the form.
Find the Request Group
287 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Place the program in the Request Group
Switch the responsibility and Submit the Request
In this example we only have 22 rows so we are uploading all the rows.
Submit the request.
Now check the staging table.
288 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Now you can see the loaded rows status is change to S.
Scroll to right side.
Now check the data by using id from all the tables & check the count for the entire
base table which we have used in previous example
Conversion
Conversions are data migrations program used to migrate data from legacy system to
oracle system. It is one time process. Let us suppose client is using some legacy
system and now moving to oracle apps. So, whatever data is present in existing
legacy system must also be present in oracle apps table. Conversion program can be
developed to load the data into oracle apps table. Conversion is not a scheduled
process.
289 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Forms Registration
Forms are used to accept the data from the user. Form is a file extension .fmb(Form
module build). When we compile fmb file we will get .fmx file (Form module
executable). We use form builder tool, to build a new form.
We need to build the form based on template(template.fmb) so it can be used in
oracle apps. Template.fmb is available in AU_TOP/ Forms AU_TOP (Application utility
TOP).
Step 1: - Create a folder in local machine and get template.fmb & Appstand.fmb
from AU_TOP.
Step 2:- Get resources folder from AU_TOP
In resources folder you will get all the libraries (PLL files).
290 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Step 3: - Transfer the Appstand.fmb & template.fmb file to resource folder which
we have extracted from server.
Local Machine Path
Files
After transfer the file we have all the files in single path.
291 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Step 4: - Need to mention the path of the resources folder.
1) Right Click on form builder & open properties.
2) Mention the complete path in Start in: option.
Step 5: - Open form builder tool & select build a form based on template & select
the template.fmb file.
292 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
It look like this.We need to do all the above steps othewise you will get and error.
293 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
In this template.fmb file we will get some existing data block, canvas, windows & all
those steps don’t worry about that & don’t change anything from existing work.
Step 6:- we are going to create a new form based on Table AP_INVOICE_ALL. With
only four columns INVOICE_ID, VENDOR_ID, INVOICE_NUM&INVOICE_AMOUNT
Step 7: - Create new data block.
Data block is mapping the relationship between form & table. Data block is pointing
the table name & column name which is required for our form.
1) Select the data block option & press the plus symbol (+).
Plus Symbol
2) Create data block using Wizard& Press Ok.
Press Next
294 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
3) Select Table or view.
Press Next
4) Enter the table name for which you need to create a form.
Press Refresh E nter the database details you will get all the columns those
are available in the table you need to select only four columns for our form.
To transfer the
columns from left to
right select the column
& press this arrow
button
295 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Press Next Press Next Press Next Create the data block,
then call the layout wizard Press Finish.
With this we have created a data block now we are going to create the layout wizard.
Step 8: - Create new layout wizard.
Layout is a how do you place your buttons & where do you place your buttons.
After Press Finish We will get this screen automatically.
Press Next
In Layout we need to create a canvas.
296 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
A canvas is a surface inside a window container on which you place visual objects
such as interface items and graphics. It is similar to the canvas upon which a picture is
painted.
1) Select New Canvas because we don’t need to change anything from the existing
Canvas.
Press Next
2) Transfer all the fields from left to right
These are the fields which we have selected in above step now we need to
transfer all the fields from left to right.
This button is used
to transfer all the
fields in a single
shot.
Press Next Press Next.
297 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
3) Select Form Style
Form is display one record at a time.
Tabular is displaying multiple records at a time.
Press Next Press Next Press Finish.
You will get this screen.
The internal box is
Text Items called frame.
Complete Grey Window is known as CANVAS.
Change the name of canvas to sample_can. Go to the canvas in object navigator.
Select CANVAS105 then right click & go to the property palette change the name.
Save the form in the resources folder with name Sample.
298 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Step 9: - Create Window
The outer most frame is called window. Where we can close, minimise & maximum
button.
Go to the object navigator.
1) Select windows & press plus button (+).
Plus Sign
2) Change the name of the window107 to Sample_win
299 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Right click on the window 7 then go to the property palette
Now we have created new data block, canvas & windows.
Step 10: - Attach canvas to window
Right click on sample_winwindow & go to the property palette
We will get an option primary canvas. We need to select our canvas sample_can from
the drop down list & press Save.
300 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Step 11: - Attach window to canvas
Select our canvas sample_can from the canvas option & right click open the property
palette. We will get the option window. Select our window sample_win
Step 12: - Provide Subclass information
We provide the subclass information for the look & feel of the standard form.
Component Sub Class information
Data Block BLOCK
Item TEXT_ITEM
Canvas CANVAS
Window WINDOW
301 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Total now we have 6 data blocks 5 are the standard one & 1 is the created by us.
The entire standard data blocks are having red arrow mark but which we have
created is not having arrow mark because of subclass information. When we provide
subclass information to the AP_INVOICES_ALL it will automatically get the red arrow
mark.
Like this all the things which we have created are not having red arrow mark. Red
arrow mark indicated the sub class information.
1) Provide subclass information to data block
Right click on AP_INOVICE_ALL & go to the property palette. Select subclass
information option & go to the property class. For the data block subclass
information is BLOCK.
302 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Press Ok.
Now we will see the red arrow mark on our data block.
2) Provide subclass information to Items
Extract the data block in this we will see the items & extract the items you will get 4
items.
Items
No item is having red arrow mark.
Right Click on any item & open property palette
Select Subclass information as TEXT_ITEM.
303 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Like this we need to do for all the four items & all will get the red arrow mark.
3) Provide Subclass information for canvas.
Open property palette.
Select CANVAS as subclass information for canvas.
304 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
4) Provide Subclass information for windows.
Open property palette.
Select WINDOW as subclass information for window.
305 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
This is how we need to give subclass information.
Step 13: - Change close_window procedure in app_custom Package
App_Custom package is available in program unit. Close_window procedure is
whenever user clicks close button then form needs to be close. If we don’t change
the code form is not close with close button.
As we know package is of two files package specification & package body.
Open package body.
306 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
It looks like this. We need to scroll down. Clear mention that following code must not
be modified we can edit the below mention code.
We can edit this code. They have given a generic name we need to mention our form
name& Compile the form.
307 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Step 14: - Transfer the FMB file to the server (AU_TOP/forms) using WINSCP.
Step 15: - Generate the FMX file using Putty.
Login putty & paste the below mention code using right of the mouse & press enter.
frmcmp_batchmodule=/d01/oracle/VIS/apps/apps_st/appl/au/12.0.0/forms/US/sam
ple.fmb userid=APPS/apps
output_file=/d01/oracle/VIS/apps/apps_st/appl/ap/12.0.0/forms/US/sample.fmx
module_type=form batch=yes compile_all=special
The Complete line need to be in a single line on notepad & then copy paste done on
putty.
308 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Step 16: -Register the FMX file as form in oracle apps
Select Application Developer Application Form.
Mention form name, Application (Module) & User Form Name is same as form name.
Press Save.
309 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Step 17:- Form as Function
We already learn that every form is considered as function at page number 20 when
we are learning about menu creation.
Select Application Developer Application Function
Enter Function Name & User Function Name is same as function name.
Go to Properties Tab.
Select Type as Form
310 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Go to Form Tab.
Select the form which we have created in Step 16 (Sample).
Press Save.
Step 18:- Create a new Menu& attach a function
Creating a new menu need to select system administrator
Security Application Menu
Press Save.
311 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Step 19:- Create a Responsibility & Attach a menu.
Press Save.
Step 20: - Create a New user & attach a responsibility.
Press Save & Login with new user.
In real time we need to attach the form with the existing menu.
312 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Calling Concurrent Program from Menu
Till the time we have submit the entire concurrent program from the view Option.
We can add the mostly used concurrent program in our menu.
Create Concurrent program.
Register concurrent program as Function.
Select Application Developer Application Function
Enter Function Name & User Function Name is same as function name.
Go to Properties Tab.
Select Type as Form
313 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Go to Form Tab.
In the above example we have select the form which we have register in oracle apps.
In this we need to select Run Reports.
In the Parameters Option we need to mention our concurrent program short name &
Application Short name.
CONCURRENT_PROGRAM_NAME="WORLD_PRO"
PROGRAM_APPL_SHORT_NAME="INV"
Press Save.
WORLD_PRO is the short name of the concurrent program
INV is the short name of the Application.
314 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Attach the function with the menu.
Check your user.
It is sample look like form. Click on the concurrent program.
315 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Form Personalization
Making changes to the standard form is called Personalization.
Example 1: - Make optional Field mandatory.
Every mandatory field colour is Yellow.
This is the form. You can see that revision is not a mandatory field.
1) Find the block name and field name.
316 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Block Name: - B_BILL_OF_MATLS
Field Name: - REVISION_MIR
2) Make field mandatory
Select the revision field
317 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Mention the description as per the task & sequence number
Go to the Action Tab.
Mention again sequence number & target object need to mention
blockname.fieldname
318 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Change property name to required.
Property name is very important as a developer we need to guess right property is
used for the task. We have a lot of properties.
Select Values as True.
Press Apply Now& Press Save button.
With this we can change the field to mandatory & field colour is change to yellow.
319 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Example 2: - Change the prompt.
Prompt is something like label
Change the prompt Alternate to Alternate item.
Find the block Name & field name of alternate field with the same process which we
used in first example.
Select Alternate field Help Diagnostics Examine
Block Name: - B_BILL_OF_MATLS
Field Name: - ALTRENATE_BOM_DESIGNATOR_MIR
Select Alternate field
320 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
We get this screen because we have already done one change on the form.
We need to add sequence number & description as per our task.
Go to the Action tab
Mention sequence number & Target object. Select Property Name as PROMPT_TEXT
Press Get value. Will get the current prompt. Current prompt is alternate.
321 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Change the prompt as per your need we are going to change alternate to alternate
item.
Press Apply Now & Save the form.
Example 3: - Hiding the field.
322 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
In this example we are going to hide the revision field.
Select the revision field.
Find the block name & Field Name with the same process.
Block Name: - CONTROL
Field Name: - REV1
Select revision field.
Enter the sequence number & description
Go to the action tab.
Enter the sequence number & Target object.
Target object is objectname.fieldname
323 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Select Property Name as Displayed & value as false.
Press Apply now & save the form.
324 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
How to disable the form personalization.
Remove the enabled check box.
Save the form. Close the form & reopen the form. The Revision field is back in the
form.
325 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Example 4: - Increase the Width of the field.
In this example we are going to change the width of the alternate field.
Find the block name & Field Name.
Block Name: - CONTROL
Field Name: - ALTERNATE2
Select Alternate field.
Add Sequence number & Description.
Go to the action tab.
Add Sequence number & Target object.
326 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Select property Name: - Width
Press Get Value.
Enter Value as 1.5
Press Apply now & save the form.
327 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Example 5: - Hint Message
Add a Hint Message in Revision field when we put cursor on revision field need to get
a hint message Enter Revision information
Find block name & field name
Block Name: - CONTROL
Field Name: - REV1
328 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Select Revision field.
Enter Sequence number &Description
You can see by default trigger event is when new form instance but for this example
we need to use When new item instance.
Enter Target object.
Blockname.fieldname
329 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Go to the action tab.
Enter sequence number
Select message type as hint & enter message text as per the requirement.
Press Validate & save the form.
Hint Message
330 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Example 6: - Change the position of the button
Form Name
Change the position of the find button from right side to the left side.
Till now first step we have find block name & the field name but find is a button we
are not able to find the block name & field name. So this we directly go to the second
step. But we need to know the form name (FIND ITEM WHEREUSER)
Enter Sequence number & Description
Go to the action tab.
331 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Enter sequence number.
Generally we enter block name & field name but for this we don’t have.
We can select with select by text button.
We are dealing with button & type should be button. Total we have 2 buttons clear &
find but we need to change the position of find button select find button & press ok.
For previous examples also we can select block name & field name by select by text
button.
Left to right or right to left is X position.
Up to down or down to up is Y position.
Property we need to select X_POS& press get value.
Enter value as 1 & press apply now & save the form.
332 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Example 7: - Calling the form from special menu
Enter the sequence number & Description.
Trigger event needs to be when new form instance
Go to Actions tab.
Enter sequence number & select type as menu.
Menu Entry: Menu 1 to Special 15 Is for tool special menu
Special 16 to 30 is for bills special menu
Special 31 to 45 is for components special menu
333 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Enter Menu label.
We want to open this form.
Select Enterprise asset management, vision operations Assets Asset Statuses
Note down the function name: EAM_EAMASHDW
334 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Go the existing form.
Open personalization
Trigger event need to be same as menu entry which we have selected in the
sequence number 10.
Go to action tab.
Enter Sequence number & Select Built in.
In Built in type select launch a function.
335 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Enter function code as EAM_EAMASHDW & Press tab button system will pick the
function name.
Save the form , Close the form & open again.
Click on training menu
336 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Like this we can open the two forms at a time & we can use it.
We can call the concurrent program also in the special menu in built in type we need
to select Launch SRS Form.
Level of personalization
It’s the level that defines at what level the forms personalization is being applied. If
Level is 'Forms' then all the functions under that forms will be impacted. If Level is
function then the forms personalization will have its impact only when you are
working on that function inside the form.
Context of Personalization
1) Industry
2) Site
3) Responsibility
4) User
Example 8: - Convert Full Form In query mode
For this example I have created one coffee user.
Open the user creation form
337 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Note down the block name.
Click on User name & check the block name.
We are going to user & select our user (coffee).
338 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Go to Actions
Select block as object type.
Enter block name in target object.
We want to restrict the user so we cannot insert & update the user.
We are selecting update_allowed & insert allowed.
339 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Press Get Value.
Change the value as False.
Like we need to do for update_allowed
340 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Save the form.
Form has been converted with grey fields.
But still we are able to insert the responsibility to the user.
Now find the block for the direct responsibility.
341 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Now you can we are not able to insert any responsibility.
Disabled the personalization
Example 9: - Initial value (Default Value)
On the same user creation form.
Find the block name & Field name for User name.
342 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Enter Sequence Number & Description
Select Context level as User.
Change Trigger Event to When New Record Instance & enter block name.
Go to actions tab.
Select property name as INITIAL_VALUE & enter the value as which user we want to
show when we open the form.
343 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Example 10: - Restrict LOV
Select General Ledger Journals Entry New Journals
Select Category Field.
You will see we have lot of LOV in the category field. We just want only two fields
Adjustment & Manual
Find the form Name.
344 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Form Name: GLXJEENT
Get this form FMB File to the client machine from the AU Top.
Open FMB File in form builder tool.
Find your canvas & open your canvas.
Our canvas is HEADER.
Your Block name is your canvas name.
345 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Double click on the field then property palette window will open & find the list of
values attach to the field.
Find USER_JE_CATEGORY_NAME List of Values (LOV).
Open Property Palette of the LOV & Find Record Group.
346 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Find USER_JE_CATRGORY_NAME Record Group.
Open Property Palette of the Record Group.
You will get the SQL Query used for the lov.
We will get this Query.
Select je_category_name , user_je_category_name , description from
gl_je_categories_v order by user_je_category_name;
The Query will get is displaying the data.
347 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
We want to display only two Rows.
Create your SQL Query.
We cannot change the columns name. We can only filter the rows.
Select je_category_name , user_je_category_name , description from
gl_je_categories_v where user_je_category_name in ('Adjustment','Manual')
Order by user_je_category_name;
First Columns is Hiding from the oracle.
Go to Actions Tab.
Enter Sequence number & Select Type as Builtin.
348 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Select Builtin Type as Create Record Group From Query.
In the argument enter your sql query.
Mention any name in the group.
Enter Sequence number & Select Type as property.
Select Object Type as LOV
349 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Target object is field name.
Property Name is Group_name & in the value we need to enter the group name
which we have mention the built in type.
Save the form & reopen the journal form.
Now check the Category Field.
Example 11: - Create message & Show the message before form open.
Go to Application Developer Applications Messages
Enter Name, Language, Application & Message which we want to display.
We want to display the message when a user open that forms it will show his
username & organization id.
THIS IS A TEST MESSAGE FOR USER NAME &USERNAME AND ORG ID &ORG_ID
350 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Go to Purchasing Responsibility.
Purchase Orders Purchase Orders
Execute a procedure to set the Responsibility & Message Name
FND_MESSAGE.SET_NAME ('INV','BB_TEST')
Enter Sequence Number & Select Type as Builtin
351 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
FND_MESSAGE.SET_TOKEN('USERNAME',FND_PROFILE.VALUE('USERNAME'))
Enter Sequence Number & Select Type as Builtin
FND_MESSAGE.SET_TOKEN('ORG_ID',FND_PROFILE.VALUE('ORG_ID'))
Enter Sequence Number & Select Type as Message.
Select Message Type as Show.
=FND_MESSAGE.GET
Save the form & open the purchase order form.
352 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Change Message Type to Hint.
Now Open the Form.
Change Message Type to Error.
Change Message Type to Warn
About FND Messages
https://selfonlinetraining.wordpress.com/fnd-messages-in-oracle/
Example 12: - Restrict User to Select Values from LOV.
Go to Inventory Responsibility
Transactions Miscellaneous Transactions
We want to restrict the user not able to select Miscellaneous Recpt (RG update) &
Miscellaneous Receipt.
353 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Change Trigger Event To When New Item Instance.
Trigger Object we need to mention any of the field block name & field name.
Condition :MISC_TRX.TRANSCTION_TYPE IN ('Miscellaneous receipt' , 'Miscellaneous
recpt (RG update)')
Go to Actions Tab.
354 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Enter Sequence Number & Select Type As Built in
Enter Sequence Number & Select Type As Message
Save the form & Open the Miscellaneous Transactions Form.
Select the LOV which we have restricted.
Example 13: - When Validate Record
Go to Payables Responsibility
Invoices Entry Invoices
Find the block name & Field name of Invoice date
355 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Find the block name & Field name of GL date
We want invoice data & GL date needs to be of the same month.
Go to Personalization
Select When Validate Record in the trigger event
Trigger object needs to the block name (INV_SUM_FOLDER)
Enter the condition :
to_char(:INV_SUM_FOLDER.GL_DATE,'MM') <>
to_char(:INV_SUM_FOLDER.INVOICE_DATE,'MM')
356 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Go to Actions Tab.
Enter sequence number & select type as Message.
Select message type as per your requirement & enter message as per your client.
Save the form & open the invoice form.
Select Old invoice date.
Select Current Month GL Date
357 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Enter other details & try to save the form.
Example 14: - When new block instance
When we block instance we are using when we are moving from one block to another
block.
Open the user creation form
Find the block name of the user field.
Find the block name of the Direct Responsibility.
358 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
One page can have multiple blocks.
If we move from one block to another block that time when new block instance
trigger will get execute.
Go to Personalization.
Enter sequence number & description.
Select trigger event needs to be when new block instance
Trigger object needs to be when we have click on that block that it will execute.
Select user_resp as trigger object.
Go to Actions tab.
Enter sequence number & Type as message.
Select message type & enter message text.
359 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Create new user & try to select any responsibility.
Whenever we do any personalization the Base table affected is
FND_FORM_CUSTOM_RULES
FND_FORM_CUSTOM_SCOPES
FND_FORM_CUSTOM_ACTIONS
FND_FORM_CUSTOM_PARAMS
FND_FORM_CUSTOM_PROP_VALUES
FND_FORM_CUSTOM_PROP_LIST
Custom PLL
Custom Library (custom.pll) allows extend/customize Oracle Applications form
(Oracle Form) without changing or modifying Oracle Applications code.
PLL stands for Program Link library.
Library collection of code & collection of code is in the form of procedure & functions.
Means custom.pll is collection of procedure & functions.
Collection of procedure & function is also knows as package.
Custom.pll is a package.
Custom.pll is also known as custom library.
Custom.pll is used to implement zoom functionality for the required form.
360 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Using zoom button we can call the 2nd form, so that user can work with both the
forms.
Custom.pll is available in AU_TOP/resource.
We already know for getting the file or transfer the file to server we need to login
with winscp.
Create a new folder in local machine.
Login winscp & transfer the custom.pll & custom.plx file from AU_TOP/ Resource
folder to local folder.
When we compile the pll file we will get the plx file.
There are more than 2000 pll files but we are not supposed to disturb any file only
file we can change is custom.pll.
Take backup for both the files in another folder so if we have done any mistake so we
have the backup
361 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Open a form
Zoom button means is not form size is bigger. Zoom butoon is used to calling the
second form at a time. In oracle apps if you open one form & try to open the second
from first form is automatically closed. With this you are able to open second from.
Till now Zoom button is disable. First we need to enable the zoom button.
Zoom Button
First we need to know on which form we need to enable the zoom function we are
going to enable the zoom function on above form.
362 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Find the form Name.
Help About oracle application
Form name: - INVTOMAI
Find Block Name Like we have done in form personalization.
Help Diagnostics Examine
Block Name: - TOMAI_MAIN_HEADER_BLK
363 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Open Form builder tool
Select open an existing form & Select custom.pll file.
In the above we have mention that custom.pll is a package.
Package is having two files package specification & package body.
Open package specification to see how many procedure & function are there in
package.
Custom package is collection of 2 functions& one procedure.
Function zoom_available return Boolean;
Function style return integer;
Procedure event;
In this example we are trying to enable planning to enable the zoom button.
364 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
So we need to changes in zoom_available function & we can do change in code in
package body.
Open custom package body & go to the function Zoom_available.
If we need to
enable the
zoom button
we need to
copy this code
& make
changes as per
the
requirement
Real Code
In the code we need to mention only form name & block name.
Compile the pll file & save the file.
If we want to enable the zoom button we need to transfer the pll file into the server.
365 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Completely Logout the oracle apps & transfer the pll file into the server.
Login winscp & transfer the custom.pll file to AU_TOP/ Resource folder from local
folder.
Custom.pll is updated but custom.plx file is still the old one for this we need to
generate the plx file in the putty environment.
Login putty.
Run the below mention command in putty to generate the custom.plx file.
frmcmp_batch
module=/d01/oracle/VIS/apps/apps_st/appl/au/12.0.0/resource/CUSTOM.pll
userid=apps/apps
output_file=/d01/oracle/VIS/apps/apps_st/appl/au/12.0.0/resource/CUSTOM.plx
module_type=LIBRARY batch=yes compile_all=special
Important: - Run the above command in one line.
Copy Paste the command & press enter.
366 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
You can check the winscp the AU_TOP/ Resource for the custom.plx file is generated
& date is changed.
Login oracle apps & open the Move orders form.
Zoom button is enable
After you click the zoom button nothing is happen because we didn’t mention the
about the second form details.
Calling the second form
Open inventory Transaction Sub Inventory transfer
Find Function Name for the Sub inventory transfer form.
367 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Function Name: - INV_INVTTMTX_SUBINV
After know the function name Need to change in event procedure.
If we need to open the second form with zoom
button we need to copy this code & make changes
as per our requirement.
Real Code
368 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
https://sites.google.com/view/callingsecondform/
Compile the code & save the code.
Logout of oracle Apps.
Transfer the custom.pll file into the server & generate the custom.plx file like we have
done on the above steps.
Open the Move orders form & press zoom button.
369 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Now you can see the 2 forms are open at a time in oracle apps.
Event procedure allows us to execute our own code for the specific events.
1) ZOOM
2) WHEN – NEW – FORM – INSTANCE
3) WHEN – NEW – BLOCK – INSTANCE
4) WHEN – NEW – RECORD – INSTANCE
5) WHEN – NEW – ITEM – INSTANCE
6) WHEN – VALIDATE – RECORD
370 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
FLEX FIELDS
These are special fields in oracle apps. With Flex Fields we can add the new fields in
the forms.
Two types of flex fields
1) Descriptive Flex Fields (DFF)
2) Key Flex Fields (KFF)
Descriptive Flex Fields (DFF)
1) DFF are used to capture additional Information.
2) It is collection of segments.
3) Each segment value will be stored in attribute columns of the base table.
4) We can identify DFF by using [ ]
Example 1: - Customizing Descriptive Flex Fields (DFF).
Go to Inventory, Vision Operations (USA) Items Master Items
Descriptive Flex Fields
We have seen already that this from is used to create the items.
371 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Click on Descriptive Flex Fields.
Descriptive Flex Fields Form name
Segments
We want to create item Vivo V95 & client need one more option of Card slot& oracle
is not providing card slot option in any tab.
Note down the Descriptive Flex Fields Form name (Items).
Switch to System Administrator
Application Flex Fields Descriptive Segments
Go to Query Mode (Press F11).
Enter Items In Title Option.
372 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Uncheck Freeze flex field definition Option.
Click Segments.
Add New
373 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Press Add New Button.
Enter the Sequence number, Name, Window prompt is same as name.
Every Segment is need to attach with attribute column of the base table. You can
select any attribute for the segment.
Go to open Button.
Uncheck required option.
We can also create the value set & attach the values set with segments.
Save the form.
374 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Freeze the form.
Save the form &Compile the form.
Again open the Master items form& check on flex field’s option.
To check we can create a new item VIVO V95.
Save the form.
375 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Example 2: - Enable the Descriptive Flex Fields (DFF).
Go to Order Management Super User, Vision Operations (USA).
Orders, Returns Sales Orders
If you click on Flex Field option no form is open because flex field option is disable.
Step 1: - Find the block name & Field Name for Flex Field
Click on flex field option & go to Help Diagnostics Examine.
376 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Note down the Block Name (Order) & Field Name (DF).
Change Block Name as $DESCRIPTIVE_FLEXFIELD$ & Field as ORDER.DF.
ORDER.DF is Descriptive Flex Fields BLOCK.FIELD name.
Additional Header Information is Descriptive Flex Fields form name.
Order Management is Application.
Switch to System Administrator
Application Flex Fields Descriptive Segments
Go to Query Mode (Press F11)
Search for Title (Additional Header Information)
377 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Unfreeze the flex field definition & go to segments
Fill the form as per requirement of the client.
Press Open to uncheck the required option.
378 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Save the form.
Freeze the form.
Save the form & Compile the form.
Open the order management sales order form.
By default you will get one field extra. To hide we again need to open the flex field
form in system administrator.
Unfreeze the flex field definition.
379 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Uncheck Displayed option.
Save the form
Freeze the form.
Save the form & Compile the form.
Open the sales order form.
380 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Example 3: - Context Field Value.
Context Fields Value is used to define multiple structures in DFF.
Like we if enter any details in website we select our country India then after selecting
India we are able to see our states name if we select Australia we are able to see all
the Australia cities name details.
Go to Purchasing, Vision Operations
If you click on Flex Field option no form is open because flex field option is disable.
Step 1: - Find the block name & Field Name for Flex Field
Click on flex field option & go to Help Diagnostics Examine.
381 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Note down the Block Name (LINES) & Field Name (DESCRIPTIVE_FLEX).
Change Block Name as $DESCRIPTIVE_FLEXFIELD$ & Field as
LINES.DESCRIPTIVE_FLEX
LINES.DESCRIPTIVE_FLEX is Descriptive Flex Fields BLOCK.FIELD name.
Requisition Lines is Descriptive Flex Fields form name.
Purchasing is Application.
Content Field Prompt Item Details
Content Field Values Segments
Sub Inventory
Item Location
Inventory
382 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
On Hand Qty
Item Quantity Reserved Qty
Available Qty
Product
Item Category
Sub Product
The above details we need in our DFF.
Switch to System Administrator
Application Flex Fields Descriptive Segments
Go to Query Mode (Press F11)
Search for Title (Requisition Lines)
Press CTRL + F11.
Un Freeze the Structure.
383 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Change Content Field Prompt to Item Details.
Enter the Context Field Values.
Check the Displayed Option & if you want to make the field mandatory then check
the required option also.
Click on Segments.
384 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Enter the Segments details & Select Value Set.
Save the form.
Enter second context field value.
Click on Segments.
Enter the Segments details & Select Value set.
385 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Save the form.
Enter third context field value.
Click on Segments.
Save the Form.
Freeze the Form & Press Compile.
386 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
After press Compile One Concurrent program is running.
Switch the Responsibility to Purchasing, Vision Operations.
Open form.
Click on DFF Field.
Click on Item Details three dots.
387 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
We get the Context Field Value
Select Item Category.
Change Item Details to Item Location.
Change Item Details to Item Quality.
Delete Context Field Values
We already know our DFF form title (Requisition Lines).
388 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
We cannot delete the Context Field value from the front end. We can only disable the
context field value. So now we are going to delete the context field value from the
backend.
Step 1. We need to find the DFF form name.
Go to Application Developer Responsibility.
Flex fields Descriptive Register
Search your form title.
We get the DFF form name (PO_REQUISITION_LINES)
Application is purchasing.
Step 2. Find the Application ID.
We can find the application id from fnd_application_vl table.
389 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Application id is 201.
To delete the context field value we use pre-defined package
(fnd_descr_flex_contexts_pkg ) & in the package we have one procedure to delete
the context field value is delete_row.
Code for Delete row procedure
Procedure DELETE_ROW (
X_APPLICATION_ID in NUMBER,
X_DESCRIPTIVE_FLEXFIELD_NAME in VARCHAR2,
X_DESCRIPTIVE_FLEX_CONTEXT_COD in VARCHAR2
);
Step 3: Context field Value Code.
Go to Application Developer Responsibility.
Flex fields Descriptive Segments
Search your DFF form Title.
390 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Context Field Values Code
Step 4: Delete the Values.
Below is the code to delete the context field values.
Begin
fnd_descr_flex_contexts_pkg. delete_row (X_APPLICATION_ID,
X_DESCRIPTIVE_FLEXFIELD_NAME, X_DESCRIPTIVE_FLEX_CONTEXT_COD)
End;
Begin
fnd_descr_flex_contexts_pkg.delete_row (201,'PO_REQUISITION_LINES','Item
Category');
End;
We need to call the commit after the above code.
391 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Like this we need to delete all three Context field values code which we have created
in the previous example.
Key Flex Fields (KFF)
1) Used to store important information.
2) It is collection of segments.
3) Each segment value will have LOV.
4) Collection of segment values is called code combination.
Key Flex Fields
Click on Key Flex Field We will see segments with lov.
392 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Segments
Go to General Ledger, Vision Operations (USA)
Journals Enter
Click on New Journal.
Actual Form
Click on Account.
In account we have just one segment but we need 5 segments like company, division,
location, department & account. It needs to be look like this.
393 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Step 1) Creating a Period Type
Go to General Ledger, Vision Operations (USA)
Enter the Period Type (Name) & Periods we have in a calendar.
In oracle apps we have 13 periods (12months & one Adjusting Period) that we have
already discussed when we learn how to create a calendar.
Save the form.
Step 2) Creating a Calendar
Go to General Ledger, Vision Operations (USA)
394 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Create a new calendar.
Save the Calendar.
When you try to close calendar form they will ask you to run the concurrent program
press the current button & ok. You can check one concurrent program is running.
395 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Step 3) Define the currency.
We don’t need to define the currencies because oracle has created all the currencies
for us.
Go to General Ledger, Vision Operations (USA)
Setup Currencies Define
Go to Query Mode & Search (%) Press CTRL+F11.
Step 4) Creating Chart of Accounts
Charts of accounts are all about to defining companies, divisions, locations,
departments &account.
Go to General Ledger, Vision Operations (USA)
https://selfonlinetraining.wordpress.com/list-of-key-flex-fields/
396 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
For every form there is one flexfield title & for which form we are creating flex field is
realted to account.
Query for Flexfield Title: - Accounting flexfield
Add New Chart of account
In chart of account we need to create 5 segments.
Create chart of account as per requirements.
397 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
We already discuss every segments have LOV. We need to create a value set for LOV.
Press Value Set.
We need to create 5 value set for every segments
Value Set for Division.
After Create values set. Save the form.
Value Set for Location.
398 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Value Set for Company.
Value Set for Department.
Value Set for Account.
Attach the value set as per the segments.
399 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Save the form.
Flex Field Qualifiers.
Qualifiers are words that explain the functions performed by a particular segment.
There are six flex field qualifiers. They are:
Cost Center Segment: - This attribute is used to identify the cost center segment.
Natural Account Segment: - This attribute is used to identify the natural account
segment.
Balancing Segment: - This attribute is used to identify the balancing segment. This is
typically the company segment.
Intercompany Segment: - This attribute is used to identify the intercompany segment
400 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Management Segment: - This attribute is used to identify the management segment.
Secondary Tracking Segment: - This attribute is used to identify the secondary
tracking segment to process income statement closing, translation and revaluation.
Click on company & press Flex field qualifiers.
All the general ledger entry should balance at company segment. So select balancing
segment.
Save the form.
Click on Accounts & press Flex field qualifiers.
All the accounts are coming under expendure.So select balancing segment
Save the form
Freeze flex field Definition
401 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Compile the form Two Times.
Again one more concurrent program is running in backend automatically.
We cannot delete Chart of account but we can change the title & we can also change
the window prompt for segments or we can disable the segments.
Step 5) Adding Values for Segments
Go to General Ledger, Vision Operations (USA)
402 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Select Application as General Ledger.
Title as Accounting flex fields.
Structure is AA_COA (which we have created in step 4).
Segment we need to select all one by one because we need to add the values in all
the segments.
First we select company Segment & Press find.
We will make three companies Tata Steel, Tata Motors & TCA. Give the values also.
403 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
In this example we have very less company & account we can build manually if we
have lot of company & accounts we can need load the data by using inbound
interface & make the table value set & attach the values set with segments. Then we
no need to enter the values in segments.
Go to Values, Hierarchy, Qualifiers tab
Select Qualifiers as Yes for all the values Except Accounts.
404 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Flow the same steps for Division, Location & Department.
Division
It is good practice to create first one no division because some companies don’t have
any division.
405 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Location
Departments
Accounts
Allow Budgeting and Allow posting again we need to select as yes but with this we
need to select account type and for every Description we need to select account type
as per the options available.
In accounts we are getting extra options because we have selected nature account.
406 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Retained Earnings
We need to select Ownership/Stock hold. Retained Earnings is used for partners in
the company. Retained earnings are profit which we need to share with the
shareholders.
Rent & Salaries
We need to Select Expense.
407 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Interest Received
We need to select Revenue.
Cash
We need to select Asset.
408 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Save the form.
Step 6) Accounting Setups
Setup Financials Accounting Setup Manager
Accounting Setups
Click on Create Account Setup
409 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Click on Create Legal Entity.
We need to create 3 legal entities for each company & all the 3 legal entities are
having one primary ledger.
Select Territory.
Enter Name, organization name & legal entity identifier.
Enter Pan Number.
Create the address or Select Existing address.
410 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
We are going to create address.
Select Create new Address Option.
Press Apply& again click on Create Legal Entity.
Now we are going to create 2nd legal entity for the second company & Select the
existing address.
Press Apply & again click on Create Legal Entity.
411 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Now we are going to create 3rd legal entity for the Third Company& Select the
existing address.
Like this we want to create legal entity for all the companies for one group of
company.
Click on apply then next.
Creating Primary Ledger.
412 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
In primary Ledger we need to attach our calendar, chart of accounts & Currency.
Combining all these things is primary ledger.
Click on next.
Press Finish.
Note: - Before Press Finish Check your details after this you are not able to do any
changes in primary ledger.
Click Define accounting Options
Now we need to add the legal entity.
413 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Click on Add Legal Entity.
Click on Add legal entity & Search for your company.
Select all the companies one by one you have create Select & Press Select.
414 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Now we need to add the segments for the companies which we have created.
Click on Update balancing Segments values for Tata Steels & then for other legal
entity also
415 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Click on Add Balancing Segment value (is the first Segment).
Press go.
Select Tata Steel & Press Select Button.
Select Start Date & Press Apply.
416 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
For Tata Steel we have updated our balancing segment value like this we need to
update for Tata motor & TCA.
Update primary ledger
With this we have updated balancing segment for the Legal entity.
Update the Primary Ledger
It is a four step process.
We No need to do in step one so press next.
417 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Short Name Created by Oracle Team
Automatically we can also change.
First Month we need to open &
count of Next month open
automatically.
In this from we need to attach our retained earnings accounts.
418 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Retained account should be mapped on company level.
Select option as below mention image.
Click on create.
Select code combination & press Select.
419 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Press Next for Step 3.
No need to do on step 3.
Press Next for Step 4.
420 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
In this form we just need to check the option we have selected are correct or not. If
option are not correct Press Back & change the option otherwise press finish.
Press complete button.
Press Yes.
Press Return to accounting Setups.
421 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
With this we have created our key flex field now we created new responsibility & new
user.
Step 7) Create New Responsibility
Creating the new responsibility with General Ledger Module.
Menu (GL_GIS_SUPER_USER).
Step 8) Link Responsibility to Primary Ledger.
Primary ledger means we need to attach currency, calendar & Chart of account.
In system administrator responsibility.
Profile System
422 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Go to Responsibility & Select your responsibility.
Find Profile Name as GL Ledger Name.
Press Find.
Select your responsibility.
423 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Save the form.
Step 8) Create New User.
Create a new user & attach the New Responsibility & System Administrator.
Step 9) Login with New &Open current Period
Indirect Responsibility
424 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Go to AA_GL_Responsiblity
Setup Open/Close
We are getting our primary ledger.
Press Find.
Press open.
Press Yes.
Open for Next Month also.
425 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Press Open period then select your next month.
Press Open.
You can also see two concurrent programs are running in backend automatically.
Step 10)
Go to Journals Enter New Journals
426 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
You can see that our period is open & one month is open automatically. This is
because when we create primary ledger we have select next month need to open
automatically.
Click on Account.
We can do entries in the form with key flex fields.
427 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
428 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
XML Publisher / BI Publisher
XML Publisher / BI Publisher is a reporting tool.
Advantage
1) Very Attractive reports, as the output will be in the form of pdf.
2) We can develop report in multiple languages.
BI Publisher report development process
RDF File XML File RTF File PDF File
Example 1
Step 1: - First Design the Query and develop the rdf file.
Select Invoice_id, vendor_id, invoice_num, invoice_amount from ap_invoices_all
Where invoice_id = 145054;
429 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Select No template (Oracle Apps doesn’t support reports template)
Save the file as .RDF file.
430 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Step 2: - Transfer the RDF file to server.
In real time we all need to transfer these file in custom top but in our machine we
don’t have custom top folder so we are transferring the file in payables top.
Path - /d01/oracle/VIS/apps/apps_st/appl/ap/12.0.0/reports/US
Step 3: - Register the RDF file as Concurrent program& Get XML File.
Create Executable
Define the Program
After RDF file we need XML file so while defining the program we need to change the
format to XML.
431 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Save the Program.
Find the Request Group.
432 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Place the program in the Request Group
Switch the responsibility and submit the Request
Click on View Output.
433 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Save the File.
Now we have 2 files one is RDF file and another is XML file.
Step 4: - Open Word doc, load xml data into it.
Open MS Word.
Go to File Word Options.
File
Go to Add-ins. Select Word add-ins from Manage Lov.
434 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Press Go Then Ok.
Click Attach.
Browse the file (C:\Program Files \ Oracle \ XML Publisher Desktop \ Template
Builder for Word \ TemplateBuilder_en_US.dot)
435 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Press Ok.
436 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Press Options.
Enable the content & Press Ok.
Go to Add-Ins tab.
Data
Press Data.
Press Load XML Data.
437 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Select our XML File.
Press Open.
Step 5: - Insert Fields& Preview PDF.
Press Insert.
438 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Press Field
Select Field Ony By One & Press Insert.
Adjust the Field as per requirment
These are just fields we need to give headers name.
439 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Save the Report as RTF
Now Click on Preview & then PDF.
440 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Save the PDF File.
Add-Ins option in MS-Word is temprorary we always need to do the same process for
add-ins tab & we can use all the features of MS-Word.
Example 2
Step 1: - First Design the Query and develop the rdf file.
441 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Select Invoice_id, vendor_id, invoice_num, invoice_amount from ap_invoices_all
Where invoice_amount between -100 and 0;
Select No Template Because oracle apps does not support template.
Save the File as RDF File.
Step 2: - Transfer the RDF file to server.
442 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
In real time we all need to transfer these file in custom top but in our machine we
don’t have custom top folder so we are transferring the file in payables top.
Path - /d01/oracle/VIS/apps/apps_st/appl/ap/12.0.0/reports/US
Step 3: - Register the RDF file as Concurrent program& Get XML File.
Create Executable
Define the Program
After RDF file we need XML file so while defining the program we need to change the
format to XML.
443 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Save the Program.
Find the Request Group.
Place the program in the Request Group
Switch the responsibility and submit the Request
444 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Click on View Output.
Save the File as XML.
Step 4: - Open Word doc, load xml data into it.
We have already discussed how to add Add-ins tab in MS word
Load the XML Data same way we have done in first example.
445 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Step 5: - Insert All Fields& Preview PDF.
Select Insert All Fields.
All the Fields are inserted at once. For Each Tag
For Each Tag is created.
For Each tag is behaving like Repeating Frame.
You can see instarting we have F & in end we have E.
Now you will see the diffrence when we you will see the output.
Save the File as RTF file.
Now Click on Preview & then PDF.
446 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
If Client is not happy with this design we need to do changes in RTF file.
BI Publisher report registration process
We can attach our RTF file with concurrent program so end user when submit the
program they will get data in PDF file for this we need to create Data Definition (XML
File ) & Data Template (Design). In this Registration process we are going to use XML
File & RTF File from the example 2.
To Create Data Definition & Data Template we need to select XML Publisher
Administrator Responsibilty in oracle apps.
Step 1) Create Data Definition.
Select XML Publisher Administrator Create Data Definition.
447 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
We are not finding the data definitions so click on create data definition.
Name we can mention any name for our data definition.
Code is very imp. We are getting data from the XML file & we are getting the XML file
from the concurrent program so in code we need to mention concurrent program
short name. In this example conncurent program short name is XML_SAMPLE_PROG.
Application we need to mention as same when we are creating concurrent program.
Then Press Apply.
448 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Step 2) Create Data Template.
Go to Template Tab.
Click on create template.
Name we can mention any name.
Code is same as name . Here code is no point.
Application Again mention the same when we are creating concurrent program.
Data Definition which we have create on step one. Enter the name of that data
definition.
449 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Default output type select as PDF.
File we need to select our RTF file. Browse that file and Attach.
Language we need to mention English. If you want report in any other language you
can select language as per your requirement.
Territory is not important if you want to mention select any terriotory.
Step 3 Switch the Responsbilty & Submit the request.
Go to Payables , Vision Operations (USA).
Now Submit the Request.
450 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Earlier when we submit the concurrent program we get the XML File but now we get
the PDF File. You can see in the above screenshot in layout we have the RTF file which
we have attach with the data template.
Submit the program & Press View Output.
Converting RDF Report to BI Publisher Report
When we want to change the standard report format or client want the standard
report in BI Publisher.
In this we are going to changes in Customer Open Balance Letter Report. This Report
is giving details about balance we have recieived from the customer & This report is
related to receivables.
First understand the RDF file report.
451 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Total Balance Received
from the customer
This output we will get from the RDF File.
This report is build using Report Builder Tool.
The Same Output we can get it in PDF File.
The First thing we need XML File.
452 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Go to System Adminstrator.
Concurrent Program Define
Query for the program.
Change format to XML.
Save the program.
Switch the Responsibility (Receivables) & Submit the Request.
453 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Click on view output.
Save the File as XML File.
Open MS word Add Add-Ins Tab
Select Insert Press Fields.
454 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Move the Fields are per your Requirement.
We can also insert tables in our report.
Go to Insert Table/Form Wizard.
Select the last option because we want the information about invoices.
455 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Move Trx Number, Tranaction Date, Trans amount & Trans Amount Remaining.
Here we get For each tag because this line is having data from then one line.
456 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Save the file as RTF File.
Go to Preview PDF
Save the PDF File.
Register the RTF as template.
Create Data Definitions
Select XML Publisher Administrator Create Data Definition.
457 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Press Apply.
Create Template like we have created in previous example.
Switch the Responsilbity and Submit the Request.
458 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Now you can see RTF File in program.
Add the Parameters & Submit the request.
Click on View Output.
459 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Data Template Feature
Till now are getting the PDF File through Below mention structure.
RDF File XML File RTF File PDF File
In Second Step we need XML File from RDF File . The same step we can do with the
SQL Query also. We can create a SQL Query & get the XML File.
SQL Query XML File RTF File PDF File
For SQL Query we have a syntax.
To Download the SQL Query Click on below mention Link.
https://drive.google.com/open?id=1Wo0EG0u1hGAhiH1YvJZMzSza5viKg9nC
Step 1) Copy the SQL Query & paste on Notepad & Save the file as extension .XML.
460 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Syntax for the SQL is Mention the SQL Statement & in the end we need to mention
every Column name.
461 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
We save the file as XML but you can see it is not like our XML file
The data template is an XML document that consists of 5 basic sections:
1) Properties
2) Parameters : We need to Create Bind parameter query.
3) Triggers
4) Data Query
5) Data Structure
Syntax for Parameter : <parameter name="P_DEPTNO" dataType="character" />
Syntax for Triggers :<dataTrigger name="beforeReport"
SOURCE="xx_emp_pkg.print_params()"/>
Triggers : Triggers are optional elements of data template which are nothing but a
PL/SQL Function or a Procedure which gets executed at specific times during the
execution and generation of XML output. Triggers can be used to perform some pre
and post execution activities like in financial reports where we update some table
columns stating that a particular is row is printed.
In Data Template only before & after report trigger supports.
Step 2) Define the Program
For generating the XML File we already have pre-defined executable.
Executable Name : - XML Publisher Data Template Executable.
Executable Short Name : - XDODTEXE
462 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Define the program & attach the executable short name with the program.
Format needs to be XML.
Save the program
Step 3) Create the data definitions.
Code need to be same as concurrent program short name.
Press Apply.
463 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
In data Template load your XML File which we have create with SQL Query.
Data definitions will only respesent the data. Earlier we attach the RDF file with the
executable So now we are goint to attach the XML file with the data template.
Press Open
464 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Press Apply.
Press Update.
Step 4) Find the Request Group.
Step 5) Place the program in the Request Group
465 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Step 6) Switch the responsibility and submit the Request
Switch the responsbility to Payables.
Submit the Request & Press Find.
Click on view Output.
We get our XML file .
466 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Step 7) Add Add-ins tab in ms word & Load XML Data
Step 8) Insert All Fields & Preview PDF.
467 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
For Each tag is created.
Save as RTF File.
Preview PDF.
Step 9) Create Template.
468 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Step 10) Switch the responsibility and submit the Request
469 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Click on view Output.
BI Publisher Tags.
In the above example we have generate the XML File using Concurrent program. We
can genrate the XML File using report builder tool also.
Create a simple report using report builder tool.
470 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Click on File Generate to File XML
Save the xml file.
471 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Open MS workd.
Add Add ins Button
Click on Insert Table Wizard Next Next
Move all the Columns.
Press Next
In group by Select Dept number.
Press Next.
472 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Save the rtf file & click on Preview PDF
We are not getting Heading for department number . we can add our own heading
also.
We can add Sum of Salary of Each Department Number.
Click on insert Field Select Sal Column
473 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Select Sum from Calculation.
Check on grouping BOX.
Press Insert.
Give the Heading.
474 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Date on Report
We need to add a tag for Date (Date : - <?xdofx:sysdate(‘DD-Mon-YYYY’)?>)
We can change the data format also.
475 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
We can break page also .
For Break page we need to edit the end for each group tag.
Double click on end for each group tag.
Add page break attribute (<xsl:attribute name="break-before">page</xsl:attribute> )
Click on Add Help Text
476 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Add the attribute
<xsl:attribute name="break-before">page</xsl:attribute>
In the above we can see the page number.
We can use group above , Functions feature of our report builder tool.
If we create parameters and trigger in report builder tool it will not work in BI
publisher.
477 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Condition Tag :
If Sal is less than 1000 then it need to display sal in red color.
Double click on sal tag.
Click Add Help Text.
Add If tag : <?if:SAL<1000?> <xsl:attribute xdofo:ctx="block" name="background-
color"> red</xsl:attribute> <?end if?>
Now we the pdf file.
478 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
If employee is having commession it will display Y else it will display N.
Double click on Comm tag.
Click Add Help Text.
Remove the existing Tag.
Enter new if tag (<?xdofx:if COMM='' then 'N' else 'Y' end if?> )
Press Ok. Now view your pdf file.
Like this we have a lot of tags we can use in our xml publisher report :
https://selfonlinetraining.wordpress.com/xml-tags/
479 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
XML File Using PLSQL
Create Procedure
https://drive.google.com/open?id=1Y-98WI7vMRE-qwNk9BnVfKNi6CoF9n23
Create Executable
480 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Define the Program
Create Parameter
Find the Request Group
Add the program into the request group
481 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Switch the Responsibility & Submit the Request
Click on view output
Save this file as .xml
Create RTF File
Create Data Definition
482 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Create Data Template
Switch the Responsibility & Submit the Request
Click on view Output
483 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
XML File Using PLSQL Master Report
Create procedure
https://drive.google.com/open?id=1Aw2lPy9qpzygtQcHGAE3c-L4AsTQT34n
Create Executable
Define the program
Create Parameters
484 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
For organization we are using profile option & uncheck the display.
Find the Request Group
Add the program into the request group
Switch the Responsibility & Submit the Request
Enter the supplier name : = HVAC Express
Click on view output
Save this file as .xml
Create RTF File
485 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
https://drive.google.com/open?id=1wELf4GFaN_xSGdhy6lcuqK48iVA1RE38
Create Data Definition
Create Data Template
Switch the Responsibility & Submit the Request
Click on output.
486 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Images In BI Publisher
Add Image from Local Machine
Go to Insert Picture
Select the image from your machine.
Check the preview.
487 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Add Image from Web URL
Add dummy image in the RTF File.
Right Click on image.
Select size.
Go to alt text
url:{ 'IMAGE URL WITH IMAGE NAME'}
url:{'https://selfonlinetraining.files.wordpress.com/2018/11/capture.png'}
488 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Check the preview.
Add image from Server
Transfer one image to server in oa_media folder.
If you don’t know the path of the oa_media folder.
Login with putty.
Enter cd $OA_MEDIA
Press enter
Enter pwd
/oraAS/oracle/VIS/apps/apps_st/comn/java/classes/oracle/apps/media
489 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Tranfer one image to the server or you can use the existing one also.
We transfer the new image.
Add dummy image in the RTF File.
Right Click on image.
Select size.
Go to alt text
url:{'${OA_MEDIA}/IMAGE_NAME'}
url:{'${OA_MEDIA}/selfonlinetraining2.PNG'}
Register the file as template in xml publisher responsibility & submit the concurrent
program & check the output.
490 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Add image from Table
There is one table fnd_lobs from oracle where we have images.
Create your query which you want to display.
select file_id, file_name , file_data from fnd_lobs
where file_id in ( 123077, 218887, 228998, 229000, 229002);
In the file data column we have images but xml publisher by default cannot handle
the image for this we want to convert those images into text.
CREATE FUNCTION blob_text( p_source BLOB )
RETURN CLOB
IS
v_result CLOB;
BEGIN
DBMS_LOB.createtemporary(lob_loc => v_result, CACHE => FALSE, dur => 0);
Wf_Mail_Util.EncodeBLOB ( p_source, v_result);
RETURN ( v_result );
END blob_text;
/
491 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
https://drive.google.com/open?id=1bwtn4uKjvl8eybVueEbL4PowsZ7Rd37c
Select file_id, file_name, blob_text(file_data) IMAGE_FILE
from fnd_lobs
where 1=1 and file_id in ( 123077, 218887, 228998,229000, 229002);
Create data template file using the above sql query.
https://drive.google.com/open?id=1vWMpZU_nzq1ti2Q-1vMxWlfn3MLNORyB
492 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
For generating the XML File we already have pre-defined executable.
Executable Name : - XML Publisher Data Template Executable.
Executable Short Name : - XDODTEXE
Define the program
Format needs to be XML
Create Data Definitions
Click on apply.
Load the data template file.
493 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Add the program in request group
Switch the responsibility & submit the request.
Check the output.
Create the rtf file.
Check the output.
We are not getting the image .
Now we need to change this text into images.
<fo:instream-foreign-object content-type=”image/jpg” height=”1.25 in” width=”1.5
in”><xsl:value-of select=”.//IMAGE_FILE”/>
494 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Image_file is the column alias name.
Double click on image tag.
Click on word properties.
Click on add help text
Remove this tag.
495 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Now check the output
496 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Before Report Trigger
Before Report Specification
Before Report Body
https://drive.google.com/open?id=1rFFFGlT0Z2cvNl7tRTeFD4RvRWSwm1Bf
Create data template file with before report trigger
https://drive.google.com/open?id=1-p-0udbPFnDY90KkUSxWKE6l-uyTd9-I
497 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
If we are using package in the data template file we need to mention the package
name also.
Register the file as template in xml publisher responsibility & submit the concurrent
program & check the output.
Create parameter also while define the program.
Create rtf file.
Register rtf file
Submit the program & check the output & log.
After Report Trigger
Package specification
498 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Package Body
https://drive.google.com/open?id=1uNcorZZ8Rjk5I5uAAScgXZ_jzU9lLLda
Update the data template file.
https://drive.google.com/open?id=1i0OMyx_ZLZyVX09bg9XT70hHkWrCrn1D
Update the data template file in program & submit the request
Log file
499 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
SUB Template
A subtemplate is a piece of formatting functionality that can be defined once and
used multiple times within a single layout template or across multiple layout
template files.
Create Data Template SQL File.
https://drive.google.com/open?id=1hYl0BgdvGCTLPcI126pERawnVY2NKTcm
Define the program.
We no need to create executable we alredy know we will use pre defined executable.
Executable Name : - XML Publisher Data Template Executable.
Executable Short Name : - XDODTEXE
Change the format as XML.
500 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Create Data Definition.
Select XML Publisher Administrator Create Data Definition.
Attach your sql file with the data defination.
Switch the responsibility & submit the request.
We Will get the our XML File.
501 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Create RTF File. ( Main Template)
Create Data Template
Switch the responsibility & submit the request.
502 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Create SUB Template
Syntax to Create Sub Template
<?TEMPLATE:SUB_TEMPLATE_NAME?>
Sub Template Design Layout -- Content
<?END TEMPLATE?>
We have create two sub template First for Header & Second for Footer.
Sub Template Name
Content we want
to display.
Sub Template Name
We can create multiple sub template in one file & save the file as extenstion (.RTF).
Register SUB Template
Like we have register template in above the same way we need to register the sub
template but we need to Select sub template as Yes & Data Definition name need to
same ( TRAIN_DEF).
Note Down the sub
Template Code
Sub Template
Select as Yes
503 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Import & Call Sub Template In Main Template
Import
We Need to Enter this import command on the top.
Syntax : <?import:xdo://Short_Name.Sub_Template_code.Language.Territory/?>
<?import:xdo://INV.TRAIN_SUB.en.00/?>
INV – Application short name
TRAIN_SUB -- Sub Template Code ( Case Senstive)
EN -- Language ( English)
00 – Territory ( We Didn’t select any territory so we are giving zero).
Call Template
Syntax : <?call:Sub_Template_name?>
We Need to enter this syntax we we want to place our sub template
504 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Call Sub Template
Sub Template Name which we have mention
while creating sub template.
Save the RTF File.
Change your old rtf file with new rtf file.
Switch the responsibility & submit the request.
Sub Template 1
Sub Template 2
505 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Sub Template With Condition
Create Data Template SQL Query with Parameter
https://drive.google.com/open?id=1e95dDWhTw8obvGDQTqBG_gvxM5NUAUGr
Define the program.
We no need to create executable we alredy know we will use pre defined executable.
Executable Name : - XML Publisher Data Template Executable.
Change the format as XML while creating program.
Create Parameters in Program.
Token name needs to be same as Parameter Name.
506 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Create Data Definition.
Select XML Publisher Administrator Create Data Definition.
Attach your sql file with the data defination.
Create RTF File. ( Main Template)
Create Data Template
Switch the responsibility & submit the request.
Create SUB Template
We Will create 2 sub template one for department number 10 & One for department
number 20. Save the file as extension (.RTF)
Sub Template For Department Number 10.
Sub Template For Department Number 20
507 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
If We enter department number 10 while submiting concurrent program then this is
footer needs to display.
Register Both the template as SUB Template & Note down the Template Code.
Import & Call Sub Template In Main Template
We need to import both the sub template .
Import Sub Template
<?import:xdo://INV.TRAIN_SUB.en.00/?>
<?import:xdo://INV.TRAIN_SUB1.en.00/?>
Call the Template With if Condition
Call the Template
<?if:P_DEPTNO=20?><?call:HEADER?><?END IF?>
<?if: P_DEPTNO =10?><?call:Footer?><?END IF?>
Update the new rtf file in data template.
508 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Switch the responsibility & submit the request.
Enter Department Number 10 & Click on View Output.
Enter Department Number 20 & Click on View Output.
in this example we have seen if we submit the concurrent program with department
number 10 & 20 it will print this is header & footer but if we give any other
department number that time if will print only the data present in the table.
509 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
We want if we enter department number 10 & 20 then it will print this is header &
this is footer but if we enter any other department number then it needs to print
some another message so for this we need to use when condition in our rtf file.
Choose- when-otherwise statement
Create one more rtf file with sub template for other department numbers.
Register this file as sub template in xml publisher responsibility.
Import the sub template in the main file like we have done in the above example.
Call the sub template but in this we are using chose when condition.
<?choose:?>
<?when:P_DEPTNO=10?><?call:HEADER?>
<?end when?>
<?when:P_DEPTNO=20?><?call:FOOTER?>
<?end when?>
<?otherwise:?><?call:PHOTO?>
<?end otherwise?>
<?end choose?>
510 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Update the new rtf file in xml publisher responsibility.
Switch the responsibility & submit the request with another department number
present in the table.
511 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
XML Bursting
XML Bursting can be used to split one XML File into multiple XML Blocks. These
individual xml blocks can then be used to generate reports and even use different
layouts. You can also deliver the reports to multiple destinations based on a XML
Element.
Step 1 : - Design the SQL Query & Save the File as .XML in XML Format.
https://drive.google.com/open?id=1PuPj5yuO_UNCj3zhWM9eB_gEBf0rNu5W
We have define the data structure as master details report
Step 2) Define the Program
For generating the XML File we already have pre-defined executable.
Executable Name : - XML Publisher Data Template Executable.
Executable Short Name : - XDODTEXE
Define the program & output as XML.
Step 3) Create the data definitions.
Step 4) Find the Request Group.
512 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Step 5) Place the Program in the Request Group
Step 6) Switch the Responsibility & Submit the Request.
Click on view Output.
Save the File as extension .XML.
Note Down These tags.
XXAR_CLIENT_DET_CASH_PYMT/LIST_G1_CLNT_DET/G1_CLNT_DET
Step 7) Add Add-ins tab in ms word & Load XML Data
Step 8) Create the Design as per our requirement
https://drive.google.com/open?id=1vBwog-jxVD_aldhOwJrli1QyBb7_rTNH
Step 9) Create Template
Step 10) Switch the Responsibility & Submit the Request
Step 11) Create Folder in Server.
Folder Creation Path : -/d01/oracle/VIS/inst/apps/VIS_glo
513 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Step 12) Create Bursting XML File.
https://drive.google.com/open?id=1bCXU0GpmgyozFn7rXKOQ3WylGaW93YfB
Tags
Server
path
Rows Filter
Module Short Name . Template Name Code . Language. Territory (we don’t mention
any territory so we mention 00 )
In output we have mention the complete path of the folder which we have create in
server with File Name for every order number (59219.pdf)
Attach Control File with Data Definitions
514 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Step 13) Switch the Responsibility & Submit the Request.
But Still we get only one file when we click the view output & no file in server folder
which we have created.
Note down the request number of the concurrent program.
Step 14) Run XML Publisher Report Bursting Program
After Run the Concurrent program we need to run one more concurrent program
that is XML Publisher Report Bursting Program . This program wil help us to filter the
PDF Files & in paramter we need to select our concurrent program request id.
Select your request id & submit the request.
Now check the server & transfer the complete folder into the local machine.
515 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
If we want to genarate the rtf to excel we just want a little change in our bursing file.
Change
https://drive.google.com/open?id=1hfExz2feNkOpg70wC52k2biH4xg4hsPl
516 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Xml Bursting With Filter
XML Bursting can be used to split one XML File into multiple XML Blocks. These
individual xml blocks can then be used to generate reports and even use different
layouts. You can also deliver the reports to multiple destinations based on a XML
Element.
Requirement: is to send the pdf as an output to the file server.Dynamic selection of
template at runtime.
Step 1 : - Design the SQL Query & Save the File as .XML in XML Format.
select ppx.nationality, ppx.person_id, ppx.full_name, ppx.date_of_birth,
ppx.email_address, ppx.employee_number,
ppx.marital_status
from per_people_x ppx
where person_id
in
(18640,18672,18678,18682,18688,174,268,372,436,495,582,1099,461,26960,26959,
8923,4717,4784);
517 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
This is the data we get from our SQL Query & you can see we have total 18 rows. 5
from Australia, 5 from Canada, 5 from India & 3 from japan.
Our Requirement is to create different PDF file for every Person & For every country
we need different outlook.
Download the XML File From the below mention Link
https://drive.google.com/open?id=1VoKy32K4NPiW2DEIh_zAyC7qc5OOGUZt
Step 2) Define the Program
For generating the XML File we already have pre-defined executable.
Executable Name : - XML Publisher Data Template Executable.
Executable Short Name : - XDODTEXE
518 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Define the program & attach the executable short name with the program.
Format needs to be XML.
Save the Program.
Step 3) Create the data definitions.
Select XML Publisher Administrator Create Data Definition.
519 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Code need to be same as concurrent program short name.
Press Apply.
In data Template load your XML File which we have create with SQL Query.
Data definitions will only respesent the data. Earlier we attach the RDF file with the
executable So now we are goint to attach the XML file with the data template.
Step 4) Find the Request Group.
520 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Step 5) Place the Program in the Request Group
Step 6) Switch the Responsibility & Submit the Request.
Click on view Output.
521 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Save the File as extension .XML.
Note Down These tags.
/XXCV_BURSTING/LIST_G_USER_NAME/G_USER_NAME
522 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Step 7) Add Add-ins tab in ms word & Load XML Data
Step 8) Insert All Fields & Create the Different style RTF file for the country.
For Australia
For India
523 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
For Canada
For Japan
Save the all the File with extension .RTF.
Step 9) Create Template For every country & Attach their respected RTF file .
Australia Template.
524 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Canada Template
India Template
Japan Template
525 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Step 10) Switch the Responsibility & Submit the Request
You can see that in layout Japan template is get selected automatically because we
have created Japan template in the end. Which ever template we create in the end it
get select with the concurrent program.
Submit the request & view output.
526 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
But Still we get only one PDF File with all the countries data. To seprate the data we
need to create Bursting File& create Folder in serve.Need to attach the bursting file
with our data template which we have created in Step3.
Step 11) Create Folder in Server.
Folder Creation Path : -/d01/oracle/VIS/inst/apps/VIS_glo
527 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Step 12) Create Bursting XML File.
Tags
Folder Path
Rows Filter
Module Short Name . Template Name Code . Language. Territory (we don’t mention
any territory so we mention 00 )
In output we have mention the complete path of the folder which we have create in
server with File Name for every persion (person_id & nationality).(174_CAN.pdf)
In Template Option we need to mention Our RTL File name.
In Filter Details we need to mention on which column system create filter.
Save the Control File as extenston .XML
528 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
https://drive.google.com/open?id=1kyh-KzV1fU-IOUJPQFRZNJpq8-UlwOzJ
Attach Control File with Data Definitions
Query for you Data Definitions
Click on your data definitions.
Click on Bursting Control File.
529 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Step 13) Switch the Responsibility & Submit the Request.
But Still we get only one file when we click the view output & no file in server folder
which we have created.
Note down the request number of the concurrent program.
Step 14) Run XML Publisher Report Bursting Program
After Run the Concurrent program we need to run one more concurrent program
that is XML Publisher Report Bursting Program . This program wil help us to filter the
PDF Files & in paramter we need to select our concurrent program request id.
Select your Request ID from the LOV option.
Submit the Request
530 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Click on View Output.
You are not able to see any thing when you click on view output.
Now check the server total 18 PDF Files are created with person_id & Nationality.
Transfer the complete folder in your client machine so you can see the output.
Canada Template.
531 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Australia Template
India Template
Japan Template
532 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
XML Publisher Password Protect
In Real time when we are received any PDF file from any company for mobile bill or
bank statement all those file are password protected file. We need to enter the
password to open the PDF File.
For Password protect example we are taking the same SQL File which we used in
Bursting program.
Download the XML File From the below mention Link
https://drive.google.com/open?id=1VoKy32K4NPiW2DEIh_zAyC7qc5OOGUZt
Step 2) Define the Program
For generating the XML File we already have pre-defined executable.
Executable Name : - XML Publisher Data Template Executable.
Executable Short Name : - XDODTEXE
533 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Define the program & attach the executable short name with the program.
Format needs to be XML.
Save the Program.
Step 3) Create the Data Definitions
534 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Press Apply
Attach your SQL command file.
Step 4) Find the Request Group.
Step 5) Place the Program in the Request Group
535 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Step 6) Switch the Responsibility & Submit the Request.
Click on view Output.
Save the File as extension .XML.
536 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Note Down These tags.
{/XXCV_BURSTING/LIST_G_USER_NAME/G_USER_NAME}
Step 7) Add Add-ins tab in ms word & Load XML Data
Step 8) Insert All Fields.
537 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Save the File as RTF File.
Step 9) Enable the password (Static password).
File Prepare Properties
538 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Go to Custom.
Enter the Details Below mention Details & Add.
Name: - xdo-pdf-open-password
Type: - text
Values (Password): - 123456
Press Add.
This password is static password.
Step 10) Enable the Security.
In the Custom Option.
Name: - xdo-pdf-security
Type: - text
Values: - true
Press Add.
539 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Press Ok.
Save the RTF File.
Step 11) Preview PDF File.
Go to Preview PDF.
System is asking for Password. Enter Password 123456.
540 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Press OK.
Step 12) Dynamic Password.
Dynamic password is different for every file. When we create the files in XML Bursting
for every file password need to be different & password needs to their person_id.
For Dynamic password we need to enter the XML Tags names in MS-Word Properties.
541 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
{/XXCV_BURSTING/LIST_G_USER_NAME/G_USER_NAME/PERSON_ID}
To change the static password to dynamic password.
Go to File Prepare Properties Custom.
Change 123456 to
{/XXCV_BURSTING/LIST_G_USER_NAME/G_USER_NAME/PERSON_ID}
Press Modify
Press ok.
Now Try to Enter the First Person id (174)
542 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
You are able to view the output.
If you try to enter any other person id you will get the error.
Because file will tag the first Person id as password for this file.
Now try to create the Different RTF Files for Every country & then create Template.
Create Folder in Server.
Create Bursting XML File.
Switch the Responsibility & Submit the Request.
Run XML Publisher Report Bursting Program.
For Every file you will get different password as their person_id.
543 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Translation in BI Publisher
Create Data Template File
https://drive.google.com/open?id=1J2uVAGOECuwmPlqcAYtDHQF4Euienw6R
Define the program
Select output as XML.
Add the program into the request group
544 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Create Data Definitions
Add the data template file in data definitions
Create Template
Check the translatable box.
545 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Click on Export Translation
Save the file as XLF.
Open the file. Update target language to the appropriate locale
Update
target
elements
with
translation
of source
elements
Update the target language & target.
Copy the target name & use Google translation to translate in target language.
546 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
We are going to use French & for French language code is Fr.
547 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Save this file.
Go to Template
Click on upload translations
548 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Upload the update xlf file.
After that when we will submit the concurrent program our output will come in
French language.
549 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Excel Template
Create Data Template SQL File.
https://drive.google.com/open?id=1hYl0BgdvGCTLPcI126pERawnVY2NKTcm
Define the Program using XML Executable.
Select Format as XML.
Attach the program with the request group.
550 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Create Data Definition.
Attach the Xml File.
Open Excel File
First Line always is for Headings.
Select Below line of Empno column.
Go to Formulas tab Click on name manager
551 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Click on New.
In the Name we are getting column name we need to create a tag & enter the tag
name.
Mark cells with XDO_ defined names – necessary for mapping
XDO_ is the required prefix
?EMPNO? is either: the XML tag name
A name used for mapping derived value to the cell
XDO_?EMPNO?
552 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Scope needs to be workbook
Refers to shows sheet name with column name.
Like this we need to create tag for every column.
XDO_?ENAME?
XDO_?JOB?
553 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
XDO_?SAL?
After creating tag for each column we need to create one group tag.
Click on New.
For Group we need to enter tag XDO_GROUP?GROUP_NAME?
We have given the group name as G1_CLNT_DET (XDO_GROUP? G1_CLNT_DET?)
554 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Click on Refers
In the refers it has select only sal column we need to select all the columns.
Create XDO_METADATA Sheet
Each Excel template requires a sheet within the template workbook called
"XDO_METADATA". Use this sheet to identify your template to BI Publisher as an
Excel template.
This sheet is also used to specify calculations and processing instructions to perform
on fields or groups in the template. BI Publisher provides a set of functions to provide
specific report features. Other formatting and calculations can be expressed in XSLT.
Create a new sheet in your Excel Workbook and name it "XDO_METADATA".
555 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Create the header section by entering the following variable names in column A, one
per row, starting with row 1:
Version
ARU-dbdrv
Extractor Version
Template Code
Template Type
Preprocess XSLT File
Last Modified Date
Last Modified By
Skip a row and enter "Data Constraints" in column A of row 10.
In the header region, for the variable "Template Type" enter the value:
TYPE_EXCEL_TEMPLATE
556 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Create Data Template.
Select Type as Microsoft Excel & Default output type as Excel.
Switch the Responsibility & submit the request.
Click on View Output.
557 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Work Flows
Workflow is an automated process of work.
Workflow Builder tool is used to build the workflows.
Workflow is also called as Item type.
To create a new workflow, we need to have Standard Workflow (WFSTD.WFT).
Workflows are used for Approval process and sending notifications to users of Oracle
Apps.
Workflows are saved with extension .wft
In Below mention picture all are standard workflows.
Components of workflows
1) Attributes
2) Processes
3) Functions
4) Notifications
5) Messages
6) Events
7) Lookup types
558 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Example One : - Create a workflow to send "Have a nice day" notification to a
Operations user.
Open Workflow builder
Open
we have already disscused for creating a workflow we need standard workflow
Click on Open .
Select Standard workflow (WFSTD).
559 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Right Click on Standard workflow & select New Item Type
Enter Internal Name & Display Name.
560 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Press Apply & then OK.
After Creating the Item we will get all the components of Workflows.
Create Process
Right Click on Process & then select New Process
You will get a blank window
Create Start Function
Right Click on process screen
Select New Function
561 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
We don't need to create the new function Start function is a pre defined function &
pre defined function is available in standard workflow (WFSTD)
Change Item Name to Standard.
Select Start Function from the internal name.
Press Apply then Ok.
562 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Go to Node Tab
Change Start/ End from normal to Start.
Create End Function
Process is same as we create start function we need to create end function.
Press Apply then ok.
563 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Go to Node Tab.
Select Start/End from normal to END.
Press Apply then ok.
All these Steps Which we have done till now are the common steps for every
workflow.
Create Notification
Right Click on process screen
Earlier we have selected funcation now we need to select notification.
564 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Notification we need to create in our own item.
Enter Notification Internal & Display name.
Press Apply then Ok.
565 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Now we have Start & End Function & One Notification.
Create New Message
We are not able to create message with process screen we need to create message
from navigator screen.
In the message we need to add the message we need to send to operations user.
Right Click on message Component from our item.
566 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Enter Internal Name & Display name.
Go to Body tab.
Enter the Message
567 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Press Apply then ok.
Not We need to Attach out message with Notification.
Double Click on notification.
Select Your message.
568 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Go to Node Tab.
In the node tab we need to give the user name in value to which user we need to
send the notification.
Give Directions
Need to Give Directions from Start to Notification & From notification to End.
Right Click On Start & Drag to Notification.
569 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Same way Click on notification and drag to end.
Close the Process Window and minimise every thing.
Right Click on Standard workflow and click on verify.
570 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Save the Workflow in the database.
Go to File then Press Save as.
Select Database.
Enter your database details.
Press OK.
571 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Close the Workflow Builder & Login oracle apps with user sysadmin & password is
also sysadmin.
Search your workflow and press Go.
Press Run
572 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Enter item Key.
Item Key is needs to be unique every time .
So we enter 1 as item key.
Press Submit.
Ok.
573 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Go to Status Monitor Tab to Check the status of our workflow.
Enter Your Workflow.
Press Go.
Status is Complete.
Go to Status Diagram ( Which we have created in Process)
574 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Logout Sysadmin user
Login Operations user.
Open Your Message.
Press Ok.
575 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
We read our message so it removed from worklist.
Now Again Run your same workflow.
Login with sysadmin
Item key always be unique if we enter the same item key we will get error.
Now try with 2 item key
576 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Press Submit.
Press ok.
Again we get the workflow notification.
Press Full List.
View Select as All Notifications.
You can see one is open and one is closed because first notification we have open
second notification we didn't open.
If you open the second status changed to closed.
Base table Get affected: - wf_item_types
577 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Create a workflow to calculate sum of two numbers.
Open Workflow Builder & Attach the standard workflow (WFSTD) .
Create New Item.
Create New Process
578 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Create Start & End Function
The same way from example one & in the node tab change Start/End option.
Create one more function
Right Click in between start & end function
Select New Function.
Start and end function is pre defined function for this example we are creating our
own function
Enter internal name and display name.
579 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
ADD_FUN funcation is created.
If we need to do some calculation in PL/SQL Block we need some local variables like
in this example also we need some local variable.
Rather than local variables in workflows we need to create Attributes.
Create Attributes.
Right Click on attributes & select Create Attribute.
580 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Types in Attbiutes.
Text : It is used to store characters
Number : It is used to store number
Date : Itis used to store date.
Lookup : It is like fnd lookup
Form : It is used to refer to any form.
URL is used for calling external url in the notification. Like we need to call any website
in the nofitication.
Document The attribute value is an attached document like if we want to store excel
or word document.
Role : It is a database role.
Attribute : It is used to refer from one attribute to another
Event : It is reference to a Business Event
https://docs.oracle.com/cd/B13789_01/workflow.101/b10284/defcom03.htm
Enter the Internal name , Display name & Select type.
581 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
We can enter the value in this form or we can enter the value in the end when we
going to submit the workflow.
Like this create one more attribute (B) & enter the value as 20.
Create one more attribute ( C ) but without any value because C wil store value which
we are getting after adding A & B.
582 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Create Package with procedure to accomplish the specific task.
Package Specification.
All the variables which we are creating in this procedure is a manditory variables in
workflows like we have errorbuf and redcode for concurrent program.
Package Body
583 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
To download above package click on below mention link.
https://drive.google.com/open?id=1yXaGyE-3wGKtBXHwx3kotTAgHZiXk3cf
Link the Package to ADD_FUN function.
Double click on ADD_FUN function.
Enter package name . procedure name
Give Directions.
Right Click on function and drag to another function.
Close process window and minimize every thing.
Right Click on WFSTD and select verify.
584 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Save the workflow in the database.
Login Oracle Apps with user sysadmin/sysadmin
Search your workflow.
585 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Press Run.
We need to enter the item key only.
In the workflow attributes screen in A and B we get the values 10 and 20 by default
because we have given the values while creating attributes.
If we don't give the values at the time of creating attributes we need to give the
values now.
Enter Item Key as 1 & Press Submit.
586 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Press OK.
Go to Status Monitor and Search your workflow
Status is completed
Now Click on status diagram.
Now open SQL Developer.
Check the base table: - wf_item_attribute_values
587 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Example 3: Create a workflow to calculate sum of two numbers & If Sum is greater
than 100 then First Notification to user if less then 100 then second notification to
user.
Open work flow builder and select standard workflow (WFSTD)
Create Item
588 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Create Process
Create Start and End Function
Select Start and end In Node Tab.
Create Three Attribute Like we have create in Example 2
589 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
In these attributes we are not giving default value.
Create Our Function
590 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Create Two Notifications
One is For More than 100 & One is for Less than 100.
ADDNOT1 We have created for more than 100
ADDNOT 2 is for less 100
591 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
After creating a notification we need to create message and need to attach the
message with notification.
Go to Navigator
Right Click on message & Select Create Message.
Go to Body Tab.
Press Apply & Ok.
Create Second Message.
592 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Press Apply
Attach Message with Notification
Double Click on ADDNOT1 & Attach MSG1
Go to Node Tab & Give the User Details to which you want to send the message.
593 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Attach MSG2 with ADDNOT2
Go to Node Tab & Give the User Details to which you want to send the message.
594 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Create Package
In Package Spec we need to create as second example
Package Body
Package body is also same we just need to add this code
For Complete Code open below mention link: -
https://drive.google.com/open?id=19cVY6QhZlGAJgiTksjQ8VxqJmVdnYSA5
Open ADDFUN Function
595 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Give Function Name (Package name . Procedure Name).
Change Result type Yes/No.
IF Total is More than 100 then it take as Yes or If Less than 100 it take as No.
Give Directions
Give Directions from addfun to both the notifications
596 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
System is asking you want addnot1 as yes or no we select as Yes.
Not give direction to addnot2
From both the notifications give directions to end.
597 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Close process window and minimize everything.
Right Click on workflow and verify.
Save the workflow in database.
598 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Login oracle Apps with Sysadmin user.
Find your workflow.
Press Run
You can we are not any values because we didn’t give any values while creating
attributes.
Enter Item Key and values in A & B
599 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Press Submit
Go to Status Monitor
Search Your workflow.
Go to Status Diagram
600 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
You can see System has taken the ADDNOT1 path because our total of values is
greater then 100.
Submit your workflow again with total is less than 100 & then check the status
diagram.
Press Submit
Now check the Status diagram
601 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Our total is less than 100 so system is taking the path from addnot2.
Login Oracle apps with operations user.
Lookup Types
A lookup type has one or more values called lookup code associated with it. Function
or process as its possible result type. A lookup type is a list of values that can be
referenced by a message or by a notification, attribute.
It is collection of lookup codes.
In the above example we have select result type as Yes / No.
In this example we can create our own result type.
Result type is also known as lookup Types.
For this example we are using the above example.
602 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Standard Lookup Types.
We want to create one more notification where can give the message ‘Sum is equal
to 100’.
Create 3rd notification.
603 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Add the user name to which user we want to send the notification.
Create 3rd Message.
Attach message to notification.
604 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Create Lookup Type.
Right Click on Lookup Types.
Press New
605 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Enter Internal name & Display name.
Right Click on Lookup which we have created.
Select New Lookup Code.
We are creating 3 lookup code. We create G lookup code for greater than 100 . E for
equal to 100 & L for Less than 100.
606 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
607 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Now we need to do some changes in our PLSQL Code also.
https://drive.google.com/open?id=18qtuxKb3qLIEvuLJM-uerXvJuWn2YzCw
Change the Result Type from the function.
Select your lookup type & Press OK.
Give directions to all the notifications & attach your result type to respective
notification.
608 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Submit the workflow.
609 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Status Monitor
Workflow Attributes With Approve & Reject Message
We have two types of notification one is for your information purpose & other one is
for your action. Till the time we have learn about FYI types of Notification. In this we
will learn about FYA types of Notification.
In this example we will learn about how to call attibutes with our message & we can
provide the approve & Reject button in the message.
When we run the workflow operations user will get the notification that we need
holiday for next two days. Then if operations user will approve then It will send the
approve message or if press reject button it will send reject message.
610 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
We will create four attributes for manager name , employee name , from date & to
date.
We can create start & end function with the short cut.
Go to File Quick Start wizard Enter Item name & Process name.
Create Item & Process
We will get the start & end function.
Create 3 Notification . Request Notification , approve notification & Reject
Notification.
Request Notification we are creating where we will add approve & reject button.
We will add message also where we will get the manager name , employee name &
dates from which we need leave.
611 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
We are sending this notification to operations users.
Operations will approve or reject.
612 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Create Approve Notification
We will send this notification to our user workflow_notes .
We have created the workflow_notes user with the workflow responsibility.
Create Rejected Notification
613 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
We will create 4 attributes for Request Message.
Till the time we have create attibutes but we didn’t call them in our message. Now in
this example we will learn how to call the attributes in message.
For Manager Name
614 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Mention Length also.
For Employee Name
For From Date
Select Type as Date & Provide the date format as DD-MON-YYYY.
615 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
For To Date
Create message for Request Notification
Attach Attribute in Message by using Ampersand operator ( & ). Just put the
Ampersand operator with attribute name.
616 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Request Message
In Request message go to result tab.
We are attaching the lookup type in the message option because we need approval &
Rejected button in the message option.
Enter display name & Desciption.
We have pre defined lookup approval.
617 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
We are getting it question mark sign in request_msg after attaching result type.
Approval Message.
Reject Message.
618 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Attach Message to Notification.
In notification also we need to add approval result type.
619 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Give directions to all the notifications & attach your result type to respective
notification.
620 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Verify the workflow.
We will get an error.
To over come to this error we need to attach all the attribute with the message. For
this trag the attribute and paste in Request message. Because we have call the
attributes in request message.
Now verify your workflow & save the workflow in your database.
Login the new user workflow_notes & Run the workflow.
621 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Fill the Details.
Press Submit.
Check the Status Monitor
622 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
You can see the path is not completed because we have mention approve & reject
button in the message.
Login with operations users.
Open the notification.
We are able to see the details in the message we have enter the details in the
attributes fields.
Right Side we are able to see the approve & reject button.
Press Approve Button. Login workflow_notes user.
623 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Open the nofitication.
Now check the status montor of your workflow.
Calling workflow using PLSQL Block
Till the time we are calling the workflow using the form . we can call the workflow
using plsql block also.
In this we are using the package wf_engine.
WF_ENGINE is a workflow package which provides APIs that can be called by an
application program or a workflow function in the runtime phase to communicate
with the Workflow Engine and to change the status of workflow process activities.
https://drive.google.com/open?id=1Eb_jDOYl3XY6xmP1ztC7WK0iFsrr3Wg5
624 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Procedures we have in wf_engine.
1) Create process -- Creates a new runtime process for a work item.
2) SetItemAttrText – Used to store text values in attributes
3) SetItemAttrDate -- Used to store date in attributes
4) SetItemAttrNumber -- Used to store number in attributes
5) Start Process -- Begins execution of the specified process.
In aname we need to mention the attribute name.
In avalue we need to mention the name of the manager.
Like this we have given details for other attributes.
625 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Run the PlSQL Block
Check the operations user.
Open the notification.
Action History Automatically Created in
this we will get the information from which
user to which user notification has sent.
We are getting the values which we have mention in the PLSQL Block.
Click on approve.
Now check the workflow_notes user.
626 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Open the notification.
Leave Approval Second Example
Till the time the example we have done we have hardcore the user name in the
notification & notification so to that user.
Create One Table
create table xx_approval_tab ( name varchar2(20),
mgr varchar2(20),
lv_days number,
hr varchar2(20));
insert into xx_approval_tab values ( 'OPERATIONS', 'SYSADMIN', 30,'ACL');
insert into xx_approval_tab values ( 'WORKFLOW_NOTES', 'OPERATIONS', 30,'ACL');
insert into xx_approval_tab values ( 'KRISHNA', 'ANAND', 30,'ACL');
insert into xx_approval_tab values ( 'ANAND', 'OPERATIONS', 30,'ACL');
627 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Operations, workflow_notes, krishan & Anand are FND Users.
For this example I am using the existing workflow.
We are creating 3 new attributes One for Requester, One for Manager & One for
Leave Days.
In the table we have mention 30 days for every employee & it’s manager name also.
628 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
629 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Changes in Notification.
Open Request notification & Go to Node tab ( Where we have hard core the user
name).
Change Type to item attribute
Select Manager into the value.
Like this we need to select requestor for Approve & Reject notification.
630 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Create One Function.
Function will subtract the number of leaves we have used from the table.
We need to create one procedure for the calcualtion & Attach that procedure with
the function.
https://drive.google.com/open?id=1wAxAbIVlO2Lp1JmmAcTti0HUgk-sQ48c
631 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Provide Directions.
632 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Run the workflow using workflow_notes user.
Login Operations users . Because workflw_notes manager is operations.
633 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Click on approve.
Now check the table.
You can see with approve & Reject button we have two more buttons Reassign &
Request Information. These are the pre defined button from oracle.
Request Information
It is used to more information about the notification to the user .
If the Manager want some another information before approval or we have send this
notifcation to our project manager. He want some information from our Team Leader
so he can Request information from other users also.
Click on Request Information.
634 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
We are getting two options . Workflow Particpant & Any user.
In Workflow particpant we will only get the name of the user which we have in the
workflow.
In Any user we can select any user from the oracle apps.
Press Submit.
635 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Login With workflow_notes.
In the below one answer field get created automatically.
636 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Reassign
Click on Reassign
We got two options Delegate your response & Tranfer nofitication ownership.
Delegate your response we will use when we are giving temporary rights to the other
person so can approve on our behalf . In this we will be the owner of the workflow
notification.
Select the Delegate.
Find another user.
637 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Press Submit.
We can run this workflow using plsql block also.
https://drive.google.com/open?id=1JzbtJ_wPiDxZAPUiD8fvdh4kcxUstAHi
638 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
We have given the user name as Operations.
Login with sysadmin user because we have given opertions as employee & sysadmin
is the manager of the operations as per our table we have created.
639 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Click on approve.
User will get the Approve Message.
Now check the table.
640 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Roles
When we want to send the one nofitication to multiple users we need to create roles.
Create Role
We are creating a roles using API ( wf_directory.createadhocrole )
https://drive.google.com/open?id=10steWEiZBlmz-UxQuYBTlu0qeHQkDPiI
Table : wf_roles
Adding User to Role
We are adding a users in our role.
API ( wf_directory.adduserstoadhocrole)
https://drive.google.com/open?id=1S1yid53M4nfSaLfk6nEmRaCi_R87cS0Q
Table : - wf_user_role_assignments
641 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Create Workflow
Create Attribute
Select Type as Role
Create Message
642 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Create Notification
Attach Message to Notification
Add Attrivute to Notification
Give Directions
643 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Run the workflow
https://drive.google.com/open?id=1bM1WMold-60tSsGzluSaCS5ihpeEp9cH
Notification will receive by all the users we have added in our role.
If one user has read the notification then if will automatically read for the other users
also.
644 | P a g e mailankitnarula@gmail.com
www.selfonlinetraining.wordpress.com
Delete the User from Role
We can delete the user also from the role.
https://drive.google.com/open?id=1m0M1Z1AZ4ZsiIfhP4FuFnWYCVzK8fCEf
Now check the users table ( wf_user_role_assignments )
Tables
wf_items
wf_item_types
wf_item_attributes
wf_message_attributes
wf_notifications
wf_activities
WF_MESSAGES
WF_NOTIFICATION_ATTRIBUTES
FND Load Script
Fnd load script is used to transfer the data from Development server to test server or
test server to production server.
https://drive.google.com/open?id=1itq3GHNHJQom6jSfYsfxQsucIRvnixhB
645 | P a g e mailankitnarula@gmail.com
You might also like
- Oracle Data Management With Rubrik Technical White PaperNo ratings yetOracle Data Management With Rubrik Technical White Paper32 pages
- Basic Concepts List of Useful Oracle Apps Articles 2No ratings yetBasic Concepts List of Useful Oracle Apps Articles 24 pages
- Product - Middleware - Oracle SOA Suite 12c - System Architecture and Administration100% (1)Product - Middleware - Oracle SOA Suite 12c - System Architecture and Administration12 pages
- Troubleshooting Oracle Apps Performance IssuesNo ratings yetTroubleshooting Oracle Apps Performance Issues20 pages
- Qdoc - Tips - Oracle Student Guide 113065985 Schedule Dec12 May1No ratings yetQdoc - Tips - Oracle Student Guide 113065985 Schedule Dec12 May18 pages
- Oracle E-business Suite Training MaterialNo ratings yetOracle E-business Suite Training Material229 pages
- Query To Fetch Proxy User Activities in Oracle Apps R12 (JTF - PF - ALL - ACTIVITY - VL) - Oracle Apps Knowledge SharingNo ratings yetQuery To Fetch Proxy User Activities in Oracle Apps R12 (JTF - PF - ALL - ACTIVITY - VL) - Oracle Apps Knowledge Sharing3 pages
- Requirements For Installing Oracle10gR2 On RHEL 5 OL 5 (x86 - 64) (Doc ID 421308.1)No ratings yetRequirements For Installing Oracle10gR2 On RHEL 5 OL 5 (x86 - 64) (Doc ID 421308.1)5 pages
- Project Implementation For COSC 5050 Distributed Database Applications Lab1No ratings yetProject Implementation For COSC 5050 Distributed Database Applications Lab118 pages
- Configure Microsoft Azure in Oracle Identity Cloud ServiceNo ratings yetConfigure Microsoft Azure in Oracle Identity Cloud Service3 pages
- Improve Security in Oracle EBS and Prepare For Fusion Cloud Collaborate 15 WPNo ratings yetImprove Security in Oracle EBS and Prepare For Fusion Cloud Collaborate 15 WP16 pages
- R12 Installation 64 Bit On OEL 5 Update 5No ratings yetR12 Installation 64 Bit On OEL 5 Update 518 pages
- TO Get The Associated With The: Responsbility Particular UserNo ratings yetTO Get The Associated With The: Responsbility Particular User14 pages
- Multi Organization Access Control: January 29 TH 2011No ratings yetMulti Organization Access Control: January 29 TH 201121 pages
- Oracle SQL Loader the definitive guide 1st Edition Jonathan Gennick - The special ebook edition is available for download now100% (1)Oracle SQL Loader the definitive guide 1st Edition Jonathan Gennick - The special ebook edition is available for download now56 pages
- Toad World Blog: Switchover and Switchback Simplied in Oracle 12cNo ratings yetToad World Blog: Switchover and Switchback Simplied in Oracle 12c4 pages
- How To Change The Oracle EBusiness Suite UrlNo ratings yetHow To Change The Oracle EBusiness Suite Url2 pages
- Register Attach Submit and Delete Concurrent Program Using APINo ratings yetRegister Attach Submit and Delete Concurrent Program Using API3 pages
- Zapdoc - Tips Oracle e Business Suiter122 TroubleshootingNo ratings yetZapdoc - Tips Oracle e Business Suiter122 Troubleshooting20 pages
- How To Change Oracle Fusion Application LogoNo ratings yetHow To Change Oracle Fusion Application Logo6 pages
- Tuning All Layers of The E-Business Suite - Part INo ratings yetTuning All Layers of The E-Business Suite - Part I63 pages
- Configure In-App Approval Notifications With BI Publisher: White Paper / October 24, 2019No ratings yetConfigure In-App Approval Notifications With BI Publisher: White Paper / October 24, 201936 pages
- Extending HCM Redwood Applications Using Visual Builder Studio - Technical BriefNo ratings yetExtending HCM Redwood Applications Using Visual Builder Studio - Technical Brief25 pages
- Oracle Financials RXi Reports Administration ToolNo ratings yetOracle Financials RXi Reports Administration Tool52 pages
- A) STEPS To Enable PLSQL Tracing:: (Some Settings May Already Be Configured If Done Before)No ratings yetA) STEPS To Enable PLSQL Tracing:: (Some Settings May Already Be Configured If Done Before)8 pages
- Tutorial On Oracle Forms, Reports and GraphicsNo ratings yetTutorial On Oracle Forms, Reports and Graphics124 pages
- Oracle SQL and PLSQL Interview Questions and AnswersNo ratings yetOracle SQL and PLSQL Interview Questions and Answers21 pages
- Digital Input Module Bmt-Di10: Bacnet MS/TP On Rs-485No ratings yetDigital Input Module Bmt-Di10: Bacnet MS/TP On Rs-4853 pages
- Rootkits and Bootkits Reversing Modern Malware and Next Generation Threats 1st Edition Alex Matrosov - The complete ebook is available for download with one click100% (1)Rootkits and Bootkits Reversing Modern Malware and Next Generation Threats 1st Edition Alex Matrosov - The complete ebook is available for download with one click50 pages
- Object Oriented Programming Lab 1 (A) PointersNo ratings yetObject Oriented Programming Lab 1 (A) Pointers3 pages
- New Form 7 Sky Consent With New PoliciesNo ratings yetNew Form 7 Sky Consent With New Policies4 pages
- INVERSORES - FSCA02DI Solar Inverters Brochure 2015No ratings yetINVERSORES - FSCA02DI Solar Inverters Brochure 2015124 pages
- Limited Access Humanitarian Programming: Operational Guidance For Managing Programme QualityNo ratings yetLimited Access Humanitarian Programming: Operational Guidance For Managing Programme Quality48 pages
- Result: Credit SL - No. Subject Subject CodeNo ratings yetResult: Credit SL - No. Subject Subject Code3 pages
- VTU Exam Question Paper With Solution of 15CS52 Computer Networks Dec-2017-Priyadharshini ANo ratings yetVTU Exam Question Paper With Solution of 15CS52 Computer Networks Dec-2017-Priyadharshini A53 pages
- Scenic Corridors and Open Space - Lexington County SCNo ratings yetScenic Corridors and Open Space - Lexington County SC8 pages
- Army Engineer Cartography I Map Mathematics100% (2)Army Engineer Cartography I Map Mathematics183 pages
- E-Commerce and Digital Marketing: Name: Aditya Chavan ROLL - NO: 10 Class: Tybms (Marketing)No ratings yetE-Commerce and Digital Marketing: Name: Aditya Chavan ROLL - NO: 10 Class: Tybms (Marketing)14 pages
- 100 K8 Errors Solution by DevOps Shack 1712799024No ratings yet100 K8 Errors Solution by DevOps Shack 171279902425 pages