Using the Flat File Journal Import Process

This section provides an overview of the flat file format and discusses how to import journal entries from flat files.

Note: Journal entries loaded to your system using flat file journal import must be edited using batch edit only. This is important because, imported journals do not yet have all journal lines and values populated by the various automatic features associated with batch edit. Imported journals should not be edited online. After importing journals, if you open them online you get a warning message stating that they must be edited first. It is only after the batch edit that the journals display the final entries for such things as interunit journals, separate debit credit reversals, and multibook lines.

Page Used to Import Journals from Flat Files

Page Name	Definition Name	Navigation	Usage
Flat File Journal Import Request	LOAD_JRNL_PNL	select General Ledger, then select Journals, then select Import Journals, then select External Flat Files, then select Flat File Journal Import Request	Use the Load Journals From a Flat File process (GL_JRNL_IMP) to load data from a flat file into the General Ledger journal tables.

Understanding File Format in the Flat File Journal Import Process

The file format is determined by the File Layout Object (GL_JRNL_IMPORT). The following is the file format for the Flat File Journal Import process. PeopleSoft delivers a sample flat file named journal.dat under the "data" folder. The layout varies depending on the first column with the following meaning:

# = Comments.

H = Journal header.

L = Journal line.

V = Journal VAT line.

C = Journal control totals.

Comments

This information describes the flat file's comments format:

Column	Length	Description
1	1	#
2	100	Comments

File Format for Journal Header

This information describes the flat file's journal header format:

Column	Length	Description
1	1	H
2	5	Business Unit
7	10	Journal ID A value of `NEXT` or a blank field create auto-numbered journal IDs.
17	8	Journal Date (MMDDYYYY)
25	1	Adjusting Entry Y = Adjusting journal. N = Regular journal (default).
26	3	Adjusting Period Appears by default as 998 for adjusting journal.
29	8	ADB Average Daily Balance Date (MMDDYYYY) Appears by default as the journal date if this field is left blank.
37	10	Ledger Group
47	10	Ledger
57	1	Reversal Code B = Begin next period. E = End next period. X = Next day. D = User defined date. U = Adjustment period. N = No reversal (default).
58	8	Reversal Date (MMDDYYYY) Populated by journal edit program if B or E. Must have a valid date if reversal code is D.
66	3	Reversal Adjusting Period Only used when reversal code is U.
69	1	ADB Reversal Code D = User defined date. S = Same as journal reversal (default).
70	8	ADB Reversal Date (MMDDYYYY) Must have a valid date if ADB reversal code is D.
78	3	Journal Source
81	8	Transaction Reference Number
89	30	Description
119	3	Default Currency Code Foreign Currency Code Appears by default from the base currency of the business unit.
122	5	Default Currency Rate Type
127	8	Currency Effective Date (MMDDYYYY) Appears by default as journal date.
135	17	Default Currency Exchange Rate
152	3	System Source EXT = Journal Edit creates VAT lines and calculates them if VAT lines are not imported (default). EXV = Journal Edit won't create or calculate imported VAT lines.
155	8	Document Type for Document Sequencing If blank, the system gets the value from the default on the run request panel, journal source, and ledger group.
163	12	Document Sequence number Filled by document sequencing routine if document sequencing is enabled; otherwise, the field is blank. The entered number is checked by document sequencing.
175	1	Budget Header Status V = Budget validated. N = Not validated (default).
176	1	Commitment Control Amount Type 1 = Actuals and Recognized. 2 = Encumbrance. 3 = Pre-Encumbrance. 4 = Collected Revenue. 5 = Planned. 7 = Actuals, Recognize and Collect. Note: There is no 6.
177	4	GL Adjustment Type
181	10	Journal Class

File Format for Journal Line

Flat file's journal line format. Journal line data follows immediately after its header data in the file.

Column	Length	Description
1	1	L
2	5	Business Unit Appears by default from the business unit from header. If a different business unit is entered, this is an interunit line.
7	9	Journal Line Number Changes to one more than the previous line number. First line changes to 1.
16	10	Ledger If ledger group is a commitment control ledger group, this is ignored from the flat file and assigned according to the commitment control amount type.
26	10	Account
36	10	Alternate Account
46	10	Department
56	8	Operating Unit
64	6	Product
70	5	Fund Code
75	5	Class Field
80	5	Program Code
85	8	Budget Reference
93	5	Affiliate
98	10	Fund Affiliate
108	10	Operating Unit Affiliate
118	10	ChartField 1
128	10	ChartField 2
138	10	ChartField 3
148	15	Project
163	4	Book Code
167	8	Budget Period
175	10	Scenario
185	3	Statistics Code
188	28	Base Currency Amount Calculated from transaction amount and exchange rate. If entered and exchange rate = 0, and `recalc exchange rate` is selected for the journal edit request, the exchange rate is calculated.
216	1	Movement Flag This is only used in a separate debit and credit database. N = Natural (default). R = Reverse.
217	17	Statistics Amount Only valid for statistical accounts or for lines with statistic codes.
234	10	Journal Line Reference
244	30	Journal Line Description
274	3	Currency Code Appears by default as the default currency on header. The journal edit program clears this field for a statistical account.
277	5	Currency Rate Type
282	28	Amount Foreign Currency Amount
310	17	Currency Exchange Rate Appears by default as 1 if Currency Code = Base Currency. Uses table lookup in journal edit program if currency rate type is entered and `recalc exchange rate` is selected for the journal edit request.
327	5	Projects Business Unit
332	15	Projects Activity ID
347	3	Projects Analysis Type
350	5	Projects Resource Type
355	5	Projects Resource Category
360	5	Projects Resource Sub-category
365	8	Budget Date (MMDDYYYY) Appears by default as Journal Date if blank.
373	1	Budget Line Status Appears by default as N if blank or Budget Header Status = N.
374	10	Entry Event
384	4	Interunit and intraunit transaction group number
388	1	Interunit and intraunit anchor flag
389	30	Open Item Key

Note: After importing a journal using the flat file journal import process, you must run the Journal Edit process on the journal before you make corrections using the Create Journal Entries page.

Flat file journal import does not allow the use of control accounts. However, you can remove this restriction by changing the JIMP_LN_WRK record to set the prompt table edit for the Account field to GL_ACCOUNT_TBL and the prompt table edit for Alternate Account field to ALTACCT_TBL.

File Format for Journal VAT Line

Flat file's journal VAT line format. Journal VAT data follows immediately after its journal line data in the file.

Column	Length	Description
1	1	V
2	1	Physical Nature G = Goods (default). S = Service.
3	6	Defaulting State
9	1	VAT Exception Type N = None. S = Suspended. X = Exonerated.
10	20	VAT Exception Certificate ID
30	1	Record VAT Input Whether a business unit pays VAT and recovers it later from VAT tax authority. Typically for purchase transactions. Y = Yes. N = No.
31	1	Record VAT Output Whether VAT is collected by a supplier on behalf of the government. Typically for sales transactions. Y = Yes. N = No.
32	1	Calculation Type E = Exclusive: VAT stated separately from merchandise. I = Inclusive: VAT included with merchandise.
33	1	Calculation at Gross or Net G = Gross. N = Net.
34	3	VAT Reporting Country Required field.
37	8	VAT Declaration Date Appears by default as journal date if blank.
45	4	VAT Transaction Type Required field.
49	1	VAT Applicability Required field. E = Exempt. N = Not applicable. O = Outside of scope of VAT. S = Suspended. T = Taxable. V = VAT only. X = Exonerated.
50	8	VAT Code Required field.
58	4	VAT Account Type
62	1	VAT Distribution Status D = Distributed. E = Error flag. I = Ignored. M = Archival entry. N = Not distributed. P = Processed. R = Reversal entry. U = Undefined.
63	28	VAT Amount if system source = EXV Entered VAT amount = VAT amount; otherwise, entered VAT amount = 0.
91	28	VAT Amount in Base Currency if system source =EXV Entered VAT Base Amt = VAT Base Amount; otherwise, entered VAT base amount = 0.
119	28	VAT Basis Amount
147	28	VAT Basis Amount in Base Currency
175	9	Tax Code Aggregate Percent
184	1	Override VAT Tolerance Check Y = Override: no check. N = Check.
185	6	VAT Use Type
191	7	Recovery percent
198	7	Rebate percent
205	28	Recovery Amount
233	28	Recovery Amount in Base Currency
261	28	Rebate Amount
289	28	Rebate Amount in Base Currency
317	1	Recovery Percent Source A = Automatically calculated. M = Manual entry.
318	1	Rebate Percent Source A = Automatically calculated. M = Manual entry.
319	1	VAT Rounding Rule D = Round down. N = Natural round. U = Round up.
320	1	Amounts for Reporting Currency Y = Yes. N = No.
321	3	Reporting Currency
324	28	VAT Amount in Reporting Currency
352	28	VAT Transaction Amount Reporting
380	17	Currency Exchange Rate Changes to 1 if Currency Code = Base Currency.
397	1	Prorate Non-recoverable VAT Y = Yes. N = No.
398	1	Allocate nonrecoverable VAT Y = Yes. N = No.
399	1	VAT Apportionment Control D = Distribution GL business unit. G = Transaction GL business unit. T = Transaction business unit.
400	9	VAT Applicable Journal Line Number

File Format for Journal Control Total

Flat file's journal control total data format. Control total data follows its header data but comes after journal line and journal VAT.

Column	Length	Description
1	1	C
2	5	Control Business Unit The control totals are for this business unit. Changes to the header business unit if this is blank.
7	10	Ledger
17	3	Base Currency Code The control totals are for this base currency. Should always equal the business unit's base currency except for statistical account totals.
20	3	Currency Code The control totals are for this foreign currency. Changes to the header business unit if this is blank.
23	28	Journal Control Base Currency Debits
51	28	Journal Control Base Currency Credits
79	28	Journal Control Foreign Currency Debits
107	28	Journal Control Foreign Currency Credits
135	17	Journal Control Statistical Units
152	9	Journal Control Lines
161	10	Department
171	8	Operating Unit
179	6	Product
185	5	Fund Code
190	5	Class Field
195	5	Program Code
200	8	Budget Reference
208	5	Affiliate
213	10	Fund Affiliate
223	10	Operating Unit Affiliate
233	10	ChartField 1
243	10	ChartField 2
253	10	ChartField 3
263	15	Project
278	4	Book Code
282	4	GL Adjustment Type
286	8	Budget Period
294	10	Scenario
304	2	Balance Sheet Indicator

Note: The file layout object GL_JRNL_IMPORT for flat file journal import is delivered in fixed column format as shown in the previous table. You can change this to CSV format and also adjust the date format if necessary.

Flat File Journal Import Request Page

Use the Flat File Journal Import Request page (LOAD_JRNL_PNL) to launch the Load Journals From a Flat File process (GL_JRNL_IMP) ; this process loads data from a flat file into the General Ledger journal tables.

Navigation

select General Ledger, then select Journals, then select Import Journals, then select External Flat Files, then select Flat File Journal Import Request

Character Set

Select the appropriate character set for the flat file being processed. When you create a request, the character set changes to the character set that is associated with the default language code of the user that is creating the run control request. You can change this value, but you must have a UNICODE database if the character set of the file being processed requires UNICODE.

UNICODE is important when your database must function in other than the Latin alphabet, such as Japanese Kanji.

Validate ChartFields

Select the level of ChartField validation for the import process:

Account, Alternate Account
All Common ChartFields
None

Note: When you select None, the process does not check for invalid Account and AltAcct values; however, invalid Control Account and Control AltAcct values will fail.

A lower validation level enables you to run the import process faster and make use of journal suspense processing when Journal Edit revalidates the journal at a later stage.

Note: Selecting All Common ChartFields does not validate PeopleSoft Project Costing specific ChartFields.

Default GL Document Type

If you use document sequencing, specify a default document type to indicate the business purpose for the transaction. You can specify a document type for each journal header in the flat file.

Journal ID Mask

Enter a unique mask or prefix to identify journals created through flat file journal import, if the journal ID is blank or NEXT in the file.