APPS.EDW_VBH_SETUP dependencies on DBMS_SQL

Line 51: l_cursor_id:=DBMS_SQL.OPEN_CURSOR;

47: loop
48: fetch l_source_instances_cur into l_instance_code,l_instance_link;
49: exit when l_source_instances_cur%NOTFOUND;
50:
51: l_cursor_id:=DBMS_SQL.OPEN_CURSOR;
52: l_insert_stmt:= 'insert into edw_set_of_books(EDW_SET_OF_BOOKS_ID,
53: instance,
54: SET_OF_BOOKS_ID,
55: SET_OF_BOOKS_NAME ,

Line 73: DBMS_SQL.parse(l_cursor_id,l_insert_stmt,DBMS_SQL.V7);

69: fnd_global.user_id,
70: fnd_global.login_id
71: from gl_sets_of_books@'||l_instance_link
72: ||' where CHART_OF_ACCOUNTS_ID in (select distinct STRUCTURE_NUM from edw_flex_seg_mappings_v where instance_code=:b_instance_code)';
73: DBMS_SQL.parse(l_cursor_id,l_insert_stmt,DBMS_SQL.V7);
74: DBMS_SQL.bind_variable(l_cursor_id,':b_instance_code',l_instance_code);
75: l_rows_inserted:=DBMS_SQL.execute(l_cursor_id);
76: DBMS_SQL.close_cursor(l_cursor_id);
77: commit;

Line 74: DBMS_SQL.bind_variable(l_cursor_id,':b_instance_code',l_instance_code);

70: fnd_global.login_id
71: from gl_sets_of_books@'||l_instance_link
72: ||' where CHART_OF_ACCOUNTS_ID in (select distinct STRUCTURE_NUM from edw_flex_seg_mappings_v where instance_code=:b_instance_code)';
73: DBMS_SQL.parse(l_cursor_id,l_insert_stmt,DBMS_SQL.V7);
74: DBMS_SQL.bind_variable(l_cursor_id,':b_instance_code',l_instance_code);
75: l_rows_inserted:=DBMS_SQL.execute(l_cursor_id);
76: DBMS_SQL.close_cursor(l_cursor_id);
77: commit;
78: end loop;

Line 75: l_rows_inserted:=DBMS_SQL.execute(l_cursor_id);

71: from gl_sets_of_books@'||l_instance_link
72: ||' where CHART_OF_ACCOUNTS_ID in (select distinct STRUCTURE_NUM from edw_flex_seg_mappings_v where instance_code=:b_instance_code)';
73: DBMS_SQL.parse(l_cursor_id,l_insert_stmt,DBMS_SQL.V7);
74: DBMS_SQL.bind_variable(l_cursor_id,':b_instance_code',l_instance_code);
75: l_rows_inserted:=DBMS_SQL.execute(l_cursor_id);
76: DBMS_SQL.close_cursor(l_cursor_id);
77: commit;
78: end loop;
79: close l_source_instances_cur;

Line 76: DBMS_SQL.close_cursor(l_cursor_id);

72: ||' where CHART_OF_ACCOUNTS_ID in (select distinct STRUCTURE_NUM from edw_flex_seg_mappings_v where instance_code=:b_instance_code)';
73: DBMS_SQL.parse(l_cursor_id,l_insert_stmt,DBMS_SQL.V7);
74: DBMS_SQL.bind_variable(l_cursor_id,':b_instance_code',l_instance_code);
75: l_rows_inserted:=DBMS_SQL.execute(l_cursor_id);
76: DBMS_SQL.close_cursor(l_cursor_id);
77: commit;
78: end loop;
79: close l_source_instances_cur;
80: exception

Line 175: l_delete_cursor_id:=DBMS_SQL.OPEN_CURSOR;

171: loop
172: fetch l_source_instances_cur into l_instance_link;
173: exit when l_source_instances_cur%NOTFOUND;
174:
175: l_delete_cursor_id:=DBMS_SQL.OPEN_CURSOR;
176: l_delete_stmt:='delete from EDW_LOCAL_CONS_SET_OF_BOOKS@'||l_instance_link;
177: DBMS_SQL.parse(l_delete_cursor_id,l_delete_stmt,DBMS_SQL.V7);
178: l_rows_deleted:=DBMS_SQL.execute(l_delete_cursor_id);
179: commit;

Line 177: DBMS_SQL.parse(l_delete_cursor_id,l_delete_stmt,DBMS_SQL.V7);

173: exit when l_source_instances_cur%NOTFOUND;
174:
175: l_delete_cursor_id:=DBMS_SQL.OPEN_CURSOR;
176: l_delete_stmt:='delete from EDW_LOCAL_CONS_SET_OF_BOOKS@'||l_instance_link;
177: DBMS_SQL.parse(l_delete_cursor_id,l_delete_stmt,DBMS_SQL.V7);
178: l_rows_deleted:=DBMS_SQL.execute(l_delete_cursor_id);
179: commit;
180: DBMS_SQL.close_cursor(l_delete_cursor_id);
181:

Line 178: l_rows_deleted:=DBMS_SQL.execute(l_delete_cursor_id);

174:
175: l_delete_cursor_id:=DBMS_SQL.OPEN_CURSOR;
176: l_delete_stmt:='delete from EDW_LOCAL_CONS_SET_OF_BOOKS@'||l_instance_link;
177: DBMS_SQL.parse(l_delete_cursor_id,l_delete_stmt,DBMS_SQL.V7);
178: l_rows_deleted:=DBMS_SQL.execute(l_delete_cursor_id);
179: commit;
180: DBMS_SQL.close_cursor(l_delete_cursor_id);
181:
182: l_insert_cursor_id:=DBMS_SQL.OPEN_CURSOR;

Line 180: DBMS_SQL.close_cursor(l_delete_cursor_id);

176: l_delete_stmt:='delete from EDW_LOCAL_CONS_SET_OF_BOOKS@'||l_instance_link;
177: DBMS_SQL.parse(l_delete_cursor_id,l_delete_stmt,DBMS_SQL.V7);
178: l_rows_deleted:=DBMS_SQL.execute(l_delete_cursor_id);
179: commit;
180: DBMS_SQL.close_cursor(l_delete_cursor_id);
181:
182: l_insert_cursor_id:=DBMS_SQL.OPEN_CURSOR;
183: l_insert_stmt:= 'insert into EDW_LOCAL_CONS_SET_OF_BOOKS@'||l_instance_link||'
184: (child_EDW_SET_OF_BOOKS_ID,

Line 182: l_insert_cursor_id:=DBMS_SQL.OPEN_CURSOR;

178: l_rows_deleted:=DBMS_SQL.execute(l_delete_cursor_id);
179: commit;
180: DBMS_SQL.close_cursor(l_delete_cursor_id);
181:
182: l_insert_cursor_id:=DBMS_SQL.OPEN_CURSOR;
183: l_insert_stmt:= 'insert into EDW_LOCAL_CONS_SET_OF_BOOKS@'||l_instance_link||'
184: (child_EDW_SET_OF_BOOKS_ID,
185: parent_edw_SET_OF_BOOKS_ID,
186: consolidation_id,

Line 204: DBMS_SQL.parse(l_insert_cursor_id,l_insert_stmt,DBMS_SQL.V7);

200: LAST_UPDATE_DATE,
201: LAST_UPDATED_BY ,
202: LAST_UPDATE_LOGIN
203: from edw_cons_set_of_books';
204: DBMS_SQL.parse(l_insert_cursor_id,l_insert_stmt,DBMS_SQL.V7);
205: l_rows_inserted:=DBMS_SQL.execute(l_insert_cursor_id);
206: commit;
207: DBMS_SQL.close_cursor(l_insert_cursor_id);
208: end loop;

Line 205: l_rows_inserted:=DBMS_SQL.execute(l_insert_cursor_id);

201: LAST_UPDATED_BY ,
202: LAST_UPDATE_LOGIN
203: from edw_cons_set_of_books';
204: DBMS_SQL.parse(l_insert_cursor_id,l_insert_stmt,DBMS_SQL.V7);
205: l_rows_inserted:=DBMS_SQL.execute(l_insert_cursor_id);
206: commit;
207: DBMS_SQL.close_cursor(l_insert_cursor_id);
208: end loop;
209: close l_source_instances_cur;

Line 207: DBMS_SQL.close_cursor(l_insert_cursor_id);

203: from edw_cons_set_of_books';
204: DBMS_SQL.parse(l_insert_cursor_id,l_insert_stmt,DBMS_SQL.V7);
205: l_rows_inserted:=DBMS_SQL.execute(l_insert_cursor_id);
206: commit;
207: DBMS_SQL.close_cursor(l_insert_cursor_id);
208: end loop;
209: close l_source_instances_cur;
210:
211: exception

Line 243: l_delete_cursor_id:=DBMS_SQL.OPEN_CURSOR;

239: loop
240: fetch l_source_instances_cur into l_instance_link;
241: exit when l_source_instances_cur%NOTFOUND;
242:
243: l_delete_cursor_id:=DBMS_SQL.OPEN_CURSOR;
244: l_delete_stmt:='delete from EDW_LOCAL_EQUI_SET_OF_BOOKS@'||l_instance_link;
245: DBMS_SQL.parse(l_delete_cursor_id,l_delete_stmt,DBMS_SQL.V7);
246: l_rows_deleted:=DBMS_SQL.execute(l_delete_cursor_id);
247: commit;

Line 245: DBMS_SQL.parse(l_delete_cursor_id,l_delete_stmt,DBMS_SQL.V7);

241: exit when l_source_instances_cur%NOTFOUND;
242:
243: l_delete_cursor_id:=DBMS_SQL.OPEN_CURSOR;
244: l_delete_stmt:='delete from EDW_LOCAL_EQUI_SET_OF_BOOKS@'||l_instance_link;
245: DBMS_SQL.parse(l_delete_cursor_id,l_delete_stmt,DBMS_SQL.V7);
246: l_rows_deleted:=DBMS_SQL.execute(l_delete_cursor_id);
247: commit;
248: DBMS_SQL.close_cursor(l_delete_cursor_id);
249:

Line 246: l_rows_deleted:=DBMS_SQL.execute(l_delete_cursor_id);

242:
243: l_delete_cursor_id:=DBMS_SQL.OPEN_CURSOR;
244: l_delete_stmt:='delete from EDW_LOCAL_EQUI_SET_OF_BOOKS@'||l_instance_link;
245: DBMS_SQL.parse(l_delete_cursor_id,l_delete_stmt,DBMS_SQL.V7);
246: l_rows_deleted:=DBMS_SQL.execute(l_delete_cursor_id);
247: commit;
248: DBMS_SQL.close_cursor(l_delete_cursor_id);
249:
250: l_insert_cursor_id:=DBMS_SQL.OPEN_CURSOR;

Line 248: DBMS_SQL.close_cursor(l_delete_cursor_id);

244: l_delete_stmt:='delete from EDW_LOCAL_EQUI_SET_OF_BOOKS@'||l_instance_link;
245: DBMS_SQL.parse(l_delete_cursor_id,l_delete_stmt,DBMS_SQL.V7);
246: l_rows_deleted:=DBMS_SQL.execute(l_delete_cursor_id);
247: commit;
248: DBMS_SQL.close_cursor(l_delete_cursor_id);
249:
250: l_insert_cursor_id:=DBMS_SQL.OPEN_CURSOR;
251: l_insert_stmt:= 'insert into EDW_LOCAL_EQUI_SET_OF_BOOKS@'||l_instance_link||'
252: (EDW_SET_OF_BOOKS_ID,

Line 250: l_insert_cursor_id:=DBMS_SQL.OPEN_CURSOR;

246: l_rows_deleted:=DBMS_SQL.execute(l_delete_cursor_id);
247: commit;
248: DBMS_SQL.close_cursor(l_delete_cursor_id);
249:
250: l_insert_cursor_id:=DBMS_SQL.OPEN_CURSOR;
251: l_insert_stmt:= 'insert into EDW_LOCAL_EQUI_SET_OF_BOOKS@'||l_instance_link||'
252: (EDW_SET_OF_BOOKS_ID,
253: equi_SET_OF_BOOKS_ID,
254: CREATION_DATE,

Line 268: DBMS_SQL.parse(l_insert_cursor_id,l_insert_stmt,DBMS_SQL.V7);

264: LAST_UPDATE_DATE,
265: LAST_UPDATED_BY ,
266: LAST_UPDATE_LOGIN
267: from edw_equi_set_of_books';
268: DBMS_SQL.parse(l_insert_cursor_id,l_insert_stmt,DBMS_SQL.V7);
269: l_rows_inserted:=DBMS_SQL.execute(l_insert_cursor_id);
270: commit;
271: DBMS_SQL.close_cursor(l_insert_cursor_id);
272: end loop;

Line 269: l_rows_inserted:=DBMS_SQL.execute(l_insert_cursor_id);

265: LAST_UPDATED_BY ,
266: LAST_UPDATE_LOGIN
267: from edw_equi_set_of_books';
268: DBMS_SQL.parse(l_insert_cursor_id,l_insert_stmt,DBMS_SQL.V7);
269: l_rows_inserted:=DBMS_SQL.execute(l_insert_cursor_id);
270: commit;
271: DBMS_SQL.close_cursor(l_insert_cursor_id);
272: end loop;
273: close l_source_instances_cur;

Line 271: DBMS_SQL.close_cursor(l_insert_cursor_id);

267: from edw_equi_set_of_books';
268: DBMS_SQL.parse(l_insert_cursor_id,l_insert_stmt,DBMS_SQL.V7);
269: l_rows_inserted:=DBMS_SQL.execute(l_insert_cursor_id);
270: commit;
271: DBMS_SQL.close_cursor(l_insert_cursor_id);
272: end loop;
273: close l_source_instances_cur;
274:
275: exception

Line 302: l_cursor_id:=dbms_sql.open_cursor;

298:
299: l_stmt:='alter session set global_names = FALSE';
300: execute immediate l_stmt;
301:
302: l_cursor_id:=dbms_sql.open_cursor;
303: l_select_stmt :=
304: 'select set_of_books_id, chart_of_accounts_id, description
305: from gl_sets_of_books@'||p_db_link||'
306: where name = :b_sob_name';

Line 307: dbms_sql.parse(l_cursor_id,l_select_stmt,dbms_sql.v7);

303: l_select_stmt :=
304: 'select set_of_books_id, chart_of_accounts_id, description
305: from gl_sets_of_books@'||p_db_link||'
306: where name = :b_sob_name';
307: dbms_sql.parse(l_cursor_id,l_select_stmt,dbms_sql.v7);
308: dbms_sql.bind_variable(l_cursor_id,':b_sob_name',p_sob_name);
309: dbms_sql.define_column(l_cursor_id,1,l_sob_id);
310: dbms_sql.define_column(l_cursor_id,2,l_coa_id);
311: dbms_sql.define_column(l_cursor_id,3,l_description,240);

Line 308: dbms_sql.bind_variable(l_cursor_id,':b_sob_name',p_sob_name);

304: 'select set_of_books_id, chart_of_accounts_id, description
305: from gl_sets_of_books@'||p_db_link||'
306: where name = :b_sob_name';
307: dbms_sql.parse(l_cursor_id,l_select_stmt,dbms_sql.v7);
308: dbms_sql.bind_variable(l_cursor_id,':b_sob_name',p_sob_name);
309: dbms_sql.define_column(l_cursor_id,1,l_sob_id);
310: dbms_sql.define_column(l_cursor_id,2,l_coa_id);
311: dbms_sql.define_column(l_cursor_id,3,l_description,240);
312: l_rows_selected:= dbms_sql.execute(l_cursor_id);

Line 309: dbms_sql.define_column(l_cursor_id,1,l_sob_id);

305: from gl_sets_of_books@'||p_db_link||'
306: where name = :b_sob_name';
307: dbms_sql.parse(l_cursor_id,l_select_stmt,dbms_sql.v7);
308: dbms_sql.bind_variable(l_cursor_id,':b_sob_name',p_sob_name);
309: dbms_sql.define_column(l_cursor_id,1,l_sob_id);
310: dbms_sql.define_column(l_cursor_id,2,l_coa_id);
311: dbms_sql.define_column(l_cursor_id,3,l_description,240);
312: l_rows_selected:= dbms_sql.execute(l_cursor_id);
313: if(dbms_sql.fetch_rows(l_cursor_id)=0) then

Line 310: dbms_sql.define_column(l_cursor_id,2,l_coa_id);

306: where name = :b_sob_name';
307: dbms_sql.parse(l_cursor_id,l_select_stmt,dbms_sql.v7);
308: dbms_sql.bind_variable(l_cursor_id,':b_sob_name',p_sob_name);
309: dbms_sql.define_column(l_cursor_id,1,l_sob_id);
310: dbms_sql.define_column(l_cursor_id,2,l_coa_id);
311: dbms_sql.define_column(l_cursor_id,3,l_description,240);
312: l_rows_selected:= dbms_sql.execute(l_cursor_id);
313: if(dbms_sql.fetch_rows(l_cursor_id)=0) then
314: p_status:=false;

Line 311: dbms_sql.define_column(l_cursor_id,3,l_description,240);

307: dbms_sql.parse(l_cursor_id,l_select_stmt,dbms_sql.v7);
308: dbms_sql.bind_variable(l_cursor_id,':b_sob_name',p_sob_name);
309: dbms_sql.define_column(l_cursor_id,1,l_sob_id);
310: dbms_sql.define_column(l_cursor_id,2,l_coa_id);
311: dbms_sql.define_column(l_cursor_id,3,l_description,240);
312: l_rows_selected:= dbms_sql.execute(l_cursor_id);
313: if(dbms_sql.fetch_rows(l_cursor_id)=0) then
314: p_status:=false;
315: fnd_message.set_name('BIS','EDW_NODATA_SET_OF_BOOKS');

Line 312: l_rows_selected:= dbms_sql.execute(l_cursor_id);

308: dbms_sql.bind_variable(l_cursor_id,':b_sob_name',p_sob_name);
309: dbms_sql.define_column(l_cursor_id,1,l_sob_id);
310: dbms_sql.define_column(l_cursor_id,2,l_coa_id);
311: dbms_sql.define_column(l_cursor_id,3,l_description,240);
312: l_rows_selected:= dbms_sql.execute(l_cursor_id);
313: if(dbms_sql.fetch_rows(l_cursor_id)=0) then
314: p_status:=false;
315: fnd_message.set_name('BIS','EDW_NODATA_SET_OF_BOOKS');
316: fnd_message.set_token('NAME', p_sob_name);

Line 313: if(dbms_sql.fetch_rows(l_cursor_id)=0) then

309: dbms_sql.define_column(l_cursor_id,1,l_sob_id);
310: dbms_sql.define_column(l_cursor_id,2,l_coa_id);
311: dbms_sql.define_column(l_cursor_id,3,l_description,240);
312: l_rows_selected:= dbms_sql.execute(l_cursor_id);
313: if(dbms_sql.fetch_rows(l_cursor_id)=0) then
314: p_status:=false;
315: fnd_message.set_name('BIS','EDW_NODATA_SET_OF_BOOKS');
316: fnd_message.set_token('NAME', p_sob_name);
317: p_errMsg:=fnd_message.get;

Line 320: dbms_sql.column_value(l_cursor_id,1,l_sob_id);

316: fnd_message.set_token('NAME', p_sob_name);
317: p_errMsg:=fnd_message.get;
318: --p_errMsg:='Cannot find '||p_sob_name||' in gl_sets_of_books';
319: else
320: dbms_sql.column_value(l_cursor_id,1,l_sob_id);
321: dbms_sql.column_value(l_cursor_id,2,l_coa_id);
322: dbms_sql.column_value(l_cursor_id,3,l_description);
323: p_sob_id:=l_sob_id;
324: p_coa_id:=l_coa_id;

Line 321: dbms_sql.column_value(l_cursor_id,2,l_coa_id);

317: p_errMsg:=fnd_message.get;
318: --p_errMsg:='Cannot find '||p_sob_name||' in gl_sets_of_books';
319: else
320: dbms_sql.column_value(l_cursor_id,1,l_sob_id);
321: dbms_sql.column_value(l_cursor_id,2,l_coa_id);
322: dbms_sql.column_value(l_cursor_id,3,l_description);
323: p_sob_id:=l_sob_id;
324: p_coa_id:=l_coa_id;
325: p_description:= l_description;

Line 322: dbms_sql.column_value(l_cursor_id,3,l_description);

318: --p_errMsg:='Cannot find '||p_sob_name||' in gl_sets_of_books';
319: else
320: dbms_sql.column_value(l_cursor_id,1,l_sob_id);
321: dbms_sql.column_value(l_cursor_id,2,l_coa_id);
322: dbms_sql.column_value(l_cursor_id,3,l_description);
323: p_sob_id:=l_sob_id;
324: p_coa_id:=l_coa_id;
325: p_description:= l_description;
326: p_status:=true;

Line 391: cid := DBMS_SQL.open_cursor;

387:
388: x_instance_code:='';
389: l_progress := '010';
390: -- Check to make sure that all the enabled OLTP sources are up and running
391: cid := DBMS_SQL.open_cursor;
392: OPEN instances;
393: LOOP
394: BEGIN
395: l_progress := '020';

Line 402: DBMS_SQL.PARSE(cid, 'SELECT 1 FROM sys.dual@'||l_db_link, dbms_sql.native);

398: EXIT WHEN instances%NOTFOUND;
399:
400: -- Store the instance name in the out parameter to return
401:
402: DBMS_SQL.PARSE(cid, 'SELECT 1 FROM sys.dual@'||l_db_link, dbms_sql.native);
403: l_dummy_int := DBMS_SQL.EXECUTE(cid);
404: l_progress := '030';
405: EXCEPTION
406: when others then

Line 403: l_dummy_int := DBMS_SQL.EXECUTE(cid);

399:
400: -- Store the instance name in the out parameter to return
401:
402: DBMS_SQL.PARSE(cid, 'SELECT 1 FROM sys.dual@'||l_db_link, dbms_sql.native);
403: l_dummy_int := DBMS_SQL.EXECUTE(cid);
404: l_progress := '030';
405: EXCEPTION
406: when others then
407: l_status := FALSE;

Line 413: DBMS_SQL.close_cursor(cid);

409: edw_message_s.sql_error('check_db_status',l_progress,sqlcode);
410: END;
411: END LOOP;
412: CLOSE instances;
413: DBMS_SQL.close_cursor(cid);
414: return l_status;
415:
416: exception
417: when others then

Line 418: DBMS_SQL.close_cursor(cid);

414: return l_status;
415:
416: exception
417: when others then
418: DBMS_SQL.close_cursor(cid);
419: x_instance_code := null;
420: return false;
421: END check_db_status_all;
422:

Line 446: l_cursor_id:=DBMS_SQL.OPEN_CURSOR;

442:
443:
444: lookup_db_link(p_instance,l_status,l_errMsg,l_instance_link);
445: if(l_status) then
446: l_cursor_id:=DBMS_SQL.OPEN_CURSOR;
447: l_select_stmt:=
448: 'select count(*)
449: into :b_count
450: from gl_consolidation@'||l_instance_link||'

Line 453: DBMS_SQL.parse(l_cursor_id,l_select_stmt,DBMS_SQL.V7);

449: into :b_count
450: from gl_consolidation@'||l_instance_link||'
451: where from_ledger_id =:b_from_ledger_id
452: and to_ledger_id=:b_to_ledger_id';
453: DBMS_SQL.parse(l_cursor_id,l_select_stmt,DBMS_SQL.V7);
454: DBMS_SQL.BIND_VARIABLE(l_cursor_id,':b_from_ledger_id',
455: p_from_ledger_id);
456: DBMS_SQL.BIND_VARIABLE(l_cursor_id,':b_to_ledger_id',
457: p_to_ledger_id);

Line 454: DBMS_SQL.BIND_VARIABLE(l_cursor_id,':b_from_ledger_id',

450: from gl_consolidation@'||l_instance_link||'
451: where from_ledger_id =:b_from_ledger_id
452: and to_ledger_id=:b_to_ledger_id';
453: DBMS_SQL.parse(l_cursor_id,l_select_stmt,DBMS_SQL.V7);
454: DBMS_SQL.BIND_VARIABLE(l_cursor_id,':b_from_ledger_id',
455: p_from_ledger_id);
456: DBMS_SQL.BIND_VARIABLE(l_cursor_id,':b_to_ledger_id',
457: p_to_ledger_id);
458: DBMS_SQL.define_column(l_cursor_id,1,l_count);

Line 456: DBMS_SQL.BIND_VARIABLE(l_cursor_id,':b_to_ledger_id',

452: and to_ledger_id=:b_to_ledger_id';
453: DBMS_SQL.parse(l_cursor_id,l_select_stmt,DBMS_SQL.V7);
454: DBMS_SQL.BIND_VARIABLE(l_cursor_id,':b_from_ledger_id',
455: p_from_ledger_id);
456: DBMS_SQL.BIND_VARIABLE(l_cursor_id,':b_to_ledger_id',
457: p_to_ledger_id);
458: DBMS_SQL.define_column(l_cursor_id,1,l_count);
459: l_rows_selected:=DBMS_SQL.execute(l_cursor_id);
460:

Line 458: DBMS_SQL.define_column(l_cursor_id,1,l_count);

454: DBMS_SQL.BIND_VARIABLE(l_cursor_id,':b_from_ledger_id',
455: p_from_ledger_id);
456: DBMS_SQL.BIND_VARIABLE(l_cursor_id,':b_to_ledger_id',
457: p_to_ledger_id);
458: DBMS_SQL.define_column(l_cursor_id,1,l_count);
459: l_rows_selected:=DBMS_SQL.execute(l_cursor_id);
460:
461: if(DBMS_SQL.fetch_rows(l_cursor_id)=0) then
462: p_status:=false;

Line 459: l_rows_selected:=DBMS_SQL.execute(l_cursor_id);

455: p_from_ledger_id);
456: DBMS_SQL.BIND_VARIABLE(l_cursor_id,':b_to_ledger_id',
457: p_to_ledger_id);
458: DBMS_SQL.define_column(l_cursor_id,1,l_count);
459: l_rows_selected:=DBMS_SQL.execute(l_cursor_id);
460:
461: if(DBMS_SQL.fetch_rows(l_cursor_id)=0) then
462: p_status:=false;
463: p_result:=false;

Line 461: if(DBMS_SQL.fetch_rows(l_cursor_id)=0) then

457: p_to_ledger_id);
458: DBMS_SQL.define_column(l_cursor_id,1,l_count);
459: l_rows_selected:=DBMS_SQL.execute(l_cursor_id);
460:
461: if(DBMS_SQL.fetch_rows(l_cursor_id)=0) then
462: p_status:=false;
463: p_result:=false;
464: return;
465: end if;

Line 467: DBMS_SQL.column_value(l_cursor_id,1,l_count);

463: p_result:=false;
464: return;
465: end if;
466:
467: DBMS_SQL.column_value(l_cursor_id,1,l_count);
468: DBMS_SQL.close_cursor(l_cursor_id);
469: if l_count=0 then
470: p_status:=true;
471: p_result:=false;

Line 468: DBMS_SQL.close_cursor(l_cursor_id);

464: return;
465: end if;
466:
467: DBMS_SQL.column_value(l_cursor_id,1,l_count);
468: DBMS_SQL.close_cursor(l_cursor_id);
469: if l_count=0 then
470: p_status:=true;
471: p_result:=false;
472: elsif l_count>0 then

Line 512: l_cursor_id:=DBMS_SQL.OPEN_CURSOR;

508: execute immediate l_stmt;
509:
510: lookup_db_link(p_instance,p_status, p_error_mesg,l_instance_link );
511: if(p_status) then
512: l_cursor_id:=DBMS_SQL.OPEN_CURSOR;
513: l_select_stmt:=
514: 'select consolidation_id
515: into :b_consolidation_id
516: from gl_consolidation@'||l_instance_link||'

Line 520: DBMS_SQL.parse(l_cursor_id,l_select_stmt,DBMS_SQL.V7);

516: from gl_consolidation@'||l_instance_link||'
517: where from_ledger_id =:b_from_ledger_id
518: and to_ledger_id=:b_to_ledger_id
519: and name =:b_consolidation_name';
520: DBMS_SQL.parse(l_cursor_id,l_select_stmt,DBMS_SQL.V7);
521: DBMS_SQL.BIND_VARIABLE(l_cursor_id,':b_from_ledger_id',
522: p_from_ledger_id);
523: DBMS_SQL.BIND_VARIABLE(l_cursor_id,':b_to_ledger_id',
524: p_to_ledger_id);

Line 521: DBMS_SQL.BIND_VARIABLE(l_cursor_id,':b_from_ledger_id',

517: where from_ledger_id =:b_from_ledger_id
518: and to_ledger_id=:b_to_ledger_id
519: and name =:b_consolidation_name';
520: DBMS_SQL.parse(l_cursor_id,l_select_stmt,DBMS_SQL.V7);
521: DBMS_SQL.BIND_VARIABLE(l_cursor_id,':b_from_ledger_id',
522: p_from_ledger_id);
523: DBMS_SQL.BIND_VARIABLE(l_cursor_id,':b_to_ledger_id',
524: p_to_ledger_id);
525: DBMS_SQL.BIND_VARIABLE(l_cursor_id,':b_consolidation_name',

Line 523: DBMS_SQL.BIND_VARIABLE(l_cursor_id,':b_to_ledger_id',

519: and name =:b_consolidation_name';
520: DBMS_SQL.parse(l_cursor_id,l_select_stmt,DBMS_SQL.V7);
521: DBMS_SQL.BIND_VARIABLE(l_cursor_id,':b_from_ledger_id',
522: p_from_ledger_id);
523: DBMS_SQL.BIND_VARIABLE(l_cursor_id,':b_to_ledger_id',
524: p_to_ledger_id);
525: DBMS_SQL.BIND_VARIABLE(l_cursor_id,':b_consolidation_name',
526: p_consolidation_name);
527: DBMS_SQL.define_column(l_cursor_id,1,p_consolidation_id);

Line 525: DBMS_SQL.BIND_VARIABLE(l_cursor_id,':b_consolidation_name',

521: DBMS_SQL.BIND_VARIABLE(l_cursor_id,':b_from_ledger_id',
522: p_from_ledger_id);
523: DBMS_SQL.BIND_VARIABLE(l_cursor_id,':b_to_ledger_id',
524: p_to_ledger_id);
525: DBMS_SQL.BIND_VARIABLE(l_cursor_id,':b_consolidation_name',
526: p_consolidation_name);
527: DBMS_SQL.define_column(l_cursor_id,1,p_consolidation_id);
528: l_rows_selected:=DBMS_SQL.execute(l_cursor_id);
529:

Line 527: DBMS_SQL.define_column(l_cursor_id,1,p_consolidation_id);

523: DBMS_SQL.BIND_VARIABLE(l_cursor_id,':b_to_ledger_id',
524: p_to_ledger_id);
525: DBMS_SQL.BIND_VARIABLE(l_cursor_id,':b_consolidation_name',
526: p_consolidation_name);
527: DBMS_SQL.define_column(l_cursor_id,1,p_consolidation_id);
528: l_rows_selected:=DBMS_SQL.execute(l_cursor_id);
529:
530: if(DBMS_SQL.fetch_rows(l_cursor_id)>0) then
531: p_status:=true;

Line 528: l_rows_selected:=DBMS_SQL.execute(l_cursor_id);

524: p_to_ledger_id);
525: DBMS_SQL.BIND_VARIABLE(l_cursor_id,':b_consolidation_name',
526: p_consolidation_name);
527: DBMS_SQL.define_column(l_cursor_id,1,p_consolidation_id);
528: l_rows_selected:=DBMS_SQL.execute(l_cursor_id);
529:
530: if(DBMS_SQL.fetch_rows(l_cursor_id)>0) then
531: p_status:=true;
532: DBMS_SQL.column_value(l_cursor_id,1,p_consolidation_id);

Line 530: if(DBMS_SQL.fetch_rows(l_cursor_id)>0) then

526: p_consolidation_name);
527: DBMS_SQL.define_column(l_cursor_id,1,p_consolidation_id);
528: l_rows_selected:=DBMS_SQL.execute(l_cursor_id);
529:
530: if(DBMS_SQL.fetch_rows(l_cursor_id)>0) then
531: p_status:=true;
532: DBMS_SQL.column_value(l_cursor_id,1,p_consolidation_id);
533: else
534: p_status:=false;

Line 532: DBMS_SQL.column_value(l_cursor_id,1,p_consolidation_id);

528: l_rows_selected:=DBMS_SQL.execute(l_cursor_id);
529:
530: if(DBMS_SQL.fetch_rows(l_cursor_id)>0) then
531: p_status:=true;
532: DBMS_SQL.column_value(l_cursor_id,1,p_consolidation_id);
533: else
534: p_status:=false;
535: end if;
536: DBMS_SQL.close_cursor(l_cursor_id);

Line 536: DBMS_SQL.close_cursor(l_cursor_id);

532: DBMS_SQL.column_value(l_cursor_id,1,p_consolidation_id);
533: else
534: p_status:=false;
535: end if;
536: DBMS_SQL.close_cursor(l_cursor_id);
537: end if;
538: exception
539: when others then
540: p_status:=false;

Line 675: l_cursor_id:=dbms_sql.open_cursor;

671: and to_b.chart_of_accounts_id=to_f.structure_num
672: and to_f.instance_code=p_instance
673: and to_f.segment_name=p_segment_name;
674:
675: l_cursor_id:=dbms_sql.open_cursor;
676: l_select_stmt:='select count(*) from edw_cons_mapping_v@'||l_db_link||
677: ' where FROM_VALUE_SET_ID=:b_from_value_set_id and to_value_set_id=:b_to_value_set_id and consolidation_id=:b_consolidation_id';
678: dbms_sql.parse(l_cursor_id, l_select_stmt,dbms_sql.v7);
679:

Line 678: dbms_sql.parse(l_cursor_id, l_select_stmt,dbms_sql.v7);

674:
675: l_cursor_id:=dbms_sql.open_cursor;
676: l_select_stmt:='select count(*) from edw_cons_mapping_v@'||l_db_link||
677: ' where FROM_VALUE_SET_ID=:b_from_value_set_id and to_value_set_id=:b_to_value_set_id and consolidation_id=:b_consolidation_id';
678: dbms_sql.parse(l_cursor_id, l_select_stmt,dbms_sql.v7);
679:
680: dbms_sql.bind_variable(l_cursor_id,':b_from_value_set_id',l_from_value_set_id);
681: dbms_sql.bind_variable(l_cursor_id,':b_to_value_set_id',l_to_value_set_id);
682: dbms_sql.bind_variable(l_cursor_id,':b_consolidation_id',l_consolidation_id);

Line 680: dbms_sql.bind_variable(l_cursor_id,':b_from_value_set_id',l_from_value_set_id);

676: l_select_stmt:='select count(*) from edw_cons_mapping_v@'||l_db_link||
677: ' where FROM_VALUE_SET_ID=:b_from_value_set_id and to_value_set_id=:b_to_value_set_id and consolidation_id=:b_consolidation_id';
678: dbms_sql.parse(l_cursor_id, l_select_stmt,dbms_sql.v7);
679:
680: dbms_sql.bind_variable(l_cursor_id,':b_from_value_set_id',l_from_value_set_id);
681: dbms_sql.bind_variable(l_cursor_id,':b_to_value_set_id',l_to_value_set_id);
682: dbms_sql.bind_variable(l_cursor_id,':b_consolidation_id',l_consolidation_id);
683: dbms_sql.define_column(l_cursor_id,1,l_result);
684: l_dummy:=dbms_sql.execute(l_cursor_id);

Line 681: dbms_sql.bind_variable(l_cursor_id,':b_to_value_set_id',l_to_value_set_id);

677: ' where FROM_VALUE_SET_ID=:b_from_value_set_id and to_value_set_id=:b_to_value_set_id and consolidation_id=:b_consolidation_id';
678: dbms_sql.parse(l_cursor_id, l_select_stmt,dbms_sql.v7);
679:
680: dbms_sql.bind_variable(l_cursor_id,':b_from_value_set_id',l_from_value_set_id);
681: dbms_sql.bind_variable(l_cursor_id,':b_to_value_set_id',l_to_value_set_id);
682: dbms_sql.bind_variable(l_cursor_id,':b_consolidation_id',l_consolidation_id);
683: dbms_sql.define_column(l_cursor_id,1,l_result);
684: l_dummy:=dbms_sql.execute(l_cursor_id);
685: if dbms_sql.fetch_rows(l_cursor_id)=0 then exit;

Line 682: dbms_sql.bind_variable(l_cursor_id,':b_consolidation_id',l_consolidation_id);

678: dbms_sql.parse(l_cursor_id, l_select_stmt,dbms_sql.v7);
679:
680: dbms_sql.bind_variable(l_cursor_id,':b_from_value_set_id',l_from_value_set_id);
681: dbms_sql.bind_variable(l_cursor_id,':b_to_value_set_id',l_to_value_set_id);
682: dbms_sql.bind_variable(l_cursor_id,':b_consolidation_id',l_consolidation_id);
683: dbms_sql.define_column(l_cursor_id,1,l_result);
684: l_dummy:=dbms_sql.execute(l_cursor_id);
685: if dbms_sql.fetch_rows(l_cursor_id)=0 then exit;
686: end if;

Line 683: dbms_sql.define_column(l_cursor_id,1,l_result);

679:
680: dbms_sql.bind_variable(l_cursor_id,':b_from_value_set_id',l_from_value_set_id);
681: dbms_sql.bind_variable(l_cursor_id,':b_to_value_set_id',l_to_value_set_id);
682: dbms_sql.bind_variable(l_cursor_id,':b_consolidation_id',l_consolidation_id);
683: dbms_sql.define_column(l_cursor_id,1,l_result);
684: l_dummy:=dbms_sql.execute(l_cursor_id);
685: if dbms_sql.fetch_rows(l_cursor_id)=0 then exit;
686: end if;
687: dbms_sql.column_value(l_cursor_id,1,l_result);

Line 684: l_dummy:=dbms_sql.execute(l_cursor_id);

680: dbms_sql.bind_variable(l_cursor_id,':b_from_value_set_id',l_from_value_set_id);
681: dbms_sql.bind_variable(l_cursor_id,':b_to_value_set_id',l_to_value_set_id);
682: dbms_sql.bind_variable(l_cursor_id,':b_consolidation_id',l_consolidation_id);
683: dbms_sql.define_column(l_cursor_id,1,l_result);
684: l_dummy:=dbms_sql.execute(l_cursor_id);
685: if dbms_sql.fetch_rows(l_cursor_id)=0 then exit;
686: end if;
687: dbms_sql.column_value(l_cursor_id,1,l_result);
688: dbms_sql.close_cursor(l_cursor_id);

Line 685: if dbms_sql.fetch_rows(l_cursor_id)=0 then exit;

681: dbms_sql.bind_variable(l_cursor_id,':b_to_value_set_id',l_to_value_set_id);
682: dbms_sql.bind_variable(l_cursor_id,':b_consolidation_id',l_consolidation_id);
683: dbms_sql.define_column(l_cursor_id,1,l_result);
684: l_dummy:=dbms_sql.execute(l_cursor_id);
685: if dbms_sql.fetch_rows(l_cursor_id)=0 then exit;
686: end if;
687: dbms_sql.column_value(l_cursor_id,1,l_result);
688: dbms_sql.close_cursor(l_cursor_id);
689:

Line 687: dbms_sql.column_value(l_cursor_id,1,l_result);

683: dbms_sql.define_column(l_cursor_id,1,l_result);
684: l_dummy:=dbms_sql.execute(l_cursor_id);
685: if dbms_sql.fetch_rows(l_cursor_id)=0 then exit;
686: end if;
687: dbms_sql.column_value(l_cursor_id,1,l_result);
688: dbms_sql.close_cursor(l_cursor_id);
689:
690: if l_result<>0 then
691: l_cursor_id:=dbms_sql.open_cursor;

Line 688: dbms_sql.close_cursor(l_cursor_id);

684: l_dummy:=dbms_sql.execute(l_cursor_id);
685: if dbms_sql.fetch_rows(l_cursor_id)=0 then exit;
686: end if;
687: dbms_sql.column_value(l_cursor_id,1,l_result);
688: dbms_sql.close_cursor(l_cursor_id);
689:
690: if l_result<>0 then
691: l_cursor_id:=dbms_sql.open_cursor;
692: l_select_stmt:='select count(*) from edw_vbh_roots where edw_set_of_books_id=:b_problem_sob_id and root_value'||p_hierarchy_no||' is not null and segment_name =:b_segment_name';

Line 691: l_cursor_id:=dbms_sql.open_cursor;

687: dbms_sql.column_value(l_cursor_id,1,l_result);
688: dbms_sql.close_cursor(l_cursor_id);
689:
690: if l_result<>0 then
691: l_cursor_id:=dbms_sql.open_cursor;
692: l_select_stmt:='select count(*) from edw_vbh_roots where edw_set_of_books_id=:b_problem_sob_id and root_value'||p_hierarchy_no||' is not null and segment_name =:b_segment_name';
693:
694: dbms_sql.parse(l_cursor_id, l_select_stmt,dbms_sql.v7);
695: dbms_sql.bind_variable(l_cursor_id,':b_problem_sob_id',l_child_edw_sob_id);

Line 694: dbms_sql.parse(l_cursor_id, l_select_stmt,dbms_sql.v7);

690: if l_result<>0 then
691: l_cursor_id:=dbms_sql.open_cursor;
692: l_select_stmt:='select count(*) from edw_vbh_roots where edw_set_of_books_id=:b_problem_sob_id and root_value'||p_hierarchy_no||' is not null and segment_name =:b_segment_name';
693:
694: dbms_sql.parse(l_cursor_id, l_select_stmt,dbms_sql.v7);
695: dbms_sql.bind_variable(l_cursor_id,':b_problem_sob_id',l_child_edw_sob_id);
696: dbms_sql.bind_variable(l_cursor_id,':b_segment_name',p_segment_name);
697:
698: dbms_sql.define_column(l_cursor_id,1,l_result);

Line 695: dbms_sql.bind_variable(l_cursor_id,':b_problem_sob_id',l_child_edw_sob_id);

691: l_cursor_id:=dbms_sql.open_cursor;
692: l_select_stmt:='select count(*) from edw_vbh_roots where edw_set_of_books_id=:b_problem_sob_id and root_value'||p_hierarchy_no||' is not null and segment_name =:b_segment_name';
693:
694: dbms_sql.parse(l_cursor_id, l_select_stmt,dbms_sql.v7);
695: dbms_sql.bind_variable(l_cursor_id,':b_problem_sob_id',l_child_edw_sob_id);
696: dbms_sql.bind_variable(l_cursor_id,':b_segment_name',p_segment_name);
697:
698: dbms_sql.define_column(l_cursor_id,1,l_result);
699: l_dummy:=dbms_sql.execute(l_cursor_id);

Line 696: dbms_sql.bind_variable(l_cursor_id,':b_segment_name',p_segment_name);

692: l_select_stmt:='select count(*) from edw_vbh_roots where edw_set_of_books_id=:b_problem_sob_id and root_value'||p_hierarchy_no||' is not null and segment_name =:b_segment_name';
693:
694: dbms_sql.parse(l_cursor_id, l_select_stmt,dbms_sql.v7);
695: dbms_sql.bind_variable(l_cursor_id,':b_problem_sob_id',l_child_edw_sob_id);
696: dbms_sql.bind_variable(l_cursor_id,':b_segment_name',p_segment_name);
697:
698: dbms_sql.define_column(l_cursor_id,1,l_result);
699: l_dummy:=dbms_sql.execute(l_cursor_id);
700: if dbms_sql.fetch_rows(l_cursor_id)=0 then exit;

Line 698: dbms_sql.define_column(l_cursor_id,1,l_result);

694: dbms_sql.parse(l_cursor_id, l_select_stmt,dbms_sql.v7);
695: dbms_sql.bind_variable(l_cursor_id,':b_problem_sob_id',l_child_edw_sob_id);
696: dbms_sql.bind_variable(l_cursor_id,':b_segment_name',p_segment_name);
697:
698: dbms_sql.define_column(l_cursor_id,1,l_result);
699: l_dummy:=dbms_sql.execute(l_cursor_id);
700: if dbms_sql.fetch_rows(l_cursor_id)=0 then exit;
701: end if;
702: dbms_sql.column_value(l_cursor_id,1,l_result);

Line 699: l_dummy:=dbms_sql.execute(l_cursor_id);

695: dbms_sql.bind_variable(l_cursor_id,':b_problem_sob_id',l_child_edw_sob_id);
696: dbms_sql.bind_variable(l_cursor_id,':b_segment_name',p_segment_name);
697:
698: dbms_sql.define_column(l_cursor_id,1,l_result);
699: l_dummy:=dbms_sql.execute(l_cursor_id);
700: if dbms_sql.fetch_rows(l_cursor_id)=0 then exit;
701: end if;
702: dbms_sql.column_value(l_cursor_id,1,l_result);
703: dbms_sql.close_cursor(l_cursor_id);

Line 700: if dbms_sql.fetch_rows(l_cursor_id)=0 then exit;

696: dbms_sql.bind_variable(l_cursor_id,':b_segment_name',p_segment_name);
697:
698: dbms_sql.define_column(l_cursor_id,1,l_result);
699: l_dummy:=dbms_sql.execute(l_cursor_id);
700: if dbms_sql.fetch_rows(l_cursor_id)=0 then exit;
701: end if;
702: dbms_sql.column_value(l_cursor_id,1,l_result);
703: dbms_sql.close_cursor(l_cursor_id);
704: if l_result <> 0 then

Line 702: dbms_sql.column_value(l_cursor_id,1,l_result);

698: dbms_sql.define_column(l_cursor_id,1,l_result);
699: l_dummy:=dbms_sql.execute(l_cursor_id);
700: if dbms_sql.fetch_rows(l_cursor_id)=0 then exit;
701: end if;
702: dbms_sql.column_value(l_cursor_id,1,l_result);
703: dbms_sql.close_cursor(l_cursor_id);
704: if l_result <> 0 then
705: p_status:=false;
706: p_problem_sob_id:=l_child_edw_sob_id;

Line 703: dbms_sql.close_cursor(l_cursor_id);

699: l_dummy:=dbms_sql.execute(l_cursor_id);
700: if dbms_sql.fetch_rows(l_cursor_id)=0 then exit;
701: end if;
702: dbms_sql.column_value(l_cursor_id,1,l_result);
703: dbms_sql.close_cursor(l_cursor_id);
704: if l_result <> 0 then
705: p_status:=false;
706: p_problem_sob_id:=l_child_edw_sob_id;
707: return;

Line 737: l_cursor_id:=dbms_sql.open_cursor;

733: and to_b.chart_of_accounts_id=to_f.structure_num
734: and to_f.instance_code=p_instance
735: and to_f.segment_name=p_segment_name;
736:
737: l_cursor_id:=dbms_sql.open_cursor;
738: l_select_stmt:='select count(*) from edw_cons_mapping_v@'||l_db_link||
739: ' where FROM_VALUE_SET_ID=:b_from_value_set_id and to_value_set_id=:b_to_value_set_id and consolidation_id=:b_consolidation_id';
740:
741: dbms_sql.parse(l_cursor_id, l_select_stmt,dbms_sql.v7);

Line 741: dbms_sql.parse(l_cursor_id, l_select_stmt,dbms_sql.v7);

737: l_cursor_id:=dbms_sql.open_cursor;
738: l_select_stmt:='select count(*) from edw_cons_mapping_v@'||l_db_link||
739: ' where FROM_VALUE_SET_ID=:b_from_value_set_id and to_value_set_id=:b_to_value_set_id and consolidation_id=:b_consolidation_id';
740:
741: dbms_sql.parse(l_cursor_id, l_select_stmt,dbms_sql.v7);
742: dbms_sql.bind_variable(l_cursor_id,':b_from_value_set_id',l_from_value_set_id);
743: dbms_sql.bind_variable(l_cursor_id,':b_to_value_set_id',l_to_value_set_id);
744: dbms_sql.bind_variable(l_cursor_id,':b_consolidation_id',l_consolidation_id);
745: dbms_sql.define_column(l_cursor_id,1,l_result);

Line 742: dbms_sql.bind_variable(l_cursor_id,':b_from_value_set_id',l_from_value_set_id);

738: l_select_stmt:='select count(*) from edw_cons_mapping_v@'||l_db_link||
739: ' where FROM_VALUE_SET_ID=:b_from_value_set_id and to_value_set_id=:b_to_value_set_id and consolidation_id=:b_consolidation_id';
740:
741: dbms_sql.parse(l_cursor_id, l_select_stmt,dbms_sql.v7);
742: dbms_sql.bind_variable(l_cursor_id,':b_from_value_set_id',l_from_value_set_id);
743: dbms_sql.bind_variable(l_cursor_id,':b_to_value_set_id',l_to_value_set_id);
744: dbms_sql.bind_variable(l_cursor_id,':b_consolidation_id',l_consolidation_id);
745: dbms_sql.define_column(l_cursor_id,1,l_result);
746: l_dummy:=dbms_sql.execute(l_cursor_id);

Line 743: dbms_sql.bind_variable(l_cursor_id,':b_to_value_set_id',l_to_value_set_id);

739: ' where FROM_VALUE_SET_ID=:b_from_value_set_id and to_value_set_id=:b_to_value_set_id and consolidation_id=:b_consolidation_id';
740:
741: dbms_sql.parse(l_cursor_id, l_select_stmt,dbms_sql.v7);
742: dbms_sql.bind_variable(l_cursor_id,':b_from_value_set_id',l_from_value_set_id);
743: dbms_sql.bind_variable(l_cursor_id,':b_to_value_set_id',l_to_value_set_id);
744: dbms_sql.bind_variable(l_cursor_id,':b_consolidation_id',l_consolidation_id);
745: dbms_sql.define_column(l_cursor_id,1,l_result);
746: l_dummy:=dbms_sql.execute(l_cursor_id);
747: if dbms_sql.fetch_rows(l_cursor_id)=0 then exit;

Line 744: dbms_sql.bind_variable(l_cursor_id,':b_consolidation_id',l_consolidation_id);

740:
741: dbms_sql.parse(l_cursor_id, l_select_stmt,dbms_sql.v7);
742: dbms_sql.bind_variable(l_cursor_id,':b_from_value_set_id',l_from_value_set_id);
743: dbms_sql.bind_variable(l_cursor_id,':b_to_value_set_id',l_to_value_set_id);
744: dbms_sql.bind_variable(l_cursor_id,':b_consolidation_id',l_consolidation_id);
745: dbms_sql.define_column(l_cursor_id,1,l_result);
746: l_dummy:=dbms_sql.execute(l_cursor_id);
747: if dbms_sql.fetch_rows(l_cursor_id)=0 then exit;
748: end if;

Line 745: dbms_sql.define_column(l_cursor_id,1,l_result);

741: dbms_sql.parse(l_cursor_id, l_select_stmt,dbms_sql.v7);
742: dbms_sql.bind_variable(l_cursor_id,':b_from_value_set_id',l_from_value_set_id);
743: dbms_sql.bind_variable(l_cursor_id,':b_to_value_set_id',l_to_value_set_id);
744: dbms_sql.bind_variable(l_cursor_id,':b_consolidation_id',l_consolidation_id);
745: dbms_sql.define_column(l_cursor_id,1,l_result);
746: l_dummy:=dbms_sql.execute(l_cursor_id);
747: if dbms_sql.fetch_rows(l_cursor_id)=0 then exit;
748: end if;
749: dbms_sql.column_value(l_cursor_id,1,l_result);

Line 746: l_dummy:=dbms_sql.execute(l_cursor_id);

742: dbms_sql.bind_variable(l_cursor_id,':b_from_value_set_id',l_from_value_set_id);
743: dbms_sql.bind_variable(l_cursor_id,':b_to_value_set_id',l_to_value_set_id);
744: dbms_sql.bind_variable(l_cursor_id,':b_consolidation_id',l_consolidation_id);
745: dbms_sql.define_column(l_cursor_id,1,l_result);
746: l_dummy:=dbms_sql.execute(l_cursor_id);
747: if dbms_sql.fetch_rows(l_cursor_id)=0 then exit;
748: end if;
749: dbms_sql.column_value(l_cursor_id,1,l_result);
750: dbms_sql.close_cursor(l_cursor_id);

Line 747: if dbms_sql.fetch_rows(l_cursor_id)=0 then exit;

743: dbms_sql.bind_variable(l_cursor_id,':b_to_value_set_id',l_to_value_set_id);
744: dbms_sql.bind_variable(l_cursor_id,':b_consolidation_id',l_consolidation_id);
745: dbms_sql.define_column(l_cursor_id,1,l_result);
746: l_dummy:=dbms_sql.execute(l_cursor_id);
747: if dbms_sql.fetch_rows(l_cursor_id)=0 then exit;
748: end if;
749: dbms_sql.column_value(l_cursor_id,1,l_result);
750: dbms_sql.close_cursor(l_cursor_id);
751: if l_result<>0 then

Line 749: dbms_sql.column_value(l_cursor_id,1,l_result);

745: dbms_sql.define_column(l_cursor_id,1,l_result);
746: l_dummy:=dbms_sql.execute(l_cursor_id);
747: if dbms_sql.fetch_rows(l_cursor_id)=0 then exit;
748: end if;
749: dbms_sql.column_value(l_cursor_id,1,l_result);
750: dbms_sql.close_cursor(l_cursor_id);
751: if l_result<>0 then
752:
753: l_cursor_id:=dbms_sql.open_cursor;

Line 750: dbms_sql.close_cursor(l_cursor_id);

746: l_dummy:=dbms_sql.execute(l_cursor_id);
747: if dbms_sql.fetch_rows(l_cursor_id)=0 then exit;
748: end if;
749: dbms_sql.column_value(l_cursor_id,1,l_result);
750: dbms_sql.close_cursor(l_cursor_id);
751: if l_result<>0 then
752:
753: l_cursor_id:=dbms_sql.open_cursor;
754: l_select_stmt:='select count(*) from edw_vbh_roots where edw_set_of_books_id=:b_problem_sob_id and root_value'||p_hierarchy_no||' is not null and segment_name=:b_segment_name';

Line 753: l_cursor_id:=dbms_sql.open_cursor;

749: dbms_sql.column_value(l_cursor_id,1,l_result);
750: dbms_sql.close_cursor(l_cursor_id);
751: if l_result<>0 then
752:
753: l_cursor_id:=dbms_sql.open_cursor;
754: l_select_stmt:='select count(*) from edw_vbh_roots where edw_set_of_books_id=:b_problem_sob_id and root_value'||p_hierarchy_no||' is not null and segment_name=:b_segment_name';
755:
756: dbms_sql.parse(l_cursor_id, l_select_stmt,dbms_sql.v7);
757: dbms_sql.bind_variable(l_cursor_id,':b_problem_sob_id',l_parent_edw_sob_id);

Line 756: dbms_sql.parse(l_cursor_id, l_select_stmt,dbms_sql.v7);

752:
753: l_cursor_id:=dbms_sql.open_cursor;
754: l_select_stmt:='select count(*) from edw_vbh_roots where edw_set_of_books_id=:b_problem_sob_id and root_value'||p_hierarchy_no||' is not null and segment_name=:b_segment_name';
755:
756: dbms_sql.parse(l_cursor_id, l_select_stmt,dbms_sql.v7);
757: dbms_sql.bind_variable(l_cursor_id,':b_problem_sob_id',l_parent_edw_sob_id);
758: dbms_sql.bind_variable(l_cursor_id,':b_segment_name',p_segment_name);
759:
760: dbms_sql.define_column(l_cursor_id,1,l_result);

Line 757: dbms_sql.bind_variable(l_cursor_id,':b_problem_sob_id',l_parent_edw_sob_id);

753: l_cursor_id:=dbms_sql.open_cursor;
754: l_select_stmt:='select count(*) from edw_vbh_roots where edw_set_of_books_id=:b_problem_sob_id and root_value'||p_hierarchy_no||' is not null and segment_name=:b_segment_name';
755:
756: dbms_sql.parse(l_cursor_id, l_select_stmt,dbms_sql.v7);
757: dbms_sql.bind_variable(l_cursor_id,':b_problem_sob_id',l_parent_edw_sob_id);
758: dbms_sql.bind_variable(l_cursor_id,':b_segment_name',p_segment_name);
759:
760: dbms_sql.define_column(l_cursor_id,1,l_result);
761: l_dummy:=dbms_sql.execute(l_cursor_id);

Line 758: dbms_sql.bind_variable(l_cursor_id,':b_segment_name',p_segment_name);

754: l_select_stmt:='select count(*) from edw_vbh_roots where edw_set_of_books_id=:b_problem_sob_id and root_value'||p_hierarchy_no||' is not null and segment_name=:b_segment_name';
755:
756: dbms_sql.parse(l_cursor_id, l_select_stmt,dbms_sql.v7);
757: dbms_sql.bind_variable(l_cursor_id,':b_problem_sob_id',l_parent_edw_sob_id);
758: dbms_sql.bind_variable(l_cursor_id,':b_segment_name',p_segment_name);
759:
760: dbms_sql.define_column(l_cursor_id,1,l_result);
761: l_dummy:=dbms_sql.execute(l_cursor_id);
762: if dbms_sql.fetch_rows(l_cursor_id)=0 then exit;

Line 760: dbms_sql.define_column(l_cursor_id,1,l_result);

756: dbms_sql.parse(l_cursor_id, l_select_stmt,dbms_sql.v7);
757: dbms_sql.bind_variable(l_cursor_id,':b_problem_sob_id',l_parent_edw_sob_id);
758: dbms_sql.bind_variable(l_cursor_id,':b_segment_name',p_segment_name);
759:
760: dbms_sql.define_column(l_cursor_id,1,l_result);
761: l_dummy:=dbms_sql.execute(l_cursor_id);
762: if dbms_sql.fetch_rows(l_cursor_id)=0 then exit;
763: end if;
764: dbms_sql.column_value(l_cursor_id,1,l_result);

Line 761: l_dummy:=dbms_sql.execute(l_cursor_id);

757: dbms_sql.bind_variable(l_cursor_id,':b_problem_sob_id',l_parent_edw_sob_id);
758: dbms_sql.bind_variable(l_cursor_id,':b_segment_name',p_segment_name);
759:
760: dbms_sql.define_column(l_cursor_id,1,l_result);
761: l_dummy:=dbms_sql.execute(l_cursor_id);
762: if dbms_sql.fetch_rows(l_cursor_id)=0 then exit;
763: end if;
764: dbms_sql.column_value(l_cursor_id,1,l_result);
765: dbms_sql.close_cursor(l_cursor_id);

Line 762: if dbms_sql.fetch_rows(l_cursor_id)=0 then exit;

758: dbms_sql.bind_variable(l_cursor_id,':b_segment_name',p_segment_name);
759:
760: dbms_sql.define_column(l_cursor_id,1,l_result);
761: l_dummy:=dbms_sql.execute(l_cursor_id);
762: if dbms_sql.fetch_rows(l_cursor_id)=0 then exit;
763: end if;
764: dbms_sql.column_value(l_cursor_id,1,l_result);
765: dbms_sql.close_cursor(l_cursor_id);
766: if l_result <> 0 then

Line 764: dbms_sql.column_value(l_cursor_id,1,l_result);

760: dbms_sql.define_column(l_cursor_id,1,l_result);
761: l_dummy:=dbms_sql.execute(l_cursor_id);
762: if dbms_sql.fetch_rows(l_cursor_id)=0 then exit;
763: end if;
764: dbms_sql.column_value(l_cursor_id,1,l_result);
765: dbms_sql.close_cursor(l_cursor_id);
766: if l_result <> 0 then
767: p_status:=false;
768: p_problem_sob_id:=l_parent_edw_sob_id;

Line 765: dbms_sql.close_cursor(l_cursor_id);

761: l_dummy:=dbms_sql.execute(l_cursor_id);
762: if dbms_sql.fetch_rows(l_cursor_id)=0 then exit;
763: end if;
764: dbms_sql.column_value(l_cursor_id,1,l_result);
765: dbms_sql.close_cursor(l_cursor_id);
766: if l_result <> 0 then
767: p_status:=false;
768: p_problem_sob_id:=l_parent_edw_sob_id;
769: return;