<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: Hongquan.Dai  
    $Date: 2018-3-14 下午6:53:26  
    $Revision: 1.0  
    $Purpose: 
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" alias="t1">
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
            select *
            from
                (
                SELECT
                    pi.project_number, --项目编号
                    ta.contract_number,--支付表编号
                    ta.check_id,
                    ta.bp_id_tenant,
                    (SELECT h.bp_name FROM hls_bp_master h WHERE h.bp_id = ta.bp_id_tenant
                    ) AS bp_name,--承租人
                    pi.invoice_agent_id,
                    (SELECT
                        a.bp_name value_name
                    FROM
                        hls_bp_master_lv a
                    WHERE
                        a.bp_category       = 'AGENT' AND
                        pi.invoice_agent_id = a.bp_id
                    ) AS invoice_agent_id_n,--经销商
                    pi.unit_id,
                    (SELECT u.unit_name FROM exp_org_unit_v u WHERE u.unit_id = pi.unit_id
                    ) AS unit_id_n,--区域
                    pi.province,
                    (SELECT
                        tg.description
                    FROM
                        fnd_province tg
                    WHERE
                        tg.province_id = pi.province
                    ) AS province_n,                          --省份
                    ta.inception_of_lease AS actual_loan_date,--放款时间
                    ta.description,                           --文档名称
                    ta.file_name,                             --附件名
                    ta.paper_apply_flag,                      --纸质归档申请
                    ta.doc_type_n,                            --原件/复印件
                    ta.amount,                                --归档份数
                    ta.doc_address,                           --归档位置
                    ta.note,                                  --备注
                    ta.paper_content_flag,                    --纸质是否归档
                    ta.paper_content_flag_n,
                    ta.archive_reason                         --未入库原因
                FROM
                    (SELECT
                        t.project_id,
                        pcc.check_id,
                        t.bp_id_tenant,
                        t.inception_of_lease,
                        t.contract_number,
                        t.contract_id document_id,
                        pci.description,                                                                                                                                   -------------
                        pci.note,                                                                                                                                                  -----
                        pci.paper_content_flag,
                        (SELECT V.CODE_VALUE_NAME 
                         FROM SYS_CODE_VALUES_V V
 						 WHERE V.CODE = 'YES_NO'
  						 AND V.CODE_VALUE = pci.paper_content_flag) as  paper_content_flag_n,                                                                                                                                ----------
                        hls_sys_upload_pkg.get_atm_file_href(p_table_pk_value => pcc.check_id,p_source_type => 'PRJ_CDD_ITEM_CHECK',p_user_id => ${/session/@user_id}) AS file_name,----
                        (SELECT
                            v.code_value_name AS value_name
                        FROM
                            sys_code_values_v v
                        WHERE
                            v.code      = 'DOC_TYPE' AND
                            v.code_value=NVL(pci.DOC_TYPE,pcit.DOC_TYPE)
                        ) DOC_TYPE_N,                        -----------
                        NVL(pci.AMOUNT, pcit.AMOUNT) AS AMOUNT,    -----
                        NVL(pci.DOC_TYPE,pcit.DOC_TYPE) AS DOC_TYPE,----
                        pci.doc_address,                      ----------
                        pci.archive_reason,                  -----------
                        pci.paper_apply_flag                        ----
                    FROM
                        prj_cdd_item pci,
                        prj_cdd_item_check pcc,
                        con_contract t,
                        prj_cdd_item_templet pcit,
                        prj_cdd_item_templet_hd pt
                    WHERE
                        pt.templet_code = (
                            CASE
                                WHEN t.DIVISION='00'
                                THEN 'HY_CDD_NP_00'
                                WHEN t.DIVISION='01'
                                THEN 'HY_CDD_NP_01'
                                ELSE 'HY_CDD_NP_00'
                            END ) AND
                            t.contract_status not in ('CLOSED') AND
                        pcit.templet_head_id =pt.templet_head_id(+) AND
                        pci.cdd_item         =pcit.cdd_item(+) AND
                        pci.cdd_item_id(+)   = pcc.cdd_item_id AND
                        t.cdd_list_id        = pci.cdd_list_id AND
                        pci.enabled_flag     = 'Y' AND
                        (
                            pci.cdd_item_id IN
                            (SELECT
                                t1.cdd_item_id
                            FROM
                                prj_cdd_item_list_grp_tab T1,
                                PRJ_CDD_ITEM_TAB_GROUP T2
                            WHERE
                                t1.cdd_list_id = t.cdd_list_id AND
                                (
                                    T2.TAB_GROUP_ID IN
                                    (SELECT
                                        T3.TAB_GROUP_ID
                                    FROM
                                        PRJ_CDD_ITEM_TAB_GROUP T4,
                                        PRJ_CDD_ITEM_TAB_SUB_GROUP T3
                                    WHERE
                                        T4.TAB_GROUP_ID=T3.PARENT_TAB_GROUP_ID AND
                                        T4.TAB_GROUP   ='DATA_ARCHIVING'
                                    UNION
                                    SELECT
                                        T4.TAB_GROUP_ID
                                    FROM
                                        PRJ_CDD_ITEM_TAB_GROUP T4
                                    WHERE
                                        T4.TAB_GROUP='DATA_ARCHIVING'
                                    )
                                )
                                AND
                                t2.tab_group_id = t1.tab_group_id
                            ) OR
                            NVL(pci.sys_flag, 'N')='N'
                        )
                    UNION ALL
                    SELECT
                        t.project_id,
                        pcc.check_id,
                        t.bp_id_tenant,
                        t.inception_of_lease,
                        t.contract_number,
                        t.contract_id document_id,
                        pci.description,                                                                                                                                     -----------
                        pci.note,                                                                                                                                                -------
                        pci.paper_content_flag,   
                        (SELECT V.CODE_VALUE_NAME 
                         FROM SYS_CODE_VALUES_V V
 						 WHERE V.CODE = 'YES_NO'
  						 AND V.CODE_VALUE = pci.paper_content_flag) as  paper_content_flag_n,                                                                                                                      --------------
                        hls_sys_upload_pkg.get_atm_file_href(p_table_pk_value => pcc.check_id,p_source_type => 'PRJ_CDD_ITEM_CHECK',p_user_id => ${/session/@user_id}) AS file_name,----
                        NULL DOC_TYPE_N,                                                                                                                                        --------
                        pci.AMOUNT AS AMOUNT,                                                                                                                                       ----
                        pci.DOC_TYPE AS DOC_TYPE,                                                                                                                                 ------
                        pci.doc_address,                                                                                                                                     -----------
                        pci.archive_reason,                                                                                                                                  -----------
                        pci.paper_apply_flag                                                                                                                                  ----------
                    FROM
                        prj_cdd_item pci,
                        prj_cdd_item_check pcc,
                        con_contract t
                    WHERE
                        pci.cdd_item_id(+) = pcc.cdd_item_id AND
                        t.cdd_list_id      = pci.cdd_list_id AND
                        pci.enabled_flag   = 'Y' AND
                         t.contract_status not in ('CLOSED')
                         AND 
                        (
                            pci.cdd_item_id IN
                            (SELECT
                                t1.cdd_item_id
                            FROM
                                prj_cdd_item_list_grp_tab T1,
                                PRJ_CDD_ITEM_TAB_GROUP T2
                            WHERE
                                t1.cdd_list_id = t.cdd_list_id AND
                                (
                                    T2.TAB_GROUP_ID IN
                                    (SELECT
                                        T3.TAB_GROUP_ID
                                    FROM
                                        PRJ_CDD_ITEM_TAB_GROUP T4,
                                        PRJ_CDD_ITEM_TAB_SUB_GROUP T3
                                    WHERE
                                        T4.TAB_GROUP_ID=T3.PARENT_TAB_GROUP_ID AND
                                        T4.TAB_GROUP   ='DATA_ARCHIVING'
                                    UNION
                                    SELECT
                                        T4.TAB_GROUP_ID
                                    FROM
                                        PRJ_CDD_ITEM_TAB_GROUP T4
                                    WHERE
                                        T4.TAB_GROUP='DATA_ARCHIVING'
                                    )
                                )
                                AND
                                t2.tab_group_id = t1.tab_group_id
                            ) OR
                            NVL(pci.sys_flag, 'N')='N'
                        )
                    ) ta,
                    prj_project pi
                WHERE
                    pi.project_id(+) = ta.project_id) t1
                 #WHERE_CLAUSE#
            ]]></bm:query-sql>
        </bm:operation>
    </bm:operations>
    <bm:fields>
        <bm:field name="project_number"/>
        <bm:field name="contract_number"/>
        <bm:field name="bp_name"/>
        <bm:field name="invoice_agent_id_n"/>
        <bm:field name="unit_id_n"/>
        <bm:field name="province_n"/>
        <bm:field name="actual_loan_date"/>
        <bm:field name="description"/>
        <bm:field name="file_name"/>
        <bm:field name="paper_apply_flag"/>
        <bm:field name="paper_content_flag_n"/>
        <bm:field name="doc_type_n"/>
        <bm:field name="amount"/>
        <bm:field name="doc_address"/>
        <bm:field name="note"/>
        <bm:field name="paper_content_flag"/>
        <bm:field name="archive_reason"/>
        <bm:field name="bp_id_tenant"/>
        <bm:field name="unit_id"/>
        <bm:field name="province"/>
        <bm:field name="invoice_agent_id"/>
        <bm:field name="check_id"/>
    </bm:fields>
    <bm:query-fields>
        <bm:query-field field="project_number" queryOperator="like"/>
        <bm:query-field field="contract_number" queryOperator="like"/>
        <bm:query-field field="bp_name" queryOperator="like"/>
        <bm:query-field field="invoice_agent_id_n" queryOperator="like"/>
        <bm:query-field field="unit_id_n" queryOperator="like"/>
        <bm:query-field field="province_n" queryOperator="like"/>
        <bm:query-field name="actual_loan_date_from" queryExpression="t1.actual_loan_date &gt;=  to_date(${@actual_loan_date_from},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field name="actual_loan_date_to" queryExpression="t1.actual_loan_date &lt;=  to_date(${@actual_loan_date_to},&apos;yyyy-mm-dd&apos;)"/>
        <bm:query-field field="paper_content_flag" queryOperator="="/>
        <bm:query-field field="paper_content_flag_n" queryOperator="="/>
        <bm:query-field field="description" queryOperator="like"/>
    </bm:query-fields>
</bm:model>
