git.roojs.org Git - Pman.Xtuple/blob - pgsql/x-timewarp-trialbal-fill.sql

   1 CREATE SCHEMA timewarp;
   2
   3 CREATE OR REPLACE FUNCTION timewarp.trialbal_fill_all()
   4     RETURNS  boolean
   5 AS $BODY$
   6 DECLARE
   7
   8 BEGIN
   9
  10     PERFORM  timewarp.trialbal_fill(trialbal_id) FROM (
  11                     SELECT
  12                             trialbal_id
  13                         FROM
  14                             public.trialbal
  15
  16                     ) x;
  17     RETURN true;
  18 END;
  19 $BODY$
  20   LANGUAGE plpgsql VOLATILE
  21   COST 100;
  22
  23 ALTER FUNCTION   timewarp.trialbal_fill_all()
  24   OWNER TO admin;
  25
  26
  27 CREATE OR REPLACE FUNCTION timewarp.trialbal_fill(integer)
  28     RETURNS  boolean
  29
  30 AS $BODY$
  31 DECLARE
  32     i_trialbal_id  ALIAS FOR $1;
  33
  34     r_trialbal RECORD;
  35     r_trialbalview RECORD;
  36
  37     v_is_update BOOLEAN;
  38
  39 BEGIN
  40
  41     SELECT
  42         *
  43     INTO
  44         r_trialbal
  45     FROM
  46         timewarp.trialbal
  47     WHERE
  48         trialbal_id = i_trialbal_id;
  49
  50     v_is_update := false;
  51     -- already there..
  52     IF FOUND THEN
  53         v_is_update := true;
  54     END IF;
  55
  56     SELECT
  57         *
  58     INTO
  59         r_trialbalview
  60     FROM
  61         timewarp.trialbalview
  62     WHERE
  63         trialbal_id = i_trialbal_id;
  64
  65     IF v_is_update THEN
  66
  67         UPDATE
  68             timewarp.trialbal
  69         SET
  70             trialbal_period_id  =  r_trialbalview.trialbal_period_id,
  71             trialbal_accnt_id   =  r_trialbalview.trialbal_accnt_id,
  72             trialbal_beginning  =  r_trialbalview.trialbal_beginning,
  73             trialbal_ending     =  r_trialbalview.trialbal_ending,
  74             trialbal_credits    =  r_trialbalview.trialbal_credits,
  75             trialbal_debits     =  r_trialbalview.trialbal_debits,
  76             trialbal_dirty      =  r_trialbalview.trialbal_dirty,
  77             trialbal_yearend    =  r_trialbalview.trialbal_yearend
  78
  79         WHERE
  80             trialbal_id = i_trialbal_id;
  81
  82     ELSE
  83
  84         INSERT INTO  timewarp.trialbal (
  85             trialbal_id,trialbal_period_id,
  86             trialbal_accnt_id, trialbal_beginning,
  87             trialbal_ending, trialbal_credits,
  88             trialbal_debits, trialbal_dirty,
  89             trialbal_yearend
  90         ) VALUES  (
  91             r_trialbalview.trialbal_id, r_trialbalview.trialbal_period_id,
  92             r_trialbalview.trialbal_accnt_id, r_trialbalview.trialbal_beginning,
  93             r_trialbalview.trialbal_ending, r_trialbalview.trialbal_credits,
  94             r_trialbalview.trialbal_debits, r_trialbalview.trialbal_dirty,
  95             r_trialbalview.trialbal_yearend
  96         );
  97     END IF;
  98
  99     RETURN true;
 100 END;
 101 $BODY$
 102   LANGUAGE plpgsql VOLATILE
 103   COST 100;
 104
 105 ALTER FUNCTION  timewarp.trialbal_fill(integer)
 106   OWNER TO admin;