
END;
/


/**********************************************************************
Step 3 to Step 7.A will be repeated for each table
**********************************************************************/

/*
XC_DATA1
*/

/*
Step 3 
If the target table does not already contain a timestamp field, then add an additional column to your table to record the timestamp value when a row is inserted or updated. You must use the ALTER_MASTER_REPOBJECT procedure to apply the DDL to the target table. Simply issuing the DDL may cause the replicated object to become invalid.
Add a site column to your table to store the site value. Use the DBMS_REPCAT.ALTER_MASTER_REPOBJECT procedure to apply the DDL to the target table. Simply issuing the DDL may cause the replicated object to become invalid.
*/

BEGIN
   DBMS_REPCAT.ALTER_MASTER_REPOBJECT (
      sname => 'XCManager',
      oname => 'XC_DATA1',
      type => 'TABLE',
      ddl_text => 'ALTER TABLE XCManager.XC_DATA1 ADD (TIME_STAMP DATE)');
END;
/

BEGIN
   DBMS_REPCAT.ALTER_MASTER_REPOBJECT (
      sname => 'XCManager',
      oname => 'XC_DATA1',
      type => 'TABLE',
      ddl_text => 'ALTER TABLE XCManager.XC_DATA1 ADD (REP_SITE VARCHAR2(20))');
END;
/


/*
Step 4 Regenerate replication support for altered table.
*/

BEGIN 
    DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
      sname => 'XCManager',
      oname => 'XC_DATA1', 
      type => 'TABLE',
      min_communication => TRUE); 
END;
/


/*
Step 5 Create a trigger that records the timestamp and rep_site when a row is either inserted or updated. This recorded value is used in the resolution of conflicts based on the Timestamp method. Instead of directly executing the DDL, you should use the DBMS_REPCAT.CREATE_MASTER_REPOBJECT procedure to create the trigger and add it to your master group.

--------------------------------------------------------------------------------
Note: 
You cannot use columns of datetime and interval datatypes for priority group conflict resolution.

--------------------------------------------------------------------------------
*/

BEGIN
   DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
      gname => 'XCREP',
      type => 'TRIGGER',
      oname => 'XC_DATA1_REP_TRG',
      sname => 'XCManager',
      ddl_text => 'CREATE TRIGGER XCManager.XC_DATA1_REP_TRG
                      BEFORE
                         INSERT OR UPDATE ON XCManager.XC_DATA1 FOR EACH ROW
                      BEGIN
                         IF DBMS_REPUTIL.FROM_REMOTE = FALSE THEN
                            :NEW.TIME_STAMP := SYSDATE;
                            SELECT global_name INTO :NEW.REP_SITE FROM GLOBAL_NAME;
                         END IF;
                      END;');
END;
/


/*
Step 6 Create a column group for your target table by using the DBMS_REPCAT.MAKE_COLUMN_GROUP procedure. All Oracle conflict resolution methods are based on logical column groupings called column groups.
*/

BEGIN
   DBMS_REPCAT.MAKE_COLUMN_GROUP (
      sname => 'XCManager',
      oname => 'XC_DATA1',
      column_group => 'XC_DATA1_CG',
      list_of_column_names => '*');
END;
/


/*
Step 7.A Use the DBMS_REPCAT.ADD_UPDATE_RESOLUTION procedure to define the conflict resolution method for a specified table. This example specifies the LATEST TIMESTAMP conflict resolution method using the timestamp column that you created earlier.
*/

BEGIN
   DBMS_REPCAT.ADD_UPDATE_RESOLUTION (
      sname => 'XCManager',
      oname => 'XC_DATA1',
      column_group => 'XC_DATA1_CG',
      sequence_no => 1,
      method => 'LATEST TIMESTAMP',
      parameter_column_name => 'TIME_STAMP');
END;
/


/*
XC_SITES
*/

/*
Step 3 
If the target table does not already contain a timestamp field, then add an additional column to your table to record the timestamp value when a row is inserted or updated. You must use the ALTER_MASTER_REPOBJECT procedure to apply the DDL to the target table. Simply issuing the DDL may cause the replicated object to become invalid.
Add a site column to your table to store the site value. Use the DBMS_REPCAT.ALTER_MASTER_REPOBJECT procedure to apply the DDL to the target table. Simply issuing the DDL may cause the replicated object to become invalid.
*/

BEGIN
   DBMS_REPCAT.ALTER_MASTER_REPOBJECT (
      sname => 'XCManager',
      oname => 'XC_SITES',
      type => 'TABLE',
      ddl_text => 'ALTER TABLE XCManager.XC_SITES ADD (TIME_STAMP DATE)');
END;
/

BEGIN
   DBMS_REPCAT.ALTER_MASTER_REPOBJECT (
      sname => 'XCManager',
      oname => 'XC_SITES',
      type => 'TABLE',
      ddl_text => 'ALTER TABLE XCManager.XC_SITES ADD (REP_SITE VARCHAR2(20))');
END;
/


/*
Step 4 Regenerate replication support for altered table.
*/

BEGIN 
    DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
      sname => 'XCManager',
      oname => 'XC_SITES', 
      type => 'TABLE',
      min_communication => TRUE); 
END;
/


/*
Step 5 Create a trigger that records the timestamp and rep_site when a row is either inserted or updated. This recorded value is used in the resolution of conflicts based on the Timestamp method. Instead of directly executing the DDL, you should use the DBMS_REPCAT.CREATE_MASTER_REPOBJECT procedure to create the trigger and add it to your master group.

--------------------------------------------------------------------------------
Note: 
You cannot use columns of datetime and interval datatypes for priority group conflict resolution.

--------------------------------------------------------------------------------
*/

BEGIN
   DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
      gname => 'XCREP',
      type => 'TRIGGER',
      oname => 'XC_SITES_REP_TRG',
      sname => 'XCManager',
      ddl_text => 'CREATE TRIGGER XCManager.XC_SITES_REP_TRG
                      BEFORE
                         INSERT OR UPDATE ON XCManager.XC_SITES FOR EACH ROW
                      BEGIN
                         IF DBMS_REPUTIL.FROM_REMOTE = FALSE THEN
                            :NEW.TIME_STAMP := SYSDATE;
                            SELECT global_name INTO :NEW.REP_SITE FROM GLOBAL_NAME;
                         END IF;
                      END;');
END;
/


/*
Step 6 Create a column group for your target table by using the DBMS_REPCAT.MAKE_COLUMN_GROUP procedure. All Oracle conflict resolution methods are based on logical column groupings called column groups.
*/

BEGIN
   DBMS_REPCAT.MAKE_COLUMN_GROUP (
      sname => 'XCManager',
      oname => 'XC_SITES',
      column_group => 'XC_SITES_CG',
      list_of_column_names => '*');
END;
/


/*
Step 7.A Use the DBMS_REPCAT.ADD_UPDATE_RESOLUTION procedure to define the conflict resolution method for a specified table. This example specifies the LATEST TIMESTAMP conflict resolution method using the timestamp column that you created earlier.
*/

BEGIN
   DBMS_REPCAT.ADD_UPDATE_RESOLUTION (
      sname => 'XCManager',
      oname => 'XC_SITES',
      column_group => 'XC_SITES_CG',
      sequence_no => 1,
      method => 'LATEST TIMESTAMP',
      parameter_column_name => 'TIME_STAMP');
END;
/


/*
XC_SITESENSORS
*/

/*
Step 3 
If the target table does not already contain a timestamp field, then add an additional column to your table to record the timestamp value when a row is inserted or updated. You must use the ALTER_MASTER_REPOBJECT procedure to apply the DDL to the target table. Simply issuing the DDL may cause the replicated object to become invalid.
Add a site column to your table to store the site value. Use the DBMS_REPCAT.ALTER_MASTER_REPOBJECT procedure to apply the DDL to the target table. Simply issuing the DDL may cause the replicated object to become invalid.
*/

BEGIN
   DBMS_REPCAT.ALTER_MASTER_REPOBJECT (
      sname => 'XCManager',
      oname => 'XC_SITESENSORS',
      type => 'TABLE',
      ddl_text => 'ALTER TABLE XCManager.XC_SITESENSORS ADD (TIME_STAMP DATE)');
END;
/

BEGIN
   DBMS_REPCAT.ALTER_MASTER_REPOBJECT (
      sname => 'XCManager',
      oname => 'XC_SITESENSORS',
      type => 'TABLE',
      ddl_text => 'ALTER TABLE XCManager.XC_SITESENSORS ADD (REP_SITE VARCHAR2(20))');
END;
/


/*
Step 4 Regenerate replication support for altered table.
*/

BEGIN 
    DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
      sname => 'XCManager',
      oname => 'XC_SITESENSORS', 
      type => 'TABLE',
      min_communication => TRUE); 
END;
/


/*
Step 5 Create a trigger that records the timestamp and rep_site when a row is either inserted or updated. This recorded value is used in the resolution of conflicts based on the Timestamp method. Instead of directly executing the DDL, you should use the DBMS_REPCAT.CREATE_MASTER_REPOBJECT procedure to create the trigger and add it to your master group.

--------------------------------------------------------------------------------
Note: 
You cannot use columns of datetime and interval datatypes for priority group conflict resolution.

--------------------------------------------------------------------------------
*/

BEGIN
   DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
      gname => 'XCREP',
      type => 'TRIGGER',
      oname => 'XC_SITESENSORS_REP_TRG',
      sname => 'XCManager',
      ddl_text => 'CREATE TRIGGER XCManager.XC_SITESENSORS_REP_TRG
                      BEFORE
                         INSERT OR UPDATE ON XCManager.XC_SITESENSORS FOR EACH ROW
                      BEGIN
                         IF DBMS_REPUTIL.FROM_REMOTE = FALSE THEN
                            :NEW.TIME_STAMP := SYSDATE;
                            SELECT global_name INTO :NEW.REP_SITE FROM GLOBAL_NAME;
                         END IF;
                      END;');
END;
/


/*
Step 6 Create a column group for your target table by using the DBMS_REPCAT.MAKE_COLUMN_GROUP procedure. All Oracle conflict resolution methods are based on logical column groupings called column groups.
*/

BEGIN
   DBMS_REPCAT.MAKE_COLUMN_GROUP (
      sname => 'XCManager',
      oname => 'XC_SITESENSORS',
      column_group => 'XC_SITESENSORS_CG',
      list_of_column_names => '*');
END;
/


/*
Step 7.A Use the DBMS_REPCAT.ADD_UPDATE_RESOLUTION procedure to define the conflict resolution method for a specified table. This example specifies the LATEST TIMESTAMP conflict resolution method using the timestamp column that you created earlier.
*/

BEGIN
   DBMS_REPCAT.ADD_UPDATE_RESOLUTION (
      sname => 'XCManager',
      oname => 'XC_SITESENSORS',
      column_group => 'XC_SITESENSORS_CG',
      sequence_no => 1,
      method => 'LATEST TIMESTAMP',
      parameter_column_name => 'TIME_STAMP');
END;
/

/*
XC_GOESQC
*/

/*
Step 3 
If the target table does not already contain a timestamp field, then add an additional column to your table to record the timestamp value when a row is inserted or updated. You must use the ALTER_MASTER_REPOBJECT procedure to apply the DDL to the target table. Simply issuing the DDL may cause the replicated object to become invalid.
Add a site column to your table to store the site value. Use the DBMS_REPCAT.ALTER_MASTER_REPOBJECT procedure to apply the DDL to the target table. Simply issuing the DDL may cause the replicated object to become invalid.
*/

BEGIN
   DBMS_REPCAT.ALTER_MASTER_REPOBJECT (
      sname => 'XCManager',
      oname => 'XC_GOESQC',
      type => 'TABLE',
      ddl_text => 'ALTER TABLE XCManager.XC_GOESQC ADD (TIME_STAMP DATE)');
END;
/

BEGIN
   DBMS_REPCAT.ALTER_MASTER_REPOBJECT (
      sname => 'XCManager',
      oname => 'XC_GOESQC',
      type => 'TABLE',
      ddl_text => 'ALTER TABLE XCManager.XC_GOESQC ADD (REP_SITE VARCHAR2(20))');
END;
/


/*
Step 4 Regenerate replication support for altered table.
*/

BEGIN 
    DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
      sname => 'XCManager',
      oname => 'XC_GOESQC', 
      type => 'TABLE',
      min_communication => TRUE); 
END;
/


/*
Step 5 Create a trigger that records the timestamp and rep_site when a row is either inserted or updated. This recorded value is used in the resolution of conflicts based on the Timestamp method. Instead of directly executing the DDL, you should use the DBMS_REPCAT.CREATE_MASTER_REPOBJECT procedure to create the trigger and add it to your master group.

--------------------------------------------------------------------------------
Note: 
You cannot use columns of datetime and interval datatypes for priority group conflict resolution.

--------------------------------------------------------------------------------
*/

BEGIN
   DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
      gname => 'XCREP',
      type => 'TRIGGER',
      oname => 'XC_GOESQC_REP_TRG',
      sname => 'XCManager',
      ddl_text => 'CREATE TRIGGER XCManager.XC_GOESQC_REP_TRG
                      BEFORE
                         INSERT OR UPDATE ON XCManager.XC_GOESQC FOR EACH ROW
                      BEGIN
                         IF DBMS_REPUTIL.FROM_REMOTE = FALSE THEN
                            :NEW.TIME_STAMP := SYSDATE;
                            SELECT global_name INTO :NEW.REP_SITE FROM GLOBAL_NAME;
                         END IF;
                      END;');
END;
/


/*
Step 6 Create a column group for your target table by using the DBMS_REPCAT.MAKE_COLUMN_GROUP procedure. All Oracle conflict resolution methods are based on logical column groupings called column groups.
*/

BEGIN
   DBMS_REPCAT.MAKE_COLUMN_GROUP (
      sname => 'XCManager',
      oname => 'XC_GOESQC',
      column_group => 'XC_GOESQC_CG',
      list_of_column_names => '*');
END;
/


/*
Step 7.A Use the DBMS_REPCAT.ADD_UPDATE_RESOLUTION procedure to define the conflict resolution method for a specified table. This example specifies the LATEST TIMESTAMP conflict resolution method using the timestamp column that you created earlier.
*/

BEGIN
   DBMS_REPCAT.ADD_UPDATE_RESOLUTION (
      sname => 'XCManager',
      oname => 'XC_GOESQC',
      column_group => 'XC_GOESQC_CG',
      sequence_no => 1,
      method => 'LATEST TIMESTAMP',
      parameter_column_name => 'TIME_STAMP');
END;
/


/*
XC_RTUQC
*/

/*
Step 3 
If the target table does not already contain a timestamp field, then add an additional column to your table to record the timestamp value when a row is inserted or updated. You must use the ALTER_MASTER_REPOBJECT procedure to apply the DDL to the target table. Simply issuing the DDL may cause the replicated object to become invalid.
Add a site column to your table to store the site value. Use the DBMS_REPCAT.ALTER_MASTER_REPOBJECT procedure to apply the DDL to the target table. Simply issuing the DDL may cause the replicated object to become invalid.
*/

BEGIN
   DBMS_REPCAT.ALTER_MASTER_REPOBJECT (
      sname => 'XCManager',
      oname => 'XC_RTUQC',
      type => 'TABLE',
      ddl_text => 'ALTER TABLE XCManager.XC_RTUQC ADD (TIME_STAMP DATE)');
END;
/

BEGIN
   DBMS_REPCAT.ALTER_MASTER_REPOBJECT (
      sname => 'XCManager',
      oname => 'XC_RTUQC',
      type => 'TABLE',
      ddl_text => 'ALTER TABLE XCManager.XC_RTUQC ADD (REP_SITE VARCHAR2(20))');
END;
/


/*
Step 4 Regenerate replication support for altered table.
*/

BEGIN 
    DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
      sname => 'XCManager',
      oname => 'XC_RTUQC', 
      type => 'TABLE',
      min_communication => TRUE); 
END;
/


/*
Step 5 Create a trigger that records the timestamp and rep_site when a row is either inserted or updated. This recorded value is used in the resolution of conflicts based on the Timestamp method. Instead of directly executing the DDL, you should use the DBMS_REPCAT.CREATE_MASTER_REPOBJECT procedure to create the trigger and add it to your master group.

--------------------------------------------------------------------------------
Note: 
You cannot use columns of datetime and interval datatypes for priority group conflict resolution.

--------------------------------------------------------------------------------
*/

BEGIN
   DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
      gname => 'XCREP',
      type => 'TRIGGER',
      oname => 'XC_RTUQC_REP_TRG',
      sname => 'XCManager',
      ddl_text => 'CREATE TRIGGER XCManager.XC_RTUQC_REP_TRG
                      BEFORE
                         INSERT OR UPDATE ON XCManager.XC_RTUQC FOR EACH ROW
                      BEGIN
                         IF DBMS_REPUTIL.FROM_REMOTE = FALSE THEN
                            :NEW.TIME_STAMP := SYSDATE;
                            SELECT global_name INTO :NEW.REP_SITE FROM GLOBAL_NAME;
                         END IF;
                      END;');
END;
/


/*
Step 6 Create a column group for your target table by using the DBMS_REPCAT.MAKE_COLUMN_GROUP procedure. All Oracle conflict resolution methods are based on logical column groupings called column groups.
*/

BEGIN
   DBMS_REPCAT.MAKE_COLUMN_GROUP (
      sname => 'XCManager',
      oname => 'XC_RTUQC',
      column_group => 'XC_RTUQC_CG',
      list_of_column_names => '*');
END;
/


/*
Step 7.A Use the DBMS_REPCAT.ADD_UPDATE_RESOLUTION procedure to define the conflict resolution method for a specified table. This example specifies the LATEST TIMESTAMP conflict resolution method using the timestamp column that you created earlier.
*/

BEGIN
   DBMS_REPCAT.ADD_UPDATE_RESOLUTION (
      sname => 'XCManager',
      oname => 'XC_RTUQC',
      column_group => 'XC_RTUQC_CG',
      sequence_no => 1,
      method => 'LATEST TIMESTAMP',
      parameter_column_name => 'TIME_STAMP');
END;
/


/*
XC_TXTDATA1
*/

/*
Step 3 
If the target table does not already contain a timestamp field, then add an additional column to your table to record the timestamp value when a row is inserted or updated. You must use the ALTER_MASTER_REPOBJECT procedure to apply the DDL to the target table. Simply issuing the DDL may cause the replicated object to become invalid.
Add a site column to your table to store the site value. Use the DBMS_REPCAT.ALTER_MASTER_REPOBJECT procedure to apply the DDL to the target table. Simply issuing the DDL may cause the replicated object to become invalid.
*/

BEGIN
   DBMS_REPCAT.ALTER_MASTER_REPOBJECT (
      sname => 'XCManager',
      oname => 'XC_TXTDATA1',
      type => 'TABLE',
      ddl_text => 'ALTER TABLE XCManager.XC_TXTDATA1 ADD (TIME_STAMP DATE)');
END;
/

BEGIN
   DBMS_REPCAT.ALTER_MASTER_REPOBJECT (
      sname => 'XCManager',
      oname => 'XC_TXTDATA1',
      type => 'TABLE',
      ddl_text => 'ALTER TABLE XCManager.XC_TXTDATA1 ADD (REP_SITE VARCHAR2(20))');
END;
/


/*
Step 4 Regenerate replication support for altered table.
*/

BEGIN 
    DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
      sname => 'XCManager',
      oname => 'XC_TXTDATA1', 
      type => 'TABLE',
      min_communication => TRUE); 
END;
/


/*
Step 5 Create a trigger that records the timestamp and rep_site when a row is either inserted or updated. This recorded value is used in the resolution of conflicts based on the Timestamp method. Instead of directly executing the DDL, you should use the DBMS_REPCAT.CREATE_MASTER_REPOBJECT procedure to create the trigger and add it to your master group.

--------------------------------------------------------------------------------
Note: 
You cannot use columns of datetime and interval datatypes for priority group conflict resolution.

--------------------------------------------------------------------------------
*/

BEGIN
   DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
      gname => 'XCREP',
      type => 'TRIGGER',
      oname => 'XC_TXTDATA1_REP_TRG',
      sname => 'XCManager',
      ddl_text => 'CREATE TRIGGER XCManager.XC_TXTDATA1_REP_TRG
                      BEFORE
                         INSERT OR UPDATE ON XCManager.XC_TXTDATA1 FOR EACH ROW
                      BEGIN
                         IF DBMS_REPUTIL.FROM_REMOTE = FALSE THEN
                            :NEW.TIME_STAMP := SYSDATE;
                            SELECT global_name INTO :NEW.REP_SITE FROM GLOBAL_NAME;
                         END IF;
                      END;');
END;
/


/*
Step 6 Create a column group for your target table by using the DBMS_REPCAT.MAKE_COLUMN_GROUP procedure. All Oracle conflict resolution methods are based on logical column groupings called column groups.
*/

BEGIN
   DBMS_REPCAT.MAKE_COLUMN_GROUP (
      sname => 'XCManager',
      oname => 'XC_TXTDATA1',
      column_group => 'XC_TXTDATA1_CG',
      list_of_column_names => '*');
END;
/


/*
Step 7.A Use the DBMS_REPCAT.ADD_UPDATE_RESOLUTION procedure to define the conflict resolution method for a specified table. This example specifies the LATEST TIMESTAMP conflict resolution method using the timestamp column that you created earlier.
*/

BEGIN
   DBMS_REPCAT.ADD_UPDATE_RESOLUTION (
      sname => 'XCManager',
      oname => 'XC_TXTDATA1',
      column_group => 'XC_TXTDATA1_CG',
      sequence_no => 1,
      method => 'LATEST TIMESTAMP',
      parameter_column_name => 'TIME_STAMP');
END;
/


/*
XC_PPLIST
*/

/*
Step 3 
If the target table does not already contain a timestamp field, then add an additional column to your table to record the timestamp value when a row is inserted or updated. You must use the ALTER_MASTER_REPOBJECT procedure to apply the DDL to the target table. Simply issuing the DDL may cause the replicated object to become invalid.
Add a site column to your table to store the site value. Use the DBMS_REPCAT.ALTER_MASTER_REPOBJECT procedure to apply the DDL to the target table. Simply issuing the DDL may cause the replicated object to become invalid.
*/

BEGIN
   DBMS_REPCAT.ALTER_MASTER_REPOBJECT (
      sname => 'XCManager',
      oname => 'XC_PPLIST',
      type => 'TABLE',
      ddl_text => 'ALTER TABLE XCManager.XC_PPLIST ADD (TIME_STAMP DATE)');
END;
/

BEGIN
   DBMS_REPCAT.ALTER_MASTER_REPOBJECT (
      sname => 'XCManager',
      oname => 'XC_PPLIST',
      type => 'TABLE',
      ddl_text => 'ALTER TABLE XCManager.XC_PPLIST ADD (REP_SITE VARCHAR2(20))');
END;
/


/*
Step 4 Regenerate replication support for altered table.
*/

BEGIN 
    DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
      sname => 'XCManager',
      oname => 'XC_PPLIST', 
      type => 'TABLE',
      min_communication => TRUE); 
END;
/


/*
Step 5 Create a trigger that records the timestamp and rep_site when a row is either inserted or updated. This recorded value is used in the resolution of conflicts based on the Timestamp method. Instead of directly executing the DDL, you should use the DBMS_REPCAT.CREATE_MASTER_REPOBJECT procedure to create the trigger and add it to your master group.

--------------------------------------------------------------------------------
Note: 
You cannot use columns of datetime and interval datatypes for priority group conflict resolution.

--------------------------------------------------------------------------------
*/

BEGIN
   DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
      gname => 'XCREP',
      type => 'TRIGGER',
      oname => 'XC_PPLIST_REP_TRG',
      sname => 'XCManager',
      ddl_text => 'CREATE TRIGGER XCManager.XC_PPLIST_REP_TRG
                      BEFORE
                         INSERT OR UPDATE ON XCManager.XC_PPLIST FOR EACH ROW
                      BEGIN
                         IF DBMS_REPUTIL.FROM_REMOTE = FALSE THEN
                            :NEW.TIME_STAMP := SYSDATE;
                            SELECT global_name INTO :NEW.REP_SITE FROM GLOBAL_NAME;
                         END IF;
                      END;');
END;
/


/*
Step 6 Create a column group for your target table by using the DBMS_REPCAT.MAKE_COLUMN_GROUP procedure. All Oracle conflict resolution methods are based on logical column groupings called column groups.
*/

BEGIN
   DBMS_REPCAT.MAKE_COLUMN_GROUP (
      sname => 'XCManager',
      oname => 'XC_PPLIST',
      column_group => 'XC_PPLIST_CG',
      list_of_column_names => '*');
END;
/


/*
Step 7.A Use the DBMS_REPCAT.ADD_UPDATE_RESOLUTION procedure to define the conflict resolution method for a specified table. This example specifies the LATEST TIMESTAMP conflict resolution method using the timestamp column that you created earlier.
*/

BEGIN
   DBMS_REPCAT.ADD_UPDATE_RESOLUTION (
      sname => 'XCManager',
      oname => 'XC_PPLIST',
      column_group => 'XC_PPLIST_CG',
      sequence_no => 1,
      method => 'LATEST TIMESTAMP',
      parameter_column_name => 'TIME_STAMP');
END;
/


/*
XC_PPDATA1
*/

/*
Step 3 
If the target table does not already contain a timestamp field, then add an additional column to your table to record the timestamp value when a row is inserted or updated. You must use the ALTER_MASTER_REPOBJECT procedure to apply the DDL to the target table. Simply issuing the DDL may cause the replicated object to become invalid.
Add a site column to your table to store the site value. Use the DBMS_REPCAT.ALTER_MASTER_REPOBJECT procedure to apply the DDL to the target table. Simply issuing the DDL may cause the replicated object to become invalid.
*/

BEGIN
   DBMS_REPCAT.ALTER_MASTER_REPOBJECT (
      sname => 'XCManager',
      oname => 'XC_PPDATA1',
      type => 'TABLE',
      ddl_text => 'ALTER TABLE XCManager.XC_PPDATA1 ADD (TIME_STAMP DATE)');
END;
/

BEGIN
   DBMS_REPCAT.ALTER_MASTER_REPOBJECT (
      sname => 'XCManager',
      oname => 'XC_PPDATA1',
      type => 'TABLE',
      ddl_text => 'ALTER TABLE XCManager.XC_PPDATA1 ADD (REP_SITE VARCHAR2(20))');
END;
/


/*
Step 4 Regenerate replication support for altered table.
*/

BEGIN 
    DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
      sname => 'XCManager',
      oname => 'XC_PPDATA1', 
      type => 'TABLE',
      min_communication => TRUE); 
END;
/


/*
Step 5 Create a trigger that records the timestamp and rep_site when a row is either inserted or updated. This recorded value is used in the resolution of conflicts based on the Timestamp method. Instead of directly executing the DDL, you should use the DBMS_REPCAT.CREATE_MASTER_REPOBJECT procedure to create the trigger and add it to your master group.

--------------------------------------------------------------------------------
Note: 
You cannot use columns of datetime and interval datatypes for priority group conflict resolution.

--------------------------------------------------------------------------------
*/

BEGIN
   DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
      gname => 'XCREP',
      type => 'TRIGGER',
      oname => 'XC_PPDATA1_REP_TRG',
      sname => 'XCManager',
      ddl_text => 'CREATE TRIGGER XCManager.XC_PPDATA1_REP_TRG
                      BEFORE
                         INSERT OR UPDATE ON XCManager.XC_PPDATA1 FOR EACH ROW
                      BEGIN
                         IF DBMS_REPUTIL.FROM_REMOTE = FALSE THEN
                            :NEW.TIME_STAMP := SYSDATE;
                            SELECT global_name INTO :NEW.REP_SITE FROM GLOBAL_NAME;
                         END IF;
                      END;');
END;
/


/*
Step 6 Create a column group for your target table by using the DBMS_REPCAT.MAKE_COLUMN_GROUP procedure. All Oracle conflict resolution methods are based on logical column groupings called column groups.
*/

BEGIN
   DBMS_REPCAT.MAKE_COLUMN_GROUP (
      sname => 'XCManager',
      oname => 'XC_PPDATA1',
      column_group => 'XC_PPDATA1_CG',
      list_of_column_names => '*');
END;
/


/*
Step 7.A Use the DBMS_REPCAT.ADD_UPDATE_RESOLUTION procedure to define the conflict resolution method for a specified table. This example specifies the LATEST TIMESTAMP conflict resolution method using the timestamp column that you created earlier.
*/

BEGIN
   DBMS_REPCAT.ADD_UPDATE_RESOLUTION (
      sname => 'XCManager',
      oname => 'XC_PPDATA1',
      column_group => 'XC_PPDATA1_CG',
      sequence_no => 1,
      method => 'LATEST TIMESTAMP',
      parameter_column_name => 'TIME_STAMP');
END;
/


/**********************************************************************
Step 7.B to 7.C are global
**********************************************************************/

/*
Step 7.B Before assigning a site priority value to the sites in your replicated environment, create a site priority group that holds the values you defined.
*/

BEGIN
   DBMS_REPCAT.DEFINE_SITE_PRIORITY (
      gname => 'XCREP',
      name => 'XC_GLOBAL_SITE_PG');
END;
/


/*
Step 7.C Define the priority value for each of the sites in your replication environment using the DBMS_REPCAT.ADD_SITE_PRIORITY_SITE procedure. Execute this procedure as often as necessary until you have defined a site priority value for each of the sites in our replication environment.
*/

BEGIN
   DBMS_REPCAT.ADD_SITE_PRIORITY_SITE (
      gname => 'XCREP',
      name => 'XC_GLOBAL_SITE_PG',
      site => 'XCMAS.WORKGROUP.COM',
      priority => 1000);
END;
/

BEGIN
   DBMS_REPCAT.ADD_SITE_PRIORITY_SITE (
      gname => 'XCREP',
      name => 'XC_GLOBAL_SITE_PG',
      site => 'XCSLV.WORKGROUP.COM',
      priority => 500);
END;
/


/**********************************************************************
Step 7.D to 8 will be repeated for each table
**********************************************************************/

/*
Step 7.D After assigning your site priority values, add the SITE PRIORITY resolution method to your replicated table. The following example shows that it is the third conflict resolution method for the specified column group (sequence_no parameter).
*/

BEGIN
   DBMS_REPCAT.ADD_UPDATE_RESOLUTION (
      sname => 'XCManager',
      oname => 'XC_DATA1',
      column_group => 'XC_DATA1_CG',
      sequence_no => 2,
      method => 'SITE PRIORITY',
      parameter_column_name => 'REP_SITE',
      priority_group => 'XC_GLOBAL_SITE_PG');
END;
/


/*
Step 8 Regenerate replication support for the table that received the conflict resolution method.
*/

BEGIN 
    DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
      sname => 'XCManager',
      oname => 'XC_DATA1', 
      type => 'TABLE',
      min_communication => TRUE); 
END;
/

/*
Step 7.D After assigning your site priority values, add the SITE PRIORITY resolution method to your replicated table. The following example shows that it is the third conflict resolution method for the specified column group (sequence_no parameter).
*/

BEGIN
   DBMS_REPCAT.ADD_UPDATE_RESOLUTION (
      sname => 'XCManager',
      oname => 'XC_SITES',
      column_group => 'XC_SITES_CG',
      sequence_no => 2,
      method => 'SITE PRIORITY',
      parameter_column_name => 'REP_SITE',
      priority_group => 'XC_GLOBAL_SITE_PG');
END;
/


/*
Step 8 Regenerate replication support for the table that received the conflict resolution method.
*/

BEGIN 
    DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
      sname => 'XCManager',
      oname => 'XC_SITES', 
      type => 'TABLE',
      min_communication => TRUE); 
END;
/

/*
Step 7.D After assigning your site priority values, add the SITE PRIORITY resolution method to your replicated table. The following example shows that it is the third conflict resolution method for the specified column group (sequence_no parameter).
*/

BEGIN
   DBMS_REPCAT.ADD_UPDATE_RESOLUTION (
      sname => 'XCManager',
      oname => 'XC_SITESENSORS',
      column_group => 'XC_SITESENSORS_CG',
      sequence_no => 2,
      method => 'SITE PRIORITY',
      parameter_column_name => 'REP_SITE',
      priority_group => 'XC_GLOBAL_SITE_PG');
END;
/


/*
Step 8 Regenerate replication support for the table that received the conflict resolution method.
*/

BEGIN 
    DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
      sname => 'XCManager',
      oname => 'XC_SITESENSORS', 
      type => 'TABLE',
      min_communication => TRUE); 
END;
/

/*
Step 7.D After assigning your site priority values, add the SITE PRIORITY resolution method to your replicated table. The following example shows that it is the third conflict resolution method for the specified column group (sequence_no parameter).
*/

BEGIN
   DBMS_REPCAT.ADD_UPDATE_RESOLUTION (
      sname => 'XCManager',
      oname => 'XC_GOESQC',
      column_group => 'XC_GOESQC_CG',
      sequence_no => 2,
      method => 'SITE PRIORITY',
      parameter_column_name => 'REP_SITE',
      priority_group => 'XC_GLOBAL_SITE_PG');
END;
/


/*
Step 8 Regenerate replication support for the table that received the conflict resolution method.
*/

BEGIN 
    DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
      sname => 'XCManager',
      oname => 'XC_GOESQC', 
      type => 'TABLE',
      min_communication => TRUE); 
END;
/

/*
Step 7.D After assigning your site priority values, add the SITE PRIORITY resolution method to your replicated table. The following example shows that it is the third conflict resolution method for the specified column group (sequence_no parameter).
*/

BEGIN
   DBMS_REPCAT.ADD_UPDATE_RESOLUTION (
      sname => 'XCManager',
      oname => 'XC_RTUQC',
      column_group => 'XC_RTUQC_CG',
      sequence_no => 2,
      method => 'SITE PRIORITY',
      parameter_column_name => 'REP_SITE',
      priority_group => 'XC_GLOBAL_SITE_PG');
END;
/


/*
Step 8 Regenerate replication support for the table that received the conflict resolution method.
*/

BEGIN 
    DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
      sname => 'XCManager',
      oname => 'XC_RTUQC', 
      type => 'TABLE',
      min_communication => TRUE); 
END;
/

/*
Step 7.D After assigning your site priority values, add the SITE PRIORITY resolution method to your replicated table. The following example shows that it is the third conflict resolution method for the specified column group (sequence_no parameter).
*/

BEGIN
   DBMS_REPCAT.ADD_UPDATE_RESOLUTION (
      sname => 'XCManager',
      oname => 'XC_TXTDATA1',
      column_group => 'XC_TXTDATA1_CG',
      sequence_no => 2,
      method => 'SITE PRIORITY',
      parameter_column_name => 'REP_SITE',
      priority_group => 'XC_GLOBAL_SITE_PG');
END;
/


/*
Step 8 Regenerate replication support for the table that received the conflict resolution method.
*/

BEGIN 
    DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
      sname => 'XCManager',
      oname => 'XC_TXTDATA1', 
      type => 'TABLE',
      min_communication => TRUE); 
END;
/

/*
Step 7.D After assigning your site priority values, add the SITE PRIORITY resolution method to your replicated table. The following example shows that it is the third conflict resolution method for the specified column group (sequence_no parameter).
*/

BEGIN
   DBMS_REPCAT.ADD_UPDATE_RESOLUTION (
      sname => 'XCManager',
      oname => 'XC_PPLIST',
      column_group => 'XC_PPLIST_CG',
      sequence_no => 2,
      method => 'SITE PRIORITY',
      parameter_column_name => 'REP_SITE',
      priority_group => 'XC_GLOBAL_SITE_PG');
END;
/


/*
Step 8 Regenerate replication support for the table that received the conflict resolution method.
*/

BEGIN 
    DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
      sname => 'XCManager',
      oname => 'XC_PPLIST', 
      type => 'TABLE',
      min_communication => TRUE); 
END;
/

/*
Step 7.D After assigning your site priority values, add the SITE PRIORITY resolution method to your replicated table. The following example shows that it is the third conflict resolution method for the specified column group (sequence_no parameter).
*/

BEGIN
   DBMS_REPCAT.ADD_UPDATE_RESOLUTION (
      sname => 'XCManager',
      oname => 'XC_PPDATA1',
      column_group => 'XC_PPDATA1_CG',
      sequence_no => 2,
      method => 'SITE PRIORITY',
      parameter_column_name => 'REP_SITE',
      priority_group => 'XC_GLOBAL_SITE_PG');
END;
/


/*
Step 8 Regenerate replication support for the table that received the conflict resolution method.
*/

BEGIN 
    DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
      sname => 'XCManager',
      oname => 'XC_PPDATA1', 
      type => 'TABLE',
      min_communication => TRUE); 
END;
/


/**********************************************************************
Step 9 Create a table that stores the messages.
**********************************************************************/

BEGIN
   DBMS_REPCAT.EXECUTE_DDL (
      gname => 'XCREP',
      ddl_text => 'CREATE TABLE XCManager.CONF_REPORT(
                   line NUMBER(2),
                   txt VARCHAR2(80),
                   timestamp DATE,
                   table_name VARCHAR2(30),
                   table_owner VARCHAR2(30),
                   conflict_type VARCHAR2(7))');
END;
/


/**********************************************************************
Step 10 Create a package that sends a notification to the conf_report table when a conflict is detected. 
See Also: 
Appendix B, "User-Defined Conflict Resolution Methods" describes the conflict resolution notification package that is created in this script
**********************************************************************/

CREATE OR REPLACE PACKAGE XCManager.notify AS
    -- Report uniqueness constraint violations on CUSTOMERS table
    FUNCTION XC_DATA1_UNIQUE_VS (
        v_STATION_ID        IN OUT VARCHAR2,
        v_SENSORNAME        IN OUT VARCHAR2,
        v_TIME_TAG          IN OUT DATE,
        v_SOURCE            IN OUT VARCHAR2,
        v_ORIG_VALUE        IN OUT FLOAT,
        v_ED_VALUE          IN OUT FLOAT,
        discard_new_values  IN OUT BOOLEAN) 
    RETURN BOOLEAN;

    FUNCTION XC_SITES_UNIQUE_VS (
        v_STATION_ID        IN OUT VARCHAR2,
        discard_new_values  IN OUT BOOLEAN) 
    RETURN BOOLEAN;

    FUNCTION XC_SITESENSORS_UNIQUE_VS (
        v_STATION_ID        IN OUT VARCHAR2,
        v_SENSORNAME        IN OUT VARCHAR2,
        discard_new_values  IN OUT BOOLEAN) 
    RETURN BOOLEAN;

    FUNCTION XC_GOESQC_UNIQUE_VS (
        v_NESDIS_ID         IN OUT VARCHAR2,
        v_TIME_TAG          IN OUT DATE,
        v_SOURCE            IN OUT VARCHAR2,
        discard_new_values  IN OUT BOOLEAN) 
    RETURN BOOLEAN;

    FUNCTION XC_RTUQC_UNIQUE_VS (
        v_STATION_ID        IN OUT VARCHAR2,
        v_TIME_TAG          IN OUT DATE,
        v_RX_TX             IN OUT VARCHAR2,
        discard_new_values  IN OUT BOOLEAN) 
    RETURN BOOLEAN;

    FUNCTION XC_TXTDATA1_UNIQUE_VS (
        v_STATION_ID        IN OUT VARCHAR2,
        v_SENSORNAME        IN OUT VARCHAR2,
        v_TIME_TAG          IN OUT DATE,
        v_SOURCE            IN OUT VARCHAR2,
        discard_new_values  IN OUT BOOLEAN) 
    RETURN BOOLEAN;

    FUNCTION XC_PPLIST_UNIQUE_VS (
        v_STATION_ID        IN OUT VARCHAR2,
        v_SENSORNAME        IN OUT VARCHAR2,
        v_DATA_LABEL        IN OUT VARCHAR2,
        v_FUNCTION_NAME     IN OUT VARCHAR2,
        discard_new_values  IN OUT BOOLEAN) 
    RETURN BOOLEAN;

    FUNCTION XC_PPDATA1_UNIQUE_VS (
        v_STATION_ID        IN OUT VARCHAR2,
        v_SENSORNAME        IN OUT VARCHAR2,
        v_DATA_LABEL        IN OUT VARCHAR2,
        v_FUNCTION_NAME     IN OUT VARCHAR2,
        v_TIME_TAG          IN OUT DATE,
        v_TIME_PERIOD       IN OUT FLOAT,
        discard_new_values  IN OUT BOOLEAN) 
    RETURN BOOLEAN;

END notify;
/

CREATE OR REPLACE PACKAGE BODY XCManager.notify AS
    -- Define a PL/SQL index-by table to hold the notification message
    TYPE message_table IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;

    PROCEDURE report_conflict (
       conflict_report IN MESSAGE_TABLE,
       report_length   IN NUMBER,
       conflict_time   IN DATE,
       conflict_table  IN VARCHAR2,
       table_owner     IN VARCHAR2,
       conflict_type   IN VARCHAR2) IS
    BEGIN
       FOR idx IN 1..report_length LOOP
         BEGIN
           INSERT INTO XCManager.CONF_REPORT 
             (line, txt, timestamp, table_name, table_owner, conflict_type)
             VALUES (idx, SUBSTR(conflict_report(idx),1,80), conflict_time,
                     conflict_table, table_owner, conflict_type);
         EXCEPTION WHEN others THEN NULL;
         END;
       END LOOP;
    END report_conflict;

    -- This is the conflict resolution method that is called first when
    -- a uniqueness constraint violated is detected in the table.
    FUNCTION XC_DATA1_UNIQUE_VS (
       v_STATION_ID         IN OUT VARCHAR2,
       v_SENSORNAME         IN OUT VARCHAR2,
       v_TIME_TAG           IN OUT DATE,
       v_SOURCE             IN OUT VARCHAR2,
       v_ORIG_VALUE         IN OUT FLOAT,
       v_ED_VALUE           IN OUT FLOAT,
       discard_new_values IN OUT BOOLEAN) 
       RETURN BOOLEAN IS
         local_node  VARCHAR2(128);
         conf_report MESSAGE_TABLE;
         conf_time   DATE := SYSDATE;
         v_rep_site    VARCHAR2(20);
    BEGIN
        -- Get the global name of the local site
        BEGIN
          SELECT global_name INTO local_node FROM global_name;
        EXCEPTION WHEN others THEN local_node := '?';
        END;
        -- Generate a message for the DBA
        conf_report(1) := 'UNIQUENESS CONFLICT DETECTED IN TABLE XC_DATA1 ON ' ||
                        TO_CHAR(conf_time, 'MM-DD-YYYY HH24:MI:SS');
        conf_report(2) := '  AT NODE ' || local_node;
        conf_report(3) := 'ATTEMPTING TO RESOLVE CONFLICT USING ' ||
                        'SITE_PRIORITY';
        conf_report(4) := 'STATION_ID: ' || v_STATION_ID;
        conf_report(5) := 'SENSORNAME:  ' || v_SENSORNAME;
        conf_report(6) := 'TIME_TAG:  ' || v_TIME_TAG;
        conf_report(7) := 'SOURCE:  ' || v_SOURCE;
        conf_report(8) := 'ORIG_VALUE:  ' || v_ORIG_VALUE;
        conf_report(9) := 'ED_VALUE:  ' || v_ED_VALUE;
        conf_report(10) := NULL;
        --- Report the conflict
        report_conflict(conf_report, 10, conf_time, 'XC_DATA1',  
                    'XCManager', 'UNIQUE');

        -- Handle the conflict here
        SELECT global_name INTO v_rep_site FROM GLOBAL_NAME;
        IF v_rep_site = 'XCMAS.WORKGROUP.COM' THEN
            --- Discard the new column values. 
            discard_new_values := TRUE;
            RETURN TRUE;
        ELSE 
            --- Do not discard the new column values. They are still needed by
            --- other conflict resolution methods.
            DELETE FROM XC_DATA1 WHERE 
                STATION_ID = v_STATION_ID AND SENSORNAME = v_SENSORNAME AND TIME_TAG = v_TIME_TAG AND SOURCE = v_SOURCE;
            discard_new_values := FALSE;
            --- Indicate that the conflict was resolved.
            RETURN TRUE;
        END IF;  
    END XC_DATA1_UNIQUE_VS;


    FUNCTION XC_SITES_UNIQUE_VS (
        v_STATION_ID        IN OUT VARCHAR2,
        discard_new_values  IN OUT BOOLEAN) 
    RETURN BOOLEAN IS
         local_node  VARCHAR2(128);
         conf_report MESSAGE_TABLE;
         conf_time   DATE := SYSDATE;
         v_rep_site    VARCHAR2(20);
    BEGIN
        -- Get the global name of the local site
        BEGIN
          SELECT global_name INTO local_node FROM global_name;
        EXCEPTION WHEN others THEN local_node := '?';
        END;
        -- Generate a message for the DBA
        conf_report(1) := 'UNIQUENESS CONFLICT DETECTED IN TABLE XC_SITES ON ' ||
                        TO_CHAR(conf_time, 'MM-DD-YYYY HH24:MI:SS');
        conf_report(2) := '  AT NODE ' || local_node;
        conf_report(3) := 'ATTEMPTING TO RESOLVE CONFLICT USING ' ||
                        'SITE_PRIORITY';
        conf_report(4) := 'STATION_ID: ' || v_STATION_ID;
        conf_report(5) := NULL;
        --- Report the conflict
        report_conflict(conf_report, 5, conf_time, 'XC_SITES',  
                    'XCManager', 'UNIQUE');

        -- Handle the conflict here
        SELECT global_name INTO v_rep_site FROM GLOBAL_NAME;
        IF v_rep_site = 'XCMAS.WORKGROUP.COM' THEN
            --- Discard the new column values. 
            discard_new_values := TRUE;
            RETURN TRUE;
        ELSE 
            --- Do not discard the new column values. They are still needed by
            --- other conflict resolution methods.
            DELETE FROM XC_SITES WHERE 
                STATION_ID = v_STATION_ID;
            discard_new_values := FALSE;
            --- Indicate that the conflict was resolved.
            RETURN TRUE;
        END IF;  
    END XC_SITES_UNIQUE_VS;


    FUNCTION XC_SITESENSORS_UNIQUE_VS (
        v_STATION_ID        IN OUT VARCHAR2,
        v_SENSORNAME        IN OUT VARCHAR2,
        discard_new_values  IN OUT BOOLEAN) 
    RETURN BOOLEAN IS
         local_node  VARCHAR2(128);
         conf_report MESSAGE_TABLE;
         conf_time   DATE := SYSDATE;
         v_rep_site    VARCHAR2(20);
    BEGIN
        -- Get the global name of the local site
        BEGIN
          SELECT global_name INTO local_node FROM global_name;
        EXCEPTION WHEN others THEN local_node := '?';
        END;
        -- Generate a message for the DBA
        conf_report(1) := 'UNIQUENESS CONFLICT DETECTED IN TABLE XC_SITESENSORS ON ' ||
                        TO_CHAR(conf_time, 'MM-DD-YYYY HH24:MI:SS');
        conf_report(2) := '  AT NODE ' || local_node;
        conf_report(3) := 'ATTEMPTING TO RESOLVE CONFLICT USING ' ||
                        'SITE_PRIORITY';
        conf_report(4) := 'STATION_ID: ' || v_STATION_ID;
        conf_report(5) := 'SENSORNAME:  ' || v_SENSORNAME;
        conf_report(6) := NULL;
        --- Report the conflict
        report_conflict(conf_report, 6, conf_time, 'XC_SITESENSORS',  
                    'XCManager', 'UNIQUE');

        -- Handle the conflict here
        SELECT global_name INTO v_rep_site FROM GLOBAL_NAME;
        IF v_rep_site = 'XCMAS.WORKGROUP.COM' THEN
            --- Discard the new column values. 
            discard_new_values := TRUE;
            RETURN TRUE;
        ELSE 
            --- Do not discard the new column values. They are still needed by
            --- other conflict resolution methods.
            DELETE FROM XC_SITESENSORS WHERE 
                STE_STATION_ID = v_STATION_ID AND SENSORNAME = v_SENSORNAME;
            discard_new_values := FALSE;
            --- Indicate that the conflict was resolved.
            RETURN TRUE;
        END IF;  
    END XC_SITESENSORS_UNIQUE_VS;

    FUNCTION XC_GOESQC_UNIQUE_VS (
        v_NESDIS_ID         IN OUT VARCHAR2,
        v_TIME_TAG          IN OUT DATE,
        v_SOURCE            IN OUT VARCHAR2,
        discard_new_values  IN OUT BOOLEAN) 
    RETURN BOOLEAN IS
         local_node  VARCHAR2(128);
         conf_report MESSAGE_TABLE;
         conf_time   DATE := SYSDATE;
         v_rep_site    VARCHAR2(20);
    BEGIN
        -- Get the global name of the local site
        BEGIN
          SELECT global_name INTO local_node FROM global_name;
        EXCEPTION WHEN others THEN local_node := '?';
        END;
        -- Generate a message for the DBA
        conf_report(1) := 'UNIQUENESS CONFLICT DETECTED IN TABLE XC_GOESQC ON ' ||
                        TO_CHAR(conf_time, 'MM-DD-YYYY HH24:MI:SS');
        conf_report(2) := '  AT NODE ' || local_node;
        conf_report(3) := 'ATTEMPTING TO RESOLVE CONFLICT USING ' ||
                        'SITE_PRIORITY';
        conf_report(4) := 'NESDIS_ID: ' || v_NESDIS_ID;
        conf_report(5) := 'TIME_TAG:  ' || v_TIME_TAG;
        conf_report(6) := 'SOURCE:  ' || v_SOURCE;
        conf_report(7) := NULL;
        --- Report the conflict
        report_conflict(conf_report, 7, conf_time, 'XC_GOESQC',  
                    'XCManager', 'UNIQUE');

        -- Handle the conflict here
        SELECT global_name INTO v_rep_site FROM GLOBAL_NAME;
        IF v_rep_site = 'XCMAS.WORKGROUP.COM' THEN
            --- Discard the new column values. 
            discard_new_values := TRUE;
            RETURN TRUE;
        ELSE 
            --- Do not discard the new column values. They are still needed by
            --- other conflict resolution methods.
            DELETE FROM XC_GOESQC WHERE 
                NESDIS_ID = v_NESDIS_ID AND TIME_TAG = v_TIME_TAG AND SOURCE = v_SOURCE;
            discard_new_values := FALSE;
            --- Indicate that the conflict was resolved.
            RETURN TRUE;
        END IF;  
    END XC_GOESQC_UNIQUE_VS;

    FUNCTION XC_RTUQC_UNIQUE_VS (
        v_STATION_ID        IN OUT VARCHAR2,
        v_TIME_TAG          IN OUT DATE,
        v_RX_TX             IN OUT VARCHAR2,
        discard_new_values  IN OUT BOOLEAN) 
    RETURN BOOLEAN IS
         local_node  VARCHAR2(128);
         conf_report MESSAGE_TABLE;
         conf_time   DATE := SYSDATE;
         v_rep_site    VARCHAR2(20);
    BEGIN
        -- Get the global name of the local site
        BEGIN
          SELECT global_name INTO local_node FROM global_name;
        EXCEPTION WHEN others THEN local_node := '?';
        END;
        -- Generate a message for the DBA
        conf_report(1) := 'UNIQUENESS CONFLICT DETECTED IN TABLE XC_RTUQC ON ' ||
                        TO_CHAR(conf_time, 'MM-DD-YYYY HH24:MI:SS');
        conf_report(2) := '  AT NODE ' || local_node;
        conf_report(3) := 'ATTEMPTING TO RESOLVE CONFLICT USING ' ||
                        'SITE_PRIORITY';
        conf_report(4) := 'STATION_ID: ' || v_STATION_ID;
        conf_report(5) := 'TIME_TAG:  ' || v_TIME_TAG;
        conf_report(6) := 'RX_TX:  ' || v_RX_TX;
        conf_report(7) := NULL;
        --- Report the conflict
        report_conflict(conf_report, 7, conf_time, 'XC_RTUQC',  
                    'XCManager', 'UNIQUE');

        -- Handle the conflict here
        SELECT global_name INTO v_rep_site FROM GLOBAL_NAME;
        IF v_rep_site = 'XCMAS.WORKGROUP.COM' THEN
            --- Discard the new column values. 
            discard_new_values := TRUE;
            RETURN TRUE;
        ELSE 
            --- Do not discard the new column values. They are still needed by
            --- other conflict resolution methods.
            DELETE FROM XC_RTUQC WHERE 
                STATION_ID = v_STATION_ID AND TIME_TAG = v_TIME_TAG AND RX_TX = v_RX_TX;
            discard_new_values := FALSE;
            --- Indicate that the conflict was resolved.
            RETURN TRUE;
        END IF;  
    END XC_RTUQC_UNIQUE_VS;

    FUNCTION XC_TXTDATA1_UNIQUE_VS (
        v_STATION_ID        IN OUT VARCHAR2,
        v_SENSORNAME        IN OUT VARCHAR2,
        v_TIME_TAG          IN OUT DATE,
        v_SOURCE            IN OUT VARCHAR2,
        discard_new_values  IN OUT BOOLEAN) 
    RETURN BOOLEAN IS
         local_node  VARCHAR2(128);
         conf_report MESSAGE_TABLE;
         conf_time   DATE := SYSDATE;
         v_rep_site    VARCHAR2(20);
    BEGIN
        -- Get the global name of the local site
        BEGIN
          SELECT global_name INTO local_node FROM global_name;
        EXCEPTION WHEN others THEN local_node := '?';
        END;
        -- Generate a message for the DBA
        conf_report(1) := 'UNIQUENESS CONFLICT DETECTED IN TABLE XC_TXTDATA1 ON ' ||
                        TO_CHAR(conf_time, 'MM-DD-YYYY HH24:MI:SS');
        conf_report(2) := '  AT NODE ' || local_node;
        conf_report(3) := 'ATTEMPTING TO RESOLVE CONFLICT USING ' ||
                        'SITE_PRIORITY';
        conf_report(4) := 'STATION_ID: ' || v_STATION_ID;
        conf_report(5) := 'SENSORNAME:  ' || v_SENSORNAME;
        conf_report(6) := 'TIME_TAG:  ' || v_TIME_TAG;
        conf_report(7) := 'SOURCE:  ' || v_SOURCE;
        conf_report(8) := NULL;
        --- Report the conflict
        report_conflict(conf_report, 8, conf_time, 'XC_TXTDATA1',  
                    'XCManager', 'UNIQUE');

        -- Handle the conflict here
        SELECT global_name INTO v_rep_site FROM GLOBAL_NAME;
        IF v_rep_site = 'XCMAS.WORKGROUP.COM' THEN
            --- Discard the new column values. 
            discard_new_values := TRUE;
            RETURN TRUE;
        ELSE 
            --- Do not discard the new column values. They are still needed by
            --- other conflict resolution methods.
            DELETE FROM XC_TXTDATA1 WHERE 
                STATION_ID = v_STATION_ID AND SENSORNAME = v_SENSORNAME AND TIME_TAG = v_TIME_TAG AND SOURCE = v_SOURCE;
            discard_new_values := FALSE;
            --- Indicate that the conflict was resolved.
            RETURN TRUE;
        END IF;  
    END XC_TXTDATA1_UNIQUE_VS;

    FUNCTION XC_PPLIST_UNIQUE_VS (
        v_STATION_ID        IN OUT VARCHAR2,
        v_SENSORNAME        IN OUT VARCHAR2,
        v_DATA_LABEL        IN OUT VARCHAR2,
        v_FUNCTION_NAME     IN OUT VARCHAR2,
        discard_new_values  IN OUT BOOLEAN) 
    RETURN BOOLEAN IS
         local_node  VARCHAR2(128);
         conf_report MESSAGE_TABLE;
         conf_time   DATE := SYSDATE;
         v_rep_site    VARCHAR2(20);
    BEGIN
        -- Get the global name of the local site
        BEGIN
          SELECT global_name INTO local_node FROM global_name;
        EXCEPTION WHEN others THEN local_node := '?';
        END;
        -- Generate a message for the DBA
        conf_report(1) := 'UNIQUENESS CONFLICT DETECTED IN TABLE XC_PPLIST ON ' ||
                        TO_CHAR(conf_time, 'MM-DD-YYYY HH24:MI:SS');
        conf_report(2) := '  AT NODE ' || local_node;
        conf_report(3) := 'ATTEMPTING TO RESOLVE CONFLICT USING ' ||
                        'SITE_PRIORITY';
        conf_report(4) := 'STATION_ID: ' || v_STATION_ID;
        conf_report(5) := 'SENSORNAME:  ' || v_SENSORNAME;
        conf_report(6) := 'DATA_LABEL:  ' || v_DATA_LABEL;
        conf_report(7) := 'FUNCTION_NAME:  ' || v_FUNCTION_NAME;
        conf_report(8) := NULL;
        --- Report the conflict
        report_conflict(conf_report, 8, conf_time, 'XC_PPLIST',  
                    'XCManager', 'UNIQUE');

        -- Handle the conflict here
        SELECT global_name INTO v_rep_site FROM GLOBAL_NAME;
        IF v_rep_site = 'XCMAS.WORKGROUP.COM' THEN
            --- Discard the new column values. 
            discard_new_values := TRUE;
            RETURN TRUE;
        ELSE 
            --- Do not discard the new column values. They are still needed by
            --- other conflict resolution methods.
            DELETE FROM XC_PPLIST WHERE 
                STATION_ID = v_STATION_ID AND SENSORNAME = v_SENSORNAME AND DATA_LABEL = v_DATA_LABEL AND FUNCTION_NAME = v_FUNCTION_NAME;
            discard_new_values := FALSE;
            --- Indicate that the conflict was resolved.
            RETURN TRUE;
        END IF;  
    END XC_PPLIST_UNIQUE_VS;

    FUNCTION XC_PPDATA1_UNIQUE_VS (
        v_STATION_ID        IN OUT VARCHAR2,
        v_SENSORNAME        IN OUT VARCHAR2,
        v_DATA_LABEL        IN OUT VARCHAR2,
        v_FUNCTION_NAME     IN OUT VARCHAR2,
        v_TIME_TAG          IN OUT DATE,
        v_TIME_PERIOD       IN OUT FLOAT,
        discard_new_values  IN OUT BOOLEAN) 
    RETURN BOOLEAN IS
         local_node  VARCHAR2(128);
         conf_report MESSAGE_TABLE;
         conf_time   DATE := SYSDATE;
         v_rep_site    VARCHAR2(20);
    BEGIN
        -- Get the global name of the local site
        BEGIN
          SELECT global_name INTO local_node FROM global_name;
        EXCEPTION WHEN others THEN local_node := '?';
        END;
        -- Generate a message for the DBA
        conf_report(1) := 'UNIQUENESS CONFLICT DETECTED IN TABLE XC_PPDATA1 ON ' ||
                        TO_CHAR(conf_time, 'MM-DD-YYYY HH24:MI:SS');
        conf_report(2) := '  AT NODE ' || local_node;
        conf_report(3) := 'ATTEMPTING TO RESOLVE CONFLICT USING ' ||
                        'SITE_PRIORITY';
        conf_report(4) := 'STATION_ID: ' || v_STATION_ID;
        conf_report(5) := 'SENSORNAME:  ' || v_SENSORNAME;
        conf_report(6) := 'TIME_TAG:  ' || v_TIME_TAG;
        conf_report(7) := 'DATA_LABEL:  ' || v_DATA_LABEL;
        conf_report(8) := 'FUNCTION_NAME:  ' || v_FUNCTION_NAME;
        conf_report(9) := 'TIME_PERIOD:  ' || v_TIME_PERIOD;
        conf_report(10) := NULL;
        --- Report the conflict
        report_conflict(conf_report, 10, conf_time, 'XC_PPDATA1',  
                    'XCManager', 'UNIQUE');

        -- Handle the conflict here
        SELECT global_name INTO v_rep_site FROM GLOBAL_NAME;
        IF v_rep_site = 'XCMAS.WORKGROUP.COM' THEN
            --- Discard the new column values. 
            discard_new_values := TRUE;
            RETURN TRUE;
        ELSE 
            --- Do not discard the new column values. They are still needed by
            --- other conflict resolution methods.
            DELETE FROM XC_PPDATA1 WHERE 
                STATION_ID = v_STATION_ID AND SENSORNAME = v_SENSORNAME AND TIME_TAG = v_TIME_TAG AND DATA_LABEL = v_DATA_LABEL AND
                    FUNCTION_NAME = v_FUNCTION_NAME AND TIME_PERIOD = v_TIME_PERIOD;
            discard_new_values := FALSE;
            --- Indicate that the conflict was resolved.
            RETURN TRUE;
        END IF;  
    END XC_PPDATA1_UNIQUE_VS;

END notify;
/


/*
Step 11 Replicate the package you created in Step 5 to all of the master sites in your replication environment, which ensures that the notification facility is available at all master sites.
*/

BEGIN
   DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
      gname => 'XCREP',
      type =>  'PACKAGE',
      oname => 'notify',
      sname => 'XCManager');
END;
/

BEGIN
   DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
      gname => 'XCREP',
      type =>  'PACKAGE BODY',
      oname => 'notify',
      sname => 'XCManager');
END;
/


/**********************************************************************
Step 12 - 13 repeat for each table
**********************************************************************/


/* XC_DATA1 */
/*
Step 12 Add the notification facility as one of your conflict resolution methods as a USER FUNCTION.
*/
BEGIN
   DBMS_REPCAT.ADD_UNIQUE_RESOLUTION(
      sname => 'XCManager', 
      oname => 'XC_DATA1', 
      constraint_name => 'DAT1_PK',
      sequence_no => 1, 
      method => 'USER FUNCTION',
      comment => 'Notify and Resolve',
      parameter_column_name => 'STATION_ID,SENSORNAME,TIME_TAG,SOURCE,ORIG_VALUE,ED_VALUE',
      function_name => 'XCManager.notify.XC_DATA1_UNIQUE_VS'); 
END;
/

/*
Step 13 Regenerate replication support for the table that received the conflict resolution method.
*/

BEGIN 
    DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
      sname => 'XCManager',
      oname => 'XC_DATA1', 
      type => 'TABLE',
      min_communication => TRUE); 
END;
/


/* XC_SITES */
/*
Step 12 Add the notification facility as one of your conflict resolution methods as a USER FUNCTION.
*/
BEGIN
   DBMS_REPCAT.ADD_UNIQUE_RESOLUTION(
      sname => 'XCManager', 
      oname => 'XC_SITES', 
      constraint_name => 'STE_PK',
      sequence_no => 1, 
      method => 'USER FUNCTION',
      comment => 'Notify and Resolve',
      parameter_column_name => 'STATION_ID',
      function_name => 'XCManager.notify.XC_SITES_UNIQUE_VS'); 
END;
/

/*
Step 13 Regenerate replication support for the table that received the conflict resolution method.
*/

BEGIN 
    DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
      sname => 'XCManager',
      oname => 'XC_SITES', 
      type => 'TABLE',
      min_communication => TRUE); 
END;
/


/* XC_SITESENSORS */
/*
Step 12 Add the notification facility as one of your conflict resolution methods as a USER FUNCTION.
*/
BEGIN
   DBMS_REPCAT.ADD_UNIQUE_RESOLUTION(
      sname => 'XCManager', 
      oname => 'XC_SITESENSORS', 
      constraint_name => 'SITSEN_PK',
      sequence_no => 1, 
      method => 'USER FUNCTION',
      comment => 'Notify and Resolve',
      parameter_column_name => 'STE_STATION_ID,SENSORNAME',
      function_name => 'XCManager.notify.XC_SITESENSORS_UNIQUE_VS'); 
END;
/

/*
Step 13 Regenerate replication support for the table that received the conflict resolution method.
*/

BEGIN 
    DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
      sname => 'XCManager',
      oname => 'XC_SITESENSORS', 
      type => 'TABLE',
      min_communication => TRUE); 
END;
/


/* XC_GOESQC */
/*
Step 12 Add the notification facility as one of your conflict resolution methods as a USER FUNCTION.
*/
BEGIN
   DBMS_REPCAT.ADD_UNIQUE_RESOLUTION(
      sname => 'XCManager', 
      oname => 'XC_GOESQC', 
      constraint_name => 'GOESQC_PK',
      sequence_no => 1, 
      method => 'USER FUNCTION',
      comment => 'Notify and Resolve',
      parameter_column_name => 'NESDIS_ID,TIME_TAG,SOURCE',
      function_name => 'XCManager.notify.XC_GOESQC_UNIQUE_VS'); 
END;
/

/*
Step 13 Regenerate replication support for the table that received the conflict resolution method.
*/

BEGIN 
    DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
      sname => 'XCManager',
      oname => 'XC_GOESQC', 
      type => 'TABLE',
      min_communication => TRUE); 
END;
/

/* XC_RTUQC */
/*
Step 12 Add the notification facility as one of your conflict resolution methods as a USER FUNCTION.
*/
BEGIN
   DBMS_REPCAT.ADD_UNIQUE_RESOLUTION(
      sname => 'XCManager', 
      oname => 'XC_RTUQC', 
      constraint_name => 'RTUQC_PK',
      sequence_no => 1, 
      method => 'USER FUNCTION',
      comment => 'Notify and Resolve',
      parameter_column_name => 'STATION_ID,TIME_TAG,RX_TX',
      function_name => 'XCManager.notify.XC_RTUQC_UNIQUE_VS'); 
END;
/

/*
Step 13 Regenerate replication support for the table that received the conflict resolution method.
*/

BEGIN 
    DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
      sname => 'XCManager',
      oname => 'XC_RTUQC', 
      type => 'TABLE',
      min_communication => TRUE); 
END;
/

/* XC_TXTDATA1 */
/*
Step 12 Add the notification facility as one of your conflict resolution methods as a USER FUNCTION.
*/
BEGIN
   DBMS_REPCAT.ADD_UNIQUE_RESOLUTION(
      sname => 'XCManager', 
      oname => 'XC_TXTDATA1', 
      constraint_name => 'TXD1_PK',
      sequence_no => 1, 
      method => 'USER FUNCTION',
      comment => 'Notify and Resolve',
      parameter_column_name => 'STATION_ID,SENSORNAME,TIME_TAG,SOURCE',
      function_name => 'XCManager.notify.XC_TXTDATA1_UNIQUE_VS'); 
END;
/

/*
Step 13 Regenerate replication support for the table that received the conflict resolution method.
*/

BEGIN 
    DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
      sname => 'XCManager',
      oname => 'XC_TXTDATA1', 
      type => 'TABLE',
      min_communication => TRUE); 
END;
/


/* XC_PPDATA1 */
/*
Step 12 Add the notification facility as one of your conflict resolution methods as a USER FUNCTION.
*/
BEGIN
   DBMS_REPCAT.ADD_UNIQUE_RESOLUTION(
      sname => 'XCManager', 
      oname => 'XC_PPDATA1', 
      constraint_name => 'PPDAT1_PK',
      sequence_no => 1, 
      method => 'USER FUNCTION',
      comment => 'Notify and Resolve',
      parameter_column_name => 'STATION_ID,SENSORNAME,DATA_LABEL,FUNCTION_NAME,TIME_TAG,TIME_PERIOD',
      function_name => 'XCManager.notify.XC_PPDATA1_UNIQUE_VS'); 
END;
/

/*
Step 13 Regenerate replication support for the table that received the conflict resolution method.
*/

BEGIN 
    DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
      sname => 'XCManager',
      oname => 'XC_PPDATA1', 
      type => 'TABLE',
      min_communication => TRUE); 
END;
/

/* XC_PPLIST */
/*
Step 12 Add the notification facility as one of your conflict resolution methods as a USER FUNCTION.
*/
BEGIN
   DBMS_REPCAT.ADD_UNIQUE_RESOLUTION(
      sname => 'XCManager', 
      oname => 'XC_PPLIST', 
      constraint_name => 'PPLIST_PK',
      sequence_no => 1, 
      method => 'USER FUNCTION',
      comment => 'Notify and Resolve',
      parameter_column_name => 'STATION_ID,SENSORNAME,DATA_LABEL,FUNCTION_NAME',
      function_name => 'XCManager.notify.XC_PPLIST_UNIQUE_VS'); 
END;
/

/*
Step 13 Regenerate replication support for the table that received the conflict resolution method.
*/

BEGIN 
    DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
      sname => 'XCManager',
      oname => 'XC_PPLIST', 
      type => 'TABLE',
      min_communication => TRUE); 
END;
/



/*
Step 14 Resume replication activity by using the RESUME_MASTER_ACTIVITY procedure.
*/

BEGIN
   DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
      gname => 'XCREP');
END;
/

SET ECHO OFF

SPOOL OFF

/************************* END OF SCRIPT **********************************/

