MW_PLSQL_CHECK_GRANTS
SELECT 'GRANT SELECT ON ' || CASE WHEN RD.SQLTABLENAME = ' ' THEN 'PS_' || RD.RECNAME ELSE RD.SQLTABLENAME END || ' TO ' || G.ROLENAME 
        FROM PS_UO_GRANTS G, PSRECDEFN RD 
        WHERE RD.RECNAME = G.RECNAME 
          AND ( END_DATE >= trunc( SYSDATE ) OR END_DATE IS NULL ) 
          AND SELECT_FLAG = 'Y' 
UNION 
SELECT 'GRANT INSERT ON ' || CASE WHEN RD.SQLTABLENAME = ' ' THEN 'PS_' || RD.RECNAME ELSE RD.SQLTABLENAME END || ' TO ' || G.ROLENAME 
       FROM PS_UO_GRANTS G, PSRECDEFN RD 
       WHERE RD.RECNAME = G.RECNAME 
         AND ( END_DATE >= trunc( SYSDATE ) OR END_DATE IS NULL ) 
         AND INSERT_FL = 'Y' 
UNION 
SELECT 'GRANT UPDATE ON ' || CASE WHEN RD.SQLTABLENAME = ' ' THEN 'PS_' || RD.RECNAME ELSE RD.SQLTABLENAME END || ' TO ' || G.ROLENAME 
       FROM PS_UO_GRANTS G, PSRECDEFN RD 
       WHERE RD.RECNAME = G.RECNAME 
         AND ( END_DATE >= trunc( SYSDATE ) OR END_DATE IS NULL ) 
         AND UPDATE_FLAG = 'Y' 
UNION 
SELECT 'GRANT DELETE ON ' || CASE WHEN RD.SQLTABLENAME = ' ' THEN 'PS_' || RD.RECNAME ELSE RD.SQLTABLENAME END || ' TO ' || G.ROLENAME 
       FROM PS_UO_GRANTS G, PSRECDEFN RD 
       WHERE RD.RECNAME = G.RECNAME 
         AND ( END_DATE >= trunc( SYSDATE ) OR END_DATE IS NULL ) 
         AND DELETE_FLAG = 'Y' 
MINUS 
SELECT 'GRANT ' || PRIVILEGE || ' ON ' || TABLE_NAME || ' TO ' || GRANTEE 
       FROM ALL_TAB_PRIVS A 
       WHERE GRANTOR = 'SYSADM'
*****************************************************************************************************************************************
MW_PLSQL_CHECK_PRIMARY_KEY_OK
SELECT COUNT(*), SUM( CASE WHEN tc.nullable = 'Y' AND TC.COLUMN_NAME NOT IN ( 'EFFDT','EFFDT_FROM', 'EFFDT_TO' ) THEN 1 ELSE 0 END ) 
       FROM ALL_IND_COLUMNS IC, ALL_TAB_COLUMNS TC
       WHERE IC.TABLE_NAME = :1 
         AND IC.INDEX_NAME LIKE 'PS\_%' ESCAPE '\' 
         AND TC.TABLE_NAME = IC.TABLE_NAME 
         AND TC.COLUMN_NAME = IC.COLUMN_NAME
*****************************************************************************************************************************************
MW_PLSQL_EXT_CHECK_GRANTS
SELECT 'grant select on ' || CASE WHEN RD.SQLTABLENAME = ' ' THEN 'PS_' || RD.RECNAME ELSE RD.SQLTABLENAME END || ' to UOESB_WEB_ROLE;' 
       FROM PS_UO_EXT_VIEW_REC EV, PSRECDEFN RD 
       WHERE EV.SCC_SAMPLE_DBLINK = 'ESB' 
         AND RD.RECNAME = EV.RECNAME 
MINUS 
SELECT 'grant select on ' || TABLE_NAME || ' to UOESB_WEB_ROLE;' 
       FROM :1 
       WHERE grantee = 'UOESB_WEB_ROLE' 
         AND PRIVILEGE = 'SELECT'
*****************************************************************************************************************************************
MW_PLSQL_EXT_VIEW_FIELDS
SELECT listagg( COLUMN_NAME, ', ' ) within GROUP ( ORDER BY COLUMN_ID ) 
       FROM USER_TAB_COLUMNS 
       WHERE TABLE_NAME = :1
*****************************************************************************************************************************************
MW_PLSQL_GET_EXT_VIEW_NAME
SELECT CASE WHEN RD.SQLTABLENAME = ' ' THEN 'PS_' || RD.RECNAME ELSE RD.SQLTABLENAME END 
       FROM PS_UO_EXT_VIEW_REC EVR, PSRECDEFN RD 
       WHERE RD.RECNAME = EVR.RECNAME 
         AND EVR.SCC_SAMPLE_DBLINK = :1
*****************************************************************************************************************************************
MW_PLSQL_GET_EXT_VIEW_TEXT
SELECT TEXT 
       FROM USER_VIEWS 
       WHERE VIEW_NAME = :1
*****************************************************************************************************************************************
MW_PLSQL_GET_MVIEWS
SELECT RD.RECNAME, CASE WHEN RD.SQLTABLENAME = ' ' THEN 'PS_' || RD.RECNAME ELSE RD.SQLTABLENAME END, 
       'sysdate ' || CASE UO_REFRESH_UNITS WHEN 'S' THEN '+' || MVR.UO_REFRESH_INTERVL || ' / 86400' 
                                           WHEN 'M' THEN '+' || MVR.UO_REFRESH_INTERVL || ' / 1400' 
                                           WHEN 'H' THEN '+' || MVR.UO_REFRESH_INTERVL || ' / 24' 
                                           WHEN 'D' THEN '' 
                                           WHEN 'W' THEN '+' || MVR.UO_REFRESH_INTERVL || ' * 7' 
                                           ELSE '+' || MVR.UO_REFRESH_INTERVL || ' * 7' END, 
       UO_RECREATE_FLAG 
  FROM PS_UO_MAT_VIEW_REC MVR, PSRECDEFN RD 
       WHERE MVR.ENABLED = 'Y' 
         AND RD.RECNAME = MVR.RECNAME 
         AND SCC_SAMPLE_DBLINK = :1 
       ORDER BY RD.RECNAME
*****************************************************************************************************************************************
MW_PLSQL_NOTNULLCLOB
SELECT COLUMN_NAME 
       FROM USER_TAB_COLUMNS 
       WHERE TABLE_NAME = :1 
         AND DATA_TYPE = 'CLOB' 
         AND NULLABLE = 'N'
*****************************************************************************************************************************************
MW_PLSQL_REVOKE_GRANTS
SELECT 'REVOKE ' || PRIVILEGE || ' ON ' || TABLE_NAME ||' FROM ' || GRANTEE 
       FROM ALL_TAB_PRIVS A 
       WHERE GRANTOR = 'SYSADM' 
         AND PRIVILEGE <> 'INHERIT PRIVILEGES' 
         AND GRANTEE NOT IN ( 'PEOPLE','PEOPLE2' ) 
         AND TABLE_NAME NOT LIKE 'BIN$%' 
MINUS 
SELECT 'REVOKE SELECT ON ' || CASE WHEN RD.SQLTABLENAME = ' ' THEN 'PS_' || RD.RECNAME ELSE RD.SQLTABLENAME END ||' FROM ' || G.ROLENAME 
       FROM PS_UO_GRANTS G, PSRECDEFN RD 
       WHERE RD.RECNAME = G.RECNAME 
         AND ( END_DATE >= trunc( SYSDATE ) OR END_DATE IS NULL ) 
         AND SELECT_FLAG = 'Y' minus 
SELECT 'REVOKE INSERT ON ' || CASE WHEN RD.SQLTABLENAME = ' ' THEN 'PS_' || RD.RECNAME ELSE RD.SQLTABLENAME END ||' FROM ' || G.ROLENAME 
       FROM PS_UO_GRANTS G, PSRECDEFN RD 
       WHERE RD.RECNAME = G.RECNAME 
         AND ( END_DATE >= trunc( SYSDATE ) OR END_DATE IS NULL ) 
         AND INSERT_FL = 'Y' 
MINUS 
SELECT 'REVOKE UPDATE ON ' || CASE WHEN RD.SQLTABLENAME = ' ' THEN 'PS_' || RD.RECNAME ELSE RD.SQLTABLENAME END ||' FROM ' || G.ROLENAME 
       FROM PS_UO_GRANTS G, PSRECDEFN RD 
       WHERE RD.RECNAME = G.RECNAME 
         AND ( END_DATE >= trunc( SYSDATE ) OR END_DATE IS NULL ) 
         AND UPDATE_FLAG = 'Y' 
MINUS 
SELECT 'REVOKE DELETE ON ' || CASE WHEN RD.SQLTABLENAME = ' ' THEN 'PS_' || RD.RECNAME ELSE RD.SQLTABLENAME END ||' FROM ' || G.ROLENAME 
       FROM PS_UO_GRANTS G, PSRECDEFN RD 
       WHERE RD.RECNAME = G.RECNAME 
         AND ( END_DATE >= trunc( SYSDATE ) OR END_DATE IS NULL ) 
         AND DELETE_FLAG = 'Y'
*****************************************************************************************************************************************
MW_RECORD_SRCH
SELECT RECNAME 
       FROM PSRECDEFN 
       WHERE RECTYPE = 0
*****************************************************************************************************************************************
MW_CHECK_CONSTRAINT
SELECT CONSTRAINT_NAME, 'Y' 
       FROM ALL_CONSTRAINTS 
       WHERE Owner = 'SYSADM' 
         AND CONSTRAINT_TYPE = 'P' 
         AND TABLE_NAME = :1
*****************************************************************************************************************************************
MW_CHECK_MATLOG
SELECT 'Y' 
       FROM ALL_MVIEW_LOGS 
       WHERE LOG_OWNER = 'SYSADM' 
         AND MASTER = :1
*****************************************************************************************************************************************
MW_GET_RECORD_KEYS
SELECT listagg( COLUMN_NAME, ', ' ) within GROUP ( ORDER BY COLUMN_POSITION ) 
       FROM PSRECDEFN RD, ALL_IND_COLUMNS AIC 
       WHERE RD.RECNAME = :1 
         AND AIC.TABLE_NAME = CASE WHEN RD.SQLTABLENAME = ' ' THEN 'PS_' || RD.RECNAME ELSE RD.SQLTABLENAME END 
         AND AIC.INDEX_NAME = 'PS_' || RD.RECNAME
*****************************************************************************************************************************************
MW_TRIGGER_OK
SELECT 'Y' 
       FROM PS_UO_PLSQL S, ALL_OBJECTS O, ALL_TRIGGERS T 
       WHERE S.ITEMNAME = :1 
         AND S.ITEM_TYPE = 'T' 
         AND S.LASTINSTALLED > S.LASTUPDDTTM 
         AND O.LAST_DDL_TIME = S.LASTINSTALLED 
         AND O.OBJECT_TYPE = 'TRIGGER' 
         AND O.OBJECT_NAME = S.ITEMNAME 
         AND O.LAST_DDL_TIME = S.LASTINSTALLED 
         AND O.STATUS = 'VALID' 
         AND T.TRIGGER_NAME = O.OBJECT_NAME 
         AND T.STATUS = 'ENABLED'