1 Reply Latest reply on May 24, 2009 2:25 PM by 578444

    Matched PO receipts query

    468695
      Dear All,

      I have to develop a report for "Matched PO receipts" with the following columns:

      Receipt Date
      Receipt Number
      PO Number
      Supplier Code
      Supplier Name
      Amount

      Please look at this query, Could this solved my problem?

      select to_char(rt.transaction_date,'yyyy-mm-dd')rct_date
      ,rsh.receipt_num rct_number
      ,pha.segment1 po_number
      ,rsh.vendor_id
      ,pv.vendor_name
      ,pvsa.vendor_site_code vendor_site
      ,sum(rt.amount_billed)amount
      from rcv_transactions rt
      ,rcv_shipment_lines rsl
      ,rcv_shipment_headers rsh
      ,po_headers_all pha
      ,po_vendors pv
      ,po_vendor_sites_all pvsa
      ,po_distributions_all pda
      ,ap_invoice_distributions_all aida
      where rt.shipment_line_id = rsl.shipment_line_id
      and rsl.shipment_header_id = rsh.shipment_header_id
      and rt.po_header_id = pha.po_header_id
      and rsh.vendor_id = pvsa.vendor_id
      and rsh.vendor_site_id = pvsa.vendor_site_id
      and pvsa.vendor_id = pv.vendor_id
      and rt.po_header_id = pda.po_header_id
      and pda.po_distribution_id = aida.po_distribution_id
      and rt.transaction_id = aida.rcv_transaction_id
      and to_char(rt.transaction_date ,'yyyy/mm/dd')||' 00:00:00' between :p_date_from and :p_date_to
      and rt.organization_id = :p_unit
      group by to_char(rt.transaction_date,'yyyy-mm-dd')
      ,pha.segment1
      ,rsh.receipt_num
      ,rsh.vendor_id
      ,pv.vendor_name
      ,pvsa.vendor_site_code
      order by 1,to_number(rsh.receipt_num)