Home » Applications » Oracle Fusion Apps & E-Business Suite » How to find Invoice and Reciept number, based on po number
How to find Invoice and Reciept number, based on po number [message #234221] Mon, 30 April 2007 08:41 Go to next message
world.apps
Messages: 70
Registered: January 2007
Location: Hyderabad
Member
Hi,

Could anybody help me, how to find Invoice and Reciept number, based on po number from Back end?

Thanks in advance
Re: How to find Invoice and Reciept number, based on po number [message #234292 is a reply to message #234221] Mon, 30 April 2007 13:45 Go to previous messageGo to next message
gaizka71
Messages: 10
Registered: May 2005
Location: Erandio
Junior Member
I think you can get all the info from here

po_headers
po_lines
po_distributions
ap_invoice_distributions
ap_invoices_all

i might be wrong thou....
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 Go to previous message
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;
Previous Topic: How to Submit report through form using custom.pll
Next Topic: OM- return order problem
Goto Forum:
  


Current Time: Sat Dec 10 09:00:57 CST 2016

Total time taken to generate the page: 0.26499 seconds