WM_PC_PRJ_VW2LG(SQL View) |
Index Back |
---|---|
View by WO type and ServiceThe 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 A.BUSINESS_UNIT , CASE WHEN A.WO_TYPE = '%' THEN ' ' ELSE A.WO_TYPE END , C.SRVC_GRP_ID , A.EFFDT , A.PROJECT_ID , LB.LANGUAGE_CD , LB.DESCR FROM PS_WM_PC_DFLT_DTL A , PS_PROJECT B , PS_WM_SRVC_GRP C , PS_PROJECT_LANG LB WHERE A.BUSINESS_UNIT_PC = B.BUSINESS_UNIT AND A.PROJECT_ID = B.PROJECT_ID AND B.EFF_STATUS = 'A' AND (A.SRVC_GRP_ID = C.SRVC_GRP_ID OR A.SRVC_GRP_ID = '%') AND EXISTS ( SELECT SETCNTRLVALUE FROM PS_SET_CNTRL_REC S WHERE C.SETID = S.SETID AND S.RECNAME='WM_SRVC_GRP' AND S.SETCNTRLVALUE = A.BUSINESS_UNIT ) AND B.BUSINESS_UNIT = LB.BUSINESS_UNIT AND B.PROJECT_ID = LB.PROJECT_ID |
# | 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 | EFFDT | Date(10) | DATE |
Effective Date
Default Value: %date |
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 |