-- ABS-70820 / ABS-70895
--- STORENUMBERMASTER ---
CREATE SEQUENCE SEQ_STORENUMBERMASTER
START WITH 1
INCREMENT BY 1;

CREATE TABLE STORENUMBERMASTER
(
   STORENUMBERMASTER_ID NUMBER NOT NULL,
   STORENUMBER VARCHAR2(10) NOT NULL,
   STREET VARCHAR2(30) NULL,
   STREET2 VARCHAR2(30) NULL,
   COUNTY VARCHAR2(20) NULL,
   CITY VARCHAR2(30) NOT NULL,
   STATE VARCHAR2(20) NULL,
   ZIPCODE VARCHAR2(15) NOT NULL,
   CONTACTPHONE VARCHAR2(30) NULL,
   CONTACTFAX VARCHAR2(30) NULL,
   CUSTOMERNAME VARCHAR2(40) NOT NULL,
   SYSTEMUSER_ID NUMBER NOT NULL,
   TIMESTAMP DATE NOT NULL
);

ALTER TABLE STORENUMBERMASTER ADD CONSTRAINT XPKSTORENUMBERMASTER PRIMARY KEY (STORENUMBERMASTER_ID);

CREATE UNIQUE INDEX XAK1STORENUMBERMASTER ON STORENUMBERMASTER (STORENUMBER ASC);

--- CUSTOMER ---
ALTER TABLE CUSTOMER ADD (STORENUMBER VARCHAR2(10) NULL);

COMMENT ON COLUMN CUSTOMER.STORENUMBER IS 'For storing the customer’s identifier for the site.  If USPS module, this comes from a master table, else free text entry';

--- EDI_I_FEDSTRIP ---
CREATE SEQUENCE SEQ_EDI_I_FEDSTRIP
START WITH 1
INCREMENT BY 1;

CREATE TABLE EDI_I_FEDSTRIP
(
   EDI_I_FEDSTRIP_ID NUMBER NOT NULL,
   FEDSTRIPNUMBER VARCHAR2(10) NOT NULL,
   FINANCENUMBER NUMBER NOT NULL,
   AREAOFFICECODE VARCHAR2(2) NOT NULL,
   DISTRICTCODE NUMBER NOT NULL,
   COSTASCERTAINGROUP VARCHAR2(1) NOT NULL,
   ADDRESSTYPE VARCHAR2(10) NOT NULL,
   ADDRESSLINE1 VARCHAR2(30) NOT NULL,
   ADDRESSLINE2 VARCHAR2(30) NOT NULL,
   ADDRESSLINE3 VARCHAR2(20) NOT NULL,
   CITY VARCHAR2(30) NOT NULL,
   STATEORPROVINCE VARCHAR2(20) NOT NULL,
   ZIPORPOSTALCODE VARCHAR2(15) NOT NULL,
   NMICSSITECODE VARCHAR2(10) NULL,
   NMICSSUBSITECODE VARCHAR2(10) NULL,
   AUTHORIZEDTOBUYPARTS VARCHAR2(1) NOT NULL,
   PARTSFINANCENUMBER NUMBER NOT NULL,
   CONTACTPHONE VARCHAR2(30) NULL,
   CONTACTFAX VARCHAR2(30) NULL,
   PARTSFACTYPECODE VARCHAR2(10) NULL,
   SECTIONICNTRFACCODE NUMBER NOT NULL,
   CUSTOMERNAME VARCHAR2(40) NOT NULL,
   CHANGEDATE DATE NOT NULL,
   ACTIONCODE VARCHAR2(1) NOT NULL,
   TRANSACTIONSTATUS VARCHAR2(1) NOT NULL,
   FILENAME VARCHAR2(40) NOT NULL,
   CREATEDDATE DATE NOT NULL,
   IMPORTEDDATE DATE NULL,
   SYSTEMUSER_ID NUMBER NOT NULL,
   TIMESTAMP DATE NOT NULL
);

ALTER TABLE EDI_I_FEDSTRIP ADD CONSTRAINT XPKEDI_I_FEDSTRIP PRIMARY KEY (EDI_I_FEDSTRIP_ID);

COMMENT ON COLUMN EDI_I_FEDSTRIP.CREATEDDATE IS 'The date the record was created in this table';

COMMENT ON COLUMN EDI_I_FEDSTRIP.IMPORTEDDATE IS 'Updated when imported into the StoreNumberMaster table';