-- ABS-118194 BI analysis in web depending on invoice frequency / ABS-130086

-- create table WEBMENUDIALOGINVFREQUENCY
CREATE TABLE WEBMENUDIALOGINVFREQUENCY (
WEBMENUDIALOG_ID       NUMBER NOT NULL,                
INVOICEFREQUENCY_ID    NUMBER NOT NULL,
SYSTEMUSER             NUMBER NOT NULL,
TIMESTAMP              DATE NOT NULL
);

-- indexes, primary & foreign keys
ALTER TABLE WEBMENUDIALOGINVFREQUENCY
ADD CONSTRAINT PK_WEBMENUDIALOGINVFREQUENCY PRIMARY KEY (WEBMENUDIALOG_ID, INVOICEFREQUENCY_ID);

CREATE INDEX XIF1WEBMENUDIALOGINVFREQUENCY ON WEBMENUDIALOGINVFREQUENCY (WEBMENUDIALOG_ID ASC);
CREATE INDEX XIF2WEBMENUDIALOGINVFREQUENCY ON WEBMENUDIALOGINVFREQUENCY (INVOICEFREQUENCY_ID ASC);

ALTER TABLE WEBMENUDIALOGINVFREQUENCY
ADD CONSTRAINT FK_WEBMENUDIALOGINVFREQUENCY_WMENUD_ID FOREIGN KEY (WEBMENUDIALOG_ID) REFERENCES WEBMENUDIALOG (WEBMENUDIALOG_ID) ON DELETE CASCADE; 
ALTER TABLE WEBMENUDIALOGINVFREQUENCY
ADD CONSTRAINT FK_WEBMENUDIALOGINVFREQUENCY_INVFRQ_ID FOREIGN KEY (INVOICEFREQUENCY_ID) REFERENCES INVOICEFREQUENCY (INVOICEFREQUENCY_ID) ON DELETE CASCADE; 
    
-- alter table WEBMENUDIALOG 
ALTER TABLE WEBMENUDIALOG ADD (BISHOWINVOICEFREQUENCY NUMBER(1) DEFAULT 0 NOT NULL
CONSTRAINT CHK_WEBMENUDLG_BIINVFRQ CHECK (BISHOWINVOICEFREQUENCY IN (0, 1)));

COMMENT ON COLUMN WEBMENUDIALOG.BISHOWINVOICEFREQUENCY IS
'0 = All Invoice Frequencies
 1 = Only for specific invoice frequencies (in table WebMenuDialogInvFrequency)';
 
