Oracle Communities

Oracle Analytics and AI

Child Item

How can I join the SCM tables with Financial tables in Oracle Fusion ?

I'm developing a SQL report to join in Customer_po_number from DOO_HEADERS_ALL with Invoice_num from AP_INVOICES_ALL. However, there’s no direct common column between them. How can I effectively link these tables to retrieve relevant information for my report?

Find more posts tagged with

OBIEE

Financials

SQL

Supply Chain

OTBI

Accepted answers

MandeepGupta

https://community.oracle.com/products/oracleanalytics/discussion/comment/62422#Comment_62422

So, basically you are looking for a customer PO number and the invoice against that? Can you share your complete requirement?

Thanks.

All comments

MandeepGupta

https://community.oracle.com/products/oracleanalytics/discussion/24229/how-can-i-join-the-scm-tables-like-with-financial-tables-in-oracle-fusion

Is Customer_po_number same as Invoice_num ? If yes, you can create two data sets - one with SCM data source and one with FIN data source and join them using the mentioned columns.

Thanks.

AdamOoba098

Hi Mandeep, thanks for your reply, in this case all the customer_po_numbers are different than the invoice numbers, looking to bring them both into one table.

MandeepGupta

https://community.oracle.com/products/oracleanalytics/discussion/comment/62422#Comment_62422

So, basically you are looking for a customer PO number and the invoice against that? Can you share your complete requirement?

Thanks.

AdamOoba098

'So, basically you are looking for a customer PO number and the invoice against that? '

Essentially yes, this is the one part of my report I haven't been able to figure out.

Its a 16 col report but for the sake of simplicity just looking to match each invoice num with a respective customer_po_number.

MandeepGupta

https://community.oracle.com/products/oracleanalytics/discussion/comment/62424#Comment_62424

Check if below helps:

Link between DOO_HEADERS_ALL and INV_PICK_SLIP_NUMBERS — Cloud Customer Connect

AdamOoba098

Afraid it doesn't help, INV_PICK_SLIP_NUMBERS is a supply chain management table like doo_headers_all - looking to join doo_headers_all to any financials table (tertiary join if necessary).

MandeepGupta

https://community.oracle.com/products/oracleanalytics/discussion/comment/62431#Comment_62431

Did you check the query on the link?

It shows links to RA_CUSTOMER_TRX_LINES_ALL/ra_cust_trx_types_all tables which are finance tables.

Thanks.