[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| ADRAO Modified Delete_KPI_Group() not to throw error for |
| for KPIs associated for Bug #3315077 14-DEC-2003 |
| |
| |
| 08-JAN-2004 krishan fixed for the bug 3357984 |
| 27-FEB-2004 krishna fixed the bug# 3464251 |
| 10-MAR-04 jxyu Modified for enhancement #3493589 |
| 30-APR-2004 PAJOHRI Bug #3598852 |
| 05-MAY6-04 wcano add procedure Update_Kpi_Periodicities |
| 18-MAY-04 adrao Modified PL/SQL records and CRUD to accept SHORT_NAME |
| 19-MAY-04 adrao Modified Assign_Kpi_Group to pass FALSE as p_commit to |
| assign_kpi API |
| 02-JUL-04 rpenneru Modified for enh# 3532517 |
| 30-SEP-04 visuri modified for bug 3852611 |
| 08-JUL-05 ashankar nodified the API Check_Tab by changing the API |
| is_Scorecard_From_AG_Report TO is_Scorecard_From_Reports |
| 14-JUL-05 Krishna modified update_kpi_periodicities for bug#4376162 |
| 25-JUL-05 hengliu added Check_Tabview_Dependency for bug#4237294 |
| 31-AUG-2005 ashankar Bugfix#4576022 |
| 25-MAY-2006 jxyu Bugfix#4174625 |
| 02-Aug-2006 ashankar Bug fix #5400575 made changes to the method Unassign_kpi|
| 17-Oct-2006 ppandey Bug #5584826 Calender properties cascaded to Shared Obj |
| 08-jan-07 ashankar Bug#5652713 Added the method Is_Valid_Sim_Period |
| 16-NOV-2006 ankgoel Color By KPI enh#5244136 |
| 09-feb-2007 ashankar Simulation Tree Enhacement 5386112 |
| 27-feb-07 ashankar Fixed the issue in Update_Kpi_Periodicities |
| 30-MAR-2007 akoduri Enh #5928640 Migration of Periodicity properties from |
| VB to Html |
+======================================================================================+
*/
G_PKG_NAME varchar2(30) := 'BSC_PMF_UI_WRAPPER';
g_Bsc_Pmf_Dim_Tbl.delete(i);
BSC_KPI_PUB.Update_Kpi_Time_Stamp( FND_API.G_FALSE
,l_Bsc_Kpi_Entity_Rec
,x_return_status
,x_msg_count
,x_msg_data);
l_sql := 'select indicator ' ||
' from BSC_KPIS_B ' ||
' where source_indicator = :1';
BSC_KPI_PUB.Update_Kpi_Time_Stamp( FND_API.G_FALSE
,l_Bsc_Kpi_Entity_Rec
,x_return_status
,x_msg_count
,x_msg_data);
select MAX(ind_group_id)
into l_kpi_group_id
from BSC_TAB_IND_GROUPS_TL;
BSC_KPI_GROUP_PUB.Update_Kpi_Group( FND_API.G_FALSE
,l_Bsc_Kpi_Group_Rec
,x_return_status
,x_msg_count
,x_msg_data);
BSC_KPI_GROUP_PUB.Update_Kpi_Group( FND_API.G_FALSE
,l_Bsc_Kpi_Group_Rec
,x_return_status
,x_msg_count
,x_msg_data);
procedure Update_Kpi_Group(
p_commit IN VARCHAR2 := FND_API.G_TRUE
,p_kpi_group_id number
,p_kpi_group_name varchar2
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
,p_kpi_group_help varchar2
) is
l_Bsc_Kpi_Group_Rec BSC_KPI_GROUP_PUB.Bsc_Kpi_Group_Rec;
BSC_KPI_GROUP_PUB.Update_Kpi_Group( FND_API.G_FALSE
,l_Bsc_Kpi_Group_Rec
,x_return_status
,x_msg_count
,x_msg_data);
x_msg_data := x_msg_data||' -> BSC_PMF_UI_WRAPPER.Update_Kpi_Group ';
x_msg_data := SQLERRM||' at BSC_PMF_UI_WRAPPER.Update_Kpi_Group ';
x_msg_data := x_msg_data||' -> BSC_PMF_UI_WRAPPER.Update_Kpi_Group ';
x_msg_data := SQLERRM||' at BSC_PMF_UI_WRAPPER.Update_Kpi_Group ';
end Update_Kpi_Group;
procedure Update_Kpi(
p_commit IN VARCHAR2 := FND_API.G_TRUE
,p_kpi_id IN number
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
,p_kpi_name IN varchar2 DEFAULT null
,p_kpi_help IN varchar2 DEFAULT null
) is
l_Bsc_Kpi_Entity_Rec BSC_KPI_PUB.Bsc_Kpi_Entity_Rec;
SAVEPOINT BSCUpdateKPIWrapper;
select count(indicator)
into l_count
from bsc_tab_indicators a
where a.indicator <> p_kpi_id
and a.tab_id = (select tab_id
from bsc_tab_indicators
where indicator = p_kpi_id)
and a.indicator in (select indicator
from bsc_kpis_tl
where name = p_kpi_name);
select d.name
into l_tab_name
from BSC_TAB_INDICATORS a,
BSC_KPIS_TL b,
BSC_KPIS_TL c,
BSC_TABS_TL d
where a.indicator = b.indicator
and a.tab_id = d.tab_id
and b.name = c.name
and c.indicator = p_kpi_id;
BSC_KPI_PVT.Update_Kpi( FND_API.G_FALSE
,l_Bsc_Kpi_Entity_Rec
,x_return_status
,x_msg_count
,x_msg_data);
select count(indicator)
into l_count
from BSC_KPIS_B
where source_indicator = p_kpi_id;
l_sql := 'select indicator ' ||
' from BSC_KPIS_B ' ||
' where source_indicator = :1';
BSC_KPI_PVT.Update_Kpi( FND_API.G_FALSE
,l_Bsc_Kpi_Entity_Rec
,x_return_status
,x_msg_count
,x_msg_data);
ROLLBACK TO BSCUpdateKPIWrapper;
ROLLBACK TO BSCUpdateKPIWrapper;
ROLLBACK TO BSCUpdateKPIWrapper;
x_msg_data := x_msg_data||' -> BSC_PMF_UI_WRAPPER.Update_Kpi ';
x_msg_data := SQLERRM||' at BSC_PMF_UI_WRAPPER.Update_Kpi ';
ROLLBACK TO BSCUpdateKPIWrapper;
x_msg_data := x_msg_data||' -> BSC_PMF_UI_WRAPPER.Update_Kpi ';
x_msg_data := SQLERRM||' at BSC_PMF_UI_WRAPPER.Update_Kpi ';
end Update_Kpi;
BSC_SCORECARD_PUB.Update_Tab_Time_Stamp( FND_API.G_FALSE
, l_Bsc_Tab_Entity_Rec_P
, x_return_status
, x_msg_count
, x_msg_data);
BSC_SCORECARD_PUB.Update_System_Time_Stamp( FND_API.G_FALSE
, l_Bsc_Tab_Entity_Rec
, x_return_status
, x_msg_count
, x_msg_data);
procedure Update_Tab(
p_commit IN VARCHAR2 := FND_API.G_TRUE
,p_tab_id IN number
,p_tab_name IN varchar2
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
,p_tab_help IN varchar2 DEFAULT null
) is
l_Bsc_Tab_Entity_Rec BSC_SCORECARD_PUB.Bsc_Tab_Entity_Rec;
BSC_PMF_UI_WRAPPER.Update_Tab
( p_commit => FND_API.G_FALSE
, p_tab_id => p_tab_id
, p_owner_id => NULL
, p_tab_name => p_tab_name
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_tab_help => p_tab_help
, p_tab_info => NULL
, p_time_stamp => NULL
);
x_msg_data := x_msg_data||' -> BSC_PMF_UI_WRAPPER.Update_Tab ';
x_msg_data := SQLERRM||' at BSC_PMF_UI_WRAPPER.Update_Tab ';
x_msg_data := x_msg_data||' -> BSC_PMF_UI_WRAPPER.Update_Tab ';
x_msg_data := SQLERRM||' at BSC_PMF_UI_WRAPPER.Update_Tab ';
end Update_Tab;
procedure Update_Tab(
p_commit IN VARCHAR2 := FND_API.G_TRUE
,p_tab_id IN number
,p_owner_id IN number
,p_tab_name IN varchar2
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
,p_tab_help IN varchar2 DEFAULT null
,p_tab_info IN varchar2 DEFAULT null
,p_time_stamp IN VARCHAR2 := NULL
) is
l_Bsc_Tab_Entity_Rec BSC_SCORECARD_PUB.Bsc_Tab_Entity_Rec;
SELECT Name
FROM BSC_TABS_VL
WHERE Tab_Id = l_Bsc_Tab_Entity_Rec.Bsc_Tab_Id;
FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_DELETE_MESSAGE');
SELECT COUNT(*) INTO l_Count
FROM BSC_TABS_VL
WHERE Tab_Id <> l_Bsc_Tab_Entity_Rec.Bsc_Tab_Id
AND UPPER(Name) = UPPER(l_Bsc_Tab_Entity_Rec.Bsc_Tab_Name);
BSC_SCORECARD_PUB.Update_Tab( FND_API.G_FALSE
,l_Bsc_Tab_Entity_Rec
,x_return_status
,x_msg_count
,x_msg_data);
BSC_SCORECARD_PUB.Update_Tab_Time_Stamp( FND_API.G_FALSE
,l_Bsc_Tab_Entity_Rec
,x_return_status
,x_msg_count
,x_msg_data);
BSC_SCORECARD_PUB.Update_System_Time_Stamp( FND_API.G_FALSE
,l_Bsc_Tab_Entity_Rec
,x_return_status
,x_msg_count
,x_msg_data);
x_msg_data := x_msg_data||' -> BSC_PMF_UI_WRAPPER.Update_Tab ';
x_msg_data := SQLERRM||' at BSC_PMF_UI_WRAPPER.Update_Tab ';
x_msg_data := x_msg_data||' -> BSC_PMF_UI_WRAPPER.Update_Tab ';
x_msg_data := SQLERRM||' at BSC_PMF_UI_WRAPPER.Update_Tab ';
end Update_Tab;
procedure Update_Analysis_Option(
p_commit IN VARCHAR2 := FND_API.G_TRUE
,p_kpi_id IN number
,p_option_group_id IN number
,p_option_id IN number
,p_option_name IN varchar2
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
,p_option_help IN varchar2 DEFAULT null
) is
l_Bsc_Option_Rec BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type;
BSC_ANALYSIS_OPTION_PUB.Update_Analysis_Options( FND_API.G_FALSE
,l_Bsc_Option_Rec
,x_return_status
,x_msg_count
,x_msg_data);
BSC_KPI_PUB.Update_Kpi_Time_Stamp( FND_API.G_FALSE
,l_Bsc_Kpi_Entity_Rec
,x_return_status
,x_msg_count
,x_msg_data);
end Update_Analysis_Option;
procedure Delete_Analysis_Option(
p_commit IN VARCHAR2 := FND_API.G_TRUE
,p_kpi_id IN number
,p_option_group_id IN number
,p_option_id IN number
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
) is
TYPE Recdc_opt IS REF CURSOR;
select count(indicator)
into l_ind_tab_count
from BSC_TAB_INDICATORS
where indicator = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
select tab_id
into l_tab_id
from BSC_TAB_INDICATORS
where indicator = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
select dim_set_id
into l_dim_set_id
from BSC_KPI_ANALYSIS_OPTIONS_B
where indicator = p_kpi_id
and analysis_group_id = p_option_group_id
and option_id = p_option_id;
BSC_ANALYSIS_OPTION_PUB.Delete_Analysis_Options( FND_API.G_FALSE
,l_Bsc_Anal_Opt_Rec
,x_return_status
,x_msg_count
,x_msg_data);
BSC_KPI_PUB.Update_Kpi_Time_Stamp( FND_API.G_FALSE
,l_Bsc_Kpi_Entity_Rec
,x_return_status
,x_msg_count
,x_msg_data);
select count(option_id)
into l_count
from BSC_KPI_ANALYSIS_OPTIONS_B
where indicator = p_kpi_id
and dim_set_id = l_dim_set_id;
l_shr_sql := 'select indicator ' ||
' from BSC_KPIS_B ' ||
' where indicator = :1' ||
' or source_indicator = :2';
select count(indicator)
into l_ind_tab_count
from BSC_TAB_INDICATORS
where indicator = l_Bsc_Dim_Set_Rec_Type.Bsc_Kpi_Id;
select tab_id
into l_tab_id
from BSC_TAB_INDICATORS
where indicator = l_Bsc_Dim_Set_Rec_Type.Bsc_Kpi_Id;
l_opt_sql := 'select dim_group_id ' ||
' from BSC_KPI_DIM_GROUPS ' ||
' where indicator = :1' ||
' and dim_set_id = :2';
BSC_DIMENSION_SETS_PVT.Delete_Dim_Group_In_Dset( FND_API.G_FALSE
,l_Bsc_Dim_Set_Rec_Type
,x_return_status
,x_msg_count
,x_msg_data);
delete from BSC_KPI_DIM_LEVEL_PROPERTIES
where indicator = l_Bsc_Dim_Set_Rec_Type.Bsc_Kpi_Id
and dim_set_id = l_dim_set_id;
BSC_DIMENSION_SETS_PVT.Delete_Dim_Levels( FND_API.G_FALSE
,l_Bsc_Dim_Set_Rec_Type
,x_return_status
,x_msg_count
,x_msg_data);
BSC_DIMENSION_SETS_PUB.Delete_Bsc_Kpi_Dim_Sets_Tl( FND_API.G_FALSE
,l_Bsc_Dim_Set_Rec_Type
,x_return_status
,x_msg_count
,x_msg_data);
end Delete_Analysis_Option;
procedure Delete_Kpi(
p_commit IN VARCHAR2 := FND_API.G_TRUE
,p_kpi_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) is
l_Bsc_Kpi_Entity BSC_KPI_PUB.Bsc_Kpi_Entity_Rec;
SELECT DISTINCT A.Tab_Id
FROM BSC_TAB_INDICATORS A
, BSC_KPIS_B B
, BSC_SYS_FILTERS_VIEWS C
WHERE A.Indicator = B.Indicator
AND C.Source_Type = BSC_DIM_FILTERS_PUB.Source_Type_Tab
AND C.Source_Code = A.Tab_Id
AND ((B.Indicator = p_kpi_id) OR (B.Source_Indicator = p_kpi_id));
select count(tab_id)
into l_count
from BSC_TAB_INDICATORS
where indicator = p_kpi_id;
select tab_id
into l_Bsc_Tab_Entity_Rec.Bsc_Tab_Id
from BSC_TAB_INDICATORS
where indicator = p_kpi_id;
BSC_KPI_PUB.Delete_Kpi( FND_API.G_FALSE
,l_Bsc_Kpi_Entity
,x_return_status
,x_msg_count
,x_msg_data);
BSC_SCORECARD_PUB.Update_Tab_Time_Stamp( FND_API.G_FALSE
,l_Bsc_Tab_Entity_Rec
,x_return_status
,x_msg_count
,x_msg_data);
x_msg_data := x_msg_data||' -> BSC_PMF_UI_WRAPPER.Delete_Kpi ';
x_msg_data := SQLERRM||' at BSC_PMF_UI_WRAPPER.Delete_Kpi ';
x_msg_data := x_msg_data||' -> BSC_PMF_UI_WRAPPER.Delete_Kpi ';
x_msg_data := SQLERRM||' at BSC_PMF_UI_WRAPPER.Delete_Kpi ';
end Delete_Kpi;
procedure Delete_Kpi_Group(
p_commit IN VARCHAR2 := FND_API.G_TRUE
,p_kpi_group_id IN number
,p_tab_id IN number
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
) is
l_Bsc_Kpi_Group BSC_KPI_GROUP_PUB.Bsc_Kpi_Group_Rec;
BSC_KPI_GROUP_PUB.Delete_Kpi_Group( FND_API.G_FALSE
,l_Bsc_Kpi_Group
,x_return_status
,x_msg_count
,x_msg_data);
x_msg_data := x_msg_data||' -> BSC_PMF_UI_WRAPPER.Delete_Kpi_Group ';
x_msg_data := SQLERRM||' at BSC_PMF_UI_WRAPPER.Delete_Kpi_Group ';
x_msg_data := x_msg_data||' -> BSC_PMF_UI_WRAPPER.Delete_Kpi_Group ';
x_msg_data := SQLERRM||' at BSC_PMF_UI_WRAPPER.Delete_Kpi_Group ';
end Delete_Kpi_Group;
procedure Delete_Tab(
p_commit IN VARCHAR2 := FND_API.G_TRUE
,p_tab_id IN number
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
) is
l_Bsc_Tab_Entity BSC_SCORECARD_PUB.Bsc_Tab_Entity_Rec;
SELECT DISTINCT K.Indicator
FROM BSC_KPIS_VL K
, BSC_TAB_INDICATORS T
WHERE K.Indicator = T.Indicator
AND K.Share_Flag = 2
AND T.Tab_Id = p_tab_id;
SAVEPOINT BSCPMFUIDelete;
BSC_KPI_PUB.Delete_Kpi
( p_commit => FND_API.G_FALSE
, p_Bsc_Kpi_Entity_Rec => l_Bsc_Kpi_Entity
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
BSC_SCORECARD_PUB.Delete_Tab( FND_API.G_FALSE
,l_Bsc_Tab_Entity
,x_return_status
,x_msg_count
,x_msg_data);
BSC_SCORECARD_PUB.Update_System_Time_Stamp( FND_API.G_FALSE
,l_Bsc_Tab_Entity
,x_return_status
,x_msg_count
,x_msg_data);
ROLLBACK TO BSCPMFUIDelete;
ROLLBACK TO BSCPMFUIDelete;
ROLLBACK TO BSCPMFUIDelete;
x_msg_data := x_msg_data||' -> BSC_PMF_UI_WRAPPER.Delete_Tab ';
x_msg_data := SQLERRM||' at BSC_PMF_UI_WRAPPER.Delete_Tab ';
ROLLBACK TO BSCPMFUIDelete;
x_msg_data := x_msg_data||' -> BSC_PMF_UI_WRAPPER.Delete_Tab ';
x_msg_data := SQLERRM||' at BSC_PMF_UI_WRAPPER.Delete_Tab ';
end Delete_Tab;
select name, share_flag, source_indicator
into l_kpi_name, l_share_flag, l_kpi_source
from BSC_KPIS_VL
where indicator = p_kpi_id;
select count(indicator)
into l_same_name
from BSC_TAB_INDICATORS
where tab_id = p_tab_id
and indicator in (select indicator
from BSC_KPIS_TL
where upper(name) = upper(l_kpi_name));
select distinct(ind_group_id)
into l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Group_Id
from BSC_KPIS_B
where indicator = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
SELECT GROUP_TYPE
into l_kpi_group_type
FROM BSC_TAB_IND_GROUPS_B
WHERE IND_GROUP_ID = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Group_Id
and tab_id = -1 ;
SELECT COUNT(BK.INDICATOR)
into l_count
FROM BSC_KPIS_B BK, BSC_TAB_INDICATORS TI
WHERE BK.IND_GROUP_ID = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Group_Id
AND TI.TAB_ID = p_tab_id
AND BK.INDICATOR = TI.INDICATOR;
update BSC_TAB_IND_GROUPS_B
set GROUP_TYPE = 0
where IND_GROUP_ID = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Group_Id;
select count(tab_id)
into l_count
from BSC_TAB_INDICATORS
where indicator = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
BSC_SCORECARD_PUB.Update_Tab_Time_Stamp( FND_API.G_FALSE
,l_Bsc_Tab_Entity_Rec
,x_return_status
,x_msg_count
,x_msg_data);
select share_flag, source_indicator
into l_share_flag, l_kpi_source
from BSC_KPIS_B
where indicator = p_kpi_id;
select IND_GROUP_ID
into l_kpi_group_id
from BSC_KPIS_B
where indicator = p_kpi_id;
select count(indicator)
into l_count
from BSC_TAB_INDICATORS
where tab_id = l_Bsc_Kpi_Entity_Rec.Bsc_Tab_Id
and indicator = l_Master_KPI_id;
select indicator
into l_Bsc_Kpi_Entity_Rec.Bsc_kpi_id
from BSC_TAB_INDICATORS
where tab_id = l_Bsc_Kpi_Entity_Rec.Bsc_Tab_Id
and indicator in (select indicator from BSC_KPIS_B
where source_indicator = l_Master_KPI_id
);
BSC_KPI_PUB.Delete_Kpi( FND_API.G_FALSE
,l_Bsc_Kpi_Entity_Rec
,x_return_status
,x_msg_count
,x_msg_data);
select count(indicator)
into l_kpi_count
from BSC_TAB_INDICATORS
where indicator in (select indicator
from BSC_KPIS_B
where source_indicator = l_Master_KPI_id );
select prototype_flag, decode(CONFIG_TYPE, 7, 1, 0)
into l_prototype_flag, l_Simulation_Tree_flag
from BSC_KPIS_B
where indicator = l_Master_KPI_id;
BSC_KPI_PUB.Delete_Kpi_In_Tab( FND_API.G_FALSE
,l_Bsc_Kpi_Entity_Rec
,x_return_status
,x_msg_count
,x_msg_data);
BSC_KPI_PUB.Update_Kpi_Time_Stamp( FND_API.G_FALSE
,l_Bsc_Kpi_Entity_Rec
,x_return_status
,x_msg_count
,x_msg_data);
SELECT indicator
INTO l_kpi_id
FROM BSC_TAB_INDICATORS
WHERE indicator IN (SELECT indicator
FROM bsc_kpis_b
WHERE source_indicator = l_Master_KPI_id );
BSC_KPI_PUB.Delete_Kpi_Defaults( FND_API.G_FALSE
, l_Bsc_Kpi_Entity_Rec
, x_return_status
, x_msg_count
, x_msg_data);
BSC_KPI_PVT.Delete_Kpi( FND_API.G_FALSE
,l_Bsc_Kpi_Entity_Rec
,x_return_status
,x_msg_count
,x_msg_data);
BSC_KPI_PVT.Update_Kpi( FND_API.G_FALSE
, l_Bsc_Kpi_Entity_Rec
, x_return_status
, x_msg_count
, x_msg_data);
UPDATE bsc_kpis_b
SET source_indicator = NULL
WHERE indicator = l_kpi_id;
FND_MESSAGE.SET_NAME('BSC','BSC_NO_MASTER_DELETE');
select count(b.indicator)
into l_kpi_count
from bsc_kpis_b a, bsc_tab_indicators b
where a.ind_group_id = l_kpi_group_id
and a.indicator = b.indicator
and b.tab_id = p_tab_id;
BSC_KPI_GROUP_PUB.Delete_Kpi_Group( FND_API.G_FALSE
,l_Bsc_Kpi_Group_Rec
,x_return_status
,x_msg_count
,x_msg_data);
BSC_SCORECARD_PUB.Update_Tab_Time_Stamp( FND_API.G_FALSE
,l_Bsc_Tab_Entity_Rec
,x_return_status
,x_msg_count
,x_msg_data);
BSC_CUSTOM_VIEW_PUB.Delete_Custom_View_Links
(
p_commit => FND_API.G_FALSE
, p_tab_id => p_tab_id
, p_obj_id => p_kpi_id
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
select count(indicator)
into l_count
from BSC_TAB_INDICATORS
where tab_id = p_tab_id
and (indicator = p_kpi_id or
indicator in (select indicator
from BSC_KPIS_B
where source_indicator = p_kpi_id)
);
SELECT indicator
FROM bsc_kpis_b
WHERE ind_group_id = p_kpi_group_id
AND SHARE_FLAG <> 2
AND PROTOTYPE_FLAG <> 2;
SELECT COUNT(*)
INTO l_count
FROM BSC_KPIS_B
WHERE ind_group_id = p_kpi_group_id
and SHARE_FLAG <> 2
and PROTOTYPE_FLAG <> 2;
BSC_KPI_PUB.Update_Kpi_Time_Stamp(
FND_API.G_FALSE
,l_Bsc_kpi_Entity_Rec
,x_return_status
,x_msg_count
,x_msg_data );
BSC_KPI_PUB.Update_Kpi_Time_Stamp(
FND_API.G_FALSE
,l_Bsc_kpi_Entity_Rec
,x_return_status
,x_msg_count
,x_msg_data );
function Is_Analysis_Option_Selected(
p_kpi_id IN number
,p_analysis_group_id IN number
,p_option_id IN number
,p_parent_option_id IN number
,p_grandparent_Option_id IN number
) return varchar2 IS
l_Bsc_kpi_Entity_Rec BSC_KPI_PUB.Bsc_kpi_Entity_Rec;
temp := BSC_KPI_PUB.Is_Analysis_Option_Selected(
l_Bsc_kpi_Entity_Rec
,x_return_status
,x_msg_count
,x_msg_data );
end Is_Analysis_Option_Selected;
select count(option_id)
into l_count
from BSC_KPI_ANALYSIS_OPTIONS_B
where indicator = p_kpi_id
and analysis_group_id = p_analysis_group_id
and option_id = p_option_id
and parent_option_id = p_parent_option_id
and grandparent_option_id = p_grandparent_option_id;
select count(option_id)
into l_count
from BSC_KPI_ANALYSIS_OPTIONS_B
where indicator = p_kpi_id
and analysis_group_id = p_analysis_group_id
and option_id = p_option_id
and parent_option_id = p_parent_option_id
and grandparent_option_id = p_grandparent_option_id;
select DEPENDENCY_FLAG
into p_Bsc_kpi_Entity_Rec.Bsc_Dependency_Flag
from BSC_KPI_ANALYSIS_GROUPS
where INDICATOR = p_kpi_id
and ANALYSIS_GROUP_ID = p_analysis_group_id;
select DEPENDENCY_FLAG
into p_Bsc_kpi_Entity_Rec.Bsc_gp_Dependency_Flag
from BSC_KPI_ANALYSIS_GROUPS
where INDICATOR = p_kpi_id
and ANALYSIS_GROUP_ID = 1;
v_sql := 'select LEVEL_VIEW_NAME from bsc_bis_dim_levels_v where SHORT_NAME = :1';
Update_RelationShips
-------------------------------------------------------------------------------------------------------------------*/
PROCEDURE Update_RelationShips(
p_commit IN VARCHAR2 := FND_API.G_TRUE
,p_Dim_Level_Id IN number
,p_Short_Name IN varchar2
,p_Parents IN varchar2
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
) IS
x_array t_of_varchar2;
SELECT PARENT_DIM_LEVEL_ID
FROM BSC_SYS_DIM_LEVEL_RELS
WHERE DIM_LEVEL_ID = v_Dim_Level_Id;
BSC_DIMENSION_LEVELS_PUB.Delete_Dim_Level_Relation(v_commit, v_Dim_Level_Rec
,x_return_status, x_msg_count, x_msg_data);
if v_Dim_Level_Rec.Bsc_Flag = -999 then /* Flag for Update */
--DBMS_OUTPUT.PUT_LINE('Update_RelationShip Delete ' || v_Dim_Level_Rec.Bsc_Parent_Level_Id );
BSC_DIMENSION_LEVELS_PUB.Delete_Dim_Level_Relation(v_commit, v_Dim_Level_Rec
,x_return_status, x_msg_count, x_msg_data);
END Update_RelationShips;
select count(*)
into v_count
from BSC_SYS_DIM_LEVEL_RELS_V
where SHORT_NAME = p_SHORT_NAME
AND PARENT_SHORT_NAME = p_PARENT_SHORT_NAME;
BSC_DIMENSION_LEVELS_PUB.Delete_Dim_Level_Relation(FND_API.G_FALSE, v_Dim_Level_Rec
,x_return_status, x_msg_count, x_msg_data);
update BSC_TABS_B
set tab_index = Bsc_Tab_Index(i).Bsc_Tab_Index
where tab_id = Bsc_Tab_Index(i).Bsc_Tab_Id;
procedure Update_Tab_Parent(
p_commit IN VARCHAR2 := FND_API.G_TRUE
,p_tab_id IN number
,p_parent_tab_id IN number
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
) is
l_Bsc_Tab_Entity_Rec BSC_SCORECARD_PUB.Bsc_Tab_Entity_Rec;
select name into l_tab_name
from BSC_TABS_VL where tab_id = p_tab_id;
select name into l_tab_name
from BSC_TABS_VL where tab_id = p_tab_id;
select name into l_tab_name
from BSC_TABS_VL where tab_id = p_parent_tab_id;
select max(tab_index) + 1
into l_Bsc_Tab_Entity_Rec.Bsc_Tab_Index
from BSC_TABS_B
where tab_id = p_parent_tab_id;
BSC_SCORECARD_PUB.Update_Tab( FND_API.G_FALSE
,l_Bsc_Tab_Entity_Rec
,x_return_status
,x_msg_count
,x_msg_data);
BSC_SCORECARD_PUB.Update_Tab_Time_Stamp( FND_API.G_FALSE
,l_Bsc_Tab_Entity_Rec
,x_return_status
,x_msg_count
,x_msg_data);
BSC_SCORECARD_PUB.Update_Tab_Time_Stamp( FND_API.G_FALSE
,l_Bsc_Tab_Entity_Rec
,x_return_status
,x_msg_count
,x_msg_data);
BSC_SCORECARD_PUB.Update_System_Time_Stamp( FND_API.G_FALSE
,l_Bsc_Tab_Entity_Rec
,x_return_status
,x_msg_count
,x_msg_data);
x_msg_data := x_msg_data||' -> BSC_PMF_UI_WRAPPER.Update_Tab_Parent ';
x_msg_data := SQLERRM||' at BSC_PMF_UI_WRAPPER.Update_Tab_Parent ';
x_msg_data := x_msg_data||' -> BSC_PMF_UI_WRAPPER.Update_Tab_Parent ';
x_msg_data := SQLERRM||' at BSC_PMF_UI_WRAPPER.Update_Tab_Parent ';
end Update_Tab_Parent;
select distinct measure_name
into l_Bsc_Dataset_Rec.Bsc_Dataset_Name
from BISFV_PERFORMANCE_MEASURES
where upper(measure_short_name) = upper(p_short_name);
SELECT count(*)
INTO l_count
FROM bsc_sys_datasets_vl
WHERE measure_id1 = (
SELECT measure_id
FROM bsc_sys_measures
WHERE short_name = p_short_name);
/*SELECT DISTINCT region_code Region
INTO l_region_code
FROM ak_region_items
WHERE attribute1= 'MEASURE'
AND attribute2 = p_measure_short_name;*/
FUNCTION is_group_selected
(
p_tab_id IN NUMBER
, p_group_id IN NUMBER
) RETURN VARCHAR2
IS
l_count NUMBER;
SELECT count(*)
INTO l_count
FROM BSC_TAB_IND_GROUPS_B
WHERE tab_id = p_tab_id
AND ind_group_id = p_group_id;
END is_group_selected;
SELECT Tab_ID
FROM BSC_TABS_VL
WHERE Name = p_Tab_Name;
* Internal procedure called from Update_Kpi_Periodicities only.
* The API is called seperately for objective and its shared objectives if exists.
*/
PROCEDURE Update_Obj_Cal_properties (
p_commit IN VARCHAR2
,p_calendar_id IN NUMBER
,l_Bsc_Kpi_Entity_Rec IN OUT NOCOPY BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
,l_periodicities_tbl IN BSC_BIS_LOCKS_PUB.t_numberTable
,l_current_periods IN BSC_BIS_LOCKS_PUB.t_numberTable
,l_Dft_periodicity_id IN NUMBER
,l_action_flag 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 rownum-1 newRow, periodicity_id
FROM bsc_kpi_periodicities
WHERE indicator = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
UPDATE bsc_kpi_periodicities
SET display_order = cd_reorder.newRow
WHERE periodicity_id = cd_reorder.periodicity_id
AND indicator = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
UPDATE bsc_kpis_b
SET calendar_id = p_calendar_id
,periodicity_id = l_Dft_periodicity_id
,last_update_date = SYSDATE
WHERE indicator = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
UPDATE bsc_kpi_periodicities
SET user_level0 = C_DISABLE_FLAG
,user_level1 = C_DISABLE_FLAG
WHERE indicator = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id
AND periodicity_id = l_Dft_periodicity_id;
UPDATE bsc_kpi_properties
SET property_value = 1
WHERE indicator = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id
AND property_code = 'DB_TRANSFORM'
AND property_value = 2;
UPDATE bsc_kpi_periodicities
SET target_level = 1
WHERE indicator = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
END Update_Obj_Cal_properties;
PROCEDURE Update_Periodicity_Props(
p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_Indicator IN NUMBER
,p_calendar_id IN NUMBER
,p_Periods_In_Graph IN FND_TABLE_OF_NUMBER := NULL
,p_Periodicity_Id_Tbl IN FND_TABLE_OF_NUMBER := NULL
,p_Number_Of_Years IN NUMBER := 10
,p_Previous_Years IN NUMBER := 5
,p_cascade_shared IN BOOLEAN := FALSE
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_Max_Periods NUMBER := 0;
SELECT
indicator
FROM
bsc_kpis_b
WHERE
source_indicator = p_Indicator
AND PROTOTYPE_FLAG <> 2;
SELECT
kp. periodicity_id,
p.periodicity_type,
DECODE(p.periodicity_type, 1 , kp.num_of_years, p.num_of_periods) max_periods
FROM
bsc_kpi_periodicities kp,
bsc_sys_periodicities_vl p
WHERE
kp.indicator = p_Indicator
AND kp.periodicity_id = p.periodicity_id
AND p.calendar_id = p_calendar_id;
SAVEPOINT UpdatePeriodicityPropsPUB;
BSC_KPI_PUB.Update_Kpi_Periodicity (
p_commit => FND_API.G_FALSE
,p_Bsc_Kpi_Entity_Rec => l_Bsc_Kpi_Entity_Rec
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
) ;
BSC_KPI_PUB.Update_Kpi_Periodicity (
p_commit => FND_API.G_FALSE
,p_Bsc_Kpi_Entity_Rec => l_Bsc_Kpi_Entity_Rec
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
ROLLBACK TO UpdatePeriodicityPropsPUB;
ROLLBACK TO UpdatePeriodicityPropsPUB;
ROLLBACK TO UpdatePeriodicityPropsPUB;
x_msg_data := x_msg_data||' ->BSC_PMF_UI_WRAPPER.Update_Periodicity_Props ';
x_msg_data := SQLERRM||'BSC_PMF_UI_WRAPPER.Update_Periodicity_Props ';
ROLLBACK TO UpdatePeriodicityPropsPUB;
x_msg_data := x_msg_data||' ->BSC_PMF_UI_WRAPPER.Update_Periodicity_Props ';
x_msg_data := SQLERRM||'BSC_PMF_UI_WRAPPER.Update_Periodicity_Props ';
END Update_Periodicity_Props;
Name :- Update_Kpi_Periodicities
Description :- This procedure will assign the Periodicities to one KPI
It will replace the previous peridicities with the new set of
periodicities.
Input :- p_kpi_id Indicator ID (Master)
p_calendar_id Calendar Id. It is mandatory
p_periodicity_ids Set of periodicities separates by ","
when it is null it will assign allo the Calendar
periodicities
p_Dft_periodicity_id Default kpi periodicity Id
It is null it will set the first periodicity
id in the p_periodicity_ids LIST
Creator : - William Cano APR 16 / 2003
/********************************************************************************/
procedure Update_Kpi_Periodicities(
p_commit IN VARCHAR2 -- := FND_API.G_FALSE
,p_kpi_id IN NUMBER
,p_calendar_id IN NUMBER
,p_periodicity_ids IN VARCHAR2
,p_Dft_periodicity_id IN NUMBER
,p_Periods_In_Graph IN FND_TABLE_OF_NUMBER := NULL
,p_Periodicity_Id_Tbl IN FND_TABLE_OF_NUMBER := NULL
,p_Number_Of_Years IN NUMBER := 10
,p_Previous_Years IN NUMBER := 5
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_periodicity_ids VARCHAR2(300);
SELECT indicator
FROM BSC_KPIS_B
WHERE source_indicator = p_kpi_id
AND PROTOTYPE_FLAG <> 2;
SELECT
NVL(kp.num_of_years,0),
NVL(kp.previous_years,0)
FROM
bsc_kpi_periodicities kp,
bsc_sys_periodicities_vl p
WHERE
kp.indicator(+) = p_kpi_id
AND kp.periodicity_id(+) = p.periodicity_id
AND p.calendar_id = p_calendar_id;
SELECT indicator_type, config_type, calendar_id, periodicity_id, prototype_flag
INTO l_Indic_Type,l_Indic_Config_Type, l_cur_cal_id, l_cur_periodicity_id, l_cur_prototype_flag
FROM bsc_kpis_b
WHERE indicator = p_kpi_id;
SELECT COUNT(1)
FROM bsc_sys_periodicities_vl P
, bsc_sys_calendars_b C
WHERE C.calendar_id =:1
AND P.calendar_id = C.calendar_id';
SELECT P.periodicity_id
,DECODE (P.yearly_flag, 1, C.fiscal_year, 1 )
,P.periodicity_type
,P.name
FROM bsc_sys_periodicities_vl P
, bsc_sys_calendars_b C
WHERE C.calendar_id =:1
AND P.calendar_id = C.calendar_id
AND periodicity_id NOT IN (
SELECT periodicity_id FROM bsc_kpi_periodicities where indicator = :2
)';
SELECT COUNT(1)
INTO l_old_periodicities
FROM bsc_kpi_periodicities
WHERE indicator = p_kpi_id;
DELETE bsc_kpi_periodicities
WHERE indicator IN
(SELECT indicator
FROM bsc_kpis_b
WHERE indicator = :1 OR source_indicator = :2)';
SELECT COUNT(1)
INTO l_new_periodicities
FROM bsc_kpi_periodicities
WHERE indicator = p_kpi_id;
SELECT COUNT(1)
FROM bsc_sys_periodicities_vl P
, bsc_sys_calendars_b C
WHERE C.calendar_id =:1
AND P.calendar_id = C.calendar_id
AND periodicity_id = :2';
SELECT periodicity_id
INTO l_Dft_periodicity_id
FROM bsc_kpi_periodicities
WHERE indicator = p_kpi_id
AND rownum = 1;
UPDATE bsc_kpi_periodicities
SET user_level0=C_ENABLE_FLAG, user_level1=C_ENABLE_FLAG
WHERE user_level0=C_DISABLE_FLAG
AND user_level1=C_DISABLE_FLAG
AND periodicity_id = l_cur_periodicity_id
AND indicator IN
(SELECT indicator
FROM bsc_kpis_b
WHERE indicator = p_kpi_id
OR source_indicator=p_kpi_id);
SELECT P.periodicity_id
,DECODE (P.yearly_flag, 1, C.fiscal_year, 1 )
,P.periodicity_type
,P.name
FROM bsc_sys_periodicities_vl P
, bsc_sys_calendars_b C
WHERE C.calendar_id =:1
AND P.calendar_id = C.calendar_id';
DELETE bsc_kpi_periodicities p
WHERE p.indicator in
(SELECT k.indicator
FROM bsc_kpis_b k
WHERE k.indicator = p_kpi_id
OR k.source_indicator = p_kpi_id);
Update_Obj_Cal_properties (
p_commit => p_commit
,p_calendar_id => p_calendar_id
,l_Bsc_Kpi_Entity_Rec => l_Bsc_Kpi_Entity_Rec
,l_periodicities_tbl => l_periodicities_tbl
,l_current_periods => l_current_periods
,l_Dft_periodicity_id => l_Dft_periodicity_id
,l_action_flag => l_action_flag
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
SELECT COUNT(1)
INTO l_Count_Tables
FROM bsc_kpi_data_tables
WHERE indicator = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id
AND periodicity_id = l_periodicity_id;
INSERT INTO bsc_kpi_data_tables
( indicator
, periodicity_id
, dim_set_id
, level_comb)
VALUES
( l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id
, l_periodicity_id
, 0
, '?'
);
Update_Obj_Cal_properties (
p_commit => p_commit
,p_calendar_id => p_calendar_id
,l_Bsc_Kpi_Entity_Rec => l_Bsc_Kpi_Entity_Rec
,l_periodicities_tbl => l_periodicities_tbl
,l_current_periods => l_current_periods
,l_Dft_periodicity_id => l_Dft_periodicity_id
,l_action_flag => l_action_flag
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
Update_Periodicity_Props (
p_commit => p_commit
,p_Indicator => p_kpi_id
,p_calendar_id => p_calendar_id
,p_Periods_In_Graph => p_Periods_In_Graph
,p_Periodicity_Id_Tbl => p_Periodicity_Id_Tbl
,p_Number_Of_Years => p_Number_Of_Years
,p_Previous_Years => p_Previous_Years
,p_cascade_shared => TRUE
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
BSC_DESIGNER_PVT.ActionFlag_Change(p_kpi_id , BSC_DESIGNER_PVT.G_ActionFlag.GAA_Update);
BSC_DESIGNER_PVT.ActionFlag_Change(cd.indicator , BSC_DESIGNER_PVT.G_ActionFlag.GAA_Update);
x_msg_data := x_msg_data||' -> BSC_PMF_UI_WRAPPER.Update_Kpi_Periodicities ';
x_msg_data := SQLERRM||' at BSC_PMF_UI_WRAPPER.Update_Kpi_Periodicities ';
end Update_Kpi_Periodicities;
SELECT name, short_name
FROM bsc_tabs_vl
WHERE tab_id = p_tab_id;
SELECT c.function_name,d.name
FROM bsc_oaf_analysys_opt_comb_v a,bsc_tab_indicators b,bis_indicators c,bsc_kpis_vl d
WHERE a.indicator = b.indicator
AND a.dataset_id = c.dataset_id
AND a.indicator = d.indicator
AND b.tab_id = p_tab_id;
FND_MESSAGE.SET_NAME('BSC','BSC_SCR_DELETE_VIEW_ERR');
FND_MESSAGE.SET_NAME('BSC','BSC_AG_SCORECARD_DELETE');
FND_MESSAGE.SET_NAME('BSC','BSC_SCR_DELETE');
x_msg_data := x_msg_data||' -> BSC_PMF_UI_WRAPPER.Delete_Tab ';
x_msg_data := SQLERRM||' at BSC_PMF_UI_WRAPPER.Delete_Tab ';
x_msg_data := x_msg_data||' -> BSC_PMF_UI_WRAPPER.Delete_Tab ';
x_msg_data := SQLERRM||' at BSC_PMF_UI_WRAPPER.Delete_Tab ';
SELECT UNIQUE NAME, tab_view_id
FROM BSC_TAB_VIEWS_VL
WHERE tab_id = p_tab_id;
PROCEDURE Add_Or_Update_Tab_Logo
(
p_tab_id IN NUMBER
,p_image_id IN NUMBER
,p_file_name IN VARCHAR2
,p_description IN VARCHAR2
,p_width IN NUMBER
,p_height IN NUMBER
,p_mime_type IN VARCHAR2
,x_image_id OUT NOCOPY NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_count NUMBER;
SAVEPOINT AddOrUpdateTabLogo;
SELECT COUNT(0)
INTO l_count
FROM bsc_sys_images bsi,
bsc_sys_images_map_vl bsim
WHERE bsim.source_type =BSC_PMF_UI_WRAPPER.C_SCORECARD_LOGO_TYPE
AND bsim.source_code = p_tab_id
AND bsim.type = BSC_PMF_UI_WRAPPER.C_SCORECARD_LOGO_TYPE
AND bsim.image_id = p_image_id
AND bsim.image_id = bsi.image_id;
SELECT COUNT(0)
INTO l_count
FROM bsc_sys_images_map_TL
WHERE source_type =BSC_PMF_UI_WRAPPER.C_SCORECARD_LOGO_TYPE
AND source_code = p_tab_id
AND type = BSC_PMF_UI_WRAPPER.C_SCORECARD_LOGO_TYPE
AND image_id = p_image_id
AND source_lang = USERENV('LANG');
UPDATE BSC_SYS_IMAGES
SET FILE_NAME = p_file_name,
DESCRIPTION = p_description,
WIDTH = p_width,
HEIGHT = p_height,
MIME_TYPE = p_mime_type,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = fnd_global.user_id,
LAST_UPDATE_LOGIN = fnd_global.login_id,
FILE_BODY = EMPTY_BLOB()
WHERE IMAGE_ID = p_image_id;
ROLLBACK TO AddOrUpdateTabLogo;
x_msg_data := 'Update to BSC_SYS_IMAGES failed' || SQLERRM;
BSC_SYS_IMAGES_MAP_PKG.UPDATE_ROW
(
X_SOURCE_TYPE => BSC_PMF_UI_WRAPPER.C_SCORECARD_LOGO_TYPE
,X_SOURCE_CODE => p_tab_id
,X_TYPE => BSC_PMF_UI_WRAPPER.C_SCORECARD_LOGO_TYPE
,X_IMAGE_ID => p_image_id
,X_CREATION_DATE => SYSDATE
,X_CREATED_BY => fnd_global.user_id
,X_LAST_UPDATE_DATE => SYSDATE
,X_LAST_UPDATED_BY => fnd_global.user_id
,X_LAST_UPDATE_LOGIN => fnd_global.login_id
);
SELECT BSC_SYS_IMAGE_ID_S.NEXTVAL
INTO l_next_image_id
FROM dual;
BSC_SYS_IMAGES_PKG.INSERT_ROW
(
X_IMAGE_ID => l_next_image_id
,X_FILE_NAME => p_file_name
,X_DESCRIPTION => p_description
,X_WIDTH => p_width
,X_HEIGHT => p_height
,X_MIME_TYPE => p_mime_type
,X_CREATED_BY => fnd_global.user_id
,X_LAST_UPDATED_BY => fnd_global.user_id
,X_LAST_UPDATE_LOGIN => fnd_global.login_id
);
ROLLBACK TO AddOrUpdateTabLogo;
x_msg_data := 'Insertion to BSC_SYS_IMAGES_PKG failed' || SQLERRM;
BSC_SYS_IMAGES_MAP_PKG.UPDATE_ROW
(
X_SOURCE_TYPE => BSC_PMF_UI_WRAPPER.C_SCORECARD_LOGO_TYPE
,X_SOURCE_CODE => p_tab_id
,X_TYPE => BSC_PMF_UI_WRAPPER.C_SCORECARD_LOGO_TYPE
,X_IMAGE_ID => p_image_id
,X_CREATION_DATE => SYSDATE
,X_CREATED_BY => fnd_global.user_id
,X_LAST_UPDATE_DATE => SYSDATE
,X_LAST_UPDATED_BY => fnd_global.user_id
,X_LAST_UPDATE_LOGIN => fnd_global.login_id
);
ROLLBACK TO AddOrUpdateTabLogo;
END Add_Or_Update_Tab_Logo;
SELECT BSC_SYS_IMAGE_ID_S.NEXTVAL
INTO l_next_image_id
FROM dual;
BSC_SYS_IMAGES_PKG.INSERT_ROW
(
X_IMAGE_ID => l_next_image_id
,X_FILE_NAME => p_file_name
,X_DESCRIPTION => p_description
,X_WIDTH => p_width
,X_HEIGHT => p_height
,X_MIME_TYPE => p_mime_type
,X_CREATED_BY => fnd_global.user_id
,X_LAST_UPDATED_BY => fnd_global.user_id
,X_LAST_UPDATE_LOGIN=> fnd_global.login_id
);
x_msg_data := 'Insertion to BSC_SYS_IMAGES_PKG failed' || SQLERRM;
BSC_SYS_IMAGES_MAP_PKG.INSERT_ROW
(
X_ROWID => l_str
,X_SOURCE_TYPE => BSC_PMF_UI_WRAPPER.C_SCORECARD_LOGO_TYPE
,X_SOURCE_CODE => p_obj_id
,X_TYPE => BSC_PMF_UI_WRAPPER.C_SCORECARD_LOGO_TYPE
,X_IMAGE_ID => l_next_image_id
,X_CREATION_DATE => SYSDATE
,X_CREATED_BY => fnd_global.user_id
,X_LAST_UPDATE_DATE => SYSDATE
,X_LAST_UPDATED_BY => fnd_global.user_id
,X_LAST_UPDATE_LOGIN => fnd_global.login_id
);
PROCEDURE Delete_Tab_Logo
(
p_tab_id IN BSC_TABS_B.tab_id%TYPE
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
)IS
CURSOR c_tab_logo IS
SELECT image_id
FROM bsc_sys_images_map_tl
WHERE source_type = BSC_PMF_UI_WRAPPER.C_SCORECARD_LOGO_TYPE
AND source_code = p_tab_id;
SAVEPOINT DeleteTabLogo;
DELETE FROM bsc_sys_images_map_tl
WHERE image_id =l_image_id;
DELETE FROM bsc_sys_images
WHERE image_id =l_image_id;
ROLLBACK TO DeleteTabLogo;
END Delete_Tab_Logo;