659 lines
17 KiB
SQL
659 lines
17 KiB
SQL
|
|
-- eseguire come utente DWH
|
|
-----------------------------------------------------------------
|
|
|
|
-----------------------------------------------------------------
|
|
|
|
---CREA DB LINK VERSO DBCGO
|
|
|
|
create database link "DBCGO"
|
|
connect to DBCGO
|
|
identified by "<pwd>"
|
|
using 'DBCGO';
|
|
|
|
---CREA SINONIMO PER SAP_ANAG_FV
|
|
|
|
CREATE PUBLIC SYNONYM DBCGO_SAP_ANAG_FV FOR SAP_ANAG_FV@DBCGO;
|
|
|
|
-----------------------------------------------------------------
|
|
|
|
|
|
-- TIM DONOR
|
|
|
|
CREATE MATERIALIZED VIEW dwh.mnp_dwh_don
|
|
TABLESPACE tab_dwh
|
|
NOCACHE
|
|
LOGGING
|
|
NOPARALLEL
|
|
BUILD IMMEDIATE
|
|
REFRESH COMPLETE ON DEMAND
|
|
WITH PRIMARY KEY
|
|
AS
|
|
SELECT ROWNUM AS ID_PROG, r.*
|
|
FROM
|
|
(SELECT
|
|
SYSDATE AS data_aggiornamento,
|
|
DECODE(SUBSTR(codice_gruppo,1,3),'BKL','BKL',DECODE(a.richiestaadhocaom||a.richiestaadhoc,'00','Standard','PHoc')) AS tipo_richiesta,
|
|
a.tipo_cliente,
|
|
a.nome_ot,
|
|
a.codice_pre_post_pagato,
|
|
a.marcaggio,
|
|
a.sottomarcaggio,
|
|
a.codice_operatore_recipient,
|
|
a.codice_operatore_donating,
|
|
TRUNC(b.data_i_o) AS data_evento,
|
|
TO_CHAR(b.data_i_o,'yy-iw') AS settimana_evento,
|
|
TO_CHAR(b.data_i_o,'mm') AS mese_evento,
|
|
c.descr_stato AS descr_stato_evento,
|
|
ca.descr_stato AS descr_stato_attuale,
|
|
b.stato_a AS stato_evento,
|
|
a.stato AS stato_attuale,
|
|
DECODE(a.stato,10,a.codicerifiutovalidazione,NULL) AS cod_rifiuto,
|
|
DECODE(a.stato,10,r.causale,NULL) AS causale_rifiuto,
|
|
COUNT(*) AS richieste,
|
|
a.operatore_interno
|
|
FROM
|
|
mnp.mnp_gestione_richiesta a,
|
|
mnp.mnp_storico_richiesta b,
|
|
mnp.mnp_stato c,
|
|
mnp.mnp_stato ca,
|
|
mnp.mnp_causale_rifiuto r
|
|
WHERE (TRUNC(b.data_i_o) BETWEEN TO_DATE(SYSDATE - 183) AND TRUNC(SYSDATE))
|
|
AND (a.id_richiesta=b.id_richiesta)
|
|
AND (b.stato_a IN (1,3,4,9,10,12,16))
|
|
AND (b.stato_a=c.id_stato)
|
|
AND (a.stato=ca.id_stato)
|
|
AND a.codicerifiutovalidazione = r.cod_causale_rifiuto (+)
|
|
GROUP BY DECODE(SUBSTR(codice_gruppo,1,3),'BKL','BKL',DECODE(a.richiestaadhocaom||a.richiestaadhoc,'00','Standard','PHoc')),
|
|
a.tipo_cliente,
|
|
a.nome_ot,
|
|
a.codice_pre_post_pagato,
|
|
a.marcaggio,
|
|
a.sottomarcaggio,
|
|
a.codice_operatore_recipient,
|
|
a.codice_operatore_donating,
|
|
TRUNC(b.data_i_o),
|
|
TO_CHAR(b.data_i_o,'yy-iw'),
|
|
TO_CHAR(b.data_i_o,'mm'),
|
|
c.descr_stato,
|
|
ca.descr_stato,
|
|
b.stato_a,
|
|
a.stato,
|
|
DECODE(a.stato,10,a.codicerifiutovalidazione,NULL),
|
|
DECODE(a.stato,10,r.causale,NULL),
|
|
a.operatore_interno) r;
|
|
|
|
----------------------------------------------------------------
|
|
--CREA INDICE SU dwh.mnp_dwh_don
|
|
|
|
CREATE INDEX IDX_MNP_DWH_DON_1 ON dwh.mnp_dwh_don
|
|
(DATA_EVENTO)
|
|
TABLESPACE TAB_IDX_BIG;
|
|
|
|
CREATE INDEX IDX_MNP_DWH_DON_2 ON dwh.mnp_dwh_don
|
|
(STATO_ATTUALE)
|
|
TABLESPACE TAB_IDX_BIG;
|
|
|
|
-----------------------------------------------------------------
|
|
|
|
|
|
|
|
-----------------------------------------------------------------
|
|
|
|
--TIM RECIPIENT
|
|
|
|
CREATE MATERIALIZED VIEW dwh.mnp_dwh_rec_push
|
|
TABLESPACE tab_dwh
|
|
NOCACHE
|
|
LOGGING
|
|
NOPARALLEL
|
|
BUILD IMMEDIATE
|
|
REFRESH COMPLETE ON DEMAND
|
|
WITH PRIMARY KEY
|
|
AS
|
|
SELECT ROWNUM AS ID_PROG, r.*
|
|
FROM
|
|
(SELECT
|
|
SYSDATE AS data_aggiornamento,
|
|
DECODE(a.richiestaadhocaom||a.richiestaadhoc,'00','Standard','PHoc') AS tipo_richiesta,
|
|
a.tipo_cliente,
|
|
a.nome_ot,
|
|
a.codice_pre_post_pagato,
|
|
t.codice_dealer,
|
|
a.tipo_servizio_sistema_mitt AS tipo_servizio,
|
|
a.codice_operatore_donating AS donating,
|
|
TRUNC (b.data_i_o) AS data_evento,
|
|
TO_CHAR (b.data_i_o, 'yy-iw') AS settimana_evento,
|
|
TO_CHAR (b.data_i_o, 'mm') AS mese_evento,
|
|
DECODE (b.stato_da||b.stato_a,'-13','RIGENERATA',c.descr_stato) AS descr_stato_evento,
|
|
s.descr_stato AS descr_stato_attuale,
|
|
b.stato_a AS stato_evento,
|
|
a.stato AS stato_attuale,
|
|
DECODE (a.stato,5,'Scartata','Pic') AS pic_scarto,
|
|
DECODE (a.stato,7, a.codicerifiutovalidazione,9, a.codicerifiutovalidazione,NULL) AS cod_rifiuto,
|
|
DECODE (a.stato, 7, r.causale,9, r.causale, NULL) AS causale_rifiuto,
|
|
COUNT (*) AS richieste,
|
|
a.operatore_interno,
|
|
DBCGO_SAP_ANAG_FV.CANALE_ACQUISIZIONE,
|
|
DBCGO_SAP_ANAG_FV.SOTTOCANALE_ACQUISIZIONE
|
|
FROM mnp.mnp_gestione_richiesta_rec a,
|
|
mnp.mnp_storico_richiesta_rec b,
|
|
mnp.mnp_stato_rec c,
|
|
mnp.mnp_stato_rec s,
|
|
mnp.mnp_causale_rifiuto r,
|
|
mnp.mnp_gestione_richiesta_rec_ext t,
|
|
DBCGO_SAP_ANAG_FV
|
|
WHERE TRUNC (b.data_i_o) BETWEEN TO_DATE(SYSDATE - 183) AND TRUNC (SYSDATE)
|
|
AND a.id_richiesta = b.id_richiesta
|
|
AND a.id_richiesta = t.id_richiesta
|
|
AND (b.stato_a IN (2, 4, 6, 7, 8, 9, 12) OR (b.stato_da = -1 AND b.stato_a = 3 AND a.stato <> 5))
|
|
AND b.stato_a = c.id_stato
|
|
AND a.stato = s.id_stato
|
|
AND a.codicerifiutovalidazione = r.cod_causale_rifiuto(+)
|
|
AND t.CODICE_DEALER = DBCGO_SAP_ANAG_FV.COD_ID (+)
|
|
GROUP BY DECODE(a.richiestaadhocaom||a.richiestaadhoc,'00','Standard','PHoc'),
|
|
a.tipo_cliente,
|
|
a.nome_ot,
|
|
a.codice_pre_post_pagato,
|
|
t.codice_dealer,
|
|
a.tipo_servizio_sistema_mitt,
|
|
a.codice_operatore_donating,
|
|
TRUNC (b.data_i_o),
|
|
TO_CHAR (b.data_i_o, 'yy-iw'),
|
|
TO_CHAR (b.data_i_o, 'mm'),
|
|
DECODE(b.stato_da||b.stato_a,'-13','RIGENERATA',c.descr_stato),
|
|
s.descr_stato,
|
|
b.stato_a,
|
|
a.stato,
|
|
DECODE(a.stato,5,'Scartata','Pic'),
|
|
DECODE (a.stato,7, a.codicerifiutovalidazione,9, a.codicerifiutovalidazione,NULL),
|
|
DECODE (a.stato, 7, r.causale,9, r.causale, NULL),
|
|
a.operatore_interno,
|
|
DBCGO_SAP_ANAG_FV.CANALE_ACQUISIZIONE,
|
|
DBCGO_SAP_ANAG_FV.SOTTOCANALE_ACQUISIZIONE) r;
|
|
|
|
----------------------------------------------------------------
|
|
--CREA INDICE SU dwh.mnp_dwh_rec_push
|
|
|
|
CREATE INDEX IDX_MNP_DWH_REC_1 ON dwh.mnp_dwh_rec_push
|
|
(DATA_EVENTO)
|
|
TABLESPACE TAB_IDX_BIG;
|
|
|
|
CREATE INDEX IDX_MNP_DWH_REC_2 ON dwh.mnp_dwh_rec_push
|
|
(STATO_ATTUALE)
|
|
TABLESPACE TAB_IDX_BIG;
|
|
|
|
-----------------------------------------------------------------
|
|
|
|
|
|
|
|
-----------------------------------------------------------------
|
|
|
|
-- CREA VISTA MNP_DWH_REC_DATI_VIEW
|
|
--- modificata per ORA-00972: identifier is too long
|
|
|
|
CREATE OR REPLACE VIEW MNP_DWH_REC_DATI_VIEW
|
|
(ID_PROG,
|
|
RICHIESTE,
|
|
TIPO_CLIENTE,
|
|
NOME_OT,
|
|
DONATING,
|
|
TIPO_RICHIESTA,
|
|
DATA_EVENTO,
|
|
SETTIMANA_EVENTO,
|
|
DESCR_STATO_EVENTO,
|
|
DESCR_STATO_ATTUALE,
|
|
PIC_SCARTO,
|
|
CODICE_PRE_POST_PAGATO,
|
|
OPERATORE_INTERNO)
|
|
AS
|
|
(SELECT ROWNUM, r.aa, r.bb, r.cc, r.dd, r.ee, r.ff, r.gg, r.hh, r.ii, r.ll, r.mm, r.nn
|
|
FROM
|
|
(
|
|
SELECT
|
|
Sum(MNP_DWH_REC_PUSH.RICHIESTE)aa,
|
|
MNP_DWH_REC_PUSH.TIPO_CLIENTE bb,
|
|
MNP_DWH_REC_PUSH.NOME_OT cc,
|
|
MNP_DWH_REC_PUSH.DONATING dd,
|
|
MNP_DWH_REC_PUSH.TIPO_RICHIESTA ee,
|
|
MNP_DWH_REC_PUSH.DATA_EVENTO ff,
|
|
MNP_DWH_REC_PUSH.SETTIMANA_EVENTO gg,
|
|
MNP_DWH_REC_PUSH.DESCR_STATO_EVENTO hh,
|
|
MNP_DWH_REC_PUSH.DESCR_STATO_ATTUALE ii,
|
|
MNP_DWH_REC_PUSH.PIC_SCARTO ll,
|
|
MNP_DWH_REC_PUSH.CODICE_PRE_POST_PAGATO mm,
|
|
MNP_DWH_REC_PUSH.OPERATORE_INTERNO nn
|
|
FROM MNP_DWH_REC_PUSH
|
|
WHERE (MNP_DWH_REC_PUSH.DONATING<>'TIMT')
|
|
GROUP BY
|
|
MNP_DWH_REC_PUSH.TIPO_CLIENTE,
|
|
MNP_DWH_REC_PUSH.NOME_OT,
|
|
MNP_DWH_REC_PUSH.TIPO_RICHIESTA,
|
|
MNP_DWH_REC_PUSH.DONATING,
|
|
MNP_DWH_REC_PUSH.DATA_EVENTO,
|
|
MNP_DWH_REC_PUSH.SETTIMANA_EVENTO,
|
|
MNP_DWH_REC_PUSH.DESCR_STATO_EVENTO,
|
|
MNP_DWH_REC_PUSH.DESCR_STATO_ATTUALE,
|
|
MNP_DWH_REC_PUSH.PIC_SCARTO,
|
|
MNP_DWH_REC_PUSH.CODICE_PRE_POST_PAGATO,
|
|
MNP_DWH_REC_PUSH.OPERATORE_INTERNO)r);
|
|
|
|
|
|
|
|
-----------------------------------------------------------------
|
|
|
|
-- CREA LA VISTA MNP_DWH_REC_DATI_RIFIUTI_VIEW
|
|
--- modificata per ORA-00972: identifier is too long
|
|
|
|
CREATE OR REPLACE VIEW MNP_DWH_REC_DATI_RIFIUTI_VIEW
|
|
(ID_PROG,
|
|
RICHIESTE,
|
|
TIPO_CLIENTE,
|
|
NOME_OT,
|
|
TIPO_RICHIESTA,
|
|
DONATING,
|
|
DATA_EVENTO,
|
|
SETTIMANA_EVENTO,
|
|
DESCR_STATO_EVENTO,
|
|
CAUSALE_RIFIUTO,
|
|
COD_RIFIUTO,
|
|
CODICE_PRE_POST_PAGATO,
|
|
DESC_STATO_ATTUALE,
|
|
OPERATORE_INTERNO)
|
|
AS
|
|
(
|
|
SELECT ROWNUM, r.aa, r.bb, r.cc, r.dd, r.ee, r.ff, r.gg, r.hh, r.ii, r.ll, r.mm, r.nn, r.oo
|
|
FROM
|
|
(
|
|
SELECT
|
|
Sum(MNP_DWH_REC_PUSH.RICHIESTE)aa,
|
|
MNP_DWH_REC_PUSH.TIPO_CLIENTE bb,
|
|
MNP_DWH_REC_PUSH.NOME_OT cc,
|
|
MNP_DWH_REC_PUSH.TIPO_RICHIESTA dd,
|
|
MNP_DWH_REC_PUSH.DONATING ee,
|
|
MNP_DWH_REC_PUSH.DATA_EVENTO ff,
|
|
MNP_DWH_REC_PUSH.SETTIMANA_EVENTO gg,
|
|
MNP_DWH_REC_PUSH.DESCR_STATO_EVENTO hh,
|
|
MNP_DWH_REC_PUSH.CAUSALE_RIFIUTO ii,
|
|
MNP_DWH_REC_PUSH.COD_RIFIUTO ll,
|
|
MNP_DWH_REC_PUSH.CODICE_PRE_POST_PAGATO mm,
|
|
MNP_DWH_REC_PUSH.DESCR_STATO_ATTUALE nn,
|
|
MNP_DWH_REC_PUSH.OPERATORE_INTERNO oo
|
|
FROM MNP_DWH_REC_PUSH
|
|
WHERE (MNP_DWH_REC_PUSH.STATO_ATTUALE IN (7,9))
|
|
GROUP BY
|
|
MNP_DWH_REC_PUSH.TIPO_CLIENTE,
|
|
MNP_DWH_REC_PUSH.NOME_OT,
|
|
MNP_DWH_REC_PUSH.TIPO_RICHIESTA,
|
|
MNP_DWH_REC_PUSH.DONATING,
|
|
MNP_DWH_REC_PUSH.DATA_EVENTO,
|
|
MNP_DWH_REC_PUSH.SETTIMANA_EVENTO,
|
|
MNP_DWH_REC_PUSH.DESCR_STATO_EVENTO,
|
|
MNP_DWH_REC_PUSH.CAUSALE_RIFIUTO,
|
|
MNP_DWH_REC_PUSH.COD_RIFIUTO,
|
|
MNP_DWH_REC_PUSH.CODICE_PRE_POST_PAGATO,
|
|
MNP_DWH_REC_PUSH.DESCR_STATO_ATTUALE,
|
|
MNP_DWH_REC_PUSH.OPERATORE_INTERNO
|
|
)r);
|
|
|
|
|
|
|
|
-----------------------------------------------------------------
|
|
|
|
--- CREA LA VISTA MNP_RECIPIENT_CANALE_VIEW
|
|
--- modificata per ORA-00972: identifier is too long
|
|
|
|
CREATE OR REPLACE VIEW MNP_RECIPIENT_CANALE_VIEW
|
|
(ID_PROG,
|
|
DATA_AGGIORNAMENTO,
|
|
TIPO_RICHIESTA,
|
|
TIPO_CLIENTE,
|
|
NOME_OT,
|
|
CODICE_DEALER,
|
|
TIPO_SERVIZIO,
|
|
DONATING,
|
|
DATA_EVENTO,
|
|
SETTIMANA_EVENTO,
|
|
MESE_EVENTO,
|
|
DESCR_STATO_EVENTO,
|
|
DESC_STATO_ATTUALE,
|
|
STATO_EVENTO,
|
|
STATO_ATTUALE,
|
|
COD_RIFIUTO,
|
|
CAUSALE_RIFIUTO,
|
|
CANALE_ACQUISIZIONE,
|
|
SOTTOCANALE_ACQUISIZIONE,
|
|
RIGA)
|
|
AS
|
|
(
|
|
SELECT ROWNUM, r.aa, r.bb, r.cc, r.dd, r.ee, r.ff, r.gg, r.hh, r.ii, r.ll, r.mm, r.nn, r.oo, r.pp, r.qq, r.rr, r.ss, r.tt, r.uu
|
|
FROM
|
|
(
|
|
SELECT
|
|
MNP_DWH_REC_PUSH.DATA_AGGIORNAMENTO aa,
|
|
MNP_DWH_REC_PUSH.TIPO_RICHIESTA bb,
|
|
MNP_DWH_REC_PUSH.TIPO_CLIENTE cc,
|
|
MNP_DWH_REC_PUSH.NOME_OT dd,
|
|
MNP_DWH_REC_PUSH.CODICE_DEALER ee,
|
|
MNP_DWH_REC_PUSH.TIPO_SERVIZIO ff,
|
|
MNP_DWH_REC_PUSH.DONATING gg,
|
|
MNP_DWH_REC_PUSH.DATA_EVENTO hh,
|
|
MNP_DWH_REC_PUSH.SETTIMANA_EVENTO ii,
|
|
MNP_DWH_REC_PUSH.MESE_EVENTO ll,
|
|
MNP_DWH_REC_PUSH.DESCR_STATO_EVENTO mm,
|
|
MNP_DWH_REC_PUSH.DESCR_STATO_ATTUALE nn,
|
|
MNP_DWH_REC_PUSH.STATO_EVENTO oo,
|
|
MNP_DWH_REC_PUSH.STATO_ATTUALE pp,
|
|
MNP_DWH_REC_PUSH.COD_RIFIUTO qq,
|
|
MNP_DWH_REC_PUSH.CAUSALE_RIFIUTO rr,
|
|
MNP_DWH_REC_PUSH.CANALE_ACQUISIZIONE ss,
|
|
MNP_DWH_REC_PUSH.SOTTOCANALE_ACQUISIZIONE tt,
|
|
SUM(MNP_DWH_REC_PUSH.RICHIESTE) uu
|
|
FROM MNP_DWH_REC_PUSH
|
|
GROUP BY
|
|
MNP_DWH_REC_PUSH.DATA_AGGIORNAMENTO,
|
|
MNP_DWH_REC_PUSH.TIPO_RICHIESTA,
|
|
MNP_DWH_REC_PUSH.TIPO_CLIENTE,
|
|
MNP_DWH_REC_PUSH.NOME_OT,
|
|
MNP_DWH_REC_PUSH.CODICE_DEALER,
|
|
MNP_DWH_REC_PUSH.TIPO_SERVIZIO,
|
|
MNP_DWH_REC_PUSH.DONATING,
|
|
MNP_DWH_REC_PUSH.DATA_EVENTO,
|
|
MNP_DWH_REC_PUSH.SETTIMANA_EVENTO,
|
|
MNP_DWH_REC_PUSH.MESE_EVENTO,
|
|
MNP_DWH_REC_PUSH.DESCR_STATO_EVENTO,
|
|
MNP_DWH_REC_PUSH.DESCR_STATO_ATTUALE,
|
|
MNP_DWH_REC_PUSH.STATO_EVENTO,
|
|
MNP_DWH_REC_PUSH.STATO_ATTUALE,
|
|
MNP_DWH_REC_PUSH.COD_RIFIUTO,
|
|
MNP_DWH_REC_PUSH.CAUSALE_RIFIUTO,
|
|
MNP_DWH_REC_PUSH.CANALE_ACQUISIZIONE,
|
|
MNP_DWH_REC_PUSH.SOTTOCANALE_ACQUISIZIONE
|
|
)r);
|
|
|
|
|
|
-----------------------------------------------------------------
|
|
|
|
--TIM TERZA PARTE
|
|
|
|
CREATE MATERIALIZED VIEW dwh.mnp_dwh_por
|
|
TABLESPACE tab_dwh
|
|
NOCACHE
|
|
LOGGING
|
|
NOPARALLEL
|
|
BUILD IMMEDIATE
|
|
REFRESH COMPLETE ON DEMAND
|
|
WITH PRIMARY KEY
|
|
AS
|
|
SELECT ROWNUM AS ID_PROG, r.*
|
|
FROM
|
|
(SELECT
|
|
SYSDATE AS data_aggiornamento,
|
|
COUNT(*) AS richieste,
|
|
a.codice_operatore_donating AS donating,
|
|
a.codice_operatore_recipient AS recipient,
|
|
TRUNC(b.data_i_o) AS data_evento,
|
|
TO_CHAR(b.data_i_o,'yy-iw') AS settimana_evento,
|
|
TO_CHAR(b.data_i_o,'mm') AS mese_evento,
|
|
c.descr_stato AS stato_evento,
|
|
s.descr_stato AS stato_attuale
|
|
FROM mnp.mnp_gestione_richiesta_porting a,
|
|
mnp.mnp_storico_porting b,
|
|
mnp.mnp_stato_porting c,
|
|
mnp.mnp_stato_porting s
|
|
WHERE b.data_i_o BETWEEN TO_DATE(SYSDATE - 183) AND TRUNC(SYSDATE)
|
|
AND a.id_richiesta= b.id_richiesta
|
|
AND b.stato_a = c.id_stato
|
|
AND a.stato = s.id_stato
|
|
GROUP BY
|
|
a.codice_operatore_donating,
|
|
a.codice_operatore_recipient,
|
|
TRUNC(b.data_i_o),
|
|
TO_CHAR(b.data_i_o,'yy-iw') ,
|
|
TO_CHAR(b.data_i_o,'mm') ,
|
|
c.descr_stato,s.descr_stato) r;
|
|
|
|
----------------------------------------------------------------
|
|
--CREA INDICE SU dwh.mnp_dwh_por
|
|
|
|
CREATE INDEX IDX_MNP_DWH_POR_1 ON dwh.mnp_dwh_por
|
|
(DATA_EVENTO)
|
|
TABLESPACE TAB_IDX_BIG;
|
|
|
|
-----------------------------------------------------------------
|
|
|
|
|
|
|
|
-----------------------------------------------------------------
|
|
|
|
|
|
--TIM CESSAZIONI IN
|
|
|
|
|
|
CREATE MATERIALIZED VIEW dwh.mnp_dwh_cess_in
|
|
TABLESPACE tab_dwh
|
|
NOCACHE
|
|
NOLOGGING
|
|
NOPARALLEL
|
|
BUILD IMMEDIATE
|
|
REFRESH COMPLETE ON DEMAND
|
|
WITH PRIMARY KEY
|
|
AS
|
|
SELECT ROWNUM AS ID_PROG, r.*
|
|
FROM
|
|
(SELECT
|
|
SYSDATE AS data_aggiornamento,
|
|
COUNT(*) AS richieste,
|
|
codice_operatore_recipient AS operatore_donating,
|
|
TRUNC(b.data_i_o) AS data_evento,
|
|
TO_CHAR(b.data_i_o,'yy-iw') AS settimana_evento,
|
|
TO_CHAR(b.data_i_o,'mm') AS mese_evento,
|
|
c.descr_stato AS descr_stato_evento,
|
|
s.descr_stato AS descr_stato_attuale,
|
|
b.stato_a AS stato_evento,
|
|
a.stato AS stato_attuale
|
|
FROM mnp.mnp_gestione_richiesta_cess a,
|
|
mnp.mnp_storico_cess b,
|
|
mnp.mnp_stato_cess c,
|
|
mnp.mnp_stato_cess s
|
|
WHERE TRUNC(b.data_i_o) BETWEEN TO_DATE(SYSDATE - 183)AND TRUNC(SYSDATE)
|
|
AND a.id_richiesta= b.id_richiesta
|
|
AND b.stato_a = c.id_stato
|
|
AND a.stato = s.id_stato
|
|
AND a.tim_donor = 1
|
|
GROUP BY codice_operatore_recipient,
|
|
TRUNC(b.data_i_o),
|
|
TO_CHAR(b.data_i_o,'yy-iw'),
|
|
TO_CHAR(b.data_i_o,'mm'),
|
|
c.descr_stato,
|
|
s.descr_stato,
|
|
b.stato_a,
|
|
a.stato) r;
|
|
|
|
-----------------------------------------------------------------
|
|
|
|
|
|
|
|
-----------------------------------------------------------------
|
|
|
|
|
|
-- TIM CESSAZIONI OUT
|
|
|
|
|
|
CREATE MATERIALIZED VIEW dwh.mnp_dwh_cess_out
|
|
TABLESPACE tab_dwh
|
|
NOCACHE
|
|
LOGGING
|
|
NOPARALLEL
|
|
BUILD IMMEDIATE
|
|
REFRESH COMPLETE ON DEMAND
|
|
WITH PRIMARY KEY
|
|
AS
|
|
SELECT ROWNUM AS ID_PROG, r.*
|
|
FROM
|
|
(SELECT
|
|
SYSDATE AS data_aggiornamento,
|
|
COUNT(*) AS richieste,
|
|
DECODE(SUBSTR(msisdn,1,4),'3932','WIND','3934','OPIV','3933','TIMG','3936','TIMG','3938','WIND','3939','H3GI',SUBSTR(msisdn,1,4)) AS operatore_recipient,
|
|
TRUNC(b.data_i_o) AS data_evento,
|
|
TO_CHAR(b.data_i_o,'yy-iw') AS settimana_evento,
|
|
TO_CHAR(b.data_i_o,'mm') AS mese_evento,
|
|
c.descr_stato AS descr_stato_evento,
|
|
s.descr_stato AS descr_stato_attuale,
|
|
b.stato_a AS stato_evento,
|
|
a.stato AS stato_attuale
|
|
FROM mnp.mnp_gestione_rich_cess_port a,
|
|
mnp.mnp_storico_cess_porting b,
|
|
mnp.mnp_stato_cess_porting c,
|
|
mnp.mnp_stato_cess_porting s
|
|
WHERE TRUNC(b.data_i_o) BETWEEN TO_DATE(SYSDATE - 183) AND TRUNC(SYSDATE)
|
|
AND a.id_richiesta= b.id_richiesta
|
|
AND b.stato_a = c.id_stato
|
|
AND a.stato = s.id_stato
|
|
AND SUBSTR(msisdn,1,4) <> '3933'
|
|
AND SUBSTR(msisdn,1,4) <> '3936'
|
|
GROUP BY DECODE(SUBSTR(msisdn,1,4),'3932','WIND','3934','OPIV','3933','TIMG','3936','TIMG','3938','WIND','3939','H3GI', SUBSTR(msisdn,1,4)),
|
|
TRUNC(b.data_i_o),
|
|
TO_CHAR(b.data_i_o,'yy-iw'),
|
|
TO_CHAR(b.data_i_o,'mm'),
|
|
c.descr_stato,
|
|
s.descr_stato,
|
|
b.stato_a,
|
|
a.stato) r;
|
|
|
|
-----------------------------------------------------------------
|
|
|
|
|
|
|
|
-----------------------------------------------------------------
|
|
|
|
--MOBILITA INTEROPERATORE - L'estrazione è basata sugli snapshots Donor, Recipient, e Porting
|
|
|
|
CREATE OR REPLACE VIEW mnp_mob_int_view
|
|
(ID_PROG,
|
|
DATA_AGGIORNAMENTO,
|
|
TIPO_RICHIESTA,
|
|
TIPO_CLIENTE,
|
|
CODICE_OPERATORE_DONATING,
|
|
CODICE_OPERATORE_RECIPIENT,
|
|
DATA_EVENTO,
|
|
SETTIMANA_EVENTO,
|
|
ANNO_MESE_EVENTO,
|
|
CODICE_PRE_POST_PAGATO,
|
|
RICHIESTE)
|
|
AS
|
|
(SELECT ROWNUM, r.*
|
|
FROM
|
|
(SELECT
|
|
a.DATA_AGGIORNAMENTO,
|
|
a.TIPO_RICHIESTA,
|
|
a.TIPO_CLIENTE,
|
|
a.CODICE_OPERATORE_DONATING,
|
|
a.CODICE_OPERATORE_RECIPIENT,
|
|
a.DATA_EVENTO,
|
|
a.SETTIMANA_EVENTO,
|
|
TO_CHAR(a.DATA_EVENTO,'YY-MM'),
|
|
a.CODICE_PRE_POST_PAGATO,
|
|
SUM(a.RICHIESTE)
|
|
FROM MNP_DWH_DON a
|
|
WHERE a.DESCR_STATO_EVENTO = 'ESPLETATA'
|
|
AND a.DATA_EVENTO >= (SYSDATE - 183)
|
|
GROUP BY
|
|
a.DATA_AGGIORNAMENTO,
|
|
a.TIPO_RICHIESTA,
|
|
a.TIPO_CLIENTE,
|
|
a.CODICE_OPERATORE_DONATING,
|
|
a.CODICE_OPERATORE_RECIPIENT ,
|
|
a.DATA_EVENTO,
|
|
a.SETTIMANA_EVENTO,
|
|
TO_CHAR(a.DATA_EVENTO,'YY-MM'),
|
|
a.CODICE_PRE_POST_PAGATO
|
|
UNION
|
|
SELECT
|
|
b.DATA_AGGIORNAMENTO,
|
|
b.TIPO_RICHIESTA,
|
|
b.TIPO_CLIENTE,
|
|
b.DONATING,
|
|
'TIMG' ,
|
|
b.DATA_EVENTO,
|
|
b.SETTIMANA_EVENTO,
|
|
TO_CHAR(b.DATA_EVENTO,'YY-MM'),
|
|
b.CODICE_PRE_POST_PAGATO,
|
|
SUM(b.RICHIESTE)
|
|
FROM MNP_DWH_REC_PUSH b
|
|
WHERE b.DESCR_STATO_EVENTO = 'EVASA'
|
|
AND b.DATA_EVENTO >= (SYSDATE - 183)
|
|
GROUP BY
|
|
b.DATA_AGGIORNAMENTO,
|
|
b.TIPO_RICHIESTA,
|
|
b.TIPO_CLIENTE,
|
|
b.DONATING,
|
|
'TIMG' ,
|
|
b.DATA_EVENTO,
|
|
b.SETTIMANA_EVENTO,
|
|
TO_CHAR(b.DATA_EVENTO,'YY-MM'),
|
|
b.CODICE_PRE_POST_PAGATO
|
|
UNION
|
|
SELECT
|
|
c.DATA_AGGIORNAMENTO,
|
|
'TERZE PARTI',
|
|
'TERZE PARTI',
|
|
c.DONATING,
|
|
c.RECIPIENT,
|
|
c.DATA_EVENTO,
|
|
c.SETTIMANA_EVENTO,
|
|
TO_CHAR(c.DATA_EVENTO,'YY-MM'),
|
|
'TERZE PARTI',
|
|
SUM(c.RICHIESTE)
|
|
FROM MNP_DWH_POR c
|
|
WHERE c.STATO_EVENTO = 'ESPLETATA'
|
|
AND c.DATA_EVENTO >= (SYSDATE - 183)
|
|
GROUP BY c.DATA_AGGIORNAMENTO,'TERZE PARTI', 'TERZE PARTI', c.DONATING, c.RECIPIENT, c.DATA_EVENTO,
|
|
c.SETTIMANA_EVENTO, TO_CHAR(c.DATA_EVENTO,'YY-MM'), 'TERZE PARTI') r);
|
|
|
|
|
|
-----------------------------------------------------------------
|
|
|
|
|
|
|
|
-----------------------------------------------------------------
|
|
|
|
-----Delibera Donating
|
|
-- modificata per ORA-00972: identifier is too long
|
|
|
|
CREATE OR REPLACE VIEW mnp_delibera_don_view
|
|
(ID_PROG,
|
|
OLO,
|
|
RIC_RICEVUTE,
|
|
RIC_PIC,
|
|
DATA_EVENTO)
|
|
AS
|
|
(SELECT ROWNUM, r.aa, r.bb, r.cc, r.dd
|
|
FROM
|
|
(
|
|
select
|
|
desc_olo aa,
|
|
nvl(rric,0) bb,
|
|
nvl(rpic,0) cc,
|
|
nvl(data_Evento,FUN_GIORNI_MENO (sysdate,1)) dd
|
|
from
|
|
(select DATA_EVENTO,CODICE_OPERATORE_RECIPIENT,sum(RICHIESTE) as rric
|
|
from mnp_dwh_don
|
|
where DATA_EVENTO = FUN_GIORNI_MENO (sysdate,1)
|
|
AND STATO_EVENTO = 1
|
|
AND TIPO_RICHIESTA ='Standard'
|
|
group by DATA_EVENTO,CODICE_OPERATORE_RECIPIENT) riric,
|
|
(select CODICE_OPERATORE_RECIPIENT,sum(RICHIESTE) as rpic
|
|
from mnp_dwh_don
|
|
where DATA_EVENTO = FUN_GIORNI_MENO (sysdate,1)
|
|
AND STATO_EVENTO = 1
|
|
AND STATO_ATTUALE <> 4
|
|
AND TIPO_RICHIESTA ='Standard'
|
|
group by CODICE_OPERATORE_RECIPIENT) ripic,
|
|
(select desc_olo
|
|
from mnp.mnp_olo
|
|
where flag_terze_parti = 1) olo
|
|
where desc_olo=ripic.CODICE_OPERATORE_RECIPIENT (+)
|
|
and desc_olo=riric.CODICE_OPERATORE_RECIPIENT (+)) r);
|
|
|
|
----------------------------------------------------------------- |