IN_POR_NPS_VW(SQL View) |
Index Back |
---|---|
IN Peg PO Search View06/22/04 EGS: Created |
SELECT H.BUSINESS_UNIT , H.PO_ID , D.LINE_NBR , D.SCHED_NBR , D.DISTRIB_LINE_NUM , '010' , %TrimSubstr(H.BUSINESS_UNIT %CONCAT X.PEG_KEY_SEPARATOR %CONCAT H.PO_ID, 1, 25) , %NUMTOCHAR(D.LINE_NBR) %Concat X.PEG_KEY_SEPARATOR %Concat %NUMTOCHAR(D.SCHED_NBR) %Concat X.PEG_KEY_SEPARATOR %Concat %NUMTOCHAR(D.DISTRIB_LINE_NUM) , D.BUSINESS_UNIT_IN , L.INV_ITEM_ID , D.QTY_PO_STD_EXP - SUM(QTY_DS_ACCPT_SUOM) , D.QTY_PO_STD_EXP , 'N' , S.DUE_DT , S.DUE_TIME , %Dttm(S.DUE_DT, S.DUE_TIME) , 'N' , D.PEG_STATUS , H.VENDOR_ID , H.VNDR_LOC , H.BUYER_ID FROM PS_PO_LINE_DISTRIB D , PS_PO_LINE_SHIP S , PS_PO_LINE L , PS_PO_HDR H , PS_RECV_LN_DISTRIB R , PS_BU_ITEMS_INV B , PS_BUS_UNIT_TBL_IN BUTI , PS_INSTALLATION_IN X WHERE D.PEG_STATUS IN ('30', '40') AND H.PO_STATUS IN ('O','PA','A','D') AND H.PO_TYPE <> 'KAN' AND L.BUSINESS_UNIT = H.BUSINESS_UNIT AND L.PO_ID = H.PO_ID AND L.CANCEL_STATUS = 'A' AND L.CONFIG_CODE = ' ' AND S.BUSINESS_UNIT = L.BUSINESS_UNIT AND S.PO_ID = L.PO_ID AND S.LINE_NBR = L.LINE_NBR AND S.CANCEL_STATUS = 'A' AND S.PRODUCTION_ID = ' ' AND D.BUSINESS_UNIT_IN <> ' ' AND D.BUSINESS_UNIT = S.BUSINESS_UNIT AND D.PO_ID = S.PO_ID AND D.LINE_NBR = S.LINE_NBR AND D.SCHED_NBR = S.SCHED_NBR AND D.DST_ACCT_TYPE = 'DST' AND D.DISTRIB_LN_STATUS = 'O' AND B.BUSINESS_UNIT = D.BUSINESS_UNIT_IN AND B.INV_ITEM_ID = L.INV_ITEM_ID AND D.BUSINESS_UNIT_IN = BUTI.BUSINESS_UNIT AND BUTI.VMI_UNIT_FLG = 'N' AND R.BUSINESS_UNIT_PO = D.BUSINESS_UNIT AND R.PO_ID = D.PO_ID AND R.LINE_NBR = D.LINE_NBR AND R.SCHED_NBR = D.SCHED_NBR AND R.PO_DIST_LINE_NUM = D.DISTRIB_LINE_NUM AND R.RECV_DS_STATUS <> 'X' AND D.QTY_PO_STD_EXP > ( SELECT SUM(RLD.QTY_DS_ACCPT_SUOM) FROM PS_RECV_LN_DISTRIB RLD WHERE RLD.BUSINESS_UNIT_PO = D.BUSINESS_UNIT AND RLD.PO_ID = D.PO_ID AND RLD.LINE_NBR = D.LINE_NBR AND RLD.SCHED_NBR = D.SCHED_NBR AND RLD.PO_DIST_LINE_NUM = D.DISTRIB_LINE_NUM AND RLD.RECV_DS_STATUS <> 'X') GROUP BY H.BUSINESS_UNIT, H.PO_ID, D.LINE_NBR, D.SCHED_NBR, D.DISTRIB_LINE_NUM, D.BUSINESS_UNIT_IN, L.INV_ITEM_ID, D.QTY_PO_STD_EXP, S.DUE_DT, S.DUE_TIME, D.PEG_STATUS, X.PEG_KEY_SEPARATOR, H.VENDOR_ID, H.VNDR_LOC, H.BUYER_ID |
# | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
---|---|---|---|---|
1 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
2 | PO_ID | Character(10) | VARCHAR2(10) NOT NULL | Purchase Order |
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 | PEG_ORDER_KEY | Character(30) | VARCHAR2(30) NOT NULL | Used to store the various Order Number formats in 1 field |
8 | PEG_LINE_KEY | Character(30) | VARCHAR2(30) NOT NULL | Used to Store the concatenation of Line, Demand, Sched, Kit, etc in 1 field. |
9 | BUSINESS_UNIT_IN | Character(5) | VARCHAR2(5) NOT NULL | Inventory Unit |
10 | INV_ITEM_ID | Character(18) | VARCHAR2(18) NOT NULL | Item ID |
11 | QTY_AVAILABLE | Signed Number(17,4) | DECIMAL(15,4) NOT NULL | Qty Available |
12 | QTY_ORDER | Signed Number(17,4) | DECIMAL(15,4) NOT NULL | 06/21/04 EGS: Created |
13 | PEG_FLAG | Character(1) | VARCHAR2(1) NOT NULL | 06/17/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 | HARD_PEG | Character(1) | VARCHAR2(1) NOT NULL | 09/01/04 EGS: Created |
18 | PEG_STATUS | Character(2) | VARCHAR2(2) NOT NULL |
07/21/04 EGS: Created
10=Open 20=Completed 30=Canceled 40=Not Pegged |
19 | VENDOR_ID | Character(10) | VARCHAR2(10) NOT NULL | Vendor Identifier |
20 | VNDR_LOC | Character(10) | VARCHAR2(10) NOT NULL | Vendor Location |
21 | BUYER_ID | Character(30) | VARCHAR2(30) NOT NULL | Buyer |