SPF_WO_RLINE_VW

(SQL View)
 Index Back

Requistion Line view

Resource based Requistion Line view 02/01/2012:SMO: Modified the sql to import Multi-Position Requisition to a Work Order

SELECT DISTINCT A.REQ_ID , B.BUSINESS_UNIT , A.LINE_NBR , A.SP_REQ_ID , B.SP_REQ_STATUS ,A.SP_LINE_NBR FROM PS_PV_REQ_LN_XREF A , PS_SPF_REQ_LINE B WHERE A.SP_BUSINESS_UNIT = B.BUSINESS_UNIT AND A.SP_REQ_ID=B.SP_REQ_ID AND A.SP_LINE_NBR = B.LINE_NBR AND (B.SP_REQ_STATUS = 'A' OR B.SP_REQ_STATUS = 'S' OR B.SP_REQ_STATUS = 'F') AND B.SERVICE_METHOD = 'R' 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 REQ_ID Character(10) VARCHAR2(10) NOT NULL Requisition ID
2 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
3 LINE_NBR Number(5,0) INTEGER NOT NULL Line Number: 11/24/08 - Added TARGET label [PC product]
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 SP_LINE_NBR Number(5,0) INTEGER NOT NULL Line Number field