[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
lAttrNameList.DELETE;
lLovWHereList.DELETE;
lAttrNameList.DELETE;
lLovWhereList.DELETE;
lAttrNameList.DELETE;
lLovWhereList.DELETE;
lAttrNameList.DELETE;
lLovWhereList.DELETE;
SELECT nested_region_code
FROM ak_region_items
WHERE region_code = cpRegionCode
AND nested_region_code IS NOT NULL;
SELECT attribute10 FROM
ak_regions where region_code = cpRegionCode;
SELECT attribute2, attribute_code, display_sequence, attribute4
BULK COLLECT INTO ldef_attr2_table, ldef_attr_code_table, lDef_disp_seq, lLov_where_table
FROM ak_region_items
WHERE region_code = l_region_code -- pRegionCode
AND NODE_QUERY_FLAG = 'Y'
ORDER BY DISPLAY_SEQUENCE;
lAttrNameList.DELETE;
lLovWhereList.DELETE;
lAttrNameList.DELETE;
PROCEDURE bulkDeleteFromSession(
pSessionId in varchar2,
pUserId in varchar2,
pFunctionName in varchar2,
pAttributeNameTbl IN BISVIEWER.t_char
) IS
BEGIN
-- FIX FOR P1 2797318 : kiprabha
-- DELETE ONLY IF SCHEDULE_ID IS NULL
IF pAttributeNameTbl IS NOT NULL AND pAttributeNameTbl.COUNT >0 THEN
FORALL i IN pAttributeNameTbl.FIRST..pAttributeNameTbl.LAST
DELETE FROM bis_user_attributes
WHERE function_name=pFunctionName
AND session_id = pSessionId
AND user_id = pUserId
AND attribute_name = pAttributeNameTbl(i)
AND schedule_id is null ;
END bulkDeleteFromSession;
PROCEDURE bulkInsertIntoSession(
pSessionId in varchar2,
pUserId in varchar2,
pFunctionName in varchar2,
pAttributeNameTbl IN BISVIEWER.t_char,
pDimensionTbl IN BISVIEWER.t_char,
pSessionValueTbl IN BISVIEWER.t_char,
pSessionDescTbl IN BISVIEWER.t_char,
pPeriodDateTbl IN BISVIEWER.t_date
) IS
BEGIN
IF pAttributeNameTbl IS NOT NULL AND pAttributeNameTbl.COUNT >0 THEN
FORALL i IN pAttributeNameTbl.FIRST..pAttributeNameTbl.LAST
insert into bis_user_attributes (USER_ID,
FUNCTION_NAME,
SESSION_ID,
SESSION_VALUE,
SESSION_DESCRIPTION,
ATTRIBUTE_NAME,
DIMENSION,
PERIOD_DATE
)VALUES (
pUserId,
pFunctionName,
pSessionId,
pSessionValueTbl(i),
pSessionDescTbl(i),
pAttributeNameTbl(i),
pDimensionTbl(i),
pPeriodDateTbl(i)
);
END bulkInsertIntoSession;
PROCEDURE bulkDeleteFromPage(
pPAgeId in VARCHAR2,
pUserId in VARCHAR2,
pFunctionName IN VARCHAR2,
pAttributeNameTbl IN BISVIEWER.t_char
) IS
BEGIN
IF pAttributeNameTbl IS NOT NULL AND pAttributeNameTbl.COUNT >0 THEN
FORALL i IN pAttributeNameTbl.FIRST..pAttributeNameTbl.LAST
DELETE FROM bis_user_attributes
WHERE page_id = pPageId
AND user_id = pUserId
AND function_name = pFunctionName
AND attribute_name = pAttributeNameTbl(i);
END bulkDeleteFromPage;
PROCEDURE getDeleteAtttrList(
pAttrNameList IN BISVIEWER.t_char,
pDimension IN VARCHAR2,
xAttrNameList OUT NOCOPY BISVIEWER.t_char
) IS
l_index NUMBER := 1;
END getDeleteAtttrList;
/* Procedure to delete those parameters from session that belong to the same grp and hence have the
same attribute name as the attribute name present in the attrNameList table.*/
PROCEDURE deletePageForGroup(
pUserId in varchar2,
pFunctionName in varchar2,
pPageId in varchar2,
pAttrNameList IN BISVIEWER.t_char,
pDimension IN VARCHAR2
) IS
lAttrNameList BISVIEWER.t_char;
getDeleteAtttrList(
pAttrNameList => pAttrNameList,
pDimension => pDimension ,
xAttrNameList => lAttrNameList
);
bulkDeleteFromPage(
pPageId => pPageId,
pUserId => pUserId,
pFunctionName => pFunctionName,
pAttributeNameTbl => lAttrNameList
) ;
END deletePageForGroup;
/* Procedure to delete those parameters from session that belong to the same grp and hence have the
same attribute name as the attribute name present in the attrNameList table.*/
PROCEDURE deleteSessionForGroup(
pSessionId in varchar2,
pUserId in varchar2,
pFunctionName in varchar2,
pAttrNameList IN BISVIEWER.t_char,
pDimension IN VARCHAR2
) IS
lAttrNameList BISVIEWER.t_char;
getDeleteAtttrList(
pAttrNameList => pAttrNameList,
pDimension => pDimension ,
xAttrNameList => lAttrNameList
);
bulkDeleteFromSession(
pSessionId => pSessionId,
pUserId => pUserId,
pFunctionName => pFunctionName,
pAttributeNameTbl => lAttrNameList
) ;
END deleteSessionForGroup;
/* This will sent the parameters to be deleted and corresponding parameters to be inserted for the same group as
pAttributeNameTbl being sent in. - these can then be deleted or inserted into session/page etc.*/
PROCEDURE getDeleteAndInsertTables(
pUserId in varchar2,
pAttributeNameTbl IN BISVIEWER.t_char,
pDimensionTbl IN BISVIEWER.t_char,
pSessionValueTbl IN BISVIEWER.t_char,
pSessionDescTbl IN BISVIEWER.t_char,
pPeriodDateTbl IN BISVIEWER.t_date,
pParameterGroup IN parameter_group_tbl_type,
pIncludeViewBy IN BOOLEAN DEFAULT FALSE,
pIncludeBusinessPlan IN BOOLEAN DEFAULT FALSE,
pIncludePrevAsOfDate IN BOOLEAN DEFAULT FALSE,
xAttrNameForInsert OUT NOCOPY BISVIEWER.t_char,
xDimensionForInsert OUT NOCOPY BISVIEWER.t_char,
xSessValueForInsert OUT NOCOPY BISVIEWER.t_char,
xSessDescForInsert OUT NOCOPY BISVIEWER.t_char,
xPeriodDateForInsert OUT NOCOPY BISVIEWER.t_date,
xAttrNameForDelete OUT NOCOPY BISVIEWER.t_char
) IS
lAttrNameList BISVIEWER.t_char;
insert_index NUMBER :=1;
delete_index NUMBER :=1;
xAttrNameForDelete(delete_index) := lAttrNameList(j);
delete_index:= delete_index+1;
xAttrNameForDelete(delete_index) := lAttrNameList(j)||'_TO';
delete_index:= delete_index+1;
xAttrNameForDelete(delete_index) := lAttrNameList(j)||'_FROM';
delete_index:= delete_index+1;
xAttrNameForInsert(insert_index) := pAttributeNameTbl(i) ;
xDimensionForInsert(insert_index) := pDimensionTbl(i) ;
xSessValueForInsert(insert_index) := pSessionValueTbl(i);
xSessDescForInsert(insert_index) := pSessionDescTbl(i);
xPeriodDateForInsert(insert_index) := pPeriodDateTbl(i);
insert_index := insert_index+1;
xAttrNameForDelete(delete_index) := pAttributeNameTbl(i) ;
delete_index := delete_index+1;
xAttrNameForInsert(insert_index) := pAttributeNameTbl(i) ;
xDimensionForInsert(insert_index) := pDimensionTbl(i) ;
xSessValueForInsert(insert_index) := pSessionValueTbl(i);
xSessDescForInsert(insert_index) := pSessionDescTbl(i);
xPeriodDateForInsert(insert_index) := pPeriodDateTbl(i);
insert_index := insert_index+1;
xAttrNameForDelete(delete_index) := pAttributeNameTbl(i) ;
delete_index := delete_index+1;
xAttrNameForInsert(insert_index) := pAttributeNameTbl(i) ;
xDimensionForInsert(insert_index) := pDimensionTbl(i) ;
xSessValueForInsert(insert_index) := pSessionValueTbl(i);
xSessDescForInsert(insert_index) := pSessionDescTbl(i);
xPeriodDateForInsert(insert_index) := pPeriodDateTbl(i);
insert_index := insert_index+1;
END getDeleteAndInsertTables;
/* This will delete any parameters alreadyPresent for the same group as
pAttributeNameTbl being sent in. - should use the getDeleteAndInsertTables later
and donly do the bulk delet and insert into session*/
PROCEDURE deleteAndInsertIntoSession(
pSessionId in varchar2,
pUserId in varchar2,
pFunctionName in varchar2,
pAttributeNameTbl IN BISVIEWER.t_char,
pDimensionTbl IN BISVIEWER.t_char,
pSessionValueTbl IN BISVIEWER.t_char,
pSessionDescTbl IN BISVIEWER.t_char,
pPeriodDateTbl IN BISVIEWER.t_date,
pParameterGroup IN parameter_group_tbl_type,
pIncludeViewBy IN BOOLEAN DEFAULT FALSE,
pIncludeBusinessPlan IN BOOLEAN DEFAULT FALSE,
pIncludePrevAsOfDate IN BOOLEAN DEFAULT FALSE
) IS
lAttrNameList BISVIEWER.t_char;
insert_index NUMBER :=1;
lAttrNameForInsert BISVIEWER.t_char;
lDimensionForInsert BISVIEWER.t_char;
lSessValueForInsert BISVIEWER.t_char;
lSessDescForInsert BISVIEWER.t_char;
lPeriodDateForInsert BISVIEWER.t_date;
delete_index NUMBER :=1;
lAttrNameForDelete BISVIEWER.t_char;
lAttrNameForDelete(delete_index) := pAttributeNameTbl(i);
delete_index:= delete_index+1;
lAttrNameForDelete(delete_index) := lAttrNameList(j);
delete_index:= delete_index+1;
lAttrNameForDelete(delete_index) := lAttrNameList(j)||'_TO';
delete_index:= delete_index+1;
lAttrNameForDelete(delete_index) := lAttrNameList(j)||'_FROM';
delete_index:= delete_index+1;
lAttrNameForInsert(insert_index) := pAttributeNameTbl(i) ;
lDimensionForInsert(insert_index) := pDimensionTbl(i) ;
lSessValueForInsert(insert_index) := pSessionValueTbl(i);
lSessDescForInsert(insert_index) := pSessionDescTbl(i);
lPeriodDateForInsert(insert_index) := pPeriodDateTbl(i);
insert_index := insert_index+1;
lAttrNameForDelete(delete_index) := pAttributeNameTbl(i) ;
delete_index := delete_index+1;
lAttrNameForInsert(insert_index) := pAttributeNameTbl(i) ;
lDimensionForInsert(insert_index) := pDimensionTbl(i) ;
lSessValueForInsert(insert_index) := pSessionValueTbl(i);
lSessDescForInsert(insert_index) := pSessionDescTbl(i);
lPeriodDateForInsert(insert_index) := pPeriodDateTbl(i);
insert_index := insert_index+1;
lAttrNameForDelete(delete_index) := pAttributeNameTbl(i) ;
delete_index := delete_index+1;
lAttrNameForInsert(insert_index) := pAttributeNameTbl(i) ;
lDimensionForInsert(insert_index) := pDimensionTbl(i) ;
lSessValueForInsert(insert_index) := pSessionValueTbl(i);
lSessDescForInsert(insert_index) := pSessionDescTbl(i);
lPeriodDateForInsert(insert_index) := pPeriodDateTbl(i);
insert_index := insert_index+1;
IF (lAttrNameForDelete IS NOT NULL AND lAttrNameForDelete.COUNT >0) THEN
bulkDeleteFromSession(
pSessionId => pSessionId,
pUserId => pUserId,
pFunctionName => pFunctionName,
pAttributeNameTbl => lAttrNameForDelete
) ;
IF (lAttrNameForInsert IS NOT NULL AND lAttrNameForInsert.COUNT >0) THEN
--insert all the records now
bulkInsertIntoSession(
pSessionId => pSessionId,
pUserId => pUserId,
pFunctionName => pFunctionName,
pAttributeNameTbl => lAttrNameForInsert,
pDimensionTbl => lDimensionForInsert,
pSessionValueTbl => lSessValueForInsert,
pSessionDescTbl => lSessDescForInsert,
pPeriodDateTbl => lPeriodDateForInsert
);
END deleteAndInsertIntoSession;
SELECT attribute_name, dimension, session_value, session_description, period_date
FROM bis_user_attributes
WHERE schedule_id=pScheduleId;
deleteAndInsertIntoSession(
pSessionId => pSessionId,
pUserId => pUserId,
pFunctionName => pFunctionName,
pAttributeNameTbl => vAttributeCodeTable,
pDimensionTbl => vDimensionTable,
pSessionValueTbl => vSessionValueTable,
pSessionDescTbl => vSessionDescTable,
pPeriodDateTbl => vPeriodDateTable,
pParameterGroup => pParameterGroup,
pIncludePrevAsOfDate => TRUE
);
SELECT attribute_name, dimension, session_value, session_description, period_date
FROM bis_user_attributes
WHERE function_name =pPreFunctionName
AND session_id = pSessionId
AND user_id = pUserId;
deleteAndInsertIntoSession(
pSessionId => pSessionId,
pUserId => pUserId,
pFunctionName => pFunctionName,
pAttributeNameTbl => vAttributeCodeTable,
pDimensionTbl => vDimensionTable,
pSessionValueTbl => vSessionValueTable,
pSessionDescTbl => vSessionDescTable,
pPeriodDateTbl => vPeriodDateTable,
pParameterGroup => pParameterGroup,
pIncludePrevAsOfDate => TRUE,
pIncludeBusinessPlan => TRUE
);
SELECT attribute_name, dimension, session_value, session_description, period_date
FROM bis_user_attributes
WHERE page_id =pPageId
AND user_id = pUserId;
deleteAndInsertIntoSession(
pSessionId => pSessionId,
pUserId => pUserId,
pFunctionName => pFunctionName,
pAttributeNameTbl => vAttributeCodeTable,
pDimensionTbl => vDimensionTable,
pSessionValueTbl => vSessionValueTable,
pSessionDescTbl => vSessionDescTable,
pPeriodDateTbl => vPeriodDateTable,
pParameterGroup => pParameterGroup,
pIncludeViewBy => FALSE ,
pIncludePrevAsOfDate => TRUE
);
SELECT attribute_name, dimension, default_value, default_description, period_date
FROM bis_user_attributes
WHERE function_name = pFunctionName
AND user_id = pUserId
AND session_id IS NULL
AND session_description = 'NULL';
deleteAndInsertIntoSession(
pSessionId => pSessionId,
pUserId => pUserId,
pFunctionName => pFunctionName,
pAttributeNameTbl => vAttributeCodeTable,
pDimensionTbl => vDimensionTable,
pSessionValueTbl => vSessionValueTable,
pSessionDescTbl => vSessionDescTable,
pPeriodDateTbl => vPeriodDateTable,
pParameterGroup => pParameterGroup,
pIncludeViewBy => TRUE,
pIncludeBusinessPlan => TRUE
);
l_attr_code.DELETE(i);
l_attr_value.DELETE(i);
l_attr_code.DELETE(i);
l_attr_value.DELETE(i);
l_attr_code.DELETE(i);
l_attr_value.DELETE(i);
SELECT attribute_name INTO l_view_by
FROM bis_user_attributes
WHERE function_name=pFunctionName
AND session_id=pSessionId
AND user_id = pUserId
AND attribute_name ='VIEW_BY';
l_attr_code.DELETE(i);
l_attr_value.DELETE(i);
SELECT parameters INTO lParameters
FROM fnd_form_functions
WHERE function_name=pFunctionName;
INSERT INTO BIS_USER_ATTRIBUTES (USER_ID,
FUNCTION_NAME,
SESSION_ID,
SESSION_VALUE,
SESSION_DESCRIPTION,
ATTRIBUTE_NAME,
DIMENSION,
PERIOD_DATE,
OPERATOR)
SELECT pUserId,
pFunctionName,
pSessionId,
DEFAULT_VALUE,
DEFAULT_DESCRIPTION,
ATTRIBUTE_NAME,
DIMENSION,
PERIOD_DATE,
OPERATOR
FROM BIS_USER_ATTRIBUTES
WHERE function_name = pFunctionName
AND user_id = pUserId
AND session_id IS NULL
AND session_description = 'NULL'
AND nvl(substr(attribute_name,1,instr(attribute_name,'+')-1), attribute_name) NOT IN (
SELECT nvl(substr(attribute_name,1,instr(attribute_name,'+')-1), attribute_name)
FROM bis_user_attributes
WHERE function_name=pFunctionName
AND user_id=pUserId
AND session_id= pSessionId
AND schedule_id IS NULL
);
DELETE BIS_USER_ATTRIBUTES
WHERE session_id is null and
function_name = pFunctionName
AND user_id= pUserId;
INSERT INTO BIS_USER_ATTRIBUTES (USER_ID,
FUNCTION_NAME,
ATTRIBUTE_NAME,
SESSION_DESCRIPTION,
DEFAULT_VALUE,
DEFAULT_DESCRIPTION,
PERIOD_DATE,
DIMENSION,
OPERATOR)
SELECT pUserId,
pFunctionName,
ATTRIBUTE_NAME,
'NULL',
SESSION_VALUE,
SESSION_DESCRIPTION,
PERIOD_DATE,
DIMENSION,
OPERATOR
FROM BIS_USER_ATTRIBUTES
WHERE session_id = pSessionId
AND function_name = pFunctionName
AND user_id = pUserId
AND schedule_id IS NULL;
select attribute17 from bis_ak_region_item_extension ext
where ext.region_code=p_user_session_rec.region_code
and attribute_code =
( select attribute_code from ak_region_items akItems
where akItems.region_code= ext.region_code
and nvl(akItems.attribute2, akItems.attribute_code) = p_parameter_rec.parameter_name);
SELECT name
INTO p_parameter_rec.parameter_description
FROM BISBV_BUSINESS_PLANS
WHERE plan_id = p_parameter_rec.parameter_value;
SELECT plan_id
INTO p_parameter_rec.parameter_value
FROM BISBV_BUSINESS_PLANS
WHERE name = p_parameter_rec.parameter_description;
SELECT attribute14
FROM ak_regions
WHERE region_code = pRegionCode;
SELECT attribute24
FROM bis_ak_region_item_extension
WHERE region_code=pRegionCode AND attribute_code=pAttributeCode;
select vl.attribute15, -- level view name
substr(vl.attribute2, instr(vl.attribute2,'+')+1), -- dimension level
nvl(r.region_object_type, 'OLTP') -- level type
from ak_region_items vl, ak_regions r
where nvl(vl.attribute2,vl.attribute_code) = rtrim(cp_parameter_name)
and vl.region_code = rtrim(cp_region_code)
and vl.region_code = r.region_code;
BIS_PMF_GET_DIMLEVELS_PUB.GET_DIMLEVEL_SELECT_STRING(
p_DimLevelShortName => l_dimension_level
, p_bis_source => l_level_type
, x_Select_String => l_sql_stmnt
, x_table_name => l_view_name
, x_id_name => l_id_name
, x_value_name => l_value_name
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
);
INSERT INTO BIS_USER_ATTRIBUTES (user_id, function_name,
attribute_name, session_description,
default_value, default_description,
period_date, dimension, operator,
creation_date, created_by,
last_update_Date, last_updated_by)
VALUES (p_user_session_rec.user_id, p_user_session_rec.function_name,
l_parameter_name, 'NULL',
l_parameter_value, l_parameter_description,
p_parameter_rec.period_date, l_dimension,
p_parameter_rec.operator,
sysdate, -1, sysdate, -1);
INSERT INTO BIS_USER_ATTRIBUTES (user_id, function_name,
session_id, attribute_name,
session_value, session_description,
period_date, dimension, operator,
creation_date, created_by,
last_update_Date, last_updated_by)
VALUES (p_user_session_rec.user_id, p_user_session_rec.function_name,
p_user_session_rec.session_id, l_parameter_name,
l_parameter_value, l_parameter_description,
p_parameter_rec.period_date, l_dimension,
p_parameter_rec.operator,
sysdate, -1, sysdate, -1);
INSERT INTO BIS_USER_ATTRIBUTES (user_id, page_id, attribute_name, function_name, session_id,
session_value, session_description,
period_date, dimension, operator,
creation_date, created_by,
last_update_Date, last_updated_by)
VALUES (p_user_session_rec.user_id, p_user_session_rec.page_id, l_parameter_name,
p_user_session_rec.function_name, p_user_session_rec.session_id,
l_parameter_value, l_parameter_description,
p_parameter_rec.period_date, l_dimension,
p_parameter_rec.operator,
sysdate, -1, sysdate, -1);
SELECT default_description,
default_value,
period_date,
dimension,
operator
INTO p_parameter_rec.parameter_description,
p_parameter_rec.parameter_value,
p_parameter_rec.period_date,
p_parameter_rec.dimension,
p_parameter_rec.operator
FROM BIS_USER_ATTRIBUTES
WHERE attribute_name = p_parameter_rec.parameter_name
AND function_name = p_user_session_rec.function_name
AND user_id = p_user_session_rec.user_id
AND session_description = 'NULL'
AND session_id IS NULL;
SELECT session_description,
session_value,
period_date,
dimension,
operator
INTO p_parameter_rec.parameter_description,
p_parameter_rec.parameter_value,
p_parameter_rec.period_date,
p_parameter_rec.dimension,
p_parameter_rec.operator
FROM BIS_USER_ATTRIBUTES
WHERE attribute_name = p_parameter_rec.parameter_name
AND function_name = p_user_session_rec.function_name
AND user_id = p_user_session_rec.user_id
AND session_id = p_user_session_rec.session_id
AND schedule_id IS NULL;
SELECT session_description,
session_value,
period_date,
dimension,
operator
INTO p_parameter_rec.parameter_description,
p_parameter_rec.parameter_value,
p_parameter_rec.period_date,
p_parameter_rec.dimension,
p_parameter_rec.operator
FROM BIS_USER_ATTRIBUTES
WHERE attribute_name = p_parameter_rec.parameter_name
AND user_id = p_user_session_rec.user_id
AND page_id = p_user_session_rec.page_id;
SELECT session_description,
session_value,
period_date,
dimension,
operator
INTO p_parameter_rec.parameter_description,
p_parameter_rec.parameter_value,
p_parameter_rec.period_date,
p_parameter_rec.dimension,
p_parameter_rec.operator
FROM BIS_USER_ATTRIBUTES
WHERE attribute_name = p_parameter_rec.parameter_name
AND schedule_id = p_schedule_id
AND attribute_name not in (l_page_dims(1), l_page_dims(2), l_page_dims(3), l_page_dims(4),
l_page_dims(5), l_page_dims(6), l_page_dims(7), l_page_dims(8), l_page_dims(9),
l_page_dims(10), l_page_dims(11), l_page_dims(12), l_page_dims(13), l_page_dims(14),
l_page_dims(15), l_page_dims(16), l_page_dims(17), l_page_dims(18), l_page_dims(19),
l_page_dims(20));
SELECT session_description,
session_value,
period_date,
dimension,
operator
INTO p_parameter_rec.parameter_description,
p_parameter_rec.parameter_value,
p_parameter_rec.period_date,
p_parameter_rec.dimension,
p_parameter_rec.operator
FROM BIS_USER_ATTRIBUTES
WHERE attribute_name = p_parameter_rec.parameter_name
AND schedule_id = p_schedule_id;
SELECT session_description,
session_value,
period_date,
dimension,
operator
INTO p_parameter_rec.parameter_description,
p_parameter_rec.parameter_value,
p_parameter_rec.period_date,
p_parameter_rec.dimension,
p_parameter_rec.operator
FROM BIS_USER_ATTRIBUTES
WHERE attribute_name = p_parameter_rec.parameter_name
AND user_id = p_user_session_Rec.user_id
AND session_id = p_user_session_Rec.session_id
AND function_name = p_user_session_rec.function_name
AND attribute_name not in (l_page_dims(1), l_page_dims(2), l_page_dims(3), l_page_dims(4),
l_page_dims(5), l_page_dims(6), l_page_dims(7), l_page_dims(8), l_page_dims(9), l_page_dims(10),
l_page_dims(11), l_page_dims(12), l_page_dims(13), l_page_dims(14), l_page_dims(15),
l_page_dims(16), l_page_dims(17), l_page_dims(18), l_page_dims(19), l_page_dims(20));
SELECT session_description,
session_value,
period_date,
dimension,
operator
INTO p_parameter_rec.parameter_description,
p_parameter_rec.parameter_value,
p_parameter_rec.period_date,
p_parameter_rec.dimension,
p_parameter_rec.operator
FROM BIS_USER_ATTRIBUTES
WHERE attribute_name = p_parameter_rec.parameter_name
AND user_id = p_user_session_Rec.user_id
AND session_id = p_user_session_Rec.session_id
AND function_name = p_user_session_rec.function_name;
SELECT session_description,
session_value,
period_date,
dimension,
operator
INTO p_parameter_rec.parameter_description,
p_parameter_rec.parameter_value,
p_parameter_rec.period_date,
p_parameter_rec.dimension,
p_parameter_rec.operator
FROM BIS_USER_ATTRIBUTES
WHERE attribute_name = p_parameter_rec.parameter_name
AND user_id = p_user_session_Rec.user_id
AND session_id = p_user_session_Rec.session_id
AND function_name = p_user_session_rec.function_name;
SELECT session_description,
session_value,
period_date,
dimension,
operator
INTO p_parameter_rec.parameter_description,
p_parameter_rec.parameter_value,
p_parameter_rec.period_date,
p_parameter_rec.dimension,
p_parameter_rec.operator
FROM BIS_USER_ATTRIBUTES
WHERE attribute_name = p_parameter_rec.parameter_name
AND user_id = p_user_session_rec.user_id
AND page_id = p_user_session_rec.page_id
AND attribute_name not in (l_user_dims(1), l_user_dims(2), l_user_dims(3), l_user_dims(4),
l_user_dims(5), l_user_dims(6), l_user_dims(7), l_user_dims(8), l_user_dims(9),
l_user_dims(10), l_user_dims(11), l_user_dims(12), l_user_dims(13), l_user_dims(14),
l_user_dims(15), l_user_dims(16), l_user_dims(17), l_user_dims(18), l_user_dims(19), l_user_dims(20));
SELECT session_description,
session_value,
period_date,
dimension,
operator
INTO p_parameter_rec.parameter_description,
p_parameter_rec.parameter_value,
p_parameter_rec.period_date,
p_parameter_rec.dimension,
p_parameter_rec.operator
FROM BIS_USER_ATTRIBUTES
WHERE attribute_name = p_parameter_rec.parameter_name
AND user_id = p_user_session_rec.user_id
AND page_id = p_user_session_rec.page_id;
SELECT session_description,
session_value,
period_date,
dimension,
operator
INTO p_parameter_rec.parameter_description,
p_parameter_rec.parameter_value,
p_parameter_rec.period_date,
p_parameter_rec.dimension,
p_parameter_rec.operator
FROM BIS_USER_ATTRIBUTES
WHERE attribute_name = p_parameter_rec.parameter_name
AND schedule_id = p_schedule_id;
PROCEDURE DELETE_PARAMETER
(p_user_session_rec IN BIS_PMV_SESSION_PVT.session_rec_type
,p_parameter_name IN VARCHAR2
,p_schedule_option IN VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
BEGIN
IF p_schedule_option = 'NULL' THEN
DELETE FROM BIS_USER_ATTRIBUTES
WHERE user_id = p_user_session_rec.user_id
AND session_id = p_user_session_rec.session_id
AND function_name = p_user_session_rec.function_name
AND attribute_name = p_parameter_name
AND schedule_id IS NULL;
DELETE FROM BIS_USER_ATTRIBUTES
WHERE user_id = p_user_session_rec.user_id
AND session_id = p_user_session_rec.session_id
AND function_name = p_user_session_rec.function_name
AND attribute_name = p_parameter_name
AND schedule_id IS NOT NULL;
DELETE FROM BIS_USER_ATTRIBUTES
WHERE user_id = p_user_session_rec.user_id
AND session_id = p_user_session_rec.session_id
AND function_name = p_user_session_rec.function_name
AND attribute_name = p_parameter_name;
END DELETE_PARAMETER;
PROCEDURE DELETE_SCHEDULE_PARAMETER
(p_parameter_name IN VARCHAR2
,p_schedule_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
BEGIN
DELETE FROM BIS_USER_ATTRIBUTES
WHERE attribute_name = p_parameter_name
AND schedule_id = p_schedule_id;
END DELETE_SCHEDULE_PARAMETER;
select attribute4
from ak_region_items
where region_code = cpRegionCode
and attribute2 = cpAttr2;
SELECT attribute_name,
session_description,
session_value,
period_date,
dimension,
operator
FROM BIS_USER_ATTRIBUTES
WHERE user_id = p_user_session_rec.user_id
AND page_id = p_user_session_rec.page_id;
SELECT attribute_name,
session_description,
session_value,
period_date,
dimension,
operator
FROM BIS_USER_ATTRIBUTES
WHERE schedule_id = p_schedule_id
AND ((dimension IS NULL AND attribute_name not in (SELECT nvl(attribute_name,'-11')
FROM BIS_USER_ATTRIBUTES
WHERE page_id=p_user_session_rec.page_id
AND user_id=p_user_session_rec.user_id))
OR (dimension IS NOT NULL AND dimension not in (SELECT nvl(dimension,'-11')
FROM BIS_USER_ATTRIBUTES
WHERE page_id=p_user_session_rec.page_id
AND user_id=p_user_session_rec.user_id)));
SELECT attribute_name,
session_description,
session_value,
period_date,
dimension,
operator
FROM BIS_USER_ATTRIBUTES
WHERE user_id = p_user_session_rec.user_id
AND session_id = p_user_session_rec.session_id
AND function_name = p_user_session_rec.function_name
AND ((dimension IS NULL AND attribute_name not in (SELECT nvl(attribute_name,'-11')
FROM BIS_USER_ATTRIBUTES
WHERE page_id=p_user_session_rec.page_id
AND user_id=p_user_session_rec.user_id))
OR (dimension IS NOT NULL AND dimension not in (SELECT nvl(dimension, '-11')
FROM BIS_USER_ATTRIBUTES
WHERE page_id=p_user_session_rec.page_id
AND user_id=p_user_session_rec.user_id)));
SELECT attribute_name,
session_description,
session_value,
period_date,
dimension,
operator
FROM BIS_USER_ATTRIBUTES
WHERE user_id = p_user_session_rec.user_id
AND session_id = p_user_session_rec.session_id
AND function_name = p_user_session_rec.function_name;
SELECT attribute_name,
session_description,
session_value,
period_date,
dimension,
operator
FROM BIS_USER_ATTRIBUTES
WHERE user_id = p_user_session_rec.user_id
AND page_id = p_user_session_rec.page_id
AND ((dimension IS NULL AND attribute_name not in (SELECT nvl(attribute_name, '-11')
FROM BIS_USER_ATTRIBUTES
WHERE user_id = p_user_session_rec.user_id
AND session_id = p_user_session_rec.session_id
AND function_name = p_user_session_rec.function_name))
OR (dimension IS NOT NULL AND dimension not in (SELECT nvl(dimension,'-11')
FROM BIS_USER_ATTRIBUTES
WHERE user_id = p_user_session_rec.user_id
AND session_id = p_user_session_rec.session_id
AND function_name = p_user_session_rec.function_name)));
SELECT attribute_name, session_description,
session_value,
period_date,
dimension,
operator
INTO l_parameter_rec.parameter_name,
l_parameter_rec.parameter_description,
l_parameter_rec.parameter_value,
l_parameter_rec.period_date,
l_parameter_rec.dimension,
l_parameter_rec.operator
FROM BIS_USER_ATTRIBUTES
WHERE dimension = l_dimension
AND user_id = p_user_session_rec.user_id
AND page_id = p_user_session_rec.page_id;
SELECT attribute_name
FROM BIS_USER_ATTRIBUTES
WHERE schedule_id = p_schedule_id;
SELECT attribute_name,
session_description,
session_value,
period_date,
dimension,
operator
FROM BIS_USER_ATTRIBUTES
WHERE schedule_id = p_schedule_id;
SELECT attribute_name
FROM BIS_USER_ATTRIBUTES
WHERE user_id = p_user_session_rec.user_id
AND session_id = p_user_session_rec.session_id
AND function_name = p_user_session_rec.function_name;
SELECT attribute_name,
session_description,
session_value,
period_date,
dimension,
operator
FROM BIS_USER_ATTRIBUTES
WHERE function_name = p_user_session_rec.function_name
AND user_id = p_user_session_rec.user_id
AND session_id = p_user_session_rec.session_id
AND schedule_id IS NULL;
SELECT attribute_name
FROM BIS_USER_ATTRIBUTES
WHERE user_id = p_user_session_rec.user_id
AND session_id = p_user_session_rec.session_id
AND function_name = p_user_session_rec.function_name;
SELECT attribute_name,
default_description,
default_value,
period_date,
dimension,
operator
FROM BIS_USER_ATTRIBUTES
WHERE function_name = p_user_session_rec.function_name
AND user_id = p_user_session_rec.user_id
AND session_description = 'NULL'
AND session_id IS NULL;
PROCEDURE DELETE_SESSION_PARAMETERS
(p_user_session_rec IN BIS_PMV_SESSION_PVT.session_rec_type
,p_schedule_option IN VARCHAR2
,x_return_Status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
BEGIN
IF p_schedule_option = 'NULL' THEN
DELETE FROM BIS_USER_ATTRIBUTES
WHERE user_id = p_user_session_rec.user_id
AND session_id = p_user_session_rec.session_id
AND function_name = p_user_session_rec.function_name
AND schedule_id IS NULL;
DELETE FROM BIS_USER_ATTRIBUTES
WHERE user_id = p_user_session_rec.user_id
AND session_id = p_user_session_rec.session_id
AND function_name = p_user_session_rec.function_name
AND schedule_id IS NOT NULL;
DELETE FROM BIS_USER_ATTRIBUTES
WHERE user_id = p_user_session_rec.user_id
AND session_id = p_user_session_rec.session_id
AND function_name = p_user_session_rec.function_name;
END DELETE_SESSION_PARAMETERS;
PROCEDURE DELETE_PAGE_PARAMETERS
(p_user_session_rec IN BIS_PMV_SESSION_PVT.session_rec_type
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
)
IS
BEGIN
DELETE BIS_USER_ATTRIBUTES
WHERE user_id = p_user_session_rec.user_id
AND page_id = p_user_session_rec.page_id;
END DELETE_PAGE_PARAMETERS;
PROCEDURE DELETE_DEFAULT_PARAMETERS
(p_user_session_rec IN BIS_PMV_SESSION_PVT.session_rec_type
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
)
IS
BEGIN
DELETE BIS_USER_ATTRIBUTES
WHERE session_id is null and
function_name = p_user_session_rec.function_name
AND user_id=p_user_session_rec.user_id;
END DELETE_DEFAULT_PARAMETERS;
PROCEDURE DELETE_SCHEDULE_PARAMETERS
(p_schedule_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
BEGIN
DELETE FROM BIS_USER_ATTRIBUTES
WHERE schedule_id = p_schedule_id;
END DELETE_SCHEDULE_PARAMETERS;
select attribute15 level_values_view_name, substr(attribute2, 1, instr(attribute2, '+')-1) dimension ,substr(attribute2,instr(attribute2, '+')+1) dimension_level
from ak_region_items_vl
where nvl(attribute2,attribute_code) = rtrim(cp_parameter_name)
and region_code = rtrim(cp_region_code);
select vl.attribute15 level_values_view_name, substr(vl.attribute2, instr(vl.attribute2, '+')+1) dimension_level
from ak_region_items_vl vl, ak_regions r
where nvl(vl.attribute2,vl.attribute_code) LIKE rtrim(cp_parameter_name)
and vl.region_code = rtrim(cp_region_code)
and vl.region_code = r.region_code
and lower(r.region_object_type) = 'edw' ;
select vl.attribute15, -- level view name
substr(vl.attribute2, 1, instr(vl.attribute2,'+')-1), -- dimension
substr(vl.attribute2, instr(vl.attribute2,'+')+1), -- dimension level
nvl(r.region_object_type, 'OLTP'), -- level type
vl.attribute4 -- lov where clause
from ak_region_items vl, ak_regions r
where nvl(vl.attribute2,vl.attribute_code) = rtrim(cp_parameter_name)
and vl.region_code = rtrim(cp_region_code)
and vl.region_code = r.region_code;
v_sql_stmnt := 'select '||v_id_name||', '||v_value_name||' from '||v_level_values_view_name;
BIS_PMF_GET_DIMLEVELS_PUB.GET_DIMLEVEL_SELECT_STRING(
p_DimLevelShortName => vDimensionLevel
,p_bis_source => vLevelType
,x_Select_String => v_sql_stmnt
,x_table_name=> v_table_name
,x_id_name=> v_id_name
,x_value_name=> v_value_name
,x_return_status=> v_return_status
,x_msg_count=> v_msg_count
,x_msg_data=> v_msg_data
);
v_sql_stmnt := 'select '||v_id_name||', '||v_value_name||' from '||
v_edw_level_values_view_name;
BIS_PMF_GET_DIMLEVELS_PUB.GET_DIMLEVEL_SELECT_STRING(
p_DimLevelShortName => vEdwDimensionLevel
,p_bis_source => 'EDW'
,x_Select_String => v_sql_stmnt
,x_table_name=> v_table_name
,x_id_name=> v_id_name
,x_value_name=> v_value_name
,x_return_status=> v_return_status
,x_msg_count=> v_msg_count
,x_msg_data=> v_msg_data
);
v_sql_stmnt := 'select '||v_id_name||', '||v_value_name||' from '||
v_level_values_view_name;
BIS_PMF_GET_DIMLEVELS_PUB.GET_DIMLEVEL_SELECT_STRING(
p_DimLevelShortName => vDimensionLevel
,p_bis_source => 'OLTP'
,x_Select_String => v_sql_stmnt
,x_table_name=> v_table_name
,x_id_name=> v_id_name
,x_value_name=> v_value_name
,x_return_status=> v_return_status
,x_msg_count=> v_msg_count
,x_msg_data=> v_msg_data
);
end if; -- end of constructing the select string
select attribute15 level_values_view_name, substr(attribute2,instr(attribute2, '+')+1) dimension_level
from ak_region_items_vl
where nvl(attribute2,attribute_code) = rtrim(cp_parameter_name)
and region_code = rtrim(cp_region_code);
select vl.attribute15 edw_level_values_view_name, substr(vl.attribute2, instr(vl.attribute2, '+')+1) dimension_level
from ak_region_items_vl vl, ak_regions r
where nvl(vl.attribute2,vl.attribute_code) LIKE rtrim(cp_parameter_name)
and vl.region_code = rtrim(cp_region_code)
and vl.region_code = r.region_code
and lower(r.region_object_type) = 'edw';
select vl.attribute15, -- view name
substr(vl.attribute2, instr(vl.attribute2, '+')+1), -- dimension level
nvl(r.region_object_type, 'OLTP') -- level type
from ak_region_items vl, ak_regions r
where nvl(vl.attribute2,vl.attribute_code) = rtrim(cp_parameter_name)
and vl.region_code = rtrim(cp_region_code)
and vl.region_code = r.region_code;
select nested_region_code
from ak_region_items
where region_code = cp_region_code
and nested_region_code is not null;
v_sql_stmnt := 'select distinct '||v_id_name||', '||v_value_name;
BIS_PMF_GET_DIMLEVELS_PUB.GET_DIMLEVEL_SELECT_STRING(
p_DimLevelShortName => vEdwDimensionLevel
,p_bis_source => 'EDW'
,x_Select_String => v_sql_stmnt
,x_table_name=> v_table_name
,x_id_name=> v_id_name
,x_value_name=> v_value_name
,x_return_status=> v_return_status
,x_msg_count=> v_msg_count
,x_msg_data=> v_msg_data
);
v_sql_stmnt := 'select distinct '||v_id_name||', '||v_value_name||' from '||v_table_name;
v_sql_stmnt := v_sql_stmnt || ' where end_date = (select max(end_date) from '
|| v_table_name || ' where ''' ||v_date|| ''' > end_date) ';
v_bind_sql := v_bind_sql || ' where end_date = (select max(end_date) from '
|| v_table_name || ' where :' ||v_bind_count|| ' > end_date) ';
v_sql_stmnt := v_sql_stmnt || ' where start_date = (select min(start_date) from '
|| v_table_name || ' where ''' ||v_date|| ''' < start_date) ';
v_bind_sql := v_bind_sql || ' where start_date = (select min(start_date) from '
|| v_table_name || ' where :' ||v_bind_count|| ' < start_date) ';
v_sql_stmnt := 'select '||v_id_name||', '||v_value_name||', start_date, end_date from '||
v_level_values_view_name;
BIS_PMF_GET_DIMLEVELS_PUB.GET_DIMLEVEL_SELECT_STRING(
p_DimLevelShortName => vDimensionLevel
,p_bis_source => 'OLTP'
,x_Select_String => v_sql_stmnt
,x_table_name=> v_table_name
,x_id_name=> v_id_name
,x_value_name=> v_value_name
,x_return_status=> v_return_status
,x_msg_count=> v_msg_count
,x_msg_data=> v_msg_data
);
v_sql_stmnt := v_sql_stmnt || ' where end_date = (select max(end_date) from '
|| v_table_name || ' where ''' ||v_date|| ''' > end_date) ';
v_bind_sql := v_bind_sql || ' where end_date = (select max(end_date) from '
|| v_table_name || ' where :' ||v_bind_count|| ' > end_date) ';
v_sql_stmnt := v_sql_stmnt || ' where start_date = (select min(start_date) from '
|| v_table_name || ' where ''' ||v_date|| ''' < start_date) ';
v_bind_sql := v_bind_sql || ' where start_date = (select min(start_date) from '
|| v_table_name || ' where :' ||v_bind_count|| ' < start_date) ';
--First delete all the parameters for this session.
FND_MSG_PUB.INITIALIZE;
delete_default_parameters(
p_user_session_rec => l_user_session_rec,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_return_status => x_return_status
);
delete_session_parameters(
p_user_session_rec => l_user_session_Rec,
p_schedule_option => 'NULL',
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_return_status => x_return_status
);
delete_page_parameters(
p_user_session_rec => l_user_session_rec,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_return_status => x_return_status
);
select session_value from bis_user_attributes
where user_id = p_user_id
and schedule_id = p_schedule_id
and attribute_name = p_attribute_name
and function_name = p_function_name;
insert into bis_user_attributes
(user_id, function_name, attribute_name, session_value,
schedule_id,creation_date, created_by,
last_update_Date, last_updated_by)
VALUES
(p_user_id,p_function_name,p_attribute_name,p_graph_file_id,
p_schedule_id,sysdate,-1,sysdate,-1);
select session_value from bis_user_attributes
where user_id = p_user_id
and schedule_id = p_schedule_id
and attribute_name = p_attribute_name
and function_name = p_function_name;
insert into bis_user_attributes
(user_id, function_name, attribute_name, session_value,
schedule_id,creation_date, created_by,
last_update_Date, last_updated_by)
VALUES
(p_user_id,p_function_name,p_attribute_name,p_context_values,
p_schedule_id,sysdate,-1,sysdate,-1);
PROCEDURE bulkInsertIntoPage(
pSessionId IN VARCHAR2,
pPageId in VARCHAR2,
pUserId in VARCHAR2,
pFunctionName IN VARCHAR2,
pAttributeNameTbl IN BISVIEWER.t_char,
pDimensionTbl IN BISVIEWER.t_char,
pSessionValueTbl IN BISVIEWER.t_char,
pSessionDescTbl IN BISVIEWER.t_char,
pPeriodDateTbl IN BISVIEWER.t_date
) IS
BEGIN
IF pAttributeNameTbl IS NOT NULL AND pAttributeNameTbl.COUNT >0 THEN
FORALL i IN pAttributeNameTbl.FIRST..pAttributeNameTbl.LAST
insert into bis_user_attributes (
SESSION_ID,
USER_ID,
FUNCTION_NAME,
PAGE_ID,
SESSION_VALUE,
SESSION_DESCRIPTION,
ATTRIBUTE_NAME,
DIMENSION,
PERIOD_DATE,
LAST_UPDATE_DATE
)VALUES (
pSessionId,
pUserId,
pFunctionName,
pPageId,
pSessionValueTbl(i),
pSessionDescTbl(i),
pAttributeNameTbl(i),
pDimensionTbl(i),
pPeriodDateTbl(i),
SYSDATE
);
END bulkInsertIntoPage;
SELECT parameters
FROM fnd_form_functions
WHERE function_name = pParamFunctionName;
SELECt attribute_name, session_value, session_description, dimension, period_date, operator
FROM bis_user_attributes
WHERE page_id = pPageId
AND user_id = pUserId;
SELECT a.attribute2, c.attribute26
FROM ak_region_items a, bis_ak_region_item_extension c
WHERE a.region_code=pParamRegionCode AND a.attribute_code=c.attribute_code(+) AND a.region_code=c.region_code(+);
SELECT function_name
FROM bis_user_attributes
WHERE page_id=pPageId
AND user_id = pUserId
AND function_name IS NOT NULL
AND rownum < 2;
/* serao - 04/03- added sessionId to be inserted into page level records -
mainly for the as_of_date so that it is not over-ridden by sysdate */
PROCEDURE copyParamtersBetweenPages(
pSessionId IN VARCHAR2,
pFromPageId IN VARCHAR2,
pToPageId IN VARCHAR2,
pUserId IN VARCHAR2,
xParamRegionCode OUT NOCOPY VARCHAR2,
xParamFunctionName OUT NOCOPY VARCHAR2,
xParamGroup OUT NOCOPY parameter_group_tbl_type,
-- nbarik - 04/20/04 - Enhancement 3378782 - Parameter Validation
x_DrillDefaultParameters OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
CURSOR getPageFromParams IS
SELECT attribute_name, dimension, session_value, session_description, period_date
FROM bis_user_attributes
WHERE page_id = pFromPageId
AND user_id = pUserId;
lAttrNameForInsert BISVIEWER.t_char;
lDimensionForInsert BISVIEWER.t_char;
lSessValueForInsert BISVIEWER.t_char;
lSessDescForInsert BISVIEWER.t_char;
lPeriodDateForInsert BISVIEWER.t_date;
lAttrNameForDelete BISVIEWER.t_char;
getDeleteAndInsertTables(
pUserId => pUserId,
pAttributeNameTbl => lAttributeCodeTable,
pDimensionTbl => lDimensionTable,
pSessionValueTbl => lSessionValueTable,
pSessionDescTbl => lSessionDescTable,
pPeriodDateTbl=> lPeriodDateTable,
pParameterGroup => xParamGroup,
pIncludeViewBy => FALSE,
pIncludeBusinessPlan => FALSE,
pIncludePrevAsOfDate => TRUE,
xAttrNameForInsert => lAttrNameForInsert,
xDimensionForInsert => lDimensionForInsert,
xSessValueForInsert => lSessValueForInsert,
xSessDescForInsert => lSessDescForInsert,
xPeriodDateForInsert => lPeriodDateForInsert,
xAttrNameForDelete => lAttrNameForDelete
) ;
IF (lAttrNameForDelete IS NOT NULL AND lAttrNameForDelete.COUNT > 0) THEN
bulkDeleteFromPage(
pPAgeId => pToPageId ,
pUserId => pUserId ,
pFunctionName => xParamFunctionName,
pAttributeNameTbl => lAttrNameForDelete
);
IF (lAttrNameForInsert IS NOT NULL AND lAttrNameForInsert.COUNT > 0) THEN
bulkInsertIntoPage(
pSessionId => pSessionId,
pPageId => pToPageId,
pUserId => pUserId,
pFunctionName => xParamFunctionName,
pAttributeNameTbl => lAttrNameForInsert,
pDimensionTbl => lDimensionForInsert,
pSessionValueTbl => lSessValueForInsert,
pSessionDescTbl => lSessDescForInsert,
pPeriodDateTbl => lPeriodDateForInsert
);
SELECT attribute_name, dimension, session_value, session_description, period_date
FROM bis_user_attributes
WHERE function_name = pFunctionName
AND session_id = pSessionId
AND user_id = pUserId
AND schedule_id IS NULL;
lAttrNameForInsert BISVIEWER.t_char;
lDimensionForInsert BISVIEWER.t_char;
lSessValueForInsert BISVIEWER.t_char;
lSessDescForInsert BISVIEWER.t_char;
lPeriodDateForInsert BISVIEWER.t_date;
lAttrNameForDelete BISVIEWER.t_char;
getDeleteAndInsertTables(
pUserId => pUserId,
pAttributeNameTbl => lAttributeCodeTable,
pDimensionTbl => lDimensionTable,
pSessionValueTbl => lSessionValueTable,
pSessionDescTbl => lSessionDescTable,
pPeriodDateTbl=> lPeriodDateTable,
pParameterGroup => xParamGroup,
pIncludeViewBy => FALSE,
pIncludeBusinessPlan => FALSE,
pIncludePrevAsOfDate => TRUE,
xAttrNameForInsert => lAttrNameForInsert,
xDimensionForInsert => lDimensionForInsert,
xSessValueForInsert => lSessValueForInsert,
xSessDescForInsert => lSessDescForInsert,
xPeriodDateForInsert => lPeriodDateForInsert,
xAttrNameForDelete => lAttrNameForDelete
) ;
IF (lAttrNameForDelete IS NOT NULL AND lAttrNameForDelete.COUNT > 0) THEN
bulkDeleteFromPage(
pPAgeId => pToPageId ,
pUserId => pUserId ,
pFunctionName => xParamFunctionName,
pAttributeNameTbl => lAttrNameForDelete
);
IF (lAttrNameForInsert IS NOT NULL AND lAttrNameForInsert.COUNT > 0) THEN
bulkInsertIntoPage(
pSessionId => pSessionId,
pPageId => pToPageId,
pUserId => pUserId,
pFunctionName => xParamFunctionName,
pAttributeNameTbl => lAttrNameForInsert,
pDimensionTbl => lDimensionForInsert,
pSessionValueTbl => lSessValueForInsert,
pSessionDescTbl => lSessDescForInsert,
pPeriodDateTbl => lPeriodDateForInsert
);
SELECT attribute_name, dimension, session_value, session_description, period_date
FROM bis_user_attributes
WHERE function_name =pFunctionName
AND session_id = pSessionId
AND user_id = pUserId
AND (attribute_name ='AS_OF_DATE' OR dimension ='TIME');
deleteAndInsertIntoSession(
pSessionId => pSessionId,
pUserId => pUserId,
pFunctionName => pFunctionName,
pAttributeNameTbl => vAttributeCodeTable,
pDimensionTbl => vDimensionTable,
pSessionValueTbl => vSessionValueTable,
pSessionDescTbl => vSessionDescTable,
pPeriodDateTbl => vPeriodDateTable,
pParameterGroup => pParameterGroup,
pIncludePrevAsOfDate => TRUE
);
PROCEDURE UPDATE_COMPUTED_DATES(
p_user_id IN NUMBER,
p_page_id IN NUMBER,
p_function_name IN VARCHAR2,
p_time_comparison_type IN VARCHAR2,
p_asof_date IN DATE,
p_time_level IN VARCHAR2,
x_prev_asof_Date OUT NOCOPY DATE,
x_curr_report_Start_date OUT NOCOPY DATE,
x_prev_report_Start_date OUT NOCOPY DATE,
x_curr_effective_start_date OUT NOCOPY DATE,
x_curr_effective_end_date OUT NOCOPY DATE,
x_time_level_id OUT NOCOPY VARCHAR2,
x_time_level_value OUT NOCOPY VARCHAR2,
x_prev_effective_start_date OUT NOCOPY DATE,
x_prev_effective_end_date OUT NOCOPY DATE,
x_prev_time_level_id OUT NOCOPY VARCHAR2,
x_prev_time_level_value OUT NOCOPY VARCHAR2,
x_prev_asof_Date_char OUT NOCOPY VARCHAR2,
x_curr_report_Start_date_char OUT NOCOPY VARCHAR2,
x_prev_report_Start_date_char OUT NOCOPY VARCHAR2,
x_curr_eff_start_date_char OUT NOCOPY VARCHAR2,
x_curr_eff_end_date_char OUT NOCOPY VARCHAR2,
x_prev_eff_start_date_char OUT NOCOPY VARCHAR2,
x_prev_eff_end_date_char OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
p_plug_id IN NUMBER DEFAULT 0
)
IS
l_RegionCode varchar2(80);
SELECT SCHEDULE_ID
FROM BIS_SCHEDULE_PREFERENCES
WHERE PLUG_ID = p_plug_id
AND USER_ID = p_user_id;
UPDATE BIS_USER_ATTRIBUTES SET
session_value = DECODE(attribute_name, 'AS_OF_DATE', l_AsOfDate_Char, 'BIS_P_ASOF_DATE', l_PrevAsOfDate_Char,
'BIS_CUR_REPORT_START_DATE', l_CurrReportStartDate_Char, 'BIS_PREV_REPORT_START_DATE', l_PrevReportStartDate_Char,
'BIS_PREVIOUS_EFFECTIVE_START_DATE', l_PrevTimeLevelId, 'BIS_PREVIOUS_EFFECTIVE_END_DATE', l_PrevTimeLevelId,
p_time_level || '_FROM', l_TimeLevelId, p_time_level || '_TO', l_TimeLevelId),
session_description = DECODE(attribute_name, 'AS_OF_DATE', l_AsOfDate_Char, 'BIS_P_ASOF_DATE', l_PrevAsOfDate_Char,
'BIS_CUR_REPORT_START_DATE', l_CurrReportStartDate_Char, 'BIS_PREV_REPORT_START_DATE', l_PrevReportStartDate_Char,
'BIS_PREVIOUS_EFFECTIVE_START_DATE', l_PrevTimeLevelValue, 'BIS_PREVIOUS_EFFECTIVE_END_DATE', l_PrevTimeLevelValue,
p_time_level || '_FROM', l_TimeLevelValue, p_time_level || '_TO', l_TimeLevelValue),
period_date = DECODE(attribute_name, 'AS_OF_DATE', trunc(p_asof_date), 'BIS_P_ASOF_DATE', l_PrevAsOfDate,
'BIS_CUR_REPORT_START_DATE', l_CurrReportStartDate, 'BIS_PREV_REPORT_START_DATE', l_PrevReportStartDate,
'BIS_PREVIOUS_EFFECTIVE_START_DATE', l_PrevEffStartDate, 'BIS_PREVIOUS_EFFECTIVE_END_DATE', l_PrevEffEndDate,
p_time_level || '_FROM', l_CurrEffStartDate, p_time_level || '_TO', l_CurrEffEndDate),
last_update_date = sysdate,
last_updated_by = p_user_id
WHERE schedule_id = l_schedule_id
AND attribute_name IN ('AS_OF_DATE','BIS_P_ASOF_DATE','BIS_CUR_REPORT_START_DATE','BIS_PREV_REPORT_START_DATE',
'BIS_PREVIOUS_EFFECTIVE_START_DATE','BIS_PREVIOUS_EFFECTIVE_END_DATE',p_time_level || '_FROM', p_time_level || '_TO');
UPDATE BIS_USER_ATTRIBUTES SET
session_value = DECODE(attribute_name, 'AS_OF_DATE', l_AsOfDate_Char, 'BIS_P_ASOF_DATE', l_PrevAsOfDate_Char,
'BIS_CUR_REPORT_START_DATE', l_CurrReportStartDate_Char, 'BIS_PREV_REPORT_START_DATE', l_PrevReportStartDate_Char,
'BIS_PREVIOUS_EFFECTIVE_START_DATE', l_PrevTimeLevelId, 'BIS_PREVIOUS_EFFECTIVE_END_DATE', l_PrevTimeLevelId,
p_time_level || '_FROM', l_TimeLevelId, p_time_level || '_TO', l_TimeLevelId),
session_description = DECODE(attribute_name, 'AS_OF_DATE', l_AsOfDate_Char, 'BIS_P_ASOF_DATE', l_PrevAsOfDate_Char,
'BIS_CUR_REPORT_START_DATE', l_CurrReportStartDate_Char, 'BIS_PREV_REPORT_START_DATE', l_PrevReportStartDate_Char,
'BIS_PREVIOUS_EFFECTIVE_START_DATE', l_PrevTimeLevelValue, 'BIS_PREVIOUS_EFFECTIVE_END_DATE', l_PrevTimeLevelValue,
p_time_level || '_FROM', l_TimeLevelValue, p_time_level || '_TO', l_TimeLevelValue),
period_date = DECODE(attribute_name, 'AS_OF_DATE', trunc(p_asof_date), 'BIS_P_ASOF_DATE', l_PrevAsOfDate,
'BIS_CUR_REPORT_START_DATE', l_CurrReportStartDate, 'BIS_PREV_REPORT_START_DATE', l_PrevReportStartDate,
'BIS_PREVIOUS_EFFECTIVE_START_DATE', l_PrevEffStartDate, 'BIS_PREVIOUS_EFFECTIVE_END_DATE', l_PrevEffEndDate,
p_time_level || '_FROM', l_CurrEffStartDate, p_time_level || '_TO', l_CurrEffEndDate),
last_update_date = sysdate,
last_updated_by = p_user_id
WHERE user_id = p_user_id
AND page_id = p_page_id
AND attribute_name IN ('AS_OF_DATE','BIS_P_ASOF_DATE','BIS_CUR_REPORT_START_DATE','BIS_PREV_REPORT_START_DATE',
'BIS_PREVIOUS_EFFECTIVE_START_DATE','BIS_PREVIOUS_EFFECTIVE_END_DATE',p_time_level || '_FROM', p_time_level || '_TO');
End UPDATE_COMPUTED_DATES;