CREATE SEQUENCE SEQ_CESS_REST_OUT
  START WITH 1
  MAXVALUE 999999999999999999999999999
  MINVALUE 1
  NOCYCLE
  NOCACHE
  NOORDER;
  
  
  CREATE SEQUENCE SEQ_CESS_RESTTP_OUT
    START WITH 1
    MAXVALUE 999999999999999999999999999
    MINVALUE 1
    NOCYCLE
    NOCACHE
  NOORDER;
  
  


CREATE TABLE MNP_GISP_CESS_RESTITUZIONE_OUT
(
  UNIQUE_ID          NUMBER,
  DATA_CREAZIONE     DATE                       DEFAULT SYSDATE               NOT NULL,
  DATA_ULTIMO_INVIO  DATE                       NOT NULL,
  NOME_RICHIESTA     VARCHAR2(18 BYTE)          DEFAULT 'DBC_RESTITUZIONE'    NOT NULL,
  NUM_INVII          NUMBER,
  DA_PROCESSARE      NUMBER(1),
  CAS                VARCHAR2(3 BYTE)           DEFAULT 'DBC'                 NOT NULL,
  NUM_TEL            VARCHAR2(15 BYTE)          NOT NULL,
  DATA_ESPL          VARCHAR2(20 BYTE)          NOT NULL,
  TID                VARCHAR2(23 BYTE),
  DA_SCODARE         NUMBER(1)
)
TABLESPACE TAB_BIG
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          1M
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
NOMONITORING;


CREATE UNIQUE INDEX MNP_GISP_CESS_RESTITUZIONE__PK ON MNP_GISP_CESS_RESTITUZIONE_OUT
(UNIQUE_ID)
LOGGING
TABLESPACE TAB_IDX_BIG
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          1M
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


ALTER TABLE MNP_GISP_CESS_RESTITUZIONE_OUT ADD (
  CONSTRAINT MNP_GISP_CESS_RESTITUZIONE__PK
 PRIMARY KEY
 (UNIQUE_ID)
    USING INDEX 
    TABLESPACE TAB_IDX_BIG
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          1M
                NEXT             1M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
               ));




CREATE TABLE MNP_GISP_CESS_RESTTP_OUT
(
  UNIQUE_ID          NUMBER,
  DATA_CREAZIONE     DATE                       DEFAULT SYSDATE               NOT NULL,
  DATA_ULTIMO_INVIO  DATE                       NOT NULL,
  NOME_RICHIESTA     VARCHAR2(16 BYTE)          DEFAULT 'DBC_TERZEPARTI'      NOT NULL,
  NUM_INVII          NUMBER,
  DA_PROCESSARE      NUMBER(1),
  CAS                VARCHAR2(3 BYTE)           DEFAULT 'DBC'                 NOT NULL,
  NUM_TEL            VARCHAR2(15 BYTE)          NOT NULL,
  DATA_ESPL          VARCHAR2(20 BYTE)          NOT NULL,
  RGND               VARCHAR2(3 BYTE)           NOT NULL,
  RGNR               VARCHAR2(3 BYTE)           NOT NULL,
  TID                VARCHAR2(23 BYTE),
  DA_SCODARE         NUMBER(1)
)
TABLESPACE TAB_BIG
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          1M
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
NOMONITORING;


CREATE UNIQUE INDEX MNP_GISP_CESS_RESTTP_OUT__PK ON MNP_GISP_CESS_RESTTP_OUT
(UNIQUE_ID)
LOGGING
TABLESPACE TAB_IDX_BIG
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          1M
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


ALTER TABLE MNP_GISP_CESS_RESTTP_OUT ADD (
  CONSTRAINT MNP_GISP_CESS_RESTTP_OUT__PK
 PRIMARY KEY
 (UNIQUE_ID)
    USING INDEX 
    TABLESPACE TAB_IDX_BIG
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          1M
                NEXT             1M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
               ));

ALTER TABLE MNP_GISP_CESS_RESTTP_OUT ADD (
  CONSTRAINT MNP_GISP_CESS_RESTTP_OUT_R01 
 FOREIGN KEY (TID) 
 REFERENCES MNP_GESTIONE_RICHIESTA_PORTING (ID_RICHIESTA));

create table mnp_importi_sla_operatore
(
id_operatore	varchar2(4),
sla		varchar2(5),
importo		number
) tablespace tab_small;

alter table mnp_importi_sla_operatore
add constraint pk_mnp_importi_sla_operatore primary key (id_operatore, sla)
using index  tablespace TAB_IDX_SMALL;

alter table mnp_importi_sla_operatore
add constraint FK_importi_sla_operatore_op foreign key (id_operatore)
references mnp_anagrafica_operatori (id_operatore);

grant select on mnp_importi_sla_operatore to DWH with grant option;

ALTER TABLE MNP_MSS_CESS_OUT RENAME TO BCK_MNP_MSS_CESS_OUT;
ALTER TABLE MNP_MSS_CESS_RECUPERO_OUT RENAME TO BCK_MNP_MSS_CESS_RECUPERO_OUT;
ALTER TABLE MNP_MSS_PORTING_OUT RENAME TO BCK_MNP_MSS_PORTING_OUT;
ALTER TABLE MNP_MSS_PORTING_RECUPERO_OUT RENAME TO BKMNP_MSS_PORTING_RECUPERO_OUT;

ALTER TABLE MNP_MSS_VERIFICA_CESS_OUT
 ADD (RISPOSTA  VARCHAR2(255));

ALTER TABLE MNP_MVNO_NOT_SLA_REC_OUT
  ADD LISTA_SLA  varchar2(100);
  
alter table MNP_STORICO_CESS modify STATO_DA NUMBER(2);
alter table MNP_STORICO_CESS modify STATO_A NUMBER(2);

create index IDX_MNP_GEST_RICH_DONOR_VIRT3 on MNP_GEST_RICH_DONOR_VIRT (MSISDN)
tablespace TAB_IDX_BIG;

create index IDX_MNP_GEST_RICH_REC_VIRT2 on MNP_GEST_RICH_REC_VIRT (MSISDN)
tablespace TAB_IDX_BIG;