Reports

Q: Name the report triggers.


A: 1. Before Parameter 2. After Parameter 3. Before Report 4. Between Pages 5. After Report


Q: What are bind parameter and lexical parameter used for?


A: A bind reference replaces a single value or expression.To create a bind reference in a query, prefix the parameter name with a colon (:). A lexical reference is a text string and can replace any part of a SELECT statement, such as column names, the FROM clause, the WHERE clause, or the ORDER BY clause. To create a lexical reference in a query, prefix the parameter name with an ampersand (&).


USER EXIT is of five types

 

1. FND SRWINIT

2. FND SRWEXIT

3. FND FORMAT_CURRENCY

4. FND FLEXIDVAL

5. FND FLEXSQL

 

What are Oracle Report and Flex field Report-Writing steps?

  1.   Define before report trigger
  2.   Define after report trigger
  3.   Define required parameters
  4.   Define other parameter
  5.   Call FND FLEXSQL to populate data field
  6.   Call FND FLEXSQL to populate other parameters
  7.   Define report query or queries
  8.   Create formula columns
  9.   Populate formula columns with FND FLEXIDVAL
  10.   Create default report layout
  11.   Finish Report
 


Differences between D2k Report and Apps Report

 

(1) in D2k Report there is no fecility to schedule the program where as in Apps report we have that facility

 

(2) in D2k Report we can’t use User-Exits where as we can use in Apps Report… to add dff values and diff currencies.

 

(3) when Compare to D2k Report in Apps report we can have more fecility to give out put in Fonts and formats.

 

 

 

Oracle apps reports are same as D2k reports.Only thing is they use exits(specifically defined for ERP) and use them in report. D2K reports and Oracle Reports basically have no difference the main difference between them is only that simple D2k report run in any environment  and we always use Tables if we want to run a report in D2K

 

But if we want to run any Oracle Report then we need an environment which is given by an pre-defined function “USER EXIT” which is used in Oracle. USER EXIT gives an environment for D2K Connect to Oracle APPS and if we use this function then we need to pass a parameter named “P_CONC_REQUEST_ID” in “FND SRWINIT” USER EXIT .

 

What are the User PARAMETERS in the Reports?

 

              P_CONC_REQUEST_ID

              P_FLEX_VALUE

 

What are User Exits? What are different types of User Exits in Oracle Apps? Explain the types.

 

User exit is a C subroutine written by user and called by Oracle Forms to do special-purpose processing. Helps the User to exit from the current working environment.

 

Types of User exit:

 

FND SRWINIT: Sets your profile option values and allows Oracle AOL user exits to detect that they have been called by an Oracle Reports program

 

FND SRWEXIT: Ensures that all the memory allocated for Oracle AOL user exits has been freed up properly.

 

FND FORMAT_CURRENCY: Is used for MULTIPLE CURRNECY REPORTING.

 

 

FND FLEXIDVAL: This API is used for get descriptions from flexfields get input from

 

 

FND FLEXSQL: this API is used for get ELECT/WHERE/HAVING/ORDER BY/GROUP BY from flex field tables.

 

 

How to Use XML Publisher to Generate Oracle Reports in Excel and PDF

 

The prerequisites to use XML Publisher include:

  • XML Data
  • Report Template
  • Registration of Data Definition and Template at XML Publisher

XML Publisher will use Oracle Reports for only the data query portion and will ignore the defined report layout

  • Log into Oracle with System Administrator Responsibility
  • Navigate to > Concurrent > Program > Define
  • Search for the desired Oracle Report
  • Change the Output Format from the default ‘Text’ to ‘XML’
  • Take special note of the Program Short Name (you’ll need it later to register the data at XML Publisher)

Download the XML Publisher Desktop Utility

The XML Publisher Desktop Utility is a plug-in to Microsoft Word. To create the RTF template, you must first load the XML Data File by clicking on the Data button and selecting Load XML Data. To get a sample XML data file, you can run the Oracle seeded report and download the output. Once the data file is loaded, you can make references to the XML Data elements.

You can test your RTF template with the loaded XML Data File by click on the Preview button and selecting one of the supported output formats. Once satisfied with the created RTF Template, the final steps is to register both the Data Definition and the Template at XML Publisher.

Register the Data Definition:

  • Log into Oracle with XML Publisher Administrator responsibility
  • Navigate to > Home > Data Definitions
  • Click on the Create Data Definition button
  • The Code must be the Concurrent Program Shortname from above

Register the Template

  • From Data Definitions, Navigate to > Templates
  • Click on the Create Template button
  • The Data Definition value should be the Data Definition you registered above

When the Customer runs this configured report through the Concurrent Manager, the Customer will follow the same steps to run the standard Oracle Report by selecting the report in the Single Request Screen and entering the desired run parameters. Now, the Customer has one additional option to select the desired output format of the report. From the Single Request Screen, click on the Options button and select the desired output.


 

1. Created query through Report Builder and saved as XXCNREC02.rdf


2. Registered Concurrent Program Executable and Define as XML output.
 

Executable : XXCNREC02
Short Name : XXCNREC02
Application: Application Object Library
Execution Method : Oracle Reports
Execution File Name : XXCNREC02

Definition window :

Program : Received/Consumption Detailed Report
Executable & Short Name : XXCNREC02
Method : Oracle Reports
Output : XML


3. Attached to the request group i.e., ‘SYSTEM ADMINISTRATOR REPORTS’.


4. Run through SRS , got output then save as XML type in local drive
ie., XXCNREC02_25678.xml


5. Through MS Word, Load XML Data and selected XML type file which is created in step 4.


6. Insert /Table Form then created layout.


7. Using ‘XML Publishing Administrator’ responsibility

 

Data Definition-> Create Data Definition

Name : Received/Consumption Details
Application : Application Object Library
Code : XXCNREC02
Start Date : 17-Jul-07

Templates -> Create Template

Name : Received/Consumption Details
Code : XXCNREC02
Application : Application Object Library
Data Definition: Received/Consumption Details
Type : RTF

File : Received/Consumption Details
Language : English
Territory : India


8. Run the report through SRS with System Administrator Responsibility.
 

select Upon Completion – > Options button – > Preview then
shows error.

WARNING

Executing request completion options…

 

 

 

R2R stands for Record 2 Report

 

 i.e. Record Jounal entries in the General Ledger and Report to the Financial Managers using reporting tools such as hyperion.

 

Below modules come under R2R cycle

1) General Ledger and below could be part of R2R cycle as well.


2) Financial Consolidation Hub


3) Enterprise Planning and Budgeting.

 

 

Q: How do you print barcode in the reports?


A: By installing the Barcode Font and using the Chart field in the Layout.


Q: What are the different sections in the Layout?


A: Header, Main, Trailer


Q: What is SRW package and some procedures in SRW?


A: It is the standard reports package and it has many procedures like USER_EXITS, DO_SQL, RUN_REPORT, MESSAGE,TRACE, BREAK, SET_ATTR.


Q: What are user exits in reports and name a few?


A: User exits provided a way to pass control from Reports Builder to a program you have written, which performs some function, and then returns control to Reports Builder. Ex: SRW.DO_SQL, SRW.USER_EXIT


Q: How do you display only one record on each page in a report?


A: Give Page Break in the Format trigger of the repeating frame.


Q: How do you write the report output to Excel file or text file?


A: 1.Use TEXT_IO package 2.Use SPOOL in After Report trigger 3.Use UTL Package


Q: Give an example of the implementation of “between pages trigger” in reports.


A: The total printed at the bottom of first page has to be carried to the top of the next page.


 

Q: Where in reports do you set the context information (like org_id)?


A: SRW.INIT


Q: What is Anchor in reports?


A: Anchors fasten an edge of one object to an edge of another object, ensuring that they maintain their positions relative to its parent.


 

Q: What is the difference between Conditional Formatting and format trigger?


A: Both provide the same functionality, used to format the output based on particular conditions. Format triggers provide a wide variety of options when compared to conditional formatting(GUI). In format Triggers we have the option to write PL/SQL code where as conditional formatting is GUI based which provide limited options.


Q: How do you call a concurrent program or another report from a report?


A: Use FND_SUBMIT.REQUEST() to call a concurrent program or a report. Use SRW.RUN_REPORT() to run a report directly without registering it as a concurrent program.


Q: How do you mail the output of a report?


A: 1. Use UTL_SMTP (refer to Scripts tab for more details) 2. Use MAILX called in a shell script registered as a concurrent program with parameters File name and path.


Q: How can you grey out/ highlight/hide some records based on conditions in a report?


A: Use Conditional formatting


 

Q: Q: Give an example of the implementation of “between pages trigger” in reports.

A: The total printed at the bottom of first page has to be carried to the top of the next page.



Q: What is Report Busting?


A: Reports bursting offers you to deliver a single report to multiple destinations simultaneously. It offers you to create multiple reports out of one single report model.


For example, you can create just one employee report for all your departments and send an email with a PDF-attachment containing the report to each manager. Each report would contain only the relevant department information for that particular manager. Using the reports bursting functionality will reduce the overhead since you will only have a single data fetch and report format.


Q: Is it possible to change the margins for oracle report?


A: Yes.

 

 

 

Q: How do I locate my output file from my report?

 

A: Perform the following steps:

 

1. Determine the Concurrent Request Number The first step to determining the file name is to locate the concurrent request number.

Navigate: Help=>View My Requests.

The Request ID field contains the concurrent request number.

 

2. Open a Telnet Session

a. You will need to open an MS-Dos prompt.

b. Select the Start button

c. Select Programs

d. Select Command Prompt

e. At the Dos prompt, enter the following command:

telnet <your server name>

 

3. Log into the Server

You will be prompted for your login and password for the Server.  Enter

these values.

 

4. Set your environment variables if necessary.  You may need to have your System Administrator perform this function for you.

 

5. Determine the Output File Directory

 

There are two environment variables that may be used to set the default output directory:


APPLCSF

APPLOUT

 

You can determine which is being used by “echoing” the above variables.

 

One of these will return a value.

 

APPLCSF is Applications Commonly Shared Files.

 

a. echo $APPLCSF

 

b. You MAY see a returned value, for example:

 

/oraclelogs/f1070161/app

 

c. There is usually an associated /out directory located off of the particular directory.  In that case, the output directory would be:

 

/oraclelogs/f1070161/app/out

 

d. You may access this directory by typing the following command:

cd $APPLCSF/out

 

e. Verify the directory name by typing:

pwd

 

f. Result:

 

/oraclelogs/f1070161/app/out

 

Next, try using APPLOUT.  This variable assumes that the value will be associated with a product top directory.

 

a. echo $APPLOUT

 

b. You MAY see a returned value:

out

 

c. In this instance, the directory /out is located off of the Product top (AP_TOP). To access this directory,

 

type the following command:

 

cd $AP_TOP/out

 

d. Verify the directory name by typing:

pwd

 

e. Result:

/u13/appl/f1070161/ap/8.0.159/out

 

If a variable did not return a value when echoed, it is not being used.

 

 

How do I determine the version of my report?

 

============================================================

A:  The report version can be determined from the operating system by running the following commands:

 

UNIX:

————–

Release 10.7:

cd $AP_TOP/srw

strings –a APXT7F99.rdf | grep Header

 

 

Release 11:

cd $AP_TOP/reports

strings -a AP7T7F99.rdf | grep Header

 

 

 

 

Win NT:

————–

 

Release 10.7:

cd %AP_TOP/srw

find /I “Header” APXT7F99.rdf

 

 

Release 11:

cd %AP_TOP/reports

find /I “Header” APXT7F99.rdf

 

 

 

 

D2K

Apps Reports

No User Exit

User Exit

P_Conc_Request_id not Required

P_CONC_REQUEST_ID Mandatory Parameter

 

 

 

 

 

What is Format Trigger? Format trigger is used to print conditionally printing of Layout. Format trigger return Boolean values.

Qns: What do you mean by Anchoring in Reports? With the help of Anchoring Relative position of two objects will be fixed.

Qns: What are Reports Trigger and Their Firing Sequence?

§         BEFORE PARAMETER FORM

§         AFTER PARAMETER FORM

§         BEFORE REPORT

§         BETWEEN PAGES

§         AFTER REPORT

Qns: What do you mean by Confine Mode in Reports?

Ans : Confine Mode Means Locking of Frames. If Confine Mode is on then you can not move the Object outside the Frame. If it is off you can move the objects.



Qns: Whats is Difference between Formula Column, Summary and Placeholder Column.

Ans:

§         Formula Column: Formula Column is used to compute Values. Formula Column always returns the Values.


§         Summary Column: Summary Column is used for Calculating summary function like Sum, Max. Min, Count.


§         Placeholder Columns: Placeholder Column is used to place the Variables.




Qns: Whats is Difference between and Lexical and Bind Parameter.

Ans:

Bind Parameter

Lexical Parameter

With the help of Bind Parameter You Can Pass Values.

With the help of Lexical Parameter You can pass string. You can pass lexical parameter in Select, From , Where Clause

Denoted by Colon

Denoted by Ampersand(&) sign



Qns: How will you Print Conditionally Layout in reports.
Ans: With the help of Format trigger.

 

For example code like:

 

 

 

 

Qns: How will you call Concurrent Program through Reports.

 

Qns: Whats are User Exit in Reports?

Qns: In Which Folder You will put the Rerort on the Server?

Qns : How will you Register the Reports?

Qns : What do you Mean by Token in Reports.

Qns : What are Various Default Type in Concurrent Programs?

Qns : What do you incompatibility in Concurrent Programs?

Qns: How will you Capture ORG_ID Dynamically in Concurrent Programs.

Qns : Suppose I have two Parameter Start Date and End Date and I want My Start Date should be lower then End Date. How will you Validate.

Qns : What are Various Execution Method in Concurrent Programs in executables.

Qns : What is Difference between Application Name Defined in Concurrent Program Executables and  Concurrent Programs.

Qns : Whats is Mandatory Parameter to Register PL/SQL Procedure in Apps?

Qns: How will you Call Reports with in Reports?

Qns: What do you Mean by Incompatibility in Reports?

Qns : What is significance of Mandatory Parameter in P_CONC_REQUEST_ID?

 

Qns: How will you put Message in Log File? 

Qns : How will you put Message of Reports in LOG Files.

 

 

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: