Cesar Rivera Skill Set: Sample Oracle Query

Sample Query for #1 - A Sample Query I do

SELECT p4, mensaje, 'Error en Etapa 1' x

FROM (SELECT l.nro_ejecucion,

DENSE_RANK () OVER (ORDER BY l.nro_ejecucion DESC) n,

DECODE

(l.p4,

'NPA', '<p class="style12">Anulación no posee su pago correspondiente.</p>'

|| '<table border="0" class="style12">'

|| '<tr><td>Fecha Transacción:</td><td><b>'

|| SUBSTR (l.mensaje,

INSTR (l.mensaje, 'F:') + LENGTH ('F:'),

INSTR (SUBSTR (mensaje,

INSTR (l.mensaje, 'F:')

+ LENGTH ('F:')

','

)

- 1

)

|| '</b></td><td> </td>'

|| '<td>Tipo Transacción:</td><td><b>'

|| SUBSTR (mensaje,

INSTR (mensaje, 'R1:') + LENGTH ('R1:'),

INSTR (SUBSTR (mensaje,

INSTR (l.mensaje, 'R1:')

+ LENGTH ('R1:')

','

)

- 1

)

|| '</b></td></tr>'

|| '<tr><td>Servicio: </td><td><b>'

|| SUBSTR (mensaje,

INSTR (mensaje, 'S:') + LENGTH ('S:'),

INSTR (SUBSTR (mensaje,

INSTR (mensaje, 'S:')

+ LENGTH ('S:')

','

)

- 1

)

|| '</b></td><td> </td>'

|| '<td>Traza: </td><td><b>'

|| SUBSTR (mensaje,

INSTR (mensaje, 'A:') + LENGTH ('A:'),

INSTR (SUBSTR (mensaje,

INSTR (mensaje, 'A:')

+ LENGTH ('A:')

','

)

- 1

)

|| '</b></td></tr>'

|| '<tr><td>Traza que anula: </td><td colspan=4><b>'

|| SUBSTR (mensaje,

INSTR (mensaje, 'T:') + LENGTH ('T:'),

INSTR (SUBSTR (mensaje,

INSTR (mensaje, 'T:')

+ LENGTH ('T:')

','

)

- 1

)

|| '</b></td></tr></table>',

'D1X', ' '

|| '<table border="0"><tr><td class="style12">'

|| 'Campo <b>Tipo Registro</b> no posee un valor válido ( <b>'

|| l.p3

|| '</b> ). Valores aceptables: <b>D1, D2, D3, D4</b>',

'NUME1', ' '

|| '<table border="0"><tr><td class="style12">'

|| 'Campo <b>Tipo de Transacción</b> no es numérico ( <b>'

|| l.p3

|| '</b> ).'

|| '</td></tr></table>',

'NUME2', ' '

|| '<table border="0"><tr><td class="style12">'

|| 'Campo <b>Tipo de Transacción</b> debe ser mayor a cero ( <b>'

|| l.p3

|| '</b> ).'

|| '</td></tr></table>',

'FECE1', ' '

|| '<table border="0"><tr><td class="style12">'

|| 'Campo <b>Fecha Transacción</b> No Válida ( <b>'

|| l.p3

|| '</b> ).'

|| '</td></tr></table>',

'FEMA1', ' '

|| '<table border="0"><tr><td class="style12">'

|| 'Campo <b>Fecha Transacción</b>( <b>'

|| l.p3

|| '</b> ) debe ser menor a la fecha recaudación ( <b>'

|| l.p2

|| '</b> ).'

|| '</td></tr></table>',

'HORA1', ' '

|| '<table border="0"><tr><td class="style12">'

|| 'Campo <b>Hora Transacción</b> No Válida ( <b>'

|| l.p3

|| '</b> ).'

|| '</td></tr></table>',

'NUME2', ' '

|| '<table border="0"><tr><td class="style12">'

|| 'Campo <b>Monto Transacción</b> No Válido ( <b>'

|| l.p3

|| '</b> ).'

|| '</td></tr></table>',

'DIF2', ' '

|| '<table border="0"><tr><td class="style12">'

|| '<p>Diferencias entre monto indicado en la cabecera por el campo <b>Monto Recaudado</b> y el monto calculado en el detalle.</p>'

|| '<table border="0" class="style12">'

|| '<tr><td class="style12">Monto recaudado según cabecera:</td>'

|| '<td class="style12" align=right><b>'

|| TO_CHAR (l.n1, '999,999,999,999.99')

|| '</b></td>'

|| '</tr><tr><td class="style12">Monto recaudado según detalle:</td>'

|| '<td class="style12" align=right><b>'

|| TO_CHAR (l.n2, '999,999,999,999.99')

|| '</b></td></tr></table>'

|| '</td></tr></table>',

'DIF4', ' '

|| '<table border="0"><tr><td class="style12">'

|| '<p>Diferencias entre monto indicado en la cabecera por el campo <b>Monto Aplicado</b> y el monto calculado en el detalle.</p>'

|| '<table border="0" class="style12">'

|| '<tr><td class="style12">Monto Aplicado según cabecera:</td>'

|| '<td class="style12" align=right><b>'

|| TO_CHAR (l.n1, '999,999,999,999.99')

|| '</b></td>'

|| '</tr><tr><td class="style12">Monto Aplicado según detalle:</td>'

|| '<td class="style12" align=right><b>'

|| TO_CHAR (l.n2, '999,999,999,999.99')

|| '</b></td></tr></table>'

|| '</td></tr></table>',

'DIF8', ' ',

'<table border="0"><tr><td class="style12">'

|| l.mensaje

|| '</td></tr></table>'

) mensaje,

FROM LOG l,

(SELECT fecha_proceso, archivo, nombre_nuevo,

'Error formato Archivo (Carga)' id_proceso,

nro_ejecucion

FROM (SELECT ROW_NUMBER () OVER (PARTITION BY da.archivo, da.nombre_nuevo ORDER BY da.estatus_split,

NVL (da.estatus_carga, 'E') DESC) n,

da.*

FROM ctcw_batch_archivos a,

ctcw_batch_archivos_det d,

ctcw_batch_archivos_det da

WHERE a.fecha_archivo = ('20140207')

AND a.fecha_archivo = d.fecha_archivo

AND a.bin = d.bin

AND d.bin = SUBSTR ('123456', -6)

AND a.nro_ejecucion = d.nro_ejecucion

AND a.fecha_proceso = d.fecha_proceso

AND a.archivo = d.archivo

AND d.estatus_carga IS NULL

AND d.archivo = da.archivo(+)

AND d.nombre_nuevo = da.nombre_nuevo(+))

WHERE n = 1 AND estatus_carga IS NULL) a

WHERE l.id_proceso = 200

AND l.fecha = a.fecha_proceso

AND l.p1 = SUBSTR (a.nombre_nuevo, 1, 23)

AND archivo = '123456_20140107_00'

AND l.tipo_mensaje = 'ERR')

WHERE n = 1

UNION

SELECT p4,

'<table><tr><td class="style12">'

|| REPLACE (mensaje, CHR (10), '<br>')

|| '</td></tr></table>' mensaje,

'Error en Etapa 2' x

FROM (SELECT l.nro_ejecucion,

DENSE_RANK () OVER (ORDER BY l.nro_ejecucion DESC) n,

l.mensaje mensaje, p4

FROM LOG l,

(SELECT fecha_proceso, archivo, NULL nombre_nuevo,

'Error Split' id_proceso, nro_ejecucion

FROM (SELECT ROW_NUMBER () OVER (PARTITION BY a.archivo ORDER BY a.estatus_split DESC)

a.*

FROM ctcw_batch_archivos a

WHERE a.fecha_archivo = ('20140207')

AND a.bin = SUBSTR ('123456', -6)

AND a.estatus_split = 'ERR')

WHERE n = 1) a

WHERE l.id_proceso = 401

AND l.fecha = a.fecha_proceso

AND l.p1 = SUBSTR (a.archivo, 1, 23)

AND archivo = '123456_20140207_00'

AND l.tipo_mensaje = 'ERR'

AND l.mensaje NOT LIKE '%@%')

WHERE n = 1

UNION

SELECT p4, mensaje, 'Error en Etapa 3' x

FROM (SELECT l.nro_ejecucion,

DENSE_RANK () OVER (ORDER BY l.nro_ejecucion DESC) n,

'<p class="cbra_b">'

|| DECODE

(l.n1,

1, 'El pago está reversado en línea.',

2, 'El pago fue rechazado por CTC en línea.',

3, 'Número de Aprobación no coincide.',

4, 'Pago anulado en línea.',

6, 'Pago ya estaba conciliado.',

5, 'Pago no posee <b>pcode</b> válido en línea.',

7, 'Pago duplicado en línea.',

8, 'Pago no aparece registrado en línea.',

9, 'Error en Conciliación, revisar CTC.',

l.mensaje

)

|| '</p><table border="0" class="style12">'

|| '<tr><td>Fecha Transacción:</td><td><b>'

|| SUBSTR (l.mensaje,

INSTR (l.mensaje, 'R1:') + LENGTH ('F1:'),

INSTR (SUBSTR (mensaje,

INSTR (l.mensaje, 'R1:')

+ LENGTH ('A1:')

','

)

- 1

)

|| '</b></td></tr>'

|| '<tr><td>Servicio: </td><td><b>'

|| SUBSTR (mensaje,

INSTR (mensaje, 'S:') + LENGTH ('S:'),

INSTR (SUBSTR (mensaje,

INSTR (mensaje, 'S:')

+ LENGTH ('R:')

','

)

- 1

)

|| '</b></td><td> </td>'

|| '<td>Traza: </td><td><b>'

|| SUBSTR (mensaje,

INSTR (mensaje, 'A:') + LENGTH ('A:'),

INSTR (SUBSTR (mensaje,

INSTR (mensaje, 'A:')

+ LENGTH ('S:')

','

)

- 1

)

|| '</b></td></tr>'

|| '<tr><td>Modalidad: </td><td><b>'

|| SUBSTR (mensaje,

INSTR (mensaje, 'M:') + LENGTH ('M:'),

INSTR (SUBSTR (mensaje,

INSTR (mensaje, 'M:')

+ LENGTH ('T:')

','

)

- 1

)

|| '</b></td><td> </td>'

|| '<td>Empresa: </td><td><b>'

|| SUBSTR (mensaje,

INSTR (mensaje, 'E:') + LENGTH ('E:'),

INSTR (SUBSTR (mensaje,

INSTR (mensaje, 'E:')

+ LENGTH ('M:')

','

)

- 1

)

|| '</b></td></tr></table><br/>' mensaje,

FROM LOG l,

(SELECT fecha_proceso, archivo, nombre_nuevo,

'Error formato Archivo (Carga)' id_proceso,

nro_ejecucion

FROM (SELECT ROW_NUMBER () OVER (PARTITION BY da.archivo, da.nombre_nuevo ORDER BY da.estatus_split,

NVL (da.estatus_carga, 'E'),

da.nro_ejecucion DESC) n,

da.*

FROM ctcw_batch_archivos a,

ctcw_batch_archivos_det d,

ctcw_batch_archivos_det da

WHERE a.fecha_archivo = ('20140207')

AND a.fecha_archivo = d.fecha_archivo

AND a.bin = d.bin

AND d.bin = SUBSTR ('0000123456', -6)

AND a.nro_ejecucion = d.nro_ejecucion

AND a.fecha_proceso = d.fecha_proceso

AND a.archivo = d.archivo

AND d.estatus_carga IS NULL

AND d.archivo = da.archivo(+)

AND d.nombre_nuevo = da.nombre_nuevo(+))

WHERE n = 1 AND estatus_carga IS NULL) a

WHERE l.id_proceso = 402

AND l.fecha =

a.fecha_proceso

AND l.p1 = SUBSTR (a.nombre_nuevo, 1, 23)

AND archivo = '123456_20140207_00'

AND l.tipo_mensaje = 'ERR'

AND l.p4 = 'PRE')

WHERE n = 1;

Cesar Rivera Skill Set

Friday, February 7, 2014

Sample Oracle Query

No comments:

Post a Comment