ITEM_ITEM_OPRVW (SQL View)	Index Back
Open Items (keyed by Item)

ITEM_ITEM_OPRVW

(SQL View)

Open Items (keyed by Item)

SELECT C.OPRID , I.ITEM , I.ITEM_LINE , I.BUSINESS_UNIT , I.CUST_ID , I.BAL_AMT , I.ACCOUNTING_DT , I.DISC_DT , I.DISC_AMT , I.DISC_DT1 , I.DISC_AMT1 , I.DISC_DAYS , I.ALLOW_DISC , I.BAL_CURRENCY , I.CURRENCY_CD , I.RATE_MULT , I.RATE_DIV , I.DOCUMENT , I.PO_REF , I.ITEM_STATUS , I.VAT_DCLRTN_POINT , I.VAT_RECALC_FLG FROM PS_BUS_UNIT_TBL_FS B , PS_SEC_BU_OPR C , PS_ITEM I , PS_BUS_UNIT_TBL_AR A WHERE I.BUSINESS_UNIT = B.BUSINESS_UNIT AND B.BUSINESS_UNIT = C.BUSINESS_UNIT AND A.BUSINESS_UNIT = C.BUSINESS_UNIT AND ITEM_STATUS = 'O'

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

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

4 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
Prompt Table: SP_BUARED_NONVW

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

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

7 ACCOUNTING_DT Date(10) DATE The accounting entry construction date for a given transaction (a generic field that crosses multiple

8 DISC_DT Date(10) DATE Specifies the date before which payment must be made on an invoice in order to qualify for a discount. This field applies to the first of two possible discounts and is associated with the discount amount specified in DISC_AMT.

9 DISC_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Specifies the discount amount available if payment on an item is made before the discount date. The amount is manually entered or system-generated using the payment terms code. The discount date associated with this amount is specified in DISC_DT. This is the first of two possible discounts associated with an item.

10 DISC_DT1 Date(10) DATE Specifies the date before which payment must be made on an invoice in order to qualify for a discount. This field applies to the second of two possible discounts and is associated with the discount amount specified in DISC_AMT1.

11 DISC_AMT1 Signed Number(28,3) DECIMAL(26,3) NOT NULL Specifies the discount amount available if payment on an item is made before the discount date. The amount is manually entered or system-generated using the payment terms code. The discount date associated with this amount is specified in DISC_DT1. This is the second of two possible discounts associated with an item.

12 DISC_DAYS Number(3,0) SMALLINT NOT NULL Specifies the number of days beyond the discount date during which a discount is valid. This defines a discount "grace period".

13 ALLOW_DISC Character(1) VARCHAR2(1) NOT NULL A flag that specifies whether or not (Y or N) all discounts for the associated item are to be considered earned even if they have expired.

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

15 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code

16 RATE_MULT Signed Number(17,8) DECIMAL(15,8) NOT NULL Rate Multiplier

17 RATE_DIV Number(16,8) DECIMAL(15,8) NOT NULL Rate Divisor
Default Value: 1

18 DOCUMENT Character(30) VARCHAR2(30) NOT NULL Specifies a document used as a reference number for a Receivables item. Document ID is used by the Receivables Update program and the Maintenance Worksheet to "match" related invoices debit memos and credit memos.

19 PO_REF Character(30) VARCHAR2(30) NOT NULL Specifies the purchase order number associated with a receivables item.

20 ITEM_STATUS Character(1) VARCHAR2(1) NOT NULL Item Status
C=Closed
O=Open

21 VAT_DCLRTN_POINT Character(1) VARCHAR2(1) NOT NULL A flag that specifies when VAT information for sales or purchase transaction is recognized for reporting to the appropriate VAT authority. VAT is declared at invoice time, delivery time, accounting date or the time of payment.
A=At Accounting Date
D=At Delivery Time
I=At Invoice Time
P=At Payment Time

22 VAT_RECALC_FLG Character(1) VARCHAR2(1) NOT NULL A flag that indicates whether or not the VAT discount amount is recalculated at payment time. This field is only used if the VAT Calculation Type (VAT_CAL_GROSS_NET) is gross.

#	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	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.
3	ITEM_LINE	Number(6,0)	INTEGER NOT NULL	Item Line
4	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit Prompt Table: SP_BUARED_NONVW
5	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.
6	BAL_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Item Balance
7	ACCOUNTING_DT	Date(10)	DATE	The accounting entry construction date for a given transaction (a generic field that crosses multiple
8	DISC_DT	Date(10)	DATE	Specifies the date before which payment must be made on an invoice in order to qualify for a discount. This field applies to the first of two possible discounts and is associated with the discount amount specified in DISC_AMT.
9	DISC_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Specifies the discount amount available if payment on an item is made before the discount date. The amount is manually entered or system-generated using the payment terms code. The discount date associated with this amount is specified in DISC_DT. This is the first of two possible discounts associated with an item.
10	DISC_DT1	Date(10)	DATE	Specifies the date before which payment must be made on an invoice in order to qualify for a discount. This field applies to the second of two possible discounts and is associated with the discount amount specified in DISC_AMT1.
11	DISC_AMT1	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Specifies the discount amount available if payment on an item is made before the discount date. The amount is manually entered or system-generated using the payment terms code. The discount date associated with this amount is specified in DISC_DT1. This is the second of two possible discounts associated with an item.
12	DISC_DAYS	Number(3,0)	SMALLINT NOT NULL	Specifies the number of days beyond the discount date during which a discount is valid. This defines a discount "grace period".
13	ALLOW_DISC	Character(1)	VARCHAR2(1) NOT NULL	A flag that specifies whether or not (Y or N) all discounts for the associated item are to be considered earned even if they have expired.
14	BAL_CURRENCY	Character(3)	VARCHAR2(3) NOT NULL	Currency Code - Bal Amount
15	CURRENCY_CD	Character(3)	VARCHAR2(3) NOT NULL	Currency Code
16	RATE_MULT	Signed Number(17,8)	DECIMAL(15,8) NOT NULL	Rate Multiplier
17	RATE_DIV	Number(16,8)	DECIMAL(15,8) NOT NULL	Rate Divisor Default Value: 1
18	DOCUMENT	Character(30)	VARCHAR2(30) NOT NULL	Specifies a document used as a reference number for a Receivables item. Document ID is used by the Receivables Update program and the Maintenance Worksheet to "match" related invoices debit memos and credit memos.
19	PO_REF	Character(30)	VARCHAR2(30) NOT NULL	Specifies the purchase order number associated with a receivables item.
20	ITEM_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Item Status C=Closed O=Open
21	VAT_DCLRTN_POINT	Character(1)	VARCHAR2(1) NOT NULL	A flag that specifies when VAT information for sales or purchase transaction is recognized for reporting to the appropriate VAT authority. VAT is declared at invoice time, delivery time, accounting date or the time of payment. A=At Accounting Date D=At Delivery Time I=At Invoice Time P=At Payment Time
22	VAT_RECALC_FLG	Character(1)	VARCHAR2(1) NOT NULL	A flag that indicates whether or not the VAT discount amount is recalculated at payment time. This field is only used if the VAT Calculation Type (VAT_CAL_GROSS_NET) is gross.