EX_BILL_AN_VW(SQL View) |
Index Back |
|---|---|
EX BILLING ANALYSISPIVOT GRID |
| SELECT A.EMPLID , B.FIRST_NAME %Concat ' ' %Concat B.LAST_NAME , A.BUSINESS_UNIT_GL , G.DEPTID %Concat ' - ' %Concat D.DESCR , A.SHEET_ID , E.DESCR , A.SHEET_NAME , A.SHEET_STATUS , C.TRANS_DT , I.DESCR , F.DESCR , C.MONETARY_AMOUNT , C.CURRENCY_CD , H.DESCR FROM PS_EX_SHEET_HDR A , PS_EX_SHEET_LINE C , PS_EX_SHEET_DIST G , PS_PERSONAL_DATA B , PS_DEPT_TBL D , PS_EX_PURPOSE_TBL E , PS_EX_TYPES_TBL F , PS_EX_EXP_MTHD_TBL H , PS_EX_BILL_CD_TBL I WHERE A.SHEET_ID = C.SHEET_ID AND C.SHEET_ID = G.SHEET_ID AND C.LINE_NBR = G.LINE_NBR AND A.SHEET_STATUS NOT IN ('PND','CLS','ESC','DEN','DNA','DNU') AND B.EMPLID = A.EMPLID AND D.SETID = ( SELECT S.SETID FROM PS_SET_CNTRL_REC S WHERE S.SETCNTRLVALUE = G.BUSINESS_UNIT_GL AND S.REC_GROUP_ID = 'FS_06' AND S.RECNAME = 'DEPT_TBL') AND D.DEPTID = G.DEPTID AND %EffdtCheck(DEPT_TBL, D, %CurrentDateIn) AND D.EFF_STATUS = 'A' AND E.SETID = A.SETID AND E.BUSINESS_PURPOSE = A.BUSINESS_PURPOSE AND %EffdtCheck(EX_PURPOSE_TBL, E, %CurrentDateIn) AND E.EFF_STATUS = 'A' AND F.SETID = A.SETID AND F.EXPENSE_TYPE = C.EXPENSE_TYPE AND %EffdtCheck(EX_TYPES_TBL, F, %CURRENTDATEIN) AND F.EFF_STATUS = 'A' AND H.SETID = A.SETID AND H.EXPEND_MTHD = C.EXPEND_MTHD AND %EffdtCheck(EX_EXP_MTHD_TBL, H, %CURRENTDATEIN) AND H.EFF_STATUS = 'A' AND I.SETID = A.SETID AND I.BILL_CODE_EX = C.BILL_CODE_EX AND %EffdtCheck(EX_BILL_CD_TBL, I, %CURRENTDATEIN) AND I.EFF_STATUS = 'A' |
| # | 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 | DESCR | Character(30) | VARCHAR2(30) NOT NULL | Description |
| 7 | SHEET_NAME | Character(30) | VARCHAR2(30) NOT NULL | Report Description |
| 8 | 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 |
| 9 | TRANS_DT | Date(10) | DATE | Transaction Date |
| 10 | DESCR1 | Character(30) | VARCHAR2(30) NOT NULL | Descr |
| 11 | DESCR_EX_TYPE | Character(30) | VARCHAR2(30) NOT NULL | EX Type Description |
| 12 | 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. |
| 13 | CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Currency Code |
| 14 | DESCR2 | Character(30) | VARCHAR2(30) NOT NULL | Descr2 |