EM_BUY_AGE1P_VW (SQL View)	Index Back
Buyer Aging Balance View This view provides the search criteria needed to populate the grids on the EM_BUY_AGING_BAL page for the buyer's aging balance screen.

EM_BUY_AGE1P_VW

(SQL View)

Buyer Aging Balance View

This view provides the search criteria needed to populate the grids on the EM_BUY_AGING_BAL page for the buyer's aging balance screen.

SELECT SEC.OPRID , V.TXN_CURRENCY_CD , RT.TO_CUR , V.VENDOR_ID , VEN.VENDOR_NAME_SHORT , V.BUSINESS_UNIT , BU.DESCRSHORT , RT.RT_RATE_INDEX , RT.RT_TYPE , X.DUE_DT , X.PYMNT_HOLD , 'P' , %Round(%DECMULT((%DECDIV(P.AVAILABLE_AMT, RT.RATE_DIV)), RT.RATE_MULT), C.DECIMAL_POSITIONS) FROM PS_VOUCHER V , PS_VCHR_PPAY_XREF P , PS_PYMNT_VCHR_XREF X , PS_RT_RATE_TBL RT , PS_SEC_BU_OPR SEC , PS_BUS_UNIT_TBL_FS BU , PS_VENDOR VEN , PS_CURRENCY_CD_TBL C WHERE SEC.BUSINESS_UNIT = V.BUSINESS_UNIT AND V.VOUCHER_STYLE = 'PPAY' AND (V.PROCESS_MAN_CLOSE = 'N' OR V.CLOSE_STATUS <> 'C') AND V.VENDOR_SETID = VEN.SETID AND V.VENDOR_ID = VEN.VENDOR_ID AND X.PREPAID_STATUS = 'P' AND P.SEQNUM = 1 AND X.DUE_DT IS NOT NULL AND V.BUSINESS_UNIT = P.BUSINESS_UNIT AND V.VOUCHER_ID = P.VOUCHER_ID AND V.BUSINESS_UNIT = X.BUSINESS_UNIT AND V.VOUCHER_ID = X.VOUCHER_ID AND BU.BUSINESS_UNIT = V.BUSINESS_UNIT AND RT.TERM = 0 AND RT.FROM_CUR = V.TXN_CURRENCY_CD AND RT.EFFDT = ( SELECT MAX(EFFDT) FROM PS_RT_RATE_TBL RT1 WHERE RT1.RT_RATE_INDEX = RT.RT_RATE_INDEX AND RT1.TERM = RT.TERM AND RT1.RT_TYPE = RT.RT_TYPE AND RT1.FROM_CUR = RT.FROM_CUR AND RT1.TO_CUR = RT.TO_CUR AND RT1.EFFDT <= %CurrentDateIn ) AND RT.RATE_DIV <>0 AND C.CURRENCY_CD = RT.TO_CUR AND C.EFF_STATUS = 'A' AND C.EFFDT = ( SELECT MAX(C1.EFFDT) FROM PS_CURRENCY_CD_TBL C1 WHERE C1.CURRENCY_CD = C.CURRENCY_CD AND C1.EFFDT <= %CurrentDateIn)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).

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

3 TO_CUR Character(3) VARCHAR2(3) NOT NULL To Currency Code

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

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

6 EM_BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Buyer

7 EM_BU_NAME_SHORT Character(10) VARCHAR2(10) NOT NULL Buyer Name

8 RT_RATE_INDEX Character(10) VARCHAR2(10) NOT NULL Market Rate Index

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

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

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

12 EM_TYPE Character(1) VARCHAR2(1) NOT NULL Type

13 EM_TOTAL_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL EBPP-Added 2000/10/01-Last Mod 2000/10/19 by Matthias Davet Total amount

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	OPRID	Character(30)	VARCHAR2(30) NOT NULL	A user's ID (see PSOPRDEFN).
2	TXN_CURRENCY_CD	Character(3)	VARCHAR2(3) NOT NULL	Transaction Currency
3	TO_CUR	Character(3)	VARCHAR2(3) NOT NULL	To Currency Code
4	VENDOR_ID	Character(10)	VARCHAR2(10) NOT NULL	Vendor Identifier
5	VENDOR_NAME_SHORT	Character(14)	VARCHAR2(14) NOT NULL	Short Vendor Name
6	EM_BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Buyer
7	EM_BU_NAME_SHORT	Character(10)	VARCHAR2(10) NOT NULL	Buyer Name
8	RT_RATE_INDEX	Character(10)	VARCHAR2(10) NOT NULL	Market Rate Index
9	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.
10	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.
11	PYMNT_HOLD	Character(1)	VARCHAR2(1) NOT NULL	Hold Payment N=No Y=Yes
12	EM_TYPE	Character(1)	VARCHAR2(1) NOT NULL	Type
13	EM_TOTAL_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	EBPP-Added 2000/10/01-Last Mod 2000/10/19 by Matthias Davet Total amount