EX_TOP_SPD_VW(SQL View) |
Index Back |
|---|---|
Top employee spenderTop employees spenders Used in Query, Pivot Grid and Pagelet |
| SELECT C.EMPLID , D.FIRST_NAME %Concat ' ' %Concat D.LAST_NAME , A.BUSINESS_UNIT_GL , C.DEPTID %Concat '-' %Concat E.DESCR , B.SHEET_ID , A.SHEET_STATUS , A.BUSINESS_PURPOSE , F.DESCR , A.SHEET_NAME , B.TRANS_DT , B.EXPENSE_TYPE , T.DESCR , SUM(B.MONETARY_AMOUNT) , B.CURRENCY_CD FROM PS_EX_SHEET_HDR A , PS_EX_SHEET_LINE B , PS_JOB C , PS_PERSONAL_DATA D , PS_DEPTID_BUGL_VW E , PS_EX_PURPOSE_TBL F , PS_EX_TYPES_TBL T WHERE A.SHEET_ID = B.SHEET_ID AND C.EMPLID = A.EMPLID AND C.EMPLID = D.EMPLID AND C.DEPTID = E.DEPTID AND E.SETID = ( SELECT G.SETID FROM PS_SET_CNTRL_REC G WHERE G.SETCNTRLVALUE = A.BUSINESS_UNIT_GL AND G.REC_GROUP_ID = 'FS_06' AND G.RECNAME = 'DEPTID_BUGL_VW') AND %EffdtCheck(DEPTID_BUGL_VW, E, %CURRENTDATEIN ) AND E.EFF_STATUS = 'A' AND A.SHEET_STATUS NOT IN ('CLS','ESC','DEN','DNA','DNU','PND') AND B.PERSONAL_EXPENSE = 'N' AND %EffdtCheck(JOB, C, %CURRENTDATEIN ) AND C.EFFSEQ=( SELECT MAX(JOB.EFFSEQ) FROM PS_JOB JOB WHERE JOB.EMPLID=C.EMPLID AND JOB.EMPL_RCD=C.EMPL_RCD AND JOB.EFFSEQ=C.EFFSEQ AND JOB.EFFDT<=%CurrentDateIn) AND A.BUSINESS_PURPOSE = F.BUSINESS_PURPOSE AND F.SETID = A.SETID AND %EffdtCheck(EX_PURPOSE_TBL, F, %CURRENTDATEIN ) AND F.EFF_STATUS = 'A' AND B.EXPENSE_TYPE = T.EXPENSE_TYPE AND T.SETID = A.SETID AND %EffdtCheck(EX_TYPES_TBL, T, %CurrentDateIn) AND T.EFF_STATUS = 'A' GROUP BY C.EMPLID , D.FIRST_NAME %Concat ' ' %Concat D.LAST_NAME , A.BUSINESS_UNIT_GL , C.DEPTID %Concat '-' %Concat E.DESCR , B.SHEET_ID , A.SHEET_STATUS, A.BUSINESS_PURPOSE , F.DESCR , A.SHEET_NAME , B.TRANS_DT , B.EXPENSE_TYPE , T.DESCR , B.CURRENCY_CD |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | EMPLID | Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
| 2 | NAME_60 | Character(61) | VARCHAR2(61) NOT NULL | Name |
| 3 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 4 | DESCR_DEPT | Character(45) | VARCHAR2(45) NOT NULL | Deptid + Description |
| 5 | SHEET_ID | Character(10) | VARCHAR2(10) NOT NULL | Report ID |
| 6 | SHEET_STATUS | Character(3) | VARCHAR2(3) NOT NULL |
Report Status
APY=Approved for Payment APZ=Approved For Zero Payment CLS=Closed DEN=Denied DNA=Denied by Approver DNU=Denied by Auditor ESC=Escheated Payment HDA=On Hold, with Approver HDU=On Hold, with Auditor HLD=On Hold MFS=Marked for Submit OPN=Open PAR=Approvals in Process PD=Paid PND=Pending PRO=In Process RAP=Approved SFA=Submission in Process STG=Staged SUB=Submitted for Approval XML=Submitted, Pending Validation |
| 7 | BUSINESS_PURPOSE | Character(5) | VARCHAR2(5) NOT NULL |
Business Purpose
Prompt Table: EX_PURPOSE_TBL |
| 8 | DESCR | Character(30) | VARCHAR2(30) NOT NULL | Description |
| 9 | SHEET_NAME | Character(30) | VARCHAR2(30) NOT NULL | Report Description |
| 10 | TRANS_DT | Date(10) | DATE | Transaction Date |
| 11 | EXPENSE_TYPE | Character(7) | VARCHAR2(7) NOT NULL |
Expense Type
Prompt Table: EX_TYPES_TBL |
| 12 | DESCR1 | Character(30) | VARCHAR2(30) NOT NULL | Descr |
| 13 | MONETARY_AMOUNT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Specifies the monetary amount of a debit or credit in the business unit base currency. Debit entries are positive and credit entries are negative. This amount is only zero if associated with a statistical account. |
| 14 | CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Currency Code |