Tuesday 8 July 2014

Expense Invoices Query

/* Formatted on 7/8/2014 8:48:23 AM (QP5 v5.115.810.9015) */
SELECT sob.name set_of_books,
       flv.meaning TYPE,
       ven.vendor_name supplier,
       ven.segment1 supplier_num,
       pvs.vendor_site_code site,
       inv.invoice_date,
       inv.terms_date receipt_date,
       inv.invoice_num,
       inv.invoice_currency_code,
       inv.invoice_amount,
       inv.base_amount functional_amount,
       inv.doc_sequence_value voucher_number,
       NVL (inv.tax_amount, 0) tax_amount,
       DECODE (inv.auto_tax_calc_flag,
          'Y', 'Header Level',
          'L', 'Line Level',
          'N', 'None',
          'T', 'Tax Code Level',
          NULL)
          tax_calculation_level,
       flv2.meaning payment_method,
       inv.gl_date,
       att.name terms,
       inv.pay_group_lookup_code,
       inv.exclusive_payment_flag pay_alone,
       (SELECT (SUM (NVL (amount, 0)))
        FROM ap_invoice_distributions_all
        WHERE invoice_id = inv.invoice_id AND line_type_lookup_code = 'AWT')
          withheld_amount,
       (SELECT (SUM (NVL (amount, 0)))
        FROM ap_invoice_distributions_all
        WHERE invoice_id = inv.invoice_id
              AND (line_type_lookup_code = 'PREPAY'
                   OR line_type_lookup_code = 'TAX'
                     AND prepay_tax_parent_id IS NOT NULL))
          prepaid_amount,
          gcc.segment1
       || '.'
       || gcc.segment2
       || '.'
       || gcc.segment3
       || '.'
       || gcc.segment5
       || '.'
       || gcc.segment4
       || '.'
       || gcc.segment6
          liability_account,
       inv.payment_currency_code,
       inv.payment_cross_rate_date,
       inv.description
FROM ap_invoices_all inv,
     fnd_lookup_values flv,
     fnd_lookup_values flv2,
     po_vendors ven,
     po_vendor_sites_all pvs,
     gl_sets_of_books sob,
     ap_terms_tl att,
     gl_code_combinations gcc
WHERE     1 = 1
      --AND inv.org_id IN (543, 718)
      AND flv.lookup_type = 'INVOICE TYPE'
      AND flv.view_application_id = 200
      AND flv.lookup_code = inv.invoice_type_lookup_code
      AND inv.invoice_type_lookup_code <> 'DEBIT'
      AND flv2.lookup_type = 'PAYMENT METHOD'
      AND flv2.view_application_id = 200
      AND flv2.lookup_code = inv.payment_method_lookup_code
      AND ven.vendor_id = inv.vendor_id
      AND pvs.vendor_id = inv.vendor_id
      AND pvs.vendor_site_id = inv.vendor_site_id
      AND sob.set_of_books_id = inv.set_of_books_id
      AND att.term_id = inv.terms_id
      AND att.language = 'US'
      AND gcc.code_combination_id = inv.accts_pay_code_combination_id
      AND gcc.segment1 = '2'
      AND inv.invoice_date BETWEEN '01-JAN-2009' AND '31-MAR-2010'
ORDER BY gl_date, invoice_num

No comments:

Post a Comment