SISTEMAS
Page tree
Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 12 Next »

Tablas referenciadas

Tablas referidas
-- Ceibo sin sicoop
SELECT *
FROM POWERBI."Aut_s_Sicoop" ass;

-- Sicoop sin ceibo
SELECT *
FROM POWERBI.H_ORDER_SAUT hos;

-- Ceibo + Sicoop
SELECT *
FROM POWERBI.AUT_H_ORDER aho;

1- Query para importar las transacciones de CEIBO SIN SICOOP

Ceibo sin Sicoop 
-- Ceibo sin sicoop
SELECT
ltc.NRO_TARJETA,
TO_CHAR(ltc.NRO_CUENTA) AS NRO_CUENTA_ORIGINANTE,
ltc.NRO_DOCUMENTO AS DOCUMENTO_ORIGINANTE,
p.NOMBRES || ' ' || p.APELLIDOS AS ORIGINANTE,
LTC.CODIGO_ADQUIRIENTE ,
acq.DESCRIPCION AS ADQUIRIENTE,
ltc.MOVEXTFCH AS FECHA_TRANSACCION,
NVL(TRIM(ltc.NRO_TICKET) || '-' || ltc.NRO_TARJETA, TO_CHAR(ltc.NRO_AUTORIZACION) || '-' || ltc.NRO_TARJETA) AS ID_TRANSACCION,
TRIM(ltc.NRO_TICKET),
ltc.NRO_AUTORIZACION,
ltc.IMPORTE,
M.DESCRIPCION AS MONEDA,
t.DESCRIPCION_OPERACION AS TIPO_OPERACION,
ltc.TX_INTERNA AS TIPO_TRANSACCION_INTERNA,
PA.DESCRIPCION AS PAIS,
e.DESCRIPCION AS EMISOR,
SE.DESCRIPCION AS SUCURSAL_EMISOR,
C.DESCRIPCION AS CARTERA,
ltc.EXTRANJERA,
GA.DESCRIPCION AS GRUPO_AFINIDAD,
CAST(ltc.ORDEN AS NUMBER) AS ORDEN_A,
r.DESCRIPCION AS RUBRO,
TO_CHAR(ltc.ISCOMERCIO) AS ISCOMERCIO,
TO_CHAR(ltc.ATM) AS ATM,
ect.DESCRIPCION AS ESTADO_CUENTA,
ect2.DESCRIPCION AS ESTADO_TARJETA,
CASE
WHEN ltc.TIPO_AUTORIZACION = 'A' THEN 'APROBADA'
WHEN ltc.TIPO_AUTORIZACION = 'R' THEN 'RECHAZADA'
END AS TIPO_AUTORIZACION,
LTC.MODO_ENTRADA AS MODO_ENTRADA,
ltc.COTIZACION ,
ltc.CAPTURA ,
ltc.MANUAL ,
ltc.IMPORTE_DOLARES ,
ltc.ADICIONAL ,
ltc.CODIGO_OPERACION,
'CEIBO' AS FUENTE_ORIGEN
FROM CREDITOPY.LOG_TRANSACC_CR ltc
LEFT JOIN CREDITOPY.TIPOS_OPERACIONES t
ON ltc.TIPO_OPERACION = t.TIPO_OPERACION
LEFT JOIN CREDITOPY.ACQUIRERS acq
ON ltc.CODIGO_ADQUIRIENTE = acq.ACQUIRER
LEFT JOIN DEBCREDPY.EMISORES e
ON ltc.EMISOR = e.EMISOR
LEFT JOIN CREDITOPY.GRUPOS_AFINIDAD ga
ON ltc.GRUPO_AFINIDAD = ga.GRUPO_AFINIDAD
LEFT JOIN CREDITOPY.RUBROS r
ON ltc.RUBRO = r.RUBRO
LEFT JOIN DEBCREDPY.SUCURSALES_EMISORES se
ON ltc.SUCURSAL_EMISOR = se.SUCURSAL_EMISOR
AND ltc.EMISOR = se.EMISOR
LEFT JOIN DEBCREDPY.CARTERAS c
ON ltc.CARTERA = c.CARTERA
AND ltc.EMISOR = c.EMISOR
LEFT JOIN DEBCREDPY.PAISES pa
ON ltc.PAIS = pa.PAIS
LEFT JOIN DEBCREDPY.MONEDAS m
ON ltc.MONEDA = m.CODIGO_DE_MONEDA
LEFT JOIN DEBCREDPY.PERSONAS p
ON p.DOCUMENTO = LTC.NRO_DOCUMENTO
LEFT JOIN CREDITOPY.ESTADOS_CTAS_TARJ ect
ON ltc.ESTADOC = ect.ESTADO
LEFT JOIN CREDITOPY.ESTADOS_CTAS_TARJ ect2
ON ltc.ESTADOT = ect2.ESTADO
WHERE ltc.ACQUIRING_INSTITUTION NOT IN ('10000000023', '490508','10000000017','490509')
and MOVEXTFCH >= TO_DATE('01012025', 'ddmmyyyy')
AND MOVEXTFCH < TO_DATE('01122025', 'ddmmyyyy')
UNION ALL
SELECT
ltc.NRO_TARJETA,
TO_CHAR(ltc.NRO_CUENTA) AS NRO_CUENTA_ORIGINANTE,
ltc.NRO_DOCUMENTO AS DOCUMENTO_ORIGINANTE,
p.NOMBRES || ' ' || p.APELLIDOS AS ORIGINANTE,
LTC.CODIGO_ADQUIRIENTE ,
acq.DESCRIPCION AS ADQUIRIENTE,
ltc.MOVEXTFCH AS FECHA_TRANSACCION,
NVL(TRIM(ltc.NRO_TICKET) || '-' || ltc.NRO_TARJETA, TO_CHAR(ltc.NRO_AUTORIZACION) || '-' || ltc.NRO_TARJETA) AS ID_TRANSACCION,
TRIM(ltc.NRO_TICKET),
ltc.NRO_AUTORIZACION,
ltc.IMPORTE,
M.DESCRIPCION AS MONEDA,
t.DESCRIPCION_OPERACION AS TIPO_OPERACION,
ltc.TX_INTERNA AS TIPO_TRANSACCION_INTERNA,
PA.DESCRIPCION AS PAIS,
e.DESCRIPCION AS EMISOR,
SE.DESCRIPCION AS SUCURSAL_EMISOR,
C.DESCRIPCION AS CARTERA,
ltc.EXTRANJERA,
GA.DESCRIPCION AS GRUPO_AFINIDAD,
CAST(ltc.ORDEN AS NUMBER) AS ORDEN_A,
r.DESCRIPCION AS RUBRO,
TO_CHAR(ltc.ISCOMERCIO) AS ISCOMERCIO,
TO_CHAR(ltc.ATM) AS ATM,
ect.DESCRIPCION AS ESTADO_CUENTA,
ect2.DESCRIPCION AS ESTADO_TARJETA,
CASE
WHEN ltc.TIPO_AUTORIZACION = 'A' THEN 'APROBADA'
WHEN ltc.TIPO_AUTORIZACION = 'R' THEN 'RECHAZADA'
END AS TIPO_AUTORIZACION,
LTC.MODO_ENTRADA AS MODO_ENTRADA,
ltc.COTIZACION ,
ltc.CAPTURA ,
ltc.MANUAL ,
ltc.IMPORTE_DOLARES ,
ltc.ADICIONAL ,
ltc.CODIGO_OPERACION,
'CEIBO' AS FUENTE_ORIGEN
FROM CREDITOPY.LOG_TRANSACC_CR_HIST ltc
LEFT JOIN CREDITOPY.TIPOS_OPERACIONES t
ON ltc.TIPO_OPERACION = t.TIPO_OPERACION
LEFT JOIN CREDITOPY.ACQUIRERS acq
ON ltc.CODIGO_ADQUIRIENTE = acq.ACQUIRER
LEFT JOIN DEBCREDPY.EMISORES e
ON ltc.EMISOR = e.EMISOR
LEFT JOIN CREDITOPY.GRUPOS_AFINIDAD ga
ON ltc.GRUPO_AFINIDAD = ga.GRUPO_AFINIDAD
LEFT JOIN CREDITOPY.RUBROS r
ON ltc.RUBRO = r.RUBRO
LEFT JOIN DEBCREDPY.SUCURSALES_EMISORES se
ON ltc.SUCURSAL_EMISOR = se.SUCURSAL_EMISOR
AND ltc.EMISOR = se.EMISOR
LEFT JOIN DEBCREDPY.CARTERAS c
ON ltc.CARTERA = c.CARTERA
AND ltc.EMISOR = c.EMISOR
LEFT JOIN DEBCREDPY.PAISES pa
ON ltc.PAIS = pa.PAIS
LEFT JOIN DEBCREDPY.MONEDAS m
ON ltc.MONEDA = m.CODIGO_DE_MONEDA
LEFT JOIN DEBCREDPY.PERSONAS p
ON p.DOCUMENTO = LTC.NRO_DOCUMENTO
LEFT JOIN CREDITOPY.ESTADOS_CTAS_TARJ ect
ON ltc.ESTADOC = ect.ESTADO
LEFT JOIN CREDITOPY.ESTADOS_CTAS_TARJ ect2
ON ltc.ESTADOT = ect2.ESTADO
WHERE ltc.ACQUIRING_INSTITUTION NOT IN ('10000000023', '490508','10000000017','490509')
and MOVEXTFCH >= TO_DATE('01012025', 'ddmmyyyy')
AND MOVEXTFCH < TO_DATE('01122025', 'ddmmyyyy')

2- Query para importar las transacciones de SICOOP SIN CEIBO

Sicoop sin Ceibo 
-- Sicoop sin Ceibo
 SELECT
  TO_CHAR(ho.COD_ORIGINANTE)             AS PARTICIPANTE_ORIGINATE,
  TO_CHAR(ho.CUENTA_ORIGEN)              AS NRO_CUENTA_ORIGINANTE,
  TO_CHAR(ho.CI_NIT_ORIGINANTE)          AS DOCUMENTO_ORIGINANTE,
  hp.FULLNAME                            AS ORIGINANTE,
  TO_CHAR(ho.TIPO_CUENTA_ORIGEN)         AS TIPO_CUENTA_ORIGEN,
  CAST(ho.INIT_STAMP AS TIMESTAMP)       AS FECHA_TRANSACCION,
  TO_CHAR(ho.ID_SESION)                  AS ID_SESION,
  ho.NUM_ORDEN_ORIGINANTE                 AS ID_TRANSACCION,
  CAST(ho.IMPORTE/100 AS NUMBER(18,2))   AS IMPORTE,
  TO_CHAR(ho.COD_MONEDA)                  AS MONEDA,
  TO_CHAR(ho.TIPO_MOVIMIENTO)            AS TIPO_OPERACION,
  TO_CHAR(ho.TIPO_TRANSACCION)           AS TIPO_TRANSACCION_INTERNA,
  TO_CHAR(ho.COD_PAIS_ORIGINANTE)        AS PAIS,
  TO_CHAR(ho.CANAL)                      AS CANAL,
  TO_CHAR(ho.COD_SERVICIO)               AS COD_SERVICIO,
  TO_CHAR(ho.GLOSA)                      AS GLOSA,
  CAST(ho.MONTO_CARGO AS NUMBER)         AS MONTO_CARGO,
  TO_CHAR(ho.DESTINO_FONDOS)             AS DESTINO_FONDOS,
  TO_CHAR(ho.COD_EMPRESA)                AS EMISOR,
  TO_CHAR(ho.COD_DESTINATARIO)             AS PARTICIPANTE_DESTINO,
  TO_CHAR(ho.CUENTA_DESTINO)             AS CUENTA_DESTINO,
  TO_CHAR(ho.TIPO_CUENTA_DESTINO)        AS TIPO_CUENTA_DESTINO,
  TO_CHAR(ho.CI_NIT_DESTINATARIO)        AS CI_NIT_DESTINATARIO,
  hp2.FULLNAME                           AS DESTINATARIO,
  ho.NUM_ORDEN_DESTINATARIO,
  ho.estado                                AS ESTADO_TRANSACCION,
  'SICOOP'                               AS FUENTE_ORIGEN
FROM GATEWAY.H_ORDER@SICOOP ho
LEFT JOIN GATEWAY.H_PARTY@SICOOP  hp
       ON ho.COD_ORIGINANTE = hp.CODE
LEFT JOIN GATEWAY.H_PARTY@SICOOP  hp2
       ON ho.COD_DESTINATARIO = hp2.CODE
WHERE ho.COD_ORIGINANTE NOT IN ('1600', '3601')
AND ho.COD_DESTINATARIO NOT IN ('1600', '3601')
ORDER BY CAST(ho.INIT_STAMP AS TIMESTAMP) desc

3- Query para importar el Consolidado de CEIBO y SICOOP

Ceibo + Sicoop 
-- Ceibo + Sicoop
SELECT
    NULL                                     AS PARTICIPANTE_ORIGINATE,
    ltc.NRO_TARJETA,
    TO_CHAR(ltc.NRO_CUENTA)                  AS NRO_CUENTA_ORIGINANTE,
    ltc.NRO_DOCUMENTO                        AS DOCUMENTO_ORIGINANTE,
    p.NOMBRES || ' ' || p.APELLIDOS          AS ORIGINANTE,
    LTC.CODIGO_ADQUIRIENTE ,
    acq.DESCRIPCION                          AS ADQUIRIENTE,
    null                                     AS TIPO_CUENTA_ORIGEN,
    ltc.MOVEXTFCH                            AS FECHA_TRANSACCION,
    NULL                                     AS ID_SESION,
    NVL(ltc.NRO_TICKET,ltc.NRO_AUTORIZACION) AS ID_TRANSACCION,
    ltc.IMPORTE,
    M.DESCRIPCION                            AS MONEDA,
    t.DESCRIPCION_OPERACION                  AS TIPO_OPERACION,
    ltc.TX_INTERNA                           AS TIPO_TRANSACCION_INTERNA,
    PA.DESCRIPCION                           AS PAIS,
    null                                     AS CANAL,
    null                                     AS COD_SERVICIO,
    null                                     AS GLOSA,
    NULL                                     AS  MONTO_CARGO,
    null                                     AS DESTINO_FONDOS,
    e.DESCRIPCION                            AS EMISOR,
    SE.DESCRIPCION                           AS SUCURSAL_EMISOR,
    C.DESCRIPCION                            AS CARTERA,
    ltc.EXTRANJERA,
    GA.DESCRIPCION                           AS GRUPO_AFINIDAD,
    CAST(ltc.ORDEN AS NUMBER)              AS ORDEN_A,
    r.DESCRIPCION                          AS RUBRO,
    TO_CHAR(ltc.ISCOMERCIO)                AS ISCOMERCIO,
    TO_CHAR(ltc.ATM)                       AS ATM,
    NULL                                   AS PARTICIPANTE_DESTINO,
    null                                   AS CUENTA_DESTINO,
    null                                   AS TIPO_CUENTA_DESTINO,
    null                                   AS CI_NIT_DESTINATARIO,
    null                                   AS DESTINATARIO,
    null                                   AS NUM_ORDEN_DESTINATARIO,
    ect.DESCRIPCION                        AS ESTADO_CUENTA,
    ect2.DESCRIPCION                       AS ESTADO_TARJETA,
    CASE
        WHEN ltc.TIPO_AUTORIZACION = 'A' THEN 'APROBADA'
    WHEN ltc.TIPO_AUTORIZACION = 'R' THEN 'RECHAZADA'
    END                                    AS TIPO_AUTORIZACION,
    LTC.MODO_ENTRADA                       AS MODO_ENTRADA,
    NULL                                   AS ESTADO_TRANSACCION,
    ltc.COTIZACION ,
    ltc.CAPTURA ,
    ltc.MANUAL ,
    ltc.IMPORTE_DOLARES ,
    ltc.ADICIONAL ,
    ltc.CODIGO_OPERACION,
    'CEIBO'                                AS FUENTE_ORIGEN
FROM CREDITOPY.LOG_TRANSACC_CR ltc
    LEFT JOIN CREDITOPY.TIPOS_OPERACIONES t ON ltc.TIPO_OPERACION = t.TIPO_OPERACION
    LEFT JOIN CREDITOPY.ACQUIRERS acq ON ltc.CODIGO_ADQUIRIENTE = acq.ACQUIRER
    LEFT JOIN DEBCREDPY.EMISORES e ON ltc.EMISOR = e.EMISOR
    LEFT JOIN CREDITOPY.GRUPOS_AFINIDAD ga ON ltc.GRUPO_AFINIDAD = ga.GRUPO_AFINIDAD
    LEFT JOIN CREDITOPY.RUBROS r ON ltc.RUBRO = r.RUBRO
    LEFT JOIN DEBCREDPY.SUCURSALES_EMISORES se ON ltc.SUCURSAL_EMISOR = se.SUCURSAL_EMISOR AND ltc.EMISOR = se.EMISOR
    LEFT JOIN DEBCREDPY.CARTERAS c ON ltc.CARTERA = c.CARTERA AND ltc.EMISOR  = c.EMISOR
    LEFT JOIN DEBCREDPY.PAISES pa ON ltc.PAIS = pa.PAIS
    LEFT JOIN DEBCREDPY.MONEDAS m ON ltc.MONEDA = m.CODIGO_DE_MONEDA
    LEFT JOIN DEBCREDPY.PERSONAS p ON p.DOCUMENTO = LTC.NRO_DOCUMENTO
    LEFT JOIN CREDITOPY.ESTADOS_CTAS_TARJ ect ON ltc.ESTADOC = ect.ESTADO
    LEFT JOIN CREDITOPY.ESTADOS_CTAS_TARJ ect2 ON ltc.ESTADOT = ect2.ESTADO
WHERE  ltc.ACQUIRING_INSTITUTION IN ('10000000023', '490508','10000000017','490509')
and MOVEXTFCH >= TO_DATE('01012025', 'ddmmyyyy')
UNION ALL
SELECT
    NULL                                     AS PARTICIPANTE_ORIGINATE,
    ltc.NRO_TARJETA,
    TO_CHAR(ltc.NRO_CUENTA)                  AS NRO_CUENTA_ORIGINANTE,
    ltc.NRO_DOCUMENTO                        AS DOCUMENTO_ORIGINANTE,
    p.NOMBRES || ' ' || p.APELLIDOS          AS ORIGINANTE,
    LTC.CODIGO_ADQUIRIENTE ,
    acq.DESCRIPCION                          AS ADQUIRIENTE,
    null                                     AS TIPO_CUENTA_ORIGEN,
    ltc.MOVEXTFCH                            AS FECHA_TRANSACCION,
    NULL                                     AS ID_SESION,
    NVL(ltc.NRO_TICKET,ltc.NRO_AUTORIZACION) AS ID_TRANSACCION,
    ltc.IMPORTE,
    M.DESCRIPCION                            AS MONEDA,
    t.DESCRIPCION_OPERACION                  AS TIPO_OPERACION,
    ltc.TX_INTERNA                           AS TIPO_TRANSACCION_INTERNA,
    PA.DESCRIPCION                           AS PAIS,
    null                                     AS CANAL,
    null                                     AS COD_SERVICIO,
    null                                     AS GLOSA,
    NULL                                     AS  MONTO_CARGO,
    null                                     AS DESTINO_FONDOS,
    e.DESCRIPCION                            AS EMISOR,
    SE.DESCRIPCION                           AS SUCURSAL_EMISOR,
    C.DESCRIPCION                            AS CARTERA,
    ltc.EXTRANJERA,
    GA.DESCRIPCION                           AS GRUPO_AFINIDAD,
    CAST(ltc.ORDEN AS NUMBER)              AS ORDEN_A,
    r.DESCRIPCION                          AS RUBRO,
    TO_CHAR(ltc.ISCOMERCIO)                AS ISCOMERCIO,
    TO_CHAR(ltc.ATM)                       AS ATM,
    NULL                                   AS PARTICIPANTE_DESTINO,
    null                                   AS CUENTA_DESTINO,
    null                                   AS TIPO_CUENTA_DESTINO,
    null                                   AS CI_NIT_DESTINATARIO,
    null                                   AS DESTINATARIO,
    null                                   AS NUM_ORDEN_DESTINATARIO,
    ect.DESCRIPCION                        AS ESTADO_CUENTA,
    ect2.DESCRIPCION                       AS ESTADO_TARJETA,
    CASE
        WHEN ltc.TIPO_AUTORIZACION = 'A' THEN 'APROBADA'
    WHEN ltc.TIPO_AUTORIZACION = 'R' THEN 'RECHAZADA'
    END                                    AS TIPO_AUTORIZACION,
    LTC.MODO_ENTRADA                       AS MODO_ENTRADA,
    NULL                                   AS ESTADO_TRANSACCION,
    ltc.COTIZACION ,
    ltc.CAPTURA ,
    ltc.MANUAL ,
    ltc.IMPORTE_DOLARES ,
    ltc.ADICIONAL ,
    ltc.CODIGO_OPERACION,
    'CEIBO'                                AS FUENTE_ORIGEN
FROM CREDITOPY.LOG_TRANSACC_CR_HIST ltc
    LEFT JOIN CREDITOPY.TIPOS_OPERACIONES t ON ltc.TIPO_OPERACION = t.TIPO_OPERACION
    LEFT JOIN CREDITOPY.ACQUIRERS acq ON ltc.CODIGO_ADQUIRIENTE = acq.ACQUIRER
    LEFT JOIN DEBCREDPY.EMISORES e ON ltc.EMISOR = e.EMISOR
    LEFT JOIN CREDITOPY.GRUPOS_AFINIDAD ga ON ltc.GRUPO_AFINIDAD = ga.GRUPO_AFINIDAD
    LEFT JOIN CREDITOPY.RUBROS r ON ltc.RUBRO = r.RUBRO
    LEFT JOIN DEBCREDPY.SUCURSALES_EMISORES se ON ltc.SUCURSAL_EMISOR = se.SUCURSAL_EMISOR AND ltc.EMISOR = se.EMISOR
    LEFT JOIN DEBCREDPY.CARTERAS c ON ltc.CARTERA = c.CARTERA AND ltc.EMISOR  = c.EMISOR
    LEFT JOIN DEBCREDPY.PAISES pa ON ltc.PAIS = pa.PAIS
    LEFT JOIN DEBCREDPY.MONEDAS m ON ltc.MONEDA = m.CODIGO_DE_MONEDA
    LEFT JOIN DEBCREDPY.PERSONAS p ON p.DOCUMENTO = LTC.NRO_DOCUMENTO
    LEFT JOIN CREDITOPY.ESTADOS_CTAS_TARJ ect ON ltc.ESTADOC = ect.ESTADO
    LEFT JOIN CREDITOPY.ESTADOS_CTAS_TARJ ect2 ON ltc.ESTADOT = ect2.ESTADO
WHERE  ltc.ACQUIRING_INSTITUTION IN ('10000000023', '490508','10000000017','490509')
and MOVEXTFCH >= TO_DATE('01012025', 'ddmmyyyy')
UNION ALL
SELECT
  TO_CHAR(ho.COD_ORIGINANTE)             AS PARTICIPANTE_ORIGINATE,
  null                                   AS NRO_TARJETA,
  TO_CHAR(ho.CUENTA_ORIGEN)              AS NRO_CUENTA_ORIGINANTE,
  TO_CHAR(ho.CI_NIT_ORIGINANTE)          AS DOCUMENTO_ORIGINANTE,
  hp.FULLNAME                            AS ORIGINANTE,
  null                                   AS CODIGO_ADQUIRIENTE,
  null                                   AS ADQUIRIENTE,
  TO_CHAR(ho.TIPO_CUENTA_ORIGEN)         AS TIPO_CUENTA_ORIGEN,
  CAST(ho.INIT_STAMP AS TIMESTAMP)       AS FECHA_TRANSACCION,
  TO_CHAR(ho.ID_SESION)                  AS ID_SESION,
  ho.NUM_ORDEN_ORIGINANTE                AS ID_TRANSACCION,
  CAST(ho.IMPORTE/100 AS NUMBER(18,2))   AS IMPORTE,
  TO_CHAR(ho.COD_MONEDA)                 AS MONEDA,
  TO_CHAR(ho.TIPO_MOVIMIENTO)            AS TIPO_OPERACION,
  TO_CHAR(ho.TIPO_TRANSACCION)           AS TIPO_TRANSACCION_INTERNA,
  TO_CHAR(ho.COD_PAIS_ORIGINANTE)        AS PAIS,
  TO_CHAR(ho.CANAL)                      AS CANAL,
  TO_CHAR(ho.COD_SERVICIO)               AS COD_SERVICIO,
  TO_CHAR(ho.GLOSA)                      AS GLOSA,
  CAST(ho.MONTO_CARGO AS NUMBER)         AS MONTO_CARGO,
  TO_CHAR(ho.DESTINO_FONDOS)             AS DESTINO_FONDOS,
  TO_CHAR(ho.COD_EMPRESA)                AS EMISOR,
  null                                   AS SUCURSAL_EMISOR,
  null                                   AS CARTERA,
  null                                   AS EXTRANJERA,
  null                                   AS GRUPO_AFINIDAD,
  null                                   AS ORDEN_A,
  null                                   AS RUBRO,
  null                                   AS ISCOMERCIO,
  null                                   AS ATM,
  TO_CHAR(ho.COD_DESTINATARIO)           AS PARTICIPANTE_DESTINO,
  TO_CHAR(ho.CUENTA_DESTINO)             AS CUENTA_DESTINO,
  TO_CHAR(ho.TIPO_CUENTA_DESTINO)        AS TIPO_CUENTA_DESTINO,
  TO_CHAR(ho.CI_NIT_DESTINATARIO)        AS CI_NIT_DESTINATARIO,
  hp2.FULLNAME                           AS DESTINATARIO,
  ho.NUM_ORDEN_DESTINATARIO,
  NULL                                   AS ESTADO_CUENTA,
  NULL                                   AS ESTADO_TARJETA,
  NULL                                   AS TIPO_AUTORIZACION,
  NULL                                   AS MODO_ENTRADA,
  ho.estado                              AS ESTADO_TRANSACCION,
  NULL                                   AS COTIZACION ,
  NULL                                   AS CAPTURA ,
  NULL                                   AS MANUAL ,
  NULL                                   AS IMPORTE_DOLARES ,
  NULL                                   AS ADICIONAL ,
  NULL                                     AS CODIGO_OPERACION,
  'SICOOP'                               AS FUENTE_ORIGEN
FROM GATEWAY.H_ORDER@SICOOP ho
    LEFT JOIN GATEWAY.H_PARTY@SICOOP  hp ON ho.COD_ORIGINANTE = hp.CODE
    LEFT JOIN GATEWAY.H_PARTY@SICOOP  hp2 ON ho.COD_DESTINATARIO = hp2.CODE
WHERE (HO.COD_ORIGINANTE IN ('1600','3601') OR HO.COD_DESTINATARIO IN ('1600','3601'))
and CAST(ho.INIT_STAMP AS TIMESTAMP)  >= TO_DATE('01012025', 'ddmmyyyy')

4- Query para limpiar el consolidado de las transacciones de CEIBO y SICOOP

Unificado - Limpio 
SELECT sc.PARTICIPANTE_ORIGINATE,
sc.NRO_CUENTA_ORIGINANTE,
sc.DOCUMENTO_ORIGINANTE,
(SELECT ceibo.ORIGINANTE
FROM AUT_H_ORDER ceibo
WHERE FUENTE_ORIGEN = 'CEIBO' 
AND TRUNC(ceibo.FECHA_TRANSACCION) = TRUNC(sc.FECHA_TRANSACCION)
AND sc.ID_TRANSACCION = ceibo.ID_TRANSACCION
AND ceibo.CODIGO_OPERACION = 0
) AS TITULAR_ORIGINANTE,
(SELECT ceibo.CODIGO_ADQUIRIENTE
FROM AUT_H_ORDER ceibo
WHERE FUENTE_ORIGEN = 'CEIBO' 
AND TRUNC(ceibo.FECHA_TRANSACCION) = TRUNC(sc.FECHA_TRANSACCION)
AND sc.ID_TRANSACCION = ceibo.ID_TRANSACCION
AND ceibo.CODIGO_OPERACION = 0
) AS CODIGO_ADQUIRIENTE,
(SELECT ceibo.ADQUIRIENTE
FROM AUT_H_ORDER ceibo
WHERE FUENTE_ORIGEN = 'CEIBO' 
AND TRUNC(ceibo.FECHA_TRANSACCION) = TRUNC(sc.FECHA_TRANSACCION)
AND sc.ID_TRANSACCION = ceibo.ID_TRANSACCION
AND ceibo.CODIGO_OPERACION = 0
) AS ADQUIRIENTE,
sc.TIPO_CUENTA_ORIGEN,
sc.FECHA_TRANSACCION,
sc.ID_SESION,
sc.ID_TRANSACCION,
sc.IMPORTE,
sc.MONEDA,
sc.TIPO_OPERACION,
(SELECT ceibo.TIPO_OPERACION
FROM AUT_H_ORDER ceibo
WHERE FUENTE_ORIGEN = 'CEIBO' 
AND TRUNC(ceibo.FECHA_TRANSACCION) = TRUNC(sc.FECHA_TRANSACCION)
AND sc.ID_TRANSACCION = ceibo.ID_TRANSACCION
AND ceibo.CODIGO_OPERACION = 0
) AS TIPO_RED,
sc.TIPO_TRANSACCION_INTERNA,
(SELECT ceibo.PAIS
FROM AUT_H_ORDER ceibo
WHERE FUENTE_ORIGEN = 'CEIBO' 
AND TRUNC(ceibo.FECHA_TRANSACCION) = TRUNC(sc.FECHA_TRANSACCION)
AND sc.ID_TRANSACCION = ceibo.ID_TRANSACCION
AND ceibo.CODIGO_OPERACION = 0
) AS PAIS,
sc.CANAL,
sc.COD_SERVICIO,
sc.GLOSA,
sc.MONTO_CARGO,
sc.DESTINO_FONDOS,
sc.EMISOR,
(SELECT ceibo.SUCURSAL_EMISOR
FROM AUT_H_ORDER ceibo
WHERE FUENTE_ORIGEN = 'CEIBO' 
AND TRUNC(ceibo.FECHA_TRANSACCION) = TRUNC(sc.FECHA_TRANSACCION)
AND sc.ID_TRANSACCION = ceibo.ID_TRANSACCION
AND ceibo.CODIGO_OPERACION = 0
) AS SUCURSAL_EMISOR,
(SELECT ceibo.CARTERA
FROM AUT_H_ORDER ceibo
WHERE FUENTE_ORIGEN = 'CEIBO' 
AND TRUNC(ceibo.FECHA_TRANSACCION) = TRUNC(sc.FECHA_TRANSACCION)
AND sc.ID_TRANSACCION = ceibo.ID_TRANSACCION
AND ceibo.CODIGO_OPERACION = 0
) AS CARTERA,
(SELECT ceibo.EXTRANJERA
FROM AUT_H_ORDER ceibo
WHERE FUENTE_ORIGEN = 'CEIBO' 
AND TRUNC(ceibo.FECHA_TRANSACCION) = TRUNC(sc.FECHA_TRANSACCION)
AND sc.ID_TRANSACCION = ceibo.ID_TRANSACCION
AND ceibo.CODIGO_OPERACION = 0
) AS EXTRANJERA,
(SELECT ceibo.GRUPO_AFINIDAD
FROM AUT_H_ORDER ceibo
WHERE FUENTE_ORIGEN = 'CEIBO' 
AND TRUNC(ceibo.FECHA_TRANSACCION) = TRUNC(sc.FECHA_TRANSACCION)
AND sc.ID_TRANSACCION = ceibo.ID_TRANSACCION
AND ceibo.CODIGO_OPERACION = 0
) AS GRUPO_AFINIDAD,
(SELECT ceibo.ORDEN_A
FROM AUT_H_ORDER ceibo
WHERE FUENTE_ORIGEN = 'CEIBO' 
AND TRUNC(ceibo.FECHA_TRANSACCION) = TRUNC(sc.FECHA_TRANSACCION)
AND sc.ID_TRANSACCION = ceibo.ID_TRANSACCION
AND ceibo.CODIGO_OPERACION = 0
) AS ORDEN_A,
(SELECT ceibo.RUBRO
FROM AUT_H_ORDER ceibo
WHERE FUENTE_ORIGEN = 'CEIBO' 
AND TRUNC(ceibo.FECHA_TRANSACCION) = TRUNC(sc.FECHA_TRANSACCION) 
AND sc.ID_TRANSACCION = ceibo.ID_TRANSACCION
AND ceibo.CODIGO_OPERACION = 0
) AS RUBRO,
(SELECT ceibo.ISCOMERCIO
FROM AUT_H_ORDER ceibo
WHERE FUENTE_ORIGEN = 'CEIBO' 
AND TRUNC(ceibo.FECHA_TRANSACCION) = TRUNC(sc.FECHA_TRANSACCION)
AND sc.ID_TRANSACCION = ceibo.ID_TRANSACCION
AND ceibo.CODIGO_OPERACION = 0
) AS ISCOMERCIO,
(SELECT ceibo.ATM
FROM AUT_H_ORDER ceibo
WHERE FUENTE_ORIGEN = 'CEIBO' 
AND TRUNC(ceibo.FECHA_TRANSACCION) = TRUNC(sc.FECHA_TRANSACCION)
AND sc.ID_TRANSACCION = ceibo.ID_TRANSACCION
AND ceibo.CODIGO_OPERACION = 0
) AS ATM,
sc.PARTICIPANTE_DESTINO,
sc.CUENTA_DESTINO,
sc.TIPO_CUENTA_DESTINO,
sc.CI_NIT_DESTINATARIO,
sc.DESTINATARIO,
sc.NUM_ORDEN_DESTINATARIO,
(SELECT ceibo.ESTADO_CUENTA
FROM AUT_H_ORDER ceibo
WHERE FUENTE_ORIGEN = 'CEIBO' 
AND TRUNC(ceibo.FECHA_TRANSACCION) = TRUNC(sc.FECHA_TRANSACCION)
AND sc.ID_TRANSACCION = ceibo.ID_TRANSACCION
AND ceibo.CODIGO_OPERACION = 0
) AS ESTADO_CUENTA,
(SELECT ceibo.ESTADO_TARJETA
FROM AUT_H_ORDER ceibo
WHERE FUENTE_ORIGEN = 'CEIBO' 
AND TRUNC(ceibo.FECHA_TRANSACCION) = TRUNC(sc.FECHA_TRANSACCION)
AND sc.ID_TRANSACCION = ceibo.ID_TRANSACCION
AND ceibo.CODIGO_OPERACION = 0
) AS ESTADO_TARJETA,
(SELECT ceibo.TIPO_AUTORIZACION
FROM AUT_H_ORDER ceibo
WHERE FUENTE_ORIGEN = 'CEIBO' 
AND TRUNC(ceibo.FECHA_TRANSACCION) = TRUNC(sc.FECHA_TRANSACCION)
AND sc.ID_TRANSACCION = ceibo.ID_TRANSACCION
AND ceibo.CODIGO_OPERACION = 0
) AS TIPO_AUTORIZACION,
(SELECT ceibo.MODO_ENTRADA
FROM AUT_H_ORDER ceibo
WHERE FUENTE_ORIGEN = 'CEIBO' 
AND TRUNC(ceibo.FECHA_TRANSACCION) = TRUNC(sc.FECHA_TRANSACCION)
AND sc.ID_TRANSACCION = ceibo.ID_TRANSACCION
AND ceibo.CODIGO_OPERACION = 0
) AS MODO_ENTRADA,
sc.ESTADO_TRANSACCION,
(SELECT ceibo.COTIZACION
FROM AUT_H_ORDER ceibo
WHERE FUENTE_ORIGEN = 'CEIBO' 
AND TRUNC(ceibo.FECHA_TRANSACCION) = TRUNC(sc.FECHA_TRANSACCION)
AND sc.ID_TRANSACCION = ceibo.ID_TRANSACCION
AND ceibo.CODIGO_OPERACION = 0
) AS COTIZACION ,
(SELECT ceibo.CAPTURA
FROM AUT_H_ORDER ceibo
WHERE FUENTE_ORIGEN = 'CEIBO' 
AND TRUNC(ceibo.FECHA_TRANSACCION) = TRUNC(sc.FECHA_TRANSACCION)
AND sc.ID_TRANSACCION = ceibo.ID_TRANSACCION
AND ceibo.CODIGO_OPERACION = 0
) AS CAPTURA ,
(SELECT ceibo.MANUAL
FROM AUT_H_ORDER ceibo
WHERE FUENTE_ORIGEN = 'CEIBO' 
AND TRUNC(ceibo.FECHA_TRANSACCION) = TRUNC(sc.FECHA_TRANSACCION)
AND sc.ID_TRANSACCION = ceibo.ID_TRANSACCION
AND ceibo.CODIGO_OPERACION = 0
) AS MANUAL ,
(SELECT ceibo.IMPORTE_DOLARES
FROM AUT_H_ORDER ceibo
WHERE FUENTE_ORIGEN = 'CEIBO' 
AND TRUNC(ceibo.FECHA_TRANSACCION) = TRUNC(sc.FECHA_TRANSACCION)
AND sc.ID_TRANSACCION = ceibo.ID_TRANSACCION
AND ceibo.CODIGO_OPERACION = 0
) AS IMPORTE_DOLARES ,
(SELECT ceibo.ADICIONAL
FROM AUT_H_ORDER ceibo
WHERE FUENTE_ORIGEN = 'CEIBO' 
AND TRUNC(ceibo.FECHA_TRANSACCION) = TRUNC(sc.FECHA_TRANSACCION)
AND sc.ID_TRANSACCION = ceibo.ID_TRANSACCION
AND ceibo.CODIGO_OPERACION = 0
) AS ADICIONAL ,
'SICOOP/CEIBO' AS FUENTE_ORIGEN
FROM AUT_H_ORDER sc
WHERE FUENTE_ORIGEN = 'SICOOP'

5- Maestro

6-Debito Automático 

  • No labels