AP Bank Charges - draft

select
decode
  (:P_ORDER_BY,
    'NUMBER',abb1.bank_number,
    'NAME',
     decode
        (:H_SORT_BY_PHONETIC,
          'N', nvl(abb1.bank_name,nvl(abc.transferring_bank_name,abc.transferring_bank)),
          'Y', abb1.bank_name_alt,nvl(abc.transferring_bank_name,abc.transferring_bank))) C_ORDER,
abb1.bank_number C_TRANSFER_BANK_NUMBER,
abb1.bank_name C_TRANSFER_BANK_NAME,
abb1.branch_number C_TRANSFER_BANK_NUM,
abb1.bank_branch_name C_TRANSFER_BANK_BRANCH_NAME,
abb2.bank_number C_RECEIVE_BANK_NUMBER,
abb2.bank_name C_RECEIVE_BANK_NAME,
abb2.branch_number C_RECEIVE_BANK_NUM,
abb2.bank_branch_name C_RECEIVE_BANK_BRANCH_NAME,
abc.transferring_bank C_TRANSFERRING_BANK,
abc.transferring_bank_name C_TRANSFERRING_BANK_NAME,
abc.transferring_branch C_TRANSFERRING_BRANCH,
abc.receiving_bank C_RECEIVING_BANK,
abc.receiving_bank_name C_RECEIVING_BANK_NAME,
abc.receiving_branch C_RECEIVING_BRANCH,
abcl.trans_amount_from C_TRANS_AMOUNT_FROM,
abcl.trans_amount_to C_TRANS_AMOUNT_TO,
abcl.bank_charge_standard C_BANK_CHARGE_STANDARD,
abcl.bank_charge_negotiated C_BANK_CHARGE_NEGOTIATED,
abcl.tolerance_limit C_TOLERANCE_LIMIT,
abcl.start_date C_START_DATE,
abcl.end_date C_END_DATE,
abc.transfer_priority C_TRANSFER_PRIORITY, 
	AP_APXBCRPT_XMLP_PKG.c_line_transfer_priorityformul(abc.transfer_priority) C_LINE_TRANSFER_PRIORITY
from
ap_bank_charges abc,
ce_bank_branches_v abb1,
ce_bank_branches_v abb2,
ap_bank_charge_lines abcl
&lp_ap_ar_tab
where abc.transferring_bank_branch_id = abb1.branch_party_id(+)
and abc.receiving_bank_branch_id = abb2.branch_party_id(+)
and abc.bank_charge_id = abcl.bank_charge_id
and abc.currency_code = :P_CURRENCY_CODE
and ( abb1.bank_name = :P_TRANSFERRING_BANK_NAME or ( abc.transferring_bank = 'ONE' and transferring_bank_name = :P_TRANSFERRING_BANK_NAME ) or :P_TRANSFERRING_BANK_NAME is null or ( abc.transferring_bank = 'ALL' and abc.transferring_bank_name is NULL ))
and ( abc.transfer_priority = :P_TRANSFER_PRIORITY or :P_TRANSFER_PRIORITY is null )and (abc.transfer_priority = :P_TRANSFER_PRIORITY or :P_TRANSFER_PRIORITY is null)
&lp_ap_ar_where
and ((:H_APPLICATION_ID = 200 and abc.transfer_priority<>'AR')
          or (:H_APPLICATION_ID = 222 and abc.transfer_priority='AR'))
 ORDER BY 1 ASC,4 ASC,6 ASC,8 ASC,5 ASC,7 
  ASC,9 ASC,15 ASC,10 ASC,12 ASC,13 ASC,14 ASC,2 ASC,3 ASC,11 ASC