EX_ED_SHEET_VW

(SQL View)
 Index Back

Endeca Main Expense Report VW

Flattened expense report header, line, and distribution for Endeca Project Discovery.

SELECT DST.SHEET_ID , DST.LINE_NBR , DST.DISTRIB_LINE_NUM , DST.BUSINESS_UNIT , DST.EMPLID , PER.NAME , DST.SHEET_STATUS , XLAT.XLATLONGNAME , DST.CREATION_DT , DST.SUBMISSION_DATE , DST.RESUBMISSION_DT , DST.EXPENSE_TYPE , TYP.DESCR , DST.DST_ACCT_TYPE , CASE WHEN DST.REFERENCE_ID <> ' ' THEN DST.REFERENCE_ID ELSE 'N/A' END , DST.SHEET_NAME , DST.ACCOUNT , %Sql(DEPTID, DST.) , %subrec(CFS16_AN_SBR, DST) , CASE WHEN DST.ACCOUNT <> ' ' THEN ACC.DESCR ELSE ' ' END , CASE WHEN %Sql(DEPTID, DST.) <> ' ' THEN DPT.DESCR ELSE ' ' END , CASE WHEN %Sql(OPERATING_UNIT, DST.) <> ' ' THEN OU.DESCR ELSE ' ' END , CASE WHEN %Sql(PRODUCT, DST.) <> ' ' THEN PRD.DESCR ELSE ' ' END , CASE WHEN %Sql(FUND_CODE, DST.) <> ' ' THEN FC.DESCR ELSE ' ' END , CASE WHEN %Sql(CLASS_FLD, DST.) <> ' ' THEN CLASS.DESCR ELSE ' ' END , CASE WHEN %Sql(PROGRAM_CODE, DST.) <> ' ' THEN PC.DESCR ELSE ' ' END , CASE WHEN %Sql(BUDGET_REF, DST.) <> ' ' THEN BR.DESCR ELSE ' ' END , DST.BUSINESS_UNIT_PC , DST.PROJECT_ID , PRJ.DESCR , DST.ACTIVITY_ID , CASE WHEN DST.RESOURCE_TYPE <> ' ' THEN DST.RESOURCE_TYPE ELSE 'N/A' END , CASE WHEN DST.RESOURCE_CATEGORY <> ' ' THEN DST.RESOURCE_CATEGORY ELSE 'N/A' END , CASE WHEN DST.RESOURCE_SUB_CAT <> ' ' THEN DST.RESOURCE_SUB_CAT ELSE 'N/A' END , DST.ANALYSIS_TYPE , DST.MONETARY_AMOUNT , DST.CURRENCY_CD , DST.GL_DISTRIB_STATUS , DST.BI_DISTRIB_STATUS , DST.ACCOUNTING_DT , CASE WHEN DST.TRAVEL_AUTH_ID <> ' ' THEN DST.TRAVEL_AUTH_ID ELSE ' ' END , DST.TXN_LOCATION , DST.PREF_MRCHNT_FLG , CASE WHEN DST.MERCHANT_CD <> ' ' THEN 'Preferred Merchant' WHEN DST.MERCHANT <> ' ' THEN 'Non-Preferred Merchant' ELSE 'N/A' END , CASE WHEN DST.MERCHANT_CD <> ' ' THEN DST.MERCHANT_CD ELSE 'N/A' END , DST.MERCHANT , CASE WHEN DST.DESCR60 <> ' ' THEN DST.DESCR60 ELSE 'N/A' END , CASE WHEN DST.DESCR254 <> ' ' THEN DST.DESCR254 ELSE 'N/A' END , CASE WHEN DST.EE_LOCAMT_COMMENT <> ' ' THEN DST.EE_LOCAMT_COMMENT ELSE 'N/A' END , CASE WHEN DST.EE_NORCPT_COMMENT <> ' ' THEN DST.EE_NORCPT_COMMENT ELSE 'N/A' END , CASE WHEN DST.EE_PRFMRC_COMMENT <> ' ' THEN DST.EE_PRFMRC_COMMENT ELSE 'N/A' END , CASE WHEN DST.EE_DPLCAT_COMMENT <> ' ' THEN DST.EE_DPLCAT_COMMENT ELSE 'N/A' END , CASE WHEN DST.EE_OLDTXN_COMMENT <> ' ' THEN DST.EE_OLDTXN_COMMENT ELSE 'N/A' END , DST.OUT_OF_POLICY , DST.NO_RECEIPT_FLG , DST.PREF_MRCH_NOT_USED , DST.DUPLICATES_EXIST , DST.OLDER_TRANSACTION , DST.RISK_EXISTS_FLG , DST.BILLING_ACTION , DST.SETID , DST.APPROVE_FLAG , DST.COMMENTS , DST.LASTUPDDTTM FROM PS_EX_SHEETDIST_VW DST LEFT OUTER JOIN PS_GL_ACCT_ALL_VW ACC ON DST.SETID_ACCOUNT = ACC.SETID AND DST.ACCOUNT = ACC.ACCOUNT LEFT OUTER JOIN PS_DEPT_ALL_VW DPT ON DST.SETID_DEPT = DPT.SETID AND %Sql(DEPTID, DST.) = %Sql(DEPTID, DPT.) LEFT OUTER JOIN PS_OPERUNIT_ALL_VW OU ON DST.SETID_OPER_UNIT = OU.SETID AND %Sql(OPERATING_UNIT, DST.) = %Sql(OPERATING_UNIT, OU.) LEFT OUTER JOIN PS_PROD_ALL_VW PRD ON DST.SETID1 = PRD.SETID AND %Sql(PRODUCT, DST.) = %Sql(PRODUCT, PRD.) LEFT OUTER JOIN PS_FUND_ALL_VW FC ON DST.SETID2 = FC.SETID AND %Sql(FUND_CODE, DST.) = %Sql(FUND_CODE, FC.) LEFT OUTER JOIN PS_CLASS_CF_ALL_VW CLASS ON DST.SETID3 = CLASS.SETID AND %Sql(CLASS_FLD, DST.) = %Sql(CLASS_FLD, CLASS.) LEFT OUTER JOIN PS_PROGRAM_ALL_VW PC ON DST.SETID4 = PC.SETID AND %Sql(PROGRAM_CODE, DST.) = %Sql(PROGRAM_CODE, PC.) LEFT OUTER JOIN PS_BUDREF_ALL_VW BR ON DST.SETID5 = BR.SETID AND %Sql(BUDGET_REF, DST.) = %Sql(BUDGET_REF, BR.) , XLATTABLE_VW XLAT , PS_PERSONAL_DATA PER , PS_PROJECT PRJ , PS_EX_TYPES_TBL TYP , PS_EID_ETL_LASTRUN LR WHERE XLAT.FIELDNAME = 'SHEET_STATUS' AND XLAT.FIELDVALUE = DST.SHEET_STATUS AND PER.EMPLID = DST.EMPLID AND PRJ.BUSINESS_UNIT = DST.BUSINESS_UNIT_PC AND PRJ.PROJECT_ID = DST.PROJECT_ID AND LR.EID_DS_ID = 'Expenses' AND DST.LASTUPDDTTM >= LR.LAST_RUN_DTM AND DST.EXPENSE_TYPE = TYP.EXPENSE_TYPE AND DST.SETID = TYP.SETID AND TYP.EFFDT=( SELECT MAX(X.EFFDT) FROM PS_EX_TYPES_TBL X WHERE X.SETID = TYP.SETID AND X.EXPENSE_TYPE = TYP.EXPENSE_TYPE AND X.EFFDT <= %CurrentDateIn)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 SHEET_ID Character(10) VARCHAR2(10) NOT NULL Report ID
2 LINE_NBR Number(5,0) INTEGER NOT NULL Line Number: 11/24/08 - Added TARGET label [PC product]
3 DISTRIB_LINE_NUM Number(5,0) INTEGER NOT NULL Distribution Line

Default Value: 1

4 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
5 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
6 NAME Character(50) VARCHAR2(50) NOT NULL Name
7 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
8 SHEET_STATUS_DESCR Character(30) VARCHAR2(30) NOT NULL Sheet Status Description
9 CREATION_DT Date(10) DATE Creation Date
10 SUBMISSION_DATE Date(10) DATE Submission Date
11 RESUBMISSION_DT Date(10) DATE Resubmission Date
12 EXPENSE_TYPE Character(7) VARCHAR2(7) NOT NULL Expense Type
13 EXP_TYPE_DESCR Character(30) VARCHAR2(30) NOT NULL Expense Type
14 DST_ACCT_TYPE Character(4) VARCHAR2(4) NOT NULL 07/20/04 st: added RCN for Advance Reconciliation 02/16/98 ebn CN#CM800-2.0 : Added for MISC type for Landed Cost Enhancement 11/11/99 llr: Added ADVP for Payables Advance Payment

Default Value: DST

15 REFERENCE_ID Character(10) VARCHAR2(10) NOT NULL Reference ID
16 SHEET_NAME Character(30) VARCHAR2(30) NOT NULL Report Description
17 ACCOUNT Character(10) VARCHAR2(10) NOT NULL Account
18 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
19 OPERATING_UNIT Character(8) VARCHAR2(8) NOT NULL Operating Unit ChartField

Prompt Table: %EDIT_OPER_UNIT

20 PRODUCT Character(6) VARCHAR2(6) NOT NULL Product ChartField

Prompt Table: %EDIT_PRODUCT

21 FUND_CODE Character(5) VARCHAR2(5) NOT NULL Fund Code

Prompt Table: %EDIT_FUND_CODE

22 CLASS_FLD Character(5) VARCHAR2(5) NOT NULL Class Field

Prompt Table: %EDIT_CLASS_FLD

23 PROGRAM_CODE Character(5) VARCHAR2(5) NOT NULL Program Code ChartField

Prompt Table: %EDIT_PROG_CODE

24 BUDGET_REF Character(8) VARCHAR2(8) NOT NULL Budget Reference

Prompt Table: %EDIT_BUD_REF

25 AFFILIATE Character(5) VARCHAR2(5) NOT NULL Affiliate

Prompt Table: AFFILIATE_VW

26 AFFILIATE_INTRA1 Character(10) VARCHAR2(10) NOT NULL IntraUnit Affiliate1

Prompt Table: %EDIT_INTRA01

27 AFFILIATE_INTRA2 Character(10) VARCHAR2(10) NOT NULL Operating Unit Affiliate

Prompt Table: %EDIT_INTRA02

28 CHARTFIELD1 Character(10) VARCHAR2(10) NOT NULL Expansion chartfield 1

Prompt Table: %EDIT_CHARTFLD1

29 CHARTFIELD2 Character(10) VARCHAR2(10) NOT NULL Expansion Chartfield 2

Prompt Table: %EDIT_CHARTFLD2

30 CHARTFIELD3 Character(10) VARCHAR2(10) NOT NULL Expansion Chartfield 3

Prompt Table: %EDIT_CHARTFLD3

31 ACCOUNT_DESCR Character(30) VARCHAR2(30) NOT NULL Account Description
32 DEPTID_DESCR Character(30) VARCHAR2(30) NOT NULL Department Description
33 OPER_UNIT_DESCR Character(30) VARCHAR2(30) NOT NULL Operating Unit Description
34 PRODUCT_DESCR Character(30) VARCHAR2(30) NOT NULL Product Description
35 CF_DESCR3 Character(30) VARCHAR2(30) NOT NULL
36 CF_DESCR4 Character(30) VARCHAR2(30) NOT NULL
37 CF_DESCR5 Character(30) VARCHAR2(30) NOT NULL
38 CF_DESCR6 Character(30) VARCHAR2(30) NOT NULL
39 BUSINESS_UNIT_PC Character(5) VARCHAR2(5) NOT NULL PC Business Unit
40 PROJECT_ID Character(15) VARCHAR2(15) NOT NULL Project Id ChartField
41 PROJ_DESCR Character(30) VARCHAR2(30) NOT NULL Project ID Description
42 ACTIVITY_ID Character(15) VARCHAR2(15) NOT NULL Activity ID
43 RESOURCE_TYPE Character(5) VARCHAR2(5) NOT NULL Source Type
44 RESOURCE_CATEGORY Character(5) VARCHAR2(5) NOT NULL Category
45 RESOURCE_SUB_CAT Character(5) VARCHAR2(5) NOT NULL Subcategory
46 ANALYSIS_TYPE Character(3) VARCHAR2(3) NOT NULL Analysis Type
47 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.
48 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
49 GL_DISTRIB_STATUS Character(1) VARCHAR2(1) NOT NULL Distribution Status
C=Available to Contracts
D=Distributed
G=Generated
H=Hold
I=Ignored
J=Creating Journals
M=Modified
N=None
P=To be processed
X=Waiting for Reversal

Default Value: N

50 BI_DISTRIB_STATUS Character(1) VARCHAR2(1) NOT NULL Billing Distribution Status
D=Distributed
I=Ignore
N=Not Distributed
P=Priced
U=Unbillable/Non-billable
W=Billing Worksheet

Default Value: I

51 ACCOUNTING_DT Date(10) DATE The accounting entry construction date for a given transaction (a generic field that crosses multiple
52 TRAVEL_AUTH_ID Character(10) VARCHAR2(10) NOT NULL Travel Authorization Identification. Number used to define travel authorizations. Key field on most travel authorization records and exists at level 0 for travel authorization panels.
53 TXN_LOCATION Character(5) VARCHAR2(5) NOT NULL Expense Location
54 PREF_MRCHNT_FLG Character(1) VARCHAR2(1) NOT NULL Preferred Merchant
N=No
Y=Yes
55 MERCH_TYPE_DESCR Character(30) VARCHAR2(30) NOT NULL Merchandise Type Description
56 MERCHANT_CD Character(10) VARCHAR2(10) NOT NULL Preferred Merchant
57 MERCHANT Character(40) VARCHAR2(40) NOT NULL Merchant
58 DESCR60 Character(60) VARCHAR2(60) NOT NULL Description
59 DESCR254 Character(254) VARCHAR2(254) NOT NULL Description of length 254
60 EE_LOCAMT_COMMENT Character(60) VARCHAR2(60) NOT NULL Location Amount Comment
61 EE_NORCPT_COMMENT Character(60) VARCHAR2(60) NOT NULL No Receipt Comment
62 EE_PRFMRC_COMMENT Character(60) VARCHAR2(60) NOT NULL Preferred Vendor Comment
63 EE_DPLCAT_COMMENT Character(80) VARCHAR2(80) NOT NULL Duplicate Comment
64 EE_OLDTXN_COMMENT Character(60) VARCHAR2(60) NOT NULL Older Transactions Comment
65 OUT_OF_POLICY Character(1) VARCHAR2(1) NOT NULL Authorized Amount Exceeded
66 NO_RECEIPT_FLG Character(1) VARCHAR2(1) NOT NULL No Receipt
67 PREF_MRCH_NOT_USED Character(1) VARCHAR2(1) NOT NULL Non-Preferred Merchant
68 DUPLICATES_EXIST Character(1) VARCHAR2(1) NOT NULL Duplicates Exist
69 OLDER_TRANSACTION Character(1) VARCHAR2(1) NOT NULL Older Transactions
70 RISK_EXISTS_FLG Character(1) VARCHAR2(1) NOT NULL Risk Exists
71 BILLING_ACTION Character(1) VARCHAR2(1) NOT NULL Billing Action
B=Billable
I=Internal
P=Personal
U=Nonbillable
72 SETID Character(5) VARCHAR2(5) NOT NULL SetID
73 APPROVE_FLAG Character(1) VARCHAR2(1) NOT NULL Approve Flag
74 COMMENTS Long Character CLOB Comment
75 LASTUPDDTTM DateTime(26) TIMESTAMP Specifies the date and time of the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.

Default Value: %datetime