<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: chenlingfeng7543
$Date: 2018/8/8 10:26
$Revision: 1.0 
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
                SELECT t.contract_number,
                       t.contract_id,
                       t.bp_name,
                       (SELECT v.code_value_name
                          FROM sys_code_values_v v
                         WHERE v.code = 'YES_NO'
                           AND v.code_value = nvl(t.information_completed,'N')) information_completed,
                       li.pattern,
                       li.machine_number,
                       to_char(li.factory_date, 'yyyy-mm-dd') factory_date,
                       t.bp_id_agent_level1_n,
                       --ds_common_get_data_pkg.get_call_status(p_wo_id => wo.wo_id) call_status,
                       cd0.CALL_RESULT_N tenant_verify_result,
                       cd0.CLASS_ONE_N || cd0.CLASS_TWO_N tenant_class,
                       hbm1.bp_name guarantor_1_name,
                       cd1.CALL_RESULT_N guarantor_1_VERIFY_RESULT,
                       cd1.CLASS_ONE_N || cd1.CLASS_TWO_N guarantor_1_class,
                       hbm2.bp_name guarantor_2_name,
                       cd2.CALL_RESULT_N guarantor_2_VERIFY_RESULT,
                       cd2.CLASS_ONE_N || cd2.CLASS_TWO_N guarantor_2_class,
                       cd0.detail_note description,
                       ccd.user_id,
                       u.description user_name,
                       (select m.extra_nam
                          from hls_bp_master m
                         where m.bp_id = t.bp_id_agent_level1) agent_extra_nam,
                         t.DOWN_PAYMENT_STATUS,
                         t.DOWN_PAYMENT_STATUS_N,
                         t.AREA_DISTINGUISH as AREA_DISTINGUISH,
                         t.execution_status,
                        (SELECT
                                v.code_value_name
                        FROM
                                sys_code_values_v v
                        WHERE
                                v.code      = 'EXECUTION_STATUS_DS' AND
                                v.code_value= t.execution_status
                        ) execution_status_desc,
                        t.last_call_time,
                         (select count(record_id)
			                from call_detail_record_map cdrm
			                where cd0.call_id = cdrm.call_id) record_times
                  FROM con_contract_v t
                  LEFT OUTER JOIN work_orders wo
                    ON t.contract_id = wo.contract_id
                   AND wo.business_type = '0'
                  LEFT OUTER JOIN con_contract_lease_item li
                    ON li.equipment_type = 'MAIN'
                   AND t.contract_id = li.contract_id
                  LEFT OUTER JOIN con_contract_bp g1
                    ON t.contract_id = g1.contract_id
                   AND g1.bp_seq = 1
                   AND g1.bp_category = 'GUARANTOR'
                  LEFT OUTER JOIN con_contract_bp g2
                    ON t.contract_id = g2.contract_id
                   AND g2.bp_seq = 2
                   AND g2.bp_category = 'GUARANTOR'
                  LEFT OUTER JOIN CALL_CONTRACT_DISTRIBUTION_V ccd
                    ON t.contract_id = ccd.contract_id
                  LEFT OUTER JOIN con_last_call_detail_v cd0
                    ON cd0.wo_id = wo.wo_id
                   AND cd0.call_detail_type = 'TENANT'
                   AND cd0.bp_id = t.bp_id_tenant
                  LEFT OUTER JOIN con_last_call_detail_v cd1
                    ON cd1.wo_id = wo.wo_id
                   AND cd1.call_detail_type = 'GUARANTOR'
                   AND cd1.bp_id = g1.bp_id
                  LEFT OUTER JOIN con_last_call_detail_v cd2
                    ON cd2.wo_id = wo.wo_id
                   AND cd2.call_detail_type = 'GUARANTOR'
                   AND cd2.bp_id = g2.bp_id
                  LEFT OUTER JOIN sys_user u
                    ON u.user_id = ccd.user_id
                  LEFT OUTER JOIN hls_bp_master hbm1
                    ON hbm1.bp_id = g1.bp_id
                  LEFT OUTER JOIN hls_bp_master hbm2
                    ON hbm2.bp_id = g2.bp_id
                #WHERE_CLAUSE#
]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field name="division" queryExpression="t.division = ${@division}"/>
        <bm:query-field name="business_type" queryExpression="t.business_type = ${@business_type}"/>
        <bm:query-field name="lease_channel" queryExpression="t.lease_channel = ${@lease_channel}"/>
        <bm:query-field name="contract_status" queryExpression="t.contract_status = ${@contract_status}"/>
        <bm:query-field name="contract_number"  queryExpression="t.contract_number like ${@contract_number}"/>
        <bm:query-field name="contract_name" queryExpression="t.contract_name like ${@contract_name}"/>
        <bm:query-field name="bp_name" queryExpression="t.bp_name like ${@bp_name}"/>
        <bm:query-field name="bp_id_agent_level1_n" queryExpression="t.bp_id_agent_level1_n like ${@bp_id_agent_level1_n}"/>
        <bm:query-field name="bp_code" queryExpression="t.bp_code like ${@bp_code}"/>
        <bm:query-field name="information_completed" queryExpression="nvl(t.information_completed,'N') = ${@information_completed}"/>
        <bm:query-field name="bp_id_agent_level1" queryExpression="t.bp_id_agent_level1 = ${@bp_id_agent_level1}"/>
        <bm:query-field name="agent_extra_nam" queryExpression="(select m.extra_nam from hls_bp_master m where m.bp_id = t.bp_id_agent_level1) like ${@agent_extra_nam}"/>
        <bm:query-field name="down_payment_status" queryExpression="t.down_payment_status = ${@down_payment_status}"/>
        <bm:query-field name="user_name" queryExpression="u.description like ${@user_name}"/>
    </bm:query-fields>
    <bm:data-filters>
    <bm:data-filter name="status" expression="t.contract_status = 'SIGN'
        and nvl(wo.call_status, 'N') = 'N'"/>

    </bm:data-filters>
</bm:model>