Different Types of Tables in Oracle Apps
1)
Interface Tables
2)
Base Tables
1)
Global Tables
2)
Org Specific
Tables
3)
Language Specific
Tables
Interface Tables :
These
are intermediate tables between apps base tables and external tables. Oracle
Program is used to transfer data from other tables to Apps Interface tables. Apps
interface program is used to transfer data from Interface tables to Base
tables.
Eg:
AP_INVOICE_HEADER_INTERFACE
AP_INVOICE_LINES_INTERFACE
Base Tables :
These
are actual tables where the data will be stored. These tables will not be
updated from the back end .
A
from is dependent on one or more tables.
Eg:
AP_INVOICE_ALL
Based on storage of data, base tables are divided into
following
1)
Global Tables
2)
Org Specific
Tables
3)
Language Specific
Tables
Global Tables :
The data in this tables is not specific to any
organization of a business. This data can be shared across all the
organizations of business group.
Eg: Employs
data, Suppliers data, Accounting data, Bank Transactions data
Org specific Tables :
The data stored in this tables are specific to particular
organization.
Eg: Invoice Details
Org Specific tables will have ALL as their suffix
AP_INVOICE_ALL
Language Tables :
Apps supports the multiple languages. These tables will
maintain the language specific data.
These table will have TL as it’s suffix
Different Types Columns in a table :
1) Data Columns :
These columns contains base
transactional data, which is entered from the apps form.
USER_NAME, DESCRIPTION, EMPLOYEE_ID, EMAIL_ADDRESS
2) Derived Columns :
These are primary key columns. This data will be generated
from oracle database sequence.
USER_ID
3) Who Columns :
These are called as history columns.
This data will be generated automatically by the oracle application form.
These columns are
1)
CREATED_BY
2)
CREATION_DATE
3)
LAST_UPDATED_BY
4)
LAST_UPDATE_DATE
5)
LAST_UPDATE_LOGIN
Every
apps table will have the above columns
4) Global Attribute Columns :
These columns will maintain the functionality
information, which is provided by the ORACLE Corp.
GLOBAL_ATTRIBUTE1
GLOBAL_ATTRIBUTE2,
…. 10.
5) Additional Cols :
These
columns are used to store the data, which is from additional fields added to
the existing form during the customization of a form.
ATTRIBUTE1,
ATTRIBUTE2, ATTRIBUTE3….. 15
6) Key Columns : These are called as segment columns. Used to maintain the key flex
field data.
A field will have subfields
Eg:
Account Num :
COMPANY-COUNTRY-DEPARTMENT-ACCOUNT
LG-IND-SALES-4523
SEGMENT1, SEGMENT2, …. SEGMENT20.
Developing one more report and to
application and execute
Eg:
Employees information report
EmpId EmpName Startdate
1) develop the report
2) transfer form client machine to
server machine
3) create executable
4) create concurrent program and attach
executable
5) Create Request Group and attach Concurrent Program
6) create responsibility and attach 1)Request Group
2)Data Group
3)Menu
7) add the responsibility to user
8) go to srs execute
Report with input Parameters
STEP 1: Develop
the report with parameters
Requirement:
First Page :
Title from
the user input
Second Page :
UserId UserName CreationDate
Last Page :
Total Users
:
Input parameters are
Enter From Id :
Enter To Id :
Enter Title
1)
Create three parameter
P_From : number type (10)
P_To : number type (10)
P_Title : char (100)
2)
Data Model
Query :
SELECT USER_ID,
USER_NAME,
CREATION_DATE
FROM FND_USER
WHERE USER_ID BETWEEN
:P_FROM AND :P_TO
Create Summary column for total count :
USER_COUNT
3)
Layout model
Header
Section : text filed à map with P_TITLE
Main
Section : required layout and map the
text fields with required fields
Trailer
Section : Text Field à map
with USER_COUNT
4) compile and test the report
5) save in the local machine : file name : user-param.rdf
STEP 2: transfer
the file user-param.rdf from local machine to the server
STEP 3 : LOGIN
INTO APPLICATION
Create
executable file
STEP 4: Create concurrent program and attach
the executable
Map
the parameters
Click
on parameters button ( at the bottom )
Seq : it is serial number ( unique value )
Parameter : Enter From Id ( its prompt value )
Description : as desired (optional)
Value Set : data type ( 10/number pre-defined )
Token : P_From ( it is a name of the input
parameter in the report for mapping )
Required
: enable à
mandatory field
Disable à optional filed
Range
: low à for validation ( lower than other
filed value )
STEP 5 : create
request group and attach the concurrent program
STEP 6 : create
responsibility and attach
Request
Group
Data
Group
Menu
STEP 7 : add responsibility to user
STEP 8 : go to srs window
Go
to application home
Select
the any on the left side pane it shows current responsibility
View
à request
Submit
request
Change in the above report
Lay
out Model :
Header Section :
Default
Title : if user input is null it should be displayed
Select
the label and place on the canvas ( user
information report )
Write
the trigger ( right click )
PL/SQL Editor :
function B_5FormatTrigger return
boolean is
begin
if( :P_TITLE is null )then
return (TRUE);
else
return
false;
end if;
end;
Trailer Section :
Default
label : No data found ( it total no.of records are zero )
Select
the label and place on the canvas (No
data found )
Write
the trigger ( right click )
PL/SQL Editor :
function B_6FormatTrigger return
boolean is
begin
if ( :USER_COUNT <=0 )THEN
return (TRUE);
else
return false ;
end if ;
end;
Text field it’s lable : disable if
no records
PL/SQL
Editor : ( for both
same code )
if ( :USER_COUNT <=0 )THEN
return false;
else
return true ;
end if ;
Again Change in the above report
Using lexical parameters dynamically
If input for both From Id and To Id
is null where clause should be obtained dynamically
1) Create lexical parameter : P_WHERE
2) Query :
SELECT USER_ID,
USER_NAME,
CREATION_DATE
FROM FND_USER
&P_WHERE
Here lexical parameter is not reading
form the user. It’s value populated dynamically. Hence it is not required to
map with
the application parameters
To populate dynamical write PL/CODE
in the after parameter form trigger
function AfterPForm return boolean is
begin
if
( :P_FROM is null and :P_TO is null ) then
:P_WHERE:='';
else
:P_WHERE:='where
USER_ID between :P_FROM and P_TO';
end
if ;
return (TRUE);
end;
CHECKING THE REQUIRED OPTION :
enable ( mandatory field )
Go to Create concurrent program à
parameters à enable the fields
Working with date :
Requirement
Input :
From
Date :
To
Date :
UserId UserName CreationDate
Total Users :
Step1: Develop
the report
1)
Create Parameters
P_FROM :
Date type : date, input mask : YYYY/MM/DD
HH24:MI:SS
P_TO :
Date type : date, input mask : YYYY/MM/DD
HH24:MI:SS
2)
Data Model
Query
SELECT USER_ID,
USER_NAME,
CREATION_DATE
FROM FND_USER
WHERE CREATION_DATE
BETWEEN :P_FROM AND :P_TO
create
a summary column : TOTAL_COUNT
3)
Layout Model
Header Section :
Prepare the required title
Main Section:
UserId UserName CreationDate
Trailer
Section :
Total
Users :
Repeat the other steps such registry
and running as explained in the previous examples
Step 5 : while creation of concurrent
program :
Parameters
:
Pre
defined Date value set : FND_STANDARD_DATE
In all the above example we have
followed following steps
1) Executable
2)
Concurrent Program
3)
Request Group
4)
Responsibility
5)
Users
6)
SRS
Here the question is why not use
executable directly with users ?
What is the advantage in using
Concurrent program ?
Purpose
: single exe can be created with multiple Concurrent Programs
with
different input parameters
with
different output forms
with
different applications
different input parameters
eg:
Client1
requires : only one parameter ( From Date)
Client2
requires : two parameters ( From Date )
( To Date )
different output forms:
eg:
Client1
requires in PDF format
Client2
requires in XML format
Other
client requires in excel sheet format
What is the advantage in using
Request group ?
To group the multiple programs
What is the advantage in using
Responsibility group ?
Menu
à we can add different menus with different Concurrent
Program and Form can also be attached
Request
Group : Nothing
Data
Group : Nothing
Default Types
1) Constant
2) SQL Statement
3) Segment
4) Current Date
5) Current Time
6) Profile
Constant
To pass the constant values such as
numbers, chars, String , Date.
Enter From
Id
Enter To Id
Enter Tile :
Eg:
Enter Tile :
Default Type
: constant Default
Value : Users Information Report
SQL Statement
Enter From
Id
Enter To Id
Enter Tile :
Enter From
Id :
Default Type
: sql statement Default
Value : select min(user_id) from fnd_user
The above select query returns
user_id : -1, -ve value will not be considered for 10/number, we have to change
data type as 15 char
Enter To Id :
Default Type
: sql statement Default
Value : select max(user_id) from fnd_user
This
is to get previous parameter value to the next parameter
Enter
From Id:
Enter
To Id :
Default
Type: segment Default
Value : Enter From Id
Value Sets : System provides built-in ( predefined ) value sets
Value set is a list of values with
validations which will be used to restrict the user without entering the
invalid data in the Parameters
value
sets are used in two scenarios.
1)Concurrent Program parameters
2)Flex fields
No comments:
Post a Comment