REPORT_ENTITYPRCR_YEARPRCR_PERIODVENDOR_SETIDVENDOR_IDSCD_PRCR_HST_VW(SQL View) |
Index Back |
|---|---|
Procurement History Items SCDProcurement History Item View for Supplier Scorecarding KPI |
| SELECT A.REPORT_ENTITY , A.PRCR_YEAR , A.PRCR_PERIOD , A.VENDOR_SETID , A.VENDOR_ID ,C.BEGIN_DT ,C.END_DT , SUM(A.QTY_PO) , SUM(A.QTY_CANCEL) , SUM(A.QTY_SH_RECVD_SUOM) , SUM(A.QTY_SH_RECVD_SUOM - A.QTY_SH_REJCT_SUOM - A.QTY_RETURN) , SUM(A.QTY_SH_REJCT_SUOM) , SUM(A.QTY_RETURN) , SUM(A.QTY_VCHR) , SUM(A.QTY_DEFECTIVE) , SUM(A.QTY_EARLY) , SUM(A.QTY_LATE) , SUM(A.QTY_OPEN) , SUM(A.AMT_ORDERED) , SUM(A.AMT_CANCELED) , SUM(A.AMT_RECEIVED) , SUM(A.AMT_REJECTED) , SUM(A.AMT_RETURNED) , SUM(A.AMT_INVOICED) , SUM(A.PAID_AMT_GROSS) , SUM(A.PAID_AMT) , SUM(A.PRICE_LIST) , SUM(A.NUM_OF_ORDERS) , SUM(A.NUM_OF_RECEIPTS) , SUM(A.EARLY_SHIPMENTS) , SUM(A.LATE_SHIPMENTS) , SUM(A.NUM_OF_RETURNS) , SUM(A.OVER_SHIPMENTS) , SUM(A.UNDER_SHIPMENTS) , SUM(A.NUM_DAYS_DEVIATION) FROM PS_PRCR_ITEM_HST A INNER JOIN PS_PRCR_ENTITY_TBL B ON A.REPORT_ENTITY = B.REPORT_ENTITY INNER JOIN PS_CAL_DETP_TBL C ON B.CALENDAR_SETID = C.SETID AND B.CALENDAR_ID = C.CALENDAR_ID AND A.PRCR_YEAR = C.FISCAL_YEAR AND A.PRCR_PERIOD = C.ACCOUNTING_PERIOD GROUP BY A.REPORT_ENTITY , A.PRCR_YEAR , A.PRCR_PERIOD ,C.BEGIN_DT ,C.END_DT , A.VENDOR_SETID , A.VENDOR_ID |
| # | 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 | VENDOR_SETID |
Character(5) | VARCHAR2(5) NOT NULL | Vendor SetID |
| 5 | VENDOR_ID |
Character(10) | VARCHAR2(10) NOT NULL | Vendor Identifier |
| 6 | BEGIN_DT | Date(10) | DATE | Begin Date |
| 7 | END_DT | Date(10) | DATE | End Date |
| 8 | QTY_PO | Number(16,4) | DECIMAL(15,4) NOT NULL | Purchase Order Quantity |
| 9 | QTY_CANCEL | Number(16,4) | DECIMAL(15,4) NOT NULL | Quantity Canceled |
| 10 | QTY_SH_RECVD_SUOM | Number(16,4) | DECIMAL(15,4) NOT NULL | Receipt Quantity |
| 11 | QTY_ACCEPTED | Number(16,4) | DECIMAL(15,4) NOT NULL | Qty Accepted |
| 12 | QTY_SH_REJCT_SUOM | Number(16,4) | DECIMAL(15,4) NOT NULL | Rejected Quantity |
| 13 | QTY_RETURN | Number(16,4) | DECIMAL(15,4) NOT NULL | Quantity Returned |
| 14 | QTY_VCHR | Signed Number(17,4) | DECIMAL(15,4) NOT NULL | Quantity Vouchered |
| 15 | QTY_DEFECTIVE | Number(16,4) | DECIMAL(15,4) NOT NULL | Quantity Defective |
| 16 | QTY_EARLY | Number(16,4) | DECIMAL(15,4) NOT NULL | Quantity Early |
| 17 | QTY_LATE | Number(16,4) | DECIMAL(15,4) NOT NULL | Quantity Late |
| 18 | QTY_OPEN | Number(16,4) | DECIMAL(15,4) NOT NULL | Open Quantity |
| 19 | AMT_ORDERED | Number(27,3) | DECIMAL(26,3) NOT NULL | Amount Ordered |
| 20 | AMT_CANCELED | Number(27,3) | DECIMAL(26,3) NOT NULL | Amount Canceled |
| 21 | AMT_RECEIVED | Number(27,3) | DECIMAL(26,3) NOT NULL | Amount Received |
| 22 | AMT_REJECTED | Number(27,3) | DECIMAL(26,3) NOT NULL | Amount Rejected |
| 23 | AMT_RETURNED | Number(27,3) | DECIMAL(26,3) NOT NULL | Amount Returned |
| 24 | AMT_INVOICED | Number(27,3) | DECIMAL(26,3) NOT NULL | Amount Invoiced |
| 25 | PAID_AMT_GROSS | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Gross Paid Amount |
| 26 | PAID_AMT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Paid Amount |
| 27 | PRICE_LIST | Number(16,5) | DECIMAL(15,5) NOT NULL | Standard Price |
| 28 | NUM_OF_ORDERS | Number(7,0) | INTEGER NOT NULL | Number of Orders |
| 29 | NUM_OF_RECEIPTS | Number(7,0) | INTEGER NOT NULL | Number of Receipts |
| 30 | EARLY_SHIPMENTS | Number(7,0) | INTEGER NOT NULL | Number of Early Shipments |
| 31 | LATE_SHIPMENTS | Number(7,0) | INTEGER NOT NULL | Number of Late Shipments |
| 32 | NUM_OF_RETURNS | Number(7,0) | INTEGER NOT NULL | Number of Returns |
| 33 | OVER_SHIPMENTS | Number(7,0) | INTEGER NOT NULL | Number of Over Shipments |
| 34 | UNDER_SHIPMENTS | Number(7,0) | INTEGER NOT NULL | Number of Under Shipments |
| 35 | NUM_DAYS_DEVIATION | Number(9,0) | DECIMAL(9) NOT NULL | Number Days of Deviation |