Tuesday, June 21, 2011

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

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect