-------------------------------------------------------------------------
-- encoding: UTF-8
-- MP-Biz 2.2: funções da Base de Dados
-------------------------------------------------------------------------

-------------------------------------------------------------------------
-- prod_cot
-------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION mpbiz_get_prod_supplier(integer) RETURNS integer
    AS '
  select ent_id
  from prod_cot
    where 
			prod_id = $1;
' LANGUAGE sql;

-------------------------------------------------------------------------
-- tab_tipo_prod
-------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION mpbiz_get_tipo_prod_compra_p_venda(integer) RETURNS boolean
    AS '
  select compra_p_venda from tab_tipo_prod 
    where 
      tipo_prod_id =
        (select tipo_prod_id from prod
          where prod_id = $1 );
'
    LANGUAGE sql;

-------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION mpbiz_get_tipo_prod_af_stocks(integer) RETURNS boolean
    AS '
  select controla_stocks from tab_tipo_prod 
    where 
      tipo_prod_id =
        (select tipo_prod_id from prod
          where prod_id = $1 );
' LANGUAGE sql;


-------------------------------------------------------------------------
-- tot_prod
-------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION mpbiz_get_tot_prod(integer, integer, date) 
RETURNS double precision
    AS '
  select sum( total ) from tot_prod
    where 
			prod_id = $1 and 
			arm_id = $2 and 
      ((ano = date_part( ''year'', $3 ) and mes < date_part(''month'', $3 )) 
				or
        ano < date_part( ''year'', $3 ));
' LANGUAGE sql;

-------------------------------------------------------------------------
-- PRODUTOS
-------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION mpbiz_get_prod_nome_by_id(integer) RETURNS character
    AS '
  select nome from prod 
    where 
      prod_id = $1;
' LANGUAGE sql;

-------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION mpbiz_get_tot_prod_by_date(integer, date) 
RETURNS double precision
    AS '
  select sum( total ) from tot_prod
    where 
			prod_id = $1 and 
      ((ano = date_part( ''year'', $2 ) and
        mes < date_part(''month'', $2 )) or
      ano < date_part( ''year'', $2 ));
					 
' LANGUAGE sql;

-------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION mpbiz_get_tot_prod_by_date_ym(integer, integer, integer) 
RETURNS double precision
    AS '
  select sum( total ) from tot_prod
    where 
			prod_id = $1 and 
      ((ano = $2 and mes <= $3) or ano < $2);
					 
' LANGUAGE sql;

-------------------------------------------------------------------------
-- ENTIDADES
-------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION mpbiz_get_ent_nome_by_id(integer) RETURNS character
    AS '
  select nome from ent 
    where 
      ent_id = $1;
' LANGUAGE sql;

-------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION mpbiz_get_tot_ent_debito(integer, integer, integer, date) 
RETURNS double precision
    AS '
  select sum( debito ) from tot_ent
    where 
			tipo_ent_id = $1 and ent_id = $2 and moeda_id = $3 and
      ((ano = date_part( ''year'', $4 ) and
        mes < date_part(''month'', $4 )) or
      ano < date_part( ''year'', $4 ));
					 
' LANGUAGE sql;

-------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION mpbiz_get_cot_moeda(integer, integer, integer) 
RETURNS double precision
    AS '
	select moeda_cot from doc
		where
			tipo_ent_id = $1 and
			tipo_doc_id = $2 and 
			doc_id = $3;
' LANGUAGE sql;

-------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION mpbiz_get_tax_value(integer, integer, integer, integer) 
RETURNS double precision
    AS '
	select imposto from doc_taxa
		where
			tipo_ent_id = $1 and
			tipo_doc_id = $2 and 
			doc_id = $3 and 
			taxa_id = $4;
' LANGUAGE sql;

-------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION mpbiz_get_tot_ent_credito(integer, integer, integer, date ) 
RETURNS double precision
    AS '
  select sum( credito ) from tot_ent
    where 
			tipo_ent_id = $1 and ent_id = $2 and 
			moeda_id = $3 and
      ((ano = date_part( ''year'', $4 ) and
        mes < date_part(''month'', $4 )) or
      	ano < date_part( ''year'', $4 ));
' LANGUAGE sql;

-------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION mpbiz_create_tot_ent_tuple( integer, integer, integer, date ) 
RETURNS void
AS '
	DECLARE
		v_tipo_ent_id ALIAS FOR $1;
		v_ent_id ALIAS FOR $2;
		v_moeda_id ALIAS FOR $3;
		v_data_doc ALIAS FOR $4;
		v_ano integer;
		v_mes integer;
  BEGIN
		v_ano := extract( year from v_data_doc );
		v_mes := extract( month from v_data_doc );
    if( NOT mpbiz_tot_ent_exists( v_tipo_ent_id, v_ent_id ) )
    then
      insert into 
				tot_ent ( tipo_ent_id, ent_id, moeda_id, ano, mes, credito, debito, vol_neg )
      	values ( v_tipo_ent_id, v_ent_id, v_moeda_id, v_ano, v_mes, 0.0, 0.0, 0.0 );
    end if;
		return;
	END;
' LANGUAGE plpgsql;

-------------------------------------------------------------------------
-- DOCUMENTOS
-------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION mpbiz_get_doc_total(integer, integer, integer, integer, date) RETURNS double precision
    AS '
  select sum( total_com_taxas ) from doc
    where 
			tipo_ent_id = $1 and tipo_doc_id = $2 and 
			ent_id = $3 and moeda_id = $4 and
			data_doc >= date_trunc(''month'', $5 ) and
			data_doc < $5;
' LANGUAGE sql;

-------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION mpbiz_get_doc_seller_id(integer,integer,integer) 
RETURNS integer
    AS '
  select seller_id
  from doc
    where 
			tipo_ent_id = $1 and
      tipo_doc_id = $2 and
      doc_id = $3;

' LANGUAGE sql;

-------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION mpbiz_get_ent_credito(integer, integer, integer, date) 
RETURNS double precision
    AS '
  select coalesce( mpbiz_get_tot_ent_credito( $1, $2, $3, $4 ), 0 ) +
				 coalesce( mpbiz_get_doc_total( $1, 101, $2, $3, $4 ), 0 );
' LANGUAGE sql;

-------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION mpbiz_get_ent_debito(integer, integer, integer, date) 
RETURNS double precision
    AS '
  select coalesce( mpbiz_get_tot_ent_debito( $1, $2, $3, $4 ), 0 ) +
				 coalesce( mpbiz_get_doc_total( $1, 2, $2, $3, $4 ), 0 );
' LANGUAGE sql;


-------------------------------------------------------------------------
-- VÁRIOS
-------------------------------------------------------------------------

-------------------------------------------------------------------------
-- tab_tipo_doc
-------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION mpbiz_get_mov_prod_factor(integer, integer)
RETURNS integer
    AS '
	DECLARE
		v_tipo_ent_id ALIAS FOR $1;
		v_tipo_doc_id ALIAS FOR $2;
	BEGIN
		-- cliente
		if( v_tipo_ent_id = 1 ) THEN
		
			-- factura, v.d., n.deb., g.rem.
			if( v_tipo_doc_id in ( 2, 3, 12, 21 ) ) THEN
				return -1;
				
			-- devol., n.cred.
			elsif( v_tipo_doc_id in ( 5, 11 ) ) THEN
				return 1;
				
			else
				return 0;
			end if;
			
		-- fornecedor
		elsif( v_tipo_ent_id = 2 ) THEN
		
			-- factura, c.d., n.deb., g.rem., g.cons. 
			if( v_tipo_doc_id in ( 2, 3, 12, 21, 25 ) ) THEN
				return 1;
				
			-- devol., n.cred., dev.cons.
			elsif( v_tipo_doc_id in ( 5, 11, 26 ) ) THEN
				return -1;
				
			else
				return 0;
			end if;
		elsif( v_tipo_ent_id = 99 ) THEN
			if( v_tipo_doc_id in ( 92, 99, 1001 ) ) THEN
				return 1;
			elsif( v_tipo_doc_id in ( 98 ) ) THEN
				return -1;
			else
				return 0;
			end if;
		end if;
		return 0;
	END;
' LANGUAGE plpgsql;


-------------------------------------------------------------------------
-- MISC
-------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION mpbiz_get_prod_mov(integer, integer, integer, timestamp with time zone, date) 
RETURNS double precision
    AS '
  select sum( quant ) from doc_lin, doc
    where 
			doc.tipo_doc_id = doc_lin.tipo_doc_id and
			doc.tipo_ent_id = doc_lin.tipo_ent_id and
			doc.doc_id = doc_lin.doc_id and	
			doc_lin.tipo_ent_id = $1 and 
      doc_lin.tipo_doc_id = $2 and 
			item_id = $3 and 
			doc.data_doc >= $4 and 
			doc.data_doc < $5;
' LANGUAGE sql;


-------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION mpbiz_get_prod_saldo(integer, date) 
RETURNS double precision
    AS '
  select 
		coalesce( mpbiz_get_tot_prod( $1, 1, $2 ), 0 ) +
		coalesce( mpbiz_get_prod_mov( 2, 2, $1, date_trunc( ''month'', $2 ), $2 ), 0 ) -
		coalesce( mpbiz_get_prod_mov( 1, 2, $1, date_trunc( ''month'', $2 ), $2 ), 0 ) +
		coalesce( mpbiz_get_prod_mov( 2, 3, $1, date_trunc( ''month'', $2 ), $2 ), 0 ) -
		coalesce( mpbiz_get_prod_mov( 1, 3, $1, date_trunc( ''month'', $2 ), $2 ), 0 );
' LANGUAGE sql;


-------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION mpbiz_get_prod_sales(integer,date,date) 
RETURNS DOUBLE PRECISION
    AS '
  select sum( quant )
  from doc_lin 
    join doc using( tipo_ent_id, tipo_doc_id, doc_id ) 
  where 
    item_id = $1 and 
    tipo_ent_id = 1 and 
    tipo_doc_id in ( 2, 3 ) and 
    data_doc between $2 and $3;

' LANGUAGE sql;

-------------------------------------------------------------------------
-- functions for doc lines
-------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION mpbiz_check_doc_org( integer, integer, integer ) 
RETURNS void
AS '
	DECLARE
		v_tipo_ent_id ALIAS FOR $1;
		v_tipo_doc_id ALIAS FOR $2;
		v_doc_id ALIAS FOR $3;
		v_final_state boolean;
		v_doc_lin record;
		
  BEGIN
		v_final_state := true;
		for v_doc_lin in 
			select * 
				from doc 
				where 
					tipo_ent_id = v_tipo_ent_id AND
					tipo_doc_id = v_tipo_doc_id AND
					tipo_doc_id = v_tipo_doc_id 
			loop
				if v_doc_lin.estado_final != true then
					v_final_state := false;
					exit;
				end if;
			end loop;
		if v_final_state then
			update doc set estado_final = true
				where 
					tipo_ent_id = v_tipo_ent_id AND
					tipo_doc_id = v_tipo_doc_id AND
					tipo_doc_id = v_tipo_doc_id 
		end if;
		return;
	END;
' LANGUAGE plpgsql;

-------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION mpbiz_prod_arm_exists( integer, integer ) 
RETURNS boolean
AS '
	DECLARE
		v_arm_id ALIAS FOR $1;
		v_prod_id ALIAS FOR $2;
		
  BEGIN
    return
      v_arm_id in ( 
        select arm_id from prod_arm where arm_id = v_arm_id and prod_id = v_prod_id );
	END;
' LANGUAGE plpgsql;

-------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION mpbiz_create_prod_arm_tuple( integer, integer  ) 
RETURNS void
AS '
	DECLARE
		v_arm_id ALIAS FOR $1;
		v_prod_id ALIAS FOR $2;
  BEGIN
    if( NOT mpbiz_prod_arm_exists( v_arm_id, v_prod_id ) )
    then
      insert into 
				prod_arm ( arm_id, prod_id, existencia, qt_enc_cli, qt_enc_forn )
      	values ( v_arm_id, v_prod_id, 0.0, 0.0, 0.0 );
    end if;
		return;
	END;
' LANGUAGE plpgsql;

-------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION mpbiz_prod_cot_exists( integer, integer ) 
RETURNS boolean
AS '
	DECLARE
		v_prod_id ALIAS FOR $1;
		v_ent_id ALIAS FOR $2;
		
  BEGIN
    return
      v_ent_id in ( 
        select ent_id from prod_cot where prod_id = v_prod_id and ent_id = v_ent_id );
	END;
' LANGUAGE plpgsql;

-------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION mpbiz_create_prod_cot_tuple( integer, integer, date ) 
RETURNS void
AS '
	DECLARE
		v_prod_id ALIAS FOR $1;
		v_ent_id ALIAS FOR $2;
		v_data ALIAS FOR $3;
  BEGIN
    if( NOT mpbiz_prod_cot_exists( v_prod_id, v_ent_id ) )
    then
      insert into 
				prod_cot ( prod_id, ent_id, cotacao, moeda_id, ref_forn, data_cot, custos_adicionais, obs )
      	values ( v_prod_id, v_ent_id, 0.0, 0, '''', v_data, 0.0, '''' );
    end if;
		return;
	END;
' LANGUAGE plpgsql;

-------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION mpbiz_update_prod_arm_qt_enc_cli( integer, integer, double precision ) 
RETURNS void
AS '
	DECLARE
		v_arm_id ALIAS FOR $1;
		v_prod_id ALIAS FOR $2;
		v_qt_enc_cli ALIAS FOR $3;
  BEGIN
    update prod_arm 
			set qt_enc_cli = qt_enc_cli + v_qt_enc_cli
			where arm_id = v_arm_id AND prod_id = v_prod_id;
		return;
	END;
' LANGUAGE plpgsql;

-------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION mpbiz_update_prod_arm_qt_enc_forn( integer, integer, double precision ) 
RETURNS void
AS '
	DECLARE
		v_arm_id ALIAS FOR $1;
		v_prod_id ALIAS FOR $2;
		v_qt_enc_forn ALIAS FOR $3;
  BEGIN
    update prod_arm 
			set qt_enc_forn = qt_enc_forn + v_qt_enc_forn
			where arm_id = v_arm_id AND prod_id = v_prod_id;
		return;
	END;
' LANGUAGE plpgsql;

-------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION mpbiz_update_prod_arm_exist( integer, integer, double precision ) 
RETURNS void
AS '
	DECLARE
		v_arm_id ALIAS FOR $1;
		v_prod_id ALIAS FOR $2;
		v_exist ALIAS FOR $3;
  BEGIN
    update prod_arm 
			set existencia = existencia + v_exist
			where arm_id = v_arm_id AND prod_id = v_prod_id;
		return;
	END;
' LANGUAGE plpgsql;

-------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION mpbiz_update_prod_quant( integer, double precision ) 
RETURNS void
AS '
	DECLARE
		v_prod_id ALIAS FOR $1;
		v_quant ALIAS FOR $2;
  BEGIN
    update prod 
			set quant = quant + v_quant
			where prod_id = v_prod_id;
		return;
	END;
' LANGUAGE plpgsql;

-------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION mpbiz_update_prod_p_custo( integer, double precision, date ) 
RETURNS void
AS '
	DECLARE
		v_prod_id ALIAS FOR $1;
		v_pcusto ALIAS FOR $2;
		v_date ALIAS FOR $3;
  BEGIN
    update prod 
			set ult_p_custo = v_pcusto, data_ult_p_custo = v_date
			where prod_id = v_prod_id;
		return;
	END;
' LANGUAGE plpgsql;

-------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION mpbiz_update_prod_cot( integer, double precision, date, integer, integer ) 
RETURNS void
AS '
	DECLARE
		v_prod_id ALIAS FOR $1;
		v_pcusto ALIAS FOR $2;
		v_date ALIAS FOR $3;
		v_ent_id ALIAS FOR $4;
		v_moeda_id ALIAS FOR $5;
  BEGIN
    perform mpbiz_create_prod_cot_tuple( v_prod_id, v_ent_id, v_date );
		
    update prod_cot
			set 
				cotacao = v_pcusto, 
				data_cot = v_date, 
				moeda_id = v_moeda_id, 
				ent_id = v_ent_id
			where 
				prod_id = v_prod_id AND 
				ent_id = v_ent_id;
			
		return;
	END;
' LANGUAGE plpgsql;

-------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION mpbiz_update_prod_p_venda( integer, double precision ) 
RETURNS void
AS '
	DECLARE
		v_prod_id ALIAS FOR $1;
		v_pvenda ALIAS FOR $2;
  BEGIN
    update prod 
			set pvenda = v_pvenda
			where prod_id = v_prod_id;
		return;
	END;
' LANGUAGE plpgsql;

-------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION mpbiz_update_tot_prod( integer, integer, double precision, date ) 
RETURNS void
AS '
	DECLARE
		v_arm_id ALIAS FOR $1;
		v_prod_id ALIAS FOR $2;
		v_qt ALIAS FOR $3;
		v_date ALIAS FOR $4;
		v_ano integer;
		v_mes integer;
  BEGIN
		v_ano := extract( year from v_date );
		v_mes := extract( month from v_date );
    update tot_prod 
			set total = total + v_qt
			where 
				arm_id = v_arm_id AND 
				prod_id = v_prod_id AND 
				ano = v_ano AND
				mes = v_mes;
		return;
	END;
' LANGUAGE plpgsql;

-------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION mpbiz_update_doc_lin_qt_org( integer, integer, integer, integer, double precision ) 
RETURNS void
AS '
	DECLARE
		v_tipo_ent_id ALIAS FOR $1;
		v_tipo_doc_id ALIAS FOR $2;
		v_doc_id ALIAS FOR $3;
		v_doc_lin_id ALIAS FOR $4;
		v_upd_qt ALIAS FOR $5;
		
  BEGIN
	
		update doc_lin 
			set aux_double_1 = aux_double_1 + v_upd_qt
		where 
			doc_lin.tipo_ent_id = v_tipo_ent_id AND
			doc_lin.tipo_doc_id = v_tipo_doc_id AND
			doc_lin.doc_id = v_doc_id AND
			doc_lin.doc_lin_id = v_doc_lin_id;
		
		update doc_lin 
			set estado_final = true
			where 
				doc_lin.tipo_ent_id = v_tipo_ent_id AND
				doc_lin.tipo_doc_id = v_tipo_doc_id AND
				doc_lin.doc_id = v_doc_id AND
				doc_lin.doc_lin_id = v_doc_lin_id AND
				doc_lin.quant = doc_lin.aux_double_1;
		return;
	END;
' LANGUAGE plpgsql;

-------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION mpbiz_get_moeda_doc( integer, integer, integer )
RETURNS integer
AS '
	select moeda_id from doc 
		where 
			doc.tipo_ent_id = $1 AND
			doc.tipo_doc_id = $2 AND
			doc.doc_id = $3;
' LANGUAGE sql;

-------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION mpbiz_get_ent_doc( integer, integer, integer )
RETURNS integer
AS '
	select ent_id from doc 
		where 
			doc.tipo_ent_id = $1 AND
			doc.tipo_doc_id = $2 AND
			doc.doc_id = $3;
' LANGUAGE sql;

-------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION mpbiz_get_data_doc( integer, integer, integer )
RETURNS date
AS '
	select data_doc from doc 
		where 
			doc.tipo_ent_id = $1 AND
			doc.tipo_doc_id = $2 AND
			doc.doc_id = $3;
' LANGUAGE sql;

-------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION mpbiz_get_percent( double precision, double precision, integer )
RETURNS double precision
AS '
	DECLARE
		v_result double precision;
		v_desc double precision;
	BEGIN
		v_desc := $1 * $2 * 0.01;
		v_result := $1 - v_desc;
		v_result := round( cast( v_result as numeric), $3 );
		return v_result;
	END;
' LANGUAGE plpgsql;

-------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION mpbiz_get_doc_ent( integer, integer, integer )
RETURNS integer
AS '
	DECLARE
		v_tipo_ent_id ALIAS FOR $1;
		v_tipo_doc_id ALIAS FOR $2;
		v_doc_id ALIAS FOR $2;
		v_ent_id integer;
	BEGIN
		v_desc = trunc( v_value * v_percent / 100 ); 
		v_result = trunc( v_value - v_desc ); 
		v_result = trunc( v_result, 3 ); 
		return v_result;
	END;
' LANGUAGE plpgsql;

-------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION mpbiz_update_val_pag_org( integer, integer, integer, double precision ) 
RETURNS void
AS '
	DECLARE
		v_tipo_ent_id ALIAS FOR $1;
		v_tipo_doc_id ALIAS FOR $2;
		v_doc_id ALIAS FOR $3;
		v_upd_val_pago ALIAS FOR $4;
		
  BEGIN
	
		update doc 
			set aux_double_1 = aux_double_1 + v_upd_val_pago
		where 
			doc.tipo_ent_id = v_tipo_ent_id AND
			doc.tipo_doc_id = v_tipo_doc_id AND
			doc.doc_id = v_doc_id;
		
		update doc
			set estado_final = true
			where 
				doc.tipo_ent_id = v_tipo_ent_id AND
				doc.tipo_doc_id = v_tipo_doc_id AND
				doc.doc_id = v_doc_id AND
				doc.aux_double_1 = doc.total_com_taxas;
		return;
	END;
' LANGUAGE plpgsql;

-------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION tf_insert_doc_lin_tuple() 
RETURNS trigger
AS '
	DECLARE
		v_data_doc date;
		v_ent_id integer;
		v_moeda_id integer;
  BEGIN
		-- recover mode only: BEGIN
		perform mpbiz_create_prod_arm_tuple( new.aux_item_id, new.item_id  );
		-- recover mode only: END
		
		if (NOT mpbiz_get_tipo_prod_af_stocks( new.item_id )) AND new.tipo_doc_id != 101 then
			return new;
		end if;
		
		v_data_doc := mpbiz_get_data_doc( new.tipo_ent_id, new.tipo_doc_id, new.doc_id );
		v_ent_id := mpbiz_get_ent_doc( new.tipo_ent_id, new.tipo_doc_id, new.doc_id );
		v_moeda_id := mpbiz_get_moeda_doc( new.tipo_ent_id, new.tipo_doc_id, new.doc_id );
		
		-- clientes
    if new.tipo_ent_id = 1 then
		
			-- encomendas
			if new.tipo_doc_id = 1 then
			
				perform mpbiz_create_prod_arm_tuple( new.aux_item_id, new.item_id  );
				perform mpbiz_update_prod_arm_qt_enc_cli( new.aux_item_id, new.item_id, new.quant );
							
			-- facturas e v. dinheiro
			elsif new.tipo_doc_id in ( 2, 3 ) then
			
				-- origem != g.remessa, actualiza stocks
				if new.org_tipo_doc_id != 21 then
					perform mpbiz_update_prod_arm_exist( new.aux_item_id, new.item_id, -new.quant );
					perform mpbiz_update_prod_quant( new.item_id, -new.quant );
					perform mpbiz_update_tot_prod( new.aux_item_id, new.item_id, -new.quant, v_data_doc );
				end if;
				
				-- de encomendas
				if new.org_tipo_doc_id = 1 then
				
					perform mpbiz_update_prod_arm_qt_enc_cli( new.aux_item_id, new.item_id, -new.quant );
					perform mpbiz_update_doc_lin_qt_org( new.tipo_ent_id, new.org_tipo_doc_id, new.org_doc_id, new.org_doc_lin_id, new.quant );
					
				-- de g.remessa
				elsif new.org_tipo_doc_id = 1 then
				
					perform mpbiz_update_doc_lin_qt_org( new.tipo_ent_id, new.org_tipo_doc_id, new.org_doc_id, new.org_doc_lin_id, new.quant );
				
				end if;

			-- n.credito
			elsif new.tipo_doc_id = 11 then
			
				perform mpbiz_update_prod_arm_exist( new.aux_item_id, new.item_id, new.quant );
				perform mpbiz_update_prod_quant( new.item_id, new.quant );
				perform mpbiz_update_tot_prod( new.aux_item_id, new.item_id, new.quant, v_data_doc );
				
			-- n.debito
			elsif new.tipo_doc_id = 12 then
			
				perform mpbiz_update_prod_arm_exist( new.aux_item_id, new.item_id, -new.quant );
				perform mpbiz_update_prod_quant( new.item_id, -new.quant );
				perform mpbiz_update_tot_prod( new.aux_item_id, new.item_id, -new.quant, v_data_doc );
				
			-- g.remessa
			elsif new.tipo_doc_id = 21 then
			
				perform mpbiz_update_prod_arm_exist( new.aux_item_id, new.item_id, -new.quant );
				perform mpbiz_update_prod_quant( new.item_id, -new.quant );
				perform mpbiz_update_tot_prod( new.aux_item_id, new.item_id, -new.quant, v_data_doc ); 
				
				-- de encomendas
				if new.org_tipo_doc_id = 1 then
				
					perform mpbiz_update_prod_arm_qt_enc_cli( new.aux_item_id, new.item_id, -new.quant );
					perform mpbiz_update_doc_lin_qt_org( new.tipo_ent_id, new.org_tipo_doc_id, new.org_doc_id, new.org_doc_lin_id, new.quant );
				end if;
			
			-- recibo
			elsif new.tipo_doc_id = 101 then
			
					perform mpbiz_update_val_pag_org( new.tipo_ent_id, new.org_tipo_doc_id, new.org_doc_id, new.total_com_taxa );
					
			end if;
				
		-- fornecedor
    elsif new.tipo_ent_id = 2 then
		
			-- encomendas
			if new.tipo_doc_id = 1 then
			
				perform mpbiz_create_prod_arm_tuple( new.aux_item_id, new.item_id  );
				perform mpbiz_update_prod_arm_qt_enc_forn( new.aux_item_id, new.item_id, new.quant );
							
			-- facturas e c. dinheiro
			elsif new.tipo_doc_id in ( 2, 3 ) then

				perform mpbiz_create_prod_arm_tuple( new.aux_item_id, new.item_id  );
				
				-- origem != g.remessa, actualiza stocks
				if new.org_tipo_doc_id != 21 then
					perform mpbiz_update_prod_arm_exist( new.aux_item_id, new.item_id, new.quant );
					perform mpbiz_update_prod_quant( new.item_id, new.quant );
					perform mpbiz_update_tot_prod( new.aux_item_id, new.item_id, new.quant, v_data_doc );
				end if;
				
				perform mpbiz_update_prod_p_custo( 
					new.item_id, mpbiz_get_percent( new.preco_uni, new.desconto1, 3 ), v_data_doc );
					
				perform mpbiz_update_prod_cot( 
					new.item_id, mpbiz_get_percent( new.preco_uni, new.desconto1, 3 ), v_data_doc, v_ent_id, v_moeda_id );
				
				-- de encomendas
				if new.org_tipo_doc_id = 1 then
				
					perform mpbiz_update_prod_arm_qt_enc_forn( new.aux_item_id, new.item_id, -new.quant );
					perform mpbiz_update_doc_lin_qt_org( new.tipo_ent_id, new.org_tipo_doc_id, new.org_doc_id, new.org_doc_lin_id, new.quant );
					
				-- de g.remessa
				elsif new.org_tipo_doc_id = 21 then
				
					perform mpbiz_update_doc_lin_qt_org( new.tipo_ent_id, new.org_tipo_doc_id, new.org_doc_id, new.org_doc_lin_id, new.quant );
				
				end if;
				
			-- n.credito
			elsif new.tipo_doc_id = 11 then
			
				perform mpbiz_update_prod_arm_exist( new.aux_item_id, new.item_id, -new.quant );
				perform mpbiz_update_prod_quant( new.item_id, -new.quant );
				perform mpbiz_update_tot_prod( new.aux_item_id, new.item_id, -new.quant, v_data_doc );
				
			-- n.debito
			elsif new.tipo_doc_id = 12 then
			
				perform mpbiz_update_prod_arm_exist( new.aux_item_id, new.item_id, new.quant );
				perform mpbiz_update_prod_quant( new.item_id, new.quant );
				perform mpbiz_update_tot_prod( new.aux_item_id, new.item_id, new.quant, v_data_doc );
				
			-- g.remessa
			elsif new.tipo_doc_id = 21 then
			
				perform mpbiz_create_prod_arm_tuple( new.aux_item_id, new.item_id  );
				perform mpbiz_update_prod_arm_exist( new.aux_item_id, new.item_id, new.quant );
				perform mpbiz_update_prod_quant( new.item_id, new.quant );
				perform mpbiz_update_tot_prod( new.aux_item_id, new.item_id, new.quant, v_data_doc );
				
				-- de encomendas
				if new.org_tipo_doc_id = 1 then
				
					perform mpbiz_update_prod_arm_qt_enc_forn( new.aux_item_id, new.item_id, -new.quant );
					perform mpbiz_update_doc_lin_qt_org( new.tipo_ent_id, new.org_tipo_doc_id, new.org_doc_id, new.org_doc_lin_id, new.quant );
				end if;
				
			-- g.consignação
			elsif new.tipo_doc_id = 25 then
			
				perform mpbiz_create_prod_arm_tuple( new.aux_item_id, new.item_id  );
				perform mpbiz_update_prod_arm_exist( new.aux_item_id, new.item_id, new.quant );
				perform mpbiz_update_prod_quant( new.item_id, new.quant );
				perform mpbiz_update_tot_prod( new.aux_item_id, new.item_id, new.quant, v_data_doc );
				
				perform mpbiz_update_prod_p_custo( 
					new.item_id, mpbiz_get_percent( new.preco_uni, new.desconto1, 3 ), v_data_doc );
					
				perform mpbiz_update_prod_cot( 
					new.item_id, mpbiz_get_percent( new.preco_uni, new.desconto1, 3 ), v_data_doc, v_ent_id, v_moeda_id );
				
				if mpbiz_get_tipo_prod_compra_p_venda( new.item_id ) then
				
					perform mpbiz_update_prod_p_venda( new.item_id, new.preco_uni );
						
				end if;
				
			-- recibo
			elsif new.tipo_doc_id = 101 then
			
					perform mpbiz_update_val_pag_org( new.tipo_ent_id, new.org_tipo_doc_id, new.org_doc_id, new.total_com_taxa );
					
			end if;
				
		-- interno
    elsif new.tipo_ent_id = 99 then
		
			-- saídas de armazém
			if new.tipo_doc_id = 91 then
			
				perform mpbiz_update_prod_arm_exist( new.aux_item_id, new.item_id, -new.quant );
				perform mpbiz_update_prod_quant( new.item_id, -new.quant );
				perform mpbiz_update_tot_prod( new.aux_item_id, new.item_id, -new.quant, v_data_doc );
				
			-- entradas em armazém
			elsif new.tipo_doc_id in ( 92, 99, 1001 ) then

				perform mpbiz_create_prod_arm_tuple( new.aux_item_id, new.item_id  );
				perform mpbiz_update_prod_arm_exist( new.aux_item_id, new.item_id, new.quant );
				perform mpbiz_update_prod_quant( new.item_id, new.quant );
				perform mpbiz_update_tot_prod( new.aux_item_id, new.item_id, new.quant, v_data_doc );
						
						
			end if;
			
    end if;
		return new;
	END;
' LANGUAGE plpgsql;

-------------------------------------------------------------------------
DROP TRIGGER trg_insert_doc_lin_tuple on doc_lin;

CREATE TRIGGER trg_insert_doc_lin_tuple 
BEFORE INSERT ON doc_lin 
FOR EACH ROW 
  EXECUTE PROCEDURE tf_insert_doc_lin_tuple();

-------------------------------------------------------------------------
-- functions for docs
-------------------------------------------------------------------------
-------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION mpbiz_ent_tipo_exists( integer, integer ) 
RETURNS boolean
AS '
	DECLARE
		v_tipo_ent_id ALIAS FOR $1;
		v_ent_id ALIAS FOR $2;
		
  BEGIN
    return
      v_ent_id in ( 
        select ent_id from ent_tipo 
				where tipo_ent_id = v_tipo_ent_id and ent_id = v_ent_id );
	END;
' LANGUAGE plpgsql;

-------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION mpbiz_create_ent_tipo_tuple( integer, integer  ) 
RETURNS void
AS '
	DECLARE
		v_tipo_ent_id ALIAS FOR $1;
		v_ent_id ALIAS FOR $2;
  BEGIN
    if( NOT mpbiz_ent_tipo_exists( v_tipo_ent_id, v_ent_id ) )
    then
      insert into 
				ent_tipo ( tipo_ent_id, ent_id )
      	values ( v_tipo_ent_id, v_ent_id );
    end if;
		return;
	END;
' LANGUAGE plpgsql;

-------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION mpbiz_tot_ent_exists( integer, integer ) 
RETURNS boolean
AS '
	DECLARE
		v_tipo_ent_id ALIAS FOR $1;
		v_ent_id ALIAS FOR $2;
		
  BEGIN
    return
      v_ent_id in ( 
        select ent_id from tot_ent 
				where tipo_ent_id = v_tipo_ent_id and ent_id = v_ent_id );
	END;
' LANGUAGE plpgsql;

-------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION mpbiz_ent_fin_exists( integer, integer ) 
RETURNS boolean
AS '
	DECLARE
		v_tipo_ent_id ALIAS FOR $1;
		v_ent_id ALIAS FOR $2;
		
  BEGIN
    return
      v_ent_id in ( 
        select ent_id from ent_fin
				where tipo_ent_id = v_tipo_ent_id and ent_id = v_ent_id );
	END;
' LANGUAGE plpgsql;

-------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION mpbiz_create_ent_fin_tuple( integer, integer, date, integer ) 
RETURNS void
AS '
	DECLARE
		v_tipo_ent_id ALIAS FOR $1;
		v_ent_id ALIAS FOR $2;
		v_data_doc ALIAS FOR $3;
		v_moeda_id ALIAS FOR $4;
  BEGIN
		perform mpbiz_create_ent_tipo_tuple( v_tipo_ent_id, v_ent_id  );
    if( NOT mpbiz_ent_fin_exists( v_tipo_ent_id, v_ent_id ) )
    then
      insert into 
				ent_fin ( 
					tipo_ent_id, ent_id, credito, debito, vol_neg, val_enc, prazo_pag, 
					plafond, moeda_id, isento_iva, tax_inc  )
      	values ( 
					v_tipo_ent_id, v_ent_id, 0.0, 0.0, 0.0, 0.0, 0, 
					 0.0, 0, false, false );
    end if;
    perform mpbiz_create_tot_ent_tuple( v_tipo_ent_id, v_ent_id, v_moeda_id, v_data_doc );
		return;
	END;
' LANGUAGE plpgsql;

-------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION mpbiz_update_ent_fin_deb( integer, integer, double precision ) 
RETURNS void
AS '
	DECLARE
		v_tipo_ent_id ALIAS FOR $1;
		v_ent_id ALIAS FOR $2;
		v_valor ALIAS FOR $2;
  BEGIN
    update ent_fin 
			set debito = debito + v_valor
			where 
				tipo_ent_id = v_tipo_ent_id AND
				ent_id = v_ent_id;
		return;
	END;
' LANGUAGE plpgsql;

-------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION mpbiz_update_ent_fin_cred( integer, integer, double precision ) 
RETURNS void
AS '
	DECLARE
		v_tipo_ent_id ALIAS FOR $1;
		v_ent_id ALIAS FOR $2;
		v_valor ALIAS FOR $2;
  BEGIN
    update ent_fin 
			set credito = credito + v_valor
			where 
				tipo_ent_id = v_tipo_ent_id AND
				ent_id = v_ent_id;
		return;
	END;
' LANGUAGE plpgsql;

-------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION mpbiz_update_ent_fin_vol_neg( integer, integer, double precision ) 
RETURNS void
AS '
	DECLARE
		v_tipo_ent_id ALIAS FOR $1;
		v_ent_id ALIAS FOR $2;
		v_valor ALIAS FOR $2;
  BEGIN
    update ent_fin 
			set vol_neg = vol_neg + v_valor
			where 
				tipo_ent_id = v_tipo_ent_id AND
				ent_id = v_ent_id;
		return;
	END;
' LANGUAGE plpgsql;

-------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION mpbiz_update_tot_ent_vol_neg( integer, integer, double precision, date ) 
RETURNS void
AS '
	DECLARE
		v_tipo_ent_id ALIAS FOR $1;
		v_ent_id ALIAS FOR $2;
		v_valor ALIAS FOR $3;
		v_date ALIAS FOR $4;
		v_ano integer;
		v_mes integer;
  BEGIN
		v_ano := extract( year from v_date );
		v_mes := extract( month from v_date );
    update tot_ent 
			set vol_neg = vol_neg + v_valor
			where 
				tipo_ent_id = v_tipo_ent_id AND
				ent_id = v_ent_id AND
				ano = v_ano AND 
				mes = v_mes;
		return;
	END;
' LANGUAGE plpgsql;

-------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION mpbiz_update_tot_ent_cred( integer, integer, double precision, date ) 
RETURNS void
AS '
	DECLARE
		v_tipo_ent_id ALIAS FOR $1;
		v_ent_id ALIAS FOR $2;
		v_valor ALIAS FOR $3;
		v_date ALIAS FOR $4;
		v_ano integer;
		v_mes integer;
  BEGIN
		v_ano := extract( year from v_date );
		v_mes := extract( month from v_date );
    update tot_ent 
			set credito = credito + v_valor
			where 
				tipo_ent_id = v_tipo_ent_id AND
				ent_id = v_ent_id AND
				ano = v_ano AND 
				mes = v_mes;
		return;
	END;
' LANGUAGE plpgsql;

-------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION mpbiz_update_tot_ent_deb( integer, integer, double precision, date ) 
RETURNS void
AS '
	DECLARE
		v_tipo_ent_id ALIAS FOR $1;
		v_ent_id ALIAS FOR $2;
		v_valor ALIAS FOR $3;
		v_date ALIAS FOR $4;
		v_ano integer;
		v_mes integer;
  BEGIN
		v_ano := extract( year from v_date );
		v_mes := extract( month from v_date );
    update tot_ent 
			set debito = debito + v_valor
			where 
				tipo_ent_id = v_tipo_ent_id AND
				ent_id = v_ent_id AND
				ano = v_ano AND 
				mes = v_mes;
		return;
	END;
' LANGUAGE plpgsql;

-------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION tf_insert_doc_tuple() 
RETURNS trigger
AS '
  BEGIN
		-- clientes
    if  new.tipo_ent_id = 1 then
			
			-- encomenda
			if new.tipo_doc_id = 1 then
			
				perform mpbiz_create_ent_fin_tuple( new.tipo_ent_id, new.ent_id, new.data_doc, new.moeda_id );
			
			-- facturas
			elsif new.tipo_doc_id = 2 then
			
				perform mpbiz_create_ent_fin_tuple( new.tipo_ent_id, new.ent_id, new.data_doc, new.moeda_id );
				perform mpbiz_update_ent_fin_cred( new.tipo_ent_id, new.ent_id, new.total_com_taxas );
				perform mpbiz_update_tot_ent_cred( new.tipo_ent_id, new.ent_id, new.total_com_taxas, new.data_doc );
				perform mpbiz_update_ent_fin_vol_neg( new.tipo_ent_id, new.ent_id, new.total_sem_taxas );
				perform mpbiz_update_tot_ent_vol_neg( new.tipo_ent_id, new.ent_id, new.total_sem_taxas, new.data_doc );
				
			-- v.dinheiro
			elsif new.tipo_doc_id = 3 then
			
				perform mpbiz_create_ent_fin_tuple( new.tipo_ent_id, new.ent_id, new.data_doc, new.moeda_id );
				perform mpbiz_update_ent_fin_vol_neg( new.tipo_ent_id, new.ent_id, new.total_sem_taxas );
				perform mpbiz_update_tot_ent_vol_neg( new.tipo_ent_id, new.ent_id, new.total_sem_taxas, new.data_doc );
				
			-- factura pro-forma
			elsif new.tipo_doc_id = 5 then
			
				perform mpbiz_create_ent_fin_tuple( new.tipo_ent_id, new.ent_id, new.data_doc, new.moeda_id );
				
			-- n.credito
			elsif new.tipo_doc_id = 11 then
			
				perform mpbiz_update_ent_fin_cred( new.tipo_ent_id, new.ent_id, new.total_com_taxas );
				perform mpbiz_update_tot_ent_cred( new.tipo_ent_id, new.ent_id, new.total_com_taxas, new.data_doc );
				perform mpbiz_update_ent_fin_vol_neg( new.tipo_ent_id, new.ent_id, -new.total_sem_taxas );
				perform mpbiz_update_tot_ent_vol_neg( new.tipo_ent_id, new.ent_id, -new.total_sem_taxas, new.data_doc );
				
			-- n.debito
			elsif new.tipo_doc_id = 12 then
			
				perform mpbiz_create_ent_fin_tuple( new.tipo_ent_id, new.ent_id, new.data_doc, new.moeda_id );
				perform mpbiz_update_ent_fin_deb( new.tipo_ent_id, new.ent_id, new.total_com_taxas );
				perform mpbiz_update_tot_ent_deb( new.tipo_ent_id, new.ent_id, new.total_com_taxas, new.data_doc );
				perform mpbiz_update_ent_fin_vol_neg( new.tipo_ent_id, new.ent_id, new.total_sem_taxas );
				perform mpbiz_update_tot_ent_vol_neg( new.tipo_ent_id, new.ent_id, new.total_sem_taxas, new.data_doc );
				
			-- recibo
			elsif new.tipo_doc_id = 101 then
			
				perform mpbiz_update_ent_fin_deb( new.tipo_ent_id, new.ent_id, -new.total_com_taxas );
				perform mpbiz_update_tot_ent_deb( new.tipo_ent_id, new.ent_id, -new.total_com_taxas, new.data_doc );
				
			end if;
			
		-- fornecedor
    elsif( new.tipo_ent_id = 2 ) then
			-- encomenda
			if( new.tipo_doc_id = 1 ) then
			
				perform mpbiz_create_ent_fin_tuple( new.tipo_ent_id, new.ent_id, new.data_doc, new.moeda_id );
			
			-- factura
			elsif( new.tipo_doc_id = 2 ) then
			
				perform mpbiz_create_ent_fin_tuple( new.tipo_ent_id, new.ent_id, new.data_doc, new.moeda_id );
				perform mpbiz_update_ent_fin_cred( new.tipo_ent_id, new.ent_id, new.total_com_taxas );
				perform mpbiz_update_tot_ent_cred( new.tipo_ent_id, new.ent_id, new.total_com_taxas, new.data_doc );
				perform mpbiz_update_ent_fin_vol_neg( new.tipo_ent_id, new.ent_id, new.total_sem_taxas );
				perform mpbiz_update_tot_ent_vol_neg( new.tipo_ent_id, new.ent_id, new.total_sem_taxas, new.data_doc );
				
			-- c.dinheiro
			elsif( new.tipo_doc_id = 3 ) then
			
				perform mpbiz_create_ent_fin_tuple( new.tipo_ent_id, new.ent_id, new.data_doc, new.moeda_id );
				perform mpbiz_update_ent_fin_vol_neg( new.tipo_ent_id, new.ent_id, new.total_sem_taxas );
				perform mpbiz_update_tot_ent_vol_neg( new.tipo_ent_id, new.ent_id, new.total_sem_taxas, new.data_doc );
				
			-- n.credito
			elsif( new.tipo_doc_id = 11 ) then
			
				perform mpbiz_update_ent_fin_cred( new.tipo_ent_id, new.ent_id, new.total_com_taxas );
				perform mpbiz_update_tot_ent_cred( new.tipo_ent_id, new.ent_id, new.total_com_taxas, new.data_doc );
				perform mpbiz_update_ent_fin_vol_neg( new.tipo_ent_id, new.ent_id, -new.total_sem_taxas );
				perform mpbiz_update_tot_ent_vol_neg( new.tipo_ent_id, new.ent_id, -new.total_sem_taxas, new.data_doc );
				
			-- n.debito
			elsif( new.tipo_doc_id = 12 ) then
			
				perform mpbiz_create_ent_fin_tuple( new.tipo_ent_id, new.ent_id, new.data_doc, new.moeda_id );
				perform mpbiz_update_ent_fin_deb( new.tipo_ent_id, new.ent_id, new.total_com_taxas );
				perform mpbiz_update_tot_ent_deb( new.tipo_ent_id, new.ent_id, new.total_com_taxas, new.data_doc );
				perform mpbiz_update_ent_fin_vol_neg( new.tipo_ent_id, new.ent_id, new.total_sem_taxas );
				perform mpbiz_update_tot_ent_vol_neg( new.tipo_ent_id, new.ent_id, new.total_sem_taxas, new.data_doc );
				
			-- recibo
			elsif( new.tipo_doc_id = 101 ) then
			
				perform mpbiz_update_ent_fin_deb( new.tipo_ent_id, new.ent_id, -new.total_com_taxas );
				perform mpbiz_update_tot_ent_deb( new.tipo_ent_id, new.ent_id, -new.total_com_taxas, new.data_doc );
				
			end if;

		-- interno
    elsif( new.tipo_ent_id = 99 ) then
		
			perform mpbiz_create_ent_fin_tuple( new.tipo_ent_id, new.ent_id, new.data_doc, new.moeda_id );
			
    end if;
		return new;
	END;
' LANGUAGE plpgsql;

-------------------------------------------------------------------------
DROP TRIGGER trg_insert_doc_tuple on doc;

CREATE TRIGGER trg_insert_doc_tuple 
BEFORE INSERT ON doc 
FOR EACH ROW 
  EXECUTE PROCEDURE tf_insert_doc_tuple();