A NEW TODAY IS DAWNING!

Journal Import File Format

Posted Sat, 27 Mar 2010 - 03:57 by admin

 

Introduction

The OCS applications have the ability to load Statistical & Financial Journals from file. This is useful where financial or statistical information must be loaded from a third party application/program. To do this the journal serial file must be in a certain format. This format could be provided by the third party application or may have to be converted using a conversion tool/script/program. 
 

Journal Import File Format

The Journal Import file format follows the standard OCS file format guidelines. Refer to the OCS Standard File Format Guidelines knowledge base article for this information. NOTE: Only the Fixed File Format is supported for the journal import.

The journal import serial file has 2 different sections. The first section consists of a single line of data and is used for the journal header information. The second section consists of 1 line per transaction being imported.

Unless otherwise indicated all fields left justified, alphanumeric and are mandatory. Fields indicated numeric are Right Justified and the length includes the number of decimal places and the decimal point. All date fields are in the format CCYYMMDD. All fields are uppercase characters (OCS will convert them to uppercase without reporting an error if required).

Most fields correspond to fields entered in the manual journal input screen. Comments are only made if some restriction or other important note must be made about the field.

The format of the file is:

Field Length Comments
Header Section
DocumentType 1 Must be H
Company 2 The company the journal relates to. 
JournalType 2 The type of journal, Valid Values are:

J - Regular Journal

O - Opening Balance Journal

L - Inter Ledger Journal

C - Intercompany Journal

NOTE: Standing or Template Journals cannot be imported using this method.
FinancialYear 4 The financial year the journal is for. Must be either the current or new year.
TransactionCode 4  
JournalName 40  
ReferenceCode1 20 As per journals these are default reference codes and so may be left blank. If entered the first 4 characters consist of the reference type - which must be a valid type, the following 16 characters are the reference code.
ReferenceCode2 20  
ReferenceCode3 20  
ReferenceCode4 20  
SourceCode 6 As per journals this is a default source code and so may be left blank. The field is not validated and can be any value to represent the source of the transactions.
AuthCodeCreate 4 Should be a valid OCS authority code who created the journal. As these journals a being imported from a third party application we recommend an authority code be created that represents the third party system.
Approved 2 Should be Y or N to indicate if this is an approved journal. If this is set to N then the journal will have to be approved within OCS before it can be updated.
AuthCodeApproved 4 The authority who approved the journal. Only required if the Approved field is set to Y. See comments for AuthCodeCreate above.
TransactionDate 8 Date field.
PostingDate 8 Date field.
JournalPostingShift 1 Numeric Field. 
JournalUpdateDate 8 Date Field. The date this journal was updated. Useful where the source system has a separate update date, otherwise leave it the same as the transaction or posting date.
JournalStatus 2 Leave Blank.
SegmentCode 4  
LedgerCode 4  
Balanced 1 Leave Blank.
BalanceLedger 4 Leave Blank.
AutoJournal 1 Must be Y
Debits 16 Numeric Fields. 2 decimal places. The total of all the debits in the journal.
Credits 16 Numeric Fields. 2 decimal places. The total of all the credits in the journal.
ReversalDate 8 Reversing date. For accrual journal only.
JournalIndicator 1 Journal Indicator. Valid values are:

N - Normal Journal

A - Accrual Journal

E - Estimating Journal
Transaction Section
DocumentType 1 Must be T
Company 2 Must match the journal type on in the header section.
JournalType 2 Must match the company on in the header section.
FinancialYear 4 Must match the financial year in the header section.
BatchSequence 6 Numeric Field. 0 decimal places. Start from 100 and increment by 100 for each transaction. NOTE: The number of transaction that can be made in 1 imported is therefore limited to 9999.
Company 2 The company the transaction is for. Must match the company in the header section unless this is a inter-company journal.
Ledger 4  
Account 32  
UtilLedger 4 Leave Blank.
UtilAccount 32 Leave Blank.
Narration1 40 May be mixed case.
Narration2 40 May be mixed case. Optional
ReferenceCode1 20 First 4 characters consist of the reference type - which must be a valid type, the following 16 characters are the reference code. Optional.
ReferenceCode2 20 First 4 characters consist of the reference type - which must be a valid type, the following 16 characters are the reference code. Optional.
ReferenceCode3 20 First 4 characters consist of the reference type - which must be a valid type, the following 16 characters are the reference code. Optional.
ReferenceCode4 20 First 4 characters consist of the reference type - which must be a valid type, the following 16 characters are the reference code. Optional.
SourceCode 6 Optional.
TransactionDate 8 Date Field.
RelatedSegmentCode1 4 Optional.
RelatedSegmentValue1 16 Numeric Field. 2 decimal places. Optional.
RelatedRateSegmentCode1 4 Optional.
RelatedRateSegmentValue1 16 Numeric Field. 2 decimal places. Optional.
RelatedSegmentCode2 4 Optional.
RelatedSegmentValue2 16 Numeric Field. 2 decimal places. Optional.
RelatedRateSegmentCode2 4 Optional.
RelatedRateSegmentValue2 16 Numeric Field. 2 decimal places. Optional.
RelatedSegmentCode3 4 Optional.
RelatedSegmentValue3 16 Numeric Field. 2 decimal places. Optional.
RelatedRateSegmentCode3 4 Optional.
RelatedRateSegmentValue3 16 Numeric Field. 2 decimal places. Optional.
PrimarySegmentCode 4  
PrimarySegmentValue 16 Numeric Field. 2 decimal places.
UpdateFlag 1 Leave Blank


Journal Import from Excel Spreadsheet

This Excel template and associated macro can be used as a guide for entering data into an Excel spreadsheet then export the contents to a serial file to be used for the journal import.