SPF_WO_RREQ1_VW

(SQL View)
 Index Back

WORKORDER VIEW TO REQ

View to populate the resource based requistions that are approved & sourced and created by the person who has logged in 02/01/2012:SMO: Modified the sql to import Multi-Position Requisition to a Work Order

SELECT DISTINCT(B.BUSINESS_UNIT) , A.REQ_ID , O.OPRID , A.SP_REQ_ID , B.SP_REQ_STATUS , A.LINE_NBR 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.SP_REQ_STATUS = 'A' OR B.SP_REQ_STATUS = 'S' OR B.SP_REQ_STATUS = 'F') AND B.REQUESTOR_ID = O.OPRID AND B.SERVICE_METHOD = 'R' AND B.SP_ORIGIN_TYPE = 'ORG' AND B.SP_NUM_OF_POSITION > ( SELECT COUNT(DISTINCT H.WORK_ORDER_ID) FROM PS_SPF_WORDERREC W , PS_SPF_WO_HISTORY H WHERE W.BUSINESS_UNIT = H.BUSINESS_UNIT AND W.WORK_ORDER_ID = H.WORK_ORDER_ID AND W.BUSINESS_UNIT = B.BUSINESS_UNIT AND W.SP_REQ_ID = B.SP_REQ_ID AND W.LINE_NBR = B.LINE_NBR AND (W.WO_STATUS <> 'N') AND W.SP_WO_TYPE NOT IN ('R', 'E'))

# 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 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
4 SP_REQ_ID Character(10) VARCHAR2(10) NOT NULL Service Request ID
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 LINE_NBR Number(5,0) INTEGER NOT NULL Line Number: 11/24/08 - Added TARGET label [PC product]