租用租入合同信息SQL脚本

--租用租入合同信息
--CREATE VIEW NC_RENT_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 主合同编号,
CP.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，
CIP.INSTALL_SIZE 安装尺寸
FROM CMS_CONTRACT CC,
FAM_FU FF,
CMS_CONTRACT_PROJECT CCP,
CONTRACT_INSTALL_POINTS CIP,
(SELECT P.INSTAL_POINTS_ID, PP.CUSTOMER_ID PAYMENT_OBJECT_ID
FROM CMS_CONTRACT_PAYMENT P,CMS_PRODUCT_CONTRACT_PAYMENT PP
WHERE PP.RELATION_ID = P.ID
AND PP.RELATION_TYPE = '01'
AND PP.ENABLED_FLAG = 'Y'
GROUP BY P.INSTAL_POINTS_ID, PP.CUSTOMER_ID) PAY,
CMS_CONTRACT_CHANGE CCC,
FAM_VENDORS FV,
CMS_PROJECT CP,
CMS_CITY CITY,
FSP_FAM.FA_ORG FO
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 CCP.ID = CIP.PROJECT_ID
AND CIP.ENABLED_FLAG = 'Y'
AND CC.ID = CCC.NEW_CONTRACT_ID(+)
--AND CC.CUSTOMER_ID = FV.ID
AND CCP.CRM_PROJECT_ID = CP.ID
AND CC.CITY_CODE = CITY.CITY_CODE(+)
AND CC.SALES_DEPT = FO.ID(+)
AND CIP.ID = PAY.INSTAL_POINTS_ID
AND FV.ID(+) = PAY.PAYMENT_OBJECT_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_CONTRACT_PAYMENT PAY, CMS_PRODUCT_CONTRACT_PAYMENT CPCP
WHERE CIP.ID = PAY.INSTAL_POINTS_ID
AND PAY.ENABLED_FLAG = 'Y'
AND CPCP.RELATION_ID = PAY.ID
AND CPCP.RELATION_TYPE = '01'
AND CPCP.ENABLED_FLAG = 'Y')
;
--租用合同付款
--CREATE VIEW NC_RENT_CON_PAY_V AS
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 CMS_CONTRACT CC,
CMS_CONTRACT_PROJECT CCP,
CONTRACT_INSTALL_POINTS CIP,
CMS_CONTRACT_PAYMENT PAY,
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 CCP.ID = CIP.PROJECT_ID
AND CIP.ENABLED_FLAG = 'Y'
AND CIP.ID = PAY.INSTAL_POINTS_ID
AND PAY.ENABLED_FLAG = 'Y'
AND CPCP.ENABLED_FLAG = 'Y'
AND CPCP.RELATION_ID = PAY.ID
AND CPCP.RELATION_TYPE = '01'
--AND CC.CODE IN ('SH-F-20190241','SH-A-20190293')
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 CMS_CONTRACT CC,
CMS_CONTRACT_PROJECT CCP,
CONTRACT_INSTALL_POINTS CIP,
CMS_PRODUCT_CONTRACT_PAYMENT CPCP,
CMS_CONTRACT_DEDUCTION CCD,
CMS_PRODUCT_CONTRACT_PAYMENT CPCP1,
CMS_CONTRACT_PAYMENT CP
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 CCP.ID = CIP.PROJECT_ID
AND CPCP.RELATION_TYPE = '05'
AND CPCP.RELATION_ID = CCD.ID
AND CPCP1.ID = CCD.RENTS_PAYMENT_ID
AND CPCP1.RELATION_ID = CP.ID
AND CPCP.ENABLED_FLAG = 'Y'
AND CPCP.AMOUNT != 0
AND CP.INSTAL_POINTS_ID = CIP.ID
;