You Asked
Hi tom,
i prepared a Query to Get the Details of Requisition, Purchase Order, Receipt and Invoice by giving any of Requisition Number, Purchase Order Number, Receipt Number and Invoice Number. IF all the modules are not complete then to get the details of completed modules.
i used full outer join for this
i.e.
SELECT
a.segment1"Req Number"
,TO_CHAR(a.creation_date,'DD-MON-YYYY') "Creation Date"
,a.authorization_status "Status"
,c.unit_meas_lookup_code "UOM"
,c.item_id "Item ID"
,c.quantity "Quantity"
,c.unit_price "Price"
,c.quantity*c.unit_price "Amount"
,null
,g.segment1 "PO Number"
,to_char(g.creation_date, 'DD-MON-YYYY ') "PO Created"
,g.authorization_status "PO Status"
,i.vendor_name "Suplier"
,j.match_option "Match option"
,null
,n.receipt_num "Receipt Number"
,n.created_by "Receipt Created By"
,to_char(n.creation_date, 'DD-MON-YYYY ') "Receipt Created"
,m.item_id "Item ID"
,d.segment1 "Item"
,m.quantity_shipped "Quantity Shipped"
,null
,l.invoice_num "Invoice Number"
,to_char(l.invoice_date , 'DD-MON-YYYY ')"Invoice Date"
,l.source "Invoice Source"
,l.invoice_amount "Invoice Amount"
,l.created_by "Invoice Created By"
FROM
(SELECT segment1,creation_date,authorization_status,preparer_id,requisition_header_id FROM po_requisition_headers_all) a
FULL OUTER JOIN
(SELECT full_name,person_id FROM per_all_people_f)b on a.preparer_id=b.person_id
FULL OUTER JOIN
(SELECT requisition_header_id,unit_meas_lookup_code,quantity, unit_price, item_id,requisition_line_id FROM po_requisition_lines_all ) c ON a.requisition_header_id=c.requisition_header_id
FULL OUTER JOIN
(SELECT requisition_line_id,distribution_id FROM po_req_distributions_all ) e ON c.requisition_line_id=e.requisition_line_id
FULL OUTER JOIN
(SELECT req_distribution_id,po_header_id,line_location_id FROM PO_DISTRIBUTIONS_ALL ) f ON e.distribution_id=f.req_distribution_id
FULL OUTER JOIN
(SELECT po_header_id,vendor_id,segment1,creation_date,authorization_status FROM po_headers_all ) g ON f.po_header_id=g.po_header_id
FULL OUTER JOIN
(SELECT vendor_id,vendor_name FROM po_vendors ) i ON g.vendor_id=i.vendor_id
FULL OUTER JOIN
(SELECT invoice_num,quick_po_header_id,invoice_date,source,invoice_amount,created_by FROM ap_invoices_all ) l ON g.PO_header_ID = l.QUICK_PO_HEADER_ID
FULL OUTER JOIN
(SELECT match_option,line_location_id,po_line_id FROM PO_LINE_LOCATIONS_ALL ) j ON f.line_location_id=j.line_location_id
FULL OUTER JOIN
(SELECT po_line_id,shipment_header_id,quantity_shipped,item_id FROM RCV_SHIPMENT_LINES ) m ON j.po_line_id = m.po_line_id
FULL OUTER JOIN
(SELECT organization_id,inventory_item_id,segment1 FROM mtl_system_items_b where organization_id=204 ) d on d.inventory_item_id=m.item_id
FULL OUTER JOIN
(SELECT shipment_header_id,receipt_num,creation_date,created_by,ship_to_org_id,vendor_id FROM rcv_shipment_headers where ship_to_org_id=204 ) n
ON m.shipment_header_id=n.shipment_header_id
where
g.segment1 IN ('6207','6193','6194','6208','6209')
-- a.segment1 IN ('14321','14318','14319')
-- l.invoice_num IN ('I6208','13/09/16','13-SEP-2016')
-- n.receipt_num IN ('8478','8479') ;
is this task can do like All the table name in From clause and all the conditions in Where clause..
and Chris said...
Full outer joining everything looks suspicious to me.
Surely you can replace some of the full outer joins with either inner or left joins?
For example, surely invoices must belong to a PO? So you can have a PO without an invoice, but not an invoice without a PO? If so you can change this to a left join.
Go through the joins. Check the foreign key relationships. Those that have a not null foreign key can only exist if the parent exists. So you can change those to left joins.
To give any more detailed help than this you'll need to tell us what the relationships between the tables are!
Is this answer out of date? If it is, please let us know via a Comment