<?xml version="1.0" encoding="UTF-8"?>
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
                select t1.contract_id,
       t1.project_number,
       t1.virtual_con_number_5,
       t1.virtual_con_number_1,
       t1.bp_name,
       t1.lease_item_amount_rmb,
       t1.lease_item_amount,
       t1.finance_amount,
       t1.int_rate,
       t1.inception_of_lease,
       t1.inception_of_lease_year,
       t1.inception_of_lease_month,
       t1.inception_of_lease_day,
       
       substr(change_number_to_rmb(t1.total_rental),
              0,
              length(change_number_to_rmb(t1.total_rental))) total_rental_rmb,
       nvl(substr(change_number_to_rmb(t1.deposit),
                  0,
                  length(change_number_to_rmb(t1.deposit)) - 2),
           '零') deposit_rmb,
       nvl(substr(change_number_to_rmb(t1.residual_value),
                  0,
                  length(change_number_to_rmb(t1.residual_value)) - 2),
           '零') residual_value_rmb,
       nvl(substr(change_number_to_rmb(t1.lease_charge),
                  0,
                  length(change_number_to_rmb(t1.lease_charge)) - 2),
           '零') lease_charge_rmb,
       to_char(t1.residual_value, 'FM999,999,999,990.00') residual_value,
       to_char(t1.lease_charge, 'FM999,999,999,990.00') net_lease_charge,
       to_char(t1.total_rental, 'FM999,999,999,990.00') total_rental,
       to_char(t1.deposit, 'FM999,999,999,990.00') deposit
  from (select cc.contract_id,
               (select pp.virtual_con_number
                  from prj_project pp
                 where pp.project_id = cc.project_id
                   and pp.data_class = 'VIRTUAL_CON') project_number,
               (select pp.virtual_con_number || '-5'
                  from prj_project pp
                 where pp.project_id = cc.project_id
                   and pp.data_class = 'VIRTUAL_CON') virtual_con_number_5,
               (select pp.virtual_con_number || '-1'
                  from prj_project pp
                 where pp.project_id = cc.project_id
                   and pp.data_class = 'VIRTUAL_CON') virtual_con_number_1,
               (select hbm.bp_name
                  from hls_bp_master hbm
                 where hbm.bp_id = cc.bp_id_tenant) bp_name,
               substr(change_number_to_rmb((select sum(cwo.write_off_due_amount)
                                             from csh_write_off cwo
                                            where cwo.contract_id =
                                                  cc.contract_id
                                              and cwo.write_off_type =
                                                  'PAYMENT_DEBT'
                                              and nvl(cwo.reversed_flag, 'N') = 'N'
                                              and cwo.times = 0)),
                      0,
                      length(change_number_to_rmb((select sum(cwo.write_off_due_amount)
                                                    from csh_write_off cwo
                                                   where cwo.contract_id =
                                                         cc.contract_id
                                                     and cwo.write_off_type =
                                                         'PAYMENT_DEBT'
                                                     and nvl(cwo.reversed_flag,
                                                             'N') = 'N'
                                                     and cwo.times = 0))) - 2) lease_item_amount_rmb, --租赁成本(汉字)
               to_char((select sum(cwo.write_off_due_amount)
                         from csh_write_off cwo
                        where cwo.contract_id = cc.contract_id
                          and cwo.write_off_type = 'PAYMENT_DEBT'
                          and nvl(cwo.reversed_flag, 'N') = 'N'
                          and cwo.times = 0),
                       'FM999,999,999,990.00') lease_item_amount, --租赁成本
               
               to_char(cc.finance_amount, 'FM999,999,999,990.00') finance_amount,
               to_char(round((cc.int_rate * 100), 2), 'FM990.90') int_rate,
               
               (select pq.inception_of_lease
                  from prj_project pp, prj_quotation pq
                 where pp.project_id = cc.project_id
                   and pp.data_class = 'VIRTUAL_CON'
                   and pq.document_category = 'PROJECT'
                   and pp.project_id = pq.document_id
                   and pq.quotation_type = 'MAJOR') inception_of_lease,
               (select to_char(max(cwo.write_off_date), 'YYYY')
                  from csh_write_off cwo
                 where cwo.write_off_type = 'PAYMENT_DEBT'
                   and nvl(cwo.reversed_flag, 'N') = 'N'
                   and cwo.times = 0
                   and cwo.contract_id = cc.contract_id) inception_of_lease_year, --起租日(年)
               (select to_char(max(cwo.write_off_date), 'MM')
                  from csh_write_off cwo
                 where cwo.write_off_type = 'PAYMENT_DEBT'
                   and nvl(cwo.reversed_flag, 'N') = 'N'
                   and cwo.times = 0
                   and cwo.contract_id = cc.contract_id) inception_of_lease_month, --起租日(月)
               (select to_char(max(cwo.write_off_date), 'DD')
                  from csh_write_off cwo
                 where cwo.write_off_type = 'PAYMENT_DEBT'
                   and nvl(cwo.reversed_flag, 'N') = 'N'
                   and cwo.times = 0
                   and cwo.contract_id = cc.contract_id) inception_of_lease_day, --起租日(日)
               (select sum(ccc.due_amount)
                  from con_contract_cashflow ccc
                 where ccc.contract_id = cc.contract_id
                   and ccc.cf_direction <> 'NONCASH'
                   and ccc.cf_status <> 'CANCEL'
                   and ccc.cf_item = 1) total_rental,
               (select sum(ccc.due_amount)
                  from con_contract_cashflow ccc
                 where ccc.contract_id = cc.contract_id
                   and ccc.cf_direction <> 'NONCASH'
                   and ccc.cf_status <> 'CANCEL'
                   and ccc.cf_item = 3) lease_charge,
               (select sum(ccc.due_amount)
                  from con_contract_cashflow ccc
                 where ccc.contract_id = cc.contract_id
                   and ccc.cf_direction <> 'NONCASH'
                   and ccc.cf_status <> 'CANCEL'
                   and ccc.cf_item = 51) deposit,
               (select sum(ccc.due_amount)
                  from con_contract_cashflow ccc
                 where ccc.contract_id = cc.contract_id
                   and ccc.cf_direction <> 'NONCASH'
                   and ccc.cf_status <> 'CANCEL'
                   and ccc.cf_item = 8) residual_value
          from con_contract cc) t1
                    #WHERE_CLAUSE#
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
</bm:model>
