Tuesday, March 22, 2016

Find Receipt based on PO Header_ID , PO Receipt ,Query To Get PO Number, Invoice Number and Receipt Number With one of the Input

Find Receipt based on PO Header_ID


select distinct pha.po_header_id , pha.segment1 po_num,
  pha.creation_date,
  pha.authorization_status,
  plla. ship_to_organization_id,
  (select organization_code
  from apps.org_organization_definitions ood
  where ood.organization_id=plla. ship_to_organization_id
  ) org_code,
  pha.org_id,
  (select name
  from apps.hr_operating_units hou
  where hou.organization_id=pha.org_id
  ) ou_name ,rsh. RECEIPT_NUM
from po.po_headers_all pha ,
  po.po_line_locations_all plla ,
  po.rcv_shipment_headers rsh ,
  po.rcv_shipment_lines rsl
where plla.po_header_id = pha.po_header_id
and rsh.shipment_header_id = rsl.shipment_header_id
and pha.po_header_id = rsl.po_header_id
and pha.po_header_id in (308176);

Query To Get PO Number, Invoice Number and Receipt Number With one of the Input



SELECT distinct pha.segment1 po_number
       ,aia.invoice_num invoice_number
       ,rsh.receipt_num receipt_number
  FROM po_headers_all pha
       ,po_distributions_all pda
       ,ap_invoice_distributions_all aid
       ,ap_invoices_all aia
       ,rcv_shipment_lines rsl
       ,rcv_shipment_headers rsh
 WHERE pha.po_header_id=pda.po_header_id
   AND aid.po_distribution_id=pda.po_distribution_id
   AND aia.invoice_id=aid.invoice_id
   AND rsl.po_header_id=pha.po_header_id
   AND rsl.shipment_header_id=rsh.shipment_header_id
   AND pha.segment1=nvl(:P_PO_NUM,pha.segment1)
   AND aia.invoice_num=nvl(:P_INVOICE_NUM,aia.invoice_num)
   AND rsh.receipt_num=nvl(:P_RECEIPT_NUM,rsh.receipt_num)
 order by 2 

No comments:

Post a Comment

How to improve blog performance

Improving the performance of a blog can involve a variety of strategies, including optimizing the website's technical infrastructure, im...