Manual

DataCollector 2.0

January 2014
UNIT4 Accountancy B.V.
Bastion 4
Postbus 755
3900 AT Veenendaal

Copyright 2014 UNIT4 Accountancy B.V., Veenendaal, the Netherlands

All rights reserved. This manual may not, in whole or in part, be reproduced, stored in anelectronic file or published, by electronic or
mechanical means, photo copy or any other means, without the prior written permission of UNIT4 Accountancy B.V.

The information contained in this document may be modified without prior notification. Unless stated otherwise, all companies,
persons, data and adresses are entirely fictional or are used fictitiously. Although this document was prepared with utmost care,
UNIT4 Accountancy B.V. cannot be held liable for any (printing) errors or incompleteness. All trademarks mentioned are the
property of their respective owners..
TABLE OF CONTENTS
1. Introduction 1
1.1. Data points 1
2. Supported systems, versions and databases 6
3. Technical requirements 7
4. Extraction parameters 8
5. Tool handling 9
5.1. Program folder structure 9
5.2. Generic screen navigation 9
5.3. File split 11
5.4. Checks at program starts 11
5.5. Error handling 12
5.6. Load and save parameters 14
5.7. Guidance 17
5.8. Generic screens 18
5.8.1. Define operation mode 18
5.8.2. Select data source 20
5.8.3. Select entities 23
5.8.4. Select multiple companies 25
5.8.5. Select multiple plants 26
5.8.6. Specify data destination 27
6. Tool output 31
6.1. Folders used 31
6.2. File naming conventions 31
6.3. Run time 31
6.4. Analyse 31
6.5. Data validation 34
6.6. Screen output 34
6.7. Log file 39
7. Messages 40
7.1. Change the operation mode 40
7.2. Select a different ERP system 40
7.3. Database does not correspond with ERP system 41
7.4. Invalid date selection 43
7.5. Multiple plants 44
8. Field mapping 46
8.1. Axapta 46
8.1.1. Trial balance 46
8.1.2. Journal entries 46
8.1.3. Creditors master data 48
8.1.4. Debtors master data 49
8.1.5. Accounts payable open items 50
8.1.6. Accounts receivable open items 51
8.1.7. Period End Inventory 52
8.1.8. Sales History 52
8.2. JD Edwards EnterpriseOne 53
8.2.1. Trial balance 53
8.2.2. Journal entries 54
8.2.3. Creditors master data 55
8.2.4. Debtors master data 56
8.2.5. Accounts payable open items 56
8.2.6. Accounts receivable open items 57
8.2.7. Period End Inventory 58
8.2.8. Sales History 59
8.3. Navision 60
8.3.1. Trial balance 60
8.3.2. Journal entries 60
8.3.3. Creditors master data 62
8.3.4. Debtors master data 63
8.3.5. Accounts payable open items 64
8.3.6. Accounts receivable open items 65
8.3.7. Period End Inventory 65
8.3.8. Sales History 66
8.4. Oracle Financials 11 67
8.4.1. Trial balance 67
8.4.2. Journal entries 68
8.4.3. Creditors master data 69
8.4.4. Debtors master data 70
8.4.5. Accounts payable open items 71
8.4.6. Accounts receivable open items 72
8.4.7. Period End Inventory 73
8.4.8. Sales History 73
8.5. Oracle Financials 12 74
8.5.1. Trial balance 74
8.5.2. Journal entries 75
8.5.3. Creditors master data 76
8.5.4. Debtors master data 77
8.5.5. Accounts payable open items 78
8.5.6. Accounts receivable open items 79
8.5.7. Period End Inventory 80
8.5.8. Sales History 80
8.6. SAP R/3 Classic GL - SAP ECC New GL 81
8.6.1. Trial balance SAP 81
8.6.2. Journal entries 82
8.6.3. Creditors master data 84
8.6.4. Debtors master data 84
8.6.5. Accounts payable open items 85
8.6.6. Accounts receivable open items 86
8.6.7. Period End Inventory 88
8.6.8. Sales History 89
1. INTRODUCTION
DataCollector is a program that extracts data entities from various ERP systems such as
SAP, Oracle Financials, Microsoft Dynamics (AX and NAV) and JD Edwards
EnterpriseOne.

No installation required
DataCollector is a runtime version. The advantage is that DataCollector can be used
immediately (for example on a USB stick) without having to install the software. You can
copy the complete runtime folder to the system of your client. Also within this situation no
install is needed.

Meaning of the icons

The icons used in this guide have the following meaning:

This is important information

This is a tip

1.1. Data points

By default Microsoft Access (MDB) files will be created with the following structure:

Trial balance

Field Type Length

GL Account Number Text 30

GL Account Name Text 200

Account Type Text 40

Account Class Text 40

GL Account Current Period Beginning Balance Currency

GL Account Current Period Ending Balance Currency

UserDefined1 Text 40
UserDefined2 Text 40
UserDefined3 Text 40
UserDefined4 Text 40

Manual DataCollecor 2.0 Page 1 of 89

Field Type Length
UserDefined5 Text 40

Journal entries

Field Type Length

JE Number Text 30

GL Account Number Text 30

Amount Prefix Text 1

Amount Currency

Business Unit Text 40

Effective Date Date

Period Text

Entry Date Date

Preparer ID Text 40

Source Text 40

JE Description Text 200

BSTAT Text 1

UMSZK Text 1

UserDefined1 Text 40
UserDefined2 Text 40
UserDefined3 Text 40
UserDefined4 Text 40
UserDefined5 Text 40

Manual DataCollecor 2.0 Page 2 of 89

Creditors/Debtors master data

Field Type Length

Account Number Text 50

Name Text 50

Credit Limit Text 50

Payment Terms Text 50

Address 1 Text 50

Address 2 Text 50

Postal Code Text 50

Country Text 50

UserDefined1 Text 50
UserDefined2 Text 50
UserDefined3 Text 50
UserDefined4 Text 40
UserDefined5 Text 40

Accounts Payable / Accounts Receivable open items

Field Type Length

Account Number Text 50

Name Text 50

Type Text 50

Reference Text 50

Document Date Date

Payment Date Date

Manual DataCollecor 2.0 Page 3 of 89

Field Type Length
Amount Numeric 18

Local Currency Text 50

CurrencyAmount Numeric 18

Currency Text 50

LineItemNumber Text 50

GL Account Number Text 30

UMSKZ Text 1

BSTAT Text 1

UserDefined1 Text 50
Userdefined2 Text 50
UserDefined3 Text 50
UserDefined4 Text 50
UserDefined5 Text 50

Period End Inventory

Field Type Length

Inventory Reference Text 50

Description Text 50

Unit Price*) Numeric 18

Quantity Numeric 18

Amount Numeric 18

Last Purchase Date Date

Last Movement Date Date

Manual DataCollecor 2.0 Page 4 of 89

Field Type Length
New Material Text 1

UserDefined1 Text 50
Userdefined2 Text 50
UserDefined3 Text 50
UserDefined4 Text 50
UserDefined5 Text 50

*) the last unit price (at period end)

Sales History

Field Type Length

Inventory Reference Text 50

Sales Quantity Numeric 18

Sales Amount Numeric 18

Latest Selling Price Numeric 18

UserDefined1 Text 50
Userdefined2 Text 50
UserDefined3 Text 50
UserDefined4 Text 50
UserDefined5 Text 50

If MDB creation fails the extracted data will be stored in the format Comma-Separated
Values (CSV) files. The CSV file has the same column names as the MDB files. The CSV
file has the following format:
the delimiter is "| Bar",
the text qualifier is "None",
the first row contains column names,
the decimal symbol indicator is ". (Period)",
the date format is "yyyy-MM-dd".

The file extension is TXT.

All control characters are stripped from all string entity properties.

Manual DataCollecor 2.0 Page 5 of 89

2. SUPPORTED SYSTEMS, VERSIONS AND
DATABASES
DataCollector supports the following systems, versions and databases:

System Version Database

JD Edwards EnterpriseOne 8.x Microsoft SQL Server
JD Edwards EnterpriseOne 8.x Oracle DBMS 10g/11g
Microsoft Dynamics AX 4.0 Microsoft SQL Server
Microsoft Dynamics AX 2009 Microsoft SQL Server
Microsoft Dynamics Navision 4 Microsoft SQL Server
Microsoft Dynamics Navision 5 Microsoft SQL Server
Microsoft Dynamics Navision 2009 Microsoft SQL Server
Oracle Financials 11.0 Oracle RDBMS 10g/11g
Oracle Financials 12.0 Oracle RDBMS 10g/11g
SAP Classic GL R/3 and ECC Supported by RFC*
SAP New GL ECC Supported by RFC*

* DBMS independent

Manual DataCollecor 2.0 Page 6 of 89

3. TECHNICAL REQUIREMENTS
Requirements for DataCollector:
Operating system Windows XP with Service Pack 1a or higher
Minimum 512 MB RAM; 1 GB is recommended
Minimum 200 MB free disk space
.NET Framework 2.0 or later
Write access on a medium with enough free disk space to store the extracted data
No access restrictions to the Windows Temp folder
Online access to the ERP system
No CITRIX environment
Customization in the database is not supported
Minimum screen resolution 1024x768 pixels

DataCollector can be executed on Windows systems that have the Microsoft .NET
Framework 2.0 installed. Most Windows operating systems, such as Windows XP,
Windows 2003 and Windows Vista usually have the Microsoft .NET Framework 2.0 (or
later) installed by default. If this is not the case, the Microsoft installation package can be
found here:

http://www.microsoft.com/en-us/download/details.aspx?id=1639.

If .NET Framework 2.0 or later is not installed the following error message appears:

Manual DataCollecor 2.0 Page 7 of 89

4. EXTRACTION PARAMETERS
To extract data entities from an ERP system you have to connect to the ERP system
concerned and enter several data:
Connection information
You will need some parameters like the IP address of the ERP system and / or
underlying database and port addresses.
User (account) information
A user account must be available with a password on the ERP system concerned.
Company code
Periods
It is possible to extract one or more periods of one or more fiscal years at the same
time.
Entities
In this version you can select General Ledger (GL), Accounts Payable (AP),
Accounts Receivable (AR) and/or Inventory.

Manual DataCollecor 2.0 Page 8 of 89

5. TOOL HANDLING
5.1. Program folder structure
The structure of the program folder is displayed in the figure below.

<root> Contains the executable of the program

Collector Contains the software packages of the ERP systems available
DLL Contains the software components used
Guidance Contains the screen guidance
Help Contains this documentation

DataCollector creates the Guidance folder automatically when this folder does not exist.

To start DataCollector double-click (click the mouse button twice in quick succession)
.

5.2. Generic screen navigation

Overview buttons
Button Function
Click this button to go to the next window of the program.

Click this button to return to the previous window of the program.

Entered data will be saved during this session.
Click this button to start the extraction process.

Manual DataCollecor 2.0 Page 9 of 89

Button Function
Click this button to exit the program. You can also use the Escape
button.
Click this button to abort the extraction. You can also use the Escape
button to cancel.
Click this button to load the parameters previously saved.

Click this button to save the entered parameters.

Click this button to start the extraction parameter analysis.

Overview navigation bar

In simple mode the navigation bar consists of the following four buttons:

In advanced mode the navigation bar consists of a variable number of buttons. The
number of buttons depends on the selected ERP system and the selected entities. The
most comprehensive navigation bar looks like:

Manual DataCollecor 2.0 Page 10 of 89

The caption of the buttons corresponds to the names of the parameter screens. Before
each caption an indicator is shown. Each indicator has one of the following colors:

Indicator Function
Transparent The screen is not accessible yet
(open grey)
Grey The screen is accessible, but the parameters are not entered
Yellow In the screen all required parameters are not entered
Green In the screen all required parameters are valid
Red In the screen the entered parameters are not valid

The colors dark yellow and dark green are shown on non-selected screens.
When such a screen is selected the color light yellow or light green is shown.

In the navigation bar you can always click the buttons with the indicator grey, yellow and
green. The corresponding screen will be shown. A button with the indicator transparent is
not accessible.

A screen cannot be left when the button in the navigation bar shows the color red. The
color is changed into yellow when a value of a parameter is re-entered.

It is still possible to navigate through the screens with the button and the

button.

5.3. File split

If the number of records is very large, it is possible that the physical file limits of the file
format are reached. Therefore the number of records that will be written to a single file is
limited. This option allows to circumvent reaching that limit by writing the records to a
series of files in stead of one single file. The default value is 8.000.000 records and
cannot be changed by the user.

5.4. Checks at program starts

Before you can select the data source (see "Select data source" on page 20),
DataCollector checks if MDB files can be used to extract the data.

DataCollector determines the driver type. When Microsoft OLEDB is installed,

DataCollector will use this driver. Otherwise Microsoft ODBC will be used. After checking
the driver type DataCollector determines the driver name for the selected driver type. If
OLEDB is used the DataCollector will use the Microsoft.Jet.OLEDB.4.0 by default,
otherwise the Microsoft.ACE.OLEDB.12.0 is used. The Jet driver is normally present on
all Windows XP systems and later. The ACE driver is present on Windows systems
having Office 2007 or later installed.

Manual DataCollecor 2.0 Page 11 of 89

When both driver type and driver name are available on the system, DataCollector
creates in the temp folder a temporarily MDB file with the selected driver type and driver
name and stores a single record in the MDB file. If successful the temporarily MDB file is
deleted and DataCollector will use MDB files to extract the data.

DataCollector will use TXT files (in CSV file format) to extract the data when:
the required driver type is not available on the system
the required driver name is not available on the system
the temporarily MDB file cannot be created
it is not possible to store data in the temporarily MDB file

The following message appears:

Click the button to proceed or click the button to cancel the

program.

5.5. Error handling

When an error occurred the error message is shown in the following window (or similar):

With the button the details of the error message is shown:

Manual DataCollecor 2.0 Page 12 of 89

In this window the original error message is always shown.

Select the button to hide the details of the error message.

With the button this window will be closed. This information can be stored in

a text file with the button. The following window is shown:

Manual DataCollecor 2.0 Page 13 of 89

With the button the information is stored in the given file name. The default
file name is ErrorMessage following by a time stamp. The extension is txt. It is possible to
overwrite the default file name. The screen with the error message is closed
automatically.

5.6. Load and save parameters

Before starting the extraction process the entered parameters can be stored in an
extraction settings file. The next time these parameters can be used again by loading this
file in the same environment.

With the button (see "Specify data destination" on page 27), the entered
parameters are stored in an extraction settings file.

Manual DataCollecor 2.0 Page 14 of 89

With the button the parameters are stored in the given file name. The
default file name corresponds with the dcz file, but the extension is "settings". It is
possible to overwrite the default file name.

If you have selected the Batch extraction option as extraction mode, one or more batch
files are created.

Manual DataCollecor 2.0 Page 15 of 89

The default file name corresponds with the dcz file, but the extension is "bef". It is
possible to overwrite the default file name.

The next time the data source parameters must be entered again. With the

button (see "Select data source" on page 20), the extraction settings file
can be selected. The extraction settings file contains the parameters of a previous
extraction. These parameters are loaded in DataCollector.

Manual DataCollecor 2.0 Page 16 of 89

Select an extraction settings file and click the button.

5.7. Guidance
The screens can be customized for the end user by adding screen and/or label guidance.

Screen guidance is shown at the top of the screen. The description of parameters can be
changed by label guidance. For each parameter a tooltip is available by moving the
mouse pointer over the field:

Manual DataCollecor 2.0 Page 17 of 89

Guidance can also be added to error messages. When an error occurred the original
error message is requested in the details

The end user cannot change the guidance.

Because of adding guidance the figures in this manual may look different.

5.8. Generic screens

In this paragraph you will find a description of the generic screens.

5.8.1. Define operation mode

Start DataCollector. The following window is displayed:

Manual DataCollecor 2.0 Page 18 of 89

In this window the extraction and operation mode are selected.

Set extraction mode

Select the extraction mode:
Direct extraction
Data entities are extracted directly after all the necessary parameters are entered.
Batch extraction
In this mode DataCollector creates a settings file which can be used in
DataCollector batch extraction. With DataCollector batch extraction you can
specify at what date and time the data must be extracted.

Set prepare batch mode

You can select one of the following options if you have selected the batch extraction
mode:
Single selection
You can select one company and one plant to extract data from.

Manual DataCollecor 2.0 Page 19 of 89

 Multiple company selection
You can select one or more companies to extract data from.
Multiple plant selection
You can select one or more plants of one company to extract data from.

Set operation mode

Simple mode
In simple mode only the mandatory parameters of the selected entities are
displayed. Additional parameters (such as user defined fields) are only available in
advanced mode.
Advanced mode
In advanced mode all the parameters of the selected entities are displayed and
must be entered.

Not experienced users are recommended to select the simple mode.

The number of screens depends on the selected operation mode and ERP system. The
progress bar shows the progress in the number of screens.

The first digit (2) is the number of the current screen. The last digit (5) contains the total
number of screens. The progress bar is not shown on the last screen.

5.8.2. Select data source

In this window you select the ERP system and you enter the connection parameters of
the selected ERP system.

With the button an extraction settings file can be selected (see "Load
and save parameters" on page 14).

With the button the concerned ERP system is selected:

Manual DataCollecor 2.0 Page 20 of 89

After selecting the ERP system the connection parameters are shown. These parameters
depend on the selected ERP system. The following window (or similar) is shown:

Manual DataCollecor 2.0 Page 21 of 89

Click the button to select the entities that will be extracted. It is also
possible to click the corresponding button in the navigation bar if the button is enabled.

When the connection parameters are entered for the first time the button in the
navigation bar is disabled. This also applies when the connection parameters are
changed.

The connection parameters are validated by clicking the button.

When this screen is accessed again and the connection parameters are unchanged
you can also click a button in the navigation bar.

When a value of an entered parameter is changed (for example by selecting a different

ERP system or changing the operation mode) a message is shown in order to inform the
user. The user will also be notified with a message when the selected database does not
correspond with the selected ERP system (see "Messages" on page 40).

Manual DataCollecor 2.0 Page 22 of 89

5.8.3. Select entities
In this screen you select the entities that will be extracted. It may be possible that some
entities and/or options are not available. The selected options determines the specific
screens that will be shown.

The number of options depends on the operation mode. In simple mode the date values
are also entered for the selected entities.

Manual DataCollecor 2.0 Page 23 of 89

Click the button to enter the company parameters concerning the
selected ERP system. It is also possible to click the button in the navigation bar if the
button is enabled.

With the button you can go to the previous screen. You can also click

the button in the navigation bar.

For each ERP system the available options in this screen can be customized for the
end user.

When the date selection is entered incorrectly a message is shown in order to inform the
user (see "Messages" on page 40).

Manual DataCollecor 2.0 Page 24 of 89

5.8.4. Select multiple companies
This window is displayed if you have selected the option "Multiple company selection" in
the "Define operation mode" window (see "Define operation mode" on page 18).

The appearance of the window depends on the selected ERP system and operation
mode (simple or advanced). However, the operation is the same.

Click on the Selected column to select the companies concerned. In the ledger
selection column you have to select the ledger.

If you want to select non-consecutive companies, hold the Ctrl key.

To select consecutive companies:

select the first company,
hold the Shift key,
select the last company.

Manual DataCollecor 2.0 Page 25 of 89

5.8.5. Select multiple plants
This window is displayed if you have selected the option "Multiple plant selection" in the
"Define operation mode" window (see "Define operation mode" on page 18).

The appearance of the window depends on the selected ERP system.

The "Select multiple plants" mode is only available for ERP systems that support
multiple plants by company.

Select in the Company field the company concerned.

Click on the Selected column to select the plants concerned.

If you want to select non-consecutive plants, hold the Ctrl key.

To select consecutive plants:

select the first plant,

Manual DataCollecor 2.0 Page 26 of 89

 hold the Shift key,
select the last plant.

5.8.6. Specify data destination

After you have selected the ERP system and entered the data source parameters,
DataCollector will present a final entry screen where the parameters related to the
destination of the extracted data is to be entered. The specified and selected data source
parameters will be shown first. It is possible to check these parameters before extracting
the data from the selected ERP system.

If you have selected the Batch extraction option as operation mode, the

button is shown instead of the button.

The Filename field contains the folder in which the file is saved. The extension of the file
is dcz and contains one or more files with the extracted data of the selected entities. The
dcz file is encrypted and compressed. With the program Decrypter for DataCollector this

Manual DataCollecor 2.0 Page 27 of 89

file can be extracted. If you have selected the batch extraction option, the Filename field
is not shown.

Click the button to change the filename. It is also possible to change the destination
by selecting an other folder.

Click the button to commit the changed file name and/or folder.

Manual DataCollecor 2.0 Page 28 of 89

With the button you can go to the previous screens in order to change
the data source parameters. You can also click the corresponding button in the
navigation bar.

The summary in this screen will also be shown on the output screen and is also stored in
the log file.

In simple mode additional parameters are shown in this screen. These parameters
belong to advanced mode and are not shown in the previous screens. The extraction
parameters are grouped on operation mode. The advanced parameters are shown after
the simple parameters:

Manual DataCollecor 2.0 Page 29 of 89

With the button the entered parameters are stored in an extraction
settings file; the extension of the file is "settings". If you have selected the Prepare batch
option as operation mode, the extension of the settings file is "bef" (see "Load and save
parameters" on page 14).

With the button the extraction parameter analysis can be started (see
"Analyse" on page 31).

Click the button to start the extraction.

Manual DataCollecor 2.0 Page 30 of 89

6. TOOL OUTPUT
6.1. Folders used
You can select the output folder in the "Specify data destination" window (see "Specify
data destination" on page 27). Temporary files will be placed in the temporary folder of
Windows and in the folder of DataCollector.

6.2. File naming conventions

The name of the dcz file is made up as followed:

Company number
Company name
Time stamp of extraction

The date format is according to ISO 8601 standard.

The figure below shows an example of a dcz file:

6.3. Run time

In a development environment it is not possible to determine the average time the tool
would need for an extraction.

6.4. Analyse
The analyse function is not available when multiple company selection or multiple
plant selection mode is selected.

Before starting the extraction process the extraction parameters can be analysed. The
availability of the required tables are checked for all entities. Also the number of entries
are estimated for all entities.

For the SAP collector the entries of all entities are not estimated in this version.

Manual DataCollecor 2.0 Page 31 of 89

When the analysis has succeeded the results are colored green. No recommended
actions are needed.

With the button the analyses are stored in the given file name. The default
file name is AnalyseInfo following by a time stamp. The extension is txt. It is possible to
overwrite the default file name.

With the button this window will be closed.

When the analysis has not succeeded the errors are colored red. Recommended actions
are shown.

Manual DataCollecor 2.0 Page 32 of 89

With the button the recommended actions are applied.

In the example above the application jumps to the second screen (Select entities) and
the entity "GeneralLedger" is deselected automatically. Click the
button to return to this screen.

When all selected entities contains errors the following recommended action is shown:

It is not possible to extract data. Select a different company.

The application jumps to the third screen (Company) in order to select a different
company.

Manual DataCollecor 2.0 Page 33 of 89

6.5. Data validation
After the extracted data is stored in the output files the data points will be validated and
the results are stored in the log file. The following validations are executed:
For all date fields the first and the last date is determined.
All fields are checked for blank values. An User Defined Field (UDF) is not
checked when the value is <not selected>.
For all numeric and currency fields the sum of each field is determined.
For each entity the total number of records processed is calculated.

In this sequence the results are stored in the log file and HTML file.

For the list of the data points with their types, (see "Data points" on page 1).

6.6. Screen output

Once DataCollector starts extracting the financial information the following progress
screen (or similar) will be shown:

Manual DataCollecor 2.0 Page 34 of 89

The summary of the data source parameters and the version number of the connected
ERP system (if available) are also shown.

It is a report in HTML format containing a summary of the completed extraction and

validations. Click the button to print the report. To save the report, click the
button.

With the button in the "Exporting data" window the extraction can be
aborted. It is also possible to abort the extraction by pushing the escape button. The
following message appears:

Manual DataCollecor 2.0 Page 35 of 89

Click the button to abort the extraction. The current entity will be completed
and the following entities are not extracted.

The extracted entities are stored in the dcz file.

An entity is skipped when an error occurred during extracting. When the extraction
contains warnings and/or errors a summary is shown in the following window (or similar):

Manual DataCollecor 2.0 Page 36 of 89

With the button this window will be closed. This information can be stored in
a text file with the button. The following window is shown:

Manual DataCollecor 2.0 Page 37 of 89

With the button the information is stored in the given file name. The default
file name corresponds with the dcz file, but the extension is txt. It is possible to overwrite
the default file name.

It is possible to abort the program by pushing the escape button. The following message
appears:

Click the button to abort the program; no data entities will be extracted.

Click the button for a new extraction.

Manual DataCollecor 2.0 Page 38 of 89

6.7. Log file
When the extracted data is stored, the information of the extraction (also displayed on the
screen) is stored in a log file and HTML file. The summary of the data source parameters
and the version number of the connected ERP system (if available) are also stored. The
log file and HTML file are also added to the dcz file.

Manual DataCollecor 2.0 Page 39 of 89

7. MESSAGES
When a value of an entered data source parameter is changed or the date selection is
entered incorrectly a message is shown in order to inform the user. The various
messages are described in this chapter.

7.1. Change the operation mode

When the operation mode is changed from simple mode to advanced mode the entered
parameters will be adopted. The following message is shown:

When the operation mode is changed from advanced mode to simple mode the entered
parameters (except connection and company parameters) will be lost. The following
message is shown:

7.2. Select a different ERP system

When the selected ERP system is changed the entered parameters will be lost. The
following message is shown:

Manual DataCollecor 2.0 Page 40 of 89

When the connection parameters of the selected ERP system are changed the
connection parameters must be revalidated. The following message is shown:

7.3. Database does not correspond with ERP system

When the selected database does not correspond with the selected ERP system the user
will be notified with an error or a warning.

The following message is shown when the version of the selected ERP system cannot be
found in the selected database:

Manual DataCollecor 2.0 Page 41 of 89

When the version of the selected database does not match the selected ERP system the
following message (or similar) is shown:

For some ERP systems it is possible to continue. The following message (or similar) is
shown:

For SAP specific messages are shown when the version of the selected database does
not match the selected ERP system:

It is not allowed to connect to a database with SAP New GL (ECC) when the ERP
system SAP Classic GL is selected:

It is not allowed to connect to a database with SAP Classic GL (R/3 and ECC)
when the ERP system SAP New GL is selected:

Manual DataCollecor 2.0 Page 42 of 89

7.4. Invalid date selection
When the date selection is entered incorrectly the following messages are shown:

The time sequence is not correct. Adjust the from date or the "to" date.

Only a date value between 01-01-1990 and 31-12-2025 is allowed.

Manual DataCollecor 2.0 Page 43 of 89

 Only a year value between 1990 and 2025 is allowed.

The allowed value for the period depends on the selected ERP systems, for
example:

7.5. Multiple plants

If you have selected the direct extraction mode or the batch extraction with single
selection mode, and the selected company contains multiple plants, the following
message is shown:

Manual DataCollecor 2.0 Page 44 of 89

To generate multiple plant specific bef files in a single prepare batch action, select the
Prepatre batch mode 'Multiple plant selection'.

Manual DataCollecor 2.0 Page 45 of 89

8. FIELD MAPPING
8.1. Axapta

8.1.1. Trial balance

Source field Mapped field
LEDGERTABLE.ACCOUNTNUM GL Account Number

LEDGERTABLE.ACCOUNTNAME GL Account Name

LEDGERTABLE.ACCOUNTPLTYPE Account Type

See below Account Class

See below GL Account Current Period Beginning Balance

See below GL Account Current Period Ending Balance

The Class is determined using summary accounts having ACCOUNTPLTYPE of 6, 9, or

10.

The BeginAmount and EndingAmount are calculated:

(LEDGERBALANCESTRANS.DEBITMST) +
(LEDGERBALANCESTRANS.CREDITMST)

For the BeginAmount all transactions having

PERIODCODE = 0 AND Year(TRANSDATE) = Year(startdate)
OR
PERIODCODE = 1 AND TRANSDATE < startdate

For the EndingAmount all transactions having

PERIODCODE = 0 AND Year(startdate) <= Year(TRANSDATE) < Year(enddate)
OR
PERIODCODE = 1 AND startdate <= TRANSDATE <= enddate

The LEDGERTABLE and LEDGERTRANS tables are joined on the ACCOUNTNUM

fields. Only accounts are selected that have an LEDGERTABLE.ACCOUNTPLTYPE <=
5.

8.1.2. Journal entries

Source field Mapped field
LEDGERTRANS.VOUCHER JE Number

LEDGERTRANS.ACCOUNTNUM GL Account Number

Manual DataCollecor 2.0 Page 46 of 89

Source field Mapped field

Amount Prefix

LEDGERTRANS.AMOUNTMST Amount

LEDGERTRANS.DATAAREAID Business Unit

LEDGERTRANS.TRANSDATE Effective Date

See below Period

TRANSACTIONLOG.CREATEDDATE Entry Date

TRANSACTIONLOG.CREATEDBY Preparer ID
or
USERINFO.NAME

LEDGERTRANS.TRANSTYPE Source

LEDGERTRANS.TXT JE Description

See below UserDefined1

See below UserDefined2
See below UserDefined3
See below UserDefined4
See below UserDefined5

Possible dimensions:
<not selected>
USERINFO.NAME=RealUserName
LEDGERTRANS.DIMENSION = DimensionCode
LEDGERTRANS.DIMENSION2_ = DimensionCode2
LEDGERTRANS.DIMENSION3_ = DimensionCode3
LEDGERTRANS.DIMENSION4_ = DimensionCode4
LEDGERTRANS.DIMENSION5_ = DimensionCode5

The period is the sequence number from the periods table based on the effective date
field.

All transactions are selected that match the following conditions:

LEDGERTRANS.PERIODCODE <> 0
AND
LEDGERTRANS.OPERATIONSTAX = 0

Manual DataCollecor 2.0 Page 47 of 89

AND
startdate <= LEDGERTRANS.TRANSDATE <= enddate

8.1.3. Creditors master data

Source field Mapped field
VENDTABLE.ACCOUNTNUM Account Number

VENDTABLE.NAME Name

VENDTABLE.CREDITMAX Credit Limit

VENDTABLE.PAYMTERMID Payment Terms

VENDTABLE.STREET Address 1

VENDTABLE.CITY Address 2

VENDTABLE.ZIPCODE Postal Code

VENDTABLE.COUNTRYREGIONID Country

See the dimensions below UserDefined1

See the dimensions below UserDefined2
See the dimensions below UserDefined3
See the dimensions below UserDefined4
See the dimensions below UserDefined5

Possible dimensions:
<not selected>
CONTACTPERSON.NAME = ContactPerson
VENDTABLE.YOURACCOUNTNUM = CustomerAccount
VENDTABLE.EMAIL = Email
VENDTABLE.TELEFAX = Fax
VENDTABLE.URL = InternetAddress
VENDTABLE.NAMEALIAS= SearchName
VENDTABLE.BLOCKED = Stopped
VENDTABLE.PHONE = Telephone
VENDTABLE.VATNUM = VatNumber
VENDTABLE.DIMENSION = DimensionCode1
VENDTABLE.DIMENSION2_ = DimensionCode2
VENDTABLE.DIMENSION3_ = DimensionCode3
VENDTABLE.DIMENSION4_ = DimensionCode4
VENDTABLE.DIMENSION5_ = DimensionCode5

Manual DataCollecor 2.0 Page 48 of 89

8.1.4. Debtors master data
Source field Mapped field
CUSTTABLE.ACCOUNTNUM Account Number

CUSTTABLE.NAME Name

CUSTTABLE.CREDITMAX Credit Limit

CUSTTABLE.PAYMTERMID Payment Terms

CUSTTABLE.STREET Address 1

CUSTTABLE.CITY Address 2

CUSTTABLE.ZIPCODE Postal Code

CUSTTABLE.COUNTRYREGIONID Country

See the dimensions below UserDefined1

See the dimensions below UserDefined2
See the dimensions below UserDefined3
See the dimensions below UserDefined4
See the dimensions below UserDefined5

Possible dimensions:
<not selected>
CONTACTPERSON.NAME = ContactPerson
CUSTTABLE.EMAIL = Email
CUSTTABLE.TELEFAX = Fax
CUSTTABLE.URL = InternetAddress
CUSTTABLE.NAMEALIAS= SearchName
CUSTTABLE.BLOCKED = Stopped
CUSTTABLE.PHONE = Telephone
CUSTTABLE.VATNUM = VatNumber
CUSTTABLE.DIMENSION = DimensionCode1
CUSTTABLE.DIMENSION2_ = DimensionCode2
CUSTTABLE.DIMENSION3_ = DimensionCode3
CUSTTABLE.DIMENSION4_ = DimensionCode4
CUSTTABLE.DIMENSION5_ = DimensionCode5

Manual DataCollecor 2.0 Page 49 of 89

8.1.5. Accounts payable open items
Source field Mapped field
VENDTRANS.ACCOUNTNUM Account Number

VENDTABLE.NAME Name

VENDTRANS.TRANSTYPE Type

VENDTRANS.RECID Reference

VENDTRANS.TRANSDATE Document Date

VENDTRANS.DUEDATE Payment Date

VENDTRANS.AMOUNTMST Amount
VENDSETTLEMENT.SETTLEAMOUNTMST

COMPANYINFO.CURRENCYCODE Local Currency

VENDTRANS.AMOUNTCUR CurrencyAmount
VENDSETTLEMENT.SETTLEAMOUNTCUR

VENDTRANS.CURRENCYCODE Currency

See the dimensions below UserDefined1

See the dimensions below UserDefined2
See the dimensions below UserDefined3
See the dimensions below UserDefined4
See the dimensions below UserDefined5

Possible dimensions:
<not selected>
VENDTRANS.TXT = Description
VENDTRANS.INVOICE = Invoice
VENDTRANS.CREATEDBY = User
VENDTRANS.VOUCHER = Voucher
VENDTRANS.DIMENSION = DimensionCode1
VENDTRANS.DIMENSION2_ = DimensionCode2
VENDTRANS.DIMENSION3_ = DimensionCode3
VENDTRANS.DIMENSION4_ = DimensionCode4
VENDTRANS.DIMENSION5_ = DimensionCode5

Manual DataCollecor 2.0 Page 50 of 89

8.1.6. Accounts receivable open items
Source field Mapped field
CUSTTRANS.ACCOUNTNUM Account Number

CUSTTABLE.NAME Name

CUSTTRANS.TRANSTYPE Type

CUSTTRANS.RECID Reference

CUSTTRANS.TRANSDATE Document Date

CUSTTRANS.DUEDATE Payment Date

CUSTTRANS.AMOUNTMST - Amount
CUSTSETTLEMENT. SETTLEAMOUNT

COMPANYINFO.CURRENCYCODE Local Currency

CUSTTRANS. AMOUNTCUR - CurrencyAmount

CUSTSETTLEMENT.SETTLEAMOUNTCUR

CUSTTRANS.CURRENCYCODE Currency

See the dimensions below UserDefined1

See the dimensions below UserDefined2
See the dimensions below UserDefined3
See the dimensions below UserDefined4
See the dimensions below UserDefined5

Possible dimensions:
<not selected>
CUSTTRANS.TXT = Description
CUSTTRANS.INVOICE = Invoice
CUSTTRANS.CREATEDBY = User
CUSTTRANS.VOUCHER = Voucher
CUSTTRANS.DIMENSION = DimensionCode1
CUSTTRANS.DIMENSION2_ = DimensionCode2
CUSTTRANS.DIMENSION3_ = DimensionCode3
CUSTTRANS.DIMENSION4_ = DimensionCode4
CUSTTRANS.DIMENSION5_ = DimensionCode5

Manual DataCollecor 2.0 Page 51 of 89

8.1.7. Period End Inventory
Source field Mapped field
INVENTTABLE.ITEMID Inventory Reference

INVENTTABLE.ITEMNAME Description

INVENTTRANS.COSTAMOUNTPOSTED / Unit Price

INVENTTRANS.QTY

INVENTTRANS.QTY Quantity

MAX(INVENTSUM.POSTEDVALUE - Amount
INVENTSETTLEMENT.COST.AMOUNTADJUSTMENT -
INVENTTRANS(POSTING).COSTAMOUNTPOSTED)

INVENTTRANS.DATEFINANCIAL Last Purchase Date

INVENTTRANS.DATEFINANCIAL Last Movement Date

See the dimensions below UserDefined1

See the dimensions below UserDefined2
See the dimensions below UserDefined3
See the dimensions below UserDefined4
See the dimensions below UserDefined5

Possible dimensions:
<not selected>
INVENTTABLE.NAMEALIAS = SearchName
INVENTTABLE.ITEMGROUPID = ItemGroupID
INVENTTABLE.PRIMARYVENDORID = VendorID
INVENTTABLE.DIMENSION = DimensionCode1
INVENTTABLE.DIMENSION2_ = DimensionCode2
INVENTTABLE.DIMENSION3_ = DimensionCode3
INVENTTABLE.DIMENSION4_ = DimensionCode4
INVENTTABLE.DIMENSION5_ = DimensionCode5

8.1.8. Sales History

Source field Mapped field
INVENTTABLE.ITEMID Inventory Reference

CUSTINVOICETRANS.INVENTQTY Sales Quantity

CUSTINVOICETRANS.LINEAMOUNTMST Sales Amount

Manual DataCollecor 2.0 Page 52 of 89

Source field Mapped field

CUSTINVOICETRANS.LINEAMOUNTMST / Latest Selling Price

CUSTINVOICETRANS.INVENTQTY

See the dimensions below UserDefined1

See the dimensions below UserDefined2
See the dimensions below UserDefined3
See the dimensions below UserDefined4
See the dimensions below UserDefined5

Possible dimensions:
<not selected>
INVENTTABLE.NAMEALIAS = SearchName
INVENTTABLE.ITEMGROUPID = ItemGroupID
INVENTTABLE.PRIMARYVENDORID = VendorID
INVENTTABLE.DIMENSION = DimensionCode1
INVENTTABLE.DIMENSION2_ = DimensionCode2
INVENTTABLE.DIMENSION3_ = DimensionCode3
INVENTTABLE.DIMENSION4_ = DimensionCode4
INVENTTABLE.DIMENSION5_ = DimensionCode5

8.2. JD Edwards EnterpriseOne

8.2.1. Trial balance

Source field Mapped field
F0901.GMMCU + F0901.GMOBJ + GL Account Number
F0901.GMSUB

F0901.GMDL01 GL Account Name

F0901.GMOBJ < F0012.GLG6 = B(alance) Account Type

F0901.GMOBJ >= F0012.GLG6 = R(esult)

F0901.GMDL01 from the above level Account Class

If account type = B GL Account Current Period Beginning

F0902.GBAPYC + F0902.GBANxx Balance

If account type = R
F0902.GBANxx

If account type = B GL Account Current Period Ending

F0902.GBAPYC + F0902.GBANxx Balance

Manual DataCollecor 2.0 Page 53 of 89

Source field Mapped field
If accounttype = R
F0902.GBANxx

xx is the number of a period

TB includes the name of the company (level 1) and the names of the business units
(level 2).
The GL Account Number for the company is the company code with the extension A.
For each business unit the GL Account Number is the business unit code with the
extension B.

8.2.2. Journal entries

Source field Mapped field
F0911.GLDOC JE Number

F0911.GLMCU + F0911.GLOBJ + F0911.GLSUB GL Account Number

Amount Prefix

F0911.GLAA Amount

F0911.GLCO Business Unit

F0911.GLDGJ Effective Date

F0911.GLPN + F0911.GLFY Period

F0911.GLDICJ Entry Date

F0911.GLUSER Preparer ID

F0911.GLDCT Source
or
F0005.DRDL01

F0911.GLEXR or F0911.GLEXA if no value JE Description

See the dimensions below UserDefined1

See the dimensions below UserDefined2
See the dimensions below UserDefined3
See the dimensions below UserDefined4
See the dimensions below UserDefined5

Manual DataCollecor 2.0 Page 54 of 89

Possible dimensions:
<not selected>
F0911.GLAN8 = AddressNumber
F0911.GLICU = BatchNumber
F0911.GLICUT = BatchType
F0911.GLMCU = BusinessUnit
F0911.GLGLC = GL_Offset
F0911.GLJELN = JournalEntryLineNumber
F0911.GLLT = LedgerType
F0911.GLOBJ = ObjectAccount
F0911.GLSBL = Subledger
F0911.GLSBLT = SubledgerType
F0911.GLSUB = SubsidiaryAccount
F0911.GLVINC = SupplierInvoiceNumber
F0005.DRDL01=ReadableDocumentType

All journal entries will be selected with the condition F0911.GLPOST = "P".

8.2.3. Creditors master data

Source field Mapped field
F0101.ABAN8 Account Number

F0101.ABALPH Name

Credit Limit

F0401.A6TRAP Payment Terms

F0116.ALADD1 + F0116.ALADD2 Address 1

F0116.ALCTY1 Address 2

F0116.ALADDZ Postal Code

F0116.ALCTR Country

See the dimensions below UserDefined1

See the dimensions below UserDefined2
See the dimensions below UserDefined3

Possible dimensions:
<not selected>
F0101.ABMCU = BusinessUnit
F0101.ABALKY = LongAddressNumber

Manual DataCollecor 2.0 Page 55 of 89

 F0101.ABTAX = TaxID

8.2.4. Debtors master data

Source field Mapped field
F0101.ABAN8 Account Number

F0101.ABALPH Name

F03012.AIACL Credit Limit

F03012 AITRAR Payment Terms

F0116.ALADD1 + F0116.ALADD2 Address 1

F0116.ALCTY1 Address 2

F0116.ALADDZ Postal Code

F0116.ALCTR Country

See the dimensions below UserDefined1

See the dimensions below UserDefined2
See the dimensions below UserDefined3
See the dimensions below UserDefined4

Possible dimensions:
<not selected>
F0101.ACMCU = BusinessUnit
F03012.AICO = CompanyCode
F0101.ABALKY = LongAddressNumber
F0101.ABTAX = TaxID

8.2.5. Accounts payable open items

Source field Mapped field
F0411.RPAN8 Account Number

F0101.ABALPH Name

IF F0411.RPGLC = 'PREP' Type

THEN 'Prepayment
ELSE 'Invoice'

Manual DataCollecor 2.0 Page 56 of 89

Source field Mapped field
F0411.RPICU + F0411.RPDOC + Reference
F0411.RPSFX

F0411.RPDGJ Document Date

F0411.RPDDJ WHEN Type <> 'PREP' Payment Date

F0411.RPAG Amount
(F0414.RNPAAP + F0414.RNADSA)

F0411.RPBCRC Local Currency

F0411.RPACR CurrencyAmount
(F0414.RNPFAP + F0414.RNCDSA)

F0411.RPCRCD Currency

See the dimensions below UserDefined1

See the dimensions below UserDefined2
See the dimensions below UserDefined3
See the dimensions below UserDefined4
See the dimensions below UserDefined5

Possible dimensions:
<not selected>
F0411.RPICU =BatchNumber
F0411.RPDOC = DocumentNumber
F0411.RPDCT = DocumentType
F0411.RPSFX = LineItem
F0411.RPRMK = Remark
F0411.RPVINV = SupplierInvoiceNumber
F0411.RPUSER = UserID

8.2.6. Accounts receivable open items

Source field Mapped field
F03B11.RPAN8 Account Number

F0101.ABALPH Name

IF F03B11.RPDCT = 'RU' Type

THEN 'Prepayment
ELSE 'Invoice'

Manual DataCollecor 2.0 Page 57 of 89

Source field Mapped field

F03B11.RPICU + F03B11.RPDOC + Reference

F03B11.RPSFX

F03B11.RPDGJ Document Date

F03B11.RPDDJ WHEN Type <> 'RU' Payment Date

F03B11.RPAG Amount
(F03B14.RZPAAP + F03B14.RZADSA +
F03B14.RZDDA + F03B14.RZECBA +
F03B14.RZAAAJ)

F03B11.RPBCRC Local Currency

F03B11.RPACR CurrencyAmount
(F03B14.RZPFAP + F03B14.RZCDSA +
F03B14.RZFDA + F03B14.RZECBF)

F03B11.RPCRCD Currency

See the dimensions below UserDefined1

See the dimensions below UserDefined2
See the dimensions below UserDefined3
See the dimensions below UserDefined4
See the dimensions below UserDefined5

Possible dimensions:
<not selected>
F03B11.RPICU = BatchNumber
F03B11.RPDOC = DocumentNumber
F03B11.RPDCT = DocumentType
F03B11.RPSFX = LineItem
F03B11.RPRMK = Remark
F03B11.RPUSER = UserID

8.2.7. Period End Inventory

Source field Mapped field
F4101.IMITM + F4111.ILTRUM Inventory Reference

F4101.IMDSC1 Description

Manual DataCollecor 2.0 Page 58 of 89

Source field Mapped field
F4111.ILUNCS Unit Price

F4111.ILTRQT Quantity

F4111.ILPAID Amount

F4111.ILDGL Last Purchase Date

F4111.ILDGL Last Movement Date

See the dimensions below UserDefined1

See the dimensions below UserDefined2
See the dimensions below UserDefined3
See the dimensions below UserDefined4
See the dimensions below UserDefined5

Possible dimensions:
<not selected>
F4101.IMUOM1 = BaseUnitOfMeasure
F4101.IMAITM = CatalogNumber
F4101.IMDSC2 = Description2
F4102.IBGLPT = GLCLass
F4101.IMLITM = ProductNumber
F4101.IMSRTX = SearchText
F4102.IBVEND = SupplierNumber

8.2.8. Sales History

Source field Mapped field
F4101.IMITM + F42119.SDUOM Inventory Reference

F42119.SDUORG Sales Quantity

F42119.SDAEXP Sales Amount

F42119.SDAEXP / F42119.SDUORG Latest Selling Price

See the dimensions below UserDefined1

See the dimensions below UserDefined2
See the dimensions below UserDefined3
See the dimensions below UserDefined4
See the dimensions below UserDefined5

Manual DataCollecor 2.0 Page 59 of 89

Possible dimensions:
<not selected>
F4101.IMUOM1 = BaseUnitOfMeasure
F4101.IMAITM = CatalogNumber
F4101.IMDSC2 = Description2
F4102.IBGLPT = GLCLass
F4101.IMLITM = ProductNumber
F4101.IMSRTX = SearchText
F4102.IBVEND = SupplierNumber

8.3. Navision

8.3.1. Trial balance

Source field Mapped field
[$G_L Account].No_ GL Account Number

[$G_L Account].Name Account Name

[$G_L Account].Income_Balance Account Type

Account Class

([$G_L Entry].Amount GL Account Current Period Beginning

Balance

([$G_L Entry].Amount GL Account Current Period Ending

Balance

The begin balance amount includes all entries (including year-end closing entries before
the start date). The ending balance amount includes all year-end closing entries for dates
before the start date and excludes all year-end closing entries for dates equal or after the
start date.

8.3.2. Journal entries

Source field Mapped field
[$G_L Entry].[Transaction No_] JE Number

[$G_L Entry].[G_L Account No] GL Account Number

Amount Prefix

[$G_L Entry].[Amount] Amount

[$G_L Entry].[Business Unit Code] Business Unit

Manual DataCollecor 2.0 Page 60 of 89

Source field Mapped field

[$G_L Entry].[Posting Date] Effective Date

See below Period

[$G_L Entry].[Posting Date] Entry Date

[$G_L Entry].[User ID] Preparer ID

or
[User].[Name]

[$G_L Entry].[Source Code] Source

or
[$Source Code].[Description]

[$G_L Entry].[Description] JE Description

See the dimension below UserDefined1

See the dimension below UserDefined2
See the dimension below UserDefined3
See the dimension below UserDefined4
See the dimension below UserDefined5

The period is the sequence number from the accounting periods table based on the
effective date field.

Possible dimensions:
<not selected>
[$G_L Entry].[Document Date] = Document Date
[$G_L Entry].[Document No_] = Document No_
[$G_L Entry].[Document Type] = Document Type
[$G_L Entry].[Global Dimension 1 Code] = Global Dimension 1 Code
[$G_L Entry].[Global Dimension 2 Code] = Global Dimension 2 Code
[$G_L Entry].[Journal Batch Name] = Journal Batch Name
[$G_L Entry].[Open] = Open (only Navision 5)
[$G_L Entry].[Quantity] = Quantity
[User].[Name]=RealUserName
[$Source Code].[Description]=ReadableDocumentType
[$G_L Entry].[Reason Code] = Reason Code
[$G_L Entry].[Reversed] = Reversed
[$G_L Entry].[Source Type] = Source Type
[$G_L Entry].[System-Created Entry] = System-Created Entry
[$G_L Entry].[Tax Area Code] = Tax Area Code
[$G_L Entry].[Tax Group Code] = Tax Group Code
[$G_L Entry].[VAT Amount] = VAT Amount

Manual DataCollecor 2.0 Page 61 of 89

The year-end closing entries will not be imported.

8.3.3. Creditors master data

Source field Mapped field
[$Vendor].No_ Account Number

[$Vendor].Name Name

Credit Limit

[$Vendor].Payment Terms Code Payment Terms

[$Vendor].Address + [$Vendor].Address 2 Address 1

[$Vendor].City Address 2

[$Vendor].Post Code Postal Code

[$Vendor].Country Code (Navision 4) Country

or
[$Vendor].Country_Region Code (Navision 5)

See the dimension below UserDefined1

See the dimension below UserDefined2
See the dimension below UserDefined3
See the dimension below UserDefined4
See the dimension below UserDefined5

Possible dimensions:
<not selected>
[$Vendor].Blocked = Blocked
[$Vendor].Contact = Contact
[$Vendor].Global Dimension 1 Code = DimensionCode1
[$Vendor].Global Dimension 2 Code = DimensionCode2
[$Vendor].E-Mail = Email
[$Vendor].Fax No_ = FaxNumber
[$Vendor].Home Page = HomePage
[$Vendor].Our Account No_ = OurAccountNumber
[$Vendor].Phone No_ = PhoneNumber
[$Vendor].Search Name = SearchName
[$Vendor].VAT Registration No_ = VATRegistrationNumber

Manual DataCollecor 2.0 Page 62 of 89

8.3.4. Debtors master data
Source field Mapped field
[$Customer].No_ Account Number

[$Customer].Name Name

[$Customer].Credit Limit (LCY) Credit Limit

[$Customer].Payment Terms Code Payment Terms

[$Customer].Address + [$Customer].Address 2 Address 1

[$Customer].City Address 2

[$Customer].Post Code Postal Code

[$Customer].Country Code (Navision 4) Country

or
[$Customer].Country_Region Code (Navision 5)

See the dimension below UserDefined1

See the dimension below UserDefined2
See the dimension below UserDefined3
See the dimension below UserDefined4
See the dimension below UserDefined5

Possible dimensions:
<not selected>
[$Customer].Blocked = Blocked
[$Customer].Contact = Contact
[$Customer].Global Dimension 1 Code = DimensionCode1
[$Customer].Global Dimension 2 Code = DimensionCode2
[$Customer].E-Mail = Email
[$Customer].Fax No_ = FaxNumber
[$Customer].Home Page = HomePage
[$Customer].Phone No_ = PhoneNumber
[$Customer].Search Name = SearchName
[$Customer].VAT Registration No_ = VATRegistrationNumber

Manual DataCollecor 2.0 Page 63 of 89

8.3.5. Accounts payable open items
Source field Mapped field
[$Vendor].No_ Account Number

[$Vendor].Name Name

[$Detailed Vendor Ledg_ Entry].Document Type Type

[$Detailed Vendor Ledg_ Entry].Vendor Ledger Entry No_ Reference

[$Vendor Ledger Entry].Posting Date Document Date

[$Vendor Ledger Entry].Due Date when type = 2 Payment Date

[$Detailed Vendor Ledg_ Entry].Amount (LCY) Amount

[$General Ledger Setup].LCY Code Local Currency

[$Detailed Vendor Ledg_ Entry].Amount CurrencyAmount

[$Detailed Vendor Ledg_ Entry].Currency Code Currency

See the dimension below UserDefined1

See the dimension below UserDefined2
See the dimension below UserDefined3
See the dimension below UserDefined4
See the dimension below UserDefined5

Possible dimensions:
<not selected>
[$Vendor Ledger Entry].Description = Description
[$Vendor Ledger Entry].Global Dimension 1 Code = DimensionCode1
[$Vendor Ledger Entry].Global Dimension 2 Code = DimensionCode2
[$Vendor Ledger Entry].External Document No_ = ExternalDocumentNumber
[$Vendor Ledger Entry].Due Date = PaymentDate
[$Vendor Ledger Entry].Source Code = SourceCode
[$Vendor Ledger Entry].Transaction No_ = TransactionNumber
[$Vendor Ledger Entry].User ID = UserID

Manual DataCollecor 2.0 Page 64 of 89

8.3.6. Accounts receivable open items
Source field Mapped field
[$Customer].No_ Account Number

[$Customer].Name Name

[$Detailed Cust_ Ledg_ Entry].Document Type Type

[$Detailed Cust_ Ledg_ Entry].Cust_ Ledger Entry No_ Reference

[$Cust_ Ledger Entry].Posting Date Document Date

[$Cust_ Ledger Entry].Due Date when type = 2 Payment Date

[$Detailed Cust_ Ledg_ Entry].Amount (LCY) Amount

[$General Ledger Setup].LCY Code Local Currency

[$Detailed Cust_ Ledg_ Entry].Amount CurrencyAmount

[$Detailed Cust_ Ledg_ Entry].Currency Code Currency

See the dimension below UserDefined1

See the dimension below UserDefined2
See the dimension below UserDefined3
See the dimension below UserDefined4
See the dimension below UserDefined5

Possible dimensions:
<not selected>
[$Cust_ Ledger Entry].Description = Description
[$Cust_ Ledger Entry].Global Dimension 1 Code = DimensionCode1
[$Cust_ Ledger Entry].Global Dimension 2 Code = DimensionCode2
[$Cust_ Ledger Entry].Due Date = PaymentDate
[$Cust_ Ledger Entry].Source Code = SourceCode
[$Cust_ Ledger Entry].Transaction No_ =TransactionNumber
[$Cust_ Ledger Entry].User ID = UserID

8.3.7. Period End Inventory

Source field Mapped field
[$Item].[No_] Inventory Reference

[$Item].[Description] Description

Manual DataCollecor 2.0 Page 65 of 89

Source field Mapped field

[$Value Entry].[Cost per Unit] Unit Price

[$Value Entry].[Invoiced Quantity] Quantity

[$Value Entry].[Cost Amount (Actual)] Amount

[$Value Entry].[Posting Date] Last Purchase Date

[$Value Entry].[Posting Date] Last Movement Date

See the dimension below UserDefined1

See the dimension below UserDefined2
See the dimension below UserDefined3
See the dimension below UserDefined4
See the dimension below UserDefined5

Possible dimensions:
<not selected>
[$Item].[Base Unit of Measure] = BaseUnitOfMeasure
[$Item].[Description 2] = Description2
[$Item].[Global Dimension 1 Code] = DimensionCode1
[$Item].[Global Dimension 2 Code] = DimensionCode2
[$Item].[No_ 2] = ItemNumber2
[$Item].[Price Unit Conversion] = PriceUnitConversion
[$Item].[Profit %] = ProfitPercentage
[$Item].[Search Description] = SearchDescription
[$Item].[Vendor Item No_] = VendorItemNumber
[$Item].[Vendor No_] = VendorNumber

8.3.8. Sales History

Source field Mapped field
[$Item].[No_] Inventory Reference

[$Item Ledger Entry].[Invoiced Quantity] * -1 Sales Quantity

[$Value Entry].[Sales Amount (Actual)] Sales Amount

[$Value Entry].[Sales Amount (Actual)] / Latest Selling Price

[$Value Entry].[Invoiced Quantity]

See the dimension below UserDefined1

Manual DataCollecor 2.0 Page 66 of 89

Source field Mapped field
See the dimension below UserDefined2
See the dimension below UserDefined3
See the dimension below UserDefined4
See the dimension below UserDefined5

Possible dimensions:
<not selected>
[$Item].[Base Unit of Measure] = BaseUnitOfMeasure
[$Item].[Description] = Description
[$Item].[Description 2] = Description2
[$Item].[Global Dimension 1 Code] = DimensionCode1
[$Item].[Global Dimension 2 Code] = DimensionCode2
[$Item].[No_ 2] = ItemNumber2
[$Item].[Price Unit Conversion] = PriceUnitConversion
[$Item].[Profit %] = ProfitPercentage
[$Item].[Search Description] = SearchDescription
[$Item].[Vendor Item No_] = VendorItemNumber
[$Item].[Vendor No_] = VendorNumber

8.4. Oracle Financials 11

8.4.1. Trial balance

Source field Mapped field
applsys.fnd_flex_values.flex_value GL Account Number

applsys.fnd_flex_values_tl.description GL Account Name

applsys.fnd_lookup_values.description Account Type

Account Class

gl.gl_balances.begin_balance_dr - GL Account Current Period Beginning Balance

gl.gl_balances.begin_balance_cr

( gl.gl_balances.begin_balance_dr + GL Account Current Period Ending Balance

gl.gl_balances.period_net_dr) -
( gl.gl_balances.begin_balance_cr +
gl.gl_balances.period_net_cr)

Due to the datamodel in Oracle Financials several values are fetched from so called "flex
values".

Multiple indirections are necessary to fetch the appropriate fields. For example the
AccountNumber is determined by selecting records from apps.fnd_id_flex_segments

Manual DataCollecor 2.0 Page 67 of 89

where the id_flex_code field equals 'GL#' and the id_flex_num equals
gl.gl_sets_of_books.char_of_accounts_id.

Subsequently records are selected from applsys.fnd_flex_values where the

applsys.fnd_flex_values.flex_value_set_id equals the
apps.fnd_id_flex_segments.flex_value_set_id.

8.4.2. Journal entries

Source field Mapped field
gl.gl_je_headers.je_header_id JE Number

gl.gl_code_combinations.SEGMENT# GL Account Number

Amount Prefix

gl.gl_je_lines.accounted_dr - Amount
gl.gl_je_lines.accounted_cr

gl.gl_sets_of_books.set_of_books_id Business Unit

gl.gl_je_lines.effective_date Effective Date

gl.gl_periods.period_num + "-" + Period

gl.gl_periodes.period_year

gl.gl_je_headers.creation_date Entry Date

applsys.fnd_user.user_name Preparer ID
or
(applsys.fnd_user.description or
hr.per_all_people_f.full_name)

gl.gl_je_headers.je_source Source
or
gl.gl_je_sources_tl.user_je_source_name

gl.gl_je_lines.description JE Description

See the dimensions below UserDefined1

See the dimensions below UserDefined2

Possible dimensions:
applsys.fnd_user.description (when empty
hr.per_all_people_f.full_name)=RealUserName

Manual DataCollecor 2.0 Page 68 of 89

 gl.gl_je_sources_tl.description=ReadableDocumentType

The SEGMENT# is determined through an indirect selection from

gl.gl_code_combinations where code_combination_id matches the
gl.gl_je_lines.code_combination_id and a match need to be made against the balancing
segment value.

Determining journal entries is done by combining records from the gl.gl_je_headers and
gl.gl_je_lines tables.

Date selection is performed by joining a journal entry with fields from the gl.gl_periods
table matching on the period_set_name field. Once the appropriate gl.gl_periods record
is determined the date match is done using the gl.gl_periods.period_year and
gl.gl_periods.period_num fields.

8.4.3. Creditors master data

Source field Mapped field
po.po_vendors.segment1 + "-" + Account Number
po.po_vendor_sites_all.vendor_site_code

po.po_vendors.vendor_name Name

Credit Limit

ap.ap_terms_tl.name* Payment Terms

po.po_vendor_sites_all.address_line1 Address 1

po.po_vendor_sites_all.city Address 2

po.po_vendor_sites_all.zip Postal Code

po.po_vendor_sites_all.country Country

See the dimensions below UserDefined1

See the dimensions below UserDefined2
See the dimensions below UserDefined3
See the dimensions below UserDefined4
See the dimensions below UserDefined5

Possible dimensions:
<not selected>
po.po_vendors.vendor_name_alt = AlternateSupplierName
po.po_vendors.customer_num = CustomerNumber

Manual DataCollecor 2.0 Page 69 of 89

 po.po_vendor_sites_all.email_address = Email
po.po_vendor_sites_all.fax_area_code + po.po_vendor_sites_all.fax = FaxNumber
po.po_vendor_sites_all.area_code + po.po_vendor_sites_all.phone =
PhoneNumber
po.po_vendors.attribute14 = SupplierHomePage
applsys.fnd_lookup_values.meaning = SupplierType
po.po_vendor_sites_all.vat_registration_num OR
po.po_vendors.vat_registration_num = VATRegistrationNumber

* depends on po.po_vendor_sites_all.terms_id OR po.po_vendors.terms_id

All creditors will be selected for Site Uses = "Pay"

8.4.4. Debtors master data

Source field Mapped field
ar.hz_cust_accounts.account_number + "-" + Account Number
ar.hz_cust_site_uses_all.location

ar.hz_parties.party_name Name

ar.hz_cust_prof_class_amts .overall_credit_limit* Credit Limit

ar.ra_terms_tl.name** Payment Terms

ar.hz_locations.address1 Address 1

ar.hz_locations.city Address 2

ar.hz_locations.postal_code Postal Code

ar.hz_locations.country Country

See the dimensions below UserDefined1

See the dimensions below UserDefined2

Possible dimensions:
<not selected>
ar.hz_parties.organization_name_phonetic = AlternateName
ar.hz_parties.tax_reference = VATRegistrationNumber

* depends on ar.hz_cust_site_uses_all.site_use_id OR
ar.hz_cust_accounts.cust_account_id

Manual DataCollecor 2.0 Page 70 of 89

** depends on ar.hz_cust_site_uses_all.payment_term_id OR
ar.hz_customer_profiles.standard_terms (site_use_id) OR
ar.hz_customer_profiles.standard_terms (cust_account_id)

All debtors will be selected for Business Purposes = "Bill To"

8.4.5. Accounts payable open items

Source field Mapped field
po.po_vendors.segment1 + "-" + Account Number
po.po_vendor_sites_all.vendor_site_code

po.po_vendors.vendor_name Name

ap.ap_invoices_all.invoice_type_lookup_code Type

('INV' OR PRE) + ap.ap_invoices_all.invoice_id Reference

ap.ap_invoices_all.invoice_date Document Date

ap.ap_payment_schedules_all.due_date Payment Date

ap.ap_invoices_all.invoice_amount - Amount
ap.ap_invoice_payments_all.amount

IF Local Currency <> Transaction Currency

ap.ap_invoices_all.base_amount -
ap.ap_invoice_payments_all.payment_base_amount

gl.gl_sets_of_books.currency_code Local Currency

ap.ap_invoices_all.invoice_amount - CurrencyAmount
ap.ap_invoice_payments_all.amount

ap.ap_invoices_all.invoice_currency_code Currency

See the dimensions below UserDefined1

See the dimensions below UserDefined2
See the dimensions below UserDefined3
See the dimensions below UserDefined4
See the dimensions below UserDefined5

Possible dimensions:
<not selected>
ap.ap_invoices_all.description = Description

Manual DataCollecor 2.0 Page 71 of 89

 ap.ap_invoices_all.doc_sequence_value = DocumentSequenceNumber
ap.ap_invoices_all.invoice_num = TransactionNumber
applsys.fnd_user.user_name = User
ap.ap_invoices_all.voucher_num = VoucherNumber

8.4.6. Accounts receivable open items

Source field Mapped field
ar.hz_cust_accounts.account_number + "-" + Account Number
ar.hz_cust_site_uses_all.location

ar.hz_parties.party_name Name

ar.ra_cust_trx_types_all.name Type

'TRX' + ar.ra_customer_trx_all.customer_trx_id Reference

OR
'REC' + ar.ar_cash_receipts_all.cash_receipt_id

ar.ra_customer_trx_all.trx_date Document Date

ar.ar_payment_schedules_all.due_date Payment Date

ar.ra_cust_trx_line_gl_dist_all.amount - Amount
ar.ar_receivable_applications_all.amount_applied

IF Local Currency <> Transaction Currency

ar.ra_cust_trx_line_gl_dist_all. acctd_amount -
ar.ar_receivable_applications_all.acctd_amount_applied_to

gl.gl_sets_of_books.currency_code Local Currency

ar.ra_cust_trx_line_gl_dist_all.amount - CurrencyAmount
ar.ar_receivable_applications_all.amount_applied

ar.ra_customer_trx_all.invoice_currency_code Currency

See the dimensions below UserDefined1

See the dimensions below UserDefined2
See the dimensions below UserDefined3

Possible dimensions:
<not selected>
ar.ra_customer_trx_all.ct_reference = Description
ar.ra_customer_trx_all.trx_number = TransactionNumber

Manual DataCollecor 2.0 Page 72 of 89

 applsys.fnd_user.user_name = User

8.4.7. Period End Inventory

Source field Mapped field
inv.mtl_system_items_b.segment1 Inventory Reference

inv.mtl_system_items_b.description Description

bom.cst_elemental_costs.standard_cost Unit Price

( inv.mtl_onhand_quantities_detail.primary_transaction_quantity Quantity
- inv.mtl_material_transactions.primary_quantity1)

(Quantity * bom.cst_elemental_costs.standard_cost) Amount

ap.ap_invoices_all.invoice_date Last Purchase Date

inv.mtl_material_transactions.transaction_date Last Movement Date

See the dimensions below UserDefined1

See the dimensions below UserDefined2
See the dimensions below UserDefined3
See the dimensions below UserDefined4

1)
uncosted and rollback

Possible dimensions:
<not selected>
inv.mtl_system_items_b.primary_unit_of_measure = BaseUnitOfMeasure
inv.mtl_system_items_tl.long_description = LongDescription
apps.fnd_lookup_values.meaning = UserItemType
po.po_approved_supplier_list.segment1 = VendorID

8.4.8. Sales History

Source field Mapped field
inv.mtl_system_items_b.segment1 Inventory Reference

ont.oe_order_lines_all.shipping_quantity Sales Quantity

Sales Amount
( IF gl.gl_sets_of_books.currency_code =
ar.ra_customer_trx_all.invoice_currency_code

Manual DataCollecor 2.0 Page 73 of 89

Source field Mapped field
THEN
ar.ra_cust_trx_line_gl_dist_all.amount
ELSE
ar.ra_cust_trx_line_gl_dist_all.acctd_amount )

( Latest Selling Price

IF gl.gl_sets_of_books.currency_code =
ar.ra_customer_trx_all.invoice_currency_code
THEN
ar.ra_cust_trx_line_gl_dist_all.amount
ELSE
ar.ra_cust_trx_line_gl_dist_all.acctd_amount ) /
(ar.ra_customer_trx_lines_all.quantity_invoiced *
(ont.oe_order_lines_all.shipping_quantity /
ont.oe_order_lines_all.ordered_quantity))

See the dimensions below UserDefined1

See the dimensions below UserDefined2
See the dimensions below UserDefined3
See the dimensions below UserDefined4
See the dimensions below UserDefined5

Possible dimensions:
<not selected>
inv.mtl_system_items_b.primary_unit_of_measure = BaseUnitOfMeasure
inv.mtl_system_items_b.description = Description
inv.mtl_system_items_tl.long_description = LongDescription
apps.fnd_lookup_values.meaning = UserItemType
po.po_approved_supplier_list.segment1 = VendorID

8.5. Oracle Financials 12

8.5.1. Trial balance

Source field Mapped field
applsys.fnd_flex_values.flex_value GL Account Number

applsys.fnd_flex_values_tl.description GL Account Name

applsys.fnd_lookup_values.description Account Type

Account Class

gl.gl_balances.begin_balance_dr - GL Account Current Period Beginning

Manual DataCollecor 2.0 Page 74 of 89

Source field Mapped field
gl.gl_balances.begin_balance_cr Balance

( gl.gl_balances.begin_balance_dr + GL Account Current Period Ending

gl.gl_balances.period_net_dr) - Balance
( gl.gl_balances.begin_balance_cr +
gl.gl_balances.period_net_cr)

The basic mechanism selecting from the Oracle Financials Financials 12 datamodel is
similar to that of the Oracle Financials Financials 11 mechanism. Subtle differences in
the indirection mechanism and some datamodel changes govern the differences.

8.5.2. Journal entries

Source field Mapped field
gl.gl_je_headers.je_header_id JE Number

gl.gl_code_combinations.SEGMENT# GL Account Number

Amount Prefix

gl.gl_je_lines.accounted_dr - Amount
gl.gl_je_lines.accounted_cr

gl.gl_sets_of_books.ledger_id Business Unit

gl.gl_je_lines.effective_date Effective Date

gl.gl_periods.period_num + "-" + Period

gl.gl_periodes.period_year

gl.gl_je_headers.creation_date Entry Date

applsys.fnd_user.user_name Preparer ID
or
(applsys.fnd_user.description or
hr.per_all_people_f.full_name)

gl.gl_je_headers.je_source Source
or
gl.gl_je_sources_tl.user_je_source_name

gl.gl_je_lines.description JE Description

Manual DataCollecor 2.0 Page 75 of 89

Source field Mapped field
See the dimensions below UserDefined1
See the dimensions below UserDefined2

Possible dimensions:
applsys.fnd_user.description (when empty
hr.per_all_people_f.full_name)=RealUserName
gl.gl_je_sources_tl.description=ReadableDocumentType

8.5.3. Creditors master data

Source field Mapped field
ap.ap_suppliers.segment1 + "-" + Account Number
ap.ap_supplier_sites_all.vendor_site_code

ap.ap_suppliers.vendor_name Name

Credit Limit

ap.ap_terms_tl.name* Payment Terms

ap.ap_supplier_sites_all.address_line1 Address 1

ap.ap_supplier_sites_all.city Address 2

ap.ap_supplier_sites_all.zip Postal Code

ap.ap_supplier_sites_all.country Country

See the dimensions below UserDefined1

See the dimensions below UserDefined2
See the dimensions below UserDefined3
See the dimensions below UserDefined4
See the dimensions below UserDefined5

Possible dimensions:
<not selected>
ap.ap_suppliers.vendor_name_alt = AlternateSupplierName
apps.po_vendors.customer_num = CustomerNumber
ar.hz_contact_points.email_address OR ap.ap_supplier_sites_all.email_address =
Email
ar.hz_contact_points.raw_phone_number OR
(ar.hz_contact_points.raw_area_code + ar.hz_contact_points.raw_phone_number)
= FaxNumber **

Manual DataCollecor 2.0 Page 76 of 89

 ar.hz_contact_points.raw_phone_number OR
(ar.hz_contact_points.raw_area_code + ar.hz_contact_points.raw_phone_number)
= PhoneNumber **
ap.ap_suppliers.attribute14 = SupplierHomePage
applsys.fnd_lookup_values.meaning = SupplierType
zx.zx_party_tax_profile.rep_registration_number = VATRegistrationNumber
(party_type_code = 'THIRD_PARTY_SITE' OR 'THIRD_PARTY')

* depends on ap.ap_supplier_sites_all.terms_id OR ap.ap_suppliers.terms_id

** depends on ar.hz_contact_points.phone_line_type

All creditors will be selected for Purpose (Site Using the Address) = "Payment"

8.5.4. Debtors master data

Source field Mapped field
ar.hz_cust_accounts.account_number + "-" + Account Number
ar.hz_cust_site_uses_all.location

ar.hz_parties.party_name Name

ar.hz_cust_prof_class_amts.overall_credit_limit* Credit Limit

ar.ra_terms_tl.name** Payment Terms

ar.hz_locations.address1 Address 1

ar.hz_locations.city Address 2

ar.hz_locations.postal_code Postal Code

ar.hz_locations.country Country

See the dimensions below UserDefined1

See the dimensions below UserDefined2

Possible dimensions
<not selected>
ar.hz_locations.address_lines_phonetic OR
ar.hz_parties.known_as = AlternateName
zx.zx_party_tax_profile.rep_registration_number = VATRegistrationNumber
(party_type_code = 'THIRD_PARTY_SITE' OR 'THIRD_PARTY')

* depends on ar.hz_cust_site_uses_all.site_use_id OR
ar.hz_cust_accounts.cust_account_id

Manual DataCollecor 2.0 Page 77 of 89

** depends on ar.hz_cust_site_uses_all.payment_term_id OR
ar.hz_customer_profiles.standard_terms (site_use_id) OR
ar.hz_customer_profiles.standard_terms (cust_account_id)

All debtors will be selected for Business Purposes = "Bill To"

8.5.5. Accounts payable open items

Source field Mapped field
ap.ap_suppliers.segment1 + '-' + Account Number
ap.ap_supplier_sites_all.vendor_site_code

ap.ap_suppliers.vendor_name Name

ap.ap_invoices_all.invoice_type_lookup_code Type

('INV' OR PRE) + ap.ap_invoices_all.invoice_id Reference

ap.ap_invoices_all.invoice_date Document Date

ap.ap_payment_schedules_all.due_date Payment Date

ap.ap_invoices_all.invoice_amount - Amount
ap.ap_invoice_payments_all.amount

IF Local Currency <> Transaction Currency

ap.ap_invoices_all.base_amount -
ap.ap_invoice_payments_all.payment_base_amount

gl.gl_ledgers.currency_code Local Currency

ap.ap_invoices_all.invoice_amount - CurrencyAmount
ap.ap_invoice_payments_all.amount

ap.ap_invoices_all.invoice_currency_code Currency

See the dimensions below UserDefined1

See the dimensions below UserDefined2
See the dimensions below UserDefined3
See the dimensions below UserDefined4
See the dimensions below UserDefined5

Possible dimensions:
<not selected>

Manual DataCollecor 2.0 Page 78 of 89

 ap.ap_invoices_all.description = Description
ap.ap_invoices_all.doc_sequence_value = DocumentSequenceNumber
ap.ap_invoices_all.invoice_num = TransactionNumber
applsys.fnd_user.user_name = User
ap.ap_invoices_all.voucher_num = VoucherNumber

8.5.6. Accounts receivable open items

Source field Mapped field
ar.hz_cust_accounts.account_number + '-' + Account Number
ar.hz_cust_site_uses_all.location

ar.hz_parties.party_name Name

ra_cust_trx_types_all.name Type

'TRX' + '-' + ar.ra_customer_trx_all.customer_trx_id Reference

OR
'REC' + '-' + ar.ar_cash_receipts_all.cash_receipt_id

ar.ra_customer_trx_all.trx_date Document Date

ar.ar_payment_schedules_all.due_date Payment Date

ar.ra_cust_trx_line_gl_dist_all.amount - Amount
ar.ar_receivable_applications_all.amount_applied

IF Local Currency <> Transaction Currency

ar.ra_cust_trx_line_gl_dist_all.acctd_amount -
ar.ar_receivable_applications_all.acctd_amount_applied_to

gl.gl_ledgers.currency_code Local Currency

ar.ra_cust_trx_line_gl_dist_all.amount - CurrencyAmount
ar.ar_receivable_applications_all.amount_applied

ar.ra_customer_trx_all.invoice_currency_code Currency

See the dimensions below UserDefined1

See the dimensions below UserDefined2
See the dimensions below UserDefined3

Possible dimensions:
<not selected>
ar.ra_customer_trx_all.ct_reference = Description

Manual DataCollecor 2.0 Page 79 of 89

 ar.ra_customer_trx_all.trx_number = TransactionNumber
applsys.fnd_user.user_name = User

8.5.7. Period End Inventory

Source field Mapped field
inv.mtl_system_items_b.segment1 Inventory Reference

inv.mtl_system_items_b.description Description

bom.cst_elemental_costs.standard_cost Unit Price

( Quantity
inv.mtl_onhand_quantities_detail.primary_transaction_quantity
- inv.mtl_material_transactions.primary_quantity1)

(Quantity * bom.cst_elemental_costs.standard_cost) Amount

ap.ap_invoices_all.invoice_date Last Purchase Date

inv.mtl_material_transactions.transaction_date Last Movement Date

See the dimensions below UserDefined1

See the dimensions below UserDefined2
See the dimensions below UserDefined3
See the dimensions below UserDefined4

1)
uncosted and rollback

Possible dimensions:
<not selected>
inv.mtl_system_items_b.primary_unit_of_measure = BaseUnitOfMeasure
inv.mtl_system_items_tl.long_description = LongDescription
apps.fnd_lookup_values.meaning = UserItemType
po.po_approved_supplier_list.segment1 = VendorID

8.5.8. Sales History

Source field Mapped field
inv.mtl_system_items_b.segment1 Inventory Reference

ont.oe_order_lines_all.shipping_quantity Sales Quantity

Sales Amount

Manual DataCollecor 2.0 Page 80 of 89

Source field Mapped field
( IF gl.gl_ledgers.currency_code =
ar.ra_customer_trx_all.invoice_currency_code
THEN
ar.ra_cust_trx_line_gl_dist_all.amount
ELSE
ar.ra_cust_trx_line_gl_dist_all.acctd_amount )

( IF gl.gl_ledgers.currency_code = Latest Selling Price

ar.ra_customer_trx_all.invoice_currency_code
THEN
ar.ra_cust_trx_line_gl_dist_all.amount
ELSE
ar.ra_cust_trx_line_gl_dist_all.acctd_amount ) /
(ar.ra_customer_trx_lines_all.quantity_invoiced*
(ont.oe_order_lines_all.shipping_quantity /
ont.oe_order_lines_all.ordered_quantity ))

See the dimensions below UserDefined1

See the dimensions below UserDefined2
See the dimensions below UserDefined3
See the dimensions below UserDefined4
See the dimensions below UserDefined5

Possible dimensions:
<not selected>
inv.mtl_system_items_b.primary_unit_of_measure = BaseUnitOfMeasure
inv.mtl_system_items_b.description = Description
inv.mtl_system_items_tl.long_description = LongDescription
apps.fnd_lookup_values.meaning = UserItemType
po.po_approved_supplier_list.segment1 = VendorID

8.6. SAP R/3 Classic GL - SAP ECC New GL

8.6.1. Trial balance

Source field Mapped field
SKB1.SAKNR GL Account Number

SKAT.TXT50 GL Account Name

IF(SKA1.XBILK = 'X' , 'B', 'R') Account Type

FAGL_011QT.TXT45 * Account Class

Manual DataCollecor 2.0 Page 81 of 89

Source field Mapped field
If account type = B GL Account Current Period Beginning Balance
GLT0.HSLVT + GLT0.HSLxx

If account type = R
GLT0.HSLxx

If account type = B GL Account Current Period Ending Balance

GLT0.HSLVT + GLT0.HSLxx

If account type = R
GLT0.HSLxx

See the dimensions below UserDefined1

xx is the number of a period

Possible dimensions:
SKB1.ALTKT = AlternativeAccountNumber

*) Only used in SAP ECC NewGL

8.6.2. Journal entries

For a non-leading ledger (only SAP ECC New GL) line items can also be extracted from
the table BSEG_ADD next to BSEG.

Source field Mapped field

BKPF.BELNR JE Number

BSEG.HKONT GL Account Number

BSEG.SHKZG Amount Prefix

BSEG.DMBTR * IF(BSEG.SHKZG = 'H' , -1, 1) Amount

BSEG.BUKRS Business Unit

BKPF.BUDAT Effective Date

BKPF.GJAHR + '-' + BKPF.MONAT Period

BKPF.CPUDT Entry Date

BKPF.USNAM Preparer ID
or

Manual DataCollecor 2.0 Page 82 of 89

Source field Mapped field
ADRP.NAME_TEXT

BKPF.BLART Source
or
T003T.LTEXT

BKPF.BKTXT + BSEG.SGTXT JE Description

BKPF.BSTAT BSTAT

BSEG.UMSKZ UMSKZ

See the dimensions below UserDefined1

See the dimensions below UserDefined2
See the dimensions below UserDefined3
See the dimensions below UserDefined4
See the dimensions below UserDefined5

Possible dimensions:
<not selected>
BSEG.ALTKT = AlternativeAccountNumber
BSEG.GSBER = BusinessArea
BSEG.KOSTL = CostCentre
BSEG.KSTRG = CostObject
BSEG.LIFNR = Creditor*
BSEG.KKBER = CreditControlArea*
BSEG.KUNNR = Debtor*
BSEG.AUFNR = InternalOrder
BSEG.SGTXT = ItemText
BSEG.NPLNR = NetworkNumber
BSEG.WERKS = Plant
BSEG.PRCTR = ProfitCentre
BKPF.TCODE = TransactionCode
BSEG.PROJK = WBSElement
BKPF.BLDAT = DocumentDate
ADRP.NAME_TEXT=RealUserName
T003T.LTEXT = ReadableDocumentName

*) These dimensions are not available in the table BSEG_ADD.

Manual DataCollecor 2.0 Page 83 of 89

8.6.3. Creditors master data
Source field Mapped field
LFB1.LIFNR Account Number

LFA1.NAME1 Name

Credit Limit

LFB1.ZTERM Payment Terms

LFA1.STRAS Address 1

LFA1.ORT01 Address 2

LFA1.PSTLZ Postal Code

LFA1.LAND1 Country

See the dimension below UserDefined1

See the dimension below UserDefined2
See the dimension below UserDefined3
See the dimension below UserDefined4
See the dimension below UserDefined5

Possible dimensions:
<not selected>
LFB1.ZSABE = Contact
LFA1.MCOD1 = SearchCode
LFA1.TELFX = Telefax
LFA1.TELF1 = Telephone
LFA1.LFURL = URL
LFA1.STCEG = VATRegistration

8.6.4. Debtors master data

Source field Mapped field
KNB1.KUNNR Account Number

KNA1.NAME1 Name

KNKA.KLIMG Credit Limit

KNB1.ZTERM Payment Terms

Manual DataCollecor 2.0 Page 84 of 89

Source field Mapped field

KNA1.STRAS Address 1

KNA1.ORT01 Address 2

KNA1.PSTLZ Postal Code

KNA1.LAND1 Country

See the dimension below UserDefined1

See the dimension below UserDefined2
See the dimension below UserDefined3
See the dimension below UserDefined4
See the dimension below UserDefined5

Possible dimensions:
<not selected>
KNB1.ZSABE = Contact
KNA1.MCOD1 = SearchCode
KNA1.TELFX = Telefax
KNA1.TELF1 = Telephone
KNA1.KNURL = URL
KNA1.STCEG = VATRegistration

8.6.5. Accounts payable open items

Source field Mapped field
BSXK.LIFNR Account Number

LFA1.NAME1 Name

T003T.LTEXT Type

BSXK.BELNR Reference

BSXK.BUDAT Document Date

(BSXK.ZFBDT OR BSXK.BLDAT OR BSXK.BUDAT) Payment Date

+ (BSXK.ZBD3T OR BSXK.ZBD2T OR BSXK.ZBD1T)

BSXK.DMBTR * IF(BSXK.SHKZG = H , 1 , -1) Amount

T001.WEARS Local Currency

Manual DataCollecor 2.0 Page 85 of 89

Source field Mapped field

BSXK.WRBTR * IF(BSXK.SHKZG = H , 1 , -1) CurrencyAmount

BSXK.WAERS Currency

BSXK.BUZEI LineItemNumber

BSXK.HKONT GL Account Number

BSXK.UMSKZ UMSKZ

BSXK.BSTAT BSTAT

See the dimensions below UserDefined1

See the dimensions below UserDefined2
See the dimensions below UserDefined3
See the dimensions below UserDefined4
See the dimensions below UserDefined5

Possible dimensions:
<not selected>
BSXK.GSBER = BusinessArea
BSXK.AUGDT = ClearingDate
BSXK.AUGBL = ClearingDocument
BSXK.SGTXT = Description
BSXK.BLDAT = DocumentDate
BSXK.BLART = DocumentType
BSXK.MONAT = FiscalPeriod
BSXK.GJAHR = FiscalYear
BSXK.AUFNR = OrderNumber
BSXK.BSCHL = PostingKey
BSXK.XBLNR = ReferenceDocument
BSXK.MWSTS = TaxAmount
BSXK.MWSKZ = TaxCode
BSXK.ZTERM = TermsOfPayment

Replace the character X in the table BSXK in A or I.

8.6.6. Accounts receivable open items

Source field Mapped field
BSXD.KUNNR Account Number

KNA1.NAME1 Name

Manual DataCollecor 2.0 Page 86 of 89

Source field Mapped field

T003T.LTEXT Type

BSXD.BELNR Reference

BSXD.BUDAT Document Date

(BSXD.ZFBDT OR BSXD.BLDAT OR BSXD.BUDAT) Payment Date

+ (BSXD.ZBD3T OR BSXD.ZBD2T OR BSXD.ZBD1T)

BSXD.DMBTR * IF(BSXD.SHKZG = H, -1, 1) Amount

T001.WEARS Local Currency

BSXD.WRBTR * IF(BSXD.SHKZG = H, -1, 1) CurrencyAmount

BSXD.WAERS Currency

BSXD.BUZEI LineItemNumber

BSXD.HKONT GL Account Number

BSXD.UMSKZ UMSKZ

BSXD.BSTAT BSTAT

See the dimensions below UserDefined1

See the dimensions below UserDefined2
See the dimensions below UserDefined3
See the dimensions below UserDefined4
See the dimensions below UserDefined5

Possible dimensions:
BSXD.GSBER = BusinessArea
BSXD.AUGDT = ClearingDate
BSXD.AUGBL = ClearingDocument
BSXD.SGTXT = Description
BSXD.BLDAT = DocumentDate
BSXD.BLART = DocumentType
BSXD.MONAT = FiscalPeriod
BSXD.GJAHR = FiscalYear
BSXD.AUFNR = OrderNumber
BSXD.BSCHL = PostingKey

Manual DataCollecor 2.0 Page 87 of 89

 BSXD.XBLNR = ReferenceDocument
BSXD.MWSTS = TaxAmount
BSXD.MWSKZ = TaxCode
BSXD.ZTERM = TermsOfPayment

Replace the character X in the table BSXD in A or I.

8.6.7. Period End Inventory

Source field Mapped field
MARA.MATNR + MBEWX.BWTAR Inventory Reference

MAKT.MAKTX Description

IF MBEWX.VPRSV = S Unit Price

MBEWX.STPRS / MBEWX.PEINH
IF MBEWX.VPRSV = V
MBEWX.VERPR / MBEWX.PEINH

MBEWX.LBKUM Quantity

MBEWX.SALK3 Amount

MKPF.BUDAT Last Purchase Date

MKPF.BUDAT Last Movement Date

See below * New Material

See the dimensions below UserDefined1

See the dimensions below UserDefined2
See the dimensions below UserDefined3
See the dimensions below UserDefined4
See the dimensions below UserDefined5

Possible dimensions:
<not selected>
MARA.MEINS = BaseUnitOfMeasure
MBEWX.LFGJA + - + MBEWX.LFMON = CurrentYearPeriod
MARA.MTART = MaterialType
MBEWX.VPRSV = PriceControl
MBEWX.PEINH = PriceUnit
MBEWX.BWKEY = ValuationArea
TCURM.BWKRS_CUS = ValuationLevel

Manual DataCollecor 2.0 Page 88 of 89

Delete the character X in the table name MBEWX or replace it with H.

*) A material is new when the initial entry cannot be determined in the SAP tables
MBEW/MBEWH: the Inventory as of (Year and Period) does not match an entry and no
previous entry is found in these tables. For that particular material the value X is stored
in the field New Material.

8.6.8. Sales History

Source field Mapped field
MARA.MATNR + ENT6041.BWTAR Inventory Reference

ENT6041.FKLMG Sales Quantity

IF ENT6041.KURSK < 0 Sales Amount

ENT6041.NETWR / (ENT6041.KURSK * -1)
ELSE
ENT6041.NETWR * ENT6041.KURSK
ENDIF

( Latest Selling Price

IF ENT6041.KURSK < 0
ENT6041.NETWR / (ENT6041.KURSK * -1)
ELSE
ENT6041.NETWR * ENT6041.KURSK
ENDIF
) / ENT6041.FKLMG

See the dimensions below UserDefined1

See the dimensions below UserDefined2
See the dimensions below UserDefined3
See the dimensions below UserDefined4

ENT6041 is a view of table VBRP

All billing documents are selected where VBRK.VBTYP <> U

Possible dimensions:
<not selected>
MARA.MEINS = BaseUnitOfMeasure
MARA.MTART = MaterialTyp
VBRK.BUKRS or ENT6041.WERKS = ValuationArea
TCURM.BWKRS_CUS = ValuationLevel

Manual DataCollecor 2.0 Page 89 of 89