git.roojs.org Git - Pman.Xtuple/blob - pgsql/voidpostedcheck.sql

   1 -- updted to match svn version.
   2 --- added checkhead_voided
   3
   4 ALTER TABLE checkhead add column checkhead_voided DATE;
   5
   6 CREATE OR REPLACE FUNCTION voidPostedCheck(INTEGER, INTEGER, DATE) RETURNS INTEGER AS $$
   7 -- Copyright (c) 1999-2012 by OpenMFG LLC, d/b/a xTuple.
   8 -- See www.xtuple.com/CPAL for the full text of the software license.
   9 DECLARE
  10   pCheckid              ALIAS FOR $1;
  11   pJournalNumber        ALIAS FOR $2;
  12   pVoidDate             ALIAS FOR $3;
  13   _amount_base          NUMERIC := 0;
  14   _result               INTEGER;
  15   _apopenid             INTEGER;
  16   _credit_glaccnt       INTEGER;
  17   _docnumber            TEXT;
  18   _exchGain             NUMERIC := 0;
  19   _exchGainTmp          NUMERIC := 0;
  20   _gltransNote          TEXT;
  21   _p                    RECORD;
  22   _r                    RECORD;
  23   _sequence             INTEGER;
  24   _amount_check         NUMERIC := 0;
  25
  26 BEGIN
  27
  28   SELECT fetchGLSequence() INTO _sequence;
  29
  30   SELECT checkhead.*,
  31          checkhead_amount / checkhead_curr_rate AS checkhead_amount_base,
  32          bankaccnt_accnt_id AS bankaccntid,
  33          findPrepaidAccount(checkhead_recip_id) AS prepaidaccntid,
  34          checkrecip.* INTO _p
  35   FROM bankaccnt, checkhead LEFT OUTER JOIN
  36        checkrecip ON ((checkrecip_type=checkhead_recip_type)
  37                   AND (checkrecip_id=checkhead_recip_id))
  38   WHERE ((checkhead_bankaccnt_id=bankaccnt_id)
  39     AND  (checkhead_id=pCheckid));
  40
  41   IF (NOT _p.checkhead_posted) THEN
  42     RETURN -10;
  43   END IF;
  44
  45   IF (_p.checkrecip_id IS NULL) THEN    -- outer join failed
  46     RETURN -11;
  47   END IF;
  48
  49   -- Cannot void if already reconciled
  50   SELECT trans_id INTO _result
  51   FROM ( SELECT gltrans_id AS trans_id
  52          FROM gltrans
  53          WHERE ((gltrans_doctype='CK')
  54            AND  (gltrans_misc_id=_p.checkhead_id)
  55            AND  (gltrans_rec))
  56          UNION ALL
  57          SELECT sltrans_id AS trans_id
  58          FROM sltrans
  59          WHERE ((sltrans_doctype='CK')
  60            AND  (sltrans_misc_id=_p.checkhead_id)
  61            AND  (sltrans_rec)) ) AS data;
  62   IF (FOUND) THEN
  63     RETURN -14;
  64   END IF;
  65
  66   _gltransNote := 'Void Posted Check #' || _p.checkhead_number || ' ' ||
  67                   _p.checkrecip_number || '-' || _p.checkrecip_name;
  68
  69   IF (_p.checkhead_misc) THEN
  70     IF (COALESCE(_p.checkhead_expcat_id, -1) < 0) THEN
  71       IF (_p.checkhead_recip_type = 'V') THEN
  72         PERFORM createAPDebitMemo(_p.checkhead_recip_id, pJournalNumber,
  73                                   CAST(fetchAPMemoNumber() AS text), '',
  74                                   pVoidDate, _p.checkhead_amount,
  75                                   _gltransNote || ' '|| _p.checkhead_notes,
  76                                   -1, pVoidDate, -1, _p.checkhead_curr_id );
  77         _credit_glaccnt := findAPPrepaidAccount(_p.checkhead_recip_id);
  78
  79       ELSIF (_p.checkhead_recip_type = 'C') THEN
  80         PERFORM createARCreditMemo(NULL, _p.checkhead_recip_id,
  81                                   fetchARMemoNumber(), '',
  82                                   pVoidDate, _p.checkhead_amount,
  83                                   _gltransNote || ' '|| _p.checkhead_notes,
  84                                   -1, -1, -1, pVoidDate, -1, NULL, 0.0,
  85                                   pJournalNumber, _p.checkhead_curr_id );
  86         _credit_glaccnt := _p.prepaidaccntid;
  87
  88       ELSIF (_p.checkhead_recip_type = 'T') THEN
  89         -- TODO: should we create a debit memo for the tax authority? how?
  90         _credit_glaccnt := _p.checkrecip_accnt_id;
  91
  92       END IF; -- recip type
  93
  94     ELSE
  95       SELECT expcat_exp_accnt_id INTO _credit_glaccnt
  96       FROM expcat
  97       WHERE (expcat_id=_p.checkhead_expcat_id);
  98       IF (NOT FOUND) THEN
  99         RETURN -12;
 100       END IF;
 101     END IF;
 102
 103     IF (COALESCE(_credit_glaccnt, -1) < 0) THEN
 104       RETURN -13;
 105     END IF;
 106
 107     PERFORM insertIntoGLSeries( _sequence, _p.checkrecip_gltrans_source, 'CK',
 108                                 text(_p.checkhead_number),
 109                                 _credit_glaccnt,
 110                                 round(_p.checkhead_amount_base, 2),
 111                                 pVoidDate, _gltransNote, pCheckid);
 112
 113     _amount_base := _p.checkhead_amount_base;
 114
 115   ELSE
 116     FOR _r IN SELECT checkitem_amount, checkitem_discount,
 117                      CASE WHEN (checkitem_apopen_id IS NOT NULL) THEN
 118                        checkitem_amount / apopen_curr_rate
 119                      ELSE
 120                        currToBase(checkitem_curr_id,
 121                                   checkitem_amount,
 122                                   COALESCE(checkitem_docdate, _p.checkhead_checkdate))
 123                      END AS checkitem_amount_base,
 124                        checkitem_amount / checkitem_curr_rate    AS amount_check,
 125                      apopen_id, apopen_doctype, apopen_docnumber, apopen_curr_rate, apopen_docdate,
 126                      aropen_id, aropen_doctype, aropen_docnumber,
 127                      checkitem_curr_id, checkitem_curr_rate,
 128                      COALESCE(checkitem_docdate, _p.checkhead_checkdate) AS docdate
 129               FROM (checkitem LEFT OUTER JOIN
 130                     apopen ON (checkitem_apopen_id=apopen_id)) LEFT OUTER JOIN
 131                     aropen ON (checkitem_aropen_id=aropen_id)
 132               WHERE (checkitem_checkhead_id=pcheckid) LOOP
 133
 134       _exchGainTmp := 0;
 135       IF (_r.apopen_id IS NOT NULL) THEN
 136         -- undo the APDiscount Credit Memo if a discount was taken
 137         IF(_r.checkitem_discount > 0) THEN
 138           SELECT NEXTVAL('apopen_apopen_id_seq') INTO _apopenid;
 139           SELECT fetchAPMemoNumber() INTO _docnumber;
 140           INSERT INTO apopen
 141           ( apopen_id, apopen_username, apopen_journalnumber,
 142             apopen_vend_id, apopen_docnumber, apopen_doctype, apopen_ponumber,
 143             apopen_docdate, apopen_duedate, apopen_distdate, apopen_terms_id,
 144             apopen_amount, apopen_paid, apopen_open,
 145             apopen_notes,
 146             apopen_accnt_id, apopen_curr_id, apopen_discount, apopen_curr_rate,
 147             apopen_closedate )
 148           VALUES
 149           ( _apopenid, getEffectiveXtUser(), pJournalNumber,
 150             _p.checkhead_recip_id, _docnumber, 'D', '',
 151             pVoidDate, pVoidDate, pVoidDate, -1,
 152             _r.checkitem_discount, _r.checkitem_discount, FALSE,
 153             ('Reverse Posted Discount ' || _r.apopen_doctype || ' ' ||
 154               _r.apopen_docnumber),
 155             -1, _p.checkhead_curr_id, TRUE, _r.apopen_curr_rate,
 156             current_date );
 157
 158
 159           PERFORM insertIntoGLSeries( _sequence, _p.checkrecip_gltrans_source,
 160                                       'DS', _r.apopen_docnumber,
 161                                       findAPDiscountAccount(_p.checkhead_recip_id),
 162                                       round(_r.checkitem_discount / _r.apopen_curr_rate, 2) * -1,
 163                                       pVoidDate, _gltransNote, pCheckid);
 164
 165           PERFORM insertIntoGLSeries( _sequence, _p.checkrecip_gltrans_source,
 166                                       'DS', _r.apopen_docnumber,
 167                                       findAPAccount(_p.checkhead_recip_id),
 168                                       round(_r.checkitem_discount / _r.apopen_curr_rate, 2),
 169                                       pVoidDate, _gltransNote, pCheckid);
 170
 171           --  Post the application
 172           INSERT INTO apapply
 173           ( apapply_vend_id, apapply_postdate, apapply_username,
 174             apapply_source_apopen_id, apapply_source_doctype, apapply_source_docnumber,
 175             apapply_target_apopen_id, apapply_target_doctype, apapply_target_docnumber,
 176             apapply_journalnumber, apapply_amount, apapply_curr_id )
 177           VALUES
 178           ( _p.checkhead_recip_id, pVoidDate, getEffectiveXtUser(),
 179             _apopenid, 'D', _docnumber,
 180             _r.apopen_id, _r.apopen_doctype, _r.apopen_docnumber,
 181             pJournalNumber, (_r.checkitem_discount * -1), _r.checkitem_curr_id );
 182         END IF; -- discount was taken
 183
 184         UPDATE apopen
 185        SET apopen_paid = round(apopen_paid -
 186                                 (_r.checkitem_amount + noNeg(_r.checkitem_discount)), 2),
 187             apopen_open = round(apopen_amount, 2) >
 188                           round(apopen_paid -
 189                                 (_r.checkitem_amount + noNeg(_r.checkitem_discount)), 2),
 190             apopen_closedate = CASE WHEN (round(apopen_amount, 2) >
 191                                           round(apopen_paid -
 192                                            (_r.checkitem_amount + noNeg(_r.checkitem_discount)))) THEN NULL ELSE apopen_closedate END
 193         WHERE (apopen_id=_r.apopen_id);
 194
 195         --  Post the application
 196         INSERT INTO apapply
 197         ( apapply_vend_id, apapply_postdate, apapply_username,
 198           apapply_source_apopen_id, apapply_source_doctype, apapply_source_docnumber,
 199           apapply_target_apopen_id, apapply_target_doctype, apapply_target_docnumber,
 200           apapply_journalnumber, apapply_amount, apapply_curr_id )
 201         VALUES
 202         ( _p.checkhead_recip_id, pVoidDate, getEffectiveXtUser(),
 203           -1, 'K', _p.checkhead_number,
 204           _r.apopen_id, _r.apopen_doctype, _r.apopen_docnumber,
 205           pJournalNumber, (_r.checkitem_amount * -1), _r.checkitem_curr_id );
 206       END IF; -- if check item's apopen_id is not null
 207
 208       IF (_r.aropen_id IS NOT NULL) THEN
 209         UPDATE aropen
 210         SET aropen_paid = round(aropen_paid -_r.checkitem_amount, 2),
 211             aropen_open = round(aropen_amount, 2) >
 212                           round(aropen_paid - _r.checkitem_amount, 2)
 213         WHERE (aropen_id=_r.aropen_id);
 214
 215         --  Post the application
 216         INSERT INTO arapply
 217         ( arapply_cust_id, arapply_postdate, arapply_distdate, arapply_username,
 218           arapply_source_aropen_id, arapply_source_doctype, arapply_source_docnumber,
 219           arapply_target_aropen_id, arapply_target_doctype, arapply_target_docnumber,
 220           arapply_journalnumber, arapply_applied, arapply_curr_id )
 221         VALUES
 222         ( _p.checkhead_recip_id, pVoidDate, pVoidDate, getEffectiveXtUser(),
 223           -1, 'K', _p.checkhead_number,
 224           _r.aropen_id, _r.aropen_doctype, _r.aropen_docnumber,
 225           pJournalNumber, (_r.checkitem_amount * -1), _r.checkitem_curr_id );
 226
 227       END IF; -- if check item's aropen_id is not null
 228
 229 --  calculate currency gain/loss
 230       IF (_r.apopen_id IS NOT NULL) THEN
 231         IF (_p.checkhead_curr_id = _r.checkitem_curr_id) THEN
 232           IF (_r.apopen_docdate > _p.checkhead_checkdate) THEN
 233             _exchGainTmp := ((_r.checkitem_amount/_p.checkhead_curr_rate) - (_r.checkitem_amount / _r.apopen_curr_rate)) * -1;
 234           ELSE
 235             _exchGainTmp := ((_r.checkitem_amount / _r.apopen_curr_rate) - (_r.checkitem_amount/_p.checkhead_curr_rate));
 236           END IF;
 237         ELSE
 238           -- unusual condition where bank overridden and different currency from voucher
 239           IF (_r.apopen_docdate > _p.checkhead_checkdate) THEN
 240             _exchGainTmp := ((_r.checkitem_amount/_r.checkitem_curr_rate) - (_r.checkitem_amount / _r.apopen_curr_rate)) * -1;
 241           ELSE
 242             _exchGainTmp := ((_r.checkitem_amount / _r.apopen_curr_rate) - (_r.checkitem_amount/_r.checkitem_curr_rate));
 243           END IF;
 244         END IF;
 245       ELSE
 246         SELECT arCurrGain(_r.aropen_id,_r.checkitem_curr_id, _r.checkitem_amount,
 247                         _p.checkhead_checkdate)
 248               INTO _exchGainTmp;
 249       END IF;
 250       _exchGain := _exchGain + _exchGainTmp;
 251
 252       PERFORM insertIntoGLSeries( _sequence, _p.checkrecip_gltrans_source,
 253                                   'CK', text(_p.checkhead_number),
 254                                   _p.checkrecip_accnt_id,
 255                                   round(_r.checkitem_amount_base, 2),
 256                                   pVoidDate, _gltransNote, pCheckid);
 257       IF (_exchGainTmp <> 0) THEN
 258           PERFORM insertIntoGLSeries( _sequence, _p.checkrecip_gltrans_source,
 259                                       'CK', text(_p.checkhead_number),
 260                                       getGainLossAccntId(_p.checkrecip_accnt_id),
 261                                       round(_exchGainTmp, 2) * -1,
 262                                       pVoidDate, _gltransNote, pCheckid);
 263       END IF;
 264
 265       _amount_check := (_amount_check + _r.amount_check);
 266       _amount_base := (_amount_base + _r.checkitem_amount_base);
 267
 268     END LOOP;
 269
 270     IF( (_amount_check - _p.checkhead_amount) <> 0.0 ) THEN
 271       _exchGainTmp :=  (_amount_check - _p.checkhead_amount) / _p.checkhead_curr_rate;
 272       _exchGain := _exchGain + _exchGainTmp;
 273     END IF;
 274
 275     --  ensure that the check balances, attribute rounding errors to gain/loss
 276     IF round(_amount_base, 2) - round(_exchGain, 2) <> round(_p.checkhead_amount_base, 2) THEN
 277       IF round(_amount_base - _exchGain, 2) = round(_p.checkhead_amount_base, 2) THEN
 278         PERFORM insertIntoGLSeries( _sequence, _p.checkrecip_gltrans_source,
 279                                     'CK',
 280                                     text(_p.checkhead_number), getGainLossAccntId(_p.bankaccntid),
 281                                     (round(_amount_base, 2) -
 282                                      round(_exchGain, 2) -
 283                                      round(_p.checkhead_amount_base, 2)) * -1,
 284                                     pVoidDate, _gltransNote, pCheckid);
 285       ELSE
 286         RAISE EXCEPTION 'checkhead_id % does not balance (% - % <> %)', pCheckid,
 287               _amount_base, _exchGain, _p.checkhead_amount_base;
 288       END IF;
 289     END IF;
 290   END IF;
 291
 292   PERFORM insertIntoGLSeries( _sequence, _p.checkrecip_gltrans_source, 'CK',
 293                               text(_p.checkhead_number),
 294                               _p.bankaccntid,
 295                               round(_p.checkhead_amount_base, 2) * -1,
 296                               pVoidDate, _gltransNote, pCheckid);
 297
 298   PERFORM postGLSeries(_sequence, pJournalNumber);
 299
 300   UPDATE gltrans
 301      SET gltrans_misc_id=pCheckid
 302    WHERE gltrans_sequence=_sequence;
 303
 304   UPDATE checkhead
 305   SET checkhead_posted=false,
 306       checkhead_void=true,
 307       checkhead_voided=pVoidDate,
 308       checkhead_journalnumber=pJournalNumber
 309   WHERE (checkhead_id=pCheckid);
 310
 311   RETURN pJournalNumber;
 312
 313 END;
 314 $$ LANGUAGE 'plpgsql';