SII_LN_ARP_VW (SQL View)	Index Back
SII_LN_ARP_VW

SII_LN_ARP_VW

(SQL View)

SII_LN_ARP_VW

SELECT DISTINCT A.BUSINESS_UNIT , A.CUST_ID , A.ITEM , A.ITEM_LINE , A.ITEM_SEQ_NUM , A.VAT_SEQ_NUM , A.TAX_CD_VAT ,A.TAX_CD_VAT_PCT ,A.VAT_APPLICABILITY ,(CASE A.VAT_RVRSE_CHG_GDS WHEN ' ' THEN 'N' ELSE A.VAT_RVRSE_CHG_GDS END) ,A.VAT_TXN_TYPE_CD ,B.ACCOUNTING_DT , A.VAT_AMT_I , A.VAT_AMT_BASE_I , A.VAT_BASIS_AMT , A.VAT_BASIS_AMT_BASE , A.ENTRY_CURRENCY , A.CURRENCY_CD , B.rt_type , B.GROUP_ID , B.GrOUP_seq_num , A.vat_entity , S.SETID , B.ACCOUNTING_DT FROM PS_ITEM_ACT_VAT A , PS_ITEM_ACTIVITY B ,PS_ITEM I ,PS_SET_CNTRL_REC S WHERE A.VAT_DISTRIB_STATUS <> 'I' AND A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.CUST_ID = B.CUST_ID AND A.ITEM = B.ITEM AND A.ITEM_LINE = B.ITEM_LINE AND A.ITEM_SEQ_NUM = B.ITEM_SEQ_NUM AND I.BUSINESS_UNIT = B.BUSINESS_UNIT AND I.CUST_ID = B.CUST_ID AND I.ITEM = B.ITEM AND I.ITEM_LINE = B.ITEM_LINE AND I.VAT_DCLRTN_POINT = 'P' AND S.SETCNTRLVALUE = A.BUSINESS_UNIT AND S.RECNAME = 'CUSTOMER' AND B.ENTRY_USE_ID IN ('IT-01', 'IT-02')

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

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

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

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

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

5 ITEM_SEQ_NUM Number(3,0) SMALLINT NOT NULL Item Sequence Number

6 VAT_SEQ_NUM Number(6,0) INTEGER NOT NULL VAT Sequence Number

7 TAX_CD_VAT Character(8) VARCHAR2(8) NOT NULL Defines the VAT (Value Added Tax) percentage (via link to the Tax Authority) and is used to retrieve VAT accounting ChartFields. The VAT code determines how the VAT amount is calculated on a transaction and how that amount is accounted and reported for.

8 TAX_CD_VAT_PCT Signed Number(9,4) DECIMAL(7,4) NOT NULL Specifies the tax percentage that corresponds to the VAT code. If more than one VAT authority is linked to a VAT code this will represent an aggregate percentage.

9 VAT_APPLICABILITY Character(1) VARCHAR2(1) NOT NULL A flag that indicates whether or not a transaction is eligible for VAT (Value Added Tax) taxation or why it is not eligible.
E=Exempt
N=Not Applicable
O=Outside of Scope of VAT
S=Suspended
T=Taxable
V=VAT Only
X=Exonerated

10 VAT_RVRSE_CHG_GDS Character(1) VARCHAR2(1) NOT NULL VAT Reverse Charge goods flag

11 VAT_TXN_TYPE_CD Character(4) VARCHAR2(4) NOT NULL Specifies a user-defined category of business transaction that is subject to VAT accounting and reporting. The VAT code and the VAT transaction type are used in conjunction with the VAT account type to obtain the ChartFields for accounting entries. Some examples of VAT Transaction Types are Exempt Sales Exempt Purchases Triangulation EU Sales and Domestic Sales.

12 INVOICE_DT Date(10) DATE Invoice Date

13 TOT_VAT Signed Number(28,3) DECIMAL(26,3) NOT NULL Total VAT Amount

14 TOT_VAT_BASIS_XEU Signed Number(28,3) DECIMAL(26,3) NOT NULL Total VAT Basis Amount

15 TOT_VAT_BASIS Signed Number(28,3) DECIMAL(26,3) NOT NULL Total VAT Basis Amount

16 TOT_VAT_BASIS_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Total VAT Basis Amount

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

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

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

20 GROUP_ID Character(15) VARCHAR2(15) NOT NULL Uniquely identifies a collection of pending items that are to be posted to accounts receivable. The identifier is manually entered or automatically generated by the system.

21 GROUP_SEQ_NUM Number(6,0) INTEGER NOT NULL Specifies the system-assigned sequence number of a pending item within a group. A group is a postable entity that may be entered online or created by the system. In either case the group sequence number is automatically generated.

22 VAT_ENTITY Character(20) VARCHAR2(20) NOT NULL Specifies an organizational entity that is responsible for VAT reporting and that is registered in one or more countries that require VAT reporting. A single VAT entity is linked to one or more General Ledger Business Units depending on the reporting requirements.

23 CUST_SETID Character(5) VARCHAR2(5) NOT NULL Customer SetID

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

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit
2	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.
3	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.
4	ITEM_LINE	Number(6,0)	INTEGER NOT NULL	Item Line
5	ITEM_SEQ_NUM	Number(3,0)	SMALLINT NOT NULL	Item Sequence Number
6	VAT_SEQ_NUM	Number(6,0)	INTEGER NOT NULL	VAT Sequence Number
7	TAX_CD_VAT	Character(8)	VARCHAR2(8) NOT NULL	Defines the VAT (Value Added Tax) percentage (via link to the Tax Authority) and is used to retrieve VAT accounting ChartFields. The VAT code determines how the VAT amount is calculated on a transaction and how that amount is accounted and reported for.
8	TAX_CD_VAT_PCT	Signed Number(9,4)	DECIMAL(7,4) NOT NULL	Specifies the tax percentage that corresponds to the VAT code. If more than one VAT authority is linked to a VAT code this will represent an aggregate percentage.
9	VAT_APPLICABILITY	Character(1)	VARCHAR2(1) NOT NULL	A flag that indicates whether or not a transaction is eligible for VAT (Value Added Tax) taxation or why it is not eligible. E=Exempt N=Not Applicable O=Outside of Scope of VAT S=Suspended T=Taxable V=VAT Only X=Exonerated
10	VAT_RVRSE_CHG_GDS	Character(1)	VARCHAR2(1) NOT NULL	VAT Reverse Charge goods flag
11	VAT_TXN_TYPE_CD	Character(4)	VARCHAR2(4) NOT NULL	Specifies a user-defined category of business transaction that is subject to VAT accounting and reporting. The VAT code and the VAT transaction type are used in conjunction with the VAT account type to obtain the ChartFields for accounting entries. Some examples of VAT Transaction Types are Exempt Sales Exempt Purchases Triangulation EU Sales and Domestic Sales.
12	INVOICE_DT	Date(10)	DATE	Invoice Date
13	TOT_VAT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Total VAT Amount
14	TOT_VAT_BASIS_XEU	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Total VAT Basis Amount
15	TOT_VAT_BASIS	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Total VAT Basis Amount
16	TOT_VAT_BASIS_BSE	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Total VAT Basis Amount
17	TXN_CURRENCY_CD	Character(3)	VARCHAR2(3) NOT NULL	Transaction Currency
18	CURRENCY_CD	Character(3)	VARCHAR2(3) NOT NULL	Currency Code
19	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.
20	GROUP_ID	Character(15)	VARCHAR2(15) NOT NULL	Uniquely identifies a collection of pending items that are to be posted to accounts receivable. The identifier is manually entered or automatically generated by the system.
21	GROUP_SEQ_NUM	Number(6,0)	INTEGER NOT NULL	Specifies the system-assigned sequence number of a pending item within a group. A group is a postable entity that may be entered online or created by the system. In either case the group sequence number is automatically generated.
22	VAT_ENTITY	Character(20)	VARCHAR2(20) NOT NULL	Specifies an organizational entity that is responsible for VAT reporting and that is registered in one or more countries that require VAT reporting. A single VAT entity is linked to one or more General Ledger Business Units depending on the reporting requirements.
23	CUST_SETID	Character(5)	VARCHAR2(5) NOT NULL	Customer SetID
24	ACCOUNTING_DT	Date(10)	DATE	The accounting entry construction date for a given transaction (a generic field that crosses multiple