tianzhizy

--行政合同付款
--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