IST_EDI_VW(SQL View) |
Index Back |
|---|---|
Intrastat Work Doc |
| SELECT 'INTRASTAT', 0, '0', A.PROCESS_INSTANCE, A.DESCR, A.VAT_ENTITY, A.COUNTRY_IST_RPTG, COUNTRY_IST_RGSTRN, A.IST_RGSTRN_ID, A.COUNTRY, A.ADDRESS1, A.ADDRESS2, A.ADDRESS3, A.ADDRESS4, A.CITY, A.COUNTY, A.STATE, A.POSTAL, A.GEO_CODE, A.PHONE, A.FAX, A.CONTACT_NAME, A.IST_FLOW, A.IST_LEVEL_OBLIG, A.RPT_DT, A.RPT_FROM_DT, A.RPT_THRU_DT, A.RPT_TXN_FRM_DT, A.RPT_TXN_THRU_DT, A.IST_REPORT_TYPE, B.IST_DCMNT_ID, B.LINE_NBR, B.COUNTRY_VAT_TPRTNR, B.VAT_RGSTRN_TPRTNR, B.IST_TXN_DT, B.COMMODITY_CD_EU, B.INV_ITEM_ID, B.IST_MEMBER_STATE, B.IST_REGION, B.IST_DELIVERY_TRM1, B.IST_DELIVERY_TRM2, B.NATURE_OF_TXN1, B.NATURE_OF_TXN2, B.COUNTRY_IST_ORIGIN, B.IST_TRANSPORT_MODE, B.PORT_AIRPORT, B.STAT_PROCEDURE, B.FISCAL_REGIME, B.QTY_IST_SUOM, B.UOM_IST, B.NET_MASS, B.SUPPLMNTRY_UNITS, B.UOM_SUPPLMNTRY, B.IST_INV_AMT_RPTG, B.IST_STAT_VAL_RPTG, B.VENDOR_ID, B.CUST_ID, B.IST_CRNCY_RPTG, B.IST_ERR_FLG FROM PS_IST_RPT_INST1 A, PS_IST_RPT_INST2 B WHERE A.PROCESS_INSTANCE = B.PROCESS_INSTANCE AND B.IST_ERR_FLG <> 'Y' |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | ECTRANSID | Character(15) | VARCHAR2(15) NOT NULL | Electronic Commerce Transaction ID. Used in EDI Manager. One of three(3) Key Fields(ECTRANSID, ECTRANSINOUTSW, ECQUEUEINSTANCE) used in Electronic Commerce(EC) Staging Tables. ECTRANSID in EDI Manager identifies a transaction type(like "PO" for Purchase Orders) |
| 2 | ECQUEUEINSTANCE | Number(9,0) | DECIMAL(9) NOT NULL | Electronic Commerce Queue Instance ID. Used in EDI Manager. One of three(3) Key Fields(ECTRANSID, ECTRANSINOUTSW, ECQUEUEINSTANCE) used in Electronic Commerce(EC) Staging Tables. |
| 3 | ECTRANSINOUTSW | Character(1) | VARCHAR2(1) NOT NULL |
Electronic Commerce Inbound/Outbound Switch. Used in EDI Manager. One of three(3) Key Fields(ECTRANSID, ECTRANSINOUTSW, ECQUEUEINSTANCE) used in Electronic Commerce(EC) Staging Tables. The field value is "I" for Inbound Transactions, and "O" for Outbound.
I=Inbound O=Outbound |
| 4 | PROCESS_INSTANCE | Number(10,0) | DECIMAL(10) NOT NULL | Process Instance |
| 5 | DESCR | Character(30) | VARCHAR2(30) NOT NULL | Description |
| 6 | 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. |
| 7 | COUNTRY_IST_RPTG | Character(3) | VARCHAR2(3) NOT NULL | IST Reporting Country |
| 8 | COUNTRY_IST_RGSTRN | Character(3) | VARCHAR2(3) NOT NULL | Intrastat Country Prefix |
| 9 | IST_RGSTRN_ID | Character(20) | VARCHAR2(20) NOT NULL | Intrastat Registration Id |
| 10 | COUNTRY | Character(3) | VARCHAR2(3) NOT NULL | Country |
| 11 | ADDRESS1 | Character(55) | VARCHAR2(55) NOT NULL | Address 1 |
| 12 | ADDRESS2 | Character(55) | VARCHAR2(55) NOT NULL | Address 2 |
| 13 | ADDRESS3 | Character(55) | VARCHAR2(55) NOT NULL | Address 3 |
| 14 | ADDRESS4 | Character(55) | VARCHAR2(55) NOT NULL | Address 4 |
| 15 | CITY | Character(30) | VARCHAR2(30) NOT NULL | City |
| 16 | COUNTY | Character(30) | VARCHAR2(30) NOT NULL | County |
| 17 | STATE | Character(6) | VARCHAR2(6) NOT NULL | State |
| 18 | POSTAL | Character(12) | VARCHAR2(12) NOT NULL | Postal |
| 19 | GEO_CODE | Character(11) | VARCHAR2(11) NOT NULL | Geo Code |
| 20 | PHONE | Character(24) | VARCHAR2(24) NOT NULL | Telephone |
| 21 | FAX | Character(24) | VARCHAR2(24) NOT NULL | Fax Number |
| 22 | CONTACT_NAME | Character(50) | VARCHAR2(50) NOT NULL | The individual contact name associated with a given bank/counterparty. |
| 23 | IST_FLOW | Character(1) | VARCHAR2(1) NOT NULL |
Intrastat Report Flow
A=Arrivals D=Dispatches |
| 24 | IST_LEVEL_OBLIG | Character(1) | VARCHAR2(1) NOT NULL |
Intrastat Level of Obligation
1=Level 1 2=Level 2 3=Level 3 4=Level 4 |
| 25 | RPT_DT | Date(10) | DATE | Report Date |
| 26 | RPT_FROM_DT | Date(10) | DATE | Report Transactions From |
| 27 | RPT_THRU_DT | Date(10) | DATE | Report Thru Date |
| 28 | RPT_TXN_FRM_DT | Date(10) | DATE | Select Transactions From |
| 29 | RPT_TXN_THRU_DT | Date(10) | DATE | Select Transactions Thru |
| 30 | IST_REPORT_TYPE | Character(1) | VARCHAR2(1) NOT NULL |
Report Type
D=Supplementary Declaration O=Other |
| 31 | IST_DCMNT_ID | Number(8,0) | INTEGER NOT NULL | Intrastat Document ID |
| 32 | LINE_NBR | Number(5,0) | INTEGER NOT NULL | Line Number: 11/24/08 - Added TARGET label [PC product] |
| 33 | 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. |
| 34 | 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. |
| 35 | IST_TXN_DT | Date(10) | DATE | Intrastat Transaction Date |
| 36 | COMMODITY_CD_EU | Character(10) | VARCHAR2(10) NOT NULL | EU Commodity Code |
| 37 | INV_ITEM_ID | Character(18) | VARCHAR2(18) NOT NULL | Item ID |
| 38 | IST_MEMBER_STATE | Character(3) | VARCHAR2(3) NOT NULL | Country of Cons/Dest |
| 39 | IST_REGION | Character(4) | VARCHAR2(4) NOT NULL | Region of Origin/Destination |
| 40 | 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 |
| 41 | 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 |
| 42 | NATURE_OF_TXN1 | Character(1) | VARCHAR2(1) NOT NULL | Nature of Transaction 1 |
| 43 | NATURE_OF_TXN2 | Character(1) | VARCHAR2(1) NOT NULL | Nature of Transaction 2 |
| 44 | COUNTRY_IST_ORIGIN | Character(3) | VARCHAR2(3) NOT NULL | Intrastat Country of Origin |
| 45 | 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 |
| 46 | PORT_AIRPORT | Character(4) | VARCHAR2(4) NOT NULL | Port or Airport |
| 47 | STAT_PROCEDURE | Character(6) | VARCHAR2(6) NOT NULL | Statistical Procedure |
| 48 | FISCAL_REGIME | Character(6) | VARCHAR2(6) NOT NULL | Fiscal Regime |
| 49 | QTY_IST_SUOM | Number(16,4) | DECIMAL(15,4) NOT NULL | Intrastat Quantity |
| 50 | UOM_IST | Character(3) | VARCHAR2(3) NOT NULL | Intrastat Unit Of Measure |
| 51 | NET_MASS | Number(6,0) | INTEGER NOT NULL | Net Mass in KG |
| 52 | SUPPLMNTRY_UNITS | Number(6,0) | INTEGER NOT NULL | Supplementary Units |
| 53 | UOM_SUPPLMNTRY | Character(3) | VARCHAR2(3) NOT NULL | Supplementary Unit Of Measure |
| 54 | IST_INV_AMT_RPTG | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Invoice Amount - Reporting |
| 55 | IST_STAT_VAL_RPTG | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Statistical Value - Reporting |
| 56 | VENDOR_ID | Character(10) | VARCHAR2(10) NOT NULL | Vendor Identifier |
| 57 | 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. |
| 58 | IST_CRNCY_RPTG | Character(3) | VARCHAR2(3) NOT NULL | Intrastat Reporting Currency |
| 59 | IST_ERR_FLG | Character(1) | VARCHAR2(1) NOT NULL | Transaction in Error |