CREATE USER EPBC IDENTIFIED BY epbc DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP PROFILE DEFAULT;
GRANT CONNECT TO EPBC;
GRANT RESOURCE TO EPBC;
GRANT UNLIMITED TABLESPACE TO EPBC;
GRANT ALTER USER TO EPBC;
GRANT CREATE USER TO EPBC;
COMMIT;

CREATE USER EPB IDENTIFIED BY epb DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP PROFILE DEFAULT;
GRANT CONNECT TO EPB;
GRANT RESOURCE TO EPB;
GRANT CREATE ANY DIRECTORY TO EPB;
GRANT UNLIMITED TABLESPACE TO EPB;
GRANT CREATE VIEW TO EPB;
GRANT CREATE TABLE TO EPB;
GRANT CREATE ANY TRIGGER TO EPB;
GRANT CREATE ANY MATERIALIZED VIEW TO EPB;
COMMIT;

--GRANT CREATE DATABASE LINK TO EPB;   --CREATE AND VISTA DBLINK



CREATE USER TESTEPB IDENTIFIED BY epb DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP PROFILE DEFAULT;
GRANT CONNECT TO TESTEPB;
GRANT RESOURCE TO TESTEPB;
GRANT CREATE ANY DIRECTORY TO TESTEPB;
GRANT UNLIMITED TABLESPACE TO TESTEPB;
GRANT CREATE VIEW TO TESTEPB;
GRANT CREATE TABLE TO TESTEPB;
GRANT CREATE ANY TRIGGER TO TESTEPB;
GRANT CREATE ANY MATERIALIZED VIEW TO TESTEPB;
COMMIT;

--GRANT CREATE DATABASE LINK TO TESTEPB;


--11G password expired--
--SYSTEM USER RUN

conn sys/***@*** as sysdba
select * from dba_profiles WHERE dba_profiles.profile='DEFAULT'; 

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED; 
alter user smsc identified by <Ô­À´µÄÃÜÂë> 



-- for 11g stmp mail--

--SYSTEM USER RUN
--for first user EPB

--/
begin
  dbms_network_acl_admin.create_acl (acl => 'UTL_INADDR.xml',description => 'utl_inaddr',
      principal => 'EPB',  
      is_grant => TRUE,privilege => 'resolve');
  commit;
end;
/

--/
begin
  dbms_network_acl_admin.add_privilege (acl => 'UTL_INADDR.xml',
      principal => 'EPB',
      is_grant => TRUE,privilege => 'connect');
  commit;
end;
/

--/
begin
  dbms_network_acl_admin.assign_acl(acl => 'UTL_INADDR.xml',host => '*');
  commit;
end;
/


--for remove
--BEGIN
--  DBMS_NETWORK_ACL_ADMIN.drop_acl (acl => 'UTL_INADDR.xml');
--  COMMIT;
--END;
--/



--for second user TESTEPB

--/
begin
  dbms_network_acl_admin.add_privilege (acl => 'UTL_INADDR.xml',
      principal => 'TESTEPB',
      is_grant => TRUE,privilege => 'resolve');
  commit;
end;
/

--/
begin
  dbms_network_acl_admin.add_privilege (acl => 'UTL_INADDR.xml',
      principal => 'TESTEPB',
      is_grant => TRUE,privilege => 'connect');
  commit;
end;
/



-- end for 11g--




CONNECT EPBC/epbc@<DB SID>

EPBC????????
CREATE TABLE EPB_DBUSER (
 DBUSER_NAME  		VARCHAR2(64),
 DBUSER_PWD       	VARCHAR2(2000) NOT NULL,
 CREATE_DATE DATE DEFAULT SYSDATE,
 CONSTRAINT PK_EPB_DBUSER PRIMARY KEY (DBUSER_NAME)
);

------------------------

imp system/epb@ORCL fromuser=epbqa touser=EPB file=d:\epbqa1.dmp log=d:\imp_epbqa1.log

------------------------

--start P854£¬have 5 jobs--

conn <dbuser>/<****>@<DB SID>



--/
declare
  cursor c1 is select job from user_jobs;
begin
  for c1rec in c1 loop
    dbms_job.remove(c1rec.job);
  end loop;
  COMMIT;
end;
/

--/
declare
  jobno number;
begin
  dbms_job.submit(jobno,'EP_SYSUTL.ep_system_job;',TRUNC(SYSDATE) + 10/86400,'SYSDATE + 60/86400',false,0,true);
  dbms_job.submit(jobno,'EP_SYSUTL.ep_syn_job;',TRUNC(SYSDATE) + 10/86400,'SYSDATE + 60/86400',false,0,true);
  dbms_job.submit(jobno,'EP_SYSUTL.ep_clear_syn_data;',TRUNC(SYSDATE) + 1,'SYSDATE + 1',false,0,true);
  dbms_job.submit(jobno,'EP_SYSUTL.epb_backend_gen_doc;',TRUNC(SYSDATE) + 10/86400,'SYSDATE + 10/86400',false,0,true);
  dbms_job.submit(jobno,'EP_SYSUTL.ep_pos_transaction_upload;',TRUNC(SYSDATE) + 10/86400,'SYSDATE + 60/86400',false,0,true);
  COMMIT;
end;
/




------------------

CHECK:
select * from user_jobs;

select * from sys_trans_send_task_buf

------------------

delete from EP_WEATHER;

commit;


delete from sys_site;

commit;

#################################

--new version start v778

--GRANT CREATE MATERIALIZED VIEW TO EPB;

drop MATERIALIZED VIEW LOG ON GLDTL;
CREATE MATERIALIZED VIEW LOG ON GLDTL
WITH ROWID, SEQUENCE(REC_KEY,ORG_ID,TRANS_TYPE,ACC_ID,FYEAR,FPERIOD,LOC_ID,CURR_ID,EMP_ID,PROJ_ID,DEPT_ID,ANA_ID1,ANA_ID2,ANA_ID3,ANA_ID4,ANA_ID5,ANA_ID6,
ANA_ID7,ANA_ID8,ANA_ID9,ANA_ID10,CS_ID,BUDGET_FLG,COST_ID,CURR_DR,CURR_CR,DR,CR) 
INCLUDING NEW VALUES;

drop MATERIALIZED VIEW GLSUM_ANA;

CREATE MATERIALIZED VIEW GLSUM_ANA
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS 
SELECT ORG_ID,TRANS_TYPE,ACC_ID,FYEAR,FPERIOD,LOC_ID,CURR_ID,EMP_ID,PROJ_ID,DEPT_ID,ANA_ID1,ANA_ID2,ANA_ID3,ANA_ID4,ANA_ID5,ANA_ID6,
ANA_ID7,ANA_ID8,ANA_ID9,ANA_ID10,CS_ID,BUDGET_FLG,COST_ID,
COUNT(*) CNT,SUM(REC_KEY) REC_KEY,SUM(CURR_DR) CURR_DR, SUM(CURR_CR) CURR_CR, SUM(DR) DR,SUM(CR) CR,
COUNT(REC_KEY) REC_CNT,COUNT(CURR_DR) CURR_DR_CNT, COUNT(CURR_CR) CURR_CR_CNT, COUNT(DR) DR_CNT,COUNT(CR) CR_CNT
FROM GLDTL 
GROUP BY ORG_ID,TRANS_TYPE,ACC_ID,FYEAR,FPERIOD,LOC_ID,CURR_ID,EMP_ID,PROJ_ID,DEPT_ID,ANA_ID1,ANA_ID2,ANA_ID3,ANA_ID4,ANA_ID5,ANA_ID6,
ANA_ID7,ANA_ID8,ANA_ID9,ANA_ID10,CS_ID,BUDGET_FLG,COST_ID;


CREATE INDEX I_GLSUM_ANA1 ON GLSUM_ANA (ORG_ID,ACC_ID);
CREATE INDEX I_GLSUM_ANA2 ON GLSUM_ANA (ORG_ID,FYEAR,FPERIOD);
CREATE INDEX I_GLSUM_ANA3 ON GLSUM_ANA (ORG_ID,ACC_ID,FYEAR,FPERIOD);


DROP materialized view log on INV_SUMMARY;
CREATE MATERIALIZED VIEW LOG ON INV_SUMMARY 
WITH ROWID, SEQUENCE(REC_KEY,ORG_ID,VALAREA_ID,STORE_ID,STK_ID,BATCH_ID1,BATCH_ID2,BATCH_ID3,BATCH_ID4,SRN_ID,DRCR_FLG,STK_QTY,STK_VALUE,TRN_STK_VALUE) 
INCLUDING NEW VALUES;

DROP MATERIALIZED VIEW INV_SUMMARY_VIEW_STK;
CREATE MATERIALIZED VIEW INV_SUMMARY_VIEW_STK
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS 
SELECT SUM(A.REC_KEY) REC_KEY, A.ORG_ID,A.VALAREA_ID,A.STORE_ID,A.STK_ID,A.BATCH_ID1,A.BATCH_ID2,A.BATCH_ID3,A.BATCH_ID4,A.SRN_ID,COUNT(*) COUNT,
COUNT(A.DRCR_FLG*A.STK_QTY) QTY_COUNT,COUNT(A.DRCR_FLG*A.STK_VALUE) VALUE_COUNT,COUNT(A.DRCR_FLG*A.TRN_STK_VALUE) TRN_VALUE_COUNT,
SUM(A.DRCR_FLG*A.STK_QTY) STK_QTY,SUM(A.DRCR_FLG*A.STK_VALUE) STK_VALUE,SUM(A.DRCR_FLG*A.TRN_STK_VALUE) TRN_STK_VALUE
FROM INV_SUMMARY A
GROUP BY A.ORG_ID,A.VALAREA_ID,A.STORE_ID,A.STK_ID,A.BATCH_ID1,A.BATCH_ID2,A.BATCH_ID3,A.BATCH_ID4,A.SRN_ID;

#################################


-- Old EPB job before P854


--Stop jobs

--/
declare
  cursor c1 is select job from user_jobs where what like 'EP_SYSUTL%';
begin
  for c1rec in c1 loop
    dbms_job.remove(c1rec.job);
  end loop;
  COMMIT;
end;
/



-- Start jobs

--/
declare
  jobno number;
begin
  dbms_job.submit(jobno,'EP_SYSUTL.ep_system_job;',TRUNC(SYSDATE) + 10/86400,'SYSDATE + 60/86400',false,0,true);
  dbms_job.submit(jobno,'EP_SYSUTL.ep_syn_job;',TRUNC(SYSDATE) + 10/86400,'SYSDATE + 60/86400',false,0,true);
  dbms_job.submit(jobno,'EP_SYSUTL.ep_clear_syn_data;',TRUNC(SYSDATE) + 1,'SYSDATE + 1',false,0,true);
  dbms_job.submit(jobno,'EP_SYSUTL.epb_backend_gen_doc;',TRUNC(SYSDATE) + 10/86400,'SYSDATE + 10/86400',false,0,true);
  COMMIT;
end;
/


commit;


+++++++++++++++++++++++++++++++


-- Old EPB job before P844

--Stop jobs

--/
declare
  cursor c1 is select job from user_jobs where what like 'EP_SYSUTL%';
begin
  for c1rec in c1 loop
    dbms_job.remove(c1rec.job);
  end loop;
  COMMIT;
end;
/




-- Update Version

--/
declare
  jobno number;
begin
  dbms_job.submit(jobno,'EP_SYSUTL.ep_system_job;',TRUNC(SYSDATE) + 10/86400,'SYSDATE + 60/86400',false,0,true);
  COMMIT;
end;
/


--/
declare
  jobno number;
begin
  dbms_job.submit(jobno,'EP_SYSUTL.ep_syn_job;',TRUNC(SYSDATE) + 10/86400,'SYSDATE + 60/86400',false,0,true);
  COMMIT;
end;
/
 

--/
declare
  jobno number;
begin
  dbms_job.submit(jobno,'EP_SYSUTL.ep_clear_syn_data;',TRUNC(SYSDATE) + 1,'SYSDATE + 1',false,0,true);
  COMMIT;
end;
/

commit;

#################################