--Order 20013413.100

----ROUTECONTROL table----

ALTER TABLE ROUTECONTROL
	ADD (SCRUTINYSTATUS NUMBER(2)  DEFAULT 10 NOT NULL CONSTRAINT ScrutinyStatus_valid1_1 CHECK (SCRUTINYSTATUS IN (10, 20, 30)));

ALTER TABLE ROUTECONTROL
	ADD (PACKINGNOTEGENERATION NUMBER(1)  DEFAULT 0 NOT NULL CONSTRAINT PackingNoteGeneration_valid1_1 CHECK (PACKINGNOTEGENERATION IN (0, 1, 2)));

COMMENT ON COLUMN ROUTECONTROL.SCRUTINYSTATUS IS '10 = Not started
20 = Not approved
30 = Approved
';

COMMENT ON COLUMN ROUTECONTROL.PACKINGNOTEGENERATION IS '0 = Not applicable / Not started
1 = Partly generated
2 = Completed
';


----STOP table----

ALTER TABLE STOP
	ADD (SCRUTINYSTATUS NUMBER(2)  DEFAULT 11 NOT NULL CONSTRAINT ScrutinyStatus_valid2_1 CHECK (SCRUTINYSTATUS IN (10, 11, 20, 21, 30)));

COMMENT ON COLUMN STOP.SCRUTINYSTATUS IS '10 = Not applicable
11 = Not checked
20 = Not approved (Soil receiving)
21 = Not approved (Delivery variation)
30 = Approved
';


----DELIVERYVARIATIONS table----

ALTER TABLE DELIVERYVARIATIONS
	ADD (SCRUTINYSTATUS NUMBER(2)  DEFAULT 11 NOT NULL CONSTRAINT ScrutinyStatus_valid2_2 CHECK (SCRUTINYSTATUS IN (0, 10, 11, 21, 30)));

COMMENT ON COLUMN DELIVERYVARIATIONS.SCRUTINYSTATUS IS '0 = Not used
10 = Not applicable
11 = Not checked
21 = Not approved
30 = Approved
Note: the numbering is corresponding with the ScrutinyStatus of the RouteControl and Stop, therefore 21 is used instead of 20.
';

----CUSTOMER table----

ALTER TABLE CUSTOMER
	ADD (SCRUTINYEXCLUDECHECKBAGS NUMBER(1)  DEFAULT 0 NOT NULL CONSTRAINT ScrutinyExclChB_valid1_1 CHECK (SCRUTINYEXCLUDECHECKBAGS IN (0, 1)));

ALTER TABLE CUSTOMER
	ADD (SCRUTINYEXCLUDECHECKDV NUMBER(1)  DEFAULT 0 NOT NULL CONSTRAINT ScrutinyExclChDV_valid1_1 CHECK (SCRUTINYEXCLUDECHECKDV IN (0, 1)));

COMMENT ON COLUMN CUSTOMER.SCRUTINYEXCLUDECHECKBAGS IS '0 = Not excluded / not applicable
1 = Exclude check bags
';

COMMENT ON COLUMN CUSTOMER.SCRUTINYEXCLUDECHECKDV IS '0 = Not excluded / not applicable
1 = Exclude check delivery variations
';


----REASONCODE table----

ALTER TABLE REASONCODE
	ADD (EXCLUDESCRUTINYCHECK VARCHAR2(1)  DEFAULT 'N' NOT NULL CONSTRAINT YN_ValidationField198 CHECK (EXCLUDESCRUTINYCHECK IN ('Y', 'N')));


----SCRUTINYMINNOMINAL table----

CREATE TABLE SCRUTINYMINNOMINAL
(
	BUSINESSUNIT_ID      NUMBER NOT NULL ,
	DELIVERIESPERWEEK    NUMBER NOT NULL  CONSTRAINT  DeliveriesPerWeek_valid1_1 CHECK (DELIVERIESPERWEEK BETWEEN 1 AND 7),
	MINIMUMNOMINAL       NUMBER NOT NULL 
);

ALTER TABLE SCRUTINYMINNOMINAL
ADD CONSTRAINT  XPKSCRUTINYMINNOMINAL PRIMARY KEY (BUSINESSUNIT_ID,DELIVERIESPERWEEK);

CREATE INDEX XIF1SCRUTINYMINNOMINAL ON SCRUTINYMINNOMINAL
(BUSINESSUNIT_ID   ASC);

ALTER TABLE SCRUTINYMINNOMINAL
ADD CONSTRAINT SCRUTINYMINNOMINAL_BU FOREIGN KEY (BUSINESSUNIT_ID) REFERENCES BUSINESSUNIT (BUSINESSUNIT_ID) ON DELETE CASCADE;

-------process scheduler--------
insert into process(process_id,description,active) values ('119','Scrutiny','Y');
commit;

