Generic Interface Process
Definition
Generic Interface is an extension to the standard journal import interface and enables feeder system files to be loaded without the need for additional programming or system administrator support.
Feeder system data files are files in an ASCII text format containing comma-delimited record types which are loaded into General Ledger.
Feeder system data files must have a certain format.
Overview
Generic Interface meets the following business requirements:
-
Simple loading of feeder system data files
-
Prevents invalid files from being loaded
-
Prevents duplicate files from being loaded
-
Creates summary general ledger transactions
-
Enables access to feeder system transaction details
-
Enables data archive, purge and restore
All files feeding into the General Ledger through Generic Interface must use the same layout. The format for all feeder system data files is described in this chapter.
Accounting information can be created by many different software programs. Use the format described in this chapter to access the accounting information in General Ledger.
The following record types are required in the file format:
File Headers
File Headers (FH), identify the spreadsheet to General Ledger. This record usually has no corresponding row in the spreadsheet and is created by editing the file after exporting data.
A file must have one spreadsheet header.
The following is an example of a spreadsheet file header record:
FH,"Payroll",0598,00123,001
Journal Headers
Journal Headers (JH), identify each group of transactions to be posted to the journal.
A file must have one or more journal headers.
The following is an example of a spreadsheet journal header:
JH,"Monthly",N,A,,GBP
Transaction Lines
Transaction Lines (TL), contain details of individual transactions. Each line record corresponds to one row; each field corresponds to one column in the spreadsheet.
A file must have one or more transaction line records and each transaction line must be contained within a journal header.
The following is an example of a spreadsheet transaction line:
"TL",120.15,"Primary Desc",180594,"Desc 1","Dec 2",,,,,,01,100,5960,1000,45
File Footers
File Footers (FF), summarize the information of the file.
A file must have one file footer.
The following is an example of a spreadsheet file footer:
"FF",120.15,45
Feeder System Data File Layout
The following is an example of a feeder system data file layout:
File Header (FH)
Journal Header (JH)
Transaction Line (TL)
Transaction Line (TL)
Transaction Line (TL)
Journal Header (JH)
Transaction Line (TL)
Transaction Line (TL)
Transaction Line (TL)
File Footer (FF)
If the feeder system is a spreadsheet program, each line record corresponds to one row and each field in a record corresponds to one column in the spreadsheet.
Prerequisites
The feeder system software must convert or export data to a comma-delimited ASCII file with variable length records. To eliminate the risk of incorrectly reading text containing commas, all text values must be enclosed in double quotes (" ").
The following figure shows the Generic Interface process flow, as described in the accompanying text.
Generic Interface Process Flow Diagram
Setting Up Generic Interface
The Generic Interface functionality is based on the standard General Ledger journal interface. Generic Interface enhances the file loading, data manipulation, and inquiry steps in the import process.
Setting up Generic Interface consists of the following procedures:
Define New Journal Source
In the standard General Ledger Journal Source Maintenance window, define a new source for the feeder system. Ensure that the Import References option is selected for the new journal source.
Define Period Mappings
It is possible that the feeder system uses different period names to those defined in General Ledger. To handle this situation a new window is provided enabling mappings to be defined between Feeder System Period Names and General Ledger Period Names.
Define Ledger Mappings
If multiple ledgers are used, the feeder systems may use different book identifiers than General Ledger. To handle this situation, a new window is provided to enable mappings to be defined between feeder system book identifiers and ledger.
Define Feeder System Descriptors
There are three additional sections of information required for each journal source that enable the Generic Interface to function. The information is defined in the Feeder System Descriptors window as follows:
-
Secure source
If a feeder system is defined as a secure source, only users with the profile option View Secure Sources set to Yes are able to view the feeder system transaction details. This setting has no impact on standard General Ledger account or journal inquiry which is controlled using the standard flexfield security functionality.
-
Date picture
It is possible that the date format used by the feeder system does not match the standard Oracle date format. The date picture is used to describe the format used by the feeder system.
-
Descriptors
Generic Interface enables the transfer of 10 items of data for each transaction line. The first three data items are system defined as the transaction date, the primary description, and the amount. The remaining seven data items can be used to transfer any information as required. The descriptors are used to provide a user description for each piece of data passed from the feeder system.
Define Generic Interface Profile Options
Generic Interface provides one profile option that controls access to secure source feeder system detail transactions.
-
View Secure Source
This profile option is set at Responsibility or User level and accepts either a Yes or No value.
When the View Secure Source profile option is set to No, users are unable to view feeder system transaction details for secure sources.
Loading Interface Files
When the feeder system file is available for loading, the file is processed using a number of concurrent programs as shown in the following figure.
Loading Interface Files
All of the concurrent programs are submitted through the standard reports window.
-
Load and Validate Feeder File
Run the Generic Interface: Extract Feeder File report to load a file into Oracle Public Sector Financials (International) Generic Interface tables and the Generic Interface: Load and Validate Feeder File Report to ensure the following:
-
The file is valid
-
The file is of the correct format
-
The file control totals are correct
-
The file has not been loaded previously
-
The file refers to a correct journal source, ledger, and period name.
-
-
Extract Feeder File
Once the file passes validation it is available for extraction by executing the Generic Interface: Extract Feeder File report. This process takes the loaded information and passes it through period and ledger mapping, date conversion, accounting flexfield technical structure conversion, and prepares it for importing into the General Ledger by loading the data into the GL_INTERFACE table.
-
Journal Import and Post
Following extraction, the standard Journal Import and Journal Post routines are executed to create and post the journals to the general ledger.
Reloading Interface Files
A feeder system file cannot be reloaded if the file is already loaded. However, it may be necessary to reload a file for various reasons.
Before reloading a feeder file, you must mark the file as Reloadable by running a concurrent program and then, reload the file using the Generic Interface: Extract Feeder File and the Generic Interface: Load and Validate Feeder File Report.
Feeder Transaction Inquiry
Feeder transactions can be viewed depending on the setting of the Secure Source flag and the View Secure Sources profile option, through the standard Account Inquiry window. When viewing a journal line from a feeder system, the feeder system transaction details can be viewed by selecting the option from the Tools menu. This option displays the feeder system transactions used to create the summary General Ledger journal line.
File Header Record Specification
The file header record passes journal source information to the General Ledger.
| Field Name | Max Width | Description | Type |
|---|---|---|---|
| Record Type | 2 | FH | required |
| Source Name | 25 | name of the feeder system, for example, "Payroll" | required |
| Source Period Name | 15 | accounting period name from feeder system | required |
| Transmission Number | 50 | unique file identification number | required |
| Feeder Book ID | 25 | maps to ledger ID | required |
Journal Header Record Specification
The journal header record passes journal header information to the General Ledger.
| Field Name | Max Width | Description | Type |
|---|---|---|---|
| Record Type | 2 | JH | required |
| Category Name | 25 | journal category | required |
| Reverse Flag | 1 | Is this a reversing batch? Y if yes, N if no | required |
| Reverse Period Offset | # | if Reverse Flag is Y, offset period in which the journal is reversed; for example, 1 for next period, 3 for next quarter | conditionally required |
| Actual Flag | 1 | type of transaction; A for actual or E for encumbrance | required |
| Encumbrance Type ID | # | if Actual Flag is E, journal encumbrance type, such as 1001 | conditionally required |
| Currency Code | 10 | journal currency code | required |
| Currency Conversion Date | 11 | foreign currency journal conversion date; set up through the feeder system descriptors | optional |
| Currency Conversion Type | 15 | foreign currency journal conversion type | optional |
| Currency Conversion Rate | # | foreign currency journal conversion type | optional |
Note: # symbol indicates numeric field, which can be any width.
Journal Lines Record Specification
Journal line records contain details of individual transactions and passes journal line information to the General Ledger.
| Field Name | Max Width | Description | Type |
|---|---|---|---|
| Record Type | 2 | "JL" | required |
| Amount | # | transaction amount; enter leading sign and decimal point | required |
| Primary Description | 240 | primary transaction description | optional |
| Transaction Date | 240 | transaction date | required |
| Descriptor1 | 240 | first transaction descriptor | optional |
| Descriptor2 | 240 | second transaction descriptor | optional |
| Descriptor3 | 240 | third transaction descriptor | optional |
| Descriptor4 | 240 | fourth transaction descriptor | optional |
| Descriptor5 | 240 | fifth transaction descriptor | optional |
| Descriptor6 | 240 | sixth transaction descriptor | optional |
| Descriptor7 | 240 | seventh transaction descriptor | optional |
| Account Segment1 | 240 | accounting flexfield segment | optional |
| Account Segment2 | 240 | accounting flexfield segment | optional |
| Account Segment3 | 240 | accounting flexfield segment | optional |
| Account Segment4 | 240 | accounting flexfield segment | optional |
| Account Segment5 | 240 | accounting flexfield segment | optional |
| Account Segment6 | 240 | accounting flexfield segment | optional |
| Account Segment7 | 240 | accounting flexfield segment | optional |
| Account Segment8 | 240 | accounting flexfield segment | optional |
| Account Segment9 | 240 | accounting flexfield segment | optional |
| Account Segment10 | 240 | accounting flexfield segment | optional |
| Account Segment11 | 240 | accounting flexfield segment | optional |
| Account Segment12 | 240 | accounting flexfield segment | optional |
| Account Segment13 | 240 | accounting flexfield segment | optional |
| Account Segment14 | 240 | accounting flexfield segment | optional |
| Account Segment15 | 240 | accounting flexfield segment | optional |
| Account Segment16 | 240 | accounting flexfield segment | optional |
| Account Segment17 | 240 | accounting flexfield segment | optional |
| Account Segment18 | 240 | accounting flexfield segment | optional |
| Account Segment19 | 240 | accounting flexfield segment | optional |
| Account Segment20 | 240 | accounting flexfield segment | optional |
| Account Segment21 | 240 | accounting flexfield segment | optional |
| Account Segment22 | 240 | accounting flexfield segment | optional |
| Account Segment23 | 240 | accounting flexfield segment | optional |
| Account Segment24 | 240 | accounting flexfield segment | optional |
| Account Segment25 | 240 | accounting flexfield segment | optional |
| Account Segment26 | 240 | accounting flexfield segment | optional |
| Account Segment27 | 240 | accounting flexfield segment | optional |
| Account Segment28 | 240 | accounting flexfield segment | optional |
| Account Segment29 | 240 | accounting flexfield segment | optional |
| Account Segment30 | 240 | accounting flexfield segment | optional |
Note: # symbol indicates numeric field, which may be any width.
File Footer Record Specification
The file footer record passes summary information to the General Ledger and validates the feeder file content.
| Field Name | Max Width | Description | Type |
|---|---|---|---|
| Record Type | 2 | FF | required |
| Total Amount | # | total of all transaction amounts, regardless of currency | required |
| Record Count | 240 | total number of transaction line, TL, records | required |
Note: # symbol indicates a numeric field, which may be any width.