Brief about Oracle apps for Beginners
1
ERP - ORACLE APPLICATIONS
Pre-requisites to learn Oracle Applications
Oracle 9i
SQL - Reference books
• DDL Commands
• DML Commands
• TCL Commands
• Quarries, Sub Quarries
• Operators
PL/SQL - Reference books – by author Scott Urman
• Cursors
• Trigger
• Packages, Procedure, Functions
Forms 6i - Reference books – by Evan Baris
• Canvases – types – functionality
• Property Classes
• Visual attributes
• Programe Units
• Record Group
• Triggers – Sequences
• Form functions – like call form
Reports 6i
• Variables – like Bind, Lexical
• Columns - like Summery, Placeholder, Formula
• Report Triggers – 6 types and sequence
• Format Triggers and Validate triggers
• Layout objects
o Frame
o Repeat frame
o Anchor
o Boilerplate
2
Oracle Applications 11i - Introduction
ERPs available in market
• SAP
• Oracle Applications
o People soft
o Siebel
o JD Edwards
Oracle Applications are used to capture Business functionality information of the
organization.
• Instances
o Development - Development
�� Development stage 1
�� Development stage 2
o Testing - Testing
o Production – Client testing
Involved personalities of Oracle Applications and their roles and responsibilities
in brief
1) DBA – Database Administrator
a. Installation of Oracle Applications
b. Applying patches
c. Maintaining Multiple Instances
2) Functional Consultant
a. Interaction with the client
b. Gathering the requirements for development / customization
c. About oracle applications what available and what wasn’t available.
d. Example invoice form – fields - Or develop a invoice form from scratch
e. Preparation of FDD (Functional Design Document)
3) Technical Consultant – Input FDD
a. Go through the FDD
b. Prepare TDD (Technical Design Document) – Logic, Tables, Procedures,
Forms, Menus, Packages – approval
c. Development of Component
d. Test the component
e. Deliver to the client
All these sessions will go trough the below mentioned components
R I C E - Components for development 70%
�� R - Reports - Reports 6i
�� I - Interfaces - Programs, SQL, PL/SQL
�� C - Conversion - Programs, SQL, PL/SQL
�� E - Extensions - Forms 6i
Functions Knowledge 30%
• Purchase Order Module
• Accounts Payable Module
• Order Management Module
• Inventory Module
3
I) Types of Projects
a. Implementation Project: Client will be using another software to
maintain the business solutions. Now the client wants to have Oracle
Applications package to capture the data. That time we will start setups,
data conversions, gap analysis, functions document preparation from the
scratch. - first time
b. Customization Project: Client was already using Oracle Applications,
now the client would like to have few more modules to cover the
business, for that we will customize new modules and integrate with
existing modules.
c. Migration or Up-gradation Project: Client was already using Oracle
Applications older version now client would like to move new version of
Oracle Applications that time we will migrate older version objects in to
new version.
Version Database GUI
10.7 Oracle 7 Forms 4.5, Reports 2.5
11.0.3 Oracle 8 Forms 6i, Reports 6i
11.5.3 Oracle 9i Forms 6i, Reports 6i
11.5.4 Oracle 9i Forms 6i, Reports 6i
11.5.5 Oracle 9i Forms 6i, Reports 6i
11.5.6 Oracle 9i Forms 6i, Reports 6i
11.5.7 Oracle 9i Forms 6i, Reports 6i
11.5.8 Oracle 9i Forms 6i, Reports 6i
11.5.9 Oracle 9i Forms 6i, Reports 6i
11.5.10 Oracle 10g Forms 6i, Reports 6i, Java, JDeveloper
d. Support or Maintenance: Once the implementation, customization and
migration are over, then supporting project will be started where we will
give supporting for the objects which were already developed, customized
and migrated.
Existing
Ora Apps
Version
10.7
New Version
11.5.9 or 11.5.10
Existing
Ora Apps
Few modules
Financial Modules
Migration
Manufacturing Modules
(Customization and
Integration)
Old System
Cobal
Fox Pro
New System
Oracle Apps
4
II) Types of Modules
Module: Module is nothing but one application it contains forms, reports and
programs which are related for specific business functionality.
Example: PO – Module – This will be used to capture purchasing information like
suppliers, their contact details, supplying material, unit price and discounts and so
on.
ERP
Financial Manufacturing HRMS CRM
AP – Accounts
Payable
PO – Purchase
Order
People Customer
Relationship and
Management
AR – Accounts
Receivables
Inv – Invoicing Payroll Tele Services
FA – Fixed Assets WIP – Work in
Process
Benefits Service Contract
CM – Cash
Management
BOM – Bills of
Materials
OTL – Oracle Time
& Labour
Mobile Process
GL – General
Ledger
OM – Order
Management
PA – Project
Accounting
Production
MRP Schedules
Enquiring
III) Modules Integration :
PO
Supplier Inventory
WIP
Production
BOM
MRP Scheduling
QA
Testing
OM
Sales, Shipping
GL AR
CM
AP
HRMS
FA
5
4) Types of Documents: The methodology of behind is
AIM – Application Implementation Methodology developed by Oracle Corporation
R I C E Number Name Prepared by
MDO 50 Functional Design Document Functional Consultant
MDO 70 Technical Design Document Technical Consultant
Module
Design
Forms &
Reports
MDO120 Migration Document Technical Consultant
CVO40 Conversion functional Design
Document
Conversions Functional Consultant
Interface
CVO 60 Conversion Technical Design
Document
Technical Consultant
Support CR Document Change Request Technical Consultant
NOTE:
Migration Document is two types
1) Installation Process: Installation Stages or three types’ development, testing,
production. If any of the form or report is developed, when it is moving from one
instance to another instance, to be moved all the components of that. Example
functions, procedure, packages etc. This process will be done by DBA.
2) Execution Process: In this the execution of the form or report with a screen
shoot to be specified along with the functionality
CR Document:
The document tell about the request made by the client, it could be very small change
like single filed to be added or deleted to the form or procedure to be deleted.
5) Oracle Applications File Architecture
Linux (Server)
Windows (Client) Windows (Client)
FTP
FTP
6
Custom_ TOP:
Custom top will be created by customer (client) for the custom development and
customizations. At least one custom top is required for every client. We can
have multiple custom tops also as per the requirements
NOTE:
1) We are not suppose to develop are customize the product tops like AP, AR, GL, PO
2) If oracle applies the patches (scripts) against the application the script will over right
both developments and customization whatever we have done. That is the reason
we will us custom top. Oracle will not touch the custom top and it will not provide any
type of support.
US Folder:
This folder is language specific by default American English language will be
implemented. If we want to have multiple languages we can implement in Oracle
Applications by specifying different folders. This folder is only applicable for
Forms and Reports because both are GUI objects.
Select * from FND_LANGUAGE where installed_flag in (‘B’, ‘I’)
Flag B - Base language, Flag I - Installed language,
Flag D - Disable language
11.5.0 Folder:
This oracle application release name based on the release it will be specified
10.7 10.7
11.0.3 11.0.3
11.5.3
11.5.4
11.5.5
11.5.6
11.5.7
11.5.8
11.5.9
11.5.0
11.5.10 11.5.10
APPL_TOP
CUS_TOP PO_TOP AP_TOP GL_TOP INV_TOP AR_TOP
11.5.0 11.5.0 11.5.0 11.5.0 11.5.0 11.5.0
Forms Reports SQL PL/SQL BIN Mesg., Out Admin
US US
..fmx ..rdf
.SQL .proc
.func
.pkg
.pkgsc
.pks
C Prog
.java
.ctl
.shell
script
.msg .outfile Admin
script
7
Oracle Applications Database Design
Predefined database Oracle 9i
Schema Diagram Item Details Report
Item Supper Cheque Buyer
Monitor IBM 1745/- Stockpet
Invoice
Schema
PO
Schema
AP
Schema
HRMS
Schema
• User connect only one schema at time
• Total approximately 4000 schemas available
• APPS Schema contains only synonyms
• Per ever database object we will find synonym in APPS Schema
• Table name and synonym name should be same
• No module to cover the Share Market Business
• New Module – New Schema – New Top
APPS Schema:
This one of the Schema in Oracle Applications database it contains only
synonyms. It has got access other schema database objects
PO Schema:
It contains only PO Objects like PO Tables Indexes, Sequences, and views and
materialized views and so on. It will connect to PO Schema we can access only
PO objects. We can’t access other schema database objects.
NOTE
• We are not suppose to create tables in apps schema, we will create the tables in
custom schema then we will provide grants to apps schema
• While development of forms, reports, programs we will always connect to apps
schema in oracle applications.
AOL – Application Object Library
After o development / customization
n the objects to be registered with this. This has got the two responsibilities.
1) System Administrator: Which has got the functions like 1) users, 2)
Responsibilities 3) Reports development / customization 4) Procedure 5) Printer
Installation 6) Admin activities - Server Monitoring, Request submission
2) Application Developer: Which has got the functions like 1) Forms 2) Menus 3)
Messages 4) Flexi fields 5) Profiles
PO
AR
HRMS
AP
INV
GL
APPS
8
After installation of Oracle Applications the default User details as follows
User Name : OPERATIONS
Password : WELCOME
And connect and select System Administrator responsibility
To create New User
Security – User – Define – Enter
To move in the form some shortcut keys
1) If you want to query all the record – Ctrl + F11
2) If you want to query few records for form
a. Open the form
b. Press F11 – Query mode
c. Search criteria by using %
d. Press Ctrl + F11
3) To close the form F4
To Connect to database or Bank End
User ID : APPS
Password : APPS
Host string: PRODUCTIONS
Creation of New User – Front End
1) Connect to the application
2) Enter application User Name : OPERATION and Password: WELCOME
3) Select the Responsibility called “System Administrator
4) Open the user Form - Security – User- Define and enter
5) Enter Username and password and attach Responsibility
6) Save the Transaction.
7) Once user is created we can’t delete
WE can find all the user details in FND_USER table
Select * from FND_USER where user_name = “20user”
Note: We can find the table names from front end by using help menu
Help – Record History option
If open a form we can three types of fields
9
Yellow Color – Mandatory Fields, White Color – Optional Fields, Gray Color – Read Only
Effective Dates : From – To
Some of the records we can’t delete in Oracle applications instead of the we will
effective dates From and To to enable or disable the record.
Who columns in Oracle Applications
Ever Database table contains following Colums.
CRETED_BY User Id
CREATION_DATE System date When we create
LAST_UPDATED_BY User ID
LAST_UPDATED_DATE System Date When we Updated
LAST_LOGIN_DATE System date Login system date
When we go to Record History option we can find all the who columns
Help – Record History
Executables
.rdf Reports
.sh Unix Shell Script
.sql SQL
.proc, .fun, .pck PL/SQL
.C C Programe
.proC ProC
.ctl Sql * loader
.prl Perl
10
Concurrent Programe
• Is nothing but instances of execution file along with parameter and incompatible
programs
• We can register 11 types of executable as Concurrent Programe
• Advantages
o We can execute multi language programmes as Concurrent Programe
o When we run Concurrent Programe we can utilize 100% local machine
hardware capacity
o We can change the Concurrent Programe output based on employee
profile
o We can submit only Request
o We can schedule the Concurrent Programe as per client requirement.
Report Registration
1) Develop the report (.rdf) as per client requirement by using Reports 6i Builder
2) Move the report from the local machine in to the server
a. CUS_TOP\11.5.0\reports\US .rdf (or)
b. PO_TOP\11.5.0\reports\US .rdf
3) Select System Administrator
a. Create Executable
b. Executable name
c. Application Name
d. Execution Method
e. Report (.rdf) file name
4) Create Concurrent Programe and attach
a. Executable
b. Parameters
c. Incompatibilities
5) Create Request group and attach Concurrent Programe
6) Create Responsibility
a. Request Group – Concurrent Programe
b. Data Group – Collection of User IDs
c. Menu – Collection of forms
7) Create user attach Responsibility to the user
8) User will select the responsibility and go to SRS (Standard Request Submission)
window submit the request
Executable => Concurrent Programme => Request Group => User=> SRS
Note: For Single Executable we can have different concurrent programs with different
parameters
To create simple report with below SQL statement
Select User_ID, User_name, Trunc(creation_date) from fnd _user
11
Executable:
After moving .rdf in to the server, we will crate executable by specifying execution
name execution method and application name.
Concurrent Programe:
After creation of executable we will create concurrent Programe by attaching
executable, if parameters are there we will attach parameter if incompatibility
programs are there we will add those programs , for single executable we create
multiple concurrent programs with different parameters.
Request Group:
After creation of concurrent program we must add the programme to the request
group. Request Group is nothing but collection of Concurrent Programs and
Reports.
Responsibility:
It is level of authority where we will combine Data Group, Request Group and
Menu. Request Group is optional menu and data group is mandatory.
Data Group:
Data Group is nothing but collection of applications names and Oracle User
Names based on this Username data will be retrieved from database.
Menu:
Menu is nothing but collection of functions (forms) and submenus.
Create a simple report using below query
Select * from PO_VENDORS
Vendor_ID, Vendor_Name, Creation_Date
Navigation
Executable Window - Concurrent=>Programme=>Executable
Concurrent Programem Window - Concurrent=>Programme=>Define
Request Group – Security=>Responsibility=>Request
Responsibility – Security=>Responsibility=>Define
User – Security=>User=>Define
SRS Window – View Menu => Requests
To find out output file path and log file path we will write the below select
statement
Select logfile_name, outfile_name from fnd_concurrent_requests
Where Request_ID = ‘ ‘;
12
Reports with Parameters
Data Model
Select * from FND_USER
USER_ID, USER_NAME, CRATION_DATE
Parameters
From USER_ID, To USER_ID
Layout Model
Designing of layout
User Parameters
P_From
P_To
P_Title
Data Model – Change the SQL Query
Select * from FND_USER WHERE user_id between :P_From :P_To
Compile and save
To register the parameter with applications
Concurrent Programe window to be opened.
Note: We can change the sequence based on the requirement.
13
• If report is having the parameters then we have to register those parameters at
the time of creation Concurrent Programe.
• Value Set: values set is nothing but list values. It will be used to validate values
while entering the parameters.
• Token: Token is one the filed wile be used to map concurrent Programe
parameters with report builder find variable. We will enter the find variable name
in the toke field so that parameters will be passed to the find variable.
• Required check box: By using this check box we can’t make the parameters
mandatory or optional
• Enabled Check Box: By using this we can enable or disable the parameter.
• Display Check Box: By using this we can hide or display the parameters in SRS
window.
• Range Option: While defining the from and to parameters if we wanted to accept
values in accession order menus from values is low and to value is high we will
select the options called low and high.
• Default Types: If we are hiding the parameters user can’t enter the values that
time we can pass default values by using default type and default value filed.
• SRS Window – Copy Button: This will be used to find out recent Concurrent
Programe list with parameters in SRS Window.
Default Types
14
When we are hiding the parameter in SRS windows user can’t enter the values that time
we can pass values internally by using defaults types.
1) Constant: If we want to pass constant values as default then we will select
default type constant and we will specify the values in default value field.
2) Current Date: System Date
3) Current Time: System Time
4) Profile: By using the profile option we can pass user profile values as default
5) SQL Statement: When we want to pass select statement to rest as default
values that time we will select default types as SQL statement and write the
select statement in the default values filed. Select statement should not return
more then one value.
6) Segment: When we wanted to pass previous parameter values as default to the
next parameter then we will use segment, select default type as segment give
the parameter name in the default values field.
Report Builders
Data Model
Select User_ID, User_name, Trunc(creation_date) from fnd_user;
Layout Model
Object Navigator
User Parameters
1) P_From_Date - Date - Input Mask
2) P_To_Date – Date –Input Mask
3) P_Title – Character – 100
Data Model
Select User_ID, User_name, Trunc(creation_date) from fnd_user
Where trunc (creation_date) between: P_From_Date and: P_To_Date
Create a Summery Column
Count of UserIDs
Oracle Applications
To register the parameter
P_From_Date – Values Set – FND_DATE
Default Values.
P_From_Date – Current Date
P_To_Date – Current Date
15
P_Title – Constant
To hide parameter – To Uncheck the Display Check box in bottom of the form
To write SQL Statement Min and Max creation Date
Select MIN(TRUNC(CREATION_DATE)) FROM FND_USER
Select MAX(TRUNC(CREATION_DATE)) FROM FND_USER
Default values type – SQL Statement option
Default Value field – SQL Statement
Segment
Default Type as – Segment Option
Default Values – Specify the previous parameter
VLUES SETS - Application – Validation – Set
Value set is nothing but list of values with validation. It will restrict the user to enter valid
values. These are 8 types.
1) NONE : When we write maintain some format conditioning NO – LOV
Emp NO: => Only nos. 0 – 9
=> 35 to 7856
=> 45 to 0045
2) INDEPENDENT: Yes – LOV – user must select values from the list
3) DEPENDENT: Yes – LOV – The value which will depend upon the previous
parameter
Country Code: City Code:
i.e. based on the selection of country code lov city code lov will change
16
4) TABLE: Combination functionality of Independent and Dependent to provide
database table name and column name.
5) TRANSLATED: Independent – Multi language values
6) TRANSLATED DEPENDENT: Dependent – Multi language values
7) Special : To Display Flexi field data
8) Pair: To Display Flexi field data.
1) NONE -
a. When we want to restrict the user to enter the values based on some
conditions then we will use NONE type. Here no list of values user
manually will enter the value based on the conditions values will be
accepted.
b. Enter the values set name select format type enter the minimum size
select validation type as NONE.
c. Once the value set is created we can use it for any concurrent program
either single time or multiple times.
d. Once the value set is created we can’t delete if value if value set is being
used by concurrent program if we want to delete release from the
concurrent program then we can delete by using delete option.
2) INDEPENDENT –
a. Provide the values to the user either number or character creates the
independent value set to enter the values for the value set Application-
Validation-Values
b. If we want to provide list values to the user we will go for selecting
independent values set type user must select the values from the list.
c. Open the value set for an enter value set name select format type and
select validation type as Independent.
17
d. Copy the value set name go to values screen enter the values set name
and click Find enter the values in the values field save the transactions
attach value set to concurrent program.
e. Once the values are inserted in to the list we can’t delete instead of
deletion we can disable by using enable check box or effective date from
and to.
3) DEPENDENT
a. Dependent value set is nothing but another LOV but values will be
changing based on the previous Independent value set.
b. When we are creating dependent we must have 2 parameters
1)Independent 2) Dependent
c. Open the value set form create Independent value set go to values from
enter the values.
d. Open the values set form create dependent value set by selecting
validation type as dependent.
e. Select edit information button attach Independent value set what ever we
have created.
f. Copy the value set name go to values screen click on find button enter
the values based on the Independent value.
Hyderabad
Mumbai
Chennai
India
Bangalore
Newyork
Newjersy
US
California
UK London
Paris
4) TABLE –
Select user_name, user_id, creation_date
From FND-USER
Where row num < 20
Order by User_name desc
a. If values are available in database table then we will go for using table
value set, we will give the table name and column name system will
retrieve the values from the database table.
b. Open the value set form and give the value set name select validation
type is table click edit information button.
c. Enter the table name, column name in the value field.
d. Enter the where clause order by clause in the text item called where order
by.
e. Use the additional column field to display extra columns data in LOV, we
have to use alias name while specifying additional columns.
f. Table Application : It is a optional field based on the table name we can
find out table application name.
g. Go to Application Developer Responsibility –
Application=>Database=>Table query the records based on table name.
18
h. Select User_ID, USER_name from FND_USER – From front end user
name should be available and internally user_id to be passed.
i. Id Column in Value set form – If we want to display one column to the
user in the front end and pass another column value internally we will use
ID column.
j. Meaning Column – This will be used to display the extra columns like
additional columns it will work like a additional columns
k. Display the values from multiple tables –
i. At the time of giving the table name enter table names with alias
name by specifying. ( , )
ii. AT the time of selecting give the column name by giving the alias
name, column name.
iii. Join condition is mandatory in where / orderby clause
iv. PO_VENDORS, PO_VENDORS_SITE_ALL.
Select pv.vendor_name, pvs.vendor_site_code
From po_vendors pv, po_vendors_sites_all pvs
Where pv.vendor_id = pvs.vendor_id.
5) TRANSLATED INDEPENDENT & TRANSLATED DEPENDENT
a. These two value set will work like independent and dependent value set
but these two value sets will be used to display translation values (other
thane English language values) if application is installed for mlti language
there we will create these two value sets.
Select * from FND_LANGUAGES
Installed_Flag – I - Installed languages
B - Base language
D - Disabled language
6) SPECIAL & PAIR –
a. These two value sets will be used to display flexi filed data.
Develop the below report with parameters - CPLISTREP.RDF
From Date : P_From_Date
To Date : P_To_Date
Title : P_Title
CP Name CDate Application Name Executable Executable Method
• When the width of the report is more we have chose the style of the output in
Concurrent program window BACS instead of A4.
Query
Select fcp.user_concurrent_program_name,
Trunc(fcp.creation_date) CDate,
fav.application_name,
fev.executable_name,
fl.meaning
19
from fnd_concurrent_programs_vl fcp,
fnd_application_vl fav,
fnd_executables_form_v fev,
fnd_lookups fl
where fcp.application_id = fav.application_id
and fcp.EXECUTABLE_ID = fev.EXECUTABLE_ID
and fl.lookup_type ='CP_EXECUTION_METHOD_CODE'
and fcp.execution_method_code = fl.lookup_code&p_lexical
Parameters:
• Lexical parameter to change the query dynamically.
• Bind parameter to be register before Oracle applications.
If title parameter is null i.e. if user doesn’t pass the value to the title parameter
Format trigger.
Begin
If :P_Title is NULL then
Return (True)
Else
Return (False)
End if
End
If parameters were not passed to the :P_From_Date and :P_To_Date report should
display all the records.
After Parameter Form Trigger
Begin
If :P_From_Date is NULL and :P_To_Date is NULL then
:P_lexical:= ‘ ‘
Else
:P_lexical:= ‘and Trunc(fcp.creation_date)
between :P_From_Date and :P_To_Date
End if
End.
================
Exercise
From User ID, To User ID, From Date, To Date
Title
User_ID User_Name CDate Responsibility Application
Name
Security
Group
From
Date
To
Date
Query prepared by SIR in class
Master Level
20
SELECT USER_ID,
USER_NAME,
TRUNC(CREATION_DATE) CDATE
FROM FND_USER
WHERE USER_ID BETWEEN :P_FROM_ID AND :P_TO_DATE
Detail Level
SELECT FU.USER_ID,
FRV.RESPONSIBILITY_NAME,
FAV.APPLICATION_NAME,
FSG.SECURITY_GROUP_KEY,
TRUNC(FRG.START_DATE) FROMDATE,
TRUNC(FRG.END_DATE) TODATE
FROM FND_USER FU,
FND_USER_RESP_GROUPS FRG,
FND_RESPONSIBILITY_VL FRV,
FND_APPLICATION_VL FAV,
FND_SECURITY_GROUPS_VL FSG
WHERE FU.USER_ID = FRG.USER_ID
AND FRG.RESPONSIBILITY_ID = FRV.RESPONSIBILITY_ID
AND FRV.APPLICATION_ID = FAV.APPLICATION_ID
AND FRG.SECURITY_GROUP_ID = FSG.SECURITY_GROUP_ID
Steps to develop a report
• Data Model – Data link to be given between both the quarries.
• Adjust the margin and comeback to main page
• Report width to be increased - 13 – 135
• Select the main from and other frames – expand – variable
• Place all the text fields.
• Take the repeating frame and source to master query
o Put all the fields which are related to master query and what ever are
needed.
• Take another repeating frame and place that on the first repeating frame and
source to detail query.
o Put all the fields which are related to details query and what ever are
needed.
• Select all the fields and no fill and no line
• Define the User parameters P_From_ID and P_To_ID
• Applications
o Create the table value set for select the parameters
21
Standards to develop a report in Oracle Applications – 3 STANDARD STEPS.
1) Define the mandatory parameter called P_CONC_REQUEST_ID – This is one of
the bind variable when ever we submit a request in SRS window that request ID
will be passed to this bind variable, based on this request id we can execute
concurrent request process. Without this find variable we can’t use userexits.
2) Call the userexit in Before Report Trigger
• SRW.USEREXIT(FND SRWINIT)
3) Call the userexit in after report trigger
• SRW.USEREXIT(FND SRWEXIT)
Report Triggers
1) Before Parameter Form Trigger
2) After Parameter Form Trigger
3) Before Report Trigger – Before retrieving the data from database
4) Between Pages Trigger - when ever courser goes between pages at first time.
5) After Report Trigger – After out is reached the destination i.e. printer, file, email.
USER EXIT
It is one of the predefined program in Reports 6i will be used for stop the report
execution process for sometime and transfer the control to the 3rd Generation Language
get the data and completes the remain execution process.
Start End
Parameter
form
Query Layout
3GL
User Exit
Start End
Parameter
form
Query Layout
22
We have 5 types of User Exits available in Oracle Applications.
1) FND SRWINIT: We will use this user exit in the before report trigger. It will
initialize user profile values according to that profile values data will be retrieved
from database.
• SRW.USEREXIT(FND SRWINIT)
2) FND SRWEXIT: We will call this user exit from ater report rigger to freeze the
memory which is occupied by user profile values.
• SRW.USEREXIT (FND SRWEXIT)
3) FND FLEX SQL
4) FND FLEX IDVAL
5) FND FORMATCURRENCY
Purchase Order Application: Group of Forms, Reports and Programs
• Requisition
o Internal
o Purchase
• RFQ
o Standard
o Bid
o Catalog
• Quotation
o Standard
o Bid and Catalog
• Purchase Order
o Standard
o Planned
o Contract
o Blanket
• Receipts
o Direct Delivery
o Standard Receipt
o Inspection Required.
Requisition
Request for Quotation (RFQ)
Quotation
Purchase Order
Receipts
AP Interface INV Interface
Purchase
Documents
1
• Before going to start PO application we have to define following things
1. Items Creation
2. Suppliers creation
3. Buyer creation
1) Item Creation
a. Open the user from in System Administrator Responsibility attach the
responsibility called Inventory, Vision Operations(USA)
b. Open the Items from - Items – Master Item – enter, system will show the
organization list, select the organization vision operations, enter the item name
and description.
c. Go to Inventory tab check the check box called Inventory Item.
d. Go to purchasing tab check the check box called purchased and save the
transactions.
e. We can assign the item to the multiple organization by selecting tool menu –
Organization assignment option check the check box called assigned.
Select * from ORG_ORGANIZATION_ DEFINETIONS
- Organizatio_ID is primary key column
Select * from MTL_SYSTEM_ITEM_S
Where sgment1=’Keyboad’
- Inventory_item_ID -
- Organizatin_ID - Composite Primary Key
Select MSI.SEGMENT ITEM
OOD.ORGANIZATION_NAME
From MTL_SYSTEM_ITEM_B MSI
ORG_ORGANIZATION_DEFINETIONS OOD
Where MSI.ORGANIZATION_ID = OOD.ORGANIZATION_ID
And MSI.SEGMENT1 = ‘KEYBOARD’
2) Supplier Creation
a. Select the responsibility called Purchasing, Vision Operations(USA)
b. Open the suppliers form - Supply Base – Suppliers press enter
c. Enter the suppliers name and save the transaction and we will get supplier
number select status button enter site address select contact tab – enter contact
details and save
Select * from PO_VENDORS where SEGMENT1=’5080’
- VENDOR_ID – Primary Key
Select * from PO_VENDOR_SITESM_ALL where VENDOR_ID=’1010’
- VENDOR_SITE_ID – Primary Key
Select * from PO_VENDOR_CONTACTS
where VENDOR_SITE_ID= ‘2215’
- VENDOR_CONTACT_ID – Primary Key
2
3) Buyer Creation:
a. Attach the Responsibility called HRMS Management; Open the Employee form
(N) HRMS Manager – People – Enter and Maintain.
b. Select new button enter employee name and Date of Birth, select action as
creation employment chose the option called buyer and save the transaction.
Select * from PER_ALL_PEOPLE_F
Where EMPLOYEE_NUMBER = ‘893’
PERSON_ID is Primary Key column
c. Go to system administrator create user name attach employee name in the filed
called Person and save the transaction.
Select FU. USER_NAME,
PPF.FULL_NAME
From FND_USER FU,
PER_ALL_PEOPLE_F PPF
Where FU.USER_NAME = ‘20USER’
And FU.EMPLOYEE_ID = PPF.PERSON_ID
d. Copy the employee name go to purchasing application open the Buyers form
attach to the Buyers list - Setup – Personnel – Buyers and enter.
Select * from PO_AGENTS
This contains only Buyer list and primary key is AGEN_ID
PO PROCESS
1) Requisition: It is one of the purchasing document will be crated by employers when
ever they required goods or services or training, we will find 2 types of requisitions.
a. Internal Requisition: This will be created, when we receiving the materials from
other organizations(Branches)
b. Purchase Requisition: This will be created, when we are receiving the materials
from outside means suppliers.
c. We will enter the requisition information at 3 levels i.e. Headers, Lines,
Distributions.
d. One header will be there at least one line multiple line we create for every line at
least one distribution or multiple distributions will be there.
e. Open the requisitions form – Requisitions – Requisitions and enter.
f. Select the requisition type at header level enter item details at line level (item
Name, quantity, price and need by date) select bistributions buttion enter
distribution details like quantity and account details and save the transactions.
g. Select approve button press OK system will send the document for approval.
h. Go to requisition number select find button, it will show requisition approved
status.
Select * from PO_REQUISITON_HEADERS_ALL
3
WHERE SEGMENT1 = ‘1656’
Primary key - REQUISITION_HEAD_ID
Select * from PO_REQUISTION_LINES_ALL
WHERE REQUISITION_HEADER_ID = ‘11458’
Primary Key - REQUISITION_LINE_ID
Select * from PO_REG_DISTRIBUTIONS_ALL
WHERE REQUISITION_LINE_ID= 9922
Primary Key - DISTRIBUTION_ID
i. Cancel the Requisition: Open the requisition summery form enter requisition
number select find button go to tools menu select option called ‘Control’ and
chose the option as cancel requisition.
Select AUTHORIZATION_STATUS from PO_REQUISITION_HEADER_ALL
j. We can find out requisition history by using tools menu option called view action
history.
Select * from PO-ACTION_HISTORY
Where OBJECT_ID = REQUISITION_HEADER_ID
Select PAH.ACTION_CODE
PDF.FULL_NAME
From PO_ACTION_HISTORY PAH
PER_ALL_PEOPLE_F PPF
Where OBJECT_ID = 11459
And PAH.EMPLOYEE_ID = PPF.PERSON_ID
k. Enter manual requisition Number: Setup – Organization – Purchasing Options
and enter - go to numbering tab select requisition number entry as manual type.
2) RFQ – REQUEST FOR QUOTATION: It is one of the purchasing document after
requisition is approved. We will go for creation of RFQ we have 3 types of RFQs.
a. Bid : If company is going to purchase large number of items which are
expensive. We will create bid RFQ where we will specify Headers, lines and
shipments, where we are not specify any price breaks in Bid RFQ.
b. Catalog : If company is purchasing materials regularly fixed quantity location and
date, then we will select regularly we can include price breaks at different levels.
c. Standard : This will be created for items we need only once or not regularly, we
can include price breaks at different quality levels.
3) QUOTATIONS : After creation of RFQ document we will send this document to the
different suppliers who are going to supply the materials, next we will receive quotations
from the suppliers either by email or fax or by phone,
a. Again quotation are 3 types i.e. Bid, Catalog and Standard
b. What ever the quotation we are received from the suppliers we will enter those
quotation details in the system for future purpose.
c. If we are sending bid RFQ to the suppliers we will receive the bid quotation for
catalog RFQ we will receive catalog quotation for standard RFQ we will receive
standard quotation.
4
d. RFQ – Terms and conditions.
i. Payment Terms – At the time of creating RFQ document we will
specifying the payment terms like due date, interest rates and so on.
ii. Fright Terms: It is nothing by at transportation charges whether supplier
will bear it or buyer.
iii. FOB(Foot on Board) : It is responsibility of material damage or material
missing during the transportation.
iv. Carrier : We will give the transportation company name so that supplier
will supply the materials by this transportation company.
e. Navigation – Open the RFQ form – RFQs and Quotation – RFQs and enter -
select RFQ type from header level enter ship to location, Bill to location details –
select line level enter item details select price breaks button enter price break
details select terms button enter terms and conditions details, select suppliers
button and enter supplier name who are going to receive RFQ document
Select * from PO_HEADERS_ALL Where segment1=’306’
Primary Key- PO_HEADER_ID
Select * from PO_LINES_ALL Where PO_HEADERS_ID = ‘11845’
Primary Key – PO_LINE_ID
Select * from PO_LINE_LOCATIONS_ALL where PO_LINE_ID 12233
Primary Key –LINE_LOCATION_ID
Select * from FND_CURRIENCIES
Primary Key –CURRENCY_CODE
Select * from AP_TERMS
Primary Key – TERM_ID
Select * from AP_TERMS_LINES
Select * from HR_LOCATIONS
Primary Key – LOCATION_ID
f. AUTO CRATE OPTION : This is one of the feature application to create RFQ or
PO documents automatically based on the approved requisition document.
i. Create requisition and approve it
ii. Open the auto create form select clear button enter requisition number
select find button which will display requisition then details
iii. Select the line by checking the checkbox select action as create,
document types as RFQ
iv. Select automatic button click the create button whichwill create RFQ
document will selected requisition lines and displays RFQ No.
g. QUOTATION : - RFQs and Quotation – Quotation and enter
i. We will receive the quotations from suppliers against the RFQ either by
fax or email we will enter those quotation details manually in the system
to make the quote analysis and future purpose.
ii. Quotation tables: One we create the quotation from front end data will be
stored in the RFQs tables only by column type_lookup_code=’quotation’.
5
Select * from PO_HEADERS_ ALL
WHERE SEGMENT1 = ’500’
AND TYPE_LOOKUP_CODE =’QUOTATION’
4) Purchase Orders : It is one of the purchasing document at the time of purchasing from
the supplier we will create this document by specifying terms and conditions and
shipping details distribution details and so on. We have 4 types of Purchase Orders
a. Standard
b. Planned
c. Blanket
d. Contract
Purchase Order Types Summery
Standard Purchase Order
When we require the materials from suppliers we will cerate standard PO by specifying terms
and conditions price, quantity and so on.
Select * from PO_HEADERS_ALL
where segment1=’3445’ and type_lookup_id = ‘STANDARD’
Select * from Po_lines_all where PO_Header_id = 11858
Select * from PO_Line_locations_all where Po_line_id=12216
Primary Key – LINE_LOCATION_ID
Select * from po_distributions_all where line_location_id
Primary Key – PO_DISTRIBUTION_ID
• (N) – Open the purchase order form – Purchase Orders-Purchase Orders and enter
select PO type and suppliers information enter the line level details like Items quantity
price and so on.
• Select shipments button enter shipping location details quantity promice date, need by
date and so on select distribution button enter distribution details and save the
transactions.
6
• Select approve button system will submit the document for approvals.
• Copy the PO number and go to purchase or summary form enter PO number select find
button.
• To cancel the purchase order go the tools menu – control option.
• Tools menu copy document to create the same document.
• Auto Create
o By using Auto Create option we can create purchase orders automatically from
approved requisition by selecting document type as Purchase order.
SELECT PHA.SEGMENT1 PONUM,
PHA.TYPE_LOOKUP_CODE POTYPE,
TRUNC(PHA.CREATION_DATE) CDATE,
PV.VENDOR_NAME SUPPLIER,
PVS.VENDOR_SITE_CODE SUPPLIERSITE,
(PVC.FIRST_NAME
','
pvc.LAST_NAME) Contact,
HL1.LOCATION_CODE ShipTO,
HL2.LOCATION_CODE BillTo,
PHA.CURRENCY_CODE Currency,
PPF.FULL_NAME Buyer,
PHA.AUTHORIZATION_STATUS PoStatus,
SUM((PLA.QUANTITY*PLA.UNIT_PRICE)) LineLevelPrice,
PHA.COMMENTS PODesc
FROM PO_HEADERS_ALL PHA ,
PO_VENDORS PV ,
PO_VENDOR_SITES_ALL PVS,
PO_VENDOR_CONTACTS PVC,
HR_LOCATIONS HL1,
HR_LOCATIONS HL2,
PER_ALL_PEOPLE_F PPF,
PO_LINES_ALL PLA
WHERE PHA.SEGMENT1 = '3449'
AND PHA.VENDOR_ID = PV.VENDOR_ID
AND PHA.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID
AND PHA.VENDOR_CONTACT_ID = PVC.VENDOR_CONTACT_ID
AND PHA.SHIP_TO_LOCATION_ID = HL1.LOCATION_ID
AND PHA.BILL_TO_LOCATION_ID = HL2.LOCATION_ID
AND PHA.AGENT_ID = PPF.PERSON_ID
AND PHA.PO_HEADER_ID = PLA.PO_HEADER_ID
GROUP BY
PHA.SEGMENT1 ,
PHA.TYPE_LOOKUP_CODE ,
TRUNC(PHA.CREATION_DATE),
PV.VENDOR_NAME ,
PVS.VENDOR_SITE_CODE ,
(PVC.FIRST_NAME
','
PVC.LAST_NAME) ,
HL1.LOCATION_CODE ,
HL2.LOCATION_CODE ,
PHA.CURRENCY_CODE ,
PPF.FULL_NAME ,
PHA.AUTHORIZATION_STATUS ,
7
PHA.COMMENTS
Blanket Purchase Order
• When ever company would like to have the agreement with supplier that time first we will
create Blanket Agreement, when ever we require materials we will go for releasing the
purchase orders.
• Open the purchase order form select Blanket Purchase agreement and enter the details
and approve the purchase order.
• Select releases form either blanket PO Number and Item quantity details select
distributions button enter distribution details, select approve button for approvals.
Match Approval
2 way PO Qty – 100 Invoice Qty 80
3 way PO Qty – 100 Receipt Qty 80 Invoice Qty 80
4 way PO Qty – 100 Receipt Qty 80 Invoice Qty 50 Invoice Qty 50
• At the time of creation Purchase Order in the shipments, release we will specify the
Match approval option.
• Two way matching is nothing but company purchase order quantity, price with Invoice
price.
• Three way matching is nothing but comparing 3 documents Purchase Order, Receipt
and Invoice.
• Four ways Matching is nothing but company PO receipt inspection and Invoice
documents.
Receipt Documents
1. Standard - 3 Way
2. Direct Delivery - 2 way
3. Inspection Required - 4 way
• It is one of the purchasing document will be created while receiving the materials form
the suppliers we have 3 types of receipt.
• Navigation – Receiving – Receipts and enter.
• Create the purchase order and approve it open the receipts from enter PO Number,
select find button, system will show the PO Line details, check the check box save to
transactions, system write automatically generate Receipt number
Select * from RCV_SHIPMENT_HEADERS
SELECT * FROM RCV_SHIPPMENT_LINES
8
SELECT * FRO RCV_TRANSACTIONS
SELECT * FORM ORG_ORGANIZATIONS_DEFINITIONS.
• Create PO write 3 lines
o Go to receipt – generate the receipt for only one Item and either the PO Number
again – create another receipt until all the Items over.
Select Receipt_Num
From RCV_shippment_headers
Where shipment_header_id IN (Select shipment_header_id
From rcv_shpment_lines
Where PO_header_id IN( Select Po_Header_ID
From PO_heaer_all
Where segment1=’3452’))
----------------------------------------------------------
MD050 – Functional Design Document
Based on above develop Quotation Report
Query
MASTER QUERY
SELECT PHA.PO_HEADER_ID,
PHA.SEGMENT1 QUOTENO,
PHA.QUOTE_TYPE_LOOKUP_CODE,
TRUNC(PHA.CREATION_DATE) CDATE,
PPF.FULL_NAME BUYER,
H1.LOCATION_CODE SHIPTO,
PHA.QUOTE_VENDOR_QUOTE_NUMBER QUOTEAMT ,
H2.LOCATION_CODE BILLTO ,
PHA.CURRENCY_CODE CURR,
PV.VENDOR_NAME,
PVS.ADDRESS_LINE1,
PVS.CITY,
PVS.ZIP
FROM PO_HEADERS_ALL PHA ,
PER_ALL_PEOPLE_F PPF ,
HR_LOCATIONS H1 ,
HR_LOCATIONS H2,
PO_VENDOR_SITES_ALL PVS,
PO_VENDORS PV
WHERE TYPE_LOOKUP_CODE = 'QUOTATION'
AND PHA.VENDOR_ID = :P_VENDOR_ID
AND PHA.AGENT_ID = PPF.PERSON_ID
AND PHA.SHIP_TO_LOCATION_ID = H1.LOCATION_ID
9
AND PHA.BILL_TO_LOCATION_ID = H2.LOCATION_ID
AND PHA.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID
AND PHA.VENDOR_ID = PV.VENDOR_ID
LINE – QUERY
SELECT PLA.PO_HEADER_ID,
MSI.SEGMENT1 ITEM,
MSI.DESCRIPTION ITEMDESC,
MC.SEGMENT1
','
MC.SEGMENT2 CATEGORY,
PLA.UNIT_PRICE PRICE
FROM PO_LINES_ALL PLA,
MTL_SYSTEM_ITEMS_B MSI,
MTL_CATEGORIES MC
WHERE PLA.ITEM_ID = MSI.INVENTORY_ITEM_ID
AND PLA.ORG_ID = MSI.ORGANIZATION_ID
AND PLA.CATEGORY_ID = MC.CATEGORY_ID
Purchase Order Interview Questions.
1) What is the Flow of Purchasing Module
2) Where the types of Requisition and table
3) Types of Purchase orders and tables
4) What is the different between standard PO and Blanket PO
5) We have created a Item but which is not available in the pO what will be the problem
6) What are the types of Match approvals
7) What are the Receipt types
8) When we enter a Blanket Release data where it will be stored
9) If give the requisition no how to find out corresponding PO Numbers
10) If give the PO Number how to find out corresponding Receipt Numbers
11) What is Auto Create and Advantage
12) In which table we can find out
i) Shipped Quantity
ii) Received Quantity
iii) Canceled Quantity
13) Can we create Purchase order directly without
i) Requisition,
ii) RFQ
iii) Quotation
14) Can we have the Receipt without Purchase order
15) What are the tables for RFQ, Quotation, Purchase Order
PL / SQL Procedure registration with Oracle APPS.
Create or Replace( Errorbuff OUT Varchar2(10),
Retcode OUT Varchar2(20) , , , , )
Local Variable declaration
Cursor Declaration
Collection Declaration
10
Begin
Space for Control Statements like IF
LOOP s
Procedure, Function and Package Calling
DBMS_OUTPTU.PUT_LINE - -----�� we will not use this function.
FND_FILE.PUT_LINE(FND_FILE.LOG, ‘Write message’
Variable name)
FND_FILE.PUT_LINE(FND_FILE.OUT, ‘Write message’
Variable name)
Exception
When Others then
FND_FILE.PUT_LINE(FND_FILE.LOG, ‘Write message’
Variable name)
End
ErrorBuf: While registering the procedure in concurrent Programe we are suppose define
errorbuf out parameter, which will give the error messages in the log file if any occurs in the
procedure.
Retcode: It returns the status of concurrent program either 0-Normal, 1-Warnining, 2-Error
API – Application Programe Interface – FND_FILE: API is nothing but Application
Programming Interface, instead of DBMS_OUTPUT package we will use FND_FILE.LOG to
write in the log file, FND_FILE.OUTPUT to write in the output file.
NOTE: We can register procedure or package procedure as concurrent Programe to do some
DML validation and some other programming logic, we can’t register function as concurrent
Programe.
11
Registration Steps
• Develop the procedure and compile, specification of the procedure will be stored in the
Database.
• Select System Administrator Responsibility - Crate executable with execution method
as ‘PL/SQL Stored Procedure”
• Create concurrent Programe and attach – Executable, Parameters, and
Incompatibilities.
• Create the Request Group and the Concurrent Programe
• Attach the Request Group to the Responsibility
• Attach Responsibility to the User
• User submit Request from SRS window
Simple Example
Create or Replace Procedure EX20( Errorbuf out Varchar2,
Retcode out Varchar2) as
L_no number(8) default 1000,
L_Name Varchar2(100) Default ‘Mr. Rajan Bhatnagar,
Begin
FND_FILE.PUT_LINE(FND_FILE.LOG, ‘Write message’
L_no)
FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ‘Write message’
L-Name)
Exception
When Other then
FND_FILE.PUT_LINE(FND_FILE.LOG, ‘Error has occurred during the execution’)
End EX20
Procedure with parameters
Develop a Procedure which will take two parameters
1. Vendor ID
2. New Vendor Name
System should find weather vendor ID is exist or not if it exists it should update the Vendor
Name.
Create or Replace Procedure VEN_UPDATE20( Errorbuf OUT Varchar2,
Retcode OUT Varchar2,
V_ID IN Number,
V_Name IN Varchar2) as
L_Flag Varchar2(10) Default ’Y’;
L_Name PO_VENDORS.VENDOR_NAME%TYPE;
Begin
Select Vendor_name INTO L_name from PO_vendors where vendor_ID=V_ID;
Exception
When NO_DATA_FOUND then
FND_FILE.PUT_LINE(FND_FILE.LOG, ‘There no records in the Table’)
12
L_Flag:=’E’;
When TOO_MENY_ROWS then
FND_FILE.PUT_LINE(FND_FILE.LOG, ‘There multiple records in the table’)
L_Flag:=’E’;
End;
If L_flag:= ‘Y’ then
Update PO_VENDORS SET VENDOR_NAME=V_NAME
Where Vendor_id=V_ID;
Commit
FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ‘vendor name changed ‘)
Endif;
Exception
WHEN OTHERS then
FND_FILE.PUT_LINE(FND_FILE.LOG, ‘Error occurred while updating’)
End VEN_UPDATE20;
• While registering the procedure, if we have user defined parameter we are suppose to
define those parameter at the time of registering concurrent program, select parameter
button enter sequence number parameter name attach value sets if required attach
default types.
• Token filed will be disabled for procedures, it is enabled for only reports because report
builder find variable may or may not be in the sequence order but procedures variable or
permits will be always in the same sequence. First parameter values will be passed to
the first variable second parameter values will be second variable and so on
Exercise
Develop a procedure to transfer the following details in to the Temporary Table.
Create Table Quotation_temp( Total Quotations Number(8),
Total amount Number(8));
Create Table Quotation_Details ( Qno Number(8),
Qtype Varchar2(10),
Cdate Date
Buyer Varchar2(100),
BillTo Varchar2(100),
ShippTo Varchar2(100),
Currency Number);
• Program will accept one parameter supplier name LOV Vendor Name Internally
Vendor_ID should pass
• Based on the Vendor_id find the Quotation Details and load in the temp tables;
NOTE:
13
1. Crate Two table
2. Define the cursor based on Vendor_ID
3. Transfer the data in to quotation temp and quotation details
4. Define local variable to count to calculation and transfer to quotation_temp
SQL * LOADER - Oracle Tool
It is one of the Oracles tool will be used to upload the data from flat files in to oracle database
tables.
1. Flat file or Data file: It contains the data in specific format it will be either text file (.txt)
or excel sheet. (.txt, .dat, .csv – Comma Separated Value)
2. Control File: It contains SQL * Loader program contains data file path, database table
name and column mapping and soon, once we develop the control file we will execute
this, that time data will be transfer from file to Oracle Database Table, during this data
transfer, the following files will be created. The extension of the control files is .ctl.
3. Badfile: It contains the rejected records which are rejected by SQL*Loader, because of
bad format or data type mismatch and so on. The extension of the bad file .bad.
4. Discard file: It contains the rejected records which are rejected by control file if we have
specified any condition in the control file if record is not satisfying the condition the
complete record will be inserted in the discard file. The extension of file is .dis.
5. Log file: It contains information about control file execution like, execution start time
and end time, successful records count, bad records count, if there are any errors in the
control file, those error messages will be available in segments, the extension of file .log.
The Syntax of the control file is as follows:
Load Data
Infile ‘Data file Path’
Insert into table
Fields terminated by ‘,’ or ’$’
( Column1,
Column2,
Column3, , , , , ,, );
And save as .ctl file
For execution Command for .ctl file.
SQLLDER username/password@hoststring
Control =
Class Exercise
• Connect to Scott Schema and create temporary table
14
Create table emp_details ( empno Number(8),
Ename Varchar2(100),
JDate Date,
Deptno Number(8),
Sale Number(8),
Comm Number(8),
Tax Number(8) );
Select * from emp_details
• Prepare data file and save in the local machine.
• Develop the control file like as follows and save it with extension .ctl
Load Data
Infile ‘C:\20\load\emp.txt’
Insert into table emp_details
Fields terminated by ‘,’
( Empno,
Ename,
JDate,
Deptno,
Sale,
Comm,
Tax );
• Go to Command Prompt select following path
o D:\Oracle\proddb\8.1.7\bin>
• Execute the control file with SQLLDR Command
SQLLDER SCOTT/TIGER@PROD
Control = C:\20\LOAD\EMP.CTL
• To find path to execute the SQLLDR command is HOST Command from SQL prompt
which will shows the path where we can execute control file
o Select * from v$parameter.
Control File
INSERT - Table should be empty – New records will be inserted.
APPEND – New records will be add for existing records
TRUNCATE – It will delete existing records and inserts new records from the file.
.csv file (Comma Separated Valu)
If data in excel sheet we can save that file as .csv file.
If any string contains commas we will enclosed this will in double quotation in the control file we
will specify the following statement syntax
15
Fields Terminated by ‘ , ‘ optionally enclosed by ‘ ” ’
If one of the field is missing for the database column we can generate sequences and also we
can use the some functions like to_char, NVL, Decode, Instr, Rtrim, Trim and so on
Training Nullcols – Inserts NULL columns if no data is there, we can not use any DDL, DML or
TCL command in control file.
LOAD DATA
Infile ‘C:\20\load\emp.txt.’
Truncate into Table emp_details
Fields Terminated by ‘ , ‘ optionally enclosed ‘ “ ‘
Trailing Nullcols
( Empno ‘emp_seq.nextval’,
Ename ‘upper(:ename)’,
Jdate Sysdate,
Deptno,
Sal,
Commn,
Tax )
Fixed format control file exampled
Sometimes data may found in fixed format - for example
Empno - 5, Ename – 10, Jdate – 11, Deptno – 2, Sal – 5, Comm – 3, Tax -3
If data is in the fixed format the control file will be developed in different format
LOAD DATA
Infile ‘C:\20\LOAD\FIXED.TXT
Truncate Into table emp_details
( Empno position(1:5),
Ename position(6:15),
Jdate position(16:26),
Deptno position(27:28),
Sal position(29:33),
Commn position(34:36),
Tax position(37:39))
In control file itself we can specify the data and at the time of insertion we specify the specify the
condition and we can also specify the discard file pat in control file it self - for example
LOAD DATA
Infile *
Discardfile ‘C:\emp_rejected.dis’
Truncate Into table emp_details
When deptno=’20’
( Empno position(1:5),
Ename position(6:15),
16
Jdate position(16:26),
Deptno position(27:28),
Sal position(29:33),
Commn position(34:36),
Tax position(37:39))
Begin Data
Fsfsd fgdfdsf
Fsfsfsf fdfdsfsd gsfsdfg gdgd
Along with the SQLLDR command we can also use the few commands like as bellow
LOAD – Will be used to load the first ‘N’ records
SQLLDER username/password@Hoststring Load=10
SKIP – TO skip the first ‘n’ records
SQLLDER username/password@Hoststring skip=10
ROWS – It will be used to issue the commit after insert ’N’records for every 10 records internally
auto commit will happen
SQLLDER username/password@Hoststring rows=10
INIT.ORA file contains the default values for the commit point
Data Uplaoding in to two tables
Create Table emp1 ( Empno Number(5),
Ename Varchar2(30),
Job Varchar2(30));
Create Table dept1( Deptno Number(2),
Dname Varchar2(30));
Positions of the data in datafile
Empno 5, Ename 10, Job1, Deptno2, dname 7
DECODE Statement to be used to Code the ‘A” for Analyst ‘M’ for Manager and Multiple data
files can also be called in single control file
Example control file
Load data
Infile ‘C:\20load\multiple.txt
Truncate Into table emp1
(Empno Position(1:5),
Ename Position(6:15),
Job Position(16:16) “Decode (:Job ,‘A’, ‘Analyst’,’M’,”Manager’)”)
Insert Into table dept1
17
When deptno:=’ ‘
( Deptno Position(17:18),
Dname Position(19:25))
Register Control file as Concurrent Program
1. Develop the control file and move the both data file and control file in the server
2. CUS_TOP\11.5.0\BIN\ .CTL
3. Create executable by seleting the execution method as SQL*Loader
4. create concurrent Program attach executable, Parameters
5. Attach the concurrent program to the Request Group
6. Attach the Request group to the Responsibility
7. Attach Responsibility to the user will submit the request form SRS window.
Example
Create the table in APPS Schema
Create table MTL_ITEMS( Item Varchar2(10),
ItemDesc Varchar2(100),
ItemCost Number(8),
ItemTax Number(8),
BasicCost Number(8))
Basic Cost to the calculated field in the Control file i.e. ItemCost – ItemTax
Development of Control file
Load Data
Infile *
Insert into Table MTL_ITEMS
Fields Terminated by ‘,’ Optionally Enclosed by ‘ “ ‘
Trailing NULLcols
( Item,
Item Desc,
ItemCost,
ItemTax,
BasicCost “(:Itemcost - :Itemtax)”)
Begin Data
Fdfdsf sgdfg fgdgdgds dgdgdgd gfgdgd hgdghdg
Fdfdsf sgdfg fgdgdgds dgdgdgd gfgdgd hgdghdg
• Move the .ctl file in the Inventory top i.e. BIN Directory
• And register the control file with applications.
• Go to System Administrator and register with the applications and concurrent program
and method should be SQL * Loader
• Data file pat can changed dynamically
o Infile ‘&1’
o We muse use the ‘1` degit
18
o We are allowed to submit only one parameter that should be data file path
SQL * Loader with Parameters
At the time of defining concurrent programe select parameter butiton define the parameter
specify the ‘&1’ in the control file so that what ever the values we have passed that will be
replaced in the control file.
Interview Questions
1. How to issue the commit in the control file
a. By suing ROWS Command
2. What are the types loading we have
a. Direct Method Load
b. Conventional method load (Default)
3. How to upload the data in to multiple table at with time
a. By using WHEN command
4. Can we have the data in Fixed format and how to upload
a. Yes, we can have fixed format that time we will use POSITION keyword
5. What is the difference between Bad file and Discard file
a. Both file contains rejected records – Bad file contains records which are rejected by
SQL*Loader and Discard file contains which are rejected by Control file.
6. What are the SQL functions we can use in the control file
a. TO_CAHR, TO_DATE, INSTER, SUBSTER, RTRIM, LTRIM, DECODE, NVL and so
on.
7. can we use user defined functions
a. NO We can’t user defined functions
8. If control file rejected more than 10 records error the I would like to stop the process how to
do it
a. We have a file called INIT.ORA file where we will set parent called maximum errors
allowed and max auto commit other parameter also.
9. How to skip specific field data
a. By using FILLER command – In this position column will not accept the data and it
inserts the NULL values.
For example
( Empno,
Ename ,
JDate FILLER,
Deptno);
SQL * Plus
19
1. Develop the SQL Program (.sql)
2. Move the file form local machine to server in to respective path
a. CUST_TOP\11.5.0\SQL\ .sql
3. Create executable execution method as “SQL*PLUS”
4. Crate concurrent program and attach executable and parameters
5. Attach concurrent program to the request group
6. Attach request group to the responsibility
7. Attach responsibility to the user.
8. Submit request from SRS Window
SQL * Plus Program
Column User_id format 9999999999
Column User_name Format A25
Column Cration_Date Format A11
Column &3 Format A50
Prompt SQL Report with User Details
Select User_ID, User_Name, Creation_Date, &3 from fnd_user
Where User_id between &1 and &2
Save as the file .sql and create the parameters in Oracle apps systems administrator and
column can be added dynamically
NOTE
• We can define Maximum 100 parameter
• We are suppose to use &1, &2, &3 ……………
• We are not suppose to skip the sequence
• WE can use same parameter values in multiple places in the script
• SQL * PLUS concurrent program to execute DDL and Connect to remote database and
executes the scripts and so on.
Submission of Concurrent Program from Back End because, sometimes we don’t have
the access to the front end.
Executable
Concurrent Program
Request Group
SRS
We need to use the Application Programe Interface details in 115devg.pdf 21 chapter
FND_PROGRAM.EXECUTABLE - Executable
FND_PROGRAM.REQUEST - Concurrent Program
FND_PROGRAM.PARAMETER - Attach Parameter
FND_PROGRAM.REQUEST_GROUP - Create Request Group
FND_PROGRAM.ADD_TO_GROUP - Add Concurrent Program R.G
Submission of CP from Back End
20
SQL
PL/SQL
Report – Report Triggers
Forms – Form Trigger
FND_REQUEST.SUBMIT_REQUEST( ) - It will be used to submit the Request from Back end.
Sample Program
Declare
l_request_id number(10);
Begin
Fnd_global.apps_initialize ( user_id,
Resp_id
Resp_appl_id);
l_request_id:= Fnd_request.submit_request ( ‘PO’,
‘Users Information Script’,
‘User Desc’,
‘ ‘,
‘FALSE’,
1000,
1200,
‘Email_address’);
Commit;
If l_request_id is != 0
Fnd_file.Put_line (Fnd_file.log ‘Program successfully submitted Request ID is’
l_request_id);
Else
Fnd_file.Put_line (Fnd_file.log ‘Program not successfully submitted’);
Endif;
Exception
When other then
Fnd_file.Put_line (Fnd_file.log ‘Error occurred during the program submission’);
End;
NOTE: If we are submitting concurrent program from the Form Trigger we are suppose to pass
the 100 parameter. If parameter are not defined, still we are suppose to pass NUL values
Fnd_user
Fnd_responsibility_vl - responsibility_key
Fnd_application_vl - application_name
Scheduling the Concurrent program
We can submit the Concurrent program future date or date by using the schedule button in SRS
window
21
1. As soon as possible: This is default option whenever we submit the request it will
submit the as soon as possible
2. Once: It will submit the rest only once for future date.
3. Periodically: WE can specify the from_date and to_date to submit program periodically
no of. Days months, hours, minutes and so on.
4. Specific Days: If we want submit concurrent program in the specific days we write
select this option
5. Save this Schedule: This check box will be used to save the schedule and apply same
schedule to other concurrent programs by selecting the button called ‘Apply save
schedule’
NOTE: After schedule the Concurrent program we can also cancel by selecting the cancel
button.
PROFILES
Profile is one the changeable option it will change the way of application execution, when we
crate user in oracle applications we will design the profile values for ever user by selecting any
of this level.
Profile levels
User �� Responsibility �� Application �� Site
1. Site Level: Site is the lowest profile level when we install Oracle Applications by default
values will be assigned at this level, site level values will be applicable for all the users.
2. Application Level: If we want to assign profile value to the specific application users
then we will select this level specify the application name and assign the value,
application level profile value will over write site level values.
3. Responsibility Level: We will select the responsibility name assign the profile value,
this is applicable only for the users, who are having access to the specified
responsibility. Responsibility will overwrite the both application and site level values.
Most of the profile will setup at responsibility level because it will be creating
responsibility for ever position in the organization we will also assign the profile values
based on position.
4. User Level: We will select the user name and assign the profit value this is only for
specific user which we have specified at will over write responsibility, application and site
level values.
Definition
Application: It is collection of forms, reports and programs which are related for specific
business functionality
22
Responsibility: It is also collection of forms, reports and programs which are related for specific
position in the organization.
Navigation
Application Developer – Profile - press enter
• We will find profile name and validation access levels, weather we can visible the values
or we can update the values.
• Based on the access level we will go to system administrator open the profile form
assign the profile values.
Navigation
System Administrator – Profile – System – Press Enter
• Select profile name and profile level by default site select find button, it will shows profile
values at 4 levels
Important Profiles:
1. GL Set of Books Name: It is one of the GL application profile, set of books is nothing
but collection of currency, calendar, and chart of accounts. We will assign this profile
values at responsibility level based on this currency and calendar and accounts will get
change.
2. MO: Operating Unit: This will be used to setup user operating unit name which
operating unit user is working, when ever user login to the system by using this profile.
System will understand the user operating unit (Branch Name)
3. HR: Business Group: This profile will give the user business group which group user is
working.
4. HR: User_Type: This profile will give the user employment type which user is
Permanent, Contract or consultant will be identified.
USER_ID
USER_NAME
RESP_NAME
RESP_ID
RESP_APPL_ID
All these are user personal profile values for user will be there whenever we create it contains
the values. We cannot change the values.
GL: St of Books
Currency
23
Calendar
Chart of Account
Select Currency_code Currency
Period_set_name Calendar
Chatof_account_id Accounts
Name SOBName
From gl_set_of_books
18user GL:Set of Books Name Vision Operations(USA) USD
19User GL:Set of Books Name Vision Swedan SEK
20User GL:Set of Books Name Vison Itely ITL
Go to Systems Administrator and open the profile form and select the profile name and assign
the values.
• When we assign set of books to the user based on the that set of book name currency,
calendar and accounts structure will be changed
• Create there different users go to systems administrator assign the profile values, select
user level enter user name and profile name select find futon and assign the user level
values.
• Login with the user name select GL responsibility open the Journals form, Journals –
Entry and press enter we will find the different currencies in currucy filed
Personal Profile – To find
(N) Help – Diagnosis – Examine and press enter
Select block as profile we will find profile names in the filed Profile values in the values field
Technical Consultant Job
Get - Procedure
Value – Function
2 APIs to be used
FND_PROFILE.GET(Profilename, Local Variable);
Local Variable:= FND_PROFILE.VALUE(PROFILE Name);
APIs will be used to retrieve the profile values form Backend like SQL, PL/SQL, Forms, and
Reports.
NOTE: Here get() is a procedure and Values() is a function both will be used to get a profile
value from the backend
Example: In Select clause we can’t use procedure that time we can use function.
Simple PO Report
24
User Name :
Responsibility Name :
Set Of Books Name :
Supplier Name
Supplier ID
PO Type Date Currency
Query
Select PHA.SEGMENT1 PONUMBER,
PHA.TYPE_LOOKUP_CODE TYPE,
TRUNC(PHA.CREATION_DATE) CDATE,
PHA.CURRENCY_CODE CURRENCY
From PO_HEADERS_ALL PHA
Where VUNDER_ID=&PO_VENDOR_ID
Report Development Process
Lexical Parameters
P_CONC_REQUEST_ID
P_USER_ID
P_RESP_ID
P_SOB_NAME
SRW.USEREXIT(SRWINIT) - Before Report Trigger
SRW.USEREXIT(SRWEXIT) - After Report Trigger
And adjust the margin and design the layout
Before Report Trigger
:P_RESP_ID := FND_PROFILE.Value(‘Resp_name’)
User Name – Username
Resp_name – Responsibility name i.e. personnel profiles
GL_SET_BOOKS_ID
MO: Operating Unit – ORG_ID
Access the Profile values from the reports
Define find variable to store the profile values, go to before report trigger call the following APIs,
then use the find variable either in the layout or in the report builder
FND_PROFILE.GET(‘USER_NAME’, :P_USER_ID)
FND_PROFILE.GET(‘RESP_NAME’, :P_RESP_ID)
:P_SOBNAME := FND_PROFILE.VALUE(‘GL:SET_OF_BOOKS_NAME’)
PL/SQL – Procedure as Concurrent Program
Temp Table – Perfomr_taart
User_name Varchar2(50)
Resp_name Varchar2(50)
SOBName Varchar2(50)
ORGName Varchar2(50)
25
Develop the procedure and register as Concurrent program and user submit the request form
SRW window system should capture the dynamically and inserts in to the temp table
:X:= FND_PROFILE.VALUE(‘ORG_ID’)
Select name from HR_OPERATING_UNITS Where organization_id=:x
Chapter 15, 115devg.pdf
FAQs
1. What is profile
2. What are the profiles you have come across in your experience
3. Profile levels in sequential order
4. How to get profile value from Backend
5. How to pass profile values as default in parameter
Default Types
Constant
Segment
SQL
Profile
- Concurrent Programe window Parameter
• At the time of defining parameter select default type as profile and give the profile
name in the default values field.
26
Multi Org
Multi Org is nothing but implementing multiple organization under single oracle APPS
implementation. We will define the multi org levels like follows:
Business Group - Top level org where employee will be define
Set of Books - Currency, Calendar, Chart of accounts – Financial
Entities
Legal Entity - Where company is going to create Income Tax
Documents to Government
Operating Unit - Where Company will procure, sell, pay, receive, stock
Inventory Organization - Where company will store the product
Sub inventories
Stock Locations
Items
- Under the Inventory it will come
Business Group: It represents the highest level in the organization structure, it can be a major
division or operating company or consolidated enterprises, Business Group secure human
recourses information. Example: When we request list of employees assigned to the business
group where organization is a part
Set Of Books: It is a financial reporting entity it uses particular functional currency, chart of
accounts and accounting calendar, Oracle General Ledger application secures this transaction
information like, journal entries, balances, we will use the Set of Books to capture these financial
information.
Legal Entity: it is a legal company for which we prepare physical or tax reports and we will
submit company flanking sheet to Government.
Operating Unit: It is an organization where company is going to do the main business
operations like sales, purchasing payments, receiving and son, it may be a sales office or
27
division or department, operating unit information will be secured by following applications like
PO, AP, AR, OM, CM
Inventory Organization: It is an organization for which we will track Inventory transactions and
balances, Inventory Organization means warehouse, distribution center, manufacturing plants
and sales offices, the following application secures information by Inventory organization
- Oracle Inventory
- Bills of Material
- Work in process
- Enginering
- Capacity
- MRP and so on
Again we will define Inventory Organization as 2 levels
1) sub Inventory
2) Stock location – inside of this location we will keep the items.
Advantages or Major functions
• Data Secure: We can assign users to the particular organization when ever user
logon to the application then we can get the data access, which is related to their
organization
• Multiple Organizations Reporting: We can setup Oracle applications
implementation to allow the reporting across operating units we can allow the
reports either at SOB level or legal entities level or operating unit level
• We can sell the products from one legal entity and ship the products from
another level entity – that time we can easily create financial transactions.
• We can receive the good from the suppliers in any Inventory organization.
Example: A branch employee will create the PO then we can rece4ive the
material in B branch Inventory organization but these two organization should be
defined under same set of books.
• When employee creates internal requisitions in one organization thesewe can
ship the materials from another organization with corrent inter company
inventory.
115multiorg.pdf - Implementing Multiple Organizations Chapter1 and 4 - multiorg.xls.
28
Multi Org Tables Column Text
PO_HEADER_ALL ORG_ID Select * from po_header_all
where org_id=client_info
PO_LINES_ALL ORG_ID Select * from po_header_all
where org_id=client_info
AP_INVOICES_ALL ORG_ID Select * from ap_invoices_all
where org_id=client_info
AP_CHECKS_ALL ORG_ID Select * from ap_checks_all
where org_id=client_info
OE_ORDER_HEADERS_ALL ORG_ID Select * from oe_order_header_all
where org_id=client_info
• Multi Org table contains multiples organization data. This table names are goint to end
with _all and this table contains common column called org_id
• Whenever user login to the systems by using MO:OPERATING UNIT profile, systems
will understand user operating unit code (ORG_ID)
• Multi Org views – These are created based on, multi org table having the where clause
like “where org_id=client_info”. If we are using multi org views in the RICE components,
we will get the data from the database which is related for the user organization. Ex:
PO_HEADERS, PO_LINES, AP_INVOICES, AP_CHECKS, OE_ORDER_HEADERS
• CLIENT_INFO: It is one of the RDBMS variable, it contains user operating unit code
(org_id) whenever user login to the application
o Select * from hr_operating_units (you can get all the branch codes)
• To create Operating units navigation
o HRMS Responsibility – Work Structures - Organization – Description
• In before report trigger we will write SRW.USER_EXIT(‘FND SRWINIT’) – it will initialize
the user profile values (MO:OPERATING UNIT)
Create 3 users, assign to them different branches by using MO:Operating Unit profile and
develop the report, use the multi org tables and register it as concurrent program and submit
from SRS window form 3 users we will get all the operating units data in the output instead of
multi table, use multi org view. Submit request from SRS. It will display the output which is
related for user organization. If we are not using the user exit (FND SRWINIT) system will not
initialize user profile values including MO operating Unit profile that time, client_info variable
values is null.
In SQL & PL/SQL – We need to use API
SQL> select count(*) from PO_HEADERS_ALL
2777 POs are created form multiple organizations
29
SQL> select count(*) from PO_HEADERS – output ‘0’ becoz system doesnot know the branch
working for which branch
SQL> Begin
FND_CLIENT_INFO.SET_ORG_CONTEXT(204)
END;
SQL> select count(*) from PO_HEADERS - Now it is showing the 1645 Pos fro branch 204
Begin
FND_CLIENT_INFO.SET_ORG_CONTEXT(FND_PROFILE.VALUE(‘ORG_ID’)
END
It will retrieve User ORG_ID value
Table shows details about the Multi Org structure table
Organization
Name Table Profile Column
Business Group HRFV_BUSINESS_GROUPS HR:Business Group ID BUSINESS_GROUP_ID
Set Of Books GL_SET_OF_BOOKS GL:Set of Books Name
Legal Entity HR_LEGAL_ENTITIES
Operating Unit HR_OPERATING_UNITS MO:Operating Unit ORG_ID
Inventory
Organization
ORG_ORGANIZATION_
DEFINITIONS MFG_ORGANIZATION_ID ORGANIZATION_ID
SubInventory
MTL_SECONDARY_
INVENTORIES
Stock Locations MTL_ITEM_LOCATIONSQ
Items MTL_SYSTEM_ITEMS
Requstions
RFQ
Quotations
PO
PO Receipts
• While developing or customizing the RICE component we will be using Multiorg views.
We are not supposed to use the Multi org tables.
• By suing the following select statement we can find whether multi org is implemented
successfully or not
o Select multi_org_flag, release name from fnd_product_groups
Multi_org_flag Releasename
Y 11.5.7
• Y – Implemeted successfully (Business Group, I.E., Set of Books, operating Unit,
Inventory organziaiton)
30
• BUSINESS_GROUP_ID: This at Business group level we can find them common
column in all the table, which are in HRMS tables like
o PER_ALL_PEOPLE_F
o PER_JOBS
o PER_GRADES
o PER_POSITIONS
• ORG_ID: It is at operating unit level it we will find this common column in all the table at
operating unit level like
o PO_REQUESITION_HEADERS_ALL
o PO_HEADERS_ALL
o PO_DISTRIBUTION_ALL
• NOTE: But PO receipts functionality will come at Inventory organization level. Because
we are crating receipt means we are receiving the goods from supplier. We will be
receiving the material at Inventories (stories, gowdans ) only. Example:
RCV_SHIPMENT_HEADERS table is not having the _ALL it is not having ORG_ID
column
• ORGANIZATION_ID: It is at Inventory Organization level we will find all the
manufacturing tables column this ORGANIZATION_ID column including PO Receipts
Tables
o MTL_SYSTEM_ITESM_B
o WIP_ENTITITES
o BOM_RESOURCES
o BOM_DEPARTMENT
PO_VENDORS is not having _ALL
PO_VENDORS_SITES_ALL is having _ALL
Supplier is global we can access from any organization
Supplier site is organization specific and we create site based on the user ORG_ID sites will
retrieved.
FAQs
1. What is application hierarchy or Multi org flow?
2. What is Business Group?
3. What is legal entity and operating unit
4. What is meant by multi org table
5. What are module will come at operating unit level
6. Will PO Receipt will come tat Operating Unit level or Inventory Organization level?
7. What is difference between OR_ID and ORGANIZATION_ID?
8. Why there is no _ALL for PO_VENDORS table?
9. How to implement multi org in reports?
10. How to populate ORG_ID from SQL prompt?
11. What is meant by Client_info?
12. When user login in to the system how the system will identify user operating unit?
31
Flex Fields
Flex Field is nothing but a normal field but it is a ore flexible in the application either to extend
the form functionality or to capture the key information
We have 2 types of flex fields in oracle applications
1. Descriptive Flex Fields , 2. Key Flex Fields
1. Descriptive Flex Fields: DFF will be used to capture extra information from the end
user without changing form code and without altering database table
a. We will use the attribute column while working with DFF
b. We will find all the DFF in Application Developer Responsibility
i. N – Flex Filed – Descriptive – Register
c. To find DFF in form – PO Requisition Form
d. Customization of DFF
i. Open the form in application (PO, AP…) we will find the fields which are
enclosed with [ ] it is nothing but a DFF we can enable this DFF by using
Application Developer Responsibility
ii. Find the DFF title from following navigation
1. Help Menu – Diagnostics – Examine and enter password as apps
and Block name $Descriptive Flex field$ we will find the list of
DFFs in the fields select Field:
PO_REQ_HDR_DESCRIPTIVE_F4 we will find the DFF in the
value fields Value: Requisition Header (Oracle Purchasing)
iii. Copy the value go to Application Developer
1. N – Flex field – Descriptive – Segments and enter and press F11
enter the title Ctrl + F11
iv. Un check the checkbox called Freeze Flex field definition select segments
button enter the column names select attribute column and value set and
save the transactions.
v. Check the check box called Freeze Flex Field Definition click on compile
button internally system will submit concurrent program (Flex field view
Generation) to compile flex field it will generate one flex field view to
capture the data.
vi. The view name will be database table name _DFV
vii. Go to the form where DFF is attached, place the cursor, it will open DFF
form enter the data save it go to backend query the records.
Select Attribute3 Buyertype,
Attribute9 Buyerlocaton,
Attribute13 Last Date
From PO_REQUISITION_HEADER_ALL
Where segment1=’15467’
viii. Create the value set in system administrator; attach the value set at the
time of creating DFF column name in the value set field.
ix. We can make the DFF column is optional or mandatory by selecting
required check box in the segments form.
32
x. Context Field: While creation of DFF we can define multiple structures
based on the user selection we can change the DFF structure. While
defining the context field we will enter context field values for every
context value there will be a structure here we can use the entire attribute
column from attribute 1 to 15.
1. NOTE: One we use the attributes in one structure we can use the
same attributes in another structure, because user can enter only
one structure details at a time.
2. NOTE: Once we enter the value in the context field it will be stored
in the Database column called Attribute Category
xi. Global Data Elements: While defining the context field we will enter the
context field values based on the value we can change the DFF structure
if we want to define any field globally we will select option called Global
Data elements, select se4gments enter the details.
1. Once we use the attribute column in Global Data elements it will
not be available for the context field values.
2. Key Flex Field : These will be used capture key information which is in the code
language for every code there will be a specific meaning in the application we will find all
the KFF s in the Application Developer Responsibility. Navigation – Flex field – Key –
Register and enter we can find the structure in the segments form Navigation Flex Field
– Key – Segments and press enter when we enter the data in KFF it will be stored in
segments columns we will find segments column around 30 based on the requirement.
Differences between DFF and KFF
Item Location
Item Quantity
Item Attributes
Sub Inventory
Stock Location
Available Qty
Reserved Qty
Purchasable Item
Inventory Item
Customer Item
Production Item
Item Details
Inventory
33
DFF KFF
1. DFF will be used to capture EXTRA
Information
1. KFF will be used to capture KEY
Information
2. Attribute columns will be used 2. Segment columns will be used
3. We can register MAX DFF 3. We have 29 built in KFF we are not
suppose to create new KFF
4. DFF will be accept what ever Data we
enter
4. KFF we can define rules to capture
valid data
5. WE have context field 5. WE have no Context field
6. DFF data will be stored in Attribute
Columns
6. DFF codes will be stored in segments
KFF description will be stored in
FND_FLEX_VALUES tables.
To find a table of DFF – go to the segment form of DFF
System
Last Query
Value - Quary will get
KFF_List.xls file contains all the important KFF to study
KFF name Application
Name Table Staructure Column
Accounting Flexfield GL GL_CODE_COMBINATIONS CHART_OF_ACCOUNTS_ID
Assets KFF FA FA_ASSET_KEYWORDS No
Category Flexfield FA FA_CATEGORIES_B No
Location Flexfield FA FA_LOCATIONS No
Sales Tax Location
Flexfield AR AR_LOCATION_COMBINATIONS LOCATION_STRUCTURE_I
Territory Flexfield AR RA_TERRITORIES No
Item Catalogs INV MTL_ITEM_CATALOG_GROUPS no
Item Categories INV MTL_CATEGORIES_B STRUCTURE_ID
Stock Locators INV MTL_ITEM_LOCATIONS ORGANIZATION_ID
System Items INV MTL_SYSTEM_ITEMS_B ORGANIZATION_ID
• To Capture the Key Information
• We can query the Information at any level
• Data entry to the user will be very easy
• System will accept valid accounting transactions
• GL_CODE_COMBINATION – All Codes
• GLFV_CHARTS_OF_ACCOUNTS – Structure Details
•
Accounting Key Flex Field:
• This is one of the built in KFF available in GL application to capture accounting
transactions.
34
• Accounting KFF is nothing but Chart of accounts in the set of books when ever we
assign the profile (GL: Set of Books Name) to the user based on the this profile system
will assign Accounting KFF to the User.
*******
Set of Books
o Currency
o Calendar
o Chat of Account
*******
Select Name,
Currency_code Currency
Period_set_name Calendar
Chat_of_account_id ChartOfA/C
From GL_SET_OF_BOOKS
*******
16USER GL:SET OF BOOKS Vision Operation(USA) USD 101(5)
17USER GL:SET OF BOOKS Vision Italy ITL 50173(4)
18USER GL:SET OF BOOKS Vision Belgium BEL 50714(6)
*******
Structure Column
• While defining the KFF we will find structure column field if it is null value then KFF has
got only one structure, if there is a structure column that will find multiple structure, open
the segments form query the records based on KFF title, we will find structure details
• For Accounting KFF structure column is CHART_OF_ACCOUNTS_ID
• And we will get all the structure details from this table – GL_CHARTS_OF_ACCOUNTS.
• All the accounting KFF transaction will be stored in the data based table called
GL_CODE_COMBINATIONS
Developing the Reports on KFF - Process
1) Call the user ext from before report trigger
SRW.USER_EXIT ( 'FND SRWINIT');
2) Call another User Exit from After Report Trigger
SRW.USER_EXIT ('FND SRWEXIT');
3) Define the following parameters
1) P_CONC_REQUEST_ID
2) P_FLEXDATA
3) P_STRUCT_NUM segment1
'\n'
segment2
'\n'
segment3
'\n'
.......
'\n'
segment30
4) Call another User exit from before report trigger
SRW.USER_EXIT ('FND FLEXSQL
CODE = "GL#"
NUM = ":P_STRUCT_NUM"
35
APPL_SHORT_NAME = "SQLGL"
OUTPUT = "P_FLEXDATA"
MODE = "SELECT"
DISPLAY = "ALL");
5) Define the Query like follows
SELECT
&P_FLEXDATA
FROM
KFF Table
WHERE KFF Structure Column = :P_STRUCT_NUM;
6) Define Formula Column and CALL another User Exit from formula column
SRW.USER_EXIT ('FND FLEXIDVAL
CODE = "GL#"
NUM = ":P_STRUCT_NUM"
APPL_SHORT_NAME = "SQLGL"
DATA = "Column Name where the data is available"
VALUE = "to get KFF Values we will give formula column Name"
MODE = "SELECT"
DISPLAY = "ALL");
Flex Fields Reports development
P_FLEXDATA: It is one of the lexical parameter having the default value it is a contribution of
all the segment columns, we will use this lexical parameter in the select statement to retrieve
the data from database
P_STRUCT_NUM: This is a bind variable will be used to capture KFF structure number
FND FLEX SQL: It is one of the User Exit we will define in the before report trigger to retrieve
KFF segments data
FND FLEXIDVAL: This is another user exit will called from formula column to display the KFF
data in the output
1) Call the user ext from before report trigger
SRW.USER_EXIT ( 'FND SRWINIT');
2) Call another User Exit from After Report Trigger
SRW.USER_EXIT ('FND SRWEXIT');
3) Define the following parameters
1) P_CONC_REQUEST_ID
2) P_FLEXDATA
3) P_STRUCT_NUM
4) Before Report Trigger
function BeforeReport return boolean is
L_NAME VARCHAR2(100);
begin
36
SRW.USER_EXIT( 'FND SRWINIT');
l_name :=fnd_profile.value('GL_SET_OF_BKS_NAME');
select chart_of_accounts_id
into :P_STRUCT_NUM
FROM gl_sets_of_books
where name = l_name;
SRW.REFERENCE(:P_STRUCT_NUM);
SRW.USER_EXIT('FND FLEXSQL
CODE = "GL#"
NUM = ":P_STRUCT_NUM"
APPL_SHORT_NAME = "SQLGL"
OUTPUT = ":P_FLEXDATA"
MODE = "SELECT"
DISPLAY = "ALL" ');
return (TRUE);
end;
5) Go to data model select query
SELECT &P_FLEXDATA C_FLEXDATA,
GCC.LAST_UPDATE_DATE
FROM GL_CODE_COMBINATIONS GCC
WHERE CHART_OF_ACCOUNTS_ID = :P_STRUCT_NUM;
6) Take the Formula column and place it in query and edit the PL/SQL editor
function CF_DATAFormula return Char is
begin
SRW.REFERENCE(:P_STRUCT_NUM);
SRW.REFERENCE(:C_FLEXDATA);
SRW.USER_EXIT('FND FLEXIDVAL
CODE = "GL#"
NUM = ":P_STRUCT_NUM"
APPL_SHORT_NAME = "SQLGL"
DATA = ":C_FLEXDATA"
VALUE = ":CF_DATA"
MODE = "SELECT"
DISPLAY = "ALL"
IDISPLAY = "ALL"');
RETURN(:CF_DATA);
end;
function CF_DESCFormula return Char is
begin
SRW.REFERENCE(:P_STRUCT_NUM);
SRW.REFERENCE(:C_FLEXDATA);
SRW.USER_EXIT('FND FLEXIDVAL
CODE = "GL#"
NUM = ":P_STRUCT_NUM"
APPL_SHORT_NAME = "SQLGL"
DATA = ":C_FLEXDATA"
37
DESCRIPTION = ":CF_DESC"
MODE = "SELECT"
DISPLAY = "ALL"
IDISPLAY = "ALL"');
RETURN(:CF_DESC);
end;
7) Go to layout model design the report and register with Oracle Applications and submit from
the user SRS form
SRW.REFERENCE( ) : It we are using bind variable in source column the user exit we will
refer to get the latest values for variable and source column
Changing the structure number dynamically:
• KFF structure number is nothing but chat of accounts ID, if we know the user set of
books name we can find out chart of Accounts ID.
• Set of books name is one of the user profile by using FND profile API we can get from
report triggers
• Go to before report trigger declare the local variable write the following API
o L_name varchar2 (100)
o L_name:=fnd_profile.value(‘GL_SET_OFBKS_NAME’);
• Write the following select statement to get the structure number based on the set of
books name
Select Chart_of_account_id
INTO :P-STRUCT_NUM
From GL_SET_OF_BOOKS
Where name=L_name;
Develop the report on PO Module and display the charge account in PO Distributions Form
Line Level Dist Level
ReqNO Type Cdate Lineno Item ItemDesc DistNo ChargeA/C
FAQ in Flex Fields
1. What is FLEX FIELD?
2. What is KFF
3. In you experience what are the KFF you have come across?
4. Have u created any KFF? NO
5. Have u created any DF? Yes
6. What is FND FLEX SQL and FND IDVAL?
7. What is SRW REFERENCE?
8. What are parameters we suppose to pass for FND FLEXIDVAL?
9. How to change structure number dynamically?
10. What are the KFF are there in Inventory module?
11. Is there any KFF in PO? NO
12. Where the KFF data will be stored? Segments
13. Where the DFF data will be stored? Attributes
14. What in Attribute_Category?
38
Forms Development Process
1)We Will download the TEMPLETE.fmb from AU_TOP\Resource Folder
Application Utilities
2)Open with Forms6i builder and delete the existing Block
Canvas
Window
3)Create New Block , Canvas , Window attach oracle apps Properties
4)Develop the from as per client requirement
Note:We will change the Property called First Navigaction Data Block as Datablock Name in the
Module Properties.
II)Customize folooiwng two triggers1) Pre-Form -New Window Name
2) WHEN-NEW-FORM-INSTANCE -New form Name
3) APP_Custom package -New Window Name
5)Save the form we will get (.fmb)
6)compile the form we will get(.fmx)
7)Move the .fmb into au_top\11.5.0\Forms\US\ .fmb
8)Move the .fmx into CuS_top\11.5.0\Forms\US\ .fmx
9)Goto Applicaton Developer and create Form
10)create Function attach form to the function
11)Create Menu attach function menu
Executable
Concurrent
Programe
Request Group
Responsibility
User
Form
Function
Menu
Data Group
39
12)Attach Menu to the responsibility , Responsibility will be attached to the user
so that user can open the from and enter/Query the Data.
Develop the User Form
First Form
User ID
User Name
Creation Date
Second Form
Supplier ID
Supplier Name
Supplier Creation Date
Template.fmb: It is a built in form available in AU_TOP resources folder, it has got 21 libraries
to support menu functionality standard toolbar functionalities and other oracle apps functions
like who columns, profiles, flex fields, multi org and so on.
Form Registration:
• After moving the .fmb and .fmx in to the server we will take the .fmx name select
application developer responsibility register the form Navigation Application Developer
Application – Form and enter
• Enter the .fmx name and application name where the .fmx is located enter user form
name
• Copy the user form name attach to the function.
Function:
• It is nothing but instance of form along with parameter navigation Applicatoin – Function
and enter
• Enter the function name and user function name go to properties tab select type as form,
go to form tab attach user form name what ever we have created
• Copy the user function name attach to menu
Menu:
• Group of submenus and functions, enter menu name sequence no, prompt, attach user
function name in the function field, copy the user menu name, go to responsibility form in
system administrator attach in the menu field
• Select the responsibility we will find the form in object navigator window.
Form Setup
First step
AU/11.5.0/FORM/US
Copy the files in to resource folder like Templete.fmb and appstand.fmb
Second Step
40
Start menu – Run – Regedit
Hkey_local_mechine -> software -> oracle
Forms60 _path
- - - - - - - - - - -- - - -- - - ; give the template.fmd file path
Table registration
Note: connect to custom top (wip) --wip/wip@prod
1) create table custom_vendors
2) grant all on custom_vendors to apps;
3) conn apps/apps@prod
4) create public synonym custom_vendors for po.custom_vendors;
5) exec ad_dd.register_table ('po','custom_vendors','t', 8, 10, 90);
Exec Ad_dd.register_table(
p_appl_short_name ,
p_tab_name ,
p_tab_type ,
p_next_extent ,
p_pct_free ,
p_pct_used )
6) exec ad_dd.register_column ('po','custom_vendors','vendor_id', 1,'number', 10,'n','y');
Exec ad_dd.register_column (
p_appl_short_name ,
p_tab_name ,
p_col_name ,
p_col_seq ,
p_col_type ,
p_col_width ,
p_nullable ,
p_translate ,
p_precision default null,
p_scale default null)
Note: register all columns
7)commit;
Value sets
Who columns
41
Flexfield (dff,kff)
Note :
If we are registering table by using AD_DD package we can utilize following functions
1) Who columns
2) Flex fields
3) Multi org
4) Profile, Table Value set creation and so on.
Create Table wip_item_details(
Item Varchar2(50),
ItemDesc Varchar2(100),
ItemDate Date,
ItemLoc Varchar2(50),
Item Category Varchar2(50),
ItemCost Number(9),
Created_By Number(9),
Creation_Date Date,
Last_Update_By Number(9),
Last_update_date Date,
Attribute_Category Varchar2(100),
Attribute1 Varchar2(100),
Attribute2 Varchar2(100),
Attribute3 Varchar2(100),
Attribute4 Varchar2(100),
Attribute5 Varchar2(100))
Grant all on WIP_ITEM_DETAILS to APPS;
Conn APPS/APPS@PROD;
Create Public Synonym WIP_ITEM_DETAILS for WIP.WIP_ITEM_DETAIS
Table type contains three values
T – Table, V – View, S – Synonym
Exec AD_DD.REGISTER_TABLE (‘WIP’,’WIP_ITEM_DETAILS’,’T’,0,10,90)
EXEC AD_DD.REGISTER_COLUMN
(‘WIP’,’WIP_ITEM_DETAILS’, ‘ITEM’,1,’VARCHAR2’,50,’N’,’Y’);
All the columns to be registered
To know the table status about registration
Navigation
Application - > Database - > Table and press enter
And query for the required table.
42
Implementing who columns
• Create two triggers Pre_Insert and Pre_updata at Block level
• Call the following API from the both the triggers.
o Fnd_standard.set_who();
• This API is available in the library called ‘FNDSQF.PLL’
Implementing the calendar to the date field
• Go to the field properties, change the property called list of values – Enable_List_Lamp
• Create the trigger called KeyListVal at Item level call the following API
o Calender.show()
• It is available in the library called ‘APPDAYPK.PLL’
Attaching the list to the field
• Record Group-> LOV -> field
• Create Record Group by enter list of value or by enter select statement
• Create LOV attach record group
• Attach LOV to field property called list of values
Reference: 115devg.pdf - 24th Chapter
Development of Master Details Form
Master Form
PO-VENDORS
Vendor_ID, Vendor_name, Creation_Date
Detail Form
PO_VENDOR_SITES_ALL
Vendor_ID, Vendor_site_code, Site_Creation_Date,
Address1, Address2, City.
• Develop the form with master table by using template.fmb (Primary Key must be
selected)
• Crate detailed data block after selecting the column click next futon, can check the check
box called Auto join data blocks, select the button called create relationship, select radio
button called based on join condition.
• Select Primary Key from Master Item, reference key from detail Item, system will
automatically create join condition and select finish button.
• At the time of selection layout style select tabular format and select number of records to
be displayed in the layout.
43
• Attach property classes and save the .Ffmb generate .FMX move in to the custom top.
Practice development
PO_HEADERS_ALL
PO_header_id, PO_NO, type, Cration_Date
POLINE_ALL
Po_heder_id, item_desc, quantity, Unit_price,
Line_total(quantity*unitprice)
Manual Development of Form
Crate a table in WIP
Item, Item_Desc, Item_date, Item_loc.
• Create new data block by select the manual option
• Change the following the properties
o Subclass: Block
o Database source Name: Table Name
o Database Column Name: Column Names
• Go to canvas create and change the property class to Frame_Rect
• Define the Text Item attach following properties
o Subclass: Tex_Item
o Column Name: DB column
• Register the column and compile and generate .FMX
Descriptive Flex Field Form Development Process
• Table should contain attribute column and also it should be registered
• Go to application developer open the DFF register form navigation Flex Field –
Descriptive – Register and enter
• Create New DFF by giving database table name.
• Copy the DFF table go to segments form query based on table, select segments button,
enter DFF structure details.
• Open the template.fmb select all the attribute column at the time of data block creation
• Define the field in the Canvas change the field property database items as "no"
• Define the Package like follows.
Package Specification:
----------------------
PACKAGE DFF_PKG IS
PROCEDURE DFF_PROC(EVENT VARCHAR2);
END;
Package Body:
-------------
44
PACKAGE BODY DFF_PKG IS
PROCEDURE DFF_PROC(EVENT VARCHAR2) AS
BEGIN
IF (EVENT = 'WHEN-NEW-FORM-INSTANCE' ) THEN
FND_DESCR_FLEX.DEFINE(
BLOCK => 'WIP_ITEMS10',
FIELD => 'DFF_TEXT',
APPL_SHORT_NAME => 'WIP',
DESC_FLEX_NAME => 'WIPDFF' );
END IF;
END DFF_PROC;
END;
• Call this Package from WHEN-NEW-FORM-INSTANCE Trigger
o Packagename.Procedure name('WHEN-NEW-FORM-INSTANCE');
o DFF_PKG.DFF_PROC('WHEN-NEW-FORM-INSTANCE');
• Call another API from WHEN-NEW-ITEM-ISNATCE Trigger
o fnd_flex.event('WHEN-NEW-ITEM-INSTANCE');
o It will Populate the DFF in the Form.
Note:
FNDSQF library will support for all the flex field APIS like
FND_DESC_FLEX.DEFINE
FND_FLEX.EVENT
FND_KEY_FLEX
Function Security:
• For single form creation of multiple functions at the time of creation function passing the
parameter which will change the form functionality.
• Develop a form register in application developer at the time of creation function pass the
parameter called QUERYONLY=”YES”
• Attach the functions to menu and menu to responsibility and responsibility to the user.
• NOTE: At the time of crating function select form tab pass the parameter in the field
called parameter
Calling another form from the existing form.
Form 6i feature – Call_Form, New_form, Open_form
APPS - FND_FUNCTION.EXECUTE( )
OPEN_FORM built-in
Opens the indicated form. Use OPEN_FORM to create multiple-form applications, that
is, applications that open more than one form at the same time.
45
NEW_FORM built-in
Exits the current form and enters the indicated form. The calling form is terminated as
the parent form. If the calling form had been called by a higher form, Form Builder
keeps the higher call active and treats it as a call to the new form. Form Builder
releases memory (such as database cursors) that the terminated form was using.
Form Builder runs the new form with the same Runform options as the parent form. If
the parent form was a called form, Form Builder runs the new form with the same
options as the parent form.
CALL_FORM built-in
Runs an indicated form while keeping the parent form active. Form Builder runs the
called form with the same Runform preferences as the parent form. When the called
form is exited Form Builder processing resumes in the calling form at the point from
which you initiated the call to CALL_FORM.
1) Develop both forms and register in application.
2) Place the Button in first form
3) Write the following code in WHEN-BUTTON-PRESSED Trigger.
Fnd_FunCtion.execute (
function_name => 'Second from function Name',
open_flag => 'Y' ,
OTHER_PARAMS => 'P1 = '
:PO_VENDORS.VENDOR_ID);
P1 is Second form parameter
4) Open the Second form and define the Parmaeter called P1
5) Go to PRE-QUERY trigger and write the following code to change the query dynamically.
:PO_VENDOR_SITES_ALL.VENDOR_ID := :PARAMETER.P1;
--:Blockname .fieldname := :PARAMETER.Parametername
6) Goto the WHEN-NEW-FORM-INSTANCE Trigger at from level. write the following code:
GO_BLOCK('PO_VENDOR_SITES_ALL');
EXECUTE_QUERY;
Sub Menus
Sub menu is nothing but is collection of function and menus we will create the
submenus just like menus, then we will attach to main menu by using the field called
submenu.
Calling the SRS window from Menu
• Go to the function form in application developer (N) Application - funcatoin
• Create new function for the user form called Run Reports, copy user function name
attach to menu.
46
Adding Multiple Request Groups to the Responsibility
• Create Request Group in System Administrator
• Copy Request Group name application short name and Request group code.
• Go to application developer create function for the form called “Run Report” and pass
the following parameters in the parameter field
o REQUEST_GORUP_CODE = “20SUPPLIER_CODE”
o REQUEST_GROUP_APPL_SHORT_NAME=”PO”
o TITLE = “20SUPPLIERS”
Hide the menus and function at responsibility level (Menu Exclusions)
• At the time of creating responsibility we will attach menu by default all the submenus and
functions will be available if we want to hid we will go to Menu Exclusion tab in
Responsibility form select type as function or menu and give the function name or menu
name in the name field.
Forms Customization
Two Ways
1) Download .fmb from au_top\11.5.0\Forms\resource\US
2) Custom.pll
a. When_new_form_instance
b. When-new-block-instance
c. When-new-record-instance
d. When-new-item-instance
e. When-Validate-record
f. Special
g. Zoom
If we want to customize standard oracle forms will going to use custom.pll by using this we can
customize few events in the form. This custom.pll is available in the au_top – resource folder
1) Hiding the fields
2) Chane the promplt
3) Change visual attributes
4) Make field is mandatory
5) Attaching the menu options.
Before going to customization the form should know following details
• Form Name :
• Block Name :
• Field Name :
• What Customization :
• When we would like to do
Data capture
47
• Form Name : Help About Oracle Applications
• Block Name : Help Diagnostics – examine
• Minimum : Amount
• Hide the filed
• When form is opened (When-new-form-instance)
Forms Customization Steps:
1) Download the CUSTOM.pll from AU_TOP\11.5.0\Resource folder.
2) Open with Forms 6i.
3) Write the Following Code in the place of 'Real Code Starts Here'
form_name varchar2(30) := name_in('system.current_form');
block_name varchar2(30) := name_in('system.cursor_block');
begin
if (event_name = 'WHEN-NEW-FORM-INSTANCE') then
if (form_name = 'APXWCARD' and block_name = 'CARDS') then
APP_ITEM_PROPERTY2.SET_PROPERTY (
'CARDS.DEPARTMENT_NAME', -BlockName.FieldName
DISPLAYED,
PROPERTY_OFF);
APP_ITEM_PROPERTY2.SET_PROPERTY(
'PO_HEADERS.SEGMENT1',
PROMPT_TEXT,
'PO Number');
APP_ITEM_PROPERTY2.SET_PROPERTY(
'REGIONS.REGION_LONG_NAME',
CASE_RESTRICTION,
UPPERCASE);
end if;
end if; (attach the Library called APPCORE2.pll)
4) Compile the Library we can get .pll (Program link Library)
Execute the Library we will get .plx (Program Link Executable)
(File ->Administration->Compile File)
5) Transfer both .pll and .plx files into the Resource folder in the AU top.
(Note: before going to copy into the server close the complete Application)
Zoom Trigger
Zoom event will be used to call the another forms from Oracle standard forms, by default zoom
will be disabled for the form, first we have to enable, the \we will go for execution.
48
• Open the Custom.pll got the function call zoom available and creat the following code in
the place of “Real Code Starts here”
form_name varchar2(30) := name_in('system.current_form');
block_name varchar2(30) := name_in('system.cursor_block');
begin
if (form_name = 'POXRQERQ' and block_name = 'PO_REQ_HDR') then
return TRUE;
else
return FALSE;
end if;
• Go to the event procedure and write the following code in the place of “Real Code Starts
Here”
form_name varchar2(30) := name_in('system.current_form');
block_name varchar2(30) := name_in('system.cursor_block');
Begin
if (event_name = 'ZOOM') then
if (form_name = 'POXRQERQ' and block_name = 'PO_REQ_HDR') then
fnd_function.execute(function_name=>'PO_POXRQVRQ',
open_flag => 'Y');
end if;
end if;
• Ave the custom.pll and compile this execute the custom.pll
• Copy both .pll and .plx in to the resource folder (Close the applications before copying in
to the respective top)
Special Trigger: - Total 45 are available.
We will use the special trigger to attach the menu options to the forms.
FAQs:
1) What are the steps we will follow for the form development
2) What are the libraries are available in TEMPLETE.FMB?
3) How to register table and primary key?
4) Why do we need to register the table
5) How t o implement who columns?
6) How to attach a calendar?
7) How to call another form from the standard forms?
8) How to implement DFF in the forms?
9) What are the events we can use in custom.pll?
10) What is meant by zoom?
11) We can’t copy the CUSTOM.PLL in to the server when applications is opened?
a. In oracle applications all the forms are developed by TEMPLETE.FMB including
Navigator. So that, that time CUSTOM.PLL IS being used by the application we
can not update the library.
12) When we are working in the project we are not able to close all the users application
that time how to copy in to server.
49
a. We will be having the link file (crated by DBA), we will copy in to that path only
we have to logout and login then we can find the customization effects.
13) How to generate .fmx and .plx in LINIX operating system
a. In windows we will use CTRL + T
b. In linix we will use F60GEN command
F60GEN MODULE=TEST.FMB USERNAME/PASSWORD@HOST
F60GEN MODULE=CUSTOM.PLL TYPE=LIBRARY USERNAME/PASSWORD@HOST
14) In which top we will execute this F60GEN command
a. IN AU_TOP\11.5.0\FORMS\US
INTERFACES
Interface is nothing but a program will be used to transfer the data from flat files to database
table or from database table to flat files with validations. WE have two types of Interfaces like
Inbound Interface Outbound Interface.
Inbound Interface
It will be used to upload the data from legacy system into Oracle Applications base table
Note: Legacy system is nothing but other applications which is used by client like SAP, JAVA,
Mainframe so on.
Example: In implementation project we can implement forms and report so that users can enter
the data after implementation but the previous data will be available in the legacy system we
need to bring that data in to Oracle Applicatoins with validations.
Usage: SQL*Loader, .txt, .ctl, .dis, .bad, .log
Outbound Interface
It will be used to extract the data from Oracle Applications base tables in to flat files
Example: Client is using to applications one is for financial and another one is for
manufacturing, we have to transfer manufacture data in to financial applications, so that we can
generate the Invoices in financial applications.
Usage: UTL_FILE_PACKAGE
UTL_FILE.FOPEN() - Crate / Open file
UTL_FILE.PUT_LINE() - Transfer the data in to file
UTL_FILE.FCLOSE() - Close file
Exercise – OUTBOUND INTERFACE
We can generate flat file only in specified directory - to find directory list
Select * from V$PARAMETER Where NAME like ‘%UTL_%
Or
“INIT.ORA” file contains the information about active directory list.
50
Flat file Requirement
PO Number, Type, Creation date, Buyer Name,
PO Type – STANDARD Purchase orders only
Process Steps
1) Develop a procedure or package and register it as Concurrent Program
2) Writ the cursor to retrieve the data from database
3) Define the file by using UTL_FILE.FOPEN()
4) Open the cursor for loop and transfer the data in to file by using UTL_FILE.PUT_LINE()
5) Close the cursor and close the file by using UTL_FILE.FCLOSE()
Create or Replace PO_Information ( Errbuf OUT varchar2,
Retcode ouT varchar2,) as
Cursor c1 is Select pha.segment1 PoNum,
Pha.type_lookup_code POType,
Trunc( pha.creation_date) CDate,
Ppf.full_name Buyer
From po-headers_all PHA
Per_all_people_f PPF
Where PHA.type_lookup_code = ‘STANDARD’
AND PHA.agent_id = ppf.person_id;
l-id UTL_FILE.FILE_TYPE
Begin
l_id := UTL_FILE.FOPEN(‘d:\........................’, ‘PO_ORDERS.TXT’, ‘W’)
For c2 in c1 loop
Utl_file.Put_line (l_id, c2, PoNum
’$’
C2.POType
’$’
C2. CDate
’$’
C2.Buyer);
End loop;
UTL_FILE.FCLOSE(L_ID);
End PO_Informaton;
Inventory Outbound Interface
Requirement Fields - Item, itemid, itemdesc, uom, name, id , category
CREATE OR REPLACE procedure INV_Out( Errbuf OUT varchar2,
Retcode ouT varchar2,
f_id in number,
t_id in varchar2) as
cursor c1 is select
msi.segment1 item,
msi.inventory_item_id Itemid,
msi.description itemdesc,
51
msi.primary_uom_code Uom,
ood.organization_name name,
ood.organization_id id,
mc . segment1
','
mc.segment2 Category
from
mtl_system_items_b msi,
org_organization_definitions ood,
mtl_item_categories mic,
mtl_categories mc
where
msi.organization_id = ood.organization_id
and msi.inventory_item_id = mic.inventory_item_id
and msi.organization_id = mic.organization_id
and mic.category_id = mc.category_id
and msi.purchasing_item_flag = 'Y'
and msi.organization_id between f_id and t_id;
x_id utl_file.file_type;
l_count number(5) default 0;
begin
x_id:=utl_file.fopen('d:\oracle\proddb\8.1.7\plsql\temp','invoutdata.dat','W');
--select * from v$parameter where name like '%utl_file%'
for x1 in c1 loop
l_count:=l_count+1;
utl_file.put_line(x_id,x1.item
'-'
x1.itemid
'-'
x1.itemdesc
'-'
x1.uom
'-'
x1.name
'-'
x1.id
'-'
x1.category );
end loop;
utl_file.fclose(x_id);
Fnd_file.Put_line(Fnd_file.output,'No of Records transfered to the data file :'
l_count);
Fnd_File.Put_line(fnd_File.Output,' ');
Fnd_File.Put_line(fnd_File.Output,'Submitted User name '
Fnd_Profile.Value('USERNAME'));
Fnd_File.Put_line(fnd_File.Output,' ');
Fnd_File.Put_line(fnd_File.Output,'Submitted Responsibility name
'
Fnd_profile.value('RESP_NAME'));
Fnd_File.Put_line(fnd_File.Output,' ');
Fnd_File.Put_line(fnd_File.Output,'Submission Date :'
SYSDATE);
Exception
WHEN utl_file.invalid_operation THEN
fnd_file.put_line(fnd_File.log,'invalid operation');
utl_file.fclose_all;
WHEN utl_file.invalid_path THEN
fnd_file.put_line(fnd_File.log,'invalid path');
utl_file.fclose_all;
WHEN utl_file.invalid_mode THEN
fnd_file.put_line(fnd_File.log,'invalid mode');
utl_file.fclose_all;
WHEN utl_file.invalid_filehandle THEN
52
fnd_file.put_line(fnd_File.log,'invalid filehandle');
utl_file.fclose_all;
WHEN utl_file.read_error THEN
fnd_file.put_line(fnd_File.log,'read error');
utl_file.fclose_all;
WHEN utl_file.internal_error THEN
fnd_file.put_line(fnd_File.log,'internal error');
utl_file.fclose_all;
WHEN OTHERS THEN
fnd_file.put_line(fnd_File.log,'other error');
utl_file.fclose_all;
End INV_Out;
53
Inbound Interface
Process Steps:
1) Stage Table Cration
2) Control file development
3) PL/SQL Program
4) Submit Standard Program
• Inbound interface will be used to upload the data from legacy system in to Oracle
applications base tables.
• We will receive the flat file from the client then we will create staging table upload the
data from flat file in to stage table.
• Develop the PL/SQL program to validate the data weather it is valid or not, if it is
valid we will insert in to interface table, if it is not valid we will insert in to error table.
• Once the data is available in interface table submit the standard program from SRS
window, and we will transfer the data from interface table to base tables.
• Note
o Staging table is required because the flat file structure may or may not be
compatibles with base table structure.
o Interface table is required to make the pre validation and populate dynamic
records.
GL PO AP AR OM HRM
Flat File
Staging or Temp or Pre-Interface tables
Flat File Interface Table
Base Tables
.bad or .dis SQL* Loader
Error Message PL/SQL Block
Error Tables
Error Table Standard Program
Error Field
Error Report
54
• Journals
• Currency
Conversi
on
• Daily
Conversi
on Rates
• Requisitions
• RFQ
• Quotations
• Purchase
Orders
• Invoices
• Payments
• Customers
Invoices
Receipts
• Sales
Orde
rs
• Emp
GL Interface
CLASS – 42
GL_INTERFACE_TABLE
STATUS: This column will accept any string but we will always insert standards string called
new it indicates that we are brining new data in to General Ledge Applications.
SET_OF_BOOKS_ID: We have to enter the appropriate set of books ID, it should be valid set
of books id is available in GL_SETS_BOOKS table it is valid, otherwise i8t is invalid.
USER_JE_SOURCES_NAME: We have to enter the journal sources name for the transaction
we can find all the valid source names in the tabled called GL_JE_SOURCES.
USER_JE_CATEGORY_NAME: WE have to find out weather journal category is available in
the GL_JE_CATEGORIES table. It is is available then we will insert, otherwise we will reject.
CURRENCY_CODE: We have to enter the valid currency code in FND_CURREINCES table
we can find out weather it is valid or not.
Journal Flat File
XXXX Staging Table
GL_Interface
GL_JE_HEADERS, GL_JE_LINES,
GL JE BATCHES
.bad or .dis SQL* Loader
Error Message PL/SQL Block
Error Tables
GL Execution Journal Import
Report
55
ACCOUNTING_DATE and CREATION_DATE: Both columns will accept valid date but that
date should be less than or equal to System date.
CREATED_BY: WE have to enter valid user_id from FND_USER table we can identify weather
it is valid user_id or not.
PERIOD_NAME: We have to enter valid period name and period should be in the open status
from GL_PERIODS table we can find out weather it is valid period or not, from
GL_PERIOD_STATUS table we can find out period is in the open status or not.
ENTERED_DR and ENTERED_CR: Both columns will accept positive number Debit and credit
amount, both debit and credit should be equal otherwise account will be imported as suspense
account.
GROUP_ID: WE will enter unique group number while importing from interface table to base
table it will be used as parameter.
CTUAL_FLAG: This column will accept single character either ‘A’ or ‘B’ or ‘E’ a- Actual
amounts, B- Budget Amounts, E- Encumbrance Amounts
Reference 1 - Batch Name
Reference 2 - Batch Description
Reference 3 - Don’t Enter any value
Reference 4 - Journal Entry Name
Reference 5 - Journal Entry Description
Reference 11 to 20 - Don’t enter any values.
Chart_of_accounts_id - Don’t enter any values.
Transationc_date - Don’t enter any values.
Je-Batch_id - Don’t enter any values.
Je_header_id - Don’t enter any values.
Reference PDF : 115glup.pdf - Page no. 153
GL_JE_HEADERS
GL_JE_LINES
GL_JE_BATCHES GL INTERFACE
AP
AR
INV
FA
CM
F FIELS
56
Pre requisites for GL Interface:
1) Set of books should be defined (Currency, Calendar, Chart of Accounts)
2) 2) Currency Conversion Rates needs to be defined.
3) Accounting Period should be defined and also opened
4) Source name and as well as category name should be defined.
Process Steps:
1) We have received flat file from client
2) We have created Staging table as per flat file structure
3) Developed Control file and uploaded data
4) Developed PL/SQL Program to upload the data from stage into interface table
i. declare Cursor
ii. open cursor
iii. Validate each record
iv. If no invalid record then insert into interface table.
5) Run the journal import from GL => Journal => Import => Run
i. Give the two parameters 1)Source 2)Group ID
6) Open the Output if status is 'SUCEESS' then take Request ID.
7) open Journal Enter screen Query the records based on the %requestid% As
batch Name - Select Review journal button we can see the journal detailed
transaction
8) If we want correct the journals we can correct from Journal=>Import=>Correct
9) If we want delete the journals we can delete from Journal=>Import=>Delete
Temporary Table Creation
Create Table GL_INT_TEMP (
STATUS Varchar2(10),
SET_OF_BOOKS_ID Number(8),
ACCOUNTING_DATE, Date,
CURRENCY Number(8),
DATE_CREATED Date,
CREATED_BY Number(8),
ACTUAL_FLAG Varchar2(1),
CATEGORY Varchar2(10),
SOURCE Varchar2(10),
CURR_CONVERSION Number(8),
SEGMENT1 Varchar2(100),
SEGMENT2 Varchar2(100),
SEGMENT3 Varchar2(100),
SEGMENT4 Varchar2(100),
SEGMENT5 Varchar2(100),
ENTERED_DR Number(8),
ENTERED_CR Number(8),
ACCOUNTED_DR Number(8),
ACCOUNTED_CR Number(8),
GROUP_ID Number(8) );
57
Control File Creation – To transfer the data from flat file to staging table.
LOAD DATA
INFILE *
INSERT INTO TABLE GL_INT_TEMP
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(STATUS,
SET_OF_BOOKS_ID,
ACCOUNTING_DATE,
CURRENCY,
DATE_CREATED,
CREATED_BY,
ACTUAL_FLAG,
CATEGORY,
SOURCE,
CURR_CONVERSION,
SEGMENT1,
SEGMENT2,
SEGMENT3,
SEGMENT4,
SEGMENT5,
ENTERED_DR,
ENTERED_CR,
ACCOUNTED_DR,
ACCOUNTED_CR,
GROUP_ID)
BEGINDATA
"NEW",1,"11-AUG-2002","USD","11-AUG-
2002",1318,"A","Inventory","JETFORMS","Corporate","01","000","1410","0000","000",555,555,5
55,555,66
"NEW",1,"11-AUG-2002","USD","11-AUG-
2002",1318,"A","Inventory","JETFORMS","Corporate","01","000","1410","0000","000",554,554,5
54,554,66
"NEW",1,"11-AUG-2002","USD","11-AUG-
2002",1318,"A","Inventory","JETFORMS","Corporate","01","000","1410","0000","000",321,321,3
21,321,66
"NEW",1,"11-AUG-2002","USD","11-AUG-
2002",1318,"A","Inventory","JETFORMS","Corporate","01","000","1410","0000","000",431,431,4
31,431,66
"NEW",1,"11-AUG-2002","SGD","11-AUG-
2002",1318,"K","Inventory","JETFORMS","Corporate","01","000","1410","0000","000",1500,1500
,1500,1500,66
"NEW",1,"11-AUG-2002","EUR","11-AUG-
2002",1318,"A","Inventory","JETFORMS","Corporate","01","000","1410","0000","000",1600,1600
,1600,1600,66
58
Creation of PL/SQL procedure – to transfer the data from staging table to interface table
after validation.
CREATE OR REPLACE PROCEDURE GL_IN_PRO( Errbuf OUT VARCHAR2,
Retcode OUT VARCHAR2)
IS
-- cursor declaration
CURSOR gl_cur IS
SELECT
status ,
set_of_books_id ,
accounting_date ,
currency ,
date_created ,
created_by ,
actual_flag ,
category ,
source ,
curr_conversion ,
segment1 ,
segment2 ,
segment3 ,
segment4 ,
segment5 ,
entered_dr ,
entered_cr ,
accounted_dr ,
accounted_cr ,
group_id
FROM GL_INT_TEMP;
l_currencycode VARCHAR2(25);
l_set_of_books_id NUMBER(3);
l_flag VARCHAR2(2);
l_error_msg VARCHAR2(100);
l_err_flag VARCHAR2(10);
l_category VARCHAR2(100);
L_USERID NUMBER(10);
BEGIN
DELETE FROM gl_interface;
COMMIT;
FOR rec_cur IN gl_cur LOOP
l_flag:='A';
l_err_flag:='A';
--This PL/SQL Block will do the currency validation
--end of the currency validation
--Category Column Validation
BEGIN
SELECT USER_JE_CATEGORY_NAME
INTO l_CATEGORY
FROM GL_JE_CATEGORIES
WHERE USER_JE_CATEGORY_NAME = REC_CUR.Category;
59
EXCEPTION
WHEN OTHERS THEN
l_category:=NULL;
l_flag:='E';
l_error_msg:='Category does not exist ';
END;
Fnd_File.put_line (Fnd_File.LOG,'Inserting data into the Interface TABLE'
L_FLAG);
--End Category Column Validation
--User ID column validation
BEGIN
SELECT USER_ID
INTO L_USERID
FROM FND_USER
WHERE USER_ID = REC_CUR.created_by;
EXCEPTION
WHEN OTHERS THEN
L_userid:=NULL;
l_flag:='E';
l_error_msg:='User ID does not exist ';
END;
Fnd_File.put_line (Fnd_File.LOG,'Inserting data into the Interface TABLE'
L_FLAG);
--End of Created_by OR UserID column Validation
--Set of books Validation
BEGIN
SELECT set_of_books_id
INTO l_set_of_books_id
FROM GL_SETS_OF_BOOKS
WHERE set_of_books_id=rec_cur.set_of_books_id;
EXCEPTION
WHEN OTHERS THEN
l_set_of_books_id:=NULL;
l_flag:='E';
l_error_msg:='set of Books ID does not exist ';
END;
Fnd_File.put_line (Fnd_File.LOG,'Inserting data into the Interface TABLE'
L_FLAG);
-- End Set of books Validation
--Status Column validation
/* BEGIN
IF rec_cur.status = 'NEW' THEN
l_flag:= 'A';
ELSE
l_flag:= 'E';
Fnd_File.put_line (Fnd_File.LOG,'Status column has got invalid data');
END IF;
END;*/
BEGIN
SELECT currency_code
INTO l_currencycode
FROM fnd_currencies
WHERE currency_code=rec_cur.currency
AND currency_code='USD';
60
EXCEPTION
WHEN OTHERS THEN
l_currencycode:=NULL;
l_flag:='E';
l_error_msg:='currency code does not exists';
END;
Fnd_File.put_line (Fnd_File.LOG,'Inserting data into the Interface TABLE'
L_FLAG);
Fnd_File.put_line (Fnd_File.LOG,'Inserting data into the Interface TABLE'
L_FLAG);
--End of Actual Flag Column validation
IF l_flag!='E' THEN
Fnd_File.put_line (Fnd_File.LOG,'Inserting data into the Interface TABLE');
INSERT INTO gl_interface(status,
set_of_books_id,
accounting_date,
currency_code,
date_created,
created_by,
actual_flag,
user_je_category_name,
user_je_source_name,
user_currency_conversion_type,
segment1,
segment2,
segment3,
segment4,
segment5,
entered_dr,
entered_cr,
accounted_dr,
accounted_cr,
group_id)
VALUES
(rec_cur.status ,
rec_cur.set_of_books_id ,
rec_cur.accounting_date ,
rec_cur.currency ,
rec_cur.date_created ,
rec_cur.created_by ,
rec_cur.actual_flag ,
rec_cur.category ,
rec_cur.source ,
rec_cur.curr_conversion ,
rec_cur.segment1 ,
rec_cur.segment2 ,
rec_cur.segment3 ,
rec_cur.segment4 ,
rec_cur.segment5 ,
rec_cur.entered_dr ,
rec_cur.entered_cr ,
rec_cur.accounted_dr ,
rec_cur.accounted_cr ,
61
rec_cur.group_id);
END IF;
l_flag:=NULL;
l_error_msg:=NULL;
END LOOP;
COMMIT;
END GL_IN_PRO;
Purchase Order Inbound Interface
PO_HEADERS_INTEFACE
Column Name Validation Table Name
VENDOR_ID PO_VENDORS
VENDOR_SITE_ID PO_VENDOR_SITES_ALL
VENDOR_CONTACT_ID PO_VENDOR_CONTANTCT
AGENT_NAME PER_ALL_PEOPLE_F
ORG_ID HR_OPERATING_UNITS
DOCUMENT_LOOKUP_CODE Standard, Planed, Contract, Blanket
CREATION_DATE < = SYSDATE
CURRENCY_CODE FND_CURREINCES
SHIP_TO HR_LOCATIONS
PO Flat File
XXXX Staging Table Headers
XXXX Staging Table Lines
PO_HEADERS_INTERFACE
PO_LINES_INTERFACE
PO_HEADERS_ALL
PO_LINES_ALL
PO_LINE_LOCATIONS_ALL
PO_DISTRIBUTIONS
.bad or .dis SQL* Loader
Error Message PL/SQL Block
Error Tables
PO_INTERFACE Standard Program
_ERROR
62
BILL_TO HR_LOCATIONS
PO_LINES_INTERFACE
Column Name Validation Table Name
LINE_NUM PRIMARY KEY
LINE_TYPE PO_LINE_TYPES
ITEM
ITEM_ID
ITEM_DESCRIPTION
MTL_SYSTEM_ITEM_B
ITEM_CATEGORY MTL_CATEGORIES
ITEM_UOM MTL_UNITS_OF_MEASURE
QUANTITY Any Positive number
UNIT_PRICE Any Positive number
NEED_BY_DATE
PROMISED_DATE > = PO CRATION DATE
SHIP_TO_ORG_ID ORG_ORGANIZATION_DEFINITIONS
SHIP_TO_LOCAITONS HR_LOCATIONS
ORG_ID HR_OPERATING_UNITS
Pre Requisites
1) Supplier sites contact details should be defined
2) Organization structure should be defined
3) Ship to bill to locations needs to be defined
4) Set of books needs to be defined
5) Items, item categories, UOM needs to be defined
6) Employee creation, buyer setup should be defined.
Process Steps
1) Create the Staging tables
2) Develop the Control files and register as concurrent program
3) Develop the PL/SQL Program and write the validations and insert into interface table
4) Run the standard program called Import Standard Purchase orders from PO
Responsibility
Parameter : Default Buyer : Null
Create or update items : No
PO Status : APPROVED
Batch ID : 13
(We can get from headers interface table)
5) Take the Request ID execute following query we can get the PO numbers
select segment1 Ponumber
from po_headers_all
where request _id = 145233;
6) Go to the PO Application and Query the PO from as per the PO number.
63
Control File: - PO_HEADERS_INTERFACE
load data
infile *
TRUNCATE into table XX_HEADERS
fields terminated by "," optionally enclosed by '"'
TRAILING NULLCOLS
( INTERFACE_HEADER_ID
,BATCH_ID
,ACTION
,ORG_ID
,DOCUMENT_TYPE_CODE
,CURRENCY_CODE
,AGENT_NAME
,VENDOR_NAME
,VENDOR_SITE_CODE
,SHIP_TO_LOCATION
,BILL_TO_LOCATION
,APPROVAL_STATUS
,FREIGHT_CARRIER
,FOB
,FREIGHT_TERMS
)
BEGINDATA
1,13,"ORIGINAL",204,"STANDARD","USD","Stock, Ms. Pat","IBM","IBM-BAN","H1-
Detroit","V1- New York City","APPROVED","UPS","Origin","Due"
2,13,"ORIGINAL",204,"STANDARD","USD","Stock, Ms. Pat","SONY","SONY-CHN","H1-
Detroit","V1- New York City","APPROVED","UPS","Origin","Due"
Control File: - PO_LINES_INTERFACE
load data
infile *
insert into table XX_LINES
fields terminated by "," optionally enclosed by '"'
TRAILING NULLCOLS
( interface_header_id
,interface_line_id
,LINE_NUM
,SHIPMENT_NUM
,LINE_TYPE
,ITEM
,ITEM_DESCRIPTION
,item_id
,UOM_CODE
,QUANTITY
,UNIT_PRICE
,SHIP_TO_ORGANIZATION_CODE
,SHIP_TO_LOCATION
,list_price_per_unit)
64
BEGINDATA
1,1,2,2,"Goods","AS54999","Sentinel Standard Desktop -
Rugged",2155,"Ea",10,120,"M1","Adelaide",45
1,2,3,3,"Goods","75100005","Colour Cartidge",6076,"Ea",11,111,"M1","Adelaide",55
1,3,4,4,"Goods","AS54999","Sentinel Standard Desktop -
Rugged",2155,"Ea",1,1400,"M1","Adelaide",65
2,4,5,1,"Goods","AS54999","Sentinel Standard Desktop -
Rugged",2155,"Ea",222,234,"M1","Adelaide",105
Procedure for Insertions of data from Interface tables to Base tables after validating.
CREATE OR REPLACE PROCEDURE PO_Int1(Errbuf OUT VARCHAR2,
Retcode OUT VARCHAR2) AS
CURSOR c1 IS SELECT * FROM PO_HEADER_TABLE;
CURSOR c2 IS SELECT * FROM PO_LINE_TABLE;
l_vendor_id number(10);
l_item varchar2(150);
l_flag varchar2(4) default 'A';
l_msg varchar2(200);
l_site_code varchar2(100);
l_curr_code varchar2(10);
l_org_id number(6);
BEGIN
DELETE FROM PO_HEADERS_INTERFACE;
DELETE FROM PO_LINES_INTERFACE;
COMMIT;
FOR x1 IN c1 LOOP
BEGIN
SELECT vendor_id
INTO l_vendor_id
FROM po_vendors
WHERE vendor_name = x1.VENDOR_NAME;
-- AND ORG_ID = Fnd_Profile.Value('ORG_ID');
EXCEPTION
WHEN OTHERS THEN
l_flag := 'E';
l_vendor_id := 0;
l_msg := 'Vendor id is Not in SYSTEM';
END;
--Vendor Site code Validation
/* begin
select vendor_site_code
into l_site_code
from po_vendor_sites_all
where vendor_site_code = x1.vendor_site_code;
EXCEPTION
WHEN OTHERS THEN
l_flag := 'E';
65
l_site_code := 0;
l_msg := 'Vendor Site Code is Not in SYSTEM';
END;*/
--End of Site Code Validation
--Currency Code Validation
Begin
select currency_code
into l_curr_code
from fnd_currencies
where currency_code = x1.CURRENCY_CODE;
EXCEPTION
WHEN OTHERS THEN
l_flag := 'E';
l_curr_code := 0;
l_msg := 'Currency Code is Invalid';
END;
--End of te Currency Validation
--Operating Unit ID Validation
Begin
select organization_id
into l_org_id
from hr_operating_units
where organization_id = x1.org_id;
EXCEPTION
WHEN OTHERS THEN
l_flag := 'E';
l_org_id := 0;
l_msg := 'Invalid Organization ID';
END;
--End of the ORG ID Validation
IF l_flag != 'E' THEN
INSERT INTO po_headers_interface
(
INTERFACE_HEADER_ID
,BATCH_ID
,ACTION
,ORG_ID
,DOCUMENT_TYPE_CODE
,CURRENCY_CODE
,AGENT_NAME
,VENDOR_NAME
,VENDOR_SITE_CODE
,SHIP_TO_LOCATION
,BILL_TO_LOCATION
,creation_date
,APPROVAL_STATUS
,APPROVED_DATE
,FREIGHT_TERMS
)
VALUES
(
66
x1.INTERFACE_HEADER_ID
,x1.batch_id
,x1.action
,x1.org_id
,x1.document_type_code
,x1.CURRENCY_CODE
,x1.AGENT_NAME
,x1.VENDOR_NAME
,x1.VENDOR_SITE_CODE
,x1.SHIP_TO_LOCATION
,x1.BILL_TO_LOCATION
,SYSDATE-10
,x1.APPROVAL_STATUS
,SYSDATE
,x1.FREIGHT_TERMS
);
end if;
END LOOP;
FOR x2 IN c2 LOOP
l_flag := 'A';
--Item Validation
begin
select segment1
into l_item
from mtl_system_items_b
where segment1 = x2.item
AND ORGANIZATION_ID = fnd_profile.value('ORG_ID');
exception
when others then
l_flag := 'E';
l_vendor_id := 0;
l_msg := 'Item is not valid Item';
END;
--End of the Item Validation
if l_flag != 'E' then
INSERT INTO PO_LINES_INTERFACE
(
INTERFACE_LINE_ID
,INTERFACE_HEADER_ID
,LINE_NUM
,SHIPMENT_NUM
,LINE_TYPE
,ITEM
,ITEM_DESCRIPTION
,item_id
,UOM_CODE
,QUANTITY
,UNIT_PRICE
,SHIP_TO_ORGANIZATION_CODE
,SHIP_TO_LOCATION
,NEED_BY_DATE
67
,PROMISED_DATE
,list_price_per_unit
)
VALUES
(
x2.INTERFACE_LINE_ID
,x2.INTERFACE_HEADER_ID
,x2.LINE_NUM
,x2.SHIPMENT_NUM
,x2.LINE_TYPE
,x2.ITEM
,x2.ITEM_DESCRIPTION
,x2.item_id
,x2.UOM_CODE
,x2.QUANTITY,
X2.UNIT_PRICE,
X2.SHIP_TO_ORGANIZATION_CODE,
X2.SHIP_TO_LOCATION,
X2.NEED_BY_DATE,
X2.PROMISED_DATE,
X2.LIST_PRICE_PER_UNIT);
END IF;
END LOOP;
COMMIT;
END PO_INT1;
Conversion
Conversion is one of the program which will be used to upload the data from legacy system in to
oracle applications, base tables, one time data transfer like
• Customer Conversion
• Supplier Conversion
• Employee Conversion
• Items Conversion and so on
Differences between Inbound Interface and Conversion
Inbound Interface Conversion
It is schedule concurrent process which will
be executed multiple times
One time data transfer
WE will not be knowing the flat file volume We will be knowing the exact flat file
volume
We need to handle the all the expected
exceptions
We do not need to know all the exceptions
Error Reports and sending email alerts, if
any error occurs
It is not required we have to upload all the
records and close.
Enhancement or customization projects Implementation, Migrations or up-gradation
projects we will find.
68
Request Set
• It is nothing but collection of concurrent programs and report, which will be used to
submit more than one program at a time from SRS windows
• Navigation – Concurrent – Set and enter - Select Request Set Wizard button, select
sequentially or parallel option, enter request set name and enter concurrent programs
select finish button.
• Go to the request group, select type as set attach request set.
• Go to SRS window select Request set option instead of single request.
Executable
Concurrent
Programe
Request Set
Request Group
Responsibility
User
SRS
69
Accounts Payables
Procure to Pay Cycle
• Without supplier we cann’t create invoices
• Without Invoices we can’t make payments.
Types of Invoices
1) Standard – For all the normal payments we will crate this.
2) Prepayment - Advance Payment to the supplier based on the requirement.
3) Credit Memo - If supplier give the discount - for negative amount
4) Debit Memo - If company is going to deduct the amount - for negative amount
5) With holding tax – if supplier is not recognized, the on be half of the supplier company
will pay the tax.
6) PO Default - We will give the PO number according to that it will generate the
Invoices.
7) Recurring Invoices – WE will generate the Invoices multiple times for fixed supplier, fixed
amount, fixed duration.
8) Expense Report – Employee expenses
9) Quick Match – 2 Way, 3 Way, 4 Way
10) Mixed Invoice – Miscellaneous expenses
After creating the invoice we have to do the 3 steps
1) Validate Invoice with Distribution lines – Invoice Amount = Distribution Amount
2) Approve Invoice
3) Create Accounting Transactions.
GL AP PO
Requisition
RFQ
Quotation
PO
Receipts
Invoices
Payments
Transfer to GL
70
Creating Invoice
Payables, Vision Operations (USA)
Tools – View Accounting
AP_INVOICES_ALL
AP_INVOICES-DISTRIBUTIONS_ALL
Creating accounting transactions these data will be stored in the following tables.
AP_AE_HEADERS
AP_AE_LINES
NOTE: WE can give same number to other invoice number for other suppliers.
• Open the Invoice form N-Invoices-Entry-Invoices select Invoice type supplier data enter
Invoice number and amount.
• Select distributions button and enter the distribution details save the transactions.
• Compare Invoice table and distribution total
• Select actions button check the checkbox called approve to get the Invoice approval
• Select actions button check the check box called crate accounting, it will crate invoice
accounting transactions.
• Select tools menu view accounting option to find the accounting entries.
Distribution Set:
• (N) Setup – Invoices – Distribution Set and press enter – It will be used to generate the
invoice distributed automatically. AT the time of creating invoices we will attach
distribution set which will distribute the amounts
• Tables
o AP_DISTRIBUTION_SETS_ALL
o AP_DISTRIBUTION_SETS_LINES_ALL
• After creation of invoice we have to submit the transfer program called – Paybles
transfer to General Ledger.
o Copy the Invoice data, go to SRS window select the following program
�� Payables Transfer to General Ledger
• From Date
• To Date
o After completion of this program system will automatically submits the ‘Journal
Import’ program to transfer in the GL Base Tables.
o Copy the Request ID of ‘Journal Import’ program go to GL application
o Open the journal enter screen and enter %Request Id% in the batch filed which
will shows the Invoice date
o Select the Review Journal button which will displays the invoices detailed
transactions.
71
Process Steps
1) AP Table –
a. AP_INVOICES_ALL
b. AP_INVOICES_DISTRIBUTIONS_ALL
2) Payables transfer to General Ledger
3) Select * from GL_INTERFACE
4) Journal import program
a. GL_JE_BATCHES
b. GL_JE_LINES
c. GL_HEADERS
5) Positing Journals
a. Select * form GL_BALANCES.
Payment in Payables
1) Quick - System will automatically generate check
2) Refund – Refund Approval payments
3) Manual – Cash or check
• Crate the Invoice validate approve it and crate accounting transactions, select payments
from (N) Payments – Entry – Payments and enter select Payment type, Bank Account
Name, Document as check system will automatically generate check number enter
supplier details, select the button called enter invoices select invoice number save the
transactions.
• Select actions button, check the check box called crate accounting to create accounting
transactions, select tools menu, view accounting option, it will displays accounting debit,
and credit transactions, select the button called payment overview it will give complete
payment details.
Important AP Payment Tables
• AP_INVOICES_ALL
• AP_INVOICE_PAYMENTS_ALL
• AP_CHECK_ALL
• AP_BANK_ACCOUNTS
• AP_BANK_BRANCHES
Payment Holds – Invoice needs re-approval then we can check hold buttoned and see
• AP_HOLD_ALL
• AP_HOLD_RELEASE_NAME_V
Payment Terms
• AP_TERMS
72
• AP_TERM_LINES
• AP_APYMENT_SHEDULES – It contains the scheduled payment details
Expense Report
• AP_EXPENSE_REPORTS_HEADERS_ALL
• AP_EXPENSE_REPORTS_LINES_ALL
Recurring Invoices
• AP_RECURRING_PAYMENTS_ALL
ORACLE INVENTORY
Items Item Attributes
Assign Organizations
Assign to categories
Define lot / serial no
Define revisions
Unit of Measure
Item Attributes
Purchasable
Inventory
WIP Item
Customer
Stackable
Item Transactions Sub Inventory Transfer
Miscellaneous Transfer
Move Orders
Inter Organization Transfer
Item Locations Inventory
Sub Inventory
Stock locations
Item Quantities Items on Hand Qty
Items Resserved Qty
Item Available Qty
Items
Items Defining – Master Items
ORG-ORGANIZATIONS_DEFNINITIONS
MTL_SYSTEM_ITEMS_B
To Assign the Multiple Organizations (N) Tools -> Organization
To Revisions – Tools - > Revision
MTL_ITEM_REVISOINS - Inventory_item_id and Org_id
To know quantity – MTL_ONHAND_QUANTITIES
Do PO Cycle -
Inventory – Receiving – Receiving Transactions
• Open the Items form, enter Item name and description select the tabs and check the
check box called Inventory items, and Purchased Item - Go to tools menu select option
73
called organization assignment to assign the item to the organization. Select Revision
option to create Item revisions.
• Open the on hand quantity form enter Item name select find button, it will give item
quantity and details report.
• Once we create item quantity will be zero we have to purchase from suppliers or we
have to receive from other organizations.
• Select PO Applications create requisitions purchase order and receipt copy the receipt
number to Inventory applications, open the receiving transaction form (N) Transactions
– Receiving – Receiving Transactions ----- Enter the receipt number select find button
check the check box save the transactions.
o MTL_SECONDARY_INVENTORIES
o MTL_ITEM_LOCATIONS
o MTL_ITEM_CATEGORIES
o MTL_CATEGORIES
o MTL_MATERIAL_TRANSACTIONS
o MTL_MATERIAL_ACCOUNTS
o MTL_TRANSACTION_ACCOUNTS
• (N) Setup – Organization- Sub inventories and press enter
o WE can transfer the Item form one sub Inventory to another sub inventory by
using sub inventory transfer
o MTL_TRANSACTION_TYPES
o MTL_MATERIAL_TRANSCATIONS
o MTL_TRANSACTION_ACCOUNTS
• Other Tables
o MTL_PARAMETERS – Contains both Master and child organization
o FINANCIAL_SYSTEM_PARAMETERS – Financial setup details
o CST_ITEM_COST – Item cost Details
o CST_ITEM_COST_TYPES
o ITEM_LOT_SERIAL_NUMBERS
o MTL_LOT_NUMBERS
o MTL_SERIAL_NUMBER
o MTL_UNITS_OF_MEASURE
Order Management
Enter Sales Order - We get orders from Customer
Book Order - Customer Confirmation
Pick release - Release the Materials from Inventory
Ship confirm - Delivery Details, Shipping Details.
Auto Invoice - Generate the Invoice
After Pick Release if the goods are not available again PO cycle we have to follow
74
Drop Shipping : Sales and Purchase the materials without having the Inventory
Client
Supplier Customer
Sales Order Creations:
• (N) Orders & Returns – Sales Orders and data 2 levels headers and lines header level
customer Information customer ship to bill to and sales person other tax details, select
line items tab enter the Item, quantity, price and other details select the button called
book order to book the sale orders select availability button it will gives the Item available
on hand reserved quantity details select ATP details button it will give the available to
promise date details.
• Select actions button to cancel the sales order to calculate the tax and to keep the sales
orders on hold and release the hold and so on.
o OE_ORDER_HEADERS_ALL
o OE_ORDERS_LINES_ALL
Release the Sales Orders
• (N) Shipping – Release Sales Orders – Release shipments and enter the, and the enter
sales order number select the button called concurrent, it will submit the concurrent
program to release the sales order it picks Items from Inventory tables and user in to
deliver tables.
Pick confirmation and Ship Confirmation
• Once the Items are released, we will confirm the materials packing, then we will give the
shipping date (N) shipping – Transaction and enter
o RA_CUSTOMER_TRS_ALL
o RA_CUSTOMER_TRS_LINES_ALL
o WSH_DELIVER_DETAILS
o OE_ORDER_LINES_ALL
o OE_ORDER_HEADER_ALL
o WSH_NEW_DELIVERS.
OTHERs
Concurrent programs – Incompatibilities
• It is nothing but not compatible for other programs we will select this button we will list
out the programs which are not compatible.
• Run Alone check box: By using this check box we can make the program is incompatible
for all other concurrent programs.
• Copy to Button: By using this button we can create new concurrent program including
the parameters and incompatible programs.
75
Pair Value Set:
• We want to pass KFF data as a parameter to the executable then we will use validation
type as paid select edit information button. Pass the parameters like applications short
name KFF CODE, Structure number and so on
o Example: GL_SRS_LEDGER_FLEXFIELD
$FLEX$
• It will be used to retrieve the previous parameter value in to the next parameter values
set
o : $FLEX$.Previous Value set name
$PROFILE$
• To Get the front end the table value set where clause from backend we will
o Fnd_profile.value
o Fnd_profile.get
o Select segment1, org_id from PO_HEADERS_ALL
WHERE ORG_ID=$PROFILE$.ORG_ID
ERP - ORACLE APPLICATIONS
Pre-requisites to learn Oracle Applications
Oracle 9i
SQL - Reference books
• DDL Commands
• DML Commands
• TCL Commands
• Quarries, Sub Quarries
• Operators
PL/SQL - Reference books – by author Scott Urman
• Cursors
• Trigger
• Packages, Procedure, Functions
Forms 6i - Reference books – by Evan Baris
• Canvases – types – functionality
• Property Classes
• Visual attributes
• Programe Units
• Record Group
• Triggers – Sequences
• Form functions – like call form
Reports 6i
• Variables – like Bind, Lexical
• Columns - like Summery, Placeholder, Formula
• Report Triggers – 6 types and sequence
• Format Triggers and Validate triggers
• Layout objects
o Frame
o Repeat frame
o Anchor
o Boilerplate
2
Oracle Applications 11i - Introduction
ERPs available in market
• SAP
• Oracle Applications
o People soft
o Siebel
o JD Edwards
Oracle Applications are used to capture Business functionality information of the
organization.
• Instances
o Development - Development
�� Development stage 1
�� Development stage 2
o Testing - Testing
o Production – Client testing
Involved personalities of Oracle Applications and their roles and responsibilities
in brief
1) DBA – Database Administrator
a. Installation of Oracle Applications
b. Applying patches
c. Maintaining Multiple Instances
2) Functional Consultant
a. Interaction with the client
b. Gathering the requirements for development / customization
c. About oracle applications what available and what wasn’t available.
d. Example invoice form – fields - Or develop a invoice form from scratch
e. Preparation of FDD (Functional Design Document)
3) Technical Consultant – Input FDD
a. Go through the FDD
b. Prepare TDD (Technical Design Document) – Logic, Tables, Procedures,
Forms, Menus, Packages – approval
c. Development of Component
d. Test the component
e. Deliver to the client
All these sessions will go trough the below mentioned components
R I C E - Components for development 70%
�� R - Reports - Reports 6i
�� I - Interfaces - Programs, SQL, PL/SQL
�� C - Conversion - Programs, SQL, PL/SQL
�� E - Extensions - Forms 6i
Functions Knowledge 30%
• Purchase Order Module
• Accounts Payable Module
• Order Management Module
• Inventory Module
3
I) Types of Projects
a. Implementation Project: Client will be using another software to
maintain the business solutions. Now the client wants to have Oracle
Applications package to capture the data. That time we will start setups,
data conversions, gap analysis, functions document preparation from the
scratch. - first time
b. Customization Project: Client was already using Oracle Applications,
now the client would like to have few more modules to cover the
business, for that we will customize new modules and integrate with
existing modules.
c. Migration or Up-gradation Project: Client was already using Oracle
Applications older version now client would like to move new version of
Oracle Applications that time we will migrate older version objects in to
new version.
Version Database GUI
10.7 Oracle 7 Forms 4.5, Reports 2.5
11.0.3 Oracle 8 Forms 6i, Reports 6i
11.5.3 Oracle 9i Forms 6i, Reports 6i
11.5.4 Oracle 9i Forms 6i, Reports 6i
11.5.5 Oracle 9i Forms 6i, Reports 6i
11.5.6 Oracle 9i Forms 6i, Reports 6i
11.5.7 Oracle 9i Forms 6i, Reports 6i
11.5.8 Oracle 9i Forms 6i, Reports 6i
11.5.9 Oracle 9i Forms 6i, Reports 6i
11.5.10 Oracle 10g Forms 6i, Reports 6i, Java, JDeveloper
d. Support or Maintenance: Once the implementation, customization and
migration are over, then supporting project will be started where we will
give supporting for the objects which were already developed, customized
and migrated.
Existing
Ora Apps
Version
10.7
New Version
11.5.9 or 11.5.10
Existing
Ora Apps
Few modules
Financial Modules
Migration
Manufacturing Modules
(Customization and
Integration)
Old System
Cobal
Fox Pro
New System
Oracle Apps
4
II) Types of Modules
Module: Module is nothing but one application it contains forms, reports and
programs which are related for specific business functionality.
Example: PO – Module – This will be used to capture purchasing information like
suppliers, their contact details, supplying material, unit price and discounts and so
on.
ERP
Financial Manufacturing HRMS CRM
AP – Accounts
Payable
PO – Purchase
Order
People Customer
Relationship and
Management
AR – Accounts
Receivables
Inv – Invoicing Payroll Tele Services
FA – Fixed Assets WIP – Work in
Process
Benefits Service Contract
CM – Cash
Management
BOM – Bills of
Materials
OTL – Oracle Time
& Labour
Mobile Process
GL – General
Ledger
OM – Order
Management
PA – Project
Accounting
Production
MRP Schedules
Enquiring
III) Modules Integration :
PO
Supplier Inventory
WIP
Production
BOM
MRP Scheduling
QA
Testing
OM
Sales, Shipping
GL AR
CM
AP
HRMS
FA
5
4) Types of Documents: The methodology of behind is
AIM – Application Implementation Methodology developed by Oracle Corporation
R I C E Number Name Prepared by
MDO 50 Functional Design Document Functional Consultant
MDO 70 Technical Design Document Technical Consultant
Module
Design
Forms &
Reports
MDO120 Migration Document Technical Consultant
CVO40 Conversion functional Design
Document
Conversions Functional Consultant
Interface
CVO 60 Conversion Technical Design
Document
Technical Consultant
Support CR Document Change Request Technical Consultant
NOTE:
Migration Document is two types
1) Installation Process: Installation Stages or three types’ development, testing,
production. If any of the form or report is developed, when it is moving from one
instance to another instance, to be moved all the components of that. Example
functions, procedure, packages etc. This process will be done by DBA.
2) Execution Process: In this the execution of the form or report with a screen
shoot to be specified along with the functionality
CR Document:
The document tell about the request made by the client, it could be very small change
like single filed to be added or deleted to the form or procedure to be deleted.
5) Oracle Applications File Architecture
Linux (Server)
Windows (Client) Windows (Client)
FTP
FTP
6
Custom_ TOP:
Custom top will be created by customer (client) for the custom development and
customizations. At least one custom top is required for every client. We can
have multiple custom tops also as per the requirements
NOTE:
1) We are not suppose to develop are customize the product tops like AP, AR, GL, PO
2) If oracle applies the patches (scripts) against the application the script will over right
both developments and customization whatever we have done. That is the reason
we will us custom top. Oracle will not touch the custom top and it will not provide any
type of support.
US Folder:
This folder is language specific by default American English language will be
implemented. If we want to have multiple languages we can implement in Oracle
Applications by specifying different folders. This folder is only applicable for
Forms and Reports because both are GUI objects.
Select * from FND_LANGUAGE where installed_flag in (‘B’, ‘I’)
Flag B - Base language, Flag I - Installed language,
Flag D - Disable language
11.5.0 Folder:
This oracle application release name based on the release it will be specified
10.7 10.7
11.0.3 11.0.3
11.5.3
11.5.4
11.5.5
11.5.6
11.5.7
11.5.8
11.5.9
11.5.0
11.5.10 11.5.10
APPL_TOP
CUS_TOP PO_TOP AP_TOP GL_TOP INV_TOP AR_TOP
11.5.0 11.5.0 11.5.0 11.5.0 11.5.0 11.5.0
Forms Reports SQL PL/SQL BIN Mesg., Out Admin
US US
..fmx ..rdf
.SQL .proc
.func
.pkg
.pkgsc
.pks
C Prog
.java
.ctl
.shell
script
.msg .outfile Admin
script
7
Oracle Applications Database Design
Predefined database Oracle 9i
Schema Diagram Item Details Report
Item Supper Cheque Buyer
Monitor IBM 1745/- Stockpet
Invoice
Schema
PO
Schema
AP
Schema
HRMS
Schema
• User connect only one schema at time
• Total approximately 4000 schemas available
• APPS Schema contains only synonyms
• Per ever database object we will find synonym in APPS Schema
• Table name and synonym name should be same
• No module to cover the Share Market Business
• New Module – New Schema – New Top
APPS Schema:
This one of the Schema in Oracle Applications database it contains only
synonyms. It has got access other schema database objects
PO Schema:
It contains only PO Objects like PO Tables Indexes, Sequences, and views and
materialized views and so on. It will connect to PO Schema we can access only
PO objects. We can’t access other schema database objects.
NOTE
• We are not suppose to create tables in apps schema, we will create the tables in
custom schema then we will provide grants to apps schema
• While development of forms, reports, programs we will always connect to apps
schema in oracle applications.
AOL – Application Object Library
After o development / customization
n the objects to be registered with this. This has got the two responsibilities.
1) System Administrator: Which has got the functions like 1) users, 2)
Responsibilities 3) Reports development / customization 4) Procedure 5) Printer
Installation 6) Admin activities - Server Monitoring, Request submission
2) Application Developer: Which has got the functions like 1) Forms 2) Menus 3)
Messages 4) Flexi fields 5) Profiles
PO
AR
HRMS
AP
INV
GL
APPS
8
After installation of Oracle Applications the default User details as follows
User Name : OPERATIONS
Password : WELCOME
And connect and select System Administrator responsibility
To create New User
Security – User – Define – Enter
To move in the form some shortcut keys
1) If you want to query all the record – Ctrl + F11
2) If you want to query few records for form
a. Open the form
b. Press F11 – Query mode
c. Search criteria by using %
d. Press Ctrl + F11
3) To close the form F4
To Connect to database or Bank End
User ID : APPS
Password : APPS
Host string: PRODUCTIONS
Creation of New User – Front End
1) Connect to the application
2) Enter application User Name : OPERATION and Password: WELCOME
3) Select the Responsibility called “System Administrator
4) Open the user Form - Security – User- Define and enter
5) Enter Username and password and attach Responsibility
6) Save the Transaction.
7) Once user is created we can’t delete
WE can find all the user details in FND_USER table
Select * from FND_USER where user_name = “20user”
Note: We can find the table names from front end by using help menu
Help – Record History option
If open a form we can three types of fields
9
Yellow Color – Mandatory Fields, White Color – Optional Fields, Gray Color – Read Only
Effective Dates : From – To
Some of the records we can’t delete in Oracle applications instead of the we will
effective dates From and To to enable or disable the record.
Who columns in Oracle Applications
Ever Database table contains following Colums.
CRETED_BY User Id
CREATION_DATE System date When we create
LAST_UPDATED_BY User ID
LAST_UPDATED_DATE System Date When we Updated
LAST_LOGIN_DATE System date Login system date
When we go to Record History option we can find all the who columns
Help – Record History
Executables
.rdf Reports
.sh Unix Shell Script
.sql SQL
.proc, .fun, .pck PL/SQL
.C C Programe
.proC ProC
.ctl Sql * loader
.prl Perl
10
Concurrent Programe
• Is nothing but instances of execution file along with parameter and incompatible
programs
• We can register 11 types of executable as Concurrent Programe
• Advantages
o We can execute multi language programmes as Concurrent Programe
o When we run Concurrent Programe we can utilize 100% local machine
hardware capacity
o We can change the Concurrent Programe output based on employee
profile
o We can submit only Request
o We can schedule the Concurrent Programe as per client requirement.
Report Registration
1) Develop the report (.rdf) as per client requirement by using Reports 6i Builder
2) Move the report from the local machine in to the server
a. CUS_TOP\11.5.0\reports\US .rdf (or)
b. PO_TOP\11.5.0\reports\US .rdf
3) Select System Administrator
a. Create Executable
b. Executable name
c. Application Name
d. Execution Method
e. Report (.rdf) file name
4) Create Concurrent Programe and attach
a. Executable
b. Parameters
c. Incompatibilities
5) Create Request group and attach Concurrent Programe
6) Create Responsibility
a. Request Group – Concurrent Programe
b. Data Group – Collection of User IDs
c. Menu – Collection of forms
7) Create user attach Responsibility to the user
8) User will select the responsibility and go to SRS (Standard Request Submission)
window submit the request
Executable => Concurrent Programme => Request Group => User=> SRS
Note: For Single Executable we can have different concurrent programs with different
parameters
To create simple report with below SQL statement
Select User_ID, User_name, Trunc(creation_date) from fnd _user
11
Executable:
After moving .rdf in to the server, we will crate executable by specifying execution
name execution method and application name.
Concurrent Programe:
After creation of executable we will create concurrent Programe by attaching
executable, if parameters are there we will attach parameter if incompatibility
programs are there we will add those programs , for single executable we create
multiple concurrent programs with different parameters.
Request Group:
After creation of concurrent program we must add the programme to the request
group. Request Group is nothing but collection of Concurrent Programs and
Reports.
Responsibility:
It is level of authority where we will combine Data Group, Request Group and
Menu. Request Group is optional menu and data group is mandatory.
Data Group:
Data Group is nothing but collection of applications names and Oracle User
Names based on this Username data will be retrieved from database.
Menu:
Menu is nothing but collection of functions (forms) and submenus.
Create a simple report using below query
Select * from PO_VENDORS
Vendor_ID, Vendor_Name, Creation_Date
Navigation
Executable Window - Concurrent=>Programme=>Executable
Concurrent Programem Window - Concurrent=>Programme=>Define
Request Group – Security=>Responsibility=>Request
Responsibility – Security=>Responsibility=>Define
User – Security=>User=>Define
SRS Window – View Menu => Requests
To find out output file path and log file path we will write the below select
statement
Select logfile_name, outfile_name from fnd_concurrent_requests
Where Request_ID = ‘ ‘;
12
Reports with Parameters
Data Model
Select * from FND_USER
USER_ID, USER_NAME, CRATION_DATE
Parameters
From USER_ID, To USER_ID
Layout Model
Designing of layout
User Parameters
P_From
P_To
P_Title
Data Model – Change the SQL Query
Select * from FND_USER WHERE user_id between :P_From :P_To
Compile and save
To register the parameter with applications
Concurrent Programe window to be opened.
Note: We can change the sequence based on the requirement.
13
• If report is having the parameters then we have to register those parameters at
the time of creation Concurrent Programe.
• Value Set: values set is nothing but list values. It will be used to validate values
while entering the parameters.
• Token: Token is one the filed wile be used to map concurrent Programe
parameters with report builder find variable. We will enter the find variable name
in the toke field so that parameters will be passed to the find variable.
• Required check box: By using this check box we can’t make the parameters
mandatory or optional
• Enabled Check Box: By using this we can enable or disable the parameter.
• Display Check Box: By using this we can hide or display the parameters in SRS
window.
• Range Option: While defining the from and to parameters if we wanted to accept
values in accession order menus from values is low and to value is high we will
select the options called low and high.
• Default Types: If we are hiding the parameters user can’t enter the values that
time we can pass default values by using default type and default value filed.
• SRS Window – Copy Button: This will be used to find out recent Concurrent
Programe list with parameters in SRS Window.
Default Types
14
When we are hiding the parameter in SRS windows user can’t enter the values that time
we can pass values internally by using defaults types.
1) Constant: If we want to pass constant values as default then we will select
default type constant and we will specify the values in default value field.
2) Current Date: System Date
3) Current Time: System Time
4) Profile: By using the profile option we can pass user profile values as default
5) SQL Statement: When we want to pass select statement to rest as default
values that time we will select default types as SQL statement and write the
select statement in the default values filed. Select statement should not return
more then one value.
6) Segment: When we wanted to pass previous parameter values as default to the
next parameter then we will use segment, select default type as segment give
the parameter name in the default values field.
Report Builders
Data Model
Select User_ID, User_name, Trunc(creation_date) from fnd_user;
Layout Model
Object Navigator
User Parameters
1) P_From_Date - Date - Input Mask
2) P_To_Date – Date –Input Mask
3) P_Title – Character – 100
Data Model
Select User_ID, User_name, Trunc(creation_date) from fnd_user
Where trunc (creation_date) between: P_From_Date and: P_To_Date
Create a Summery Column
Count of UserIDs
Oracle Applications
To register the parameter
P_From_Date – Values Set – FND_DATE
Default Values.
P_From_Date – Current Date
P_To_Date – Current Date
15
P_Title – Constant
To hide parameter – To Uncheck the Display Check box in bottom of the form
To write SQL Statement Min and Max creation Date
Select MIN(TRUNC(CREATION_DATE)) FROM FND_USER
Select MAX(TRUNC(CREATION_DATE)) FROM FND_USER
Default values type – SQL Statement option
Default Value field – SQL Statement
Segment
Default Type as – Segment Option
Default Values – Specify the previous parameter
VLUES SETS - Application – Validation – Set
Value set is nothing but list of values with validation. It will restrict the user to enter valid
values. These are 8 types.
1) NONE : When we write maintain some format conditioning NO – LOV
Emp NO: => Only nos. 0 – 9
=> 35 to 7856
=> 45 to 0045
2) INDEPENDENT: Yes – LOV – user must select values from the list
3) DEPENDENT: Yes – LOV – The value which will depend upon the previous
parameter
Country Code: City Code:
i.e. based on the selection of country code lov city code lov will change
16
4) TABLE: Combination functionality of Independent and Dependent to provide
database table name and column name.
5) TRANSLATED: Independent – Multi language values
6) TRANSLATED DEPENDENT: Dependent – Multi language values
7) Special : To Display Flexi field data
8) Pair: To Display Flexi field data.
1) NONE -
a. When we want to restrict the user to enter the values based on some
conditions then we will use NONE type. Here no list of values user
manually will enter the value based on the conditions values will be
accepted.
b. Enter the values set name select format type enter the minimum size
select validation type as NONE.
c. Once the value set is created we can use it for any concurrent program
either single time or multiple times.
d. Once the value set is created we can’t delete if value if value set is being
used by concurrent program if we want to delete release from the
concurrent program then we can delete by using delete option.
2) INDEPENDENT –
a. Provide the values to the user either number or character creates the
independent value set to enter the values for the value set Application-
Validation-Values
b. If we want to provide list values to the user we will go for selecting
independent values set type user must select the values from the list.
c. Open the value set for an enter value set name select format type and
select validation type as Independent.
17
d. Copy the value set name go to values screen enter the values set name
and click Find enter the values in the values field save the transactions
attach value set to concurrent program.
e. Once the values are inserted in to the list we can’t delete instead of
deletion we can disable by using enable check box or effective date from
and to.
3) DEPENDENT
a. Dependent value set is nothing but another LOV but values will be
changing based on the previous Independent value set.
b. When we are creating dependent we must have 2 parameters
1)Independent 2) Dependent
c. Open the value set form create Independent value set go to values from
enter the values.
d. Open the values set form create dependent value set by selecting
validation type as dependent.
e. Select edit information button attach Independent value set what ever we
have created.
f. Copy the value set name go to values screen click on find button enter
the values based on the Independent value.
Hyderabad
Mumbai
Chennai
India
Bangalore
Newyork
Newjersy
US
California
UK London
Paris
4) TABLE –
Select user_name, user_id, creation_date
From FND-USER
Where row num < 20
Order by User_name desc
a. If values are available in database table then we will go for using table
value set, we will give the table name and column name system will
retrieve the values from the database table.
b. Open the value set form and give the value set name select validation
type is table click edit information button.
c. Enter the table name, column name in the value field.
d. Enter the where clause order by clause in the text item called where order
by.
e. Use the additional column field to display extra columns data in LOV, we
have to use alias name while specifying additional columns.
f. Table Application : It is a optional field based on the table name we can
find out table application name.
g. Go to Application Developer Responsibility –
Application=>Database=>Table query the records based on table name.
18
h. Select User_ID, USER_name from FND_USER – From front end user
name should be available and internally user_id to be passed.
i. Id Column in Value set form – If we want to display one column to the
user in the front end and pass another column value internally we will use
ID column.
j. Meaning Column – This will be used to display the extra columns like
additional columns it will work like a additional columns
k. Display the values from multiple tables –
i. At the time of giving the table name enter table names with alias
name by specifying. ( , )
ii. AT the time of selecting give the column name by giving the alias
name, column name.
iii. Join condition is mandatory in where / orderby clause
iv. PO_VENDORS, PO_VENDORS_SITE_ALL.
Select pv.vendor_name, pvs.vendor_site_code
From po_vendors pv, po_vendors_sites_all pvs
Where pv.vendor_id = pvs.vendor_id.
5) TRANSLATED INDEPENDENT & TRANSLATED DEPENDENT
a. These two value set will work like independent and dependent value set
but these two value sets will be used to display translation values (other
thane English language values) if application is installed for mlti language
there we will create these two value sets.
Select * from FND_LANGUAGES
Installed_Flag – I - Installed languages
B - Base language
D - Disabled language
6) SPECIAL & PAIR –
a. These two value sets will be used to display flexi filed data.
Develop the below report with parameters - CPLISTREP.RDF
From Date : P_From_Date
To Date : P_To_Date
Title : P_Title
CP Name CDate Application Name Executable Executable Method
• When the width of the report is more we have chose the style of the output in
Concurrent program window BACS instead of A4.
Query
Select fcp.user_concurrent_program_name,
Trunc(fcp.creation_date) CDate,
fav.application_name,
fev.executable_name,
fl.meaning
19
from fnd_concurrent_programs_vl fcp,
fnd_application_vl fav,
fnd_executables_form_v fev,
fnd_lookups fl
where fcp.application_id = fav.application_id
and fcp.EXECUTABLE_ID = fev.EXECUTABLE_ID
and fl.lookup_type ='CP_EXECUTION_METHOD_CODE'
and fcp.execution_method_code = fl.lookup_code&p_lexical
Parameters:
• Lexical parameter to change the query dynamically.
• Bind parameter to be register before Oracle applications.
If title parameter is null i.e. if user doesn’t pass the value to the title parameter
Format trigger.
Begin
If :P_Title is NULL then
Return (True)
Else
Return (False)
End if
End
If parameters were not passed to the :P_From_Date and :P_To_Date report should
display all the records.
After Parameter Form Trigger
Begin
If :P_From_Date is NULL and :P_To_Date is NULL then
:P_lexical:= ‘ ‘
Else
:P_lexical:= ‘and Trunc(fcp.creation_date)
between :P_From_Date and :P_To_Date
End if
End.
================
Exercise
From User ID, To User ID, From Date, To Date
Title
User_ID User_Name CDate Responsibility Application
Name
Security
Group
From
Date
To
Date
Query prepared by SIR in class
Master Level
20
SELECT USER_ID,
USER_NAME,
TRUNC(CREATION_DATE) CDATE
FROM FND_USER
WHERE USER_ID BETWEEN :P_FROM_ID AND :P_TO_DATE
Detail Level
SELECT FU.USER_ID,
FRV.RESPONSIBILITY_NAME,
FAV.APPLICATION_NAME,
FSG.SECURITY_GROUP_KEY,
TRUNC(FRG.START_DATE) FROMDATE,
TRUNC(FRG.END_DATE) TODATE
FROM FND_USER FU,
FND_USER_RESP_GROUPS FRG,
FND_RESPONSIBILITY_VL FRV,
FND_APPLICATION_VL FAV,
FND_SECURITY_GROUPS_VL FSG
WHERE FU.USER_ID = FRG.USER_ID
AND FRG.RESPONSIBILITY_ID = FRV.RESPONSIBILITY_ID
AND FRV.APPLICATION_ID = FAV.APPLICATION_ID
AND FRG.SECURITY_GROUP_ID = FSG.SECURITY_GROUP_ID
Steps to develop a report
• Data Model – Data link to be given between both the quarries.
• Adjust the margin and comeback to main page
• Report width to be increased - 13 – 135
• Select the main from and other frames – expand – variable
• Place all the text fields.
• Take the repeating frame and source to master query
o Put all the fields which are related to master query and what ever are
needed.
• Take another repeating frame and place that on the first repeating frame and
source to detail query.
o Put all the fields which are related to details query and what ever are
needed.
• Select all the fields and no fill and no line
• Define the User parameters P_From_ID and P_To_ID
• Applications
o Create the table value set for select the parameters
21
Standards to develop a report in Oracle Applications – 3 STANDARD STEPS.
1) Define the mandatory parameter called P_CONC_REQUEST_ID – This is one of
the bind variable when ever we submit a request in SRS window that request ID
will be passed to this bind variable, based on this request id we can execute
concurrent request process. Without this find variable we can’t use userexits.
2) Call the userexit in Before Report Trigger
• SRW.USEREXIT(FND SRWINIT)
3) Call the userexit in after report trigger
• SRW.USEREXIT(FND SRWEXIT)
Report Triggers
1) Before Parameter Form Trigger
2) After Parameter Form Trigger
3) Before Report Trigger – Before retrieving the data from database
4) Between Pages Trigger - when ever courser goes between pages at first time.
5) After Report Trigger – After out is reached the destination i.e. printer, file, email.
USER EXIT
It is one of the predefined program in Reports 6i will be used for stop the report
execution process for sometime and transfer the control to the 3rd Generation Language
get the data and completes the remain execution process.
Start End
Parameter
form
Query Layout
3GL
User Exit
Start End
Parameter
form
Query Layout
22
We have 5 types of User Exits available in Oracle Applications.
1) FND SRWINIT: We will use this user exit in the before report trigger. It will
initialize user profile values according to that profile values data will be retrieved
from database.
• SRW.USEREXIT(FND SRWINIT)
2) FND SRWEXIT: We will call this user exit from ater report rigger to freeze the
memory which is occupied by user profile values.
• SRW.USEREXIT (FND SRWEXIT)
3) FND FLEX SQL
4) FND FLEX IDVAL
5) FND FORMATCURRENCY
Purchase Order Application: Group of Forms, Reports and Programs
• Requisition
o Internal
o Purchase
• RFQ
o Standard
o Bid
o Catalog
• Quotation
o Standard
o Bid and Catalog
• Purchase Order
o Standard
o Planned
o Contract
o Blanket
• Receipts
o Direct Delivery
o Standard Receipt
o Inspection Required.
Requisition
Request for Quotation (RFQ)
Quotation
Purchase Order
Receipts
AP Interface INV Interface
Purchase
Documents
1
• Before going to start PO application we have to define following things
1. Items Creation
2. Suppliers creation
3. Buyer creation
1) Item Creation
a. Open the user from in System Administrator Responsibility attach the
responsibility called Inventory, Vision Operations(USA)
b. Open the Items from - Items – Master Item – enter, system will show the
organization list, select the organization vision operations, enter the item name
and description.
c. Go to Inventory tab check the check box called Inventory Item.
d. Go to purchasing tab check the check box called purchased and save the
transactions.
e. We can assign the item to the multiple organization by selecting tool menu –
Organization assignment option check the check box called assigned.
Select * from ORG_ORGANIZATION_ DEFINETIONS
- Organizatio_ID is primary key column
Select * from MTL_SYSTEM_ITEM_S
Where sgment1=’Keyboad’
- Inventory_item_ID -
- Organizatin_ID - Composite Primary Key
Select MSI.SEGMENT ITEM
OOD.ORGANIZATION_NAME
From MTL_SYSTEM_ITEM_B MSI
ORG_ORGANIZATION_DEFINETIONS OOD
Where MSI.ORGANIZATION_ID = OOD.ORGANIZATION_ID
And MSI.SEGMENT1 = ‘KEYBOARD’
2) Supplier Creation
a. Select the responsibility called Purchasing, Vision Operations(USA)
b. Open the suppliers form - Supply Base – Suppliers press enter
c. Enter the suppliers name and save the transaction and we will get supplier
number select status button enter site address select contact tab – enter contact
details and save
Select * from PO_VENDORS where SEGMENT1=’5080’
- VENDOR_ID – Primary Key
Select * from PO_VENDOR_SITESM_ALL where VENDOR_ID=’1010’
- VENDOR_SITE_ID – Primary Key
Select * from PO_VENDOR_CONTACTS
where VENDOR_SITE_ID= ‘2215’
- VENDOR_CONTACT_ID – Primary Key
2
3) Buyer Creation:
a. Attach the Responsibility called HRMS Management; Open the Employee form
(N) HRMS Manager – People – Enter and Maintain.
b. Select new button enter employee name and Date of Birth, select action as
creation employment chose the option called buyer and save the transaction.
Select * from PER_ALL_PEOPLE_F
Where EMPLOYEE_NUMBER = ‘893’
PERSON_ID is Primary Key column
c. Go to system administrator create user name attach employee name in the filed
called Person and save the transaction.
Select FU. USER_NAME,
PPF.FULL_NAME
From FND_USER FU,
PER_ALL_PEOPLE_F PPF
Where FU.USER_NAME = ‘20USER’
And FU.EMPLOYEE_ID = PPF.PERSON_ID
d. Copy the employee name go to purchasing application open the Buyers form
attach to the Buyers list - Setup – Personnel – Buyers and enter.
Select * from PO_AGENTS
This contains only Buyer list and primary key is AGEN_ID
PO PROCESS
1) Requisition: It is one of the purchasing document will be crated by employers when
ever they required goods or services or training, we will find 2 types of requisitions.
a. Internal Requisition: This will be created, when we receiving the materials from
other organizations(Branches)
b. Purchase Requisition: This will be created, when we are receiving the materials
from outside means suppliers.
c. We will enter the requisition information at 3 levels i.e. Headers, Lines,
Distributions.
d. One header will be there at least one line multiple line we create for every line at
least one distribution or multiple distributions will be there.
e. Open the requisitions form – Requisitions – Requisitions and enter.
f. Select the requisition type at header level enter item details at line level (item
Name, quantity, price and need by date) select bistributions buttion enter
distribution details like quantity and account details and save the transactions.
g. Select approve button press OK system will send the document for approval.
h. Go to requisition number select find button, it will show requisition approved
status.
Select * from PO_REQUISITON_HEADERS_ALL
3
WHERE SEGMENT1 = ‘1656’
Primary key - REQUISITION_HEAD_ID
Select * from PO_REQUISTION_LINES_ALL
WHERE REQUISITION_HEADER_ID = ‘11458’
Primary Key - REQUISITION_LINE_ID
Select * from PO_REG_DISTRIBUTIONS_ALL
WHERE REQUISITION_LINE_ID= 9922
Primary Key - DISTRIBUTION_ID
i. Cancel the Requisition: Open the requisition summery form enter requisition
number select find button go to tools menu select option called ‘Control’ and
chose the option as cancel requisition.
Select AUTHORIZATION_STATUS from PO_REQUISITION_HEADER_ALL
j. We can find out requisition history by using tools menu option called view action
history.
Select * from PO-ACTION_HISTORY
Where OBJECT_ID = REQUISITION_HEADER_ID
Select PAH.ACTION_CODE
PDF.FULL_NAME
From PO_ACTION_HISTORY PAH
PER_ALL_PEOPLE_F PPF
Where OBJECT_ID = 11459
And PAH.EMPLOYEE_ID = PPF.PERSON_ID
k. Enter manual requisition Number: Setup – Organization – Purchasing Options
and enter - go to numbering tab select requisition number entry as manual type.
2) RFQ – REQUEST FOR QUOTATION: It is one of the purchasing document after
requisition is approved. We will go for creation of RFQ we have 3 types of RFQs.
a. Bid : If company is going to purchase large number of items which are
expensive. We will create bid RFQ where we will specify Headers, lines and
shipments, where we are not specify any price breaks in Bid RFQ.
b. Catalog : If company is purchasing materials regularly fixed quantity location and
date, then we will select regularly we can include price breaks at different levels.
c. Standard : This will be created for items we need only once or not regularly, we
can include price breaks at different quality levels.
3) QUOTATIONS : After creation of RFQ document we will send this document to the
different suppliers who are going to supply the materials, next we will receive quotations
from the suppliers either by email or fax or by phone,
a. Again quotation are 3 types i.e. Bid, Catalog and Standard
b. What ever the quotation we are received from the suppliers we will enter those
quotation details in the system for future purpose.
c. If we are sending bid RFQ to the suppliers we will receive the bid quotation for
catalog RFQ we will receive catalog quotation for standard RFQ we will receive
standard quotation.
4
d. RFQ – Terms and conditions.
i. Payment Terms – At the time of creating RFQ document we will
specifying the payment terms like due date, interest rates and so on.
ii. Fright Terms: It is nothing by at transportation charges whether supplier
will bear it or buyer.
iii. FOB(Foot on Board) : It is responsibility of material damage or material
missing during the transportation.
iv. Carrier : We will give the transportation company name so that supplier
will supply the materials by this transportation company.
e. Navigation – Open the RFQ form – RFQs and Quotation – RFQs and enter -
select RFQ type from header level enter ship to location, Bill to location details –
select line level enter item details select price breaks button enter price break
details select terms button enter terms and conditions details, select suppliers
button and enter supplier name who are going to receive RFQ document
Select * from PO_HEADERS_ALL Where segment1=’306’
Primary Key- PO_HEADER_ID
Select * from PO_LINES_ALL Where PO_HEADERS_ID = ‘11845’
Primary Key – PO_LINE_ID
Select * from PO_LINE_LOCATIONS_ALL where PO_LINE_ID 12233
Primary Key –LINE_LOCATION_ID
Select * from FND_CURRIENCIES
Primary Key –CURRENCY_CODE
Select * from AP_TERMS
Primary Key – TERM_ID
Select * from AP_TERMS_LINES
Select * from HR_LOCATIONS
Primary Key – LOCATION_ID
f. AUTO CRATE OPTION : This is one of the feature application to create RFQ or
PO documents automatically based on the approved requisition document.
i. Create requisition and approve it
ii. Open the auto create form select clear button enter requisition number
select find button which will display requisition then details
iii. Select the line by checking the checkbox select action as create,
document types as RFQ
iv. Select automatic button click the create button whichwill create RFQ
document will selected requisition lines and displays RFQ No.
g. QUOTATION : - RFQs and Quotation – Quotation and enter
i. We will receive the quotations from suppliers against the RFQ either by
fax or email we will enter those quotation details manually in the system
to make the quote analysis and future purpose.
ii. Quotation tables: One we create the quotation from front end data will be
stored in the RFQs tables only by column type_lookup_code=’quotation’.
5
Select * from PO_HEADERS_ ALL
WHERE SEGMENT1 = ’500’
AND TYPE_LOOKUP_CODE =’QUOTATION’
4) Purchase Orders : It is one of the purchasing document at the time of purchasing from
the supplier we will create this document by specifying terms and conditions and
shipping details distribution details and so on. We have 4 types of Purchase Orders
a. Standard
b. Planned
c. Blanket
d. Contract
Purchase Order Types Summery
Standard Purchase Order
When we require the materials from suppliers we will cerate standard PO by specifying terms
and conditions price, quantity and so on.
Select * from PO_HEADERS_ALL
where segment1=’3445’ and type_lookup_id = ‘STANDARD’
Select * from Po_lines_all where PO_Header_id = 11858
Select * from PO_Line_locations_all where Po_line_id=12216
Primary Key – LINE_LOCATION_ID
Select * from po_distributions_all where line_location_id
Primary Key – PO_DISTRIBUTION_ID
• (N) – Open the purchase order form – Purchase Orders-Purchase Orders and enter
select PO type and suppliers information enter the line level details like Items quantity
price and so on.
• Select shipments button enter shipping location details quantity promice date, need by
date and so on select distribution button enter distribution details and save the
transactions.
6
• Select approve button system will submit the document for approvals.
• Copy the PO number and go to purchase or summary form enter PO number select find
button.
• To cancel the purchase order go the tools menu – control option.
• Tools menu copy document to create the same document.
• Auto Create
o By using Auto Create option we can create purchase orders automatically from
approved requisition by selecting document type as Purchase order.
SELECT PHA.SEGMENT1 PONUM,
PHA.TYPE_LOOKUP_CODE POTYPE,
TRUNC(PHA.CREATION_DATE) CDATE,
PV.VENDOR_NAME SUPPLIER,
PVS.VENDOR_SITE_CODE SUPPLIERSITE,
(PVC.FIRST_NAME
','
pvc.LAST_NAME) Contact,
HL1.LOCATION_CODE ShipTO,
HL2.LOCATION_CODE BillTo,
PHA.CURRENCY_CODE Currency,
PPF.FULL_NAME Buyer,
PHA.AUTHORIZATION_STATUS PoStatus,
SUM((PLA.QUANTITY*PLA.UNIT_PRICE)) LineLevelPrice,
PHA.COMMENTS PODesc
FROM PO_HEADERS_ALL PHA ,
PO_VENDORS PV ,
PO_VENDOR_SITES_ALL PVS,
PO_VENDOR_CONTACTS PVC,
HR_LOCATIONS HL1,
HR_LOCATIONS HL2,
PER_ALL_PEOPLE_F PPF,
PO_LINES_ALL PLA
WHERE PHA.SEGMENT1 = '3449'
AND PHA.VENDOR_ID = PV.VENDOR_ID
AND PHA.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID
AND PHA.VENDOR_CONTACT_ID = PVC.VENDOR_CONTACT_ID
AND PHA.SHIP_TO_LOCATION_ID = HL1.LOCATION_ID
AND PHA.BILL_TO_LOCATION_ID = HL2.LOCATION_ID
AND PHA.AGENT_ID = PPF.PERSON_ID
AND PHA.PO_HEADER_ID = PLA.PO_HEADER_ID
GROUP BY
PHA.SEGMENT1 ,
PHA.TYPE_LOOKUP_CODE ,
TRUNC(PHA.CREATION_DATE),
PV.VENDOR_NAME ,
PVS.VENDOR_SITE_CODE ,
(PVC.FIRST_NAME
','
PVC.LAST_NAME) ,
HL1.LOCATION_CODE ,
HL2.LOCATION_CODE ,
PHA.CURRENCY_CODE ,
PPF.FULL_NAME ,
PHA.AUTHORIZATION_STATUS ,
7
PHA.COMMENTS
Blanket Purchase Order
• When ever company would like to have the agreement with supplier that time first we will
create Blanket Agreement, when ever we require materials we will go for releasing the
purchase orders.
• Open the purchase order form select Blanket Purchase agreement and enter the details
and approve the purchase order.
• Select releases form either blanket PO Number and Item quantity details select
distributions button enter distribution details, select approve button for approvals.
Match Approval
2 way PO Qty – 100 Invoice Qty 80
3 way PO Qty – 100 Receipt Qty 80 Invoice Qty 80
4 way PO Qty – 100 Receipt Qty 80 Invoice Qty 50 Invoice Qty 50
• At the time of creation Purchase Order in the shipments, release we will specify the
Match approval option.
• Two way matching is nothing but company purchase order quantity, price with Invoice
price.
• Three way matching is nothing but comparing 3 documents Purchase Order, Receipt
and Invoice.
• Four ways Matching is nothing but company PO receipt inspection and Invoice
documents.
Receipt Documents
1. Standard - 3 Way
2. Direct Delivery - 2 way
3. Inspection Required - 4 way
• It is one of the purchasing document will be created while receiving the materials form
the suppliers we have 3 types of receipt.
• Navigation – Receiving – Receipts and enter.
• Create the purchase order and approve it open the receipts from enter PO Number,
select find button, system will show the PO Line details, check the check box save to
transactions, system write automatically generate Receipt number
Select * from RCV_SHIPMENT_HEADERS
SELECT * FROM RCV_SHIPPMENT_LINES
8
SELECT * FRO RCV_TRANSACTIONS
SELECT * FORM ORG_ORGANIZATIONS_DEFINITIONS.
• Create PO write 3 lines
o Go to receipt – generate the receipt for only one Item and either the PO Number
again – create another receipt until all the Items over.
Select Receipt_Num
From RCV_shippment_headers
Where shipment_header_id IN (Select shipment_header_id
From rcv_shpment_lines
Where PO_header_id IN( Select Po_Header_ID
From PO_heaer_all
Where segment1=’3452’))
----------------------------------------------------------
MD050 – Functional Design Document
Based on above develop Quotation Report
Query
MASTER QUERY
SELECT PHA.PO_HEADER_ID,
PHA.SEGMENT1 QUOTENO,
PHA.QUOTE_TYPE_LOOKUP_CODE,
TRUNC(PHA.CREATION_DATE) CDATE,
PPF.FULL_NAME BUYER,
H1.LOCATION_CODE SHIPTO,
PHA.QUOTE_VENDOR_QUOTE_NUMBER QUOTEAMT ,
H2.LOCATION_CODE BILLTO ,
PHA.CURRENCY_CODE CURR,
PV.VENDOR_NAME,
PVS.ADDRESS_LINE1,
PVS.CITY,
PVS.ZIP
FROM PO_HEADERS_ALL PHA ,
PER_ALL_PEOPLE_F PPF ,
HR_LOCATIONS H1 ,
HR_LOCATIONS H2,
PO_VENDOR_SITES_ALL PVS,
PO_VENDORS PV
WHERE TYPE_LOOKUP_CODE = 'QUOTATION'
AND PHA.VENDOR_ID = :P_VENDOR_ID
AND PHA.AGENT_ID = PPF.PERSON_ID
AND PHA.SHIP_TO_LOCATION_ID = H1.LOCATION_ID
9
AND PHA.BILL_TO_LOCATION_ID = H2.LOCATION_ID
AND PHA.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID
AND PHA.VENDOR_ID = PV.VENDOR_ID
LINE – QUERY
SELECT PLA.PO_HEADER_ID,
MSI.SEGMENT1 ITEM,
MSI.DESCRIPTION ITEMDESC,
MC.SEGMENT1
','
MC.SEGMENT2 CATEGORY,
PLA.UNIT_PRICE PRICE
FROM PO_LINES_ALL PLA,
MTL_SYSTEM_ITEMS_B MSI,
MTL_CATEGORIES MC
WHERE PLA.ITEM_ID = MSI.INVENTORY_ITEM_ID
AND PLA.ORG_ID = MSI.ORGANIZATION_ID
AND PLA.CATEGORY_ID = MC.CATEGORY_ID
Purchase Order Interview Questions.
1) What is the Flow of Purchasing Module
2) Where the types of Requisition and table
3) Types of Purchase orders and tables
4) What is the different between standard PO and Blanket PO
5) We have created a Item but which is not available in the pO what will be the problem
6) What are the types of Match approvals
7) What are the Receipt types
8) When we enter a Blanket Release data where it will be stored
9) If give the requisition no how to find out corresponding PO Numbers
10) If give the PO Number how to find out corresponding Receipt Numbers
11) What is Auto Create and Advantage
12) In which table we can find out
i) Shipped Quantity
ii) Received Quantity
iii) Canceled Quantity
13) Can we create Purchase order directly without
i) Requisition,
ii) RFQ
iii) Quotation
14) Can we have the Receipt without Purchase order
15) What are the tables for RFQ, Quotation, Purchase Order
PL / SQL Procedure registration with Oracle APPS.
Create or Replace
Retcode OUT Varchar2(20) , , , , )
Local Variable declaration
Cursor Declaration
Collection Declaration
10
Begin
Space for Control Statements like IF
LOOP s
Procedure, Function and Package Calling
DBMS_OUTPTU.PUT_LINE - -----�� we will not use this function.
FND_FILE.PUT_LINE(FND_FILE.LOG, ‘Write message’
Variable name)
FND_FILE.PUT_LINE(FND_FILE.OUT, ‘Write message’
Variable name)
Exception
When Others then
FND_FILE.PUT_LINE(FND_FILE.LOG, ‘Write message’
Variable name)
End
ErrorBuf: While registering the procedure in concurrent Programe we are suppose define
errorbuf out parameter, which will give the error messages in the log file if any occurs in the
procedure.
Retcode: It returns the status of concurrent program either 0-Normal, 1-Warnining, 2-Error
API – Application Programe Interface – FND_FILE: API is nothing but Application
Programming Interface, instead of DBMS_OUTPUT package we will use FND_FILE.LOG to
write in the log file, FND_FILE.OUTPUT to write in the output file.
NOTE: We can register procedure or package procedure as concurrent Programe to do some
DML validation and some other programming logic, we can’t register function as concurrent
Programe.
11
Registration Steps
• Develop the procedure and compile, specification of the procedure will be stored in the
Database.
• Select System Administrator Responsibility - Crate executable with execution method
as ‘PL/SQL Stored Procedure”
• Create concurrent Programe and attach – Executable, Parameters, and
Incompatibilities.
• Create the Request Group and the Concurrent Programe
• Attach the Request Group to the Responsibility
• Attach Responsibility to the User
• User submit Request from SRS window
Simple Example
Create or Replace Procedure EX20( Errorbuf out Varchar2,
Retcode out Varchar2) as
L_no number(8) default 1000,
L_Name Varchar2(100) Default ‘Mr. Rajan Bhatnagar,
Begin
FND_FILE.PUT_LINE(FND_FILE.LOG, ‘Write message’
L_no)
FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ‘Write message’
L-Name)
Exception
When Other then
FND_FILE.PUT_LINE(FND_FILE.LOG, ‘Error has occurred during the execution’)
End EX20
Procedure with parameters
Develop a Procedure which will take two parameters
1. Vendor ID
2. New Vendor Name
System should find weather vendor ID is exist or not if it exists it should update the Vendor
Name.
Create or Replace Procedure VEN_UPDATE20( Errorbuf OUT Varchar2,
Retcode OUT Varchar2,
V_ID IN Number,
V_Name IN Varchar2) as
L_Flag Varchar2(10) Default ’Y’;
L_Name PO_VENDORS.VENDOR_NAME%TYPE;
Begin
Select Vendor_name INTO L_name from PO_vendors where vendor_ID=V_ID;
Exception
When NO_DATA_FOUND then
FND_FILE.PUT_LINE(FND_FILE.LOG, ‘There no records in the Table’)
12
L_Flag:=’E’;
When TOO_MENY_ROWS then
FND_FILE.PUT_LINE(FND_FILE.LOG, ‘There multiple records in the table’)
L_Flag:=’E’;
End;
If L_flag:= ‘Y’ then
Update PO_VENDORS SET VENDOR_NAME=V_NAME
Where Vendor_id=V_ID;
Commit
FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ‘vendor name changed ‘)
Endif;
Exception
WHEN OTHERS then
FND_FILE.PUT_LINE(FND_FILE.LOG, ‘Error occurred while updating’)
End VEN_UPDATE20;
• While registering the procedure, if we have user defined parameter we are suppose to
define those parameter at the time of registering concurrent program, select parameter
button enter sequence number parameter name attach value sets if required attach
default types.
• Token filed will be disabled for procedures, it is enabled for only reports because report
builder find variable may or may not be in the sequence order but procedures variable or
permits will be always in the same sequence. First parameter values will be passed to
the first variable second parameter values will be second variable and so on
Exercise
Develop a procedure to transfer the following details in to the Temporary Table.
Create Table Quotation_temp( Total Quotations Number(8),
Total amount Number(8));
Create Table Quotation_Details ( Qno Number(8),
Qtype Varchar2(10),
Cdate Date
Buyer Varchar2(100),
BillTo Varchar2(100),
ShippTo Varchar2(100),
Currency Number);
• Program will accept one parameter supplier name LOV Vendor Name Internally
Vendor_ID should pass
• Based on the Vendor_id find the Quotation Details and load in the temp tables;
NOTE:
13
1. Crate Two table
2. Define the cursor based on Vendor_ID
3. Transfer the data in to quotation temp and quotation details
4. Define local variable to count to calculation and transfer to quotation_temp
SQL * LOADER - Oracle Tool
It is one of the Oracles tool will be used to upload the data from flat files in to oracle database
tables.
1. Flat file or Data file: It contains the data in specific format it will be either text file (.txt)
or excel sheet. (.txt, .dat, .csv – Comma Separated Value)
2. Control File: It contains SQL * Loader program contains data file path, database table
name and column mapping and soon, once we develop the control file we will execute
this, that time data will be transfer from file to Oracle Database Table, during this data
transfer, the following files will be created. The extension of the control files is .ctl.
3. Badfile: It contains the rejected records which are rejected by SQL*Loader, because of
bad format or data type mismatch and so on. The extension of the bad file .bad.
4. Discard file: It contains the rejected records which are rejected by control file if we have
specified any condition in the control file if record is not satisfying the condition the
complete record will be inserted in the discard file. The extension of file is .dis.
5. Log file: It contains information about control file execution like, execution start time
and end time, successful records count, bad records count, if there are any errors in the
control file, those error messages will be available in segments, the extension of file .log.
The Syntax of the control file is as follows:
Load Data
Infile ‘Data file Path’
Insert into table
Fields terminated by ‘,’ or ’$’
( Column1,
Column2,
Column3, , , , , ,, );
And save as .ctl file
For execution Command for .ctl file.
SQLLDER username/password@hoststring
Control =
Class Exercise
• Connect to Scott Schema and create temporary table
14
Create table emp_details ( empno Number(8),
Ename Varchar2(100),
JDate Date,
Deptno Number(8),
Sale Number(8),
Comm Number(8),
Tax Number(8) );
Select * from emp_details
• Prepare data file and save in the local machine.
• Develop the control file like as follows and save it with extension .ctl
Load Data
Infile ‘C:\20\load\emp.txt’
Insert into table emp_details
Fields terminated by ‘,’
( Empno,
Ename,
JDate,
Deptno,
Sale,
Comm,
Tax );
• Go to Command Prompt select following path
o D:\Oracle\proddb\8.1.7\bin>
• Execute the control file with SQLLDR Command
SQLLDER SCOTT/TIGER@PROD
Control = C:\20\LOAD\EMP.CTL
• To find path to execute the SQLLDR command is HOST Command from SQL prompt
which will shows the path where we can execute control file
o Select * from v$parameter.
Control File
INSERT - Table should be empty – New records will be inserted.
APPEND – New records will be add for existing records
TRUNCATE – It will delete existing records and inserts new records from the file.
.csv file (Comma Separated Valu)
If data in excel sheet we can save that file as .csv file.
If any string contains commas we will enclosed this will in double quotation in the control file we
will specify the following statement syntax
15
Fields Terminated by ‘ , ‘ optionally enclosed by ‘ ” ’
If one of the field is missing for the database column we can generate sequences and also we
can use the some functions like to_char, NVL, Decode, Instr, Rtrim, Trim and so on
Training Nullcols – Inserts NULL columns if no data is there, we can not use any DDL, DML or
TCL command in control file.
LOAD DATA
Infile ‘C:\20\load\emp.txt.’
Truncate into Table emp_details
Fields Terminated by ‘ , ‘ optionally enclosed ‘ “ ‘
Trailing Nullcols
( Empno ‘emp_seq.nextval’,
Ename ‘upper(:ename)’,
Jdate Sysdate,
Deptno,
Sal,
Commn,
Tax )
Fixed format control file exampled
Sometimes data may found in fixed format - for example
Empno - 5, Ename – 10, Jdate – 11, Deptno – 2, Sal – 5, Comm – 3, Tax -3
If data is in the fixed format the control file will be developed in different format
LOAD DATA
Infile ‘C:\20\LOAD\FIXED.TXT
Truncate Into table emp_details
( Empno position(1:5),
Ename position(6:15),
Jdate position(16:26),
Deptno position(27:28),
Sal position(29:33),
Commn position(34:36),
Tax position(37:39))
In control file itself we can specify the data and at the time of insertion we specify the specify the
condition and we can also specify the discard file pat in control file it self - for example
LOAD DATA
Infile *
Discardfile ‘C:\emp_rejected.dis’
Truncate Into table emp_details
When deptno=’20’
( Empno position(1:5),
Ename position(6:15),
16
Jdate position(16:26),
Deptno position(27:28),
Sal position(29:33),
Commn position(34:36),
Tax position(37:39))
Begin Data
Fsfsd fgdfdsf
Fsfsfsf fdfdsfsd gsfsdfg gdgd
Along with the SQLLDR command we can also use the few commands like as bellow
LOAD – Will be used to load the first ‘N’ records
SQLLDER username/password@Hoststring Load=10
SKIP – TO skip the first ‘n’ records
SQLLDER username/password@Hoststring skip=10
ROWS – It will be used to issue the commit after insert ’N’records for every 10 records internally
auto commit will happen
SQLLDER username/password@Hoststring rows=10
INIT.ORA file contains the default values for the commit point
Data Uplaoding in to two tables
Create Table emp1 ( Empno Number(5),
Ename Varchar2(30),
Job Varchar2(30));
Create Table dept1( Deptno Number(2),
Dname Varchar2(30));
Positions of the data in datafile
Empno 5, Ename 10, Job1, Deptno2, dname 7
DECODE Statement to be used to Code the ‘A” for Analyst ‘M’ for Manager and Multiple data
files can also be called in single control file
Example control file
Load data
Infile ‘C:\20load\multiple.txt
Truncate Into table emp1
(Empno Position(1:5),
Ename Position(6:15),
Job Position(16:16) “Decode (:Job ,‘A’, ‘Analyst’,’M’,”Manager’)”)
Insert Into table dept1
17
When deptno:=’ ‘
( Deptno Position(17:18),
Dname Position(19:25))
Register Control file as Concurrent Program
1. Develop the control file and move the both data file and control file in the server
2. CUS_TOP\11.5.0\BIN\ .CTL
3. Create executable by seleting the execution method as SQL*Loader
4. create concurrent Program attach executable, Parameters
5. Attach the concurrent program to the Request Group
6. Attach the Request group to the Responsibility
7. Attach Responsibility to the user will submit the request form SRS window.
Example
Create the table in APPS Schema
Create table MTL_ITEMS( Item Varchar2(10),
ItemDesc Varchar2(100),
ItemCost Number(8),
ItemTax Number(8),
BasicCost Number(8))
Basic Cost to the calculated field in the Control file i.e. ItemCost – ItemTax
Development of Control file
Load Data
Infile *
Insert into Table MTL_ITEMS
Fields Terminated by ‘,’ Optionally Enclosed by ‘ “ ‘
Trailing NULLcols
( Item,
Item Desc,
ItemCost,
ItemTax,
BasicCost “(:Itemcost - :Itemtax)”)
Begin Data
Fdfdsf sgdfg fgdgdgds dgdgdgd gfgdgd hgdghdg
Fdfdsf sgdfg fgdgdgds dgdgdgd gfgdgd hgdghdg
• Move the .ctl file in the Inventory top i.e. BIN Directory
• And register the control file with applications.
• Go to System Administrator and register with the applications and concurrent program
and method should be SQL * Loader
• Data file pat can changed dynamically
o Infile ‘&1’
o We muse use the ‘1` degit
18
o We are allowed to submit only one parameter that should be data file path
SQL * Loader with Parameters
At the time of defining concurrent programe select parameter butiton define the parameter
specify the ‘&1’ in the control file so that what ever the values we have passed that will be
replaced in the control file.
Interview Questions
1. How to issue the commit in the control file
a. By suing ROWS Command
2. What are the types loading we have
a. Direct Method Load
b. Conventional method load (Default)
3. How to upload the data in to multiple table at with time
a. By using WHEN command
4. Can we have the data in Fixed format and how to upload
a. Yes, we can have fixed format that time we will use POSITION keyword
5. What is the difference between Bad file and Discard file
a. Both file contains rejected records – Bad file contains records which are rejected by
SQL*Loader and Discard file contains which are rejected by Control file.
6. What are the SQL functions we can use in the control file
a. TO_CAHR, TO_DATE, INSTER, SUBSTER, RTRIM, LTRIM, DECODE, NVL and so
on.
7. can we use user defined functions
a. NO We can’t user defined functions
8. If control file rejected more than 10 records error the I would like to stop the process how to
do it
a. We have a file called INIT.ORA file where we will set parent called maximum errors
allowed and max auto commit other parameter also.
9. How to skip specific field data
a. By using FILLER command – In this position column will not accept the data and it
inserts the NULL values.
For example
( Empno,
Ename ,
JDate FILLER,
Deptno);
SQL * Plus
19
1. Develop the SQL Program (.sql)
2. Move the file form local machine to server in to respective path
a. CUST_TOP\11.5.0\SQL\ .sql
3. Create executable execution method as “SQL*PLUS”
4. Crate concurrent program and attach executable and parameters
5. Attach concurrent program to the request group
6. Attach request group to the responsibility
7. Attach responsibility to the user.
8. Submit request from SRS Window
SQL * Plus Program
Column User_id format 9999999999
Column User_name Format A25
Column Cration_Date Format A11
Column &3 Format A50
Prompt SQL Report with User Details
Select User_ID, User_Name, Creation_Date, &3 from fnd_user
Where User_id between &1 and &2
Save as the file .sql and create the parameters in Oracle apps systems administrator and
column can be added dynamically
NOTE
• We can define Maximum 100 parameter
• We are suppose to use &1, &2, &3 ……………
• We are not suppose to skip the sequence
• WE can use same parameter values in multiple places in the script
• SQL * PLUS concurrent program to execute DDL and Connect to remote database and
executes the scripts and so on.
Submission of Concurrent Program from Back End because, sometimes we don’t have
the access to the front end.
Executable
Concurrent Program
Request Group
SRS
We need to use the Application Programe Interface details in 115devg.pdf 21 chapter
FND_PROGRAM.EXECUTABLE - Executable
FND_PROGRAM.REQUEST - Concurrent Program
FND_PROGRAM.PARAMETER - Attach Parameter
FND_PROGRAM.REQUEST_GROUP - Create Request Group
FND_PROGRAM.ADD_TO_GROUP - Add Concurrent Program R.G
Submission of CP from Back End
20
SQL
PL/SQL
Report – Report Triggers
Forms – Form Trigger
FND_REQUEST.SUBMIT_REQUEST( ) - It will be used to submit the Request from Back end.
Sample Program
Declare
l_request_id number(10);
Begin
Fnd_global.apps_initialize ( user_id,
Resp_id
Resp_appl_id);
l_request_id:= Fnd_request.submit_request ( ‘PO’,
‘Users Information Script’,
‘User Desc’,
‘ ‘,
‘FALSE’,
1000,
1200,
‘Email_address’);
Commit;
If l_request_id is != 0
Fnd_file.Put_line (Fnd_file.log ‘Program successfully submitted Request ID is’
l_request_id);
Else
Fnd_file.Put_line (Fnd_file.log ‘Program not successfully submitted’);
Endif;
Exception
When other then
Fnd_file.Put_line (Fnd_file.log ‘Error occurred during the program submission’);
End;
NOTE: If we are submitting concurrent program from the Form Trigger we are suppose to pass
the 100 parameter. If parameter are not defined, still we are suppose to pass NUL values
Fnd_user
Fnd_responsibility_vl - responsibility_key
Fnd_application_vl - application_name
Scheduling the Concurrent program
We can submit the Concurrent program future date or date by using the schedule button in SRS
window
21
1. As soon as possible: This is default option whenever we submit the request it will
submit the as soon as possible
2. Once: It will submit the rest only once for future date.
3. Periodically: WE can specify the from_date and to_date to submit program periodically
no of. Days months, hours, minutes and so on.
4. Specific Days: If we want submit concurrent program in the specific days we write
select this option
5. Save this Schedule: This check box will be used to save the schedule and apply same
schedule to other concurrent programs by selecting the button called ‘Apply save
schedule’
NOTE: After schedule the Concurrent program we can also cancel by selecting the cancel
button.
PROFILES
Profile is one the changeable option it will change the way of application execution, when we
crate user in oracle applications we will design the profile values for ever user by selecting any
of this level.
Profile levels
User �� Responsibility �� Application �� Site
1. Site Level: Site is the lowest profile level when we install Oracle Applications by default
values will be assigned at this level, site level values will be applicable for all the users.
2. Application Level: If we want to assign profile value to the specific application users
then we will select this level specify the application name and assign the value,
application level profile value will over write site level values.
3. Responsibility Level: We will select the responsibility name assign the profile value,
this is applicable only for the users, who are having access to the specified
responsibility. Responsibility will overwrite the both application and site level values.
Most of the profile will setup at responsibility level because it will be creating
responsibility for ever position in the organization we will also assign the profile values
based on position.
4. User Level: We will select the user name and assign the profit value this is only for
specific user which we have specified at will over write responsibility, application and site
level values.
Definition
Application: It is collection of forms, reports and programs which are related for specific
business functionality
22
Responsibility: It is also collection of forms, reports and programs which are related for specific
position in the organization.
Navigation
Application Developer – Profile - press enter
• We will find profile name and validation access levels, weather we can visible the values
or we can update the values.
• Based on the access level we will go to system administrator open the profile form
assign the profile values.
Navigation
System Administrator – Profile – System – Press Enter
• Select profile name and profile level by default site select find button, it will shows profile
values at 4 levels
Important Profiles:
1. GL Set of Books Name: It is one of the GL application profile, set of books is nothing
but collection of currency, calendar, and chart of accounts. We will assign this profile
values at responsibility level based on this currency and calendar and accounts will get
change.
2. MO: Operating Unit: This will be used to setup user operating unit name which
operating unit user is working, when ever user login to the system by using this profile.
System will understand the user operating unit (Branch Name)
3. HR: Business Group: This profile will give the user business group which group user is
working.
4. HR: User_Type: This profile will give the user employment type which user is
Permanent, Contract or consultant will be identified.
USER_ID
USER_NAME
RESP_NAME
RESP_ID
RESP_APPL_ID
All these are user personal profile values for user will be there whenever we create it contains
the values. We cannot change the values.
GL: St of Books
Currency
23
Calendar
Chart of Account
Select Currency_code Currency
Period_set_name Calendar
Chatof_account_id Accounts
Name SOBName
From gl_set_of_books
18user GL:Set of Books Name Vision Operations(USA) USD
19User GL:Set of Books Name Vision Swedan SEK
20User GL:Set of Books Name Vison Itely ITL
Go to Systems Administrator and open the profile form and select the profile name and assign
the values.
• When we assign set of books to the user based on the that set of book name currency,
calendar and accounts structure will be changed
• Create there different users go to systems administrator assign the profile values, select
user level enter user name and profile name select find futon and assign the user level
values.
• Login with the user name select GL responsibility open the Journals form, Journals –
Entry and press enter we will find the different currencies in currucy filed
Personal Profile – To find
(N) Help – Diagnosis – Examine and press enter
Select block as profile we will find profile names in the filed Profile values in the values field
Technical Consultant Job
Get - Procedure
Value – Function
2 APIs to be used
FND_PROFILE.GET(Profilename, Local Variable);
Local Variable:= FND_PROFILE.VALUE(PROFILE Name);
APIs will be used to retrieve the profile values form Backend like SQL, PL/SQL, Forms, and
Reports.
NOTE: Here get() is a procedure and Values() is a function both will be used to get a profile
value from the backend
Example: In Select clause we can’t use procedure that time we can use function.
Simple PO Report
24
User Name :
Responsibility Name :
Set Of Books Name :
Supplier Name
Supplier ID
PO Type Date Currency
Query
Select PHA.SEGMENT1 PONUMBER,
PHA.TYPE_LOOKUP_CODE TYPE,
TRUNC(PHA.CREATION_DATE) CDATE,
PHA.CURRENCY_CODE CURRENCY
From PO_HEADERS_ALL PHA
Where VUNDER_ID=&PO_VENDOR_ID
Report Development Process
Lexical Parameters
P_CONC_REQUEST_ID
P_USER_ID
P_RESP_ID
P_SOB_NAME
SRW.USEREXIT(SRWINIT) - Before Report Trigger
SRW.USEREXIT(SRWEXIT) - After Report Trigger
And adjust the margin and design the layout
Before Report Trigger
:P_RESP_ID := FND_PROFILE.Value(‘Resp_name’)
User Name – Username
Resp_name – Responsibility name i.e. personnel profiles
GL_SET_BOOKS_ID
MO: Operating Unit – ORG_ID
Access the Profile values from the reports
Define find variable to store the profile values, go to before report trigger call the following APIs,
then use the find variable either in the layout or in the report builder
FND_PROFILE.GET(‘USER_NAME’, :P_USER_ID)
FND_PROFILE.GET(‘RESP_NAME’, :P_RESP_ID)
:P_SOBNAME := FND_PROFILE.VALUE(‘GL:SET_OF_BOOKS_NAME’)
PL/SQL – Procedure as Concurrent Program
Temp Table – Perfomr_taart
User_name Varchar2(50)
Resp_name Varchar2(50)
SOBName Varchar2(50)
ORGName Varchar2(50)
25
Develop the procedure and register as Concurrent program and user submit the request form
SRW window system should capture the dynamically and inserts in to the temp table
:X:= FND_PROFILE.VALUE(‘ORG_ID’)
Select name from HR_OPERATING_UNITS Where organization_id=:x
Chapter 15, 115devg.pdf
FAQs
1. What is profile
2. What are the profiles you have come across in your experience
3. Profile levels in sequential order
4. How to get profile value from Backend
5. How to pass profile values as default in parameter
Default Types
Constant
Segment
SQL
Profile
- Concurrent Programe window Parameter
• At the time of defining parameter select default type as profile and give the profile
name in the default values field.
26
Multi Org
Multi Org is nothing but implementing multiple organization under single oracle APPS
implementation. We will define the multi org levels like follows:
Business Group - Top level org where employee will be define
Set of Books - Currency, Calendar, Chart of accounts – Financial
Entities
Legal Entity - Where company is going to create Income Tax
Documents to Government
Operating Unit - Where Company will procure, sell, pay, receive, stock
Inventory Organization - Where company will store the product
Sub inventories
Stock Locations
Items
- Under the Inventory it will come
Business Group: It represents the highest level in the organization structure, it can be a major
division or operating company or consolidated enterprises, Business Group secure human
recourses information. Example: When we request list of employees assigned to the business
group where organization is a part
Set Of Books: It is a financial reporting entity it uses particular functional currency, chart of
accounts and accounting calendar, Oracle General Ledger application secures this transaction
information like, journal entries, balances, we will use the Set of Books to capture these financial
information.
Legal Entity: it is a legal company for which we prepare physical or tax reports and we will
submit company flanking sheet to Government.
Operating Unit: It is an organization where company is going to do the main business
operations like sales, purchasing payments, receiving and son, it may be a sales office or
27
division or department, operating unit information will be secured by following applications like
PO, AP, AR, OM, CM
Inventory Organization: It is an organization for which we will track Inventory transactions and
balances, Inventory Organization means warehouse, distribution center, manufacturing plants
and sales offices, the following application secures information by Inventory organization
- Oracle Inventory
- Bills of Material
- Work in process
- Enginering
- Capacity
- MRP and so on
Again we will define Inventory Organization as 2 levels
1) sub Inventory
2) Stock location – inside of this location we will keep the items.
Advantages or Major functions
• Data Secure: We can assign users to the particular organization when ever user
logon to the application then we can get the data access, which is related to their
organization
• Multiple Organizations Reporting: We can setup Oracle applications
implementation to allow the reporting across operating units we can allow the
reports either at SOB level or legal entities level or operating unit level
• We can sell the products from one legal entity and ship the products from
another level entity – that time we can easily create financial transactions.
• We can receive the good from the suppliers in any Inventory organization.
Example: A branch employee will create the PO then we can rece4ive the
material in B branch Inventory organization but these two organization should be
defined under same set of books.
• When employee creates internal requisitions in one organization thesewe can
ship the materials from another organization with corrent inter company
inventory.
115multiorg.pdf - Implementing Multiple Organizations Chapter1 and 4 - multiorg.xls.
28
Multi Org Tables Column Text
PO_HEADER_ALL ORG_ID Select * from po_header_all
where org_id=client_info
PO_LINES_ALL ORG_ID Select * from po_header_all
where org_id=client_info
AP_INVOICES_ALL ORG_ID Select * from ap_invoices_all
where org_id=client_info
AP_CHECKS_ALL ORG_ID Select * from ap_checks_all
where org_id=client_info
OE_ORDER_HEADERS_ALL ORG_ID Select * from oe_order_header_all
where org_id=client_info
• Multi Org table contains multiples organization data. This table names are goint to end
with _all and this table contains common column called org_id
• Whenever user login to the systems by using MO:OPERATING UNIT profile, systems
will understand user operating unit code (ORG_ID)
• Multi Org views – These are created based on, multi org table having the where clause
like “where org_id=client_info”. If we are using multi org views in the RICE components,
we will get the data from the database which is related for the user organization. Ex:
PO_HEADERS, PO_LINES, AP_INVOICES, AP_CHECKS, OE_ORDER_HEADERS
• CLIENT_INFO: It is one of the RDBMS variable, it contains user operating unit code
(org_id) whenever user login to the application
o Select * from hr_operating_units (you can get all the branch codes)
• To create Operating units navigation
o HRMS Responsibility – Work Structures - Organization – Description
• In before report trigger we will write SRW.USER_EXIT(‘FND SRWINIT’) – it will initialize
the user profile values (MO:OPERATING UNIT)
Create 3 users, assign to them different branches by using MO:Operating Unit profile and
develop the report, use the multi org tables and register it as concurrent program and submit
from SRS window form 3 users we will get all the operating units data in the output instead of
multi table, use multi org view. Submit request from SRS. It will display the output which is
related for user organization. If we are not using the user exit (FND SRWINIT) system will not
initialize user profile values including MO operating Unit profile that time, client_info variable
values is null.
In SQL & PL/SQL – We need to use API
SQL> select count(*) from PO_HEADERS_ALL
2777 POs are created form multiple organizations
29
SQL> select count(*) from PO_HEADERS – output ‘0’ becoz system doesnot know the branch
working for which branch
SQL> Begin
FND_CLIENT_INFO.SET_ORG_CONTEXT(204)
END;
SQL> select count(*) from PO_HEADERS - Now it is showing the 1645 Pos fro branch 204
Begin
FND_CLIENT_INFO.SET_ORG_CONTEXT(FND_PROFILE.VALUE(‘ORG_ID’)
END
It will retrieve User ORG_ID value
Table shows details about the Multi Org structure table
Organization
Name Table Profile Column
Business Group HRFV_BUSINESS_GROUPS HR:Business Group ID BUSINESS_GROUP_ID
Set Of Books GL_SET_OF_BOOKS GL:Set of Books Name
Legal Entity HR_LEGAL_ENTITIES
Operating Unit HR_OPERATING_UNITS MO:Operating Unit ORG_ID
Inventory
Organization
ORG_ORGANIZATION_
DEFINITIONS MFG_ORGANIZATION_ID ORGANIZATION_ID
SubInventory
MTL_SECONDARY_
INVENTORIES
Stock Locations MTL_ITEM_LOCATIONSQ
Items MTL_SYSTEM_ITEMS
Requstions
RFQ
Quotations
PO
PO Receipts
• While developing or customizing the RICE component we will be using Multiorg views.
We are not supposed to use the Multi org tables.
• By suing the following select statement we can find whether multi org is implemented
successfully or not
o Select multi_org_flag, release name from fnd_product_groups
Multi_org_flag Releasename
Y 11.5.7
• Y – Implemeted successfully (Business Group, I.E., Set of Books, operating Unit,
Inventory organziaiton)
30
• BUSINESS_GROUP_ID: This at Business group level we can find them common
column in all the table, which are in HRMS tables like
o PER_ALL_PEOPLE_F
o PER_JOBS
o PER_GRADES
o PER_POSITIONS
• ORG_ID: It is at operating unit level it we will find this common column in all the table at
operating unit level like
o PO_REQUESITION_HEADERS_ALL
o PO_HEADERS_ALL
o PO_DISTRIBUTION_ALL
• NOTE: But PO receipts functionality will come at Inventory organization level. Because
we are crating receipt means we are receiving the goods from supplier. We will be
receiving the material at Inventories (stories, gowdans ) only. Example:
RCV_SHIPMENT_HEADERS table is not having the _ALL it is not having ORG_ID
column
• ORGANIZATION_ID: It is at Inventory Organization level we will find all the
manufacturing tables column this ORGANIZATION_ID column including PO Receipts
Tables
o MTL_SYSTEM_ITESM_B
o WIP_ENTITITES
o BOM_RESOURCES
o BOM_DEPARTMENT
PO_VENDORS is not having _ALL
PO_VENDORS_SITES_ALL is having _ALL
Supplier is global we can access from any organization
Supplier site is organization specific and we create site based on the user ORG_ID sites will
retrieved.
FAQs
1. What is application hierarchy or Multi org flow?
2. What is Business Group?
3. What is legal entity and operating unit
4. What is meant by multi org table
5. What are module will come at operating unit level
6. Will PO Receipt will come tat Operating Unit level or Inventory Organization level?
7. What is difference between OR_ID and ORGANIZATION_ID?
8. Why there is no _ALL for PO_VENDORS table?
9. How to implement multi org in reports?
10. How to populate ORG_ID from SQL prompt?
11. What is meant by Client_info?
12. When user login in to the system how the system will identify user operating unit?
31
Flex Fields
Flex Field is nothing but a normal field but it is a ore flexible in the application either to extend
the form functionality or to capture the key information
We have 2 types of flex fields in oracle applications
1. Descriptive Flex Fields , 2. Key Flex Fields
1. Descriptive Flex Fields: DFF will be used to capture extra information from the end
user without changing form code and without altering database table
a. We will use the attribute column while working with DFF
b. We will find all the DFF in Application Developer Responsibility
i. N – Flex Filed – Descriptive – Register
c. To find DFF in form – PO Requisition Form
d. Customization of DFF
i. Open the form in application (PO, AP…) we will find the fields which are
enclosed with [ ] it is nothing but a DFF we can enable this DFF by using
Application Developer Responsibility
ii. Find the DFF title from following navigation
1. Help Menu – Diagnostics – Examine and enter password as apps
and Block name $Descriptive Flex field$ we will find the list of
DFFs in the fields select Field:
PO_REQ_HDR_DESCRIPTIVE_F4 we will find the DFF in the
value fields Value: Requisition Header (Oracle Purchasing)
iii. Copy the value go to Application Developer
1. N – Flex field – Descriptive – Segments and enter and press F11
enter the title Ctrl + F11
iv. Un check the checkbox called Freeze Flex field definition select segments
button enter the column names select attribute column and value set and
save the transactions.
v. Check the check box called Freeze Flex Field Definition click on compile
button internally system will submit concurrent program (Flex field view
Generation) to compile flex field it will generate one flex field view to
capture the data.
vi. The view name will be database table name _DFV
vii. Go to the form where DFF is attached, place the cursor, it will open DFF
form enter the data save it go to backend query the records.
Select Attribute3 Buyertype,
Attribute9 Buyerlocaton,
Attribute13 Last Date
From PO_REQUISITION_HEADER_ALL
Where segment1=’15467’
viii. Create the value set in system administrator; attach the value set at the
time of creating DFF column name in the value set field.
ix. We can make the DFF column is optional or mandatory by selecting
required check box in the segments form.
32
x. Context Field: While creation of DFF we can define multiple structures
based on the user selection we can change the DFF structure. While
defining the context field we will enter context field values for every
context value there will be a structure here we can use the entire attribute
column from attribute 1 to 15.
1. NOTE: One we use the attributes in one structure we can use the
same attributes in another structure, because user can enter only
one structure details at a time.
2. NOTE: Once we enter the value in the context field it will be stored
in the Database column called Attribute Category
xi. Global Data Elements: While defining the context field we will enter the
context field values based on the value we can change the DFF structure
if we want to define any field globally we will select option called Global
Data elements, select se4gments enter the details.
1. Once we use the attribute column in Global Data elements it will
not be available for the context field values.
2. Key Flex Field : These will be used capture key information which is in the code
language for every code there will be a specific meaning in the application we will find all
the KFF s in the Application Developer Responsibility. Navigation – Flex field – Key –
Register and enter we can find the structure in the segments form Navigation Flex Field
– Key – Segments and press enter when we enter the data in KFF it will be stored in
segments columns we will find segments column around 30 based on the requirement.
Differences between DFF and KFF
Item Location
Item Quantity
Item Attributes
Sub Inventory
Stock Location
Available Qty
Reserved Qty
Purchasable Item
Inventory Item
Customer Item
Production Item
Item Details
Inventory
33
DFF KFF
1. DFF will be used to capture EXTRA
Information
1. KFF will be used to capture KEY
Information
2. Attribute columns will be used 2. Segment columns will be used
3. We can register MAX DFF 3. We have 29 built in KFF we are not
suppose to create new KFF
4. DFF will be accept what ever Data we
enter
4. KFF we can define rules to capture
valid data
5. WE have context field 5. WE have no Context field
6. DFF data will be stored in Attribute
Columns
6. DFF codes will be stored in segments
KFF description will be stored in
FND_FLEX_VALUES tables.
To find a table of DFF – go to the segment form of DFF
System
Last Query
Value - Quary will get
KFF_List.xls file contains all the important KFF to study
KFF name Application
Name Table Staructure Column
Accounting Flexfield GL GL_CODE_COMBINATIONS CHART_OF_ACCOUNTS_ID
Assets KFF FA FA_ASSET_KEYWORDS No
Category Flexfield FA FA_CATEGORIES_B No
Location Flexfield FA FA_LOCATIONS No
Sales Tax Location
Flexfield AR AR_LOCATION_COMBINATIONS LOCATION_STRUCTURE_I
Territory Flexfield AR RA_TERRITORIES No
Item Catalogs INV MTL_ITEM_CATALOG_GROUPS no
Item Categories INV MTL_CATEGORIES_B STRUCTURE_ID
Stock Locators INV MTL_ITEM_LOCATIONS ORGANIZATION_ID
System Items INV MTL_SYSTEM_ITEMS_B ORGANIZATION_ID
• To Capture the Key Information
• We can query the Information at any level
• Data entry to the user will be very easy
• System will accept valid accounting transactions
• GL_CODE_COMBINATION – All Codes
• GLFV_CHARTS_OF_ACCOUNTS – Structure Details
•
Accounting Key Flex Field:
• This is one of the built in KFF available in GL application to capture accounting
transactions.
34
• Accounting KFF is nothing but Chart of accounts in the set of books when ever we
assign the profile (GL: Set of Books Name) to the user based on the this profile system
will assign Accounting KFF to the User.
*******
Set of Books
o Currency
o Calendar
o Chat of Account
*******
Select Name,
Currency_code Currency
Period_set_name Calendar
Chat_of_account_id ChartOfA/C
From GL_SET_OF_BOOKS
*******
16USER GL:SET OF BOOKS Vision Operation(USA) USD 101(5)
17USER GL:SET OF BOOKS Vision Italy ITL 50173(4)
18USER GL:SET OF BOOKS Vision Belgium BEL 50714(6)
*******
Structure Column
• While defining the KFF we will find structure column field if it is null value then KFF has
got only one structure, if there is a structure column that will find multiple structure, open
the segments form query the records based on KFF title, we will find structure details
• For Accounting KFF structure column is CHART_OF_ACCOUNTS_ID
• And we will get all the structure details from this table – GL_CHARTS_OF_ACCOUNTS.
• All the accounting KFF transaction will be stored in the data based table called
GL_CODE_COMBINATIONS
Developing the Reports on KFF - Process
1) Call the user ext from before report trigger
SRW.USER_EXIT ( 'FND SRWINIT');
2) Call another User Exit from After Report Trigger
SRW.USER_EXIT ('FND SRWEXIT');
3) Define the following parameters
1) P_CONC_REQUEST_ID
2) P_FLEXDATA
3) P_STRUCT_NUM segment1
'\n'
segment2
'\n'
segment3
'\n'
.......
'\n'
segment30
4) Call another User exit from before report trigger
SRW.USER_EXIT ('FND FLEXSQL
CODE = "GL#"
NUM = ":P_STRUCT_NUM"
35
APPL_SHORT_NAME = "SQLGL"
OUTPUT = "P_FLEXDATA"
MODE = "SELECT"
DISPLAY = "ALL");
5) Define the Query like follows
SELECT
&P_FLEXDATA
FROM
KFF Table
WHERE KFF Structure Column = :P_STRUCT_NUM;
6) Define Formula Column and CALL another User Exit from formula column
SRW.USER_EXIT ('FND FLEXIDVAL
CODE = "GL#"
NUM = ":P_STRUCT_NUM"
APPL_SHORT_NAME = "SQLGL"
DATA = "Column Name where the data is available"
VALUE = "to get KFF Values we will give formula column Name"
MODE = "SELECT"
DISPLAY = "ALL");
Flex Fields Reports development
P_FLEXDATA: It is one of the lexical parameter having the default value it is a contribution of
all the segment columns, we will use this lexical parameter in the select statement to retrieve
the data from database
P_STRUCT_NUM: This is a bind variable will be used to capture KFF structure number
FND FLEX SQL: It is one of the User Exit we will define in the before report trigger to retrieve
KFF segments data
FND FLEXIDVAL: This is another user exit will called from formula column to display the KFF
data in the output
1) Call the user ext from before report trigger
SRW.USER_EXIT ( 'FND SRWINIT');
2) Call another User Exit from After Report Trigger
SRW.USER_EXIT ('FND SRWEXIT');
3) Define the following parameters
1) P_CONC_REQUEST_ID
2) P_FLEXDATA
3) P_STRUCT_NUM
4) Before Report Trigger
function BeforeReport return boolean is
L_NAME VARCHAR2(100);
begin
36
SRW.USER_EXIT( 'FND SRWINIT');
l_name :=fnd_profile.value('GL_SET_OF_BKS_NAME');
select chart_of_accounts_id
into :P_STRUCT_NUM
FROM gl_sets_of_books
where name = l_name;
SRW.REFERENCE(:P_STRUCT_NUM);
SRW.USER_EXIT('FND FLEXSQL
CODE = "GL#"
NUM = ":P_STRUCT_NUM"
APPL_SHORT_NAME = "SQLGL"
OUTPUT = ":P_FLEXDATA"
MODE = "SELECT"
DISPLAY = "ALL" ');
return (TRUE);
end;
5) Go to data model select query
SELECT &P_FLEXDATA C_FLEXDATA,
GCC.LAST_UPDATE_DATE
FROM GL_CODE_COMBINATIONS GCC
WHERE CHART_OF_ACCOUNTS_ID = :P_STRUCT_NUM;
6) Take the Formula column and place it in query and edit the PL/SQL editor
function CF_DATAFormula return Char is
begin
SRW.REFERENCE(:P_STRUCT_NUM);
SRW.REFERENCE(:C_FLEXDATA);
SRW.USER_EXIT('FND FLEXIDVAL
CODE = "GL#"
NUM = ":P_STRUCT_NUM"
APPL_SHORT_NAME = "SQLGL"
DATA = ":C_FLEXDATA"
VALUE = ":CF_DATA"
MODE = "SELECT"
DISPLAY = "ALL"
IDISPLAY = "ALL"');
RETURN(:CF_DATA);
end;
function CF_DESCFormula return Char is
begin
SRW.REFERENCE(:P_STRUCT_NUM);
SRW.REFERENCE(:C_FLEXDATA);
SRW.USER_EXIT('FND FLEXIDVAL
CODE = "GL#"
NUM = ":P_STRUCT_NUM"
APPL_SHORT_NAME = "SQLGL"
DATA = ":C_FLEXDATA"
37
DESCRIPTION = ":CF_DESC"
MODE = "SELECT"
DISPLAY = "ALL"
IDISPLAY = "ALL"');
RETURN(:CF_DESC);
end;
7) Go to layout model design the report and register with Oracle Applications and submit from
the user SRS form
SRW.REFERENCE( ) : It we are using bind variable in source column the user exit we will
refer to get the latest values for variable and source column
Changing the structure number dynamically:
• KFF structure number is nothing but chat of accounts ID, if we know the user set of
books name we can find out chart of Accounts ID.
• Set of books name is one of the user profile by using FND profile API we can get from
report triggers
• Go to before report trigger declare the local variable write the following API
o L_name varchar2 (100)
o L_name:=fnd_profile.value(‘GL_SET_OFBKS_NAME’);
• Write the following select statement to get the structure number based on the set of
books name
Select Chart_of_account_id
INTO :P-STRUCT_NUM
From GL_SET_OF_BOOKS
Where name=L_name;
Develop the report on PO Module and display the charge account in PO Distributions Form
Line Level Dist Level
ReqNO Type Cdate Lineno Item ItemDesc DistNo ChargeA/C
FAQ in Flex Fields
1. What is FLEX FIELD?
2. What is KFF
3. In you experience what are the KFF you have come across?
4. Have u created any KFF? NO
5. Have u created any DF? Yes
6. What is FND FLEX SQL and FND IDVAL?
7. What is SRW REFERENCE?
8. What are parameters we suppose to pass for FND FLEXIDVAL?
9. How to change structure number dynamically?
10. What are the KFF are there in Inventory module?
11. Is there any KFF in PO? NO
12. Where the KFF data will be stored? Segments
13. Where the DFF data will be stored? Attributes
14. What in Attribute_Category?
38
Forms Development Process
1)We Will download the TEMPLETE.fmb from AU_TOP\Resource Folder
Application Utilities
2)Open with Forms6i builder and delete the existing Block
Canvas
Window
3)Create New Block , Canvas , Window attach oracle apps Properties
4)Develop the from as per client requirement
Note:We will change the Property called First Navigaction Data Block as Datablock Name in the
Module Properties.
II)Customize folooiwng two triggers1) Pre-Form -New Window Name
2) WHEN-NEW-FORM-INSTANCE -New form Name
3) APP_Custom package -New Window Name
5)Save the form we will get (.fmb)
6)compile the form we will get(.fmx)
7)Move the .fmb into au_top\11.5.0\Forms\US\ .fmb
8)Move the .fmx into CuS_top\11.5.0\Forms\US\ .fmx
9)Goto Applicaton Developer and create Form
10)create Function attach form to the function
11)Create Menu attach function menu
Executable
Concurrent
Programe
Request Group
Responsibility
User
Form
Function
Menu
Data Group
39
12)Attach Menu to the responsibility , Responsibility will be attached to the user
so that user can open the from and enter/Query the Data.
Develop the User Form
First Form
User ID
User Name
Creation Date
Second Form
Supplier ID
Supplier Name
Supplier Creation Date
Template.fmb: It is a built in form available in AU_TOP resources folder, it has got 21 libraries
to support menu functionality standard toolbar functionalities and other oracle apps functions
like who columns, profiles, flex fields, multi org and so on.
Form Registration:
• After moving the .fmb and .fmx in to the server we will take the .fmx name select
application developer responsibility register the form Navigation Application Developer
Application – Form and enter
• Enter the .fmx name and application name where the .fmx is located enter user form
name
• Copy the user form name attach to the function.
Function:
• It is nothing but instance of form along with parameter navigation Applicatoin – Function
and enter
• Enter the function name and user function name go to properties tab select type as form,
go to form tab attach user form name what ever we have created
• Copy the user function name attach to menu
Menu:
• Group of submenus and functions, enter menu name sequence no, prompt, attach user
function name in the function field, copy the user menu name, go to responsibility form in
system administrator attach in the menu field
• Select the responsibility we will find the form in object navigator window.
Form Setup
First step
AU/11.5.0/FORM/US
Copy the files in to resource folder like Templete.fmb and appstand.fmb
Second Step
40
Start menu – Run – Regedit
Hkey_local_mechine -> software -> oracle
Forms60 _path
- - - - - - - - - - -- - - -- - - ; give the template.fmd file path
Table registration
Note: connect to custom top (wip) --wip/wip@prod
1) create table custom_vendors
2) grant all on custom_vendors to apps;
3) conn apps/apps@prod
4) create public synonym custom_vendors for po.custom_vendors;
5) exec ad_dd.register_table ('po','custom_vendors','t', 8, 10, 90);
Exec Ad_dd.register_table(
p_appl_short_name ,
p_tab_name ,
p_tab_type ,
p_next_extent ,
p_pct_free ,
p_pct_used )
6) exec ad_dd.register_column ('po','custom_vendors','vendor_id', 1,'number', 10,'n','y');
Exec ad_dd.register_column (
p_appl_short_name ,
p_tab_name ,
p_col_name ,
p_col_seq ,
p_col_type ,
p_col_width ,
p_nullable ,
p_translate ,
p_precision default null,
p_scale default null)
Note: register all columns
7)commit;
Value sets
Who columns
41
Flexfield (dff,kff)
Note :
If we are registering table by using AD_DD package we can utilize following functions
1) Who columns
2) Flex fields
3) Multi org
4) Profile, Table Value set creation and so on.
Create Table wip_item_details(
Item Varchar2(50),
ItemDesc Varchar2(100),
ItemDate Date,
ItemLoc Varchar2(50),
Item Category Varchar2(50),
ItemCost Number(9),
Created_By Number(9),
Creation_Date Date,
Last_Update_By Number(9),
Last_update_date Date,
Attribute_Category Varchar2(100),
Attribute1 Varchar2(100),
Attribute2 Varchar2(100),
Attribute3 Varchar2(100),
Attribute4 Varchar2(100),
Attribute5 Varchar2(100))
Grant all on WIP_ITEM_DETAILS to APPS;
Conn APPS/APPS@PROD;
Create Public Synonym WIP_ITEM_DETAILS for WIP.WIP_ITEM_DETAIS
Table type contains three values
T – Table, V – View, S – Synonym
Exec AD_DD.REGISTER_TABLE (‘WIP’,’WIP_ITEM_DETAILS’,’T’,0,10,90)
EXEC AD_DD.REGISTER_COLUMN
(‘WIP’,’WIP_ITEM_DETAILS’, ‘ITEM’,1,’VARCHAR2’,50,’N’,’Y’);
All the columns to be registered
To know the table status about registration
Navigation
Application - > Database - > Table and press enter
And query for the required table.
42
Implementing who columns
• Create two triggers Pre_Insert and Pre_updata at Block level
• Call the following API from the both the triggers.
o Fnd_standard.set_who();
• This API is available in the library called ‘FNDSQF.PLL’
Implementing the calendar to the date field
• Go to the field properties, change the property called list of values – Enable_List_Lamp
• Create the trigger called KeyListVal at Item level call the following API
o Calender.show()
• It is available in the library called ‘APPDAYPK.PLL’
Attaching the list to the field
• Record Group-> LOV -> field
• Create Record Group by enter list of value or by enter select statement
• Create LOV attach record group
• Attach LOV to field property called list of values
Reference: 115devg.pdf - 24th Chapter
Development of Master Details Form
Master Form
PO-VENDORS
Vendor_ID, Vendor_name, Creation_Date
Detail Form
PO_VENDOR_SITES_ALL
Vendor_ID, Vendor_site_code, Site_Creation_Date,
Address1, Address2, City.
• Develop the form with master table by using template.fmb (Primary Key must be
selected)
• Crate detailed data block after selecting the column click next futon, can check the check
box called Auto join data blocks, select the button called create relationship, select radio
button called based on join condition.
• Select Primary Key from Master Item, reference key from detail Item, system will
automatically create join condition and select finish button.
• At the time of selection layout style select tabular format and select number of records to
be displayed in the layout.
43
• Attach property classes and save the .Ffmb generate .FMX move in to the custom top.
Practice development
PO_HEADERS_ALL
PO_header_id, PO_NO, type, Cration_Date
POLINE_ALL
Po_heder_id, item_desc, quantity, Unit_price,
Line_total(quantity*unitprice)
Manual Development of Form
Crate a table in WIP
Item, Item_Desc, Item_date, Item_loc.
• Create new data block by select the manual option
• Change the following the properties
o Subclass: Block
o Database source Name: Table Name
o Database Column Name: Column Names
• Go to canvas create and change the property class to Frame_Rect
• Define the Text Item attach following properties
o Subclass: Tex_Item
o Column Name: DB column
• Register the column and compile and generate .FMX
Descriptive Flex Field Form Development Process
• Table should contain attribute column and also it should be registered
• Go to application developer open the DFF register form navigation Flex Field –
Descriptive – Register and enter
• Create New DFF by giving database table name.
• Copy the DFF table go to segments form query based on table, select segments button,
enter DFF structure details.
• Open the template.fmb select all the attribute column at the time of data block creation
• Define the field in the Canvas change the field property database items as "no"
• Define the Package like follows.
Package Specification:
----------------------
PACKAGE DFF_PKG IS
PROCEDURE DFF_PROC(EVENT VARCHAR2);
END;
Package Body:
-------------
44
PACKAGE BODY DFF_PKG IS
PROCEDURE DFF_PROC(EVENT VARCHAR2) AS
BEGIN
IF (EVENT = 'WHEN-NEW-FORM-INSTANCE' ) THEN
FND_DESCR_FLEX.DEFINE(
BLOCK => 'WIP_ITEMS10',
FIELD => 'DFF_TEXT',
APPL_SHORT_NAME => 'WIP',
DESC_FLEX_NAME => 'WIPDFF' );
END IF;
END DFF_PROC;
END;
• Call this Package from WHEN-NEW-FORM-INSTANCE Trigger
o Packagename.Procedure name('WHEN-NEW-FORM-INSTANCE');
o DFF_PKG.DFF_PROC('WHEN-NEW-FORM-INSTANCE');
• Call another API from WHEN-NEW-ITEM-ISNATCE Trigger
o fnd_flex.event('WHEN-NEW-ITEM-INSTANCE');
o It will Populate the DFF in the Form.
Note:
FNDSQF library will support for all the flex field APIS like
FND_DESC_FLEX.DEFINE
FND_FLEX.EVENT
FND_KEY_FLEX
Function Security:
• For single form creation of multiple functions at the time of creation function passing the
parameter which will change the form functionality.
• Develop a form register in application developer at the time of creation function pass the
parameter called QUERYONLY=”YES”
• Attach the functions to menu and menu to responsibility and responsibility to the user.
• NOTE: At the time of crating function select form tab pass the parameter in the field
called parameter
Calling another form from the existing form.
Form 6i feature – Call_Form, New_form, Open_form
APPS - FND_FUNCTION.EXECUTE( )
OPEN_FORM built-in
Opens the indicated form. Use OPEN_FORM to create multiple-form applications, that
is, applications that open more than one form at the same time.
45
NEW_FORM built-in
Exits the current form and enters the indicated form. The calling form is terminated as
the parent form. If the calling form had been called by a higher form, Form Builder
keeps the higher call active and treats it as a call to the new form. Form Builder
releases memory (such as database cursors) that the terminated form was using.
Form Builder runs the new form with the same Runform options as the parent form. If
the parent form was a called form, Form Builder runs the new form with the same
options as the parent form.
CALL_FORM built-in
Runs an indicated form while keeping the parent form active. Form Builder runs the
called form with the same Runform preferences as the parent form. When the called
form is exited Form Builder processing resumes in the calling form at the point from
which you initiated the call to CALL_FORM.
1) Develop both forms and register in application.
2) Place the Button in first form
3) Write the following code in WHEN-BUTTON-PRESSED Trigger.
Fnd_FunCtion.execute (
function_name => 'Second from function Name',
open_flag => 'Y' ,
OTHER_PARAMS => 'P1 = '
:PO_VENDORS.VENDOR_ID);
P1 is Second form parameter
4) Open the Second form and define the Parmaeter called P1
5) Go to PRE-QUERY trigger and write the following code to change the query dynamically.
:PO_VENDOR_SITES_ALL.VENDOR_ID := :PARAMETER.P1;
--:Blockname .fieldname := :PARAMETER.Parametername
6) Goto the WHEN-NEW-FORM-INSTANCE Trigger at from level. write the following code:
GO_BLOCK('PO_VENDOR_SITES_ALL');
EXECUTE_QUERY;
Sub Menus
Sub menu is nothing but is collection of function and menus we will create the
submenus just like menus, then we will attach to main menu by using the field called
submenu.
Calling the SRS window from Menu
• Go to the function form in application developer (N) Application - funcatoin
• Create new function for the user form called Run Reports, copy user function name
attach to menu.
46
Adding Multiple Request Groups to the Responsibility
• Create Request Group in System Administrator
• Copy Request Group name application short name and Request group code.
• Go to application developer create function for the form called “Run Report” and pass
the following parameters in the parameter field
o REQUEST_GORUP_CODE = “20SUPPLIER_CODE”
o REQUEST_GROUP_APPL_SHORT_NAME=”PO”
o TITLE = “20SUPPLIERS”
Hide the menus and function at responsibility level (Menu Exclusions)
• At the time of creating responsibility we will attach menu by default all the submenus and
functions will be available if we want to hid we will go to Menu Exclusion tab in
Responsibility form select type as function or menu and give the function name or menu
name in the name field.
Forms Customization
Two Ways
1) Download .fmb from au_top\11.5.0\Forms\resource\US
2) Custom.pll
a. When_new_form_instance
b. When-new-block-instance
c. When-new-record-instance
d. When-new-item-instance
e. When-Validate-record
f. Special
g. Zoom
If we want to customize standard oracle forms will going to use custom.pll by using this we can
customize few events in the form. This custom.pll is available in the au_top – resource folder
1) Hiding the fields
2) Chane the promplt
3) Change visual attributes
4) Make field is mandatory
5) Attaching the menu options.
Before going to customization the form should know following details
• Form Name :
• Block Name :
• Field Name :
• What Customization :
• When we would like to do
Data capture
47
• Form Name : Help About Oracle Applications
• Block Name : Help Diagnostics – examine
• Minimum : Amount
• Hide the filed
• When form is opened (When-new-form-instance)
Forms Customization Steps:
1) Download the CUSTOM.pll from AU_TOP\11.5.0\Resource folder.
2) Open with Forms 6i.
3) Write the Following Code in the place of 'Real Code Starts Here'
form_name varchar2(30) := name_in('system.current_form');
block_name varchar2(30) := name_in('system.cursor_block');
begin
if (event_name = 'WHEN-NEW-FORM-INSTANCE') then
if (form_name = 'APXWCARD' and block_name = 'CARDS') then
APP_ITEM_PROPERTY2.SET_PROPERTY (
'CARDS.DEPARTMENT_NAME', -BlockName.FieldName
DISPLAYED,
PROPERTY_OFF);
APP_ITEM_PROPERTY2.SET_PROPERTY(
'PO_HEADERS.SEGMENT1',
PROMPT_TEXT,
'PO Number');
APP_ITEM_PROPERTY2.SET_PROPERTY(
'REGIONS.REGION_LONG_NAME',
CASE_RESTRICTION,
UPPERCASE);
end if;
end if; (attach the Library called APPCORE2.pll)
4) Compile the Library we can get .pll (Program link Library)
Execute the Library we will get .plx (Program Link Executable)
(File ->Administration->Compile File)
5) Transfer both .pll and .plx files into the Resource folder in the AU top.
(Note: before going to copy into the server close the complete Application)
Zoom Trigger
Zoom event will be used to call the another forms from Oracle standard forms, by default zoom
will be disabled for the form, first we have to enable, the \we will go for execution.
48
• Open the Custom.pll got the function call zoom available and creat the following code in
the place of “Real Code Starts here”
form_name varchar2(30) := name_in('system.current_form');
block_name varchar2(30) := name_in('system.cursor_block');
begin
if (form_name = 'POXRQERQ' and block_name = 'PO_REQ_HDR') then
return TRUE;
else
return FALSE;
end if;
• Go to the event procedure and write the following code in the place of “Real Code Starts
Here”
form_name varchar2(30) := name_in('system.current_form');
block_name varchar2(30) := name_in('system.cursor_block');
Begin
if (event_name = 'ZOOM') then
if (form_name = 'POXRQERQ' and block_name = 'PO_REQ_HDR') then
fnd_function.execute(function_name=>'PO_POXRQVRQ',
open_flag => 'Y');
end if;
end if;
• Ave the custom.pll and compile this execute the custom.pll
• Copy both .pll and .plx in to the resource folder (Close the applications before copying in
to the respective top)
Special Trigger: - Total 45 are available.
We will use the special trigger to attach the menu options to the forms.
FAQs:
1) What are the steps we will follow for the form development
2) What are the libraries are available in TEMPLETE.FMB?
3) How to register table and primary key?
4) Why do we need to register the table
5) How t o implement who columns?
6) How to attach a calendar?
7) How to call another form from the standard forms?
8) How to implement DFF in the forms?
9) What are the events we can use in custom.pll?
10) What is meant by zoom?
11) We can’t copy the CUSTOM.PLL in to the server when applications is opened?
a. In oracle applications all the forms are developed by TEMPLETE.FMB including
Navigator. So that, that time CUSTOM.PLL IS being used by the application we
can not update the library.
12) When we are working in the project we are not able to close all the users application
that time how to copy in to server.
49
a. We will be having the link file (crated by DBA), we will copy in to that path only
we have to logout and login then we can find the customization effects.
13) How to generate .fmx and .plx in LINIX operating system
a. In windows we will use CTRL + T
b. In linix we will use F60GEN command
F60GEN MODULE=TEST.FMB USERNAME/PASSWORD@HOST
F60GEN MODULE=CUSTOM.PLL TYPE=LIBRARY USERNAME/PASSWORD@HOST
14) In which top we will execute this F60GEN command
a. IN AU_TOP\11.5.0\FORMS\US
INTERFACES
Interface is nothing but a program will be used to transfer the data from flat files to database
table or from database table to flat files with validations. WE have two types of Interfaces like
Inbound Interface Outbound Interface.
Inbound Interface
It will be used to upload the data from legacy system into Oracle Applications base table
Note: Legacy system is nothing but other applications which is used by client like SAP, JAVA,
Mainframe so on.
Example: In implementation project we can implement forms and report so that users can enter
the data after implementation but the previous data will be available in the legacy system we
need to bring that data in to Oracle Applicatoins with validations.
Usage: SQL*Loader, .txt, .ctl, .dis, .bad, .log
Outbound Interface
It will be used to extract the data from Oracle Applications base tables in to flat files
Example: Client is using to applications one is for financial and another one is for
manufacturing, we have to transfer manufacture data in to financial applications, so that we can
generate the Invoices in financial applications.
Usage: UTL_FILE_PACKAGE
UTL_FILE.FOPEN() - Crate / Open file
UTL_FILE.PUT_LINE() - Transfer the data in to file
UTL_FILE.FCLOSE() - Close file
Exercise – OUTBOUND INTERFACE
We can generate flat file only in specified directory - to find directory list
Select * from V$PARAMETER Where NAME like ‘%UTL_%
Or
“INIT.ORA” file contains the information about active directory list.
50
Flat file Requirement
PO Number, Type, Creation date, Buyer Name,
PO Type – STANDARD Purchase orders only
Process Steps
1) Develop a procedure or package and register it as Concurrent Program
2) Writ the cursor to retrieve the data from database
3) Define the file by using UTL_FILE.FOPEN()
4) Open the cursor for loop and transfer the data in to file by using UTL_FILE.PUT_LINE()
5) Close the cursor and close the file by using UTL_FILE.FCLOSE()
Create or Replace PO_Information ( Errbuf OUT varchar2,
Retcode ouT varchar2,) as
Cursor c1 is Select pha.segment1 PoNum,
Pha.type_lookup_code POType,
Trunc( pha.creation_date) CDate,
Ppf.full_name Buyer
From po-headers_all PHA
Per_all_people_f PPF
Where PHA.type_lookup_code = ‘STANDARD’
AND PHA.agent_id = ppf.person_id;
l-id UTL_FILE.FILE_TYPE
Begin
l_id := UTL_FILE.FOPEN(‘d:\........................’, ‘PO_ORDERS.TXT’, ‘W’)
For c2 in c1 loop
Utl_file.Put_line (l_id, c2, PoNum
’$’
C2.POType
’$’
C2. CDate
’$’
C2.Buyer);
End loop;
UTL_FILE.FCLOSE(L_ID);
End PO_Informaton;
Inventory Outbound Interface
Requirement Fields - Item, itemid, itemdesc, uom, name, id , category
CREATE OR REPLACE procedure INV_Out( Errbuf OUT varchar2,
Retcode ouT varchar2,
f_id in number,
t_id in varchar2) as
cursor c1 is select
msi.segment1 item,
msi.inventory_item_id Itemid,
msi.description itemdesc,
51
msi.primary_uom_code Uom,
ood.organization_name name,
ood.organization_id id,
mc . segment1
','
mc.segment2 Category
from
mtl_system_items_b msi,
org_organization_definitions ood,
mtl_item_categories mic,
mtl_categories mc
where
msi.organization_id = ood.organization_id
and msi.inventory_item_id = mic.inventory_item_id
and msi.organization_id = mic.organization_id
and mic.category_id = mc.category_id
and msi.purchasing_item_flag = 'Y'
and msi.organization_id between f_id and t_id;
x_id utl_file.file_type;
l_count number(5) default 0;
begin
x_id:=utl_file.fopen('d:\oracle\proddb\8.1.7\plsql\temp','invoutdata.dat','W');
--select * from v$parameter where name like '%utl_file%'
for x1 in c1 loop
l_count:=l_count+1;
utl_file.put_line(x_id,x1.item
'-'
x1.itemid
'-'
x1.itemdesc
'-'
x1.uom
'-'
x1.name
'-'
x1.id
'-'
x1.category );
end loop;
utl_file.fclose(x_id);
Fnd_file.Put_line(Fnd_file.output,'No of Records transfered to the data file :'
l_count);
Fnd_File.Put_line(fnd_File.Output,' ');
Fnd_File.Put_line(fnd_File.Output,'Submitted User name '
Fnd_Profile.Value('USERNAME'));
Fnd_File.Put_line(fnd_File.Output,' ');
Fnd_File.Put_line(fnd_File.Output,'Submitted Responsibility name
'
Fnd_profile.value('RESP_NAME'));
Fnd_File.Put_line(fnd_File.Output,' ');
Fnd_File.Put_line(fnd_File.Output,'Submission Date :'
SYSDATE);
Exception
WHEN utl_file.invalid_operation THEN
fnd_file.put_line(fnd_File.log,'invalid operation');
utl_file.fclose_all;
WHEN utl_file.invalid_path THEN
fnd_file.put_line(fnd_File.log,'invalid path');
utl_file.fclose_all;
WHEN utl_file.invalid_mode THEN
fnd_file.put_line(fnd_File.log,'invalid mode');
utl_file.fclose_all;
WHEN utl_file.invalid_filehandle THEN
52
fnd_file.put_line(fnd_File.log,'invalid filehandle');
utl_file.fclose_all;
WHEN utl_file.read_error THEN
fnd_file.put_line(fnd_File.log,'read error');
utl_file.fclose_all;
WHEN utl_file.internal_error THEN
fnd_file.put_line(fnd_File.log,'internal error');
utl_file.fclose_all;
WHEN OTHERS THEN
fnd_file.put_line(fnd_File.log,'other error');
utl_file.fclose_all;
End INV_Out;
53
Inbound Interface
Process Steps:
1) Stage Table Cration
2) Control file development
3) PL/SQL Program
4) Submit Standard Program
• Inbound interface will be used to upload the data from legacy system in to Oracle
applications base tables.
• We will receive the flat file from the client then we will create staging table upload the
data from flat file in to stage table.
• Develop the PL/SQL program to validate the data weather it is valid or not, if it is
valid we will insert in to interface table, if it is not valid we will insert in to error table.
• Once the data is available in interface table submit the standard program from SRS
window, and we will transfer the data from interface table to base tables.
• Note
o Staging table is required because the flat file structure may or may not be
compatibles with base table structure.
o Interface table is required to make the pre validation and populate dynamic
records.
GL PO AP AR OM HRM
Flat File
Staging or Temp or Pre-Interface tables
Flat File Interface Table
Base Tables
.bad or .dis SQL* Loader
Error Message PL/SQL Block
Error Tables
Error Table Standard Program
Error Field
Error Report
54
• Journals
• Currency
Conversi
on
• Daily
Conversi
on Rates
• Requisitions
• RFQ
• Quotations
• Purchase
Orders
• Invoices
• Payments
• Customers
Invoices
Receipts
• Sales
Orde
rs
• Emp
GL Interface
CLASS – 42
GL_INTERFACE_TABLE
STATUS: This column will accept any string but we will always insert standards string called
new it indicates that we are brining new data in to General Ledge Applications.
SET_OF_BOOKS_ID: We have to enter the appropriate set of books ID, it should be valid set
of books id is available in GL_SETS_BOOKS table it is valid, otherwise i8t is invalid.
USER_JE_SOURCES_NAME: We have to enter the journal sources name for the transaction
we can find all the valid source names in the tabled called GL_JE_SOURCES.
USER_JE_CATEGORY_NAME: WE have to find out weather journal category is available in
the GL_JE_CATEGORIES table. It is is available then we will insert, otherwise we will reject.
CURRENCY_CODE: We have to enter the valid currency code in FND_CURREINCES table
we can find out weather it is valid or not.
Journal Flat File
XXXX Staging Table
GL_Interface
GL_JE_HEADERS, GL_JE_LINES,
GL JE BATCHES
.bad or .dis SQL* Loader
Error Message PL/SQL Block
Error Tables
GL Execution Journal Import
Report
55
ACCOUNTING_DATE and CREATION_DATE: Both columns will accept valid date but that
date should be less than or equal to System date.
CREATED_BY: WE have to enter valid user_id from FND_USER table we can identify weather
it is valid user_id or not.
PERIOD_NAME: We have to enter valid period name and period should be in the open status
from GL_PERIODS table we can find out weather it is valid period or not, from
GL_PERIOD_STATUS table we can find out period is in the open status or not.
ENTERED_DR and ENTERED_CR: Both columns will accept positive number Debit and credit
amount, both debit and credit should be equal otherwise account will be imported as suspense
account.
GROUP_ID: WE will enter unique group number while importing from interface table to base
table it will be used as parameter.
CTUAL_FLAG: This column will accept single character either ‘A’ or ‘B’ or ‘E’ a- Actual
amounts, B- Budget Amounts, E- Encumbrance Amounts
Reference 1 - Batch Name
Reference 2 - Batch Description
Reference 3 - Don’t Enter any value
Reference 4 - Journal Entry Name
Reference 5 - Journal Entry Description
Reference 11 to 20 - Don’t enter any values.
Chart_of_accounts_id - Don’t enter any values.
Transationc_date - Don’t enter any values.
Je-Batch_id - Don’t enter any values.
Je_header_id - Don’t enter any values.
Reference PDF : 115glup.pdf - Page no. 153
GL_JE_HEADERS
GL_JE_LINES
GL_JE_BATCHES GL INTERFACE
AP
AR
INV
FA
CM
F FIELS
56
Pre requisites for GL Interface:
1) Set of books should be defined (Currency, Calendar, Chart of Accounts)
2) 2) Currency Conversion Rates needs to be defined.
3) Accounting Period should be defined and also opened
4) Source name and as well as category name should be defined.
Process Steps:
1) We have received flat file from client
2) We have created Staging table as per flat file structure
3) Developed Control file and uploaded data
4) Developed PL/SQL Program to upload the data from stage into interface table
i. declare Cursor
ii. open cursor
iii. Validate each record
iv. If no invalid record then insert into interface table.
5) Run the journal import from GL => Journal => Import => Run
i. Give the two parameters 1)Source 2)Group ID
6) Open the Output if status is 'SUCEESS' then take Request ID.
7) open Journal Enter screen Query the records based on the %requestid% As
batch Name - Select Review journal button we can see the journal detailed
transaction
8) If we want correct the journals we can correct from Journal=>Import=>Correct
9) If we want delete the journals we can delete from Journal=>Import=>Delete
Temporary Table Creation
Create Table GL_INT_TEMP (
STATUS Varchar2(10),
SET_OF_BOOKS_ID Number(8),
ACCOUNTING_DATE, Date,
CURRENCY Number(8),
DATE_CREATED Date,
CREATED_BY Number(8),
ACTUAL_FLAG Varchar2(1),
CATEGORY Varchar2(10),
SOURCE Varchar2(10),
CURR_CONVERSION Number(8),
SEGMENT1 Varchar2(100),
SEGMENT2 Varchar2(100),
SEGMENT3 Varchar2(100),
SEGMENT4 Varchar2(100),
SEGMENT5 Varchar2(100),
ENTERED_DR Number(8),
ENTERED_CR Number(8),
ACCOUNTED_DR Number(8),
ACCOUNTED_CR Number(8),
GROUP_ID Number(8) );
57
Control File Creation – To transfer the data from flat file to staging table.
LOAD DATA
INFILE *
INSERT INTO TABLE GL_INT_TEMP
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(STATUS,
SET_OF_BOOKS_ID,
ACCOUNTING_DATE,
CURRENCY,
DATE_CREATED,
CREATED_BY,
ACTUAL_FLAG,
CATEGORY,
SOURCE,
CURR_CONVERSION,
SEGMENT1,
SEGMENT2,
SEGMENT3,
SEGMENT4,
SEGMENT5,
ENTERED_DR,
ENTERED_CR,
ACCOUNTED_DR,
ACCOUNTED_CR,
GROUP_ID)
BEGINDATA
"NEW",1,"11-AUG-2002","USD","11-AUG-
2002",1318,"A","Inventory","JETFORMS","Corporate","01","000","1410","0000","000",555,555,5
55,555,66
"NEW",1,"11-AUG-2002","USD","11-AUG-
2002",1318,"A","Inventory","JETFORMS","Corporate","01","000","1410","0000","000",554,554,5
54,554,66
"NEW",1,"11-AUG-2002","USD","11-AUG-
2002",1318,"A","Inventory","JETFORMS","Corporate","01","000","1410","0000","000",321,321,3
21,321,66
"NEW",1,"11-AUG-2002","USD","11-AUG-
2002",1318,"A","Inventory","JETFORMS","Corporate","01","000","1410","0000","000",431,431,4
31,431,66
"NEW",1,"11-AUG-2002","SGD","11-AUG-
2002",1318,"K","Inventory","JETFORMS","Corporate","01","000","1410","0000","000",1500,1500
,1500,1500,66
"NEW",1,"11-AUG-2002","EUR","11-AUG-
2002",1318,"A","Inventory","JETFORMS","Corporate","01","000","1410","0000","000",1600,1600
,1600,1600,66
58
Creation of PL/SQL procedure – to transfer the data from staging table to interface table
after validation.
CREATE OR REPLACE PROCEDURE GL_IN_PRO( Errbuf OUT VARCHAR2,
Retcode OUT VARCHAR2)
IS
-- cursor declaration
CURSOR gl_cur IS
SELECT
status ,
set_of_books_id ,
accounting_date ,
currency ,
date_created ,
created_by ,
actual_flag ,
category ,
source ,
curr_conversion ,
segment1 ,
segment2 ,
segment3 ,
segment4 ,
segment5 ,
entered_dr ,
entered_cr ,
accounted_dr ,
accounted_cr ,
group_id
FROM GL_INT_TEMP;
l_currencycode VARCHAR2(25);
l_set_of_books_id NUMBER(3);
l_flag VARCHAR2(2);
l_error_msg VARCHAR2(100);
l_err_flag VARCHAR2(10);
l_category VARCHAR2(100);
L_USERID NUMBER(10);
BEGIN
DELETE FROM gl_interface;
COMMIT;
FOR rec_cur IN gl_cur LOOP
l_flag:='A';
l_err_flag:='A';
--This PL/SQL Block will do the currency validation
--end of the currency validation
--Category Column Validation
BEGIN
SELECT USER_JE_CATEGORY_NAME
INTO l_CATEGORY
FROM GL_JE_CATEGORIES
WHERE USER_JE_CATEGORY_NAME = REC_CUR.Category;
59
EXCEPTION
WHEN OTHERS THEN
l_category:=NULL;
l_flag:='E';
l_error_msg:='Category does not exist ';
END;
Fnd_File.put_line (Fnd_File.LOG,'Inserting data into the Interface TABLE'
L_FLAG);
--End Category Column Validation
--User ID column validation
BEGIN
SELECT USER_ID
INTO L_USERID
FROM FND_USER
WHERE USER_ID = REC_CUR.created_by;
EXCEPTION
WHEN OTHERS THEN
L_userid:=NULL;
l_flag:='E';
l_error_msg:='User ID does not exist ';
END;
Fnd_File.put_line (Fnd_File.LOG,'Inserting data into the Interface TABLE'
L_FLAG);
--End of Created_by OR UserID column Validation
--Set of books Validation
BEGIN
SELECT set_of_books_id
INTO l_set_of_books_id
FROM GL_SETS_OF_BOOKS
WHERE set_of_books_id=rec_cur.set_of_books_id;
EXCEPTION
WHEN OTHERS THEN
l_set_of_books_id:=NULL;
l_flag:='E';
l_error_msg:='set of Books ID does not exist ';
END;
Fnd_File.put_line (Fnd_File.LOG,'Inserting data into the Interface TABLE'
L_FLAG);
-- End Set of books Validation
--Status Column validation
/* BEGIN
IF rec_cur.status = 'NEW' THEN
l_flag:= 'A';
ELSE
l_flag:= 'E';
Fnd_File.put_line (Fnd_File.LOG,'Status column has got invalid data');
END IF;
END;*/
BEGIN
SELECT currency_code
INTO l_currencycode
FROM fnd_currencies
WHERE currency_code=rec_cur.currency
AND currency_code='USD';
60
EXCEPTION
WHEN OTHERS THEN
l_currencycode:=NULL;
l_flag:='E';
l_error_msg:='currency code does not exists';
END;
Fnd_File.put_line (Fnd_File.LOG,'Inserting data into the Interface TABLE'
L_FLAG);
Fnd_File.put_line (Fnd_File.LOG,'Inserting data into the Interface TABLE'
L_FLAG);
--End of Actual Flag Column validation
IF l_flag!='E' THEN
Fnd_File.put_line (Fnd_File.LOG,'Inserting data into the Interface TABLE');
INSERT INTO gl_interface(status,
set_of_books_id,
accounting_date,
currency_code,
date_created,
created_by,
actual_flag,
user_je_category_name,
user_je_source_name,
user_currency_conversion_type,
segment1,
segment2,
segment3,
segment4,
segment5,
entered_dr,
entered_cr,
accounted_dr,
accounted_cr,
group_id)
VALUES
(rec_cur.status ,
rec_cur.set_of_books_id ,
rec_cur.accounting_date ,
rec_cur.currency ,
rec_cur.date_created ,
rec_cur.created_by ,
rec_cur.actual_flag ,
rec_cur.category ,
rec_cur.source ,
rec_cur.curr_conversion ,
rec_cur.segment1 ,
rec_cur.segment2 ,
rec_cur.segment3 ,
rec_cur.segment4 ,
rec_cur.segment5 ,
rec_cur.entered_dr ,
rec_cur.entered_cr ,
rec_cur.accounted_dr ,
rec_cur.accounted_cr ,
61
rec_cur.group_id);
END IF;
l_flag:=NULL;
l_error_msg:=NULL;
END LOOP;
COMMIT;
END GL_IN_PRO;
Purchase Order Inbound Interface
PO_HEADERS_INTEFACE
Column Name Validation Table Name
VENDOR_ID PO_VENDORS
VENDOR_SITE_ID PO_VENDOR_SITES_ALL
VENDOR_CONTACT_ID PO_VENDOR_CONTANTCT
AGENT_NAME PER_ALL_PEOPLE_F
ORG_ID HR_OPERATING_UNITS
DOCUMENT_LOOKUP_CODE Standard, Planed, Contract, Blanket
CREATION_DATE < = SYSDATE
CURRENCY_CODE FND_CURREINCES
SHIP_TO HR_LOCATIONS
PO Flat File
XXXX Staging Table Headers
XXXX Staging Table Lines
PO_HEADERS_INTERFACE
PO_LINES_INTERFACE
PO_HEADERS_ALL
PO_LINES_ALL
PO_LINE_LOCATIONS_ALL
PO_DISTRIBUTIONS
.bad or .dis SQL* Loader
Error Message PL/SQL Block
Error Tables
PO_INTERFACE Standard Program
_ERROR
62
BILL_TO HR_LOCATIONS
PO_LINES_INTERFACE
Column Name Validation Table Name
LINE_NUM PRIMARY KEY
LINE_TYPE PO_LINE_TYPES
ITEM
ITEM_ID
ITEM_DESCRIPTION
MTL_SYSTEM_ITEM_B
ITEM_CATEGORY MTL_CATEGORIES
ITEM_UOM MTL_UNITS_OF_MEASURE
QUANTITY Any Positive number
UNIT_PRICE Any Positive number
NEED_BY_DATE
PROMISED_DATE > = PO CRATION DATE
SHIP_TO_ORG_ID ORG_ORGANIZATION_DEFINITIONS
SHIP_TO_LOCAITONS HR_LOCATIONS
ORG_ID HR_OPERATING_UNITS
Pre Requisites
1) Supplier sites contact details should be defined
2) Organization structure should be defined
3) Ship to bill to locations needs to be defined
4) Set of books needs to be defined
5) Items, item categories, UOM needs to be defined
6) Employee creation, buyer setup should be defined.
Process Steps
1) Create the Staging tables
2) Develop the Control files and register as concurrent program
3) Develop the PL/SQL Program and write the validations and insert into interface table
4) Run the standard program called Import Standard Purchase orders from PO
Responsibility
Parameter : Default Buyer : Null
Create or update items : No
PO Status : APPROVED
Batch ID : 13
(We can get from headers interface table)
5) Take the Request ID execute following query we can get the PO numbers
select segment1 Ponumber
from po_headers_all
where request _id = 145233;
6) Go to the PO Application and Query the PO from as per the PO number.
63
Control File: - PO_HEADERS_INTERFACE
load data
infile *
TRUNCATE into table XX_HEADERS
fields terminated by "," optionally enclosed by '"'
TRAILING NULLCOLS
( INTERFACE_HEADER_ID
,BATCH_ID
,ACTION
,ORG_ID
,DOCUMENT_TYPE_CODE
,CURRENCY_CODE
,AGENT_NAME
,VENDOR_NAME
,VENDOR_SITE_CODE
,SHIP_TO_LOCATION
,BILL_TO_LOCATION
,APPROVAL_STATUS
,FREIGHT_CARRIER
,FOB
,FREIGHT_TERMS
)
BEGINDATA
1,13,"ORIGINAL",204,"STANDARD","USD","Stock, Ms. Pat","IBM","IBM-BAN","H1-
Detroit","V1- New York City","APPROVED","UPS","Origin","Due"
2,13,"ORIGINAL",204,"STANDARD","USD","Stock, Ms. Pat","SONY","SONY-CHN","H1-
Detroit","V1- New York City","APPROVED","UPS","Origin","Due"
Control File: - PO_LINES_INTERFACE
load data
infile *
insert into table XX_LINES
fields terminated by "," optionally enclosed by '"'
TRAILING NULLCOLS
( interface_header_id
,interface_line_id
,LINE_NUM
,SHIPMENT_NUM
,LINE_TYPE
,ITEM
,ITEM_DESCRIPTION
,item_id
,UOM_CODE
,QUANTITY
,UNIT_PRICE
,SHIP_TO_ORGANIZATION_CODE
,SHIP_TO_LOCATION
,list_price_per_unit)
64
BEGINDATA
1,1,2,2,"Goods","AS54999","Sentinel Standard Desktop -
Rugged",2155,"Ea",10,120,"M1","Adelaide",45
1,2,3,3,"Goods","75100005","Colour Cartidge",6076,"Ea",11,111,"M1","Adelaide",55
1,3,4,4,"Goods","AS54999","Sentinel Standard Desktop -
Rugged",2155,"Ea",1,1400,"M1","Adelaide",65
2,4,5,1,"Goods","AS54999","Sentinel Standard Desktop -
Rugged",2155,"Ea",222,234,"M1","Adelaide",105
Procedure for Insertions of data from Interface tables to Base tables after validating.
CREATE OR REPLACE PROCEDURE PO_Int1(Errbuf OUT VARCHAR2,
Retcode OUT VARCHAR2) AS
CURSOR c1 IS SELECT * FROM PO_HEADER_TABLE;
CURSOR c2 IS SELECT * FROM PO_LINE_TABLE;
l_vendor_id number(10);
l_item varchar2(150);
l_flag varchar2(4) default 'A';
l_msg varchar2(200);
l_site_code varchar2(100);
l_curr_code varchar2(10);
l_org_id number(6);
BEGIN
DELETE FROM PO_HEADERS_INTERFACE;
DELETE FROM PO_LINES_INTERFACE;
COMMIT;
FOR x1 IN c1 LOOP
BEGIN
SELECT vendor_id
INTO l_vendor_id
FROM po_vendors
WHERE vendor_name = x1.VENDOR_NAME;
-- AND ORG_ID = Fnd_Profile.Value('ORG_ID');
EXCEPTION
WHEN OTHERS THEN
l_flag := 'E';
l_vendor_id := 0;
l_msg := 'Vendor id is Not in SYSTEM';
END;
--Vendor Site code Validation
/* begin
select vendor_site_code
into l_site_code
from po_vendor_sites_all
where vendor_site_code = x1.vendor_site_code;
EXCEPTION
WHEN OTHERS THEN
l_flag := 'E';
65
l_site_code := 0;
l_msg := 'Vendor Site Code is Not in SYSTEM';
END;*/
--End of Site Code Validation
--Currency Code Validation
Begin
select currency_code
into l_curr_code
from fnd_currencies
where currency_code = x1.CURRENCY_CODE;
EXCEPTION
WHEN OTHERS THEN
l_flag := 'E';
l_curr_code := 0;
l_msg := 'Currency Code is Invalid';
END;
--End of te Currency Validation
--Operating Unit ID Validation
Begin
select organization_id
into l_org_id
from hr_operating_units
where organization_id = x1.org_id;
EXCEPTION
WHEN OTHERS THEN
l_flag := 'E';
l_org_id := 0;
l_msg := 'Invalid Organization ID';
END;
--End of the ORG ID Validation
IF l_flag != 'E' THEN
INSERT INTO po_headers_interface
(
INTERFACE_HEADER_ID
,BATCH_ID
,ACTION
,ORG_ID
,DOCUMENT_TYPE_CODE
,CURRENCY_CODE
,AGENT_NAME
,VENDOR_NAME
,VENDOR_SITE_CODE
,SHIP_TO_LOCATION
,BILL_TO_LOCATION
,creation_date
,APPROVAL_STATUS
,APPROVED_DATE
,FREIGHT_TERMS
)
VALUES
(
66
x1.INTERFACE_HEADER_ID
,x1.batch_id
,x1.action
,x1.org_id
,x1.document_type_code
,x1.CURRENCY_CODE
,x1.AGENT_NAME
,x1.VENDOR_NAME
,x1.VENDOR_SITE_CODE
,x1.SHIP_TO_LOCATION
,x1.BILL_TO_LOCATION
,SYSDATE-10
,x1.APPROVAL_STATUS
,SYSDATE
,x1.FREIGHT_TERMS
);
end if;
END LOOP;
FOR x2 IN c2 LOOP
l_flag := 'A';
--Item Validation
begin
select segment1
into l_item
from mtl_system_items_b
where segment1 = x2.item
AND ORGANIZATION_ID = fnd_profile.value('ORG_ID');
exception
when others then
l_flag := 'E';
l_vendor_id := 0;
l_msg := 'Item is not valid Item';
END;
--End of the Item Validation
if l_flag != 'E' then
INSERT INTO PO_LINES_INTERFACE
(
INTERFACE_LINE_ID
,INTERFACE_HEADER_ID
,LINE_NUM
,SHIPMENT_NUM
,LINE_TYPE
,ITEM
,ITEM_DESCRIPTION
,item_id
,UOM_CODE
,QUANTITY
,UNIT_PRICE
,SHIP_TO_ORGANIZATION_CODE
,SHIP_TO_LOCATION
,NEED_BY_DATE
67
,PROMISED_DATE
,list_price_per_unit
)
VALUES
(
x2.INTERFACE_LINE_ID
,x2.INTERFACE_HEADER_ID
,x2.LINE_NUM
,x2.SHIPMENT_NUM
,x2.LINE_TYPE
,x2.ITEM
,x2.ITEM_DESCRIPTION
,x2.item_id
,x2.UOM_CODE
,x2.QUANTITY,
X2.UNIT_PRICE,
X2.SHIP_TO_ORGANIZATION_CODE,
X2.SHIP_TO_LOCATION,
X2.NEED_BY_DATE,
X2.PROMISED_DATE,
X2.LIST_PRICE_PER_UNIT);
END IF;
END LOOP;
COMMIT;
END PO_INT1;
Conversion
Conversion is one of the program which will be used to upload the data from legacy system in to
oracle applications, base tables, one time data transfer like
• Customer Conversion
• Supplier Conversion
• Employee Conversion
• Items Conversion and so on
Differences between Inbound Interface and Conversion
Inbound Interface Conversion
It is schedule concurrent process which will
be executed multiple times
One time data transfer
WE will not be knowing the flat file volume We will be knowing the exact flat file
volume
We need to handle the all the expected
exceptions
We do not need to know all the exceptions
Error Reports and sending email alerts, if
any error occurs
It is not required we have to upload all the
records and close.
Enhancement or customization projects Implementation, Migrations or up-gradation
projects we will find.
68
Request Set
• It is nothing but collection of concurrent programs and report, which will be used to
submit more than one program at a time from SRS windows
• Navigation – Concurrent – Set and enter - Select Request Set Wizard button, select
sequentially or parallel option, enter request set name and enter concurrent programs
select finish button.
• Go to the request group, select type as set attach request set.
• Go to SRS window select Request set option instead of single request.
Executable
Concurrent
Programe
Request Set
Request Group
Responsibility
User
SRS
69
Accounts Payables
Procure to Pay Cycle
• Without supplier we cann’t create invoices
• Without Invoices we can’t make payments.
Types of Invoices
1) Standard – For all the normal payments we will crate this.
2) Prepayment - Advance Payment to the supplier based on the requirement.
3) Credit Memo - If supplier give the discount - for negative amount
4) Debit Memo - If company is going to deduct the amount - for negative amount
5) With holding tax – if supplier is not recognized, the on be half of the supplier company
will pay the tax.
6) PO Default - We will give the PO number according to that it will generate the
Invoices.
7) Recurring Invoices – WE will generate the Invoices multiple times for fixed supplier, fixed
amount, fixed duration.
8) Expense Report – Employee expenses
9) Quick Match – 2 Way, 3 Way, 4 Way
10) Mixed Invoice – Miscellaneous expenses
After creating the invoice we have to do the 3 steps
1) Validate Invoice with Distribution lines – Invoice Amount = Distribution Amount
2) Approve Invoice
3) Create Accounting Transactions.
GL AP PO
Requisition
RFQ
Quotation
PO
Receipts
Invoices
Payments
Transfer to GL
70
Creating Invoice
Payables, Vision Operations (USA)
Tools – View Accounting
AP_INVOICES_ALL
AP_INVOICES-DISTRIBUTIONS_ALL
Creating accounting transactions these data will be stored in the following tables.
AP_AE_HEADERS
AP_AE_LINES
NOTE: WE can give same number to other invoice number for other suppliers.
• Open the Invoice form N-Invoices-Entry-Invoices select Invoice type supplier data enter
Invoice number and amount.
• Select distributions button and enter the distribution details save the transactions.
• Compare Invoice table and distribution total
• Select actions button check the checkbox called approve to get the Invoice approval
• Select actions button check the check box called crate accounting, it will crate invoice
accounting transactions.
• Select tools menu view accounting option to find the accounting entries.
Distribution Set:
• (N) Setup – Invoices – Distribution Set and press enter – It will be used to generate the
invoice distributed automatically. AT the time of creating invoices we will attach
distribution set which will distribute the amounts
• Tables
o AP_DISTRIBUTION_SETS_ALL
o AP_DISTRIBUTION_SETS_LINES_ALL
• After creation of invoice we have to submit the transfer program called – Paybles
transfer to General Ledger.
o Copy the Invoice data, go to SRS window select the following program
�� Payables Transfer to General Ledger
• From Date
• To Date
o After completion of this program system will automatically submits the ‘Journal
Import’ program to transfer in the GL Base Tables.
o Copy the Request ID of ‘Journal Import’ program go to GL application
o Open the journal enter screen and enter %Request Id% in the batch filed which
will shows the Invoice date
o Select the Review Journal button which will displays the invoices detailed
transactions.
71
Process Steps
1) AP Table –
a. AP_INVOICES_ALL
b. AP_INVOICES_DISTRIBUTIONS_ALL
2) Payables transfer to General Ledger
3) Select * from GL_INTERFACE
4) Journal import program
a. GL_JE_BATCHES
b. GL_JE_LINES
c. GL_HEADERS
5) Positing Journals
a. Select * form GL_BALANCES.
Payment in Payables
1) Quick - System will automatically generate check
2) Refund – Refund Approval payments
3) Manual – Cash or check
• Crate the Invoice validate approve it and crate accounting transactions, select payments
from (N) Payments – Entry – Payments and enter select Payment type, Bank Account
Name, Document as check system will automatically generate check number enter
supplier details, select the button called enter invoices select invoice number save the
transactions.
• Select actions button, check the check box called crate accounting to create accounting
transactions, select tools menu, view accounting option, it will displays accounting debit,
and credit transactions, select the button called payment overview it will give complete
payment details.
Important AP Payment Tables
• AP_INVOICES_ALL
• AP_INVOICE_PAYMENTS_ALL
• AP_CHECK_ALL
• AP_BANK_ACCOUNTS
• AP_BANK_BRANCHES
Payment Holds – Invoice needs re-approval then we can check hold buttoned and see
• AP_HOLD_ALL
• AP_HOLD_RELEASE_NAME_V
Payment Terms
• AP_TERMS
72
• AP_TERM_LINES
• AP_APYMENT_SHEDULES – It contains the scheduled payment details
Expense Report
• AP_EXPENSE_REPORTS_HEADERS_ALL
• AP_EXPENSE_REPORTS_LINES_ALL
Recurring Invoices
• AP_RECURRING_PAYMENTS_ALL
ORACLE INVENTORY
Items Item Attributes
Assign Organizations
Assign to categories
Define lot / serial no
Define revisions
Unit of Measure
Item Attributes
Purchasable
Inventory
WIP Item
Customer
Stackable
Item Transactions Sub Inventory Transfer
Miscellaneous Transfer
Move Orders
Inter Organization Transfer
Item Locations Inventory
Sub Inventory
Stock locations
Item Quantities Items on Hand Qty
Items Resserved Qty
Item Available Qty
Items
Items Defining – Master Items
ORG-ORGANIZATIONS_DEFNINITIONS
MTL_SYSTEM_ITEMS_B
To Assign the Multiple Organizations (N) Tools -> Organization
To Revisions – Tools - > Revision
MTL_ITEM_REVISOINS - Inventory_item_id and Org_id
To know quantity – MTL_ONHAND_QUANTITIES
Do PO Cycle -
Inventory – Receiving – Receiving Transactions
• Open the Items form, enter Item name and description select the tabs and check the
check box called Inventory items, and Purchased Item - Go to tools menu select option
73
called organization assignment to assign the item to the organization. Select Revision
option to create Item revisions.
• Open the on hand quantity form enter Item name select find button, it will give item
quantity and details report.
• Once we create item quantity will be zero we have to purchase from suppliers or we
have to receive from other organizations.
• Select PO Applications create requisitions purchase order and receipt copy the receipt
number to Inventory applications, open the receiving transaction form (N) Transactions
– Receiving – Receiving Transactions ----- Enter the receipt number select find button
check the check box save the transactions.
o MTL_SECONDARY_INVENTORIES
o MTL_ITEM_LOCATIONS
o MTL_ITEM_CATEGORIES
o MTL_CATEGORIES
o MTL_MATERIAL_TRANSACTIONS
o MTL_MATERIAL_ACCOUNTS
o MTL_TRANSACTION_ACCOUNTS
• (N) Setup – Organization- Sub inventories and press enter
o WE can transfer the Item form one sub Inventory to another sub inventory by
using sub inventory transfer
o MTL_TRANSACTION_TYPES
o MTL_MATERIAL_TRANSCATIONS
o MTL_TRANSACTION_ACCOUNTS
• Other Tables
o MTL_PARAMETERS – Contains both Master and child organization
o FINANCIAL_SYSTEM_PARAMETERS – Financial setup details
o CST_ITEM_COST – Item cost Details
o CST_ITEM_COST_TYPES
o ITEM_LOT_SERIAL_NUMBERS
o MTL_LOT_NUMBERS
o MTL_SERIAL_NUMBER
o MTL_UNITS_OF_MEASURE
Order Management
Enter Sales Order - We get orders from Customer
Book Order - Customer Confirmation
Pick release - Release the Materials from Inventory
Ship confirm - Delivery Details, Shipping Details.
Auto Invoice - Generate the Invoice
After Pick Release if the goods are not available again PO cycle we have to follow
74
Drop Shipping : Sales and Purchase the materials without having the Inventory
Client
Supplier Customer
Sales Order Creations:
• (N) Orders & Returns – Sales Orders and data 2 levels headers and lines header level
customer Information customer ship to bill to and sales person other tax details, select
line items tab enter the Item, quantity, price and other details select the button called
book order to book the sale orders select availability button it will gives the Item available
on hand reserved quantity details select ATP details button it will give the available to
promise date details.
• Select actions button to cancel the sales order to calculate the tax and to keep the sales
orders on hold and release the hold and so on.
o OE_ORDER_HEADERS_ALL
o OE_ORDERS_LINES_ALL
Release the Sales Orders
• (N) Shipping – Release Sales Orders – Release shipments and enter the, and the enter
sales order number select the button called concurrent, it will submit the concurrent
program to release the sales order it picks Items from Inventory tables and user in to
deliver tables.
Pick confirmation and Ship Confirmation
• Once the Items are released, we will confirm the materials packing, then we will give the
shipping date (N) shipping – Transaction and enter
o RA_CUSTOMER_TRS_ALL
o RA_CUSTOMER_TRS_LINES_ALL
o WSH_DELIVER_DETAILS
o OE_ORDER_LINES_ALL
o OE_ORDER_HEADER_ALL
o WSH_NEW_DELIVERS.
OTHERs
Concurrent programs – Incompatibilities
• It is nothing but not compatible for other programs we will select this button we will list
out the programs which are not compatible.
• Run Alone check box: By using this check box we can make the program is incompatible
for all other concurrent programs.
• Copy to Button: By using this button we can create new concurrent program including
the parameters and incompatible programs.
75
Pair Value Set:
• We want to pass KFF data as a parameter to the executable then we will use validation
type as paid select edit information button. Pass the parameters like applications short
name KFF CODE, Structure number and so on
o Example: GL_SRS_LEDGER_FLEXFIELD
$FLEX$
• It will be used to retrieve the previous parameter value in to the next parameter values
set
o : $FLEX$.Previous Value set name
$PROFILE$
• To Get the front end the table value set where clause from backend we will
o Fnd_profile.value
o Fnd_profile.get
o Select segment1, org_id from PO_HEADERS_ALL
WHERE ORG_ID=$PROFILE$.ORG_ID
No comments:
Post a Comment