CREATE OR REPLACE FUNCTION dwh.FUN_TIPO_NOTIFICA_ID2DESC (ID IN NUMBER) RETURN VARCHAR2
IS
	v_descr  VARCHAR2(40);
BEGIN
      SELECT   DESCR
        INTO v_descr
        FROM mnp.MNP_LOV
       WHERE LOV_NAME='XML_FILE_TYPE'
	     AND NAME=ID;

     	RETURN v_descr;

END FUN_TIPO_NOTIFICA_ID2DESC;
/

CREATE OR REPLACE FUNCTION dwh.FUN_EXTRACT_MSISDN (TEXT IN VARCHAR2) RETURN VARCHAR2
IS
	v_MSISDN  	VARCHAR2(15);
	v_init 		NUMBER:=0;
	v_end 		NUMBER:=0;
	v_length	NUMBER:=0;
BEGIN
        v_init := INSTR(TEXT,'<MSISDN>')+8;
	v_end  := INSTR(TEXT,'</MSISDN>');
	v_length := v_end - v_init;
	
	v_MSISDN:=SUBSTR(TEXT,v_init,v_length);	
	
     	RETURN v_MSISDN;

END FUN_EXTRACT_MSISDN;
/


CREATE MATERIALIZED VIEW dwh.MNP_DW_XML_IN_OUT
TABLESPACE tab_dwh
NOCACHE
NOLOGGING
NOPARALLEL
BUILD IMMEDIATE
REFRESH COMPLETE ON DEMAND
WITH PRIMARY KEY
AS 
select 	 b.id_richiesta					id_richiesta
	,FUN_EXTRACT_MSISDN(b.RICHIESTAXML)		MSISDN
       	,'XML'						tipo_file
       	,FUN_TIPO_NOTIFICA_ID2DESC(a.tipo_file)		tipo_notifica
       	,a.nome_file					nome_file    
       	,'Ricevuto'					direzione
       	,a.data_eff					data
	,to_char(a.data_eff,'dd/mm/yyyy HH24:mi:ss')	data_show
	,a.destinatario					destinatario
       	,a.mittente					mittente
	,a.canale_alternativo				canale_alternativo
	,d.tipo_processo				tipo_processo
	,a.nome_file					nome_file_xml
	,c.nome_file					nome_file_ack
	,0						ord2
from 
	 mnp.MNP_XML_IN			a
     	,mnp.MNP_XML_RICHIESTA_IN	b
	,mnp.MNP_XML_ACK_OUT            c
	,mnp.MNP_XML_OUT		d
where 
	a.nome_file=b.nome_file
	and b.nome_file=c.nome_file_riferito(+)
	and c.nome_file=d.nome_file (+)
union
select 
	 b.id_richiesta					id_richiesta
	,FUN_EXTRACT_MSISDN(b.RICHIESTAXML)		MSISDN
	,'ACK'						tipo_file
	,FUN_TIPO_NOTIFICA_ID2DESC(d.tipo_file)		tipo_notifica
	,d.nome_file					nome_file    
	,'Ricevuto'					direzione
	,d.data_eff					data
        ,to_char(d.data_eff,'dd/mm/yyyy HH24:mi:ss')    data_show
	,d.destinatario					destinatario
    	,d.mittente					mittente
	,d.canale_alternativo				canale_alternativo
	,a.tipo_processo				tipo_processo
	,b.nome_file					nome_file_xml
	,c.nome_file					nome_file_ack
	,1						ord2
from 
	mnp.MNP_XML_OUT			a
	,mnp.MNP_XML_RICHIESTA_OUT	b
	,mnp.MNP_XML_ACK_IN         	c
	,mnp.MNP_XML_IN			d
where 
      	a.nome_file=b.nome_file
	and b.nome_file=c.nome_file_riferito
	and c.nome_file=d.nome_file
union
select 
	 b.id_richiesta					id_richiesta
	,FUN_EXTRACT_MSISDN(b.RICHIESTAXML)		MSISDN
	,'XML'						tipo_file
       	,FUN_TIPO_NOTIFICA_ID2DESC(a.tipo_file)		tipo_notifica
       	,a.nome_file					nome_file
	,'Inviato'					direzione
       	,a.data_eff					data
        ,to_char(a.data_eff,'dd/mm/yyyy HH24:mi:ss')    data_show
	,a.destinatario					destinatario
       	,a.mittente					mittente
	,a.canale_alternativo				canale_alternativo
	,a.tipo_processo				tipo_processo
	,a.nome_file					nome_file_xml
	,c.nome_file					nome_file_ack
	,0						ord2
from 
	 mnp.MNP_XML_OUT		a
     	,mnp.MNP_XML_RICHIESTA_OUT	b
	,mnp.MNP_XML_ACK_IN             c
	,mnp.MNP_XML_IN			d
where 
	a.nome_file=b.nome_file
	and b.nome_file = c.nome_file_riferito  (+)
	and c.nome_file = d.nome_file 		(+)
union
select 
	 b.id_richiesta					id_richiesta
	,FUN_EXTRACT_MSISDN(b.RICHIESTAXML)		MSISDN
	,'ACK'						tipo_file
	,FUN_TIPO_NOTIFICA_ID2DESC(d.tipo_file)		tipo_notifica
	,d.nome_file					nome_file    
	,'Inviato'					direzione
	,d.data_eff					data
	,to_char(d.data_eff,'dd/mm/yyyy HH24:mi:ss')    data_show
	,d.destinatario					destinatario
    	,d.mittente					mittente
	,d.canale_alternativo				canale_alternativo
	,d.tipo_processo				tipo_processo
	,b.nome_file					nome_file_rif
	,d.nome_file					nome_file_ack
	,1						ord2
from 
	mnp.MNP_XML_RICHIESTA_IN     	b
	,mnp.MNP_XML_ACK_OUT         	c
	,mnp.MNP_XML_OUT		d
where 
      	b.nome_file=c.nome_file_riferito
	and c.nome_file=d.nome_file
;

CREATE INDEX IDX_MNP_DW_XML_IN_OUT_1 ON dwh.MNP_DW_XML_IN_OUT
(id_richiesta)
TABLESPACE TAB_IDX_BIG;

CREATE INDEX IDX_MNP_DW_XML_IN_OUT_2 ON dwh.MNP_DW_XML_IN_OUT
(nome_file,data)
TABLESPACE TAB_IDX_BIG;