Using the Flat File Journal Import Process
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 Name |
Definition Name |
Usage |
|---|---|---|
|
LOAD_JRNL_PNL |
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. |
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 |
|
191 |
254 |
DESCR254 |
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 |
|
419 |
50 |
Parent PIID (Parent Procurement Instrument Identifier Data) |
|
469 |
50 |
PIID (Procurement Instrument Identifier Data) |
|
519 |
30 |
FAIN (Federal Award Identification Number) |
|
549 |
70 |
URI (Universal Record Identifier) |
|
619 |
1 |
Exclude Federal Award from Report. Appears by default as N. Y: Exclude the associated purchase order from DATA Act reporting for RSS file C. N: Include the associated purchase order from DATA Act reporting for RSS file C. |
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.
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:
This example illustrates the fields and controls on the Flat File Journal Import Request page. You can find definitions for the fields and controls later on this page.
Note: When running this process, if the user or user role does not have access to the business unit, source, or both, then the journal is not created for that business unit and source.
Field or Control |
Description |
|---|---|
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:
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. |
Check Decimal Position |
Select this option to validate decimal position. If selected, the Flat File Journal Batch import process checks the decimal position of Amount values for journals to be imported and logs a message that provides details of journals with invalid decimal positions. Invalid decimal positions can occur when the decimal position for a given currency is set at two positions, for example, but the flat file that is being imported allows up to three or four decimal positions. This option allows you to correct the discrepancy before importing the journal to PeopleSoft General Ledger. |