Reporting and Analytics for SCM

Get Involved. Join the Conversation.


  • Mohamed Rashad_Ora

    Hi Niamath ,

     We had this requirement and opened an Service Request for the same purpose but it can't be done and they recommend to buy the "Analytics Cloud Service" , please review the answer from this document " Doc ID 2003782.1 "

    Hope this answer your question.



  • Siva Kumar

    Please check the below




  • Sanoj Sudheera


    Check ERP Report Sharing Center

    There are few reports which you can cover your requirement. Update as per your requirement.



  • sai sudheer

    Hi Can you be more specific on your requirement!!

  • Shyam Singh Patel

    SQL to Obtain ALL copied/Returned orders where original order is known

    SELECT  orig_dha.source_order_number "Original Order",
            Copied_dha.source_order_number "Copied Order" ,
            dcr.HEADER_ID                                 ,
            dcr.LINE_ID                                   ,
            dcr.FULFILL_LINE_ID                           ,
            dcr.DOC_REF_TYPE                              ,
            dcr.DOC_ID                                    ,
            dcr.DOC_CONTEXT_ID                            ,
            dcr.DOC_USER_KEY                              ,
            fusion.doo_headers_All orig_dha   ,
            fusion.doo_headers_All copied_dha
                    TO_CHAR(dcr.doc_id)    = TO_CHAR(orig_dha.source_order_number)
                    OR TO_CHAR(dcr.doc_id) = TO_CHAR(orig_dha.header_id)
            AND orig_dha.source_order_number in('&ENTER_ORIGINAL_ORDER')
            AND copied_dha.header_id    = DCR.header_id
    AND copied_dha.submitted_flag = 'Y'
            AND orig_dha.submitted_flag = 'Y'
    order by Copied_dha.source_order_number


    SQL to show receiving transactions for the Return Order
    SELECT  ra_document_code       ,
            SHIPMENT_HEADER_ID     ,
            CUSTOMER_ID            ,
            RA_DOCUMENT_CODE       ,
            RA_DOCUMENT_NUMBER     ,
            HEADER_INTERFACE_ID    ,
    FROM    fusion.rcv_shipment_headers
    WHERE   ra_document_code        = 'RMA'
            AND ra_document_number in( '&ENTER_RETURN_ORDER')  


    SQL to show Shipping transactions for the Return Order

            QUANTITY_RECEIVED        ,
            QUANTITY_DELIVERED       ,
            ITEM_ID                  ,
            SOURCE_DOCUMENT_CODE     ,
            RA_DOO_HEADER_NUMBER     ,
    FROM    fusion.rcv_shipment_lines
    WHERE   shipment_header_id IN
                    SELECT  SHIPMENT_HEADER_ID
                    FROM    fusion.rcv_shipment_headers
                    WHERE   ra_document_code        = 'RMA'
                            AND ra_document_number IN( '&ENTER_RETURN_ORDER')


    RMA- One or more Order Lines have already been returned, A Return Order for one or more lines on the order already exists for sales order and can be validated in one of the ways below


       dha.order_number "Original Sales Order"

     , dla.line_number "Original Order Line"

     , dhr.order_number "RMA Order"


      doo_headers_all dha

     , doo_lines_all dla

     , doo_document_references ddr

     , doo_headers_all dhr


           ddr.doc_id = dha.header_id

    AND dha.order_number = '289966'  ---- Enter your Sales Order Here

    AND dhr.header_id = ddr.header_id

    AND doc_ref_type = 'ORIGINAL_SALES_ORDER'

    AND dla.header_id = ddr.doc_id

    AND dla.line_id = ddr.doc_line_id


  • Ling Xiang

    No, this doesn't affect BI Publisher reports.  You can continue to create a SQL data model with BI Publisher.  Creating a BI Answers report with 'Direct Database Query' option is different from BI Publisher.  It is BI Answers 'Direct Database Query' option that will be deprecated in 12 months.

  • Shyam Singh Patel

    Hi Harshal,

    This Note ID has all the details

    Back End Query to Print the Work Order Traveler Report (Doc ID 2397326.1)
    Work Order Number (Doc ID 2526462.1)
    Notification For Work Order Completion (Doc ID 2450492.1)   ------ ER


  • Senthilrajan Vaithianathan

    With "BI Platform Author Role", "BI Administrator Role" you should be able to work on the data model. Check if you have one of these roles.

  • Senthilrajan Vaithianathan

    You can get table details from below link,

    You have some API's as well.

    For the data source issue, you may need to get access, check if you can add BI Administrator role to your user.



  • Piyush Bihany

    Hi Caio/Senthil

    At this moment,  creating a custom Infolet is not a possible feature under most of the modules (Procurement, Inventory Management, Sourcing, Receivables, etc.)


    Kindly refer to the following Doc ID's for reference:

    How To Create Custom Infolets In Procurement (Doc ID 2321871.1)

    Unable to Add New Infolet to Inventory Management Page (Doc ID 2519699.1)

    Unable To Edit Home > Open Orders By Buyer Infolet (Source To Settle Infolet) (Doc ID 2423464.1)

    How To Create Custom Infolets On The Landing Pages In Receivables ? (Doc ID 2160012.1)



  • Senthilrajan Vaithianathan

    Hi Koteswara,

    We are unable to edit the OOTB Infolets in Fusion Cloud ERP/SCM and I am unable to create a custom Infolets in Oracle Fusion ERP, let me know if there is any specific role to be added for creating new Infolets.

    Please confirm if you were able to create one, also the document link you have highlighted is specific to EPM cloud.


  • Tom Strudley
    Hi Karim Best to put this in the BI idea lab Thanks Tom
  • Milind Dalvi
  • Milind Dalvi

    You should be able to use LSQL to join two subject area's. Please refer to file attached.

  • Sudhakara Rao Kovuru
    --SQL for P2P Details - PR-> PO -> Receipt -> AP Invoice 
           hzp.party_name        supplier_name, 
           hzp.party_number      supplier_number, 
           poss.vendor_site_code supplier_site_name, 
           prh.creation_date     pr_creation_date, 
           poh.segment1          po_Number, 
           poh.creation_date     po_creation_date, 
           rsh.receipt_num       receipt_number, 
           aia.invoice_num       invoice_number, 
           aila.amount           Invoice_amount 
    FROM   por_requisition_headers_all prh, 
           por_requisition_lines_all prl, 
           po_headers_all poh, 
           po_lines_all pol, 
           poz_suppliers pos, 
           hz_parties hzp, 
           poz_supplier_sites_all_m poss, 
           hr_all_organization_units bu, 
           po_distributions_all pda, 
           gl_code_combinations gcc, 
           rcv_shipment_lines rsl, 
           rcv_shipment_headers rsh, 
           ap_invoice_lines_all aila, 
           ap_invoices_all aia, 
           ap_invoice_distributions_all aida 
    WHERE  prl.requisition_header_id = prh.requisition_header_id 
           --AND prh.req_bu_id=fbu.bu_id
           AND prl.po_header_id = poh.po_header_id 
           AND prl.po_line_id = pol.po_line_id 
           AND poh.po_header_id = pol.po_header_id 
           AND poh.vendor_id = pos.vendor_id 
           AND poh.vendor_site_id = poss.vendor_site_id 
           AND hzp.party_id = pos.party_id 
           AND pos.vendor_id = poss.vendor_id 
           AND bu.organization_id = poss.prc_bu_id 
           AND pda.po_header_id = pol.po_header_id 
           AND pda.po_line_id = pol.po_line_id 
           AND rsh.shipment_header_id(+) = rsl.shipment_header_id 
           AND rsl.po_line_id(+) = pol.po_line_id 
           AND aila.po_line_id(+) = pol.po_line_id 
           AND gcc.code_combination_id = aida.dist_code_combination_id 
           AND aia.invoice_id(+) = aila.invoice_id 
           AND aia.invoice_id = aida.invoice_id(+) 
           --AND prh.requisition_number = pr_requisition_number 
           --AND poh.segment1 = po_number