tianzhizy

--租用租入合同信息
--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
;