204 lines
5.4 KiB
SQL
204 lines
5.4 KiB
SQL
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; |