AP Payment Exceptions Report- Not Supported: Reserved For Future Use
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Payment Exceptions Report
Application: Payables
Source: Payment Exceptions Report (XML) - Not Supported: Reserved For Future Use
Short Name: APXCHECC_XML
DB package: AP_APXCHECC_XMLP_PKG
Description: Payment Exceptions Report
Application: Payables
Source: Payment Exceptions Report (XML) - Not Supported: Reserved For Future Use
Short Name: APXCHECC_XML
DB package: AP_APXCHECC_XMLP_PKG
Run
AP Payment Exceptions Report- Not Supported: Reserved For Future Use and other Oracle EBS reports with Blitz Report™ on our demo environment
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 | |
---|---|---|---|
Operating Unit |
|
LOV | |
Bank Branch (skip for All) |
|
LOV Oracle | |
Bank Account (skip for All) |
|
LOV Oracle | |
Payment Document (skip for All) |
|
LOV Oracle | |
Begin Document Number |
|
Char | |
End Document Number |
|
Char | |
Begin Date |
|
Date | |
End Date |
|
Date | |
Payment Exception (skip for All) |
|
LOV Oracle |