Tuesday, January 10, 2017

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
select
trx.customer_trx_id customer_trx_id
,trx.trx_number trx_number
,trx.purchase_order purchase_order_number
,pjb.attribute1 BILLING_PERIOD
,to_char(trx.TRX_DATE,'YYYY-MM-DD') TRX_DATE
,t.name TERM_NAME 
,to_char(ps.due_date,'YYYY-MM-DD') DUE_DATE
,b_bill.account_number  BILL_TO_CUSTOMER_NUMBER
,b_bill_party.party_name BILL_TO_CUSTOMER_NAME
,a_bill_loc.address1 BILL_TO_ADDRESS1
,a_bill_loc.address2 BILL_TO_ADDRESS2
,a_bill_loc.address3 BILL_TO_ADDRESS3
,a_bill_loc.address4 BILL_TO_ADDRESS4
,a_bill_loc.city bill_to_city
,a_bill_loc.state bill_to_state
,a_bill_loc.province bill_to_province
,a_bill_loc.postal_code bill_to_postal_code
,a_bill_loc.country bill_to_country
,AR_BPA_UTILS_PKG.fn_get_contact_name(trx.bill_to_contact_id) CUSTOMER_CONTACT_NAME
,trx.SPECIAL_INSTRUCTIONS
FROM
ra_customer_trx_all  trx
,ar_payment_schedules_all  ps
,ra_terms t
,hz_cust_accounts b_bill
,hz_parties b_bill_party
,hz_cust_site_uses_all u_bill
,hz_cust_acct_sites_all a_bill
,hz_party_sites a_bill_ps        
,hz_locations a_bill_loc
where 1=1
AND trx.customer_trx_id =  PS.CUSTOMER_TRX_ID
AND trx.term_id = t.term_id
AND trx.bill_to_customer_id = b_bill.cust_account_id
ANd b_bill.party_id = b_bill_party.party_id
AND trx.bill_to_site_use_id = u_bill.site_use_id
AND u_bill.cust_acct_site_id = a_bill.cust_acct_site_id
AND a_bill.party_site_id = a_bill_ps.party_site_id
AND a_bill_ps.location_id = a_bill_loc.location_id
and u_bill.site_use_code    ='BILL_TO'
and b_bill.status = 'A'
and b_bill_party.status = 'A'
and u_bill.status = 'A'
and a_bill.status = 'A'
and a_bill_ps.status = 'A'
AND b_bill_party.party_name = NVL(:P_CUSTOMER_NAME,b_bill_party.party_name)
AND trx.trx_number = NVL(:P_TRX_NUMBER,trx.trx_number)
ORDER BY trx.trx_number


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
SELECT
c.customer_trx_id customer_trx_id_line
,trx.trx_number line_trx_number
,c.customer_trx_line_id customer_trx_line_id
,to_char(decode( c2.line_number, null, c.line_number, null)) LINE_NUMBER
,c.line_type line_type
,c.description line_description
,decode(c.line_type, 'TAX', null, to_char(nvl(c.quantity_invoiced, c.quantity_credited))) quantity
,to_char(c.unit_selling_price,fnd_currency.get_format_mask(trx.invoice_currency_code,40)) unit_price
,to_char(c.extended_amount,fnd_currency.get_format_mask(trx.invoice_currency_code,40)) extended_amount
from
ra_customer_trx_lines_all   c
,ra_customer_trx_lines_all  c2
,ra_customer_trx_all        trx
where 1=1
AND trx.customer_trx_id = c.customer_trx_id
AND trx.org_id = c.org_id
AND trx.complete_flag = 'Y'
and c.link_to_cust_trx_line_id = c2.customer_trx_line_id(+)
and c.org_id = c2.org_id(+)
ORDER BY 4



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
select distinct
hz.party_name
from
ra_customer_trx_all ra
,hz_parties hz
,hz_cust_accounts b_bill
where b_bill.party_id = hz.party_id
AND ra.bill_to_customer_id = b_bill.cust_account_id
and b_bill.status = 'A'
and hz.status = 'A'

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

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect