Oracle Transactional Business Intelligence

Products Banner

Want to know the exact name and subject area in OTBI for adding 'Reference Number' field

Received Response

Summary: Hi Team,

OTBI - Could you please suggest the correct subject area and field name for adding Reference Number field (Highlighted in Yellow below) available in the Payment Details section under the head General Information?

Content (required): OTBI - Could you please suggest the correct subject area and field name for adding Reference Number field (Highlighted in Yellow below) available in the Payment Details section under the head General Information?

Version (include the version you are using, if applicable):

Code Snippet (add any code snippets that support your topic, if applicable):



  • Nathan CCC
    Nathan CCC ✭✭✭✭✭

    Hi, What did you click to get this screen shot. Is this Procurement, Payables, Receivables? Which navigator group did you expand, which work area did you select, which task did you you to go to a manage page? In any case you must look at your data lineage in your user guide. This tells you what source transaction fields MAY be mapped to presentation tables and columns in the subject areas. Not all fields are available in analytics. For example, go to financials then select link "Review Data Lineage" to download the sheet. Else just build analysis with all cols and see which one has your data.

  • Nathan CCC
    Nathan CCC ✭✭✭✭✭

    So I think you want to try subject area "Payables Payments - Disbursements Real Time". Assume to get screen shot you did select Navigator, select Show More, in group Payables select work area Payments, select Tasks, select Manage Payments, expand section Search, select Advanced if Basic, enter criteria, select Search, select row, select link in Payment Number. System go to page Payment: 999.

  • Nathan CCC
    Nathan CCC ✭✭✭✭✭

    BTW I think the field "Reference Number" is physical table column IBY_PAYMENTS_ALL.PAYMENT_REFERENCE_NUMBER. So if you cant find this mapped to a subject area you can instead build it in a data model with an OTBI report.

  • @Nathan CCC - The Payables OTBI report is already built and the customer is already expecting just 'Reference Number' field to be added..

    Can you please guide on how to meet this requirement with detail explaination/Steps?

    Thanks in advance.

  • Nathan CCC
    Nathan CCC ✭✭✭✭✭

    Hi Rahul,

    Now you are talking about an OTBI report? I assumed you had built an OTBI Analysis. So did you build it using an OTBI Analysis (blue) not an OTBI Report (yellow) - the colors of the icons in the list of values when you select menu "Create"? If you built it as an OTBI report was it that your data model is logical sql using data source OBIEE/analysis so that why you were asking about subject areas? If you did in fact build an OTBI Analysis, but for some reason are calling it an OTBI Report, which subject area(s) have you used already to build your query?

    If the attribute you need is not in one of the subject areas then that is it. You cannot meet this requirement using an analysis. All you can do is raise an idea with oracle to ask them to consider adding "Payment Reference Number" in a subsequent release of the metadata repository database (RPD) in OTBI.

    Now since you cant use the subject areas your only option in OTBI is to build an OTBI report with a data model with a dataset type sql with data source application databse (physical sql not logical sql). Take that ~99% that you have built as an analysis, cut paste the logical sql from the advanced tab, go to saw.dll?issuerawsql (grant privilege to your user/role "issue sql directly" and "manage sessions", issue the sql with logging level 2, view the log, get the physical sql from the log. Now extend it to meet your requirements. If the table you need with your missing col is not in the query then you need to find the joins. Read the table and views documents in your help guide to see what foreign keys joins on tables and views you can use to add to the query to meet your requirement. Good luck.

  • Hi @Nathan CCC ,

    Yes, I meant OTBI Analysis.

    Sure, I will raise an idea as I couldn't find the 'Payment Reference Number' field in any of the Payables Payment Subject area.

    Thank you. ?

  • Hi,

    Were you able to add the "Payment Referece Number" on your OTBI Analysis?