IN_REQS_PS_VW(SQL View) |
Index Back |
---|---|
IN Peg PO Search View06/22/04 EGS: Created |
SELECT D.BUSINESS_UNIT , D.REQ_ID , D.LINE_NBR , D.SCHED_NBR , D.DISTRIB_LINE_NUM , '100' , D.BUSINESS_UNIT_IN , L.INV_ITEM_ID , D.QTY_OPEN_STD + SUM(T.QTY_PO_STD) , SUM(QTY_PEGGED) , SUM(IP.QTY_COMPLETE) , SUM(IP.QTY_RECEIVED) , D.QTY_OPEN_STD + SUM(T.QTY_PO_STD) , %DatePart(IP.SUP_DTTM) , %TimePart(IP.SUP_DTTM) , IP.SUP_DTTM , H.REQ_STATUS , H.REQUESTOR_ID , L.VENDOR_ID , L.VNDR_LOC , A.HASH_KEY , D.PEG_STATUS , D.DISTRIB_LN_STATUS FROM PS_REQ_LN_DISTRIB D , PS_REQ_LINE L , PS_REQ_HDR H , PS_REQ_HASH A , PS_IN_PEGGING IP , PS_PO_ITM_STG P , PS_PO_DISTRIB_STG T WHERE D.PEG_STATUS < '40' AND L.BUSINESS_UNIT = D.BUSINESS_UNIT AND L.REQ_ID = D.REQ_ID AND L.LINE_NBR = D.LINE_NBR AND H.BUSINESS_UNIT = D.BUSINESS_UNIT AND H.REQ_ID = D.REQ_ID AND A.BUSINESS_UNIT = D.BUSINESS_UNIT AND A.REQ_ID = D.REQ_ID AND A.LINE_NBR = D.LINE_NBR AND A.SCHED_NBR = D.SCHED_NBR AND A.DISTRIB_LINE_NUM = D.DISTRIB_LINE_NUM AND IP.SUPPLY_HASH = A.HASH_KEY AND IP.SUP_ORDER_TYPE = '100' AND T.STAGE_STATUS IN ('S','E','R','B','N','P','I','V') AND T.BUSINESS_UNIT = D.BUSINESS_UNIT AND T.REQ_ID = D.REQ_ID AND T.REQ_LINE_NBR = D.LINE_NBR AND T.REQ_SCHED_NBR = D.SCHED_NBR AND T.REQ_DISTRIB_NBR = D.DISTRIB_LINE_NUM AND P.PO_PROCESS_ID = 'OLS' AND P.PROCESS_INSTANCE = T.PROCESS_INSTANCE AND P.BUSINESS_UNIT = T.BUSINESS_UNIT AND P.PO_STG_TYPE = T.PO_STG_TYPE AND P.PO_STG_ID = T.PO_STG_ID AND P.LINE_NBR = T.LINE_NBR AND P.SCHED_NBR = T.SCHED_NBR GROUP BY D.BUSINESS_UNIT, D.REQ_ID, D.LINE_NBR, D.SCHED_NBR, D.DISTRIB_LINE_NUM, D.BUSINESS_UNIT_IN, L.INV_ITEM_ID, D.QTY_OPEN_STD, IP.SUP_DTTM, H.REQ_STATUS, H.REQUESTOR_ID, L.VENDOR_ID, L.VNDR_LOC, A.HASH_KEY, D.PEG_STATUS, D.DISTRIB_LN_STATUS |
# | 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 | LINE_NBR | Number(5,0) | INTEGER NOT NULL | Line Number: 11/24/08 - Added TARGET label [PC product] |
4 | SCHED_NBR | Number(3,0) | SMALLINT NOT NULL | Schedule Number |
5 | DISTRIB_LINE_NUM | Number(5,0) | INTEGER NOT NULL | Distribution Line |
6 | PEG_ORDER_TYPE | Character(4) | VARCHAR2(4) NOT NULL |
Used by Pegging Maintenance/Inquiry
010=Purchase 020=Production 030=Transfer Demand 035=Transfer Supply 070=Stock Request 090=Sales Order 100=Requisition 110=WO Inventory 115=WO Non-Inventory |
7 | BUSINESS_UNIT_IN | Character(5) | VARCHAR2(5) NOT NULL | Inventory Unit |
8 | INV_ITEM_ID | Character(18) | VARCHAR2(18) NOT NULL | Item ID |
9 | QTY_AVAILABLE | Signed Number(17,4) | DECIMAL(15,4) NOT NULL | Qty Available |
10 | QTY_PEGGED | Signed Number(17,4) | DECIMAL(15,4) NOT NULL | 06/16/04 EGS: Created. |
11 | QTY_COMPLETE | Number(16,4) | DECIMAL(15,4) NOT NULL | Quantity Complete |
12 | QTY_RECEIVED | Number(16,4) | DECIMAL(15,4) NOT NULL | Received Qty |
13 | QTY_ORDER | Signed Number(17,4) | DECIMAL(15,4) NOT NULL | 06/21/04 EGS: Created |
14 | SCHED_DATE | Date(10) | DATE | Schedule Date |
15 | SCHED_TIME | Time(15) | TIMESTAMP | Schedule Time |
16 | SCHED_DTTM | DateTime(26) | TIMESTAMP | 08/16/01 JNW (CN#PL900-1.0): Added |
17 | REQ_STATUS | Character(4) | VARCHAR2(4) NOT NULL |
Req status
A=Approved C=Complete D=Denied I=Initial LA=Line Approved O=Open P=Pending Approval V=Preview X=Canceled |
18 | REQUESTOR_ID | Character(30) | VARCHAR2(30) NOT NULL | Requestor id |
19 | VENDOR_ID | Character(10) | VARCHAR2(10) NOT NULL | Vendor Identifier |
20 | VNDR_LOC | Character(10) | VARCHAR2(10) NOT NULL | Vendor Location |
21 | PEG_HASH | Character(28) | VARCHAR2(28) NOT NULL | 07/29/04 EGS: Created |
22 | PEG_STATUS | Character(2) | VARCHAR2(2) NOT NULL |
07/21/04 EGS: Created
10=Open 20=Completed 30=Canceled 40=Not Pegged |
23 | DISTRIB_LN_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
Distribution Line Status
C=Closed M=Mass Liquidated O=Open P=Unavailable for Sourcing X=Canceled |