ORACLE MASTERMINDS: Excel Download Package in Apex

Monday, January 2, 2017

Excel Download Package in Apex

Step 1:

Create below Package in DB.

Package Spec

create or replace PACKAGE doy_PK_APEX_EXCEL_DOWNLOAD
IS
TYPE query_res IS REF CURSOR;

PROCEDURE excel_initialize;

PROCEDURE print_headings;

PROCEDURE set_sql (
  p_session_id IN NUMBER,
  p_app_id IN NUMBER,
  p_page_id   IN NUMBER,
  p_tot_rows IN NUMBER,
  p_sql   OUT   query_res,
  p_tot_sql   OUT   query_res
);

PROCEDURE print_data (
  p_session_id IN NUMBER,
  p_app_id IN NUMBER,
  p_page_id   IN NUMBER
);

l_session_id NUMBER;
l_ir_id   NUMBER;
l_report_id   NUMBER;
l_app_id NUMBER;
l_page_id   NUMBER;
l_user_id   VARCHAR2 (10);
l_location VARCHAR2 (100);
l_month   VARCHAR2 (100);
l_schtype   VARCHAR2 (100);
l_scheme VARCHAR2 (100);
l_distributor   VARCHAR2 (100);
l_invsegment VARCHAR2 (100);
l_base_report_id NUMBER;

PROCEDURE get_excel (
  p_session_id   IN NUMBER,
  p_app_id   IN NUMBER,
  p_page_id IN NUMBER,
  p_static   IN VARCHAR2,
  p_user_id IN VARCHAR2,
  p_report_output IN CLOB
);

gv_session_id   NUMBER;
gv_company_code   VARCHAR2 (100);
gv_static   VARCHAR2 (1);
gv_user_id VARCHAR2 (32000);
l_dealer_code   VARCHAR2 (15);
l_ta_scheme   VARCHAR2 (100);
g_report_output   CLOB;
g_report_header   CLOB;
g_report_header2 CLOB;
g_report_header3 CLOB;
g_report_header4 CLOB;
g_report_header5 CLOB;
g_report_header6 CLOB;
g_file_name   VARCHAR2 (2000);
g_report_footer1 CLOB ;
g_report_footer2 CLOB ;
g_report_footer3 CLOB ;

PROCEDURE close_excel;

PROCEDURE ds_get_excel (
  p_session_id IN NUMBER,
  p_app_id IN NUMBER,
  p_page_id   IN NUMBER,
  p_static IN VARCHAR2,
  p_user_id   IN VARCHAR2,
  p_report_output   IN CLOB,
  p_report_header   IN CLOB,
  p_report_header2   CLOB,
  p_report_header3   CLOB,
  p_report_header4   CLOB,
  p_report_header5   CLOB,
  p_report_header6   CLOB,
  p_file_name VARCHAR2,
  p_report_footer1   CLOB default null,
  p_report_footer2   CLOB default null,
  p_report_footer3   CLOB default null
);

PROCEDURE ps_ds_get_excel_new (
  p_session_id IN NUMBER,
  p_app_id IN NUMBER,
  p_page_id   IN NUMBER,
  p_static IN VARCHAR2,
  p_user_id   IN VARCHAR2,
  p_report_output   IN CLOB,
  p_report_header   IN CLOB,
  p_report_header2   CLOB,
  p_report_header3   CLOB,
  p_report_header4   CLOB,
  p_report_header5   CLOB,
  p_report_header6   CLOB,
  p_file_name VARCHAR2,
  p_report_footer1   CLOB DEFAULT NULL,
  p_report_footer2   CLOB DEFAULT NULL,
  p_report_footer3   CLOB DEFAULT NULL
);
END doy_pk_apex_excel_download;

Package Body

create or replace PACKAGE BODY doy_pk_apex_excel_download
IS
PROCEDURE excel_initialize
IS
  p_user_id   VARCHAR2 (200) := gv_user_id;
  p_session_id NUMBER := gv_session_id;
BEGIN
  OWA_UTIL.mime_header ('application/octet', FALSE);
  HTP.p ( 'Content-Disposition: attachment; filename='
|| g_file_name
|| '.xls'
  );
--   HTP.p ('Content-type: application/vnd.ms-excel;');
  HTP.p ('Pragma: cache');
  HTP.p ('Expires: 0');
  OWA_UTIL.http_header_close;
END;

PROCEDURE print_headings
IS
  p_cols   wwv_flow_global.vc_arr2;
  p_columns VARCHAR2 (4000);
  p_report_label   VARCHAR2 (4000);
  p_disp_condition   VARCHAR2 (2000);
  p_disp_condition2 VARCHAR2 (2000);
  p_title VARCHAR2 (2000);

  CURSOR get_cols
  IS
SELECT column_alias, report_label
FROM apex_application_page_ir_col
  WHERE application_id = l_app_id AND page_id = l_page_id
ORDER BY display_order;

  CURSOR col_disp_yn (p_col_name VARCHAR2)
  IS
SELECT display_condition, display_condition2
FROM apex_application_page_ir_col
  WHERE application_id = l_app_id
  AND page_id = l_page_id
  AND display_condition IS NOT NULL
  AND UPPER (column_alias) = UPPER (p_col_name);

  l_increment NUMBER   := 0;
BEGIN
  HTP.p
('<table border=1 style="background-color:yellow foreground-color:white ">'
);
  HTP.p ('<tr>');

  IF get_cols%ISOPEN
  THEN
CLOSE get_cols;
  END IF;

  FOR get_cols1 IN get_cols
  LOOP
p_columns := p_columns || ':' || get_cols1.report_label;
  END LOOP;

  p_columns := SUBSTR (p_columns, 2);
  p_cols := apex_util.string_to_table (p_columns);

  FOR i IN p_cols.FIRST .. p_cols.LAST
  LOOP
l_increment := l_increment + 1;
/************for hiding conditionally hidden columns start **********
IF col_disp_yn%ISOPEN THEN
  CLOSE col_disp_yn;
END IF;
  OPEN col_disp_yn(p_cols(l_increment));
  FETCH col_disp_yn INTO p_disp_condition,p_disp_condition2;
  CLOSE col_disp_yn;
if p_disp_condition is not null THEN
if p_disp_condition2 <> l_location THEN
l_increment := l_increment + 1;
end if;

end if;
  /************for hiding conditionally hidden columns end **********/

--dbms_output.put_line(t_cols(i));
HTP.p
  ( '<td ><font style="color:blue;width:50px;font-weight:800"  >'
|| 'EMPLOYEE ID'
|| '</font></td>'
  );
p_disp_condition := NULL;
  END LOOP;

  l_increment := 0;
  HTP.p ('</tr>');
  HTP.p ('</table>');
END;

PROCEDURE set_sql (
  p_session_id IN NUMBER,
  p_app_id IN NUMBER,
  p_page_id   IN NUMBER,
  p_tot_rows IN NUMBER,
  p_sql   OUT   query_res,
  p_tot_sql   OUT   query_res
)
IS
  CURSOR get_ir_id
  IS
SELECT interactive_report_id, report_id
FROM apex_application_page_ir_rpt
  WHERE session_id = p_session_id
  AND page_id = p_page_id
  AND base_report_id = NVL (l_base_report_id, base_report_id);

  l_ir_id   NUMBER;
  l_report_id   NUMBER;
  p_cols wwv_flow_global.vc_arr2;
  p_tot_cols wwv_flow_global.vc_arr2;
  p_columns   VARCHAR2 (4000);
  p_columns1 VARCHAR2 (4000);
  p_report_label VARCHAR2 (4000);
  p_format_mask   VARCHAR2 (1000);
  p_max   NUMBER;
  lv_v_slno   VARCHAR2 (32767);
  p_title   VARCHAR2 (50);

  CURSOR get_title
  IS
SELECT SUBSTR (region_name, 1, 50)
FROM apex_application_page_regions
  WHERE application_id = l_app_id
  AND page_id = l_page_id
  AND source_type = 'Interactive Report';

  CURSOR get_cols
  IS
SELECT report_columns
FROM apex_application_page_ir_rpt
  WHERE application_id = p_app_id
  AND page_id = p_page_id
  AND session_id = p_session_id
  AND interactive_report_id = l_ir_id
  AND report_id = l_report_id;

  CURSOR get_format_mask (p_column VARCHAR2)
  IS
SELECT column_alias, report_label, format_mask
FROM apex_application_page_ir_col
  WHERE application_id = p_app_id
  AND page_id = p_page_id
  AND interactive_report_id = l_ir_id
  AND column_alias = p_column
  /* for hiding ALL columns */
  AND display_order NOT BETWEEN 3
  AND (SELECT MIN (display_order) - 1
FROM apex_application_page_ir_col
  WHERE application_id = p_app_id
  AND page_id = p_page_id);

  p_set_query   VARCHAR2 (18000);
  p_set_tot_query   VARCHAR2 (18000);

  CURSOR get_query (c_ir_id NUMBER)
  IS
SELECT sql_query
FROM apex_application_page_ir
  WHERE interactive_report_id = c_ir_id AND page_id = p_page_id;

  CURSOR get_conditions (c_ir_id NUMBER, c_report_id NUMBER)
  IS
SELECT REPLACE
(REPLACE (condition_sql,
'#APXWS_EXPR#',
CONCAT (CONCAT ('''', condition_expression),
'''')
  ),
  '#APXWS_EXPR2#',
  CONCAT (CONCAT ('''', condition_expression2), '''')
) sql_condition
FROM apex_application_page_ir_cond
  WHERE interactive_report_id = c_ir_id
  AND application_id = p_app_id
  AND report_id = c_report_id
  AND page_id = p_page_id
  AND condition_enabled = 'Yes'
  AND condition_type = 'Filter';

  l_sql_query   VARCHAR2 (32000);
  l_tot_sql_query   VARCHAR2 (32000); --
  l_report_query VARCHAR2 (32000);
  l_tot_report_query VARCHAR2 (32000); --
  l_query_conditions VARCHAR2 (24000);
  l_sql_condition   VARCHAR2 (24000);
  l_conditions_exist VARCHAR2 (1)   := 'N';
  v_title   VARCHAR2 (4000);
BEGIN
  IF get_ir_id%ISOPEN
  THEN
CLOSE get_ir_id;
  END IF;

  OPEN get_ir_id;

  FETCH get_ir_id
INTO l_ir_id, l_report_id;

  CLOSE get_ir_id;

/****************************/
  IF get_cols%ISOPEN
  THEN
CLOSE get_cols;
  END IF;

  OPEN get_cols;

  FETCH get_cols
INTO p_columns;

  CLOSE get_cols;

  p_cols := apex_util.string_to_table (p_columns);
  p_tot_cols := apex_util.string_to_table (p_columns);
  p_max := p_cols.LAST;
  p_set_query := '''' || '<td>' || '''||';
  p_set_tot_query := '''' || '<td>' || '''||'; --
/*******************displaying title row  start */
  -- IF p_tot_rows > 0.1 THEN
  HTP.p ('<table border=1 style="background-color:#FFA500">');

  IF get_title%ISOPEN
  THEN
CLOSE get_title;
  END IF;

  OPEN get_title;

  FETCH get_title
INTO p_title;

  CLOSE get_title;

  l_sql_query := g_report_output;

  OPEN p_sql FOR TO_CHAR (l_sql_query);

  COMMIT;
END set_sql;

PROCEDURE print_data (
  p_session_id IN NUMBER,
  p_app_id IN NUMBER,
  p_page_id   IN NUMBER
)
IS
  p_tot_rows   NUMBER := 0;
  query_result   query_res;
  query_columns VARCHAR2 (32000);
  query_tot_result   query_res;
  query_tot_columns VARCHAR2 (32000);
  v_num1   VARCHAR (32000);
  v_num2   VARCHAR (32000);
  v_num3   VARCHAR (500);
  v_num4   VARCHAR (500);
  v_num5   VARCHAR (500);
  v_num6   VARCHAR (500);
  v_num7   VARCHAR (500);
  v_num8   VARCHAR (500);
BEGIN
  /*executing cursor to find number of rows start
  set_sql(p_session_id,p_app_id,p_page_id,0.1,query_result,query_tot_result);

  LOOP
  FETCH query_result INTO query_columns;
  EXIT WHEN query_result%NOTFOUND;
  p_tot_rows := p_tot_rows + 1; --TO find number of rows;
  END LOOP;
  CLOSE query_result;
  CLOSE query_tot_result;
  executing cursor to find number of rows end */
  set_sql (p_session_id,
p_app_id,
p_page_id,
p_tot_rows,
query_result,
query_tot_result
  );
  HTP.p ('<table border=1>');

  LOOP
FETCH query_result
  --INTO V_NUM1; --,V_NUM2,V_NUM3,V_NUM4,V_NUM5,V_NUM6,V_NUM7,V_NUM8;
INTO  query_columns;

EXIT WHEN query_result%NOTFOUND;
HTP.p ('<tr>');
HTP.p (query_columns);
  --htp.p('<br>'||'a'<);
  --htp.p('b');
--htp.p(query_columns."Serial Number");
  --HTP.p(V_NUM1);
HTP.p ('</tr>');
  END LOOP;

  CLOSE query_result;

  /* htp.p('<table border=1>');
FETCH query_tot_result INTO query_tot_columns;
  htp.p('<tr>');
htp.p(query_tot_columns);
htp.p('</tr>');

  CLOSE query_tot_result;
  htp.p('</table>');*/
  HTP.p ('</table>');
  HTMLDB_APPLICATION.g_unrecoverable_error := TRUE;
END;

PROCEDURE close_excel
IS
BEGIN
  -- Send an error code so that the
  -- rest of the HTML does not render
  HTMLDB_APPLICATION.g_unrecoverable_error := TRUE;
END;

PROCEDURE get_excel (
  p_session_id   IN NUMBER,
  p_app_id   IN NUMBER,
  p_page_id IN NUMBER,
  p_static   IN VARCHAR2,
  p_user_id IN VARCHAR2,
  p_report_output IN CLOB
)
IS
BEGIN
  l_session_id := p_session_id;
  l_app_id := p_app_id;
  l_page_id := p_page_id;
  gv_session_id := p_session_id;
  gv_static := p_static;
  gv_user_id := p_user_id;
  g_report_output := p_report_output;
  excel_initialize;
  --  print_headings;
  print_data (p_session_id, p_app_id, p_page_id);
-- pro_error (1 ,'a3' );
-- close_excel;
END;

PROCEDURE ds_set_sql (
  p_session_id IN NUMBER,
  p_app_id IN NUMBER,
  p_page_id   IN NUMBER,
  p_tot_rows IN NUMBER,
  p_sql   OUT   query_res,
  p_tot_sql   OUT   query_res
)
IS
  CURSOR get_ir_id
  IS
SELECT interactive_report_id, report_id
FROM apex_application_page_ir_rpt
  WHERE session_id = p_session_id
  AND page_id = p_page_id
  AND base_report_id = NVL (l_base_report_id, base_report_id);

  l_ir_id   NUMBER;
  l_report_id   NUMBER;
  p_cols wwv_flow_global.vc_arr2;
  p_tot_cols wwv_flow_global.vc_arr2;
  p_columns   VARCHAR2 (4000);
  p_columns1 VARCHAR2 (4000);
  p_report_label VARCHAR2 (4000);
  p_format_mask   VARCHAR2 (1000);
  p_max   NUMBER;
  lv_v_slno   VARCHAR2 (32767);
  p_title   VARCHAR2 (50);

  CURSOR get_title
  IS
SELECT SUBSTR (region_name, 1, 50)
FROM apex_application_page_regions
  WHERE application_id = l_app_id
  AND page_id = l_page_id
  AND source_type = 'Interactive Report';

  CURSOR get_cols
  IS
SELECT report_columns
FROM apex_application_page_ir_rpt
  WHERE application_id = p_app_id
  AND page_id = p_page_id
  AND session_id = p_session_id
  AND interactive_report_id = l_ir_id
  AND report_id = l_report_id;

  CURSOR get_format_mask (p_column VARCHAR2)
  IS
SELECT column_alias, report_label, format_mask
FROM apex_application_page_ir_col
  WHERE application_id = p_app_id
  AND page_id = p_page_id
  AND interactive_report_id = l_ir_id
  AND column_alias = p_column
  /* for hiding ALL columns */
  AND display_order NOT BETWEEN 3
  AND (SELECT MIN (display_order) - 1
FROM apex_application_page_ir_col
  WHERE application_id = p_app_id
  AND page_id = p_page_id);

  p_set_query   VARCHAR2 (18000);
  p_set_tot_query   VARCHAR2 (18000);

  CURSOR get_query (c_ir_id NUMBER)
  IS
SELECT sql_query
FROM apex_application_page_ir
  WHERE interactive_report_id = c_ir_id AND page_id = p_page_id;

  CURSOR get_conditions (c_ir_id NUMBER, c_report_id NUMBER)
  IS
SELECT REPLACE
(REPLACE (condition_sql,
'#APXWS_EXPR#',
CONCAT (CONCAT ('''', condition_expression),
'''')
  ),
  '#APXWS_EXPR2#',
  CONCAT (CONCAT ('''', condition_expression2), '''')
) sql_condition
FROM apex_application_page_ir_cond
  WHERE interactive_report_id = c_ir_id
  AND application_id = p_app_id
  AND report_id = c_report_id
  AND page_id = p_page_id
  AND condition_enabled = 'Yes'
  AND condition_type = 'Filter';

  l_sql_query   VARCHAR2 (32000);
  l_tot_sql_query   VARCHAR2 (32000); --
  l_report_query VARCHAR2 (32000);
  l_tot_report_query VARCHAR2 (32000); --
  l_query_conditions VARCHAR2 (24000);
  l_sql_condition   VARCHAR2 (24000);
  l_conditions_exist VARCHAR2 (1)   := 'N';
  v_title   VARCHAR2 (4000);
BEGIN
  --lv_v_slno := gv_slno;
  IF get_ir_id%ISOPEN
  THEN
CLOSE get_ir_id;
  END IF;

  OPEN get_ir_id;

  FETCH get_ir_id
INTO l_ir_id, l_report_id;

  CLOSE get_ir_id;

/****************************/
  IF get_cols%ISOPEN
  THEN
CLOSE get_cols;
  END IF;

  OPEN get_cols;

  FETCH get_cols
INTO p_columns;

  CLOSE get_cols;

  p_cols := apex_util.string_to_table (p_columns);
  p_tot_cols := apex_util.string_to_table (p_columns);
  p_max := p_cols.LAST;
  p_set_query := '''' || '<td>' || '''||';
  p_set_tot_query := '''' || '<td>' || '''||'; --
/*******************displaying title row  start */
  -- IF p_tot_rows > 0.1 THEN
  HTP.p ('<table border=1 style="background-color:#FFA500">');

  IF get_title%ISOPEN
  THEN
CLOSE get_title;
  END IF;

  OPEN get_title;

  FETCH get_title
INTO p_title;

  CLOSE get_title;

  --select v_heading into v_title from test_heading;
-- pro_error (1 ,'n1' );
  IF g_report_header IS NOT NULL
  THEN
INSERT INTO ap_t_apex_report_heading_n
(report_id, report_header
)
  VALUES (p_page_id, g_report_header
);
  END IF;

  IF g_report_header2 IS NOT NULL
  THEN
INSERT INTO ap_t_apex_report_heading_n
(report_id, report_header
)
  VALUES (p_page_id, g_report_header2
);
  END IF;

  IF g_report_header3 IS NOT NULL
  THEN
INSERT INTO ap_t_apex_report_heading_n
(report_id, report_header
)
  VALUES (p_page_id, g_report_header3
);
  END IF;

  IF g_report_header4 IS NOT NULL
  THEN
INSERT INTO ap_t_apex_report_heading_n
(report_id, report_header
)
  VALUES (p_page_id, g_report_header4
);
  END IF;

  IF g_report_header5 IS NOT NULL
  THEN
INSERT INTO ap_t_apex_report_heading_n
(report_id, report_header
)
  VALUES (p_page_id, g_report_header5
);
  END IF;

  IF g_report_header6 IS NOT NULL
  THEN
INSERT INTO ap_t_apex_report_heading_n
(report_id, report_header
)
  VALUES (p_page_id, g_report_header6
);
  END IF;

  FOR i IN (SELECT report_header
  FROM ap_t_apex_report_heading_n
WHERE report_id = p_page_id)
  LOOP
HTP.p (i.report_header);
  END LOOP;

  HTP.p ('</table>');
  COMMIT;
  l_sql_query := g_report_output;

  OPEN p_sql FOR TO_CHAR (l_sql_query);
END ds_set_sql;

PROCEDURE ds_print_data (
  p_session_id IN NUMBER,
  p_app_id IN NUMBER,
  p_page_id   IN NUMBER
)
IS
  p_tot_rows   NUMBER := 0;
  query_result   query_res;
  query_columns VARCHAR2 (32000);
  query_tot_result   query_res;
  query_tot_columns VARCHAR2 (32000);
  v_num1   VARCHAR (32000);
  v_num2   VARCHAR (32000);
  v_num3   VARCHAR (500);
  v_num4   VARCHAR (500);
  v_num5   VARCHAR (500);
  v_num6   VARCHAR (500);
  v_num7   VARCHAR (500);
  v_num8   VARCHAR (500);
BEGIN
  ds_set_sql (p_session_id,
  p_app_id,
  p_page_id,
  p_tot_rows,
  query_result,
  query_tot_result
);
  HTP.p ('<table border=1>');

  LOOP
FETCH query_result
  INTO query_columns;

EXIT WHEN query_result%NOTFOUND;
HTP.p ('<tr>');
HTP.p (query_columns);
HTP.p ('</tr>');
  END LOOP;

  CLOSE query_result;

  IF g_report_footer1 IS NOT NULL
  THEN
HTP.p ('<tr>');
HTP.p (g_report_footer1);
HTP.p ('</tr>');
  END IF;

  IF g_report_footer2 IS NOT NULL
  THEN
HTP.p ('<tr>');
HTP.p (g_report_footer2);
HTP.p ('</tr>');
  END IF;

  IF g_report_footer3 IS NOT NULL
  THEN
HTP.p ('<tr>');
HTP.p (g_report_footer3);
HTP.p ('</tr>');
  END IF;

  HTP.p ('</table>');
  HTP.p (l_ta_scheme);
  HTMLDB_APPLICATION.g_unrecoverable_error := TRUE;
END;

PROCEDURE ds_get_excel (
  p_session_id IN NUMBER,
  p_app_id IN NUMBER,
  p_page_id   IN NUMBER,
  p_static IN VARCHAR2,
  p_user_id   IN VARCHAR2,
  p_report_output   IN CLOB,
  p_report_header   IN CLOB,
  p_report_header2   CLOB,
  p_report_header3   CLOB,
  p_report_header4   CLOB,
  p_report_header5   CLOB,
  p_report_header6   CLOB,
  p_file_name VARCHAR2,
  p_report_footer1   CLOB,
  p_report_footer2   CLOB,
  p_report_footer3   CLOB
)
IS
BEGIN
  l_session_id := p_session_id;
  l_app_id := p_app_id;
  l_page_id := p_page_id;
  gv_session_id := p_session_id;
  gv_static := p_static;
  gv_user_id := p_user_id;
  g_report_output := p_report_output;
  g_report_header := p_report_header;
  g_report_header2 := p_report_header2;
  g_report_header3 := p_report_header3;
  g_report_header4 := p_report_header4;
  g_report_header5 := p_report_header5;
  g_report_header6 := p_report_header6;
  g_file_name := p_file_name;
  g_report_footer1 := p_report_footer1;
  g_report_footer2 := p_report_footer2;
  g_report_footer3 := p_report_footer3;
  excel_initialize;
  ds_print_data (p_session_id, p_app_id, p_page_id);
  g_report_header := NULL;
  g_report_header2 := NULL;
  g_report_header3 := NULL;
  g_report_header4 := NULL;
  g_report_header5 := NULL;
  g_report_header6 := NULL;
  g_file_name := 'Excel';
  HTP.p ('<tr><td > '' </td></tr>');
  HTP.p ('<tr><td > ''  </td></tr>');
END;

PROCEDURE ps_ds_get_excel_new (
  p_session_id IN NUMBER,
  p_app_id IN NUMBER,
  p_page_id   IN NUMBER,
  p_static IN VARCHAR2,
  p_user_id   IN VARCHAR2,
  p_report_output   IN CLOB,
  p_report_header   IN CLOB,
  p_report_header2   CLOB,
  p_report_header3   CLOB,
  p_report_header4   CLOB,
  p_report_header5   CLOB,
  p_report_header6   CLOB,
  p_file_name VARCHAR2,
  p_report_footer1   CLOB DEFAULT NULL,
  p_report_footer2   CLOB DEFAULT NULL,
  p_report_footer3   CLOB DEFAULT NULL
)
IS
  -- this procedure do not includes HTML tags in Excel output.
  p_sql query_res;
  l_sql_query VARCHAR2 (32000);
  query_columns VARCHAR2 (32000);
BEGIN
  g_file_name := p_file_name;
  /*For initializing the Excel call*/
  excel_initialize;

  /*Printing the column header*/
  IF p_report_header IS NOT NULL
  THEN
HTP.p (TO_CHAR (p_report_header));
  END IF;

  IF p_report_header2 IS NOT NULL
  THEN
HTP.p (TO_CHAR (p_report_header2));
  END IF;

  IF p_report_header3 IS NOT NULL
  THEN
HTP.p (TO_CHAR (p_report_header3));
  END IF;

  IF p_report_header4 IS NOT NULL
  THEN
HTP.p (TO_CHAR (p_report_header4));
  END IF;

  IF p_report_header5 IS NOT NULL
  THEN
HTP.p (TO_CHAR (p_report_header5));
  END IF;

  IF p_report_header6 IS NOT NULL
  THEN
HTP.p (TO_CHAR (p_report_header6));
  END IF;

  HTP.p ('');

  /*Printing the data values from query */
  OPEN p_sql FOR TO_CHAR (p_report_output);

  LOOP
FETCH p_sql
  INTO query_columns;

EXIT WHEN p_sql%NOTFOUND;
HTP.p (query_columns);
  END LOOP;

  CLOSE p_sql;

  HTP.p ('');
  HTP.p (TO_CHAR (p_report_footer1));
  HTP.p (TO_CHAR (p_report_footer2));
  HTP.p (TO_CHAR (p_report_footer3));

  HTMLDB_APPLICATION.g_unrecoverable_error := TRUE;
-- Closing the Excel call
EXCEPTION
  WHEN OTHERS
  THEN
NULL;
END;
END doy_pk_apex_excel_download;

Step 2:

write the PLSQL code in APEX page using our custom package (doy_pk_apex_excel_download.ps_ds_get_excel_new). This process is used to generate Excel file .

DECLARE

p_report_output CLOB;

p_report_output1 CLOB;

p_report_output2 CLOB;

p_report_header CLOB;

p_report_header2 CLOB;

p_report_header3 CLOB;

p_session_id NUMBER;

p_app_id NUMBER;

p_page_id NUMBER;

p_static VARCHAR2 (1);

lv_v_user_id VARCHAR2 (15);

lv_report_name VARCHAR2 (100);

p_user_id VARCHAR2 (200);

lv_v_desc_name VARCHAR2 (1000);

lv_v_where_clase VARCHAR2 (4000);

lv_v_alias_name VARCHAR2 (4000);

lv_v_group_name VARCHAR2 (4000);

lv_v_order_name VARCHAR2 (4000);

lv_v_col_name VARCHAR2 (4000);

v_filename VARCHAR2 (2000);

select_clause VARCHAR2 (32000);

BEGIN

p_report_header :=

CHR (9)

|| CHR (9)

|| 'Critical Patterns'

|| CHR (9);

p_report_header2 := CHR (9) || 'Report Run Date' || CHR (9) || SYSDATE;

lv_v_group_name :=

' group by

homes_fnd_utils.item_number(ii.item_id),

homes_fnd_utils.item_name(ii.item_id),

icp.usage_quantity,

HOMES_FND_UTILS.party_name(ii.CUSTOMER_ID),

icp.material,

icp.number_of_cavity,

to_char(icp.expiry_date,''DD-MON-YYYY'')

having

icp.usage_quantity*nvl(icp.number_of_cavity,1)-sum(hpi.poured_quantity) < 100

to_char(icp.expiry_date,''DD-MON-YYYY'') < sysdate+30';

p_report_output1 := 'SELECT ';

p_report_header3 :=

p_report_header3

|| 'pattern_number'

|| CHR (9)

|| 'item_name'

|| CHR (9)

|| 'customer_name'

|| CHR (9)

|| 'material'

|| CHR (9)

|| 'poured_quantity'

|| CHR (9)

|| 'usage_quantity'

|| CHR (9)

|| 'cavity'

|| CHR (9)

|| 'expiry_date'

|| CHR (9);

p_report_output1 :=

p_report_output1

|| 'TA."pattern_number"||CHR(9)

||TA."item_name"||CHR(9)

||TA."customer_name"||CHR(9)

||TA."material"||CHR(9)

||TA."poured_quantity"||CHR(9)

||TA."usage_quantity"||CHR(9)

||TA."cavity"||CHR(9)

||TA."expiry_date"||CHR(9)

FROM ( ';

select_clause :=

select_clause

|| ' select

homes_fnd_utils.item_number(ii.item_id) "pattern_number",

homes_fnd_utils.item_name(ii.item_id) "item_name",

HOMES_FND_UTILS.party_name(ii.CUSTOMER_ID) "customer_name",

icp.material "material",

sum(hpi.poured_quantity) "poured_quantity",

icp.usage_quantity "usage_quantity",

icp.number_of_cavity "cavity",

to_char(icp.expiry_date,''DD-MON-YYYY'') "expiry_date"

from

homes_inv_heat_pour_items hpi,

homes_inv_itm_core_patterns icp,

homes_inv_items ii

where

hpi.item_id = icp.item_id

and icp.core_pattern_type = ''PATTERN''

and icp.end_date is null

and icp.item_id = ii.item_id';

p_report_output2 := select_clause;

p_report_output :=

p_report_output1

|| p_report_output2

|| lv_v_where_clase

|| lv_v_group_name

|| lv_v_order_name

|| ') TA ';

p_static := 'N';

p_app_id := 127;

p_page_id := 114;

p_user_id := -1;

v_filename :=

'Critical Patterns_' || TO_CHAR (SYSDATE, 'DDMMYYYY _HH24MISS');

doy_pk_apex_excel_download.ps_ds_get_excel_new (p_session_id,

p_app_id,

p_page_id,

p_static,

p_user_id,

p_report_output,

p_report_header,

p_report_header2,

p_report_header3,

NULL,

v_filename

);

END;

Step 3:

Create one button to submit the above page process.

Palani Kumar K

Monday, January 2, 2017

Excel Download Package in Apex

No comments:

Post a Comment