WM_WO_EMAIL_VW

(SQL View)
 Index Back

WO Task Roles Email View


SELECT A0.BUSINESS_UNIT , A0.WO_ID , '0' , A0.CONTACT_USERID , B0.FIELDNAME , B0.SHORTNAME , C0.EMPLID , A0.CONTACT_USERID , C0.NAME , C0.NAME_DISPLAY , D0.EMAILID FROM PS_WM_WO_ASSCSR_VW A0 , PSDBFLDLABL B0 , PS_WM_OPER_NAME_VW C0 , PSUSEREMAIL D0 WHERE A0.CONTACT_USERID <> ' ' AND B0.FIELDNAME = 'CONTACT_USERID' AND B0.LABEL_ID = 'REQUESTER' AND C0.OPRID = A0.CONTACT_USERID AND D0.OPRID = C0.OPRID AND D0.PRIMARY_EMAIL = 'Y' UNION SELECT A.BUSINESS_UNIT , A.WO_ID , '1' , A.SUPERVISOR , B.FIELDNAME , B.SHORTNAME , A.SUPERVISOR , C.OPRID , C.NAME , C.NAME_DISPLAY , D.EMAILID FROM PS_WM_WO_HDR A , PSDBFLDLABL B , PS_WM_OPER_NAME_VW C , PSUSEREMAIL D WHERE A.SUPERVISOR <> ' ' AND B.FIELDNAME = 'SUPERVISOR' AND B.LABEL_ID = B.FIELDNAME AND C.EMPLID = A.SUPERVISOR AND D.OPRID = C.OPRID AND D.PRIMARY_EMAIL = 'Y' UNION SELECT A2.BUSINESS_UNIT , A2.WO_ID , '2' , A2.SCHEDULER_CODE , B2.FIELDNAME , B2.SHORTNAME , S.EMPLID , C2.OPRID , C2.NAME , C2.NAME_DISPLAY , D2.EMAILID FROM PS_WM_WO_HDR A2 , PSDBFLDLABL B2 , PS_WM_SCHEDULER_VW S , PS_WM_OPER_NAME_VW C2 , PSUSEREMAIL D2 WHERE A2.SCHEDULER_CODE <> ' ' AND B2.FIELDNAME = 'SCHEDULER_CODE' AND B2.LABEL_ID = 'SCHEDULER' AND S.BUSINESS_UNIT = A2.BUSINESS_UNIT AND S.SHOP_ID = A2.SHOP_ID AND C2.EMPLID = S.EMPLID AND D2.OPRID = C2.OPRID AND D2.PRIMARY_EMAIL = 'Y' UNION SELECT A3.BUSINESS_UNIT , A3.WO_ID , '3' , A3.LEAD_PERSON , B3.FIELDNAME , B3.SHORTNAME , C3.EMPLID , A3.LEAD_PERSON , C3.NAME , C3.NAME_DISPLAY , D3.EMAILID FROM PS_WM_WO_HDR A3 , PSDBFLDLABL B3 , PS_WM_OPER_NAME_VW C3 , PSUSEREMAIL D3 WHERE A3.LEAD_PERSON <> ' ' AND B3.FIELDNAME = 'LEAD_PERSON' AND B3.LABEL_ID = 'CONTACT_PERSON' AND C3.OPRID = A3.LEAD_PERSON AND D3.OPRID = C3.OPRID AND D3.PRIMARY_EMAIL = 'Y'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

Default Value: WM_WO_INSPC_ACT.BUSINESS_UNIT

Prompt Table: SP_BU_WM_NONVW

2 WO_ID Character(10) VARCHAR2(10) NOT NULL Work Order Identification

Default Value: WM_WO_INSPC_ACT.WO_ID

3 SORT_ORDER Character(1) VARCHAR2(1) NOT NULL Sort Order
A=Ascending
D=Descending
4 FIELD_VALUE Character(30) VARCHAR2(30) NOT NULL Field Value
5 FIELDNAME Character(18) VARCHAR2(18) NOT NULL Field Name (see PSDBFIELD).
6 ROLENAME Character(30) VARCHAR2(30) NOT NULL The name of a Role in the Role Definition Table (see PSROLEDEFN).
7 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
8 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
9 NAME Character(50) VARCHAR2(50) NOT NULL Name
10 NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Name Display
11 EMAILID Character(70) VARCHAR2(70) NOT NULL A user's E-mail address