-- #################################################################
--
-- Parameters:
--   1: DB SID
--   2: Owner user
--   3: Owner password
--
-- Usage:
--   sqlplus /nolog @ppb_install_ddl.sql <ORACLE_SID> <PRDUCT_USER> <PRODUCT_PASS>
--
--#################################################################
--
--
SET DEFINE ON
--
DEFINE db_sid  = &1
DEFINE own_usr = &2
DEFINE own_pwd = &3
--
CONNECT &own_usr/&own_pwd@&db_sid
--
--
SET FEEDBACK OFF
--
SET FEEDBACK ON
--
--

CREATE TABLE CDRFILE
(
	Id				 Number(15, 0) NOT NULL,
	File_name 		 VARCHAR2(60) NOT NULL,
	Create_date		 DATE NOT NULL,
	Update_date		 DATE NOT NULL,
	Status			 NUMBER(1) NOT NULL,
	Recs_read		 NUMBER(15) NOT NULL,
	Recs_success	 NUMBER(15) NOT NULL,
	Recs_inserted	 NUMBER(15) NOT NULL,
	Recs_duplicate	 NUMBER(15) NOT NULL,
	Recs_error_total	 NUMBER(15) NOT NULL,
	Recs_decode_failed	 NUMBER(15) NOT NULL,
	Recs_unknown_sub	 NUMBER(15) NOT NULL,
	Recs_err_other		 NUMBER(15) NOT NULL,
	Sanity			     NUMBER(2) NOT NULL,
	Sanity_desc			 VARCHAR2(60)
);
--
COMMENT ON COLUMN Id            	 IS 'Unique identifier. Primary key.';
COMMENT ON COLUMN File_name          IS 'The name of the CDR file';
COMMENT ON COLUMN Create_date        IS 'Timestamp when current entry was created.';
COMMENT ON COLUMN Update_date     	 IS 'Timestamp when current entry was last modified.';
COMMENT ON COLUMN Status          	 IS 'File status: 0=new, 1=done, 2=stopped';
COMMENT ON COLUMN Recs_read          IS 'Number of records read from file. Default 0.';
COMMENT ON COLUMN Recs_success       IS 'Virtual column. Automatically generated as (recs_inserted + recs_duplicate).';
COMMENT ON COLUMN Recs_inserted      IS 'Number of records successfully processed and inserted in DB. Default 0.';
COMMENT ON COLUMN Recs_duplicate     IS 'Number of duplicate records. Default 0.';
COMMENT ON COLUMN Recs_error_total   IS 'Virtual column. Automatically generated as (recs_decode_failed + recs_unknown_sub + recs_err_other).';
COMMENT ON COLUMN Recs_decode_failed IS 'Number of ‘record decoding failed’ errors. Default 0.';
COMMENT ON COLUMN Recs_unknown_sub   IS 'Number of ‘unknown subscriber’ errors. Default 0.';
COMMENT ON COLUMN Recs_err_other     IS 'Number of all other errors (e.g. DB error). Default 0.';
COMMENT ON COLUMN Sanity             IS 'Indicates whether sanity on the trailer record was done or not, and what was the outcome. Valid values:
														0 – sanity not done
														1- sanity done successful
														10 – sanity failed: wrong number of records
														11 – sanity failed: charges do not match
														12 – sanity failed; wrong number of records and charges do not match
														';
COMMENT ON COLUMN Sanity_desc        IS 'Detailed description in case of a failed sanity check.';

--primary key
ALTER TABLE CDRFILE
  ADD CONSTRAINT pk_cdrfile PRIMARY KEY (Id);

--index
CREATE INDEX FilenameIndex ON CDRFILE (File_name);
CREATE INDEX CreateDateIndex ON CDRFILE (Create_date);
--
PROMPT
PROMPT *** CDRFILE TABLE CREATED
PROMPT

-- SEQUENCE

CREATE SEQUENCE SEQ_CDRFILE_ID
	START WITH 1
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 999 999 999 999 999;

--alter tables
ALTER TABLE CDRCALL
ADD (SRV_SEQ NUMBER(15, 0),
     SESSION_ID VARCHAR(100),
     TRS_ID    NUMBER(15, 0));

PROMPT
PROMPT *** ADDED 3 COLUMNS TO CDRCALL TABLE
PROMPT

ALTER TABLE CDRCALLFRAGM
ADD (SRV_SEQ NUMBER(15, 0),
     SESSION_ID VARCHAR(100),
     TRS_ID    NUMBER(15, 0));

PROMPT
PROMPT *** ADDED 3 COLUMNS TO CDRCALLFRAGM TABLE
PROMPT

ALTER TABLE CDRPPR
ADD (SRV_SEQ NUMBER(15, 0),
     SESSION_ID VARCHAR(100),
     TRS_ID    NUMBER(15, 0));

PROMPT
PROMPT *** ADDED 3 COLUMNS TO CDRPPR TABLE
PROMPT

ALTER TABLE CDRPPRFRAGM
ADD (SRV_SEQ NUMBER(15, 0),
     SESSION_ID VARCHAR(100),
     TRS_ID    NUMBER(15, 0));

PROMPT
PROMPT *** ADDED 3 COLUMNS TO CDRPPRFRAGM TABLE
PROMPT

ALTER TABLE CDRMMS
ADD (SRV_SEQ NUMBER(15, 0),
     SESSION_ID VARCHAR(100),
     TRS_ID    NUMBER(15, 0));

PROMPT
PROMPT *** ADDED 3 COLUMNS TO CDRMMS TABLE
PROMPT

ALTER TABLE CDRSMS
ADD (SRV_SEQ NUMBER(15, 0),
     SESSION_ID VARCHAR(100),
     TRS_ID    NUMBER(15, 0));

PROMPT
PROMPT *** ADDED 3 COLUMNS TO CDRSMS TABLE
PROMPT

--

PROMPT
PROMPT *** OPERATION SUCCEEDED
PROMPT

EXIT;
-------------------------------------------------
--