|
SELECT
%SUBREC(IST_ARR4_SBR,A)
,A.IST_MEMBER_STATE
,A.SOURCE_BUS_UNIT
,A.RMA_ID
,A.RMA_LINE_NBR
,A.IST_INV_TXN_TMP
,A.IST_INV_BSE_TMP
,A.IST_INV_AMT_TXN
,A.IST_INV_AMT_BSE
,A.UOM_BILL
,A.QTY_BILL
,A.IST_UNIT_PRICE_TXN
,A.IST_UNIT_PRICE_BSE
,A.TXN_CURRENCY_CD
,A.BASE_CURRENCY
,A.RATE_MULT
,A.RATE_DIV
,B.COUNTRY
,B.VAT_RGSTRN_ID
,A.IST_AMOUNT_SRC
FROM PS_IST_ARR4B_TAO A,
PS_CUST_VAT_REG B,
PS_IST_ARR4A_TAO C
WHERE A.CUST_SETID = B.SETID
AND A.CUST_ID = B.CUST_ID
AND A.IST_MEMBER_STATE = B.COUNTRY
AND A.BUSINESS_UNIT = C.BUSINESS_UNIT
AND A.BUSINESS_UNIT_IN = C.BUSINESS_UNIT_IN
AND A.SOURCE_BUS_UNIT = C.SOURCE_BUS_UNIT
AND A.RECEIVER_ID = C.RECEIVER_ID
AND A.RECV_LN_NBR = C.RECV_LN_NBR
|
| # |
PeopleSoft Field Name |
PeopleSoft Field Type |
Database Column Type |
Description |
| 1 |
 BUSINESS_UNIT |
Character(5) |
VARCHAR2(5) NOT NULL |
Business Unit
|
| 2 |
RECEIVER_ID |
Character(10) |
VARCHAR2(10) NOT NULL |
Receiver number
|
| 3 |
RECV_LN_NBR |
Number(5,0) |
INTEGER NOT NULL |
Receipt Line
|
| 4 |
BUSINESS_UNIT_IN |
Character(5) |
VARCHAR2(5) NOT NULL |
Inventory Unit
|
| 5 |
PROCESS_INSTANCE |
Number(10,0) |
DECIMAL(10) NOT NULL |
Process Instance
|
| 6 |
COUNTRY_IST_RPTG |
Character(3) |
VARCHAR2(3) NOT NULL |
IST Reporting Country
|
| 7 |
IST_TXN_SOURCE |
Character(4) |
VARCHAR2(4) NOT NULL |
Intrastat Transaction Source
BILL=Billing Adjustment
IREC=RMA Receipt
IURC=Interunit Receipt
IUSH=Interunit Shipment
MANU=Manual Entry
RECV=Purchase Receipt
RTVR=Receipt Returns
RTVS=Shipment Return
SHIP=Shipment
VCHR=Voucher Adjustment
|
| 8 |
RECEIPT_DTTM |
DateTime(26) |
TIMESTAMP |
Receipt Datetime
|
| 9 |
IST_TXN_DT |
Date(10) |
DATE |
Intrastat Transaction Date
|
| 10 |
COMMODITY_CD_EU |
Character(10) |
VARCHAR2(10) NOT NULL |
EU Commodity Code
|
| 11 |
ITM_SETID |
Character(5) |
VARCHAR2(5) NOT NULL |
Item SetID
|
| 12 |
INV_ITEM_ID |
Character(18) |
VARCHAR2(18) NOT NULL |
Item ID
|
| 13 |
IST_REGION |
Character(4) |
VARCHAR2(4) NOT NULL |
Region of Origin/Destination
|
| 14 |
FREIGHT_TERMS |
Character(10) |
VARCHAR2(10) NOT NULL |
Freight Terms Code
|
| 15 |
IST_DELIVERY_TRM1 |
Character(3) |
VARCHAR2(3) NOT NULL |
Delivery Terms 1
CFR=Cost and Freight
CIF=Cost, Insurance, Freight
CIP=Carriage and Insurance Paid To
CPT=Carriage Paid To
DAF=Delivered at Frontier
DDP=Delivered Duty Paid
DDU=Delivered Duty Unpaid
DEQ=Delivered Ex-Quay
DES=Delivered Ex-Ship
EXW=Ex-Works
FAS=Free Alongside Ship
FCA=Franco Carrier
FOB=Free on Board
XXX=Other Delivery Terms
|
| 16 |
IST_DELIVERY_TRM2 |
Character(1) |
VARCHAR2(1) NOT NULL |
Delivery Terms 2
1=Place in Reporting Country
2=Place in Other EU Country
3=Place in Other Non-EU Country
|
| 17 |
NATURE_OF_TXN1 |
Character(1) |
VARCHAR2(1) NOT NULL |
Nature of Transaction 1
|
| 18 |
NATURE_OF_TXN2 |
Character(1) |
VARCHAR2(1) NOT NULL |
Nature of Transaction 2
|
| 19 |
COUNTRY_IST_ORIGIN |
Character(3) |
VARCHAR2(3) NOT NULL |
Intrastat Country of Origin
|
| 20 |
SHIP_TYPE_ID |
Character(10) |
VARCHAR2(10) NOT NULL |
Ship Via Code
|
| 21 |
IST_TRANSPORT_MODE |
Character(1) |
VARCHAR2(1) NOT NULL |
Mode of Transport for Intrastat Reporting
1=Transport by Sea
2=Transport by Rail
3=Transport by Road
4=Transport by Air
5=Consignments by Post
7=Fixed Transport Installations
8=Transport by Inland Waterway
9=Own Propulsion
|
| 22 |
PORT_AIRPORT |
Character(4) |
VARCHAR2(4) NOT NULL |
Port or Airport
|
| 23 |
STAT_PROCEDURE |
Character(6) |
VARCHAR2(6) NOT NULL |
Statistical Procedure
|
| 24 |
FISCAL_REGIME |
Character(6) |
VARCHAR2(6) NOT NULL |
Fiscal Regime
|
| 25 |
QTY_IST_SUOM |
Number(16,4) |
DECIMAL(15,4) NOT NULL |
Intrastat Quantity
|
| 26 |
UOM_IST |
Character(3) |
VARCHAR2(3) NOT NULL |
Intrastat Unit Of Measure
|
| 27 |
IST_WEIGHT |
Signed Number(17,4) |
DECIMAL(15,4) NOT NULL |
Intrastat Weight
|
| 28 |
UOM_SUPPLMNTRY |
Character(3) |
VARCHAR2(3) NOT NULL |
Supplementary Unit Of Measure
|
| 29 |
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.
|
| 30 |
CUST_SETID |
Character(5) |
VARCHAR2(5) NOT NULL |
Customer SetID
|
| 31 |
TITLE_PASSAGE |
Character(1) |
VARCHAR2(1) NOT NULL |
Title Passage
B=Buyer's Destination-Ship To
I=Intermediate Consignee
S=Seller's Origination-Ship From
|
| 32 |
IST_MEMBER_STATE |
Character(3) |
VARCHAR2(3) NOT NULL |
Country of Cons/Dest
|
| 33 |
SOURCE_BUS_UNIT |
Character(5) |
VARCHAR2(5) NOT NULL |
Source Bus Unit
|
| 34 |
RMA_ID |
Character(10) |
VARCHAR2(10) NOT NULL |
RMA Number
|
| 35 |
RMA_LINE_NBR |
Number(5,0) |
INTEGER NOT NULL |
RMA Line Number
|
| 36 |
IST_INV_TXN_TMP |
Signed Number(30,5) |
DECIMAL(28,5) NOT NULL |
IST Txn Invoice Amt - Temp
|
| 37 |
IST_INV_BSE_TMP |
Signed Number(30,5) |
DECIMAL(28,5) NOT NULL |
IST Bse Invoice Amt - Temp
|
| 38 |
IST_INV_AMT_TXN |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
Invoice Amount - Transaction
|
| 39 |
IST_INV_AMT_BSE |
Signed Number(28,3) |
DECIMAL(26,3) NOT NULL |
Invoice Amount - Base Currency
|
| 40 |
UOM_BILL |
Character(3) |
VARCHAR2(3) NOT NULL |
Bill Unit of Measure
|
| 41 |
QTY_BILL |
Signed Number(17,4) |
DECIMAL(15,4) NOT NULL |
Quantity in Billing UOM
|
| 42 |
IST_UNIT_PRICE_TXN |
Signed Number(30,5) |
DECIMAL(28,5) NOT NULL |
Net Unit Price
|
| 43 |
IST_UNIT_PRICE_BSE |
Signed Number(30,5) |
DECIMAL(28,5) NOT NULL |
Net Unit Price (base)
|
| 44 |
TXN_CURRENCY_CD |
Character(3) |
VARCHAR2(3) NOT NULL |
Transaction Currency
|
| 45 |
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. "
|
| 46 |
RATE_MULT |
Signed Number(17,8) |
DECIMAL(15,8) NOT NULL |
Rate Multiplier
|
| 47 |
RATE_DIV |
Number(16,8) |
DECIMAL(15,8) NOT NULL |
Rate Divisor
|
| 48 |
COUNTRY_VAT_TPRTNR |
Character(3) |
VARCHAR2(3) NOT NULL |
Trading Partner's VAT Registration Country.
**** WARNING **** - This field has label ids of CUSTOMER, VENDOR, BANK. Do NOT delete these label ids. They are used to ensure the correct label is displayed on the common VAT subpages.
|
| 49 |
VAT_RGSTRN_TPRTNR |
Character(20) |
VARCHAR2(20) NOT NULL |
Trading Partner's VAT Registration ID
**** WARNING **** - This field has label ids of CUSTOMER, VENDOR, BANK. Do NOT delete these label ids. They are used to ensure the correct label is displayed on the common VAT subpages.
|
| 50 |
IST_AMOUNT_SRC |
Character(1) |
VARCHAR2(1) NOT NULL |
Intrastat Amount Source
I=Invoice
M=Manual Entry
O=Order
|