[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
function insertDimensionRecord(p_dimension_rec in zpb_md_records.DIMENSIONS_ENTRY,
p_primary_key_provided in boolean default false)
return number is
l_dimension_rec zpb_md_records.dimensions_entry;
insert_flag boolean;
insert_flag := p_primary_key_provided;
if insert_flag = false then
begin
select dimension_id into l_dimension_rec.DimensionId
from zpb_dimensions
where bus_area_id = bus_area_id_num and
epb_id = l_dimension_rec.EpbId;
update zpb_dimensions
set
AW_NAME = l_dimension_rec.AwName,
DEFAULT_HIER = l_dimension_rec.DefaultHier,
DEFAULT_MEMBER = l_dimension_rec.DefaultMember,
DIM_CODE = l_dimension_rec.DimCode,
DIM_TYPE = l_dimension_rec.DimType,
IS_CURRENCY_DIM = l_dimension_rec.IsCurrencyDim,
IS_DATA_DIM = l_dimension_rec.IsDataDim,
IS_OWNER_DIM = l_dimension_rec.IsOwnerDim,
PERS_CWM_NAME = l_dimension_rec.PersCWMName,
PERS_TABLE_ID = l_dimension_rec.PersTableId,
SHAR_CWM_NAME = l_dimension_rec.SharCWMName,
SHAR_TABLE_ID = l_dimension_rec.SharTableId,
ANNOTATION_DIM = l_dimension_rec.AnnotationDim,
CREATED_BY = FND_GLOBAL.USER_ID,
CREATION_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
where dimension_id = l_dimension_rec.DimensionId;
SELECT zpb_dimensions_seq.NEXTVAL INTO l_dimension_rec.DimensionId FROM DUAL;
insert_flag := true;
if insert_flag=true then
insert into zpb_dimensions
(
AW_NAME,
BUS_AREA_ID,
DEFAULT_HIER,
DEFAULT_MEMBER,
DIMENSION_ID,
DIM_CODE,
DIM_TYPE,
EPB_ID,
IS_CURRENCY_DIM,
IS_DATA_DIM,
IS_OWNER_DIM,
PERS_CWM_NAME,
PERS_TABLE_ID,
SHAR_CWM_NAME,
SHAR_TABLE_ID,
ANNOTATION_DIM,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES
(
l_dimension_rec.AwName,
bus_area_id_num,
l_dimension_rec.DefaultHier,
l_dimension_rec.DefaultMember,
l_dimension_rec.DimensionId,
l_dimension_rec.DimCode,
l_dimension_rec.DimType,
l_dimension_rec.EpbId,
l_dimension_rec.IsCurrencyDim,
l_dimension_rec.IsDataDim,
l_dimension_rec.IsOwnerDim,
l_dimension_rec.PersCWMName,
l_dimension_rec.PersTableId,
l_dimension_rec.SharCWMName,
l_dimension_rec.SharTableId,
l_dimension_rec.AnnotationDim,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.LOGIN_ID
);
end insertDimensionRecord;
function insertTableRecord(p_table_rec in zpb_md_records.TABLES_ENTRY,
p_primary_key_provided in boolean default false)
return number is
l_table_rec zpb_md_records.tables_entry;
insert_flag boolean;
insert_flag := p_primary_key_provided;
select table_id into l_table_rec.TableId
from zpb_tables
where bus_area_id = bus_area_id_num and
table_name = l_table_rec.TableName;
update zpb_tables
set
AW_NAME = l_table_rec.AwName,
TABLE_TYPE = l_table_rec.TableType,
CREATED_BY = FND_GLOBAL.USER_ID,
CREATION_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
where table_id = l_table_rec.TableId;
SELECT zpb_tables_seq.NEXTVAL INTO l_table_rec.TableId FROM DUAL;
insert_flag := true;
if insert_flag = true then
insert into zpb_tables
(
AW_NAME,
BUS_AREA_ID,
TABLE_ID,
TABLE_NAME,
TABLE_TYPE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES
(
l_table_rec.AwName,
bus_area_id_num,
l_table_rec.TableId,
l_table_rec.TableName,
l_table_rec.TableType,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.LOGIN_ID
);
end insertTableRecord;
function insertColumnRecord(p_col_rec in zpb_md_records.columns_entry)
return number is
l_col_rec zpb_md_records.columns_entry;
select column_id into l_col_rec.ColumnId
from zpb_columns
where table_id = l_col_rec.TableId and
column_name = l_col_rec.ColumnName;
update zpb_columns
set
AW_NAME = l_col_rec.AwName,
COLUMN_NAME = l_col_rec.ColumnName,
COLUMN_TYPE = l_col_rec.ColumnType,
TABLE_ID = l_col_rec.TableId,
CREATED_BY = FND_GLOBAL.USER_ID,
CREATION_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
where column_id = l_col_rec.ColumnId;
SELECT zpb_columns_seq.NEXTVAL INTO l_col_rec.ColumnId FROM DUAL;
insert into zpb_columns
(
COLUMN_ID,
AW_NAME,
COLUMN_NAME,
COLUMN_TYPE,
TABLE_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES
(
l_col_rec.ColumnId,
l_col_rec.AwName,
l_col_rec.ColumnName,
l_col_rec.ColumnType,
l_col_rec.TableId,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.LOGIN_ID
);
end insertColumnRecord;
function insertLevelRecord(p_level_rec zpb_md_records.levels_entry)
return number is
l_level_rec zpb_md_records.levels_entry;
select level_id into l_level_rec.LevelId
from zpb_levels
where dimension_id = l_level_rec.DimensionId and
pers_cwm_name = l_level_rec.PersCWMName;
update zpb_levels
set
PERS_CWM_NAME = l_level_rec.PersCWMName,
DIMENSION_ID = l_level_rec.DimensionId,
EPB_ID = l_level_rec.EpbId,
SHAR_CWM_NAME = l_level_rec.SharCWMName,
PERS_LEVEL_FLAG = l_level_rec.PersLevelFlag,
CREATED_BY = FND_GLOBAL.USER_ID,
CREATION_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
where level_id = l_level_rec.LevelId;
SELECT zpb_levels_seq.NEXTVAL INTO l_level_rec.LevelId FROM DUAL;
insert into zpb_levels
(
PERS_CWM_NAME,
DIMENSION_ID,
EPB_ID,
LEVEL_ID,
SHAR_CWM_NAME,
PERS_LEVEL_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES
(
l_level_rec.PersCWMName,
l_level_rec.DimensionId,
l_level_rec.EpbId,
l_level_rec.LevelId,
l_level_rec.SharCWMName,
l_level_rec.PersLevelFlag,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.LOGIN_ID
);
end insertLevelRecord;
function insertHierLevelRecord(p_hier_level_rec zpb_md_records.hier_level_entry)
return number is
l_hier_level_rec zpb_md_records.hier_level_entry;
select relation_id into l_hier_level_rec.RelationId
from zpb_hier_level
where level_id = l_hier_level_rec.LevelId and
hier_id = l_hier_level_rec.HierId;
update zpb_hier_level
set
LEVEL_ORDER = l_hier_level_rec.LevelOrder,
PERS_COL_ID = l_hier_level_rec.PersColId,
SHAR_COL_ID = l_hier_level_rec.SharColId,
CREATED_BY = FND_GLOBAL.USER_ID,
CREATION_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
where relation_id = l_hier_level_rec.RelationId;
SELECT zpb_hier_level_seq.NEXTVAL INTO l_hier_level_rec.RelationId FROM DUAL;
insert into zpb_hier_level
(
HIER_ID,
LEVEL_ID,
LEVEL_ORDER,
PERS_COL_ID,
RELATION_ID,
SHAR_COL_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES
(
l_hier_level_rec.HierId,
l_hier_level_rec.LevelId,
l_hier_level_rec.LevelOrder,
l_hier_level_rec.PersColId,
l_hier_level_rec.RelationId,
l_hier_level_rec.SharColId,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.LOGIN_ID
);
end insertHierLevelRecord;
function insertAttributeRecord(p_attr_rec zpb_md_records.attributes_entry)
return number is
l_attr_rec zpb_md_records.attributes_entry;
select attribute_id into l_attr_rec.AttributeId
from zpb_attributes
where dimension_id = l_attr_rec.DimensionId and
pers_cwm_name = l_attr_rec.PersCWMName;
update zpb_attributes
set
DIMENSION_ID = l_attr_rec.DimensionId,
EPB_ID = l_attr_rec.EpbId,
LABEL = l_attr_rec.Label,
RANGE_DIM_ID = l_attr_rec.RangeDimId,
SHAR_CWM_NAME = l_attr_rec.SharCWMName,
TYPE = l_attr_rec.Type,
PERS_CWM_NAME = l_attr_rec.PersCWMName,
FEM_ATTRIBUTE_ID = l_attr_rec.FEMAttrId,
CREATED_BY = FND_GLOBAL.USER_ID,
CREATION_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
where attribute_id = l_attr_rec.AttributeId;
SELECT zpb_attributes_seq.NEXTVAL INTO l_attr_rec.AttributeId FROM DUAL;
insert into zpb_attributes
(
ATTRIBUTE_ID,
DIMENSION_ID,
EPB_ID,
LABEL,
RANGE_DIM_ID,
SHAR_CWM_NAME,
TYPE,
PERS_CWM_NAME,
FEM_ATTRIBUTE_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES
(
l_attr_rec.AttributeId,
l_attr_rec.DimensionId,
l_attr_rec.EpbId,
l_attr_rec.Label,
l_attr_rec.RangeDimId,
l_attr_rec.SharCWMName,
l_attr_rec.Type,
l_attr_rec.PersCWMName,
l_attr_rec.FEMAttrId,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.LOGIN_ID
);
end insertAttributeRecord;
function insertAttrTableColRecord(p_attr_table_col_rec zpb_md_records.attr_table_col_entry)
return number is
l_attr_table_col_rec zpb_md_records.attr_table_col_entry;
select relation_id into l_attr_table_col_rec.RelationId
from zpb_attr_table_col
where attribute_id = l_attr_table_col_rec.AttributeId and
table_id = l_attr_table_col_rec.TableId and
column_id = l_attr_table_col_rec.ColumnId;
update zpb_attr_table_col
set
CREATED_BY = FND_GLOBAL.USER_ID,
CREATION_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
where relation_id = l_attr_table_col_rec.RelationId;
SELECT zpb_attr_table_col_seq.NEXTVAL INTO l_attr_table_col_rec.RelationId FROM DUAL;
insert into zpb_attr_table_col
(
ATTRIBUTE_ID,
COLUMN_ID,
RELATION_ID,
TABLE_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES
(
l_attr_table_col_rec.AttributeId,
l_attr_table_col_rec.ColumnId,
l_attr_table_col_rec.RelationId,
l_attr_table_col_rec.TableId,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.LOGIN_ID
);
end insertAttrTableColRecord;
function insertCubeDimsRecord(p_cube_dims_rec zpb_md_records.cube_dims_entry)
return number is
l_cube_dims_rec zpb_md_records.cube_dims_entry;
select relation_id into l_cube_dims_rec.RelationId
from zpb_cube_dims
where cube_id = l_cube_dims_rec.CubeId and
dimension_id = l_cube_dims_rec.DimensionId;
update zpb_cube_dims
set
COLUMN_ID = l_cube_dims_rec.ColumnId,
CREATED_BY = FND_GLOBAL.USER_ID,
CREATION_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
where relation_id = l_cube_dims_rec.RelationId;
SELECT zpb_cube_dims_seq.NEXTVAL INTO l_cube_dims_rec.RelationId FROM DUAL;
insert into zpb_cube_dims
(
COLUMN_ID,
CUBE_ID,
DIMENSION_ID,
RELATION_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES
(
l_cube_dims_rec.ColumnId,
l_cube_dims_rec.CubeId,
l_cube_dims_rec.DimensionId,
l_cube_dims_rec.RelationId,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.LOGIN_ID
);
end insertCubeDimsRecord;
function insertCubeHierRecord(p_cube_hier_rec zpb_md_records.cube_hier_entry)
return number is
l_cube_hier_rec zpb_md_records.cube_hier_entry;
select relation_id into l_cube_hier_rec.RelationId
from zpb_cube_hier
where cube_id = l_cube_hier_rec.CubeId and
hierarchy_id = l_cube_hier_rec.HierarchyId;
update zpb_cube_hier
set
COLUMN_ID = l_cube_hier_rec.ColumnId,
CREATED_BY = FND_GLOBAL.USER_ID,
CREATION_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
where relation_id = l_cube_hier_rec.RelationId;
SELECT zpb_cube_hier_seq.NEXTVAL INTO l_cube_hier_rec.RelationId FROM DUAL;
insert into zpb_cube_hier
(
COLUMN_ID,
CUBE_ID,
HIERARCHY_ID,
RELATION_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES
(
l_cube_hier_rec.ColumnId,
l_cube_hier_rec.CubeId,
l_cube_hier_rec.HierarchyId,
l_cube_hier_rec.RelationId,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.LOGIN_ID
);
end insertCubeHierRecord;
function insertMeasureRecord(p_measure_rec zpb_md_records.measures_entry)
return number is
l_measure_rec zpb_md_records.measures_entry;
select measure_id into l_measure_rec.MeasureId
from zpb_measures
where cube_id = l_measure_rec.CubeId and
cwm_name = l_measure_rec.CWMName;
update zpb_measures
set
AW_NAME = l_measure_rec.AwName,
COLUMN_ID = l_measure_rec.ColumnId,
CURRENCY_TYPE = l_measure_rec.CurrencyType,
CURR_INST_FLAG = l_measure_rec.CurrInstFlag,
EPB_ID = l_measure_rec.EpbId,
INSTANCE_ID = l_measure_rec.InstanceId,
TEMPLATE_ID = l_measure_rec.TemplateId,
APPROVEE_ID = l_measure_rec.ApproveeId,
TYPE = l_measure_rec.Type,
SELECTED_CUR = l_measure_rec.SelectedCur,
NAME = l_measure_rec.Name,
CURRENCY_REL = l_measure_rec.CurrencyRel,
CPR_MEASURE = l_measure_rec.CPRMeasure,
CREATED_BY = FND_GLOBAL.USER_ID,
CREATION_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
where measure_id = l_measure_rec.MeasureId;
SELECT zpb_measures_seq.NEXTVAL INTO l_measure_rec.MeasureId FROM DUAL;
insert into zpb_measures
(
AW_NAME,
COLUMN_ID,
CUBE_ID,
CURRENCY_TYPE,
CURR_INST_FLAG,
CWM_NAME,
EPB_ID,
INSTANCE_ID,
MEASURE_ID,
TEMPLATE_ID,
APPROVEE_ID,
TYPE,
SELECTED_CUR,
NAME,
CURRENCY_REL,
CPR_MEASURE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES
(
l_measure_rec.AwName,
l_measure_rec.ColumnId,
l_measure_rec.CubeId,
l_measure_rec.CurrencyType,
l_measure_rec.CurrInstFlag,
l_measure_rec.CwmName,
l_measure_rec.EpbId,
l_measure_rec.InstanceId,
l_measure_rec.MeasureId,
l_measure_rec.TemplateId,
l_measure_rec.ApproveeId,
l_measure_rec.Type,
l_measure_rec.SelectedCur,
l_measure_rec.Name,
l_measure_rec.CurrencyRel,
l_measure_rec.CPRMeasure,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.LOGIN_ID
);
end insertMeasureRecord;
procedure insertAttributesTLRecord(p_attributes_tl_rec zpb_md_records.attributes_tl_entry)
is
l_attributes_tl_rec zpb_md_records.attributes_tl_entry;
delete zpb_attributes_tl
where attribute_id = l_attributes_tl_rec.AttributeId and
language = l_attributes_tl_rec.Language;
insert into zpb_attributes_tl
(
ATTRIBUTE_ID,
LANGUAGE,
LONG_NAME,
NAME,
PLURAL_LONG_NAME,
PLURAL_NAME,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES
(
l_attributes_tl_rec.AttributeId,
l_attributes_tl_rec.Language,
l_attributes_tl_rec.LongName,
l_attributes_tl_rec.Name,
l_attributes_tl_rec.PluralLongName,
l_attributes_tl_rec.PluralName,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.LOGIN_ID
);
end insertAttributesTLRecord;
procedure insertDimensionsTLRecord(p_dimensions_tl_rec zpb_md_records.dimensions_tl_entry)
is
l_dimensions_tl_rec zpb_md_records.dimensions_tl_entry;
delete zpb_dimensions_tl
where dimension_id = l_dimensions_tl_rec.DimensionId and
language = l_dimensions_tl_rec.Language;
insert into zpb_dimensions_tl
(
DIMENSION_ID,
LANGUAGE,
LONG_NAME,
NAME,
PLURAL_LONG_NAME,
PLURAL_NAME,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES
(
l_dimensions_tl_rec.DimensionId,
l_dimensions_tl_rec.Language,
l_dimensions_tl_rec.LongName,
l_dimensions_tl_rec.Name,
l_dimensions_tl_rec.PluralLongName,
l_dimensions_tl_rec.PluralName,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.LOGIN_ID
);
end insertDimensionsTLRecord;
function insertHierarchyRecord(p_hierarchies_rec in zpb_md_records.hierarchies_entry)
return number is
l_hierarchies_rec zpb_md_records.hierarchies_entry;
select hierarchy_id into l_hierarchies_rec.HierarchyId
from zpb_hierarchies
where dimension_id = l_hierarchies_rec.DimensionId and
pers_cwm_name = l_hierarchies_rec.PersCWMName;
update zpb_hierarchies
set
EPB_ID = l_hierarchies_rec.EpbId,
HIER_TYPE = l_hierarchies_rec.HierType,
PERS_TABLE_ID = l_hierarchies_rec.PersTableId,
SHAR_CWM_NAME = l_hierarchies_rec.SharCWMName,
SHAR_TABLE_ID = l_hierarchies_rec.SharTableId,
CREATED_BY = FND_GLOBAL.USER_ID,
CREATION_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
where hierarchy_id = l_hierarchies_rec.HierarchyId;
SELECT zpb_hierarchies_seq.NEXTVAL INTO l_hierarchies_rec.HierarchyId FROM DUAL;
insert into zpb_hierarchies
(
DIMENSION_ID,
EPB_ID,
HIERARCHY_ID,
HIER_TYPE,
PERS_CWM_NAME,
PERS_TABLE_ID,
SHAR_CWM_NAME,
SHAR_TABLE_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES
(
l_hierarchies_rec.DimensionId,
l_hierarchies_rec.EpbId,
l_hierarchies_rec.HierarchyId,
l_hierarchies_rec.HierType,
l_hierarchies_rec.PersCWMName,
l_hierarchies_rec.PersTableId,
l_hierarchies_rec.SharCWMName,
l_hierarchies_rec.SharTableId,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.LOGIN_ID
);
end insertHierarchyRecord;
procedure insertHierarchyTLRecord(p_hierarchies_tl_rec in zpb_md_records.hierarchies_tl_entry)
is
l_hierarchies_tl_rec zpb_md_records.hierarchies_tl_entry;
select count(*) into l_record_count
from zpb_hierarchies_tl
where hierarchy_id = l_hierarchies_tl_rec.HierarchyId and
language = l_hierarchies_tl_rec.Language;
insert into zpb_hierarchies_tl
(
HIERARCHY_ID,
LANGUAGE,
LONG_NAME,
NAME,
PLURAL_LONG_NAME,
PLURAL_NAME,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES
(
l_hierarchies_tl_rec.HierarchyId,
l_hierarchies_tl_rec.Language,
l_hierarchies_tl_rec.LongName,
l_hierarchies_tl_rec.Name,
l_hierarchies_tl_rec.PluralLongName,
l_hierarchies_tl_rec.PluralName,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.LOGIN_ID
);
update zpb_hierarchies_tl
set
LONG_NAME = l_hierarchies_tl_rec.LongName,
NAME = l_hierarchies_tl_rec.Name,
PLURAL_LONG_NAME = l_hierarchies_tl_rec.PluralLongName,
PLURAL_NAME = l_hierarchies_tl_rec.PluralName,
CREATED_BY = FND_GLOBAL.USER_ID,
CREATION_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
where hierarchy_id = l_hierarchies_tl_rec.HierarchyId and
language = l_hierarchies_tl_rec.Language;
end insertHierarchyTLRecord;
procedure insertLevelTLRecord(p_levels_tl_rec in zpb_md_records.levels_tl_entry)
is
l_levels_tl_rec zpb_md_records.levels_tl_entry;
select count(*) into l_record_count
from zpb_levels_tl
where level_id = l_levels_tl_rec.LevelId and
language = l_levels_tl_rec.Language;
update zpb_levels_tl
set
LONG_NAME = l_levels_tl_rec.LongName,
NAME = l_levels_tl_rec.Name,
PLURAL_LONG_NAME = l_levels_tl_rec.PluralLongName,
PLURAL_NAME = l_levels_tl_rec.PluralName,
CREATED_BY = FND_GLOBAL.USER_ID,
CREATION_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
where level_id = l_levels_tl_rec.LevelId and
language = l_levels_tl_rec.Language;
insert into zpb_levels_tl
(
LEVEL_ID,
LANGUAGE,
LONG_NAME,
NAME,
PLURAL_LONG_NAME,
PLURAL_NAME,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES
(
l_levels_tl_rec.LevelId,
l_levels_tl_rec.Language,
l_levels_tl_rec.LongName,
l_levels_tl_rec.Name,
l_levels_tl_rec.PluralLongName,
l_levels_tl_rec.PluralName,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.LOGIN_ID
);
end insertLevelTLRecord;
procedure deleteCubeRecord(p_cube_id in number) is
begin
-- zpb_log.write_error('zpb_metadata_pkg.deleteCubeRecord',
-- 'Deleteing Cube '||p_cube_id);
delete zpb_measures
where cube_id = p_cube_id;
delete zpb_columns
where table_id=(select table_id
from zpb_cubes
where cube_id = p_cube_id);
delete zpb_tables
where table_id = (select table_id
from zpb_cubes
where cube_id = p_cube_id);
delete zpb_cube_dims
where cube_id = p_cube_id;
delete zpb_cube_hier
where cube_id = p_cube_id;
delete zpb_cubes
where cube_id = p_cube_id;
end deleteCubeRecord;
function insertCubeRecord(p_cube_rec in zpb_md_records.CUBES_ENTRY,
p_primary_key_provided in boolean default false)
return number is
l_cube_rec zpb_md_records.cubes_entry;
insert_flag boolean;
insert_flag := p_primary_key_provided;
if insert_flag = false then
begin
select cube_id into l_cube_rec.CubeId
from zpb_cubes
where name = l_cube_rec.Name and
bus_area_id = bus_area_id_num;
update zpb_cubes
set
EPB_ID = l_cube_rec.EpbId,
TABLE_ID = l_cube_rec.TableId,
TYPE = l_cube_rec.Type,
CREATED_BY = FND_GLOBAL.USER_ID,
CREATION_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
where cube_id = l_cube_rec.CubeId;
SELECT zpb_cubes_seq.NEXTVAL INTO l_cube_rec.CubeId FROM DUAL;
insert_flag:=true;
if insert_flag = true then
insert into zpb_cubes
(
BUS_AREA_ID,
CUBE_ID,
EPB_ID,
NAME,
TABLE_ID,
TYPE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES
(
-- l_cube_rec.BusAreaId,
bus_area_id_num,
l_cube_rec.CubeId,
l_cube_rec.EpbId,
l_cube_rec.Name,
l_cube_rec.TableId,
l_cube_rec.Type,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.LOGIN_ID
);
end insertCubeRecord;
function insertHierScopeRecord(p_hier_scope_rec zpb_md_records.hier_scope_entry)
return number is
l_hier_scope_rec zpb_md_records.hier_scope_entry;
select scope_id into l_hier_scope_rec.ScopeId
from zpb_hier_scope
where hierarchy_id = l_hier_scope_rec.HierarchyId and
user_id = l_hier_scope_rec.UserId and
resp_id = nvl(to_number(sys_context('ZPB_CONTEXT', 'resp_id')), FND_GLOBAL.RESP_ID);
update zpb_hier_scope
set
PERS_TABLE_ID = l_hier_scope_rec.PersTableId,
CREATED_BY = FND_GLOBAL.USER_ID,
CREATION_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
where scope_id = l_hier_scope_rec.ScopeId;
SELECT zpb_hier_scope_seq.NEXTVAL INTO l_hier_scope_rec.ScopeId FROM DUAL;
insert into zpb_hier_scope
(
END_DATE,
HIERARCHY_ID,
SCOPE_ID,
START_DATE,
USER_ID,
RESP_ID,
PERS_TABLE_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES
(
l_hier_scope_rec.EndDate,
l_hier_scope_rec.HierarchyId,
l_hier_scope_rec.ScopeId,
l_hier_scope_rec.StartDate,
l_hier_scope_rec.UserId,
nvl(to_number(sys_context('ZPB_CONTEXT', 'resp_id')), FND_GLOBAL.RESP_ID),
l_hier_scope_rec.PersTableId,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.LOGIN_ID
);
end insertHierScopeRecord;
function insertHierlevelscopeRecord(p_hier_level_scope_rec zpb_md_records.hier_level_scope_entry)
return number is
l_hier_level_scope_rec zpb_md_records.hier_level_scope_entry;
select scope_id into l_hier_level_scope_rec.ScopeId
from zpb_hier_level_scope
where hier_id = l_hier_level_scope_rec.HierId and
level_id = l_hier_level_scope_rec.LevelId and
user_id = l_hier_level_scope_rec.UserId and
resp_id = nvl(to_number(sys_context('ZPB_CONTEXT', 'resp_id')), FND_GLOBAL.RESP_ID);
update zpb_hier_level_scope
set
PERS_COL_ID = l_hier_level_scope_rec.PersColId,
CREATED_BY = FND_GLOBAL.USER_ID,
CREATION_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
where scope_id = l_hier_level_scope_rec.ScopeId;
SELECT zpb_hier_level_scope_seq.NEXTVAL INTO l_hier_level_scope_rec.ScopeId FROM DUAL;
insert into zpb_hier_level_scope
(
LEVEL_ID,
HIER_ID,
SCOPE_ID,
USER_ID,
RESP_ID,
PERS_COL_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES
(
l_hier_level_scope_rec.LevelId,
l_hier_level_scope_rec.HierId,
l_hier_level_scope_rec.ScopeId,
l_hier_level_scope_rec.UserId,
nvl(to_number(sys_context('ZPB_CONTEXT', 'resp_id')), FND_GLOBAL.RESP_ID),
l_hier_level_scope_rec.PersColId,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.LOGIN_ID
);
end insertHierlevelscopeRecord;
function insertAttributescopeRecord(p_attribute_scope_rec zpb_md_records.attribute_scope_entry)
return number is
l_attribute_scope_rec zpb_md_records.attribute_scope_entry;
select scope_id into l_attribute_scope_rec.ScopeId
from zpb_attribute_scope
where attribute_id = l_attribute_scope_rec.AttributeId and
user_id = l_attribute_scope_rec.UserId and
resp_id = nvl(to_number(sys_context('ZPB_CONTEXT', 'resp_id')), FND_GLOBAL.RESP_ID);
update zpb_attribute_scope
set
CREATED_BY = FND_GLOBAL.USER_ID,
CREATION_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
where scope_id = l_attribute_scope_rec.ScopeId;
SELECT zpb_attribute_scope_seq.NEXTVAL INTO l_attribute_scope_rec.ScopeId FROM DUAL;
insert into zpb_attribute_scope
(
SCOPE_ID,
ATTRIBUTE_ID,
USER_ID,
RESP_ID,
END_DATE,
START_DATE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES
(
l_attribute_scope_rec.ScopeId,
l_attribute_scope_rec.AttributeId,
l_attribute_scope_rec.UserId,
nvl(to_number(sys_context('ZPB_CONTEXT', 'resp_id')), FND_GLOBAL.RESP_ID),
l_attribute_scope_rec.EndDate,
l_attribute_scope_rec.StartDate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.LOGIN_ID
);
end insertAttributescopeRecord;
procedure deleteDimensionRecord(p_dimension_id in number) is
begin
-- delete dimensions_tl
delete zpb_dimensions_tl
where dimension_id = p_dimension_id;
delete zpb_attribute_scope
where attribute_id in (select attribute_id
from zpb_attributes
where dimension_id = p_dimension_id);
delete zpb_attr_table_col
where attribute_id in (select attribute_id
from zpb_attributes
where dimension_id = p_dimension_id);
delete zpb_attributes_tl
where attribute_id in (select attribute_id
from zpb_attributes
where dimension_id = p_dimension_id);
delete zpb_attributes
where dimension_id = p_dimension_id;
delete zpb_hierarchies_tl
where hierarchy_id in (select hierarchy_id
from zpb_hierarchies
where dimension_id = p_dimension_id);
delete zpb_hier_level
where hier_id in (select hierarchy_id
from zpb_hierarchies
where dimension_id = p_dimension_id);
delete zpb_hier_level_scope
where hier_id in (select hierarchy_id
from zpb_hierarchies
where dimension_id = p_dimension_id);
delete zpb_hier_scope
where hierarchy_id in (select hierarchy_id
from zpb_hierarchies
where dimension_id = p_dimension_id);
delete zpb_tables
where table_id in (select pers_table_id from zpb_hierarchies
where dimension_id = p_dimension_id);
delete zpb_columns
where table_id in (select pers_table_id from zpb_hierarchies
where dimension_id = p_dimension_id);
delete zpb_tables
where table_id in (select shar_table_id from zpb_hierarchies
where dimension_id = p_dimension_id);
delete zpb_columns
where table_id in (select shar_table_id from zpb_hierarchies
where dimension_id = p_dimension_id);
delete zpb_hierarchies
where dimension_id = p_dimension_id;
delete zpb_levels_tl
where level_id in (select level_id
from zpb_levels
where dimension_id = p_dimension_id);
delete zpb_levels
where dimension_id = p_dimension_id;
delete zpb_tables where table_id = (select pers_table_id from zpb_dimensions where dimension_id = p_dimension_id);
delete zpb_columns where table_id = (select pers_table_id from zpb_dimensions where dimension_id = p_dimension_id);
delete zpb_tables where table_id = (select shar_table_id from zpb_dimensions where dimension_id = p_dimension_id);
delete zpb_columns where table_id = (select shar_table_id from zpb_dimensions where dimension_id = p_dimension_id);
delete zpb_dimensions
where dimension_id = p_dimension_id;
end deleteDimensionRecord;
procedure deleteAttributeRecord(p_attribute_id in number) is
begin
-- delete attribute_scope
delete zpb_attribute_scope
where attribute_id = p_attribute_id;
delete zpb_attr_table_col
where attribute_id = p_attribute_id;
delete zpb_attributes_tl
where attribute_id = p_attribute_id;
delete zpb_attributes
where attribute_id = p_attribute_id;
end deleteAttributeRecord;
procedure deleteLevelRecord(p_level_id in number) is
begin
-- delete levels_tl
delete zpb_levels_tl
where level_id = p_level_id;
delete zpb_levels
where level_id = p_level_id;
end deleteLevelRecord;
procedure deleteHierarchyRecord(p_hierarchy_id in number) is
begin
-- delete hierarchies_tl
delete zpb_hierarchies_tl
where hierarchy_id = p_hierarchy_id;
delete zpb_hier_level
where hier_id = p_hierarchy_id;
delete zpb_hier_level_scope
where hier_id = p_hierarchy_id;
delete zpb_hier_scope
where hierarchy_id = p_hierarchy_id;
delete zpb_tables where table_id = (select pers_table_id from zpb_hierarchies where hierarchy_id = p_hierarchy_id);
delete zpb_columns where table_id = (select pers_table_id from zpb_hierarchies where hierarchy_id = p_hierarchy_id);
delete zpb_tables where table_id = (select shar_table_id from zpb_hierarchies where hierarchy_id = p_hierarchy_id);
delete zpb_columns where table_id = (select shar_table_id from zpb_hierarchies where hierarchy_id = p_hierarchy_id);
delete zpb_hierarchies
where hierarchy_id = p_hierarchy_id;
end deleteHierarchyRecord;
select dimension_id
from zpb_dimensions
where bus_area_id = bus_area_id_num and
last_update_date < p_start_time;
select attribute_id
from zpb_attributes
where last_update_date < p_start_time and
dimension_id in (select dimension_id
from zpb_dimensions
where bus_area_id = bus_area_id_num);
select hierarchy_id
from zpb_hierarchies
where last_update_date < p_start_time and
dimension_id in (select dimension_id
from zpb_dimensions
where bus_area_id = bus_area_id_num);
select level_id
from zpb_levels
where last_update_date < p_start_time and
dimension_id in (select dimension_id
from zpb_dimensions
where bus_area_id = bus_area_id_num);
deleteDimensionRecord(v_dim.dimension_id);
deleteAttributeRecord(v_attribute.attribute_id);
deleteHierarchyRecord(v_hierarchy.hierarchy_id);
deleteLevelRecord(v_level.level_id);
delete zpb_attr_table_col
where last_update_date < p_start_time and
table_id in (select table_id
from zpb_tables
where bus_area_id = bus_area_id_num);
delete zpb_hier_level
where last_update_date < p_start_time
and hier_id in (select hier.hierarchy_id
from zpb_hierarchies hier,
zpb_dimensions dim
where hier.dimension_id = dim.dimension_id and
dim.bus_area_id = bus_area_id_num);
select dimension_id into m_dimension_en.DimensionId
from zpb_dimensions
where bus_area_id = bus_area_id_num and
epb_id = m_dimension_en.EPBId;
SELECT zpb_dimensions_seq.NEXTVAL INTO m_dimension_en.DimensionId FROM DUAL;
m_dimension_en.SharTableId := insertTableRecord(m_shar_table_en);
m_dimension_en.PersTableId := insertTableRecord(m_pers_table_en);
m_column_id := insertColumnRecord(m_pers_column_en);
m_column_id := insertColumnRecord(m_shar_column_en);
m_column_id := insertColumnRecord(m_pers_column_en);
m_column_id := insertColumnRecord(m_shar_column_en);
m_column_id := insertColumnRecord(m_pers_column_en);
m_column_id := insertColumnRecord(m_shar_column_en);
m_column_id := insertColumnRecord(m_pers_column_en);
m_column_id := insertColumnRecord(m_shar_column_en);
m_column_id :=insertColumnRecord(m_pers_column_en);
m_column_id :=insertColumnRecord(m_shar_column_en);
select sysdate into l_pp_hiert_start_time from dual;
select table_id into m_hr_shar_table_en.tableId
from zpb_tables
where table_id = (select shar_table_id
from zpb_hierarchies
where dimension_id = m_dimension_en.DimensionId and
pers_cwm_name = m_hier_en.PersCWMName);
SELECT zpb_tables_seq.NEXTVAL INTO m_hr_shar_table_en.tableId FROM DUAL;
select table_id into m_hr_pers_table_en.tableId
from zpb_tables
where table_name = m_hr_pers_table_en.TableName and
bus_area_id = bus_area_id_num;
select table_id into m_hr_pers_table_en.tableId
from zpb_tables
where table_id = (select pers_table_id
from zpb_hierarchies
where dimension_id = m_dimension_en.DimensionId and
pers_cwm_name = m_hier_en.PersCWMName);
SELECT zpb_tables_seq.NEXTVAL INTO m_hr_pers_table_en.tableId FROM DUAL;
select hierarchy_id into m_hierarchy_id
from zpb_hierarchies
where dimension_id = m_dimension_en.DimensionId and
pers_cwm_name = m_hier_en.PersCWMName;
m_hierarchy_id := insertHierarchyRecord(m_hier_en);
insertHierarchyTLRecord(m_hier_tl_en);
m_hier_level_en.SharColId := insertColumnRecord(m_shar_column_en);
m_hier_level_en.PersColId := insertColumnRecord(m_pers_column_en);
m_level_id := insertLevelRecord(m_level_en);
insertLevelTLRecord(m_level_tl_en);
m_hier_level_id := insertHierLevelRecord(m_hier_level_en);
delete zpb_columns
where table_id = m_hr_pers_table_en.TableId and
last_update_date < l_pp_hiert_start_time;
m_table_id := insertTableRecord(m_hr_pers_table_en, m_pers_table_exists);
m_table_id := insertTableRecord(m_hr_shar_table_en, m_shar_table_exists);
m_column_id:=insertColumnRecord(m_hr_pers_col_memCol);
m_column_id:=insertColumnRecord(m_hr_shar_col_memCol);
m_column_id:=insertColumnRecord(m_hr_pers_col_gidCol);
m_column_id:=insertColumnRecord(m_hr_shar_col_gidCol);
m_column_id:=insertColumnRecord(m_hr_shar_col_parentCol);
m_column_id:=insertColumnRecord(m_hr_pers_col_parentCol);
m_column_id:=insertColumnRecord(m_hr_shar_col_pgidCol);
m_column_id:=insertColumnRecord(m_hr_pers_col_pgidCol);
m_column_id:=insertColumnRecord(m_hr_pers_col_orderCol);
m_column_id:=insertColumnRecord(m_hr_shar_col_orderCol);
select table_id into m_hier_en.SharTableId
from zpb_tables
where table_name = m_hr_shar_table_en.TableName and
bus_area_id = bus_area_id_num and
table_type = 'DIMENSION';
m_hier_en.SharTableId := insertTableRecord(m_hr_shar_table_en);
select table_id into m_hier_en.PersTableId
from zpb_tables
where table_name = m_hr_pers_table_en.TableName and
bus_area_id = bus_area_id_num and
table_type = 'DIMENSION';
m_hier_en.PersTableId := insertTableRecord(m_hr_pers_table_en);
m_hierarchy_id := insertHierarchyRecord(m_hier_en);
insertHierarchyTLRecord(m_hier_tl_en);
m_column_id:=insertColumnRecord(m_hr_pers_col_memCol);
m_column_id:=insertColumnRecord(m_hr_shar_col_memCol);
m_column_id:=insertColumnRecord(m_hr_pers_col_gidCol);
m_column_id:=insertColumnRecord(m_hr_shar_col_gidCol);
m_column_id:=insertColumnRecord(m_hr_shar_col_parentCol);
m_column_id:=insertColumnRecord(m_hr_pers_col_parentCol);
m_column_id:=insertColumnRecord(m_hr_shar_col_pgidCol);
m_column_id:=insertColumnRecord(m_hr_pers_col_pgidCol);
m_hier_level_en.SharColId := insertColumnRecord(m_shar_column_en);
m_hier_level_en.PersColId := insertColumnRecord(m_pers_column_en);
m_level_id := insertLevelRecord(m_level_en);
insertLevelTLRecord(m_level_tl_en);
m_hier_level_id := insertHierLevelRecord(m_hier_level_en);
m_attr_rangecol_en.TableId := insertTableRecord(m_attr_rangetbl_en);
m_attr_range_sh_col_en.TableId := insertTableRecord(m_attr_range_sh_tbl_en);
m_attr_rangedim_en.DimensionId := insertDimensionRecord(m_attr_rangedim_en);
m_column_id := insertColumnRecord(m_attr_rangecol_en);
m_column_id := insertColumnRecord(m_attr_range_sh_col_en);
m_column_id := insertColumnRecord(m_attr_rangecol_en);
m_column_id := insertColumnRecord(m_attr_range_sh_col_en);
m_column_id := insertColumnRecord(m_attr_rangecol_en);
m_column_id := insertColumnRecord(m_attr_range_sh_col_en);
m_column_id := insertColumnRecord(m_attr_rangecol_en);
m_column_id := insertColumnRecord(m_attr_range_sh_col_en);
m_attr_rangehl_en.LevelId := insertLevelRecord(m_attr_rangelev_en);
m_attr_rangehl_en.PersColId := insertColumnRecord(m_attr_rangecol_en);
m_attr_rangehl_en.SharColId := insertColumnRecord(m_attr_range_sh_col_en);
m_attr_rangeatc_en.AttributeId := insertAttributeRecord(m_attr_rangeattr_en);
m_attr_rangeatc_en.ColumnId := insertColumnRecord(m_attr_rangecol_en);
m_attr_sh_rangeatc_en.ColumnId := insertColumnRecord(m_attr_range_sh_col_en);
m_attr_table_col_id := insertAttrTableColRecord(m_attr_rangeatc_en);
m_attr_table_col_id := insertAttrTableColRecord(m_attr_sh_rangeatc_en);
m_attr_rangeatc_en.AttributeId := insertAttributeRecord(m_attr_rangeattr_en);
m_attr_rangeatc_en.ColumnId := insertColumnRecord(m_attr_rangecol_en);
m_attr_sh_rangeatc_en.ColumnId := insertColumnRecord(m_attr_range_sh_col_en);
m_attr_table_col_id := insertAttrTableColRecord(m_attr_rangeatc_en);
m_attr_table_col_id := insertAttrTableColRecord(m_attr_sh_rangeatc_en);
insertDimensionsTLRecord(m_attr_rangedim_tl_en);
m_attr_rangehl_en.HierId := insertHierarchyRecord(m_attr_rangehier_en);
m_hier_level_id := insertHierLevelRecord(m_attr_rangehl_en);
m_attr_table_col_en.attributeId := insertAttributeRecord(m_attr_en);
insertAttributesTLRecord(m_attr_tl_en);
m_attr_table_col_en.columnId := insertColumnRecord(m_pers_column_en);
m_attr_table_col_id := insertAttrTableColRecord(m_attr_table_col_en);
m_attr_table_col_en.columnId := insertColumnRecord(m_shar_column_en);
m_attr_table_col_id := insertAttrTableColRecord(m_attr_table_col_en);
m_attr_table_col_en.attributeId := insertAttributeRecord(m_attr_en);
m_attr_table_col_en.columnId := insertColumnRecord(m_pers_column_en);
m_attr_table_col_id := insertAttrTableColRecord(m_attr_table_col_en);
m_attr_table_col_en.columnId := insertColumnRecord(m_shar_column_en);
m_attr_table_col_id := insertAttrTableColRecord(m_attr_table_col_en);
m_attr_table_col_en.attributeId := insertAttributeRecord(m_attr_en);
m_attr_table_col_en.columnId := insertColumnRecord(m_pers_column_en);
m_attr_table_col_id := insertAttrTableColRecord(m_attr_table_col_en);
m_attr_table_col_en.columnId := insertColumnRecord(m_shar_column_en);
m_attr_table_col_id := insertAttrTableColRecord(m_attr_table_col_en);
m_attr_table_col_en.attributeId := insertAttributeRecord(m_attr_en);
m_attr_table_col_en.columnId := insertColumnRecord(m_pers_column_en);
m_attr_table_col_id := insertAttrTableColRecord(m_attr_table_col_en);
m_attr_table_col_en.columnId := insertColumnRecord(m_shar_column_en);
m_attr_table_col_id := insertAttrTableColRecord(m_attr_table_col_en);
m_attr_table_col_en.attributeId := insertAttributeRecord(m_attr_en);
m_attr_table_col_en.columnId := insertColumnRecord(m_pers_column_en);
m_attr_table_col_id := insertAttrTableColRecord(m_attr_table_col_en);
m_attr_table_col_en.columnId := insertColumnRecord(m_shar_column_en);
m_attr_table_col_id := insertAttrTableColRecord(m_attr_table_col_en);
m_attr_table_col_en.attributeId := insertAttributeRecord(m_attr_en);
m_attr_table_col_en.columnId := insertColumnRecord(m_pers_column_en);
m_attr_table_col_id := insertAttrTableColRecord(m_attr_table_col_en);
m_attr_table_col_en.columnId := insertColumnRecord(m_shar_column_en);
m_attr_table_col_id := insertAttrTableColRecord(m_attr_table_col_en);
m_attr_table_col_en.attributeId := insertAttributeRecord(m_attr_en);
m_attr_table_col_en.columnId := insertColumnRecord(m_pers_column_en);
m_attr_table_col_id := insertAttrTableColRecord(m_attr_table_col_en);
m_attr_table_col_en.columnId := insertColumnRecord(m_shar_column_en);
m_attr_table_col_id := insertAttrTableColRecord(m_attr_table_col_en);
m_dummy_num := insertDimensionRecord(m_dimension_en, m_dimension_exists);
insertDimensionsTLRecord(m_dimension_tl_en);
m_table_id := insertTableRecord(m_table_en);
m_cube_num := insertCubeRecord(m_cube_en);
select dimension_id into m_cube_dims_en.dimensionId
from zpb_dimensions
where epb_id = l_dim and bus_area_id = bus_area_id_num;
m_cube_dims_en.columnId := insertColumnRecord(m_column_en);
m_relation_id := insertCubeDimsRecord(m_cube_dims_en);
select hierarchy_id into m_cube_hier_en.hierarchyId
from zpb_hierarchies
where epb_id = l_hier and
dimension_id = m_cube_dims_en.dimensionId;
m_cube_hier_en.columnId := insertColumnRecord(m_column_en);
m_relation_id := insertCubeHierRecord(m_cube_hier_en);
select hierarchy_id into m_cube_hier_en.hierarchyId
from zpb_hierarchies
where dimension_id = m_cube_dims_en.dimensionId and
hier_type = 'NULL';
m_cube_hier_en.columnId := insertColumnRecord(m_column_en);
m_relation_id := insertCubeHierRecord(m_cube_hier_en);
select count(*)
into l_count
from zpb_cubes
where name = l_cube and
bus_area_id = bus_area_id_num;
select count(*)
into l_count
from zpb_cubes
where name = l_cube and
bus_area_id = bus_area_id_num;
select count(*)
into l_count
from zpb_cubes
where name = l_cube and
bus_area_id = bus_area_id_num;
select count(*)
into l_count
from zpb_cubes
where name = l_cube and
bus_area_id = bus_area_id_num;
select count(*)
into l_count
from zpb_cubes
where name = l_cube and
bus_area_id = bus_area_id_num;
l_insert_meas_flag boolean;
select cube_id, table_id into m_meas_en.cubeId, m_column_en.tableId
from zpb_cubes
where name = p_cube and
bus_area_id = bus_area_id_num;
l_insert_meas_flag := true;
l_insert_meas_flag := false;
if l_insert_meas_flag = true then
m_column_en.columnName := p_column;
m_meas_en.columnId := InsertColumnRecord(m_column_en);
m_meas_en.SelectedCur :='NA';
m_meas_en.SelectedCur := zpb_aw.interp ('shw shared!instance.currency (shared!instance ''' || p_instance || ''')');
m_meas_id := InsertMeasureRecord(m_meas_en);
select sysdate into m_start_time from dual;
delete_shared_cubes(p_sharedAW);
m_meas_en.SelectedCur := 'TempNA';
select cube_id, table_id into m_meas_en.cubeId, m_column_en.TableId
from zpb_cubes
where name = m_meas_cube_name and
bus_area_id = bus_area_id_num;
m_meas_en.ColumnId := InsertColumnRecord(m_column_en);
m_meas_id := InsertMeasureRecord(m_meas_en);
m_column_en.TableId := insertTableRecord(m_table_en);
m_column_id_num := insertColumnRecord(m_column_en);
m_column_id_num := insertColumnRecord(m_column_en);
m_column_id_num := insertColumnRecord(m_column_en);
m_column_id_num := insertColumnRecord(m_column_en);
m_column_id_num := insertColumnRecord(m_column_en);
m_column_id_num := insertColumnRecord(m_column_en);
m_column_en.TableId := insertTableRecord(m_table_en);
m_column_id_num := insertColumnRecord(m_column_en);
m_column_id_num := insertColumnRecord(m_column_en);
m_column_id_num := insertColumnRecord(m_column_en);
m_column_id_num := insertColumnRecord(m_column_en);
m_column_id_num := insertColumnRecord(m_column_en);
m_column_id_num := insertColumnRecord(m_column_en);
m_column_en.TableId := insertTableRecord(m_table_en);
m_column_id_num := insertColumnRecord(m_column_en);
m_column_id_num := insertColumnRecord(m_column_en);
m_column_id_num := insertColumnRecord(m_column_en);
m_column_en.TableId := insertTableRecord(m_table_en);
m_column_en.TableId := insertTableRecord(m_table_en);
m_column_en.TableId := insertTableRecord(m_table_en);
m_column_id_num := insertColumnRecord(m_column_en);
m_column_id_num := insertColumnRecord(m_column_en);
m_column_id_num := insertColumnRecord(m_column_en);
m_column_id_num := insertColumnRecord(m_column_en);
m_column_en.TableId := insertTableRecord(m_table_en);
m_column_id_num := insertColumnRecord(m_column_en);
m_column_id_num := insertColumnRecord(m_column_en);
m_column_id_num := insertColumnRecord(m_column_en);
m_column_id_num := insertColumnRecord(m_column_en);
m_column_en.TableId := insertTableRecord(m_table_en);
m_column_id_num := insertColumnRecord(m_column_en);
m_column_id_num := insertColumnRecord(m_column_en);
m_column_id_num := insertColumnRecord(m_column_en);
m_column_id_num := insertColumnRecord(m_column_en);
select DIMENSION_NAME
from ZPB_CYCLE_MODEL_DIMENSIONS
where ANALYSIS_CYCLE_ID = p_instance and
nvl(REMOVE_DIMENSION_FLAG, 'N') <> 'Y'
UNION select 'CAL_PERIODS' from dual;
select CURRENT_INSTANCE_ID
into l_currInst
from ZPB_ANALYSIS_CYCLES
where ANALYSIS_CYCLE_ID = p_instance;
select table_name into m_table_en.TableName
from zpb_cubes, zpb_tables
where zpb_cubes.name = l_cpr_meas_cube and
zpb_cubes.bus_area_id = bus_area_id_num and
zpb_cubes.table_id = zpb_tables.table_id;
m_cube_en.TableId := insertTableRecord(m_table_en);
m_cube_id := insertCubeRecord(m_cube_en);
select dimension_id into m_cube_dims_en.dimensionId
from zpb_dimensions
where epb_id = l_dim and
bus_area_id = bus_area_id_num;
m_cube_dims_en.ColumnId := insertColumnRecord(m_column_en);
m_relation_id := insertCubeDimsRecord(m_cube_dims_en);
m_cube_hier_en.ColumnId :=insertColumnRecord(m_column_en);
select hierarchy_id into m_cube_hier_en.hierarchyId
from zpb_hierarchies
where epb_id = l_hier and
dimension_id = m_cube_dims_en.dimensionId;
m_relation_id := insertCubeHierRecord(m_cube_hier_en);
m_cube_hier_en.ColumnId :=insertColumnRecord(m_column_en);
select hierarchy_id into m_cube_hier_en.hierarchyId
from zpb_hierarchies
where dimension_id = m_cube_dims_en.dimensionId and
hier_type = 'NULL';
m_relation_id := insertCubeHierRecord(m_cube_hier_en);
m_meas_en.SelectedCur :='NA';
m_meas_en.ColumnId := insertColumnRecord(m_column_en);
m_meas_id := insertMeasureRecord(m_meas_en);
select cube_id into existing_cube
from zpb_cubes
where bus_area_id = bus_area_id_num and
name like '%OWNERMAP%';
deleteCubeRecord(existing_cube);
m_cube_en.TableId := insertTableRecord(m_table_en);
m_cube_id := insertCubeRecord(m_cube_en);
select dimension_id into m_cube_dims_en.dimensionId
from zpb_dimensions
where epb_id = l_dim and
bus_area_id = bus_area_id_num;
m_cube_dims_en.ColumnId := insertColumnRecord(m_column_en);
m_relation_id := insertCubeDimsRecord(m_cube_dims_en);
m_cube_hier_en.ColumnId :=insertColumnRecord(m_column_en);
select hierarchy_id into m_cube_hier_en.hierarchyId
from zpb_hierarchies
where epb_id = l_hier and
dimension_id = m_cube_dims_en.dimensionId;
m_relation_id := insertCubeHierRecord(m_cube_hier_en);
m_cube_hier_en.ColumnId :=insertColumnRecord(m_column_en);
select hierarchy_id into m_cube_hier_en.hierarchyId
from zpb_hierarchies
where dimension_id = m_cube_dims_en.dimensionId and
hier_type = 'NULL';
m_relation_id := insertCubeHierRecord(m_cube_hier_en);
m_meas_en.SelectedCur :='NA';
m_meas_en.ColumnId := insertColumnRecord(m_column_en);
m_meas_id := insertMeasureRecord(m_meas_en);
delete from zpb_measures
where measure_id in
(select measure_id from zpb_measures, zpb_cubes
where zpb_measures.cube_id = zpb_cubes.cube_id and
zpb_cubes.name like '%'||p_aw||'/_%' escape '/' and
zpb_measures.epb_id= l_meas);
delete from zpb_measures
where measure_id in
(select measure_id from zpb_measures, zpb_cubes
where zpb_measures.cube_id = zpb_cubes.cube_id and
zpb_cubes.name like '%'||p_aw||'/_%' escape '/' and
zpb_measures.epb_id= l_meas);
delete from zpb_measures
where measure_id in
(select measure_id from zpb_measures, zpb_cubes
where zpb_measures.cube_id = zpb_cubes.cube_id and
zpb_cubes.name like '%'||p_aw||'/_%' escape '/' and
zpb_measures.epb_id= l_meas);
select count(*)
into l_count
from ZPB_MEASURES
where INSTANCE_ID = l_instance
and TYPE = 'PERSONAL_DATA'
and CUBE_ID in (select CUBE_ID from ZPB_CUBES
where NAME like '%'||p_aw||'/_%' escape '/');
delete from zpb_measures
where measure_id in
(select measure_id from zpb_measures, zpb_cubes
where zpb_measures.cube_id = zpb_cubes.cube_id and
zpb_cubes.name like '%'||p_aw||'/_%' escape '/' and
zpb_measures.epb_id= l_meas);
delete from zpb_measures
where measure_id in
(select measure_id from zpb_measures, zpb_cubes
where zpb_measures.cube_id = zpb_cubes.cube_id and
zpb_cubes.name like '%'||p_aw||'/_%' escape '/' and
zpb_measures.epb_id= l_meas);
delete from zpb_measures
where measure_id in
(select measure_id from zpb_measures, zpb_cubes
where zpb_measures.cube_id = zpb_cubes.cube_id and
zpb_cubes.name like '%'||p_aw||'/_%' escape '/' and
zpb_measures.epb_id= l_meas);
delete from zpb_measures
where measure_id in
(select measure_id from zpb_measures, zpb_cubes
where zpb_measures.cube_id = zpb_cubes.cube_id and
zpb_cubes.name like '%'||p_aw||'/_%' escape '/' and
zpb_measures.epb_id= l_meas);
procedure delete_shared_cubes(p_sharaw in varchar2) is
bus_area_id_num number;
select cube_id
from zpb_cubes
where bus_area_id = bus_area_id_num and
name like p_sharaw || '%';
deleteCubeRecord(cube.cube_id);
end delete_shared_cubes;
procedure delete_user(p_user varchar2) is
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_USER';
select cube_id
from zpb_cubes
where bus_area_id = bus_area_id_num and
name like p_user || '/_%' escape '/';
deleteCubeRecord(cube.cube_id);
delete zpb_hier_scope
where user_id = to_number(p_user) and
hierarchy_id in (select hierarchy_id
from zpb_hierarchies hier,
zpb_dimensions dims
where hier.dimension_id = dims.dimension_id and
dims.bus_area_id = bus_area_id_num);
delete zpb_hier_level_scope
where user_id = to_number(p_user) and
hier_id in (select hierarchy_id
from zpb_hierarchies hier,
zpb_dimensions dims
where hier.dimension_id = dims.dimension_id and
dims.bus_area_id = bus_area_id_num);
delete zpb_attribute_scope
where user_id = to_number(p_user) and
attribute_id in (select attribute_id
from zpb_attributes attr,
zpb_dimensions dims
where attr.dimension_id = dims.dimension_id and
dims.bus_area_id = bus_area_id_num);
end delete_user;
select attribute_id
from zpb_attributes
where dimension_id = l_dimension_id and
type in ('TIMESPAN_ATTRIBUTE', 'ENDDATE_ATTRIBUTE', 'SHORT_VALUE_NAME_ATTRIBUTE', 'VALUE_NAME_ATTRIBUTE');
select hier.hierarchy_id, hier.pers_table_id
from zpb_hierarchies hier,
zpb_dimensions dims,
zpb_attributes attr
where dims.bus_area_id = bus_area_id_num and
dims.dimension_id = attr.range_dim_id and
dims.dimension_id = hier.dimension_id;
select hl.hier_id, hl.level_id, hl.pers_col_id
from zpb_hier_level hl,
zpb_dimensions dims,
zpb_attributes attr,
zpb_hierarchies hier
where dims.bus_area_id = bus_area_id_num and
dims.dimension_id = attr.range_dim_id and
dims.dimension_id = hier.dimension_id and
hl.hier_id = hier.hierarchy_id;
select attr2.attribute_id
from zpb_dimensions dims,
zpb_attributes attr,
zpb_attributes attr2
where dims.bus_area_id = bus_area_id_num and
dims.dimension_id = attr.range_dim_id and
dims.dimension_id = attr2.dimension_id;
select sysdate into l_start_time from dual;
select dimension_id into l_dimension_id
from zpb_dimensions
where epb_id = l_dim and
bus_area_id = bus_area_id_num;
update zpb_hier_scope hscope
set hscope.pers_table_id = (select pers_table_id
from zpb_hierarchies hier
where hscope.hierarchy_id = hier.hierarchy_id)
where hscope.user_id = l_user_id and
hscope.user_id = nvl(to_number(sys_context('ZPB_CONTEXT', 'resp_id')), FND_GLOBAL.RESP_ID) and
hscope.hierarchy_id in (select hierarchy_id
from zpb_hierarchies hier,
zpb_dimensions dims
where hier.dimension_id = dims.dimension_id and
dims.bus_area_id = bus_area_id_num and
dims.dimension_id = l_dimension_id);
select count(hierarchy_id) into l_nullHierCnt
from zpb_hierarchies
where dimension_id=l_dimension_id and hier_type='NULL';
select hierarchy_id, pers_table_id
into l_hier_scope_en.HierarchyId, l_hier_scope_en.PersTableId
from zpb_hierarchies
where dimension_id = l_dimension_id and
pers_cwm_name = l_hier_perscwm;
l_dummy_num := insertHierScopeRecord(l_hier_scope_en);
l_pers_column_en.TableId:= insertTableRecord(l_hr_pers_table_en);
l_hl_scope_en.PersColId := insertColumnRecord(l_pers_column_en);
update zpb_hier_scope
set pers_table_id = l_pers_column_en.TableId
where user_id = l_user_id and
resp_id = nvl(to_number(sys_context('ZPB_CONTEXT', 'resp_id')), FND_GLOBAL.RESP_ID) and
hierarchy_id = l_hier_scope_en.HierarchyId;
select level_id into l_hl_scope_en.LevelId
from zpb_levels
where pers_cwm_name = l_level_perscwm and
dimension_id = l_dimension_id;
l_dummy_num := insertHierlevelScopeRecord(l_hl_scope_en);
update zpb_hier_level_scope hlevscope
set hlevscope.pers_col_id = (select pers_col_id from zpb_hier_level hlev
where hlevscope.hier_id = hlev.hier_id and
hlevscope.level_id = hlev.level_id)
where hlevscope.user_id = l_user_id and
hlevscope.resp_id = nvl(to_number(sys_context('ZPB_CONTEXT', 'resp_id')), FND_GLOBAL.RESP_ID) and
hlevscope.hier_id = l_hier_scope_en.HierarchyId;
select hierarchy_id, pers_table_id
into l_hier_scope_en.HierarchyId, l_hier_scope_en.PersTableId
from zpb_hierarchies
where dimension_id = l_dimension_id and
hier_type='NULL';
l_dummy_num := insertHierScopeRecord(l_hier_scope_en);
select hier_id, level_id, pers_col_id
into l_hl_scope_en.HierId, l_hl_scope_en.LevelId, l_hl_scope_en.PersColId
from zpb_hier_level
where hier_id = l_hier_scope_en.HierarchyId;
l_dummy_num := insertHierlevelScopeRecord(l_hl_scope_en);
l_dummy_num := insertHierScopeRecord(l_hier_scope_en);
l_dummy_num := insertHierlevelScopeRecord(l_hl_scope_en);
select attribute_id into l_attr_scope_en.AttributeId
from zpb_attributes
where dimension_id = l_dimension_id and
pers_cwm_name = l_attr_perscwm;
l_dummy_num := insertAttributeScopeRecord(l_attr_scope_en);
l_dummy_num := insertAttributeScopeRecord(l_attr_scope_en);
l_dummy_num := insertAttributeScopeRecord(l_attr_scope_en);
delete zpb_hier_scope
where user_id = l_user_id and
resp_id = nvl(to_number(sys_context('ZPB_CONTEXT', 'resp_id')), FND_GLOBAL.RESP_ID) and
last_update_date < l_start_time and
hierarchy_id in (select hierarchy_id
from zpb_hierarchies hier,
zpb_dimensions dims
where hier.dimension_id = dims.dimension_id and
dims.bus_area_id = bus_area_id_num and
dims.dimension_id = l_dimension_id);
delete zpb_hier_level_scope
where user_id = l_user_id and
resp_id = nvl(to_number(sys_context('ZPB_CONTEXT', 'resp_id')), FND_GLOBAL.RESP_ID) and
last_update_date < l_start_time and
hier_id in (select hierarchy_id
from zpb_hierarchies hier,
zpb_dimensions dims
where hier.dimension_id = dims.dimension_id and
dims.bus_area_id = bus_area_id_num and
dims.dimension_id = l_dimension_id);
delete zpb_attribute_scope
where user_id = l_user_id and
resp_id = nvl(to_number(sys_context('ZPB_CONTEXT', 'resp_id')), FND_GLOBAL.RESP_ID) and
last_update_date < l_start_time and
attribute_id in (select attribute_id
from zpb_attributes attr,
zpb_dimensions dims
where attr.dimension_id = dims.dimension_id and
dims.bus_area_id = bus_area_id_num and
dims.dimension_id = l_dimension_id);
select dimension_id
from zpb_dimensions
where bus_area_id = p_bus_area_id;
select table_id
from zpb_tables
where bus_area_id = p_bus_area_id;
select cube_id
from zpb_cubes
where bus_area_id = p_bus_area_id;
delete zpb_dimensions_tl
where dimension_id = v_dim.dimension_id;
delete zpb_attribute_scope
where attribute_id in (select attribute_id
from zpb_attributes
where dimension_id = v_dim.dimension_id);
delete zpb_attr_table_col
where attribute_id in (select attribute_id
from zpb_attributes
where dimension_id = v_dim.dimension_id);
delete zpb_attributes_tl
where attribute_id in (select attribute_id
from zpb_attributes
where dimension_id = v_dim.dimension_id);
delete zpb_attributes
where dimension_id = v_dim.dimension_id;
delete zpb_hierarchies_tl
where hierarchy_id in (select hierarchy_id
from zpb_hierarchies
where dimension_id = v_dim.dimension_id);
delete zpb_hier_level
where hier_id in (select hierarchy_id
from zpb_hierarchies
where dimension_id = v_dim.dimension_id);
delete zpb_hier_level_scope
where hier_id in (select hierarchy_id
from zpb_hierarchies
where dimension_id = v_dim.dimension_id);
delete zpb_hier_scope
where hierarchy_id in (select hierarchy_id
from zpb_hierarchies
where dimension_id = v_dim.dimension_id);
delete zpb_hierarchies
where dimension_id = v_dim.dimension_id;
delete zpb_levels_tl
where level_id in (select level_id
from zpb_levels
where dimension_id = v_dim.dimension_id);
delete zpb_levels
where dimension_id = v_dim.dimension_id;
delete zpb_dimensions
where dimension_id = v_dim.dimension_id;
delete zpb_columns
where table_id = v_table.table_id;
delete zpb_tables
where table_id = v_table.table_id;
deleteCubeRecord(v_cube.cube_id);