[Home] [Help]
3537: l_stack := l_stack ||newline||' fetched';
3538: EXIT WHEN cv%NOTFOUND;
3539: -- PMD does not update the related indicators
3540: l_stack := l_stack ||newline||'going to update...';
3541: UPDATE BSC_KPIS_B
3542: SET PROTOTYPE_FLAG = DECODE(PROTOTYPE_FLAG, 2, 2, 3, 3, 4),
3543: LAST_UPDATED_BY = BSC_METADATA_OPTIMIZER_PKG.gUserId,
3544: LAST_UPDATE_DATE = SYSDATE
3545: WHERE INDICATOR = l_indicator
4269: /****************************************************************************
4270: -- CheckAllIndicsHaveSystem
4271: --
4272: -- DESCRIPTION:
4273: -- Check that all indicators in BSC_KPIS_B have been assigned to some Tab.
4274: -- IF some indicator has not been assigned the GAA cannot continue.
4275: --
4276: -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
4277: -- Arun Santhanam
4284: l_error VARCHAR2(4000);
4285:
4286: CURSOR cIndics IS
4287: SELECT DISTINCT K.INDICATOR
4288: FROM BSC_KPIS_B K, BSC_TAB_INDICATORS T WHERE K.INDICATOR = T.INDICATOR (+)
4289: AND T.INDICATOR IS NULL AND K.PROTOTYPE_FLAG <> 2 ORDER BY K.INDICATOR;
4290:
4291: BEGIN
4292: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
4332: /****************************************************************************
4333: -- CheckAllSharedIndicsSync : CheckAllSharedIndicsSynchronyzed
4334: --
4335: -- DESCRIPTION:
4336: -- Check that all shared indicators in BSC_KPIS_B have been synchronized
4337: -- IF some shared indicator has not been synchronized,GAA cannot continue.
4338: --
4339: -- AUTHOR/DATE - MODIFICATIONS (AUTHOR/DATE/DESCRIPTION):
4340: -- Arun Santhanam
4345: l_indicator number;
4346: l_source_indicator number;
4347:
4348: CURSOR cSharedIndics IS
4349: SELECT DISTINCT INDICATOR, SOURCE_INDICATOR FROM BSC_KPIS_B
4350: WHERE SHARE_FLAG = 3 AND SOURCE_INDICATOR IS NOT NULL AND PROTOTYPE_FLAG <> 2 ;
4351:
4352: BEGIN
4353: IF BSC_METADATA_OPTIMIZER_PKG.g_log THEN
4400: l_error varchar2(1000);
4401:
4402: CURSOR cEDWIndics IS
4403: SELECT DISTINCT K.INDICATOR
4404: FROM BSC_KPIS_B K
4405: WHERE EDW_FLAG = 1
4406: AND EXISTS ( SELECT P.PROPERTY_CODE FROM BSC_KPI_PROPERTIES P
4407: WHERE P.INDICATOR = K.INDICATOR AND P.PROPERTY_VALUE = 0
4408: AND P.PROPERTY_CODE IN ('EDW_DATASET_STATUS', 'EDW_CALENDAR_STATUS', 'EDW_DIMENSION_STATUS'));
4461:
4462: CURSOR cTables IS
4463: SELECT TABLE_NAME FROM BSC_DB_TABLES WHERE TABLE_TYPE <> 2;
4464: CURSOR cIndics4 IS
4465: SELECT INDICATOR FROM BSC_KPIS_B WHERE PROTOTYPE_FLAG = 4 ORDER BY INDICATOR;
4466:
4467: l_original_count number := 0;
4468: l_impl_type NUMBER := 1;
4469:
4488:
4489:
4490: --If we are running optimizer for the first time, the prototype_flag will be 1, so treat it as 3.
4491: IF BSC_METADATA_OPTIMIZER_PKG.gSYSTEM_STAGE = 1 THEN
4492: UPDATE bsc_kpis_b
4493: SET prototype_flag = 3
4494: WHERE prototype_flag=1;
4495: END IF;
4496:
4613: Add_value_bulk(1, BSC_METADATA_OPTIMIZER_PKG.garrIndics);
4614: END IF;
4615: strWhereNotInIndics := 'NOT ('|| strWhereInIndics ||')';
4616:
4617: --We need to update the related indicators' prototype_flag as 4 in bsc_kpis_b .
4618: -- Designer is only flagging the indicators
4619: --that are using the measure direclty. We need to flag other indicators
4620: --using the same measures alone or as part of a formula.
4621: BSC_METADATA_OPTIMIZER_PKG.logProgress('INIT', 'Mark indics for non struc changes');
5603: --re-load all indicator from the database. So, it is necessary to update the
5604: --prototype flag with the correct one (remember that due to the relations between
5605: --kpi, some kpi could be flagged too)
5606: IF BSC_METADATA_OPTIMIZER_PKG.gSYSTEM_STAGE = 2 THEN
5607: l_stmt := ' UPDATE BSC_KPIS_B
5608: SET PROTOTYPE_FLAG = DECODE(PROTOTYPE_FLAG, 2, 2, 3),
5609: LAST_UPDATED_BY = :1,
5610: LAST_UPDATE_DATE = SYSDATE WHERE '|| strWhereInIndics;
5611: execute immediate l_stmt using BSC_METADATA_OPTIMIZER_PKG.gUserId;
5668: --prototype flag with the correct one (remember that due to the relations between
5669: --kpi, some kpi could be flagged too)
5670:
5671: IF BSC_METADATA_OPTIMIZER_PKG.gSYSTEM_STAGE = 2 THEN
5672: l_stmt := 'UPDATE BSC_KPIS_B SET PROTOTYPE_FLAG = DECODE(PROTOTYPE_FLAG, 2, 2, 4), '||
5673: ' LAST_UPDATED_BY = :1 ,'||
5674: ' LAST_UPDATE_DATE = SYSDATE '||
5675: ' WHERE (' || strWhereInIndics4 || ')';
5676: IF BSC_METADATA_OPTIMIZER_PKG.garrIndics.count > 0 THEN
6587: EXIT WHEN BSC_METADATA_OPTIMIZER_PKG.gIndicators.count = 0;
6588:
6589: IF (BSC_METADATA_OPTIMIZER_PKG.gIndicators(i).Action_Flag=2) THEN
6590: --Case 2
6591: DELETE FROM BSC_KPIS_B WHERE INDICATOR = BSC_METADATA_OPTIMIZER_PKG.gIndicators(i).Code;
6592: DELETE FROM BSC_KPIS_TL WHERE INDICATOR = BSC_METADATA_OPTIMIZER_PKG.gIndicators(i).code;
6593: DELETE FROM BSC_KPI_DATA_TABLES WHERE INDICATOR = BSC_METADATA_OPTIMIZER_PKG.gIndicators(i).code;
6594: writeTmp('Deleting entries for indicator='||BSC_METADATA_OPTIMIZER_PKG.gIndicators(i).Code||', old value='||BSC_METADATA_OPTIMIZER_PKG.gIndicators(i).Action_Flag, FND_LOG.LEVEL_STATEMENT, false);
6595: ELSIF (BSC_METADATA_OPTIMIZER_PKG.gIndicators(i).Action_Flag=1 OR BSC_METADATA_OPTIMIZER_PKG.gIndicators(i).Action_Flag=3) THEN
6593: DELETE FROM BSC_KPI_DATA_TABLES WHERE INDICATOR = BSC_METADATA_OPTIMIZER_PKG.gIndicators(i).code;
6594: writeTmp('Deleting entries for indicator='||BSC_METADATA_OPTIMIZER_PKG.gIndicators(i).Code||', old value='||BSC_METADATA_OPTIMIZER_PKG.gIndicators(i).Action_Flag, FND_LOG.LEVEL_STATEMENT, false);
6595: ELSIF (BSC_METADATA_OPTIMIZER_PKG.gIndicators(i).Action_Flag=1 OR BSC_METADATA_OPTIMIZER_PKG.gIndicators(i).Action_Flag=3) THEN
6596: --Case 1, 3
6597: UPDATE BSC_KPIS_B SET PROTOTYPE_FLAG = 0,
6598: LAST_UPDATED_BY = BSC_METADATA_OPTIMIZER_PKG.gUserId,
6599: LAST_UPDATE_DATE = SYSDATE
6600: WHERE INDICATOR = BSC_METADATA_OPTIMIZER_PKG.gIndicators(i).Code;
6601: -- Color By KPI: Mark KPIs to Production mode in tendem with Objective's prototype flag.
6607: --Execute IMMEDIATE l_stmt USING BSC_METADATA_OPTIMIZER_PKG.gUserId, BSC_METADATA_OPTIMIZER_PKG.gIndicators(i).Code;
6608: writeTmp('Updating prototype_flag=0 for indicator='||BSC_METADATA_OPTIMIZER_PKG.gIndicators(i).Code||' from old value='||BSC_METADATA_OPTIMIZER_PKG.gIndicators(i).Action_Flag, FND_LOG.LEVEL_STATEMENT, false);
6609: ELSIF (BSC_METADATA_OPTIMIZER_PKG.gIndicators(i).Action_Flag=4) THEN
6610: --Case 4
6611: UPDATE BSC_KPIS_B SET PROTOTYPE_FLAG = 6,
6612: LAST_UPDATED_BY = BSC_METADATA_OPTIMIZER_PKG.gUserId,
6613: LAST_UPDATE_DATE = SYSDATE
6614: WHERE INDICATOR = BSC_METADATA_OPTIMIZER_PKG.gIndicators(i).Code;
6615: -- Color By KPI: Mark KPIs for color re-calculation
6620: writeTmp('Updating prototype_flag=6 for indicator='||BSC_METADATA_OPTIMIZER_PKG.gIndicators(i).Code||' from old value='||BSC_METADATA_OPTIMIZER_PKG.gIndicators(i).Action_Flag, FND_LOG.LEVEL_STATEMENT, false);
6621: ELSE
6622: --BSC_MV Note: If summarizarion level changed then update prototype flag to 6
6623: If BSC_METADATA_OPTIMIZER_PKG.g_Sum_Level_Change <> 0 Then
6624: UPDATE BSC_KPIS_B SET PROTOTYPE_FLAG = 6,
6625: LAST_UPDATED_BY = BSC_METADATA_OPTIMIZER_PKG.gUserId,
6626: LAST_UPDATE_DATE = SYSDATE
6627: WHERE INDICATOR = BSC_METADATA_OPTIMIZER_PKG.gIndicators(i).Code;
6628: -- Color By KPI: Mark KPIs for color re-calculation