MTRC_PDUE_VW2D (SQL View)	Index Back
Aging Metrics Details - Drafts

MTRC_PDUE_VW2D

(SQL View)

Aging Metrics Details - Drafts

SELECT V.BUSINESS_UNIT , V.VOUCHER_ID , V.INVOICE_ID , V.INVOICE_DT , ' ' , PAY.DFT_MAT_DT , PAY.PYMNT_AMT , PAY.CURRENCY_PYMNT , ' ' FROM PS_PAYMENT_TBL PAY , PS_PYMNT_VCHR_XREF P , PS_VOUCHER V WHERE PAY.PYMNT_METHOD IN ('BOO', 'BEF', 'DFT') AND PAY.CANCEL_ACTION = 'N' AND PAY.PYMNT_AMT >= 0 AND PAY.DFT_STATUS IN ('A', 'C') AND PAY.BANK_SETID = P.BANK_SETID AND PAY.BANK_CD = P.BANK_CD AND PAY.BANK_ACCT_KEY = P.BANK_ACCT_KEY AND PAY.PYMNT_ID = P.PYMNT_ID AND P.BUSINESS_UNIT = V.BUSINESS_UNIT AND P.VOUCHER_ID = V.VOUCHER_ID

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

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

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

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

4 INVOICE_DT Date(10) DATE Invoice Date

5 APPR_STATUS Character(1) VARCHAR2(1) NOT NULL Approval status flag used on appr_inst_log and derived records to track virtual approver status
A=Approved
D=Denied
P=Pending

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

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

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

9 PYMNT_HOLD Character(1) VARCHAR2(1) NOT NULL Hold Payment
N=No
Y=Yes

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit
2	VOUCHER_ID	Character(8)	VARCHAR2(8) NOT NULL	Voucher ID
3	INVOICE_ID	Character(30)	VARCHAR2(30) NOT NULL	Invoice Number
4	INVOICE_DT	Date(10)	DATE	Invoice Date
5	APPR_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Approval status flag used on appr_inst_log and derived records to track virtual approver status A=Approved D=Denied P=Pending
6	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.
7	PYMNT_GROSS_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Gross Payment Amount
8	TXN_CURRENCY_CD	Character(3)	VARCHAR2(3) NOT NULL	Transaction Currency
9	PYMNT_HOLD	Character(1)	VARCHAR2(1) NOT NULL	Hold Payment N=No Y=Yes