-- **************************************************************
--
-- TAMS II database upgrade script
-- From v.4.12.03.00 to v.4.13.02.00
-- This script must be run by a user with DBA authority.
-- Open this script using dbisqlc.  Do not copy and paste this script into dbisqlc.
-- This script can take several minutes to open and run.
--
-- **************************************************************

SET TEMPORARY OPTION ALLOW_NULLS_BY_DEFAULT = 'ON';
-- Settings only for Sybase SQL Anywhere 7.
IF (SELECT SUBSTRING(PROPERTY('ProductVersion'), 1, PATINDEX('%.%', PROPERTY('ProductVersion')) - 1)) = '7' THEN
  -- The AUTOMATIC_TIMESTAMP setting is deprecated in Sybase SQL Anywhere 11 - the default behavior is 'OFF'.
  SET TEMPORARY OPTION AUTOMATIC_TIMESTAMP = 'OFF';
END IF;

CREATE VARIABLE v_this_rev VARCHAR(20);
CREATE VARIABLE v_last_rev VARCHAR(20);
CREATE VARIABLE v_rev_date DATE;
CREATE VARIABLE v_rev_note LONG VARCHAR;
CREATE VARIABLE v_msg VARCHAR(254);
CREATE VARIABLE b_upgrade BIT;
CREATE VARIABLE i_script_type INTEGER; -- 0 = Developer Script (Project build), 1 = Production Script (Release build)

SET v_last_rev = 'v.4.12.03.00';
SET v_this_rev = 'v.4.13.02.00';
SET v_rev_date = '2024-06-26';
SET v_rev_note = 'This release implements change(s) SPP-198, SPP-197, SPP-199, TNSS-7679, TNSS-7680, TNSS-7753, TNSS-7681, EOD-168';

SET b_upgrade = 1;
SET i_script_type = 1;

IF i_script_type = 1 THEN
  IF (SELECT COUNT(*) FROM db_release
        WHERE release_num = v_last_rev
          OR (LEFT(release_num, 9) = LEFT(v_this_rev, 9)  -- Accept range of minor version numbers
              AND RIGHT(release_num, 3) BETWEEN '.00' AND RIGHT(v_this_rev, 3))) < 1 THEN
    SET v_msg = 'Database is not on revision ' || v_last_rev || '!';
    SET b_upgrade = 0;
    MESSAGE v_msg TYPE WARNING TO CLIENT;
  END IF;
END IF;

SETUSER tams;

IF b_upgrade = 1 THEN -- Begin the big IF statement!

IF i_script_type = 1 THEN
  SET v_msg = 'Updating database to version ' || v_this_rev || ', please wait...';
  MESSAGE v_msg TO CLIENT;

  DELETE FROM db_release
    WHERE release_num = v_last_rev
      OR (LEFT(release_num, 9) = LEFT(v_this_rev, 9)  -- Accept range of minor version numbers
          AND RIGHT(release_num, 3) BETWEEN '.00' AND RIGHT(v_this_rev, 3));

  SET v_msg = 'Upgrading from ' || v_last_rev || ' to ' || v_this_rev || '...' || '00% done.';
  INSERT INTO db_release VALUES ('NOLOG', v_rev_date, v_msg);
END IF;

--------------------------------------------------------
--
-- Structure changes
--===============================
MESSAGE 'Beginning structure changes.' TO CLIENT;

-- Begin SPP-198
CALL tams_dba.sp_drop_object('table', 'CUSTOMER_TO_ITEM_PRICING');

CREATE TABLE CUSTOMER_TO_ITEM_PRICING (
       ID                   INTEGER NOT NULL DEFAULT AUTOINCREMENT,
       CUSTOMER_NUMBER      INTEGER NOT NULL,
       LINE_ABBREV          VARCHAR(3) NOT NULL
                                          CHECK (LENGTH(LINE_ABBREV) >= 2 AND PATINDEX('%[^A-Z0-9]%',LINE_ABBREV)=0),
       PART_NUMBER          VARCHAR(22) NOT NULL
                                          CHECK (PATINDEX('%[^A-Z0-9]%',PART_NUMBER)=0),
       EFFECTIVE_FROM_DATE  DATE NOT NULL,
       EFFECTIVE_TO_DATE    DATE NOT NULL,
       PRICE_LEVEL          VARCHAR(5),
       REF_PRICE_SHEET_CD   VARCHAR(2),
       MARKUP_PERCENT       DECIMAL(5,2),
       EXACT_PRICE          DECIMAL(10,2)
                                          CHECK (EXACT_PRICE >= 0),
       CONTRACT_FLAG        VARCHAR(1) NOT NULL DEFAULT 'N'
                                          CHECK (CONTRACT_FLAG IN ('Y', 'N')),
       REF_CURRENCY_ROUNDING_CD VARCHAR(1) NOT NULL DEFAULT 'N',
       REASON_CODE          VARCHAR(10),
       DELETE_FLAG          VARCHAR(1)
                                          CHECK (DELETE_FLAG IN ('Y')),
       SOURCE_SYSTEM_IDENTIFIER INTEGER NOT NULL,
       SOURCE_SYSTEM_PARENT_IDENTIFIER_LIST VARCHAR(20),
       SOURCE_SYSTEM_PUBLISHER VARCHAR(20),
       SOURCE_UPDATED_TS    TIMESTAMP NOT NULL,
       LAST_MODIFIED_DATE   TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP
);

CREATE UNIQUE INDEX XAK1CUSTOMER_TO_ITEM_PRICING ON CUSTOMER_TO_ITEM_PRICING
(
       CUSTOMER_NUMBER ASC,
       LINE_ABBREV ASC,
       PART_NUMBER ASC,
       EFFECTIVE_FROM_DATE ASC
);


ALTER TABLE CUSTOMER_TO_ITEM_PRICING
       ADD PRIMARY KEY (ID);

COMMENT ON TABLE CUSTOMER_TO_ITEM_PRICING IS 'Provides a Price Level, Price Sheet & markup %, or an exact price for a Customer and Product (line abbrev & part number) combination.  A resulting Price Level is resolved with a lookup in another table.';
COMMENT ON COLUMN CUSTOMER_TO_ITEM_PRICING.ID IS 'PK autoicrement field';
COMMENT ON COLUMN CUSTOMER_TO_ITEM_PRICING.CUSTOMER_NUMBER IS 'Customer Account Number in the AR Group.';
COMMENT ON COLUMN CUSTOMER_TO_ITEM_PRICING.LINE_ABBREV IS 'Field (DC, TAMS, customer facing) product line abbreviation code.';
COMMENT ON COLUMN CUSTOMER_TO_ITEM_PRICING.PART_NUMBER IS 'Product part number with all non alpha-numeric characters removed.  This field is used with a product line (HQ or Field) to identify and determine a unique product.';
COMMENT ON COLUMN CUSTOMER_TO_ITEM_PRICING.EFFECTIVE_FROM_DATE IS 'The first date this price rule is effective.';
COMMENT ON COLUMN CUSTOMER_TO_ITEM_PRICING.EFFECTIVE_TO_DATE IS 'The last date when this price rule is effective.';
COMMENT ON COLUMN CUSTOMER_TO_ITEM_PRICING.PRICE_LEVEL IS 'The price level to lookup in another table to calculate the price.';
COMMENT ON COLUMN CUSTOMER_TO_ITEM_PRICING.REF_PRICE_SHEET_CD IS 'The price sheet of the product to use as the basis for price calculation.';
COMMENT ON COLUMN CUSTOMER_TO_ITEM_PRICING.MARKUP_PERCENT IS 'The markup (or down if negative) to apply to the price sheet.';
COMMENT ON COLUMN CUSTOMER_TO_ITEM_PRICING.EXACT_PRICE IS 'The exact price to use for the product.';
COMMENT ON COLUMN CUSTOMER_TO_ITEM_PRICING.CONTRACT_FLAG IS 'Y=Yes; N=No.  Indicates this pricing rule is for a contract with the customer and should not consider promo pricing.';
COMMENT ON COLUMN CUSTOMER_TO_ITEM_PRICING.REF_CURRENCY_ROUNDING_CD IS 'Indicates if Penny or Dollar level rounding rules should be applied the customer price.';
COMMENT ON COLUMN CUSTOMER_TO_ITEM_PRICING.REASON_CODE IS 'A code that gives additional insight on the pricing platform system into why a particular price rule was calculated.';
COMMENT ON COLUMN CUSTOMER_TO_ITEM_PRICING.DELETE_FLAG IS 'Y=Yes.  Indicates a record was logically deleted, and should not be used for price calculation (not initially used).';
COMMENT ON COLUMN CUSTOMER_TO_ITEM_PRICING.SOURCE_SYSTEM_IDENTIFIER IS 'The ID of this record on the pricing platform system (PriceFX).';
COMMENT ON COLUMN CUSTOMER_TO_ITEM_PRICING.SOURCE_SYSTEM_PARENT_IDENTIFIER_LIST IS 'A comma-separated list of source system ID values that this record replaces.';
COMMENT ON COLUMN CUSTOMER_TO_ITEM_PRICING.SOURCE_SYSTEM_PUBLISHER IS 'The name of the source system that published this pricing rule record.';
COMMENT ON COLUMN CUSTOMER_TO_ITEM_PRICING.SOURCE_UPDATED_TS IS 'The timestamp when the source system published the record.  Used during out-of-sequence file processing to prevent overwriting newer records with older records.';
COMMENT ON COLUMN CUSTOMER_TO_ITEM_PRICING.LAST_MODIFIED_DATE IS 'The timestamp this record was last inserted/updated.';


ALTER TABLE CUSTOMER_TO_ITEM_PRICING
       ADD FOREIGN KEY FK_CUSTOMER_TO_ITEM_PRICING_REF_PRICE_SHEET (
              REF_PRICE_SHEET_CD)
                             REFERENCES REF_PRICE_SHEET  (CD)
                             ON DELETE RESTRICT
                             ON UPDATE RESTRICT;


ALTER TABLE CUSTOMER_TO_ITEM_PRICING
       ADD FOREIGN KEY FK_CUSTOMER_TO_ITEM_PRICING_REF_CURRENCY_ROUNDING (
              REF_CURRENCY_ROUNDING_CD)
                             REFERENCES REF_CURRENCY_ROUNDING  (CD)
                             ON DELETE RESTRICT
                             ON UPDATE RESTRICT;

GRANT SELECT ON CUSTOMER_TO_ITEM_PRICING TO tams_read, support_l12;
GRANT DELETE, INSERT, UPDATE ON CUSTOMER_TO_ITEM_PRICING TO tams_modify, support_l12;
GRANT ALL ON CUSTOMER_TO_ITEM_PRICING TO support_l34;

-- End SPP-198



MESSAGE '10% done...' TO CLIENT;
IF i_script_type = 1 THEN
  UPDATE db_release SET notes = SUBSTRING(notes, 1, CHARINDEX('%', notes) - 3) || '10% done.' WHERE release_num = 'NOLOG';
END IF;

-- Begin TNSS-7753
ALTER TABLE employee         MODIFY password             VARCHAR(60);
ALTER TABLE password_history MODIFY password             VARCHAR(60);

-- End TNSS-7753


-- Begin SPP-197
CALL tams_dba.sp_drop_object('table', 'CUSTOMER_TO_CUSTOMER_SEGMENT');

CREATE TABLE CUSTOMER_TO_CUSTOMER_SEGMENT (
       ID                   INTEGER NOT NULL DEFAULT AUTOINCREMENT,
       CUSTOMER_NUMBER      INTEGER NOT NULL,
       EFFECTIVE_FROM_DATE  DATE NOT NULL,
       EFFECTIVE_TO_DATE    DATE NOT NULL,
       CUSTOMER_SEGMENT_ID  VARCHAR(30) NOT NULL,
       CUSTOMER_SEGMENT_NAME VARCHAR(30),
       GLOBAL_CUSTOMER_ID   VARCHAR(30),
       DELETE_FLAG          VARCHAR(1)
                                          CHECK (DELETE_FLAG IN ('Y')),
       SOURCE_SYSTEM_IDENTIFIER INTEGER NOT NULL,
       SOURCE_SYSTEM_PARENT_IDENTIFIER_LIST VARCHAR(20),
       SOURCE_SYSTEM_PUBLISHER VARCHAR(20),
       SOURCE_UPDATED_TS    TIMESTAMP NOT NULL,
       LAST_MODIFIED_DATE   TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP
);

CREATE UNIQUE INDEX XAK1CUSTOMER_TO_CUSTOMER_SEGMENT ON CUSTOMER_TO_CUSTOMER_SEGMENT
(
       CUSTOMER_NUMBER ASC,
       EFFECTIVE_FROM_DATE ASC
);


ALTER TABLE CUSTOMER_TO_CUSTOMER_SEGMENT
       ADD PRIMARY KEY (ID);

COMMENT ON TABLE CUSTOMER_TO_CUSTOMER_SEGMENT IS 'Maps a customer to a customer segment.  Pricing for Customer segements are resolved in another table.';
COMMENT ON COLUMN CUSTOMER_TO_CUSTOMER_SEGMENT.ID IS 'PK autoicrement field';
COMMENT ON COLUMN CUSTOMER_TO_CUSTOMER_SEGMENT.CUSTOMER_NUMBER IS 'Customer Account Number in the AR Group.';
COMMENT ON COLUMN CUSTOMER_TO_CUSTOMER_SEGMENT.EFFECTIVE_FROM_DATE IS 'The first date this price rule is effective.';
COMMENT ON COLUMN CUSTOMER_TO_CUSTOMER_SEGMENT.EFFECTIVE_TO_DATE IS 'The last date when this price rule is effective.';
COMMENT ON COLUMN CUSTOMER_TO_CUSTOMER_SEGMENT.CUSTOMER_SEGMENT_ID IS 'The customer segment for a customer.';
COMMENT ON COLUMN CUSTOMER_TO_CUSTOMER_SEGMENT.CUSTOMER_SEGMENT_NAME IS 'The name for the customer segment.';
COMMENT ON COLUMN CUSTOMER_TO_CUSTOMER_SEGMENT.GLOBAL_CUSTOMER_ID IS 'UUID value for this customer from the Customer Domain.';
COMMENT ON COLUMN CUSTOMER_TO_CUSTOMER_SEGMENT.DELETE_FLAG IS 'Y=Yes.  Indicates a record was logically deleted, and should not be used for price calculation (not initially used).';
COMMENT ON COLUMN CUSTOMER_TO_CUSTOMER_SEGMENT.SOURCE_SYSTEM_IDENTIFIER IS 'The ID of this record on the pricing platform system (PriceFX).';
COMMENT ON COLUMN CUSTOMER_TO_CUSTOMER_SEGMENT.SOURCE_SYSTEM_PARENT_IDENTIFIER_LIST IS 'A comma-separated list of source system ID values that this record replaces.';
COMMENT ON COLUMN CUSTOMER_TO_CUSTOMER_SEGMENT.SOURCE_SYSTEM_PUBLISHER IS 'The name of the source system that published this pricing rule record.';
COMMENT ON COLUMN CUSTOMER_TO_CUSTOMER_SEGMENT.SOURCE_UPDATED_TS IS 'The timestamp when the source system published the record.  Used during out-of-sequence file processing to prevent overwriting newer records with older records.';
COMMENT ON COLUMN CUSTOMER_TO_CUSTOMER_SEGMENT.LAST_MODIFIED_DATE IS 'The timestamp this record was last inserted/updated.';

GRANT SELECT ON CUSTOMER_TO_CUSTOMER_SEGMENT TO tams_read, support_l12;
GRANT DELETE, INSERT, UPDATE ON CUSTOMER_TO_CUSTOMER_SEGMENT TO tams_modify, support_l12;
GRANT ALL ON CUSTOMER_TO_CUSTOMER_SEGMENT TO support_l34;
-- End SPP-197

-- Begin SPP-199
CALL tams_dba.sp_drop_object('table', 'CUSTOMER_TO_PRODUCT_SEGMENT_PRICING');

CREATE TABLE CUSTOMER_TO_PRODUCT_SEGMENT_PRICING (
       ID                   INTEGER NOT NULL DEFAULT AUTOINCREMENT,
       CUSTOMER_NUMBER      INTEGER NOT NULL,
       PRODUCT_CATEGORY     VARCHAR(20),
       LINE_ABBREV          VARCHAR(3) NOT NULL
                                          CHECK (LENGTH(LINE_ABBREV) >= 2 AND PATINDEX('%[^A-Z0-9]%',LINE_ABBREV)=0),
       GROUP_CODE           SMALLINT
                                          CHECK (GROUP_CODE BETWEEN 0 AND 999),
       EFFECTIVE_FROM_DATE  DATE NOT NULL,
       EFFECTIVE_TO_DATE    DATE NOT NULL,
       PRICE_LEVEL          VARCHAR(5),
       REF_PRICE_SHEET_CD   VARCHAR(2),
       MARKUP_PERCENT       DECIMAL(5,2),
       EXACT_PRICE          DECIMAL(10,2),
       CONTRACT_FLAG        VARCHAR(1) NOT NULL DEFAULT 'N'
                                          CHECK (CONTRACT_FLAG IN ('Y', 'N')),
       REF_CURRENCY_ROUNDING_CD VARCHAR(1) NOT NULL DEFAULT 'N',
       REASON_CODE          VARCHAR(10),
       DELETE_FLAG          VARCHAR(1)
                                          CHECK (DELETE_FLAG IN ('Y')),
       SOURCE_SYSTEM_IDENTIFIER INTEGER NOT NULL,
       SOURCE_SYSTEM_PARENT_IDENTIFIER_LIST VARCHAR(20),
       SOURCE_SYSTEM_PUBLISHER VARCHAR(20),
       SOURCE_UPDATED_TS    TIMESTAMP NOT NULL,
       LAST_MODIFIED_DATE   TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP
);

CREATE UNIQUE INDEX XAK1CUSTOMER_TO_PRODUCT_SEGMENT_PRICING ON CUSTOMER_TO_PRODUCT_SEGMENT_PRICING
(
       CUSTOMER_NUMBER ASC,
       PRODUCT_CATEGORY ASC,
       LINE_ABBREV ASC,
       GROUP_CODE ASC,
       EFFECTIVE_FROM_DATE ASC
);


ALTER TABLE CUSTOMER_TO_PRODUCT_SEGMENT_PRICING
       ADD PRIMARY KEY (ID);

COMMENT ON TABLE CUSTOMER_TO_PRODUCT_SEGMENT_PRICING IS 'Provides a Price Level or Price Sheet & markup % for a Customer Segment and Product Category, Product Line, or Product Line and Group Code combination.  This table is used to resolve a Customer Segment lookup.  A resulting Price Level is resolved with a lookup in another table.';
COMMENT ON COLUMN CUSTOMER_TO_PRODUCT_SEGMENT_PRICING.ID IS 'PK autoicrement field';
COMMENT ON COLUMN CUSTOMER_TO_PRODUCT_SEGMENT_PRICING.CUSTOMER_NUMBER IS 'Customer Account Number in the AR Group.';
COMMENT ON COLUMN CUSTOMER_TO_PRODUCT_SEGMENT_PRICING.PRODUCT_CATEGORY IS 'Indicates a broad category of products that this pricing rule applies to (not initially used).';
COMMENT ON COLUMN CUSTOMER_TO_PRODUCT_SEGMENT_PRICING.LINE_ABBREV IS 'Field (DC, TAMS, customer facing) product line abbreviation code.';
COMMENT ON COLUMN CUSTOMER_TO_PRODUCT_SEGMENT_PRICING.GROUP_CODE IS 'Product group code.  Subdivision of a product line.';
COMMENT ON COLUMN CUSTOMER_TO_PRODUCT_SEGMENT_PRICING.EFFECTIVE_FROM_DATE IS 'The first date this price rule is effective.';
COMMENT ON COLUMN CUSTOMER_TO_PRODUCT_SEGMENT_PRICING.EFFECTIVE_TO_DATE IS 'The last date when this price rule is effective.';
COMMENT ON COLUMN CUSTOMER_TO_PRODUCT_SEGMENT_PRICING.PRICE_LEVEL IS 'The price level to lookup in another table to calculate the price.';
COMMENT ON COLUMN CUSTOMER_TO_PRODUCT_SEGMENT_PRICING.REF_PRICE_SHEET_CD IS 'The price sheet of the product to use as the basis for price calculation.';
COMMENT ON COLUMN CUSTOMER_TO_PRODUCT_SEGMENT_PRICING.MARKUP_PERCENT IS 'The markup (or down if negative) to apply to the price sheet.';
COMMENT ON COLUMN CUSTOMER_TO_PRODUCT_SEGMENT_PRICING.EXACT_PRICE IS 'The exact price to use for the product.';
COMMENT ON COLUMN CUSTOMER_TO_PRODUCT_SEGMENT_PRICING.CONTRACT_FLAG IS 'Y=Yes; N=No.  Indicates this pricing rule is for a contract with the customer and should not consider promo pricing.';
COMMENT ON COLUMN CUSTOMER_TO_PRODUCT_SEGMENT_PRICING.REF_CURRENCY_ROUNDING_CD IS 'Indicates if Penny or Dollar level rounding rules should be applied the customer price.';
COMMENT ON COLUMN CUSTOMER_TO_PRODUCT_SEGMENT_PRICING.REASON_CODE IS 'A code that gives additional insight on the pricing platform system into why a particular price rule was calculated.';
COMMENT ON COLUMN CUSTOMER_TO_PRODUCT_SEGMENT_PRICING.DELETE_FLAG IS 'Y=Yes.  Indicates a record was logically deleted, and should not be used for price calculation (not initially used).';
COMMENT ON COLUMN CUSTOMER_TO_PRODUCT_SEGMENT_PRICING.SOURCE_SYSTEM_IDENTIFIER IS 'The ID of this record on the pricing platform system (PriceFX).';
COMMENT ON COLUMN CUSTOMER_TO_PRODUCT_SEGMENT_PRICING.SOURCE_SYSTEM_PARENT_IDENTIFIER_LIST IS 'A comma-separated list of source system ID values that this record replaces.';
COMMENT ON COLUMN CUSTOMER_TO_PRODUCT_SEGMENT_PRICING.SOURCE_SYSTEM_PUBLISHER IS 'The name of the source system that published this pricing rule record.';
COMMENT ON COLUMN CUSTOMER_TO_PRODUCT_SEGMENT_PRICING.SOURCE_UPDATED_TS IS 'The timestamp when the source system published the record.  Used during out-of-sequence file processing to prevent overwriting newer records with older records.';
COMMENT ON COLUMN CUSTOMER_TO_PRODUCT_SEGMENT_PRICING.LAST_MODIFIED_DATE IS 'The timestamp this record was last inserted/updated.';

ALTER TABLE CUSTOMER_TO_PRODUCT_SEGMENT_PRICING
       ADD FOREIGN KEY FK_CUSTOMER_TO_PRODUCT_SEGMENT_PRICING_REF_PRICE_SHEET (
              REF_PRICE_SHEET_CD)
                             REFERENCES REF_PRICE_SHEET  (CD)
                             ON DELETE RESTRICT
                             ON UPDATE RESTRICT;


ALTER TABLE CUSTOMER_TO_PRODUCT_SEGMENT_PRICING
       ADD FOREIGN KEY FK_CUSTOMER_TO_PRODUCT_SEGMENT_PRICING_REF_CURRENCY_ROUNDING (
              REF_CURRENCY_ROUNDING_CD)
                             REFERENCES REF_CURRENCY_ROUNDING  (CD)
                             ON DELETE RESTRICT
                             ON UPDATE RESTRICT;

GRANT SELECT ON CUSTOMER_TO_PRODUCT_SEGMENT_PRICING TO tams_read, support_l12;
GRANT DELETE, INSERT, UPDATE ON CUSTOMER_TO_PRODUCT_SEGMENT_PRICING TO tams_modify, support_l12;
GRANT ALL ON CUSTOMER_TO_PRODUCT_SEGMENT_PRICING TO support_l34;
-- End SPP-199 


MESSAGE '20% done...' TO CLIENT;
IF i_script_type = 1 THEN
  UPDATE db_release SET notes = SUBSTRING(notes, 1, CHARINDEX('%', notes) - 3) || '20% done.' WHERE release_num = 'NOLOG';
END IF;

-- Begin EOD-168
CALL tams_dba.sp_drop_object('table', 'STORE_DAY_CLOSED');

CREATE TABLE STORE_DAY_CLOSED (
       CLOSED_DAY           DATE NOT NULL,
       NAME                 VARCHAR(30) NOT NULL
);


ALTER TABLE STORE_DAY_CLOSED
       ADD PRIMARY KEY (CLOSED_DAY);


CALL tams_dba.sp_drop_object('table', 'STORE_HOUR_EXCEPTION');

CREATE TABLE STORE_HOUR_EXCEPTION (
       EXCEPTION_DATE       DATE NOT NULL,
       OPEN_TIME            TIME NOT NULL,
       CLOSE_TIME           TIME NOT NULL
);


ALTER TABLE STORE_HOUR_EXCEPTION
       ADD PRIMARY KEY (EXCEPTION_DATE);

CALL tams_dba.sp_drop_object('table', 'STORE_HOURS');

CREATE TABLE STORE_HOURS (
       REF_DAY_OF_WEEK_ID   TINYINT NOT NULL
                                          CHECK (REF_DAY_OF_WEEK_ID BETWEEN 0 AND 6),
       STORE_IS_OPEN        VARCHAR(1) NOT NULL DEFAULT 'Y'
                                          CHECK (STORE_IS_OPEN IN ('Y', 'N')),
       OPEN_TIME            TIME,
       CLOSE_TIME           TIME
);


ALTER TABLE STORE_HOURS
       ADD PRIMARY KEY (REF_DAY_OF_WEEK_ID);

COMMENT ON COLUMN STORE_HOURS.STORE_IS_OPEN IS 'Y=Yes; N=No';

ALTER TABLE STORE_HOURS
       ADD FOREIGN KEY FK_STORE_HOURS_REF_DAY_OF_WEEK (
              REF_DAY_OF_WEEK_ID)
                             REFERENCES REF_DAY_OF_WEEK  (ID)
                             ON DELETE RESTRICT
                             ON UPDATE RESTRICT;


GRANT SELECT ON STORE_DAY_CLOSED TO tams_read, support_l12;
GRANT SELECT ON STORE_HOUR_EXCEPTION TO tams_read, support_l12;
GRANT SELECT ON STORE_HOURS TO tams_read, support_l12;

GRANT DELETE, INSERT, UPDATE ON STORE_DAY_CLOSED TO tams_modify, support_l12;
GRANT DELETE, INSERT, UPDATE ON STORE_HOUR_EXCEPTION TO tams_modify, support_l12;
GRANT DELETE, INSERT, UPDATE ON STORE_HOURS TO tams_modify, support_l12;

GRANT ALL ON STORE_DAY_CLOSED TO support_l34;
GRANT ALL ON STORE_HOUR_EXCEPTION TO support_l34;
GRANT ALL ON STORE_HOURS TO support_l34;


-- End EOD-168


MESSAGE '30% done...' TO CLIENT;
IF i_script_type = 1 THEN
  UPDATE db_release SET notes = SUBSTRING(notes, 1, CHARINDEX('%', notes) - 3) || '30% done.' WHERE release_num = 'NOLOG';
END IF;


--
-- Data changes
--===============================
MESSAGE 'Completed structure changes.' TO CLIENT;
MESSAGE 'Beginning data changes.' TO CLIENT;
MESSAGE '40% done...' TO CLIENT;
IF i_script_type = 1 THEN
  UPDATE db_release SET notes = SUBSTRING(notes, 1, CHARINDEX('%', notes) - 3) || '40% done.' WHERE release_num = 'NOLOG';
END IF;

--Begin TNSS-7679

INSERT INTO data_extract_table
SELECT (
		SELECT isnull(MAX(id), 0)
		FROM data_extract_table
		) + NUMBER() AS max_id
	, systable.table_name AS table_name
	, 2 AS extract_type
	, 0 AS include_delete
	, 0 AS initial_load
	, NULL AS initial_load_from_date
	, CAST(NULL AS DATETIME) AS initial_load_date
FROM systable
WHERE systable.table_name IN ('CUSTOMER_CERTIFICATION','CERTIFICATION_PART','CERTIFICATION_PART_GROUP','CERTIFICATION_STATE')
	AND systable.table_name NOT IN (
		SELECT NAME
		FROM data_extract_table
		);


--End TNSS-7679

--Begin TNSS-7680

INSERT INTO data_extract_column
SELECT (
		SELECT isnull(MAX(id), 0)
		FROM data_extract_column
		) + NUMBER() AS max_id
	, data_extract_table.id
	, syscolumn.column_name
	, syscolumn.column_id
FROM syscolumn
INNER JOIN systable ON systable.table_id = syscolumn.table_id
INNER JOIN data_extract_table ON data_extract_table.name = systable.table_name
WHERE systable.table_name IN (
		  'CUSTOMER_CERTIFICATION'
		, 'CERTIFICATION_PART'
		, 'CERTIFICATION_PART_GROUP'
		, 'CERTIFICATION_STATE')
		AND systable.table_name || syscolumn.column_name NOT IN (
		SELECT dt.name || dc.name
		FROM data_extract_column dc
			, data_extract_table dt
		WHERE dc.data_extract_table_id = dt.id
			AND dt.name IN (
				   'CUSTOMER_CERTIFICATION'
		, 'CERTIFICATION_PART'
		, 'CERTIFICATION_PART_GROUP'
		, 'CERTIFICATION_STATE'))
ORDER BY syscolumn.table_id
	   , syscolumn.column_id;

--End TNSS-7680

--Begin TNSS-7681

INSERT INTO data_extract_table_identifier
SELECT (
		SELECT isnull(MAX(id), 0)
		FROM data_extract_table_identifier
		) + NUMBER() AS max_id
	, data_extract_table_id
	, NAME AS identifier
	, 'TABLE_ID_ENTRY' AS incr_identifier_column
FROM data_extract_column
WHERE sequence = 1
	AND data_extract_table_id IN (
		SELECT id
		FROM data_extract_table
		WHERE NAME IN ('CUSTOMER_CERTIFICATION','CERTIFICATION_PART','CERTIFICATION_PART_GROUP','CERTIFICATION_STATE'))
	AND data_extract_table_id NOT IN (
		SELECT data_extract_table_id
		FROM data_extract_table_identifier
		)
ORDER BY data_extract_table_id;
 

--End TNSS-7681

MESSAGE '50% done...' TO CLIENT;
IF i_script_type = 1 THEN
  UPDATE db_release SET notes = SUBSTRING(notes, 1, CHARINDEX('%', notes) - 3) || '50% done.' WHERE release_num = 'NOLOG';
END IF;



MESSAGE '60% done...' TO CLIENT;
IF i_script_type = 1 THEN
  UPDATE db_release SET notes = SUBSTRING(notes, 1, CHARINDEX('%', notes) - 3) || '60% done.' WHERE release_num = 'NOLOG';
END IF;



MESSAGE '70% done...' TO CLIENT;
IF i_script_type = 1 THEN
  UPDATE db_release SET notes = SUBSTRING(notes, 1, CHARINDEX('%', notes) - 3) || '70% done.' WHERE release_num = 'NOLOG';
END IF;



--
-- Stored Procedure Changes
--===============================
MESSAGE 'Completed data changes.' TO CLIENT;
MESSAGE 'Beginning stored procedure and function changes.' TO CLIENT;
MESSAGE '80% done...' TO CLIENT;
IF i_script_type = 1 THEN
  UPDATE db_release SET notes = SUBSTRING(notes, 1, CHARINDEX('%', notes) - 3) || '80% done.' WHERE release_num = 'NOLOG';
END IF;



--
-- Trigger Changes
--===============================
MESSAGE 'Completed stored procedure and function changes.' TO CLIENT;
MESSAGE 'Beginning trigger changes.' TO CLIENT;
MESSAGE '90% done...' TO CLIENT;
IF i_script_type = 1 THEN
  UPDATE db_release SET notes = SUBSTRING(notes, 1, CHARINDEX('%', notes) - 3) || '90% done.' WHERE release_num = 'NOLOG';
END IF;

-- Begin SPP-198
CALL tams_dba.sp_drop_object('trigger', 'tr_bi_customer_to_item_pricing');

CREATE TRIGGER tr_bi_customer_to_item_pricing
  BEFORE INSERT ON CUSTOMER_TO_ITEM_PRICING
  REFERENCING NEW AS inserted
  for each row BEGIN
  IF (CASE WHEN inserted.price_level IS NULL THEN 0 ELSE 1 END
      + CASE WHEN inserted.ref_price_sheet_cd IS NULL THEN 0 ELSE 1 END
      + CASE WHEN inserted.exact_price IS NULL THEN 0 ELSE 1 END) <> 1 THEN
    RAISERROR 18253 'tr_bi_customer_to_item_pricing: Must have a value for exactly one of PRICE_LEVEL, REF_PRICE_SHEET_CD, and EXACT_PRICE (the others must be NULL).';
  END IF;
  IF (CASE WHEN inserted.ref_price_sheet_cd IS NULL THEN 0 ELSE 1 END
      + CASE WHEN inserted.markup_percent IS NULL THEN 0 ELSE 1 END) = 1 THEN
    RAISERROR 18254 'tr_bi_customer_to_item_pricing: Either REF_PRICE_SHEET_CD and MARKUP_PERCENT both must have a value or neither must have a value.';
  END IF;
  IF inserted.effective_from_date > inserted.effective_to_date THEN
    RAISERROR 18255 'tr_bi_customer_to_item_pricing: EFFECTIVE_TO_DATE cannot be earlier than EFFECTIVE_FROM_DATE.';
  END IF;

  -- Remove any older records that conflict with the incoming record.
  DELETE FROM CUSTOMER_TO_ITEM_PRICING
    WHERE customer_number = inserted.customer_number
      AND line_abbrev = inserted.line_abbrev
      AND part_number = inserted.part_number
      AND (
          effective_from_date BETWEEN inserted.effective_from_date AND inserted.effective_to_date
          OR effective_to_date BETWEEN inserted.effective_from_date AND inserted.effective_to_date
          OR inserted.effective_from_date BETWEEN effective_from_date AND effective_to_date
        )
      AND source_updated_ts <= inserted.source_updated_ts
      AND id <> inserted.id;

  -- If there is a conflicting newer record, we can't insert this one.
  IF EXISTS (SELECT * FROM CUSTOMER_TO_ITEM_PRICING
      WHERE customer_number = inserted.customer_number
        AND line_abbrev = inserted.line_abbrev
        AND part_number = inserted.part_number
        AND (
            effective_from_date BETWEEN inserted.effective_from_date AND inserted.effective_to_date
            OR effective_to_date BETWEEN inserted.effective_from_date AND inserted.effective_to_date
            OR inserted.effective_from_date BETWEEN effective_from_date AND effective_to_date
          )
        AND source_updated_ts > inserted.source_updated_ts
        AND id <> inserted.id
    ) THEN
    RAISERROR 18250 'tr_bi_customer_to_item_pricing: Newer record with conflicting criteria already exists.';
  END IF;

END;

CALL tams_dba.sp_drop_object('trigger', 'tr_bu_customer_to_item_pricing');

CREATE TRIGGER tr_bu_customer_to_item_pricing
  BEFORE UPDATE ON CUSTOMER_TO_ITEM_PRICING
  REFERENCING OLD AS deleted NEW AS inserted
  for each row BEGIN
  IF (CASE WHEN inserted.price_level IS NULL THEN 0 ELSE 1 END
      + CASE WHEN inserted.ref_price_sheet_cd IS NULL THEN 0 ELSE 1 END
      + CASE WHEN inserted.exact_price IS NULL THEN 0 ELSE 1 END) <> 1 THEN
    RAISERROR 18253 'tr_bu_customer_to_item_pricing: Must have a value for exactly one of PRICE_LEVEL, REF_PRICE_SHEET_CD, and EXACT_PRICE (the others must be NULL).';
  END IF;
  IF (CASE WHEN inserted.ref_price_sheet_cd IS NULL THEN 0 ELSE 1 END
      + CASE WHEN inserted.markup_percent IS NULL THEN 0 ELSE 1 END) = 1 THEN
    RAISERROR 18254 'tr_bu_customer_to_item_pricing: Either REF_PRICE_SHEET_CD and MARKUP_PERCENT both must have a value or neither must have a value.';
  END IF;
  IF inserted.effective_from_date > inserted.effective_to_date THEN
    RAISERROR 18255 'tr_bu_customer_to_item_pricing: EFFECTIVE_TO_DATE cannot be earlier than EFFECTIVE_FROM_DATE.';
  END IF;

  -- Prevent updates if there are any conflicting records.  The user must clean up any conflicts before updating.
  IF EXISTS (SELECT * FROM CUSTOMER_TO_ITEM_PRICING
      WHERE customer_number = inserted.customer_number
        AND line_abbrev = inserted.line_abbrev
        AND part_number = inserted.part_number
        AND (
            effective_from_date BETWEEN inserted.effective_from_date AND inserted.effective_to_date
            OR effective_to_date BETWEEN inserted.effective_from_date AND inserted.effective_to_date
            OR inserted.effective_from_date BETWEEN effective_from_date AND effective_to_date
          )
        AND id <> inserted.id
    ) THEN
    RAISERROR 18260 'tr_bu_customer_to_item_pricing: Record with conflicting criteria already exists.';
  END IF;

END;

-- End SPP-198


-- Begin SPP-197
CALL tams_dba.sp_drop_object('trigger', 'tr_bi_customer_to_customer_segment');

CREATE TRIGGER tr_bi_customer_to_customer_segment
  BEFORE INSERT ON CUSTOMER_TO_CUSTOMER_SEGMENT
  REFERENCING NEW AS inserted
  for each row BEGIN
  IF inserted.effective_from_date > inserted.effective_to_date THEN
    RAISERROR 18255 'tr_bi_customer_to_customer_segment: EFFECTIVE_TO_DATE cannot be earlier than EFFECTIVE_FROM_DATE.';
  END IF;

  DELETE FROM CUSTOMER_TO_CUSTOMER_SEGMENT
    WHERE customer_number = inserted.customer_number
      AND (
          effective_from_date BETWEEN inserted.effective_from_date AND inserted.effective_to_date
          OR effective_to_date BETWEEN inserted.effective_from_date AND inserted.effective_to_date
          OR inserted.effective_from_date BETWEEN effective_from_date AND effective_to_date
        )
      AND source_updated_ts <= inserted.source_updated_ts
      AND id <> inserted.id;

  -- If there is a conflicting newer record, we can't insert this one.
  IF EXISTS (SELECT * FROM CUSTOMER_TO_CUSTOMER_SEGMENT
      WHERE customer_number = inserted.customer_number
        AND (
            effective_from_date BETWEEN inserted.effective_from_date AND inserted.effective_to_date
            OR effective_to_date BETWEEN inserted.effective_from_date AND inserted.effective_to_date
            OR inserted.effective_from_date BETWEEN effective_from_date AND effective_to_date
          )
        AND source_updated_ts > inserted.source_updated_ts
        AND id <> inserted.id
    ) THEN
    RAISERROR 18250 'tr_bi_customer_to_customer_segment: Newer record with conflicting criteria already exists.';
  END IF;

END;

CALL tams_dba.sp_drop_object('trigger', 'tr_bu_customer_to_customer_segment');

CREATE TRIGGER tr_bu_customer_to_customer_segment
  BEFORE UPDATE ON CUSTOMER_TO_CUSTOMER_SEGMENT
  REFERENCING OLD AS deleted NEW AS inserted
  for each row BEGIN
  IF inserted.effective_from_date > inserted.effective_to_date THEN
    RAISERROR 18255 'tr_bu_customer_to_customer_segment: EFFECTIVE_TO_DATE cannot be earlier than EFFECTIVE_FROM_DATE.';
  END IF;

  -- Prevent updates if there are any conflicting records.  The user must clean up any conflicts before updating.
  IF EXISTS (SELECT * FROM CUSTOMER_TO_CUSTOMER_SEGMENT
      WHERE customer_number = inserted.customer_number
        AND (
            effective_from_date BETWEEN inserted.effective_from_date AND inserted.effective_to_date
            OR effective_to_date BETWEEN inserted.effective_from_date AND inserted.effective_to_date
            OR inserted.effective_from_date BETWEEN effective_from_date AND effective_to_date
          )
        AND id <> inserted.id
    ) THEN
    RAISERROR 18260 'tr_bu_customer_to_customer_segment: Record with conflicting criteria already exists.';
  END IF;

END;

-- End SPP-197


-- Begin SPP-199
CALL tams_dba.sp_drop_object('trigger', 'tr_bi_customer_to_product_segment_pricing');

CREATE TRIGGER tr_bi_customer_to_product_segment_pricing
  BEFORE INSERT ON CUSTOMER_TO_PRODUCT_SEGMENT_PRICING
  REFERENCING NEW AS inserted
  for each row BEGIN
  IF (CASE WHEN inserted.line_abbrev IS NULL THEN 0 ELSE 1 END
      + CASE WHEN inserted.product_category IS NULL THEN 0 ELSE 1 END) <> 1 THEN
    RAISERROR 18251 'tr_bi_customer_to_product_segment_pricing: Must have a value for only one of LINE_ABBREV and PRODUCT_CATEGORY (the other must be NULL).';
  END IF;
  IF inserted.line_abbrev IS NULL AND inserted.group_code IS NOT NULL THEN
    RAISERROR 18252 'tr_bi_customer_to_product_segment_pricing: Cannot have value for GROUP_CODE without a value for LINE_ABBREV.';
  END IF;
  IF (CASE WHEN inserted.price_level IS NULL THEN 0 ELSE 1 END
      + CASE WHEN inserted.ref_price_sheet_cd IS NULL THEN 0 ELSE 1 END
      + CASE WHEN inserted.exact_price IS NULL THEN 0 ELSE 1 END) <> 1 THEN
    RAISERROR 18253 'tr_bi_customer_to_product_segment_pricing: Must have a value for exactly one of PRICE_LEVEL, REF_PRICE_SHEET_CD, and EXACT_PRICE (the others must be NULL).';
  END IF;
  IF (CASE WHEN inserted.ref_price_sheet_cd IS NULL THEN 0 ELSE 1 END
      + CASE WHEN inserted.markup_percent IS NULL THEN 0 ELSE 1 END) = 1 THEN
    RAISERROR 18254 'tr_bi_customer_to_product_segment_pricing: Either REF_PRICE_SHEET_CD and MARKUP_PERCENT both must have a value or neither must have a value.';
  END IF;
  IF inserted.effective_from_date > inserted.effective_to_date THEN
    RAISERROR 18255 'tr_bi_customer_to_product_segment_pricing: EFFECTIVE_TO_DATE cannot be earlier than EFFECTIVE_FROM_DATE.';
  END IF;

  IF inserted.line_abbrev IS NOT NULL THEN
    -- Remove any older records that conflict with the incoming record.
    DELETE FROM CUSTOMER_TO_PRODUCT_SEGMENT_PRICING
      WHERE customer_number = inserted.customer_number
        AND product_category IS NULL
        AND line_abbrev = inserted.line_abbrev
        AND ISNULL(group_code, -1) = ISNULL(inserted.group_code, -1)
        AND (
            effective_from_date BETWEEN inserted.effective_from_date AND inserted.effective_to_date
            OR effective_to_date BETWEEN inserted.effective_from_date AND inserted.effective_to_date
            OR inserted.effective_from_date BETWEEN effective_from_date AND effective_to_date
          )
        AND source_updated_ts <= inserted.source_updated_ts
        AND id <> inserted.id;

    -- If there is a conflicting newer record, we can't insert this one.
    IF EXISTS (SELECT * FROM CUSTOMER_TO_PRODUCT_SEGMENT_PRICING
        WHERE customer_number = inserted.customer_number
          AND product_category IS NULL
          AND line_abbrev = inserted.line_abbrev
          AND ISNULL(group_code, -1) = ISNULL(inserted.group_code, -1)
          AND (
              effective_from_date BETWEEN inserted.effective_from_date AND inserted.effective_to_date
              OR effective_to_date BETWEEN inserted.effective_from_date AND inserted.effective_to_date
              OR inserted.effective_from_date BETWEEN effective_from_date AND effective_to_date
            )
          AND source_updated_ts > inserted.source_updated_ts
          AND id <> inserted.id
      ) THEN
      RAISERROR 18250 'tr_bi_customer_to_product_segment_pricing: Newer record with conflicting criteria already exists.';
    END IF;
  ELSE
    -- Remove any older records that conflict with the incoming record.
    DELETE FROM CUSTOMER_TO_PRODUCT_SEGMENT_PRICING
      WHERE customer_number = inserted.customer_number
        AND product_category = inserted.product_category
        AND line_abbrev IS NULL
        AND group_code IS NULL
        AND (
            effective_from_date BETWEEN inserted.effective_from_date AND inserted.effective_to_date
            OR effective_to_date BETWEEN inserted.effective_from_date AND inserted.effective_to_date
            OR inserted.effective_from_date BETWEEN effective_from_date AND effective_to_date
          )
        AND source_updated_ts <= inserted.source_updated_ts
        AND id <> inserted.id;

    -- If there is a conflicting newer record, we can't insert this one.
    IF EXISTS (SELECT * FROM CUSTOMER_TO_PRODUCT_SEGMENT_PRICING
        WHERE customer_number = inserted.customer_number
          AND product_category = inserted.product_category
          AND line_abbrev IS NULL
          AND group_code IS NULL
          AND (
              effective_from_date BETWEEN inserted.effective_from_date AND inserted.effective_to_date
              OR effective_to_date BETWEEN inserted.effective_from_date AND inserted.effective_to_date
              OR inserted.effective_from_date BETWEEN effective_from_date AND effective_to_date
            )
          AND source_updated_ts > inserted.source_updated_ts
          AND id <> inserted.id
      ) THEN
      RAISERROR 18250 'tr_bi_customer_to_product_segment_pricing: Newer record with conflicting criteria already exists.';
    END IF;
  END IF;

END;


CALL tams_dba.sp_drop_object('trigger', 'tr_bu_customer_to_product_segment_pricing');

CREATE TRIGGER tr_bu_customer_to_product_segment_pricing
  BEFORE UPDATE ON CUSTOMER_TO_PRODUCT_SEGMENT_PRICING
  REFERENCING OLD AS deleted NEW AS inserted
  for each row BEGIN
  IF (CASE WHEN inserted.line_abbrev IS NULL THEN 0 ELSE 1 END
      + CASE WHEN inserted.product_category IS NULL THEN 0 ELSE 1 END) <> 1 THEN
    RAISERROR 18251 'tr_bu_customer_to_product_segment_pricing: Must have a value for only one of LINE_ABBREV and PRODUCT_CATEGORY (the other must be NULL).';
  END IF;
  IF inserted.line_abbrev IS NULL AND inserted.group_code IS NOT NULL THEN
    RAISERROR 18252 'tr_bu_customer_to_product_segment_pricing: Cannot have value for GROUP_CODE without a value for LINE_ABBREV.';
  END IF;
  IF (CASE WHEN inserted.price_level IS NULL THEN 0 ELSE 1 END
      + CASE WHEN inserted.ref_price_sheet_cd IS NULL THEN 0 ELSE 1 END
      + CASE WHEN inserted.exact_price IS NULL THEN 0 ELSE 1 END) <> 1 THEN
    RAISERROR 18253 'tr_bu_customer_to_product_segment_pricing: Must have a value for exactly one of PRICE_LEVEL, REF_PRICE_SHEET_CD, and EXACT_PRICE (the others must be NULL).';
  END IF;
  IF (CASE WHEN inserted.ref_price_sheet_cd IS NULL THEN 0 ELSE 1 END
      + CASE WHEN inserted.markup_percent IS NULL THEN 0 ELSE 1 END) = 1 THEN
    RAISERROR 18254 'tr_bu_customer_to_product_segment_pricing: Either REF_PRICE_SHEET_CD and MARKUP_PERCENT both must have a value or neither must have a value.';
  END IF;
  IF inserted.effective_from_date > inserted.effective_to_date THEN
    RAISERROR 18255 'tr_bu_customer_to_product_segment_pricing: EFFECTIVE_TO_DATE cannot be earlier than EFFECTIVE_FROM_DATE.';
  END IF;

  IF inserted.line_abbrev IS NOT NULL THEN
    -- Prevent updates if there are any conflicting records.  The user must clean up any conflicts before updating.
    IF EXISTS (SELECT * FROM CUSTOMER_TO_PRODUCT_SEGMENT_PRICING
        WHERE customer_number = inserted.customer_number
          AND product_category IS NULL
          AND line_abbrev = inserted.line_abbrev
          AND ISNULL(group_code, -1) = ISNULL(inserted.group_code, -1)
          AND (
              effective_from_date BETWEEN inserted.effective_from_date AND inserted.effective_to_date
              OR effective_to_date BETWEEN inserted.effective_from_date AND inserted.effective_to_date
              OR inserted.effective_from_date BETWEEN effective_from_date AND effective_to_date
            )
          AND id <> inserted.id
      ) THEN
      RAISERROR 18260 'tr_bu_customer_to_product_segment_pricing: Record with conflicting criteria already exists.';
    END IF;
  ELSE
    -- Prevent updates if there are any conflicting records.  The user must clean up any conflicts before updating.
    IF EXISTS (SELECT * FROM CUSTOMER_TO_PRODUCT_SEGMENT_PRICING
        WHERE customer_number = inserted.customer_number
          AND product_category = inserted.product_category
          AND line_abbrev IS NULL
          AND group_code IS NULL
          AND (
              effective_from_date BETWEEN inserted.effective_from_date AND inserted.effective_to_date
              OR effective_to_date BETWEEN inserted.effective_from_date AND inserted.effective_to_date
              OR inserted.effective_from_date BETWEEN effective_from_date AND effective_to_date
            )
          AND id <> inserted.id
      ) THEN
      RAISERROR 18260 'tr_bu_customer_to_product_segment_pricing: Record with conflicting criteria already exists.';
    END IF;
  END IF;

END;

-- End SPP-199







--
-- View Changes
--===============================
MESSAGE 'Completed trigger changes.' TO CLIENT;
MESSAGE 'Beginning view changes.' TO CLIENT;



MESSAGE 'Completed view changes.' TO CLIENT;

--------------------------------------------------------

IF i_script_type = 1 THEN
  DELETE FROM db_release WHERE release_num = 'NOLOG' AND release_date = v_rev_date;
  INSERT INTO db_release VALUES(v_this_rev, v_rev_date, v_rev_note);
END IF;

MESSAGE 'Synchronizing TABLE_SEQUENCE....' TO CLIENT;
CALL sp_sync_table_sequence(1);

END IF; -- End the big IF statement!

SETUSER ;

DROP VARIABLE v_this_rev;
DROP VARIABLE v_last_rev;
DROP VARIABLE v_rev_date;
DROP VARIABLE v_rev_note;
DROP VARIABLE v_msg;

IF b_upgrade = 1 THEN
  MESSAGE '100% done.' TO CLIENT;
ELSE
  MESSAGE 'Upgrade aborted.' TO CLIENT;
END IF;

DROP VARIABLE b_upgrade;
DROP VARIABLE i_script_type;