<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: duzhihong  
    $Date: 2015-1-14 下午3:51:36  
    $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
                    *
                FROM
                    (SELECT
                        c.batch_hd_id,
                        c.batch_ln_id,
                        c.change_req_id,
                        
                        (SELECT
                            cd.approval_status
                        FROM
                            con_batch_adjust_interest_hd cd
                        WHERE
                            cd.batch_hd_id =c.batch_hd_id
                        )approval_status,
                        b.bp_id_tenant,
                        b.contract_number,
                        b.contract_id,
                        b.virtual_con_number,
                        b.virtual_con_name,
                        b.inception_of_lease,
                        b.adjust_rate_type_n,
                        b.bp_name,
                        b.employee_id,
                        b.lease_organization,
                        b.employee_name,
                        b.employee_name_of_manager,
                        b.employee_id_of_manager,
                        b.lease_organization_desc,
                        (select v.code_value_name from sys_code_values_v
                        v where v.code = 'TRE502_INT_RATE_FIXING_WAY'
                        and v.code_value = b.int_rate_fixing_way) int_rate_fixing_way_des,
                        (select v.code_value_name from sys_code_values_v
                        v where v.code = 'HLS_FLT_ANNUAL_ADJ_TIMES'
                        and v.code_value = b.flt_annual_adj_times) flt_annual_adj_times_des,
                        b.base_rate*100 base_rate_n,
                        flt.base_rate_new*100 base_rate_new_n,
                        b.int_rate_fixing_range*100 int_rate_fixing_range_n,
                        b.int_rate*100 int_rate_n,
                         flt.int_rate_new*100 int_rate_new_n,
                        c.adjust_flag,
                        DECODE(c.adjust_flag, 'Y', '成功', 'N', '失败','E','无权限') adjust_flag_desc,
                        (SELECT
                            SUM(ccc.due_amount)
                        FROM
                            con_contract_cashflow ccc
                        WHERE
                            ccc.contract_id   = b.contract_id AND
                            ccc.cf_direction <> 'NONCASH' AND
                            ccc.cf_item       = 1
                        ) sum_due_amount,
                        (SELECT
                            SUM(ccc.interest)
                        FROM
                            con_contract_cashflow ccc
                        WHERE
                            ccc.contract_id   = b.contract_id AND
                            ccc.cf_direction <> 'NONCASH' AND
                            ccc.cf_item       = 1
                        ) sum_interest,
                        (SELECT
                            SUM(cf.due_amount)
                        FROM
                            con_change_req_cf_test cf
                        WHERE
                            cf.change_req_id = c.change_req_id AND
                            cf.cf_direction <> 'NONCASH' AND
                            cf.cf_item       = 1
                        ) change_sum_due_amount,
                        (SELECT
                            SUM(cf.interest)
                        FROM
                            con_change_req_cf_test cf
                        WHERE
                            cf.change_req_id = c.change_req_id AND
                            cf.cf_direction <> 'NONCASH' AND
                            cf.cf_item       = 1
                        ) change_sum_interest
                    FROM
                        con_batch_adjust_int_test_ln c,
                        con_change_req_flt_test flt,
                        con_contract_v b
                    WHERE
                        c.contract_id   = b.contract_id AND
                        c.change_req_id = flt.change_req_id 
                    ORDER BY
                        b.contract_number
                    ) t1 #WHERE_CLAUSE#
            ]]></bm:query-sql>
        </bm:operation>
        <bm:operation name="execute">
            <bm:update-sql><![CDATA[
            begin
            	con_batch_adjust_interest_pkg.workflow_start(p_batch_hd_id =>${@batch_hd_id},
					                                         p_company_id  =>${/session/@company_id},
					                                         p_user_id     =>${/session/@user_id});
            end;
]]></bm:update-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field name="bp_id_tenant" queryExpression="t1.bp_id_tenant = ${@bp_id_tenant}"/>
        <bm:query-field name="batch_hd_id" queryExpression="t1.batch_hd_id = ${@batch_hd_id}"/>
        <bm:query-field name="project_id" queryExpression="t1.project_id = ${@project_id}"/>
        <bm:query-field name="employee_id" queryExpression="t1.employee_id = ${@employee_id}"/>
        <bm:query-field name="employee_id_of_manager" queryExpression="t1.employee_id_of_manager = ${@employee_id_of_manager}"/>
        <bm:query-field name="lease_organization" queryExpression="t1.lease_organization = ${@lease_organization}"/>
        <bm:query-field name="lease_channel" queryExpression="t1.lease_channel = ${@lease_channel}"/>
        <bm:query-field name="document_type" queryExpression="t1.document_type = ${@document_type}"/>
        <bm:query-field name="contract_status" queryExpression="t1.contract_status = ${@contract_status}"/>
        <bm:query-field name="contract_number" queryExpression="t1.contract_number = ${@contract_number}"/>
        <bm:query-field name="contract_id" queryExpression="t1.contract_id = ${@contract_id}"/>
        <bm:query-field name="lease_start_date_from" queryExpression="lease_start_date &gt;= to_date(${@lease_start_date_from},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="lease_start_date_to" queryExpression="lease_start_date &lt;= to_date(${@lease_start_date_to},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="inception_of_lease_from" queryExpression="inception_of_lease &gt;= to_date(${@inception_of_lease_from},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="inception_of_lease_to" queryExpression="inception_of_lease &lt;= to_date(${@inception_of_lease_to},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="bp_name" queryExpression="t1.bp_name like ${@bp_name}"/>
        <bm:query-field name="project_number" queryExpression="t1.project_number = ${@project_number}"/>
        <bm:query-field name="adjust_flag" queryExpression="t1.adjust_flag = ${@adjust_flag}"/>
        <bm:query-field name="project_name" queryExpression="t1.project_name = ${@project_name}"/>
        <bm:query-field name="contract_name" queryExpression="t1.contract_name = ${@contract_name}"/>
        <bm:query-field name="virtual_con_number" queryExpression="t1.virtual_con_number = ${@virtual_con_number}"/>
        <bm:query-field name="virtual_con_name" queryExpression="t1.virtual_con_name like &apos;%&apos;||${@virtual_con_name}||&apos;%&apos;"/>
    </bm:query-fields>
</bm:model>
