SIMACMOBILE.VM_GAR_REPORTE_CLARIOS

Base de datos origen: ORCLSMM
Tipo de objeto: MATERIALIZED VIEW
SQL

  CREATE MATERIALIZED VIEW "SIMACMOBILE"."VM_GAR_REPORTE_CLARIOS" ("ID", "ANIO", "CO06", "COD_SUCURSAL", "NOMBRE_SUCURSAL", "CIUDAD", "VACIA_4", "TIPO", "FECHA_DILIGENCIA", "FECHA_RECLAMACION_GARANTIA", "FECHA_VENTA", "GARANTIA_DE_GARANTIA", "FECHA_INICIAL_GARANTIA", "VACIA_9", "VACIA_8", "VACIA_7", "VACIA_6", "CAJA", "REFERENCIA_SIMAC", "MARCA_BATERIA", "NUMERO_GARANTIA", "CLAVE_FABRICACION", "ACELACION", "PLACAS", "DIA_TRAZ", "MES_TRAZ", "ANO_TRAZ", "LINEA", "TURNO", "COD_FALLA", "DESC_FALLA", "VACIA_22", "TIPO_SERVICIO", "COD_BATERIOLOGO", "BATERIOLOGO", "TIPO_VEHICULO", "MARCA_VEHICULO", "MODELO", "OPERADOR", "CIUDAD_CLIENTE", "TELEFONO", "CC_CLIENTE", "VACIO_20", "OBS_COE", "ABREVIATURA_MARCA", "VACIO_30", "FECHA_CREACION")
  SEGMENT CREATION IMMEDIATE
  ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING  NO INMEMORY 
  BUILD DEFERRED
  USING INDEX 
  REFRESH FORCE ON DEMAND
  USING DEFAULT LOCAL ROLLBACK SEGMENT
  USING ENFORCED CONSTRAINTS DISABLE ON QUERY COMPUTATION DISABLE QUERY REWRITE
  AS SELECT ROW_NUMBER() OVER(ORDER BY NULL) AS ID, QUERY.*
FROM (SELECT DISTINCT  
	TO_CHAR(de.FECHA_CREACION,'YYYY') AS ANIO, 
	TO_CHAR(dde.CONSECUTIVO) AS CO06, 
	se.CODIGO AS COD_SUCURSAL, 
	se.NOMBRE AS NOMBRE_SUCURSAL, 
	cie.CIUDAD AS CIUDAD, 
	TO_CHAR(0) AS VACIA_4, 
	gose.SISTEMA AS TIPO, 
	TO_CHAR(dde.FECHA_CREACION,'YYYYMMDD') AS FECHA_DILIGENCIA, 
	TO_CHAR(sge.FECHA_CREACION,'YYYYMMDD') AS FECHA_RECLAMACION_GARANTIA, 
	TO_CHAR(sdge.FECHA_VENTA,'YYYYMMDD') AS FECHA_VENTA, 
	(CASE WHEN ddfe.RECLAMO_GARANTIA_SE = 1 THEN 'SI' ELSE 'NO' END) AS GARANTIA_DE_GARANTIA, 
	TO_CHAR(sdge.FECHA_VENTA,'YYYYMMDD') AS FECHA_INICIAL_GARANTIA, 
	TO_CHAR(0) AS VACIA_9, 
	TO_CHAR(0) AS VACIA_8, 
	TO_CHAR(0) AS VACIA_7, 
	TO_CHAR(0) AS VACIA_6, 
	NVL(SUBSTR(re.CAJA_BATERIA, INSTR(re.CAJA_BATERIA, '_', -1) + 1),'0') AS CAJA, 
	NVL(re.ITEM_CODIGO_SIMAC,'0') AS REFERENCIA_SIMAC, 
	TO_CHAR(1) AS MARCA_BATERIA, 
	sdge.NUMERO_GARANTIA AS NUMERO_GARANTIA, 
	SUBSTR(sdge.NUMERO_GARANTIA, 2,2) AS CLAVE_FABRICACION, 
	SUBSTR(dde.LOTE, 1,1) AS ACELACION, 
	dde.PLACAS AS PLACAS, 
	SUBSTR(dde.LOTE, 4,2) AS DIA_TRAZ, 
	(CASE   
		WHEN SUBSTR(dde.LOTE, 6,1) = 'E' THEN '01'  
		WHEN SUBSTR(dde.LOTE, 6,1) = 'F' THEN '02' 
		WHEN SUBSTR(dde.LOTE, 6,1) = 'M' THEN '03'  
		WHEN SUBSTR(dde.LOTE, 6,1) = 'A' THEN '04'  
		WHEN SUBSTR(dde.LOTE, 6,1) = 'Y' THEN '05'  
		WHEN SUBSTR(dde.LOTE, 6,1) = 'J' THEN '06'  
		WHEN SUBSTR(dde.LOTE, 6,1) = 'U' THEN '07'  
		WHEN SUBSTR(dde.LOTE, 6,1) = 'G' THEN '08'  
		WHEN SUBSTR(dde.LOTE, 6,1) = 'S' THEN '09'  
		WHEN SUBSTR(dde.LOTE, 6,1) = 'O' THEN '10'  
		WHEN SUBSTR(dde.LOTE, 6,1) = 'N' THEN '11'  
		WHEN SUBSTR(dde.LOTE, 6,1) = 'D' THEN '12'  
		ELSE NULL 
	END) AS MES_TRAZ, 
	SUBSTR(dde.LOTE, 7,2) AS ANO_TRAZ, 
	SUBSTR(dde.LOTE, 9,1) AS LINEA, 
	SUBSTR(dde.LOTE, 10,1) AS TURNO, 
	SUBSTR(cbe.CODIGO , INSTR(cbe.CODIGO ,'_', -1) + 1)  AS COD_FALLA, 
cbe.DESCRIPCION AS DESC_FALLA, 
TO_CHAR(dde.FECHA_CREACION,'YYYYMM') AS VACIA_22, 
(CASE   
	WHEN tve.CODIGO = 'PARTICULAR' THEN TO_CHAR(1)  
	WHEN tve.CODIGO = 'PUBLICO_NO_TAXI' THEN TO_CHAR(2)  
	WHEN tve.CODIGO = 'TAXI' THEN TO_CHAR(3)  
	ELSE TO_CHAR(0) 
END) AS TIPO_SERVICIO, 
be.CODIGO AS COD_BATERIOLOGO, 
be.NOMBRE AS BATERIOLOGO, 
NVL(tvde.CODIGO,'0') AS TIPO_VEHICULO, 
NVL(mve.NOMBRE,'0') AS MARCA_VEHICULO, 
NVL(dde.MODELO,'0') AS MODELO, 
ce.PARTY_SITE_NAME AS OPERADOR, 
cie.CIUDAD AS CIUDAD_CLIENTE, 
NVL(ce.NUMERO_TELEFONO,'0') AS TELEFONO, 
ce.NIT AS CC_CLIENTE, 
de.OPER_CREADOR AS VACIO_20, 
NVL(dde.OBSERVACIONES,'0') AS OBS_COE, 
tge.ABREVIATURA AS ABREVIATURA_MARCA, TO_CHAR(0) AS VACIO_30,    
de.FECHA_CREACION
FROM SIMACMOBILE.DIAGNOSTICO_ERP de 
	INNER JOIN SIMACMOBILE.SOLICITUD_GARANTIA_ERP sge ON sge.ID_SOLGAERP = de.ID_SOLGAERP 
	INNER JOIN SIMACMOBILE.VENDEDOR_ERP ve ON ve.ID_VERP = sge.ID_VERP 
	LEFT JOIN SIMACMOBILE.CLIENTE_VENDEDOR_ERP cve ON cve.ID_VERP = VE.ID_VERP  
	INNER JOIN SIMACMOBILE.GARANTIA_ORIGEN_SISTEMA_ERP gose ON gose.ID_GAORSIERP = sge.ID_GAORSIERP 
	INNER JOIN SIMACMOBILE.CLIENTE_ERP ce ON ce.ID_CLERP = sge.ID_CLERP 
	INNER JOIN SIMACMOBILE.CIUDAD_ERP cie ON cie.ID_CIUERP = ce.ID_CIUERP 
	INNER JOIN SIMACMOBILE.SUCURSAL_ERP se ON se.ID_SUERP = sge.ID_SUERP 
	INNER JOIN SIMACMOBILE.SOLICITUD_DETALLE_GARANTIA_ERP sdge ON sdge.ID_SOLGAERP = sge.ID_SOLGAERP 
	LEFT JOIN SIMACMOBILE.REFERENCIA_ERP re ON re.ID_REERP = sdge.ID_REERP 
	INNER JOIN SIMACMOBILE.DIAGNOSTICO_DETALLE_ERP dde ON dde.ID_SOLDEGAERP = sdge.ID_SOLDEGAERP 
	LEFT JOIN SIMACMOBILE.BATERIOLOGO_ERP be ON be.ID_BATERERP = dde.ID_BATEERP 
	LEFT JOIN SIMACMOBILE.REFERENCIA_ERP refeing ON refeing.ID_REERP = dde.ID_REFEINGERP 
	LEFT JOIN SIMACMOBILE.TIEMPO_GARANTIA_ERP tge ON tge.ID_REERP = refeing.ID_REERP 
	LEFT JOIN SIMACMOBILE.CAUSAL_BATERIOLOGO_ERP cbe ON cbe.ID_CAUBATEERP = dde.ID_CAUBATEERP 
	INNER JOIN SIMACMOBILE.DIAGNOSTICO_DETALLE_FISICO_ERP ddfe ON ddfe.ID_DEDIERP = dde.ID_DEDIERP 
	INNER JOIN SIMACMOBILE.TIPO_VEHICULO_ERP tve ON tve.ID_TIVEERP = dde.ID_TIVEERP 
	LEFT JOIN SIMACMOBILE.TIPO_VEHICULO_DIAGNOSTICO_ERP tvde ON tvde.ID_TIVEDIERP = dde.ID_TIVEDIERP 
	LEFT JOIN SIMACMOBILE.MARCA_VEHICULO_ERP mve ON mve.ID_MARVEERP = dde.ID_MARVEERP
	WHERE sge.ID_TIGAERP = 1
		AND dde.ID_TIDIERP = 1
		AND sdge.ID_ESGAERP = 22
ORDER BY de.FECHA_CREACION) QUERY;
← Volver al esquema SIMACMOBILE
🏠 Volver al menú principal