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 a disc known by the Oracle server. See External File Assistant for more information.
The External Files toolkit introduces the following basic definitions:
A file type is more or less associated with one interface and describes the data that the interface can handle.
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.
External Files also supplies the possibility to create an external file type and an external file template based on a view definition. This can be very useful if it is required to select specific data from a view and spool it out to a file with a specific format.
A new general wizard, the External File Wizard, is introduced. It can be used to load any input file or to create any output file, described by external file types and associated file templates. This means that some of the existing external interfaces are actually obsolete and can be replaced by this single wizard. See External File Assistant for more information.
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.
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 types and file templates can be copied. |
Use View Definitions | A source/Oracle view can be used to
|
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 Wizard | This is a general wizard 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 window 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 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 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.
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. One specific case is when a file type and file template is created from a view definition, leading to that the interface is an API within External Files.
An External File Type consists of the following basic data definitions,
General file type data
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.
Parameters can be associated with a file type. The parameters are used during input or output in the External File Wizard where the user supplies values for the parameters.
A file type can have one or more parameter sets. One parameter set can be defined as the default one.
Available parameters are associated with a parameter set.
To easily get an overview of defined file types, Overview External File Types 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 |
DescribeInput |
Internal file type in External Files, used when creating an example output file for a file template |
EuSalesTax |
|
ExtAd jJournal |
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.
User-defined file types can be created
This functionality can be used to create an external file type and a matching external file template definition using a view name. The purpose of this dialog box is to provide a quick and easy method to create a file type and file template that can be used directly for input/output purposes.
To launch the window, right-click and select Create File Type And Template From View Definition in the External File Types window in IFS/Accounting Rules.
Using this dialog box, you create the following,
- An External File Type definition, including detailed information such as record data and column data.
- Parameter definitions for the file type, i.e., available parameters, parameter set, and parameters per set.
- If needed, you can also create an external file template definition that includes input, output, and detailed information.
In the above case the purpose is to create a file type and file template from
the source view ACCOUNT_GROUP
in the component ACCRUL
.
The file type is named MyAccountGroup
and the file template is given
the same name. Only definitions for output file will be created.
When the file type and file template has been created, they can immediately be
used in the External File Assistant. The
output file will be contain data retrieved from the view ACCOUNT_GROUP
with respect to parameter values, select criteria, and will by default be
a semicolon separated file. The template can be modified to output the data differently.
Note that it will only be possible to extract data that the current user is authorized
to see.
If input definitions were created, then it will be possible to load a file with
account group data and to insert or modify definitions in an a company. In this
case the general method External_File_Utility_API.Create_External_Input
will be used and it will call the New__
or Modify__
methods
in the package Account_Group_API
.
To connect a file type to another view, select the file type, right-click and then click Details to open the External File Type window. Create a new record and enter the relevant details. If this record type should be considered details of the first record type enter 1 in the Parent Record Type field as shown below. Save the information.
You can make changes to the available columns and their position. (e.g. You can remove a column if it is not required, but you should ensure that the remaining column numbers are in a sequential order, and that all columns for which the Mandatory check box is selected are available.)
Follow step 8 for the Output Files tab if you want to include information about the record type in the file.
Now it is possible to use the external file type and external file template.
Use this window to create file type definitions that describes the contents of a file to be handled by the External Files toolkit.
To launch the window, right-click and select Detail on one or more file types in the External File Types window in IFS/Accounting Rules.
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.
The picture shows the definition for the system defined file type ExtSuppInv.
This file type has two available record sets,
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 | Select this check box if the record type must be the first section in a file when a new record set is found. The check box must always be selected if the file type contains only one record set |
Last In Record Set | Select this check box if the record type must be the last section in a file when handling a record set. Select the check boxes First In Record Set and Last In Record Set if the file type contains only one record set |
Mandatory Record | Select this check box 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 but is defined when creating a
file type from a view definition |
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 but
is defined when creating a file type from a view definition. |
Right Mouse Buttons (RMB) in header section,
Will launch the External File Template
detail window and show all file templates having a reference to the current
file type (i.e. ExtSuppInv
)
Will launch a window where parameter data for the file type can be specified.
This RMB option will be enabled if the interface that is associated with the file type has a parameter window with specific load parameters for that interface.
Right Mouse Buttons (RMB) in detail section,
Will launch the External File Column Definition window, where details for each record type is defined.
This option makes it possible to create record type details from a view definition.
Use this window to enter valid column definitions for each record type in an external file type definition.
To launch the window, right-click on one or more record types in the External File Type Definition window in IFS/Accounting Rules.
System-defined file types usually don't require any modifications using this window since the column definitions are pre-defined.
If a new External File Type is defined, then it is necessary to define all valid columns for each Record Type.
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 Definition window in IFS/Accounting Rules |
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,
|
Right Mouse Buttons (RMB) in header section,
Opens the
External File Template
detail window and show all file templates having a reference to the current
file type (i.e. ExtSuppInv
)
Use this dialog to create record type details associated with a file type using a view name.
To launch the window, right-click on one record type without a column definition in the External File Type Definition window in IFS/Accounting Rules and then use RMB option Create Details From View Definition.
The example shows that we want to create details for the record type 1 in file
type MyFileType
by using view definitions for the source view
ACCOUNT_GROUP
in component ACCRUL
.
The view name and the input package will be defined for the file type.
Use this window 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 window in IFS/Accounting Rules.
Use the tab Available Parameters define the available input and output parameters associated with an external file type. Parameters are used by windows 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 |
---|---|
Param No | 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 |
Param 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,
|
Description | Name or description of the file type parameter. This field is supported by translation. |
Browsable Field | If this check box 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 window in IFS/Accounting Rules. 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 COMPANY and CURRENCY_TYPE will be replaced
by the actual input values of these parameters. |
Lov 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 |
Use the tab Parameter Sets in the External File Type Parameter Definition window 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 dialogs/wizards 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.
Right Mouse Buttons (RMB) options,
Will launch the External File Type Parameter Set Definition window, that displays parameters associated with each parameter set for the current file type.
This options can be used to create parameters in a parameter set, by adding all available parameters to the set.
Use this window to define the parameters that will be associated with a file type parameter set. Parameter sets can be referred to in external dialogs/wizards, whenever there is a need to import or export an external file.
Detailed Information,
Column | Description | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Param No | his is the number of the file type parameter that was defined in the External File Type Parameter Definition - Available Parameters window in IFS/Accounting Rules. | ||||||||||||
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 Assistantwindow in IFS/Accounting
Rules. 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,
If 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 If the file type is not system-defined, the company must still be known
in order to evaluate the default value represented by e.g. |
||||||||||||
Mandatory Param | Select this check box if the parameter should be mandatory in the External File Assistant window | ||||||||||||
Show At Load | Select this check box if the parameter should be displayed in the External File Assistant window | ||||||||||||
Editable At Load | Select this check box if the parameter should be possible to edit in the External File Assistant window |
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:
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.
To easily get an overview of defined file template Overview External File templates 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 |
BudgetTemplate | BudgetTemplate | Standard budget template file template |
BudgetTransactionTemp | BudgetTransaction | Standard budget transaction file template |
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 |
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 |
Use this window to get an overview of the external file templates used in External File Interfaces. Right-click and then Details 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.
Right Mouse Buttons (RMB):
Will launch the External File Template window, containing all details for a file template.
Can be used to create a new file template by copying an existing file template.
Can be used to create a new file template by using the definitions from an existing file type.
This dialog box is used to create a new external file template by copying all the definitions from an existing external file template.
To launch the window, right-click and select Copy File Template, on one file type in the External File Templates window in IFS/Accounting Rules.
Using this dialog box, 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 Definition window. Remember to check if the file controls has to be modified or removed in the External File Template Controls window.
To make it usable, please check that the template definition is valid, performed in the template detail window.
Use this dialog box to create a complete external file type definition by copying all definitions from an existing external file type. To launch the window, right-click and select Create File Template From File Type in the External File Templates window in IFS/Accounting Rules. If a file template is marked in the overview then the associated file type will be used as the source file type.
Using this dialog box, 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 Definition window.
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 window.
Remember to check if you have to create any information in the window External File Template Controls.
The last step will be to check if the template definition is valid, performed in the template detail window.
Use this window 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 launch the window, select one or more file templates in the External File Templates window in IFS/Accounting Rules, then right-click and click 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 tabs are,
Note the following:
The tab contains the following,
Field | Description |
---|---|
File Format | Separated if the columns in the file
are separated by a specified string
|
Separator Id | Specify the identity for field separator string if the columns in the file are separated. These separator id's are defined in window 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 DATE and 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 DATE and defines the Date
NLS Calendar. One example of value in this column is Japanese Imperial,
to specify that the column include Japanese year |
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 check box if you want to log the lines that are considered invalid according to External File Control. If this check box is not selected, the invalid file lines will be skipped and they will not be logged |
Log Skipped Lines | Select this check box if you want to log the lines that are to be skipped according to External File Control. If this check box is not selected, skipped lines will never be logged |
Abort Immediately | Select this check box 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 check box if all blanks in a column should be skipped |
Skip Initial Blanks | Select this check box 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: E.g. |
Input File Path Client | Client path to file. Used for online processing, i.e. a path available from the client |
Input File Directory | Server directory to specify the server path to file. Used for batch processing, i.e., a path available from the server. To create a directory this can be done by the application owner: |
Backup File Path Client | Client path where a backup of the input file should be created. Used
for online processing. If no backup file path is specified, then no backup
file is created. If a backup file path is specified, then the original file
will be moved to the backup path and the file will also be renamed according
to <file_name>_<file_load_id>.bak . |
Backup File Directory | Server directory to specify the server path where a backup of the input file should be created. Used for batch processing. If no backup file directory is specified, then no backup file is created. If a backup file directory is specified, then the a backup file will be created in the path specified in the server directory and will be named according to <file_name>_<file_load_id>.bak. The original file will not be removed after load (due to Oracle restrictions) but it will be empty. |
Character Set | Used to specify the character that was used when the file was created. |
Allow Record Set Repeat | If this check box is selected, then a record set can appear several times in the file. If the check box 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 check box 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
RemoveTrans to start the removal of loaded transactions in
the External File Assistant. |
Remove Completely | Select this check box 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 |
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:
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: All lines are checked according to control rules specified in External Files Control window. 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. |
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 ,
AddDate or AddLoadIdAndDate ). If any option other
than None is 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 AddDate
is specified, then the created file will have the name MyFile20021224.txt |
Output File Path Client | Client path to file. Used for online processing. |
Output File Directory | Server directory to specify the server path to file. Used for
batch processing. To create a directory this can be
done by the application owner: |
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 check box 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 check box 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
RemoveTrans to start the removal of loaded transactions in
the External File Assistant |
Remove Completely | Select this check box if the file load header and file log information should be removed when the transactions are removed |
Use this window 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 ExtSuppInv
that 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
|
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 |
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 |
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 RMB 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 the check box 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 | f this columns should be hidden in window External File Transactions, then the check box should be selected. |
Max Length | Specify maximum allowed length for this column |
Right Mouse Buttons (RMB)
General tab options,
Opens the window External File Separators
Input Files tab options,
Opens the window External File Template Control window, where control statements for input files are specified.
Output Files tab options,
Opens the window External File Template Control window, where control statements for output files are specified.
Detail tab options,
Opens the dialog Detail Functions , used to define column functions associated with the column described by the marked detail row.
Use this window to specify how to control the read/write process of an external file. This window is launched when you right-click and select External File Template Control in the Input Files or Output Files tab in the External File Template window in IFS/Accounting Rules.
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 | Satisfy or Skip .
|
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 | Satisfy or Skip
|
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 |
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 The argument could also be defined as |
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 |
|
|
Search for the position of |
|
2 |
|
|
Add 4 to the result of function 1. This gives the first position of the account number. |
|
3 |
|
|
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. |
|
4 |
|
|
Subtract 25 (the result of step 2) from 34
(the result of step 3).This gives the length of the account number. |
|
5 |
|
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. |
|
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 NumberA to 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
|
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 TRUE and 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 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 Example: We have a value |
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
|
RIGHT_TRIM |
RIGHT_TRIM(SourceText[,Set]) |
Trim a string from right. Removes values from right that has the value
specified in Example: We have a value |
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 |
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 Use main function For a file in XML format |
Use this window 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 window 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 commi |
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. |
The window System Parameters for Accounting Rules contains general system parameters for Financials.
The following system parameters are associated with External Files,
Parameter | Description |
---|---|
CLIENT_INPUT_PATH | Default path for Client Input Path in External File Assistant |
CLIENT_OUTPUT_PATH | Default path for Client Output Path in External File Assistant |
CLIENT_BACKUP_PATH | Default path for Client Backup Path in External File Assistant |
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 AddDate in
Name Option output files
list in External File Assistant |
SERVER_DIRECTORY | Directory defined in the database which is set to valid server path. If this value is set to
NULL , it is not possible to use Batch
processing in External File Assistant |
Use this window for an overview of the external transactions/loads handled by External Files. Right-click and then click Details to reach 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 |
Right Mouse Buttons (RMB)
Will launch the External File Transaction detail window.
Will launch the External File Log window showing log information for each performed step in the input or output process.
Use this window to view detailed information gathered during the processing of an external file.
This window can also be used to manually test the definition of an external file. To do so, create a new header record in this window and then use the right mouse button options for Input or Output file to manually perform each step in the input or output process.
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 window.
This header part of the window displays things 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 RemoveTrans
to 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 Erro
r 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 window will show the file line details as separate columns with a column header describing the contents of each column.
Right Mouse Buttons (RMB)
Opens a dialog where parameters and associated values can be supplied or viewed.
Opens a log window with information for the different steps.
Sub menu with menu options for Input Files
Sub menu with menu options for Output Files
Will launch the External File Template Definition window and display the file template valid for the current load.
Will remove all transaction details for a load.
RMB options in sub menu Input File,
Performs a load of an external input file and stores the transactions as External File Transactions.
Unpacks a loaded external file lines according to the template instructions.
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
Executes all Input Flow steps, i.e. Load External File, Unpack External File and Call Input Package Method.
RMB options in sub menu Output File
Start the method specified in Api To Call in External File Template Definition on tab Output files.
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.
Creates an external output file by writing the file lines created in the Pack External File step.
Executes all Output Flow steps, i.e. Call Output Package Method, Pack External File and Create External Output File
This dialog box 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 window in IFS/Accounting Rules is used to manually execute each step for an output or input file, then this dialog box 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.
Use this window to view the information that is logged during the execution of an external transaction/load.
Right Mouse Buttons (RMB)
Opens the detail log window.
Use this wizard 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 wizard.
First select the mode of processing:
This mode is always available and will start the process instantly as the last step of the wizard.
This mode starts the process on a defined schedule, such as at a certain time each day or each week.
It's only possible to select batch mode if parameter
SERVER_DIRECTORY
in System Parameters For Accounting
Rules is set to a valid directory in the database. This
means that the directory is defined with a valid server path in
the database.
The next step looks as follows
In this step you can specify,
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,
|
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 | Enter a file path and file name, or select a file by clicking Browse (for on line processing only). 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.
In the case above we are about to execute load of an external supplier invoice
file and we also want to perform the check (validate) step and the create step automatically.
For this reason we have changed two of the parameters 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 window in IFS/Accounting Rules.
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.
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,
DescribeInput
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.
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.
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
. The transaction detail window shows the following:
If we look at the associated template, CUSTOMISED VOUCHER
, the input definition
like this:
Here we see that transactions related to input for this template have a lifetime of 0 days, i.e. 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 Wizard
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
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 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 RMB in
External File Transactions
named Call Input Package Method
.
The Procedure is also called from External File Assistant after loading and unpacking the file.
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 RMB in
External File Transactions
named Call Output Package Method
.
The Procedure is also called from External File Assistant before packing and writing the file.
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;
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;