UNFILLED_RPT_VW

(SQL View)
 Index Back

SELECT A.BUSINESS_UNIT , A.SERVICE_OPRID ,A.SO_ID ,A.OWNERSHIP_ID , C.SO_STATUS , A.MANAGER_CONTACT_ID , A.START_DATE , A.END_EST_DATE , B.QTY_INTEGER , B.JOBCODE , B.WEEKLY_HOURS_STD , B.REG_BILL_RATE , A.CUST_ID , A.USER_ORDER_TYPE , A.DEPTID ,B.SALARY_RANGE_MAX ,B.INITIAL_COMP ,COUNT(*) FROM PS_RS_SO_HDR A , PS_RS_SO_LINE B , PS_RS_ASSIGNMENT E , PS_RS_SO_STATUS C WHERE B.SO_ID = E.SO_ID (+) AND B.BUSINESS_UNIT = E.BUSINESS_UNIT (+) AND B.SO_LINE = E.SO_LINE (+) AND A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.SO_ID = B.SO_ID AND A.BUSINESS_UNIT = C.BUSINESS_UNIT AND A.SO_ID = C.SO_ID AND A.SYSTEM_SOURCE = 'STF' AND C.SO_STATUS IN ('D', 'U') AND C.LASTUPDDTTM = ( SELECT MAX(D.LASTUPDDTTM) FROM PS_RS_SO_STATUS D WHERE D.SO_ID = C.SO_ID AND D.BUSINESS_UNIT = C.BUSINESS_UNIT) GROUP BY A.BUSINESS_UNIT, A.SO_ID, A.SERVICE_OPRID, A.OWNERSHIP_ID,C.SO_STATUS, A.MANAGER_CONTACT_ID, A.START_DATE, A.END_EST_DATE, B.QTY_INTEGER, B.JOBCODE, B.WEEKLY_HOURS_STD, B.REG_BILL_RATE, A.CUST_ID, A.USER_ORDER_TYPE, A.DEPTID,B.SALARY_RANGE_MAX,B.INITIAL_COMP

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
2 SERVICE_OPRID Character(30) VARCHAR2(30) NOT NULL Service Operator
3 ORDER_ID Character(25) VARCHAR2(25) NOT NULL Order ID
4 OWNERSHIP_ID Character(30) VARCHAR2(30) NOT NULL Owning Organization
5 ORDER_STATUS Character(3) VARCHAR2(3) NOT NULL Order Status
C=Closed
O=Open
P=Pending
X=Canceled
6 MANAGER_CONTACT_ID Character(15) VARCHAR2(15) NOT NULL Order Contact
7 START_DATE Date(10) DATE Start Date for Gen Standing PO
8 END_EST_DATE Date(10) DATE Estimated End Date
9 QTY_INTEGER Number(11,0) DECIMAL(11) NOT NULL Quantity Rounded to 0 Decimals
10 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code
11 WEEKLY_HOURS Number(6,2) DECIMAL(5,2) NOT NULL Schedule Hours
12 REG_BILL_RATE Number(19,6) DECIMAL(18,6) NOT NULL Bill Rate
13 CUST_ID Character(15) VARCHAR2(15) NOT NULL Uniquely identifies an organizational entity that purchases goods or services from the enterprise. Represents the sold-to customer which is the customer organization that places orders. The CUST_ID may or may not be the same as the identifiers for the bill-to and ship-to locations.
14 USER_ORDER_TYPE Character(5) VARCHAR2(5) NOT NULL User Order Type
15 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
16 SALARY_RANGE_MAX Number(11,2) DECIMAL(10,2) NOT NULL Maximum Salary Range
17 INITIAL_COMP Number(11,2) DECIMAL(10,2) NOT NULL Compensation
18 QTY_ACTIVE_CNT Number(4,0) SMALLINT NOT NULL Active Count