Table LocationPriority

Table to create per-user picking order for special locations.

DDL

create sequence seqLocationPriority as bigint start with 1;

create table LocationPriority (
   lpID bigint not null default next value for seqLocationPriority,
   lpCreatedOn datetime not null default sysdatetime(),
   lpCreatedByFK bigint not null,
   lpChangedOn datetime not null default sysdatetime(),
   lpChangedByFK bigint not NULL,
   lpLocationFK pointer not null,
   lpCustomerFK pointer not null,
   lpPriority smallint not null,
   CONSTRAINT pkLocationPriority PRIMARY key (lpID)
 );
 create unique index udxLocationPriority on LocationPriority (lpLocationFK,lpCustomerFK)

Default Data

insert into LocationPriority (lpCreatedByFK, lpChangedByFK,lpLocationFK, lpCustomerFK,lpPriority )
SELECT
   1,1,  lID, (select cID from customer where cName='JAMBERRY') ,

   (CASE
     when lName>='GG00001' and lName<='GG00300' and LEFT(lName, 2)='GG' then 1
     when lName>='FA01A' and lName<='FA50F' and LEFT(lName, 2)='FA' then 1
     when lName>='FB01A' and lName<='FB50F' and LEFT(lName, 2)='FB' then 1
     when lName>='A00001' and lName<='A00516' and LEFT(lName, 2)='A0' then 2
     when lName>='H.25.01.A' and lName<='H.90.01.A' and LEFT(lName, 2)='H.' then 2
     when lName>='H.01.01.A' and lName<='H.23.36.D' and LEFT(lName, 2)='H.' then 3
     when lName in ('L.99.01.A', 'H.99.01.A', 'R.00.00.A') then 4
    ELSE 3 END)
  AS LocationLevelPriority
from
  location  
where
  (lName>='GG00001' and lName<='GG00300' and LEFT(lName, 2)='GG')
  or (lName>='FA01A' and lName<='FA50F' and LEFT(lName, 2)='FA')
  or (lName>='FB01A' and lName<='FB50F' and LEFT(lName, 2)='FB')
  or (lName>='A00001' and lName<='A00516' and LEFT(lName, 2)='A0')
  or (lName>='H.25.01.A' and lName<='H.90.01.A' and LEFT(lName, 2)='H.')
  or (lName>='H.01.01.A' and lName<='H.23.36.D' and LEFT(lName, 2)='H.')
  or (lName in ('L.99.01.A', 'H.99.01.A', 'R.00.00.A'))

Mapping

None, new table.