create sequence acctreq_seq;

create sequence batchuser_seq;

create sequence batch_seq;

create sequence datainfopk;

create sequence dsrequest;

create sequence expertcategoryid_seq;

create sequence foo_seq;

create sequence ntuple_variables_seq;

create sequence shift_seq;

create sequence sourceidseq;

create sequence tmpshift_seq;

create sequence trendabledata_seq;

create sequence trendablerange_seq;

create sequence visitor_seq;

create table acct_group (
  acctgrp                         varchar2(30) not null
);

create table acct_jira_group (
  acctgrp                         varchar2(30) not null,
  jiragrp                         varchar2(40) not null
);

create table acct_manager (
  acct_pk                         integer not null,
  acctgrp                         varchar2(30) not null,
  manager                         varchar2(50) not null,
  email_address                   varchar2(128) not null
);

create table acct_reqs (
  req_pk                          integer not null,
  fullname                        varchar2(80) not null,
  email                           varchar2(128) not null,
  acctgrp                         varchar2(30) not null,
  uacctid                         varchar2(128) not null,
  request_date                    date,
  accepted                        varchar2(1),
  accept_date                     date,
  comments                        varchar2(250)
);

create table applications (
  appid                           number(2) not null,
  application                     varchar2(100),
  shortname                       varchar2(5),
  url                             varchar2(50),
  page                            varchar2(30),
  dataprovider                    varchar2(20)
);

create table batch (
  batchid_pk                      integer not null,
  userid_fk                       integer not null,
  task_type                       varchar2(8) not null,
  task_name                       varchar2(255) not null,
  run_mode                        varchar2(8) not null,
  min_range                       integer,
  max_range                       integer,
  tcut                            varchar2(255),
  debug                           varchar2(1),
  requested_files                 integer,
  batch_number                    integer not null,
  submit_date                     timestamp(6),
  batch_status                    varchar2(14)
    default 'not submitted',
  output_dir                      varchar2(255),
  user_comment                    varchar2(255),
  batch_options                   varchar2(256)
    default '',
  extra_options                   varchar2(256)
    default '',
  max_filesize                    number
    default '-1'
);

create table batchuser (
  userid_pk                       integer not null,
  username                        varchar2(64) not null,
  email_address                   varchar2(128) not null,
  last_batch_number               integer,
  is_expert                       varchar2(1)
    default 'N'
);

create table datacatalog (
  node_id                         number(19) not null,
  node_type                       number(3) not null,
  name                            varchar2(255),
  parent_id                       number(19),
  id                              varchar2(255),
  child_index                     number(10)
);

create table datagroups (
  groupid                         integer not null,
  groupname                       varchar2(30) not null,
  descshort                       varchar2(50),
  desclong                        clob,
  owner                           varchar2(25),
  operator                        varchar2(25),
  parentgroup                     integer,
  appid                           number(2)
);

create table datainfo (
  id                              integer not null,
  name                            varchar2(100),
  title                           varchar2(200),
  path                            varchar2(50),
  logyaxis                        varchar2(1),
  label                           varchar2(30),
  style                           varchar2(30),
  appid                           integer not null,
  xaxislabel                      varchar2(100),
  yaxislabel                      varchar2(100),
  description                     long,
  information                     clob,
  create_date                     date,
  createdby                       varchar2(20),
  updated                         date,
  updatedby                       varchar2(20),
  type                            varchar2(100),
  metadata                        varchar2(100)
);

create table datalinks (
  linkid                          number not null,
  startappid                      number not null,
  endappid                        number not null,
  fromfk                          number not null,
  togroupid                       number,
  todata                          varchar2(60),
  owner                           varchar2(20),
  linkname                        varchar2(50)
);

create table datasourcemapping (
  datasourcemode                  varchar2(15) not null,
  pipelinedatasourcemode          varchar2(15) not null,
  isocdatasourcemode              varchar2(15) not null,
  pipelinedatabase                varchar2(30) not null,
  isocdatabase                    varchar2(30) not null
);

create table datasourcemode (
  datasourcemode                  varchar2(15) not null
);

create table datasourcemodemap (
  datasourcevariable              varchar2(50) not null,
  datasourcemode                  varchar2(15) not null,
  value                           varchar2(100) not null
);

create table datasourcemodevariables (
  datasourcevariable              varchar2(50) not null
);

create table datepicker (
  dateseed                        date
);

create table decorator (
  name                            varchar2(250) not null,
  url                             varchar2(250),
  title                           varchar2(250)
);

create table dp_astro_db (
  dbname                          varchar2(20) not null,
  dburl                           varchar2(256) not null
);

create table dp_config (
  config                          varchar2(20) not null,
  description                     varchar2(256) not null
);

create table dp_params (
  name                            varchar2(100) not null,
  value                           varchar2(256) not null,
  version                         varchar2(20)
);

create table dp_request (
  request_pk                      number not null,
  userid                          varchar2(32) not null,
  task_type                       varchar2(32) not null,
  user_comment                    varchar2(255),
  id                              varchar2(255) not null,
  request_data                    clob not null,
  submit_date                     timestamp(6) not null,
  output_dir                      varchar2(256),
  job_name                        varchar2(256),
  pipeline_task                   varchar2(50),
  unique_id                       number
    default 0 not null
);

create table dp_tabs (
  config                          varchar2(20) not null,
  tabname                         varchar2(50) not null,
  url                             varchar2(256) not null,
  displayorder                    number
);

create table dp_task_type (
  task_type                       varchar2(32) not null,
  url                             varchar2(255) not null
);

create table dp_version (
  version                         varchar2(20) not null
);

create table ds_request (
  request_pk                      number not null,
  userid_fk                       number not null,
  task_type                       varchar2(8) not null,
  user_comment                    varchar2(255),
  batch_ids                       varchar2(255) not null,
  request_data                    clob not null,
  submit_date                     date not null,
  stream_id                       varchar2(12)
    default 'null'
);

create table glast_visitor (
  visitorid                       integer not null,
  user_name                       varchar2(100) not null,
  arrivaldate                     date,
  departuredate                   date,
  comments                        varchar2(256),
  moddate                         date,
  entrydate                       date
);

create table global_params (
  key                             varchar2(20) not null,
  value                           number not null
);

create table groupleaf (
  leafid                          integer not null,
  dataid                          integer not null,
  groupid                         integer not null
);

create table histoinfo (
  id                              integer not null,
  histname                        varchar2(50),
  title                           varchar2(50),
  path                            varchar2(50),
  log                             varchar2(1),
  label                           varchar2(30),
  style                           varchar2(30),
  application                     varchar2(30),
  xaxislabel                      varchar2(50),
  yaxislabel                      varchar2(50),
  description                     long,
  information                     clob,
  create_date                     date,
  createdby                       varchar2(20),
  updated                         date,
  updatedby                       varchar2(20)
);

create table linkleaf (
  id                              number not null,
  linkid                          number not null,
  dataid                          number not null
);

create table menugroup (
  menugrp                         varchar2(30) not null,
  grppositon                      integer,
  grpstyle                        varchar2(100)
);

create table menumember (
  menumem                         varchar2(30) not null,
  menugrp                         varchar2(30) not null,
  memposition                     integer,
  memurl                          varchar2(250),
  memstyle                        varchar2(100),
  memlevel                        number
    default 1
);

create table monitormetadata (
  metaname                        varchar2(80) not null,
  oldmetavalue                    number(22),
  monitortime                     timestamp(6) not null,
  metavalue                       number not null
);

create table ntuple_variables (
  nameid_pk                       integer not null,
  name                            varchar2(256) not null,
  type                            varchar2(16) not null,
  description                     varchar2(256),
  tuple                           varchar2(8)
    default 'MERIT' not null
);

create table oncall_expert (
  user_name                       varchar2(100) not null,
  expertcategoryid                number(10) not null
);

create table oncall_expertcategory (
  expertcategoryid                number(10) not null,
  category                        varchar2(20) not null,
  catgroup                        varchar2(20) not null,
  catsubgroup                     varchar2(20),
  catsubgrouptype                 varchar2(20),
  description                     varchar2(200) not null
);

create table profile_gg (
  parent_id                       varchar2(100) not null,
  child_id                        varchar2(100) not null
);

create table profile_group (
  group_name                      varchar2(100) not null,
  group_manager                   varchar2(100)
);

create table profile_ug (
  user_id                         varchar2(100) not null,
  group_id                        varchar2(100) not null
);

create table profile_user (
  user_name                       varchar2(100) not null,
  first_name                      varchar2(100) not null,
  last_name                       varchar2(100) not null,
  email                           varchar2(100),
  im                              varchar2(100),
  slac_username                   varchar2(100),
  voice_phone                     varchar2(100),
  fax                             varchar2(100),
  lab_phone                       varchar2(100),
  secretary_phone                 varchar2(100),
  pager                           varchar2(100),
  home_phone                      varchar2(100),
  alt_email                       varchar2(100),
  mobilephone                     varchar2(100),
  mobileemail                     varchar2(100)
);

create global temporary table results_temp (
  user_name                       varchar2(80)
) on commit preserve rows;

create table shift_block (
  block_id                        number(10) not null,
  block_name                      varchar2(80) not null
);

create table shift_blockbkup_16jun08 (
  block_id                        number(10) not null,
  block_name                      varchar2(80) not null
);

create table shift_block_8apr2008 (
  block_id                        number(10),
  block_name                      varchar2(80)
);

create table shift_block_bk30may08 (
  block_id                        number(10) not null,
  block_name                      varchar2(80) not null
);

create table shift_category (
  category_name                   varchar2(80) not null
);

create table shift_signup (
  starttime                       timestamp(6) not null,
  stoptime                        timestamp(6) not null,
  user_name                       varchar2(100),
  shift_type_id                   number(10) not null,
  block_id                        number(10),
  shift_id                        number(10) not null
);

create table shift_signupbkup_16jun08 (
  starttime                       timestamp(6) not null,
  stoptime                        timestamp(6) not null,
  user_name                       varchar2(100),
  shift_type_id                   number(10) not null,
  block_id                        number(10),
  shift_id                        number(10) not null
);

create table shift_signup_11aug08 (
  starttime                       timestamp(6) not null,
  stoptime                        timestamp(6) not null,
  user_name                       varchar2(100),
  shift_type_id                   number(10) not null,
  block_id                        number(10),
  shift_id                        number(10) not null
);

create table shift_signup_25mar08 (
  slac_username                   varchar2(100) not null,
  starttime                       timestamp(6) not null,
  stoptime                        timestamp(6) not null,
  category_name                   varchar2(80) not null,
  shift_type                      varchar2(20) not null,
  user_name                       varchar2(100)
);

create table shift_signup_6aug08 (
  starttime                       timestamp(6) not null,
  stoptime                        timestamp(6) not null,
  user_name                       varchar2(100),
  shift_type_id                   number(10) not null,
  block_id                        number(10),
  shift_id                        number(10) not null
);

create table shift_signup_bk30may08 (
  starttime                       timestamp(6) not null,
  stoptime                        timestamp(6) not null,
  user_name                       varchar2(100),
  shift_type_id                   number(10) not null,
  block_id                        number(10),
  shift_id                        number(10) not null
);

create table shift_signup_bk3jun08 (
  starttime                       timestamp(6) not null,
  stoptime                        timestamp(6) not null,
  user_name                       varchar2(100),
  shift_type_id                   number(10) not null,
  block_id                        number(10),
  shift_id                        number(10) not null
);

create table shift_signup_bk6may08 (
  starttime                       timestamp(6),
  stoptime                        timestamp(6),
  user_name                       varchar2(100),
  shift_type_id                   number(10),
  block_id                        number(10)
);

create table shift_type (
  shift_type_id                   number(10) not null,
  shift_type_name                 varchar2(80) not null,
  category_name                   varchar2(80) not null,
  start_time_of_day               number(4) not null,
  end_time_of_day                 number(4) not null
);

create table styles (
  style                           clob
);

create table temp1 (
  user_name                       varchar2(100)
);

create table tomcat_apps (
  name                            varchar2(50) not null,
  server                          varchar2(20) not null
);

create table tomcat_servers (
  name                            varchar2(20) not null,
  host                            varchar2(100) not null,
  jk_port                         number not null,
  http_port                       number not null,
  skip                            number,
  servertype                      varchar2(10),
  jmx_port                        number not null
);

create table userpreferences (
  preference_name                 varchar2(20) not null,
  userid                          varchar2(20) not null,
  application                     varchar2(240) not null,
  data                            clob
);

alter table "BIN$TMDi6kRPS0vgRAAUT5fcQA==$0"
add constraint "BIN$TMDi6kRMS0vgRAAUT5fcQA==$0"
primary key (
  id
);

alter table datasourcemapping
add constraint datasourcemapping_pk
primary key (
  datasourcemode
);

alter table oncall_expertcategory
add constraint expertisecategoryid_pk
primary key (
  expertcategoryid
);

alter table oncall_expert
add constraint expert_pk
primary key (
  user_name,
  expertcategoryid
);

alter table profile_group
add constraint group_pk
primary key (
  group_name
);

alter table menumember
add constraint menumember_pk
primary key (
  menumem,
  menugrp
);

alter table acct_group
add constraint pk_acctgrp
primary key (
  acctgrp
);

alter table acct_manager
add constraint pk_acct_pk
primary key (
  acct_pk
);

alter table userpreferences
add constraint pk_app
primary key (
  preference_name,
  userid,
  application
);

alter table applications
add constraint pk_appid
primary key (
  appid
);

alter table batch
add constraint pk_batchid
primary key (
  batchid_pk
);

alter table datainfo
add constraint pk_dataid
primary key (
  id
);

alter table decorator
add constraint pk_decname
primary key (
  name
);

alter table profile_gg
add constraint pk_gg
primary key (
  parent_id,
  child_id
);

alter table datagroups
add constraint pk_groupid
primary key (
  groupid
);

alter table histoinfo
add constraint pk_histid
primary key (
  id
);

alter table acct_jira_group
add constraint pk_jiragrp
primary key (
  acctgrp,
  jiragrp
);

alter table groupleaf
add constraint pk_leafid
primary key (
  leafid
);

alter table menugroup
add constraint pk_menugrp
primary key (
  menugrp
);

alter table ntuple_variables
add constraint pk_nameid
primary key (
  nameid_pk
);

alter table acct_reqs
add constraint pk_reqpk
primary key (
  req_pk
);

alter table profile_ug
add constraint pk_ug
primary key (
  user_id,
  group_id
);

alter table batchuser
add constraint pk_userid_pk
primary key (
  userid_pk
);

alter table shift_block
add constraint shiftblock_pk
primary key (
  block_id
);

alter table shift_signup
add constraint shiftid_pk
primary key (
  shift_id
);

alter table shift_type
add constraint shifttype_pk
primary key (
  shift_type_id
);

alter table datacatalog
add constraint sys_c0020933
primary key (
  node_id
);

alter table ds_request
add constraint sys_c0020952
primary key (
  request_pk
);

alter table datalinks
add constraint sys_c0020961
primary key (
  linkid
);

alter table linkleaf
add constraint sys_c0021639
primary key (
  id
);

alter table dp_request
add constraint sys_c0053010
primary key (
  request_pk
);

alter table dp_task_type
add constraint sys_c0053083
primary key (
  task_type
);

alter table dp_config
add constraint sys_c0053087
primary key (
  config
);

alter table dp_version
add constraint sys_c0055578
primary key (
  version
);

alter table tomcat_apps
add constraint sys_c0060738
primary key (
  name
);

alter table tomcat_servers
add constraint sys_c0060865
primary key (
  name
);

alter table global_params
add constraint sys_c0063801
primary key (
  key
);

alter table datasourcemode
add constraint sys_c0074317
primary key (
  datasourcemode
);

alter table datasourcemodevariables
add constraint sys_c0074327
primary key (
  datasourcevariable
);

alter table dp_astro_db
add constraint sys_c0094804
primary key (
  dbname
);

alter table profile_user
add constraint user_pk
primary key (
  user_name
);

alter table glast_visitor
add constraint visitor_pk
primary key (
  visitorid
);

alter table dp_params
add constraint dp_params_pk
unique (
  name,
  version
);

alter table menumember
add constraint menu_position
unique (
  menugrp,
  memposition
);

alter table applications
add constraint uidx_app
unique (
  appid,
  application
);

alter table batchuser
add constraint uidx_email_address
unique (
  email_address
);

alter table datainfo
add constraint uidx_histdata
unique (
  name,
  appid
);

alter table histoinfo
add constraint uidx_histname
unique (
  histname,
  application
);

alter table datagroups
add constraint unq_datagroup
unique (
  groupid,
  groupname,
  parentgroup
);

alter table groupleaf
add constraint unq_dataleaf
unique (
  leafid,
  dataid,
  groupid
);

alter table datasourcemodemap
add constraint unq_datasourcemodemap
unique (
  datasourcevariable,
  datasourcemode,
  value
);

alter table oncall_expertcategory
add constraint unq_expertcategory
unique (
  category,
  catgroup,
  catsubgroup,
  catsubgrouptype
);

alter table profile_gg
add constraint child_fk
foreign key (
  child_id
) references profile_group (
  group_name
) on delete cascade;

alter table oncall_expert
add constraint expertusername_fk
foreign key (
  user_name
) references profile_user (
  user_name
);

alter table oncall_expert
add constraint expert_expertcategory_fk
foreign key (
  expertcategoryid
) references oncall_expertcategory (
  expertcategoryid
);

alter table datacatalog
add constraint fke5f0f10f1c0bdb57
foreign key (
  parent_id
) references datacatalog (
  node_id
);

alter table datagroups
add constraint fkgrp_appid
foreign key (
  appid
) references applications (
  appid
) on delete cascade;

alter table acct_manager
add constraint fk_acct
foreign key (
  acctgrp
) references acct_group (
  acctgrp
);

alter table datainfo
add constraint fk_application
foreign key (
  appid
) references applications (
  appid
);

alter table datasourcemodemap
add constraint fk_datasourcemode
foreign key (
  datasourcemode
) references datasourcemode (
  datasourcemode
);

alter table datasourcemodemap
add constraint fk_datasourcevariable
foreign key (
  datasourcevariable
) references datasourcemodevariables (
  datasourcevariable
);

alter table dp_tabs
add constraint fk_dp_config
foreign key (
  config
) references dp_config (
  config
);

alter table dp_request
add constraint fk_dp_task_type
foreign key (
  task_type
) references dp_task_type (
  task_type
);

alter table ds_request
add constraint fk_dsrequid
foreign key (
  userid_fk
) references batchuser (
  userid_pk
);

alter table datagroups
add constraint fk_grpparent
foreign key (
  parentgroup
) references datagroups (
  groupid
) on delete cascade;

alter table acct_jira_group
add constraint fk_jiraacctgrp
foreign key (
  acctgrp
) references acct_group (
  acctgrp
);

alter table groupleaf
add constraint fk_leafdataid
foreign key (
  dataid
) references datainfo (
  id
) on delete cascade;

alter table groupleaf
add constraint fk_leafgrpid
foreign key (
  groupid
) references datagroups (
  groupid
) on delete cascade;

alter table menumember
add constraint fk_menugrp
foreign key (
  menugrp
) references menugroup (
  menugrp
);

alter table dp_params
add constraint fk_params
foreign key (
  version
) references dp_version (
  version
);

alter table acct_reqs
add constraint fk_userid
foreign key (
  acctgrp
) references acct_group (
  acctgrp
);

alter table batch
add constraint fk_userid_fk
foreign key (
  userid_fk
) references batchuser (
  userid_pk
);

alter table profile_ug
add constraint group_fk
foreign key (
  group_id
) references profile_group (
  group_name
) on delete cascade;

alter table profile_gg
add constraint parent_fk
foreign key (
  parent_id
) references profile_group (
  group_name
) on delete cascade;

alter table tomcat_apps
add constraint server_fk
foreign key (
  server
) references tomcat_servers (
  name
);

alter table shift_signup
add constraint shiftsignupshifttype_fk
foreign key (
  shift_type_id
) references shift_type (
  shift_type_id
);

alter table profile_ug
add constraint user_fk
foreign key (
  user_id
) references profile_user (
  user_name
) on delete cascade;

alter table glast_visitor
add constraint visitor_fk
foreign key (
  user_name
) references profile_user (
  user_name
) on delete cascade;

alter table acct_reqs
add constraint chk_accept
check (accepted in('Y','N','P'));

alter table batch
add constraint chk_batch_status
check (batch_status in
      ('not submitted', 'submitted', 'running','finished'));

alter table batch
add constraint chk_debug
check (debug in ('Y', 'N'));

alter table ntuple_variables
add constraint chk_type
check (type in ('int', 'boolean', 'float', 'double', 'string'));

create index idx_dp_task_type_user_id
on dp_request (
  task_type,
  userid,
  unique_id
);

create index idx_monitormetadata
on monitormetadata (
  metaname,
  monitortime
);

create index idx_monitormetaname
on monitormetadata (
  metaname
);

create or replace PACKAGE GLAST_UTIL
AS
	-- Package-specific types:

	TYPE CURSOR IS REF CURSOR;

	FUNCTION GetTimeFromEpochMS(Time_in TIMESTAMP) RETURN int;

	FUNCTION GetDeltaSeconds(TimeDiff_in INTERVAL DAY TO SECOND) RETURN int;
END GLAST_UTIL;
/

create or replace PACKAGE SHIFT
AS
	-- Package-specific types:

	TYPE CURSOR IS REF CURSOR;

	FUNCTION GetShiftWeek(Date_in DATE) RETURN CURSOR;
END SHIFT;
/

create or replace PACKAGE BODY GLAST_UTIL
AS
	FUNCTION GetTimeFromEpochMS(Time_in TIMESTAMP) RETURN int IS
		TimeDiff INTERVAL DAY(9) TO SECOND;
		Epoch TIMESTAMP;
		msFromEpoch_out int;
	BEGIN
		select TIMESTAMP'1970-01-01 00:00:00.000'
			into Epoch
			from dual;

		select Time_in - Epoch
			into TimeDiff
			from Dual;

		select 1000*(Extract(SECOND FROM TimeDiff) + 60*(Extract(MINUTE FROM TimeDiff) + 60*(Extract(HOUR FROM TimeDiff) + 24*Extract(DAY FROM TimeDiff))))
			into msFromEpoch_out
			from Dual;
		RETURN msFromEpoch_out;
	END;


	FUNCTION GetDeltaSeconds(TimeDiff_in INTERVAL DAY TO SECOND) RETURN int IS
		seconds_out int;
	BEGIN
		select Extract(SECOND FROM TimeDiff_in) + 60*(Extract(MINUTE FROM TimeDiff_in) + 60*(Extract(HOUR FROM TimeDiff_in) + 24*Extract(DAY FROM TimeDiff_in)))
			into seconds_out
			from Dual;
		RETURN seconds_out;
	END;

END GLAST_UTIL;
/

create or replace PACKAGE BODY SHIFT
AS

	FUNCTION GetShiftWeekStartDate(Date_in DATE) RETURN DATE IS
		Date_out DATE;
	BEGIN
		select NEXT_DAY(Date_in,'MON') - INTERVAL '7' DAY
			into Date_out
			from Dual;

		RETURN Date_out;
	END;


	FUNCTION GetShiftWeek(Date_in DATE) RETURN CURSOR IS
		ShiftWeekStartDay DATE;
		RS CURSOR;
	BEGIN
		ShiftWeekStartDay := GetShiftWeekStartDate(Date_in);

		OPEN RS FOR
			with
				st_rows AS
					(select distinct st.shift_type_name, st.category_name, st.shift_type_id from shift_type st),
				ss_rows AS
					(select ss.shift_type_id, NVL(ss.user_name, 'AVAILABLE') shifter, trunc(ss.starttime) shift_date from shift_signup ss)
			select
				st.shift_type_name,
				st.category_name,
				ssMon.shifter Monday,
				ssTue.shifter Tuesday,
				ssWed.shifter Wednesday,
				ssThu.shifter Thursday,
				ssFri.shifter Friday,
				ssSat.shifter Saturday,
				ssSun.shifter Sunday
			from
				st_rows st
				join ss_rows ssMon using (shift_type_id)
				join ss_rows ssTue using (shift_type_id)
				join ss_rows ssWed using (shift_type_id)
				join ss_rows ssThu using (shift_type_id)
				join ss_rows ssFri using (shift_type_id)
				join ss_rows ssSat using (shift_type_id)
				join ss_rows ssSun using (shift_type_id)
			where
				ssMon.shift_date = ShiftWeekStartDay
				and ssTue.shift_date = ShiftWeekStartDay + INTERVAL '1' DAY
				and ssWed.shift_date = ShiftWeekStartDay + INTERVAL '2' DAY
				and ssThu.shift_date = ShiftWeekStartDay + INTERVAL '3' DAY
				and ssFri.shift_date = ShiftWeekStartDay + INTERVAL '4' DAY
				and ssSat.shift_date = ShiftWeekStartDay + INTERVAL '5' DAY
				and ssSun.shift_date = ShiftWeekStartDay + INTERVAL '6' DAY
				order by st.shift_type_name, st.category_name;

		RETURN RS;
	END;

END SHIFT;
/

create or replace trigger AcctReq_pk
  before insert
    on Acct_reqs
    for each row
begin
  select acctReq_Seq.nextval
     into :new.req_pk
     from dual;
end;
/

create or replace trigger "GLASTGEN"."BIN$TMDi6kROS0vgRAAUT5fcQA==$0" 
  before insert
     on BIN$TMDi6kRPS0vgRAAUT5fcQA==$0
     for each row
begin
  select foo_seq.nextval
     into :new.id
    from dual;
end;
/

create or replace trigger ExpertCategoryID_pk
  before insert
    on OnCall_ExpertCategory
    for each row
begin
  select ExpertCategoryID_seq.nextval
    into :new.ExpertCategoryID
    from dual;
end;
/

create or replace trigger "GLASTGEN"."SHIFT_BLOCK_PK"
  before insert
    on shift_block
    for each row
begin
  select shift_seq.nextval
    into :new.block_id
    from dual;
end;
/

create or replace trigger "GLASTGEN"."SHIFT_ID_PK"
  before insert
    on shift_signup
    for each row
begin
  select shift_seq.nextval
    into :new.shift_id
    from dual;
end;
/

create or replace trigger "GLASTGEN"."SHIFT_TYPE_PK"
  before insert
    on shift_type
    for each row
begin
  select shift_seq.nextval
    into :new.shift_type_id
    from dual;
end;
/

create or replace trigger "GLASTGEN"."bifer_batch_id_pk" 
  before insert
     on batch
     for each row
begin
  select batch_seq.nextval
     into :new.batchid_pk
    from dual;
end;
/

create or replace trigger "GLASTGEN"."bifer_batchuser_id_pk" 
  before insert
     on batchuser
     for each row
begin
  select batchuser_seq.nextval
     into :new.userid_pk
    from dual;
end;
/

create or replace trigger "GLASTGEN"."bifer_ntuple_variables_id_pk" 
  before insert
     on ntuple_variables
     for each row
begin
  select ntuple_variables_seq.nextval
     into :new.nameid_pk
    from dual;
end;
/

