~~~BEGIN_MAIN_SECTION~~~
[PRODUCT]
view=OAC_USAGE_TRACKING_SNAPSHOT_DETAIL_VW
view=OAC_USAGE_TRACKING_SNAPSHOT_VW
table=OAC_USAGE_TRACKING_SNAPSHOT_DETAIL_TB
table=OAC_USAGE_TRACKING_SNAPSHOT_TB
procedure=UPDATE_USAGE_TRACKING_SNAPSHOT
job=UPDATE_USAGE_TRACKING_SNAPSHOT_JOB
~~~END_MAIN_SECTION~~~


~~~BEGIN_VERSION_SECTION~~~
[VERSION]
version=2026-02-25

[SQL_SCRIPT___ADMIN]
--each OAC must have its own schema

DEFINE trackschema = 'OAC_TRACKING_PROD_NEW';
DEFINE trackschema = 'OAC_TRACKING_PROD';
DEFINE trackschema = 'OAC_TRACKING_DEV_OAC01';
DEFINE trackschema = 'USAGE_TRACKING'; --sandbox




create or replace view "&trackschema".OAC_USAGE_TRACKING_SNAPSHOT_DETAIL_VW as
select
    (to_char(query_timestamp,'YYYY-MM-DD')) "DAY"
	,case when DASHBOARD_PATH is null then 'UNKNOWN' else DASHBOARD_PATH end "DASHBOARD PATH"
    ,case when dashboard_name is null then 'UNKNOWN' else dashboard_name end "DASHBOARD NAME"
    ,count (distinct user_name) "UNIQUE USERS"
    ,count(*) VIEWS
from "&trackschema".OAC_USAGE_TRACKING_VW
where /*query_timestamp>sysdate-1 and*/ dataset != 'USAGE_TRACKING'
group by
    (to_char(query_timestamp,'YYYY-MM-DD'))
	,DASHBOARD_PATH
    ,dashboard_name
order by
    (to_char(query_timestamp,'YYYY-MM-DD')) desc
	,DASHBOARD_PATH
    ,dashboard_name;
/
drop table "&trackschema".OAC_USAGE_TRACKING_SNAPSHOT_DETAIL_TB;
/
create table "&trackschema".OAC_USAGE_TRACKING_SNAPSHOT_DETAIL_TB as select * from "&trackschema".OAC_USAGE_TRACKING_SNAPSHOT_DETAIL_VW;
/

create or replace view "&trackschema".OAC_USAGE_TRACKING_SNAPSHOT_VW as
select
    (to_char(query_timestamp,'YYYY-MM-DD')) "DAY"
	,case when DASHBOARD_PATH is null then 'UNKNOWN' else DASHBOARD_PATH end "DASHBOARD PATH"
    ,count (distinct user_name) "UNIQUE USERS"
    ,count(*) VIEWS
from "&trackschema".OAC_USAGE_TRACKING_VW
where /*query_timestamp>sysdate-1 and*/ dataset != 'USAGE_TRACKING'
group by
    (to_char(query_timestamp,'YYYY-MM-DD'))
	,DASHBOARD_PATH
order by
    (to_char(query_timestamp,'YYYY-MM-DD')) desc
	,DASHBOARD_PATH;
/
drop table "&trackschema".OAC_USAGE_TRACKING_SNAPSHOT_TB;
/
create table "&trackschema".OAC_USAGE_TRACKING_SNAPSHOT_TB as select * from "&trackschema".OAC_USAGE_TRACKING_SNAPSHOT_VW;
/



CREATE OR REPLACE PROCEDURE "&trackschema".UPDATE_USAGE_TRACKING_SNAPSHOT AUTHID CURRENT_USER
IS
begin
	begin EXECUTE IMMEDIATE q'!delete from "&trackschema".OAC_USAGE_TRACKING_SNAPSHOT_DETAIL_TB where DAY>=TRUNC(SYSDATE -2)!';end;commit;
	begin EXECUTE IMMEDIATE q'!insert into "&trackschema".OAC_USAGE_TRACKING_SNAPSHOT_DETAIL_TB (select * from "&trackschema".OAC_USAGE_TRACKING_SNAPSHOT_DETAIL_VW where DAY>=TRUNC(SYSDATE -2))!';end;commit;
	begin EXECUTE IMMEDIATE q'!delete from "&trackschema".OAC_USAGE_TRACKING_SNAPSHOT_TB where DAY>=TRUNC(SYSDATE -2)!';end;commit;
	begin EXECUTE IMMEDIATE q'!insert into "&trackschema".OAC_USAGE_TRACKING_SNAPSHOT_TB (select * from "&trackschema".OAC_USAGE_TRACKING_SNAPSHOT_VW where DAY>=TRUNC(SYSDATE -2))!';end;commit;
	commit;
end;
/



begin
    dbms_scheduler.create_job(job_name        => '"&trackschema".UPDATE_USAGE_TRACKING_SNAPSHOT_JOB',
                              job_type        => 'STORED_PROCEDURE',
                              job_action      => '"&trackschema".UPDATE_USAGE_TRACKING_SNAPSHOT',
                              start_date      => systimestamp,
                              end_date        => null,
                              repeat_interval => 'freq=hourly; interval=12;',
                              enabled         => true,
                              auto_drop       => false,
                              comments        => 'Run UPDATE_USAGE_TRACKING_SNAPSHOT_JOB procedure.');
end;
/

GRANT SELECT ON "&trackschema".OAC_USAGE_TRACKING_SNAPSHOT_DETAIL_TB TO USER_OTHQ;
GRANT SELECT ON "&trackschema".OAC_USAGE_TRACKING_SNAPSHOT_DETAIL_TB TO OTHQ_OWN;
CREATE OR REPLACE SYNONYM USER_OTHQ.TRACKING_DETAIL_&trackschema for "&trackschema".OAC_USAGE_TRACKING_SNAPSHOT_DETAIL_TB;
CREATE OR REPLACE SYNONYM OTHQ_OWN.TRACKING_DETAIL_&trackschema for "&trackschema".OAC_USAGE_TRACKING_SNAPSHOT_DETAIL_TB;
GRANT SELECT ON "&trackschema".OAC_USAGE_TRACKING_SNAPSHOT_TB TO USER_OTHQ;
GRANT SELECT ON "&trackschema".OAC_USAGE_TRACKING_SNAPSHOT_TB TO OTHQ_OWN;
CREATE OR REPLACE SYNONYM USER_OTHQ.TRACKING_&trackschema for "&trackschema".OAC_USAGE_TRACKING_SNAPSHOT_TB;
CREATE OR REPLACE SYNONYM OTHQ_OWN.TRACKING_&trackschema for "&trackschema".OAC_USAGE_TRACKING_SNAPSHOT_TB;




/* PROD
create or replace view USER_OTHQ.TRACKING_DETAIL_VW as
(select 'OAC_TRACKING_DEV_OAC01' "SYSTEM", x.* from OTHQ_OWN.TRACKING_DETAIL_OAC_TRACKING_DEV_OAC01 x
union all
select 'OAC_TRACKING_PROD_NEW' "SYSTEM", x.* from OTHQ_OWN.TRACKING_DETAIL_OAC_TRACKING_PROD_NEW x
union all
select 'OAC_TRACKING_PROD' "SYSTEM", x.* from OTHQ_OWN.TRACKING_DETAIL_OAC_TRACKING_PROD x)


create or replace view USER_OTHQ.TRACKING_VW as
(select 'OAC_TRACKING_DEV_OAC01' "SYSTEM", x.* from OTHQ_OWN.TRACKING_OAC_TRACKING_DEV_OAC01 x
union all
select 'OAC_TRACKING_PROD_NEW' "SYSTEM", x.* from OTHQ_OWN.TRACKING_OAC_TRACKING_PROD_NEW x
union all
select 'OAC_TRACKING_PROD' "SYSTEM", x.* from OTHQ_OWN.TRACKING_OAC_TRACKING_PROD x)

GRANT SELECT ON OTHQ_OWN.TRACKING_DETAIL_OAC_TRACKING_DEV_OAC01 TO USER_OTHQ;
GRANT SELECT ON OTHQ_OWN.TRACKING_DETAIL_OAC_TRACKING_PROD_NEW TO USER_OTHQ;
GRANT SELECT ON OTHQ_OWN.TRACKING_DETAIL_OAC_TRACKING_PROD TO USER_OTHQ;

GRANT SELECT ON OTHQ_OWN.TRACKING_OAC_TRACKING_DEV_OAC01 TO USER_OTHQ;
GRANT SELECT ON OTHQ_OWN.TRACKING_OAC_TRACKING_PROD_NEW TO USER_OTHQ;
GRANT SELECT ON OTHQ_OWN.TRACKING_OAC_TRACKING_PROD TO USER_OTHQ;

*/



/* SANDBOX
create or replace view USER_OTHQ.TRACKING_DETAIL_VW as select 'SANDBOX' "SYSTEM", x.* from OTHQ_OWN.TRACKING_DETAIL_USAGE_TRACKING x;
create or replace view USER_OTHQ.TRACKING_VW as select 'SANDBOX' "SYSTEM", x.* from OTHQ_OWN.TRACKING_USAGE_TRACKING x;


GRANT SELECT ON OTHQ_OWN.TRACKING_DETAIL_USAGE_TRACKING TO USER_OTHQ;
GRANT SELECT ON OTHQ_OWN.TRACKING_USAGE_TRACKING TO USER_OTHQ;

GRANT SELECT ON OTHQ_OWN.TRACKING_DETAIL_VW TO USER_OTHQ;
GRANT SELECT ON OTHQ_OWN.TRACKING_VW TO USER_OTHQ;
*/






~~~END_VERSION_SECTION~~~

