CREATE OR REPLACE PROCEDURE UPDATE_ON_DEMAND_ACS_MONITORING_TB_PROC AUTHID CURRENT_USER
IS
	-- BEGIN MODIFY THIS --	
	TABLE_NAME VARCHAR2(128) := 'ON_DEMAND_ACS_MONITORING_TB';
	USER_SCHEMA VARCHAR2(128) := 'USER_ACS';
	CODE_BLOCK VARCHAR2(32767) := '
	SELECT TABLE_NAME,
       COMPID,
       "TIMESTAMP",
       COUNT_RECORDS
	FROM (
	  SELECT ''SRC_ACS_EC_WORKFLOWS_V2_TB'' AS TABLE_NAME,
	         METADATA_COMPID               AS COMPID,
	         TRUNC(METADATA_TIMESTAMP)     AS "TIMESTAMP",
	         COUNT(*)                      AS COUNT_RECORDS
	  FROM SRC_ACS_EC_WORKFLOWS_V2_TB
	  GROUP BY METADATA_COMPID, TRUNC(METADATA_TIMESTAMP)

	  UNION ALL
	  SELECT ''SRC_ACS_ENVIRONMENT_TB'', METADATA_COMPID, TRUNC(METADATA_TIMESTAMP), COUNT(*)
	  FROM SRC_ACS_ENVIRONMENT_TB
	  GROUP BY METADATA_COMPID, TRUNC(METADATA_TIMESTAMP)

	  UNION ALL
	  SELECT ''SRC_ACS_FIELD_SEARCHES_TB'', METADATA_COMPID, TRUNC(METADATA_TIMESTAMP), COUNT(*)
	  FROM SRC_ACS_FIELD_SEARCHES_TB
	  GROUP BY METADATA_COMPID, TRUNC(METADATA_TIMESTAMP)

	  UNION ALL
	  SELECT ''SRC_ACS_FORM_FIELDS_TB'', METADATA_COMPID, TRUNC(METADATA_TIMESTAMP), COUNT(*)
	  FROM SRC_ACS_FORM_FIELDS_TB
	  GROUP BY METADATA_COMPID, TRUNC(METADATA_TIMESTAMP)

	  UNION ALL
	  SELECT ''SRC_ACS_INTEGRATION_CONCURRENCY_TB'', METADATA_COMPID, TRUNC(METADATA_TIMESTAMP), COUNT(*)
	  FROM SRC_ACS_INTEGRATION_CONCURRENCY_TB
	  GROUP BY METADATA_COMPID, TRUNC(METADATA_TIMESTAMP)

	  UNION ALL
	  SELECT ''SRC_ACS_INTEGRATION_DETAILS_TB'', METADATA_COMPID, TRUNC(METADATA_TIMESTAMP), COUNT(*)
	  FROM SRC_ACS_INTEGRATION_DETAILS_TB
	  GROUP BY METADATA_COMPID, TRUNC(METADATA_TIMESTAMP)

	  UNION ALL
	  SELECT ''SRC_ACS_LOGO_SIZE_TB'', METADATA_COMPID, TRUNC(METADATA_TIMESTAMP), COUNT(*)
	  FROM SRC_ACS_LOGO_SIZE_TB
	  GROUP BY METADATA_COMPID, TRUNC(METADATA_TIMESTAMP)

	  UNION ALL
	  SELECT ''SRC_ACS_MRSSWAIT_TIME_TB'', METADATA_COMPID, TRUNC(METADATA_TIMESTAMP), COUNT(*)
	  FROM SRC_ACS_MRSSWAIT_TIME_TB
	  GROUP BY METADATA_COMPID, TRUNC(METADATA_TIMESTAMP)

	  UNION ALL
	  SELECT ''SRC_ACS_RECORD_NAMES_TB'', METADATA_COMPID, TRUNC(METADATA_TIMESTAMP), COUNT(*)
	  FROM SRC_ACS_RECORD_NAMES_TB
	  GROUP BY METADATA_COMPID, TRUNC(METADATA_TIMESTAMP)

	  UNION ALL
	  SELECT ''SRC_ACS_SCPROCESSOR_PREFERENCES_TB'', METADATA_COMPID, TRUNC(METADATA_TIMESTAMP), COUNT(*)
	  FROM SRC_ACS_SCPROCESSOR_PREFERENCES_TB
	  GROUP BY METADATA_COMPID, TRUNC(METADATA_TIMESTAMP)

	  UNION ALL
	  SELECT ''SRC_ACS_SCPROCESSOR_UTILIZATION_APM_TB'', METADATA_COMPID, TRUNC(METADATA_TIMESTAMP), COUNT(*)
	  FROM SRC_ACS_SCPROCESSOR_UTILIZATION_APM_TB
	  GROUP BY METADATA_COMPID, TRUNC(METADATA_TIMESTAMP)

	  UNION ALL
	  SELECT ''SRC_ACS_SCPROCESSOR_UTILIZATION_HOURLY_TB'', METADATA_COMPID, TRUNC(METADATA_TIMESTAMP), COUNT(*)
	  FROM SRC_ACS_SCPROCESSOR_UTILIZATION_HOURLY_TB
	  GROUP BY METADATA_COMPID, TRUNC(METADATA_TIMESTAMP)

	  UNION ALL
	  SELECT ''SRC_ACS_SCPROCESSOR_WAIT_TIME_HOURLY_TB'', METADATA_COMPID, TRUNC(METADATA_TIMESTAMP), COUNT(*)
	  FROM SRC_ACS_SCPROCESSOR_WAIT_TIME_HOURLY_TB
	  GROUP BY METADATA_COMPID, TRUNC(METADATA_TIMESTAMP)

	  UNION ALL
	  SELECT ''SRC_ACS_SCRIPT_FAILURE_DETAILS_TB'', METADATA_COMPID, TRUNC(METADATA_TIMESTAMP), COUNT(*)
	  FROM SRC_ACS_SCRIPT_FAILURE_DETAILS_TB
	  GROUP BY METADATA_COMPID, TRUNC(METADATA_TIMESTAMP)

	  UNION ALL
	  SELECT ''SRC_ACS_SCRIPT_FAILURES_TB'', METADATA_COMPID, TRUNC(METADATA_TIMESTAMP), COUNT(*)
	  FROM SRC_ACS_SCRIPT_FAILURES_TB
	  GROUP BY METADATA_COMPID, TRUNC(METADATA_TIMESTAMP)

	  UNION ALL
	  SELECT ''SRC_ACS_SUITEAPP_BUNDLE_OBJECTS_TB'', METADATA_COMPID, TRUNC(METADATA_TIMESTAMP), COUNT(*)
	  FROM SRC_ACS_SUITEAPP_BUNDLE_OBJECTS_TB
	  GROUP BY METADATA_COMPID, TRUNC(METADATA_TIMESTAMP)

	  UNION ALL
	  SELECT ''SRC_ACS_TH_SCRIPT_CONTEXT_USAGE_TB'', METADATA_COMPID, TRUNC(METADATA_TIMESTAMP), COUNT(*)
	  FROM SRC_ACS_TH_SCRIPT_CONTEXT_USAGE_TB
	  GROUP BY METADATA_COMPID, TRUNC(METADATA_TIMESTAMP)

	  UNION ALL
	  SELECT ''SRC_ACS_TH_UI_E2E_DETAILS_TB'', METADATA_COMPID, TRUNC(METADATA_TIMESTAMP), COUNT(*)
	  FROM SRC_ACS_TH_UI_E2E_DETAILS_TB
	  GROUP BY METADATA_COMPID, TRUNC(METADATA_TIMESTAMP)

	  UNION ALL
	  SELECT ''SRC_ACS_TH_UI_SCRIPT_DETAILS_TB'', METADATA_COMPID, TRUNC(METADATA_TIMESTAMP), COUNT(*)
	  FROM SRC_ACS_TH_UI_SCRIPT_DETAILS_TB
	  GROUP BY METADATA_COMPID, TRUNC(METADATA_TIMESTAMP)

	  UNION ALL
	  SELECT ''SRC_ACS_TH_UI_SCRIPT_ERRORS_TB'', METADATA_COMPID, TRUNC(METADATA_TIMESTAMP), COUNT(*)
	  FROM SRC_ACS_TH_UI_SCRIPT_ERRORS_TB
	  GROUP BY METADATA_COMPID, TRUNC(METADATA_TIMESTAMP)

	  UNION ALL
	  SELECT ''SRC_ACS_TH_UI_WF_DETAILS_TB'', METADATA_COMPID, TRUNC(METADATA_TIMESTAMP), COUNT(*)
	  FROM SRC_ACS_TH_UI_WF_DETAILS_TB
	  GROUP BY METADATA_COMPID, TRUNC(METADATA_TIMESTAMP)

	  UNION ALL
	  SELECT ''SRC_ACS_TH_WS_CONCURRENCY_TB'', METADATA_COMPID, TRUNC(METADATA_TIMESTAMP), COUNT(*)
	  FROM SRC_ACS_TH_WS_CONCURRENCY_TB
	  GROUP BY METADATA_COMPID, TRUNC(METADATA_TIMESTAMP)

	  UNION ALL
	  SELECT ''SRC_ACS_TH_WS_REST_TB'', METADATA_COMPID, TRUNC(METADATA_TIMESTAMP), COUNT(*)
	  FROM SRC_ACS_TH_WS_REST_TB
	  GROUP BY METADATA_COMPID, TRUNC(METADATA_TIMESTAMP)

	  UNION ALL
	  SELECT ''SRC_ACS_TH_WS_RESTLET_FAILURES_TB'', METADATA_COMPID, TRUNC(METADATA_TIMESTAMP), COUNT(*)
	  FROM SRC_ACS_TH_WS_RESTLET_FAILURES_TB
	  GROUP BY METADATA_COMPID, TRUNC(METADATA_TIMESTAMP)

	  UNION ALL
	  SELECT ''SRC_ACS_TH_WS_RESTLETS_TB'', METADATA_COMPID, TRUNC(METADATA_TIMESTAMP), COUNT(*)
	  FROM SRC_ACS_TH_WS_RESTLETS_TB
	  GROUP BY METADATA_COMPID, TRUNC(METADATA_TIMESTAMP)

	  UNION ALL
	  SELECT ''SRC_ACS_TH_WS_TB'', METADATA_COMPID, TRUNC(METADATA_TIMESTAMP), COUNT(*)
	  FROM SRC_ACS_TH_WS_TB
	  GROUP BY METADATA_COMPID, TRUNC(METADATA_TIMESTAMP)

	  UNION ALL
	  SELECT ''SRC_ACS_TH_WS_USERID_TB'', METADATA_COMPID, TRUNC(METADATA_TIMESTAMP), COUNT(*)
	  FROM SRC_ACS_TH_WS_USERID_TB
	  GROUP BY METADATA_COMPID, TRUNC(METADATA_TIMESTAMP)

	  UNION ALL
	  SELECT ''SRC_ACS_TRANSACTION_COUNTS_TB'', METADATA_COMPID, TRUNC(METADATA_TIMESTAMP), COUNT(*)
	  FROM SRC_ACS_TRANSACTION_COUNTS_TB
	  GROUP BY METADATA_COMPID, TRUNC(METADATA_TIMESTAMP)
	)
	';
	-- END MODIFY THIS --
BEGIN
	dbms_output.put_line('INFO: Procedure UPDATE_' || TABLE_NAME || '_PROC started');

	BEGIN
		EXECUTE IMMEDIATE 'DROP TABLE ' || TABLE_NAME || '_TMP';
		dbms_output.put_line('INFO: Dropped table ' || TABLE_NAME || '_TMP');
	EXCEPTION WHEN OTHERS THEN
		dbms_output.put_line('WARNING: Table ' || TABLE_NAME || '_TMP does not exist');
	END;

	BEGIN
		EXECUTE IMMEDIATE 'DROP TABLE ' || TABLE_NAME || '_DEL';
		dbms_output.put_line('INFO: Dropped table ' || TABLE_NAME || '_DEL');
	EXCEPTION WHEN OTHERS THEN
		dbms_output.put_line('WARNING: Table ' || TABLE_NAME || '_DEL does not exist');
	END;

	EXECUTE IMMEDIATE 'CREATE TABLE ' || TABLE_NAME || '_TMP AS (' || CODE_BLOCK || ')';
	dbms_output.put_line('INFO: Created table ' || TABLE_NAME || '_TMP');

	BEGIN
		EXECUTE IMMEDIATE 'ALTER TABLE ' || TABLE_NAME || ' RENAME TO ' || TABLE_NAME || '_DEL';
		dbms_output.put_line('INFO: Renamed table ' || TABLE_NAME || ' to ' || TABLE_NAME || '_DEL');
	EXCEPTION WHEN OTHERS THEN
		dbms_output.put_line('WARNING: Table ' || TABLE_NAME || ' does not exist');
	END;

	EXECUTE IMMEDIATE 'ALTER TABLE ' || TABLE_NAME || '_TMP RENAME TO ' || TABLE_NAME;
	dbms_output.put_line('INFO: Renamed table ' || TABLE_NAME || '_TMP to ' || TABLE_NAME);

	BEGIN
		EXECUTE IMMEDIATE 'DROP TABLE ' || TABLE_NAME || '_DEL';
		dbms_output.put_line('Dropped table ' || TABLE_NAME || '_DEL');
	EXCEPTION WHEN OTHERS THEN
		dbms_output.put_line('WARNING: Table ' || TABLE_NAME || '_DEL does not exist');
	END;

	EXECUTE IMMEDIATE 'GRANT SELECT ON ' || TABLE_NAME || ' TO ' || USER_SCHEMA;
	dbms_output.put_line('INFO: Granted SELECT on ' || TABLE_NAME || ' to ' || USER_SCHEMA);

	dbms_output.put_line('INFO: Procedure UPDATE_' || TABLE_NAME || '_PROC finished successfully');
END;
