con_contract_file.lwm 16 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 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272
<?xml version="1.0" encoding="UTF-8"?>
<bm:model xmlns:bm="http://www.leaf-framework.org/schema/bm" alias="t1" baseTable="CON_CONTRACT_FILE">
    <bm:fields>
        <bm:field name="contract_id"/>
        <bm:field name="pk"/>
        <bm:field name="file_code"/>
        <bm:field name="file_name"/>
        <bm:field name="save_table"/>
        <bm:field name="save_table_pk"/>
        <bm:field name="attach_file_name"/>
        <bm:field name="atm_count"/>
        <bm:field name="note"/>
    </bm:fields>

    <bm:operations>
        <bm:operation name="query">
            <bm:query-sql><![CDATA[
            Select v.*, cn.note
              From (Select to_number(${@contract_id}) contract_id,
                           t1.cdd_item_id pk,
                           t1.cdd_item file_code,
                           t1.description file_name,

                           'PRJ_CDD_ITEM_CHECK' save_table,
                           t2.check_id save_table_pk,

                           hls_sys_upload_pkg.get_atm_file_href(p_table_pk_value => t2.check_id,
                                                                p_source_type    => 'PRJ_CDD_ITEM_CHECK',
                                                                p_user_id        => 1) As attach_file_name,
                           (ds_common_get_data_pkg.get_atm_counts(p_table_name     => 'PRJ_CDD_ITEM_CHECK',
                                                                  p_table_pk_value => t2.check_id)) As atm_count
                      From prj_cdd_item t1, prj_cdd_item_check t2
                     Where t1.cdd_item_id = t2.cdd_item_id
                       And t1.cdd_list_id =
                           (Select cc.cdd_list_id
                              From con_contract cc
                             Where cc.data_class = 'NORMAL'
                               And cc.contract_id = ${@contract_id}) --合同cdd_list_id   信审阶段上传
                    Union All
                    Select to_number(${@contract_id}) contract_id,
                           t3.contract_attch_id pk,
                           '' file_code,
                           '承租人变更附件' file_name,

                           'CCR_UNIVERSAL_LEASSE_ATTH' save_table,
                           t3.contract_attch_id save_table_pk,

                           hls_sys_upload_pkg.get_atm_file_href(p_table_pk_value => t3.contract_attch_id,
                                                                p_source_type    => 'CCR_UNIVERSAL_LEASSE_ATTH',
                                                                p_user_id        => 1) As attach_file_name,
                           (ds_common_get_data_pkg.get_atm_counts(p_table_name     => 'CCR_UNIVERSAL_LEASSE_ATTH',
                                                                  p_table_pk_value => t3.contract_attch_id)) As atm_count
                      From ccr_universal_leasse_atth t3
                     Where t3.contract_id In
                           (Select ccr.change_req_id
                              From con_contract_change_req ccr
                             Where ccr.contract_id = ${@contract_id}
                               And ccr.req_status = 'APPROVED'
                               And ccr.document_type = 'LEASSE_CHAG') ----合同变更申请-承租人变更
                    Union All
                    Select to_number(${@contract_id}) contract_id,

                           t4.contract_attch_id pk,
                           '' file_code,
                           '冬雨季延期附件' file_name,

                           'CCR_UNIVERSAL_E_ATTH' save_table,
                           t4.contract_attch_id save_table_pk,

                           hls_sys_upload_pkg.get_atm_file_href(p_table_pk_value => t4.contract_attch_id,
                                                                p_source_type    => 'CCR_UNIVERSAL_E_ATTH',
                                                                p_user_id        => 1) As attach_file_name,
                           (ds_common_get_data_pkg.get_atm_counts(p_table_name     => 'CCR_UNIVERSAL_E_ATTH',
                                                                  p_table_pk_value => t4.contract_attch_id)) As atm_count
                      From ccr_universal_e_atth t4
                     Where t4.contract_id In
                           (Select ccr.change_req_id
                              From con_contract_change_req ccr
                             Where ccr.contract_id = ${@contract_id}
                               And ccr.req_status = 'APPROVED'
                               And ccr.document_type = 'LEASSE_CHAG') -----合同变更申请-冬雨季延期
                    Union All
                    Select to_number(${@contract_id}) contract_id,
                           t5.contract_attch_id pk,
                           '' file_code,
                           '非月付转月付附件' file_name,

                           'CCR_UNIVERSAL_PAY_ATTH' save_table,
                           t5.contract_attch_id save_table_pk,

                           hls_sys_upload_pkg.get_atm_file_href(p_table_pk_value => t5.contract_attch_id,
                                                                p_source_type    => 'CCR_UNIVERSAL_PAY_ATTH',
                                                                p_user_id        => 1) As attach_file_name,
                           (ds_common_get_data_pkg.get_atm_counts(p_table_name     => 'CCR_UNIVERSAL_PAY_ATTH',
                                                                  p_table_pk_value => t5.contract_attch_id)) As atm_count
                      From ccr_universal_pay_atth t5
                     Where t5.contract_id In
                           (Select ccr.change_req_id
                              From con_contract_change_req ccr
                             Where ccr.contract_id = ${@contract_id}
                               And ccr.req_status = 'APPROVED'
                               And ccr.document_type = 'PAY_CHAG') ----合同变更申请-非月付转月付
                    Union All
                    Select to_number(${@contract_id}) contract_id,

                           t6.contract_attch_id pk,
                           '' file_code,
                           '租赁物变更附件' file_name,

                           'CCR_UNIVERSAL_LH_ATTH' save_table,
                           t6.contract_attch_id save_table_pk,

                           hls_sys_upload_pkg.get_atm_file_href(p_table_pk_value => t6.contract_attch_id,
                                                                p_source_type    => 'CCR_UNIVERSAL_LH_ATTH',
                                                                p_user_id        => 1) As attach_file_name,
                           (ds_common_get_data_pkg.get_atm_counts(p_table_name     => 'CCR_UNIVERSAL_LH_ATTH',
                                                                  p_table_pk_value => t6.contract_attch_id)) As atm_count
                      From ccr_universal_lh_atth t6
                     Where t6.contract_id In
                           (Select ccr.change_req_id
                              From con_contract_change_req ccr
                             Where ccr.contract_id = ${@contract_id}
                               And ccr.req_status = 'APPROVED'
                               And ccr.document_type = 'LEASEHOLD_CHAG') --租赁物变更

                    Union All
                    Select to_number(${@contract_id}) contract_id,
                           t7.contract_attch_id pk,
                           '' file_code,
                           '债权债务变更附件' file_name,

                           'CCR_UNIVERSAL_DEPT_ATTH' save_table,
                           t7.contract_attch_id save_table_pk,

                           hls_sys_upload_pkg.get_atm_file_href(p_table_pk_value => t7.contract_attch_id,
                                                                p_source_type    => 'CCR_UNIVERSAL_DEPT_ATTH',
                                                                p_user_id        => 1) As attach_file_name,
                           (ds_common_get_data_pkg.get_atm_counts(p_table_name     => 'CCR_UNIVERSAL_DEPT_ATTH',
                                                                  p_table_pk_value => t7.contract_attch_id)) As atm_count
                      From ccr_universal_dept_atth t7
                     Where t7.contract_id In
                           (Select ccr.change_req_id
                              From con_contract_change_req ccr
                             Where ccr.contract_id = ${@contract_id}
                               And ccr.req_status = 'APPROVED'
                               And ccr.document_type = 'DEBT_CHAG') --债权债务变更
                    Union All
                    Select to_number(${@contract_id}) contract_id,
                           t8.contract_attch_id pk,
                           '' file_code,
                           '部分变更附件' file_name,

                           'CCR_UNIVERSAL_PART_ATTH' save_table,
                           t8.contract_attch_id save_table_pk,

                           hls_sys_upload_pkg.get_atm_file_href(p_table_pk_value => t8.contract_attch_id,
                                                                p_source_type    => 'CCR_UNIVERSAL_PART_ATTH',
                                                                p_user_id        => 1) As attach_file_name,
                           (ds_common_get_data_pkg.get_atm_counts(p_table_name     => 'CCR_UNIVERSAL_PART_ATTH',
                                                                  p_table_pk_value => t8.contract_attch_id)) As atm_count
                      From ccr_universal_part_atth t8
                     Where t8.contract_id In
                           (Select ccr.change_req_id
                              From con_contract_change_req ccr
                             Where ccr.contract_id = ${@contract_id}
                               And ccr.req_status = 'APPROVED'
                               And ccr.document_type = 'PART_CHAG') --合同变更申请-部分变更
                    Union All
                    Select to_number(${@contract_id}) contract_id,
                           t9.contract_id pk,
                           '' file_code,
                           '保单附件' file_name,

                           'CONTRACT_INSURANCE' save_table,
                           t9.contract_id save_table_pk,
                           hls_sys_upload_pkg.get_atm_file_href(p_table_pk_value => t9.contract_id,
                                                                p_source_type    => 'CONTRACT_INSURANCE',
                                                                p_user_id        => 1) As attach_file_name,
                           (ds_common_get_data_pkg.get_atm_counts(p_table_name     => 'CONTRACT_INSURANCE',
                                                                  p_table_pk_value => t9.contract_id)) As atm_count
                      From contract_insurance_lv t9
                     Where t9.contract_id = ${@contract_id} --保单附件
                    Union All

select t10.contract_id,
       t10.content_id pk,
       'CON_CONTRACT_CONTENT' file_code,
       t10.content_number file_name,
       'CON_CONTRACT_CONTENT' save_table,
       t10.content_id save_table_pk,
       hls_sys_upload_pkg.get_atm_file_href(p_table_pk_value => t10.content_id,
                                            p_source_type    => 'CON_CONTRACT_CONTENT',
                                            p_user_id        => 1) As attach_file_name,
       (ds_common_get_data_pkg.get_atm_counts(p_table_name     => 'CON_CONTRACT_CONTENT',
                                              p_table_pk_value => t10.content_id)) As atm_count
  from con_contract_content t10
 WHERE t10.contract_id = ${@contract_id}
                             Union All

select t11.contract_id,
       t11.content_id pk,
       'CON_CONTRACT_CONTENT_SIGN_ATTCH' file_code,
       t11.content_number||'扫描件' file_name,
       'CON_CONTRACT_CONTENT_SIGN_ATTCH' save_table,
       t11.content_id save_table_pk,
       hls_sys_upload_pkg.get_atm_file_href(p_table_pk_value => t11.content_id,
                                            p_source_type    => 'CON_CONTRACT_CONTENT_SIGN_ATTCH',
                                            p_user_id        => 1) As attach_file_name,
       (ds_common_get_data_pkg.get_atm_counts(p_table_name     => 'CON_CONTRACT_CONTENT_SIGN_ATTCH',
                                              p_table_pk_value => t11.content_id)) As atm_count
  from con_contract_content t11
 WHERE t11.content_print_flag = 'Y'
   and t11.contract_id = ${@contract_id}
                             union all
 Select t12.contract_id contract_id,
       t12.invoice_line_id pk,
       'ACP_INVOICE_LN' file_code,
       '进项发票' file_name,
       'ACP_INVOICE_LN' save_table,
       t12.invoice_line_id save_table_pk,
       hls_sys_upload_pkg.get_atm_file_href(p_table_pk_value => t12.invoice_line_id,
                                            p_source_type    => 'ACP_INVOICE_LN',
                                            p_user_id        => 1) As attach_file_name,
       (ds_common_get_data_pkg.get_atm_counts(p_table_name     => 'ACP_INVOICE_LN',
                                              p_table_pk_value => t12.invoice_line_id)) As atm_count
  From ACP_INVOICE_LN t12
 Where t12.contract_id = ${@contract_id}
                             ) v,
                   con_archive_atth_note cn
             Where v.contract_id = cn.contract_id(+)
              and v.save_table = cn.save_table(+)
               And v.save_table_pk = cn.save_table_pk(+)
               #ORDER_BY_CLAUSE#
                    ]]></bm:query-sql>
        </bm:operation>
        <bm:operation name="update">
            <bm:update-sql><![CDATA[
            begin

                con_contract_archive_pkg.save_archive_data(p_contract_id       => ${@contract_id},
                                                            p_ele_doc_archive_flag => ${@ele_doc_archive_flag},
                                                            p_archive_flag         => ${@archive_flag},
                                                            p_complete_flag        => ${@complete_flag},
                                                            p_note                 => ${@note},
                                                            p_cert_receive_flag    => ${@cert_receive_flag},
                                                            p_doc_warehouse         => ${@doc_warehouse},
                                                            p_doc_cabinet          => ${@doc_cabinet},
                                                            p_archive_date         => to_date(${@archive_date},'yyyy-mm-dd'),
                                                            p_doc_layers           => ${@doc_layers},
                                                            p_doc_order             => ${@doc_order},
                                                            p_res_doc_cabinet       => ${@res_doc_cabinet},
                                                            p_res_doc_layers        => ${@res_doc_layers},
                                                            p_res_doc_order        => ${@res_doc_order},
                                                            p_user_id              => ${/session/@user_id});


             end;
                    ]]></bm:update-sql>
        </bm:operation>
        <bm:operation name="execute">
            <bm:update-sql><![CDATA[
            begin
             con_contract_file_pkg.get_all_contract_file(p_contract_id  => ${@contract_id},
                                                         p_project_id   => '',
                                                         p_quotation_id => '',
                                                         p_cdd_list_id  => ${@cdd_list_id},
                                                         p_user_id      => ${/session/@user_id});
             end;
                    ]]></bm:update-sql>
        </bm:operation>
    </bm:operations>
</bm:model>