[Home] [Help]
PACKAGE BODY: APPS.RG_XFER_UTILS_PKG
Source
1 PACKAGE BODY RG_XFER_UTILS_PKG as
2 /* $Header: rgixutlb.pls 120.8 2006/08/22 14:55:40 kkchopra ship $ */
3
4 /* Variables */
5
6 G_UserId NUMBER;
7 G_LoginId NUMBER;
8 G_ApplId NUMBER;
9 G_SourceCOAId NUMBER;
10 G_TargetCOAId NUMBER;
11 G_LinkName VARCHAR2(100);
12
13 /* The message level specified by the profile option 'FSG:Message Details' */
14 G_MsgLevel NUMBER;
15
16
17 /* Name: display_string
18 * Desc: Display a long string LineSize characters at a time. Display the
19 * string only if the message level is 'Full'.
20 *
21 * History:
22 * 02/08/96 S Rahman Created.
23 */
24 PROCEDURE display_string(
25 InputStr VARCHAR2
26 ) IS
27 InputLen INTEGER;
28 CurrPos INTEGER;
29 LineSize INTEGER := 60;
30 BEGIN
31 IF (G_MsgLevel = G_ML_Full) THEN
32 -- DBMS_OUTPUT.put_line('SQL: Executing:');
33 FND_FILE.put_line(FND_FILE.OUTPUT, 'SQL: Executing:');
34 CurrPos := 1;
35 InputLen := LENGTH(InputStr);
36 WHILE (CurrPos <= InputLen) LOOP
37 IF (CurrPos + LineSize < InputLen) THEN
38 -- DBMS_OUTPUT.put_line('> ' || SUBSTR(InputStr, CurrPos, LineSize));
39 FND_FILE.put_line(FND_FILE.OUTPUT, '> ' || SUBSTR(InputStr, CurrPos, LineSize));
40 CurrPos := CurrPos + LineSize;
41 ELSE
42 -- DBMS_OUTPUT.put_line('> ' || SUBSTR(InputStr, CurrPos));
43 FND_FILE.put_line(FND_FILE.OUTPUT, '> ' || SUBSTR(InputStr, CurrPos));
44 CurrPos := InputLen + 1;
45 END IF;
46 END LOOP;
47 END IF;
48 END display_string;
49
50
51 /* Name: copy_adjust_string
52 * Desc: Copy a string. Adjust the string for single quotes.
53 *
54 * History:
55 * 02/07/96 S Rahman Created.
56 */
57 PROCEDURE copy_adjust_string(
58 TargetStr IN OUT NOCOPY VARCHAR2,
59 SourceStr VARCHAR2) IS
60 CurrPos INTEGER;
61 TempPos INTEGER;
62 Done BOOLEAN;
63 SourceLen INTEGER;
64 BEGIN
65 TargetStr := '';
66 CurrPos := 1;
67 SourceLen := LENGTH(SourceStr);
68 Done := FALSE;
69 WHILE ((NOT Done) AND (CurrPos <= SourceLen)) LOOP
70 TempPos := INSTR(SourceStr, '''', CurrPos);
71 IF (TempPos <> 0) THEN
72 TargetStr := TargetStr ||
73 SUBSTR(SourceStr, CurrPos, TempPos-CurrPos) || '''''';
74 CurrPos := TempPos + 1;
75 ELSE
76 TargetStr := TargetStr || SUBSTR(SourceStr, CurrPos);
77 Done := TRUE;
78 END IF;
79 END LOOP;
80 END copy_adjust_string;
81
82
83 /* Name: display_message
84 * Desc: Write a message to the output.
85 *
86 * History:
87 * 10/17/95 S Rahman Created.
88 */
89 PROCEDURE display_message(
90 MsgName VARCHAR2,
91 Token1 VARCHAR2 DEFAULT NULL,
92 Token1Val VARCHAR2 DEFAULT NULL,
93 Token1Xlate BOOLEAN DEFAULT FALSE,
94 Token2 VARCHAR2 DEFAULT NULL,
95 Token2Val VARCHAR2 DEFAULT NULL,
96 Token2Xlate BOOLEAN DEFAULT FALSE,
97 Token3 VARCHAR2 DEFAULT NULL,
98 Token3Val VARCHAR2 DEFAULT NULL,
99 Token3Xlate BOOLEAN DEFAULT FALSE,
100 Token4 VARCHAR2 DEFAULT NULL,
101 Token4Val VARCHAR2 DEFAULT NULL,
102 Token4Xlate BOOLEAN DEFAULT FALSE,
103 Token5 VARCHAR2 DEFAULT NULL,
104 Token5Val VARCHAR2 DEFAULT NULL,
105 Token5Xlate BOOLEAN DEFAULT FALSE
106 ) IS
107 Msg VARCHAR2(500);
108 BEGIN
109 FND_MESSAGE.set_name('RG', MsgName);
110 IF (Token1 IS NOT NULL) THEN
111 FND_MESSAGE.set_token(Token1, Token1Val, Token1Xlate);
112 IF (Token2 IS NOT NULL) THEN
113 FND_MESSAGE.set_token(Token2, Token2Val, Token2Xlate);
114 IF (Token3 IS NOT NULL) THEN
115 FND_MESSAGE.set_token(Token3, Token3Val, Token3Xlate);
116 IF (Token4 IS NOT NULL) THEN
117 FND_MESSAGE.set_token(Token4, Token4Val, Token4Xlate);
118 IF (Token5 IS NOT NULL) THEN
119 FND_MESSAGE.set_token(Token5, Token5Val, Token5Xlate);
120 END IF;
121 END IF;
122 END IF;
123 END IF;
124 END IF;
125 Msg := FND_MESSAGE.get;
126 -- DBMS_OUTPUT.put_line(Msg);
127 FND_FILE.put_line(FND_FILE.OUTPUT, Msg);
128 END display_message;
129
130
131 /* Name: display_log
132 * Desc: Write a log message (depending on the message level).
133 *
134 * History:
135 * 10/17/95 S Rahman Created.
136 */
137 PROCEDURE display_log(
138 MsgLevel NUMBER,
139 MsgName VARCHAR2,
140 Token1 VARCHAR2 DEFAULT NULL,
141 Token1Val VARCHAR2 DEFAULT NULL,
142 Token1Xlate BOOLEAN DEFAULT FALSE,
143 Token2 VARCHAR2 DEFAULT NULL,
144 Token2Val VARCHAR2 DEFAULT NULL,
145 Token2Xlate BOOLEAN DEFAULT FALSE,
146 Token3 VARCHAR2 DEFAULT NULL,
147 Token3Val VARCHAR2 DEFAULT NULL,
148 Token3Xlate BOOLEAN DEFAULT FALSE,
149 Token4 VARCHAR2 DEFAULT NULL,
150 Token4Val VARCHAR2 DEFAULT NULL,
151 Token4Xlate BOOLEAN DEFAULT FALSE
152 ) IS
153 BEGIN
154 IF (MsgLevel <= G_MsgLevel) THEN
155 display_message(
156 MsgName,
157 Token1, Token1Val, Token1Xlate,
158 Token2, Token2Val, Token2Xlate,
159 Token3, Token3Val, Token3Xlate,
160 Token4, Token4Val, Token4Xlate);
161 END IF;
162 END display_log;
163
164
165 /* Name: display_error
166 * Desc: Write a error message.
167 *
168 * History:
169 * 10/17/95 S Rahman Created.
170 */
171 PROCEDURE display_error(
172 MsgName VARCHAR2,
173 Token1 VARCHAR2 DEFAULT NULL,
174 Token1Val VARCHAR2 DEFAULT NULL,
175 Token1Xlate BOOLEAN DEFAULT FALSE,
176 Token2 VARCHAR2 DEFAULT NULL,
177 Token2Val VARCHAR2 DEFAULT NULL,
178 Token2Xlate BOOLEAN DEFAULT FALSE,
179 Token3 VARCHAR2 DEFAULT NULL,
180 Token3Val VARCHAR2 DEFAULT NULL,
181 Token3Xlate BOOLEAN DEFAULT FALSE
182 ) IS
183 BEGIN
184 FoundError := TRUE;
185 display_message(
186 MsgName,
187 Token1, Token1Val, Token1Xlate,
188 Token2, Token2Val, Token2Xlate,
189 Token3, Token3Val, Token3Xlate);
190 END display_error;
191
192
193 /* Name: display_warning
194 * Desc: Write a warning message.
195 *
196 * History:
197 * 10/17/95 S Rahman Created.
198 */
199 PROCEDURE display_warning(
200 MsgName VARCHAR2,
201 Token1 VARCHAR2 DEFAULT NULL,
202 Token1Val VARCHAR2 DEFAULT NULL,
203 Token1Xlate BOOLEAN DEFAULT FALSE,
204 Token2 VARCHAR2 DEFAULT NULL,
205 Token2Val VARCHAR2 DEFAULT NULL,
206 Token2Xlate BOOLEAN DEFAULT FALSE,
207 Token3 VARCHAR2 DEFAULT NULL,
208 Token3Val VARCHAR2 DEFAULT NULL,
209 Token3Xlate BOOLEAN DEFAULT FALSE,
210 Token4 VARCHAR2 DEFAULT NULL,
211 Token4Val VARCHAR2 DEFAULT NULL,
212 Token4Xlate BOOLEAN DEFAULT FALSE
213 ) IS
214 BEGIN
215 FoundWarning := TRUE;
216 display_message(
217 MsgName,
218 Token1, Token1Val, Token1Xlate,
219 Token2, Token2Val, Token2Xlate,
220 Token3, Token3Val, Token3Xlate,
221 Token4, Token4Val, Token4Xlate);
222 END display_warning;
223
224
225 /* Name: display_exception
226 * Desc: Display exception information
227 *
228 * History:
229 * 03/08/96 S Rahman Created.
230 */
231 PROCEDURE display_exception(
232 ErrorNum NUMBER,
233 ErrorMsg VARCHAR2
234 ) IS
235 BEGIN
236 display_log(
237 MsgLevel => G_ML_Full,
238 MsgName => 'RG_XFER_L_EXCEPTION',
239 Token1 => 'ERROR_NUM',
240 Token1Val => TO_CHAR(ErrorNum));
241 display_string(ErrorMsg);
242 END display_exception;
243
244
245 /* Name: init
246 * Desc: Initialize variable used by RG_XFER_* package routines.
247 *
248 * History:
249 * 10/17/95 S Rahman Created.
250 */
251 PROCEDURE init(
252 SourceCOAId NUMBER,
253 TargetCOAId NUMBER,
254 LinkName VARCHAR2) IS
255 ProfileUserId VARCHAR2(20);
256 ProfileLoginId VARCHAR2(20);
257 ProfileMsgLevel VARCHAR2(20) := NULL;
258 BEGIN
259 /* Set the values to be used for who columns from profile options. */
260 FND_PROFILE.get('USER_ID', ProfileUserId);
261 FND_PROFILE.get('CONC_LOGIN_ID', ProfileLoginId);
262 G_UserId := to_number(ProfileUserId);
263 G_LoginId := to_number(ProfileLoginId);
264 G_ApplId := 101;
265
266 /* Get message level from profile option and set local variable */
267 FND_PROFILE.get('RG_LOGFILE_DETAIL_LEVEL', ProfileMsgLevel);
268 IF (ProfileMsgLevel IS NOT NULL) THEN
269 G_MsgLevel := to_number(ProfileMsgLevel);
270 ELSE
271 /* Profile option not set. Set to default the message level */
272 G_MsgLevel := G_ML_Minimal;
273 END IF;
274
275 /* Initialize passed parameters. */
276 G_SourceCOAId := SourceCOAId;
277 G_TargetCOAId := TargetCOAId;
278 G_LinkName := LinkName;
279
280 /* Iniitialize the PRIVATE package */
281 RG_XFER_COMPONENTS_PKG.init(SourceCOAId, TargetCOAId, LinkName, G_ApplId);
282 END init;
283
284
285 FUNCTION ping_link(LinkName VARCHAR2) RETURN BOOLEAN IS
286 CursorId INTEGER;
287 ExecuteValue INTEGER;
288 BEGIN
289 -- select using the database link
290 CursorId := DBMS_SQL.open_cursor;
291 DBMS_SQL.parse(CursorId,
292 'SELECT 1 FROM DUAL@'|| LinkName,
293 DBMS_SQL.v7);
294 ExecuteValue := DBMS_SQL.execute(CursorId);
295 DBMS_SQL.close_cursor(CursorId);
296 RETURN(TRUE);
297 EXCEPTION
298 WHEN OTHERS THEN
299 RETURN(FALSE);
300 END ping_link;
301
302
303 FUNCTION create_link(LinkName VARCHAR2,
304 Username VARCHAR2,
305 Password VARCHAR2,
306 ConnectString VARCHAR2) RETURN NUMBER IS
307 CursorId INTEGER;
308 ExecuteValue INTEGER;
309
310 CURSOR GetLink(LinkName VARCHAR2) IS
311 SELECT *
312 FROM user_db_links
313 WHERE UPPER(db_link) = UPPER(LinkName);
314 LinkRow user_db_links%ROWTYPE;
315
316 RetVal NUMBER;
317 TempVal BOOLEAN;
318
319 link_exists EXCEPTION;
320 PRAGMA EXCEPTION_INIT(link_exists, -2011);
321
322 insufficient_priv EXCEPTION;
323 PRAGMA EXCEPTION_INIT(insufficient_priv, -1031);
324
325 BEGIN
326 -- create the database link
327 CursorId := DBMS_SQL.open_cursor;
328 DBMS_SQL.parse(CursorId,
329 'CREATE DATABASE LINK '|| LinkName ||
330 ' CONNECT TO '|| Username ||' IDENTIFIED BY '|| Password ||
331 ' USING '''|| ConnectString ||'''',
332 DBMS_SQL.v7);
333 ExecuteValue := DBMS_SQL.execute(CursorId);
334 DBMS_SQL.close_cursor(CursorId);
335 IF NOT ping_link(LinkName) THEN
336 /* Try to drop the link; ignore return value */
337 TempVal := drop_link(LinkName);
338 RETURN(0);
339 END IF;
340 RETURN(1);
341 EXCEPTION
342 WHEN link_exists THEN
343 OPEN GetLink(LinkName);
344 FETCH GetLink INTO LinkRow;
345 IF ((UPPER(LinkRow.username) = UPPER(Username)) AND
346 (UPPER(LinkRow.password) = UPPER(Password)) AND
347 (UPPER(LinkRow.host) = UPPER(ConnectString))) THEN
348 RetVal := 1;
349 ELSE
350 RetVal := 0;
351 END IF;
352 CLOSE GetLink;
353 RETURN(RetVal);
354 WHEN insufficient_priv THEN
355 RETURN(1031);
356 WHEN OTHERS THEN
357 RETURN(0);
358 END create_link;
359
360
361 FUNCTION drop_link(LinkName VARCHAR2) RETURN BOOLEAN IS
362 CursorId INTEGER;
363 ExecuteValue INTEGER;
364 link_not_found EXCEPTION;
365 PRAGMA EXCEPTION_INIT(link_not_found, -2024);
366 BEGIN
367 -- drop the database link
368 CursorId := DBMS_SQL.open_cursor;
369 DBMS_SQL.parse(CursorId,
370 'DROP DATABASE LINK '|| LinkName,
371 DBMS_SQL.v7);
372 ExecuteValue := DBMS_SQL.execute(CursorId);
373 DBMS_SQL.close_cursor(CursorId);
374 RETURN(TRUE);
375 EXCEPTION
376 WHEN link_not_found THEN
377 RETURN(FALSE);
378 WHEN OTHERS THEN
379 RETURN(FALSE);
380 END drop_link;
381
382
383 /* Name: insert_into_list
384 * Desc: Insert the passed name into the PL/SQL table and update the count.
385 *
386 * History:
387 * 10/17/95 S Rahman Created.
388 */
389 PROCEDURE insert_into_list(
390 ListName IN OUT NOCOPY ListType,
391 ListCount IN OUT NOCOPY BINARY_INTEGER,
392 Name VARCHAR2) IS
393 BEGIN
394 ListName(ListCount) := Name;
395 ListCount := ListCount + 1;
396 END insert_into_list;
397
398
399 /* Name: search_list
400 * Desc: Search the PL/SQL table for the name, and return the index if the
401 * name is found. If the name is not found then return error code.
402 *
403 * History:
404 * 10/17/95 S Rahman Created.
405 */
406 FUNCTION search_list(
407 ListName ListType,
408 ListCount BINARY_INTEGER,
409 Name VARCHAR2) RETURN BINARY_INTEGER IS
410 i BINARY_INTEGER := 0;
411 BEGIN
412 /* Scan the list */
413 WHILE (i < ListCount) LOOP
414 BEGIN
415 /* Search for the name */
416 EXIT WHEN (ListName(i) = Name);
417 EXCEPTION
418 WHEN no_data_found THEN
419 NULL;
420 END;
421 /* Try the next entry in the list */
422 i := i + 1;
423 END LOOP;
424
425 /* Check if the name was found */
426 IF (i >= ListCount) THEN
427 i := G_Error;
428 END IF;
429
430 /* Return the index, or error code */
431 RETURN(i);
432 END search_list;
433
434
435 /* Name: get_source_id
436 * Desc: Get the id from the source database for the specified component.
437 *
438 * History:
439 * 10/17/95 S Rahman Created.
440 */
441 FUNCTION get_source_id(
442 TableName VARCHAR2,
443 IdName VARCHAR2,
444 CompName VARCHAR2,
445 WhereClause VARCHAR2 DEFAULT NULL) RETURN NUMBER IS
446 CursorId INTEGER;
447 ExecuteValue INTEGER;
448 Id NUMBER;
449 SQLString VARCHAR2(200);
450 ComponentName VARCHAR2(60);
451 BEGIN
452 copy_adjust_string(ComponentName, CompName);
453 SQLString := 'SELECT ' || IdName || ' FROM ' || TableName||'@'||G_LinkName ||
454 ' WHERE name = ''' || ComponentName || ''' ' || WhereClause;
455 display_string(SQLString);
456 CursorId := DBMS_SQL.open_cursor;
457 DBMS_SQL.parse(CursorId, SQLString, DBMS_SQL.v7);
458 DBMS_SQL.define_column(CursorId, 1, Id);
459 ExecuteValue := DBMS_SQL.execute_and_fetch(CursorId);
460 DBMS_SQL.column_value(CursorId, 1, Id);
461 DBMS_SQL.close_cursor(CursorId);
462 RETURN(Id);
463 END get_source_id;
464
465
466 /* Name: get_new_id
467 * Desc: Get a new id from the sequence in the target database.
468 *
469 * History:
470 * 10/17/95 S Rahman Created.
471 */
472 FUNCTION get_new_id(SequenceName VARCHAR2) RETURN NUMBER IS
473 CursorId INTEGER;
474 ExecuteValue INTEGER;
475 Id NUMBER;
476 SQLString VARCHAR2(100);
477 BEGIN
478 SQLString := 'SELECT ' || SequenceName || '.nextval FROM sys.dual';
479 display_string(SQLString);
480 CursorId := DBMS_SQL.open_cursor;
481 DBMS_SQL.parse(CursorId, SQLString, DBMS_SQL.v7);
482 DBMS_SQL.define_column(CursorId, 1, Id);
483 ExecuteValue := DBMS_SQL.execute_and_fetch(CursorId);
484 DBMS_SQL.column_value(CursorId, 1, Id);
485 DBMS_SQL.close_cursor(CursorId);
486 RETURN(Id);
487 END get_new_id;
488
489
490 /* Name: get_source_ref_object_name
491 * Desc: Get the name of a sub-component given the component information.
492 * For example, this routine is used to obtain the row set name
493 * from the report id (both in the source database).
494 *
495 * History:
496 * 10/17/95 S Rahman Created.
497 */
498 FUNCTION get_source_ref_object_name(
499 MainTableName VARCHAR2,
500 RefTableName VARCHAR2,
501 ColumnName VARCHAR2,
502 ColumnValue VARCHAR2,
503 MainIdName VARCHAR2,
504 RefIdName VARCHAR2,
505 CharColumn BOOLEAN DEFAULT TRUE) RETURN VARCHAR2 IS
506 CursorId INTEGER;
507 ExecuteValue INTEGER;
508 RefObjectName VARCHAR2(30);
509 ValueString VARCHAR2(60);
510 SQLString VARCHAR2(500);
511 TempValue VARCHAR2(100);
512 BEGIN
513 /* If the column value is of type char, then append the quotes to it */
514 IF (CharColumn) THEN
515 copy_adjust_string(TempValue, ColumnValue);
516 ValueString := '''' || TempValue || '''';
517 ELSE
518 ValueString := ColumnValue;
519 END IF;
520
521 SQLString := 'SELECT ref_table.name '||
522 'FROM '||MainTableName||'@'||G_LinkName||' main_table,' ||
523 RefTableName || '@'|| G_LinkName || ' ref_table ' ||
524 'WHERE main_table.'||ColumnName || '='|| ValueString ||
525 ' AND main_table.'||MainIdName || '= ref_table.'||RefIdName;
526 IF (MainTableName = 'RG_REPORTS') THEN
527 SQLString := SQLString ||
528 ' AND main_table.application_id = '|| TO_CHAR(G_ApplId);
529 END IF;
530
531 display_string(SQLString);
532 CursorId := DBMS_SQL.open_cursor;
533 DBMS_SQL.parse(CursorId, SQLString, DBMS_SQL.v7);
534 DBMS_SQL.define_column(CursorId, 1, RefObjectName, 30);
535 ExecuteValue := DBMS_SQL.execute_and_fetch(CursorId);
536 IF (ExecuteValue > 0) THEN
537 DBMS_SQL.column_value(CursorId, 1, RefObjectName);
538 ELSE
539 RefObjectName := '';
540 END IF;
541 DBMS_SQL.close_cursor(CursorId);
542 RETURN(RefObjectName);
543 END get_source_ref_object_name;
544
545
546 /* Name: get_target_id_from_source_id
547 * Desc: Get the id in the target database given the id in the source
548 * database. For example, this routine get the budget id in the
549 * target database given the budget id in the source database.
550 *
551 * History:
552 * 10/17/95 S Rahman Created.
553 */
554 PROCEDURE get_target_id_from_source_id(
555 TableName VARCHAR2,
556 NameColumn VARCHAR2,
557 IdColumnName VARCHAR2,
558 IdValue IN OUT NOCOPY NUMBER,
559 IdName IN OUT NOCOPY VARCHAR2) IS
560 CursorId INTEGER;
561 ExecuteValue INTEGER;
562 Id NUMBER;
563 SourceIdName VARCHAR2(100);
564 SQLString VARCHAR2(500);
565 BEGIN
566 SQLString := 'SELECT t.'|| IdColumnName || ', l.' || NameColumn ||
567 ' FROM '|| TableName || ' t,' ||
568 TableName || '@' || G_LinkName ||' l' ||
569 ' WHERE l.'|| IdColumnName || '=' || TO_CHAR(IdValue) ||
570 ' AND l.' || NameColumn || '= t.' || NameColumn;
571 display_string(SQLString);
572
573 CursorId := DBMS_SQL.open_cursor;
574 DBMS_SQL.parse(CursorId, SQLString, DBMS_SQL.v7);
575 DBMS_SQL.define_column(CursorId, 1, Id);
576 DBMS_SQL.define_column(CursorId, 2, SourceIdName, 100);
577 ExecuteValue := DBMS_SQL.execute_and_fetch(CursorId);
578 IF (ExecuteValue > 0) THEN
579 DBMS_SQL.column_value(CursorId, 1, Id);
580 DBMS_SQL.column_value(CursorId, 2, SourceIdName);
581 ELSE
582 Id := G_Error;
583
584 /* Get the parameter name from the source database table since it doesn't
585 * exist in the target database table */
586 DBMS_SQL.close_cursor(CursorId);
587
588 SQLString := 'SELECT ' || NameColumn ||
589 ' FROM ' || TableName||'@'||G_LinkName ||
590 ' WHERE '|| IdColumnName || '=' || TO_CHAR(IdValue);
591 display_string(SQLString);
592
593 CursorId := DBMS_SQL.open_cursor;
594 DBMS_SQL.parse(CursorId, SQLString, DBMS_SQL.v7);
595 DBMS_SQL.define_column(CursorId, 1, SourceIdName, 100);
596 ExecuteValue := DBMS_SQL.execute_and_fetch(CursorId);
597 IF (ExecuteValue > 0) THEN
598 DBMS_SQL.column_value(CursorId, 1, SourceIdName);
599 ELSE
600 SourceIdName := NULL;
601 END IF;
602
603 END IF;
604 DBMS_SQL.close_cursor(CursorId);
605 IdValue := Id;
606 IdName := SourceIdName;
607 END get_target_id_from_source_id;
608
609
610 /* Name: get_target_ldg_from_source_ldg
611 * Desc: Get the ledger id in the target database given the ledger id
612 * and ledger currency in the source database.
613 *
614 * History:
615 * 03/31/03 T Cheng Created.
616 */
617 PROCEDURE get_target_ldg_from_source_ldg(
618 LedgerId IN OUT NOCOPY NUMBER,
619 LedgerName IN OUT NOCOPY VARCHAR2,
620 LedgerCurrency IN OUT NOCOPY VARCHAR2) IS
621 CursorId INTEGER;
622 ExecuteValue INTEGER;
623 Id NUMBER;
624 SourceIdName VARCHAR2(100);
625 SQLString VARCHAR2(500);
626 BEGIN
627 IF (LedgerCurrency IS NULL) THEN
628 get_target_id_from_source_id('GL_LEDGERS',
629 'NAME',
630 'LEDGER_ID',
631 LedgerId,
632 LedgerName);
633 LedgerCurrency := 'NULL';
634 RETURN;
635 END IF;
636
637 SQLString := 'SELECT t.TARGET_LEDGER_ID, l.TARGET_LEDGER_NAME ' ||
638 'FROM GL_LEDGER_RELATIONSHIPS t, GL_LEDGER_RELATIONSHIPS@' ||
639 G_LinkName || ' l ' ||
640 'WHERE l.TARGET_LEDGER_ID = ' || TO_CHAR(LedgerId) ||
641 ' AND l.TARGET_CURRENCY_CODE = ''' || LedgerCurrency ||
642 ''' AND l.TARGET_LEDGER_NAME = t.TARGET_LEDGER_NAME';
643 display_string(SQLString);
644
645 CursorId := DBMS_SQL.open_cursor;
646 DBMS_SQL.parse(CursorId, SQLString, DBMS_SQL.v7);
647 DBMS_SQL.define_column(CursorId, 1, Id);
648 DBMS_SQL.define_column(CursorId, 2, SourceIdName, 100);
649 ExecuteValue := DBMS_SQL.execute_and_fetch(CursorId);
650 IF (ExecuteValue > 0) THEN
651 DBMS_SQL.column_value(CursorId, 1, Id);
652 DBMS_SQL.column_value(CursorId, 2, SourceIdName);
653 LedgerCurrency := '''' || LedgerCurrency || '''';
654 ELSE
655 Id := G_Error;
656
657 /* Get the parameter name from the source database table since it doesn't
658 * exist in the target database table */
659 DBMS_SQL.close_cursor(CursorId);
660
661 SQLString := 'SELECT TARGET_LEDGER_NAME ' ||
662 'FROM GL_LEDGER_RELATIONSHIPS@' || G_LinkName ||
663 ' WHERE TARGET_LEDGER_ID = ' || TO_CHAR(LedgerId) ||
664 ' AND TARGET_CURRENCY_CODE = ''' || LedgerCurrency || '''';
665
666 display_string(SQLString);
667
668 CursorId := DBMS_SQL.open_cursor;
669 DBMS_SQL.parse(CursorId, SQLString, DBMS_SQL.v7);
670 DBMS_SQL.define_column(CursorId, 1, SourceIdName, 100);
671 ExecuteValue := DBMS_SQL.execute_and_fetch(CursorId);
672 IF (ExecuteValue > 0) THEN
673 DBMS_SQL.column_value(CursorId, 1, SourceIdName);
674 ELSE
675 SourceIdName := NULL;
676 END IF;
677
678 LedgerCurrency := 'NULL';
679 END IF;
680 DBMS_SQL.close_cursor(CursorId);
681
682 LedgerId := Id;
683 LedgerName := SourceIdName;
684 END get_target_ldg_from_source_ldg;
685
686
687 /* Name: insert_rows
688 * Desc: Insert rows using the provided SQL statement. Binds variable as
689 * necessary.
690 *
691 * History:
692 * 10/17/95 S Rahman Created.
693 */
694 PROCEDURE insert_rows(
695 SQLStmt VARCHAR2,
696 Id NUMBER,
697 UseCOAId BOOLEAN DEFAULT FALSE,
698 UseRowId BOOLEAN DEFAULT FALSE,
699 RecRowId ROWID DEFAULT NULL) IS
700 CursorId INTEGER;
701 ExecuteValue INTEGER;
702 BEGIN
703 display_string(SQLStmt);
704 CursorId := DBMS_SQL.open_cursor;
705 DBMS_SQL.parse(CursorId, SQLStmt, DBMS_SQL.v7);
706 DBMS_SQL.bind_variable(CursorId, ':id', Id);
707 DBMS_SQL.bind_variable(CursorId, ':user_id', G_UserId);
708 DBMS_SQL.bind_variable(CursorId, ':login_id', G_LoginId);
709 IF (UseCOAId) THEN
710 DBMS_SQL.bind_variable(CursorId, ':coa_id', G_TargetCOAId);
711 END IF;
712 IF (UseRowId) THEN
713 DBMS_SQL.bind_variable(CursorId, ':row_id', RecRowId);
714 END IF;
715 ExecuteValue := DBMS_SQL.execute(CursorId);
716 DBMS_SQL.close_cursor(CursorId);
717 END insert_rows;
718
719
720 /* Name: execute_sql_statement
721 * Desc: The name says it all.
722 *
723 * History:
724 * 10/17/95 S Rahman Created.
725 */
726 PROCEDURE execute_sql_statement(SQLStmt VARCHAR2) IS
727 CursorId INTEGER;
728 ExecuteValue INTEGER;
729 BEGIN
730 display_string(SQLStmt);
731 CursorId := DBMS_SQL.open_cursor;
732 DBMS_SQL.parse(CursorId, SQLStmt, DBMS_SQL.v7);
733 ExecuteValue := DBMS_SQL.execute(CursorId);
734 DBMS_SQL.close_cursor(CursorId);
735 END execute_sql_statement;
736
737
738 /* Name: check_coa_id
739 * Desc: Check if the chart of accounts id specified and the chart of
740 * accounts id for the component matches.
741 *
742 * History:
743 * 10/17/95 S Rahman Created.
744 */
745 FUNCTION check_coa_id(
746 TableName VARCHAR2,
747 CompName VARCHAR2,
748 WhereString VARCHAR2 DEFAULT NULL) RETURN NUMBER IS
749 CursorId INTEGER;
750 ExecuteValue INTEGER;
751 COAId NUMBER;
752 RetVal NUMBER := G_NoError;
753 ComponentName VARCHAR2(60);
754 SQLString VARCHAR2(2000);
755 BEGIN
756 copy_adjust_string(ComponentName, CompName);
757 SQLString := 'SELECT structure_id FROM ' || TableName || '@' || G_LinkName||
758 ' WHERE name = ''' || ComponentName ||'''' ||
759 NVL(WhereString,' AND application_id = ' || TO_CHAR(G_ApplId));
760 display_string(SQLString);
761
762 CursorId := DBMS_SQL.open_cursor;
763 DBMS_SQL.parse(CursorId, SQLString, DBMS_SQL.v7);
764 DBMS_SQL.define_column(CursorId, 1, COAId);
765 ExecuteValue := DBMS_SQL.execute_and_fetch(CursorId);
766 DBMS_SQL.column_value(CursorId, 1, COAId);
767 IF (COAId IS NULL) THEN
768 RetVal := G_NoCOA;
769 ELSIF (COAId <> G_SourceCOAId) THEN
770 RetVal := G_Error;
771 display_log(
772 MsgLevel => G_ML_Full,
773 MsgName => 'RG_XFER_L_WRONG_SRC_COA',
774 Token1 => 'SRC_ID',
775 Token1Val => TO_CHAR(COAId),
776 Token2 => 'SUB_SRC_ID',
777 Token2Val => TO_CHAR(G_SourceCOAId));
778 END IF;
779 DBMS_SQL.close_cursor(CursorId);
780 RETURN(RetVal);
781 END check_coa_id;
782
783
784 /* Name: check_target_coa_id
785 * Desc: Check if the chart of accounts id specified and the chart of
786 * accounts id for the component matches.
787 *
788 * History:
789 * 08/08/96 S Rahman Created.
790 */
791 FUNCTION check_target_coa_id(
792 TableName VARCHAR2,
793 CompName VARCHAR2,
794 WhereString VARCHAR2 DEFAULT NULL) RETURN NUMBER IS
795 CursorId INTEGER;
796 ExecuteValue INTEGER;
797 COAId NUMBER;
798 RetVal NUMBER := G_NoError;
799 ComponentName VARCHAR2(60);
800 SQLString VARCHAR2(200);
801 BEGIN
802 copy_adjust_string(ComponentName, CompName);
803 SQLString := 'SELECT structure_id FROM ' || TableName ||
804 ' WHERE name = ''' || ComponentName ||'''' ||
805 NVL(WhereString,' AND application_id = ' || TO_CHAR(G_ApplId));
806 display_string(SQLString);
807
808 CursorId := DBMS_SQL.open_cursor;
809 DBMS_SQL.parse(CursorId, SQLString, DBMS_SQL.v7);
810 DBMS_SQL.define_column(CursorId, 1, COAId);
811 ExecuteValue := DBMS_SQL.execute_and_fetch(CursorId);
812 DBMS_SQL.column_value(CursorId, 1, COAId);
813 IF (COAId IS NULL) THEN
814 RetVal := G_NoCOA;
815 ELSIF (COAId <> G_TargetCOAId) THEN
816 RetVal := G_Error;
817 display_log(
818 MsgLevel => G_ML_Full,
819 MsgName => 'RG_XFER_L_TARGET_COA_MISMATCH',
820 Token1 => 'TARGET_ID',
821 Token1Val => TO_CHAR(COAId),
822 Token2 => 'SUB_TARGET_ID',
823 Token2Val => TO_CHAR(G_TargetCOAId));
824 END IF;
825 DBMS_SQL.close_cursor(CursorId);
826 RETURN(RetVal);
827 END check_target_coa_id;
828
829
830 /* Name: substitute_tokens
831 * Desc: Substitute values for tokens.
832 *
833 * Notes: The tokens must appear in the order passed to this routine.
834 *
835 * History:
836 * 10/17/95 S Rahman Created.
837 */
838 PROCEDURE substitute_tokens(
839 InputStr IN OUT NOCOPY VARCHAR2,
840 Token1 VARCHAR2 DEFAULT NULL,
841 Token1Val VARCHAR2 DEFAULT NULL,
842 Token2 VARCHAR2 DEFAULT NULL,
843 Token2Val VARCHAR2 DEFAULT NULL,
844 Token3 VARCHAR2 DEFAULT NULL,
845 Token3Val VARCHAR2 DEFAULT NULL,
846 Token4 VARCHAR2 DEFAULT NULL,
847 Token4Val VARCHAR2 DEFAULT NULL,
848 Token5 VARCHAR2 DEFAULT NULL,
849 Token5Val VARCHAR2 DEFAULT NULL,
850 Token6 VARCHAR2 DEFAULT NULL,
851 Token6Val VARCHAR2 DEFAULT NULL,
852 Token7 VARCHAR2 DEFAULT NULL,
853 Token7Val VARCHAR2 DEFAULT NULL,
854 Token8 VARCHAR2 DEFAULT NULL,
855 Token8Val VARCHAR2 DEFAULT NULL,
856 Token9 VARCHAR2 DEFAULT NULL,
857 Token9Val VARCHAR2 DEFAULT NULL) IS
858 SubsStr VARCHAR2(12000);
859 CurrPos INTEGER;
860 TempPos INTEGER;
861 BEGIN
862 SubsStr := '';
863 CurrPos := 1;
864 IF (Token1 IS NOT NULL) THEN
865 TempPos := INSTR(InputStr, Token1, CurrPos);
866 IF (TempPos <> 0) THEN
867 SubsStr := SubsStr || SUBSTR(InputStr, CurrPos, TempPos-CurrPos) ||
868 NVL(Token1Val, 'NULL');
869 CurrPos := TempPos + LENGTH(Token1);
870 ELSE
871 display_log(
872 MsgLevel => G_ML_Full,
873 MsgName => 'RG_XFER_L_INVALID',
874 Token1 => 'VALUE',
875 Token1Val => Token1);
876 END IF;
877 END IF;
878 IF (Token2 IS NOT NULL) THEN
879 TempPos := INSTR(InputStr, Token2, CurrPos);
880 IF (TempPos <> 0) THEN
881 SubsStr := SubsStr || SUBSTR(InputStr, CurrPos, TempPos-CurrPos) ||
882 NVL(Token2Val, 'NULL');
883 CurrPos := TempPos + LENGTH(Token2);
884 ELSE
885 display_log(
886 MsgLevel => G_ML_Full,
887 MsgName => 'RG_XFER_L_INVALID',
888 Token1 => 'VALUE',
889 Token1Val => Token2);
890 END IF;
891 END IF;
892 IF (Token3 IS NOT NULL) THEN
893 TempPos := INSTR(InputStr, Token3, CurrPos);
894 IF (TempPos <> 0) THEN
895 SubsStr := SubsStr || SUBSTR(InputStr, CurrPos, TempPos-CurrPos) ||
896 NVL(Token3Val, 'NULL');
897 CurrPos := TempPos + LENGTH(Token3);
898 ELSE
899 display_log(
900 MsgLevel => G_ML_Full,
901 MsgName => 'RG_XFER_L_INVALID',
902 Token1 => 'VALUE',
903 Token1Val => Token3);
904 END IF;
905 END IF;
906 IF (Token4 IS NOT NULL) THEN
907 TempPos := INSTR(InputStr, Token4, CurrPos);
908 IF (TempPos <> 0) THEN
909 SubsStr := SubsStr || SUBSTR(InputStr, CurrPos, TempPos-CurrPos) ||
910 NVL(Token4Val, 'NULL');
911 CurrPos := TempPos + LENGTH(Token4);
912 ELSE
913 display_log(
914 MsgLevel => G_ML_Full,
915 MsgName => 'RG_XFER_L_INVALID',
916 Token1 => 'VALUE',
917 Token1Val => Token4);
918 END IF;
919 END IF;
920 IF (Token5 IS NOT NULL) THEN
921 TempPos := INSTR(InputStr, Token5, CurrPos);
922 IF (TempPos <> 0) THEN
923 SubsStr := SubsStr || SUBSTR(InputStr, CurrPos, TempPos-CurrPos) ||
924 NVL(Token5Val, 'NULL');
925 CurrPos := TempPos + LENGTH(Token5);
926 ELSE
927 display_log(
928 MsgLevel => G_ML_Full,
929 MsgName => 'RG_XFER_L_INVALID',
930 Token1 => 'VALUE',
931 Token1Val => Token5);
932 END IF;
933 END IF;
934 IF (Token6 IS NOT NULL) THEN
935 TempPos := INSTR(InputStr, Token6, CurrPos);
936 IF (TempPos <> 0) THEN
937 SubsStr := SubsStr || SUBSTR(InputStr, CurrPos, TempPos-CurrPos) ||
938 NVL(Token6Val, 'NULL');
939 CurrPos := TempPos + LENGTH(Token6);
940 ELSE
941 display_log(
942 MsgLevel => G_ML_Full,
943 MsgName => 'RG_XFER_L_INVALID',
944 Token1 => 'VALUE',
945 Token1Val => Token6);
946 END IF;
947 END IF;
948 IF (Token7 IS NOT NULL) THEN
949 TempPos := INSTR(InputStr, Token7, CurrPos);
950 IF (TempPos <> 0) THEN
951 SubsStr := SubsStr || SUBSTR(InputStr, CurrPos, TempPos-CurrPos) ||
952 NVL(Token7Val, 'NULL');
953 CurrPos := TempPos + LENGTH(Token7);
954 ELSE
955 display_log(
956 MsgLevel => G_ML_Full,
957 MsgName => 'RG_XFER_L_INVALID',
958 Token1 => 'VALUE',
959 Token1Val => Token7);
960 END IF;
961 END IF;
962 IF (Token8 IS NOT NULL) THEN
963 TempPos := INSTR(InputStr, Token8, CurrPos);
964 IF (TempPos <> 0) THEN
965 SubsStr := SubsStr || SUBSTR(InputStr, CurrPos, TempPos-CurrPos) ||
966 NVL(Token8Val, 'NULL');
967 CurrPos := TempPos + LENGTH(Token8);
968 ELSE
969 display_log(
970 MsgLevel => G_ML_Full,
971 MsgName => 'RG_XFER_L_INVALID',
972 Token1 => 'VALUE',
973 Token1Val => Token8);
974 END IF;
975 END IF;
976 IF (Token9 IS NOT NULL) THEN
977 TempPos := INSTR(InputStr, Token9, CurrPos);
978 IF (TempPos <> 0) THEN
979 SubsStr := SubsStr || SUBSTR(InputStr, CurrPos, TempPos-CurrPos) ||
980 NVL(Token9Val, 'NULL');
981 CurrPos := TempPos + LENGTH(Token9);
982 ELSE
983 display_log(
984 MsgLevel => G_ML_Full,
985 MsgName => 'RG_XFER_L_INVALID',
986 Token1 => 'VALUE',
987 Token1Val => Token9);
988 END IF;
989 END IF;
990 SubsStr := SubsStr || SUBSTR(InputStr, CurrPos);
991 InputStr := SubsStr;
992 END substitute_tokens;
993
994
995 /* Name: source_component_exists
996 * Desc: Check if the specified component exists in the source database.
997 * Return TRUE if it exists, otherwise return FALSE.
998 *
999 * History:
1000 * 10/17/95 S Rahman Created.
1001 */
1002 FUNCTION source_component_exists(
1003 ComponentType VARCHAR2,
1004 CompName VARCHAR2) RETURN BOOLEAN IS
1005 CursorId INTEGER;
1006 ExecuteValue INTEGER;
1007 DummyId NUMBER;
1008 RetVal BOOLEAN := FALSE;
1009 ComponentName VARCHAR2(60);
1010 SQLString VARCHAR2(200) := 'SELECT 1 FROM ';
1011 BEGIN
1012 copy_adjust_string(ComponentName, CompName);
1013
1014 /* Construct SQL string */
1015 IF (ComponentType = 'RG_ROW_SET') THEN
1016 SQLString := SQLString || 'RG_REPORT_AXIS_SETS@' || G_LinkName ||
1017 ' WHERE axis_set_type = ''R''' ||
1018 ' AND application_id = ' || TO_CHAR(G_ApplId);
1019 ELSIF (ComponentType = 'RG_COLUMN_SET') THEN
1020 SQLString := SQLString || 'RG_REPORT_AXIS_SETS@' || G_LinkName ||
1021 ' WHERE axis_set_type = ''C''' ||
1022 ' AND ((application_id = 168)' ||
1023 ' OR (application_id = ' || TO_CHAR(G_ApplId) || '))';
1024 ELSIF (ComponentType = 'RG_CONTENT_SET') THEN
1025 SQLString := SQLString || 'RG_REPORT_CONTENT_SETS@' || G_LinkName ||
1026 ' WHERE application_id = ' || TO_CHAR(G_ApplId);
1027 ELSIF (ComponentType = 'RG_ROW_ORDER') THEN
1028 SQLString := SQLString || 'RG_ROW_ORDERS@' || G_LinkName ||
1029 ' WHERE application_id = ' || TO_CHAR(G_ApplId);
1030 ELSIF (ComponentType = 'RG_DISPLAY_SET') THEN
1031 SQLString := SQLString || 'RG_REPORT_DISPLAY_SETS@' || G_LinkName ||
1032 ' WHERE 1 = 1';
1033 ELSIF (ComponentType = 'RG_DISPLAY_GROUP') THEN
1034 SQLString := SQLString || 'RG_REPORT_DISPLAY_GROUPS@' || G_LinkName ||
1035 ' WHERE 1 = 1';
1036 ELSIF (ComponentType = 'RG_REPORT') THEN
1037 SQLString := SQLString || 'RG_REPORTS@' || G_LinkName ||
1038 ' WHERE application_id = ' || TO_CHAR(G_ApplId);
1039 ELSIF (ComponentType = 'RG_REPORT_SET') THEN
1040 SQLString := SQLString || 'RG_REPORT_SETS@' || G_LinkName ||
1041 ' WHERE application_id = ' || TO_CHAR(G_ApplId);
1042 ELSE
1043 display_log(
1044 MsgLevel => G_ML_Full,
1045 MsgName => 'RG_XFER_L_INVALID',
1046 Token1 => 'VALUE',
1047 Token1Val => ComponentType);
1048 END IF;
1049 SQLString := SQLString || ' AND name = ''' || ComponentName || '''';
1050 display_string(SQLString);
1051
1052 /* Execute the constructed string and return value */
1053 CursorId := DBMS_SQL.open_cursor;
1054 DBMS_SQL.parse(CursorId, SQLString, DBMS_SQL.v7);
1055 DBMS_SQL.define_column(CursorId, 1, DummyId);
1056 ExecuteValue := DBMS_SQL.execute_and_fetch(CursorId);
1057 IF (ExecuteValue > 0) THEN
1058 /* Found matching component */
1059 RetVal := TRUE;
1060 END IF;
1061 DBMS_SQL.close_cursor(CursorId);
1062 RETURN(RetVal);
1063 END source_component_exists;
1064
1065
1066 /* Name: component_exists
1067 * Desc: Check if a component exists in the target database.
1068 *
1069 * History:
1070 * 10/17/95 S Rahman Created.
1071 */
1072 FUNCTION component_exists(SelectString VARCHAR2) RETURN NUMBER IS
1073 CursorId INTEGER;
1074 ExecuteValue INTEGER;
1075 Id NUMBER;
1076 BEGIN
1077 display_string(SelectString);
1078 CursorId := DBMS_SQL.open_cursor;
1079 DBMS_SQL.parse(CursorId, SelectString, DBMS_SQL.v7);
1080 DBMS_SQL.define_column(CursorId, 1, Id);
1081 ExecuteValue := DBMS_SQL.execute_and_fetch(CursorId);
1082 IF (ExecuteValue > 0) THEN
1083 DBMS_SQL.column_value(CursorId, 1, Id);
1084 ELSE
1085 Id := G_Error;
1086 END IF;
1087 DBMS_SQL.close_cursor(CursorId);
1088 RETURN(Id);
1089 END component_exists;
1090
1091
1092 /* Name: currency_exists
1093 * Desc: Check if a currency exists in the target database.
1094 *
1095 * History:
1096 * 10/17/95 S Rahman Created.
1097 */
1098 FUNCTION currency_exists(CurrencyCode VARCHAR2) RETURN BOOLEAN IS
1099 CursorId INTEGER;
1100 ExecuteValue INTEGER;
1101 RetVal BOOLEAN;
1102 Id NUMBER;
1103 SQLString VARCHAR2(500);
1104 BEGIN
1105 SQLString := 'SELECT 1 FROM fnd_currencies' ||
1106 ' WHERE currency_code = ''' || CurrencyCode || '''';
1107 display_string(SQLString);
1108
1109 CursorId := DBMS_SQL.open_cursor;
1110 DBMS_SQL.parse(CursorId, SQLString, DBMS_SQL.v7);
1111 DBMS_SQL.define_column(CursorId, 1, Id);
1112 ExecuteValue := DBMS_SQL.execute_and_fetch(CursorId);
1113 IF (ExecuteValue > 0) THEN
1114 RetVal := TRUE;
1115 ELSE
1116 RetVal := FALSE;
1117 END IF;
1118 DBMS_SQL.close_cursor(CursorId);
1119 RETURN(RetVal);
1120 END currency_exists;
1121
1122
1123 /* Name: ro_column_exists
1124 * Desc: Check if a column specified in a row order exists in the target db.
1125 *
1126 * History:
1127 * 03/08/96 S Rahman Created.
1128 */
1129 FUNCTION ro_column_exists(ColumnName VARCHAR2) RETURN BOOLEAN IS
1130 CursorId INTEGER;
1131 ExecuteValue INTEGER;
1132 RetVal BOOLEAN;
1133 Id NUMBER;
1134 SQLString VARCHAR2(3000);
1135 AdjustedName VARCHAR2(60);
1136 BEGIN
1137 copy_adjust_string(AdjustedName, ColumnName);
1138 SQLString := 'SELECT 1 FROM sys.dual WHERE EXISTS ' ||
1139 '(SELECT 1 FROM rg_report_axes ax, rg_report_axis_sets axs'||
1140 ' WHERE axs.application_id+0 in ('||TO_CHAR(G_ApplId)||',168)'||
1141 ' AND axs.axis_set_type = ''C'''||
1142 ' AND ax.axis_set_id = DECODE(axs.axis_set_type, ''C'',' ||
1143 ' axs.axis_set_id, axs.axis_set_id)' ||
1144 ' AND ax.axis_name = '''||AdjustedName||''')';
1145 display_string(SQLString);
1146
1147 CursorId := DBMS_SQL.open_cursor;
1148 DBMS_SQL.parse(CursorId, SQLString, DBMS_SQL.v7);
1149 DBMS_SQL.define_column(CursorId, 1, Id);
1150 ExecuteValue := DBMS_SQL.execute_and_fetch(CursorId);
1151 IF (ExecuteValue > 0) THEN
1152 RetVal := TRUE;
1153 ELSE
1154 RetVal := FALSE;
1155 END IF;
1156 DBMS_SQL.close_cursor(CursorId);
1157 RETURN(RetVal);
1158 END ro_column_exists;
1159
1160
1161 /* Name: token_from_id
1162 * Desc: Get the token value from the specified id.
1163 *
1164 * History:
1165 * 10/17/95 S Rahman Created.
1166 */
1167 FUNCTION token_from_id(Id NUMBER) RETURN VARCHAR2 IS
1168 BEGIN
1169 IF ((Id = G_Error) OR (Id = G_Warning) OR (Id IS NULL)) THEN
1170 RETURN('NULL');
1171 ELSE
1172 RETURN(TO_CHAR(Id));
1173 END IF;
1174 END token_from_id;
1175
1176
1177 /* Name: get_varchar2
1178 * Desc: Get the varchar2 column value using the specified SQL statement.
1179 *
1180 * History:
1181 * 10/17/95 S Rahman Created.
1182 */
1183 FUNCTION get_varchar2(
1184 SQLString VARCHAR2,
1185 ColumnSize NUMBER) RETURN VARCHAR2 IS
1186 CursorId INTEGER;
1187 ExecuteValue INTEGER;
1188 RetVal VARCHAR2(1000) := NULL;
1189 Id NUMBER;
1190 BEGIN
1191 display_string(SQLString);
1192 CursorId := DBMS_SQL.open_cursor;
1193 DBMS_SQL.parse(CursorId, SQLString, DBMS_SQL.v7);
1194 DBMS_SQL.define_column(CursorId, 1, RetVal, ColumnSize);
1195 ExecuteValue := DBMS_SQL.execute_and_fetch(CursorId);
1196 IF (ExecuteValue > 0) THEN
1197 DBMS_SQL.column_value(CursorId, 1, RetVal);
1198 END IF;
1199 DBMS_SQL.close_cursor(CursorId);
1200 RETURN(RetVal);
1201 END get_varchar2;
1202
1203
1204 BEGIN
1205 /* Initialize variables on package access. */
1206
1207 /* Error and warning status */
1208 FoundError := FALSE;
1209 FoundWarning := FALSE;
1210
1211 /* Error Codes */
1212 G_Error := -1;
1213 G_Warning := -2;
1214 G_NoCOA := -3;
1215 G_NoError := -4;
1216
1217 /* Define the message levels */
1218 G_ML_Minimal := 1;
1219 G_ML_Normal := 2;
1220 G_ML_Full := 3;
1221
1222 END RG_XFER_UTILS_PKG;