(DEU) Working With Electronic Tax Reporting for Germany

This chapter provides an overview of electronic tax reporting , lists prerequisites, and discusses how to:

Create or modify extraction programs.
Run the extraction programs.
Modify the Index.XML file.

Understanding Electronic Tax Reporting for Germany

The German Federal Agency of Finance (Bundesamt für Finanzen) requires businesses operating in Germany to provide electronic access to copies of accounting data according to an audit process detailed in the document Grundsätze zum Datensugriff und zur Prüfbarkeit digitaler Unterlagen (GDPdU). Data from live data processing systems must be extracted and provided to tax auditors on industry-standard digital media such as a CD-ROM or DVD.

JD Edwards EnterpriseOne software provides the tools that enable businesses to extract relevant tax data from JD Edwards EnterpriseOne tables and write the data to text files. You send the text files, along with an index that explains the data and a copy of the DTD (document type definition) that is used with the index, to the tax auditors. Table extraction programs are provided to extract data from the Customer Ledger table (F03B11), the Accounts Payable Ledger table (F0411), the Account Ledger table (F0911), and the Taxes table (F0018). Additionally, an XML-formatted index that you must modify to meet reporting requirements, and a copy of the gdpdu-01–08–2002 DTD are provided on the Oracle | PeopleSoft Customer Connection web site.

Note. Search for the Index.XML and DTD files on the Oracle | PeopleSoft Customer Connection web site by selecting Support > Documentation > EnterpriseOne > Localizations > Germany. Select the document set for your release.

Important! The provided solution might not provide the needed data for every situation. For example, if the tax authority requests information that does not reside in the F03B11, F0411, F0911, or F0018 tables, or you have customized your installation to store tax data in alternate fields in these tables or other tables, you must create your own table conversions to extract data from other JD Edwards EnterpriseOne tables, or modify the data extraction programs provided to alter the output produced by the programs.

You can use the provided data extraction programs as models for new data extraction programs. However, you must refer to official government publications for the requirements of the data and formatting required.

See JD Edwards EnterpriseOne Tools 8.98 Development Tools: Data Access Tools Guide.

To generate and review the data to submit to the tax authority:

Run the data extraction programs to create text files.

The data extraction programs create a file that is semicolon (;) delimited and which has double-quote (“ ) text encapsulation. The system saves the text files to the Export folder for your JD Edwards EnterpriseOne installation. JD Edwards EnterpriseOne software provides data conversion programs for some tables; you might need to create your own programs if you need to report on data that resides in other tables. Additionally, you might need to modify the provided data extraction programs prior to running them to create the text files.

See Running the Data Extraction Programs.
Review the text files as necessary.

You can open the text files in any program that reads .txt files. If you need to add or delete columns in the text files due to customizations, consider modifying the data extraction programs and then run the programs again.
Modify the Index.XML file to include relevant data about the data submission, your company, the dates for which you generated the text files, and records that are not included in the sample Index.XML provided.

You can open and modify the Index.XML file in any program that reads .XML files. However, they are easier to read and modify in an XML editor.

In addition to the information about your company and the reporting dates, you must modify the XML file to include information about additional data if the provided data extraction programs are modified or if you create additional data extraction programs that produce new .txt files.

See Modifying the Index.XML File.
Submit the text files, your modified version of the Index.XML file, and an unmodified version of the gdpdu-01–08–2002 DTD to the tax audit authority on industry-standard digital media such as a CD-ROM or DVD.

This graphic illustrates the process flow for extracting data and submitting files:

Process flow for extracting data and submitting files

Prerequisites

Before completing the tasks in this section:

Verify that an Export folder exists in the installation directory for the version of the software that you use.

If you run the data extraction programs from a FAT client installation on a PC, the Export folder must exist in the installation directory on the PC. For example, your installation directory with the Export folder might be C:\B10\PD10\Export.

If you run the data extraction programs from a web client on a PC or from a server, the Export folder must exist in the installation directory on the server.
Verify that the user running the report has write privileges for the Export folder.

Creating or Modifying Data Extraction Programs

Because your company might have modified the delivered base software, and because the audit authority might request different data from time to time, the software cannot be programmed to determine the exact data that might be required for an audit. The provided solution is meant to provide you with the tools and examples to produce the data and data descriptions that might be required. You must modify the data extraction programs and the .XML file provided to meet the requirements of data reporting. Additionally, you might need to create other data extraction (table conversion) programs to produce required data.

Note. Whenever you make a modification to a data extraction program or create a new program, you change the text files that you submit to the tax authority. Because the Index.XML file describes the text files, you must also modify the Index.XML file to reflect any changes to the text files.

If creating new program or modifying a provided data extraction program, consider these factors:

File formats can be variable length or fixed length.

Variable length fields can be formatted as alphanumeric, numeric, or as a date.

The data extraction programs provided are variable length.
In variable length files, text files can be encapsulated.

The text files produced by the provided data extraction programs are encapsulated with double quotes (“). The text files are semi-colon (;) delimited.
For each table for which you provide data, you must include in the Index.XML file a description of the data, including:
- URL
- Name of the table.
- Description of the table.
- Date range of the data extracted.
- Date format.
- Decimal separator.
- Digit grouping symbol.
  
  This symbol defines the thousand-separator.
- Description of the variable length or fixed length text, including whether the fields are alphanumeric, numeric, or a date.

Refer to the official government publication for details of the data to include in your text and Index.XML files.

See JD Edwards EnterpriseOne Tools 8.98 Development Tools: Data Access Tools Guide.

Running the Data Extraction Programs

This section provides an overview of the extraction programs and discusses how to:

Run the F0018 GDPdU Data Extract program (R89F0018G).
Run the F03B11 GDPdU Data Extract program (R89F03B11G).
Run the F0411 GDPdU Data Extract program (R89F0411G).
Run the F0911 GDPdU Data Extract program (R89F0911G).

Understanding the Data Extraction Programs

Most tax-relevant data resides in the F0911, F0411, F03B11, and F0018 tables. When you run the extraction programs for these tables, the system generates a text file that it writes to the Export folder of the installation directory. The system does not generate a report when you run these programs. The text files are semi-colon (;) delimited with double quote (“) text encapsulation.

Information about transactions that might be subject to review by the tax authority might be written to more than one table. For example, the taxable amount of a transaction (data item ATXA) might be written to both the F0411 and the F0018 tables. The system extracts all data in a table, even when that data is duplicated in another table.

Note. If data requested by the tax authority does not reside in the F0911, F0411, F03B11, or F0018 tables, you must create custom table conversion programs to extract the additional data and modify the Index.XML file to describe the additional data.

Data Selection

The XJDE0001 versions of the R89F03B11G, R89F0411G, and the R89F0018G data extraction programs do not include any data selection. You can use any of the fields that reside in the table for which you generate the text file to specify the records that the system includes in the text file that you generate. At a minimum, specify the dates for which the system selects records to process. Record in the Index.XML file the date range for which you extract data.

The XJDE0001 version of the R89F0911G data extraction program is set to select records with the ledger type of AA. Tax information and Accounts Payable and Accounts Receivable accounting information resides in the AA ledger. As with the other data extraction programs, at a minimum, specify the dates for which the system selects records to process. Record in the Index.XML file the date range for which you extract data.

F0018 GDPdU Data Extraction Program (R89F0018G)

When you run the F0018 GDPdU Data Extraction program, the system generates a text file named F0018GDPDU.txt, and writes the file to the Export directory for your installation. The system writes data for the selected records for the data items listed in this table, in the order listed.

Fields	Description of Values
DOC	Document number
DCT	Document type.
KCO	Document company.
SFX	Document pay item number.
JELN	Line number.
UPMJ	Update date.
UPMT	Update time.
DCT description	Document type value description. The extraction program calls GetUDC (X0005) for 00/DT UDC (user-defined code) and writes the description to the text file.
KCO description	Document company value description. The extraction program calls GetCompanyName (B0000051) and writes the description to the text file.
CO	Company
CO description	Company description The extraction program calls GetCompanyName (B0000051) and writes the description to the text file.
MCU	Business unit.
MCU description	Business unit value description. The extraction program calls IsColumnInBusinessUnitTable (ISCOLBU) and writes the description to the text file.
OBJ	Object
SUB	Subsidiary
AN8	Address number
AN8 description	Address number value description. The extraction program calls GetAddressBookDescriptionA7 (GETABDA7) and writes the description to the text file.
ECST	Amount extended cost.
AEXP	Amount extended price.
ATXA	Amount – taxable.
ATXN	Amount – nontaxable.
NRTA	Amount – nonrecoverable tax.
STAM	Amount – tax.
TXA1	Tax area 1.
TXA1 description	Tax rate/area 1 value description. The extraction program calls EditTaxRateArea (B0000140) and writes the description to the text file.
EXR1	Tax explanation 1.
EXR1 description	Tax explanation 1 value description. The extraction program calls GetUDC (X0005) for the Tax Explanation Code (00/EX) UDC and writes the description to the text file.
EXR2	Tax explanation 2.
EXR2 description	Tax explanation 2 value description. The extraction program calls GetUDC (X0005) for the Tax Explanation Code (00/EX) UDC and writes the description to the text file.
TRDJ	Transaction date.
DGL	Date – G/L date.
DSVJ	Service/tax date.
TX	Taxable Y/N
ITM	Item description.
ITM description.	Item description value description. The extraction program calls GetItemMasterByShortItem (X4101) and writes the description to the text file.
LITM	Second item number.
LITM description	Second item number value description. The extraction program calls GetSecondItemDescription (B4100321) and writes the description to the text file.
AITM	Third item number.
AITM description	Third item number value description. The extraction program calls GetThirdItemDescription (B4100322) and writes the description to the text file.
LNTY	Line type.
LNTY description	Line type value description. The extraction program calls GetLineTypeConstants (B4000260) and writes the description to the text file.
UORG	Units – order/transaction quantity.
UOM	Unit of measure.
UOM description	Unit of measure value description. The extraction program calls GetUDC (X0005) for the Unit of Measure (00/UM) UDC and writes the description to the text file.
FVTY	Factor value – type.
FVTY description.	Factor value – type value description. The extraction program calls GetUDC (X0005) for H00/FV UDC and writes the description to the text file.
TA1	Tax authority 1
TA1 description	Tax authority 1 value description. The extraction program calls GetAddressBookDescriptionA7 (GETABDA7) and writes the description to the text file.
TXR1	Tax rate 1.
TA2	Tax authority 2
TA2 description	Tax authority 2 value description. The extraction program calls GetAddressBookDescriptionA7 (GETABDA7) and writes the description to the text file.
TXR2	Tax rate 2.
TA3	Tax authority 3
TA3 description	Tax authority 3 value description. The extraction program calls GetAddressBookDescriptionA7 (GETABDA7) and writes the description to the text file.
TXR3	Tax rate 3.
TA4	Tax authority 4
TA4 description	Tax authority 4 value description. The extraction program calls GetAddressBookDescriptionA7 (GETABDA7) and writes the description to the text file.
TXR4	Tax rate 4.
TA5	Tax authority 5.
TA5 description	Tax authority 5 value description. The extraction program calls GetAddressBookDescriptionA7 (GETABDA7) and writes the description to the text file.
TXR5	Tax rate 5.
STA1	Tax amount 1.
STA2	Tax amount 2.
STA3	Tax amount 3.
STA4	Tax amount 4.
STA5	Tax amount 5.
OVTX	Tax override.
TXVA	Amount – tax variance.
TXVR	Percent – tax variance.
POST	G/L posted code.
GDVL	Amount – goods value.
CRCD	Currency code – from
CRCD description	Currency code value description The extraction program calls F0013GetCurrencyDescription (B03B0103) and writes the description to the text file.
CRR	Currency conversion rate
SQNO	Sequence number.
AG	Amount – gross.
APD	Amount – paid.
TAXE	Amount – sales tax exempt.
DOC	Document number.
PROCFL	Tax processing flag.
ADJFL	Value-added tax (VAT) adjustment flag.
TOFL	Transaction origin flag.

F03B11 GDPdU Data Extraction Program (R89F03B11G)

When you run the F03B11 GDPdU Data Extraction program, the system generates a text file named F03B11GDPDU.txt, and writes the file to the Export directory for your installation. The system writes data for the selected records for the data items listed in this table, in the order listed.

Fields	Description of Value
DOC	Document number
DCT	Document type.
KCO	Document company.
SFX	Document payitem number.
DCT description	Document type value description. The extraction program calls GetUDC (X0005) for 00/DT UDC and writes the description to the text file.
KCO description	Document company value description. The extraction program calls GetCompanyName (B0000051) and writes the description to the text file.
AN8	Address number
AN8 description	Address number value description. The extraction program calls GetAddressBookDescriptionA7 (GETABDA7) and writes the description to the text file.
DGJ	Date – G/L.
DIVJ	Date – invoice.
FY	Fiscal year
CTRY	Century
PN	Period number
CO	Company
CO description	Company description The extraction program calls GetCompanyName (B0000051) and writes the description to the text file.
PA8	Address number – parent.
PA8 description	Address number – parent value description. The extraction program calls GetAddressBookDescriptionA7 (GETABDA7) and writes the description to the text file.
AN8J	Address number – alternate payee.
AN8J description	Address number – alternate payee value description. The extraction program calls GetAddressBookDescriptionA7 (GETABDA7) and writes the description to the text file.
PYR	Address number – payor.
PYR description	Address number – payor value description. The extraction program calls GetAddressBookDescriptionA7 (GETABDA7) and writes the description to the text file.
POST	G/L posted code
ISTR	A/R post status
PST	Pay status code
PST description	Pay status code value description. The extraction program calls GetUDC (X0005) for the Payment Status (00/PS) UDC and writes the description to the text file.
AG	Amount – gross
AAP	Amount – open
ATXA	Amount – taxable
ATXN	Amount – non-taxable
STAM	Amount – tax
BCRC	Currency code – base.
BCRC description	Currency code – base value description. The extraction program calls F0013GetCurrencyDescription (B03B0103) and writes the description to the text file.
CRCD	Currency code – from
CRCD description	Currency code value description The extraction program calls F0013GetCurrencyDescription (B03B0103) and writes the description to the text file.
CRR	Currency conversion rate
ACR	Amount – foreign
FAP	Amount – foreign open
CTXA	Amount – foreign taxable
CTXN	Amount – non-foreign taxable.
CTAM	Amount – foreign tax.
TXA1	Tax rate/area 1
TXA1 description	Tax rate/area 1 value description. The extraction program calls EditTaxRateArea (B0000140) and writes the description to the text file.
EXR1	Tax explanation.
EXR1 description	Tax explanation value description. The extraction program calls GetUDC (X0005) for the Tax Explanation Code (00/EX) UDC and writes the description to the text file.
DSVJ	Date – service/tax.
GLBA	G/L bank account.
GLBA description	G/L bank account value description. The extraction program calls RetrieveAccountMasterInfoGLPost (B0900104) and writes the description to the text file.
RSCO	Write-off reason code.
RSCO description	Write-off reason code value description. The extraction program calls GetUDC (X0005) for the Reason Code (03B/RC) UDC and writes the description to the text file.
VLDT	Date – cleared/value.
RMK	Name – remark.
ALPH	Name – alpha.
RYIN	Payment instrument.
RYIN description	Payment instrument value description. The extraction program calls GetUDC (X0005) for the Payment Instrument (00/PY) UDC and writes the description to the text file.
VDGJ	Void date.
VOD	Void flag.
JCL	Date – invoice close.
DDEX	Deduction reason code.
DDEX description	Deduction reason code value description. The extraction program calls GetUDC (X0005) for the Deduction Reason Code (03B/CR) UDC and writes the description to the text file.
DTXS	Deferred tax status - VAT.
ATAD	Amount – to distribute.
CTAD	Amount – foreign to distribute.
NRTA	Amount – nonrecoverable tax.
FNRT	Amount – foreign nonrecoverable tax.
SOTF	Sales order tax flag.
ERDJ	Date – for exchange rate.
AJCL	Date – actual invoice closed.

F0411 GDPdU Data Extraction Program (R89F0411G)

When you run the F0411 GDPdU Data Extraction program, the system generates a text file named F0411GDPDU.txt, and writes the file to the Export directory for your installation. The system writes data for the selected records for the data items listed in this table, in the order listed.

Fields	Description of Value
DOC	Document number
DCT	Document type.
KCO	Document company.
SFX	Document payitem number
SFXE	Payitem extension number
DCT description	Document type value description. The extraction program calls GetUDC (X0005) for 00/DT UDC and writes the description to the text file.
KCO description	Document company value description. The extraction program calls GetCompanyName (B0000051) and writes the description to the text file.
DCTA	Document type – adjusting
DCTA description	Document type – adjusting value description. The extraction program calls GetUDC (X0005) for the Document Type (00/DT) UDC and writes the description to the text file.
AN8	Address number
AN8 description	Address number value description. The extraction program calls GetAddressBookDescriptionA7 (GETABDA7) and writes the description to the text file.
PYE	Address number – payee
PYE description	Address number – payee value description. The extraction program calls GetAddressBookDescriptionA7 (GETABDA7) and writes the description to the text file.
DIVJ	Date – invoice
DSVJ	Date – service/tax
DGL	Date – G/L
FY	Fiscal year
CTRY	Century
PN	Period number
CO	Company
CO description	Company description The extraction program calls GetCompanyName (B0000051) and writes the description to the text file.
PST	Pay status code
PST description	Pay status code value description. The extraction program calls GetUDC (X0005) for the Payment Status (00/PS) UDC and writes the description to the text file.
AG	Amount – gross
AAP	Amount – open
ATXA	Amount – taxable
ATXN	Amount – non-taxable
STAM	Amount – tax
TXA1	Tax rate/area 1
TXA1 description	Tax rate/area 1 value description. The extraction program calls EditTaxRateArea (B0000140) and writes the description to the text file.
EXR1	Tax explanation
EXR1 description	Tax explanation value description. The extraction program calls GetUDC (X0005) for the Tax Explanation Code (00/EX) UDC and writes the description to the text file.
CRCD	Currency code – from
CRCD description	Currency code value description The extraction program calls F0013GetCurrencyDescription (B03B0103) and writes the description to the text file.
CRR	Currency conversion rate
ACR	Amount – foreign
FAP	Amount – foreign open
CTXA	Amount – foreign taxable
CTXN	Amount – non-foreign taxable.
CTAM	Amount – foreign tax.
GLBA	G/L bank account.
GLBA description	G/L bank account value description. The extraction program calls RetrieveAccountMasterInfoGLPost (B0900104) and writes the description to the text file.
POST	G/L posted code.
VOD	Void flag.
VINV	Supplier invoice number.
RMK	Name – remark.
PYIN	Payment instrument.
PYIN description	Payment instrument value description. The extraction program calls GetUDC (X0005) for the Payment Instrument (00/PY) UDC and writes the description to the text file.
TXA3	Tax rate/area 3 – withholding.
TXA3 description	Tax rate/area 3 – withholding value description. The extraction program calls EditTaxRateArea (B0000140) and writes the description to the text file.
EXR3	Tax explanation 3 – withholding.
EXR3 description	Tax explanation 3 – withholding value description. The extraction program calls GetUDC (X0005) for the Tax Explanation Code (00/EX) UDC and writes the description to the text file.
AC07	Category code – address book 07.
AC07 description	Category code – address book 07 value description. The extraction program calls GetUDC (X0005) for the Address Book Category Code 07 (01/07) UDC and writes the description to the text file.
TNN	Flag for 1099.
DTXS	Deferred tax status – VAT.
BCRC	Currency code – base.
BCRC description	Currency code – base value description. The extraction program calls F0013GetCurrencyDescription (B03B0103) and writes the description to the text file.
ATAD	Amount – to distribute.
CTAD	Amount – foreign to distribute.
NRTA	Amount – nonrecoverable tax.
FNRT	Amount – foreign nonrecoverable tax.
TAXP	Purchasing taxable Y/N.

F0911 GDPdU Data Extraction Program (R89F0911G)

When you run the F0911 GDPdU Data Extraction program, the system generates a text file named F0911GDPDU.txt, and writes the file to the Export directory for your installation. The system writes data for the selected records for the data items listed in this table, in the order listed.

Field	Description of Value
DCT	Document type.
DOC	Document number
KCO	Document company.
DGJ	G/L date.
JELN	Journal entry line number.
LT	Ledger type.
EXTL	Line extension code.
DCT description	Document type value description. The extraction program calls GetUDC (X0005) for 00/DT UDC and writes the description to the text file.
KCO description	Document company value description. The extraction program calls GetCompanyName (B0000051) and writes the description to the text file.
LT description	Ledger type value description. The extraction program calls IsColInLedgerTypeTable (B0000378) and writes the description to the text file.
POST	G/L posted code.
CO	Company.
CO description	Company description. The extraction program calls GetCompanyName (B0000051) and writes the description to the text file.
MCU	Business unit.
MCU description	Business unit value description. The extraction program calls IsColumnInBusinessUnitTable (ISCOLBU) and writes the description to the text file.
OBJ	Object
SUB	Subsidiary
PN	Period number
CTRY	Century
FY	Fiscal year
CRCD	Currency code – from
CRCD description	Currency code value description The extraction program calls F0013GetCurrencyDescription (B03B0103) and writes the description to the text file.
CRR	Currency conversion rate
AA	Amount
EXA	Name – alpha explanation
EXR	Name – remark explanation
AN8	Address number
AN8 description	Address number value description. The extraction program calls GetAddressBookDescriptionA7 (GETABDA7) and writes the description to the text file.
CN	Payment number
ALTX	Consumption tax cross reference.
DSVJ	Date service/tax
BCRC	Currency code – base
BCRC description	Currency code – base value description. The extraction program calls F0013GetCurrencyDescription (B03B0103) and writes the description to the text file.
TXA1	Tax rate/area 1
TXA1 description	Tax rate/area 1 value description. The extraction program calls EditTaxRateArea (B0000140) and writes the description to the text file.
EXR1	Tax explanation
EXR1 description	Tax explanation value description. The extraction program calls GetUDC (X0005) for the Tax Explanation Code (00/EX) UDC and writes the description to the text file.
TXITM	Tax – short item number
TXITM description	Tax – short item number value description. The extraction program calls GetItemMasterByShortItem (X4101) and writes the description to the text file.
ACR	Amount currency

Running the F0018 GDPdU Data Extract Program (R89F0018G)

Select GDPdU Data Extraction (G74D1), F0018 Data Extraction.

Running the F03B11 GDPdU Data Extract Program (R89F03B11G)

Select GDPdU Data Extraction (G74D1), F03B11 Data Extraction.

Running the F0411 GDPdU Data Extraction Program (R89F0411G)

Select GDPdU Data Extraction (G74D1), F0411 Data Extraction.

Running the F0911 GDPdU Data Extract Program (R89F0911G)

Select GDPdU Data Extraction (G74D1), F0911 Data Extraction.

Modifying the Index.XML File

The Index.XML file that you must send to the tax authority is provided on the Oracle | Peoplesoft Customer Connection web site. The text files that you generate using the data extraction programs do not modify the Index.XML file. Rather, the Index.XML provides information about the data in the text files that you generate using the table conversion programs provided with this solution. For example, the Index.XML file includes information about the values in the text file.

Note. Index.XML files that describe the fields in the F0911, F03B11, F0411, and F0018 table are provided on the Oracle | Peoplesoft Customer Connection web site. An XML file is provided for each release for which data conversion programs are supported. Be sure to download and modify the Index.XML file that corresponds to your release of JD Edwards EnterpriseOne.

The Index.XML file includes a header area in which you complete this information:

Version

Complete this field with the version number of the medium used to submit the text files, Index.XML file, and DTD.
DataSupplier

Complete these fields with the name and location of the company suppling the data. A Comment field is also provided in which you can enter additional information.
Media

Complete the Media fields with the name and the dates of the media that you send to the tax audit authority.

This image shows the DataSupplier fields as displayed in an XML viewer. Depending on the text file viewer or XML viewer that you use, the data set might look different. However, the content of the fields is the same.

For each table for which you extract data, complete:

URL

Enter the physical file name of the text file.
Name

Enter the name of the table.
Description

Enter a description of the table.
Date range and date format
Decimal symbol

Complete this field with the decimal separator that you use.
Digit grouping symbol.

Complete this field with the symbol used to separate thousands.
Variable length

The data included in the VariableLength section of the Index.XML file includes a listing of the fields included for the specified table as shipped with the software. If you have modified the F0911, F03B11, F0411, or F0018 tables, you must modify the listing of the fields and the descriptions provided. The data further specifies whether a field is alphanumeric, numeric, or a date, and the maximum length of the field.

Review the entire Index.XML file before you send it to the tax audit authority to assure that the data in the Index.XML file accurately describes your submission.

This image shows some of the data items for the F0018 table, as displayed in an XML viewer. Depending on the text file or XML viewer that you use, the data set might look different. However, the content of the fields is the same.