CREATE OR REPLACE PROCEDURE Test_C_3_v2 IS
-- Matrix report
   ---------------
  CURSOR c_data IS 
    SELECT code, 
             SUM(DECODE(TYPE,'AR',cost,0)) cost_ar,
			 SUM(DECODE(TYPE,'AT',cost,0)) cost_at,
			 SUM(DECODE(TYPE,'AK',cost,0)) cost_ak,
			 SUM(DECODE(TYPE,'AS',cost,0)) cost_as,
			 SUM(DECODE(TYPE,'AV',cost,0)) cost_av,
			 SUM(cost) summ
FROM TEST_DATA_3
GROUP BY code;

  l_blob BLOB;
  
  l_sum_ar NUMBER := 0;
  l_sum_at NUMBER := 0;
  l_sum_ak NUMBER := 0;
  l_sum_as NUMBER := 0;
  l_sum_av NUMBER := 0;
  l_sum_sum NUMBER := 0;
  
  PROCEDURE theader IS
  BEGIN
    Plpdf.SetPrintFont('Arial','B',12); -- set header font: Arial 10, bold     
	Plpdf.SetColor4Filling(200,220,255); -- set color for header background
	Plpdf.PrintCell(67,8,'CODE','1',0,'C',1); 
	Plpdf.PrintCell(35,8,'AR Type','1',0,'C',1);
	Plpdf.PrintCell(35,8,'AT Type','1',0,'C',1);
	Plpdf.PrintCell(35,8,'AK Type','1',0,'C',1);
	Plpdf.PrintCell(35,8,'AS Type','1',0,'C',1); 
	Plpdf.PrintCell(35,8,'AV Type','1',0,'C',1);
	Plpdf.PrintCell(35,8,'SUM','1',1,'C',1);
  END;
      
 BEGIN
    Plpdf.init('L'); -- initialize, without parameters means: page orientation: portrait, unit: mm, default page format: A4
	Plpdf.nopAlias;
	Plpdf.SetFooterProcName('x1footer'); -- set footer creator procedure
	
	
    Plpdf.NewPage; -- begin a new page, without parameters means: page orientation: default (portrait)
	
	Plpdf.SetPrintFont('Arial','BU',14); -- set titles font: Arial 14, bold, underline
    Plpdf.PrintCell(0,20,'Important report','0',0,'C'); -- print out the title
	
   Plpdf.LineBreak(5); -- line break with 5mm
	
	Plpdf.SetPrintFont('Arial','I',10); -- set subtitle font: Arial 10, italic
    Plpdf.PrintCell(0,20,'Created date:' || TO_CHAR(SYSDATE,'DD-MM-YYYY'),'0',0,'C'); -- print out the subtitle: creation date
	
    Plpdf.LineBreak(20); -- line break with 20mm
	
	theader;
	
   Plpdf.SetPrintFont('Arial','',12); -- set print font of table's cell: Arial 12, regular

	
   FOR f_data IN c_data LOOP -- rows of table
      IF Plpdf.CheckPageBreak(8) THEN
	     theader;
	 	Plpdf.SetPrintFont('Arial','',12); -- set print font of table's cell: Arial 12, regular
	  END IF;
	  	Plpdf.PrintCell(67,8,f_data.code,'1',0,'L',0); 
	    Plpdf.PrintCell(35,8,TO_CHAR(f_data.cost_ar,'999G999G999G999'),'1',0,'R',0);
	    Plpdf.PrintCell(35,8,TO_CHAR(f_data.cost_at,'999G999G999G999'),'1',0,'R',0);
	    Plpdf.PrintCell(35,8,TO_CHAR(f_data.cost_ak,'999G999G999G999'),'1',0,'R',0);
	    Plpdf.PrintCell(35,8,TO_CHAR(f_data.cost_as,'999G999G999G999'),'1',0,'R',0); 
	    Plpdf.PrintCell(35,8,TO_CHAR(f_data.cost_av,'999G999G999G999'),'1',0,'R',0);
		Plpdf.SetPrintFont('Arial','B',12);
	    Plpdf.PrintCell(35,8,TO_CHAR(f_data.summ,'999G999G999G999'),'1',1,'R',0);
		Plpdf.SetPrintFont('Arial','',12);
		
		l_sum_ar := l_sum_ar + NVL(f_data.cost_ar,0); 
        l_sum_at := l_sum_at + NVL(f_data.cost_at,0);
        l_sum_ak := l_sum_ak + NVL(f_data.cost_ak,0);
        l_sum_as := l_sum_as + NVL(f_data.cost_as,0);
        l_sum_av := l_sum_av + NVL(f_data.cost_av,0);
        l_sum_sum := l_sum_sum + NVL(f_data.summ,0);
		
	END LOOP;
	   		
  		Plpdf.SetPrintFont('Arial','B',12);
	  	Plpdf.PrintCell(67,8,'Summary','1',0,'L',0); 
	    Plpdf.PrintCell(35,8,TO_CHAR(l_sum_ar,'999G999G999G999'),'1',0,'R',0);
	    Plpdf.PrintCell(35,8,TO_CHAR(l_sum_at,'999G999G999G999'),'1',0,'R',0);
	    Plpdf.PrintCell(35,8,TO_CHAR(l_sum_ak,'999G999G999G999'),'1',0,'R',0);
	    Plpdf.PrintCell(35,8,TO_CHAR(l_sum_as,'999G999G999G999'),'1',0,'R',0); 
	    Plpdf.PrintCell(35,8,TO_CHAR(l_sum_av,'999G999G999G999'),'1',0,'R',0);
	    Plpdf.PrintCell(35,8,TO_CHAR(l_sum_sum,'999G999G999G999'),'1',1,'R',0);
	
    Plpdf.LineBreak;  -- line break with last cells height (8mm)
	
	Plpdf.SetPrintFont('Arial','I',10); -- set print font: Arial 10, italic
    Plpdf.PrintCell(0,10,'----- End of Report -----','0',0,'C');-- print out closer string of table
    
	
    Plpdf.SendDoc(l_blob); -- create content
	
	-- store
	INSERT INTO STORE_BLOB (blob_file, created_date) VALUES (l_blob, SYSDATE);
	COMMIT;
 END;
/