<?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.*
                FROM
                    (SELECT
                        cc.contract_id,
                        pp.project_id,
                        pp.chance_no,
                        cc.contract_number,
                        cc.bp_id_tenant,
                        (SELECT bp.bp_name FROM hls_bp_master bp WHERE bp.bp_id = cc.bp_id_tenant
                        ) bp_id_tenant_n,
                        cc.bp_id_agent_level1,
                        (SELECT
                            bp.bp_name
                        FROM
                            hls_bp_master bp
                        WHERE
                            bp.bp_id = cc.bp_id_agent_level1
                        ) bp_id_agent_level1_n,
                        cc.insurance_rate,
                        cc.insurance_plan,
                        cc.insurance_method,
                        (SELECT
                            v.code_value_name
                        FROM
                            sys_code_values_v v
                        WHERE
                            v.code       = 'INSURANCE_METHOD' AND
                            v.code_value = cc.insurance_method
                        ) insurance_method_n,
                        cc.insurance_number,
                        to_char(cc.insurance_end_date,'yyyy-mm-dd') insurance_end_date,
                        to_char(cc.real_insurance_date,'yyyy-mm-dd') real_insurance_date,
                        cc.lease_term,
                        cci.machine_number,
                        cci.item_engine_number
                    FROM
                        con_contract cc,
                        prj_project pp,
                        con_contract_lease_item cci
                    WHERE
                        cc.project_id  = pp.project_id AND
                        cc.contract_id = cci.contract_id AND
                        cc.data_class  = 'NORMAL' AND
                        cci.equipment_type = 'MAIN'
                    ) t1#WHERE_CLAUSE#
                ORDER BY
                    t1.contract_number
            ]]></bm:query-sql>
        </bm:operation>
        <bm:operation name="update">
            <bm:update-sql><![CDATA[
                begin
                    contract_insurance_pkg.change_ds_insurance(p_contract_id => ${@contract_id},p_bp_id => ${@bp_id},p_insurance_rate => ${@insurance_rate}, p_user_id => ${/session/@user_id});
                end;
               ]]></bm:update-sql>
        </bm:operation>
        <bm:operation name="execute">
            <bm:update-sql><![CDATA[
                begin
                    contract_insurance_pkg.change_other_insurance(p_contract_id => ${@contract_id},p_insurance_method =>${@insurance_method}, p_user_id => ${/session/@user_id});
                end;
               ]]></bm:update-sql>
        </bm:operation>
    </bm:operations>
    <bm:fields>
        <bm:field name="contract_id"/>
        <bm:field name="project_id"/>
        <bm:field name="chance_no"/>
        <bm:field name="contract_number"/>
        <bm:field name="bp_id_tenant"/>
        <bm:field name="bp_id_tenant_n"/>
        <bm:field name="bp_id_agent_level1"/>
        <bm:field name="bp_id_agent_level1_n"/>
        <bm:field name="insurance_rate"/>
        <bm:field name="insurance_plan"/>
        <bm:field name="insurance_method"/>
        <bm:field name="insurance_method_n"/>
        <bm:field name="machine_number"/>
        <bm:field name="item_engine_number"/>
        <bm:field name="insurance_number"/>
        <bm:field name="insurance_end_date"/>
        <bm:field name="real_insurance_date"/>
        <bm:field name="lease_term"/>
    </bm:fields>
    <bm:query-fields>
        <bm:query-field field="contract_id" queryOperator="="/>
    </bm:query-fields>
</bm:model>