#cat OPSDM002.dim_provider.20080602.sql
CONNECT TO TST103 ;
SET CURRENT SCHEMA OPSDM002 ;
CREATE TABLE OPSDM002.DIM_PROVIDER (
PROV_ID CHAR(7) NOT NULL ,
PROV_FST_NM VARCHAR(30) NOT NULL ,
DEA_NBR CHAR(9) NOT NULL ,
DEA_ALPH_NBR CHAR(2) NOT NULL ,
COS_PROV_SPCL_CD SMALLINT NOT NULL ,
UPDT_DT DATE NOT NULL WITH DEFAULT CURRENT DATE ,
TIN CHAR(9) NOT NULL ,
SITE_CD CHAR(3) NOT NULL ,
NAT_ASSOC_BD_PHRM_NBR CHAR(7) NOT NULL ,
MPIN INTEGER NOT NULL ,
PROV_LST_NM VARCHAR(40) NOT NULL ,
LOAD_DT DATE NOT NULL WITH DEFAULT CURRENT DATE ,
PROV_SYS_ID INTEGER NOT NULL ,
ZIP_CD CHAR(5) NOT NULL ,
UNIQ_PROV_SYS_ID BIGINT NOT NULL )
COMPRESS YES
IN TSOPS06_S02 INDEX IN TSOPIX06_S02 ;
CREATE INDEX OPSDM002.IDX1_PROV_COS_UNET ON OPSDM002.DIM_PROVIDER
(SITE_CD ASC,
PROV_ID ASC,
MPIN ASC)
ALLOW REVERSE SCANS;
CREATE INDEX OPSDM002.IDX2_PROVIDER_TIN ON OPSDM002.DIM_PROVIDER
(TIN ASC)
ALLOW REVERSE SCANS;
CREATE INDEX OPSDM002.IDX2_TIN_UNIQ_PROV ON OPSDM002.DIM_PROVIDER
(TIN ASC,
UNIQ_PROV_SYS_ID ASC)
ALLOW REVERSE SCANS;
CREATE INDEX OPSDM002.IDX3_UNIQ_PROV ON OPSDM002.DIM_PROVIDER
(UNIQ_PROV_SYS_ID ASC)
ALLOW REVERSE SCANS;
CREATE UNIQUE INDEX OPSDM002.PK1_PROVIDER ON OPSDM002.DIM_PROVIDER
(PROV_SYS_ID ASC)
CLUSTER ALLOW REVERSE SCANS;
ALTER TABLE OPSDM002.DIM_PROVIDER
ADD CONSTRAINT PK1_PROVIDER PRIMARY KEY
(PROV_SYS_ID);
CREATE ALIAS OPSDM002.PROVIDER FOR OPSDM002.DIM_PROVIDER;
CREATE VIEW OPSDM002.VW_PROVIDER_MBR_PRI ( MBR_PRI_PROV_SYS_ID, MBR_PRI_COS_PROV_SPCL_CD,
MBR_PRI_DEA_ALPH_NBR, MBR_PRI_DEA_NBR, MBR_PRI_MPIN, MBR_PRI_NAT_ASSOC_BD_PHRM_NBR,
MBR_PRI_PROV_FST_NM, MBR_PRI_PROV_ID, MBR_PRI_PROV_LST_NM, MBR_PRI_SITE_CD,
MBR_PRI_TIN, MBR_PRI_ZIP_CD, MBR_PRI_UNIQ_PROV_SYS_ID, MBR_PRI_LOAD_DT,
MBR_PRI_UPDT_DT) AS SELECT DIM_PROVIDER.PROV_SYS_ID, DIM_PROVIDER.COS_PROV_SPCL_CD,
DIM_PROVIDER.DEA_ALPH_NBR, DIM_PROVIDER.DEA_NBR, DIM_PROVIDER.MPIN, DIM_PROVIDER.NAT_ASSOC_BD_PHRM_NBR,
DIM_PROVIDER.PROV_FST_NM, DIM_PROVIDER.PROV_ID, DIM_PROVIDER.PROV_LST_NM,
DIM_PROVIDER.SITE_CD, DIM_PROVIDER.TIN, DIM_PROVIDER.ZIP_CD, DIM_PROVIDER.UNIQ_PROV_SYS_ID,
DIM_PROVIDER.LOAD_DT, DIM_PROVIDER.UPDT_DT FROM DIM_PROVIDER;
CREATE VIEW OPSDM002.VW_PROVIDER_REF ( REF_PROV_SYS_ID, REF_COS_PROV_SPCL_CD,
REF_DEA_ALPH_NBR, REF_DEA_NBR, REF_MPIN, REF_NAT_ASSOC_BD_PHRM_NBR,
REF_PROV_FST_NM, REF_PROV_ID, REF_PROV_LST_NM, REF_SITE_CD, REF_TIN,
REF_ZIP_CD, REF_UNIQ_PROV_SYS_ID, REF_LOAD_DT, REF_UPDT_DT) AS
SELECT DIM_PROVIDER.PROV_SYS_ID, DIM_PROVIDER.COS_PROV_SPCL_CD, DIM_PROVIDER.DEA_ALPH_NBR,
DIM_PROVIDER.DEA_NBR, DIM_PROVIDER.MPIN, DIM_PROVIDER.NAT_ASSOC_BD_PHRM_NBR,
DIM_PROVIDER.PROV_FST_NM, DIM_PROVIDER.PROV_ID, DIM_PROVIDER.PROV_LST_NM,
DIM_PROVIDER.SITE_CD, DIM_PROVIDER.TIN, DIM_PROVIDER.ZIP_CD, DIM_PROVIDER.UNIQ_PROV_SYS_ID,
DIM_PROVIDER.LOAD_DT, DIM_PROVIDER.UPDT_DT FROM DIM_PROVIDER;
CREATE VIEW OPSDM002.VW_PROVIDER_SRVC ( SRVC_PROV_SYS_ID, SRVC_COS_PROV_SPCL_CD,
SRVC_DEA_ALPH_NBR, SRVC_DEA_NBR, SRVC_MPIN, SRVC_NAT_ASSOC_BD_PHRM_NBR,
SRVC_PROV_FST_NM, SRVC_PROV_ID, SRVC_PROV_LST_NM, SRVC_SITE_CD,
SRVC_TIN, SRVC_ZIP_CD, SRVC_UNIQ_PROV_SYS_ID, SRVC_LOAD_DT, SRVC_UPDT_DT)
AS SELECT DIM_PROVIDER.PROV_SYS_ID, DIM_PROVIDER.COS_PROV_SPCL_CD, DIM_PROVIDER.DEA_ALPH_NBR,
DIM_PROVIDER.DEA_NBR, DIM_PROVIDER.MPIN, DIM_PROVIDER.NAT_ASSOC_BD_PHRM_NBR,
DIM_PROVIDER.PROV_FST_NM, DIM_PROVIDER.PROV_ID, DIM_PROVIDER.PROV_LST_NM,
DIM_PROVIDER.SITE_CD, DIM_PROVIDER.TIN, DIM_PROVIDER.ZIP_CD, DIM_PROVIDER.UNIQ_PROV_SYS_ID,
DIM_PROVIDER.LOAD_DT, DIM_PROVIDER.UPDT_DT FROM DIM_PROVIDER;
ALTER TABLE OPSDM002.DIM_PROVIDER
ADD CONSTRAINT FK1_UNIQ_PRV_SYS FOREIGN KEY
(UNIQ_PROV_SYS_ID)
REFERENCES UHCDM001.DIM_UNIQUE_PROVIDER
(UNIQ_PROV_SYS_ID)
ON DELETE NO ACTION
ON UPDATE NO ACTION
NOT ENFORCED
ENABLE QUERY OPTIMIZATION;
GRANT SELECT ON TABLE OPSDM002.VW_.DIM_PROVIDER TO GROUP DSDBDEV ;
GRANT SELECT ON TABLE OPSDM002.VW_.DIM_PROVIDER TO GROUP UHCDMTST ;
GRANT SELECT ON TABLE OPSDM002.VW_.DIM_PROVIDER TO GROUP CLOSEL01 ;
GRANT SELECT ON TABLE OPSDM002.VW_.DIM_PROVIDER TO GROUP CLOSEL02 ;
GRANT SELECT ON TABLE OPSDM002.VW_.DIM_PROVIDER TO GROUP CLOSEL04 ;
GRANT SELECT ON TABLE OPSDM002.VW_.DIM_PROVIDER TO GROUP CLOSEL05 ;
GRANT SELECT ON TABLE OPSDM002.VW_.DIM_PROVIDER TO GROUP SELCOE01 ;
GRANT CONTROL ON TABLE OPSDM002.VW_.DIM_PROVIDER TO GROUP UHCDMDBA ;
GRANT CONTROL ON TABLE OPSDM002.VW_.DIM_PROVIDER TO USER ESHIRLE ;
GRANT CONTROL ON TABLE OPSDM002.VW_.DIM_PROVIDER TO USER SNIETER ;
GRANT CONTROL ON TABLE OPSDM002.VW_.DIM_PROVIDER TO USER LTREXL1 ;
GRANT CONTROL ON TABLE OPSDM002.VW_.DIM_PROVIDER TO USER RSMIT11 ;
GRANT CONTROL ON TABLE OPSDM002.VW_.DIM_PROVIDER TO USER KJOHNS5 ;
GRANT CONTROL ON TABLE OPSDM002.VW_.DIM_PROVIDER TO USER AMENSEY ;
GRANT SELECT ON TABLE OPSDM002.VW_.PROVIDER_MBR_PRI TO GROUP DSDBDEV ;
GRANT SELECT ON TABLE OPSDM002.VW_.PROVIDER_MBR_PRI TO GROUP UHCDMTST ;
GRANT SELECT ON TABLE OPSDM002.VW_.PROVIDER_MBR_PRI TO GROUP CLOSEL01 ;
GRANT SELECT ON TABLE OPSDM002.VW_.PROVIDER_MBR_PRI TO GROUP CLOSEL02 ;
GRANT SELECT ON TABLE OPSDM002.VW_.PROVIDER_MBR_PRI TO GROUP CLOSEL04 ;
GRANT SELECT ON TABLE OPSDM002.VW_.PROVIDER_MBR_PRI TO GROUP CLOSEL05 ;
GRANT SELECT ON TABLE OPSDM002.VW_.PROVIDER_MBR_PRI TO GROUP SELCOE01 ;
GRANT CONTROL ON TABLE OPSDM002.VW_.PROVIDER_MBR_PRI TO GROUP UHCDMDBA ;
GRANT CONTROL ON TABLE OPSDM002.VW_.PROVIDER_MBR_PRI TO USER ESHIRLE ;
GRANT CONTROL ON TABLE OPSDM002.VW_.PROVIDER_MBR_PRI TO USER SNIETER ;
GRANT CONTROL ON TABLE OPSDM002.VW_.PROVIDER_MBR_PRI TO USER LTREXL1 ;
GRANT CONTROL ON TABLE OPSDM002.VW_.PROVIDER_MBR_PRI TO USER RSMIT11 ;
GRANT CONTROL ON TABLE OPSDM002.VW_.PROVIDER_MBR_PRI TO USER KJOHNS5 ;
GRANT CONTROL ON TABLE OPSDM002.VW_.PROVIDER_MBR_PRI TO USER AMENSEY ;
GRANT SELECT ON TABLE OPSDM002.VW_.PROVIDER_REF TO GROUP DSDBDEV ;
GRANT SELECT ON TABLE OPSDM002.VW_.PROVIDER_REF TO GROUP UHCDMTST ;
GRANT SELECT ON TABLE OPSDM002.VW_.PROVIDER_REF TO GROUP CLOSEL01 ;
GRANT SELECT ON TABLE OPSDM002.VW_.PROVIDER_REF TO GROUP CLOSEL02 ;
GRANT SELECT ON TABLE OPSDM002.VW_.PROVIDER_REF TO GROUP CLOSEL04 ;
GRANT SELECT ON TABLE OPSDM002.VW_.PROVIDER_REF TO GROUP CLOSEL05 ;
GRANT SELECT ON TABLE OPSDM002.VW_.PROVIDER_REF TO GROUP SELCOE01 ;
GRANT CONTROL ON TABLE OPSDM002.VW_.PROVIDER_REF TO GROUP UHCDMDBA ;
GRANT CONTROL ON TABLE OPSDM002.VW_.PROVIDER_REF TO USER ESHIRLE ;
GRANT CONTROL ON TABLE OPSDM002.VW_.PROVIDER_REF TO USER SNIETER ;
GRANT CONTROL ON TABLE OPSDM002.VW_.PROVIDER_REF TO USER LTREXL1 ;
GRANT CONTROL ON TABLE OPSDM002.VW_.PROVIDER_REF TO USER RSMIT11 ;
GRANT CONTROL ON TABLE OPSDM002.VW_.PROVIDER_REF TO USER KJOHNS5 ;
GRANT CONTROL ON TABLE OPSDM002.VW_.PROVIDER_REF TO USER AMENSEY ;
GRANT SELECT ON TABLE OPSDM002.VW_.PROVIDER_SRVC TO GROUP DSDBDEV ;
GRANT SELECT ON TABLE OPSDM002.VW_.PROVIDER_SRVC TO GROUP UHCDMTST ;
GRANT SELECT ON TABLE OPSDM002.VW_.PROVIDER_SRVC TO GROUP CLOSEL01 ;
GRANT SELECT ON TABLE OPSDM002.VW_.PROVIDER_SRVC TO GROUP CLOSEL02 ;
GRANT SELECT ON TABLE OPSDM002.VW_.PROVIDER_SRVC TO GROUP CLOSEL04 ;
GRANT SELECT ON TABLE OPSDM002.VW_.PROVIDER_SRVC TO GROUP CLOSEL05 ;
GRANT SELECT ON TABLE OPSDM002.VW_.PROVIDER_SRVC TO GROUP SELCOE01 ;
GRANT CONTROL ON TABLE OPSDM002.VW_.PROVIDER_SRVC TO GROUP UHCDMDBA ;
GRANT CONTROL ON TABLE OPSDM002.VW_.PROVIDER_SRVC TO USER ESHIRLE ;
GRANT CONTROL ON TABLE OPSDM002.VW_.PROVIDER_SRVC TO USER SNIETER ;
GRANT CONTROL ON TABLE OPSDM002.VW_.PROVIDER_SRVC TO USER LTREXL1 ;
GRANT CONTROL ON TABLE OPSDM002.VW_.PROVIDER_SRVC TO USER RSMIT11 ;
GRANT CONTROL ON TABLE OPSDM002.VW_.PROVIDER_SRVC TO USER KJOHNS5 ;
GRANT CONTROL ON TABLE OPSDM002.VW_.PROVIDER_SRVC TO USER AMENSEY ;
CREATE TABLE OPSDM002.RPL_DIM_PROVIDER AS (SELECT * FROM OPSDM002.DIM_PROVIDER
) DATA INITIALLY DEFERRED REFRESH DEFERRED IN TS_REPL_S02 REPLICATED;
REFRESH TABLE OPSDM002.RPL_DIM_PROVIDER;
CREATE INDEX OPSDM002.RPL_IDX1_PROV_COS_UNET ON OPSDM002.RPL_DIM_PROVIDER
(SITE_CD ASC,
PROV_ID ASC,
MPIN ASC)
ALLOW REVERSE SCANS;
CREATE INDEX OPSDM002.RPL_IDX2_PROVIDER_TIN ON OPSDM002.RPL_DIM_PROVIDER
(TIN ASC)
ALLOW REVERSE SCANS;
CREATE INDEX OPSDM002.RPL_IDX2_TIN_UNIQ_PROV ON OPSDM002.RPL_DIM_PROVIDER
(TIN ASC,
UNIQ_PROV_SYS_ID ASC)
ALLOW REVERSE SCANS;
CREATE INDEX OPSDM002.RPL_IDX3_UNIQ_PROV ON OPSDM002.RPL_DIM_PROVIDER
(UNIQ_PROV_SYS_ID ASC)
ALLOW REVERSE SCANS;
CREATE INDEX OPSDM002.RPL_PK1_PROVIDER ON OPSDM002.RPL_DIM_PROVIDER
(PROV_SYS_ID ASC)
ALLOW REVERSE SCANS;
COMMIT WORK ;
CONNECT RESET ;
TERMINATE ;
Below is the output that we want ( I am coping only that part that needs to be changed from above ) .
GRANT SELECT ON TABLE OPSDM002.DIM_PROVIDER TO GROUP DSDBDEV ;
GRANT SELECT ON TABLE OPSDM002.DIM_PROVIDER TO GROUP UHCDMTST ;
GRANT SELECT ON TABLE OPSDM002.DIM_PROVIDER TO GROUP CLOSEL01 ;
GRANT SELECT ON TABLE OPSDM002.DIM_PROVIDER TO GROUP CLOSEL02 ;
GRANT SELECT ON TABLE OPSDM002.DIM_PROVIDER TO GROUP CLOSEL04 ;
GRANT SELECT ON TABLE OPSDM002.DIM_PROVIDER TO GROUP CLOSEL05 ;
GRANT SELECT ON TABLE OPSDM002.DIM_PROVIDER TO GROUP SELCOE01 ;
GRANT CONTROL ON TABLE OPSDM002.DIM_PROVIDER TO GROUP UHCDMDBA ;
GRANT CONTROL ON TABLE OPSDM002.DIM_PROVIDER TO USER ESHIRLE ;
GRANT CONTROL ON TABLE OPSDM002.DIM_PROVIDER TO USER SNIETER ;
GRANT CONTROL ON TABLE OPSDM002.DIM_PROVIDER TO USER LTREXL1 ;
GRANT CONTROL ON TABLE OPSDM002.DIM_PROVIDER TO USER RSMIT11 ;
GRANT CONTROL ON TABLE OPSDM002.DIM_PROVIDER TO USER KJOHNS5 ;
GRANT CONTROL ON TABLE OPSDM002.DIM_PROVIDER TO USER AMENSEY ;
GRANT SELECT ON TABLE OPSDM002.VW_PROVIDER_MBR_PRI TO GROUP DSDBDEV ;
GRANT SELECT ON TABLE OPSDM002.VW_PROVIDER_MBR_PRI TO GROUP UHCDMTST ;
GRANT SELECT ON TABLE OPSDM002.VW_PROVIDER_MBR_PRI TO GROUP CLOSEL01 ;
GRANT SELECT ON TABLE OPSDM002.VW_PROVIDER_MBR_PRI TO GROUP CLOSEL02 ;
GRANT SELECT ON TABLE OPSDM002.VW_PROVIDER_MBR_PRI TO GROUP CLOSEL04 ;
GRANT SELECT ON TABLE OPSDM002.VW_PROVIDER_MBR_PRI TO GROUP CLOSEL05 ;
GRANT SELECT ON TABLE OPSDM002.VW_PROVIDER_MBR_PRI TO GROUP SELCOE01 ;
GRANT CONTROL ON TABLE OPSDM002.VW_PROVIDER_MBR_PRI TO GROUP UHCDMDBA ;
GRANT CONTROL ON TABLE OPSDM002.VW_PROVIDER_MBR_PRI TO USER ESHIRLE ;
GRANT CONTROL ON TABLE OPSDM002.VW_PROVIDER_MBR_PRI TO USER SNIETER ;
GRANT CONTROL ON TABLE OPSDM002.VW_PROVIDER_MBR_PRI TO USER LTREXL1 ;
GRANT CONTROL ON TABLE OPSDM002.VW_PROVIDER_MBR_PRI TO USER RSMIT11 ;
GRANT CONTROL ON TABLE OPSDM002.VW_PROVIDER_MBR_PRI TO USER KJOHNS5 ;
GRANT CONTROL ON TABLE OPSDM002.VW_PROVIDER_MBR_PRI TO USER AMENSEY ;
GRANT SELECT ON TABLE OPSDM002.VW_PROVIDER_REF TO GROUP DSDBDEV ;
GRANT SELECT ON TABLE OPSDM002.VW_PROVIDER_REF TO GROUP UHCDMTST ;
GRANT SELECT ON TABLE OPSDM002.VW_PROVIDER_REF TO GROUP CLOSEL01 ;
GRANT SELECT ON TABLE OPSDM002.VW_PROVIDER_REF TO GROUP CLOSEL02 ;
GRANT SELECT ON TABLE OPSDM002.VW_PROVIDER_REF TO GROUP CLOSEL04 ;
GRANT SELECT ON TABLE OPSDM002.VW_PROVIDER_REF TO GROUP CLOSEL05 ;
GRANT SELECT ON TABLE OPSDM002.VW_PROVIDER_REF TO GROUP SELCOE01 ;
GRANT CONTROL ON TABLE OPSDM002.VW_PROVIDER_REF TO GROUP UHCDMDBA ;
GRANT CONTROL ON TABLE OPSDM002.VW_PROVIDER_REF TO USER ESHIRLE ;
GRANT CONTROL ON TABLE OPSDM002.VW_PROVIDER_REF TO USER SNIETER ;
GRANT CONTROL ON TABLE OPSDM002.VW_PROVIDER_REF TO USER LTREXL1 ;
GRANT CONTROL ON TABLE OPSDM002.VW_PROVIDER_REF TO USER RSMIT11 ;
GRANT CONTROL ON TABLE OPSDM002.VW_PROVIDER_REF TO USER KJOHNS5 ;
GRANT CONTROL ON TABLE OPSDM002.VW_PROVIDER_REF TO USER AMENSEY ;
GRANT SELECT ON TABLE OPSDM002.VW_PROVIDER_SRVC TO GROUP DSDBDEV ;
GRANT SELECT ON TABLE OPSDM002.VW_PROVIDER_SRVC TO GROUP UHCDMTST ;
GRANT SELECT ON TABLE OPSDM002.VW_PROVIDER_SRVC TO GROUP CLOSEL01 ;
GRANT SELECT ON TABLE OPSDM002.VW_PROVIDER_SRVC TO GROUP CLOSEL02 ;
GRANT SELECT ON TABLE OPSDM002.VW_PROVIDER_SRVC TO GROUP CLOSEL04 ;
GRANT SELECT ON TABLE OPSDM002.VW_PROVIDER_SRVC TO GROUP CLOSEL05 ;
GRANT SELECT ON TABLE OPSDM002.VW_PROVIDER_SRVC TO GROUP SELCOE01 ;
GRANT CONTROL ON TABLE OPSDM002.VW_PROVIDER_SRVC TO GROUP UHCDMDBA ;
GRANT CONTROL ON TABLE OPSDM002.VW_PROVIDER_SRVC TO USER ESHIRLE ;
GRANT CONTROL ON TABLE OPSDM002.VW_PROVIDER_SRVC TO USER SNIETER ;
GRANT CONTROL ON TABLE OPSDM002.VW_PROVIDER_SRVC TO USER LTREXL1 ;
GRANT CONTROL ON TABLE OPSDM002.VW_PROVIDER_SRVC TO USER RSMIT11 ;
GRANT CONTROL ON TABLE OPSDM002.VW_PROVIDER_SRVC TO USER KJOHNS5 ;
GRANT CONTROL ON TABLE OPSDM002.VW_PROVIDER_SRVC TO USER AMENSEY ;
Thanks ,