AV_E_RATNG_SRCH

(SQL View)
 Index Back

Rating Org Search View

Action Org Search View ------------------------------------------------------------------------------------------------------------ Changed format to be like AV_INST_E_SRCH , changed Name field for Descr, Name/Descr no longer key - 12 Dec 2000

SELECT C.OPRID , C.INSTITUTION , A.EXT_ORG_ID , D.RATING_NO , D.RATING_CAT , D.AV_RATING_TYPE , D.IND_CD , D.AV_VALID_VALUE , H.DESCR , D.INTV_CD , A.OTH_NAME_SORT_SRCH , A.DESCR , A.EXT_ORG_TYPE FROM PS_EXT_ORG_TBL A , PS_SCRTY_TBL_INST C , PS_AV_PRSN_RATING D LEFT OUTER JOIN PS_AV_R_IND_V_VAL H ON D.IND_CD = H.IND_CD AND D.AV_VALID_VALUE <> ' ' WHERE A.EXT_ORG_ID=D.EXT_ORG_ID AND %EffdtCheck(EXT_ORG_TBL A1, A, %CurrentDateIn) AND C.ACCESS_CD ='Y' AND C.INSTITUTION = D.INSTITUTION AND D.EFFDT = ( SELECT MAX(D1.EFFDT) FROM PS_AV_PRSN_RATING D1 WHERE D1.INSTITUTION = D.INSTITUTION AND D1.EMPLID = D.EMPLID AND D1.EXT_ORG_ID = D.EXT_ORG_ID AND D1.RATING_NO =D.RATING_NO AND D1.EFFDT <= %CurrentDateIn) AND D.EFF_STATUS = 'A' AND ((D.AV_VALID_VALUE = ' ' ) OR (H.IND_CD = D.IND_CD AND H.EFFDT <= D.EFFDT AND H.AV_VALID_VALUE = D.AV_VALID_VALUE ))

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
2 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Institution

Default Value: AV_OPR_DEF_TBL.INSTITUTION

Prompt Table: INSTITUTN_SCRTY

3 EXT_ORG_ID Character(11) VARCHAR2(11) NOT NULL External Org ID

Prompt Table: AV_EXT_ORG_SRCH

4 RATING_NO Number(10,0) DECIMAL(10) NOT NULL Rating Nbr

Default Value: 0

Prompt Table: AV_RTNG_NO_E_VW

5 RATING_CAT Character(6) VARCHAR2(6) NOT NULL Rating Category
6 AV_RATING_TYPE Character(6) VARCHAR2(6) NOT NULL Rating Type
7 IND_CD Character(6) VARCHAR2(6) NOT NULL Indicator Code
8 AV_VALID_VALUE Character(15) VARCHAR2(15) NOT NULL Valid Value
9 AV_VALID_VAL_DESCR Character(30) VARCHAR2(30) NOT NULL Ratings search page indicator valid value description
10 INTV_CD Character(10) VARCHAR2(10) NOT NULL Initiative Code
11 OTH_NAME_SORT_SRCH Character(50) VARCHAR2(50) NOT NULL Search Name
12 DESCR Character(30) VARCHAR2(30) NOT NULL Description
13 EXT_ORG_TYPE Character(4) VARCHAR2(4) NOT NULL Organization Type

Prompt Table: SCC_ORG_TYP_TBL