This is embarrasing but I'm having trouble with a SQL join I was hoping someone could assit with.
I'm writing a report (by item_id) that includes cost, po and invoice information. I'm fine with the item and cost data until I start joining to the po tables for the po data. Since I have to go to po_lines_all to complete the join from the item master to the po_header (for inventory_item_id), I'm now getting multiple records because of the individual po_lines. I don't see any way around this though. Can this not all be done through the same SQL statement?
Here is a small chunk of the code including the problematic join:
sum(qoh.transaction_quantity) qoh, -- total quantity on hand
cic.item_cost item_cost, -- frozen cost
max(poh.segment1) po_num -- latest PO
msi.inventory_item_id = qoh.inventory_item_id and
msi.inventory_item_id = cic.inventory_item_id and
cic.cost_type_id = 1 and
cic.organization_id = msi.organization_id and
pol.item_id = msi.inventory_item_id and
poh.po_header_id = pol.po_header_id and
msi.organization_id = 18 and
msi.inventory_item_id = 2746