<?xml version="1.0" encoding="UTF-8"?>
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" xmlns:f="leaf.database.features" alias="t1" baseTable="ZJ_WFL_INSTANCE_NODE_RECIPIENT">
    <bm:fields>
        <bm:field name="instance_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="INSTANCE_ID" prompt="ZJ_WFL_INSTANCE_NODE_RECIPIENT.INSTANCE_ID"/>
        <bm:field name="approve_count" databaseType="NUMBER" datatype="java.lang.Long" physicalName="APPROVE_COUNT" prompt="ZJ_WFL_INSTANCE_NODE_RECIPIENT.APPROVE_COUNT"/>
        <bm:field name="node_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="NODE_ID" prompt="ZJ_WFL_INSTANCE_NODE_RECIPIENT.NODE_ID"/>
        <bm:field name="submitted_by" databaseType="NUMBER" datatype="java.lang.Long" physicalName="SUBMITTED_BY" prompt="ZJ_WFL_INSTANCE_NODE_RECIPIENT.NODE_ID"/>
        <bm:field name="submitted_by_name" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="SUBMITTED_BY_NAME"/>
        <bm:field name="seq_number" databaseType="NUMBER" datatype="java.lang.Long" physicalName="SEQ_NUMBER" prompt="ZJ_WFL_INSTANCE_NODE_RECIPIENT.SEQ_NUMBER"/>
        <bm:field name="user_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="USER_ID" prompt="ZJ_WFL_INSTANCE_NODE_RECIPIENT.USER_ID"/>
        <bm:field name="date_limit" databaseType="DATE" datatype="java.util.Date" physicalName="DATE_LIMIT" prompt="ZJ_WFL_INSTANCE_NODE_RECIPIENT.DATE_LIMIT"/>
        <bm:field name="record_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="RECORD_ID" prompt="ZJ_WFL_INSTANCE_NODE_RECIPIENT.RECORD_ID"/>
        <bm:field name="commision_by" databaseType="NUMBER" datatype="java.lang.Long" physicalName="COMMISION_BY" prompt="ZJ_WFL_INSTANCE_NODE_RECIPIENT.COMMISION_BY"/>
        <bm:field name="commision_desc" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="COMMISION_DESC" prompt="ZJ_WFL_INSTANCE_NODE_RECIPIENT.COMMISION_DESC"/>
        <bm:field name="last_notify_date" databaseType="DATE" datatype="java.util.Date" physicalName="LAST_NOTIFY_DATE" prompt="ZJ_WFL_INSTANCE_NODE_RECIPIENT.LAST_NOTIFY_DATE"/>
        <bm:field name="hierarchy_record_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="HIERARCHY_RECORD_ID" prompt="ZJ_WFL_INSTANCE_NODE_RECIPIENT.HIERARCHY_RECORD_ID"/>
        <bm:field name="workflow_id" databaseType="NUMBER" datatype="java.lang.Long" forInsert="false" forUpdate="false"/>
        <bm:field name="workflow_code" databaseType="VARCHAR2" datatype="java.lang.String" forInsert="false" forUpdate="false"/>
        <bm:field name="document_info" databaseType="VARCHAR2" datatype="java.lang.String" forInsert="false" forUpdate="false"/>
        <bm:field name="workflow_info" databaseType="VARCHAR2" datatype="java.lang.String" forInsert="false" forUpdate="false"/>
        <bm:field name="node_desc" databaseType="VARCHAR2" datatype="java.lang.String" forInsert="false" forUpdate="false"/>
        <bm:field name="workflow_desc" databaseType="VARCHAR2" datatype="java.lang.String" forInsert="false" forUpdate="false"/>
        <bm:field name="creation_date_format" databaseType="VARCHAR2" datatype="java.lang.String" forInsert="false" forUpdate="false"/>
        <bm:field name="urgent_type"/>
        <bm:field name="position_code"/>
        <bm:field name="node_sequence_num"/>
        <bm:field name="record_type"/>
        <bm:field name="isreturn"/>
        <bm:field name="finance_amount"/>
        <bm:field name="down_payment_ratio"/>
        <bm:field name="price_list_n"/>
        <bm:field name="division"/>
        <bm:field name="expand_flag"/>
        <bm:field name="child_workflow_desc"/>
        <bm:field name="color_flag"/>
    </bm:fields>
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
                SELECT
                    v.*,
                    rownum order_seq
                FROM
                    (SELECT
                        *
                    FROM
                        (SELECT
                            NULL instance_id,
                            NULL approve_count,
                            NULL submitted_by,
                            NULL submitted_by_name,
                            NULL node_id,
                            NULL node_desc,
                            NULL hierarchy_record_id,
                            NULL seq_number,
                            NULL user_id,
                            NULL date_limit,
                            NULL record_id,
                            NULL commision_by,
                            NULL commision_desc,
                            NULL last_notify_date,
                            NULL workflow_id,
                            NULL workflow_code,
                            NULL workflow_desc,
                            t1.workflow_desc child_workflow_desc,
                            NULL workflow_info,
                            NULL document_info,
                            NULL isreturn,
                            NULL creation_date_format,
                            NULL urgent_type,
                            NULL position_code,
                            NULL node_sequence_num,
                            NULL record_type,
                            NULL finance_amount,     --申请融资总额
                            NULL down_payment_ratio, --首付款比例
                            NULL price_list_n,       --产品方案
                            NULL division,
                            'Y' expand_flag,
                             NULL color_flag
                        FROM
                            zj_wfl_instance_node_rcpt_v t1,
                            zj_wfl_workflow zww,
                            zj_wfl_workflow_type zwwt
                        WHERE
                            t1.user_id            = ${/session/@user_id} AND
                            zww.workflow_id       = t1.workflow_id AND
                            zwwt.workflow_type_id = zww.workflow_type_id
                        GROUP BY
                            t1.workflow_desc,
                            zwwt.workflow_type_code
                        UNION ALL
                        SELECT
                            t1.instance_id,
                            t1.approve_count,
                            t1.submitted_by,
                            t1.submitted_by_name,
                            t1.node_id,
                            t1.node_desc,
                            t1.hierarchy_record_id,
                            t1.seq_number,
                            t1.user_id,
                            t1.date_limit,
                            t1.record_id,
                            t1.commision_by,
                            t1.commision_desc,
                            t1.last_notify_date,
                            t1.workflow_id,
                            t1.workflow_code,
                            t1.workflow_desc,
                            NULL child_workflow_desc,
                            t1.workflow_desc
                            || '-'
                            || (
                                CASE
                                    WHEN t1.node_type ='END_NODE' AND
                                        t1.record_type='NOTICE'
                                    THEN '工作流结束通知'
                                    ELSE t1.node_desc
                                END) AS workflow_info,
                            t1.document_info,
                            (SELECT
                                COUNT(1)
                            FROM
                                prj_project p,
                                yonda_doc_status_history h
                            WHERE
                                h.document_id = p.project_id AND
                                h.status      = '60' AND
                                t1.document_info LIKE p.project_number
                                ||'%'
                            ) isreturn,
                            TO_CHAR(t1.creation_date, 'yyyy-mm-dd hh24:mi:ss') AS creation_date_format,
                            t1.urgent_type,
                            (SELECT
                                e.position_code
                            FROM
                                exp_emp_assign_e_v e
                            WHERE
                                e.user_id              =t1.user_id AND
                                e.primary_position_flag='Y'
                            )position_code,
                            (SELECT
                                sequence_num
                            FROM
                                zj_wfl_workflow_node n
                            WHERE
                                n.node_id = t1.node_id
                            ) node_sequence_num,
                            t1.record_type,
                            (SELECT
                                TO_CHAR(pp.finance_amount,'fm999,999,999,999,999,990.00')
                            FROM
                                prj_project pp
                            WHERE
                                pp.wfl_instance_id = t1.instance_id
                            )finance_amount, --申请融资总额
                            (SELECT
                                NVL(TO_CHAR(pp.down_payment_ratio*100 ,'fm999,999,999,999,999,990.009'),0)
                                || '%'
                            FROM
                                prj_project pp
                            WHERE
                                pp.wfl_instance_id = t1.instance_id
                            )down_payment_ratio, --首付款比例
                            (SELECT
                                ppl.price_list_n
                            FROM
                                prj_project_lease_item_lv ppl,
                                prj_project pp
                            WHERE
                                ppl.project_id     = pp.project_id AND
                                pp.wfl_instance_id = t1.instance_id AND
                                ppl.price_list    IS NOT NULL
                            )price_list_n, --产品方案
                            (SELECT
                                pp.division_n
                            FROM
                                prj_project_lv pp
                            WHERE
                                pp.wfl_instance_id = t1.instance_id
                            )division,
                            'Y' expand_flag,
                            nvl((SELECT
                                DECODE(
                                (SELECT
                                    COUNT(1) FROM prj_project pp WHERE pp.project_id = tt.project_id AND
                                    pp.note                                              IS NOT NULL
                                ), 0, 'N', 'Y')
                            FROM
                                prj_project tt where tt.wfl_instance_id = t1.instance_id and t1.workflow_code = 'WFL_PRJ'
                               
                            ),'N')color_flag
                        FROM
                            zj_wfl_instance_node_rcpt_v t1   #WHERE_CLAUSE#
                        ) t
                    ORDER BY
                        NVL(t.workflow_desc,t.child_workflow_desc) ASC,
                        t.date_limit DESC
                    )v
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:features>
        <f:standard-who/>
    </bm:features>
    <bm:primary-key>
        <bm:pk-field name="record_id"/>
    </bm:primary-key>
    <bm:data-filters>
        <bm:data-filter enforceOperations="query" expression="t1.user_id = ${/session/@user_id}"/>
        <bm:data-filter enforceOperations="query" expression="NOT EXISTS( select 1 from dual where  t1.node_type =&apos;END_NODE&apos; and (trunc(SYSDATE) - trunc(t1.creation_date)) &gt;= 3)"/>
    </bm:data-filters>
    <bm:query-fields>
        <bm:query-field name="any_info" queryExpression="(t1.workflow_desc like  &apos;%&apos;|| ${@any_info} || &apos;%&apos; or t1.node_desc like  &apos;%&apos;|| ${@any_info} || &apos;%&apos; or t1.document_info like  &apos;%&apos;|| ${@any_info} || &apos;%&apos; or to_char(t1.creation_date, &apos;yyyy-mm-dd hh24:mi:ss&apos;) like &apos;%&apos;|| ${@any_info} || &apos;%&apos; or t1.submitted_by_name like &apos;%&apos;|| ${@any_info} || &apos;%&apos; )"/>
        <bm:query-field name="workflow_info" queryExpression="(t1.workflow_desc like  &apos;%&apos;|| ${@workflow_info} || &apos;%&apos; or t1.node_desc like  &apos;%&apos;|| ${@workflow_info} || &apos;%&apos;)"/>
        <bm:query-field name="document_info" queryExpression="t1.document_info like  &apos;%&apos;|| ${@document_info} || &apos;%&apos;"/>
    </bm:query-fields>
</bm:model>
