Diccionario de Datos
Diccionario de datos de los modelos de COÉXITO
| 🏠 Menú principal | ← Volver a vistas materializadas |
SIMACMOBILE.VM_GAR_REPORTE_DOCUMENTO_SOPORTE_DOCUMENTO
Base de datos origen: ORCLSMM
Tipo de objeto: MATERIALIZED VIEW
SQL
CREATE MATERIALIZED VIEW "SIMACMOBILE"."VM_GAR_REPORTE_DOCUMENTO_SOPORTE_DOCUMENTO" ("ID", "FECHA_CREACION_SOLICITUD", "FECHA_DIAGNOSTICO", "NRO_SOLICITUD", "NRO_ORDEN_RECOLECCION", "TIPO_SUCURSAL", "SUCURSAL", "LINEA_PRODUCTO", "RESULTADO", "NRO_FORMULARIO", "DOCUMENTO_SOPORTE", "REFERENCIA_ENTRA", "DESCRIPCION_REF_ENTRA", "REFERENCIA_SALE", "DESCRIPCION_REF_SALE", "CODIGO_FALLA", "DESCRIPCION_CODIGO_FALLA", "CODIGO_BATERIOLOGO", "NOMBRE_BATERIOLOGO", "CANTIDAD_DOCUMENTOS_ADJUNTOS", "NOMBRE_DOCUMENTO_SOPORTE_ADJUNTO", "OBSERVACIONES_DIAGNOSTICO", "FECHA_CREACION", "ID_TIGAERP", "ID_GAORSIERP", "ID_SUERP", "ID_REERP", "NIT", "RAZON_SOCIAL", "VENDEDOR_MAYORISTA", "FECHA_DIAGNOSTICO_FILTER", "VENTA_CANAL_COMERCIAL")
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 WITH CANTIDAD_DCTOS AS (
SELECT
COUNT(GSSE.ID_GARSOPSOLERP) AS COUNT_SOLICITUDES,
SDGE.ID_SOLDEGAERP
FROM
SIMACMOBILE.SOLICITUD_DETALLE_GARANTIA_ERP SDGE
LEFT JOIN SIMACMOBILE.GARANTIA_SOPORTE_SOLICITUD_ERP GSSE
ON SDGE.ID_SOLDEGAERP = GSSE.ID_SOLDEGAERP
AND GSSE.ESTADO_REGISTRO = 'A' AND GSSE.URL IS NOT NULL
GROUP BY
SDGE.ID_SOLDEGAERP
), LISTADO_DOCUMENTOS AS (
SELECT
LISTAGG(DISTINCT GTSE.NOMBRE_DOCUMENTO, ', ') WITHIN GROUP (ORDER BY GTSE.NOMBRE_DOCUMENTO) AS DOCUMENTOS,
SDGE.ID_SOLDEGAERP
FROM
SIMACMOBILE.SOLICITUD_DETALLE_GARANTIA_ERP SDGE
LEFT JOIN SIMACMOBILE.GARANTIA_SOPORTE_SOLICITUD_ERP GSSEGT
ON GSSEGT.ID_SOLDEGAERP = SDGE.ID_SOLDEGAERP
LEFT JOIN SIMACMOBILE.GARANTIA_TIPO_SOPORTE_ERP GTSE
ON GTSE.ID_GATISOERP = GSSEGT.ID_GATISOERP
AND GTSE.ESTADO_REGISTRO = 'A' AND GSSEGT.URL IS NOT NULL
GROUP BY
SDGE.ID_SOLDEGAERP
)
SELECT
ROW_NUMBER() OVER(ORDER BY NULL) AS ID,
query.*
FROM
(
SELECT
TO_CHAR(SGE.FECHA_CREACION, 'DD/MM/YYYY HH:MM:SS') AS "FECHA_CREACION_SOLICITUD",
TO_CHAR(DDE.FECHA_CREACION, 'DD/MM/YYYY HH:MM:SS') AS "FECHA_DIAGNOSTICO",
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",
(
CASE
WHEN SGE.ID_GAORSIERP = 1 THEN
'CEDI'
ELSE
'Energiteca'
END) AS "TIPO_SUCURSAL",
(VSE.CODIGO
|| '-'
|| VSE.NOMBRE) AS "SUCURSAL",
'Batería Auto' AS "LINEA_PRODUCTO",
TDEDEDI.NOMBRE AS "RESULTADO",
DDE.CONSECUTIVO AS "NRO_FORMULARIO",
'Tarjeta de Garantía' AS "DOCUMENTO_SOPORTE",
REDDEING.CODIGO AS "REFERENCIA_ENTRA",
REDDEING.DESCRIPCION AS "DESCRIPCION_REF_ENTRA",
REDDESAL.CODIGO AS "REFERENCIA_SALE",
REDDESAL.DESCRIPCION AS "DESCRIPCION_REF_SALE",
CBE.CODIGO AS "CODIGO_FALLA",
CBE.DESCRIPCION AS "DESCRIPCION_CODIGO_FALLA",
BE.CODIGO AS "CODIGO_BATERIOLOGO",
BE.NOMBRE AS "NOMBRE_BATERIOLOGO",
CD.COUNT_SOLICITUDES AS "CANTIDAD_DOCUMENTOS_ADJUNTOS",
LD.DOCUMENTOS AS "NOMBRE_DOCUMENTO_SOPORTE_ADJUNTO",
DDE.OBSERVACIONES AS "OBSERVACIONES_DIAGNOSTICO",
SGE.FECHA_CREACION AS "FECHA_CREACION",
SGE.ID_TIGAERP AS "ID_TIGAERP",
SGE.ID_GAORSIERP AS "ID_GAORSIERP",
SGE.ID_SUERP AS "ID_SUERP",
SDGE.ID_REERP AS "ID_REERP",
CLER.NIT AS "NIT" ,
CLER.PARTY_SITE_NAME AS "RAZON_SOCIAL" ,
DDFE.VENDEDOR_MAYORISTA AS "VENDEDOR_MAYORISTA",
DDE.FECHA_CREACION AS "FECHA_DIAGNOSTICO_FILTER",
(CASE
WHEN DDFE.VENTA_CANAL_COMERCIAL = 1 THEN 'SI'
ELSE
'NO'
END) AS "VENTA_CANAL_COMERCIAL"
FROM
SIMACMOBILE.SOLICITUD_GARANTIA_ERP SGE
INNER JOIN SIMACMOBILE.CLIENTE_ERP CLER
ON CLER.ID_CLERP = SGE.ID_CLERP
INNER JOIN SIMACMOBILE.SOLICITUD_DETALLE_GARANTIA_ERP SDGE
ON SDGE.ID_SOLGAERP = SGE.ID_SOLGAERP
INNER JOIN SIMACMOBILE.VISTA_SUCURSALES_ERP VSE
ON VSE.ID_SUERP = SGE.ID_SUERP
INNER JOIN SIMACMOBILE.DIAGNOSTICO_ERP DE
ON DE.ID_SOLGAERP = SGE.ID_SOLGAERP
INNER JOIN SIMACMOBILE.DIAGNOSTICO_DETALLE_ERP DDE
ON DDE.ID_SOLDEGAERP = SDGE.ID_SOLDEGAERP
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.BATERIOLOGO_ERP BE
ON BE.ID_BATERERP = DDE.ID_BATEERP
INNER JOIN SIMACMOBILE.TIPO_DIAGNOSTICO_ERP TDEDEDI
ON TDEDEDI.ID_TIDIERP = DDE.ID_TIDIERP
LEFT JOIN SIMACMOBILE.REFERENCIA_ERP REDDESAL
ON REDDESAL.ID_REERP = DDE.ID_REFESALERP
LEFT JOIN SIMACMOBILE.REFERENCIA_ERP REDDEING
ON REDDEING.ID_REERP = DDE.ID_REFEINGERP
LEFT JOIN CANTIDAD_DCTOS CD
ON CD.ID_SOLDEGAERP = SDGE.ID_SOLDEGAERP
LEFT JOIN LISTADO_DOCUMENTOS LD
ON LD.ID_SOLDEGAERP = SDGE.ID_SOLDEGAERP
WHERE
DDFE.TARJETA_GARANTIA = 1
UNION
SELECT
TO_CHAR(SGE.FECHA_CREACION, 'DD/MM/YYYY HH:MM:SS') AS "FECHA_CREACION_SOLICITUD",
TO_CHAR(DDE.FECHA_CREACION, 'DD/MM/YYYY HH:MM:SS') AS "FECHA_DIAGNOSTICO",
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",
(
CASE
WHEN SGE.ID_GAORSIERP = 1 THEN
'CEDI'
ELSE
'Energiteca'
END) AS "TIPO_SUCURSAL",
(VSE.CODIGO
|| '-'
|| VSE.NOMBRE) AS "SUCURSAL",
'Batería Auto' AS "LINEA_PRODUCTO",
TDEDEDI.NOMBRE AS "RESULTADO",
DDE.CONSECUTIVO AS "NRO_FORMULARIO",
'Almacén de cadena' AS "DOCUMENTO_SOPORTE",
REDDEING.CODIGO AS "REFERENCIA_ENTRA",
REDDEING.DESCRIPCION AS "DESCRIPCION_REF_ENTRA",
REDDESAL.CODIGO AS "REFERENCIA_SALE",
REDDESAL.DESCRIPCION AS "DESCRIPCION_REF_SALE",
CBE.CODIGO AS "CODIGO_FALLA",
CBE.DESCRIPCION AS "DESCRIPCION_CODIGO_FALLA",
BE.CODIGO AS "CODIGO_BATERIOLOGO",
BE.NOMBRE AS "NOMBRE_BATERIOLOGO",
CD.COUNT_SOLICITUDES AS "CANTIDAD_DOCUMENTOS_ADJUNTOS",
LD.DOCUMENTOS AS "NOMBRE_DOCUMENTO_SOPORTE_ADJUNTO",
DDE.OBSERVACIONES AS "OBSERVACIONES_DIAGNOSTICO",
SGE.FECHA_CREACION AS "FECHA_CREACION",
SGE.ID_TIGAERP AS "ID_TIGAERP",
SGE.ID_GAORSIERP AS "ID_GAORSIERP",
SGE.ID_SUERP AS "ID_SUERP",
SDGE.ID_REERP AS "ID_REERP",
CLER.NIT AS "NIT",
CLER.PARTY_SITE_NAME AS "RAZON_SOCIAL" ,
DDFE.VENDEDOR_MAYORISTA AS "VENDEDOR_MAYORISTA",
DDE.FECHA_CREACION AS "FECHA_DIAGNOSTICO_FILTER",
(CASE
WHEN DDFE.VENTA_CANAL_COMERCIAL = 1 THEN 'SI'
ELSE
'NO'
END) AS "VENTA_CANAL_COMERCIAL"
FROM
SIMACMOBILE.SOLICITUD_GARANTIA_ERP SGE
INNER JOIN SIMACMOBILE.CLIENTE_ERP CLER
ON CLER.ID_CLERP = SGE.ID_CLERP
INNER JOIN SIMACMOBILE.SOLICITUD_DETALLE_GARANTIA_ERP SDGE
ON SDGE.ID_SOLGAERP = SGE.ID_SOLGAERP
INNER JOIN SIMACMOBILE.VISTA_SUCURSALES_ERP VSE
ON VSE.ID_SUERP = SGE.ID_SUERP
INNER JOIN SIMACMOBILE.DIAGNOSTICO_ERP DE
ON DE.ID_SOLGAERP = SGE.ID_SOLGAERP
INNER JOIN SIMACMOBILE.DIAGNOSTICO_DETALLE_ERP DDE
ON DDE.ID_SOLDEGAERP = SDGE.ID_SOLDEGAERP
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.BATERIOLOGO_ERP BE
ON BE.ID_BATERERP = DDE.ID_BATEERP
INNER JOIN SIMACMOBILE.TIPO_DIAGNOSTICO_ERP TDEDEDI
ON TDEDEDI.ID_TIDIERP = DDE.ID_TIDIERP
LEFT JOIN SIMACMOBILE.REFERENCIA_ERP REDDESAL
ON REDDESAL.ID_REERP = DDE.ID_REFESALERP
LEFT JOIN SIMACMOBILE.REFERENCIA_ERP REDDEING
ON REDDEING.ID_REERP = DDE.ID_REFEINGERP
LEFT JOIN CANTIDAD_DCTOS CD
ON CD.ID_SOLDEGAERP = SDGE.ID_SOLDEGAERP
LEFT JOIN LISTADO_DOCUMENTOS LD
ON LD.ID_SOLDEGAERP = SDGE.ID_SOLDEGAERP
WHERE
DDFE.ALMACEN_CADENA = 1
UNION
SELECT
TO_CHAR(SGE.FECHA_CREACION, 'DD/MM/YYYY HH:MM:SS') AS "FECHA_CREACION_SOLICITUD",
TO_CHAR(DDE.FECHA_CREACION, 'DD/MM/YYYY HH:MM:SS') AS "FECHA_DIAGNOSTICO",
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",
(
CASE
WHEN SGE.ID_GAORSIERP = 1 THEN
'CEDI'
ELSE
'Energiteca'
END) AS "TIPO_SUCURSAL",
(VSE.CODIGO
|| '-'
|| VSE.NOMBRE) AS "SUCURSAL",
'Batería Auto' AS "LINEA_PRODUCTO",
TDEDEDI.NOMBRE AS "RESULTADO",
DDE.CONSECUTIVO AS "NRO_FORMULARIO",
'Factura ENERGITECA' AS "DOCUMENTO_SOPORTE",
REDDEING.CODIGO AS "REFERENCIA_ENTRA",
REDDEING.DESCRIPCION AS "DESCRIPCION_REF_ENTRA",
REDDESAL.CODIGO AS "REFERENCIA_SALE",
REDDESAL.DESCRIPCION AS "DESCRIPCION_REF_SALE",
CBE.CODIGO AS "CODIGO_FALLA",
CBE.DESCRIPCION AS "DESCRIPCION_CODIGO_FALLA",
BE.CODIGO AS "CODIGO_BATERIOLOGO",
BE.NOMBRE AS "NOMBRE_BATERIOLOGO",
CD.COUNT_SOLICITUDES AS "CANTIDAD_DOCUMENTOS_ADJUNTOS",
LD.DOCUMENTOS AS "NOMBRE_DOCUMENTO_SOPORTE_ADJUNTO",
DDE.OBSERVACIONES AS "OBSERVACIONES_DIAGNOSTICO",
SGE.FECHA_CREACION AS "FECHA_CREACION",
SGE.ID_TIGAERP AS "ID_TIGAERP",
SGE.ID_GAORSIERP AS "ID_GAORSIERP",
SGE.ID_SUERP AS "ID_SUERP",
SDGE.ID_REERP AS "ID_REERP",
CLER.NIT AS "NIT",
CLER.PARTY_SITE_NAME AS "RAZON_SOCIAL" ,
DDFE.VENDEDOR_MAYORISTA AS "VENDEDOR_MAYORISTA",
DDE.FECHA_CREACION AS "FECHA_DIAGNOSTICO_FILTER",
(CASE
WHEN DDFE.VENTA_CANAL_COMERCIAL = 1 THEN 'SI'
ELSE
'NO'
END) AS "VENTA_CANAL_COMERCIAL"
FROM
SIMACMOBILE.SOLICITUD_GARANTIA_ERP SGE
INNER JOIN SIMACMOBILE.CLIENTE_ERP CLER
ON CLER.ID_CLERP = SGE.ID_CLERP
INNER JOIN SIMACMOBILE.SOLICITUD_DETALLE_GARANTIA_ERP SDGE
ON SDGE.ID_SOLGAERP = SGE.ID_SOLGAERP
INNER JOIN SIMACMOBILE.VISTA_SUCURSALES_ERP VSE
ON VSE.ID_SUERP = SGE.ID_SUERP
INNER JOIN SIMACMOBILE.DIAGNOSTICO_ERP DE
ON DE.ID_SOLGAERP = SGE.ID_SOLGAERP
INNER JOIN SIMACMOBILE.DIAGNOSTICO_DETALLE_ERP DDE
ON DDE.ID_SOLDEGAERP = SDGE.ID_SOLDEGAERP
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.BATERIOLOGO_ERP BE
ON BE.ID_BATERERP = DDE.ID_BATEERP
INNER JOIN SIMACMOBILE.TIPO_DIAGNOSTICO_ERP TDEDEDI
ON TDEDEDI.ID_TIDIERP = DDE.ID_TIDIERP
LEFT JOIN SIMACMOBILE.REFERENCIA_ERP REDDESAL
ON REDDESAL.ID_REERP = DDE.ID_REFESALERP
LEFT JOIN SIMACMOBILE.REFERENCIA_ERP REDDEING
ON REDDEING.ID_REERP = DDE.ID_REFEINGERP
LEFT JOIN CANTIDAD_DCTOS CD
ON CD.ID_SOLDEGAERP = SDGE.ID_SOLDEGAERP
LEFT JOIN LISTADO_DOCUMENTOS LD
ON LD.ID_SOLDEGAERP = SDGE.ID_SOLDEGAERP
WHERE
DDFE.FACTURA_ENERGITECA = 1
UNION
SELECT
TO_CHAR(SGE.FECHA_CREACION, 'DD/MM/YYYY HH:MM:SS') AS "FECHA_CREACION_SOLICITUD",
TO_CHAR(DDE.FECHA_CREACION, 'DD/MM/YYYY HH:MM:SS') AS "FECHA_DIAGNOSTICO",
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",
(
CASE
WHEN SGE.ID_GAORSIERP = 1 THEN
'CEDI'
ELSE
'Energiteca'
END) AS "TIPO_SUCURSAL",
(VSE.CODIGO
|| '-'
|| VSE.NOMBRE) AS "SUCURSAL",
'Batería Auto' AS "LINEA_PRODUCTO",
TDEDEDI.NOMBRE AS "RESULTADO",
DDE.CONSECUTIVO AS "NRO_FORMULARIO",
'Factura Distribuidor' AS "DOCUMENTO_SOPORTE",
REDDEING.CODIGO AS "REFERENCIA_ENTRA",
REDDEING.DESCRIPCION AS "DESCRIPCION_REF_ENTRA",
REDDESAL.CODIGO AS "REFERENCIA_SALE",
REDDESAL.DESCRIPCION AS "DESCRIPCION_REF_SALE",
CBE.CODIGO AS "CODIGO_FALLA",
CBE.DESCRIPCION AS "DESCRIPCION_CODIGO_FALLA",
BE.CODIGO AS "CODIGO_BATERIOLOGO",
BE.NOMBRE AS "NOMBRE_BATERIOLOGO",
CD.COUNT_SOLICITUDES AS "CANTIDAD_DOCUMENTOS_ADJUNTOS",
LD.DOCUMENTOS AS "NOMBRE_DOCUMENTO_SOPORTE_ADJUNTO",
DDE.OBSERVACIONES AS "OBSERVACIONES_DIAGNOSTICO",
SGE.FECHA_CREACION AS "FECHA_CREACION",
SGE.ID_TIGAERP AS "ID_TIGAERP",
SGE.ID_GAORSIERP AS "ID_GAORSIERP",
SGE.ID_SUERP AS "ID_SUERP",
SDGE.ID_REERP AS "ID_REERP",
CLER.NIT AS "NIT",
CLER.PARTY_SITE_NAME AS "RAZON_SOCIAL" ,
DDFE.VENDEDOR_MAYORISTA AS "VENDEDOR_MAYORISTA",
DDE.FECHA_CREACION AS "FECHA_DIAGNOSTICO_FILTER",
(CASE
WHEN DDFE.VENTA_CANAL_COMERCIAL = 1 THEN 'SI'
ELSE
'NO'
END) AS "VENTA_CANAL_COMERCIAL"
FROM
SIMACMOBILE.SOLICITUD_GARANTIA_ERP SGE
INNER JOIN SIMACMOBILE.CLIENTE_ERP CLER
ON CLER.ID_CLERP = SGE.ID_CLERP
INNER JOIN SIMACMOBILE.SOLICITUD_DETALLE_GARANTIA_ERP SDGE
ON SDGE.ID_SOLGAERP = SGE.ID_SOLGAERP
INNER JOIN SIMACMOBILE.VISTA_SUCURSALES_ERP VSE
ON VSE.ID_SUERP = SGE.ID_SUERP
INNER JOIN SIMACMOBILE.DIAGNOSTICO_ERP DE
ON DE.ID_SOLGAERP = SGE.ID_SOLGAERP
INNER JOIN SIMACMOBILE.DIAGNOSTICO_DETALLE_ERP DDE
ON DDE.ID_SOLDEGAERP = SDGE.ID_SOLDEGAERP
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.BATERIOLOGO_ERP BE
ON BE.ID_BATERERP = DDE.ID_BATEERP
INNER JOIN SIMACMOBILE.TIPO_DIAGNOSTICO_ERP TDEDEDI
ON TDEDEDI.ID_TIDIERP = DDE.ID_TIDIERP
LEFT JOIN SIMACMOBILE.REFERENCIA_ERP REDDESAL
ON REDDESAL.ID_REERP = DDE.ID_REFESALERP
LEFT JOIN SIMACMOBILE.REFERENCIA_ERP REDDEING
ON REDDEING.ID_REERP = DDE.ID_REFEINGERP
LEFT JOIN CANTIDAD_DCTOS CD
ON CD.ID_SOLDEGAERP = SDGE.ID_SOLDEGAERP
LEFT JOIN LISTADO_DOCUMENTOS LD
ON LD.ID_SOLDEGAERP = SDGE.ID_SOLDEGAERP
WHERE
DDFE.FACTURA_DISTRIBUIDOR = 1
UNION
SELECT
TO_CHAR(SGE.FECHA_CREACION, 'DD/MM/YYYY HH:MM:SS') AS "FECHA_CREACION_SOLICITUD",
TO_CHAR(DDE.FECHA_CREACION, 'DD/MM/YYYY HH:MM:SS') AS "FECHA_DIAGNOSTICO",
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",
(
CASE
WHEN SGE.ID_GAORSIERP = 1 THEN
'CEDI'
ELSE
'Energiteca'
END) AS "TIPO_SUCURSAL",
(VSE.CODIGO
|| '-'
|| VSE.NOMBRE) AS "SUCURSAL",
'Batería Auto' AS "LINEA_PRODUCTO",
TDEDEDI.NOMBRE AS "RESULTADO",
DDE.CONSECUTIVO AS "NRO_FORMULARIO",
'Certificado de garantía' AS "DOCUMENTO_SOPORTE",
REDDEING.CODIGO AS "REFERENCIA_ENTRA",
REDDEING.DESCRIPCION AS "DESCRIPCION_REF_ENTRA",
REDDESAL.CODIGO AS "REFERENCIA_SALE",
REDDESAL.DESCRIPCION AS "DESCRIPCION_REF_SALE",
CBE.CODIGO AS "CODIGO_FALLA",
CBE.DESCRIPCION AS "DESCRIPCION_CODIGO_FALLA",
BE.CODIGO AS "CODIGO_BATERIOLOGO",
BE.NOMBRE AS "NOMBRE_BATERIOLOGO",
CD.COUNT_SOLICITUDES AS "CANTIDAD_DOCUMENTOS_ADJUNTOS",
LD.DOCUMENTOS AS "NOMBRE_DOCUMENTO_SOPORTE_ADJUNTO",
DDE.OBSERVACIONES AS "OBSERVACIONES_DIAGNOSTICO",
SGE.FECHA_CREACION AS "FECHA_CREACION",
SGE.ID_TIGAERP AS "ID_TIGAERP",
SGE.ID_GAORSIERP AS "ID_GAORSIERP",
SGE.ID_SUERP AS "ID_SUERP",
SDGE.ID_REERP AS "ID_REERP",
CLER.NIT AS "NIT",
CLER.PARTY_SITE_NAME AS "RAZON_SOCIAL" ,
DDFE.VENDEDOR_MAYORISTA AS "VENDEDOR_MAYORISTA",
DDE.FECHA_CREACION AS "FECHA_DIAGNOSTICO_FILTER",
(CASE
WHEN DDFE.VENTA_CANAL_COMERCIAL = 1 THEN 'SI'
WHEN DDFE.VENTA_CANAL_COMERCIAL = 0 THEN 'NO'
ELSE
''
END) AS "VENTA_CANAL_COMERCIAL"
FROM
SIMACMOBILE.SOLICITUD_GARANTIA_ERP SGE
INNER JOIN SIMACMOBILE.CLIENTE_ERP CLER
ON CLER.ID_CLERP = SGE.ID_CLERP
INNER JOIN SIMACMOBILE.SOLICITUD_DETALLE_GARANTIA_ERP SDGE
ON SDGE.ID_SOLGAERP = SGE.ID_SOLGAERP
INNER JOIN SIMACMOBILE.VISTA_SUCURSALES_ERP VSE
ON VSE.ID_SUERP = SGE.ID_SUERP
INNER JOIN SIMACMOBILE.DIAGNOSTICO_ERP DE
ON DE.ID_SOLGAERP = SGE.ID_SOLGAERP
INNER JOIN SIMACMOBILE.DIAGNOSTICO_DETALLE_ERP DDE
ON DDE.ID_SOLDEGAERP = SDGE.ID_SOLDEGAERP
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.BATERIOLOGO_ERP BE
ON BE.ID_BATERERP = DDE.ID_BATEERP
INNER JOIN SIMACMOBILE.TIPO_DIAGNOSTICO_ERP TDEDEDI
ON TDEDEDI.ID_TIDIERP = DDE.ID_TIDIERP
LEFT JOIN SIMACMOBILE.REFERENCIA_ERP REDDESAL
ON REDDESAL.ID_REERP = DDE.ID_REFESALERP
LEFT JOIN SIMACMOBILE.REFERENCIA_ERP REDDEING
ON REDDEING.ID_REERP = DDE.ID_REFEINGERP
LEFT JOIN CANTIDAD_DCTOS CD
ON CD.ID_SOLDEGAERP = SDGE.ID_SOLDEGAERP
LEFT JOIN LISTADO_DOCUMENTOS LD
ON LD.ID_SOLDEGAERP = SDGE.ID_SOLDEGAERP
WHERE
DDFE.CERTIFICADO_GARANTIA = 1
UNION
--MJ
SELECT
TO_CHAR(SGE.FECHA_CREACION, 'DD/MM/YYYY HH:MM:SS') AS "FECHA_CREACION_SOLICITUD",
TO_CHAR(DDE.FECHA_CREACION, 'DD/MM/YYYY HH:MM:SS') AS "FECHA_DIAGNOSTICO",
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",
(
CASE
WHEN SGE.ID_GAORSIERP = 1 THEN
'CEDI'
ELSE
'Energiteca'
END) AS "TIPO_SUCURSAL",
(VSE.CODIGO
|| '-'
|| VSE.NOMBRE) AS "SUCURSAL",
TIGAE.DESCRIPCION AS "LINEA_PRODUCTO",
TDEDEDI.NOMBRE AS "RESULTADO",
DDE.CONSECUTIVO AS "NRO_FORMULARIO",
'' AS "DOCUMENTO_SOPORTE",
REDDEING.CODIGO AS "REFERENCIA_ENTRA",
REDDEING.DESCRIPCION AS "DESCRIPCION_REF_ENTRA",
REDDESAL.CODIGO AS "REFERENCIA_SALE",
REDDESAL.DESCRIPCION AS "DESCRIPCION_REF_SALE",
CBE.CODIGO AS "CODIGO_FALLA",
CBE.DESCRIPCION AS "DESCRIPCION_CODIGO_FALLA",
BE.CODIGO AS "CODIGO_BATERIOLOGO",
BE.NOMBRE AS "NOMBRE_BATERIOLOGO",
CD.COUNT_SOLICITUDES AS "CANTIDAD_DOCUMENTOS_ADJUNTOS",
LD.DOCUMENTOS AS "NOMBRE_DOCUMENTO_SOPORTE_ADJUNTO",
DDE.OBSERVACIONES AS "OBSERVACIONES_DIAGNOSTICO",
SGE.FECHA_CREACION AS "FECHA_CREACION",
SGE.ID_TIGAERP AS "ID_TIGAERP",
SGE.ID_GAORSIERP AS "ID_GAORSIERP",
SGE.ID_SUERP AS "ID_SUERP",
SDGE.ID_REERP AS "ID_REERP",
CLER.NIT AS "NIT",
CLER.PARTY_SITE_NAME AS "RAZON_SOCIAL" ,
DDFE.VENDEDOR_MAYORISTA AS "VENDEDOR_MAYORISTA",
DDE.FECHA_CREACION AS "FECHA_DIAGNOSTICO_FILTER",
(CASE
WHEN DDFE.VENTA_CANAL_COMERCIAL = 1 THEN 'SI'
WHEN DDFE.VENTA_CANAL_COMERCIAL = 0 THEN 'NO'
ELSE
''
END) AS "VENTA_CANAL_COMERCIAL"
FROM
SIMACMOBILE.SOLICITUD_GARANTIA_ERP SGE
INNER JOIN SIMACMOBILE.TIPO_GARANTIA_ERP TIGAE
ON SGE.ID_TIGAERP = TIGAE.ID_TIGAERP
INNER JOIN SIMACMOBILE.CLIENTE_ERP CLER
ON CLER.ID_CLERP = SGE.ID_CLERP
INNER JOIN SIMACMOBILE.SOLICITUD_DETALLE_GARANTIA_ERP SDGE
ON SDGE.ID_SOLGAERP = SGE.ID_SOLGAERP
LEFT JOIN SIMACMOBILE.GARANTIA_SOPORTE_SOLICITUD_ERP GSSEGT
ON GSSEGT.ID_SOLDEGAERP = SDGE.ID_SOLDEGAERP
LEFT JOIN SIMACMOBILE.GARANTIA_TIPO_SOPORTE_ERP GTSE
ON GTSE.ID_GATISOERP = GSSEGT.ID_GATISOERP
INNER JOIN SIMACMOBILE.VISTA_SUCURSALES_ERP VSE
ON VSE.ID_SUERP = SGE.ID_SUERP
INNER JOIN SIMACMOBILE.DIAGNOSTICO_ERP DE
ON DE.ID_SOLGAERP = SGE.ID_SOLGAERP
INNER JOIN SIMACMOBILE.DIAGNOSTICO_DETALLE_ERP DDE
ON DDE.ID_SOLDEGAERP = SDGE.ID_SOLDEGAERP
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
LEFT JOIN SIMACMOBILE.BATERIOLOGO_ERP BE
ON BE.ID_BATERERP = DDE.ID_BATEERP
INNER JOIN SIMACMOBILE.TIPO_DIAGNOSTICO_ERP TDEDEDI
ON TDEDEDI.ID_TIDIERP = DDE.ID_TIDIERP
LEFT JOIN SIMACMOBILE.REFERENCIA_ERP REDDESAL
ON REDDESAL.ID_REERP = DDE.ID_REFESALERP
LEFT JOIN SIMACMOBILE.REFERENCIA_ERP REDDEING
ON REDDEING.ID_REERP = DDE.ID_REFEINGERP
LEFT JOIN CANTIDAD_DCTOS CD
ON CD.ID_SOLDEGAERP = SDGE.ID_SOLDEGAERP
LEFT JOIN LISTADO_DOCUMENTOS LD
ON LD.ID_SOLDEGAERP = SDGE.ID_SOLDEGAERP
WHERE
NOT (
TDEDEDI.NOMBRE = 'OTORGADA'
AND TIGAE.DESCRIPCION = 'Batería Auto'
)
) QUERY;