AP Cash Requirement

➜ Back to Library
Description
Categories: Enginatics
Repository: Github
Detail cash requirement report showing all unpaid or partially paid amounts, where the invoice is neither on hold nor cancelled, including invoice currency amount, exchange rate, and currency code
Run AP Cash Requirement and other Oracle EBS reports with Blitz Report™ on our demo environment
select
apsa.due_date,
hp.party_name trading_partner,
aia.invoice_num,
aia.invoice_date,
ap_apxcrrcr_xmlp_pkg.c_amountformula(assa.always_take_disc_flag, apsa.discount_amount_available, apsa.discount_date, apsa.second_discount_date, apsa.second_disc_amt_available, apsa.third_discount_date, apsa.third_disc_amt_available, apsa.gross_amount, apsa.amount_remaining) amount,
aia.payment_currency_code payment_currency,
aia.pay_group_lookup_code pay_group,
xxen_util.ap_invoice_status(aia.invoice_id,aia.invoice_amount,aia.payment_status_flag,aia.invoice_type_lookup_code,aia.validation_request_id) invoice_status,
xxen_util.meaning(case when aia.wfapproval_status in('MANUALLY APPROVED','NOT REQUIRED','WFAPPROVED') then 'Y' else 'N' end,'YES_NO',0) approved,
xxen_util.meaning(ap_invoices_utility_pkg.get_payment_status(aia.invoice_id),'INVOICE PAYMENT STATUS',200) payment_status,
aia.description,
decode(aia.holds_count,0,null,aia.holds_count) holds_count,
trunc(apsa.due_date-sysdate) days_to_due,
apsa.future_pay_due_date,
apsa.gross_amount,
apsa.hold_flag,
xxen_util.meaning(apsa.payment_method_lookup_code,'PAYMENT METHOD',200) payment_method_lookup_code,
apsa.payment_priority,
apsa.discount_date,
apsa.second_discount_date,
apsa.third_discount_date,
apsa.discount_amount_available,
apsa.second_disc_amt_available,
apsa.third_disc_amt_available,
apsa.discount_amount_remaining,
apsa.inv_curr_gross_amount,
aia.exchange_rate,
aia.payment_cross_rate,
ap_apxcrrcr_xmlp_pkg.c_base_currency_amountformula(
assa.always_take_disc_flag,
apsa.discount_amount_available,
apsa.discount_date,
apsa.second_discount_date,
apsa.second_disc_amt_available,
apsa.third_discount_date,
apsa.third_disc_amt_available,
apsa.gross_amount,
apsa.amount_remaining,
fc.minimum_accountable_unit,
aia.exchange_rate,
aia.payment_cross_rate,
fc.precision) base_currency_amount,
aspa.base_currency_code,
fc.minimum_accountable_unit base_min_unit,
fc.precision base_precision,
sum(case when aia.invoice_currency_code=aspa.base_currency_code or fc.derive_type in ('EURO','EMU') or aia.exchange_rate is not null then 0 else 1 end) over (partition by aia.invoice_currency_code) num_invoices_no_rate,
aia.invoice_currency_code,
gl.name ledger,
hou.name operating_unit,
aia.invoice_id
from
gl_ledgers gl,
hr_operating_units hou,
ap_system_parameters_all aspa,
ap_payment_schedules_all apsa,
(
select
(select count(*) from ap_holds_all aha where aia.invoice_id=aha.invoice_id and aha.release_lookup_code is null) holds_count,
aia.*
from
ap_invoices_all aia
) aia,
fnd_currencies fc,
hz_parties hp,
ap_suppliers aps,
ap_supplier_sites_all assa
&apt_table
where
1=1 and
apsa.payment_status_flag in ('N','P') and
aia.payment_status_flag in ('N','P') and
aia.cancelled_date is null and
nvl(apsa.hold_flag,'N')='N' and
nvl(assa.hold_all_payments_flag,'N')='N' and
gl.ledger_id=hou.set_of_books_id and
hou.set_of_books_id=aspa.set_of_books_id and
hou.organization_id=aspa.org_id and
hou.organization_id=apsa.org_id and
apsa.invoice_id=aia.invoice_id and
aia.invoice_currency_code=fc.currency_code and
aia.party_id=hp.party_id and
aia.vendor_id=aps.vendor_id(+) and
aia.vendor_id=assa.vendor_id(+) and
aia.vendor_site_id=assa.vendor_site_id(+)
order by
hou.name,
aia.invoice_currency_code,
apsa.due_date,
hp.party_name,
aia.invoice_num
Parameter Name SQL text Validation
Include Unvalidated Invoices 
aia.wfapproval_status in ('NOT REQUIRED', 'WFAPPROVED', 'MANUALLY APPROVED')
 LOV Oracle
Include Unapproved Invoices 
nvl(ap_invoices_pkg.get_approval_status(aia.invoice_id,aia.invoice_amount,aia.payment_status_flag,aia.invoice_type_lookup_code),'n/a')not in('NEVER APPROVED', 'NEEDS REAPPROVAL', 'UNAPPROVED')
 LOV Oracle
Pay Through Date 
:pay_through_date=:pay_through_date
 Date
Payment Date 
:payment_date=:payment_date
 Date
Template 
,(
select
:template_id template_id_,
nvl(to_date(:payment_date),sysdate+nvl(decode(apt.payment_date_option,'EXTRADAYS',apt.addl_payment_days),0)) payment_date,
nvl(:pay_through_date, sysdate+apt.addl_pay_thru_days) pay_through_date,
sysdate-apt.addl_pay_from_days pay_from_date,
decode(apt.pay_only_when_due_flag,'Y',to_date('01/01/80','MM/DD/RR'),nvl(:pay_through_date,sysdate+apt.addl_pay_thru_days)) disc_pay_thru_date,
apt.*
from
ap_payment_templates apt
where
apt.template_id=:template_id
) apt
 LOV Oracle
Exclude Selected Invoices 
apsa.checkrun_id is null
 LOV Oracle
Supplier 
aps.vendor_name=:vendor_name
 LOV
Supplier Number 
aps.segment1=:supplier_number
 LOV
Supplier Type 
aps.vendor_type_lookup_code=xxen_util.lookup_code('Employee','VENDOR TYPE',201)
 LOV
Due Date From 
apsa.due_date>=:due_date_from
 Date
Due Date To 
apsa.due_date<:due_date_to+1
 Date
Payment Priority From 
nvl(apsa.payment_priority,99)<=:payment_priority_from
 Number
Payment Priority To 
nvl(apsa.payment_priority,99)>=:payment_priority_to
 Number
Economically Beneficial 
fv_econ_benf_disc.ebd_check(null, aia.invoice_id, :payment_date, apsa.due_date, apsa.discount_amount_available, apsa.discount_date)='Y'
 LOV
Operating Unit 
hou.name=:operating_unit
 LOV
Download
 
Related Blitz Reports
GL Account Analysis COPY OF: GL Account Analysis GL Account Analysis (Distributions) AP Invoice on Hold AP Accounted Invoice Aging AP Cash Requirement - draft AP Invoices and Lines XXSJ Report VAT Register INV Intercompany Invoice Reconciliation
Blitz Report™

Blitz Report™ provides multiple benefits: