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;