OI_SEC_PRG_VW

(SQL View)
 Index Back

Program Code Security View

Program Code Security View

SELECT DISTINCT SU.OPRID ,SU.SETID , %Sql(PROGRAM_CODE,SU.) FROM PS_SEC_PROG_USER SU , PS_%Sql(PROGRAM_TBL,) CF1 , PS_INSTALLATION_FS I1 , PS_SEC_CF_PROD SP1 , PS_SEC_FIELDS_TBL SF1 , PS_FS_CF_TEMPLATE D WHERE SU.SETID = CF1.SETID AND %Sql(PROGRAM_CODE,SU.)=%Sql(PROGRAM_CODE,CF1.) AND SU.PRODSRCID = SP1.PRODSRCID AND I1.CF_SECURITY = '1' AND SP1.PRODSRCID='FGL' AND SP1.ACTIVE_PROD='A' AND SF1.FIELDNAME = D.FIELDNAME AND D.ORIG_CF_NAME='PROGRAM_CODE' AND SF1.ACTIVE_FLAG='A' UNION SELECT DISTINCT RU.ROLEUSER ,SR.SETID , %Sql(PROGRAM_CODE,SR.) FROM PS_SEC_PROG_ROLE SR , PS_%Sql(PROGRAM_TBL,) CF2 , PS_INSTALLATION_FS I2 , PS_SEC_CF_PROD SP2 , PS_SEC_FIELDS_TBL SF2 , PSROLEUSER RU , PS_FS_CF_TEMPLATE D WHERE SR.SETID = CF2.SETID AND %Sql(PROGRAM_CODE,SR.)=%Sql(PROGRAM_CODE,CF2.) AND SR.PRODSRCID = SP2.PRODSRCID AND I2.CF_SECURITY = '2' AND SP2.PRODSRCID='FGL' AND SP2.ACTIVE_PROD='A' AND SF2.FIELDNAME = D.FIELDNAME AND D.ORIG_CF_NAME='PROGRAM_CODE' AND SF2.ACTIVE_FLAG='A' AND RU.ROLENAME = SR.ROLENAME UNION SELECT DISTINCT OP.OPRID ,SC.SETID , %Sql(PROGRAM_CODE,SC.) FROM PS_SEC_PROG_PERM SC , PS_%Sql(PROGRAM_TBL,) CF3 , PS_INSTALLATION_FS I3 , PS_SEC_CF_PROD SP3 , PS_SEC_FIELDS_TBL SF3 , PSOPRDEFN OP , PS_FS_CF_TEMPLATE D WHERE SC.SETID = CF3.SETID AND %Sql(PROGRAM_CODE,SC.)=%Sql(PROGRAM_CODE,CF3.) AND SC.PRODSRCID = SP3.PRODSRCID AND I3.CF_SECURITY = '3' AND SP3.PRODSRCID='FGL' AND SP3.ACTIVE_PROD='A' AND SF3.FIELDNAME = D.FIELDNAME AND D.ORIG_CF_NAME='PROGRAM_CODE' AND SF3.ACTIVE_FLAG='A' AND OP.ROWSECCLASS = SC.ROWSECCLASS UNION SELECT DISTINCT 'ALLUSERS' AS OPRID , CF4.SETID , %Sql(PROGRAM_CODE,CF4.) FROM PS_%Sql(PROGRAM_TBL,) CF4 , PS_INSTALLATION_FS I4 , PS_SEC_CF_PROD SP4 , PS_SEC_FIELDS_TBL SF4 , PS_FS_CF_TEMPLATE D WHERE I4.CF_SECURITY = '0' OR (SP4.PRODSRCID = 'FGL' AND SP4.ACTIVE_PROD = 'I') OR (SP4.PRODSRCID = 'FGL' AND SP4.ACTIVE_PROD = 'A' AND SF4.FIELDNAME = D.FIELDNAME AND D.ORIG_CF_NAME='PROGRAM_CODE' AND SF4.ACTIVE_FLAG='I') UNION SELECT DISTINCT 'ALLUSERS' AS OPRID , CF5.SETID , ' ' FROM PS_%Sql(PROGRAM_TBL,) CF5 UNION SELECT 'ALLUSERS' AS OPRID , ' ' AS SETID , ' ' FROM PS_INSTALLATION_FS

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 USERID Character(30) VARCHAR2(30) NOT NULL A user's ID
2 SETID Character(5) VARCHAR2(5) NOT NULL SetID
3 PROGRAM_CODE Character(5) VARCHAR2(5) NOT NULL Program Code ChartField