行政合同付款SQL脚本

--行政合同付款
--create view nc_xz_con_pay_v as
select cc.code || '_' || cpcp.PROJECT_ID || '_' || cip.id || '_' ||
cpcp.customer_id 合同编号,
cpcp.start_date 起始日期,
cpcp.end_date 终止日期,
cpcp.amount 付款金额,
'0' 增值税率,
'0' 预估可抵扣税额,
cpcp.amount 预估入账金额,
nvl(cpcp.pay_due_date, cpcp.start_date - 7) 计划付款日期,
'租金' 金额类型,
'否' 是否预提,
'TZ_' || cpcp.id 唯一标识ID
from
fsp_cms.cms_contract cc,
fsp_cms.contract_install_points cip,
fsp_cms.cms_product_contract_payment cpcp
where cc.enabled_flag = 'Y'
and cc.validity_flag = 'Y'
and cc.contract_pro = 'XZZLHT'
and cc.is_part_seal = 'Y'
and cpcp.PROJECT_ID = cip.PROJECT_ID(+)
and cpcp.code = cc.code
and cpcp.relation_type = '01'
and cpcp.enabled_flag = 'Y'
union all
select cc.code || '_' || ccp.id || '_' || cip.id || '_' || cpcp.customer_id 合同编号,
cpcp.start_date 起始日期,
cpcp.end_date 终止日期,
cpcp.amount 付款金额,
'0' 增值税率,
'0' 预估可抵扣税额,
cpcp.amount 预估入账金额,
nvl(cpcp.pay_due_date, cpcp.start_date - 7) 计划付款日期,
'租金' 金额类型,
'否' 是否预提,
'TZ_' || cpcp.id 唯一标识ID
from fsp_cms.cms_contract cc,
fsp_cms.cms_contract_project ccp,
fsp_cms.contract_install_points cip,
fsp_cms.cms_product_contract_payment cpcp,
fsp_cms.cms_contract_deduction ccd,
fsp_cms.cms_product_contract_payment cpcp1,
fsp_cms.cms_contract_payment cp
where cc.enabled_flag = 'Y'
--and cc.validity_flag = 'Y'
and cc.contract_pro = 'XZZLHT'
and cc.is_part_seal = 'Y'
and cc.id = ccp.contract_id
and ccp.enabled_flag = 'Y'
and ccp.id = cip.project_id
and cip.terminal_type != '0203'
and cpcp.relation_type = '05'
and cpcp.relation_id = ccd.id
and cpcp.enabled_flag = 'Y'
and cpcp1.id = ccd.rents_payment_id
and cpcp1.relation_id = cp.id
and cpcp.amount != 0
and cp.instal_points_id = cip.id