-- **************************************************************
--
-- TAMS II database upgrade script
-- From v.3.44.01.00 to v.3.45.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.44.01.00';
SET v_this_rev = 'v.3.45.01.00';
SET v_rev_date = '2023-10-17';
SET v_rev_note = 'This release implements change(s) TNSS-5940, TNSS-6432, TNSS-5976, TNSS-6469, STEL-2142, STEL-2188, STEL-2194';

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;



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;



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

IF NOT EXISTS (SELECT * FROM REF_ORDER_PART_DEFER_REASON WHERE id =8 and DESCRIPTION='DC') THEN
INSERT INTO tams.REF_ORDER_PART_DEFER_REASON (ID,DESCRIPTION) VALUES(8,'DC');
End if;

DELETE FROM translation WHERE table_name = 'REF_ORDER_PART_DEFER_REASON' AND column_name = 'DESCRIPTION' AND ref_id = 8;
INSERT INTO translation(table_name, column_name, ref_id, ref_language_cd, text) VALUES ('REF_ORDER_PART_DEFER_REASON', 'DESCRIPTION', 8, 'EN', 'DC');
INSERT INTO translation(table_name, column_name, ref_id, ref_language_cd, text) VALUES ('REF_ORDER_PART_DEFER_REASON', 'DESCRIPTION', 8, 'ES', 'DC');
INSERT INTO translation(table_name, column_name, ref_id, ref_language_cd, text) VALUES ('REF_ORDER_PART_DEFER_REASON', 'DESCRIPTION', 8, 'FR', 'DC');


--End TNSS-5940


--Begin TNSS-5976


IF NOT EXISTS (select * from data_extract_table where id=14) THEN
INSERT INTO "tams"."DATA_EXTRACT_TABLE" ("ID","NAME","EXTRACT_TYPE","INCLUDE_DELETE","INITIAL_LOAD","INITIAL_LOAD_FROM_DATE","INITIAL_LOAD_DATE") VALUES(14,'INVENTORY',2,0,1,'2022-10-11','2023-10-11')
END IF;


IF NOT EXISTS (SELECT * FROM data_extract_column WHERE name ='DC_ONLY' and data_extract_table_id= 14) THEN
insert into data_extract_column ( ID,data_extract_table_id,NAME, sequence)
select (select IsNull(max(ID+1),1) from data_extract_column),14,'DC_ONLY',IsNULL(max(sequence+1),1)from data_extract_column where data_extract_table_id =14;
END IF;


--End TNSS-5976

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-6432
DELETE FROM communication_url WHERE id IN (142, 143, 144);

INSERT INTO communication_url VALUES (142,'CUSTOMER_DOMAIN_FEED_SERVICE_DEV', 'https://pubsub.googleapis.com/v1/projects/gpc-d-cust/topics/cdc-tams-pub-customer' );
INSERT INTO communication_url VALUES (143,'CUSTOMER_DOMAIN_FEED_SERVICE_QA',  'https://pubsub.googleapis.com/v1/projects/gpc-q-cust/topics/cdc-tams-pub-customer');
INSERT INTO communication_url VALUES (144,'CUSTOMER_DOMAIN_FEED_SERVICE_PROD','https://pubsub.googleapis.com/v1/projects/gpc-p-cust/topics/cdc-tams-pub-customer');
-- End TNSS-6432



--Begin STEL-2142


IF NOT EXISTS (SELECT * FROM ref_eod_task  WHERE id=88 and log_text='TAMS OS Patching') THEN
INSERT INTO ref_eod_task VALUES (88, 2075, 'TAMS OS Patching', 'com.gpc.tams.eod.task.offlinetask.OfflineOSPatching', 'O', 'N')
END IF;

delete from translation where table_name='REF_EOD_TASK' and column_name='LOG_TEXT' and ref_id=88;
INSERT INTO translation (table_name, column_name, ref_id, ref_language_cd, text) VALUES ('REF_EOD_TASK', 'LOG_TEXT', 88, 'EN', 'TAMS OS Patching');
INSERT INTO translation (table_name, column_name, ref_id, ref_language_cd, text) VALUES ('REF_EOD_TASK', 'LOG_TEXT', 88, 'ES', 'TAMS OS Patching');
INSERT INTO translation (table_name, column_name, ref_id, ref_language_cd, text) VALUES ('REF_EOD_TASK', 'LOG_TEXT', 88, 'FR', 'TAMS OS Patching');


delete from EOD_TASK_DETAILS where REF_EOD_TASK_ID=88;
INSERT INTO EOD_TASK_DETAILS VALUES(88, 1, 'Y', NOW(), NULL);


--End STEL-2142 


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;

-- Begin TNSS-6469

CALL tams_dba.sp_drop_object('function', 'fn_get_next_fotab_identifier');
-- Description: Use to get the next number in sequence for FOTAB.
--                Verifys that nobody else got the number that is returned.
--
-- Parameters: Pass the type and loc to get the next number for FOTAB.
--
-- Modification History:
--
-- 2023-10-10 : CF  : Written.
--
COMMIT;

-- Last Modified: 2023-10-10 by NZN
-- Copyright (c) 2017 by Genuine Parts Company (GPC)

CREATE FUNCTION fn_get_next_fotab_identifier (
  IN  in_loc INTEGER,
  IN  in_type VARCHAR(2))
  RETURNS VARCHAR(5)
BEGIN
    DECLARE max_identifier VARCHAR(6);
    DECLARE type_pattern VARCHAR(3);
    DECLARE identifier_number INTEGER;
    DECLARE new_identifier_number INTEGER;

    SET type_pattern = STRING(in_type, '%');
    
    SELECT MAX(IDENTIFIER)
        INTO max_identifier
        FROM FACTORY_ORDER_TABLE
        WHERE IDENTIFIER LIKE type_pattern;
    
    SET identifier_number = CAST(SUBSTRING(max_identifier, 3, 3) AS INTEGER);
    
    IF (identifier_number = 999) 
        THEN
            SET new_identifier_number = 1;
        ELSE
            SET new_identifier_number = identifier_number + 1;
        END IF;
    
    WHILE EXISTS (SELECT IDENTIFIER
                      FROM FACTORY_ORDER_TABLE
                      WHERE IDENTIFIER = STRING(in_type, (select REPLICATE('0',3 - LENGTH(new_identifier_number))), new_identifier_number)) LOOP
        SET new_identifier_number = new_identifier_number + 1;
    
        IF (new_identifier_number >= 999) THEN
            RETURN 'ERROR'
        END IF;
        
    END LOOP;
        
    RETURN STRING(in_type, (select REPLICATE('0',3 - LENGTH(new_identifier_number))), new_identifier_number);
END;

GRANT EXECUTE ON fn_get_next_fotab_identifier TO tams_application;
GRANT EXECUTE ON fn_get_next_fotab_identifier TO support_l34;

-- End TNSS-6469


--
-- 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 STEL-2188


CALL tams_dba.sp_drop_object('trigger', 'tr_aid_customer');
CALL tams_dba.sp_drop_object('trigger', 'tr_bd_customer');


CREATE  TRIGGER tr_bd_customer
  BEFORE DELETE ON CUSTOMER
  REFERENCING OLD AS deleted
  for each row BEGIN
DECLARE v_text varchar(254);
DECLARE v_CQT_text varchar(1000);

  SET v_text=deleted.CUSTOMER_NUM || '|' || deleted.ALPHA_KEY;
  SET v_text=v_text || '|' || deleted.NAME || '|' || deleted.ADDRESS1;
  SET v_text=v_text || '|' || deleted.PHONE;

  SET v_CQT_text=deleted.ID || '|' || deleted.CUSTOMER_NUM || '|' || deleted.GLOBAL_CUSTOMER_ID;
  SET v_CQT_text=v_CQT_text || '|' || deleted.LAST_MODIFIED_DATE || '|' || deleted.MODIFIED_BY_EMPLOYEE_ID;

  CALL sp_log_file_insdel_activity (deleted.loc, 68,
      deleted.id, NULL, NULL,
      v_text, 'D', deleted.last_modified_date, deleted.modified_by_employee_id, 1);

  If ((Select send_customer_domain from tams.store_profile) = 'Y') THEN
    Insert Into customer_change_queue (customer_id,loc,action,trigger_source,deleted_value) 
			 Values (deleted.id,deleted.loc,'D','CUSTOMER',v_CQT_text);	 
  END IF;
End;


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

CREATE  TRIGGER tr_bi_customer
  BEFORE INSERT ON CUSTOMER
  REFERENCING NEW AS inserted
 for each row BEGIN
  DECLARE v_text varchar(254);
  DECLARE i_return_status INTEGER;
  SET v_text=inserted.CUSTOMER_NUM || '|' || inserted.ALPHA_KEY;
  SET v_text=v_text || '|' || inserted.NAME || '|' || inserted.ADDRESS1;
  SET v_text=v_text || '|' || inserted.PHONE;
  i_return_status = CALL sp_log_file_insdel_activity (inserted.loc, 68,
      inserted.id, NULL, NULL,
      v_text, 'A', inserted.last_modified_date, inserted.modified_by_employee_id, 1);
  IF i_return_status = -1 THEN
    SET inserted.modified_by_employee_id = NULL;
    SET inserted.last_modified_date = CURRENT TIMESTAMP;
  END IF;
  IF ((Select send_customer_domain from tams.store_profile) = 'Y') Then    
    Insert Into customer_change_queue (customer_id,loc,action,trigger_source) 
			 Values (inserted.id,inserted.loc,'I','CUSTOMER');	 
  END IF;
END;

--End STEL-2188

--Begin STEL-2194

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

Create Trigger tr_ai_communication_directory
After Insert On tams.COMMUNICATION_DIRECTORY
Referencing OLD As existed NEW as inserted
For Each Row

BEGIN
  If (((Select send_customer_domain from tams.store_profile) = 'Y') AND
      (Select count(*) from customer_change_queue where customer_id = inserted.INVOICE_TO_CUSTOMER_ID 
         and loc = inserted.loc and validation_cd = 'T' and action = 'I' and trigger_source = 'CUSTOMER') = 0)
       Then
     If INSERTING  
	   Then
		  Insert Into tams.customer_change_queue (customer_id,loc,action,trigger_source,deleted_value) 
			  Values (inserted.INVOICE_TO_CUSTOMER_ID,inserted.loc,'I','COMMUNICATION_DIRECTORY',inserted.id)
     End If
   End If
End;

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

CREATE  TRIGGER tr_bd_customer_ar
  BEFORE DELETE ON CUSTOMER_AR
  REFERENCING OLD AS deleted
  for each row BEGIN
DECLARE v_text varchar(254);
DECLARE v_CQT_text varchar(1000);

  SET v_text=deleted.AR_TERMS_ID || '|' || deleted.CREDIT_LIMIT;
  SET v_text=v_text || '|' || deleted.CURRENT_BAL;
  
  CALL sp_log_file_insdel_activity (deleted.loc, 71,
      deleted.customer_id, NULL, NULL,
      v_text, 'D', deleted.last_modified_date, deleted.modified_by_employee_id, 1);

  IF (((Select send_customer_domain from tams.store_profile) = 'Y') AND
      (Select count(*) from customer_change_queue where customer_id = deleted.CUSTOMER_ID 
         and loc = deleted.loc and validation_cd = 'T' and action = 'D' and trigger_source = 'CUSTOMER') = 0)
  THEN
    SET v_CQT_text=deleted.CUSTOMER_ID || '|' || deleted.AR_TERMS_ID || '|' || deleted.REF_STATEMENT_TYPE_ID || '|' || deleted.STATEMENT_CUSTOMER_ID; 
    SET v_CQT_text=v_CQT_text || '|' || deleted.LOC || '|' || deleted.EXTRA_STATEMENT_COPIES || '|' || deleted.COMBINED_STATEMENT;
    SET v_CQT_text=v_CQT_text || '|' || deleted.CREDIT_LIMIT || '|' || deleted.STORE_RESPONSIBILITY || '|' || deleted.AGE_CUSTOMER;
    SET v_CQT_text=v_CQT_text || '|' || deleted.BAD_DEBT_ACCT || '|' || deleted.ACCT_SUMMARY_DAYS || '|' || deleted.INVOICE_COPY_DAYS;
    SET v_CQT_text=v_CQT_text || '|' || deleted.RETURNED_CHECK_COUNT || '|' || deleted.ACCEPT_CHECK_IF_PRIOR_BAD_CHECK;
    SET v_CQT_text=v_CQT_text || '|' || deleted.REF_STATEMENT_FORMAT_ID || '|' || deleted.STATEMENT_EMAIL_ADDRESS; 
    SET v_CQT_text=v_CQT_text || '|' || deleted.LAST_COD_DATE || '|' || deleted.NUM_OF_TIMES_COD;

    Insert Into customer_change_queue (customer_id,loc,action,trigger_source,deleted_value) 
			 Values (deleted.customer_id,deleted.loc,'D','CUSTOMER_AR',v_CQT_text);	 
  END IF;
END;


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

 CREATE  TRIGGER tr_bi_customer_ar
  BEFORE INSERT ON CUSTOMER_AR
  REFERENCING NEW AS inserted
  for each row BEGIN
DECLARE v_text varchar(254);
DECLARE i_return_status INTEGER;
  
  SET v_text=inserted.AR_TERMS_ID || '|' || inserted.CREDIT_LIMIT;
  SET v_text=v_text || '|' || inserted.CURRENT_BAL;
  
  i_return_status = CALL sp_log_file_insdel_activity (inserted.loc, 71,
      inserted.customer_id, NULL, NULL,
      v_text, 'A', inserted.last_modified_date, inserted.modified_by_employee_id, 1);
  IF i_return_status = -1 THEN
    SET inserted.modified_by_employee_id = NULL;
    SET inserted.last_modified_date = CURRENT TIMESTAMP;
    END IF;
    
  IF (((Select send_customer_domain from tams.store_profile) = 'Y') AND
      (Select count(*) from customer_change_queue where customer_id = inserted.CUSTOMER_ID 
       and loc = inserted.loc and validation_cd = 'T' and action = 'I' and trigger_source = 'CUSTOMER') = 0)
   THEN
          Insert Into customer_change_queue (customer_id,loc,action,trigger_source) 
  		Values (inserted.customer_id,inserted.loc,'I','CUSTOMER_AR');
   END IF;

  IF inserted.statement_customer_id IS NOT NULL AND inserted.statement_customer_id <> inserted.customer_id THEN
    IF EXISTS
      (SELECT customer_id
         FROM customer_ar
         WHERE customer_id = inserted.statement_customer_id
           AND statement_customer_id IS NOT NULL
           AND statement_customer_id <> customer_id)
    THEN
      RAISERROR 18500 '18500: tr_bi_customer_ar: STATEMENT_CUSTOMER_ID cannot reference a customer already assigned to another customer''s statement.';
    END IF;
    IF EXISTS
      (SELECT customer_id
         FROM customer_ar
         WHERE statement_customer_id = inserted.customer_id
           AND customer_id <> inserted.customer_id)
    THEN
      RAISERROR 18501 '18501: tr_bi_customer_ar: STATEMENT_CUSTOMER_ID cannot be set when customer is referenced by other customers.';
    END IF;
  END IF;

END;



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

CREATE  TRIGGER tr_bd_customer_blanket_po
  BEFORE DELETE ON CUSTOMER_BLANKET_PO
  REFERENCING OLD AS deleted
  for each row BEGIN
DECLARE v_text varchar(254);
DECLARE v_CQT_text varchar(1000);

  SET v_text=deleted.CUSTOMER_ID || '|' || deleted.BLANKET_PO_NUM;
  SET v_text=v_text || '|' || deleted.END_DATE || '|' || deleted.MAXIMUM_AMOUNT_ALLOWED;
  
  CALL sp_log_file_insdel_activity (deleted.loc, 108,
      deleted.id, NULL, NULL,
      v_text, 'D', deleted.last_modified_date, deleted.modified_by_employee_id, 1);
  
  IF (((Select send_customer_domain from tams.store_profile) = 'Y') AND
      (Select count(*) from customer_change_queue where customer_id = deleted.CUSTOMER_ID 
         and loc = deleted.loc and validation_cd = 'T' and action = 'D' and trigger_source = 'CUSTOMER') = 0)
  THEN
       SET v_CQT_text=deleted.CUSTOMER_ID || '|' || deleted.id || '|' || deleted.BLANKET_PO_NUM || '|' || deleted.BEGIN_DATE;
       SET v_CQT_text=v_CQT_text || '|' || deleted.END_DATE || '|' || deleted.MAXIMUM_AMOUNT_ALLOWED;
       SET v_CQT_text=v_CQT_text || '|' || deleted.LAST_MODIFIED_DATE || '|' || deleted.MODIFIED_BY_EMPLOYEE_ID;
       Insert Into customer_change_queue (customer_id,loc,action,trigger_source,deleted_value) 
			 Values (deleted.customer_id,deleted.loc,'D','CUSTOMER_BLANKET_PO',v_CQT_text);
  END IF;
END;



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

CREATE  TRIGGER tr_bd_customer_certification
  BEFORE DELETE ON CUSTOMER_CERTIFICATION
  REFERENCING OLD AS deleted
  for each row BEGIN
DECLARE v_text varchar(254);
DECLARE v_CQT_text varchar(1000);

  SET v_text=deleted.certification_part_group_id || '|' || deleted.technician_name;
  SET v_text=v_text || '|' || deleted.certificate_number || '|' || deleted.end_date;
  
  CALL sp_log_file_insdel_activity (deleted.loc, 677,
      deleted.id, deleted.customer_id, NULL,
      v_text, 'D', deleted.last_modified_date, deleted.modified_by_employee_id, 1);
 
  IF (((Select send_customer_domain from tams.store_profile) = 'Y') AND
      (Select count(*) from customer_change_queue where customer_id = deleted.CUSTOMER_ID 
         and loc = deleted.loc and validation_cd = 'T' and action = 'D' and trigger_source = 'CUSTOMER') = 0)
  THEN
       SET v_CQT_text=deleted.CUSTOMER_ID || '|' || deleted.id || '|' || deleted.certification_part_group_id || '|' || deleted.technician_name;
       SET v_CQT_text=v_CQT_text || '|' || deleted.BEGIN_DATE || '|' || deleted.END_DATE || '|' || deleted.certificate_number;
       SET v_CQT_text=v_CQT_text || '|' || deleted.LAST_MODIFIED_DATE || '|' || deleted.MODIFIED_BY_EMPLOYEE_ID;

       Insert Into customer_change_queue (customer_id,loc,action,trigger_source,deleted_value) 
			 Values (deleted.customer_id,deleted.loc,'D','CUSTOMER_CERTIFICATION',v_CQT_text);
  END IF; 
END;


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

Create  Trigger tr_aiu_customer_certification
After Insert, Update On tams.CUSTOMER_CERTIFICATION 
Referencing OLD As existed NEW as inserted
For Each Row
BEGIN
  If ((Select send_customer_domain from tams.store_profile) = 'Y') 
  THEN
     IF INSERTING THEN
       IF (Select count(*) from customer_change_queue where customer_id = inserted.CUSTOMER_ID 
         and loc = inserted.loc and validation_cd = 'T' and action = 'I' and trigger_source = 'CUSTOMER') = 0
       THEN
         Insert Into tams.customer_change_queue (customer_id,loc,action,trigger_source,deleted_value) 
	   Values (inserted.customer_id,inserted.loc,'I','CUSTOMER_CERTIFICATION',inserted.id)
       END IF
     ElseIf UPDATING THEN
       IF (Select count(*) from customer_change_queue where customer_id = existed.CUSTOMER_ID 
         and loc = existed.loc and validation_cd = 'T' and action = 'I' and trigger_source = 'CUSTOMER') = 0
       THEN
	  Insert Into customer_change_queue (customer_id,loc,action,trigger_source,deleted_value) 
	    Values (existed.customer_id,existed.loc,'U','CUSTOMER_CERTIFICATION',existed.id)
       END IF
     END IF
  END IF
End;


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

CREATE  TRIGGER tr_bd_customer_contact
  BEFORE DELETE ON CUSTOMER_CONTACT
  REFERENCING OLD AS deleted
  for each row BEGIN
DECLARE v_text varchar(254);
DECLARE v_CQT_text varchar(1000);
  SET v_text=deleted.CUSTOMER_ID || '|' || deleted.NAME;
  SET v_text=v_text || '|' || deleted.ADDRESS1 || '|' || deleted.PHONE;
  SET v_text=v_text || '|' || deleted.AUTHORIZED_BUYER;
  CALL sp_log_file_insdel_activity (deleted.loc, 124,
      deleted.id, NULL, NULL,
      v_text, 'D', deleted.last_modified_date, deleted.modified_by_employee_id, 1);
  IF (((Select send_customer_domain from tams.store_profile) = 'Y') AND
      (Select count(*) from customer_change_queue where customer_id = deleted.CUSTOMER_ID 
         and loc = deleted.loc and validation_cd = 'T' and action = 'D' and trigger_source = 'CUSTOMER') = 0)
  THEN
    SET v_CQT_text=deleted.CUSTOMER_ID || '|' || deleted.id || '|' || deleted.NAME || '|' || deleted.TITLE || '|' || deleted.PHONE;
    SET v_CQT_text=v_CQT_text || '|' || deleted.EXTENSION || '|' || deleted.CELL_PHONE || '|' || deleted.PAGER;
    SET v_CQT_text=v_CQT_text || '|' || deleted.HOME_PHONE || '|' || deleted.FAX_NUMBER || '|' || deleted.AUTHORIZED_BUYER;
    SET v_CQT_text=v_CQT_text || '|' || deleted.ADDRESS1 || '|' || deleted.ADDRESS2 || '|' || deleted.ADDRESS3 || '|' || deleted.ADDRESS4;
    SET v_CQT_text=v_CQT_text || '|' || deleted.CITY || '|' || deleted.REF_STATE_CD || '|' || deleted.POSTAL_CODE;
    SET v_CQT_text=v_CQT_text || '|' || deleted.REF_COUNTRY_CD || '|' || deleted.NOTE || '|' || deleted.EMAIL;
    SET v_CQT_text=v_CQT_text || '|' || deleted.LAST_MODIFIED_DATE || '|' || deleted.MODIFIED_BY_EMPLOYEE_ID;

    Insert Into customer_change_queue (customer_id,loc,action,trigger_source,deleted_value) 
			 Values (deleted.customer_id,deleted.loc,'D','CUSTOMER_CONTACT',v_CQT_text);	 
   END IF;
END;

END IF; -- End and restart the big IF statement, because Sybase doesn't like it too long.
IF b_upgrade = 1 THEN

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

Create  Trigger tr_aiu_customer_contact
After Insert, Update On tams.CUSTOMER_CONTACT
Referencing OLD As existed NEW as inserted
For Each Row
BEGIN
  If ((Select send_customer_domain from tams.store_profile) = 'Y')
  THEN
     IF INSERTING THEN
       IF (Select count(*) from customer_change_queue where customer_id = inserted.CUSTOMER_ID 
         and loc = inserted.loc and validation_cd = 'T' and action = 'I' and trigger_source = 'CUSTOMER') = 0
       THEN
	  Insert Into tams.customer_change_queue (customer_id,loc,action,trigger_source,deleted_value) 
	    Values (inserted.customer_id,inserted.loc,'I','CUSTOMER_CONTACT',inserted.id)
       END IF
      ElseIf UPDATING THEN 
       IF (Select count(*) from customer_change_queue where customer_id = existed.CUSTOMER_ID 
         and loc = existed.loc and validation_cd = 'T' and action = 'I' and trigger_source = 'CUSTOMER') = 0
       THEN
	  Insert Into customer_change_queue (customer_id,loc,action,trigger_source,deleted_value) 
	    Values (existed.customer_id,existed.loc,'U','CUSTOMER_CONTACT',existed.id)
       END IF
     END IF
  END IF
End;


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

CREATE  TRIGGER tr_bd_customer_delivery
  BEFORE DELETE ON CUSTOMER_DELIVERY
  REFERENCING OLD AS deleted
  for each row BEGIN
DECLARE v_text varchar(254);
DECLARE v_CQT_text varchar(1000);
  
  SET v_text=deleted.DELIVERY_REF_OPTION_CD || '|' || deleted.DELIVERY_ROUTE_CD;
  SET v_text=v_text || '|' || deleted.DELIVERY_PRIORITY_ID;
  
  CALL sp_log_file_insdel_activity (deleted.loc, 79,
      deleted.customer_id, NULL, NULL,
      v_text, 'D', deleted.last_modified_date, deleted.modified_by_employee_id, 1);

 IF (((Select send_customer_domain from tams.store_profile) = 'Y') AND
      (Select count(*) from customer_change_queue where customer_id = deleted.CUSTOMER_ID 
         and loc = deleted.loc and validation_cd = 'T' and action = 'D' and trigger_source = 'CUSTOMER') = 0)
 THEN
    SET v_CQT_text=deleted.CUSTOMER_ID || '|' || deleted.DELIVERY_REF_OPTION_CD || '|' || deleted.DELIVERY_ROUTE_CD; 
    SET v_CQT_text=v_CQT_text || '|' || deleted.DELIVERY_PRIORITY_ID || '|' || deleted.TRAVEL_TIME_CUSTOMER; 
    SET v_CQT_text=v_CQT_text || '|' || deleted.DISTANCE_TO_CUSTOMER || '|' || deleted.DIRECTIONS;
    SET v_CQT_text=v_CQT_text || '|' || deleted.MIN_AMT_FREE_DELIVERY || '|' || deleted.DELIVERY_CHARGE;
    
    Insert Into customer_change_queue (customer_id,loc,action,trigger_source,deleted_value) 
			 Values (deleted.customer_id,deleted.loc,'D','CUSTOMER_DELIVERY',v_CQT_text);	 
  END IF;
END;

END IF; -- End and restart the big IF statement, because Sybase doesn't like it too long.
IF b_upgrade = 1 THEN

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

Create  Trigger tr_aiu_customer_delivery
After Insert, Update On tams.CUSTOMER_DELIVERY
Referencing OLD As existed NEW as inserted
For Each Row
BEGIN
  IF ((Select send_customer_domain from tams.store_profile) = 'Y')
  THEN
     IF INSERTING THEN
       IF (Select count(*) from customer_change_queue where customer_id = inserted.CUSTOMER_ID 
         and loc = inserted.loc and validation_cd = 'T' and action = 'I' and trigger_source = 'CUSTOMER') = 0
       THEN
	 Insert Into tams.customer_change_queue (customer_id,loc,action,trigger_source) 
	   Values (inserted.customer_id,inserted.loc,'I','CUSTOMER_DELIVERY')
       END IF
      ElseIf UPDATING THEN 
       IF (Select count(*) from customer_change_queue where customer_id = existed.CUSTOMER_ID 
         and loc = existed.loc and validation_cd = 'T' and action = 'I' and trigger_source = 'CUSTOMER') = 0
       THEN
 	  Insert Into customer_change_queue (customer_id,loc,action,trigger_source) 
	    Values (existed.customer_id,existed.loc,'U','CUSTOMER_DELIVERY')
       END IF
     END IF
  END IF
End;


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

Create  Trigger tr_aiu_customer_blanket_po
After Insert, Update On tams.CUSTOMER_BLANKET_PO 
Referencing OLD As existed NEW as inserted
For Each Row
	
BEGIN
  IF ((Select send_customer_domain from tams.store_profile) = 'Y') 
  THEN
     IF INSERTING THEN
       IF (Select count(*) from customer_change_queue where customer_id = inserted.CUSTOMER_ID 
         and loc = inserted.loc and validation_cd = 'T' and action = 'I' and trigger_source = 'CUSTOMER') = 0
       THEN
		  Insert Into tams.customer_change_queue (customer_id,loc,action,trigger_source,deleted_value) 
			  Values (inserted.customer_id,inserted.loc,'I','CUSTOMER_BLANKET_PO',inserted.id)
       END IF	  
     ElseIf UPDATING THEN
       IF (Select count(*) from customer_change_queue where customer_id = existed.CUSTOMER_ID 
         and loc = existed.loc and validation_cd = 'T' and action = 'I' and trigger_source = 'CUSTOMER') = 0
       THEN
		  Insert Into customer_change_queue (customer_id,loc,action,trigger_source,deleted_value) 
			 Values (existed.customer_id,existed.loc,'U','CUSTOMER_BLANKET_PO',existed.id)
       END IF
     END IF
  END IF 
End;




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

CREATE  TRIGGER tr_bd_communication_directory
  BEFORE DELETE ON COMMUNICATION_DIRECTORY
  REFERENCING OLD AS deleted
  for each row BEGIN
DECLARE v_text varchar(254);
DECLARE v_CQT_text varchar(1000);

  -- Cascade delete from child tables. 
  delete from PHONE_ROOM_STORE
    where PHONE_ROOM_STORE.COMMUNICATION_DIRECTORY_ID = deleted.ID and
          PHONE_ROOM_STORE.LOC = deleted.LOC; 
  delete from PRICING_TRACKER_COMMUNICATION
    where PRICING_TRACKER_COMMUNICATION.COMMUNICATION_DIRECTORY_ID = deleted.ID and
          PRICING_TRACKER_COMMUNICATION.LOC = deleted.LOC; 
  delete from STORE_COMMUNICATION
    where STORE_COMMUNICATION.COMMUNICATION_DIRECTORY_ID = deleted.ID and
          STORE_COMMUNICATION.LOC = deleted.LOC; 
  delete from COMMUNICATION_USAGE
    where COMMUNICATION_USAGE.COMMUNICATION_DIRECTORY_ID = deleted.ID and
          COMMUNICATION_USAGE.LOC = deleted.LOC; 
  delete from COMMUNICATION_ACCESS_NUMBER
    where COMMUNICATION_ACCESS_NUMBER.COMMUNICATION_DIRECTORY_ID = deleted.ID and
          COMMUNICATION_ACCESS_NUMBER.LOC = deleted.LOC; 
  
  SET v_text=deleted.REF_COMMUNICATION_TYPE_ID || '|' || deleted.PASSWORD;
  
  CALL sp_log_file_insdel_activity (deleted.loc, 462,
      deleted.id, NULL, NULL,
      v_text, 'D', deleted.last_modified_date, deleted.modified_by_employee_id, 1);
 
  IF (MOD((SELECT use_site_relationship_feed
    FROM store_profile
    WHERE loc = deleted.loc),10) = 1 ) AND deleted.ref_communication_type_id = 2 THEN 
		INSERT INTO communication_directory_change_queue (id, communication_directory_id, loc, action)
        VALUES (NULL, deleted.id, deleted.loc, 'U' );
  END IF;
  
   If ((Select send_customer_domain from tams.store_profile) = 'Y') AND
      (Select count(*) from customer_change_queue where customer_id = deleted.INVOICE_TO_CUSTOMER_ID 
         and loc = deleted.loc and validation_cd = 'T' and action = 'D' and trigger_source = 'CUSTOMER') = 0
Then
     SET v_CQT_text=deleted.INVOICE_TO_CUSTOMER_ID || '|' || deleted.id || '|' || deleted.REF_COMMUNICATION_TYPE_ID || '|' || deleted.NAME;
     SET v_CQT_text=v_CQT_text || '|' || deleted.PASSWORD || '|' || deleted.LAST_LOGIN_DATE || '|' || deleted.TIMEOUT_MINUTES;
     SET v_CQT_text=v_CQT_text || '|' || deleted.ALLOW_DC_STOCK_CHECK  || '|' || deleted.PROLINK_ACCOUNT_NUMBER;
     SET v_CQT_text=v_CQT_text || '|' || deleted.DISPLAY_ON_HAND_DETAILS || '|' || deleted.STORE_NUMBER;
     SET v_CQT_text=v_CQT_text || '|' || deleted.LAST_MODIFIED_DATE || '|' || deleted.MODIFIED_BY_EMPLOYEE_ID;

     Insert Into customer_change_queue (customer_id,loc,action,trigger_source,deleted_value) 
			 Values (deleted.INVOICE_TO_CUSTOMER_ID,deleted.loc,'D','COMMUNICATION_DIRECTORY',v_CQT_text);	 
   END IF;

END;


END IF; -- End and restart the big IF statement, because Sybase doesn't like it too long.
IF b_upgrade = 1 THEN

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

CREATE  TRIGGER tr_bd_customer_statement_address
  BEFORE DELETE ON CUSTOMER_STATEMENT_ADDRESS
  REFERENCING OLD AS deleted
  for each row BEGIN
DECLARE v_text varchar(254);
DECLARE v_CQT_text varchar(1000);

  SET v_text=deleted.NAME;
  
  CALL sp_log_file_insdel_activity (deleted.loc, 70,
      deleted.customer_id, NULL, NULL,
      v_text, 'D', deleted.last_modified_date, deleted.modified_by_employee_id, 1);
      
  IF (((Select send_customer_domain from tams.store_profile) = 'Y') AND
      (Select count(*) from customer_change_queue where customer_id = deleted.CUSTOMER_ID 
         and loc = deleted.loc and validation_cd = 'T' and action = 'D' and trigger_source = 'CUSTOMER') = 0)
  THEN
    SET v_CQT_text=deleted.CUSTOMER_ID || '|' || deleted.NAME || '|' || deleted.ADDRESS1 || '|' || deleted.ADDRESS2;
    SET v_CQT_text=v_CQT_text || '|' || deleted.ADDRESS3 || '|' || deleted.ADDRESS4 || '|' || deleted.CITY;
    SET v_CQT_text=v_CQT_text || '|' || deleted.REF_STATE_CD || '|' || deleted.POSTAL_CODE || '|' || deleted.REF_COUNTRY_CD;
    
    Insert Into customer_change_queue (customer_id,loc,action,trigger_source,deleted_value) 
			 Values (deleted.customer_id,deleted.loc,'D','CUSTOMER_STATEMENT_ADDRESS',v_CQT_text);
  END IF;
END;


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

Create  Trigger tr_aiu_customer_statement_address
After Insert, Update On tams.CUSTOMER_STATEMENT_ADDRESS
Referencing OLD As existed NEW as inserted
For Each Row
BEGIN
  IF ((Select send_customer_domain from tams.store_profile) = 'Y') 
  THEN
     IF INSERTING THEN
       IF (Select count(*) from customer_change_queue where customer_id = inserted.CUSTOMER_ID 
         and loc = inserted.loc and validation_cd = 'T' and action = 'I' and trigger_source = 'CUSTOMER') = 0
       THEN
              Insert Into tams.customer_change_queue (customer_id,loc,action,trigger_source) 
		Values (inserted.customer_id,inserted.loc,'I','CUSTOMER_STATEMENT_ADDRESS')
       END IF
      ElseIf UPDATING then
       IF (Select count(*) from customer_change_queue where customer_id = existed.CUSTOMER_ID 
         and loc = existed.loc and validation_cd = 'T' and action = 'I' and trigger_source = 'CUSTOMER') = 0
       THEN
	      Insert Into customer_change_queue (customer_id,loc,action,trigger_source) 
		 Values (existed.customer_id,existed.loc,'U','CUSTOMER_STATEMENT_ADDRESS')
       END IF
     END IF
  END IF 
End;

--End STEL-2194
--
-- 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;