The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_last_update_date in varchar2,
p_custom_mode in varchar2
) is
Begin
Update_row(
p_definition_name ,
p_dimension_code ,
p_collect_flag ,
p_collect_level_id ,
p_aggregation_type ,
p_allocation_type ,
p_owner ,
p_last_update_date ,
p_custom_mode );
Insert_row(
p_definition_name ,
p_dimension_code ,
p_collect_flag ,
p_collect_level_id ,
p_aggregation_type ,
p_allocation_type ,
p_owner ,
p_last_update_date );
Procedure Update_row (
p_definition_name in varchar2,
p_dimension_code in varchar2,
p_collect_flag in varchar2,
p_collect_level_id in number,
p_aggregation_type in varchar2,
p_allocation_type in varchar2,
p_owner in varchar2,
p_last_update_date in varchar2,
p_custom_mode in varchar2
) is
l_user number;
f_ludate date; -- entity update date in file
db_ludate date; -- entity update date in db
select cs_definition_id from msd_cs_definitions
where name = p_definition_name;
select level_id from msd_levels
where level_name = p_collect_level_name;
select last_updated_by, last_update_date
from msd_cs_defn_dim_dtls
where cs_definition_id = p_cs_def_id
and dimension_code = p_dim_code;
f_ludate := nvl(to_date(p_last_update_date, 'YYYY/MM/DD'), sysdate);
update MSD_CS_DEFN_DIM_DTLS set
collect_flag = p_collect_flag,
collect_level_id = l_level_id,
aggregation_type = p_aggregation_type,
allocation_type = p_allocation_type,
last_updated_by = l_user,
last_update_date = f_ludate
where
cs_definition_id = l_cs_definition_id and
dimension_code = p_dimension_code;
Procedure Insert_row (
p_definition_name in varchar2,
p_dimension_code in varchar2,
p_collect_flag in varchar2,
p_collect_level_id in number ,
p_aggregation_type in varchar2,
p_allocation_type in varchar2,
p_owner in varchar2,
p_last_update_date in varchar2
) is
l_user number;
f_ludate date; -- entity update date in file
select cs_definition_id
from msd_cs_definitions
where
name = p_definition_name;
select level_id
from msd_levels
where
level_name = p_collect_level_name;
f_ludate := nvl(to_date(p_last_update_date, 'YYYY/MM/DD'), sysdate);
select MSD_CS_DEFN_DIM_DTLS_S.nextval into l_pk_id from dual;
insert into MSD_CS_DEFN_DIM_DTLS(
cs_defn_dim_dtls_id,
cs_definition_id,
dimension_code,
collect_flag,
collect_level_id,
aggregation_type,
allocation_type,
created_by,
creation_date,
last_updated_by,
last_update_date ,
last_update_login
)
values
(
l_pk_id,
l_cs_definition_id,
p_dimension_code,
p_collect_flag,
l_level_id,
p_aggregation_type,
p_allocation_type,
l_user,
f_ludate,
l_user,
f_ludate,
fnd_global.login_id
);