PYMNT_UNAPPL_VW

(SQL View)

SELECT A.BUSINESS_UNIT , A.VOUCHER_ID , A.PYMNT_CNT , A.PYMNT_ID , A.BANK_SETID , A.BANK_CD , A.BANK_ACCT_KEY , A.REMIT_SETID , A.REMIT_VENDOR , A.VNDR_LOC , A.PYMNT_GROSS_AMT , A.CURRENCY_PYMNT , A.PREPAID_STATUS , B.BASE_CURRENCY FROM PS_PYMNT_VCHR_XREF A , PS_VOUCHER B WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.VOUCHER_ID = B.VOUCHER_ID AND B.VOUCHER_STYLE = 'REG' AND B.GROSS_AMT > 0 AND A.PYMNT_SELCT_STATUS = 'N' AND EXISTS ( SELECT 'X' FROM PS_VCHR_PPAY_XREF C , PS_VOUCHER D WHERE C.BUSINESS_UNIT = D.BUSINESS_UNIT AND C.VOUCHER_ID = D.VOUCHER_ID AND C.PREPAID_STATUS = 'P' AND B.BUSINESS_UNIT = D.BUSINESS_UNIT AND B.VENDOR_SETID = D.VENDOR_SETID AND B.VENDOR_ID = D.VENDOR_ID AND B.TXN_CURRENCY_CD = D.TXN_CURRENCY_CD)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
Default Value: OPR_DEF_TBL_FS.BUSINESS_UNIT
Prompt Table: SP_BU_AP_NONVW

2 VOUCHER_ID Character(8) VARCHAR2(8) NOT NULL Voucher ID

3 PYMNT_CNT Number(5,0) INTEGER NOT NULL Payments

4 PYMNT_ID Character(10) VARCHAR2(10) NOT NULL Payment Number

5 BANK_SETID Character(5) VARCHAR2(5) NOT NULL The PeopleSoft tableset ID associated with a given bank/counterparty.

6 BANK_CD Character(5) VARCHAR2(5) NOT NULL Bank Code

7 BANK_ACCT_KEY Character(4) VARCHAR2(4) NOT NULL A user defined unique identifier that facilitates the identification of a given account with a given bank

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

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

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

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

12 CURRENCY_PYMNT Character(3) VARCHAR2(3) NOT NULL Payment Currency

13 PREPAID_STATUS Character(1) VARCHAR2(1) NOT NULL Prepaid Status
A=Applied
B=Pending Application
C=Pending Unapply
D=Pending Change
F=Fully Applied
I=Inactive
P=Available Prepayment
R=Restricted
U=Unapplied
X=Cancelled

14 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. "

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit Default Value: OPR_DEF_TBL_FS.BUSINESS_UNIT Prompt Table: SP_BU_AP_NONVW
2	VOUCHER_ID	Character(8)	VARCHAR2(8) NOT NULL	Voucher ID
3	PYMNT_CNT	Number(5,0)	INTEGER NOT NULL	Payments
4	PYMNT_ID	Character(10)	VARCHAR2(10) NOT NULL	Payment Number
5	BANK_SETID	Character(5)	VARCHAR2(5) NOT NULL	The PeopleSoft tableset ID associated with a given bank/counterparty.
6	BANK_CD	Character(5)	VARCHAR2(5) NOT NULL	Bank Code
7	BANK_ACCT_KEY	Character(4)	VARCHAR2(4) NOT NULL	A user defined unique identifier that facilitates the identification of a given account with a given bank
8	REMIT_SETID	Character(5)	VARCHAR2(5) NOT NULL	Remit SetID
9	REMIT_VENDOR	Character(10)	VARCHAR2(10) NOT NULL	Remit Vendor
10	VNDR_LOC	Character(10)	VARCHAR2(10) NOT NULL	Vendor Location
11	PYMNT_GROSS_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Gross Payment Amount
12	CURRENCY_PYMNT	Character(3)	VARCHAR2(3) NOT NULL	Payment Currency
13	PREPAID_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Prepaid Status A=Applied B=Pending Application C=Pending Unapply D=Pending Change F=Fully Applied I=Inactive P=Available Prepayment R=Restricted U=Unapplied X=Cancelled
14	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. "