|
|
|
|
| Re: How to find Invoice and Reciept number, based on po number [message #234679 is a reply to message #234221] |
Wed, 02 May 2007 16:18  |
venkat.arakla
Messages: 2
Registered: April 2007
|
Junior Member |
|
|
Hi,
Please see below query which gives ur required details...
Modify according to ur requirement. But this query has all join conditions
SELECT --pv.vendor_name,
ai.invoice_num invoice_num,
ph.segment1 po_num,
mp.organization_code wh_code,
msi.segment1 model_suffix,
pll.quantity_received recv_qty,
pl.unit_price po_fg_price,
oh.order_number order_num,
oh.creation_date ord_create_date,
FROM oe_order_headers_all oh,
mtl_system_items msi,
mtl_parameters mp,
ap_invoices_all ai,
ap_invoice_distributions_all aid,
po_distributions_all pd,
po_line_locations_all pll,
po_lines_all pl,
po_vendors pv,
po_headers_all ph
WHERE ph.creation_date BETWEEN P_FROM_DATE AND P_TO_DATE
-- BETWEEN SYSDATE-100 AND SYSDATE
AND pv.segment1=NVL(p_supplier_code,pv.segment1)
AND ph.vendor_id=pv.vendor_id
AND pv.attribute2='Y'
AND ph.po_header_id=pl.po_header_id
AND pl.po_line_id=pll.po_line_id
AND pll.quantity_received>0
AND pd.po_line_id=pll.po_line_id
AND pd.po_distribution_id=aid.po_distribution_id
AND aid.LINE_TYPE_LOOKUP_CODE='ITEM'
AND aid.invoice_id=ai.invoice_id
AND pll.ship_to_organization_id=mp.organization_id
AND pl.item_id=msi.inventory_item_id
AND msi.organization_id=pll.ship_to_organization_id
AND oh.ORIG_SYS_DOCUMENT_REF='PO#'||ph.segment1
AND msi.ORGANIZATION_ID = p_org_id /* */
AND oh.cust_po_number=ph.segment1
ORDER BY 1,2,3;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
