Hi 

I am extracting data to PowerBI.

I want to extract cost of sales and revenue for all customer order shipments.

I want to combine shipment & order line data with GL voucher data (account, sales group) 

We are on Apps 9.

I can’t figure out the links to do this.

I want to start from closed/completed shipment lines. 

COST OF SALES:

SHIPMENT_ORDER_LINE_OVW → links to → SHIPMENT, CUSTOMER_ORDER_LINE & CUSTOMER_ORDER using shipment_id, order_no, line_no, rel_no & line_item_no. Easy!

INVENTORY_TRANSACTION_HIST2 → links to → MPCCOM_ACCOUNTING → links to → vouchers & voucher rows on accounting_id. Easy!

But what is the link from shipment lines to inv trans hist? 

INVENTORY_TRANSACTION_HIST2 has fields SOURCE_REF1,2,3,4 & 5.

1,2,3&4 are clearly Customer Order, Line, Rel, Lini Item (for SOURCE_REF_TYPE = Customer Order)

What is SOURCE_REF5… Help says its delivery reference… so I think it is the key to link back to shipment line…. but cant find where it links to.                                                    

REVENUE:

SHIPMENT_ORDER_LINE_OVW → links to → SHIPMENT, CUSTOMER_ORDER_LINE & CUSTOMER_ORDER using shipment_id, order_no, line_no, rel_no & line_item_no. Easy!

CUST_ORDER_INV_ITEM_UIV_ALL → links to → CUST_ORDER_INV_HEAD_UIV_ALL → links to → GEN_LED_VOUCHER2  → links to → GEN_LED_VOUCHER_ROW_UNION_QRY.  

But what is the link from shipment lines to customer order invoice lines? 

Much appreciated if anyone can point in the right direction… or knows where there is a database tech ref that has the answers!

Many thanks

Tom