[Home] [Help]
PACKAGE BODY: APPS.EDW_SOURCE_INSTANCES_PKG
Source
1 Package Body EDW_SOURCE_INSTANCES_PKG AS
2 /* $Header: EDWSRCIB.pls 115.19 2003/11/19 09:19:36 smulye ship $ */
3
4
5
6 FUNCTION CHECK_REMOTE_ENTRY(p_dblink IN varchar2) return boolean
7 IS
8 dummy number:= 0;
9 l_dummy number;
10 cid number;
11 l_bool boolean := true;
12
13 BEGIN
14
15 /* check if the source already has a row */
16 /* if so, records may
17 already collected with that instance_fk etc
18 so, throw a warning to the user */
19
20
21 /* return false if entry exists in remote db
22 return true if entry does not exist */
23
24 begin
25 edw_misc_util.globalNamesOff;
26 cid := DBMS_SQL.open_cursor;
27 DBMS_SQL.PARSE(cid, 'SELECT count(*) FROM edw_local_instance@'||P_DBLINK, dbms_sql.native);
28 dbms_sql.define_column(cid, 1, dummy);
29 l_dummy:=dbms_sql.execute(cid);
30 if dbms_sql.fetch_rows(cid)<>0 then
31 dbms_sql.column_value(cid, 1, dummy);
32 if (dummy > 0) then /* throw error message */
33 /*fnd_message.set_name('BIS', 'EDW_SOURCE_ALREADY_PRESENT');
34 fnd_message.set_token('NAME',
35 X_WAREHOUSE_TO_INSTANCE_LINK, FALSE);
36 app_exception.raise_exception; */
37 l_bool := false;
38 else
39 l_bool := true;
40 end if;
41 end if;
42
43 return l_bool;
44
45 END ;
46
47 END CHECK_REMOTE_ENTRY;
48
49 procedure INSERT_ROW (
50 X_ROWID in out NOCOPY VARCHAR2,
51 X_INSTANCE_CODE in VARCHAR2,
52 X_ENABLED_FLAG in VARCHAR2,
53 X_WAREHOUSE_TO_INSTANCE_LINK in VARCHAR2,
54 X_NAME in VARCHAR2,
55 X_DESCRIPTION in VARCHAR2,
56 X_CREATION_DATE in DATE,
57 X_CREATED_BY in NUMBER,
58 X_LAST_UPDATE_DATE in DATE,
59 X_LAST_UPDATED_BY in NUMBER,
60 X_LAST_UPDATE_LOGIN in NUMBER
61 ) is
62 dummy number:= 0;
63 l_dummy number;
64 cid number;
65 cursor C is select ROWID from EDW_SOURCE_INSTANCES
66 where INSTANCE_CODE = X_INSTANCE_CODE
67 ;
68
69 begin
70 begin
71
72 insert into EDW_SOURCE_INSTANCES (
73 INSTANCE_CODE,
74 ENABLED_FLAG,
75 WAREHOUSE_TO_INSTANCE_LINK,
76 CREATION_DATE,
77 CREATED_BY,
78 LAST_UPDATE_DATE,
79 LAST_UPDATED_BY,
80 LAST_UPDATE_LOGIN
81 ) values (
82 X_INSTANCE_CODE,
83 X_ENABLED_FLAG,
84 X_WAREHOUSE_TO_INSTANCE_LINK,
85 X_CREATION_DATE,
86 X_CREATED_BY,
87 X_LAST_UPDATE_DATE,
88 X_LAST_UPDATED_BY,
89 X_LAST_UPDATE_LOGIN
90 );
91
92 insert into EDW_SOURCE_INSTANCES_TL (
93 INSTANCE_CODE,
94 NAME,
95 DESCRIPTION,
96 CREATION_DATE,
97 CREATED_BY,
98 LAST_UPDATE_DATE,
99 LAST_UPDATE_LOGIN,
100 LAST_UPDATED_BY,
101 LANGUAGE,
102 SOURCE_LANG
103 ) select
104 X_INSTANCE_CODE,
105 X_NAME,
106 X_DESCRIPTION,
107 X_CREATION_DATE,
108 X_CREATED_BY,
109 X_LAST_UPDATE_DATE,
110 X_LAST_UPDATE_LOGIN,
111 X_LAST_UPDATED_BY,
112 L.LANGUAGE_CODE,
113 userenv('LANG')
114 from FND_LANGUAGES L
115 where L.INSTALLED_FLAG in ('I', 'B')
116 and not exists
117 (select NULL
118 from EDW_SOURCE_INSTANCES_TL T
119 where T.INSTANCE_CODE = X_INSTANCE_CODE
120 and T.LANGUAGE = L.LANGUAGE_CODE);
121
122 open c;
123 fetch c into X_ROWID;
124 if (c%notfound) then
125 close c;
126 raise no_data_found;
127 end if;
128 close c;
129 Exception when others then
130 x_rowid:=null;
131 raise;
132 end;
133 end INSERT_ROW;
134
135 procedure LOCK_ROW (
136 X_INSTANCE_CODE in VARCHAR2,
137 X_ENABLED_FLAG in VARCHAR2,
138 X_WAREHOUSE_TO_INSTANCE_LINK in VARCHAR2,
139 X_NAME in VARCHAR2,
140 X_DESCRIPTION in VARCHAR2
141 ) is
142 cursor c is select
143 ENABLED_FLAG,
144 WAREHOUSE_TO_INSTANCE_LINK,
145 INSTANCE_CODE
146 from EDW_SOURCE_INSTANCES
147 where INSTANCE_CODE = X_INSTANCE_CODE
148 for update of INSTANCE_CODE nowait;
149 recinfo c%rowtype;
150
151 cursor c1 is select
152 NAME,
153 DESCRIPTION,
154 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
155 from EDW_SOURCE_INSTANCES_TL
156 where INSTANCE_CODE = X_INSTANCE_CODE
157 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
158 for update of INSTANCE_CODE nowait;
159 begin
160 open c;
161 fetch c into recinfo;
162 if (c%notfound) then
163 close c;
164 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
165 app_exception.raise_exception;
166 end if;
167 close c;
168 if ( /*((recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
169 OR ((recinfo.ENABLED_FLAG is null)
170 AND (X_ENABLED_FLAG is null)))
171 AND*/ ((recinfo.WAREHOUSE_TO_INSTANCE_LINK = X_WAREHOUSE_TO_INSTANCE_LINK)
172 OR ((recinfo.WAREHOUSE_TO_INSTANCE_LINK is null)
173 AND (X_WAREHOUSE_TO_INSTANCE_LINK is null)))
174 AND ((recinfo.INSTANCE_CODE = X_INSTANCE_CODE)
175 OR ((recinfo.INSTANCE_CODE is null)
176 AND (X_INSTANCE_CODE is null)))
177 ) then
178 null;
179 else
180 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
181 app_exception.raise_exception;
182 end if;
183
184 for tlinfo in c1 loop
185 if (tlinfo.BASELANG = 'Y') then
186 if ( ((tlinfo.NAME = X_NAME)
187 OR ((tlinfo.NAME is null) AND (X_NAME is null)))
188 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
189 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
190 ) then
191 null;
192 else
193 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
194 app_exception.raise_exception;
195 end if;
196 end if;
197 end loop;
198 return;
199 end LOCK_ROW;
200
201 procedure UPDATE_ROW (
202 X_INSTANCE_CODE in VARCHAR2,
203 X_ENABLED_FLAG in VARCHAR2,
204 X_WAREHOUSE_TO_INSTANCE_LINK in VARCHAR2,
205 X_NAME in VARCHAR2,
206 X_DESCRIPTION in VARCHAR2,
207 X_LAST_UPDATE_DATE in DATE,
208 X_LAST_UPDATED_BY in NUMBER,
209 X_LAST_UPDATE_LOGIN in NUMBER
210 ) is
211 begin
212 update EDW_SOURCE_INSTANCES set
213 ENABLED_FLAG = X_ENABLED_FLAG,
214 WAREHOUSE_TO_INSTANCE_LINK = X_WAREHOUSE_TO_INSTANCE_LINK,
215 INSTANCE_CODE = X_INSTANCE_CODE,
216 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
217 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
218 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
219 where INSTANCE_CODE = X_INSTANCE_CODE;
220
221 if (sql%notfound) then
222 raise no_data_found;
223 end if;
224
225 update EDW_SOURCE_INSTANCES_TL set
226 NAME = X_NAME,
227 DESCRIPTION = X_DESCRIPTION,
228 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
229 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
230 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
231 SOURCE_LANG = userenv('LANG')
232 where INSTANCE_CODE = X_INSTANCE_CODE
233 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
234
235 if (sql%notfound) then
236 raise no_data_found;
237 end if;
238 end UPDATE_ROW;
239
240 procedure DELETE_ROW (
241 X_INSTANCE_CODE in VARCHAR2
242 ) is
243 begin
244 delete from EDW_SOURCE_INSTANCES_TL
245 where INSTANCE_CODE = X_INSTANCE_CODE;
246
247 if (sql%notfound) then
248 raise no_data_found;
249 end if;
250
251 delete from EDW_SOURCE_INSTANCES
252 where INSTANCE_CODE = X_INSTANCE_CODE;
253
254 if (sql%notfound) then
255 raise no_data_found;
256 end if;
257 end DELETE_ROW;
258
259 procedure ADD_LANGUAGE is
260 v_sql_stmt VARCHAR2(1000);
261 v_db_link VARCHAR2(240);
262
263 cursor db_links IS
264 select WAREHOUSE_TO_INSTANCE_LINK
265 from EDW_SOURCE_INSTANCES;
266
267 begin
268 delete from EDW_SOURCE_INSTANCES_TL T
269 where not exists
270 (select NULL
271 from EDW_SOURCE_INSTANCES B
272 where B.INSTANCE_CODE = T.INSTANCE_CODE
273 );
274
275 update EDW_SOURCE_INSTANCES_TL T set (
276 NAME,
277 DESCRIPTION
278 ) = (select
279 B.NAME,
280 B.DESCRIPTION
281 from EDW_SOURCE_INSTANCES_TL B
282 where B.INSTANCE_CODE = T.INSTANCE_CODE
283 and B.LANGUAGE = T.SOURCE_LANG)
284 where (
285 T.INSTANCE_CODE,
286 T.LANGUAGE
287 ) in (select
288 SUBT.INSTANCE_CODE,
289 SUBT.LANGUAGE
290 from EDW_SOURCE_INSTANCES_TL SUBB, EDW_SOURCE_INSTANCES_TL SUBT
291 where SUBB.INSTANCE_CODE = SUBT.INSTANCE_CODE
292 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
293 and (SUBB.NAME <> SUBT.NAME
294 or (SUBB.NAME is null and SUBT.NAME is not null)
295 or (SUBB.NAME is not null and SUBT.NAME is null)
296 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
297 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
298 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
299 ));
300
301 insert into EDW_SOURCE_INSTANCES_TL (
302 INSTANCE_CODE,
303 NAME,
304 DESCRIPTION,
305 CREATION_DATE,
306 CREATED_BY,
307 LAST_UPDATE_DATE,
308 LAST_UPDATE_LOGIN,
309 LAST_UPDATED_BY,
310 LANGUAGE,
311 SOURCE_LANG
312 ) select
313 B.INSTANCE_CODE,
314 B.NAME,
315 B.DESCRIPTION,
316 B.CREATION_DATE,
317 B.CREATED_BY,
318 B.LAST_UPDATE_DATE,
319 B.LAST_UPDATE_LOGIN,
320 B.LAST_UPDATED_BY,
321 L.LANGUAGE_CODE,
322 B.SOURCE_LANG
323 from EDW_SOURCE_INSTANCES_TL B, FND_LANGUAGES L
324 where L.INSTALLED_FLAG in ('I', 'B')
325 and B.LANGUAGE = userenv('LANG')
326 and not exists
327 (select NULL
328 from EDW_SOURCE_INSTANCES_TL T
329 where T.INSTANCE_CODE = B.INSTANCE_CODE
330 and T.LANGUAGE = L.LANGUAGE_CODE);
331
332 ---------------------------------------------------------------------
333 -- After this, copy records to EDW_LOCAL_INSTANCE_TL at each instance.
334 -- This is for the fix of bug 2125924
335 ---------------------------------------------------------------------
336
337 edw_misc_util.globalNamesOff;
338
339 FOR db_link_to_local IN db_links LOOP
340 v_db_link := db_link_to_local.WAREHOUSE_TO_INSTANCE_LINK;
341 v_sql_stmt := 'delete from EDW_LOCAL_INSTANCE_TL@' || v_db_link;
342
343 BEGIN
344 EXECUTE IMMEDIATE v_sql_stmt;
345
346 v_sql_stmt := 'insert into EDW_LOCAL_INSTANCE_TL@' || v_db_link ||
347 ' select * from EDW_SOURCE_INSTANCES_TL ' ||
348 ' where INSTANCE_CODE = ' ||
349 '(select distinct INSTANCE_CODE ' ||
350 ' from EDW_SOURCE_INSTANCES ' ||
351 ' where WAREHOUSE_TO_INSTANCE_LINK = '''||v_db_link||''')';
352
353 EXECUTE IMMEDIATE v_sql_stmt;
354 EXCEPTION /* this should not happen */
355 WHEN OTHERS THEN NULL;
356 END;
357 END LOOP;
358
359
360 -- we also need to consider the case that no db_link pointing to warehouse itself,
361 -- so we repeat the above for EDW_LOCAL_INSTANCE_TL in the warehouse.
362
363 BEGIN
364 delete from EDW_LOCAL_INSTANCE_TL;
365
366 insert into EDW_LOCAL_INSTANCE_TL(
367 INSTANCE_CODE,
368 NAME,
369 DESCRIPTION,
370 CREATION_DATE,
371 CREATED_BY,
372 LAST_UPDATE_DATE,
373 LAST_UPDATE_LOGIN,
374 LAST_UPDATED_BY,
375 LANGUAGE,
376 SOURCE_LANG)
377 select
378 INSTANCE_CODE,
379 NAME,
380 DESCRIPTION,
381 CREATION_DATE,
382 CREATED_BY,
383 LAST_UPDATE_DATE,
384 LAST_UPDATE_LOGIN,
385 LAST_UPDATED_BY,
386 LANGUAGE,
387 SOURCE_LANG
388 from EDW_SOURCE_INSTANCES_TL
389 where INSTANCE_CODE =
390 (select distinct INSTANCE_CODE from EDW_LOCAL_INSTANCE);
391
392 EXCEPTION /* this should not happen */
393 WHEN OTHERS THEN NULL;
394 END;
395
396 -------------------------------------------------------------------------
397
398 end ADD_LANGUAGE;
399
400 procedure CHECK_UNIQUE (
401 X_ROWID in VARCHAR2,
402 X_INSTANCE_CODE in VARCHAR2
403 ) IS
404 dummy NUMBER;
405 BEGIN
406 SELECT COUNT(1) INTO dummy
407 FROM EDW_SOURCE_INSTANCES_VL
408 WHERE INSTANCE_CODE = X_INSTANCE_CODE
409 AND ((X_ROWID IS NULL) OR (ROW_ID <> X_ROWID));
410
411 IF (dummy >= 1) THEN
412 fnd_message.set_name('BIS', 'EDW_DUPLICATE_INSTANCE');
413 fnd_message.set_token('INSTANCE_CODE',
414 x_instance_code, FALSE);
415 app_exception.raise_exception;
416 END IF;
417
418 END CHECK_UNIQUE;
419
420 procedure CHECK_REFERENCES (
421 X_INSTANCE_CODE in VARCHAR2
422 ) IS
423 dummy NUMBER;
424 BEGIN
425 SELECT 1 INTO dummy FROM DUAL WHERE NOT EXISTS
426 (SELECT 1 FROM edw_push_detail_log
427 WHERE INSTANCE_CODE = X_INSTANCE_CODE);
428
429 EXCEPTION
430 WHEN NO_DATA_FOUND THEN
431 fnd_message.set_name('BIS', 'EDW_INSTANCE_IN_USE');
432 app_exception.raise_exception;
433 END CHECK_REFERENCES;
434
435
436 procedure CHECK_UNIQUE_NAME (
437 X_ROWID in VARCHAR2,
438 X_INSTANCE_NAME in VARCHAR2
439 ) IS
440 dummy NUMBER;
441 BEGIN
442 /* will fail cos the rowid is for edw_source_instances and NOT tl */
443 SELECT count(*) INTO dummy
444 FROM EDW_SOURCE_INSTANCES_VL
445 WHERE NAME = X_INSTANCE_NAME
446 AND ((X_ROWID IS NULL) OR (ROW_ID <> X_ROWID));
447
448
449
450 IF (dummy >= 1) THEN
451 fnd_message.set_name('BIS', 'EDW_DUPLICATE_INSTANCE_NAME');
452 fnd_message.set_token('INSTANCE_CODE', X_ROWID, FALSE);
453 --x_instance_name, FALSE);
454 app_exception.raise_exception;
455 END IF;
456 END CHECK_UNIQUE_NAME;
457
458 procedure CHECK_UNIQUE_DBLINK (
459 X_ROWID in VARCHAR2,
460 X_DBLINK in VARCHAR2
461 ) IS
462 dummy NUMBER;
463 BEGIN
467 AND ((X_ROWID IS NULL) OR (ROW_ID <> X_ROWID));
464 SELECT COUNT(1) INTO dummy
465 FROM EDW_SOURCE_INSTANCES_VL
466 WHERE WAREHOUSE_TO_INSTANCE_LINK = X_DBLINK
468
469 IF (dummy >= 1) THEN
470 fnd_message.set_name('BIS', 'EDW_DBLINK_ALREADY_USED');
471 fnd_message.set_token('DBLINK',
472 x_dblink, FALSE);
473 app_exception.raise_exception;
474 END IF;
475 END CHECK_UNIQUE_DBLINK;
476
477
478 procedure INSERT_ROW_REMOTE (
479 X_INSTANCE_CODE in VARCHAR2,
480 X_ENABLED_FLAG in VARCHAR2,
481 X_WAREHOUSE_TO_INSTANCE_LINK in VARCHAR2,
482 X_NAME in VARCHAR2,
483 X_DESCRIPTION in VARCHAR2,
484 X_CREATION_DATE in DATE,
485 X_CREATED_BY in NUMBER,
486 X_LAST_UPDATE_DATE in DATE,
487 X_LAST_UPDATED_BY in NUMBER,
488 X_LAST_UPDATE_LOGIN in NUMBER
489 ) IS
490 v_cursor_id NUMBER;
491 v_ret_code NUMBER;
492 v_sql_stmt VARCHAR2(1000);
493 v_source_lang VARCHAR2(40);
494 cid NUMBER;
495 remote_date date;
496
497 BEGIN
498 edw_misc_util.globalNamesOff;
499
500 cid := DBMS_SQL.open_cursor;
501 DBMS_SQL.PARSE(cid, 'SELECT sysdate FROM dual@'||X_WAREHOUSE_TO_INSTANCE_LINK, dbms_sql.native);
502 dbms_sql.define_column(cid, 1, remote_date);
503 v_ret_code:=dbms_sql.execute(cid);
504 if dbms_sql.fetch_rows(cid)<>0 then
505 dbms_sql.column_value(cid, 1, remote_date);
506 end if;
507 DBMS_SQL.close_cursor(cid);
508
509 delete_row_remote(x_instance_code, X_WAREHOUSE_TO_INSTANCE_LINK);
510
511 v_sql_stmt := 'INSERT INTO edw_local_instance@' ||
512 x_warehouse_to_instance_link ||
513 '(instance_code, enabled_flag, creation_date, created_by,
514 last_update_date, last_updated_by, last_update_login,
515 warehouse_to_instance_link)
516 values (:x1, :x2, :x3, :x4, :x5, :x6, :x7, :x8)';
517 v_cursor_id := dbms_sql.open_cursor;
518 dbms_sql.parse(v_cursor_id, v_sql_stmt, DBMS_SQL.V7);
519 dbms_sql.bind_variable(v_cursor_id, ':x1', x_instance_code);
520 dbms_sql.bind_variable(v_cursor_id, ':x2', x_enabled_flag);
521 dbms_sql.bind_variable(v_cursor_id, ':x3', remote_date);
522 dbms_sql.bind_variable(v_cursor_id, ':x4', x_created_by);
523 dbms_sql.bind_variable(v_cursor_id, ':x5', remote_date);
524 dbms_sql.bind_variable(v_cursor_id, ':x6', x_last_updated_by);
525 dbms_sql.bind_variable(v_cursor_id, ':x7', x_last_update_login);
526 dbms_sql.bind_variable(v_cursor_id, ':x8', x_warehouse_to_instance_link);
527 v_ret_code := dbms_sql.execute(v_cursor_id);
528 dbms_sql.close_cursor(v_cursor_id);
529
530 SELECT userenv('LANG') INTO v_source_lang
531 FROM dual;
532
533 v_sql_stmt := 'insert into EDW_LOCAL_INSTANCE_TL@' ||
534 x_warehouse_to_instance_link ||
535 '(INSTANCE_CODE, NAME, DESCRIPTION, CREATION_DATE, CREATED_BY,
536 LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, LAST_UPDATED_BY, LANGUAGE,
537 SOURCE_LANG) select :x1, :x2, :x3, :x4, :x5, :x6, :x7, :x8,
538 L.LANGUAGE_CODE, :x9 from FND_LANGUAGES@' ||
539 x_warehouse_to_instance_link ||
540 ' L where L.INSTALLED_FLAG in (''I'', ''B'')
541 and not exists (select NULL from EDW_LOCAL_INSTANCE_TL@' ||
542 x_warehouse_to_instance_link ||
543 ' T where T.INSTANCE_CODE = :x1 and T.LANGUAGE = L.LANGUAGE_CODE)';
544
545 v_cursor_id := dbms_sql.open_cursor;
546 dbms_sql.parse(v_cursor_id, v_sql_stmt, DBMS_SQL.V7);
547 dbms_sql.bind_variable(v_cursor_id, ':x1', x_instance_code);
548 dbms_sql.bind_variable(v_cursor_id, ':x2', x_name);
549 dbms_sql.bind_variable(v_cursor_id, ':x3', x_description);
550 dbms_sql.bind_variable(v_cursor_id, ':x4', remote_date);
551 dbms_sql.bind_variable(v_cursor_id, ':x5', x_created_by);
552 dbms_sql.bind_variable(v_cursor_id, ':x6', remote_date);
553 dbms_sql.bind_variable(v_cursor_id, ':x7', x_last_updated_by);
554 dbms_sql.bind_variable(v_cursor_id, ':x8', x_last_update_login);
555 dbms_sql.bind_variable(v_cursor_id, ':x9', v_source_lang);
556 v_ret_code := dbms_sql.execute(v_cursor_id);
557 dbms_sql.close_cursor(v_cursor_id);
558
559 EXCEPTION
560 WHEN e_too_many_local_instances THEN
561 fnd_message.set_name('BIS', 'EDW_TOO_MANY_LOCAL_INSTANCES');
562 app_exception.raise_exception;
563 WHEN others THEN
564 IF (X_ENABLED_FLAG = 'N') THEN
565 null;
566 ELSE
567 app_exception.raise_exception;
568 END IF;
569 END INSERT_ROW_REMOTE;
570
571 procedure DELETE_ROW_REMOTE (
572 X_INSTANCE_CODE in VARCHAR2,
573 X_WAREHOUSE_TO_INSTANCE_LINK in VARCHAR2
574 ) IS
575 v_cursor_id NUMBER;
576 v_ret_code NUMBER;
577 v_sql_stmt VARCHAR2(240);
578
579 BEGIN
580
581 edw_misc_util.globalNamesOff;
582 v_sql_stmt := 'delete from EDW_LOCAL_INSTANCE@' ||
583 x_warehouse_to_instance_link;
584 v_cursor_id := dbms_sql.open_cursor;
585 dbms_sql.parse(v_cursor_id, v_sql_stmt, DBMS_SQL.V7);
586 --dbms_sql.bind_variable(v_cursor_id, ':x1', x_instance_code);
587 v_ret_code := dbms_sql.execute(v_cursor_id);
588 dbms_sql.close_cursor(v_cursor_id);
589
590 v_sql_stmt := 'delete from EDW_LOCAL_INSTANCE_TL@' ||
591 x_warehouse_to_instance_link ;
592 v_cursor_id := dbms_sql.open_cursor;
596 dbms_sql.close_cursor(v_cursor_id);
593 dbms_sql.parse(v_cursor_id, v_sql_stmt, DBMS_SQL.V7);
594 --dbms_sql.bind_variable(v_cursor_id, ':x1', x_instance_code);
595 v_ret_code := dbms_sql.execute(v_cursor_id);
597 END DELETE_ROW_REMOTE;
598
599 END EDW_SOURCE_INSTANCES_PKG;