CREATE TABLE P_HELP_TRIGGER (
  HELP_TRIGGER_PK INTEGER UNSIGNED NOT NULL,
  HELP_TRIGGER_OBJECT VARCHAR(255) NOT NULL,
  HELP_TRIGGER_VALUE VARCHAR(2000) NOT NULL,
  HELP_TRIGGER_PRIORITY SMALLINT UNSIGNED NOT NULL DEFAULT '1',
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  REC_UPDATE_NAME VARCHAR(255) NULL,
  REC_UPDATE_STAMP DATETIME NULL,
  PRIMARY KEY(HELP_TRIGGER_PK)
)
TYPE=InnoDB;

CREATE TABLE P_ACCESS_LOG (
  ACCESS_LOG_PK INTEGER UNSIGNED NOT NULL,
  ACCESS_ACCOUNT VARCHAR(255) NULL,
  ACCESS_TABLE VARCHAR(255) NULL,
  ACCESS_ACTION VARCHAR(20) NULL,
  ACCESS_TABLE_PK INTEGER UNSIGNED NULL,
  ACCESS_ACTION_STATUS VARCHAR(20) NULL,
  ACCESS_ACTION_MESSAGE TEXT NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  PRIMARY KEY(ACCESS_LOG_PK)
)
TYPE=InnoDB;

CREATE TABLE P_ALERT_RULE (
  ALERT_RULE_PK INTEGER UNSIGNED NOT NULL,
  ALERT_RULE_TITLE VARCHAR(255) NOT NULL,
  ALERT_RULE_DESC TEXT NULL,
  ALERT_RULE_START DATETIME NOT NULL,
  ALERT_RULE_END DATETIME NULL,
  ALERT_RULE_REVIEW DATETIME NULL,
  ALERT_RULE_REVIEW_LAST DATETIME NULL,
  ALERT_RULE_REVIEW_FREQ SMALLINT UNSIGNED NULL DEFAULT '90',
  ALERT_RULE_ACTIVE BOOL NOT NULL DEFAULT '1',
  REC_TO_DEL BOOL NOT NULL DEFAULT '0',
  REC_IN_CORE BOOL NOT NULL DEFAULT '0',
  REC_TBP INTEGER UNSIGNED NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  REC_UPDATE_NAME VARCHAR(255) NULL,
  REC_UPDATE_STAMP DATETIME NULL,
  PRIMARY KEY(ALERT_RULE_PK),
  INDEX P_ALERT_RULE_AK1(ALERT_RULE_TITLE)
)
TYPE=InnoDB;

CREATE TABLE P_ALERT_CFG (
  ALERT_CFG_PK INTEGER UNSIGNED NOT NULL,
  ALERT_TYPE VARCHAR(20) NOT NULL DEFAULT 'SMTP',
  ALERT_SERVER VARCHAR(255) NOT NULL DEFAULT 'localhost',
  ALERT_SERVER_TIMEOUT SMALLINT UNSIGNED NOT NULL DEFAULT '60',
  ALERT_PORT SMALLINT UNSIGNED NOT NULL DEFAULT '25',
  ALERT_LOGON_REQ BOOL NOT NULL DEFAULT '0',
  ALERT_LOGON_ACC VARCHAR(255) NULL,
  ALERT_LOGON_PASS VARCHAR(255) NULL,
  ALERT_START DATETIME NOT NULL,
  ALERT_END DATETIME NULL,
  ALERT_FREQ INTEGER UNSIGNED NOT NULL DEFAULT '60',
  ALERT_DEF_FROM VARCHAR(2000) NULL,
  ALERT_DEF_TO VARCHAR(2000) NULL,
  ALERT_DEF_SEC_TO VARCHAR(2000) NULL,
  ALERT_DEF_REPLY VARCHAR(2000) NULL,
  ALERT_DISCLAIMER VARCHAR(2000) NULL,
  ALERT_ACTIVE BOOL NOT NULL DEFAULT '0',
  ALERT_PAUSE BOOL NOT NULL DEFAULT '0',
  ALERT_LAST DATETIME NULL,
  ALERT_NEXT DATETIME NULL,
  ALERT_LOG_SCREEN BOOL NOT NULL DEFAULT '1',
  ALERT_LOG_FILE BOOL NOT NULL DEFAULT '1',
  ALERT_LOG_DB BOOL NOT NULL DEFAULT '1',
  ALERT_LOG_FILENAME VARCHAR(2000) NULL,
  REC_TO_DEL BOOL NOT NULL DEFAULT '0',
  REC_IN_CORE BOOL NOT NULL DEFAULT '0',
  REC_TBP VARCHAR(255) NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  REC_UPDATE_NAME VARCHAR(255) NULL,
  REC_UPDATE_STAMP DATETIME NULL,
  PRIMARY KEY(ALERT_CFG_PK)
)
TYPE=InnoDB;

CREATE TABLE P_IMPORT_MANAGER (
  IMPORT_MANAGER_PK INTEGER UNSIGNED NOT NULL,
  IMPORT_FILE VARCHAR(255) NOT NULL,
  IMPORT_LAST_CHECK DATETIME NOT NULL,
  IMPORT_LAST_CHANGE DATETIME NOT NULL,
  IMPORT_MD5 VARCHAR(45) NOT NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  REC_UPDATE_NAME VARCHAR(255) NULL,
  REC_UPDATE_STAMP DATETIME NULL,
  PRIMARY KEY(IMPORT_MANAGER_PK)
)
TYPE=InnoDB;

-- ------------------------------------------------------------
-- list of products available for sale
-- ------------------------------------------------------------

CREATE TABLE P_PRODUCT (
  PRODUCT_PK INTEGER UNSIGNED NOT NULL,
  PRODUCT_UID VARCHAR(255) NULL,
  PRODUCT_BODY VARCHAR(255) NOT NULL,
  PRODUCT_TITLE VARCHAR(255) NOT NULL,
  PRODUCT_TYPE VARCHAR(255) NULL,
  PRODUCT_TYPE_SUB VARCHAR(255) NULL,
  PRODUCT_START DATETIME NOT NULL,
  PRODUCT_END DATETIME NULL,
  PRODUCT_REVIEW DATETIME NULL,
  PRODUCT_REVIEW_LAST DATETIME NULL,
  PRODUCT_REVIEW_FREQ SMALLINT UNSIGNED NOT NULL DEFAULT '90',
  PRODUCT_CORE BOOL NOT NULL DEFAULT '0',
  PRODUCT_ACTIVE BOOL NOT NULL DEFAULT '1',
  PRODUCT_BODY_REVIEW DATETIME NULL,
  PRODUCT_BODY_REVIEW_LAST DATETIME NULL,
  PRODUCT_BODY_REVIEW_FREQ SMALLINT UNSIGNED NOT NULL DEFAULT '90',
  PRODUCT_BODY_CERT VARCHAR(255) NULL,
  PRODUCT_BODY_CERT_LEVEL VARCHAR(255) NULL,
  PRODUCT_BODY_CERT_START DATETIME NULL,
  PRODUCT_BODY_CERT_END DATETIME NULL,
  PRODUCT_BODY_CERT_REVIEW DATETIME NULL,
  PRODUCT_BODY_CERT_REVIEW_LAST DATETIME NULL,
  PRODUCT_BODY_CERT_REVIEW_FREQ SMALLINT UNSIGNED ZEROFILL NOT NULL DEFAULT '90',
  REC_IMPORT BOOL NOT NULL DEFAULT '0',
  REC_LOCAL BOOL NOT NULL DEFAULT '0',
  REC_TO_DEL BOOL NOT NULL DEFAULT '0',
  REC_IN_CORE BOOL NOT NULL DEFAULT '0',
  REC_TBP VARCHAR(255) NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  REC_UPDATE_NAME VARCHAR(255) NULL,
  REC_UPDATE_STAMP DATETIME NULL,
  PRIMARY KEY(PRODUCT_PK),
  INDEX P_PRODUCT_IE1(PRODUCT_BODY, PRODUCT_TITLE),
  INDEX P_PRODUCT_IE2(PRODUCT_UID)
)
TYPE=InnoDB;

CREATE TABLE P_PRODUCT_FAMILY (
  PROD_FAMILY_PK INTEGER UNSIGNED NOT NULL,
  PROD_FAMILY_TITLE VARCHAR(255) NOT NULL,
  PROD_FAMILY_USE VARCHAR(45) NULL,
  PROD_FAMILY_START DATETIME NOT NULL,
  PROD_FAMILY_END DATETIME NULL,
  PROD_FAMILY_REVIEW DATETIME NULL,
  PROD_FAMILY_REVIEW_LAST DATETIME NULL,
  PROD_FAMILY_REVIEW_FREQ SMALLINT UNSIGNED NOT NULL DEFAULT '90',
  PROD_FAMILY_CORE BOOL NOT NULL,
  PROD_FAMILY_ACTIVE BOOL NOT NULL DEFAULT '1',
  REC_TO_DEL BOOL NOT NULL DEFAULT '0',
  REC_IN_CORE BOOL NOT NULL DEFAULT '0',
  REC_TBP VARCHAR(255) NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  REC_UPDATE_NAME VARCHAR(255) NULL,
  REC_UPDATE_STAMP DATETIME NULL,
  PRIMARY KEY(PROD_FAMILY_PK),
  UNIQUE INDEX P_PRODUCT_FAMILY_AK1(PROD_FAMILY_TITLE, PROD_FAMILY_USE)
)
TYPE=InnoDB;

CREATE TABLE P_IMPORT_MANAGER_SPEC (
  IMPORT_MANAGER_SPEC_PK INTEGER UNSIGNED NOT NULL,
  IMS_FORM VARCHAR(255) NOT NULL,
  IMS_COL VARCHAR(255) NOT NULL,
  IMS_TAB VARCHAR(45) NOT NULL,
  IMS_TAB_COL VARCHAR(45) NOT NULL,
  IMS_COL_TYPE VARCHAR(20) NOT NULL,
  IMS_COL_ACT BOOL NOT NULL DEFAULT '1',
  IMS_COL_START DATETIME NOT NULL,
  IMS_COL_END DATETIME NULL,
  IMS_COL_REQ BOOL NOT NULL DEFAULT '0',
  IMS_COL_KEY BOOL NOT NULL DEFAULT '0',
  IMS_COL_DEF BOOL NOT NULL DEFAULT '0',
  IMS_COL_DEF_VAL VARCHAR(2000) NULL,
  REC_CREATE_NAME VARCHAR(45) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  REC_UPDATE_NAME VARCHAR(45) NULL,
  REC_UPDATE_STAMP DATETIME NULL,
  PRIMARY KEY(IMPORT_MANAGER_SPEC_PK),
  UNIQUE INDEX P_IMPORT_MANAGER_SPEC_AK1(IMS_FORM, IMS_COL)
)
TYPE=InnoDB;

CREATE TABLE P_KEYS (
  KEYSPK INTEGER UNSIGNED NOT NULL DEFAULT '1',
  MASTER_PK INTEGER UNSIGNED NOT NULL DEFAULT '1',
  PRIMARY KEY(KEYSPK)
)
TYPE=InnoDB;

CREATE TABLE P_HELP (
  HELP_PK INTEGER UNSIGNED NOT NULL,
  HELP_SOURCE VARCHAR(2000) NOT NULL,
  HELP_DESC TEXT NOT NULL,
  HELP_CAUSE TEXT NULL,
  HELP_EFFECT TEXT NULL,
  HELP_PRIORITY SMALLINT UNSIGNED NOT NULL DEFAULT '1',
  HELP_ACTIVE BOOL NOT NULL DEFAULT '1',
  HELP_TRACK_USE BOOL NOT NULL DEFAULT '1',
  HELP_TRACK_USER BOOL NOT NULL DEFAULT '1',
  HELP_TRACK_COMMENT BOOL NOT NULL DEFAULT '1',
  HELP_TRACK_ANON BOOL NOT NULL DEFAULT '1',
  HELP_TRACK_SHOW_APP BOOL NOT NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  REC_UPDATE_NAME VARCHAR(255) NULL,
  REC_UPDATE_STAMP DATETIME NULL,
  PRIMARY KEY(HELP_PK)
)
TYPE=InnoDB;

CREATE TABLE P_CUSTOMER (
  CUSTOMER_PK INTEGER UNSIGNED NOT NULL,
  CUSTOMER_UID VARCHAR(255) NULL,
  CUSTOMER_TYPE VARCHAR(20) NULL,
  CUSTOMER_NAME VARCHAR(255) NULL,
  CUSTOMER_TITLE VARCHAR(255) NULL,
  CUSTOMER_FIRST VARCHAR(255) NULL,
  CUSTOMER_FIRST_NICK VARCHAR(255) NULL,
  CUSTOMER_MIDDLE VARCHAR(255) NULL,
  CUSTOMER_LAST VARCHAR(255) NULL,
  CUSTOMER_INITIALS VARCHAR(255) NULL,
  CUSTOMER_LEVEL VARCHAR(45) NULL,
  CUSTOMER_DOB DATETIME NULL,
  CUSTOMER_DOD DATETIME NULL,
  CUSTOMER_SEX BOOL NOT NULL,
  CUSTOMER_START DATETIME NOT NULL,
  CUSTOMER_END DATETIME NULL,
  CUSTOMER_INTRODUCED_BY VARCHAR(255) NULL,
  CUSTOMER_REVIEW DATETIME NULL,
  CUSTOMER_REVIEW_LAST DATETIME NULL,
  CUSTOMER_REVIEW_FREQ SMALLINT UNSIGNED NOT NULL DEFAULT '90',
  CUSTOMER_SUSPEND BOOL NOT NULL DEFAULT '0',
  REC_IMPORT BOOL NOT NULL DEFAULT '0',
  REC_LOCAL BOOL NOT NULL DEFAULT '0',
  REC_TO_DEL BOOL NOT NULL DEFAULT '0',
  REC_IN_CORE BOOL NOT NULL DEFAULT '0',
  REC_TBP VARCHAR(255) NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  REC_UPDATE_NAME VARCHAR(255) NULL,
  REC_UPDATE_STAMP DATETIME NULL,
  PRIMARY KEY(CUSTOMER_PK),
  UNIQUE INDEX P_CUSTOMER_AK1(CUSTOMER_NAME),
  INDEX P_CUSTOMER_IE1(CUSTOMER_UID),
  INDEX P_CUSTOMER_IE2(CUSTOMER_FIRST, CUSTOMER_MIDDLE, CUSTOMER_LAST, CUSTOMER_DOB)
)
TYPE=InnoDB;

-- ------------------------------------------------------------
-- Holds information as to how a person/group/co etc. can be contacted
-- ------------------------------------------------------------

CREATE TABLE P_CONTACT (
  CONTACT_PK INTEGER UNSIGNED NOT NULL,
  CONTACT_TYPE VARCHAR(45) NOT NULL,
  CONTACT_CLASS VARCHAR(45) NULL,
  CONTACT_ADDRESS VARCHAR(2000) NOT NULL,
  CONTACT_ADDRESS2 VARCHAR(255) NULL,
  CONTACT_ADDRESS3 VARCHAR(255) NULL,
  CONTACT_ADDRESS4 VARCHAR(255) NULL,
  CONTACT_ADDRESS5 VARCHAR(255) NULL,
  CONTACT_ADDRESS6 VARCHAR(255) NULL,
  CONTACT_ADDRESS_NO VARCHAR(20) NULL,
  CONTACT_POSTCODE VARCHAR(20) NULL,
  CONTACT_START DATETIME NOT NULL,
  CONTACT_END DATETIME NULL,
  CONTACT_REVIEW DATETIME NULL,
  CONTACT_REVIEW_LAST DATETIME NULL,
  CONTACT_REVIEW_FREQ SMALLINT UNSIGNED NOT NULL DEFAULT '90',
  CONTACT_TYPE_DEF BOOL NOT NULL DEFAULT '0',
  CONTACT_SUSPEND BOOL NOT NULL DEFAULT '0',
  REC_IMPORT BOOL NOT NULL DEFAULT '0',
  REC_LOCAL BOOL NOT NULL DEFAULT '1',
  REC_TO_DEL BOOL NOT NULL DEFAULT '0',
  REC_IN_CORE BOOL NOT NULL DEFAULT '0',
  REC_TBP VARCHAR(255) NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  REC_UPDATE_NAME VARCHAR(255) NULL,
  REC_UPDATE_STAMP DATETIME NULL,
  PRIMARY KEY(CONTACT_PK),
  INDEX P_CONTACT_IE1(CONTACT_PK, CONTACT_TYPE),
  INDEX P_CONTACT_IE2(CONTACT_PK, CONTACT_TYPE, CONTACT_CLASS)
)
TYPE=InnoDB;

CREATE TABLE P_DDL (
  DDL_PK INTEGER UNSIGNED NOT NULL,
  DDL_TABLE VARCHAR(255) NOT NULL,
  DDL_COL VARCHAR(45) NOT NULL,
  DDL_STORE VARCHAR(255) NOT NULL,
  DDL_DISP VARCHAR(255) NOT NULL,
  DDL_START DATETIME NOT NULL,
  DDL_END DATETIME NULL,
  DDL_ACTIVE BOOL NOT NULL DEFAULT '1',
  DDL_GLOBAL BOOL NOT NULL DEFAULT '1',
  DDL_REVIEW DATETIME NULL,
  DDL_REVIEW_LAST DATETIME NULL,
  DDL_REVIEW_FREQ SMALLINT UNSIGNED NOT NULL DEFAULT '90',
  REC_TO_DEL BOOL NOT NULL DEFAULT '0',
  REC_IN_CORE BOOL NOT NULL DEFAULT '0',
  REC_TBP INTEGER UNSIGNED NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  REC_UPDATE_NAME VARCHAR(255) NULL,
  REC_UPDATE_STAMP DATETIME NULL,
  PRIMARY KEY(DDL_PK),
  UNIQUE INDEX P_DDL_AK1(DDL_TABLE, DDL_COL, DDL_STORE, DDL_DISP),
  INDEX P_DDL_IE1(DDL_TABLE, DDL_COL)
)
TYPE=InnoDB;

-- ------------------------------------------------------------
-- Link table holding information as to who received the communication, how it was issued and what the communication was
-- ------------------------------------------------------------

CREATE TABLE P_CUSTOMER_COMMUNICATION (
  CUSTOMER_COMMUNICATION_PK INTEGER UNSIGNED NOT NULL,
  CUSTOMER_PK INTEGER UNSIGNED NOT NULL,
  COMMUNICATION_PK INTEGER UNSIGNED NOT NULL,
  CUSTOMER_NAME VARCHAR(255) NULL,
  CONTACT_TO TEXT NULL,
  CONTACT_CC TEXT NULL,
  CONTACT_BCC TEXT NULL,
  COMMUNICATION_SUBJECT VARCHAR(255) NULL,
  COMMUNICATION_BODY TEXT NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  PRIMARY KEY(CUSTOMER_COMMUNICATION_PK),
  INDEX P_CUSTOMER_COMMUNICATION_IE1(CUSTOMER_PK),
  INDEX P_CUSTOMER_COMMUNICATION_IE2(COMMUNICATION_PK)
)
TYPE=InnoDB;

CREATE TABLE P_ERROR (
  ERROR_UID INTEGER UNSIGNED NOT NULL,
  ERROR_SOURCE VARCHAR(255) NOT NULL,
  ERROR_SEVERITY VARCHAR(45) NOT NULL,
  ERROR_DESC TEXT NOT NULL,
  ERROR_SOL TEXT NOT NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  REC_UPDATE_NAME VARCHAR(255) NULL,
  REC_UPDATE_STAMP DATETIME NULL,
  PRIMARY KEY(ERROR_UID)
)
TYPE=InnoDB;

CREATE TABLE P_FLAG_NAME (
  FLAG_NAME_PK INTEGER UNSIGNED NOT NULL,
  FLAG_NAME_DISP VARCHAR(255) NOT NULL,
  FLAG_NAME_STORE VARCHAR(255) NOT NULL,
  FLAG_NAME_START DATETIME NOT NULL,
  FLAG_NAME_END DATETIME NULL,
  FLAG_NAME_ACTIVE BOOL NOT NULL DEFAULT '1',
  FLAG_NAME_GLOBAL BOOL NOT NULL DEFAULT '1',
  FLAG_OWNER INTEGER UNSIGNED NULL,
  FLAG_IS_REQ BOOL NOT NULL DEFAULT '0',
  FLAG_HAS_DEFAULT BOOL NOT NULL DEFAULT '0',
  FLAG_HAS_FREE_VALUE BOOL NOT NULL DEFAULT '0',
  FLAG_NAME_REVIEW DATETIME NULL,
  FLAG_NAME_REVIEW_LAST DATETIME NULL,
  FLAG_NAME_REVIEW_FREQ SMALLINT UNSIGNED NOT NULL DEFAULT '90',
  REC_TO_DEL BOOL NOT NULL DEFAULT '0',
  REC_IN_CORE BOOL NOT NULL DEFAULT '0',
  REC_TBP VARCHAR(255) NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  REC_UPDATE_NAME VARCHAR(255) NULL,
  REC_UPDATE_STAMP DATETIME NULL,
  PRIMARY KEY(FLAG_NAME_PK),
  UNIQUE INDEX P_FLAG_NAME_AK1(FLAG_NAME_DISP, FLAG_NAME_STORE),
  UNIQUE INDEX P_FLAG_NAME_AK2(FLAG_NAME_DISP)
)
TYPE=InnoDB;

CREATE TABLE P_BUSINESS_RULES (
  BUSINESS_RULES_PK INTEGER UNSIGNED NOT NULL,
  REC_TO_DEL BOOL NOT NULL DEFAULT '0',
  REC_IN_CORE BOOL NOT NULL DEFAULT '0',
  REC_TBP VARCHAR(255) NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  REC_UPDATE_NAME VARCHAR(255) NULL,
  REC_UPDATE_STAMP DATETIME NULL,
  PRIMARY KEY(BUSINESS_RULES_PK)
)
TYPE=InnoDB;

CREATE TABLE P_COMPANY (
  COMPANY_PK INTEGER UNSIGNED NOT NULL,
  COMPANY_UID VARCHAR(255) NULL,
  COMPANY_NAME VARCHAR(255) NOT NULL,
  COMPANY_NAME_SHORT VARCHAR(255) NULL,
  COMPANY_NO VARCHAR(20) NULL,
  COMPANY_TRADING_FROM DATETIME NULL,
  COMPANY_TRADING_TO DATETIME NULL,
  COMPANY_YEAR_END DATETIME NULL,
  COMPANY_YEAR_END_LAST DATETIME NULL,
  COMPANY_START DATETIME NULL,
  COMPANY_FINISH DATETIME NULL,
  COMPANY_REVIEW DATETIME NULL,
  COMPANY_REVIEW_LAST DATETIME NULL,
  COMPANY_REVIEW_FREQ SMALLINT UNSIGNED NOT NULL,
  REC_IMPORT BOOL NOT NULL DEFAULT '0',
  REC_LOCAL BOOL NOT NULL DEFAULT '0',
  REC_TO_DEL BOOL NOT NULL DEFAULT '0',
  REC_IN_CORE BOOL NOT NULL DEFAULT '0',
  REC_TBP VARCHAR(255) NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  REC_UPDATE_NAME VARCHAR(255) NULL,
  REC_UPDATE_STAMP DATETIME NULL,
  PRIMARY KEY(COMPANY_PK),
  UNIQUE INDEX P_COMPANY_AK3(COMPANY_UID),
  UNIQUE INDEX P_COMPANY_AK1(COMPANY_NAME),
  UNIQUE INDEX P_COMPANY_AK2(COMPANY_NO)
)
TYPE=InnoDB;

-- ------------------------------------------------------------
-- Table used to hold master list of standard communications to be issued from inside the system.
-- ------------------------------------------------------------

CREATE TABLE P_COMMUNICATION (
  COMMUNICATION_PK INTEGER UNSIGNED NOT NULL,
  COMMUNICATION_TYPE VARCHAR(45) NOT NULL,
  COMMUNICATION_TITLE VARCHAR(255) NOT NULL,
  COMMUNICATION_START DATETIME NOT NULL,
  COMMUNICATION_END DATETIME NULL,
  COMMUNICATION_REVIEW DATETIME NULL,
  COMMUNICATION_REVIEW_LAST DATETIME NULL,
  COMMUNICATION_REVIEW_FREQ SMALLINT UNSIGNED NOT NULL DEFAULT '90',
  COMMUNICATION_SOURCE TEXT NULL,
  COMMUNICATION_ACTIVE BOOL NOT NULL DEFAULT '1',
  REC_TO_DEL BOOL NOT NULL DEFAULT '0',
  REC_IN_CORE BOOL NOT NULL DEFAULT '0',
  REC_TBP VARCHAR(255) NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  REC_UPDATE_NAME VARCHAR(255) NULL,
  REC_UPDATE_STAMP DATETIME NULL,
  PRIMARY KEY(COMMUNICATION_PK),
  INDEX P_COMMUNICATION_AK1(COMMUNICATION_TYPE, COMMUNICATION_TITLE)
)
TYPE=InnoDB;

-- ------------------------------------------------------------
-- People working within the organisation
-- ------------------------------------------------------------

CREATE TABLE P_STAFF (
  STAFF_PK INTEGER UNSIGNED NOT NULL,
  STAFF_UID VARCHAR(255) NOT NULL,
  STAFF_SYSTEM_NAME VARCHAR(45) NOT NULL,
  STAFF_TITLE VARCHAR(20) NOT NULL,
  STAFF_GIVENNAME VARCHAR(255) NOT NULL,
  STAFF_SURNAME VARCHAR(255) NOT NULL,
  STAFF_INITIALS VARCHAR(20) NULL,
  STAFF_DISPLAY_NAME VARCHAR(2000) NOT NULL,
  STAFF_START DATETIME NOT NULL,
  STAFF_END DATETIME NULL,
  STAFF_REVIEW DATETIME NULL,
  STAFF_REVIEW_LAST DATETIME NULL,
  STAFF_REVIEW_FREQ SMALLINT UNSIGNED NOT NULL DEFAULT '90',
  STAFF_ACTIVE BOOL NOT NULL DEFAULT '1',
  STAFF_SEX TINYINT(1) UNSIGNED NOT NULL,
  STAFF_COMPANY VARCHAR(255) NULL,
  STAFF_DEPARTMENT VARCHAR(255) NULL,
  STAFF_JOB_TITLE VARCHAR(255) NULL,
  STAFF_POSITION VARCHAR(45) NULL,
  REC_LOCAL BOOL NOT NULL DEFAULT '0',
  REC_IMPORT BOOL NOT NULL DEFAULT '0',
  REC_TO_DEL BOOL NOT NULL DEFAULT '0',
  REC_IN_CORE BOOL NOT NULL DEFAULT '0',
  REC_TBP VARCHAR(255) NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  REC_UPDATE_NAME VARCHAR(255) NULL,
  REC_UPDATE_STAMP DATETIME NULL,
  PRIMARY KEY(STAFF_PK),
  UNIQUE INDEX P_STAFF_AK1(STAFF_UID),
  INDEX P_STAFF_IE1(STAFF_GIVENNAME, STAFF_SURNAME, STAFF_INITIALS)
)
TYPE=InnoDB;

-- ------------------------------------------------------------
-- holds a list of vendors of products
-- ------------------------------------------------------------

CREATE TABLE P_PRODUCT_VENDOR (
  PRODUCT_VENDOR_PK INTEGER UNSIGNED NOT NULL,
  PRODUCT_VENDOR_NAME VARCHAR(255) NULL,
  PRODUCT_VENDOR_ACTIVE BOOL NOT NULL DEFAULT '1',
  PRODUCT_VENDOR_PRIORITY SMALLINT UNSIGNED NOT NULL DEFAULT '1',
  PRODUCT_VENDOR_START DATETIME NOT NULL,
  PRODUCT_VENDOR_END DATETIME NULL,
  PRODUCT_VENDOR_REVIEW DATETIME NULL,
  PRODUCT_VENDOR_REVIEW_LAST DATETIME NULL,
  PRODUCT_VENDOR_REVIEW_FREQ SMALLINT UNSIGNED NOT NULL DEFAULT '90',
  PRODUCT_VENDOR_WEB_SITE INTEGER UNSIGNED NULL,
  REC_IMPORT BOOL NOT NULL DEFAULT '0',
  REC_LOCAL BOOL NOT NULL DEFAULT '0',
  REC_TO_DEL BOOL NOT NULL DEFAULT '0',
  REC_IN_CORE BOOL NOT NULL DEFAULT '0',
  REC_TBP VARCHAR(255) NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  REC_UPDATE_NAME VARCHAR(255) NULL,
  REC_UPDATE_STAMP DATETIME NULL,
  PRIMARY KEY(PRODUCT_VENDOR_PK),
  UNIQUE INDEX P_PRODUCT_VENDOR_AK1(PRODUCT_VENDOR_NAME)
)
TYPE=InnoDB;

-- ------------------------------------------------------------
-- Link table holding information as to who received the communication, how it was issued and what the communication was
-- ------------------------------------------------------------

CREATE TABLE P_STAFF_COMMUNICATION (
  STAFF_COMMUNICATION_PK INTEGER UNSIGNED NOT NULL,
  STAFF_PK INTEGER UNSIGNED NOT NULL,
  COMMUNICATION_PK INTEGER UNSIGNED NOT NULL,
  STAFF_DISPLAY_NAME TEXT NULL,
  CONTACT_TO TEXT NULL,
  CONTACT_CC TEXT NULL,
  CONTACT_BCC TEXT NULL,
  COMMUNICATION_SUBJECT VARCHAR(255) NULL,
  COMMUNICATION_BODY TEXT NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  PRIMARY KEY(STAFF_COMMUNICATION_PK),
  INDEX P_STAFF_COMMUNICATION_IE1(STAFF_PK),
  INDEX P_STAFF_COMMUNICATION_IE2(COMMUNICATION_PK)
)
TYPE=InnoDB;

CREATE TABLE P_SKILL (
  SKILL_PK INTEGER UNSIGNED NOT NULL,
  SKILL_NAME VARCHAR(255) NOT NULL,
  SKILL_START DATETIME NOT NULL,
  SKILL_END DATETIME NULL,
  SKILL_REVIEW DATETIME NULL,
  SKILL_REVIEW_LAST DATETIME NULL,
  SKILL_REVIEW_FREQ SMALLINT UNSIGNED NOT NULL DEFAULT '90',
  SKILL_BODY VARCHAR(255) NOT NULL,
  SKILL_RATE VARCHAR(45) NULL,
  SKILL_ACTIVE BOOL NOT NULL DEFAULT '0',
  SKILL_REQUIRED BOOL NOT NULL DEFAULT '0',
  SKILL_CORE BOOL NOT NULL DEFAULT '0',
  REC_TO_DEL BOOL NOT NULL DEFAULT '0',
  REC_IN_CORE BOOL NOT NULL DEFAULT '0',
  REC_TBP VARCHAR(255) NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  REC_UPDATE_NAME VARCHAR(255) NULL,
  REC_UPDATE_STAMP DATETIME NULL,
  PRIMARY KEY(SKILL_PK),
  INDEX P_SKILL_AK1(SKILL_NAME)
)
TYPE=InnoDB;

CREATE TABLE P_SECURITY_PROFILE (
  SECURITY_PROFILE_PK INTEGER UNSIGNED NOT NULL,
  SP_NAME VARCHAR(255) NOT NULL,
  SP_START DATETIME NOT NULL,
  SP_END DATETIME NULL,
  SP_REVIEW DATETIME NULL,
  SP_REVIEW_LAST DATETIME NULL,
  SP_REVIEW_FREQ SMALLINT UNSIGNED NOT NULL DEFAULT '90',
  SP_ACTIVE BOOL NOT NULL DEFAULT '0',
  SP_DEFAULT BOOL NOT NULL DEFAULT '0',
  SP_PRIORITY SMALLINT UNSIGNED NOT NULL DEFAULT '1',
  SP_OBJ_INS_DEF BOOL NOT NULL DEFAULT '0',
  SP_OBJ_INS_LOG_DEF BOOL NOT NULL DEFAULT '1',
  SP_OBJ_UPD_DEF BOOL NOT NULL DEFAULT '0',
  SP_OBJ_UPD_LOG_DEF BOOL NOT NULL DEFAULT '1',
  SP_OBJ_DEL_DEF BOOL NOT NULL DEFAULT '0',
  SP_OBJ_DEL_LOG_DEF BOOL NOT NULL DEFAULT '1',
  SP_OBJ_EXEC_DEF BOOL NOT NULL DEFAULT '0',
  SP_OBJ_EXEC_LOG_DEF BOOL NOT NULL DEFAULT '1',
  SP_OBJ_SEL_DEF BOOL NOT NULL DEFAULT '0',
  REC_TO_DEL BOOL NOT NULL DEFAULT '0',
  REC_IN_CORE BOOL NOT NULL DEFAULT '0',
  REC_TBP VARCHAR(255) NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  REC_UPDATE_NAME VARCHAR(255) NULL,
  REC_UPDATE_STAMP DATETIME NULL,
  PRIMARY KEY(SECURITY_PROFILE_PK),
  INDEX P_SECURITY_PROFILE_AK1(SP_NAME)
)
TYPE=InnoDB;

CREATE TABLE L_CUSTOMER_RELATION (
  CUSTOMER_PK_PARENT INTEGER UNSIGNED NOT NULL,
  CUSTOMER_PK_CHILD INTEGER UNSIGNED NOT NULL,
  CUSTOMER_RELATION VARCHAR(45) NOT NULL,
  CUSTOMER_RELATION_START DATETIME NOT NULL,
  CUSTOMER_RELATION_END DATETIME NULL,
  REC_TO_DEL BOOL NOT NULL DEFAULT '0',
  REC_IN_CORE BOOL NOT NULL DEFAULT '0',
  REC_TBP VARCHAR(255) NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  REC_UPDATE_NAME VARCHAR(255) NULL,
  REC_UPDATE_STAMP DATETIME NULL,
  PRIMARY KEY(CUSTOMER_PK_PARENT, CUSTOMER_PK_CHILD, CUSTOMER_RELATION),
  UNIQUE INDEX L_CUSTOMER_RELATION_AK1(CUSTOMER_PK_PARENT, CUSTOMER_PK_CHILD)
)
TYPE=InnoDB;

CREATE TABLE P_USER_LOG (
  USER_LOG_PK INTEGER UNSIGNED NOT NULL,
  STAFF_PK INTEGER UNSIGNED NOT NULL,
  STAFF_SYSTEM_NAME VARCHAR(255) NULL,
  STAFF_DISPLAY_NAME VARCHAR(255) NULL,
  SEC_PROCESS VARCHAR(45) NULL,
  SEC_TYPE VARCHAR(45) NOT NULL,
  SEC_OBJ VARCHAR(45) NOT NULL,
  SEC_OBJ_KEY INT NULL,
  SEC_STATUS VARCHAR(10) NOT NULL,
  SEC_RET_CODE INT NOT NULL,
  SEC_MESSAGE VARCHAR(2000) NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  PRIMARY KEY(USER_LOG_PK),
  INDEX P_USER_LOG_IE1(STAFF_PK),
  INDEX P_USER_LOG_IE2(SEC_TYPE, SEC_OBJ)
)
TYPE=InnoDB;

-- ------------------------------------------------------------
-- list of products available for sale
-- ------------------------------------------------------------

CREATE TABLE T_PRODUCT (
  PRODUCT_PK INTEGER UNSIGNED NOT NULL,
  STAFF_PK INTEGER UNSIGNED NOT NULL,
  TRANSID INTEGER UNSIGNED NULL,
  PRODUCT_TITLE VARCHAR(255) NULL,
  PRODUCT_START DATETIME NULL,
  PRODUCT_END DATETIME NULL,
  PRODUCT_REVIEW DATETIME NULL,
  PRODUCT_REVIEW_LAST DATETIME NULL,
  PRODUCT_REVIEW_FREQ SMALLINT UNSIGNED NULL DEFAULT '90',
  PRODUCT_CORE TINYINT(1) UNSIGNED NULL DEFAULT '0',
  PRODUCT_ACTIVE BOOL NULL DEFAULT '1',
  PRODUCT_OWNER VARCHAR(45) NULL,
  PRODUCT_BODY VARCHAR(255) NULL,
  PRODUCT_BODY_REVIEW DATETIME NULL,
  PRODUCT_BODY_REVIEW_LAST DATETIME NULL,
  PRODUCT_BODY_REVIEW_FREQ SMALLINT UNSIGNED NULL DEFAULT '90',
  PRODUCT_BODY_CERT VARCHAR(255) NULL,
  PRODUCT_BODY_CERT_LEVEL VARCHAR(255) NULL,
  PRODUCT_BODY_CERT_START DATETIME NULL,
  PRODUCT_BODY_CERT_END DATETIME NULL,
  PRODUCT_BODY_CERT_REVIEW DATETIME NULL,
  PRODUCT_BODY_CERT_REVIEW_LAST DATETIME NULL,
  PRODUCT_BODY_CERT_REVIEW_FREQ VARCHAR(45) NULL,
  DELETEREC BOOL NOT NULL DEFAULT '0',
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  REC_UPDATE_NAME VARCHAR(255) NULL,
  REC_UPDATE_STAMP DATETIME NULL,
  PRIMARY KEY(PRODUCT_PK, STAFF_PK)
)
TYPE=InnoDB;

CREATE TABLE A_CUSTOMER_PROSPECT (
  PROSPECT_PK INTEGER UNSIGNED NOT NULL,
  PRODUCT_TITLE VARCHAR(255) NULL,
  PRODUCT_START DATETIME NULL,
  PRODUCT_END DATETIME NULL,
  PRODUCT_REVIEW DATETIME NULL,
  PRODUCT_REVIEW_FREQ SMALLINT UNSIGNED NULL,
  PROSPECT_VALUE FLOAT NULL,
  PROSPECT_FAIL_DATE DATETIME NOT NULL,
  PROSPECT_FAIL_OWNER VARCHAR(255) NOT NULL,
  PROSPECT_TO_DEL BOOL NOT NULL DEFAULT '0',
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  REC_ABANDON_NAME VARCHAR(255) NULL,
  REC_ABANDON_STAMP DATETIME NULL,
  PRIMARY KEY(PROSPECT_PK)
)
TYPE=InnoDB;

CREATE TABLE P_SYS_CFG (
  SYS_CFG_PK INTEGER UNSIGNED NOT NULL DEFAULT '1',
  DB_VER VARCHAR(20) NULL,
  APP_VER VARCHAR(20) NULL,
  IMPORT_ACTIVE BOOL NULL,
  RUN_ACTIVE BOOL NULL,
  ENABLE_LOGON BOOL NULL,
  PRIMARY KEY(SYS_CFG_PK)
)
TYPE=InnoDB;

CREATE TABLE P_STAFF_NOTES (
  NOTES_PK INTEGER UNSIGNED NOT NULL,
  STAFF_PK INTEGER UNSIGNED NOT NULL,
  NOTE TEXT NOT NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  PRIMARY KEY(NOTES_PK),
  INDEX P_STAFF_NOTES_FK1(STAFF_PK),
  FOREIGN KEY P_STAFF_FKC4(STAFF_PK)
    REFERENCES P_STAFF(STAFF_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

CREATE TABLE P_SKILL_NOTES (
  NOTES_PK INTEGER UNSIGNED NOT NULL,
  SKILL_PK INTEGER UNSIGNED NOT NULL,
  NOTE TEXT NOT NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  PRIMARY KEY(NOTES_PK),
  INDEX P_SKILL_NOTES_FK1(SKILL_PK),
  FOREIGN KEY P_SKILL_FKC3(SKILL_PK)
    REFERENCES P_SKILL(SKILL_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

CREATE TABLE P_ERROR_LOG (
  ERROR_LOG_PK INTEGER UNSIGNED NOT NULL,
  ERROR_UID INTEGER UNSIGNED NOT NULL,
  ERROR_SOURCE VARCHAR(255) NOT NULL,
  ERROR_MESSAGE TEXT NOT NULL,
  ERROR_NAME VARCHAR(255) NULL,
  ERROR_STAMP DATETIME NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  PRIMARY KEY(ERROR_LOG_PK),
  INDEX P_ERROR_LOG_FK1(ERROR_UID),
  FOREIGN KEY P_ERROR_FKC1(ERROR_UID)
    REFERENCES P_ERROR(ERROR_UID)
      ON DELETE CASCADE
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

CREATE TABLE P_FLAG (
  FLAG_PK INTEGER UNSIGNED NOT NULL,
  FLAG_NAME_PK INTEGER UNSIGNED NOT NULL DEFAULT '0',
  FLAG_VALUE VARCHAR(2000) NOT NULL,
  REC_TO_DEL BOOL NOT NULL DEFAULT '0',
  REC_IN_CORE BOOL NOT NULL DEFAULT '0',
  REC_TBP VARCHAR(255) NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  REC_UPDATE_NAME VARCHAR(255) NULL,
  REC_UPDATE_STAMP DATETIME NULL,
  PRIMARY KEY(FLAG_PK),
  INDEX P_FLAG_FK1(FLAG_NAME_PK),
  FOREIGN KEY P_FLAG_NAME_FKC1(FLAG_NAME_PK)
    REFERENCES P_FLAG_NAME(FLAG_NAME_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

CREATE TABLE P_FLAG_VAL (
  FLAG_VAL_PK INTEGER UNSIGNED NOT NULL,
  FLAG_NAME_PK INTEGER UNSIGNED NOT NULL,
  FLAG_VAL_DISP VARCHAR(255) NOT NULL,
  FLAG_VAL_STORE VARCHAR(255) NOT NULL,
  FLAG_VAL_START DATETIME NOT NULL,
  FLAG_VAL_END DATETIME NULL,
  FLAG_VAL_ACTIVE BOOL NOT NULL DEFAULT '1',
  FLAG_VAL_GLOBAL BOOL NOT NULL DEFAULT '1',
  FLAG_VAL_DEF BOOL NOT NULL DEFAULT '0',
  REC_TO_DEL BOOL NOT NULL DEFAULT '0',
  REC_IN_CORE BOOL NOT NULL DEFAULT '0',
  REC_TBP VARCHAR(255) NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  REC_UPDATE_NAME VARCHAR(255) NULL,
  REC_UPDATE_STAMP DATETIME NULL,
  PRIMARY KEY(FLAG_VAL_PK),
  UNIQUE INDEX P_FLAG_VAL_AK1(FLAG_NAME_PK, FLAG_VAL_STORE),
  INDEX P_FLAG_VAL_FK1(FLAG_NAME_PK),
  FOREIGN KEY P_FLAG_NAME_FKC2(FLAG_NAME_PK)
    REFERENCES P_FLAG_NAME(FLAG_NAME_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

CREATE TABLE P_PRODUCT_SALE (
  PRODUCT_SALE_PK INTEGER UNSIGNED NOT NULL,
  CUSTOMER_PK INTEGER UNSIGNED NOT NULL,
  PRODUCT_PK INTEGER UNSIGNED NOT NULL,
  PRODUCT_SALE_UID VARCHAR(255) NULL,
  PRODUCT_TITLE VARCHAR(255) NOT NULL,
  PRODUCT_BODY VARCHAR(255) NOT NULL,
  PRODUCT_BODY_UID VARCHAR(255) NULL,
  PRODUCT_SALE_VALUE FLOAT NOT NULL,
  PRODUCT_SALE_START DATETIME NOT NULL,
  PRODUCT_SALE_END DATETIME NOT NULL,
  PRODUCT_SALE_REVIEW DATETIME NULL,
  PRODUCT_SALE_REVIEW_LAST DATETIME NULL,
  PRODUCT_SALE_REVIEW_FREQ SMALLINT UNSIGNED NOT NULL DEFAULT '90',
  REC_TO_DEL BOOL NOT NULL DEFAULT '0',
  REC_IN_CORE BOOL NOT NULL DEFAULT '0',
  REC_TBP INTEGER UNSIGNED NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  REC_UPDATE_NAME VARCHAR(255) NULL,
  REC_UPDATE_STAMP DATETIME NULL,
  PRIMARY KEY(PRODUCT_SALE_PK),
  INDEX P_PRODUCT_SALES_FK1(CUSTOMER_PK),
  FOREIGN KEY P_CUSTOMER_FKC7(CUSTOMER_PK)
    REFERENCES P_CUSTOMER(CUSTOMER_PK)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

-- ------------------------------------------------------------
-- product notes held
-- ------------------------------------------------------------

CREATE TABLE P_PRODUCT_NOTES (
  NOTES_PK INTEGER UNSIGNED NOT NULL,
  PRODUCT_PK INTEGER UNSIGNED NOT NULL,
  NOTE TEXT NOT NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  PRIMARY KEY(NOTES_PK),
  INDEX P_PRODUCT_NOTES_FK1(PRODUCT_PK),
  FOREIGN KEY P_PRODUCT_FKC1(PRODUCT_PK)
    REFERENCES P_PRODUCT(PRODUCT_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

CREATE TABLE P_PRODUCT_FAMILY_NOTES (
  NOTES_PK INTEGER UNSIGNED NOT NULL,
  PROD_FAMILY_PK INTEGER UNSIGNED NOT NULL,
  NOTE TEXT NOT NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  PRIMARY KEY(NOTES_PK),
  INDEX P_PRODUCT_FAMILY_NOTES_FK1(PROD_FAMILY_PK),
  FOREIGN KEY P_PRODUCT_FAMILY_FKC1(PROD_FAMILY_PK)
    REFERENCES P_PRODUCT_FAMILY(PROD_FAMILY_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

-- ------------------------------------------------------------
-- notes relating to the product vendor
-- ------------------------------------------------------------

CREATE TABLE P_PRODUCT_VENDOR_NOTES (
  NOTES_PK INTEGER UNSIGNED NOT NULL,
  PRODUCT_VENDOR_PK INTEGER UNSIGNED NOT NULL,
  NOTE TEXT NOT NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  PRIMARY KEY(NOTES_PK),
  INDEX P_PRODUCT_VENDOR_NOTES_FK1(PRODUCT_VENDOR_PK),
  FOREIGN KEY P_PRODUCT_VENDOR_FKC2(PRODUCT_VENDOR_PK)
    REFERENCES P_PRODUCT_VENDOR(PRODUCT_VENDOR_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

CREATE TABLE P_SECURITY (
  SECURITY_PK INTEGER UNSIGNED NOT NULL,
  STAFF_PK INTEGER UNSIGNED NOT NULL,
  SECURITY_ELEMENT_TYPE VARCHAR(45) NOT NULL,
  SECURITY_ELEMENT_PK INTEGER UNSIGNED NOT NULL,
  SECURITY_INS BOOL NOT NULL DEFAULT '0',
  SECURITY_UPD BOOL NOT NULL DEFAULT '0',
  SECURITY_DEL BOOL NOT NULL DEFAULT '0',
  SECURITY_EXEC BOOL NOT NULL DEFAULT '0',
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  REC_UPDATE_NAME VARCHAR(255) NULL,
  REC_UPDATE_STAMP DATETIME NULL,
  PRIMARY KEY(SECURITY_PK),
  UNIQUE INDEX P_SECURITY_AK1(SECURITY_ELEMENT_TYPE, SECURITY_ELEMENT_PK),
  INDEX P_SECURITY_FK1(STAFF_PK),
  FOREIGN KEY P_STAFF_FKC4(STAFF_PK)
    REFERENCES P_STAFF(STAFF_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

CREATE TABLE P_SECURITY_PROFILE_ELEMENT (
  SECURITY_PROFILE_ELEMENT_PK INTEGER UNSIGNED NOT NULL,
  SECURITY_PROFILE_PK INTEGER UNSIGNED NOT NULL,
  SPE_OBJ_TYPE VARCHAR(255) NOT NULL,
  SPE_OBJ VARCHAR(255) NOT NULL,
  SPE_OBJ_INS BOOL NOT NULL DEFAULT '0',
  SPE_OBJ_INS_LOG BOOL NOT NULL DEFAULT '1',
  SPE_OBJ_UPD BOOL NOT NULL DEFAULT '0',
  SPE_OBJ_UPD_LOG BOOL NOT NULL DEFAULT '1',
  SPE_OBJ_DEL BOOL NOT NULL DEFAULT '0',
  SPE_OBJ_DEL_LOG BOOL NOT NULL DEFAULT '1',
  SPE_OBJ_EXEC BOOL NOT NULL DEFAULT '0',
  SPE_OBJ_EXEC_LOG BOOL NOT NULL DEFAULT '1',
  SPE_OBJ_SEL BOOL NOT NULL DEFAULT '0',
  REC_TO_DEL BOOL NOT NULL DEFAULT '0',
  REC_IN_CORE BOOL NOT NULL DEFAULT '0',
  REC_TBP VARCHAR(255) NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  REC_UPDATE_NAME VARCHAR(255) NULL,
  REC_UPDATE_STAMP DATETIME NULL,
  PRIMARY KEY(SECURITY_PROFILE_ELEMENT_PK),
  INDEX P_SECURITY_PROFILE_ELEMENT_FK1(SECURITY_PROFILE_PK),
  INDEX P_SECURITY_PROFILE_ELEMENT_AK1(SPE_OBJ_TYPE, SPE_OBJ),
  FOREIGN KEY P_SECURITY_PROFILE_FKC1(SECURITY_PROFILE_PK)
    REFERENCES P_SECURITY_PROFILE(SECURITY_PROFILE_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

CREATE TABLE P_PRODUCT_SALE_ROLES (
  PRODUCT_SALE_ROLE_PK INTEGER UNSIGNED NOT NULL,
  PRODUCT_SALE_PK INTEGER UNSIGNED NOT NULL,
  STAFF_PK INTEGER UNSIGNED NOT NULL,
  STAFF_UID VARCHAR(255) NULL,
  STAFF_SYSTEM_NAME VARCHAR(45) NULL,
  STAFF_TITLE VARCHAR(20) NULL,
  STAFF_GIVENNAME VARCHAR(255) NULL,
  STAFF_SURNAME VARCHAR(255) NULL,
  STAFF_INITIALS VARCHAR(20) NULL,
  STAFF_POSITION VARCHAR(255) NULL,
  SKILL_PK INTEGER UNSIGNED NOT NULL,
  SKILL_NAME VARCHAR(255) NULL,
  SKILL_START DATETIME NULL,
  SKILL_END DATETIME NULL,
  SKILL_BODY VARCHAR(255) NULL,
  SKILL_RATE VARCHAR(45) NULL,
  SKILL_CORE TINYINT(1) UNSIGNED NULL,
  REC_CREATE_NAME INTEGER UNSIGNED NULL,
  CREASTE_STAMP INTEGER UNSIGNED NULL,
  PRIMARY KEY(PRODUCT_SALE_ROLE_PK),
  INDEX P_PRODUCT_SALE_ROLES_FK1(PRODUCT_SALE_PK),
  FOREIGN KEY P_PRODUCT_SALE_FKC2(PRODUCT_SALE_PK)
    REFERENCES P_PRODUCT_SALE(PRODUCT_SALE_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

CREATE TABLE P_IMPORT_MANAGER_DATA (
  IMPORT_MANAGER_DATA_PK INTEGER UNSIGNED NOT NULL,
  IMPORT_MANAGER_PK INTEGER UNSIGNED NOT NULL,
  DATA_ROW_UID VARCHAR(255) NOT NULL,
  DATA_ROW_MD5 VARCHAR(45) NOT NULL,
  DATA_ROW_LAST_CHECK DATETIME NOT NULL,
  DATA_ROW_LAST_CHANGE DATETIME NOT NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  REC_UPDATE_NAME VARCHAR(255) NULL,
  REC_UPDATE_STAMP DATETIME NULL,
  PRIMARY KEY(IMPORT_MANAGER_DATA_PK),
  INDEX P_IMPORT_MANAGER_DATA_FK1(IMPORT_MANAGER_PK),
  INDEX P_IMPORT_MANAGER_DATA_AK1(IMPORT_MANAGER_PK, DATA_ROW_UID),
  FOREIGN KEY P_IMPORT_MANAGER_FKC1(IMPORT_MANAGER_PK)
    REFERENCES P_IMPORT_MANAGER(IMPORT_MANAGER_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

CREATE TABLE P_HELP_USAGE (
  HELP_USAGE_PK INTEGER UNSIGNED NOT NULL,
  HELP_PK INTEGER UNSIGNED NOT NULL,
  STAFF_PK INTEGER UNSIGNED NOT NULL,
  STAFF_SYSTEM_NAME VARCHAR(45) NOT NULL,
  HELP_USEFUL BOOL NULL,
  HELP_USER_COMMENT TEXT NULL,
  HELP_USER_COMMENT_APPROVED BOOL NOT NULL DEFAULT '0',
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  REC_UPDATE_NAME VARCHAR(255) NULL,
  REC_UPDATE_STAMP DATETIME NULL,
  PRIMARY KEY(HELP_USAGE_PK),
  INDEX P_HELP_USAGE_FKIndex1(HELP_PK),
  FOREIGN KEY P_HELP_FKC1(HELP_PK)
    REFERENCES P_HELP(HELP_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

CREATE TABLE P_COMMUNICATION_NOTES (
  NOTES_PK INTEGER UNSIGNED NOT NULL,
  COMMUNICATION_PK INTEGER UNSIGNED NOT NULL,
  NOTE TEXT NOT NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  PRIMARY KEY(NOTES_PK),
  INDEX p_COMMUNICATION_NOTES_FK1(COMMUNICATION_PK),
  FOREIGN KEY P_COMMUNICATION_FKC3(COMMUNICATION_PK)
    REFERENCES P_COMMUNICATION(COMMUNICATION_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

CREATE TABLE P_COMPANY_NOTES (
  NOTES_PK INTEGER UNSIGNED NOT NULL,
  COMPANY_PK INTEGER UNSIGNED NOT NULL,
  NOTE TEXT NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  PRIMARY KEY(NOTES_PK),
  INDEX P_COMPANY_NOTES_FK1(COMPANY_PK),
  FOREIGN KEY P_COMPANY_FKC3(COMPANY_PK)
    REFERENCES P_COMPANY(COMPANY_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

CREATE TABLE A_CUSTOMER_PROSPECT_ROLES (
  CUSTOMER_PROSPECT_ROLE_PK INTEGER UNSIGNED NOT NULL,
  PROSPECT_PK INTEGER UNSIGNED NOT NULL,
  STAFF_PK INTEGER UNSIGNED NOT NULL,
  STAFF_UID VARCHAR(255) NULL,
  STAFF_SYSTEM_NAME VARCHAR(45) NULL,
  STAFF_TITLE VARCHAR(20) NULL,
  STAFF_FIRST_NAME VARCHAR(255) NULL,
  STAFF_SURNAME VARCHAR(255) NULL,
  STAFF_INITIALS VARCHAR(20) NULL,
  STAFF_IS_ACTIVE TINYINT(1) UNSIGNED NULL,
  STAFF_START DATETIME NULL,
  STAFF_END DATETIME NULL,
  STAFF_EMAIL VARCHAR(255) NULL,
  STAFF_SEX TINYINT(1) UNSIGNED NULL,
  STAFF_POSITION VARCHAR(255) NULL,
  SKILL_NAME VARCHAR(255) NULL,
  SKILL_PK INTEGER UNSIGNED NOT NULL,
  SKILL_START DATETIME NULL,
  SKILL_END DATETIME NULL,
  SKILL_REVIEW DATETIME NULL,
  SKILL_REVIEW_FREQ VARCHAR(45) NULL,
  SKILL_BODY VARCHAR(255) NULL,
  SKILL_RATE VARCHAR(45) NULL,
  SKILL_CORE TINYINT(1) UNSIGNED NULL,
  REC_CREATE_NAME INTEGER UNSIGNED NULL,
  REC_CREATE_STAMP INTEGER UNSIGNED NULL,
  REC_ABANDON_NAME INTEGER UNSIGNED NULL,
  REC_ABANDON_STAMP INTEGER UNSIGNED NULL,
  PRIMARY KEY(CUSTOMER_PROSPECT_ROLE_PK),
  INDEX A_CUSTOMER_PROSPECT_ROLES_FKIndex1(PROSPECT_PK),
  FOREIGN KEY A_CUSTOMER_PROSPECT_FKC1(PROSPECT_PK)
    REFERENCES A_CUSTOMER_PROSPECT(PROSPECT_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

CREATE TABLE P_ALERT_RULE_ELEMENT (
  ALERT_RULE_ELEMENT_PK INTEGER UNSIGNED NOT NULL,
  ALERT_RULE_PK INTEGER UNSIGNED NOT NULL,
  AR_ELEMENT_START DATETIME NOT NULL,
  AR_ELEMENT_END DATETIME NULL,
  AR_ELEMENT_ACTIVE BOOL NOT NULL DEFAULT '1',
  AR_ELEMENT_TYPE VARCHAR(45) NULL,
  AR_ELEMENT_INT SMALLINT UNSIGNED NULL,
  AR_ELEMENT_PRIORITY SMALLINT UNSIGNED NOT NULL DEFAULT '1',
  REC_TO_DEL BOOL NOT NULL DEFAULT '0',
  REC_IN_CORE BOOL NOT NULL DEFAULT '0',
  REC_TBP VARCHAR(255) NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  REC_UPDATE_NAME VARCHAR(255) NULL,
  REC_UPDATE_STAMP DATETIME NULL,
  PRIMARY KEY(ALERT_RULE_ELEMENT_PK),
  INDEX P_ALERT_RULE_ELEMENT_FK1(ALERT_RULE_PK),
  FOREIGN KEY P_ALERT_RULE_FKC1(ALERT_RULE_PK)
    REFERENCES P_ALERT_RULE(ALERT_RULE_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

-- ------------------------------------------------------------
-- Rule system used to distribute commissions
-- ------------------------------------------------------------

CREATE TABLE P_COMMISSION_RULE (
  COMMISSION_RULE_PK INTEGER UNSIGNED NOT NULL,
  PRODUCT_PK INTEGER UNSIGNED NOT NULL,
  CR_START DATETIME NOT NULL,
  CR_END DATETIME NULL,
  CR_REVIEW DATETIME NULL,
  CR_REVIEW_LAST DATETIME NULL,
  CR_REVIEW_FREQ SMALLINT UNSIGNED NOT NULL DEFAULT '90',
  CR_ACTIVE BOOL NOT NULL DEFAULT '1',
  CR_CALC_AT VARCHAR(45) NULL,
  CR_ACCRUE_AT VARCHAR(45) NULL,
  REC_TO_DEL BOOL NOT NULL DEFAULT '0',
  REC_IN_CORE BOOL NOT NULL DEFAULT '0',
  REC_TBP VARCHAR(255) NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  REC_UPDATE_NAME VARCHAR(255) NULL,
  REC_UPDATE_STAMP DATETIME NULL,
  PRIMARY KEY(COMMISSION_RULE_PK),
  INDEX P_COMMISSION_RULES_FK1(PRODUCT_PK),
  FOREIGN KEY P_PRODUCT_FKC2(PRODUCT_PK)
    REFERENCES P_PRODUCT(PRODUCT_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

CREATE TABLE P_CUSTOMER_NOTES (
  NOTES_PK INTEGER UNSIGNED NOT NULL,
  CUSTOMER_PK INTEGER UNSIGNED NOT NULL,
  NOTE TEXT NOT NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  PRIMARY KEY(NOTES_PK),
  INDEX P_CUSTOMER_NOTES_FK1(CUSTOMER_PK),
  FOREIGN KEY P_CUSTOMER_FKC6(CUSTOMER_PK)
    REFERENCES P_CUSTOMER(CUSTOMER_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

CREATE TABLE A_CUSTOMER_PROSPECT_FLAG (
  CUSTOMER_PROSPECT_FLAG_PK INTEGER UNSIGNED NOT NULL,
  PROSPECT_PK INTEGER UNSIGNED NOT NULL,
  FLAG_NAME_PK INTEGER UNSIGNED NULL,
  FLAG_NAME_DISP VARCHAR(255) NULL,
  FLAG_NAME_STORE VARCHAR(255) NULL,
  FLAG_VAL_PK INTEGER UNSIGNED NULL,
  FLAG_VAL_DISP VARCHAR(255) NULL,
  FLAG_VAL_STORE VARCHAR(255) NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  REC_UPDATE_NAME VARCHAR(255) NULL,
  REC_UPDATE_STAMP DATETIME NULL,
  PRIMARY KEY(CUSTOMER_PROSPECT_FLAG_PK),
  INDEX A_CUSTOMER_PROSPECT_FLAG_FKIndex1(PROSPECT_PK),
  FOREIGN KEY AS_CUSTOMER_PROSPECT_FKC3(PROSPECT_PK)
    REFERENCES A_CUSTOMER_PROSPECT(PROSPECT_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

CREATE TABLE P_CUSTOMER_HEALTH (
  CUSTOMER_HEALTH_PK INTEGER UNSIGNED NOT NULL,
  CUSTOMER_PK INTEGER UNSIGNED NOT NULL,
  CUST_HEALTH_COND VARCHAR(2000) NOT NULL,
  CUST_HEALTH_COND_DESC TEXT NOT NULL,
  CUST_HEALTH_COND_START DATETIME NOT NULL,
  CUST_HEALTH_COND_END DATETIME NULL,
  CUST_HEALTH_COND_PROG VARCHAR(255) NOT NULL,
  CUST_HEALTH_COND_CONF BOOL NOT NULL DEFAULT '0',
  CUST_HEALTH_COND_CONF_BODY VARCHAR(2000) NULL,
  CUST_HEALTH_COND_REVIEW DATETIME NULL,
  CUST_HEALTH_COND_REVIEW_LAST DATETIME NULL,
  CUST_HEALTH_COND_REVIEW_FREQ SMALLINT UNSIGNED NOT NULL DEFAULT '90',
  CUST_HEALTH_RISK VARCHAR(20) NOT NULL,
  REC_TO_DEL BOOL NOT NULL DEFAULT '0',
  REC_IN_CORE BOOL NOT NULL DEFAULT '0',
  REC_TBP VARCHAR(255) NULL,
  REC_CREATE_NAME INTEGER UNSIGNED NULL,
  REC_CREATE_STAMP INTEGER UNSIGNED NULL,
  REC_UPDATE_NAME INTEGER UNSIGNED NULL,
  REC_UPDATE_STAMP INTEGER UNSIGNED NULL,
  PRIMARY KEY(CUSTOMER_HEALTH_PK),
  INDEX P_CUSTOMER_HEALTH_FKIndex1(CUSTOMER_PK),
  FOREIGN KEY P_CUSTOMER_FKC8(CUSTOMER_PK)
    REFERENCES P_CUSTOMER(CUSTOMER_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

CREATE TABLE P_CONTACT_NOTES (
  NOTES_PK INTEGER UNSIGNED NOT NULL,
  CONTACT_PK INTEGER UNSIGNED NOT NULL,
  NOTE TEXT NOT NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  PRIMARY KEY(NOTES_PK),
  INDEX P_CONTACT_NOTES_FK1(CONTACT_PK),
  FOREIGN KEY P_CONTACT_FKC3(CONTACT_PK)
    REFERENCES P_CONTACT(CONTACT_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

CREATE TABLE A_CUSTOMER_PROSPECT_NOTES (
  NOTES_PK INTEGER UNSIGNED NOT NULL,
  PROSPECT_PK INTEGER UNSIGNED NOT NULL,
  NOTE TEXT NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  REC_ABANDON_NAME VARCHAR(255) NULL,
  REC_ABANDON_STAMP DATETIME NULL,
  PRIMARY KEY(NOTES_PK),
  INDEX A_CUSTOMER_PROSPECT_NOTES_FKIndex1(PROSPECT_PK),
  FOREIGN KEY A_CUSTOMER_PROSPECT_FKC2(PROSPECT_PK)
    REFERENCES A_CUSTOMER_PROSPECT(PROSPECT_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

CREATE TABLE L_HELP_TRIGGER (
  HELP_PK INTEGER UNSIGNED NOT NULL,
  HELP_TRIGGER_PK INTEGER UNSIGNED NOT NULL,
  REC_TO_DEL BOOL NOT NULL DEFAULT '0',
  REC_IN_CORE BOOL NOT NULL DEFAULT '0',
  REC_TBP VARCHAR(255) NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  PRIMARY KEY(HELP_PK, HELP_TRIGGER_PK),
  INDEX L_HELP_TRIGGER_FKIndex1(HELP_PK),
  INDEX L_HELP_TRIGGER_FKIndex2(HELP_TRIGGER_PK),
  FOREIGN KEY P_HELP_FKC2(HELP_PK)
    REFERENCES P_HELP(HELP_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION,
  FOREIGN KEY P_HELP_TRIGGER_FKC1(HELP_TRIGGER_PK)
    REFERENCES P_HELP_TRIGGER(HELP_TRIGGER_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

CREATE TABLE L_PRODUCT_FAMILY_CONTACT (
  PROD_FAMILY_PK INTEGER UNSIGNED NOT NULL,
  CONTACT_PK INTEGER UNSIGNED NOT NULL,
  REC_TO_DEL BOOL NOT NULL DEFAULT '0',
  REC_IN_CORE BOOL NOT NULL DEFAULT '0',
  REC_TBP VARCHAR(255) NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  PRIMARY KEY(PROD_FAMILY_PK, CONTACT_PK),
  INDEX L_PRODUCT_FAMILY_CONTACT_FK1(PROD_FAMILY_PK),
  INDEX L_PRODUCT_FAMILY_CONTACT_FK2(CONTACT_PK),
  FOREIGN KEY P_PRODUCT_FAMILY_FKC3(PROD_FAMILY_PK)
    REFERENCES P_PRODUCT_FAMILY(PROD_FAMILY_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION,
  FOREIGN KEY P_CONTACT_FKC4(CONTACT_PK)
    REFERENCES P_CONTACT(CONTACT_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

CREATE TABLE L_PRODUCT_FAMILY (
  PROD_FAMILY_PK INTEGER UNSIGNED NOT NULL,
  PRODUCT_PK INTEGER UNSIGNED NOT NULL,
  REC_TO_DEL BOOL NOT NULL DEFAULT '0',
  REC_IN_CORE BOOL NOT NULL DEFAULT '0',
  REC_TBP VARCHAR(255) NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  PRIMARY KEY(PROD_FAMILY_PK, PRODUCT_PK),
  INDEX L_PRODUCT_FAMILY_FK1(PROD_FAMILY_PK),
  INDEX L_PRODUCT_FAMILY_FK2(PRODUCT_PK),
  FOREIGN KEY P_PRODUCT_FAMILY_FKC2(PROD_FAMILY_PK)
    REFERENCES P_PRODUCT_FAMILY(PROD_FAMILY_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION,
  FOREIGN KEY P_PRODUCT_FAMILY_FKC1(PRODUCT_PK)
    REFERENCES P_PRODUCT(PRODUCT_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

CREATE TABLE L_PRODUCT_CONTACT (
  PRODUCT_PK INTEGER UNSIGNED NOT NULL,
  CONTACT_PK INTEGER UNSIGNED NOT NULL,
  REC_TO_DEL BOOL NOT NULL DEFAULT '0',
  REC_IN_CORE BOOL NOT NULL DEFAULT '0',
  REC_TBP VARCHAR(255) NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  PRIMARY KEY(PRODUCT_PK, CONTACT_PK),
  INDEX L_PRODUCT_CONTACT_FK1(PRODUCT_PK),
  INDEX L_PRODUCT_CONTACT_FK2(CONTACT_PK),
  FOREIGN KEY P_PRODUCT_FKC5(PRODUCT_PK)
    REFERENCES P_PRODUCT(PRODUCT_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION,
  FOREIGN KEY P_CONTACT_FKC5(CONTACT_PK)
    REFERENCES P_CONTACT(CONTACT_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

CREATE TABLE L_COMPANY_FLAG (
  COMPANY_PK INTEGER UNSIGNED NOT NULL,
  FLAG_PK INTEGER UNSIGNED NOT NULL,
  REC_TO_DEL BOOL NOT NULL DEFAULT '0',
  REC_IN_CORE BOOL NOT NULL DEFAULT '0',
  REC_TBP VARCHAR(255) NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  PRIMARY KEY(COMPANY_PK, FLAG_PK),
  INDEX L_COMPANY_FLAG_FK1(COMPANY_PK),
  INDEX L_COMPANY_FLAG_FK2(FLAG_PK),
  FOREIGN KEY P_COMPANY_FKC4(COMPANY_PK)
    REFERENCES P_COMPANY(COMPANY_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION,
  FOREIGN KEY P_FLAG_FKC9(FLAG_PK)
    REFERENCES P_FLAG(FLAG_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

CREATE TABLE L_COMPANY_CONTACT (
  COMPANY_PK INTEGER UNSIGNED NOT NULL,
  CONTACT_PK INTEGER UNSIGNED NOT NULL,
  REC_TO_DEL BOOL NOT NULL DEFAULT '0',
  REC_IN_CORE BOOL NOT NULL DEFAULT '0',
  REC_TBP VARCHAR(255) NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  PRIMARY KEY(COMPANY_PK, CONTACT_PK),
  INDEX L_COMPANY_CONTACT_FK1(COMPANY_PK),
  INDEX L_COMPANY_CONTACT_FK2(CONTACT_PK),
  FOREIGN KEY P_CONTACT_FKC10(CONTACT_PK)
    REFERENCES P_CONTACT(CONTACT_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION,
  FOREIGN KEY P_COMPANY_FKC2(COMPANY_PK)
    REFERENCES P_COMPANY(COMPANY_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

CREATE TABLE L_COMMUNICATION_FLAG (
  COMMUNICATION_PK INTEGER UNSIGNED NOT NULL,
  FLAG_PK INTEGER UNSIGNED NOT NULL,
  REC_TO_DEL BOOL NOT NULL DEFAULT '0',
  REC_IN_CORE BOOL NOT NULL DEFAULT '0',
  REC_TBP VARCHAR(255) NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  PRIMARY KEY(COMMUNICATION_PK, FLAG_PK),
  INDEX L_COMMUNICATION_FLAG_FK1(COMMUNICATION_PK),
  INDEX L_COMMUNICATION_FLAG_FK2(FLAG_PK),
  FOREIGN KEY P_COMMUNICATION_FKC5(COMMUNICATION_PK)
    REFERENCES P_COMMUNICATION(COMMUNICATION_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION,
  FOREIGN KEY P_FLAG_FKC8(FLAG_PK)
    REFERENCES P_FLAG(FLAG_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

CREATE TABLE L_CUSTOMER_FLAG (
  CUSTOMER_PK INTEGER UNSIGNED NOT NULL,
  FLAG_PK INTEGER UNSIGNED NOT NULL,
  REC_TO_DEL BOOL NOT NULL DEFAULT '0',
  REC_IN_CORE BOOL NOT NULL DEFAULT '0',
  REC_TBP VARCHAR(255) NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  PRIMARY KEY(CUSTOMER_PK, FLAG_PK),
  INDEX L_CUSTOMER_FLAG_FK1(CUSTOMER_PK),
  INDEX L_CUSTOMER_FLAG_FK2(FLAG_PK),
  FOREIGN KEY P_CUSTOMER_FKC9(CUSTOMER_PK)
    REFERENCES P_CUSTOMER(CUSTOMER_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION,
  FOREIGN KEY P_FLAG_FKC5(FLAG_PK)
    REFERENCES P_FLAG(FLAG_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

-- ------------------------------------------------------------
-- Contact Methods assigned to a customer
-- ------------------------------------------------------------

CREATE TABLE L_CUSTOMER_CONTACT (
  CUSTOMER_PK INTEGER UNSIGNED NOT NULL,
  CONTACT_PK INTEGER UNSIGNED NOT NULL,
  REC_TO_DEL BOOL NOT NULL DEFAULT '0',
  REC_IN_CORE BOOL NOT NULL DEFAULT '0',
  REC_TBP VARCHAR(255) NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  PRIMARY KEY(CUSTOMER_PK, CONTACT_PK),
  INDEX L_CUSTOMER_CONTACT_FK1(CUSTOMER_PK),
  INDEX L_CUSTOMER_CONTACT_FKIndex2(CONTACT_PK),
  FOREIGN KEY P_CUSTOMER_FKC5(CUSTOMER_PK)
    REFERENCES P_CUSTOMER(CUSTOMER_PK)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION,
  FOREIGN KEY P_CONTACT_FKC6(CONTACT_PK)
    REFERENCES P_CONTACT(CONTACT_PK)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

CREATE TABLE L_PRODUCT_FLAG (
  PRODUCT_PK INTEGER UNSIGNED NOT NULL,
  FLAG_PK INTEGER UNSIGNED NOT NULL,
  REC_TO_DEL BOOL NOT NULL DEFAULT '0',
  REC_IN_CORE BOOL NOT NULL DEFAULT '0',
  REC_TBP VARCHAR(255) NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  PRIMARY KEY(PRODUCT_PK, FLAG_PK),
  INDEX L_PRODUCT_FLAG_FK1(PRODUCT_PK),
  INDEX L_PRODUCT_FLAG_FK2(FLAG_PK),
  FOREIGN KEY P_PRODUCT_FKC8(PRODUCT_PK)
    REFERENCES P_PRODUCT(PRODUCT_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION,
  FOREIGN KEY P_FLAG_FKC4(FLAG_PK)
    REFERENCES P_FLAG(FLAG_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

CREATE TABLE L_STAFF_SECURITY_PROFILE (
  STAFF_PK INTEGER UNSIGNED NOT NULL,
  SECURITY_PROFILE_PK INTEGER UNSIGNED NOT NULL,
  REC_TO_DEL BOOL NOT NULL DEFAULT '0',
  REC_IN_CORE BOOL NOT NULL DEFAULT '0',
  REC_TBP VARCHAR(255) NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  PRIMARY KEY(STAFF_PK, SECURITY_PROFILE_PK),
  INDEX L_STAFF_SECURITY_PROFILE_FK1(STAFF_PK),
  INDEX L_STAFF_SECURITY_PROFILE_FK2(SECURITY_PROFILE_PK),
  FOREIGN KEY P_STAFF_FKC10(STAFF_PK)
    REFERENCES P_STAFF(STAFF_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION,
  FOREIGN KEY P_SECURITY_PROFILE_FKC1(SECURITY_PROFILE_PK)
    REFERENCES P_SECURITY_PROFILE(SECURITY_PROFILE_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

CREATE TABLE L_STAFF_FLAG (
  STAFF_PK INTEGER UNSIGNED NOT NULL,
  FLAG_PK INTEGER UNSIGNED NOT NULL,
  REC_TO_DEL BOOL NOT NULL DEFAULT '0',
  REC_IN_CORE BOOL NOT NULL DEFAULT '0',
  REC_TBP VARCHAR(255) NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  PRIMARY KEY(STAFF_PK, FLAG_PK),
  INDEX L_STAFF_FLAG_FK1(STAFF_PK),
  INDEX L_STAFF_FLAG_FK2(FLAG_PK),
  FOREIGN KEY P_STAFF_FKC9(STAFF_PK)
    REFERENCES P_STAFF(STAFF_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION,
  FOREIGN KEY P_FLAG_FLC2(FLAG_PK)
    REFERENCES P_FLAG(FLAG_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

-- ------------------------------------------------------------
-- Contact methods assigned to staff
-- ------------------------------------------------------------

CREATE TABLE L_STAFF_CONTACT (
  STAFF_PK INTEGER UNSIGNED NOT NULL,
  CONTACT_PK INTEGER UNSIGNED NOT NULL,
  REC_TO_DEL BOOL NOT NULL DEFAULT '0',
  REC_IN_CORE BOOL NOT NULL DEFAULT '0',
  REC_TBP VARCHAR(255) NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  PRIMARY KEY(STAFF_PK, CONTACT_PK),
  INDEX L_STAFF_CONTACT_FK1(STAFF_PK),
  INDEX L_STAFF_CONTACT_FK2(CONTACT_PK),
  FOREIGN KEY P_STAFF_FKC6(STAFF_PK)
    REFERENCES P_STAFF(STAFF_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION,
  FOREIGN KEY P_CONTACT_FKC2(CONTACT_PK)
    REFERENCES P_CONTACT(CONTACT_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

CREATE TABLE P_CUSTOMER_PROSPECT (
  PROSPECT_PK INTEGER UNSIGNED NOT NULL,
  PRODUCT_PK INTEGER UNSIGNED NOT NULL,
  CUSTOMER_PK INTEGER UNSIGNED NOT NULL,
  PRODUCT_TITLE VARCHAR(255) NOT NULL,
  PRODUCT_START DATETIME NOT NULL,
  PRODUCT_END DATETIME NULL,
  PRODUCT_REVIEW DATETIME NULL,
  PRODUCT_REVIEW_LAST DATETIME NULL,
  PRODUCT_REVIEW_FREQ SMALLINT UNSIGNED NOT NULL DEFAULT '90',
  PROSPECT_VALUE FLOAT NULL,
  PROSPECT_FAIL_DATE DATETIME NULL,
  PROSPECT_FAIL_OWNER VARCHAR(255) NULL,
  PROSPECT_NOTIFY_DONE BOOL NOT NULL DEFAULT '0',
  REC_TO_DEL BOOL NOT NULL DEFAULT '0',
  REC_IN_CORE BOOL NOT NULL DEFAULT '0',
  REC_TBP VARCHAR(255) NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  REC_UPDATE_NAME VARCHAR(255) NULL,
  REC_UPDATE_STAMP DATETIME NULL,
  PRIMARY KEY(PROSPECT_PK),
  INDEX P_CUSTOMER_PROSPECT_FK1(CUSTOMER_PK),
  INDEX P_CUSTOMER_PROSPECT_FK2(PRODUCT_PK),
  INDEX P_CUSTOMER_PROSPECT_IE1(PRODUCT_TITLE),
  FOREIGN KEY P_CUSTOMER_FKC4(CUSTOMER_PK)
    REFERENCES P_CUSTOMER(CUSTOMER_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION,
  FOREIGN KEY P_PRODUCT_FKC6(PRODUCT_PK)
    REFERENCES P_PRODUCT(PRODUCT_PK)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

CREATE TABLE P_EMPLOYMENT (
  EMPLOYMENT_PK INTEGER UNSIGNED NOT NULL,
  CUSTOMER_PK INTEGER UNSIGNED NOT NULL,
  COMPANY_PK INTEGER UNSIGNED NOT NULL,
  EMPLOYMENT_UID VARCHAR(255) NULL,
  COMPANY_OWNER BOOL NOT NULL DEFAULT '0',
  EMPLOYMENT_TITLE VARCHAR(255) NULL,
  EMPLOYMENT_TYPE INTEGER UNSIGNED NULL,
  EMPLOYMENT_START DATETIME NOT NULL,
  EMPLOYMENT_END DATETIME NULL,
  EMPLOYMENT_STATUS VARCHAR(45) NULL,
  EMPLOYMENT_GROSS INTEGER UNSIGNED NULL,
  EMPLOYMENT_REVIEW DATETIME NULL,
  EMPLOYMENT_REVIEW_LAST DATETIME NULL,
  EMPLOYMENT_REVIEW_FREQ SMALLINT UNSIGNED NOT NULL DEFAULT '365',
  REC_IMPORT BOOL NOT NULL DEFAULT '0',
  REC_LOCAL BOOL NOT NULL DEFAULT '0',
  REC_TO_DEL BOOL NOT NULL DEFAULT '0',
  REC_IN_CORE BOOL NOT NULL DEFAULT '0',
  REC_TBP VARCHAR(255) NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  REC_UPDATE_NAME VARCHAR(255) NULL,
  REC_UPDATE_STAMP DATETIME NULL,
  PRIMARY KEY(EMPLOYMENT_PK),
  UNIQUE INDEX P_EMPLOYMENT_AK1(EMPLOYMENT_UID),
  INDEX P_EMPLOYMENT_FK1(COMPANY_PK),
  INDEX P_EMPLOYMENT_FK2(CUSTOMER_PK),
  FOREIGN KEY P_COMPANY_FKC1(COMPANY_PK)
    REFERENCES P_COMPANY(COMPANY_PK)
      ON DELETE RESTRICT
      ON UPDATE NO ACTION,
  FOREIGN KEY P_CUSTOMER_FKC11(CUSTOMER_PK)
    REFERENCES P_CUSTOMER(CUSTOMER_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

CREATE TABLE L_STAFF_SKILL (
  STAFF_PK INTEGER UNSIGNED NOT NULL,
  SKILL_PK INTEGER UNSIGNED NOT NULL,
  REC_TO_DEL BOOL NOT NULL DEFAULT '0',
  REC_IN_CORE BOOL NOT NULL DEFAULT '0',
  REC_TBP VARCHAR(255) NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  PRIMARY KEY(STAFF_PK, SKILL_PK),
  INDEX L_STAFF_SKILL_FK1(STAFF_PK),
  INDEX L_STAFF_SKILL_FK2(SKILL_PK),
  FOREIGN KEY P_STAFF_FKC3(STAFF_PK)
    REFERENCES P_STAFF(STAFF_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION,
  FOREIGN KEY P_SKILL_FKC4(SKILL_PK)
    REFERENCES P_SKILL(SKILL_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

CREATE TABLE L_SKILL_FLAG (
  SKILL_PK INTEGER UNSIGNED NOT NULL,
  FLAG_PK INTEGER UNSIGNED NOT NULL,
  REC_TO_DEL BOOL NOT NULL DEFAULT '0',
  REC_IN_CORE BOOL NOT NULL DEFAULT '0',
  REC_TBP VARCHAR(255) NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  PRIMARY KEY(SKILL_PK, FLAG_PK),
  INDEX L_SKILL_FLAG_FK1(SKILL_PK),
  INDEX L_SKILL_FLAG_FK2(FLAG_PK),
  FOREIGN KEY P_SKILL_FKC6(SKILL_PK)
    REFERENCES P_SKILL(SKILL_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION,
  FOREIGN KEY P_FLAG_FKC7(FLAG_PK)
    REFERENCES P_FLAG(FLAG_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

-- ------------------------------------------------------------
-- linkage of vendors and the products they supply
-- ------------------------------------------------------------

CREATE TABLE L_PRODUCT_VENDOR (
  PRODUCT_PK INTEGER UNSIGNED NOT NULL,
  PRODUCT_VENDOR_PK INTEGER UNSIGNED NOT NULL,
  REC_TO_DEL BOOL NOT NULL DEFAULT '0',
  REC_IN_CORE BOOL NOT NULL DEFAULT '0',
  REC_TBP VARCHAR(255) NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  PRIMARY KEY(PRODUCT_PK, PRODUCT_VENDOR_PK),
  INDEX L_PRODUCT_VENDOR_FK1(PRODUCT_PK),
  INDEX L_PRODUCT_VENDOR_FK2(PRODUCT_VENDOR_PK),
  FOREIGN KEY P_PRODUCT_FKC7(PRODUCT_PK)
    REFERENCES P_PRODUCT(PRODUCT_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION,
  FOREIGN KEY P_PRODUCT_VENDOR_FKC1(PRODUCT_VENDOR_PK)
    REFERENCES P_PRODUCT_VENDOR(PRODUCT_VENDOR_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

CREATE TABLE L_PRODUCT_SALE_FLAG (
  PRODUCT_SALE_PK INTEGER UNSIGNED NOT NULL,
  FLAG_PK INTEGER UNSIGNED NOT NULL,
  REC_TO_DEL BOOL NOT NULL DEFAULT '0',
  REC_IN_CORE BOOL NOT NULL DEFAULT '0',
  REC_TBP VARCHAR(255) NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  PRIMARY KEY(PRODUCT_SALE_PK, FLAG_PK),
  INDEX L_PRODUCT_SALE_FLAG_FK1(PRODUCT_SALE_PK),
  INDEX L_PRODUCT_SALE_FLAG_FK2(FLAG_PK),
  FOREIGN KEY P_PRODUCT_SALE_FKC5(PRODUCT_SALE_PK)
    REFERENCES P_PRODUCT_SALE(PRODUCT_SALE_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION,
  FOREIGN KEY P_FLAG_FKC11(FLAG_PK)
    REFERENCES P_FLAG(FLAG_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

CREATE TABLE L_PRODUCT_SKILL (
  PRODUCT_PK INTEGER UNSIGNED NOT NULL,
  SKILL_PK INTEGER UNSIGNED NOT NULL,
  REC_TO_DEL BOOL NOT NULL DEFAULT '0',
  REC_IN_CORE BOOL NOT NULL DEFAULT '0',
  REC_TBP VARCHAR(255) NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  PRIMARY KEY(PRODUCT_PK, SKILL_PK),
  INDEX L_SKILL_PRODUCT_FK1(SKILL_PK),
  INDEX L_SKILL_PRODUCT_FK2(PRODUCT_PK),
  FOREIGN KEY P_SKILL_FKC2(SKILL_PK)
    REFERENCES P_SKILL(SKILL_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION,
  FOREIGN KEY P_PRODUCT_FKC4(PRODUCT_PK)
    REFERENCES P_PRODUCT(PRODUCT_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

CREATE TABLE L_PRODUCT_VENDOR_FLAG (
  PRODUCT_VENDOR_PK INTEGER UNSIGNED NOT NULL,
  FLAG_PK INTEGER UNSIGNED NOT NULL,
  REC_TO_DEL BOOL NOT NULL DEFAULT '0',
  REC_IN_CORE BOOL NOT NULL DEFAULT '0',
  REC_TBP VARCHAR(255) NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  PRIMARY KEY(PRODUCT_VENDOR_PK, FLAG_PK),
  INDEX L_PRODUCT_VENDOR_FLAG_FK1(PRODUCT_VENDOR_PK),
  INDEX L_PRODUCT_VENDOR_FLAG_FK2(FLAG_PK),
  FOREIGN KEY P_PRODUCT_VENDOR_FKC4(PRODUCT_VENDOR_PK)
    REFERENCES P_PRODUCT_VENDOR(PRODUCT_VENDOR_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION,
  FOREIGN KEY P_FLAG_FKC3(FLAG_PK)
    REFERENCES P_FLAG(FLAG_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

-- ------------------------------------------------------------
-- list of product vendor contact information
-- ------------------------------------------------------------

CREATE TABLE L_PRODUCT_VENDOR_CONTACT (
  PRODUCT_VENDOR_PK INTEGER UNSIGNED NOT NULL,
  CONTACT_PK INTEGER UNSIGNED NOT NULL,
  REC_TO_DEL BOOL NOT NULL DEFAULT '0',
  REC_IN_CORE BOOL NOT NULL DEFAULT '0',
  REC_TBP VARCHAR(255) NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  PRIMARY KEY(PRODUCT_VENDOR_PK, CONTACT_PK),
  INDEX L_PRODUCT_VENDOR_CONTACT_FK1(PRODUCT_VENDOR_PK),
  INDEX L_PRODUCT_VENDOR_CONTACT_FK2(CONTACT_PK),
  FOREIGN KEY P_PRODUCT_VENDOR_FKC3(PRODUCT_VENDOR_PK)
    REFERENCES P_PRODUCT_VENDOR(PRODUCT_VENDOR_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION,
  FOREIGN KEY P_CONTACT_FKC7(CONTACT_PK)
    REFERENCES P_CONTACT(CONTACT_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

CREATE TABLE P_COMMISSION_RULE_ELEMENT (
  COMMISSION_RULE_ELEMENT_PK INTEGER UNSIGNED NOT NULL,
  COMMISSION_RULE_PK INTEGER UNSIGNED NOT NULL,
  STAFF_PK INTEGER UNSIGNED NOT NULL DEFAULT '0',
  SKILL_PK INTEGER UNSIGNED NOT NULL DEFAULT '0',
  CRE_START DATETIME NOT NULL,
  CRE_END DATETIME NULL,
  CRE_REVIEW DATETIME NULL,
  CRE_REVIEW_LAST DATETIME NULL,
  CRE_REVIEW_FREQ SMALLINT UNSIGNED NOT NULL DEFAULT '90',
  CRE_ACTIVE BOOL NOT NULL DEFAULT '1',
  CRE_UNITS SMALLINT UNSIGNED NOT NULL DEFAULT '1',
  CRE_ACCRUE_AT VARCHAR(20) NOT NULL,
  REC_TO_DEL BOOL NOT NULL DEFAULT '0',
  REC_IN_CORE BOOL NOT NULL DEFAULT '0',
  REC_TBP VARCHAR(255) NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  REC_UPDATE_NAME VARCHAR(255) NULL,
  REC_UPDATE_STAMP DATETIME NULL,
  PRIMARY KEY(COMMISSION_RULE_ELEMENT_PK),
  INDEX P_COMISSION_RULES_ELEMENT_FK1(SKILL_PK),
  INDEX P_COMISSION_RULES_ELEMENT_FK2(STAFF_PK),
  INDEX P_COMMISSION_RULE_ELEMENT_FK3(COMMISSION_RULE_PK),
  FOREIGN KEY P_SKILL_FKC1(SKILL_PK)
    REFERENCES P_SKILL(SKILL_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION,
  FOREIGN KEY P_STAFF_FKC1(STAFF_PK)
    REFERENCES P_STAFF(STAFF_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION,
  FOREIGN KEY P_COMMISSION_RULE_FKC3(COMMISSION_RULE_PK)
    REFERENCES P_COMMISSION_RULE(COMMISSION_RULE_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

CREATE TABLE L_CUSTOMER_PROSPECT_ROLES (
  STAFF_PK INTEGER UNSIGNED NOT NULL,
  SKILL_PK INTEGER UNSIGNED NOT NULL,
  PROSPECT_PK INTEGER UNSIGNED NOT NULL,
  REC_TO_DEL BOOL NOT NULL DEFAULT '0',
  REC_IN_CORE BOOL NOT NULL DEFAULT '0',
  REC_TBP VARCHAR(255) NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  PRIMARY KEY(STAFF_PK, SKILL_PK, PROSPECT_PK),
  INDEX L_CUSTOMER_PROSPECT_ROLES_FK1(STAFF_PK),
  INDEX L_CUSTOMER_PROSPECT_ROLES_FK3(SKILL_PK),
  INDEX L_CUSTOMER_PROSPECT_ROLES_FKIndex3(PROSPECT_PK),
  FOREIGN KEY P_STAFF_FKC5(STAFF_PK)
    REFERENCES P_STAFF(STAFF_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION,
  FOREIGN KEY P_CUSTOMER_PROSPECT_FKC2(PROSPECT_PK)
    REFERENCES P_CUSTOMER_PROSPECT(PROSPECT_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION,
  FOREIGN KEY P_SKILL_FKC5(SKILL_PK)
    REFERENCES P_SKILL(SKILL_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

CREATE TABLE P_ALERT (
  ALERT_PK INTEGER UNSIGNED NOT NULL,
  CONTACT_PK INTEGER UNSIGNED NOT NULL,
  COMMUNICATION_PK INTEGER UNSIGNED NOT NULL,
  ALERT_RULE_PK INTEGER UNSIGNED NOT NULL,
  CUSTOMER_PK INTEGER UNSIGNED NOT NULL,
  STAFF_PK INTEGER UNSIGNED NOT NULL,
  ALERT_SOURCE VARCHAR(255) NOT NULL,
  ALERT_SOURCE_PK INTEGER UNSIGNED NOT NULL,
  ALERT_START DATETIME NOT NULL,
  ALERT_END DATETIME NULL,
  ALERT_FREQ INTEGER UNSIGNED NOT NULL DEFAULT '90',
  ALERT_FIRE DATETIME NULL,
  ALERT_FIRE_LAST DATETIME NULL,
  ALERT_FIRE_COUNT INTEGER UNSIGNED NOT NULL DEFAULT '0',
  ALERT_ACTIVE BOOL NOT NULL DEFAULT '1',
  REC_TO_DEL BOOL NOT NULL DEFAULT '0',
  REC_IN_CORE BOOL NOT NULL DEFAULT '0',
  REC_TBP VARCHAR(255) NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  REC_UPDATE_NAME VARCHAR(255) NULL,
  REC_UPDATE_STAMP DATETIME NULL,
  PRIMARY KEY(ALERT_PK),
  INDEX P_ALERT_FK1(STAFF_PK),
  INDEX P_ALERT_FK2(COMMUNICATION_PK),
  INDEX P_ALERT_FK4(CUSTOMER_PK),
  INDEX P_ALERT_FK5(ALERT_RULE_PK),
  INDEX P_ALERT_FKIndex5(CONTACT_PK),
  FOREIGN KEY P_STAFF_FKC8(STAFF_PK)
    REFERENCES P_STAFF(STAFF_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION,
  FOREIGN KEY P_COMMUNICATION_FKC2(COMMUNICATION_PK)
    REFERENCES P_COMMUNICATION(COMMUNICATION_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION,
  FOREIGN KEY P_CONTACT_FKC9(CONTACT_PK)
    REFERENCES P_CONTACT(CONTACT_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION,
  FOREIGN KEY P_CUSTOMER_FKC2(CUSTOMER_PK)
    REFERENCES P_CUSTOMER(CUSTOMER_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION,
  FOREIGN KEY P_ALERT_RULE_FKC2(ALERT_RULE_PK)
    REFERENCES P_ALERT_RULE(ALERT_RULE_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

-- ------------------------------------------------------------
-- Holds store of commissions assigned and in progress
-- ------------------------------------------------------------

CREATE TABLE P_COMMISSION (
  COMMISSION_PK INTEGER UNSIGNED NOT NULL,
  COMMISSION_RULE_PK INTEGER UNSIGNED NOT NULL,
  PROSPECT_PK INTEGER UNSIGNED NOT NULL,
  PRODUCT_PK INTEGER UNSIGNED NOT NULL,
  STAFF_PK INTEGER UNSIGNED NOT NULL,
  CUSTOMER_PK INTEGER UNSIGNED NOT NULL,
  COMMISSION_TYPE VARCHAR(45) NOT NULL,
  COMMISSION_DATE DATETIME NOT NULL,
  COMMISSION_VALUE_DATE DATETIME NULL,
  COMMISSION_VALUE FLOAT NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  REC_UPDATE_NAME VARCHAR(255) NULL,
  REC_UPDATE_STAMP DATETIME NULL,
  PRIMARY KEY(COMMISSION_PK),
  INDEX P_COMISSION_FK1(CUSTOMER_PK),
  INDEX P_COMISSION_FK2(STAFF_PK),
  INDEX P_COMISSION_FK4(PRODUCT_PK),
  INDEX P_COMISSION_FKIndex5(PROSPECT_PK),
  INDEX P_COMMISSION_FKIndex5(COMMISSION_RULE_PK),
  FOREIGN KEY P_CUSTOMER_FKC1(CUSTOMER_PK)
    REFERENCES P_CUSTOMER(CUSTOMER_PK)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION,
  FOREIGN KEY P_STAFF_FKC2(STAFF_PK)
    REFERENCES P_STAFF(STAFF_PK)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION,
  FOREIGN KEY P_COMISSION_RULE_FKC2(COMMISSION_RULE_PK)
    REFERENCES P_COMMISSION_RULE(COMMISSION_RULE_PK)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION,
  FOREIGN KEY P_PRODUCT_FKC3(PRODUCT_PK)
    REFERENCES P_PRODUCT(PRODUCT_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION,
  FOREIGN KEY P_CUSTOMER_PROSPECT_FKC1(PROSPECT_PK)
    REFERENCES P_CUSTOMER_PROSPECT(PROSPECT_PK)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

CREATE TABLE P_CUSTOMER_PROSPECT_NOTES (
  NOTES_PK INTEGER UNSIGNED NOT NULL,
  PROSPECT_PK INTEGER UNSIGNED NOT NULL,
  NOTE TEXT NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  PRIMARY KEY(NOTES_PK),
  INDEX P_CUSTOMER_PROSPECT_NOTES_FKIndex1(PROSPECT_PK),
  FOREIGN KEY P_CUSTOMER_PROSPECT_FKC4(PROSPECT_PK)
    REFERENCES P_CUSTOMER_PROSPECT(PROSPECT_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

CREATE TABLE P_EMPLOYMENT_NOTES (
  NOTES_PK INTEGER UNSIGNED NOT NULL,
  EMPLOYMENT_PK INTEGER UNSIGNED NOT NULL,
  NOTE TEXT NOT NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  PRIMARY KEY(NOTES_PK),
  INDEX P_EMPLOYMENT_NOTES_FK1(EMPLOYMENT_PK),
  FOREIGN KEY P_EMPLOYMENT_FKC2(EMPLOYMENT_PK)
    REFERENCES P_EMPLOYMENT(EMPLOYMENT_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

CREATE TABLE P_PRODUCT_SALE_COMMISSION (
  PRODUCT_SALE_COMMISSION_PK INTEGER UNSIGNED NOT NULL,
  PRODUCT_SALE_PK INTEGER UNSIGNED NOT NULL,
  COMMISSION_RULE_PK INTEGER UNSIGNED NOT NULL,
  COMMISSION_TYPE VARCHAR(45) NOT NULL,
  COMMISSION_DATE DATETIME NOT NULL,
  COMMISSION_VALUE FLOAT NOT NULL,
  COMMISSION_VALUE_DATE DATETIME NOT NULL,
  STAFF_PK INTEGER UNSIGNED NOT NULL,
  STAFF_UID VARCHAR(255) NOT NULL,
  STAFF_SYSTEM_NAME VARCHAR(45) NOT NULL,
  STAFF_TITLE VARCHAR(20) NULL,
  STAFF_GIVENNAME VARCHAR(255) NULL,
  STAFF_SURNAME VARCHAR(255) NULL,
  STAFF_INITIALS VARCHAR(20) NULL,
  STAFF_POSITION VARCHAR(45) NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  PRIMARY KEY(PRODUCT_SALE_COMMISSION_PK),
  INDEX P_PRODUCT_SALE_COMMISSION_FK1(PRODUCT_SALE_PK),
  FOREIGN KEY P_PRODUCT_SALE_FKC3(PRODUCT_SALE_PK)
    REFERENCES P_PRODUCT_SALE(PRODUCT_SALE_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

CREATE TABLE P_PRODUCT_SALE_FLAG (
  PRODUCT_SALE_FLAG_PK INTEGER UNSIGNED NOT NULL,
  PRODUCT_SALE_PK INTEGER UNSIGNED NOT NULL,
  FLAG_NAME_PK INTEGER UNSIGNED NOT NULL,
  FLAG_NAME_DISP VARCHAR(255) NOT NULL,
  FLAG_NAME_STORE VARCHAR(255) NOT NULL,
  FLAG_VAL_PK INTEGER UNSIGNED NOT NULL DEFAULT '0',
  FLAG_VAL_DISP VARCHAR(255) NULL,
  FLAG_VAL_STORE VARCHAR(255) NOT NULL,
  REC_CREATE_NAME INTEGER UNSIGNED NULL,
  REC_CREATE_STAMP INTEGER UNSIGNED NULL,
  PRIMARY KEY(PRODUCT_SALE_FLAG_PK),
  INDEX P_PRODUCT_SALE_FLAG_FKIndex1(PRODUCT_SALE_PK),
  FOREIGN KEY P_PRODUCT_SALE_FKC4(PRODUCT_SALE_PK)
    REFERENCES P_PRODUCT_SALE(PRODUCT_SALE_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

CREATE TABLE P_PRODUCT_SALE_NOTES (
  NOTES_PK INTEGER UNSIGNED NOT NULL,
  PRODUCT_SALE_PK INTEGER UNSIGNED NOT NULL,
  NOTE TEXT NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  PRIMARY KEY(NOTES_PK),
  INDEX P_PRODUCT_SALE_NOTES_FK1(PRODUCT_SALE_PK),
  FOREIGN KEY P_PRODUCT_SALE_FKC1(PRODUCT_SALE_PK)
    REFERENCES P_PRODUCT_SALE(PRODUCT_SALE_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

CREATE TABLE L_EMPLOYMENT_FLAG (
  EMPLOYMENT_PK INTEGER UNSIGNED NOT NULL,
  FLAG_PK INTEGER UNSIGNED NOT NULL,
  REC_TO_DEL BOOL NOT NULL DEFAULT '0',
  REC_IN_CORE BOOL NOT NULL DEFAULT '0',
  REC_TBP VARCHAR(255) NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  PRIMARY KEY(EMPLOYMENT_PK, FLAG_PK),
  INDEX L_EMPLOYMENT_FLAG_FK1(EMPLOYMENT_PK),
  INDEX L_EMPLOYMENT_FLAG_FK2(FLAG_PK),
  FOREIGN KEY P_EMPLOYMENT_FKC1(EMPLOYMENT_PK)
    REFERENCES P_EMPLOYMENT(EMPLOYMENT_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION,
  FOREIGN KEY P_FLAG_FKC1(FLAG_PK)
    REFERENCES P_FLAG(FLAG_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

CREATE TABLE L_CUSTOMER_PROSPECT_FLAG (
  PROSPECT_PK INTEGER UNSIGNED NOT NULL,
  FLAG_PK INTEGER UNSIGNED NOT NULL,
  REC_TO_DEL BOOL NOT NULL DEFAULT '0',
  REC_IN_CORE BOOL NOT NULL DEFAULT '0',
  REC_TBP VARCHAR(255) NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  PRIMARY KEY(PROSPECT_PK, FLAG_PK),
  INDEX L_CUSTOMER_PROSPECT_FLAG_FK1(PROSPECT_PK),
  INDEX L_CUSTOMER_PROSPECT_FLAG_FK2(FLAG_PK),
  FOREIGN KEY P_CUSTOMER_PROSPECT_FKC5(PROSPECT_PK)
    REFERENCES P_CUSTOMER_PROSPECT(PROSPECT_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION,
  FOREIGN KEY P_FLAG_FKC6(FLAG_PK)
    REFERENCES P_FLAG(FLAG_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

CREATE TABLE L_CUSTOMER_PROSPECT_COMMISSION (
  PROSPECT_PK INTEGER UNSIGNED NOT NULL,
  COMMISSION_RULE_PK INTEGER UNSIGNED NOT NULL,
  REC_TO_DEL BOOL NOT NULL DEFAULT '0',
  REC_IN_CORE BOOL NOT NULL DEFAULT '0',
  REC_TBP VARCHAR(255) NULL,
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  PRIMARY KEY(PROSPECT_PK, COMMISSION_RULE_PK),
  INDEX L_CUSTOMER_PROSPECT_COMMISSION_FK1(PROSPECT_PK),
  INDEX L_CUSTOMER_PROSPECT_COMMISSION_FK2(COMMISSION_RULE_PK),
  FOREIGN KEY P_CUSTOMER_PROSPECT_FKC3(PROSPECT_PK)
    REFERENCES P_CUSTOMER_PROSPECT(PROSPECT_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION,
  FOREIGN KEY P_COMMISSION_RULE_FKC3(COMMISSION_RULE_PK)
    REFERENCES P_COMMISSION_RULE(COMMISSION_RULE_PK)
      ON DELETE CASCADE
      ON UPDATE NO ACTION
)
TYPE=InnoDB;