WM_PC_PRJ_VW3LG

(SQL View)
 Index Back

View by WO type and Service

The purpose of this view is to provide a distinct list of Projects based on a work order type and service group combination. The default project table is based on wildcarding. This record joins to the WO type table to get a full view.

SELECT DISTINCT P.BUSINESS_UNIT , P.WO_TYPE , P.SRVC_GRP_ID , P.BUSINESS_UNIT_PC , P.PROJECT_ID , G.LANGUAGE_CD , G.DESCR FROM PS_WM_PC_PRJ_VW P , PS_PROJECT_LANG G WHERE P.BUSINESS_UNIT_PC = G.BUSINESS_UNIT AND P.PROJECT_ID = G.PROJECT_ID AND EXISTS ( SELECT 'X' FROM PS_WM_PC_DFLT_DTL A , PS_PROJECT F , PS_WM_WO_TYPE B , PS_WM_SRVC_GRP E WHERE P.BUSINESS_UNIT = A.BUSINESS_UNIT AND (P.WO_TYPE = A.WO_TYPE OR A.WO_TYPE = '%') AND (P.SRVC_GRP_ID = A.SRVC_GRP_ID OR A.SRVC_GRP_ID = '%') AND P.PROJECT_ID = A.PROJECT_ID AND (B.WO_TYPE = A.WO_TYPE OR A.WO_TYPE = '%') AND A.BUSINESS_UNIT_PC = F.BUSINESS_UNIT AND A.PROJECT_ID = F.PROJECT_ID AND F.EFF_STATUS = 'A' AND (E.SRVC_GRP_ID = A.SRVC_GRP_ID OR A.SRVC_GRP_ID = '%') AND EXISTS ( SELECT SETCNTRLVALUE FROM PS_SET_CNTRL_REC D WHERE B.SETID = D.SETID AND D.RECNAME='WM_WO_TYPE' AND D.SETCNTRLVALUE = A.BUSINESS_UNIT ) AND EXISTS ( SELECT SETCNTRLVALUE FROM PS_SET_CNTRL_REC E WHERE B.SETID = E.SETID AND E.RECNAME = 'WM_SRVC_GRP' AND E.SETCNTRLVALUE = A.BUSINESS_UNIT) AND A.EFFDT = ( SELECT MAX(EFFDT) FROM PS_WM_PC_DFLT_DTL X WHERE X.BUSINESS_UNIT = A.BUSINESS_UNIT AND X.WO_TYPE = A.WO_TYPE AND X.SRVC_GRP_ID = A.SRVC_GRP_ID AND X.EFFDT <= %CurrentDateIn))

  • Related Language Record for WM_PC_PRJ_VW3

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

    Prompt Table: SP_BU_WM_NONVW

    2 WO_TYPE Character(5) VARCHAR2(5) NOT NULL Work Order Type
    3 SRVC_GRP_ID Character(10) VARCHAR2(10) NOT NULL Service Group
    4 BUSINESS_UNIT_PC Character(5) VARCHAR2(5) NOT NULL PC Business Unit
    5 PROJECT_ID Character(15) VARCHAR2(15) NOT NULL Project Id ChartField
    6 LANGUAGE_CD Character(3) VARCHAR2(3) NOT NULL Language Code
    7 DESCR Character(30) VARCHAR2(30) NOT NULL Description