<?xml version="1.0" encoding="UTF-8"?>
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm"  needAccessControl="false">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
                SELECT
                    *
                FROM
                    (
                    select t.contract_id,
                           t.contract_number,
                           t.contract_name,
                           b.bp_code,
                           b.bp_name,
                           (SELECT
                                hls.bp_code
                           FROM
                                hls_bp_master hls
                           WHERE
                                hls.bp_id=t.bp_id_agent_level1
                           )bp_agent_code,
                           (SELECT
                                hls.bp_name
                           FROM
                                hls_bp_master hls
                           WHERE
                                hls.bp_id=t.bp_id_agent_level1
                           )bp_agent,
                           (SELECT
                                h8.description
                           FROM
                                hls_division h8
                           WHERE
                                h8.enabled_flag = 'Y' AND
                                p.division      = h8.division
                           ) division_n,
                            t.lease_execution_date,
                            (SELECT
                                sc.code_value_name
                            FROM
                                sys_code_values_v sc
                            WHERE
                                sc.code = 'CON500_CONTRACT_STATUS'
                            AND
                                t.contract_status = sc.code_value
                            ) contract_status_n,
                            t.lease_channel,
                            (SELECT hl.description
                                FROM hls_lease_channel hl
                                where hl.enabled_flag = 'Y'
                                and hl.lease_channel = t.lease_channel) lease_channel_n,
                            t.contract_status,
                            t.et_date,
                            t.loss_sharing_flag,
                            (select  v.code_value_name as value_name
                              from sys_code_values_v v
                             where v.code = 'YES_NO'
                               and v.code_enabled_flag = 'Y'
                               and v.code_value_enabled_flag = 'Y'
                               and v.code_value =t.loss_sharing_flag
                               ) loss_sharing_flag_n,
                               t.guarantor_info
                    FROM
                          hls_bp_master b,
                          con_contract t,
                          prj_project p
                    WHERE
                          t.bp_id_tenant = b.bp_id(+)
                    AND
                          t.project_id   = p.project_id(+)
                    AND
                          (SELECT
                                sc.code_value
                          FROM
                                sys_code_values_v sc
                          WHERE
                                sc.code = 'CON500_CONTRACT_STATUS'
                          AND
                                 t.contract_status = sc.code_value
                          )  in ('TERMINATE','LOSSSHARE_TERMINATE','AGENT_TERMINATE','INCEPT','ET','VIRTUAL_TERMINATE')
                    AND   t.data_class ='NORMAL'

                    ) v #WHERE_CLAUSE#
                    #ORDER_BY_CLAUSE#
            ]]></bm:query-sql>
        </bm:operation>
         <bm:operation name="update">
             <bm:update-sql><![CDATA[
                begin
                    con_contract_settlement_pkg.update_status(
                    p_contract_id      =>${@contract_id},
                    p_contract_status  =>${@contract_status},
                    p_user_id        =>${/session/@user_id}
                );
                end;
             ]]></bm:update-sql>
         </bm:operation>
    </bm:operations>
    <bm:fields>
            <bm:field name="contract_id"/>
            <bm:field name="contract_number"/>
            <bm:field name="contract_name"/>
            <bm:field name="bp_code"/>
            <bm:field name="bp_name"/>
            <bm:field name="bp_agent_code"/>
            <bm:field name="bp_agent"/>
            <bm:field name="division_n"/>
            <bm:field name="lease_execution_date" databaseType="DATE" datatype="java.util.Date" />
            <bm:field name="contract_status"/>
            <bm:field name="contract_status_n"/>
            <bm:field name="lease_channel"/>
            <bm:field name="lease_channel_n"/>
            <bm:field name="et_date" databaseType="DATE" datatype="java.util.Date"/>
            <bm:field name="loss_sharing_flag"/>
            <bm:field name="loss_sharing_flag_n"/>
            <bm:field name="guarantor_info"/>
        </bm:fields>
    <bm:query-fields>
        <bm:query-field name="lease_channel" queryExpression="v.lease_channel = ${@lease_channel}"/>
        <bm:query-field name="contract_number"  queryexpression="v.contract_number like &apos;%&apos; || ${@contract_number} || &apos;%&apos;"/>
        <bm:query-field name="contract_name" queryexpression="v.contract_name like &apos;%&apos; || ${@contract_name} || &apos;%&apos;"/>
        <bm:query-field name="bp_code"  queryexpression="v.bp_code like &apos;%&apos; || ${@bp_code} || &apos;%&apos;"/>
        <bm:query-field name="bp_name"  queryexpression="v.bp_name = ${@bp_name}"/>
        <bm:query-field name="bp_agent_code"  queryexpression="v.bp_agent_code like &apos;%&apos; || ${@bp_agent_code} || &apos;%&apos;"/>
        <bm:query-field name="bp_agent"  queryexpression="v.bp_agent like &apos;%&apos; || ${@bp_agent} || &apos;%&apos;"/>
        <bm:query-field name="division_n"  queryexpression="v.division_n = ${@division_n}"/>
        <bm:query-field name="contract_status"  queryexpression="v.contract_status = ${@contract_status}"/>
        <bm:query-field name="lease_execution_date_from"  queryExpression="to_char(lease_execution_date,&apos;YYYY-MM-DD&apos;)&gt;=${@lease_execution_date_from}"/>
        <bm:query-field name="lease_execution_date_to"  queryExpression="to_char(lease_execution_date,&apos;YYYY-MM-DD&apos;)&lt;=${@lease_execution_date_to}"/>
        <bm:query-field name="et_date_from"  queryExpression="to_char(et_date,&apos;YYYY-MM-DD&apos;)&gt;=${@et_date_from}"/>
        <bm:query-field name="et_date_to"  queryExpression="to_char(et_date,&apos;YYYY-MM-DD&apos;)&lt;=${@et_date_to}"/>
    </bm:query-fields>
</bm:model>
