DROP VIEW DMS.V_AQ_INSURANCECLAIMS;
CREATE VIEW DMS.V_AQ_INSURANCECLAIMS AS
SELECT * FROM (SELECT
T1.FRAMENO AS 车架号,
T1.LICENSENO AS 车牌号,
T10.CARMODELNAME AS 车型,
T7.CUSTOMERNAME AS 客户名称,
T7.MOBILEPHONE AS 联系电话,
T1.REPAIRNO AS 作业单号,
--维修类型
VARCHAR(T11.DELIVEREDDATE) AS 交车日期,
T3.COMMONNAME AS 保险公司,
VARCHAR(T1.FIXEDDATE) AS 定损日期,
T6.FULLNAME AS 理赔顾问,
T9.COMMONNAME AS 事故类型,
CASE WHEN T1.ISPROXY = 'Y' THEN '是' WHEN T1.ISPROXY = 'N' THEN '否' END AS 是否代交案,
T1.WORKFEE AS 工时定损金额,
T1.PARTFEE AS 配件定损金额,
T1.OTHERFEE AS 其他定损金额,
T1.TOTALFEE AS 定损金额合计,
VARCHAR(T1.SUBMITDATE) AS 交案时间,
T1.NOTE AS 备注
FROM RT_INSURANCECLAIMS T1
LEFT JOIN CM_COMMONCODE T2 ON T1.STATUS = T2.COMMONCODE AND T2.CODETYPE = 'CR29'
LEFT JOIN CM_COMMONCODE T3 ON T1.COMPANYCODE = T3.COMMONCODE AND T3.CODETYPE = 'CC12'
LEFT JOIN CM_STAFF T6 ON T1.CLAIMSID = T6.STAFFPKID
LEFT JOIN UM_CUSTOMER T7 ON T1.CUSTOMERNO = T7.CUSTOMERCODE
LEFT JOIN VM_CARINFO T8 ON T1.FRAMENO = T8.FRAMENO
LEFT JOIN VM_CARMODEL T10 ON T8.CARMODELPKID = T10.CARMODELPKID
LEFT JOIN CM_COMMONCODE T9 ON T1.ACCIDENTTYPE = T9.COMMONCODE AND T9.CODETYPE = 'CR42'
LEFT JOIN RT_REPAIRPROCESS T11 ON T1.REPAIRNO = T11.REPAIRNO
) T
