AP Cash Requirement - draft

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