[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
REM | 23-APR-04 adrao Added Update/Delete APIs |
REM | 06-MAY-04 hcamcho added dbdrv hint "plb" to package |
REM | 07-MAY-04 adrao KPI End-to-end Phase II: Modified CRUD APIs |
REM | to handle 1 Dimension per Analysis Option |
REM | 11-MAY-04 adrao KPI End-to-end Phase II: Modified CRUD APIs |
REM | to add Parameter Portlet Region Code as an |
REM | nested region item and not add Dimension |
REM | Level region items. |
REM | 14-MAY-04 adrao Added 2 additional regions/analysis options |
REM | 17-MAY-04 adrao Enh: Modified Create_Kpi, Create_Tab, |
REM | Create_Kpi_Group to use short_name |
REM | 19-MAY-04 adrao Used Page Function Name for KPI, KPI Group |
REM | 20-MAY-04 adrao Added the Create_Dimension API |
REM | 24-MAY-04 adrao Made Function User Name more descriptive |
REM | Bug #3644445 |
REM | 26-MAY-04 akchan remove method Create_PPX_Region_Item() |
REM | change variable names on methods |
REM | Does_Dim_Grp_Exist() and Does_KPI_Exist() |
REM | use Constant for String "MEASURE_NOTARGET" |
REM | 27-MAY-04 adrao Modularized all CRUD APIs |
REM | 04-JUN-04 adrao Added Autofactoring for Measure, Compare To |
REM | 10-JUN-04 adrao Modified Create_Measure and Update_Measure |
REM | to disable Projection |
REM | 14-JUN-04 adrao Modified Update and Create AK Regions for |
REM | Bug#3688263 and Bug#3688300 |
REM | 14-JUN-04 adrao Added APIs to check for Time Dimension Objs |
REM | 17-JUN-04 adrao Modified Bug3698962 and Bug3698936 to remove |
REM | ATTRIBUTE3 to NULL except CHANGE_MEASURE and |
REM | set ATTRIBUTE10 to "Y" for MEASURE_NOTARGET |
REM | 18-JUN-04 adrao Added API Get_Next_Region_Code_By_AO for |
REM | Enh#3691035 |
REM | 22-JUN-04 adrao Bug#3717084, added a new column |
REM | BSC_DB_MEASURE_GROUPS_TL.SHORT_NAME |
REM | 05-JUL-04 adrao Bug#3742500, Enabled Graph display at the |
REM | measure level region item in create/update |
REM | 14-JUL-04 adrao Bug#3766260 Modified Does_KPI_Exist() to |
REM | check for Deleted Objectives |
REM | 15-JUL-04 adrao Fixed Bug#3766839 to get KPI_ID from |
REM | measure short_name instead of Page Function |
REM | 19-JUL-04 adrao added API is_XTD_Enabled () for |
REM | Bug#3767168 for Parameter Portlets |
REM | 21-JUL-04 adrao added API Get_S2EObjective_With_XTD for |
REM | Bug#3780082 |
REM | 21-JUL-04 adrao made short_name nonunique. prototype_flag =2 |
REM | Bug#3781764 |
REM | 22-JUL-04 adrao Fixed Bug#3770986 (please see bug) |
REM | 02-AUG-04 adrao Fixed Bug#3802192 |
REM | 04-AUG-04 adrao Added API Populate_DBI_Calendar() |
REM | for Bug#3809721 |
REM | 11-AUG-04 adrao Added API Get_Region_Codes_By_Short_Name |
REM | for Bug#3777647 |
REM | 18-AUG-04 adrao Added API Check_XTD_Summarization() for |
REM | Bug#3831859 |
REM | 23-AUG-04 adrao Fixed Bug#3844823 |
REM | 24-AUG-04 adrao Updated Report Name for Bug#3844849 |
REM | 02-SEP-04 adrao Added API Get_Kpi_Details for Bug#3814292 |
REM | 09-SEP-04 adrao Added API Get_Change_Disp_Type_By_Mask() for |
REM | Bug#3876413 |
REM | 20-SEP-04 ankgoel Added Get_Pmf_Metadata_By_Objective for |
REM | Bug#3759819 |
REM | 21-SEP-04 adrao Added exception handling to Create_Measure |
REM | API for Bug#3755656 |
REM | 27-SEP-04 ankgoel Modified Get_Pmf_Metadata_By_Objective for |
REM | Bug#3916377 |
REM | 29-SEP-04 adrao Modified base column attribute3 based on the |
REM | format masking for Bug#3919666 |
REM | 30-SEP-04 rpenneru Added Get_S2ESCR_DeleteMessage, |
REM | , Delete_S2E_Metadata for bug#3893949 |
REM | 05-OCT-04 ankgoel Bug#3933075 Moved Get_Pmf_Metadata_By_Objective
REM | and C_AK_DATASOURCE and get_Region_Code to |
REM | BSCCSUBB.pls. Moved C_BSC_UNDERSCORE to |
REM | BSCUTILB.pls. Removed Get_KpiId_By_DatasetId |
REM | 30-SEP-04 rpenneru Modified for bug#3938515 |
REM | 30-SEP-04 skchoudh Modified for bug#3940652, changed |
REM | Validate_Kpi_Delete |
REM | 02-NOV-04 akoduri Modified for bug#3977463 |
REM | 21-DEC-04 adrao Modified for 8i compatibility, Bug#4079898 |
REM | 28-JAN-05 visuri added Has_Compare_To_Or_Plan() , modified |
REM | Create_Addl_Ak_Region_Items() for Enh. 4065089 |
REM | 08-FEB-05 visuri Modified Create_Addl_Ak_Region_Items() for |
REM | Enh. 4065098 |
REM | 11-Feb-05 sawu Bug#4057761: create unique tab name, group |
REM | name |
REM | 22-FEB-05 adrao Autogenerated Measures Enhancement for Report |
REM | Designer |
REM | 01-MAR-05 adrao Fixed Bug#4213345 and modify the API |
REM | Get_Dim_Info_From_ParamPortlet |
REM | 07-MAR-05 vtulasi Added procedure Get_Dep_Obj_Func_Name |
REM | for bug# 3786130 |
REM | 21-Feb-05 rpenneru Enh#4059160, Add FA as property to Custom KPIs|
REM | 10-MAR-05 adrao added API Convert_AutoGen_To_ViewBased for |
REM | Convert AGR to VBR enhancement. |
REM | 18-MAR-05 adrao Made modification to ensure Duplication of |
REM | reports and added a few util APIs |
REM | 24-MAR-05 visuri Modified Delete_Misc_Region_Items() Bug 4231753|
REM | 30-MAR-05 adrao Modified Create AG report to manage deleted |
REM | measures |
REM | 21-Feb-05 rpenneru Bug#4287317, Pass p_Measure_Short_Name to |
REM | createBscBisMetaData instread of default NULL|
REM | 12-APR-05 kyadamak Modified for bug# 4288237 calling |
REM | update_measure only from report designer |
REM | 22-APR-05 akoduri Enhancement#3865711 -- Obsolete Seeded Objects|
REM | 26-APR-05 visuri Enhancement#4309381 --Length of WHERE_CLAUSE of|
REM | BSC_SYS_DI_LEVELS_BY_GROUP increased to 4000 |
REM | 27-APR-05 adrao Fixed bug#4327887 - Moved validation to lower |
REM | PL/SQL API Create_Bsc_Bis_Metadata |
REM | 04-MAY-05 adrao Always create default measure group for AG |
REM | 03-MAY-2005 akoduri Enh #4268374 -- Weighted Average Measures |
REM | 11-MAY-2005 adrao Created the following APIs for |
REM | selective cascading of Dimensions and Measures|
REM | Has_Measure_Column_Changed |
REM | Has_Time_Dim_Obj_Changed |
REM | Has_Non_Time_Dim_Obj_Changed |
REM | 19-MAY-2005 visuri GSCC Issues bug 4363884 |
REM | 22-JUN-2005 ppandey Bug #4447283, used cursor. |
REM | 28-JUN-2005 rpenneru Bug #4447654, Has_Measure_Column_Changed |
REM | modified |
REM | 30-JUN-2005 akoduri Bug#4370200 , Default Number of Rows not |
REM | getting saved |
REM | 08-jul-05 ASHANKAR added the method is_Scorecard_From_Reports |
REM | 02-JUN-2005 adrao Added APIs for Calendar Enhancement (4376162) |
REM | 13-JUL-2005 adrao Enabled addition of Time based Periodicities |
REM | in the Calendar+Periodicity format #4376162 |
REM | 15-JUL-2005 akoduri Provided warning messasges for strucutural |
REM | and color changes #4492177 |
REM | 04-AUG-2005 adrao Fixed Bug#4520525 |
REM | 16-AUG-2005 akoduri Bug#4482355 Removing attribute_code and |
REM| attribute2 dependency in Report Designer |
REM | 25-AUG-2005 adrao Filtered out Rolling Dimension Object type |
REM | by calling API Get_Non_Rolling_Dim_Obj for |
REM | Bug#4566634 |
REM | 25-AUG-2005 rpenneru Report formFunction parameters should have |
REM | 'pParameters=pParamIds@Y'. bug#4560857 |
REM | 05-SEP-2005 adrao Modifed AGReport creation without AS_OF_DATE |
REM | Bug#4552657 |
REM | 14-SEP-2005 adrao Fixed Bug#4599432 for updating non seeded |
REM | existing source measures |
REM | 07-SEP-2005 adrao Implemented dynamic Parameter Portlet builder |
REM | as required by Bug#4558279 |
REM | 23-SEP-2005 akoduri Bug #4389280 Removing all the measures from |
REM | an AG Report is not showing warning |
REM | 26-SEP-2005 arhegde bug# 4624100 Moved get_format_mask code to |
REM | BSC_BIS_CUSTOM_KPI_UTIL_PUB since pure BIS can use it too. |
REM | 28-SEP-2005 akoduri Bug #4626935 Unchecking all the periodicities|
REM | is not updating bsc_kpi_periodicities |
REM | 26-SEP-2005 ashankar Bug#4619367 Made chnages to the following API|
REM | 1.Has_Time_Dim_Obj_Changed |
REM | 2.Has_Measure_Column_Changed |
REM | 30-SEP-2005 adrao Modified code to ensure that the compare is |
REM | populated when the measure is created |
REM | Fixed Bug#4638384 |
REM |07-NOV-2005 arhegde bug# 4720781 Handle bisviewer.showReport changed to|
REM | OA.jsp?page=/oracle/apps/bis/report/webui/BISReportPG |
REM | 25-DEC-2005 adrao Added APIs following APIs for Enh#3909868 |
REM | - Migrate_AGR_To_PLSQL |
REM | - Is_Primary_Source_Of_Measure |
REM | - Cascade_Attr_Code_Into_Measure |
REM | - Cascade_Changes_Into_Forumla |
REM | 03-JAN-2005 adrao Added API for Is_Dim_Associated_To_Objective()|
REM | for Bug#4923006 |
REM | 06-JAN-2006 akoduri Enh#4739401 - Hide Dimensions/Dim Objects |
REM | 17-JAN-2006 rpenneru bug#4741919 - AG report deletion |
REM | 17-JAN-2006 adrao Modified Migrate_AGR_To_PLSQL() |
REM | for Bug#4958056 |
REM | 19-JAN-2006 adrao Added API Migrate_To_Existing_Source() for |
REM | Enhancement#4952167 |
REM | 07-FEB-2006 hengliu Bug#4955493 - Not overwrite global menu/title|
REM | 07-FEB-2006 ppandey Bug#4771854 - Rolling Periods for AG |
REM | 16-FEB-2006 adrao added ABS() to DBMS_UTILITY.GET_TIME for |
REM | Bug#5039894 |
REM | 08-MAR-2006 adrao Bug#5081180 - Ensured that Delete_Dimension |
REM | is not called in Delete_AG_Bsc_Metadata() |
REM | when there is no dimension |
REM | 18-MAY-2006 akoduri Bug #5072842 Added Have_Measures_Changed API|
REM | to check for structural changes |
REM | 19-MAY-2006 ankgoel Bug #5201116 Get correct Comparison Source |
REM | 22-MAY-2006 akoduri Bug #5104426 Data Source getting updated |
REM | BSC Type measures |
REM | 11-OCT-2006 akoduri Bug #5554168 Issue with Measures having |
REM | different short names in bis_indicators & |
REM | bsc_sys_measures |
REM | 09-feb-2007 ashankar Simulation Tree Enhacement 5386112 |
REM | 22-Mar-2007 ashankar Fixed the Bug#5930808 |
REM +=======================================================================+
*/
FUNCTION Is_Assign_To_Tab
(
p_indicator IN BSC_KPIS_B.indicator%TYPE
,p_tabId IN BSC_TABS_B.tab_id%TYPE
)RETURN VARCHAR2 IS
l_count NUMBER;
SELECT COUNT(0)
INTO l_count
FROM bsc_tab_indicators
WHERE tab_id = p_tabId
AND indicator = p_indicator;
PROCEDURE Update_Kpi_End_To_End(
p_Commit IN VARCHAR2 := FND_API.G_FALSE
,p_Param_Portlet_Region_Code IN VARCHAR2
,p_Page_Function_Name IN VARCHAR2
,p_Kpi_Portlet_Function_Name IN VARCHAR2
,p_Measure_Name IN VARCHAR2
,p_Measure_Short_Name IN VARCHAR2 := NULL
,p_Measure_Description IN VARCHAR2 := NULL
,p_Measure_Type IN NUMBER := NULL
,p_Measure_Operation IN VARCHAR2 := BSC_BIS_MEASURE_PUB.c_SUM
,p_Dataset_Format_Id IN NUMBER := NULL
,p_Dataset_Autoscale_Flag IN NUMBER := NULL
,p_Measure_Increase_In_Measure IN VARCHAR2 := NULL
,p_Measure_Random_Style IN NUMBER := NULL
,p_Measure_Min_Act_Value IN NUMBER := NULL
,p_Measure_Max_Act_Value IN NUMBER := NULL
,p_Measure_App_Id IN NUMBER := NULL
,p_Func_Area_Short_Name IN VARCHAR2 := NULL
,x_Return_Status OUT NOCOPY VARCHAR2
,x_Msg_Count OUT NOCOPY NUMBER
,x_Msg_Data OUT NOCOPY VARCHAR2
) IS
x_Non_Time_Dimension_Groups BSC_VARCHAR2_TBL_TYPE;
SAVEPOINT UpdateEndToEndKPI;
SELECT
m.measure_id
INTO
l_Bsc_Measure_Id
FROM
bsc_sys_measures m,
bsc_sys_datasets_vl d,
bis_indicators i
WHERE
i.dataset_id = d.dataset_id AND
d.measure_id1 = m.measure_id AND
i.short_name = p_measure_short_name;
BSC_BIS_MEASURE_PUB.Update_Measure(
p_Commit => p_commit
,p_Dataset_Id => l_dataset_id
,p_Dataset_Source => BSC_BIS_MEASURE_PUB.c_BSC
,p_Dataset_Name => p_measure_name
,p_Dataset_Help => p_measure_description
,p_Dataset_Measure_Id1 => l_Bsc_Measure_Id
,p_Dataset_Operation => NULL
,p_Dataset_Measure_Id2 => NULL
,p_Dataset_Format_Id => p_dataset_format_id
,p_Dataset_Color_Method => NULL
,p_Dataset_Autoscale_Flag => p_dataset_autoscale_flag
,p_Dataset_Projection_Flag => NULL
,p_Measure_Short_Name => p_measure_short_name
,p_Measure_Act_Data_Src_Type => 'AK'
,p_Measure_Act_Data_Src => l_Region_Code || '.' || p_measure_short_name
,p_Measure_Comparison_Source => NULL
,p_Measure_Operation => p_measure_operation
,p_Measure_Uom_Class => NULL
,p_Measure_Increase_In_Measure => p_measure_increase_in_measure
,p_Measure_Random_Style => p_measure_random_style
,p_Measure_Min_Act_Value => p_measure_min_act_value
,p_Measure_Max_Act_Value => p_measure_max_act_value
,p_Measure_Min_Bud_Value => NULL
,p_Measure_Max_Bud_Value => NULL
,p_Measure_App_Id => p_Measure_App_Id
,p_Measure_Col => NULL
,p_Measure_Group_Id => NULL
,p_Measure_Projection_Id => BSC_BIS_KPI_CRUD_PUB.C_NO_PROJECTION
,p_Measure_Type => p_measure_type
,p_Measure_Apply_Rollup => NULL
,p_Measure_Function_Name => l_Region_Code
,p_Measure_Enable_Link => 'Y'
,p_Time_Stamp => NULL
,p_Dimension1_Id => x_all_dim_group_ids(1)
,p_Dimension2_Id => x_all_dim_group_ids(2)
,p_Dimension3_Id => x_all_dim_group_ids(3)
,p_Dimension4_Id => x_all_dim_group_ids(4)
,p_Dimension5_Id => x_all_dim_group_ids(5)
,p_Dimension6_Id => x_all_dim_group_ids(6)
,p_Dimension7_Id => x_all_dim_group_ids(7)
,p_Y_Axis_Title => NULL
,p_func_area_short_name => p_Func_Area_Short_Name
,x_Return_Status => x_return_status
,x_Msg_Count => x_msg_count
,x_Msg_Data => x_msg_data
);
BSC_BIS_KPI_CRUD_PUB.Update_Kpi_Analysis_Option(
p_Commit => p_Commit
,p_Kpi_Id => l_Kpi_Id
,p_Dataset_Id => l_Dataset_Id
,p_Measure_Name => p_Measure_Name
,p_Measure_Description => p_Measure_Description
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
FND_MESSAGE.SET_TOKEN('ACTION', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON','UPDATE'));
BSC_BIS_KPI_CRUD_PUB.Update_Measure_Region_Item(
p_commit => p_commit,
p_measure_short_name => p_measure_short_name,
p_sequence_number => l_sequence,
p_kpi_id => l_Kpi_Id,
p_Analysis_Option => l_Analysis_Option_Id,
p_dataset_format_id => p_dataset_format_id,
p_dataset_autoscale_flag => p_dataset_autoscale_flag,
p_Analysis_Option_Name => p_Measure_Name,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
BSC_BIS_KPI_CRUD_PUB.Update_Addl_Ak_Region_Items(
p_commit => p_commit
, p_Region_Code => l_Region_Code
, p_Region_Application_Id => BSC_BIS_KPI_CRUD_PUB.C_BSC_APPLICATION_ID
, p_Display_Format => Get_Format_Mask(p_Dataset_Format_Id)
, p_Format_Id => p_Dataset_Format_Id
, p_Measure_Short_Name => p_measure_short_name
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
BSC_BIS_KPI_CRUD_PUB.Update_Region_By_AO (
p_Commit => p_Commit
, p_Kpi_Id => l_Kpi_Id
, p_Analysis_Option_Id => l_Analysis_Option_Id
, p_Dim_Set_Id => 0
, p_Region_Name => p_Measure_Name
, p_Region_Description => p_Measure_Description
, p_Region_Application_Id => BSC_BIS_KPI_CRUD_PUB.C_BSC_APPLICATION_ID -- BSC
, p_Disable_View_By => 'N'
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
) ;
FND_FORM_FUNCTIONS_PKG.UPDATE_ROW
(
X_FUNCTION_ID => BSC_BIS_KPI_CRUD_PUB.Get_Function_Id_By_Name(l_Region_Code)
,X_WEB_HOST_NAME => ''
,X_WEB_AGENT_NAME => ''
,X_WEB_HTML_CALL => BSC_BIS_KPI_CRUD_PUB.c_bisreportpg
,X_WEB_ENCRYPT_PARAMETERS => 'N'
,X_WEB_SECURED => 'N'
,X_WEB_ICON => ''
,X_OBJECT_ID => NULL
,X_REGION_APPLICATION_ID => NULL
,X_REGION_CODE => ''
,X_FUNCTION_NAME => l_Region_Code
,X_APPLICATION_ID => NULL
,X_FORM_ID => NULL
,X_PARAMETERS => l_Form_Parameters
,X_TYPE => 'JSP'
,X_USER_FUNCTION_NAME => p_Measure_Name
,X_DESCRIPTION => p_Measure_Description
,X_LAST_UPDATE_DATE => SYSDATE
,X_LAST_UPDATED_BY => FND_GLOBAL.USER_ID
,X_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID
);
ROLLBACK TO UpdateEndToEndKPI;
ROLLBACK TO UpdateEndToEndKPI;
x_msg_data := x_msg_data||' -> BSC_BIS_KPI_CRUD_PUB.Update_Kpi_End_To_End ';
x_msg_data := SQLERRM||' at BSC_BIS_KPI_CRUD_PUB.Update_Kpi_End_To_End ';
ROLLBACK TO UpdateEndToEndKPI;
x_msg_data := x_msg_data||' -> BSC_BIS_KPI_CRUD_PUB.Update_Kpi_End_To_End ';
x_msg_data := SQLERRM||' at BSC_BIS_KPI_CRUD_PUB.Update_Kpi_End_To_End ';
ROLLBACK TO UpdateEndToEndKPI;
END Update_Kpi_End_To_End;
Start the DELETE APIs from here on.
*/
PROCEDURE Delete_Kpi_End_To_End(
p_Commit IN VARCHAR2 := FND_API.G_FALSE
,p_Param_Portlet_Region_Code IN VARCHAR2
,p_Measure_Short_Name IN VARCHAR2 := NULL
,p_Page_Function_Name IN VARCHAR2
,p_Kpi_Portlet_Function_Name IN VARCHAR2
,x_Return_Status OUT NOCOPY VARCHAR2
,x_Msg_Count OUT NOCOPY NUMBER
,x_Msg_Data OUT NOCOPY VARCHAR2
) IS
l_Dataset_Source VARCHAR2(10) := NULL;
SAVEPOINT DeleteEndToEndKPI;
SELECT COUNT(1) INTO l_Count
FROM BSC_SYS_DATASETS_B
WHERE DATASET_ID = l_Dataset_Id;
FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_DELETE_MESSAGE');
SELECT COUNT(1) INTO l_Count
FROM BSC_KPIS_B
WHERE INDICATOR = l_Kpi_Id;
FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_DELETE_MESSAGE');
BSC_BIS_KPI_CRUD_PUB.Delete_AK_Metadata(
p_Commit => p_Commit
, p_Region_Code => l_Region_Code
, p_Region_Code_Application_Id => BSC_BIS_KPI_CRUD_PUB.C_BSC_APPLICATION_ID
, x_Return_Status => x_Return_Status
, x_Msg_Count => x_Msg_Count
, x_Msg_Data => x_Msg_Data
);
BSC_BIS_KPI_CRUD_PUB.Delete_Measure_Region_Item(
p_Commit => p_commit
,p_Param_Portlet_Region_Code => l_Region_Code
,p_Measure_Short_Name => p_Measure_Short_Name
,p_Application_Id => BSC_BIS_KPI_CRUD_PUB.C_BSC_APPLICATION_ID -- BSC
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
FND_MESSAGE.SET_TOKEN('ACTION', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON','UPDATE'));
BSC_BIS_MEASURE_PUB.Delete_Measure(
p_Commit => p_commit
,p_Dataset_Id => l_Dataset_Id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
SELECT NVL(MAX(ANALYSIS_OPTION0), 0)
INTO l_Last_Analysis_Option
FROM BSC_KPI_ANALYSIS_MEASURES_B
WHERE INDICATOR = l_kpi_Id;
, p_Deleted_AO_Index => l_Analysis_Option_Id
, p_Param_Portlet_Region_Code => l_Parameter_Portlet
, x_Return_Status => x_Return_Status
, x_Msg_Count => x_Msg_Count
, x_Msg_Data => x_Msg_Data
);
ELSE -- End - IF NOT Get_Num_Measures_By_Kpi > 1 (delete all the KPI Metatada and Regions)
-- Start deleting the PMV/AK Metadata
-- Delete the Measure Metadata
--DBMS_OUTPUT.PUT_LINE('Outside Delete_Measure_Region_Item' );
BSC_BIS_KPI_CRUD_PUB.Delete_AK_Metadata(
p_Commit => p_Commit
, p_Region_Code => l_Region_Code
, p_Region_Code_Application_Id => BSC_BIS_KPI_CRUD_PUB.C_BSC_APPLICATION_ID
, x_Return_Status => x_Return_Status
, x_Msg_Count => x_Msg_Count
, x_Msg_Data => x_Msg_Data
);
At this point, Delete ALL the KPI Metadata and info related to the KPI.
*/
--DBMS_OUTPUT.PUT_LINE('Outside BSC_PMF_UI_WRAPPER.Delete_Kpi' );
BSC_PMF_UI_WRAPPER.Delete_Kpi(
p_commit => p_commit
,p_Kpi_Id => l_Kpi_Id
,x_Return_Status => x_Return_Status
,x_Msg_Count => x_Msg_Count
,x_Msg_Data => x_Msg_Data
);
BSC_BIS_MEASURE_PUB.Delete_Measure(
p_Commit => p_Commit
,p_Dataset_Id => l_Dataset_Id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
BSC_BIS_KPI_CRUD_PUB.Delete_AK_Metadata(
p_Commit => p_Commit
, p_Region_Code => l_Parameter_Portlet
, p_Region_Code_Application_Id => BSC_BIS_KPI_CRUD_PUB.C_BSC_APPLICATION_ID
, x_Return_Status => x_Return_Status
, x_Msg_Count => x_Msg_Count
, x_Msg_Data => x_Msg_Data
);
BSC_BIS_DIMENSION_PUB.Delete_Dimension
( p_commit => p_Commit
, p_dim_short_name => l_Parameter_Portlet
, x_return_status => x_Return_Status
, x_msg_count => x_Msg_Count
, x_msg_data => x_Msg_Data
);
ROLLBACK TO DeleteEndToEndKPI;
ROLLBACK TO DeleteEndToEndKPI;
x_msg_data := x_msg_data||' -> BSC_BIS_KPI_CRUD_PUB.Delete_Kpi_End_To_End ';
x_msg_data := SQLERRM||' at BSC_BIS_KPI_CRUD_PUB.Delete_Kpi_End_To_End ';
ROLLBACK TO DeleteEndToEndKPI;
x_msg_data := x_msg_data||' -> BSC_BIS_KPI_CRUD_PUB.Delete_Kpi_End_To_End ';
x_msg_data := SQLERRM||' at BSC_BIS_KPI_CRUD_PUB.Delete_Kpi_End_To_End ';
ROLLBACK TO DeleteEndToEndKPI;
END Delete_Kpi_End_To_End;
SELECT DISTINCT a.attribute2, a.display_sequence
FROM ak_region_items a
WHERE a.region_code = p_region_code
AND ATTRIBUTE1 IN ('DIMENSION LEVEL',
'DIM LEVEL SINGLE VALUE',
'DIMENSION VALUE',
'HIDE_VIEW_BY',
'HIDE_VIEW_BY_SINGLE',
'HIDE PARAMETER',
'VIEWBY PARAMETER',
'HIDE_DIM_LVL',
'HIDE DIMENSION LEVEL',
'HIDE VIEW BY DIMENSION',
'HIDE_VIEW_BY_DIM_SINGLE')
AND a.attribute2 LIKE '%+%'
AND a.attribute2 NOT LIKE 'TIME_COMPARISON_TYPE%'
ORDER BY a.display_sequence;
SELECT a.dimension_id
FROM bis_dimensions a
WHERE UPPER(a.short_name) = UPPER(l_dimension_grp(l_counter));
SELECT a.short_name
INTO l_measure_short_name
FROM bsc_sys_measures a
WHERE a.measure_id =
(SELECT b.measure_id1
FROM bsc_sys_datasets_b b
WHERE b.dataset_id = x_dataset_id);
SELECT A.SHORT_NAME
INTO l_Measure_Short_Name
FROM BSC_SYS_MEASURES A
WHERE A.MEASURE_ID =
(SELECT B.MEASURE_ID1
FROM BSC_SYS_DATASETS_B B
WHERE B.DATASET_ID = p_Dataset_Id);
SELECT COUNT(1)
INTO l_Has_One_Row
FROM BSC_SYS_DIM_GROUPS_TL A
WHERE A.SHORT_NAME = p_Param_Portlet_Region_Code
AND A.LANGUAGE = USERENV('LANG');
SELECT COUNT(1)
INTO l_Has_One_Row
FROM BSC_KPIS_B A
WHERE A.SHORT_NAME = p_Portlet_Function_Name
AND A.PROTOTYPE_FLAG <> BSC_KPI_PUB.DELETE_KPI_FLAG;
SELECT NVL(MAX(A.DISPLAY_SEQUENCE), 0)
INTO l_max_seq_num
FROM AK_REGION_ITEMS A
WHERE A.REGION_CODE = p_Region_Code;
SELECT DISTINCT a.attribute2
FROM ak_region_items a
WHERE a.region_code = Get_Param_Portlet_By_Region(p_region_code)
AND a.attribute2 like '%+%'
AND a.attribute2 NOT LIKE 'TIME_COMPARISON_TYPE%'
AND a.attribute1 <> C_COMP_TO_DIM_LEVEL
UNION
SELECT DISTINCT a.attribute2
FROM ak_region_items a
WHERE a.region_code = p_region_code
AND a.attribute2 like '%+%'
AND a.attribute2 NOT LIKE 'TIME_COMPARISON_TYPE%'
AND a.attribute1 <> C_COMP_TO_DIM_LEVEL;
CURSOR c_DimObj_Name IS SELECT a.name
FROM bsc_sys_dim_levels_tl a, bsc_sys_dim_levels_b b
WHERE a.dim_level_id = b.dim_level_id
AND a.language = USERENV('LANG')
AND b.short_name = l_dimension_object;
SELECT a.tab_id
INTO l_tab_id
FROM bsc_tabs_b a
WHERE a.short_name = p_page_function_name;
SELECT A.IND_GROUP_ID
FROM BSC_TAB_IND_GROUPS_B A
WHERE A.SHORT_NAME = p_Kpi_Portlet_Function_Name;
SELECT a.PERIODICITY_ID
FROM BSC_sys_periodicities a
WHERE a.CALENDAR_id = l_calendar_id
AND a.PERIODICITY_TYPE = 9;
SELECT CALENDAR_ID
FROM BSC_SYS_CALENDARS_B
WHERE EDW_CALENDAR_TYPE_ID = 1
AND EDW_CALENDAR_ID = p_Calendar;
SELECT CALENDAR_ID
INTO l_kpi_calendar_id
FROM BSC_KPIS_B
WHERE INDICATOR = p_kpi_id;
BSC_PMF_UI_WRAPPER.Update_Kpi_Periodicities(
p_commit => p_commit
,p_kpi_id => p_kpi_id
,p_calendar_id => l_calendar_id
,p_periodicity_ids => l_periodicity_ids
,p_Dft_periodicity_id => l_Dft_periodicity_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
BSC_PMF_UI_WRAPPER.Update_Kpi_Periodicities(
p_commit => p_Commit
,p_kpi_id => p_Kpi_Id
,p_calendar_id => l_Calendar_Id
,p_periodicity_ids => NULL
,p_Dft_periodicity_id => NULL
,x_return_status => x_Return_Status
,x_msg_count => x_Msg_Count
,x_msg_data => x_Msg_Data
);
PROCEDURE Update_Actual_Data_Source(
p_Kpi_Id IN NUMBER
, p_Dataset_Id IN NUMBER
, p_Measure_Short_Name IN VARCHAR2
, p_Create_Region_Per_AO IN VARCHAR2 := FND_API.G_FALSE
) IS
l_value VARCHAR2(1000);
UPDATE BIS_INDICATORS
SET ACTUAL_DATA_SOURCE = l_Value
, ACTUAL_DATA_SOURCE_TYPE = 'AK'
, FUNCTION_NAME = l_Region_Code
, ENABLE_LINK = 'Y'
WHERE DATASET_ID = p_Dataset_Id;
END Update_Actual_Data_Source;
PROCEDURE Update_Dim_Dim_Level_Columns(
p_dim_object_short_name VARCHAR2,
p_non_time_dimension_objects bsc_varchar2_tbl_type,
p_non_time_counter NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
l_index NUMBER;
SELECT d.total_flag, d.comparison_flag, d.where_clause
INTO l_total_flag, l_comparison_flag, l_where_clause
FROM bsc_sys_dim_groups_tl a,
bis_dimensions b,
bis_levels c,
bsc_sys_dim_levels_by_group d,
bsc_sys_dim_levels_b e
WHERE c.short_name = p_non_time_dimension_objects(l_index)
AND c.dimension_id = b.dimension_id
AND b.short_name = a.short_name
AND a.language = userenv('LANG')
AND a.dim_group_id = d.dim_group_id
AND e.short_name = p_non_time_dimension_objects(l_index)
AND e.dim_level_id = d.dim_level_id;
SELECT d.dim_group_id, d.dim_level_id
INTO l_dim_group_id, l_dim_level_id
FROM bsc_sys_dim_groups_tl a,
bsc_sys_dim_levels_by_group d,
bsc_sys_dim_levels_b e
WHERE d.dim_group_id = a.dim_group_id
AND a.short_name = p_dim_object_short_name
AND a.language = userenv('LANG')
AND e.short_name = p_non_time_dimension_objects(l_index)
AND e.dim_level_id = d.dim_level_id;
x_Msg_Data := BSC_APPS.Get_Message('BSC_DIP_ERR_UPDATE_DIM_REL');
UPDATE bsc_sys_dim_levels_by_group
SET total_flag = l_total_flag,
comparison_flag = l_comparison_flag,
where_clause = l_where_clause
WHERE dim_group_id = l_dim_group_id
AND dim_level_id = l_dim_level_id;
x_msg_data := x_msg_data||' -> BSC_BIS_KPI_CRUD_PUB.Update_Dim_Dim_Level_Columns ';
x_msg_data := SQLERRM||' at BSC_BIS_KPI_CRUD_PUB.Update_Dim_Dim_Level_Columns ';
x_msg_data := x_msg_data||' -> BSC_BIS_KPI_CRUD_PUB.Update_Dim_Dim_Level_Columns ';
x_msg_data := SQLERRM||' at BSC_BIS_KPI_CRUD_PUB.Update_Dim_Dim_Level_Columns ';
END Update_Dim_Dim_Level_Columns;
Update Section Added here
*/
/* -*********************************************** */
PROCEDURE Update_Measure_Region_Item(
p_Commit VARCHAR2,
p_Measure_Short_Name VARCHAR2,
p_Sequence_Number NUMBER,
p_Kpi_Id NUMBER,
p_Analysis_Option NUMBER := NULL,
p_Dataset_Format_Id NUMBER,
p_Dataset_Autoscale_Flag NUMBER,
p_Analysis_Option_Name VARCHAR2,
x_Return_Status OUT NOCOPY VARCHAR2,
x_Msg_Count OUT NOCOPY NUMBER,
x_Msg_Data OUT NOCOPY VARCHAR2)
IS
l_region_item_rec BIS_AK_REGION_PUB.Bis_Region_Item_Rec_Type;
BIS_PMV_REGION_ITEMS_PVT.UPDATE_REGION_ITEMS(
p_Commit => p_Commit
,p_Region_Code => l_Region_Code
,p_Region_Application_Id => BSC_BIS_KPI_CRUD_PUB.C_BSC_APPLICATION_ID
,p_Region_Item_Tbl => l_Region_Item_Table_Measure
,x_Return_Status => x_Return_Status
,x_Msg_Count => x_Msg_Count
,x_Msg_Data => x_Msg_Data
);
x_msg_data := x_msg_data||' -> BSC_BIS_KPI_CRUD_PUB.Update_Measure_Region_Item ';
x_msg_data := SQLERRM||' at BSC_BIS_KPI_CRUD_PUB.Update_Measure_Region_Item ';
x_msg_data := x_msg_data||' -> BSC_BIS_KPI_CRUD_PUB.Update_Measure_Region_Item ';
x_msg_data := SQLERRM||' at BSC_BIS_KPI_CRUD_PUB.Update_Measure_Region_Item ';
END Update_Measure_Region_Item;
Procedure to Delete the Measure Region Item
*/
PROCEDURE Delete_Measure_Region_Item(
p_commit VARCHAR2 := FND_API.G_FALSE,
p_Param_Portlet_Region_Code VARCHAR2,
p_Measure_Short_Name VARCHAR2,
p_Application_Id NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
l_Attribute_Code_Tbl BISVIEWER.t_char ;
BIS_PMV_REGION_ITEMS_PVT.DELETE_REGION_ITEMS
( p_commit => p_commit
, p_region_code => p_Param_Portlet_Region_Code
, p_region_application_id => p_Application_Id
, p_Attribute_Code_Tbl => l_Attribute_Code_Tbl
, p_Attribute_Appl_Id_Tbl => l_Attribute_App_Id_Tbl
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
END Delete_Measure_Region_Item;
SELECT A.ANALYSIS_OPTION0
, A.ANALYSIS_OPTION1
, A.ANALYSIS_OPTION2
, A.SERIES_ID
FROM BSC_KPI_ANALYSIS_MEASURES_B A
WHERE A.INDICATOR = p_Kpi_Id
AND A.DATASET_ID = p_Dataset_Id;
BSC_BIS_KPI_MEAS_PUB.Delete_KPI_Multi_Groups_Opts
( p_commit => p_Commit
, p_kpi_id => p_Kpi_Id
, p_data_source => BSC_BIS_MEASURE_PUB.c_BSC
, p_Option_0 => l_Option0
, p_Option_1 => l_Option1
, p_Option_2 => l_Option2
, p_Sid => l_Series_Id
, p_time_stamp => NULL
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
SELECT COUNT(1)
INTO l_count
FROM BSC_KPI_ANALYSIS_MEASURES_B K
WHERE K.INDICATOR = p_Kpi_Id;
SELECT K.INDICATOR
INTO l_Kpi_Id
FROM BSC_KPIS_B K
WHERE K.SHORT_NAME = p_Page_Function_Name
AND K.PROTOTYPE_FLAG <> BSC_KPI_PUB.DELETE_KPI_FLAG;
PROCEDURE Delete_Dim_Level_Region_Item(
p_commit VARCHAR2 := FND_API.G_FALSE
,p_Application_Id NUMBER
,p_Non_Time_Counter NUMBER
,p_Non_Time_Dimension_Objects bsc_varchar2_tbl_type
,p_Non_Time_Dimension_Groups bsc_varchar2_tbl_type
,p_Time_Counter NUMBER
,p_Time_Dimension_Objects bsc_varchar2_tbl_type
,p_Time_Dimension_Groups bsc_varchar2_tbl_type
,p_Region_Code VARCHAR2
,x_Return_Status OUT NOCOPY VARCHAR2
,x_Msg_Count OUT NOCOPY NUMBER
,x_Msg_Data OUT NOCOPY VARCHAR2)
IS
l_Attribute_Code_Tbl BISVIEWER.t_char ;
BIS_PMV_REGION_ITEMS_PVT.DELETE_REGION_ITEMS
( p_commit => p_commit
, p_region_code => p_Region_Code
, p_region_application_id => p_Application_Id
, p_Attribute_Code_Tbl => l_Attribute_Code_Tbl
, p_Attribute_Appl_Id_Tbl => l_Attribute_App_Id_Tbl
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
END Delete_Dim_Level_Region_Item;
SELECT F.Function_Id
INTO l_Fun_Id
FROM FND_FORM_FUNCTIONS F
WHERE F.Function_Name = p_Kpi_Portlet_Function_Name;
Update_Kpi_Analysis_Option - Updates the KPI Analysis Option Name and Description..
*/
PROCEDURE Update_Kpi_Analysis_Option (
p_Commit VARCHAR2 := FND_API.G_FALSE
,p_Kpi_Id NUMBER
,p_Dataset_Id NUMBER
,p_Measure_Name VARCHAR2
,p_Measure_Description VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2)
IS
l_Option0 NUMBER;
SELECT A.ANALYSIS_OPTION0
FROM BSC_KPI_ANALYSIS_MEASURES_B A
WHERE A.INDICATOR = p_Kpi_Id
AND A.DATASET_ID = p_Dataset_Id;
UPDATE BSC_KPI_ANALYSIS_OPTIONS_TL
SET NAME = p_Measure_Name
,HELP = p_Measure_Description
,SOURCE_LANG = USERENV('LANG')
WHERE INDICATOR = p_Kpi_Id
AND ANALYSIS_GROUP_ID = 0
AND OPTION_ID = l_Option0
AND PARENT_OPTION_ID = 0
AND GRANDPARENT_OPTION_ID = 0
AND USERENV('LANG') IN (LANGUAGE, SOURCE_LANG);
UPDATE BSC_KPI_ANALYSIS_MEASURES_TL
SET NAME = p_Measure_Name
,HELP = p_Measure_Description
,SOURCE_LANG = USERENV('LANG')
WHERE INDICATOR = p_Kpi_Id
AND ANALYSIS_OPTION0 = l_Option0
AND ANALYSIS_OPTION1 = 0
AND ANALYSIS_OPTION2 = 0
AND SERIES_ID = 0
AND USERENV('LANG') IN (LANGUAGE, SOURCE_LANG);
END Update_Kpi_Analysis_Option;
SELECT A.Display_Sequence
INTO l_Sequence_Id
FROM AK_REGION_ITEMS A
WHERE A.REGION_APPLICATION_ID = p_Region_Application_Id
AND A.REGION_CODE = p_Region_Code
AND A.ATTRIBUTE_APPLICATION_ID = p_Attribute_Application_Id
AND A.ATTRIBUTE_CODE = p_Attribute_Code;
SELECT
i.dataset_id
INTO l_dataset_id
FROM
bis_indicators i
WHERE
i.short_name = p_Measure_Short_Name;
SELECT T.NAME
FROM BSC_TABS_VL T, BSC_TAB_INDICATORS K
WHERE T.TAB_ID = K.TAB_ID
AND T.TAB_ID <> l_Tab_Id
AND K.INDICATOR = l_Kpi_Id;
SELECT ANALYSIS_OPTION0
FROM BSC_KPI_ANALYSIS_MEASURES_B
WHERE DATASET_ID = p_Dataset_Id
AND INDICATOR = p_Kpi_Id;
PROCEDURE Update_Region_By_AO (
p_Commit VARCHAR2 := FND_API.G_FALSE
, p_Kpi_Id IN NUMBER
, p_Analysis_Option_Id IN NUMBER
, p_Dim_Set_Id IN NUMBER
, p_Region_Name IN VARCHAR2
, p_Region_Description IN VARCHAR2
, p_Region_Application_Id IN NUMBER
, p_Disable_View_By IN VARCHAR2 := 'N'
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
) IS
l_report_region_rec BIS_AK_REGION_PUB.Bis_Region_Rec_Type;
BIS_PMV_REGION_PVT.UPDATE_REGION
(
p_commit => p_Commit
,p_Report_Region_Rec => l_report_region_rec
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
x_msg_data := x_msg_data||' -> BSC_BIS_KPI_CRUD_PUB.Update_Region_By_AO ';
x_msg_data := SQLERRM||' at BSC_BIS_KPI_CRUD_PUB.Update_Region_By_AO ';
x_msg_data := x_msg_data||' -> BSC_BIS_KPI_CRUD_PUB.Update_Region_By_AO ';
x_msg_data := SQLERRM||' at BSC_BIS_KPI_CRUD_PUB.Update_Region_By_AO ';
END Update_Region_By_AO;
SELECT COUNT(1)
INTO l_Count
FROM AK_REGIONS
WHERE REGION_CODE = p_Region_Code;
SELECT COUNT(1)
INTO l_Count
FROM AK_REGION_ITEMS
WHERE REGION_CODE = p_Region_Code
AND ( (ATTRIBUTE1 = 'DIMENSION LEVEL') OR
(ATTRIBUTE1 = 'DIM LEVEL SINGLE VALUE') OR
(ATTRIBUTE1 = 'DIMENSION VALUE') OR
(ATTRIBUTE1 = 'HIDE_VIEW_BY') OR
(ATTRIBUTE1 = 'HIDE_VIEW_BY_SINGLE') OR
(ATTRIBUTE1 = 'HIDE PARAMETER') OR
(ATTRIBUTE1 = 'VIEWBY PARAMETER') OR
(ATTRIBUTE1 = 'HIDE_DIM_LVL') OR
(ATTRIBUTE1 = 'HIDE DIMENSION LEVEL') OR
(ATTRIBUTE1 = 'HIDE VIEW BY DIMENSION') OR
(ATTRIBUTE1 = 'HIDE_VIEW_BY_DIM_SINGLE'))
AND attribute2 NOT LIKE 'TIME_COMPARISON_TYPE%';
SELECT A.REGION_APPLICATION_ID
INTO l_App_Id
FROM AK_REGIONS A
WHERE A.REGION_CODE = p_Region_Code;
PROCEDURE Delete_Nested_Region_Item(
p_commit VARCHAR2 := FND_API.G_FALSE,
p_Root_AK_Region VARCHAR2,
p_Application_Id NUMBER,
p_Nested_Region_Code VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_Attribute_Code_Tbl BISVIEWER.t_char ;
BIS_PMV_REGION_ITEMS_PVT.DELETE_REGION_ITEMS
( p_commit => p_commit
, p_region_code => p_Root_AK_Region
, p_region_application_id => p_Application_Id
, p_Attribute_Code_Tbl => l_Attribute_Code_Tbl
, p_Attribute_Appl_Id_Tbl => l_Attribute_App_Id_Tbl
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
END Delete_Nested_Region_Item;
SELECT COUNT(1) INTO l_Count
FROM AK_REGION_ITEMS
WHERE Attribute_Code = p_Short_Name;
SELECT COUNT(1) INTO l_Count
FROM AK_REGIONS
WHERE Region_Code = p_Short_Name;
SELECT COUNT(1) INTO l_exist_cnt
FROM AK_REGION_ITEMS
WHERE REGION_CODE = p_Region_Code
AND ATTRIBUTE_CODE = l_final_string;
PROCEDURE Delete_Misc_Region_Items(
p_commit VARCHAR2 := FND_API.G_FALSE,
p_Region_Code VARCHAR2,
p_Application_Id NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_Attribute_Code_Tbl BISVIEWER.t_char ;
SELECT ATTRIBUTE_CODE, ATTRIBUTE_APPLICATION_ID
FROM AK_REGION_ITEMS
WHERE REGION_CODE = p_Region_Code;
BIS_PMV_REGION_ITEMS_PVT.DELETE_REGION_ITEMS
( p_commit => p_commit
, p_region_code => p_Region_Code
, p_region_application_id => p_Application_Id
, p_Attribute_Code_Tbl => l_Attribute_Code_Tbl
, p_Attribute_Appl_Id_Tbl => l_Attribute_App_Id_Tbl
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
END Delete_Misc_Region_Items;
Update Wrapper for Generic Region Item Creation
*/
PROCEDURE Update_Sim_Generic_Region_Item(
p_commit IN VARCHAR2 := FND_API.G_FALSE
, p_Region_Code IN VARCHAR2
, p_Region_Application_Id IN NUMBER
, p_Attribute_Code IN VARCHAR2
, p_Attribute_Application_Id IN NUMBER
, p_Display_Sequence IN NUMBER
, p_Node_Display_Flag IN VARCHAR2
, p_Required_Flag IN VARCHAR2
, p_Queryable_Flag IN VARCHAR2
, p_Display_Length IN NUMBER
, p_Long_Label IN VARCHAR2
, p_Url IN VARCHAR2
, p_Attribute_Type IN VARCHAR2
, p_Display_Format IN VARCHAR2
, p_Display_Type IN VARCHAR2
, p_Measure_Level IN VARCHAR2
, p_Base_Column IN VARCHAR2
, p_Graph_Position IN NUMBER
, p_Graph_Style IN VARCHAR2
, p_Aggregate_Function IN VARCHAR2
, p_Display_Total IN VARCHAR2
, p_Graph_Measure_Type IN VARCHAR2
, p_Item_Style IN VARCHAR2
, p_Grand_Total_Flag IN VARCHAR2
, p_Nested_Region_Code IN VARCHAR2
, p_Nested_Region_Application_Id IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
) IS
l_region_item_rec BIS_AK_REGION_PUB.Bis_Region_Item_Rec_Type;
BIS_PMV_REGION_ITEMS_PVT.UPDATE_REGION_ITEMS(
p_commit => p_commit
,p_region_code => p_Region_Code
,p_region_application_id => p_Region_Application_Id
,p_Region_Item_Tbl => l_region_item_table_measure
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
END Update_Sim_Generic_Region_Item;
PROCEDURE Update_Addl_Ak_Region_Items(
p_commit IN VARCHAR2 := FND_API.G_FALSE
, p_Region_Code IN VARCHAR2
, p_Region_Application_Id IN NUMBER
, p_Display_Format IN VARCHAR2
, p_Format_id IN NUMBER
, p_Measure_Short_Name IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
) IS
l_Attribute_Code VARCHAR2(30);
SELECT ATTRIBUTE_CODE
FROM AK_REGION_ITEMS
WHERE REGION_CODE = p_Region_Code
AND ATTRIBUTE1 = BSC_BIS_KPI_CRUD_PUB.C_CHANGE_ATTRIBURE_TYPE;
SELECT ATTRIBUTE_CODE
FROM AK_REGION_ITEMS
WHERE REGION_CODE = p_Region_Code
AND ATTRIBUTE1 = BSC_BIS_KPI_CRUD_PUB.C_COMPARE_ATTRIBURE_TYPE;
SELECT ATTRIBUTE_CODE
FROM AK_REGION_ITEMS
WHERE REGION_CODE = p_Region_Code
AND ATTRIBUTE1 = BSC_BIS_KPI_CRUD_PUB.C_GRAND_TOTAL_ATTRIBURE_TYPE
AND ATTRIBUTE3 = p_Measure_Short_Name;
SELECT ATTRIBUTE_CODE
FROM AK_REGION_ITEMS
WHERE REGION_CODE = p_Region_Code
AND ATTRIBUTE1 = BSC_BIS_KPI_CRUD_PUB.C_GRAND_TOTAL_ATTRIBURE_TYPE
AND ATTRIBUTE3 = l_Compare_Column;
Update_Sim_Generic_Region_Item(
p_commit => p_Commit
, p_Region_Code => p_Region_Code
, p_Region_Application_Id => p_Region_Application_Id
, p_Attribute_Code => l_Attribute_Code
, p_Attribute_Application_Id => BSC_BIS_KPI_CRUD_PUB.C_BSC_APPLICATION_ID
, p_Display_Sequence => l_Display_Sequence
, p_Node_Display_Flag => 'Y'
, p_Required_Flag => ' '
, p_Queryable_Flag => ' '
, p_Display_Length => LENGTH(l_Lookup_Value)
, p_Long_Label => l_Lookup_Value
, p_Url => NULL
, p_Attribute_Type => BSC_BIS_KPI_CRUD_PUB.C_COMPARE_ATTRIBURE_TYPE
, p_Display_Format => p_Display_Format
, p_Display_Type => BSC_BIS_KPI_CRUD_PUB.C_AUTOFACTOR_GROUP1
, p_Measure_Level => p_Measure_Short_Name
, p_Base_Column => NULL
, p_Graph_Position => 1
, p_Graph_Style => NULL
, p_Aggregate_Function => NULL
, p_Display_Total => 'Y'
, p_Graph_Measure_Type => NULL
, p_Item_Style => NULL
, p_Grand_Total_Flag => 'Y'
, p_Nested_Region_Code => NULL
, p_Nested_Region_Application_Id => NULL
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
Update_Sim_Generic_Region_Item(
p_commit => p_Commit
, p_Region_Code => p_Region_Code
, p_Region_Application_Id => p_Region_Application_Id
, p_Attribute_Code => l_Attribute_Code
, p_Attribute_Application_Id => BSC_BIS_KPI_CRUD_PUB.C_BSC_APPLICATION_ID
, p_Display_Sequence => l_Display_Sequence
, p_Node_Display_Flag => 'Y'
, p_Required_Flag => ' '
, p_Queryable_Flag => ' '
, p_Display_Length => LENGTH(l_Lookup_Value)
, p_Long_Label => l_Lookup_Value
, p_Url => NULL
, p_Attribute_Type => BSC_BIS_KPI_CRUD_PUB.C_CHANGE_ATTRIBURE_TYPE
, p_Display_Format => NULL
, p_Display_Type => l_Format_Type
, p_Measure_Level => p_Measure_Short_Name
, p_Base_Column => l_Base_Column
, p_Graph_Position => NULL
, p_Graph_Style => NULL
, p_Aggregate_Function => NULL
, p_Display_Total => NULL
, p_Graph_Measure_Type => NULL
, p_Item_Style => NULL
, p_Grand_Total_Flag => NULL
, p_Nested_Region_Code => NULL
, p_Nested_Region_Application_Id => NULL
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
x_msg_data := x_msg_data||' -> BSC_BIS_KPI_CRUD_PUB.Update_Addl_Ak_Region_Items ';
x_msg_data := SQLERRM||' at BSC_BIS_KPI_CRUD_PUB.Update_Addl_Ak_Region_Items ';
x_msg_data := x_msg_data||' -> BSC_BIS_KPI_CRUD_PUB.Update_Addl_Ak_Region_Items ';
x_msg_data := SQLERRM||' at BSC_BIS_KPI_CRUD_PUB.Update_Addl_Ak_Region_Items ';
END Update_Addl_Ak_Region_Items;
SELECT MEASURE_GROUP_ID
INTO l_Measure_Group_Id
FROM BSC_DB_MEASURE_GROUPS_VL
WHERE UPPER(HELP) = UPPER(p_Kpi_Portlet_Function_Name);
BSC_DB_MEASURE_GROUPS_PKG.INSERT_ROW( x_Measure_Group_Id => x_Measure_Group_Id
,x_Help => p_Kpi_Portlet_Function_Name
,x_Short_Name => p_Kpi_Portlet_Function_Name);
SELECT PROTOTYPE_FLAG
FROM BSC_KPIS_B
WHERE INDICATOR = l_Kpi_Id;
SELECT D.TOTAL_FLAG,
D.COMPARISON_FLAG,
D.WHERE_CLAUSE
FROM BSC_SYS_DIM_GROUPS_TL A,
BIS_DIMENSIONS B,
BIS_LEVELS C,
BSC_SYS_DIM_LEVELS_BY_GROUP D,
BSC_SYS_DIM_LEVELS_B E
WHERE C.SHORT_NAME = l_Dim_Obj_Short_Name
AND C.DIMENSION_ID = B.DIMENSION_ID
AND B.SHORT_NAME = A.SHORT_NAME
AND A.LANGUAGE = USERENV('LANG')
AND A.DIM_GROUP_ID = D.DIM_GROUP_ID
AND E.SHORT_NAME = l_Dim_Obj_Short_Name
AND E.DIM_LEVEL_ID = D.DIM_LEVEL_ID;
select dim_group_id, dim_level_id, dim_level_index
from bsc_sys_dim_levels_by_group
where dim_group_id in
(select dim_group_id
from bsc_sys_dim_groups_vl
where short_name = p_Dim_Short_Name);*/
Update_Actual_Data_Source(
p_kpi_id => p_Kpi_Id
, p_dataset_id => Get_Dataset_Id(p_Measure_Short_Name)
, p_measure_short_name => p_Measure_Short_Name
, p_Create_Region_Per_AO => p_Create_Region_Per_AO
);
SELECT FND_FORM_FUNCTIONS_S.nextval
INTO l_fid
FROM SYS.DUAL;
FND_FORM_FUNCTIONS_PKG.INSERT_ROW
( X_ROWID => l_Rowid
,X_FUNCTION_ID => l_Fid
,X_WEB_HOST_NAME => ''
,X_WEB_AGENT_NAME => ''
,X_WEB_HTML_CALL => BSC_BIS_KPI_CRUD_PUB.c_bisreportpg
,X_WEB_ENCRYPT_PARAMETERS => 'N'
,X_WEB_SECURED => 'N'
,X_WEB_ICON => ''
,X_OBJECT_ID => NULL
,X_REGION_APPLICATION_ID => NULL
,X_REGION_CODE => ''
,X_FUNCTION_NAME => l_Region_Code
,X_APPLICATION_ID => NULL
,X_FORM_ID => NULL
,X_PARAMETERS => l_Form_Parameters
,X_TYPE => 'JSP'
,X_USER_FUNCTION_NAME => p_Measure_Name
,X_DESCRIPTION => p_Measure_Description
,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
,X_MAINTENANCE_MODE_SUPPORT => 'NONE'
,X_CONTEXT_DEPENDENCE => 'RESP'
);
SELECT
M.SOURCE
INTO
l_Measure_Source
FROM
bsc_sys_measures m,
bsc_sys_datasets_vl d,
bis_indicators i
WHERE
i.dataset_id = d.dataset_id AND
d.measure_id1 = m.measure_id AND
i.short_name = p_Measure_Short_Name;
SELECT COUNT(1) INTO l_Count
FROM BIS_INDICATORS
WHERE SHORT_NAME = p_measure_short_name;
SELECT
m.measure_id
INTO
l_Bsc_Measure_Id
FROM
bsc_sys_measures m,
bsc_sys_datasets_vl d,
bis_indicators i
WHERE
i.dataset_id = d.dataset_id AND
d.measure_id1 = m.measure_id AND
i.short_name = p_measure_short_name;
BSC_BIS_MEASURE_PUB.Update_Measure(
p_Commit => p_Commit
,p_Dataset_Id => l_Dataset_Id
,p_Dataset_Source => l_Measure_Source
,p_Dataset_Name => p_Measure_Name
,p_Dataset_Help => p_Measure_Description
,p_Dataset_Measure_Id1 => l_Bsc_Measure_Id
,p_Dataset_Operation => NULL
,p_Dataset_Measure_Id2 => NULL
,p_Dataset_Format_Id => p_Dataset_Format_Id
,p_Dataset_Color_Method => NULL
,p_Dataset_Autoscale_Flag => p_Dataset_Autoscale_Flag
,p_Dataset_Projection_Flag => NULL
,p_Measure_Short_Name => p_Measure_Short_Name
,p_Measure_Act_Data_Src_Type => l_Actual_Data_Source_Type
,p_Measure_Act_Data_Src => l_Actual_Data_Source
,p_Measure_Comparison_Source => l_Comparison_Source
,p_Measure_Operation => p_Measure_Operation
,p_Measure_Uom_Class => NULL
,p_Measure_Increase_In_Measure => p_Measure_Increase_In_Measure
,p_Measure_Random_Style => p_Measure_Random_Style
,p_Measure_Min_Act_Value => p_Measure_Min_Act_Value
,p_Measure_Max_Act_Value => p_Measure_Max_Act_Value
,p_Measure_Min_Bud_Value => NULL
,p_Measure_Max_Bud_Value => NULL
,p_Measure_App_Id => p_Measure_App_Id
,p_Measure_Col => NULL
,p_Measure_Group_Id => NULL
,p_Measure_Projection_Id => BSC_BIS_KPI_CRUD_PUB.C_NO_PROJECTION
,p_Measure_Type => p_Measure_Type
,p_Measure_Apply_Rollup => NULL
,p_Measure_Function_Name => l_Function_Name
,p_Measure_Enable_Link => 'Y'
,p_Measure_Obsolete => p_Measure_Obsolete
,p_Type => p_Type
,p_Time_Stamp => NULL
,p_Dimension1_Id => x_All_Dim_Group_Ids(1)
,p_Dimension2_Id => x_All_Dim_Group_Ids(2)
,p_Dimension3_Id => x_All_Dim_Group_Ids(3)
,p_Dimension4_Id => x_All_Dim_Group_Ids(4)
,p_Dimension5_Id => x_All_Dim_Group_Ids(5)
,p_Dimension6_Id => x_All_Dim_Group_Ids(6)
,p_Dimension7_Id => x_All_Dim_Group_Ids(7)
,p_Y_Axis_Title => NULL
,p_Func_Area_Short_Name => p_Func_Area_Short_Name
,x_Return_Status => x_Return_Status
,x_Msg_Count => x_Msg_Count
,x_Msg_Data => x_Msg_Data
);
SELECT COUNT(1) INTO l_Count1
FROM BSC_KPI_ANALYSIS_MEASURES_B B
WHERE B.DATASET_ID = l_Dataset_Id
AND B.INDICATOR = l_Kpi_id;
BSC_ANALYSIS_OPTION_PUB.Update_Data_Series(
p_Commit => p_Commit
, p_Anal_Opt_Rec => l_Anal_Opt_Rec
, x_Return_Status => x_Return_Status
, x_Msg_Count => x_Msg_Count
, x_Msg_Data => x_Msg_Data
);
SELECT A.SHORT_NAME
INTO x_Measure_Short_Name
FROM BSC_SYS_MEASURES A
WHERE A.MEASURE_ID =
(
SELECT B.MEASURE_ID1
FROM BSC_SYS_DATASETS_B B
WHERE B.DATASET_ID = l_Dataset_Id
);
UPDATE BIS_INDICATORS
SET ACTUAL_DATA_SOURCE = p_Region_Function_Name || '.' || l_attribute_code
, ACTUAL_DATA_SOURCE_TYPE = 'AK'
, FUNCTION_NAME = p_Region_Function_Name
, COMPARISON_SOURCE = l_Comparison_Source
, ENABLE_LINK = 'Y'
WHERE DATASET_ID = l_Dataset_Id;
SELECT COUNT(1) INTO l_Count
FROM BSC_SYS_DIM_LEVELS_BY_GROUP
WHERE DIM_GROUP_ID IN
(
SELECT DIM_GROUP_ID
FROM BSC_SYS_DIM_GROUPS_VL
WHERE SHORT_NAME = l_Dimension_Short_Name
);
PROCEDURE Delete_AK_Metadata(
p_Commit IN VARCHAR2
, p_Region_Code IN VARCHAR2
, p_Region_Code_Application_Id IN NUMBER
, x_Return_Status OUT NOCOPY VARCHAR2
, x_Msg_Count OUT NOCOPY NUMBER
, x_Msg_Data OUT NOCOPY VARCHAR2
) IS
BEGIN
SAVEPOINT DeleteAKMetadata;
BSC_BIS_KPI_CRUD_PUB.Delete_Misc_Region_Items(
p_commit => p_commit
, p_Region_Code => p_Region_Code
, p_Application_Id => p_Region_Code_Application_Id
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
BIS_PMV_REGION_PVT.DELETE_REGION
( p_commit => p_commit
, p_Region_Code => p_Region_Code
, p_Region_Application_Id => p_Region_Code_Application_Id
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
FND_FORM_FUNCTIONS_PKG.DELETE_ROW (
X_FUNCTION_ID => BSC_BIS_KPI_CRUD_PUB.Get_Function_Id_By_Name(p_Region_Code)
);
ROLLBACK TO DeleteAKMetadata;
ROLLBACK TO DeleteAKMetadata;
ROLLBACK TO DeleteAKMetadata;
x_msg_data := x_msg_data||' -> BSC_BIS_KPI_CRUD_PUB.Delete_AK_Metadata ';
x_msg_data := SQLERRM||' at BSC_BIS_KPI_CRUD_PUB.Delete_AK_Metadata ';
ROLLBACK TO DeleteAKMetadata;
x_msg_data := x_msg_data||' -> BSC_BIS_KPI_CRUD_PUB.Delete_AK_Metadata ';
x_msg_data := SQLERRM||' at BSC_BIS_KPI_CRUD_PUB.Delete_AK_Metadata ';
END Delete_AK_Metadata;
, p_Deleted_AO_Index IN NUMBER
, p_Param_Portlet_Region_Code IN VARCHAR2
, x_Return_Status OUT NOCOPY VARCHAR2
, x_Msg_Count OUT NOCOPY NUMBER
, x_Msg_Data OUT NOCOPY VARCHAR2
) IS
l_Region_Code AK_REGIONS.REGION_CODE%TYPE;
SELECT A.ANALYSIS_OPTION0 ,
A.DATASET_ID,
D.NAME,
D.HELP,
M.SHORT_NAME,
D.FORMAT_ID
FROM BSC_KPI_ANALYSIS_MEASURES_VL A,
BIS_INDICATORS M,
BSC_SYS_DATASETS_VL D
WHERE A.ANALYSIS_OPTION0 >= p_Deleted_AO_Index
AND A.ANALYSIS_OPTION1 = 0
AND A.ANALYSIS_OPTION2 = 0
AND A.SERIES_ID = 0
AND A.INDICATOR = p_Kpi_Id
AND D.DATASET_ID = A.DATASET_ID
AND M.DATASET_ID = D.DATASET_ID;
BSC_BIS_KPI_CRUD_PUB.Delete_AK_Metadata(
p_Commit => p_Commit
, p_Region_Code => l_Region_Code
, p_Region_Code_Application_Id => BSC_BIS_KPI_CRUD_PUB.C_BSC_APPLICATION_ID
, x_Return_Status => x_Return_Status
, x_Msg_Count => x_Msg_Count
, x_Msg_Data => x_Msg_Data
);
BSC_KPI_PVT.Update_Kpi_Calculations(
p_commit => p_commit
,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
);
SELECT DO.SHORT_NAME
FROM BSC_SYS_DIM_GROUPS_VL DG
, BSC_SYS_DIM_LEVELS_BY_GROUP DD
, BSC_SYS_DIM_LEVELS_B DO
WHERE DG.DIM_GROUP_ID = DD.DIM_GROUP_ID
AND DD.DIM_LEVEL_ID = DO.DIM_LEVEL_ID
AND DG.SHORT_NAME = p_Dim_Short_Name;
SELECT COUNT(1) INTO l_Count
FROM BSC_SYS_DIM_GROUPS_VL
WHERE SHORT_NAME = p_Dim_Short_Name;
SELECT DECODE(COUNT(1), 0, FND_API.G_FALSE, FND_API.G_TRUE)
INTO l_Is_Time_Dim_Obj
FROM BSC_SYS_DIM_GROUPS_VL DG,
BSC_SYS_DIM_LEVELS_BY_GROUP DD,
BSC_SYS_DIM_LEVELS_B DO
WHERE DG.DIM_GROUP_ID = DD.DIM_GROUP_ID
AND DD.DIM_LEVEL_ID = DO.DIM_LEVEL_ID
AND ((DG.SHORT_NAME = BSC_BIS_KPI_CRUD_PUB.C_OLTP_TIME) OR (DG.SHORT_NAME = BSC_BIS_KPI_CRUD_PUB.C_EDW_TIME))
AND DO.SHORT_NAME = p_Dim_Obj_Short_Name;
SELECT DECODE(COUNT(1), 0, FND_API.G_FALSE, FND_API.G_TRUE)
INTO x_Is_Time_Dim_Obj
FROM BSC_SYS_DIM_GROUPS_VL DG,
BSC_SYS_DIM_LEVELS_BY_GROUP DD,
BSC_SYS_DIM_LEVELS_B DO
WHERE DG.DIM_GROUP_ID = DD.DIM_GROUP_ID
AND DD.DIM_LEVEL_ID = DO.DIM_LEVEL_ID
AND ((DG.SHORT_NAME = BSC_BIS_KPI_CRUD_PUB.C_OLTP_TIME) OR (DG.SHORT_NAME = BSC_BIS_KPI_CRUD_PUB.C_EDW_TIME))
AND DO.SHORT_NAME = p_Dim_Obj_Short_Name;
SELECT (NVL(MAX(OPTION_ID), -1)+1)
INTO l_Next_Analysis_Option
FROM BSC_KPI_ANALYSIS_OPTIONS_B
WHERE INDICATOR = p_Kpi_Id
AND ANALYSIS_GROUP_ID = NVL(p_Analysis_Group0, 0);
SELECT
COUNT(1)
INTO
l_Count
FROM
bsc_sys_measures m,
bsc_sys_datasets_vl d,
bis_indicators i
WHERE
i.dataset_id = d.dataset_id AND
d.measure_id1 = m.measure_id AND
i.short_name = p_Short_Name AND
m.source = BSC_BIS_MEASURE_PUB.c_BSC;
SELECT DISTINCT INDICATOR
INTO l_Kpi_Id
FROM BSC_KPI_ANALYSIS_OPTIONS_B
WHERE SHORT_NAME = (SELECT SUBSTR(ACTUAL_DATA_SOURCE, 1, INSTR(ACTUAL_DATA_SOURCE, '.') - 1) REGION_CODE
FROM BIS_INDICATORS
WHERE SHORT_NAME = p_Short_Name);
SELECT ATTRIBUTE_CODE
FROM AK_REGION_ITEMS
WHERE REGION_CODE = p_Region_Code
AND ITEM_STYLE = BIS_AK_REGION_PUB.c_NESTED_REGION_STYLE
AND NESTED_REGION_CODE IS NOT NULL;
SELECT COUNT(1)
INTO l_Count
FROM AK_REGION_ITEMS
WHERE REGION_CODE = p_Region_Code
AND ATTRIBUTE_CODE = BSC_BIS_KPI_CRUD_PUB.C_AS_OF_DATE;
SELECT (KV.INDICATOR || ' - ' || KV.NAME) XTDOBJECTIVE
FROM BSC_KPIS_VL KV
,BSC_KPI_CALCULATIONS KC
WHERE KC.INDICATOR = KV.INDICATOR
AND KC.CALCULATION_ID = BSC_BIS_KPI_CRUD_PUB.C_CALC_XTD
AND KC.USER_LEVEL0 = BSC_BIS_KPI_CRUD_PUB.C_ENABLE_CALC_U0
AND KC.USER_LEVEL1 = BSC_BIS_KPI_CRUD_PUB.C_ENABLE_CALC_U1
AND KV.SHORT_NAME IS NOT NULL
ORDER BY KV.INDICATOR;
PROCEDURE Validate_Kpi_Delete
(
p_Measure_Short_Name IN VARCHAR2
, x_Return_Status OUT NOCOPY VARCHAR2
, x_Msg_Count OUT NOCOPY NUMBER
, x_Msg_Data OUT NOCOPY VARCHAR2
) IS
l_Return_Status VARCHAR2(3);
SELECT T.NAME
FROM BSC_TABS_VL T
, BSC_TAB_INDICATORS TI
, BSC_KPI_ANALYSIS_MEASURES_B AM
, BIS_INDICATORS BI
WHERE BI.SHORT_NAME = l_Measure_Short_Name
AND AM.DATASET_ID = BI.DATASET_ID
AND TI.INDICATOR = AM.INDICATOR
AND T.TAB_ID = TI.TAB_ID;
SELECT B.FUNCTION_NAME
FROM BIS_INDICATORS B
WHERE B.SHORT_NAME = l_Measure_Short_Name;
SELECT K.NAME || ' [' || K.INDICATOR || ']' NAME
FROM BSC_KPIS_VL K
, BSC_KPI_ANALYSIS_MEASURES_B A
, BIS_INDICATORS B
WHERE K.INDICATOR = A.INDICATOR
AND A.DATASET_ID = B.DATASET_ID
AND B.SHORT_NAME = l_Measure_Short_Name;
SELECT DISTINCT R.NAME NAME
FROM AK_REGION_ITEMS RI,
AK_REGIONS_VL R
WHERE RI.ATTRIBUTE1 IN ('MEASURE', 'MEASURE_NOTARGET')
AND RI.ATTRIBUTE2 = l_Measure_Short_Name
AND RI.REGION_CODE = R.REGION_CODE;
SELECT NAME
INTO l_Kpi_Name
FROM BIS_INDICATORS_VL
WHERE SHORT_NAME = l_Measure_Short_Name;
FND_MESSAGE.SET_NAME('BSC','BSC_KPI_DELETE_WARNING');
x_Msg_Data := x_Msg_Data; --||' -> BSC_BIS_KPI_CRUD_PUB.Validate_Kpi_Delete ';
x_Msg_Data := SQLERRM||' at BSC_BIS_KPI_CRUD_PUB.Validate_Kpi_Delete ';
END Validate_Kpi_Delete;
SELECT SHORT_NAME
FROM BSC_KPI_ANALYSIS_OPTIONS_B
WHERE INDICATOR = l_Kpi_Id;
SELECT NAME
INTO x_Kpi_Name
FROM BSC_SYS_DATASETS_VL
WHERE DATASET_ID = Get_Dataset_Id(p_Measure_Short_Name);
SELECT SUBSTR(ACTUAL_DATA_SOURCE, 1, INSTR(ACTUAL_DATA_SOURCE, '.') - 1)
INTO x_Report_Code
FROM BIS_INDICATORS
WHERE SHORT_NAME = p_Measure_Short_Name;
API to delete S2E Objective and all the analysis options under the Objective
deleting each analysis option will delete the assosiated base Measure,
Fnd Form function and Report.
*/
PROCEDURE Delete_S2E_Objective(
p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_indicator IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_measure_shortname BIS_INDICATORS.SHORT_NAME%TYPE;
SELECT a.indicator,a.analysis_option0,a.analysis_option1,a.analysis_option2,a.series_id
FROM bsc_oaf_analysys_opt_comb_v a
WHERE a.indicator = p_indicator;
SELECT a.short_name FROM bis_indicators a,bsc_kpi_analysis_measures_b b
WHERE a.dataset_id = b.dataset_id
AND B.INDICATOR = p_indicator
AND B.ANALYSIS_OPTION0 = p_ana_option0
AND B.ANALYSIS_OPTION1 = p_ana_option1
AND B.ANALYSIS_OPTION2 = p_ana_option2
AND B.SERIES_ID = p_series_id;
BSC_BIS_KPI_CRUD_PUB.Delete_Kpi_End_To_End(
p_Commit => p_commit,
p_Param_Portlet_Region_Code => 'DUMMY',
p_Measure_Short_Name => l_measure_shortname,
p_Page_Function_Name => NULL,
p_Kpi_Portlet_Function_Name => NULL,
x_Return_Status => x_return_status ,
x_Msg_Count => x_msg_count ,
x_Msg_Data => x_msg_data);
x_msg_data := x_msg_data||' -> Delete_S2E_Objective ';
x_msg_data := SQLERRM||' at Delete_S2E_Objective ';
x_msg_data := x_msg_data||' -> Delete_S2E_Objective ';
x_msg_data := SQLERRM||' at Delete_S2E_Objective ';
END Delete_S2E_Objective;
API to delete S2E Objective Group. This is wrapper API to delete Objective group after
deleting all objectives under this group.
*/
PROCEDURE Delete_S2E_ObjectiveGroup(
p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_tabId IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_kpiGroup_id bsc_tab_ind_groups_vl.IND_GROUP_ID%TYPE;
SELECT ind_group_id FROM bsc_tab_ind_groups_vl
WHERE tab_id = p_tabId;
BSC_PMF_UI_WRAPPER.Delete_Kpi_Group(
p_kpi_group_id => l_kpiGroup_id
, p_tab_id => -1
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
x_msg_data := x_msg_data||' -> Delete_S2E_ObjectiveGroup';
x_msg_data := SQLERRM||' at Delete_S2E_ObjectiveGroup ';
x_msg_data := x_msg_data||' -> Delete_S2E_ObjectiveGroup ';
x_msg_data := SQLERRM||' at Delete_S2E_ObjectiveGroup ';
END Delete_S2E_ObjectiveGroup;
API to delete S2E Scorecard. This is wrapper API to delete scorecard
*/
PROCEDURE Delete_S2E_Scorecard(
p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_tabId IN number
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
) IS
BEGIN
BSC_PMF_UI_WRAPPER.Delete_Tab(
p_tab_id => p_tabId
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
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 ';
END Delete_S2E_Scorecard;
procedure Delete_S2E_Metadata(
p_commit IN VARCHAR2 := FND_API.G_FALSE
,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_ret_status varchar2(10);
SELECT indicator FROM bsc_tab_indicators
WHERE tab_id = p_tab_id;
Delete_S2E_Objective(
p_commit => FND_API.G_FALSE
,p_indicator => l_indicator
,x_return_status => l_ret_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data );
Delete_S2E_ObjectiveGroup(
p_commit => FND_API.G_FALSE
,p_tabId => p_tab_id
,x_return_status => l_ret_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data );
Delete_S2E_Scorecard(
p_commit => FND_API.G_FALSE
,p_tabId => p_tab_id
,x_return_status => l_ret_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data );
x_msg_data := x_msg_data||' -> Delete_S2E_Metadata ';
x_msg_data := SQLERRM||' at Delete_S2E_Metadata ';
x_msg_data := x_msg_data||' -> Delete_S2E_Metadata ';
x_msg_data := SQLERRM||' at Delete_S2E_Metadata ';
END Delete_S2E_Metadata;
PROCEDURE Get_S2ESCR_DeleteMessage(
p_tabId IN number
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
) IS
l_objective_name BSC_KPIS_VL.NAME%TYPE;
SELECT a.name,a.indicator FROM BSC_KPIS_VL a,BSC_TAB_INDICATORS b
WHERE a.indicator = b.indicator
AND b.tab_id = p_tabId;
SELECT a.full_name FROM bsc_oaf_analysys_opt_comb_v a
WHERE a.indicator = p_indicator;
FND_MESSAGE.SET_NAME('BSC','BSC_SCR_DELETE_WARN');
x_msg_data := x_msg_data||' -> Get_S2E_Scorecard_DeleteMessage ';
x_msg_data := x_msg_data||' -> Get_S2E_Scorecard_DeleteMessage ';
x_msg_data := SQLERRM||' at Get_S2E_Scorecard_DeleteMessage ';
END Get_S2ESCR_DeleteMessage;
SELECT COUNT(1)
INTO l_count
FROM ak_region_items AK
WHERE AK.region_code = p_param_portlet_region_code
AND ATTRIBUTE1 IN ('DIMENSION LEVEL',
'DIM LEVEL SINGLE VALUE',
'DIMENSION VALUE',
'HIDE_VIEW_BY',
'HIDE_VIEW_BY_SINGLE',
'HIDE PARAMETER',
'VIEWBY PARAMETER',
'HIDE_DIM_LVL',
'HIDE DIMENSION LEVEL',
'HIDE VIEW BY DIMENSION',
'HIDE_VIEW_BY_DIM_SINGLE')
AND AK.attribute2 LIKE '%+%'
AND (AK.attribute2 LIKE '%TIME_COMPARISON_TYPE%' OR AK.attribute2 LIKE '%PLAN_SNAPSHOT%');
SELECT a.DIMENSION_ID
FROM BIS_DIMENSIONS A
WHERE UPPER(a.SHORT_NAME) = UPPER(l_Dimension_Grp(l_Counter));
l_Region_Last_Updated_By Ak_Regions_Tl.LAST_UPDATED_BY%TYPE;
l_Region_Last_Update_Date Ak_Regions_Tl.LAST_UPDATE_DATE%TYPE;
l_Region_Last_Update_Login Ak_Regions_Tl.LAST_UPDATE_LOGIN%TYPE;
BIS_PMV_REGION_PVT.UPDATE_REGION
(
p_commit => p_Commit
,p_Report_Region_Rec => l_report_region_rec
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
BSC_BIS_DIMENSION_PUB.Update_Dimension
( p_commit => p_Commit
, p_dim_short_name => p_Region_Code
, p_display_name => p_Region_Code
, p_description => p_Region_Code
, p_application_id => p_Region_Application_Id
, p_dim_obj_short_names => x_Non_Time_Dim_Obj_Short_Names
, p_time_stamp => NULL
, p_hide => FND_API.G_TRUE
, x_return_status => x_Return_Status
, x_msg_count => x_Msg_Count
, x_msg_data => x_Msg_Data
);
, x_Region_Last_Updated_By => l_Region_Last_Updated_By
, x_Region_Last_Update_Date => l_Region_Last_Update_Date
, x_Region_Last_Update_Login => l_Region_Last_Update_Login
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
BSC_PMF_UI_WRAPPER.Update_Kpi(
p_Commit => p_Commit
,p_Kpi_Id => p_Kpi_Id
,x_Return_Status => x_return_status
,x_Msg_Count => x_msg_count
,x_Msg_Data => x_msg_data
,p_Kpi_Name => l_Region_Name
,p_Kpi_Help => l_Region_Description
);
SELECT ATTRIBUTE2
FROM AK_REGION_ITEMS
WHERE REGION_CODE = p_Region_Code
AND ATTRIBUTE1 IN ('BUCKET_MEASURE', 'MEASURE', 'MEASURE_NOTARGET', 'SUB MEASURE')
ORDER BY DISPLAY_SEQUENCE;
SELECT COUNT(1) INTO l_Count
FROM BSC_KPI_ANALYSIS_MEASURES_B B
WHERE B.DATASET_ID = l_Dataset_Id
AND B.INDICATOR = p_Kpi_Id;
SELECT A.SERIES_ID
INTO l_Data_Series_Id
FROM BSC_KPI_ANALYSIS_MEASURES_B A
WHERE A.INDICATOR = p_Kpi_Id
AND A.ANALYSIS_OPTION0 = 0
AND A.ANALYSIS_OPTION1 = 0
AND A.ANALYSIS_OPTION2 = 0
AND A.DATASET_ID = p_Dataset_Id;
SELECT
COUNT(1)
INTO
l_Count
FROM
bsc_sys_measures m,
bsc_sys_datasets_vl d,
bis_indicators i
WHERE
i.dataset_id = d.dataset_id AND
d.measure_id1 = m.measure_id AND
i.short_name = p_Short_Name AND
m.source = BSC_BIS_MEASURE_PUB.c_BSC;
SELECT DISTINCT INDICATOR
INTO l_Kpi_Id
FROM BSC_KPIS_B
WHERE SHORT_NAME = (SELECT SUBSTR(ACTUAL_DATA_SOURCE, 1, INSTR(ACTUAL_DATA_SOURCE, '.') - 1) REGION_CODE
FROM BIS_INDICATORS
WHERE SHORT_NAME = p_Short_Name);
l_Delete_Measure VARCHAR2(1);
l_Delete_Dimensions VARCHAR2(1);
SELECT I.SHORT_NAME
FROM BIS_INDICATORS I,
BSC_KPI_ANALYSIS_MEASURES_VL A
WHERE A.INDICATOR = l_Kpi_Id
AND I.DATASET_ID = A.DATASET_ID;
l_Delete_Measure := FND_API.G_FALSE;
l_Delete_Dimensions := FND_API.G_FALSE;
BSC_BIS_KPI_CRUD_PUB.Delete_AG_Bsc_Metadata (
p_Commit => p_Commit
, p_Region_Code => p_Region_Code
, p_Delete_Measures => l_Delete_Measure
, p_Delete_Dimensions => l_Delete_Dimensions
, x_Return_Status => x_Return_Status
, x_Msg_Count => x_Msg_Count
, x_Msg_Data => x_Msg_Data
);
o PHASE 2 - Delete/Convert Measure to PMF type
+ Update BSC_SYS_DATASETS_B.SOURCE and BSC_SYS_MEASURES.SOURCE to "PMF"
For each l_Measure_List, update BSC_SYS_MEASURES.SOURCE and BSC_SYS_DATASETS_B.SOURCE
to PMF.
+ Delete entries in BSC_DB_MEASURE_COLS_TL and BSC_DB_MEASURE_GROUPS_TL
for the Measure Column (Identified by BSC_SYS_MEASURES.MEASURE_COL)
For each l_Measure_List, with its BSC_SYS_DATASETS_B.MEASURE_ID1 maps to BSC_SYS_MEASURES.MEASURE_ID.
We need to call the following APIs ..
Measure_Group_Id can be found out by querying BSC_DB_MEASURE_GROUPS_TL.SHORT_NAME
with the passed REGION_CODE
BSC_DB_MEASURE_GROUPS_PKG.DELETE_ROW(L_Measure_Group_Id)
+ Retain BIS_INDICATORS.ACTUAL_DATA_SOURCE_TYPE to "AK"
+ Retain BIS_INDICATORS.FUNCTION_NAME to the current function - no changes
+ Manipulate BIS_INDICATORS.ACTUAL_DATA_SOURCE - Open Issue
*/
--DBMS_OUTPUT.PUT_LINE('Coming to ... BSC_BIS_KPI_CRUD_PUB.Switch_Measure_Type');
IF (l_Delete_Measure = FND_API.G_FALSE) THEN
FOR iCount IN 0..l_Measure_List.LAST LOOP
--DBMS_OUTPUT.PUT_LINE('loop ... BSC_BIS_KPI_CRUD_PUB.Switch_Measure_Type ... ' || l_Measure_List(iCount));
, p_Delete_Columns => FND_API.G_FALSE
, p_Clean_Measure_Date_Source => FND_API.G_TRUE
, x_Return_Status => x_Return_Status
, x_Msg_Count => x_Msg_Count
, x_Msg_Data => x_Msg_Data
);
BIS_PMV_REGION_PVT.UPDATE_REGION
(
p_commit => p_Commit
,p_Report_Region_Rec => l_report_region_rec
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
PROCEDURE Delete_AG_Bsc_Metadata (
p_Commit IN VARCHAR2 := FND_API.G_FALSE
, p_Region_Code IN VARCHAR2
, p_Delete_Measures IN VARCHAR2
, p_Delete_Dimensions IN VARCHAR2
, x_Return_Status OUT NOCOPY VARCHAR
, x_Msg_Count OUT NOCOPY NUMBER
, x_Msg_Data OUT NOCOPY VARCHAR
) IS
l_Kpi_Id NUMBER;
l_Delete_Dimension VARCHAR2(1);
SELECT I.SHORT_NAME
FROM BIS_INDICATORS I,
BSC_KPI_ANALYSIS_MEASURES_VL A
WHERE A.INDICATOR = l_Kpi_Id
AND I.DATASET_ID = A.DATASET_ID;
l_Delete_Dimension := FND_API.G_FALSE;
o PHASE 1 - Delete Base BSC Metadata
1) The Kpi_Id, Kpi_Group_Id and Tab_Id should be obtained from the Region_Code level
2) The list of Measures associated to the Objective must be noted down (Comma separated SHORT_NAME)
and store it in l_Measure_List
3) The Dimension Short_Name is the REGION_CODE.
+ Disassociate the 2 AG Measures associated to the Objective -
from Data Series (BSC_KPI_ANALSYSIS_MEASURES_B) (we dont want
to delete the measure, but convert it rather)
+ Delete the Objective created for the Report (Deletes all
Objecitve Metadata including Dataseries, Analysis Options,
Objective-Dimension assiciation, Periodicities at the Objective
Level). All BSC_KPI% tables are cleaned up.
*/
BSC_PMF_UI_WRAPPER.Delete_Kpi(
p_commit => p_commit
,p_Kpi_Id => l_Kpi_Id
,x_Return_Status => x_Return_Status
,x_Msg_Count => x_Msg_Count
,x_Msg_Data => x_Msg_Data
);
+ Delete the Objective Group created for the AG Report
*/
IF(l_Tab_Id <> BSC_BIS_KPI_CRUD_PUB.C_INVALID_ENTITY AND l_kpi_Group_Id <> BSC_BIS_KPI_CRUD_PUB.C_INVALID_ENTITY) THEN
BSC_PMF_UI_WRAPPER.Delete_Kpi_Group(
p_commit => p_Commit
,p_kpi_group_id => l_kpi_Group_Id
,p_tab_id => l_Tab_Id
,x_return_status => x_Return_Status
,x_msg_count => x_Msg_Count
,x_msg_data => x_Msg_Data
);
BSC_PMF_UI_WRAPPER.Delete_Kpi_Group(
p_commit => p_Commit
,p_kpi_group_id => l_kpi_Group_Id
,p_tab_id => -1
,x_return_status => x_Return_Status
,x_msg_count => x_Msg_Count
,x_msg_data => x_Msg_Data
);
+ Delete the Scorecard Created for the AG Report
*/
BSC_PMF_UI_WRAPPER.Delete_Tab(
p_commit => p_Commit
, p_tab_id => l_Tab_Id
, x_Return_Status => x_Return_Status
, x_Msg_Count => x_Msg_Count
, x_Msg_Data => x_Msg_Data
);
+ Delete the Dimension created for the AG Report.
*/
-- This Dimension should not be deleted since it is currently being used
-- for the 'where clauses'
-- Added for Bug#5081180 to ensure Delete_Dimension API is not called when
-- the Dimension does not exist.
SELECT COUNT(1) INTO l_Count
FROM BSC_SYS_DIM_GROUPS_VL G
WHERE G.SHORT_NAME = p_Region_Code;
IF ((p_Delete_Dimensions = FND_API.G_TRUE) AND (l_Count <> 0)) THEN
BSC_BIS_DIMENSION_PUB.Delete_Dimension
( p_commit => p_Commit
, p_dim_short_name => p_Region_Code
, x_return_status => x_Return_Status
, x_msg_count => x_Msg_Count
, x_msg_data => x_Msg_Data
);
IF (p_Delete_Measures = FND_API.G_TRUE) THEN
--DBMS_OUTPUT.PUT_LINE(' Step 1');
BSC_BIS_MEASURE_PUB.Delete_Measure(
p_Commit => p_commit
,p_Dataset_Id => l_Dataset_Id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
x_msg_data := x_msg_data||' -> BSC_BIS_KPI_CRUD_PUB.Delete_AG_Bsc_Metadata ';
x_msg_data := SQLERRM||' at BSC_BIS_KPI_CRUD_PUB.Delete_AG_Bsc_Metadata ';
x_msg_data := x_msg_data||' -> BSC_BIS_KPI_CRUD_PUB.Delete_AG_Bsc_Metadata ';
x_msg_data := SQLERRM||' at BSC_BIS_KPI_CRUD_PUB.Delete_AG_Bsc_Metadata ';
END Delete_AG_Bsc_Metadata;
, p_Delete_Columns IN VARCHAR2
, p_Clean_Measure_Date_Source IN VARCHAR2
, x_Return_Status OUT NOCOPY VARCHAR
, x_Msg_Count OUT NOCOPY NUMBER
, x_Msg_Data OUT NOCOPY VARCHAR
) IS
l_Measure_Source BSC_SYS_MEASURES.SOURCE%TYPE;
UPDATE BSC_SYS_MEASURES M
SET M.SOURCE = BSC_BIS_MEASURE_PUB.c_PMF
WHERE M.SHORT_NAME = p_Measure_Short_Name;
UPDATE BSC_SYS_DATASETS_B D
SET D.SOURCE = BSC_BIS_MEASURE_PUB.c_PMF
WHERE D.DATASET_ID = l_Dataset_Id;
IF(p_Delete_Columns = FND_API.G_TRUE) THEN
-- delete the measure columns
BSC_DB_MEASURE_COLS_PKG.DELETE_ROW(l_Measure_Column);
UPDATE BIS_INDICATORS B
SET B.ACTUAL_DATA_SOURCE = NULL
WHERE B.SHORT_NAME = p_Measure_Short_Name;
SELECT
m.source
INTO
l_Measure_Source
FROM
bsc_sys_measures m,
bsc_sys_datasets_vl d,
bis_indicators i
WHERE
i.dataset_id = d.dataset_id AND
d.measure_id1 = m.measure_id AND
i.short_name = p_Measure_Short_Name;
SELECT COUNT(1)
INTO l_count
FROM ak_regions
WHERE region_code = l_region_code;
SELECT B.ACTUAL_DATA_SOURCE
INTO l_Actual_Data_Source
FROM BIS_INDICATORS B
WHERE B.SHORT_NAME = p_Measures_Short_Name;
SELECT B.FUNCTION_NAME
INTO l_Measure_Function_Name
FROM BIS_INDICATORS B
WHERE B.SHORT_NAME = p_Measures_Short_Name;
SELECT COUNT(1)
INTO l_Count
FROM fnd_form_functions
WHERE function_name = l_Measure_Function_Name;
SELECT B.ACTUAL_DATA_SOURCE_TYPE, B.ACTUAL_DATA_SOURCE
INTO l_Actual_Data_Source_Type, l_Actual_Data_Source
FROM BIS_INDICATORS B
WHERE B.SHORT_NAME = p_Measures_Short_Name;
SELECT COUNT(1) INTO l_Count
FROM BSC_TABS_B T,
BSC_TAB_INDICATORS K,
BSC_KPI_ANALYSIS_OPTIONS_B A
WHERE T.TAB_ID = p_Tab_id
AND K.TAB_ID = T.TAB_ID
AND A.INDICATOR = K.INDICATOR
AND A.SHORT_NAME = T.SHORT_NAME;
SELECT COUNT(1) INTO l_Count
FROM BSC_KPI_PERIODICITIES K
WHERE K.INDICATOR = p_Kpi_Id
AND K.PERIODICITY_ID = l_Periodicity_Id;
SELECT COUNT(1) INTO l_Count
FROM BSC_KPI_PERIODICITIES K
WHERE K.INDICATOR = p_Kpi_Id;
SELECT B.LEVEL_SHORTNAME
FROM BSC_KPI_DIM_LEVELS_VL B
WHERE B.INDICATOR = p_Kpi_Id
ORDER BY B.DIM_LEVEL_INDEX;
SELECT I.SHORT_NAME
FROM BSC_KPI_ANALYSIS_MEASURES_B K
, BIS_INDICATORS I
WHERE K.INDICATOR = p_Kpi_Id
AND K.ANALYSIS_OPTION0 = 0
AND K.ANALYSIS_OPTION1 = 0
AND K.ANALYSIS_OPTION2 = 0
AND I.DATASET_ID = K.DATASET_ID
AND K.DATASET_ID <>-1
ORDER BY K.SERIES_ID;
SELECT I.SHORT_NAME
FROM BSC_KPI_ANALYSIS_MEASURES_B K
, BIS_INDICATORS I
WHERE K.INDICATOR = p_Kpi_Id
AND K.ANALYSIS_OPTION0 = 0
AND K.ANALYSIS_OPTION1 = 0
AND K.ANALYSIS_OPTION2 = 0
AND I.DATASET_ID = K.DATASET_ID
AND K.DATASET_ID <>-1
ORDER BY K.SERIES_ID;
SELECT COUNT(1) INTO l_PreviousMeasCnt
FROM BSC_KPI_ANALYSIS_MEASURES_B K
, BIS_INDICATORS I
WHERE K.INDICATOR = p_Kpi_Id
AND K.ANALYSIS_OPTION0 = 0
AND K.ANALYSIS_OPTION1 = 0
AND K.ANALYSIS_OPTION2 = 0
AND I.DATASET_ID = K.DATASET_ID
AND K.DATASET_ID <>-1
ORDER BY K.SERIES_ID;
Name : Delete_Tab_And_TabViews
Description : This API is used to delete the scorecard and the associated
tab_views which are attached to it.
Creator : ashankar 25-May-2005
Note : Before using this API from Report designer we need to check if BSC
is installed or not.If not then we will not call this API.
/************************************************************/
PROCEDURE Delete_Tab_And_TabViews
(
p_commit IN VARCHAR2 := FND_API.G_FALSE
, p_region_code IN AK_REGION_ITEMS.region_code%TYPE
, x_Return_Status OUT NOCOPY VARCHAR2
, x_Msg_Count OUT NOCOPY NUMBER
, x_Msg_Data OUT NOCOPY VARCHAR2
)IS
l_tab_id BSC_TABS_B.tab_id%TYPE;
SELECT tab_id
FROM BSC_TABS_B
WHERE SHORT_NAME = p_region_code;
BSC_PMF_UI_WRAPPER.Delete_Tab
(
p_commit => p_commit
, p_tab_id => l_tab_id
, x_Return_Status => x_Return_Status
, x_Msg_Count => x_Msg_Count
, x_Msg_Data => x_Msg_Data
);
x_msg_data := x_Msg_Data||' -> BSC_BIS_KPI_CRUD_PUB.Delete_Tab_And_TabViews ';
x_msg_data := SQLERRM||' at BSC_BIS_KPI_CRUD_PUB.Delete_Tab_And_TabViews ';
x_Msg_Data := x_Msg_Data||' -> BSC_BIS_KPI_CRUD_PUB.Delete_Tab_And_TabViews ';
x_Msg_Data := SQLERRM||' at BSC_BIS_KPI_CRUD_PUB.Delete_Tab_And_TabViews ';
END Delete_Tab_And_TabViews;
SELECT B.short_name
FROM BIS_INDICATORS B
,BSC_TAB_VIEW_LABELS_B C
,BSC_TABS_B A
WHERE A.tab_id = C.tab_id
AND C.link_id = B.dataset_id
AND A.short_name = p_region_code;
SELECT A.tab_id
,B.tab_view_id
,B.last_update_date
FROM BSC_TABS_B A
,BSC_TAB_VIEWS_B B
WHERE A.tab_id =B.tab_id
AND A.short_name = p_region_code;
SELECT COUNT(0)
INTO l_count
FROM AK_REGION_ITEMS
WHERE region_code = p_region_code
AND region_application_id = p_region_app_id;
Scorecard created from page designer can be deleted from Scorecard designers but not the scorecard created from
report designer.
INPUT : p_tab_sht_name
OUPTUT : 'F' --> Not from report designer
'T' --> From report designer
Created by : ashankar 08-JUL-2005
/****************************************************/
FUNCTION is_Scorecard_From_Reports (
p_tab_sht_name IN BSC_TABS_B.short_name%TYPE
) RETURN VARCHAR2 IS
CURSOR c_form_function IS
SELECT Web_html_call
FROM FND_FORM_FUNCTIONS
WHERE FUNCTION_NAME =p_tab_sht_name;
SELECT COUNT(1) INTO l_Count
FROM BSC_SYS_PERIODICITIES P
WHERE P.SHORT_NAME = p_Short_Name;
SELECT COUNT(1) INTO l_Count
FROM BSC_SYS_CALENDARS_B P
WHERE P.SHORT_NAME = p_Short_Name;
SELECT B.PERIODICITY_ID, B.CALENDAR_ID
INTO x_Periodicity_Id, x_Calendar_Id
FROM BSC_SYS_PERIODICITIES B
WHERE B.SHORT_NAME = p_Time_Short_Name;
SELECT attribute_code
INTO l_attribute_code
FROM ak_region_items
WHERE region_code = p_Report_Region_Code
AND attribute2 = p_Measure_Short_Name;
SELECT COUNT(1) INTO l_Count
FROM AK_REGION_ITEMS A
WHERE A.REGION_CODE = p_Region_Code
AND A.REGION_APPLICATION_ID = p_Region_Application_Id
AND A.ATTRIBUTE1 IN
('DIMENSION LEVEL',
'DIM LEVEL SINGLE VALUE',
'DIMENSION VALUE',
'HIDE_VIEW_BY',
'HIDE_VIEW_BY_SINGLE',
'HIDE PARAMETER',
'VIEWBY PARAMETER',
'HIDE_DIM_LVL',
'HIDE DIMENSION LEVEL',
'HIDE VIEW BY DIMENSION',
'HIDE_VIEW_BY_DIM_SINGLE');
SELECT A.NESTED_REGION_CODE
,A.NESTED_REGION_APPLICATION_ID
FROM AK_REGION_ITEMS A
WHERE A.REGION_CODE = p_Region_Code
AND A.REGION_APPLICATION_ID = p_Region_Application_Id
AND A.ITEM_STYLE = BIS_AK_REGION_PUB.c_NESTED_REGION_STYLE;
ELSIF (p_Action_Type = BSC_UTILITY.c_UPDATE) THEN
x_Region_Code := Get_Param_Portlet_By_Dashboard(p_Page_Function_Name);
ELSIF (p_Action_Type = BSC_UTILITY.c_DELETE) THEN
x_Region_Code := Get_Param_Portlet_By_Dashboard(p_Page_Function_Name);
SELECT A.NESTED_REGION_CODE
,A.NESTED_REGION_APPLICATION_ID
FROM AK_REGION_ITEMS A
WHERE A.REGION_CODE = p_Region_Code
AND A.REGION_APPLICATION_ID = p_Region_Application_Id
AND A.ITEM_STYLE = BIS_AK_REGION_PUB.c_NESTED_REGION_STYLE
ORDER BY A.DISPLAY_SEQUENCE;
SELECT
AV.REGION_APPLICATION_ID
, AV.REGION_CODE
, AV.ATTRIBUTE_APPLICATION_ID
, AV.ATTRIBUTE_CODE
, AV.DISPLAY_SEQUENCE
, AV.NODE_DISPLAY_FLAG
, AV.REQUIRED_FLAG
, AV.NODE_QUERY_FLAG
, AV.DISPLAY_VALUE_LENGTH
, AV.ATTRIBUTE_LABEL_LONG
, AV.ORDER_SEQUENCE
, AV.INITIAL_SORT_SEQUENCE
, AV.ORDER_DIRECTION
, AV.URL
, AV.ATTRIBUTE1
, AV.ATTRIBUTE2
, AV.ATTRIBUTE3
, AV.ATTRIBUTE4
, AV.ATTRIBUTE5
, AV.ATTRIBUTE6
, AV.ATTRIBUTE7
, AV.ATTRIBUTE8
, AV.ATTRIBUTE9
, AV.ATTRIBUTE10
, AV.ATTRIBUTE11
, AV.ATTRIBUTE12
, AV.ATTRIBUTE13
, AV.ATTRIBUTE14
, AV.ATTRIBUTE15
FROM AK_REGION_ITEMS_VL AV
WHERE AV.ITEM_STYLE <> BIS_AK_REGION_PUB.c_NESTED_REGION_STYLE
AND AV.REGION_CODE = p_Region_Code
AND AV.REGION_APPLICATION_ID = p_Region_Application_Id
AND AV.ATTRIBUTE1 IN ('DIMENSION LEVEL',
'DIM LEVEL SINGLE VALUE',
'DIMENSION VALUE',
'HIDE_VIEW_BY',
'HIDE_VIEW_BY_SINGLE',
'HIDE PARAMETER',
'VIEWBY PARAMETER',
'HIDE_DIM_LVL',
'HIDE DIMENSION LEVEL',
'HIDE VIEW BY DIMENSION',
'HIDE_VIEW_BY_DIM_SINGLE')
ORDER BY AV.DISPLAY_SEQUENCE;
SELECT G.SHORT_NAME INTO l_Region_Code
FROM BSC_KPIS_B K,
BSC_KPI_DIM_GROUPS KG,
BSC_SYS_DIM_GROUPS_VL G
WHERE K.SHORT_NAME = p_Page_Function_Name
AND KG.INDICATOR = K.INDICATOR
AND G.DIM_GROUP_ID = KG.DIM_GROUP_ID;
SELECT R.ATTRIBUTE_CODE INTO l_Attribute_Code
FROM AK_REGION_ITEMS R
WHERE R.ATTRIBUTE1 = C_COMPARE_ATTRIBURE_TYPE
AND R.ATTRIBUTE2 = Get_Attribute_Code_For_Measure(p_Region_Code, p_Measure_Short_Name)
AND R.REGION_CODE = p_Region_Code;
SELECT B.COMPARISON_SOURCE
INTO l_Comparison_Source
FROM BIS_INDICATORS B
WHERE B.SHORT_NAME = p_Measures_Short_Name;
, p_Update_AK_Metadata IN VARCHAR2
, p_Plsql_For_Report_Query IN VARCHAR2
, p_Old_Attribute_Code_App_Ids IN VARCHAR2
, p_Old_Attribute_Codes IN VARCHAR2
, p_New_Attribute_Code_App_Ids IN VARCHAR2
, p_New_Attribute_Codes IN VARCHAR2
, x_Return_Status OUT NOCOPY VARCHAR
, x_Msg_Count OUT NOCOPY NUMBER
, x_Msg_Data OUT NOCOPY VARCHAR
) IS
l_Old_Attribute_Code_App_Ids BSC_UTILITY.Varchar_Tabletype;
l_Delete_Measure VARCHAR2(1);
l_Delete_Dimensions VARCHAR2(1);
l_Delete_Measure := FND_API.G_FALSE;
l_Delete_Dimensions := FND_API.G_FALSE;
BSC_BIS_KPI_CRUD_PUB.Delete_AG_Bsc_Metadata (
p_Commit => p_Commit
, p_Region_Code => p_Region_Code
, p_Delete_Measures => l_Delete_Measure
, p_Delete_Dimensions => l_Delete_Dimensions
, x_Return_Status => x_Return_Status
, x_Msg_Count => x_Msg_Count
, x_Msg_Data => x_Msg_Data
);
IF (p_Update_AK_Metadata = FND_API.G_TRUE) THEN
l_report_region_rec.Region_Code := p_Region_Code;
BIS_PMV_REGION_PVT.UPDATE_REGION
(
p_commit => p_Commit
,p_Report_Region_Rec => l_report_region_rec
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
NOTE: This API has been designed only to delete BSC Metadata and
in no way modified/delete's AK Metadata.
*/
PROCEDURE Migrate_AGR_To_PLSQL (
p_Commit IN VARCHAR := FND_API.G_FALSE
, p_Region_Application_Id IN VARCHAR2
, p_Region_Code IN VARCHAR2
, x_Return_Status OUT NOCOPY VARCHAR
, x_Msg_Count OUT NOCOPY NUMBER
, x_Msg_Data OUT NOCOPY VARCHAR
) IS
BEGIN
SAVEPOINT MigrateAGRTOPLSQL;
, p_Update_AK_Metadata => FND_API.G_FALSE
, p_Plsql_For_Report_Query => NULL
, p_Old_Attribute_Code_App_Ids => NULL
, p_Old_Attribute_Codes => NULL
, p_New_Attribute_Code_App_Ids => NULL
, p_New_Attribute_Codes => NULL
, x_Return_Status => x_Return_Status
, x_Msg_Count => x_Msg_Count
, x_Msg_Data => x_Msg_Data
);
SELECT AK.ATTRIBUTE_CODE
, AK.ATTRIBUTE2
FROM AK_REGION_ITEMS AK
WHERE AK.REGION_CODE = p_Region_Code
AND AK.REGION_APPLICATION_ID = p_Region_Application_Id
AND AK.ATTRIBUTE1 IN ('BUCKET_MEASURE', 'MEASURE', 'MEASURE_NOTARGET', 'SUB MEASURE')
ORDER BY AK.DISPLAY_SEQUENCE;
SELECT BIS.ACTUAL_DATA_SOURCE_TYPE
, BIS.ACTUAL_DATA_SOURCE
, BIS.FUNCTION_NAME
, BIS.COMPARISON_SOURCE
, BIS.ENABLE_LINK
FROM BIS_INDICATORS BIS
WHERE BIS.SHORT_NAME = l_Measure_Short_Name;
SELECT
m.measure_col, m.measure_id
FROM
bsc_sys_measures m,
bsc_sys_datasets_vl d,
bis_indicators i
WHERE
i.dataset_id = d.dataset_id AND
d.measure_id1 = m.measure_id AND
i.short_name = p_Measure_Short_Name;
BSC_DATASETS_PVT.Update_Measures(
p_commit => p_commit
,p_Dataset_Rec => l_Dataset_Rec
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
SELECT COUNT(1) INTO l_Count
FROM BSC_KPIS_B K
, BSC_KPI_DIM_GROUPS KG
, BSC_SYS_DIM_GROUPS_VL G
WHERE
K.INDICATOR = p_Kpi_Id
AND G.SHORT_NAME = p_Dimension_Short_Name
AND KG.INDICATOR = K.INDICATOR
AND KG.DIM_GROUP_ID = G.DIM_GROUP_ID;
PROCEDURE Delete_AG_Bsc_Metadata (
p_Commit IN VARCHAR2 := FND_API.G_FALSE
, p_Region_Code IN VARCHAR2
, p_Delete_Measures IN VARCHAR2
, x_Return_Status OUT NOCOPY VARCHAR
, x_Msg_Count OUT NOCOPY NUMBER
, x_Msg_Data OUT NOCOPY VARCHAR
) IS
BEGIN
BSC_BIS_KPI_CRUD_PUB.Delete_AG_Bsc_Metadata (
p_Commit => p_Commit
, p_Region_Code => p_Region_Code
, p_Delete_Measures => p_Delete_Measures
, p_Delete_Dimensions => FND_API.G_TRUE
, x_Return_Status => x_Return_Status
, x_Msg_Count => x_Msg_Count
, x_Msg_Data => x_Msg_Data
);
x_msg_data := x_msg_data||' -> BSC_BIS_KPI_CRUD_PUB.Delete_AG_Bsc_Metadata ';
x_msg_data := SQLERRM||' at BSC_BIS_KPI_CRUD_PUB.Delete_AG_Bsc_Metadata ';
x_msg_data := x_msg_data||' -> BSC_BIS_KPI_CRUD_PUB.Delete_AG_Bsc_Metadata ';
x_msg_data := SQLERRM||' at BSC_BIS_KPI_CRUD_PUB.Delete_AG_Bsc_Metadata ';
END Delete_AG_Bsc_Metadata;
PROCEDURE Delete_AG_Report (
p_Commit IN VARCHAR2 := FND_API.G_FALSE
, p_Region_Code IN VARCHAR2
, p_Delete_Measures IN VARCHAR2
, x_Return_Status OUT NOCOPY VARCHAR
, x_Msg_Count OUT NOCOPY NUMBER
, x_Msg_Data OUT NOCOPY VARCHAR
)
IS
l_Kpi_Id NUMBER;
BSC_BIS_KPI_CRUD_PUB.Delete_AG_Bsc_Metadata (
p_Commit => FND_API.G_FALSE
,p_Region_Code => p_Region_Code
,p_Delete_Measures => p_Delete_Measures
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
x_msg_data := x_msg_data||' -> BSC_BIS_KPI_CRUD_PUB.Delete_AG_Report ';
x_msg_data := SQLERRM||' at BSC_BIS_KPI_CRUD_PUB.Delete_AG_Report ';
x_msg_data := x_msg_data||' -> BSC_BIS_KPI_CRUD_PUB.Delete_AG_Report ';
x_msg_data := SQLERRM||' at BSC_BIS_KPI_CRUD_PUB.Delete_AG_Report ';
END Delete_AG_Report;
l_Delete_Measure VARCHAR2(1);
l_Delete_Dimensions VARCHAR2(1);
l_Delete_Measure := FND_API.G_FALSE;
l_Delete_Dimensions := FND_API.G_FALSE;
BSC_BIS_KPI_CRUD_PUB.Delete_AG_Bsc_Metadata (
p_Commit => p_Commit
, p_Region_Code => p_Region_Code
, p_Delete_Measures => l_Delete_Measure
, p_Delete_Dimensions => l_Delete_Dimensions
, x_Return_Status => x_Return_Status
, x_Msg_Count => x_Msg_Count
, x_Msg_Data => x_Msg_Data
);
SELECT KP.NAME || '[' || KP.INDICATOR || ']' NAME
FROM BSC_SYS_DIM_GROUPS_VL GP,
BSC_KPI_DIM_GROUPS KG,
BSC_KPIS_VL KP
WHERE GP.SHORT_NAME = p_Region_Code
AND KG.DIM_GROUP_ID = GP.DIM_GROUP_ID
AND KP.INDICATOR = KG.INDICATOR
AND KP.PROTOTYPE_FLAG >= 0
AND KP.PROTOTYPE_FLAG <= DECODE(p_Production_Only, 'T', 0, 7);
SELECT KP.INDICATOR
FROM BSC_SYS_DIM_GROUPS_VL GP,
BSC_KPI_DIM_GROUPS KG,
BSC_KPIS_VL KP
WHERE GP.SHORT_NAME = p_Region_Code
AND KG.DIM_GROUP_ID = GP.DIM_GROUP_ID
AND KP.INDICATOR = KG.INDICATOR;
BSC_BIS_DIMENSION_PUB.Update_Dimension
( p_commit => p_Commit
, p_dim_short_name => p_Region_Code
, p_display_name => p_Region_Code
, p_description => p_Region_Code
, p_application_id => p_Region_Application_Id
, p_dim_obj_short_names => x_Non_Time_Dim_Obj_Short_Names
, p_time_stamp => NULL
, x_return_status => x_Return_Status
, x_msg_count => x_Msg_Count
, x_msg_data => x_Msg_Data
);
SELECT attribute19, attribute20
INTO x_Global_Menu, x_Global_Title
FROM bis_ak_region_extension
WHERE region_code = p_Region_Code AND region_application_id = p_Region_Application_Id;
SELECT name
INTO l_tab_name
FROM bsc_tabs_vl
WHERE tab_id = p_tab_id;
SELECT name
INTO l_obj_grp_name
FROM bsc_tab_ind_groups_vl
WHERE ind_group_id = p_obj_grp_id
AND tab_id = -1;
SELECT BSC_BIS_KPI_CRUD_PUB.C_PMD || TO_CHAR(SYSDATE,'ddmmyyhh24miss')
INTO l_region_code
FROM dual;
SELECT DISTINCT a.attribute2, a.display_sequence
FROM ak_region_items a
WHERE a.region_code = p_region_code
AND ATTRIBUTE1 IN ('DIMENSION LEVEL',
'DIM LEVEL SINGLE VALUE',
'DIMENSION VALUE',
'HIDE_VIEW_BY',
'HIDE_VIEW_BY_SINGLE',
'HIDE PARAMETER',
'VIEWBY PARAMETER',
'HIDE_DIM_LVL',
'HIDE DIMENSION LEVEL',
'HIDE VIEW BY DIMENSION',
'HIDE_VIEW_BY_DIM_SINGLE')
AND a.attribute2 LIKE '%+%'
AND a.attribute2 NOT LIKE 'TIME_COMPARISON_TYPE%'
ORDER BY a.display_sequence;