REPORT_ENTITYPRCR_YEARPRCR_PERIODSETIDINV_ITEM_IDCATEGORY_IDCATEGORY_CDVENDOR_SETIDVENDOR_IDBUSINESS_UNIT_POSHIPTO_SETIDSHIPTO_IDCURRENCY_CDUNIT_MEASURE_STDWV_HST_AAA_VW2(SQL View) |
Index Back |
|---|---|
Procurement History Item ViewAdded for CN#WV802-2.0 5/2007 CEL F-AHAYT-L64E0 Changed description for category cd to descr60. |
| SELECT A.REPORT_ENTITY ,A.PRCR_YEAR ,A.PRCR_PERIOD ,A.ITM_SETID ,A.INV_ITEM_ID ,A.CATEGORY_ID ,C.CATEGORY_CD ,C.DESCR60 ,A.VENDOR_SETID ,A.VENDOR_ID ,A.BUSINESS_UNIT_PO ,A.SHIPTO_SETID ,A.SHIPTO_ID ,A.CURRENCY_CD ,A.UNIT_MEASURE_STD ,A.UNIT_MEASURE_WT ,A.QTY_PO ,A.QTY_CANCEL , %Round(%DecDiv(%DecMult(A.QTY_CANCEL, 100), (A.QTY_PO + .00001) ), 2) ,A.QTY_SH_RECVD_SUOM ,(A.QTY_SH_RECVD_SUOM - A.QTY_SH_REJCT_SUOM - A.QTY_RETURN) , %Round(%DecDiv(%DecMult((A.QTY_SH_RECVD_SUOM - A.QTY_SH_REJCT_SUOM - A.QTY_RETURN), 100), (A.QTY_SH_RECVD_SUOM + .00001) ), 2) ,A.QTY_SH_REJCT_SUOM , %Round(%DecDiv(%DecMult(A.QTY_SH_REJCT_SUOM, 100), (A.QTY_SH_RECVD_SUOM + .00001) ), 2) ,A.QTY_RETURN , %Round(%DecDiv(%DecMult(A.QTY_RETURN, 100), (A.QTY_SH_RECVD_SUOM + .00001) ), 2) ,A.QTY_VCHR ,A.QTY_DEFECTIVE , %Round(%DecDiv(%DecMult(A.QTY_DEFECTIVE, 100), (A.QTY_SH_RECVD_SUOM + .0001) ), 2) , %Round(%DecDiv(%DecMult((A.QTY_SH_RECVD_SUOM - A.QTY_EARLY - A.QTY_LATE), 100), (A.QTY_SH_RECVD_SUOM + .00001) ), 2) ,A.QTY_EARLY , %Round(%DecDiv(%DecMult(A.QTY_EARLY, 100), (A.QTY_SH_RECVD_SUOM + .0001) ), 2) ,A.QTY_LATE , %Round(%DecDiv(%DecMult(A.QTY_LATE, 100), (A.QTY_SH_RECVD_SUOM + .0001) ), 2) ,A.QTY_OPEN , %Round(%DecDiv(%DecMult(A.QTY_PO - A.QTY_OPEN, 100), (A.QTY_PO + .0001) ), 2) ,A.AMT_ORDERED , %Round(%DecDiv(%DecMult(A.AMT_ORDERED, 100), (A.QTY_PO + .00001) ), 2) ,A.AMT_CANCELED ,A.AMT_RECEIVED , %Round(%DecDiv(%DecMult(A.AMT_RECEIVED, 100), (A.QTY_SH_RECVD_SUOM + .00001) ), 2) ,A.AMT_REJECTED ,A.AMT_RETURNED ,A.AMT_INVOICED , %Round(%DecDiv(%DecMult(A.AMT_INVOICED, 100), (A.QTY_VCHR + .00001) ), 2) ,A.CURRENCY_PYMNT ,A.PAID_AMT_GROSS ,A.PAID_AMT ,A.PRICE_LIST ,A.BANK_SETID ,A.BANK_CD ,A.WT_ORDERED ,A.WT_CANCELED ,A.WT_RECEIVED ,A.WT_REJECTED ,A.WT_RETURNED ,A.NUM_OF_ORDERS ,A.NUM_OF_RECEIPTS , %Round(%DecDiv(%DecMult((A.NUM_OF_RECEIPTS - A.EARLY_SHIPMENTS - A.LATE_SHIPMENTS), 100), (A.NUM_OF_RECEIPTS + .00001) ), 2) ,A.EARLY_SHIPMENTS , %Round(%DecDiv(%DecMult(A.EARLY_SHIPMENTS, 100), (A.NUM_OF_RECEIPTS + .00001) ), 2) ,A.LATE_SHIPMENTS , %Round(%DecDiv(%DecMult(A.LATE_SHIPMENTS, 100), (A.NUM_OF_RECEIPTS + .00001) ), 2) ,A.NUM_OF_RETURNS , %Round(%DecDiv(%DecMult((A.NUM_OF_RECEIPTS - A.OVER_SHIPMENTS - A.UNDER_SHIPMENTS), 100), (A.NUM_OF_RECEIPTS + .00001) ), 2) ,A.OVER_SHIPMENTS , %Round(%DecDiv(%DecMult(A.OVER_SHIPMENTS, 100), (A.NUM_OF_RECEIPTS + .00001) ), 2) ,A.UNDER_SHIPMENTS , %Round(%DecDiv(%DecMult(A.UNDER_SHIPMENTS, 100), (A.NUM_OF_RECEIPTS + .00001) ), 2) ,A.NUM_DAYS_DEVIATION ,A.LEAD_TIME ,A.LEAD_TIME_MAD ,A.LAST_DTTM_UPDATE ,A.BUYER_ID ,V.VENDOR_NAME_SHORT ,V.NAME1 FROM PS_PRCR_ITEM_HST A , PS_VENDOR V , PS_ITM_CAT_TBL C WHERE A.VENDOR_SETID = V.SETID AND A.VENDOR_ID = V.VENDOR_ID AND C.SETID = A.ITM_SETID AND C.CATEGORY_ID = A.CATEGORY_ID AND C.EFFDT <= %CurrentDateIn AND C.EFF_STATUS = 'A' |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | REPORT_ENTITY |
Character(5) | VARCHAR2(5) NOT NULL | Reporting Entity ID |
| 2 | PRCR_YEAR |
Number(4,0) | SMALLINT NOT NULL | Procurement Year |
| 3 | PRCR_PERIOD |
Number(3,0) | SMALLINT NOT NULL | Procurement Period |
| 4 | SETID |
Character(5) | VARCHAR2(5) NOT NULL | SetID |
| 5 | INV_ITEM_ID |
Character(18) | VARCHAR2(18) NOT NULL | Item ID |
| 6 | CATEGORY_ID |
Character(5) | VARCHAR2(5) NOT NULL | Category ID |
| 7 | CATEGORY_CD |
Character(18) | VARCHAR2(18) NOT NULL | Category Code |
| 8 | DESCR60 | Character(60) | VARCHAR2(60) NOT NULL | Description |
| 9 | VENDOR_SETID |
Character(5) | VARCHAR2(5) NOT NULL | Vendor SetID |
| 10 | VENDOR_ID |
Character(10) | VARCHAR2(10) NOT NULL | Vendor Identifier |
| 11 | BUSINESS_UNIT_PO |
Character(5) | VARCHAR2(5) NOT NULL | PO Business Unit |
| 12 | SHIPTO_SETID |
Character(5) | VARCHAR2(5) NOT NULL | ShipTo SetID |
| 13 | SHIPTO_ID |
Character(10) | VARCHAR2(10) NOT NULL | Ship To Location |
| 14 | CURRENCY_CD |
Character(3) | VARCHAR2(3) NOT NULL | Currency Code |
| 15 | UNIT_MEASURE_STD |
Character(3) | VARCHAR2(3) NOT NULL | Standard Unit of Measure |
| 16 | UNIT_MEASURE_WT | Character(3) | VARCHAR2(3) NOT NULL | Weight UOM |
| 17 | QTY_PO | Number(16,4) | DECIMAL(15,4) NOT NULL | Purchase Order Quantity |
| 18 | QTY_CANCEL | Number(16,4) | DECIMAL(15,4) NOT NULL | Quantity Canceled |
| 19 | QTY_CANCEL_PER | Signed Number(9,2) | DECIMAL(7,2) NOT NULL | Quantity Cancel Percent |
| 20 | QTY_SH_RECVD_SUOM | Number(16,4) | DECIMAL(15,4) NOT NULL | Receipt Quantity |
| 21 | QTY_ACCEPTED | Number(16,4) | DECIMAL(15,4) NOT NULL | Qty Accepted |
| 22 | QTY_ACCEPT_PER | Signed Number(17,4) | DECIMAL(15,4) NOT NULL | Added for CN#WV802-2.0 |
| 23 | QTY_SH_REJCT_SUOM | Number(16,4) | DECIMAL(15,4) NOT NULL | Rejected Quantity |
| 24 | QTY_REJECT_PER | Signed Number(9,2) | DECIMAL(7,2) NOT NULL | Quantity Reject Percent |
| 25 | QTY_RETURN | Number(16,4) | DECIMAL(15,4) NOT NULL | Quantity Returned |
| 26 | QTY_RETURN_PER | Signed Number(9,2) | DECIMAL(7,2) NOT NULL | Quantity Return Percent |
| 27 | QTY_VCHR | Signed Number(17,4) | DECIMAL(15,4) NOT NULL | Quantity Vouchered |
| 28 | QTY_DEFECTIVE | Number(16,4) | DECIMAL(15,4) NOT NULL | Quantity Defective |
| 29 | QTY_DEFECTIVE_PER | Signed Number(9,2) | DECIMAL(7,2) NOT NULL | Quantity Defective Percentage |
| 30 | QTY_ON_TIME_PER | Signed Number(9,2) | DECIMAL(7,2) NOT NULL | Quantity On-Time Percentage |
| 31 | QTY_EARLY | Number(16,4) | DECIMAL(15,4) NOT NULL | Quantity Early |
| 32 | QTY_EARLY_PER | Signed Number(9,2) | DECIMAL(7,2) NOT NULL | Quantity Early Percentage |
| 33 | QTY_LATE | Number(16,4) | DECIMAL(15,4) NOT NULL | Quantity Late |
| 34 | QTY_LATE_PER | Signed Number(9,2) | DECIMAL(7,2) NOT NULL | Quantity Late Percentage |
| 35 | QTY_OPEN | Number(16,4) | DECIMAL(15,4) NOT NULL | Open Quantity |
| 36 | QTY_OPEN_PER | Signed Number(9,2) | DECIMAL(7,2) NOT NULL | Quantity Open Percentage |
| 37 | AMT_ORDERED | Number(27,3) | DECIMAL(26,3) NOT NULL | Amount Ordered |
| 38 | AVG_PO_PRICE | Signed Number(17,2) | DECIMAL(15,2) NOT NULL | Average PO Price |
| 39 | AMT_CANCELED | Number(27,3) | DECIMAL(26,3) NOT NULL | Amount Canceled |
| 40 | AMT_RECEIVED | Number(27,3) | DECIMAL(26,3) NOT NULL | Amount Received |
| 41 | AVG_RECV_PRICE | Signed Number(17,2) | DECIMAL(15,2) NOT NULL | Average Receipt Price |
| 42 | AMT_REJECTED | Number(27,3) | DECIMAL(26,3) NOT NULL | Amount Rejected |
| 43 | AMT_RETURNED | Number(27,3) | DECIMAL(26,3) NOT NULL | Amount Returned |
| 44 | AMT_INVOICED | Number(27,3) | DECIMAL(26,3) NOT NULL | Amount Invoiced |
| 45 | AVG_INV_PRICE | Signed Number(17,2) | DECIMAL(15,2) NOT NULL | Average Invoice Price |
| 46 | CURRENCY_PYMNT | Character(3) | VARCHAR2(3) NOT NULL | Payment Currency |
| 47 | PAID_AMT_GROSS | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Gross Paid Amount |
| 48 | PAID_AMT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Paid Amount |
| 49 | PRICE_LIST | Number(16,5) | DECIMAL(15,5) NOT NULL | Standard Price |
| 50 | BANK_SETID | Character(5) | VARCHAR2(5) NOT NULL | The PeopleSoft tableset ID associated with a given bank/counterparty. |
| 51 | BANK_CD | Character(5) | VARCHAR2(5) NOT NULL | Bank Code |
| 52 | WT_ORDERED | Number(16,4) | DECIMAL(15,4) NOT NULL | Weight Ordered |
| 53 | WT_CANCELED | Number(16,4) | DECIMAL(15,4) NOT NULL | Weight Canceled |
| 54 | WT_RECEIVED | Number(16,4) | DECIMAL(15,4) NOT NULL | Weight Received |
| 55 | WT_REJECTED | Number(16,4) | DECIMAL(15,4) NOT NULL | Weight Rejected |
| 56 | WT_RETURNED | Number(16,4) | DECIMAL(15,4) NOT NULL | Weight Returned |
| 57 | NUM_OF_ORDERS | Number(7,0) | INTEGER NOT NULL | Number of Orders |
| 58 | NUM_OF_RECEIPTS | Number(7,0) | INTEGER NOT NULL | Number of Receipts |
| 59 | SHIP_ON_TIME_PER | Signed Number(9,2) | DECIMAL(7,2) NOT NULL | Shipments on time Percent |
| 60 | EARLY_SHIPMENTS | Number(7,0) | INTEGER NOT NULL | Number of Early Shipments |
| 61 | SHIP_EARLY_PER | Signed Number(9,2) | DECIMAL(7,2) NOT NULL | Shipments Early Percent |
| 62 | LATE_SHIPMENTS | Number(7,0) | INTEGER NOT NULL | Number of Late Shipments |
| 63 | SHIP_LATE_PER | Signed Number(9,2) | DECIMAL(7,2) NOT NULL | Shipments Late Percent |
| 64 | NUM_OF_RETURNS | Number(7,0) | INTEGER NOT NULL | Number of Returns |
| 65 | FULL_SHIP_PER | Signed Number(17,4) | DECIMAL(15,4) NOT NULL | Added for CN#WV802-2.0 |
| 66 | OVER_SHIPMENTS | Number(7,0) | INTEGER NOT NULL | Number of Over Shipments |
| 67 | OVER_SHIP_PER | Signed Number(9,2) | DECIMAL(7,2) NOT NULL | Over Shipment Percent |
| 68 | UNDER_SHIPMENTS | Number(7,0) | INTEGER NOT NULL | Number of Under Shipments |
| 69 | UNDER_SHIP_PER | Signed Number(9,2) | DECIMAL(7,2) NOT NULL | Under Shipment Percent |
| 70 | NUM_DAYS_DEVIATION | Number(9,0) | DECIMAL(9) NOT NULL | Number Days of Deviation |
| 71 | LEAD_TIME | Number(3,0) | SMALLINT NOT NULL | Lead Time Days |
| 72 | LEAD_TIME_MAD | Signed Number(9,2) | DECIMAL(7,2) NOT NULL | Lead Time Deviation |
| 73 | LAST_DTTM_UPDATE | DateTime(26) | TIMESTAMP | Specifies the date and time of the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts. |
| 74 | BUYER_ID | Character(30) | VARCHAR2(30) NOT NULL | Buyer |
| 75 | VENDOR_NAME_SHORT | Character(14) | VARCHAR2(14) NOT NULL | Short Vendor Name |
| 76 | NAME1 | Character(40) | VARCHAR2(40) NOT NULL | Name 1 |