AP Cash Requirement - draft
Description
Categories: BI Publisher
Application: Payables
Source: Cash Requirement Report (XML) - Not Supported: Reserved For Future Use
Short Name: APXCRRCR_XML
DB package: AP_APXCRRCR_XMLP_PKG
Source: Cash Requirement Report (XML) - Not Supported: Reserved For Future Use
Short Name: APXCRRCR_XML
DB package: AP_APXCRRCR_XMLP_PKG
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 |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Include Unvalidated Invoices | LOV Oracle | ||
Include Unapproved Invoices | LOV Oracle | ||
Pay Through Date | Date | ||
Payment Date | Date | ||
Template | LOV Oracle | ||
Summary Option | LOV Oracle | ||
Include Selected Invoices | LOV Oracle |