PASTD_OP_DTL_VW (SQL View)	Index Back
Past Due Itens Past Due Itens

PASTD_OP_DTL_VW

(SQL View)

Past Due Itens

SELECT S.OPRID , I.BUSINESS_UNIT , I.CUST_ID , CASE S.RESP_PART_OPT WHEN 'A' THEN I.AR_SPECIALIST WHEN 'B' THEN I.BROKER_ID WHEN 'R' THEN I.CR_ANALYST WHEN 'S' THEN I.SALES_PERSON ELSE I.COLLECTOR END , I.ITEM , I.ITEM_LINE , I.PYMNT_TERMS_CD , I.SUBCUST_QUAL1 , I.SUBCUST_QUAL2 , I.DUE_DT , %DateDiff(I.DUE_DT, %CurrentDateIn) , I.BAL_AMT , I.BAL_CURRENCY , I.BAL_AMT_BASE , I.CURRENCY_CD , I.DISPUTE_STATUS FROM PS_PASTD_DTL_MVW I , PS_ARFO_PRF_PSTDUE S WHERE ( S.BU_SELECT_FLG = 'A' OR I.BUSINESS_UNIT IN ( SELECT BU.BUSINESS_UNIT FROM PS_ARFO_PRFPSTD_BU BU WHERE BU.OPRID = S.OPRID)) AND ( S.CUST_ID = ' ' OR EXISTS ( SELECT CUST.CUST_ID FROM PS_ARFO_PRFPST_CUS CUST WHERE CUST.OPRID = S.OPRID AND ( CUST.CUSTOMER_SETID = ( SELECT E.SETID FROM PS_AR_BUSET_MVW E WHERE E.SETCNTRLVALUE = I.BUSINESS_UNIT AND E.RECNAME = 'CUSTOMER') AND CUST.CUST_ID = I.CUST_ID )))

# 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 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
Prompt Table: SP_BUARDS_NONVW

3 CUST_ID Character(15) VARCHAR2(15) NOT NULL Uniquely identifies an organizational entity that purchases goods or services from the enterprise. Represents the sold-to customer which is the customer organization that places orders. The CUST_ID may or may not be the same as the identifiers for the bill-to and ship-to locations.

4 RESP_PART Character(15) VARCHAR2(15) NOT NULL Responsible Party

5 ITEM Character(30) VARCHAR2(30) NOT NULL Uniquely identifies an invoice credit memo or debit memo. The item ID is also called the invoice number.

6 ITEM_LINE Number(6,0) INTEGER NOT NULL Item Line

7 PYMNT_TERMS_CD Character(5) VARCHAR2(5) NOT NULL Specifies how the payment due date and discount due date are calculated. A payment terms code is associated with various business units bill-to customers vendors as well as sales orders purchase orders and vouchers.

8 SUBCUST_QUAL1 Character(15) VARCHAR2(15) NOT NULL Specifies a user-defined grouping of customer receivables activity. A subcustomer qualifier provides the ability to record and report receivables activity across multiple business units and/or multiple customers. For example a subcustomer qualifier could represent a sales region or a geographic area. There are two possible subcustomer qualifiers: SUBCUST_QUAL1 and SUBCUST_QUAL2.

9 SUBCUST_QUAL2 Character(15) VARCHAR2(15) NOT NULL Specifies a user-defined grouping of customer receivables activity. A subcustomer qualifier provides the ability to record and report receivables activity across multiple business units and/or multiple customers. For example a subcustomer qualifier could represent a sales region or a geographic area. There are two possible subcustomer qualifiers: SUBCUST_QUAL1 and SUBCUST_QUAL2.

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 DAYS_LATE Signed Number(7,0) DECIMAL(6) NOT NULL Days Late

12 BAL_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Item Balance

13 BAL_CURRENCY Character(3) VARCHAR2(3) NOT NULL Currency Code - Bal Amount

14 BAL_AMT_BASE Signed Number(28,3) DECIMAL(26,3) NOT NULL Balance - Base Currency

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

16 DISPUTE_STATUS Character(3) VARCHAR2(3) NOT NULL Specifies the reason that an item or customer is in dispute. For example the dispute status code might represent pricing error.

#	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	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit Prompt Table: SP_BUARDS_NONVW
3	CUST_ID	Character(15)	VARCHAR2(15) NOT NULL	Uniquely identifies an organizational entity that purchases goods or services from the enterprise. Represents the sold-to customer which is the customer organization that places orders. The CUST_ID may or may not be the same as the identifiers for the bill-to and ship-to locations.
4	RESP_PART	Character(15)	VARCHAR2(15) NOT NULL	Responsible Party
5	ITEM	Character(30)	VARCHAR2(30) NOT NULL	Uniquely identifies an invoice credit memo or debit memo. The item ID is also called the invoice number.
6	ITEM_LINE	Number(6,0)	INTEGER NOT NULL	Item Line
7	PYMNT_TERMS_CD	Character(5)	VARCHAR2(5) NOT NULL	Specifies how the payment due date and discount due date are calculated. A payment terms code is associated with various business units bill-to customers vendors as well as sales orders purchase orders and vouchers.
8	SUBCUST_QUAL1	Character(15)	VARCHAR2(15) NOT NULL	Specifies a user-defined grouping of customer receivables activity. A subcustomer qualifier provides the ability to record and report receivables activity across multiple business units and/or multiple customers. For example a subcustomer qualifier could represent a sales region or a geographic area. There are two possible subcustomer qualifiers: SUBCUST_QUAL1 and SUBCUST_QUAL2.
9	SUBCUST_QUAL2	Character(15)	VARCHAR2(15) NOT NULL	Specifies a user-defined grouping of customer receivables activity. A subcustomer qualifier provides the ability to record and report receivables activity across multiple business units and/or multiple customers. For example a subcustomer qualifier could represent a sales region or a geographic area. There are two possible subcustomer qualifiers: SUBCUST_QUAL1 and SUBCUST_QUAL2.
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	DAYS_LATE	Signed Number(7,0)	DECIMAL(6) NOT NULL	Days Late
12	BAL_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Item Balance
13	BAL_CURRENCY	Character(3)	VARCHAR2(3) NOT NULL	Currency Code - Bal Amount
14	BAL_AMT_BASE	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Balance - Base Currency
15	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. "
16	DISPUTE_STATUS	Character(3)	VARCHAR2(3) NOT NULL	Specifies the reason that an item or customer is in dispute. For example the dispute status code might represent pricing error.