-- ABS-67076/ ABS-82126

-- ROUTECONTROLBULKPACKING TABLE --
DROP TABLE ROUTECONTROLBULKPACKING;

CREATE TABLE ROUTECONTROLBULKPACKING
(
  ROUTECONTROLBULKPACKING_ID NUMBER NOT NULL,
  ROUTECONTROL_ID            NUMBER NOT NULL,
  CUSTOMER_LINK_ID           NUMBER NULL,
  PRODUCT_LINK_ID            NUMBER NOT NULL,
  SIZEDEFINITION_LINK_ID     NUMBER NOT NULL,
  FINISHINGMETHOD_ID         NUMBER NOT NULL,
  SEQUENCENUMBER             NUMBER DEFAULT 1 NOT NULL,
  CUSTOMPRODUCTCODE          VARCHAR2(16) NULL,
  CUSTOMPRODUCTDESCRIPTION   VARCHAR2(30) NULL,
  BUNDLESIZE                 NUMBER DEFAULT 1 NOT NULL,
  TYPE                       NUMBER NOT NULL
                             CONSTRAINT CHK_RCBP_TYPE CHECK (TYPE IN (1, 2, 3, 4)),
  QUANTITYTOBEPACKED         NUMBER NOT NULL,
  QUANTITYPACKED             NUMBER NULL,
  ACTIVESTOCKLOCATION_ID     NUMBER NULL,
  PASSIVESTOCKLOCATION_ID    NUMBER NULL,
  ACTIVE                     VARCHAR2(1) DEFAULT 'Y' NOT NULL CONSTRAINT CHK_RCBP_ACTIVE CHECK (ACTIVE IN ('Y', 'N')),
  SYSTEMUSER_ID              NUMBER NOT NULL,
  TIMESTAMP                  DATE DEFAULT SYSDATE NOT NULL
)
;

COMMENT ON COLUMN ROUTECONTROLBULKPACKING.TYPE
  IS '1 = Customer Logo Mat
2 = Bulk Logo Mat
3 = Pool
4 = One Way 
';

ALTER TABLE ROUTECONTROLBULKPACKING
  ADD CONSTRAINT XPKROUTECONTROLBULKPACKING PRIMARY KEY (ROUTECONTROLBULKPACKING_ID);

CREATE UNIQUE INDEX XAK1ROUTECONTROLBULKPACKING ON ROUTECONTROLBULKPACKING (NVL(CUSTOMER_LINK_ID,-1) ASC, PRODUCT_LINK_ID ASC, SIZEDEFINITION_LINK_ID ASC, FINISHINGMETHOD_ID ASC, SEQUENCENUMBER ASC);

CREATE INDEX XIF1ROUTECONTROLBULKPACKING ON ROUTECONTROLBULKPACKING (ROUTECONTROL_ID ASC);
CREATE INDEX XIF2ROUTECONTROLBULKPACKING ON ROUTECONTROLBULKPACKING (CUSTOMER_LINK_ID ASC);
CREATE INDEX XIF3ROUTECONTROLBULKPACKING ON ROUTECONTROLBULKPACKING (PRODUCT_LINK_ID ASC);
CREATE INDEX XIF4ROUTECONTROLBULKPACKING ON ROUTECONTROLBULKPACKING (SIZEDEFINITION_LINK_ID ASC);
CREATE INDEX XIF5ROUTECONTROLBULKPACKING ON ROUTECONTROLBULKPACKING (FINISHINGMETHOD_ID ASC);

ALTER TABLE ROUTECONTROLBULKPACKING
  ADD CONSTRAINT ROUTECONTROLBULKPACKING_RC FOREIGN KEY (ROUTECONTROL_ID)
  REFERENCES ROUTECONTROL (ROUTECONTROL_ID) ON DELETE CASCADE;
  
ALTER TABLE ROUTECONTROLBULKPACKING
  ADD CONSTRAINT ROUTECONTROLBULKPACKING_C FOREIGN KEY (CUSTOMER_LINK_ID)
  REFERENCES CUSTOMER_LINK (CUSTOMER_LINK_ID);
  
ALTER TABLE ROUTECONTROLBULKPACKING
  ADD CONSTRAINT ROUTECONTROLBULKPACKING_P FOREIGN KEY (PRODUCT_LINK_ID)
  REFERENCES PRODUCT_LINK (PRODUCT_LINK_ID);
  
ALTER TABLE ROUTECONTROLBULKPACKING
  ADD CONSTRAINT ROUTECONTROLBULKPACKING_S FOREIGN KEY (SIZEDEFINITION_LINK_ID)
  REFERENCES SIZEDEFINITION_LINK (SIZEDEFINITION_LINK_ID);
  
ALTER TABLE ROUTECONTROLBULKPACKING
  ADD CONSTRAINT ROUTECONTROLBULKPACKING_FM FOREIGN KEY (FINISHINGMETHOD_ID)
  REFERENCES FINISHINGMETHOD (FINISHINGMETHOD_ID);