REATE OR REPLACE FUNCTION romaneooficial(wfecha date, wdetic smallint, whatic smallint, wdecue integer, whacue integer, wderom integer, wharom integer)
  RETURNS SETOF record AS
$BODY$---
-- Fernando Aguada 07/04/2008
---

declare resul1 RECORD;
declare resul2 RECORD;
declare akil   numeric[10];
declare wtipc  integer;
declare wcuen  integer;
declare wdenoC character varying(40);
declare wcuitC character varying(13);
declare wonccaC character varying(8);
declare wtipcF integer;
declare wcuenF integer;
declare wdenoF character varying(40);
declare wcuitF character varying(13);
declare wonccaF character varying(8);
declare wmediaI integer;
declare wmediaF integer;
declare wcant integer;
declare wguia integer;
declare wkivi numeric(10,2);
declare wcanc integer;
declare wrema integer;
declare wtrop integer;
declare wguias integer;
declare wfefa date;
declare wclas integer;
declare wind  integer;
declare wf1   integer;
declare wsuma numeric(10,2);

begin 
  if EXISTS(select relname from pg_class where relname='auxiliar') then
     drop table auxiliar;
  end if;
 
  create temp table auxiliar (ctipc smallint, ccuen integer, cdenoc character varying(40), 
	ccuitC character varying(13), conccaC character varying(8), ctipcf smallint, 
	ccuitF character varying(13), conccaF character varying(8), ccuenf integer, cdenof character varying(40), 
	cmedi integer, cmedif integer, ccant integer, cguia integer, cfefa date, cclas smallint, 
        ckilo1 numeric(10,2), ckilo2 numeric(10,2), ckilo3 numeric(10,2), ckilo4 numeric(10,2),
        ckilo5 numeric(10,2), ckilo6 numeric(10,2), ckilo7 numeric(10,2), ckilo8 numeric(10,2),
        ckilo9 numeric(10,2), ckilo10 numeric(10,2));
  
  wind :=00;
  wtipc:=-01;
  wcuen:=-01;
  wdenoC:= '';
  wtipcF:=-01;
  wcuenF:=-01;
  wdenoF:='';
  wclas:=-01;

  FOR wf1 IN 1..10 LOOP
      akil[wf1]:=00; 
  END LOOP;

  ---
  -- Ciclo previo, obtengo el resultado bruto y acumulo.
  ---
  for resul1 in select rtotal.ctipc, rtotal.ccuen, c1.cdeno as DenoCuen,
                guiasb.ctipf, guiasb.cferi, c2.cdeno as DenoFeri,
                rlinea.cclas, rlinea.ckilo
                from romaneosbtotal rtotal
                left join romaneosblinea rlinea on rtotal.cnumtra = rlinea.cnumtra 
                left join cuentas c1 on rtotal.ctipc = c1.ctipc and rtotal.ccuen = c1.ccuen
                left join pedidosb on rtotal.ctrop = pedidosb.ctrop and rtotal.canio = pedidosb.canio
                left join guiasb on rtotal.ctrop = guiasb.ctrop and rtotal.canio = guiasb.canio
                left join cuentas c2 on guiasb.ctipf = c2.ctipc and guiasb.cferi = c2.ccuen
                where rtotal.cfefa = wfecha and
                rtotal.ctipc between wdetic and whatic and
                rtotal.ccuen between wdecue and whacue and
                rtotal.croma between wderom and wharom
                order by rtotal.ctipc, rtotal.ccuen, rlinea.cclas, rtotal.croma 
                loop

         ---
         -- Hace el corte de control
         ---  
         if resul1.ctipc<>wtipc or resul1.ccuen<>wcuen or resul1.cclas<>wclas or wind>=10 then
            wsuma:=00;
            FOR wf1 IN 1..10 LOOP
                wsuma=wsuma+akil[wf1];
            END LOOP;
            
            if wsuma<>00 then
               insert into auxiliar (ctipc,ccuen,cdenoc,ctipcf,ccuenf,cdenof,cclas,
	       ckilo1,ckilo2,ckilo3,ckilo4,ckilo5,ckilo6,ckilo7,ckilo8,ckilo9,ckilo10)
               values (wtipc,wcuen,wdenoC,wtipcF,wcuenf,wdenof,wclas,akil[01],akil[02],akil[03],
	       akil[04],akil[05],akil[06],akil[07],akil[08],akil[09],akil[10]);
            end if;   

            FOR wf1 IN 1..10 LOOP
                akil[wf1]:=00; 
            END LOOP;
            wind:=00; 
         end if; 
              
            
         if resul1.ctipc<>wtipc or resul1.ccuen<>wcuen then
            wtipc:=resul1.ctipc;
            wcuen:=resul1.ccuen;
	    wdenoc:=resul1.DenoCuen;
	    wtipcf:=resul1.ctipf;
	    wcuenf:=resul1.cferi;
	    wdenof:=resul1.DenoFeri;
         end if;

         if resul1.cclas<>wclas then
            wclas:=resul1.cclas;
         end if;

         wind:=wind+1;  
         akil[wind]:=resul1.ckilo;
        
  end loop;

  ---
  -- Vuelve a controlar
  ---
  wsuma:=00;
  FOR wf1 IN 1..10 LOOP
      wsuma=wsuma+akil[wf1];
  END LOOP;
            
  if wsuma<>00 then
     insert into auxiliar (ctipc,ccuen,cdenoc,ctipcf,ccuenf,cdenof,cclas,ckilo1,ckilo2,ckilo3,ckilo4,
     ckilo5,ckilo6,ckilo7,ckilo8,ckilo9,ckilo10) values (wtipc,wcuen,wdenoC,wtipcF,wcuenf,wdenof,wclas,
     akil[01],akil[02],akil[03],akil[04],akil[05],akil[06],akil[07],akil[08],
     akil[09],akil[10]);
  end if;   
  ---
 
 
  ---
  -- Obtiene resultado final
  ---
  for resul2 in select ctipc,ccuen,cdenoc,ctipcf,ccuenf,cdenof,cclas,ckilo1,ckilo2,ckilo3,ckilo4,ckilo5,ckilo6,ckilo7,
      ckilo8,ckilo9,ckilo10    
      from auxiliar loop

      return next resul2;
  end loop;  
  
  drop table auxiliar;
end
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION romaneooficial(wfecha date, wdetic smallint, whatic smallint, wdecue integer, whacue integer, wderom integer, wharom integer) OWNER TO postgres;