REPORT_ENTITYPRCR_YEARPRCR_PERIODITM_SETIDINV_ITEM_IDCATEGORY_IDVENDOR_SETIDVENDOR_IDBUSINESS_UNITSHIPTO_SETIDSHIPTO_IDCURRENCY_CDUNIT_MEASURE_STDUNIT_MEASURE_WTPO_ONTIME_SA_VW(SQL View) |
Index Back |
|---|---|
Procurement History Item View |
| SELECT A.REPORT_ENTITY ,A.PRCR_YEAR ,A.PRCR_PERIOD ,A.ITM_SETID ,A.INV_ITEM_ID ,A.CATEGORY_ID ,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 ,RH.RECEIPT_DT ,RH.RECEIVER_ID , RL.PO_ID ,A.QTY_PO , PH.PO_REF , PH.VNDR_LOC , CAT.CATEGORY_CD , C.BEGIN_DT , C.END_DT , %Round(%DecDiv(%DecMult((A.QTY_SH_RECVD_SUOM - A.QTY_SH_REJCT_SUOM - A.QTY_RETURN), 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 ) + .0001) ), 2) ,%Round(%DecDiv(%DecMult((A.QTY_SH_RECVD_SUOM - A.QTY_EARLY - A.QTY_LATE), 100), ((A.QTY_SH_RECVD_SUOM ) + .0001) ), 2) ,A.QTY_EARLY , %Round(%DecDiv(%DecMult(A.QTY_EARLY, 100), (A.QTY_SH_RECVD_SUOM + .0001) ), 2) ,A.QTY_LATE ,A.QTY_LATE , %Round(%DecDiv(%DecMult(A.QTY_LATE, 100), (A.QTY_SH_RECVD_SUOM + .0001) ), 2) , %Round(%DecDiv(%DecMult(A.QTY_LATE, 100), (A.QTY_SH_RECVD_SUOM + .0001) ), 2) ,A.BUYER_ID ,O.OPRDEFNDESC ,A.DESCR ,V.VENDOR_NAME_SHORT ,V.NAME1 , %NumToChar(A.PRCR_PERIOD) %Concat '/' %Concat %Substring(%NumToChar(A.PRCR_YEAR), 3, 2) FROM PS_PRCR_ITEM_HST A , PS_VENDOR V , PS_CAL_DETP_TBL C , PS_PRCR_ENTITY_TBL E , PS_RECV_HDR RH , PS_RECV_LN_SHIP RL , PS_PO_HDR PH , ps_itm_cat_tbl CAT , PS_INSTALLATION_PO D , PS_WV_METRIC_SETUP W , PSOPRDEFN O WHERE A.REPORT_ENTITY = W.REPORT_ENTITY AND A.VENDOR_SETID = V.SETID AND A.VENDOR_ID = V.VENDOR_ID AND A.INV_ITEM_ID <> ' ' AND E.REPORT_ENTITY = A.REPORT_ENTITY AND E.ENTITY_TYPE = 'P' AND C.SETID = E.CALENDAR_SETID AND C.CALENDAR_ID = E.CALENDAR_ID AND C.FISCAL_YEAR = A.PRCR_YEAR AND C.ACCOUNTING_PERIOD = A.PRCR_PERIOD AND RH.VENDOR_SETID = A.VENDOR_SETID AND RH.VENDOR_ID = A.VENDOR_ID AND PH.VENDOR_SETID = A.VENDOR_SETID AND PH.VENDOR_ID = A.VENDOR_ID AND PH.BUYER_ID = A.BUYER_ID AND A.BUSINESS_UNIT_PO = RH.BUSINESS_UNIT AND RH.BUSINESS_UNIT = RL.BUSINESS_UNIT AND RH.RECEIVER_ID = RL.RECEIVER_ID AND O.OPRID = PH.BUYER_ID AND A.BUSINESS_UNIT_PO = RL.BUSINESS_UNIT_PO AND A.BUSINESS_UNIT_PO = PH.BUSINESS_UNIT AND PH.PO_ID = RL.PO_ID AND RL.ITM_SETID = A.ITM_SETID AND RL.INV_ITEM_ID = A.INV_ITEM_ID AND A.SHIPTO_ID = RL.SHIPTO_ID AND RH.RECEIPT_DT >= C.BEGIN_DT AND RH.RECEIPT_DT <= C.END_DT AND A.num_of_receipts > 0 AND A.ITM_SETID = CAT.SETID AND CAT.CATEGORY_TYPE = D.CATEGORY_TYPE AND A.CATEGORY_ID = CAT.CATEGORY_ID AND CAT.EFF_STATUS = 'A' AND CAT.EFFDT = ( SELECT MAX(CT.EFFDT) FROM PS_ITM_CAT_TBL CT WHERE CT.SETID = CAT.SETID AND CT.CATEGORY_TYPE = CAT.CATEGORY_TYPE AND CT.CATEGORY_ID = CAT.CATEGORY_ID AND CT.EFFDT <= %CurrentDateIn) |
| # | 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 | VENDOR_SETID |
Character(5) | VARCHAR2(5) NOT NULL | Vendor SetID |
| 8 | VENDOR_ID |
Character(10) | VARCHAR2(10) NOT NULL | Vendor Identifier |
| 9 | BUSINESS_UNIT |
Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 10 | SHIPTO_SETID |
Character(5) | VARCHAR2(5) NOT NULL | ShipTo SetID |
| 11 | SHIPTO_ID |
Character(10) | VARCHAR2(10) NOT NULL | Ship To Location |
| 12 | CURRENCY_CD |
Character(3) | VARCHAR2(3) NOT NULL | Currency Code |
| 13 | UNIT_MEASURE_STD |
Character(3) | VARCHAR2(3) NOT NULL | Standard Unit of Measure |
| 14 | UNIT_MEASURE_WT |
Character(3) | VARCHAR2(3) NOT NULL | Weight UOM |
| 15 | RECEIPT_DT | Date(10) | DATE | Received Date |
| 16 | RECEIVER_ID | Character(10) | VARCHAR2(10) NOT NULL | Receiver number |
| 17 | PO_ID | Character(10) | VARCHAR2(10) NOT NULL | Purchase Order |
| 18 | QTY_PO | Number(16,4) | DECIMAL(15,4) NOT NULL | Purchase Order Quantity |
| 19 | PO_REF | Character(30) | VARCHAR2(30) NOT NULL | Specifies the purchase order number associated with a receivables item. |
| 20 | VNDR_LOC | Character(10) | VARCHAR2(10) NOT NULL | Vendor Location |
| 21 | CATEGORY_CD | Character(18) | VARCHAR2(18) NOT NULL | Category Code |
| 22 | BEGIN_DT | Date(10) | DATE | Begin Date |
| 23 | END_DT | Date(10) | DATE | End Date |
| 24 | QTY_RECEIPT_PER | Number(8,2) | DECIMAL(7,2) NOT NULL | Quantity Receipt Percent |
| 25 | QTY_ON_TIME_PER | Signed Number(9,2) | DECIMAL(7,2) NOT NULL | Quantity On-Time Percentage |
| 26 | QTY_ON_TIME_PER2 | Signed Number(9,2) | DECIMAL(7,2) NOT NULL | Quantity On-Time Percentage |
| 27 | QTY_EARLY | Number(16,4) | DECIMAL(15,4) NOT NULL | Quantity Early |
| 28 | QTY_EARLY_PER | Signed Number(9,2) | DECIMAL(7,2) NOT NULL | Quantity Early Percentage |
| 29 | QTY_LATE | Number(16,4) | DECIMAL(15,4) NOT NULL | Quantity Late |
| 30 | QTY_LATE2 | Number(16,4) | DECIMAL(15,4) NOT NULL | Quantity Late |
| 31 | QTY_LATE_PER | Signed Number(9,2) | DECIMAL(7,2) NOT NULL | Quantity Late Percentage |
| 32 | QTY_LATE_PER2 | Signed Number(9,2) | DECIMAL(7,2) NOT NULL | Quantity Late Percentage |
| 33 | BUYER_ID | Character(30) | VARCHAR2(30) NOT NULL | Buyer |
| 34 | BUYER_NAME | Character(30) | VARCHAR2(30) NOT NULL | Buyer Desc |
| 35 | ITM_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Item Description |
| 36 | VENDOR_NAME_SHORT | Character(14) | VARCHAR2(14) NOT NULL | Short Vendor Name |
| 37 | NAME1 | Character(40) | VARCHAR2(40) NOT NULL | Name 1 |
| 38 | MONTH_CHAR | Character(15) | VARCHAR2(15) NOT NULL | Accounting Period |