Skip to content

External Files Interface

The purpose with this document is to describe the functionality in the External Files Interface, a general tool kit that handles input and output of text files

The processing of input and output files can be executed online or in batch mode. If batch mode is used then the files are read from or written to an FTP server using IFSCONNECT. See External File Assistant and Batch Process for more information.

The External Files toolkit introduces the following basic definitions:

  • External File Types

    A file type is more or less associated with one interface and describes the data that the interface can handle.

  • External File Templates

    A file template defines the file layout for input as well as for output files. This means that it is possible to create different file layouts that reads the data associated with one file type

Other external interfaces in IFS Financials External Currencies Interface, External Voucher Interface, External Supplier Invoice Interface and External Customer Invoice Interface are interacting with the External Files toolkit. When e.g. external supplier invoices are loaded through the External Supplier Invoice Assistant, it actually means that the External File Assistant is executed but with the restriction to only present valid template applicable for supplier invoices. All data/transactions will be handled and stored by the External Files toolkit, but of course each specific interface has to take care reading or creating the interface specific data.

System defined external file types and file templates will be created in the database during installation to  enable a quick start-up for the end-user. The External Files toolkit can be used to create user-defined file templates, tailored for a specific file layout.

Use this page when you want find get detailed knowledge about the External File Interface, i.e. what it consists of and how to use it.

Functional Overview

The External Files toolkit can be divided in two parts, Basic Data and General Utilities.

Basic Data related functionality

Functionality Description
External File Type Each External File Type specifies that data that the interface associated with the file type can handle
External File Template An External File Template describes how to interpret a file with data valid for a specific file type. It also specifies how to format data during creation of an output file.
External File Separators Specifies valid file separators in an external file
Default External File Template Enables definition of  a default file template per file type and company
Copy File templates can be copied.
Pre-Defined Definitions During installation of IFS Financials some pre-defined file types and file templates are defined. These file types and file templates are considered to be system defined. The purpose is to supply default definitions that can be used more or less instantly after the installation

General Utilities related functionality

Functionality Description
External File Assistant This is a general assistant that handles load of an input file or creation of an output file for any valid combination of external file type and external file template
External File Transactions The External File Transactions page makes it possible to view details of any performed input or output operation. It can also be used as a toolbox where each step in the input or output process can be executed
External File Identities A file template may contain definitions of control columns that during input will be used to build an identity string that is stored. Control column definitions are during input verified against stored identities to avoid reading and processing the same data more than once.
External File Log The log contains information for each step performed during the input or output process

The Input Process

The steps in the input process are,

  • Read the file and store the lines as External File Transactions
  • Unpack the file transaction lines into separate column using defined control statements for the file temp
  • Call an interface, API, that has the responsibility to fetch valid unpacked data and process it

The Output Process

The steps in the output process are,

  • Call an interface, API, that has the responsibility to create output data and define it as.External File Transactions.
  • Pack the data according to template instructions to file lines.
  • Write the file lines to an output file.

External File Types

An External File Type is normally associated with an interface in a component and it defines the data that can be handled by this interface.

An External File Type consists of the following basic data definitions,

  • General file type data
  • Record Types A file type must contain at least one record type.
  • Column Definitions for a Record Type Each record type has a definition of one or more possible columns.
  • Available Parameters Parameters can be associated with a file type. The parameters are used during input or output in the External File Assistant where the user supplies values for the parameters.
  • Parameter Sets A file type can have one or more parameter sets. One parameter set can be defined as the default one.
  • Parameters Per Parameter Set Available parameters are associated with a parameter set.

For analysis of defined file types, External File Types page can be used.

The following system defined file types are created during an installation/upgrade of IFS Financials,

External File Type Description
BankStatement Associated with the Bank Statement interface in Payment. Note that Bank Statement files can only be loaded via the External File Assistant
BudgetPeriod Associated with the budget interface in General Ledger, used to import or export budget period data from/to file
BudgetTemplate Associated with the Budget Process, used to import or export budget template information from/to file
BudgetTransaction Associated with the Budget Process, used to import or export budget template transactional data from/to file
BudgetYear Associated with the budget interface in General Ledger, used to import or export budget year data from/to file
CashPlanAdjustment Associated with Cash Planning, used to import or export Cash Plan Adjustments.
DescribeInput Internal file type in External Files, used when creating an example output file for a file template
EuSalesTax  
ExtAdjJournal Associated with Group Consolidation, used to import adjustment journals.
ExtCurrency Associated with the External Currency interface in Accounting Rules, used to load a file with currency rates to a company
ExtCustInv Associated with the External Customer Invoices interface in Invoice, used to load a file with external customer invoices.
ExtCustPayment Associated with the External Payment Interface in Payment, used to load a file with customer payments to a company
ExtFAObjectImport Associated with Importing Fixed Asset Objects using templates.
ExtFileImpEmp Internal file type in External Files used when creating an export or import of a file type and all related data.
ExtFileInsCreate Internal file type in External Files used when creating a file with insert instructions for a file type and all related data.
ExtMixedPayment Associated with the External Payment Interface in Payment, used to load a file with bank account balances and payments related to customer and supplier
ExtPayment This file type is used internally by the external interface in Payment.
ExtPosPayCheck Associated with the Supplier Check payment, used to export Positive Pay files.
ExtRepBal Associated with Group Consolidation, used to import reporting journals related to external reporting entities.
ExtSuppInv Associated with the External Supplier Invoices interface in Invoice, used to load a file with external supplier invoices.
ExtSuppPaymRet Used to handle supplier payment return file
ExtVoucher Associated with the External Vouchers interface in Accounting Rules, used to load a file with external vouchers.
GermanReturnTax Used to handle German specific return tax file
GrossIncome Associated with the Tax Ledger. Used for output purposes
JinsuiExport Used for export of Jinsui specific info
JinsuiImport Used for import of Jinsui specific info
MandateToPi Used for creation of mandate file to a payment institute
PlanUnitTransactions Associated with the Planning Unit interface. Used to import or export planning unit transactions.
RemoveTrans Internal file type in External Files to be used when ordering a job that removes transactions in the External File Transactions storage.
SAF-T Associated with the Standard Audit File For Tax File. Note that SAF-T files can only be created via the  Audit Interface Assistant.
SICORE Associated with the Tax Ledger. Used for output purposes of specific SICORE reports (Argentina).
SnapCons Associated with the Snapshot Export functionality in Consolidated Accounts.
TaxTempExpCreate Associated with Tax Ledger
TaxTempField Associated with Tax Ledger. Used for output/creation of tax reports

All these file types can be used in External File Assistant.

One external file type can be referenced by several external file template definitions.

External File Type

This page is used to view file type definitions that describes the contents of a file to be handled by the External Files toolkit.

To navigate to the page, select Details on one or more file types in the External File Types page.

Every file type needs at least one Record Type. Each record type has a detailed specification that defines the columns that are valid when interpreting a file line of this type.

Definitions for System Defined file types are pre-defined.

Record Type ID Description Record Set Identity First In Record Set Last In Record Set Mandatory Record Parent Record Type
H Supplier Invoice Head 1 Yes No Yes
N Supplier Invoice Installment 1 No No No I
I Supplier Invoice Item 1 No No Yes H
V Supplier Advance Invoice 1 No No No H
O One-Time Supplier Specific Information 1 No No No H
P Supplier Invoice Posting 1 No No No I
T Supplier Invoice Tax 1 No No No I
A Supplier Invoice 2 Yes Yes Yes

The table shows the record types for system defined file type ExtSuppInv.

This file type has two available record sets,

  1. Records set 1 is used for detailed specification of invoice header, invoice line, posting information, installment data and tax lines.
  2. Record set 2 is an all-in-one record type that can be used when each external invoice can be described on one single line.

Detailed information for the some of the columns in the detail section,

Column Description
Record Set Id Identity that can be connected to several Record Type IDs. The purpose is to be able to group several record types into one record set. A file can contain several record sets, each one described by one or several record type
First In Record Set Indicates the record type must be the first section in a file when a new record set is found. The check box is always selected if the file type contains only one record set
Last In Record Set Indicates if the record type must be the last section in a file when handling a record set. Options First In Record Set and Last In Record Set are selected if the file type contains only one record set
Mandatory Record Indicates if the record type is mandatory
Parent Record Type Optional. Defines the parent record type for the current record type and must refer to an existing record type. Parent Record Type cannot be the same as the Record Type ID. The field is used during the interpretation of a record set to sort the records in appropriate order
View Name Optional. Defines the view name to be used by the general output method. The general method for output is called External_File_Utility_API.Create_External_Output and can be defined as Api To Call on the Output Files tab for a file template. The general method will use the view name when extracting the record type related column data for output files. The view name cannot be defined manually
Input Package The field defined the package name to be used by the general input method. The general method for input is called External_File_Utility_API.Create_External_Input and can be defined as Api To Call on the Input Files tab for a file template. In this case the general method will try to access the standard methods New__and Modify__in the Input Package when the unpacked file data is processed. The input package cannot be defined manually

Commands in header section,

  • External File Template Will navigate to the External File Template detail page and show all file templates having a reference to the current file type (i.e. ExtSuppInv)
  • External File Type Parameter Definition Will navigate to a page where parameter data for the file type can be specified.
  • External File Load Parameters This command will be enabled if the interface that is associated with the file type has a parameter page with specific load parameters for that interface.

Commands in detail section,

External File Column Definition

Use this page to view valid column definitions for each record type in an external file type definition.

To navigate to this page, click on one or more record types in the External File Type page and select the command External File Column Definition.

System-defined file types usually don't require any modifications using this page since the column definitions are pre-defined.

Detailed Information,

Column Description
Column ID This is the identity of the column. This identity is referred to on the detail tab in the External File Template page.
Description Name or description of the column
Mandatory Determines whether the column is mandatory when the file type is used in a file template definition.
Destination Column Specifies the destination column in the External File Transaction storage. Possible values are C1-C80,  N1-N40, and D1-D20
Data Type Specifies what type of data the column can contain. Possible values are,
  • STRING
  • NUMBER
  • DATE

Command in header section,

  • External File Template

    Opens the External File Template detail page and show all file templates having a reference to the current file type (i.e. ExtSuppInv)

External File Type Parameter Definition

Use this page to define input and output parameters associated with a file type. The parameters are used when the execution of an input or output file is ordered, e.g., in the External File Assistant page.

Use the tab Available Parameters define the available input and output parameters associated with an external file type. Parameters are used by pages related to the external interface where an execution of an input or output file is ordered. The parameters used can be one, several, or all of the available parameters.

Information related to detail columns,

Column Description
Parameter Number This is the number of the file type parameter, defined as a number. The number also defines the input order when parameters are listed on the parameter page in the External File Assistant
Parameter ID This is the internal identity of the file type parameter. There are a few identities that can be recognized by the External Files toolkit. These are,
  • FILE_DIRECTION_DB
    This parameter can be used to define the file direction (1 for input and 2 for output), making it possible to define whether a parameter set containing this parameter applies to input or output file handling.
  • ORDER_BY
    This parameter can be used to define an order by statement
Description Name or description of the file type parameter. This field is supported by translation.
Browsable Field If this is selected, it is possible to get the value for the parameter through the file browser.
Help Text Help text for the parameter shown in the general parameter input dialog,available e.g. in the External File Assistant page. The field is supported by translation.
Validate Method Name of method, "<package_name>.<method_name>" to be used for validation of the parameter value during input. File type parameter identities can be used as parameters to the method, e.g. Currency_Type_API.Exist(COMPANY,CURRENCY_TYPE) where COMPANYand CURRENCY_TYPEwill be replaced by the actual input values of these parameters.
List of Values View Name of a view to be used as List Of Values view for this parameter in the External File Assistant
Enumerate Method Name of a method, "<package_name>.<method_name>" to be used to create an enumeration list for the parameter during input in the External File Assistant

External File Type Parameter Sets

Use the list Parameter Sets in the External File Type Parameter Definition page to define parameter sets associated with an external file type.

A parameter set makes it possible to define different combinations of available input or output parameters. The parameter sets can be referred to in External File Assistant where an import or export of an external file is ordered.

A parameter set can be used for both input and output files, but if the parameters for input files differ from those for output files, then different parameter sets must be defined.

Only one parameter set can be defined as the default one.

Commands from parameter sets list ,

  • External File Parameters Per Set

    Will navigate to the External File Type Parameter Set Definition page, that displays parameters associated with each parameter set for the current file type.

  • Create Parameters Per Set From Available Parameters

    This options can be used to create parameters in a parameter set, by adding all available parameters to the set.

External File Type Parameter Set Definition

Use this page to define the parameters that will be associated with a file type parameter set. Parameter sets can be referred to in external dialogs/assistants, whenever there is a need to import or export an external file.

Detailed Information,

Column Description
Parameter Number This is the number of the file type parameter that was defined in the External File Type Parameter Definition - Available Parameters page.
Description Name or description of the file type parameter. This field is not editable
Default Value This is the default value that will be displayed for the parameter as, for example, in the External File Assistant page. It can be a fixed value, it can refer to a system value or it can be defined with no value (in which case the field will appear empty)
Available system values are,
<USER_ID> - User id in current session
<COMPANY> - The current user's default company
<SYSDATE> - The system date at execution time
<CURR_TYPE> - Default currency rate type for the current company with respect to the base currency
<BASE_CURRENCY> - Base currency for the current company

If <COMPANY> is specified, then at runtime the current default company (for the current user) will be used as the default value. Note that when the company-related default values, <CURR_TYPE> and <BASE_CURRENCY> are evaluated, the company must be known. Company is normally specified via another parameter.
How the system values are evaluated depends on the implementation for each file type. For system-defined file types, a special method will be registered during installation/upgrade that can take care of the default parameter value evaluation via the parameter IDs and the specified default values. In this case, the parameter representing company (it usually has parameter ID COMPANY), is given the default value <COMPANY> or is given a fixed default value. In either case, the specific method will understand how to evaluate the value of company before evaluating the value of another parameter such as <CURR_TYPE>
Mandatory Param Select this option if the parameter should be mandatory in the External File Assistant page
Show At Load Select this option if the parameter should be displayed in the External File Assistant page
Editable At Load Select this option if the parameter should be possible to edit in the External File Assistant page

External File Template

External File Templates defines how to unpack/pack a file lines in/to a text file.

The following can be defined for a file template:

Valid file separators are defined separately.

For analysis of defined file templates External File templates page can be used.

The following system defined file templates are are created during an installation/upgrade of IFS Financials,

External File Template External File Type Description
BudgetPeriod BudgetPeriod Associated with the budget interface in General Ledger, defining a standard budget period file
BudgetYear BudgetYear Associated with the budget interface in General Ledger, defining a standard budget year file
CUP_BASSOC ExtCustPayment File Template used to read a customer payment file for Banker Associate File - Domestic Payments in JPY, Japan
CUP_BGMAX ExtCustPayment Template for bankgiro BGMAX
CUP_BGMAXQ ExtCustPayment Template for bankgiro BGMAX (No22,23)
CUP_DDAGNOR ExtCustPayment File Template used to read a customer payment file for Direct Debiting, in NOK, Norway
CUP_DDBGSEK ExtCustPayment File Template used to read a customer payment file for Bankgiro, Direct Debiting in SEK, Sweden
CUP_DDNAGSEK ExtCustPayment Template for direct debiting new autogiro, Sweden
CUP_DDPGSEK ExtCustPayment File Template used to read a customer payment file for Post giro, Direct Debiting in SEK, Sweden
CUP_DEDUCTION ExtCustPayment Generic File Template to read customer payment advice file with deductions.
CUP_KTL_FI ExtCustPayment File Template used to read a customer payment file for KTL Reference Domestic Payments in EUR, Finland
CUP_MANDATE_TO_PI MandateToPi Mandate to Payment Institute file template
CUP_MT940NLABN ExtCustPayment Template for MT940 ABN AMRO Netherland
CUP_OCRBGSEK ExtCustPayment File Template used to read a customer payment file for Bankgiro, OCR Domestic Payments in SEK, Sweden
CUP_OCRDKK ExtCustPayment File Template used to read a customer payment file for OCR Domestic Payments in DKK, Danmark
CUP_OCRNOR ExtCustPayment File Template used to read a customer payment file for OCR Domestic Payments in NOK, Norway
CUP_OCRPGSEK ExtCustPayment File Template used to read a customer payment file for Postgiro OCR Domestic Payments in SEK, Sweden
CUP_REMADV ExtCustPayment Generic File Template to read EDI remittance advice file with deductions.
CUP_TOTALIN ExtCustPayment Plusgirot Total In, Sweden
CashPlanAdjustment CashPlanAdjustment File Template used for Cash Plan Adjustments.
DescribeInput DescribeInput Internal file template used when creating an example file describing how to input data for a specific file template.
EuSalesTax EuSalesTax German EU Sales Tax file template
ExtAdjJournal ExtAdjJournal  External Consolidation Adjustment Journal 
ExtFAObjectImport ExtFAObjectImport Associated with Importing Fixed Asset Objects using templates.
ExtFileExpImp ExtFileExpImp Internal file template used when creating an export or import of a file type and all related data
ExtReportedBalances  ExtRepBal  Reported Balances 
GermanReturnTax GermanReturnTax German Return Tax file template
IIBBBuenosAiresCity GrossIncome File template used when creating a tax report in Tax Ledger for Buenos Aires City (Argentina)
IIBBBuenosAiresStatePurch GrossIncome File template used when creating a purchase tax report in Tax Ledger for Buenos Aires State (Argentina)
IIBBBuenosAiresStateSales GrossIncome File template used when creating a sales tax report in Tax Ledger for Buenos Aires State (Argentina)
IIBBCatamarcaState GrossIncome File template used when creating a tax report in Tax Ledger for Catamarca State (Argentina)
IIBBChacoState GrossIncome File template used when creating a tax report in Tax Ledger for Chaco State (Argentina).
IIBBCorrientesState GrossIncome File template used when creating a tax report in Tax Ledger for Corrientes State (Argentina).
IIBBMendozaState GrossIncome File template used when creating a tax report in Tax Ledger for Mendoza State (Argentina)
IIBBNeuquenState GrossIncome File template used when creating a tax report in Tax Ledger for Neuquen State (Argentina)
IIBBTucumanState GrossIncome File template used when creating a tax report in Tax Ledger for Tucuman State (Argentina)
JinsuiExport JinsuiExport Standard Jinsui Export File
JinsuiImport JinsuiImport Standard Jinsui Import File
MXP_MT940DE ExtMixedPayment File template used to load bank statement of accounts for MT940 Germany (Version of the MT940 swift file format used in Germany)
PlanUnitTransactions PlanUnitTransactions Associated with the Planning Unit interface. Standard planning transaction file template.
RemoveTrans RemoveTrans Internal file template used when ordering a job that removes transactions in the External File Transactions storage.
SAF-T Norwegian Template SAF-T Associated with the Standard Audit File For Tax File. This is used to create the SAF-T xml file.
SICORE SICORE Standard file template for SICORE output files in Tax Ledger
SRF_BGSWE ExtSuppPaymRet Bankgiro Return BGSWE
SRF_BR_237_CP ExtSuppPaymRet Brazilian 237-CP layout return file from bank
SRF_PGFOR ExtSuppPaymRet Plusgiro Foreign Return PGFOR
SRF_PGSWE ExtSuppPaymRet Plusgiro Domestic Return PGSWE
STDBNKSTAT BankStatement File template describing a standard bank statement file
STDCIN1 ExtCustInv File template to be used to read a file with external customer invoices, where each invoice is described by a header line, item lines, posting lines and tax lines.
STDCIN2 ExtCustInv File template to be used to read a file with external customer invoices where each invoice is described on one single line
STDCURR ExtCurrency File template describing a standard file with currency rates
STDFICURR ExtCurrency File template describing a standard currency rate file for the Finnish market
STDFRBNKSTAT BankStatement File template describing a standard bank statement file for the French market
STDPAY ExtPayment File template for standard payments
STDSIN1 ExtSuppInv File template to be used to read a file with external supplier invoices, where each invoice is described by a header line, item lines, posting lines, tax lines and installment lines
STDSIN2 ExtSuppInv File template to be used to read a file with external supplier invoices where each invoice is described on one single line
STDSIN3 ExtSuppInv Standard Supplier Invoice File A (eInvoice)
STDVOU ExtVoucher File template used to read a standard file with external vouchers
SnapConsTemp SnapCons File template describing a standard snapshot consolidation file
TaxTempExpCreate TaxTempExpCreate Tax Template for export of Create file
TaxTemplField TaxTemplField Filed template for tax file
TaxTemplate27 TaxTemplField Tax EU (VAT-UE/2, VAT-UE/A/2, VAT-UE/B/2, VAT-UE/C/1, VAT-UEK/2) Poland
TaxTemplate35 TaxTemplField Tax Sum VAT-7/11 Poland
TaxTemplate36 TaxTemplField Tax Sum VAT-7D/2 Poland
TaxTemplate53 TaxTemplField Austrian Return Tax 2009
TaxTemplate54 TaxTemplField German Return Tax 2009
TaxTemplate56 TaxTemplField Austrian EU Sales Tax 2010
TaxTemplate61 TaxTemplField German EU Sales List 2010
TaxTemplate62 TaxTemplField EU Sales List 2010
TucumanSICORE GrossIncome File template used when creating SICORE file for Tucuman state through the Tax Ledger

External File Templates

Use this page to get an overview of the external file templates used in External File Interfaces. Select one or more external file templates and then Details command to reach the details for the file template.

This shows all system defined external file templates. Some of these file templates are defined as active, but not all. The purpose is to by default only mark those file templates that are considered as general templates as active. A non-active template must be marked as active in order to be able to use it.

Commands:

  • Details

    Will navigate to the External File Template page, containing all details for a file template.

  • Copy File Template

    Can be used to create a new file template by copying an existing file template.

  • New File Template From File Type

    Can be used to create a new file template by using the definitions from an existing file type.

Copy File Template

This dialog is used to create a new external file template by copying all the definitions from an existing external file template.

To navigate to the page, select the command Copy File Template ,on one file type in the External File Templates page.

Using this dialog, you will create the following:
- A new file template with general data. - Template data related to the input file. - Template data related to the output file. - File template control data related to input and output files. - Column detail data.

When  the new template has been created, you can modify it in the External File Template page. Remember to check if the file controls has to be modified or removed in the External File Template Controls page.

To make it usable, please check that the template definition is valid, performed in the template detail page.

New File Template From File Type

Use this dialog to create a complete external file type definition by copying all definitions from an existing external file type. To navigate to the page, select New File Template From File Type command in the External File Templates page.

Using this dialog, you will create the following:
- A new file template with general information, defined for a semicolon separated file. - File template details.

Note that it is possible to create details only for mandatory file type columns if required.

When  the new template has been created, you can modify it in the External File Template page.

To be able to use the new template you have to create information on the tabs Input Files and/or Output Files in this detail page.

Remember to check if you have to create any information in the page External File Template Controls.

The last step will be to check if the template definition is valid, performed in the template detail page.

External File Template

Use this page to define the file layout for a file related to a specific File Type. For input files this means how to interpret a file line in the file. For output files it means how to format an output file line.

To navigate to the page, select one or more file templates in the External File Templates page, then go to Details .

One example is the predefined file type ExtSuppInv that is referred to by two different pre-defined file templates: the STDSIN1 (Standard Supplier Invoice File with record types H,I,P,T,N,V,O) and the STDSIN2 (Standard Supplier Invoice File with record type A).

Available information,

Note the following:

  • The option Valid Definition is selected if the template definition is valid. The validation can be performed with a command.
  • The option Active Definition must be selected if the file template should be active. Some of the pre-defined system file templates are active, some are not.
  • The option System Defined is selected if the file template has been defined during installation and is thus regarded as a system template.

External File Template - General Information

The tab contains the following,

Field Description
File Format Separated if the columns in the file are separated by a specified string
Fixedif the columns have fixed positions
Separator ID Specify the identity for field separator string if the columns in the file are separated. These separator id's are defined in page External File Separator
Description Name or description of the separator id
Text Qualifier The columns in the file are surrounded by this string. One example is to specify " (quotation mark).
Decimal Symbol Used for columns with data type NUMBER. Specify sign for decimal symbol as specified in the file (normally comma or point). This definition can be overridden on the detail level
Denominator Used for columns with data type NUMBER. Specify number of decimals in format 10, 100, 1000 etc, e.g. if the column in file contains the value 15025 and denominator is specified as 100, then the unpacked value will be 150,25. This definition can be overridden on the detail level.
Date Format Used for columns with data type DATEand defines the default date format for all date columns in the file, e.g. YYYY-MM-DD. Note that the date format must be able to interpret with Oracle date functions. This definition can be overridden on the detail level.
Date NLS Calendar Used for columns with data type DATEand defines the Date NLS Calendar. One example of value in this column is Japanese Imperial, to specify that the column include Japanese year

External File Template - Information on tab Input Files

Use this tab to define input file parameters for a specific file template. By defining these parameters, it will be possible to use the file template when processing input files.

Field Description
Log Invalid Lines Select this option if you want to log the lines that are considered invalid according to External File Control. If this option is not selected, the invalid file lines will be skipped and they will not be logged
Log Skipped Lines Select this option if you want to log the lines that are to be skipped according to External File Control. If this option is not selected, skipped lines will never be logged
Abort Immediately Select this option if the process should be aborted when an error is detected. The existence of an invalid line, for example, is an error
Skip All Blanks Select this option if all blanks in a column should be skipped
Skip Initial Blanks Select this option if initial blanks in a column should be skipped
File Name Name of the file to be loaded. The file name is the default file name suggested when the file template is used to process an input file. Note that the name should never contain a file path
Load File Type List This is the list of file types that will be available when a user is browsing for an input file. In other words, the value in this field controls the list of file types available in the standard client file browser.
The file type list is specified like this: name of file type^file extension[^name of file type^file extension]....

E.g. All Types^*^Voucher Files^vou
Character Set Used to specify the character that was used when the file was created.
Allow Record Set Repeat If this option is selected, then a record set can appear several times in the file. If this is not selected, a record set can only appear once in the file. In the latter case, a file may still contain several different record sets, but each record set may only appear once
Allow One Record Set Only If this option is selected, only one record set is allowed in the file
Remove Days Specify the number of days, beginning with the load date, after which the loaded transactions should be removed. Use the file type / file template RemoveTransto start the removal of loaded transactions in the External File Assistant.
Remove Completely Select this option if the file load header and file log information should be removed when the transactions are removed
Api To Call Specifies the method to be called after the file is loaded and unpacked. This method, normally placed in an external interface package, has to take care of unpacked file data so use it appropriately.
It is possible to specify External_File_Utility_API.Create_External_Input if the general input method should be used. When using this method, you need to specify a View Name and Input Packagefor the corresponding Record Type in the associated external file type definition. The columns specified on tab File Template Details must correspond to columns used in the general method New__ or Modify__ in the specified input Package
Api Before Unpack Specifies a method to be called before the file is unpacked. This method can be a method that modifies loaded data so it can be handled by the normal unpacking routine.
There is one "standard" method that can be used: External_File_Utility_API.Merge_File_Lines (<file_line LIKE ':6%'>).For this method the parameter is a condition to use in the methods SQL statement

All records selected are regarded as "master" records. All other records between these "master" records are merge to the "master" record.

There is also one other "standard" method that can be used: External_File_Utility_API.Merge_File_Skipped_Lines

All lines are checked according to control rules specified in External Files Control page.

All lines that not fulfill these rules, are merged to the first record before the line. (Useful for XML files)

Any Procedure with following parameters can be used,

Some_API.Method(
info_ IN OUT VARCHAR2,
load_file_id_ IN NUMBER )

The two parameters info_ and load_file_id_ are mandatory. It's possible to have 3 optional parameters.
Api After Unpack Specifies a method to be called after the file is unpacked. This method can be a method that modifies loaded data that can't be done by the normal unpacking routine.

External File Template - Information on tab Output Files

Use this tab to define output file parameters for a specific file template. By defining these parameters, it will be possible to use the file template when processing output files.

Field Description
File Name Name of file to be created.
Name Option Defines an output file name option (None, AddLoadId, AddDateor AddLoadIdAndDate). If any option other than Noneis selected, the option will be used to define a file suffix that will be added to the specified file name. For example, if the file name is MyFile.txt and the option AddDateis specified, then the created file will have the name MyFile20021224.txt
Number Out Fill Value For columns with a fixed length that contain numbers, this is the value used to fill the left-most position
Overwrite Server File Only applicable for batch processing. Select this check box if overwriting an existing file is allowed
Create Header Select this option if a header for the columns should be created in the output file.
Character Set Used to specify the character that should be used when creating the file.
Create XML If this is checked, the resulted output will be a XML file.
Style Sheet Name Name of the Style Sheet which is used to transform the xml. This is not mandatory.
Import Option to import a Style Sheet for the template. Disabled for System Defined templates.
Export Option to export the Style Sheet from the template.
Clear Option to clear the Style Sheet from the template. Disabled for System Defined templates.
Api To Call Specifies the method to be called that will generate output transaction data before the data is packed to lines and the lines are written to a file. It is possible to specify External_File_Utility_API.Create_External_Output if the general output method is going to be used. When using this method, you have to specify a View Name on the corresponding Record Type
Remove Days Specify the number of days, beginning with the load date, after which the created transactions can be removed. Use the file type and file template RemoveTransto start the removal of loaded transactions in the External File Assistant
Remove Completely Select this if the file load header and file log information should be removed when the transactions are removed

External File Template - Information on tab Detail

Use this tab to define how to interpret a specific input file layout or how to format an output file layout that is related to a specific file type.

One example is the pre-defined file type ExtSuppInvthat is referred to by two different pre-defined file templates: the File Template Id STDSIN1(Invoice file with header, item, posting, tax, installments, advance invoice information, one-time address information on different lines) and the File Template Id STDSIN2(Standard Supplier Invoice File with record type A).

This tab contains the following detailed information,

Column Description
Record Type ID Identity of the record type that is associated with the row. The available record types are those that have been previously defined for the Record Type Identity in the file type definition
Column ID The available columns are those that have been previously defined for the Record Type Identity in the file type definition
Description Name or description of the column. This field cannot be edited. The description is associated with the Column ID and will be defined automatically
Data Type Specifies the type of data in the column. This field cannot be edited. The data type is associated with the Column ID and will be defined automatically
Column No Used for file definitions where the columns are separated by a separator string. This is the number of the column position, from left to right, where the column appears in the file line.
For example, if the file line is M;123999;20021231 then M is column number 1, 123999 is column number 2 and 20021231 is column number 3.
Start Position Used for file definitions where columns have fixed positions. This is the number of the column position, from left to right, where the column starts.
For example, if the file line is M12399920021231 then Mhas start position 1, 123999 has start position 2 and end position 7, 20021231 has start position 9
End Position Used for file definitions where columns have fixed positions. This is the number of the column position, from left to right, where the column ends.
For example, if the file line is M12399920021231  then Mhas end position 1, 123999 has start position 2 and end position 7, 20021231 has end position 15
Date Format Used for columns with data type DATE. Defines the default date format for columns in the file, e.g. YYYY-MM-DD. Note that the date format must be able to interact with Oracle date functions and thus has nothing to do with regional settings. If no value is entered in this field, the default date format from the file template header is used.
Denominator Used for columns with data type NUMBER. Specifies the number of decimals using the format 10, 100, 1000, etc. For example, if the column in the file contains the value 15025, and the denominator is specified as 100, then the unpacked value will be 150,25. If no value is entered in this field, the default denominator from the file template header is used
Detail Functions Displays column functions associated with the file template column described by the current detail row. If the value in the column starts with the character *, then this means that there are more than one function associated with the column.
Use command Function Details to open a dialog where column function definitions are maintained.
Control Column If this column should be treated as a control column, then this option should be selected. A control column defines a column that, along with other control columns for the record type, is used to verify if the value/identity defined by these columns has already been handled by the external interface.
Destination Column Specifies the destination column in the result table. Possible values are C1-C70,  N1-N30, and D1-D20. This field cannot be edited
Hide Column If this columns should be hidden in page External File Transactions, then this option should be selected.
Max Length Specify maximum allowed length for this column

Commands

Header,

Input Files tab options,

Output Files tab options,

Detail tab options,

  • Function Details

    Opens the dialog Detail Functions, used to define column functions associated with the column described by the marked detail row.

External File Template Control

Use this page to specify how to control the read/write process of an external file. You can navigate to this page from the command External File Template Control in the Input Files or Output Files tab in the External File Template page.

The specified control records are used on each line to determine whether the line satisfies the specified control conditions. You can specify skip conditions or satisfy conditions.

You can also group together several conditions that all must be met to in order for a line to be considered as a skip or satisfy candidate.

If only satisfy conditions are specified then this means that all lines that satisfies the criteria are considered as valid, all other lines are skipped.

Skip conditions can be useful when you want to make sure that some specific lines are always skipped while the other ones are considered as valid.

Detailed information for Input File Templates,

Column Description
Record Type ID Identity of the record type for which the control statements apply. This identity is returned from the control process when conditions are satisfied, meaning that if a control record with record type=1 satisfies the file line then the file definition details related to record type=1 will be used to unpack the line. It is possible to have different controls on different record types, e.g., a header record has to start with the string H in 1st position and a detail record must start with the string D in 1st position
Row No Sequence number for the control records
Group No Grouping number for the control records. Records with the same Group No are controlled together
Condition Satisfyor Skip.
Satisfymeans that the file line is used if all the conditions of the satisfy statements for a group are satisfied. For example, a file line is considered valid if position 1-2 contains the string XX

Skipmeans that the file line is not used if all the conditions of skip statements of a group are satisfied. For example, a file line is considered skipped (not used) if position 1-2 contains the string XX.
Column No Specifies the column (in a separated file) on which the control should be applied. It is the number of the location (from left to right) where the column appears in the file line.
Start Position Specifies the first file line position on which the control should be applied
End Position Specifies the last file line position on which the control should be applied
Control String Defines the string that should be controlled on each file line
No Of Lines Specify number of physical file lines that should be regarded as one record when unpacking the file.

Detailed information for Output File Templates,

Column Description
Record Type ID Identity of the record type for which the control statements apply. This identity is returned from the control process when conditions are satisfied, meaning that if a control record with record type=1 satisfies the file line then the file definition details related to record type=1 will be used to unpack the line.  It is possible to have different controls on different record types, e.g., a header record has to start with the string H in 1st position and a detail record must start with the string D in 1st position
Row No Sequence number for the control records
Group No Grouping number for the control records. Records with the same Group No are controlled together
Condition Satisfyor Skip
Satisfymeans that the file line is used if all the conditions of the satisfy statements for a group are satisfied. For example, a file line is considered valid if position 1-2 contains the string XX

Skipmeans that the file line is not used if all the conditions of the skip statements for a group are satisfied. For example, a file line is considered skipped (not used) if position 1-2 contains the string XX
Control String Defines the string that should be controlled on each file line.
Destination Column Specifies the destination column that is considered the control column in the result table. During the creation of an output file, data is defined in destination columns in the external files framework. Generated values in these columns can be used to verify if the line is valid or if it should be skipped before performing the write operation. Possible values are C1-C70, N1-N30, and D1-D20

External Files Function Details

Use the dialog to specify functions to be associated with the column on the currently marked template detail row. The purpose is to make it possible to perform advanced operations for a template column, in one or several steps, thus drastically increasing the number of files that can be handled by External Files.

Detailed information,

Column Description
Function No Function number. Functions are processed in ascending order
Main Function Function to be executed in a specific detail step
Function Arguments Arguments to use on the main function. The arguments could be other functions (called sub functions).
Functions can not be arguments to a sub function.

The argument could be a string defined as "ABC" or 'ABC'

The argument could also be defined as CHR(34) for " and CHR(39) for ' and CHR(32) for space etc

E.g. we want to create a template that can handle a file line like the following:

:20:6441347/0021178:25:479468117:28:15901/01

One task is to find the account number in the file line. We know that the account number always appears after the identifier :25: and that a blank character always succeeds the account number. The instructions in the dialog layout will do the trick. If we look at this more in detail:

Function No Main Function Function Argument Description Result
1 FIND_STRING LINE_REF,":25:" Search for the position of :25: in the loaded file line 21
2 ADD DETAUL_REF(1),"4" Add 4 to the result of function 1. This gives the first position of the account number. 25
3 FIND_STRING LINE_REF," ",DETAIL_REF(2) Search for the first blank character after position 25 (the result of step 2). This gives us the last position in the account number plus 1. 34
4 SUBTRACT DETAIL_REF(3),DETAIL_REF(2) Subtract 25 (the result of step 2) from 34 (the result of step 3).
This gives the length of the account number.		 10
5 SUB_STRING LINE_REF,DETAIL_REF(2),DETAIL_REF(4) Now extract 10 characters (result of step 4) from the file line starting on position 25 (result of step 2). This will give us the account number. 479468117

The destination column will contain the result of the last step.

Valid functions are

Function Id Syntax Description
ABS_NUMBER ABS_NUMBER(SourceText) Returns the ABS value
ADD ADD(NumberA,NumberB) Add NumberAto NumberB.
ADD_LINE_FEED_FIRST ADD_LINE_FEED_FIRST(SourceText) Add a line feed (CHR(13)-CHR(10)) in the beginning of a value
ADD_LINE_FEED_LAST ADD_LINE_FEED_LAST(SourceText) Add a line feed (CHR(13)-CHR(10)) at the end of a value
CHANGE_SIGN CHANGE_SIGN(Number) Change sign on a number value
COLUMN_REF COLUMN_REF(ColumnId[,RecordType]) A reference to a column on the same record type
CONCATENATE CONCATENATE(TextA,TextB[,TextC[,TextD]]) Concatenate TextB (TextC and TextD) to TextA.
CURRENT_BASE_CURRENCY CURRENT_BASE_CURRENCY[(SourceText)] Fetch the base currency. SourceText can be a company. If omitted the current company will be used.
CURRENT_COMPANY CURRENT_COMPANY Fetch the current company.
CURRENT_DATE CURRENT_DATE([NoOfDaysPlusOrMinus]) Fetch the current date (SYSDATE Plus/Minus no of days)
CURRENT_DATE_TIME CURRENT_DATE_TIME Fetch the current date and time (SYSDATE)
CURRENT_LOAD_FILE_ID CURRENT_LOAD_FILE_ID Fetch the current load file id.
CURRENT_USER CURRENT_USER Fetch the current user.
DESTCOL_REF DESTCOL_REF(DestinationColumn) A reference to a destination column on the same record type
DETAIL_REF DETAIL_REF(DetailRow) A reference to another detail row on the same file template detail.
DIVIDE DIVIDE(Numerator,Denominator) Divide Numerator with Denominator.
FIND_ONE_OF FIND_ONE_OF(SourceText,SearchTexts[,MaxSearchPos]) Find one of specified values. Values to find are specified in SearchTexts separated by ; (semicolon).
Example: We want to find the position of RD or RC. SearchTexts will be RB;RC;

MaxSearchPos is the maximum position to search for the values.
FIND_STRING FIND_STRING(SourceText,SearchText[,Position[,Occurance]] Find one value.
IF_ELSE_CONDITION IF_ELSE_CONDITION(SourceText,Condition,CompareTo,TrueValue,FalseValue,[ExitIfTrue]) Compare a SourceText with CompareTo using Condition and returns TrueValue if TRUEand FalseValue if FALSE.
Possible conditions: <, <=, =, !=, >, >=, LIKE, NOTLIKE.For testing if a value is empty, use condition IS and CompareTo NULL.
For testing if a value is not empty, use condition ISNOT and CompareTo NULL.When argument ExitIfTrue is set to EXIT_TRUE and the condition is true, the chain of steps will be exited and the actual step result will be returned
IF_ELSE IF_ELSE(SourceText,CompareTo,TrueValue,[CompareTo,TrueValue],FalseValue,[ExitIfTrue]) Compare a SourceText with CompareTo and returns TrueValue if TRUE and FalseValue if FALSE.
When argument ExitIfTrue is set to EXIT_TRUE and the condition is true, the chain of steps will be exited and the actual step result will be returned.
IS_NUMBER IS_NUMBER(SourceText) Returns 1 if the value is numeric else returns 0
LAST_DAY_IN_MONTH LAST_DAY_IN_MONTH(SourceText,DateFormat) Returns the last day in the month.
LEFT_PAD LEFT_PAD(SourceText,Length[,Set]) Pad values to left of a string.
Example: We have a value 123 and want to fill out zeroes to the left and the result should have a length of 10 positions. Length 10 and Set "0" will result in 0000000123.

If Set is omitted spaces will be used.
LEFT_TRIM LEFT_TRIM(SourceText[,Set]) Trim a string from left.
Removes values from left that has the value specified in Set.

Example: We have a value 000000123 and want to remove all zeroes to the left. Specify Set to "0"and the result will be 123.
LENGTH LENGTH(SourceText) Returns the length of a string.
LINE_REF LINE_REF A reference to the loaded file line.
LOOP_MERGE LOOP_MERGE(MergeSeparator) If a file line (merged or loaded) contains line feed, the line could be looped "line by line" using this column function.
Then other column functions can be executed for each line.
The final return result will be the result from each line separated by the value specified in MergeSeparator.
MULTIPLY MULTIPLY(NumberA,NumberB) Multiply NumberA with NumberB.
PARAMETER_PUT PARAMETER_PUT(ParameterName,Value) Put the value to a specified parameter. Can be used when a value is found in the file and normally is entered in a parameter.
PARAMETER_REF PARAMETER_REF(ParameterName) A reference to a parameter (specified for the file type)
REMOVE_NOT_NUM REMOVE_NOT_NUM(SourceText[,ExceptCharacters]) Removes all characters that are not numeric
REPLACE REPLACE(SourceText,SearchText[,ReplacementText]) Replace a value in a string.
RIGHT_PAD RIGHT_PAD(SourceText,Length[,Set]) Pad values to right of a string.
Example: We have a value 123 and want to fill out zeroes to the right and the result should have a length of 10 positions.

Length 10 and Set "0" will result in 1230000000. If Set is omitted spaces will be used.
RIGHT_TRIM RIGHT_TRIM(SourceText[,Set]) Trim a string from right.
Removes values from right that has the value specified in Set.

Example: We have a value 1230000000 and want to remove all zeroes to the right. Specify Set to "0" and the result will be 123.
ROUND_NUMBER ROUND_NUMBER (SourceText[,NoOfDecimals]) Round a numeric value to specified no of decimals
ROW_NUMBER ROW_NUMBER Returns the row number from the current record
SET_DEFAULT_VALUE SET_DEFAULT_VALUE(DefaultValue) Set a default value to a column that is null.
SIGN_NUMBER SIGN_NUMBER(SourceText) Returns 1 when the value is positive and -1 when the value is negative
SUBTRACT SUBTRACT(NumberA,NumberB) Subtract NumberB from NumberA.
SUB_STRING SUB_STRING(SourceText,StartPos[,NoOfCharacters]) Returns a part of a string (from StartPos and NoOfCharacters positions)
TO_CHAR TO_CHAR(SourceText[,Format]) Converts a value from number to character in any format.
TO_LOWERCASE TO_LOWERCASE(SourceText) Converts every character in a string to lowercase.
TO_NUMBER TO_NUMBER(SourceText[,Format]) Converts a value from character in any format to number.
TO_UPPERCASE TO_UPPERCASE(SourceText) Converts every character in a string to uppercase.
TRANSLATE TRANSLATE(SourceText,SearchValues,ReplacementValues) Translate SearchValues in SourceText with ReplacementValues.
Example: We want to convert every character that has value A with 1 and every character that has value B with 2 and every character that has value C with 3. Specify ABC in SearchValues and 123 in ReplacementValues.
TRUNC_NUMBER TRUNC_NUMBER  (SourceText[,NoOfDecimals]) Truncate a numeric value to specified no of decimals
FIND_X_ATTR FIND_X_ATTR(SourceText[,TagIdentity][,TagStart][,TagEnd][,ValueEnd]) To find a value for a file in XML format.
Example: If looking for a value for a tag KZ061 in a string like this <KZ061>0000000006990.49</KZ061>

Use main function FIND_X_ATTR with function arguments LINE_REF,'KZ061'

For a file in XML format External_File_Utility_API.Merge_Skipped_File_Lineshas to be specified in column API Before Unpack on tab Input Files in page External File Template Definition.

External File Separators

Use this page to define the valid file separators that can be referred to in the external file templates (assuming that the template defines a separated file).

This page contains the following detailed information,

Column Description
Separator ID This is the identity of the file separator
Description Name or description of the file separator. This field is mandatory. This field is supported by the translation
Separator Separator value. This can be a single character string, or a string containing several characters. If possible, the separator string will be translated to an ASCII value during commit.
Separator Ascii Separator ASCII value, e.g., if separator='#' then Separator ASCII=35
If the Separator is a string with one single character, then the matching Separator ASCII value will automatically be defined during commit. If the Separator is a string with several characters, then the Separator ASCII string will be empty since it is not possible to describe this string with one ASCII value. If a value is supplied in the Separator ASCII field, then the matching separator character will be defined automatically during commit.

System Parameters For Accounting Rules

The page System Parameters for Accounting Rules contains general system parameters for Financials.

The following system parameters are associated with External Files,

Parameter  Description
EXT_REMOVE_DAYS_OUT Default value for Remove Days output files in External File Assistant
EXT_REMOVE_DAYS_IN Default value for Remove Days input files in External File Assistant
DEFAULT_X_SEPARATOR_ID Default value for Separator ID in External File Assistant
DEFAULT_X_DECIMAL_SYMBOL Default value for Decimal Symbol in External File Assistant
DEFAULT_X_DATE_FORMAT Default value for Date Format in External File Assistant
DEFAULT_X_FILE_EXT Default value for file extension for File Name in External File Assistant
DEFAULT_X_FILE_TYPE_LIST Default value for Load File Type List in External File Assistant
NAME_OPTION_DATE_FORMAT Default format for option AddDatein Name Option output files list in External File Assistant

External File Transactions

External File Load IDs

Use this page for an analysis of the external transactions/loads handled by External Files. Select Command Details to navigate to the details for the transaction.

The status field displays the current status of the transaction/load and can be one of the following:
Empty  A transaction is set to Empty status when a transaction header record has been created. A header record is always created before any file processing is performed.
Loaded  For input files, a transaction is set to Loaded status when the file has been read, but not yet unpacked. For output files, a transaction is set to Loaded status when the file transactions have been created, but not yet packed.
Unpacked  For input files, a transaction is set to Unpacked status when the contents of each line has been unpacked.
Transferred  For input files, a transaction is set to Transferred status when the unpacked data has been sent to the receiver (typically the external interface that initiated the transaction).
Aborted  For input files and output files, a transaction is set to Aborted status when the process has been aborted, e.g., errors are detected when unpacking / packing the transactions.
Packed  For output files, a transaction is set to Packed status when the transactions have been packed.
FileCreated  For output files, the transaction is set to File Created status when the file has been created.
Removed For input files and output files, a transaction is set to Removed status when the transactions have been removed. Use the file type / file template RemoveTrans to start the removal of loaded transactions in the External File Assistant

Commands

  • Details

    Will navigate to the External File Transaction detail page.

  • External File Log

    Will navigate to the External File Log page showing log information for each performed step in the input or output process.

External File Transactions

Use this page to view detailed information gathered during the processing of an external file.

This page can also be used to manually test the definition of an external file.

For every line (transaction) containing a record type, additional values will be displayed in a detail section, namely those values related to the unpacked values for input files and the values created before packing an output file. Each detail column will get a header corresponding to the Description for each column in the File Template Column page.

The header part of the page displays information as the load identity, load date, user that performed the load, file type, file type parameter set, file template, file direction and file name.

The status field displays the current status of the transaction/load and can be one of the following:

  • Empty

    A transaction is set to Empty status when a transaction header record has been created. A header record is always created before any file processing is performed

  • Loaded

    For input files, a transaction is set to Loaded status when the file has been read, but not yet unpacked. For output files, a transaction is set to Loaded status when the file transactions have been created, but not yet packed

  • Unpacked

    For input files, a transaction is set to Unpacked status when the contents of each line has been unpacked

  • Transferred

    For input files, a transaction is set to Transferred status when the unpacked data has been sent to the receiver (typically the external interface that initiated the transaction)

  • Aborted

    For input files and output files, a transaction is set to Aborted status when the process has been aborted, e.g., errors are detected when unpacking / packing the transaction

  • Packed

    For output files, a transaction is set to Packed status when the transactions have been packed.

  • FileCreated

    For output files, the transaction is set to File Created status when the file has been created

  • Removed

    For input files and output files, a transaction is set to Removed status when the transactions have been removed. Use the file type / file template RemoveTransto start the removal of loaded transactions in the External File Assistant

The detail section displays the file line, line/transaction status, record set number and record type associated with each line, an error text field if errors have occurred.

The column Record Set No represents the sequence number of record sets within the loaded file. Each time a new record set is found in the file, this sequence number will be updated. The status column displays the current state for each line, and can be one of the following:

  • Loaded

    For input files, a row is set to Loaded status when a line from the file has been read.

  • Unpacked

    For input files, a row is set to Unpacked status when the line has been unpacked.

  • Transferred

    For input files, a row is set to Transferred status when the line has been transferred to the receiver.

  • Unpack Error

    For input files, a row is set to Unpack Error status if errors are found during the unpack operation.

  • Transfer Error

    For input files, a row is set to Transfer Error status if errors are reported when the line is being transferred.

  • Unpack Skipped

    For input files, a row is set to Unpack Skipped status if a line is regarded as a "Skip" line during the unpack operation.

  • Packed

    For output files, a row is set to Packed status when the line has been packed.

  • Pack Error

    For output files, a row is set to Pack Error status if errors are found during the pack operation.

  • Pack Skipped

    For output files, a row is set to Pack Skipped status if a line is regarded as a "Skip" line during the pack operation.

If a line in the middle section is selected then the last detail page will show the file line details as separate columns with a column header describing the contents of each column.

Commands,

  • Load Parameters

    Opens a dialog where parameters and associated values can be supplied or viewed.

  • External File Log

    Navigates to a log page with information for the different steps.

  • Input File

    Command group with options for Input Files

  • Output File

    Command group with options for Output Files

  • External File Template

    Will navigate to the External File Template Definition page and display the file template valid for the current load.

  • Remove Transactions

    Will remove all transaction details for a load.

Commands in command group Input File,

  • Load External File

    Performs a load of an external input file and stores the transactions as External File Transactions.

  • Unpack External File

    Unpacks a loaded external file lines according to the template instructions.

  • Call Input Package Method

    Starts the interface method specified in Api To Call in External File Template Definition on tab Input Files for the current template. The called method has the responsibility to call

  • Complete Input Flow

    Executes all Input Flow steps, i.e.  Load External File , Unpack External File and Call Input Package Method .

Commands in command group Output File

  • Call Output Package Method

    Start the method specified in Api To Call in External File Template Definition on tab Output files.

  • Pack External File

    Performs a pack operation where data in transaction columns, created during the Call Output Package Method step, will be packed to files lines according to template instructions.

  • Create External Output File

    Creates an external output file by writing the file lines created in the Pack External File step.

  • Complete Output Flow

    Executes all Output Flow steps, i.e. Call Output Package Method , Pack External File and Create External Output File

External File Transactions - Load Parameters

This dialog displays parameters and parameter values that are available for and/or used by an external file transaction. The parameters are always related to the File Type and Parameter Set ID that are referred to by the transaction.

If the External File Transactions is used to manually execute each step for an output or input file, then this dialog should be used to define the necessary parameters for the load/transaction before any of the input or output file steps are handled.

When a load has been completed the parameters can be used to display the parameter values used for a load.

External File Log

Use this page to view the information that is logged during the execution of an external transaction/load.

Commands

External File Assistant

Use this assistant to start the processing of input or output text files.

The processing mode is either Online or Batch.

Note:  An external file type and an external file template must have already been defined before executing the assistant.

First select the mode of processing:

  • Online

    This mode is always available and will start the process instantly as the last step of the assistant.

  • Batch

This mode starts the process on a defined schedule, such as at a certain time each day or each week. See Batch Process for more information.

In this next step you can specify following information,

Field Description
File Type Specify the identity of the External File Type definition to be used.
Parameter Set ID Specify the identity of the External File Type Parameter Set definition to be used. The default parameter set for the File Type will always be suggested.
If no parameter set exists, then just leave the field empty
File Template Specify the identity of the External File Template definition to be used to unpack/pack the file. Only templates with Active and Valid status - and that have template definitions for input and/or output file - can be selected.
A default External File Template will be provided according to the following criteria,
  • If the parameter set contains the parameter FILE_TEMPLATE_ID, and FILE_TEMPLATE_ID has a specified default value, then the default value will be used
  • If you use a parameter set that contains the parameter Company, then the default template for that company and file type is retrieved. If no company is specified, then the current users default company will be used.
    The default template can be defined in the Define Default External File Template page.
  • The last option will be to retrieve the first valid and active file template that has a reference to the file type
Input Direction The input direction is suggested automatically. If the file template only supports input file then only the Input File direction can be used. The same goes for output file templates. If however the template supports both output and input files then the suitable direction can be selected
File Name  Add a file here to give the file name. If the suggested file template contains information regarding the file name and client or server path, then this path and name will be suggested. If however, for the on line mode, the path and name does not refer to a valid file, then this field will be left empty

Click Next to specify file type-specific parameter values.

If we want to perform the check (validate) step and the create step automatically, we need to change two of the parameters Check When Loaded and Create When Checked from FALSE to TRUE.

If the process mode is On Line then the execution is started by selecting Finish.

If the process mode is Batch, then select **Finish ** to get to a page where the scheduling of the batch job is specified.

When the process has finished, it is possible to see all transactions in the External File Transactions page.

Utility File Templates

The External Files toolkit consists of the following utility file templates:
External File Type Description
DescribeInput File template used when creating an example file describing how to input data for a specific file template.
ExtFileExpImp File template used when creating an export or import of a file type and all related data. It's also possible to export all related data to a file template without the data related to the file type.
ExtFileInsCreate File template used when creating insert instructions for a file type and all related data. It's also possible to create insert instructions for related data to a file template without the data related to the file type.
RemoveTrans File template used when ordering a job that removes transactions in the External File Transactions storage.
ExtFileFunctionList File template used to create a list of valid column functions

Each file template is associated with a file type having the same name as the template.

This section will describe how to use these templates.

File Template Describe Input

This template makes it possible to create an output file, describing any existing file template, record type for record type, and supplying an input example row for each record type. This can be very useful when creating a file manually,

  • Start the External File Assistant and use the file type and file template DescribeInput
  • Specify output file and the file template to be described.
  • Start the output process.
  • Open the file in an editor. Now you can use this file to manually create an input file.

The resulting file can be viewed by clicking on the following link DescribeSTDSIN1.txt

Each record type has a descriptive session and then an example line containing the column descriptions to be able to see each what attribute/column that goes where.

This line is followed by a data example line.

File Template ExtFileExpImp

This template makes it possible to export and import definitions related to one file type.

This then allows to export ALL definitions related to a file type in one installation, send it to a receiver that can import the definition in the receiver environment.

It's possible to export the file type definition and / or the file template definition.

If file type definition should be exported enter TRUE for Export File Type, else enter FALSE.

If file template definition should be exported enter TRUE in Export File Template, else enter FALSE.

Parameter Description
File Type Enter file type that should be exported. Wildcard is possible.
File Template Enter file template that should be exported. Wildcard is possible.
Export File Type Enter TRUE if file type related data should be exported else FALSE.
Export File Template Enter TRUE if file template related data should be exported else FALSE.

An example of a created export file can be view by clicking on the following link ExpLaliAccountGrp.txt

Similarly it is possible to import definition of the file type if it does not exists. The version of Financials where the import is performed must of course be compatible with the environment where the export was created.

File Template RemoveTrans

This template can be used to start removal of transactions (in External File Transaction) that have passed their life time limit.

For input and output file template definitions it is possible to define Remove Transaction Options, stating the lifetime for transactions created with the template and of the load header should be removed or not.

When the RemoveTrans template is executing it will find all loads associated with templates stating that the "load date" + "remove days" is <= current date. For these loads all transactions will be removed and also the load header itself if specified.

This job can be scheduled to be executed e.g. once a week, to remove old transactions. Of course this should not be done if it, for some reason, is necessary to keep the original transactions.

It is possible to select a file type for parameter File Type / a file template for parameter File Template or all file types and / or file templates by entering % for parameter File Type   and parameter File Template.

It is also possible to remove all load status by entering TRUE for parameter Remove All Load States, which means that loads with all status will be handled. If FALSE is entered only loads with status Transferred and FileCreated are handled.

Let us assume that we have loaded an input file using the user-defined file template CUSTOMISED VOUCHER and in the associated template's input definition, Remove Days have a life time of 0 days, then the remove job should remove the transactions directly since they should be saved for 0 days.

Note: The column Remove Completely is not checked.

If we now start the job that will take care of removing old transactions:

Parameter Description
File Type Enter file type that should be removed. Wildcard is possible.
File Template Enter file template that should be removed. Wildcard is possible.
Remove All Load States Enter TRUE if loads with all load states should be removed. Enter FALSE if only loads with state Transferred or FileCreated should be removed.

The execution will lead to creation of a log file (Removelog2015-03-27.txt), showing the loads that have been removed.

Note: The file name supplied in the External File Assistant was Removelog.txt, but the current date has been added to the output file name. This has to do with how the file template RemoveTrans is defined.

The Name Option for output files is AddDate, meaning that the date is added to the output file name.

Note: By checking the column Remove Completely the load header will also be removed

File Template ExtFileInsCreate

This template makes it possible to create a file insert instructions for a file type and all related data.

This then allows to create a file with insert instructions a file type / file template in one installation, send it to a receiver that can deploy the file in the receiver environment.

If insert instructions for a file type definition should be created enter a file type in File Type.

If insert instructions for a file template definition should be created enter a file template in File Template

Example of created file:

-----------------------------------------------------------------------------  
--  
-- Filename : FileTypeTemplateExtVoucherSTDVOU.ins  
--  
-- Module : ACCRUL  
--  
-- Purpose : Define basic data for File Type and / or File Template   
--  
-----------------------------------------------------------------------------------------------------------------------  
-- Date Sign History  
-- ---- ---- ----------------------------------------------------------------------------------------------------  
-- 091106 ALAIN File created.  
--------------------------------------------------------------------------------------------------------------------------------------  
--  
--------------------------------------------------------------------------------------------------------------------------------------  
PROMPT Insert Instructions for file type  
--------------------------------------------------------------------------------------------------------------------------------------  

DECLARE   
row_no_ NUMBER;  
BEGIN   

DELETE FROM Ext_File_Type_Tab WHERE file_type = 'ExtVoucherX';  
External_File_Utility_API.File_Type_Row('ExtVoucherX', 'Voucher File',   
'ACCRUL', 'TRUE', 'FALSE', '', 'tbwExtParameters', 'Ext_Parameters_API.Create_Parameter_Msg', 'Ext_Load_API.Start_Ext_Vouchers', '' );  

DELETE FROM Ext_File_Type_Rec_Tab WHERE file_type = 'ExtVoucherX';  
External_File_Utility_API.File_Type_Rec_Row('ExtVoucherX', '1', 'Voucher Line', '1', 'TRUE', 'TRUE', 'FALSE', '', '', '' );  

DELETE FROM Ext_File_Type_Rec_Column_Tab WHERE file_type = 'ExtVoucherX';  
External_File_Utility_API.File_Type_Rec_Column_Row('ExtVoucherX', '1', 'ACCOUNT', 'Account', '1', 'TRUE', 'C1' );  
External_File_Utility_API.File_Type_Rec_Column_Row('ExtVoucherX', '1', 'ACCOUNTING_YEAR', 'Accounting Year', '2', 'FALSE', 'N2' );  
External_File_Utility_API.File_Type_Rec_Column_Row('ExtVoucherX', '1', 'AMOUNT', 'Amount', '2', 'FALSE', 'N8' );  
...
Parameter Description
File Type Enter file type that should be included in the file
File Template Enter file template that should be included in the file

Example code for Api_To_Call

Example of procedure called from Start_Online_Process for an input file.

If column Api_To_Call on tab Input file in External File Template Definition Input Files is filled with a value like Ext_Dummy_API.Update_From_Columns it is possible to use a command in External File Transactions named Call Input Package Method.

The Procedure is also called from External File Assistant after loading and unpacking the file.

Example of code for input files:

PROCEDURE Update_From_Columns (  
   info_             IN OUT VARCHAR2,  
   load_file_id_     IN     NUMBER,  
   parameter_string_ IN     VARCHAR2 DEFAULT NULL )  
IS  
   ext_file_load_rec_ Ext_File_Load_API.Public_Rec;  
   parameter_attr_    VARCHAR2(2000);  
   ledger_id_         VARCHAR2(10);  
   add_repl_flag_     VARCHAR2(1);  
   CURSOR get_ext_file_trans IS  
      SELECT *  
      FROM   Ext_File_Trans_Tab  
      WHERE  load_file_id = load_file_id_  
      ORDER BY row_no;  
BEGIN  
   General_SYS.Init_Method(lu_name_, '&PKG', 'Update_From_Columns');  
   -- Get all information from the load header  
   ext_file_load_rec_ := Ext_File_Load_API.Get;  
   IF (parameter_string_ IS NOT NULL) THEN  
      parameter_attr_ := parameter_string_;  
   ELSE  
      parameter_attr_ := ext_file_load_rec_.parameter_string;  
   END IF;  
   IF (parameter_attr_ IS NOT NULL) THEN  
      -- Unpack mandatory parameter from parameter_sting_  
      Message_SYS.Get_Attribute (parameter_attr_, 'LEDGER_ID', ledger_id_);  
      -- Unpack optional parameter from parameter_sting_  
      BEGIN  
         Message_SYS.Get_Attribute (parameter_attr_, 'ADD_REPL_FLAG', add_repl_flag_);  
         add_repl_flag_ := NVL(add_repl_flag_,'D');  
      EXCEPTION  
         WHEN OTHERS THEN  
            add_repl_flag_ := 'D';  
      END;  
   ELSE  
      Error_SYS.Record_General( lu_name_, 'EXTPAREMPTY: External parameter string is empty');  
   END IF;  
   FOR trans_rec_ IN get_ext_file_trans LOOP   
      -- Put your code here   
   END LOOP;  
END Update_From_Columns;

If column Api_To_Call on tab Output file in External File Template Definition Output Files is filled with a value like Ext_Dummy_API.Start_Online_Process it is possible to use a command in External File Transactions named Call Output Package Method.

The Procedure is also called from External File Assistant before packing and writing the file.

Example of code for output files:

This is an example where we use static PL/SQL code.

PROCEDURE Create_External_Output (  
   info_             IN OUT VARCHAR2,  
   load_file_id_     IN     NUMBER,  
   parameter_string_ IN     VARCHAR2 DEFAULT NULL )  
IS  
   newrec_           Ext_File_Trans_Tab%ROWTYPE;  
   row_no_           NUMBER := 0;  
   company_          VARCHAR2(20);  
   file_type_        VARCHAR2(30);  
   file_template_id_ VARCHAR2(30);  
   parameter_string_ VARCHAR2(2000);  
   -- Retrives neccesary information from Load-header  
   CURSOR GetLoadInfo IS  
      SELECT parameter_string,  
             file_type,  
             file_template_id,  
             company  
      FROM   Ext_File_Load_Tab  
      WHERE  load_file_id = load_file_id_;  
   -- A fixed cursor can look like this start  
   CURSOR GetCurrency IS  
      SELECT *  
      FROM Currency_Rate A  
      WHERE A.company = company_  
      AND A.valid_from =  
         (SELECT MAX(B.valid_from)  
          FROM Currency_Rate_Tab B  
          WHERE B.currency_type = A.currency_type  
          AND B.currency_code = A.currency_code  
          AND B.company = A.company)  
      ORDER BY A.currency_type,  
               DECODE(A.currency_code,A.ref_currency_code,0,1),  
               A.currency_code;  
BEGIN  
   OPEN GetLoadInfo;  
   FETCH GetLoadInfo INTO parameter_string_,  
                          file_type_,  
                          file_template_id_,  
                          company_;  
   CLOSE GetLoadInfo;  
   date_format_ := Ext_File_Template_API.Get_Date_Format ( file_template_id_ );  
   Ext_File_Message_API.Create_Out_Where ( where_,  
                                           parameter_string_,  
                                           file_type_,  
                                           date_format_ );  
   FOR rec_ IN GetCurrency LOOP  
      row_no_                := row_no_ + 1;  
      newrec_.load_file_id   := load_file_id_;  
      newrec_.row_no         := row_no_;  
      newrec_.c1             := rec_.currency_type;  
      newrec_.c2             := rec_.currency_code;  
      newrec_.n1             := rec_.currency_rate;  
      newrec_.n2             := rec_.conv_factor;  
      newrec_.d1             := rec_.valid_from;  
      newrec_.c3             := rec_.ref_currency_code;  
      newrec_.record_type_id := 'CURRENCY';  
      newrec_.row_state      := '1';  
      newrec_.Rowversion     := SYSDATE;  
      Ext_File_Trans_API.Insert_Record ( newrec_ );   
   END LOOP;  
   Ext_File_Load_API.Update_State (load_file_id_,   
                                   '2');  
   COMMIT;  
END Create_External_Output;

Example of code:

This is an example where we use dynamic PL/SQL code.

PROCEDURE Create_External_Output (  
   info_             IN OUT VARCHAR2,  
   load_file_id_     IN     NUMBER,  
   parameter_string_ IN     VARCHAR2 DEFAULT NULL )  
IS  
   TYPE RecordType IS REF CURSOR;  
   rec_ RecordType;  
   select_           VARCHAR2(32000);  
   where_            VARCHAR2(32000);  
   order_            VARCHAR2(32000);  
   stmnt_            VARCHAR2(32000);  
   newrec_           Ext_File_Trans_Tab%ROWTYPE;  
   row_no_           NUMBER := 0;  
   company_          VARCHAR2(20);  
   file_type_        VARCHAR2(30);  
   file_template_id_ VARCHAR2(30);  
   parameter_string_ VARCHAR2(2000);  
   -- Retrives neccesary information from Load-header  
   CURSOR GetLoadInfo IS  
      SELECT parameter_string,  
             file_type,  
             file_template_id,  
             company  
      FROM   Ext_File_Load_Tab  
      WHERE  load_file_id = load_file_id_;  
BEGIN  
   OPEN GetLoadInfo;  
   FETCH GetLoadInfo INTO parameter_string_,  
                          file_type_,  
                          file_template_id_,  
                          company_;  
   CLOSE GetLoadInfo;  
   select_ := 'SELECT * ' ||
   'FROM Currency_Rate A ';
-- Alternativ 1 (a fixed where-statement)
where_ := 'WHERE A.company = ' || CHR(39) || company_ || CHR(39) || ' ' ||'
           'AND A.valid_from = ' ||
           '   (SELECT MAX(B.valid_from) ' ||
           '    FROM Currency_Rate_Tab B ' ||
           '    WHERE B.currency_type = A.currency_type ' ||
           '    AND B.currency_code = A.currency_code ' ||
           '    AND B.company = A.company) '; 
-- Alternativ 2 (a where-statement created from parameter_string)
-- date_format_ := Ext_File_Template_API.Get_Date_Format ( file_template_id_ );
-- Ext_File_Message_API.Create_Out_Where ( where_,
--                                        parameter_string_,
--                                        file_type_,
--                                        date_format_ );
-- posibilaty to specify an 'order by'
order_ := 'ORDER BY A.currency_type, ' ||
'         DECODE(A.currency_code,A.ref_currency_code,0,1), ' ||
'         A.currency_code ';
stmnt_ := select_ || where_ || order_;
FOR rec_ IN stmnt_ LOOP
    row_no_                := row_no_ + 1;
    newrec_.load_file_id   := load_file_id_;
    newrec_.row_no         := row_no_;
    newrec_.c1             := rec_.currency_type;
    newrec_.c2             := rec_.currency_code;
    newrec_.n1             := rec_.currency_rate;
    newrec_.n2             := rec_.conv_factor;
    newrec_.d1             := rec_.valid_from;
    newrec_.c3             := rec_.ref_currency_code;
    newrec_.record_type_id := 'CURRENCY';
    newrec_.row_state      := '1';
    newrec_.Rowversion     := SYSDATE;
    Ext_File_Trans_API.Insert_Record ( newrec_ ); 
END LOOP;
Ext_File_Load_API.Update_State (load_file_id_,
                                '2');
COMMIT;
END Create_External_Output;

Batch Process

The Batch process is a part of the Input Process and the Output Process. Below given is the configuration done in IFSCONNECT for this functionality.

Outbound Routing Rule used in Batch Process (Export)

The defined Routing Rule for exporting external files under Batch Process is named Example_External_File_Export. Currently, the unique parameter that triggers this Routing Rule is the message function EXTERNAL_FILE_EXPORT. All output files generated through External File Assistant under Batch Process will use this Routing Rule. The successfully generated file will be available in the FTP directory.

Current FTP Out Folder Directory: <host>\FTP\OUT

The steps in this process are,

  • Schedule a Database Task for an Export File from the relevant Assistant.
  • Run the scheduled task.
  • Application Message will be created for the Export file in Application Messages.
  • File will be generated in the FTP OUT folder.

Inbound Routing Rule used in Batch Process (Import)

The defined Routing Rule for importing external files is named Example_External_File_Import. The API that will be called from the triggered Routing Rule is Ext_File_Server_Util_API.Load_External_File. Location based conditions have been used to trigger this Routing Rule. Currently, the unique parameter set is the file path.

File Path: EXTERNAL_FILE*

As seen here, default File Path accepts file names prefixed with EXTERNAL_FILE. It can be configured based on the user requirement. The steps in this process are,

  • Add file to the FTP IN folder with a file name that is accepted by the Routing Rule and one that satisfies the file name in External File Template.
  • Check Application Messages for the status of the input file.
  • Run the Batch Process using the relevant Assistant.

Backup File Handling

Batch Process Information page is used to keep record of back up files. This page can be accessed through External File Lod IDs using the Command Batch Process Information. This Command will only be available for files that have been run through Batch Process functionality available in the assistants. Information will be saved here only if the user requires a backup file.

Commands available in Batch Process Information,

  • Batch Process Log

    Opens a new page that will have a log of the input file since the time it is loaded from the application message.

  • Download File

    Will allow the user to download the back up file.

  • Cleanup Batch Process Information

    Will clear the existing back up files.