Reports2017-11-18T12:27:27+00:00

AP Cash Requirement

Description
Categories: Enginatics, Financials, Procurement
AP cash requirement / payment schedules

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,
ap_invoices_utility_pkg.get_payment_status(aia.invoice_id) 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,
haou.name operating_unit,
aia.invoice_id
from
hr_all_organization_units haou,
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 asu,
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
haou.organization_id=aspa.org_id and
haou.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=asu.vendor_id(+) and
aia.vendor_id=assa.vendor_id(+) and
aia.vendor_site_id=assa.vendor_site_id(+)
order by
haou.name,
aia.invoice_currency_code,
apsa.due_date,
hp.party_name,
aia.invoice_num

Parameter Name SQL text Validation
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
haou.name=:operating_unit
LOV
Supplier
asu.vendor_name=:vendor_name
LOV
Template
nvl(apsa.payment_priority,99) between apt.hi_payment_priority and apt.low_payment_priority and
(apt.vendor_type_lookup_code is null or apt.vendor_type_lookup_code=asu.vendor_type_lookup_code) and
(apt.vendor_id is null or apt.vendor_id=aia.vendor_id) and
(apt.party_id is null or apt.party_id=aia.party_id) and
(apt.payment_method_code is null or apt.payment_method_code=apsa.payment_method_code) and
(
aia.exchange_rate_type='USER' and apt.inv_exchange_rate_type='IS_USER' or
aia.exchange_rate_type<>'USER' and apt.inv_exchange_rate_type = 'IS_NOT_USER' or
aia.exchange_rate_type is null or
apt.inv_exchange_rate_type is null
) and
(apt.zero_inv_allowed_flag='Y' or apsa.amount_remaining<>0) and
(apt.pay_group_option='ALL' or aia.pay_group_lookup_code in (select apg.vendor_pay_group from ap_pay_group apg where apt.template_id=apg.template_id)) and
(apt.currency_group_option='ALL' or aia.payment_currency_code in (select acg.currency_code from ap_currency_group acg where apt.template_id=acg.template_id)) and
(apt.le_group_option='ALL' or aia.legal_entity_id in (select alg.legal_entity_id from ap_le_group alg where apt.template_id=alg.template_id)) and
(apt.ou_group_option='ALL' or aia.org_id in (select aog.org_id from ap_ou_group aog where apt.template_id=aog.template_id)) and
(
trunc(apsa.due_date)<=apt.pay_through_date and
trunc(apsa.due_date)>=nvl(apt.pay_from_date,trunc(due_date))
or
decode(nvl(assa.pay_date_basis_lookup_code,'DISCOUNT'),'DISCOUNT',
decode(assa.always_take_disc_flag,'Y',apsa.discount_date,
case
when apt.payment_date<nvl(apsa.discount_date,apt.payment_date+1)-1 then apsa.discount_date
when apt.payment_date<nvl(apsa.second_discount_date,apt.payment_date+1)-1 then apsa.second_discount_date
when apt.payment_date<nvl(apsa.third_discount_date,apt.payment_date+1)-1 then apsa.third_discount_date
else trunc(apsa.due_date)
end
),
trunc(apsa.due_date)
) between decode(assa.always_take_disc_flag,'Y',nvl(apt.pay_from_date,to_date('1901','YYYY')),apt.payment_date) and apt.disc_pay_thru_date
)
Payment Priority To
nvl(apsa.payment_priority,99)>=:payment_priority_to
Number
Payment Priority From
nvl(apsa.payment_priority,99)<=:payment_priority_from
Number
Due Date To
apsa.due_date<:due_date_to+1
Date
Due Date From
apsa.due_date>=:due_date_from
Date
Supplier Type
asu.vendor_type_lookup_code=xxen_util.lookup_code('Employee','VENDOR TYPE',201)
LOV
Exclude Selected Invoices
apsa.checkrun_id is null
LOV Oracle
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
Payment Date
:payment_date=:payment_date
Date
Pay Through Date
:pay_through_date=:pay_through_date
Date
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
Include Unvalidated Invoices
aia.wfapproval_status in ('NOT REQUIRED', 'WFAPPROVED', 'MANUALLY APPROVED')
LOV Oracle

By continuing to use the site, you agree to the use of cookies. Accept