Creation of a Simple report in oracle Fusion Application.

Login to Cloud application.

Navigate to Tools  --> Reports and Analytics.

 

Below Screen opens click on browse catalog.

Below screen opens

 

Click on new and choose Data Model.

 

We are considering source of Data Model as SQL Query.

 

Consider a simple Query for invoice details with on parameter.

SELECT INV.INVOICE_NUM INV_NUMBER,

  INV.INVOICE_TYPE_LOOKUP_CODE INV_TYPE,

  TO_CHAR(INV.INVOICE_DATE,'YYYY-MM-DD HH24:MI:SS','NLS_DATE_LANGUAGE=AMERICAN') INV_DATE,

  INV.DESCRIPTION INV_DEC,

  INV.INVOICE_AMOUNT INV_AMOUNT,

  INV.INVOICE_CURRENCY_CODE INV_CURR,

  PERSONNAME.DISPLAY_NAME AS REQUESTOR,

  INV.SOURCE SOURCE,

  TO_CHAR(INV.CREATION_DATE,'YYYY-MM-DD HH24:MI:SS','NLS_DATE_LANGUAGE=AMERICAN') CREATION_DATE,

  TO_CHAR(INV.LAST_UPDATE_DATE,'YYYY-MM-DD HH24:MI:SS','NLS_DATE_LANGUAGE=AMERICAN') LAST_UPDATED,

  PO.SEGMENT1 PO_NUM,

  LEGAL_E.NAME LEGAL_ENTITY,

  SUP.VENDOR_NAME SUPPLIER_NAME,

  SUP.SEGMENT1 SUPPLIER_NUMBER,

  SUP_SITE.VENDOR_SITE_CODE SUPPLIER_SITE,

  SUP_ADD.PARTY_SITE_NAME ADDRESS_NAME,

  ORG.NAME BUSS_UNIT

FROM AP_INVOICES_ALL INV,

  POZ_SUPPLIERS_V SUP,

  POZ_SUPPLIER_SITES_V SUP_SITE,

  POZ_SUPPLIER_ADDRESS_V SUP_ADD,

  PER_PERSON_NAMES_F_V PERSONNAME,

  PO_HEADERS_ALL PO,

  XLE_ENTITY_PROFILES LEGAL_E,

  HR_ORGANIZATION_UNITS ORG

WHERE 1                    =1

AND INV.VENDOR_ID          =SUP.VENDOR_ID

AND SUP_SITE.VENDOR_ID     =INV.VENDOR_ID

AND SUP_SITE.VENDOR_SITE_ID=INV.VENDOR_SITE_ID

AND SUP_SITE.LOCATION_ID   =SUP_ADD.LOCATION_ID

AND (INV.REQUESTER_ID      = PERSONNAME.PERSON_ID(+)

AND TRUNC(SYSDATE) BETWEEN PERSONNAME.EFFECTIVE_START_DATE(+) AND PERSONNAME.EFFECTIVE_END_DATE(+))

AND PO.PO_HEADER_ID(+)     =INV.PO_HEADER_ID

AND LEGAL_E.LEGAL_ENTITY_ID=INV.LEGAL_ENTITY_ID

AND INV.ORG_ID             =ORG.ORGANIZATION_ID

AND ORG.NAME               =:BUSSINESS_UNIT

 

 

Click on Save.

Save the Data Model in desired Folder as below

 

Now our Data Model is saved.

 To view the data

Click on “Save As Sample Data.” To save sample data to create a report.