gistfile1.sql

BEGIN TRAN

CREATE Table CdrPresentation ( 
	CdrPresentationId INT NOT NULL IDENTITY(1,1),
	
	Description VARCHAR(250) NOT NULL,
	ChargePerNumber NUMERIC(7,4) NOT NULL,
	ChargeIntervall BIGINT NOT NULL,
	BaseUnit char(1) NOT NULL,
	ChargePerUnit NUMERIC(7,4) NOT NULL,
	ChargeUnit BIGINT NOT NULL,
	VolumeInterval BIGINT,
	
	CONSTRAINT PK_CdrPresentation PRIMARY KEY (CdrPresentationId),
);

CREATE TABLE ClaimClientNetworkContract (
  BatchId INT NOT NULL,
  UniqueId INT NOT NULL,
  NetworkContractNr TINYINT NOT NULL,
  ClientNr numeric(7,0) NOT NULL,
  
  ContractClientNr NUMERIC(7,0) NOT NULL,
  ContractContractNr NUMERIC(9, 0) NOT NULL,
  
  /* This allows us to rate a VERY expensive call, and still small anough to at least rate
     the cost of one single byte at a price of 0.04kr / MB and two extra decimals.
     19 in precision takes 9 bytes, the nest step (20 and above tkaes 13,
     9 in precision is previous step and take 5 bytes, but is to small  */
  Amount NUMERIC(19, 10) NULL,
  AmountVatPercent TINYINT NULL,
  
  CdrPresentationId INT NULL,
  InvoiceNr Numeric(9,0) NULL,  
  
  CONSTRAINT PK_Claims PRIMARY KEY (Batchid, UniqueId, NetworkContractNr, ClientNr),
  CONSTRAINT FK_Claim_Cdr FOREIGN KEY (BatchId,UniqueId) REFERENCES Cdr (BatchId,UniqueId),
  CONSTRAINT FK_Claim_ClientNetworkContract FOREIGN KEY (ClientNr,NetworkContractNr) REFERENCES ClientNetworkContract (ClientNr,NetworkContractNr),
  CONSTRAINT FK_Claim_Contract FOREIGN KEY (ContractClientNr, ContractContractNr) REFERENCES Contract (ClientNr, ContractNr),
  CONSTRAINT FK_Claim_CdrPresentation FOREIGN KEY (CdrPresentationId) REFERENCES CdrPresentation (CdrPresentationId),
  CONSTRAINT FK_Claim_Invoice FOREIGN KEY (ContractClientNr, InvoiceNr) REFERENCES Invoice (ClientNr, InvoiceNr)
);


CREATE NONCLUSTERED INDEX FIX_ClaimClientNetworkContract_Contract
    ON dbo.ClaimClientNetworkContract (ContractClientNr, ContractContractNr)
    --INCLUDE (BatchId, UniqueId, NetworkContractNr, ClientNr)
    WHERE InvoiceNr IS NULL;
GO

ALTER TABLE ClientNetworkContract ALTER COLUMN ClientNr numeric(7,0) NOT NULL
ALTER TABLE ClientNetworkContract ALTER COLUMN ContractClientNr numeric(7,0) NOT NULL
IF NOT (EXISTS (SELECT * FROM Sys.objects WHERE NAME like 'UID_ClientNetworkContract')) BEGIN
  ALTER TABLE ClientNetworkContract ADD CONSTRAINT UID_ClientNetworkContract UNIQUE (ClientNr, NetworkContractNr, ContractClientNr)
END


CREATE TABLE ClientNetworkContractPrice (
	ClientNr Numeric(7,0) NOT NULL,
	NetworkContractNr TINYINT NOT NULL,
	ContractClientNr Numeric(7,0) NOT NULL,
	PriceListCode VARCHAR(16) NOT NULL,
	ActiveFrom DateTime NOT NULL,
	ActiveTo DateTime NULL,
	
	CONSTRAINT PK_ClientNetworkContractPrice PRIMARY KEY (ClientNr, NetworkContractNr),
	CONSTRAINT FK_ClientNetworkContractPrice_ClientNetworkContract FOREIGN KEY (ClientNr, NetworkContractNr, ContractClientNr) REFERENCES ClientNetworkContract (ClientNr, NetworkContractNr, ContractClientNr),
	CONSTRAINT FK_ClientNetworkContractPrice_PriceList FOREIGN KEY (ContractClientNr, PriceListCode) REFERENCES PriceList (ClientNr, PriceListCode),
);

ALTER TABLE NetworkContract ALTER COLUMN ClientNr numeric(7,0) NOT NULL
IF NOT (EXISTS (SELECT * FROM Sys.objects WHERE NAME like 'UID_NetworkContract')) BEGIN
  ALTER TABLE NetworkContract ADD CONSTRAINT UID_NetworkContract UNIQUE (NetworkContractNr, ClientNr)
END


CREATE TABLE NetworkContractPrice (
	NetworkContractNr TINYINT NOT NULL,
	ClientNr numeric(7,0) NOT NULL,
	PriceListCode VARCHAR(16) NOT NULL,
	ActiveFrom DateTime NOT NULL,
	ActiveTo DateTime NULL,
	
	CONSTRAINT PK_NetworkContractPrice PRIMARY KEY (NetworkContractNr),
	CONSTRAINT FK_NetworkContractPrice_NetworkContract FOREIGN KEY (NetworkContractNr, ClientNr) REFERENCES NetworkContract (NetworkContractNr, ClientNr),
	CONSTRAINT FK_NetworkContractPrice_PriceList FOREIGN KEY (ClientNr, PriceListCode) REFERENCES PriceList (ClientNr, PriceListCode)
);

CREATE TABLE ClaimEnduser (
  BatchId INT NOT NULL,
  UniqueId INT NOT NULL,
  ClientNr numeric(7,0) NOT NULL,
  ContractNr numeric(9,0) NOT NULL,

  Amount NUMERIC(19, 10) NULL,
  AmountVatPercent TINYINT NULL,
  
  CdrPresentationId INT NULL,
  
  InvoiceNr Numeric(9,0) NULL,  
  
  CONSTRAINT PK_ClaimEnduser PRIMARY KEY (Batchid, UniqueId),
  CONSTRAINT FK_ClaimEnduser_Cdr FOREIGN KEY (BatchId,UniqueId) REFERENCES Cdr (BatchId,UniqueId),
  CONSTRAINT FK_ClaimEnduser_Contract FOREIGN KEY (ClientNr, ContractNr) REFERENCES Contract (ClientNr, ContractNr),
  CONSTRAINT FK_ClaimEnduser_CdrPresentation FOREIGN KEY (CdrPresentationId) REFERENCES CdrPresentation (CdrPresentationId),
  CONSTRAINT FK_ClaimEnduser_Invoice FOREIGN KEY (ClientNr, InvoiceNr) REFERENCES Invoice (ClientNr, InvoiceNr)  
);

CREATE NONCLUSTERED INDEX FIX_ClaimEnduser_Contract
    ON dbo.ClaimEnduser (ClientNr, ContractNr)
    --INCLUDE (BatchId, UniqueId)
    WHERE InvoiceNr IS NULL;
GO


CREATE TABLE ClaimNetworkContract (
  BatchId INT NOT NULL,
  UniqueId INT NOT NULL,
  NetworkContractNr TINYINT NOT NULL,
 
  Amount NUMERIC(19, 10) NULL,
  AmountVatPercent TINYINT NULL,
  
  CdrPresentationId INT NULL,
  
  CONSTRAINT PK_ClaimNetworkContract PRIMARY KEY (Batchid, UniqueId, NetworkContractNr),
  CONSTRAINT FK_ClaimNetworkContract_Cdr FOREIGN KEY (BatchId,UniqueId) REFERENCES Cdr (BatchId,UniqueId),
  CONSTRAINT FK_ClaimNetworkContract_NetworkContract FOREIGN KEY (NetworkContractNr) REFERENCES NetworkContract (NetworkContractNr),
  CONSTRAINT FK_ClaimNetworkContract_CdrPresentation FOREIGN KEY (CdrPresentationId) REFERENCES CdrPresentation (CdrPresentationId)
);

ROLLBACK