<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: gaoyang  
    $Date: 2013-9-10 下午02:50:18  
    $Revision: 1.0  
    $Purpose: 
-->
<bm:model xmlns:f="leaf.database.features" xmlns:bm="http://www.leaf-framework.org/schema/bm" alias="t1" baseTable="CON_CONTRACT_CONTENT_V">
    <bm:fields>
        <bm:field name="content_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="CONTENT_ID" prompt="CON_CONTRACT_CONTENT_V.CONTENT_ID"/>
        <bm:field name="contract_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="CONTRACT_ID" prompt="CON_CONTRACT_CONTENT_V.CONTRACT_ID"/>
        <bm:field name="con_contract_bp_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="CON_CONTRACT_BP_ID" prompt="CON_CONTRACT_CONTENT_V.CON_CONTRACT_BP_ID"/>
        <bm:field name="mortgage_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="MORTGAGE_ID" prompt="CON_CONTRACT_CONTENT_V.MORTGAGE_ID"/>
        <bm:field name="content_number" databaseType="VARCHAR2" datatype="java.lang.String" forDisplay="true" forQuery="true" physicalName="CONTENT_NUMBER" prompt="文本编号"/>
        <bm:field name="content_print_flag" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="CONTENT_PRINT_FLAG" prompt="CON_CONTRACT_CONTENT_V.CONTENT_PRINT_FLAG"/>
        <bm:field name="content_print_flag_name" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="CONTENT_PRINT_FLAG_NAME" prompt="CON_CONTRACT_CONTENT_V.CONTENT_PRINT_FLAG_NAME"/>
        <bm:field name="bp_category" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="BP_CATEGORY" prompt="CON_CONTRACT_CONTENT_V.BP_CATEGORY"/>
        <bm:field name="bp_category_desc" databaseType="VARCHAR2" datatype="java.lang.String" forDisplay="true" physicalName="BP_CATEGORY_DESC" prompt="商业伙伴类别"/>
        <bm:field name="bp_class" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="BP_CLASS" prompt="CON_CONTRACT_CONTENT_V.BP_CLASS"/>
        <bm:field name="bp_class_desc" databaseType="VARCHAR2" datatype="java.lang.String" forDisplay="true" physicalName="BP_CLASS_DESC" prompt="合同类型"/>
        <bm:field name="bp_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="BP_ID" prompt="CON_CONTRACT_CONTENT_V.BP_ID"/>
        <bm:field name="bp_name" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="BP_NAME" prompt="CON_CONTRACT_CONTENT_V.BP_NAME"/>
        <bm:field name="available_flag" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="AVAILABLE_FLAG" prompt="CON_CONTRACT_CONTENT_V.AVAILABLE_FLAG"/>
        <bm:field name="employee_id_witness_name" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="EMPLOYEE_ID_WITNESS_NAME" prompt="CON_CONTRACT_CONTENT_V.EMPLOYEE_ID_WITNESS_NAME"/>
        <bm:field name="sign_date" databaseType="DATE" datatype="java.util.Date" physicalName="SIGN_DATE" prompt="CON_CONTRACT_CONTENT_V.SIGN_DATE"/>
        <bm:field name="sign_place" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="SIGN_PLACE" prompt="CON_CONTRACT_CONTENT_V.SIGN_PLACE"/>
        <bm:field name="signer_opposite_side" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="SIGNER_OPPOSITE_SIDE" prompt="CON_CONTRACT_CONTENT_V.SIGNER_OPPOSITE_SIDE"/>
        <bm:field name="notary" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="NOTARY" prompt="CON_CONTRACT_CONTENT_V.NOTARY"/>
        <bm:field name="templet_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="TEMPLET_ID" prompt="CON_CONTRACT_CONTENT_V.TEMPLET_ID"/>
        <bm:field name="templet_code" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="TEMPLET_CODE" prompt="CON_CONTRACT_CONTENT_V.TEMPLET_CODE"/>
        <bm:field name="templet_name" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="TEMPLET_NAME" prompt="CON_CONTRACT_CONTENT_V.TEMPLET_NAME"/>
        <bm:field name="templet_usage" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="TEMPLET_USAGE" prompt="CON_CONTRACT_CONTENT_V.TEMPLET_USAGE"/>
        <bm:field name="clause_usage" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="CLAUSE_USAGE" prompt="CON_CONTRACT_CONTENT_V.CLAUSE_USAGE"/>
        <bm:field name="clause_usage_name" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="CLAUSE_USAGE_NAME" prompt="CON_CONTRACT_CONTENT_V.CLAUSE_USAGE_NAME"/>
        <bm:field name="ref_v01" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="REF_V01" prompt="CON_CONTRACT_CONTENT.REF_V01"/>
        <bm:field name="ref_v02" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="REF_V02" prompt="CON_CONTRACT_CONTENT.REF_V02"/>
        <bm:field name="ref_v03" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="REF_V03" prompt="CON_CONTRACT_CONTENT.REF_V03"/>
        <bm:field name="ref_v04" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="REF_V04" prompt="CON_CONTRACT_CONTENT.REF_V04"/>
        <bm:field name="ref_v05" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="REF_V05" prompt="CON_CONTRACT_CONTENT.REF_V05"/>
        <bm:field name="ref_n01" databaseType="NUMBER" datatype="java.lang.Long" physicalName="REF_N01" prompt="CON_CONTRACT_CONTENT.REF_N01"/>
        <bm:field name="ref_n02" databaseType="NUMBER" datatype="java.lang.Long" physicalName="REF_N02" prompt="CON_CONTRACT_CONTENT.REF_N02"/>
        <bm:field name="ref_n03" databaseType="NUMBER" datatype="java.lang.Long" physicalName="REF_N03" prompt="CON_CONTRACT_CONTENT.REF_N03"/>
        <bm:field name="ref_n04" databaseType="NUMBER" datatype="java.lang.Long" physicalName="REF_N04" prompt="CON_CONTRACT_CONTENT.REF_N04"/>
        <bm:field name="ref_n05" databaseType="NUMBER" datatype="java.lang.Long" physicalName="REF_N05" prompt="CON_CONTRACT_CONTENT.REF_N05"/>
        <bm:field name="ref_d01" databaseType="DATE" datatype="java.util.Date" physicalName="REF_D01" prompt="CON_CONTRACT_CONTENT.REF_D01"/>
        <bm:field name="ref_d02" databaseType="DATE" datatype="java.util.Date" physicalName="REF_D02" prompt="CON_CONTRACT_CONTENT.REF_D02"/>
        <bm:field name="ref_d03" databaseType="DATE" datatype="java.util.Date" physicalName="REF_D03" prompt="CON_CONTRACT_CONTENT.REF_D03"/>
        <bm:field name="ref_d04" databaseType="DATE" datatype="java.util.Date" physicalName="REF_D04" prompt="CON_CONTRACT_CONTENT.REF_D04"/>
        <bm:field name="ref_d05" databaseType="DATE" datatype="java.util.Date" physicalName="REF_D05" prompt="CON_CONTRACT_CONTENT.REF_D05"/>
        <bm:field name="status" databaseType="VARCHAR2" datatype="java.lang.String" />
        <bm:field name="status_desc" databaseType="VARCHAR2" datatype="java.lang.String" />
        <bm:field name="download_flag" databaseType="VARCHAR2" datatype="java.lang.String" />

        <bm:field name="agent_content_flag" databaseType="VARCHAR2" datatype="java.lang.String" />
        <bm:field name="agent_content_flag_name" databaseType="VARCHAR2" datatype="java.lang.String" />
        <bm:field name="download_content_flag" databaseType="VARCHAR2" datatype="java.lang.String" />
        <bm:field name="download_content_flag_name" databaseType="VARCHAR2" datatype="java.lang.String" />

        <!-- <bm:field name="couple_guarantee_flag" databaseType="VARCHAR2" datatype="java.lang.String"/>
        <bm:field name="attach_count" expression="(select count(1) from fnd_atm_attachment_multi m  where m.table_name = &apos;CON_CONTRACT_CONTENT_WORKFLOW&apos;  and m.table_pk_value = to_char(t1.content_id))" forInsert="false" forUpdate="false"/>
        <bm:field name="file_exists_flag" expression="(select &apos;Y&apos;           from fnd_atm_attachment_multi m1          where m1.table_name = &apos;CON_CONTRACT_CONTENT&apos;            and m1.table_pk_value = t1.content_id)"/> -->
    </bm:fields>
    <bm:features>
        <f:standard-who/>
    </bm:features>
    <bm:query-fields>
        <bm:query-field field="content_number" queryOperator="like"/>
    </bm:query-fields>
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
            SELECT * FROM(
            SELECT
			    t1.content_id,
			    t1.contract_id,
			    t1.con_contract_bp_id,
			    t1.mortgage_id,
			    t1.content_number,
			    t1.content_print_flag,
			    t1.content_print_flag_name,
			    t1.bp_category,
			    t1.bp_category_desc,
			    t1.bp_class,
			    t1.bp_class_desc,
			    t1.bp_id,
			    t1.bp_name,
			    t1.available_flag,
			    t1.employee_id_witness_name,
			    t1.sign_date,
			    t1.sign_place,
			    t1.signer_opposite_side,
			    t1.notary,
			    t1.templet_id,
			    t1.templet_code,
			    t1.templet_name,
			    t1.templet_usage,
			    t1.clause_usage,
			    t1.clause_usage_name,
			    t1.ref_v01,
			    t1.ref_v02,
			    t1.ref_v03,
			    t1.ref_v04,
			    t1.ref_v05,
			    t1.ref_n01,
			    t1.ref_n02,
			    t1.ref_n03,
			    t1.ref_n04,
			    t1.ref_n05,
			    t1.ref_d01,
			    t1.ref_d02,
			    t1.ref_d03,
			    t1.ref_d04,
			    t1.ref_d05,
			    t1.couple_guarantee_flag,
			    (SELECT
			        COUNT(1)
			    FROM
			        fnd_atm_attachment_multi m
			    WHERE
			        m.table_name     = 'CON_CONTRACT_CONTENT_WORKFLOW' AND
			        m.table_pk_value = TO_CHAR(t1.content_id)
			    ) AS attach_count,
			    (SELECT
			        'Y'
			    FROM
			        fnd_atm_attachment_multi m1
			    WHERE
			        m1.table_name     = 'CON_CONTRACT_CONTENT' AND
			        m1.table_pk_value = t1.content_id
			    ) AS file_exists_flag,
			      CASE
                 WHEN (SELECT COUNT(*)
                         FROM con_contract_signature s
                        WHERE s.content_id = t1.content_id
                          AND s.status = 2) >= 1 THEN
                  '2'
                 WHEN (SELECT COUNT(*)
                         FROM con_contract_signature s
                        WHERE s.content_id = t1.content_id
                          AND s.status = 3) >= 1 THEN
                  '3'
                 WHEN (SELECT COUNT(*)
                         FROM con_contract_signature s
                        WHERE s.content_id = t1.content_id
                          AND s.status = 4) >= 1 THEN
                  '4'
                  WHEN (SELECT COUNT(*)
                         FROM con_contract_signature s
                        WHERE s.content_id = t1.content_id
                          AND s.status = 5) >= 1 THEN
                  '5'
                 ELSE
                  '1'
               END AS status,
			      CASE
                 WHEN (SELECT COUNT(*)
                         FROM con_contract_signature s
                        WHERE s.content_id = t1.content_id
                          AND s.status = 2) >= 1 THEN
                  '部分签署'
                 WHEN (SELECT COUNT(*)
                         FROM con_contract_signature s
                        WHERE s.content_id = t1.content_id
                          AND s.status = 3) >= 1 THEN
                  '全部签署'
                 WHEN (SELECT COUNT(*)
                         FROM con_contract_signature s
                        WHERE s.content_id = t1.content_id
                          AND s.status = 4) >= 1 THEN
                  '已盖章'
                 WHEN (SELECT COUNT(*)
                         FROM con_contract_signature s
                        WHERE s.content_id = t1.content_id
                          AND s.status = 5) >= 1 THEN
                  '已拒绝'
                 ELSE
                  '未签署'
               END AS status_desc,
                (SELECT COUNT(*)  FROM con_contract_signature s WHERE s.content_id = t1.content_id AND s.status = 4)download_flag,
                t1.agent_content_flag,
                t1.download_content_flag,
                t1.agent_content_flag_name,
                t1.download_content_flag_name
			FROM
			    CON_CONTRACT_CONTENT_V t1
			WHERE
			    t1.templet_code !='GH_WITHHOLD_AGREE'
			    UNION ALL 
		    SELECT
			    t1.content_id,
			    t1.contract_id,
			    t1.con_contract_bp_id,
			    t1.mortgage_id,
			    t1.content_number,
			    t1.content_print_flag,
			    t1.content_print_flag_name,
			    t1.bp_category,
			    t1.bp_category_desc,
			    t1.bp_class,
			    t1.bp_class_desc,
			    t1.bp_id,
			    t1.bp_name,
			    t1.available_flag,
			    t1.employee_id_witness_name,
			    t1.sign_date,
			    t1.sign_place,
			    t1.signer_opposite_side,
			    t1.notary,
			    t1.templet_id,
			    t1.templet_code,
			    t1.templet_name,
			    t1.templet_usage,
			    t1.clause_usage,
			    t1.clause_usage_name,
			    t1.ref_v01,
			    t1.ref_v02,
			    t1.ref_v03,
			    t1.ref_v04,
			    t1.ref_v05,
			    t1.ref_n01,
			    t1.ref_n02,
			    t1.ref_n03,
			    t1.ref_n04,
			    t1.ref_n05,
			    t1.ref_d01,
			    t1.ref_d02,
			    t1.ref_d03,
			    t1.ref_d04,
			    t1.ref_d05,
			    t1.couple_guarantee_flag,
			    (SELECT
			        COUNT(1)
			    FROM
			        fnd_atm_attachment_multi m
			    WHERE
			        m.table_name     = 'CON_CONTRACT_CONTENT_WORKFLOW' AND
			        m.table_pk_value = TO_CHAR(t1.content_id)
			    ) AS attach_count,
			    (SELECT
			        'Y'
			    FROM
			        fnd_atm_attachment_multi m1
			    WHERE
			        m1.table_name     = 'CON_CONTRACT_CONTENT' AND
			        m1.table_pk_value = t1.content_id
			    ) AS file_exists_flag,
			      CASE
                 WHEN (SELECT COUNT(*)
                         FROM con_contract_signature s
                        WHERE s.content_id = t1.content_id
                          AND s.status = 2) >= 1 THEN
                  '2'
                 WHEN (SELECT COUNT(*)
                         FROM con_contract_signature s
                        WHERE s.content_id = t1.content_id
                          AND s.status = 3) >= 1 THEN
                  '3'
                 WHEN (SELECT COUNT(*)
                         FROM con_contract_signature s
                        WHERE s.content_id = t1.content_id
                          AND s.status = 4) >= 1 THEN
                  '4'
                 WHEN (SELECT COUNT(*)
                         FROM con_contract_signature s
                        WHERE s.content_id = t1.content_id
                          AND s.status = 5) >= 1 THEN
                  '5'
                 ELSE
                  '1'
               END AS status,
			     CASE
                 WHEN (SELECT COUNT(*)
                         FROM con_contract_signature s
                        WHERE s.content_id = t1.content_id
                          AND s.status = 2) >= 1 THEN
                  '部分签署'
                 WHEN (SELECT COUNT(*)
                         FROM con_contract_signature s
                        WHERE s.content_id = t1.content_id
                          AND s.status = 3) >= 1 THEN
                  '全部签署'
                 WHEN (SELECT COUNT(*)
                         FROM con_contract_signature s
                        WHERE s.content_id = t1.content_id
                          AND s.status = 4) >= 1 THEN
                  '已盖章'
                 WHEN (SELECT COUNT(*)
                         FROM con_contract_signature s
                        WHERE s.content_id = t1.content_id
                          AND s.status = 5) >= 1 THEN
                  '已拒绝'
                 ELSE
                  '未签署'
               END AS status_desc,
               (SELECT COUNT(*)  FROM con_contract_signature s WHERE s.content_id = t1.content_id AND s.status = 4)download_flag,
                t1.agent_content_flag,
                t1.download_content_flag,
                t1.agent_content_flag_name,
                t1.download_content_flag_name
			FROM
			    CON_CONTRACT_CONTENT_V t1
			WHERE
			    t1.templet_code='GH_WITHHOLD_AGREE'
			    AND NVL(t1.withhold_way,'0') = 'SPD')t
    		 #WHERE_CLAUSE#
    		 ORDER BY decode(t.bp_category,'TENANT',0,'GUARANTOR',1,'VENDER',2,'')
		]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:data-filters>
        <bm:data-filter enforceOperations="query" expression="t.contract_id=${@contract_id}"/>
    </bm:data-filters>
</bm:model>
