[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
This procedure updates ZPB_ACCOUNT_STATES.READ_SCOPE
WRITE_SCOPE
OWNERSHIP
setting these columns to 2 (locked) as needed.
Also inserts the invalid querys name and path details into
the ZPB_VALIDATION_TEMP_DATA table for later retrieval in java layer.
-- p_baId -- Business Area Id
-- p_user_id -- User id pulled from query
-- p_queryName -- The Invalid Query Object Name
-- p_queryPath -- The Invalid Query object path
-- p_queryType -- G_READ_RULE,G_WRITE_RULE,G_OWNER_RULE
-- p_queryErrorType -- Tells whether the query is to be fixed +
-- marked as Invalid ("F") OR Just Refrshed ("R").
-- "R" only if a dimension has been removed
-- -- in which case fixing is not going to work.
-- p_init_fix -- Flag to confirm whether MD fixing should be done or not
-- We do not fix for real-time validation from UI.
-- p_statusSqlId -- Status sql id from query
*/
------------------------------------------------------------------------------
PROCEDURE LOCK_OUT_USER(p_baId IN NUMBER,
p_userid IN FND_USER.USER_ID%type,
p_queryName IN VARCHAR2,
p_queryPath IN ZPB_STATUS_SQL.QUERY_PATH%type,
p_queryType IN VARCHAR2,
p_queryErrorType IN VARCHAR2,
p_init_fix IN VARCHAR2,
p_statusSqlId IN ZPB_STATUS_SQL.STATUS_SQL_ID%type)
IS
BEGIN
IF p_init_fix = 'Y'
THEN
IF p_queryType = G_READ_RULE
THEN
UPDATE ZPB_ACCOUNT_STATES
SET READ_SCOPE = G_LOCK_OUT,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = FND_GLOBAL.USER_ID
WHERE USER_ID = p_userId AND
BUSINESS_AREA_ID = p_baId;
UPDATE ZPB_ACCOUNT_STATES
SET WRITE_SCOPE = G_LOCK_OUT,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = FND_GLOBAL.USER_ID
WHERE USER_ID = p_userId AND
BUSINESS_AREA_ID = p_baId;
UPDATE ZPB_ACCOUNT_STATES
SET OWNERSHIP = G_LOCK_OUT,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = FND_GLOBAL.USER_ID
WHERE USER_ID = p_userId AND
BUSINESS_AREA_ID = p_baId;
INSERT into ZPB_VALIDATION_TEMP_DATA
(BUSINESS_AREA_ID,
VALUE_TYPE,
VALUE,
STATUS_SQL_ID)
VALUES (p_baId,
p_queryErrorType,
p_queryPath || fnd_global.newline()|| p_queryName,
p_statusSqlId);
l_command := 'SELECT '||G_MEMBER_NAME_COL||' FROM '
||G_LINE_DIM_TABLE_NAME||' WHERE '||G_MEMBER_ID_COL|| ' = ''' ||p_memberID||'''';
This procedure updates the invalid Published BP definition's status_code as
'INVALID_BP' and Inserts the invalid query's name and path details into
the ZPB_VALIDATION_TEMP_DATA table for later retrieval in java layer.
-- p_queryName -- The Invalid Query Object Name
-- p_queryPath -- The Invalid Query object path
-- p_queryErrorType -- Tells whether the query is to be fixed +
-- marked as Invalid ("F") OR Just Refrshed ("R")
-- p_acID -- ANALYSIS_CYCLE_ID
-- p_init_fix -- Flag to confirm whether MD fixing should be done
-- fixed or not
*/
------------------------------------------------------------------------------
PROCEDURE DISABLE_BP(p_baId IN NUMBER,
p_queryName IN VARCHAR,
p_queryPath IN VARCHAR,
p_queryErrorType IN VARCHAR,
p_acID IN zpb_analysis_cycles.analysis_cycle_id%TYPE := NULL,
p_init_fix IN VARCHAR2)
IS
l_num NUMBER;
INSERT into ZPB_VALIDATION_TEMP_DATA
(BUSINESS_AREA_ID,
VALUE_TYPE,
VALUE,
ANALYSIS_CYCLE_ID)
VALUES (p_baId,
p_queryErrorType ,
p_queryPath || fnd_global.newline()|| p_queryName,
p_acID);
SELECT nvl(published_ac_id, 0)
INTO l_num
FROM zpb_cycle_relationships
WHERE published_ac_id = p_acID;
UPDATE zpb_analysis_cycles
SET STATUS_CODE = 'INVALID_BP'
WHERE analysis_cycle_id = p_acID
AND business_area_id = p_baID;
insert into ZPB_BUSAREA_VALIDATIONS
(VALIDATION_TYPE,
ERROR_TYPE,
MESSAGE)
values (p_val_type,
p_err_type,
FND_MESSAGE.GET);
select distinct(TABLE_NAME)
from FEM_DATA_LOCATIONS
where dataset_code = l_dataset_id;
select A.DATASET_ID
from ZPB_BUSAREA_DATASETS A
where A.VERSION_ID = p_version_id
and A.DATASET_ID in
(select DATASET_CODE
from FEM_DATASETS_B
where ENABLED_FLAG = 'Y');
select CURRENCY_ENABLED
into l_currency
from ZPB_BUSAREA_VERSIONS
where VERSION_ID = p_version_id;
select varchar_assign_value
into l_spec_dim_list
from fem_datasets_attr fdat, fem_dim_attributes_b fatt
where fdat.attribute_id = fatt.attribute_id
AND fatt.attribute_varchar_label = 'ZPB_DIMENSION_LIST'
AND fdat.dataset_code = l_dataset_id;
'select distinct(fem_xdims.dimension_id)
from fem_tab_column_prop fem_tab, fem_xdim_dimensions fem_xdims, fem_dimensions_b fem_dims
where fem_xdims.member_col = fem_tab.column_name
AND (fem_tab.table_name = '''||each.TABLE_NAME||''')
AND (fem_tab.column_property_code) = ''PROCESSING_KEY''
AND fem_dims.DIMENSION_ID = fem_xdims.DIMENSION_ID
AND fem_dims.DIMENSION_VARCHAR_LABEL <> ''LEDGER''
AND fem_dims.DIMENSION_VARCHAR_LABEL <> ''DATASET''
AND fem_dims.DIMENSION_VARCHAR_LABEL <> ''SOURCE_SYSTEM''
AND fem_dims.DIMENSION_VARCHAR_LABEL <> ''CURRENCY_TYPE''
AND fem_dims.DIMENSION_VARCHAR_LABEL <> ''NATURAL_ACCOUNT''
AND fem_xdims.DIMENSION_TYPE_CODE <> ''LINE'' ';
l_command := 'select dimension_id from ( ';
l_command := l_command || ' select ' || l_dimension_id || ' dimension_id from dual ';
select dimension_id
from ZPB_BUSAREA_DIMENSIONS
where version_id = ' || p_version_id;
l_command := 'select DIMENSION_NAME
from FEM_DIMENSIONS_VL
where DIMENSION_ID in ('||l_missing_dim_list||')';
select A.HIERARCHY_ID,
A.LOGICAL_DIM_ID
from ZPB_BUSAREA_HIERARCHIES A
where A.VERSION_ID = p_version_id
and A.HIERARCHY_ID not in
(select HIERARCHY_OBJ_ID
from FEM_HIERARCHIES
where PERSONAL_FLAG = 'N');
select A.DIMENSION_ID,
A.DEFAULT_HIERARCHY_ID,
A.LOGICAL_DIM_ID
from ZPB_BUSAREA_DIMENSIONS A
where A.VERSION_ID = p_version_id
and A.DEFAULT_HIERARCHY_ID not in
(select HIERARCHY_OBJ_ID
from FEM_HIERARCHIES
where PERSONAL_FLAG = 'N');
select A.VERSION_ID,
A.HIERARCHY_ID,
A.HIER_VERSION_ID,
A.LOGICAL_DIM_ID
from ZPB_BUSAREA_HIER_VERSIONS A
where A.VERSION_ID = p_version_id
and A.HIER_VERSION_ID not in
(select B.OBJECT_DEFINITION_ID
from FEM_OBJECT_DEFINITION_B B
where A.HIERARCHY_ID = B.OBJECT_ID);
select A.LEVEL_ID,
A.HIERARCHY_ID,
A.LOGICAL_DIM_ID
from ZPB_BUSAREA_LEVELS A
where A.VERSION_ID = p_version_id
and A.LEVEL_ID not in
(select B.DIMENSION_GROUP_ID
from FEM_DIMENSION_GRPS_B B,
FEM_HIER_DIMENSION_GRPS C
where B.DIMENSION_GROUP_ID = C.DIMENSION_GROUP_ID
and C.HIERARCHY_OBJ_ID = A.HIERARCHY_ID
and B.PERSONAL_FLAG = 'N');
select A.ATTRIBUTE_ID,
A.LOGICAL_DIM_ID
from ZPB_BUSAREA_ATTRIBUTES A
where A.VERSION_ID = p_version_id
and A.ATTRIBUTE_ID not in
(select ATTRIBUTE_ID
from FEM_DIM_ATTRIBUTES_B
where PERSONAL_FLAG = 'N');
select A.LEDGER_ID
from ZPB_BUSAREA_LEDGERS A
where A.VERSION_ID = p_version_id
and A.LEDGER_ID not in
(select LEDGER_ID
from FEM_LEDGERS_B
where ENABLED_FLAG = 'Y');
select A.DATASET_ID
from ZPB_BUSAREA_DATASETS A
where A.VERSION_ID = p_version_id
and A.DATASET_ID not in
(select DATASET_CODE
from FEM_DATASETS_B
where ENABLED_FLAG = 'Y');
select A.DIMENSION_ID, B.HIER_EDITOR_MANAGED_FLAG,
A.LOGICAL_DIM_ID
from ZPB_BUSAREA_DIMENSIONS A, FEM_XDIM_DIMENSIONS B
where A.VERSION_ID = p_version_id
and A.DIMENSION_ID = B.DIMENSION_ID;
update ZPB_BUSAREA_DIMENSIONS
set DEFAULT_HIERARCHY_ID = null,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
where VERSION_ID = p_version_id
and LOGICAL_DIM_ID = each.LOGICAL_DIM_ID
and DIMENSION_ID = each.DIMENSION_ID;
delete from ZPB_BUSAREA_LEDGERS
where VERSION_ID = p_version_id
and LEDGER_ID = each.LEDGER_ID;
delete from ZPB_BUSAREA_DATASETS
where VERSION_ID = p_version_id
and DATASET_ID = each.DATASET_ID;
select A.MEMBER_B_TABLE_NAME,
A.HIERARCHY_TABLE_NAME,
A.MEMBER_COL,
A.VALUE_SET_REQUIRED_FLAG
into l_dim_table, l_hier_table, l_col, l_vset_required
from FEM_XDIM_DIMENSIONS A,
ZPB_BUSAREA_DIMENSIONS B
where B.DIMENSION_ID = each.DIMENSION_ID
and B.VERSION_ID = p_version_id
and B.LOGICAL_DIM_ID = each.LOGICAL_DIM_ID
AND A.DIMENSION_ID = B.DIMENSION_ID;
'select A.HIERARCHY_ID, A.MEMBER_ID, A.VALUE_SET_ID,
A.LOGICAL_DIM_ID
from ZPB_BUSAREA_HIER_MEMBERS A,
FEM_HIERARCHIES B
where A.HIERARCHY_ID = B.HIERARCHY_OBJ_ID
and A.LOGICAL_DIM_ID = '||each.LOGICAL_DIM_ID||'
and B.DIMENSION_ID = '||each.DIMENSION_ID||'
and A.VERSION_ID = '||p_version_id||'
and A.MEMBER_ID not in
(select distinct C.CHILD_ID
from '||l_hier_table||' C,
FEM_OBJECT_DEFINITION_B D,
'||l_dim_table||' E
where C.HIERARCHY_OBJ_DEF_ID = D.OBJECT_DEFINITION_ID
and D.OBJECT_ID = A.HIERARCHY_ID
and D.OBJECT_DEFINITION_ID = nvl(A.HIER_VERSION_ID,
D.OBJECT_DEFINITION_ID)
and C.CHILD_ID = E.'||l_col||'
and C.CHILD_ID = C.PARENT_ID
and C.CHILD_DEPTH_NUM = 1';
delete from ZPB_BUSAREA_HIER_MEMBERS
where VERSION_ID = p_version_id
and LOGICAL_DIM_ID = l_logical_dim_id
and HIERARCHY_ID = l_hierarchy
and MEMBER_ID = l_member_id
and VALUE_SET_ID = l_value_set_id;
select A.DIMENSION_ID,
A.DEFAULT_HIERARCHY_ID,
A.USE_MEMBER_CONDITIONS,
A.EPB_LINE_DIMENSION,
A.LOGICAL_DIM_ID,
A.AW_DIM_NAME,
A.AW_DIM_PREFIX,
DECODE(nvl(FDR.FUNC_DIM_SET_NAME, '-99'), '-99',
B.DIMENSION_NAME,FDR.FUNC_DIM_SET_NAME) AS DIMENSION_NAME,
X.MEMBER_COL
from ZPB_BUSAREA_DIMENSIONS A,
FEM_FUNC_DIM_SETS_VL FDR,
FEM_DIMENSIONS_VL B,
FEM_XDIM_DIMENSIONS X
where A.VERSION_ID = p_version_id
and FDR.FUNC_DIM_SET_ID (+) = A.FUNC_DIM_SET_ID
and A.DIMENSION_ID = B.DIMENSION_ID
AND X.DIMENSION_ID = A.DIMENSION_ID;
select A.ATTRIBUTE_ID, B.VERSION_ID, A.NAME
from ZPB_BUSAREA_ATTRIBUTES_VL A,
FEM_DIM_ATTR_VERSIONS_B B
where A.LOGICAL_DIM_ID = p_logical_dim_id
and A.VERSION_ID = p_version_id
and A.ATTRIBUTE_ID = B.ATTRIBUTE_ID
and B.DEFAULT_VERSION_FLAG = 'Y'
and B.AW_SNAPSHOT_FLAG = 'N';
select A.ATTRIBUTE_ID, A.VALUE, A.VALUE_SET_ID
from ZPB_BUSAREA_CONDITIONS_V A
where A.VERSION_ID = p_version_id
and A.LOGICAL_DIM_ID = p_logical_dim_id
MINUS
select A.ATTRIBUTE_ID, A.VALUE, A.VALUE_SET_ID
from ZPB_BUSAREA_CONDITIONS_V A,
FEM_VALUE_SETS_B C,
FEM_GLOBAL_VS_COMBO_DEFS D
where A.VERSION_ID = p_version_id
and A.LOGICAL_DIM_ID = p_logical_dim_id
and A.VALUE_SET_ID is not null
and C.VALUE_SET_ID = A.VALUE_SET_ID
and C.DIMENSION_ID = A.DIMENSION_ID
and D.DIMENSION_ID = A.DIMENSION_ID
and D.VALUE_SET_ID = C.VALUE_SET_ID
and D.GLOBAL_VS_COMBO_ID = l_vs_combo_id;
select name from ZPB_BUSAREA_LEVELS_VL
where version_id = p_version_id
order by logical_dim_id, hierarchy_id;
select hierarchy_id, name from ZPB_BUSAREA_HIERARCHIES_VL
where version_id = p_version_id
order by logical_dim_id;
select BUSINESS_AREA_ID, VERSION_TYPE, CURRENCY_ENABLED,
INTERCOMPANY_ENABLED
into l_ba_id, l_vers_type, l_currency, l_intercompany
from ZPB_BUSAREA_VERSIONS
where VERSION_ID = p_version_id;
select count(*)
into l_count
from FEM_OBJECT_DEFINITION_B
where OBJECT_ID = each_hier.HIERARCHY_ID
and effective_start_date <= sysdate
and effective_end_date >= sysdate;
select count(*)
into l_count
from ZPB_BUSAREA_USERS A,
FND_USER_RESP_GROUPS B,
FND_RESPONSIBILITY C,
FND_USER D
where A.BUSINESS_AREA_ID = l_ba_id
and A.USER_ID = B.USER_ID
and B.RESPONSIBILITY_APPLICATION_ID = 210
and nvl(B.END_DATE, sysdate) >= sysdate
and nvl(B.START_DATE, sysdate) <= sysdate
and B.RESPONSIBILITY_ID = C.RESPONSIBILITY_ID
and C.APPLICATION_ID = 210
and C.RESPONSIBILITY_KEY = 'ZPB_MANAGER_RESP'
and A.USER_ID = D.USER_ID
and nvl(D.END_DATE, sysdate) >= sysdate
and D.START_DATE <= sysdate;
select count(*)
into l_count
from ZPB_BUSAREA_DIMENSIONS A, FEM_XDIM_DIMENSIONS B
where A.VERSION_ID = p_version_id
and A.DIMENSION_ID = B.DIMENSION_ID
and B.DIMENSION_TYPE_CODE = 'TIME';
select count(*)
into l_count
from ZPB_BUSAREA_DIMENSIONS
where VERSION_ID = p_version_id
and EPB_LINE_DIMENSION = 'Y';
select count(*)
into l_count
from ZPB_BUSAREA_DIMENSIONS A,
FEM_XDIM_DIMENSIONS B
where A.VERSION_ID = p_version_id
and A.DIMENSION_ID = B.DIMENSION_ID
and B.MEMBER_B_TABLE_NAME = 'FEM_CURRENCIES_VL';
select count(*)
into l_count
from ZPB_BUSAREA_LEDGERS
where VERSION_ID = p_version_id;
select nvl(FUNC_DIM_SET_OBJ_DEF_ID, -99)
into l_fdr_id
from ZPB_BUSAREA_VERSIONS
where VERSION_ID = p_version_id;
select count(distinct (C.DIM_ATTRIBUTE_NUMERIC_MEMBER))
into l_count
from ZPB_BUSAREA_LEDGERS B,
FEM_LEDGERS_ATTR C, FEM_DIM_ATTRIBUTES_B D,
FEM_DIM_ATTR_VERSIONS_B E
where D.ATTRIBUTE_VARCHAR_LABEL = 'GLOBAL_VS_COMBO'
and D.ATTRIBUTE_ID = E.ATTRIBUTE_ID
and E.DEFAULT_VERSION_FLAG = 'Y'
and E.AW_SNAPSHOT_FLAG = 'N'
and C.VERSION_ID = E.VERSION_ID
and C.ATTRIBUTE_ID = D.ATTRIBUTE_ID
and B.LEDGER_ID = C.LEDGER_ID
and B.VERSION_ID = p_version_id;
select count(distinct (C.DIM_ATTRIBUTE_NUMERIC_MEMBER))
into l_count
from ZPB_BUSAREA_LEDGERS B,
FEM_LEDGERS_ATTR C, FEM_DIM_ATTRIBUTES_B D,
FEM_DIM_ATTR_VERSIONS_B E,
fem_object_definition_b objdef,fem_object_catalog_b obj
where D.ATTRIBUTE_VARCHAR_LABEL = 'GLOBAL_VS_COMBO'
and D.ATTRIBUTE_ID = E.ATTRIBUTE_ID
and E.DEFAULT_VERSION_FLAG = 'Y'
and E.AW_SNAPSHOT_FLAG = 'N'
and C.VERSION_ID = E.VERSION_ID
and C.ATTRIBUTE_ID = D.ATTRIBUTE_ID
and B.LEDGER_ID = C.LEDGER_ID
and B.VERSION_ID = p_version_id
and objdef.object_definition_id=l_fdr_id
and objdef.object_id=obj.object_id
and C.DIM_ATTRIBUTE_NUMERIC_MEMBER<>obj.LOCAL_VS_COMBO_ID;
SELECT A.DISPLAY_NAME
into l_fdr_desc
FROM FEM_OBJECT_DEFINITION_VL A, FEM_FUNC_DIM_SETS_B B,
ZPB_BUSAREA_VERSIONS C
WHERE C.VERSION_ID = p_version_id
and A.OBJECT_DEFINITION_ID = C.FUNC_DIM_SET_OBJ_DEF_ID;
select count(*)
into l_count
from FEM_DIM_ATTRIBUTES_B A,
ZPB_BUSAREA_DIMENSIONS B
where A.ATTRIBUTE_VARCHAR_LABEL = 'ZPB_ORG_CURRENCY'
and A.DIMENSION_ID = B.DIMENSION_ID
and B.VERSION_ID = p_version_id;
select count(distinct B.DIMENSION_ID)
into l_count
from FEM_DIM_ATTRIBUTES_B A,
ZPB_BUSAREA_DIMENSIONS B
where A.ATTRIBUTE_VARCHAR_LABEL = 'ZPB_ORG_CURRENCY'
and A.DIMENSION_ID = B.DIMENSION_ID
and B.VERSION_ID = p_version_id;
select A.ATTRIBUTE_ID, A.DIMENSION_ID, B.LOGICAL_DIM_ID
into l_curr_attr_id, l_org_dim_id, l_org_logical_dim_id
from FEM_DIM_ATTRIBUTES_B A,
ZPB_BUSAREA_DIMENSIONS B,
ZPB_BUSAREA_ATTRIBUTES C
where ATTRIBUTE_VARCHAR_LABEL = 'ZPB_ORG_CURRENCY'
and A.DIMENSION_ID = B.DIMENSION_ID
and B.LOGICAL_DIM_ID = C.LOGICAL_DIM_ID
and B.VERSION_ID = p_version_id
and C.VERSION_ID = B.VERSION_ID;
select A.ATTRIBUTE_ID, A.DIMENSION_ID, B.LOGICAL_DIM_ID
into l_curr_attr_id, l_org_dim_id, l_org_logical_dim_id
from FEM_DIM_ATTRIBUTES_B A,
ZPB_BUSAREA_DIMENSIONS B
where ATTRIBUTE_VARCHAR_LABEL = 'ZPB_ORG_CURRENCY'
and A.DIMENSION_ID = B.DIMENSION_ID
and B.VERSION_ID = p_version_id;
select count(*)
into l_count
from ZPB_BUSAREA_DIMENSIONS A,
FEM_TAB_COLUMNS_B B
where A.VERSION_ID = p_version_id
and A.DIMENSION_ID = B.DIMENSION_ID
and B.COLUMN_NAME = 'INTERCOMPANY_ID'
and B.TABLE_NAME = 'FEM_BALANCES';
select distinct (C.DIM_ATTRIBUTE_NUMERIC_MEMBER)
into l_vs_combo_id
from ZPB_BUSAREA_LEDGERS B,
FEM_LEDGERS_ATTR C, FEM_DIM_ATTRIBUTES_B D,
FEM_DIM_ATTR_VERSIONS_B E
where D.ATTRIBUTE_VARCHAR_LABEL = 'GLOBAL_VS_COMBO'
and D.ATTRIBUTE_ID = E.ATTRIBUTE_ID
and E.DEFAULT_VERSION_FLAG = 'Y'
and E.AW_SNAPSHOT_FLAG = 'N'
and C.VERSION_ID = E.VERSION_ID
and C.ATTRIBUTE_ID = D.ATTRIBUTE_ID
and B.LEDGER_ID = C.LEDGER_ID
and B.VERSION_ID = p_version_id;
select count(*)
into l_count
from ZPB_BUSAREA_DIMENSIONS A,
FEM_DIM_ATTRIBUTES_B B
where A.DIMENSION_ID = B.DIMENSION_ID
and B.ATTRIBUTE_VARCHAR_LABEL = 'EXTENDED_ACCOUNT_TYPE'
and A.EPB_LINE_DIMENSION = 'Y'
and A.VERSION_ID = p_version_id;
l_command := 'select count(*)
from table(ZPB_FEM_UTILS_PKG.GET_LIST_DIM_MEMBERS
('||each.DIMENSION_ID||', '||each.LOGICAL_DIM_ID||','||l_ba_id||', '''||l_vers_type||'''))';
select A.MEMBER_B_TABLE_NAME,
A.HIERARCHY_TABLE_NAME,
A.ATTRIBUTE_TABLE_NAME,
A.MEMBER_COL,
A.VALUE_SET_REQUIRED_FLAG,
A.DIMENSION_TYPE_CODE
into l_dim_table, l_hier_table, l_attr_table,
l_col, l_vset_required, l_dim_type
from FEM_XDIM_DIMENSIONS A,
ZPB_BUSAREA_DIMENSIONS B
where B.DIMENSION_ID = each.DIMENSION_ID
AND B.LOGICAL_DIM_ID = each.LOGICAL_DIM_ID
AND B.VERSION_ID = p_version_id
AND A.DIMENSION_ID = B.DIMENSION_ID;
select count(*), decode(count(*), 0, 'Y', 'N')
into l_count, l_no_hierarchies
from ZPB_BUSAREA_HIERARCHIES_VL
where DIMENSION_ID = each.DIMENSION_ID
and LOGICAL_DIM_ID = each.LOGICAL_DIM_ID
and VERSION_ID = p_version_id;
select IS_OWNER_DIM
into l_owner_dim
from ZPB_DIMENSIONS
where BUS_AREA_ID = l_ba_id
and EPB_ID = each.AW_DIM_PREFIX;
l_command := 'select count(*)
from table(ZPB_FEM_UTILS_PKG.GET_LIST_DIM_MEMBERS
('||each.DIMENSION_ID||','||each.LOGICAL_DIM_ID||', '||l_ba_id||', '''||l_vers_type||
''')) A, '||l_attr_table||' B ';
select count(*)
into l_count
from ZPB_BUSAREA_HIERARCHIES
where VERSION_ID = p_version_id
and LOGICAL_DIM_ID = each.LOGICAL_DIM_ID
and HIERARCHY_ID = each.DEFAULT_HIERARCHY_ID;
l_command := 'select distinct HIERARCHY_ID, VERSION_ID, LOGICAL_DIM_ID
from table(ZPB_FEM_UTILS_PKG.GET_HIERARCHY_MEMBERS
('||each.LOGICAL_DIM_ID||', '||l_ba_id||', '''||
l_vers_type||'''))';
l_command := 'select A.HIERARCHY_ID, A.VERSION_ID, A.CURRENT_VERSION
from table(ZPB_FEM_UTILS_PKG.GET_BUSAREA_HIERARCHIES
('||l_ba_id||','''||l_vers_type||''')) A,
ZPB_BUSAREA_HIERARCHIES_VL B
where A.HIERARCHY_ID = B.HIERARCHY_ID
and A.LOGICAL_DIM_ID = B.LOGICAL_DIM_ID
and B.VERSION_ID = '||p_version_id||'
and B.LOGICAL_DIM_ID = '||each.LOGICAL_DIM_ID||'
and B.DIMENSION_ID = '||each.DIMENSION_ID;
select OBJECT_NAME
into l_hier_name
from FEM_OBJECT_CATALOG_VL
where OBJECT_ID = l_hier_id;
select DISPLAY_NAME
into l_hier_name
from FEM_OBJECT_DEFINITION_VL
where OBJECT_DEFINITION_ID = l_hier_vers_id;
'select distinct Y.HIERARCHY_ID, Y.VERSION_ID from
FEM_HIER_DIMENSION_GRPS X,
(select distinct A.HIERARCHY_ID, A.VERSION_ID,
B.RELATIVE_DIMENSION_GROUP_SEQ PARENT_SEQ,
C.RELATIVE_DIMENSION_GROUP_SEQ CHILD_SEQ
from (table(ZPB_FEM_UTILS_PKG.GET_HIERARCHY_MEMBERS
('||each.LOGICAL_DIM_ID||', '||l_ba_id||', '''||
l_vers_type||'''))) A,
FEM_HIER_DIMENSION_GRPS B, FEM_HIER_DIMENSION_GRPS C,
'||l_dim_table||' D, '||l_dim_table||' E
where A.PARENT_ID = D.'||l_col||'
and D.DIMENSION_GROUP_ID = B.DIMENSION_GROUP_ID
and B.HIERARCHY_OBJ_ID = A.HIERARCHY_ID
and A.CHILD_ID = E.'||l_col||'
and E.DIMENSION_GROUP_ID = C.DIMENSION_GROUP_ID
and C.HIERARCHY_OBJ_ID = A.HIERARCHY_ID) Y
where X.HIERARCHY_OBJ_ID = Y.HIERARCHY_ID
and X.RELATIVE_DIMENSION_GROUP_SEQ > Y.PARENT_SEQ
and X.RELATIVE_DIMENSION_GROUP_SEQ < Y.CHILD_SEQ';
select OBJECT_NAME
into l_hier_name
from FEM_OBJECT_CATALOG_VL
where OBJECT_ID = l_hier_id;
select DISPLAY_NAME
into l_hier_name
from FEM_OBJECT_DEFINITION_VL
where OBJECT_DEFINITION_ID = l_hier_vers_id;
l_command := 'select count(*)
from table(ZPB_FEM_UTILS_PKG.GET_HIERARCHY_MEMBERS
('||each.LOGICAL_DIM_ID||', '||l_ba_id||', '''||l_vers_type||
''')) A, '||l_attr_table||' B ';
select DEFAULT_HIER, IS_OWNER_DIM
into l_def_hier, l_owner_dim
from ZPB_DIMENSIONS
where BUS_AREA_ID = l_ba_id
and EPB_ID = each.AW_DIM_PREFIX;
'select count(*) '||
'from table(ZPB_FEM_UTILS_PKG.GET_HIERARCHY_MEMBERS('||
each.LOGICAL_DIM_ID||', '||l_ba_id||', '''||l_vers_type||
''')) A, '||l_attr_table||' B, FEM_DIM_ATTR_VERSIONS_B C';
l_command := 'select decode(count(*), 0, 0, 1)';
l_command := l_command || ' from (SELECT DISTINCT(to_number(substr(A.MEMBER_ID, instr(A.MEMBER_ID, ''_'')+1))) from table(ZPB_FEM_UTILS_PKG.GET_LIST_DIM_MEMBERS(';
l_command := l_command || ' from (SELECT DISTINCT(to_number(A.MEMBER_ID)) from table(ZPB_FEM_UTILS_PKG.GET_LIST_DIM_MEMBERS(';
l_command := l_command || ' from (SELECT DISTINCT(to_number(substr(A.CHILD_ID, instr(A.CHILD_ID, ''_'')+1))) ';
l_command := l_command || ' from (SELECT DISTINCT(to_number(A.CHILD_ID))';
' MINUS select B.'||l_col||
' from FEM_DIM_ATTRIBUTES_B A,' ||
' '||l_dim_table||' B,' ||
' '||l_attr_table||' C,' ||
' FEM_DIM_ATTR_GRPS D,' ||
' FEM_DIM_ATTR_VERSIONS_B E,';
select DECODE(nvl(FDR.FUNC_DIM_SET_NAME, '-99'), '-99',
A.DIMENSION_NAME,FDR.FUNC_DIM_SET_NAME) AS DIMENSION_NAME
into l_dim_name
from FEM_DIMENSIONS_VL A,
ZPB_BUSAREA_DIMENSIONS B,
FEM_FUNC_DIM_SETS_VL FDR
where B.DIMENSION_ID = l_org_dim_id
and B.LOGICAL_DIM_ID = l_org_logical_dim_id
and B.VERSION_ID = p_version_id
and A.DIMENSION_ID = B.DIMENSION_ID
and FDR.FUNC_DIM_SET_ID (+) = B.FUNC_DIM_SET_ID ;
select ATTRIBUTE_NAME
into l_attr_name
from FEM_DIM_ATTRIBUTES_VL
where ATTRIBUTE_VARCHAR_LABEL = 'ZPB_ORG_CURRENCY'
and DIMENSION_ID = l_org_dim_id;
l_command := 'select decode(count(*), 0, 0, 1)';
l_command := l_command || ' from (SELECT DISTINCT(to_number(substr(A.MEMBER_ID, instr(A.MEMBER_ID, ''_'')+1))) from table(ZPB_FEM_UTILS_PKG.GET_LIST_DIM_MEMBERS(';
l_command := l_command || ' from (SELECT DISTINCT(to_number(A.MEMBER_ID)) from table(ZPB_FEM_UTILS_PKG.GET_LIST_DIM_MEMBERS(';
l_command := l_command || ' from (SELECT DISTINCT(to_number(substr(A.CHILD_ID, instr(A.CHILD_ID, ''_'')+1)))';
l_command := l_command || ' from (SELECT DISTINCT(to_number(A.CHILD_ID))';
' MINUS select B.'||l_col||
' from FEM_DIM_ATTRIBUTES_B A,' ||
' '||l_dim_table||' B,' ||
' '||l_attr_table||' C,' ||
' FEM_DIM_ATTR_VERSIONS_B E,';
select DECODE(nvl(FDR.FUNC_DIM_SET_NAME, '-99'), '-99',
A.DIMENSION_NAME,FDR.FUNC_DIM_SET_NAME) AS DIMENSION_NAME
into l_dim_name
from FEM_DIMENSIONS_VL A,
ZPB_BUSAREA_DIMENSIONS B,
FEM_FUNC_DIM_SETS_VL FDR
where B.DIMENSION_ID = l_org_dim_id
and B.LOGICAL_DIM_ID = l_org_logical_dim_id
and B.VERSION_ID = p_version_id
and A.DIMENSION_ID = B.DIMENSION_ID
and FDR.FUNC_DIM_SET_ID (+) = B.FUNC_DIM_SET_ID ;
select ATTRIBUTE_NAME
into l_attr_name
from FEM_DIM_ATTRIBUTES_VL
where ATTRIBUTE_VARCHAR_LABEL = 'ZPB_ORG_CURRENCY'
and DIMENSION_ID = l_org_dim_id;
SELECT distinct A.OBJECT_ID,
A.OBJECT_NAME,
B.OBJECT_TYPE_NAME,
C.OBJECT_NAME FOLDER_NAME,
A.FOLDER_ID
FROM BISM_OBJECTS A,
BISM_OBJECT_TYPES B,
BISM_OBJECTS C,
(select C.CONTAINER_ID
from BISM_OBJECTS C,
BISM_OBJECT_TYPES D
where C.OBJECT_TYPE_ID = D.OBJECT_TYPE_ID
and D.OBJECT_TYPE_NAME = 'Selection'
and C.XML like p_search_str
and C.FOLDER_ID IN
(select OBJECT_ID
from BISM_OBJECTS
where OBJECT_TYPE_ID = 100
start with OBJECT_ID = l_folder
connect by FOLDER_ID = prior OBJECT_ID)) D
WHERE A.OBJECT_TYPE_ID = B.OBJECT_TYPE_ID
and A.FOLDER_ID = C.OBJECT_ID
and A.OBJECT_ID = D.CONTAINER_ID
and B.OBJECT_TYPE_NAME <> 'Selection';
select TEMPLATE_NAME, OBJECT_TYPE, A.ANALYSIS_CYCLE_ID, TARGET_OBJ_PATH
into l_str2, l_str, l_acID, l_queryPath
from ZPB_DC_OBJECTS A, ZPB_ANALYSIS_CYCLES B
where A.ANALYSIS_CYCLE_ID = B.ANALYSIS_CYCLE_ID
AND B.STATUS_CODE <> 'MARKED_FOR_DELETION'
AND B.BUSINESS_AREA_ID = p_baID
AND TARGET_OBJ_NAME = p_search_str;
select TEMPLATE_NAME, OBJECT_TYPE, A.ANALYSIS_CYCLE_ID, TARGET_OBJ_PATH
into l_str2, l_str, l_acID, l_queryPath
from ZPB_DC_OBJECTS A, ZPB_ANALYSIS_CYCLES B
where A.ANALYSIS_CYCLE_ID = B.ANALYSIS_CYCLE_ID
AND B.STATUS_CODE <> 'MARKED_FOR_DELETION'
AND B.BUSINESS_AREA_ID = p_baID
AND DATAENTRY_OBJ_NAME = p_search_str;
select SELECTION_NAME, SELECTION_PATH, A.ANALYSIS_CYCLE_ID, B.NAME
into l_str, l_queryPath, l_acID, l_bpName
from ZPB_SOLVE_INPUT_SELECTIONS A, ZPB_ANALYSIS_CYCLES B
where A.ANALYSIS_CYCLE_ID = B.ANALYSIS_CYCLE_ID
AND B.STATUS_CODE <> 'MARKED_FOR_DELETION'
AND B.BUSINESS_AREA_ID = p_baID
AND SELECTION_NAME = p_search_str;
select SELECTION_NAME, SELECTION_PATH, A.ANALYSIS_CYCLE_ID, B.NAME
into l_str, l_queryPath, l_acID, l_bpName
from ZPB_SOLVE_OUTPUT_SELECTIONS A, ZPB_ANALYSIS_CYCLES B
where A.ANALYSIS_CYCLE_ID = B.ANALYSIS_CYCLE_ID
AND B.STATUS_CODE <> 'MARKED_FOR_DELETION'
AND B.BUSINESS_AREA_ID = p_baID
AND SELECTION_NAME = p_search_str;
select SOURCE_QUERY_NAME, QUERY_PATH, MEMBER, A.ANALYSIS_CYCLE_ID
into l_str, l_queryPath, l_memberID, l_acID
from ZPB_DATA_INITIALIZATION_DEFS A, ZPB_ANALYSIS_CYCLES B
where A.ANALYSIS_CYCLE_ID = B.ANALYSIS_CYCLE_ID
AND B.STATUS_CODE <> 'MARKED_FOR_DELETION'
AND B.BUSINESS_AREA_ID = p_baID
AND SOURCE_QUERY_NAME = p_search_str;
select TARGET_QUERY_NAME, QUERY_PATH, MEMBER, A.ANALYSIS_CYCLE_ID
into l_str, l_queryPath, l_memberID, l_acID
from ZPB_DATA_INITIALIZATION_DEFS A, ZPB_ANALYSIS_CYCLES B
where A.ANALYSIS_CYCLE_ID = B.ANALYSIS_CYCLE_ID
AND B.STATUS_CODE <> 'MARKED_FOR_DELETION'
AND B.BUSINESS_AREA_ID = p_baID
AND TARGET_QUERY_NAME = p_search_str;
select SUM_SELECTION_NAME, SUM_SELECTION_PATH, DIMENSION_NAME, A.ANALYSIS_CYCLE_ID
into l_str, l_queryPath, l_dim, l_acID
from zpb_cycle_model_dimensions A, ZPB_ANALYSIS_CYCLES B
where A.ANALYSIS_CYCLE_ID = B.ANALYSIS_CYCLE_ID
AND B.STATUS_CODE <> 'MARKED_FOR_DELETION'
AND B.BUSINESS_AREA_ID = p_baID
AND SUM_SELECTION_NAME = p_search_str;
select SUM_SELECTION_NAME, SUM_SELECTION_PATH, MEMBER, DIMENSION, A.ANALYSIS_CYCLE_ID
into l_str, l_queryPath, l_memberID, l_dim, l_acID
from ZPB_LINE_DIMENSIONALITY A, ZPB_ANALYSIS_CYCLES B
where A.ANALYSIS_CYCLE_ID = B.ANALYSIS_CYCLE_ID
AND B.STATUS_CODE <> 'MARKED_FOR_DELETION'
AND B.BUSINESS_AREA_ID = p_baID
AND SUM_SELECTION_NAME = p_search_str;
SELECT status_sql_id
FROM zpb_status_sql
WHERE query_path = p_query_path;
select distinct A.NAME, A.ANALYSIS_CYCLE_ID, B.QUERY_OBJECT_PATH
from ZPB_ANALYSIS_CYCLES A,
ZPB_CYCLE_MODEL_DIMENSIONS B
where B.QUERY_OBJECT_NAME = p_object_name
and B.QUERY_OBJECT_PATH like '%'||p_folder_name
and A.ANALYSIS_CYCLE_ID = B.ANALYSIS_CYCLE_ID
and A.STATUS_CODE <> 'MARKED_FOR_DELETION'
and A.BUSINESS_AREA_ID = p_business_area
and not exists
(select B.ANALYSIS_CYCLE_ID
from ZPB_ANALYSIS_CYCLE_INSTANCES B
where B.INSTANCE_AC_ID = A.ANALYSIS_CYCLE_ID);
select BUSAREA.OBJECT_ID
into l_folder
from BISM_OBJECTS ORCL,
BISM_OBJECTS APPS,
BISM_OBJECTS ZPB,
BISM_OBJECTS BUSAREA
where ORCL.USER_VISIBLE = 'Y'
and APPS.USER_VISIBLE = 'Y'
and ZPB.USER_VISIBLE = 'Y'
and BUSAREA.USER_VISIBLE = 'Y'
and ORCL.OBJECT_NAME = 'oracle'
and APPS.OBJECT_NAME = 'apps'
and ZPB.OBJECT_NAME = 'zpb'
and BUSAREA.OBJECT_NAME = 'BusArea'||p_business_area
and ORCL.FOLDER_ID = HEXTORAW('31')
and APPS.FOLDER_ID = ORCL.OBJECT_ID
and ZPB.FOLDER_ID = APPS.OBJECT_ID
and BUSAREA.FOLDER_ID = ZPB.OBJECT_ID;
select NAME
into l_line_dim
from ZPB_BUSAREA_DIMENSIONS_VL
where VERSION_ID = p_version_id
and DIMENSION_ID = (select MIN(DIMENSION_ID)
from ZPB_BUSAREA_DIMENSIONS
where VERSION_ID = p_version_id
and EPB_LINE_DIMENSION = 'Y');
select A.TASK_NAME, A.TASK_ID, A.ANALYSIS_CYCLE_ID,
nvl (D.INSTANCE_DESCRIPTION, B.NAME) NAME
into l_str2, l_taskID, l_acID, l_str
from ZPB_ANALYSIS_CYCLE_TASKS A,
ZPB_ANALYSIS_CYCLES B,
ZPB_TASK_PARAMETERS C,
ZPB_ANALYSIS_CYCLE_INSTANCES D
where A.ANALYSIS_CYCLE_ID = B.ANALYSIS_CYCLE_ID
and B.STATUS_CODE <> 'MARKED_FOR_DELETION'
and B.BUSINESS_AREA_ID = p_business_area
and A.ANALYSIS_CYCLE_ID = l_num
and A.TASK_ID = C.TASK_ID
and C.NAME = 'QUERY_OBJECT_NAME'
and C.VALUE = each.object_name
and A.ANALYSIS_CYCLE_ID = D.INSTANCE_AC_ID(+);
SELECT value
INTO l_queryPath
FROM ZPB_TASK_PARAMETERS
WHERE name = 'QUERY_OBJECT_PATH'
AND TASK_ID = l_taskID;
SELECT xml
INTO l_xml
FROM BISM_OBJECTS
WHERE OBJECT_NAME = l_str
AND FOLDER_ID = each.FOLDER_ID;
SELECT USER_NAME
INTO l_user
FROM FND_USER
WHERE USER_ID = l_user_id;
SELECT xml
INTO l_xml
FROM BISM_OBJECTS
WHERE OBJECT_NAME = l_str
AND FOLDER_ID = each.FOLDER_ID;
SELECT USER_NAME
INTO l_user
FROM FND_USER
WHERE USER_ID = l_user_id;
SELECT xml
INTO l_xml
FROM BISM_OBJECTS
WHERE OBJECT_NAME = l_str
AND FOLDER_ID = each.FOLDER_ID;
SELECT USER_NAME
INTO l_user
FROM FND_USER
WHERE USER_ID = l_user_id;
each_input_sel.SELECTION_PATH, l_queryErrorType,
each_input_sel.ANALYSIS_CYCLE_ID, p_init_fix);
each_output_sel.SELECTION_PATH, l_queryErrorType,
each_output_sel.ANALYSIS_CYCLE_ID, p_init_fix);
each_sum_sel.SUM_SELECTION_PATH, l_queryErrorType,
each_sum_sel.ANALYSIS_CYCLE_ID, p_init_fix);
SELECT name INTO l_dimName FROM zpb_dimensions_vl
WHERE bus_area_id = p_business_area
AND aw_name = each_sum_sel.DIMENSION;
SELECT name INTO l_dimName FROM zpb_dimensions_vl
WHERE bus_area_id = p_business_area
AND aw_name = each_sum_sel.DIMENSION_NAME;
each_sum_sel.SUM_SELECTION_PATH, l_queryErrorType,
each_sum_sel.ANALYSIS_CYCLE_ID, p_init_fix);
SELECT distinct A.OBJECT_ID,
A.OBJECT_NAME,
B.OBJECT_TYPE_NAME,
C.OBJECT_NAME FOLDER_NAME,
A.FOLDER_ID
FROM BISM_OBJECTS A,
BISM_OBJECT_TYPES B,
BISM_OBJECTS C,
(select C.CONTAINER_ID
from BISM_OBJECTS C,
BISM_OBJECT_TYPES D
where C.OBJECT_TYPE_ID = D.OBJECT_TYPE_ID
and D.OBJECT_TYPE_NAME = 'Selection'
and C.XML like p_search_str
and C.FOLDER_ID IN
(select OBJECT_ID
from BISM_OBJECTS
where OBJECT_TYPE_ID = 100
start with OBJECT_ID = l_folder
connect by FOLDER_ID = prior OBJECT_ID)) D
WHERE A.OBJECT_TYPE_ID = B.OBJECT_TYPE_ID
and A.FOLDER_ID = C.OBJECT_ID
and A.OBJECT_ID = D.CONTAINER_ID
and B.OBJECT_TYPE_NAME <> 'Selection';
select TEMPLATE_NAME, OBJECT_TYPE, A.ANALYSIS_CYCLE_ID, TARGET_OBJ_PATH
into l_str2, l_str, l_acID, l_queryPath
from ZPB_DC_OBJECTS A, ZPB_ANALYSIS_CYCLES B
where A.ANALYSIS_CYCLE_ID = B.ANALYSIS_CYCLE_ID
AND B.STATUS_CODE <> 'MARKED_FOR_DELETION'
AND B.BUSINESS_AREA_ID = p_baID
AND TARGET_OBJ_NAME = p_search_str;
select TEMPLATE_NAME, OBJECT_TYPE, A.ANALYSIS_CYCLE_ID, TARGET_OBJ_PATH
into l_str2, l_str, l_acID, l_queryPath
from ZPB_DC_OBJECTS A, ZPB_ANALYSIS_CYCLES B
where A.ANALYSIS_CYCLE_ID = B.ANALYSIS_CYCLE_ID
AND B.STATUS_CODE <> 'MARKED_FOR_DELETION'
AND B.BUSINESS_AREA_ID = p_baID
AND DATAENTRY_OBJ_NAME = p_search_str;
select SELECTION_NAME, SELECTION_PATH, A.ANALYSIS_CYCLE_ID, DIMENSION, B.NAME
into l_str, l_queryPath, l_acID, l_current_dim, l_bpName
from ZPB_SOLVE_INPUT_SELECTIONS A, ZPB_ANALYSIS_CYCLES B
where A.ANALYSIS_CYCLE_ID = B.ANALYSIS_CYCLE_ID
AND B.STATUS_CODE <> 'MARKED_FOR_DELETION'
AND B.BUSINESS_AREA_ID = p_baID
AND SELECTION_NAME = p_search_str;
select SELECTION_NAME, SELECTION_PATH, A.ANALYSIS_CYCLE_ID, DIMENSION, B.NAME
into l_str, l_queryPath, l_acID, l_current_dim, l_bpName
from ZPB_SOLVE_OUTPUT_SELECTIONS A, ZPB_ANALYSIS_CYCLES B
where A.ANALYSIS_CYCLE_ID = B.ANALYSIS_CYCLE_ID
AND B.STATUS_CODE <> 'MARKED_FOR_DELETION'
AND B.BUSINESS_AREA_ID = p_baID
AND SELECTION_NAME = p_search_str;
select SOURCE_QUERY_NAME, QUERY_PATH, MEMBER, A.ANALYSIS_CYCLE_ID
into l_str, l_queryPath, l_memberID, l_acID
from ZPB_DATA_INITIALIZATION_DEFS A, ZPB_ANALYSIS_CYCLES B
where A.ANALYSIS_CYCLE_ID = B.ANALYSIS_CYCLE_ID
AND B.STATUS_CODE <> 'MARKED_FOR_DELETION'
AND B.BUSINESS_AREA_ID = p_baID
AND SOURCE_QUERY_NAME = p_search_str;
select TARGET_QUERY_NAME, QUERY_PATH, MEMBER, A.ANALYSIS_CYCLE_ID
into l_str, l_queryPath, l_memberID, l_acID
from ZPB_DATA_INITIALIZATION_DEFS A, ZPB_ANALYSIS_CYCLES B
where A.ANALYSIS_CYCLE_ID = B.ANALYSIS_CYCLE_ID
AND B.STATUS_CODE <> 'MARKED_FOR_DELETION'
AND B.BUSINESS_AREA_ID = p_baID
AND TARGET_QUERY_NAME = p_search_str;
select SUM_SELECTION_NAME, SUM_SELECTION_PATH, DIMENSION_NAME, A.ANALYSIS_CYCLE_ID
into l_str, l_queryPath, l_dim, l_acID
from zpb_cycle_model_dimensions A, ZPB_ANALYSIS_CYCLES B
where A.ANALYSIS_CYCLE_ID = B.ANALYSIS_CYCLE_ID
AND B.STATUS_CODE <> 'MARKED_FOR_DELETION'
AND B.BUSINESS_AREA_ID = p_baID
AND SUM_SELECTION_NAME = p_search_str;
select SUM_SELECTION_NAME, SUM_SELECTION_PATH, MEMBER, DIMENSION, A.ANALYSIS_CYCLE_ID
into l_str, l_queryPath, l_memberID, l_dim, l_acID
from ZPB_LINE_DIMENSIONALITY A, ZPB_ANALYSIS_CYCLES B
where A.ANALYSIS_CYCLE_ID = B.ANALYSIS_CYCLE_ID
AND B.STATUS_CODE <> 'MARKED_FOR_DELETION'
AND B.BUSINESS_AREA_ID = p_baID
AND SUM_SELECTION_NAME = p_search_str;
SELECT DIM SOURCE_DIMENSION
INTO l_current_dim
FROM ZPB_COPY_DIM_MEMBERS
WHERE LINE_MEMBER_ID = p_memberID
AND analysis_cycle_id = p_acId
AND SOURCE_NUM_MEMBERS IS NOT NULL;
SELECT DIM TARGET_DIM
INTO l_current_dim
FROM ZPB_COPY_DIM_MEMBERS
WHERE LINE_MEMBER_ID = p_memberID
AND ANALYSIS_CYCLE_ID = p_acId
AND TARGET_NUM_MEMBERS IS NOT NULL;
SELECT status_sql_id
FROM zpb_status_sql
WHERE query_path = p_query_path;
select distinct A.NAME, A.ANALYSIS_CYCLE_ID, B.QUERY_OBJECT_PATH
from ZPB_ANALYSIS_CYCLES A,
ZPB_CYCLE_MODEL_DIMENSIONS B
where B.QUERY_OBJECT_NAME = p_object_name
and B.QUERY_OBJECT_PATH like '%'||p_folder_name
and A.ANALYSIS_CYCLE_ID = B.ANALYSIS_CYCLE_ID
and A.STATUS_CODE <> 'MARKED_FOR_DELETION'
and A.BUSINESS_AREA_ID = p_business_area
and not exists
(select B.ANALYSIS_CYCLE_ID
from ZPB_ANALYSIS_CYCLE_INSTANCES B
where B.INSTANCE_AC_ID = A.ANALYSIS_CYCLE_ID);
select BUSAREA.OBJECT_ID
into l_folder
from BISM_OBJECTS ORCL,
BISM_OBJECTS APPS,
BISM_OBJECTS ZPB,
BISM_OBJECTS BUSAREA
where ORCL.USER_VISIBLE = 'Y'
and APPS.USER_VISIBLE = 'Y'
and ZPB.USER_VISIBLE = 'Y'
and BUSAREA.USER_VISIBLE = 'Y'
and ORCL.OBJECT_NAME = 'oracle'
and APPS.OBJECT_NAME = 'apps'
and ZPB.OBJECT_NAME = 'zpb'
and BUSAREA.OBJECT_NAME = 'BusArea'||p_business_area
and ORCL.FOLDER_ID = HEXTORAW('31')
and APPS.FOLDER_ID = ORCL.OBJECT_ID
and ZPB.FOLDER_ID = APPS.OBJECT_ID
and BUSAREA.FOLDER_ID = ZPB.OBJECT_ID;
SELECT AW_NAME INTO l_rem_def_hier_dim FROM ZPB_DIMENSIONS_VL
WHERE BUS_AREA_ID = p_business_area
AND DEFAULT_HIER = SUBSTR(p_object_id, INSTR(p_object_id, '_', -1, 1) + 1);
SELECT AW_NAME INTO l_line_dimID FROM ZPB_DIMENSIONS_VL
WHERE BUS_AREA_ID = p_business_area
AND DIM_TYPE = 'LINE';
select NAME
into l_line_dim
from ZPB_BUSAREA_DIMENSIONS_VL
where VERSION_ID = p_version_id
and DIMENSION_ID = (select MIN(DIMENSION_ID)
from ZPB_BUSAREA_DIMENSIONS
where VERSION_ID = p_version_id
and EPB_LINE_DIMENSION = 'Y');
select A.TASK_NAME, A.TASK_ID, A.ANALYSIS_CYCLE_ID,
nvl (D.INSTANCE_DESCRIPTION, B.NAME) NAME
into l_str2, l_taskID, l_acID, l_str
from ZPB_ANALYSIS_CYCLE_TASKS A,
ZPB_ANALYSIS_CYCLES B,
ZPB_TASK_PARAMETERS C,
ZPB_ANALYSIS_CYCLE_INSTANCES D
where A.ANALYSIS_CYCLE_ID = B.ANALYSIS_CYCLE_ID
and B.STATUS_CODE <> 'MARKED_FOR_DELETION'
and B.BUSINESS_AREA_ID = p_business_area
and A.ANALYSIS_CYCLE_ID = l_num
and A.TASK_ID = C.TASK_ID
and C.NAME = 'QUERY_OBJECT_NAME'
and C.VALUE = each.object_name
and A.ANALYSIS_CYCLE_ID = D.INSTANCE_AC_ID(+);
SELECT value
INTO l_queryPath
FROM ZPB_TASK_PARAMETERS
WHERE name = 'QUERY_OBJECT_PATH'
AND TASK_ID = l_taskID;
SELECT value
INTO l_current_dim
FROM ZPB_TASK_PARAMETERS
WHERE name = 'EXCEPTION_DIMENSION'
AND TASK_ID = l_taskID;
SELECT xml
INTO l_xml
FROM BISM_OBJECTS
WHERE OBJECT_NAME = l_str
AND FOLDER_ID = each.FOLDER_ID;
SELECT USER_NAME
INTO l_user
FROM FND_USER
WHERE USER_ID = l_user_id;
SELECT xml
INTO l_xml
FROM BISM_OBJECTS
WHERE OBJECT_NAME = l_str
AND FOLDER_ID = each.FOLDER_ID;
SELECT USER_NAME
INTO l_user
FROM FND_USER
WHERE USER_ID = l_user_id;
SELECT xml
INTO l_xml
FROM BISM_OBJECTS
WHERE OBJECT_NAME = l_str
AND FOLDER_ID = each.FOLDER_ID;
SELECT USER_NAME
INTO l_user
FROM FND_USER
WHERE USER_ID = l_user_id;
each_input_sel.SELECTION_PATH, l_queryErrorType,
each_input_sel.ANALYSIS_CYCLE_ID, p_init_fix);
each_output_sel.SELECTION_PATH, l_queryErrorType,
each_output_sel.ANALYSIS_CYCLE_ID, p_init_fix);
SELECT name INTO l_dimName FROM zpb_dimensions_vl
WHERE bus_area_id = p_business_area
AND aw_name = each_sum_sel.DIMENSION;
each_sum_sel.SUM_SELECTION_PATH, l_queryErrorType,
each_sum_sel.ANALYSIS_CYCLE_ID, p_init_fix);
SELECT name INTO l_dimName FROM zpb_dimensions_vl
WHERE bus_area_id = p_business_area
AND aw_name = each_sum_sel.DIMENSION_NAME;
each_sum_sel.SUM_SELECTION_PATH, l_queryErrorType,
each_sum_sel.ANALYSIS_CYCLE_ID, p_init_fix);
select A.DIMENSION_ID,
DECODE(nvl(FDR.FUNC_DIM_SET_NAME, '-99'), '-99',
C.DIMENSION_NAME,FDR.FUNC_DIM_SET_NAME) AS DIMENSION_NAME,
A.LOGICAL_DIM_ID
from ZPB_BUSAREA_DIMENSIONS A,
FEM_DIMENSIONS_VL C,
FEM_FUNC_DIM_SETS_VL FDR
where A.VERSION_ID = l_refr_vers
and A.DIMENSION_ID = C.DIMENSION_ID
AND FDR.FUNC_DIM_SET_ID (+) = A.FUNC_DIM_SET_ID
and A.DIMENSION_ID not in
(select B.DIMENSION_ID
from ZPB_BUSAREA_DIMENSIONS B
where B.VERSION_ID = p_version_id);
select A.DIMENSION_ID
from ZPB_BUSAREA_DIMENSIONS A,
FEM_DIMENSIONS_VL C
where A.VERSION_ID = p_version_id
and A.DIMENSION_ID = C.DIMENSION_ID
and A.DIMENSION_ID not in
(select B.DIMENSION_ID
from ZPB_BUSAREA_DIMENSIONS B
where B.VERSION_ID = l_refr_vers);
select A.DIMENSION_ID,
DECODE(nvl(FDR.FUNC_DIM_SET_NAME, '-99'), '-99',
C.DIMENSION_NAME,FDR.FUNC_DIM_SET_NAME) AS DIMENSION_NAME,
A.EPB_LINE_DIMENSION,
A.LOGICAL_DIM_ID
from ZPB_BUSAREA_DIMENSIONS A,
ZPB_BUSAREA_DIMENSIONS B,
FEM_DIMENSIONS_VL C,
FEM_FUNC_DIM_SETS_VL FDR
where A.DIMENSION_ID = B.DIMENSION_ID
and A.DIMENSION_ID = C.DIMENSION_ID
and A.VERSION_ID = p_version_id
and A.VERSION_ID = l_refr_vers
AND FDR.FUNC_DIM_SET_ID (+) = A.FUNC_DIM_SET_ID
and (A.EPB_LINE_DIMENSION = 'Y' and B.EPB_LINE_DIMENSION = 'N' or
A.EPB_LINE_DIMENSION = 'N' and B.EPB_LINE_DIMENSION = 'Y');
select decode (A.CURRENT_VERSION, 'Y', to_char(A.HIERARCHY_ID),
A.HIERARCHY_ID||'V'||A.VERSION_ID) HIERARCHY_ID,
C.OBJECT_NAME,
E.AW_DIM_PREFIX AS DIMENSION_ID,
D.DIMENSION_ID FEM_DIMENSION_ID, E.DEFAULT_HIERARCHY_ID,
E.LOGICAL_DIM_ID,
nvl(E.FUNC_DIM_SET_ID, -99) AS FUNC_DIM_SET_ID
from table(ZPB_FEM_UTILS_PKG.GET_BUSAREA_HIERARCHIES(l_ba_id,
'R')) A,
FEM_HIERARCHIES D,
FEM_OBJECT_CATALOG_VL C,
ZPB_BUSAREA_DIMENSIONS E
where A.HIERARCHY_ID = C.OBJECT_ID
and A.HIERARCHY_ID = D.HIERARCHY_OBJ_ID
and A.LOGICAL_DIM_ID = E.LOGICAL_DIM_ID
and D.DIMENSION_ID = E.DIMENSION_ID
and E.VERSION_ID = l_refr_vers
and decode (A.CURRENT_VERSION, 'Y', to_char(A.HIERARCHY_ID),
A.HIERARCHY_ID||'V'||A.VERSION_ID) not in
(select decode (B.CURRENT_VERSION, 'Y', to_char(B.HIERARCHY_ID),
B.HIERARCHY_ID||'V'||B.VERSION_ID) HIERARCHY_ID
from table(ZPB_FEM_UTILS_PKG.GET_BUSAREA_HIERARCHIES(l_ba_id,
l_vers_type)) B);
select B.LEVEL_ID,
B.HIERARCHY_ID,
A.DIMENSION_GROUP_NAME,
C.AW_DIM_PREFIX AS DIMENSION_ID,
A.DIMENSION_ID FEM_DIMENSION_ID,
C.LOGICAL_DIM_ID,
nvl(C.FUNC_DIM_SET_ID, -99) AS FUNC_DIM_SET_ID
from FEM_DIMENSION_GRPS_VL A,
ZPB_BUSAREA_LEVELS B,
ZPB_BUSAREA_DIMENSIONS C
where A.DIMENSION_GROUP_ID = B.LEVEL_ID
and B.VERSION_ID = l_refr_vers
and C.VERSION_ID = l_refr_vers
and C.LOGICAL_DIM_ID = B.LOGICAL_DIM_ID
and C.DIMENSION_ID = A.DIMENSION_ID
and B.LEVEL_ID not in
(select C.LEVEL_ID
from ZPB_BUSAREA_LEVELS C
where C.VERSION_ID = p_version_id);
select A.DATASET_ID, A.NAME
from ZPB_BUSAREA_DATASETS_VL A
where A.VERSION_ID = l_refr_vers
and A.DATASET_ID not in
(select B.DATASET_ID
from ZPB_BUSAREA_DATASETS B
where B.VERSION_ID = p_version_id);
select A.ATTRIBUTE_ID, C.ATTRIBUTE_NAME,
D.AW_DIM_PREFIX AS DIMENSION_ID,
C.DIMENSION_ID FEM_DIMENSION_ID,
D.LOGICAL_DIM_ID,
nvl(D.FUNC_DIM_SET_ID, -99) AS FUNC_DIM_SET_ID
from ZPB_BUSAREA_ATTRIBUTES A,
FEM_DIM_ATTRIBUTES_VL C,
ZPB_BUSAREA_DIMENSIONS D
where A.VERSION_ID = l_refr_vers
and A.ATTRIBUTE_ID = C.ATTRIBUTE_ID
and C.DIMENSION_ID = D.DIMENSION_ID
and A.LOGICAL_DIM_ID = D.LOGICAL_DIM_ID
and D.VERSION_ID = l_refr_vers
and A.ATTRIBUTE_ID not in
(select B.ATTRIBUTE_ID
from ZPB_BUSAREA_ATTRIBUTES B
where B.VERSION_ID = p_version_id);
select distinct nvl (C.INSTANCE_DESCRIPTION, A.NAME) NAME
from ZPB_ANALYSIS_CYCLES A, ZPB_CYCLE_DATASETS B,
ZPB_ANALYSIS_CYCLE_INSTANCES C
where A.BUSINESS_AREA_ID = p_ba
and A.STATUS_CODE <> 'MARKED_FOR_DELETION'
and A.ANALYSIS_CYCLE_ID = B.ANALYSIS_CYCLE_ID
and B.DATASET_CODE = p_dataset
and A.ANALYSIS_CYCLE_ID = C.INSTANCE_AC_ID(+);
select A.BUSINESS_AREA_ID, A.VERSION_ID, C.DATA_AW, B.VERSION_TYPE
into l_ba_id, l_refr_vers, l_aw, l_vers_type
from ZPB_BUSAREA_VERSIONS A,
ZPB_BUSAREA_VERSIONS B,
ZPB_BUSINESS_AREAS C
where A.BUSINESS_AREA_ID = B.BUSINESS_AREA_ID
and A.VERSION_TYPE = 'R'
and B.VERSION_ID = p_version_id
and C.BUSINESS_AREA_ID = A.BUSINESS_AREA_ID;
SELECT SHAR_TABLE_ID INTO l_tableID FROM zpb_dimensions
WHERE BUS_AREA_ID = l_ba_id
AND dim_type = 'LINE';
SELECT table_name INTO G_LINE_DIM_TABLE_NAME FROM zpb_tables
WHERE TABLE_ID = l_tableID;
SELECT COLUMN_NAME INTO G_MEMBER_ID_COL FROM ZPB_COLUMNS
WHERE COLUMN_TYPE = 'MEMBER_COLUMN' AND TABLE_ID = l_tableID;
SELECT COLUMN_NAME INTO G_MEMBER_NAME_COL FROM ZPB_COLUMNS
WHERE COLUMN_TYPE = 'LNAME_COLUMN' AND TABLE_ID = l_tableID;
select DIMENSION_NAME
into l_val
from FEM_DIMENSIONS_VL
where DIMENSION_ID = each_hier.FEM_DIMENSION_ID;
select FUNC_DIM_SET_NAME
into l_val
from FEM_FUNC_DIM_SETS_VL
where FUNC_DIM_SET_ID = each_hier.FUNC_DIM_SET_ID;
select DIMENSION_NAME
into l_val
from FEM_DIMENSIONS_VL
where DIMENSION_ID = each_level.FEM_DIMENSION_ID;
select FUNC_DIM_SET_NAME
into l_val
from FEM_FUNC_DIM_SETS_VL
where FUNC_DIM_SET_ID = each_level.FUNC_DIM_SET_ID;
select DIMENSION_NAME
into l_val
from FEM_DIMENSIONS_VL
where DIMENSION_ID = each_attr.FEM_DIMENSION_ID;
select FUNC_DIM_SET_NAME
into l_val
from FEM_FUNC_DIM_SETS_VL
where FUNC_DIM_SET_ID = each_attr.FUNC_DIM_SET_ID;