root/rita/doc/master-schema-from-aaron.sql

-- sed -i.orig -e '/^-- TOC entry/d' -e '/^-- Dependencies:/d' -e '/^SET /d'
-- svn diff --diff-cmd=diff -x "-u -F TABLE"

--
-- PostgreSQL database dump
--

-- Started on 2010-07-14 14:25:34



--
-- Name: list_valid_actions(character varying, character varying, integer, integer, integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION list_valid_actions("ScreenCode" character varying, "SelectedViewpoint" character varying, "ActiveUser" integer, "ActiveProject" integer, "ShowHiddenActions" integer, OUT action_code character varying, OUT description character varying, OUT is_hidden integer, OUT seqno integer) RETURNS SETOF record
    LANGUAGE sql
    AS $_$select distinct LA.screen_code || '_' || LA.list_action_code As action_code
     , LA.description
     , 1 - LA.is_displayed as is_hidden
     , LA.seqno

-- Source (Note: All tables must be included the query, to ensure all deleted rows are excluded)
  from viewpoints             as VP
     , list_action_viewpoints as LV
     
     , list_action            as LA
     
     , list_action_roles      as LR
     , user_roles             as UR
     , users                  as US

     , project                as PR

-- Changeable criteria
 where VP.screen_code      = upper($1)
   and VP.viewpoint_code   = upper($2)
   and UR.user_id          = $3
   and (   UR.role_code In ('ADM', 'CEN', 'RPA')
        Or UR.project_id   = $4)

-- Joins
   and VP.screen_code      = LV.screen_code
   and VP.viewpoint_code   = LV.viewpoint_code
   
   and LV.screen_code      = LA.screen_code
   and LV.list_action_code = LA.list_action_code
   
   and LA.screen_code      = LR.screen_code
   and LA.list_action_code = LR.list_action_code
   
   and LR.role_code        = UR.role_code
   and UR.user_id          = US.id

   and PR.id               = $4

-- Exclude hidden records
   and ($5 = 1 Or ($5 = 0 and LA.is_displayed     = 1))

-- Exclude deleted records
   and VP.is_deleted       = 0
   and LV.is_deleted       = 0
   and LA.is_deleted       = 0   
   and LR.is_deleted       = 0
   and UR.is_deleted       = 0   
   and US.is_deleted       = 0

-- Exclude expired records
   and (US.expiry_date Is Null or US.expiry_date > Now())
   and (UR.expiry_date Is Null or UR.expiry_date > Now())

-- Exclude selected actions if the project is closed
   and (   (LA.is_allowed_if_proj_closed = 1)
        or (LA.is_allowed_if_proj_closed = 0 And PR.end_date Is Null)
        or (LA.is_allowed_if_proj_closed = 0 And PR.end_date > Now()))

order by LA.seqno$_$;


ALTER FUNCTION public.list_valid_actions("ScreenCode" character varying, "SelectedViewpoint" character varying, "ActiveUser" integer, "ActiveProject" integer, "ShowHiddenActions" integer, OUT action_code character varying, OUT description character varying, OUT is_hidden integer, OUT seqno integer) OWNER TO postgres;

--
-- Name: list_valid_projects(character varying, integer, integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION list_valid_projects("SelectedViewpoint" character varying, "ActiveUser" integer, "ShowClosedProjects" integer, OUT project_id integer, OUT project_name character varying, OUT project_description character varying, OUT project_is_closed integer) RETURNS SETOF record
    LANGUAGE sql
    AS $_$

select distinct PR.id
     , PR.name
     , PR.description
     , case when (PR.end_date Is Null or PR.end_date > Now()) then 0 else 1 end as is_project_closed

-- Source (Note: All tables are required to ensure all deleted rows are excluded)
  from viewpoints             as VP
     , list_action_viewpoints as LV
     
     , list_action            as LA
     
     , list_action_roles      as LR

     , users                  as US
     , user_roles             as UR

-- Project table is not directly linked to any other table in the query     
     , project                as PR

-- This query is only applicable for ScreenCode ‘MM’
 where VP.screen_code      = 'MM'

-- Changeable criteria
   and VP.viewpoint_code   = upper($1)
   and UR.user_id          = $2

-- Joins
   and VP.screen_code      = LV.screen_code
   and VP.viewpoint_code   = LV.viewpoint_code
   
   and LV.screen_code      = LA.screen_code
   and LV.list_action_code = LA.list_action_code
   
   and LA.screen_code      = LR.screen_code
   and LA.list_action_code = LR.list_action_code
   
   and LR.role_code        = UR.role_code
   and UR.user_id          = US.id

-- Exclude deleted records
   and VP.is_deleted       = 0
   and LV.is_deleted       = 0
   and LA.is_deleted       = 0   
   and LR.is_deleted       = 0
   and UR.is_deleted       = 0   
   and US.is_deleted       = 0
   and PR.is_deleted       = 0 

-- Exclude expired users
   and (US.expiry_date Is Null or US.expiry_date > Now())
   and (UR.expiry_date Is Null or UR.expiry_date > Now())

-- Exclude closed projects (if required)
   and ($3 > 0 Or ($3 = 0 And (PR.end_date Is Null or PR.end_date > Now())))
   
-- If this is a GLOBAL Role, display all current Projects, 
-- Otherwise, display only those projects that are referenced 
-- in the User_Role table for the selected user
   and (   (UR.project_id Is Null)
        or (UR.project_id =PR.id )) 

 order by PR.name


$_$;


ALTER FUNCTION public.list_valid_projects("SelectedViewpoint" character varying, "ActiveUser" integer, "ShowClosedProjects" integer, OUT project_id integer, OUT project_name character varying, OUT project_description character varying, OUT project_is_closed integer) OWNER TO postgres;

--
-- Name: list_valid_viewpoints(character varying, integer, integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION list_valid_viewpoints("ScreenCode" character varying, "ActiveUser" integer, "ActiveProject" integer, OUT viewpoint_code character, OUT description character varying, OUT viewpoint_site_desc character varying, OUT seqno integer, OUT is_default smallint) RETURNS SETOF record
    LANGUAGE sql
    AS $_$select distinct VP.viewpoint_code
     , VP.description
     , VP.viewpoint_site_name
     , VP.seqno
     , VP.is_default

-- Source (Note: All tables must be included the query, to ensure all deleted rows are excluded)
  from viewpoints             as VP
     , list_action_viewpoints as LV
     
     , list_action            as LA
     
     , list_action_roles      as LR
     , user_roles             as UR
     , users                  as US

     , project                as PR

-- Changeable criteria
 where VP.screen_code      = upper($1)
   and UR.user_id          = $2
   and (   UR.role_code In ('ADM', 'CEN', 'RPA')
        Or UR.project_id   = $3)

-- Joins
   and VP.screen_code      = LV.screen_code
   and VP.viewpoint_code   = LV.viewpoint_code
   
   and LV.screen_code      = LA.screen_code
   and LV.list_action_code = LA.list_action_code
   
   and LA.screen_code      = LR.screen_code
   and LA.list_action_code = LR.list_action_code
   
   and LR.role_code        = UR.role_code
   and UR.user_id          = US.id

   and PR.id               = $3

-- Exclude hidden records
--   and LA.is_displayed     = 1

-- Exclude deleted records
   and VP.is_deleted       = 0
   and LV.is_deleted       = 0
   and LA.is_deleted       = 0   
   and LR.is_deleted       = 0
   and UR.is_deleted       = 0   
   and US.is_deleted       = 0

-- Exclude expired records
   and (US.expiry_date Is Null or US.expiry_date > Now())
   and (UR.expiry_date Is Null or UR.expiry_date > Now())

-- Exclude selected actions if the project is closed
   and (   (VP.is_allowed_if_proj_closed = 1)
        or (VP.is_allowed_if_proj_closed = 0 And PR.end_date Is Null)
        or (VP.is_allowed_if_proj_closed = 0 And PR.end_date > Now()))

order by VP.seqno$_$;


ALTER FUNCTION public.list_valid_viewpoints("ScreenCode" character varying, "ActiveUser" integer, "ActiveProject" integer, OUT viewpoint_code character, OUT description character varying, OUT viewpoint_site_desc character varying, OUT seqno integer, OUT is_default smallint) OWNER TO postgres;

--
-- Name: validate_user_action(character varying, character varying, integer, integer, character varying); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION validate_user_action("ScreenCode" character varying, "SelectedViewpoint" character varying, "ActiveUser" integer, "ActiveProject" integer, "RequestedAction" character varying) RETURNS bigint
    LANGUAGE sql
    AS $_$select count(*)

-- Source (Note: All tables must be included the query, to ensure all deleted rows are excluded)
  from viewpoints             as VP
     , list_action_viewpoints as LV
     
     , list_action            as LA
     
     , list_action_roles      as LR
     , user_roles             as UR
     , users                  as US

     , project                as PR

-- Changeable criteria
 where VP.screen_code      = upper($1)
   and VP.viewpoint_code   = upper($2)
   and UR.user_id          = $3
   and (   UR.Role_code In ('ADM', 'CEN', 'RPA')
        Or UR.Project_ID   = $4)
       
   and PR.id               = $4
   
   and LA.list_action_code = upper($5)


-- Joins
   and VP.screen_code      = LV.screen_code
   and VP.viewpoint_code   = LV.viewpoint_code
   
   and LV.screen_code      = LA.screen_code
   and LV.list_action_code = LA.list_action_code
   
   and LA.screen_code      = LR.screen_code
   and LA.list_action_code = LR.list_action_code
   
   and LR.role_code        = UR.role_code
   and UR.user_id          = US.id

-- Exclude deleted records
   and VP.is_deleted       = 0
   and LV.is_deleted       = 0
   and LA.is_deleted       = 0   
   and LR.is_deleted       = 0
   and UR.is_deleted       = 0   
   and US.is_deleted       = 0

-- Exclude expired records
   and (US.expiry_date Is Null or US.expiry_date > Now())
   and (UR.expiry_date Is Null or UR.expiry_date > Now())

-- Exclude selected actions if the project is closed
   and (   (LA.is_allowed_if_proj_closed = 1)
        or (LA.is_allowed_if_proj_closed = 0 And PR.end_date Is Null)
        or (LA.is_allowed_if_proj_closed = 0 And PR.end_date > Now()))


   $_$;


ALTER FUNCTION public.validate_user_action("ScreenCode" character varying, "SelectedViewpoint" character varying, "ActiveUser" integer, "ActiveProject" integer, "RequestedAction" character varying) OWNER TO postgres;



--
-- Name: arrival_type; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE arrival_type (
    code character(1) NOT NULL,
    description character varying(40) NOT NULL,
    record_version bigint NOT NULL,
    is_deleted smallint NOT NULL
);


ALTER TABLE public.arrival_type OWNER TO postgres;

--
-- Name: COLUMN arrival_type.code; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN arrival_type.code IS 'Code of the screen using the viewpoint';


--
-- Name: COLUMN arrival_type.description; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN arrival_type.description IS 'Description';


--
-- Name: COLUMN arrival_type.record_version; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN arrival_type.record_version IS 'Audit field: Lastest (local) change version number of the data';


--
-- Name: COLUMN arrival_type.is_deleted; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN arrival_type.is_deleted IS 'Record has been deleted (Remove from current slection lists)';


--
-- Name: building_type; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE building_type (
    id integer NOT NULL,
    description character varying(255) NOT NULL,
    record_version bigint NOT NULL,
    is_deleted smallint NOT NULL
);


ALTER TABLE public.building_type OWNER TO postgres;

--
-- Name: COLUMN building_type.id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN building_type.id IS 'Site Building Type Code';


--
-- Name: COLUMN building_type.description; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN building_type.description IS 'English language description';


--
-- Name: COLUMN building_type.record_version; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN building_type.record_version IS 'Audit field: Lastest (local) change version number of the data';


--
-- Name: COLUMN building_type.is_deleted; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN building_type.is_deleted IS 'Record has been deleted (Remove from current slection lists)';


--
-- Name: bundle; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE bundle (
    bundle_site_id integer NOT NULL,
    id integer NOT NULL,
    bundle_type_id integer NOT NULL,
    bundle_qty integer NOT NULL,
    bundle_idnf character varying(40),
    is_new_bundle_provided smallint NOT NULL,
    record_version bigint NOT NULL,
    is_deleted smallint NOT NULL,
    CONSTRAINT ckc_bundle_qty_bundle CHECK ((bundle_qty >= 0))
);


ALTER TABLE public.bundle OWNER TO postgres;

--
-- Name: COLUMN bundle.bundle_site_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN bundle.bundle_site_id IS 'Site that owns the bundle record, and normally (except for goods arriving from CUST, or where the bundle was reconfigured upon arrival) the site at the origin of the movement';


--
-- Name: COLUMN bundle.id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN bundle.id IS 'Internally generated unique identifier';


--
-- Name: COLUMN bundle.bundle_type_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN bundle.bundle_type_id IS 'Container Type';


--
-- Name: COLUMN bundle.bundle_qty; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN bundle.bundle_qty IS 'Number of containers';


--
-- Name: COLUMN bundle.bundle_idnf; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN bundle.bundle_idnf IS 'Container identifier (either NULL or unique within a journey)';


--
-- Name: COLUMN bundle.is_new_bundle_provided; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN bundle.is_new_bundle_provided IS 'Were new pallets (containers) provided? Multiply cost per container x ContainerQty';


--
-- Name: COLUMN bundle.record_version; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN bundle.record_version IS 'Audit field: Lastest (local) change version number of the data';


--
-- Name: COLUMN bundle.is_deleted; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN bundle.is_deleted IS 'Record has been deleted (Remove from current slection lists)';


--
-- Name: bundle_type; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE bundle_type (
    id integer NOT NULL,
    description character varying(255) NOT NULL,
    group_id integer NOT NULL,
    enclosure_type_code character(2) NOT NULL,
    bundle_weight numeric(12,6) NOT NULL,
    bundle_volume numeric(12,6) NOT NULL,
    max_cap_weight numeric(12,6) NOT NULL,
    max_cap_volume numeric(12,6) NOT NULL,
    is_valid_for_land smallint NOT NULL,
    is_valid_for_water smallint NOT NULL,
    is_valid_for_air smallint NOT NULL,
    record_version bigint NOT NULL,
    is_deleted smallint NOT NULL,
    CONSTRAINT ckc_max_cap_volume_bundle_t CHECK ((max_cap_volume >= (0)::numeric)),
    CONSTRAINT ckc_max_cap_weight_bundle_t CHECK ((max_cap_weight >= (0)::numeric))
);


ALTER TABLE public.bundle_type OWNER TO postgres;

--
-- Name: COLUMN bundle_type.id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN bundle_type.id IS 'Internally generated unique identifier';


--
-- Name: COLUMN bundle_type.description; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN bundle_type.description IS 'English language description';


--
-- Name: COLUMN bundle_type.group_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN bundle_type.group_id IS 'Bundle Group';


--
-- Name: COLUMN bundle_type.enclosure_type_code; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN bundle_type.enclosure_type_code IS 'Bundle Enclosure Type (Base Only; Fully Enclosed; Top Open)';


--
-- Name: COLUMN bundle_type.bundle_weight; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN bundle_type.bundle_weight IS 'Weight (kg) of the empty bundle';


--
-- Name: COLUMN bundle_type.bundle_volume; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN bundle_type.bundle_volume IS 'Volume (m3) of the empty bundle';


--
-- Name: COLUMN bundle_type.max_cap_weight; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN bundle_type.max_cap_weight IS 'Maximum capacity (weight) of the bundle, excluding own weight (0=No maximum capacity)';


--
-- Name: COLUMN bundle_type.max_cap_volume; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN bundle_type.max_cap_volume IS 'Maximum capacity (volume) of the bundle, excluding own volume (0=No maximum capacity)';


--
-- Name: COLUMN bundle_type.is_valid_for_land; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN bundle_type.is_valid_for_land IS 'Is a valid bundle type for LAND journeys';


--
-- Name: COLUMN bundle_type.is_valid_for_water; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN bundle_type.is_valid_for_water IS 'Is a valid bundle type for WATER journeys';


--
-- Name: COLUMN bundle_type.is_valid_for_air; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN bundle_type.is_valid_for_air IS 'Is a valid bundle type for AIR journeys';


--
-- Name: COLUMN bundle_type.record_version; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN bundle_type.record_version IS 'Audit field: Lastest (local) change version number of the data';


--
-- Name: COLUMN bundle_type.is_deleted; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN bundle_type.is_deleted IS 'Record has been deleted (Remove from current slection lists)';


--
-- Name: bundle_type_group; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE bundle_type_group (
    id integer NOT NULL,
    description character varying(255) NOT NULL,
    is_qty_allowed smallint NOT NULL,
    record_version bigint NOT NULL,
    is_deleted smallint NOT NULL
);


ALTER TABLE public.bundle_type_group OWNER TO postgres;

--
-- Name: COLUMN bundle_type_group.id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN bundle_type_group.id IS 'Internally generated unique identifier';


--
-- Name: COLUMN bundle_type_group.description; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN bundle_type_group.description IS 'English language description';


--
-- Name: COLUMN bundle_type_group.is_qty_allowed; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN bundle_type_group.is_qty_allowed IS 'Allow the user to enter bundle quantity for this group - NOT used for "loose" items';


--
-- Name: COLUMN bundle_type_group.record_version; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN bundle_type_group.record_version IS 'Audit field: Lastest (local) change version number of the data';


--
-- Name: COLUMN bundle_type_group.is_deleted; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN bundle_type_group.is_deleted IS 'Record has been deleted (Remove from current slection lists)';


--
-- Name: change_type; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE change_type (
    code character(1) NOT NULL,
    description character varying(255) NOT NULL,
    record_version bigint NOT NULL,
    is_deleted smallint NOT NULL
);


ALTER TABLE public.change_type OWNER TO postgres;

--
-- Name: COLUMN change_type.code; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN change_type.code IS 'Static Change Type Code (S=Status Change; P=Priority Change; C=Comment)';


--
-- Name: COLUMN change_type.description; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN change_type.description IS 'English language description';


--
-- Name: COLUMN change_type.record_version; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN change_type.record_version IS 'Audit field: Lastest (local) change version number of the data';


--
-- Name: COLUMN change_type.is_deleted; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN change_type.is_deleted IS 'Record has been deleted (Remove from current slection lists)';


--
-- Name: contact; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE contact (
    owner_site_id integer NOT NULL,
    id integer NOT NULL,
    project_id integer NOT NULL,
    org_id integer NOT NULL,
    title character varying(40),
    name character varying(255) NOT NULL,
    job_title character varying(255),
    address character varying(255),
    country_id integer,
    email1 character varying(100),
    email2 character varying(100),
    email3 character varying(100),
    phone1 character varying(40),
    phone2 character varying(40),
    phone3 character varying(40),
    record_version bigint NOT NULL,
    is_deleted smallint NOT NULL
);


ALTER TABLE public.contact OWNER TO postgres;

--
-- Name: COLUMN contact.owner_site_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN contact.owner_site_id IS 'Site that created/owns the record';


--
-- Name: COLUMN contact.id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN contact.id IS 'Internally generated identifier, unique within Owner_Site_ID';


--
-- Name: COLUMN contact.project_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN contact.project_id IS 'Contact is active for which project';


--
-- Name: COLUMN contact.org_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN contact.org_id IS 'Contact belongs to which organisation';


--
-- Name: COLUMN contact.title; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN contact.title IS 'Contact"s title';


--
-- Name: COLUMN contact.name; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN contact.name IS 'Contact"s name';


--
-- Name: COLUMN contact.job_title; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN contact.job_title IS 'Contact"s position';


--
-- Name: COLUMN contact.address; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN contact.address IS 'Contact"s physical address';


--
-- Name: COLUMN contact.country_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN contact.country_id IS 'Contact"s address country';


--
-- Name: COLUMN contact.email1; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN contact.email1 IS 'Contact"s email address (used when confirming dispatch/delivery)';


--
-- Name: COLUMN contact.email2; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN contact.email2 IS 'Contact"s email address (used when confirming dispatch/delivery)';


--
-- Name: COLUMN contact.email3; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN contact.email3 IS 'Contact"s email address (used when confirming dispatch/delivery)';


--
-- Name: COLUMN contact.phone1; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN contact.phone1 IS 'Contact"s phone number';


--
-- Name: COLUMN contact.phone2; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN contact.phone2 IS 'Contact"s phone number';


--
-- Name: COLUMN contact.phone3; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN contact.phone3 IS 'Contact"s phone number';


--
-- Name: COLUMN contact.record_version; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN contact.record_version IS 'Audit field: Lastest (local) change version number of the data';


--
-- Name: COLUMN contact.is_deleted; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN contact.is_deleted IS 'Record has been deleted (Remove from current slection lists)';


--
-- Name: country; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE country (
    id integer NOT NULL,
    subregion_id integer NOT NULL,
    name character varying(40) NOT NULL,
    official_name character varying(40) NOT NULL,
    alternate_name character varying(255),
    country_type_code character(1) NOT NULL,
    currency_id integer NOT NULL,
    iso2_idnf character(2) NOT NULL,
    iso3_idnf character(3) NOT NULL,
    isonumb_idnf integer NOT NULL,
    latitude numeric(8,5),
    longitude numeric(8,5),
    timezone numeric(3,1) NOT NULL,
    capital_city character varying(40),
    record_version bigint NOT NULL,
    is_deleted smallint NOT NULL
);


ALTER TABLE public.country OWNER TO postgres;

--
-- Name: COLUMN country.id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN country.id IS 'Internally generated unique identifier';


--
-- Name: COLUMN country.subregion_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN country.subregion_id IS 'Sub-Region';


--
-- Name: COLUMN country.name; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN country.name IS 'Country name, as known to public';


--
-- Name: COLUMN country.official_name; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN country.official_name IS 'Official Name to be printed on reports, invoices, etc';


--
-- Name: COLUMN country.alternate_name; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN country.alternate_name IS 'Alternative Country Name';


--
-- Name: COLUMN country.country_type_code; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN country.country_type_code IS 'Country Type (UN Classification)';


--
-- Name: COLUMN country.currency_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN country.currency_id IS 'Official Currency';


--
-- Name: COLUMN country.iso2_idnf; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN country.iso2_idnf IS '2-Character ISO Code (Internet suffix)';


--
-- Name: COLUMN country.iso3_idnf; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN country.iso3_idnf IS '3-Character ISO Code';


--
-- Name: COLUMN country.isonumb_idnf; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN country.isonumb_idnf IS 'Numeric ISO code';


--
-- Name: COLUMN country.latitude; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN country.latitude IS 'Latitude of country (centre)';


--
-- Name: COLUMN country.longitude; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN country.longitude IS 'Longitude of country (centre)';


--
-- Name: COLUMN country.timezone; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN country.timezone IS 'Timezone of Capital City (disregards daylights saving)';


--
-- Name: COLUMN country.capital_city; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN country.capital_city IS 'Name of capital city';


--
-- Name: COLUMN country.record_version; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN country.record_version IS 'Audit field: Lastest (local) change version number of the data';


--
-- Name: COLUMN country.is_deleted; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN country.is_deleted IS 'Record has been deleted (Remove from current slection lists)';


--
-- Name: country_type; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE country_type (
    code character(1) NOT NULL,
    description character varying(255) NOT NULL,
    record_version bigint NOT NULL,
    is_deleted smallint NOT NULL
);


ALTER TABLE public.country_type OWNER TO postgres;

--
-- Name: COLUMN country_type.code; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN country_type.code IS 'Country Type Code: C, T';


--
-- Name: COLUMN country_type.description; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN country_type.description IS 'English language description';


--
-- Name: COLUMN country_type.record_version; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN country_type.record_version IS 'Audit field: Lastest (local) change version number of the data';


--
-- Name: COLUMN country_type.is_deleted; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN country_type.is_deleted IS 'Record has been deleted (Remove from current slection lists)';


--
-- Name: currency; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE currency (
    id integer NOT NULL,
    iso3_idnf character(3) NOT NULL,
    name character varying(40) NOT NULL,
    decimal_places smallint,
    record_version bigint NOT NULL,
    is_deleted smallint NOT NULL,
    CONSTRAINT ckc_decimal_places_currency CHECK (((decimal_places IS NULL) OR (decimal_places >= 0)))
);


ALTER TABLE public.currency OWNER TO postgres;

--
-- Name: COLUMN currency.id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN currency.id IS 'Internally generated unique identifier';


--
-- Name: COLUMN currency.iso3_idnf; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN currency.iso3_idnf IS '3-character ISO currency code';


--
-- Name: COLUMN currency.name; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN currency.name IS 'Currency Name';


--
-- Name: COLUMN currency.decimal_places; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN currency.decimal_places IS 'Currency Precision (Digits after decimal point)';


--
-- Name: COLUMN currency.record_version; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN currency.record_version IS 'Audit field: Lastest (local) change version number of the data';


--
-- Name: COLUMN currency.is_deleted; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN currency.is_deleted IS 'Record has been deleted (Remove from current slection lists)';


--
-- Name: cust_order; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE cust_order (
    id integer NOT NULL,
    project_id integer NOT NULL,
    request_site_id integer NOT NULL,
    owners_ref character varying(40),
    arrival_type_code character(1) NOT NULL,
    arrival_location_site_id integer,
    arrival_location_id integer,
    arrival_instructions character varying(255),
    arrival_contact character varying(255),
    service_type_code character(1) NOT NULL,
    exit_location_site_id integer,
    exit_location_id integer,
    final_instructions character varying(255),
    storage_duration smallint,
    status_code character(2) NOT NULL,
    sender_site_id integer NOT NULL,
    sender_contact_id integer NOT NULL,
    receiver_site_id integer NOT NULL,
    receiver_contact_id integer NOT NULL,
    invoice_site_id integer,
    invoice_contact_id integer,
    on_behalf_of character varying(255),
    description character varying(255),
    product_id integer,
    est_dispatch_time timestamp without time zone,
    est_deliver_time timestamp without time zone,
    followup_date date,
    shipment_docm_idnf character varying(40),
    created_by_logon character varying(40) NOT NULL,
    created_time timestamp without time zone NOT NULL,
    record_version bigint NOT NULL,
    is_deleted smallint NOT NULL,
    CONSTRAINT ckc_storage_duration_cust_ord CHECK (((storage_duration IS NULL) OR (storage_duration >= 0)))
);


ALTER TABLE public.cust_order OWNER TO postgres;

--
-- Name: COLUMN cust_order.id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN cust_order.id IS '--> Request.Cust_Order_ID: Internally generated unique identifier';


--
-- Name: COLUMN cust_order.project_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN cust_order.project_id IS '--> Request.Project ID';


--
-- Name: COLUMN cust_order.request_site_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN cust_order.request_site_id IS '--> Request.Request_Site_ID: Start site';


--
-- Name: COLUMN cust_order.owners_ref; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN cust_order.owners_ref IS 'Owner''s Reference';


--
-- Name: COLUMN cust_order.arrival_type_code; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN cust_order.arrival_type_code IS 'Identifies the way in which the cargo will enter the tracking system (D=Direct Delivery by Client; P=Pickup from Client Site; X=Transshipment; I=Internal)';


--
-- Name: COLUMN cust_order.arrival_location_site_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN cust_order.arrival_location_site_id IS 'Point where goods will initially enter the system';


--
-- Name: COLUMN cust_order.arrival_location_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN cust_order.arrival_location_id IS 'Point where goods will initially enter the system';


--
-- Name: COLUMN cust_order.arrival_instructions; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN cust_order.arrival_instructions IS 'Additional instructions for the arrival of goods';


--
-- Name: COLUMN cust_order.arrival_contact; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN cust_order.arrival_contact IS 'Additional contact details (eg. name & tel. of supplier)';


--
-- Name: COLUMN cust_order.service_type_code; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN cust_order.service_type_code IS '--> Request.Type_Code: Static Request-Type Code';


--
-- Name: COLUMN cust_order.exit_location_site_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN cust_order.exit_location_site_id IS 'Destination Point';


--
-- Name: COLUMN cust_order.exit_location_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN cust_order.exit_location_id IS 'Destination Point';


--
-- Name: COLUMN cust_order.final_instructions; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN cust_order.final_instructions IS 'Addtional handling instruictions for the final delivery (if appropriate) of the goods';


--
-- Name: COLUMN cust_order.storage_duration; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN cust_order.storage_duration IS 'Length of time goods will be stored in the warehouse';


--
-- Name: COLUMN cust_order.status_code; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN cust_order.status_code IS '<Field is *NOT* copied to Request> Current request Status';


--
-- Name: COLUMN cust_order.sender_site_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN cust_order.sender_site_id IS '--> Request.Sender_Site_ID: Site owning the Sender''s Contact ID';


--
-- Name: COLUMN cust_order.sender_contact_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN cust_order.sender_contact_id IS '--> Request.Sender_Contact_ID: Sender Organisation Contact (within Sender_Site_ID)';


--
-- Name: COLUMN cust_order.receiver_site_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN cust_order.receiver_site_id IS '--> Request.Receiver_Site_ID: Site owning the Receiver''s Contact ID';


--
-- Name: COLUMN cust_order.receiver_contact_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN cust_order.receiver_contact_id IS '--> Request.Receiver_Contact_ID: Receiving Organisation Contact (within Receiver_Site_ID)';


--
-- Name: COLUMN cust_order.invoice_site_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN cust_order.invoice_site_id IS '--> Request.Invoice_Site_ID: Site owning the Billable Contact ID';


--
-- Name: COLUMN cust_order.invoice_contact_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN cust_order.invoice_contact_id IS '--> Request.Invoice_Contact_ID: Billable Organisation Contact (within Invoice_Site_ID)';


--
-- Name: COLUMN cust_order.on_behalf_of; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN cust_order.on_behalf_of IS '--> Request.On_Behalf_Of: Additional information, in the event that the cargo will be fowarded on by the Consignee';


--
-- Name: COLUMN cust_order.description; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN cust_order.description IS '--> Request_History.Remarks: Customer entered description';


--
-- Name: COLUMN cust_order.product_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN cust_order.product_id IS '--> Request.Product_ID: High-level sector being covered by the CMR (Null if none)';


--
-- Name: COLUMN cust_order.est_dispatch_time; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN cust_order.est_dispatch_time IS '--> Journey.Est_Dispatch_Time: Estimated (Sheduled) Date/Time of Dispatch';


--
-- Name: COLUMN cust_order.est_deliver_time; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN cust_order.est_deliver_time IS '--> Journey.Est_Deliver_Time: Estimated (Sheduled) Date/Time of Delivery';


--
-- Name: COLUMN cust_order.followup_date; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN cust_order.followup_date IS '<Field is *NOT* copied to Request> Set a date for followup (when Cust Order is placed on HOLD)';


--
-- Name: COLUMN cust_order.shipment_docm_idnf; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN cust_order.shipment_docm_idnf IS '--> Shipment.Shipment_Docm_Idnf: Document Number';


--
-- Name: COLUMN cust_order.created_by_logon; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN cust_order.created_by_logon IS '<Field is *NOT* copied to Request> User who created the request';


--
-- Name: COLUMN cust_order.created_time; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN cust_order.created_time IS '<Field is *NOT* copied to Request> Date the request was created';


--
-- Name: COLUMN cust_order.record_version; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN cust_order.record_version IS 'Audit field: Lastest (local) change version number of the data';


--
-- Name: COLUMN cust_order.is_deleted; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN cust_order.is_deleted IS 'Record has been deleted (Remove from current slection lists)';


--
-- Name: cust_order_history; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE cust_order_history (
    commenting_site_id integer NOT NULL,
    cust_order_id integer NOT NULL,
    change_time timestamp without time zone NOT NULL,
    change_type_code character(1) NOT NULL,
    new_status_code character(2),
    remarks character varying(255) NOT NULL,
    created_by_logon character varying(40) NOT NULL,
    record_version bigint NOT NULL,
    is_deleted smallint NOT NULL
);


ALTER TABLE public.cust_order_history OWNER TO postgres;

--
-- Name: COLUMN cust_order_history.commenting_site_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN cust_order_history.commenting_site_id IS 'Site owning this comment';


--
-- Name: COLUMN cust_order_history.cust_order_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN cust_order_history.cust_order_id IS 'Customer order ID';


--
-- Name: COLUMN cust_order_history.change_time; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN cust_order_history.change_time IS 'Date/Time of status change';


--
-- Name: COLUMN cust_order_history.change_type_code; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN cust_order_history.change_type_code IS '(S=Status Change; P=Priority Change; C=Comment)';


--
-- Name: COLUMN cust_order_history.new_status_code; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN cust_order_history.new_status_code IS 'If ChangeTypeCode="S": New request status; Otherwise NULL';


--
-- Name: COLUMN cust_order_history.remarks; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN cust_order_history.remarks IS 'If ChangeTypeCode="S", "C": User comment';


--
-- Name: COLUMN cust_order_history.created_by_logon; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN cust_order_history.created_by_logon IS 'User who created the request';


--
-- Name: COLUMN cust_order_history.record_version; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN cust_order_history.record_version IS 'Audit field: Lastest (local) change version number of the data';


--
-- Name: COLUMN cust_order_history.is_deleted; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN cust_order_history.is_deleted IS 'Record has been deleted (Remove from current slection lists)';


--
-- Name: cust_order_line; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE cust_order_line (
    id integer NOT NULL,
    cust_order_id integer NOT NULL,
    seqno integer NOT NULL,
    kit_type_code character(1) NOT NULL,
    parent_line_id integer,
    ownr_item_desc character varying(255) NOT NULL,
    ownr_item_idnf character varying(40),
    product_id integer,
    is_cold_storage smallint NOT NULL,
    cold_min_temp numeric(4,1),
    cold_max_temp numeric(4,1),
    is_dangerous smallint NOT NULL,
    is_regulated smallint NOT NULL,
    is_fragile smallint NOT NULL,
    handling_instructions character varying(255),
    is_on_packing_list smallint NOT NULL,
    sku_id integer,
    sku_qty smallint,
    ltu_id integer NOT NULL,
    ltu_qty integer NOT NULL,
    ltu_weight numeric(12,6) NOT NULL,
    ltu_volume numeric(12,6) NOT NULL,
    bundle_type_id integer NOT NULL,
    bundle_qty smallint NOT NULL,
    record_version bigint NOT NULL,
    is_deleted smallint NOT NULL,
    CONSTRAINT ckc_bundle_qty_cust_ord CHECK ((bundle_qty >= 0)),
    CONSTRAINT ckc_ltu_qty_cust_ord CHECK ((ltu_qty >= 0)),
    CONSTRAINT ckc_ltu_volume_cust_ord CHECK ((ltu_volume >= (0)::numeric)),
    CONSTRAINT ckc_ltu_weight_cust_ord CHECK ((ltu_weight >= (0)::numeric)),
    CONSTRAINT ckc_sku_qty_cust_ord CHECK (((sku_qty IS NULL) OR (sku_qty >= 0)))
);


ALTER TABLE public.cust_order_line OWNER TO postgres;

--
-- Name: COLUMN cust_order_line.id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN cust_order_line.id IS 'Internally generated unique identifier';


--
-- Name: COLUMN cust_order_line.cust_order_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN cust_order_line.cust_order_id IS 'Project ID';


--
-- Name: COLUMN cust_order_line.seqno; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN cust_order_line.seqno IS 'Sequential Number within CMR';


--
-- Name: COLUMN cust_order_line.kit_type_code; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN cust_order_line.kit_type_code IS 'Kit Type: "N" = Normal (One or more items per single LTU); "S" = Splittable kit / "U" = Unsplittable Kit (multiple LTUs (boxes) make up one kit)';


--
-- Name: COLUMN cust_order_line.parent_line_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN cust_order_line.parent_line_id IS 'ProdSkuType = "S" or "U" only: Link back to the parent item in the multi-box kit';


--
-- Name: COLUMN cust_order_line.ownr_item_desc; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN cust_order_line.ownr_item_desc IS 'Item description as known to the owner (copy master item description if none supplied by customer)';


--
-- Name: COLUMN cust_order_line.ownr_item_idnf; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN cust_order_line.ownr_item_idnf IS 'Item code as known to the owner';


--
-- Name: COLUMN cust_order_line.product_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN cust_order_line.product_id IS 'Product Identifier';


--
-- Name: COLUMN cust_order_line.is_cold_storage; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN cust_order_line.is_cold_storage IS 'Does the item requires cold storage?';


--
-- Name: COLUMN cust_order_line.cold_min_temp; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN cust_order_line.cold_min_temp IS 'Cold storage minimum temperature (NULL indicates no minimum)';


--
-- Name: COLUMN cust_order_line.cold_max_temp; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN cust_order_line.cold_max_temp IS 'Cold storage maximum temperature (NULL indicates no maximum (only when IsColdStorage = "N"))';


--
-- Name: COLUMN cust_order_line.is_dangerous; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN cust_order_line.is_dangerous IS 'Does the item require dangerous goods documentation?';


--
-- Name: COLUMN cust_order_line.is_regulated; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN cust_order_line.is_regulated IS 'Requires other special documentation (psychotropic drugs, etc)';


--
-- Name: COLUMN cust_order_line.is_fragile; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN cust_order_line.is_fragile IS 'Requires special handling';


--
-- Name: COLUMN cust_order_line.handling_instructions; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN cust_order_line.handling_instructions IS 'Handling instructions - made available whenever Is_Dangerous, Is_Regulated or Is_Fragile is set to TRUE';


--
-- Name: COLUMN cust_order_line.is_on_packing_list; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN cust_order_line.is_on_packing_list IS 'Determines whether Handling instructions are to be printed on the packing list or not';


--
-- Name: COLUMN cust_order_line.sku_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN cust_order_line.sku_id IS 'Kit_Type = "N" only: Accounting Unit Quantity per LTU';


--
-- Name: COLUMN cust_order_line.sku_qty; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN cust_order_line.sku_qty IS 'Kit_Type = "N" only: Accounting Unit Quantity per LTU';


--
-- Name: COLUMN cust_order_line.ltu_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN cust_order_line.ltu_id IS 'Selected Unit of Measurement';


--
-- Name: COLUMN cust_order_line.ltu_qty; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN cust_order_line.ltu_qty IS 'Quantity of goods (at Lowest Transportable Unit) being transported';


--
-- Name: COLUMN cust_order_line.ltu_weight; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN cust_order_line.ltu_weight IS 'Weight (kg) of each individual item (Lowest Transportable Unit)';


--
-- Name: COLUMN cust_order_line.ltu_volume; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN cust_order_line.ltu_volume IS 'Volume (m3) of each individual item (Lowest Transportable Unit)';


--
-- Name: COLUMN cust_order_line.bundle_type_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN cust_order_line.bundle_type_id IS 'Container Type';


--
-- Name: COLUMN cust_order_line.bundle_qty; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN cust_order_line.bundle_qty IS 'Number of containers';


--
-- Name: COLUMN cust_order_line.record_version; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN cust_order_line.record_version IS 'Audit field: Lastest (local) change version number of the data';


--
-- Name: COLUMN cust_order_line.is_deleted; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN cust_order_line.is_deleted IS 'Record has been deleted (Remove from current slection lists)';


--
-- Name: dispatch_priority; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE dispatch_priority (
    id integer NOT NULL,
    description character varying(255) NOT NULL,
    seqno integer NOT NULL,
    is_default smallint NOT NULL,
    name character varying(40) NOT NULL,
    record_version bigint NOT NULL,
    is_deleted smallint NOT NULL
);


ALTER TABLE public.dispatch_priority OWNER TO postgres;

--
-- Name: COLUMN dispatch_priority.id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN dispatch_priority.id IS 'Internally generated unique identifier';


--
-- Name: COLUMN dispatch_priority.description; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN dispatch_priority.description IS 'English language description';


--
-- Name: COLUMN dispatch_priority.seqno; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN dispatch_priority.seqno IS 'Priority Sort Sequency Number';


--
-- Name: COLUMN dispatch_priority.is_default; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN dispatch_priority.is_default IS 'Is this the default selection';


--
-- Name: COLUMN dispatch_priority.name; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN dispatch_priority.name IS 'Short name of the priority';


--
-- Name: COLUMN dispatch_priority.record_version; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN dispatch_priority.record_version IS 'Audit field: Lastest (local) change version number of the data';


--
-- Name: COLUMN dispatch_priority.is_deleted; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN dispatch_priority.is_deleted IS 'Record has been deleted (Remove from current slection lists)';


--
-- Name: distribution_type; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE distribution_type (
    id integer NOT NULL,
    description character varying(255) NOT NULL,
    record_version bigint NOT NULL,
    is_deleted smallint NOT NULL
);


ALTER TABLE public.distribution_type OWNER TO postgres;

--
-- Name: COLUMN distribution_type.id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN distribution_type.id IS 'Site Distribution Type Code';


--
-- Name: COLUMN distribution_type.description; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN distribution_type.description IS 'English language description';


--
-- Name: COLUMN distribution_type.record_version; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN distribution_type.record_version IS 'Audit field: Lastest (local) change version number of the data';


--
-- Name: COLUMN distribution_type.is_deleted; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN distribution_type.is_deleted IS 'Record has been deleted (Remove from current slection lists)';


--
-- Name: enclosure_type; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE enclosure_type (
    code character(2) NOT NULL,
    description character varying(255) NOT NULL,
    record_version bigint NOT NULL,
    is_deleted smallint NOT NULL
);


ALTER TABLE public.enclosure_type OWNER TO postgres;

--
-- Name: COLUMN enclosure_type.code; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN enclosure_type.code IS 'Enclosure Type Code';


--
-- Name: COLUMN enclosure_type.description; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN enclosure_type.description IS 'English language description';


--
-- Name: COLUMN enclosure_type.record_version; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN enclosure_type.record_version IS 'Audit field: Lastest (local) change version number of the data';


--
-- Name: COLUMN enclosure_type.is_deleted; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN enclosure_type.is_deleted IS 'Record has been deleted (Remove from current slection lists)';


--
-- Name: exchange_rate; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE exchange_rate (
    currency_id integer NOT NULL,
    take_effect_date date NOT NULL,
    exch_rate_usd numeric(10,4),
    record_version bigint NOT NULL,
    is_deleted smallint NOT NULL
);


ALTER TABLE public.exchange_rate OWNER TO postgres;

--
-- Name: COLUMN exchange_rate.take_effect_date; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN exchange_rate.take_effect_date IS 'Effective Date';


--
-- Name: COLUMN exchange_rate.exch_rate_usd; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN exchange_rate.exch_rate_usd IS 'Exchange rate against USD1 (accurate to 4 decimal places)';


--
-- Name: COLUMN exchange_rate.record_version; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN exchange_rate.record_version IS 'Audit field: Lastest (local) change version number of the data';


--
-- Name: COLUMN exchange_rate.is_deleted; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN exchange_rate.is_deleted IS 'Record has been deleted (Remove from current slection lists)';


--
-- Name: journey; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE journey (
    journey_site_id integer NOT NULL,
    id integer NOT NULL,
    project_id integer NOT NULL,
    journey_type_code character(1) NOT NULL,
    is_dispatched smallint NOT NULL,
    est_dispatch_time timestamp without time zone NOT NULL,
    actual_dispatch_time timestamp without time zone,
    transport_company character varying(40),
    vehicle_category_id integer,
    vehicle_type character varying(40),
    vehicle_idnf character varying(40),
    driver_name character varying(255),
    driver_idnf character varying(255),
    driver_contact character varying(255),
    load_port_address character varying(255),
    remarks_journey character varying(255),
    record_version bigint NOT NULL,
    is_deleted smallint NOT NULL
);


ALTER TABLE public.journey OWNER TO postgres;

--
-- Name: COLUMN journey.journey_site_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN journey.journey_site_id IS 'Site that owns the record - normally the same as Origin_Site_ID, except where goods originat from the special CUST Site';


--
-- Name: COLUMN journey.id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN journey.id IS 'Internally generated unique identifier';


--
-- Name: COLUMN journey.project_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN journey.project_id IS 'Project - Included in this table, even though it can be derived via Movement & Request), as when a Journey is initially defined, it has no Movement and no Request records. When multiple projects use the same site, this field is needed to separate unused Journey records by project';


--
-- Name: COLUMN journey.journey_type_code; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN journey.journey_type_code IS 'I=Initial (Record customer promise); T=Transfer (Physical movement of cargo); R=Release (Cargo surrendered to customer without a movement); A=Adjustment (Change to stock levels within a site)';


--
-- Name: COLUMN journey.is_dispatched; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN journey.is_dispatched IS 'FALSE = Journey still in planning stages; TRUE = Journey has been dispatched';


--
-- Name: COLUMN journey.est_dispatch_time; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN journey.est_dispatch_time IS 'Estimated (Sheduled) Date/Time of Dispatch';


--
-- Name: COLUMN journey.actual_dispatch_time; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN journey.actual_dispatch_time IS 'Actual Date/Time of Dispatch';


--
-- Name: COLUMN journey.transport_company; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN journey.transport_company IS 'Name of the company providing the vehicle';


--
-- Name: COLUMN journey.vehicle_category_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN journey.vehicle_category_id IS 'Vehicle Category';


--
-- Name: COLUMN journey.vehicle_type; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN journey.vehicle_type IS 'Make/model of the vehicle';


--
-- Name: COLUMN journey.vehicle_idnf; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN journey.vehicle_idnf IS 'Licence plate/call sign of the vehicle';


--
-- Name: COLUMN journey.driver_name; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN journey.driver_name IS 'Driver"s name';


--
-- Name: COLUMN journey.driver_idnf; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN journey.driver_idnf IS 'Driver"s Identification';


--
-- Name: COLUMN journey.driver_contact; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN journey.driver_contact IS 'Driver"s contact details';


--
-- Name: COLUMN journey.load_port_address; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN journey.load_port_address IS 'Loading Port Name (Free text, optional)';


--
-- Name: COLUMN journey.remarks_journey; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN journey.remarks_journey IS 'Additional remarks about the Journey (pickup sequence, etc)';


--
-- Name: COLUMN journey.record_version; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN journey.record_version IS 'Audit field: Lastest (local) change version number of the data';


--
-- Name: COLUMN journey.is_deleted; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN journey.is_deleted IS 'Record has been deleted (Remove from current slection lists)';


--
-- Name: journey_type; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE journey_type (
    code character(1) NOT NULL,
    description character varying(255) NOT NULL,
    record_version bigint NOT NULL,
    is_deleted smallint NOT NULL
);


ALTER TABLE public.journey_type OWNER TO postgres;

--
-- Name: COLUMN journey_type.code; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN journey_type.code IS 'Journey Type Code';


--
-- Name: COLUMN journey_type.description; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN journey_type.description IS 'English language description';


--
-- Name: COLUMN journey_type.record_version; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN journey_type.record_version IS 'Audit field: Lastest (local) change version number of the data';


--
-- Name: COLUMN journey_type.is_deleted; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN journey_type.is_deleted IS 'Record has been deleted (Remove from current slection lists)';


--
-- Name: kit_type; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE kit_type (
    code character(1) NOT NULL,
    description character varying(255) NOT NULL,
    record_version bigint NOT NULL,
    is_deleted smallint NOT NULL
);


ALTER TABLE public.kit_type OWNER TO postgres;

--
-- Name: COLUMN kit_type.code; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN kit_type.code IS 'Kit Type: "N" = Normal (One or more items per single LTU); "S" / "U" = Splittable / Unsplittable kit (multiple LTUs (boxes) make up one kit)';


--
-- Name: COLUMN kit_type.description; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN kit_type.description IS 'English language description';


--
-- Name: COLUMN kit_type.record_version; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN kit_type.record_version IS 'Audit field: Lastest (local) change version number of the data';


--
-- Name: COLUMN kit_type.is_deleted; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN kit_type.is_deleted IS 'Record has been deleted (Remove from current slection lists)';


--
-- Name: languages; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE languages (
    iso2_idnf character(2) NOT NULL,
    locale_idnf character(6) NOT NULL,
    english_desc character varying(255) NOT NULL,
    original_desc character varying(255) NOT NULL,
    record_version bigint NOT NULL,
    is_deleted smallint NOT NULL
);


ALTER TABLE public.languages OWNER TO postgres;

--
-- Name: COLUMN languages.iso2_idnf; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN languages.iso2_idnf IS 'ISO639-1 language code';


--
-- Name: COLUMN languages.locale_idnf; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN languages.locale_idnf IS 'Java Locale Code';


--
-- Name: COLUMN languages.english_desc; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN languages.english_desc IS 'Language name in English';


--
-- Name: COLUMN languages.original_desc; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN languages.original_desc IS 'Language name in that language';


--
-- Name: COLUMN languages.record_version; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN languages.record_version IS 'Audit field: Lastest (local) change version number of the data';


--
-- Name: COLUMN languages.is_deleted; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN languages.is_deleted IS 'Record has been deleted (Remove from current slection lists)';


--
-- Name: list_action; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE list_action (
    screen_code character(2) NOT NULL,
    list_action_code character(3) NOT NULL,
    description character varying(255) NOT NULL,
    seqno integer NOT NULL,
    is_displayed smallint NOT NULL,
    is_allowed_if_proj_closed smallint NOT NULL,
    record_version bigint NOT NULL,
    is_deleted smallint NOT NULL
);


ALTER TABLE public.list_action OWNER TO postgres;

--
-- Name: COLUMN list_action.screen_code; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN list_action.screen_code IS 'Code of the screen using the action';


--
-- Name: COLUMN list_action.list_action_code; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN list_action.list_action_code IS 'Code of the action';


--
-- Name: COLUMN list_action.description; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN list_action.description IS 'English language description';


--
-- Name: COLUMN list_action.seqno; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN list_action.seqno IS 'Sequence Number within the list. Lowest SeqNo will be the double-click default';


--
-- Name: COLUMN list_action.is_displayed; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN list_action.is_displayed IS 'Is the action to be displayed in the Action list.';


--
-- Name: COLUMN list_action.is_allowed_if_proj_closed; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN list_action.is_allowed_if_proj_closed IS 'Is the viewpoint allowed/valid if the project is closed';


--
-- Name: COLUMN list_action.record_version; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN list_action.record_version IS 'Audit field: Lastest (local) change version number of the data';


--
-- Name: COLUMN list_action.is_deleted; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN list_action.is_deleted IS 'Record has been deleted (Remove from current slection lists)';


--
-- Name: list_action_roles; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE list_action_roles (
    screen_code character(2) NOT NULL,
    list_action_code character(3) NOT NULL,
    role_code character(3) NOT NULL,
    record_version bigint NOT NULL,
    is_deleted smallint NOT NULL
);


ALTER TABLE public.list_action_roles OWNER TO postgres;

--
-- Name: COLUMN list_action_roles.screen_code; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN list_action_roles.screen_code IS 'Code of the screen using the viewpoint';


--
-- Name: COLUMN list_action_roles.list_action_code; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN list_action_roles.list_action_code IS 'List Action being taken';


--
-- Name: COLUMN list_action_roles.role_code; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN list_action_roles.role_code IS 'Role';


--
-- Name: COLUMN list_action_roles.record_version; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN list_action_roles.record_version IS 'Audit field: Lastest (local) change version number of the data';


--
-- Name: COLUMN list_action_roles.is_deleted; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN list_action_roles.is_deleted IS 'Record has been deleted (Remove from current slection lists)';


--
-- Name: list_action_viewpoints; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE list_action_viewpoints (
    screen_code character(2) NOT NULL,
    viewpoint_code character(3) NOT NULL,
    list_action_code character(3) NOT NULL,
    record_version bigint NOT NULL,
    is_deleted smallint NOT NULL
);


ALTER TABLE public.list_action_viewpoints OWNER TO postgres;

--
-- Name: COLUMN list_action_viewpoints.screen_code; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN list_action_viewpoints.screen_code IS 'Code of the screen using the viewpoint';


--
-- Name: COLUMN list_action_viewpoints.viewpoint_code; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN list_action_viewpoints.viewpoint_code IS 'Viewpoint';


--
-- Name: COLUMN list_action_viewpoints.list_action_code; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN list_action_viewpoints.list_action_code IS 'List Action being taken';


--
-- Name: COLUMN list_action_viewpoints.record_version; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN list_action_viewpoints.record_version IS 'Audit field: Lastest (local) change version number of the data';


--
-- Name: COLUMN list_action_viewpoints.is_deleted; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN list_action_viewpoints.is_deleted IS 'Record has been deleted (Remove from current slection lists)';


--
-- Name: location; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE location (
    location_site_id integer NOT NULL,
    id integer NOT NULL,
    location_type_code character(1) NOT NULL,
    project_id integer NOT NULL,
    parent_site_id integer NOT NULL,
    name character varying(40) NOT NULL,
    latitude numeric(8,5),
    longitude numeric(8,5),
    address1 character varying(255),
    address2 character varying(255),
    country_id integer NOT NULL,
    email character varying(100),
    phone character varying(40),
    fax character varying(40),
    warehouse_cap_area numeric(9,3),
    warehouse_cap_volume numeric(12,6),
    warehouse_cap_weight numeric(12,6),
    remarks character varying(255),
    port_iso_code character(4),
    port_type_id integer,
    port_consignee_detail text,
    port_is_active smallint NOT NULL,
    record_version bigint NOT NULL,
    is_deleted smallint NOT NULL,
    CONSTRAINT ckc_warehouse_cap_are_location CHECK (((warehouse_cap_area IS NULL) OR (warehouse_cap_area >= (0)::numeric))),
    CONSTRAINT ckc_warehouse_cap_vol_location CHECK (((warehouse_cap_volume IS NULL) OR (warehouse_cap_volume >= (0)::numeric))),
    CONSTRAINT ckc_warehouse_cap_wei_location CHECK (((warehouse_cap_weight IS NULL) OR (warehouse_cap_weight >= (0)::numeric)))
);


ALTER TABLE public.location OWNER TO postgres;

--
-- Name: COLUMN location.location_site_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN location.location_site_id IS 'Site that created/owns the record';


--
-- Name: COLUMN location.id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN location.id IS 'Internally generated identifier. Unique within Owner_Site_ID';


--
-- Name: COLUMN location.location_type_code; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN location.location_type_code IS 'W=Warehouse (destinations limited to this for transfers) - GPS mandatory; P=Port (additional fields available) - GPS Optional; C=Customer Address - GPS optional';


--
-- Name: COLUMN location.project_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN location.project_id IS 'Project ID';


--
-- Name: COLUMN location.parent_site_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN location.parent_site_id IS 'Site closest to, or responsible for the Location (usually but not necessarily the same as the owener Site ID)';


--
-- Name: COLUMN location.name; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN location.name IS 'Location''s short name';


--
-- Name: COLUMN location.latitude; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN location.latitude IS 'Latitude';


--
-- Name: COLUMN location.longitude; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN location.longitude IS 'Longitude';


--
-- Name: COLUMN location.address1; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN location.address1 IS 'Site Address';


--
-- Name: COLUMN location.address2; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN location.address2 IS 'Site Address';


--
-- Name: COLUMN location.country_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN location.country_id IS 'Country ID';


--
-- Name: COLUMN location.email; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN location.email IS 'Primary email of the site';


--
-- Name: COLUMN location.phone; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN location.phone IS 'Site Phone';


--
-- Name: COLUMN location.fax; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN location.fax IS 'Site Fax';


--
-- Name: COLUMN location.warehouse_cap_area; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN location.warehouse_cap_area IS 'Site Capacity - Floor Area (m2)';


--
-- Name: COLUMN location.warehouse_cap_volume; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN location.warehouse_cap_volume IS 'Site Capacity - Volume (m3)';


--
-- Name: COLUMN location.warehouse_cap_weight; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN location.warehouse_cap_weight IS 'Site Capacity - Weight (kg)';


--
-- Name: COLUMN location.remarks; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN location.remarks IS 'Additional comments';


--
-- Name: COLUMN location.port_iso_code; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN location.port_iso_code IS 'Location_Type_Code=''P'': 3-4 Character ISO code for the port';


--
-- Name: COLUMN location.port_type_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN location.port_type_id IS 'Location_Type_Code=''P'': Airport, Seaport, etc';


--
-- Name: COLUMN location.port_consignee_detail; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN location.port_consignee_detail IS 'Location_Type_Code=''P'': Sample consignee text required for all incoming waybills';


--
-- Name: COLUMN location.port_is_active; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN location.port_is_active IS 'Location_Type_Code=''P'': Flag indicating transhipment port is available for clients to select';


--
-- Name: COLUMN location.record_version; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN location.record_version IS 'Audit field: Lastest (local) change version number of the data';


--
-- Name: COLUMN location.is_deleted; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN location.is_deleted IS 'Record has been deleted (Remove from current slection lists)';


--
-- Name: location_type; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE location_type (
    code character(1) NOT NULL,
    description character varying(255) NOT NULL,
    record_version bigint NOT NULL,
    is_deleted smallint NOT NULL
);


ALTER TABLE public.location_type OWNER TO postgres;

--
-- Name: COLUMN location_type.code; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN location_type.code IS 'Location Type Code: W, P, C';


--
-- Name: COLUMN location_type.description; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN location_type.description IS 'English language description';


--
-- Name: COLUMN location_type.record_version; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN location_type.record_version IS 'Audit field: Lastest (local) change version number of the data';


--
-- Name: COLUMN location_type.is_deleted; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN location_type.is_deleted IS 'Record has been deleted (Remove from current slection lists)';


--
-- Name: movement; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE movement (
    move_site_id integer NOT NULL,
    id integer NOT NULL,
    affected_location_site_id integer NOT NULL,
    affected_location_id integer NOT NULL,
    shipment_site_id integer NOT NULL,
    shipment_id integer NOT NULL,
    bundle_site_id integer NOT NULL,
    bundle_id integer NOT NULL,
    request_site_id integer NOT NULL,
    request_line_id integer NOT NULL,
    movement_ledger_code character(1) NOT NULL,
    ltu_qty integer NOT NULL,
    ltu_weight numeric(12,6) NOT NULL,
    ltu_volume numeric(12,6) NOT NULL,
    record_version bigint NOT NULL,
    is_deleted smallint NOT NULL,
    CONSTRAINT ckc_ltu_volume_movement CHECK ((ltu_volume >= (0)::numeric)),
    CONSTRAINT ckc_ltu_weight_movement CHECK ((ltu_weight >= (0)::numeric))
);


ALTER TABLE public.movement OWNER TO postgres;

--
-- Name: COLUMN movement.move_site_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN movement.move_site_id IS 'Site that created and owns the movement record';


--
-- Name: COLUMN movement.id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN movement.id IS 'Internally generated identifier, Unique within Move_Site_ID';


--
-- Name: COLUMN movement.affected_location_site_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN movement.affected_location_site_id IS 'Site whose stock will be affected by this record';


--
-- Name: COLUMN movement.affected_location_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN movement.affected_location_id IS 'Location whose stock will be affected by this record';


--
-- Name: COLUMN movement.shipment_site_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN movement.shipment_site_id IS 'Site that owns the Journey and Shipment record, and normally (except for goods arriving from CUST) the site at the origin of the movement';


--
-- Name: COLUMN movement.shipment_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN movement.shipment_id IS 'Journey identifier';


--
-- Name: COLUMN movement.bundle_site_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN movement.bundle_site_id IS 'Owning site of the bundle record (may be the same or different between ''O'' and ''D'' records, depending whether the bundle config in the w/house is the same as the bundle config sent)';


--
-- Name: COLUMN movement.bundle_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN movement.bundle_id IS 'Bundle ID, unique within Bundle_Site_ID';


--
-- Name: COLUMN movement.request_site_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN movement.request_site_id IS 'Site that originally raised the request - not necessarily the same as the Site originating the currernt movement';


--
-- Name: COLUMN movement.request_line_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN movement.request_line_id IS 'Request Detail';


--
-- Name: COLUMN movement.movement_ledger_code; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN movement.movement_ledger_code IS 'E: Expected (Goods in transit); S (in-Stock); D (Damaged); L (Lost); P: (Planning -not yet dispatched)';


--
-- Name: COLUMN movement.ltu_qty; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN movement.ltu_qty IS 'Quantity of goods (at Lowest Transportable Unit) being transported';


--
-- Name: COLUMN movement.ltu_weight; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN movement.ltu_weight IS 'Weight (kg) of each individual item (Lowest Transportable Unit)';


--
-- Name: COLUMN movement.ltu_volume; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN movement.ltu_volume IS 'Volume (m3) of each individual item (Lowest Transportable Unit)';


--
-- Name: COLUMN movement.record_version; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN movement.record_version IS 'Audit field: Lastest (local) change version number of the data';


--
-- Name: COLUMN movement.is_deleted; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN movement.is_deleted IS 'Record has been deleted (Remove from current slection lists)';


--
-- Name: movement_comment; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE movement_comment (
    move_site_id integer NOT NULL,
    id integer NOT NULL,
    remarks character varying(255),
    record_version bigint NOT NULL,
    is_deleted smallint NOT NULL
);


ALTER TABLE public.movement_comment OWNER TO postgres;

--
-- Name: COLUMN movement_comment.move_site_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN movement_comment.move_site_id IS 'Site originating this cargo movement';


--
-- Name: COLUMN movement_comment.id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN movement_comment.id IS 'Internally generated unique identifier';


--
-- Name: COLUMN movement_comment.remarks; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN movement_comment.remarks IS 'Comments around a movement. Normally optional, this becomes mandatory when moving goods to the special Site ID "DAMAGED';


--
-- Name: COLUMN movement_comment.record_version; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN movement_comment.record_version IS 'Audit field: Lastest (local) change version number of the data';


--
-- Name: COLUMN movement_comment.is_deleted; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN movement_comment.is_deleted IS 'Record has been deleted (Remove from current slection lists)';


--
-- Name: org; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE org (
    id integer NOT NULL,
    name character varying(100) NOT NULL,
    abrv character varying(40),
    org_type_code character(3) NOT NULL,
    is_intl smallint NOT NULL,
    is_donor smallint NOT NULL,
    country_id integer,
    website character varying(255),
    wings_idnf character varying(40),
    compas_idnf character varying(40),
    dacota_idnf integer,
    record_version bigint NOT NULL,
    is_deleted smallint NOT NULL
);


ALTER TABLE public.org OWNER TO postgres;

--
-- Name: COLUMN org.id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN org.id IS 'Internally generated unique identifier';


--
-- Name: COLUMN org.name; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN org.name IS 'Organisation"s full name';


--
-- Name: COLUMN org.abrv; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN org.abrv IS 'Abbreviation';


--
-- Name: COLUMN org.org_type_code; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN org.org_type_code IS 'Organisation type code';


--
-- Name: COLUMN org.is_intl; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN org.is_intl IS 'Non-country specific organisation? If "Yes", Country field is unavailable and set to NULL. If "No", Country field is mandatory';


--
-- Name: COLUMN org.is_donor; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN org.is_donor IS 'Is this organisation a donor?';


--
-- Name: COLUMN org.country_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN org.country_id IS 'Country where this organisation operates';


--
-- Name: COLUMN org.website; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN org.website IS 'Organisation"s Webpage address';


--
-- Name: COLUMN org.wings_idnf; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN org.wings_idnf IS 'Organisation Identifier as known to WINGS';


--
-- Name: COLUMN org.compas_idnf; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN org.compas_idnf IS 'Organisation Identifier as known to COMPAS';


--
-- Name: COLUMN org.dacota_idnf; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN org.dacota_idnf IS 'Organisation Identifier as known to DACOTA';


--
-- Name: COLUMN org.record_version; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN org.record_version IS 'Audit field: Lastest (local) change version number of the data';


--
-- Name: COLUMN org.is_deleted; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN org.is_deleted IS 'Record has been deleted (Remove from current slection lists)';


--
-- Name: org_address; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE org_address (
    id integer NOT NULL,
    org_id integer NOT NULL,
    address_line1 character varying(255) NOT NULL,
    address_line2 character varying(255),
    address_line3 character varying(255),
    address_postcode character varying(255),
    address_country_id integer NOT NULL,
    is_billing_address smallint NOT NULL,
    is_mailing_address smallint NOT NULL,
    is_default smallint NOT NULL,
    is_valid smallint NOT NULL,
    record_version bigint NOT NULL,
    is_deleted smallint NOT NULL
);


ALTER TABLE public.org_address OWNER TO postgres;

--
-- Name: COLUMN org_address.id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN org_address.id IS 'Internally generated unique identifier';


--
-- Name: COLUMN org_address.org_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN org_address.org_id IS 'Unique Organisation Identifier';


--
-- Name: COLUMN org_address.address_line1; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN org_address.address_line1 IS 'Mailing Address - Line 1';


--
-- Name: COLUMN org_address.address_line2; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN org_address.address_line2 IS 'Mailing Address - Line 2';


--
-- Name: COLUMN org_address.address_line3; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN org_address.address_line3 IS 'Mailing Address - Line 3';


--
-- Name: COLUMN org_address.address_postcode; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN org_address.address_postcode IS 'Mailing Address - Postcode';


--
-- Name: COLUMN org_address.address_country_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN org_address.address_country_id IS 'Mailing Address - Country';


--
-- Name: COLUMN org_address.is_billing_address; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN org_address.is_billing_address IS 'Is this a billing address?';


--
-- Name: COLUMN org_address.is_mailing_address; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN org_address.is_mailing_address IS 'Is this a mailing address?';


--
-- Name: COLUMN org_address.is_default; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN org_address.is_default IS 'Is the default address for the organisation?';


--
-- Name: COLUMN org_address.is_valid; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN org_address.is_valid IS 'Is the address still valid?';


--
-- Name: COLUMN org_address.record_version; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN org_address.record_version IS 'Audit field: Lastest (local) change version number of the data';


--
-- Name: COLUMN org_address.is_deleted; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN org_address.is_deleted IS 'Record has been deleted (Remove from current slection lists)';


--
-- Name: org_type; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE org_type (
    code character(3) NOT NULL,
    description character varying(255) NOT NULL,
    is_system smallint NOT NULL,
    record_version bigint NOT NULL,
    is_deleted smallint NOT NULL
);


ALTER TABLE public.org_type OWNER TO postgres;

--
-- Name: COLUMN org_type.code; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN org_type.code IS 'Organisation type code, as extracted from COMPAS';


--
-- Name: COLUMN org_type.description; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN org_type.description IS 'English language description';


--
-- Name: COLUMN org_type.is_system; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN org_type.is_system IS 'Identifies special rows needed for system purposes';


--
-- Name: COLUMN org_type.record_version; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN org_type.record_version IS 'Audit field: Lastest (local) change version number of the data';


--
-- Name: COLUMN org_type.is_deleted; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN org_type.is_deleted IS 'Record has been deleted (Remove from current slection lists)';


--
-- Name: port_type; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE port_type (
    id integer NOT NULL,
    description character varying(255) NOT NULL,
    record_version bigint NOT NULL,
    is_deleted smallint NOT NULL
);


ALTER TABLE public.port_type OWNER TO postgres;

--
-- Name: COLUMN port_type.id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN port_type.id IS 'Site Port Type Code';


--
-- Name: COLUMN port_type.description; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN port_type.description IS 'English language description';


--
-- Name: COLUMN port_type.record_version; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN port_type.record_version IS 'Audit field: Lastest (local) change version number of the data';


--
-- Name: COLUMN port_type.is_deleted; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN port_type.is_deleted IS 'Record has been deleted (Remove from current slection lists)';


--
-- Name: product; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE product (
    id integer NOT NULL,
    product_idnf character(18) NOT NULL,
    description character varying(255) NOT NULL,
    level integer,
    parent_id integer,
    dflt_ltu_id integer,
    dflt_sku_id integer,
    est_weight_volume_ratio numeric(12,6),
    is_cold_storage smallint NOT NULL,
    cold_min_temp numeric(4,1),
    cold_max_temp numeric(4,1),
    is_dangerous smallint NOT NULL,
    is_regulated smallint NOT NULL,
    is_fragile smallint NOT NULL,
    record_version bigint NOT NULL,
    is_deleted smallint NOT NULL,
    CONSTRAINT ckc_est_weight_volume_product CHECK (((est_weight_volume_ratio IS NULL) OR (est_weight_volume_ratio >= (0)::numeric)))
);


ALTER TABLE public.product OWNER TO postgres;

--
-- Name: COLUMN product.id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN product.id IS 'Internally generated unique identifier';


--
-- Name: COLUMN product.product_idnf; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN product.product_idnf IS 'Product Code';


--
-- Name: COLUMN product.description; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN product.description IS 'English language description';


--
-- Name: COLUMN product.level; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN product.level IS 'Product level within the hierarchy';


--
-- Name: COLUMN product.parent_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN product.parent_id IS 'Internally generated unique identifier';


--
-- Name: COLUMN product.dflt_ltu_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN product.dflt_ltu_id IS 'Default Lowest Transportable Unit Type';


--
-- Name: COLUMN product.dflt_sku_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN product.dflt_sku_id IS 'Default Accounting Unit Type';


--
-- Name: COLUMN product.est_weight_volume_ratio; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN product.est_weight_volume_ratio IS 'Stores the approximate weight-to-volume ratio for the product category';


--
-- Name: COLUMN product.is_cold_storage; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN product.is_cold_storage IS 'Requires refirdgeration?';


--
-- Name: COLUMN product.cold_min_temp; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN product.cold_min_temp IS 'Cold storage minimum temperature (NULL indicates no minimum)';


--
-- Name: COLUMN product.cold_max_temp; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN product.cold_max_temp IS 'Cold storage maximum temperature (NULL indicates no maximum (only when IsColdStorage = "N"))';


--
-- Name: COLUMN product.is_dangerous; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN product.is_dangerous IS 'Requires Dangerous Goods documentation (flammable liquids, etc)';


--
-- Name: COLUMN product.is_regulated; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN product.is_regulated IS 'Requires other special documentation (psychotropic drugs, etc)';


--
-- Name: COLUMN product.is_fragile; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN product.is_fragile IS 'Requires special handling';


--
-- Name: COLUMN product.record_version; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN product.record_version IS 'Audit field: Lastest (local) change version number of the data';


--
-- Name: COLUMN product.is_deleted; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN product.is_deleted IS 'Record has been deleted (Remove from current slection lists)';


--
-- Name: product_ltu; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE product_ltu (
    id integer NOT NULL,
    description character varying(255) NOT NULL,
    record_version bigint NOT NULL,
    is_deleted smallint NOT NULL
);


ALTER TABLE public.product_ltu OWNER TO postgres;

--
-- Name: COLUMN product_ltu.id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN product_ltu.id IS 'Internally generated unique identifier';


--
-- Name: COLUMN product_ltu.description; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN product_ltu.description IS 'English language description';


--
-- Name: COLUMN product_ltu.record_version; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN product_ltu.record_version IS 'Audit field: Lastest (local) change version number of the data';


--
-- Name: COLUMN product_ltu.is_deleted; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN product_ltu.is_deleted IS 'Record has been deleted (Remove from current slection lists)';


--
-- Name: product_sku; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE product_sku (
    id integer NOT NULL,
    description character varying(255) NOT NULL,
    record_version bigint NOT NULL,
    is_deleted smallint NOT NULL
);


ALTER TABLE public.product_sku OWNER TO postgres;

--
-- Name: COLUMN product_sku.id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN product_sku.id IS 'Internally generated unique identifier';


--
-- Name: COLUMN product_sku.description; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN product_sku.description IS 'English language description';


--
-- Name: COLUMN product_sku.record_version; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN product_sku.record_version IS 'Audit field: Lastest (local) change version number of the data';


--
-- Name: COLUMN product_sku.is_deleted; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN product_sku.is_deleted IS 'Record has been deleted (Remove from current slection lists)';


--
-- Name: project; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE project (
    id integer NOT NULL,
    project_idnf character(10) NOT NULL,
    name character varying(40) NOT NULL,
    description character varying(255) NOT NULL,
    org_id integer NOT NULL,
    region_id integer NOT NULL,
    subregion_id integer,
    country_id integer,
    purpose_id integer NOT NULL,
    is_logcluster smallint NOT NULL,
    approval_date date NOT NULL,
    end_date date,
    close_date date,
    storage_duration smallint,
    external_idnf character(15),
    budget_idnf character(15),
    subsidy_pct numeric(6,3) DEFAULT 0 NOT NULL,
    email_address character varying(100),
    web_address character varying(255),
    sop_address character varying(255),
    record_version bigint NOT NULL,
    is_deleted smallint NOT NULL,
    CONSTRAINT ckc_storage_duration_project CHECK (((storage_duration IS NULL) OR (storage_duration >= 0))),
    CONSTRAINT ckc_subsidy_pct_project CHECK (((subsidy_pct >= (0)::numeric) AND (subsidy_pct <= (100)::numeric)))
);


ALTER TABLE public.project OWNER TO postgres;

--
-- Name: COLUMN project.id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN project.id IS 'Internally generated unique identifier';


--
-- Name: COLUMN project.project_idnf; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN project.project_idnf IS 'Internal Project identifier';


--
-- Name: COLUMN project.name; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN project.name IS 'Project name';


--
-- Name: COLUMN project.description; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN project.description IS 'Project detailed description';


--
-- Name: COLUMN project.org_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN project.org_id IS 'Organisation responsible for/running the project';


--
-- Name: COLUMN project.region_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN project.region_id IS 'Main Region of operation';


--
-- Name: COLUMN project.subregion_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN project.subregion_id IS 'Main Sub-Region of operation';


--
-- Name: COLUMN project.country_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN project.country_id IS 'Main country of operation';


--
-- Name: COLUMN project.purpose_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN project.purpose_id IS 'Project type';


--
-- Name: COLUMN project.is_logcluster; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN project.is_logcluster IS 'Is this a Log Cluster project? (Yes will enforce use of product nomenclature in Requests)';


--
-- Name: COLUMN project.approval_date; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN project.approval_date IS 'Date the project was approved (start date) - No cargo movement dates allowed before this date';


--
-- Name: COLUMN project.end_date; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN project.end_date IS 'Date the project operations ended - No cargo movement dates allowed after this date';


--
-- Name: COLUMN project.close_date; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN project.close_date IS 'Date the project was closed';


--
-- Name: COLUMN project.storage_duration; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN project.storage_duration IS 'Maximum length of time goods will be stored in a warehouse (applies to Request and Cust_Order) (0=No Maximum)';


--
-- Name: COLUMN project.external_idnf; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN project.external_idnf IS 'Project Identifier to external systems (Normally SLA projects only)';


--
-- Name: COLUMN project.budget_idnf; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN project.budget_idnf IS 'Project Budget Code (Normally SLA projects only)';


--
-- Name: COLUMN project.subsidy_pct; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN project.subsidy_pct IS 'Percent of total transport cost subsidised by WFP';


--
-- Name: COLUMN project.email_address; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN project.email_address IS 'Project Email (Shown on the bottom of all external communications)';


--
-- Name: COLUMN project.web_address; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN project.web_address IS 'Project Website (Shown on the bottom of all external communications)';


--
-- Name: COLUMN project.sop_address; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN project.sop_address IS 'Project Standard Operating Procedures (Direct Link)';


--
-- Name: COLUMN project.record_version; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN project.record_version IS 'Audit field: Lastest (local) change version number of the data';


--
-- Name: COLUMN project.is_deleted; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN project.is_deleted IS 'Record has been deleted (Remove from current slection lists)';


--
-- Name: project_classify; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE project_classify (
    code character(1) NOT NULL,
    description character varying(255) NOT NULL,
    record_version bigint NOT NULL,
    is_deleted smallint NOT NULL
);


ALTER TABLE public.project_classify OWNER TO postgres;

--
-- Name: COLUMN project_classify.code; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN project_classify.code IS 'Project Class Code: S=Service Level Agreement; O=Special Operation';


--
-- Name: COLUMN project_classify.description; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN project_classify.description IS 'English language description';


--
-- Name: COLUMN project_classify.record_version; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN project_classify.record_version IS 'Audit field: Lastest (local) change version number of the data';


--
-- Name: COLUMN project_classify.is_deleted; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN project_classify.is_deleted IS 'Record has been deleted (Remove from current slection lists)';


--
-- Name: project_purpose; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE project_purpose (
    id integer NOT NULL,
    description character varying(255) NOT NULL,
    project_classify_code character(1) NOT NULL,
    record_version bigint NOT NULL,
    is_deleted smallint NOT NULL
);


ALTER TABLE public.project_purpose OWNER TO postgres;

--
-- Name: COLUMN project_purpose.id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN project_purpose.id IS 'Internally generated unique identifier';


--
-- Name: COLUMN project_purpose.description; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN project_purpose.description IS 'English language description';


--
-- Name: COLUMN project_purpose.project_classify_code; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN project_purpose.project_classify_code IS 'Relates to which Project Classification';


--
-- Name: COLUMN project_purpose.record_version; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN project_purpose.record_version IS 'Audit field: Lastest (local) change version number of the data';


--
-- Name: COLUMN project_purpose.is_deleted; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN project_purpose.is_deleted IS 'Record has been deleted (Remove from current slection lists)';


--
-- Name: project_site; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE project_site (
    project_id integer NOT NULL,
    site_id integer NOT NULL,
    site_prefix character(3),
    record_version bigint NOT NULL,
    is_deleted smallint NOT NULL
);


ALTER TABLE public.project_site OWNER TO postgres;

--
-- Name: COLUMN project_site.project_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN project_site.project_id IS 'Project ID';


--
-- Name: COLUMN project_site.site_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN project_site.site_id IS 'Project is Active for which sites';


--
-- Name: COLUMN project_site.site_prefix; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN project_site.site_prefix IS 'Site Prefix (for orders initiated from this project/site) - Mandatory if ''Is_Entry_Point'' is TRUE';


--
-- Name: COLUMN project_site.record_version; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN project_site.record_version IS 'Audit field: Lastest (local) change version number of the data';


--
-- Name: COLUMN project_site.is_deleted; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN project_site.is_deleted IS 'Record has been deleted (Remove from current slection lists)';


--
-- Name: region; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE region (
    id integer NOT NULL,
    name character varying(40) NOT NULL,
    is_system smallint NOT NULL,
    record_version bigint NOT NULL,
    is_deleted smallint NOT NULL
);


ALTER TABLE public.region OWNER TO postgres;

--
-- Name: COLUMN region.id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN region.id IS 'Internally generated unique identifier';


--
-- Name: COLUMN region.name; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN region.name IS 'Region Name';


--
-- Name: COLUMN region.is_system; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN region.is_system IS 'Identifies special rows needed for system purposes';


--
-- Name: COLUMN region.record_version; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN region.record_version IS 'Audit field: Lastest (local) change version number of the data';


--
-- Name: COLUMN region.is_deleted; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN region.is_deleted IS 'Record has been deleted (Remove from current slection lists)';


--
-- Name: request; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE request (
    request_site_id integer NOT NULL,
    id integer NOT NULL,
    project_id integer NOT NULL,
    seqno integer NOT NULL,
    suffix character(2) NOT NULL,
    owners_ref character varying(40),
    arrival_type_code character(1) NOT NULL,
    arrival_location_site_id integer NOT NULL,
    arrival_location_id integer NOT NULL,
    arrival_instructions character varying(255),
    arrival_contact character varying(255),
    service_type_code character(1) NOT NULL,
    final_instructions character varying(255),
    initial_shipment_id integer NOT NULL,
    storage_duration smallint,
    status_code character(2) NOT NULL,
    sender_site_id integer NOT NULL,
    sender_contact_id integer NOT NULL,
    receiver_site_id integer NOT NULL,
    receiver_contact_id integer NOT NULL,
    invoice_site_id integer,
    invoice_contact_id integer,
    on_behalf_of character varying(255),
    cust_order_id integer,
    product_id integer,
    is_local_purchase smallint NOT NULL,
    identifier_history_desc character varying(255),
    dispatch_priority_id integer NOT NULL,
    request_raised_date date NOT NULL,
    transport_mode_code character(1),
    load_date date,
    dispatch_plan_date date,
    followup_date date,
    created_by_logon character varying(40) NOT NULL,
    created_time timestamp without time zone NOT NULL,
    track_idnf character(10) NOT NULL,
    record_version bigint NOT NULL,
    is_deleted smallint NOT NULL,
    CONSTRAINT ckc_storage_duration_request CHECK (((storage_duration IS NULL) OR (storage_duration >= 0)))
);


ALTER TABLE public.request OWNER TO postgres;

--
-- Name: COLUMN request.request_site_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request.request_site_id IS 'Site raising the request (will determine CMR prefix) - Also the record owner (sync partition on this field)';


--
-- Name: COLUMN request.id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request.id IS 'Internally generated unique identifier';


--
-- Name: COLUMN request.project_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request.project_id IS 'Project';


--
-- Name: COLUMN request.seqno; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request.seqno IS 'Request sequence Number';


--
-- Name: COLUMN request.suffix; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request.suffix IS 'Request suffix';


--
-- Name: COLUMN request.owners_ref; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request.owners_ref IS 'Owner''s Reference';


--
-- Name: COLUMN request.arrival_type_code; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request.arrival_type_code IS 'Identifies the way in which the cargo will enter the tracking system: X=Direct Delivery by Client (Location Type = ''S''); C=Pickup from Client Site (Location Type = ''C''); P=Transshipment (Location Type = ''P''); I=Internal (Location Type = ''S'' or ''W'')';


--
-- Name: COLUMN request.arrival_location_site_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request.arrival_location_site_id IS 'Point where goods initially enter the system';


--
-- Name: COLUMN request.arrival_location_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request.arrival_location_id IS 'Point where goods initially enter the system';


--
-- Name: COLUMN request.arrival_instructions; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request.arrival_instructions IS 'Additional instructions for the arrival of goods';


--
-- Name: COLUMN request.arrival_contact; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request.arrival_contact IS 'Additional contact details (eg. name & tel. of supplier)';


--
-- Name: COLUMN request.service_type_code; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request.service_type_code IS 'Service Type Code';


--
-- Name: COLUMN request.final_instructions; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request.final_instructions IS 'Addtional handling instruictions for the final delivery (if appropriate) of the goods';


--
-- Name: COLUMN request.initial_shipment_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request.initial_shipment_id IS 'Shipment record containing information from CUSTOMER to OPERATION';


--
-- Name: COLUMN request.storage_duration; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request.storage_duration IS 'Length of time goods will be stored in the warehouse';


--
-- Name: COLUMN request.status_code; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request.status_code IS 'Current CMR Status';


--
-- Name: COLUMN request.sender_site_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request.sender_site_id IS 'Site owning the Sender''s Contact ID';


--
-- Name: COLUMN request.sender_contact_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request.sender_contact_id IS 'Sender Organisation Contact (within Sender_Site_ID)';


--
-- Name: COLUMN request.receiver_site_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request.receiver_site_id IS 'Site owning the Receiver''s Contact ID';


--
-- Name: COLUMN request.receiver_contact_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request.receiver_contact_id IS 'Receiving Organisation Contact (within Receiver_Site_ID)';


--
-- Name: COLUMN request.invoice_site_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request.invoice_site_id IS 'Site owning the Billable Contact ID';


--
-- Name: COLUMN request.invoice_contact_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request.invoice_contact_id IS 'Billable Organisation Contact (within Invoice_Site_ID)';


--
-- Name: COLUMN request.on_behalf_of; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request.on_behalf_of IS 'Additional information, in the event that the cargo will be fowarded on by the Consignee';


--
-- Name: COLUMN request.cust_order_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request.cust_order_id IS 'Customer order ID';


--
-- Name: COLUMN request.product_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request.product_id IS 'High-level sector being covered by the CMR (Null if none)';


--
-- Name: COLUMN request.is_local_purchase; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request.is_local_purchase IS 'is a local purchase flag';


--
-- Name: COLUMN request.identifier_history_desc; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request.identifier_history_desc IS 'Arrival_Type_Code=''I'': String of all Requests making up the current request';


--
-- Name: COLUMN request.dispatch_priority_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request.dispatch_priority_id IS 'Current CMR Priority';


--
-- Name: COLUMN request.request_raised_date; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request.request_raised_date IS 'The date the request was initially raised';


--
-- Name: COLUMN request.transport_mode_code; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request.transport_mode_code IS 'Preferred Transport Mode: A=Air, W=Water, L=Land (NULL = No preference)';


--
-- Name: COLUMN request.load_date; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request.load_date IS 'Planned loading date';


--
-- Name: COLUMN request.dispatch_plan_date; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request.dispatch_plan_date IS 'Planned dispatch date';


--
-- Name: COLUMN request.followup_date; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request.followup_date IS 'Set a date for followup (when Request is placed on HOLD)';


--
-- Name: COLUMN request.created_by_logon; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request.created_by_logon IS 'User who created the request';


--
-- Name: COLUMN request.created_time; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request.created_time IS 'Date the request was created';


--
-- Name: COLUMN request.track_idnf; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request.track_idnf IS 'Tracking Identifier - The number the client will enter to track their request online';


--
-- Name: COLUMN request.record_version; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request.record_version IS 'Audit field: Lastest (local) change version number of the data';


--
-- Name: COLUMN request.is_deleted; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request.is_deleted IS 'Record has been deleted (Remove from current slection lists)';


--
-- Name: request_cust_type; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE request_cust_type (
    code character(1) NOT NULL,
    description character varying(255) NOT NULL,
    record_version bigint NOT NULL,
    is_deleted smallint NOT NULL
);


ALTER TABLE public.request_cust_type OWNER TO postgres;

--
-- Name: COLUMN request_cust_type.code; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request_cust_type.code IS 'R = Request; C = Cust_Order';


--
-- Name: COLUMN request_cust_type.description; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request_cust_type.description IS 'English language description';


--
-- Name: COLUMN request_cust_type.record_version; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request_cust_type.record_version IS 'Audit field: Lastest (local) change version number of the data';


--
-- Name: COLUMN request_cust_type.is_deleted; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request_cust_type.is_deleted IS 'Record has been deleted (Remove from current slection lists)';


--
-- Name: request_history; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE request_history (
    commenting_site_id integer NOT NULL,
    request_site_id integer NOT NULL,
    request_id integer NOT NULL,
    change_time timestamp without time zone NOT NULL,
    change_type_code character(1) NOT NULL,
    new_status_code character(2),
    new_priority_id integer,
    remarks character varying(255),
    created_by_logon character varying(40) NOT NULL,
    record_version bigint NOT NULL,
    is_deleted smallint NOT NULL
);


ALTER TABLE public.request_history OWNER TO postgres;

--
-- Name: COLUMN request_history.commenting_site_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request_history.commenting_site_id IS 'Site owning this comment';


--
-- Name: COLUMN request_history.request_site_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request_history.request_site_id IS 'Site owning the Request';


--
-- Name: COLUMN request_history.request_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request_history.request_id IS 'Request ID';


--
-- Name: COLUMN request_history.change_time; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request_history.change_time IS 'Date/Time of status change';


--
-- Name: COLUMN request_history.change_type_code; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request_history.change_type_code IS '(S=Status Change; P=Priority Change; C=Comment)';


--
-- Name: COLUMN request_history.new_status_code; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request_history.new_status_code IS 'If ChangeTypeCode="S": New request status; Otherwise NULL';


--
-- Name: COLUMN request_history.new_priority_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request_history.new_priority_id IS 'If ChangeTypeCode="P": New priority; Otherwise NULL';


--
-- Name: COLUMN request_history.remarks; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request_history.remarks IS 'If ChangeTypeCode="S", "P", "C": User comment';


--
-- Name: COLUMN request_history.created_by_logon; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request_history.created_by_logon IS 'User who created the request';


--
-- Name: COLUMN request_history.record_version; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request_history.record_version IS 'Audit field: Lastest (local) change version number of the data';


--
-- Name: COLUMN request_history.is_deleted; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request_history.is_deleted IS 'Record has been deleted (Remove from current slection lists)';


--
-- Name: request_line; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE request_line (
    request_site_id integer NOT NULL,
    id integer NOT NULL,
    request_id integer NOT NULL,
    seqno integer NOT NULL,
    kit_type_code character(1) NOT NULL,
    parent_line_id integer,
    ownr_item_desc character varying(255) NOT NULL,
    ownr_item_idnf character varying(40),
    product_id integer NOT NULL,
    is_cold_storage smallint NOT NULL,
    cold_min_temp numeric(4,1),
    cold_max_temp numeric(4,1),
    is_dangerous smallint NOT NULL,
    is_regulated smallint NOT NULL,
    is_fragile smallint NOT NULL,
    handling_instructions character varying(255),
    is_on_packing_list smallint NOT NULL,
    sku_id integer,
    sku_qty smallint,
    ltu_id integer NOT NULL,
    ltu_qty integer NOT NULL,
    ltu_weight numeric(12,6) NOT NULL,
    is_operator_weight smallint NOT NULL,
    ltu_volume numeric(12,6) NOT NULL,
    is_operator_volume smallint NOT NULL,
    origin_request_site_id integer,
    origin_line_id integer,
    record_version bigint NOT NULL,
    is_deleted smallint NOT NULL,
    CONSTRAINT ckc_ltu_volume_request_ CHECK ((ltu_volume >= (0)::numeric)),
    CONSTRAINT ckc_ltu_weight_request_ CHECK ((ltu_weight >= (0)::numeric)),
    CONSTRAINT ckc_sku_qty_request_ CHECK (((sku_qty IS NULL) OR (sku_qty >= 0)))
);


ALTER TABLE public.request_line OWNER TO postgres;

--
-- Name: COLUMN request_line.request_site_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request_line.request_site_id IS 'Site raising the request (will determine CMR prefix)';


--
-- Name: COLUMN request_line.id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request_line.id IS 'Internally generated unique identifier';


--
-- Name: COLUMN request_line.request_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request_line.request_id IS 'Request (CMR) ID';


--
-- Name: COLUMN request_line.seqno; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request_line.seqno IS 'Sequential Number within CMR';


--
-- Name: COLUMN request_line.kit_type_code; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request_line.kit_type_code IS 'Kit Type: "N" = Normal (One or more items per single LTU); "S" = Splittable kit / "U" = Unsplittable Kit (multiple LTUs (boxes) make up one kit)';


--
-- Name: COLUMN request_line.parent_line_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request_line.parent_line_id IS 'Parent line (multi-part kits)';


--
-- Name: COLUMN request_line.ownr_item_desc; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request_line.ownr_item_desc IS 'Item description as known to the owner (copy master item description if none supplied by customer)';


--
-- Name: COLUMN request_line.ownr_item_idnf; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request_line.ownr_item_idnf IS 'Item code as known to the owner';


--
-- Name: COLUMN request_line.product_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request_line.product_id IS 'Product Identifier';


--
-- Name: COLUMN request_line.is_cold_storage; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request_line.is_cold_storage IS 'Does the item requires cold storage?';


--
-- Name: COLUMN request_line.cold_min_temp; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request_line.cold_min_temp IS 'Cold storage minimum temperature (NULL indicates no minimum)';


--
-- Name: COLUMN request_line.cold_max_temp; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request_line.cold_max_temp IS 'Cold storage maximum temperature (NULL indicates no maximum (only when IsColdStorage = "N"))';


--
-- Name: COLUMN request_line.is_dangerous; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request_line.is_dangerous IS 'Does the item require dangerous goods documentation?';


--
-- Name: COLUMN request_line.is_regulated; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request_line.is_regulated IS 'Requires other special documentation (psychotropic drugs, etc)';


--
-- Name: COLUMN request_line.is_fragile; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request_line.is_fragile IS 'Requires special handling';


--
-- Name: COLUMN request_line.handling_instructions; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request_line.handling_instructions IS 'Handling instructions - made available whenever Is_Dangerous, Is_Regulated or Is_Fragile is set to TRUE';


--
-- Name: COLUMN request_line.is_on_packing_list; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request_line.is_on_packing_list IS 'Determines whether Handling instructions are to be printed on the packing list or not';


--
-- Name: COLUMN request_line.sku_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request_line.sku_id IS 'ProdSkuType = "N" only: Accounting Unit Identifier';


--
-- Name: COLUMN request_line.sku_qty; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request_line.sku_qty IS 'ProdSkuType = "N" only: Accounting Unit Quantity per LTU';


--
-- Name: COLUMN request_line.ltu_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request_line.ltu_id IS 'Selected Unit of Measurement';


--
-- Name: COLUMN request_line.ltu_qty; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request_line.ltu_qty IS 'Quantity of goods (at Lowest Transportable Unit) being transported';


--
-- Name: COLUMN request_line.ltu_weight; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request_line.ltu_weight IS 'Weight (kg) of each individual item (Lowest Transportable Unit)';


--
-- Name: COLUMN request_line.is_operator_weight; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request_line.is_operator_weight IS 'FALSE=Value on original request; TRUE=Value has been revised by the operator, as considered to be more accurate';


--
-- Name: COLUMN request_line.ltu_volume; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request_line.ltu_volume IS 'Volume (m3) of each individual item (Lowest Transportable Unit)';


--
-- Name: COLUMN request_line.is_operator_volume; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request_line.is_operator_volume IS 'FALSE=Value on original request; TRUE=Value has been revised by the operator, as considered to be more accurate';


--
-- Name: COLUMN request_line.origin_request_site_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request_line.origin_request_site_id IS 'Used when rebuilding/combining requests: Records the source of the combined/rebuilt line';


--
-- Name: COLUMN request_line.origin_line_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request_line.origin_line_id IS 'Used when rebuilding/combining requests: Records the source of the combined/rebuilt line';


--
-- Name: COLUMN request_line.record_version; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request_line.record_version IS 'Audit field: Lastest (local) change version number of the data';


--
-- Name: COLUMN request_line.is_deleted; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request_line.is_deleted IS 'Record has been deleted (Remove from current slection lists)';


--
-- Name: request_line_priority; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE request_line_priority (
    commenting_site_id integer NOT NULL,
    request_site_id integer NOT NULL,
    request_line_id integer NOT NULL,
    line_dispatch_priority_id integer NOT NULL,
    record_version bigint NOT NULL,
    is_deleted smallint NOT NULL
);


ALTER TABLE public.request_line_priority OWNER TO postgres;

--
-- Name: COLUMN request_line_priority.commenting_site_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request_line_priority.commenting_site_id IS 'Site owning this line priority';


--
-- Name: COLUMN request_line_priority.request_site_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request_line_priority.request_site_id IS 'Site owning the request';


--
-- Name: COLUMN request_line_priority.request_line_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request_line_priority.request_line_id IS 'Request line identifier';


--
-- Name: COLUMN request_line_priority.line_dispatch_priority_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request_line_priority.line_dispatch_priority_id IS 'Request line Cargo Priority';


--
-- Name: COLUMN request_line_priority.record_version; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request_line_priority.record_version IS 'Audit field: Lastest (local) change version number of the data';


--
-- Name: COLUMN request_line_priority.is_deleted; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request_line_priority.is_deleted IS 'Record has been deleted (Remove from current slection lists)';


--
-- Name: request_status; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE request_status (
    code character(2) NOT NULL,
    request_cust_type_code character(1) NOT NULL,
    description character varying(255) NOT NULL,
    name character varying(40) NOT NULL,
    next_code character(2),
    seqno integer,
    record_version bigint NOT NULL,
    is_deleted smallint NOT NULL
);


ALTER TABLE public.request_status OWNER TO postgres;

--
-- Name: COLUMN request_status.code; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request_status.code IS 'Static Request Code';


--
-- Name: COLUMN request_status.request_cust_type_code; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request_status.request_cust_type_code IS 'Indicates whether the Request Status applies to a CUST_ORDER or a REQUEST';


--
-- Name: COLUMN request_status.description; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request_status.description IS 'English language description';


--
-- Name: COLUMN request_status.name; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request_status.name IS 'Status short name';


--
-- Name: COLUMN request_status.next_code; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request_status.next_code IS 'Next Logical Status (Null if not applicable)';


--
-- Name: COLUMN request_status.seqno; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request_status.seqno IS 'Status Sequence Number';


--
-- Name: COLUMN request_status.record_version; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request_status.record_version IS 'Audit field: Lastest (local) change version number of the data';


--
-- Name: COLUMN request_status.is_deleted; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN request_status.is_deleted IS 'Record has been deleted (Remove from current slection lists)';


--
-- Name: roles; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE roles (
    code character(3) NOT NULL,
    record_version bigint NOT NULL,
    is_deleted smallint NOT NULL
);


ALTER TABLE public.roles OWNER TO postgres;

--
-- Name: COLUMN roles.code; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN roles.code IS 'Role';


--
-- Name: COLUMN roles.record_version; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN roles.record_version IS 'Audit field: Lastest (local) change version number of the data';


--
-- Name: COLUMN roles.is_deleted; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN roles.is_deleted IS 'Record has been deleted (Remove from current slection lists)';


--
-- Name: screen_code; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE screen_code (
    code character(2) NOT NULL,
    name character varying(64) NOT NULL,
    record_version bigint NOT NULL,
    is_deleted smallint NOT NULL
);


ALTER TABLE public.screen_code OWNER TO postgres;

--
-- Name: COLUMN screen_code.code; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN screen_code.code IS 'Screen using the viewpoint/action';


--
-- Name: COLUMN screen_code.record_version; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN screen_code.record_version IS 'Audit field: Lastest (local) change version number of the data';


--
-- Name: COLUMN screen_code.is_deleted; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN screen_code.is_deleted IS 'Record has been deleted (Remove from current slection lists)';


--
-- Name: service_type; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE service_type (
    code character(1) NOT NULL,
    description character varying(255) NOT NULL,
    record_version bigint NOT NULL,
    is_deleted smallint NOT NULL
);


ALTER TABLE public.service_type OWNER TO postgres;

--
-- Name: COLUMN service_type.code; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN service_type.code IS 'Static Service Type Code';


--
-- Name: COLUMN service_type.description; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN service_type.description IS 'English language description';


--
-- Name: COLUMN service_type.record_version; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN service_type.record_version IS 'Audit field: Lastest (local) change version number of the data';


--
-- Name: COLUMN service_type.is_deleted; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN service_type.is_deleted IS 'Record has been deleted (Remove from current slection lists)';


--
-- Name: shipment; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE shipment (
    shipment_site_id integer NOT NULL,
    id integer NOT NULL,
    journey_id integer NOT NULL,
    seqno integer NOT NULL,
    destination_location_site_id integer NOT NULL,
    destination_location_id integer NOT NULL,
    est_delivery_time timestamp without time zone,
    is_delivery_note_received smallint NOT NULL,
    shipment_docm_idnf character varying(40),
    shipment_docm_numb character varying(40),
    pack_list_date date,
    pack_list_name character varying(40),
    remarks_shipment character varying(255),
    record_version bigint NOT NULL,
    is_deleted smallint NOT NULL
);


ALTER TABLE public.shipment OWNER TO postgres;

--
-- Name: COLUMN shipment.shipment_site_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN shipment.shipment_site_id IS 'Site that owns the record';


--
-- Name: COLUMN shipment.id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN shipment.id IS 'Internally generated unique identifier';


--
-- Name: COLUMN shipment.journey_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN shipment.journey_id IS 'Vehicle Trip Identifier';


--
-- Name: COLUMN shipment.seqno; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN shipment.seqno IS 'Sequence of the journey within a single trip';


--
-- Name: COLUMN shipment.destination_location_site_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN shipment.destination_location_site_id IS 'Destination Point';


--
-- Name: COLUMN shipment.destination_location_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN shipment.destination_location_id IS 'Destination Point';


--
-- Name: COLUMN shipment.est_delivery_time; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN shipment.est_delivery_time IS 'Estimated Date/Time of Delivery';


--
-- Name: COLUMN shipment.is_delivery_note_received; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN shipment.is_delivery_note_received IS 'Delivery Documents have been received back at sending location';


--
-- Name: COLUMN shipment.shipment_docm_idnf; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN shipment.shipment_docm_idnf IS 'Document Number (as typed by the user)';


--
-- Name: COLUMN shipment.shipment_docm_numb; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN shipment.shipment_docm_numb IS 'Document Number (Numeric and semicolon only)';


--
-- Name: COLUMN shipment.pack_list_date; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN shipment.pack_list_date IS 'Packing List date (automatically populate with today"s date)';


--
-- Name: COLUMN shipment.pack_list_name; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN shipment.pack_list_name IS 'Packing List Name/Sequence Number';


--
-- Name: COLUMN shipment.remarks_shipment; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN shipment.remarks_shipment IS 'Remarks pertaining to the particular shipment';


--
-- Name: COLUMN shipment.record_version; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN shipment.record_version IS 'Audit field: Lastest (local) change version number of the data';


--
-- Name: COLUMN shipment.is_deleted; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN shipment.is_deleted IS 'Record has been deleted (Remove from current slection lists)';


--
-- Name: shipment_comment; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE shipment_comment (
    owner_site_id integer NOT NULL,
    shipment_site_id integer NOT NULL,
    shipment_id integer NOT NULL,
    actual_delivery_time timestamp without time zone NOT NULL,
    remarks_delivery character varying(255),
    record_version bigint NOT NULL,
    is_deleted smallint NOT NULL
);


ALTER TABLE public.shipment_comment OWNER TO postgres;

--
-- Name: COLUMN shipment_comment.owner_site_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN shipment_comment.owner_site_id IS 'Destination Site (owns the records in this table)';


--
-- Name: COLUMN shipment_comment.shipment_site_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN shipment_comment.shipment_site_id IS 'Site originating the cargo movement';


--
-- Name: COLUMN shipment_comment.shipment_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN shipment_comment.shipment_id IS 'Journey ID';


--
-- Name: COLUMN shipment_comment.actual_delivery_time; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN shipment_comment.actual_delivery_time IS 'Actual Date/Time of Delivery';


--
-- Name: COLUMN shipment_comment.remarks_delivery; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN shipment_comment.remarks_delivery IS 'Dispatch Additional remarks';


--
-- Name: COLUMN shipment_comment.record_version; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN shipment_comment.record_version IS 'Audit field: Lastest (local) change version number of the data';


--
-- Name: COLUMN shipment_comment.is_deleted; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN shipment_comment.is_deleted IS 'Record has been deleted (Remove from current slection lists)';


--
-- Name: site; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE site (
    id integer NOT NULL,
    site_name character varying(40) NOT NULL,
    rptg_site_id integer,
    org_id integer,
    rptg_ofcr_name character varying(255),
    radio_call_sign character varying(255),
    remarks character varying(255),
    site_dstr_type_id integer NOT NULL,
    site_bldg_type_id integer NOT NULL,
    site_bldg_qty smallint NOT NULL,
    record_version bigint NOT NULL,
    is_deleted smallint NOT NULL,
    CONSTRAINT ckc_site_bldg_qty_site CHECK ((site_bldg_qty >= 0))
);


ALTER TABLE public.site OWNER TO postgres;

--
-- Name: COLUMN site.id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN site.id IS 'Internally generated unique identifier';


--
-- Name: COLUMN site.site_name; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN site.site_name IS 'Site Name';


--
-- Name: COLUMN site.rptg_site_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN site.rptg_site_id IS 'Reporting (parent) Site (if any)';


--
-- Name: COLUMN site.org_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN site.org_id IS 'Organisation in charge at the site (if any)';


--
-- Name: COLUMN site.rptg_ofcr_name; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN site.rptg_ofcr_name IS 'Name of the reporting officer (if any)';


--
-- Name: COLUMN site.radio_call_sign; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN site.radio_call_sign IS 'Radio call sign';


--
-- Name: COLUMN site.remarks; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN site.remarks IS 'Additional comments';


--
-- Name: COLUMN site.site_dstr_type_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN site.site_dstr_type_id IS 'Distribution Type';


--
-- Name: COLUMN site.site_bldg_type_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN site.site_bldg_type_id IS 'Site Building Type Code';


--
-- Name: COLUMN site.site_bldg_qty; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN site.site_bldg_qty IS 'No of structures making up the wahrehouse site';


--
-- Name: COLUMN site.record_version; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN site.record_version IS 'Audit field: Lastest (local) change version number of the data';


--
-- Name: COLUMN site.is_deleted; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN site.is_deleted IS 'Record has been deleted (Remove from current slection lists)';


--
-- Name: site_track_no; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE site_track_no (
    site_id integer NOT NULL,
    site_track_idnf character(18) NOT NULL,
    site_track_date date NOT NULL,
    site_track_cptr_name character varying(40) NOT NULL,
    is_active smallint
);


ALTER TABLE public.site_track_no OWNER TO postgres;

--
-- Name: COLUMN site_track_no.site_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN site_track_no.site_id IS 'Site Identifier';


--
-- Name: COLUMN site_track_no.site_track_idnf; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN site_track_no.site_track_idnf IS 'Tracking Number';


--
-- Name: COLUMN site_track_no.site_track_date; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN site_track_no.site_track_date IS 'Date the Local Instance Clone (and tracking number) was generated';


--
-- Name: COLUMN site_track_no.site_track_cptr_name; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN site_track_no.site_track_cptr_name IS 'Name of the computer receiving the Local Instance Clone';


--
-- Name: COLUMN site_track_no.is_active; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN site_track_no.is_active IS 'True = Indicates the LIC  is active and editable by the user; False indicates initial Sync has not completed; NULL indicates de-registered or Master Instance';


--
-- Name: subregion; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE subregion (
    id integer NOT NULL,
    name character varying(40) NOT NULL,
    region_id integer NOT NULL,
    record_version bigint NOT NULL,
    is_deleted smallint NOT NULL
);


ALTER TABLE public.subregion OWNER TO postgres;

--
-- Name: COLUMN subregion.id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN subregion.id IS 'Internally generated unique identifier';


--
-- Name: COLUMN subregion.name; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN subregion.name IS 'Sub-region Name';


--
-- Name: COLUMN subregion.region_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN subregion.region_id IS 'Parent Region';


--
-- Name: COLUMN subregion.record_version; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN subregion.record_version IS 'Audit field: Lastest (local) change version number of the data';


--
-- Name: COLUMN subregion.is_deleted; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN subregion.is_deleted IS 'Record has been deleted (Remove from current slection lists)';


--
-- Name: sync_log; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE sync_log (
    id integer NOT NULL,
    direction smallint NOT NULL,
    start_time timestamp without time zone NOT NULL,
    finish_time timestamp without time zone NOT NULL,
    num_rows integer NOT NULL,
    is_success smallint NOT NULL,
    message character varying(255),
    CONSTRAINT ckc_num_rows_sync_log CHECK ((num_rows >= 0))
);


ALTER TABLE public.sync_log OWNER TO postgres;

--
-- Name: COLUMN sync_log.id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN sync_log.id IS 'Internally generated unique identifier';


--
-- Name: COLUMN sync_log.direction; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN sync_log.direction IS 'True=Upload to Master Instance; False=Download to LIC';


--
-- Name: COLUMN sync_log.start_time; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN sync_log.start_time IS 'Local Date/Time the batch process started';


--
-- Name: COLUMN sync_log.finish_time; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN sync_log.finish_time IS 'Local Date/Time the batch process finished';


--
-- Name: COLUMN sync_log.num_rows; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN sync_log.num_rows IS 'Actual number of rows in the batch';


--
-- Name: COLUMN sync_log.is_success; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN sync_log.is_success IS 'Operation was a success?';


--
-- Name: COLUMN sync_log.message; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN sync_log.message IS 'Logging messages';


--
-- Name: sync_table_version; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE sync_table_version (
    table_name character varying(40) NOT NULL,
    central_version bigint NOT NULL,
    uploaded_version bigint NOT NULL,
    last_sync_time timestamp without time zone NOT NULL
);


ALTER TABLE public.sync_table_version OWNER TO postgres;

--
-- Name: COLUMN sync_table_version.table_name; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN sync_table_version.table_name IS 'Name of a synchronised table';


--
-- Name: COLUMN sync_table_version.central_version; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN sync_table_version.central_version IS 'Highest Central_Version number received by the LIC so far from the Central Database';


--
-- Name: COLUMN sync_table_version.uploaded_version; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN sync_table_version.uploaded_version IS 'Highest Local_Version that was completely, successfully sent from the LIC to the central Database';


--
-- Name: COLUMN sync_table_version.last_sync_time; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN sync_table_version.last_sync_time IS 'Time of last successful sync';


--
-- Name: sync_version; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE sync_version (
    site_id integer,
    site_track_idnf character(18) NOT NULL,
    local_version bigint NOT NULL,
    last_upload_time timestamp without time zone,
    is_active smallint
);


ALTER TABLE public.sync_version OWNER TO postgres;

--
-- Name: COLUMN sync_version.site_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN sync_version.site_id IS 'Site Identifier';


--
-- Name: COLUMN sync_version.site_track_idnf; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN sync_version.site_track_idnf IS 'Tracking Number';


--
-- Name: COLUMN sync_version.local_version; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN sync_version.local_version IS 'Version number assigned to the last transaction';


--
-- Name: COLUMN sync_version.last_upload_time; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN sync_version.last_upload_time IS 'Time of last successful upload';


--
-- Name: COLUMN sync_version.is_active; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN sync_version.is_active IS 'True = Indicates the LIC  is active and editable by the user; False indicates initial Sync has not completed; NULL indicates de-registered or Master Instance';


--
-- Name: table_control; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE table_control (
    table_name character varying(40) NOT NULL,
    description character varying(255) NOT NULL,
    record_version bigint NOT NULL,
    is_deleted smallint NOT NULL
);


ALTER TABLE public.table_control OWNER TO postgres;

--
-- Name: COLUMN table_control.table_name; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN table_control.table_name IS 'Table name';


--
-- Name: COLUMN table_control.description; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN table_control.description IS 'English language description';


--
-- Name: COLUMN table_control.record_version; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN table_control.record_version IS 'Audit field: Lastest (local) change version number of the data';


--
-- Name: COLUMN table_control.is_deleted; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN table_control.is_deleted IS 'Record has been deleted (Remove from current slection lists)';


--
-- Name: translations; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE translations (
    id integer NOT NULL,
    table_name character varying(40) NOT NULL,
    language_idnf character(2) NOT NULL,
    lookup_id integer,
    lookup_code character(6),
    description character varying(255),
    name character varying(40),
    record_version bigint NOT NULL,
    is_deleted smallint NOT NULL
);


ALTER TABLE public.translations OWNER TO postgres;

--
-- Name: COLUMN translations.id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN translations.id IS 'Internally generated unique identifier';


--
-- Name: COLUMN translations.table_name; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN translations.table_name IS 'Table name';


--
-- Name: COLUMN translations.language_idnf; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN translations.language_idnf IS 'ISO639-1 language code';


--
-- Name: COLUMN translations.lookup_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN translations.lookup_id IS 'Identifier being looked up (Data-Control tables only)';


--
-- Name: COLUMN translations.lookup_code; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN translations.lookup_code IS 'Code being looked up (Program-Control tables only)';


--
-- Name: COLUMN translations.description; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN translations.description IS 'Translated description (Use english value in original lookup table if none found)';


--
-- Name: COLUMN translations.name; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN translations.name IS 'Translated short name (Use english value in original lookup table if none found)';


--
-- Name: COLUMN translations.record_version; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN translations.record_version IS 'Audit field: Lastest (local) change version number of the data';


--
-- Name: COLUMN translations.is_deleted; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN translations.is_deleted IS 'Record has been deleted (Remove from current slection lists)';


--
-- Name: transport_mode; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE transport_mode (
    code character(1) NOT NULL,
    description character varying(255) NOT NULL,
    record_version bigint NOT NULL,
    is_deleted smallint NOT NULL
);


ALTER TABLE public.transport_mode OWNER TO postgres;

--
-- Name: COLUMN transport_mode.code; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN transport_mode.code IS 'Mode: A=Air, W=Water, L=Land';


--
-- Name: COLUMN transport_mode.description; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN transport_mode.description IS 'English language description';


--
-- Name: COLUMN transport_mode.record_version; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN transport_mode.record_version IS 'Audit field: Lastest (local) change version number of the data';


--
-- Name: COLUMN transport_mode.is_deleted; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN transport_mode.is_deleted IS 'Record has been deleted (Remove from current slection lists)';


--
-- Name: user_parameters; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE user_parameters (
    user_id integer NOT NULL,
    parameter_name character varying(40) NOT NULL,
    parameter_value character varying(40),
    last_updated_time timestamp without time zone,
    record_version bigint NOT NULL,
    is_deleted smallint NOT NULL
);


ALTER TABLE public.user_parameters OWNER TO postgres;

--
-- Name: COLUMN user_parameters.user_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN user_parameters.user_id IS 'User identifier';


--
-- Name: COLUMN user_parameters.parameter_name; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN user_parameters.parameter_name IS 'Parameter Name';


--
-- Name: COLUMN user_parameters.parameter_value; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN user_parameters.parameter_value IS 'Parameter Value';


--
-- Name: COLUMN user_parameters.last_updated_time; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN user_parameters.last_updated_time IS 'Date/time the parameter was last updated';


--
-- Name: COLUMN user_parameters.record_version; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN user_parameters.record_version IS 'Audit field: Lastest (local) change version number of the data';


--
-- Name: COLUMN user_parameters.is_deleted; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN user_parameters.is_deleted IS 'Record has been deleted (Remove from current slection lists)';


--
-- Name: user_roles; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE user_roles (
    id integer NOT NULL,
    user_id integer NOT NULL,
    role_code character(3) NOT NULL,
    project_id integer,
    site_id integer,
    expiry_date date,
    record_version bigint NOT NULL,
    is_deleted smallint NOT NULL
);


ALTER TABLE public.user_roles OWNER TO postgres;

--
-- Name: COLUMN user_roles.id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN user_roles.id IS 'Internally generated unique identifier';


--
-- Name: COLUMN user_roles.user_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN user_roles.user_id IS 'Unique User ID';


--
-- Name: COLUMN user_roles.role_code; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN user_roles.role_code IS 'User''s Role';


--
-- Name: COLUMN user_roles.project_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN user_roles.project_id IS 'Projects that the user is allowed to edit';


--
-- Name: COLUMN user_roles.site_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN user_roles.site_id IS 'Project is Active for which sites';


--
-- Name: COLUMN user_roles.expiry_date; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN user_roles.expiry_date IS 'Date this authorisation expires (NULL if no expiry date)';


--
-- Name: COLUMN user_roles.record_version; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN user_roles.record_version IS 'Audit field: Lastest (local) change version number of the data';


--
-- Name: COLUMN user_roles.is_deleted; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN user_roles.is_deleted IS 'Record has been deleted (Remove from current slection lists)';


--
-- Name: users; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE users (
    id integer NOT NULL,
    org_id integer NOT NULL,
    email character varying(100) NOT NULL,
    password character varying(16) NOT NULL,
    name character varying(255) NOT NULL,
    expiry_date date,
    record_version bigint NOT NULL,
    is_deleted smallint NOT NULL
);


ALTER TABLE public.users OWNER TO postgres;

--
-- Name: COLUMN users.id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN users.id IS 'Internally generated unique identifier';


--
-- Name: COLUMN users.org_id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN users.org_id IS 'User"s Organisation ID (Link to WFP or LogCluster for head office users)';


--
-- Name: COLUMN users.email; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN users.email IS 'User"s Email';


--
-- Name: COLUMN users.password; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN users.password IS 'User"s encrypted password';


--
-- Name: COLUMN users.name; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN users.name IS 'User"s Name';


--
-- Name: COLUMN users.expiry_date; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN users.expiry_date IS 'Date this UserID expires (NULL if no expiry date)';


--
-- Name: COLUMN users.record_version; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN users.record_version IS 'Audit field: Lastest (local) change version number of the data';


--
-- Name: COLUMN users.is_deleted; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN users.is_deleted IS 'Record has been deleted (Remove from current slection lists)';


--
-- Name: vehicle_category; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE vehicle_category (
    id integer NOT NULL,
    description character varying(255) NOT NULL,
    transport_mode_code character(1) NOT NULL,
    record_version bigint NOT NULL,
    is_deleted smallint NOT NULL
);


ALTER TABLE public.vehicle_category OWNER TO postgres;

--
-- Name: COLUMN vehicle_category.id; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN vehicle_category.id IS 'Internally generated unique identifier';


--
-- Name: COLUMN vehicle_category.description; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN vehicle_category.description IS 'English language description';


--
-- Name: COLUMN vehicle_category.transport_mode_code; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN vehicle_category.transport_mode_code IS 'Mode: A=Air, W=Water, L=Land';


--
-- Name: COLUMN vehicle_category.record_version; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN vehicle_category.record_version IS 'Audit field: Lastest (local) change version number of the data';


--
-- Name: COLUMN vehicle_category.is_deleted; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN vehicle_category.is_deleted IS 'Record has been deleted (Remove from current slection lists)';


--
-- Name: viewpoints; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE viewpoints (
    screen_code character(2) NOT NULL,
    viewpoint_code character(3) NOT NULL,
    description character varying(255) NOT NULL,
    seqno integer NOT NULL,
    viewpoint_site_name character varying(40),
    is_default smallint NOT NULL,
    is_allowed_if_proj_closed smallint NOT NULL,
    record_version bigint NOT NULL,
    is_deleted smallint NOT NULL
);


ALTER TABLE public.viewpoints OWNER TO postgres;

--
-- Name: COLUMN viewpoints.screen_code; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN viewpoints.screen_code IS 'Code of the screen using the viewpoint';


--
-- Name: COLUMN viewpoints.viewpoint_code; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN viewpoints.viewpoint_code IS 'The Viewpoint Code, as used in the RITA code';


--
-- Name: COLUMN viewpoints.description; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN viewpoints.description IS 'English language description of the viewpoint, for the given ScreenCode';


--
-- Name: COLUMN viewpoints.seqno; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN viewpoints.seqno IS 'Sequence Number in the Viewpoint dropdown list';


--
-- Name: COLUMN viewpoints.viewpoint_site_name; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN viewpoints.viewpoint_site_name IS 'Viewpoint has an additional ''Site'' restriction (NULL if no viewpoint site)';


--
-- Name: COLUMN viewpoints.is_default; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN viewpoints.is_default IS 'Is this the default selection';


--
-- Name: COLUMN viewpoints.is_allowed_if_proj_closed; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN viewpoints.is_allowed_if_proj_closed IS 'Is the viewpoint allowed/valid if the project is closed';


--
-- Name: COLUMN viewpoints.record_version; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN viewpoints.record_version IS 'Audit field: Lastest (local) change version number of the data';


--
-- Name: COLUMN viewpoints.is_deleted; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN viewpoints.is_deleted IS 'Record has been deleted (Remove from current slection lists)';


--
-- Name: ak_keyprojsite_project_2; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY project_site
    ADD CONSTRAINT ak_keyprojsite_project_2 PRIMARY KEY (project_id, site_id);


--
-- Name: pk_arrival_type; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY arrival_type
    ADD CONSTRAINT pk_arrival_type PRIMARY KEY (code);


--
-- Name: pk_building_type; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY building_type
    ADD CONSTRAINT pk_building_type PRIMARY KEY (id);


--
-- Name: pk_bundle; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY bundle
    ADD CONSTRAINT pk_bundle PRIMARY KEY (bundle_site_id, id);


--
-- Name: pk_bundle_type; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY bundle_type
    ADD CONSTRAINT pk_bundle_type PRIMARY KEY (id);


--
-- Name: pk_bundle_type_group; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY bundle_type_group
    ADD CONSTRAINT pk_bundle_type_group PRIMARY KEY (id);


--
-- Name: pk_change_type; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY change_type
    ADD CONSTRAINT pk_change_type PRIMARY KEY (code);


--
-- Name: pk_contact; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY contact
    ADD CONSTRAINT pk_contact PRIMARY KEY (owner_site_id, id);


--
-- Name: pk_country; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY country
    ADD CONSTRAINT pk_country PRIMARY KEY (id);


--
-- Name: pk_country_type; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY country_type
    ADD CONSTRAINT pk_country_type PRIMARY KEY (code);


--
-- Name: pk_currency; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY currency
    ADD CONSTRAINT pk_currency PRIMARY KEY (id);


--
-- Name: pk_cust_order; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY cust_order
    ADD CONSTRAINT pk_cust_order PRIMARY KEY (id);


--
-- Name: pk_cust_order_history; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY cust_order_history
    ADD CONSTRAINT pk_cust_order_history PRIMARY KEY (commenting_site_id, cust_order_id, change_time);


--
-- Name: pk_cust_order_line; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY cust_order_line
    ADD CONSTRAINT pk_cust_order_line PRIMARY KEY (id);


--
-- Name: pk_dispatch_priority; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY dispatch_priority
    ADD CONSTRAINT pk_dispatch_priority PRIMARY KEY (id);


--
-- Name: pk_distribution_type; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY distribution_type
    ADD CONSTRAINT pk_distribution_type PRIMARY KEY (id);


--
-- Name: pk_enclosure_type; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY enclosure_type
    ADD CONSTRAINT pk_enclosure_type PRIMARY KEY (code);


--
-- Name: pk_exchange_rate; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY exchange_rate
    ADD CONSTRAINT pk_exchange_rate PRIMARY KEY (currency_id, take_effect_date);


--
-- Name: pk_journey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY journey
    ADD CONSTRAINT pk_journey PRIMARY KEY (journey_site_id, id);


--
-- Name: pk_journey_type; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY journey_type
    ADD CONSTRAINT pk_journey_type PRIMARY KEY (code);


--
-- Name: pk_kit_type; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY kit_type
    ADD CONSTRAINT pk_kit_type PRIMARY KEY (code);


--
-- Name: pk_languages; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY languages
    ADD CONSTRAINT pk_languages PRIMARY KEY (iso2_idnf);


--
-- Name: pk_list_action; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY list_action
    ADD CONSTRAINT pk_list_action PRIMARY KEY (screen_code, list_action_code);


--
-- Name: pk_list_action_roles; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY list_action_roles
    ADD CONSTRAINT pk_list_action_roles PRIMARY KEY (screen_code, list_action_code, role_code);


--
-- Name: pk_list_action_viewpoints; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY list_action_viewpoints
    ADD CONSTRAINT pk_list_action_viewpoints PRIMARY KEY (screen_code, list_action_code, viewpoint_code);


--
-- Name: pk_location; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY location
    ADD CONSTRAINT pk_location PRIMARY KEY (location_site_id, id);


--
-- Name: pk_location_type; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY location_type
    ADD CONSTRAINT pk_location_type PRIMARY KEY (code);


--
-- Name: pk_movement; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY movement
    ADD CONSTRAINT pk_movement PRIMARY KEY (move_site_id, id);


--
-- Name: pk_movement_comment; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY movement_comment
    ADD CONSTRAINT pk_movement_comment PRIMARY KEY (move_site_id, id);


--
-- Name: pk_org; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY org
    ADD CONSTRAINT pk_org PRIMARY KEY (id);


--
-- Name: pk_org_address; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY org_address
    ADD CONSTRAINT pk_org_address PRIMARY KEY (id);


--
-- Name: pk_org_type; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY org_type
    ADD CONSTRAINT pk_org_type PRIMARY KEY (code);


--
-- Name: pk_port_type; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY port_type
    ADD CONSTRAINT pk_port_type PRIMARY KEY (id);


--
-- Name: pk_product; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY product
    ADD CONSTRAINT pk_product PRIMARY KEY (id);


--
-- Name: pk_product_ltu; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY product_ltu
    ADD CONSTRAINT pk_product_ltu PRIMARY KEY (id);


--
-- Name: pk_product_sku; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY product_sku
    ADD CONSTRAINT pk_product_sku PRIMARY KEY (id);


--
-- Name: pk_project; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY project
    ADD CONSTRAINT pk_project PRIMARY KEY (id);


--
-- Name: pk_project_classify; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY project_classify
    ADD CONSTRAINT pk_project_classify PRIMARY KEY (code);


--
-- Name: pk_project_purpose; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY project_purpose
    ADD CONSTRAINT pk_project_purpose PRIMARY KEY (id);


--
-- Name: pk_region; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY region
    ADD CONSTRAINT pk_region PRIMARY KEY (id);


--
-- Name: pk_request; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY request
    ADD CONSTRAINT pk_request PRIMARY KEY (request_site_id, id);


--
-- Name: pk_request_cust_type; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY request_cust_type
    ADD CONSTRAINT pk_request_cust_type PRIMARY KEY (code);


--
-- Name: pk_request_history; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY request_history
    ADD CONSTRAINT pk_request_history PRIMARY KEY (commenting_site_id, request_site_id, request_id, change_time);


--
-- Name: pk_request_line; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY request_line
    ADD CONSTRAINT pk_request_line PRIMARY KEY (id, request_site_id);


--
-- Name: pk_request_line_priority; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY request_line_priority
    ADD CONSTRAINT pk_request_line_priority PRIMARY KEY (commenting_site_id, request_line_id, request_site_id);


--
-- Name: pk_request_status; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY request_status
    ADD CONSTRAINT pk_request_status PRIMARY KEY (code);


--
-- Name: pk_roles; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY roles
    ADD CONSTRAINT pk_roles PRIMARY KEY (code);


--
-- Name: pk_screen_code; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY screen_code
    ADD CONSTRAINT pk_screen_code PRIMARY KEY (code);


--
-- Name: pk_service_type; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY service_type
    ADD CONSTRAINT pk_service_type PRIMARY KEY (code);


--
-- Name: pk_shipment; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY shipment
    ADD CONSTRAINT pk_shipment PRIMARY KEY (shipment_site_id, id);


--
-- Name: pk_shipment_comment; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY shipment_comment
    ADD CONSTRAINT pk_shipment_comment PRIMARY KEY (shipment_site_id, shipment_id, owner_site_id);


--
-- Name: pk_site; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY site
    ADD CONSTRAINT pk_site PRIMARY KEY (id);


--
-- Name: pk_site_track_no; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY site_track_no
    ADD CONSTRAINT pk_site_track_no PRIMARY KEY (site_id);


--
-- Name: pk_subregion; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY subregion
    ADD CONSTRAINT pk_subregion PRIMARY KEY (id);


--
-- Name: pk_sync_log; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY sync_log
    ADD CONSTRAINT pk_sync_log PRIMARY KEY (id);


--
-- Name: pk_sync_table_version; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY sync_table_version
    ADD CONSTRAINT pk_sync_table_version PRIMARY KEY (table_name);


--
-- Name: pk_table_control; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY table_control
    ADD CONSTRAINT pk_table_control PRIMARY KEY (table_name);


--
-- Name: pk_translations; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY translations
    ADD CONSTRAINT pk_translations PRIMARY KEY (id);


--
-- Name: pk_transport_mode; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY transport_mode
    ADD CONSTRAINT pk_transport_mode PRIMARY KEY (code);


--
-- Name: pk_user_parameters; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY user_parameters
    ADD CONSTRAINT pk_user_parameters PRIMARY KEY (user_id, parameter_name);


--
-- Name: pk_user_roles; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY user_roles
    ADD CONSTRAINT pk_user_roles PRIMARY KEY (id);


--
-- Name: pk_users; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY users
    ADD CONSTRAINT pk_users PRIMARY KEY (id);


--
-- Name: pk_vehicle_category; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY vehicle_category
    ADD CONSTRAINT pk_vehicle_category PRIMARY KEY (id);


--
-- Name: pk_viewpoints; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY viewpoints
    ADD CONSTRAINT pk_viewpoints PRIMARY KEY (screen_code, viewpoint_code);


--
-- Name: idxactivetrack; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
--

CREATE UNIQUE INDEX idxactivetrack ON site_track_no USING btree (site_id, is_active);


--
-- Name: idxctryname; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
--

CREATE UNIQUE INDEX idxctryname ON country USING btree (name);


--
-- Name: idxcurrcode; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
--

CREATE UNIQUE INDEX idxcurrcode ON currency USING btree (iso3_idnf);


--
-- Name: idxcurrname; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
--

CREATE UNIQUE INDEX idxcurrname ON currency USING btree (name);


--
-- Name: idxcustline; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
--

CREATE UNIQUE INDEX idxcustline ON cust_order_line USING btree (cust_order_id, seqno);


--
-- Name: idxdpchprtyseqnnumb; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
--

CREATE UNIQUE INDEX idxdpchprtyseqnnumb ON dispatch_priority USING btree (seqno);


--
-- Name: idxiso2; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
--

CREATE UNIQUE INDEX idxiso2 ON country USING btree (iso2_idnf);


--
-- Name: idxiso3; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
--

CREATE UNIQUE INDEX idxiso3 ON country USING btree (iso3_idnf);


--
-- Name: idxjourney; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
--

CREATE INDEX idxjourney ON movement USING btree (bundle_site_id, bundle_id);


--
-- Name: idxlineseqnnumb; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
--

CREATE UNIQUE INDEX idxlineseqnnumb ON request_line USING btree (request_site_id, request_id, seqno);


--
-- Name: idxlistdesc; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
--

CREATE UNIQUE INDEX idxlistdesc ON list_action USING btree (screen_code, description);


--
-- Name: idxlistseqno; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
--

CREATE UNIQUE INDEX idxlistseqno ON list_action USING btree (screen_code, seqno);


--
-- Name: idxlookup; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
--

CREATE INDEX idxlookup ON contact USING btree (org_id);


--
-- Name: idxlookupcode; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
--

CREATE UNIQUE INDEX idxlookupcode ON translations USING btree (table_name, language_idnf, lookup_code);


--
-- Name: idxlookupid; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
--

CREATE UNIQUE INDEX idxlookupid ON translations USING btree (table_name, language_idnf, lookup_id);


--
-- Name: idxofficialname; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
--

CREATE UNIQUE INDEX idxofficialname ON country USING btree (official_name);


--
-- Name: idxorgabrv; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
--

CREATE INDEX idxorgabrv ON org USING btree (abrv);


--
-- Name: idxorgname; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
--

CREATE INDEX idxorgname ON org USING btree (name);


--
-- Name: idxprodtemidnf; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
--

CREATE UNIQUE INDEX idxprodtemidnf ON product USING btree (product_idnf);


--
-- Name: idxprojidnf; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
--

CREATE UNIQUE INDEX idxprojidnf ON project USING btree (project_idnf);


--
-- Name: idxprojname; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
--

CREATE UNIQUE INDEX idxprojname ON project USING btree (name);


--
-- Name: idxregnname; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
--

CREATE INDEX idxregnname ON region USING btree (name);


--
-- Name: idxrequest; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
--

CREATE INDEX idxrequest ON movement USING btree (request_site_id);


--
-- Name: idxrequestline; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
--

CREATE INDEX idxrequestline ON movement USING btree (request_site_id, request_line_id);


--
-- Name: idxrqstseqnnumb; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
--

CREATE UNIQUE INDEX idxrqstseqnnumb ON request USING btree (project_id, request_site_id, seqno, suffix, service_type_code);


--
-- Name: idxrqststatname; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
--

CREATE UNIQUE INDEX idxrqststatname ON request_status USING btree (request_cust_type_code, name);


--
-- Name: idxrqststatseqnnumb; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
--

CREATE UNIQUE INDEX idxrqststatseqnnumb ON request_status USING btree (request_cust_type_code, seqno);


--
-- Name: idxshipmentid; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
--

CREATE UNIQUE INDEX idxshipmentid ON request USING btree (request_site_id, initial_shipment_id);


--
-- Name: idxsitename; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
--

CREATE UNIQUE INDEX idxsitename ON site USING btree (site_name);


--
-- Name: idxsiteprfx; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
--

CREATE UNIQUE INDEX idxsiteprfx ON project_site USING btree (site_prefix);


--
-- Name: idxsitetrcknumb; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
--

CREATE UNIQUE INDEX idxsitetrcknumb ON site_track_no USING btree (site_track_idnf);


--
-- Name: idxsrgnname; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
--

CREATE INDEX idxsrgnname ON subregion USING btree (region_id, name);


--
-- Name: idxuser; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
--

CREATE UNIQUE INDEX idxuser ON user_roles USING btree (user_id, project_id, site_id, role_code);


--
-- Name: idxuserlogon; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
--

CREATE UNIQUE INDEX idxuserlogon ON users USING btree (email);


--
-- Name: idxviewdesc; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
--

CREATE UNIQUE INDEX idxviewdesc ON viewpoints USING btree (description);


--
-- Name: idxviewseqno; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
--

CREATE UNIQUE INDEX idxviewseqno ON viewpoints USING btree (seqno, screen_code);


--
-- Name: fk_bundle_t_reference_bundle_t; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY bundle_type
    ADD CONSTRAINT fk_bundle_t_reference_bundle_t FOREIGN KEY (group_id) REFERENCES bundle_type_group(id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_bundle_t_reference_enclosur; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY bundle_type
    ADD CONSTRAINT fk_bundle_t_reference_enclosur FOREIGN KEY (enclosure_type_code) REFERENCES enclosure_type(code) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_contact_reference_project_; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY contact
    ADD CONSTRAINT fk_contact_reference_project_ FOREIGN KEY (project_id, owner_site_id) REFERENCES project_site(project_id, site_id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_country_reference_country_; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY country
    ADD CONSTRAINT fk_country_reference_country_ FOREIGN KEY (country_type_code) REFERENCES country_type(code) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_country_reference_currency; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY country
    ADD CONSTRAINT fk_country_reference_currency FOREIGN KEY (currency_id) REFERENCES currency(id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_country_reference_subregio; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY country
    ADD CONSTRAINT fk_country_reference_subregio FOREIGN KEY (subregion_id) REFERENCES subregion(id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_cust_ord_reference_change_t; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY cust_order_history
    ADD CONSTRAINT fk_cust_ord_reference_change_t FOREIGN KEY (change_type_code) REFERENCES change_type(code) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_cust_ord_reference_contact; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY cust_order
    ADD CONSTRAINT fk_cust_ord_reference_contact FOREIGN KEY (sender_site_id, sender_contact_id) REFERENCES contact(owner_site_id, id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_cust_ord_reference_contact2; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY cust_order
    ADD CONSTRAINT fk_cust_ord_reference_contact2 FOREIGN KEY (invoice_site_id, invoice_contact_id) REFERENCES contact(owner_site_id, id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_cust_ord_reference_contact3; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY cust_order
    ADD CONSTRAINT fk_cust_ord_reference_contact3 FOREIGN KEY (receiver_site_id, receiver_contact_id) REFERENCES contact(owner_site_id, id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_cust_ord_reference_cust_ord; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY cust_order_history
    ADD CONSTRAINT fk_cust_ord_reference_cust_ord FOREIGN KEY (cust_order_id) REFERENCES cust_order(id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_cust_ord_reference_cust_ord2; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY cust_order_line
    ADD CONSTRAINT fk_cust_ord_reference_cust_ord2 FOREIGN KEY (cust_order_id) REFERENCES cust_order(id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_cust_ord_reference_kit_type; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY cust_order_line
    ADD CONSTRAINT fk_cust_ord_reference_kit_type FOREIGN KEY (kit_type_code) REFERENCES kit_type(code) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_cust_ord_reference_location; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY cust_order
    ADD CONSTRAINT fk_cust_ord_reference_location FOREIGN KEY (arrival_location_site_id, arrival_location_id) REFERENCES location(location_site_id, id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_cust_ord_reference_location2; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY cust_order
    ADD CONSTRAINT fk_cust_ord_reference_location2 FOREIGN KEY (exit_location_site_id, exit_location_id) REFERENCES location(location_site_id, id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_cust_ord_reference_product; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY cust_order_line
    ADD CONSTRAINT fk_cust_ord_reference_product FOREIGN KEY (product_id) REFERENCES product(id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_cust_ord_reference_product2; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY cust_order
    ADD CONSTRAINT fk_cust_ord_reference_product2 FOREIGN KEY (product_id) REFERENCES product(id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_cust_ord_reference_product_; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY cust_order_line
    ADD CONSTRAINT fk_cust_ord_reference_product_ FOREIGN KEY (sku_id) REFERENCES product_sku(id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_cust_ord_reference_product_2; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY cust_order_line
    ADD CONSTRAINT fk_cust_ord_reference_product_2 FOREIGN KEY (ltu_id) REFERENCES product_ltu(id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_cust_ord_reference_project_2; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY cust_order
    ADD CONSTRAINT fk_cust_ord_reference_project_2 FOREIGN KEY (project_id, request_site_id) REFERENCES project_site(project_id, site_id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_cust_ord_reference_request_; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY cust_order_history
    ADD CONSTRAINT fk_cust_ord_reference_request_ FOREIGN KEY (new_status_code) REFERENCES request_status(code) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_cust_ord_reference_request_2; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY cust_order
    ADD CONSTRAINT fk_cust_ord_reference_request_2 FOREIGN KEY (status_code) REFERENCES request_status(code) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_cust_ord_reference_request_3; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY cust_order
    ADD CONSTRAINT fk_cust_ord_reference_request_3 FOREIGN KEY (service_type_code) REFERENCES service_type(code) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_exchange_reference_currency; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY exchange_rate
    ADD CONSTRAINT fk_exchange_reference_currency FOREIGN KEY (currency_id) REFERENCES currency(id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_journey_reference_journey_; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY journey
    ADD CONSTRAINT fk_journey_reference_journey_ FOREIGN KEY (journey_type_code) REFERENCES journey_type(code) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_journey_reference_vehicle_; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY journey
    ADD CONSTRAINT fk_journey_reference_vehicle_ FOREIGN KEY (vehicle_category_id) REFERENCES vehicle_category(id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_list_act_reference_list_act; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY list_action_viewpoints
    ADD CONSTRAINT fk_list_act_reference_list_act FOREIGN KEY (screen_code, list_action_code) REFERENCES list_action(screen_code, list_action_code) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_list_act_reference_list_act2; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY list_action_roles
    ADD CONSTRAINT fk_list_act_reference_list_act2 FOREIGN KEY (screen_code, list_action_code) REFERENCES list_action(screen_code, list_action_code) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_list_act_reference_roles; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY list_action_roles
    ADD CONSTRAINT fk_list_act_reference_roles FOREIGN KEY (role_code) REFERENCES roles(code) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_list_act_reference_screen_c; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY list_action
    ADD CONSTRAINT fk_list_act_reference_screen_c FOREIGN KEY (screen_code) REFERENCES screen_code(code) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_list_act_reference_viewpoin; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY list_action_viewpoints
    ADD CONSTRAINT fk_list_act_reference_viewpoin FOREIGN KEY (screen_code, viewpoint_code) REFERENCES viewpoints(screen_code, viewpoint_code) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_location_reference_country; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY location
    ADD CONSTRAINT fk_location_reference_country FOREIGN KEY (country_id) REFERENCES country(id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_location_reference_location; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY location
    ADD CONSTRAINT fk_location_reference_location FOREIGN KEY (location_type_code) REFERENCES location_type(code) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_location_reference_port_typ; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY location
    ADD CONSTRAINT fk_location_reference_port_typ FOREIGN KEY (port_type_id) REFERENCES port_type(id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_location_reference_project; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY location
    ADD CONSTRAINT fk_location_reference_project FOREIGN KEY (project_id) REFERENCES project(id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_location_reference_project_; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY location
    ADD CONSTRAINT fk_location_reference_project_ FOREIGN KEY (project_id, parent_site_id) REFERENCES project_site(project_id, site_id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_movement_reference_bundle; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY movement
    ADD CONSTRAINT fk_movement_reference_bundle FOREIGN KEY (bundle_site_id, bundle_id) REFERENCES bundle(bundle_site_id, id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_movement_reference_location; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY movement
    ADD CONSTRAINT fk_movement_reference_location FOREIGN KEY (affected_location_site_id, affected_location_id) REFERENCES location(location_site_id, id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_movement_reference_movement; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY movement_comment
    ADD CONSTRAINT fk_movement_reference_movement FOREIGN KEY (move_site_id, id) REFERENCES movement(move_site_id, id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_movement_reference_shipment; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY movement
    ADD CONSTRAINT fk_movement_reference_shipment FOREIGN KEY (shipment_site_id, shipment_id) REFERENCES shipment(shipment_site_id, id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_org_addr_reference_org; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY org_address
    ADD CONSTRAINT fk_org_addr_reference_org FOREIGN KEY (org_id) REFERENCES org(id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_org_reference_country; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY org
    ADD CONSTRAINT fk_org_reference_country FOREIGN KEY (country_id) REFERENCES country(id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_org_reference_org_type; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY org
    ADD CONSTRAINT fk_org_reference_org_type FOREIGN KEY (org_type_code) REFERENCES org_type(code) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_product_reference_product_; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY product
    ADD CONSTRAINT fk_product_reference_product_ FOREIGN KEY (dflt_sku_id) REFERENCES product_sku(id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_product_reference_product_2; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY product
    ADD CONSTRAINT fk_product_reference_product_2 FOREIGN KEY (dflt_ltu_id) REFERENCES product_ltu(id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_project__reference_project_2; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY project_purpose
    ADD CONSTRAINT fk_project__reference_project_2 FOREIGN KEY (project_classify_code) REFERENCES project_classify(code) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_project__reference_site; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY project_site
    ADD CONSTRAINT fk_project__reference_site FOREIGN KEY (site_id) REFERENCES site(id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_project_reference_org; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY project
    ADD CONSTRAINT fk_project_reference_org FOREIGN KEY (org_id) REFERENCES org(id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_project_reference_region; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY project
    ADD CONSTRAINT fk_project_reference_region FOREIGN KEY (region_id) REFERENCES region(id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_project_reference_subregio; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY project
    ADD CONSTRAINT fk_project_reference_subregio FOREIGN KEY (subregion_id) REFERENCES subregion(id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_request__reference_change_t; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY request_history
    ADD CONSTRAINT fk_request__reference_change_t FOREIGN KEY (change_type_code) REFERENCES change_type(code) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_request__reference_dispatch; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY request_line_priority
    ADD CONSTRAINT fk_request__reference_dispatch FOREIGN KEY (line_dispatch_priority_id) REFERENCES dispatch_priority(id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_request__reference_kit_type; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY request_line
    ADD CONSTRAINT fk_request__reference_kit_type FOREIGN KEY (kit_type_code) REFERENCES kit_type(code) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_request__reference_product; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY request_line
    ADD CONSTRAINT fk_request__reference_product FOREIGN KEY (product_id) REFERENCES product(id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_request__reference_product_; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY request_line
    ADD CONSTRAINT fk_request__reference_product_ FOREIGN KEY (sku_id) REFERENCES product_sku(id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_request__reference_product_2; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY request_line
    ADD CONSTRAINT fk_request__reference_product_2 FOREIGN KEY (ltu_id) REFERENCES product_ltu(id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_request__reference_request2; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY request_history
    ADD CONSTRAINT fk_request__reference_request2 FOREIGN KEY (request_site_id, request_id) REFERENCES request(request_site_id, id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_request__reference_request_; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY request_line_priority
    ADD CONSTRAINT fk_request__reference_request_ FOREIGN KEY (request_line_id, request_site_id) REFERENCES request_line(id, request_site_id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_request__reference_request_3; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY request_status
    ADD CONSTRAINT fk_request__reference_request_3 FOREIGN KEY (request_cust_type_code) REFERENCES request_cust_type(code) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_request_reference_arrival_; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY request
    ADD CONSTRAINT fk_request_reference_arrival_ FOREIGN KEY (arrival_type_code) REFERENCES arrival_type(code) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_request_reference_contact; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY request
    ADD CONSTRAINT fk_request_reference_contact FOREIGN KEY (invoice_site_id, invoice_contact_id) REFERENCES contact(owner_site_id, id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_request_reference_location; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY request
    ADD CONSTRAINT fk_request_reference_location FOREIGN KEY (arrival_location_site_id, arrival_location_id) REFERENCES location(location_site_id, id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_request_reference_product; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY request
    ADD CONSTRAINT fk_request_reference_product FOREIGN KEY (product_id) REFERENCES product(id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_request_reference_project_; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY request
    ADD CONSTRAINT fk_request_reference_project_ FOREIGN KEY (project_id, request_site_id) REFERENCES project_site(project_id, site_id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_request_reference_service_; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY request
    ADD CONSTRAINT fk_request_reference_service_ FOREIGN KEY (service_type_code) REFERENCES service_type(code) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_request_reference_shipment; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY request
    ADD CONSTRAINT fk_request_reference_shipment FOREIGN KEY (request_site_id, initial_shipment_id) REFERENCES shipment(shipment_site_id, id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_request_reference_transpor; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY request
    ADD CONSTRAINT fk_request_reference_transpor FOREIGN KEY (transport_mode_code) REFERENCES transport_mode(code) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_shipment_reference_journey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY shipment
    ADD CONSTRAINT fk_shipment_reference_journey FOREIGN KEY (shipment_site_id, journey_id) REFERENCES journey(journey_site_id, id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_shipment_reference_location; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY shipment
    ADD CONSTRAINT fk_shipment_reference_location FOREIGN KEY (destination_location_site_id, destination_location_id) REFERENCES location(location_site_id, id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_shipment_reference_shipment; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY shipment_comment
    ADD CONSTRAINT fk_shipment_reference_shipment FOREIGN KEY (shipment_site_id, shipment_id) REFERENCES shipment(shipment_site_id, id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_site_reference_building; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY site
    ADD CONSTRAINT fk_site_reference_building FOREIGN KEY (site_bldg_type_id) REFERENCES building_type(id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_site_reference_distribu; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY site
    ADD CONSTRAINT fk_site_reference_distribu FOREIGN KEY (site_dstr_type_id) REFERENCES distribution_type(id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_site_reference_org; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY site
    ADD CONSTRAINT fk_site_reference_org FOREIGN KEY (org_id) REFERENCES org(id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_site_tra_reference_site; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY site_track_no
    ADD CONSTRAINT fk_site_tra_reference_site FOREIGN KEY (site_id) REFERENCES site(id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_subregio_reference_region; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY subregion
    ADD CONSTRAINT fk_subregio_reference_region FOREIGN KEY (region_id) REFERENCES region(id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_tblconta_reference_tblcont2; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY bundle
    ADD CONSTRAINT fk_tblconta_reference_tblcont2 FOREIGN KEY (bundle_type_id) REFERENCES bundle_type(id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_tblmovem_reference_tblrequ2; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY movement
    ADD CONSTRAINT fk_tblmovem_reference_tblrequ2 FOREIGN KEY (request_line_id, request_site_id) REFERENCES request_line(id, request_site_id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_tblproje_reference_tblcoun2; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY contact
    ADD CONSTRAINT fk_tblproje_reference_tblcoun2 FOREIGN KEY (country_id) REFERENCES country(id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_tblproje_reference_tbllocn2; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY project
    ADD CONSTRAINT fk_tblproje_reference_tbllocn2 FOREIGN KEY (country_id) REFERENCES country(id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_tblproje_reference_tblorga2; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY contact
    ADD CONSTRAINT fk_tblproje_reference_tblorga2 FOREIGN KEY (org_id) REFERENCES org(id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_tblproje_reference_tblproj3; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY project
    ADD CONSTRAINT fk_tblproje_reference_tblproj3 FOREIGN KEY (purpose_id) REFERENCES project_purpose(id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_tblproje_reference_tblproj6; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY project_site
    ADD CONSTRAINT fk_tblproje_reference_tblproj6 FOREIGN KEY (project_id) REFERENCES project(id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_tblreque_reference_tblproj2; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY request
    ADD CONSTRAINT fk_tblreque_reference_tblproj2 FOREIGN KEY (sender_site_id, sender_contact_id) REFERENCES contact(owner_site_id, id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_tblreque_reference_tblproj3; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY request
    ADD CONSTRAINT fk_tblreque_reference_tblproj3 FOREIGN KEY (receiver_site_id, receiver_contact_id) REFERENCES contact(owner_site_id, id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_tblreque_reference_tblrequ2; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY request
    ADD CONSTRAINT fk_tblreque_reference_tblrequ2 FOREIGN KEY (status_code) REFERENCES request_status(code) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_tblreque_reference_tblrequ3; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY request
    ADD CONSTRAINT fk_tblreque_reference_tblrequ3 FOREIGN KEY (dispatch_priority_id) REFERENCES dispatch_priority(id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_tblreque_reference_tblrequ4; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY request_line
    ADD CONSTRAINT fk_tblreque_reference_tblrequ4 FOREIGN KEY (request_site_id, request_id) REFERENCES request(request_site_id, id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_tblreque_reference_tblrequ6; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY request_history
    ADD CONSTRAINT fk_tblreque_reference_tblrequ6 FOREIGN KEY (new_priority_id) REFERENCES dispatch_priority(id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_tblreque_reference_tblrequ7; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY request_history
    ADD CONSTRAINT fk_tblreque_reference_tblrequ7 FOREIGN KEY (new_status_code) REFERENCES request_status(code) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_tbluser_reference_tblorg2; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY users
    ADD CONSTRAINT fk_tbluser_reference_tblorg2 FOREIGN KEY (org_id) REFERENCES org(id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_translat_reference_language; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY translations
    ADD CONSTRAINT fk_translat_reference_language FOREIGN KEY (language_idnf) REFERENCES languages(iso2_idnf) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_translat_reference_table_co; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY translations
    ADD CONSTRAINT fk_translat_reference_table_co FOREIGN KEY (table_name) REFERENCES table_control(table_name) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_user_par_reference_users; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY user_parameters
    ADD CONSTRAINT fk_user_par_reference_users FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_user_rol_reference_project; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY user_roles
    ADD CONSTRAINT fk_user_rol_reference_project FOREIGN KEY (project_id) REFERENCES project(id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_user_rol_reference_project_; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY user_roles
    ADD CONSTRAINT fk_user_rol_reference_project_ FOREIGN KEY (project_id, site_id) REFERENCES project_site(project_id, site_id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_user_rol_reference_roles; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY user_roles
    ADD CONSTRAINT fk_user_rol_reference_roles FOREIGN KEY (role_code) REFERENCES roles(code) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_user_rol_reference_users; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY user_roles
    ADD CONSTRAINT fk_user_rol_reference_users FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_vehicle__reference_transpor; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY vehicle_category
    ADD CONSTRAINT fk_vehicle__reference_transpor FOREIGN KEY (transport_mode_code) REFERENCES transport_mode(code) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: fk_viewpoin_reference_screen_c; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY viewpoints
    ADD CONSTRAINT fk_viewpoin_reference_screen_c FOREIGN KEY (screen_code) REFERENCES screen_code(code) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;


-- Completed on 2010-07-14 14:25:35

--
-- PostgreSQL database dump complete
--