Page History

-- 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                             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(ltc.NRO_TICKET,ltc.NRO_AUTORIZACION) AS ID_TRANSACCION,
ltc.IMPORTE,
M.DESCRIPCION                              AS MONEDA,
t.DESCRIPCION_OPERACION                  AS TIPO_OPERACION,
ltc.TX_INTERNA                             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 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                            ,
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')

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'