CREATE OR REPLACE FUNCTION public.glinetotal(in_qty real, in_sellprice numeric, in_price_factor numeric, in_discount real, in_exchangerate numeric, in_taxincluded boolean, in_taxrate numeric, OUT linetotal_net numeric, OUT linetotal_gross numeric, OUT linetotal_tax_amount numeric, OUT linetotal_discount_amount numeric, OUT sellprice_net numeric)
 RETURNS record
 LANGUAGE plpgsql
AS $function$
DECLARE
  num_dec   int;
  discount  numeric(15,5);
  sellprice numeric(15,5);
  linetotal numeric(15,5);
  tax       numeric(15,5);
  qty       numeric(15,5);
  linetotal_no_discount numeric(15,5);
BEGIN
  -- some assumptions: qty = base_qty

  -- linetotals must always be rounded to two digits
  num_dec := 2;

  sellprice := in_sellprice::numeric;
  qty       := in_qty::numeric;

  linetotal_no_discount := ROUND(in_sellprice                            * qty / in_price_factor, num_dec)::numeric(15,5);
  linetotal             := ROUND(in_sellprice * (1-in_discount)::numeric * qty / in_price_factor, num_dec)::numeric(15,5);
  -- RAISE NOTICE 'linetotal_no_discount = %, linetotal = %', linetotal_no_discount, linetotal;

  linetotal_discount_amount := linetotal_no_discount - linetotal;

  IF in_taxincluded THEN
    -- sellprice is gross
    linetotal_gross := linetotal;
    linetotal_net   := ROUND( linetotal_gross / (1+in_taxrate) , num_dec);
  ELSE
    -- sellprice is net
    linetotal_net   := linetotal;
    tax             := ROUND(linetotal_net * in_taxrate,2);
    linetotal_gross := linetotal_net + tax;
  END IF;

  linetotal_tax_amount := linetotal_gross - linetotal_net;
  sellprice_net := (linetotal_net / qty)::numeric(15,5);

  RETURN;
END;
$function$;

select * from glinetotal(1000, 4.5::numeric, 1, 0.05, 1, 'f', 0.19);
--           linetotal_net             | 4275.00000
--           linetotal_gross           | 5087.25000
--           linetotal_tax_amount      | 812.25000
--           linetotal_discount_amount | 225.00000
--           sellprice_net             | 4.27500

select * from glinetotal(1000, (4.5*1.19)::numeric, 1, 0.05, 1, 't', 0.19);
--           linetotal_net             | 4275.00
--           linetotal_gross           | 5087.25000
--           linetotal_tax_amount      | 812.25000
--           linetotal_discount_amount | 267.75000
--           sellprice_net             | 4.27500

