Query and requirement analysis
1. Analyse the tables involved in the requirement and write the required sql query in toad and run it to see view the desired output. Once we get the desired output, Copy the query.
Connect report to database and design 'data model'
2. Open report and select 'build a new report manually' and then Select the 'SQL' button from the
left panel. paste the query in the pop up box. Connect to the desired database.
3. Form the desired 'data model' structure of the report.If required, do the parent-child structure as per the requirement.
For instance, if we join dept table with emp table, such that dept.deptno= emp.deptno,
then we have to the place the fields of the emp columns inside another repeating frame.
Thus, the resultant will have the dept columns inside a repeating frame and repeating
frame of the emp columns will be inside the repeating frame of the dept. So here, dept will act
as a parent and emp as a child.
Design of report layout
4. Click on the 'paper layout' option on the top and draw the necessary fields as per the
requirements.
5. Sequence of choosing the options from the left navigation panels in the 'paper layout' window.
a. Select and draw frames. Double click the area to open the
"property inspector" window. Change the "vertical elasticity" and
"horizontal elasticity" to "variable" or "expand".
b. Select and draw the text and write as per column name or as per requirement.
c. select and draw "repeating frame".Double click the area to open the
"property inspector" window. Change the "vertical elasticity" and
"horizontal elasticity" to "variable" or "expand" and change the 'source'
option to the repeating field name.
d. select and draw 'field'. The total no of fields must be same to the
total no of columns.
6. Once all the above is done, save the report in .rdf file.
Adding user parameters and necessary triggers
7. Expand the 'data model' option of the left object navigation panel. Select 'User parameter'
click the '+' sign on the left side and enter in the Property-inspector
window as well as the parameter name (default value is P_1) as follows-
Name: P_CONC_REQUEST_ID
width: 15
8. Edit the 'Report triggers' as follows-
Before Report(right click and edit in plsql editor): begin
srw.user_exit('FND_SWRWINIT');
return (TRUE);
end;
After Report (right click and edit in plsql editor): begin
srw.user_exit('FND_SRWEXIT');
return (TRUE);
end;
9. Save the form.
Compilation of the report
10. Go to (on the top) Program -> Compile -> All
The report must compile successfully.
Copy the .rdf file to server folder
11. Once all the above process is done, including the report layout
and defining the triggers and User parameters.
Move or copy the .rdf file to the server folder through WINSCP
(For instance, let the folder name be CUSTOM_TOP).
Registering the report in apps.
12. Open Oracle app, Go to the Application developer module -> Concurrent
13. Once the form opens, enter 'Executable', 'Short name' (The short is important as the same
short name used be used later).
14. Enter 'application' - Folder name to which we moved the .rdf file thro' winSCP.
15. Enter Execution method as - 'Oracle Reports'.
16. Enter 'execution file name' - EMP_detail (enter same as the .rdf file name).
17. Copy the short name and save the form and close the form.
18. Navigate to the 'Program' under concurrent.
19. Enter Program, short name, Application (enter any name).
20. Enter 'name (executable)' - paste the copied short name.
21. Select the desired output format. Generally, we select .pdf option.
22. Save the form.
1. Analyse the tables involved in the requirement and write the required sql query in toad and run it to see view the desired output. Once we get the desired output, Copy the query.
Connect report to database and design 'data model'
2. Open report and select 'build a new report manually' and then Select the 'SQL' button from the
left panel. paste the query in the pop up box. Connect to the desired database.
3. Form the desired 'data model' structure of the report.If required, do the parent-child structure as per the requirement.
For instance, if we join dept table with emp table, such that dept.deptno= emp.deptno,
then we have to the place the fields of the emp columns inside another repeating frame.
Thus, the resultant will have the dept columns inside a repeating frame and repeating
frame of the emp columns will be inside the repeating frame of the dept. So here, dept will act
as a parent and emp as a child.
Design of report layout
4. Click on the 'paper layout' option on the top and draw the necessary fields as per the
requirements.
5. Sequence of choosing the options from the left navigation panels in the 'paper layout' window.
a. Select and draw frames. Double click the area to open the
"property inspector" window. Change the "vertical elasticity" and
"horizontal elasticity" to "variable" or "expand".
b. Select and draw the text and write as per column name or as per requirement.
c. select and draw "repeating frame".Double click the area to open the
"property inspector" window. Change the "vertical elasticity" and
"horizontal elasticity" to "variable" or "expand" and change the 'source'
option to the repeating field name.
d. select and draw 'field'. The total no of fields must be same to the
total no of columns.
6. Once all the above is done, save the report in .rdf file.
Adding user parameters and necessary triggers
7. Expand the 'data model' option of the left object navigation panel. Select 'User parameter'
click the '+' sign on the left side and enter in the Property-inspector
window as well as the parameter name (default value is P_1) as follows-
Name: P_CONC_REQUEST_ID
width: 15
8. Edit the 'Report triggers' as follows-
Before Report(right click and edit in plsql editor): begin
srw.user_exit('FND_SWRWINIT');
return (TRUE);
end;
After Report (right click and edit in plsql editor): begin
srw.user_exit('FND_SRWEXIT');
return (TRUE);
end;
9. Save the form.
Compilation of the report
10. Go to (on the top) Program -> Compile -> All
The report must compile successfully.
Copy the .rdf file to server folder
11. Once all the above process is done, including the report layout
and defining the triggers and User parameters.
Move or copy the .rdf file to the server folder through WINSCP
(For instance, let the folder name be CUSTOM_TOP).
Registering the report in apps.
12. Open Oracle app, Go to the Application developer module -> Concurrent
13. Once the form opens, enter 'Executable', 'Short name' (The short is important as the same
short name used be used later).
14. Enter 'application' - Folder name to which we moved the .rdf file thro' winSCP.
15. Enter Execution method as - 'Oracle Reports'.
16. Enter 'execution file name' - EMP_detail (enter same as the .rdf file name).
17. Copy the short name and save the form and close the form.
18. Navigate to the 'Program' under concurrent.
19. Enter Program, short name, Application (enter any name).
20. Enter 'name (executable)' - paste the copied short name.
21. Select the desired output format. Generally, we select .pdf option.
22. Save the form.
