SP_APR_EX_SR_VW(SQL View) |
Index Back |
|---|---|
Expense History ViewExpense History View |
| SELECT O.ROLEUSER ,E.PERSON_ID ,X.BUSINESS_UNIT ,X.WORK_ORDER_ID ,X.DISTRIB_LINE_NUM ,X.SHEET_ID ,E.EXP_HDR_STATUS ,E.CREATION_DT ,E.OPRID_ENTERED_BY ,B.DESCRSHORT ,W.WO_NAME ,W.PROVIDER_CONTACT ,SUM(L.MONETARY_AMOUNT) ,SUM(L.AMOUNT_BSE) AS AMOUNT_BSE ,L.CURRENCY_CD ,L.CURRENCY_CD_BASE FROM PS_SPA_EXP_XREF X , PS_SPA_SHEET_HDR E , PS_SPA_SHEET_LINE L , PS_BUS_UNIT_TBL_FS B , PS_SPF_WORDERREC W , PS_SPB_PERSON_TBL P , PS_ROLEXLATOPR O , PSOPRALIAS A WHERE E.SHEET_ID = L.SHEET_ID AND X.SHEET_ID = L.SHEET_ID AND B.BUSINESS_UNIT = X.BUSINESS_UNIT AND W.BUSINESS_UNIT = X.BUSINESS_UNIT AND W.WORK_ORDER_ID = X.WORK_ORDER_ID AND E.PERSON_ID = P.PERSON_ID AND A.PERSON_ID IN (W.PROVIDER_CONTACT) AND A.OPRID = O.ROLEUSER GROUP BY O.ROLEUSER,E.PERSON_ID ,X.BUSINESS_UNIT ,X.WORK_ORDER_ID ,X.DISTRIB_LINE_NUM ,X.SHEET_ID ,E.EXP_HDR_STATUS ,E.CREATION_DT , E.OPRID_ENTERED_BY, B.DESCRSHORT, W.WO_NAME, W.PROVIDER_CONTACT,L.CURRENCY_CD ,L.CURRENCY_CD_BASE |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  USERID |
Character(30) | VARCHAR2(30) NOT NULL | A user's ID |
| 2 | PERSON_ID |
Character(15) | VARCHAR2(15) NOT NULL | Person ID |
| 3 | BUSINESS_UNIT |
Character(5) | VARCHAR2(5) NOT NULL |
Business Unit
Default Value: OPR_DEF_TBL_FS.BUSINESS_UNIT Prompt Table: SP_BU_SP_NONVW |
| 4 | WORK_ORDER_ID |
Character(15) | VARCHAR2(15) NOT NULL | Work Order ID |
| 5 | DISTRIB_LINE_NUM |
Number(5,0) | INTEGER NOT NULL | Distribution Line |
| 6 | SHEET_ID |
Character(10) | VARCHAR2(10) NOT NULL | Report ID |
| 7 | EXP_HDR_STATUS | Character(3) | VARCHAR2(3) NOT NULL |
Expense Header Status
ADJ=Adjusted APY=Approved for Payment APZ=Approved for zero Payment DEN=Denied DNA=Denied by Approver DNU=Denied by Auditor OPN=Open PD=Paid PND=Pending RAP=Approved SUB=Submitted |
| 8 | CREATION_DT | Date(10) | DATE | Creation Date |
| 9 | OPRID_ENTERED_BY | Character(30) | VARCHAR2(30) NOT NULL | Entered By 07/25/2011 MRAD 12383033 :Ensured that OPRID_ENTERED_BY is set with format type of MixedCase. 03/22/2013 GL 16482301: Switched OPRID_ENTERED_BY back to MixedCase again. Please don't change it to UpperCase!!! FYI - The alternatives to use a User ID as uppercase: 1) Create your own User ID, add comments in the Field Properties, and fill out the Owner ID 2) Use %Upper meta-SQL in SQL statements 3) Use Upper function in peoplecodes |
| 10 | DESCRSHORT | Character(10) | VARCHAR2(10) NOT NULL | Short Description |
| 11 | WO_NAME | Character(30) | VARCHAR2(30) NOT NULL | Work Order Name |
| 12 | PROVIDER_CONTACT | Character(15) | VARCHAR2(15) NOT NULL | PROVIDER CONTACT |
| 13 | SPF_EXPENSE_AMT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Total expense amount. |
| 14 | SPF_EXP_AMT_BSE | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Total expense amount for base currency |
| 15 | CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL |
Currency Code
Prompt Table: CURRENCY_CD_TBL |
| 16 | CURRENCY_CD_BASE | Character(3) | VARCHAR2(3) NOT NULL | Business Unit Base Currency |