1 -- Function: deleteglseries(integer, text)
3 -- DROP FUNCTION deleteglseries(integer, text);
5 CREATE OR REPLACE FUNCTION undeleteglseries(integer, text)
9 pSequence ALIAS FOR $1;
18 -- March through all of the G/L Transactions for the passed sequence
20 gltrans_id, gltrans_date,
21 gltrans_accnt_id, gltrans_amount,
22 gltrans_posted, gltrans_rec,
23 accnt_closedpost, accnt_forwardupdate,
24 period_id, period_closed,
28 LEFT OUTER JOIN period ON (gltrans_date BETWEEN period_start AND period_end)
31 (gltrans_accnt_id=accnt_id)
35 (gltrans_sequence=pSequence)
39 -- If we can post into a Trial Balance, do so
40 IF ( (NOT _r.period_freeze) AND
41 ( (NOT _r.period_closed) OR (_r.accnt_closedpost) ) AND
42 ( NOT _r.gltrans_rec) AND
43 ( NOT _r.gltrans_posted ) ) THEN
45 -- Try to find an existing trialbal
46 SELECT trialbal_id INTO _trialbalid
48 WHERE ( (trialbal_period_id=_r.period_id)
49 AND (trialbal_accnt_id=_r.gltrans_accnt_id) );
51 GET DIAGNOSTICS _count = ROW_COUNT;
54 -- We found a trialbal, update it with the G/L Transaction
55 -- Note - two stage update to avoid any funny value caching logic
56 IF (_r.gltrans_amount > 0) THEN
58 SET trialbal_credits = (trialbal_credits + _r.gltrans_amount)
59 WHERE (trialbal_id=_trialbalid);
62 SET trialbal_debits = (trialbal_debits + (_r.gltrans_amount * -1))
63 WHERE (trialbal_id=_trialbalid);
67 SET trialbal_ending = (trialbal_beginning - trialbal_debits + trialbal_credits),
69 WHERE (trialbal_id=_trialbalid);
72 RAISE EXCEPTION 'Can not delete G/L Series. Trial balance record not found.';
75 -- Forward update if we should
76 IF (_r.accnt_forwardupdate AND fetchmetricbool('ManualForwardUpdate')) THEN
77 PERFORM forwardUpdateTrialBalance(_trialbalid);
80 -- Delete any bank reconciliation records if this was marked cleared but non reconciled
82 SELECT bankrecitem_id INTO v_tmp FROM
84 WHERE ((bankrecitem_source='GL')
85 AND (bankrecitem_source_id=_r.gltrans_id)) LIMIT 1;
88 RAISE EXCEPTION 'GL Series has ban reconcillation?? - this should not be possible for deleted ones!?';
93 -- Unflag any journals as posted as a result of this series
96 sltrans_gltrans_journalnumber=null
98 WHERE ((gltrans_sequence=pSequence)
99 AND (sltrans_gltrans_journalnumber=gltrans_journalnumber));
101 -- Mark the G/L Transaction as deleted
104 gltrans_deleted=false,
105 gltrans_notes=gltrans_notes || E'\n' || pNotes
106 WHERE (gltrans_id=_r.gltrans_id);
108 ELSIF (_r.period_freeze) THEN
109 RAISE EXCEPTION 'Can not delete a G/L Transaction in a frozen period';
110 ELSIF ((_r.period_closed) OR (NOT _r.accnt_closedpost)) THEN
111 RAISE EXCEPTION 'Can not delete a G/L Transaction on account % in a closed period', formatGlAccount(gltrans_accnt_id);
112 ELSIF (_r.gltrans_rec) THEN
113 RAISE EXCEPTION 'Can not delete a G/L Transaction that has been reconciled';
122 LANGUAGE plpgsql VOLATILE
124 ALTER FUNCTION undeleteglseries(integer, text)