<?xml version="1.0" encoding="UTF-8"?>

<!--
    $Author: chenlingfeng
    $Date: 2018-7-26 下午4:26:32
    $Revision: 1.0
    $Purpose: 执行合同清单
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm">
    <bm:operations>
        <bm:operation name="update">
            <bm:update-sql><![CDATA[
begin
  --代理商代付客户保证金
  con_contract_custom_pkg.con_create_agent_deposit(p_contract_id => ${@contract_id},
                                                   p_con_deposit_by_agent => ${@con_deposit_by_agent},
                                                   p_user_id => ${/session/@user_id});
end;

        ]]></bm:update-sql>
        </bm:operation>
        <bm:operation name="execute">
            <bm:update-sql><![CDATA[
        begin
          -- 合同执行
          con_contract_incept_wfl_pkg.workflow_start(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,
                    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,
                    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#

            ]]></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="bp_name" queryExpression="t1.bp_name like ${@bp_name}"/>
        <bm:query-field name="execution_status" queryExpression="t1.execution_status like ${@execution_status}"/>
        <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="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.CONTRACT_STATUS = 'SIGN'  or t1.lease_execution_flag = 'C' and t1.contract_status = 'PENDING')  and EXISTS                    (SELECT     1    FROM   work_orders wo    WHERE   wo.business_type = '0' AND    wo.call_status   = 'Y' AND       wo.contract_id   = t1.contract_id )  AND cccr.document_id(+)=t1.contract_id"/>
    </bm:data-filters>
</bm:model>
