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

   1 -- updated to match recent version
   2 -- added roudning on currtocurr...
   3
   4
   5 CREATE OR REPLACE FUNCTION createAPCreditMemoApplication(pSourceApopenId INTEGER,
   6                                                          pTargetApopenId INTEGER,
   7                                                          pAmount NUMERIC,
   8                                                          pCurrId INTEGER) RETURNS INTEGER AS $$
   9 -- Copyright (c) 1999-2012 by OpenMFG LLC, d/b/a xTuple.
  10 -- See www.xtuple.com/CPAL for the full text of the software license.
  11 DECLARE
  12   _apCreditApplyId      INTEGER;
  13
  14 BEGIN
  15   IF (pAmount > ROUND((SELECT currToCurr(apopen_curr_id, pCurrId, ROUND(apopen_amount - apopen_paid, 2), apopen_docdate)
  16                  FROM apopen
  17                  WHERE (apopen_id=pTargetApopenId)),2)) THEN
  18     RETURN -1;
  19   END IF;
  20
  21   IF (pAmount > (SELECT ROUND((apopen_amount - apopen_paid) -
  22                        COALESCE(SUM(currToCurr(apcreditapply_curr_id,
  23                                                 apopen_curr_id,
  24                                                 apcreditapply_amount,
  25                                                 apopen_docdate)), 0), 2)
  26              FROM apopen LEFT OUTER JOIN apcreditapply
  27                ON ((apcreditapply_source_apopen_id=apopen_id)
  28               AND (apcreditapply_target_apopen_id<>pTargetApopenId))
  29              WHERE (apopen_id=pSourceApopenId)
  30              GROUP BY apopen_amount, apopen_paid)) THEN
  31       RETURN -2;
  32     END IF;
  33
  34   SELECT apcreditapply_id INTO _apCreditApplyId
  35     FROM apcreditapply
  36    WHERE ((apcreditapply_source_apopen_id=pSourceApopenId)
  37      AND  (apcreditapply_target_apopen_id=pTargetApopenId));
  38
  39   IF (FOUND) THEN
  40     UPDATE apcreditapply SET apcreditapply_amount=pAmount,
  41                              apcreditapply_curr_id=pCurrId
  42     WHERE (apcreditapply_id=_apCreditApplyId);
  43   ELSE
  44     INSERT INTO apcreditapply (
  45       apcreditapply_source_apopen_id,
  46       apcreditapply_target_apopen_id,
  47       apcreditapply_amount, apcreditapply_curr_id
  48     ) VALUES (
  49       pSourceApopenId,
  50       pTargetApopenId,
  51       pAmount, pCurrId)
  52     RETURNING apcreditapply_id INTO _apCreditApplyId;
  53   END IF;
  54
  55   RETURN _apCreditApplyId;
  56
  57 END;
  58 $$ LANGUAGE 'plpgsql';