协议合同信息SQL脚本

--子协议合同信息
--CREATE VIEW NC_SUB_CON_V AS
SELECT FF.CODE 业务单元编号,
FF.NAME 业务单元名称,
'' 成本中心编号,
'' 成本中心名称,
FO.CODE 部门编码,
FO.NAME 部门名称,
'' 经办人ID,
'' 经办人名称,
CC.CODE || '_SUB_' || CS.CODE || '_' || CSPC.TERMINAL_TYPE 合同编号,
'否' 是否自动生成合同编号,
DECODE(CSPC.TERMINAL_TYPE,
'01',
'梯内屏',
'02',
'梯外屏',
'03',
'框架') 租赁物名称,
DECODE(CSPC.TERMINAL_TYPE,
'01',
'010101',
'02',
'010102',
'03',
'010103',
CSPC.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 主合同编号,
CP.CODE 项目编码,
CCP.PROJECT_NAME 项目名称,
CCP.PROJECT_PROPERTY 项目性质,
1 签约数,
CC.CITY_CODE 城市编码,
CITY.CITY_NAME 城市名字,
'IPS_' || CS.CODE || '_' || CSPC.TERMINAL_TYPE 合同唯一标识ID,
CS.CODE 子协议编码,
CIP.INSTALL_SIZE 安装尺寸
FROM CMS_CONTRACT CC,
FAM_FU FF,
CMS_CONTRACT_PROJECT CCP,
CMS_SUBCONTRACT CS,
(SELECT CSP.SUB_CONTRACT_ID, CSP.TERMINAL_TYPE
FROM CMS_SUBCONTRACT_PAYMENT CSP
WHERE CSP.ENABLED_FLAG = 'Y'
GROUP BY CSP.SUB_CONTRACT_ID, CSP.TERMINAL_TYPE) CSPC,
CMS_CONTRACT_CHANGE CCC,
FAM_VENDORS FV,
CMS_PROJECT CP,
CMS_CITY CITY,
FSP_FAM.FA_ORG FO,
CONTRACT_INSTALL_POINTS CIP
WHERE CC.ENABLED_FLAG = 'Y'
--AND CC.VALIDITY_FLAG = 'Y'
AND CC.CONTRACT_PRO = 'CDHTSQ'
AND CC.IS_PART_SEAL = 'Y'
AND NVL(CC.CONTRACT_MODE, 'CONTRACT_MODE_RENT') = 'CONTRACT_MODE_RENT'
AND CC.FU_ID = FF.ID
AND CC.ID = CCP.CONTRACT_ID
AND CCP.ENABLED_FLAG = 'Y'
AND CS.ENABLED_FLAG = 'Y'
AND CS.ID = CSPC.SUB_CONTRACT_ID
AND CS.PROJECT_ID = CCP.ID
AND CC.ID = CCC.NEW_CONTRACT_ID(+)
AND CS.CUSTOMER_ID = FV.ID
AND CCP.CRM_PROJECT_ID = CP.ID
AND CC.CITY_CODE = CITY.CITY_CODE(+)
AND CCP.ID = CIP.PROJECT_ID
AND CC.SALES_DEPT = FO.ID(+)
-- AND FO.CODE = 'D00826'
-- AND CC.CODE IN ('SH-F-20190241','SH-A-20190293')
--AND TO_CHAR(CCP.END_TIME,'YYYY-MM-DD') >'2021-01-01'
AND EXISTS (SELECT 1
FROM CMS_SUBCONTRACT_PAYMENT PAY,
CMS_PRODUCT_CONTRACT_PAYMENT CPCP
WHERE CS.ID = PAY.SUB_CONTRACT_ID
AND PAY.ENABLED_FLAG = 'Y'
AND CPCP.RELATION_ID = PAY.ID
AND CPCP.RELATION_TYPE = '03'
AND CPCP.ENABLED_FLAG = 'Y')
;

--子协议合同付款
--CREATE VIEW NC_SUB_CON_PAY_V AS
SELECT CC.CODE || '_SUB_' || CS.CODE || '_' || CPCP.TERMINAL_TYPE 合同编号,
CPCP.START_DATE 起始日期,
CPCP.END_DATE 终止日期,
CSP.PAYMENT_AMT 付款金额,
'0' 增值税率,
'0' 预估可抵扣税额,
CSP.PAYMENT_AMT 预估入账金额,
NVL(CPCP.PAY_DUE_DATE, CPCP.START_DATE - 7) 计划付款日期,
'租金' 金额类型,
'否' 是否预提,
'TZ_' || CPCP.ID 唯一标识ID
FROM CMS_CONTRACT CC,
CMS_CONTRACT_PROJECT CCP,
CMS_SUBCONTRACT_PAYMENT CSP,
CMS_SUBCONTRACT CS,
CMS_PRODUCT_CONTRACT_PAYMENT CPCP
WHERE CC.ENABLED_FLAG = 'Y'
--AND CC.VALIDITY_FLAG = 'Y'
AND CC.CONTRACT_PRO = 'CDHTSQ'
AND CC.IS_PART_SEAL = 'Y'
AND NVL(CC.CONTRACT_MODE, 'CONTRACT_MODE_RENT') = 'CONTRACT_MODE_RENT'
AND CC.ID = CCP.CONTRACT_ID
AND CCP.ENABLED_FLAG = 'Y'
AND CPCP.RELATION_ID = CSP.ID
AND CPCP.RELATION_TYPE = '03'
AND CPCP.ENABLED_FLAG = 'Y'
AND CS.ENABLED_FLAG = 'Y'
AND CSP.ENABLED_FLAG = 'Y'
AND CS.ID = CSP.SUB_CONTRACT_ID
-- AND CC.CODE IN ('SH-F-20190241','SH-A-20190293')
AND CS.PROJECT_ID = CCP.ID
;