0.Introducción
Recordemos que creamos la vista XIMO_VALORS que nos une tanto los valores temporales (VATE) como los definitivos (VALO)
CREATE OR REPLACE VIEW XIMO_SCHM.XIMO_VALORS
AS
SELECT VATE.IDENTIFICADOR_VATE,
LPAD(VALO.ANYO_CARGO_CARG,4,'0')||LPAD(VALO.ID_ORGA ,2,'0') ||
LPAD(VALO.ID_SUBO,5,'0') || VALO.COD_CONC || LPAD(VALO.EMISION_CARG,2,'0') ||
VALO.COD_TVAL || LPAD(VALO.NUMERO_VALO,6,'0') AS IDENTIFICADOR_VALO,
VATE.ID_EPER,
VALO.ID_EPER AS ID_EPER_VALOR ,
TPDL.ID_TPDL,
OT.ID_EPER_OT,
EPER.ID_PERS,
PERS.NIF_PERG, PERS.NOMBRE_PERG,
NVL(VATE.COD_TVAL,VALO.COD_TVAL) AS COD_TVAL,
NVL(VATE.COD_CONC,VALO.COD_CONC) AS COD_CONC,
VATE.TIPO_VATE,
VATE.ESTADO_VATE,
TESV.*,
TSEV.*,
TFVA.*,
SVAL.DESCR_SVAL,
VATE.COD_USUA,
NVL(VATE.FECHA_GRABACION_VATE,
NVL(VALO.FECHA_CARGA_VALO,
TO_DATE(TO_CHAR(NVL(VATE.EJERCICIO_VATE,2000))||'-01-01','YYYY-MM-DD'))) AS FECHA_GRABACION_VATE,
VATE.PERIODO_VATE,
VALO.ANYO_CARGO_CARG, VALO.EMISION_CARG, VALO.NUMERO_VALO,
VALO.ID_CARG, VALO.NUMERO_CARG,
NVL(VATE.OBJETO_TRIB_VATE,VALO.OBJETO_TRIB_VALO) AS OBJETO_TRIB_VATE,
NVL(VATE.DEUDA_VATE,VALO.IMPORTE_VALO) AS IMPORTE,
VATE.ID_EPER_ORIG_VATE,
NVL(VATE.NUM_EXP_LID_VATE, VALO.NUM_EXP_LID_VALO) AS NUM_EXP_LID_VATE,
NVL(VATE.ID_CSUB,VALO.ID_CSUB) AS ID_CSUB,
CSUB.NOMBRE_PARA_SUBO_CSUB,
VALO.ID_REGE
--SELECT COUNT(*)
FROM OPS$GTTORA.VATE_VALORES_TEMPORALES VATE -- NUM_EXP_LID_VATE, ID_EPER_ORIG_VATE-> ID_TPDL??, IDENTIFIADOR_VATE, ID_EPER_VALO-> VALO.ID_EPER,COPD_CONC,
FULL OUTER JOIN OPS$GTTORA.VALO_VALORES VALO ON VALO.ID_EPER=VATE.ID_EPER_VALOR -- ID_CARG, NUMERO_VALO!!!
LEFT JOIN OPS$GTTORA.TPDL_TP_DETALLE_LIQUIDACION TPDL ON TPDL.ID_TPDL =VATE.ID_EPER_ORIG_VATE
LEFT JOIN OPS$GTTORA.OT_OBJETO_TRIBUTARIO OT ON OT.ID_EPER_OT =VATE.ID_EPER_ORIG_VATE
LEFT JOIN OPS$GTTORA.EPER_ELEMENTOS_PERSONA EPER ON EPER.ID_EPER = GREATEST (NVL(VATE.ID_EPER,-1), NVL(VALO.ID_EPER,-1))
LEFT JOIN OPS$GTTORA.CSUB_CONCEPTOS_SUBORGANISMO CSUB ON CSUB.ID_CSUB = GREATEST (NVL(VATE.ID_CSUB,-1), NVL(VALO.ID_CSUB ,-1))
LEFT JOIN OPS$GTTORA.PERS_PERSONAS pers ON pers.ID_PERS =EPER.ID_PERS
LEFT JOIN OPS$GTTORA.TESV_TIPOS_ESTADO_VALOR TESV ON TESV.COD_TESV =VALO.COD_TESV
LEFT JOIN OPS$GTTORA.TSEV_TSUBE_VALOR TSEV ON TSEV.COD_TSEV =VALO.COD_TSEV
LEFT JOIN OPS$GTTORA.TFVA_TIPOS_FORMATO_VALOR TFVA ON TFVA.COD_TFVA =VALO.COD_TFVA
LEFT JOIN OPS$GTTORA.SVAL_SUBTIPO_VALORES SVAL ON SVAL.COD_SVAL =VALO.COD_SVAL
;
Veamos los distintos estados que tenemos en nuestra base de datos, para ello ejecutamos esta consulta
SELECT DISTINCT count(*), VALORS.ESTADO_VATE, VALORS.COD_TVAL, VALORS.COD_TESV, VALORS.DESCR_TESV, VALORS.COD_TSEV , VALORS.DESCR_TSEV
FROM XIMO_SCHM.XIMO_VALORS VALORS
GROUP BY VALORS.ESTADO_VATE, VALORS.COD_TVAL, VALORS.COD_TESV, VALORS.DESCR_TSEV, VALORS.COD_TSEV , VALORS.DESCR_TESV
ORDER BY VALORS.ESTADO_VATE, VALORS.COD_TESV , VALORS.COD_TSEV,VALORS.COD_TVAL ;
Y nos sale
COUNT(*)|ESTADO_VATE|COD_TVAL|COD_TESV|DESCR_TESV |COD_TSEV|DESCR_TSEV | --------+-----------+--------+--------+---------------------+--------+------------------------------+2111|E |A | | | | | 1862|E |L | | | | | 464|E |R | | | | | 137|G |A | | | | | 3102|G |L | | | | |860|I |A | | | | | 252|I |L | | | | |18|P |A | | | | | 166|P |L | | | | |815|R |R |B |Baja |C |Comunicada no facturada | 5|R |R |B |Baja |DT |Devuelto Total | 18|R |R |B |Baja |F |por Fallido no facturado | 168|R |R |B |Baja |Om |por otros motivos no facturada| 212|R |R |B |Baja |V |En Voluntaria no facturada | 106|R |R |B |Baja |VO |Voluntaria Otros Motivos | 13|R |R |CE |Cobrado en Ejecutiva |DP |Devuelto Parcial | 16|R |R |CE |Cobrado en Ejecutiva |DT |Devuelto Total | 9499|R |R |CE |Cobrado en Ejecutiva |O |Confirmado no facturado | 442|R |R |CE |Cobrado en Ejecutiva |OD |Confirmado por Domiciliación | 4|R |R |CV |Cobrado en Voluntaria|DP |Devuelto Parcial | 391|R |R |CV |Cobrado en Voluntaria|DT |Devuelto Total | 33339|R |R |CV |Cobrado en Voluntaria|O |Confirmado no facturado | 105260|R |R |CV |Cobrado en Voluntaria|OD |Confirmado por Domiciliación | 62|R |R |CV |Cobrado en Voluntaria|OR |Confirmado por Reposición | 1397|R |R |E |En Ejecutiva |Con |Consolidar | 21|R |R |E |En Ejecutiva |EMN |Emitida Notificación | 183|R |R |E |En Ejecutiva |Frc |Fraccionado | 3742|R |R |E |En Ejecutiva |Not |Notificación Efectiva | 1|R |R |E |En Ejecutiva |Pem |Pendiente de Emitir | 55|R |R |E |En Ejecutiva |SC |Suspensión Cautelar | 18034|R |R |V |En Voluntaria |Dom |Emitido por Domiciliación | 1389|R |R |V |En Voluntaria |Emi |Emitido y no Domiciliado | 24|R |R |V |En Voluntaria |Frc |Fraccionado | 1|R |R |V |En Voluntaria |SC |Suspensión Cautelar | 277|V |L |B |Baja |C |Comunicada no facturada | 3|V |L |B |Baja |DT |Devuelto Total | 106|V |L |B |Baja |Om |por otros motivos no facturada| 358|V |L |B |Baja |V |En Voluntaria no facturada | 2|V |L |B |Baja |VO |Voluntaria Otros Motivos | 3|V |L |CE |Cobrado en Ejecutiva |DP |Devuelto Parcial | 17|V |L |CE |Cobrado en Ejecutiva |DT |Devuelto Total | 1|V |A |CE |Cobrado en Ejecutiva |O |Confirmado no facturado | 2610|V |L |CE |Cobrado en Ejecutiva |O |Confirmado no facturado | 120|V |L |CE |Cobrado en Ejecutiva |OD |Confirmado por Domiciliación | 32|V |A |CV |Cobrado en Voluntaria|DP |Devuelto Parcial | 1|V |L |CV |Cobrado en Voluntaria|DP |Devuelto Parcial | 128|V |A |CV |Cobrado en Voluntaria|DT |Devuelto Total | 130|V |L |CV |Cobrado en Voluntaria|DT |Devuelto Total | 27904|V |A |CV |Cobrado en Voluntaria|O |Confirmado no facturado | 14653|V |L |CV |Cobrado en Voluntaria|O |Confirmado no facturado | 6977|V |L |CV |Cobrado en Voluntaria|OD |Confirmado por Domiciliación | 46|V |L |CV |Cobrado en Voluntaria|OR |Confirmado por Reposición | 3|V |A |CV |Cobrado en Voluntaria|PDT |Propuesta Devolución Total | 86|V |L |E |En Ejecutiva |Con |Consolidar | 3|V |L |E |En Ejecutiva |Emi |Emitido y no Domiciliado | 276|V |L |E |En Ejecutiva |EMN |Emitida Notificación | 22|V |L |E |En Ejecutiva |Frc |Fraccionado | 665|V |L |E |En Ejecutiva |Not |Notificación Efectiva | 12|V |L |E |En Ejecutiva |Pem |Pendiente de Emitir | 14|V |L |E |En Ejecutiva |SC |Suspensión Cautelar | 36|V |L |V |En Voluntaria |Emi |Emitido y no Domiciliado | 86|V |L |V |En Voluntaria |EMN |Emitida Notificación | 14|V |L |V |En Voluntaria |Frc |Fraccionado | 19|V |L |V |En Voluntaria |Not |Notificación Efectiva | 193|V |L |V |En Voluntaria |Pem |Pendiente de Emitir | 17|V |L |V |En Voluntaria |SC |Suspensión Cautelar | 4|V |L | | | | | 2569| |L |B |Baja |C |Comunicada no facturada | 9541| |R |B |Baja |C |Comunicada no facturada | 7| |L |B |Baja |DT |Devuelto Total | 3| |R |B |Baja |DT |Devuelto Total | 7| |L |B |Baja |F |por Fallido no facturado | 212| |R |B |Baja |F |por Fallido no facturado | 170| |L |B |Baja |FF |por Fallido facturado | 996| |R |B |Baja |FF |por Fallido facturado | 26| |L |B |Baja |Om |por otros motivos no facturada| 221| |R |B |Baja |Om |por otros motivos no facturada| 355| |L |B |Baja |Omf |por otros motivos facturada | 1631| |R |B |Baja |Omf |por otros motivos facturada | 1| |R |B |Baja |PDT |Propuesta Devolución Total | 587| |R |B |Baja |V |En Voluntaria no facturada | 45| |L |B |Baja |VO |Voluntaria Otros Motivos | 809| |A |B |Baja |VOF |Voluntaria Otros Motivos Fact.| 741| |L |B |Baja |VOF |Voluntaria Otros Motivos Fact.| 1976| |R |B |Baja |VOF |Voluntaria Otros Motivos Fact.| 9| |L |CE |Cobrado en Ejecutiva |DP |Devuelto Parcial | 96| |R |CE |Cobrado en Ejecutiva |DP |Devuelto Parcial | 17| |L |CE |Cobrado en Ejecutiva |DT |Devuelto Total | 56| |R |CE |Cobrado en Ejecutiva |DT |Devuelto Total | 7| |A |CE |Cobrado en Ejecutiva |O |Confirmado no facturado | 3630| |L |CE |Cobrado en Ejecutiva |O |Confirmado no facturado | 28221| |R |CE |Cobrado en Ejecutiva |O |Confirmado no facturado | 181| |L |CE |Cobrado en Ejecutiva |OD |Confirmado por Domiciliación | 1980| |R |CE |Cobrado en Ejecutiva |OD |Confirmado por Domiciliación | 338| |A |CE |Cobrado en Ejecutiva |OF |Confirmado facturado | 4002| |L |CE |Cobrado en Ejecutiva |OF |Confirmado facturado | 31347| |R |CE |Cobrado en Ejecutiva |OF |Confirmado facturado | 2| |R |CE |Cobrado en Ejecutiva |PDT |Propuesta Devolución Total | 409| |R |CV |Cobrado en Voluntaria|DP |Devuelto Parcial | 7| |A |CV |Cobrado en Voluntaria|DT |Devuelto Total | 72| |L |CV |Cobrado en Voluntaria|DT |Devuelto Total | 518| |R |CV |Cobrado en Voluntaria|DT |Devuelto Total | 309| |L |CV |Cobrado en Voluntaria|O |Confirmado no facturado | 45299| |R |CV |Cobrado en Voluntaria|O |Confirmado no facturado | 127| |L |CV |Cobrado en Voluntaria|OD |Confirmado por Domiciliación | 147668| |R |CV |Cobrado en Voluntaria|OD |Confirmado por Domiciliación | 24714| |A |CV |Cobrado en Voluntaria|OF |Confirmado facturado | 15017| |L |CV |Cobrado en Voluntaria|OF |Confirmado facturado | 464667| |R |CV |Cobrado en Voluntaria|OF |Confirmado facturado | 48| |L |CV |Cobrado en Voluntaria|OR |Confirmado por Reposición | 135| |R |CV |Cobrado en Voluntaria|OR |Confirmado por Reposición | 3| |L |E |En Ejecutiva |Con |Consolidar | 41| |R |E |En Ejecutiva |Con |Consolidar | 1| |L |E |En Ejecutiva |Emi |Emitido y no Domiciliado | 11| |R |E |En Ejecutiva |Emi |Emitido y no Domiciliado | 15| |L |E |En Ejecutiva |EMN |Emitida Notificación | 813| |R |E |En Ejecutiva |EMN |Emitida Notificación | 23| |L |E |En Ejecutiva |Frc |Fraccionado | 157| |R |E |En Ejecutiva |Frc |Fraccionado | 3275| |L |E |En Ejecutiva |Not |Notificación Efectiva | 12322| |R |E |En Ejecutiva |Not |Notificación Efectiva | 25| |R |E |En Ejecutiva |Pem |Pendiente de Emitir | 81| |L |E |En Ejecutiva |SC |Suspensión Cautelar | 235| |R |E |En Ejecutiva |SC |Suspensión Cautelar | 578| |R |V |En Voluntaria |Dom |Emitido por Domiciliación | 29| |R |V |En Voluntaria |Emi |Emitido y no Domiciliado | 6| |R |V |En Voluntaria |Frc |Fraccionado | 139| |L |V |En Voluntaria |SC |Suspensión Cautelar | 296| |R |V |En Voluntaria |SC |Suspensión Cautelar |
De estos estados, con lo que respecta a los estados de los valores temporales tenemos que eliminar los registros cuyo ESTADO_VATE esté en ('E','P','G') Siendo 'E'-Eliminado, 'P'-con Problemas, y G-Gestión de plusvalías.
1. Tablas que necesitamos
1.1. Tabla de cuentas operativas bancarias
Para ver los cobros necesitamos una tabla que nos indique las cuentas operativas. En este caso se puede crear una tabla a partir de esta consulta:
SELECT CUBA.ID_CUBA, CUBA.ACTIVA_CUBA, CUBA.IBAN_CUBA, CUBA.PLAZO_CUBA, CUBA.SUFIJO_PRESENTADOR_CUBA ,
CUBA.DESCRIPCION_CUBA, CUBA.NOMBRE_CORTO_CUBA
FROM OPS$GTTORA.CUBA_CUENTAS_BANCARIAS CUBA;
y nos devuelve
ID_CUBA|ACTIVA_CUBA|IBAN_CUBA |PLAZO_CUBA|SUFIJO_PRESENTADOR_CUBA|DESCRIPCION_CUBA |NOMBRE_CORTO_CUBA|
-------+-----------+------------------------+----------+-----------------------+-------------------------------------+-----------------+
538| 1|ES64********************| 56|000 |OPERATIVA y DOMICILIACIONES SABADELL|SABADELL |
724| 1|ES52********************| | |CUENTA RESTRINGIDA |CAIXA POPULAR |
536| 1|ES32********************| 58|073 |Operativa Bankia |BANKIA |
537| 1|ES73********************| 58|073 |OPERATIVA SANTANDER |SANTANDER |1.2 Tabla de canales de pago
Se puede copiar la tabla CAPA
SELECT * FROM OPS$GTTORA.CAPA_CANAL_PAGO CAPA ;
y nos devuelve
COD_CAPA|DESC_CAPA | --------+---------------------------------------------------------+ 1 |Ventanilla o adeudo en cuenta | 2 |Autoservicio (cajeros automáticos y similares) | 3 |Banca virtual (electrónica por internet, teléfono, etc..)| 4 |Pasarela de pagos (web del organismo) |
2. Campos que necesitamos para cada movimiento
Vamos a crear una vista que tenga estos campos:
- IDENTIFICADOR_VATE: Identificador del valor temporal
- IDENTIFICADOR_VALO: Identificador del valor definitivo
- ID_EPER: id del valor temporal
- ID_EPER_VALO: id del valor definitivo
- COD_ESTADO: Código del estado
- COD_SUBESTADO: Código subestado (per ejemplo en cobros puede ser por C60, domiciliado, etc)
- FECHA: Fecha que ha pasado a ese estado
- COD_USUA: Código del usuario
- OBSE: Observaciones
- IMP_PPAL: Importe principal afectado
- IMPO_RECA: Importe recardo afectado
- IMPO_INT: Importe intereses afectadosd
- IMPO_COST: Importe de costas afectados
- ID_CARGO: Id del cargo
- ID_CUBA: Id de la cuenta operativa bancaria
- ID_CAPA: Id del canal de pago
- EXP: Expediente afectado por el movimiento
Siendo COD_ESTADO:
- GR: Grabación (Creación)
Como se ve, hay un montón de estados, para ello vamos a ver los distintos estados que tenemos en función de si son autoliquidaciones, liquidaciones o recibos
