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

Rahul Kumar Gupta.-Oracle

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

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 https://docs.oracle.com/en/cloud/saas/financials/22c/analyze-and-report.html 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

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

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.

Rahul Kumar Gupta.-Oracle

@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

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. https://docs.oracle.com/en/cloud/saas/financials/22c/oedmf/ibypaymentsall-5495.html#ibypaymentsall-5495 Good luck.

Rahul Kumar Gupta.-Oracle

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. ?

User_XX0R2

Hi,

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

Raakesh Piranawar

Is there a document that can give me column names of subject areas?

Nathan CCC

Yes. Go to documents top task Analyze and Report https://docs.oracle.com/en/cloud/saas/financials/24b/analyze-and-report.html

Then "Review Data Lineage" https://us.v-cdn.net/6034893/uploads/4W86KHGD61PY/r13-24b-fin-otbi-database-mapping-with-viewobjects.xlsx

Siddhartha Sahu

Hi @Nathan -

I am looking for a document with all HCM module subject areas and their details like the way you shared for Finance module here . do you know where to find it ?