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