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

or

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.

By

Palani Kumar K