Oracle Cloud Fusion R13 Account Payable (AP) Expenses SQL

=======================================================

Detail AP expense report listing corresponding AP invoices with status,

line details, expense type and expense justification. 

Including projects task related expenses.

====================================================================

select

gl.name ledger,

gl.currency_code ledger_currency,

hou.name operating_unit,

asu.vendor_name supplier,

asu.segment1 supplier_number,

--xxen_util.meaning(asu.vendor_type_lookup_code,'VENDOR TYPE',201) supplier_type,

--xxen_util.meaning(aia.invoice_type_lookup_code,'INVOICE TYPE',200) invoice_type,

aia.invoice_date,

aia.invoice_num invoice_number,

aia.description invoice_description,

decode(aipa.invoice_payment_id,max(aipa.invoice_payment_id) over (partition by aipa.invoice_id),aia.invoice_amount) invoice_amount,

decode(aipa.invoice_payment_id,max(aipa.invoice_payment_id) over (partition by aipa.invoice_id),decode(aia.invoice_currency_code,gl.currency_code,aia.invoice_amount,aia.base_amount)) invoice_amount_functional,

decode(aipa.invoice_payment_id,max(aipa.invoice_payment_id) over (partition by aipa.invoice_id),aia.invoice_amount)*decode(aca.currency_code,'USD',1,gdr.conversion_rate) invoice_amount_usd,

aia.amount_paid invoice_amount_paid,

sum(apsa.amount_remaining) over (partition by apsa.invoice_id) invoice_amount_remaining,

aia.invoice_currency_code invoice_currency,

atv.name payment_term,

trunc(max(aca.check_date) over (partition by aipa.invoice_id)-aia.invoice_date) days_to_pay,

ipmv.payment_method_name payment_method,

aca.check_number document_number,

trunc(aca.check_date) payment_date,

aipa.accounting_date gl_date,

--xxen_util.meaning(aca.status_lookup_code,'CHECK STATE',200) check_state,

decode(aipa.invoice_payment_id,max(aipa.invoice_payment_id) over (partition by aipa.check_id),aca.amount) check_amount,

decode(aipa.invoice_payment_id,max(aipa.invoice_payment_id) over (partition by aipa.check_id),decode(aca.currency_code,gl.currency_code,aca.amount,aca.base_amount)) check_amount_functional,

decode(aipa.invoice_payment_id,max(aipa.invoice_payment_id) over (partition by aipa.check_id),aca.amount*decode(aca.currency_code,'USD',1,gdr.conversion_rate)) check_amount_usd,

decode(aipa.invoice_payment_id,max(aipa.invoice_payment_id) over (partition by aipa.check_id),aca.cleared_amount) check_cleared_amount,

decode(aipa.invoice_payment_id,max(aipa.invoice_payment_id) over (partition by aipa.check_id),aca.cleared_amount*nvl(aca.exchange_rate,1))  check_cleared_amount_funct,

decode(aipa.invoice_payment_id,max(aipa.invoice_payment_id) over (partition by aipa.check_id),aca.cleared_amount*decode(aca.currency_code,'USD',1,gdr.conversion_rate)) check_cleared_amount_usd,

aipa.amount payment_amount,

decode(aca.currency_code,gl.currency_code,aipa.amount,aipa.payment_base_amount) payment_amount_functional,

aipa.amount*decode(aca.currency_code,'USD',1,gdr.conversion_rate) payment_amount_usd,

aipa.discount_taken,

aipa.discount_lost,

aca.currency_code payment_currency,

cbbv.bank_name,

cbbv.eft_swift_code swift_code,

cba.bank_account_name,

cba.bank_account_num,

cba.iban_number,

aipa.exchange_date,

nvl(aca.exchange_rate,1) exchange_rate,

aipa.exchange_rate_type,

--decode(aipa.reversal_flag,'Y',xxen_util.meaning(aipa.reversal_flag,'YES_NO',0)) reversal_flag,

--decode(aipa.assets_addition_flag,'Y',xxen_util.meaning(aipa.reversal_flag,'YES_NO',0)) assets_addition_flag,

(select gjb.name from gl_je_batches gjb where aipa.je_batch_id=gjb.je_batch_id) je_batch_name,

count(*) over (partition by aipa.invoice_id) payments_per_invoice,

count(*) over (partition by aipa.check_id) payments_per_check,

--xxen_util.user_name(aipa.created_by) created_by,

--xxen_util.client_time(aipa.creation_date) creation_date,

--xxen_util.user_name(aipa.last_updated_by) last_updated_by,

--xxen_util.client_time(aipa.last_update_date) last_update_date,

aipa.payment_num payment_number,

aipa.invoice_payment_id,

aipa.check_id,

aipa.invoice_id

from

gl_ledgers gl,

hr_operating_units hou,

ap_invoice_payments_all aipa,

ap_checks_all aca,

gl_daily_rates gdr,

ap_invoices_all aia,

ap_suppliers asu,

ap_terms_vl atv,

ce_bank_acct_uses_all cbaua,

ce_bank_accounts cba,

ce_bank_branches_v cbbv,

iby_payment_methods_vl ipmv,

gl_code_combinations gcck,

ap_payment_schedules_all apsa

where

1=1 and

gl.ledger_id=aipa.set_of_books_id and

hou.organization_id=aipa.org_id and

aipa.check_id=aca.check_id and

aca.currency_code=gdr.from_currency(+) and

gdr.to_currency(+)='USD' and

aca.check_date=gdr.conversion_date(+) and

gdr.conversion_type(+)='Corporate' and

aipa.invoice_id=aia.invoice_id and

aia.vendor_id=asu.vendor_id and

asu.terms_id=atv.term_id(+) and

aca.ce_bank_acct_use_id=cbaua.bank_acct_use_id(+) and

cbaua.bank_account_id=cba.bank_account_id(+) and

cba.bank_branch_id=cbbv.branch_party_id(+) and

aca.payment_method_code=ipmv.payment_method_code(+) and

aipa.asset_code_combination_id=gcck.code_combination_id(+) and

aipa.invoice_id=apsa.invoice_id and

aipa.payment_num=apsa.payment_num

order by

aipa.invoice_id desc,

aipa.invoice_payment_id desc\