AP Payment Exceptions - draft

Description
Categories: BI Publisher, Financials, Procurement
Application: Payables
Source: Payment Exceptions Report (XML) - Not Supported: Reserved For Future Use
Short Name: APXCHECC_XML
DB package: AP_APXCHECC_XMLP_PKG
SELECT BB.bank_name C_BANK_NAME,
       UPPER(BB.bank_name) C_UPPER_BANK_NAME,
       BB.bank_branch_name C_BANK_BRANCH_NAME,
       C.bank_account_name C_BANK_ACCOUNT_NAME,
       nvl(cpd.payment_document_name, :c_nls_none_ep) C_CHECK_STOCK_NAME,
       cpd.payment_document_id C_CHECK_STOCK_ID,
       C.check_number C_CHECK_NUMBER,
       C.vendor_name C_VENDOR_NAME,
       C.amount C_AMOUNT,
       C.check_date C_CHECK_DATE,
      decode(sign(C.cleared_date - nvl(:lp_end_date, C.cleared_date+1)),
                     1, NULL, C.cleared_amount) C_CLEARED_AMOUNT,
       decode(sign(C.cleared_date - nvl(:lp_end_date, C.cleared_date+1)),
                     1, NULL, C.cleared_date) C_CLEARED_DATE,
       C.amount - nvl(decode(sign(C.cleared_date - nvl(:lp_end_date,
                                                                                               C.cleared_date+1)),
                                                1, NULL, C.cleared_amount), 0) C_VARIANCE,
       decode( sign(C.amount - nvl(decode(sign(C.cleared_date - nvl(:lp_end_date,       
                                                                                 C.cleared_date+1)), 
                                                          1, NULL, C.cleared_amount),C.amount) ),
                      0, null,:c_nls_amount_difference) C_AMOUNT_EXCP,
       decode( sign(C.cleared_date - C.check_date),-1,
               :c_nls_cleared_before_issued,null) C_DATE_EXCP,
       decode(void_date,null,null, decode(decode(sign(C.cleared_date -
                                                          nvl(:lp_end_date, C.cleared_date+1)),
                                                         1, NULL, C.cleared_date),null,null,
                     :c_nls_voided_and_cleared) ) C_VOID_EXCP,
       decode(C.cleared_date,null, 
                   decode(C.cleared_amount,null,:c_nls_payment_not_cleared,
                       :c_nls_clear_date_null),
                   decode(sign(C.cleared_date - nvl(:lp_end_date, C.cleared_date+1)),
                       1, :c_nls_payment_not_cleared,
                        decode(C.cleared_amount,null,:c_nls_clear_amount_null,null)))
       C_OUST_EXCP,
       BA.currency_code C_CURRENCY_CODE,
       C.currency_code C_CHECK_CURRENCY_CODE,
       BA.currency_code||' ('||substr(FA.name,1,15)||')' C_ACCOUNT_CURRENCY_DESC,
       C.currency_code||' ('||substr(FC.name,1,15)||')' C_CHECK_CURRENCY_DESC,
	AP_APXCHECC_XMLP_PKG.c_currency_descriptionformula(BA.currency_code) C_CURRENCY_DESCRIPTION,
	AP_APXCHECC_XMLP_PKG.c_exception_stringformula(decode ( sign ( C.amount - nvl ( decode ( sign ( C.cleared_date - nvl ( :lp_end_date , C.cleared_date + 1 ) ) , 1 , NULL , C.cleared_amount ) , C.amount ) ) , 0 , null , :c_nls_amount_difference ), decode ( sign ( C.cleared_date - C.check_date ) , - 1 , :c_nls_cleared_before_issued , null ), decode ( void_date , null , null , decode ( decode ( sign ( C.cleared_date - nvl ( :lp_end_date , C.cleared_date + 1 ) ) , 1 , NULL , C.cleared_date ) , null , null , :c_nls_voided_and_cleared ) ), decode ( C.cleared_date , null , decode ( C.cleared_amount , null , :c_nls_payment_not_cleared , :c_nls_clear_date_null ) , decode ( sign ( C.cleared_date - nvl ( :lp_end_date , C.cleared_date + 1 ) ) , 1 , :c_nls_payment_not_cleared , decode ( C.cleared_amount , null , :c_nls_clear_amount_null , null ) ) )) C_EXCEPTION_STRING
FROM ap_checks C,
       ce_bank_branches_v BB,
       ce_bank_accounts BA,
       ce_bank_acct_uses_all CBAU,
       ce_payment_documents cpd,
       fnd_currencies_vl FA,
       fnd_currencies_vl FC
 WHERE C.ce_bank_acct_use_id = CBAU.bank_acct_use_id
   and CBAU.bank_account_id  =  BA.bank_account_id
   and BA.bank_branch_id = BB.branch_party_id
   and C.payment_document_id(+) = cpd.payment_document_id
   and FA.currency_code = BA.currency_code
   and FC.currency_code = C.currency_code
   and (  (:LP_CHECK_EXC = 'All' and (
          (C.check_date > NVL(C.cleared_date, 
            to_date('01/01/1970','MM/DD/YYYY'))
	   and C.void_date is null)
        OR (C.void_date is not null and C.cleared_date is not null)
        OR (C.amount <> NVL(C.cleared_amount, 0) and C.void_date is null)
        OR (C.cleared_date > nvl(:p_end_date,  C.cleared_date+1)) 
		) )
        OR ((C.cleared_amount is null and C.cleared_date is null
            and C.void_date is null)  OR C.cleared_date > nvl(null, C.cleared_date+1))   )
   and BB.branch_party_id = decode(:LP_BRANCH_ID,'All',BB.branch_party_id,
                                    to_number(:LP_BRANCH_ID))
   and BA.bank_account_name = decode(:LP_BANK_ACCOUNT_NAME,'All',
                               BA.bank_account_name, :LP_BANK_ACCOUNT_NAME)
   and BA.account_classification = 'INTERNAL'
   and cpd.payment_document_name = decode(:LP_CHECK_STOCK,'All', 
                                                          cpd.payment_document_name, :LP_CHECK_STOCK)
   and C.status_lookup_code not in ('SPOILED', 'SET UP', 'OVERFLOW')
   and C.check_date >= nvl(:P_BEGIN_DATE,C.check_date -1)
   and C.check_date <= nvl(:p_end_date,C.check_date +1)
   and C.check_number >= nvl(:p_begin_number,0)
   and C.check_number <= nvl(:p_end_number,9999999999999999)
ORDER BY 2 ASC,3 ASC,4 ASC,6 ASC,19 ASC ,UPPER(BB.bank_name), 
         BB.bank_branch_name,
         BA.bank_account_name, 
         cpd.payment_document_name,
         C.currency_code, 
         C.check_number
Parameter Name SQL text Validation
Ledger
 
Payment Exception (skip for All)
 
LOV Oracle
End Date
 
Date
Begin Date
 
Date
End Document Number
 
Begin Document Number
 
Payment Document (skip for All)
 
LOV Oracle
Bank Account (skip for All)
 
LOV Oracle
Bank Branch (skip for All)
 
LOV Oracle