HCSC_RCPNT_L_VW

(SQL View)
 Index Back

Recipient Look up view

Recipient Look up view. If no role is attached to a recipient type all users can lookup the recipient type. If a role is attached to a recipient type only users with that role will have access to look up the recipient type.

SELECT DISTINCT C.ROLEUSER ,A.HCSC_RECIPIENT_ID ,D.LANGUAGE_CD ,D.HCSC_RECIPIENT_NM FROM PS_HCSC_RECIPIENT A ,PS_HCSC_RCPNT_ROLE B ,PSROLEUSER C ,PS_HCSC_RCPNT_LANG D WHERE (A.HCSC_RECIPIENT_ID = B.HCSC_RECIPIENT_ID AND A.STATUS = 'A' AND B.ROLENAME = C.ROLENAME AND A.HCSC_RECIPIENT_ID = D.HCSC_RECIPIENT_ID) UNION SELECT DISTINCT C2.ROLEUSER ,A2.HCSC_RECIPIENT_ID ,D2.LANGUAGE_CD ,D2.HCSC_RECIPIENT_NM FROM PS_HCSC_RECIPIENT A2 ,PS_HCSC_RCPNT_ROLE B2 ,PSROLEUSER C2 ,PS_HCSC_RCPNT_LANG D2 WHERE (A2.HCSC_RECIPIENT_ID = B2.HCSC_RECIPIENT_ID AND A2.STATUS = 'A' AND B2.ROLENAME = ' ' AND A2.HCSC_RECIPIENT_ID = D2.HCSC_RECIPIENT_ID) UNION SELECT DISTINCT C1.ROLEUSER ,A1.HCSC_RECIPIENT_ID ,D1.LANGUAGE_CD ,D1.HCSC_RECIPIENT_NM FROM PS_HCSC_RECIPIENT A1 ,PSROLEUSER C1 ,PS_HCSC_RCPNT_LANG D1 WHERE A1.STATUS = 'A' AND A1.HCSC_RECIPIENT_ID = D1.HCSC_RECIPIENT_ID AND NOT EXISTS( SELECT 'X' FROM PS_HCSC_RCPNT_ROLE B1 WHERE A1.HCSC_RECIPIENT_ID = B1.HCSC_RECIPIENT_ID)

  • Related Language Record for HCSC_RCPNT_VW

    • # 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 HCSC_RECIPIENT_ID Character(8) VARCHAR2(8) NOT NULL Recipient ID for Adhoc Recipient types
    3 LANGUAGE_CD Character(3) VARCHAR2(3) NOT NULL Language Code
    4 HCSC_RECIPIENT_NM Character(30) VARCHAR2(30) NOT NULL Description for Recipient ID of adhoc notification