VENDOR_SETIDVENDOR_IDBUSINESS_UNITPO_IDLINE_NBRA=Approved
C=Complete
D=Dispatched
DA=Denied
I=Initial
LD=Line Denied
O=Open
PA=Pending Approval/Approved
PX=Pending Cancel
X=Canceled
MHR=Muti Hourly
PER=Percentage
SQF=Square Footage
PO_W_VRBT_VW(SQL View) |
Index Back |
|---|---|
POs with Supplier RebatesPOs with Supplier Rebates |
| SELECT H.VENDOR_SETID , H.VENDOR_ID , H.VNDR_LOC , L.BUSINESS_UNIT , L.PO_ID , L.LINE_NBR , H.PO_STATUS , H.PO_DT , L.ITM_SETID , L.INV_ITEM_ID , L.DESCR254_MIXED , C.CATEGORY_CD , H.CURRENCY_CD , L.UNIT_OF_MEASURE , L.CNTRCT_SETID , L.CNTRCT_ID , L.VRBT_ID , SUM(S.MERCHANDISE_AMT) , SUM(S.QTY_PO) FROM PS_PO_HDR H , PS_PO_LINE L , PS_PO_LINE_SHIP S , PS_ITM_CAT_TBL C WHERE H.BUSINESS_UNIT = L.BUSINESS_UNIT AND H.PO_ID = L.PO_ID AND L.BUSINESS_UNIT = S.BUSINESS_UNIT AND L.PO_ID = S.PO_ID AND L.LINE_NBR = S.LINE_NBR AND H.PO_STATUS <> 'X' AND PO_STATUS <> 'PX' AND L.VRBT_ID <> ' ' AND C.SETID = L.ITM_SETID AND C.CATEGORY_ID = L.CATEGORY_ID 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_VRBT_ACCR_HDR V WHERE L.CNTRCT_SETID = V.SETID AND L.BUSINESS_UNIT = V.BUSINESS_UNIT_PO AND V.CLAIM_ID <> ' ' AND L.PO_ID = V.PO_ID AND L.LINE_NBR = V.PO_LINE_NBR) GROUP BY H.VENDOR_SETID, H.VENDOR_ID, H.VNDR_LOC, L.BUSINESS_UNIT, L.PO_ID, L.LINE_NBR, H.PO_STATUS, H.PO_DT, L.ITM_SETID, L.INV_ITEM_ID, L.DESCR254_MIXED, C.CATEGORY_CD, H.CURRENCY_CD, L.UNIT_OF_MEASURE, L.CNTRCT_SETID, L.CNTRCT_ID, L.VRBT_ID |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | VENDOR_SETID |
Character(5) | VARCHAR2(5) NOT NULL | Vendor SetID |
| 2 | VENDOR_ID |
Character(10) | VARCHAR2(10) NOT NULL | Vendor Identifier |
| 3 | VNDR_LOC | Character(10) | VARCHAR2(10) NOT NULL | Vendor Location |
| 4 | BUSINESS_UNIT |
Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 5 | PO_ID |
Character(10) | VARCHAR2(10) NOT NULL | Purchase Order |
| 6 | LINE_NBR |
Number(5,0) | INTEGER NOT NULL | Line Number: 11/24/08 - Added TARGET label [PC product] |
| 7 | PO_STATUS | Character(2) | VARCHAR2(2) NOT NULL |
PO Status
A=Approved C=Complete D=Dispatched DA=Denied I=Initial LD=Line Denied O=Open PA=Pending Approval/Approved PX=Pending Cancel X=Canceled |
| 8 | PO_DT | Date(10) | DATE | Date |
| 9 | ITM_SETID | Character(5) | VARCHAR2(5) NOT NULL | Item SetID |
| 10 | INV_ITEM_ID | Character(18) | VARCHAR2(18) NOT NULL | Item ID |
| 11 | DESCR254_MIXED | Character(254) | VARCHAR2(254) NOT NULL | Description |
| 12 | CATEGORY_CD | Character(18) | VARCHAR2(18) NOT NULL | Category Code |
| 13 | CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Currency Code |
| 14 | UNIT_OF_MEASURE | Character(3) | VARCHAR2(3) NOT NULL |
Used on an approval rule set.
MHR=Muti Hourly PER=Percentage SQF=Square Footage |
| 15 | CNTRCT_SETID | Character(5) | VARCHAR2(5) NOT NULL | SetID |
| 16 | CNTRCT_ID | Character(25) | VARCHAR2(25) NOT NULL | Buying Agreement ID |
| 17 | VRBT_ID | Character(25) | VARCHAR2(25) NOT NULL | Vendor Rebate ID |
| 18 | MERCHANDISE_AMT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Merchandise Amt F_KDHIL_R87H8 062804 MLM: Added new Line Amount Received Label |
| 19 | QTY_PO | Number(16,4) | DECIMAL(15,4) NOT NULL | Purchase Order Quantity |