[Home] [Help]
PACKAGE BODY: APPS.RG_XFER_COMPONENTS_PKG
Source
1 PACKAGE BODY RG_XFER_COMPONENTS_PKG as
2 /* $Header: rgixcmpb.pls 120.6 2006/03/06 22:08:02 vtreiger ship $ */
3
4
5 /*** Variables ***/
6
7 G_Error NUMBER;
8 G_Warning NUMBER;
9
10 G_SourceCOAId NUMBER;
11 G_TargetCOAId NUMBER;
12 G_LinkName VARCHAR2(100);
13 G_ApplId NUMBER;
14
15 /* Message Levels */
16 G_ML_Minimal NUMBER;
17 G_ML_Normal NUMBER;
18 G_ML_Full NUMBER;
19
20 G_AxisSet VARCHAR2(60);
21 G_RowOrder VARCHAR2(60);
22 G_ContentSet VARCHAR2(60);
23 G_DisplayGroup VARCHAR2(60);
24 G_DisplaySet VARCHAR2(60);
25 G_Report VARCHAR2(60);
26 G_ReportSet VARCHAR2(60);
27 G_AxisType VARCHAR2(60);
28
29 /* These tables contain the list of components copied for this particular
30 * run. The list is required so that we can distinguish between the
31 * components copied in this run, and the components that existed before
32 * the run. The lists are checked by the copy_<component> routines.
33 * For each list, there is a count (index). */
34 ColumnSetList RG_XFER_UTILS_PKG.ListType;
35 ColumnSetCount BINARY_INTEGER := 0;
36 ContentSetList RG_XFER_UTILS_PKG.ListType;
37 ContentSetCount BINARY_INTEGER := 0;
38 DisplayGroupList RG_XFER_UTILS_PKG.ListType;
39 DisplayGroupCount BINARY_INTEGER := 0;
40 DisplaySetList RG_XFER_UTILS_PKG.ListType;
41 DisplaySetCount BINARY_INTEGER := 0;
42 ReportList RG_XFER_UTILS_PKG.ListType;
43 ReportCount BINARY_INTEGER := 0;
44 ReportSetList RG_XFER_UTILS_PKG.ListType;
45 ReportSetCount BINARY_INTEGER := 0;
46 RowOrderList RG_XFER_UTILS_PKG.ListType;
47 RowOrderCount BINARY_INTEGER := 0;
48 RowSetList RG_XFER_UTILS_PKG.ListType;
49 RowSetCount BINARY_INTEGER := 0;
50
51
52 /* Strings to select from the source database and insert into the
53 * target database. */
54
55 AxisSetsString VARCHAR2(2000) :=
56 'INSERT INTO RG_REPORT_AXIS_SETS (' ||
57 ' APPLICATION_ID, AXIS_SET_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY' ||
58 ', LAST_UPDATE_LOGIN, CREATION_DATE, CREATED_BY, NAME, AXIS_SET_TYPE' ||
59 ', SECURITY_FLAG, DISPLAY_IN_LIST_FLAG, PERIOD_SET_NAME, DESCRIPTION' ||
60 ', COLUMN_SET_HEADER, ROW_SET_TITLE, SEGMENT_NAME, ID_FLEX_CODE' ||
61 ', STRUCTURE_ID, CONTEXT , ATTRIBUTE1 , ATTRIBUTE2 ' ||
62 ', ATTRIBUTE3 , ATTRIBUTE4 , ATTRIBUTE5 , ATTRIBUTE6 ' ||
63 ', ATTRIBUTE7 , ATTRIBUTE8 , ATTRIBUTE9 , ATTRIBUTE10' ||
64 ', ATTRIBUTE11, ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14' ||
65 ', ATTRIBUTE15, TAXONOMY_ID' ||
66 ') SELECT' ||
67 ' APPLICATION_ID, :id , SYSDATE , :user_id' ||
68 ', :login_id , SYSDATE , :user_id , NAME , AXIS_SET_TYPE' ||
69 ', ''N'', DISPLAY_IN_LIST_FLAG, PERIOD_SET_NAME, DESCRIPTION' ||
70 ', null , ROW_SET_TITLE, SEGMENT_NAME, ID_FLEX_CODE' ||
71 ', :coa_id , CONTEXT , ATTRIBUTE1 , ATTRIBUTE2 ' ||
72 ', ATTRIBUTE3 , ATTRIBUTE4 , ATTRIBUTE5 , ATTRIBUTE6 ' ||
73 ', ATTRIBUTE7 , ATTRIBUTE8 , ATTRIBUTE9 , ATTRIBUTE10' ||
74 ', ATTRIBUTE11, ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14' ||
75 ', ATTRIBUTE15, :tax_id ' ||
76 'FROM RG_REPORT_AXIS_SETS@';
77
78 ContentSetsString VARCHAR2(2000) :=
79 'INSERT INTO RG_REPORT_CONTENT_SETS (' ||
80 ' APPLICATION_ID , CONTENT_SET_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY' ||
81 ', LAST_UPDATE_LOGIN, CREATION_DATE , CREATED_BY , NAME ' ||
82 ', REPORT_RUN_TYPE , ID_FLEX_CODE , STRUCTURE_ID , DESCRIPTION ' ||
83 ', CONTEXT , ATTRIBUTE1 , ATTRIBUTE2 , ATTRIBUTE3 ' ||
84 ', ATTRIBUTE4 , ATTRIBUTE5 , ATTRIBUTE6 , ATTRIBUTE7 ' ||
85 ', ATTRIBUTE8 , ATTRIBUTE9 , ATTRIBUTE10 , ATTRIBUTE11 ' ||
86 ', ATTRIBUTE12 , ATTRIBUTE13 , ATTRIBUTE14 , ATTRIBUTE15 ' ||
87 ', SECURITY_FLAG ) SELECT ' ||
88 ' APPLICATION_ID , :id , SYSDATE , :user_id ' ||
89 ', :login_id , SYSDATE , :user_id , NAME ' ||
90 ', REPORT_RUN_TYPE , ID_FLEX_CODE , :coa_id , DESCRIPTION ' ||
91 ', CONTEXT , ATTRIBUTE1 , ATTRIBUTE2 , ATTRIBUTE3 ' ||
92 ', ATTRIBUTE4 , ATTRIBUTE5 , ATTRIBUTE6 , ATTRIBUTE7 ' ||
93 ', ATTRIBUTE8 , ATTRIBUTE9 , ATTRIBUTE10 , ATTRIBUTE11 ' ||
94 ', ATTRIBUTE12 , ATTRIBUTE13 , ATTRIBUTE14 , ATTRIBUTE15 ' ||
95 ', ''N'' FROM RG_REPORT_CONTENT_SETS@';
96
97 DisplayGroupsString VARCHAR2(2000) :=
98 'INSERT INTO RG_REPORT_DISPLAY_GROUPS (' ||
99 ' REPORT_DISPLAY_GROUP_ID, NAME , CREATION_DATE , CREATED_BY ' ||
100 ', LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, FROM_SEQUENCE' ||
101 ', TO_SEQUENCE, DESCRIPTION, ROW_SET_ID , COLUMN_SET_ID' ||
102 ', CONTEXT , ATTRIBUTE1 , ATTRIBUTE2 , ATTRIBUTE3 ' ||
103 ', ATTRIBUTE4 , ATTRIBUTE5 , ATTRIBUTE6 , ATTRIBUTE7 ' ||
104 ', ATTRIBUTE8 , ATTRIBUTE9 , ATTRIBUTE10, ATTRIBUTE11' ||
105 ', ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15' ||
106 ') SELECT' ||
107 ' :id , NAME , SYSDATE , :user_id ' ||
108 ', SYSDATE , :user_id , :login_id , FROM_SEQUENCE' ||
109 ', TO_SEQUENCE, DESCRIPTION, :row_set_id , :column_set_id' ||
110 ', CONTEXT , ATTRIBUTE1 , ATTRIBUTE2 , ATTRIBUTE3 ' ||
111 ', ATTRIBUTE4 , ATTRIBUTE5 , ATTRIBUTE6 , ATTRIBUTE7 ' ||
112 ', ATTRIBUTE8 , ATTRIBUTE9 , ATTRIBUTE10, ATTRIBUTE11 ' ||
113 ', ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15 ' ||
114 'FROM RG_REPORT_DISPLAY_GROUPS@';
115
116 DisplaysString VARCHAR2(2000) :=
117 'INSERT INTO RG_REPORT_DISPLAYS (' ||
118 ' REPORT_DISPLAY_ID, REPORT_DISPLAY_SET_ID, SEQUENCE, CREATION_DATE ' ||
119 ', CREATED_BY , LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN' ||
120 ', DISPLAY_FLAG, ROW_GROUP_ID, COLUMN_GROUP_ID, DESCRIPTION' ||
121 ', CONTEXT , ATTRIBUTE1 , ATTRIBUTE2 , ATTRIBUTE3 ' ||
122 ', ATTRIBUTE4 , ATTRIBUTE5 , ATTRIBUTE6 , ATTRIBUTE7 ' ||
123 ', ATTRIBUTE8 , ATTRIBUTE9 , ATTRIBUTE10 , ATTRIBUTE11' ||
124 ', ATTRIBUTE12 , ATTRIBUTE13 , ATTRIBUTE14 , ATTRIBUTE15' ||
125 ') SELECT' ||
126 ' rg_report_displays_s.nextval, :id, SEQUENCE, SYSDATE ' ||
127 ', :user_id , SYSDATE , :user_id , :login_id ' ||
128 ', DISPLAY_FLAG,:row_group_id,:column_group_id, DESCRIPTION ' ||
129 ', CONTEXT , ATTRIBUTE1 , ATTRIBUTE2 , ATTRIBUTE3 ' ||
130 ', ATTRIBUTE4 , ATTRIBUTE5 , ATTRIBUTE6 , ATTRIBUTE7 ' ||
131 ', ATTRIBUTE8 , ATTRIBUTE9 , ATTRIBUTE10 , ATTRIBUTE11 ' ||
132 ', ATTRIBUTE12 , ATTRIBUTE13 , ATTRIBUTE14 , ATTRIBUTE15 ' ||
133 'FROM RG_REPORT_DISPLAYS@';
134
135 DisplaySetsString VARCHAR2(2000) :=
136 'INSERT INTO RG_REPORT_DISPLAY_SETS (' ||
137 ' REPORT_DISPLAY_SET_ID, NAME , CREATION_DATE, CREATED_BY' ||
138 ', LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, ROW_SET_ID'||
139 ', COLUMN_SET_ID, DESCRIPTION, CONTEXT , ATTRIBUTE1 ' ||
140 ', ATTRIBUTE2 , ATTRIBUTE3 , ATTRIBUTE4 , ATTRIBUTE5 ' ||
141 ', ATTRIBUTE6 , ATTRIBUTE7 , ATTRIBUTE8 , ATTRIBUTE9 ' ||
142 ', ATTRIBUTE10 , ATTRIBUTE11, ATTRIBUTE12, ATTRIBUTE13' ||
143 ', ATTRIBUTE14 , ATTRIBUTE15' ||
144 ') SELECT ' ||
145 ' :id , NAME, SYSDATE, :user_id' ||
146 ', SYSDATE , :user_id , :login_id , :row_set_id' ||
147 ', :column_set_id, DESCRIPTION , CONTEXT , ATTRIBUTE1 ' ||
148 ', ATTRIBUTE2 , ATTRIBUTE3 , ATTRIBUTE4 , ATTRIBUTE5 ' ||
149 ', ATTRIBUTE6 , ATTRIBUTE7 , ATTRIBUTE8 , ATTRIBUTE9 ' ||
150 ', ATTRIBUTE10 , ATTRIBUTE11, ATTRIBUTE12, ATTRIBUTE13' ||
151 ', ATTRIBUTE14 , ATTRIBUTE15 ' ||
152 'FROM RG_REPORT_DISPLAY_SETS@';
153
154 ReportParametersString VARCHAR2(2000) :=
155 'INSERT INTO RG_REPORT_PARAMETERS (' ||
156 ' PARAMETER_SET_ID, LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN' ||
157 ', CREATION_DATE , CREATED_BY , PARAMETER_NUM , DATA_TYPE ' ||
158 ', PARAMETER_ID , ENTERED_CURRENCY, CURRENCY_TYPE , LEDGER_CURRENCY' ||
159 ', PERIOD_NUM , FISCAL_YEAR_OFFSET ' ||
160 ', CONTEXT , ATTRIBUTE1 , ATTRIBUTE2 , ATTRIBUTE3 ' ||
161 ', ATTRIBUTE4 , ATTRIBUTE5 , ATTRIBUTE6 , ATTRIBUTE7 ' ||
162 ', ATTRIBUTE8 , ATTRIBUTE9 , ATTRIBUTE10 , ATTRIBUTE11' ||
163 ', ATTRIBUTE12 , ATTRIBUTE13 , ATTRIBUTE14 , ATTRIBUTE15' ||
164 ') SELECT' ||
165 ' :id , SYSDATE , :user_id , :login_id ' ||
166 ', SYSDATE , :user_id , PARAMETER_NUM , DATA_TYPE ' ||
167 ', :parameter_id , ENTERED_CURRENCY, CURRENCY_TYPE , LEDGER_CURRENCY' ||
168 ', PERIOD_NUM , FISCAL_YEAR_OFFSET ' ||
169 ', CONTEXT , ATTRIBUTE1 , ATTRIBUTE2 , ATTRIBUTE3 ' ||
170 ', ATTRIBUTE4 , ATTRIBUTE5 , ATTRIBUTE6 , ATTRIBUTE7 ' ||
171 ', ATTRIBUTE8 , ATTRIBUTE9 , ATTRIBUTE10 , ATTRIBUTE11' ||
172 ', ATTRIBUTE12 , ATTRIBUTE13 , ATTRIBUTE14 , ATTRIBUTE15 '||
173 'FROM RG_REPORT_PARAMETERS@';
174
175 ReportRequestsString VARCHAR2(3000) :=
176 'INSERT INTO RG_REPORT_REQUESTS (' ||
177 ' APPLICATION_ID, REPORT_REQUEST_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY' ||
178 ', LAST_UPDATE_LOGIN, CREATION_DATE, CREATED_BY, REPORT_ID ' ||
179 ', SEQUENCE, FORM_SUBMISSION_FLAG, CONCURRENT_REQUEST_ID, REPORT_SET_ID' ||
180 ', CONTENT_SET_ID, ROW_ORDER_ID, EXCEPTIONS_FLAG, ROUNDING_OPTION ' ||
181 ', LEDGER_ID, ALC_LEDGER_CURRENCY, REPORT_DISPLAY_SET_ID, ID_FLEX_CODE ' ||
182 ', STRUCTURE_ID, SEGMENT_OVERRIDE, OVERRIDE_ALC_LEDGER_CURRENCY ' ||
183 ', PERIOD_NAME, UNIT_OF_MEASURE_ID, CONTEXT' ||
184 ', ATTRIBUTE1 , ATTRIBUTE2 , ATTRIBUTE3 , ATTRIBUTE4 ' ||
185 ', ATTRIBUTE5 , ATTRIBUTE6 , ATTRIBUTE7 , ATTRIBUTE8 ' ||
186 ', ATTRIBUTE9 , ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12' ||
187 ', ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15, RUNTIME_OPTION_CONTEXT' ||
188 ', ACCOUNTING_DATE, OUTPUT_OPTION' ||
189 ') SELECT' ||
190 ' APPLICATION_ID, rg_report_requests_s.nextval, SYSDATE , :user_id' ||
191 ', :login_id , SYSDATE , :user_id , :report_id' ||
192 ', SEQUENCE , FORM_SUBMISSION_FLAG, null, :id' ||
193 ', null , null , ''N'' , ''C''' ||
194 ', null , null , null , ID_FLEX_CODE ' ||
195 ', :coa_id , null , null ' ||
196 ', null , null , CONTEXT ' ||
197 ', ATTRIBUTE1 , ATTRIBUTE2 , ATTRIBUTE3 , ATTRIBUTE4 ' ||
198 ', ATTRIBUTE5 , ATTRIBUTE6 , ATTRIBUTE7 , ATTRIBUTE8 ' ||
199 ', ATTRIBUTE9 , ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12' ||
200 ', ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15, RUNTIME_OPTION_CONTEXT' ||
201 ', null, NVL(OUTPUT_OPTION, ''R'') ' ||
202 'FROM RG_REPORT_REQUESTS@';
203
204 ReportsString VARCHAR2(3000) :=
205 'INSERT INTO RG_REPORTS (' ||
206 ' APPLICATION_ID , REPORT_ID , LAST_UPDATE_DATE, LAST_UPDATED_BY ' ||
207 ', LAST_UPDATE_LOGIN, CREATION_DATE, CREATED_BY , NAME, SECURITY_FLAG ' ||
208 ', REPORT_TITLE, ROW_SET_ID, COLUMN_SET_ID, ROUNDING_OPTION ' ||
209 ', OUTPUT_OPTION , CONTENT_SET_ID , ROW_ORDER_ID ' ||
210 ', PARAMETER_SET_ID , UNIT_OF_MEASURE_ID, ID_FLEX_CODE , STRUCTURE_ID '||
211 ', SEGMENT_OVERRIDE , OVERRIDE_ALC_LEDGER_CURRENCY , PERIOD_SET_NAME ' ||
212 ', MINIMUM_DISPLAY_LEVEL, DESCRIPTION, CONTEXT , ATTRIBUTE1 ' ||
213 ', ATTRIBUTE2 , ATTRIBUTE3 , ATTRIBUTE4 , ATTRIBUTE5 ' ||
214 ', ATTRIBUTE6 , ATTRIBUTE7 , ATTRIBUTE8 , ATTRIBUTE9 ' ||
215 ', ATTRIBUTE10 , ATTRIBUTE11 , ATTRIBUTE12 , ATTRIBUTE13' ||
216 ', ATTRIBUTE14 , ATTRIBUTE15 , REPORT_DISPLAY_SET_ID ' ||
217 ') SELECT' ||
218 ' APPLICATION_ID , :id , SYSDATE , :user_id' ||
219 ', :login_id , SYSDATE , :user_id , NAME , ''N'' ' ||
220 ', REPORT_TITLE , :row_set_id , :column_set_id , ROUNDING_OPTION'||
221 ', NVL(OUTPUT_OPTION,''R''), :content_set_id, :row_order_id ' ||
222 ', :parameter_set_id, :currency_code , ID_FLEX_CODE , :coa_id ' ||
223 ', :segment_override, :override_alc_ledger_currency , PERIOD_SET_NAME ' ||
224 ', MINIMUM_DISPLAY_LEVEL, DESCRIPTION, CONTEXT , ATTRIBUTE1 ' ||
225 ', ATTRIBUTE2 , ATTRIBUTE3 , ATTRIBUTE4 , ATTRIBUTE5 ' ||
226 ', ATTRIBUTE6 , ATTRIBUTE7 , ATTRIBUTE8 , ATTRIBUTE9 ' ||
227 ', ATTRIBUTE10 , ATTRIBUTE11 , ATTRIBUTE12 , ATTRIBUTE13' ||
228 ', ATTRIBUTE14 , ATTRIBUTE15 , :display_set_id ' ||
229 'FROM RG_REPORTS@';
230
231 ReportSetsString VARCHAR2(2000) :=
232 'INSERT INTO RG_REPORT_SETS (' ||
233 ' APPLICATION_ID, REPORT_SET_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY' ||
234 ', LAST_UPDATE_LOGIN, CREATION_DATE, CREATED_BY, NAME, SECURITY_FLAG ' ||
235 ', ID_FLEX_CODE,PERIOD_TYPE, PERIOD_NAME, STRUCTURE_ID' ||
236 ', DESCRIPTION, CONTEXT , ATTRIBUTE1 , ATTRIBUTE2 ' ||
237 ', ATTRIBUTE3 , ATTRIBUTE4 , ATTRIBUTE5 , ATTRIBUTE6 ' ||
238 ', ATTRIBUTE7 , ATTRIBUTE8 , ATTRIBUTE9 , ATTRIBUTE10 ' ||
239 ', ATTRIBUTE11, ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14 ' ||
240 ', ATTRIBUTE15, UNIT_OF_MEASURE_ID' ||
241 ') SELECT' ||
242 ' APPLICATION_ID, :id , SYSDATE , :user_id ' ||
243 ', :login_id , SYSDATE , :user_id , NAME , ''N'' ' ||
244 ', ID_FLEX_CODE,PERIOD_TYPE, PERIOD_NAME, :coa_id ' ||
245 ', DESCRIPTION, CONTEXT , ATTRIBUTE1 , ATTRIBUTE2 ' ||
246 ', ATTRIBUTE3 , ATTRIBUTE4 , ATTRIBUTE5 , ATTRIBUTE6 ' ||
247 ', ATTRIBUTE7 , ATTRIBUTE8 , ATTRIBUTE9 , ATTRIBUTE10 ' ||
248 ', ATTRIBUTE11, ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14 ' ||
249 ', ATTRIBUTE15, UNIT_OF_MEASURE_ID ' ||
250 'FROM RG_REPORT_SETS@';
251
252 RowOrdersString VARCHAR2(2000) :=
253 'INSERT INTO RG_ROW_ORDERS (' ||
254 ' APPLICATION_ID , ROW_ORDER_ID , LAST_UPDATE_DATE, LAST_UPDATED_BY' ||
255 ', LAST_UPDATE_LOGIN, CREATION_DATE, CREATED_BY , NAME ' ||
256 ', SECURITY_FLAG , ROW_RANK_TYPE, ID_FLEX_CODE , STRUCTURE_ID' ||
257 ', DESCRIPTION , COLUMN_NUMBER, COLUMN_NAME , CONTEXT ' ||
258 ', ATTRIBUTE1 , ATTRIBUTE2 , ATTRIBUTE3 , ATTRIBUTE4 ' ||
259 ', ATTRIBUTE5 , ATTRIBUTE6 , ATTRIBUTE7 , ATTRIBUTE8 ' ||
260 ', ATTRIBUTE9 , ATTRIBUTE10 , ATTRIBUTE11 , ATTRIBUTE12 ' ||
261 ', ATTRIBUTE13 , ATTRIBUTE14 , ATTRIBUTE15 ' ||
262 ') SELECT ' ||
263 ' APPLICATION_ID , :id , SYSDATE , :user_id ' ||
264 ', :login_id , SYSDATE , :user_id , NAME ' ||
265 ', ''N'' , ROW_RANK_TYPE, ID_FLEX_CODE , :coa_id ' ||
266 ', DESCRIPTION , COLUMN_NUMBER, :column_name , CONTEXT ' ||
267 ', ATTRIBUTE1 , ATTRIBUTE2 , ATTRIBUTE3 , ATTRIBUTE4 ' ||
268 ', ATTRIBUTE5 , ATTRIBUTE6 , ATTRIBUTE7 , ATTRIBUTE8 ' ||
269 ', ATTRIBUTE9 , ATTRIBUTE10 , ATTRIBUTE11 , ATTRIBUTE12 ' ||
270 ', ATTRIBUTE13 , ATTRIBUTE14 , ATTRIBUTE15 ' ||
271 'FROM RG_ROW_ORDERS@';
272
273 RowSegmentSequencesString VARCHAR2(2000) :=
274 'INSERT INTO RG_ROW_SEGMENT_SEQUENCES (' ||
275 ' APPLICATION_ID , ROW_ORDER_ID , ROW_SEGMENT_SEQUENCE_ID' ||
276 ', LAST_UPDATE_DATE, LAST_UPDATED_BY , LAST_UPDATE_LOGIN' ||
277 ', CREATION_DATE , CREATED_BY , SEGMENT_SEQUENCE ' ||
278 ', SEG_ORDER_TYPE , SEG_DISPLAY_TYPE, STRUCTURE_ID ' ||
279 ', SEGMENT_NAME , SEGMENT_WIDTH , CONTEXT , ATTRIBUTE1 ' ||
280 ', ATTRIBUTE2 , ATTRIBUTE3 , ATTRIBUTE4 , ATTRIBUTE5 ' ||
281 ', ATTRIBUTE6 , ATTRIBUTE7 , ATTRIBUTE8 , ATTRIBUTE9 ' ||
282 ', ATTRIBUTE10 , ATTRIBUTE11 , ATTRIBUTE12, ATTRIBUTE13' ||
283 ', ATTRIBUTE14 , ATTRIBUTE15 , APPLICATION_COLUMN_NAME ' ||
284 ') SELECT' ||
285 ' APPLICATION_ID , :id , rg_row_segment_sequences_s.nextval' ||
286 ', sysdate , :user_id , :login_id' ||
287 ', sysdate , :user_id , SEGMENT_SEQUENCE' ||
288 ', SEG_ORDER_TYPE , SEG_DISPLAY_TYPE, :coa_id ' ||
289 ', SEGMENT_NAME , SEGMENT_WIDTH , CONTEXT , ATTRIBUTE1 ' ||
290 ', ATTRIBUTE2 , ATTRIBUTE3 , ATTRIBUTE4 , ATTRIBUTE5 ' ||
291 ', ATTRIBUTE6 , ATTRIBUTE7 , ATTRIBUTE8 , ATTRIBUTE9 ' ||
292 ', ATTRIBUTE10 , ATTRIBUTE11 , ATTRIBUTE12, ATTRIBUTE13' ||
293 ', ATTRIBUTE14 , ATTRIBUTE15 , APPLICATION_COLUMN_NAME ' ||
294 'FROM RG_ROW_SEGMENT_SEQUENCES@';
295
296
297 /*** Prototypes for local routines ***/
298
299 PROCEDURE copy_single_component(
300 ComponentType VARCHAR2,
304 FUNCTION copy_axis_set(
301 ComponentName VARCHAR2,
302 CheckExistence BOOLEAN DEFAULT TRUE);
303
305 ComponentType VARCHAR2,
306 ComponentName VARCHAR2,
307 CheckExistence BOOLEAN DEFAULT TRUE) RETURN NUMBER;
308
309 FUNCTION copy_content_set(
310 ComponentName VARCHAR2,
311 CheckExistence BOOLEAN DEFAULT TRUE) RETURN NUMBER;
312
313 FUNCTION copy_row_order(
314 ComponentName VARCHAR2,
315 CheckExistence BOOLEAN DEFAULT TRUE) RETURN NUMBER;
316
317 FUNCTION copy_display_set(
318 ComponentName VARCHAR2,
319 CheckExistence BOOLEAN DEFAULT TRUE) RETURN NUMBER;
320
321 FUNCTION copy_display_group(
322 ComponentName VARCHAR2,
323 CheckExistence BOOLEAN DEFAULT TRUE) RETURN NUMBER;
324
325 FUNCTION copy_report(
326 ComponentName VARCHAR2,
327 CheckExistence BOOLEAN DEFAULT TRUE) RETURN NUMBER;
328
329 FUNCTION copy_report_set(
330 ComponentName VARCHAR2,
331 CheckExistence BOOLEAN DEFAULT TRUE) RETURN NUMBER;
332
333 PROCEDURE copy_display_set_details(
334 SourceDisplaySetId NUMBER,
335 TargetDisplaySetId NUMBER);
336
337 PROCEDURE copy_report_set_details(
338 SourceReportSetId NUMBER,
339 TargetReportSetId NUMBER);
340
341 PROCEDURE transfer_taxonomy(
342 parent_tax_alias IN VARCHAR2,
343 parent_tax_id IN NUMBER,
344 parent_done_flag IN OUT NOCOPY NUMBER);
345
346 FUNCTION copy_report_details(ReportId NUMBER) RETURN NUMBER;
347
348
349
350 /* Name: init
351 * Desc: Initialize some variables that are used in this package.
352 *
353 * Notes: This procedure is called by RG_XFER_UTILS_PKG.init
354 *
355 * History:
356 * 10/17/95 S Rahman Created.
357 */
358 PROCEDURE init(
359 SourceCOAId NUMBER,
360 TargetCOAId NUMBER,
361 LinkName VARCHAR2,
362 ApplId NUMBER) IS
363 BEGIN
364 G_SourceCOAId := SourceCOAId;
365 G_TargetCOAId := TargetCOAId;
366 G_LinkName := LinkName;
367 G_ApplId := ApplId;
368
369 /* Iniitialize the PRIVATE package */
370 RG_XFER_COMP_PRIVATE_PKG.init(SourceCOAId, TargetCOAId, LinkName, ApplId);
371 END init;
372
373
374 /* Name: print_stats
375 * Desc: Print transfer statistics.
376 *
377 * History:
378 * 03/05/96 S Rahman Created.
379 */
380 PROCEDURE print_stats IS
381 TotalCount BINARY_INTEGER := 0;
382 BEGIN
383 TotalCount := ColumnSetCount + ContentSetCount + DisplayGroupCount +
384 DisplaySetCount + ReportCount + ReportSetCount +
385 RowOrderCount + RowSetCount;
386 RG_XFER_UTILS_PKG.display_message(
387 MsgName => 'RG_XFER_STATS_1',
388 Token1 => 'ROW_SET_COUNT',
389 Token1Val => TO_CHAR(RowSetCount),
390 Token1Xlate => FALSE,
391 Token2 => 'COLUMN_SET_COUNT',
392 Token2Val => TO_CHAR(ColumnSetCount),
393 Token2Xlate => FALSE,
394 Token3 => 'CONTENT_SET_COUNT',
395 Token3Val => TO_CHAR(ContentSetCount),
396 Token3Xlate => FALSE,
397 Token4 => 'ROW_ORDER_COUNT',
398 Token4Val => TO_CHAR(RowOrderCount),
399 Token4Xlate => FALSE
400 );
401 RG_XFER_UTILS_PKG.display_message(
402 MsgName => 'RG_XFER_STATS_2',
403 Token1 => 'DISPLAY_GROUP_COUNT',
404 Token1Val => TO_CHAR(DisplayGroupCount),
405 Token1Xlate => FALSE,
406 Token2 => 'DISPLAY_SET_COUNT',
407 Token2Val => TO_CHAR(DisplaySetCount),
408 Token2Xlate => FALSE,
409 Token3 => 'REPORT_COUNT',
410 Token3Val => TO_CHAR(ReportCount),
411 Token3Xlate => FALSE,
412 Token4 => 'REPORT_SET_COUNT',
413 Token4Val => TO_CHAR(ReportSetCount),
414 Token4Xlate => FALSE,
415 Token5 => 'TOTAL_COUNT',
416 Token5Val => TO_CHAR(TotalCount),
417 Token5Xlate => FALSE
418 );
419 END print_stats;
420
421
422 /* Name: copy_component
423 * Desc: Copies the specified component. If the component type is specified,
424 * then it copies that particulart component. If the component type
425 * is NULL, then it copies all FSG components.
426 *
427 * This routine is essentially a wrapper around copy_single_component.
428 * copy_single_component does all the work.
429 *
430 * History:
431 * 10/17/95 S Rahman Created.
432 */
433 PROCEDURE copy_component(
434 ComponentType VARCHAR2,
435 ComponentName VARCHAR2) IS
436 BEGIN
437 RG_XFER_UTILS_PKG.display_log(
438 MsgLevel => G_ML_Full,
439 MsgName => 'RG_XFER_L_ENTER_ROUTINE',
440 Token1 => 'ROUTINE',
441 Token1Val => 'copy_component',
442 Token2 => 'PARAM1',
443 Token2Val => ComponentType,
444 Token3 => 'PARAM2',
445 Token3Val => ComponentName);
446
447 IF (ComponentType = 'RG_ALL') THEN
448 /* Copy everything */
449 copy_single_component('RG_ROW_SET' , '');
450 copy_single_component('RG_COLUMN_SET' , '');
454 copy_single_component('RG_DISPLAY_SET' , '');
451 copy_single_component('RG_ROW_ORDER' , '');
452 copy_single_component('RG_CONTENT_SET' , '');
453 copy_single_component('RG_DISPLAY_GROUP', '');
455 copy_single_component('RG_REPORT' , '');
456 copy_single_component('RG_REPORT_SET' , '');
457 ELSE
458 /* Copy a particular component. */
459 copy_single_component(ComponentType, ComponentName);
460 END IF;
461
462 /* Print transfer statistics */
463 print_stats;
464
465 RG_XFER_UTILS_PKG.display_log(
466 MsgLevel => G_ML_Full,
467 MsgName => 'RG_XFER_L_EXIT_ROUTINE',
468 Token1 => 'ROUTINE',
469 Token1Val => 'copy_component');
470 END copy_component;
471
472
473 /* Name: copy_single_component
474 * Desc: Copies the specified component. It checks the component type
475 * and calls the appropriate routine to copy the component. If
476 * a component name is specified, then it copies that particular
477 * component. If component name is NULL, then it copies all
478 * components of that particular component type. If a wildcard
479 * character (%) is specified in the component name, then copy
480 * the components that match the specified pattern.
481 *
482 * Notes: ComponentType MUST have a valid value.
483 *
484 * History:
485 * 10/17/95 S Rahman Created.
486 */
487 PROCEDURE copy_single_component(
488 ComponentType VARCHAR2,
489 ComponentName VARCHAR2,
490 CheckExistence BOOLEAN DEFAULT TRUE) IS
491 Id NUMBER;
492 CursorId INTEGER;
493 ExecuteValue INTEGER;
494 TableName VARCHAR2(50) := NULL;
495 WhereClause VARCHAR2(500) := NULL;
496 CompName VARCHAR2(30);
497 SQLString VARCHAR2(700) := 'SELECT name FROM ';
498 ApplIdStr VARCHAR2(10) := NULL; /* Enables/disables index on appl_id */
499 NameStr VARCHAR2(100) := NULL; /* 'name like' part in where clause */
500 DispNameStr VARCHAR2(100) := NULL; /* 'name like' part for disp set/group */
501 ErrorNum NUMBER;
502 ErrorMsg VARCHAR2(512);
503 AdjustedName VARCHAR2(60) := NULL;
504 BEGIN
505 RG_XFER_UTILS_PKG.display_log(
506 MsgLevel => G_ML_Full,
507 MsgName => 'RG_XFER_L_ENTER_ROUTINE',
508 Token1 => 'ROUTINE',
509 Token1Val => 'copy_single_component',
510 Token2 => 'PARAM1',
511 Token2Val => ComponentType,
512 Token3 => 'PARAM2',
513 Token3Val => ComponentName);
514
515 /* Account for single quotes */
516 RG_XFER_UTILS_PKG.copy_adjust_string(AdjustedName, ComponentName);
517
518 /* Check for wildcard character (%) and set up strings */
519 IF (ComponentName IS NOT NULL) THEN
520 IF (INSTR(ComponentName, '%', 1) = 0) THEN
521 /* No percent sign */
522 ApplIdStr := '+0'; /* Disable index */
523 NameStr := NULL;
524 DispNameStr := NULL;
525 ELSE
526 /* There is a percent sign; wildcard query */
527 ApplIdStr := NULL; /* Enable index */
528 NameStr := ' AND (name LIKE ''' || AdjustedName || ''')';
529 DispNameStr := ' WHERE (name LIKE ''' || AdjustedName || ''')';
530 END IF;
531 END IF;
532
533 IF ((ComponentName IS NOT NULL) AND (NameStr IS NULL)) THEN
534 /* A simple component name has been specified. Copy it. */
535
536 /* Set save point */
537 SAVEPOINT new_component;
538
539 /* Initialize id */
540 Id := G_Error;
541
542 /* Print starting to process message */
543 RG_XFER_UTILS_PKG.display_message(MsgName => 'RG_XFER_BLANK');
544 RG_XFER_UTILS_PKG.display_message(
545 MsgName => 'RG_XFER_PROCESSING',
546 Token1 => 'COMP_TYPE',
547 Token1Val => ComponentType,
548 Token1Xlate => TRUE,
549 Token2 => 'COMP_NAME',
550 Token2Val => ComponentName,
551 Token2Xlate => FALSE);
552
553 /* Call the appropriate routine to copy the component */
554 IF (ComponentType = 'RG_ROW_SET') THEN
555 Id := copy_axis_set(ComponentType, ComponentName, CheckExistence);
556 ELSIF (ComponentType = 'RG_COLUMN_SET') THEN
557 Id := copy_axis_set(ComponentType, ComponentName, CheckExistence);
558 ELSIF (ComponentType = 'RG_CONTENT_SET') THEN
559 Id := copy_content_set(ComponentName, CheckExistence);
560 ELSIF (ComponentType = 'RG_ROW_ORDER') THEN
561 Id := copy_row_order(ComponentName, CheckExistence);
562 ELSIF (ComponentType = 'RG_DISPLAY_SET') THEN
563 Id := copy_display_set(ComponentName, CheckExistence);
564 ELSIF (ComponentType = 'RG_DISPLAY_GROUP') THEN
565 Id := copy_display_group(ComponentName, CheckExistence);
566 ELSIF (ComponentType = 'RG_REPORT') THEN
567 Id := copy_report(ComponentName, CheckExistence);
568 ELSIF (ComponentType = 'RG_REPORT_SET') THEN
569 Id := copy_report_set(ComponentName, CheckExistence);
570 ELSE
571 RG_XFER_UTILS_PKG.display_log(
572 MsgLevel => G_ML_Full,
576 END IF;
573 MsgName => 'RG_XFER_L_INVALID',
574 Token1 => 'VALUE',
575 Token1Val => ComponentType);
577
578 /* Commit or rollback depending on results from copy. */
579 IF (Id = G_Error) THEN
580 ROLLBACK;
581 RG_XFER_UTILS_PKG.display_message(MsgName => 'RG_XFER_BLANK');
582 RG_XFER_UTILS_PKG.display_error(
583 MsgName => 'RG_XFER_ROLLBACK',
584 Token1 => 'COMP_TYPE',
585 Token1Val => ComponentType,
586 Token1Xlate => TRUE,
587 Token2 => 'COMP_NAME',
588 Token2Val => ComponentName,
589 Token2Xlate => FALSE);
590 RG_XFER_UTILS_PKG.display_message(MsgName => 'RG_XFER_BLANK');
591 ELSIF (Id = G_Warning) THEN
592 COMMIT;
593 RG_XFER_UTILS_PKG.display_message(MsgName => 'RG_XFER_BLANK');
594 RG_XFER_UTILS_PKG.display_warning(
595 MsgName => 'RG_XFER_WARNING',
596 Token1 => 'COMP_TYPE',
597 Token1Val => ComponentType,
598 Token1Xlate => TRUE,
599 Token2 => 'COMP_NAME',
600 Token2Val => ComponentName,
601 Token2Xlate => FALSE);
602 RG_XFER_UTILS_PKG.display_message(MsgName => 'RG_XFER_BLANK');
603 ELSE
604 COMMIT;
605 RG_XFER_UTILS_PKG.display_message(MsgName => 'RG_XFER_BLANK');
606 RG_XFER_UTILS_PKG.display_message(
607 MsgName => 'RG_XFER_COMMIT',
608 Token1 => 'COMP_TYPE',
609 Token1Val => ComponentType,
610 Token1Xlate => TRUE,
611 Token2 => 'COMP_NAME',
612 Token2Val => ComponentName,
613 Token2Xlate => FALSE);
614 RG_XFER_UTILS_PKG.display_message(MsgName => 'RG_XFER_BLANK');
615 END IF;
616
617 ELSE
618 /* A wildcard character or string for all components has been specified.
619 * Copy the specified components of ComponentType. Do this by querying the
620 * specified names of ComponentType, e.g., all specified row set names.
621 * Then copy each component individually by calling copy_single_component
622 * recursively. */
623
624 /* Set the table name to use in the query */
625 IF (ComponentType = 'RG_ROW_SET') THEN
626 TableName := 'RG_REPORT_AXIS_SETS';
627 WhereClause := ' WHERE AXIS_SET_TYPE = ''R''' ||
628 ' AND (STRUCTURE_ID IS NULL' ||
629 ' OR STRUCTURE_ID = ' ||TO_CHAR(G_SourceCOAId)||')'||
630 ' AND (application_id' || ApplIdStr || '=' ||
631 TO_CHAR(G_ApplId) || ')' || NameStr;
632 ELSIF (ComponentType = 'RG_COLUMN_SET') THEN
633 TableName := 'RG_REPORT_AXIS_SETS';
634 WhereClause := ' WHERE AXIS_SET_TYPE = ''C''' ||
635 ' AND (STRUCTURE_ID IS NULL' ||
636 ' OR STRUCTURE_ID = ' ||TO_CHAR(G_SourceCOAId)||')'||
637 ' AND ((application_id' || ApplIdStr || ' = 168)' ||
638 ' OR (application_id' || ApplIdStr || ' = ' ||
639 TO_CHAR(G_ApplId)||'))' || NameStr;
640 ELSIF (ComponentType = 'RG_CONTENT_SET') THEN
641 TableName := 'RG_REPORT_CONTENT_SETS';
642 WhereClause := ' WHERE structure_id = ' || TO_CHAR(G_SourceCOAId) ||
643 ' AND application_id' || ApplIdStr || ' = ' ||
644 TO_CHAR(G_ApplId) || NameStr;
645 ELSIF (ComponentType = 'RG_ROW_ORDER') THEN
646 TableName := 'RG_ROW_ORDERS';
647 WhereClause := ' WHERE STRUCTURE_ID = ' || TO_CHAR(G_SourceCOAId) ||
648 ' AND application_id' || ApplIdStr || ' = ' ||
649 TO_CHAR(G_ApplId) || NameStr;
650 ELSIF (ComponentType = 'RG_DISPLAY_SET') THEN
651 TableName := 'RG_REPORT_DISPLAY_SETS';
652 WhereClause := DispNameStr;
653 ELSIF (ComponentType = 'RG_DISPLAY_GROUP') THEN
654 TableName := 'RG_REPORT_DISPLAY_GROUPS';
655 WhereClause := DispNameStr;
656 ELSIF (ComponentType = 'RG_REPORT') THEN
657 TableName := 'RG_REPORTS';
658 WhereClause := ' WHERE STRUCTURE_ID = ' || TO_CHAR(G_SourceCOAId) ||
659 ' AND application_id' || ApplIdStr || ' = ' ||
660 TO_CHAR(G_ApplId) || NameStr;
661 ELSIF (ComponentType = 'RG_REPORT_SET') THEN
662 TableName := 'RG_REPORT_SETS';
663 WhereClause := ' WHERE STRUCTURE_ID = ' || TO_CHAR(G_SourceCOAId) ||
664 ' AND application_id' || ApplIdStr || ' = ' ||
665 TO_CHAR(G_ApplId) || NameStr;
666 ELSE
667 RG_XFER_UTILS_PKG.display_log(
668 MsgLevel => G_ML_Full,
669 MsgName => 'RG_XFER_L_INVALID',
670 Token1 => 'VALUE',
671 Token1Val => ComponentType);
672 END IF;
673
674 IF (TableName IS NOT NULL) THEN
675 /* Build the SQL stmt to get all name for the specified component */
676 SQLString := SQLString || TableName || '@' || G_LinkName || WhereClause;
677 RG_XFER_UTILS_PKG.display_string(SQLString);
678
679 /* Execute the SQL stmt */
680 CursorId := DBMS_SQL.open_cursor;
681 DBMS_SQL.parse(CursorId, SQLString, DBMS_SQL.v7);
682 DBMS_SQL.define_column(CursorId, 1, CompName, 30);
683 ExecuteValue := DBMS_SQL.execute(CursorId);
684 LOOP
685 /* For each component, call the routine recursively to do the copy */
689 * name is NULL for some reason, then the routine will try to
686 IF (DBMS_SQL.fetch_rows(CursorId) > 0) THEN
687 DBMS_SQL.column_value(CursorId, 1, CompName);
688 /* Redundant check to ensure that the name is NOT NULL. If the
690 * copy all components again, and will cause errors. */
691 IF (CompName IS NOT NULL) THEN
692 /* Call routine with CheckExistence set to FALSE since
693 * we know that the name is valid in the source db. */
694 copy_single_component(
695 ComponentType,
696 CompName,
697 CheckExistence => FALSE);
698 END IF;
699 ELSE
700 EXIT;
701 END IF;
702 END LOOP;
703
704 /* Print messages if no matching rows found */
705 IF (DBMS_SQL.last_row_count = 0) THEN
706 RG_XFER_UTILS_PKG.display_message(MsgName => 'RG_XFER_NO_MATCH_ROWS');
707 RG_XFER_UTILS_PKG.display_message(MsgName => 'RG_XFER_BLANK');
708 END IF;
709
710 DBMS_SQL.close_cursor(CursorId);
711
712 END IF;
713 END IF;
714
715 RG_XFER_UTILS_PKG.display_log(
716 MsgLevel => G_ML_Full,
717 MsgName => 'RG_XFER_L_EXIT_ROUTINE',
718 Token1 => 'ROUTINE',
719 Token1Val => 'copy_single_component');
720
721 EXCEPTION
722 WHEN OTHERS THEN
723 /* Display the exception if MsgLevel is at least Normal */
724 ErrorNum := SQLCODE;
725 ErrorMsg := SUBSTRB(SQLERRM, 1, 512);
726 RG_XFER_UTILS_PKG.display_exception(ErrorNum, ErrorMsg);
727
728 /* Rollback the changes and display error message */
729 ROLLBACK;
730 RG_XFER_UTILS_PKG.display_error(
731 MsgName => 'RG_XFER_ROLLBACK',
732 Token1 => 'COMP_TYPE',
733 Token1Val => ComponentType,
734 Token1Xlate => TRUE,
735 Token2 => 'COMP_NAME',
736 Token2Val => ComponentName,
737 Token2Xlate => FALSE);
738 RAISE;
739
740 END copy_single_component;
741
742
743 /* Name: copy_axis_set
744 * Desc: Copies the specified axis set, i.e., row set, or column set.
745 *
746 * Notes: ComponentName MUST be NOT NULL.
747 *
748 * History:
749 * 10/17/95 S Rahman Created.
750 * 04/18/03 V Treiger Modified
751 */
752 FUNCTION copy_axis_set(
753 ComponentType VARCHAR2,
754 ComponentName VARCHAR2,
755 CheckExistence BOOLEAN DEFAULT TRUE) RETURN NUMBER IS
756 TargetId NUMBER; /* Target component id */
757 SourceId NUMBER; /* Source component id */
758 AxisSetType VARCHAR2(1);
759 SQLString VARCHAR2(5000);
760 CheckCOA NUMBER;
761 CheckTargetCOA NUMBER;
762 AdjustedName VARCHAR2(60);
763
764 CursorId INTEGER;
765 ExecuteValue INTEGER;
766 RefObjectName VARCHAR2(240);
767 ValueString VARCHAR2(240);
768 TempValue VARCHAR2(100);
769 TaxTargetId NUMBER; /* Taxonomy Target id */
770 TaxSourceId NUMBER; /* Taxonomy Source id */
771 TaxAlias VARCHAR2(240);
772 AdjustedTaxAlias VARCHAR2(240);
773 Tax_Done_Flag NUMBER;
774 ErrorNum NUMBER;
775 ErrorMsg VARCHAR2(512);
776
777 BEGIN
778 RG_XFER_UTILS_PKG.display_log(
779 MsgLevel => G_ML_Full,
780 MsgName => 'RG_XFER_L_ENTER_ROUTINE',
781 Token1 => 'ROUTINE',
782 Token1Val => 'copy_axis_set',
783 Token2 => 'PARAM1',
784 Token2Val => ComponentType,
785 Token3 => 'PARAM2',
786 Token3Val => ComponentName);
787
788 /* Store the name of the component being copied */
789 IF (ComponentType = 'RG_ROW_SET') THEN
790 G_AxisSet := ComponentName;
791 AxisSetType := 'R';
792 ELSE
793 G_AxisSet := ComponentName;
794 AxisSetType := 'C';
795 END IF;
796
797 /* Account for single quotes */
798 RG_XFER_UTILS_PKG.copy_adjust_string(AdjustedName, ComponentName);
799
800 /* Ensure that the component exists in the source database */
801 IF (CheckExistence) THEN
802 IF (NOT RG_XFER_UTILS_PKG.source_component_exists(
803 ComponentType, ComponentName)) THEN
804 RG_XFER_UTILS_PKG.display_error(
805 MsgName => 'RG_XFER_COMP_NOT_EXIST',
806 Token1 => 'COMP_TYPE',
807 Token1Val => ComponentType,
808 Token1Xlate => TRUE,
809 Token2 => 'COMP_NAME',
810 Token2Val => ComponentName,
811 Token2Xlate => FALSE);
812 RETURN(G_Error);
813 END IF;
814 END IF;
815
816 /* Ensure that the COA id of the component matches the source COA id */
817 CheckCOA := RG_XFER_UTILS_PKG.check_coa_id(
818 'RG_REPORT_AXIS_SETS',
819 ComponentName,
820 ' AND ((application_id = 168)' ||
821 ' OR (application_id = ' || TO_CHAR(G_ApplId) || '))' ||
822 ' AND axis_set_type = ''' || AxisSetType || '''');
823 IF (CheckCOA = G_Error) THEN
824 RG_XFER_UTILS_PKG.display_warning(
825 MsgName => 'RG_XFER_WRONG_COA',
826 Token1 => 'COMP_TYPE',
827 Token1Val => ComponentType,
828 Token1Xlate => TRUE,
829 Token2 => 'COMP_NAME',
833 MsgLevel => G_ML_Full,
830 Token2Val => ComponentName,
831 Token2Xlate => FALSE);
832 RG_XFER_UTILS_PKG.display_log(
834 MsgName => 'RG_XFER_L_EXIT_ROUTINE_WARN',
835 Token1 => 'ROUTINE',
836 Token1Val => 'copy_axis_set');
837 RETURN(G_Warning);
838 END IF;
839
840 /* Begin XBRL related changes */
841 /* Check if TAXONOMY_ID is NULL in source db XBRL */
842 TaxSourceId := RG_XFER_UTILS_PKG.get_source_id(
843 'RG_REPORT_AXIS_SETS', 'TAXONOMY_ID', ComponentName,
844 ' AND ((application_id = 168)' ||
845 ' OR (application_id = ' || TO_CHAR(G_ApplId) || '))' ||
846 ' AND axis_set_type = ''' || AxisSetType || '''');
847
848 /* IF TAXONOMY_ID is not NULL THEN */
849 /* Check if a taxonomy of the same alias already exists in target db XBRL */
850 /* ELSE deliver taxonomy of the same URL with all imported taxonomies
851 to target db by a call to a recursive procedure transfer_taxonomy */
852
853 IF (TaxSourceId IS NULL) THEN
854 TaxAlias := '';
855 TaxTargetId := NULL;
856 ELSE
857 RG_XFER_UTILS_PKG.copy_adjust_string(TempValue, ComponentName);
858 ValueString := '''' || TempValue || '''';
859
860 SQLString := 'SELECT ref_table.taxonomy_alias '||
861 'FROM RG_REPORT_AXIS_SETS'||'@'||G_LinkName||' main_table,' ||
862 ' RG_XBRL_TAXONOMIES' || '@'|| G_LinkName || ' ref_table ' ||
863 'WHERE main_table.name' || '='|| ValueString ||
864 ' AND main_table.taxonomy_id = ref_table.taxonomy_id';
865
866 RG_XFER_UTILS_PKG.display_string(SQLString);
867 CursorId := DBMS_SQL.open_cursor;
868 DBMS_SQL.parse(CursorId, SQLString, DBMS_SQL.v7);
869 DBMS_SQL.define_column(CursorId, 1, RefObjectName, 240);
870 ExecuteValue := DBMS_SQL.execute_and_fetch(CursorId);
871 IF (ExecuteValue > 0) THEN
872 DBMS_SQL.column_value(CursorId, 1, RefObjectName);
873 ELSE
874 RefObjectName := '';
875 END IF;
876 DBMS_SQL.close_cursor(CursorId);
877 TaxAlias := RefObjectName;
878
879 /* Check if a taxonomy with the same alias already exists in target db */
880 RG_XFER_UTILS_PKG.copy_adjust_string(TempValue, TaxAlias);
881 ValueString := '''' || TempValue || '''';
882
883 SQLString := 'SELECT taxonomy_id ' ||
884 'FROM rg_xbrl_taxonomies ' ||
885 'WHERE taxonomy_alias = ' || ValueString;
886 TargetId := RG_XFER_UTILS_PKG.component_exists(SQLString);
887
888 IF (TargetId = G_Error) THEN
889 /* deliver taxonomy to target db */
890 Tax_Done_Flag := 0;
891 transfer_taxonomy(TaxAlias,TaxSourceId,Tax_Done_Flag);
892 END IF;
893
894 /* Check if a taxonomy with the same alias already exists in target db */
895 RG_XFER_UTILS_PKG.copy_adjust_string(TempValue, TaxAlias);
896 ValueString := '''' || TempValue || '''';
897
898 SQLString := 'SELECT taxonomy_id ' ||
899 'FROM rg_xbrl_taxonomies ' ||
900 'WHERE taxonomy_alias = ' || ValueString;
901 TaxTargetId := RG_XFER_UTILS_PKG.component_exists(SQLString);
902
903 END IF;
904 /* End XBRL related changes */
905
906 /* Check if a component of the same name already exists in target db */
907 SQLString := 'SELECT axis_set_id ' ||
908 'FROM rg_report_axis_sets ' ||
909 'WHERE name = ''' || AdjustedName || '''' ||
910 'AND axis_set_type = ''' || AxisSetType || ''''||
911 'AND ((application_id = 168)' ||
912 ' OR (application_id = ' || TO_CHAR(G_ApplId) || '))';
913 TargetId := RG_XFER_UTILS_PKG.component_exists(SQLString);
914
915 IF (TargetId = G_Error) THEN
916
917 /* Component doesn't exist in target db. Insert data into table */
918 TargetId := RG_XFER_UTILS_PKG.get_new_id('RG_REPORT_AXIS_SETS_S');
919 SourceId := RG_XFER_UTILS_PKG.get_source_id(
920 'RG_REPORT_AXIS_SETS', 'AXIS_SET_ID', ComponentName,
921 ' AND ((application_id = 168)' ||
922 ' OR (application_id = ' || TO_CHAR(G_ApplId) || '))' ||
923 ' AND axis_set_type = ''' || AxisSetType || '''');
924 SQLString := AxisSetsString || G_LinkName ||
925 ' WHERE axis_set_id = ' || TO_CHAR(SourceId);
926
927 /* The structure_id in rg_report_axis_sets may or may not have a value.
928 * Substitute the token accordingly. */
929 IF (CheckCOA = RG_XFER_UTILS_PKG.G_NoCOA) THEN
930 RG_XFER_UTILS_PKG.substitute_tokens(
931 SQLString, Token1=> ':coa_id', Token1Val=> 'NULL');
932 ELSE
933 RG_XFER_UTILS_PKG.substitute_tokens(
934 SQLString, Token1=> ':coa_id', Token1Val=> TO_CHAR(G_TargetCOAId));
935 END IF;
936
937 /* Begin XBRL token substitution for TaxTargetId.
938 * Substitute the token accordingly. */
939 IF (TaxTargetId IS NULL) THEN
940 RG_XFER_UTILS_PKG.substitute_tokens(
941 SQLString, Token1=> ':tax_id', Token1Val=> 'NULL');
942 ELSE
943 RG_XFER_UTILS_PKG.substitute_tokens(
944 SQLString, Token1=> ':tax_id', Token1Val=> TO_CHAR(TaxTargetId));
945 END IF;
946 /* End XBRL token substitution for TaxTargetId. */
947
948 RG_XFER_UTILS_PKG.insert_rows(SQLString, TargetId, UseCOAId => FALSE);
952 END IF;
949
950 IF (ComponentType = 'RG_COLUMN_SET') THEN
951 RG_XFER_COMP_PRIVATE_PKG.copy_column_set_header(SourceId, TargetId);
953
954 /* Copy axis set detail records */
955 RG_XFER_COMP_PRIVATE_PKG.copy_axis_details(
956 ComponentType, G_AxisSet, SourceId, TargetId);
957
958 /* New component - insert into the list of components copied */
959 IF (ComponentType = 'RG_ROW_SET') THEN
960 RG_XFER_UTILS_PKG.insert_into_list(
961 RowSetList, RowSetCount, ComponentName);
962 RG_XFER_UTILS_PKG.display_log(
963 MsgLevel => G_ML_Normal,
964 MsgName => 'RG_XFER_L_TRANSFERRED',
965 Token1 => 'COMP_TYPE',
966 Token1Val => 'RG_ROW_SET',
967 Token1Xlate => TRUE,
968 Token2 => 'COMP_NAME',
969 Token2Val => ComponentName,
970 Token2Xlate => FALSE);
971 ELSE
972 RG_XFER_UTILS_PKG.insert_into_list(
973 ColumnSetList, ColumnSetCount, ComponentName);
974 RG_XFER_UTILS_PKG.display_log(
975 MsgLevel => G_ML_Normal,
976 MsgName => 'RG_XFER_L_TRANSFERRED',
977 Token1 => 'COMP_TYPE',
978 Token1Val => 'RG_COLUMN_SET',
979 Token1Xlate => TRUE,
980 Token2 => 'COMP_NAME',
981 Token2Val => ComponentName,
982 Token2Xlate => FALSE);
983 END IF;
984
985 ELSE
986
987 /* Component with same name already exists in target db. Check if
988 * this component was copied by this run. */
989 IF (ComponentType = 'RG_ROW_SET') THEN
990 IF (RG_XFER_UTILS_PKG.search_list(
991 RowSetList, RowSetCount, ComponentName) = G_Error) THEN
992 /* Component with same name existed before this run. */
993
994 /* Check if it uses the correct chart of accounts id. */
995 CheckTargetCOA :=
996 RG_XFER_UTILS_PKG.check_target_coa_id(
997 'RG_REPORT_AXIS_SETS',
998 ComponentName,
999 ' AND ((application_id = 168)' ||
1000 ' OR (application_id = ' || TO_CHAR(G_ApplId) || '))' ||
1001 ' AND axis_set_type = ''' || AxisSetType || '''');
1002 IF (CheckTargetCOA = G_Error) THEN
1003 RG_XFER_UTILS_PKG.display_warning(
1004 MsgName => 'RG_XFER_TARGET_COA_MISMATCH',
1005 Token1 => 'COMP_TYPE',
1006 Token1Val => ComponentType,
1007 Token1Xlate => TRUE,
1008 Token2 => 'COMP_NAME',
1009 Token2Val => ComponentName,
1010 Token2Xlate => FALSE);
1011 RG_XFER_UTILS_PKG.display_log(
1012 MsgLevel => G_ML_Full,
1013 MsgName => 'RG_XFER_L_EXIT_ROUTINE_WARNING',
1014 Token1 => 'ROUTINE',
1015 Token1Val => 'copy_axis_set');
1016 RETURN(G_Warning);
1017 ELSE
1018 /* Show warning and use the existing id. */
1019 RG_XFER_UTILS_PKG.display_warning(
1020 MsgName => 'RG_XFER_COMP_EXIST',
1021 Token1 => 'COMP_TYPE',
1022 Token1Val => 'RG_ROW_SET',
1023 Token1Xlate => TRUE,
1024 Token2 => 'COMP_NAME',
1025 Token2Val => ComponentName,
1026 Token2Xlate => FALSE);
1027 END IF;
1028 END IF;
1029 ELSE
1030 IF (RG_XFER_UTILS_PKG.search_list(
1031 ColumnSetList, ColumnSetCount, ComponentName) = G_Error) THEN
1032 /* Component with same name existed before this run. */
1033
1034 /* Check if it uses the correct chart of accounts id. */
1035 CheckTargetCOA :=
1036 RG_XFER_UTILS_PKG.check_target_coa_id(
1037 'RG_REPORT_AXIS_SETS',
1038 ComponentName,
1039 ' AND ((application_id = 168)' ||
1040 ' OR (application_id = ' || TO_CHAR(G_ApplId) || '))' ||
1041 ' AND axis_set_type = ''' || AxisSetType || '''');
1042 IF (CheckTargetCOA = G_Error) THEN
1043 RG_XFER_UTILS_PKG.display_warning(
1044 MsgName => 'RG_XFER_TARGET_COA_MISMATCH',
1045 Token1 => 'COMP_TYPE',
1046 Token1Val => ComponentType,
1047 Token1Xlate => TRUE,
1048 Token2 => 'COMP_NAME',
1049 Token2Val => ComponentName,
1050 Token2Xlate => FALSE);
1051 RG_XFER_UTILS_PKG.display_log(
1052 MsgLevel => G_ML_Full,
1053 MsgName => 'RG_XFER_L_EXIT_ROUTINE_WARNING',
1054 Token1 => 'ROUTINE',
1055 Token1Val => 'copy_axis_set');
1056 RETURN(G_Warning);
1057 ELSE
1058 /* Show warning and use the existing id. */
1059 RG_XFER_UTILS_PKG.display_warning(
1060 MsgName => 'RG_XFER_COMP_EXIST',
1061 Token1 => 'COMP_TYPE',
1062 Token1Val => 'RG_COLUMN_SET',
1063 Token1Xlate => TRUE,
1064 Token2 => 'COMP_NAME',
1065 Token2Val => ComponentName,
1066 Token2Xlate => FALSE);
1067 END IF;
1068 END IF;
1069 END IF;
1070
1071 END IF;
1072
1073 /* Clear the name of the component being copied */
1074 G_AxisSet := NULL;
1075
1076 RG_XFER_UTILS_PKG.display_log(
1080 Token1Val => 'copy_axis_set');
1077 MsgLevel => G_ML_Full,
1078 MsgName => 'RG_XFER_L_EXIT_ROUTINE',
1079 Token1 => 'ROUTINE',
1081
1082 /* Return the id to be used for this component */
1083 RETURN(TargetId);
1084
1085 /* This exception is added under the Bug#3843014 */
1086 EXCEPTION
1087 WHEN OTHERS THEN
1088 /* Display the exception if MsgLevel is at least Normal */
1089 ErrorNum := SQLCODE;
1090 ErrorMsg := SUBSTRB(SQLERRM, 1, 512);
1091 RG_XFER_UTILS_PKG.display_exception(ErrorNum, ErrorMsg);
1092
1093 RG_XFER_UTILS_PKG.display_log(
1094 MsgLevel => G_ML_Full,
1095 MsgName => 'RG_XFER_L_EXIT_ROUTINE_ERROR',
1096 Token1 => 'ROUTINE',
1097 Token1Val => 'copy_axis_set');
1098 RETURN(G_Error);
1099
1100 END copy_axis_set;
1101
1102
1103 /* Name: copy_content_set
1104 * Desc: Copies the specified content set. Return the id of the copied
1105 * component. If the component already exists in the target db,
1106 * then return the id for the existing component.
1107 *
1108 * Notes: ComponentName MUST be NOT NULL.
1109 *
1110 * History:
1111 * 10/17/95 S Rahman Created.
1112 */
1113 FUNCTION copy_content_set(
1114 ComponentName VARCHAR2,
1115 CheckExistence BOOLEAN DEFAULT TRUE) RETURN NUMBER IS
1116 TargetId NUMBER; /* Target component id */
1117 SourceId NUMBER; /* Source component id */
1118 SQLString VARCHAR2(2000);
1119 AdjustedName VARCHAR2(60);
1120 BEGIN
1121 RG_XFER_UTILS_PKG.display_log(
1122 MsgLevel => G_ML_Full,
1123 MsgName => 'RG_XFER_L_ENTER_ROUTINE',
1124 Token1 => 'ROUTINE',
1125 Token1Val => 'copy_content_set',
1126 Token2 => 'PARAM1',
1127 Token2Val => ComponentName);
1128
1129 /* Store the name of the component being copied */
1130 G_ContentSet := ComponentName;
1131
1132 /* Account for single quotes */
1133 RG_XFER_UTILS_PKG.copy_adjust_string(AdjustedName, ComponentName);
1134
1135 /* Ensure that the component exists in the source database */
1136 IF (CheckExistence) THEN
1137 IF (NOT RG_XFER_UTILS_PKG.source_component_exists(
1138 'RG_CONTENT_SET', ComponentName)) THEN
1139 RG_XFER_UTILS_PKG.display_error(
1140 MsgName => 'RG_XFER_COMP_NOT_EXIST',
1141 Token1 => 'COMP_TYPE',
1142 Token1Val => 'RG_CONTENT_SET',
1143 Token1Xlate => TRUE,
1144 Token2 => 'COMP_NAME',
1145 Token2Val => ComponentName,
1146 Token2Xlate => FALSE);
1147 RETURN(G_Error);
1148 END IF;
1149 END IF;
1150
1151 /* Ensure that the COA id of the component matches the source COA id */
1152 IF (RG_XFER_UTILS_PKG.check_coa_id(
1153 'RG_REPORT_CONTENT_SETS', ComponentName) = G_Error) THEN
1154 RG_XFER_UTILS_PKG.display_warning(
1155 MsgName => 'RG_XFER_WRONG_COA',
1156 Token1 => 'COMP_TYPE',
1157 Token1Val => 'RG_CONTENT_SET',
1158 Token1Xlate => TRUE,
1159 Token2 => 'COMP_NAME',
1160 Token2Val => ComponentName,
1161 Token2Xlate => FALSE);
1162 RG_XFER_UTILS_PKG.display_log(
1163 MsgLevel => G_ML_Full,
1164 MsgName => 'RG_XFER_L_EXIT_ROUTINE_WARN',
1165 Token1 => 'ROUTINE',
1166 Token1Val => 'copy_content_set');
1167 RETURN(G_Warning);
1168 END IF;
1169
1170 /* Check if a component of the same name already exists in target db */
1171 SQLString := 'SELECT content_set_id ' ||
1172 'FROM rg_report_content_sets ' ||
1173 'WHERE name = ''' || AdjustedName || '''' ||
1174 'AND application_id = ' || TO_CHAR(G_ApplId);
1175 TargetId := RG_XFER_UTILS_PKG.component_exists(SQLString);
1176
1177 IF (TargetId = G_Error) THEN
1178 /* Insert data into table */
1179 TargetId := RG_XFER_UTILS_PKG.get_new_id('RG_REPORT_CONTENT_SETS_S');
1180 SourceId := RG_XFER_UTILS_PKG.get_source_id(
1181 'RG_REPORT_CONTENT_SETS',
1182 'CONTENT_SET_ID',
1183 ComponentName,
1184 ' AND application_id = ' || TO_CHAR(G_ApplId));
1185 RG_XFER_UTILS_PKG.insert_rows(
1186 ContentSetsString || G_LinkName ||
1187 ' WHERE content_set_id = ' || TO_CHAR(SourceId),
1188 TargetId, UseCOAId => TRUE);
1189 /* Copy the content set details */
1190 RG_XFER_COMP_PRIVATE_PKG.copy_content_set_details(
1191 G_ContentSet, SourceId, TargetId);
1192
1193 /* New component - insert into the list of components copied */
1194 RG_XFER_UTILS_PKG.insert_into_list(
1195 ContentSetList, ContentSetCount, ComponentName);
1196 RG_XFER_UTILS_PKG.display_log(
1197 MsgLevel => G_ML_Normal,
1198 MsgName => 'RG_XFER_L_TRANSFERRED',
1199 Token1 => 'COMP_TYPE',
1200 Token1Val => 'RG_CONTENT_SET',
1201 Token1Xlate => TRUE,
1202 Token2 => 'COMP_NAME',
1203 Token2Val => ComponentName,
1204 Token2Xlate => FALSE);
1205
1206 ELSE
1207
1208 /* Component with same name already exists in target db. Check if
1209 * this component was copied by this run. */
1210 IF (RG_XFER_UTILS_PKG.search_list(
1211 ContentSetList, ContentSetCount, ComponentName) = G_Error) THEN
1215 IF (RG_XFER_UTILS_PKG.check_target_coa_id(
1212 /* Component with same name existed before this run. */
1213
1214 /* Check if it uses the correct chart of accounts id. */
1216 'RG_REPORT_CONTENT_SETS', ComponentName) = G_Error) THEN
1217 RG_XFER_UTILS_PKG.display_warning(
1218 MsgName => 'RG_XFER_TARGET_COA_MISMATCH',
1219 Token1 => 'COMP_TYPE',
1220 Token1Val => 'RG_CONTENT_SET',
1221 Token1Xlate => TRUE,
1222 Token2 => 'COMP_NAME',
1223 Token2Val => ComponentName,
1224 Token2Xlate => FALSE);
1225 RG_XFER_UTILS_PKG.display_log(
1226 MsgLevel => G_ML_Full,
1227 MsgName => 'RG_XFER_L_EXIT_ROUTINE_WARNING',
1228 Token1 => 'ROUTINE',
1229 Token1Val => 'copy_content_set');
1230 RETURN(G_Warning);
1231 ELSE
1232 /* Show warning and use the existing id. */
1233 RG_XFER_UTILS_PKG.display_warning(
1234 MsgName => 'RG_XFER_COMP_EXIST',
1235 Token1 => 'COMP_TYPE',
1236 Token1Val => 'RG_CONTENT_SET',
1237 Token1Xlate => TRUE,
1238 Token2 => 'COMP_NAME',
1239 Token2Val => ComponentName,
1240 Token2Xlate => FALSE);
1241 END IF;
1242 END IF;
1243
1244 END IF;
1245
1246 /* Clear the name of the component being copied */
1247 G_ContentSet := NULL;
1248
1249 RG_XFER_UTILS_PKG.display_log(
1250 MsgLevel => G_ML_Full,
1251 MsgName => 'RG_XFER_L_EXIT_ROUTINE',
1252 Token1 => 'ROUTINE',
1253 Token1Val => 'copy_content_set');
1254
1255 /* Return the id to be used for this component */
1256 RETURN(TargetId);
1257 END copy_content_set;
1258
1259
1260 /* Name: copy_row_order
1261 * Desc: Copies the specified row order. Return the id of the copied
1262 * component. If the component already exists in the target db,
1263 * then return the id for the existing component.
1264 *
1265 * Notes: ComponentName MUST be NOT NULL.
1266 *
1267 * History:
1268 * 10/17/95 S Rahman Created.
1269 */
1270 FUNCTION copy_row_order(
1271 ComponentName VARCHAR2,
1272 CheckExistence BOOLEAN DEFAULT TRUE) RETURN NUMBER IS
1273 TargetId NUMBER; /* Target component id */
1274 SourceId NUMBER; /* Source component id */
1275 SQLString VARCHAR2(3000);
1276 AdjustedName VARCHAR2(60);
1277 ColumnName VARCHAR2(30);
1278 BEGIN
1279 RG_XFER_UTILS_PKG.display_log(
1280 MsgLevel => G_ML_Full,
1281 MsgName => 'RG_XFER_L_ENTER_ROUTINE',
1282 Token1 => 'ROUTINE',
1283 Token1Val => 'copy_row_order',
1284 Token2 => 'PARAM1',
1285 Token2Val => ComponentName);
1286
1287 /* Store the name of the component being copied */
1288 G_RowOrder := ComponentName;
1289
1290 /* Account for single quotes */
1291 RG_XFER_UTILS_PKG.copy_adjust_string(AdjustedName, ComponentName);
1292
1293 /* Ensure that the component exists in the source database */
1294 IF (CheckExistence) THEN
1295 IF (NOT RG_XFER_UTILS_PKG.source_component_exists(
1296 'RG_ROW_ORDER', ComponentName)) THEN
1297 RG_XFER_UTILS_PKG.display_error(
1298 MsgName => 'RG_XFER_COMP_NOT_EXIST',
1299 Token1 => 'COMP_TYPE',
1300 Token1Val => 'RG_ROW_ORDER',
1301 Token1Xlate => TRUE,
1302 Token2 => 'COMP_NAME',
1303 Token2Val => ComponentName,
1304 Token2Xlate => FALSE);
1305 RETURN(G_Error);
1306 END IF;
1307 END IF;
1308
1309 /* Ensure that the COA id of the component matches the source COA id */
1310 IF (RG_XFER_UTILS_PKG.check_coa_id(
1311 'RG_ROW_ORDERS', ComponentName) = G_Error) THEN
1312 RG_XFER_UTILS_PKG.display_warning(
1313 MsgName => 'RG_XFER_WRONG_COA',
1314 Token1 => 'COMP_TYPE',
1315 Token1Val => 'RG_ROW_ORDER',
1316 Token1Xlate => TRUE,
1317 Token2 => 'COMP_NAME',
1318 Token2Val => ComponentName,
1319 Token2Xlate => FALSE);
1320 RG_XFER_UTILS_PKG.display_log(
1321 MsgLevel => G_ML_Full,
1322 MsgName => 'RG_XFER_L_EXIT_ROUTINE_WARN',
1323 Token1 => 'ROUTINE',
1324 Token1Val => 'copy_row_order');
1325 RETURN(G_Warning);
1326 END IF;
1327
1328 /* Check if a component of the same name already exists in target db */
1329 SQLString := 'SELECT row_order_id ' ||
1330 'FROM rg_row_orders ' ||
1331 'WHERE name = ''' || AdjustedName || '''' ||
1332 'AND application_id = ' || TO_CHAR(G_ApplId);
1333 TargetId := RG_XFER_UTILS_PKG.component_exists(SQLString);
1334
1335 IF (TargetId = G_Error) THEN
1336 /* Insert data into table */
1337 TargetId := RG_XFER_UTILS_PKG.get_new_id('RG_ROW_ORDERS_S');
1338 SourceId := RG_XFER_UTILS_PKG.get_source_id(
1339 'RG_ROW_ORDERS',
1340 'ROW_ORDER_ID',
1341 ComponentName,
1342 ' AND application_id = ' || TO_CHAR(G_ApplId));
1343
1344 /* Check the existence of the column name, if any */
1345 ColumnName := RG_XFER_UTILS_PKG.get_varchar2(
1346 'SELECT column_name FROM rg_row_orders@'|| G_LinkName ||
1350 IF (NOT RG_XFER_UTILS_PKG.ro_column_exists(ColumnName)) THEN
1347 ' WHERE row_order_id='||TO_CHAR(SourceId),
1348 30);
1349 IF (ColumnName IS NOT NULL) THEN
1351 /* Warning: column not defined in target database */
1352 RG_XFER_UTILS_PKG.display_warning(
1353 MsgName => 'RG_XFER_RO_COLUMN_NOT_EXIST',
1354 Token1 => 'COLUMN_NAME',
1355 Token1Val => ColumnName,
1356 Token1Xlate => FALSE,
1357 Token2 => 'COMP_NAME',
1358 Token2Val => ComponentName,
1359 Token2Xlate => FALSE);
1360 ColumnName := 'NULL';
1361 ELSE
1362 /* select the column name */
1363 ColumnName := 'COLUMN_NAME';
1364 END IF;
1365 ELSE
1366 ColumnName := 'NULL';
1367 END IF;
1368
1369 /* Substitute column_name token and insert row */
1370 SQLString := RowOrdersString || G_LinkName ||
1371 ' WHERE row_order_id = ' || TO_CHAR(SourceId);
1372 RG_XFER_UTILS_PKG.substitute_tokens(
1373 SQLString,
1374 Token1=> ':column_name',
1375 Token1Val=> ColumnName);
1376 RG_XFER_UTILS_PKG.insert_rows(SQLString, TargetId, UseCOAId=> TRUE);
1377
1378 /* Insert detail rows */
1379 RG_XFER_UTILS_PKG.insert_rows(
1380 RowSegmentSequencesString || G_LinkName ||
1381 ' WHERE row_order_id = ' || TO_CHAR(SourceId),
1382 TargetId, UseCOAId => TRUE);
1383
1384 /* New component - insert into the list of components copied */
1385 RG_XFER_UTILS_PKG.insert_into_list(
1386 RowOrderList, RowOrderCount, ComponentName);
1387 RG_XFER_UTILS_PKG.display_log(
1388 MsgLevel => G_ML_Normal,
1389 MsgName => 'RG_XFER_L_TRANSFERRED',
1390 Token1 => 'COMP_TYPE',
1391 Token1Val => 'RG_ROW_ORDER',
1392 Token1Xlate => TRUE,
1393 Token2 => 'COMP_NAME',
1394 Token2Val => ComponentName,
1395 Token2Xlate => FALSE);
1396
1397 ELSE
1398
1399 /* Component with same name already exists in target db. Check if
1400 * this component was copied by this run. */
1401 IF (RG_XFER_UTILS_PKG.search_list(
1402 RowOrderList, RowOrderCount, ComponentName) = G_Error) THEN
1403 /* Component with same name existed before this run. */
1404
1405 /* Check if it uses the correct chart of accounts id. */
1406 IF (RG_XFER_UTILS_PKG.check_target_coa_id(
1407 'RG_ROW_ORDERS', ComponentName) = G_Error) THEN
1408 RG_XFER_UTILS_PKG.display_warning(
1409 MsgName => 'RG_XFER_TARGET_COA_MISMATCH',
1410 Token1 => 'COMP_TYPE',
1411 Token1Val => 'RG_ROW_ORDER',
1412 Token1Xlate => TRUE,
1413 Token2 => 'COMP_NAME',
1414 Token2Val => ComponentName,
1415 Token2Xlate => FALSE);
1416 RG_XFER_UTILS_PKG.display_log(
1417 MsgLevel => G_ML_Full,
1418 MsgName => 'RG_XFER_L_EXIT_ROUTINE_WARNING',
1419 Token1 => 'ROUTINE',
1420 Token1Val => 'copy_row_order');
1421 RETURN(G_Warning);
1422 ELSE
1423 /* Show warning and use the existing id. */
1424 RG_XFER_UTILS_PKG.display_warning(
1425 MsgName => 'RG_XFER_COMP_EXIST',
1426 Token1 => 'COMP_TYPE',
1427 Token1Val => 'RG_ROW_ORDER',
1428 Token1Xlate => TRUE,
1429 Token2 => 'COMP_NAME',
1430 Token2Val => ComponentName,
1431 Token2Xlate => FALSE);
1432 END IF;
1433 END IF;
1434
1435 END IF;
1436
1437 /* Clear the name of the component being copied */
1438 G_RowOrder := NULL;
1439
1440 RG_XFER_UTILS_PKG.display_log(
1441 MsgLevel => G_ML_Full,
1442 MsgName => 'RG_XFER_L_EXIT_ROUTINE',
1443 Token1 => 'ROUTINE',
1444 Token1Val => 'copy_row_order');
1445
1446 /* Return the id to be used for this component */
1447 RETURN(TargetId);
1448 END copy_row_order;
1449
1450
1451 /* Name: copy_display_set
1452 * Desc: Copies the specified display set. Return the id of the copied
1453 * component. If the component already exists in the target db,
1454 * then return the id for the existing component.
1455 *
1456 * Notes: ComponentName MUST be NOT NULL.
1457 *
1458 * History:
1459 * 10/17/95 S Rahman Created.
1460 */
1461 FUNCTION copy_display_set(
1462 ComponentName VARCHAR2,
1463 CheckExistence BOOLEAN DEFAULT TRUE) RETURN NUMBER IS
1464 TargetId NUMBER; /* Target component id */
1465 SourceId NUMBER; /* Source component id */
1466 SQLString VARCHAR2(2000);
1467 RowSetName VARCHAR2(30);
1468 RowSetId NUMBER := G_Error;
1469 ColumnSetName VARCHAR2(30);
1470 ColumnSetId NUMBER := G_Error;
1471 AdjustedName VARCHAR2(60);
1472 BEGIN
1473 RG_XFER_UTILS_PKG.display_log(
1474 MsgLevel => G_ML_Full,
1475 MsgName => 'RG_XFER_L_ENTER_ROUTINE',
1476 Token1 => 'ROUTINE',
1477 Token1Val => 'copy_display_set',
1478 Token2 => 'PARAM1',
1479 Token2Val => ComponentName);
1480
1481 /* Store the name of the component being copied */
1482 G_DisplaySet := ComponentName;
1483
1484 /* Account for single quotes */
1488 IF (CheckExistence) THEN
1485 RG_XFER_UTILS_PKG.copy_adjust_string(AdjustedName, ComponentName);
1486
1487 /* Ensure that the component exists in the source database */
1489 IF (NOT RG_XFER_UTILS_PKG.source_component_exists(
1490 'RG_DISPLAY_SET', ComponentName)) THEN
1491 RG_XFER_UTILS_PKG.display_error(
1492 MsgName => 'RG_XFER_COMP_NOT_EXIST',
1493 Token1 => 'COMP_TYPE',
1494 Token1Val => 'RG_DISPLAY_SET',
1495 Token1Xlate => TRUE,
1496 Token2 => 'COMP_NAME',
1497 Token2Val => ComponentName,
1498 Token2Xlate => FALSE);
1499 RETURN(G_Error);
1500 END IF;
1501 END IF;
1502
1503 /* Check if a component of the same name already exists in target db */
1504 SQLString := 'SELECT report_display_set_id ' ||
1505 'FROM rg_report_display_sets ' ||
1506 'WHERE name = ''' || AdjustedName || '''';
1507 TargetId := RG_XFER_UTILS_PKG.component_exists(SQLString);
1508
1509 IF (TargetId = G_Error) THEN
1510 /* Copy row set, if any */
1511 RowSetName := RG_XFER_UTILS_PKG.get_source_ref_object_name(
1512 'RG_REPORT_DISPLAY_SETS', 'RG_REPORT_AXIS_SETS',
1513 'NAME', ComponentName, 'ROW_SET_ID', 'AXIS_SET_ID');
1514 IF (RowSetName IS NOT NULL) THEN
1515 RG_XFER_UTILS_PKG.display_log(
1516 MsgLevel => G_ML_Normal,
1517 MsgName => 'RG_XFER_L_SUB_COMP_START',
1518 Token1 => 'SUB_COMP_TYPE',
1519 Token1Val => 'RG_ROW_SET',
1520 Token1Xlate => TRUE,
1521 Token2 => 'SUB_COMP_NAME',
1522 Token2Val => RowSetName,
1523 Token2Xlate => FALSE,
1524 Token3 => 'COMP_TYPE',
1525 Token3Val => 'RG_DISPLAY_SET',
1526 Token3Xlate => TRUE,
1527 Token4 => 'COMP_NAME',
1528 Token4Val => G_DisplaySet,
1529 Token4Xlate => FALSE);
1530 RowSetId := copy_axis_set('RG_ROW_SET', RowSetName);
1531 IF ((RowSetId = G_Error) OR (RowSetId = G_Warning)) THEN
1532 /* Error transferrring optional component */
1533 RG_XFER_UTILS_PKG.display_warning(
1534 MsgName => 'RG_XFER_SUB_COMP_FAILURE',
1535 Token1 => 'COMP_TYPE',
1536 Token1Val => 'RG_DISPLAY_SET',
1537 Token1Xlate => TRUE,
1538 Token2 => 'COMP_NAME',
1539 Token2Val => G_DisplaySet,
1540 Token2Xlate => FALSE,
1541 Token3 => 'SUB_COMP_TYPE',
1542 Token3Val => 'RG_ROW_SET',
1543 Token3Xlate => TRUE);
1544 END IF;
1545 END IF;
1546
1547 /* Copy column set, if any */
1548 ColumnSetName := RG_XFER_UTILS_PKG.get_source_ref_object_name(
1549 'RG_REPORT_DISPLAY_SETS', 'RG_REPORT_AXIS_SETS',
1550 'NAME', ComponentName, 'COLUMN_SET_ID', 'AXIS_SET_ID');
1551 IF (ColumnSetName IS NOT NULL) THEN
1552 RG_XFER_UTILS_PKG.display_log(
1553 MsgLevel => G_ML_Normal,
1554 MsgName => 'RG_XFER_L_SUB_COMP_START',
1555 Token1 => 'SUB_COMP_TYPE',
1556 Token1Val => 'RG_COLUMN_SET',
1557 Token1Xlate => TRUE,
1558 Token2 => 'SUB_COMP_NAME',
1559 Token2Val => ColumnSetName,
1560 Token2Xlate => FALSE,
1561 Token3 => 'COMP_TYPE',
1562 Token3Val => 'RG_DISPLAY_SET',
1563 Token3Xlate => TRUE,
1564 Token4 => 'COMP_NAME',
1565 Token4Val => G_DisplaySet,
1566 Token4Xlate => FALSE);
1567 ColumnSetId := copy_axis_set('RG_COLUMN_SET', ColumnSetName);
1568 IF ((ColumnSetId = G_Error) OR (ColumnSetId = G_Warning)) THEN
1569 /* Error transferrring optional component */
1570 RG_XFER_UTILS_PKG.display_warning(
1571 MsgName => 'RG_XFER_SUB_COMP_FAILURE',
1572 Token1 => 'COMP_TYPE',
1573 Token1Val => 'RG_DISPLAY_SET',
1574 Token1Xlate => TRUE,
1575 Token2 => 'COMP_NAME',
1576 Token2Val => G_DisplaySet,
1577 Token2Xlate => FALSE,
1578 Token3 => 'SUB_COMP_TYPE',
1579 Token3Val => 'RG_COLUMN_SET',
1580 Token3Xlate => TRUE);
1581 END IF;
1582 END IF;
1583
1584 /* Insert data into table */
1585 TargetId := RG_XFER_UTILS_PKG.get_new_id('RG_REPORT_DISPLAY_SETS_S');
1586 SourceId := RG_XFER_UTILS_PKG.get_source_id(
1587 'RG_REPORT_DISPLAY_SETS',
1588 'REPORT_DISPLAY_SET_ID',
1589 ComponentName);
1590 SQLString := DisplaySetsString || G_LinkName ||
1591 ' WHERE report_display_set_id = ' || TO_CHAR(SourceId);
1592 RG_XFER_UTILS_PKG.substitute_tokens(
1593 SQLString,
1594 Token1=> ':row_set_id',
1595 Token1Val=> RG_XFER_UTILS_PKG.token_from_id(RowSetId),
1596 Token2=> ':column_set_id',
1597 Token2Val=> RG_XFER_UTILS_PKG.token_from_id(ColumnSetId));
1598 RG_XFER_UTILS_PKG.insert_rows(SQLString, TargetId, UseCOAId=> FALSE);
1599
1600 /* Copy the display set detail records */
1601 copy_display_set_details(SourceId, TargetId);
1602
1603 /* New component - insert into the list of components copied */
1604 RG_XFER_UTILS_PKG.insert_into_list(
1605 DisplaySetList, DisplaySetCount, ComponentName);
1606 RG_XFER_UTILS_PKG.display_log(
1610 Token1Val => 'RG_DISPLAY_SET',
1607 MsgLevel => G_ML_Normal,
1608 MsgName => 'RG_XFER_L_TRANSFERRED',
1609 Token1 => 'COMP_TYPE',
1611 Token1Xlate => TRUE,
1612 Token2 => 'COMP_NAME',
1613 Token2Val => ComponentName,
1614 Token2Xlate => FALSE);
1615
1616 ELSE
1617
1618 /* Component with same name already exists in target db. Check if
1619 * this component was copied by this run. */
1620 IF (RG_XFER_UTILS_PKG.search_list(
1621 DisplaySetList, DisplaySetCount, ComponentName) = G_Error) THEN
1622 /* Component with same name existed before this run. Show warning and
1623 * and use the existing id. */
1624 RG_XFER_UTILS_PKG.display_warning(
1625 MsgName => 'RG_XFER_COMP_EXIST',
1626 Token1 => 'COMP_TYPE',
1627 Token1Val => 'RG_DISPLAY_SET',
1628 Token1Xlate => TRUE,
1629 Token2 => 'COMP_NAME',
1630 Token2Val => ComponentName,
1631 Token2Xlate => FALSE);
1632 END IF;
1633
1634 END IF;
1635
1636 /* Clear the name of the component being copied */
1637 G_DisplaySet := NULL;
1638
1639 RG_XFER_UTILS_PKG.display_log(
1640 MsgLevel => G_ML_Full,
1641 MsgName => 'RG_XFER_L_EXIT_ROUTINE',
1642 Token1 => 'ROUTINE',
1643 Token1Val => 'copy_display_set');
1644
1645 /* Return the id to be used for this component */
1646 RETURN(TargetId);
1647 END copy_display_set;
1648
1649
1650 /* Name: copy_display_set_details
1651 * Desc: Copies the detail records for the specified display set.
1652 * Get all the detail records for the display set from the source
1653 * database and insert them into the target database. We need to
1654 * process one detail record at a time to check for the existence
1655 * of display groups. If a display group does not exist then copy
1656 * the display group from the source database. If there is an error
1657 * copying the display group, then omit the display group from the
1658 * detail record.
1659 *
1660 * History:
1661 * 10/17/95 S Rahman Created.
1662 */
1663 PROCEDURE copy_display_set_details(
1664 SourceDisplaySetId NUMBER,
1665 TargetDisplaySetId NUMBER) IS
1666 CursorId INTEGER;
1667 ExecuteValue INTEGER;
1668 DisplayId NUMBER;
1669 RowGroupId NUMBER;
1670 SQLString VARCHAR2(2000);
1671 ColumnGroupId NUMBER;
1672 DisplayGroupName VARCHAR2(30);
1673 BEGIN
1674 RG_XFER_UTILS_PKG.display_log(
1675 MsgLevel => G_ML_Full,
1676 MsgName => 'RG_XFER_L_ENTER_ROUTINE',
1677 Token1 => 'ROUTINE',
1678 Token1Val => 'copy_display_set_details',
1679 Token2 => 'PARAM1',
1680 Token2Val => TO_CHAR(SourceDisplaySetId),
1681 Token3 => 'PARAM2',
1682 Token3Val => TO_CHAR(TargetDisplaySetId));
1683
1684 /* Get all the detail records for this display set. */
1685 CursorId := DBMS_SQL.open_cursor;
1686 DBMS_SQL.parse(CursorId,
1687 'SELECT report_display_id FROM rg_report_displays@' ||
1688 G_LinkName || ' WHERE report_display_set_id =' ||
1689 TO_CHAR(SourceDisplaySetId),
1690 DBMS_SQL.v7);
1691 DBMS_SQL.define_column(CursorId, 1, DisplayId);
1692 ExecuteValue := DBMS_SQL.execute(CursorId);
1693 LOOP
1694 /* Loop through each detail record, and copy it. */
1695 IF (DBMS_SQL.fetch_rows(CursorId) > 0) THEN
1696 DBMS_SQL.column_value(CursorId, 1, DisplayId);
1697
1698 /* Copy row group, if any */
1699 DisplayGroupName := RG_XFER_UTILS_PKG.get_source_ref_object_name(
1700 'RG_REPORT_DISPLAYS', 'RG_REPORT_DISPLAY_GROUPS',
1701 'REPORT_DISPLAY_ID', TO_CHAR(DisplayId),
1702 'ROW_GROUP_ID', 'REPORT_DISPLAY_GROUP_ID',
1703 CharColumn => FALSE);
1704 IF (DisplayGroupName IS NOT NULL) THEN
1705 RG_XFER_UTILS_PKG.display_log(
1706 MsgLevel => G_ML_Normal,
1707 MsgName => 'RG_XFER_L_SUB_COMP_START',
1708 Token1 => 'SUB_COMP_TYPE',
1709 Token1Val => 'RG_DISPLAY_GROUP',
1710 Token1Xlate => TRUE,
1711 Token2 => 'SUB_COMP_NAME',
1712 Token2Val => DisplayGroupName,
1713 Token2Xlate => FALSE,
1714 Token3 => 'COMP_TYPE',
1715 Token3Val => 'RG_DISPLAY_SET',
1716 Token3Xlate => TRUE,
1717 Token4 => 'COMP_NAME',
1718 Token4Val => G_DisplaySet,
1719 Token4Xlate => FALSE);
1720 RowGroupId := copy_display_group(DisplayGroupName);
1721 IF ((RowGroupId = G_Error) OR (RowGroupId = G_Warning)) THEN
1722 /* Error transferrring optional component */
1723 RG_XFER_UTILS_PKG.display_warning(
1724 MsgName => 'RG_XFER_SUB_COMP_FAILURE',
1725 Token1 => 'COMP_TYPE',
1726 Token1Val => 'RG_DISPLAY_SET',
1727 Token1Xlate => TRUE,
1728 Token2 => 'COMP_NAME',
1729 Token2Val => G_DisplaySet,
1730 Token2Xlate => FALSE,
1731 Token3 => 'SUB_COMP_TYPE',
1732 Token3Val => 'RG_DISPLAY_GROUP',
1733 Token3Xlate => TRUE);
1737 END IF;
1734 END IF;
1735 ELSE
1736 RowGroupId := G_Error;
1738
1739 /* Copy column group, if any */
1740 DisplayGroupName := RG_XFER_UTILS_PKG.get_source_ref_object_name(
1741 'RG_REPORT_DISPLAYS', 'RG_REPORT_DISPLAY_GROUPS',
1742 'REPORT_DISPLAY_ID', TO_CHAR(DisplayId),
1743 'COLUMN_GROUP_ID', 'REPORT_DISPLAY_GROUP_ID',
1744 CharColumn => FALSE);
1745 IF (DisplayGroupName IS NOT NULL) THEN
1746 RG_XFER_UTILS_PKG.display_log(
1747 MsgLevel => G_ML_Normal,
1748 MsgName => 'RG_XFER_L_SUB_COMP_START',
1749 Token1 => 'SUB_COMP_TYPE',
1750 Token1Val => 'RG_DISPLAY_GROUP',
1751 Token1Xlate => TRUE,
1752 Token2 => 'SUB_COMP_NAME',
1753 Token2Val => DisplayGroupName,
1754 Token2Xlate => FALSE,
1755 Token3 => 'COMP_TYPE',
1756 Token3Val => 'RG_DISPLAY_SET',
1757 Token3Xlate => TRUE,
1758 Token4 => 'COMP_NAME',
1759 Token4Val => G_DisplaySet,
1760 Token4Xlate => FALSE);
1761 ColumnGroupId := copy_display_group(DisplayGroupName);
1762 IF ((ColumnGroupId = G_Error) OR (ColumnGroupId = G_Warning)) THEN
1763 /* Error transferrring optional component */
1764 RG_XFER_UTILS_PKG.display_warning(
1765 MsgName => 'RG_XFER_SUB_COMP_FAILURE',
1766 Token1 => 'COMP_TYPE',
1767 Token1Val => 'RG_DISPLAY_SET',
1768 Token1Xlate => TRUE,
1769 Token2 => 'COMP_NAME',
1770 Token2Val => G_DisplaySet,
1771 Token2Xlate => FALSE,
1772 Token3 => 'SUB_COMP_TYPE',
1773 Token3Val => 'RG_DISPLAY_GROUP',
1774 Token3Xlate => TRUE);
1775 END IF;
1776 ELSE
1777 ColumnGroupId := G_Error;
1778 END IF;
1779
1780 /* Insert the row */
1781 SQLString := DisplaysString || G_LinkName ||
1782 ' WHERE report_display_id = ' || TO_CHAR(DisplayId);
1783 RG_XFER_UTILS_PKG.substitute_tokens(
1784 SQLString,
1785 Token1=> ':row_group_id',
1786 Token1Val=> RG_XFER_UTILS_PKG.token_from_id(RowGroupId),
1787 Token2=> ':column_group_id',
1788 Token2Val=> RG_XFER_UTILS_PKG.token_from_id(ColumnGroupId));
1789 RG_XFER_UTILS_PKG.insert_rows(
1790 SQLString, TargetDisplaySetId, UseCOAId=> FALSE);
1791
1792 ELSE
1793 /* No more rows */
1794 EXIT;
1795 END IF;
1796 END LOOP;
1797 DBMS_SQL.close_cursor(CursorId);
1798
1799 RG_XFER_UTILS_PKG.display_log(
1800 MsgLevel => G_ML_Full,
1801 MsgName => 'RG_XFER_L_EXIT_ROUTINE',
1802 Token1 => 'ROUTINE',
1803 Token1Val => 'copy_display_set_details');
1804 END copy_display_set_details;
1805
1806
1807 /* Name: copy_display_group
1808 * Desc: Copies the specified display group. Return the id of the copied
1809 * component. If the component already exists in the target db,
1810 * then return the id for the existing component.
1811 *
1812 * Notes: ComponentName MUST be NOT NULL.
1813 *
1814 * History:
1815 * 10/17/95 S Rahman Created.
1816 */
1817 FUNCTION copy_display_group(
1818 ComponentName VARCHAR2,
1819 CheckExistence BOOLEAN DEFAULT TRUE) RETURN NUMBER IS
1820 TargetId NUMBER; /* Target component id */
1821 SourceId NUMBER; /* Source component id */
1822 SQLString VARCHAR2(2000);
1823 RowSetName VARCHAR2(30);
1824 RowSetId NUMBER := G_Error;
1825 ColumnSetName VARCHAR2(30);
1826 ColumnSetId NUMBER := G_Error;
1827 AdjustedName VARCHAR2(60);
1828 BEGIN
1829 RG_XFER_UTILS_PKG.display_log(
1830 MsgLevel => G_ML_Full,
1831 MsgName => 'RG_XFER_L_ENTER_ROUTINE',
1832 Token1 => 'ROUTINE',
1833 Token1Val => 'copy_display_group',
1834 Token2 => 'PARAM1',
1835 Token2Val => ComponentName);
1836
1837 /* Store the name of the component being copied */
1838 G_DisplayGroup := ComponentName;
1839
1840 /* Account for single quotes */
1841 RG_XFER_UTILS_PKG.copy_adjust_string(AdjustedName, ComponentName);
1842
1843 /* Ensure that the component exists in the source database */
1844 IF (CheckExistence) THEN
1845 IF (NOT RG_XFER_UTILS_PKG.source_component_exists(
1846 'RG_DISPLAY_GROUP', ComponentName)) THEN
1847 RG_XFER_UTILS_PKG.display_error(
1848 MsgName => 'RG_XFER_COMP_NOT_EXIST',
1849 Token1 => 'COMP_TYPE',
1850 Token1Val => 'RG_DISPLAY_GROUP',
1851 Token1Xlate => TRUE,
1852 Token2 => 'COMP_NAME',
1853 Token2Val => ComponentName,
1854 Token2Xlate => FALSE);
1855 RETURN(G_Error);
1856 END IF;
1857 END IF;
1858
1859 /* Check if a component of the same name already exists in target db */
1860 SQLString := 'SELECT report_display_group_id ' ||
1861 'FROM rg_report_display_groups ' ||
1862 'WHERE name = ''' || AdjustedName || '''';
1863 TargetId := RG_XFER_UTILS_PKG.component_exists(SQLString);
1864
1865 IF (TargetId = G_Error) THEN
1869 'NAME', ComponentName, 'ROW_SET_ID', 'AXIS_SET_ID');
1866 /* Copy row set, if any */
1867 RowSetName := RG_XFER_UTILS_PKG.get_source_ref_object_name(
1868 'RG_REPORT_DISPLAY_GROUPS', 'RG_REPORT_AXIS_SETS',
1870 IF (RowSetName IS NOT NULL) THEN
1871 RG_XFER_UTILS_PKG.display_log(
1872 MsgLevel => G_ML_Normal,
1873 MsgName => 'RG_XFER_L_SUB_COMP_START',
1874 Token1 => 'SUB_COMP_TYPE',
1875 Token1Val => 'RG_ROW_SET',
1876 Token1Xlate => TRUE,
1877 Token2 => 'SUB_COMP_NAME',
1878 Token2Val => RowSetName,
1879 Token2Xlate => FALSE,
1880 Token3 => 'COMP_TYPE',
1881 Token3Val => 'RG_DISPLAY_GROUP',
1882 Token3Xlate => TRUE,
1883 Token4 => 'COMP_NAME',
1884 Token4Val => G_DisplayGroup,
1885 Token4Xlate => FALSE);
1886 RowSetId := copy_axis_set('RG_ROW_SET', RowSetName);
1887 IF ((RowSetId = G_Error) OR (RowSetId = G_Warning)) THEN
1888 /* Error transferrring optional component */
1889 RG_XFER_UTILS_PKG.display_warning(
1890 MsgName => 'RG_XFER_SUB_COMP_FAILURE',
1891 Token1 => 'COMP_TYPE',
1892 Token1Val => 'RG_DISPLAY_GROUP',
1893 Token1Xlate => TRUE,
1894 Token2 => 'COMP_NAME',
1895 Token2Val => G_DisplayGroup,
1896 Token2Xlate => FALSE,
1897 Token3 => 'SUB_COMP_TYPE',
1898 Token3Val => 'RG_ROW_SET',
1899 Token3Xlate => TRUE);
1900 END IF;
1901 END IF;
1902
1903 /* Copy column set, if any */
1904 ColumnSetName := RG_XFER_UTILS_PKG.get_source_ref_object_name(
1905 'RG_REPORT_DISPLAY_GROUPS', 'RG_REPORT_AXIS_SETS',
1906 'NAME', ComponentName, 'COLUMN_SET_ID', 'AXIS_SET_ID');
1907 IF (ColumnSetName IS NOT NULL) THEN
1908 RG_XFER_UTILS_PKG.display_log(
1909 MsgLevel => G_ML_Normal,
1910 MsgName => 'RG_XFER_L_SUB_COMP_START',
1911 Token1 => 'SUB_COMP_TYPE',
1912 Token1Val => 'RG_COLUMN_SET',
1913 Token1Xlate => TRUE,
1914 Token2 => 'SUB_COMP_NAME',
1915 Token2Val => ColumnSetName,
1916 Token2Xlate => FALSE,
1917 Token3 => 'COMP_TYPE',
1918 Token3Val => 'RG_DISPLAY_GROUP',
1919 Token3Xlate => TRUE,
1920 Token4 => 'COMP_NAME',
1921 Token4Val => G_DisplayGroup,
1922 Token4Xlate => FALSE);
1923 ColumnSetId := copy_axis_set('RG_COLUMN_SET', ColumnSetName);
1924 IF ((ColumnSetId = G_Error) OR (ColumnSetId = G_Warning)) THEN
1925 /* Error transferrring optional component */
1926 RG_XFER_UTILS_PKG.display_warning(
1927 MsgName => 'RG_XFER_SUB_COMP_FAILURE',
1928 Token1 => 'COMP_TYPE',
1929 Token1Val => 'RG_DISPLAY_GROUP',
1930 Token1Xlate => TRUE,
1931 Token2 => 'COMP_NAME',
1932 Token2Val => G_DisplayGroup,
1933 Token2Xlate => FALSE,
1934 Token3 => 'SUB_COMP_TYPE',
1935 Token3Val => 'RG_COLUMN_SET',
1936 Token3Xlate => TRUE);
1937 END IF;
1938 END IF;
1939
1940 /* Insert data into table */
1941 TargetId := RG_XFER_UTILS_PKG.get_new_id('RG_REPORT_DISPLAY_GROUPS_S');
1942 SourceId := RG_XFER_UTILS_PKG.get_source_id(
1943 'RG_REPORT_DISPLAY_GROUPS',
1944 'REPORT_DISPLAY_GROUP_ID',
1945 ComponentName);
1946 SQLString := DisplayGroupsString || G_LinkName ||
1947 ' WHERE report_display_group_id = '||TO_CHAR(SourceId);
1948 RG_XFER_UTILS_PKG.substitute_tokens(
1949 SQLString,
1950 Token1=> ':row_set_id',
1951 Token1Val=> RG_XFER_UTILS_PKG.token_from_id(RowSetId),
1952 Token2=> ':column_set_id',
1953 Token2Val=> RG_XFER_UTILS_PKG.token_from_id(ColumnSetId));
1954 RG_XFER_UTILS_PKG.insert_rows(SQLString, TargetId, UseCOAId=> FALSE);
1955
1956 /* New component - insert into the list of components copied */
1957 RG_XFER_UTILS_PKG.insert_into_list(
1958 DisplayGroupList, DisplayGroupCount, ComponentName);
1959 RG_XFER_UTILS_PKG.display_log(
1960 MsgLevel => G_ML_Normal,
1961 MsgName => 'RG_XFER_L_TRANSFERRED',
1962 Token1 => 'COMP_TYPE',
1963 Token1Val => 'RG_DISPLAY_GROUP',
1964 Token1Xlate => TRUE,
1965 Token2 => 'COMP_NAME',
1966 Token2Val => ComponentName,
1967 Token2Xlate => FALSE);
1968
1969 ELSE
1970
1971 /* Component with same name already exists in target db. Check if
1972 * this component was copied by this run. */
1973 IF (RG_XFER_UTILS_PKG.search_list(
1974 DisplayGroupList, DisplayGroupCount, ComponentName) = G_Error) THEN
1975 /* Component with same name existed before this run. Show warning and
1976 * and use the existing id. */
1977 RG_XFER_UTILS_PKG.display_warning(
1978 MsgName => 'RG_XFER_COMP_EXIST',
1979 Token1 => 'COMP_TYPE',
1980 Token1Val => 'RG_DISPLAY_GROUP',
1981 Token1Xlate => TRUE,
1982 Token2 => 'COMP_NAME',
1983 Token2Val => ComponentName,
1984 Token2Xlate => FALSE);
1985 END IF;
1986
1987 END IF;
1988
1992 RG_XFER_UTILS_PKG.display_log(
1989 /* Clear the name of the component being copied */
1990 G_DisplayGroup := NULL;
1991
1993 MsgLevel => G_ML_Full,
1994 MsgName => 'RG_XFER_L_EXIT_ROUTINE',
1995 Token1 => 'ROUTINE',
1996 Token1Val => 'copy_display_group');
1997
1998 /* Return the id to be used for this component */
1999 RETURN(TargetId);
2000 END copy_display_group;
2001
2002
2003 /* Name: copy_report
2004 * Desc: Copies the specified report. Return the id of the copied
2005 * component. If the component already exists in the target db,
2006 * then return the id for the existing component.
2007 *
2008 * Copy the sub-components of the report, as necessary. If there
2009 * is an error copying the row or column set, then the report
2010 * copying fails, since these components are required. If there
2011 * is an error copying the content set, row order, or display set,
2012 * or if the currency used does not exist in the target database,
2013 * then omit the information from the report. This is okay since
2014 * the information is not required.
2015 *
2016 * Notes: ComponentName MUST be NOT NULL.
2017 *
2018 * History:
2019 * 10/17/95 S Rahman Created.
2020 * 03/31/03 T Cheng For each record, check if the specified
2021 * segment_override ledger exists. If it doesn't,
2022 * then omit ledger override information.
2023 */
2024 FUNCTION copy_report(
2025 ComponentName VARCHAR2,
2026 CheckExistence BOOLEAN DEFAULT TRUE) RETURN NUMBER IS
2027 TargetId NUMBER; /* Target component id */
2028 SourceId NUMBER; /* Source component id */
2029 SQLString VARCHAR2(3000);
2030 RowSetName VARCHAR2(30);
2031 RowSetId NUMBER := NULL;
2032 ColumnSetName VARCHAR2(30);
2033 ColumnSetId NUMBER := NULL;
2034 RowOrderName VARCHAR2(30);
2035 RowOrderId NUMBER := G_Error;
2036 ContentSetName VARCHAR2(30);
2037 ContentSetId NUMBER := G_Error;
2038 DisplaySetName VARCHAR2(30);
2039 DisplaySetId NUMBER := G_Error;
2040 ParameterSetId NUMBER;
2041 CurrencyCode VARCHAR2(15);
2042 AdjustedName VARCHAR2(60);
2043
2044 SegOverride VARCHAR2(800);
2045 OverrideCurr VARCHAR2(15);
2046 COADelimiter VARCHAR2(1);
2047 FirstDelimiterPos NUMBER;
2048 LedgerId NUMBER;
2049 LedgerName VARCHAR2(30);
2050 ErrorNum NUMBER;
2051 ErrorMsg VARCHAR2(512);
2052 BEGIN
2053 RG_XFER_UTILS_PKG.display_log(
2054 MsgLevel => G_ML_Full,
2055 MsgName => 'RG_XFER_L_ENTER_ROUTINE',
2056 Token1 => 'ROUTINE',
2057 Token1Val => 'copy_report',
2058 Token2 => 'PARAM1',
2059 Token2Val => ComponentName);
2060
2061 /* Store the name of the component being copied */
2062 G_Report := ComponentName;
2063
2064 /* Account for single quotes */
2065 RG_XFER_UTILS_PKG.copy_adjust_string(AdjustedName, ComponentName);
2066
2067 /* Ensure that the component exists in the source database */
2068 IF (CheckExistence) THEN
2069 IF (NOT RG_XFER_UTILS_PKG.source_component_exists(
2070 'RG_REPORT', ComponentName)) THEN
2071 RG_XFER_UTILS_PKG.display_error(
2072 MsgName => 'RG_XFER_COMP_NOT_EXIST',
2073 Token1 => 'COMP_TYPE',
2074 Token1Val => 'RG_REPORT',
2075 Token1Xlate => TRUE,
2076 Token2 => 'COMP_NAME',
2077 Token2Val => ComponentName,
2078 Token2Xlate => FALSE);
2079 RETURN(G_Error);
2080 END IF;
2081 END IF;
2082
2083 /* Ensure that the COA id of the component matches the source COA id */
2084 IF (RG_XFER_UTILS_PKG.check_coa_id(
2085 'RG_REPORTS', ComponentName) = G_Error) THEN
2086 RG_XFER_UTILS_PKG.display_warning(
2087 MsgName => 'RG_XFER_WRONG_COA',
2088 Token1 => 'COMP_TYPE',
2089 Token1Val => 'RG_REPORT',
2090 Token1Xlate => TRUE,
2091 Token2 => 'COMP_NAME',
2092 Token2Val => ComponentName,
2093 Token2Xlate => FALSE);
2094 RG_XFER_UTILS_PKG.display_log(
2095 MsgLevel => G_ML_Full,
2096 MsgName => 'RG_XFER_L_EXIT_ROUTINE_WARN',
2097 Token1 => 'ROUTINE',
2098 Token1Val => 'copy_report');
2099 RETURN(G_Warning);
2100 END IF;
2101
2102 /* Check if a component of the same name already exists in target db */
2103 SQLString := 'SELECT report_id ' ||
2104 'FROM rg_reports ' ||
2105 'WHERE name = ''' || AdjustedName || ''' ' ||
2106 'AND application_id = ' || TO_CHAR(G_ApplId);
2107 TargetId := RG_XFER_UTILS_PKG.component_exists(SQLString);
2108
2109 IF (TargetId = G_Error) THEN
2110 /* Copy row set */
2111 RowSetName := RG_XFER_UTILS_PKG.get_source_ref_object_name(
2112 'RG_REPORTS', 'RG_REPORT_AXIS_SETS',
2113 'NAME', ComponentName, 'ROW_SET_ID', 'AXIS_SET_ID');
2114 IF (RowSetName IS NOT NULL) THEN
2115 RG_XFER_UTILS_PKG.display_log(
2116 MsgLevel => G_ML_Normal,
2117 MsgName => 'RG_XFER_L_SUB_COMP_START',
2118 Token1 => 'SUB_COMP_TYPE',
2119 Token1Val => 'RG_ROW_SET',
2120 Token1Xlate => TRUE,
2121 Token2 => 'SUB_COMP_NAME',
2125 Token3Val => 'RG_REPORT',
2122 Token2Val => RowSetName,
2123 Token2Xlate => FALSE,
2124 Token3 => 'COMP_TYPE',
2126 Token3Xlate => TRUE,
2127 Token4 => 'COMP_NAME',
2128 Token4Val => G_Report,
2129 Token4Xlate => FALSE);
2130 RowSetId := copy_axis_set('RG_ROW_SET', RowSetName);
2131 END IF;
2132 IF ((RowSetId = G_Error) OR (RowSetId = G_Warning)) THEN
2133 /* Error transferrring required component */
2134 RG_XFER_UTILS_PKG.display_error(
2135 MsgName => 'RG_XFER_ABORT',
2136 Token1 => 'COMP_TYPE',
2137 Token1Val => 'RG_REPORT',
2138 Token1Xlate => TRUE,
2139 Token2 => 'COMP_NAME',
2140 Token2Val => G_Report,
2141 Token2Xlate => FALSE,
2142 Token3 => 'SUB_COMP_TYPE',
2143 Token3Val => 'RG_ROW_SET',
2144 Token3Xlate => TRUE);
2145 RG_XFER_UTILS_PKG.display_log(
2146 MsgLevel => G_ML_Full,
2147 MsgName => 'RG_XFER_L_EXIT_ROUTINE_ERROR',
2148 Token1 => 'ROUTINE',
2149 Token1Val => 'copy_report');
2150 RETURN(G_Error);
2151 END IF;
2152
2153 /* Copy column set, if any */
2154 ColumnSetName := RG_XFER_UTILS_PKG.get_source_ref_object_name(
2155 'RG_REPORTS', 'RG_REPORT_AXIS_SETS',
2156 'NAME', ComponentName, 'COLUMN_SET_ID', 'AXIS_SET_ID');
2157 IF (ColumnSetName IS NOT NULL) THEN
2158 RG_XFER_UTILS_PKG.display_log(
2159 MsgLevel => G_ML_Normal,
2160 MsgName => 'RG_XFER_L_SUB_COMP_START',
2161 Token1 => 'SUB_COMP_TYPE',
2162 Token1Val => 'RG_COLUMN_SET',
2163 Token1Xlate => TRUE,
2164 Token2 => 'SUB_COMP_NAME',
2165 Token2Val => ColumnSetName,
2166 Token2Xlate => FALSE,
2167 Token3 => 'COMP_TYPE',
2168 Token3Val => 'RG_REPORT',
2169 Token3Xlate => TRUE,
2170 Token4 => 'COMP_NAME',
2171 Token4Val => G_Report,
2172 Token4Xlate => FALSE);
2173 ColumnSetId := copy_axis_set('RG_COLUMN_SET', ColumnSetName);
2174 END IF;
2175 IF ((ColumnSetId = G_Error) OR (ColumnSetId = G_Warning)) THEN
2176 /* Error transferrring required component */
2177 RG_XFER_UTILS_PKG.display_error(
2178 MsgName => 'RG_XFER_ABORT',
2179 Token1 => 'COMP_TYPE',
2180 Token1Val => 'RG_REPORT',
2181 Token1Xlate => TRUE,
2182 Token2 => 'COMP_NAME',
2183 Token2Val => G_Report,
2184 Token2Xlate => FALSE,
2185 Token3 => 'SUB_COMP_TYPE',
2186 Token3Val => 'RG_COLUMN_SET',
2187 Token3Xlate => TRUE);
2188 RG_XFER_UTILS_PKG.display_log(
2189 MsgLevel => G_ML_Full,
2190 MsgName => 'RG_XFER_L_EXIT_ROUTINE_ERROR',
2191 Token1 => 'ROUTINE',
2192 Token1Val => 'copy_report');
2193 RETURN(G_Error);
2194 END IF;
2195
2196 /* Copy row order, if any */
2197 RowOrderName := RG_XFER_UTILS_PKG.get_source_ref_object_name(
2198 'RG_REPORTS', 'RG_ROW_ORDERS', 'NAME', ComponentName,
2199 'ROW_ORDER_ID', 'ROW_ORDER_ID');
2200 IF (RowOrderName IS NOT NULL) THEN
2201 RG_XFER_UTILS_PKG.display_log(
2202 MsgLevel => G_ML_Normal,
2203 MsgName => 'RG_XFER_L_SUB_COMP_START',
2204 Token1 => 'SUB_COMP_TYPE',
2205 Token1Val => 'RG_ROW_ORDER',
2206 Token1Xlate => TRUE,
2207 Token2 => 'SUB_COMP_NAME',
2208 Token2Val => RowOrderName,
2209 Token2Xlate => FALSE,
2210 Token3 => 'COMP_TYPE',
2211 Token3Val => 'RG_REPORT',
2212 Token3Xlate => TRUE,
2213 Token4 => 'COMP_NAME',
2214 Token4Val => G_Report,
2215 Token4Xlate => FALSE);
2216 RowOrderId := copy_row_order(RowOrderName);
2217 IF ((RowOrderId = G_Error) OR (RowOrderId = G_Warning)) THEN
2218 /* Error transferrring optional component */
2219 RG_XFER_UTILS_PKG.display_warning(
2220 MsgName => 'RG_XFER_SUB_COMP_FAILURE',
2221 Token1 => 'COMP_TYPE',
2222 Token1Val => 'RG_REPORT',
2223 Token1Xlate => TRUE,
2224 Token2 => 'COMP_NAME',
2225 Token2Val => G_Report,
2226 Token2Xlate => FALSE,
2227 Token3 => 'SUB_COMP_TYPE',
2228 Token3Val => 'RG_ROW_ORDER',
2229 Token3Xlate => TRUE);
2230 END IF;
2231 END IF;
2232
2233 /* Copy content set, if any */
2234 ContentSetName := RG_XFER_UTILS_PKG.get_source_ref_object_name(
2235 'RG_REPORTS', 'RG_REPORT_CONTENT_SETS', 'NAME',
2236 ComponentName, 'CONTENT_SET_ID', 'CONTENT_SET_ID');
2237 IF (ContentSetName IS NOT NULL) THEN
2238 RG_XFER_UTILS_PKG.display_log(
2239 MsgLevel => G_ML_Normal,
2240 MsgName => 'RG_XFER_L_SUB_COMP_START',
2241 Token1 => 'SUB_COMP_TYPE',
2242 Token1Val => 'RG_CONTENT_SET',
2243 Token1Xlate => TRUE,
2244 Token2 => 'SUB_COMP_NAME',
2245 Token2Val => ContentSetName,
2246 Token2Xlate => FALSE,
2247 Token3 => 'COMP_TYPE',
2248 Token3Val => 'RG_REPORT',
2249 Token3Xlate => TRUE,
2253 ContentSetId := copy_content_set(ContentSetName);
2250 Token4 => 'COMP_NAME',
2251 Token4Val => G_Report,
2252 Token4Xlate => FALSE);
2254 IF ((ContentSetId = G_Error) OR (ContentSetId = G_Warning)) THEN
2255 /* Error transferrring optional component */
2256 RG_XFER_UTILS_PKG.display_warning(
2257 MsgName => 'RG_XFER_SUB_COMP_FAILURE',
2258 Token1 => 'COMP_TYPE',
2259 Token1Val => 'RG_REPORT',
2260 Token1Xlate => TRUE,
2261 Token2 => 'COMP_NAME',
2262 Token2Val => G_Report,
2263 Token2Xlate => FALSE,
2264 Token3 => 'SUB_COMP_TYPE',
2265 Token3Val => 'RG_CONTENT_SET',
2266 Token3Xlate => TRUE);
2267 END IF;
2268 END IF;
2269
2270 /* Copy display set, if any */
2271 DisplaySetName := RG_XFER_UTILS_PKG.get_source_ref_object_name(
2272 'RG_REPORTS', 'RG_REPORT_DISPLAY_SETS', 'NAME',
2273 ComponentName, 'REPORT_DISPLAY_SET_ID',
2274 'REPORT_DISPLAY_SET_ID');
2275 IF (DisplaySetName IS NOT NULL) THEN
2276 RG_XFER_UTILS_PKG.display_log(
2277 MsgLevel => G_ML_Normal,
2278 MsgName => 'RG_XFER_L_SUB_COMP_START',
2279 Token1 => 'SUB_COMP_TYPE',
2280 Token1Val => 'RG_DISPLAY_SET',
2281 Token1Xlate => TRUE,
2282 Token2 => 'SUB_COMP_NAME',
2283 Token2Val => DisplaySetName,
2284 Token2Xlate => FALSE,
2285 Token3 => 'COMP_TYPE',
2286 Token3Val => 'RG_REPORT',
2287 Token3Xlate => TRUE,
2288 Token4 => 'COMP_NAME',
2289 Token4Val => G_Report,
2290 Token4Xlate => FALSE);
2291 DisplaySetId := copy_display_set(DisplaySetName);
2292 IF ((DisplaySetId = G_Error) OR (DisplaySetId = G_Warning)) THEN
2293 /* Error transferrring optional component */
2294 RG_XFER_UTILS_PKG.display_warning(
2295 MsgName => 'RG_XFER_SUB_COMP_FAILURE',
2296 Token1 => 'COMP_TYPE',
2297 Token1Val => 'RG_REPORT',
2298 Token1Xlate => TRUE,
2299 Token2 => 'COMP_NAME',
2300 Token2Val => G_Report,
2301 Token2Xlate => FALSE,
2302 Token3 => 'SUB_COMP_TYPE',
2303 Token3Val => 'RG_DISPLAY_SET',
2304 Token3Xlate => TRUE);
2305 END IF;
2306 END IF;
2307
2308 SourceId := RG_XFER_UTILS_PKG.get_source_id(
2309 'RG_REPORTS',
2310 'REPORT_ID',
2311 ComponentName,
2312 ' AND application_id = ' || TO_CHAR(G_ApplId));
2313
2314 /* Check ledger id in segment_override */
2315 SegOverride := RG_XFER_UTILS_PKG.get_varchar2(
2316 'SELECT segment_override FROM rg_reports@' ||
2317 G_LinkName || ' WHERE report_id = ' ||
2318 TO_CHAR(SourceId), 800);
2319 OverrideCurr := RG_XFER_UTILS_PKG.get_varchar2(
2320 'SELECT override_alc_ledger_currency FROM rg_reports@' ||
2321 G_LinkName || ' WHERE report_id = ' ||
2322 TO_CHAR(SourceId), 15);
2323
2324 COADelimiter := RG_XFER_UTILS_PKG.get_varchar2(
2325 'SELECT concatenated_segment_delimiter' ||
2326 ' FROM fnd_id_flex_structures@' ||
2327 G_LinkName || ' f, rg_reports@' || G_LinkName || ' r' ||
2328 ' WHERE f.application_id = r.application_id' ||
2329 ' AND f.id_flex_code = r.id_flex_code' ||
2330 ' AND f.id_flex_num = r.structure_id' ||
2331 ' AND report_id = ' || TO_CHAR(SourceId), 1);
2332
2333 FirstDelimiterPos := INSTR(SegOverride, COADelimiter);
2334 LedgerId := TO_NUMBER(SUBSTR(SegOverride, 1, FirstDelimiterPos - 1));
2335 IF (LedgerId IS NOT NULL) THEN
2336 RG_XFER_UTILS_PKG.get_target_ldg_from_source_ldg(
2337 LedgerId, LedgerName, OverrideCurr);
2338 END IF;
2339
2340 IF (LedgerId = G_Error) THEN
2341 /* Error: ledger not present in target db */
2342 RG_XFER_UTILS_PKG.display_warning(
2343 MsgName => 'RG_XFER_SUB_COMP_NOT_EXIST',
2344 Token1 => 'SUB_COMP_TYPE',
2345 Token1Val => 'RG_XFER_LEDGERS',
2346 Token1Xlate => TRUE,
2347 Token2 => 'SUB_COMP_NAME',
2348 Token2Val => LedgerName,
2349 Token2Xlate => FALSE,
2350 Token3 => 'COMP_TYPE',
2351 Token3Val => 'RG_REPORT',
2352 Token3Xlate => TRUE,
2353 Token4 => 'COMP_NAME',
2354 Token4Val => G_Report,
2355 Token4Xlate => FALSE);
2356 SegOverride := '''' || SUBSTR(SegOverride, FirstDelimiterPos) || '''';
2357 ELSE
2358 SegOverride := '''' || TO_CHAR(LedgerId) ||
2359 SUBSTR(SegOverride, FirstDelimiterPos) || '''';
2360 END IF;
2361
2362 /* Check currency */
2363 CurrencyCode := RG_XFER_UTILS_PKG.get_varchar2(
2364 'SELECT unit_of_measure_id FROM rg_reports@'||
2365 G_LinkName || ' WHERE report_id='||TO_CHAR(SourceId),
2366 15);
2367 IF (CurrencyCode IS NOT NULL) THEN
2371 MsgName => 'RG_XFER_SUB_COMP_NOT_EXIST',
2368 IF (NOT RG_XFER_UTILS_PKG.currency_exists(CurrencyCode)) THEN
2369 /* Warning: currency not defined in target database */
2370 RG_XFER_UTILS_PKG.display_warning(
2372 Token1 => 'SUB_COMP_TYPE',
2373 Token1Val => 'RG_XFER_CURRENCY',
2374 Token1Xlate => TRUE,
2375 Token2 => 'SUB_COMP_NAME',
2376 Token2Val => CurrencyCode,
2377 Token2Xlate => FALSE,
2378 Token3 => 'COMP_TYPE',
2379 Token3Val => 'RG_REPORT',
2380 Token3Xlate => TRUE,
2381 Token4 => 'COMP_NAME',
2382 Token4Val => G_Report,
2383 Token4Xlate => FALSE);
2384 CurrencyCode := 'NULL';
2385 ELSE
2386 /* add the single quotes to the currency code */
2387 CurrencyCode := '''' || CurrencyCode || '''';
2388 END IF;
2389 ELSE
2390 CurrencyCode := 'NULL';
2391 END IF;
2392
2393 ParameterSetId := copy_report_details(SourceId);
2394 IF ((ParameterSetId = G_Error) OR (ParameterSetId = G_Warning)) THEN
2395 /* Error transferrring optional component */
2396 RG_XFER_UTILS_PKG.display_warning(
2397 MsgName => 'RG_XFER_SUB_COMP_FAILURE',
2398 Token1 => 'COMP_TYPE',
2399 Token1Val => 'RG_REPORT',
2400 Token1Xlate => TRUE,
2401 Token2 => 'COMP_NAME',
2402 Token2Val => G_Report,
2403 Token2Xlate => FALSE,
2404 Token3 => 'SUB_COMP_TYPE',
2405 Token3Val => 'RG_XFER_PARAMETER_SET',
2406 Token3Xlate => TRUE);
2407 END IF;
2408
2409 /* Insert data into table */
2410 TargetId := RG_XFER_UTILS_PKG.get_new_id('RG_REPORTS_S');
2411 SQLString := ReportsString || G_LinkName ||
2412 ' WHERE report_id = ' ||TO_CHAR(SourceId);
2413 RG_XFER_UTILS_PKG.substitute_tokens(
2414 SQLString,
2415 Token1=> ':row_set_id',
2416 Token1Val=> TO_CHAR(RowSetId),
2417 Token2=> ':column_set_id',
2418 Token2Val=> TO_CHAR(ColumnSetId),
2419 Token3=> ':content_set_id',
2420 Token3Val=> RG_XFER_UTILS_PKG.token_from_id(ContentSetId),
2421 Token4=> ':row_order_id',
2422 Token4Val=> RG_XFER_UTILS_PKG.token_from_id(RowOrderId),
2423 Token5=> ':parameter_set_id',
2424 Token5Val=> RG_XFER_UTILS_PKG.token_from_id(ParameterSetId),
2425 Token6=> ':currency_code',
2426 Token6Val=> CurrencyCode,
2427 Token7=> ':segment_override',
2428 Token7Val=> SegOverride,
2429 Token8=> ':override_alc_ledger_currency',
2430 Token8Val=> OverrideCurr,
2431 Token9=> ':display_set_id',
2432 Token9Val=> RG_XFER_UTILS_PKG.token_from_id(DisplaySetId)
2433 );
2434
2435 RG_XFER_UTILS_PKG.insert_rows(SQLString, TargetId, UseCOAId=> TRUE);
2436
2437 /* New component - insert into the list of components copied */
2438 RG_XFER_UTILS_PKG.insert_into_list(
2439 ReportList, ReportCount, ComponentName);
2440 RG_XFER_UTILS_PKG.display_log(
2441 MsgLevel => G_ML_Normal,
2442 MsgName => 'RG_XFER_L_TRANSFERRED',
2443 Token1 => 'COMP_TYPE',
2444 Token1Val => 'RG_REPORT',
2445 Token1Xlate => TRUE,
2446 Token2 => 'COMP_NAME',
2447 Token2Val => ComponentName,
2448 Token2Xlate => FALSE);
2449
2450 ELSE
2451
2452 /* Component with same name already exists in target db. Check if
2453 * this component was copied by this run. */
2454 IF (RG_XFER_UTILS_PKG.search_list(
2455 ReportList, ReportCount, ComponentName) = G_Error) THEN
2456 /* Component with same name existed before this run. */
2457
2458 /* Check if it uses the correct chart of accounts id. */
2459 IF (RG_XFER_UTILS_PKG.check_target_coa_id(
2460 'RG_REPORTS', ComponentName) = G_Error) THEN
2461 RG_XFER_UTILS_PKG.display_warning(
2462 MsgName => 'RG_XFER_TARGET_COA_MISMATCH',
2463 Token1 => 'COMP_TYPE',
2464 Token1Val => 'RG_REPORT',
2465 Token1Xlate => TRUE,
2466 Token2 => 'COMP_NAME',
2467 Token2Val => ComponentName,
2468 Token2Xlate => FALSE);
2469 RG_XFER_UTILS_PKG.display_log(
2470 MsgLevel => G_ML_Full,
2471 MsgName => 'RG_XFER_L_EXIT_ROUTINE_WARNING',
2472 Token1 => 'ROUTINE',
2473 Token1Val => 'copy_report');
2474 RETURN(G_Warning);
2475 ELSE
2476 /* Show warning and use the existing id. */
2477 RG_XFER_UTILS_PKG.display_warning(
2478 MsgName => 'RG_XFER_COMP_EXIST',
2479 Token1 => 'COMP_TYPE',
2480 Token1Val => 'RG_REPORT',
2481 Token1Xlate => TRUE,
2482 Token2 => 'COMP_NAME',
2483 Token2Val => ComponentName,
2484 Token2Xlate => FALSE);
2485 END IF;
2486 END IF;
2487
2488 END IF;
2489
2490 /* Clear the name of the component being copied */
2491 G_Report := NULL;
2492
2493 RG_XFER_UTILS_PKG.display_log(
2494 MsgLevel => G_ML_Full,
2495 MsgName => 'RG_XFER_L_EXIT_ROUTINE',
2496 Token1 => 'ROUTINE',
2497 Token1Val => 'copy_report');
2498
2499 /* Return the id to be used for this component */
2500 RETURN(TargetId);
2501
2505 /* Display the exception if MsgLevel is at least Normal */
2502 /* This exception is added under the Bug#3843014 */
2503 EXCEPTION
2504 WHEN OTHERS THEN
2506 ErrorNum := SQLCODE;
2507 ErrorMsg := SUBSTRB(SQLERRM, 1, 512);
2508 RG_XFER_UTILS_PKG.display_exception(ErrorNum, ErrorMsg);
2509
2510 /* Error transferrring required component */
2511 RG_XFER_UTILS_PKG.display_error(
2512 MsgName => 'RG_XFER_ABORT',
2513 Token1 => 'COMP_TYPE',
2514 Token1Val => 'RG_REPORT',
2515 Token1Xlate => TRUE,
2516 Token2 => 'COMP_NAME',
2517 Token2Val => G_Report,
2518 Token2Xlate => FALSE,
2519 Token3 => 'SUB_COMP_TYPE',
2520 Token3Val => 'RG_ROW_SET',
2521 Token3Xlate => TRUE);
2522 RG_XFER_UTILS_PKG.display_log(
2523 MsgLevel => G_ML_Full,
2524 MsgName => 'RG_XFER_L_EXIT_ROUTINE_ERROR',
2525 Token1 => 'ROUTINE',
2526 Token1Val => 'copy_report');
2527 RETURN(G_Error);
2528 END copy_report;
2529
2530
2531 /* Name: copy_report_details
2532 * Desc: Copies the parameters of the report and returns the new
2533 * parameter set id. If a parameter doesn't exist in the target
2534 * database, then that whole record is skipped.
2535 *
2536 * History:
2537 * 10/17/95 S Rahman Created.
2538 */
2539 FUNCTION copy_report_details(ReportId NUMBER) RETURN NUMBER IS
2540 CursorId INTEGER;
2541 ExecuteValue INTEGER;
2542 DataType VARCHAR2(1);
2543 SQLString VARCHAR2(2000);
2544 ParameterId NUMBER;
2545 ParameterNum NUMBER;
2546 ParameterName VARCHAR2(100);
2547 EnteredCurrency VARCHAR2(15);
2548 LedgerCurrency VARCHAR2(15);
2549 RowsFound BOOLEAN := TRUE;
2550 TargetParameterSetId NUMBER;
2551 SourceParameterSetId NUMBER;
2552 BEGIN
2553 RG_XFER_UTILS_PKG.display_log(
2554 MsgLevel => G_ML_Full,
2555 MsgName => 'RG_XFER_L_ENTER_ROUTINE',
2556 Token1 => 'ROUTINE',
2557 Token1Val => 'copy_report_details',
2558 Token2 => 'PARAM1',
2559 Token2Val => TO_CHAR(ReportId));
2560
2561 /* Get a new parameter set id */
2562 TargetParameterSetId := RG_XFER_UTILS_PKG.get_new_id(
2563 'RG_REPORT_PARAMETERS_S');
2564
2565 /* Get all the parameters for the report */
2566 CursorId := DBMS_SQL.open_cursor;
2567 DBMS_SQL.parse(CursorId,
2568 'SELECT par.data_type, par.parameter_id, ' ||
2569 'par.parameter_set_id, par.entered_currency, '||
2570 'par.ledger_currency, ' ||
2571 'par.parameter_num FROM rg_report_parameters@' ||
2572 G_LinkName || ' par, rg_reports@' || G_LinkName ||
2573 ' rp WHERE par.parameter_set_id = rp.parameter_set_id '||
2574 'AND rp.report_id =' || TO_CHAR(ReportId),
2575 DBMS_SQL.v7);
2576 DBMS_SQL.define_column(CursorId, 1, DataType, 1);
2577 DBMS_SQL.define_column(CursorId, 2, ParameterId);
2578 DBMS_SQL.define_column(CursorId, 3, SourceParameterSetId);
2579 DBMS_SQL.define_column(CursorId, 4, EnteredCurrency, 15);
2580 DBMS_SQL.define_column(CursorId, 5, LedgerCurrency, 15);
2581 DBMS_SQL.define_column(CursorId, 6, ParameterNum);
2582 ExecuteValue := DBMS_SQL.execute(CursorId);
2583 LOOP
2584 /* For each record, check if the parameters exist in the target database.
2585 * If a parameter does not exist, then skip the record. */
2586 IF (DBMS_SQL.fetch_rows(CursorId) > 0) THEN
2587 DBMS_SQL.column_value(CursorId, 1, DataType);
2588 DBMS_SQL.column_value(CursorId, 2, ParameterId);
2589 DBMS_SQL.column_value(CursorId, 3, SourceParameterSetId);
2590 DBMS_SQL.column_value(CursorId, 4, EnteredCurrency);
2591 DBMS_SQL.column_value(CursorId, 5, LedgerCurrency);
2592 DBMS_SQL.column_value(CursorId, 6, ParameterNum);
2593
2594 IF (DataType = 'B') THEN
2595 /* Check budget */
2596 RG_XFER_UTILS_PKG.get_target_id_from_source_id(
2597 'GL_BUDGET_VERSIONS',
2598 'BUDGET_NAME',
2599 'BUDGET_VERSION_ID',
2600 ParameterId,
2601 ParameterName);
2602 ELSIF (DataType = 'E') THEN
2603 /* Check encumbrance type */
2604 RG_XFER_UTILS_PKG.get_target_id_from_source_id(
2605 'GL_ENCUMBRANCE_TYPES',
2606 'ENCUMBRANCE_TYPE',
2607 'ENCUMBRANCE_TYPE_ID',
2608 ParameterId,
2609 ParameterName);
2610 ELSIF (DataType = 'C') THEN
2611 /* Don't need to check anything for the currency here. The check is
2612 * done in the next step. */
2613 NULL;
2614 ELSIF (DataType = 'I') THEN
2615 /* Nothing to check for constant period of interest parameters */
2616 NULL;
2617 ELSE
2618 RG_XFER_UTILS_PKG.display_warning('Invalid data_type value in ' ||
2619 'table rg_report_parameters!');
2620 END IF;
2621
2622 IF ((DataType = 'B') AND
2623 (ParameterId = G_Error)) THEN
2624 /* Error: budget not present in target db */
2625 RG_XFER_UTILS_PKG.display_warning(
2626 MsgName => 'RG_XFER_SUB_COMP_NOT_EXIST',
2630 Token2 => 'SUB_COMP_NAME',
2627 Token1 => 'SUB_COMP_TYPE',
2628 Token1Val => 'RG_XFER_BUDGET',
2629 Token1Xlate => TRUE,
2631 Token2Val => ParameterName,
2632 Token2Xlate => FALSE,
2633 Token3 => 'COMP_TYPE',
2634 Token3Val => 'RG_REPORT',
2635 Token3Xlate => TRUE,
2636 Token4 => 'COMP_NAME',
2637 Token4Val => G_Report,
2638 Token4Xlate => FALSE);
2639 ELSIF ((DataType = 'E') AND
2640 (ParameterId = G_Error)) THEN
2641 /* Error: encumbrance type not present in target db */
2642 RG_XFER_UTILS_PKG.display_warning(
2643 MsgName => 'RG_XFER_SUB_COMP_NOT_EXIST',
2644 Token1 => 'SUB_COMP_TYPE',
2645 Token1Val => 'RG_XFER_ENCUMBRANCE_TYPE',
2646 Token1Xlate => TRUE,
2647 Token2 => 'SUB_COMP_NAME',
2648 Token2Val => ParameterName,
2649 Token2Xlate => FALSE,
2650 Token3 => 'COMP_TYPE',
2651 Token3Val => 'RG_REPORT',
2652 Token3Xlate => TRUE,
2653 Token4 => 'COMP_NAME',
2654 Token4Val => G_Report,
2655 Token4Xlate => FALSE);
2656 ELSIF ((DataType = 'C') AND (EnteredCurrency IS NOT NULL) AND
2657 (NOT RG_XFER_UTILS_PKG.currency_exists(EnteredCurrency))) THEN
2658 /* Error: entered currency code not present in target db */
2659 RG_XFER_UTILS_PKG.display_warning(
2660 MsgName => 'RG_XFER_SUB_COMP_NOT_EXIST',
2661 Token1 => 'SUB_COMP_TYPE',
2662 Token1Val => 'RG_XFER_CURRENCY',
2663 Token1Xlate => TRUE,
2664 Token2 => 'SUB_COMP_NAME',
2665 Token2Val => EnteredCurrency,
2666 Token2Xlate => FALSE,
2667 Token3 => 'COMP_TYPE',
2668 Token3Val => 'RG_REPORT',
2669 Token3Xlate => TRUE,
2670 Token4 => 'COMP_NAME',
2671 Token4Val => G_Report,
2672 Token4Xlate => FALSE);
2673 ELSIF ((DataType = 'C') AND (LedgerCurrency IS NOT NULL) AND
2674 (NOT RG_XFER_UTILS_PKG.currency_exists(LedgerCurrency))) THEN
2675 /* Error: ledger currency code not present in target db */
2676 RG_XFER_UTILS_PKG.display_warning(
2677 MsgName => 'RG_XFER_SUB_COMP_NOT_EXIST',
2678 Token1 => 'SUB_COMP_TYPE',
2679 Token1Val => 'RG_XFER_CURRENCY',
2680 Token1Xlate => TRUE,
2681 Token2 => 'SUB_COMP_NAME',
2682 Token2Val => LedgerCurrency,
2683 Token2Xlate => FALSE,
2684 Token3 => 'COMP_TYPE',
2685 Token3Val => 'RG_REPORT',
2686 Token3Xlate => TRUE,
2687 Token4 => 'COMP_NAME',
2688 Token4Val => G_Report,
2689 Token4Xlate => FALSE);
2690 ELSE
2691 /* Insert the row */
2692 SQLString := ReportParametersString || G_LinkName ||
2693 ' WHERE parameter_set_id = ' ||
2694 TO_CHAR(SourceParameterSetId) ||
2695 ' AND data_type = ''' || DataType || '''' ||
2696 ' AND parameter_num = ' || TO_CHAR(ParameterNum);
2697 RG_XFER_UTILS_PKG.substitute_tokens(
2698 SQLString,
2699 Token1=> ':parameter_id',
2700 Token1Val=> TO_CHAR(ParameterId));
2701 RG_XFER_UTILS_PKG.insert_rows(
2702 SQLString, TargetParameterSetId, UseCOAId=> FALSE);
2703 END IF;
2704
2705 ELSE
2706 /* No more rows */
2707 RowsFound := FALSE;
2708 EXIT;
2709 END IF;
2710 END LOOP;
2711 DBMS_SQL.close_cursor(CursorId);
2712
2713 RG_XFER_UTILS_PKG.display_log(
2714 MsgLevel => G_ML_Full,
2715 MsgName => 'RG_XFER_L_EXIT_ROUTINE',
2716 Token1 => 'ROUTINE',
2717 Token1Val => 'copy_report_details');
2718
2719 /* Return the parameter set id */
2720 RETURN(TargetParameterSetId);
2721 END copy_report_details;
2722
2723
2724 /* Name: copy_report_set
2725 * Desc: Copies the specified report set. Return the id of the copied
2726 * component. If the component already exists in the target db,
2727 * then return the id for the existing component.
2728 *
2729 * Notes: ComponentName MUST be NOT NULL.
2730 *
2731 * History:
2732 * 10/17/95 S Rahman Created.
2733 */
2734 FUNCTION copy_report_set(
2735 ComponentName VARCHAR2,
2736 CheckExistence BOOLEAN DEFAULT TRUE) RETURN NUMBER IS
2737 TargetId NUMBER; /* Target component id */
2738 SourceId NUMBER; /* Source component id */
2739 SQLString VARCHAR2(2000);
2740 AdjustedName VARCHAR2(60);
2741 BEGIN
2742 RG_XFER_UTILS_PKG.display_log(
2743 MsgLevel => G_ML_Full,
2744 MsgName => 'RG_XFER_L_ENTER_ROUTINE',
2745 Token1 => 'ROUTINE',
2746 Token1Val => 'copy_report_set',
2747 Token2 => 'PARAM1',
2748 Token2Val => ComponentName);
2749
2750 /* Store the name of the component being copied */
2751 G_ReportSet := ComponentName;
2752
2753 /* Account for single quotes */
2754 RG_XFER_UTILS_PKG.copy_adjust_string(AdjustedName, ComponentName);
2755
2759 'RG_REPORT_SET', ComponentName)) THEN
2756 /* Ensure that the component exists in the source database */
2757 IF (CheckExistence) THEN
2758 IF (NOT RG_XFER_UTILS_PKG.source_component_exists(
2760 RG_XFER_UTILS_PKG.display_error(
2761 MsgName => 'RG_XFER_COMP_NOT_EXIST',
2762 Token1 => 'COMP_TYPE',
2763 Token1Val => 'RG_REPORT_SET',
2764 Token1Xlate => TRUE,
2765 Token2 => 'COMP_NAME',
2766 Token2Val => ComponentName,
2767 Token2Xlate => FALSE);
2768 RETURN(G_Error);
2769 END IF;
2770 END IF;
2771
2772 /* Ensure that the COA id of the component matches the source COA id */
2773 IF (RG_XFER_UTILS_PKG.check_coa_id(
2774 'RG_REPORT_SETS', ComponentName) = G_Error) THEN
2775 RG_XFER_UTILS_PKG.display_warning(
2776 MsgName => 'RG_XFER_WRONG_COA',
2777 Token1 => 'COMP_TYPE',
2778 Token1Val => 'RG_REPORT_SET',
2779 Token1Xlate => TRUE,
2780 Token2 => 'COMP_NAME',
2781 Token2Val => ComponentName,
2782 Token2Xlate => FALSE);
2783 RG_XFER_UTILS_PKG.display_log(
2784 MsgLevel => G_ML_Full,
2785 MsgName => 'RG_XFER_L_EXIT_ROUTINE_WARN',
2786 Token1 => 'ROUTINE',
2787 Token1Val => 'copy_report_set');
2788 RETURN(G_Warning);
2789 END IF;
2790
2791 /* Check if a component of the same name already exists in target db */
2792 SQLString := 'SELECT report_set_id ' ||
2793 'FROM rg_report_sets ' ||
2794 'WHERE name = ''' || AdjustedName || '''' ||
2795 'AND application_id = ' || TO_CHAR(G_ApplId);
2796 TargetId := RG_XFER_UTILS_PKG.component_exists(SQLString);
2797
2798 IF (TargetId = G_Error) THEN
2799 /* Insert data into table */
2800 TargetId := RG_XFER_UTILS_PKG.get_new_id('RG_REPORT_SETS_S');
2801 SourceId := RG_XFER_UTILS_PKG.get_source_id(
2802 'RG_REPORT_SETS',
2803 'REPORT_SET_ID',
2804 ComponentName,
2805 ' AND application_id = ' || TO_CHAR(G_ApplId));
2806 RG_XFER_UTILS_PKG.insert_rows(
2807 ReportSetsString || G_LinkName ||
2808 ' WHERE report_set_id = ' || TO_CHAR(SourceId),
2809 TargetId, UseCOAId=> TRUE);
2810
2811 copy_report_set_details(SourceId, TargetId);
2812
2813 /* New component - insert into the list of components copied */
2814 RG_XFER_UTILS_PKG.insert_into_list(
2815 ReportSetList, ReportSetCount, ComponentName);
2816 RG_XFER_UTILS_PKG.display_log(
2817 MsgLevel => G_ML_Normal,
2818 MsgName => 'RG_XFER_L_TRANSFERRED',
2819 Token1 => 'COMP_TYPE',
2820 Token1Val => 'RG_REPORT_SET',
2821 Token1Xlate => TRUE,
2822 Token2 => 'COMP_NAME',
2823 Token2Val => ComponentName,
2824 Token2Xlate => FALSE);
2825
2826 ELSE
2827
2828 /* Component with same name already exists in target db. Check if
2829 * this component was copied by this run. */
2830 IF (RG_XFER_UTILS_PKG.search_list(
2831 ReportSetList, ReportSetCount, ComponentName) = G_Error) THEN
2832 /* Component with same name existed before this run. Show warning and
2833 * and use the existing id. */
2834 RG_XFER_UTILS_PKG.display_warning(
2835 MsgName => 'RG_XFER_COMP_EXIST',
2836 Token1 => 'COMP_TYPE',
2837 Token1Val => 'RG_REPORT_SET',
2838 Token1Xlate => TRUE,
2839 Token2 => 'COMP_NAME',
2840 Token2Val => ComponentName,
2841 Token2Xlate => FALSE);
2842 END IF;
2843
2844 END IF;
2845
2846 /* Clear the name of the component being copied */
2847 G_ReportSet := NULL;
2848
2849 RG_XFER_UTILS_PKG.display_log(
2850 MsgLevel => G_ML_Full,
2851 MsgName => 'RG_XFER_L_EXIT_ROUTINE',
2852 Token1 => 'ROUTINE',
2853 Token1Val => 'copy_report_set');
2854
2855 /* Return the id to be used for this component */
2856 RETURN(TargetId);
2857 END copy_report_set;
2858
2859
2860 /* Name: copy_report_set_details
2861 * Desc: Copies the reports that are in the report set. Each report is
2862 * copied to the target database, if it doesn't exist already. If
2863 * there is an error copying a report, the record is skipped.
2864 *
2865 * History:
2866 * 10/17/95 S Rahman Created.
2867 */
2868 PROCEDURE copy_report_set_details(
2869 SourceReportSetId NUMBER,
2870 TargetReportSetId NUMBER) IS
2871 CursorId INTEGER;
2872 ExecuteValue INTEGER;
2873 ReportId NUMBER := NULL;
2874 ReportName VARCHAR2(30) := NULL;
2875 SQLString VARCHAR2(3000);
2876 ReportRequestId NUMBER;
2877 BEGIN
2878 RG_XFER_UTILS_PKG.display_log(
2879 MsgLevel => G_ML_Full,
2880 MsgName => 'RG_XFER_L_ENTER_ROUTINE',
2881 Token1 => 'ROUTINE',
2882 Token1Val => 'copy_report_set_details',
2883 Token2 => 'PARAM1',
2884 Token2Val => TO_CHAR(SourceReportSetId),
2885 Token3 => 'PARAM2',
2886 Token3Val => TO_CHAR(TargetReportSetId));
2887
2888 /* Get all the reports for the report set */
2889 CursorId := DBMS_SQL.open_cursor;
2890 DBMS_SQL.parse(CursorId,
2891 'SELECT report_request_id FROM rg_report_requests@' ||
2892 G_LinkName || ' WHERE report_set_id =' ||
2893 TO_CHAR(SourceReportSetId),
2894 DBMS_SQL.v7);
2898 /* For each record, copy the report, if it is not there already.
2895 DBMS_SQL.define_column(CursorId, 1, ReportRequestId);
2896 ExecuteValue := DBMS_SQL.execute(CursorId);
2897 LOOP
2899 * If there is an error while copying the report, then skip the
2900 * record. */
2901 IF (DBMS_SQL.fetch_rows(CursorId) > 0) THEN
2902 DBMS_SQL.column_value(CursorId, 1, ReportRequestId);
2903 /* Copy report */
2904 ReportName := RG_XFER_UTILS_PKG.get_source_ref_object_name(
2905 'RG_REPORT_REQUESTS', 'RG_REPORTS',
2906 'REPORT_REQUEST_ID', TO_CHAR(ReportRequestId),
2907 'REPORT_ID', 'REPORT_ID',
2908 CharColumn => FALSE);
2909
2910 IF (ReportName IS NOT NULL) THEN
2911 RG_XFER_UTILS_PKG.display_log(
2912 MsgLevel => G_ML_Normal,
2913 MsgName => 'RG_XFER_L_SUB_COMP_START',
2914 Token1 => 'SUB_COMP_TYPE',
2915 Token1Val => 'RG_REPORT',
2916 Token1Xlate => TRUE,
2917 Token2 => 'SUB_COMP_NAME',
2918 Token2Val => ReportName,
2919 Token2Xlate => FALSE,
2920 Token3 => 'COMP_TYPE',
2921 Token3Val => 'RG_REPORT_SET',
2922 Token3Xlate => TRUE,
2923 Token4 => 'COMP_NAME',
2924 Token4Val => G_ReportSet,
2925 Token4Xlate => FALSE);
2926 ReportId := copy_report(ReportName);
2927 END IF;
2928
2929 IF ((ReportId = G_Error) OR (ReportId = G_Warning)) THEN
2930 /* Display warning message. */
2931 RG_XFER_UTILS_PKG.display_warning(
2932 MsgName => 'RG_XFER_SUB_COMP_FAILURE',
2933 Token1 => 'COMP_TYPE',
2934 Token1Val => 'RG_REPORT_SET',
2935 Token1Xlate => TRUE,
2936 Token2 => 'COMP_NAME',
2937 Token2Val => G_ReportSet,
2938 Token2Xlate => FALSE,
2939 Token3 => 'SUB_COMP_TYPE',
2940 Token3Val => 'RG_REPORT',
2941 Token3Xlate => TRUE);
2942 ELSE
2943 /* Insert the row */
2944 SQLString := ReportRequestsString || G_LinkName ||
2945 ' WHERE report_request_id = '||TO_CHAR(ReportRequestId);
2946 RG_XFER_UTILS_PKG.substitute_tokens(
2947 SQLString,
2948 Token1=> ':report_id',
2949 Token1Val=> TO_CHAR(ReportId));
2950 RG_XFER_UTILS_PKG.insert_rows(
2951 SQLString, TargetReportSetId, UseCOAId=>TRUE);
2952 END IF;
2953 ELSE
2954 /* No more rows */
2955 EXIT;
2956 END IF;
2957 END LOOP;
2958 DBMS_SQL.close_cursor(CursorId);
2959
2960 RG_XFER_UTILS_PKG.display_log(
2961 MsgLevel => G_ML_Full,
2962 MsgName => 'RG_XFER_L_EXIT_ROUTINE',
2963 Token1 => 'ROUTINE',
2964 Token1Val => 'copy_report_set_details');
2965 END copy_report_set_details;
2966
2967 /* Name: transfer_taxonomy
2968 * Desc: Copies the taxonomies that are used in the axis set. Each taxonomy is
2969 * copied to the target database, if it doesn't exist already.
2970 *
2971 * History:
2972 * 04/08/03 V Treiger Created.
2973 */
2974 PROCEDURE transfer_taxonomy(
2975 parent_tax_alias IN VARCHAR2,
2976 parent_tax_id IN NUMBER,
2977 parent_done_flag IN OUT NOCOPY NUMBER) IS
2978
2979 TYPE alias_tabtype IS TABLE OF VARCHAR2(240)
2980 INDEX BY BINARY_INTEGER;
2981 TYPE id_tabtype IS TABLE OF NUMBER
2982 INDEX BY BINARY_INTEGER;
2983 --
2984 cnt INTEGER;
2985 child_cnt INTEGER;
2986 arr_cnt INTEGER;
2987 --
2988 arr_child_tax_alias alias_tabtype;
2989 arr_child_tax_id id_tabtype;
2990 arr_child_done_flag id_tabtype;
2991 --
2992 cur_tax_alias VARCHAR2(240);
2993 cur_tax_id NUMBER;
2994 cur_done_flag NUMBER;
2995 l_child_tax_id NUMBER;
2996 --
2997 src_element_identifier VARCHAR2(240);
2998 src_element_name VARCHAR2(240);
2999 src_element_type VARCHAR2(240);
3000 src_element_group VARCHAR2(240);
3001 src_element_descr VARCHAR2(3000);
3002 src_element_label VARCHAR2(240);
3003 src_parent_identifier VARCHAR2(240);
3004 src_has_child_flag VARCHAR2(1);
3005 src_has_parent_flag VARCHAR2(1);
3006 src_hierarchy_level NUMBER;
3007 cur_elem_id NUMBER;
3008 --
3009 src_tax_id NUMBER;
3010 src_tax_alias VARCHAR2(240);
3011 dest_taxonomy_id NUMBER;
3012 TargetId NUMBER;
3013 --
3014 CursorId INTEGER;
3015 ExecuteValue INTEGER;
3016 SQLString VARCHAR2(3000);
3017 ValueString VARCHAR2(240);
3018 TempValue VARCHAR2(100);
3019 --
3020 l_user_id NUMBER;
3021 l_login_id NUMBER;
3022 --
3023 BEGIN
3024 --
3025 l_user_id := FND_GLOBAL.User_Id;
3026 l_login_id := FND_GLOBAL.Login_Id;
3027 --
3028 /* Check if parent taxonomy of the same alias already exists in target db */
3029 RG_XFER_UTILS_PKG.copy_adjust_string(TempValue, parent_tax_alias);
3030 ValueString := '''' || TempValue || '''';
3031
3032 SQLString := 'SELECT taxonomy_id ' ||
3033 'FROM rg_xbrl_taxonomies ' ||
3034 'WHERE taxonomy_alias = ' || ValueString;
3035 TargetId := RG_XFER_UTILS_PKG.component_exists(SQLString);
3036
3037 IF (TargetId <> G_Error) THEN
3041 END IF;
3038 /* parent taxonomy exists in target db */
3039 parent_done_flag := 1;
3040 RETURN;
3042
3043 /* build all childs of the parent taxonomy */
3044 SQLString := 'SELECT DISTINCT tax.taxonomy_id,tax.taxonomy_alias '||
3045 'FROM RG_XBRL_TAXONOMIES'||'@'|| G_LinkName || ' tax,' ||
3046 ' RG_XBRL_ELEMENTS'||'@'|| G_LinkName || ' elm,' ||
3047 ' RG_XBRL_MAP_ELEMENTS'||'@'|| G_LinkName || ' map ' ||
3048 'WHERE map.element_id = elm.element_id AND ' ||
3049 ' elm.taxonomy_id = tax.taxonomy_id AND ' ||
3050 ' map.enabled_flag = ''Y'' AND ' ||
3051 ' map.taxonomy_id = '|| to_char(parent_tax_id) || ' AND ' ||
3052 ' tax.taxonomy_id <> ' || to_char(parent_tax_id);
3053
3054 RG_XFER_UTILS_PKG.display_string(SQLString);
3055
3056 CursorId := DBMS_SQL.open_cursor;
3057
3058 DBMS_SQL.parse(CursorId, SQLString, DBMS_SQL.v7);
3059 DBMS_SQL.define_column(CursorId, 1, src_tax_id);
3060 DBMS_SQL.define_column(CursorId, 2, src_tax_alias, 240);
3061 child_cnt := 0;
3062 ExecuteValue := DBMS_SQL.execute(CursorId);
3063
3064 LOOP
3065 ExecuteValue := DBMS_SQL.fetch_rows(CursorId);
3066 IF (ExecuteValue > 0) THEN
3067 child_cnt := child_cnt + 1;
3068 DBMS_SQL.column_value(CursorId, 1, src_tax_id);
3069 DBMS_SQL.column_value(CursorId, 2, src_tax_alias);
3070 arr_child_tax_id(child_cnt) := src_tax_id;
3071 arr_child_tax_alias(child_cnt) := src_tax_alias;
3072 arr_child_done_flag(child_cnt) := 0;
3073 ELSE
3074 EXIT;
3075 END IF;
3076 END LOOP;
3077 DBMS_SQL.close_cursor(CursorId);
3078
3079 /* all childs are created */
3080
3081 IF (child_cnt = 0) THEN
3082
3083 dest_taxonomy_id := RG_XFER_UTILS_PKG.get_new_id('rg_xbrl_taxonomy_s');
3084
3085 /* populate one row in rg_xbrl_taxonomies */
3086
3087 SQLString :=
3088 'INSERT INTO RG_XBRL_TAXONOMIES (' ||
3089 ' TAXONOMY_ID, TAXONOMY_ALIAS, TAXONOMY_NAME, ' ||
3090 ' TAXONOMY_DESCR, TAXONOMY_URL, TAXONOMY_IMPORT_FLAG, ' ||
3091 ' CREATION_DATE, CREATED_BY, ' ||
3092 ' LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN' ||
3093 ') SELECT ' || to_char(dest_taxonomy_id) || ',' ||
3094 ' taxonomy_alias, taxonomy_name, ' ||
3095 ' taxonomy_descr, taxonomy_url, taxonomy_import_flag, ' ||
3096 ' SYSDATE,' || to_char(l_user_id) || ', ' ||
3097 ' SYSDATE,' || to_char(l_user_id) || ', ' ||
3098 to_char(l_login_id) || ' ' ||
3099 'FROM RG_XBRL_TAXONOMIES@' || G_LinkName ||
3100 ' WHERE taxonomy_id = ' || to_char(parent_tax_id);
3101
3102 RG_XFER_UTILS_PKG.display_string(SQLString);
3103
3104 CursorId := DBMS_SQL.open_cursor;
3105 DBMS_SQL.parse(CursorId, SQLString, DBMS_SQL.v7);
3106 ExecuteValue := DBMS_SQL.execute(CursorId);
3107 DBMS_SQL.close_cursor(CursorId);
3108
3109 /* populate multiple rows in rg_xbrl_elements */
3110 SQLString :=
3111 'SELECT ' ||
3112 ' element_identifier, ' ||
3113 ' element_name, element_type, element_group, ' ||
3114 ' element_descr, element_label, parent_identifier, ' ||
3115 ' has_child_flag, has_parent_flag, hierarchy_level ' ||
3116 'FROM RG_XBRL_ELEMENTS@' || G_LinkName ||
3117 ' WHERE taxonomy_id = ' || to_char(parent_tax_id);
3118
3119 RG_XFER_UTILS_PKG.display_string(SQLString);
3120
3121 CursorId := DBMS_SQL.open_cursor;
3122
3123 DBMS_SQL.parse(CursorId, SQLString, DBMS_SQL.v7);
3124
3125 DBMS_SQL.define_column(CursorId, 1, src_element_identifier,240);
3126 DBMS_SQL.define_column(CursorId, 2, src_element_name, 240);
3127 DBMS_SQL.define_column(CursorId, 3, src_element_type,240);
3128 DBMS_SQL.define_column(CursorId, 4, src_element_group, 240);
3129 DBMS_SQL.define_column(CursorId, 5, src_element_descr,3000);
3130 DBMS_SQL.define_column(CursorId, 6, src_element_label, 240);
3131 DBMS_SQL.define_column(CursorId, 7, src_parent_identifier,240);
3132 DBMS_SQL.define_column(CursorId, 8, src_has_child_flag,1);
3133 DBMS_SQL.define_column(CursorId, 9, src_has_parent_flag,1);
3134 DBMS_SQL.define_column(CursorId, 10, src_hierarchy_level);
3135
3136 ExecuteValue := DBMS_SQL.execute(CursorId);
3137
3138 LOOP
3139 ExecuteValue := DBMS_SQL.fetch_rows(CursorId);
3140 IF (ExecuteValue > 0) THEN
3141 DBMS_SQL.column_value(CursorId, 1, src_element_identifier);
3142 DBMS_SQL.column_value(CursorId, 2, src_element_name);
3143 DBMS_SQL.column_value(CursorId, 3, src_element_type);
3144 DBMS_SQL.column_value(CursorId, 4, src_element_group);
3145 DBMS_SQL.column_value(CursorId, 5, src_element_descr);
3146 DBMS_SQL.column_value(CursorId, 6, src_element_label);
3147 DBMS_SQL.column_value(CursorId, 7, src_parent_identifier);
3148 DBMS_SQL.column_value(CursorId, 8, src_has_child_flag);
3149 DBMS_SQL.column_value(CursorId, 9, src_has_parent_flag);
3150 DBMS_SQL.column_value(CursorId, 10, src_hierarchy_level);
3151 --
3152 cur_elem_id := RG_XFER_UTILS_PKG.get_new_id('rg_xbrl_elements_s');
3153 --
3154 INSERT INTO RG_XBRL_ELEMENTS (
3155 TAXONOMY_ID, ELEMENT_ID, ELEMENT_IDENTIFIER,
3156 ELEMENT_NAME, ELEMENT_TYPE, ELEMENT_GROUP,
3157 ELEMENT_DESCR, ELEMENT_LABEL, PARENT_IDENTIFIER,
3158 PARENT_ID, HAS_CHILD_FLAG, HAS_PARENT_FLAG, HIERARCHY_LEVEL,
3162 dest_taxonomy_id,cur_elem_id,src_element_identifier,
3159 CREATION_DATE, CREATED_BY,
3160 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN)
3161 VALUES (
3163 src_element_name,src_element_type,src_element_group,
3164 src_element_descr,src_element_label,src_parent_identifier,
3165 NULL,src_has_child_flag,src_has_parent_flag,src_hierarchy_level,
3166 SYSDATE,l_user_id,SYSDATE,l_user_id,l_login_id);
3167 ELSE
3168 EXIT;
3169 END IF;
3170 END LOOP;
3171
3172 DBMS_SQL.close_cursor(CursorId);
3173
3174 /* populate multiple rows in rg_xbrl_map_elements */
3175
3176 SQLString :=
3177 'INSERT INTO RG_XBRL_MAP_ELEMENTS ('||
3178 ' TAXONOMY_ID, ELEMENT_ID, ENABLED_FLAG,' ||
3179 ' CREATION_DATE, CREATED_BY,' ||
3180 ' LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN' ||
3181 ') SELECT ' || to_char(dest_taxonomy_id) || ',' ||
3182 ' element_id, ''Y'',' ||
3183 ' SYSDATE,' || to_char(l_user_id) || ',' ||
3184 ' SYSDATE,' || to_char(l_user_id) || ',' || to_char(l_login_id) ||
3185 ' FROM RG_XBRL_ELEMENTS ' ||
3186 ' WHERE taxonomy_id = ' || to_char(dest_taxonomy_id);
3187
3188 RG_XFER_UTILS_PKG.display_string(SQLString);
3189
3190 CursorId := DBMS_SQL.open_cursor;
3191 DBMS_SQL.parse(CursorId, SQLString, DBMS_SQL.v7);
3192 ExecuteValue := DBMS_SQL.execute(CursorId);
3193 DBMS_SQL.close_cursor(CursorId);
3194
3195 parent_done_flag := 1;
3196 RETURN;
3197
3198 END IF;
3199
3200 cnt := 1;
3201 WHILE cnt <= child_cnt
3202 LOOP
3203 cur_tax_id := arr_child_tax_id(cnt);
3204 cur_tax_alias := arr_child_tax_alias(cnt);
3205 cur_done_flag := 0;
3206
3210 /* Check if parent taxonomy of the same alias already exists in target db */
3207 transfer_taxonomy(cur_tax_alias,cur_tax_id,cur_done_flag);
3208
3209 --IF (cur_done_flag = 1) THEN
3211
3212 RG_XFER_UTILS_PKG.copy_adjust_string(TempValue, parent_tax_alias);
3213 ValueString := '''' || TempValue || '''';
3214
3215 SQLString := 'SELECT taxonomy_id ' ||
3216 'FROM rg_xbrl_taxonomies ' ||
3217 'WHERE taxonomy_alias = ' || ValueString;
3218
3219 RG_XFER_UTILS_PKG.display_string(SQLString);
3220
3221 TargetId := RG_XFER_UTILS_PKG.component_exists(SQLString);
3222
3223 IF (TargetId = G_Error) THEN
3224 /* parent taxonomy does not exist in target db */
3225
3226 dest_taxonomy_id := RG_XFER_UTILS_PKG.get_new_id('rg_xbrl_taxonomy_s');
3227
3228 /* populate one row in rg_xbrl_taxonomies */
3229
3230 SQLString :=
3231 'INSERT INTO RG_XBRL_TAXONOMIES (' ||
3232 ' TAXONOMY_ID, TAXONOMY_ALIAS, TAXONOMY_NAME,' ||
3233 ' TAXONOMY_DESCR, TAXONOMY_URL, TAXONOMY_IMPORT_FLAG,' ||
3234 ' CREATION_DATE, CREATED_BY,' ||
3235 ' LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN' ||
3236 ') SELECT ' || to_char(dest_taxonomy_id) || ',' ||
3237 ' taxonomy_alias, taxonomy_name,' ||
3238 ' taxonomy_descr, taxonomy_url, taxonomy_import_flag,' ||
3239 ' SYSDATE,' || TO_CHAR(l_user_id) || ',' ||
3240 ' SYSDATE,' || TO_CHAR(l_user_id) || ',' || TO_CHAR(l_login_id) ||
3241 ' FROM RG_XBRL_TAXONOMIES@' || G_LinkName ||
3242 ' WHERE taxonomy_id = ' || to_char(parent_tax_id);
3243
3244 RG_XFER_UTILS_PKG.display_string(SQLString);
3245
3246 CursorId := DBMS_SQL.open_cursor;
3247 DBMS_SQL.parse(CursorId, SQLString, DBMS_SQL.v7);
3248 ExecuteValue := DBMS_SQL.execute(CursorId);
3249 DBMS_SQL.close_cursor(CursorId);
3250
3251 /* populate multiple rows in rg_xbrl_elements */
3252
3253 SQLString :=
3254 'SELECT ' ||
3255 ' element_identifier, element_name, element_type, element_group,' ||
3256 ' element_descr, element_label, parent_identifier,' ||
3257 ' has_child_flag, has_parent_flag, hierarchy_level' ||
3258 ' FROM RG_XBRL_ELEMENTS@' || G_LinkName ||
3259 ' WHERE taxonomy_id = ' || to_char(parent_tax_id);
3260
3261 RG_XFER_UTILS_PKG.display_string(SQLString);
3262
3263 CursorId := DBMS_SQL.open_cursor;
3264
3265 DBMS_SQL.parse(CursorId, SQLString, DBMS_SQL.v7);
3266
3267 DBMS_SQL.define_column(CursorId, 1, src_element_identifier,240);
3268 DBMS_SQL.define_column(CursorId, 2, src_element_name, 240);
3269 DBMS_SQL.define_column(CursorId, 3, src_element_type,240);
3270 DBMS_SQL.define_column(CursorId, 4, src_element_group, 240);
3271 DBMS_SQL.define_column(CursorId, 5, src_element_descr,3000);
3272 DBMS_SQL.define_column(CursorId, 6, src_element_label, 240);
3273 DBMS_SQL.define_column(CursorId, 7, src_parent_identifier,240);
3274 DBMS_SQL.define_column(CursorId, 8, src_has_child_flag,1);
3275 DBMS_SQL.define_column(CursorId, 9, src_has_parent_flag,1);
3276 DBMS_SQL.define_column(CursorId, 10, src_hierarchy_level);
3277
3278 ExecuteValue := DBMS_SQL.execute(CursorId);
3279
3280 LOOP
3281 ExecuteValue := DBMS_SQL.fetch_rows(CursorId);
3282 IF (ExecuteValue > 0) THEN
3283 DBMS_SQL.column_value(CursorId, 1, src_element_identifier);
3284 DBMS_SQL.column_value(CursorId, 2, src_element_name);
3285 DBMS_SQL.column_value(CursorId, 3, src_element_type);
3286 DBMS_SQL.column_value(CursorId, 4, src_element_group);
3287 DBMS_SQL.column_value(CursorId, 5, src_element_descr);
3288 DBMS_SQL.column_value(CursorId, 6, src_element_label);
3289 DBMS_SQL.column_value(CursorId, 7, src_parent_identifier);
3290 DBMS_SQL.column_value(CursorId, 8, src_has_child_flag);
3291 DBMS_SQL.column_value(CursorId, 9, src_has_parent_flag);
3292 DBMS_SQL.column_value(CursorId, 10, src_hierarchy_level);
3293 --
3294 cur_elem_id := RG_XFER_UTILS_PKG.get_new_id('rg_xbrl_elements_s');
3295 --
3296 INSERT INTO RG_XBRL_ELEMENTS (
3297 TAXONOMY_ID, ELEMENT_ID, ELEMENT_IDENTIFIER,
3298 ELEMENT_NAME, ELEMENT_TYPE, ELEMENT_GROUP,
3299 ELEMENT_DESCR, ELEMENT_LABEL, PARENT_IDENTIFIER,
3300 PARENT_ID, HAS_CHILD_FLAG, HAS_PARENT_FLAG, HIERARCHY_LEVEL,
3301 CREATION_DATE, CREATED_BY,
3302 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN)
3303 VALUES (
3304 dest_taxonomy_id,cur_elem_id,src_element_identifier,
3305 src_element_name,src_element_type,src_element_group,
3306 src_element_descr,src_element_label,src_parent_identifier,
3307 NULL,src_has_child_flag,src_has_parent_flag,src_hierarchy_level,
3308 SYSDATE,l_user_id,SYSDATE,l_user_id,l_login_id);
3309 ELSE
3310 EXIT;
3311 END IF;
3312 END LOOP;
3313
3314 DBMS_SQL.close_cursor(CursorId);
3315
3316 /* populate multiple rows in rg_xbrl_map_elements */
3317
3318 SQLString :=
3319 'INSERT INTO RG_XBRL_MAP_ELEMENTS (' ||
3320 ' TAXONOMY_ID, ELEMENT_ID, ENABLED_FLAG,' ||
3321 ' CREATION_DATE, CREATED_BY,' ||
3322 ' LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN' ||
3323 ') SELECT ' || to_char(dest_taxonomy_id) || ',' ||
3324 ' element_id, ''Y'',' ||
3325 ' SYSDATE,' || TO_CHAR(l_user_id) || ',' ||
3326 ' SYSDATE,' || TO_CHAR(l_user_id) || ',' || TO_CHAR(l_login_id) ||
3327 ' FROM RG_XBRL_ELEMENTS ' ||
3328 ' WHERE taxonomy_id = ' || to_char(dest_taxonomy_id);
3329
3330 RG_XFER_UTILS_PKG.display_string(SQLString);
3331
3332 CursorId := DBMS_SQL.open_cursor;
3333 DBMS_SQL.parse(CursorId, SQLString, DBMS_SQL.v7);
3334 ExecuteValue := DBMS_SQL.execute(CursorId);
3335 DBMS_SQL.close_cursor(CursorId);
3336
3337 END IF;
3338
3339 /* update rg_xbrl_map_elements for the parent with a child */
3340
3341 RG_XFER_UTILS_PKG.copy_adjust_string(TempValue, cur_tax_alias);
3342 ValueString := '''' || TempValue || '''';
3343
3344 SQLString := 'SELECT taxonomy_id ' ||
3345 'FROM rg_xbrl_taxonomies ' ||
3346 'WHERE taxonomy_alias = ' || ValueString;
3347
3348 l_child_tax_id := RG_XFER_UTILS_PKG.component_exists(SQLString);
3349
3350 SQLString :=
3351 'INSERT INTO RG_XBRL_MAP_ELEMENTS (' ||
3352 ' TAXONOMY_ID, ELEMENT_ID, ENABLED_FLAG,' ||
3353 ' CREATION_DATE, CREATED_BY,' ||
3354 ' LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN' ||
3355 ') SELECT ' || to_char(dest_taxonomy_id) || ',' ||
3356 ' mel.element_id, mel.enabled_flag,' ||
3357 ' SYSDATE,' || TO_CHAR(l_user_id) || ',' ||
3358 ' SYSDATE,' || TO_CHAR(l_user_id) || ',' || TO_CHAR(l_login_id) ||
3359 ' FROM RG_XBRL_MAP_ELEMENTS mel' ||
3360 ' WHERE mel.taxonomy_id = ' || to_char(l_child_tax_id) || ' AND ' ||
3361 ' mel.element_id NOT IN ' ||
3362 '(SELECT map.element_id FROM RG_XBRL_MAP_ELEMENTS map ' ||
3363 'WHERE map.taxonomy_id = ' || to_char(dest_taxonomy_id) || ')';
3364
3365 RG_XFER_UTILS_PKG.display_string(SQLString);
3366
3367 CursorId := DBMS_SQL.open_cursor;
3368 DBMS_SQL.parse(CursorId, SQLString, DBMS_SQL.v7);
3369 ExecuteValue := DBMS_SQL.execute(CursorId);
3370 DBMS_SQL.close_cursor(CursorId);
3371
3372 --ELSE
3373 -- parent_done_flag := 0;
3374 -- RETURN;
3375 --END IF;
3376
3377 cnt := cnt + 1;
3378
3379 END LOOP;
3380
3381 END transfer_taxonomy;
3382
3383 BEGIN
3384 /* Initialize variables on package access. */
3385
3386 /* Error codes */
3387 G_Error := RG_XFER_UTILS_PKG.G_Error;
3388 G_Warning := RG_XFER_UTILS_PKG.G_Warning;
3389
3390 /* The message levels */
3391 G_ML_Minimal := RG_XFER_UTILS_PKG.G_ML_Minimal;
3392 G_ML_Normal := RG_XFER_UTILS_PKG.G_ML_Normal;
3393 G_ML_Full := RG_XFER_UTILS_PKG.G_ML_Full;
3394
3395 END RG_XFER_COMPONENTS_PKG;