Monday 2 January 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)
      || CHR (9)
      || CHR (9)
      || 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,
                                                   NULL,
                                                   NULL,
                                                   v_filename
                                                  );
END;

Step 3:
Create one button to submit the above page process.  



By
Palani Kumar K

No comments:

Post a Comment