Monday, December 6, 2010

PO-INV Link Query



SELECT
p.segment1 po_number,
pol.po_line_id,
pol.category_id,
pv.segment1 supplier_number,
pv.vendor_name supplier_name,
pol.line_type_id,
msi.segment1 Item_number,
pol.item_description,
trunc(pol.creation_date) PO_Date,
trunc(poll.need_by_date) Promised_date,
trunc(p.acceptance_due_date) Received_date,
(poll.quantity) Quantity_ordered,
(poll.quantity_received) Quantity_received,
(poll.quantity-poll.quantity_received) Quantity_remaining,
(poll.quantity_billed) Quantity_billed,
(poll.quantity_cancelled) Quantity_cancelled ,
(poll.quantity* pol.unit_price) Ordered_amount,
(poll.quantity_received*pol.unit_price) Received_amount,
(poll.quantity_billed*pol.unit_price) Billed_amount,
(poll.quantity_cancelled*pol.unit_price) Cancelled_amount
FROM
po_lines_all pol,
po_vendors pv,
po_line_locations_all poll,
po_headers p,
mtl_system_items msi
WHERE
pv.vendor_id = p.vendor_id and
p.po_header_id=pol.po_header_id and
poll.po_header_id = p.po_header_id and
poll.po_line_id=pol.po_line_id and
(msi.organization_id = poll.ship_to_organization_id or
msi.inventory_item_id is null) and
msi.inventory_item_id(+) = pol.item_id

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.