[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT KPI_MODEL
INTO l_enabled
FROM BSC_TABS_B
WHERE tab_id =p_tab_id;
SELECT BSC_MODEL
INTO l_enabled
FROM BSC_TABS_B
WHERE tab_id =p_tab_id;
SELECT enabled_flag
INTO l_enabled
FROM BSC_TAB_VIEWS_B
WHERE tab_id =p_tab_id
AND tab_view_id = p_tab_view_id;
SELECT COUNT(0)
INTO l_count
FROM BSC_TAB_VIEWS_B
WHERE tab_id = p_tab_id
AND tab_view_id =p_tab_view_id;
BSC_CUSTOM_VIEW_PVT.Update_Tab_default_View
(
p_Tab_Rec => l_Tab_Rec
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
BSC_CUSTOM_VIEW_PVT.Update_Tab_View
(
p_Tab_View_Rec => l_Tab_View_Rec
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
BSC_CUSTOM_VIEW_PVT.Update_Tab_default_View
(
p_Tab_Rec => l_Tab_Rec
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
BSC_CUSTOM_VIEW_PVT.Update_Tab_View
(
p_Tab_View_Rec => l_Tab_View_Rec
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
The default view needs to be updated in BSC_TABS_B.
If scorecard view and startegy map view are enabled then they should go into
BSC_TABS_B COLUMNS KPI_MODEL and BSC_MODEL.
For other views like Tree View/Detail View and other custom views should be
updated in BSC_TAB_VIEWS_B with enabled flag set to 1/0.
if user unassigns all the views we have to set scorecard view as default.
The default view should be updated in the last only.
if we are trying to set the default view of the view which is disabled then we have to throw
the exception.
/******************************************************************************************/
PROCEDURE Assign_Unassign_Views(
p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_tab_id IN NUMBER
,p_default_value IN NUMBER
,p_assign_views IN VARCHAR2
,p_unassign_views IN VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
)IS
l_count NUMBER;
l_Tab_Rec.Bsc_Last_Update_Date := SYSDATE;
BSC_CUSTOM_VIEW_PVT.Update_Tab_default_View
(
p_Tab_Rec => l_Tab_Rec
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
INSERT INTO BSC_TAB_VIEWS_B
( tab_id
,tab_view_id
,enabled_flag
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
)VALUES
( l_Tab_View_Rec.Bsc_Tab_Id
,l_Tab_View_Rec.Bsc_Tab_View_Id
,l_Tab_View_Rec.Bsc_Enabled_Flag
,fnd_global.USER_ID
,SYSDATE
,fnd_global.USER_ID
,SYSDATE
,fnd_global.LOGIN_ID
);
INSERT INTO BSC_TAB_VIEWS_TL
( tab_id
,tab_view_id
,language
,source_lang
,name
,help
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
)
SELECT l_Tab_View_Rec.Bsc_Tab_Id
,l_Tab_View_Rec.Bsc_Tab_View_Id
,L.LANGUAGE_CODE
,USERENV('LANG')
,l_Tab_View_Rec.Bsc_Name
,l_Tab_View_Rec.Bsc_Help
,fnd_global.USER_ID
,sysdate
,fnd_global.USER_ID
,sysdate
,fnd_global.LOGIN_ID
FROM FND_LANGUAGES L
WHERE L.INSTALLED_FLAG IN ('I', 'B')
AND NOT EXISTS
( SELECT NULL
FROM BSC_TAB_VIEWS_TL T
WHERE T.tab_id = l_Tab_View_Rec.Bsc_Tab_Id
AND T.tab_view_id = l_Tab_View_Rec.Bsc_Tab_View_Id
AND T.LANGUAGE = L.LANGUAGE_CODE);
SELECT a.tab_id
,a.kpi_model
,a.bsc_model
,a.cross_model
,a.default_model
,a.zoom_factor
,a.created_by
,a.creation_date
,a.last_updated_by
,a.last_update_date
,a.last_update_login
,a.tab_index
,a.parent_tab_id
,a.owner_id
,a.short_name
INTO x_Tab_Rec.Bsc_Tab_Id
,x_Tab_Rec.Bsc_Kpi_Model
,x_Tab_Rec.Bsc_Bsc_Model
,x_Tab_Rec.Bsc_Cross_Model
,x_Tab_Rec.Bsc_Default_Model
,x_Tab_Rec.Bsc_Zoom_Factor
,x_Tab_Rec.Bsc_Created_By
,x_Tab_Rec.Bsc_Creation_Date
,x_Tab_Rec.Bsc_Last_updated_By
,x_Tab_Rec.Bsc_Last_update_Date
,x_Tab_Rec.Bsc_Last_update_Login
,x_Tab_Rec.Bsc_Tab_Index
,x_Tab_Rec.Bsc_Parent_Tab_id
,x_Tab_Rec.Bsc_Owner_Id
,x_Tab_Rec.Bsc_Short_Name
FROM BSC_TABS_B a
WHERE a.tab_id = p_Tab_Rec.Bsc_Tab_Id;
x_msg_data := x_msg_data||' -> BSC_BIS_KPI_MEAS_PUB.Update_Dim_Set ';
x_msg_data := SQLERRM||' at BSC_BIS_KPI_MEAS_PUB.Update_Dim_Set ';
SELECT tab_id
,tab_view_id
,enabled_flag
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
INTO x_Tab_View_Rec.Bsc_Tab_Id
,x_Tab_View_Rec.Bsc_Tab_View_Id
,x_Tab_View_Rec.Bsc_Enabled_Flag
,x_Tab_View_Rec.Bsc_Created_By
,x_Tab_View_Rec.Bsc_Creation_Date
,x_Tab_View_Rec.Bsc_Last_Updated_By
,x_Tab_View_Rec.Bsc_Last_Update_Date
,x_Tab_View_Rec.Bsc_Last_Update_Login
FROM BSC_TAB_VIEWS_B
WHERE tab_id = p_Tab_View_Rec.Bsc_Tab_Id
AND tab_view_id =p_Tab_View_Rec.Bsc_Tab_View_Id;
Description :- This procedure updates the BSC_TABS_B table.
This procedure should be called from assign and unassign views.
Input :- p_tab_View_rec
Ouput :- Updates the BSC_TABS_B
Created By :- ashankar 23-Oct-2003
/******************************************************************************************/
PROCEDURE Update_Tab_View
( p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_Tab_View_Rec IN BSC_CUSTOM_VIEW_PUB.Bsc_Cust_View_Rec_Type
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
)IS
l_Tab_View_Rec BSC_CUSTOM_VIEW_PUB.Bsc_Cust_View_Rec_Type;
SAVEPOINT UpdateTabView;
IF (p_Tab_View_Rec.Bsc_Last_Updated_By IS NOT NULL) THEN
l_Tab_View_Out_Rec.Bsc_Last_Updated_By := p_Tab_View_Rec.Bsc_Last_Updated_By;
IF (p_Tab_View_Rec.Bsc_Last_Update_Date IS NOT NULL) THEN
l_Tab_View_Out_Rec.Bsc_Last_Update_Date := p_Tab_View_Rec.Bsc_Last_Update_Date;
IF (p_Tab_View_Rec.Bsc_Last_Update_Login IS NOT NULL) THEN
l_Tab_View_Out_Rec.Bsc_Last_Update_Login := p_Tab_View_Rec.Bsc_Last_Update_Login;
UPDATE BSC_TAB_VIEWS_B
SET Enabled_Flag= l_Tab_View_Out_Rec.Bsc_Enabled_Flag
,Created_By= l_Tab_View_Out_Rec.Bsc_Created_By
,Creation_Date=l_Tab_View_Out_Rec.Bsc_Creation_Date
,Last_Updated_By= l_Tab_View_Out_Rec.Bsc_Last_Updated_By
,Last_Update_Date= l_Tab_View_Out_Rec.Bsc_Last_Update_Date
,Last_Update_Login=l_Tab_View_Out_Rec.Bsc_Last_Update_Login
WHERE tab_id =l_Tab_View_Rec.Bsc_Tab_Id
AND tab_view_id = l_Tab_View_Rec.Bsc_Tab_View_Id;
ROLLBACK TO UpdateTabView;
ROLLBACK TO UpdateTabView;
ROLLBACK TO UpdateTabView;
x_msg_data := x_msg_data||' -> BSC_CUSTOM_VIEW_PVT.Update_Tab_View ';
x_msg_data := SQLERRM||' at BSC_CUSTOM_VIEW_PVT.Update_Tab_View ';
ROLLBACK TO UpdateTabView;
x_msg_data := x_msg_data||' -> BSC_CUSTOM_VIEW_PVT.Update_Tab_View ';
x_msg_data := SQLERRM||' at BSC_CUSTOM_VIEW_PVT.Update_Tab_View ';
END Update_Tab_View;
This procedure is used to update the default view of the tab.
User can change the default view of the tab.
/*****************************************************************************/
PROCEDURE Update_Tab_default_View
(
p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_Tab_Rec IN BSC_CUSTOM_VIEW_PUB.Bsc_Tab_Rec_Type
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
)IS
l_Tab_Ret_Rec BSC_CUSTOM_VIEW_PUB.Bsc_Tab_Rec_Type;
SAVEPOINT UpdateTabdefaultView;
IF (p_Tab_Rec.Bsc_Last_updated_By IS NOT NULL) THEN
l_Tab_Ret_Rec.Bsc_Last_updated_By := p_Tab_Rec.Bsc_Last_updated_By;
IF (p_Tab_Rec.Bsc_Last_update_Date IS NOT NULL) THEN
l_Tab_Ret_Rec.Bsc_Last_update_Date := p_Tab_Rec.Bsc_Last_update_Date ;
IF (p_Tab_Rec.Bsc_Last_update_Login IS NOT NULL) THEN
l_Tab_Ret_Rec.Bsc_Last_update_Login := p_Tab_Rec.Bsc_Last_update_Login;
UPDATE BSC_TABS_B
SET Kpi_Model=l_Tab_Ret_Rec.Bsc_Kpi_Model
,Bsc_Model=l_Tab_Ret_Rec.Bsc_Bsc_Model
,Cross_Model=l_Tab_Ret_Rec.Bsc_Cross_Model
,Default_Model=l_Tab_Ret_Rec.Bsc_Default_Model
,Zoom_Factor=l_Tab_Ret_Rec.Bsc_Zoom_Factor
,Created_By =l_Tab_Ret_Rec.Bsc_Created_By
,Creation_Date =l_Tab_Ret_Rec.Bsc_Creation_Date
,Last_updated_By=l_Tab_Ret_Rec.Bsc_Last_updated_By
,Last_update_Date=l_Tab_Ret_Rec.Bsc_Last_update_Date
,Last_update_Login=l_Tab_Ret_Rec.Bsc_Last_update_Login
,Tab_Index=l_Tab_Ret_Rec.Bsc_Tab_Index
,Parent_Tab_id=l_Tab_Ret_Rec.Bsc_Parent_Tab_id
,Owner_Id=l_Tab_Ret_Rec.Bsc_Owner_Id
,Short_Name=l_Tab_Ret_Rec.Bsc_Short_Name
WHERE Tab_Id = l_Tab_Ret_Rec.Bsc_Tab_Id;
ROLLBACK TO UpdateTabdefaultView;
ROLLBACK TO UpdateTabdefaultView;
ROLLBACK TO UpdateTabdefaultView;
x_msg_data := x_msg_data||' -> BSC_CUSTOM_VIEW_PVT.Update_Tab_default_View ';
x_msg_data := SQLERRM||' at BSC_CUSTOM_VIEW_PVT.Update_Tab_default_View ';
ROLLBACK TO UpdateTabdefaultView;
x_msg_data := x_msg_data||' -> BSC_CUSTOM_VIEW_PVT.Update_Tab_default_View ';
x_msg_data := SQLERRM||' at BSC_CUSTOM_VIEW_PVT.Update_Tab_default_View ';
END Update_Tab_default_View;
SELECT default_model
INTO l_default_view
FROM BSC_TABS_B
WHERE tab_id = p_Tab_Id;
Name :- delete_Custom_View
Description :- This procedure will delete the custom view from bsc_tab_views_b table.
It will do the following validations.
1. Before deleting the custom view it will verify if it is the default
view which is being deleted. If yes then it will set scorecard view
as default view and delete the custom view.
Otherwise it will update the last update date of the tab.
This is required for Granular locking purpose.
The entry will be deleted from the following tables.
1.BSC_TAB_VIEWS_B
2.BSC_TAB_VIEWS_TL
3.BSC_TAB_VIEW_KPI_TL
4.BSC_TAB_VIEW_LABELS_B
5.BSC_TAB_VIEW_LABELS_TL
6.BSC_SYS_IMAGES_MAP_TL
7.BSC_SYS_IMAGES (need for cascading)
8.Form functoins defined in each custom view upon creation in BSC_CUSTOM_VIEW_UI_WRAPPER.create_function
Input :- p_CustView_Rec
Creator/Modified by :- ashankar 10-NOV-2003
/******************************************************************************/
PROCEDURE delete_Custom_View
(
p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_CustView_Rec IN BSC_CUSTOM_VIEW_PUB.Bsc_Cust_View_Rec_Type
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_CustView_Rec BSC_CUSTOM_VIEW_PUB.Bsc_Cust_View_Rec_Type;
SELECT image_id
FROM BSC_SYS_IMAGES
WHERE image_id NOT IN
( SELECT DISTINCT(image_id)
FROM BSC_SYS_IMAGES_MAP_TL);
SAVEPOINT deleteCustomView;
SELECT count(0)
INTO l_count
FROM bsc_tab_views_b
WHERE tab_id = l_CustView_Rec.Bsc_Tab_Id
AND tab_view_id = l_CustView_Rec.Bsc_Tab_View_Id;
l_Tab_Rec.Bsc_Last_update_Date := SYSDATE;
BSC_CUSTOM_VIEW_PVT.Update_Tab_default_View
(
p_Tab_Rec => l_Tab_Rec
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
l_Tab_Rec.Bsc_Last_update_Date := SYSDATE;
BSC_CUSTOM_VIEW_PVT.Update_Tab_default_View
(
p_Tab_Rec => l_Tab_Rec
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
BSC_CUSTOM_VIEW_UI_WRAPPER.delete_function( p_tab_id => l_CustView_Rec.Bsc_Tab_Id
,p_tab_view_id => l_CustView_Rec.Bsc_Tab_View_Id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
DELETE
FROM BSC_TAB_VIEWS_B
WHERE tab_id = l_CustView_Rec.Bsc_Tab_Id
AND tab_view_id = l_CustView_Rec.Bsc_Tab_View_Id;
DELETE
FROM BSC_TAB_VIEWS_TL
WHERE tab_id = l_CustView_Rec.Bsc_Tab_Id
AND tab_view_id = l_CustView_Rec.Bsc_Tab_View_Id;
DELETE
FROM BSC_TAB_VIEW_KPI_TL
WHERE tab_id = l_CustView_Rec.Bsc_Tab_Id
AND tab_view_id = l_CustView_Rec.Bsc_Tab_View_Id;
DELETE
FROM BSC_TAB_VIEW_LABELS_B
WHERE tab_id = l_CustView_Rec.Bsc_Tab_Id
AND tab_view_id = l_CustView_Rec.Bsc_Tab_View_Id;
DELETE
FROM BSC_TAB_VIEW_LABELS_B
WHERE tab_id = l_CustView_Rec.Bsc_Tab_Id
AND label_type = 1
AND link_id = l_CustView_Rec.Bsc_Tab_View_Id;
DELETE
FROM BSC_TAB_VIEW_LABELS_TL
WHERE tab_id = l_CustView_Rec.Bsc_Tab_Id
AND tab_view_id = l_CustView_Rec.Bsc_Tab_View_Id;
DELETE
FROM BSC_SYS_IMAGES_MAP_TL
WHERE SOURCE_TYPE = 1
AND SOURCE_CODE = l_CustView_Rec.Bsc_Tab_Id
AND TYPE = l_CustView_Rec.Bsc_Tab_View_Id;
DELETE
FROM BSC_SYS_IMAGES
WHERE IMAGE_ID = l_CustView_Rec.Bsc_Image_Id;
ROLLBACK TO deleteCustomView;
ROLLBACK TO deleteCustomView;
ROLLBACK TO deleteCustomView;
x_msg_data := x_msg_data||' -> BSC_CUSTOM_VIEW_PUB.delete_Custom_View ';
x_msg_data := SQLERRM||' at BSC_CUSTOM_VIEW_PUB.delete_Custom_View ';
ROLLBACK TO deleteCustomView;
x_msg_data := x_msg_data||' -> BSC_CUSTOM_VIEW_PUB.delete_Custom_View ';
x_msg_data := SQLERRM||' at BSC_CUSTOM_VIEW_PUB.delete_Custom_View ';
END delete_Custom_View;
SELECT COUNT(0)
INTO l_count
FROM BSC_TABS_B
WHERE TAB_ID = p_Tab_Id;
DELETE CUSTOM VIEW LINKS
/*******************************************************************************/
PROCEDURE Delete_Custom_View_Links
(
p_commit IN VARCHAR2 := FND_API.G_FALSE
, p_tab_id IN NUMBER
, p_obj_id IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
) IS
l_Count NUMBER;
SELECT tab_id,
tab_view_id,
label_id
FROM BSC_TAB_VIEW_LABELS_B
WHERE tab_id = p_tab_id
AND link_id = p_obj_id;
SAVEPOINT DeleteCustomViewLinks;
SELECT COUNT(0)
INTO l_Count
FROM BSC_TAB_VIEW_KPI_VL
WHERE Tab_Id = p_tab_id
AND Indicator = p_obj_id;
DELETE
FROM BSC_TAB_VIEW_KPI_TL
WHERE tab_id = p_tab_id
AND indicator = p_obj_id;
So when the objective is deleted we have to cascade these changes in
BSC_TAB_VIEW_LABELS_B and _TL table.
So following is the LOGIC
To delete from TL table we need to cache the TAB_ID,TAB_VIEW_ID and LABEL_ID.
To delete from _B table we need tab_id and LINK_ID
*********************************************/
FOR cd IN c_CachedData LOOP
DELETE
FROM BSC_TAB_VIEW_LABELS_TL
WHERE tab_id = cd.tab_id
AND tab_view_id = cd.tab_view_id
AND label_id = cd.label_id;
DELETE
FROM BSC_TAB_VIEW_LABELS_B
WHERE tab_id = p_tab_id
AND LINK_ID =p_obj_id;
ROLLBACK TO DeleteCustomViewLinks;
ROLLBACK TO DeleteCustomViewLinks;
ROLLBACK TO DeleteCustomViewLinks;
x_msg_data := x_msg_data||' -> BSC_CUSTOM_VIEW_PVT.Delete_Custom_View_Links ';
x_msg_data := SQLERRM||' at BSC_CUSTOM_VIEW_PVT.Delete_Custom_View_Links ';
ROLLBACK TO DeleteCustomViewLinks;
x_msg_data := x_msg_data||' -> BSC_CUSTOM_VIEW_PVT.Delete_Custom_View_Links ';
x_msg_data := SQLERRM||' at BSC_CUSTOM_VIEW_PVT.Delete_Custom_View_Links ';
END Delete_Custom_View_Links;