SPF_WO_REQ_D_VW

(SQL View)
 Index Back

WORKORDER DYNAMIC VIEW TO REQ

Dynamic view to populate the requistions that are approved & sourced and created by the person who has logged in

SELECT B.BUSINESS_UNIT ,A.REQ_ID , A.SP_REQ_ID , B.LINE_NBR , B.SP_REQ_STATUS ,O.OPRID FROM PS_PV_REQ_LN_XREF A , PS_SPF_REQ_LINE B ,PSOPRALIAS O WHERE A.SP_REQ_ID = B.SP_REQ_ID AND A.SP_LINE_NBR = B.LINE_NBR AND A.SP_BUSINESS_UNIT = B.BUSINESS_UNIT AND B.REQUESTOR_ID = O.OPRID AND (B.SP_REQ_STATUS = 'A' OR B.SP_REQ_STATUS = 'S' OR (B.SP_REQ_STATUS = 'C' AND EXISTS ( SELECT 1 FROM PS_SPF_WORDERREC C , PS_SPF_WO_HISTORY H WHERE C.WO_STATUS = 'N' AND B.BUSINESS_UNIT = C.BUSINESS_UNIT AND B.BUSINESS_UNIT = H.BUSINESS_UNIT AND C.WORK_ORDER_ID = H.WORK_ORDER_ID AND B.SP_REQ_ID = C.SP_REQ_ID AND (C.WO_CLIID = O.OPRID OR H.INVAPPROVER= O.OPRID OR C.ROW_CREATE_USER= O.OPRID ))) AND A.SP_REQ_ID NOT IN ( SELECT D.SP_REQ_ID FROM PS_SPF_WORDERREC D WHERE D.SP_REQ_ID = A.SP_REQ_ID AND D.WO_STATUS NOT IN ('N')))

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
2 REQ_ID Character(10) VARCHAR2(10) NOT NULL Requisition ID
3 SP_REQ_ID Character(10) VARCHAR2(10) NOT NULL Service Request ID
4 LINE_NBR Number(5,0) INTEGER NOT NULL Line Number: 11/24/08 - Added TARGET label [PC product]
5 SP_REQ_STATUS Character(4) VARCHAR2(4) NOT NULL Requisition Status for Service Procurement
A=Approved
C=Complete
D=Denied
F=Filled
O=Open
P=Pending
R=On Hold
S=Sourced
X=Canceled
6 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).