Oracle Cloud Fusion R13 Account Payable (AP) Bank Account Listing - draft SQL

=========================================================================

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,              

la.displayed_field C_DISBURSEMENT_TYPE,      

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_lookup_codes la

WHERE   ba.bank_branch_id = bb.branch_party_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           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 )