PO_BUY_PO2_VW(SQL View) |
Index Back |
|---|---|
Purchase Order View - AE01/19/01 SCC CN#WV802-1.0: Supplier Collaboration - New View Used within the AE schedule build job. 3/19/01 cel Had problems with performance on DB2/unix with this view. Originally it just did a join between po_buy_po_vw and itm_vnd_bu_attr ... but it was very slow ... so insted duplicated the where clause used within po_buy_po_vw and it worked much better. (Actually only saw this with the planning view ... but that had a lot more volume/rows .. so to be safe did the same thing with this view. ) |
| SELECT A.BUSINESS_UNIT , A.PO_ID , B.LINE_NBR , C.SCHED_NBR , D.DISTRIB_LINE_NUM , A.VENDOR_SETID , A.VENDOR_ID , A.VNDR_LOC , A.BUYER_ID , A.PO_STATUS , B.ITM_SETID , B.INV_ITEM_ID , B.CONFIG_CODE , B.UNIT_OF_MEASURE , C.DUE_DT , C.DUE_TIME , C.REVISION , C.FROZEN_FLG , D.BUSINESS_UNIT_IN , D.QTY_PO , E.UNIT_MEASURE_STD , G.CATEGORY_CD , G.SRC_METHOD , G.PRIMARY_BUYER , J.PO_SCHED_PRIORITY FROM PS_PO_HDR A , PS_PO_LINE B , PS_PO_LINE_SHIP C , PS_PO_LINE_DISTRIB D , PS_MASTER_ITEM_TBL E , PS_ITM_CAT_SET_VW G , PS_INSTALLATION_PO H , PS_ITM_VND_BU_ATTR J WHERE A.BUSINESS_UNIT=B.BUSINESS_UNIT AND A.PO_ID=B.PO_ID AND B.BUSINESS_UNIT=C.BUSINESS_UNIT AND B.PO_ID=C.PO_ID AND B.LINE_NBR=C.LINE_NBR AND C.BUSINESS_UNIT=D.BUSINESS_UNIT AND C.PO_ID=D.PO_ID AND C.LINE_NBR=D.LINE_NBR AND C.SCHED_NBR=D.SCHED_NBR AND E.SETID=B.ITM_SETID AND E.INV_ITEM_ID=B.INV_ITEM_ID AND G.SETID=B.ITM_SETID AND G.CATEGORY_TYPE=H.CATEGORY_TYPE AND G.CATEGORY_ID=E.CATEGORY_ID AND A.PO_TYPE='GEN' AND A.PO_STATUS <> 'C' AND A.PO_STATUS <> 'X' AND A.PO_STATUS <> 'PX' AND A.HOLD_STATUS='N' AND B.CANCEL_STATUS='A' AND B.INV_ITEM_ID <> ' ' AND C.CANCEL_STATUS='A' AND D.DST_ACCT_TYPE='DST' AND D.SYSTEM_SOURCE<>'SFC' AND D.BUSINESS_UNIT_IN<>' ' AND J.SETID=B.ITM_SETID AND J.INV_ITEM_ID=B.INV_ITEM_ID AND J.VENDOR_SETID=A.VENDOR_SETID AND J.VENDOR_ID=A.VENDOR_ID AND J.VNDR_LOC = A.VNDR_LOC AND J.BUSINESS_UNIT_IN=D.BUSINESS_UNIT_IN AND J.SCHED_FLAG = 'Y' |
| # | 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 | VENDOR_SETID | Character(5) | VARCHAR2(5) NOT NULL | Vendor SetID |
| 7 | VENDOR_ID | Character(10) | VARCHAR2(10) NOT NULL | Vendor Identifier |
| 8 | VNDR_LOC | Character(10) | VARCHAR2(10) NOT NULL | Vendor Location |
| 9 | BUYER_ID | Character(30) | VARCHAR2(30) NOT NULL | Buyer |
| 10 | 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 |
| 11 | ITM_SETID | Character(5) | VARCHAR2(5) NOT NULL | Item SetID |
| 12 | INV_ITEM_ID | Character(18) | VARCHAR2(18) NOT NULL | Item ID |
| 13 | CONFIG_CODE | Character(50) | VARCHAR2(50) NOT NULL | Product Configurator |
| 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 | DUE_DT | Date(10) | DATE | Specifies the date that a transaction is due. It is a generic field used in multiple PeopleSoft applications including Receivables Payables and Purchasing. For example it represents the date that payment is due for a voucher or the date that a scheduled shipment is due to be received. |
| 16 | DUE_TIME | Time(15) | TIMESTAMP | Due Time |
| 17 | REVISION | Character(4) | VARCHAR2(4) NOT NULL | 01/27/2000 RML Added "As of" label |
| 18 | FROZEN_FLG | Character(1) | VARCHAR2(1) NOT NULL |
Frozen Flag
B=Both N=No Y=Yes |
| 19 | BUSINESS_UNIT_IN | Character(5) | VARCHAR2(5) NOT NULL | Inventory Unit |
| 20 | QTY_PO | Number(16,4) | DECIMAL(15,4) NOT NULL | Purchase Order Quantity |
| 21 | UNIT_MEASURE_STD | Character(3) | VARCHAR2(3) NOT NULL | Standard Unit of Measure |
| 22 | CATEGORY_CD | Character(18) | VARCHAR2(18) NOT NULL | Category Code |
| 23 | SRC_METHOD | Character(1) | VARCHAR2(1) NOT NULL |
Sourcing Method
B=Basic F=Flexible H=Cumulative Split S=Sched Split |
| 24 | PRIMARY_BUYER | Character(30) | VARCHAR2(30) NOT NULL | Primary Buyer |
| 25 | PO_SCHED_PRIORITY | Character(2) | VARCHAR2(2) NOT NULL |
Schedule Priority
10=High 20=Medium 30=Low |