SWEEP_TRX_QUERY

SELECT   distinct  hou.name,
                  a.org_id,
                  a.invoice_id,
                  a.INVOICE_NUM,
                  a.PAYMENT_CURRENCY_CODE,
                  a.AMOUNT_PAID,
                  pov.VENDOR_NAME,
                  a.INVOICE_TYPE_LOOKUP_CODE,
                  a.DOC_SEQUENCE_VALUE,
                  a.VOUCHER_NUM,
                  a.POSTING_STATUS,
                  a.PAYMENT_STATUS_FLAG,
                  a.SOURCE,
                  pov.SEGMENT1 VENDOR_NUM,
                  a.INVOICE_DATE,
                  a.INVOICE_AMOUNT,
                  a.gl_date
                  --,b.ACCOUNTING_DATE
  FROM   ap_invoices_all a,
        -- ap_invoice_distributions_all b,
         po_vendors pov,
         hr_operating_units hou
 WHERE   a.vendor_id = pov.vendor_id    
         --AND a.INVOICE_ID = b.INVOICE_ID        
         AND a.org_id = hou.organization_id
         AND exists (  select 1 from ap_invoice_distributions_all B
                    WHERE b.ACCOUNTING_DATE BETWEEN '01-APR-2011' AND '13-AUG-2011'
                    AND   UPPER(b.LINE_TYPE_LOOKUP_CODe) NOT LIKE '%PRE%PAY%'
                    and   b.invoice_id = a.invoice_id
                    )
--                    AND  a.gl_date <= '31-MAR-2011'
         AND a.SET_OF_BOOKS_ID = 5

---------------------   THANK TO MANOJ SIR  ---------------------------