set feed off
set pages 0
set lines 9999 long 99999 head off
set trimspool on
set wrap on
col text format a9999
spool create_miro.sql
select 'spool create_miro.log'
from dual
/
-------------------------------------- CREATE TABLESPACE -----------------------------------------------------
create tablespace PBITS_01 datafile '+DATA' size 20G;
/
create tablespace RPRT_01 datafile '+DATA' size 20G
/
-------------------------------------- CREATE USERS -----------------------------------------------------
create user CCB1REP identified by CCB1REP default tablespace PBITS_01;
/
create user MIRO identified by miro default tablespace PBITS_01;
/
create user REPORTER identified by reporter default tablespace RPRT_01;
/
-------------------------------------- GRANT USERS -----------------------------------------------------
grant connect, resource to CCB1REP;
/
grant connect, resource, create view to MIRO;
/
grant connect, resource, create view, create table, create procedure to REPORTER;
/
-------------------------------------- TABLE GRANTS -----------------------------------------------------
/
select 'grant select on '||owner||'.'||table_name||' to CCB1REP with grant option;' from all_tables where owner in ('CCBADM','MDMADM');
/
select 'grant select on '||owner||'.'||table_name||' to MIRO with grant option;' from all_tables where owner in ('CCBADM','MDMADM');
/
select 'grant select on '||owner||'.'||table_name||' to REPORTER with grant option;' from all_tables where owner in ('CCBADM','MDMADM');
/
-------------------------------------- VIEW CREATION/GRANTS ----------------------------------------------------
select case 
when owner='MDMADM' and table_name like 'CI_%_CHAR' --and table_name like '%_CHAR'
then 'create or replace view CCB1REP.MV_MDM_'||table_name||' as select a.*,SYSDATE-1 as JRN_EFF_START_DTTM,SYSDATE+1 as JRN_EFF_END_DTTM,''I'' as JRN_FLAG,SYSDATE-1 as JRN_SLICING_TS,'''' as JRN_SCN,''Y'' as JRN_CLEANSED_FLAG,''1'' as JRN_CURRENT_FLAG, SYSDATE+1 as EFF_END_DTTM,SYSDATE-1 as JRN_UPDATE_DTTM from '||owner||'.'||table_name||' a ;'
when owner='MDMADM' and table_name like 'CI_%' and table_name not like '%0'
then 'create or replace view CCB1REP.MV_MDM_'||table_name||' as select a.*,SYSDATE-1 as JRN_EFF_START_DTTM,SYSDATE+1 as JRN_EFF_END_DTTM,''I'' as JRN_FLAG,SYSDATE-1 as JRN_SLICING_TS,'''' as JRN_SCN,''Y'' as JRN_CLEANSED_FLAG,''1'' as JRN_CURRENT_FLAG, SYSDATE+1 as EFF_END_DTTM,SYSDATE-1 as JRN_UPDATE_DTTM from '||owner||'.'||table_name||' a ;'
when owner='CCBADM' and table_name not like '%_CHAR' 
then 'create or replace view CCB1REP.'||table_name||' as select a.*,SYSDATE-1 as JRN_EFF_START_DTTM,SYSDATE+1 as JRN_EFF_END_DTTM,''I'' as JRN_FLAG,SYSDATE-1 as JRN_SLICING_TS,'''' as JRN_SCN,''Y'' as JRN_CLEANSED_FLAG,''1'' as JRN_CURRENT_FLAG, SYSDATE+1 as EFF_END_DTTM,SYSDATE-1 as JRN_UPDATE_DTTM from '||owner||'.'||table_name||' a ;'
else 
'create or replace view CCB1REP.'||table_name||' as select a.*,SYSDATE-1 as JRN_EFF_START_DTTM,SYSDATE+1 as JRN_EFF_END_DTTM,''I'' as JRN_FLAG,SYSDATE-1 as JRN_SLICING_TS,'''' as JRN_SCN,''Y'' as JRN_CLEANSED_FLAG,''1'' as JRN_CURRENT_FLAG, SYSDATE+1 as EFF_END_DTTM,SYSDATE-1 as JRN_UPDATE_DTTM  from '||owner||'.'||table_name||' a ;'
end as x
from all_tables
where owner in ('MDMADM','CCBADM');
/
select 'grant select on '||owner||'.'||view_name||' to MIRO with grant option;' from all_views where owner in ('CCB1REP');
/
select 'grant select on '||owner||'.'||view_name||' to REPORTER with grant option;' from all_views where owner in ('CCB1REP');
/
select 'create or replace synonym REPORTER.MV_'||view_name||' for '||owner||'.'||view_name||';' from all_views where owner = 'CCB1REP' and view_name like 'MV_MDM_%';
/
select 'create or replace synonym REPORTER.MV_'||view_name||' for '||owner||'.'||view_name||';' from all_views where owner = 'CCB1REP' and view_name like 'F1%' or view_name like 'CM%';
/
select case 
when owner='MDMADM' and table_name like 'CI_%' 
then 'create or replace view MIRO.MV_MDM_'||table_name||' as select a.*,SYSDATE-1 as JRN_EFF_START_DTTM,SYSDATE+1 as JRN_EFF_END_DTTM,''I'' as JRN_FLAG,SYSDATE-1 as JRN_SLICING_TS,'''' as JRN_SCN,''Y'' as JRN_CLEANSED_FLAG,''1'' as JRN_CURRENT_FLAG from '||owner||'.'||table_name||' a ;'
else 
'create or replace view MIRO.MV_'||table_name||' as select a.*,SYSDATE-1 as JRN_EFF_START_DTTM,SYSDATE+1 as JRN_EFF_END_DTTM,''I'' as JRN_FLAG,SYSDATE-1 as JRN_SLICING_TS,'''' as JRN_SCN,''Y'' as JRN_CLEANSED_FLAG,''1'' as JRN_CURRENT_FLAG from '||owner||'.'||table_name||' a ;'
end as x
from all_tables
where owner in ('MDMADM','CCBADM');
/
select 'grant select on '||owner||'.'||view_name||' to REPORTER with grant option;' from all_views where owner in ('MIRO');
/
select case 
when owner='MDMADM' and table_name like 'CI_%' 
then 'create or replace view REPORTER.MV_MDM_'||table_name||' as select a.*,SYSDATE-1 as JRN_EFF_START_DTTM,SYSDATE+1 as JRN_EFF_END_DTTM,''I'' as JRN_FLAG,SYSDATE-1 as JRN_SLICING_TS,'''' as JRN_SCN,''Y'' as JRN_CLEANSED_FLAG,''1'' as JRN_CURRENT_FLAG from '||owner||'.'||table_name||' a ;'
else 
'create or replace view REPORTER.MV_'||table_name||' as select a.*,SYSDATE-1 as JRN_EFF_START_DTTM,SYSDATE+1 as JRN_EFF_END_DTTM,''I'' as JRN_FLAG,SYSDATE-1 as JRN_SLICING_TS,'''' as JRN_SCN,''Y'' as JRN_CLEANSED_FLAG,''1'' as JRN_CURRENT_FLAG from '||owner||'.'||table_name||' a ;'
end as x
from all_tables
where owner in ('MDMADM','CCBADM');
select 'spool off' from dual
/
spool off
set feed on
set pages 15
set lines 80
set trimspool off