SELECT
inv1.payment_currency_code C_CURRENCY_CODE,
inv1.invoice_date C_INVOICE_DATE,
decode(:SORT_BY_ALTERNATE, 'Y', upper(hp.organization_name_phonetic), upper(hp.party_name)) C_SORT_VENDOR_NAME,
hp.party_name C_VENDOR_NAME,
inv1.invoice_num C_INVOICE_NUM,
inv1.invoice_id C_INVOICE_ID,
inv1.description C_DESCRIPTION,
inv1.pay_group_lookup_code C_PAY_GROUP,
DECODE(fc.derive_type,
'EURO', DECODE(FC.derive_type,
'EMU', 0,
'EURO', 0,
DECODE(exchange_rate,
NULL, 1,
0)),
'EMU', DECODE(FC.derive_type,
'EMU', 0,
'EURO', 0,
DECODE(exchange_rate,
NULL, 1,
0)),
DECODE(exchange_rate,
NULL, 1,
0)) C_NULL_RATE,
ps.due_date C_DUE_DATE,
sites.ALWAYS_TAKE_DISC_FLAG C_TAKE_DISC_FLAG,
PS.DISCOUNT_DATE C_DISC_DATE,
PS.SECOND_DISCOUNT_DATE C_SEC_DISC_DATE,
PS.THIRD_DISCOUNT_DATE C_THRD_DISC_DATE,
ps.amount_remaining C_AMT_REMAINING,
PS.GROSS_AMOUNT C_GROSS_AMOUNT,
PS.DISCOUNT_AMOUNT_AVAILABLE C_DISC_AMT,
PS.SECOND_DISC_AMT_AVAILABLE C_SEC_DISC_AMT,
PS.THIRD_DISC_AMT_AVAILABLE C_THRD_DISC_AMT,
exchange_rate C_EXCH_RATE,
inv1.payment_cross_rate C_PAYMENT_CROSS_RATE,
asp.base_currency_code c_base_currency_code,
inv1.org_id c_org_id,
hr.name c_org_name,
decode(inv1.wfapproval_status,'MANUALLY APPROVED',:C_NLS_YES,
'NOT REQUIRED',:C_NLS_YES,
'WFAPPROVED',:C_NLS_YES, :C_NLS_NO) c_wf_approval_status,
fc.minimum_accountable_unit c_base_min_account_unit,
fc.precision c_base_precision,
AP_APXCRRCR_XMLP_PKG.c_pay_dateformula(AP_APXCRRCR_XMLP_PKG.c_sort_pay_dateformula(sites.ALWAYS_TAKE_DISC_FLAG, ps.due_date, PS.DISCOUNT_DATE, PS.SECOND_DISCOUNT_DATE, PS.THIRD_DISCOUNT_DATE)) C_PAY_DATE,
AP_APXCRRCR_XMLP_PKG.c_sort_pay_dateformula(sites.ALWAYS_TAKE_DISC_FLAG, ps.due_date, PS.DISCOUNT_DATE, PS.SECOND_DISCOUNT_DATE, PS.THIRD_DISCOUNT_DATE) C_SORT_PAY_DATE,
AP_APXCRRCR_XMLP_PKG.c_amount_roundformula(inv1.payment_currency_code, AP_APXCRRCR_XMLP_PKG.c_amountformula(sites.ALWAYS_TAKE_DISC_FLAG, PS.DISCOUNT_AMOUNT_AVAILABLE, PS.DISCOUNT_DATE, PS.SECOND_DISCOUNT_DATE, PS.SECOND_DISC_AMT_AVAILABLE, PS.THIRD_DISCOUNT_DATE, PS.THIRD_DISC_AMT_AVAILABLE, PS.GROSS_AMOUNT, ps.amount_remaining)) C_AMOUNT_ROUND,
AP_APXCRRCR_XMLP_PKG.c_hold_countformula(inv1.invoice_id) C_HOLD_COUNT,
AP_APXCRRCR_XMLP_PKG.c_amountformula(sites.ALWAYS_TAKE_DISC_FLAG, PS.DISCOUNT_AMOUNT_AVAILABLE, PS.DISCOUNT_DATE, PS.SECOND_DISCOUNT_DATE, PS.SECOND_DISC_AMT_AVAILABLE, PS.THIRD_DISCOUNT_DATE, PS.THIRD_DISC_AMT_AVAILABLE, PS.GROSS_AMOUNT, ps.amount_remaining) C_AMOUNT,
AP_APXCRRCR_XMLP_PKG.c_base_currency_amountformula(sites.ALWAYS_TAKE_DISC_FLAG, PS.DISCOUNT_AMOUNT_AVAILABLE, PS.DISCOUNT_DATE, PS.SECOND_DISCOUNT_DATE, PS.SECOND_DISC_AMT_AVAILABLE, PS.THIRD_DISCOUNT_DATE, PS.THIRD_DISC_AMT_AVAILABLE, PS.GROSS_AMOUNT, ps.amount_remaining, fc.minimum_accountable_unit, exchange_rate, inv1.payment_cross_rate, fc.precision) C_BASE_CURRENCY_AMOUNT,
AP_APXCRRCR_XMLP_PKG.c_approved_statusformula(inv1.invoice_id) C_APPROVED_STATUS
FROM ap_supplier_sites_all sites,
ap_invoices_all inv1,
ap_payment_schedules ps,
ap_suppliers suppliers,
hz_parties hp,
fnd_currencies fc,
ap_system_parameters asp,
hr_operating_units hr
WHERE ((TRUNC(due_date) <= :p_pay_through_date_v and
TRUNC(due_date) >= nvl(:c_pay_from_date,trunc(due_date)))
OR
DECODE(NVL(sites.pay_date_basis_lookup_code,'DISCOUNT'),
'DISCOUNT',
DECODE(sites.always_take_disc_flag,
'Y', ps.discount_date,
DECODE(SIGN(:p_check_date_v
-NVL(ps.discount_date,
:p_check_date_v+1)-1),
-1, ps.discount_date,
DECODE(SIGN(:p_check_date_v
-NVL(ps.second_discount_date,
:p_check_date_v+1)-1),
-1, ps.second_discount_date,
DECODE(SIGN(:p_check_date_v
-NVL(ps.third_discount_date,
:p_check_date_v+1)-1),
-1, ps.third_discount_date,
TRUNC(ps.due_date))))),
TRUNC(due_date))
BETWEEN DECODE(sites.always_take_disc_flag,'Y',
nvl(:c_pay_from_date, TO_DATE('1901','YYYY')),
:p_check_date_v)
AND :c_disc_pay_thru_date)
AND ps.payment_status_flag BETWEEN 'N' AND 'P'
AND (ps.checkrun_id is null and nvl(:p_include_selected,'Y') = 'Y')
and inv1.org_id = hr.organization_id
AND inv1.org_id = asp.org_id
and inv1.invoice_currency_code = fc.currency_code
AND inv1.payment_status_flag BETWEEN 'N' AND 'P'
AND NVL(ps.payment_priority, 99) BETWEEN :c_hi_payment_priority
AND :c_low_payment_priority
AND inv1.cancelled_date is null
AND hp.party_id = inv1.party_id
AND NVL(ps.hold_flag, 'N') = 'N'
AND NVL(sites.hold_all_payments_flag, 'N') = 'N'
AND inv1.invoice_id = ps.invoice_id
AND sites.vendor_id(+) = inv1.vendor_id
AND sites.vendor_site_id(+) = inv1.vendor_site_id
AND suppliers.vendor_id(+) = inv1.vendor_id
AND fv_econ_benf_disc.ebd_check(null, inv1.invoice_id,
:p_check_date_v, due_date, ps.discount_amount_available, ps.discount_date) = 'Y'
AND inv1.vendor_id = nvl(:c_vendor_id, inv1.vendor_id)
AND inv1.party_id = nvl(:c_party_id, inv1.party_id)
AND ((inv1.exchange_rate_type = 'USER' and :c_inv_exchange_rate_type = 'IS_USER')
or (:c_inv_exchange_rate_type = 'IS_NOT_USER' and inv1.exchange_rate_type <> 'USER')
or (inv1.exchange_rate_type is null)
or (:c_inv_exchange_rate_type is null))
AND ps.payment_method_code = nvl(:c_payment_method_code, ps.payment_method_code)
AND nvl(suppliers.vendor_type_lookup_code,-99) =
nvl(:c_vendor_type_lookup_code, nvl(suppliers.vendor_type_lookup_code,-99))
AND (inv1.legal_entity_id in (select legal_entity_id
from ap_le_group
where template_id = :p_template_id)
or :c_le_group_option = 'ALL')
AND (inv1.org_id in (select org_id
from AP_OU_GROUP
where template_id = :p_template_id)
or :c_ou_group_option = 'ALL')
AND (inv1.payment_currency_code in (select currency_code
from AP_CURRENCY_GROUP
where template_id = :p_template_id)
or :c_currency_group_option = 'ALL')
AND (inv1.pay_group_lookup_code in (select vendor_pay_group
from AP_PAY_GROUP
where template_id = :p_template_id)
or :c_pay_group_option = 'ALL')
AND ((:c_zero_invoices_allowed = 'N' AND ps.amount_remaining <> 0) OR
:c_zero_invoices_allowed = 'Y')
&C_UNAPPROVED_PREDICATE
&C_UNVALIDATED_PREDICATE
ORDER BY
inv1.payment_currency_code, ps.due_date,
DECODE(:SORT_BY_ALTERNATE, 'Y', UPPER(hp.organization_name_phonetic), UPPER(hp.party_name)),
invoice_num |
