--行政租入合同信息
--create view nc_xz_con_v as
select ff.code 业务单元编号,
ff.NAME 业务单元名称,
'' 成本中心编号,
'' 成本中心名称,
fo.code 部门编码,
fo.name 部门名称,
'' 经办人ID,
'' 经办人名称,
cc.code || '_' || ccp.id || '_' || cip.id || '_' ||
pay.payment_object_id 合同编号,
'否' 是否自动生成合同编号,
decode(cip.terminal_type,
'01',
'梯内屏',
'02',
'梯外屏',
'03',
'框架') 租赁物名称,
decode(cip.terminal_type,
'01',
'010101',
'02',
'010102',
'03',
'010103',
cip.terminal_type) 租赁物类别,
'人民币' 币种,
cc.sign_date 合同签订日期,
ccp.start_time 起租日期,
ccp.end_time 止租日期,
ccp.start_time 租金起算日期,
case
when to_char(ccp.start_time, 'YYYY-MM-DD') < '2021-01-01' then
'2021-01-01'
else
to_char(ccp.start_time, 'YYYY-MM-DD')
end 业务变更日期,
'否' 是否豁免,
decode(ccp.payment_type,
'CMS_PRODUCT_QUARTERLY_PAYMENT',
'季度付',
'CMS_PRODUCT_YEAR_PAYMENT',
'季度付',
'CMS_PRODUCT_SEMI_ANNUAL',
'季度付',
'CMS_PRODUCT_MONTH_PAYMENT',
'季度付') 租金支付频率,
0 付款提前天数,
0 租入面积,
fv.vendor_code 出租人编号,
fv.vendor_name 出租人名称,
'2021-01-01' 使权资起始日期,
'2021-01-01' 租负债起始日期,
'' 首期付款额,
'0%' 税率,
'否' 是否含税,
'否' 增值税是否可以抵扣,
'' 折现率,
'0' 初始直接费用,
'0' 预计复原成本,
'0' 租赁激励措施金,
'' 行使购买选择权日期,
'' 行购买金额,
'0' 押金,
'0' 定金,
'否' 是否简易征收,
'' 简易税率,
'是' 是否已识别资产,
'是' 承所有经济利益,
'是' 承租人主导资产使用权,
'002：>40000 RMB' 租赁资产价值,
'0' 预付租金,
'' 资产剩余使用期限,
'0' 初未摊销完待摊费用,
'' 备注,
cc.code 主合同编号,
'' 项目编码,
ccp.project_name 项目名称,
ccp.project_property 项目性质,
cip.signing_num 签约数,
cc.city_code 城市编码,
city.city_name 城市名字,
'IP_' || cc.code || '_' || cip.id || '_' || pay.payment_object_id 合同唯一标识ID
from fsp_cms.cms_contract cc,
fsp_cms.fam_fu ff,
fsp_cms.cms_contract_project ccp,
fsp_cms.contract_install_points cip,
(select nvl(p.instal_points_id,ppp.id) instal_points_id,pp.customer_id payment_object_id
from fsp_cms.cms_contract_payment p,fsp_cms.cms_product_contract_payment pp,fsp_cms.contract_install_points ppp
where pp.relation_id = p.id(+)
and pp.relation_type = '01'
and ppp.project_id = pp.project_id
and pp.enabled_flag = 'Y'
group by p.instal_points_id, pp.customer_id,ppp.id) pay,
fsp_cms.cms_contract_change ccc,
fsp_cms.fam_vendors fv,
fsp_cms.cms_city city,
fsp_fam.fa_org fo
where cc.enabled_flag = 'Y'
--and cc.validity_flag = 'Y'
and cc.contract_pro = 'XZZLHT'
and cc.is_part_seal = 'Y'
and cc.fu_id = ff.id
and cc.id = ccp.contract_id
and ccp.enabled_flag = 'Y'
and ccp.id = cip.project_id
and cip.enabled_flag = 'Y'
and cc.id = ccc.new_contract_id(+)
--and cc.customer_id = fv.id
and cc.city_code = city.city_code(+)
and cc.sales_dept = fo.id(+)
and cip.terminal_type != '0203'
and cip.id = pay.instal_points_id
and fv.id(+) = pay.payment_object_id
and exists
(select 1
from fsp_cms.cms_contract_payment p,fsp_cms.cms_product_contract_payment pp,fsp_cms.contract_install_points ppp
where pp.relation_id = p.id(+)
and pp.relation_type = '01'
and ppp.project_id = pp.project_id
and pp.enabled_flag = 'Y'
group by p.instal_points_id, pp.customer_id,ppp.id)
;
