1 package body BSC_KPI_SUBTITLES_PKG as
2 /* $Header: BSCKSBTB.pls 115.6 2003/02/12 14:26:01 adrao ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_INDICATOR in NUMBER,
6 X_CALC_COMB in NUMBER,
7 X_ANALYSIS_OPTION0 in NUMBER,
8 X_ANALYSIS_OPTION1 in NUMBER,
9 X_ANALYSIS_OPTION2 in NUMBER,
10 X_SUBTITLE in VARCHAR2
11 ) is
12 cursor C is select ROWID from BSC_KPI_SUBTITLES_TL
13 where INDICATOR = X_INDICATOR
14 and CALC_COMB = X_CALC_COMB
15 and ANALYSIS_OPTION0 = X_ANALYSIS_OPTION0
16 and ANALYSIS_OPTION1 = X_ANALYSIS_OPTION1
17 and ANALYSIS_OPTION2 = X_ANALYSIS_OPTION2
18 and LANGUAGE = userenv('LANG')
19 ;
20 begin
21 insert into BSC_KPI_SUBTITLES_TL (
22 INDICATOR,
23 CALC_COMB,
24 ANALYSIS_OPTION0,
25 ANALYSIS_OPTION1,
26 ANALYSIS_OPTION2,
27 SUBTITLE,
28 LANGUAGE,
29 SOURCE_LANG
30 ) select
31 X_INDICATOR,
32 X_CALC_COMB,
33 X_ANALYSIS_OPTION0,
34 X_ANALYSIS_OPTION1,
35 X_ANALYSIS_OPTION2,
36 X_SUBTITLE,
37 L.LANGUAGE_CODE,
38 userenv('LANG')
39 from FND_LANGUAGES L
40 where L.INSTALLED_FLAG in ('I', 'B')
41 and not exists
42 (select NULL
43 from BSC_KPI_SUBTITLES_TL T
44 where T.INDICATOR = X_INDICATOR
45 and T.CALC_COMB = X_CALC_COMB
46 and T.ANALYSIS_OPTION0 = X_ANALYSIS_OPTION0
47 and T.ANALYSIS_OPTION1 = X_ANALYSIS_OPTION1
48 and T.ANALYSIS_OPTION2 = X_ANALYSIS_OPTION2
49 and T.LANGUAGE = L.LANGUAGE_CODE);
50
51 open c;
52 fetch c into X_ROWID;
53 if (c%notfound) then
54 close c;
55 raise no_data_found;
56 end if;
57 close c;
58
59 end INSERT_ROW;
60
61 procedure LOCK_ROW (
62 X_INDICATOR in NUMBER,
63 X_CALC_COMB in NUMBER,
64 X_ANALYSIS_OPTION0 in NUMBER,
65 X_ANALYSIS_OPTION1 in NUMBER,
66 X_ANALYSIS_OPTION2 in NUMBER,
67 X_SUBTITLE in VARCHAR2
68 ) is
69 cursor c1 is select
70 SUBTITLE,
71 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
72 from BSC_KPI_SUBTITLES_TL
73 where INDICATOR = X_INDICATOR
74 and CALC_COMB = X_CALC_COMB
75 and ANALYSIS_OPTION0 = X_ANALYSIS_OPTION0
76 and ANALYSIS_OPTION1 = X_ANALYSIS_OPTION1
77 and ANALYSIS_OPTION2 = X_ANALYSIS_OPTION2
78 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
79 for update of INDICATOR nowait;
80 begin
81 for tlinfo in c1 loop
82 if (tlinfo.BASELANG = 'Y') then
83 if ( (tlinfo.SUBTITLE = X_SUBTITLE)
84 ) then
85 null;
86 else
87 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
88 app_exception.raise_exception;
89 end if;
90 end if;
91 end loop;
92 return;
93 end LOCK_ROW;
94
95 procedure UPDATE_ROW (
96 X_INDICATOR in NUMBER,
97 X_CALC_COMB in NUMBER,
98 X_ANALYSIS_OPTION0 in NUMBER,
99 X_ANALYSIS_OPTION1 in NUMBER,
100 X_ANALYSIS_OPTION2 in NUMBER,
101 X_SUBTITLE in VARCHAR2
102 ) is
103 begin
104 update BSC_KPI_SUBTITLES_TL set
105 SUBTITLE = X_SUBTITLE,
106 SOURCE_LANG = userenv('LANG')
107 where INDICATOR = X_INDICATOR
108 and CALC_COMB = X_CALC_COMB
109 and ANALYSIS_OPTION0 = X_ANALYSIS_OPTION0
110 and ANALYSIS_OPTION1 = X_ANALYSIS_OPTION1
111 and ANALYSIS_OPTION2 = X_ANALYSIS_OPTION2
112 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
113
114 if (sql%notfound) then
115 raise no_data_found;
116 end if;
117 end UPDATE_ROW;
118
119 procedure DELETE_ROW (
120 X_INDICATOR in NUMBER,
121 X_CALC_COMB in NUMBER,
122 X_ANALYSIS_OPTION0 in NUMBER,
123 X_ANALYSIS_OPTION1 in NUMBER,
124 X_ANALYSIS_OPTION2 in NUMBER
125 ) is
126 begin
127 delete from BSC_KPI_SUBTITLES_TL
128 where INDICATOR = X_INDICATOR
129 and CALC_COMB = X_CALC_COMB
130 and ANALYSIS_OPTION0 = X_ANALYSIS_OPTION0
131 and ANALYSIS_OPTION1 = X_ANALYSIS_OPTION1
132 and ANALYSIS_OPTION2 = X_ANALYSIS_OPTION2;
133
134 if (sql%notfound) then
135 raise no_data_found;
136 end if;
137
138 end DELETE_ROW;
139
140 procedure ADD_LANGUAGE
141 is
142 begin
143 update BSC_KPI_SUBTITLES_TL T set (
144 SUBTITLE
145 ) = (select
146 B.SUBTITLE
147 from BSC_KPI_SUBTITLES_TL B
148 where B.INDICATOR = T.INDICATOR
149 and B.CALC_COMB = T.CALC_COMB
150 and B.ANALYSIS_OPTION0 = T.ANALYSIS_OPTION0
151 and B.ANALYSIS_OPTION1 = T.ANALYSIS_OPTION1
152 and B.ANALYSIS_OPTION2 = T.ANALYSIS_OPTION2
153 and B.LANGUAGE = T.SOURCE_LANG)
154 where (
155 T.INDICATOR,
156 T.CALC_COMB,
157 T.ANALYSIS_OPTION0,
158 T.ANALYSIS_OPTION1,
159 T.ANALYSIS_OPTION2,
160 T.LANGUAGE
161 ) in (select
162 SUBT.INDICATOR,
163 SUBT.CALC_COMB,
164 SUBT.ANALYSIS_OPTION0,
165 SUBT.ANALYSIS_OPTION1,
166 SUBT.ANALYSIS_OPTION2,
167 SUBT.LANGUAGE
168 from BSC_KPI_SUBTITLES_TL SUBB, BSC_KPI_SUBTITLES_TL SUBT
169 where SUBB.INDICATOR = SUBT.INDICATOR
170 and SUBB.CALC_COMB = SUBT.CALC_COMB
171 and SUBB.ANALYSIS_OPTION0 = SUBT.ANALYSIS_OPTION0
172 and SUBB.ANALYSIS_OPTION1 = SUBT.ANALYSIS_OPTION1
173 and SUBB.ANALYSIS_OPTION2 = SUBT.ANALYSIS_OPTION2
174 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
175 and (SUBB.SUBTITLE <> SUBT.SUBTITLE
176 ));
177
178 insert into BSC_KPI_SUBTITLES_TL (
179 INDICATOR,
180 CALC_COMB,
181 ANALYSIS_OPTION0,
182 ANALYSIS_OPTION1,
183 ANALYSIS_OPTION2,
184 SUBTITLE,
185 LANGUAGE,
186 SOURCE_LANG
187 ) select
188 B.INDICATOR,
189 B.CALC_COMB,
190 B.ANALYSIS_OPTION0,
191 B.ANALYSIS_OPTION1,
192 B.ANALYSIS_OPTION2,
193 B.SUBTITLE,
194 L.LANGUAGE_CODE,
195 B.SOURCE_LANG
196 from BSC_KPI_SUBTITLES_TL B, FND_LANGUAGES L
197 where L.INSTALLED_FLAG in ('I', 'B')
198 and B.LANGUAGE = userenv('LANG')
199 and not exists
200 (select NULL
201 from BSC_KPI_SUBTITLES_TL T
202 where T.INDICATOR = B.INDICATOR
203 and T.CALC_COMB = B.CALC_COMB
204 and T.ANALYSIS_OPTION0 = B.ANALYSIS_OPTION0
205 and T.ANALYSIS_OPTION1 = B.ANALYSIS_OPTION1
206 and T.ANALYSIS_OPTION2 = B.ANALYSIS_OPTION2
207 and T.LANGUAGE = L.LANGUAGE_CODE);
208 end ADD_LANGUAGE;
209
210 end BSC_KPI_SUBTITLES_PKG;
[Home] [Help]