--Order 20010826.50

--------------------PURGEPLANTCONFIG-------------------

CREATE SEQUENCE SEQ_PURGEPLANTCONFIG
START WITH 1
INCREMENT BY 1;


CREATE TABLE PURGEPLANTCONFIG
(
	PURGEPLANTCONFIG_ID  NUMBER NOT NULL ,
	PLANT_ID             NUMBER NOT NULL ,
	PURGETABLE_ID        NUMBER NOT NULL ,
	NUMBEROFWEEKSTOKEEP  NUMBER(3) NOT NULL  CONSTRAINT  NumberOfWeeksToKeep_valid1 CHECK (NUMBEROFWEEKSTOKEEP BETWEEN 1 AND 999),
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
)
TABLESPACE ABS_DATA;


ALTER TABLE PURGEPLANTCONFIG
ADD CONSTRAINT  XPKPURGEPLANTCONFIG PRIMARY KEY (PURGEPLANTCONFIG_ID) USING INDEX TABLESPACE INDX;


CREATE  INDEX XIF1PURGEPLANTCONFIG ON PURGEPLANTCONFIG
(PLANT_ID   ASC) TABLESPACE INDX;


CREATE  INDEX XIF2PURGEPLANTCONFIG ON PURGEPLANTCONFIG
(PURGETABLE_ID   ASC) TABLESPACE INDX;


CREATE UNIQUE INDEX XAK1PURGEPLANTCONFIG ON PURGEPLANTCONFIG
(PLANT_ID   ASC,PURGETABLE_ID   ASC) TABLESPACE INDX;


--------------------PURGETABLE-------------------

CREATE SEQUENCE SEQ_PURGETABLE
START WITH 1
INCREMENT BY 1;

CREATE TABLE PURGETABLE
(
	PURGETABLE_ID        NUMBER NOT NULL ,
	TABLENAME            VARCHAR2(30) NOT NULL ,
	DESCRIPTION          VARCHAR2(40) NOT NULL ,
	REMARK               VARCHAR2(60) NULL ,
	WHERECLAUSE          VARCHAR2(1000) NULL ,
	DATEFIELDNAME        VARCHAR2(30) NOT NULL ,
	HINT                 VARCHAR2(200) NULL ,
	PLANTFIELDNAME       VARCHAR2(30) NULL ,
	BUSINESSUNITFIELDNAME VARCHAR2(30) NULL ,
	COMMITSIZE           NUMBER(4) DEFAULT  5000  NOT NULL  CONSTRAINT  CommitSize_valid1 CHECK (COMMITSIZE BETWEEN 1 AND 9999),
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
)
TABLESPACE ABS_DATA;



COMMENT ON COLUMN PURGETABLE.DATEFIELDNAME IS 'Name of the field where the reference date is stored.';



COMMENT ON COLUMN PURGETABLE.HINT IS 'Optional to improve the performance';


ALTER TABLE PURGETABLE
ADD CONSTRAINT  XPKPURGETABLE PRIMARY KEY (PURGETABLE_ID) USING INDEX TABLESPACE INDX;


CREATE UNIQUE INDEX XAK1PURGETABLE ON PURGETABLE
(TABLENAME   ASC,WHERECLAUSE   ASC) TABLESPACE INDX;



CREATE UNIQUE INDEX XAK2PURGETABLE ON PURGETABLE
(DESCRIPTION   ASC) TABLESPACE INDX;




ALTER TABLE PURGEPLANTCONFIG
ADD CONSTRAINT PURGEPLANTCONFIG_PLANT FOREIGN KEY (PLANT_ID) REFERENCES PLANT (PLANT_ID) ON DELETE CASCADE;



ALTER TABLE PURGEPLANTCONFIG
ADD CONSTRAINT PURGEPLANTCONFIG_PURGETABLE FOREIGN KEY (PURGETABLE_ID) REFERENCES PURGETABLE (PURGETABLE_ID) ON DELETE CASCADE;

------------------PACKINGNOTE--------------------

ALTER TABLE PACKINGNOTE
DROP CONSTRAINT PACKINGNOTE_ROUTECONTROL_ID;



ALTER TABLE PACKINGNOTE
DROP CONSTRAINT PACKINGNOTE_STOP_ID;



ALTER TABLE PACKINGNOTE
ADD CONSTRAINT PACKINGNOTE_ROUTECONTROL_ID FOREIGN KEY (ROUTECONTROL_ID) REFERENCES ROUTECONTROL (ROUTECONTROL_ID) ON DELETE SET NULL;



ALTER TABLE PACKINGNOTE
ADD CONSTRAINT PACKINGNOTE_STOP_ID FOREIGN KEY (STOP_ID) REFERENCES STOP (STOP_ID) ON DELETE SET NULL;


------------------STOCKTRANSFERHEADER------------

ALTER TABLE STOCKTRANSFERHEADER
DROP CONSTRAINT STOCKTRANSFERHEADER_STOCKDELIV;



ALTER TABLE STOCKTRANSFERHEADER
DROP CONSTRAINT STOCKTRANSFERHEADER_INTERNALPU;



ALTER TABLE STOCKTRANSFERHEADER
ADD CONSTRAINT STOCKTRANSFERHEADER_STOCKDELIV FOREIGN KEY (STOCKDELIVERYWORKORDER_ID) REFERENCES WORKORDER (WORKORDER_ID) ON DELETE CASCADE  NOT DEFERRABLE  INITIALLY IMMEDIATE  ENABLE  VALIDATE;



ALTER TABLE STOCKTRANSFERHEADER
ADD CONSTRAINT STOCKTRANSFERHEADER_INTERNALPU FOREIGN KEY (INTERNALPURCHASEORDERHEADER_ID) REFERENCES PURCHASEORDERHEADER (PURCHASEORDERHEADER_ID) ON DELETE SET NULL  NOT DEFERRABLE  INITIALLY IMMEDIATE  ENABLE  VALIDATE;

-----------------UNIQUEITEMFIXEDINFO--------------

ALTER TABLE UNIQUEITEMFIXEDINFO
DROP CONSTRAINT UNIQUEITEMFIXEDINFO_ISSUEDBYWO;


ALTER TABLE UNIQUEITEMFIXEDINFO
ADD CONSTRAINT UNIQUEITEMFIXEDINFO_ISSUEDBYWO FOREIGN KEY (ISSUEDBYWORKORDER_ID) REFERENCES WORKORDER (WORKORDER_ID) ON DELETE SET NULL;


--insert process
insert into process(process_id,description,active) values ('97','Generic purge','Y');

--insert into PURGETABLE
--1
insert into purgetable
  (purgetable_id,
   tablename,
   description,
   remark,
   whereclause,
   datefieldname,
   businessunitfieldname,
   systemuser_id,
   timestamp)
  select seq_purgetable.nextval,
         'Contact',
         'Contacts',
         'Only closed contacts will be purged',
         'Status = 4',
         'NewDate',
         'BusinessUnit_ID',
         getminsystemuser(),
         sysdate
    from dual;
--2
insert into purgetable
  (purgetable_id,
   tablename,
   description,
   remark,
   datefieldname,
   systemuser_id,
   timestamp)
  select seq_purgetable.nextval,
         'InternetOrder',
         'Internet / PDA Orders',
         'Warning: cleaned up for all plants',
         'OrderDate',
         getminsystemuser(),
         sysdate
    from dual;
--3
insert into purgetable
  (purgetable_id,
   tablename,
   description,
   whereclause,
   datefieldname,
   plantfieldname,
   systemuser_id,
   timestamp)
  select seq_purgetable.nextval,
         'RouteControl',
         'Route status control',
         'SettlementStatus = 3 or InvoicesGenerated = 3',
         'DrivingDate',
         'Plant_ID',
         getminsystemuser(),
         sysdate
    from dual;
--4
insert into purgetable
  (purgetable_id,
   tablename,
   description,
   remark,
   whereclause,
   datefieldname,
   businessunitfieldname,
   systemuser_id,
   timestamp)
  select seq_purgetable.nextval,
         'WorkOrder',
         'Work orders',
         'Time filter based on date needed',
         'Origin < 6',
         'NeededDate',
         'BusinessUnit_ID',
         getminsystemuser(),
         sysdate
    from dual;
--5
insert into purgetable
  (purgetable_id,
   tablename,
   description,
   remark,
   whereclause,
   datefieldname,
   businessunitfieldname,
   systemuser_id,
   timestamp)
  select seq_purgetable.nextval,
         'WorkOrder',
         'Sales / credit notes',
         'Time filter based on date needed',
         'Origin = 6 or Origin = 7',
         'NeededDate',
         'BusinessUnit_ID',
         getminsystemuser(),
         sysdate
    from dual;
--6
insert into purgetable
  (purgetable_id,
   tablename,
   description,
   remark,
   whereclause,
   datefieldname,
   businessunitfieldname,
   systemuser_id,
   timestamp)
  select seq_purgetable.nextval,
         'WorkOrder',
         'Stock delivery / internal purchase order',
         'Time filter based on date needed',
         'Origin = 8 or Origin = 9',
         'NeededDate',
         'BusinessUnit_ID',
         getminsystemuser(),
         sysdate
    from dual;
--7
insert into purgetable
  (purgetable_id,
   tablename,
   description,
   remark,
   whereclause,
   datefieldname,
   businessunitfieldname,
   systemuser_id,
   timestamp)
  select seq_purgetable.nextval,
         'WorkOrder',
         'Mounting / dismounting order',
         'Time filter based on date needed',
         'Origin = 10 or Origin = 11',
         'NeededDate',
         'BusinessUnit_ID',
         getminsystemuser(),
         sysdate
    from dual;
--8
insert into purgetable
  (purgetable_id,
   tablename,
   description,
   remark,
   datefieldname,
   businessunitfieldname,
   systemuser_id,
   timestamp)
  select seq_purgetable.nextval,
         'ClientDeliveryNote',
         'Client delivery note',
         'Time filter based on date needed',
         'DeliveryDate',
         'ProcessBusinessUnit_ID',
         getminsystemuser(),
         sysdate
    from dual;