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 13 Current »

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,
CASE 
    WHEN ltc.NRO_AUTORIZACION= '0' THEN
        ltc.NRO_TARJETA || '-' || TO_CHAR(ltc.MOVEXTFCH, 'ddmmyyyy')
    ELSE 
        TO_CHAR(ltc.NRO_AUTORIZACION) || '-' || ltc.NRO_TARJETA || '-' || TO_CHAR(ltc.MOVEXTFCH, 'ddmmyyyy')
END    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,
ltc.EMISOR  AS EMISOR_TARJETA,
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,
ltc.NRO_COMERCIO,
E1.DESCRIPCION AS PROCESADORA,
'CEIBO' AS FUENTE_ORIGEN,
ltc.ramo
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 DEBCREDPY.EMISORES e1
ON ltc.EMISOR_PROCESADOR  = e1.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')
AND (ltc.TX_INTERNA != 'OFF COMP' AND ltc.RAMO !='80')
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,
CASE 
    WHEN ltc.NRO_AUTORIZACION= '0' THEN
        ltc.NRO_TARJETA || '-' || TO_CHAR(ltc.MOVEXTFCH, 'ddmmyyyy')
    ELSE 
        TO_CHAR(ltc.NRO_AUTORIZACION) || '-' || ltc.NRO_TARJETA || '-' || TO_CHAR(ltc.MOVEXTFCH, 'ddmmyyyy')
END    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,
ltc.EMISOR  AS EMISOR_TARJETA,
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,
ltc.NRO_COMERCIO,
E1.DESCRIPCION AS PROCESADORA,
'CEIBO' AS FUENTE_ORIGEN,
ltc.ramo
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 DEBCREDPY.EMISORES e1
ON ltc.EMISOR_PROCESADOR  = e1.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')
AND (ltc.TX_INTERNA != 'OFF COMP' AND ltc.RAMO !='80')

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.order_id                            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,
  NULL                                      AS EMISOR_PROCESADOR,
  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,
  NULL                                      AS NRO_COMERCIO,
  NULL                                      AS EMISOR_TARJETA,
  NULL                                     AS PROCESADORA,
  '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')
AND ho.INIT_STAMP  >= TO_DATE('01012025', 'ddmmyyyy')
AND ho.INIT_STAMP  < TO_DATE('01122025', 'ddmmyyyy')

3- Query para importar el Consolidado de CEIBO y SICOOP

Sicoop sin Ceibo 
-- 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,
    CASE 
    WHEN ltc.NRO_AUTORIZACION= '0' THEN
        ltc.NRO_TARJETA || '-' || TO_CHAR(ltc.MOVEXTFCH, 'ddmmyyyy')
    ELSE 
        TO_CHAR(ltc.NRO_AUTORIZACION) || '-' || ltc.NRO_TARJETA || '-' || TO_CHAR(ltc.MOVEXTFCH, 'ddmmyyyy')
	END    AS ID_TRANSACCION,
    ltc.IMPORTE,
    M.DESCRIPCION                            AS MONEDA,
    t.DESCRIPCION_OPERACION                  AS TIPO_RED,
    NULL 						             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,
    LTC.EMISOR                            AS EMISOR_TARJETA,
    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,
    NULL  									AS NUM_ORDEN_ORIGINANTE,
    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,
    LTC.NRO_COMERCIO,
    E1.DESCRIPCION 							AS PROCESADORA,
    LTC.NRO_TICKET ,
    '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 DEBCREDPY.EMISORES e1 ON ltc.EMISOR_PROCESADOR = e1.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')
AND (Ltc.RAMO != 80 AND ltc.TX_INTERNA != 'OFF COMP')
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,
    CASE 
    WHEN ltc.NRO_AUTORIZACION= '0' THEN
        ltc.NRO_TARJETA || '-' || TO_CHAR(ltc.MOVEXTFCH, 'ddmmyyyy')
    ELSE 
        TO_CHAR(ltc.NRO_AUTORIZACION) || '-' || ltc.NRO_TARJETA || '-' || TO_CHAR(ltc.MOVEXTFCH, 'ddmmyyyy')
	END    AS ID_TRANSACCION,
    ltc.IMPORTE,
    M.DESCRIPCION                            AS MONEDA,
    t.DESCRIPCION_OPERACION                  AS TIPO_RED,
    NULL 						             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,
    LTC.EMISOR                            AS EMISOR_TARJETA,
    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,
    NULL  									AS NUM_ORDEN_ORIGINANTE,
    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,
    LTC.NRO_COMERCIO ,
    E1.DESCRIPCION 							AS PROCESADORA,
    LTC.NRO_TICKET,
    '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 DEBCREDPY.EMISORES e1 ON ltc.EMISOR_PROCESADOR = e1.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')
AND (Ltc.RAMO != 80 AND ltc.TX_INTERNA != 'OFF COMP')
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,
  TO_CHAR(ho.ORDER_ID)                   AS ID_TRANSACCION,
  CAST(ho.IMPORTE/100 AS NUMBER(18,2))   AS IMPORTE,
  TO_CHAR(ho.COD_MONEDA)                 AS MONEDA,
  NULL 						             AS TIPO_RED,
  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,
  NULL 									 AS EMISOR_TARJETA,
  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,
  ho.NUM_ORDEN_ORIGINANTE,
  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,
  NULL 									 AS NRO_COMERCIO,
  NULL    								 AS PROCESADORA,
  NULL									 AS NRO_TICKET,
  '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-Debito Automático


Ceibo + Sicoop 
SELECT 
    NULL                                   AS PARTICIPANTE_ORIGINATE,
    pdl.TARJETA_PROC                       AS NRO_TARJETA,
    cu.NUMERO_CUENTA                       AS NRO_CUENTA_ORIGINANTE,
    p.DOCUMENTO                            AS DOCUMENTO_ORIGINANTE,
    p.NOMBRES || ' ' || p.APELLIDOS        AS ORIGINANTE,
    10000000004                            AS CODIGO_ADQUIRIENTE ,
    'COMPRA OFFLINE'                       AS ADQUIRIENTE,
    null                                   AS TIPO_CUENTA_ORIGEN,
    PDL.FECHA_PROCESO                      AS FECHA_TRANSACCION,
    NULL                                   AS ID_SESION,
    PDL.SEC_PRES_DEBAUT || '-' || PDL.SEC_LINEA_DEBAUT     AS ID_TRANSACCION,
    T.IMPORTE,
    'GUARANIES'                            AS MONEDA,
    'DEBITO AUTOMATICO'                    AS TIPO_OPERACION,
    'DEBITO AUTOMATICO'                    AS TIPO_TRANSACCION_INTERNA,
    'PARAGUAY'                             AS PAIS,
    null                                   AS CANAL,
    null                                   AS COD_SERVICIO,
    PDL.GLOSA_RESPUESTA                    AS GLOSA,
    NULL                                   AS MONTO_CARGO,
    null                                   AS DESTINO_FONDOS,
    e.DESCRIPCION                          AS PROCESADORA,
    SE.DESCRIPCION                         AS SUCURSAL_EMISOR,
    NULL                                   AS CARTERA,
    'N'                                    AS EXTRANJERA,
    GA.DESCRIPCION                         AS GRUPO_AFINIDAD,
    null                                   AS ORDEN_A,
    null                                   AS RUBRO,
    0                                      AS ISCOMERCIO,
    c.ATM                                      AS ATM,
    C.DESCRIPCION                          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,
    NULL                                   AS ESTADO_CUENTA,
    NULL                                   AS ESTADO_TARJETA,
    CASE
        WHEN t.RESPUESTA  = 0 THEN 'APROBADA'
        WHEN t.RESPUESTA = 1 THEN 'RECHAZADA'
    END                                    AS TIPO_AUTORIZACION,
    NULL                                   AS MODO_ENTRADA,
    NULL                                   AS ESTADO_TRANSACCION,
    tc.MR_INTERBANCARIO_COMPRA             AS COTIZACION ,
    C.CAPTURA                              AS CAPTURA ,
    NULL                                   AS MANUAL ,
    trunc(T.IMPORTE/tc.MR_INTERBANCARIO_COMPRA,2) AS IMPORTE_DOLARES,
    t2.ADICIONAL,
    t.CODIGO_OPERACION,
    t.COMERCIO                                 AS NRO_COMERCIO,
    CREDITOPY.FN_GET_EMISOR_TARJETA( CREDITOPY.PROCESAR_DEBAUT.Get_Dato_Input_Debaut(
    T.EMISOR_PROCESADOR,
    T.IDFORMATO,
    'I_TARJETA', -- El nombre interno del campo de la tarjeta
    PDL.SEC_PRES_DEBAUT,
    PDL.SEC_LINEA_DEBAUT
  ) ) as EMISOR_TARJETA,
    'DEBITOS_AUTOMATICOS'                  AS FUENTE_ORIGEN
FROM CREDITOPY.PRESENTACIONES_DEBAUT T
LEFT JOIN CREDITOPY.PRESENTACIONES_DEBAUT_LIN pdl ON T.SEC_PRES_DEBAUT = PDL.SEC_PRES_DEBAUT  
LEFT JOIN DEBCREDPY.EMISORES e ON T.EMISOR_PROCESADOR  = e.EMISOR
LEFT JOIN CREDITOPY.COMERCIOS c ON T.COMERCIO = C.COMERCIO
LEFT JOIN CREDITOPY.TARJETAS t2 ON pdl.TARJETA_PROC = t2.TARJETA 
LEFT JOIN creditopy.cuentas cu ON  cu.NUMERO_CUENTA = t2.NUMERO_CUENTA 
LEFT JOIN DEBCREDPY.PERSONAS p ON p.TIPO_DE_DOCUMENTO = 1 AND p.DOCUMENTO = t2.DOCUMENTO 
LEFT JOIN CREDITOPY.GRUPOS_AFINIDAD ga ON t2.GRUPO_AFINIDAD = ga.GRUPO_AFINIDAD
LEFT JOIN DEBCREDPY.SUCURSALES_EMISORES se ON cu.SUCURSAL_EMISOR = se.SUCURSAL_EMISOR AND cu.EMISOR = se.EMISOR
LEFT JOIN DEBCREDPY.tipo_de_cambio tc ON trunc(tc.FECHA_CAMBIO) = trunc(PDL.FECHA_PROCESO)
WHERE T.CODIGO_OPERACION =0
AND PDL.FECHA_PROCESO >= TO_DATE('01012025', 'ddmmyyyy')
AND PDL.FECHA_PROCESO < TO_DATE('01122025', 'ddmmyyyy')

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

Unificado - Limpio 
-- las filas que son 0
SELECT
    sc.PARTICIPANTE_ORIGINATE,
    sc.NRO_CUENTA_ORIGINANTE,
    sc.DOCUMENTO_ORIGINANTE,
    ceibo_cargo.ORIGINANTE AS TITULAR_ORIGINANTE,
    ceibo_cargo.CODIGO_ADQUIRIENTE,
    ceibo_cargo.ADQUIRIENTE,
    sc.TIPO_CUENTA_ORIGEN,
    sc.FECHA_TRANSACCION,
    sc.ID_SESION,
    sc.ID_TRANSACCION,
    sc.IMPORTE,
    sc.MONEDA,
    ceibo_cargo.TIPO_RED,
    sc.TIPO_OPERACION,
    sc.TIPO_TRANSACCION_INTERNA,
    ceibo_cargo.PAIS,
    sc.CANAL,
    sc.COD_SERVICIO,
    sc.GLOSA,
    sc.MONTO_CARGO,
    sc.DESTINO_FONDOS,
    sc.PROCESADORA,
    ceibo_cargo.SUCURSAL_EMISOR,
    ceibo_cargo.CARTERA,
    ceibo_cargo.EXTRANJERA,
    ceibo_cargo.GRUPO_AFINIDAD,
    ceibo_cargo.ORDEN_A,
    ceibo_cargo.RUBRO,
    ceibo_cargo.ISCOMERCIO,
    ceibo_cargo.ATM,
    sc.PARTICIPANTE_DESTINO,
    sc.CUENTA_DESTINO,
    sc.TIPO_CUENTA_DESTINO,
    sc.CI_NIT_DESTINATARIO,
    sc.DESTINATARIO,
    sc.NUM_ORDEN_DESTINATARIO,
    ceibo_cargo.ESTADO_CUENTA,
    ceibo_cargo.ESTADO_TARJETA,
    ceibo_cargo.TIPO_AUTORIZACION,
    ceibo_cargo.MODO_ENTRADA,
    sc.ESTADO_TRANSACCION,
    ceibo_cargo.COTIZACION,
    ceibo_cargo.CAPTURA,
    ceibo_cargo.MANUAL,
    ceibo_cargo.IMPORTE_DOLARES AS IMPORTE_DOLARES,
    ceibo_cargo.ADICIONAL AS ADICIONAL,
    sc.NRO_COMERCIO,
    ceibo_cargo.EMISOR_TARJETA AS EMISOR_TARJETA,
    'SICOOP/CEIBO' AS FUENTE_ORIGEN
FROM POWERBI.AUT_H_ORDER sc
LEFT JOIN POWERBI.AUT_H_ORDER ceibo_cargo ON ceibo_cargo.FUENTE_ORIGEN = 'CEIBO'
       AND ceibo_cargo.FECHA_TRANSACCION >= TRUNC(sc.FECHA_TRANSACCION)
       AND ceibo_cargo.FECHA_TRANSACCION <  TRUNC(sc.FECHA_TRANSACCION) + 1
       AND ceibo_cargo.NRO_TICKET = sc.NUM_ORDEN_ORIGINANTE
       AND ceibo_cargo.CODIGO_OPERACION = 0
       AND ceibo_cargo.TIPO_TRANSACCION_INTERNA <> 'CARGO'
WHERE sc.FUENTE_ORIGEN = 'SICOOP'
AND sc.NUM_ORDEN_ORIGINANTE IS NOT NULL
AND sc.FECHA_TRANSACCION >= TO_DATE('01012025','ddmmyyyy')
AND sc.FECHA_TRANSACCION <  TO_DATE('01112025','ddmmyyyy')
UNION ALL
-- las filas que son 1
SELECT
    sc.PARTICIPANTE_ORIGINATE,
    sc.NRO_CUENTA_ORIGINANTE,
    sc.DOCUMENTO_ORIGINANTE,
    ceibo_rev.ORIGINANTE AS TITULAR_ORIGINANTE,
    ceibo_rev.CODIGO_ADQUIRIENTE,
    ceibo_rev.ADQUIRIENTE,
    sc.TIPO_CUENTA_ORIGEN,
    sc.FECHA_TRANSACCION,
    sc.ID_SESION,
    sc.ID_TRANSACCION,
    sc.IMPORTE,
    sc.MONEDA,
    ceibo_rev.TIPO_RED,
    sc.TIPO_OPERACION,
    sc.TIPO_TRANSACCION_INTERNA,
    ceibo_rev.PAIS,
    sc.CANAL,
    sc.COD_SERVICIO,
    sc.GLOSA,
    sc.MONTO_CARGO,
    sc.DESTINO_FONDOS,
    sc.PROCESADORA,
    ceibo_rev.SUCURSAL_EMISOR,
    ceibo_rev.CARTERA,
    ceibo_rev.EXTRANJERA,
    ceibo_rev.GRUPO_AFINIDAD,
    ceibo_rev.ORDEN_A,
    ceibo_rev.RUBRO,
    ceibo_rev.ISCOMERCIO,
    ceibo_rev.ATM,
    sc.PARTICIPANTE_DESTINO,
    sc.CUENTA_DESTINO,
    sc.TIPO_CUENTA_DESTINO,
    sc.CI_NIT_DESTINATARIO,
    sc.DESTINATARIO,
    sc.NUM_ORDEN_DESTINATARIO,
    ceibo_rev.ESTADO_CUENTA,
    ceibo_rev.ESTADO_TARJETA,
    ceibo_rev.TIPO_AUTORIZACION,
    ceibo_rev.MODO_ENTRADA,
    sc.ESTADO_TRANSACCION,
    ceibo_rev.COTIZACION,
    ceibo_rev.CAPTURA,
    ceibo_rev.MANUAL,
    ceibo_rev.IMPORTE_DOLARES AS IMPORTE_DOLARES,
    ceibo_rev.ADICIONAL AS ADICIONAL,
    sc.NRO_COMERCIO,
    ceibo_rev.EMISOR_TARJETA AS EMISOR_TARJETA,
    'SICOOP/CEIBO' AS FUENTE_ORIGEN
FROM POWERBI.AUT_H_ORDER sc
LEFT JOIN POWERBI.AUT_H_ORDER ceibo_rev
        ON ceibo_rev.FUENTE_ORIGEN = 'CEIBO'
       AND ceibo_rev.FECHA_TRANSACCION >= TRUNC(sc.FECHA_TRANSACCION)
       AND ceibo_rev.FECHA_TRANSACCION <  TRUNC(sc.FECHA_TRANSACCION) + 1
       AND ceibo_rev.NRO_TICKET = sc.NUM_ORDEN_DESTINATARIO
       AND ceibo_rev.CODIGO_OPERACION = 1
       AND ceibo_rev.TIPO_TRANSACCION_INTERNA <> 'REVCARGO'
WHERE sc.FUENTE_ORIGEN = 'SICOOP'
AND sc.NUM_ORDEN_ORIGINANTE IS NULL
AND sc.FECHA_TRANSACCION >= TO_DATE('01012025','ddmmyyyy')
AND sc.FECHA_TRANSACCION <  TO_DATE('01112025','ddmmyyyy')

6- Maestro

Unificado - Limpio 
SELECT   hos.PARTICIPANTE_ORIGINATE,
CAST(hos.NRO_CUENTA_ORIGINANTE AS VARCHAR2(255)) AS NRO_CUENTA_ORIGINANTE,
hos.DOCUMENTO_ORIGINANTE,
dp.PARTICIPANTE_UNIFICADO                           AS TITULAR_ORIGINANTE,
null                                   AS CODIGO_ADQUIRIENTE,
null                                   AS ADQUIRIENTE,
hos.TIPO_CUENTA_ORIGEN,
hos.FECHA_TRANSACCION,
hos.ID_SESION,
CAST(hos.ID_TRANSACCION  AS VARCHAR2(255)) AS ID_TRANSACCION,
hos.IMPORTE,
hos.MONEDA,
hos.TIPO_OPERACION,
NULL                                    AS TIPO_RED,
hos.TIPO_TRANSACCION_INTERNA,
hos.PAIS,
hos.CANAL,
hos.COD_SERVICIO,
hos.GLOSA,
hos.MONTO_CARGO,
hos.DESTINO_FONDOS,
de.EMISOR_UNIFICADO                     AS EMISOR_TARJETA,
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,
hos.PARTICIPANTE_DESTINO,
hos.CUENTA_DESTINO,
hos.TIPO_CUENTA_DESTINO,
hos.CI_NIT_DESTINATARIO,
dp1.PARTICIPANTE_UNIFICADO AS DESTINATARIO,
hos.NUM_ORDEN_DESTINATARIO,
NULL                                     AS ESTADO_CUENTA,
NULL                                     AS ESTADO_TARJETA,
NULL                                     AS TIPO_AUTORIZACION,
NULL                                     AS MODO_ENTRADA,
hos.ESTADO_TRANSACCION                   AS ESTADO_TRANSACCION,
NULL                                     AS COTIZACION ,
NULL                                     AS CAPTURA ,
NULL                                     AS MANUAL ,
NULL                                     AS IMPORTE_DOLARES ,
NULL                                     AS ADICIONAL ,
HOS.PROCESADORA ,
hos.FUENTE_ORIGEN
FROM H_ORDER_SAUT hos 
LEFT JOIN POWERBI.DICCIONARIO_EMISORES de ON hos.EMISOR_TARJETA =de.COD_EMISOR 
LEFT JOIN POWERBI.DICCIONARIO_PARTICIPANTES dp ON dp.COD_PARTICIPANTE = hos.PARTICIPANTE_ORIGINATE 
LEFT JOIN POWERBI.DICCIONARIO_PARTICIPANTES dp1 ON dp1.COD_PARTICIPANTE = hos.PARTICIPANTE_DESTINO 
UNION ALL 
SELECT 
NULL                                      AS PARTICIPANTE_ORIGINATE,
CAST(ass.NRO_CUENTA_ORIGINANTE AS VARCHAR2(255)) AS NRO_CUENTA_ORIGINANTE,
ass.DOCUMENTO_ORIGINANTE,
ass.ORIGINANTE                            AS TITULAR_ORIGINANTE,
ass.CODIGO_ADQUIRIENTE ,
ass.ADQUIRIENTE,
null                                      AS TIPO_CUENTA_ORIGEN,
ass.FECHA_TRANSACCION,
NULL                                      AS ID_SESION,
ass.ID_TRANSACCION, 
ass.IMPORTE,
ass.MONEDA,
NULL                                      AS TIPO_OPERACION,
ass.TIPO_OPERACION                        AS TIPO_RED,
ass.TIPO_TRANSACCION_INTERNA,
ass.PAIS,
null                                      AS CANAL,
null                                      AS COD_SERVICIO,
null                                      AS GLOSA,
NULL                                      AS MONTO_CARGO,
null                                      AS DESTINO_FONDOS,
de.EMISOR_UNIFICADO                       AS EMISOR_TARJETA,
ass.SUCURSAL_EMISOR,
ass.CARTERA,
ass.EXTRANJERA,
ass.GRUPO_AFINIDAD,
CAST(ass.ORDEN_A AS VARCHAR2(255)) AS ORDEN_A,
ass.RUBRO,
CAST(ass.ISCOMERCIO AS VARCHAR2(255)) AS ISCOMERCIO,
ass.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,
ass.ESTADO_CUENTA,
ass.ESTADO_TARJETA,
ass.TIPO_AUTORIZACION,
CAST(ass.MODO_ENTRADA AS VARCHAR2(255)) AS MODO_ENTRADA,
NULL                                      AS ESTADO_TRANSACCION,
ass.COTIZACION ,
ass.CAPTURA ,
ass.MANUAL ,
ass.IMPORTE_DOLARES ,
ass.ADICIONAL ,
ASS.PROCESADORA ,
ass.FUENTE_ORIGEN
FROM POWERBI."Aut_s_Sicoop" ass
LEFT JOIN POWERBI.DICCIONARIO_EMISORES de ON ass.EMISOR_TARJETA =de.COD_EMISOR 
UNION ALL
SELECT 
sc.PARTICIPANTE_ORIGINATE,
CAST(sc.NRO_CUENTA_ORIGINANTE AS VARCHAR2(255)) AS NRO_CUENTA_ORIGINANTE,
sc.DOCUMENTO_ORIGINANTE,
DP.PARTICIPANTE_UNIFICADO AS TITULAR_ORIGINANTE,
sc.CODIGO_ADQUIRIENTE ,
sc.ADQUIRIENTE,
sc.TIPO_CUENTA_ORIGEN,
sc.FECHA_TRANSACCION,
sc.ID_SESION,
sc.ID_TRANSACCION, 
sc.IMPORTE,
sc.MONEDA,
sc.TIPO_OPERACION,
sc.TIPO_RED,
sc.TIPO_TRANSACCION_INTERNA,
sc.PAIS,
sc.CANAL,
sc.COD_SERVICIO,
sc.GLOSA,
sc.MONTO_CARGO,
sc.DESTINO_FONDOS,
de.EMISOR_UNIFICADO  AS EMISOR_TARJETA,
sc.SUCURSAL_EMISOR,
sc.CARTERA,
sc.EXTRANJERA,
sc.GRUPO_AFINIDAD,
CAST(sc.ORDEN_A AS VARCHAR2(255)) AS ORDEN_A,
sc.RUBRO,
CAST(sc.ISCOMERCIO AS VARCHAR2(255)) AS ISCOMERCIO,
CAST(sc.ATM AS VARCHAR2(255)) AS ATM,
sc.PARTICIPANTE_DESTINO,
sc.CUENTA_DESTINO,
sc.TIPO_CUENTA_DESTINO,
sc.CI_NIT_DESTINATARIO,
DP1.PARTICIPANTE_UNIFICADO AS DESTINATARIO,
sc.NUM_ORDEN_DESTINATARIO,
sc.ESTADO_CUENTA,
sc.ESTADO_TARJETA,
sc.TIPO_AUTORIZACION,
CAST(sc.MODO_ENTRADA AS VARCHAR2(255)) AS MODO_ENTRADA,
sc.ESTADO_TRANSACCION,
sc.COTIZACION ,
sc.CAPTURA ,
sc.MANUAL ,
sc.IMPORTE_DOLARES ,
sc.ADICIONAL ,
SC.PROCESADORA ,
sc.FUENTE_ORIGEN
FROM UNION_AUT_H_ORDER sc
LEFT JOIN POWERBI.DICCIONARIO_EMISORES de ON sc.EMISOR_TARJETA = de.COD_EMISOR 
LEFT JOIN POWERBI.DICCIONARIO_PARTICIPANTES dp ON dp.COD_PARTICIPANTE = sc.PARTICIPANTE_ORIGINATE 
LEFT JOIN POWERBI.DICCIONARIO_PARTICIPANTES dp1 ON dp1.COD_PARTICIPANTE = sc.PARTICIPANTE_DESTINO 
UNION ALL
SELECT 
da.PARTICIPANTE_ORIGINATE,
CAST(da.NRO_CUENTA_ORIGINANTE AS VARCHAR2(255)) AS NRO_CUENTA_ORIGINANTE,
da.DOCUMENTO_ORIGINANTE,
da.ORIGINANTE AS TITULAR_ORIGINANTE,
da.CODIGO_ADQUIRIENTE ,
da.ADQUIRIENTE,
da.TIPO_CUENTA_ORIGEN,
da.FECHA_TRANSACCION,
da.ID_SESION,
da.ID_TRANSACCION, 
da.IMPORTE,
da.MONEDA,
da.TIPO_OPERACION,
NULL AS TIPO_RED,
da.TIPO_TRANSACCION_INTERNA,
da.PAIS,
da.CANAL,
da.COD_SERVICIO,
CAST(da.GLOSA AS VARCHAR2(255))AS GLOSA,
CAST(da.MONTO_CARGO AS float)AS MONTO_CARGO,
da.DESTINO_FONDOS,
de.EMISOR_UNIFICADO  AS EMISOR_TARJETA,
da.SUCURSAL_EMISOR,
da.CARTERA,
da.EXTRANJERA,
da.GRUPO_AFINIDAD,
CAST(da.ORDEN_A AS VARCHAR2(255)) AS ORDEN_A,
da.RUBRO,
CAST(da.ISCOMERCIO AS VARCHAR2(255)) AS ISCOMERCIO,
CAST(da.ATM AS VARCHAR2(255)) AS ATM,
da.PARTICIPANTE_DESTINO,
da.CUENTA_DESTINO,
da.TIPO_CUENTA_DESTINO,
da.CI_NIT_DESTINATARIO,
da.DESTINATARIO,
da.NUM_ORDEN_DESTINATARIO,
da.ESTADO_CUENTA,
da.ESTADO_TARJETA,
da.TIPO_AUTORIZACION,
da.MODO_ENTRADA,
da.ESTADO_TRANSACCION,
da.COTIZACION ,
da.CAPTURA ,
da.MANUAL ,
da.IMPORTE_DOLARES ,
da.ADICIONAL ,
da.PROCESADORA ,
da.FUENTE_ORIGEN
FROM "debitos_automaticos" da
LEFT JOIN POWERBI.DICCIONARIO_EMISORES de ON da.EMISOR_TARJETA =de.COD_EMISOR
  • No labels