\echo Set materialised view QUERY_PER_TASK⠒MV up

drop materialized view if exists QUERY_PER_TASK⠒MV;

create materialized view QUERY_PER_TASK⠒MV as
           select TDP.TOP_LEVEL_TASK_TYPE⠒NAME,
                  TDP.TOP_LEVEL_TASK⠒NAME,
                  TDP.TASK_TREE⠒HIGHEST_PRIORITY,
                  TDP.TASK⠒NAME,
                  TDP.TASK_TYPE⠒NAME,
                  TDP.TASK⠒PRIORITY,
                  TDP.TASK⠒DESCRIPTION,
                  TDP.GOOD⠒NAME,
                  TDP.GOOD_4_THIS_TASK,
                  TDP.QUANTITY⠒DROP_OFF,
                  TDP.QUANTITY⠒NEEDED_4_THIS_REGION,
                  TDP.QUANTITY⠒NEEDED_4_THIS_TASK_TREE,
                  TDP.QUANTITY⠒PICK_UP,
                  TDP.QUANTITY⠒READY_4_THIS_REGION,
                  TDP.GOOD⠒SIZE,
                  TDP.GOOD⠒SIZE * TDP.QUANTITY⠒DROP_OFF
                                      as NEEDED_TRANSPORT_CAPACITY_4_THIS_TASK,
                  TDP.NEEDED_TRANSPORT_CAPACITY_4_THIS_REGION,
                  TDP.NEEDED_TRANSPORT_CAPACITY_4_THIS_TASK_TREE,
                  case TDP.NODE_TYPE⠒NAME⠒PICK_UP
                      when 'Crafting zone' then R.GOOD⠒RESOURCE⠒NAME
                      else null
                  end                 as GOOD⠒RESOURCE⠒NAME,
                  P.QUANTITY⠒READY_4_THIS_REGION
                                      as RESOURCE_QUANTITY⠒READY_4_THIS_REGION,
                  TDP.NODE⠒MAP⠒NAME⠒DROP_OFF,
                  TDP.NODE⠒NAME⠒DROP_OFF,
                  TDP.NODE⠒MAP⠒NAME⠒PICK_UP,
                  TDP.NODE⠒NAME⠒PICK_UP,
                  P.NODE⠒MAP⠒NAME     as NODE⠒MAP⠒NAME⠒PICK_UP_RESOURCE,
                  P.NODE⠒NAME         as NODE⠒NAME⠒PICK_UP_RESOURCE,
                  TDP.DIRECTION⠒CODE⠒DROP_OFF,
                  TDP.CENTRICITY⠒NAME⠒DROP_OFF,
                  TDP.DIRECTION⠒CODE⠒PICK_UP,
                  TDP.CENTRICITY⠒NAME⠒PICK_UP,
                  TDP.NODE⠒DESCRIPTION⠒DROP_OFF,
                  TDP.PICK_UP⠒DESCRIPTION,
                  TDP.NODE⠒DESCRIPTION⠒PICK_UP,
                  TDP.DIRECTION⠒DESCRIPTION⠒DROP_OFF,
                  TDP.CENTRICITY⠒DESCRIPTION⠒DROP_OFF,
                  TDP.DIRECTION⠒DESCRIPTION⠒PICK_UP,
                  TDP.CENTRICITY⠒DESCRIPTION⠒PICK_UP,
                  P.DIRECTION⠒DESCRIPTION
                                      as DIRECTION⠒DESCRIPTION⠒PICK_UP_RESOURCE,
                  P.CENTRICITY⠒DESCRIPTION
                                      as CENTRICITY⠒DESCRIPTION⠒PICK_UP_RESOURCE,
                  TDP.GOOD⠒DESCRIPTION,
                  TDP.GOOD⠒WEIGHT,
                  TDP.PATH,
                  TDP.REGION⠒NAME,
                  TDP.TOP_LEVEL_TASK⠒PRIORITY,
                  TDP.REGION⠒ID,
                  TDP.TASK_PREDECESSOR⠒ID,
                  TDP.TASK⠒ID,
                  TDP.LEVEL,
                  TDP.GOOD⠒ID
             from SNOWRUNNER.TASK_HIERARCHY_DROP_OFF_PICK_UP_AVAIL⠒V TDP
  left outer join SNOWRUNNER.PRODUCTION⠒V R
               on 1 = 1
              and TDP.GOOD⠒ID = R.GOOD⠒PRODUCT⠒ID
              and TDP.REGION⠒ID = R.REGION⠒ID
  left outer join SNOWRUNNER.PICK_UP⠒V P
               on 1 = 1
              and R.GOOD⠒RESOURCE⠒ID = P.GOOD⠒ID
              and R.REGION⠒ID = P.REGION⠒ID
            where 1 = 1
         order by TDP.REGION⠒NAME asc,
                  TASK_TREE⠒HIGHEST_PRIORITY asc,
                  NODE⠒MAP⠒NAME⠒DROP_OFF asc,
                  NODE⠒MAP⠒NAME⠒PICK_UP asc,
                  NODE⠒MAP⠒NAME⠒PICK_UP asc,
                  NODE⠒MAP⠒NAME⠒PICK_UP_RESOURCE asc,
                  NODE⠒NAME⠒PICK_UP_RESOURCE asc,
                  GOOD⠒NAME asc,
                  NODE⠒MAP⠒NAME⠒DROP_OFF asc,
                  PATH asc
  with data;

refresh materialized view QUERY_PER_TASK⠒MV
  with data;


comment on materialized view QUERY_PER_TASK⠒MV is
  '
$Header$';


commit;