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