prj520_file_query.lwm 7.78 KB
Newer Older
Spencer Chang's avatar
Spencer Chang committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167
<?xml version="1.0" encoding="UTF-8"?>
<!--
    $Author: gaoyang  
    $Date: 2013-9-10 下午02:50:18  
    $Revision: 1.0  
    $Purpose: 
-->
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm">
    <bm:fields>
        <bm:field name="description" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="DESCRIPTION"/>
        <bm:field name="note" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="NOTE"/>
        <bm:field name="file_name" databaseType="VARCHAR2" datatype="java.lang.String" physicalName="FILE_NAME"/>
        <bm:field name="doc_require_n" databaseType="VARCHAR2" datatype="java.lang.String"/>
        <bm:field name="check_id" databaseType="NUMBER" datatype="java.lang.Long" physicalName="CHECK_ID"/>
    </bm:fields>
    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
                SELECT
                    *
                FROM
                    (SELECT
                    	pcc.check_id,
                        pci.description,
                        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,
                        nvl2(pci.doc_require, pci.doc_require, pcit.doc_require) doc_require_n,
                        nvl2(pci.doc_require,
                        (SELECT
                            v.code_value_name AS value_name
                        FROM
                            sys_code_values_v v
                        WHERE
                            v.code       = 'DOC_REQUIRE' AND
                            v.code_value = pci.doc_require
                        ),
                        (SELECT
                            v.code_value_name AS value_name
                        FROM
                            sys_code_values_v v
                        WHERE
                            v.code       = 'DOC_REQUIRE' AND
                            v.code_value = pcit.doc_require
                        )) doc_require,
                        pci.note
                    FROM
                        prj_cdd_item pci,
                        prj_cdd_item_check pcc,
                        prj_project 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
                        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'
                        )
                        AND
                        t.project_id = ${/parameter/@project_id}
                    UNION ALL
                    SELECT
                    	pcc.check_id,
                        pci.description,
                        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,
                        pci.doc_require doc_require_n,
                        (SELECT
                            v.code_value_name AS value_name
                        FROM
                            sys_code_values_v v
                        WHERE
                            v.code       = 'DOC_REQUIRE' AND
                            v.code_value = pci.doc_require
                        ) doc_require,
                        pci.note
                    FROM
                        prj_cdd_item pci,
                        prj_cdd_item_check pcc,
                        prj_project 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
                        (
                            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'
                        )
                        AND
                        t.project_id = ${/parameter/@project_id}
                    ) t1
                WHERE
                    t1.file_name IS NULL
            ]]></bm:query-sql>
            <bm:parameters>
                <bm:parameter inputPath="/parameter/@project_id"/>
            </bm:parameters>
        </bm:operation>
    </bm:operations>
</bm:model>