[Home] [Help]
62: p_Short_Name IN BIS_INDICATORS.short_name%TYPE
63: ) RETURN VARCHAR2;
64: /*******************************************************************************
65: Refresh_Measure_Col_Names API ensures that all the PMF measures that were
66: generated using SHORT_NAME for BSC_SYS_MEASURES.MEASURE_COL is modified to
67: more intelligible names which is ideally derived from the name of the measure.
68:
69: These columns that will be generated will ensure that the MEASURE_COL is derived
70: from NAME of the measure uniquely. This API as standalone does not have any
85: directly run on exisitng type of measures.
86:
87: This API should not be called without calling Refresh_Measure_Col_Names,
88: Though this API can run independently, it will generate column that are
89: available directly in BSC_SYS_MEASURES.MEASURE_COL (which ideally may not
90: be intelligible as a TABLE COLUMN). Hence it is *recommened* that
91: the API Refresh_Measure_Col_Names is run before this API is run.
92:
93: Added as part of Enhancement Bug#4239216
145:
146: PROCEDURE delete_bsc_measures_from_pmf
147: IS
148: -- part of bug#3436393: the previous query was not getting all
149: -- the bsc datasets from pmf repository. It was joining with bsc_sys_measures
150: -- but in case of datasets that are formulas between measures
151: -- they were not fetched. We need to join is with bsc_sys_datasets_b
152: CURSOR c_bsc_measures_in_pmf IS
153: SELECT indicator_id, measure_id1, i.short_name
158: CURSOR c_s2e_kpis(p_indicator_id NUMBER) IS
159: SELECT count(1)
160: FROM bis_indicators BIS_IND
161: ,bsc_sys_datasets_b BSC_DTS
162: ,bsc_sys_measures BSC_MEAS
163: WHERE BIS_IND.dataset_id = BSC_DTS.dataset_id
164: AND BSC_DTS.measure_id1 = BSC_MEAS.measure_id
165: AND BIS_IND.indicator_id = p_indicator_id;
166: -- AND BIS_IND.short_name = BSC_MEAS.short_name --kyadamak commented out as this resulted in bad datacorruption 'we should never join by meausre short name'
274: l_num NUMBER;
275: i NUMBER;
276: l_err VARCHAR2(2000);
277:
278: l_Measure_Col BSC_SYS_MEASURES.MEASURE_COL%TYPE;
279: l_Measure_Group_Id BSC_DB_MEASURE_COLS_TL.MEASURE_GROUP_ID%TYPE;
280: l_Projection_Id BSC_DB_MEASURE_COLS_TL.PROJECTION_ID%TYPE;
281: l_Measure_Type BSC_DB_MEASURE_COLS_TL.MEASURE_TYPE%TYPE;
282: CURSOR bsc_indicators_cursor IS
280: l_Projection_Id BSC_DB_MEASURE_COLS_TL.PROJECTION_ID%TYPE;
281: l_Measure_Type BSC_DB_MEASURE_COLS_TL.MEASURE_TYPE%TYPE;
282: CURSOR bsc_indicators_cursor IS
283: SELECT d.dataset_id, i.name as measure_name, i.description, i.short_name as measure_short_name, i.indicator_id measure_id, i.created_by created_by, i.last_updated_by last_updated_by, i.last_update_login last_update_login
284: FROM bsc_sys_datasets_vl d, bis_indicators_vl i, bsc_sys_measures m
285: WHERE i.short_name = m.short_name (+)
286: and (d.source is null or d.source = 'PMF')
287: and m.measure_id = d.measure_id1 (+)
288: and (i.dataset_id is null or d.dataset_id <> i.dataset_id);
441: l_Short_Name BIS_INDICATORS.short_name%TYPE;
442:
443: CURSOR bsc_datasets_cursor_1 IS
444: SELECT d.dataset_id, name, m.measure_id, m.created_by created_by, m.last_updated_by last_updated_by, m.last_update_login last_update_login
445: FROM bsc_sys_datasets_vl d, bsc_sys_measures m, bisbv_performance_measures pm
446: WHERE d.measure_id1 = m.measure_id
447: AND m.short_name is null
448: AND d.source = 'BSC'
449: AND d.dataset_id = pm.dataset_id (+)
489: IF ((l_return_status IS NOT NULL) AND (l_return_status <> FND_API.G_RET_STS_SUCCESS)) THEN
490: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
491: END IF;
492:
493: UPDATE bsc_sys_measures
494: SET short_name = l_measure_rec.Measure_Short_Name
495: WHERE measure_id = cr.measure_id;
496:
497: BEGIN
554: l_err VARCHAR2(2000);
555:
556: CURSOR cds_datasets_cursor IS
557: SELECT d.dataset_id, m.short_name
558: FROM bsc_sys_datasets_vl d, bsc_sys_measures m, bisbv_performance_measures pm
559: WHERE d.measure_id1 = m.measure_id
560: AND m.short_name = pm.measure_short_name (+)
561: AND m.short_name IS NOT NULL
562: AND d.source = 'CDS'
632: l_err VARCHAR2(2000);
633:
634: CURSOR bsc_datasets_cursor_2 IS
635: SELECT d.dataset_id, name, m.short_name, m.measure_id, pm.measure_short_name, m.created_by created_by, m.last_updated_by last_updated_by, m.last_update_login last_update_login
636: FROM bsc_sys_datasets_vl d, bsc_sys_measures m, bisbv_performance_measures pm
637: WHERE d.measure_id1 = m.measure_id
638: AND m.short_name = pm.measure_short_name (+)
639: AND m.short_name IS NOT NULL
640: AND d.source = 'BSC'
717: IF ((l_return_status IS NOT NULL) AND (l_return_status <> FND_API.G_RET_STS_SUCCESS)) THEN
718: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
719: END IF;
720:
721: UPDATE BSC_SYS_MEASURES
722: SET short_name = l_measure_rec.Measure_Short_Name
723: WHERE measure_id = cr.measure_id;
724: END IF;
725:
766: END sync_measures_bsc_to_pmf;
767: --
768: /***************************************************************************************************
769:
770: if the short name sent already exists in bsc_sys_measures it gets time based unique short_name
771:
772: ***************************************************************************************************/
773: FUNCTION getUniqueShtNameSysMeasure(p_Short_Name IN VARCHAR2)
774: RETURN VARCHAR2
776: l_count NUMBER;
777: BEGIN
778: SELECT COUNT(1)
779: INTO l_count
780: FROM BSC_SYS_MEASURES
781: WHERE SHORT_NAME = p_Short_Name;
782:
783: IF(l_count > 0) THEN
784: RETURN bsc_utility.Get_Unique_Sht_Name_By_Obj_Typ(p_Object_Type => bsc_utility.c_BSC_MEASURE);
792:
793:
794: /***************************************************************************************************
795: Added for the bug#4477575
796: This function populates short_name in bsc_sys_measures if it is null.
797: we found that there may be case where short_name can be null after syncup is run as migration
798: is putting null for all bsc_short_names
799: ***************************************************************************************************/
800: PROCEDURE update_short_name_bsc_sys_mes
803: SELECT M.short_name bscShortName
804: ,B.short_name bisShortName
805: ,B.dataset_id
806: ,M.measure_id
807: FROM bsc_sys_measures M
808: ,bsc_sys_datasets_vl V
809: ,bis_indicators_Vl B
810: WHERE M.measure_id = V.measure_id1
811: AND B.dataset_id = V.dataset_id
812: AND V.SOURCE = 'BSC'
813: AND M.short_name IS NULL;
814:
815: l_Measure_Short_Name BIS_INDICATORS.SHORT_NAME%TYPE;
816: l_Bsc_Measure_Short_Name BSC_SYS_MEASURES.SHORT_NAME%TYPE;
817: BEGIN
818: SAVEPOINT SP_UPDATE_SHORT_NAME;
819: FOR cBSCM IN cBscSysMeasures LOOP
820: l_Bsc_Measure_Short_Name := getUniqueShtNameSysMeasure(p_Short_Name => cBSCM.bisShortName);
818: SAVEPOINT SP_UPDATE_SHORT_NAME;
819: FOR cBSCM IN cBscSysMeasures LOOP
820: l_Bsc_Measure_Short_Name := getUniqueShtNameSysMeasure(p_Short_Name => cBSCM.bisShortName);
821:
822: UPDATE bsc_sys_measures
823: SET short_name = l_Bsc_Measure_Short_Name
824: WHERE measure_id = cBSCM.measure_id;
825:
826: END LOOP;
829: WHEN OTHERS THEN
830: ROLLBACK TO SP_UPDATE_SHORT_NAME;
831: BSC_MESSAGE.Add (
832: x_message => SQLERRM
833: , x_source => 'Update short names in bsc_sys_measures'
834: , x_mode => 'I'
835: );
836: END update_short_name_bsc_sys_mes;
837: /**********************************************************************************************/
855: -- Then synchronization will refresh the dataset_id in BIS_INDICATORS with the correct value
856: -- from BSC_SYS_DATASETS_VL
857: delete_bsc_measures_from_pmf;
858:
859: -- For every PMF record that does not exist in bsc_sys_datasets, create one in bsc_sys_datasets and bsc_sys_measures
860: sync_measures_pmf_to_bsc;
861:
862:
863: -- For every dataset, create a record in bis_indicator table.
2563:
2564:
2565: /*******************************************************************************
2566: Refresh_Measure_Col_Names API ensures that all the PMF measures that were
2567: generated using SHORT_NAME for BSC_SYS_MEASURES.MEASURE_COL is modified to
2568: more intelligible names which is ideally derived from the name of the measure.
2569:
2570: These columns that will be generated will ensure that the MEASURE_COL is derived
2571: from NAME of the measure uniquely. This API as standalone does not have any
2584: I.SHORT_NAME,
2585: I.DATASET_ID,
2586: I.NAME
2587: FROM
2588: BSC_SYS_MEASURES M,
2589: BSC_SYS_DATASETS_VL D,
2590: BIS_INDICATORS_VL I
2591: WHERE
2592: M.MEASURE_ID = D.MEASURE_ID1 AND
2600: I.SHORT_NAME,
2601: I.DATASET_ID,
2602: I.NAME
2603: FROM
2604: BSC_SYS_MEASURES M,
2605: BIS_INDICATORS_VL I
2606: WHERE
2607: M.SHORT_NAME = I.SHORT_NAME AND
2608: M.SOURCE = BSC_BIS_MEASURE_PUB.c_PMF AND
2606: WHERE
2607: M.SHORT_NAME = I.SHORT_NAME AND
2608: M.SOURCE = BSC_BIS_MEASURE_PUB.c_PMF AND
2609: M.MEASURE_COL = I.SHORT_NAME ORDER BY I.DATASET_ID;*/
2610: l_Measure_Col BSC_SYS_MEASURES.MEASURE_COL%TYPE;
2611: l_Dataset_Rec BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type;
2612: l_Return_Status VARCHAR2(1);
2613: l_Msg_Count NUMBER;
2614: l_Msg_Data VARCHAR2(2000);
2655: BSC_DB_MEASURE_COLS_TL table, which will be used by Generate Database to
2656: directly run on exisitng type of measures.
2657: This API should not be called without calling Refresh_Measure_Col_Names,
2658: Though this API can run independently, it will generate column that are
2659: available directly in BSC_SYS_MEASURES.MEASURE_COL (which ideally may not
2660: be intelligible as a TABLE COLUMN). Hence it is *recommened* that
2661: the API Refresh_Measure_Col_Names is run before this API is run.
2662: Added as part of Enhancement Bug#4239216
2663: ********************************************************************************/
2669: M.MEASURE_COL,
2670: D.SOURCE
2671: FROM
2672: BSC_SYS_DATASETS_B D,
2673: BSC_SYS_MEASURES M
2674: WHERE
2675: D.SOURCE = BSC_BIS_MEASURE_PUB.c_PMF AND
2676: M.MEASURE_ID = D.MEASURE_ID1;
2677: l_Measure_Col BSC_SYS_MEASURES.MEASURE_COL%TYPE;
2673: BSC_SYS_MEASURES M
2674: WHERE
2675: D.SOURCE = BSC_BIS_MEASURE_PUB.c_PMF AND
2676: M.MEASURE_ID = D.MEASURE_ID1;
2677: l_Measure_Col BSC_SYS_MEASURES.MEASURE_COL%TYPE;
2678: l_Return_Status VARCHAR2(1);
2679: l_Msg_Count NUMBER;
2680: l_Msg_Data VARCHAR2(2000);
2681: l_Count NUMBER;