45 Upload Budgets from a PC to the AS/400

This chapter contains these topics:

• Section 45.1, "Creating Spreadsheets,"

• Section 45.2, "Uploading Budgets to a Temporary File,"

• Section 45.3, "Reviewing the Information Transfer,"

• Section 45.4, "Printing the Temporary File for Budget Upload,"

• Section 45.5, "Defining the Fields for Account Balances,"

• Section 45.6, "Uploading the Fields to Account Balances."

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.

45.1 Creating Spreadsheets

1. Create a spreadsheet using a spreadsheet application such as Microsoft (MS) Excel and save it.

2. 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.

3. 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.

4. Minimize the spreadsheet application.

45.2 Uploading Budgets to a Temporary File

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.

45.2.1 Before You Begin

• 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: Text fields should be in character format. This includes business unit, object, subsidiary, subledger, and subledger type. If business unit, object, and subsidiary are in one cell or field, they should be left-justified. If business unit, object, and subsidiary are in three separate cells, justify as follows: Business unit - right Object - center (all objects the same length) Subsidiary - left If you include a subledger and subledger type, they should be right-justified. Amount fields should be numeric fields. They can include decimal points. If you use a minus sign, it must be the first character to the left of the leading (non-zero filled) amount in the field. Amount fields cannot be in a packed or zoned 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:

1. Click the icon for Data Transfer to AS/400.

2. Enter the name of your file, for example Budget.prn, in the following field:

   • File

3. Enter the name of your AS/400, for example JDEG, in the following field:

   • AS/400 System

4. 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):

5. Click Details.

6. 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.)

7. Click OK to close the Details window.

8. Click on the bar for Transfer Data to AS/400. A window will appear with the message: Send completed successfully.

45.3 Reviewing the Information Transfer

There are two methods for ensuring that your information uploaded into the AS/400.

To use the Command Line

From the Master Directory (Menu G):

1. Enter DSPPFM (Display Physical File Member) in the following field:

   • Selection or Command Line

2. Press F4.

   Figure 45-1 Display Physical File (DSPPFM) screen

   
   Description of "Figure 45-1 Display Physical File (DSPPFM) screen"
   
   

3. Enter F14112 in the following field:

   • File

4. Enter *Lib in the following file:

   • Library

5. Enter the member name you assigned to the spreadsheet in the following Field Explanations

   • Member

6. 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):

1. Enter 40 in the following field:

   • Selection line

     This is a Hidden Selection that displays the Field Descriptions window.

2. Enter F14112 in the following field:

   • File

3. Press Enter.

4. Press F5 to display a Source File Member List screen.

5. Enter * in the following field:

   • Member Search

6. Press Enter to display the Spreadsheet file.

7. Enter a 1 in the option filed for your spreadsheet.

8. Press enter to browse your file.

   If data exists in the file, you were successful in your upload.

45.4 Printing the Temporary File for Budget Upload

Navigation

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.

To Complete the Upload

On PC Budget Source File Report

1. Use the report to create version mapping in P14110MAP. Create a version of P14110 and reference the version of the MAP created.

2. Run P14110 in proof mode to review the results.

3. Run in Final mode when results are correct.

4. 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.

Figure 45-2 PC Budget Source File Report

Description of "Figure 45-2 PC Budget Source File Report"

45.4.1 Processing Options

See Section 87.8, "Print Upload Text File (P14110PRT)."

45.5 Defining the Fields for Account Balances

Navigation

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

45.5.1 Example: File Layouts and Field Definitions

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

Description of "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).

45.5.2 What You Should Know About

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. To upload fields for a period budget, provide mapping instructions for each period you want to upload. The system totals the period amounts and updates the appropriate annual budget in the Account Balances table. To upload fields for an annual budget, provide mapping instructions for the fields for one of the budgets: Requested, Approved, or Final.
Processing multiple spreadsheets	If your company uploads several PC budget spreadsheets, you can process them together or separately. To process all budgets together, you need only one version of this program to map the fields. To process each budget separately, you must create a separate version for each budget, or change the member selection each time you process.
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.

45.5.3 Processing Options

See Section 87.9, "PC Budget Upload (P14110MAP)."

45.6 Uploading the Fields to Account Balances

Navigation

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

Description of "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.

45.6.1 Processing Options

See Section 87.10, "PC Budget Upload (P14110)."