IN_REQ_NCPS_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(QTY_PEGGED) , SUM(IP.QTY_COMPLETE) , SUM(IP.QTY_RECEIVED) , D.QTY_REQ_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 , C.CATEGORY_CD , L.DESCR254_MIXED FROM PS_REQ_LN_DISTRIB D , PS_REQ_LINE_SHIP S , PS_REQ_LINE L , PS_REQ_HDR H , PS_REQ_HASH A , PS_IN_PEGGING IP , PS_ITM_CAT_TBL C , PS_SET_CNTRL_REC SCR WHERE D.PEG_STATUS < '40' AND D.DISTRIB_LN_STATUS = 'O' AND S.BUSINESS_UNIT = D.BUSINESS_UNIT AND S.REQ_ID = D.REQ_ID AND S.LINE_NBR = D.LINE_NBR AND S.SCHED_NBR = D.SCHED_NBR AND S.SHIP_TO_CUST_ID = ' ' 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 IP.PEG_STATUS < '30' AND L.CATEGORY_ID = C.CATEGORY_ID AND C.SETID = SCR.SETID AND SCR.SETCNTRLVALUE = D.BUSINESS_UNIT AND SCR.RECNAME = 'ITM_CAT_TBL' AND C.EFFDT = ( SELECT MAX(C1.EFFDT) FROM PS_ITM_CAT_TBL C1 WHERE C.SETID = C1.SETID AND C.CATEGORY_TYPE = C1.CATEGORY_TYPE AND C.CATEGORY_CD = C1.CATEGORY_CD AND C.CATEGORY_ID = C1.CATEGORY_ID AND C1.EFFDT <= %CurrentDateIn ) AND NOT EXISTS( SELECT 'x' FROM PS_PO_DISTRIB_STG T WHERE T.STAGE_STATUS IN ('S','E','R','B','N','P','I','V') AND T.BUSINESS_UNIT_IN = D.BUSINESS_UNIT_IN 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) 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, D.QTY_REQ_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, C.CATEGORY_CD, L.DESCR254_MIXED |
| # | 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 |
| 24 | CATEGORY_CD | Character(18) | VARCHAR2(18) NOT NULL | Category Code |
| 25 | DESCR254_MIXED | Character(254) | VARCHAR2(254) NOT NULL | Description |