EX_SHEETDIST_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 , HDR.BUSINESS_UNIT_GL , HDR.EMPLID , HDR.SHEET_STATUS , HDR.CREATION_DT , HDR.SUBMISSION_DATE , HDR.RESUBMISSION_DT , LN.EXPENSE_TYPE , DST.DST_ACCT_TYPE , HDR.REFERENCE_ID , HDR.SHEET_NAME , DST.ACCOUNT , DST.DEPTID , %subrec(CFS16_AN_SBR, DST) , DST.BUSINESS_UNIT_PC , DST.PROJECT_ID , DST.ACTIVITY_ID , DST.RESOURCE_TYPE , DST.RESOURCE_CATEGORY , DST.RESOURCE_SUB_CAT , DST.ANALYSIS_TYPE , DST.MONETARY_AMOUNT , DST.CURRENCY_CD , DST.GL_DISTRIB_STATUS , DST.BI_DISTRIB_STATUS , DST.ACCOUNTING_DT , DST.TRAVEL_AUTH_ID , LN.TXN_LOCATION , LN.PREF_MRCHNT_FLG , LN.MERCHANT_CD , LN.MERCHANT , LN.DESCR60 , LN.DESCR254 , LN.EE_LOCAMT_COMMENT , LN.EE_NORCPT_COMMENT , LN.EE_PRFMRC_COMMENT , LN.EE_DPLCAT_COMMENT , LN.EE_OLDTXN_COMMENT , LN.OUT_OF_POLICY , LN.NO_RECEIPT_FLG , LN.PREF_MRCH_NOT_USED , LN.DUPLICATES_EXIST , LN.OLDER_TRANSACTION , HDR.RISK_EXISTS_FLG , LN.BILLING_ACTION , HDR.SETID , GL.SETID , DEPT.SETID , OPER.SETID , PROD.SETID , FUND.SETID , CLASS.SETID , PROG.SETID , BUD.SETID , LN.APPROVE_FLAG , HDR.COMMENTS , HDR.LASTUPDDTTM FROM PS_EX_SHEET_DIST DST , PS_EX_SHEET_LINE LN , PS_EX_SHEET_HDR HDR , PS_SET_CNTRL_REC GL , PS_SET_CNTRL_REC DEPT , PS_SET_CNTRL_REC OPER , PS_SET_CNTRL_REC PROD , PS_SET_CNTRL_REC FUND , PS_SET_CNTRL_REC CLASS , PS_SET_CNTRL_REC PROG , PS_SET_CNTRL_REC BUD WHERE HDR.SHEET_ID = LN.SHEET_ID AND HDR.SHEET_ID = DST.SHEET_ID AND LN.LINE_NBR = DST.LINE_NBR AND HDR.SHEET_STATUS NOT IN ('DEN', 'DNA', 'DNU', 'MFS', 'PND') AND DST.BUSINESS_UNIT_PC <> ' ' AND DST.PROJECT_ID <> ' ' AND DST.ACTIVITY_ID <> ' ' AND GL.SETCNTRLVALUE = HDR.BUSINESS_UNIT_GL AND GL.RECNAME = 'GL_ACCT_ALL_VW' AND DEPT.SETCNTRLVALUE = HDR.BUSINESS_UNIT_GL AND DEPT.RECNAME = 'DEPT_ALL_VW' AND OPER.SETCNTRLVALUE = HDR.BUSINESS_UNIT_GL AND OPER.RECNAME = 'OPERUNIT_ALL_VW' AND PROD.SETCNTRLVALUE = HDR.BUSINESS_UNIT_GL AND PROD.RECNAME = 'PROD_ALL_VW' AND FUND.SETCNTRLVALUE = HDR.BUSINESS_UNIT_GL AND FUND.RECNAME = 'FUND_ALL_VW' AND CLASS.SETCNTRLVALUE = HDR.BUSINESS_UNIT_GL AND CLASS.RECNAME = 'CLASS_CF_ALL_VW' AND PROG.SETCNTRLVALUE = HDR.BUSINESS_UNIT_GL AND PROG.RECNAME = 'PROGRAM_ALL_VW' AND BUD.SETCNTRLVALUE = HDR.BUSINESS_UNIT_GL AND BUD.RECNAME = 'BUDREF_ALL_VW' AND %DateDiff(HDR.CREATION_DT, %CurrentDateIn) < 1826

# 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 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 CREATION_DT Date(10) DATE Creation Date
8 SUBMISSION_DATE Date(10) DATE Submission Date
9 RESUBMISSION_DT Date(10) DATE Resubmission Date
10 EXPENSE_TYPE Character(7) VARCHAR2(7) NOT NULL Expense Type
11 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

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

Prompt Table: %EDIT_OPER_UNIT

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

Prompt Table: %EDIT_PRODUCT

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

Prompt Table: %EDIT_FUND_CODE

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

Prompt Table: %EDIT_CLASS_FLD

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

Prompt Table: %EDIT_PROG_CODE

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

Prompt Table: %EDIT_BUD_REF

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

Prompt Table: AFFILIATE_VW

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

Prompt Table: %EDIT_INTRA01

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

Prompt Table: %EDIT_INTRA02

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

Prompt Table: %EDIT_CHARTFLD1

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

Prompt Table: %EDIT_CHARTFLD2

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

Prompt Table: %EDIT_CHARTFLD3

28 BUSINESS_UNIT_PC Character(5) VARCHAR2(5) NOT NULL PC Business Unit
29 PROJECT_ID Character(15) VARCHAR2(15) NOT NULL Project Id ChartField
30 ACTIVITY_ID Character(15) VARCHAR2(15) NOT NULL Activity ID
31 RESOURCE_TYPE Character(5) VARCHAR2(5) NOT NULL Source Type
32 RESOURCE_CATEGORY Character(5) VARCHAR2(5) NOT NULL Category
33 RESOURCE_SUB_CAT Character(5) VARCHAR2(5) NOT NULL Subcategory
34 ANALYSIS_TYPE Character(3) VARCHAR2(3) NOT NULL Analysis Type
35 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.
36 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
37 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

38 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

39 ACCOUNTING_DT Date(10) DATE The accounting entry construction date for a given transaction (a generic field that crosses multiple
40 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.
41 TXN_LOCATION Character(5) VARCHAR2(5) NOT NULL Expense Location
42 PREF_MRCHNT_FLG Character(1) VARCHAR2(1) NOT NULL Preferred Merchant
N=No
Y=Yes
43 MERCHANT_CD Character(10) VARCHAR2(10) NOT NULL Preferred Merchant
44 MERCHANT Character(40) VARCHAR2(40) NOT NULL Merchant
45 DESCR60 Character(60) VARCHAR2(60) NOT NULL Description
46 DESCR254 Character(254) VARCHAR2(254) NOT NULL Description of length 254
47 EE_LOCAMT_COMMENT Character(60) VARCHAR2(60) NOT NULL Location Amount Comment
48 EE_NORCPT_COMMENT Character(60) VARCHAR2(60) NOT NULL No Receipt Comment
49 EE_PRFMRC_COMMENT Character(60) VARCHAR2(60) NOT NULL Preferred Vendor Comment
50 EE_DPLCAT_COMMENT Character(80) VARCHAR2(80) NOT NULL Duplicate Comment
51 EE_OLDTXN_COMMENT Character(60) VARCHAR2(60) NOT NULL Older Transactions Comment
52 OUT_OF_POLICY Character(1) VARCHAR2(1) NOT NULL Authorized Amount Exceeded
53 NO_RECEIPT_FLG Character(1) VARCHAR2(1) NOT NULL No Receipt
54 PREF_MRCH_NOT_USED Character(1) VARCHAR2(1) NOT NULL Non-Preferred Merchant
55 DUPLICATES_EXIST Character(1) VARCHAR2(1) NOT NULL Duplicates Exist
56 OLDER_TRANSACTION Character(1) VARCHAR2(1) NOT NULL Older Transactions
57 RISK_EXISTS_FLG Character(1) VARCHAR2(1) NOT NULL Risk Exists
58 BILLING_ACTION Character(1) VARCHAR2(1) NOT NULL Billing Action
B=Billable
I=Internal
P=Personal
U=Nonbillable
59 SETID Character(5) VARCHAR2(5) NOT NULL SetID
60 SETID_ACCOUNT Character(5) VARCHAR2(5) NOT NULL Account Table Setid
61 SETID_DEPT Character(5) VARCHAR2(5) NOT NULL Department SetID
62 SETID_OPER_UNIT Character(5) VARCHAR2(5) NOT NULL Setid for Operating Unit
63 SETID1 Character(5) VARCHAR2(5) NOT NULL SetID Value
64 SETID2 Character(5) VARCHAR2(5) NOT NULL 2nd SetId
65 SETID3 Character(5) VARCHAR2(5) NOT NULL 3rd SetId
66 SETID4 Character(5) VARCHAR2(5) NOT NULL 4th SetId
67 SETID5 Character(5) VARCHAR2(5) NOT NULL 5th SetId
68 APPROVE_FLAG Character(1) VARCHAR2(1) NOT NULL Approve Flag
69 COMMENTS Long Character CLOB Comment
70 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