EX_SHEETRPT_VW (SQL View)	Index Back
Sheet Report View

EX_SHEETRPT_VW

(SQL View)

Sheet Report View

SELECT A.SHEET_ID, B.LINE_NBR, A.EMPLID, A.EMPL_RCD, A.SHEET_STATUS, A.BUSINESS_UNIT_GL, A.CREATION_DT, A.ADVANCE_ID, A.APPROVAL_DT, A.ACCOUNTING_DT, A.POST_STATUS_EX, B.EXPEND_MTHD, C.EXPEND_MTHD_EDIT, C.VENDOR_ID, B.REIMBURSEMENT_CD, B.MONETARY_AMOUNT, B.CURRENCY_CD, B.TXN_AMOUNT, B.TXN_CURRENCY_CD, B.RATE_MULT, B.RATE_DIV, B.RT_TYPE FROM PS_EX_SHEET_HDR A, PS_EX_SHEET_LINE B, PS_EX_EXP_MTHD_TBL C WHERE B.EXPEND_MTHD = C.EXPEND_MTHD AND A.SETID = C.SETID AND C.EFFDT=(SELECT MAX(X.EFFDT) FROM PS_EX_EXP_MTHD_TBL X WHERE X.SETID = C.SETID AND X.EXPEND_MTHD = C.EXPEND_MTHD AND X.EFFDT <= %CurrentDateIn) AND C.EFF_STATUS = 'A' AND B.SHEET_ID = A.SHEET_ID

# 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 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

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

5 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

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

7 CREATION_DT Date(10) DATE Creation Date

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

9 APPROVAL_DT Date(10) DATE Date of Approval

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

11 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

12 EXPEND_MTHD Character(3) VARCHAR2(3) NOT NULL Payment Type

13 EXPEND_MTHD_EDIT Character(3) VARCHAR2(3) NOT NULL Payment Type Edit
EMP=Employee
PPA=Prepaid Airfare
PPD=Prepaid
PPH=Prepaid Hotel
VND=Supplier

14 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier

15 REIMBURSEMENT_CD Character(1) VARCHAR2(1) NOT NULL Deny
N=Nonreimburseable
P=Prepaid
R=Reimburseable

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

17 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code

18 TXN_AMOUNT Signed Number(28,3) DECIMAL(26,3) NOT NULL Transaction Amount

19 TXN_CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Transaction Currency

20 RATE_MULT Signed Number(17,8) DECIMAL(15,8) NOT NULL Rate Multiplier

21 RATE_DIV Number(16,8) DECIMAL(15,8) NOT NULL Rate Divisor

22 RT_TYPE Character(5) VARCHAR2(5) NOT NULL Defines a category of market rates for currency conversion. Some examples of rate types are commercial, average, floating, and historical.

#	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	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID
4	EMPL_RCD	Number(3,0)	SMALLINT NOT NULL	Empl Rcd Nbr
5	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
6	BUSINESS_UNIT_GL	Character(5)	VARCHAR2(5) NOT NULL	GL Business Unit
7	CREATION_DT	Date(10)	DATE	Creation Date
8	ADVANCE_ID	Character(10)	VARCHAR2(10) NOT NULL	Advance ID
9	APPROVAL_DT	Date(10)	DATE	Date of Approval
10	ACCOUNTING_DT	Date(10)	DATE	The accounting entry construction date for a given transaction (a generic field that crosses multiple
11	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
12	EXPEND_MTHD	Character(3)	VARCHAR2(3) NOT NULL	Payment Type
13	EXPEND_MTHD_EDIT	Character(3)	VARCHAR2(3) NOT NULL	Payment Type Edit EMP=Employee PPA=Prepaid Airfare PPD=Prepaid PPH=Prepaid Hotel VND=Supplier
14	VENDOR_ID	Character(10)	VARCHAR2(10) NOT NULL	Vendor Identifier
15	REIMBURSEMENT_CD	Character(1)	VARCHAR2(1) NOT NULL	Deny N=Nonreimburseable P=Prepaid R=Reimburseable
16	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.
17	CURRENCY_CD	Character(3)	VARCHAR2(3) NOT NULL	Currency Code
18	TXN_AMOUNT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Transaction Amount
19	TXN_CURRENCY_CD	Character(3)	VARCHAR2(3) NOT NULL	Transaction Currency
20	RATE_MULT	Signed Number(17,8)	DECIMAL(15,8) NOT NULL	Rate Multiplier
21	RATE_DIV	Number(16,8)	DECIMAL(15,8) NOT NULL	Rate Divisor
22	RT_TYPE	Character(5)	VARCHAR2(5) NOT NULL	Defines a category of market rates for currency conversion. Some examples of rate types are commercial, average, floating, and historical.