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 Will navigate to the External File Column Definition page, where details for each record type is defined.

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:

• General data
• Specific data for Input Files Must be supplied to enable a template as an input file template.
• Specific data for Output Files Must be supplied to enable a template as an output file template.
• Input or output file template unpack or pack control options.
• Template detail information

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,

• General
• Input Files
• Output Files
• Detail

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,

• External File Separator

  navigates to the page External File Separators

Input Files tab options,

• External File Template Control

  navigates to the page External File Template Control , where control statements for input files are specified.

Output Files tab options,

• External File Template Control

  navigates to the page External File Template Control , where control statements for output files are specified.

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 Log Details

  Navigates to the detail log page.

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.