1 package body BSC_SYS_DATASETS_PKG as
2 /* $Header: BSCDSETB.pls 120.0 2005/06/01 17:03:43 appldev noship $ */
3 procedure INSERT_ROW (
4 X_DATASET_ID in NUMBER,
5 X_PROJECTION_FLAG in NUMBER,
6 X_MEASURE_ID1 in NUMBER,
7 X_OPERATION in VARCHAR2,
8 X_MEASURE_ID2 in NUMBER,
9 X_FORMAT_ID in NUMBER,
10 X_COLOR_METHOD in NUMBER,
11 X_NAME in VARCHAR2,
12 X_HELP in VARCHAR2,
13 X_CREATION_DATE in DATE DEFAULT NULL,
14 X_CREATED_BY in NUMBER DEFAULT NULL,
15 X_LAST_UPDATE_DATE in DATE DEFAULT NULL,
16 X_LAST_UPDATED_BY in NUMBER DEFAULT NULL,
17 X_LAST_UPDATE_LOGIN in NUMBER DEFAULT NULL
18 ) is
19 begin
20 insert into BSC_SYS_DATASETS_B (
21 PROJECTION_FLAG,
22 DATASET_ID,
23 MEASURE_ID1,
24 OPERATION,
25 MEASURE_ID2,
26 FORMAT_ID,
27 COLOR_METHOD,
28 CREATION_DATE ,
29 CREATED_BY,
30 LAST_UPDATE_DATE,
31 LAST_UPDATED_BY ,
32 LAST_UPDATE_LOGIN
33 ) values (
34 X_PROJECTION_FLAG,
35 X_DATASET_ID,
36 X_MEASURE_ID1,
37 X_OPERATION,
38 X_MEASURE_ID2,
39 X_FORMAT_ID,
40 X_COLOR_METHOD,
41 X_CREATION_DATE ,
42 X_CREATED_BY,
43 X_LAST_UPDATE_DATE,
44 X_LAST_UPDATED_BY ,
45 X_LAST_UPDATE_LOGIN
46 );
47
48 insert into BSC_SYS_DATASETS_TL (
49 DATASET_ID,
50 NAME,
51 HELP,
52 LANGUAGE,
53 SOURCE_LANG
54 ) select
55 X_DATASET_ID,
56 X_NAME,
57 X_HELP,
58 L.LANGUAGE_CODE,
59 userenv('LANG')
60 from FND_LANGUAGES L
61 where L.INSTALLED_FLAG in ('I', 'B')
62 and not exists
63 (select NULL
64 from BSC_SYS_DATASETS_TL T
65 where T.DATASET_ID = X_DATASET_ID
66 and T.LANGUAGE = L.LANGUAGE_CODE);
67
68 end INSERT_ROW;
69
70 procedure TRANSLATE_ROW(
71 X_DATASET_ID in NUMBER,
72 X_NAME in VARCHAR2,
73 X_HELP in VARCHAR2)
74 is
75 begin
76 update BSC_SYS_DATASETS_TL set
77 NAME = NVL(X_NAME,NAME),
78 HELP = NVL(X_HELP, HELP),
79 SOURCE_LANG = userenv('LANG')
80 where
81 userenv('LANG') in (LANGUAGE, SOURCE_LANG)
82 and dataset_id = X_DATASET_ID;
83 end TRANSLATE_ROW;
84
85 procedure LOCK_ROW (
86 X_DATASET_ID in NUMBER,
87 X_PROJECTION_FLAG in NUMBER,
88 X_MEASURE_ID1 in NUMBER,
89 X_OPERATION in VARCHAR2,
90 X_MEASURE_ID2 in NUMBER,
91 X_FORMAT_ID in NUMBER,
92 X_COLOR_METHOD in NUMBER,
93 X_NAME in VARCHAR2,
94 X_HELP in VARCHAR2
95 ) is
96 cursor c is select
97 PROJECTION_FLAG,
98 MEASURE_ID1,
99 OPERATION,
100 MEASURE_ID2,
101 FORMAT_ID,
102 COLOR_METHOD
103 from BSC_SYS_DATASETS_B
104 where DATASET_ID = X_DATASET_ID
105 for update of DATASET_ID nowait;
106 recinfo c%rowtype;
107
108 cursor c1 is select
109 NAME,
110 HELP,
111 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
112 from BSC_SYS_DATASETS_TL
113 where DATASET_ID = X_DATASET_ID
114 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
115 for update of DATASET_ID nowait;
116 begin
117 open c;
118 fetch c into recinfo;
119 if (c%notfound) then
120 close c;
121 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
122 app_exception.raise_exception;
123 end if;
124 close c;
125 if ( ((recinfo.PROJECTION_FLAG = X_PROJECTION_FLAG)
126 OR ((recinfo.PROJECTION_FLAG is null) AND (X_PROJECTION_FLAG is null)))
127 AND (recinfo.MEASURE_ID1 = X_MEASURE_ID1)
128 AND ((recinfo.OPERATION = X_OPERATION)
129 OR ((recinfo.OPERATION is null) AND (X_OPERATION is null)))
130 AND ((recinfo.MEASURE_ID2 = X_MEASURE_ID2)
131 OR ((recinfo.MEASURE_ID2 is null) AND (X_MEASURE_ID2 is null)))
132 AND (recinfo.FORMAT_ID = X_FORMAT_ID)
133 AND (recinfo.COLOR_METHOD = X_COLOR_METHOD)
134 ) then
135 null;
136 else
137 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
138 app_exception.raise_exception;
139 end if;
140
141 for tlinfo in c1 loop
142 if (tlinfo.BASELANG = 'Y') then
143 if ( (tlinfo.NAME = X_NAME)
144 AND (tlinfo.HELP = X_HELP)
145 ) then
146 null;
147 else
148 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
149 app_exception.raise_exception;
150 end if;
151 end if;
152 end loop;
153 return;
154 end LOCK_ROW;
155
156 procedure UPDATE_ROW (
157 X_DATASET_ID in NUMBER,
158 X_PROJECTION_FLAG in NUMBER,
159 X_MEASURE_ID1 in NUMBER,
160 X_OPERATION in VARCHAR2,
161 X_MEASURE_ID2 in NUMBER,
162 X_FORMAT_ID in NUMBER,
163 X_COLOR_METHOD in NUMBER,
164 X_NAME in VARCHAR2,
165 X_HELP in VARCHAR2,
166 X_CREATION_DATE in DATE DEFAULT NULL,
167 X_CREATED_BY in NUMBER DEFAULT NULL,
168 X_LAST_UPDATE_DATE in DATE DEFAULT NULL,
169 X_LAST_UPDATED_BY in NUMBER DEFAULT NULL,
170 X_LAST_UPDATE_LOGIN in NUMBER DEFAULT NULL
171 ) is
172 begin
173 update BSC_SYS_DATASETS_B set
174 PROJECTION_FLAG = X_PROJECTION_FLAG,
175 MEASURE_ID1 = X_MEASURE_ID1,
176 OPERATION = X_OPERATION,
177 MEASURE_ID2 = X_MEASURE_ID2,
178 FORMAT_ID = X_FORMAT_ID,
179 COLOR_METHOD = X_COLOR_METHOD,
180 LAST_UPDATE_DATE = DECODE(LAST_UPDATED_BY,1,X_LAST_UPDATE_DATE,LAST_UPDATE_DATE),
181 LAST_UPDATED_BY = DECODE(LAST_UPDATED_BY,1,X_LAST_UPDATED_BY,LAST_UPDATED_BY),
182 LAST_UPDATE_LOGIN = DECODE(LAST_UPDATED_BY,1,X_LAST_UPDATE_LOGIN,LAST_UPDATE_LOGIN)
183 where DATASET_ID = X_DATASET_ID;
184
185 if (sql%notfound) then
186 raise no_data_found;
187 end if;
188
189 update BSC_SYS_DATASETS_TL set
190 NAME = X_NAME,
191 HELP = X_HELP,
192 SOURCE_LANG = userenv('LANG')
193 where DATASET_ID = X_DATASET_ID
194 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
195
196 if (sql%notfound) then
197 raise no_data_found;
198 end if;
199 end UPDATE_ROW;
200
201 procedure DELETE_ROW (
202 X_DATASET_ID in NUMBER
203 ) is
204 begin
205 delete from BSC_SYS_DATASETS_TL
206 where DATASET_ID = X_DATASET_ID;
207
208 if (sql%notfound) then
209 raise no_data_found;
210 end if;
211
212 delete from BSC_SYS_DATASETS_B
213 where DATASET_ID = X_DATASET_ID;
214
215 if (sql%notfound) then
216 raise no_data_found;
217 end if;
218 end DELETE_ROW;
219
220 procedure ADD_LANGUAGE
221 is
222 begin
223 delete from BSC_SYS_DATASETS_TL T
224 where not exists
225 (select NULL
226 from BSC_SYS_DATASETS_B B
227 where B.DATASET_ID = T.DATASET_ID
228 );
229
230 update BSC_SYS_DATASETS_TL T set (
231 NAME,
232 HELP,Y_AXIS_TITLE
233 ) = (select
234 B.NAME,
235 B.HELP,B.Y_AXIS_TITLE
236 from BSC_SYS_DATASETS_TL B
237 where B.DATASET_ID = T.DATASET_ID
238 and B.LANGUAGE = T.SOURCE_LANG)
239 where (
240 T.DATASET_ID,
241 T.LANGUAGE
242 ) in (select
243 SUBT.DATASET_ID,
244 SUBT.LANGUAGE
245 from BSC_SYS_DATASETS_TL SUBB, BSC_SYS_DATASETS_TL SUBT
246 where SUBB.DATASET_ID = SUBT.DATASET_ID
247 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
248 and (SUBB.NAME <> SUBT.NAME
249 or SUBB.HELP <> SUBT.HELP or SUBB.Y_AXIS_TITLE <> SUBT.Y_AXIS_TITLE
250 ));
251
252 insert into BSC_SYS_DATASETS_TL (
253 DATASET_ID,
254 NAME,
255 HELP,
256 Y_AXIS_TITLE,
257 LANGUAGE,
258 SOURCE_LANG
259 ) select
260 B.DATASET_ID,
261 B.NAME,
262 B.HELP,
263 B.Y_AXIS_TITLE,
264 L.LANGUAGE_CODE,
265 B.SOURCE_LANG
266 from BSC_SYS_DATASETS_TL B, FND_LANGUAGES L
267 where L.INSTALLED_FLAG in ('I', 'B')
268 and B.LANGUAGE = userenv('LANG')
269 and not exists
270 (select NULL
271 from BSC_SYS_DATASETS_TL T
272 where T.DATASET_ID = B.DATASET_ID
273 and T.LANGUAGE = L.LANGUAGE_CODE);
274 end ADD_LANGUAGE;
275
276 end BSC_SYS_DATASETS_PKG;
[Home] [Help]