samplereport.proc

 


 CREATE OR REPLACE PROCEDURE samplereport
 (
     xdate VARCHAR2 := NULL,
     xSID VARCHAR2 := NULL,
     xclient varchar2 :=NULL
 ) AS
 
 CURSOR c1 IS
 select company_name_txt         Company_Name, 
        company_nbr              Company_Number,
        month_txt                Month, 
        strategy_set_cd          Strategy,
        attempt_nbr              Attempt, 
        margin_cd                Margin, 
        eff_start_dt             Start_Date, 
        eff_end_dt               End_Date, 
        level_cd                 Level, 
        type_cd                  Type,
        over_margin_amt          Over_Amt,
        over_margin_pct_nbr      Over_Pct, 
        under_margin_amt         Under_Amt, 
        under_margin_pct_nbr     Under_Pct, 
        sample_count_nbr         Sample_Count, 
        percent_of_strategy_nbr  Strategy
 from rpt.rpt_sample_statistics
 where month_txt  = xdate
 order by Company_Name, Strategy, Attempt;
 
 --- substr(create_ts, 1, 9)  Timestamp
 
 ls_sql varchar2 (200);
 
 BEGIN
 
    --- Before running this, enter sqlplus as user "rpt" and run:
    --- 
    ---   grant execute on sp_sample_stat_report to x_report
    --- 
 
      rpt.sp_sample_stat_report(xclient, xdate);
 --   ls_sql := 'rpt.sp_sample_stat_report(' || xclient || ',' ||  xdate ||  ');';
 --   execute immediate  ls_sql;
 
      htp.prn(' <html> ');
      htp.prn(' <title>Sample Initial Statistics</title> ');
      htp.prn(' </head> ');
      htp.prn(' <body> ');
      htp.prn(' <center> ');
      htp.prn(' <br><br> ');
      htp.prn(' <h2>Sample Initial Statistics</h2> ');
      htp.prn(' <h3> ');
      htp.prn( xSID );
      htp.prn(' - ');
      htp.prn( xdate );
      htp.prn(' </h3> ');
      htp.prn(' <br> <br> ');
 ---  htp.tableopen('border');
      htp.tableopen(cattributes=> 'cellpadding=3' );
     htp.tableRowOpen;
     htp.tableHeader('Company Name', calign=>'left', cattributes=>'VALIGN=bottom');
     htp.tableHeader('Company Number', calign=>'left', cattributes=>'VALIGN=bottom');
     htp.tableHeader('Month', calign=>'left', cattributes=>'VALIGN=bottom');
     htp.tableHeader('Initial Strategy', calign=>'left', cattributes=>'VALIGN=bottom');
     htp.tableHeader('Attempt', calign=>'right', cattributes=>'VALIGN=bottom');
     htp.tableHeader('Margin', calign=>'left', cattributes=>'VALIGN=bottom');
     htp.tableHeader('Start Date', calign=>'left', cattributes=>'VALIGN=bottom');
     htp.tableHeader('End Date', calign=>'left', cattributes=>'VALIGN=bottom');
     htp.tableHeader('Initial Level', calign=>'left', cattributes=>'VALIGN=bottom');
     htp.tableHeader('Initial Type', calign=>'left', cattributes=>'VALIGN=bottom');
     htp.tableHeader('Over Amt', calign=>'right', cattributes=>'VALIGN=bottom');
     htp.tableHeader('Over Pct', calign=>'right', cattributes=>'VALIGN=bottom');
     htp.tableHeader('Under Amt', calign=>'right', cattributes=>'VALIGN=bottom');
     htp.tableHeader('Under Pct', calign=>'right', cattributes=>'VALIGN=bottom');
     htp.tableHeader('Sample Count', calign=>'right', cattributes=>'VALIGN=bottom');
     htp.tableHeader('Strategy', calign=>'right', cattributes=>'VALIGN=bottom');
 --- htp.tableHeader('Timestamp', calign=>'left', cattributes=>'VALIGN=bottom');
     htp.tableRowClose;
 
        FOR I IN c1 LOOP
           htp.tablerowopen;
           htp.tabledata(I.Company_Name, calign=>'left', cattributes=> 'nowrap');
           htp.tabledata(I.Company_Number, calign=>'left', cattributes=> 'nowrap');
           htp.tabledata(I.Month, calign=>'left', cattributes=> 'nowrap');
           htp.tabledata(I.Strategy, calign=>'left', cattributes=> 'nowrap');
           htp.tabledata(I.Attempt, calign=>'right', cattributes=> 'nowrap');
           htp.tabledata(I.Margin, calign=>'left', cattributes=> 'nowrap');
           htp.tabledata(I.Start_Date, calign=>'left', cattributes=> 'nowrap');
           htp.tabledata(I.End_Date, calign=>'left', cattributes=> 'nowrap');
           htp.tabledata(I.Level, calign=>'left', cattributes=> 'nowrap');
           htp.tabledata(I.Type, calign=>'left', cattributes=> 'nowrap');
           htp.tabledata(I.Over_Amt, calign=>'right', cattributes=> 'nowrap');
           htp.tabledata(I.Over_Pct, calign=>'right', cattributes=> 'nowrap');
           htp.tabledata(I.Under_Amt, calign=>'right', cattributes=> 'nowrap');
           htp.tabledata(I.Under_Pct, calign=>'right', cattributes=> 'nowrap');
           htp.tabledata(I.Sample_Count, calign=>'right', cattributes=> 'nowrap');
           htp.tabledata(I.Strategy, calign=>'right', cattributes=> 'nowrap');
 ---       htp.tabledata(I.Timestamp, calign=>'left', cattributes=> 'nowrap');
           htp.tablerowclose;
        END LOOP;
         htp.tableclose;
         htp.prn('</center> ');
         htp.prn('</body> ');
         htp.prn('</html> ');
 END;
 /
 commit;
    
 exit;