Reporting and Analytics for SCM

Get Involved. Join the Conversation.


  • Senthilrajan Vaithianathan

    Hi Vitor,

    Looks like you are using BI Publisher Template, can check if you get similar out from Data Model as well... see attached screen shot.

  • Vitor Lima

    Hello  Senthilrajan,

    I believe I'm using OTBI, I've switched the component to "data table" it showed all results in the order accordingly to what is being returned in the data model...


    Before I was using list component but seems like it has a "select distinct " function since it doesn't repeat values in the list and also mixing the output of the lists...

    bellow I've attached two files "edited" which is the editing of the report and "result" which is the final PDF output

    What are your thoughts? 

    Edited.PNG (113KB)
  • Senthilrajan Vaithianathan

    Hi Vitor,

    The report you have referred is OTBI or BI Publisher? Also add screen shot with expected output...

  • Tejaswi p

    Hi Gurus,

    I am new to Oracle cloud.We are not able to get proper link between the tables mentioned below.

    Could you please help me in getting the joins between EGP_SYSTEM_ITEMS_B,INV_ITEM_LOCATIONS,INV_MATERIAL_TXNS,INV_SERIAL_NUMBERS.

    Also i need fm_serial_number ,i am confused from which column i need to get this field joining above table.

    Appreciate your help ASAP.


    Thanks & Regards,


  • Deb Heroneme

    You can find documentation for tables and views at:



  • Koteswararao Yenikepalli

    Please find the query below

  • Koteswararao Yenikepalli

    I have a query to get the manual and the all sub-ledger entries you can use that query for your reference 


    Please let me know if you need any further information.

    Thank You. 


  • Amit Gupta

    Could you please help us to get link on Below Table.


  • Tessy van Engelen

    @Olaoluwa : you have multple lines on distribution level. If you don't want to report on this level you can make a sum on the amount and put the other columns i a group by. Then you should see one line per order. See below query.

           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,
           sum(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  1=1
           AND prl.requisition_header_id = prh.requisition_header_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 aida.po_distribution_id = pda.po_distribution_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(+)
           group by,
           hzp.party_name        ,
           hzp.party_number      ,
           poss.vendor_site_code ,
           prh.creation_date     ,
           poh.segment1          ,
           poh.creation_date     ,
           rsh.receipt_num       ,
           order by poh.segment1 desc

  • Prem Lakshmanan

    The information in this thread is related to Release 19A. Please continue this conversation on this thread which is more current (related to Release 19D):


    Prem Lakshmanan

    Oracle Support


  • Olaoluwa Sakeye

    @Tessy : Not yet, i still need help on it.

  • Tessy van Engelen

    Hi Both,

    Did you manage to finish this query?


  • Rajasekhar Bandaru
    Hi Ashish, Could you please schedule the BIP report directly from bip and check whether parameters are getting passed properly are not. If it's work fine in BIP then same will work from ESS. Thanks and Regards Rajasekhar
  • nathan morgan (at Client)

    It is impossible to know only from a report name

    But here is a list of reports - try them out to see if it fits your requirement

    or if not you can review the following subject areas if you need to build your own analysis or report


  • Harshal Agrawal

    Thank you Shyam for sharing doc id for my answer.