eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.AMS_CONTCAMPAIGN_PVT dependencies on DBMS_SQL

Line 234: -- to DBMS_SQL package

230: l_row_processed NUMBER := 0 ;
231: -- Size constraint to Use Native dynamic sql
232: l_dbms_size NUMBER := 32767 ;
233: -- The PL/SQL table which stores 255 character length strings to be passed
234: -- to DBMS_SQL package
235: l_sql_str DBMS_SQL.varchar2s ;
236: -- Store the copy of currnt SQL string
237: l_str_copy VARCHAR2(2000);
238:

Line 235: l_sql_str DBMS_SQL.varchar2s ;

231: -- Size constraint to Use Native dynamic sql
232: l_dbms_size NUMBER := 32767 ;
233: -- The PL/SQL table which stores 255 character length strings to be passed
234: -- to DBMS_SQL package
235: l_sql_str DBMS_SQL.varchar2s ;
236: -- Store the copy of currnt SQL string
237: l_str_copy VARCHAR2(2000);
238:
239: l_length NUMBER := 0 ;

Line 247: -- Store Column count,col type for dbms sql

243: l_rows NUMBER := 0 ;
244: -- Store whole query if it is less than 32k
245: l_query VARCHAR2(32767) ;
246:
247: -- Store Column count,col type for dbms sql
248: l_col_cnt NUMBER ;
249: l_rec_tab DBMS_SQL.DESC_TAB ;
250: l_rec DBMS_SQL.DESC_REC ;
251:

Line 249: l_rec_tab DBMS_SQL.DESC_TAB ;

245: l_query VARCHAR2(32767) ;
246:
247: -- Store Column count,col type for dbms sql
248: l_col_cnt NUMBER ;
249: l_rec_tab DBMS_SQL.DESC_TAB ;
250: l_rec DBMS_SQL.DESC_REC ;
251:
252: -- Declare dummy variable to check no of columns in native sql
253: l_dummy VARCHAR2(2000);

Line 250: l_rec DBMS_SQL.DESC_REC ;

246:
247: -- Store Column count,col type for dbms sql
248: l_col_cnt NUMBER ;
249: l_rec_tab DBMS_SQL.DESC_TAB ;
250: l_rec DBMS_SQL.DESC_REC ;
251:
252: -- Declare dummy variable to check no of columns in native sql
253: l_dummy VARCHAR2(2000);
254:

Line 307: -- the query as Native SQL or DBMS_SQL

303: --
304: -- API body
305: -- Take the sql query into PLSQL table
306: -- Check the Size of the query depending on the Size Execute
307: -- the query as Native SQL or DBMS_SQL
308: l_count := 0 ;
309: -- dbms_output.put_line('l_count'||l_count) ;
310:
311: OPEN C_sql_string ;

Line 325: -- dbms_output.put_line('DBMS_SQL');

321: -- dbms_output.put_line('Size Of the SQL is '||l_size);
322:
323:
324: IF l_size > l_dbms_size THEN
325: -- dbms_output.put_line('DBMS_SQL');
326: -- Use DBMS_SQL. ----
327: -- The sql statement has to be taken into PLSQL table to parse
328: -- string larger than 32kb.
329: l_count := 0 ;

Line 326: -- Use DBMS_SQL. ----

322:
323:
324: IF l_size > l_dbms_size THEN
325: -- dbms_output.put_line('DBMS_SQL');
326: -- Use DBMS_SQL. ----
327: -- The sql statement has to be taken into PLSQL table to parse
328: -- string larger than 32kb.
329: l_count := 0 ;
330: LOOP

Line 339: -- current contents into DBMS_SQL PL/SQL table

335: l_length := length(l_str_copy) ;
336: l_sql_count := l_sql_count + 1 ;
337: IF l_length < 255 THEN
338: -- If length is < 255 char we can exit loop after copying
339: -- current contents into DBMS_SQL PL/SQL table
340: l_sql_str(l_sql_count):= l_str_copy ;
341: EXIT;
342: ELSE
343: -- Copy 255 Characters and copy next 255 to the next row

Line 356: IF (DBMS_SQL.Is_Open(l_cur_hdl) = FALSE) THEN

352:
353: -- Now the query is in plsql table. Parse it and execute.
354: BEGIN
355:
356: IF (DBMS_SQL.Is_Open(l_cur_hdl) = FALSE) THEN
357: l_cur_hdl := DBMS_SQL.Open_Cursor ;
358: END IF;
359:
360: IF (AMS_DEBUG_HIGH_ON) THEN

Line 357: l_cur_hdl := DBMS_SQL.Open_Cursor ;

353: -- Now the query is in plsql table. Parse it and execute.
354: BEGIN
355:
356: IF (DBMS_SQL.Is_Open(l_cur_hdl) = FALSE) THEN
357: l_cur_hdl := DBMS_SQL.Open_Cursor ;
358: END IF;
359:
360: IF (AMS_DEBUG_HIGH_ON) THEN
361:

Line 368: DBMS_SQL.Parse(l_cur_hdl ,

364: AMS_Utility_PVT.debug_message(l_full_name||': PARSE SQL start');
365:
366: END IF;
367:
368: DBMS_SQL.Parse(l_cur_hdl ,
369: l_sql_str,
370: l_sql_str.first,
371: l_sql_str.last,
372: FALSE,

Line 373: DBMS_SQL.Native) ;

369: l_sql_str,
370: l_sql_str.first,
371: l_sql_str.last,
372: FALSE,
373: DBMS_SQL.Native) ;
374:
375: DBMS_SQL.Define_Column(l_cur_hdl,1,l_result) ;
376:
377: l_row_processed := DBMS_SQL.Execute(l_cur_hdl);

Line 375: DBMS_SQL.Define_Column(l_cur_hdl,1,l_result) ;

371: l_sql_str.last,
372: FALSE,
373: DBMS_SQL.Native) ;
374:
375: DBMS_SQL.Define_Column(l_cur_hdl,1,l_result) ;
376:
377: l_row_processed := DBMS_SQL.Execute(l_cur_hdl);
378:
379: --

Line 377: l_row_processed := DBMS_SQL.Execute(l_cur_hdl);

373: DBMS_SQL.Native) ;
374:
375: DBMS_SQL.Define_Column(l_cur_hdl,1,l_result) ;
376:
377: l_row_processed := DBMS_SQL.Execute(l_cur_hdl);
378:
379: --
380: -- Check the number of rows returned
381: --

Line 383: IF dbms_sql.fetch_rows(l_cur_hdl) > 0 THEN

379: --
380: -- Check the number of rows returned
381: --
382: LOOP
383: IF dbms_sql.fetch_rows(l_cur_hdl) > 0 THEN
384: l_rows := l_rows + 1 ;
385: ELSE
386: EXIT;
387: END IF;

Line 392: -- l_row_processed := DBMS_SQL.Execute(l_cur_hdl);

388: END LOOP ;
389:
390:
391: -- dbms_output.put_line('No of Rows Fetched '||l_rows);
392: -- l_row_processed := DBMS_SQL.Execute(l_cur_hdl);
393: -- dbms_output.put_line('No of Rows Fetched '||dbms_sql.fetch_rows(l_cur_hdl));
394: IF l_rows > 1 THEN
395: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
396: THEN -- MMSG

Line 393: -- dbms_output.put_line('No of Rows Fetched '||dbms_sql.fetch_rows(l_cur_hdl));

389:
390:
391: -- dbms_output.put_line('No of Rows Fetched '||l_rows);
392: -- l_row_processed := DBMS_SQL.Execute(l_cur_hdl);
393: -- dbms_output.put_line('No of Rows Fetched '||dbms_sql.fetch_rows(l_cur_hdl));
394: IF l_rows > 1 THEN
395: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
396: THEN -- MMSG
397: -- dbms_OUTPUT.Put_Line('The SQL statement in Discoverer is invalid: returns more than one row');

Line 401: DBMS_SQL.Close_Cursor(l_cur_hdl) ;

397: -- dbms_OUTPUT.Put_Line('The SQL statement in Discoverer is invalid: returns more than one row');
398: FND_MESSAGE.set_name('AMS', 'AMS_TRIG_INVALID_DISC_TOOROW');
399: FND_MSG_PUB.Add;
400: END IF;
401: DBMS_SQL.Close_Cursor(l_cur_hdl) ;
402: x_return_status := FND_API.G_RET_STS_ERROR;
403: -- If any errors happen abort API/Procedure.
404: RAISE FND_API.G_EXC_ERROR;
405: ELSIF l_rows = 0 THEN

Line 412: DBMS_SQL.Close_Cursor(l_cur_hdl) ;

408: -- dbms_OUTPUT.Put_Line('The SQL statement in Discoverer is invalid: returns no rows');
409: FND_MESSAGE.set_name('AMS', 'AMS_TRIG_INVALID_DISC_NOROW');
410: FND_MSG_PUB.Add;
411: END IF;
412: DBMS_SQL.Close_Cursor(l_cur_hdl) ;
413: x_return_status := FND_API.G_RET_STS_ERROR;
414: -- If any errors happen abort API/Procedure.
415: RAISE FND_API.G_EXC_ERROR;
416: END IF;

Line 420: DBMS_SQL.Describe_Columns(l_cur_hdl,l_col_cnt,l_rec_tab);

416: END IF;
417: -- dbms_OUTPUT.Put_Line('returns one row');
418:
419: -- If query returns only one row check whether it returns only one column
420: DBMS_SQL.Describe_Columns(l_cur_hdl,l_col_cnt,l_rec_tab);
421: -- dbms_output.put_line('No of columns : '||l_col_cnt);
422: IF l_col_cnt > 1 THEN
423: IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
424: THEN -- MMSG

Line 429: DBMS_SQL.Close_Cursor(l_cur_hdl) ;

425: -- dbms_OUTPUT.Put_Line('The SQL statement in Discoverer is invalid: returns more than one column');
426: FND_MESSAGE.set_name('AMS', 'AMS_TRIG_INVALID_DISC_TOOCOL');
427: FND_MSG_PUB.Add;
428: END IF;
429: DBMS_SQL.Close_Cursor(l_cur_hdl) ;
430: x_return_status := FND_API.G_RET_STS_ERROR;
431: -- If any errors happen abort API/Procedure.
432: RAISE FND_API.G_EXC_ERROR;
433: END IF;

Line 446: DBMS_SQL.Close_Cursor(l_cur_hdl) ;

442: -- dbms_OUTPUT.Put_Line('The SQL statement in Discoverer is invalid: Datatype of the column is not Number');
443: FND_MESSAGE.set_name('AMS', 'AMS_TRIG_INVALID_DISC_NONUM');
444: FND_MSG_PUB.Add;
445: END IF;
446: DBMS_SQL.Close_Cursor(l_cur_hdl) ;
447: x_return_status := FND_API.G_RET_STS_ERROR;
448: -- If any errors happen abort API/Procedure.
449: RAISE FND_API.G_EXC_ERROR;
450: END IF;

Line 454: DBMS_SQL.Column_Value(l_cur_hdl,1,l_result) ;

450: END IF;
451:
452:
453: -- If column is number return the number
454: DBMS_SQL.Column_Value(l_cur_hdl,1,l_result) ;
455: DBMS_SQL.Close_Cursor(l_cur_hdl) ;
456: -- Success Message
457: -- MMSG
458: -- dbms_OUTPUT.Put_Line('AMS_ContCampaign_PVT.Check_sql_row: The result is: '||to_char(l_result));

Line 455: DBMS_SQL.Close_Cursor(l_cur_hdl) ;

451:
452:
453: -- If column is number return the number
454: DBMS_SQL.Column_Value(l_cur_hdl,1,l_result) ;
455: DBMS_SQL.Close_Cursor(l_cur_hdl) ;
456: -- Success Message
457: -- MMSG
458: -- dbms_OUTPUT.Put_Line('AMS_ContCampaign_PVT.Check_sql_row: The result is: '||to_char(l_result));
459:

Line 477: DBMS_SQL.Close_Cursor(l_cur_hdl);

473: FND_MESSAGE.set_name('AMS', 'AMS_TRIG_INVALID_DISC_NONUM');
474: FND_MSG_PUB.Add;
475: END IF;
476: x_return_status := FND_API.G_Ret_Sts_Error ;
477: DBMS_SQL.Close_Cursor(l_cur_hdl);
478: -- If any errors happen abort API/Procedure.
479: RAISE FND_API.G_EXC_ERROR;
480: END;
481: