🏠 Menú principal

← Volver a vistas materializadas

---

SIMACMOBILE.VM_GAR_REPORTE_ERROR_INTEGRACION

Base de datos origen: ORCLSMM

Tipo de objeto: MATERIALIZED VIEW

SQL

  CREATE MATERIALIZED VIEW "SIMACMOBILE"."VM_GAR_REPORTE_ERROR_INTEGRACION" ("ID", "FECHA_CREACION_SOLICITUD", "NRO_SOLICITUD", "NRO_ORDEN_RECOLECCION", "CANTIDAD_REINTENTOS", "FECHA_ULTIMO_REINTENTO", "TIPO_SUCURSAL", "SUCURSAL", "LINEA_PRODUCTO", "ESTADO_ACTUAL", "NIT", "RAZON_SOCIAL", "CIUDAD", "DEPARTAMENTO", "NRO_CUENTA", "FECHA_CREACION", "ID_TIGAERP", "ID_GAORSIERP", "ID_SUERP")
  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
	TO_CHAR(sge.FECHA_CREACION, 'DD/MM/YYYY HH24:MI:SS') AS "FECHA_CREACION_SOLICITUD",
	sge.ID_SOLGAERP AS "NRO_SOLICITUD",
	 CASE 
        WHEN sge.ID_GAORSIERP = 1 THEN TO_CHAR(sge.NRO_SOLICITUD_OM)
        ELSE ''
    END AS "NRO_ORDEN_RECOLECCION",
    tge.CONTEO_INTENTOS AS "CANTIDAD_REINTENTOS",
	TO_CHAR(tge.FECHA_CREACION, 'DD/MM/YYYY HH24:MI:SS') AS "FECHA_ULTIMO_REINTENTO",
	CASE 
        WHEN sge.ID_GAORSIERP = 1 THEN 'CEDI'
        ELSE 'Energiteca'
    END AS "TIPO_SUCURSAL",
    (vse.CODIGO || '-' || vse.NOMBRE) AS "SUCURSAL",
    tgae.DESCRIPCION AS "LINEA_PRODUCTO",
    ege.DESCRIPCION AS "ESTADO_ACTUAL",
    ce.NIT AS "NIT",
	ce.PARTY_SITE_NAME AS "RAZON_SOCIAL",
	ciu.CIUDAD AS "CIUDAD",
	de.DEPARTAMENTO AS "DEPARTAMENTO",
	sge.NRO_CUENTA AS "NRO_CUENTA",
	sge.FECHA_CREACION AS "FECHA_CREACION",
	sge.ID_TIGAERP AS "ID_TIGAERP",
	sge.ID_GAORSIERP AS "ID_GAORSIERP",
	sge.ID_SUERP AS "ID_SUERP"
FROM SIMACMOBILE.SOLICITUD_GARANTIA_ERP sge
INNER JOIN SIMACMOBILE.TRAZABILIDAD_GARANTIA_ERP tge ON sge.ID_SOLGAERP = tge.ID_SOLGAERP
INNER JOIN SIMACMOBILE.VISTA_SUCURSALES_ERP vse ON vse.ID_SUERP = sge.ID_SUERP
INNER JOIN SIMACMOBILE.TIPO_GARANTIA_ERP tgae ON tgae.ID_TIGAERP = sge.ID_TIGAERP
INNER JOIN SIMACMOBILE.ESTADO_GARANTIA_ERP ege ON ege.ID_ESGAERP = sge.ID_ESGAERP
INNER JOIN SIMACMOBILE.CLIENTE_ERP ce ON sge.ID_CLERP = ce.ID_CLERP
	INNER JOIN SIMACMOBILE.CIUDAD_ERP ciu ON ciu.ID_CIUERP = ce.ID_CIUERP
	INNER JOIN SIMACMOBILE.DEPARTAMENTO_ERP de ON de.ID_DEPERP = ce.ID_DEPERP
ORDER BY sge.FECHA_CREACION, sge.ID_SOLGAERP ASC
) QUERY;

---

← Volver al esquema SIMACMOBILE

🏠 Volver al menú principal