Installing BI Publisher Plug -in in Oracle Fusion Applications

Steps that are involved in installing BI Publisher for Oracle Fusion Applications 

Step 1: Find out your OS Architecture ( 32 bit/64 bit)
Step 2: Go to Oracle software down load link to  down load BI Installer
Step 3: Install BI Publisher Plug -in
Step 4: Enable or disable Template Builder
Step 5: Check BI Publisher tab available in MS Word .

Step 1: Find out your OS Architecture ( 32 bit/64 bit)

Click on Windows button --> This PC (or) My Computer --> Properties 

Step 2: Go to Oracle software down load link to  down load BI Installer

Log in into Oracle Fusion Applications

Go to Reports and Analytics

Navigator --> Reports and Analytics

Click on Browse Catalog 

Login in into Xmlpserver

Take Url of your instance and remove all the content after (.com ) Part of your URL

Once you login into Oracle Fusion reports and analytics URL will be like below
E. g:

URL : https://xxxxxxxxxxxxxxxxxxx.com/analytics/saw.dll?catalog

Chang the URL to like below :

URL : https://xxxxxxxxxxxxxxxxxxxxxxx.com/xmlpserver

Once you login into xmlpserver Then go to Help --> Download BI Publisher Tools 

It will Redirect to Oracle BI Publisher Download Page 

Download BI Publisher installer based your computer architecture  

Step 3: Install BI Publisher Plug -in  

Go to your downloads folder and Install it :

Install BI Publisher Desktop in the default directory location.

After the installation the following message appears:

Depending on the OS architecture,  run TBAddInSetup and ETBAddInSetup:

Note: TBAddInSetup is for Microsoft Word and ETBAddInSetup is for Excel.

Step 4: Enable or disable Template Builder

  Click on Window button --.> Click on BI Publisher folder --> Enable or Disable Template Builder

After selecting Enable or Disable for the Template Builder, the following messages appears:

Click OK --> Conformation OK

Step 5: Check BI Publisher tab available in MS Word .

After the above open Microsoft Word and confirm that the BI Publisher tab is present:

If not present , check the Add-ins tab for COM Add-Ins and Disabled Items

Click on add in and select OK for enabling add in 

Thanks for visiting Blog :
I would like to have your valuable comments.

For More Updates . Please  click on Follow link

Development of Custom BI Publisher Report in Oracle Fusion Cloud Applications

Steps to develop Custom BI Publisher report in Oracle Fusion Cloud Applications

Step 1 : Creation of  Data Model
Step 2 : Creation of Report

Step 1 : Creation of  Data Model

Navigation : Navigator --> Reports and Analytics 

Click on Browse Catalog  

Page will be redirected to Business Intelligence Home Page 

Click on New Icon which is available on let side of BI home Page

In Diagram tab click New Data Set -- SQL Query

Enter details

Name : Can give as per your Naming Standards (E. g SP_JOUR_DM)
Data Source : This can be selected based fusion DB Schema which we would like to access
Note :
ApplicationDB_FSCM ( Financial and Supply Chain Management )
ApplicationDB_HCM ( Human Capital Management )
ApplicationDB_CRM ( Customer Relationship Management )

Type of SQL: This is can based on Type of SQL which using for report

Standard SQL :Standard SQL used for normal SELECT statements interpreted to understand database schema
Procedure Call : query type to call a database procedure
To use REF cursor in Oracle BI Publisher :
Use this query type to issue SQL statements that can include the following:
Cursor statements that return nested results sets
Functions returning REF cursors
To use REF cursor in Oracle BI Publisher

In SQL Query Section we can provide Query If readily available . Otherwise can use Query Builder to Build Query for report 

Click Ok . Then Save Data Model

Enter Details of Data Model

Click on Data  Tab to save sample data ( This sample data is required to created layout)

Step 2 : Creation of Report

Click on Create Report Which is available in BI Home Page

Default Data Model will selected Click On Next

Select

Page Options : Portrait 
Layout : Table 

Click next 

Drag Fields from Data Source --> Click Next 

Click on Finnish to Save report and View Output 

We can report output in Interactive Dash Board 

To View or Download output in different format . Click on View report icon which is available in extreme right corner .

You are Done ..!!

Thanks for visiting Blog : 

For More Updates . Please  click on Follow link

Customization Process of Oracle Fusion Standard (Seeded) BI Publisher Report

There are 6 steps involved in customizing standard report

Step By Step Process:

Step 1: Identify which job we are going to customize (E.g: Trial Balance Report)

Step 2: Create copy of the standard report

Step 3: Attach data model to New Customizing report

Step 4: Change Enterprise Scheduler Job Package Name and Enterprise Scheduler Job Definition Name

Step 5: Register as Enterprise scheduler job

Step 6: Submit report from Scheduled Process

Step 1: Identify which job we are going to customize (Ex: Trial Balance Report)

     Navigation : Navigator --> Schedule Process (Select job which needs to be customized )

     select job from the list (E.g: Trial Balance Report) and Submit 

      Click on log 

Note Report .xdo file  Path 

Step 2: Create copy of the standard report

Go to Reports and Analytics 

 Navigation : Navigator --> Reports and Analytics 

Click on browse catalog 

Go to folder navigation : /Financials/General Ledger/Trial Balances/

Click more --> Select Customization Option

Note : Now we can observe that in Custom Folder Report copy will be created with same as path like in standard folder

Step 3: Attach data model to New Customizing report   

To find out data model which is attached to this report click on edit 

Now Copy Standard Data model and Paste in Custom folder

 Go to path /Financials/General Ledger/Trial Balances/Data Model 

Go to Path : Shared Folder/Custom//Financials/General Ledger/Trial Balances/

Paste Data Model like below 

Once Pasted Then click on edit report Then Select the data model which we have copied in custom folder for the report 

We are done with Creating Copy of the Report from Standard Report 

Step 4: Change Enterprise Scheduler Job Package Name and Enterprise Scheduler Job Definition Name

Navigation : Click on edit report --> Go to Properties 

Change names as per naming standards like below

Enterprise Scheduler Job Package Name   : GLTRBALSPCROP  Enterprise Scheduler Job Definition Name : GLTRBALSPCROP

Step 5: Register as Enterprise scheduler job

Go to Setup and Maintenance : 

Select  : Financials  and search for task : Enterprise Scheduler Jobs for Financial and Supply Chain Management and Related Applications

Search for standard Report then click on Duplicate :

Once we duplicate report One record will be created with Blank Report 

Then select that entry and click on edit ( Pencil ) Symbol 

Provide details like below :

Report Display Name : As you like (E.g. SP Corp Trial Balance Report)

Name : as you like (E.g spcorptrialbalrpt this like short name in EBS )

Path : /oracle/apps/ess/custom/ ( This is same for Custom reports)

Report ID : This should be the exact Path of report which in Custom Folder with Report name Extension (.xdo)

(E.g /Custom/Financials/General Ledger/Trial Balances/TrialBalance.xdo)

Copy Standard Report Parameters : 

Click on Copy from existing job definition 

Search for standard report and click Ok 

Parameters will be copied from your standard report to New Report 

You are done with Creating Custom Enterprise Scheduler job for Standard report 

Step 6: Submit report from Scheduled Process

Thanks for visiting Blog : 

For More Updates . Please  click on Follow link 

Query to extract Purchase Order Details

SELECT POH.SEGMENT1 "Po Number"
     , POH.TYPE_LOOKUP_CODE "Po  Type"
     , POH.AUTHORIZATION_STATUS "Po  Status"
     , POV.VENDOR_NAME "Supplier Name"
     , POVS.VENDOR_SITE_CODE "Location"
     , HRLS.LOCATION_CODE "Ship To"
     , HRLB.LOCATION_CODE "Bill To"
     , POL.LINE_NUM "Line Number"
     , MSIB.SEGMENT1 "Item"
     , POL.UNIT_PRICEm "Unit Price"
     , POL.QUANTITY "Quantity"
     , POD.AMOUNT_BILLED "Amount"
     , POD.DESTINATION_SUBINVENTORY
     , PPF.FULL_NAME "Buyer Name"
     , POH.CLOSED_CODE
FROM   PO_HEADERS_ALL POH
     , PO_LINES_ALL POL
     , MTL_SYSTEM_ITEMS_B MSIB
     , PO_LINE_LOCATIONS_ALL POLL
     , PO_DISTRIBUTIONS_ALL POD
     , PO_VENDORS POV
     , PO_VENDOR_SITES_ALL POVS
     , HR_LOCATIONS_ALL HRLS
     , HR_LOCATIONS_ALL HRLB
     , PER_ALL_PEOPLE_F PPF
     , PO_LINE_TYPES POLT
WHERE  POLT.LINE_TYPE_ID = POL.LINE_TYPE_ID
AND    POVS.VENDOR_SITE_ID = POH.VENDOR_SITE_ID
AND    POV.VENDOR_ID = POH.VENDOR_ID
AND    POL.ITEM_ID = MSIB.INVENTORY_ITEM_ID
AND    MSIB.ORGANIZATION_ID = 204
AND    POH.PO_HEADER_ID = POL.PO_HEADER_ID
AND    POL.PO_LINE_ID = POD.PO_LINE_ID
AND    POLL.LINE_LOCATION_ID = POD.LINE_LOCATION_ID
AND    POH.SHIP_TO_LOCATION_ID = HRLS.LOCATION_ID
AND    POH.BILL_TO_LOCATION_ID = HRLB.LOCATION_ID
AND    POH.AGENT_ID = PPF.PERSON_ID
AND    POH.SEGMENT1 = NVL (:PO_NUMBER, POH.SEGMENT1);    -- Enter Purchase Order Number

Query to extract PO Requisitions Details

SELECT HOU.NAME "Operating unit"
     , PRH.SEGMENT1 "Requisition Number"
     , PRH.DESCRIPTION "Description"
     , PRH.TYPE_LOOKUP_CODE "Requisition Type"
     , PRH.AUTHORIZATION_STATUS "Requisition Status"
     , PPF.FULL_NAME "Preparer"
     , MSI2.SEGMENT1 "Item Number"
     , PRL.LINE_NUM "Requisition Line Num"
     , PRL.ITEM_DESCRIPTION "Requisition Line Desc"
     , PRL.UNIT_MEAS_LOOKUP_CODE "UOM"
     , PRL.QUANTITY
     , PRL.UNIT_PRICE "Unit cost"
     , MIC.SEGMENT1 || '.' || MIC.SEGMENT2 || '.' || MIC.SEGMENT3 || '.' || MIC.SEGMENT4 CATEGORY
     , HOUT.NAME ORGANIZATION
     , HLA.LOCATION_CODE LOCATION
     , PRL.DESTINATION_SUBINVENTORY DEPARTMENT
     , PPF2.FULL_NAME REQUISITER
FROM   APPS.PO_REQUISITION_HEADERS_ALL PRH
     , APPS.PO_REQUISITION_LINES_ALL PRL
     , APPS.PER_ALL_PEOPLE_F PPF
     , APPS.PER_ALL_PEOPLE_F PPF2
     , APPS.MTL_SYSTEM_ITEMS_B MSI2
     , APPS.HR_LOCATIONS_ALL HLA
     , APPS.HR_ALL_ORGANIZATION_UNITS_TL HOUT
     , APPS.MTL_CATEGORIES MIC
     , APPS.HR_OPERATING_UNITS HOU
WHERE  1 = 1
AND    HOU.ORGANIZATION_ID = PRH.ORG_ID
AND    HOUT.ORGANIZATION_ID(+) = PRL.DESTINATION_ORGANIZATION_ID
AND    HOUT.LANGUAGE(+) = USERENV ('LANG')
AND    PPF2.PERSON_ID(+) = PRL.TO_PERSON_ID
AND    TRUNC (SYSDATE) BETWEEN PPF2.EFFECTIVE_START_DATE(+) AND PPF2.EFFECTIVE_END_DATE(+)
AND    HLA.LOCATION_ID(+) = PRL.DELIVER_TO_LOCATION_ID
AND    MIC.CATEGORY_ID(+) = PRL.CATEGORY_ID
AND    MSI2.INVENTORY_ITEM_ID = PRL.ITEM_ID
AND    MSI2.INVENTORY_ITEM_ID(+) = PRL.ITEM_ID
AND    MSI2.ORGANIZATION_ID(+) = PRL.DESTINATION_ORGANIZATION_ID
AND    NVL (PRH.CANCEL_FLAG, 'N') <> 'Y'
AND    NVL (PRL.CANCEL_FLAG, 'N') <> 'Y'
AND    PRL.REQUISITION_HEADER_ID = PRH.REQUISITION_HEADER_ID
AND    PPF.PERSON_ID = PRH.PREPARER_ID
AND    TRUNC (SYSDATE) BETWEEN PPF.EFFECTIVE_START_DATE AND PPF.EFFECTIVE_END_DATE
AND    PRH.ORG_ID = 204
AND    PRH.SEGMENT1 = NVL (:REQ_NUMBER   ,PRH.SEGMENT1)                                                         -- ENTER REQUISITION NUMBER