This chapter contains these topics:
If you create budgets using a spreadsheet program on a PC, you can upload the budgets to the Account Balances table (F0902) on the AS/400. To do this, you complete these steps:
Create budget on PC
Upload budget to a temporary file
Print the temporary file
Define fields for Account Balances
Upload fields to Account Balances
You must do the first two steps using PC software, not the JD Edwards World General Accounting system. You do the last three steps within the General Accounting system.
Create a spreadsheet using a spreadsheet application such as Microsoft (MS) Excel and save it.
Save the spreadsheet again as a type *.prn (Formatted Text Space Delimited) file.
Note:
This should be the last step you perform in preparing the file to be uploaded to the AS/400.Close the spreadsheet file, responding No to saving changes, so you can preserve it as a .prn file. The spreadsheet must be closed before you can upload it.
Minimize the spreadsheet application.
You must use a PC Support program to upload your budget from a PC to the AS/400. You move the budget to a temporary table on the AS/400, the PC Budget Upload table (F14112).
If your company needs to upload several budget spreadsheets (for example, if several departments in the company set up budgets), upload each with a different member name. Later, you can either process all budgets together or process each one individually, depending on layout and your preference:
If all the budgets have identical layouts, you can process all of them together.
If all the budgets do not have identical layouts, or if you want to process them individually, you must use a separate DREAM Writer to map each member.
If the layout is the same and you want to process them individually, you can process them by member name using a single DREAM Writer.
For example, you might use the ID of the user who created the budget as the budget member name. Then you can process each budget member separately.
Enter the annual or periodic budget amounts on your PC using any spreadsheet software package. You might need to consult the reference guide for your PC spreadsheet program for help.
Verify that you have set up the following correctly.
Item | Description |
---|---|
Creating a budget spreadsheet | You must create the spreadsheet so that all the data needed to create one record is in a single row. |
Delimited text file and line length | You must save your spreadsheet as a formatted text (space delimited) file or a delimited text file. A delimited text file is a file in ASCII format that uses characters to separate the data fields.
Some spreadsheet programs use a flat file to export data in ASCII format. Other programs use a print file to export data in ASCII format. If your software uses a flat file, retain cell formats. |
Format | Data you enter must be in character format. The data conversion program will not handle packed, zoned, or other numeric data formats.
The fields in your PC budget should be in the following format:
|
PC Support | The RFROMPC.EXE and RFROMPC.PKG files must be on your PC if you are using IBM PC Support. |
To upload the budget from the PC to the AS/400, follow the instructions in the documentation for the PC Support program.
To upload using Windows NT 4.0
The following instructions are for IBM Client Access on a Windows NT 4.0 platform.
In the Client Access Group window:
Click the icon for Data Transfer to AS/400.
Enter the name of your file, for example Budget.prn, in the following field:
File
Enter the name of your AS/400, for example JDEG, in the following field:
AS/400 System
Enter the name of the library in which your F14112 file resides Library/File(Member), for example (PRD73/F14112(Budget)), in the following field:
Library/File(Member):
Click Details.
Select the following options:
PC File Descriptions: De-activate this (remove the check mark)
File Type: ASCII Text
Translate from: ASCII
Create AS/400 object:
Yes, create member (if this is the first time you are uploading this file)
No, replace member (if you have made revisions to the PC spreadsheet and you are transferring it again.)
Click OK to close the Details window.
Click on the bar for Transfer Data to AS/400. A window will appear with the message: Send completed successfully.
There are two methods for ensuring that your information uploaded into the AS/400.
From the Master Directory (Menu G):
Enter DSPPFM (Display Physical File Member) in the following field:
Selection or Command Line
Press F4.
Figure 45-1 Display Physical File (DSPPFM) screen
Enter F14112 in the following field:
File
Enter *Lib in the following file:
Library
Enter the member name you assigned to the spreadsheet in the following Field Explanations
Member
Press Enter.
To use the Field Descriptions screen
If you do not have access to the command line, do the following to verify that the information has been transferred:
From the Master Directory (Menu G):
Enter 40 in the following field:
Selection line
This is a Hidden Selection that displays the Field Descriptions window.
Enter F14112 in the following field:
File
Press Enter.
Press F5 to display a Source File Member List screen.
Enter * in the following field:
Member Search
Press Enter to display the Spreadsheet file.
Enter a 1 in the option filed for your spreadsheet.
Press enter to browse your file.
If data exists in the file, you were successful in your upload.
From General Accounting (G09), choose Budgeting
From Account Budgeting (G14), choose Other Budgeting Methods
From Other Budgeting Methods (G1421), choose PC Budget Source File Report
The file layout of your PC spreadsheet might differ from the layout in the Account Balances table, where your budget data will reside. In addition, the PC Support program handles the data from different spreadsheets or other software packages differently. For example, PC Support might insert blank characters and left-justify all fields.
You should print the PC Budget Source File Report to show the spreadsheet data that you uploaded to the PC Budget Upload file on the AS/400. This DREAM Writer report can help you identify the spreadsheet fields, their length, and their sequence. You need this report to determine whether the file layout of the temporary file:
Changed when you uploaded it to the PC Budget Upload file
Differs from the layout for the Account Balances table
If your company uploaded more than one budget spreadsheet format, run this program separately for each budget that is formatted differently.
Note:
Format here includes both column layouts and cell formats.After you print the report, you can use it to define the fields to be uploaded to the Account Balances table.
On PC Budget Source File Report
Use the report to create version mapping in P14110MAP. Create a version of P14110 and reference the version of the MAP created.
Run P14110 in proof mode to review the results.
Run in Final mode when results are correct.
View results in P14101 if period amounts uploaded or P14103 if annual amounts uploaded.
Note:
It might be necessary to recompile F14112 in order for the mapping to work correctly.From General Accounting (G09), choose Budgeting
From Account Budgeting (G14), choose Other Budgeting Methods
From Other Budgeting Methods (G1421), choose Upload Field Definition
Your PC budget spreadsheet can have any file layout that you need. However, you must "map" or link the spreadsheet fields in the temporary file to those in the Account Balances table before you can finish uploading your budgets.
The exact format of your temporary file depends on the spreadsheet program you use and any changes made by the PC Support program during uploading. Print your temporary file to verify the contents of this file.
Using the printout of the temporary file, you can define the fields in your budget for the Upload Field Definition program. This program provides the map for the upload to the Account Balances table. When you define the fields, you need to know the following characteristics of each field in the temporary file:
Starting position
Length
Justification
Sequence
If your spreadsheet program uses a flat file to export data in ASCII format, all fields are left-justified. If your spreadsheet program uses a print file, numbers are right-justified and characters are left-justified.
The fields in the Account Balances table to which you can upload budget information are listed below. Their data dictionary names and length are included.
Field | Length |
---|---|
General ledger account number (ANI) | 29 alphanumeric characters |
Subledger (SBL) | 8 alphanumeric characters |
Subledger type (SBLT) | 1 alphanumeric character |
Requested budget amount (BREQ) | 15 numeric characters |
Approved budget amount (BAPR) | 15 numeric characters |
Final budget amount (BORG) | 15 numeric characters |
Detail period amounts (AN01 - AN14) | 14 fields that are 15 numeric characters each |
The following example represents a print file where each field is 10 characters long. Generally, it shows the differences in alignment between numeric and alphabetic fields. Specifically, it shows how a budget spreadsheet containing business unit.object.subsidiary in a single field would look.
Figure 45-3 Business Unit.Object.Subsidiary Display
The first field begins in column 1, is left-justified, and contains 20 characters, ending in column 20. The next field begins in column 23, is right-justified, and contains 10 characters, ending in column 32.
To map a field for uploading to the Account Balances table, you specify both the beginning position and the length of the field. Use the format X Y, separating X from Y with a space, where:
X specifies the starting position of the field
Y specifies the length of the field
Using the layout in the example, specify the first and second fields as follows:
1 20 (defines the first field, the business unit.object.subsidiary)
23 10 (defines the second field, the final budget amount)
This mapping is specific to the processing options for the Upload Field Definition program. Use it to define the contents of the PC Budget Upload file (F14112).
Topic | Description |
---|---|
Uploading period or annual amounts | You need to upload either period budget information or annual budget information, not a combination of the two.
|
Processing multiple spreadsheets | If your company uploads several PC budget spreadsheets, you can process them together or separately.
|
Omitting a field | To omit a field (not upload it to the Account Balances table), leave the related processing option blank. The system will fill period amount fields with zeroes. |
From General Accounting (G09), choose Budgeting
From Account Budgeting (G14), choose Other Budgeting Methods
From Other Budgeting Methods (G1421), choose Upload/Conversion
After you define the fields for your PC budget, run the Upload/Conversion program to upload the information to the Account Balances table. This DREAM Writer program uses the information in the PC Budget Upload file and the mapping information you specified in the Upload Field Definition program.
For period budgets, the program sets the budget pattern code to DNS (do not spread). For annual budgets, the program updates the fields for the budget cycles you specify in the processing option.
You can run this program in proof or final mode. If you choose proof mode, the system only prints a report that you can review to determine whether to change or delete any information. It does not upload the fields to the Account Balances table. To upload, you must run the program in final mode.
In final mode, the system prints a report and uploads the fields to the Account Balances table.
The report contains no amount information. It lists the records created or changed in the Account Balances table and any errors that occurred. Examples of errors include:
Account numbers not set up in the Account Master table (F0901)
Invalid subledgers or subledger types
Fiscal date patterns not set up for the fiscal year
Invalid ledger types
Figure 45-4 Process PC Budget Upload - Proof report
See Also:
Section 40.2, "Entering Annual Budget Amounts (P14102 or P14103)" for more information about budget cycles,
Section 45.5, "Defining the Fields for Account Balances" for more information about processing more than one budget at a time.