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
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 NameSQL textValidation
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