Importing Budget Journals from a Flat File
To import budget journals from a flat file, use the Import Commitment Control Budget Journals component (KK_LOAD_BUDG_JRNL).
This section discusses how to set up for flat file import and import budget journals from flat files:
|
Page Name |
Definition Name |
Usage |
|---|---|---|
|
KK_LOAD_BUDG_JRNL |
Use the Load Budget Journals from a flat file (KK_JRNL_IMP) process to load data from a flat file into the Commitment Control budget header (KK_BUDGET_HDR) and budget lines (KK_BUDGET_LN) tables. |
The flat file budget journal import process depends on the following setup. The storage location of the file attachment is defined by the URL definition GL_FILE_IMPORT. By default, it points to a database record. You might want to change the storage location of the file attachment to another location, such as an FTP server. This is optional. To change this URL definition, navigate to: PeopleTools, Utilities, Administrations, URLs, open GL_FILE_IMPORT. Also, refer to Understanding File Attachment Architecture in the PeopleTools documentation for details. You are required to define an environmental variable, PS_FILEDIR. This variable defines the temporary flat file location on the process scheduler that runs the file import process. If you have a UNIX or OS390 process scheduler, you define this in the psconfig.sh file. If you have an NT process scheduler, you define it on the control panel. Refer to the PeopleTools description on GetFile() PeopleCode for details, or consult your system administrator.
See PeopleTools: Process Scheduler
Use the Import Commitment Control Budget Journals page (KK_LOAD_BUDG_JRNL) to use the Load Budget Journals from a flat file (KK_JRNL_IMP) process to load data from a flat file into the Commitment Control budget header (KK_BUDGET_HDR) and budget lines (KK_BUDGET_LN) tables.
Navigation:
Field or Control |
Description |
|---|---|
Character Set |
Select the character set appropriate to the flat file being processed. When the user creates a request, the character set defaults to the character set 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 in JIS_X_0208 for Japanese Kanji.) |
Journal ID Mask |
Enter a prefix for naming journals when you are using NEXT as Journal ID on the flat file. A 10 character alphanumeric ID identifies budget journals. The system automatically appends the prefix you specify to the journal IDs. For example, if you specify the Journal ID Mask to be LBDG, and your Journal ID on the flat file is NEXT, your Journal IDs might be LBDG000001, LBDG000002, and so on. Journal ID Mask has no effect on creating journals if you specify anything other than NEXT on the flat file as journal IDs. Note: Reserve a unique mask value for loading budget journals to ensure that no other process creates the same value and for ease of recognition. |
The import process interfaces with third-party systems that produce a flat file containing budget journal entries.
The process reads from the import budget journals request page. It checks the type of data in the flat file as to whether it is header data or line data. The process put the data in its corresponding file layout objects and then insert data into KK_BUDGET_HDR and KK_BUDGET_LN. If you select NEXT, the next Journal ID value is generated and used as the new journal ID. In the header BD_HDR_STATUS defaults to N and in the line EE_PROC_STATUS defaults to Y.
Flat file import does some simple field defaulting and editing. The general rule for defaulting is that budget journal header defaults from ledger group, and budget journal line defaults from journal header—including foreign currency and exchange rate type. The general rule for editing is to catch any error that cannot be corrected by opening the journal using the budget journal entry page, including ChartFields not allowed for the budget.
The following edits are performed for the data:
Checks for the existence of all of required fields as listed in the flat file format tables.
Checks to see if the user supplied journal ID already exist in the database.
Checks to see if the ledger group is a commitment control ledger group.
Checks the value of each field against the prompt table of that field.
Note: Account cannot be a Statistic Account.
Flat File Format for Flat File Budget Journal Import Process
The file format is determined by the File Layout Object (KK_BUDGET_JRNL_IMPORT). The following is the file format for the flat file Journal budget import process. The layout varies depending on the first column:
# = Comments
H = Budget Journal Header
L = Budget Journal Line
Comments
|
Column |
Length |
Description |
|---|---|---|
|
1 |
1 |
# |
|
2 |
100 |
Comments |
File Format for Budget Journal Header
|
Column |
Length |
Description |
|---|---|---|
|
1 |
1 |
H |
|
2 |
1 |
Budget Entry Type 0 = Original 1 = Adjustment 2 = Transfer Adjustment 3 = Transfer Original 4 = Closing 5 = Roll Forward |
|
3 |
5 |
Business Unit |
|
8 |
10 |
Journal ID (required) A value of NEXT creates auto numbered budget Journal IDs. |
|
18 |
8 |
Journal Date |
|
26 |
1 |
Generate Parent Budget(s) |
|
27 |
1 |
Use Default Entry Event |
|
28 |
1 |
Parent Budget Entry Type |
|
29 |
10 |
Ledger Group |
|
39 |
30 |
Description |
|
69 |
3 |
Foreign Currency Code |
|
72 |
5 |
Rate Type |
|
77 |
8 |
Currency Effective Date |
|
85 |
17 |
Currency Exchange Rate |
|
102 |
254 |
Long Description |
|
356 |
150 |
Alternate Description |
File Format for Journal Line
Budget journal line data follows its corresponding journal header in the flat file.
|
Column |
Length |
Description |
|---|---|---|
|
1 |
1 |
L |
|
2 |
9 |
Journal Line Number Defaults to a number that is one more than the previous line number. The first line defaults to 1. |
|
11 |
10 |
Account |
|
21 |
10 |
Department |
|
31 |
8 |
Operating Unit |
|
39 |
6 |
Product |
|
45 |
5 |
Fund Code |
|
50 |
5 |
Class Field |
|
55 |
5 |
Program Code |
|
60 |
8 |
Budget Reference |
|
68 |
5 |
Affiliate |
|
73 |
10 |
Fund Affiliate |
|
83 |
10 |
Operating Unit Affiliate |
|
93 |
10 |
ChartField 1 |
|
103 |
10 |
ChartField 2 |
|
113 |
10 |
ChartField 3 |
|
123 |
5 |
PC Business Unit |
|
128 |
15 |
Project ID |
|
143 |
15 |
Activity ID |
|
158 |
5 |
Resource Type |
|
163 |
8 |
Budget Period |
|
171 |
3 |
Statistics Code |
|
174 |
17 |
Statistics Amount Only valid for statistical accounts or for lines with statistic codes. |
|
191 |
3 |
Foreign Currency Code |
|
194 |
28 |
Foreign Amount |
|
222 |
10 |
Reference |
|
232 |
30 |
Line Description |
|
262 |
5 |
Rate Type |
|
267 |
17 |
Currency Exchange Rate |
|
284 |
28 |
Base Currency Amount |
|
312 |
10 |
Entry Event |
|
322 |
8 |
Cumulative Begin Date |
|
330 |
8 |
Cumulative End Date |
|
338 |
6 |
Funding Source |
|
344 |
10 |
Journal Class |
Note: The file layout object KK_BUDGET_JRNL_IMPORT for budget journal import is delivered in fixed column format as shown above. You can change this to CSV format and also adjust the date format if necessary.