<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: wujun  
    $Date: 2016-12-13 15:30:00  
    $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 ccc.contract_id,
			       (select pp.virtual_con_number from con_contract cc,prj_project pp where cc.contract_id = ccc.contract_id and pp.project_id = cc.project_id) virtual_con_number,--大合同号
			       (select hbm.bp_name from con_contract cc, hls_bp_master hbm where cc.contract_id = ccc.contract_id and hbm.bp_id = cc.bp_id_tenant) bp_name,--承租人
			       to_char((select pp.signing_date from con_contract cc, prj_project pp where cc.contract_id = ccc.contract_id and pp.project_id = cc.project_id),'YYYY') signing_date_year,--签约日期(年)
             	   to_char((select pp.signing_date from con_contract cc, prj_project pp where cc.contract_id = ccc.contract_id and pp.project_id = cc.project_id),'MM') signing_date_month,--签约日期(月)
                   to_char((select pp.signing_date from con_contract cc, prj_project pp where cc.contract_id = ccc.contract_id and pp.project_id = cc.project_id),'DD') signing_date_day,--签约日期(日)
			       (select pp.project_name from con_contract cc, prj_project pp where cc.contract_id = ccc.contract_id and pp.project_id = cc.project_id) project_name,--项目名称
			       (select ee.name from con_contract cc,exp_employees ee where cc.contract_id = ccc.contract_id and ee.employee_id = cc.employee_id) manager,--业务经理(联系人)
			       (select ee.mobil from con_contract cc,exp_employees ee where cc.contract_id = ccc.contract_id and ee.employee_id = cc.employee_id) mobil,--联系方式
			       ccc.times,              --期数
			       to_char(ccc.due_date,'YYYY') due_date_year,           --预定收款日(年)
                   to_char(ccc.due_date,'MM') due_date_month,           --预定收款日(月)
                   to_char(ccc.due_date,'DD') due_date_day,           --预定收款日(日)
			       ROUND((NVL((select cwo.write_off_date from csh_write_off cwo where cwo.cashflow_id = ccc.cashflow_id and NVL(cwo.reversed_flag,'N')='N'),sysdate)
			        - ccc.due_date),0) overdue_days, --逾期天数
			        
			       '￥'||TO_CHAR(ROUND((ccc.due_amount + (NVL((SELECT SUM(NVL(cf.due_amount, 0))
                                          FROM con_contract_cashflow cf
                                         WHERE cf.cf_item     = '15' AND
                                               ccc.contract_id = cf.contract_id AND
                                               ccc.times       = cf.times GROUP BY cf.cf_item ), 0) + 
                                   NVL((SELECT SUM(NVL(ccf.due_amount, 0))
                                          FROM con_contract_cashflow ccf
                                         WHERE ccf.cf_item     = '3' AND
                                               ccf.contract_id = ccc.contract_id AND
                                               ccf.times       = ccc.times GROUP BY ccf.cf_item), 0))),2),'FM999,999,999,990.00') due_amount,  --本金租金手续费咨询费
				   change_number_to_rmb(round((ccc.due_amount + (NVL((SELECT SUM(NVL(cf.due_amount, 0))
                                          FROM con_contract_cashflow cf
                                         WHERE cf.cf_item     = '15' AND
                                               ccc.contract_id = cf.contract_id AND
                                               ccc.times       = cf.times GROUP BY cf.cf_item ), 0) + 
                                   NVL((SELECT SUM(NVL(ccf.due_amount, 0))
                                          FROM con_contract_cashflow ccf
                                         WHERE ccf.cf_item     = '3' AND
                                               ccf.contract_id = ccc.contract_id AND
                                               ccf.times       = ccc.times GROUP BY ccf.cf_item), 0))),2)) due_amount_rmb,	--金额转中文
                 '￥'||to_char(round(((ccc.due_amount + (NVL((SELECT SUM(NVL(cf.due_amount, 0))
                                          FROM con_contract_cashflow cf
                                         WHERE cf.cf_item     = '15' AND
                                               ccc.contract_id = cf.contract_id AND
                                               ccc.times       = cf.times GROUP BY cf.cf_item ), 0) + 
                                   NVL((SELECT SUM(NVL(ccf.due_amount, 0))
                                          FROM con_contract_cashflow ccf
                                         WHERE ccf.cf_item     = '3' AND
                                               ccf.contract_id = ccc.contract_id AND
                                               ccf.times       = ccc.times GROUP BY ccf.cf_item), 0))) * (ROUND((NVL((select cwo.write_off_date from csh_write_off cwo where cwo.cashflow_id = ccc.cashflow_id and NVL(cwo.reversed_flag,'N')='N'),sysdate)
			        - ccc.due_date),0)) * 0.0005),2),'FM999,999,999,990.00') late_fee,	--滞纳金
                 change_number_to_rmb(round(((ccc.due_amount + (NVL((SELECT SUM(NVL(cf.due_amount, 0))
                                          FROM con_contract_cashflow cf
                                         WHERE cf.cf_item     = '15' AND
                                               ccc.contract_id = cf.contract_id AND
                                               ccc.times       = cf.times GROUP BY cf.cf_item ), 0) + 
                                   NVL((SELECT SUM(NVL(ccf.due_amount, 0))
                                          FROM con_contract_cashflow ccf
                                         WHERE ccf.cf_item     = '3' AND
                                               ccf.contract_id = ccc.contract_id AND
                                               ccf.times       = ccc.times GROUP BY ccf.cf_item), 0))) * (ROUND((NVL((select cwo.write_off_date from csh_write_off cwo where cwo.cashflow_id = ccc.cashflow_id and NVL(cwo.reversed_flag,'N')='N'),sysdate)
			        - ccc.due_date),0)) * 0.0005),2)) late_fee_rmb,	--滞纳金
			     '￥'||TO_CHAR(round(((ccc.due_amount + (NVL((SELECT SUM(NVL(cf.due_amount, 0))
                                          FROM con_contract_cashflow cf
                                         WHERE cf.cf_item     = '15' AND
                                               ccc.contract_id = cf.contract_id AND
                                               ccc.times       = cf.times GROUP BY cf.cf_item ), 0) + 
                                   NVL((SELECT SUM(NVL(ccf.due_amount, 0))
                                          FROM con_contract_cashflow ccf
                                         WHERE ccf.cf_item     = '3' AND
                                               ccf.contract_id = ccc.contract_id AND
                                               ccf.times       = ccc.times GROUP BY ccf.cf_item), 0))) + 
                 ((ccc.due_amount + (NVL((SELECT SUM(NVL(cf.due_amount, 0))
                                          FROM con_contract_cashflow cf
                                         WHERE cf.cf_item     = '15' AND
                                               ccc.contract_id = cf.contract_id AND
                                               ccc.times       = cf.times GROUP BY cf.cf_item ), 0) + 
                                   NVL((SELECT SUM(NVL(ccf.due_amount, 0))
                                          FROM con_contract_cashflow ccf
                                         WHERE ccf.cf_item     = '3' AND
                                               ccf.contract_id = ccc.contract_id AND
                                               ccf.times       = ccc.times GROUP BY ccf.cf_item), 0))) * (ROUND((NVL((select cwo.write_off_date from csh_write_off cwo where cwo.cashflow_id = ccc.cashflow_id and NVL(cwo.reversed_flag,'N')='N'),sysdate)
			        - ccc.due_date),0)) * 0.0005)),2),'FM999,999,999,990.00') cost_amount,	--应付总额
			     change_number_to_rmb(round(((ccc.due_amount + (NVL((SELECT SUM(NVL(cf.due_amount, 0))
                                          FROM con_contract_cashflow cf
                                         WHERE cf.cf_item     = '15' AND
                                               ccc.contract_id = cf.contract_id AND
                                               ccc.times       = cf.times GROUP BY cf.cf_item ), 0) + 
                                   NVL((SELECT SUM(NVL(ccf.due_amount, 0))
                                          FROM con_contract_cashflow ccf
                                         WHERE ccf.cf_item     = '3' AND
                                               ccf.contract_id = ccc.contract_id AND
                                               ccf.times       = ccc.times GROUP BY ccf.cf_item), 0))) + 
                 ((ccc.due_amount + (NVL((SELECT SUM(NVL(cf.due_amount, 0))
                                          FROM con_contract_cashflow cf
                                         WHERE cf.cf_item     = '15' AND
                                               ccc.contract_id = cf.contract_id AND
                                               ccc.times       = cf.times GROUP BY cf.cf_item ), 0) + 
                                   NVL((SELECT SUM(NVL(ccf.due_amount, 0))
                                          FROM con_contract_cashflow ccf
                                         WHERE ccf.cf_item     = '3' AND
                                               ccf.contract_id = ccc.contract_id AND
                                               ccf.times       = ccc.times GROUP BY ccf.cf_item), 0))) * (ROUND((NVL((select cwo.write_off_date from csh_write_off cwo where cwo.cashflow_id = ccc.cashflow_id and NVL(cwo.reversed_flag,'N')='N'),sysdate)
			        - ccc.due_date),0)) * 0.0005)),2)) rmb_cost_amount
			from con_contract_cashflow ccc 
			where ccc.cashflow_id = ${@cashflow_id}
				]]></bm:query-sql>
            <bm:parameters>
                <bm:parameter name="cashflow_id"/>
            </bm:parameters>
        </bm:operation>
    </bm:operations>
</bm:model>
