<?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 * FROM(
              SELECT cc.contract_number,      --合同编号
		             cc.contract_name, --项目名称
		             cc.hn_industry_classification, --行业分类
		             (SELECT scvv.code_value_name
		                FROM sys_code_values_v scvv
		               WHERE scvv.code IN ('WITHIN_GROUP', 'MARKETIZATION')
		                 AND scvv.code_value =
		                     (SELECT pp.binary_classification
		                        FROM prj_project pp
		                       WHERE pp.project_id = cc.project_id)) binary_classification_n, --行业细分
		             cc.bp_id_tenant,
		             (SELECT yl.bp_name
		                FROM hls_bp_master yl
		               WHERE yl.bp_id = cc.bp_id_tenant) lessee_name, --主承租人
		             sdic_report_pkg.get_contract_bp_name(p_contract_id => cc.contract_id,
		                                                  p_bp_category => 'TENANT',
		                                                  p_bp_type     => 'TENANT_SEC') lessee_name_sec, --次承租人
		             cc.project_id,
		             sdic_report_pkg.get_prj_bp_name(p_project_id  => cc.project_id,
		                                             p_bp_category => 'GUARANTOR',
		                                             p_bp_type     => 'GUARANTOR') guarantor_name, --主担保人
		             cc.business_type,
		             (SELECT hbtv.description
		                FROM hls_business_type_v hbtv
		               WHERE hbtv.business_type = cc.business_type) lease_form, --租赁形式(合同类型)
		             cc.lease_term, --租赁年限
		             (select MIN(cwo.write_off_date) from csh_write_off cwo where cwo.contract_id = cc.contract_id and cwo.times = 0
		                     and NVL(cwo.reversed_flag, 'N') = 'N' and cwo.write_off_type='PAYMENT_DEBT') start_rent_date,--第一次付出去的钱的日期(起租日)
		             (SELECT NVL(SUM(NVL(ccc.due_amount, 0)), 0)
		                FROM con_contract_cashflow ccc
		               WHERE ccc.contract_id = cc.contract_id
		                 AND ccc.cf_direction <> 'NONCASH'
		                 AND ccc.cf_item in (1,3,15)) amount_rent, --租金总额
		             (SELECT NVL(ccc.due_amount, 0)
		                FROM con_contract_cashflow ccc
		               WHERE cc.contract_id = ccc.contract_id
		                 AND ccc.times = 0
		                 AND ccc.cf_item = 0) principal_amount, --本金总额(融资本金)
		             (SELECT nvl(SUM(NVL(ccc.interest, 0)),0)
		                FROM con_contract_cashflow ccc
		               WHERE ccc.contract_id = cc.contract_id
		                 AND ccc.cf_direction <> 'NONCASH'
		                 AND ccc.cf_item = 1) amount_interest,  --利息总额    
		             (SELECT NVL(SUM(NVL(ccc.due_amount, 0)), 0)
		                  FROM con_contract_cashflow ccc
		                 WHERE ccc.contract_id = cc.contract_id
		                   AND ccc.cf_direction <> 'NONCASH'
		                   AND ccc.cf_item = 3) amount_charge, --手续费总额      
		             (SELECT NVL(SUM(NVL(ccc.due_amount, 0)), 0)
		                  FROM con_contract_cashflow ccc
		                 WHERE ccc.contract_id = cc.contract_id
		                   AND ccc.cf_direction <> 'NONCASH'
		                   AND ccc.cf_item = 15) amount_other, --咨询费总额 
		             (SELECT NVL(SUM(NVL(ccc.due_amount, 0)), 0)
		                  FROM con_contract_cashflow ccc
		                 WHERE ccc.contract_id = cc.contract_id
		                   AND ccc.cf_direction <> 'NONCASH'
		                   AND ccc.cf_item = 8) purchase_prince_amount, --留购价款
		             ((SELECT NVL(ccc.due_amount, 0)
		                FROM con_contract_cashflow ccc
		               WHERE cc.contract_id = ccc.contract_id
		                 AND ccc.times = 0
		                 AND ccc.cf_item = 0) + 
		              (SELECT nvl(SUM(NVL(ccc.interest, 0)),0)
		                  FROM con_contract_cashflow ccc
		                 WHERE ccc.contract_id = cc.contract_id
		                   AND ccc.cf_direction <> 'NONCASH'
		                   AND ccc.cf_item in (1,3,15,8))) amount_income,   --总收入
		             ((SELECT nvl(SUM(NVL(cwo.write_off_principal, 0)),0)
		                     FROM csh_write_off cwo
		                    WHERE cwo.contract_id = cc.contract_id
		                      AND cwo.write_off_type = 'RECEIPT_CREDIT'
		                      AND cwo.times <> 0
		                      AND cwo.cf_item = 1
		                      AND NVL(cwo.reversed_flag, 'N') = 'N'
		                      AND cwo.write_off_date <= to_date(${@query_date},'yyyy-mm-dd') 
		                      AND cwo.write_off_date >= DECODE(${@report_type},'MONTH',add_months(to_date(${@query_date},'yyyy-mm-dd'),-1),'SEASON',add_months(to_date(${@query_date},'yyyy-mm-dd'),-3),'HALF_YEAR',add_months(to_date(${@query_date},'yyyy-mm-dd'),-6),'YEAR',add_months(to_date(${@query_date},'yyyy-mm-dd'),-12))) + 
		             (SELECT nvl(SUM(NVL(cwo.write_off_interest, 0)),0)
		                     FROM csh_write_off cwo
		                    WHERE cwo.contract_id = cc.contract_id
		                      AND cwo.write_off_type = 'RECEIPT_CREDIT'
		                      AND cwo.times <> 0
		                      AND cwo.cf_item = 1
		                      AND NVL(cwo.reversed_flag, 'N') = 'N'
		                      AND cwo.write_off_date <= to_date(${@query_date},'yyyy-mm-dd') 
		                      AND cwo.write_off_date >= DECODE(${@report_type},'MONTH',add_months(to_date(${@query_date},'yyyy-mm-dd'),-1),'SEASON',add_months(to_date(${@query_date},'yyyy-mm-dd'),-3),'HALF_YEAR',add_months(to_date(${@query_date},'yyyy-mm-dd'),-6),'YEAR',add_months(to_date(${@query_date},'yyyy-mm-dd'),-12))) +
		             (SELECT NVL(SUM(NVL(cwo.write_off_due_amount, '0')), 0)
		                       FROM csh_write_off cwo
		                      WHERE cwo.contract_id = cc.contract_id
		                        AND cwo.cf_type = 3
		                        AND cwo.cf_item = 3
		                        AND NVL(cwo.reversed_flag, 'N') = 'N'
		                        AND cwo.write_off_date <= to_date(${@query_date},'yyyy-mm-dd')
		                        AND cwo.write_off_date >= DECODE(${@report_type},'MONTH',add_months(to_date(${@query_date},'yyyy-mm-dd'),-1),'SEASON',add_months(to_date(${@query_date},'yyyy-mm-dd'),-3),'HALF_YEAR',add_months(to_date(${@query_date},'yyyy-mm-dd'),-6),'YEAR',add_months(to_date(${@query_date},'yyyy-mm-dd'),-12))) +
		             (SELECT NVL(SUM(NVL(cwo.write_off_due_amount, '0')), 0)
		                       FROM csh_write_off cwo
		                      WHERE cwo.contract_id = cc.contract_id
		                        AND cwo.cf_type = 15
		                        AND cwo.cf_item = 15
		                        AND NVL(cwo.reversed_flag, 'N') = 'N'
		                        AND cwo.write_off_date <= to_date(${@query_date},'yyyy-mm-dd')
		                        AND cwo.write_off_date >= DECODE(${@report_type},'MONTH',add_months(to_date(${@query_date},'yyyy-mm-dd'),-1),'SEASON',add_months(to_date(${@query_date},'yyyy-mm-dd'),-3),'HALF_YEAR',add_months(to_date(${@query_date},'yyyy-mm-dd'),-6),'YEAR',add_months(to_date(${@query_date},'yyyy-mm-dd'),-12))) +
		              (SELECT NVL(SUM(NVL(cwo.write_off_due_amount, '0')), 0)
		                       FROM csh_write_off cwo
		                      WHERE cwo.contract_id = cc.contract_id
		                        AND cwo.cf_type = 8
		                        AND cwo.cf_item = 8
		                        AND NVL(cwo.reversed_flag, 'N') = 'N'
		                        AND cwo.write_off_date <= to_date(${@query_date},'yyyy-mm-dd')
		                        AND cwo.write_off_date >= DECODE(${@report_type},'MONTH',add_months(to_date(${@query_date},'yyyy-mm-dd'),-1),'SEASON',add_months(to_date(${@query_date},'yyyy-mm-dd'),-3),'HALF_YEAR',add_months(to_date(${@query_date},'yyyy-mm-dd'),-6),'YEAR',add_months(to_date(${@query_date},'yyyy-mm-dd'),-12)))) write_off_amount_term,--当期收入合计
		             ((SELECT nvl(SUM(NVL(cwo.write_off_principal, 0)),0)
		                     FROM csh_write_off cwo
		                    WHERE cwo.contract_id = cc.contract_id
		                      AND cwo.write_off_type = 'RECEIPT_CREDIT'
		                      AND cwo.times <> 0
		                      AND cwo.cf_item = 1
		                      AND NVL(cwo.reversed_flag, 'N') = 'N'
		                      AND cwo.write_off_date <= to_date(${@query_date},'yyyy-mm-dd') ) + 
		             (SELECT nvl(SUM(NVL(cwo.write_off_interest, 0)),0)
		                     FROM csh_write_off cwo
		                    WHERE cwo.contract_id = cc.contract_id
		                      AND cwo.write_off_type = 'RECEIPT_CREDIT'
		                      AND cwo.times <> 0
		                      AND cwo.cf_item = 1
		                      AND NVL(cwo.reversed_flag, 'N') = 'N'
		                      AND cwo.write_off_date <= to_date(${@query_date},'yyyy-mm-dd')) +
		             (SELECT NVL(SUM(NVL(cwo.write_off_due_amount, '0')), 0)
		                       FROM csh_write_off cwo
		                      WHERE cwo.contract_id = cc.contract_id
		                        AND cwo.cf_type = 3
		                        AND cwo.cf_item = 3
		                        AND NVL(cwo.reversed_flag, 'N') = 'N'
		                        AND cwo.write_off_date <= to_date(${@query_date},'yyyy-mm-dd') ) +
		             (SELECT NVL(SUM(NVL(cwo.write_off_due_amount, '0')), 0)
		                       FROM csh_write_off cwo
		                      WHERE cwo.contract_id = cc.contract_id
		                        AND cwo.cf_type = 15
		                        AND cwo.cf_item = 15
		                        AND NVL(cwo.reversed_flag, 'N') = 'N'
		                        AND cwo.write_off_date <= to_date(${@query_date},'yyyy-mm-dd') ) +
		              (SELECT NVL(SUM(NVL(cwo.write_off_due_amount, '0')), 0)
		                       FROM csh_write_off cwo
		                      WHERE cwo.contract_id = cc.contract_id
		                        AND cwo.cf_type = 8
		                        AND cwo.cf_item = 8
		                        AND NVL(cwo.reversed_flag, 'N') = 'N'
		                        AND cwo.write_off_date <= to_date(${@query_date},'yyyy-mm-dd') )) write_off_amount,--累计收入总额
		             (((SELECT NVL(ccc.due_amount, 0)
		                FROM con_contract_cashflow ccc
		               WHERE cc.contract_id = ccc.contract_id
		                 AND ccc.times = 0
		                 AND ccc.cf_item = 0) + 
		              (SELECT nvl(SUM(NVL(ccc.interest, 0)),0)
		                  FROM con_contract_cashflow ccc
		                 WHERE ccc.contract_id = cc.contract_id
		                   AND ccc.cf_direction <> 'NONCASH'
		                   AND ccc.cf_item = 1) +
		               (SELECT NVL(SUM(NVL(ccc.due_amount, 0)), 0)
		                  FROM con_contract_cashflow ccc
		                 WHERE ccc.contract_id = cc.contract_id
		                   AND ccc.cf_direction <> 'NONCASH'
		                   AND ccc.cf_item = 3) +
		                (SELECT NVL(SUM(NVL(ccc.due_amount, 0)), 0)
		                  FROM con_contract_cashflow ccc
		                 WHERE ccc.contract_id = cc.contract_id
		                   AND ccc.cf_direction <> 'NONCASH'
		                   AND ccc.cf_item = 15) +
		                 (SELECT NVL(SUM(NVL(ccc.due_amount, 0)), 0)
		                  FROM con_contract_cashflow ccc
		                 WHERE ccc.contract_id = cc.contract_id
		                   AND ccc.cf_direction <> 'NONCASH'
		                   AND ccc.cf_item = 8)) - 
		             ((SELECT nvl(SUM(NVL(cwo.write_off_due_amount, 0)),0)
		                     FROM csh_write_off cwo
		                    WHERE cwo.contract_id = cc.contract_id
		                      AND cwo.write_off_type = 'RECEIPT_CREDIT'
		                      AND cwo.times <> 0
		                      AND cwo.cf_item = 1
		                      AND NVL(cwo.reversed_flag, 'N') = 'N'
		                      AND cwo.write_off_date <= to_date(${@query_date},'yyyy-mm-dd')) +
		             (SELECT NVL(SUM(NVL(cwo.write_off_due_amount, '0')), 0)
		                       FROM csh_write_off cwo
		                      WHERE cwo.contract_id = cc.contract_id
		                        AND cwo.cf_type = 3
		                        AND cwo.cf_item = 3
		                        AND NVL(cwo.reversed_flag, 'N') = 'N'
		                        AND cwo.write_off_date <= to_date(${@query_date},'yyyy-mm-dd') ) +
		             (SELECT NVL(SUM(NVL(cwo.write_off_due_amount, '0')), 0)
		                       FROM csh_write_off cwo
		                      WHERE cwo.contract_id = cc.contract_id
		                        AND cwo.cf_type = 15
		                        AND cwo.cf_item = 15
		                        AND NVL(cwo.reversed_flag, 'N') = 'N'
		                        AND cwo.write_off_date <= to_date(${@query_date},'yyyy-mm-dd') ) +
		              (SELECT NVL(SUM(NVL(cwo.write_off_due_amount, '0')), 0)
		                       FROM csh_write_off cwo
		                      WHERE cwo.contract_id = cc.contract_id
		                        AND cwo.cf_type = 8
		                        AND cwo.cf_item = 8
		                        AND NVL(cwo.reversed_flag, 'N') = 'N'
		                        AND cwo.write_off_date <= to_date(${@query_date},'yyyy-mm-dd') ))) surplus_write_off_amount,--总收入余额
		             (SELECT nvl(SUM(NVL(cwo.write_off_principal, 0)),0)
		                     FROM csh_write_off cwo
		                    WHERE cwo.contract_id = cc.contract_id
		                      AND cwo.write_off_type = 'RECEIPT_CREDIT'
		                      AND cwo.times <> 0
		                      AND cwo.cf_item = 1
		                      AND NVL(cwo.reversed_flag, 'N') = 'N'
		                      AND cwo.write_off_date <= to_date(${@query_date},'yyyy-mm-dd') 
		                      AND cwo.write_off_date >= DECODE(${@report_type},'MONTH',add_months(to_date(${@query_date},'yyyy-mm-dd'),-1),'SEASON',add_months(to_date(${@query_date},'yyyy-mm-dd'),-3),'HALF_YEAR',add_months(to_date(${@query_date},'yyyy-mm-dd'),-6),'YEAR',add_months(to_date(${@query_date},'yyyy-mm-dd'),-12))) principal_term,--本期收到本金
		             (SELECT nvl(SUM(NVL(cwo.write_off_principal, 0)),0)
		                     FROM csh_write_off cwo
		                    WHERE cwo.contract_id = cc.contract_id
		                      AND cwo.write_off_type = 'RECEIPT_CREDIT'
		                      AND cwo.times <> 0
		                      AND cwo.cf_item = 1
		                      AND NVL(cwo.reversed_flag, 'N') = 'N'
		                      AND cwo.write_off_date <= to_date(${@query_date},'yyyy-mm-dd')) recovery_principal_amount,--回收本金累计   
		             ((SELECT NVL(ccc.due_amount, 0)
		                FROM con_contract_cashflow ccc
		               WHERE cc.contract_id = ccc.contract_id
		                 AND ccc.times = 0
		                 AND ccc.cf_item = 0) -
		             (SELECT nvl(SUM(NVL(cwo.write_off_principal, 0)),0)
		                     FROM csh_write_off cwo
		                    WHERE cwo.contract_id = cc.contract_id
		                      AND cwo.write_off_type = 'RECEIPT_CREDIT'
		                      AND cwo.times <> 0
		                      AND cwo.cf_item = 1
		                      AND NVL(cwo.reversed_flag, 'N') = 'N'
		                      AND cwo.write_off_date <= to_date(${@query_date},'yyyy-mm-dd'))) surplus_principal_total,--本金余额
		             (SELECT nvl(SUM(NVL(cwo.write_off_interest, 0)),0)
		                     FROM csh_write_off cwo
		                    WHERE cwo.contract_id = cc.contract_id
		                      AND cwo.write_off_type = 'RECEIPT_CREDIT'
		                      AND cwo.times <> 0
		                      AND cwo.cf_item = 1
		                      AND NVL(cwo.reversed_flag, 'N') = 'N'
		                      AND cwo.write_off_date <= to_date(${@query_date},'yyyy-mm-dd') 
		                      AND cwo.write_off_date >= DECODE(${@report_type},'MONTH',add_months(to_date(${@query_date},'yyyy-mm-dd'),-1),'SEASON',add_months(to_date(${@query_date},'yyyy-mm-dd'),-3),'HALF_YEAR',add_months(to_date(${@query_date},'yyyy-mm-dd'),-6),'YEAR',add_months(to_date(${@query_date},'yyyy-mm-dd'),-12))) interest_amount_term,--本期收到利息
		            (SELECT nvl(SUM(NVL(cwo.write_off_interest, 0)),0)
		                     FROM csh_write_off cwo
		                    WHERE cwo.contract_id = cc.contract_id
		                      AND cwo.write_off_type = 'RECEIPT_CREDIT'
		                      AND cwo.times <> 0
		                      AND cwo.cf_item = 1
		                      AND NVL(cwo.reversed_flag, 'N') = 'N'
		                      AND cwo.write_off_date <= to_date(${@query_date},'yyyy-mm-dd') ) interest_amount_total,            --回收利息累计
		            ((SELECT nvl(SUM(NVL(ccc.interest, 0)),0)
		                  FROM con_contract_cashflow ccc
		                 WHERE ccc.contract_id = cc.contract_id
		                   AND ccc.cf_direction <> 'NONCASH'
		                   AND ccc.cf_item = 1) -
		            (SELECT nvl(SUM(NVL(cwo.write_off_interest, 0)),0)
		                     FROM csh_write_off cwo
		                    WHERE cwo.contract_id = cc.contract_id
		                      AND cwo.write_off_type = 'RECEIPT_CREDIT'
		                      AND cwo.times <> 0
		                      AND cwo.cf_item = 1
		                      AND NVL(cwo.reversed_flag, 'N') = 'N'
		                      AND cwo.write_off_date <= to_date(${@query_date},'yyyy-mm-dd') )) surplus_interest_recovery,--计息余额
		             (SELECT NVL(SUM(NVL(cwo.write_off_due_amount, '0')), 0)
		                       FROM csh_write_off cwo
		                      WHERE cwo.contract_id = cc.contract_id
		                        AND cwo.cf_type = 3
		                        AND cwo.cf_item = 3
		                        AND NVL(cwo.reversed_flag, 'N') = 'N'
		                        AND cwo.write_off_date <= to_date(${@query_date},'yyyy-mm-dd')
		                        AND cwo.write_off_date >= DECODE(${@report_type},'MONTH',add_months(to_date(${@query_date},'yyyy-mm-dd'),-1),'SEASON',add_months(to_date(${@query_date},'yyyy-mm-dd'),-3),'HALF_YEAR',add_months(to_date(${@query_date},'yyyy-mm-dd'),-6),'YEAR',add_months(to_date(${@query_date},'yyyy-mm-dd'),-12))) amount_other_term,--本期收到的手续费
		             (SELECT NVL(SUM(NVL(cwo.write_off_due_amount, '0')), 0)
		                       FROM csh_write_off cwo
		                      WHERE cwo.contract_id = cc.contract_id
		                        AND cwo.cf_type = 3
		                        AND cwo.cf_item = 3
		                        AND NVL(cwo.reversed_flag, 'N') = 'N'
		                        AND cwo.write_off_date <= to_date(${@query_date},'yyyy-mm-dd') ) amount_other_total,--收到的手续费合计
		             ((SELECT NVL(SUM(NVL(ccc.due_amount, 0)), 0)
		                  FROM con_contract_cashflow ccc
		                 WHERE ccc.contract_id = cc.contract_id
		                   AND ccc.cf_direction <> 'NONCASH'
		                   AND ccc.cf_item = 3) -
		             (SELECT NVL(SUM(NVL(cwo.write_off_due_amount, '0')), 0)
		                       FROM csh_write_off cwo
		                      WHERE cwo.contract_id = cc.contract_id
		                        AND cwo.cf_type = 3
		                        AND cwo.cf_item = 3
		                        AND NVL(cwo.reversed_flag, 'N') = 'N'
		                        AND cwo.write_off_date <= to_date(${@query_date},'yyyy-mm-dd') )) amount_other_recovery,--手续费余额
		             (SELECT NVL(SUM(NVL(cwo.write_off_due_amount, '0')), 0)
		                       FROM csh_write_off cwo
		                      WHERE cwo.contract_id = cc.contract_id
		                        AND cwo.cf_type = 15
		                        AND cwo.cf_item = 15
		                        AND NVL(cwo.reversed_flag, 'N') = 'N'
		                        AND cwo.write_off_date <= to_date(${@query_date},'yyyy-mm-dd')
		                        AND cwo.write_off_date >= DECODE(${@report_type},'MONTH',add_months(to_date(${@query_date},'yyyy-mm-dd'),-1),'SEASON',add_months(to_date(${@query_date},'yyyy-mm-dd'),-3),'HALF_YEAR',add_months(to_date(${@query_date},'yyyy-mm-dd'),-6),'YEAR',add_months(to_date(${@query_date},'yyyy-mm-dd'),-12))) amount_consult_term,--本期收到的咨询费
		             (SELECT NVL(SUM(NVL(cwo.write_off_due_amount, '0')), 0)
		                       FROM csh_write_off cwo
		                      WHERE cwo.contract_id = cc.contract_id
		                        AND cwo.cf_type = 15
		                        AND cwo.cf_item = 15
		                        AND NVL(cwo.reversed_flag, 'N') = 'N'
		                        AND cwo.write_off_date <= to_date(${@query_date},'yyyy-mm-dd') ) amount_consult_total,--收到的咨询费合计
		             ((SELECT NVL(SUM(NVL(ccc.due_amount, 0)), 0)
		                  FROM con_contract_cashflow ccc
		                 WHERE ccc.contract_id = cc.contract_id
		                   AND ccc.cf_direction <> 'NONCASH'
		                   AND ccc.cf_item = 15) -
		             (SELECT NVL(SUM(NVL(cwo.write_off_due_amount, '0')), 0)
		                       FROM csh_write_off cwo
		                      WHERE cwo.contract_id = cc.contract_id
		                        AND cwo.cf_type = 15
		                        AND cwo.cf_item = 15
		                        AND NVL(cwo.reversed_flag, 'N') = 'N'
		                        AND cwo.write_off_date <= to_date(${@query_date},'yyyy-mm-dd') )) amount_consult_recovery,--咨询费余额
		         nvl((select sum(ccc.due_amount-nvl(ccc.received_amount,0))
 					 from con_contract_cashflow ccc
 					where ccc.contract_id = cc.contract_id
   					  and ccc.cf_item in (1, 3, 15, 8)
                      and ccc.cf_direction = 'INFLOW'
                      and ccc.due_date <= to_date(${@query_date},'yyyy-mm-dd')),0) overdue_amount,--累计逾期总额
		         nvl(((select sum(ccc.due_amount)
 					 from con_contract_cashflow ccc
 					where ccc.contract_id = cc.contract_id
   					  and ccc.cf_item in (1, 3, 15, 8)
                      and ccc.cf_direction = 'INFLOW'
                      and ccc.due_date <= to_date(${@query_date},'yyyy-mm-dd')
                      AND ccc.due_date >= DECODE(${@report_type},'MONTH',add_months(to_date(${@query_date},'yyyy-mm-dd'),-1),'SEASON',add_months(to_date(${@query_date},'yyyy-mm-dd'),-3),'HALF_YEAR',add_months(to_date(${@query_date},'yyyy-mm-dd'),-6),'YEAR',add_months(to_date(${@query_date},'yyyy-mm-dd'),-12))) -
                 (select sum(t.write_off_due_amount)
  					from csh_write_off t
 				   where t.write_off_type = 'RECEIPT_CREDIT'
                     and t.contract_id = cc.contract_id
                     and nvl(t.reversed_flag, 'N') = 'N'
                     and t.cf_item in (1, 3, 8, 15)
                     and t.write_off_date <= to_date(${@query_date},'yyyy-mm-dd')
                     and t.write_off_date >= DECODE(${@report_type},'MONTH',add_months(to_date(${@query_date},'yyyy-mm-dd'),-1),'SEASON',add_months(to_date(${@query_date},'yyyy-mm-dd'),-3),'HALF_YEAR',add_months(to_date(${@query_date},'yyyy-mm-dd'),-6),'YEAR',add_months(to_date(${@query_date},'yyyy-mm-dd'),-12)))),0) overdue_amount_term,--本期新增逾期总额       
		              --罚息cf_item = 9
		             (SELECT NVL(SUM(NVL(cwo.write_off_due_amount, '0')), 0)
		                       FROM csh_write_off cwo
		                      WHERE cwo.contract_id = cc.contract_id
		                        AND cwo.cf_type = 9
		                        AND cwo.cf_item = 9
		                        AND NVL(cwo.reversed_flag, 'N') = 'N'
		                        AND cwo.write_off_date <= to_date(${@query_date},'yyyy-mm-dd')
		                        AND cwo.write_off_date >= DECODE(${@report_type},'MONTH',add_months(to_date(${@query_date},'yyyy-mm-dd'),-1),'SEASON',add_months(to_date(${@query_date},'yyyy-mm-dd'),-3),'HALF_YEAR',add_months(to_date(${@query_date},'yyyy-mm-dd'),-6),'YEAR',add_months(to_date(${@query_date},'yyyy-mm-dd'),-12))) default_interest_term,--本期收到的罚息
		             (SELECT NVL(SUM(NVL(ccc.due_amount, 0)), 0)
          FROM con_contract_cashflow ccc
         WHERE ccc.contract_id = cc.contract_id
           AND ccc.cf_direction <> 'NONCASH'
           AND ccc.cf_item = 9) remain_penalty_amount, --罚息余额
       ((SELECT NVL(SUM(NVL(ccc.due_amount, 0)), 0)
           FROM con_contract_cashflow ccc
          WHERE ccc.contract_id = cc.contract_id
            AND ccc.cf_direction <> 'NONCASH'
            AND ccc.cf_item = 9) +
       (SELECT NVL(SUM(NVL(cwo.write_off_due_amount, '0')), 0)
           FROM csh_write_off cwo
          WHERE cwo.contract_id = cc.contract_id
            AND cwo.cf_type = 9
            AND cwo.cf_item = 9
            AND NVL(cwo.reversed_flag, 'N') = 'N'
            AND cwo.write_off_date <= to_date('2017-03-24', 'yyyy-mm-dd'))) sum_penalty_amount, --罚息总额
		             nvl((select '是'
		                    from dual
		                   where exists (select 1
		                            from hn_abs_contract hac
		                           where hac.contract_id = cc.contract_id)), '否') abs_flag, --ABS标识
		             (SELECT eeav.unit_name
		                FROM exp_employee_assigns_v eeav
		               WHERE eeav.employee_id  = cc.employee_id 
		                 AND eeav.enabled_flag = 'Y'
		                 and eeav.primary_position_flag='Y' ) operating_department, --业务部门
		             (SELECT ee.name FROM exp_employees ee WHERE ee.employee_id = cc.employee_id) manager, --项目经理
		             (select MAX(ccc.due_date) from con_contract_cashflow ccc where ccc.contract_id = cc.contract_id) end_rent_date,  --最后一期付款日(结束日)
		             TO_CHAR(ROUND(((select MAX(ccc.due_date) from con_contract_cashflow ccc where ccc.contract_id = cc.contract_id) 
		               - to_date(${@query_date},'yyyy-mm-dd') )/365,2),'FM90.00') surplus_lease_term, --剩余期限(年)
		             cc.contract_id,
		             nvl((select nvl(sum(nvl(ccc.due_amount, 0)), 0)
             from con_contract_cashflow ccc
            where ccc.contract_id = cc.contract_id
              and ccc.cf_item = 51),
           0) deposit_amount,
           nvl((select nvl(sum(nvl(ccc.due_amount, 0)), 0)
             from con_contract_cashflow ccc
            where ccc.contract_id = cc.contract_id
              and ccc.cf_item = 51),
           0) - nvl((select nvl(sum(nvl(cwo.write_off_due_amount,0)), 0)
                      from csh_write_off cwo
                     where cwo.contract_id = cc.contract_id
                       and cwo.cf_item = 52
                       and nvl(cwo.reversed_flag, 'N') = 'N'),
                    0) remain_deposit_amount
		        FROM con_contract cc
		       WHERE cc.data_class = 'NORMAL'
		         AND NVL(cc.hn_adjust_review_flag, 'N') = 'Y'
		         and cc.contract_status not in ('CANCEL') 
		         ) t1 #WHERE_CLAUSE#
		         ORDER BY t1.contract_number
			]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field name="hn_industry_classification_n" queryExpression="t1.hn_industry_classification like &apos;%&apos;||${@hn_industry_classification}||&apos;%&apos;"/>
        <bm:query-field name="binary_classification_n" queryExpression="t1.binary_classification_n like &apos;%&apos;||${@binary_classification_n}||&apos;%&apos;"/>
        <bm:query-field name="operating_department" queryExpression="t1.operating_department like &apos;%&apos;||${@operating_department}||&apos;%&apos;"/>
        <bm:query-field name="manager" queryExpression="t1.manager like &apos;%&apos;||${@manager}||&apos;%&apos;"/>
        <bm:query-field name="principal_amount_from" queryExpression="t1.principal_amount &gt;= ${@principal_amount_from}"/>
        <bm:query-field name="principal_amount_to" queryExpression="t1.principal_amount &lt;= ${@principal_amount_to}"/>
        <bm:query-field name="contract_number" queryExpression="t1.contract_number like &apos;%&apos;||${@contract_number}||&apos;%&apos;"/>
        <bm:query-field name="contract_name" queryExpression="t1.contract_name like &apos;%&apos;||${@contract_name}||&apos;%&apos;"/>
        <bm:query-field name="start_rent_date_from" queryExpression="t1.start_rent_date &gt;= TO_DATE(${@start_rent_date_from},&apos;YYYY-MM-DD&apos;)"/>
        <bm:query-field name="start_rent_date_to" queryExpression="t1.start_rent_date &lt;= TO_DATE(${@start_rent_date_to},&apos;YYYY-MM-DD&apos;)"/>
        <bm:query-field name="lease_term" queryExpression="t1.lease_term like &apos;%&apos;||${@lease_term}||&apos;%&apos;"/>
        <bm:query-field name="lessee_name" queryExpression="t1.lessee_name like &apos;%&apos;||${@lessee_name}||&apos;%&apos;"/>
        <bm:query-field name="guarantor_name" queryExpression="t1.guarantor_name like &apos;%&apos;||${@guarantor_name}||&apos;%&apos;"/>
        <bm:query-field name="abs_flag_n" queryExpression="t1.abs_flag like &apos;%&apos;||${@abs_flag_n}||&apos;%&apos;"/>
    </bm:query-fields>
</bm:model>
