[Home] [Help]
PACKAGE BODY: APPS.RG_REPORT_AXES_PKG
Source
1 PACKAGE BODY RG_REPORT_AXES_PKG AS
2 /* $Header: rgiraxeb.pls 120.9 2006/03/13 19:51:59 ticheng ship $ */
3 -- Name
4 -- rg_report_axes_pkg
5 -- Purpose
6 -- to include all sever side procedures and packages for table
7 -- rg_report_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_axes%ROWTYPE) IS
22 BEGIN
23 select * INTO recinfo
24 from rg_report_axes
25 where axis_set_id = recinfo.axis_set_id
26 and axis_seq = recinfo.axis_seq;
27 END select_row;
28
29 PROCEDURE select_columns(X_axis_set_id NUMBER,
30 X_axis_seq NUMBER,
31 X_name IN OUT NOCOPY VARCHAR2) IS
32 recinfo rg_report_axes%ROWTYPE;
33 BEGIN
34 recinfo.axis_set_id := X_axis_set_id;
35 recinfo.axis_seq := X_axis_seq;
36 select_row(recinfo);
37 X_name := recinfo.axis_name;
38 END select_columns;
39
40 FUNCTION check_unique(X_rowid VARCHAR2,
41 X_axis_seq NUMBER,
42 X_axis_set_id NUMBER,
43 X_axis_set_type VARCHAR2) RETURN BOOLEAN IS
44 dummy NUMBER;
45 BEGIN
46 select 1 into dummy from dual
47 where not exists
48 (select 1 from rg_report_axes
49 where axis_seq = x_axis_seq
50 and axis_set_id = x_axis_set_id
51 and ((x_rowid IS NULL) OR (rowid <> x_rowid)));
52 RETURN (TRUE);
53
54 EXCEPTION
55 WHEN NO_DATA_FOUND THEN
56 RETURN(FALSE);
57 END check_unique;
58
59 FUNCTION construct_format(X_display_format VARCHAR2,
60 X_format_before_text VARCHAR2,
61 X_format_after_text VARCHAR2,
62 X_display_precision NUMBER,
63 X_format_mask_width NUMBER,
64 X_radix VARCHAR2,
65 X_thsnd_sprtr VARCHAR2,
66 X_po_thsnd_sprtr VARCHAR2) RETURN VARCHAR2 IS
67 display_fmt VARCHAR(30);
68 tmp_display_fmt VARCHAR(30);
69 nines_bf_radix NUMBER;
70 BEGIN
71 IF (X_display_format IS NULL or X_display_format = '') THEN
72 display_fmt := '';
73 ELSE
74 IF (X_po_thsnd_sprtr = 'N') THEN
75 IF (X_display_precision = 0) THEN
76 display_fmt := LPAD('9', X_format_mask_width, '9');
77 ELSE
78 display_fmt := LPAD(RPAD(X_radix, X_display_precision + 1, '9'),
79 X_format_mask_width, '9');
80 END IF;
81 ELSE
82 IF (X_display_precision = 0) THEN
83 IF (X_format_mask_width <= 3) THEN
84 display_fmt := LPAD('9', X_format_mask_width, '9');
85 ELSE
86 display_fmt := LPAD(LPAD(X_thsnd_sprtr || '999',
87 TRUNC(X_format_mask_width/4)*4,
88 X_thsnd_sprtr || '999'),
89 X_format_mask_width, '9');
90 END IF;
91 ELSE
92 nines_bf_radix := X_format_mask_width - X_display_precision - 1;
93 IF (nines_bf_radix <= 3) THEN
94 display_fmt := RPAD(LPAD('9', nines_bf_radix, '9') ||
95 X_radix,
96 X_format_mask_width, '9');
97 ELSE
98 display_fmt := RPAD(LPAD(LPAD(X_thsnd_sprtr || '999' || X_radix,
99 TRUNC(nines_bf_radix/4)*4 + 1,
100 X_thsnd_sprtr || '999'),
101 X_format_mask_width - X_display_precision, '9'),
102 X_format_mask_width,'9');
103 END IF;
104 END IF;
105 END IF;
106 tmp_display_fmt := LPAD(display_fmt, NVL(LENGTH(X_display_format),0) -
107 NVL(LENGTH(X_format_before_text),0) -
108 NVL(LENGTH(X_format_after_text),0),
109 ' ');
110 display_fmt := X_format_before_text || tmp_display_fmt || X_format_after_text;
111 END IF;
112 RETURN (display_fmt);
113 END construct_format;
114
115 PROCEDURE create_ruler(X_column_set_id NUMBER,
116 X_radix VARCHAR2,
117 X_thsnd_sprtr VARCHAR2,
118 X_po_thsnd_sprtr VARCHAR2,
119 X_ruler IN OUT NOCOPY VARCHAR2) IS
120 BEGIN
121 FOR axes_rec IN ( select position,
122 display_format,
123 format_before_text,
124 format_after_text,
125 display_precision,
126 format_mask_width
127 from rg_report_axes
128 where axis_set_id = X_column_set_id
129 and position < 2000
130 and display_flag = 'Y'
131 order by position asc ) LOOP
132 IF (X_ruler IS NULL or X_ruler = '') THEN
133 IF (axes_rec.position <= 2) THEN
134 X_ruler := RPAD('.', 2000, '.');
135 ELSE
136 X_ruler := RPAD(LPAD('X', axes_rec.position - 2, 'X'), 2000, '.');
137 END IF;
138 END IF;
139 X_ruler := RPAD(substr(X_ruler, 1, axes_rec.position-1) ||
140 RTRIM(construct_format(axes_rec.display_format,
141 axes_rec.format_before_text,
142 axes_rec.format_after_text,
143 axes_rec.display_precision,
144 axes_rec.format_mask_width,
145 X_radix, X_thsnd_sprtr,
146 X_po_thsnd_sprtr),' '), 2000, '.');
147 END LOOP;
148 END create_ruler;
149
150 PROCEDURE default_heading(X_column_set_id NUMBER,
151 X_amount_type_line IN OUT NOCOPY VARCHAR2,
152 X_period_line IN OUT NOCOPY VARCHAR2,
153 X_dash_line IN OUT NOCOPY VARCHAR2) IS
154 dash VARCHAR2(30);
155 c_return VARCHAR2(1);
156 OfInterest VARCHAR2(10);
157 BEGIN
158 FOR axis_rec IN ( select standard_axis_name,
159 sat.standard_axis_id,
160 period_offset,
161 position,
162 display_format
163 from rg_report_axes col,
164 rg_report_standard_axes_tl sat
165 where col.axis_set_id = X_column_set_id
166 and col.standard_axis_id = sat.standard_axis_id(+)
167 and sat.language(+) = userenv('LANG')
168 and col.position < 2000
169 and col.display_flag = 'Y'
170 order by col.position asc ) LOOP
171 IF (X_amount_type_line IS NULL) THEN
172 IF (axis_rec.position <= 2) THEN
173 X_amount_type_line := ' ';
174 X_period_line := ' ';
175 X_dash_line := ' ';
176 ELSE
177 X_amount_type_line := LPAD(' ',axis_rec.position-2, ' ');
178 X_period_line := LPAD(' ',axis_rec.position-2, ' ');
179 X_dash_line := LPAD(' ',axis_rec.position-2, ' ');
180 END IF;
181 END IF;
182 X_amount_type_line := RPAD(substr(X_amount_type_line, 1, axis_rec.position-2),
183 axis_rec.position-1, ' ') ||
184 axis_rec.standard_axis_name;
185 IF (axis_rec.period_offset IS NOT NULL) THEN
186 IF ((axis_rec.standard_axis_id >= 100) AND
187 (axis_rec.standard_axis_id < 104)) THEN
188 OfInterest := 'DOI';
189 ELSE
190 OfInterest := 'POI';
191 END IF;
192 IF (axis_rec.period_offset > 0) THEN
193 X_period_line := RPAD(substr(X_period_line, 1,axis_rec.position-2),
194 axis_rec.position-1, ' ') ||
195 '&' || OfInterest || '+' || axis_rec.period_offset;
196 ELSE
197 X_period_line := RPAD(substr(X_period_line, 1,axis_rec.position-2),
198 axis_rec.position-1, ' ') ||
199 '&' || OfInterest || axis_rec.period_offset;
200 END IF;
201 END IF;
202 dash := substr('------------------------------',
203 1, nvl(length(axis_rec.display_format),30));
204 X_dash_line := RPAD(substr(X_dash_line, 1, axis_rec.position-2),
205 axis_rec.position-1, ' ') || dash;
206 END LOOP;
207
208 END default_heading;
209
210 -- *********************************************************************
211 -- The following procedures are necessary to handle the base view form.
212
213 PROCEDURE insert_row(X_rowid IN OUT NOCOPY VARCHAR2 ,
214 X_application_id NUMBER ,
215 X_axis_set_id NUMBER ,
216 X_axis_set_type VARCHAR2 ,
217 X_axis_seq NUMBER ,
218 X_last_update_date DATE ,
219 X_last_updated_by NUMBER ,
220 X_last_update_login NUMBER ,
221 X_creation_date DATE ,
222 X_created_by NUMBER ,
223 X_axis_type VARCHAR2 ,
224 X_axis_name VARCHAR2 ,
225 X_amount_id NUMBER ,
226 X_standard_axis_id NUMBER ,
227 X_width NUMBER ,
228 X_position NUMBER ,
229 X_structure_id NUMBER ,
230 X_unit_of_measure_id VARCHAR2 ,
231 X_parameter_num NUMBER ,
232 X_period_offset NUMBER ,
233 X_description VARCHAR2 ,
234 X_display_flag VARCHAR2 ,
235 X_before_axis_string VARCHAR2 ,
236 X_after_axis_string VARCHAR2 ,
237 X_number_characters_indented NUMBER ,
238 X_page_break_after_flag VARCHAR2 ,
239 X_page_break_before_flag VARCHAR2 ,
240 X_number_lines_skipped_before NUMBER ,
241 X_number_lines_skipped_after NUMBER ,
242 X_display_level NUMBER ,
243 X_display_zero_amount_flag VARCHAR2 ,
244 X_change_sign_flag VARCHAR2 ,
245 X_change_variance_sign_flag VARCHAR2 ,
246 X_display_units NUMBER ,
247 X_display_format VARCHAR2 ,
248 X_calculation_precedence_flag VARCHAR2 ,
249 X_percentage_divisor_seq NUMBER ,
250 X_transaction_flag VARCHAR2 ,
251 X_format_before_text VARCHAR2 ,
252 X_format_after_text VARCHAR2 ,
253 X_format_mask_width NUMBER ,
254 X_display_precision NUMBER ,
255 X_segment_override_value VARCHAR2 ,
256 X_override_alc_ledger_currency VARCHAR2 ,
257 X_context VARCHAR2 ,
258 X_attribute1 VARCHAR2 ,
259 X_attribute2 VARCHAR2 ,
260 X_attribute3 VARCHAR2 ,
261 X_attribute4 VARCHAR2 ,
262 X_attribute5 VARCHAR2 ,
263 X_attribute6 VARCHAR2 ,
264 X_attribute7 VARCHAR2 ,
265 X_attribute8 VARCHAR2 ,
266 X_attribute9 VARCHAR2 ,
267 X_attribute10 VARCHAR2 ,
268 X_attribute11 VARCHAR2 ,
269 X_attribute12 VARCHAR2 ,
270 X_attribute13 VARCHAR2 ,
271 X_attribute14 VARCHAR2 ,
272 X_attribute15 VARCHAR2 ,
273 X_element_id NUMBER
274 ) IS
275 CURSOR C IS SELECT rowid FROM rg_report_axes
276 WHERE axis_set_id = X_axis_set_id
277 AND axis_seq = X_axis_seq;
278 BEGIN
279 IF (NOT check_unique(X_rowid,
280 X_axis_seq,
281 X_axis_set_id,
282 X_axis_set_type)) THEN
283 FND_MESSAGE.set_name('RG','RG_FORMS_DUP_OBJECT_SEQUENCES');
284 IF (x_axis_set_type = 'C') THEN
285 FND_MESSAGE.set_token('OBJECT','RG_COLUMN_SET',TRUE);
286 ELSE
287 FND_MESSAGE.set_token('OBJECT','RG_ROW_SET',TRUE);
288 END IF;
289 APP_EXCEPTION.raise_exception;
290 END IF;
291
292 INSERT INTO rg_report_axes
293 (application_id ,
294 axis_set_id ,
295 axis_seq ,
296 last_update_date ,
297 last_updated_by ,
298 last_update_login ,
299 creation_date ,
300 created_by ,
301 axis_type ,
302 axis_name ,
303 amount_id ,
307 structure_id ,
304 standard_axis_id ,
305 width ,
306 position ,
308 unit_of_measure_id ,
309 parameter_num ,
310 period_offset ,
311 description ,
312 display_flag ,
313 before_axis_string ,
314 after_axis_string ,
315 number_characters_indented ,
316 page_break_after_flag ,
317 page_break_before_flag ,
318 number_lines_skipped_before ,
319 number_lines_skipped_after ,
320 display_level ,
321 display_zero_amount_flag ,
322 change_sign_flag ,
323 change_variance_sign_flag ,
324 display_units ,
325 display_format ,
326 calculation_precedence_flag ,
327 percentage_divisor_seq ,
328 transaction_flag ,
329 format_before_text ,
330 format_after_text ,
331 format_mask_width ,
332 display_precision ,
333 segment_override_value ,
334 override_alc_ledger_currency ,
335 context ,
336 attribute1 ,
337 attribute2 ,
338 attribute3 ,
339 attribute4 ,
340 attribute5 ,
341 attribute6 ,
342 attribute7 ,
343 attribute8 ,
344 attribute9 ,
345 attribute10 ,
346 attribute11 ,
347 attribute12 ,
348 attribute13 ,
349 attribute14 ,
350 attribute15 ,
351 element_id )
352 VALUES
353 (X_application_id ,
354 X_axis_set_id ,
355 X_axis_seq ,
356 X_last_update_date ,
357 X_last_updated_by ,
358 X_last_update_login ,
359 X_creation_date ,
360 X_created_by ,
361 X_axis_type ,
362 X_axis_name ,
363 X_amount_id ,
364 X_standard_axis_id ,
365 X_width ,
366 X_position ,
367 X_structure_id ,
368 X_unit_of_measure_id ,
369 X_parameter_num ,
370 X_period_offset ,
371 X_description ,
372 X_display_flag ,
373 X_before_axis_string ,
374 X_after_axis_string ,
375 X_number_characters_indented ,
376 X_page_break_after_flag ,
377 X_page_break_before_flag ,
378 X_number_lines_skipped_before ,
379 X_number_lines_skipped_after ,
380 X_display_level ,
381 X_display_zero_amount_flag ,
382 X_change_sign_flag ,
383 X_change_variance_sign_flag ,
384 X_display_units ,
385 X_display_format ,
386 X_calculation_precedence_flag ,
387 X_percentage_divisor_seq ,
388 X_transaction_flag ,
389 X_format_before_text ,
390 X_format_after_text ,
391 X_format_mask_width ,
392 X_display_precision ,
393 X_segment_override_value ,
394 X_override_alc_ledger_currency,
395 X_context ,
396 X_attribute1 ,
397 X_attribute2 ,
398 X_attribute3 ,
399 X_attribute4 ,
400 X_attribute5 ,
401 X_attribute6 ,
402 X_attribute7 ,
403 X_attribute8 ,
404 X_attribute9 ,
405 X_attribute10 ,
406 X_attribute11 ,
407 X_attribute12 ,
408 X_attribute13 ,
409 X_attribute14 ,
410 X_attribute15 ,
411 X_element_id );
412
413 OPEN C;
414 FETCH C INTO X_rowid;
415 IF (C%NOTFOUND) THEN
416 CLOSE C;
417 RAISE NO_DATA_FOUND;
418 END IF;
419 CLOSE C;
420 END insert_row;
421
422 PROCEDURE lock_row(X_rowid IN OUT NOCOPY VARCHAR2 ,
423 X_application_id NUMBER ,
424 X_axis_set_id NUMBER ,
425 X_axis_seq NUMBER ,
426 X_axis_type VARCHAR2 ,
427 X_axis_name VARCHAR2 ,
428 X_amount_id NUMBER ,
429 X_standard_axis_id NUMBER ,
430 X_width NUMBER ,
431 X_position NUMBER ,
432 X_structure_id NUMBER ,
433 X_unit_of_measure_id VARCHAR2 ,
434 X_parameter_num NUMBER ,
438 X_before_axis_string VARCHAR2 ,
435 X_period_offset NUMBER ,
436 X_description VARCHAR2 ,
437 X_display_flag VARCHAR2 ,
439 X_after_axis_string VARCHAR2 ,
440 X_number_characters_indented NUMBER ,
441 X_page_break_after_flag VARCHAR2 ,
442 X_page_break_before_flag VARCHAR2 ,
443 X_number_lines_skipped_before NUMBER ,
444 X_number_lines_skipped_after NUMBER ,
445 X_display_level NUMBER ,
446 X_display_zero_amount_flag VARCHAR2 ,
447 X_change_sign_flag VARCHAR2 ,
448 X_change_variance_sign_flag VARCHAR2 ,
449 X_display_units NUMBER ,
450 X_display_format VARCHAR2 ,
451 X_calculation_precedence_flag VARCHAR2 ,
452 X_percentage_divisor_seq NUMBER ,
453 X_transaction_flag VARCHAR2 ,
454 X_format_before_text VARCHAR2 ,
455 X_format_after_text VARCHAR2 ,
456 X_format_mask_width NUMBER ,
457 X_display_precision NUMBER ,
458 X_segment_override_value VARCHAR2 ,
459 X_override_alc_ledger_currency VARCHAR2 ,
460 X_context VARCHAR2 ,
461 X_attribute1 VARCHAR2 ,
462 X_attribute2 VARCHAR2 ,
463 X_attribute3 VARCHAR2 ,
464 X_attribute4 VARCHAR2 ,
465 X_attribute5 VARCHAR2 ,
466 X_attribute6 VARCHAR2 ,
467 X_attribute7 VARCHAR2 ,
468 X_attribute8 VARCHAR2 ,
469 X_attribute9 VARCHAR2 ,
470 X_attribute10 VARCHAR2 ,
471 X_attribute11 VARCHAR2 ,
472 X_attribute12 VARCHAR2 ,
473 X_attribute13 VARCHAR2 ,
474 X_attribute14 VARCHAR2 ,
475 X_attribute15 VARCHAR2 ,
476 X_element_id NUMBER
477 ) IS
478 CURSOR C IS
479 SELECT *
480 FROM rg_report_axes
481 WHERE rowid = X_rowid
482 FOR UPDATE OF axis_seq NOWAIT;
483 Recinfo C%ROWTYPE;
484 BEGIN
485 OPEN C;
486 FETCH C INTO Recinfo;
487 IF (C%NOTFOUND) THEN
488 CLOSE C;
489 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
490 APP_EXCEPTION.RAISE_EXCEPTION;
491 END IF;
492 CLOSE C;
493
494 IF (
495 ( (Recinfo.application_id = X_application_id)
496 OR ( (Recinfo.application_id IS NULL)
497 AND (X_application_id IS NULL)))
498 AND ( (Recinfo.axis_set_id = X_axis_set_id)
499 OR ( (Recinfo.axis_set_id IS NULL)
500 AND (X_axis_set_id IS NULL)))
501 AND ( (Recinfo.axis_seq = X_axis_seq)
502 OR ( (Recinfo.axis_seq IS NULL)
503 AND (X_axis_seq IS NULL)))
504 AND ( (Recinfo.axis_type = X_axis_type)
505 OR ( (Recinfo.axis_type IS NULL)
506 AND (X_axis_type IS NULL)))
507 AND ( (Recinfo.axis_name = X_axis_name)
508 OR ( (Recinfo.axis_name IS NULL)
509 AND (X_axis_name IS NULL)))
510 AND ( (Recinfo.amount_id = X_amount_id)
511 OR ( (Recinfo.amount_id IS NULL)
512 AND (X_amount_id IS NULL)))
513 AND ( (Recinfo.standard_axis_id = X_standard_axis_id)
514 OR ( (Recinfo.standard_axis_id IS NULL)
515 AND (X_standard_axis_id IS NULL)))
516 AND ( (Recinfo.width = X_width)
517 OR ( (Recinfo.width IS NULL)
518 AND (X_width IS NULL)))
519 AND ( (Recinfo.position = X_position)
520 OR ( (Recinfo.position IS NULL)
521 AND (X_position IS NULL)))
522 AND ( (Recinfo.structure_id = X_structure_id)
523 OR ( (Recinfo.structure_id IS NULL)
524 AND (X_structure_id IS NULL)))
525 AND ( (Recinfo.unit_of_measure_id = X_unit_of_measure_id)
526 OR ( (Recinfo.unit_of_measure_id IS NULL)
527 AND (X_unit_of_measure_id IS NULL)))
528 AND ( (Recinfo.parameter_num = X_parameter_num)
529 OR ( (Recinfo.parameter_num IS NULL)
530 AND (X_parameter_num IS NULL)))
531 AND ( (Recinfo.period_offset = X_period_offset)
532 OR ( (Recinfo.period_offset IS NULL)
533 AND (X_period_offset IS NULL)))
534 AND ( (Recinfo.description = X_description)
535 OR ( (Recinfo.description IS NULL)
536 AND (X_description IS NULL)))
537 AND ( (Recinfo.display_flag = X_display_flag)
541 OR ( (Recinfo.before_axis_string IS NULL)
538 OR ( (Recinfo.display_flag IS NULL)
539 AND (X_display_flag IS NULL)))
540 AND ( (Recinfo.before_axis_string = X_before_axis_string)
542 AND (X_before_axis_string IS NULL)))
543 AND ( (Recinfo.after_axis_string = X_after_axis_string)
544 OR ( (Recinfo.after_axis_string IS NULL)
545 AND (X_after_axis_string IS NULL)))
546 AND ( (Recinfo.number_characters_indented = X_number_characters_indented)
547 OR ( (Recinfo.number_characters_indented IS NULL)
548 AND (X_number_characters_indented IS NULL)))
549 AND ( (Recinfo.page_break_after_flag = X_page_break_after_flag)
550 OR ( (Recinfo.page_break_after_flag IS NULL)
551 AND (X_page_break_after_flag IS NULL)))
552 AND ( (Recinfo.page_break_before_flag = X_page_break_before_flag)
553 OR ( (Recinfo.page_break_before_flag IS NULL)
554 AND (X_page_break_before_flag IS NULL)))
555 AND ( (Recinfo.number_lines_skipped_before = X_number_lines_skipped_before)
556 OR ( (Recinfo.number_lines_skipped_before IS NULL)
557 AND (X_number_lines_skipped_before IS NULL)))
558 AND ( (Recinfo.number_lines_skipped_after = X_number_lines_skipped_after)
559 OR ( (Recinfo.number_lines_skipped_after IS NULL)
560 AND (X_number_lines_skipped_after IS NULL)))
561 AND ( (Recinfo.display_level = X_display_level)
562 OR ( (Recinfo.display_level IS NULL)
563 AND (X_display_level IS NULL)))
564 AND ( (Recinfo.display_zero_amount_flag = X_display_zero_amount_flag)
565 OR ( (Recinfo.display_zero_amount_flag IS NULL)
566 AND (X_display_zero_amount_flag IS NULL)))
567 AND ( (Recinfo.change_sign_flag = X_change_sign_flag)
568 OR ( (Recinfo.change_sign_flag IS NULL)
569 AND (X_change_sign_flag IS NULL)))
570 AND ( (Recinfo.change_variance_sign_flag = X_change_variance_sign_flag)
571 OR ( (Recinfo.change_variance_sign_flag IS NULL)
572 AND (X_change_variance_sign_flag IS NULL)))
573 AND ( (Recinfo.display_units = X_display_units)
574 OR ( (Recinfo.display_units IS NULL)
575 AND (X_display_units IS NULL)))
576 AND ( (Recinfo.display_format = X_display_format)
577 OR ( (Recinfo.display_format IS NULL)
578 AND (X_display_format IS NULL)))
579 AND ( (Recinfo.calculation_precedence_flag = X_calculation_precedence_flag)
580 OR ( (Recinfo.calculation_precedence_flag IS NULL)
581 AND (X_calculation_precedence_flag IS NULL)))
582 AND ( (Recinfo.percentage_divisor_seq = X_percentage_divisor_seq)
583 OR ( (Recinfo.percentage_divisor_seq IS NULL)
584 AND (X_percentage_divisor_seq IS NULL)))
585 AND ( (Recinfo.transaction_flag = X_transaction_flag)
586 OR ( (Recinfo.transaction_flag IS NULL)
587 AND (X_transaction_flag IS NULL)))
588 AND ( (Recinfo.format_before_text = X_format_before_text)
589 OR ( (Recinfo.format_before_text IS NULL)
590 AND (X_format_before_text IS NULL)))
591 AND ( (Recinfo.format_after_text = X_format_after_text)
592 OR ( (Recinfo.format_after_text IS NULL)
593 AND (X_format_after_text IS NULL)))
594 AND ( (Recinfo.display_precision = X_display_precision)
595 OR ( (Recinfo.display_precision IS NULL)
596 AND (X_display_precision IS NULL)))
597 AND ( (Recinfo.segment_override_value = X_segment_override_value)
598 OR ( (Recinfo.segment_override_value IS NULL)
599 AND (X_segment_override_value IS NULL)))
600 AND ( (Recinfo.override_alc_ledger_currency = X_override_alc_ledger_currency)
601 OR ( (Recinfo.override_alc_ledger_currency IS NULL)
602 AND (X_override_alc_ledger_currency IS NULL)))
603 AND ( (Recinfo.context = X_context)
604 OR ( (Recinfo.context IS NULL)
605 AND (X_context IS NULL)))
606 AND ( (Recinfo.attribute1 = X_attribute1)
607 OR ( (Recinfo.attribute1 IS NULL)
608 AND (X_attribute1 IS NULL)))
609 AND ( (Recinfo.attribute2 = X_attribute2)
610 OR ( (Recinfo.attribute2 IS NULL)
611 AND (X_attribute2 IS NULL)))
612 AND ( (Recinfo.attribute3 = X_attribute3)
613 OR ( (Recinfo.attribute3 IS NULL)
614 AND (X_attribute3 IS NULL)))
615 AND ( (Recinfo.attribute4 = X_attribute4)
616 OR ( (Recinfo.attribute4 IS NULL)
617 AND (X_attribute4 IS NULL)))
618 AND ( (Recinfo.attribute5 = X_attribute5)
619 OR ( (Recinfo.attribute5 IS NULL)
620 AND (X_attribute5 IS NULL)))
621 AND ( (Recinfo.attribute6 = X_attribute6)
622 OR ( (Recinfo.attribute6 IS NULL)
623 AND (X_attribute6 IS NULL)))
624 AND ( (Recinfo.attribute7 = X_attribute7)
625 OR ( (Recinfo.attribute7 IS NULL)
626 AND (X_attribute7 IS NULL)))
627 AND ( (Recinfo.attribute8 = X_attribute8)
628 OR ( (Recinfo.attribute8 IS NULL)
629 AND (X_attribute8 IS NULL)))
630 AND ( (Recinfo.attribute9 = X_attribute9)
631 OR ( (Recinfo.attribute9 IS NULL)
632 AND (X_attribute9 IS NULL)))
633 AND ( (Recinfo.attribute10 = X_attribute10)
637 OR ( (Recinfo.attribute11 IS NULL)
634 OR ( (Recinfo.attribute10 IS NULL)
635 AND (X_attribute10 IS NULL)))
636 AND ( (Recinfo.attribute11 = X_attribute11)
638 AND (X_attribute11 IS NULL)))
639 AND ( (Recinfo.attribute12 = X_attribute12)
640 OR ( (Recinfo.attribute12 IS NULL)
641 AND (X_attribute12 IS NULL)))
642 AND ( (Recinfo.attribute13 = X_attribute13)
643 OR ( (Recinfo.attribute13 IS NULL)
644 AND (X_attribute13 IS NULL)))
645 AND ( (Recinfo.attribute14 = X_attribute14)
646 OR ( (Recinfo.attribute14 IS NULL)
647 AND (X_attribute14 IS NULL)))
648 AND ( (Recinfo.attribute15 = X_attribute15)
649 OR ( (Recinfo.attribute15 IS NULL)
650 AND (X_attribute15 IS NULL)))
651 AND ( (Recinfo.element_id = X_element_id)
652 OR ( (Recinfo.element_id IS NULL)
653 AND (X_element_id IS NULL)))
654 ) THEN
655 RETURN;
656 ELSE
657 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
658 APP_EXCEPTION.RAISE_EXCEPTION;
659 END IF;
660 END lock_row;
661
662 PROCEDURE update_row(X_rowid IN OUT NOCOPY VARCHAR2 ,
663 X_application_id NUMBER ,
664 X_axis_set_id NUMBER ,
665 X_axis_seq NUMBER ,
666 X_last_update_date DATE ,
667 X_last_updated_by NUMBER ,
668 X_last_update_login NUMBER ,
669 X_axis_type VARCHAR2 ,
670 X_axis_name VARCHAR2 ,
671 X_amount_id NUMBER ,
672 X_standard_axis_id NUMBER ,
673 X_width NUMBER ,
674 X_position NUMBER ,
675 X_structure_id NUMBER ,
676 X_unit_of_measure_id VARCHAR2 ,
677 X_parameter_num NUMBER ,
678 X_period_offset NUMBER ,
679 X_description VARCHAR2 ,
680 X_display_flag VARCHAR2 ,
681 X_before_axis_string VARCHAR2 ,
682 X_after_axis_string VARCHAR2 ,
683 X_number_characters_indented NUMBER ,
684 X_page_break_after_flag VARCHAR2 ,
685 X_page_break_before_flag VARCHAR2 ,
686 X_number_lines_skipped_before NUMBER ,
687 X_number_lines_skipped_after NUMBER ,
688 X_display_level NUMBER ,
689 X_display_zero_amount_flag VARCHAR2 ,
690 X_change_sign_flag VARCHAR2 ,
691 X_change_variance_sign_flag VARCHAR2 ,
692 X_display_units NUMBER ,
693 X_display_format VARCHAR2 ,
694 X_calculation_precedence_flag VARCHAR2 ,
695 X_percentage_divisor_seq NUMBER ,
696 X_transaction_flag VARCHAR2 ,
697 X_format_before_text VARCHAR2 ,
698 X_format_after_text VARCHAR2 ,
699 X_format_mask_width NUMBER ,
700 X_display_precision NUMBER ,
701 X_segment_override_value VARCHAR2 ,
702 X_override_alc_ledger_currency VARCHAR2 ,
703 X_context VARCHAR2 ,
704 X_attribute1 VARCHAR2 ,
705 X_attribute2 VARCHAR2 ,
706 X_attribute3 VARCHAR2 ,
707 X_attribute4 VARCHAR2 ,
708 X_attribute5 VARCHAR2 ,
709 X_attribute6 VARCHAR2 ,
710 X_attribute7 VARCHAR2 ,
711 X_attribute8 VARCHAR2 ,
712 X_attribute9 VARCHAR2 ,
713 X_attribute10 VARCHAR2 ,
714 X_attribute11 VARCHAR2 ,
715 X_attribute12 VARCHAR2 ,
716 X_attribute13 VARCHAR2 ,
717 X_attribute14 VARCHAR2 ,
718 X_attribute15 VARCHAR2 ,
719 X_element_id NUMBER
720 ) IS
721 old_axis_seq NUMBER;
722 BEGIN
723 SELECT axis_seq INTO old_axis_seq
724 FROM rg_report_axes
725 WHERE rowid = X_rowid;
726
727 IF (old_axis_seq <> X_axis_seq) THEN
728 UPDATE rg_report_axis_contents
729 SET axis_seq = X_axis_seq
730 WHERE axis_set_id = X_axis_set_id
731 AND axis_seq = old_axis_seq;
735 WHERE axis_set_id = X_axis_set_id
732
733 UPDATE rg_report_calculations
734 SET axis_seq = X_axis_seq
736 AND axis_seq = old_axis_seq;
737
738 UPDATE rg_report_exception_flags
739 SET axis_seq = X_axis_seq
740 WHERE axis_set_id = X_axis_set_id
741 AND axis_seq = old_axis_seq;
742
743 UPDATE rg_report_exceptions
744 SET axis_seq = X_axis_seq
745 WHERE axis_set_id = X_axis_set_id
746 AND axis_seq = old_axis_seq;
747 END IF;
748
749 UPDATE rg_report_axes
750 SET application_id = X_application_id ,
751 axis_set_id = X_axis_set_id ,
752 axis_seq = X_axis_seq ,
753 last_update_date = X_last_update_date ,
754 last_updated_by = X_last_updated_by ,
755 last_update_login = X_last_update_login ,
756 axis_type = X_axis_type ,
757 axis_name = X_axis_name ,
758 amount_id = X_amount_id ,
759 standard_axis_id = X_standard_axis_id ,
760 width = X_width ,
761 position = X_position ,
762 structure_id = X_structure_id ,
763 unit_of_measure_id = X_unit_of_measure_id ,
764 parameter_num = X_parameter_num ,
765 period_offset = X_period_offset ,
766 description = X_description ,
767 display_flag = X_display_flag ,
768 before_axis_string = X_before_axis_string ,
769 after_axis_string = X_after_axis_string ,
770 number_characters_indented = X_number_characters_indented ,
771 page_break_after_flag = X_page_break_after_flag ,
772 page_break_before_flag = X_page_break_before_flag ,
773 number_lines_skipped_before = X_number_lines_skipped_before ,
774 number_lines_skipped_after = X_number_lines_skipped_after ,
775 display_level = X_display_level ,
776 display_zero_amount_flag = X_display_zero_amount_flag ,
777 change_sign_flag = X_change_sign_flag ,
778 change_variance_sign_flag = X_change_variance_sign_flag ,
779 display_units = X_display_units ,
780 display_format = X_display_format ,
781 calculation_precedence_flag = X_calculation_precedence_flag ,
782 percentage_divisor_seq = X_percentage_divisor_seq ,
783 transaction_flag = X_transaction_flag ,
784 format_before_text = X_format_before_text ,
785 format_after_text = X_format_after_text ,
786 format_mask_width = X_format_mask_width ,
787 display_precision = X_display_precision ,
788 segment_override_value = X_segment_override_value ,
789 override_alc_ledger_currency = X_override_alc_ledger_currency ,
790 context = X_context ,
791 attribute1 = X_attribute1 ,
792 attribute2 = X_attribute2 ,
793 attribute3 = X_attribute3 ,
794 attribute4 = X_attribute4 ,
795 attribute5 = X_attribute5 ,
796 attribute6 = X_attribute6 ,
797 attribute7 = X_attribute7 ,
798 attribute8 = X_attribute8 ,
799 attribute9 = X_attribute9 ,
800 attribute10 = X_attribute10 ,
801 attribute11 = X_attribute11 ,
802 attribute12 = X_attribute12 ,
803 attribute13 = X_attribute13 ,
804 attribute14 = X_attribute14 ,
805 attribute15 = X_attribute15 ,
806 element_id = X_element_id
807 WHERE rowid = X_rowid;
808
809 IF (SQL%NOTFOUND) THEN
810 RAISE NO_DATA_FOUND;
811 END IF;
812 END update_row;
813
814 PROCEDURE delete_row(X_rowid VARCHAR2) IS
815 X_axis_set_id NUMBER;
816 X_axis_seq NUMBER;
817 BEGIN
818 select axis_set_id, axis_seq
819 into X_axis_set_id, X_axis_seq
820 from rg_report_axes
821 where rowid = X_rowid;
822
823 rg_report_axis_contents_pkg.delete_rows(X_axis_set_id,
824 X_axis_seq);
825
826 rg_report_calculations_pkg.delete_rows(X_axis_set_id,
827 X_axis_seq);
828
829 rg_report_exception_flags_pkg.delete_rows(X_axis_set_id,
830 X_axis_seq);
831
832 DELETE FROM rg_report_axes
836 RAISE NO_DATA_FOUND;
833 WHERE rowid = X_rowid;
834
835 IF (SQL%NOTFOUND) THEN
837 END IF;
838
839 EXCEPTION
840 WHEN NO_DATA_FOUND THEN
841 RETURN;
842
843 END delete_row;
844
845 PROCEDURE delete_rows(X_axis_set_id NUMBER) IS
846 BEGIN
847 rg_report_axis_contents_pkg.delete_rows(X_axis_set_id,
848 -1);
849
850 rg_report_calculations_pkg.delete_rows(X_axis_set_id,
851 -1);
852
853 rg_report_exception_flags_pkg.delete_rows(X_axis_set_id,
854 -1);
855
856 delete from rg_report_axes
857 where axis_set_id = X_axis_set_id;
858
859 END delete_rows;
860
861
862 PROCEDURE Load_Row ( X_Application_Id NUMBER,
863 X_Axis_Set_Id NUMBER,
864 X_Axis_Seq NUMBER,
865 X_Axis_Type VARCHAR2,
866 X_Axis_Name VARCHAR2,
867 X_Amount_Id NUMBER,
868 X_Standard_Axis_Id NUMBER,
869 X_Width NUMBER,
870 X_Position NUMBER,
871 X_Unit_Of_Measure_Id VARCHAR2,
872 X_Parameter_Num NUMBER,
873 X_Period_Offset NUMBER,
874 X_Description VARCHAR2,
875 X_Display_Flag VARCHAR2,
876 X_Before_Axis_String VARCHAR2,
877 X_After_Axis_String VARCHAR2,
878 X_Number_Characters_Indented NUMBER,
879 X_Page_Break_After_Flag VARCHAR2,
880 X_Page_Break_Before_Flag VARCHAR2,
881 X_Number_Lines_Skipped_Before NUMBER,
882 X_Number_Lines_Skipped_After NUMBER,
883 X_Display_Level NUMBER,
884 X_Display_Zero_Amount_Flag VARCHAR2,
885 X_Change_Sign_Flag VARCHAR2,
886 X_Change_Variance_Sign_Flag VARCHAR2,
887 X_Display_Units NUMBER,
888 X_Display_Format VARCHAR2,
889 X_Calculation_Precedence_Flag VARCHAR2,
890 X_Percentage_Divisor_Seq NUMBER,
891 X_Format_Before_Text VARCHAR2,
892 X_Format_After_Text VARCHAR2,
893 X_Format_Mask_Width NUMBER,
894 X_Display_Precision NUMBER,
895 X_Segment_Override_Value VARCHAR2,
896 X_Override_Alc_Ledger_Currency VARCHAR2,
897 X_Context VARCHAR2,
898 X_Attribute1 VARCHAR2,
899 X_Attribute2 VARCHAR2,
900 X_Attribute3 VARCHAR2,
901 X_Attribute4 VARCHAR2,
902 X_Attribute5 VARCHAR2,
903 X_Attribute6 VARCHAR2,
904 X_Attribute7 VARCHAR2,
905 X_Attribute8 VARCHAR2,
906 X_Attribute9 VARCHAR2,
907 X_Attribute10 VARCHAR2,
908 X_Attribute11 VARCHAR2,
909 X_Attribute12 VARCHAR2,
910 X_Attribute13 VARCHAR2,
911 X_Attribute14 VARCHAR2,
912 X_Attribute15 VARCHAR2,
913 X_Owner VARCHAR2,
914 X_Force_Edits VARCHAR2 ) IS
915 user_id NUMBER := 0;
916 v_creation_date DATE;
917 v_last_updated_by NUMBER;
918 v_rowid ROWID := null;
919 BEGIN
920
921 /* Make sure primary key is not null */
922 IF ( X_Axis_Set_Id is null or X_Axis_Seq is null ) THEN
923 fnd_message.set_name('SQLGL', 'GL_LOAD_ROW_NO_DATA');
924 app_exception.raise_exception;
925 END IF;
926
927 /* Set user id for seeded data */
928 IF (X_OWNER = 'SEED') THEN
929 user_id := 1;
930 END IF;
931
932 BEGIN
933
934 /* Retrieve creation date from existing rows */
935 SELECT creation_date, last_updated_by, rowid
936 INTO v_creation_date, v_last_updated_by, v_rowid
937 FROM RG_REPORT_AXES
938 WHERE AXIS_SET_ID = X_axis_set_id
939 AND AXIS_SEQ = X_axis_seq;
940
941 /* Do no overwrite if it has been customized */
942 IF (v_last_updated_by <> 1) THEN
943 RETURN;
944 END IF;
945
946 /*
947 * Update only if force_edits is 'Y' or owner = 'SEED'
948 */
952 X_application_id => X_Application_Id,
949 IF ( user_id = 1 or X_Force_Edits = 'Y' ) then
950 RG_REPORT_AXES_PKG.update_row(
951 X_rowid => v_rowid,
953 X_axis_set_id => X_Axis_Set_Id,
954 X_axis_seq => X_Axis_Seq,
955 X_last_update_date => sysdate,
956 X_last_updated_by => user_id,
957 X_last_update_login => 0,
958 X_axis_type => X_Axis_Type,
959 X_axis_name => X_Axis_Name,
960 X_amount_id => X_Amount_Id,
961 X_standard_axis_id => X_Standard_Axis_Id,
962 X_width => X_Width,
963 X_position => X_Position,
964 X_structure_id => null,
965 X_unit_of_measure_id => X_Unit_Of_Measure_Id,
966 X_parameter_num => X_Parameter_Num,
967 X_period_offset => X_Period_Offset,
968 X_description => X_Description,
969 X_display_flag => X_Display_Flag,
970 X_before_axis_string => X_Before_Axis_String,
971 X_after_axis_string => X_After_Axis_String,
972 X_number_characters_indented => X_Number_Characters_Indented,
973 X_page_break_after_flag => X_Page_Break_After_Flag,
974 X_page_break_before_flag => X_Page_Break_Before_Flag,
975 X_number_lines_skipped_before => X_Number_Lines_Skipped_Before,
976 X_number_lines_skipped_after => X_Number_Lines_Skipped_After,
977 X_display_level => X_Display_Level,
978 X_display_zero_amount_flag => X_Display_Zero_Amount_Flag,
979 X_change_sign_flag => X_Change_Sign_Flag,
980 X_change_variance_sign_flag => X_Change_Variance_Sign_Flag,
981 X_display_units => X_Display_Units,
982 X_display_format => X_Display_Format,
983 X_calculation_precedence_flag => X_Calculation_Precedence_Flag,
984 X_percentage_divisor_seq => X_Percentage_Divisor_Seq,
985 X_transaction_flag => null,
986 X_format_before_text => X_Format_Before_Text,
987 X_format_after_text => X_Format_After_Text,
988 X_format_mask_width => X_Format_Mask_Width,
989 X_display_precision => X_Display_Precision,
990 X_segment_override_value => X_Segment_Override_Value,
991 X_override_alc_ledger_currency=> X_Override_Alc_Ledger_Currency,
992 X_context => X_Context,
993 X_attribute1 => X_Attribute1,
994 X_attribute2 => X_Attribute2,
995 X_attribute3 => X_Attribute3,
996 X_attribute4 => X_Attribute4,
997 X_attribute5 => X_Attribute5,
998 X_attribute6 => X_Attribute6,
999 X_attribute7 => X_Attribute7,
1000 X_attribute8 => X_Attribute8,
1001 X_attribute9 => X_Attribute9,
1002 X_attribute10 => X_Attribute10,
1003 X_attribute11 => X_Attribute11,
1004 X_attribute12 => X_Attribute12,
1005 X_attribute13 => X_Attribute13,
1006 X_attribute14 => X_Attribute14,
1007 X_attribute15 => X_Attribute15,
1008 X_element_id => null);
1009 END IF;
1010
1011 EXCEPTION
1012 WHEN NO_DATA_FOUND THEN
1013 /* If this is a column set, check it it is unique */
1014 IF ((check_unique(v_rowid, X_Axis_Seq, X_Axis_Set_Id, 'C')) AND
1015 (check_unique(v_rowid, X_Axis_Seq, X_Axis_Set_Id, 'R'))) THEN
1016 /*
1017 * If the row doesn't exist yet, call Insert_Row().
1018 */
1019 RG_REPORT_AXES_PKG.insert_row(
1020 X_rowid => v_rowid,
1021 X_application_id => X_Application_Id,
1022 X_axis_set_id => X_Axis_Set_Id,
1023 X_axis_set_type => 'C',
1024 X_axis_seq => X_Axis_Seq,
1025 X_last_update_date => sysdate,
1026 X_last_updated_by => user_id,
1027 X_last_update_login => 0,
1028 X_creation_date => sysdate,
1029 X_created_by => user_id,
1030 X_axis_type => X_Axis_Type,
1031 X_axis_name => X_Axis_Name,
1032 X_amount_id => X_Amount_Id,
1033 X_standard_axis_id => X_Standard_Axis_Id,
1034 X_width => X_Width,
1035 X_position => X_Position,
1036 X_structure_id => null,
1037 X_unit_of_measure_id => X_Unit_Of_Measure_Id,
1038 X_parameter_num => X_Parameter_Num,
1039 X_period_offset => X_Period_Offset,
1040 X_description => X_Description,
1041 X_display_flag => X_Display_Flag,
1042 X_before_axis_string => X_Before_Axis_String,
1043 X_after_axis_string => X_After_Axis_String,
1044 X_number_characters_indented => X_Number_Characters_Indented,
1045 X_page_break_after_flag => X_Page_Break_After_Flag,
1046 X_page_break_before_flag => X_Page_Break_Before_Flag,
1047 X_number_lines_skipped_before => X_Number_Lines_Skipped_Before,
1048 X_number_lines_skipped_after => X_Number_Lines_Skipped_After,
1049 X_display_level => X_Display_Level,
1050 X_display_zero_amount_flag => X_Display_Zero_Amount_Flag,
1051 X_change_sign_flag => X_Change_Sign_Flag,
1052 X_change_variance_sign_flag => X_Change_Variance_Sign_Flag,
1053 X_display_units => X_Display_Units,
1054 X_display_format => X_Display_Format,
1055 X_calculation_precedence_flag => X_Calculation_Precedence_Flag,
1056 X_percentage_divisor_seq => X_Percentage_Divisor_Seq,
1057 X_transaction_flag => null,
1058 X_format_before_text => X_Format_Before_Text,
1059 X_format_after_text => X_Format_After_Text,
1060 X_format_mask_width => X_Format_Mask_Width,
1061 X_display_precision => X_Display_Precision,
1062 X_segment_override_value => X_Segment_Override_Value,
1063 X_override_alc_ledger_currency => X_Override_Alc_Ledger_Currency,
1064 X_context => X_Context,
1065 X_attribute1 => X_Attribute1,
1066 X_attribute2 => X_Attribute2,
1067 X_attribute3 => X_Attribute3,
1068 X_attribute4 => X_Attribute4,
1069 X_attribute5 => X_Attribute5,
1070 X_attribute6 => X_Attribute6,
1071 X_attribute7 => X_Attribute7,
1072 X_attribute8 => X_Attribute8,
1073 X_attribute9 => X_Attribute9,
1074 X_attribute10 => X_Attribute10,
1075 X_attribute11 => X_Attribute11,
1076 X_attribute12 => X_Attribute12,
1077 X_attribute13 => X_Attribute13,
1078 X_attribute14 => X_Attribute14,
1079 X_attribute15 => X_Attribute15,
1080 X_element_id => null);
1081 END IF;
1082 END;
1083 END Load_Row;
1084
1085
1086 PROCEDURE Translate_Row(
1087 X_Axis_Name VARCHAR2,
1088 X_Description VARCHAR2,
1089 X_Axis_Set_Id NUMBER,
1090 X_Axis_Seq NUMBER,
1091 X_Owner VARCHAR2,
1092 X_Force_Edits VARCHAR2 ) IS
1093 user_id number := 0;
1094 BEGIN
1095 IF (X_OWNER = 'SEED') THEN
1096 user_id := 1;
1097 END IF;
1098
1099 /*
1100 * Update only if force_edits is 'Y' or owner = 'SEED'
1101 */
1102 IF ( user_id = 1 or X_Force_Edits = 'Y' ) THEN
1103 UPDATE RG_REPORT_AXES
1104 SET
1105 axis_name = X_Axis_Name,
1106 description = X_Description,
1107 last_update_date = sysdate,
1108 last_updated_by = user_id,
1109 last_Update_login = 0
1110 WHERE axis_set_id = X_Axis_Set_Id
1111 AND axis_seq = X_Axis_Seq
1112 AND userenv('LANG') =
1113 ( SELECT language_code
1114 FROM FND_LANGUAGES
1115 WHERE installed_flag = 'B' );
1116 END IF;
1117
1118 END Translate_Row;
1119
1120 END RG_REPORT_AXES_PKG;