[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
c_stmt_type CONSTANT VARCHAR2(6) := 'INSERT'; --INSERT
select a.object_definition_id,
c.value_set_id,
a.effective_start_date,
a.effective_end_date
into OBJ_INFO
from fem_object_definition_b a,
fem_object_catalog_b b,
fem_global_vs_combo_defs c
where a.object_id = OBJ_ID
and a.old_approved_copy_flag = 'N'
and trunc(EFFECTIVE_DATE) between a.effective_start_date
and nvl(a.effective_end_date,
to_date('99991231', 'YYYYMMDD'))
and a.object_id = b.object_id
and b.local_vs_combo_id = c.global_vs_combo_id(+)
and c.dimension_id(+) = main_dim_id;
select transfer_price_object_id, prepay_object_id
into tp_obj_id, pp_obj_id
from ftp_tp_process_rule
where object_definition_id = process_info.obj_id;
select local_vs_combo_id into combo_id
from fem_object_catalog_b where object_id = tp_obj_id;
select value_set_id into ORG_VAL_SET
from fem_global_vs_combo_defs where dimension_id = 8 and
global_vs_combo_id = combo_id;
select value_set_id into LN_ITEM_VAL_SET
from fem_global_vs_combo_defs where dimension_id = 14 and
global_vs_combo_id = combo_id;
EXECUTE IMMEDIATE 'select source_system_code from fem_source_systems_vl
where source_system_display_code = :1' into SOURCE_SYS_CD USING c_app;
select hierarchy_id
from fem_rule_hierarchies
where object_id = id
and dimension_id = main_dim_id;
select
cat.object_id hierarchy_id
from
fem_object_catalog_b cat,
fem_object_dependencies dep,
fem_object_definition_b def
where
def.object_id = id and
eff_date between def.effective_start_date and def.effective_end_date and
def.object_definition_id = dep.object_definition_id and
dep.required_object_id = cat.object_id and
cat.object_type_code = 'HIERARCHY';
delete ftp_tp_pp_node_map where map_id = node_map_id;
insert into ftp_tp_pp_node_map
(node_map_id, line_item_id, value_set_id,
currency, tp_node,
pp_node,
creation_date, created_by, last_updated_by,
last_update_date, last_update_login,
created_by_object_id,created_by_request_id,
last_updated_by_object_id,last_updated_by_request_id)
select distinct map_id, line_item_id, l_value_set_id,
currency, line_item_id,
null, sysdate,
user_id, user_id, sysdate, login_id,
OBJECT_ID,REQUEST_ID,OBJECT_ID,REQUEST_ID from ftp_transfer_price_rule
where object_definition_id = tp_obj_def;
update ftp_tp_pp_node_map a set pp_node =
(select distinct line_item_id from ftp_prepayment_rule p
where p.object_definition_id = pp_obj_def
and p.line_item_id = a.line_item_id
and p.currency = a.currency),
-- The object id will always be the same.
created_by_request_id = REQUEST_ID,
last_updated_by_request_id = REQUEST_ID
where a.node_map_id = map_id;
(select distinct line_item_id, currency from ftp_adjustment_rule
where object_definition_id = adj_obj_def) adj
ON (nm.line_item_id =adj.line_item_id and nm.currency=adj.currency
and nm.node_map_id = map_id)
WHEN MATCHED THEN
update set adj_node=adj.line_item_id
WHEN NOT MATCHED THEN
insert (node_map_id, line_item_id, value_set_id,
currency, adj_node, tp_node, pp_node,
creation_date, created_by, last_updated_by,
last_update_date, last_update_login,
created_by_object_id,created_by_request_id,
last_updated_by_object_id,last_updated_by_request_id) values
(map_id, adj.line_item_id, l_value_set_id, adj.currency, adj.line_item_id,
NULL, NULL, sysdate, user_id, user_id, sysdate, login_id,
OBJECT_ID,REQUEST_ID,OBJECT_ID,REQUEST_ID);
cursor c is select distinct currency from ftp_transfer_price_rule
where object_definition_id = tp_obj_def;
delete ftp_tp_pp_node_map where map_id = node_map_id;
insert into ftp_tp_pp_node_map
(node_map_id, line_item_id,
value_set_id,
currency,
tp_node,
pp_node,
creation_date, created_by, last_updated_by,
last_update_date, last_update_login,
created_by_object_id,created_by_request_id,
last_updated_by_object_id,last_updated_by_request_id)
select map_id, h.child_id, l_value_set_id,
cur.currency, h.parent_id,
null, sysdate,
user_id, user_id, sysdate, login_id,
OBJECT_ID,REQUEST_ID,OBJECT_ID,REQUEST_ID
from fem_ln_items_hier h
where h.hierarchy_obj_def_id = hier_id
/* restrict to this value set */
and l_value_set_id = h.parent_value_set_id
and l_value_set_id = h.child_value_set_id
/* restrict to parents in tp id */
and exists (select null from ftp_transfer_price_rule tp
where tp.object_definition_id = tp_obj_def
and tp.line_item_id = h.parent_id
and tp.currency = cur.currency)
/* restrict to leaves in hierarchy */
and not exists (select null from fem_ln_items_hier h1
where h1.hierarchy_obj_def_id
= hier_id
and h1.parent_id = h.child_id
and h1.parent_value_set_id = l_value_set_id
and h1.child_value_set_id = l_value_set_id
and h1.child_id <> h.child_id)
/* restrict to lowest level when overrides exist */
/* select max level num parent existing for given child within ID */
and h.parent_depth_num = (select max(h1.parent_depth_num)
from fem_ln_items_hier h1,
ftp_transfer_price_rule q
where h1.hierarchy_obj_def_id
= hier_id
and q.object_definition_id = tp_obj_def
and q.line_item_id = h1.parent_id
and h1.parent_value_set_id
= l_value_set_id
and h1.child_value_set_id
= l_value_set_id
and q.currency = cur.currency
and h1.child_id = h.child_id);
cursor c is select distinct currency from ftp_prepayment_rule
where object_definition_id = pp_obj_def;
update ftp_tp_pp_node_map a set pp_node =
(select h.parent_id
from fem_ln_items_hier h
where h.hierarchy_obj_def_id = hier_id
and h.parent_value_set_id = l_value_set_id
/* restrict to leaves in hierarchy */
and a.line_item_id = h.child_id
and h.child_value_set_id = l_value_set_id
/* restrict to parents in pp id */
and exists (select null from ftp_prepayment_rule pp
where pp.object_definition_id = pp_obj_def
and pp.line_item_id = h.parent_id
and pp.currency = cur.currency)
/* restrict to lowest level when overrides exist */
/* select max level num parent existing for given */
/* child within ID */
and h.parent_depth_num = (select max(h1.parent_depth_num)
from fem_ln_items_hier h1,
ftp_prepayment_rule q
where h1.hierarchy_obj_def_id
= hier_id
and q.object_definition_id
= pp_obj_def
and q.line_item_id = h1.parent_id
and l_value_set_id
= h1.parent_value_set_id
and q.currency = cur.currency
and h1.child_id = h.child_id
and h1.child_value_set_id
= l_value_set_id)),
-- The object id will always be the same.
created_by_request_id = REQUEST_ID,
last_updated_by_request_id = REQUEST_ID
where a.node_map_id = map_id and a.currency = cur.currency;
cursor c is select distinct currency from ftp_adjustment_rule
where object_definition_id = adj_obj_def;
(select h.child_id, cur.currency, h.parent_id
from fem_ln_items_hier h
where h.hierarchy_obj_def_id = hier_id
/* restrict to this value set */
and l_value_set_id = h.parent_value_set_id
and l_value_set_id = h.child_value_set_id
/* restrict to parents in adjustment id */
and exists (select null from ftp_adjustment_rule adj
where adj.object_definition_id = adj_obj_def
and adj.line_item_id = h.parent_id
and adj.currency = cur.currency)
/* restrict to leaves in hierarchy */
and not exists (select null from fem_ln_items_hier h1
where h1.hierarchy_obj_def_id
= hier_id
and h1.parent_id = h.child_id
and h1.parent_value_set_id = l_value_set_id
and h1.child_value_set_id = l_value_set_id
and h1.child_id <> h.child_id)
/* restrict to lowest level when overrides exist */
/* select max level num parent existing for given child within ID */
and h.parent_depth_num = (select max(h1.parent_depth_num)
from fem_ln_items_hier h1,
ftp_adjustment_rule q
where h1.hierarchy_obj_def_id
= hier_id
and q.object_definition_id = adj_obj_def
and q.line_item_id = h1.parent_id
and h1.parent_value_set_id
= l_value_set_id
and h1.child_value_set_id
= l_value_set_id
and q.currency = cur.currency
and h1.child_id = h.child_id)) adj
ON (nm.line_item_id = adj.child_id and nm.currency=cur.currency
and node_map_id = map_id)
WHEN MATCHED THEN
update set adj_node = adj.parent_id
WHEN NOT MATCHED THEN
insert (node_map_id, line_item_id,value_set_id,currency,
adj_node, tp_node, pp_node,
creation_date, created_by, last_updated_by,
last_update_date, last_update_login,
created_by_object_id,created_by_request_id,
last_updated_by_object_id,last_updated_by_request_id) values
(map_id, adj.child_id, l_value_set_id,
cur.currency,adj.parent_id,null,
null, sysdate,
user_id, user_id, sysdate, login_id,
OBJECT_ID,REQUEST_ID,OBJECT_ID,REQUEST_ID);
select ftp_node_map_id_seq.nextval into map_id from dual;
insert into ftp_tp_pp_node_header (
node_map_id,
tp_process_object_def_id,
tp_object_def_id,
pp_object_def_id,
adj_object_def_id,
effective_start_date,
effective_end_date,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
created_by_object_id,
created_by_request_id,
last_updated_by_object_id,
last_updated_by_request_id
) values (
map_id,
proc_def_id,
tp_def_id,
pp_def_id,
adj_def_id,
e_s_date,
e_e_date,
sysdate,
fnd_global.user_id,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
OBJECT_ID,
REQUEST_ID,
OBJECT_ID,
REQUEST_ID
);
select transfer_price_object_id, prepay_object_id, adjustment_object_id
into tp_obj_id, pp_obj_id, adj_obj_id
from ftp_tp_process_rule
where object_definition_id = proc_def_id;
procedure check_hier_update (map_id in number,
proc_def_id in number,
tp_def_id in number,
pp_def_id in number,
adj_def_id in number,
e_date in date,
tp_hier_def_id in number,
pp_hier_def_id in number,
adj_hier_def_id in number,
hier_updated out nocopy boolean
)
is
l_tp_hier_date DATE;
select max(last_update_date) into l_tp_hier_date from fem_ln_items_hier h
where h.hierarchy_obj_def_id = tp_hier_def_id group by h.hierarchy_obj_def_id;
select max(last_update_date) into l_pp_hier_date from fem_ln_items_hier h
where h.hierarchy_obj_def_id = pp_hier_def_id group by h.hierarchy_obj_def_id;
select max(last_update_date) into l_adj_hier_date from fem_ln_items_hier h
where h.hierarchy_obj_def_id = adj_hier_def_id group by h.hierarchy_obj_def_id;
select last_update_date into l_node_map_date from ftp_tp_pp_node_header where
tp_process_object_def_id = proc_def_id
and (tp_object_def_id = tp_def_id
or tp_object_def_id is null and tp_def_id is null)
and (pp_object_def_id = pp_def_id
or pp_object_def_id is null and pp_def_id is null)
and (adj_object_def_id = adj_def_id
or adj_object_def_id is null and adj_def_id is null)
and trunc(e_date) between effective_start_date and
nvl(effective_end_date,
to_date('99991231',
'YYYYMMDD'));
hier_updated := TRUE;
hier_updated := FALSE;
hier_updated := TRUE;
hier_updated := FALSE;
hier_updated := TRUE;
hier_updated := FALSE;
if hier_updated then
update ftp_tp_pp_node_header a
set last_updated_by = fnd_global.user_id,
last_update_date = sysdate,
last_update_login = fnd_global.login_id
where a.node_map_id = map_id;
hier_updated := FALSE;
end check_hier_update;
select effective_start_date into s_date
from ftp_tp_pp_node_header
where node_map_id = map_id;
update ftp_tp_pp_node_header a
set effective_end_date = e_date,
last_updated_by = fnd_global.user_id,
last_update_date = sysdate,
last_update_login = fnd_global.login_id
where a.node_map_id = map_id;
hier_updated boolean;
select node_map_id,
effective_start_date,
effective_end_date
into map_id.obj_id,
map_id.eff_start_date,
map_id.eff_end_date
from ftp_tp_pp_node_header
where tp_process_object_def_id = process_info.obj_id
and (tp_object_def_id = tp_info.obj_id
or tp_object_def_id is null and tp_info.obj_id is null)
and (pp_object_def_id = pp_info.obj_id
or pp_object_def_id is null and pp_info.obj_id is null)
and (adj_object_def_id = adj_info.obj_id
or adj_object_def_id is null and adj_info.obj_id is null)
and trunc(effective_date) between effective_start_date and
nvl(effective_end_date,
to_date('99991231',
'YYYYMMDD'));
check_hier_update(node_map_id,
process_info.obj_id,
tp_info.obj_id,
pp_info.obj_id,
adj_info.obj_id,
effective_date,
tp_hier_info.obj_id,
pp_hier_info.obj_id,
adj_hier_info.obj_id,
hier_updated
);
update ftp_tp_pp_node_header a
set effective_end_date = e_date,
last_updated_by = fnd_global.user_id,
last_update_date = sysdate,
last_update_login = fnd_global.login_id,
-- The object id will always be the same.
created_by_request_id = REQUEST_ID,
last_updated_by_request_id = REQUEST_ID
where a.node_map_id = validate_node_map.node_map_id;
if hier_updated then
--delete existing details and recreate them
delete ftp_tp_pp_node_map a where a.node_map_id = node_map_id;
select calc_mode_code into remain_term_flag from ftp_tp_proc_stoch_params
where object_definition_id = process_info.obj_id and job_id = jobid;
LAST_OBJID_COL := 'last_updated_by_object_id';
LAST_REQID_COL := 'last_updated_by_request_id';
ATTR_COL_NAME out NOCOPY varchar2, -- attribute column to select
IS_ASSET_DECODE out NOCOPY varchar2, -- decode to determine if asset/liab
WHERE_CLAUSE out NOCOPY varchar2 -- where clause for join
)
is
begin
select attribute_table_name into join_tbl_name
from fem_xdim_dimensions
where dimension_id = main_dim_id;
select attribute_value_column_name into attr_col_name
from fem_dim_attributes_b
where dimension_id = main_dim_id
and attribute_id = acct_type_attr_id;
select ext.ext_account_type_code,
(decode(af.dim_attribute_varchar_member, 'Y', 1, 0)
+ decode(obf.dim_attribute_varchar_member, 'Y', 2, 0)) flags
from fem_ext_account_types_b ext,
fem_ext_acct_types_attr af,
fem_ext_acct_types_attr obf
where ext.ext_account_type_code = af.ext_account_type_code
and af.attribute_id = ASSET_FLAG_ID
and ext.ext_account_type_code = obf.ext_account_type_code
and obf.attribute_id = OFF_BAL_FLAG_ID;
Attr_COL_NAME out NOCOPY varchar2, -- attribute column to select
ACCR_DECODE out NOCOPY varchar2, -- decode to determine if asset/liab
WHERE_CLAUSE out NOCOPY varchar2 -- where clause for join
)
is
begin
join_tbl_name := 'FTP_LN_ITEM_CURRENCIES'; -- Changed to FTP_LN_ITEM_CURRENCIES since used only by FTP -Mallica
FOR indx IN (select input_dataset_code from fem_ds_input_lists
where dataset_io_obj_def_id = p_io_def_id)
LOOP
x_datasets := x_datasets || indx.input_dataset_code ||',';
p_last_update_login IN NUMBER,
p_program_id IN NUMBER,
p_program_login_id IN NUMBER,
p_program_application_id IN NUMBER,
x_exec_lock_exists OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
process_info obj_info_type;
CURSOR l_cur_tables IS select table_name from ftp_tp_proc_tabls_params
where object_definition_id = process_info.obj_id and job_id = p_job_id;
select output_dataset_code into l_output_ds from fem_ds_input_output_defs
where dataset_io_obj_def_id = p_dataset_def_id;
select filter_object_id into l_condition_id from ftp_tp_proc_stoch_params where
object_definition_id = process_info.obj_id and job_id = p_job_id;
p_last_update_login => p_last_update_login,
p_program_id => p_program_id,
p_program_login_id => p_program_login_id,
p_program_application_id => p_program_application_id,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_return_status => x_return_status
);
l_table_names.DELETE;
p_last_update_login => p_last_update_login,
x_exec_state => l_exec_state,
x_prev_request_id => l_prev_request_id,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_return_status => x_return_status
);
p_last_update_login => p_last_update_login,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_return_status => x_return_status
);
p_last_update_login => p_last_update_login,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_return_status => x_return_status
);
l_stmt_type := 'UPDATE';
p_last_update_login => p_last_update_login,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_return_status => x_return_status
);
p_last_update_login => p_last_update_login,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_return_status => x_return_status
);
p_last_update_login => p_last_update_login,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_return_status => x_return_status
);
l_table_names.DELETE;
p_last_update_login IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_input_cnt NUMBER;
l_select_stmt VARCHAR2(4000);
CURSOR l_cur_tables IS select table_name from ftp_tp_proc_tabls_params
where object_definition_id = process_info.obj_id and job_id = p_job_id;
FND_MSG_PUB.Delete_Msg();
select filter_object_id into l_condition_id from ftp_tp_proc_stoch_params where
object_definition_id = process_info.obj_id and job_id = p_job_id;
select INSTR(modify_valid_table_list,',') into pos from dual;
select length(modify_valid_table_list) into len from dual;
select substr(modify_valid_table_list,0,pos-1) into token from dual;
select LTRIM(RTRIM(token,''''),'''') into token from dual;
select substr(modify_valid_table_list,pos+1,len) into modify_valid_table_list from dual;
select length(modify_valid_table_list) into len2 from dual;
l_select_stmt := 'select count(*) from '|| l_table_names(i) ||
' where (ledger_id = :1) and (cal_period_id = :2) and dataset_code in '||
l_input_ds||' and '||l_condition_sql;
l_select_stmt := 'select count(*) from '|| l_table_names(i) ||
' where (ledger_id = :1) and (cal_period_id = :2) and dataset_code in '||
l_input_ds;
EXECUTE IMMEDIATE l_select_stmt INTO l_input_cnt USING p_ledger_id,p_cal_period_id;
l_select_stmt := 'select count(*) from '||l_table_names(i) ||
' where last_updated_by_object_id = :1 and last_updated_by_request_id = :2';
EXECUTE IMMEDIATE l_select_stmt INTO l_output_cnt USING p_object_id,p_request_id;
select trans_rate_propagate_flg,trans_rate_calc_flg,
option_cost_propagate_flg,option_cost_calc_flg,calc_mode_code,adj_calc_flg
into trate_prop_flg,trate_calc_flg,ocost_prop_flg,ocost_calc_flg,
calcmode_flg,l_adj_calc_flag
from ftp_tp_proc_stoch_params
where object_definition_id = process_info.obj_id and job_id = p_job_id;
l_col_names(8) := 'last_updated_by_object_id';
l_col_names(9) := 'last_updated_by_request_id';
l_stmt_type := 'UPDATE';
p_module => g_block || '.REGISTER_UPDATED_COLUMN',
p_msg_text => 'before REGISTER_UPDATED_COLUMN j = ' || j ||' l_col_names(j)=' ||l_col_names(j)
);
fem_pl_pkg.REGISTER_UPDATED_COLUMN(
p_api_version => c_api_version,
p_request_id => p_request_id,
p_object_id => p_object_id,
p_table_name => l_table_names(i),
p_statement_type => l_stmt_type,
p_column_name => UPPER(l_col_names(j)),
p_user_id => p_user_id,
p_last_update_login => p_last_update_login,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_return_status => x_return_status
);
FEM_PL_PKG.update_num_of_output_rows(
p_api_version => c_api_version,
p_request_id => p_request_id,
p_object_id => p_object_id,
p_table_name => l_table_names(i),
p_statement_type => l_stmt_type,
p_num_of_output_rows => l_output_cnt,
p_user_id => p_user_id,
p_last_update_login => p_last_update_login,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_return_status => x_return_status
);
select count(*) into l_output_cnt from FTP_TP_PP_NODE_HEADER
where created_by_object_id = OBJECT_ID and created_by_request_id = REQUEST_ID;
FEM_PL_PKG.update_num_of_output_rows(
p_api_version => c_api_version,
p_request_id => p_request_id,
p_object_id => p_object_id,
p_table_name => 'FTP_TP_PP_NODE_HEADER',
p_statement_type => c_stmt_type,
p_num_of_output_rows => l_output_cnt,
p_user_id => p_user_id,
p_last_update_login => p_last_update_login,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_return_status => x_return_status
);
select count(*) into l_output_cnt from FTP_TP_PP_NODE_MAP
where created_by_object_id = OBJECT_ID and created_by_request_id = REQUEST_ID;
FEM_PL_PKG.update_num_of_output_rows(
p_api_version => c_api_version,
p_request_id => p_request_id,
p_object_id => p_object_id,
p_table_name => 'FTP_TP_PP_NODE_MAP',
p_statement_type => c_stmt_type,
p_num_of_output_rows => l_output_cnt,
p_user_id => p_user_id,
p_last_update_login => p_last_update_login,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_return_status => x_return_status
);
FEM_PL_PKG.update_num_of_input_rows (
p_api_version => c_api_version,
p_commit => FND_API.G_FALSE,
p_request_id => p_request_id,
p_object_id => p_object_id,
p_num_of_input_rows => l_total_inp,
p_user_id => p_user_id,
p_last_update_login => p_last_update_login,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_return_status => x_return_status
);
EXECUTE IMMEDIATE 'select source_system_code from fem_source_systems_vl
where source_system_display_code = :1' into l_ss_code USING c_app;
select output_dataset_code into l_output_ds from fem_ds_input_output_defs
where dataset_io_obj_def_id = p_dataset_def_id;
fem_pl_pkg.UPDATE_OBJ_EXEC_STATUS(
p_api_version => c_api_version,
p_request_id => p_request_id,
p_object_id => p_object_id,
p_exec_status_code => l_exec_status_code,
p_user_id => p_user_id,
p_last_update_login => p_last_update_login,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_return_status => x_return_status
);
FEM_PL_PKG.update_request_status(
p_api_version => c_api_version,
p_request_id => p_request_id,
p_exec_status_code => l_exec_status_code,
p_user_id => p_user_id,
p_last_update_login => p_last_update_login,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_return_status => x_return_status
);
l_table_names.DELETE;
fem_pl_pkg.UPDATE_OBJ_EXEC_STATUS(
p_api_version => c_api_version,
p_request_id => p_request_id,
p_object_id => p_object_id,
p_exec_status_code => c_prg_err_undo,
p_user_id => p_user_id,
p_last_update_login => p_last_update_login,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_return_status => x_return_status
);
FEM_PL_PKG.update_request_status(
p_api_version => c_api_version,
p_request_id => p_request_id,
p_exec_status_code => c_prg_err_undo,
p_user_id => p_user_id,
p_last_update_login => p_last_update_login,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_return_status => x_return_status
);
fem_pl_pkg.UPDATE_OBJ_EXEC_STATUS(
p_api_version => c_api_version,
p_request_id => p_request_id,
p_object_id => p_object_id,
p_exec_status_code
=> c_prg_err_undo,
p_user_id => p_user_id,
p_last_update_login => p_last_update_login,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_return_status => x_return_status
);
FEM_PL_PKG.update_request_status(
p_api_version => c_api_version,
p_request_id => p_request_id,
p_exec_status_code => c_prg_err_undo,
p_user_id => p_user_id,
p_last_update_login => p_last_update_login,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_return_status => x_return_status
);
p_last_update_login IN NUMBER,
x_exec_lock_exists OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
TYPE t_obj_id_tbl IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
CURSOR l_cur_tables IS select table_name from ftp_tp_proc_tabls_params
where object_definition_id = process_info.obj_id and job_id = p_job_id;
select output_dataset_code into l_output_ds from fem_ds_input_output_defs
where dataset_io_obj_def_id = p_dataset_def_id;
select trans_rate_migrate_flg,option_cost_migrate_flg into l_trate_migr,l_ocost_migr
from ftp_tp_proc_stoch_params where object_definition_id = process_info.obj_id
and job_id = p_job_id;
FEM_UD_PKG.Delete_Balances (
p_api_version => c_api_version,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
p_encoded => FND_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_current_request_id => p_request_id,
p_object_id => p_object_id,
p_cal_period_id => p_cal_period_id,
p_ledger_id => p_ledger_id,
p_dataset_code => l_output_ds
);
l_sql_stmt := 'select distinct CREATED_BY_OBJECT_ID,CREATED_BY_REQUEST_ID
from ' || l_table_names(i) ||
' where ledger_id=:1 and cal_period_id = :2 and created_by_object_id <> :3';
p_last_update_login => p_last_update_login,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_return_status => x_return_status
);
/*DELETE from fem_balances where created_by_object_id = p_object_id AND
(DATASET_CODE = l_output_ds) AND (LEDGER_ID = p_ledger_id) AND
(CAL_PERIOD_ID = p_cal_period_id);*/
select count(*) into l_chain_count from fem_pl_chains
where source_created_by_object_id = p_object_id;
CURSOR l_cur_tables IS select table_name from ftp_tp_proc_tabls_params
where object_definition_id = process_info.obj_id and job_id = jobid;
FND_MSG_PUB.Delete_Msg();
select ALT_RATE_OP_OBJECT_ID,adjustment_object_id
into rate_output_rule_obj_id,adj_object_id
from ftp_tp_process_rule
where object_definition_id = process_info.obj_id ;
select distinct(adjustment_type_code) into adj_type_code
from ftp_adjustment_rule
where object_definition_id = adj_rule_info.obj_id; --11762;
LAST_OBJID_COL := 'last_updated_by_object_id';
LAST_REQID_COL := 'last_updated_by_request_id';
select count(*) into cnt from FEM_TABLE_CLASS_ASSIGNMT
where table_name = l_table_names(i) --'FEM_BREAK_FUNDING'
and table_classification_code ='FTP_BREAK_FUND';
select count(calc_method_code) into ecoloss_meth_count
from ftp_adjustment_rule where
object_definition_id = adj_rule_info.obj_id and
calc_method_code = c_ecoloss_method;
FOR ids IN (SELECT
i.table_name,
max (DECODE (TRANSFER_RATE_COL_NAME, column_name, TRANSFER_RATE_COL_NAME,NULL,NULL, -1)) as trate,
max (DECODE (MATCHED_SPREAD_COL_NAME, column_name, MATCHED_SPREAD_COL_NAME, NULL,NULL, -1)) as match_spread,
max (DECODE (REMAINING_TERM_COL_NAME, column_name, REMAINING_TERM_COL_NAME, NULL,NULL, -1)) as rem_term,
max (DECODE (HIST_OAS_COL_NAME, column_name, HIST_OAS_COL_NAME, NULL,NULL, -1)) as hist_option,
max (DECODE (ADJUSTMENT_SPRD_COL_NAME, column_name, ADJUSTMENT_SPRD_COL_NAME, NULL,NULL, -1)) as adj_rate,
max(DECODE(HIST_STAT_SPREAD_COL_NAME, column_name, HIST_STAT_SPREAD_COL_NAME, NULL,NULL, -1)) as hist_stat_spr,
max(DECODE(CUR_STAT_SPREAD_COL_NAME, column_name, CUR_STAT_SPREAD_COL_NAME, NULL,NULL, -1)) as cur_stat_spr,
max(DECODE(ADJUSTMENT_AMOUNT_COL_NAME, column_name, ADJUSTMENT_AMOUNT_COL_NAME, NULL,NULL, -1)) as adj_amt,
max(DECODE(CUR_OAS_COL_NAME, column_name, CUR_OAS_COL_NAME, NULL,NULL, -1)) as cur_option
FROM fem_tab_columns_v i, FTP_RATE_OUTPUT_MAPPING_RULE j, ftp_tp_proc_tabls_params k
WHERE i.table_name = j. FTP_ACCOUNT_TABLE_NAME
AND j. FTP_ACCOUNT_TABLE_NAME = k.table_name
AND job_id = jobid
AND k.object_definition_id = process_info.obj_id
AND j.object_definition_id = rate_map_info.obj_id
GROUP BY i.table_name) LOOP
g_alt_rate_obj.extend();
LAST_OBJID_COL := 'last_updated_by_object_id';
LAST_REQID_COL := 'last_updated_by_request_id';
select RTRIM(valid_table_list,',') into new_valid_table_list from dual;
select count(*) into cnt from fem_table_class_assignmt
where table_name = p_table_name
and table_classification_code = 'FTP_OPTION_COST';
select dimension_id into main_dim_id from fem_dimensions_b
where dimension_varchar_label = 'LINE_ITEM';
select attribute_id into ACCT_TYPE_ATTR_ID from fem_dim_attributes_b
where attribute_varchar_label = 'EXTENDED_ACCOUNT_TYPE'
and dimension_id = main_dim_id;
select dimension_id into ext_account_type_id from fem_dimensions_b
where dimension_varchar_label = EXT_ACCOUNT_TYPE_NAME;
select attribute_id into asset_flag_id from fem_dim_attributes_b
where dimension_id = ext_account_type_id
and attribute_varchar_label = ASSET_FLAG_NAME;
select attribute_id into off_bal_flag_id from fem_dim_attributes_b
where dimension_id = ext_account_type_id
and attribute_varchar_label = OFF_BAL_NAME;