1 -- modified to use poreject_date
3 -- Function: postporeturncreditmemo(integer, numeric)
5 -- DROP FUNCTION postporeturncreditmemo(integer, numeric);
7 CREATE OR REPLACE FUNCTION postporeturncreditmemo(integer, numeric)
10 -- Copyright (c) 1999-2011 by OpenMFG LLC, d/b/a xTuple.
11 -- See www.xtuple.com/CPAL for the full text of the software license.
13 pPorejectId ALIAS FOR $1;
20 _journalNumber INTEGER;
22 _exchGainItem NUMERIC;
23 _itemAmount_base NUMERIC;
25 _glseriesTotal NUMERIC;
30 _taxAmount NUMERIC := 0;
31 _taxAmount_base NUMERIC;
36 SELECT NEXTVAL('apopen_apopen_id_seq') INTO _apopenid;
37 SELECT fetchGLSequence() INTO _sequence;
38 SELECT fetchJournalNumber('AP-MISC') INTO _journalNumber;
39 SELECT fetchapmemonumber() INTO _docNumber;
43 SELECT pohead_vend_id, pohead_number, pohead_curr_id, pohead_orderdate, pohead_taxzone_id,
44 poitem_id, poitem_itemsite_id,poitem_expcat_id, poitem_taxtype_id,
45 itemsite_costcat_id, poreject_qty, poreject_date,
46 ('Return of Item ' || COALESCE(item_number,poitem_vend_item_number)
47 || ', qty. ' || formatqty(poreject_qty)) AS notes,
48 poreject_value AS value,
49 currToBase(pohead_curr_id,(poitem_unitprice * poreject_qty),poreject_date) AS itemAmount_base,
50 (poitem_unitprice * poreject_qty) AS itemAmount
52 FROM pohead, poreject, poitem
53 LEFT OUTER JOIN itemsite ON (poitem_itemsite_id=itemsite_id)
54 LEFT OUTER JOIN item ON (itemsite_item_id=item_id)
55 WHERE ((poreject_poitem_id=poitem_id)
56 AND (pohead_id=poitem_pohead_id)
57 AND (poreject_id=pPorejectId));
59 _itemAmount := _p.itemAmount;
60 _itemAmount_base := _p.itemAmount_base;
61 IF (pAmount IS NOT NULL) THEN
62 _itemAmount := pAmount;
63 _itemAmount_base := currToBase(_p.pohead_curr_id, pAmount, _p.poreject_date);
67 -- Grab the G/L Accounts
68 IF (COALESCE(_p.poitem_itemsite_id, -1) = -1) THEN
69 SELECT pp.accnt_id AS pp_accnt_id,
70 lb.accnt_id AS lb_accnt_id INTO _a
71 FROM expcat, accnt AS pp, accnt AS lb
72 WHERE ( (expcat_purchprice_accnt_id=pp.accnt_id)
73 AND (expcat_liability_accnt_id=lb.accnt_id)
74 AND (expcat_id=_p.poitem_expcat_id) );
76 RAISE EXCEPTION 'Cannot Post Credit Memo due to unassigned G/L Accounts.';
79 SELECT pp.accnt_id AS pp_accnt_id,
80 lb.accnt_id AS lb_accnt_id INTO _a
81 FROM costcat, accnt AS pp, accnt AS lb
82 WHERE ( (costcat_purchprice_accnt_id=pp.accnt_id)
83 AND (costcat_liability_accnt_id=lb.accnt_id)
84 AND (costcat_id=_p.itemsite_costcat_id) );
86 RAISE EXCEPTION 'Cannot Post Credit Memo due to unassigned G/L Accounts.';
90 -- AP Open Item record
92 ( apopen_id, apopen_username, apopen_journalnumber,
93 apopen_vend_id, apopen_docnumber, apopen_doctype, apopen_ponumber,
94 apopen_docdate, apopen_duedate, apopen_distdate, apopen_terms_id,
95 apopen_amount, apopen_paid, apopen_open, apopen_notes, apopen_accnt_id, apopen_curr_id,
98 ( _apopenid, getEffectiveXtUser(), _journalNumber,
99 _p.pohead_vend_id, _docNumber, 'C', _p.pohead_number,
100 _p.poreject_date, _p.poreject_date, _p.poreject_date, -1,
101 round(_itemAmount, 2), 0, (round(_itemAmount, 2) <> 0), _p.notes, -1, _p.pohead_curr_id,
102 CASE WHEN (round(_itemAmount, 2) = 0) THEN _p.poreject_date END );
106 SELECT taxdetail_tax_id, sum(taxdetail_tax) AS taxdetail_tax,
107 currToBase(_p.pohead_curr_id, round(sum(taxdetail_tax),2), _p.poreject_date) AS taxbasevalue
108 FROM calculateTaxDetail(_p.pohead_taxzone_id, _p.poitem_taxtype_id,
109 _p.poreject_date, _p.pohead_curr_id,
111 GROUP BY taxdetail_tax_id
113 INSERT INTO apopentax (taxhist_basis,taxhist_percent,taxhist_amount,taxhist_docdate, taxhist_tax_id, taxhist_tax,
114 taxhist_taxtype_id, taxhist_parent_id, taxhist_journalnumber )
115 VALUES (0, 0, 0, _p.poreject_date, _tax.taxdetail_tax_id, _tax.taxdetail_tax, getadjustmenttaxtypeid(),
116 _apopenid, _journalNumber);
118 _taxAmount := _taxAmount + _tax.taxdetail_tax;
122 _taxAmount_base := addTaxToGLSeries(_sequence,
123 'A/P', 'CM', _docNumber,
124 _p.pohead_curr_id, _p.poreject_date, _p.poreject_date,
125 'apopentax', _apopenid,
128 UPDATE apopen SET apopen_amount = round(_itemAmount + _taxAmount,2)
129 WHERE (apopen_id = _apopenid);
131 -- Distribute from the clearing account
132 PERFORM insertIntoGLSeries( _sequence, 'A/P', 'CM', _docNumber,
135 _p.poreject_date, _p.notes );
136 _glseriesTotal := _glseriesTotal + round(_p.value, 2);
138 -- Distribute the remaining variance to the Purchase Price Variance account
139 IF (round(_itemAmount_base, 2) <> round(_p.value, 2)) THEN
140 _tmpTotal := round(_itemAmount_base, 2) - round(_p.value, 2);
141 PERFORM insertIntoGLSeries( _sequence, 'A/P', 'CM', _docNumber,
144 _p.poreject_date, _p.notes );
145 _glseriesTotal := _glseriesTotal + _tmpTotal;
148 -- Post the reject item for this P/O Item as Invoiced
150 SET poreject_invoiced=TRUE
151 WHERE poreject_id=pPorejectId;
153 -- Update the qty vouchered field
155 SET poitem_qty_vouchered = (poitem_qty_vouchered - _p.poreject_qty)
156 WHERE (poitem_id=_p.poitem_id);
159 SELECT findAPAccount(_p.pohead_vend_id) INTO _apaccntid;
161 RAISE EXCEPTION 'Cannot Post Credit Memo due to an unassigned A/P Account.';
164 SELECT insertIntoGLSeries( _sequence, 'A/P', 'CM', _docNumber,
165 _apaccntid, round(_itemAmount_base + _taxAmount_base, 2) *-1,
166 _p.poreject_date, _p.notes ) INTO _test;
168 RAISE EXCEPTION 'Cannot Post Credit Memo.';
171 -- Clean up loose ends
173 _glseriesTotal := _glseriesTotal + round(_itemAmount_base, 2)*-1;
175 IF (round(_glseriesTotal, 2) != 0) THEN
176 PERFORM insertIntoGLSeries(_sequence, 'A/P', 'CM',
177 'Currency Exchange Rounding - ' || _docNumber,
178 getGainLossAccntId(_apaccntid), round(_glseriesTotal, 2) * -1,
179 _p.poreject_date, _p.notes);
183 PERFORM postGLSeries(_sequence, _journalNumber);
185 RETURN _journalNumber;
189 LANGUAGE plpgsql VOLATILE
191 ALTER FUNCTION postporeturncreditmemo(integer, numeric)