Use Oracle SQL*Loader or develop a process to load budget execution information into the following interface tables:
FV_BE_INTERFACE_CONTROL
FV_BE_INTERFACE
This process contains several procedures and functions for validating the records loaded into the FV_BE_INTERFACE table before being imported into the Budget Execution Transaction tables. The selected records are processed to validate all the required information. Once the validation is completed, the status of the records passing validation is set to Accepted and the status of the records not passing validation is set to Rejected. If all the records pass validation, then these records are imported into the FV_BE_TRX_HDRS and FV_BE_TRX_DTLS tables. If some of the records do not pass validation, then none of these records are imported.
Users can correct rejected records in the Correct Budgetary Transactions Import Data window. Users can run the process again to validate the rectified records and import all the records. Users can delete an entire batch in the BE Delete Import Data window by providing the Source and Group ID for the batch to be deleted.
The interface tables include columns, which Oracle U.S. Federal Financials uses to categorize and store specific budget execution information. For example, source of budget data is stored in the column called SOURCE in FV_BE_INTERFACE. Columns can have these attributes:
Enter values for all NOT NULL columns in the interface tables to successfully upload budget execution data in the interface tables. If a value is not entered in a NOT NULL column, then the record cannot be uploaded to the interface table.
Leave these columns NULL as they are for internal use by the Budget Execution Open Interface Import process. For example, during the import process, Budget Execution Open Interface Import updates the values of the ERROR_CODE and ERROR_REASON columns.
Use some columns in the interface tables to import additional information for the budget execution transaction through the Budget Execution Open Interface Import process. The data that is loaded into these optional columns is imported, provided that the information passes the validation checks during the Budget Execution Open Interface Import Process.
The following tables list the columns in the Budget Execution Open Interface tables and indicate which columns require values for importing budget transactions into Oracle U.S. Federal Financials:
FV_BE_INTERFACE_CONTROL table stores the control information required for import processing. Users insert a row into the following table before submitting the import process.
| Column Name | NULL | Type | Description |
|---|---|---|---|
| SOURCE | NOT NULL | VARCHAR2(25) | import source |
| GROUP_ID | NOT NULL | NUMBER | import group |
| STATUS | NOT NULL | VARCHAR2(25) | import status. Use initial value of NEW. |
| DATE_PROCESSED | NULL | DATE | processed date |
| TIME_PROCESSED | NULL | VARCHAR2(25) | processed time |
Users insert records into the following table before executing the Budget Execution Transactions Import process.
| Column Name | NULL | Type | Description |
|---|---|---|---|
| SET_OF_BOOKS_ID | NOT NULL | NUMBER | Ledger identifier |
| SOURCE | NOT NULL | VARCHAR2(25) | import source |
| GROUP_ID | NOT NULL | NUMBER | import group |
| RECORD_NUMBER | NOT NULL | NUMBER | sequential record number for importing records |
| ERROR_CODE | NULL | VARCHAR2(10) | error code for the rejected record |
| ERROR_REASON | NULL | VARCHAR2(250) | error reason for the rejected record |
| BUDGET_LEVEL_ID | NOT NULL | NUMBER | budget level identifier |
| BUDGETING_SEGMENTS | NOT NULL | VARCHAR2(1000) | from distribution segments |
| TRANSACTION_TYPE | NOT NULL | VARCHAR2(25) | budget transaction type |
| SUB_TYPE | OPTIONAL | VARCHAR2(30) | Budget transaction sub type |
| FUND_VALUE | NOT NULL | VARCHAR2(25) | fund value |
| PERIOD_NAME | NOT NULL | VARCHAR2(15) | period name |
| SEGMENTS1 – 30 | OPTIONAL | VARCHAR2(25) | to distribution segments |
| INCREASE_DECREASE_FLAG | NOT NULL | VARCHAR2(1) | increase or decrease flag |
| AMOUNT | NOT NULL | NUMBER | amount |
| DOC_NUMBER | NOT NULL | VARCHAR2(20) | document number |
| ATTRIBUTES1 – 15 | OPTIONAL | VARCHAR2(150) | attributes |
| ATTRIBUTE_CATEGORY | OPTIONAL | VARCHAR2(30) | attribute category |
| PROCESSED_FLAG | NOT NULL (DEFAULT N) | VARCHAR2(1) | processed flag |
| STATUS | NOT NULL | VARCHAR2(25) | record status |
| DATE_CREATED | NOT NULL | DATE | date when record created |
| CREATED_BY | NOT NULL | NUMBER | user identifier for who created the record |
| CORRECTED_FLAG | NOT NULL (DEFAULT N) | VARCHAR2(1) | selected if users correct the record using the Correct Budgetary Transactions Import Data window |
| LAST_UPDATE_DATE | NULL | DATE | last updated date |
| LAST_UPDATED_BY | NULL | NUMBER | last update identifier |
| GL_DATE | NOT NULL | DATE | GL Date |
| PUBLIC_LAW_CODE | OPTIONAL | VARCHAR2(25) | Public law code |
| ADVANCE_TYPE | OPTIONAL | VARCHAR2(25) | Advance type |
| DEPT_ID | OPTIONAL | NUMBER(2) | Department ID |
| MAIN_ACCOUNT | OPTIONAL | NUMBER(4) | Main account |
| TRANSFER_DESCRIPTION | OPTIONAL | VARCHAR2(30) | Transfer description |
| BUDGET_USER_ID | OPTIONAL | NUMBER(15) | Budget user ID |
Enter the appropriate ledger identifier for the transaction. Define the ledger in the Accounting Setups window of the General Ledger application. The SET_OF_BOOKS_ID column of the ledger view, GL_SET_OF_BOOKS.SET_OF BOOKS_ID as shown in the following table, contains a list of valid values.
Note: You can use the following SQL*Statement to access the current ledger ID:
SELECT SET_OF_BOOKS_ID,
NAME FROM GL_SETS_OF BOOKS:
| Validation | Destination |
|---|---|
| Must be a valid Ledger ID | FV_BE_TRX_HDRS.SET_OF_BOOKS_IDFV_BE_TRX_DTLS.SET_OF_BOOKS_ID |
Source from which the budget execution data is being imported. The Source name also determines which records are to be selected for import. OFA (Oracle Financial Analyzer), PSB (Oracle Public Sector Budgeting), or OTHER are valid values. Users can define additional values for Source in the Lookups window.
The following table describes the Source column.
| Validation | Destination |
|---|---|
| If users do not use a predefined source, OFA, PSB, or OTHER, users must enter the name exactly as they have defined the lookup value in the Lookups window, or the Budget Execution Open Interface Import will not create the budget execution record. The lookup value must have the Type BE_SOURCE. For information on the Oracle U.S. Federal Financials Lookups window, see Federal Seed Data Setup, page 3-1. |
FV_BE_TRX_HDRS.SOURCE FV_BE_TRX_DTLS.SOURCE |
Identifier for this batch. When users submit Budget Execution Open Interface Import or Delete BE Import, they must specify a Source and a Group. Processing record by Group allows users to concurrently import or delete subsets of records for the same Source. In order to import the data, the SOURCE and GROUP_ID combination must be present in the FV_BE_INTERFACE_CONTROL table.
The following table describes the Group_ID column.
| Validation | Destination |
|---|---|
| None | FV_BE_TRX_DTLS.GROUP_ID |
Sequential record number for importing records.
The following table describes the Record_Number column.
| Validation | Destination |
|---|---|
| Record number must be unique. Oracle U.S. Federal Financials stores each record number that is uploaded to the FV_BE_INTERFACE table in an index. | None |
Leave this field NULL.
The following table describes the Error_Code column.
| Validation | Destination |
|---|---|
| Record number must be unique. Oracle U.S. Federal Financials stores each record number that is uploaded to the FV_BE_INTERFACE table in an index. | None |
Leave this field NULL.
The following table describes the Error_Reason column.
| Validation | Destination |
|---|---|
| None | None |
Enter the Budget Level ID for the transaction record. A batch may have multiple Budget Level IDs. The process sorts the records in ascending order by Budget Level ID and records the records in hierarchical order. Users can find a list of valid values in FV_BUDGET_LEVELS.
Note: Oracle recommends using the following SQL* Statement to identify the budget level IDs:
SELECT SET_OF_BOOKS_ID,
BUDGET_LEVEL_ID, DESCRIPTION
FROM FV_BUDGET_LEVELS
ORDER BY SET_OF_BOOKS_ID,
BUDGET_LEVEL_ID;
The following table describes the Budget_Level_ID column.
| Validation | Destination |
|---|---|
| None | FV_BUDGET_LEVELS |
The From Distribution Accounting Flexfield values are contained in BUDGETING_SEGMENTS. Users assign values for each segment that is enabled in General Ledger. The same rules apply for assigning segment values as for the GL Import.
For information on journal import and the GL_INTERFACE, see Exporting Data From Your Feeder System, Oracle General Ledger User Guide.
Furthermore, users must assign an account value for each segment that is enabled in the Define Budget Distributions window for the budget level that is associated with the FROM Distribution.
For example, if the record is for distributing funds to Budget Level 2 from Budget Level 1, then the budget level associated with the BUDGETING_SEGMENTS is Budget Level 1. Therefore, users must enter values for the segments set to Y for the given Treasury Symbol, Fund, and Budget Level, in this case Budget Level 1, in the Define Budget Distributions window. For segments that are set to N, users should not enter values for these segments; instead, users enter the segment separator that is defined for the Accounting Flexfield.
For information on segment separators, see Overview of Flexfields, Oracle General Ledger User Guide.
If values are provided for segments that are set to N, they are overwritten with the default segment values for the given Treasury Symbol and Budget Level recorded in the Define Budget Distributions window.
The following table describes the Budgeting_Segments column.
| Validation | Destination |
|---|---|
| BUDGETING_SEGMENTS values are validated. The segment values must already be defined in General Ledger. Cross Validation Rules setup for segment values are validated during the Budget Execution Open Interface Import process. A validation is also performed to ensure that the combination of Accounting Flexfield values contained in BUDGETING_SEGMENTS for the given record already exist in the Budget Execution Transaction tables.
Note: This validation is performed at the time records are being inserted into budget execution transaction tables after the records have passed all other validations of the Budget Execution Open Interface Import process. If a record fails this validation, Oracle U.S. Federal Financials rolls back the database and updates the record status to REJECTED. |
FV_BE_TRX_HDRS.SEGMENTS1-30 FV_BE_TRX_DTLS.SEGMENTS1-30 |
Enter a transaction type for the budget execution record.
The following table describes the Transaction_Type column.
| Validation | Destination |
|---|---|
| The transaction type must be a valid type for the given budget level as defined in the Define Budget Transaction Types window in Oracle U.S. Federal Financials. | FV_BE_TRX_DTLS.TRANSACTION_TYPE |
Enter a sub type of the transaction type if the sub type is required for the budget execution record.
The following table describes the Sub_Type column.
| Validation | Destination |
|---|---|
| The sub type must be designated for the transaction type if the sub type is required to be used with the given transaction type on the record. The sub type is defined in the Define Budget Transaction Types window in Oracle U.S. Federal Financials. | FV_BE_TRX_DTLS.SUB_TYPE |
Enter a Fund Value (balancing segment) for the budget execution record.
The following table describes the Fund_Value column.
| Validation | Destination |
|---|---|
| Budget Execution Open Interface Import validates the values against General Ledger. In addition, a fund must be mapped to a defined Treasury Symbol in the Define Appropriation Parameters window in Oracle U.S. Federal Financials and the Treasury Symbol of the fund must not be cancelled or expired. Also, a budget distribution hierarchy must be established in Oracle U.S. Federal Financials for this Fund in the Define Budget Distribution window. | FV_BE_TRX_HDRS.FUND_VALUE |
Specify the period name. The Budget Execution Open Interface Import process defaults the GL_DATE to the last day of the period.
The following table describes the Period_Name column.
| Validation | Destination |
|---|---|
| Must be an open or future enterable period | FV_BE_TRX_DTLS.GL_DATE |
The To Distribution Accounting Flexfield values are contained in Segments1-30. Assign an account value for each segment that is enabled in the Define Budget Distributions window for the budget level indicated on the record. Users must enter values for segments set to Y for the given Treasury Symbol, Fund, and Budget Level in the Define Budget Distributions window. For segments that are set to N, users should not enter values for these segments. If values are provided, they are overwritten with the default segment values from the Define Budget Distributions window.
The following table describes the Segments1-30 column.
| Validation | Destination |
|---|---|
| Segment values are validated. The segment values must already be defined in General Ledger. Cross Validation Rules setup for segment values are validated during the Budget Execution Open Interface Import process. | FV_BE_TRX_HDRS.SEGMENTS1-30 FV_BE_TRX_DTLS.SEGMENTS1-30 |
Indicate whether the amount is an increase or decrease.
The following table describes the Increase_Decrease_Flag column.
| Validation | Destination |
|---|---|
| Valid values are I or D. | FV_BE_TRX_DTLS.INCREASE_DECREASE_FLAG |
Enter the amount for each line of the transaction.
The following table describes the Amount column.
| Validation | Destination |
|---|---|
| Amount must be a positive number. | FV_BE_TRX_DTLS.AMOUNT |
Enter the document number for the transaction.
The following table describes the Doc_Number column.
| Validation | Destination |
|---|---|
| Document Number is edited for uniqueness against records already in the Budget Execution tables; however, records in the Budgetary Transactions Import table can have the same document number. The following rules apply: - If the document number does not exist in Oracle U.S. Federal Financials, then accept the document number as is and the revision must be zero. - If the document number does exist in Oracle U.S. Federal Financials, then the source, ledgers, budget level, and fund must be the same to be considered a revision. If they are not the same and it is the fund value that is different, then reject the API document; otherwise, the document is accepted as is and the revision number is set to zero. - If the document revises an existing document in Oracle U.S. Federal Financials, then validate that the document in Oracle U.S. Federal Financials is approved. If the document in Oracle U.S. Federal Financials is not approved, then reject the document in the API tables. If the document in Oracle U.S. Federal Financials is approved, then check the revision number in Oracle U.S. Federal Financials and accept the document with the revision number set to the next highest revision number. |
FV_BE_TRX_HDRS.DOC_NUMBER |
| - If the Oracle U.S. Federal Financials document numbering is set to Manual, the interface accepts the document number provided in the interface, unless it fails one of the previously mentioned edits in this section. - If the Oracle U.S. Federal Financials document numbering is set to Automated, the document number must be numeric. Any numeric document number provided in the interface is accepted unless if fails one of the previously mentioned edits in this section. However, if the number provided is greater than the MAX sequencing number, the MAX sequencing number must be reset at completion of the import process and the gaps in numbering are noted in the log file. If the number provided is less than the MAX sequencing number and is not a duplicate, the gaps are noted in the log file of the import concurrent process. - Documents manually created in Oracle U.S. Federal Financials use a different numbering sequence to those imported using the Federal Administrator API. The process looks at FV_BUDGET_LEVELS.NEXT_DOC_NUMBER for documents created in Oracle U.S. Federal Financials and at FV_BE_TRX_HDRS_S for imported documents, thus creating two different numbering systems. |
Enter values for the following descriptive flexfields:
Budget Appropriation
Budget Fund Distributions
Budget Transactions Detail
Budget Transactions Header
The values are inserted where the flexfield segment has been assigned that attribute number. For example, if Attribute1 is assigned to the Budget Appropriation flexfield segment, the field is updated in the import if the Attribute 1 field in the import has a value. Attribute fields are not validated against descriptive flexfield value sets.
The following table describes the Attributes1-15 column.
| Validation | Destination |
|---|---|
| None | FV_BE_TRX_HDRS.SEGMENTS1-30 FV_BE_TRX_DTLS.SEGMENTS1-30 |
Leave this field NULL.
The following table describes the Attribute_Category column.
| Validation | Destination |
|---|---|
| None | None |
All new records loaded into the interface must have a value of N.
The following table describes the Processed_Flag column.
| Validation | Destination |
|---|---|
| None | None |
All new records loaded into the interface must have a status of NEW.
The following table describes the Status column.
| Validation | Destination |
|---|---|
| None | None |
Enter the date that the budget execution import line was created using the format of DD-MON-YYYY.
The following table describes the Date_Created column.
| Validation | Destination |
|---|---|
| None | FV_BE_TRX_HDRS.CREATION_DATE FV_BE_TRX_DTLS.CREATION_DATE |
Enter a User ID number.
The following table describes the Created_By column.
| Validation | Destination |
|---|---|
| Must be a user number defined in the system | FV_BE_TRX_HDRS.SEGMENTS1-30 FV_BE_TRX_DTLS.SEGMENTS1-30 |
All new records must have a value of N.
The following table describes the Corrected_Flag column.
| Validation | Destination |
|---|---|
| None | FV_BE_TRX_DTLS.CORRECTED_FLAG |
Leave this field NULL.
The following table describes the Last_Update_Date column.
| Validation | Destination |
|---|---|
| None | None |
Leave this field NULL.
The following table describes the Last_Update_By column.
| Validation | Destination |
|---|---|
| None | None |
General ledger date for the transaction.
The following table describes the GL_Date column.
| Validation | Destination |
|---|---|
| None | FV_BE_TRX_DTLS.GL_DATE |
The public law code for the transaction.
The following table describes the Public_Law_Code column.
| Validation | Destination |
|---|---|
| If public law code is enabled on the transaction type then the public law code is enabled and the public law code should not be more than seven characters. The transaction type is defined in the Enter Transaction Type window in Oracle U.S. Federal Financials. | FV_BE_TRX_DTLS.PUBLIC_LAW_CODE |
The advance type for the transaction.
The following table describes the Advance_Type column.
| Validation | Destination |
|---|---|
| If the advance type checkbox is selected for the transaction type, the advance type is required and must be a valid advance type entered for the lookup type, “Advance type” in the Oracle U.S. Federal Financials Lookup window in Federal Administrator. The transaction type is defined in the Enter Transaction Type window in Oracle U.S. Federal Financials. | FV_BE_TRX_DTLS.ADVANCE_TYPE |
The department identifier for the transaction.
The following table describes the Dept_ID column.
| Validation | Destination |
|---|---|
| If the advance type checkbox is selected for the transaction type, the dept_id is required. The transaction type is defined in the Enter Transaction Type window in Oracle U.S. Federal Financials. | FV_BE_TRX_DTLS.DEPT_ID |
The main account for the transaction.
The following table describes the Main_Account column.
| Validation | Destination |
|---|---|
| If the advance type checkbox is selected for the transaction type, the main_account is required. The transaction type is defined in the Enter Transaction Type window in Oracle U.S. Federal Financials. | FV_BE_TRX_DTLS.MAIN_ACCOUNT |
The direct transfer description for the transaction.
The following table describes the Transfer_Description column.
| Validation | Destination |
|---|---|
| None | FV_BE_TRX_DTLS.TRANSFER_DESCRIPTION |
Budget users are a set of authorized users who are allowed to access and enter Budget Information. The Budget Users are defined in the Define Budget Users window where the access rights to various levels of budget are also indicated.
The following table describes the Budget_User_ID column.
| Validation | Destination |
|---|---|
| The budget_user_id should be a valid budget user entered in the Define Budget Users window in Oracle U.S. Federal Financials. | FV_BE_TRX_DTLS.BUDGET_USER_ID |