Query to Display Module Wise Reports in oracle apps

/* Formatted on 11/14/2016 3:47:18 PM (QP5 v5.114.809.3010) */
  SELECT   fa.application_short_name,
           fcpv.user_concurrent_program_name,
           description,
           DECODE (fcpv.execution_method_code,
                   'B', 'Request Set Stage Function',
                   'Q', 'SQL*Plus',
                   'H', 'Host',
                   'L', 'SQL*Loader',
                   'A', 'Spawned',
                   'I', 'PL/SQL Stored Procedure',
                   'P', 'Oracle Reports',
                   'S', 'Immediate',
                   fcpv.execution_method_code)
              exe_method,
           output_file_type,
           program_type,
           printer_name,
           minimum_width,
           minimum_length,
           concurrent_program_name,
           concurrent_program_id
    FROM   fnd_concurrent_programs_vl fcpv, fnd_application fa
   WHERE   fcpv.application_id = fa.application_id
ORDER BY   1


      ********************************************************************
 Module Wise Count:-

/* Formatted on 11/14/2016 3:49:03 PM (QP5 v5.114.809.3010) */
  SELECT   fa.application_short_name,
           DECODE (fcpv.execution_method_code,
                   'B', 'Request Set Stage Function',
                   'Q', 'SQL*Plus',
                   'H', 'Host',
                   'L', 'SQL*Loader',
                   'A', 'Spawned',
                   'I', 'PL/SQL Stored Procedure',
                   'P', 'Oracle Reports',
                   'S', 'Immediate',
                   fcpv.execution_method_code)
              exe_method,
           COUNT (concurrent_program_id) COUNT
    FROM   fnd_concurrent_programs_vl fcpv, fnd_application fa
   WHERE   fcpv.application_id = fa.application_id
GROUP BY   fa.application_short_name, fcpv.execution_method_code
ORDER BY   1

Creditor Combine Report

/* Formatted on 7/4/2014 3:23:49 PM (QP5 v5.115.810.9015) */
SELECT vendor_type_lookup_code,
       org_id,
       vendor_num,
       vendor_name,
       vendor_site_code,
       code_combination,
       NVL (SUM(DECODE (invoice_type_lookup_code,
                        'CREDIT',
                        invoice_amount + (paid_amount + adusted_amount),
                        'DEBIT',
                        invoice_amount + (paid_amount + adusted_amount),
                        'STANDARD',
                        invoice_amount + (paid_amount + adusted_amount)
                )),
            0
       )
       + NVL (SUM(DECODE (invoice_type_lookup_code,
                          'PREPAYMENT',
                          (invoice_amount + (paid_amount + adjusted_amount))
                  )),
              0
         )
          "AMOUNT",
       NVL (SUM(DECODE (invoice_type_lookup_code,
                        'CREDIT',
                        invoice_amount + (paid_amount + adusted_amount),
                        'DEBIT',
                        invoice_amount + (paid_amount + adusted_amount),
                        'STANDARD',
                        invoice_amount + (paid_amount + adusted_amount)
                )),
            0
       )
          "LIABILITY",
       NVL (SUM(DECODE (invoice_type_lookup_code,
                        'PREPAYMENT',
                        (invoice_amount + (paid_amount + adjusted_amount))
                )),
            0
       )
          "PREPAYMENT"
FROM (SELECT DISTINCT
             ai.vendor_id,
             aps.vendor_name,
             aps.segment1 "VENDOR_NUM",
             aps.vendor_type_lookup_code,
             apss1.vendor_site_code,
             ai.org_id,
             ai.invoice_id,
             ai.invoice_num,
             ai.invoice_date,
             ai.invoice_type_lookup_code,
             ROUND (DECODE (ai.invoice_type_lookup_code,
                            'PREPAYMENT',
                            ai.invoice_amount * NVL (ai.exchange_rate, 1),
                            ai.invoice_amount * NVL (ai.exchange_rate, 1) * -1
                    ),
                    2
             )
                invoice_amount,
             ai.payment_status_flag,
             NVL ( (SELECT ROUND (SUM(NVL (aip.amount, 0)
                                      * NVL (ai.exchange_rate, 1)),
                                  2
                           )
                              amount_paid
                    FROM apps.ap_invoice_payments_all aip
                    WHERE     aip.invoice_id = ai.invoice_id
                          AND TRUNC (aip.accounting_date) <= :p89_date
                          AND ai.invoice_type_lookup_code <> 'PREPAYMENT'),
                  0
             )
                paid_amount,
             NVL (cc.code_combination,
                  (SELECT gk.concatenated_segments
                   FROM apps.gl_code_combinations_kfv gk
                   WHERE gk.code_combination_id =
                            ai.accts_pay_code_combination_id)
             )
                code_combination,
             ROUND ( (SELECT NVL (SUM (amount) * -1, 0)
                      FROM apps.ap_invoice_lines_all aia
                      WHERE     aia.invoice_id = ai.invoice_id
                            AND aia.org_id = ai.org_id
                            AND aia.line_type_lookup_code = 'PREPAY'
                            AND TRUNC (aia.accounting_date) <= :p89_date)
                    * NVL (ai.exchange_rate, 1),
                    2
             )
                adusted_amount,
             ROUND ( (SELECT NVL (SUM (amount), 0)
                      FROM apps.ap_invoice_lines_all aim
                      WHERE     aim.prepay_invoice_id = ai.invoice_id
                            AND aim.org_id = ai.org_id
                            AND TRUNC (aim.accounting_date) <= :p89_date)
                    * NVL (ai.exchange_rate, 1),
                    2
             )
                adjusted_amount
      FROM apps.ap_invoices_all ai,
           apps.ap_suppliers aps,
           apps.ap_supplier_sites_all apss1,
           apps.ap_invoice_distributions_all ad,
           apps.ap_invoice_lines_all ail,
           (SELECT DISTINCT
                   xd.applied_to_source_id_num_1,
                   DECODE (xal.accounting_class_code,
                      'PREPAID_EXPENSE', 'PREPAYMENT',
                      xal.accounting_class_code)
                      "CODE",
                      gcc.segment1
                   || '.'
                   || gcc.segment2
                   || '.'
                   || gcc.segment3
                   || '.'
                   || gcc.segment4
                   || '.'
                   || gcc.segment5
                   || '.'
                   || gcc.segment6
                   || '.'
                   || gcc.segment7
                      code_combination
            FROM apps.xla_ae_lines xal,
                 apps.xla_distribution_links xd,
                 apps.gl_code_combinations gcc
            WHERE xd.ae_header_id = xal.ae_header_id
                  AND xd.ae_line_num = xal.ae_line_num
                  AND xal.accounting_class_code IN
                           ('LIABILITY', 'PREPAID_EXPENSE')
                  AND xal.code_combination_id = gcc.code_combination_id) cc
      WHERE     ai.invoice_id = ad.invoice_id
            AND ad.match_status_flag = 'A'
            AND ai.vendor_id = aps.vendor_id
            AND ai.vendor_id = apss1.vendor_id
            AND ai.vendor_site_id = apss1.vendor_site_id
            AND ai.org_id = apss1.org_id
            AND ai.invoice_id = cc.applied_to_source_id_num_1(+)
            AND ai.invoice_id = ail.invoice_id
            AND ai.org_id = ail.org_id
            AND DECODE (ai.invoice_type_lookup_code,
                  'PREPAYMENT', ai.invoice_type_lookup_code,
                  'LIABILITY') = cc.code(+)
            AND ad.line_type_lookup_code <> 'AWT'
            AND ai.org_id = :p89_org_id
            AND NOT (ai.invoice_type_lookup_code = 'PREPAYMENT'
                     AND payment_status_flag = 'N')
            AND TRUNC (ad.accounting_date) <= :p89_date
      ORDER BY aps.segment1)
GROUP BY vendor_type_lookup_code,
         org_id,
         vendor_num,
         vendor_name,
         vendor_site_code,
         code_combination

How to register the oracle reports in oracle apps

Steps

1. Concurrent executable creations.
2. Concurrent program definitions & adding parameters.
3. Attach this program into request group.
4. Develop the oracle reports as per requirements.
5. Move the reports into $XXCUSTOM_TOP/reports/US.
6. Run and verify the report output. (concurrent program)

Steps in detail

1. Concurrent executable creations.
     Navigation: Application Developer > Concurrent > Executable

Enter the valid values for the “Executable, short name, application, execution method , execution file name ”

Execution methods
1. Oracle Reports – used for the RDF reports.
2. Host – used for shell scripts, basically the language of the host operating system.
3. PL/SQL Stored procedure – used to run the stored procedure through oracle applications.
4. SQL*Loader – used to run the sql loader programs.
5. SQL*Plus - used to run the anonymous PL/SQL blocks. It will get executed in the same fashion as you are running on SQL Plus.
6. Java Stored Procedure – The execution file is a Java stored procedure.
7. Java Concurrent Program – Used for program written in Java.
8. Spawned – used for c or pro*c Program. Mainly used by standard oracle interfaces.
9. Perl Concurrent Program – used for programs written in CGI Perl.
10. Request Set Stage Function – PL/SQL stored function that can be used to calculate the completion statuses of request set stages.
11. Immediate – execution file is a program written to run as subroutine of the concurrent manager. Oracle doesn’t recommend use of this executable type.
12. Multi-Language function – execution file is an MLS function that supports running concurrent program in multiple languages.

PL/SQL Stored Procedure, Oracle Reports, Host, SQL*loader, SQL*Plus are the most commonly used executable types.

2. Concurrent program definitions & adding parameters.
    Navigation: Application Developer > Concurrent > program

Token (nothing but report parameter name)

3. Attach this program into appropriaterequest group.

4. Develop the oracle reports as per requirements.

5. Move the reports into $XXCUSTOM_TOP/reports/US.

6. Run and verify the report output. (concurrent program)

Summary column vs. Formula Column vs. Placeholder Column

We use these columns in our oracle reports, but always have few doubts about which column to use for what purposes. Here are few brief differences among these columns and the purpose of their use in oracle reports.

Summary column:

It summarizes another column and can recalculate for each record in a specified group. The following properties apply specifically to summary columns:

• Function: The calculation to be performed on the values of the column specified in Source.
• Source: The name of the column whose values are to be summarized.
• Reset At: The group at which the summary column value resets to zero.
• Compute At: The group for which a % of Total summary column is computed.

The datatype of a summary column depends on the data type of the source of the summary. If you change the data type of the source column, the datatype of the summary also changes. The Report Wizard does not support page summaries. If you select a page summary in the Field tab of the Report Wizard, an error message appears.

Formula Column:

A formula column performs a user-defined computation on the data of one or more other columns. A formula column executes a PL/SQL function and must return a value. The value can be Character, Number, or Date and returned value must match data type.

Placeholder Column:

A placeholder column is an empty container at design time. The placeholder can hold a value at run time that has been calculated and placed into it by PL/SQL code from another object.Using placeholder columns, you can:

• Populate multiple columns from one piece of code. You can calculate several values in one block of PL/SQL code in a formula column and assign each value to a different placeholder column. Thus, you create and maintain only one program unit instead of many.
• Store a temporary value for future reference. For example, store the current maximum salary as records are retrieved.

A Scenario:

The goal is to design a salary report of all employees. The aim of the report is to:

• Calculate and temporarily store the name of the employee who earns the highest salary in the company.
• Display the highest earner and the maximum salary once at the beginning of the report.

For this report, you need to create the following columns:

• A summary to show the maximum salary for the company.
• A placeholder to contain the highest earner’s name at run time.
• A formula to:
  • Compare each employee salary with the maximum salary.
  • Populate the placeholder with the employee name if salary equals maximum salary.

User Exits in Oracle Reports

User Exits in Oracle Reports

We can integrate Oracle reports with Oracle Application Object Library, and run them as concurrent programs from your forms or through standard request submission.
These are the user exits available in Oracle Reports that makes AOL integration.

FND SRWINIT
FND SRWEXIT
FND FORMAT_CURRENCY
FND FLEXIDVAL
FND FLEXSQL
FND SRWINIT

• This is a User Exit which sets your profile option values and allows Oracle AOL user exits to detect that they have been called by oracle repots.
• It must be included if one is using any ORACLE APPLICATION OBJECT LIBRARY features in his report (such as concurrent processing).
• FND SRWINIT also allows your report to use the correct organization automatically.
• Can be used in BEFORE-REPORT Trigger.
• Syntax is : SRW.USER_EXIT(‘FND SRWINIT’);

FND SRWEXIT

• This user exit ensures that all the memory allocated for AOL user exits has been freed up properly.
• Can be used in AFTER- REPORT Trigger.
• Syntax is : SRW.USER_EXIT(‘FND SRWEXIT’);

FND FORMAT_CURRENCY

• To format the currency amount dynamically depending upon the precision of the actual currency value, standard precision, users profile values and location (country) of the site.
• You obtain the currency value from the database into an Oracle Reports column.
• Define another Oracle Reports column, a formula column of type CHAR, which executes the FORMAT_CURRENCY user exit to format the currency value.
• A displayed field has this formula column as its source so that the formatted value is automatically copied into the field for display.

Syntax:

FND FORMAT_CURRENCY
CODE=:column containing currency
DISPLAY_WIDTH=field width for display
AMOUNT=:source column name
DISPLAY=:display column name
[MINIMUM_PRECISION=:P_MIN_PRECISION]
[PRECISION={STANDARD|EXTENDED}]
[DISPLAY_SCALING_FACTOR=:P_SCALING_FACTOR]

FND FLEXSQL
Call this user exit to create a SQL fragment usable by your report to tailor your SELECT statement that retrieves flexfield values. This fragment allows you to SELECT flexfield values or to create a WHERE, ORDER BY, GROUP BY, or HAVING clause to limit or sort the flexfield values returned by your SELECT statement. You call this user exit once for each fragment you need for your select statement. You define all flexfield columns in your report as type CHARACTER even though your table may use NUMBER or DATE or some other datatype.
Syntax:

FND FLEXSQL
CODE=”flexfield code”
APPL_SHORT_NAME=”application short name”
OUTPUT=”:output lexical parameter name”
MODE=”{ SELECT | WHERE | HAVING | ORDER BY}”
[DISPLAY="{ALL | flexfield qualifier | segment number}"]
[SHOWDEPSEG="{Y | N}"]
[NUM=":structure defining lexical" | MULTINUM="{Y | N}"]
[TABLEALIAS="code combination table alias"]
[OPERATOR="{ = | < | > | <= | >= | != | "||" | BETWEEN | QBE}"]
[OPERAND1=":input parameter or value"]
[OPERAND2=":input parameter or value"]

FND FLEXIDVAL
Call this user exit to populate fields for display. You pass the key flexfields data retrieved by the query into this exit from the formula column. With this exit you display values, descriptions and prompts by passing appropriate token (any one of VALUE, DESCRIPTION, APROMPT or LPROMPT).
Syntax:

FND FLEXIDVAL
CODE=”flexfield code”
APPL_SHORT_NAME=”application short name”
DATA=”:source column name”
[NUM=":structure defining source column/lexical"]
[DISPLAY="{ALL|flexfield qualifier|segment number}"]
[IDISPLAY="{ALL|flexfield qualifier|segment
number}"]
[SHOWDEPSEG="{Y | N}"]
[VALUE=":output column name"]
[DESCRIPTION=":output column name"]
[APROMPT=":output column name"]
[LPROMPT=":output column name"]
[PADDED_VALUE=":output column name"]
[SECURITY=":column name"]

Calling a Report from a Form in oracle apps

Using RUN_PRODUCT Built-in we can call a Report from a Form.
It will take 7 Parameters as follows
1) Product name :: REPORT/GRAPHICS
2) Module name  :: Report name
3) Communication Mode :: Synchronous /Asynchronous
SYN :: we cannot work with the Form until and unless you closes the Report.
ASYN:: We can work with Form & Reprot (Navigate)
4) Exec :: Batch/Run time
Batch :: it will fetch all records at a time
Run time :: It will fetch only one Record at a time
5) File name :: database/Filesystem
6) Parameters :: Report Parameters
7) Display :: For Graphics (Mostly null)
Ex :: If we have :p_deptno parameter then we can call as Follows
RUN_PRODUCT (‘REPORT’,’C:TEST1.rep’,SYN,BATCH,DATABASE,p_deptno,’ ‘);

How Create a report with Lexical Parameter in oracle Reports

Lexical parameter is used to replace a specific ‘String’ in sql statement at runtime.

• It can be created with ‘&’ at runtime.
• String to  replace can be handled in ‘After Parameter Form’trigger.
• Used in SQL only.
• Used in all Clauses.

In this example we are going to retrieve the employees who hired in certain period of time i.e.
Step 1: Open Reports builder and connect the database, go to the data model of a new report and write a query as bellow

Press OK, system will show that ‘Query has created the lexical parameter’

Step 2: Now create report using this query. To do this right click on ‘Q_1’ and select ‘Report Wizard’ shown below









Step 3: Provide the dynamic parameter value as bellow


you can also use WHERE hirdate > ’01-Jan-2012′ OR WHERE hirdate = ’01-Jan-2012′ WHERE hirdate BETWEEN ’01-Jan-2012′ AND ’01-Jan-2013′

About these ads

SRW Package in Oracle Report

SRW (Sql Report Writer) Package is a built in package in Oracle Reports Builder. It is a collection of PL/SQL constructs that include many functions, procedures, and exceptions you can reference in any of your libraries or reports.
The PL/SQL provided by the SRW package enables you to perform such actions as change the formatting of fields, run reports from within other reports, create customized messages to display in the event of report error, and execute SQL statements. There are nearly 70 functions, procedures, and exceptions are there in this package. Here I am giving brief information and uses of few important functions, procedures, and exceptions.

SRW.MESSAGE:

It is a Procedure that displays a message with the message number and text that you specify. It is mainly used to debug a report in Reports Builder.
SRW.MESSAGE(msg_number NUMBER, msg_text CHAR);
Example:

function foo return boolean is begin if :sal < 0 then SRW.MESSAGE(100, 'Found a negative salary. Check the EMP table.'); raise SRW.PROGRAM_ABORT; else :bonus := :sal * .01; end if; return(true); end;

function foo return boolean is begin   if :sal < 0 then     SRW.MESSAGE(100, 'Found a negative salary. Check the EMP table.');     raise SRW.PROGRAM_ABORT;   else     :bonus := :sal * .01;   end if;   return(true); end;

SRW.PROGRAM_ABORT:
This exception stops the report execution and raises the following error message: REP-1419: PL/SQL program aborted. SRW.PROGRAM_ABORT stops report execution when you raise it.
SRW.DO_SQL:

This procedure executes the specified SQL statement from within Reports Builder. The SQL statement can be DDL (statements that define data), or DML (statements that manipulate data). DML statements are usually faster when they are in PL/SQL, instead of in SRW.DO_SQL.
Since you cannot perform DDL statements in PL/SQL, the SRW.DO_SQL packaged procedure is especially useful for performing them within Reports Builder.
Example:

FUNCTION CREATETABLE RETURN BOOLEAN IS BEGIN SRW.DO_SQL('CREATE TABLE TEST_EMP (EMPNO NUMBER NOT NULL PRIMARY KEY, SAL NUMBER (10,2)) PCTFREE 5 PCTUSED 75'); RETURN (TRUE); EXCEPTION WHEN SRW.DO_SQL_FAILURE THEN SRW.MESSAGE(100, 'ERROR WHILE CREATING TEST_EMP TABLE.'); RAISE SRW.PROGRAM_ABORT; END;

FUNCTION CREATETABLE RETURN BOOLEAN IS BEGIN   SRW.DO_SQL('CREATE TABLE TEST_EMP (EMPNO NUMBER NOT NULL     PRIMARY KEY, SAL NUMBER (10,2)) PCTFREE 5 PCTUSED 75');     RETURN (TRUE);    EXCEPTION     WHEN SRW.DO_SQL_FAILURE  THEN     SRW.MESSAGE(100, 'ERROR WHILE CREATING TEST_EMP TABLE.');     RAISE     SRW.PROGRAM_ABORT; END;

SRW.DO_SQL_FAILURE:

Reports Builder raises this exception when the SRW.DO_SQL packaged procedure fails. This exception stops the report execution and raises the following error message:
REP-1425: Error running DO_SQL package – REP-msg ORA-msg.

SRW.GET_REPORT_NAME:

This function returns the file name of the report being executed.
SRW.GET_REPORT_NAME (report_name);
Example:

function AfterPForm return boolean is my_var varchar2(80); BEGIN SRW.GET_REPORT_NAME (my_var); SRW.MESSAGE(0,'Report Filename = '||my_var); RETURN (TRUE); END;

function AfterPForm return boolean is     my_var varchar2(80);   BEGIN     SRW.GET_REPORT_NAME (my_var);     SRW.MESSAGE(0,'Report Filename = '||my_var);    RETURN (TRUE);   END;

SRW.RUN_REPORT:

This procedure synchronously executes the specified report within the context of the currently running report.
SRW.RUN_REPORT (“report=test.rdf … “)

SRW.SET_FIELD:

This procedure sets the value of a character, number, or date field. This is useful when you want to conditionally change a field’s value.
SRW.SET_FIELD (object_id, text CHAR | number NUM | date DATE);
Example:
Suppose you want to conditionally change the number of a field, based on each employee’s salary. In the format trigger for the field, you could type the following:

FUNCTION CHGFIELD RETURN BOOLEAN IS TMP NUMBER; BEGIN if :sal >= 5000 then tmp := :sal * 1.10; srw.set_field (0, tmp); else srw.set_field (0, 4000); end if; RETURN (TRUE); END;

FUNCTION CHGFIELD RETURN BOOLEAN IS TMP NUMBER; BEGIN   if :sal >= 5000 then   tmp := :sal * 1.10;   srw.set_field (0, tmp); else   srw.set_field (0, 4000); end if; RETURN (TRUE); END;

SRW.SET_FIELD should be used only to change the contents of a field’s datatype, not change the field to a different datatype.

Others in Brief:

• SRW.SET_FONT_FACE: This procedure specifies font face for a CHAR, DATE, or NUMBER field. SRW.SET_FONT_FACE(‘arial’);
• SRW.SET_FONT_SIZE: This procedure specifies font size for a CHAR, DATE, or NUMBER field. SRW.SET_FONT_SIZE(10);
• SRW.SET_FONT_STYLE: This procedure specifies font style for a CHAR, DATE, or NUMBER field. SRW.SET_FONT_STYLE(SRW.ITALIC_STYLE);
• SRW.SET_FORMAT_MASK: This procedure specifies the format mask for the DATE or NUMBER field. SRW.SET_FORMAT_MASK(‘mask’);
• SRW.SET_TEXT_COLOR: This procedure specifies the global text color of the CHAR, DATE, or NUMBER field. SRW.SET_TEXT_COLOR(‘color’);

If you want to design similar functionality as below in your report parameter, do the below steps.

1] Design two parameters to capture Accounting Flexfield From and To and use them in your report. Also set the default values.

Here use the value set: XLA_SRS_ACCOUNTING_FLEXFIELD
2] XLA_SRS_ACCOUNTING_FLEXFIELD valueset is designed as below:

Edit:

FND POPIDR APPL_SHORT_NAME=”SQLGL” CODE=”GL#”
  NUM=:$FLEX$.XLA_SRS_CHART_OF_ACCOUNTS REQUIRED=”Y”
  VALIDATE=”NONE” SEG=”:!VALUE” DESC=”:!MEANING”
  NAVIGATE=”!DIR” VDATE=””

Validate:

FND VALIDR APPL_SHORT_NAME=”SQLGL” CODE=”GL#”
  NUM=:$FLEX$.XLA_SRS_CHART_OF_ACCOUNTS VALIDATE=”NONE”
  REQUIRED=”Y” DESC=”:!MEANING” SEG=”:!VALUE” VDATE=””

Report regiser steps in oracle apps

Detailed Explanations and Step by Step Report Registration Tasks
Requirements:
1.     Oracle Applications (11i or R12) Access
2.     Oracle Reports Builder(6i or 10g)
3.     WinSCP Tool or ftp tool like ssh or putty
- WinSCP is an open source free SFTP client and FTP client for Windows. Legacy SCP protocol is also supported. Its main function is safe copying of files between a local and a remote computer.
- Download option of WinSCP Tool was available the following link http://winscp.net/eng/download.php#download2
 FTP the Report to Remote Server

Step1:
- Develop the report in Client Windows PC using Oracle report Builder and save the name as (.rdf) Extension for example (TESTREPORT.rdf.) (or)
- Any other report other than .rdf like sql report  to register in Oracle Application 11i or R12.
Step2:
-         Open the winSCP tool, The Following window is opening,

In this Window, Enter the Host name of your Oracle Application
-         And Enter the Username & Password, then click the Login button.

-         In Remote system window, open the report’s (.rdf) current folder (appltop/custom/reports/us)
-         Then Move (copy) the developed Report (TEXTREPORT.rdf) from local system to Remote system (path like $CUSTOM_TOP/reports/US).
- Move the Next Step, Once FTP of report Completed as above.

Registering the Reports into Oracle Application

Step 1:
Registering a Executable
Navigation – Application Developer ->Concurrent->Executable
Now, the following window appearing on the Screen.

Enter the following fields,
Executable name: TEST REPORT  (Whichever Name you want to mention the Executable or in this case to reflect TESTREPORT.rdf) (Mandatory Field)
Short Name: TEST_REPORT (Enter Unique value without space) (Mandatory Field)
Application: Application Object Library (or suitable Application name)  (Mandatory Field)
Description: This for Test Report ( Non Mandatory Field)
Execution Method: Select the “Oracle Reports” from List of Values.
Execution File Name: TESTREPORT (Enter the File name in Upper Case without extension of .rdf that is TESTREPORT.rdf without .rdf )
Save It through File -> Save or Press the Save Icon.
Step 2:
Creating a Program Associate with an Executable
Navigation – Application Developer  Responsibility ->Concurrent->Program
Now this window appearing on the Screen.

Enter the following fields,
Program Name: Report for TEST (Enter the Report name whatever you want?)
Short Name: TEST_FOR_REPORT ( Enter the Unique name without Space for this Report)
Executable name: TEST REPORT  (Executable Name mentioned by you in previous step/Window for Executable Registration) (Mandatory Field)
Output Format: PDF (Select the Output format from the List of Values)
Output Style: Landscape ( Select the style from the available List of Values)
Save it Through File -> save or Click the Save Icon,
If Reports have a Parameter Values, Then You should mention the parameter values through the following way,
Click the Parameter button from bottom of that form, Now one more window will opening,

In this Window,
Enter the Seq value of Parameter like 10,20,…
Enter the Parameter name without Space
Please note that in this current example we have not passed any parameters as this report does not have any report parameters.
In Validation Part, Assign the Value set for this Parameter from validation List,
In Display Part, Enter Prompt name and Token Value of the Parameter,
Save it Through File -> save or Click the Save Icon,

Assign the Concurrent Program to Request Group
Step 1:
Navigation – System Administrator Responsibility ->  Security ->  Responsibility -> Request
The below window appears on the Screen.

In this Window,
Select the Request Group (name whichever you want)
After that move the Cursor into Name field in Requests Part, then select file-> New(assuming that there are already concurrent programs associated in this already existing Request Group), to create a new row then enter registered report program or the above Concurrent Program name in this field. Please note that choose Type as “Program”.
Then Save it through File à save.

Assign the Request Group to Responsibility

Navigation – System Administrator -> Security->Responsibility->Define

Click on Define button and you will see following screen:
Please note that Request Group created in the previous step has been assigned to the Responsibility.

Trial Balance Report Output mismatched egin_balance and end_balance amounts in oracle apps

/* Formatted on 11/16/2016 1:21:22 PM (QP5 v5.114.809.3010) */
  SELECT   GCC.SEGMENT5 "ACCOUNT",
           FND.DESCRIPTION "DESCRIPTION",
           GJH.JE_SOURCE "SOURCE",
           (SELECT   SUM (NVL (GJL2.ACCOUNTED_DR, 0))
                     - SUM (NVL (GJL2.ACCOUNTED_CR, 0))
              FROM   GL_JE_LINES GJL2, GL_JE_HEADERS GJH2
             WHERE       1 = 1
                     AND GJL2.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
                     AND GJL2.LEDGER_ID = 2021
                     AND TRUNC (GJL2.EFFECTIVE_DATE) <
                           (SELECT   TRUNC (START_DATE)
                              FROM   GL_PERIODS
                             WHERE   PERIOD_NAME = 'Apr-16')
                     AND GJL2.JE_HEADER_ID = GJH2.JE_HEADER_ID
                     AND GJH2.ACTUAL_FLAG = 'A'
                     AND GJH2.JE_SOURCE != 'Consolidation'
                     AND GJH2.LEDGER_ID = GJL2.LEDGER_ID
                     AND GJH2.JE_SOURCE = GJH.JE_SOURCE
                     AND GJL2.STATUS = 'P')
              "BEGIN_BALANCE",
           (  SELECT   SUM (NVL (GJL1.ACCOUNTED_DR, 0))
                FROM   GL_JE_LINES GJL1, GL_JE_HEADERS GJH1
               WHERE       1 = 1
                       AND GJL1.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
                       AND GJL1.LEDGER_ID = 2021
                       AND GJH1.LEDGER_ID = GJL1.LEDGER_ID
                       AND GJH1.ACTUAL_FLAG = 'A'
                       AND GJH1.JE_SOURCE != 'Consolidation'
                       AND GJH1.JE_HEADER_ID = GJL1.JE_HEADER_ID
                       AND TRUNC (GJL1.EFFECTIVE_DATE) BETWEEN (SELECT   TRUNC(START_DATE)
                                                                  FROM   GL_PERIODS
                                                                 WHERE   PERIOD_SET_NAME =
                                                                            'CORPORATE'
                                                                         AND PERIOD_NAME =
                                                                               'Apr-16')
                                                           AND  (SELECT   TRUNC(END_DATE)
                                                                   FROM   GL_PERIODS
                                                                  WHERE   PERIOD_SET_NAME =
                                                                             'CORPORATE'
                                                                          AND PERIOD_NAME =
                                                                                'Apr-16')
                       AND GJH1.JE_SOURCE = GJH.JE_SOURCE
                       AND GJL1.STATUS = 'P'
            GROUP BY   GCC.SEGMENT5, FND.DESCRIPTION, GJH.JE_SOURCE)
              "DEBIT",
           (  SELECT   SUM (NVL (GJL1.ACCOUNTED_CR, 0))
                FROM   GL_JE_LINES GJL1, GL_JE_HEADERS GJH1
               WHERE       1 = 1
                       AND GJL1.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
                       AND GJL1.LEDGER_ID = 2021
                       AND GJH1.LEDGER_ID = GJL1.LEDGER_ID
                       AND GJH1.ACTUAL_FLAG = 'A'
                       AND GJH1.JE_SOURCE != 'Consolidation'
                       AND GJH1.JE_HEADER_ID = GJL1.JE_HEADER_ID
                       AND TRUNC (GJL1.EFFECTIVE_DATE) BETWEEN (SELECT   TRUNC(START_DATE)
                                                                  FROM   GL_PERIODS
                                                                 WHERE   PERIOD_SET_NAME =
                                                                            'CORPORATE'
                                                                         AND PERIOD_NAME =
                                                                               'Apr-16')
                                                           AND  (SELECT   TRUNC(END_DATE)
                                                                   FROM   GL_PERIODS
                                                                  WHERE   PERIOD_SET_NAME =
                                                                             'CORPORATE'
                                                                          AND PERIOD_NAME =
                                                                                'Apr-16')
                       AND GJH1.JE_SOURCE = GJH.JE_SOURCE
                       AND GJL1.STATUS = 'P'
            GROUP BY   GCC.SEGMENT5, FND.DESCRIPTION, GJH.JE_SOURCE)
              "CREDIT",
           (SELECT   SUM (NVL (GJL2.ACCOUNTED_DR, 0))
                     - SUM (NVL (GJL2.ACCOUNTED_CR, 0))
              FROM   GL_JE_LINES GJL2, GL_JE_HEADERS GJH2
             WHERE       1 = 1
                     AND GJL2.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
                     AND GJL2.LEDGER_ID = 2021
                     AND UPPER (GJL2.EFFECTIVE_DATE) <=
                           (SELECT   TRUNC (END_DATE)
                              FROM   GL_PERIODS
                             WHERE   PERIOD_NAME = 'Apr-16')
                     AND GJL2.JE_HEADER_ID = GJH2.JE_HEADER_ID
                     AND GJH2.ACTUAL_FLAG = 'A'
                     AND GJH2.JE_SOURCE != 'Consolidation'
                     AND GJH2.LEDGER_ID = GJL2.LEDGER_ID
                     AND GJH2.JE_SOURCE = GJH.JE_SOURCE
                     AND GJL2.STATUS = 'P')
              "END_BALANCE"
    FROM   GL_CODE_COMBINATIONS GCC,
           FND_FLEX_VALUES_VL FND,
           GL_JE_LINES GJL,
           GL_JE_HEADERS GJH
   WHERE       1 = 1
           AND GCC.CHART_OF_ACCOUNTS_ID = 50420
--           AND GCC.SEGMENT1 BETWEEN P_SEGMENT1_LOW AND P_SEGMENT1_HIGH
           AND GCC.SUMMARY_FLAG = 'N'
           AND GCC.TEMPLATE_ID IS NULL
           AND FND.FLEX_VALUE = GCC.SEGMENT5
           AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
           AND GJL.LEDGER_ID = 2021
           AND GJL.STATUS = 'P'
           AND GJH.CURRENCY_CODE = 'USD'
           AND GJH.LEDGER_ID = GJL.LEDGER_ID
           AND GJH.ACTUAL_FLAG = 'A'
           AND GJH.JE_SOURCE != 'Consolidation'
           AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
           --AND NVL2(P_SOURCE,GJH.JE_SOURCE,1) = NVL2(P_SOURCE,P_SOURCE,1)
           --ADDED ADDITIONAL LOGIC FOR RESTRICTING ADDITIONAL ACCOUNTS WHICH ARE NOT OCCURING IN TRIAL BALANCE REPORT
           AND (   (SELECT   SUM (BEGIN_BALANCE_DR) - SUM (BEGIN_BALANCE_CR)
                      FROM   GL_BALANCES
                     WHERE       CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
                             AND LEDGER_ID = GJL.LEDGER_ID
                             AND CURRENCY_CODE = 'USD'
                             AND PERIOD_NAME IN ('Apr-16')) != 0
                OR (SELECT   SUM (PERIOD_NET_DR)
                      FROM   GL_BALANCES
                     WHERE       CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
                             AND LEDGER_ID = GJL.LEDGER_ID
                             AND CURRENCY_CODE = 'USD'
                             AND PERIOD_NAME IN ('Apr-16')) != 0
                OR (SELECT   SUM (PERIOD_NET_CR)
                      FROM   GL_BALANCES
                     WHERE       CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
                             AND LEDGER_ID = GJL.LEDGER_ID
                             AND CURRENCY_CODE = 'USD'
                             AND PERIOD_NAME IN ('Apr-16')) != 0)
---------------------END OF ADDITIONAL LOGIC
GROUP BY   GCC.SEGMENT5,
           FND.DESCRIPTION,
           GJH.JE_SOURCE,
           GCC.CODE_COMBINATION_ID
ORDER BY   1;