🏠 Menú principal

← Volver a vistas materializadas

---

SIMACMOBILE.VM_GAR_REPORTE_DESACTIVACION_TG

Base de datos origen: ORCLSMM

Tipo de objeto: MATERIALIZED VIEW

SQL

  CREATE MATERIALIZED VIEW "SIMACMOBILE"."VM_GAR_REPORTE_DESACTIVACION_TG" ("ID", "FECHA_CREACION_SOLICITUD", "NRO_SOLICITUD", "ESTADO_SOLICITUD", "NRO_SOLICITUD_OM", "COD_REFERENCIA", "DESCRIPCION_REFERENCIA", "VENDEDOR_USUARIO", "TG_CLAVE_DOT", "USUARIO_DESACTIVA_TG", "FECHA_DESACTIVACION_TG", "ESTADO_TG", "TIPO_SUCURSAL", "CAUSAL", "SUCURSAL", "LINEA_PRODUCTO", "NIT", "RAZON_SOCIAL", "CIUDAD", "DEPARTAMENTO", "NRO_CUENTA", "FECHA_CREACION", "ID_TIGAERP", "ID_GAORSIERP", "ID_SUERP", "ID_VERP")
  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",
	ege.DESCRIPCION AS "ESTADO_SOLICITUD",
	sge.NRO_SOLICITUD_OM AS "NRO_SOLICITUD_OM",
	re.CODIGO AS "COD_REFERENCIA",
	re.DESCRIPCION AS "DESCRIPCION_REFERENCIA",
	((CASE WHEN UPPER(ve.PRIMER_NOMBRE) = 'NULL' THEN '' ELSE ve.PRIMER_NOMBRE END) ||' '|| (CASE WHEN UPPER(ve.SEGUNDO_NOMBRE) = 'NULL' THEN '' ELSE ve.SEGUNDO_NOMBRE END))  || ' ' || ve.APELLIDOS AS "VENDEDOR_USUARIO",
	sdge.NUMERO_GARANTIA AS "TG_CLAVE_DOT",
	ttge.OPER_CREADOR AS "USUARIO_DESACTIVA_TG",
	TO_CHAR(ttge.FECHA_CREACION , 'DD/MM/YYYY HH24:MI:SS') AS "FECHA_DESACTIVACION_TG",
	(CASE 
		WHEN ttge.ESTADO_ACTUAL = 'R' THEN 'Inactivo'
		WHEN ttge.ESTADO_ACTUAL = 'A' THEN 'Activo'
		ELSE TO_CHAR(0)
	END) AS "ESTADO_TG",
	gose.SISTEMA AS "TIPO_SUCURSAL",
	ctg.DESCRIPCION_CAUSAL AS "CAUSAL",
	(vse.CODIGO || '-' || vse.NOMBRE) AS "SUCURSAL",
	tgae.DESCRIPCION AS "LINEA_PRODUCTO",
	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",
	sge.ID_VERP AS "ID_VERP"
FROM SIMACMOBILE.SOLICITUD_GARANTIA_ERP sge
INNER JOIN SIMACMOBILE.VENDEDOR_ERP ve ON ve.ID_VERP = sge.ID_VERP 
INNER JOIN SIMACMOBILE.ESTADO_GARANTIA_ERP ege ON ege.ID_ESGAERP = sge.ID_ESGAERP
INNER JOIN SIMACMOBILE.SOLICITUD_DETALLE_GARANTIA_ERP sdge ON sdge.ID_SOLGAERP = sge.ID_SOLGAERP
	INNER JOIN SIMACMOBILE.REFERENCIA_ERP re ON re.ID_REERP = sdge.ID_REERP
	INNER JOIN SIMACMOBILE.TRAZABILIDAD_TARJETA_GARANTIA_ERP ttge ON ttge.ID_SOLDEGAERP = sdge.ID_SOLDEGAERP
    INNER JOIN SIMACMOBILE.CAUSALES_TG_GARANTIAS ctg ON ctg.ID_CATGGA = ttge.ID_CATGGA
	INNER JOIN SIMACMOBILE.GARANTIA_ORIGEN_SISTEMA_ERP gose ON gose.ID_GAORSIERP = sge.ID_GAORSIERP
	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.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
WHERE --sge.NRO_SOLICITUD_OM <> 0 AND
sge.ID_GAORSIERP IN (1,2) 
ORDER BY ttge.FECHA_CREACION DESC 
) QUERY;

---

← Volver al esquema SIMACMOBILE

🏠 Volver al menú principal