tianzhizy

--子協(xié)議合同信息
--CREATE VIEW NC_SUB_CON_V AS
SELECT FF.CODE 業(yè)務(wù)單元編號(hào),
FF.NAME 業(yè)務(wù)單元名稱,
'' 成本中心編號(hào),
'' 成本中心名稱,
FO.CODE 部門編碼,
FO.NAME 部門名稱,
'' 經(jīng)辦人ID,
'' 經(jīng)辦人名稱,
CC.CODE || '_SUB_' || CS.CODE || '_' || CSPC.TERMINAL_TYPE 合同編號(hào),
'否' 是否自動(dòng)生成合同編號(hào),
DECODE(CSPC.TERMINAL_TYPE,
'01',
'梯內(nèi)屏',
'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 業(yè)務(wù)變更日期,
'否' 是否豁免,
DECODE(CCP.PAYMENT_TYPE,
'CMS_PRODUCT_QUARTERLY_PAYMENT',
'季度付',
'CMS_PRODUCT_YEAR_PAYMENT',
'季度付',
'CMS_PRODUCT_SEMI_ANNUAL',
'季度付',
'CMS_PRODUCT_MONTH_PAYMENT',
'季度付') 租金支付頻率,
0 付款提前天數(shù),
0 租入面積,
FV.VENDOR_CODE 出租人編號(hào),
FV.VENDOR_NAME 出租人名稱,
'2021-01-01' 使權(quán)資起始日期,
'2021-01-01' 租負(fù)債起始日期,
'' 首期付款額,
'0%' 稅率,
'否' 是否含稅,
'否' 增值稅是否可以抵扣,
'' 折現(xiàn)率,
'0' 初始直接費(fèi)用,
'0' 預(yù)計(jì)復(fù)原成本,
'0' 租賃激勵(lì)措施金,
'' 行使購買選擇權(quán)日期,
'' 行購買金額,
'0' 押金,
'0' 定金,
'否' 是否簡(jiǎn)易征收,
'' 簡(jiǎn)易稅率,
'是' 是否已識(shí)別資產(chǎn),
'是' 承所有經(jīng)濟(jì)利益,
'是' 承租人主導(dǎo)資產(chǎn)使用權(quán),
'002：>40000 RMB' 租賃資產(chǎn)價(jià)值,
'0' 預(yù)付租金,
'' 資產(chǎn)剩余使用期限,
'0' 初未攤銷完待攤費(fèi)用,
'' 備注,
CC.CODE 主合同編號(hào),
CP.CODE 項(xiàng)目編碼,
CCP.PROJECT_NAME 項(xiàng)目名稱,
CCP.PROJECT_PROPERTY 項(xiàng)目性質(zhì),
1 簽約數(shù),
CC.CITY_CODE 城市編碼,
CITY.CITY_NAME 城市名字,
'IPS_' || CS.CODE || '_' || CSPC.TERMINAL_TYPE 合同唯一標(biāo)識(shí)ID,
CS.CODE 子協(xié)議編碼,
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')
;

--子協(xié)議合同付款
--CREATE VIEW NC_SUB_CON_PAY_V AS
SELECT CC.CODE || '_SUB_' || CS.CODE || '_' || CPCP.TERMINAL_TYPE 合同編號(hào),
CPCP.START_DATE 起始日期,
CPCP.END_DATE 終止日期,
CSP.PAYMENT_AMT 付款金額,
'0' 增值稅率,
'0' 預(yù)估可抵扣稅額,
CSP.PAYMENT_AMT 預(yù)估入賬金額,
NVL(CPCP.PAY_DUE_DATE, CPCP.START_DATE - 7) 計(jì)劃付款日期,
'租金' 金額類型,
'否' 是否預(yù)提,
'TZ_' || CPCP.ID 唯一標(biāo)識(shí)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
;