eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

[Home] [Help]

APPS.BSC_ANALYSIS_OPTION_PVT dependencies on BSC_KPI_ANALYSIS_OPTIONS_B

Line 73: FROM BSC_KPI_ANALYSIS_OPTIONS_B

69: ) RETURN BOOLEAN IS
70: l_Count NUMBER;
71: BEGIN
72: SELECT COUNT(*) INTO l_Count
73: FROM BSC_KPI_ANALYSIS_OPTIONS_B
74: WHERE Parent_Option_Id = p_Parent
75: AND Analysis_Group_Id = p_Group
76: AND Indicator = p_kpi_Id;
77: IF (l_Count <> 0) THEN

Line 95: FROM BSC_KPI_ANALYSIS_OPTIONS_B

91: IS
92: l_Count NUMBER;
93: BEGIN
94: SELECT COUNT(*) INTO l_Count
95: FROM BSC_KPI_ANALYSIS_OPTIONS_B
96: WHERE Parent_Option_Id = p_Parent
97: AND OPTION_ID = p_child
98: AND Analysis_Group_Id = p_Group
99: AND Indicator = p_kpi_Id;

Line 115: FROM BSC_KPI_ANALYSIS_OPTIONS_B

111: ) RETURN BOOLEAN IS
112: l_Count NUMBER;
113: BEGIN
114: SELECT COUNT(*) INTO l_Count
115: FROM BSC_KPI_ANALYSIS_OPTIONS_B
116: WHERE GrandParent_Option_Id = p_GrandParent
117: AND Analysis_Group_Id = p_Group
118: AND Indicator =p_kpi_Id;
119: IF (l_Count <> 0) THEN

Line 141: FROM BSC_KPI_ANALYSIS_OPTIONS_B

137: IF (p_Anal_Opt_Tbl(p_group_count + 1).Bsc_dependency_flag = 1) THEN
138: IF((p_group_count = 1)AND(p_Anal_Opt_Tbl(p_group_count).Bsc_dependency_flag = 1))THEN
139: SELECT COUNT(0)
140: INTO l_count
141: FROM BSC_KPI_ANALYSIS_OPTIONS_B
142: WHERE Indicator = p_Kpi_id
143: AND Analysis_Group_Id = p_group_count + 1
144: AND Parent_Option_Id = p_Anal_Opt_Comb_Tbl(p_group_count)
145: AND Grandparent_Option_Id = p_Anal_Opt_Comb_Tbl(p_group_count - 1);

Line 149: FROM BSC_KPI_ANALYSIS_OPTIONS_B

145: AND Grandparent_Option_Id = p_Anal_Opt_Comb_Tbl(p_group_count - 1);
146: ELSIF((p_group_count = 1)AND(p_Anal_Opt_Tbl(p_group_count).Bsc_dependency_flag = 0)) THEN
147: SELECT COUNT(0)
148: INTO l_count
149: FROM BSC_KPI_ANALYSIS_OPTIONS_B
150: WHERE Indicator = p_Kpi_id
151: AND Analysis_Group_Id = p_group_count + 1
152: AND Parent_Option_Id = p_Anal_Opt_Comb_Tbl(p_group_count);
153: ELSE

Line 156: FROM BSC_KPI_ANALYSIS_OPTIONS_B

152: AND Parent_Option_Id = p_Anal_Opt_Comb_Tbl(p_group_count);
153: ELSE
154: SELECT COUNT(0)
155: INTO l_count
156: FROM BSC_KPI_ANALYSIS_OPTIONS_B
157: WHERE Indicator = p_Kpi_id
158: AND Analysis_Group_Id = p_group_count + 1
159: AND Parent_Option_Id = p_Anal_Opt_Comb_Tbl(p_group_count);
160: END IF;

Line 170: , p_Group_id IN BSC_KPI_ANALYSIS_OPTIONS_B.Analysis_Group_Id%TYPE

166:
167: /*******************************************************************************/
168: FUNCTION get_parent_level_id
169: ( p_Kpi_id IN BSC_KPIS_B.indicator%TYPE
170: , p_Group_id IN BSC_KPI_ANALYSIS_OPTIONS_B.Analysis_Group_Id%TYPE
171: , p_Option_id IN BSC_KPI_ANALYSIS_OPTIONS_B.Option_Id%TYPE
172: ) RETURN NUMBER IS
173: l_parent_option BSC_KPI_ANALYSIS_OPTIONS_B.Parent_Option_Id%TYPE;
174: BEGIN

Line 171: , p_Option_id IN BSC_KPI_ANALYSIS_OPTIONS_B.Option_Id%TYPE

167: /*******************************************************************************/
168: FUNCTION get_parent_level_id
169: ( p_Kpi_id IN BSC_KPIS_B.indicator%TYPE
170: , p_Group_id IN BSC_KPI_ANALYSIS_OPTIONS_B.Analysis_Group_Id%TYPE
171: , p_Option_id IN BSC_KPI_ANALYSIS_OPTIONS_B.Option_Id%TYPE
172: ) RETURN NUMBER IS
173: l_parent_option BSC_KPI_ANALYSIS_OPTIONS_B.Parent_Option_Id%TYPE;
174: BEGIN
175: SELECT PARENT_OPTION_ID

Line 173: l_parent_option BSC_KPI_ANALYSIS_OPTIONS_B.Parent_Option_Id%TYPE;

169: ( p_Kpi_id IN BSC_KPIS_B.indicator%TYPE
170: , p_Group_id IN BSC_KPI_ANALYSIS_OPTIONS_B.Analysis_Group_Id%TYPE
171: , p_Option_id IN BSC_KPI_ANALYSIS_OPTIONS_B.Option_Id%TYPE
172: ) RETURN NUMBER IS
173: l_parent_option BSC_KPI_ANALYSIS_OPTIONS_B.Parent_Option_Id%TYPE;
174: BEGIN
175: SELECT PARENT_OPTION_ID
176: INTO l_parent_option
177: FROM BSC_KPI_ANALYSIS_OPTIONS_B

Line 177: FROM BSC_KPI_ANALYSIS_OPTIONS_B

173: l_parent_option BSC_KPI_ANALYSIS_OPTIONS_B.Parent_Option_Id%TYPE;
174: BEGIN
175: SELECT PARENT_OPTION_ID
176: INTO l_parent_option
177: FROM BSC_KPI_ANALYSIS_OPTIONS_B
178: WHERE Indicator = p_Kpi_id
179: AND Analysis_Group_Id = p_Group_id
180: AND Option_Id = p_Option_id;
181:

Line 228: FROM BSC_KPI_ANALYSIS_OPTIONS_B

224:
225: CURSOR c_anal_grp_opt_count IS
226: SELECT analysis_group_id
227: , COUNT(option_id) option_count
228: FROM BSC_KPI_ANALYSIS_OPTIONS_B
229: WHERE indicator = p_Kpi_Id
230: GROUP BY analysis_group_id;
231: BEGIN
232: l_count := 0;

Line 257: FROM BSC_KPI_ANALYSIS_OPTIONS_B

253: l_count NUMBER := 0;
254:
255: CURSOR c_option_id IS
256: SELECT Option_Id
257: FROM BSC_KPI_ANALYSIS_OPTIONS_B
258: WHERE Indicator = p_Kpi_Id
259: AND Analysis_Group_ID = p_group_Id
260: AND ROWNUM < 2;
261: BEGIN

Line 271: FROM BSC_KPI_ANALYSIS_OPTIONS_B

267: AND Analysis_Group_Id = p_group_Id;
268:
269: IF (l_Dependency_Flag = 0) THEN -- for indenpendent
270: SELECT COUNT(*) INTO l_User_Default
271: FROM BSC_KPI_ANALYSIS_OPTIONS_B
272: WHERE Indicator = p_Kpi_Id
273: AND Analysis_Group_Id = p_group_Id
274: AND User_Level0 = 1;
275: IF (l_User_Default = 0) THEN

Line 286: UPDATE BSC_KPI_ANALYSIS_OPTIONS_B

282: l_next_option := 0;
283: END IF;
284: CLOSE c_option_id;
285:
286: UPDATE BSC_KPI_ANALYSIS_OPTIONS_B
287: SET User_Level0 = 1
288: , User_Level1 = 1
289: WHERE Indicator = p_Kpi_Id
290: AND Analysis_Group_Id = p_group_Id

Line 303: FROM BSC_KPI_ANALYSIS_OPTIONS_B

299:
300: ELSE -- for dependent
301: IF (p_group_Id = 0) THEN
302: SELECT COUNT(*) INTO l_User_Default
303: FROM BSC_KPI_ANALYSIS_OPTIONS_B
304: WHERE Indicator = p_Kpi_Id
305: AND Analysis_Group_Id = p_group_Id
306: AND User_Level0 = 1;
307: IF (l_User_Default = 0) THEN

Line 318: UPDATE BSC_KPI_ANALYSIS_OPTIONS_B

314: l_next_option := 0;
315: END IF;
316: CLOSE c_option_id;
317:
318: UPDATE BSC_KPI_ANALYSIS_OPTIONS_B
319: SET User_Level0 = 1
320: , User_Level1 = 1
321: WHERE Indicator = p_Kpi_Id
322: AND Analysis_Group_Id = p_group_Id

Line 333: FROM BSC_KPI_ANALYSIS_OPTIONS_B

329: l_Default_Modified := TRUE;
330: END IF;
331: ELSIF (p_group_Id = 1) THEN
332: SELECT COUNT(*) INTO l_User_Default
333: FROM BSC_KPI_ANALYSIS_OPTIONS_B
334: WHERE Indicator = p_Kpi_Id
335: AND Analysis_Group_Id = p_group_Id
336: AND User_Level0 = 1;
337: IF (l_User_Default = 0) THEN

Line 348: UPDATE BSC_KPI_ANALYSIS_OPTIONS_B

344: l_next_option := 0;
345: END IF;
346: CLOSE c_option_id;
347:
348: UPDATE BSC_KPI_ANALYSIS_OPTIONS_B
349: SET User_Level0 = 1
350: , User_Level1 = 1
351: WHERE Indicator = p_Kpi_Id
352: AND Analysis_Group_Id = p_group_Id

Line 364: FROM BSC_KPI_ANALYSIS_OPTIONS_B

360: l_Default_Modified := TRUE;
361: END IF;
362: ELSIF (p_group_Id = 2) THEN
363: SELECT COUNT(*) INTO l_User_Default
364: FROM BSC_KPI_ANALYSIS_OPTIONS_B
365: WHERE Indicator = p_Kpi_Id
366: AND Analysis_Group_Id = p_group_Id
367: AND User_Level0 = 1;
368: IF (l_User_Default = 0) THEN

Line 379: UPDATE BSC_KPI_ANALYSIS_OPTIONS_B

375: l_next_option := 0;
376: END IF;
377: CLOSE c_option_id;
378:
379: UPDATE BSC_KPI_ANALYSIS_OPTIONS_B
380: SET User_Level0 = 1
381: , User_Level1 = 1
382: WHERE Indicator = p_Kpi_Id
383: AND Analysis_Group_Id = p_group_Id

Line 399: FROM BSC_KPI_ANALYSIS_OPTIONS_B

395: END IF;
396:
397: SELECT COUNT(*)
398: INTO l_count
399: FROM BSC_KPI_ANALYSIS_OPTIONS_B
400: WHERE Indicator = p_Kpi_Id
401: AND Analysis_Group_Id = p_group_Id;
402: IF(l_count =0) THEN
403: l_Default_Modified := TRUE;

Line 444: FROM BSC_KPI_ANALYSIS_OPTIONS_B

440: l_grand_parent_option_Id NUMBER := -1;
441:
442: CURSOR c_Kpi_InDependent_Opts IS
443: SELECT Option_ID
444: FROM BSC_KPI_ANALYSIS_OPTIONS_B
445: WHERE Indicator = p_Kpi_Id
446: AND Analysis_Group_Id = p_group_Id
447: ORDER BY Option_ID;
448:

Line 451: FROM BSC_KPI_ANALYSIS_OPTIONS_B

447: ORDER BY Option_ID;
448:
449: CURSOR c_Kpi_Dependent_Opts IS
450: SELECT Option_ID
451: FROM BSC_KPI_ANALYSIS_OPTIONS_B
452: WHERE Indicator = p_Kpi_Id
453: AND Analysis_Group_Id = p_group_Id
454: AND Parent_Option_Id = p_parent_option_Id
455: AND GrandParent_Option_Id = p_grand_parent_option_Id

Line 460: FROM BSC_KPI_ANALYSIS_OPTIONS_B

456: ORDER BY Option_ID;
457:
458: CURSOR c_Kpi_Par_Dependent_Opts IS
459: SELECT Parent_Option_Id
460: FROM BSC_KPI_ANALYSIS_OPTIONS_B
461: WHERE Indicator = p_Kpi_Id
462: AND Analysis_Group_Id = 1
463: AND Parent_Option_Id = l_parent_option_Id
464: ORDER BY Parent_Option_Id;

Line 468: FROM BSC_KPI_ANALYSIS_OPTIONS_B

464: ORDER BY Parent_Option_Id;
465:
466: CURSOR c_Kpi_Gra_Par_Dep_Opts IS
467: SELECT Parent_Option_Id
468: FROM BSC_KPI_ANALYSIS_OPTIONS_B
469: WHERE Indicator = p_Kpi_Id
470: AND Analysis_Group_Id = 2
471: AND Parent_Option_Id = l_parent_option_Id
472: ORDER BY Parent_Option_Id;

Line 476: FROM BSC_KPI_ANALYSIS_OPTIONS_B

472: ORDER BY Parent_Option_Id;
473:
474: CURSOR c_Kpi_GraPar_Dependent_Opts IS
475: SELECT GrandParent_Option_Id
476: FROM BSC_KPI_ANALYSIS_OPTIONS_B
477: WHERE Indicator = p_Kpi_Id
478: AND Analysis_Group_Id = 2
479: AND GrandParent_Option_Id = l_grand_parent_option_Id
480: ORDER BY GrandParent_Option_Id;

Line 508: UPDATE BSC_KPI_ANALYSIS_OPTIONS_B

504: IF (l_Count <> cd.Option_Id) THEN
505: l_swap_Table(l_Table_Count).p_AnaOpt_Prev_Id := cd.Option_Id;
506: l_swap_Table(l_Table_Count).p_AnaOpt_Next_Id := l_Count;
507:
508: UPDATE BSC_KPI_ANALYSIS_OPTIONS_B
509: SET Option_ID = l_Count
510: WHERE Indicator = p_Kpi_Id
511: AND Analysis_Group_ID = p_group_Id
512: AND Option_Id = cd.Option_Id;

Line 528: UPDATE BSC_KPI_ANALYSIS_OPTIONS_B

524: --DBMS_OUTPUT.PUT_LINE('l_Grand_Parent_Option_Id <'||l_Grand_Parent_Option_Id||'>');
525: --DBMS_OUTPUT.PUT_LINE('l_swap_Table('||l_Table_Count||').p_AnaOpt_Prev_Id <'||cd.Option_Id||'>');
526: --DBMS_OUTPUT.PUT_LINE('l_swap_Table('||l_Table_Count||').p_AnaOpt_Next_Id <'||l_Count||'>');
527: FOR ck IN c_Kpi_Gra_Par_Dep_Opts LOOP
528: UPDATE BSC_KPI_ANALYSIS_OPTIONS_B
529: SET parent_option_id = l_Count
530: WHERE Indicator = p_Kpi_Id
531: AND Analysis_Group_ID = 2
532: AND Parent_Option_Id = ck.Parent_Option_Id;

Line 542: UPDATE BSC_KPI_ANALYSIS_OPTIONS_B

538: AND Parent_Option_Id = ck.Parent_Option_Id;
539: END LOOP;
540:
541: FOR cn1 IN c_Kpi_GraPar_Dependent_Opts LOOP
542: UPDATE BSC_KPI_ANALYSIS_OPTIONS_B
543: SET Grandparent_Option_Id = l_Count
544: WHERE Indicator = p_Kpi_Id
545: AND Analysis_Group_ID = 2
546: AND Grandparent_Option_Id = cn1.GrandParent_Option_Id;

Line 608: UPDATE BSC_KPI_ANALYSIS_OPTIONS_B

604: l_swap_Table(l_Table_Count).p_AnaOpt_Prev_Id := cd.Option_Id;
605: l_swap_Table(l_Table_Count).p_AnaOpt_Next_Id := l_Count;
606: --DBMS_OUTPUT.PUT_LINE('*** SWAP ***');
607: IF (p_Group_Id = 0) THEN
608: UPDATE BSC_KPI_ANALYSIS_OPTIONS_B
609: SET Option_ID = l_Count
610: WHERE Indicator = p_Kpi_Id
611: AND Option_Id = cd.Option_Id
612: AND Analysis_Group_ID = p_Group_Id

Line 626: UPDATE BSC_KPI_ANALYSIS_OPTIONS_B

622: AND Grandparent_Option_Id = p_Grand_Parent_Option_Id;
623: l_Parent_Option_Id := cd.Option_Id;
624: l_Grand_Parent_Option_Id := cd.Option_Id;
625: FOR cm IN c_Kpi_Par_Dependent_Opts LOOP
626: UPDATE BSC_KPI_ANALYSIS_OPTIONS_B
627: SET parent_option_id = l_Count
628: WHERE Indicator = p_Kpi_Id
629: AND Analysis_Group_ID = 1
630: AND Parent_Option_Id = cm.Parent_Option_Id;

Line 639: UPDATE BSC_KPI_ANALYSIS_OPTIONS_B

635: AND Analysis_Group_ID = 1
636: AND Parent_Option_Id = cm.Parent_Option_Id;
637: END LOOP;
638: FOR ck IN c_Kpi_Gra_Par_Dep_Opts LOOP
639: UPDATE BSC_KPI_ANALYSIS_OPTIONS_B
640: SET parent_option_id = l_Count
641: WHERE Indicator = p_Kpi_Id
642: AND Analysis_Group_ID = 2
643: AND Parent_Option_Id = ck.Parent_Option_Id;

Line 652: UPDATE BSC_KPI_ANALYSIS_OPTIONS_B

648: AND Analysis_Group_ID = 2
649: AND Parent_Option_Id = ck.Parent_Option_Id;
650: END LOOP;
651: FOR cn1 IN c_Kpi_GraPar_Dependent_Opts LOOP
652: UPDATE BSC_KPI_ANALYSIS_OPTIONS_B
653: SET Grandparent_Option_Id = l_Count
654: WHERE Indicator = p_Kpi_Id
655: AND Analysis_Group_ID = 2
656: AND Grandparent_Option_Id = cn1.GrandParent_Option_Id;

Line 665: UPDATE BSC_KPI_ANALYSIS_OPTIONS_B

661: AND Analysis_Group_ID = 2
662: AND Grandparent_Option_Id = cn1.GrandParent_Option_Id;
663: END LOOP;
664: ELSIF (p_Group_Id = 1) THEN
665: UPDATE BSC_KPI_ANALYSIS_OPTIONS_B
666: SET Option_ID = l_Count
667: WHERE Indicator = p_Kpi_Id
668: AND Option_Id = cd.Option_Id
669: AND Analysis_Group_ID = p_Group_Id

Line 683: UPDATE BSC_KPI_ANALYSIS_OPTIONS_B

679: AND Grandparent_Option_Id = p_Grand_Parent_Option_Id;
680:
681: l_Parent_Option_Id := cd.Option_Id;
682: FOR cm IN c_Kpi_Par_Dependent_Opts LOOP
683: UPDATE BSC_KPI_ANALYSIS_OPTIONS_B
684: SET parent_option_id = l_Count
685: WHERE Indicator = p_Kpi_Id
686: AND Analysis_Group_ID = 1
687: AND parent_option_id = cm.Parent_Option_Id;

Line 696: UPDATE BSC_KPI_ANALYSIS_OPTIONS_B

692: AND Analysis_Group_ID = 1
693: AND parent_option_id = cm.Parent_Option_Id;
694: END LOOP;
695: ELSIF (p_Group_Id = 2) THEN
696: UPDATE BSC_KPI_ANALYSIS_OPTIONS_B
697: SET Option_ID = l_Count
698: WHERE Indicator = p_Kpi_Id
699: AND Option_Id = cd.Option_Id
700: AND Analysis_Group_ID = p_Group_Id

Line 813: g_db_object := 'BSC_KPI_ANALYSIS_OPTIONS_B';

809: FND_MSG_PUB.ADD;
810: RAISE FND_API.G_EXC_ERROR;
811: end if;
812:
813: g_db_object := 'BSC_KPI_ANALYSIS_OPTIONS_B';
814:
815: -- Insert pertaining values into table bsc_kpi_analysis_options_b.
816: INSERT INTO BSC_KPI_ANALYSIS_OPTIONS_B( INDICATOR
817: ,ANALYSIS_GROUP_ID

Line 815: -- Insert pertaining values into table bsc_kpi_analysis_options_b.

811: end if;
812:
813: g_db_object := 'BSC_KPI_ANALYSIS_OPTIONS_B';
814:
815: -- Insert pertaining values into table bsc_kpi_analysis_options_b.
816: INSERT INTO BSC_KPI_ANALYSIS_OPTIONS_B( INDICATOR
817: ,ANALYSIS_GROUP_ID
818: ,OPTION_ID
819: ,PARENT_OPTION_ID

Line 816: INSERT INTO BSC_KPI_ANALYSIS_OPTIONS_B( INDICATOR

812:
813: g_db_object := 'BSC_KPI_ANALYSIS_OPTIONS_B';
814:
815: -- Insert pertaining values into table bsc_kpi_analysis_options_b.
816: INSERT INTO BSC_KPI_ANALYSIS_OPTIONS_B( INDICATOR
817: ,ANALYSIS_GROUP_ID
818: ,OPTION_ID
819: ,PARENT_OPTION_ID
820: ,GRANDPARENT_OPTION_ID

Line 1175: update BSC_KPI_ANALYSIS_OPTIONS_B

1171: IF ((p_Data_Source IS NOT NULL) AND
1172: (p_Data_Source = 'BSC') AND
1173: (p_Anal_Opt_Rec.Bsc_Grandparent_Option_Id IS NULL) AND
1174: (p_Anal_Opt_Rec.Bsc_Parent_Option_Id IS NOT NULL )) THEN
1175: update BSC_KPI_ANALYSIS_OPTIONS_B
1176: set grandparent_option_id = l_Anal_Opt_Rec.Bsc_Grandparent_Option_Id
1177: ,dim_set_id = l_Anal_Opt_Rec.Bsc_Dim_Set_Id
1178: ,user_level0 = l_Anal_Opt_Rec.Bsc_User_Level0
1179: ,user_level1 = l_Anal_Opt_Rec.Bsc_User_Level1

Line 1202: update BSC_KPI_ANALYSIS_OPTIONS_B

1198: ELSIF ((p_Data_Source IS NOT NULL) AND
1199: (p_Data_Source = 'BSC') AND
1200: (p_Anal_Opt_Rec.Bsc_Grandparent_Option_Id IS NOT NULL) AND
1201: (p_Anal_Opt_Rec.Bsc_Parent_Option_Id IS NOT NULL )) THEN
1202: update BSC_KPI_ANALYSIS_OPTIONS_B
1203: set dim_set_id = l_Anal_Opt_Rec.Bsc_Dim_Set_Id
1204: ,user_level0 = l_Anal_Opt_Rec.Bsc_User_Level0
1205: ,user_level1 = l_Anal_Opt_Rec.Bsc_User_Level1
1206: ,user_level1_default = l_Anal_Opt_Rec.Bsc_User_Level1_Default

Line 1227: update BSC_KPI_ANALYSIS_OPTIONS_B

1223: and parent_option_id = p_Anal_Opt_Rec.Bsc_Parent_Option_Id
1224: and grandparent_option_id = p_Anal_Opt_Rec.Bsc_Grandparent_Option_Id
1225: and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
1226: ELSE
1227: update BSC_KPI_ANALYSIS_OPTIONS_B
1228: set parent_option_id = l_Anal_Opt_Rec.Bsc_Parent_Option_Id
1229: ,grandparent_option_id = l_Anal_Opt_Rec.Bsc_Grandparent_Option_Id
1230: ,dim_set_id = l_Anal_Opt_Rec.Bsc_Dim_Set_Id
1231: ,user_level0 = l_Anal_Opt_Rec.Bsc_User_Level0

Line 1306: l_Group_ID BSC_KPI_ANALYSIS_OPTIONS_B.Analysis_Group_Id%TYPE;

1302: , x_return_status OUT NOCOPY VARCHAR2
1303: , x_msg_count OUT NOCOPY NUMBER
1304: , x_msg_data OUT NOCOPY VARCHAR2
1305: ) IS
1306: l_Group_ID BSC_KPI_ANALYSIS_OPTIONS_B.Analysis_Group_Id%TYPE;
1307: l_Anal_Opt_Rec BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type;
1308: l_Bsc_Kpi_Entity_Rec BSC_KPI_PUB.Bsc_Kpi_Entity_Rec;
1309:
1310: l_AnaOpt_Delete BOOLEAN := TRUE;

Line 1384: FROM BSC_KPI_ANALYSIS_OPTIONS_B

1380: --DBMS_OUTPUT.PUT_LINE('l_Anal_Opt_Rec.Bsc_Grandparent_Option_Id <'||l_Anal_Opt_Rec.Bsc_Grandparent_Option_Id||'>');
1381: --DBMS_OUTPUT.PUT_LINE('l_Anal_Opt_Rec.Bsc_Analysis_Group_Id <'||l_Anal_Opt_Rec.Bsc_Analysis_Group_Id||'>');
1382: IF (l_AnaOpt_Delete) THEN
1383: SELECT MAX(Analysis_Group_Id) INTO l_Group_ID
1384: FROM BSC_KPI_ANALYSIS_OPTIONS_B
1385: WHERE Indicator = l_Anal_Opt_Rec.Bsc_Kpi_Id;
1386:
1387: IF (l_Group_ID = 0) THEN
1388: l_Anal_Opt_Rec.Bsc_Parent_Option_Id := NULL;

Line 1406: DELETE FROM BSC_KPI_ANALYSIS_OPTIONS_B

1402: END IF;
1403: IF ((l_Anal_Opt_Rec.Bsc_Analysis_Option_Id IS NOT NULL) AND
1404: (l_Anal_Opt_Rec.Bsc_Parent_Option_Id IS NOT NULL) AND
1405: (l_Anal_Opt_Rec.Bsc_Grandparent_Option_Id IS NOT NULL)) THEN
1406: DELETE FROM BSC_KPI_ANALYSIS_OPTIONS_B
1407: WHERE indicator = l_Anal_Opt_Rec.Bsc_Kpi_Id
1408: AND analysis_group_id = NVL(l_Anal_Opt_Rec.Bsc_Analysis_Group_Id, 2)
1409: AND option_id = l_Anal_Opt_Rec.Bsc_Analysis_Option_Id
1410: AND parent_option_id = l_Anal_Opt_Rec.Bsc_Parent_Option_Id

Line 1422: DELETE FROM BSC_KPI_ANALYSIS_OPTIONS_B

1418: AND grandparent_option_id = l_Anal_Opt_Rec.Bsc_Grandparent_Option_Id;
1419:
1420: ELSIF ((l_Anal_Opt_Rec.Bsc_Analysis_Option_Id IS NOT NULL) AND
1421: (l_Anal_Opt_Rec.Bsc_Parent_Option_Id IS NOT NULL)) THEN
1422: DELETE FROM BSC_KPI_ANALYSIS_OPTIONS_B
1423: WHERE indicator = l_Anal_Opt_Rec.Bsc_Kpi_Id
1424: AND analysis_group_id = NVL(l_Anal_Opt_Rec.Bsc_Analysis_Group_Id, 1)
1425: AND option_id = l_Anal_Opt_Rec.Bsc_Analysis_Option_Id
1426: AND parent_option_id = l_Anal_Opt_Rec.Bsc_Parent_Option_Id;

Line 1435: DELETE FROM BSC_KPI_ANALYSIS_OPTIONS_B

1431: AND option_id = l_Anal_Opt_Rec.Bsc_Analysis_Option_Id
1432: AND parent_option_id = l_Anal_Opt_Rec.Bsc_Parent_Option_Id;
1433:
1434: ELSE
1435: DELETE FROM BSC_KPI_ANALYSIS_OPTIONS_B
1436: WHERE indicator = l_Anal_Opt_Rec.Bsc_Kpi_Id
1437: AND analysis_group_id = NVL(l_Anal_Opt_Rec.Bsc_Analysis_Group_Id, 0)
1438: AND option_id = l_Anal_Opt_Rec.Bsc_Analysis_Option_Id;
1439:

Line 1497: delete from BSC_KPI_ANALYSIS_OPTIONS_B

1493: IF (l_AnaOpt_Delete) THEN
1494: IF ((l_Anal_Opt_Rec.Bsc_Analysis_Option_Id IS NOT NULL) AND
1495: (l_Anal_Opt_Rec.Bsc_Parent_Option_Id IS NOT NULL) AND
1496: (l_Anal_Opt_Rec.Bsc_Grandparent_Option_Id IS NOT NULL)) THEN
1497: delete from BSC_KPI_ANALYSIS_OPTIONS_B
1498: where indicator = l_Anal_Opt_Rec.Bsc_Kpi_Id
1499: and analysis_group_id = NVL(l_Anal_Opt_Rec.Bsc_Analysis_Group_Id, 2)
1500: and option_id = l_Anal_Opt_Rec.Bsc_Analysis_Option_Id
1501: and parent_option_id = l_Anal_Opt_Rec.Bsc_Parent_Option_Id

Line 1512: delete from BSC_KPI_ANALYSIS_OPTIONS_B

1508: and parent_option_id = l_Anal_Opt_Rec.Bsc_Parent_Option_Id
1509: and grandparent_option_id = l_Anal_Opt_Rec.Bsc_Grandparent_Option_Id;
1510: ELSIF ((l_Anal_Opt_Rec.Bsc_Analysis_Option_Id IS NOT NULL) AND
1511: (l_Anal_Opt_Rec.Bsc_Parent_Option_Id IS NOT NULL)) THEN
1512: delete from BSC_KPI_ANALYSIS_OPTIONS_B
1513: where indicator = l_Anal_Opt_Rec.Bsc_Kpi_Id
1514: and analysis_group_id = NVL(l_Anal_Opt_Rec.Bsc_Analysis_Group_Id, 1)
1515: and option_id = l_Anal_Opt_Rec.Bsc_Analysis_Option_Id
1516: and parent_option_id = l_Anal_Opt_Rec.Bsc_Parent_Option_Id;

Line 1524: delete from BSC_KPI_ANALYSIS_OPTIONS_B

1520: and analysis_group_id = NVL(l_Anal_Opt_Rec.Bsc_Analysis_Group_Id, 1)
1521: and option_id = l_Anal_Opt_Rec.Bsc_Analysis_Option_Id
1522: and parent_option_id = l_Anal_Opt_Rec.Bsc_Parent_Option_Id;
1523: ELSE
1524: delete from BSC_KPI_ANALYSIS_OPTIONS_B
1525: where indicator = l_Anal_Opt_Rec.Bsc_Kpi_Id
1526: and analysis_group_id = NVL(l_Anal_Opt_Rec.Bsc_Analysis_Group_Id, 0)
1527: and option_id = l_Anal_Opt_Rec.Bsc_Analysis_Option_Id;
1528:

Line 2214: FROM BSC_KPI_ANALYSIS_OPTIONS_B A

2210: CURSOR c_GrandParent_Option IS
2211: SELECT A.Option_ID Option_Id
2212: , B.Option_ID Parent_Option_Id
2213: , C.Option_ID GrandParent_Option_Id
2214: FROM BSC_KPI_ANALYSIS_OPTIONS_B A
2215: , BSC_KPI_ANALYSIS_OPTIONS_B B
2216: , BSC_KPI_ANALYSIS_OPTIONS_B C
2217: WHERE A.Indicator = B.Indicator
2218: AND A.Indicator = C.Indicator

Line 2215: , BSC_KPI_ANALYSIS_OPTIONS_B B

2211: SELECT A.Option_ID Option_Id
2212: , B.Option_ID Parent_Option_Id
2213: , C.Option_ID GrandParent_Option_Id
2214: FROM BSC_KPI_ANALYSIS_OPTIONS_B A
2215: , BSC_KPI_ANALYSIS_OPTIONS_B B
2216: , BSC_KPI_ANALYSIS_OPTIONS_B C
2217: WHERE A.Indicator = B.Indicator
2218: AND A.Indicator = C.Indicator
2219: AND A.Analysis_Group_Id = 0

Line 2216: , BSC_KPI_ANALYSIS_OPTIONS_B C

2212: , B.Option_ID Parent_Option_Id
2213: , C.Option_ID GrandParent_Option_Id
2214: FROM BSC_KPI_ANALYSIS_OPTIONS_B A
2215: , BSC_KPI_ANALYSIS_OPTIONS_B B
2216: , BSC_KPI_ANALYSIS_OPTIONS_B C
2217: WHERE A.Indicator = B.Indicator
2218: AND A.Indicator = C.Indicator
2219: AND A.Analysis_Group_Id = 0
2220: AND B.Analysis_Group_Id = 1

Line 2227: FROM BSC_KPI_ANALYSIS_OPTIONS_B A

2223:
2224: CURSOR c_Parent_Option IS
2225: SELECT A.Option_ID Option_Id
2226: , B.Option_ID Parent_Option_Id
2227: FROM BSC_KPI_ANALYSIS_OPTIONS_B A
2228: , BSC_KPI_ANALYSIS_OPTIONS_B B
2229: WHERE A.Indicator = B.Indicator
2230: AND A.Analysis_Group_Id = 0
2231: AND B.Analysis_Group_Id = 1

Line 2228: , BSC_KPI_ANALYSIS_OPTIONS_B B

2224: CURSOR c_Parent_Option IS
2225: SELECT A.Option_ID Option_Id
2226: , B.Option_ID Parent_Option_Id
2227: FROM BSC_KPI_ANALYSIS_OPTIONS_B A
2228: , BSC_KPI_ANALYSIS_OPTIONS_B B
2229: WHERE A.Indicator = B.Indicator
2230: AND A.Analysis_Group_Id = 0
2231: AND B.Analysis_Group_Id = 1
2232: AND A.Indicator = p_Anal_Opt_Rec.Bsc_Kpi_Id;

Line 2332: FROM BSC_KPI_ANALYSIS_OPTIONS_B A

2328: IF ((l_GrandParent_Dependent > 0) AND (l_Parent_Dependent > 0)) THEN
2329: IF ((is_GrandParent_Exists(p_Anal_Opt_Rec.Bsc_Kpi_Id, cd.Option_Id, 2)) AND
2330: (is_Parent_Exists(p_Anal_Opt_Rec.Bsc_Kpi_Id, cd.Parent_Option_Id, 2))) THEN
2331: SELECT COUNT(*) INTO l_Count
2332: FROM BSC_KPI_ANALYSIS_OPTIONS_B A
2333: , BSC_KPI_ANALYSIS_MEASURES_B D
2334: WHERE D.Indicator = A.Indicator
2335: AND A.Analysis_Group_Id = 2
2336: AND A.Option_Id = D.Analysis_Option2

Line 2365: FROM BSC_KPI_ANALYSIS_OPTIONS_B A

2361: --DBMS_OUTPUT.PUT_LINE('BOTH -- 0 <'||l_Count||'> <'||cd.Option_Id||'> <'||cd.Parent_Option_Id||'> <'||cd.GrandParent_Option_Id||'>');
2362: ELSIF (l_Parent_Dependent > 0) THEN
2363: IF (is_Parent_Exists(p_Anal_Opt_Rec.Bsc_Kpi_Id, cd.Option_Id, 1)) THEN
2364: SELECT COUNT(*) INTO l_Count
2365: FROM BSC_KPI_ANALYSIS_OPTIONS_B A
2366: , BSC_KPI_ANALYSIS_MEASURES_B D
2367: WHERE D.Indicator = A.Indicator
2368: AND A.Analysis_Group_Id = 1
2369: AND A.Option_Id = D.Analysis_Option1

Line 2385: FROM BSC_KPI_ANALYSIS_OPTIONS_B A

2381: --DBMS_OUTPUT.PUT_LINE('L_PARENT_DEPENDENT -- <'||l_Count||'> <'||cd.Option_Id||'> <'||cd.Parent_Option_Id||'> <'||cd.GrandParent_Option_Id||'>');
2382: ELSIF (l_GrandParent_Dependent > 0) THEN
2383: IF (is_Parent_Exists(p_Anal_Opt_Rec.Bsc_Kpi_Id, cd.Parent_Option_Id, 2)) THEN
2384: SELECT COUNT(*) INTO l_Count
2385: FROM BSC_KPI_ANALYSIS_OPTIONS_B A
2386: , BSC_KPI_ANALYSIS_MEASURES_B D
2387: WHERE D.Indicator = A.Indicator
2388: AND A.Analysis_Group_Id = 2
2389: AND A.Parent_Option_Id = D.Analysis_Option1

Line 2457: FROM BSC_KPI_ANALYSIS_OPTIONS_B

2453: AND Analysis_Group_Id = 1;
2454:
2455: SELECT COUNT(*)
2456: INTO l_count
2457: FROM BSC_KPI_ANALYSIS_OPTIONS_B
2458: WHERE INDICATOR = p_Anal_Opt_Rec.Bsc_Kpi_Id
2459: AND Analysis_Group_Id = 1;
2460: END IF;
2461: IF (l_Parent_Dependent > 0) THEN

Line 2464: FROM BSC_KPI_ANALYSIS_OPTIONS_B A

2460: END IF;
2461: IF (l_Parent_Dependent > 0) THEN
2462: IF (is_Parent_Exists(p_Anal_Opt_Rec.Bsc_Kpi_Id, cd.Option_Id, 1)) THEN
2463: SELECT COUNT(*) INTO l_Count
2464: FROM BSC_KPI_ANALYSIS_OPTIONS_B A
2465: , BSC_KPI_ANALYSIS_MEASURES_B D
2466: WHERE D.Indicator = A.Indicator
2467: AND A.Analysis_Group_Id = 1
2468: AND A.Option_Id = D.Analysis_Option1

Line 2624: , BSC_KPI_ANALYSIS_OPTIONS_B B

2620: CURSOR c_kpi_ids IS
2621: SELECT DISTINCT A.Indicator
2622: , B.User_Level1
2623: FROM BSC_KPIS_B A
2624: , BSC_KPI_ANALYSIS_OPTIONS_B B
2625: WHERE Source_Indicator = p_kpi_id
2626: AND A.Indicator = B.Indicator
2627: AND B.analysis_group_id = p_anal_group_id
2628: AND B.option_id = p_anal_option_id

Line 2645: FROM BSC_KPI_ANALYSIS_OPTIONS_B

2641:
2642: -- Now check that this is not the last analysis Option, if it is then no deletion.
2643: SELECT COUNT(option_id)
2644: INTO l_value
2645: FROM BSC_KPI_ANALYSIS_OPTIONS_B
2646: WHERE indicator = p_kpi_id;
2647: IF (l_value < 2) THEN
2648: RETURN 'L';
2649: END IF;

Line 2726: l_option_id BSC_KPI_ANALYSIS_OPTIONS_B.Option_Id%TYPE;

2722: , p_Anal_Opt_Comb_Tbl IN BSC_ANALYSIS_OPTION_PUB.Anal_Opt_Comb_Num_Tbl_Type
2723: , p_Anal_Det_Opt_Tbl IN OUT NOCOPY BSC_ANALYSIS_OPTION_PUB.Bsc_Anal_Opt_Det_Tbl_Type
2724: )IS
2725: l_group_count NUMBER;
2726: l_option_id BSC_KPI_ANALYSIS_OPTIONS_B.Option_Id%TYPE;
2727: l_parent_option_id BSC_KPI_ANALYSIS_OPTIONS_B.Parent_Option_Id%TYPE;
2728: l_grand_parent_option_id BSC_KPI_ANALYSIS_OPTIONS_B.Grandparent_Option_Id%TYPE;
2729: l_dependent BSC_KPI_ANALYSIS_GROUPS.dependency_flag%TYPE;
2730: l_no_child NUMBER;

Line 2727: l_parent_option_id BSC_KPI_ANALYSIS_OPTIONS_B.Parent_Option_Id%TYPE;

2723: , p_Anal_Det_Opt_Tbl IN OUT NOCOPY BSC_ANALYSIS_OPTION_PUB.Bsc_Anal_Opt_Det_Tbl_Type
2724: )IS
2725: l_group_count NUMBER;
2726: l_option_id BSC_KPI_ANALYSIS_OPTIONS_B.Option_Id%TYPE;
2727: l_parent_option_id BSC_KPI_ANALYSIS_OPTIONS_B.Parent_Option_Id%TYPE;
2728: l_grand_parent_option_id BSC_KPI_ANALYSIS_OPTIONS_B.Grandparent_Option_Id%TYPE;
2729: l_dependent BSC_KPI_ANALYSIS_GROUPS.dependency_flag%TYPE;
2730: l_no_child NUMBER;
2731: l_Anal_grp_Id BSC_KPI_ANALYSIS_OPTIONS_B.Analysis_Group_Id%TYPE;

Line 2728: l_grand_parent_option_id BSC_KPI_ANALYSIS_OPTIONS_B.Grandparent_Option_Id%TYPE;

2724: )IS
2725: l_group_count NUMBER;
2726: l_option_id BSC_KPI_ANALYSIS_OPTIONS_B.Option_Id%TYPE;
2727: l_parent_option_id BSC_KPI_ANALYSIS_OPTIONS_B.Parent_Option_Id%TYPE;
2728: l_grand_parent_option_id BSC_KPI_ANALYSIS_OPTIONS_B.Grandparent_Option_Id%TYPE;
2729: l_dependent BSC_KPI_ANALYSIS_GROUPS.dependency_flag%TYPE;
2730: l_no_child NUMBER;
2731: l_Anal_grp_Id BSC_KPI_ANALYSIS_OPTIONS_B.Analysis_Group_Id%TYPE;
2732:

Line 2731: l_Anal_grp_Id BSC_KPI_ANALYSIS_OPTIONS_B.Analysis_Group_Id%TYPE;

2727: l_parent_option_id BSC_KPI_ANALYSIS_OPTIONS_B.Parent_Option_Id%TYPE;
2728: l_grand_parent_option_id BSC_KPI_ANALYSIS_OPTIONS_B.Grandparent_Option_Id%TYPE;
2729: l_dependent BSC_KPI_ANALYSIS_GROUPS.dependency_flag%TYPE;
2730: l_no_child NUMBER;
2731: l_Anal_grp_Id BSC_KPI_ANALYSIS_OPTIONS_B.Analysis_Group_Id%TYPE;
2732:
2733:
2734: CURSOR c_grp_one_details IS
2735: SELECT Option_Id,Parent_Option_Id,Grandparent_Option_Id

Line 2736: FROM BSC_KPI_ANALYSIS_OPTIONS_B

2732:
2733:
2734: CURSOR c_grp_one_details IS
2735: SELECT Option_Id,Parent_Option_Id,Grandparent_Option_Id
2736: FROM BSC_KPI_ANALYSIS_OPTIONS_B
2737: WHERE Indicator = p_Kpi_id
2738: AND Analysis_Group_Id = l_group_count
2739: AND Option_Id = l_option_id
2740: AND Parent_Option_Id = l_parent_option_id;

Line 2744: FROM BSC_KPI_ANALYSIS_OPTIONS_B

2740: AND Parent_Option_Id = l_parent_option_id;
2741:
2742: CURSOR c_grp_two_details IS
2743: SELECT Option_Id,Parent_Option_Id,Grandparent_Option_Id
2744: FROM BSC_KPI_ANALYSIS_OPTIONS_B
2745: WHERE Indicator = p_Kpi_id
2746: AND Analysis_Group_Id = l_group_count
2747: AND Option_Id = l_option_id
2748: AND Parent_Option_Id = l_parent_option_id

Line 2754: FROM BSC_KPI_ANALYSIS_OPTIONS_B

2750:
2751:
2752: CURSOR c_grp_zero_details IS
2753: SELECT Option_Id,Parent_Option_Id,Grandparent_Option_Id
2754: FROM BSC_KPI_ANALYSIS_OPTIONS_B
2755: WHERE Indicator = p_Kpi_id
2756: AND Analysis_Group_Id = l_group_count
2757: AND Option_Id = l_option_id;
2758:

Line 2905: l_parent_option_id BSC_KPI_ANALYSIS_OPTIONS_B.Parent_Option_Id%TYPE;

2901: )IS
2902: l_count NUMBER;
2903: l_Anal_Opt_Rec BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type;
2904: l_Source VARCHAR2(3) := 'BSC';
2905: l_parent_option_id BSC_KPI_ANALYSIS_OPTIONS_B.Parent_Option_Id%TYPE;
2906: l_Anal_Det_Opt_Tbl BSC_ANALYSIS_OPTION_PUB.Bsc_Anal_Opt_Det_Tbl_Type;
2907: l_anal_opt_name VARCHAR2(3000);
2908: BEGIN
2909: FND_MSG_PUB.Initialize;

Line 3154: FROM BSC_KPI_ANALYSIS_OPTIONS_B

3150: ((l_Anal_Grp_Opt_Tbl(l_group_count).Bsc_no_option_id) <>
3151: (l_Anal_Opt_Tbl(l_group_count).Bsc_no_option_id))) THEN
3152:
3153: SELECT COUNT(DISTINCT(Option_Id)) INTO l_Num_Opt_Id
3154: FROM BSC_KPI_ANALYSIS_OPTIONS_B
3155: WHERE Indicator = p_Kpi_Id
3156: AND Analysis_Group_Id = l_group_count;
3157:
3158: UPDATE BSC_KPI_ANALYSIS_GROUPS

Line 3228: FROM BSC_KPI_ANALYSIS_OPTIONS_B

3224: ) IS
3225:
3226: CURSOR c_Update_Short_Names IS
3227: SELECT INDICATOR, OPTION_ID
3228: FROM BSC_KPI_ANALYSIS_OPTIONS_B
3229: WHERE INDICATOR = p_Kpi_Id
3230: AND ANALYSIS_GROUP_ID = 0
3231: AND SHORT_NAME IS NOT NULL;
3232:

Line 3240: UPDATE BSC_KPI_ANALYSIS_OPTIONS_B

3236: x_Return_Status := FND_API.G_RET_STS_SUCCESS;
3237:
3238:
3239: FOR cUSN IN c_Update_Short_Names LOOP
3240: UPDATE BSC_KPI_ANALYSIS_OPTIONS_B
3241: SET SHORT_NAME = BSC_ANALYSIS_OPTION_PUB.C_BSC_UNDERSCORE || cUSN.INDICATOR || '_' || cUSN.OPTION_ID
3242: WHERE INDICATOR = cUSN.INDICATOR
3243: AND OPTION_ID = cUSN.OPTION_ID
3244: AND ANALYSIS_GROUP_ID = 0

Line 3340: FROM bsc_kpi_analysis_options_b a, bsc_kpis_vl b

3336: AND a.SERIES_ID = p_series_id;
3337:
3338: CURSOR c_AnaOpt_ak IS
3339: SELECT a.short_name , b.name
3340: FROM bsc_kpi_analysis_options_b a, bsc_kpis_vl b
3341: WHERE a.indicator = p_kpi_id
3342: AND a.option_id = p_option0
3343: AND a.parent_option_id = p_option1
3344: AND a.grandparent_option_id = p_option2

Line 3920: The API ensures that there is exactly one entry in BSC_kPI_ANALYSIS_OPTIONS_B

3916: with default_value = 1.
3917: 3) If One of the default value is being changed from 0 to 1, then the rest of
3918: default_Value is set to 0 and the current series is set to 1.
3919:
3920: The API ensures that there is exactly one entry in BSC_kPI_ANALYSIS_OPTIONS_B
3921: table for DEFAULT_VALUE =1 for the (kpi,option0,option1,option2) combination.
3922:
3923: Appropriate color changes are cascaded into the Objectives (and Shared)
3924: Also changes will be cascaded only if the current analysis option combination