[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT COUNT(*)
INTO v_row_count
FROM fnd_lookup_values
WHERE lookup_type = 'FEM_XGL_QTD_YTD_DSC'
AND lookup_code = pv_qtd_ytd_code
AND language = USERENV('LANG')
AND view_application_id = 274;
P_LAST_UPDATE_LOGIN => pv_login_id,
P_PROGRAM_ID => pv_pgm_id,
P_PROGRAM_LOGIN_ID => pv_login_id,
P_PROGRAM_APPLICATION_ID => pv_pgm_app_id,
P_EXEC_MODE_CODE => pv_exec_mode,
X_MSG_COUNT => v_msg_count,
X_MSG_DATA => v_msg_data,
X_RETURN_STATUS => v_return_status);
P_LAST_UPDATE_LOGIN => pv_login_id,
P_EXEC_MODE_CODE => pv_exec_mode,
X_EXEC_STATE => pv_exec_state,
X_PREV_REQUEST_ID => pv_prev_req_id,
X_MSG_COUNT => v_msg_count,
X_MSG_DATA => v_msg_data,
X_RETURN_STATUS => v_return_status);
P_LAST_UPDATE_LOGIN => pv_login_id,
X_MSG_COUNT => v_msg_count,
X_MSG_DATA => v_msg_data,
X_RETURN_STATUS => v_return_status);
pv_stmt_type := 'INSERT';
P_LAST_UPDATE_LOGIN => pv_login_id,
X_MSG_COUNT => v_msg_count,
X_MSG_DATA => v_msg_data,
X_RETURN_STATUS => v_return_status);
v_statement_type := 'INSERT';
FEM_PL_PKG.Update_Num_of_Output_Rows
(P_API_VERSION => pc_API_version,
P_COMMIT => 'T',
P_REQUEST_ID => pv_req_id,
P_OBJECT_ID => pv_rule_obj_id,
P_TABLE_NAME => 'FEM_BALANCES',
P_STATEMENT_TYPE => v_statement_type,
P_NUM_OF_OUTPUT_ROWS => p_num_output_rows,
P_USER_ID => pv_user_id,
P_LAST_UPDATE_LOGIN => pv_login_id,
X_MSG_COUNT => v_msg_count,
X_MSG_DATA => v_msg_data,
X_RETURN_STATUS => v_return_status);
FEM_PL_PKG.Update_Obj_Exec_Errors
(P_API_VERSION => pc_API_version,
P_COMMIT => 'T',
P_REQUEST_ID => pv_req_id,
P_OBJECT_ID => pv_rule_obj_id,
P_ERRORS_REPORTED => p_num_data_errors,
P_ERRORS_REPROCESSED => p_num_data_errors_reproc,
P_USER_ID => pv_user_id,
P_LAST_UPDATE_LOGIN => pv_login_id,
X_MSG_COUNT => v_msg_count,
X_MSG_DATA => v_msg_data,
X_RETURN_STATUS => v_return_status);
FEM_PL_PKG.Update_Obj_Exec_Status
(P_API_VERSION => pc_API_version,
P_COMMIT => 'T',
P_REQUEST_ID => pv_req_id,
P_OBJECT_ID => pv_rule_obj_id,
P_EXEC_STATUS_CODE => p_exec_status,
P_USER_ID => pv_user_id,
P_LAST_UPDATE_LOGIN => pv_login_id,
X_MSG_COUNT => v_msg_count,
X_MSG_DATA => v_msg_data,
X_RETURN_STATUS => v_return_status);
FEM_PL_PKG.Update_Request_Status
(P_API_VERSION => pc_API_version,
P_COMMIT => 'T',
P_REQUEST_ID => pv_req_id,
P_EXEC_STATUS_CODE => p_exec_status,
P_USER_ID => pv_user_id,
P_LAST_UPDATE_LOGIN => pv_login_id,
X_MSG_COUNT => v_msg_count,
X_MSG_DATA => v_msg_data,
X_RETURN_STATUS => v_return_status);
SELECT source_system_code
INTO v_src_sys_code
FROM fem_source_systems_b
WHERE source_system_display_code = pv_ssc_tbp(i).display_code;
SELECT
xd.dimension_id,
NULL,
xd.value_set_required_flag,
tcp.column_name,
xd.member_b_table_name,
xd.member_col,
xd.member_display_code_col,
cm.interface_column_name,
xd.attribute_table_name
BULK COLLECT INTO pv_proc_keys
FROM fem_tab_column_prop tcp,
fem_tab_columns_b tc,
fem_xdim_dimensions xd,
fem_int_column_map cm
WHERE tcp.table_name = 'FEM_BALANCES'
AND tcp.column_property_code = 'PROCESSING_KEY'
AND tc.table_name = 'FEM_BALANCES'
AND tc.column_name = tcp.column_name
AND xd.dimension_id = tc.dimension_id
AND cm.target_column_name (+) = tcp.column_name
AND cm.object_type_code (+) = 'XGL_INTEGRATION'
ORDER BY tcp.column_name;
v_sql_stmt := 'SELECT source_system_display_code, COUNT(*)' ||
' FROM fem_bal_interface_t' ||
' WHERE ' || pv_ssc_where ||
' GROUP BY source_system_display_code' ||
' ORDER BY source_system_display_code';
v_rows_deleted NUMBER;
SELECT ledger_id, cal_period_id, output_dataset_code
INTO pv_ledger_id, pv_cal_period_id, pv_dataset_code
FROM fem_pl_requests
WHERE request_id = p_request_id;
SELECT dimension_id
INTO pv_cal_per_dim_id
FROM fem_dimensions_b
WHERE dimension_varchar_label = 'CAL_PERIOD';
SELECT dimension_group_id
INTO v_cal_per_dim_group_id
FROM fem_cal_periods_b
WHERE cal_period_id = pv_cal_period_id
AND personal_flag = 'N';
SELECT dimension_group_display_code
INTO pv_cal_per_dim_grp_dsp_cd
FROM fem_dimension_grps_b
WHERE dimension_group_id = v_cal_per_dim_group_id;
SELECT date_assign_value
INTO pv_cal_per_end_date
FROM fem_cal_periods_attr
WHERE attribute_id = pv_dim_attr_id
AND version_id = pv_dim_attr_ver_id
AND cal_period_id = pv_cal_period_id;
SELECT number_assign_value
INTO pv_gl_per_number
FROM fem_cal_periods_attr
WHERE attribute_id = pv_dim_attr_id
AND version_id = pv_dim_attr_ver_id
AND cal_period_id = pv_cal_period_id;
SELECT dimension_id
INTO pv_ledger_dim_id
FROM fem_dimensions_b
WHERE dimension_varchar_label = 'LEDGER';
SELECT ledger_display_code
INTO pv_ledger_dsp_cd
FROM fem_ledgers_b
WHERE ledger_id = pv_ledger_id
AND enabled_flag = 'Y'
AND personal_flag = 'N';
SELECT dimension_id
INTO pv_dataset_dim_id
FROM fem_dimensions_b
WHERE dimension_varchar_label = 'DATASET';
SELECT dim_attribute_varchar_member
INTO pv_ds_balance_type_cd
FROM fem_datasets_attr
WHERE attribute_id = pv_dim_attr_id
AND version_id = pv_dim_attr_ver_id
AND dataset_code = pv_dataset_code;
SELECT dim_attribute_numeric_member
INTO pv_budget_id
FROM fem_datasets_attr
WHERE attribute_id = pv_dim_attr_id
AND version_id = pv_dim_attr_ver_id
AND dataset_code = pv_dataset_code;
SELECT dimension_id
INTO pv_budget_dim_id
FROM fem_dimensions_b
WHERE dimension_varchar_label = 'BUDGET';
SELECT budget_display_code
INTO pv_budget_dsp_cd
FROM fem_budgets_b
WHERE budget_id = pv_budget_id;
SELECT dim_attribute_numeric_member
INTO pv_enc_type_id
FROM fem_datasets_attr
WHERE attribute_id = pv_dim_attr_id
AND version_id = pv_dim_attr_ver_id
AND dataset_code = pv_dataset_code;
SELECT dimension_id
INTO pv_enc_type_dim_id
FROM fem_dimensions_b
WHERE dimension_varchar_label = 'ENCUMBRANCE_TYPE';
SELECT encumbrance_type_code
INTO pv_enc_type_dsp_cd
FROM fem_encumbrance_types_b
WHERE encumbrance_type_id = pv_enc_type_id;
DELETE FROM fem_bal_interface_t
WHERE cal_per_dim_grp_display_code = pv_cal_per_dim_grp_dsp_cd
AND cal_period_end_date = pv_cal_per_end_date
AND cal_period_number = pv_gl_per_number
AND ledger_display_code = pv_ledger_dsp_cd
AND ds_balance_type_code = pv_ds_balance_type_cd
AND (budget_display_code = pv_budget_dsp_cd
OR pv_budget_dsp_cd is NULL)
AND (encumbrance_type_code = pv_enc_type_dsp_cd
OR pv_enc_type_dsp_cd is NULL)
AND posting_error_code IS NOT NULL;
v_rows_deleted := SQL%ROWCOUNT;
'undo_xgl_int_error_rows.v_rows_deleted',
p_app_name => 'FEM',
p_msg_name => 'FEM_GL_POST_218',
p_token1 => 'NUM',
p_value1 => TO_CHAR(v_rows_deleted),
p_token2 => 'TABLE',
p_value2 => 'FEM_BAL_INTERFACE_T');
SELECT dimension_id
INTO pv_cal_per_dim_id
FROM fem_dimensions_b
WHERE dimension_varchar_label = 'CAL_PERIOD';
SELECT dimension_id
INTO pv_ledger_dim_id
FROM fem_dimensions_b
WHERE dimension_varchar_label = 'LEDGER';
SELECT dimension_id
INTO pv_dataset_dim_id
FROM fem_dimensions_b
WHERE dimension_varchar_label = 'DATASET';
SELECT dimension_id
INTO pv_budget_dim_id
FROM fem_dimensions_b
WHERE dimension_varchar_label = 'BUDGET';
SELECT dimension_id
INTO pv_enc_type_dim_id
FROM fem_dimensions_b
WHERE dimension_varchar_label = 'ENCUMBRANCE_TYPE';
SELECT dimension_id
INTO pv_ext_acct_type_dim_id
FROM fem_dimensions_b
WHERE dimension_varchar_label = 'EXTENDED_ACCOUNT_TYPE';
SELECT dimension_id
INTO pv_nat_acct_dim_id
FROM fem_dimensions_b
WHERE dimension_varchar_label = 'NATURAL_ACCOUNT';
SELECT budget_display_code
INTO pv_budget_dsp_cd
FROM fem_budgets_b
WHERE budget_id = pv_budget_id
AND enabled_flag = 'Y'
AND personal_flag = 'N';
SELECT dim_attribute_numeric_member
INTO v_ds_budget_id
FROM fem_datasets_attr
WHERE attribute_id = pv_dim_attr_id
AND version_id = pv_dim_attr_ver_id
AND dataset_code = pv_dataset_code;
SELECT calendar_id, dimension_group_id
INTO pv_cal_per_calendar_id, v_cal_per_dim_group_id
FROM fem_cal_periods_b
WHERE cal_period_id = pv_cal_period_id
AND enabled_flag = 'Y'
AND personal_flag = 'N';
SELECT dimension_group_display_code
INTO pv_cal_per_dim_grp_dsp_cd
FROM fem_dimension_grps_b
WHERE dimension_group_id = v_cal_per_dim_group_id;
SELECT date_assign_value
INTO pv_cal_per_end_date
FROM fem_cal_periods_attr
WHERE attribute_id = pv_dim_attr_id
AND version_id = pv_dim_attr_ver_id
AND cal_period_id = pv_cal_period_id;
SELECT number_assign_value
INTO pv_gl_per_number
FROM fem_cal_periods_attr
WHERE attribute_id = pv_dim_attr_id
AND version_id = pv_dim_attr_ver_id
AND cal_period_id = pv_cal_period_id;
SELECT COUNT(*)
INTO v_rowcount
FROM fem_datasets_b
WHERE dataset_code = pv_dataset_code
AND enabled_flag = 'Y'
AND personal_flag = 'N';
SELECT dim_attribute_varchar_member
INTO pv_ds_balance_type_cd
FROM fem_datasets_attr
WHERE attribute_id = pv_dim_attr_id
AND version_id = pv_dim_attr_ver_id
AND dataset_code = pv_dataset_code;
SELECT MAX(r.output_dataset_code)
INTO pv_precedent_dataset_code
FROM fem_pl_requests r,
fem_pl_object_executions x,
fem_object_catalog_b o
WHERE r.ledger_id = pv_ledger_id
AND r.cal_period_id = pv_cal_period_id
AND r.output_dataset_code <> pv_dataset_code
AND r.exec_mode_code = 'S'
AND r.exec_status_code = 'SUCCESS'
AND x.request_id = r.request_id
AND o.object_id = x.object_id
AND o.object_type_code = 'XGL_INTEGRATION'
AND 'ACTUAL' =
(SELECT DIM_ATTRIBUTE_VARCHAR_MEMBER
FROM fem_datasets_attr
WHERE dataset_code = r.output_dataset_code
AND (attribute_id, version_id) =
(SELECT a.attribute_id, v.version_id
FROM fem_dim_attributes_b a,
fem_dim_attr_versions_b v
WHERE a.attribute_varchar_label = 'DATASET_BALANCE_TYPE_CODE'
AND v.attribute_id = a.attribute_id
and v.default_version_flag = 'Y'));
SELECT MAX(r.output_dataset_code)
INTO pv_precedent_dataset_code
FROM fem_pl_requests r,
fem_pl_object_executions x,
fem_object_catalog_b o
WHERE r.ledger_id = pv_ledger_id
AND r.cal_period_id = pv_cal_period_id
AND r.output_dataset_code <> pv_dataset_code
AND r.exec_mode_code = 'S'
AND r.exec_status_code = 'SUCCESS'
AND x.request_id = r.request_id
AND o.object_id = x.object_id
AND o.object_type_code = 'XGL_INTEGRATION'
AND pv_budget_id =
(SELECT DIM_ATTRIBUTE_NUMERIC_MEMBER
FROM fem_datasets_attr
WHERE dataset_code = r.output_dataset_code
AND (attribute_id, version_id) =
(SELECT a.attribute_id, v.version_id
FROM fem_dim_attributes_b a,
fem_dim_attr_versions_b v
WHERE a.attribute_varchar_label = 'BUDGET_ID'
AND v.attribute_id = a.attribute_id
and v.default_version_flag = 'Y'));
SELECT MAX(r.output_dataset_code)
INTO pv_precedent_dataset_code
FROM fem_pl_requests r,
fem_pl_object_executions x,
fem_object_catalog_b o
WHERE r.ledger_id = pv_ledger_id
AND r.cal_period_id = pv_cal_period_id
AND r.output_dataset_code <> pv_dataset_code
AND r.exec_mode_code = 'S'
AND r.exec_status_code = 'SUCCESS'
AND x.request_id = r.request_id
AND o.object_id = x.object_id
AND o.object_type_code = 'XGL_INTEGRATION'
AND pv_enc_type_id =
(SELECT DIM_ATTRIBUTE_NUMERIC_MEMBER
FROM fem_datasets_attr
WHERE dataset_code = r.output_dataset_code
AND (attribute_id, version_id) =
(SELECT a.attribute_id, v.version_id
FROM fem_dim_attributes_b a,
fem_dim_attr_versions_b v
WHERE a.attribute_varchar_label = 'ENCUMBRANCE_TYPE_ID'
AND v.attribute_id = a.attribute_id
and v.default_version_flag = 'Y'));
SELECT encumbrance_type_code
INTO pv_enc_type_dsp_cd
FROM fem_encumbrance_types_b
WHERE encumbrance_type_id = pv_enc_type_id
AND enabled_flag = 'Y'
AND personal_flag = 'N';
SELECT dim_attribute_numeric_member
INTO v_ds_enc_type_id
FROM fem_datasets_attr
WHERE attribute_id = pv_dim_attr_id
AND version_id = pv_dim_attr_ver_id
AND dataset_code = pv_dataset_code;
SELECT ledger_display_code
INTO pv_ledger_dsp_cd
FROM fem_ledgers_b
WHERE ledger_id = pv_ledger_id
AND enabled_flag = 'Y'
AND personal_flag = 'N';
SELECT dim_attribute_varchar_member
INTO pv_entered_crncy_flag
FROM fem_ledgers_attr
WHERE attribute_id = pv_dim_attr_id
AND version_id = pv_dim_attr_ver_id
AND ledger_id = pv_ledger_id;
SELECT dim_attribute_numeric_member
INTO pv_ledger_per_hier_obj_def_id
FROM fem_ledgers_attr
WHERE attribute_id = pv_dim_attr_id
AND version_id = pv_dim_attr_ver_id
AND ledger_id = pv_ledger_id;
SELECT object_id
INTO pv_ledger_per_hier_obj_id
FROM fem_object_definition_b
WHERE object_definition_id = pv_ledger_per_hier_obj_def_id;
SELECT calendar_id
INTO pv_ledger_calendar_id
FROM fem_hierarchies
WHERE hierarchy_obj_id = pv_ledger_per_hier_obj_id;
SELECT dim_attribute_varchar_member
INTO pv_func_ccy_code
FROM fem_ledgers_attr
WHERE attribute_id = pv_dim_attr_id
AND version_id = pv_dim_attr_ver_id
AND ledger_id = pv_ledger_id;
SELECT dim_attribute_numeric_member
INTO pv_global_vs_combo_id
FROM fem_ledgers_attr
WHERE attribute_id = pv_dim_attr_id
AND version_id = pv_dim_attr_ver_id
AND ledger_id = pv_ledger_id;
SELECT object_id, approval_status_code
INTO pv_rule_obj_id, v_obj_approval_status_cd
FROM fem_object_definition_b
WHERE object_definition_id = pv_rule_obj_def_id
AND old_approved_copy_flag = 'N';
SELECT object_type_code, folder_id
INTO pv_obj_type_cd, v_folder_id
FROM fem_object_catalog_b
WHERE object_id = pv_rule_obj_id;
SELECT dim_attribute_varchar_member
INTO v_ds_production_flag
FROM fem_datasets_attr
WHERE attribute_id = pv_dim_attr_id
AND version_id = pv_dim_attr_ver_id
AND dataset_code = pv_dataset_code;
SELECT dataset_name
INTO v_precedent_dataset_name
FROM fem_datasets_vl
WHERE dataset_code = pv_precedent_dataset_code;
SELECT ds_balance_type_name
INTO v_ds_balance_type_name
FROM fem_ds_balance_types_vl
WHERE ds_balance_type_code = pv_ds_balance_type_cd;
SELECT budget_name
INTO v_budget_name
FROM fem_budgets_vl
WHERE budget_id = pv_budget_id;
SELECT encumbrance_type_name
INTO v_enc_type_name
FROM fem_encumbrance_types_vl
WHERE encumbrance_type_id = pv_enc_type_id;
SELECT COUNT(*)
INTO v_count
FROM fem_dimensions_b fdb,
fem_dim_attributes_b fdab,
fem_dim_attr_versions_b fdavb,
fem_ext_acct_types_attr feata
WHERE fdb.dimension_varchar_label = 'EXTENDED_ACCOUNT_TYPE'
AND fdab.attribute_varchar_label = 'SIGN'
AND fdb.dimension_id = fdab.dimension_id
AND fdab.attribute_id = fdavb.attribute_id
AND fdavb.default_version_flag = 'Y'
AND fdab.attribute_id = feata.attribute_id
AND fdavb.version_id = feata.version_id;
SELECT SOURCE_SYSTEM_CODE
INTO pv_gl_source_system_code
FROM FEM_SOURCE_SYSTEMS_B
WHERE SOURCE_SYSTEM_DISPLAY_CODE = 'OGL';
SELECT balRDef.BAL_RULE_OBJ_DEF_ID
, objDefT.DISPLAY_NAME
, balRule.BAL_RULE_OBJ_ID
, balRule.LEDGER_ID
, lgr.NAME
, balRule.CHART_OF_ACCOUNTS_ID
, flex.ID_FLEX_STRUCTURE_NAME
, balRule.BAL_SEG_COLUMN_NAME
, balRule.DS_BAL_TYPE_CODE
, balRule.INCLUDE_AVG_BAL_FLAG
, balRule.MAINTAIN_QTD_FLAG
, DECODE(balRDef.LOAD_METHOD_CODE
, 'SNAPSHOT', 'S'
, 'INCREMENTAL', 'I')
, balRDef.BAL_SEG_VALUE_OPTION_CODE
, balRDef.CURRENCY_OPTION_CODE
, balRDef.XLATED_BAL_OPTION_CODE
, balRDef.ACTUAL_OUTPUT_DATASET_CODE
, objDef.EFFECTIVE_START_DATE
, objDef.EFFECTIVE_END_DATE
INTO pv_rule_obj_def_id
, pv_rule_obj_def_name
, pv_rule_obj_id
, pv_ledger_id
, pv_ledger_name
, pv_coa_id
, pv_coa_name
, pv_bsv_app_col_name
, pv_ds_balance_type_cd
, pv_include_avg_bal
, pv_maintain_qtd_flag
, pv_exec_mode
, pv_bsv_option
, pv_curr_option
, pv_xlated_bal_option
, pv_dataset_code
, pv_rule_eff_start_date
, pv_rule_eff_end_date
FROM FEM_INTG_BAL_RULES balRule
, FEM_INTG_BAL_RULE_DEFS balRDef
, FEM_OBJECT_DEFINITION_B objDef
, FEM_OBJECT_DEFINITION_TL objDefT
, FND_ID_FLEX_STRUCTURES_TL flex
, GL_LEDGERS lgr
WHERE balRule.BAL_RULE_OBJ_ID = objDef.OBJECT_ID
AND objDef.OBJECT_DEFINITION_ID = balRDef.BAL_RULE_OBJ_DEF_ID
AND objDefT.OBJECT_DEFINITION_ID = objDef.OBJECT_DEFINITION_ID
AND objDefT.LANGUAGE = USERENV('LANG')
AND balRDef.BAL_RULE_OBJ_DEF_ID = p_bal_rule_obj_def_id
AND flex.APPLICATION_ID = 101
AND flex.ID_FLEX_CODE = 'GL#'
AND flex.ID_FLEX_NUM = balRule.CHART_OF_ACCOUNTS_ID
AND flex.LANGUAGE = USERENV('LANG')
AND lgr.LEDGER_ID = balRule.LEDGER_ID;
SELECT 'Y'
INTO v_dummy_flag
FROM FEM_LEDGERS_HIER h
, FEM_LEDGERS_B p
WHERE h.HIERARCHY_OBJ_DEF_ID = 1505
AND h.CHILD_ID = pv_ledger_id
AND h.PARENT_ID = p.LEDGER_ID
AND p.LEDGER_DISPLAY_CODE = 'OGL_SOURCE_LEDGER_GROUP';
SELECT DISTINCT 'X'
INTO v_dummy_flag
FROM FEM_XDIM_DIMENSIONS xdim, FEM_DIMENSIONS_B dim
WHERE xdim.VALUE_SET_REQUIRED_FLAG = 'Y'
AND xdim.DIMENSION_ID = dim.DIMENSION_ID
AND dim.DIMENSION_VARCHAR_LABEL
NOT IN ('COMPANY', 'COST_CENTER') -- Bug fix 4158130
AND NOT EXISTS
(SELECT 'X'
FROM FEM_GLOBAL_VS_COMBO_DEFS gvsc
WHERE gvsc.DIMENSION_ID = dim.DIMENSION_ID
AND gvsc.GLOBAL_VS_COMBO_ID = pv_global_vs_combo_id);
SELECT DISTINCT 'X'
INTO v_dummy_flag
FROM fem_intg_dim_rules r
, fem_intg_dim_rule_defs rd
, fem_object_definition_b od
WHERE r.dim_rule_obj_id = od.object_id
AND rd.dim_rule_obj_def_id = od.object_definition_id
AND rd.dim_mapping_option_code <> 'SINGLEVAL'
AND r.dimension_id <> 0
AND r.dimension_id NOT IN
(SELECT tc.dimension_id
FROM fem_tab_column_prop tcp
, fem_tab_columns_b tc
WHERE tcp.table_name = 'FEM_BALANCES'
AND tcp.column_property_code = 'PROCESSING_KEY'
AND tc.table_name = 'FEM_BALANCES'
AND tc.column_name = tcp.column_name)
AND r.chart_of_accounts_id = pv_coa_id;
SELECT per.END_DATE
, per.EFFECTIVE_PERIOD_NUM
INTO pv_from_date
, pv_from_period_eff_num
FROM GL_PERIOD_STATUSES per
WHERE per.APPLICATION_ID = 101
AND per.LEDGER_ID = pv_ledger_id
AND per.PERIOD_NAME = p_from_period;
SELECT per.END_DATE
, per.EFFECTIVE_PERIOD_NUM
, (SELECT 'Y' FROM DUAL
WHERE NVL(p_effective_date, per.END_DATE) BETWEEN per.START_DATE
AND per.END_DATE)
INTO pv_to_date
, pv_to_period_eff_num
, v_dummy_flag
FROM GL_PERIOD_STATUSES per
WHERE per.APPLICATION_ID = 101
AND per.LEDGER_ID = pv_ledger_id
AND per.PERIOD_NAME = p_to_period;
SELECT 'Y'
INTO v_dummy_flag
FROM DUAL
WHERE EXISTS
(SELECT 'Loaded'
FROM FEM_DL_DIMENSIONS
WHERE LEDGER_ID = pv_ledger_id
AND EXISTS (SELECT 1
FROM GL_PERIOD_STATUSES per
WHERE per.APPLICATION_ID = 101
AND per.LEDGER_ID = pv_ledger_id
AND FEM_DL_DIMENSIONS.CAL_PERIOD_ID =
FEM_DIMENSION_UTIL_PKG.Get_Cal_Period_ID( pv_ledger_id
, 'OGL_'||per.PERIOD_TYPE
, per.PERIOD_NUM
, per.PERIOD_YEAR)
AND per.EFFECTIVE_PERIOD_NUM BETWEEN pv_from_period_eff_num
AND pv_to_period_eff_num)
AND DATASET_CODE = pv_dataset_code
AND SOURCE_SYSTEM_CODE = pv_gl_source_system_code
AND TABLE_NAME = 'FEM_BALANCES');
INSERT INTO FEM_INTG_EXEC_PARAMS_GT
( OUTPUT_DATASET_CODE
, EFFECTIVE_PERIOD_NUM
, PERIOD_NAME
, CAL_PERIOD_ID
, LOAD_METHOD_CODE
, ERROR_CODE
, NUM_OF_ROWS_SELECTED
, NUM_OF_ROWS_POSTED
)
SELECT pv_dataset_code
, per.EFFECTIVE_PERIOD_NUM
, per.PERIOD_NAME
, FEM_DIMENSION_UTIL_PKG.Get_Cal_Period_ID( pv_ledger_id
, 'OGL_'||per.PERIOD_TYPE
, per.PERIOD_NUM
, per.PERIOD_YEAR)
, pv_exec_mode
, DECODE(per.CLOSING_STATUS
, 'C', NULL
, 'O', NULL
, 'P', NULL
, 'INVALID_PERIOD_STATUS')
, 0
, 0
FROM GL_PERIOD_STATUSES per
WHERE per.APPLICATION_ID = 101
AND per.LEDGER_ID = pv_ledger_id
AND per.EFFECTIVE_PERIOD_NUM BETWEEN pv_from_period_eff_num
AND pv_to_period_eff_num;
INSERT INTO FEM_INTG_EXEC_PARAMS_GT
( OUTPUT_DATASET_CODE
, BUDGET_ID
, EFFECTIVE_PERIOD_NUM
, PERIOD_NAME
, CAL_PERIOD_ID
, LOAD_METHOD_CODE
, ERROR_CODE
, NUM_OF_ROWS_SELECTED
, NUM_OF_ROWS_POSTED
)
SELECT bgetDS.OUTPUT_DATASET_CODE
, bget.BUDGET_ID
, per.EFFECTIVE_PERIOD_NUM
, per.PERIOD_NAME
, FEM_DIMENSION_UTIL_PKG.Get_Cal_Period_ID( pv_ledger_id
, 'OGL_'||per.PERIOD_TYPE
, per.PERIOD_NUM
, per.PERIOD_YEAR)
, pv_exec_mode
-- Bug 4394404 hkaniven start - populate 'INVALID_PERIOD_STATUS' error
-- code if period's period year greater than the latest opened period
-- year of the Budget
, DECODE(SIGN(gb.latest_opened_year - per.period_year)
, -1, 'INVALID_PERIOD_STATUS'
, NULL)
-- Bug 4394404 hkaniven end - populate 'INVALID_PERIOD_STATUS' error
-- code if period's period year greater than the latest opened period
-- year of the Budget
, 0
, 0
FROM GL_PERIOD_STATUSES per
, FEM_INTG_BAL_DEF_BUDGTS bget
, FEM_INTG_BUDGT_DS bgetDS
-- Bug 4394404 hkaniven start - to get the latest opened year of each
-- budget
, GL_BUDGETS gb
, GL_BUDGET_VERSIONS gbv
-- Bug 4394404 hkaniven end - to get the latest opened year of each
-- budget
WHERE per.APPLICATION_ID = 101
AND per.LEDGER_ID = pv_ledger_id
AND per.EFFECTIVE_PERIOD_NUM BETWEEN pv_from_period_eff_num
AND pv_to_period_eff_num
AND bget.BAL_RULE_OBJ_DEF_ID = pv_rule_obj_def_id
AND bgetDS.BUDGET_ID = bget.BUDGET_ID
-- Bug 4394404 hkaniven start - to get the latest opened year of each
-- budget
AND gbv.budget_version_id = bget.budget_id
AND gb.budget_name = gbv.budget_name;
INSERT INTO FEM_INTG_EXEC_PARAMS_GT
( OUTPUT_DATASET_CODE
, ENCUMBRANCE_TYPE_ID
, EFFECTIVE_PERIOD_NUM
, PERIOD_NAME
, CAL_PERIOD_ID
, LOAD_METHOD_CODE
, ERROR_CODE
, NUM_OF_ROWS_SELECTED
, NUM_OF_ROWS_POSTED
)
SELECT encTypeDS.OUTPUT_DATASET_CODE
, encType.ENCUMBRANCE_TYPE_ID
, per.EFFECTIVE_PERIOD_NUM
, per.PERIOD_NAME
, FEM_DIMENSION_UTIL_PKG.Get_Cal_Period_ID( pv_ledger_id
, 'OGL_'||per.PERIOD_TYPE
, per.PERIOD_NUM
, per.PERIOD_YEAR)
, pv_exec_mode
-- Bug 4394404 hkaniven start - populate 'INVALID_PERIOD_STATUS' error
-- code if period's period year greater than the latest encumbrance year
, DECODE(SIGN(glgr.latest_encumbrance_year - per.period_year)
, -1, 'INVALID_PERIOD_STATUS'
, NULL)
-- Bug 4394404 hkaniven end - populate 'INVALID_PERIOD_STATUS' error
-- code if period's period year greater than the latest encumbrance year
, 0
, 0
FROM GL_PERIOD_STATUSES per
, FEM_INTG_BAL_DEF_ENCS encType
, FEM_INTG_ENC_TYPE_DS encTypeDS
-- Bug 4394404 hkaniven start - to get latest encumbrance year
, GL_LEDGERS glgr
-- Bug 4394404 hkaniven end - to get latest encumbrance year
WHERE per.APPLICATION_ID = 101
AND per.LEDGER_ID = pv_ledger_id
AND per.EFFECTIVE_PERIOD_NUM BETWEEN pv_from_period_eff_num
AND pv_to_period_eff_num
AND encType.BAL_RULE_OBJ_DEF_ID = pv_rule_obj_def_id
AND encTypeDS.ENCUMBRANCE_TYPE_ID = encType.ENCUMBRANCE_TYPE_ID
-- Bug 4394404 hkaniven start - to get latest encumbrance year
AND glgr.ledger_id = pv_ledger_id;
UPDATE FEM_INTG_EXEC_PARAMS_GT gt
SET ERROR_CODE = 'OTHER_DS_LOADED'
WHERE EFFECTIVE_PERIOD_NUM IN
(SELECT DISTINCT per.EFFECTIVE_PERIOD_NUM
FROM FEM_DL_DIMENSIONS dl
, FEM_CAL_PERIODS_ATTR year
, FEM_DIM_ATTR_VERSIONS_B yearV
, GL_PERIOD_STATUSES per
WHERE dl.LEDGER_ID = pv_ledger_id
AND dl.DATASET_CODE <> pv_dataset_code
AND dl.SOURCE_SYSTEM_CODE = pv_gl_source_system_code
AND dl.TABLE_NAME = 'FEM_BALANCES'
-- Bug fix 4335649: Change to check ACTUAL balance type only
AND dl.BALANCE_TYPE_CODE = 'ACTUAL'
AND year.CAL_PERIOD_ID = dl.CAL_PERIOD_ID
AND year.ATTRIBUTE_ID =
(SELECT ATTRIBUTE_ID
FROM FEM_DIM_ATTRIBUTES_B
WHERE ATTRIBUTE_VARCHAR_LABEL = 'ACCOUNTING_YEAR')
AND year.ATTRIBUTE_ID = yearV.ATTRIBUTE_ID
AND year.VERSION_ID = yearV.VERSION_ID
AND yearV.DEFAULT_VERSION_FLAG = 'Y'
AND year.NUMBER_ASSIGN_VALUE = per.PERIOD_YEAR
AND per.APPLICATION_ID = 101
AND per.LEDGER_ID = dl.LEDGER_ID
AND per.EFFECTIVE_PERIOD_NUM BETWEEN pv_from_period_eff_num
AND pv_to_period_eff_num);
UPDATE FEM_INTG_EXEC_PARAMS_GT
SET ERROR_CODE = 'PERIOD_NOT_MAPPED'
WHERE ERROR_CODE IS NULL
AND CAL_PERIOD_ID = -1;
SELECT NVL(min(EFFECTIVE_PERIOD_NUM), -1)
, NVL(max(EFFECTIVE_PERIOD_NUM), -1)
INTO pv_min_valid_period_eff_num
, pv_max_valid_period_eff_num
FROM FEM_INTG_EXEC_PARAMS_GT
WHERE ERROR_CODE IS NULL;
SELECT COUNT(*)
INTO pv_num_rows_valid
FROM FEM_INTG_EXEC_PARAMS_GT
WHERE ERROR_CODE IS NULL;
SELECT NVL(MAX(DELTA_RUN_ID), 0)
INTO pv_max_delta_run_id
FROM GL_BALANCES_DELTA;
SELECT DISTINCT OUTPUT_DATASET_CODE
FROM FEM_INTG_EXEC_PARAMS_GT
WHERE ERROR_CODE IS NULL;
SELECT gt.CAL_PERIOD_ID
, gt.LOAD_METHOD_CODE
, gt.PERIOD_NAME
, gt.EFFECTIVE_PERIOD_NUM
, per.END_DATE
FROM FEM_INTG_EXEC_PARAMS_GT gt
, GL_PERIOD_STATUSES per
WHERE gt.ERROR_CODE IS NULL
AND gt.OUTPUT_DATASET_CODE = p_dataset_code
AND per.PERIOD_NAME = gt.PERIOD_NAME
AND per.APPLICATION_ID = 101
AND per.LEDGER_ID = pv_ledger_id
ORDER BY gt.EFFECTIVE_PERIOD_NUM;
SELECT REQUEST_ID
FROM FEM_INTG_EXEC_PARAMS_GT
WHERE ERROR_CODE IS NULL
AND REQUEST_ID IS NOT NULL;
SELECT NVL(min(EFFECTIVE_PERIOD_NUM), -1)
, NVL(max(EFFECTIVE_PERIOD_NUM), -1)
INTO v_min_valid_eff_per_num,
v_max_valid_eff_per_num
FROM FEM_INTG_EXEC_PARAMS_GT
WHERE ERROR_CODE IS NULL
AND OUTPUT_DATASET_CODE = v_ds.output_dataset_code;
SELECT NVL(min(CAL_PERIOD_ID), -1)
, NVL(max(CAL_PERIOD_ID), -1)
INTO v_first_load_cal_per_id, v_last_load_cal_per_id
FROM FEM_DL_DIMENSIONS
WHERE LEDGER_ID = pv_ledger_id
-- 04-26-05: Changed to check balance_type_code only when the dataset
-- balance type is ACTUAL
AND ( (pv_ds_balance_type_cd = 'ACTUAL'
AND BALANCE_TYPE_CODE = pv_ds_balance_type_cd)
OR (DATASET_CODE = v_ds.output_dataset_code))
AND SOURCE_SYSTEM_CODE = pv_gl_source_system_code
AND TABLE_NAME = 'FEM_BALANCES';
SELECT CAL_PERIOD_NAME
INTO v_cal_per_name
FROM FEM_CAL_PERIODS_TL
WHERE CAL_PERIOD_ID = v_first_load_cal_per_id
AND LANGUAGE = USERENV('LANG');
SELECT number_assign_value
INTO v_cal_per_num
FROM fem_cal_periods_attr
WHERE attribute_id = pv_dim_attr_id
AND version_id = pv_dim_attr_ver_id
AND cal_period_id = v_first_load_cal_per_id;
SELECT number_assign_value
INTO v_cal_per_year
FROM fem_cal_periods_attr
WHERE attribute_id = pv_dim_attr_id
AND version_id = pv_dim_attr_ver_id
AND cal_period_id = v_first_load_cal_per_id;
SELECT effective_period_num
INTO v_first_load_eff_per_num
FROM GL_PERIOD_STATUSES
WHERE APPLICATION_ID = 101
AND LEDGER_ID = pv_ledger_id
AND PERIOD_YEAR = v_cal_per_year
AND PERIOD_NUM = v_cal_per_num;
SELECT CAL_PERIOD_NAME
INTO v_cal_per_name
FROM FEM_CAL_PERIODS_TL
WHERE CAL_PERIOD_ID = v_last_load_cal_per_id
AND LANGUAGE = USERENV('LANG');
SELECT number_assign_value
INTO v_cal_per_num
FROM fem_cal_periods_attr
WHERE attribute_id = pv_dim_attr_id
AND version_id = pv_dim_attr_ver_id
AND cal_period_id = v_last_load_cal_per_id;
SELECT number_assign_value
INTO v_cal_per_year
FROM fem_cal_periods_attr
WHERE attribute_id = pv_dim_attr_id
AND version_id = pv_dim_attr_ver_id
AND cal_period_id = v_last_load_cal_per_id;
SELECT effective_period_num
INTO v_last_load_eff_per_num
FROM GL_PERIOD_STATUSES
WHERE APPLICATION_ID = 101
AND LEDGER_ID = pv_ledger_id
AND PERIOD_YEAR = v_cal_per_year
AND PERIOD_NUM = v_cal_per_num;
SELECT 'Y'
INTO v_any_period_gap
FROM DUAL
WHERE EXISTS
(SELECT 'Period gap exists'
FROM GL_PERIOD_STATUSES
WHERE LEDGER_ID = pv_ledger_id
AND APPLICATION_ID = 101
AND ((v_first_load_eff_per_num > v_max_valid_eff_per_num
AND EFFECTIVE_PERIOD_NUM > v_max_valid_eff_per_num
AND EFFECTIVE_PERIOD_NUM < v_first_load_eff_per_num)
OR
(v_min_valid_eff_per_num > v_last_load_eff_per_num
AND EFFECTIVE_PERIOD_NUM > v_last_load_eff_per_num
AND EFFECTIVE_PERIOD_NUM < v_min_valid_eff_per_num)));
SELECT 'Y'
INTO v_dummy_flag
FROM DUAL
WHERE EXISTS
(SELECT 'Loaded'
FROM FEM_DL_DIMENSIONS
WHERE LEDGER_ID = pv_ledger_id
AND CAL_PERIOD_ID = v_cp.cal_period_id
AND DATASET_CODE = v_ds.output_dataset_code
AND SOURCE_SYSTEM_CODE = pv_gl_source_system_code
AND TABLE_NAME = 'FEM_BALANCES');
SELECT FND_CONCURRENT_REQUESTS_S.nextval
INTO v_req_id
FROM DUAL;
P_LAST_UPDATE_LOGIN => pv_login_id,
P_PROGRAM_ID => pv_pgm_id,
P_PROGRAM_LOGIN_ID => pv_login_id,
P_PROGRAM_APPLICATION_ID => pv_pgm_app_id,
P_EXEC_MODE_CODE => v_load_method,
X_MSG_COUNT => v_msg_count,
X_MSG_DATA => v_msg_data,
X_RETURN_STATUS => v_return_status);
P_LAST_UPDATE_LOGIN => pv_login_id,
P_EXEC_MODE_CODE => v_load_method,
X_EXEC_STATE => pv_exec_state,
X_PREV_REQUEST_ID => pv_prev_req_id,
X_MSG_COUNT => v_msg_count,
X_MSG_DATA => v_msg_data,
X_RETURN_STATUS => v_return_status);
UPDATE FEM_INTG_EXEC_PARAMS_GT
SET LOAD_METHOD_CODE = v_load_method
, ERROR_CODE = 'EXEC_LOCK_EXISTS'
WHERE CAL_PERIOD_ID = v_cp.cal_period_id
AND OUTPUT_DATASET_CODE = v_ds.output_dataset_code;
UPDATE FEM_INTG_EXEC_PARAMS_GT
SET REQUEST_ID = v_req_id
, LOAD_METHOD_CODE = v_load_method
WHERE CAL_PERIOD_ID = v_cp.cal_period_id
AND OUTPUT_DATASET_CODE = v_ds.output_dataset_code;
SELECT 'Y'
INTO v_any_period_gap
FROM DUAL
WHERE EXISTS
(SELECT 'Period gap exists'
FROM GL_PERIOD_STATUSES
WHERE LEDGER_ID = pv_ledger_id
AND APPLICATION_ID = 101
AND ((v_first_load_eff_per_num > v_last_reg_eff_per_num
AND EFFECTIVE_PERIOD_NUM > v_last_reg_eff_per_num
AND EFFECTIVE_PERIOD_NUM < v_first_load_eff_per_num)
OR
(v_first_reg_eff_per_num > v_last_load_eff_per_num
AND EFFECTIVE_PERIOD_NUM > v_last_load_eff_per_num
AND EFFECTIVE_PERIOD_NUM < v_first_reg_eff_per_num)));
UPDATE FEM_INTG_EXEC_PARAMS_GT
SET ERROR_CODE = 'PERIOD_GAP_EXISTS'
WHERE OUTPUT_DATASET_CODE = v_ds.output_dataset_code
AND ERROR_CODE IS NULL;
SELECT DISTINCT LOAD_METHOD_CODE
INTO v_load_method
FROM FEM_INTG_EXEC_PARAMS_GT
WHERE ERROR_CODE IS NULL
AND REQUEST_ID IS NOT NULL;
pv_stmt_type := 'INSERT';
P_LAST_UPDATE_LOGIN => pv_login_id,
X_MSG_COUNT => v_msg_count,
X_MSG_DATA => v_msg_data,
X_RETURN_STATUS => v_return_status);
SELECT DISTINCT bpi.DATASET_CODE
, bpi.CAL_PERIOD_ID
, bpi.CURRENCY_CODE
FROM FEM_BAL_POST_INTERIM_GT bpi
WHERE bpi.CURRENCY_TYPE_CODE = 'TRANSLATED'
AND bpi.POSTING_ERROR_FLAG = 'N'
AND NOT EXISTS
(SELECT 'Invalid Delta Load'
FROM FEM_INTG_DELTA_LOADS dl
WHERE dl.LEDGER_ID = bpi.LEDGER_ID
AND dl.DATASET_CODE = bpi.DATASET_CODE
AND dl.CAL_PERIOD_ID = bpi.CAL_PERIOD_ID
AND dl.DELTA_RUN_ID = bpi.DELTA_RUN_ID
AND dl.LOADED_FLAG = 'N')
ORDER BY bpi.DATASET_CODE, bpi.CAL_PERIOD_ID, bpi.CURRENCY_CODE;
SELECT REQUEST_ID
, OUTPUT_DATASET_CODE
, PERIOD_NAME
, CAL_PERIOD_ID
, NUM_OF_ROWS_SELECTED
, NUM_OF_ROWS_POSTED
, ERROR_CODE
FROM FEM_INTG_EXEC_PARAMS_GT
WHERE REQUEST_ID IS NOT NULL
ORDER BY OUTPUT_DATASET_CODE, CAL_PERIOD_ID;
p_value1 => 'v_req.num_of_rows_selected',
p_token2 => 'VAR_VAL',
p_value2 => TO_CHAR(v_req.num_of_rows_selected));
FEM_PL_PKG.Update_Num_of_Output_Rows
(P_API_VERSION => pc_API_version,
P_COMMIT => 'T',
P_REQUEST_ID => v_req.request_id,
P_OBJECT_ID => pv_rule_obj_id,
P_TABLE_NAME => 'FEM_BALANCES',
P_STATEMENT_TYPE => pv_stmt_type,
P_NUM_OF_OUTPUT_ROWS => v_req.num_of_rows_posted,
P_USER_ID => pv_user_id,
P_LAST_UPDATE_LOGIN => pv_login_id,
X_MSG_COUNT => v_msg_count,
X_MSG_DATA => v_msg_data,
X_RETURN_STATUS => v_return_status);
IF (v_req.num_of_rows_selected > v_req.num_of_rows_posted)
THEN
-- Initialize the FND Message API
FND_MSG_PUB.Initialize;
FEM_PL_PKG.Update_Obj_Exec_Errors
(P_API_VERSION => pc_API_version,
P_COMMIT => 'T',
P_REQUEST_ID => v_req.request_id,
P_OBJECT_ID => pv_rule_obj_id,
P_ERRORS_REPORTED => (v_req.num_of_rows_selected
- v_req.num_of_rows_posted),
P_ERRORS_REPROCESSED => 0,
P_USER_ID => pv_user_id,
P_LAST_UPDATE_LOGIN => pv_login_id,
X_MSG_COUNT => v_msg_count,
X_MSG_DATA => v_msg_data,
X_RETURN_STATUS => v_return_status);
END IF; -- IF (v_req.num_of_rows_selected > v_req.num_of_rows_posted)
OR (v_req.num_of_rows_posted = 0 AND v_req.num_of_rows_selected > 0)
OR (v_req.error_code IS NOT NULL))
THEN
-- Set the execution status to ERROR_RERUN when:
-- * The passed Execution Status is ERROR_RERUN
-- * OR there are rows selected but none of them can be posted
-- * OR the error code of this Request is populated i.e. the possible
-- error code will be PERIOD_GAP_EXIST
v_exec_status := 'ERROR_RERUN';
FEM_PL_PKG.Update_Obj_Exec_Status
(P_API_VERSION => pc_API_version,
P_COMMIT => 'T',
P_REQUEST_ID => v_req.request_id,
P_OBJECT_ID => pv_rule_obj_id,
P_EXEC_STATUS_CODE => v_exec_status,
P_USER_ID => pv_user_id,
P_LAST_UPDATE_LOGIN => pv_login_id,
X_MSG_COUNT => v_msg_count,
X_MSG_DATA => v_msg_data,
X_RETURN_STATUS => v_return_status);
FEM_PL_PKG.Update_Request_Status
(P_API_VERSION => pc_API_version,
P_COMMIT => 'T',
P_REQUEST_ID => v_req.request_id,
P_EXEC_STATUS_CODE => v_exec_status,
P_USER_ID => pv_user_id,
P_LAST_UPDATE_LOGIN => pv_login_id,
X_MSG_COUNT => v_msg_count,
X_MSG_DATA => v_msg_data,
X_RETURN_STATUS => v_return_status);
IF (v_req.num_of_rows_selected = v_req.num_of_rows_posted)
THEN
-- All rows are posted, so set the load status to COMPLETE
v_load_status := 'COMPLETE';
END IF; -- IF (v_req.num_of_rows_selected = v_req.num_of_rows_posted)