-- ABS-150969

-- CREATE TABLE
CREATE TABLE ERP_O_CLEANROOM_WEARERINVENTORY
(
  TRANSFER_ID           NUMBER NOT NULL,
  TRANSFER_STATUS       NUMBER,
  BUSINESSUNITCODE      VARCHAR2(6),
  WEARERNUMBER          NUMBER,
  FULLNAME              VARCHAR2(40),
  FIRSTNAME             VARCHAR2(24),
  BANKLOCKER            VARCHAR2(6),
  PRODUCTCODE           VARCHAR2(12),
  SIZECODE              VARCHAR2(12),
  DEFAULTGRADE          VARCHAR2(1),
  CUSTOMERDELIVERY_MO   NUMBER,
  CUSTOMERDELIVERY_TU   NUMBER,
  CUSTOMERDELIVERY_WE   NUMBER,
  CUSTOMERDELIVERY_TH   NUMBER,
  CUSTOMERDELIVERY_FR   NUMBER,
  CUSTOMERDELIVERY_SA   NUMBER,
  CUSTOMERDELIVERY_SU   NUMBER,
  FREQUENCY             NUMBER,
  MAXINVENTORY          NUMBER,
  DATEACTIVE            DATE,
  DATEINACTIVE          DATE,
  WEARERINVENTORY_ID    NUMBER,
  PRODUCTDESCRIPTION    VARCHAR2(30),
  COGFLAG               VARCHAR2(1),
  LINESTATUS            VARCHAR2(1),
  BILLTYPE              VARCHAR2(1),
  REPLACEMENTPRICE      NUMBER(16,9),
  TEMPRENTSTOPSTARTDATE DATE,
  TEMPRENTSTOPENDDATE   DATE
);

ALTER TABLE ERP_O_CLEANROOM_WEARERINVENTORY
  ADD CONSTRAINT PK_ERP_O_CLEANROOM_WI PRIMARY KEY (TRANSFER_ID);

-- ADD COMMENTS TO THE COLUMNS 
COMMENT ON COLUMN ERP_O_CLEANROOM_WEARERINVENTORY.BANKLOCKER
  IS 'LOCKER NUMBER';
COMMENT ON COLUMN ERP_O_CLEANROOM_WEARERINVENTORY.CUSTOMERDELIVERY_MO
  IS 'IF SERVICE ON MONDAY GET 1, IF NOT 0';
COMMENT ON COLUMN ERP_O_CLEANROOM_WEARERINVENTORY.CUSTOMERDELIVERY_TU
  IS 'IF SERVICE ON TUESDAY GET 1, IF NOT 0';
COMMENT ON COLUMN ERP_O_CLEANROOM_WEARERINVENTORY.CUSTOMERDELIVERY_WE
  IS 'IF SERVICE ON WEDNESDAY GET 1, IF NOT 0';
COMMENT ON COLUMN ERP_O_CLEANROOM_WEARERINVENTORY.CUSTOMERDELIVERY_TH
  IS 'IF SERVICE ON THURSDAY GET 1, IF NOT 0';
COMMENT ON COLUMN ERP_O_CLEANROOM_WEARERINVENTORY.CUSTOMERDELIVERY_FR
  IS 'IF SERVICE ON FRIDAY GET 1, IF NOT 0';
COMMENT ON COLUMN ERP_O_CLEANROOM_WEARERINVENTORY.CUSTOMERDELIVERY_SA
  IS 'IF SERVICE ON SATURDAY GET 1, IF NOT 0';
COMMENT ON COLUMN ERP_O_CLEANROOM_WEARERINVENTORY.CUSTOMERDELIVERY_SU
  IS 'IF SERVICE ON SUNDAY GET 1, IF NOT 0';
COMMENT ON COLUMN ERP_O_CLEANROOM_WEARERINVENTORY.FREQUENCY
  IS 'SUM OF ACTIVE DELIVERY (D) AND BILL (B) DAYS FOR PLPC LINE';
COMMENT ON COLUMN ERP_O_CLEANROOM_WEARERINVENTORY.COGFLAG
  IS 'IF BM = 4, COG ONLY(CHARGESWASHESONLYFORCOG) = 3, CODEUNIQUELY = Y , CUSTOMERRELATED = Y  PASS Y, ELSE N';
COMMENT ON COLUMN ERP_O_CLEANROOM_WEARERINVENTORY.LINESTATUS
  IS 'A= WEARER INTENTORY DATE INACTIVE >= SYSDATE.
I= DATEINACTIVE <= SYSDATE
D= CUSTOMER ACTIVE BOX UNCHECKED';
COMMENT ON COLUMN ERP_O_CLEANROOM_WEARERINVENTORY.BILLTYPE
  IS 'B=PRODUCT TOM GARMENT, ELSE D';
COMMENT ON COLUMN ERP_O_CLEANROOM_WEARERINVENTORY.TEMPRENTSTOPSTARTDATE
  IS 'WEARER INVENTORY TEMP RENT STOP START DATE, IF DATE IS TODAY OR IN FUTURE';
COMMENT ON COLUMN ERP_O_CLEANROOM_WEARERINVENTORY.TEMPRENTSTOPENDDATE
  IS 'WEARER INVENTORY TEMP RENT STOP END DATE, IF APPLICABLE BASED ON ABOVE';
  
-- CREATE/RECREATE CHECK CONSTRAINTS 
ALTER TABLE ERP_O_CLEANROOM_WEARERINVENTORY
  ADD CONSTRAINT CHK_ERP_CR_MO
  CHECK (CUSTOMERDELIVERY_MO IN (0,1));
ALTER TABLE ERP_O_CLEANROOM_WEARERINVENTORY
  ADD CONSTRAINT CHK_ERP_CR_TU
  CHECK (CUSTOMERDELIVERY_TU IN (0,1));
ALTER TABLE ERP_O_CLEANROOM_WEARERINVENTORY
  ADD CONSTRAINT CHK_ERP_CR_WE
  CHECK (CUSTOMERDELIVERY_WE IN (0,1));
ALTER TABLE ERP_O_CLEANROOM_WEARERINVENTORY
  ADD CONSTRAINT CHK_ERP_CR_TH
  CHECK (CUSTOMERDELIVERY_TH IN (0,1));
ALTER TABLE ERP_O_CLEANROOM_WEARERINVENTORY
  ADD CONSTRAINT CHK_ERP_CR_FR
  CHECK (CUSTOMERDELIVERY_FR IN (0,1));
ALTER TABLE ERP_O_CLEANROOM_WEARERINVENTORY
  ADD CONSTRAINT CHK_ERP_CR_SA
  CHECK (CUSTOMERDELIVERY_SA IN (0,1));
ALTER TABLE ERP_O_CLEANROOM_WEARERINVENTORY
  ADD CONSTRAINT CHK_ERP_CR_SU
  CHECK (CUSTOMERDELIVERY_SU IN (0,1));  

CREATE TABLE ERP_O_CLEANROOM_WEARERINVENTORY_LOG
(
  TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL,
  TEXT            VARCHAR2(1000),
  PROCESS_STATUS  NUMBER NOT NULL,
  TIMESTAMP       DATE DEFAULT SYSDATE NOT NULL
);

-- Create/Recreate primary, unique and foreign key constraints 
alter table ERP_O_CLEANROOM_WEARERINVENTORY_LOG
  add constraint PK_ERP_O_CR_WI_LOG primary key (TRANSFER_ID);
alter table ERP_O_CLEANROOM_WEARERINVENTORY_LOG
  add constraint FK_ERP_O_CR_WI_LOG foreign key (TRANSFER_ID)
  references erp_o_cleanroom_wearerinventory (TRANSFER_ID) on delete cascade;

ALTER TABLE WEARER ADD ERPCLEANROOMEXPORT VARCHAR2(1) DEFAULT 'N';
ALTER TABLE WEARER
  ADD CONSTRAINT YN_VALIDERPEXPORT
  CHECK (ERPCLEANROOMEXPORT IN ('Y','N'));
  
  
