- Is nothing but instances of execution file along with parameter and incompatible programs
- We can register 11 types of executable as Concurrent Programe
- We can execute multi language programs as Concurrent Programe
- When we run Concurrent Programe we can utilize 100% local machine hardware capacity
- We can change the Concurrent Programe output based on employee profile
- We can submit only Request
- We can schedule the Concurrent Programe as per client requirement.
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
- CUS_TOP\11.5.0\reports\US .rdf (or)
- PO_TOP\11.5.0\reports\US .rdf
3) Select System Administrator
- Create Executable
- Executable name
- Application Name
- Execution Method
- Report (.rdf) file name
4) Create Concurrent Programe and attach
5) Create Request group and attach Concurrent Programe
6) Create Responsibility
- Request Group – Concurrent Programe
- Data Group – Collection of User IDs
- 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
After moving .rdf in to the server, we will crate executable by specifying execution name execution method and application name.
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.
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.
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 is nothing but collection of applications names and Oracle User Names based on this Username data will be retrieved from database.
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
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 = ‘ ‘;
Reports with Parameters
Select * from FND_USER
USER_ID, USER_NAME, CRATION_DATE
From USER_ID, To USER_ID
Designing of layout
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.
- 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.
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.
Select User_ID, User_name, Trunc(creation_date) from fnd_user;
1) P_From_Date – Date – Input Mask
2) P_To_Date – Date –Input Mask
3) P_Title – Character – 100
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
To register the parameter
P_From_Date – Values Set – FND_DATE
P_From_Date – Current Date
P_To_Date – Current Date
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
Default Type as – Segment Option
Default Values – Specify the previous parameter
VALUES 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
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.
- 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.
- Enter the values set name select format type enter the minimum size select validation type as NONE.
- Once the value set is created we can use it for any concurrent program either single time or multiple times.
- 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.
- 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
- 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.
- Open the value set for an enter value set name select format type and select validation type as Independent.
- 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.
- 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.
- Dependent value set is nothing but another LOV but values will be changing based on the previous Independent value set.
- When we are creating dependent we must have 2 parameters 1)Independent 2) Dependent
- Open the value set form create Independent value set go to values from enter the values.
- Open the values set form create dependent value set by selecting validation type as dependent.
- Select edit information button attach Independent value set what ever we have created.
- Copy the value set name go to values screen click on find button enter the values based on the Independent value.
Select user_name, user_id, creation_date
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.
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.
2) 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 languag
D – Disabled language
3) 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
- When the width of the report is more we have chose the style of the output in Concurrent program window BACS instead of A4.
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
- 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
If :P_Title is NULL then
If parameters were not passed to the :P_From_Date and :P_To_Date report should display all the records.
After Parameter Form Trigger
If :P_From_Date is NULL and :P_To_Date is NULL then
:P_lexical:= ‘ ‘
:P_lexical:= ‘and Trunc(fcp.creation_date)
between :P_From_Date and :P_To_Date
From User ID, To User ID, From Date, To Date
Query prepared by SIR in class
WHERE USER_ID BETWEEN :P_FROM_ID AND :P_TO_DATE
FROM FND_USER FU,
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
o Create the table value set for select the parameters.
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)
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.
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
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