--Order 20013291.290

CREATE SEQUENCE SEQ_CUSTOMERINVENTORYCOUNT
START WITH 1
INCREMENT BY 1;

CREATE TABLE CUSTOMERINVENTORYCOUNT
(
	CUSTOMERINVENTORYCOUNT_ID NUMBER NOT NULL ,
	DISTRIBUTIONPOINT_ID NUMBER NOT NULL ,
	PRODUCTLISTPERDISTRIBPOINT_ID NUMBER NULL ,
	PRODUCT_ID           NUMBER NOT NULL ,
	SIZEDEFINITION_ID    NUMBER NOT NULL ,
	FINISHINGMETHOD_ID   NUMBER NOT NULL ,
	COUNT                NUMBER DEFAULT  0  NOT NULL ,
	ACTION               NUMBER DEFAULT  1  NOT NULL  CONSTRAINT  Action_valid1_1 CHECK (ACTION BETWEEN 1 AND 9),
	ADJUSTMENT           NUMBER DEFAULT  0  NOT NULL ,
	PROCESSED            VARCHAR2(1) DEFAULT  'N'  NOT NULL  CONSTRAINT  YN_ValidationField202 CHECK (PROCESSED IN ('Y', 'N')),
	COUNTDATE            DATE NOT NULL ,
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
);

COMMENT ON COLUMN CUSTOMERINVENTORYCOUNT.ACTION IS '1 - No action
2 - Adjust Nominal / Circulating inventory
3 - Adjust Real inventory
';

CREATE UNIQUE INDEX XAK1CUSTOMERINVENTORYCOUNT ON CUSTOMERINVENTORYCOUNT
(DISTRIBUTIONPOINT_ID   ASC,PRODUCT_ID   ASC,SIZEDEFINITION_ID   ASC,FINISHINGMETHOD_ID   ASC,COUNTDATE   ASC);

CREATE INDEX XIF1CUSTOMERINVENTORYCOUNT ON CUSTOMERINVENTORYCOUNT
(DISTRIBUTIONPOINT_ID   ASC);

CREATE INDEX XIF2CUSTOMERINVENTORYCOUNT ON CUSTOMERINVENTORYCOUNT
(PRODUCTLISTPERDISTRIBPOINT_ID   ASC);

CREATE INDEX XIF3CUSTOMERINVENTORYCOUNT ON CUSTOMERINVENTORYCOUNT
(PRODUCT_ID   ASC);

CREATE INDEX XIF4CUSTOMERINVENTORYCOUNT ON CUSTOMERINVENTORYCOUNT
(SIZEDEFINITION_ID   ASC);

CREATE INDEX XIF5CUSTOMERINVENTORYCOUNT ON CUSTOMERINVENTORYCOUNT
(FINISHINGMETHOD_ID   ASC);

CREATE INDEX XIE1CUSTOMERINVENTORYCOUNT ON CUSTOMERINVENTORYCOUNT
(DISTRIBUTIONPOINT_ID   ASC,PRODUCT_ID   ASC,SIZEDEFINITION_ID   ASC,FINISHINGMETHOD_ID   ASC);

ALTER TABLE CUSTOMERINVENTORYCOUNT
ADD CONSTRAINT  XPKCUSTOMERINVENTORYCOUNT PRIMARY KEY (CUSTOMERINVENTORYCOUNT_ID);

ALTER TABLE CUSTOMERINVENTORYCOUNT
ADD CONSTRAINT CUSTOMERINVENTORYCOUNT_DP FOREIGN KEY (DISTRIBUTIONPOINT_ID) REFERENCES DISTRIBUTIONPOINT (DISTRIBUTIONPOINT_ID);

ALTER TABLE CUSTOMERINVENTORYCOUNT
ADD CONSTRAINT CUSTOMERINVENTORYCOUNT_PLPD FOREIGN KEY (PRODUCTLISTPERDISTRIBPOINT_ID) REFERENCES PRODUCTLISTPERDISTRIBPOINT (PRODUCTLISTPERDISTRIBPOINT_ID);

ALTER TABLE CUSTOMERINVENTORYCOUNT
ADD CONSTRAINT CUSTOMERINVENTORYCOUNT_PROD FOREIGN KEY (PRODUCT_ID) REFERENCES PRODUCT (PRODUCT_ID);

ALTER TABLE CUSTOMERINVENTORYCOUNT
ADD CONSTRAINT CUSTOMERINVENTORYCOUNT_SIZEDEF FOREIGN KEY (SIZEDEFINITION_ID) REFERENCES SIZEDEFINITION (SIZEDEFINITION_ID);

ALTER TABLE CUSTOMERINVENTORYCOUNT
ADD CONSTRAINT CUSTOMERINVENTORYCOUNT_FM FOREIGN KEY (FINISHINGMETHOD_ID) REFERENCES FINISHINGMETHOD (FINISHINGMETHOD_ID);
