--Order 20010670.90
-- moved to Script_08.03.00.33.sql - issue 20011051

/*
ALTER TABLE CUSTOMER
	ADD CONSTRAINT AllocationMethod_valid1  CHECK (ALLOCATIONMETHOD IN (0, 1, 2));


COMMENT ON COLUMN CUSTOMER.ALLOCATIONMETHOD IS '0 = No re-allocation
1 = Allocation results in automatic ordering
2 = Allocation only, no automatic ordering';

update department d
   set d.bulkdeliveriesdept = 'Y'
 where d.bulkdeliveriesdept = 'N'
   and d.department_id in
       (select d1.department_id
          from department d1,
               (select d2.customer_id,
                       min(d2.departmentnumber) departmentnumber
                  from department d2
                 where d2.customer_id in
                       (select c2.customer_id
                          from customer c2
                         where c2.allocationmethod > 0
                           and not exists
                         (select 1
                                  from department d3
                                 where d3.customer_id = c2.customer_id
                                   and d3.bulkdeliveriesdept = 'Y'))
                 group by d2.customer_id) cu
         where d1.customer_id = cu.customer_id
           and d1.departmentnumber = cu.departmentnumber);
commit;


ALTER TABLE STOCKCOUNTSCHEDULE
	ADD (REPLENISHMENTTYPE NUMBER  DEFAULT 0 NOT NULL CONSTRAINT ReplenishmentType_valid1 CHECK (REPLENISHMENTTYPE IN (0, 1)));



ALTER TABLE STOCKCOUNTSCHEDULE
	ADD (DELIVERYDAYDETERMINATION NUMBER  DEFAULT 0 NOT NULL CONSTRAINT DeliveryDayDeterm_valid1 CHECK (DELIVERYDAYDETERMINATION IN (0, 1)));



COMMENT ON COLUMN STOCKCOUNTSCHEDULE.REPLENISHMENTTYPE IS '0 = Stock Count / Ordering
1 = Reallocation';



COMMENT ON COLUMN STOCKCOUNTSCHEDULE.DELIVERYDAYDETERMINATION IS '0 = Determine automatically
1 = Define manually
Only applicable in case of ReplenishmentType = 0 (Stock Count / Ordering)';


update stockcountschedule s
   set s.replenishmenttype = 1
 where s.customer_id in
       (select customer_id from customer where allocationmethod > 0)
   and (select count(1)
          from stockcountscheduledept d
         where d.stockcountschedule_id = s.stockcountschedule_id) > 1;
commit;

update stockcountschedule
   set deliverydaydetermination = 1
 where customer_id in
       (select customer_id from customer where allocationmethod = 1);
commit;
*/