<?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>
