1 CREATE OR REPLACE FUNCTION _aropenTrigger() RETURNS TRIGGER AS $$
2 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
3 -- See www.xtuple.com/CPAL for the full text of the software license.
7 _lateCount INTEGER := 0;
9 _checkLate BOOLEAN := false;
10 _checkLimit BOOLEAN := false;
15 -- Start with privileges
16 IF ( (NOT checkPrivilege('MaintainARMemos')) AND
17 (NOT checkPrivilege('PostMiscInvoices')) AND
18 (NOT checkPrivilege('PostARDocuments')) ) THEN
19 RAISE EXCEPTION 'You do not have privileges to maintain A/R Memos.';
22 IF ( (NEW.aropen_docnumber IS NULL) OR (LENGTH(NEW.aropen_docnumber) = 0) ) THEN
23 RAISE EXCEPTION 'You must enter a valid Document # for this A/R Memo.';
26 IF ( (NEW.aropen_amount IS NOT NULL) AND (NEW.aropen_amount < 0) ) THEN
27 RAISE EXCEPTION 'You must enter a positive Amount for this A/R Memo.';
30 IF (TG_OP IN ('INSERT', 'UPDATE') AND NEW.aropen_cust_id < 0) THEN
31 RAISE NOTICE 'Fixing deprecated use of negative aropen_cust_id';
32 NEW.aropen_cust_id := NULL;
35 IF (TG_OP IN ('INSERT', 'UPDATE') AND NEW.aropen_salesrep_id < 0) THEN
36 RAISE NOTICE 'Fixing deprecated use of negative aropen_salesrep_id';
37 NEW.aropen_salesrep_id := NULL;
40 IF (TG_OP = 'INSERT') THEN
41 SELECT aropen_id INTO _id
43 WHERE ( (aropen_doctype=NEW.aropen_doctype)
44 AND (aropen_docnumber=NEW.aropen_docnumber) )
47 RAISE EXCEPTION 'This Document Type/Number already exists. You may not enter a duplicate A/R Memo.';
50 --- clear the number from the issue cache if applicable
51 PERFORM clearNumberIssue('ARMemoNumber', NEW.aropen_docnumber);
54 -- Determine the number of late invoices
55 IF ( SELECT (metric_value='t')
57 WHERE(metric_name='AutoCreditWarnLateCustomers')) THEN
60 SELECT COALESCE(metric_value::integer, _graceDays)
63 WHERE(metric_name='DefaultAutoCreditWarnGraceDays');
67 SELECT COALESCE(cust_gracedays, _graceDays)
70 WHERE(cust_id=NEW.aropen_cust_id);
75 SELECT count(aropen_id)
78 WHERE((NEW.aropen_cust_id = aropen_cust_id)
80 AND (aropen_amount > aropen_paid)
81 AND (aropen_doctype IN ('I', 'D'))
82 AND (aropen_duedate < (CURRENT_DATE - _graceDays)));
84 -- Adjust _lateCount if late invoice being paid
85 IF ( (NEW.aropen_paid = NEW.aropen_amount)
86 AND (NEW.aropen_doctype IN ('I', 'D'))
87 AND (NEW.aropen_duedate < (CURRENT_DATE - _graceDays))) THEN
88 _lateCount := _lateCount - 1;
92 -- get the base exchange rate for the doc date
93 IF (TG_OP = 'INSERT' AND NEW.aropen_curr_rate IS NULL) THEN
94 SELECT curr_rate INTO _currrate
96 WHERE ( (NEW.aropen_curr_id=curr_id)
97 AND ( NEW.aropen_docdate BETWEEN curr_effective
100 NEW.aropen_curr_rate := _currrate;
102 RAISE EXCEPTION 'Currency exchange rate not found';
106 -- Close this aropen if it is paid
107 IF (NEW.aropen_paid = NEW.aropen_amount) THEN
108 NEW.aropen_open=FALSE;
110 -- Remove any aropenalloc regards that reference this aropen item
111 DELETE FROM aropenalloc WHERE (aropenalloc_aropen_id=NEW.aropen_id);
114 IF (TG_OP = 'INSERT') THEN
115 IF (NEW.aropen_open=FALSE)
116 AND (NEW.aropen_closedate IS NULL) THEN
117 NEW.aropen_closedate=current_date;
121 IF (TG_OP = 'UPDATE') THEN
122 IF ((OLD.aropen_open=TRUE)
123 AND (NEW.aropen_open=FALSE)
124 AND (NEW.aropen_closedate IS NULL)) THEN
125 NEW.aropen_closedate=current_date;
129 -- Only check if the customer in question has a non-zero Credit Limit
130 SELECT cust_id, cust_creditlmt, cust_creditstatus,
131 cust_autoupdatestatus, cust_autoholdorders INTO _p
133 WHERE (cust_id=NEW.aropen_cust_id);
134 IF (_p.cust_creditlmt > 0) THEN
137 SELECT COALESCE(SUM( CASE WHEN (aropen_doctype IN ('I', 'D')) THEN (aropen_amount - aropen_paid)
138 ELSE ((aropen_amount - aropen_paid) * -1)
139 END ), 0.0) INTO _openAmount
140 FROM aropen AS current
141 WHERE ( (current.aropen_cust_id=NEW.aropen_cust_id)
142 AND (current.aropen_open)
143 AND (current.aropen_id <> NEW.aropen_id) );
145 -- Add in the value of the current aropen item
146 IF (NEW.aropen_doctype IN ('I', 'D')) THEN
147 _openAmount := (_openAmount + (NEW.aropen_amount - NEW.aropen_paid));
149 _openAmount := (_openAmount - (NEW.aropen_amount - NEW.aropen_paid));
155 IF (_checkLimit OR _checkLate) THEN
156 -- Handle a Customer that is going under its credit limit
157 IF ((_p.cust_creditlmt >= _openAmount) AND (_lateCount <= 0)) THEN
159 -- Handle the Customer Status
160 IF ( (_p.cust_autoupdatestatus) AND (_p.cust_creditstatus='W') ) THEN
162 SET cust_creditstatus='G'
163 WHERE (cust_id=NEW.aropen_cust_id);
166 -- Handle the open Sales Orders
167 IF (_p.cust_autoholdorders) THEN
169 SET cohead_holdtype='N'
171 WHERE ( (coitem_cohead_id=cohead_id)
172 AND (cohead_holdtype='C')
173 AND (coitem_status='O')
174 AND (cohead_cust_id=_p.cust_id) );
177 -- Handle a Customer that is going over its credit limit
178 ELSIF ((_p.cust_creditlmt < _openAmount) OR (_lateCount > 0)) THEN
180 -- Handle the Customer Status
181 IF ( (_p.cust_autoupdatestatus) AND (_p.cust_creditstatus = 'G') ) THEN
183 SET cust_creditstatus='W'
184 WHERE (cust_id=NEW.aropen_cust_id);
187 -- Handle the open Sales Orders
188 IF (_p.cust_autoholdorders) THEN
190 SET cohead_holdtype='C'
192 WHERE ( (coitem_cohead_id=cohead_id)
193 AND (cohead_holdtype='N')
194 AND (coitem_status='O')
195 AND (cohead_cust_id=_p.cust_id) );
208 DROP TRIGGER IF EXISTS aropenTrigger ON aropen;
209 CREATE TRIGGER aropenTrigger BEFORE INSERT OR UPDATE ON aropen FOR EACH ROW EXECUTE PROCEDURE _aropenTrigger();
211 CREATE OR REPLACE FUNCTION _aropenAfterTrigger() RETURNS TRIGGER AS $$
212 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
213 -- See www.xtuple.com/CPAL for the full text of the software license.
216 _lateCount INTEGER := 0;
218 _checkLate BOOLEAN := false;
219 _checkLimit BOOLEAN := false;
223 IF (TG_OP = 'INSERT') THEN
224 _id := NEW.aropen_id;
226 _id := OLD.aropen_id;
228 -- If metric is set then auto close any associated incidents when AR is closed
229 IF (fetchMetricBool('AutoCloseARIncident')) THEN
230 IF (NEW.aropen_open = FALSE) THEN
231 UPDATE incdt SET incdt_status='L' WHERE (incdt_aropen_id=_id);
241 SELECT dropIfExists('TRIGGER', 'aropenAfterTrigger');
242 CREATE TRIGGER aropenAfterTrigger AFTER INSERT OR UPDATE ON aropen FOR EACH ROW EXECUTE PROCEDURE _aropenAfterTrigger();