<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: huangtianyang
    $Date: 2018/9/12 下午7:20
    $Revision: 1.0 
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" needAccessControl="false">
    <bm:operations>
        <bm:operation name="update">
            <bm:update-sql><![CDATA[
begin
  -- 刷新现金流日期
  con_contract_pkg.contract_incept(p_contract_id => ${@contract_id},
                                   p_exchange_rate_quotation => ${@exchange_rate_quotation},
                                   p_exchange_rate_type => ${@exchange_rate_type},
                                   p_exchange_rate => ${@exchange_rate},
                                   p_inception_of_lease => ${@inception_of_lease},
                                   p_base_rate_new => ${@base_rate_new},
                                   p_refresh_cashflow_only_flag => 'Y',
                                   p_user_id => ${/session/@user_id});
end;

        ]]></bm:update-sql>
        </bm:operation>
        <bm:operation name="execute">
            <bm:update-sql><![CDATA[
begin
  ds_document_interface_pkg.update_contract_from_app(p_contract_id => ${@contract_id},
                                 p_user_id => ${/session/@user_id});


end;

        ]]></bm:update-sql>
        </bm:operation>

        <bm:operation name="insert">
            <bm:update-sql><![CDATA[
            begin
                con_contract_cancle_pkg.contract_cancle(
                   p_contract_id => ${@contract_id},
                   p_user_id     => ${/session/@user_id}
                );

            end;

        ]]></bm:update-sql>
        </bm:operation>

        <bm:operation name="query">
            <bm:query-sql><![CDATA[
                 SELECT
                    su.description user_name,
                    su.user_id,
                    t1.contract_id,
                    t1.document_type,
                    t1.document_category,
                    t1.contract_number,
                    t1.bp_id_tenant,
                    t1.bp_name,
                    t2.bp_class,
                    (SELECT
                        v.code_value_name
                    FROM
                        sys_code_values_v v
                    WHERE
                        v.code       = 'HLS211_BP_CLASS' AND
                        v.code_value = t2.bp_class
                    ) AS bp_class_n,
                    t1.finance_amount,
                    t1.business_type,
                    t1.business_type_desc,
                    t1.lease_channel,
                    t1.lease_channel_desc,
                    t1.division,
                    t1.division_desc,
                    t1.contract_status,
                    t1.contract_status_desc,
                    t1.company_id,
                    DECODE(
                    (SELECT
                        COUNT(1) FROM con_contract_cashflow ccc WHERE ccc.contract_id = t1.contract_id AND
                        ccc.cf_item                                                   = 2 AND
                        ccc.cf_direction                                             <> 'NONCASH' AND
                        ccc.write_off_flag                                           IN ('NOT', 'PARTIAL')
                    ), '0', '完全核销', '未完全核销') down_payment_write_off,
                    t1.execution_status,
                    (SELECT
                        v.code_value_name
                    FROM
                        sys_code_values_v v
                    WHERE
                        v.code      = 'EXECUTION_STATUS_DS' AND
                        v.code_value= t1.execution_status
                    ) execution_status_desc,
                    to_char(t1.LEASE_EXECUTION_date,'yyyy-mm-dd') LEASE_EXECUTION_date,
                    t1.LEASE_EXECUTION_FLAG,
                    (SELECT
                        v.code_value_name
                    FROM
                        sys_code_values_v v
                    WHERE
                        v.code      = 'LEASE_EXECUTION_FLAG' AND
                        v.code_value= nvl(t1.LEASE_EXECUTION_FLAG,'N')) LEASE_EXECUTION_FLAG_desc,

                        to_char(cccr.interface_name) case_interface_name,
                        to_char(cccr.status) case_status ,
                        --ds_common_get_data_pkg.get_case_interface_name(p_contract_id=>t1.contract_id) case_interface_name,
                        --ds_common_get_data_pkg.get_case_status(p_contract_id=>t1.contract_id) case_status,


                         ( select li.pattern from  con_contract_lease_item li
                    where li.equipment_type = 'MAIN'
                   and t1.contract_id = li.contract_id) pattern,
                   ( select li.machine_number from  con_contract_lease_item li
                    where li.equipment_type = 'MAIN'
                   and t1.contract_id = li.contract_id) machine_number,
                   ( select to_char(li.factory_date,'yyyy-mm-dd') factory_date from  con_contract_lease_item li
                    where li.equipment_type = 'MAIN'
                   and t1.contract_id = li.contract_id) factory_date,
                    (select m.extra_nam
                    from hls_bp_master m
                    where m.bp_id = t1.bp_id_agent_level1) agent_extra_nam,
                    (select m.bp_name
                    from hls_bp_master m
                    where m.bp_id = t1.bp_id_agent_level1) agent_name,
                    t1.DOWN_PAYMENT_STATUS,
                    t1.DOWN_PAYMENT_STATUS_N,
                    t1.AREA_DISTINGUISH as AREA_DISTINGUISH
                FROM
                    CON_CONTRACT_V t1,
                    hls_bp_master t2,
                    SYS_USER SU,
                    CALL_CONTRACT_DISTRIBUTION_V CCD,
                    (select /*+ no_merge */
                    document_id, wm_concat(status) status,wm_concat(interface_name) interface_name from (SELECT document_id,(
                            SELECT v.code_value_name value_name
                            FROM   sys_code_values_v v
                            WHERE  v.code = 'CASE_STATUS'
                            AND    v.code_value = cccr.status
                           ) status  ,interface_name
                    FROM   con_contract_case_record cccr
                    WHERE  cccr.document_table = 'CON_CONTRACT')
                    group by document_id) cccr
                #WHERE_CLAUSE#
                --ORDER BY t1.contract_number desc
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field name="division" queryExpression="t1.division = ${@division}"/>
        <bm:query-field name="business_type" queryExpression="t1.business_type = ${@business_type}"/>
        <bm:query-field name="lease_channel" queryExpression="t1.lease_channel = ${@lease_channel}"/>
        <bm:query-field name="contract_status" queryExpression="t1.contract_status = ${@contract_status}"/>
        <bm:query-field name="contract_number" queryExpression="t1.contract_number like ${@contract_number}"/>
        <bm:query-field name="contract_name" queryExpression="t1.contract_name like ${@contract_name}"/>
        <bm:query-field name="execution_status" queryExpression="t1.execution_status like ${@execution_status}"/>
        <bm:query-field name="bp_name" queryExpression="t1.bp_name like ${@bp_name}"/>
        <bm:query-field name="user_name" queryExpression="su.description like ${@user_name}"/>
        <bm:query-field name="bp_code" queryExpression="t1.bp_code like ${@bp_code}"/>
        <bm:query-field name="lease_execution_date_from" queryExpression="t1.LEASE_EXECUTION_date &gt;= to_date(${@lease_execution_date_from},'yyyy-mm-dd')"/>
        <bm:query-field name="lease_execution_date_to" queryExpression="t1.LEASE_EXECUTION_date &lt;= to_date(${@lease_execution_date_to},'yyyy-mm-dd')"/>
        <bm:query-field name="bp_id_agent_level1" queryExpression="t1.bp_id_agent_level1 = ${@bp_id_agent_level1}"/>
        <bm:query-field name="down_payment_status" queryExpression="t1.down_payment_status = ${@down_payment_status}"/>
        <bm:query-field name="agent_extra_nam" queryExpression="(select m.extra_nam from hls_bp_master m where m.bp_id = t1.bp_id_agent_level1) like ${@agent_extra_nam}"/>
    </bm:query-fields>
    <bm:data-filters>
        <bm:data-filter name="join"
                        expression=" t1.bp_id_tenant = t2.bp_id(+) AND ccd.contract_id(+) = t1.contract_id and ccd.user_id = su.user_id(+) and ( t1.lease_execution_flag = 'Y' ) AND cccr.document_id(+)=t1.contract_id "/>
    </bm:data-filters>
</bm:model>