USER_IDSHEET_IDADJ=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
SPA_EXP_APR_H_V(SQL View) |
Index Back |
|---|---|
Expense history approver viewThis view fetches Expense sheets for the approver Date Initials Reference # Description 020707 Raja 1608556002 Added validation for alternate user to be valid when timesheet/expense was submitted. |
| SELECT O.ROLEUSER , E.SHEET_ID , X.BUSINESS_UNIT , B.DESCRSHORT , X.WORK_ORDER_ID , W.WO_NAME , X.DISTRIB_LINE_NUM , E.PERSON_ID , P.NAME , E.EXP_HDR_STATUS , E.CREATION_DT , SUM(L.MONETARY_AMOUNT) , SUM(L.AMOUNT_BSE) , L.CURRENCY_CD , L.CURRENCY_CD_BASE FROM PS_SPA_SHEET_HDR E , PS_SPA_SHEET_LINE L , PS_SPA_EXP_XREF X , PS_SPF_WORDERREC W , PS_SPF_WORDERREC_1 W1 , PS_BUS_UNIT_TBL_FS B , PS_ROLEXLATOPR O , PS_SPB_PERSON_TBL P WHERE E.SHEET_ID = X.SHEET_ID AND E.SHEET_ID = L.SHEET_ID AND E.EXP_HDR_STATUS <> 'OPN' AND X.BUSINESS_UNIT = B.BUSINESS_UNIT AND B.BUSINESS_UNIT = W.BUSINESS_UNIT AND X.WORK_ORDER_ID = W.WORK_ORDER_ID AND W.BUSINESS_UNIT = W1.BUSINESS_UNIT AND W.WORK_ORDER_ID = W1.WORK_ORDER_ID AND P.PERSON_ID = W.WO_CONID AND O.ROLEUSER = ( SELECT O1.ROLEUSER_ALT FROM PS_ROLEXLATOPR O1 WHERE E.SUBMISSION_DATE BETWEEN O1.EFFDT_FROM AND O1.EFFDT_TO) GROUP BY O.ROLEUSER , E.SHEET_ID , X.BUSINESS_UNIT , B.DESCRSHORT , X.WORK_ORDER_ID , W.WO_NAME , X.DISTRIB_LINE_NUM , E.PERSON_ID, P.NAME, E.EXP_HDR_STATUS , E.CREATION_DT , L.CURRENCY_CD , L.CURRENCY_CD_BASE |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | USER_ID |
Character(30) | VARCHAR2(30) NOT NULL | User ID |
| 2 | SHEET_ID |
Character(10) | VARCHAR2(10) NOT NULL | Report ID |
| 3 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 4 | DESCRSHORT | Character(10) | VARCHAR2(10) NOT NULL | Short Description |
| 5 | WORK_ORDER_ID | Character(15) | VARCHAR2(15) NOT NULL | Work Order ID |
| 6 | WO_NAME | Character(30) | VARCHAR2(30) NOT NULL | Work Order Name |
| 7 | DISTRIB_LINE_NUM | Number(5,0) | INTEGER NOT NULL | Distribution Line |
| 8 | PERSON_ID | Character(15) | VARCHAR2(15) NOT NULL | Person ID |
| 9 | NAME | Character(50) | VARCHAR2(50) NOT NULL | Name |
| 10 | 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 |
| 11 | CREATION_DT | Date(10) | DATE | Creation Date |
| 12 | SPF_EXPENSE_AMT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Total expense amount. |
| 13 | SPF_EXP_AMT_BSE | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Total expense amount for base currency |
| 14 | CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Currency Code |
| 15 | CURRENCY_CD_BASE | Character(3) | VARCHAR2(3) NOT NULL | Business Unit Base Currency |