/*order 502472*/

ALTER TABLE CONTACTPERSON ADD (EXTERNALREFERENCEID NUMBER NULL);

------------------------------
CREATE TABLE WEBCATCUSTOMEREXP
(
  CUSTOMER_ID            NUMBER NOT NULL,
  ACTIONCODE             VARCHAR2(1) DEFAULT 'N' NOT NULL,
				CONSTRAINT ACTIONCODE_Validation
                                          CHECK (ACTIONCODE IN ('N', 'U')),
  CUSTOMERNUMBER         NUMBER NOT NULL,
  NAME                   VARCHAR2(40) NOT NULL,
  ORGANIZATIONSTATUS     VARCHAR2(1) DEFAULT 'A' NOT NULL
				CONSTRAINT ORGANIZATIONSTATUS_Validation
                                          CHECK (ORGANIZATIONSTATUS IN ('A', 'I')),
  ADDRESSID              NUMBER NOT NULL,
  ADDRESS1               VARCHAR2(30) NOT NULL,
  ADDRESS2               VARCHAR2(30) NULL,
  CITY                   VARCHAR2(30) NOT NULL,
  STATE                  VARCHAR2(20) NOT NULL,
  COUNTY                 VARCHAR2(20) NULL,
  POSTALCODE             VARCHAR2(15) NOT NULL,
  COUNTRY                VARCHAR2(2) NOT NULL,
  BRANCHCODE             VARCHAR2(3) NOT NULL,
  PAYONACCOUNT           VARCHAR2(1) DEFAULT 'Y' NOT NULL
				 CONSTRAINT YN_ValidationNew164
                                          CHECK (PAYONACCOUNT IN ('Y', 'N')),
  CATALOGDISCOUNTBAND    NUMBER DEFAULT 0 NOT NULL,
  DECORATIONDISCOUNTBAND NUMBER DEFAULT 0 NOT NULL,
  TAXEXEMPTID            VARCHAR2(18) NULL,
  MICROSITECODE          VARCHAR2(6) NOT NULL,
  CONTRACT               VARCHAR2(30) NOT NULL,
  PROCESSEDFLAG          VARCHAR2(1) DEFAULT 'N' NOT NULL
				 CONSTRAINT PROCESSEDFLAG_Validation1
					  CHECK (PROCESSEDFLAG IN ('N','Y','E')),
  TIMESTAMP              DATE DEFAULT SYSDATE NOT NULL
);

COMMENT ON COLUMN WEBCATCUSTOMEREXP.ACTIONCODE IS '''N'' for New or ''U'' for update';

COMMENT ON COLUMN WEBCATCUSTOMEREXP.ORGANIZATIONSTATUS IS 'Status ''A''=Active, ''I''=Inactive';

COMMENT ON COLUMN WEBCATCUSTOMEREXP.PAYONACCOUNT IS 'Y=True,
 N=False';

COMMENT ON COLUMN WEBCATCUSTOMEREXP.PROCESSEDFLAG IS 'N=Not processed (when created)
 Y=Processed
 E=Error';

ALTER TABLE WEBCATCUSTOMEREXP
  ADD ( CONSTRAINT XPKWEBCATCUSTOMEREXP PRIMARY KEY (CUSTOMER_ID) );

CREATE INDEX XIE1WEBCATCUSTOMEREXP ON WEBCATCUSTOMEREXP (PROCESSEDFLAG);

-----------------------------------
CREATE TABLE WEBCATINVOICEHEADERIMP
(
  PAYLOADID                    VARCHAR2(40) NOT NULL,
  PAYMENTID                    VARCHAR2(10) NOT NULL,
  ORDERNUMBER                  NUMBER NOT NULL,
  CUSTOMERORDERNUMBER          VARCHAR2(30)NULL,
  SETTLEMENTDATE               DATE NOT NULL,
  ORDERDATE                    DATE NOT NULL,
  CURRENCY                     VARCHAR2(3) NOT NULL,
  SETTLEMENTTYPE               VARCHAR2(1) DEFAULT 'S' NOT NULL
				  CONSTRAINT SETTLEMENTTYPE_Validation
					CHECK (SETTLEMENTTYPE IN ('S','A')),
  INVOICETOTAL                 NUMBER(15,2) NOT NULL,
  INVOICEDISCOUNT              NUMBER(15,2) NOT NULL,
  FREIGHTTOTAL                 NUMBER(15,2) NOT NULL,
  FREIGHTDISCOUNT              NUMBER(15,2) NOT NULL,
  FREIGHTTAX                   NUMBER(15,2) NOT NULL,
  TAXTOTAL                     NUMBER(15,2) NOT NULL,
  CUSTOMERNUMBER               NUMBER NOT NULL,
  CUSTOMERNAME                 VARCHAR2(80) NOT NULL,
  BILLTOADDRESSID              NUMBER NOT NULL,
  BILLTOADDRESSSTREET1         VARCHAR2(240) NOT NULL,
  BILLTOADDRESSSTREET2         VARCHAR2(240) NULL,
  BILLTOADDRESSCITY            VARCHAR2(60) NOT NULL,
  BILLTOADDRESSSTATE           VARCHAR2(2) NOT NULL,
  BILLTOADDRESSZIPCODE         VARCHAR2(15) NOT NULL,
  BILLTOADDRESSCOUNTY          VARCHAR2(80) NULL,
  BILLTOCONTACTNAME            VARCHAR2(80) NULL,
  SHIPTOADDRESSID              NUMBER NOT NULL,
  SHIPTOADDRESSSTREET1         VARCHAR2(240) NOT NULL,
  SHIPTOADDRESSSTREET2         VARCHAR2(240) NULL,
  SHIPTOADDRESSCITY            VARCHAR2(60) NOT NULL,
  SHIPTOADDRESSSTATE           VARCHAR2(2) NOT NULL,
  SHIPTOADDRESSZIPCODE         VARCHAR2(15) NOT NULL,
  SHIPTOADDRESSCOUNTY          VARCHAR2(80) NULL,
  SHIPTOCONTACTNAME            VARCHAR2(80) NULL,
  PROMOTIONCODE                VARCHAR2(30) NULL,
  CONTACTPERSONID              NUMBER NOT NULL,
  CONTACTPERSONEMAIL           VARCHAR2(60) NOT NULL,
  CONTACTPERSONNAME            VARCHAR2(80) NOT NULL,
  ACCOMODATIONNOTESTYPE        VARCHAR2(80) NULL,
  CREDITCARDTYPE               VARCHAR2(10) NULL,
  PAYAMOUNT                    NUMBER(15,2) NOT NULL,
  CYBERSOURCETRANSACTIONNUMBER VARCHAR2(40) NULL,
  PROCESSEDFLAG                VARCHAR2(1) DEFAULT 'N' NOT NULL
				      CONSTRAINT PROCESSEDFLAG_Validation2
					  CHECK (PROCESSEDFLAG IN ('N','Y','E')),
  TIMESTAMP                    DATE DEFAULT SYSDATE NOT NULL
);
 
COMMENT ON COLUMN WEBCATINVOICEHEADERIMP.SETTLEMENTTYPE  IS 'S (Settlement) or A (Accommodation)';
COMMENT ON COLUMN WEBCATINVOICEHEADERIMP.PROCESSEDFLAG   IS 'N=Not processed (when created)
 Y=Processed
 E=Error';

ALTER TABLE WEBCATINVOICEHEADERIMP
  ADD ( CONSTRAINT XPKWEBCATINVOICEHEADERIMP PRIMARY KEY (PAYLOADID, PAYMENTID) );

CREATE INDEX XIE1WEBCATINVOICEHEADERIMP ON WEBCATINVOICEHEADERIMP (PROCESSEDFLAG);

------------------------------------
CREATE TABLE WEBCATINVOICEPRODUCTIMP
(
  PAYLOADID     VARCHAR2(40) NOT NULL,
  PAYMENTID     VARCHAR2(10) NOT NULL,
  LINENUMBER    NUMBER NOT NULL,
  ITEMID        VARCHAR2(24) NOT NULL,
  PRODUCTCODE   VARCHAR2(12) NOT NULL,
  SIZECODE      VARCHAR2(12) NOT NULL,
  DESCRIPTION   VARCHAR2(80) NOT NULL,
  QUANTITY      NUMBER NOT NULL,
  PRICE         NUMBER(15,4) NOT NULL,
  AMOUNT        NUMBER(15,2) NOT NULL,
  TAXABLEYN     VARCHAR2(1) NOT NULL
			CONSTRAINT YN_ValidationNew167
                                CHECK (TAXABLEYN IN ('Y', 'N')),
  PROCESSEDFLAG VARCHAR2(1) DEFAULT 'N' NOT NULL
				CONSTRAINT PROCESSEDFLAG_Validation3
				  CHECK (PROCESSEDFLAG IN ('N','Y','E')),
  TIMESTAMP     DATE DEFAULT SYSDATE NOT NULL
);

COMMENT ON COLUMN WEBCATINVOICEPRODUCTIMP.PROCESSEDFLAG IS 'N=Not processed (when created)
 Y=Processed
 E=Error';

ALTER TABLE WEBCATINVOICEPRODUCTIMP
  ADD ( CONSTRAINT XPKWEBCATINVOICEPRODUCTIMP PRIMARY KEY (PAYLOADID, PAYMENTID, LINENUMBER) );

ALTER TABLE WEBCATINVOICEPRODUCTIMP
  	ADD ( CONSTRAINT WEBCATINVPRODUCTIMP_PAYLOADID FOREIGN KEY (PAYLOADID, PAYMENTID)
        		  REFERENCES WEBCATINVOICEHEADERIMP (PAYLOADID, PAYMENTID)
			        ON DELETE CASCADE ) ;

CREATE INDEX XIE1WEBCATINVOICEPRODUCTIMP ON WEBCATINVOICEPRODUCTIMP (PROCESSEDFLAG);

---------------------------------------
CREATE TABLE WEBCATINVOICEDECORATIONIMP
(
  PAYLOADID       VARCHAR2(40) NOT NULL,
  PAYMENTID       VARCHAR2(10) NOT NULL,
  LINENUMBER      NUMBER NOT NULL,
  DECORATIONTYPE  VARCHAR2(10) NOT NULL,
  LOCATION        VARCHAR2(3) NULL,
  LOGOCODE        VARCHAR2(20) NULL,
  APPLIQUESTYLE   VARCHAR2(2) NULL,
  EMBLEMTYPE      VARCHAR2(10) NULL,
  ALIGNMENT       VARCHAR2(10) NULL,
  BACKGROUNDCOLOR VARCHAR2(30) NULL,
  BORDERCOLOR     VARCHAR2(30) NULL,
  QUANTITY        NUMBER NOT NULL,
  PRICE           NUMBER(15,4) NOT NULL,
  AMOUNT          NUMBER(15,2) NOT NULL,
  TAXABLEYN       VARCHAR2(1) NOT NULL
			CONSTRAINT YN_ValidationNew166
                                CHECK (TAXABLEYN IN ('Y', 'N')),
  PROCESSEDFLAG   VARCHAR2(1) DEFAULT 'N' NOT NULL
				CONSTRAINT PROCESSEDFLAG_Validation4
				  CHECK (PROCESSEDFLAG IN ('N','Y','E')),
  TIMESTAMP       DATE DEFAULT SYSDATE NOT NULL
);


COMMENT ON COLUMN WEBCATINVOICEDECORATIONIMP.PROCESSEDFLAG IS 'N=Not processed (when created)
 Y=Processed
 E=Error';

ALTER TABLE WEBCATINVOICEDECORATIONIMP
  ADD CONSTRAINT XPKWEBCATINVOICEDECORATIONIMP PRIMARY KEY (PAYLOADID, PAYMENTID, LINENUMBER);

ALTER TABLE WEBCATINVOICEDECORATIONIMP
      ADD ( CONSTRAINT WEBCATINVDECORATIONIMP_PAYL 
                       FOREIGN KEY (PAYLOADID, PAYMENTID) 
                               REFERENCES WEBCATINVOICEHEADERIMP (PAYLOADID, PAYMENTID)
                                          ON DELETE CASCADE );

CREATE INDEX XIE1WEBCATINVOICEDECORATIONIMP ON WEBCATINVOICEDECORATIONIMP (PROCESSEDFLAG);

---------------------------------------
CREATE TABLE WEBCATINVDECORATIONLINEIMP
(
  PAYLOADID            VARCHAR2(40) NOT NULL,
  PAYMENTID            VARCHAR2(10) NOT NULL,
  LINENUMBER           NUMBER NOT NULL,
  DECORATIONLINENUMBER NUMBER NOT NULL
				CONSTRAINT DecorationLinenumber_Valid
				  CHECK (DECORATIONLINENUMBER BETWEEN 1 AND 3),
  FONTCOLOR            VARCHAR2(30) NULL,
  FONTSTYLE            VARCHAR2(3) NULL,
  FONTSIZE             VARCHAR2(10) NULL,
  TEXT                 VARCHAR2(80) NOT NULL,
  PROCESSEDFLAG        VARCHAR2(1) DEFAULT 'N' NOT NULL
				           CONSTRAINT PROCESSEDFLAG_Validation5
				                CHECK (PROCESSEDFLAG IN ('N','Y','E')),
  TIMESTAMP            DATE DEFAULT SYSDATE NOT NULL
);


COMMENT ON COLUMN WEBCATINVDECORATIONLINEIMP.PROCESSEDFLAG IS 'N=Not processed (when created)
 Y=Processed
 E=Error';

ALTER TABLE WEBCATINVDECORATIONLINEIMP
  ADD CONSTRAINT XPKWEBCATINVDECORATIONLINEIMP PRIMARY KEY (PAYLOADID, PAYMENTID, LINENUMBER, DECORATIONLINENUMBER);

ALTER TABLE WEBCATINVDECORATIONLINEIMP
        ADD ( CONSTRAINT WEBCATINVDECLIMP_PAYLDPAYM FOREIGN KEY (PAYLOADID, PAYMENTID, LINENUMBER)
                                                      REFERENCES WEBCATINVOICEDECORATIONIMP (PAYLOADID, PAYMENTID, LINENUMBER)
                                                       ON DELETE CASCADE );

CREATE INDEX XIE1WEBCATINVDECORATIONLINEIMP ON WEBCATINVDECORATIONLINEIMP (PROCESSEDFLAG);

-------------------------
CREATE TABLE LOOKUPVALUES
(
  LOOKUPTYPE  VARCHAR2(30) NOT NULL,
  CODE        VARCHAR2(30) NOT NULL,
  DESCRIPTION VARCHAR2(30) NOT NULL,
  ACTIVEYN    VARCHAR2(1) DEFAULT 'Y' NOT NULL
                          CONSTRAINT YN_ValidationNew165
                                CHECK (ACTIVEYN IN ('Y', 'N')));


ALTER TABLE LOOKUPVALUES
  ADD ( CONSTRAINT XPKLOOKUPVALUES PRIMARY KEY (LOOKUPTYPE, CODE) );

ALTER TABLE LOOKUPVALUES
  ADD ( CONSTRAINT LOOKUPVALUES_LOOKUPTYPE
        CHECK (LOOKUPTYPE = TRIM(UPPER(LOOKUPTYPE))) );

---------------------------------
CREATE TABLE GENERALSYSTEMSETTING
(
  SETTINGNAME VARCHAR2(30) NOT NULL,
  SETTINGTYPE VARCHAR2(1) NOT NULL
                          CONSTRAINT SettingType_validation
                            CHECK (SETTINGTYPE IN ('S','N','D')),
  STRINGVALUE VARCHAR2(100) NULL,
  NUMBERVALUE NUMBER NULL,
  DATEVALUE   DATE NULL
);

COMMENT ON COLUMN GENERALSYSTEMSETTING.SETTINGTYPE
  IS 'S=STRING,N=NUMBER,D=DATE';

ALTER TABLE GENERALSYSTEMSETTING
  ADD ( CONSTRAINT XPKGENERALSYSTEMSETTINGS PRIMARY KEY (SETTINGNAME) );

ALTER TABLE GENERALSYSTEMSETTING
  ADD ( CONSTRAINT GENERALSYSTEMSETTING_SETT
        CHECK (SETTINGNAME = TRIM(UPPER(SETTINGNAME))) );

