<?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 >= to_date(${@actual_loan_date_from},'yyyy-mm-dd')"/>
<bm:query-field name="actual_loan_date_to" queryExpression="t1.actual_loan_date <= to_date(${@actual_loan_date_to},'yyyy-mm-dd')"/>
<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>