I want query for below columns, AR Invoice Integration Exception report using interface Tables only.

User_14HUA

Columns are

ACCOUNT_CLASS,

Distribution_Account,

description,

Invoice_Number,

Distribution_Amount,

Payment_Term,

Currency,

GL_DATE,

Error_Reason,

Ship_To_customer_Name,

Bill_To_customer_Name,

Bill_To_Site_Name,

Ship_To_Site_Name,

Oracle Division Number

Renuka Nuguru-Oracle

Hello,

Below is the data lineage mapping which maps from subject areas to the database columns. This is a good start to find the Subject area presentation columns.

https://community.oracle.com/customerconnect/discussion/40268/r13-financials-otbi-subject-area-to-database-lineage-mapping-spreadsheet/p1

more to explore on Oracle Financials 24A

https://docs.oracle.com/en/cloud/saas/financials/24a/analyze-and-report.html

Thanks,

Renuka

User_14HUA

I want only sql query not OTBI

Nathan CCC

Hi User_14HUA, OTBI is SQL. What you might be saying is that you want to know what are the physical application database tables and views column for a particular attribute in the system. As such it is good practice as suggested by the previous response to start with the presentation tables and columns in the subject areas of the metadata repository database (RPD). You can then trace that down to the application database tables and views using the data lineage sheets as already suggested or by looking in the session log using page /analytics/saw.dll?issuerawsql from your logical sql cut paste from the advanced tab of your analysis to get to know the physical sql(s) generated by the subject area. Then review the application database tables and view in your user guide https://docs.oracle.com/en/cloud/saas/financials/24a/oedmf/index.html#COPYRIGHT_0000

Renuka Nuguru-Oracle

Hello User_14HUA,

As Nathan said, it is easy to get the physical sql from the OTBI report.

Navigate to Administration -> Manage Session. Locate the username for the analysis in question, select View Log and paste the log into Notepad.

Also tables and views give information on the DB columns and the joins.

Check below joins between tables, your requested between tables are also part of these tables and the joins, check if this is helpful.

From Apps.Gl_Je_Headers H,

apps.gl_je_lines jl,

Apps.Gl_Code_Combinations C,

apps.gl_import_references r,

apps.xla_ae_lines al,

Apps.Xla_Ae_headers Ah,

apps.xla_distribution_links l,

apps.ap_invoices_all i,

apps.ap_invoice_distributions_all ad,

apps.ap_suppliers s,

apps.xla_events e,

apps.xla_transaction_entities te

where 

jl.je_header_id = h.je_header_id

AND jl.code_combination_id = c.code_combination_id

and al.gl_sl_link_id = r.gl_sl_link_id

and al.ae_header_id = ah.ae_header_id

and al.application_id = ah.application_id

and ah.application_id = e.application_id

and ah.event_id = e.event_id

and e.application_id = te.application_id(+)

and e.entity_id = te.entity_id(+)

AND r.je_header_id = jl.je_header_id

AND r.je_line_num = jl.je_line_num

AND l.ae_header_id = al.ae_header_id

and l.ae_line_num = al.ae_line_num

and l.applied_to_source_id_num_1 = i.invoice_id

and l.source_distribution_id_num_1 = ad.invoice_distribution_id

and ad.invoice_id = i.invoice_id

and i.vendor_id = s.vendor_id 

-------------------

Go through below given KM doc, end of the doc you can download or view the Sample output of Payables List.

Fusion: Generate Payables Data Collection Test (APList) (Doc ID 1387595.1)

User_14HUA

Error will store only interface tables not in base table, i alredy build build query thanks for the help