REPORT_ENTITYPRCR_YEARPRCR_PERIODITM_SETIDINV_ITEM_IDCATEGORY_IDBUSINESS_UNIT_POVENDOR_SETIDVENDOR_IDSHIPTO_IDUNIT_MEASURE_STDPRCR_HST_SVN_VW(SQL View) |
Index Back |
|---|---|
Prcr Hist ShP Vnd VW 4 SqlSrv |
| SELECT REPORT_ENTITY ,PRCR_YEAR ,PRCR_PERIOD ,ITM_SETID ,INV_ITEM_ID ,CATEGORY_ID ,BUSINESS_UNIT_PO ,VENDOR_SETID ,VENDOR_ID ,SHIPTO_ID ,UNIT_MEASURE_STD ,SUM(QTY_PO) ,SUM(QTY_CANCEL) , %Round(%DecDiv(%DecMult(SUM(QTY_CANCEL), 100), (SUM(QTY_PO) + .0001) ), 2) ,SUM(QTY_SH_RECVD_SUOM) ,SUM(QTY_SH_REJCT_SUOM) , %Round(%DecDiv(%DecMult(SUM(QTY_SH_REJCT_SUOM), 100), (SUM(QTY_SH_RECVD_SUOM) + SUM(QTY_SH_REJCT_SUOM) + SUM(QTY_RETURN) + .0001) ), 2) ,SUM(QTY_RETURN) , %Round(%DecDiv(%DecMult(SUM(QTY_RETURN), 100), (SUM(QTY_SH_RECVD_SUOM) + SUM(QTY_SH_REJCT_SUOM) + SUM(QTY_RETURN) + .0001) ), 2) ,SUM(QTY_VCHR) ,SUM(AMT_ORDERED) , %Round(%DecDiv(%DecMult(SUM(AMT_ORDERED), 100), (SUM(QTY_PO) + .0001) ), 2) ,SUM(AMT_CANCELED) ,SUM(AMT_RECEIVED) , %Round(%DecDiv(%DecMult(SUM(AMT_RECEIVED), 100), (SUM(QTY_SH_RECVD_SUOM) + .0001) ), 2) ,SUM(AMT_REJECTED) ,SUM(AMT_RETURNED) ,SUM(AMT_INVOICED) , %Round(%DecDiv(%DecMult(SUM(AMT_INVOICED), 100), (SUM(QTY_VCHR) + .0001) ), 2) ,CURRENCY_PYMNT ,SUM(PAID_AMT_GROSS) ,SUM(PAID_AMT) ,SUM(PRICE_LIST) ,SUM(WT_ORDERED) ,SUM(WT_CANCELED) ,SUM(WT_RECEIVED) ,SUM(WT_REJECTED) ,SUM(WT_RETURNED) ,SUM(NUM_OF_ORDERS) ,SUM(NUM_OF_RECEIPTS) , %Round(%DecDiv(%DecMult((SUM(NUM_OF_RECEIPTS) - SUM(EARLY_SHIPMENTS) - SUM(LATE_SHIPMENTS)), 100), (SUM(NUM_OF_RECEIPTS) + .0001) ), 2) ,SUM(EARLY_SHIPMENTS) , %Round(%DecDiv(%DecMult(SUM(EARLY_SHIPMENTS), 100), (SUM(NUM_OF_RECEIPTS) + .0001) ), 2) ,SUM(LATE_SHIPMENTS) , %Round(%DecDiv(%DecMult(SUM(LATE_SHIPMENTS), 100), (SUM(NUM_OF_RECEIPTS) + .0001) ), 2) ,SUM(NUM_OF_RETURNS) ,SUM(OVER_SHIPMENTS) , %Round(%DecDiv(%DecMult(SUM(OVER_SHIPMENTS), 100), (SUM(NUM_OF_RECEIPTS) + .0001) ), 2) ,SUM(UNDER_SHIPMENTS) , %Round(%DecDiv(%DecMult(SUM(UNDER_SHIPMENTS), 100), (SUM(NUM_OF_RECEIPTS) + .0001) ), 2) ,LEAD_TIME ,BUYER_ID ,ITM_DESCR ,VENDOR_NAME_SHORT ,NAME1 FROM PS_PRCR_HST_AAA_VW GROUP BY REPORT_ENTITY , PRCR_YEAR , PRCR_PERIOD , ITM_SETID , INV_ITEM_ID , CATEGORY_ID ,BUSINESS_UNIT_PO , VENDOR_SETID , VENDOR_ID , SHIPTO_ID , UNIT_MEASURE_STD , LEAD_TIME ,BUYER_ID ,ITM_DESCR ,PRICE_LIST ,VENDOR_NAME_SHORT ,NAME1 ,CURRENCY_PYMNT |
| # | 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 | ITM_SETID |
Character(5) | VARCHAR2(5) NOT NULL | Item 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 | BUSINESS_UNIT_PO |
Character(5) | VARCHAR2(5) NOT NULL | PO Business Unit |
| 8 | VENDOR_SETID |
Character(5) | VARCHAR2(5) NOT NULL | Vendor SetID |
| 9 | VENDOR_ID |
Character(10) | VARCHAR2(10) NOT NULL | Vendor Identifier |
| 10 | SHIPTO_ID |
Character(10) | VARCHAR2(10) NOT NULL | Ship To Location |
| 11 | UNIT_MEASURE_STD |
Character(3) | VARCHAR2(3) NOT NULL | Standard Unit of Measure |
| 12 | QTY_PO | Number(16,4) | DECIMAL(15,4) NOT NULL | Purchase Order Quantity |
| 13 | QTY_CANCEL | Number(16,4) | DECIMAL(15,4) NOT NULL | Quantity Canceled |
| 14 | QTY_CANCEL_PER | Signed Number(9,2) | DECIMAL(7,2) NOT NULL | Quantity Cancel Percent |
| 15 | QTY_SH_RECVD_SUOM | Number(16,4) | DECIMAL(15,4) NOT NULL | Receipt Quantity |
| 16 | QTY_SH_REJCT_SUOM | Number(16,4) | DECIMAL(15,4) NOT NULL | Rejected Quantity |
| 17 | QTY_REJECT_PER | Signed Number(9,2) | DECIMAL(7,2) NOT NULL | Quantity Reject Percent |
| 18 | QTY_RETURN | Number(16,4) | DECIMAL(15,4) NOT NULL | Quantity Returned |
| 19 | QTY_RETURN_PER | Signed Number(9,2) | DECIMAL(7,2) NOT NULL | Quantity Return Percent |
| 20 | QTY_VCHR | Signed Number(17,4) | DECIMAL(15,4) NOT NULL | Quantity Vouchered |
| 21 | AMT_ORDERED | Number(27,3) | DECIMAL(26,3) NOT NULL | Amount Ordered |
| 22 | AVG_PO_PRICE | Signed Number(17,2) | DECIMAL(15,2) NOT NULL | Average PO Price |
| 23 | AMT_CANCELED | Number(27,3) | DECIMAL(26,3) NOT NULL | Amount Canceled |
| 24 | AMT_RECEIVED | Number(27,3) | DECIMAL(26,3) NOT NULL | Amount Received |
| 25 | AVG_RECV_PRICE | Signed Number(17,2) | DECIMAL(15,2) NOT NULL | Average Receipt Price |
| 26 | AMT_REJECTED | Number(27,3) | DECIMAL(26,3) NOT NULL | Amount Rejected |
| 27 | AMT_RETURNED | Number(27,3) | DECIMAL(26,3) NOT NULL | Amount Returned |
| 28 | AMT_INVOICED | Number(27,3) | DECIMAL(26,3) NOT NULL | Amount Invoiced |
| 29 | AVG_INV_PRICE | Signed Number(17,2) | DECIMAL(15,2) NOT NULL | Average Invoice Price |
| 30 | CURRENCY_PYMNT | Character(3) | VARCHAR2(3) NOT NULL | Payment Currency |
| 31 | PAID_AMT_GROSS | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Gross Paid Amount |
| 32 | PAID_AMT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Paid Amount |
| 33 | PRICE_LIST | Number(16,5) | DECIMAL(15,5) NOT NULL | Standard Price |
| 34 | WT_ORDERED | Number(16,4) | DECIMAL(15,4) NOT NULL | Weight Ordered |
| 35 | WT_CANCELED | Number(16,4) | DECIMAL(15,4) NOT NULL | Weight Canceled |
| 36 | WT_RECEIVED | Number(16,4) | DECIMAL(15,4) NOT NULL | Weight Received |
| 37 | WT_REJECTED | Number(16,4) | DECIMAL(15,4) NOT NULL | Weight Rejected |
| 38 | WT_RETURNED | Number(16,4) | DECIMAL(15,4) NOT NULL | Weight Returned |
| 39 | NUM_OF_ORDERS | Number(7,0) | INTEGER NOT NULL | Number of Orders |
| 40 | NUM_OF_RECEIPTS | Number(7,0) | INTEGER NOT NULL | Number of Receipts |
| 41 | SHIP_ON_TIME_PER | Signed Number(9,2) | DECIMAL(7,2) NOT NULL | Shipments on time Percent |
| 42 | EARLY_SHIPMENTS | Number(7,0) | INTEGER NOT NULL | Number of Early Shipments |
| 43 | SHIP_EARLY_PER | Signed Number(9,2) | DECIMAL(7,2) NOT NULL | Shipments Early Percent |
| 44 | LATE_SHIPMENTS | Number(7,0) | INTEGER NOT NULL | Number of Late Shipments |
| 45 | SHIP_LATE_PER | Signed Number(9,2) | DECIMAL(7,2) NOT NULL | Shipments Late Percent |
| 46 | NUM_OF_RETURNS | Number(7,0) | INTEGER NOT NULL | Number of Returns |
| 47 | OVER_SHIPMENTS | Number(7,0) | INTEGER NOT NULL | Number of Over Shipments |
| 48 | OVER_SHIP_PER | Signed Number(9,2) | DECIMAL(7,2) NOT NULL | Over Shipment Percent |
| 49 | UNDER_SHIPMENTS | Number(7,0) | INTEGER NOT NULL | Number of Under Shipments |
| 50 | UNDER_SHIP_PER | Signed Number(9,2) | DECIMAL(7,2) NOT NULL | Under Shipment Percent |
| 51 | LEAD_TIME | Number(3,0) | SMALLINT NOT NULL | Lead Time Days |
| 52 | BUYER_ID | Character(30) | VARCHAR2(30) NOT NULL | Buyer |
| 53 | ITM_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Item Description |
| 54 | VENDOR_NAME_SHORT | Character(14) | VARCHAR2(14) NOT NULL | Short Vendor Name |
| 55 | NAME1 | Character(40) | VARCHAR2(40) NOT NULL | Name 1 |