If you are using E-Business Suite, PeopleSoft, or JD Edwards EnterpriseOne, and you would like to extract the budget data from these sources and import it into Oracle Business Analytics Warehouse, you can use the pre-configured adapter mappings. However, if you want to use budget data from other external systems, you can import the data into Oracle Business Analytics Warehouse using the Universal adapter.
About Configuring Universal Source Files
The tables describe the columns in the universal source CSV files file_budget.csv and file_acct_budget.csv, their data types, and how to populate them where applicable. The records in file_budget.csv are loaded into W_BUDGET_D.
| Column Name | Datatype | Size | Description |
|---|---|---|---|
|
BUDGET_NAME |
string |
80 |
Budget name. |
|
BUDGET_VERSION |
string |
30 |
Budget version. |
|
BUDGET_STATUS |
string |
30 |
Budget status. |
|
BUDGET_TYPE |
string |
30 |
Budget type. |
|
CREATED_BY_ID |
string |
80 |
ID of created by user. Populate with Integration_ID from w_user_d. |
|
CHANGED_BY_ID |
string |
80 |
ID of changed by user. Populate with Integration_ID from w_user_d. |
|
CREATED_ON_DT |
string |
14 |
Created date. |
|
CHANGED_ON_DT |
string |
14 |
Changed date. Used for updating an existing record in the warehouse. Increase the date if you want to update the record. If a record with the same integration_ID already exists in the target table W_BUDGET_D, then the load process will compare the CHANGED_ON_DT values between this record and the record in W_BUDGET_D. If this record's CHANGED_ON_DT is later than the record in W_BUDGET_D, then the load process will perform an update against the record in W_BUDGET_D; otherwise the load process will ignore this record, and no update or insertion will occur. If there is no matching record in W_BUDGET_D with the same integration_ID, then the load process will insert this record into W_BUDGET_D. |
|
AUX1_CHANGED_ON_DT |
string |
14 |
- |
|
AUX2_CHANGED_ON_DT |
string |
14 |
- |
|
AUX3_CHANGED_ON_DT |
string |
14 |
- |
|
AUX4_CHANGED_ON_DT |
string |
14 |
- |
|
DELETE_FLG |
string |
1 |
- |
|
DATASOURCE_NUM_ID |
number |
10 |
A number for your data source. Populate the same datasource_num_id as your main source application. |
|
INTEGRATION_ID |
string |
80 |
A unique identifier for the record. |
|
TENANT_ID |
string |
80 |
- |
|
X_CUSTOM |
string |
10 |
- |
This table shows the structure of the file_acct_budget.csv file. The records in the file_acct_budget.csv will be loaded into W__ACCT_BUDGET_F.
| Column Name | Datatype | Size | Description |
|---|---|---|---|
|
ADJUSTMENT_FLG |
string |
1 |
- |
|
AUX1_CHANGED_ON_DT |
string |
14 |
- |
|
AUX2_CHANGED_ON_DT |
string |
14 |
- |
|
AUX3_CHANGED_ON_DT |
string |
14 |
- |
|
AUX4_CHANGED_ON_DT |
string |
14 |
- |
|
BUDG_BUSN_AREA_ORG_ID |
string |
80 |
Company Org identifier. Populate with integration_id from w_int_org_d where business_area_flg = Y. |
|
BUDG_CTRL_AREA_ORG_ID |
string |
80 |
Company Org identifier. Populate with integration_id from w_int_org_d where ctrl_area_flg = Y. |
|
BUDG_FIN_AREA_ORG_ID |
string |
80 |
Company Org identifier. Populate with integration_id from w_int_org_d where fin_area_flg = Y. |
|
BUDGET_CALENDAR_ID |
string |
80 |
- |
|
BUDGET_DOC_AMT |
number |
22 |
Budget amount in document currency. |
|
BUDGET_GRP_AMT |
number |
22 |
- |
|
BUDGET_ID |
string |
80 |
Populate with the value from integration_id in file_budget.csv |
|
BUDGET_LEDGER_ID |
string |
80 |
- |
|
BUDGET_LOC_AMT |
number |
22 |
Budget amount in local currency. |
|
CHANGED_BY_ID |
string |
80 |
ID of changed by user. Populate with Integration_ID from w_user_d. |
|
CHANGED_ON_DT |
string |
14 |
Changed date. Used for updating an existing record in the warehouse. Increase the date if you want to update the record. If a record with the same integration_ID exists in the target table W_ACCT_BUDGET_F already, then the load process will compare the CHANGED_ON_DT values between this record and the record in W_ACCT_BUDGET_F. If this record's CHANGED_ON_DT is later than the record in W_ACCT_BUDGET_F, then the load process will perform an update against the record in W_ACCT_BUDGET_F; otherwise it'll ignore this record, no update or insertion will occur. If there's no matching record in W_ACCT_BUDGET_F with the same integration_ID, then the load process will insert this record into W_ACCT_BUDGET_F. |
|
COMPANY_ORG_ID |
string |
80 |
Company Org identifier. Populate with integration_id from w_int_org_d where company_flg = Y. |
|
COST_CENTER_ID |
string |
80 |
Cost Center identifier. Populate with integration_id from w_cost_center_d. |
|
CREATED_BY_ID |
string |
80 |
ID of created by user. Populate with Integration_ID from w_user_d. |
|
CREATED_ON_DT |
string |
14 |
Created date. |
|
DATASOURCE_NUM_ID |
number |
10 |
A number for your data source. Populate the same datasource_num_id as your main source application. |
|
DELETE_FLG |
string |
1 |
- |
|
DOC_CURR_CODE |
string |
30 |
Document currency code. |
|
GL_ACCOUNT_ID |
string |
80 |
GL Account identifier. Populate with integration_id from w_gl_account_d. |
|
GRP_CURR_CODE |
string |
30 |
- |
|
INTEGRATION_ID |
string |
80 |
A unique identifier for the record. |
|
LOC_CURR_CODE |
string |
30 |
Local currency code. |
|
PERIOD_BEGIN_DT |
string |
14 |
- |
|
PERIOD_END_DT |
string |
14 |
Populate with your budget period's end date. If your budget is monthly, populate with the month end date. |
|
POSTED_ON_DT |
string |
14 |
A date on which this transaction can be reported. |
|
PRODUCT_ID |
string |
80 |
Product identifier. Populate with integration_id from w_product_d. |
|
PROFIT_CENTER_ID |
string |
80 |
Profit Center identifier. Populate with integration_id from w_profit_center_d. |
|
PROJECT_ID |
string |
80 |
- |
|
TENANT_ID |
string |
80 |
- |
|
X_CUSTOM |
string |
10 |
- |
Note:
Date columns should be populated in the CSV file as a number in the formatYYYYMMDDHH24MISS.Use this table to understand how the integration_id (key) of some of the key dimensions are constructed for the E-Business Suite source system. You can use this information to populate the dimension foreign key identifiers in the above universal source CSV file for budget fact, if you have to use budget fact in conjunction with dimensions populated from E-Business Suite.
| Field | How to populate |
|---|---|
|
GL_ACCOUNT_ID (w_gl_account_d) |
.GL code combination ID. |
|
COMPANY_ORG_ID (w_int_org_d) |
No need to populate; will be calculated based on GL Account ID. |
|
COST_CENTER_ID (w_cost_center_d) |
No need to populate; will be calculated based on GL Account ID. |
|
PROFIT_CENTER_ID (w_profit_center_d) |
No need to populate; will be calculated based on GL Account ID. |
|
LEDGER_ID (w_ledger_d) |
For Oracle 11i, populate as set of book ID. For Oracle R12, populate as ledger ID. |
Use this table to understand how the integration_id (key) of some of the key dimensions are constructed for Oracle's JD Edwards EnterpriseOne source systems. You can use this information to populate the dimension foreign key identifiers in the above universal source CSV file for budget fact, if you have to use budget fact in conjunction with dimensions populated from Oracle's JD Edwards EnterpriseOne.
| Field | How to populate |
|---|---|
|
GL_ACCOUNT_ID (w_gl_account_d_) |
GBAID||'~'||GBSBL||'~'||GBSBLT |
|
COMPANY_ORG_ID (w_int_org_d) |
GBCO |
|
COST_CENTER_ID (w_cost_center_d) |
GBMCU |
|
PROFIT_CENTER_ID (w_profit_center_d) |
GBCO |
|
LEDGER_ID (w_ledger_d) |
GBCO |
|
PRODUCT_ID (w_product_d) |
If GBSBLT points to item, then update product ID with that GBSBL. |
|
PROJECT_ID (w_product_d) |
Not applicable |
|
BUDG_BUSN_AREA_ORG_ID (w_int_org_d) |
GBMCU |
|
BUDG_FIN_AREA_ORG_ID (w_int_org_d) |
GBMCU |
|
BUDG_CTRL_AREA_ORG_ID (w_int_org_d) |
GBMCU |
|
BUDGET_ID (w_budget_d) |
Not applicable |