--Order 20013918.20

-- Add/modify columns 
ALTER TABLE SQMATINCIRCULATION ADD (QTYNOMINALSTOCK NUMBER DEFAULT 0 NOT NULL);
ALTER TABLE SQMATINCIRCULATION ADD (QTYLOAN NUMBER DEFAULT 0 NOT NULL);
ALTER TABLE SQMATINCIRCULATION ADD (QTYCHARGEABLESTOCK NUMBER DEFAULT 0 NOT NULL);
ALTER TABLE SQMATINCIRCULATION ADD (QTYFREEOFCHARGE NUMBER DEFAULT 0 NOT NULL);


-- add type 454
ALTER TABLE DWH_SETTINGS
  DROP CONSTRAINT FISCALTYPE_VALID;
ALTER TABLE DWH_SETTINGS
  DROP CONSTRAINT PERIODTYPE_VALID;
ALTER TABLE DWH_SETTINGS
  ADD CONSTRAINT FISCALTYPE_VALID
  CHECK (FISCAL_TYPE IN (445, 544, 444, 454, 0));
ALTER TABLE DWH_SETTINGS
  ADD CONSTRAINT PERIODTYPE_VALID
  CHECK (PERIOD_TYPE IN (445, 544, 444, 454));

INSERT INTO DWH_SETTINGSPERTABLE (TABLENAME, ISFACTYN, LOADYN, SYSTEMUSER_ID, TIMESTAMP, SOURCESYSTEM)
VALUES ('WEEKLY_REVENUE', 'Y', 'N', 1, SYSDATE, 0);
COMMIT;
INSERT INTO DWH_SETTINGSPERTABLE (TABLENAME, ISFACTYN, LOADYN, SYSTEMUSER_ID, TIMESTAMP, SOURCESYSTEM)
VALUES ('CIRC_INV_ADJUST', 'Y', 'Y', 1, SYSDATE, 0);
COMMIT;

UPDATE DWH_STRING S 
   SET S.FACTORYDESCRIPTION = 'Fax'
 WHERE S.DWH_STRING_ID =241;
COMMIT;
UPDATE DWH_STRING S 
   SET S.FACTORYDESCRIPTION = 'E-mail'
 WHERE S.DWH_STRING_ID =242;
COMMIT;
UPDATE DWH_STRING_DESC S 
   SET S.STRING = 'Fax'
 WHERE S.DWH_STRING_ID =241;
COMMIT;
UPDATE DWH_STRING_DESC S 
   SET S.STRING = 'E-mail'
 WHERE S.DWH_STRING_ID =242;
COMMIT;


----INVOICELINEWEEK-----

CREATE TABLE INVOICELINEWEEK
(
  INVOICELINE_ID NUMBER NOT NULL,
  YEAR           NUMBER NOT NULL,
  WEEK           NUMBER NOT NULL,
  QUANTITYBILLED NUMBER NOT NULL,
  AMOUNTBILLED   NUMBER(15,2) NOT NULL,
  SYSTEMUSER_ID  NUMBER NOT NULL,
  TIMESTAMP      DATE NOT NULL
);

ALTER TABLE INVOICELINEWEEK
  ADD CONSTRAINT XPKINVOICELINEWEEK PRIMARY KEY (INVOICELINE_ID, YEAR, WEEK);

ALTER TABLE INVOICELINEWEEK
  ADD CONSTRAINT INVOICELINEWEEK_INVOICELINE FOREIGN KEY (INVOICELINE_ID)
  REFERENCES INVOICELINE (INVOICELINE_ID) ON DELETE CASCADE;
 
CREATE INDEX XIF985INVOICELINEWEEK ON INVOICELINEWEEK (INVOICELINE_ID);
