<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: chenlingfeng  
    $Date: 2018-1-18 上午10:35:44  
    $Revision: 1.0  
    $Purpose: 
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
                SELECT
                    *
                FROM
                    (SELECT
                       hd.hd_id,
                        pp.project_id,
                        pp.project_name,
                        pp.bp_name,
                        '落实' workflow_type,
                        i.workflow_desc,i.workflow_code,
                        (SELECT su.description FROM sys_user su WHERE su.user_id = i.submitted_by
                        ) submitted_by_name,
                        (SELECT
                            i2.creation_date
                        FROM
                            zj_wfl_workflow_instance i2
                        WHERE
                            i2.instance_id = i.instance_id
                        ) creation_date,
                        hd.wfl_status,
                        (SELECT
                            v.code_value_name
                        FROM
                            sys_code_values_v v
                        WHERE
                            v.code       = 'CSH900_CONDITION_STATUS' AND
                            v.code_value = hd.wfl_status
                        ) wfl_status_n ,
                        to_date(DECODE(i.instance_status,'1',NULL,i.last_approve_date),'yyyy-mm-dd hh24:mi:ss') last_approve_date,
                        pp.binary_classification_n,pp.hn_industry_classification_n, pp.hn_industry_classification, pp.binary_classification,
                        hd.description,
                        hd.record_type,
                        (SELECT
                            v.code_value_name
                        FROM
                            sys_code_values_v v
                        WHERE
                            v.code       = 'PAYMENT_CON_HD_TYPE' AND
                            v.code_value = hd.record_type
                        ) record_type_n,
                         decode(hd.record_type,'CONTRACT','支付表'||hd.hd_detail,hd.hd_detail ) hd_detail,
                        hd.note,
                        pp.project_number,
                        (SELECT
                            vc.virtual_con_number
                        FROM
                            con_contract cc,
                            prj_project vc
                        WHERE
                            cc.project_id  = vc.project_id AND
                            cc.contract_id = hd.contract_id
                        ) virtual_con_number,
                        '' req_number
                    FROM
                        hn_con_payment_con_hd hd,
                        zj_wfl_workflow_instance_v i,
                        prj_project_lv pp
                    WHERE
                        hd.wfl_instance_id = i.instance_id AND
                        pp.project_id      = hd.project_id
                    UNION ALL
                    SELECT
                    hd.hd_id,
                        pp.project_id,
                        pp.project_name,
                        pp.bp_name,
                        '变更' workflow_type,
                        i.workflow_desc,i.workflow_code,
                        (SELECT su.description FROM sys_user su WHERE su.user_id = i.submitted_by
                        ) submitted_by_name,
                        (SELECT
                            i2.creation_date
                        FROM
                            zj_wfl_workflow_instance i2
                        WHERE
                            i2.instance_id = i.instance_id
                        ) creation_date,
                        ccr.req_status,
                        (SELECT
                            v.code_value_name
                        FROM
                            sys_code_values_v v
                        WHERE
                            v.code       = 'HN_CONDITION_CHANGE_STATUS' AND
                            v.code_value = ccr.req_status
                        ) wfl_status_n ,
                        to_date(DECODE(i.instance_status,'1',NULL,i.last_approve_date),'yyyy-mm-dd hh24:mi:ss') last_approve_date,
                        pp.binary_classification_n,pp.hn_industry_classification_n, pp.hn_industry_classification, pp.binary_classification,
                        hd.description,
                        hd.record_type,
                        (SELECT
                            v.code_value_name
                        FROM
                            sys_code_values_v v
                        WHERE
                            v.code       = 'PAYMENT_CON_HD_TYPE' AND
                            v.code_value = hd.record_type
                        ) record_type_n,
                         decode(hd.record_type,'CONTRACT','支付表'||hd.hd_detail,hd.hd_detail ) hd_detail,
                        hd.note,
                        pp.project_number,
                        (SELECT
                            vc.virtual_con_number
                        FROM
                            con_contract cc,
                            prj_project vc
                        WHERE
                            cc.project_id  = vc.project_id AND
                            cc.contract_id = hd.contract_id
                        ) virtual_con_number,
                        ccr.change_req_number req_number
                    FROM
                        hn_con_payment_con_hd hd,
                        zj_wfl_workflow_instance_v i,
                        prj_project_lv pp,
                        hn_payment_cond_change_req ccr
                    WHERE
                        ccr.wfl_instance_id = i.instance_id AND
                        pp.project_id       = hd.project_id AND
                        hd.hd_id            = ccr.hd_id AND
                        ccr.req_status     <> 'CANCEL'
                    UNION ALL
                    SELECT
                     hd.hd_id,
                        pp.project_id,
                        pp.project_name,
                        pp.bp_name,
                         '变更' workflow_type,
                        i.workflow_desc,i.workflow_code,
                        (SELECT su.description FROM sys_user su WHERE su.user_id = i.submitted_by
                        ) submitted_by_name,
                        (SELECT
                            i2.creation_date
                        FROM
                            zj_wfl_workflow_instance i2
                        WHERE
                            i2.instance_id = i.instance_id
                        ) creation_date,
                        ccrhd.req_status,
                        (SELECT
                            v.code_value_name
                        FROM
                            sys_code_values_v v
                        WHERE
                            v.code       = 'HN_CONDITION_CHANGE_STATUS' AND
                            v.code_value = ccrhd.req_status
                        ) wfl_status_n ,
                        to_date(DECODE(i.instance_status,'1',NULL,i.last_approve_date),'yyyy-mm-dd hh24:mi:ss') last_approve_date,
                        pp.binary_classification_n,pp.hn_industry_classification_n, pp.hn_industry_classification, pp.binary_classification,
                        hd.description,
                        hd.record_type,
                        (SELECT
                            v.code_value_name
                        FROM
                            sys_code_values_v v
                        WHERE
                            v.code       = 'PAYMENT_CON_HD_TYPE' AND
                            v.code_value = hd.record_type
                        ) record_type_n,
                         decode(hd.record_type,'CONTRACT','支付表'||hd.hd_detail,hd.hd_detail ) hd_detail,
                        hd.note,
                        pp.project_number,
                        (SELECT
                            vc.virtual_con_number
                        FROM
                            con_contract cc,
                            prj_project vc
                        WHERE
                            cc.project_id  = vc.project_id AND
                            cc.contract_id = hd.contract_id
                        ) virtual_con_number,
                        ccrhd.change_req_number req_number
                    FROM
                        hn_con_payment_con_hd hd,
                        zj_wfl_workflow_instance_v i,
                        prj_project_lv pp,
                        hn_payment_cond_change_hd ccrhd,
                        hn_payment_cond_change_ln ccrln
                    WHERE
                        ccrhd.wfl_instance_id = i.instance_id AND
                        pp.project_id         = hd.project_id AND
                        hd.hd_id              = ccrln.hd_id AND
                        ccrhd.header_id       = ccrln.header_id AND
                        ccrhd.req_status     <> 'CANCEL'
                    ) t1 #WHERE_CLAUSE#
                    order by t1.project_id,t1.hd_id
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:query-fields>
        <bm:query-field name="wfl_status_n" queryExpression="t1.wfl_status_n = ${@wfl_status_n}"/>
        <bm:query-field name="record_type_n" queryExpression="t1.record_type_n = ${@record_type_n}"/>
        <bm:query-field name="project_name" queryExpression="t1.project_name like ${@project_name}"/>
        <bm:query-field name="bp_name" queryExpression="t1.bp_name like ${@bp_name}"/>
        <bm:query-field name="hn_industry_classification" queryExpression="t1.hn_industry_classification = ${@hn_industry_classification}"/>
        <bm:query-field name="workflow_type" queryExpression="t1.workflow_type = ${@workflow_type}"/>
        <bm:query-field name="creation_date_from" queryExpression="t1.creation_date &gt;=to_date(${@creation_date_from},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="creation_date_to" queryExpression="t1.creation_date &lt;=to_date(${@creation_date_to},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="submitted_by_name" queryExpression="t1.submitted_by_name like ${@submitted_by_name}"/>
        <bm:query-field name="last_approve_date_from" queryExpression="t1.last_approve_date &gt;=to_date(${@last_approve_date_from},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="last_approve_date_to" queryExpression="t1.last_approve_date &lt;=to_date(${@last_approve_date_to},&apos;yyyy-mm-dd&apos;)"/>
    </bm:query-fields>
</bm:model>
