Script to extract expense invoices for a Company for given period Query

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.invoice_num IN ('91000795' ,'5084' ,'966601109151 - CN' ,'199545' ,'199547' ,'INV0538' ,'46249' ,'2098' )*/

  --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