POS_EXPENSES_VW (SQL View)	Index Back
Position Expenses View View to show cashflows created by expenses. Used for the position monitor. The EX_SHJEET_PYMNT table is used as source.

POS_EXPENSES_VW

(SQL View)

Position Expenses View

View to show cashflows created by expenses. Used for the position monitor. The EX_SHJEET_PYMNT table is used as source.

Select SHEET_ID, LINE_NBR, EX_PYMNT_TYPE, CANCEL_ACTION, CANCEL_DT, EMPLID, APPROVAL_DT, DUE_DT, SCHEDULED_PAY_DT, STTLMNT_DT_EST, PREFERRED_LANGUAGE, BUSINESS_UNIT_GL, SOURCE_TXN, INVOICE_ID, VENDOR_ID, VENDOR_SETID, VNDR_LOC, VENDOR_NAME_SHORT, REMIT_SETID, REMIT_VENDOR, PYMNT_GROSS_AMT, GROSS_AMT_BSE, BASE_CURRENCY, TXN_CURRENCY_CD, CURRENCY_CD from PS_EX_SHEET_PYMNT A where PYMNT_SELCT_STATUS = 'N' and SEQ_NBR = ( Select Max(SEQ_NBR) from PS_EX_SHEET_PYMNT B where B.SHEET_ID = A.SHEET_ID and B.LINE_NBR = A.LINE_NBR )

# 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 EX_PYMNT_TYPE Character(1) VARCHAR2(1) NOT NULL Expenses Payment Type
A=Advance Distribution
E=Employee Payment
V=Supplier Payment

4 CANCEL_ACTION Character(1) VARCHAR2(1) NOT NULL Cancel Action
C=Do Not Reissue/Close Liability
H=Re-Open Voucher(s)/Put on Hold
N=No Cancel Action
P=Processed
R=Re-Open Voucher(s)/Re-Issue
S=Stale Dated Check
W=Escheated

5 CANCEL_DT Date(10) DATE Date Cancelled

6 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

7 APPROVAL_DT Date(10) DATE Date of Approval

8 DUE_DT Date(10) DATE Specifies the date that a transaction is due. It is a generic field used in multiple PeopleSoft applications including Receivables Payables and Purchasing. For example it represents the date that payment is due for a voucher or the date that a scheduled shipment is due to be received.

9 SCHEDULED_PAY_DT Date(10) DATE Scheduled to Pay

10 STTLMNT_DT_EST Date(10) DATE Date in which a deal transaction is estimated to be settled with a counterparty.

11 PREFERRED_LANGUAGE Character(3) VARCHAR2(3) NOT NULL Preferred Language
ARA=Arabic
CFR=Canadian French
CZE=Czech
DAN=Danish
DUT=Dutch
E=English
ENG=English
ESP=Spanish
F=French
FIN=Finnish
FRA=French
GER=German
HUN=Hungarian
INE=International English
ITA=Italian
JPN=Japanese
KOR=Korean
NOR=Norwegian
POL=Polish
POR=Portuguese
RUS=Russian
SVE=Swedish
THA=Thai
UKE=UK English
ZHS=Simplified Chinese
ZHT=Traditional Chinese

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

13 SOURCE_TXN Character(4) VARCHAR2(4) NOT NULL Source Transaction

14 INVOICE_ID Character(30) VARCHAR2(30) NOT NULL Invoice Number

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

16 VENDOR_SETID Character(5) VARCHAR2(5) NOT NULL Vendor SetID

17 VNDR_LOC Character(10) VARCHAR2(10) NOT NULL Vendor Location

18 VENDOR_NAME_SHORT Character(14) VARCHAR2(14) NOT NULL Short Vendor Name

19 REMIT_SETID Character(5) VARCHAR2(5) NOT NULL Remit SetID

20 REMIT_VENDOR Character(10) VARCHAR2(10) NOT NULL Remit Vendor

21 PYMNT_GROSS_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Gross Payment Amount

22 GROSS_AMT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Base Gross Amount

23 BASE_CURRENCY Character(3) VARCHAR2(3) NOT NULL "Specifies the primary currency for a general ledger business unit, and is sometimes referred to as the ""book"" currency. Each business unit has one base currency. which is usually, but not always, the local currency for the organization. Journal entries are posted to a business unit in its base currency. "

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

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

#	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	EX_PYMNT_TYPE	Character(1)	VARCHAR2(1) NOT NULL	Expenses Payment Type A=Advance Distribution E=Employee Payment V=Supplier Payment
4	CANCEL_ACTION	Character(1)	VARCHAR2(1) NOT NULL	Cancel Action C=Do Not Reissue/Close Liability H=Re-Open Voucher(s)/Put on Hold N=No Cancel Action P=Processed R=Re-Open Voucher(s)/Re-Issue S=Stale Dated Check W=Escheated
5	CANCEL_DT	Date(10)	DATE	Date Cancelled
6	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID
7	APPROVAL_DT	Date(10)	DATE	Date of Approval
8	DUE_DT	Date(10)	DATE	Specifies the date that a transaction is due. It is a generic field used in multiple PeopleSoft applications including Receivables Payables and Purchasing. For example it represents the date that payment is due for a voucher or the date that a scheduled shipment is due to be received.
9	SCHEDULED_PAY_DT	Date(10)	DATE	Scheduled to Pay
10	STTLMNT_DT_EST	Date(10)	DATE	Date in which a deal transaction is estimated to be settled with a counterparty.
11	PREFERRED_LANGUAGE	Character(3)	VARCHAR2(3) NOT NULL	Preferred Language ARA=Arabic CFR=Canadian French CZE=Czech DAN=Danish DUT=Dutch E=English ENG=English ESP=Spanish F=French FIN=Finnish FRA=French GER=German HUN=Hungarian INE=International English ITA=Italian JPN=Japanese KOR=Korean NOR=Norwegian POL=Polish POR=Portuguese RUS=Russian SVE=Swedish THA=Thai UKE=UK English ZHS=Simplified Chinese ZHT=Traditional Chinese
12	BUSINESS_UNIT_GL	Character(5)	VARCHAR2(5) NOT NULL	GL Business Unit
13	SOURCE_TXN	Character(4)	VARCHAR2(4) NOT NULL	Source Transaction
14	INVOICE_ID	Character(30)	VARCHAR2(30) NOT NULL	Invoice Number
15	VENDOR_ID	Character(10)	VARCHAR2(10) NOT NULL	Vendor Identifier
16	VENDOR_SETID	Character(5)	VARCHAR2(5) NOT NULL	Vendor SetID
17	VNDR_LOC	Character(10)	VARCHAR2(10) NOT NULL	Vendor Location
18	VENDOR_NAME_SHORT	Character(14)	VARCHAR2(14) NOT NULL	Short Vendor Name
19	REMIT_SETID	Character(5)	VARCHAR2(5) NOT NULL	Remit SetID
20	REMIT_VENDOR	Character(10)	VARCHAR2(10) NOT NULL	Remit Vendor
21	PYMNT_GROSS_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Gross Payment Amount
22	GROSS_AMT_BSE	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Base Gross Amount
23	BASE_CURRENCY	Character(3)	VARCHAR2(3) NOT NULL	"Specifies the primary currency for a general ledger business unit, and is sometimes referred to as the ""book"" currency. Each business unit has one base currency. which is usually, but not always, the local currency for the organization. Journal entries are posted to a business unit in its base currency. "
24	TXN_CURRENCY_CD	Character(3)	VARCHAR2(3) NOT NULL	Transaction Currency
25	CURRENCY_CD	Character(3)	VARCHAR2(3) NOT NULL	Currency Code