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