<?xml version="1.0" encoding="UTF-8"?>
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" xmlns:s="leaf.plugin.script">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
                Select t1.*
                From (Select t.doc_id,
                cc.contract_id,
                t.apply_id,
                (Select bp.bp_name
                From hls_bp_master bp
                Where bp.bp_id = t.apply_id) apply_id_n,
                nvl(cc.trans_cert_print_flag, 'N') trans_cert_print_flag,
                t.cert_recipient01,
                t.cert_tracknum01,
                to_char(t.cert_send_date01, 'yyyy-mm-dd') cert_send_date01,
                t.cert_recipient02,
                t.cert_tracknum02,
                to_char(t.cert_send_date02, 'yyyy-mm-dd') cert_send_date02,
                t.trans_recipient01,
                t.trans_tracknum01,
                to_char(t.trans_send_date01, 'yyyy-mm-dd') trans_send_date01,
                t.trans_recipient02,
                t.trans_tracknum02,
                to_char(t.trans_send_date02, 'yyyy-mm-dd') trans_send_date02,
                --nvl(t.send_flag, 'N') send_flag,
                nvl((Select 'Y'
                From con_contract_et_document a
                Where a.doc_id = t.doc_id
                And (a.cert_send_date01 Is Not Null Or
                a.cert_tracknum01 Is Not Null Or
                a.cert_recipient01 Is Not Null)),
                'N') As send_flag,
                (Select v.code_value_name
                From sys_code_values_v v
                Where v.code = 'YES_NO'
                And v.code_value = nvl((Select 'Y'
                From con_contract_et_document a
                Where a.doc_id = t.doc_id
                And (a.cert_send_date01 Is Not Null Or
                a.cert_tracknum01 Is Not Null Or
                a.cert_recipient01 Is Not Null)),
                'N')
                And v.code_enabled_flag = 'Y'
                And v.code_value_enabled_flag = 'Y') send_flag_n,
                t.note,
                cc.contract_number,
                cc.bp_id_tenant,
                (Select a.bp_name
                From hls_bp_master a
                Where a.bp_id = cc.bp_id_tenant) bp_name,
                cc.bp_id_agent_level1,
                (Select a.bp_name
                From hls_bp_master a
                Where a.bp_id = cc.bp_id_agent_level1) agent_name,
                cc.business_type,
                (Select t.description
                From hls_business_type t
                Where t.business_type = cc.business_type
                And t.enabled_flag = 'Y') business_type_n,
                to_char(cc.lease_end_date, 'yyyy-mm-dd') lease_end_date,
                to_char(cc.et_date, 'yyyy-mm-dd') et_date,

                to_char(cc.lease_execution_date, 'yyyy-mm-dd') lease_execution_date,
                cc.contract_status,
                (SELECT
                sc.code_value_name
                FROM
                sys_code_values_v sc
                WHERE
                sc.code = 'CON500_CONTRACT_STATUS'
                AND
                cc.contract_status = sc.code_value
                ) contract_status_n
                From con_contract_et_document t, con_contract cc
                Where t.contract_id(+) = cc.contract_id
                and cc.contract_status in ('TERMINATE','LOSSSHARE_TERMINATE','AGENT_TERMINATE','ET')
                And cc.data_class = 'NORMAL'
                And exists (Select 1
                From aut_trx_user_authorize a1, aut_owner_user_authorize a2
                Where a1.trx_category = 'CONTRACT'
                And a1.trx_id = cc.contract_id
                And trunc(Sysdate) Between a1.start_date And
                nvl(a1.end_date, trunc(Sysdate))
                And a1.user_id = a2.owner_user_id
                And a1.trx_category = a2.trx_category
                And a2.authorized_user_id = ${/session/@user_id}
                And trunc(Sysdate) Between a2.start_date And
                nvl(a2.end_date, trunc(Sysdate)))


                ) t1


                #WHERE_CLAUSE#
                --Order By t1.contract_number Desc
                ]]>
            </bm:query-sql>
        </bm:operation>

        <bm:operation name="update">
            <bm:update-sql><![CDATA[
                begin
                con_contract_et_pkg.update_send_info(
                p_contract_id => ${@contract_id},
                p_cert_recipient01 => ${@cert_recipient01},
                p_cert_tracknum01 => ${@cert_tracknum01},
                p_cert_send_date01 => to_date(${@cert_send_date01} ,'yyyy-mm-dd'),

                p_cert_recipient02 => ${@cert_recipient02},
                p_cert_tracknum02 => ${@cert_tracknum02},
                p_cert_send_date02 => to_date(${@cert_send_date02} ,'yyyy-mm-dd'),


                p_trans_recipient01 => ${@trans_recipient01},
                p_trans_tracknum01 => ${@trans_tracknum01},
                p_trans_send_date01 => to_date(${@trans_send_date01},'yyyy-mm-dd'),

                p_trans_recipient02 => ${@trans_recipient02},
                p_trans_tracknum02 => ${@trans_tracknum02},
                p_trans_send_date02 => to_date(${@trans_send_date02},'yyyy-mm-dd'),
                p_note => ${@note},
                p_user_id => ${/session/@user_id});

                end;
                ]]>
            </bm:update-sql>
        </bm:operation>
    </bm:operations>
    <bm:fields>
        <bm:field name="doc_id"/>
        <bm:field name="contract_id"/>
        <bm:field name="apply_id"/>
        <bm:field name="apply_id_n"/>
        <bm:field name="cert_recipient01"/>
        <bm:field name="cert_tracknum01"/>
        <bm:field name="cert_send_date01"/>
        <bm:field name="cert_recipient02"/>
        <bm:field name="cert_tracknum02"/>
        <bm:field name="cert_send_date02"/>
        <bm:field name="trans_recipient01"/>
        <bm:field name="trans_tracknum01"/>
        <bm:field name="trans_send_date01"/>
        <bm:field name="trans_recipient02"/>
        <bm:field name="trans_tracknum02"/>
        <bm:field name="trans_send_date02"/>
        <bm:field name="send_flag"/>
        <bm:field name="send_flag_n"/>
        <bm:field name="note"/>
        <bm:field name="contract_number"/>
        <bm:field name="bp_id_tenant"/>
        <bm:field name="bp_name"/>
        <bm:field name="bp_id_agent_level1"/>
        <bm:field name="agent_name"/>
        <bm:field name="business_type"/>
        <bm:field name="business_type_n"/>
        <bm:field name="lease_end_date"/>
        <bm:field name="et_date"/>
        <bm:field name="trans_cert_print_flag"/>
        <bm:field name="lease_execution_date"/>
        <bm:field name="contract_status"/>
        <bm:field name="contract_status_n"/>
    </bm:fields>
    <bm:query-fields>
        <bm:query-field field="contract_id" queryOperator="="/>
        <bm:query-field field="contract_number" queryOperator="="/>
        <bm:query-field field="bp_name" queryOperator="like"/>
        <bm:query-field field="agent_name" queryOperator="like"/>
        <bm:query-field name="lease_end_date"
                        queryExpression="t1.lease_end_date =  ${@lease_end_date}"/>
        <!-- <bm:query-field name="et_date"
                         queryExpression="t1.et_date =  ${@et_date}"/>-->
        <bm:query-field name="send_flag"
                        queryExpression="t1.send_flag =  ${@send_flag}"/>
        <bm:query-field name="trans_cert_print_flag"
                        queryExpression="t1.trans_cert_print_flag =  ${@trans_cert_print_flag}"/>

        <bm:query-field name="lease_execution_date_from"
                        queryExpression="t1.lease_execution_date &gt;=  ${@lease_execution_date_from}"/>
        <bm:query-field name="lease_execution_date_to"
                        queryExpression="t1.lease_execution_date &lt;=  ${@lease_execution_date_to}"/>
        <bm:query-field name="et_date_from"
                        queryExpression="t1.et_date &gt;=   ${@et_date_from}"/>
        <bm:query-field name="et_date_to"
                        queryExpression="t1.et_date &lt;= ${@et_date_to}"/>

        <bm:query-field name="cert_send_date01_from"
                        queryExpression="t1.cert_send_date01 &gt;=   ${@cert_send_date01_from}"/>
        <bm:query-field name="cert_send_date01_to"
                        queryExpression="t1.cert_send_date01 &lt;= ${@cert_send_date01_to}"/>

        <bm:query-field name="trans_send_date01_from"
                        queryExpression="t1.trans_send_date01 &gt;=   ${@trans_send_date01_from}"/>
        <bm:query-field name="trans_send_date01_to"
                        queryExpression="t1.trans_send_date01 &lt;= ${@trans_send_date01_to}"/>
        <bm:query-field name="contract_status"  queryexpression="t1.contract_status = ${@contract_status}"/>
    </bm:query-fields>

    <bm:features>
        <s:bm-script><![CDATA[
            var cx = Packages.leaf.javascript.Context.getCurrentContext();
            Packages.leaf.plugin.script.engine.ScriptImportor.defineExternScript(cx, this, $ctx.getData(),
            "aut_authority_bm_validate.js");
            ]]>
        </s:bm-script>
    </bm:features>
</bm:model>
