CREATE TABLE Actions ;

CREATE TABLE AuditEvent (
        AuditEventSeq       NUMBER(10) NOT NULL, 
        AuditEventName      VARCHAR2(20) NOT NULL,
        PRIMARY KEY (AuditEventSeq)
        );

CREATE TABLE Config (
        Parameter           VARCHAR2(20) NOT NULL, 
        pValue              VARCHAR2(20), 
        PRIMARY KEY(Parameter)); 

CREATE TABLE DeferredTaxCategories (
        DefTaxCategoriesSeq NUMBER(10) NOT NULL, 
        CategoryName        VARCHAR2(50),  
        PRIMARY KEY(DefTaxCategoriesSeq));

CREATE TABLE User (
        UserSeq             NUMBER(10) NOT NULL, 
        UserID              CHAR(6) NOT NULL, 
        FirstName           VARCHAR2(20), 
        LastName            VARCHAR2(30), 
        PhoneNumber         VARCHAR2(30), 
        FaxNumber           VARCHAR2(30), 
        Email               VARCHAR2(50), 
        Active              NUMBER(1) DEFAULT 1 NOT NULL,
        PRIMARY KEY (UserSeq));
		
CREATE TABLE ForeignEntity (
        ForeignEntitySeq    NUMBER(10) NOT NULL, 
        TBC                 NUMBER(10) NOT NULL, 
        SubsidiaryName      VARCHAR2(50), 
        Active              NUMBER(1) DEFAULT 1 NOT NULL,
        PRIMARY KEY(ForeignEntitySeq)) ;

CREATE TABLE Objects (
        ObjectSeq           NUMBER(10) NOT NULL,
        ObjectName          VARCHAR2(30) NOT NULL, 
        PRIMARY KEY(ObjectSeq));
     
CREATE TABLE Permissions (
        UserGroupSeq        NUMBER(10) NOT NULL, 
        ObjectSeq           NUMBER(10) NOT NULL, 
        ActionSeq           NUMBER(10) NOT NULL, 
        FOREIGN KEY(ActionSeq) 
            	REFERENCES Actions(ActionSeq) ON DELETE CASCADE, 
        FOREIGN KEY(ObjectSeq) 
     		REFERENCES Objects(ObjectSeq) ON DELETE CASCADE, 
        FOREIGN KEY(UserGroupSeq) 
     		REFERENCES UserGroup(UserGroupSeq) ON DELETE CASCADE,
        PRIMARY KEY (UserGroupSeq, ObjectSeq, ActionSeq)    	
        	); 

CREATE TABLE Schedule20 (
        Schedule20Seq               NUMBER(10) NOT NULL PRIMARY KEY, 
        ForeignEntitySeq            NUMBER(10) NOT NULL, 
        ScheduleYear                NUMBER(10) NOT NULL, 
        TaxRate                     NUMBER(2, 4) DEFAULT 0 NOT NULL, 
        TotalLoss                   NUMBER(12) DEFAULT 0 NOT NULL, 
        TotalTaxEffect              NUMBER(12) DEFAULT 0 NOT NULL, 
        TotalDeferredTaxes          NUMBER(12) DEFAULT 0 NOT NULL, 
        TotalValuationRequired      NUMBER(12) DEFAULT 0 NOT NULL, 
        TotalValuationRecorded      NUMBER(12) DEFAULT 0 NOT NULL, 
        CarrybacksCarrybacksYN      NUMBER(1) DEFAULT 0 NOT NULL, 
        CarrybackScheduleYears      NUMBER(4), 
        CarryforwardsCarrybacksYN   NUMBER(1) DEFAULT 0 NOT NULL, 
        CarryforwardScheduleYears   NUMBER(4), 
        Status                      NUMBER(1) DEFAULT 1 NOT NULL, 
        FOREIGN KEY(ForeignEntitySeq) 
        		REFERENCES ForeignEntity(ForeignEntitySeq)
        );   
    
CREATE TABLE Schedule20CarryforwardS (
        Schedule20Seq               NUMBER(10) NOT NULL, 
        LineNumber                  NUMBER(10) NOT NULL, 
        ScheduleYearOccured         NUMBER(4) DEFAULT 0 NOT NULL, 
        ScheduleYearExpores         NUMBER(4) DEFAULT 0 NOT NULL, 
        Loss                        NUMBER(12) DEFAULT 0 NOT NULL, 
        TaxEffect                   NUMBER(12) DEFAULT 0 NOT NULL, 
        DeferredTaxES               NUMBER(12) DEFAULT 0 NOT NULL, 
        ValuationRequired           NUMBER(12) DEFAULT 0 NOT NULL, 
        ValuationRecorded           NUMBER(12) DEFAULT 0 NOT NULL, 
        FOREIGN KEY(Schedule20Seq) 
        		REFERENCES Schedule20(Schedule20Seq) ON DELETE CASCADE,
        PRIMARY KEY (Schedule20Seq, LineNumber));

CREATE TABLE Schedule20UserAudit (
        Schedule20Seq NUMBER(10)    NOT NULL, 
        AuditEventSeq NUMBER(10)    NOT NULL, 
        UserSeq NUMBER(10)          NOT NULL,
        TimeStamp DATE              NOT NULL,
        FOREIGN KEY(AuditEventSeq) 
        		REFERENCES AuditEvent(AuditEventSeq),
        FOREIGN KEY(Schedule20Seq) 
        		REFERENCES Schedule20(Schedule20Seq) ON DELETE CASCADE,
        FOREIGN KEY(UserSeq)
    		REFERENCES User(UserSeq),
        PRIMARY KEY (Schedule20Seq, AuditEventSeq) );

CREATE TABLE Schedule21Version (
        Schedule21VersionSeq        NUMBER(10) NOT NULL, 
        Schedule21VersionName       VARCHAR2(50), 
        PRIMARY KEY(Schedule21VersionSeq));
    
CREATE TABLE Schedule21 (
        Schedule21Seq               NUMBER(10) NOT NULL, 
        ForeignEntitySeq            NUMBER(10) NOT NULL, 
        ScheduleYear                NUMBER(10) DEFAULT 0 NOT NULL, 
        Months                      VARCHAR2(10), 
        Schedule21VersionSeq        NUMBER(10) NOT NULL,
        PreTaxIncome                NUMBER(12) DEFAULT 0 NOT NULL, 
        PreTaxGAAP                  NUMBER(12) DEFAULT 0 NOT NULL, 
        PermanentAdjustmentTotal    NUMBER(12) DEFAULT 0 NOT NULL,
        NonStdAdjustmentTotal       NUMBER(12) DEFAULT 0 NOT NULL, 
        SubTotalC                   NUMBER(12) DEFAULT 0 NOT NULL, 
        TaxRate                     NUMBER(2, 4) DEFAULT 0 NOT NULL, 
        HolidayYN                   NUMBER(1) DEFAULT 0 NOT NULL, 
        HolidayRate                 NUMBER(2, 4) NOT NULL, 
        HolidayScheduleYears        VARCHAR2(20), 
        SubTotalID                  NUMBER(12) DEFAULT 0 NOT NULL, 
        SubTotalTaxCredits          NUMBER(12) DEFAULT 0 NOT NULL, 
        SubTotalTaxProvision        NUMBER(12) DEFAULT 0 NOT NULL, 
        SubTotalOther               NUMBER(12) DEFAULT 0 NOT NULL, 
        TotalGAAP                   NUMBER(12) DEFAULT 0 NOT NULL, 
        EffectiveTaxRate            NUMBER(2, 4) DEFAULT 0 NOT NULL, 
        Status                      NUMBER(1) DEFAULT 1 NOT NULL,
        FOREIGN KEY(ForeignEntitySeq) 
        		REFERENCES ForeignEntity(ForeignEntitySeq), 
        FOREIGN KEY(Schedule21VersionSeq) 
        		REFERENCES Schedule21Version(Schedule21VersionSeq),
        PRIMARY KEY(Schedule21Seq) );
 
CREATE TABLE Schedule21Adjustment (
        Schedule21AdjustmentSeq     NUMBER(10) NOT NULL, 
        Schedule21AdjustmentName    VARCHAR2(20), 
        PRIMARY KEY (Schedule21AdjustmentSeq));

CREATE TABLE Schedule21AdjustmentXREF (
        Schedule21Seq               NUMBER(10) NOT NULL, 
        Schedule21AdjustmentSeq     NUMBER(10) NOT NULL, 
        LineNumber                  NUMBER(2) NOT NULL, 
        LineText                    VARCHAR2(50), 
        LineValue                   NUMBER(12) NOT NULL, 
        FOREIGN KEY(Schedule21AdjustmentSeq) 
        		REFERENCES Schedule21Adjustment(Schedule21AdjustmentSeq),
        FOREIGN KEY(Schedule21Seq) 
        		REFERENCES Schedule21(Schedule21Seq),
        PRIMARY KEY (Schedule21Seq, Schedule21AdjustmentSeq, LineNumber));

CREATE TABLE Schedule21UserAudit (
        Schedule21Seq               NUMBER(10) NOT NULL, 
        AuditEventSeq               NUMBER(10) NOT NULL, 
        UserSeq                     NUMBER(10) NOT NULL, 
        TimeStamp                   DATE NOT NULL, 
        FOREIGN KEY(Schedule21Seq) 
        		REFERENCES Schedule21(Schedule21Seq) ON DELETE CASCADE,
        FOREIGN KEY(AuditEventSeq) 
        		REFERENCES AuditEvent(AuditEventSeq),
        FOREIGN KEY(UserSeq) 
        		REFERENCES User(UserSeq),
        PRIMARY KEY (Schedule21Seq, AuditEventSeq) );

CREATE TABLE Schedule7 (
        Schedule7Seq                NUMBER(10) NOT NULL, 
        ForeignEntitySeq            NUMBER(10) NOT NULL, 
        ScheduleYear                NUMBER(10) DEFAULT 0 NOT NULL, 
        TaxRate                     NUMBER(10) DEFAULT 0 NOT NULL, 
        SubTotalAssets              NUMBER(10) DEFAULT 0 NOT NULL, 
        SubTotalAssetsTax           NUMBER(10) DEFAULT 0 NOT NULL, 
        SubTotalLiabilities         NUMBER(10) DEFAULT 0 NOT NULL, 
        SubTotalLiabilitiesTax      NUMBER(10) DEFAULT 0 NOT NULL,
        TotalAssets                 NUMBER(10) DEFAULT 0 NOT NULL, 
        ValidationAllowAssets       NUMBER(10) DEFAULT 0 NOT NULL, 
        TotalAssetsTax              NUMBER(10) DEFAULT 0 NOT NULL, 
        TotalLiabilities            NUMBER(10) DEFAULT 0 NOT NULL, 
        ValidationAllowLiab         NUMBER(10) DEFAULT 0 NOT NULL, 
        TotalLiabTax                NUMBER(10) DEFAULT 0 NOT NULL, 
        NetTax                      NUMBER(10) DEFAULT 0 NOT NULL, 
        Status                      NUMBER(10) DEFAULT 0 NOT NULL, 
        FOREIGN KEY(ForeignEntitySeq) 
                REFERENCES ForeignEntity(ForeignEntitySeq), 
        PRIMARY KEY(Schedule7Seq));

CREATE TABLE Schedule7DEFTaxCATXREF (
        Schedule7Seq                NUMBER(10) NOT NULL, 
        DefTaxCategoriesSeq         NUMBER(10) NOT NULL, 
        Asset                       NUMBER(12) DEFAULT 0 NOT NULL, 
        AssetTax                    NUMBER(12) DEFAULT 0 NOT NULL, 
        Liability                   NUMBER(12) DEFAULT 0 NOT NULL, 
        LiabilityTax                NUMBER(12) DEFAULT 0 NOT NULL, 
        FOREIGN KEY(DefTaxCategoriesSeq) 
        		REFERENCES DeferredTaxCategories(DefTaxCategoriesSeq),
        FOREIGN KEY(Schedule7Seq) 
        		REFERENCES Schedule7(Schedule7Seq) ON DELETE CASCADE,
        PRIMARY KEY (Schedule7Seq, DefTaxCategoriesSeq));

CREATE TABLE Schedule7OTHDEFTaxCAT  (
        Schedule7Seq                NUMBER(10) NOT NULL, 
        LineNumber                  NUMBER(10) NOT NULL, 
        DEFTaxCATName               VARCHAR2(50),
        Asset                       NUMBER(12) DEFAULT 0 NOT NULL, 
        AssetTax                    NUMBER(12) DEFAULT 0 NOT NULL, 
        Liability                   NUMBER(12) DEFAULT 0 NOT NULL, 
        LiabilityTax                NUMBER(12) DEFAULT 0 NOT NULL, 
        FOREIGN KEY(Schedule7Seq) 
        		REFERENCES Schedule7(Schedule7Seq) ON DELETE CASCADE,
        PRIMARY KEY (Schedule7Seq, LineNumber));

CREATE TABLE Schedule7UserAudit (
        Schedule7Seq                NUMBER(10) NOT NULL, 
        AuditEventSeq               NUMBER(10) NOT NULL, 
        UserSeq                     NUMBER(10) NOT NULL, 
        TimeStamp                   DATE NOT NULL,
        FOREIGN KEY(AuditEventSeq) 
        		REFERENCES AuditEvent(AuditEventSeq),
        FOREIGN KEY(Schedule7Seq) 
        		REFERENCES Schedule7(Schedule7Seq) ON DELETE CASCADE,
        PRIMARY KEY (Schedule7Seq, AuditEventSeq)); 

CREATE TABLE UserForeignEntity (
        ForeignEntitySeq            NUMBER(10) NOT NULL, 
        UserSeq                     NUMBER(10) NOT NULL, 
        FOREIGN KEY(ForeignEntitySeq) 
                REFERENCES ForeignEntity(ForeignEntitySeq) ON DELETE CASCADE,
        FOREIGN KEY(UserSeq) 
                REFERENCES User(UserSeq) ON DELETE CASCADE,
        PRIMARY KEY (ForeignEntitySeq, UserSeq)); 

CREATE TABLE UserGroup (
        UserGroupSeq                NUMBER(10) NOT NULL, 
        UserGroupName               VARCHAR2(20) NOT NULL, 
        PRIMARY KEY(UserGroupSeq));

CREATE TABLE UserGroupXREF (
        UserGroupSeq                NUMBER(10) NOT NULL, 
        UserSeq                     NUMBER(10) NOT NULL, 
        FOREIGN KEY(UserGroupSeq) 
        		REFERENCES UserGroup(UserGroupSeq) ON DELETE CASCADE,
        FOREIGN KEY(UserSeq)
    		REFERENCES User(UserSeq) ON DELETE CASCADE,
        PRIMARY KEY (UserGroupSeq,UserSeq));
