<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: weiwuzhen  
    $Date: 2016-7-13 下午1:35:48  
    $Revision: 1.0  
    $Purpose: 
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[SELECT 
            to_char(l.apply_pay_date,'yyyy')||'/'||to_char(l.apply_pay_date,'mm')||'/'||to_char(l.apply_pay_date,'dd') apply_pay_date, --用款日期
       change_number_to_rmb(cc.due_amount) due_amount_n, --用款金额（大写）
       ''||'￥'||to_char(cc.due_amount,'FM999,999,999,990.00') due_amount, --用款金额（小写）
       (select value_name
          from (select code_value value_code, code_value_name value_name
                  from sys_code_values_v
                 where code = 'CSH502_TRANSFER_WAY'
                   and code_value_enabled_flag = 'Y') t
         where t.value_code =
               (SELECT cb.pay_type
                  FROM CSH_PAYMENT_REQ_LN_BANK cb, csh_payment_req_ln rln
                 where cb.payment_req_ln_id = rln.payment_req_ln_id
                   and cb.bank_record_id =
                       (SELECT min(cb.bank_record_id)
                          FROM CSH_PAYMENT_REQ_LN_BANK cb,
                               csh_payment_req_ln      rln
                         where cb.payment_req_ln_id = rln.payment_req_ln_id
                           and rln.payment_req_id = l.payment_req_id)
                   and rln.payment_req_id = l.payment_req_id)) pay_type_desc, --付款形式
       (select ba.bank_account_name
          from hls_bp_master_bank_account_v ba
         where ba.bank_account_id = l.bp_bank_account_id
           and ba.bp_id = l.bp_id) bp_bank_account_id_n, --收款单位名称
       (select ba.bank_branch_name
          from hls_bp_master_bank_account_v ba
         where ba.bank_account_id = l.bp_bank_account_id
           and ba.bp_id = l.bp_id) bank_branch_name, --收款单位开户行     
       l.bp_bank_account_num, --收款帐号   
       (select t.value_name from      (select t1.bp_id       value_code,
       t1.bp_code,
       t1.bp_name     value_name,
       t1.bp_category,
       t1.bp_category_name bp_category_desc
  from hls_bp_master_company_v t1
 where t1.company_id = ${/session/@company_id}
   and t1.enabled_flag = 'Y'
    and ( exists (select 1
                  from con_contract_bp b ,csh_payment_req_ln l
                 where b.contract_id= l.ref_doc_id
                   and b.bp_id = t1.bp_id
                   and l.payment_req_id=${/parameter/@payment_req_id}
                   and b.bp_category in
                       ('VENDER', 'TENANT', 'TENANT_SEC', 'LAW_FIRM','OTHER_AGENCY'))
or t1.bp_category in ('INSURER'))
   ) t where t.value_code= l.bp_id) bp_id_n, --付款单位名称   
       (SELECT cb.payment_method
          FROM CSH_PAYMENT_REQ_LN_BANK cb, csh_payment_req_ln rln
         where cb.payment_req_ln_id = rln.payment_req_ln_id
           and cb.bank_record_id =
               (SELECT min(cb.bank_record_id)
                  FROM CSH_PAYMENT_REQ_LN_BANK cb, csh_payment_req_ln rln
                 where cb.payment_req_ln_id = rln.payment_req_ln_id
                   and rln.payment_req_id = l.payment_req_id)
           and rln.payment_req_id = l.payment_req_id) payment_method, --付款结算方式
       hd.description,
       (SELECT e.name
          FROM sys_user u, exp_employees e
         WHERE hd.owner_user_id = u.user_id
           AND u.employee_id = e.employee_id) user_name --用款部门经办人
  from csh_payment_req_ln    l,
       con_contract_cashflow cc,
       csh_payment_req_hd    hd
 where l.payment_req_ln_type = 'DEBT'
   and l.ref_doc_line_id = cc.cashflow_id
   and l.payment_req_id = hd.payment_req_id
   and l.payment_req_id=${/parameter/@payment_req_id}
   ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
</bm:model>
