/*ORDER 502219*/


create sequence SEQ_DWH_PRESENTATIONCATALOG
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 20;      
---------------------------------------------------------
      /*
      ACTION is CREATE Table DWH_PRESENTATIONCATALOG
      */

CREATE TABLE DWH_PRESENTATIONCATALOG (
       DWH_PRESENTATIONCATALOG_ID NUMBER NOT NULL,
       BUSINESSDESCRIPTION  VARCHAR2(80) NOT NULL,
       BUSINESSNAME         VARCHAR2(80) NOT NULL,
       FACTORYDISPLAYNAME   VARCHAR2(80) NOT NULL,
       ACTIVE_YN            VARCHAR2(1) DEFAULT 'Y' NOT NULL
                                   CONSTRAINT YN_ValidationNew128
                                          CHECK (ACTIVE_YN IN ('Y', 'N')),
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL
);

CREATE UNIQUE INDEX XAK1DWH_PRESENTATIONCATALOG ON DWH_PRESENTATIONCATALOG
(
       BUSINESSNAME                   ASC
);


ALTER TABLE DWH_PRESENTATIONCATALOG
       ADD  ( CONSTRAINT XPKDWH_PRESENTATIONCATALOG PRIMARY KEY (
              DWH_PRESENTATIONCATALOG_ID) ) ;

      
      /*
      ACTION is CREATE Table DWH_PRESENTATIONCATALOG_DESC
      */

CREATE TABLE DWH_PRESENTATIONCATALOG_DESC (
       DWH_PRESENTATIONCATALOG_ID NUMBER NOT NULL,
       LANGUAGECODE         VARCHAR2(2) NOT NULL,
       DISPLAYNAME          VARCHAR2(80) NOT NULL,
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL
);

CREATE INDEX XIF1DWH_PRESENTATIONCATALOG_DE ON DWH_PRESENTATIONCATALOG_DESC
(
       DWH_PRESENTATIONCATALOG_ID     ASC
);


ALTER TABLE DWH_PRESENTATIONCATALOG_DESC
       ADD  ( CONSTRAINT XPKDWH_PRESENTATIONCATALOG_DES PRIMARY KEY (
              DWH_PRESENTATIONCATALOG_ID, LANGUAGECODE) ) ;

---------------------------------------------------------      
      /*
      ACTION is CREATE Table DWH_SETTINGS
      */

CREATE TABLE DWH_SETTINGS (
       DWH_SETTINGS_ID      NUMBER DEFAULT 1 NOT NULL,
       YEAR_START_DATE      DATE DEFAULT TO_DATE('01-JAN-1990','DD-MM-YYYY') NOT NULL,
       NUMBER_OF_YEARS      NUMBER DEFAULT 30 NOT NULL,
       FISCAL_TYPE          NUMBER DEFAULT 544 NOT NULL
                                   CONSTRAINT FiscalType_valid
                                          CHECK (FISCAL_TYPE IN (445, 544)),
       FISCALYEAR_START_DATE DATE DEFAULT TO_DATE('01-JUL-1989','DD-MM-YYYY') NOT NULL,
       DAY_OF_FISCAL_WEEK   NUMBER DEFAULT 1 NOT NULL
                                   CONSTRAINT DayOfFiscalWeek_valid
                                          CHECK (DAY_OF_FISCAL_WEEK IN ('1', '2', '3', '4', '5', '6', '7')),
       LANGUAGE_ID          NUMBER NOT NULL,
       LOADPIMSYN           VARCHAR2(1) DEFAULT 'N' NOT NULL
                                   CONSTRAINT YN_ValidationNew129
                                          CHECK (LOADPIMSYN IN ('Y', 'N')),
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL
);

CREATE INDEX XIF1DWH_SETTINGS ON DWH_SETTINGS
(
       LANGUAGE_ID                    ASC
);


ALTER TABLE DWH_SETTINGS
       ADD  ( CONSTRAINT XPKDWH_SETTINGS PRIMARY KEY (DWH_SETTINGS_ID) ) ;

---------------------------------------------------------
      /*
      ACTION is CREATE Table DWH_SETTINGSPERTABLE
      */

CREATE TABLE DWH_SETTINGSPERTABLE (
       TABLENAME            VARCHAR2(30) NOT NULL,
       ISFACTYN             VARCHAR2(1) DEFAULT 'N' NOT NULL
                                   CONSTRAINT YN_ValidationNew130
                                          CHECK (ISFACTYN IN ('Y', 'N')),
       LOADYN               VARCHAR2(1) DEFAULT 'N' NOT NULL
                                   CONSTRAINT YN_ValidationNew131
                                          CHECK (LOADYN IN ('Y', 'N')),
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL
);


ALTER TABLE DWH_SETTINGSPERTABLE
       ADD  ( CONSTRAINT XPKDWH_SETTINGSPERTABLE PRIMARY KEY (
              TABLENAME) ) ;

---------------------------------------------------------    
      /*
      ACTION is CREATE Table DWH_STRING
      */

CREATE TABLE DWH_STRING (
       DWH_STRING_ID        NUMBER NOT NULL,
       FACTORYDESCRIPTION   VARCHAR2(60) NOT NULL,
       MAXSTRINGLENGTH      NUMBER NOT NULL,
       DIMENSION_TABLE      VARCHAR2(30) NOT NULL,
       DIMENSION_COLUMN     VARCHAR2(30) NULL,
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL
);


ALTER TABLE DWH_STRING
       ADD  ( CONSTRAINT XPKDWH_STRING PRIMARY KEY (DWH_STRING_ID) ) ;
---------------------------------------------------------
      
      /*
      ACTION is CREATE Table DWH_STRING_DESC
      */

CREATE TABLE DWH_STRING_DESC (
       DWH_STRING_ID        NUMBER NOT NULL,
       LANGUAGECODE         VARCHAR2(7) NOT NULL,
       STRING               VARCHAR2(60) NOT NULL,
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL
);

CREATE INDEX XIF1DWH_STRING_DESC ON DWH_STRING_DESC
(
       DWH_STRING_ID                  ASC
);


ALTER TABLE DWH_STRING_DESC
       ADD  ( CONSTRAINT XPKDWH_STRING_DESC PRIMARY KEY (
              DWH_STRING_ID, LANGUAGECODE) ) ;

----------------------------------------------------------
ALTER TABLE DWH_PRESENTATIONCATALOG_DESC
       ADD  ( CONSTRAINT DWH_PRESENTATIONCATALOG_D_DWH_
              FOREIGN KEY (DWH_PRESENTATIONCATALOG_ID)
                             REFERENCES DWH_PRESENTATIONCATALOG
                             ON DELETE CASCADE ) ;


ALTER TABLE DWH_SETTINGS
       ADD  ( CONSTRAINT DWH_SETTINGS_LANGUAGE_ID
              FOREIGN KEY (LANGUAGE_ID)
                             REFERENCES LANGUAGES ) ;


ALTER TABLE DWH_STRING_DESC
       ADD  ( CONSTRAINT DWH_STRING_D_DWH_STRING_ID
              FOREIGN KEY (DWH_STRING_ID)
                             REFERENCES DWH_STRING ) ;
----------------------------------------------------------
CREATE TABLE I_DWH_PRESENTATIONCATALOG (
       BUSINESSNAME          VARCHAR2(80) NOT NULL,
       BUSINESSDESCRIPTION   VARCHAR2(80) NOT NULL,
       ENGLISHNAME           VARCHAR2(80) NOT NULL
);

ALTER TABLE I_DWH_PRESENTATIONCATALOG
       ADD  ( CONSTRAINT XPK_I_DWH_PRESENTCATALOG PRIMARY KEY (BUSINESSNAME) );
-----------------------------------------------------------
CREATE TABLE I_DWH_PRESENTATIONCATALOG_DESC
(
  LANGUAGECODE        VARCHAR2(2)  not null,
  BUSINESSNAME        VARCHAR2(80) not null,
  DISPLAYNAME         VARCHAR2(80) not null
);

ALTER TABLE I_DWH_PRESENTATIONCATALOG_DESC
       ADD   ( CONSTRAINT XPK_I_DWH_PRESENTCATALOG_DESC PRIMARY KEY (LANGUAGECODE, BUSINESSNAME) );
-----------------------------------------------------------------
insert into DWH_STRING (DWH_STRING_ID, FACTORYDESCRIPTION, MAXSTRINGLENGTH, DIMENSION_TABLE, DIMENSION_COLUMN, SYSTEMUSER_ID, TIMESTAMP) select 1,'Yes',10,'General','', 1, sysdate from dual;
insert into DWH_STRING (DWH_STRING_ID, FACTORYDESCRIPTION, MAXSTRINGLENGTH, DIMENSION_TABLE, DIMENSION_COLUMN, SYSTEMUSER_ID, TIMESTAMP) select 2,'No',10,'General','', 1, sysdate from dual;
insert into DWH_STRING (DWH_STRING_ID, FACTORYDESCRIPTION, MAXSTRINGLENGTH, DIMENSION_TABLE, DIMENSION_COLUMN, SYSTEMUSER_ID, TIMESTAMP) select 3,'Unknown',10,'General','', 1, sysdate from dual;
insert into DWH_STRING (DWH_STRING_ID, FACTORYDESCRIPTION, MAXSTRINGLENGTH, DIMENSION_TABLE, DIMENSION_COLUMN, SYSTEMUSER_ID, TIMESTAMP) select 4,'Not Applicable',30,'General','', 1, sysdate from dual;
insert into DWH_STRING (DWH_STRING_ID, FACTORYDESCRIPTION, MAXSTRINGLENGTH, DIMENSION_TABLE, DIMENSION_COLUMN, SYSTEMUSER_ID, TIMESTAMP) select 5,'N/A',6,'General','', 1, sysdate from dual;
insert into DWH_STRING (DWH_STRING_ID, FACTORYDESCRIPTION, MAXSTRINGLENGTH, DIMENSION_TABLE, DIMENSION_COLUMN, SYSTEMUSER_ID, TIMESTAMP) select 6,'Not Defined',30,'General','', 1, sysdate from dual;
insert into DWH_STRING (DWH_STRING_ID, FACTORYDESCRIPTION, MAXSTRINGLENGTH, DIMENSION_TABLE, DIMENSION_COLUMN, SYSTEMUSER_ID, TIMESTAMP) select 7,'Obsolete',15,'General','', 1, sysdate from dual;
insert into DWH_STRING (DWH_STRING_ID, FACTORYDESCRIPTION, MAXSTRINGLENGTH, DIMENSION_TABLE, DIMENSION_COLUMN, SYSTEMUSER_ID, TIMESTAMP) select 8,'Active',15,'General','', 1, sysdate from dual;
insert into DWH_STRING (DWH_STRING_ID, FACTORYDESCRIPTION, MAXSTRINGLENGTH, DIMENSION_TABLE, DIMENSION_COLUMN, SYSTEMUSER_ID, TIMESTAMP) select 9,'Inactive',15,'General','', 1, sysdate from dual;
insert into DWH_STRING (DWH_STRING_ID, FACTORYDESCRIPTION, MAXSTRINGLENGTH, DIMENSION_TABLE, DIMENSION_COLUMN, SYSTEMUSER_ID, TIMESTAMP) select 10,'All',15,'General','', 1, sysdate from dual;
insert into DWH_STRING (DWH_STRING_ID, FACTORYDESCRIPTION, MAXSTRINGLENGTH, DIMENSION_TABLE, DIMENSION_COLUMN, SYSTEMUSER_ID, TIMESTAMP) select 30,'COD',30,'Customer','Payment Method', 1, sysdate from dual;
insert into DWH_STRING (DWH_STRING_ID, FACTORYDESCRIPTION, MAXSTRINGLENGTH, DIMENSION_TABLE, DIMENSION_COLUMN, SYSTEMUSER_ID, TIMESTAMP) select 31,'Charge',30,'Customer','Payment Method', 1, sysdate from dual;
insert into DWH_STRING (DWH_STRING_ID, FACTORYDESCRIPTION, MAXSTRINGLENGTH, DIMENSION_TABLE, DIMENSION_COLUMN, SYSTEMUSER_ID, TIMESTAMP) select 32,'Cash',30,'Customer','Payment Type', 1, sysdate from dual;
insert into DWH_STRING (DWH_STRING_ID, FACTORYDESCRIPTION, MAXSTRINGLENGTH, DIMENSION_TABLE, DIMENSION_COLUMN, SYSTEMUSER_ID, TIMESTAMP) select 33,'Check',30,'Customer','Payment Type', 1, sysdate from dual;
insert into DWH_STRING (DWH_STRING_ID, FACTORYDESCRIPTION, MAXSTRINGLENGTH, DIMENSION_TABLE, DIMENSION_COLUMN, SYSTEMUSER_ID, TIMESTAMP) select 34,'Credit Card',30,'Customer','Payment Type', 1, sysdate from dual;
insert into DWH_STRING (DWH_STRING_ID, FACTORYDESCRIPTION, MAXSTRINGLENGTH, DIMENSION_TABLE, DIMENSION_COLUMN, SYSTEMUSER_ID, TIMESTAMP) select 35,'Electronic Fund Transfer',30,'Customer','Payment Type', 1, sysdate from dual;
insert into DWH_STRING (DWH_STRING_ID, FACTORYDESCRIPTION, MAXSTRINGLENGTH, DIMENSION_TABLE, DIMENSION_COLUMN, SYSTEMUSER_ID, TIMESTAMP) select 36,'EDI',30,'Customer','Payment Type', 1, sysdate from dual;
insert into DWH_STRING (DWH_STRING_ID, FACTORYDESCRIPTION, MAXSTRINGLENGTH, DIMENSION_TABLE, DIMENSION_COLUMN, SYSTEMUSER_ID, TIMESTAMP) select 37,'Accept Giro',30,'Customer','Payment Type', 1, sysdate from dual;
insert into DWH_STRING (DWH_STRING_ID, FACTORYDESCRIPTION, MAXSTRINGLENGTH, DIMENSION_TABLE, DIMENSION_COLUMN, SYSTEMUSER_ID, TIMESTAMP) select 38,'Automatic Collection',30,'Customer','Payment Type', 1, sysdate from dual;
insert into DWH_STRING (DWH_STRING_ID, FACTORYDESCRIPTION, MAXSTRINGLENGTH, DIMENSION_TABLE, DIMENSION_COLUMN, SYSTEMUSER_ID, TIMESTAMP) select 39,'Via pay-by Customer',30,'Customer','Payment Type', 1, sysdate from dual;
insert into DWH_STRING (DWH_STRING_ID, FACTORYDESCRIPTION, MAXSTRINGLENGTH, DIMENSION_TABLE, DIMENSION_COLUMN, SYSTEMUSER_ID, TIMESTAMP) select 50,'From Plant',30,'Route','Depot', 1, sysdate from dual;
insert into DWH_STRING (DWH_STRING_ID, FACTORYDESCRIPTION, MAXSTRINGLENGTH, DIMENSION_TABLE, DIMENSION_COLUMN, SYSTEMUSER_ID, TIMESTAMP) select 51,'Not linked to Route',30,'Route','Route Description', 1, sysdate from dual;
insert into DWH_STRING (DWH_STRING_ID, FACTORYDESCRIPTION, MAXSTRINGLENGTH, DIMENSION_TABLE, DIMENSION_COLUMN, SYSTEMUSER_ID, TIMESTAMP) select 60,'Non Garments',30,'Product','Type of Merchandize', 1, sysdate from dual;
insert into DWH_STRING (DWH_STRING_ID, FACTORYDESCRIPTION, MAXSTRINGLENGTH, DIMENSION_TABLE, DIMENSION_COLUMN, SYSTEMUSER_ID, TIMESTAMP) select 61,'Garments',30,'Product','Type of Merchandize', 1, sysdate from dual;
insert into DWH_STRING (DWH_STRING_ID, FACTORYDESCRIPTION, MAXSTRINGLENGTH, DIMENSION_TABLE, DIMENSION_COLUMN, SYSTEMUSER_ID, TIMESTAMP) select 62,'Sales-only Items/Disposables',30,'Product','Type of Merchandize', 1, sysdate from dual;
insert into DWH_STRING (DWH_STRING_ID, FACTORYDESCRIPTION, MAXSTRINGLENGTH, DIMENSION_TABLE, DIMENSION_COLUMN, SYSTEMUSER_ID, TIMESTAMP) select 63,'Emblems',30,'Product','Type of Merchandize', 1, sysdate from dual;
insert into DWH_STRING (DWH_STRING_ID, FACTORYDESCRIPTION, MAXSTRINGLENGTH, DIMENSION_TABLE, DIMENSION_COLUMN, SYSTEMUSER_ID, TIMESTAMP) select 64,'Residential Garments',30,'Product','Type of Merchandize', 1, sysdate from dual;
insert into DWH_STRING (DWH_STRING_ID, FACTORYDESCRIPTION, MAXSTRINGLENGTH, DIMENSION_TABLE, DIMENSION_COLUMN, SYSTEMUSER_ID, TIMESTAMP) select 65,'Not Soil Counted',20,'Product','Soil Count Method', 1, sysdate from dual;
insert into DWH_STRING (DWH_STRING_ID, FACTORYDESCRIPTION, MAXSTRINGLENGTH, DIMENSION_TABLE, DIMENSION_COLUMN, SYSTEMUSER_ID, TIMESTAMP) select 66,'Manual',20,'Product','Soil Count Method', 1, sysdate from dual;
insert into DWH_STRING (DWH_STRING_ID, FACTORYDESCRIPTION, MAXSTRINGLENGTH, DIMENSION_TABLE, DIMENSION_COLUMN, SYSTEMUSER_ID, TIMESTAMP) select 67,'Auto Return Assumed',20,'Product','Soil Count Method', 1, sysdate from dual;
insert into DWH_STRING (DWH_STRING_ID, FACTORYDESCRIPTION, MAXSTRINGLENGTH, DIMENSION_TABLE, DIMENSION_COLUMN, SYSTEMUSER_ID, TIMESTAMP) select 68,'Weight Based',20,'Product','Soil Count Method', 1, sysdate from dual;
insert into DWH_STRING (DWH_STRING_ID, FACTORYDESCRIPTION, MAXSTRINGLENGTH, DIMENSION_TABLE, DIMENSION_COLUMN, SYSTEMUSER_ID, TIMESTAMP) select 69,'Delayed Even Exchange',40,'Product','Exchange Method', 1, sysdate from dual;
insert into DWH_STRING (DWH_STRING_ID, FACTORYDESCRIPTION, MAXSTRINGLENGTH, DIMENSION_TABLE, DIMENSION_COLUMN, SYSTEMUSER_ID, TIMESTAMP) select 70,'Straight Even Exchange',40,'Product','Exchange Method', 1, sysdate from dual;
insert into DWH_STRING (DWH_STRING_ID, FACTORYDESCRIPTION, MAXSTRINGLENGTH, DIMENSION_TABLE, DIMENSION_COLUMN, SYSTEMUSER_ID, TIMESTAMP) select 71,'Delivery based on order/standards',40,'Product','Exchange Method', 1, sysdate from dual;
insert into DWH_STRING (DWH_STRING_ID, FACTORYDESCRIPTION, MAXSTRINGLENGTH, DIMENSION_TABLE, DIMENSION_COLUMN, SYSTEMUSER_ID, TIMESTAMP) select 80,'New',10,'QualityGrade','New Used', 1, sysdate from dual;
insert into DWH_STRING (DWH_STRING_ID, FACTORYDESCRIPTION, MAXSTRINGLENGTH, DIMENSION_TABLE, DIMENSION_COLUMN, SYSTEMUSER_ID, TIMESTAMP) select 81,'Used',10,'QualityGrade','New Used', 1, sysdate from dual;


------------------------------------------------------------------
insert into dwh_string_desc 
  (dwh_string_id,
   languagecode,
   string,
   systemuser_id,
   timestamp)
  select i.dwh_string_id,
         'EN_US',
         i.factorydescription,
         1,
         sysdate
    from dwh_string i;







