git.roojs.org Git - Pman.Xtuple/blob - pgsql/x-fifo-invfifo-cost-before-in.sql

   1
   2 -- short cut to get the line before
   3
   4
   5 CREATE OR REPLACE FUNCTION invfifo_cost_before_in(NUMERIC, integer,integer)
   6     RETURNS  NUMERIC
   7 AS $BODY$
   8 DECLARE
   9
  10     i_invfifo_qty_before  ALIAS FOR $1;
  11     i_location_id ALIAS FOR $2;
  12     i_itemsite_id ALIAS FOR $3;
  13
  14
  15     v_cost_before  NUMERIC;
  16
  17
  18 BEGIN
  19
  20    SELECT
  21             COALESCE(invfifo_cost_after,0.0)
  22         INTO
  23             v_cost_before
  24         FROM
  25             invdetailview
  26         WHERE
  27             invfifo_qty_after = i_invfifo_qty_before
  28             AND
  29             invdetail_location_id = i_location_id
  30             AND
  31             invhist_itemsite_id = i_itemsite_id
  32             AND
  33             invdetail_qty > 0
  34             AND
  35             invfifo_void = 0
  36         ORDER BY
  37             invfifo_qty_after DESC
  38         LIMIT 1;
  39
  40     RETURN COALESCE(v_cost_before,0);
  41
  42 END;
  43 $BODY$
  44   LANGUAGE plpgsql VOLATILE
  45   COST 100;
  46
  47 ALTER FUNCTION  invfifo_cost_before_in(NUMERIC, integer,integer)
  48   OWNER TO admin;
  49