AP Payment Exceptions - draft

Description
Categories: BI Publisher
Columns: C Bank Name, C Upper Bank Name, C Bank Branch Name, C Bank Account Name, C Check Stock Name, C Check Stock Id, C Check Number, C Vendor Name, C Amount, C Check Date ...
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
Bank Branch (skip for All)
 
LOV Oracle
Bank Account (skip for All)
 
LOV Oracle
Payment Document (skip for All)
 
LOV Oracle
Begin Document Number
 
End Document Number
 
Begin Date
 
Date
End Date
 
Date
Payment Exception (skip for All)
 
LOV Oracle
Ledger