-- **************************************************************
--
-- TAMS II database upgrade script
-- From v.3.46.04.00 to v.3.47.01.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.3.46.04.00';
SET v_this_rev = 'v.3.47.01.00';
SET v_rev_date = '2023-11-10';
SET v_rev_note = 'This release implements change(s) TNSS-6720, TNSS-6568, TNSS-6550, TNSS-6539, STEL-2250, STEL-2309';

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 TNSS-6550
IF EXISTS (SELECT * FROM systable st JOIN syscolumn sc ON sc.table_id = st.table_id
						WHERE table_name = 'INVOICE_LINE_ITEM' AND column_name = 'PPSE_TOKEN_ID') THEN
	ALTER TABLE INVOICE_LINE_ITEM MODIFY PPSE_TOKEN_ID  VARCHAR(44);
ELSE
	ALTER TABLE INVOICE_LINE_ITEM ADD PPSE_TOKEN_ID  VARCHAR(44);
END IF;

IF EXISTS (SELECT * FROM systable st JOIN syscolumn sc ON sc.table_id = st.table_id
						WHERE table_name = 'SAVED_INVOICE_LINE_ITEM' AND column_name = 'PPSE_TOKEN_ID') THEN
	ALTER TABLE SAVED_INVOICE_LINE_ITEM MODIFY PPSE_TOKEN_ID  VARCHAR(44);
ELSE
	ALTER TABLE SAVED_INVOICE_LINE_ITEM ADD PPSE_TOKEN_ID  VARCHAR(44);
END IF;

-- End TNSS-6550


--Begin STEL-2309


IF EXISTS (SELECT * FROM systable st JOIN syscolumn sc ON sc.table_id = st.table_id
						WHERE table_name = 'END_OF_DAY_PROFILE' AND column_name = 'USE_CLOUD_BACKUP_ALERT') THEN
	ALTER TABLE END_OF_DAY_PROFILE MODIFY USE_CLOUD_BACKUP_ALERT VARCHAR(1) DEFAULT 'N' CHECK (USE_CLOUD_BACKUP_ALERT IN ('Y', 'N'));
ELSE
	ALTER TABLE END_OF_DAY_PROFILE ADD USE_CLOUD_BACKUP_ALERT VARCHAR(1) DEFAULT 'N' CHECK (USE_CLOUD_BACKUP_ALERT IN ('Y', 'N'));
END IF;


ALTER TABLE END_OF_DAY_PROFILE MODIFY USE_CLOUD_BACKUP_ALERT NOT NULL;
COMMENT ON COLUMN END_OF_DAY_PROFILE.USE_CLOUD_BACKUP_ALERT  IS 'Y=Yes; N=No';


--End STEL-2309

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-6720
IF EXISTS (SELECT * FROM systable st JOIN syscolumn sc ON sc.table_id = st.table_id
						WHERE table_name = 'END_OF_DAY_PROFILE' AND column_name = 'REGULATION_RUN_FREQUENCY') THEN
	ALTER TABLE END_OF_DAY_PROFILE MODIFY REGULATION_RUN_FREQUENCY TINYINT NOT NULL DEFAULT 1
                                          CHECK (REGULATION_RUN_FREQUENCY BETWEEN 1 AND 31);
ELSE
	ALTER TABLE END_OF_DAY_PROFILE ADD REGULATION_RUN_FREQUENCY TINYINT NOT NULL DEFAULT 1
                                          CHECK (REGULATION_RUN_FREQUENCY BETWEEN 1 AND 31);
END IF;

UPDATE END_OF_DAY_PROFILE
	SET REGULATION_RUN_FREQUENCY = 1;

-- End TNSS-6720


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;



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-6539
UPDATE 
  product_line 
set 
  tax_status_primary_ref_option_cd = 'N', 
  tax_status_secondary_ref_option_cd = 'N', 
  core_sales_tax_status_primary_ref_option_cd = 'N', 
  core_sales_tax_status_secondary_ref_option_cd = 'N', 
  core_returns_tax_status_primary_ref_option_cd = 'N', 
  core_returns_tax_status_secondary_ref_option_cd = 'N' 
WHERE 
  line_abbrev = 'GCD';

-- End TNSS-6539

--Begin STEL-2250

IF EXISTS (SELECT * FROM fw_function WHERE id = 332 AND loc = 1 ) THEN
  UPDATE fw_function
    SET PARENT_FUNCTION_ID =8,
        FUNCTION_TYPE = 1, 
        NAME = 'SA_CLOSE_BUSINESS_DAY',
        DESCRIPTION = 'Close Business Day'
    WHERE id = 332 AND loc =1;
ELSE
  insert into fw_function values(332,1,8,1,'SA_CLOSE_BUSINESS_DAY','Close Business Day');
END IF;

DELETE FROM translation WHERE table_name = 'FW_FUNCTION' AND column_name = 'DESCRIPTION' AND ref_id=332;
INSERT INTO translation(table_name, column_name, ref_id, ref_language_cd, text) VALUES ('FW_FUNCTION', 'DESCRIPTION', 332, 'EN', 'Close Business Day');

INSERT INTO translation(table_name, column_name, ref_id, ref_language_cd, text) VALUES ('FW_FUNCTION', 'DESCRIPTION', 332, 'ES', 'Cierre de operaciones');
INSERT INTO translation(table_name, column_name, ref_id, ref_language_cd, text) VALUES ('FW_FUNCTION', 'DESCRIPTION', 332, 'FR', 'Fermeture de journée');

delete from fw_function_group where function_id=332 and LOC=1 ;
INSERT INTO fw_function_group VALUES (1,332,1,'Y');
INSERT INTO fw_function_group VALUES (4,332,1,'Y');
INSERT INTO fw_function_group VALUES (6,332,1,'Y');
INSERT INTO fw_function_group VALUES (7,332,1,'Y');
INSERT INTO fw_function_group VALUES (8,332,1,'Y');
INSERT INTO fw_function_group VALUES (12,332,1,'Y');


INSERT INTO fw_function_user (user_id, function_id, loc, allow_modify )
  SELECT user_id, 332, loc, allow_modify 
  FROM fw_function_user a
  WHERE function_id = 136
	 AND NOT EXISTS (SELECT * 
	                 FROM fw_function_user b 
					 WHERE b.user_id = a.user_id 
					   AND b.loc = a.loc 
					   AND b.function_id =332);


--End STEL-2250


-- Begin TNSS-6568
IF NOT EXISTS (select * from data_extract_column where data_extract_table_id='2' and name='PPSE_TOKEN_ID') THEN
	IF NOT EXISTS (select * from data_extract_table where id=2) THEN
		insert into data_extract_table (id,name,extract_type,include_delete,initial_load,initial_load_from_date,initial_load_date) 
			VALUES (2,'INVOICE_LINE_ITEM',2,0,1,'2022-10-31','2023-01-27');
	END IF;
	insert into data_extract_column (
	  ID, data_extract_table_id, NAME, sequence
	) 
	values 
	  (
		(
		  select 
			ISNULL(max(id) + 1, 1) 
		  from 
			data_extract_column
		), 
		2, 
		'PPSE_TOKEN_ID', 
		(
		  select 
			ISNULL(max(sequence) + 1, 1) 
		  from 
			data_extract_column 
		  where 
			data_extract_table_id = 2
		)
	  );

END IF;

-- End TNSS-6568


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;

-- Begin TNSS-6720
IF NOT EXISTS (SELECT * FROM ref_file_activity_column_name WHERE ref_file_activity_table_name_id = 96 and id = 28 ) THEN 
	INSERT INTO ref_file_activity_column_name VALUES (96, 28, 'REGULATION_RUN_FREQUENCY', NULL, NULL);
	ELSE 
		UPDATE ref_file_activity_column_name 
		SET  column_name='REGULATION_RUN_FREQUENCY', 
			 ms_table_link=null, 
			 ms_field_name=null 
		WHERE ref_file_activity_table_name_id = 96 and id = 28 ; 
END IF;

-- End TNSS-6720

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 TNSS-6720
CALL tams_dba.sp_drop_object('trigger', 'tr_bu_end_of_day_profile');

CREATE TRIGGER tr_bu_end_of_day_profile
  BEFORE UPDATE ON END_OF_DAY_PROFILE
  REFERENCING OLD AS deleted NEW AS inserted
  for each row BEGIN
DECLARE i_return_status INTEGER;
  
  IF ISNULL(STRING(deleted.SEND_LOCAL_DETAIL), '') <> ISNULL(STRING(inserted.SEND_LOCAL_DETAIL), '') THEN
    i_return_status = CALL sp_log_file_update_activity (inserted.LOC, 96, 1,
        NULL, NULL, NULL, deleted.SEND_LOCAL_DETAIL,
        inserted.SEND_LOCAL_DETAIL, inserted.last_modified_date,
        inserted.modified_by_employee_id, deleted.last_modified_date, 1)
  END IF;
  IF ISNULL(STRING(deleted.NON_STOCK_PURGE), '') <> ISNULL(STRING(inserted.NON_STOCK_PURGE), '') THEN
    i_return_status = CALL sp_log_file_update_activity (inserted.LOC, 96, 2,
        NULL, NULL, NULL, deleted.NON_STOCK_PURGE,
        inserted.NON_STOCK_PURGE, inserted.last_modified_date,
        inserted.modified_by_employee_id, deleted.last_modified_date, 1)
  END IF;
  IF ISNULL(STRING(deleted.HISTORY_PURGE_PROCESS_DAY), '') <> ISNULL(STRING(inserted.HISTORY_PURGE_PROCESS_DAY), '') THEN
    i_return_status = CALL sp_log_file_update_activity (inserted.LOC, 96, 3,
        NULL, NULL, NULL, deleted.HISTORY_PURGE_PROCESS_DAY,
        inserted.HISTORY_PURGE_PROCESS_DAY, inserted.last_modified_date,
        inserted.modified_by_employee_id, deleted.last_modified_date, 1)
  END IF;
  IF ISNULL(STRING(deleted.SEND_EOM_REPORTABLE_SALES), '') <> ISNULL(STRING(inserted.SEND_EOM_REPORTABLE_SALES), '') THEN
    i_return_status = CALL sp_log_file_update_activity (inserted.LOC, 96, 4,
        NULL, NULL, NULL, deleted.SEND_EOM_REPORTABLE_SALES,
        inserted.SEND_EOM_REPORTABLE_SALES, inserted.last_modified_date,
        inserted.modified_by_employee_id, deleted.last_modified_date, 1)
  END IF;
  IF ISNULL(STRING(deleted.REPORTED_SALES_SUMMARY_COUNTER), '') <> ISNULL(STRING(inserted.REPORTED_SALES_SUMMARY_COUNTER), '') THEN
    i_return_status = CALL sp_log_file_update_activity (inserted.LOC, 96, 5,
        NULL, NULL, NULL, deleted.REPORTED_SALES_SUMMARY_COUNTER,
        inserted.REPORTED_SALES_SUMMARY_COUNTER, inserted.last_modified_date,
        inserted.modified_by_employee_id, deleted.last_modified_date, 1)
  END IF;
  IF ISNULL(STRING(deleted.LAST_REBATE_CLAIM_NUMBER), '') <> ISNULL(STRING(inserted.LAST_REBATE_CLAIM_NUMBER), '') THEN
    i_return_status = CALL sp_log_file_update_activity (inserted.LOC, 96, 6,
        NULL, NULL, NULL, deleted.LAST_REBATE_CLAIM_NUMBER,
        inserted.LAST_REBATE_CLAIM_NUMBER, inserted.last_modified_date,
        inserted.modified_by_employee_id, deleted.last_modified_date, 1)
  END IF;
  IF ISNULL(STRING(deleted.LAST_BIN_LABEL_GENERATED_DATE), '') <> ISNULL(STRING(inserted.LAST_BIN_LABEL_GENERATED_DATE), '') THEN
    i_return_status = CALL sp_log_file_update_activity (inserted.LOC, 96, 7,
        NULL, NULL, NULL, deleted.LAST_BIN_LABEL_GENERATED_DATE,
        inserted.LAST_BIN_LABEL_GENERATED_DATE, inserted.last_modified_date,
        inserted.modified_by_employee_id, deleted.last_modified_date, 1)
  END IF;
  IF ISNULL(STRING(deleted.PHONE_SWITCH_REMINDER), '') <> ISNULL(STRING(inserted.PHONE_SWITCH_REMINDER), '') THEN
    i_return_status = CALL sp_log_file_update_activity (inserted.LOC, 96, 8,
        NULL, NULL, NULL, deleted.PHONE_SWITCH_REMINDER,
        inserted.PHONE_SWITCH_REMINDER, inserted.last_modified_date,
        inserted.modified_by_employee_id, deleted.last_modified_date, 1)
  END IF;
  IF ISNULL(STRING(deleted.CLEAR_AVG_COST_AT_ECY), '') <> ISNULL(STRING(inserted.CLEAR_AVG_COST_AT_ECY), '') THEN
    i_return_status = CALL sp_log_file_update_activity (inserted.LOC, 96, 9,
        NULL, NULL, NULL, deleted.CLEAR_AVG_COST_AT_ECY,
        inserted.CLEAR_AVG_COST_AT_ECY, inserted.last_modified_date,
        inserted.modified_by_employee_id, deleted.last_modified_date, 1)
  END IF;
  IF ISNULL(deleted.TSU_RECEIVED, '') <> ISNULL(inserted.TSU_RECEIVED, '') THEN
    i_return_status = CALL sp_log_file_update_activity (inserted.LOC, 96, 20,
        NULL, NULL, NULL, deleted.TSU_RECEIVED,
        inserted.TSU_RECEIVED, inserted.last_modified_date,
        inserted.modified_by_employee_id, deleted.last_modified_date, 1)
  END IF;
  IF ISNULL(STRING(deleted.EXPORT_TO_DATA_WAREHOUSE), '') <> ISNULL(STRING(inserted.EXPORT_TO_DATA_WAREHOUSE), '') THEN
    i_return_status = CALL sp_log_file_update_activity (inserted.LOC, 96, 10,
        NULL, NULL, NULL, deleted.EXPORT_TO_DATA_WAREHOUSE,
        inserted.EXPORT_TO_DATA_WAREHOUSE, inserted.last_modified_date,
        inserted.modified_by_employee_id, deleted.last_modified_date, 1)
  END IF;
  IF ISNULL(STRING(deleted.USE_AUTOSTART_EOD), '') <> ISNULL(STRING(inserted.USE_AUTOSTART_EOD), '') THEN
    i_return_status = CALL sp_log_file_update_activity (inserted.LOC, 96, 11,
        NULL, NULL, NULL, deleted.USE_AUTOSTART_EOD,
        inserted.USE_AUTOSTART_EOD, inserted.last_modified_date,
        inserted.modified_by_employee_id, deleted.last_modified_date, 1)
  END IF;
  IF ISNULL(STRING(deleted.AUTOSTART_TIME), '') <> ISNULL(STRING(inserted.AUTOSTART_TIME), '') THEN
    i_return_status = CALL sp_log_file_update_activity (inserted.LOC, 96, 12,
        NULL, NULL, NULL, deleted.AUTOSTART_TIME,
        inserted.AUTOSTART_TIME, inserted.last_modified_date,
        inserted.modified_by_employee_id, deleted.last_modified_date, 1)
  END IF;
  IF ISNULL(STRING(deleted.AUTOSTART_EMPLOYEE_ID), '') <> ISNULL(STRING(inserted.AUTOSTART_EMPLOYEE_ID), '') THEN
    i_return_status = CALL sp_log_file_update_activity (inserted.LOC, 96, 13,
        NULL, NULL, NULL, deleted.AUTOSTART_EMPLOYEE_ID,
        inserted.AUTOSTART_EMPLOYEE_ID, inserted.last_modified_date,
        inserted.modified_by_employee_id, deleted.last_modified_date, 1)
  END IF;
  IF ISNULL(STRING(deleted.AUTOSTART_REPORT_PRINTER_ID), '') <> ISNULL(STRING(inserted.AUTOSTART_REPORT_PRINTER_ID), '') THEN
    i_return_status = CALL sp_log_file_update_activity (inserted.LOC, 96, 14,
        NULL, NULL, NULL, deleted.AUTOSTART_REPORT_PRINTER_ID,
        inserted.AUTOSTART_REPORT_PRINTER_ID, inserted.last_modified_date,
        inserted.modified_by_employee_id, deleted.last_modified_date, 1)
  END IF;
  IF ISNULL(deleted.AUTOSTART_REPORT_PRINT_ORIENTATION, '') <> ISNULL(inserted.AUTOSTART_REPORT_PRINT_ORIENTATION, '') THEN
    i_return_status = CALL sp_log_file_update_activity (inserted.LOC, 96, 15,
        NULL, NULL, NULL, deleted.AUTOSTART_REPORT_PRINT_ORIENTATION,
        inserted.AUTOSTART_REPORT_PRINT_ORIENTATION, inserted.last_modified_date,
        inserted.modified_by_employee_id, deleted.last_modified_date, 1)
  END IF;
  IF ISNULL(deleted.AUTOSTART_REPORT_PRINTER_USE_DUPLEX, '') <> ISNULL(inserted.AUTOSTART_REPORT_PRINTER_USE_DUPLEX, '') THEN
    i_return_status = CALL sp_log_file_update_activity (inserted.LOC, 96, 16,
        NULL, NULL, NULL, deleted.AUTOSTART_REPORT_PRINTER_USE_DUPLEX,
        inserted.AUTOSTART_REPORT_PRINTER_USE_DUPLEX, inserted.last_modified_date,
        inserted.modified_by_employee_id, deleted.last_modified_date, 1)
  END IF;
  IF ISNULL(deleted.BACKUP_FAILURE_MSG_TO_EMAIL, '') <> ISNULL(inserted.BACKUP_FAILURE_MSG_TO_EMAIL, '') THEN
    i_return_status = CALL sp_log_file_update_activity (inserted.LOC, 96, 17,
        NULL, NULL, NULL, deleted.BACKUP_FAILURE_MSG_TO_EMAIL,
        inserted.BACKUP_FAILURE_MSG_TO_EMAIL, inserted.last_modified_date,
        inserted.modified_by_employee_id, deleted.last_modified_date, 1)
  END IF;
  IF ISNULL(STRING(deleted.BACKUP_FAILURE_MSG_TO_EMPLOYEE_ID), '') <> ISNULL(STRING(inserted.BACKUP_FAILURE_MSG_TO_EMPLOYEE_ID), '') THEN
    i_return_status = CALL sp_log_file_update_activity (inserted.LOC, 96, 18,
        NULL, NULL, NULL, deleted.BACKUP_FAILURE_MSG_TO_EMPLOYEE_ID,
        inserted.BACKUP_FAILURE_MSG_TO_EMPLOYEE_ID, inserted.last_modified_date,
        inserted.modified_by_employee_id, deleted.last_modified_date, 1)
  END IF;
  IF ISNULL(STRING(deleted.AUTO_INVOICE_COSTING_GP_PERCENT), '') <> ISNULL(STRING(inserted.AUTO_INVOICE_COSTING_GP_PERCENT), '') THEN
    i_return_status = CALL sp_log_file_update_activity (inserted.LOC, 96, 19,
        NULL, NULL, NULL, deleted.AUTO_INVOICE_COSTING_GP_PERCENT,
        inserted.AUTO_INVOICE_COSTING_GP_PERCENT, inserted.last_modified_date,
        inserted.modified_by_employee_id, deleted.last_modified_date, 1)
  END IF;
  IF ISNULL(STRING(deleted.LAST_SENT_NAPA_ORDER_ACCT_DAY), '') <> ISNULL(STRING(inserted.LAST_SENT_NAPA_ORDER_ACCT_DAY), '') THEN
    i_return_status = CALL sp_log_file_update_activity (inserted.LOC, 96, 21,
        NULL, NULL, NULL, deleted.LAST_SENT_NAPA_ORDER_ACCT_DAY,
        inserted.LAST_SENT_NAPA_ORDER_ACCT_DAY, inserted.last_modified_date,
        inserted.modified_by_employee_id, deleted.last_modified_date, 1)
  END IF;
  IF ISNULL(STRING(deleted.AUTOMATIC_CATALOG_UPDATE_DAY), '') <> ISNULL(STRING(inserted.AUTOMATIC_CATALOG_UPDATE_DAY), '') THEN
    i_return_status = CALL sp_log_file_update_activity (inserted.LOC, 96, 22,
        NULL, NULL, NULL, deleted.AUTOMATIC_CATALOG_UPDATE_DAY,
        inserted.AUTOMATIC_CATALOG_UPDATE_DAY, inserted.last_modified_date,
        inserted.modified_by_employee_id, deleted.last_modified_date, 1)
  END IF;
  IF ISNULL(STRING(deleted.USE_LABEL_QTY_FOR_GENERATION), '') <> ISNULL(STRING(inserted.USE_LABEL_QTY_FOR_GENERATION), '') THEN
    i_return_status = CALL sp_log_file_update_activity (inserted.LOC, 96, 23,
        NULL, NULL, NULL, deleted.USE_LABEL_QTY_FOR_GENERATION,
        inserted.USE_LABEL_QTY_FOR_GENERATION, inserted.last_modified_date,
        inserted.modified_by_employee_id, deleted.last_modified_date, 1)
  END IF;
  IF ISNULL(STRING(deleted.AUTO_CATALOG_UPDATE_DAY_OF_WEEK), '') <> ISNULL(STRING(inserted.AUTO_CATALOG_UPDATE_DAY_OF_WEEK), '') THEN
    i_return_status = CALL sp_log_file_update_activity (inserted.LOC, 96, 24,
        NULL, NULL, NULL, deleted.AUTO_CATALOG_UPDATE_DAY_OF_WEEK,
        inserted.AUTO_CATALOG_UPDATE_DAY_OF_WEEK, inserted.last_modified_date,
        inserted.modified_by_employee_id, deleted.last_modified_date, 1)
  END IF;
  IF ISNULL(STRING(deleted.USE_NEW_EDW_COLUMN_DELIMITER), '') <> ISNULL(STRING(inserted.USE_NEW_EDW_COLUMN_DELIMITER), '') THEN
    i_return_status = CALL sp_log_file_update_activity (inserted.LOC, 96, 25,
        NULL, NULL, NULL, deleted.USE_NEW_EDW_COLUMN_DELIMITER,
        inserted.USE_NEW_EDW_COLUMN_DELIMITER, inserted.last_modified_date,
        inserted.modified_by_employee_id, deleted.last_modified_date, 1)
  END IF;
  IF ISNULL(STRING(deleted.USE_WEEKEND_CATALOG_SETTINGS), '') <> ISNULL(STRING(inserted.USE_WEEKEND_CATALOG_SETTINGS), '') THEN
    i_return_status = CALL sp_log_file_update_activity (inserted.LOC, 96, 26,
        NULL, NULL, NULL, deleted.USE_WEEKEND_CATALOG_SETTINGS,
        inserted.USE_WEEKEND_CATALOG_SETTINGS, inserted.last_modified_date,
        inserted.modified_by_employee_id, deleted.last_modified_date, 1)
  END IF;
  IF ISNULL(deleted.EOD_TRIGGERED_SOURCE, '') <> ISNULL(inserted.EOD_TRIGGERED_SOURCE, '') THEN
    i_return_status = CALL sp_log_file_update_activity (inserted.LOC, 96, 27,
        NULL, NULL, NULL, deleted.EOD_TRIGGERED_SOURCE,
        inserted.EOD_TRIGGERED_SOURCE, inserted.last_modified_date,
        inserted.modified_by_employee_id, deleted.last_modified_date, 1)
  END IF;
  IF ISNULL(STRING(deleted.REGULATION_RUN_FREQUENCY), '') <> ISNULL(STRING(inserted.REGULATION_RUN_FREQUENCY), '') THEN
    i_return_status = CALL sp_log_file_update_activity (inserted.LOC, 96, 28,
        NULL, NULL, NULL, deleted.REGULATION_RUN_FREQUENCY,
        inserted.REGULATION_RUN_FREQUENCY, inserted.last_modified_date,
        inserted.modified_by_employee_id, deleted.last_modified_date, 1)
  END IF;
  IF i_return_status = -1 THEN
    SET inserted.modified_by_employee_id = NULL;
    SET inserted.last_modified_date = CURRENT TIMESTAMP;
  END IF;

IF  inserted.export_to_data_warehouse != 1 THEN
	SET inserted.export_to_data_warehouse = 1;
END IF;
END;

-- End TNSS-6720

--
-- 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;