26 Uploading Budget Data

This chapter contains the following topics:

Section 26.1, "Understanding the Budget Upload Process"
Section 26.2, "Importing Budget Data from Spreadsheets"
Section 26.3, "Importing Budget Data from Text Files"
Section 26.4, "Revising Imported Budget Data"
Section 26.5, "Uploading Budget Data to the F0902 Table"
Section 26.6, "Purging Processed Records in the F0902Z1 Table"

26.1 Understanding the Budget Upload Process

This table describes the two methods for uploading budgets and summarizes the steps involved for each method. Notice that the last three steps for both methods are the same. The difference between the two methods is how you import the budget data into the EnterpriseOne tables:

Method	Steps
Import budgets from a spreadsheet	Import budget data using the Upload/Conversion Revisions program. Review and revise imported data. Upload budget data to the F0902 table. Purge processed records.
Import budgets from a text file	Copy budget data to a text file. Convert the data by running the Budget Import Field Mapping program. Review and revise imported data. Upload budget data to the F0902 table. Purge processed records.

26.2 Importing Budget Data from Spreadsheets

This section provides an overview of the import budget process for spreadsheets and discusses how to:

Set processing options for Upload/Conversion Revisions (P0902Z1).
Import budget data from a spreadsheet.

26.2.1 Understanding the Import Budget Process for Spreadsheets

If you create budgets using a spreadsheet program, you can import the budget data directly into the Account Balances - Batch File table (F0902Z1) using the Upload/Conversion Revisions program.

Before you import records into the F0902Z1 table, you should compare the columns in your spreadsheet to those on the Upload Records to F0902Z1 form. Customize the grid and rearrange the columns on the Upload Records to F0902Z1 form so that they match your spreadsheet and the order in which the columns appear on the spreadsheet.

Your spreadsheet must contain an account number, at minimum, for every record. No other fields are required. You can use the Account Number processing option in the Upload/Conversion Revisions program to specify which format to use for the account number. Notice that if you change the Account Number processing option, different fields appear in the detail area to accommodate each account number format.

Before you upload your budget data to the F0902 table, use the Upload/Conversion Revisions program (P0902Z1) to verify that the data is correct and revise the data, if necessary.

26.2.2 Forms Used to Import Budget Data From a Spreadsheet

Form Name	Form ID	Navigation	Usage
Work With Batch Account Balances	W0902Z1A	Other Budgeting Methods (G1421), Upload/Conversion Revisions	Review a list of existing batches that have been uploaded to the F0902Z1 table.
Upload Records to F0902Z1	W0902Z1D	On the Work With Batch Account Balances form, select Upload Records from the Form menu. If necessary, you can delete all records regardless of whether they have been processed by selecting Delete All from the Form menu.	Import budget data from a spreadsheet. This uploads the budget data to the F0902Z1 table.

26.2.3 Setting Processing Options for Upload/Conversion Revisions (P0902Z1)

Use these processing options to specify the default values on the Upload Records to F0902Z1 form.

26.2.3.1 Default

1. Ledger Type

Specify the ledger type from UDC table 09/LT to use for the budget amounts.

2. User ID Field

Specify whether to enable the User ID field. Values are:

Blank: Disable the field.

1: Enable the field.

26.2.3.2 Account Number

1. Account Number

Specify the format to use for the account number: Values are:

1: Short account ID.

2: 3rd account number.

3: Long account number.

Blank or 4: Business unit, object, and subsidiary.

26.2.4 Importing Budget Data From a Spreadsheet

Access the Upload Records to F0902Z1 form.

The Upload Records to F0902Z1 form appears two times—when you first access the form and after you complete step 1 in the following steps. The following form appears after you complete step 1.

Figure 26-1 Upload Records to F0902Z1 form.

Description of ''Figure 26-1 Upload Records to F0902Z1 form.''

On Upload Records to F0902Z1, select Import Grid Data from the Tools menu.
Select an option under the Import Options heading. If you import from Excel, complete the next step. Otherwise, continue to step 4.
Click Browse to locate the excel file and enter the worksheet name from which to import data.
Specify a range of cells from which to import data (for example, A1:B10) under the Define Cell Range heading:
Click Preview and then click Continue to import the data.

26.3 Importing Budget Data from Text Files

This section provides an overview of the budget import mapping program and discusses how to:

Copy budget data into the F0902Z2 text file.
Run the Budget Import Field Mapping program.
Set processing options for Budget Import Field Mapping (R14110M).

26.3.1 Understanding the Budget Import Process for Text Files

As an alternative to importing your data from a spreadsheet, you can copy your budget data into the F0902Z2 text file and then run the Budget Import Field Mapping program (R14110M). This table conversion program reads the text file and converts the data to fields in the Account Balances - Batch File table (F0902Z1). The fields in the F0902Z1 table use the same format as those in the F0902 table.

Before you upload your budget data to the F0902 table, use the Upload/Conversion Revisions program (P0902Z1) to verify that the data is correct and revise the data, if necessary.

26.3.2 Prerequisites

Create a folder called Import in the B9/PROD directory, or wherever your EnterpriseOne directory resides.

26.3.3 Copying Budget Data into the F0902Z2 Text File

To copy your budget data into a text file:

Create a text file and copy your budget data directly into the file.

To create a text file from your PC, go to Start > Programs > Accessories and select Notepad.

If your budget is in a spreadsheet, first save the spreadsheet in CSV format and then copy it into the text file.
Save the text file as F0902Z2.txt.

The system assigns a default path that enables the conversion to run on any platform. The default path is path code\Import\file name, for example, xxxx\Import\F0902Z2.txt, where xxxx is the name of the input environment. When you run the Budget Import Field Mapping program (R14110M), the system searches for the text file in the Import folder.

Note:

For iSeries platforms, the default path is always the Import directory under the path code of the input environment.

26.3.3.1 Adding Column Headings

For the system to read your budget data correctly, you must add column headings to your text file as shown in this example:

F0902Z2.txt
Account Number,Subledger,Subledger Type,Net Posting 1,Net Posting
2,Net Posting 3,Net Posting 4,Net Posting 5,Net Posting 6,Net Posting
7,Net Posting 8,Net Posting 9,Net Posting 10,Net Posting 11,Net 
Posting 12, Net Posting 13,Net Posting 14,Requested,Approved,Final
1.1110.Bear,1,A,1000,100,10,1,0,0,0,0,0,0,0,0,0,0,0,1110,0
1.1110.Fib,1,A,2000,200,20,2,0,0,0,0,0,0,0,0,0,0,0,200,0
1.1110.France,,,3000,300,30,3,0,0,0,0,0,0,0,0,0,0,5000,0,0
1.1105,,,0,52,12313,13213,0,0,0,0,0,0,0,0,0,6589

The budget data appears after the column headings in the text file.

26.3.4 Running the Budget Import Field Mapping Program

Select Other Budgeting Methods (G1421), Budget Import Field Mapping.

You must perform this task in a Windows environment.

To run the Budget Import Field Mapping program from a Windows environment:

On Work With Batch Versions - Available Versions, select the version.
On Table Conversion Prompting:
- If you are working in a local environment, click Submit.
- If you are working in a server environment, select the Override Location check box and click Submit.
  
  On EnterpriseOne Data Sources, select the local data source so that the table conversion program can access the data that you copied into the F0902Z2.txt file.

On PC Budget Upload Field Definition, access the tabs described in this table and follow the instructions:

Tab	Instruction
Select Environments	Under Input Environment, choose the environment that has the F0902Z2.txt file mapped to your local drive. The input environment corresponds to the local database on the client machine. Under Output Environment, choose the environment that has the Account Balances - Batch File (F0902Z1) mapped to the server. The output environment corresponds to the target database that contains the F0902 table.
Table Options	Select the Run Currency Triggers option only if you use multicurrency processing. The system converts decimal places and other currency fields based on your multicurrency setup. Do not select the Clear Output Tables option. If you do, the system deletes all data in the F0902Z1 table, regardless of the user status or processing status. The data in the F0902Z1 table gets uploaded to the F0902 table later in the PC budget upload process. Do not select the Force Row by Row Processing option. The system ignores this option.
Debug Logging	Do not clear the option for Use ini settings for trace level and number of rows to process unless this applies: You want to generate a log during the upload process. Change the Trace Level field to a number between 1 and 10, with 10 providing the most detailed log. You do not want to process all input rows. Therefore, change the Number of Rows field to a number other zero.

Tab

Instruction

Select Environments

Under Input Environment, choose the environment that has the F0902Z2.txt file mapped to your local drive. The input environment corresponds to the local database on the client machine.

Under Output Environment, choose the environment that has the Account Balances - Batch File (F0902Z1) mapped to the server. The output environment corresponds to the target database that contains the F0902 table.

Table Options

Select the Run Currency Triggers option only if you use multicurrency processing. The system converts decimal places and other currency fields based on your multicurrency setup.

Do not select the Clear Output Tables option. If you do, the system deletes all data in the F0902Z1 table, regardless of the user status or processing status. The data in the F0902Z1 table gets uploaded to the F0902 table later in the PC budget upload process.

Do not select the Force Row by Row Processing option. The system ignores this option.

Debug Logging

Do not clear the option for Use ini settings for trace level and number of rows to process unless this applies:

You want to generate a log during the upload process. Change the Trace Level field to a number between 1 and 10, with 10 providing the most detailed log.
You do not want to process all input rows. Therefore, change the Number of Rows field to a number other zero.

When the program finishes, it sends a message to the work center that indicates whether the batch process completed successfully.

26.3.5 Setting Processing Options for Budget Import Field Mapping (R14110M)

Processing options enable you to specify the default processing for programs and reports.

26.3.5.1 Ledger Type

1. Ledger Type: Specify the ledger type for the system to assign to the budget records. Enter a valid ledger type from the Ledger Type Master Setup program (P0025). If you leave this processing option blank, the system uses ledger type BA.

26.3.5.2 Account Number

1. Account Number Format

Specify the account number format that you used in the budget text file. Values are:

blank or 3: Long account number.

1: Short account ID.

2: 3rd account number.

26.4 Revising Imported Budget Data

This section provides an overview of revisions to imported budget data and discusses how to revise imported budget data.

26.4.1 Understanding Revisions to Imported Budget Data

After you import your budget data into the F0902Z1 table, use the Upload/Conversion Revisions program (P0902Z1) to review the records for accuracy and, if necessary, revise the data.

Later, when you run the Upload/Conversion program (R14110) to upload the records to the F0902 table, some records might end in error. For example, you might have records with invalid account numbers or incorrect amounts. To ensure the integrity of your F0902 table, correct any errors using the Upload/Conversion Revisions program.

You can also use the Upload/Conversion Revisions program to delete records from the F0902Z1 table, regardless of whether the records have been successfully processed.

After revising data in the F0902Z1 table, as necessary, run the Upload/Conversion program to copy the budget data from the F0902Z1 table to the F0902 table.

26.4.2 Form Used to Revise Imported Budget Data

Form Name	Form ID	Navigation	Usage
Work With Batch Account Balances	W0902Z1A	Other Budgeting Methods (G1421), Upload/Conversion Revisions	Review a list of existing batches that have been uploaded to the F0902Z1 table.
Revise Batch Account Balances	W0902Z1B	On the Work With Batch Account Balances form, select the record to revise.	Revise a budget record. These are the budget records that have been imported to the F0902Z1 table.

26.4.3 Revising Imported Budget Data

Access the Revise Batch Account Balances form.

Figure 26-2 Revise Batch Account Balances form

Description of ''Figure 26-2 Revise Batch Account Balances form''

26.5 Uploading Budget Data to the F0902 Table

This section provides an overview of the Upload/Conversion program and discusses how to:

Run the Upload/Conversion program.
Set processing options for Upload/Conversion (R14110).

26.5.1 Understanding the Upload/Conversion Program

After you review and, if necessary, revise your budget data on the Revise Batch Account Balances form, run the Upload/Conversion program (R14110) to copy the data from the Account Balance - Batch File (F0902Z1) to the F0902 table.

In proof mode, the system identifies any errors that might occur. You must correct the errors before you run the program in final mode. If the system finds any problems with the records, it sends a workflow message to the work center notifying you of the problem and does not process any of the records in the F0902Z1 table. This ensures the integrity of the data.

Examples of possible errors include:

Account numbers not set up in the F0901 table.
Invalid subledgers or subledger types.
Fiscal date patterns not set up for the fiscal year.
Invalid ledger types.

Note:

If you are using the R14110 program to upload budgets to the JD Edwards EnterpriseOne Job Cost system, the system allows you to post units budgets to accounts in job cost ledgers with a posting edit code of N.

Use the Upload/Conversion Revisions program (P0902Z1) to correct any errors in the F0902Z1 table. Then rerun the Upload/Conversion program in proof mode. When the resulting report is free of errors, run the program in final mode.

In final mode, the program prints a report and uploads the budget data to the F0902 table. For each record that the program successfully uploads, it updates the Batch Status field to D (posted) in the F0902Z1 table. The program bypasses any records that contain errors.

For detailed budgets by period, final mode sets the budget pattern code to DNS (Do Not Spread) for the corresponding accounts in the F0901 table.

You set processing options to control the upload process. For example, you can:

Specify an annual budget upload instead of uploading periodic amounts.

If you do this, specify the type of budget (requested, approved, or final) to which the system adds all periodic amounts.
Add uploaded amounts to the existing amounts in the F0902 table.

The processing option default replaces the amounts in the F0902 table with the uploaded amounts for all matching account numbers.
Reverse the sign of the amounts for all revenue accounts.

After the budget data is loaded successfully into the F0902 table, you can purge records from the F0902Z1 table using the Processed Upload Purge program (R0902ZP).

26.5.2 Running the Upload/Conversion Program

Select Other Budgeting Methods (G1421), Upload/Conversion.

26.5.3 Setting Processing Options for Upload/Conversion (R14110)

Use these processing options to specify the budget cycles to update, whether to upload monthly or annual amounts, and so on.

26.5.3.1 Period

1. Century and 2. Fiscal Year: Use these processing options to identify the century and fiscal year that apply to the budget that you are uploading to the F0902 table. For example, if the budget is for fiscal year 2007, enter 20 for the century and 07 for the fiscal year.

26.5.3.2 Budget Cycle

You can update one, two, or all three of the budget cycles.

1. Requested Budget

Specify whether to update the Requested Budget field (BREQ) in the F0902 from the budget that you are uploading. Values are:

Blank: Do not update the field.

1: Update the field.

If you enter 1 for this processing option and also enter 1 for the Annual/Monthly processing option, the system enters the requested budget amount from the budget that you are uploading in the BREQ field.

If you enter 1 for this processing option and leave the Annual/Monthly processing option blank, the system totals the monthly budget amounts in the budget that you are uploading and enters the total in the BREQ field.

2. Approved Budget

Specify whether to update the Approved Budget field (BAPR) in the F0902 table from the budget that you are uploading. Values are:

Blank: Do not update the field.

1: Update the field.

If you enter 1 for this processing option and also enter 1 for the Annual/Monthly processing option, the system enters the approved budget amount from the budget that you are uploading in the BAPR field.

3. Final Budget

Specify whether to update the Final Budget field (BORG) in F0902 table from the budget that you are uploading. Values are:

Blank: Do not update the field.

1: Update the field.

If you enter 1 for this processing option and also enter 1 for the Annual/Monthly processing option, the system enters the final budget amount from the budget that you are uploading into the BORG field.

4. Annual/Monthly

Specify whether to upload annual budget amounts (requested, approved, and final) or monthly budget amounts (periods 1–14) to the F0902 table. Values are:

Blank: Upload monthly budget amounts.

1: Upload annual budget amounts.

If you enter 1 for this processing option, the system uploads only the annual budget amounts for which you entered 1 in the corresponding Budget Cycle processing options.

If you leave processing option blank, the system uploads the monthly budget amounts. The system also totals the monthly budget amounts and enters the total in each annual budget field for which you entered 1 in the corresponding Budget Cycle processing options.

26.5.3.3 Replace Entries

1. Replace Entries

Specify whether to replace the existing entries in the F0902 table with the entries from the budget that you are uploading, or add them. Values are:

Blank: Replace existing entries.

1: Add entries.

26.5.3.4 Reverse Sign

1. Reverse Sign

Specify whether to reverse the sign on the amounts in the revenue accounts for the budget that you are uploading. Reversing the sign means that negative numbers become positive numbers, and positive numbers become negative numbers. Values are:

Blank: Reverse the sign on the amounts.

If the amounts in the revenue accounts in the budget that you are uploading are positive numbers (debits), you must leave this processing option blank. The system changes the positive numbers to negative numbers during the upload.

1: Do not reverse the sign on the amounts; keep it as is.

Amounts for revenue accounts in the F0902 table are negative numbers (credits). If the amounts in the revenue accounts in the budget that you are uploading are negative numbers, you must enter 1 in this processing option to keep them as negative numbers.

26.5.3.5 Job Cost

1. Job Cost

Specify whether to update only the monthly budget amounts (periods 1–14) in the F0902 table from the budget that you are uploading, regardless of how you set the Budget Cycle processing options. Values are:

Blank: Update the budget amounts that are specified in the Budget Cycle processing options.

1: Update only the monthly budget amounts.

Enter 1 for job cost budgeting.

26.5.3.6 Mode

1. Mode

Specify whether to run the program in proof or final mode:

Blank: Proof mode. The system prints a report of the changes that would be made to the amounts in the F0902 table.

1: Final mode. The system updates the F0902 table and prints a report.

26.5.3.7 Purge

1. Purge

Specify whether to automatically purge processed transactions from the F0902Z1 table. Values are:

Blank: Do not purge processed transactions.

1: Purge processed transactions.

26.6 Purging Processed Records in the F0902Z1 Table

This section provides an overview of the Processed Upload Purge program and discusses how to run the program.

26.6.1 Understanding the Processed Upload Purge Program

If you upload multiple spreadsheets to the F0902Z1 table, the table might become very large. To manage the size of this table, run the Processed Upload Purge program (R0902ZP) to purge records that have been processed.

Before you run this program, ensure that you do not want to revise and upload any records that you previously uploaded. The Processed Upload Purge program deletes all records that have a batch status of D, which indicates that the records were successfully uploaded to the F0902 table. This program also prints a report that lists the records that have been deleted from the table.

Running the Processed Upload Purge program regularly ensures that the F0902Z1 table contains only budget data that is waiting to be uploaded.

This program does not have processing options.

26.6.2 Running the Processed Upload Purge Program

Select Other Budgeting Methods (G1421), Processed Upload Purge.

Alternatively, you can set the Purge processing option to purge processed records from the F0902Z1 table when you run the Upload/Conversion program (R14110).