--Order 20013741.70

----DEPARTMENTROOM----

ALTER TABLE DEPARTMENTROOM ADD (SEQUENCENUMBER NUMBER(4) DEFAULT 0 NOT NULL 
                                               CONSTRAINT SEQUENCENUMBER_VALID3 CHECK (SEQUENCENUMBER BETWEEN 0 AND 9999));

CREATE INDEX XIE2DEPARTMENTROOM ON DEPARTMENTROOM (DEPARTMENT_ID, SEQUENCENUMBER);

--update departmentroom
create or replace procedure update_departmentroom
  is
    cursor get_departmentroom is
      select dr.department_id, dr.roomnumber
        from departmentroom dr
        order by dr.department_id, dr.roomnumber;

    lv_prevdepartmentid number;
    lv_m_count number;
    begin
        -- Initialize prevdepartmentid and m_count
        lv_prevdepartmentid := 0;
        lv_m_count := 0;
      for dr_rec in get_departmentroom loop
          if lv_prevdepartmentid = dr_rec.department_id then
          -- Increase m_count
          lv_m_count := lv_m_count + 1;
          else
            begin --initialize m_count and prevdepartmentid
            lv_m_count := 1;
            lv_prevdepartmentid := dr_rec.department_id;
            end;
          end if;
              update departmentroom dr
              set dr.sequencenumber = lv_m_count
              where dr.department_id = dr_rec.department_id
              and dr.roomnumber = dr_rec.roomnumber;
              commit;
      end loop;
end update_departmentroom;
/

execute update_departmentroom;

drop procedure update_departmentroom;

----SORTINGDEFINITIONRS----

COMMENT ON COLUMN SORTINGDEFINITIONRS.SORTFIELD
  IS 'Predefined hardcoded values:
1	Department Number
2	Client Number
3	Client Name
4	Room number
5	Product Code
6	product sort group
7	Delivery point code
8	Client delivery note number
9 Room sequence number';

ALTER TABLE SORTINGDEFINITIONRS
  DROP CONSTRAINT SORTFIELD_VALID1_1;
ALTER TABLE SORTINGDEFINITIONRS
  ADD CONSTRAINT SORTFIELD_VALID1_1
  CHECK (SORTFIELD IN (1, 2, 3, 4, 5, 6, 7, 8, 9));

----CUSTOMERRESIDENTIALSETTINGS----

COMMENT ON COLUMN CUSTOMERRESIDENTIALSETTINGS.SORTORDER
  IS '0 = Sequence number delivery note
1 = Client number
2 = Client search name
3 = Department / Room number
4 = Department / Sequence number / Room number';

ALTER TABLE CUSTOMERRESIDENTIALSETTINGS
  DROP CONSTRAINT SORTORDER_VALID;
ALTER TABLE CUSTOMERRESIDENTIALSETTINGS
  ADD CONSTRAINT SORTORDER_VALID
  CHECK (SORTORDER IN (0, 1, 2, 3, 4));