AP_PYMPOVCH_VW

(SQL View)

SELECT DISTINCT A.BANK_SETID , A.BANK_CD , A.BANK_ACCT_KEY , A.SCHEDULE_ID , E.PYMNT_ID_REF , A.PYMNT_ID , A.PYMNT_METHOD , A.BUSINESS_UNIT , A.VOUCHER_ID , C.BUSINESS_UNIT_PO , C.PO_ID , B.VENDOR_SETID , B.VENDOR_ID , D.OPRID_ENTERED_BY , D.OPRID_APPROVED_BY , D.ENTERED_DT , D.APPROVAL_DT , D.BUDGET_HDR_STATUS , D.PO_STATUS FROM PS_PYMNT_VCHR_XREF A , PS_PAYMENT_TBL E , PS_VOUCHER B , PS_PO_HDR D ,PS_VOUCHER_LINE C WHERE E.BANK_SETID = A.BANK_SETID AND E.BANK_CD = A.BANK_CD AND E.BANK_ACCT_KEY = A.BANK_ACCT_KEY AND E.PYMNT_ID = A.PYMNT_ID AND B.BUSINESS_UNIT = A.BUSINESS_UNIT AND B.VOUCHER_ID = A.VOUCHER_ID AND C.BUSINESS_UNIT = A.BUSINESS_UNIT AND C.VOUCHER_ID = A.VOUCHER_ID AND C.BUSINESS_UNIT_PO <> ' ' AND D.BUSINESS_UNIT = C.BUSINESS_UNIT_PO AND D.PO_ID = C.PO_ID

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

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

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

3 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

4 SCHEDULE_ID Character(10) VARCHAR2(10) NOT NULL Schedule ID

5 PYMNT_ID_REF Character(20) VARCHAR2(20) NOT NULL Payment Reference

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

7 PYMNT_METHOD Character(3) VARCHAR2(3) NOT NULL Payment Method
ACH=Automated Clearing House
BEF=Draft - Customer EFT
BOO=Draft - Customer Initiated
CHK=System Check
D=Deposit
DD=Direct Debit
DFT=Draft - Supplier Initiated
DRA=Draft
EFT=Electronic Funds Transfer
GE=Giro - EFT
GM=Giro - Manual
LC=Letter of Credit
MAN=Manual Check
TRW=Treasury Wire
WIR=Wire Transfer

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

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

10 BUSINESS_UNIT_PO Character(5) VARCHAR2(5) NOT NULL PO Business Unit

11 PO_ID Character(10) VARCHAR2(10) NOT NULL Purchase Order

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

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

14 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

15 OPRID_APPROVED_BY Character(30) VARCHAR2(30) NOT NULL Approved By

16 ENTERED_DT Date(10) DATE Entered on

17 APPROVAL_DT Date(10) DATE Date of Approval

18 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

19 PO_STATUS Character(2) VARCHAR2(2) NOT NULL PO Status
A=Approved
C=Complete
D=Dispatched
DA=Denied
I=Initial
LD=Line Denied
O=Open
PA=Pending Approval/Approved
PX=Pending Cancel
X=Canceled

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	BANK_SETID	Character(5)	VARCHAR2(5) NOT NULL	The PeopleSoft tableset ID associated with a given bank/counterparty.
2	BANK_CD	Character(5)	VARCHAR2(5) NOT NULL	Bank Code
3	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
4	SCHEDULE_ID	Character(10)	VARCHAR2(10) NOT NULL	Schedule ID
5	PYMNT_ID_REF	Character(20)	VARCHAR2(20) NOT NULL	Payment Reference
6	PYMNT_ID	Character(10)	VARCHAR2(10) NOT NULL	Payment Number
7	PYMNT_METHOD	Character(3)	VARCHAR2(3) NOT NULL	Payment Method ACH=Automated Clearing House BEF=Draft - Customer EFT BOO=Draft - Customer Initiated CHK=System Check D=Deposit DD=Direct Debit DFT=Draft - Supplier Initiated DRA=Draft EFT=Electronic Funds Transfer GE=Giro - EFT GM=Giro - Manual LC=Letter of Credit MAN=Manual Check TRW=Treasury Wire WIR=Wire Transfer
8	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit
9	VOUCHER_ID	Character(8)	VARCHAR2(8) NOT NULL	Voucher ID
10	BUSINESS_UNIT_PO	Character(5)	VARCHAR2(5) NOT NULL	PO Business Unit
11	PO_ID	Character(10)	VARCHAR2(10) NOT NULL	Purchase Order
12	SETID	Character(5)	VARCHAR2(5) NOT NULL	SetID
13	VENDOR_ID	Character(10)	VARCHAR2(10) NOT NULL	Vendor Identifier
14	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
15	OPRID_APPROVED_BY	Character(30)	VARCHAR2(30) NOT NULL	Approved By
16	ENTERED_DT	Date(10)	DATE	Entered on
17	APPROVAL_DT	Date(10)	DATE	Date of Approval
18	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
19	PO_STATUS	Character(2)	VARCHAR2(2) NOT NULL	PO Status A=Approved C=Complete D=Dispatched DA=Denied I=Initial LD=Line Denied O=Open PA=Pending Approval/Approved PX=Pending Cancel X=Canceled