Creating a Custom Report (Job) in Oracle Fusion Applications

1.      Log into the application and click on the Reports and Analysis.

Nav : Navigator àReports and Analysis

2.      Click on Browse Catalog  button

3.      Click on New > Data Model

4.      Click on the SQL Query in data set

Nav  : Data Set àDiagram àSQL Query

5.      Enter the query and click on ok button

Field	Value
Name	XXAA_AR_INVOICE_HDR
Data Source	ApplicationDB_FSCM
Type of SQL	Standard SQL
SQL Query

6.      Select the parameters and click on Ok

Parameters are created

7.      Change parameters prompt and reorder to display the parameter sequence while running the report in data model

8.      Change the group header.

Nav : Data Model à PropertiesàData Sets àXXAA_AR_INVOICE_HDR àMenu àProperties

9.      Enter the group name and click on Ok

Field	Value
*Group Name	G_HDR
Display Name	G_HDR

10.  Click on Save button save  the Data Model

11.  Save the data model in custom location

i.e. /Shared Folder/Custom/Financials/Receivables/Data Model

Field	Value
Save in	/Shared Folder/Custom/Financials/Receivables/Data Model
Name	XXAA_AR_INVOICE_DM
Description	Peloton Fixed Fee Invoice Data Model

12.  Create Data Set for Lines

Nav  : Data Set àDiagram àSQL Query

13.  Enter the SQL Query and click on ok

Field	Value
Name	XXAA_AR_INVOICE_LIN
Data Source	ApplicationDB_FSCM
Type of SQL	Standard SQL
SQL Query

14.  Change the group header.

Nav : Data Model à PropertiesàData Sets àXXAA_AR_INVOICE_LIN àMenu àProperties

15.  Enter the group name and click on Ok

Field	Value
*Group Name	G_LIN
Display Name	G_LIN

16.  Join the link between Header group(G_HDR) column(CUSTOMER_TRX_ID) and Line group(G_LIN) column(CUSTOMER_TRX_ID_LINE)

Click on the join field from Headers section and drag it on to the join field in lines.

17.  Create List of Values for (LOV) for Parameters if required.  Click on List of Values and click on New button

Nav : Data Model à PropertiesàList of Values àNew 

18.  Enter the LOV query

Field	Value
Name	Customer Name
Type	SQL Query
Data Source	ApplicationDB_FSCM
SQL Query

19.  Assign the LOV to parameter, Change the parameter type to Menu and select the LOV value

Field	Value
Parameter Type	Menu
List of Values	Customer Name

20.  Click on the Save button to save  the Data Model

21.  Now Click on View Data to see sample data.

22.  Enter the parameters and click on View and click on the Save As Sample Data button

23.  Click on Export button to save the sample XMl data in local system for developing the RTF file

Save the XML file

<?xml version="1.0" encoding="UTF-8"?>

<!--Generated by Oracle BI Publisher 11.1.1.7.0BI-FAREL11-BP -Dataengine,

<!-- Author : Raju Chinthapatla  -->    datamodel:_Custom_Financials_Receivables_Data_Model_XXAA_AR_INVOICE_DM_xdm -->

<XXAA_AR_INVOICE>

     <P_CUSTOMER_NAME>CDS Inc</P_CUSTOMER_NAME>

     <P_TRX_NUMBER/>

     <G_HDR>

          <CUSTOMER_TRX_ID>300000092619578</CUSTOMER_TRX_ID>

          <TRX_NUMBER>10408</TRX_NUMBER>

          <TRX_DATE>2014-09-23</TRX_DATE>

          <TERM_NAME>30 Net</TERM_NAME>

          <DUE_DATE>2014-10-23</DUE_DATE>

          <BILL_TO_CUSTOMER_NUMBER>80020</BILL_TO_CUSTOMER_NUMBER>

          <BILL_TO_CUSTOMER_NAME>CDS Inc</BILL_TO_CUSTOMER_NAME>

          <BILL_TO_ADDRESS1>205 LOFTY GROVE DR</BILL_TO_ADDRESS1>

          <BILL_TO_CITY>RANCHO PALOS VERDES</BILL_TO_CITY>

          <BILL_TO_STATE>CA</BILL_TO_STATE>

          <BILL_TO_POSTAL_CODE>90275</BILL_TO_POSTAL_CODE>

          <BILL_TO_COUNTRY>US</BILL_TO_COUNTRY>

          <CUSTOMER_CONTACT_NAME>Jane Fagan</CUSTOMER_CONTACT_NAME>

          <G_LIN>

              <CUSTOMER_TRX_ID_LINE>300000092619578</CUSTOMER_TRX_ID_LINE>

              <LINE_TRX_NUMBER>10408</LINE_TRX_NUMBER>

              <CUSTOMER_TRX_LINE_ID>300000092619579</CUSTOMER_TRX_LINE_ID>

              <LINE_NUMBER>1</LINE_NUMBER>

              <LINE_TYPE>LINE</LINE_TYPE>

              <LINE_DESCRIPTION>Standard Product</LINE_DESCRIPTION>

              <QUANTITY>1</QUANTITY>

              <UNIT_PRICE>1975.00</UNIT_PRICE>

              <EXTENDED_AMOUNT>1975.00</EXTENDED_AMOUNT>

          </G_LIN>

          <G_LIN>

              <CUSTOMER_TRX_ID_LINE>300000092619578</CUSTOMER_TRX_ID_LINE>

              <LINE_TRX_NUMBER>10408</LINE_TRX_NUMBER>

              <CUSTOMER_TRX_LINE_ID>296279</CUSTOMER_TRX_LINE_ID>

              <LINE_TYPE>TAX</LINE_TYPE>

              <EXTENDED_AMOUNT>49.38</EXTENDED_AMOUNT>

          </G_LIN>

          <G_LIN>

              <CUSTOMER_TRX_ID_LINE>300000092619578</CUSTOMER_TRX_ID_LINE>

              <LINE_TRX_NUMBER>10408</LINE_TRX_NUMBER>

              <CUSTOMER_TRX_LINE_ID>296278</CUSTOMER_TRX_LINE_ID>

              <LINE_TYPE>TAX</LINE_TYPE>

              <EXTENDED_AMOUNT>128.38</EXTENDED_AMOUNT>

          </G_LIN>

     </G_HDR>

</XXAA_AR_INVOICE>

24.  Click on Create Report.

25.  Select the Use Report Editor and click on Finish button

Field	Value
Data Model	/Custom/Financials/Receivables/Data Model/XXAA_AR_INVOICE_DM.xdm
Report	Use Report Editor

26.   Save the template in custom location

i.e. /Shared Folder/Custom/Financials/Receivables

Field	Value
Save in	/Shared Folder/Custom/Financials/Receivables
Name	XXAA AR Invoice Report
Description	XXAA AR Invoice Report

27.              Click on Upload button to upload the RTF file

28.              Enter the details and click on Upload button

Field	Value
Layout Name	XXAA_AR_INVOICE
Template Name	Select the template from local system
Type	RTF Template
Locale	English

29.              Click on View Report.

30. Enter the parameter and click on apply