AP Bank Account Listing - draft

Description
Categories: BI Publisher, Financials, Procurement
Application: Payables
Source: Bank Account Listing (XML) - Not Supported: Reserved For Future Use
Short Name: APXBABAL_XML
DB package: AP_APXBABAL_XMLP_PKG
SELECT  decode(upper(:P_ORDER_BY_PAR), 	    	'BANK,BRANCH,ACCOUNT NAME','NO SORT',
					   upper(ba.bank_account_name)
		       ) C_BANK_SORT_ACCOUNT_NAME,
	upper(bb.bank_name) C_SORT_BANK_NAME,
        bb.bank_name C_BANK_NAME,
        upper(bb.bank_branch_name) C_SORT_BRANCH_NAME,
        bb.bank_branch_name C_BRANCH_NAME,
	upper(ba.bank_account_name) C_SORT_ACCOUNT_NAME,
	ba.bank_account_name C_ACCOUNT_NAME,
        bb.branch_party_id C_BRANCH_ID,
        ba.bank_account_id C_ACCOUNT_ID,
        ba.bank_account_num C_BANK_ACCOUNT_NUMBER,
        ba.description C_DESCRIPTION,
        ba.end_date C_INACTIVE_DATE,
        ba.currency_code C_CURRENCY_CODE,
        ba.max_check_amount C_MAX_CHECK_AMOUNT,
        ba.min_check_amount C_MIN_CHECK_AMOUNT,
        cs.name C_DOCUMENT_NAME,
        cs.disbursement_type_lookup_code C_DISBURSEMENT_CODE,
	la.displayed_field C_DISBURSEMENT_TYPE,
        cs.num_setup_checks C_NUM_SETUP_CHECKS,
        cs.last_document_num C_LAST_DOCUMENT_USED,
        cs.last_available_document_num C_LAST_AVAILABLE_DOC_NUM,
        cs.inactive_date C_STOCK_INACTIVE_DATE,
	--null C_FLEXDATA,
	--null C_FLEXDATA2, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flexfield', 'SQLGL', 'GL#', GLCC.CHART_OF_ACCOUNTS_ID, NULL, GLCC.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') C_FLEXFIELD, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flexfield2', 'SQLGL', 'GL#', GLCC2.CHART_OF_ACCOUNTS_ID, NULL, GLCC2.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') C_FLEXFIELD2
FROM    ce_bank_branches_v bb,
        ce_bank_accounts ba,
      ce_bank_acct_uses_all cbau,
     ce_gl_accounts_ccid cgac,
        gl_code_combinations glcc,
        gl_code_combinations glcc2,
        ap_check_stocks cs,
	ap_lookup_codes la
WHERE   ba.bank_branch_id = bb.branch_party_id
AND     cs.bank_account_id (+) = ba.bank_account_id
AND    ba.bank_account_id = cbau.bank_account_id
AND   cbau.bank_acct_use_id = cgac.bank_acct_use_id
AND     glcc.code_combination_id (+) = cgac.asset_code_combination_id
AND     glcc2.code_combination_id (+) = cgac.cash_clearing_ccid
AND     (( upper(:P_BANK_ACCOUNT_PAR) = 'BOTH')
          OR    (upper(:P_BANK_ACCOUNT_PAR) = 'INACTIVE'
                  AND (nvl(ba.end_date, :P_EFFECTIVE_DATE + 1)
                       <= :P_EFFECTIVE_DATE))
          OR    (upper(:P_BANK_ACCOUNT_PAR) = 'ACTIVE'
                  AND (nvl(ba.end_date, :P_EFFECTIVE_DATE + 1)
                      >= :P_EFFECTIVE_DATE)))
AND	la.lookup_type (+) = 'DISBURSEMENT TYPE'
AND	la.lookup_code (+) = cs.disbursement_type_lookup_code
AND           ba.account_classification = 'INTERNAL'
ORDER BY 1 ASC,2 ASC,4 ASC,8 ASC,6 ASC,7 ASC,9 ASC,11 ASC,10 ASC,23 ASC,24 ASC,13 ASC,14 ASC,12 ASC,15 ASC , DECODE ( UPPER ( : P_ORDER_BY_PAR ) , 'BANK,BRANCH,ACCOUNT NAME' , upper ( bb.bank_name ) , upper ( ba.bank_account_name ) ) , DECODE ( UPPER ( : P_ORDER_BY_PAR ) , 'BANK,BRANCH,ACCOUNT NAME' , upper ( bb.bank_branch_name ) , '' ) , DECODE ( UPPER ( : P_ORDER_BY_PAR ) , 'BANK,BRANCH,ACCOUNT NAME' , upper ( ba.bank_account_name ) , '' ) , UPPER ( cs.name )
Parameter Name SQL text Validation
Ledger
 
Effective Date
 
Date
Include Active/Inactive Accounts
 
LOV Oracle
Sort Accounts By
 
LOV Oracle