Import Budget Spreadsheet Procedures
Overview
The import budget spreadsheet procedure enables users to:
-
create a data file
-
load a text data file created by any industry-standard spreadsheet software using the correct file specification
-
extract data, automatically creating a ready-to-post budget journal batch
-
manually or automatically import batches into the General Ledger
Prerequisites
-
The spreadsheet software must be able to convert or export data to a comma-delimited ASCII file.
-
A text editor, for example, Notepad in Windows or vi in UNIX is required to create the data file.
-
To copy and load the data, access to the operating system command line and basic knowledge of operating system commands is required.
The prerequisites for importing a budget spreadsheet are the same as those for creating budget journals.
For information on prerequisites, see Create Budget Journal Procedures.
Feeder Files
The feeder file is an ASCII text file containing comma-delimited record types.
Sheet Headers
Sheet Headers (SH), identify the spreadsheet to General Ledger. This record usually has no corresponding row in the spreadsheet and is created by editing the file after exporting data.
A file must have one spreadsheet header.
The following is an example spreadsheet header record:
SH,"Main Book",1995,"Plan 001","Catering","Initial Budget"
Sheet Lines
Sheet Lines (SL), contain details of individual transactions. Each line record corresponds to one row; each field corresponds to one column in the spreadsheet.
A file must have one or more spreadsheet line records.
The following is an example spreadsheet line record, showing automatic profiling:
"SL","GBP",17000,"Full Spread","JAN - 95",17000,"01","1000","100","00"
The following is an example spreadsheet line record for manual profiling:
"SL","GBP",17000,"MANUAL","JAN - 95",17000,"01","1000","100","00",500,1000, 1000,1000,1000,1000,1000,1000,1000,1000,1000,6500
Related Topics
Budgeting: Load Spreadsheet File in the Integration Repository (http://www-apps.us.oracle.com:1100/irep/).
Feeder File Format Description
The tables below describe the feeder file format.
| Field Title | Max Length | Description |
|---|---|---|
| Record Type | 2 | spreadsheet header (SH) |
| Ledger Name | 30 | ledger name |
| Fiscal Year | 4 | target fiscal year |
| Budget Name | 30 | target budget name |
| Organization Name | 30 | target budget organization |
| Reason Code | 30 | budget reason code |
| Field Title | Max Length | Description |
|---|---|---|
| Record Type | 2 | spreadsheet line (SL) |
| Currency Code | 4 | currency code |
| Annual Amount | 15 | annual amount with leading sign and zero decimal places |
| Profile Code | 30 | budget profile code |
| Start Period | 10 | start period for new budget |
| Next Year Budget | 15 | next year amount with leading sign and zero decimal places |
| Reference1 | 15 | accounting flexfield segment or period budget amount |
| Reference2 | 15 | accounting flexfield segment or period budget amount |
| Reference3 | 15 | accounting flexfield segment or period budget amount |
| Reference4 | 15 | accounting flexfield segment or period budget amount |
| Reference5 | 15 | accounting flexfield segment or period budget amount |
| Reference6 | 15 | accounting flexfield segment or period budget amount |
| Reference7 | 15 | accounting flexfield segment or period budget amount |
| Reference8 | 15 | accounting flexfield segment or period budget amount |
| Reference9 | 15 | accounting flexfield segment or period budget amount |
| Reference10 | 15 | accounting flexfield segment or period budget amount |
| Reference11 | 15 | accounting flexfield segment or period budget amount |
| Reference12 | 15 | accounting flexfield segment or period budget amount |
| Reference13 | 15 | accounting flexfield segment or period budget amount |
| Reference14 | 15 | accounting flexfield segment or period budget amount |
| Reference15 | 15 | accounting flexfield segment or period budget amount |
| Reference16 | 15 | accounting flexfield segment or period budget amount |
| Reference17 | 15 | accounting flexfield segment or period budget amount |
| Reference18 | 15 | accounting flexfield segment or period budget amount |
| Reference19 | 15 | accounting flexfield segment or period budget amount |
| Reference20 | 15 | accounting flexfield segment or period budget amount |
| Reference21 | 15 | accounting flexfield segment or period budget amount |
| Reference22 | 15 | accounting flexfield segment or period budget amount |
| Reference23 | 15 | accounting flexfield segment or period budget amount |
| Reference24 | 15 | accounting flexfield segment or period budget amount |
| Reference25 | 15 | accounting flexfield segment or period budget amount |
| Reference26 | 15 | accounting flexfield segment or period budget amount |
| Reference27 | 15 | accounting flexfield segment or period budget amount |
| Reference28 | 15 | accounting flexfield segment or period budget amount |
| Reference29 | 15 | accounting flexfield segment or period budget amount |
| Reference30 | 15 | accounting flexfield segment or period budget amount |
| Reference31 | 15 | period budget amount |
| Reference31 | 15 | period budget amount |
| Reference32 | 15 | period budget amount |
| Reference33 | 15 | period budget amount |
| Reference34 | 15 | period budget amount |
| Reference35 | 15 | period budget amount |
| Reference36 | 15 | period budget amount |
| Reference37 | 15 | period budget amount |
| Reference38 | 15 | period budget amount |
| Reference39 | 15 | period budget amount |
| Reference40 | 15 | period budget amount |
| Reference41 | 15 | period budget amount |
| Reference41 | 15 | period budget amount |
| Reference42 | 15 | period budget amount |
| Reference43 | 15 | period budget amount |
| Reference44 | 15 | period budget amount |
| Reference45 | 15 | period budget amount |
| Reference46 | 15 | period budget amount |
| Reference47 | 15 | period budget amount |
| Reference48 | 15 | period budget amount |
| Reference49 | 15 | period budget amount |
| Reference50 | 15 | period budget amount |
| Reference51 | 15 | period budget amount |
| Reference52 | 15 | period budget amount |
| Reference53 | 15 | period budget amount |
| Reference54 | 15 | period budget amount |
| Reference55 | 15 | period budget amount |
| Reference56 | 15 | period budget amount |
| Reference57 | 15 | period budget amount |
| Reference58 | 15 | period budget amount |
| Reference59 | 15 | period budget amount |
| Reference60 | 15 | period budget amount |
| Reference61 | 15 | period budget amount |
| Reference62 | 15 | period budget amount |
| Reference63 | 15 | period budget amount |
| Reference64 | 15 | period budget amount |
| Reference65 | 15 | period budget amount |
| Reference66 | 15 | period budget amount |
| Reference67 | 15 | period budget amount |
| Reference68 | 15 | period budget amount |
| Reference69 | 15 | period budget amount |
| Reference70 | 15 | period budget amount |
| Reference71 | 15 | period budget amount |
| Reference72 | 15 | period budget amount |
| Reference73 | 15 | period budget amount |
| Reference74 | 15 | period budget amount |
| Reference75 | 15 | period budget amount |
| Reference76 | 15 | period budget amount |
| Reference77 | 15 | period budget amount |
| Reference78 | 15 | period budget amount |
| Reference79 | 15 | period budget amount |
| Reference80 | 15 | period budget amount |
| Reference81 | 15 | period budget amount |
| Reference82 | 15 | period budget amount |
| Reference83 | 15 | period budget amount |
| Reference84 | 15 | period budget amount |
| Reference85 | 15 | period budget amount |
| Reference86 | 15 | period budget amount |
| Reference87 | 15 | period budget amount |
| Reference88 | 15 | period budget amount |
| Reference89 | 15 | period budget amount |
| Reference90 | 15 | period budget amount |
Importing Budget Spreadsheet Procedure
Create a Data File
To create a data file, perform the following steps.
-
Create a backup copy of the budget spreadsheet, matching columns in the spreadsheet to the field layout for the spreadsheet lines record.
For information on the spreadsheet lines record field layout, see Feeder Files.
-
Export the data rows from the spreadsheet as comma-delimited into an ASCII file.
For information on creating an ASCII file from a spreadsheet, see the user guide or reference manual for the spreadsheet software.
-
Use a text editor to open the ASCII file.
-
Create a header record as the first line of the file for the spreadsheet header record.
For information on the spreadsheet header record field layout, see Feeder Files.
Load a Data File
To load the feeder file, perform the following steps.
-
Place the feeder file in a directory to which the concurrent manager has access permissions.
The file should have a .dat extension.
-
Navigate to the Submit Request window as follows:
OPSF(I) Budgeting Extensions - Reports
-
Select the Single Request radio button.
-
In the Name field, select Budgeting Extensions: Load Spreadsheet File from the list of values.
-
Click OK.
-
In the Filename field, enter the feeder file's name.
Note: The system automatically adds a .dat extension while loading.
-
To apply the parameters, click OK.
-
To send the request to the concurrent manager, click Submit.
-
To submit another request, click Yes, or to continue, click No.
-
View the request in the concurrent manager as follows:
View - Requests
Extract Data from File
To extract transactions from the feeder file, perform the following steps.
-
Navigate to the Submit Budget Spreadsheet Extract window as follows:
OPSF(I) Budgeting Extensions - Enter - Spreadsheet
-
Enter data in each field of the Submit Budget Spreadsheet Extract window as described in the Submit Budget Spreadsheet Extract Window Description table.
-
To import the extracted budget into the General Ledger automatically after processing, select the Submit Journal Import check box.
-
To preview the new budget on the Budget Process Impact window before processing, deselect the Submit Journal Import check box.
For information on previewing, accepting, and rejecting budget changes, see Previewing Budget Process Impact Inquiry Procedure.
-
To schedule a specific date and time to submit the processing request, enter new values in the Start Date field and Start Time field in the Submission region.
If specific dates and times are not entered, the request is automatically submitted to the concurrent manager when the window is saved.
Submit Budget Spreadsheet Extract Window Description
| Field Name | Description |
|---|---|
| Ledger Name | Ledger name The ledger name list is unique and displays the ledgers within the responsibility's Data Access Set that have read or write access on Full Ledger or Balancing and Management segment values. |
| Batch Name | unique batch name |
| Budget Name | existing budget name |
| Organization | budget organization |
| Spreadsheet Header ID | spreadsheet header identifier |
| Submit Journal Import | indicates automatic import to General Ledger after processing; if deselected, batch can be previewed and deleted before import |
| Start Date | date to submit process to concurrent manager; defaults to today; submitted when window saved |
| Start Time | time to submit process to concurrent manager; defaults to current time; submitted when window saved |