APEX

Hi,

            There is a Classic Report, the type of the report is **'Function Body Returning SQL Query’ ,** the generic Column Count is **347** for this report but in the front end only 10 columns is shown dynamically.

The query for this report is below:

declare
v_data VARCHAR2(500);
v_columns VARCHAR2(200);
v_column_1 VARCHAR2(200);
v_column_2 VARCHAR2(200);
v_rows VARCHAR2(200);
v_title_1 VARCHAR2(100);
v_title_2 VARCHAR2(100);
v_title_3 VARCHAR2(100);
v_measure_1 VARCHAR2(100);
v_measure_2 VARCHAR2(100);
v_measure_3 VARCHAR2(100);
v_final_sql VARCHAR2(32000);
LV_GROUP VARCHAR2(10000);
lv_group_sql VARCHAR2(2000);
v_table_name VARCHAR2(10000);
l_sql varchar2(32000);
l_card_rec ITCC_DS_ELEMENTS%rowtype;
l_rep_rec ITCC_DS_REPOSITORY_ITEMS%rowtype;

l_x_column varchar2(32000);
v_expression varchar2(32000);

begin

--For Pivot--
Select
PIVOT_COLUMNS_FIELDS_LIST,
substr(PIVOT_COLUMNS_FIELDS_LIST,1,instr(PIVOT_COLUMNS_FIELDS_LIST,':')-1),
substr(PIVOT_COLUMNS_FIELDS_LIST,instr(PIVOT_COLUMNS_FIELDS_LIST,':')+1),
PIVOT_ROWS_FIELDS_LIST,
SUBSTR(PIVOT_MEASURE_1,INSTR(PIVOT_MEASURE_1,'-')+2),
SUBSTR(PIVOT_MEASURE_1,1,INSTR(PIVOT_MEASURE_1,'-')-2),
SUBSTR(PIVOT_MEASURE_2,INSTR(PIVOT_MEASURE_2,'-')+2),
SUBSTR(PIVOT_MEASURE_2,1,INSTR(PIVOT_MEASURE_2,'-')-2),
SUBSTR(PIVOT_MEASURE_3,INSTR(PIVOT_MEASURE_3,'-')+2),
SUBSTR(PIVOT_MEASURE_3,1,INSTR(PIVOT_MEASURE_3,'-')-2),
ITCC_TABLE_NAME,
ide.EXPRESSION_FILTER_CALC
INTO
v_columns,v_column_1,v_column_2,v_rows,v_title_1,v_measure_1,v_title_2,v_measure_2,v_title_3,v_measure_3,v_table_name,v_expression
from
ITCC_DS_ELEMENTS IDE, ITCC_DS_REPOSITORY_ITEMS IDRE
where IDE.DATA_SOURCE_REPOSITORY__ID = IDRE.ITEM_ID
AND DS_ELEMENT_ID = :P2019_ELEMENT_ID;
--End Pivot --

if upper(:P2019_TYPE) = upper('chart')
then
l_x_column :=:P2019_X_COLUMN;

if l_x_column = '$Expression' then
l_x_column :=v_expression;
else
l_x_column := nvl('"'||l_x_column||'"','null');
end if;

if :P2019_SERIES_COLUM IS NOT NULL AND :P2019_SERIES_VALUE IS NOT NULL THEN
l_sql := 'select * from ' || nvl(:P2019_ITCC_TABLE_NAME,'TITT_CASES_DYNAMIC_DASH') || ' where '
|| '"'||:P2019_SERIES_COLUM || '"'|| ' = ' || '''' || :P2019_SERIES_VALUE|| ''''
|| ' and '
|| ' ' || l_x_column ||' ' || ' = ' || '''' || :P2019_X_VALUE || ''''
|| ' and '
|| nvl(:P2019_DATABASE_SQL,'1=1');
ELSE
l_sql := 'select * from ' || nvl(:P2019_ITCC_TABLE_NAME,'TITT_CASES_DYNAMIC_DASH') || ' where '
|| ' ' || l_x_column ||' ' || ' = ' || '''' || :P2019_X_VALUE || ''''
|| ' and '
|| nvl(:P2019_DATABASE_SQL,'1=1');
END IF;

:P2019_YEST := l_sql;

:P2019_YEST := replace(:P2019_YEST,'and and','and');

elsif
upper(:P2019_TYPE) = upper('list')
then
l_sql := 'select * from ('||:P2019_DATABASE_SQL || ') where '
|| :P2019_DRILL_COLUMN_NAME || ' = ' || '''' || :P2019_DRILL_VALUE || '''';
--added debasis 27/02/2023--
elsif
upper(:P2019_TYPE) = upper('pivot')
then

select * into l_card_rec from ITCC_DS_ELEMENTS te
where te.DS_ELEMENT_ID = :P432_DS_ELEMENT_ID;

select * into l_rep_rec from ITCC_DS_REPOSITORY_ITEMS ti where ti.ITEM_ID =l_card_rec.DATA_SOURCE_REPOSITORY__ID ;

v_table_name := ' ( select * from '||v_table_name||' where '||nvl(ITCC_DS_PCK.get_repository_sql_with_params(l_card_rec,l_rep_rec),'1=1') ||')';

IF v\_column\_2 IS NOT NULL THEN  
           IF v\_column\_1 IS NOT NULL THEN   
           l\_sql:= 'SELECT \* FROM  '||v\_table\_name||'  
           WHERE   
           "'||v\_rows||'" = :P2019\_PARAMETER\_1  
           and "'||v\_column\_2||'" = :P2019\_PARAMETER\_3  
           and "'||v\_column\_1||'" = :P2019\_PARAMETER\_4  
           ';  
           ELSE  
           l\_sql:= 'SELECT \* FROM '||v\_table\_name||'  
           WHERE   
           "'||v\_rows||'" = :P2019\_PARAMETER\_1  
           and "'||v\_column\_2||'" = :P2019\_PARAMETER\_3  
           ';  
           END IF;  
 ELSE  
     l\_sql:= 'SELECT \* FROM '||v\_table\_name||'  
     WHERE   
     "'||v\_rows||'" = :P2019\_PARAMETER\_1';  
 END IF;  

:P2019_YEST := l_sql;

else

:P2019_DATABASE_SQL := replace(:P2019_DATABASE_SQL,chr(13),' ');
:P2019_DATABASE_SQL := replace(:P2019_DATABASE_SQL,chr(10),' ');

:P2019_DATABASE_SQL := replace(:P2019_DATABASE_SQL,' ',' ');
:P2019_DATABASE_SQL := replace(:P2019_DATABASE_SQL,' ',' ');

:P2019_DATABASE_SQL := replace(:P2019_DATABASE_SQL,'and and','and');

insert into XX_TMP_DEBUG(ddate,DDBEUG) values ( sysdate, :P2019_DATABASE_SQL);
l_sql:= :P2019_DATABASE_SQL;

end if;

--:P2019_TABLE_NAME := 'table:' ||v_table_name;

return l_sql;

exception when others then return 'select ''Error On Drill Down'' error from dual';
end;

Now on 23.2 we want to transfer the layout to be interactive report with all the features of IR, but it does not support generic columns.

Any idea how to overcome this would be of great help.

Thanks in advance.