[Home] [Help]
PACKAGE BODY: APPS.RG_REPORT_STANDARD_AXES_PKG
Source
1 PACKAGE BODY RG_REPORT_STANDARD_AXES_PKG AS
2 /* $Header: rgirstdb.pls 120.6 2004/09/20 06:19:35 adesu ship $ */
3 -- Name
4 -- rg_report_standard_axes_pkg
5 -- Purpose
6 -- to include all sever side procedures and packages for table
7 -- rg_report_standard_axes
8 -- Notes
9 --
10 -- History
11 -- 11/01/93 A Chen Created
12 --
13 -- PRIVATE VARIABLES
14 -- None.
15 --
16 -- PRIVATE FUNCTIONS
17 -- None.
18 --
19 -- PUBLIC FUNCTIONS
20 --
21 PROCEDURE select_row(recinfo IN OUT NOCOPY rg_report_standard_axes_tl%ROWTYPE) IS
22 BEGIN
23 select * INTO recinfo
24 from rg_report_standard_axes_tl
25 where standard_axis_id = recinfo.standard_axis_id;
26 END select_row;
27
28 PROCEDURE select_columns(X_standard_axis_id NUMBER,
29 X_name IN OUT NOCOPY VARCHAR2) IS
30 recinfo rg_report_standard_axes_tl%ROWTYPE;
31 BEGIN
32 recinfo.standard_axis_id := X_standard_axis_id;
33 select_row(recinfo);
34 X_name := recinfo.standard_axis_name;
35 END select_columns;
36
37 --
38 -- Name
39 -- insert_row
40 -- Purpose
41 -- Insert a row into RG_REPORT_STANDARD_AXES_B , RGE_REPORT_STANDARD_AXES_TL
42 --
43 PROCEDURE insert_row(X_rowid IN OUT NOCOPY VARCHAR2,
44 X_application_id NUMBER,
45 X_last_update_date DATE,
46 X_last_updated_by NUMBER,
47 X_last_update_login NUMBER,
48 X_creation_date DATE,
49 X_created_by NUMBER,
50 X_standard_axis_id NUMBER,
51 X_standard_axis_name VARCHAR2,
52 X_class VARCHAR2,
53 X_display_in_std_list_flag VARCHAR2,
54 X_precedence_level NUMBER,
55 X_database_column VARCHAR2,
56 X_simple_where_name VARCHAR2,
57 X_period_query VARCHAR2,
58 X_standard_axis1_id NUMBER,
59 X_axis1_operator VARCHAR2,
60 X_standard_axis2_id NUMBER,
61 X_axis2_operator VARCHAR2,
62 X_constant NUMBER,
63 X_variance_flag VARCHAR2,
64 X_sign_flag VARCHAR2,
65 X_description VARCHAR2
66 ) IS
67 BEGIN
68
69 INSERT INTO RG_REPORT_STANDARD_AXES_B (
70 application_id,
71 standard_axis_id,
72 last_update_date,
73 last_updated_by,
74 last_update_login,
75 creation_date,
76 created_by,
77 class,
78 display_in_standard_list_flag,
79 precedence_level,
80 database_column,
81 simple_where_name,
82 period_query,
83 standard_axis1_id,
84 axis1_operator,
85 standard_axis2_id,
86 axis2_operator,
87 constant,
88 variance_flag,
89 sign_flag )
90 VALUES
91 ( X_application_id,
92 X_standard_axis_id,
93 X_last_update_date,
94 X_last_updated_by,
95 X_last_update_login,
96 X_creation_date,
97 X_created_by,
98 X_class,
99 X_display_in_std_list_flag,
100 X_precedence_level,
101 X_database_column,
102 X_simple_where_name,
103 X_period_query,
104 X_standard_axis1_id,
105 X_axis1_operator,
106 X_standard_axis2_id,
107 X_axis2_operator,
108 X_constant,
109 X_variance_flag,
110 X_sign_flag
111 );
112
113
114 INSERT INTO RG_REPORT_STANDARD_AXES_TL
115 (
116 STANDARD_AXIS_ID,
117 LANGUAGE,
118 SOURCE_LANG,
119 STANDARD_AXIS_NAME,
120 LAST_UPDATE_DATE,
121 LAST_UPDATED_BY,
122 LAST_UPDATE_LOGIN,
123 CREATION_DATE,
124 CREATED_BY,
125 DESCRIPTION
126 )
127 SELECT
128 X_standard_axis_id,
129 L.language_code,
130 userenv('LANG'),
131 X_standard_axis_name,
132 X_last_update_date,
133 X_last_updated_by,
134 X_last_update_login,
135 X_creation_date,
136 X_created_by,
137 X_description
138 FROM FND_LANGUAGES L
139 WHERE L.installed_flag IN ('I', 'B')
140 AND NOT EXISTS
141 ( SELECT NULL
142 FROM RG_REPORT_STANDARD_AXES_TL R
143 WHERE R.standard_axis_id = X_standard_axis_id
144 AND R.language = L.language_code );
145
146
147 END insert_row;
148
149 --
150 -- Name
151 -- update_row
152 -- Purpose
153 -- Update a row in RG_REPORT_STANDARD_AXES
154 --
155 PROCEDURE update_row(X_rowid IN OUT NOCOPY VARCHAR2,
156 X_application_id NUMBER,
157 X_standard_axis_id NUMBER,
158 X_standard_axis_name VARCHAR2,
159 X_last_update_date DATE,
160 X_last_updated_by NUMBER,
161 X_last_update_login NUMBER,
162 X_class VARCHAR2,
163 X_display_in_std_list_flag VARCHAR2,
164 X_precedence_level NUMBER,
165 X_database_column VARCHAR2,
166 X_simple_where_name VARCHAR2,
167 X_period_query VARCHAR2,
168 X_standard_axis1_id NUMBER,
169 X_axis1_operator VARCHAR2,
170 X_standard_axis2_id NUMBER,
171 X_axis2_operator VARCHAR2,
172 X_constant NUMBER,
173 X_variance_flag VARCHAR2,
174 X_sign_flag VARCHAR2,
175 X_description VARCHAR2
176 ) IS
177 BEGIN
178
179 UPDATE RG_REPORT_STANDARD_AXES_B
180 SET
181 application_id = X_application_id,
182 standard_axis_id = X_standard_axis_id,
183 last_update_date = X_last_update_date,
184 last_updated_by = X_last_updated_by,
185 last_update_login = X_last_update_login,
186 class = X_class,
187 display_in_standard_list_flag = X_display_in_std_list_flag,
188 precedence_level = X_precedence_level,
189 database_column = X_database_column,
190 simple_where_name = X_simple_where_name,
191 period_query = X_period_query,
192 standard_axis1_id = X_standard_axis1_id,
193 axis1_operator = X_axis1_operator,
194 standard_axis2_id = X_standard_axis2_id ,
195 axis2_operator = X_axis2_operator,
196 constant = X_constant,
197 variance_flag = X_variance_flag,
198 sign_flag = X_sign_flag
199 WHERE standard_axis_id = X_standard_axis_id;
200
201 IF (SQL%NOTFOUND) THEN
202 RAISE NO_DATA_FOUND;
203 END IF;
204
205 -- update non-translatable columns
206
207 UPDATE RG_REPORT_STANDARD_AXES_TL
208 SET
209 STANDARD_AXIS_ID = X_standard_axis_id,
210 LAST_UPDATE_DATE = X_last_update_date,
211 LAST_UPDATED_BY = X_last_updated_by,
212 LAST_UPDATE_LOGIN = X_last_update_login
213 WHERE standard_axis_id = X_standard_axis_id;
214
215 IF (SQL%NOTFOUND) THEN
216 RAISE NO_DATA_FOUND;
217 END IF;
218
219 -- update translatable columns
220
221 UPDATE RG_REPORT_STANDARD_AXES_TL
222 SET DESCRIPTION = X_description,
223 STANDARD_AXIS_NAME = X_standard_axis_name,
224 SOURCE_LANG = userenv('LANG')
225 WHERE standard_axis_id = X_standard_axis_id
226 AND userenv('LANG') in (LANGUAGE, SOURCE_LANG);
227
228 IF (SQL%NOTFOUND) THEN
229 RAISE NO_DATA_FOUND;
230 END IF;
231
232 END update_row;
233
234 --
235 -- Name
236 -- Load_Row
237 -- Purpose
238 -- Load a row in RG_REPORT_STANDARD_AXES for NLS support
239 --
240 PROCEDURE Load_Row ( X_Application_Id NUMBER,
241 X_Standard_Axis_Id NUMBER,
242 X_Class VARCHAR2,
243 X_Display_In_Std_List_Flag VARCHAR2,
244 X_Precedence_Level NUMBER,
245 X_Database_Column VARCHAR2,
246 X_Simple_Where_Name VARCHAR2,
247 X_Period_Query VARCHAR2,
248 X_Standard_Axis1_Id NUMBER,
249 X_Axis1_Operator VARCHAR2,
250 X_Standard_Axis2_Id NUMBER,
251 X_Axis2_Operator VARCHAR2,
252 X_Constant NUMBER,
253 X_Variance_Flag VARCHAR2,
254 X_Sign_Flag VARCHAR2,
255 X_Standard_Axis_Name VARCHAR2,
256 X_Description VARCHAR2,
257 X_Owner VARCHAR2,
258 X_Force_Edits VARCHAR2 ) IS
259
260 user_id number := 0;
261 v_creation_date date;
262 v_rowid rowid := null;
263
264 BEGIN
265 /* Validate that primary key is not null */
266 IF (X_Standard_Axis_Id IS NULL ) THEN
267 fnd_message.set_name('SQLGL', 'GL_LOAD_ROW_NO_DATA');
268 app_exception.raise_exception;
269 END IF;
270
271 /* Set user id for seeded data */
272 IF (X_Owner = 'SEED') THEN
273 user_id := 1;
274 END IF;
275
276 BEGIN
277
278 /* Retrieve creation date from existing rows */
279 SELECT creation_date, rowid
280 INTO v_creation_date, v_rowid
281 FROM RG_REPORT_STANDARD_AXES_B
282 WHERE Standard_Axis_Id = X_Standard_Axis_Id;
283
284 /*
285 * Update only if force_edits is 'Y' OR user_id is 'SEED'.
286 */
287 IF ( user_id = 1 or X_Force_Edits = 'Y' ) THEN
288 RG_REPORT_STANDARD_AXES_PKG.update_row(
289 X_rowid => v_rowid,
290 X_application_id => X_Application_Id,
291 X_standard_axis_id => X_Standard_Axis_Id,
292 X_standard_axis_name => X_Standard_Axis_Name,
293 X_last_update_date => sysdate,
294 X_last_updated_by => user_id,
295 X_last_update_login => 0,
296 X_class => X_Class,
297 X_display_in_std_list_flag => X_Display_In_Std_List_Flag,
298 X_precedence_level => X_Precedence_Level,
299 X_database_column => X_Database_Column,
300 X_simple_where_name => X_Simple_Where_Name,
301 X_period_query => X_Period_Query,
302 X_standard_axis1_id => X_Standard_Axis1_Id,
303 X_axis1_operator => X_Axis1_Operator,
304 X_standard_axis2_id => X_Standard_Axis2_Id,
305 X_axis2_operator => X_Axis2_Operator,
306 X_constant => X_Constant,
307 X_variance_flag => X_Variance_Flag,
308 X_sign_flag => X_Sign_Flag,
309 X_description => X_Description
310 );
311 END IF;
312
313 EXCEPTION
314 WHEN NO_DATA_FOUND THEN
315 /*
316 * If the row doesn't exist yet, call Insert_Row().
317 */
318 RG_REPORT_STANDARD_AXES_PKG.insert_row(
319 X_rowid => v_rowid,
320 X_application_id => X_Application_Id,
321 X_last_update_date => sysdate,
322 X_last_updated_by => user_id,
323 X_last_update_login => 0,
324 X_creation_date => sysdate,
325 X_created_by => user_id,
326 X_standard_axis_id => X_Standard_Axis_Id,
327 X_standard_axis_name => X_Standard_Axis_Name,
328 X_class => X_Class,
329 X_display_in_std_list_flag => X_Display_In_Std_List_Flag,
330 X_precedence_level => X_Precedence_Level,
331 X_database_column => X_Database_Column,
332 X_simple_where_name => X_Simple_Where_Name,
333 X_period_query => X_Period_Query,
334 X_standard_axis1_id => X_Standard_Axis1_Id,
335 X_axis1_operator => X_Axis1_Operator,
336 X_standard_axis2_id => X_Standard_Axis2_Id,
337 X_axis2_operator => X_Axis2_Operator,
338 X_constant => X_Constant,
339 X_variance_flag => X_Variance_Flag,
340 X_sign_flag => X_Sign_Flag,
341 X_description => X_Description
342 );
343 END;
344
345 END Load_Row;
346
347
348 --
349 -- Name
350 -- Translate_Row
351 -- Purpose
352 -- Translate a row in RG_REPORT_STANDARD_AXES for NLS support
353 --
354 PROCEDURE Translate_Row (
355 X_Standard_Axis_Name VARCHAR2,
356 X_Description VARCHAR2,
357 X_Standard_Axis_Id NUMBER,
358 X_Owner VARCHAR2,
359 X_Force_Edits VARCHAR2
360 ) IS
361
362 user_id number := 0;
363
364 BEGIN
365 IF (X_OWNER = 'SEED') THEN
366 user_id := 1;
367 END IF;
368
369 /*
370 * Update only if force_edits is 'Y' OR user_id is 'SEED'.
371 */
372 IF ( user_id = 1 or X_Force_Edits = 'Y' ) THEN
373 UPDATE RG_REPORT_STANDARD_AXES_TL
374 SET
375 standard_axis_name = X_Standard_Axis_Name,
376 description = X_Description,
377 source_lang = userenv('LANG'),
378 last_update_date = sysdate,
379 last_updated_by = user_id,
380 last_update_login = 0
381 WHERE
382 standard_axis_id = X_Standard_Axis_Id
383 AND
384 userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
385 /*If base language is not set to the language being uploaded, then do nothing*/
386 IF (SQL%NOTFOUND) THEN
387 NULL;
388 END IF;
389 END IF;
390 END Translate_Row;
391
392 PROCEDURE ADD_LANGUAGE
393 is
394 begin
395
396 UPDATE RG_REPORT_STANDARD_AXES_TL T
397 set ( standard_axis_name,
398 description)
399 = ( select
400 B.standard_axis_name,
401 B.description
402 from rg_report_standard_axes_tl B
403 where B.standard_axis_id = T.standard_axis_id
404 and B.language = T.source_lang)
405 where ( T.standard_axis_id,
406 T.language ) in
407 ( select
408 SUBT.standard_axis_id ,
409 SUBT.language
410 from rg_report_standard_axes_tl SUBB,
411 rg_report_standard_axes_tl SUBT
412 where SUBB.standard_axis_id = SUBT.standard_axis_id
413 and SUBB.language = SUBT.source_lang
414 and ( SUBB.standard_axis_name <> SUBT.standard_axis_name
415 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
416 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
417 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null))
418 );
419
420
421 INSERT INTO RG_REPORT_STANDARD_AXES_TL
422 (
423 STANDARD_AXIS_ID,
424 LANGUAGE,
425 SOURCE_LANG,
426 STANDARD_AXIS_NAME,
427 LAST_UPDATE_DATE,
428 LAST_UPDATED_BY,
429 LAST_UPDATE_LOGIN,
430 CREATION_DATE,
431 CREATED_BY,
432 DESCRIPTION
433 )
434 SELECT
435 B.standard_axis_id,
436 L.language_code,
437 B.source_lang,
438 B.standard_axis_name,
439 B.last_update_date,
440 B.last_updated_by,
441 B.last_update_login,
442 B.creation_date,
443 B.created_by,
444 B.description
445 FROM rg_report_standard_axes_tl B, FND_LANGUAGES L
446 WHERE L.installed_flag IN ('I', 'B')
447 AND B.language = USERENV('LANG')
448 AND NOT EXISTS
449 ( SELECT NULL
450 FROM RG_REPORT_STANDARD_AXES_TL R
451 WHERE R.standard_axis_id = B.standard_axis_id
452 AND R.language = L.language_code );
453
454 end ADD_LANGUAGE;
455
456 END RG_REPORT_STANDARD_AXES_PKG;