APPS.BIS_PMV_ACTUAL_PVT dependencies on DBMS_SQL

Line 147: l_desc_tab dbms_sql.desc_tab;

143: l_viewby_table VARCHAR2(32000);
144: l_target_alias VARCHAR2(50);
145: l_has_target VARCHAR2(1);
146: l_cursor integer;
147: l_desc_tab dbms_sql.desc_tab;
148: l_attr_codes BISVIEWER.t_char;
149: l_attr_values BISVIEWER.t_char;
150: l_actual_value varchar2(2000);
151: l_compareto_value varchar2(2000);

Line 358: l_cursor := dbms_sql.open_cursor;

354: x_bind_var_tbl => l_bind_index_tbl);
355: end if;
356: */
357:
358: l_cursor := dbms_sql.open_cursor;
359: dbms_sql.parse(l_cursor, l_query_string, DBMS_SQL.native);
360:
361: -- set up bind variables for cursor
362: if (l_bind_var_tbl.COUNT > 0) then --Added the if for Bug Fix 2394713

Line 359: dbms_sql.parse(l_cursor, l_query_string, DBMS_SQL.native);

355: end if;
356: */
357:
358: l_cursor := dbms_sql.open_cursor;
359: dbms_sql.parse(l_cursor, l_query_string, DBMS_SQL.native);
360:
361: -- set up bind variables for cursor
362: if (l_bind_var_tbl.COUNT > 0) then --Added the if for Bug Fix 2394713
363: for i in l_bind_var_tbl.FIRST..l_bind_var_tbl.LAST loop

Line 375: dbms_sql.bind_variable(l_cursor, l_bind_col, l_bind_var);

371: l_bind_Var := l_bind_Var_tbl(i);
372: end if;
373: end if;
374: commit;
375: dbms_sql.bind_variable(l_cursor, l_bind_col, l_bind_var);
376: end loop;
377: end if;
378:
379: -- find out column indexes of the main query for all the needed attribute codes

Line 380: dbms_sql.describe_columns(l_cursor, l_col_count, l_desc_tab);

376: end loop;
377: end if;
378:
379: -- find out column indexes of the main query for all the needed attribute codes
380: dbms_sql.describe_columns(l_cursor, l_col_count, l_desc_tab);
381: l_actual_count := 0;
382: l_compareto_count := 0;
383: for i in 1..l_col_count loop
384: --dbms_output.put_line('alias: '||l_desc_tab(i).col_name);

Line 429: dbms_sql.define_column(l_cursor, l_actual_col_index(i), l_actual_value, 200);

425: ,x_msg_data => x_msg_data
426: );
427:
428: for i in 1..l_actual_count loop
429: dbms_sql.define_column(l_cursor, l_actual_col_index(i), l_actual_value, 200);
430: end loop;
431: else
432: if (l_actual_val_cnt > 0) then
433: dbms_sql.define_column(l_cursor, l_actual_val_cnt, l_actual_value, 200);

Line 433: dbms_sql.define_column(l_cursor, l_actual_val_cnt, l_actual_value, 200);

429: dbms_sql.define_column(l_cursor, l_actual_col_index(i), l_actual_value, 200);
430: end loop;
431: else
432: if (l_actual_val_cnt > 0) then
433: dbms_sql.define_column(l_cursor, l_actual_val_cnt, l_actual_value, 200);
434: end if;
435: end if;
436:
437: if (l_calculate_compareto) then

Line 447: dbms_sql.define_column(l_cursor, l_compareto_col_index(i), l_compareto_value, 200);

443: ,x_msg_data => x_msg_data
444: );
445:
446: for i in 1..l_compareto_count loop
447: dbms_sql.define_column(l_cursor, l_compareto_col_index(i), l_compareto_value, 200);
448: end loop;
449: else
450: if (l_compareto_val_cnt > 0) then
451: dbms_sql.define_column(l_cursor, l_compareto_val_cnt, l_compareto_value, 200);

Line 451: dbms_sql.define_column(l_cursor, l_compareto_val_cnt, l_compareto_value, 200);

447: dbms_sql.define_column(l_cursor, l_compareto_col_index(i), l_compareto_value, 200);
448: end loop;
449: else
450: if (l_compareto_val_cnt > 0) then
451: dbms_sql.define_column(l_cursor, l_compareto_val_cnt, l_compareto_value, 200);
452: end if;
453: end if;
454:
455: --dbms_output.put_line('before executing the cursor');

Line 457: ignore := dbms_sql.execute(l_cursor);

453: end if;
454:
455: --dbms_output.put_line('before executing the cursor');
456: -- execute the cursor and get the values
457: ignore := dbms_sql.execute(l_cursor);
458: --dbms_output.put_line('after executing the cursor');
459: loop
460: if (dbms_sql.fetch_rows(l_cursor) = 0) then
461: exit;

Line 460: if (dbms_sql.fetch_rows(l_cursor) = 0) then

456: -- execute the cursor and get the values
457: ignore := dbms_sql.execute(l_cursor);
458: --dbms_output.put_line('after executing the cursor');
459: loop
460: if (dbms_sql.fetch_rows(l_cursor) = 0) then
461: exit;
462: end if;
463: l_row_found := l_row_found+1;
464:

Line 468: dbms_sql.column_value(l_cursor, l_actual_col_index(i), l_actual_values(i));

464:
465: if (l_calculate_actual) then
466: --l_temp_actual_formula := l_actual_formula;
467: for i in 1..l_actual_count loop
468: dbms_sql.column_value(l_cursor, l_actual_col_index(i), l_actual_values(i));
469: end loop;
470:
471: GET_CALCULATED_VALUE
472: (p_formula => l_Actual_formula

Line 492: dbms_sql.column_value(l_cursor, l_actual_val_cnt, l_actual_value);

488: end;
489: */
490: else
491: if (l_actual_val_cnt > 0) then
492: dbms_sql.column_value(l_cursor, l_actual_val_cnt, l_actual_value);
493: end if;
494: end if;
495:
496: if (l_calculate_compareto) then

Line 499: dbms_sql.column_value(l_cursor, l_compareto_col_index(i), l_compareto_values(i));

495:
496: if (l_calculate_compareto) then
497: --l_temp_compareto_formula := l_compareto_formula;
498: for i in 1..l_compareto_count loop
499: dbms_sql.column_value(l_cursor, l_compareto_col_index(i), l_compareto_values(i));
500: end loop;
501:
502: GET_CALCULATED_VALUE
503: (p_formula => l_compareto_formula

Line 523: dbms_sql.column_value(l_cursor, l_compareto_val_cnt, l_compareto_value);

519: end;
520: */
521: else
522: if (l_compareto_val_cnt > 0) then
523: dbms_sql.column_value(l_cursor, l_compareto_val_cnt, l_compareto_value);
524: end if;
525: end if;
526:
527: end loop;

Line 528: dbms_sql.close_cursor(l_cursor);

524: end if;
525: end if;
526:
527: end loop;
528: dbms_sql.close_cursor(l_cursor);
529:
530: if (l_row_found <= 0) then
531: x_return_status := FND_API.G_RET_STS_SUCCESS;
532: x_msg_data := 'NO DATA FOUND';

Line 546: if (dbms_sql.IS_OPEN(l_cursor)) then

542: x_actual_value := l_actual_value;
543: x_compareto_value := l_compareto_value;
544: exception
545: when others then
546: if (dbms_sql.IS_OPEN(l_cursor)) then
547: dbms_sql.close_cursor(l_cursor);
548: end if;
549: x_return_status := FND_API.G_RET_STS_ERROR;
550: END;

Line 547: dbms_sql.close_cursor(l_cursor);

543: x_compareto_value := l_compareto_value;
544: exception
545: when others then
546: if (dbms_sql.IS_OPEN(l_cursor)) then
547: dbms_sql.close_cursor(l_cursor);
548: end if;
549: x_return_status := FND_API.G_RET_STS_ERROR;
550: END;
551: -- Overloaded procedure to return a table of records for SONAR KPI portlet

Line 575: l_desc_tab dbms_sql.desc_tab;

571: l_viewby_table VARCHAR2(32000);
572: l_target_alias VARCHAR2(50);
573: l_has_target VARCHAR2(1);
574: l_cursor integer;
575: l_desc_tab dbms_sql.desc_tab;
576: l_attr_codes BISVIEWER.t_char;
577: l_attr_values BISVIEWER.t_char;
578: l_actual_value varchar2(2000);
579: l_compareto_value varchar2(2000);

Line 597: l_viewby_tbl dbms_sql.varchar2_table;

593: l_endIndex NUMBER;
594: l_bind_var VARCHAR2(32000);
595: l_tab_index NUMBER := 1;
596: l_bind_col VARCHAR2(2000);
597: l_viewby_tbl dbms_sql.varchar2_table;
598: l_actual_tbl dbms_sql.number_table;
599: l_compareto_tbl dbms_sql.number_table;
600: l_viewbyid_tbl dbms_sql.varchar2_table;
601: l_indx number := 1;

Line 598: l_actual_tbl dbms_sql.number_table;

594: l_bind_var VARCHAR2(32000);
595: l_tab_index NUMBER := 1;
596: l_bind_col VARCHAR2(2000);
597: l_viewby_tbl dbms_sql.varchar2_table;
598: l_actual_tbl dbms_sql.number_table;
599: l_compareto_tbl dbms_sql.number_table;
600: l_viewbyid_tbl dbms_sql.varchar2_table;
601: l_indx number := 1;
602: l_actual_rec BIS_PMV_ACTUAL_PVT.ACTUAL_VALUE_REC_TYPE;

Line 599: l_compareto_tbl dbms_sql.number_table;

595: l_tab_index NUMBER := 1;
596: l_bind_col VARCHAR2(2000);
597: l_viewby_tbl dbms_sql.varchar2_table;
598: l_actual_tbl dbms_sql.number_table;
599: l_compareto_tbl dbms_sql.number_table;
600: l_viewbyid_tbl dbms_sql.varchar2_table;
601: l_indx number := 1;
602: l_actual_rec BIS_PMV_ACTUAL_PVT.ACTUAL_VALUE_REC_TYPE;
603: l_function_name VARCHAr2(2000);

Line 600: l_viewbyid_tbl dbms_sql.varchar2_table;

596: l_bind_col VARCHAR2(2000);
597: l_viewby_tbl dbms_sql.varchar2_table;
598: l_actual_tbl dbms_sql.number_table;
599: l_compareto_tbl dbms_sql.number_table;
600: l_viewbyid_tbl dbms_sql.varchar2_table;
601: l_indx number := 1;
602: l_actual_rec BIS_PMV_ACTUAL_PVT.ACTUAL_VALUE_REC_TYPE;
603: l_function_name VARCHAr2(2000);
604: l_session_id VARCHAR2(2000);

Line 678: l_actual_gt_values dbms_sql.number_table;

674: l_viewby_value varchar2(2000);
675: l_viewbyid_value varchar2(2000);
676: l_temp_viewby_value varchar2(32000);
677: l_debug_msg varchar2(32000);
678: l_actual_gt_values dbms_sql.number_table;
679: l_compareto_gt_values dbms_sql.number_table;
680: l_actual_gt_value varchar2(2000);
681: l_compareto_gt_value varchar2(2000);
682: l_actual_gt_attrib varchar2(32000);

Line 679: l_compareto_gt_values dbms_sql.number_table;

675: l_viewbyid_value varchar2(2000);
676: l_temp_viewby_value varchar2(32000);
677: l_debug_msg varchar2(32000);
678: l_actual_gt_values dbms_sql.number_table;
679: l_compareto_gt_values dbms_sql.number_table;
680: l_actual_gt_value varchar2(2000);
681: l_compareto_gt_value varchar2(2000);
682: l_actual_gt_attrib varchar2(32000);
683: l_compareto_gt_attrib varchar2(32000);

Line 978: l_cursor := dbms_sql.open_cursor;

974: END IF;
975: CLOSE c_grandattrib;
976: --dbms_output.put_line ('The compareto gt attrib is '|| l_compareto_gt_attrib);
977: END IF;
978: l_cursor := dbms_sql.open_cursor;
979: dbms_sql.parse(l_cursor, l_query_string, DBMS_SQL.native);
980: if (l_bind_var_tbl.COUNT > 0) then
981: for i in l_bind_var_tbl.FIRST..l_bind_var_tbl.LAST loop
982: l_bind_col := ':x'|| i;

Line 979: dbms_sql.parse(l_cursor, l_query_string, DBMS_SQL.native);

975: CLOSE c_grandattrib;
976: --dbms_output.put_line ('The compareto gt attrib is '|| l_compareto_gt_attrib);
977: END IF;
978: l_cursor := dbms_sql.open_cursor;
979: dbms_sql.parse(l_cursor, l_query_string, DBMS_SQL.native);
980: if (l_bind_var_tbl.COUNT > 0) then
981: for i in l_bind_var_tbl.FIRST..l_bind_var_tbl.LAST loop
982: l_bind_col := ':x'|| i;
983:

Line 995: dbms_sql.bind_variable(l_cursor, l_bind_col, l_bind_var);

991: l_bind_Var := l_bind_Var_tbl(i);
992: end if;
993: end if;
994: --dbms_output.put_line ('bind var '||(i)||':'|| l_bind_var);
995: dbms_sql.bind_variable(l_cursor, l_bind_col, l_bind_var);
996: end loop;
997: end if;
998:
999: --dbms_output.put_line ('before describe columns');

Line 1004: dbms_sql.describe_columns(l_cursor, l_col_count, l_desc_tab);

1000: if g_debug_on then
1001: l_debug_msg := l_debug_msg || 'before describe columns';
1002: end if;
1003:
1004: dbms_sql.describe_columns(l_cursor, l_col_count, l_desc_tab);
1005: l_actual_count := 0;
1006: l_compareto_count := 0;
1007: for i in 1..l_col_count loop
1008: --dbms_output.put_line('alias: '||l_desc_tab(i).col_name);

Line 1106: dbms_sql.define_column(l_cursor, l_actual_col_index(i), l_actual_value, 200);

1102: ,x_msg_data => x_msg_data
1103: );
1104:
1105: for i in 1..l_actual_count loop
1106: dbms_sql.define_column(l_cursor, l_actual_col_index(i), l_actual_value, 200);
1107: end loop;
1108: else
1109: if (l_actual_val_cnt > 0) then
1110: dbms_sql.define_column(l_cursor, l_actual_val_cnt, l_actual_value, 200);

Line 1110: dbms_sql.define_column(l_cursor, l_actual_val_cnt, l_actual_value, 200);

1106: dbms_sql.define_column(l_cursor, l_actual_col_index(i), l_actual_value, 200);
1107: end loop;
1108: else
1109: if (l_actual_val_cnt > 0) then
1110: dbms_sql.define_column(l_cursor, l_actual_val_cnt, l_actual_value, 200);
1111: end if;
1112: end if;
1113:
1114: --dbms_output.put_line ('before defining compareto columns');

Line 1131: dbms_sql.define_column(l_cursor, l_compareto_col_index(i), l_compareto_value, 200);

1127:
1128: --dbms_output.put_line('compareto_count:'||l_compareto_count);
1129: for i in 1..l_compareto_count loop
1130: --dbms_output.put_line ('l_compareto_col_index'||i||':'||l_compareto_col_index(i));
1131: dbms_sql.define_column(l_cursor, l_compareto_col_index(i), l_compareto_value, 200);
1132: end loop;
1133: else
1134: if (l_compareto_val_cnt > 0) then
1135: dbms_sql.define_column(l_cursor, l_compareto_val_cnt, l_compareto_value, 200);

Line 1135: dbms_sql.define_column(l_cursor, l_compareto_val_cnt, l_compareto_value, 200);

1131: dbms_sql.define_column(l_cursor, l_compareto_col_index(i), l_compareto_value, 200);
1132: end loop;
1133: else
1134: if (l_compareto_val_cnt > 0) then
1135: dbms_sql.define_column(l_cursor, l_compareto_val_cnt, l_compareto_value, 200);
1136: end if;
1137: end if;
1138:
1139: --dbms_output.put_line ('before defining viewby columns');

Line 1145: dbms_sql.define_column(l_cursor, l_viewby_val_cnt, l_viewby_value, 200);

1141: l_debug_msg := l_debug_msg || 'before defining viewby columns';
1142: end if;
1143:
1144: if (l_viewby_val_cnt > 0) then
1145: dbms_sql.define_column(l_cursor, l_viewby_val_cnt, l_viewby_value, 200);
1146: end if;
1147:
1148: --dbms_output.put_line ('before defining viewbyid columns');
1149: if g_debug_on then

Line 1154: dbms_sql.define_column(l_cursor, l_viewby_id_cnt, l_viewbyid_value, 200);

1150: l_debug_msg := l_debug_msg || 'before defining viewbyid columns';
1151: end if;
1152:
1153: if (l_viewby_id_cnt > 0) then
1154: dbms_sql.define_column(l_cursor, l_viewby_id_cnt, l_viewbyid_value, 200);
1155: end if;
1156:
1157: if (l_Actual_gt_ct > 0) then
1158: dbms_sql.define_column(l_cursor,l_actual_gt_ct, l_actual_gt_value, 200);

Line 1158: dbms_sql.define_column(l_cursor,l_actual_gt_ct, l_actual_gt_value, 200);

1154: dbms_sql.define_column(l_cursor, l_viewby_id_cnt, l_viewbyid_value, 200);
1155: end if;
1156:
1157: if (l_Actual_gt_ct > 0) then
1158: dbms_sql.define_column(l_cursor,l_actual_gt_ct, l_actual_gt_value, 200);
1159: end if;
1160:
1161: if (l_compareto_gt_ct > 0) then
1162: dbms_sql.define_column(l_cursor,l_compareto_gt_ct, l_compareto_gt_value, 200);

Line 1162: dbms_sql.define_column(l_cursor,l_compareto_gt_ct, l_compareto_gt_value, 200);

1158: dbms_sql.define_column(l_cursor,l_actual_gt_ct, l_actual_gt_value, 200);
1159: end if;
1160:
1161: if (l_compareto_gt_ct > 0) then
1162: dbms_sql.define_column(l_cursor,l_compareto_gt_ct, l_compareto_gt_value, 200);
1163: end if;
1164:
1165: else
1166: if (l_Actual_val_cnt > 0) then

Line 1167: dbms_sql.define_array(l_cursor, l_Actual_val_cnt, l_actual_tbl, 10, 1);

1163: end if;
1164:
1165: else
1166: if (l_Actual_val_cnt > 0) then
1167: dbms_sql.define_array(l_cursor, l_Actual_val_cnt, l_actual_tbl, 10, 1);
1168: end if;
1169: if (l_compareto_val_cnt > 0) then
1170: dbms_sql.define_array(l_cursor, l_compareto_val_cnt, l_compareto_tbl, 10, 1);
1171: end if;

Line 1170: dbms_sql.define_array(l_cursor, l_compareto_val_cnt, l_compareto_tbl, 10, 1);

1166: if (l_Actual_val_cnt > 0) then
1167: dbms_sql.define_array(l_cursor, l_Actual_val_cnt, l_actual_tbl, 10, 1);
1168: end if;
1169: if (l_compareto_val_cnt > 0) then
1170: dbms_sql.define_array(l_cursor, l_compareto_val_cnt, l_compareto_tbl, 10, 1);
1171: end if;
1172: if (l_viewby_val_cnt > 0) then
1173: dbms_sql.define_array(l_cursor, l_viewby_val_cnt, l_viewby_tbl, 10, 1);
1174: end if;

Line 1173: dbms_sql.define_array(l_cursor, l_viewby_val_cnt, l_viewby_tbl, 10, 1);

1169: if (l_compareto_val_cnt > 0) then
1170: dbms_sql.define_array(l_cursor, l_compareto_val_cnt, l_compareto_tbl, 10, 1);
1171: end if;
1172: if (l_viewby_val_cnt > 0) then
1173: dbms_sql.define_array(l_cursor, l_viewby_val_cnt, l_viewby_tbl, 10, 1);
1174: end if;
1175: if (l_viewby_id_cnt > 0) then
1176: dbms_sql.define_array(l_cursor, l_viewby_id_cnt, l_viewbyid_tbl, 10, 1);
1177: end if;

Line 1176: dbms_sql.define_array(l_cursor, l_viewby_id_cnt, l_viewbyid_tbl, 10, 1);

1172: if (l_viewby_val_cnt > 0) then
1173: dbms_sql.define_array(l_cursor, l_viewby_val_cnt, l_viewby_tbl, 10, 1);
1174: end if;
1175: if (l_viewby_id_cnt > 0) then
1176: dbms_sql.define_array(l_cursor, l_viewby_id_cnt, l_viewbyid_tbl, 10, 1);
1177: end if;
1178: if (l_Actual_gt_ct > 0) then
1179: dbms_sql.define_array(l_cursor,l_actual_gt_ct, l_actual_gt_values,10,1);
1180: end if;

Line 1179: dbms_sql.define_array(l_cursor,l_actual_gt_ct, l_actual_gt_values,10,1);

1175: if (l_viewby_id_cnt > 0) then
1176: dbms_sql.define_array(l_cursor, l_viewby_id_cnt, l_viewbyid_tbl, 10, 1);
1177: end if;
1178: if (l_Actual_gt_ct > 0) then
1179: dbms_sql.define_array(l_cursor,l_actual_gt_ct, l_actual_gt_values,10,1);
1180: end if;
1181: if (l_compareto_gt_ct > 0) then
1182: dbms_sql.define_array(l_cursor,l_compareto_gt_ct, l_compareto_gt_values,10,1);
1183: end if;

Line 1182: dbms_sql.define_array(l_cursor,l_compareto_gt_ct, l_compareto_gt_values,10,1);

1178: if (l_Actual_gt_ct > 0) then
1179: dbms_sql.define_array(l_cursor,l_actual_gt_ct, l_actual_gt_values,10,1);
1180: end if;
1181: if (l_compareto_gt_ct > 0) then
1182: dbms_sql.define_array(l_cursor,l_compareto_gt_ct, l_compareto_gt_values,10,1);
1183: end if;
1184: end if;
1185:
1186: --dbms_output.put_line ('before executing cursor');

Line 1191: ignore := dbms_sql.execute(l_cursor);

1187: if g_debug_on then
1188: l_debug_msg := l_debug_msg || 'before executing cursor';
1189: end if;
1190:
1191: ignore := dbms_sql.execute(l_cursor);
1192:
1193: --dbms_output.put_line ('after executing cursor:'||ignore);
1194: if g_debug_on then
1195: l_debug_msg := l_debug_msg || 'after executing cursor:' || ignore;

Line 1199: l_num_of_rows_fetched := dbms_sql.fetch_rows(l_cursor);

1195: l_debug_msg := l_debug_msg || 'after executing cursor:' || ignore;
1196: end if;
1197:
1198: loop
1199: l_num_of_rows_fetched := dbms_sql.fetch_rows(l_cursor);
1200: --dbms_output.put_line('row fetched:'||l_num_of_rows_fetched);
1201: if g_debug_on then
1202: l_debug_msg := l_debug_msg || 'row fetched:'||l_num_of_rows_fetched;
1203: end if;

Line 1215: dbms_sql.column_value(l_cursor, l_actual_col_index(i), l_actual_values(i));

1211:
1212: if (l_calculate_actual) then
1213: --l_temp_actual_formula := l_Actual_formula;
1214: for i in 1..l_actual_count loop
1215: dbms_sql.column_value(l_cursor, l_actual_col_index(i), l_actual_values(i));
1216: end loop;
1217:
1218: GET_CALCULATED_VALUE
1219: (p_formula => l_Actual_formula

Line 1244: dbms_sql.column_value(l_cursor, l_actual_val_cnt, l_actual_value);

1240: if g_debug_on then
1241: l_debug_msg := l_debug_msg || 'l_actual_val_cnt:'||l_actual_val_cnt;
1242: end if;
1243:
1244: dbms_sql.column_value(l_cursor, l_actual_val_cnt, l_actual_value);
1245: end if;
1246: end if;
1247: l_actual_tbl(l_row_found) := l_actual_value;
1248: --dbms_output.put_line ('actual'||l_row_found||':'||l_actual_tbl(l_row_found));

Line 1256: dbms_sql.column_value(l_cursor, l_compareto_col_index(i), l_compareto_values(i));

1252:
1253: if (l_calculate_compareto) then
1254: --l_temp_compareto_formula := l_compareto_formula;
1255: for i in 1..l_compareto_count loop
1256: dbms_sql.column_value(l_cursor, l_compareto_col_index(i), l_compareto_values(i));
1257: --dbms_output.put_line ('compareto'||i||':'||l_compareto_values(i));
1258: if g_debug_on then
1259: l_debug_msg := l_debug_msg || 'compareto'||i||':'||l_compareto_values(i);
1260: end if;

Line 1290: dbms_sql.column_value(l_cursor, l_compareto_val_cnt, l_compareto_value);

1286: end;
1287: */
1288: else
1289: if (l_compareto_val_cnt > 0) then
1290: dbms_sql.column_value(l_cursor, l_compareto_val_cnt, l_compareto_value);
1291: end if;
1292: end if;
1293: l_compareto_tbl(l_row_found) := l_compareto_value;
1294: --dbms_output.put_line ('compareto'||l_row_found||':'||l_compareto_tbl(l_row_found));

Line 1300: dbms_sql.column_value (l_cursor, l_viewby_val_cnt, l_viewby_tbl(l_row_found));

1296: l_debug_msg := l_debug_msg || 'compareto'||l_row_found||':'||l_compareto_tbl(l_row_found);
1297: end if;
1298:
1299: if (l_viewby_val_cnt > 0) then
1300: dbms_sql.column_value (l_cursor, l_viewby_val_cnt, l_viewby_tbl(l_row_found));
1301: end if;
1302:
1303: if (l_viewby_id_cnt > 0) then
1304: dbms_sql.column_value (l_cursor, l_viewby_id_cnt, l_viewbyid_tbl(l_row_found));

Line 1304: dbms_sql.column_value (l_cursor, l_viewby_id_cnt, l_viewbyid_tbl(l_row_found));

1300: dbms_sql.column_value (l_cursor, l_viewby_val_cnt, l_viewby_tbl(l_row_found));
1301: end if;
1302:
1303: if (l_viewby_id_cnt > 0) then
1304: dbms_sql.column_value (l_cursor, l_viewby_id_cnt, l_viewbyid_tbl(l_row_found));
1305: end if;
1306:
1307: if (l_actual_gt_ct > 0) then
1308: --dbms_output.put_line ('Getting the actual gt value');

Line 1309: dbms_sql.column_value (l_cursor, l_actual_gt_ct, l_actual_gt_value);

1305: end if;
1306:
1307: if (l_actual_gt_ct > 0) then
1308: --dbms_output.put_line ('Getting the actual gt value');
1309: dbms_sql.column_value (l_cursor, l_actual_gt_ct, l_actual_gt_value);
1310: l_actual_gt_values(l_row_found) := l_actual_gt_value;
1311: end if;
1312: if (l_compareto_gt_ct > 0) then
1313: --dbms_output.put_line ('Getting the compareto gt value');

Line 1314: dbms_sql.column_value (l_cursor, l_compareto_gt_ct, l_compareto_gt_value);

1310: l_actual_gt_values(l_row_found) := l_actual_gt_value;
1311: end if;
1312: if (l_compareto_gt_ct > 0) then
1313: --dbms_output.put_line ('Getting the compareto gt value');
1314: dbms_sql.column_value (l_cursor, l_compareto_gt_ct, l_compareto_gt_value);
1315: l_compareto_gt_values(l_row_found) := l_compareto_gt_value;
1316: end if;
1317:
1318: else

Line 1320: dbms_sql.column_Value (l_cursor, l_actual_val_cnt, l_actual_tbl);

1316: end if;
1317:
1318: else
1319: if (l_Actual_val_cnt > 0) then
1320: dbms_sql.column_Value (l_cursor, l_actual_val_cnt, l_actual_tbl);
1321: end if;
1322: if (l_compareto_val_cnt > 0) then
1323: dbms_sql.column_value (l_cursor, l_compareto_val_cnt, l_compareto_tbl);
1324: end if;

Line 1323: dbms_sql.column_value (l_cursor, l_compareto_val_cnt, l_compareto_tbl);

1319: if (l_Actual_val_cnt > 0) then
1320: dbms_sql.column_Value (l_cursor, l_actual_val_cnt, l_actual_tbl);
1321: end if;
1322: if (l_compareto_val_cnt > 0) then
1323: dbms_sql.column_value (l_cursor, l_compareto_val_cnt, l_compareto_tbl);
1324: end if;
1325: if (l_viewby_val_cnt > 0) then
1326: dbms_sql.column_value (l_cursor, l_viewby_val_cnt, l_viewby_tbl);
1327: end if;

Line 1326: dbms_sql.column_value (l_cursor, l_viewby_val_cnt, l_viewby_tbl);

1322: if (l_compareto_val_cnt > 0) then
1323: dbms_sql.column_value (l_cursor, l_compareto_val_cnt, l_compareto_tbl);
1324: end if;
1325: if (l_viewby_val_cnt > 0) then
1326: dbms_sql.column_value (l_cursor, l_viewby_val_cnt, l_viewby_tbl);
1327: end if;
1328: if (l_viewby_id_cnt > 0) then
1329: dbms_sql.column_value (l_cursor, l_viewby_id_cnt, l_viewbyid_tbl);
1330: end if;

Line 1329: dbms_sql.column_value (l_cursor, l_viewby_id_cnt, l_viewbyid_tbl);

1325: if (l_viewby_val_cnt > 0) then
1326: dbms_sql.column_value (l_cursor, l_viewby_val_cnt, l_viewby_tbl);
1327: end if;
1328: if (l_viewby_id_cnt > 0) then
1329: dbms_sql.column_value (l_cursor, l_viewby_id_cnt, l_viewbyid_tbl);
1330: end if;
1331: if (l_actual_gt_ct > 0) then
1332: --dbms_output.put_line ('Getting the actual gt value');
1333: dbms_sql.column_value (l_cursor, l_actual_gt_ct, l_actual_gt_values);

Line 1333: dbms_sql.column_value (l_cursor, l_actual_gt_ct, l_actual_gt_values);

1329: dbms_sql.column_value (l_cursor, l_viewby_id_cnt, l_viewbyid_tbl);
1330: end if;
1331: if (l_actual_gt_ct > 0) then
1332: --dbms_output.put_line ('Getting the actual gt value');
1333: dbms_sql.column_value (l_cursor, l_actual_gt_ct, l_actual_gt_values);
1334: end if;
1335: if (l_compareto_gt_ct > 0) then
1336: --dbms_output.put_line ('Getting the compareto gt value');
1337: dbms_sql.column_value (l_cursor, l_compareto_gt_ct, l_compareto_gt_values);

Line 1337: dbms_sql.column_value (l_cursor, l_compareto_gt_ct, l_compareto_gt_values);

1333: dbms_sql.column_value (l_cursor, l_actual_gt_ct, l_actual_gt_values);
1334: end if;
1335: if (l_compareto_gt_ct > 0) then
1336: --dbms_output.put_line ('Getting the compareto gt value');
1337: dbms_sql.column_value (l_cursor, l_compareto_gt_ct, l_compareto_gt_values);
1338: end if;
1339: exit;
1340: end if;
1341: end loop;

Line 1369: dbms_sql.close_cursor(l_cursor);

1365: x_actual_value(l_indx) := l_actual_rec;
1366: l_indx := l_indx+1;
1367: end loop;
1368: end if;
1369: dbms_sql.close_cursor(l_cursor);
1370: if (l_row_found <= 0) then
1371: x_return_status := FND_API.G_RET_STS_SUCCESS;
1372: x_msg_data := 'NO DATA FOUND';
1373: end if;

Line 1381: if (dbms_sql.IS_OPEN(l_cursor)) then

1377: end if;
1378: x_msg_data := l_debug_msg || x_msg_data;
1379: EXCEPTION
1380: when others then
1381: if (dbms_sql.IS_OPEN(l_cursor)) then
1382: dbms_sql.close_cursor(l_cursor);
1383: end if;
1384: x_return_status := FND_API.G_RET_STS_ERROR;
1385: -- x_return_status := SQLERRM;

Line 1382: dbms_sql.close_cursor(l_cursor);

1378: x_msg_data := l_debug_msg || x_msg_data;
1379: EXCEPTION
1380: when others then
1381: if (dbms_sql.IS_OPEN(l_cursor)) then
1382: dbms_sql.close_cursor(l_cursor);
1383: end if;
1384: x_return_status := FND_API.G_RET_STS_ERROR;
1385: -- x_return_status := SQLERRM;
1386: --dbms_output.put_line ('The err 2 is '|| x_return_Status);

Line 1748: l_desc_tab dbms_sql.desc_tab;

1744: l_viewby_table VARCHAR2(32000);
1745: l_target_alias VARCHAR2(50);
1746: l_has_target VARCHAR2(1);
1747: l_cursor integer;
1748: l_desc_tab dbms_sql.desc_tab;
1749: l_viewby_val_cnt number := 0;
1750: l_viewby_id_cnt number := 0;
1751: l_col_count integer;
1752: ignore integer;

Line 1944: l_cursor := dbms_sql.open_cursor;

1940: p_bind_variables => l_plsql_bind_variables,
1941: x_bind_var_tbl => l_bind_var_tbl);
1942: end if;
1943:
1944: l_cursor := dbms_sql.open_cursor;
1945: dbms_sql.parse(l_cursor, l_query_string, DBMS_SQL.native);
1946:
1947: if (l_bind_var_tbl.COUNT > 0) then
1948: for i in l_bind_var_tbl.FIRST..l_bind_var_tbl.LAST loop

Line 1945: dbms_sql.parse(l_cursor, l_query_string, DBMS_SQL.native);

1941: x_bind_var_tbl => l_bind_var_tbl);
1942: end if;
1943:
1944: l_cursor := dbms_sql.open_cursor;
1945: dbms_sql.parse(l_cursor, l_query_string, DBMS_SQL.native);
1946:
1947: if (l_bind_var_tbl.COUNT > 0) then
1948: for i in l_bind_var_tbl.FIRST..l_bind_var_tbl.LAST loop
1949: l_bind_col := ':x'|| i;

Line 1962: dbms_sql.bind_variable(l_cursor, l_bind_col, l_bind_var);

1958: l_bind_Var := l_bind_Var_tbl(i);
1959: end if;
1960: end if;
1961: --dbms_output.put_line ('bind var '||(i)||':'|| l_bind_var);
1962: dbms_sql.bind_variable(l_cursor, l_bind_col, l_bind_var);
1963: end loop;
1964: end if;
1965:
1966: --dbms_output.put_line ('before describe columns');

Line 1971: dbms_sql.describe_columns(l_cursor, l_col_count, l_desc_tab);

1967: if g_debug_on then
1968: l_debug_msg := l_debug_msg || 'before describe columns';
1969: end if;
1970:
1971: dbms_sql.describe_columns(l_cursor, l_col_count, l_desc_tab);
1972:
1973: -- get all of the table column attribute codes and base column definitions
1974: -- it includes regular measures and calculation measures
1975: if column_items_cursor%ISOPEN then

Line 2090: dbms_sql.define_column(l_cursor, l_selected_col_index(i), l_measure_value, 200);

2086: end if;
2087:
2088: if l_measure_count > 0 then
2089: for i in 1..l_measure_count loop
2090: dbms_sql.define_column(l_cursor, l_selected_col_index(i), l_measure_value, 200);
2091: end loop;
2092: end if;
2093:
2094: --dbms_output.put_line ('before defining grand total columns');

Line 2102: dbms_sql.define_column(l_cursor, l_gt_index(i), l_gt_value, 200);

2098:
2099: if l_gt_count > 0 then
2100: for i in 1..l_gt_count loop
2101: if (l_gt_index(i) is not null and l_gt_index(i) > 0) then
2102: dbms_sql.define_column(l_cursor, l_gt_index(i), l_gt_value, 200);
2103: end if;
2104: end loop;
2105: end if;
2106:

Line 2113: dbms_sql.define_column(l_cursor, l_viewby_val_cnt, l_viewby_value, 200);

2109: l_debug_msg := l_debug_msg || 'before defining viewby columns';
2110: end if;
2111:
2112: if (l_viewby_val_cnt > 0) then
2113: dbms_sql.define_column(l_cursor, l_viewby_val_cnt, l_viewby_value, 200);
2114: end if;
2115:
2116: --dbms_output.put_line ('before defining viewbyid columns');
2117: if g_debug_on then

Line 2122: dbms_sql.define_column(l_cursor, l_viewby_id_cnt, l_viewbyid_value, 200);

2118: l_debug_msg := l_debug_msg || 'before defining viewbyid columns';
2119: end if;
2120:
2121: if (l_viewby_id_cnt > 0) then
2122: dbms_sql.define_column(l_cursor, l_viewby_id_cnt, l_viewbyid_value, 200);
2123: end if;
2124:
2125: --dbms_output.put_line ('before executing cursor');
2126: if g_debug_on then

Line 2130: ignore := dbms_sql.execute(l_cursor);

2126: if g_debug_on then
2127: l_debug_msg := l_debug_msg || 'before executing cursor';
2128: end if;
2129:
2130: ignore := dbms_sql.execute(l_cursor);
2131:
2132: --dbms_output.put_line ('after executing cursor:'||ignore);
2133: if g_debug_on then
2134: l_debug_msg := l_debug_msg || 'after executing cursor:' || ignore;

Line 2139: l_num_of_rows_fetched := dbms_sql.fetch_rows(l_cursor);

2135: end if;
2136:
2137: l_rec_count := 0;
2138: loop
2139: l_num_of_rows_fetched := dbms_sql.fetch_rows(l_cursor);
2140: exit when l_num_of_rows_fetched = 0;
2141:
2142: --dbms_output.put_line('row fetched:'||l_num_of_rows_fetched);
2143: if g_debug_on then

Line 2148: dbms_sql.column_value (l_cursor, l_viewby_val_cnt, l_viewby_value);

2144: l_debug_msg := l_debug_msg || 'row fetched:'||l_num_of_rows_fetched;
2145: end if;
2146:
2147: if (l_viewby_val_cnt > 0) then
2148: dbms_sql.column_value (l_cursor, l_viewby_val_cnt, l_viewby_value);
2149: end if;
2150:
2151: if (l_viewby_id_cnt > 0) then
2152: dbms_sql.column_value (l_cursor, l_viewby_id_cnt, l_viewby_id);

Line 2152: dbms_sql.column_value (l_cursor, l_viewby_id_cnt, l_viewby_id);

2148: dbms_sql.column_value (l_cursor, l_viewby_val_cnt, l_viewby_value);
2149: end if;
2150:
2151: if (l_viewby_id_cnt > 0) then
2152: dbms_sql.column_value (l_cursor, l_viewby_id_cnt, l_viewby_id);
2153: end if;
2154:
2155: if l_measure_count > 0 then
2156: for i in 1..l_measure_count loop

Line 2157: dbms_sql.column_value(l_cursor, l_selected_col_index(i), l_selected_measure_values(i));

2153: end if;
2154:
2155: if l_measure_count > 0 then
2156: for i in 1..l_measure_count loop
2157: dbms_sql.column_value(l_cursor, l_selected_col_index(i), l_selected_measure_values(i));
2158: end loop;
2159: end if;
2160:
2161: if l_gt_count > 0 then

Line 2164: dbms_sql.column_value(l_cursor, l_gt_index(i), l_selected_gt_values(i));

2160:
2161: if l_gt_count > 0 then
2162: for i in 1..l_gt_count loop
2163: if (l_gt_index(i) is not null and l_gt_index(i) > 0) then
2164: dbms_sql.column_value(l_cursor, l_gt_index(i), l_selected_gt_values(i));
2165: end if;
2166: end loop;
2167: end if;
2168:

Line 2229: dbms_sql.close_cursor(l_cursor);

2225: l_row_found := l_row_found+1;
2226: --dbms_output.put_line ('row found:'||l_row_found);
2227: end loop;
2228:
2229: dbms_sql.close_cursor(l_cursor);
2230:
2231: if (l_row_found <= 0) then
2232: x_return_status := FND_API.G_RET_STS_SUCCESS;
2233: x_msg_data := 'NO DATA FOUND';

Line 2240: if (dbms_sql.IS_OPEN(l_cursor)) then

2236: x_msg_data := l_debug_msg || x_msg_data;
2237:
2238: EXCEPTION
2239: when others then
2240: if (dbms_sql.IS_OPEN(l_cursor)) then
2241: dbms_sql.close_cursor(l_cursor);
2242: end if;
2243: x_return_status := FND_API.G_RET_STS_ERROR;
2244: -- x_return_status := SQLERRM;

Line 2241: dbms_sql.close_cursor(l_cursor);

2237:
2238: EXCEPTION
2239: when others then
2240: if (dbms_sql.IS_OPEN(l_cursor)) then
2241: dbms_sql.close_cursor(l_cursor);
2242: end if;
2243: x_return_status := FND_API.G_RET_STS_ERROR;
2244: -- x_return_status := SQLERRM;
2245: --dbms_output.put_line ('The err 2 is '|| x_return_Status);

Line 2283: l_cursor := dbms_sql.open_cursor;

2279:
2280: l_sql := 'select '||l_formula||' from dual';
2281: --dbms_output.put_line('sql: '||l_sql);
2282:
2283: l_cursor := dbms_sql.open_cursor;
2284: dbms_sql.parse(l_cursor, l_sql, DBMS_SQL.native);
2285:
2286: --dbms_output.put_line ('before binding');
2287:

Line 2284: dbms_sql.parse(l_cursor, l_sql, DBMS_SQL.native);

2280: l_sql := 'select '||l_formula||' from dual';
2281: --dbms_output.put_line('sql: '||l_sql);
2282:
2283: l_cursor := dbms_sql.open_cursor;
2284: dbms_sql.parse(l_cursor, l_sql, DBMS_SQL.native);
2285:
2286: --dbms_output.put_line ('before binding');
2287:
2288: if (l_bind_count > 0) then

Line 2291: dbms_sql.bind_variable(l_cursor, l_bind_col, l_bind_values(i));

2287:
2288: if (l_bind_count > 0) then
2289: for i in 1..l_bind_count loop
2290: l_bind_col := ':x'|| i;
2291: dbms_sql.bind_variable(l_cursor, l_bind_col, l_bind_values(i));
2292: --dbms_output.put_line('l_bind_col:'||l_bind_col);
2293: --dbms_output.put_line('l_bind_value:'||l_bind_values(i));
2294: end loop;
2295: end if;

Line 2299: dbms_sql.define_column(l_cursor, 1, x_calculated_value);

2295: end if;
2296:
2297: --dbms_output.put_line ('after binding');
2298:
2299: dbms_sql.define_column(l_cursor, 1, x_calculated_value);
2300:
2301: ignore := dbms_sql.execute_and_fetch(l_cursor);
2302:
2303: --dbms_output.put_line ('after executing');

Line 2301: ignore := dbms_sql.execute_and_fetch(l_cursor);

2297: --dbms_output.put_line ('after binding');
2298:
2299: dbms_sql.define_column(l_cursor, 1, x_calculated_value);
2300:
2301: ignore := dbms_sql.execute_and_fetch(l_cursor);
2302:
2303: --dbms_output.put_line ('after executing');
2304:
2305: dbms_sql.column_value (l_cursor, 1, x_calculated_value);

Line 2305: dbms_sql.column_value (l_cursor, 1, x_calculated_value);

2301: ignore := dbms_sql.execute_and_fetch(l_cursor);
2302:
2303: --dbms_output.put_line ('after executing');
2304:
2305: dbms_sql.column_value (l_cursor, 1, x_calculated_value);
2306:
2307: --dbms_output.put_line ('before closing cursor');
2308:
2309: dbms_sql.close_cursor(l_cursor);

Line 2309: dbms_sql.close_cursor(l_cursor);

2305: dbms_sql.column_value (l_cursor, 1, x_calculated_value);
2306:
2307: --dbms_output.put_line ('before closing cursor');
2308:
2309: dbms_sql.close_cursor(l_cursor);
2310:
2311: exception
2312: when others then
2313: x_calculated_value := null;

Line 2314: dbms_sql.close_cursor(l_cursor);

2310:
2311: exception
2312: when others then
2313: x_calculated_value := null;
2314: dbms_sql.close_cursor(l_cursor);
2315: END GET_CALCULATED_VALUE;
2316:
2317: PROCEDURE SORTBY_BASE_COLUMN_LENGTH
2318: (p_table1 in OUT NOCOPY BISVIEWER.t_char