SPA_SHEET_HBUVW (SQL View)	Index Back
Expense Sheet Header View Expense Sheet Header View with BU and WO

SPA_SHEET_HBUVW

(SQL View)

Expense Sheet Header View

Expense Sheet Header View with BU and WO

SELECT B.BUSINESS_UNIT ,A.SHEET_ID ,A.PERSON_ID ,A.EXP_HDR_STATUS ,B.WORK_ORDER_ID ,B.DISTRIB_LINE_NUM ,A.EX_ORIGIN ,A.APPROVAL_LEVEL ,A.CREATION_DT ,A.APPROVAL_OPRID ,A.APPROVAL_DT ,A.AUDIT_DT ,A.AUDIT_OPRID ,A.ADVANCE_AMT ,A.ADVANCE_ID ,A.SHEET_ID_RELATED ,A.SHEET_TYPE ,A.PP_DENIAL_ADV ,A.CREDIT_ADV ,A.SUBMISSION_DATE ,A.RESUBMISSION_DT ,A.LAST_UPDATE_DT ,A.OPRID_LAST_UPDT ,A.RCPT_VERIFY_DT ,A.RCPT_VERIFY_OPRID ,A.RCPT_VERIF_LEVEL ,A.RECEIPTS_RECEIVED ,A.RECEIPT_VERIFIED ,A.DST_CNTRL_ID ,A.REFERENCE_ID ,A.TEMPLATE ,A.SHEET_NAME ,A.TXN_LOCATION ,A.COPY_EXPENSE_SHEET ,A.SETID ,A.BUSINESS_UNIT_GL ,A.EMPL_RCD ,A.RECEIPT_ID ,A.POST_STATUS_EX ,A.ACCOUNTING_DT ,A.PROCESS_INSTANCE ,A.PROCESS_FLAG ,A.UNPOST_SEQ ,A.REVERSAL_DATE ,A.BUSINESS_PURPOSE ,A.APPL_JRNL_ID ,A.DOC_TYPE ,A.DOC_SEQ_NBR ,A.DOC_SEQ_DATE ,A.OPRID_ENTERED_BY ,A.TRAVEL_AUTH_ID ,A.PROJ_MGR_FLAG ,A.OUT_OF_POLICY ,A.VAT_ENTRIES ,A.NO_RECEIPT_FLG ,A.VAT_RECEIPT ,A.TAX_IMPLICATIONS ,A.DUPLICATES_EXIST ,A.PREF_MRCH_NOT_USED ,A.CC_FEED_FLG ,A.BUDGET_HDR_STATUS ,A.KK_TRAN_OVER_FLAG ,A.KK_TRAN_OVER_OPRID ,A.KK_TRAN_OVER_DTTM ,A.KK_AMOUNT_TYPE ,A.COUNTRY ,A.VAT_ENTITY ,A.RCPT_RECEIVED_DT ,A.COMMENTS FROM PS_SPA_SHEET_HDR A , PS_SPA_EXP_XREF B WHERE A.SHEET_ID = B.SHEET_ID

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

2 SHEET_ID Character(10) VARCHAR2(10) NOT NULL Report ID
Default Value: NEXT

3 PERSON_ID Character(15) VARCHAR2(15) NOT NULL Person ID

4 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

5 WORK_ORDER_ID Character(15) VARCHAR2(15) NOT NULL Work Order ID

6 DISTRIB_LINE_NUM Number(5,0) INTEGER NOT NULL Distribution Line

7 EX_ORIGIN Character(1) VARCHAR2(1) NOT NULL Origin of Entry (Expenses)
A=Asset Lifecycle Management
H=Mobile Hand Held Device
M=Mobile Time and Expense
N=NUI
P=Primavera P6
W=Web
Default Value: W

8 APPROVAL_LEVEL Character(1) VARCHAR2(1) NOT NULL Approval Level
A=Approver Only
B=Pre- and Post-Pay Audit
E=Approver & Pre-Pay Auditor
I=Approve, Pre- & Post-Pay Audit
N=None
S=Approver & Post-Pay Auditor
T=Auditor Only - Post-Payment
U=Auditor Only - Pre-Payment

9 CREATION_DT Date(10) DATE Creation Date
Default Value: %date

10 APPROVAL_OPRID Character(30) VARCHAR2(30) NOT NULL A system generated value that reflects the operator that approved a given transaction.

11 APPROVAL_DT Date(10) DATE Date of Approval

12 AUDIT_DT Date(10) DATE Date of Audit

13 AUDIT_OPRID Character(30) VARCHAR2(30) NOT NULL The OPRID of the user who made the changes audited on an audit record.

14 ADVANCE_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Advance Amount

15 ADVANCE_ID Character(10) VARCHAR2(10) NOT NULL Advance ID

16 SHEET_ID_RELATED Character(10) VARCHAR2(10) NOT NULL Journal Report ID

17 SHEET_TYPE Character(1) VARCHAR2(1) NOT NULL Expense Sheet Type
J=Journal Sheet
R=Regular Sheet
Default Value: R

18 PP_DENIAL_ADV Character(10) VARCHAR2(10) NOT NULL Cash advance created from expense items denied on an expense sheet that has already been paid.

19 CREDIT_ADV Character(10) VARCHAR2(10) NOT NULL Credit Advance

20 SUBMISSION_DATE Date(10) DATE Submission Date

21 RESUBMISSION_DT Date(10) DATE Resubmission Date

22 LAST_UPDATE_DT Date(10) DATE Last Updated

23 OPRID_LAST_UPDT Character(30) VARCHAR2(30) NOT NULL Last User to Update

24 RCPT_VERIFY_DT Date(10) DATE Date of Verification

25 RCPT_VERIFY_OPRID Character(30) VARCHAR2(30) NOT NULL Verification UserID

26 RCPT_VERIF_LEVEL Character(1) VARCHAR2(1) NOT NULL Receipt Verification Level - Determines to what level the receipts on expense sheets need to be verified.
B=Both Received & Verified
N=Neither need Receive or Verfy
R=Receipts Received
V=Receipts Verified

27 RECEIPTS_RECEIVED Character(1) VARCHAR2(1) NOT NULL Receipts Received
Y/N Table Edit
Default Value: N

28 RECEIPT_VERIFIED Character(1) VARCHAR2(1) NOT NULL Receipt Verified
Y/N Table Edit
Default Value: N

29 DST_CNTRL_ID Character(10) VARCHAR2(10) NOT NULL Accounting Template

30 REFERENCE_ID Character(10) VARCHAR2(10) NOT NULL Reference ID

31 TEMPLATE Character(8) VARCHAR2(8) NOT NULL Used to define a particular type of travel, i.e. company relocation, internal travel, etc. Determines what type of expenses and expenditure limits are associated with that particular type of travel. Established in the Structure Expenses system and chosen on the header panel of cash advances, expense sheets and travel authorizations.

32 SHEET_NAME Character(30) VARCHAR2(30) NOT NULL Report Description

33 TXN_LOCATION Character(5) VARCHAR2(5) NOT NULL Expense Location
Prompt Table: EX_LOCATION_VW2

34 COPY_EXPENSE_SHEET Character(1) VARCHAR2(1) NOT NULL Copy From Existing Report
Y/N Table Edit
Default Value: N

35 SETID Character(5) VARCHAR2(5) NOT NULL SetID

36 BUSINESS_UNIT_GL Character(5) VARCHAR2(5) NOT NULL GL Business Unit

37 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Rcd Nbr

38 RECEIPT_ID Character(10) VARCHAR2(10) NOT NULL Receipt ID

39 POST_STATUS_EX Character(1) VARCHAR2(1) NOT NULL Expenses Post Status
C=Closed
M=Marked for Unpost
N=Not Applied
O=Unpost In Process
P=Posted
S=Close In Process
U=Unposted
Default Value: N

40 ACCOUNTING_DT Date(10) DATE The accounting entry construction date for a given transaction (a generic field that crosses multiple

41 PROCESS_INSTANCE Number(10,0) DECIMAL(10) NOT NULL Process Instance

42 PROCESS_FLAG Character(1) VARCHAR2(1) NOT NULL Process Flag
A=Purge All
S=Only purge items with at least
Default Value: N

43 UNPOST_SEQ Number(2,0) SMALLINT NOT NULL Identifies the sequence of journal entries when a journal is "unposted". When a journal is posted the UnPost Sequence is automatically set to "0". When a journal is unposted, a new reversing entry is automatically created with an UnPost Sequence of "1".

44 REVERSAL_DATE Date(10) DATE Specifies the date for an automatic reversal journal entry. The date must be within the calendar of the target (posting) ledger. PeopleSoft General Ledger automatically creates and populates a journal entry with this date.

45 BUSINESS_PURPOSE Character(5) VARCHAR2(5) NOT NULL Business Purpose
Prompt Table: EX_PURPOSE_TBL

46 APPL_JRNL_ID Character(10) VARCHAR2(10) NOT NULL Journal Template

47 DOC_TYPE Character(8) VARCHAR2(8) NOT NULL Specifies the business purpose of a financial transaction in countries that require all financial transactions to be tracked as "documents". A Document Type is associated with one and only one Journal Code.

48 DOC_SEQ_NBR Character(12) VARCHAR2(12) NOT NULL Specifies the sequence number assigned to each financial transaction (a document). The sequence number may be manually entered or system-generated.

49 DOC_SEQ_DATE Date(10) DATE Specifies the date that a document sequence number is assigned to a document or the date the document was created.

50 OPRID_ENTERED_BY Character(30) VARCHAR2(30) NOT NULL Entered By 07/25/2011 MRAD 12383033 :Ensured that OPRID_ENTERED_BY is set with format type of MixedCase. 03/22/2013 GL 16482301: Switched OPRID_ENTERED_BY back to MixedCase again. Please don't change it to UpperCase!!! FYI - The alternatives to use a User ID as uppercase: 1) Create your own User ID, add comments in the Field Properties, and fill out the Owner ID 2) Use %Upper meta-SQL in SQL statements 3) Use Upper function in peoplecodes

51 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.

52 PROJ_MGR_FLAG Character(1) VARCHAR2(1) NOT NULL Project Manager Review
Default Value: N

53 OUT_OF_POLICY Character(1) VARCHAR2(1) NOT NULL Authorized Amount Exceeded
Y/N Table Edit
Default Value: N

54 VAT_ENTRIES Character(1) VARCHAR2(1) NOT NULL VAT Items
Y/N Table Edit
Default Value: N

55 NO_RECEIPT_FLG Character(1) VARCHAR2(1) NOT NULL No Receipt
Y/N Table Edit
Default Value: N

56 VAT_RECEIPT Character(1) VARCHAR2(1) NOT NULL No VAT Receipt
Y/N Table Edit
Default Value: N

57 TAX_IMPLICATIONS Character(1) VARCHAR2(1) NOT NULL Items with Tax Implications
Y/N Table Edit
Default Value: N

58 DUPLICATES_EXIST Character(1) VARCHAR2(1) NOT NULL Duplicates Exist
Y/N Table Edit
Default Value: N

59 PREF_MRCH_NOT_USED Character(1) VARCHAR2(1) NOT NULL Non-Preferred Merchant
Y/N Table Edit
Default Value: N

60 CC_FEED_FLG Character(1) VARCHAR2(1) NOT NULL Field used to indicate whether a credit card feed was used to populate an expense sheet or not.
Y/N Table Edit
Default Value: N

61 BUDGET_HDR_STATUS Character(1) VARCHAR2(1) NOT NULL Budget Checking Header Status
E=Error in Budget Check
I=Document In Processing
N=Not Budget Checked
P=Provisionally Valid
V=Valid
Default Value: N

62 KK_TRAN_OVER_FLAG Character(1) VARCHAR2(1) NOT NULL Commitment Control source transaction override flag
N=No
Y=Yes
Y/N Table Edit

63 KK_TRAN_OVER_OPRID Character(30) VARCHAR2(30) NOT NULL Commitment Control source transaction override operator

64 KK_TRAN_OVER_DTTM DateTime(26) TIMESTAMP Commitment Control source transaction override datetime

65 KK_AMOUNT_TYPE Character(1) VARCHAR2(1) NOT NULL Commitment Control Amount Type
0=Budget
1=Actuals and Recognized
2=Encumbrance
3=Pre-Encumbrance
4=Collected Revenue
5=Planned
7=Actuals, Recognize and Collect
9=Dynamic

66 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country

67 VAT_ENTITY Character(20) VARCHAR2(20) NOT NULL Specifies an organizational entity that is responsible for VAT reporting and that is registered in one or more countries that require VAT reporting. A single VAT entity is linked to one or more General Ledger Business Units depending on the reporting requirements.

68 RCPT_RECEIVED_DT DateTime(26) TIMESTAMP Receipts Received Date

69 COMMENTS Long Character CLOB Comment

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit
2	SHEET_ID	Character(10)	VARCHAR2(10) NOT NULL	Report ID Default Value: NEXT
3	PERSON_ID	Character(15)	VARCHAR2(15) NOT NULL	Person ID
4	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
5	WORK_ORDER_ID	Character(15)	VARCHAR2(15) NOT NULL	Work Order ID
6	DISTRIB_LINE_NUM	Number(5,0)	INTEGER NOT NULL	Distribution Line
7	EX_ORIGIN	Character(1)	VARCHAR2(1) NOT NULL	Origin of Entry (Expenses) A=Asset Lifecycle Management H=Mobile Hand Held Device M=Mobile Time and Expense N=NUI P=Primavera P6 W=Web Default Value: W
8	APPROVAL_LEVEL	Character(1)	VARCHAR2(1) NOT NULL	Approval Level A=Approver Only B=Pre- and Post-Pay Audit E=Approver & Pre-Pay Auditor I=Approve, Pre- & Post-Pay Audit N=None S=Approver & Post-Pay Auditor T=Auditor Only - Post-Payment U=Auditor Only - Pre-Payment
9	CREATION_DT	Date(10)	DATE	Creation Date Default Value: %date
10	APPROVAL_OPRID	Character(30)	VARCHAR2(30) NOT NULL	A system generated value that reflects the operator that approved a given transaction.
11	APPROVAL_DT	Date(10)	DATE	Date of Approval
12	AUDIT_DT	Date(10)	DATE	Date of Audit
13	AUDIT_OPRID	Character(30)	VARCHAR2(30) NOT NULL	The OPRID of the user who made the changes audited on an audit record.
14	ADVANCE_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Advance Amount
15	ADVANCE_ID	Character(10)	VARCHAR2(10) NOT NULL	Advance ID
16	SHEET_ID_RELATED	Character(10)	VARCHAR2(10) NOT NULL	Journal Report ID
17	SHEET_TYPE	Character(1)	VARCHAR2(1) NOT NULL	Expense Sheet Type J=Journal Sheet R=Regular Sheet Default Value: R
18	PP_DENIAL_ADV	Character(10)	VARCHAR2(10) NOT NULL	Cash advance created from expense items denied on an expense sheet that has already been paid.
19	CREDIT_ADV	Character(10)	VARCHAR2(10) NOT NULL	Credit Advance
20	SUBMISSION_DATE	Date(10)	DATE	Submission Date
21	RESUBMISSION_DT	Date(10)	DATE	Resubmission Date
22	LAST_UPDATE_DT	Date(10)	DATE	Last Updated
23	OPRID_LAST_UPDT	Character(30)	VARCHAR2(30) NOT NULL	Last User to Update
24	RCPT_VERIFY_DT	Date(10)	DATE	Date of Verification
25	RCPT_VERIFY_OPRID	Character(30)	VARCHAR2(30) NOT NULL	Verification UserID
26	RCPT_VERIF_LEVEL	Character(1)	VARCHAR2(1) NOT NULL	Receipt Verification Level - Determines to what level the receipts on expense sheets need to be verified. B=Both Received & Verified N=Neither need Receive or Verfy R=Receipts Received V=Receipts Verified
27	RECEIPTS_RECEIVED	Character(1)	VARCHAR2(1) NOT NULL	Receipts Received Y/N Table Edit Default Value: N
28	RECEIPT_VERIFIED	Character(1)	VARCHAR2(1) NOT NULL	Receipt Verified Y/N Table Edit Default Value: N
29	DST_CNTRL_ID	Character(10)	VARCHAR2(10) NOT NULL	Accounting Template
30	REFERENCE_ID	Character(10)	VARCHAR2(10) NOT NULL	Reference ID
31	TEMPLATE	Character(8)	VARCHAR2(8) NOT NULL	Used to define a particular type of travel, i.e. company relocation, internal travel, etc. Determines what type of expenses and expenditure limits are associated with that particular type of travel. Established in the Structure Expenses system and chosen on the header panel of cash advances, expense sheets and travel authorizations.
32	SHEET_NAME	Character(30)	VARCHAR2(30) NOT NULL	Report Description
33	TXN_LOCATION	Character(5)	VARCHAR2(5) NOT NULL	Expense Location Prompt Table: EX_LOCATION_VW2
34	COPY_EXPENSE_SHEET	Character(1)	VARCHAR2(1) NOT NULL	Copy From Existing Report Y/N Table Edit Default Value: N
35	SETID	Character(5)	VARCHAR2(5) NOT NULL	SetID
36	BUSINESS_UNIT_GL	Character(5)	VARCHAR2(5) NOT NULL	GL Business Unit
37	EMPL_RCD	Number(3,0)	SMALLINT NOT NULL	Empl Rcd Nbr
38	RECEIPT_ID	Character(10)	VARCHAR2(10) NOT NULL	Receipt ID
39	POST_STATUS_EX	Character(1)	VARCHAR2(1) NOT NULL	Expenses Post Status C=Closed M=Marked for Unpost N=Not Applied O=Unpost In Process P=Posted S=Close In Process U=Unposted Default Value: N
40	ACCOUNTING_DT	Date(10)	DATE	The accounting entry construction date for a given transaction (a generic field that crosses multiple
41	PROCESS_INSTANCE	Number(10,0)	DECIMAL(10) NOT NULL	Process Instance
42	PROCESS_FLAG	Character(1)	VARCHAR2(1) NOT NULL	Process Flag A=Purge All S=Only purge items with at least Default Value: N
43	UNPOST_SEQ	Number(2,0)	SMALLINT NOT NULL	Identifies the sequence of journal entries when a journal is "unposted". When a journal is posted the UnPost Sequence is automatically set to "0". When a journal is unposted, a new reversing entry is automatically created with an UnPost Sequence of "1".
44	REVERSAL_DATE	Date(10)	DATE	Specifies the date for an automatic reversal journal entry. The date must be within the calendar of the target (posting) ledger. PeopleSoft General Ledger automatically creates and populates a journal entry with this date.
45	BUSINESS_PURPOSE	Character(5)	VARCHAR2(5) NOT NULL	Business Purpose Prompt Table: EX_PURPOSE_TBL
46	APPL_JRNL_ID	Character(10)	VARCHAR2(10) NOT NULL	Journal Template
47	DOC_TYPE	Character(8)	VARCHAR2(8) NOT NULL	Specifies the business purpose of a financial transaction in countries that require all financial transactions to be tracked as "documents". A Document Type is associated with one and only one Journal Code.
48	DOC_SEQ_NBR	Character(12)	VARCHAR2(12) NOT NULL	Specifies the sequence number assigned to each financial transaction (a document). The sequence number may be manually entered or system-generated.
49	DOC_SEQ_DATE	Date(10)	DATE	Specifies the date that a document sequence number is assigned to a document or the date the document was created.
50	OPRID_ENTERED_BY	Character(30)	VARCHAR2(30) NOT NULL	Entered By 07/25/2011 MRAD 12383033 :Ensured that OPRID_ENTERED_BY is set with format type of MixedCase. 03/22/2013 GL 16482301: Switched OPRID_ENTERED_BY back to MixedCase again. Please don't change it to UpperCase!!! FYI - The alternatives to use a User ID as uppercase: 1) Create your own User ID, add comments in the Field Properties, and fill out the Owner ID 2) Use %Upper meta-SQL in SQL statements 3) Use Upper function in peoplecodes
51	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.
52	PROJ_MGR_FLAG	Character(1)	VARCHAR2(1) NOT NULL	Project Manager Review Default Value: N
53	OUT_OF_POLICY	Character(1)	VARCHAR2(1) NOT NULL	Authorized Amount Exceeded Y/N Table Edit Default Value: N
54	VAT_ENTRIES	Character(1)	VARCHAR2(1) NOT NULL	VAT Items Y/N Table Edit Default Value: N
55	NO_RECEIPT_FLG	Character(1)	VARCHAR2(1) NOT NULL	No Receipt Y/N Table Edit Default Value: N
56	VAT_RECEIPT	Character(1)	VARCHAR2(1) NOT NULL	No VAT Receipt Y/N Table Edit Default Value: N
57	TAX_IMPLICATIONS	Character(1)	VARCHAR2(1) NOT NULL	Items with Tax Implications Y/N Table Edit Default Value: N
58	DUPLICATES_EXIST	Character(1)	VARCHAR2(1) NOT NULL	Duplicates Exist Y/N Table Edit Default Value: N
59	PREF_MRCH_NOT_USED	Character(1)	VARCHAR2(1) NOT NULL	Non-Preferred Merchant Y/N Table Edit Default Value: N
60	CC_FEED_FLG	Character(1)	VARCHAR2(1) NOT NULL	Field used to indicate whether a credit card feed was used to populate an expense sheet or not. Y/N Table Edit Default Value: N
61	BUDGET_HDR_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Budget Checking Header Status E=Error in Budget Check I=Document In Processing N=Not Budget Checked P=Provisionally Valid V=Valid Default Value: N
62	KK_TRAN_OVER_FLAG	Character(1)	VARCHAR2(1) NOT NULL	Commitment Control source transaction override flag N=No Y=Yes Y/N Table Edit
63	KK_TRAN_OVER_OPRID	Character(30)	VARCHAR2(30) NOT NULL	Commitment Control source transaction override operator
64	KK_TRAN_OVER_DTTM	DateTime(26)	TIMESTAMP	Commitment Control source transaction override datetime
65	KK_AMOUNT_TYPE	Character(1)	VARCHAR2(1) NOT NULL	Commitment Control Amount Type 0=Budget 1=Actuals and Recognized 2=Encumbrance 3=Pre-Encumbrance 4=Collected Revenue 5=Planned 7=Actuals, Recognize and Collect 9=Dynamic
66	COUNTRY	Character(3)	VARCHAR2(3) NOT NULL	Country
67	VAT_ENTITY	Character(20)	VARCHAR2(20) NOT NULL	Specifies an organizational entity that is responsible for VAT reporting and that is registered in one or more countries that require VAT reporting. A single VAT entity is linked to one or more General Ledger Business Units depending on the reporting requirements.
68	RCPT_RECEIVED_DT	DateTime(26)	TIMESTAMP	Receipts Received Date
69	COMMENTS	Long Character	CLOB	Comment