AP Cash Requirement

Description
Categories: Enginatics, Toolkit - Operations
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