Skip Headers

Oracle U.S. Federal Financials User Guide
Release 12.1
Part Number E13551-04
Go to Table of Contents
Contents
Go to previous page
Previous
Go to next page
Next

Budget Execution Open Interface Tables

Understanding the Budget Execution Open Interface Tables

Use Oracle SQL*Loader or develop a process to load budget execution information into the following interface tables:

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:

NOT NULL Columns

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.

NULL Columns

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.

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

Budget Execution Open Interface Table Descriptions

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

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.

FV_BE_INTERFACE_CONTROL
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

FV_BE_INTERFACE

Users insert records into the following table before executing the Budget Execution Transactions Import process.

FV_BE_Interface
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

SET_OF_BOOKS_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:

SET_OF_BOOKS_ID
Validation Destination
Must be a valid Ledger ID FV_BE_TRX_HDRS.SET_OF_BOOKS_IDFV_BE_TRX_DTLS.SET_OF_BOOKS_ID

SOURCE

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.

SOURCE
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

GROUP_ID

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.

GROUP_ID
Validation Destination
None FV_BE_TRX_DTLS.GROUP_ID

RECORD_NUMBER

Sequential record number for importing records.

The following table describes the Record_Number column.

RECORD_NUMBER
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

ERROR_CODE

Leave this field NULL.

The following table describes the Error_Code column.

ERROR_CODE
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

ERROR_REASON

Leave this field NULL.

The following table describes the Error_Reason column.

ERROR_REASON
Validation Destination
None None

BUDGET_LEVEL_ID

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.

BUDGET_LEVEL_ID
Validation Destination
None FV_BUDGET_LEVELS

BUDGETING_SEGMENTS

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.

BUDGETING_SEGMENTS
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

Transaction Type

Enter a transaction type for the budget execution record.

The following table describes the Transaction_Type column.

Transaction Type
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

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

Sub Type
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

FUND_VALUE

Enter a Fund Value (balancing segment) for the budget execution record.

The following table describes the Fund_Value column.

FUND_VALUE
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

PERIOD_NAME

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.

PERIOD_NAME
Validation Destination
Must be an open or future enterable period FV_BE_TRX_DTLS.GL_DATE

SEGMENTS1-30

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.

SEGMENTS1-30
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

INCREASE_DECREASE_FLAG

Indicate whether the amount is an increase or decrease.

The following table describes the Increase_Decrease_Flag column.

INCREASE_DECREASE_FLAG
Validation Destination
Valid values are I or D. FV_BE_TRX_DTLS.INCREASE_DECREASE_FLAG

AMOUNT

Enter the amount for each line of the transaction.

The following table describes the Amount column.

AMOUNT
Validation Destination
Amount must be a positive number. FV_BE_TRX_DTLS.AMOUNT

DOC_NUMBER

Enter the document number for the transaction.

The following table describes the Doc_Number column.

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

ATTRIBUTES1-15

Enter values for the following descriptive flexfields:

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.

ATTRIBUTES1-15
Validation Destination
None FV_BE_TRX_HDRS.SEGMENTS1-30
FV_BE_TRX_DTLS.SEGMENTS1-30

ATTRIBUTE_CATEGORY

Leave this field NULL.

The following table describes the Attribute_Category column.

ATTRIBUTE_CATEGORY
Validation Destination
None None

PROCESSED_FLAG

All new records loaded into the interface must have a value of N.

The following table describes the Processed_Flag column.

PROCESSED_FLAG
Validation Destination
None None

STATUS

All new records loaded into the interface must have a status of NEW.

The following table describes the Status column.

STATUS
Validation Destination
None None

DATE_CREATED

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.

DATE_CREATED
Validation Destination
None FV_BE_TRX_HDRS.CREATION_DATE
FV_BE_TRX_DTLS.CREATION_DATE

CREATED_BY

Enter a User ID number.

The following table describes the Created_By column.

CREATED_BY
Validation Destination
Must be a user number defined in the system FV_BE_TRX_HDRS.SEGMENTS1-30
FV_BE_TRX_DTLS.SEGMENTS1-30

CORRECTED_FLAG

All new records must have a value of N.

The following table describes the Corrected_Flag column.

CORRECTED_FLAG
Validation Destination
None FV_BE_TRX_DTLS.CORRECTED_FLAG

LAST_UPDATE_DATE

Leave this field NULL.

The following table describes the Last_Update_Date column.

LAST_UPDATE_DATE
Validation Destination
None None

LAST_UPDATED_BY

Leave this field NULL.

The following table describes the Last_Update_By column.

LAST_UPDATED_BY
Validation Destination
None None

GL_DATE

General ledger date for the transaction.

The following table describes the GL_Date column.

GL_DATE
Validation Destination
None FV_BE_TRX_DTLS.GL_DATE

PUBLIC_LAW_CODE

The public law code for the transaction.

The following table describes the Public_Law_Code column.

PUBLIC_LAW_CODE
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

ADVANCE_TYPE

The advance type for the transaction.

The following table describes the Advance_Type column.

ADVANCE_TYPE
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

DEPT_ID

The department identifier for the transaction.

The following table describes the Dept_ID column.

DEPT_ID
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

MAIN_ACCOUNT

The main account for the transaction.

The following table describes the Main_Account column.

MAIN_ACCOUNT
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

TRANSFER_DESCRIPTION

The direct transfer description for the transaction.

The following table describes the Transfer_Description column.

TRANSFER_DESCRIPTION
Validation Destination
None FV_BE_TRX_DTLS.TRANSFER_DESCRIPTION

BUDGET_USER_ID

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.

BUDGET_USER_ID
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