1 CREATE OR REPLACE FUNCTION postCCcredit(INTEGER, TEXT, INTEGER) RETURNS INTEGER 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.
14 _notes TEXT := 'Credit via Credit Card';
20 IF ((preftype = 'cohead') AND NOT EXISTS(SELECT cohead_id
22 WHERE (cohead_id=prefid))) THEN
23 RAISE EXCEPTION 'Cannot find original Sales Order for this Credit Card credit [xtuple: postCCcredit, -2, %, %, %]',
24 pCCpay, preftype, prefid;
25 ELSIF ((preftype = 'aropen') AND NOT EXISTS(SELECT aropen_id
27 WHERE (aropen_id=prefid))) THEN
28 RAISE EXCEPTION 'Cannot find original A/R Open record for this Credit Card credit [xtuple: postCCcredit, -2, %, %, %]',
29 pCCpay, preftype, prefid;
30 ELSIF ((preftype = 'cmhead') AND NOT EXISTS(SELECT cmhead_id
32 WHERE cmhead_id=prefid)) THEN
33 RAISE EXCEPTION 'Cannot find original Credit Memo record for this Credit Card credit [xtuple: postCCcredit, -2, %, %, %]',
34 pCCpay, preftype, prefid;
39 LEFT OUTER JOIN ccard ON ccpay_ccard_id = ccard_id
40 WHERE (ccpay_id = pCCpay);
43 RAISE EXCEPTION 'Cannot find the record for this Credit Card credit [xtuple: postCCcredit, -3, %, %, %]',
44 pCCpay, preftype, prefid;
47 IF (preftype = 'cohead') THEN
48 _dglaccnt := findPrepaidAccount(_c.ccpay_cust_id);
50 _dglaccnt := findARAccount(_c.ccpay_cust_id);
53 IF (_c.ccard_type IS NOT NULL) THEN
54 _cardType = _c.ccard_type;
55 ELSIF (_c.ccpay_card_type IS NOT NULL) THEN
56 -- Support External Pre-Auths where the Card Type is pushed into ccpay.
57 -- There is no ccpay_ccard_id to join ccard on and get ccard_type.
58 _cardType = _c.ccpay_card_type;
60 -- TODO: Where is the other half of these -n error codes???
61 RAISE EXCEPTION 'Cannot find the Credit Card type [xtuple: postCCcredit, -5, %, %, %]',
62 pCCpay, preftype, prefid;
65 SELECT bankaccnt_accnt_id INTO _cglaccnt
67 JOIN bankaccnt ON (ccbank_bankaccnt_id=bankaccnt_id)
68 WHERE (ccbank_ccard_type=_cardType);
71 RAISE EXCEPTION 'Cannot find the default Bank Account for this Credit Card [xtuple: postCCcredit, -1, %]',
75 IF (_c.ccpay_type != 'R') THEN
76 RAISE EXCEPTION 'This Credit Card transaction is not a credit/refund [xtuple: postCCcredit, -4, %]',
80 _sequence := fetchGLSequence();
82 IF (_c.ccpay_r_ref IS NOT NULL) THEN
83 _ccOrderDesc := (_cardType || '-' || _c.ccpay_r_ref);
85 _ccOrderDesc := (_cardType || '-' || _c.ccpay_order_number::TEXT ||
86 '-' || COALESCE(_c.ccpay_order_number_seq::TEXT, ''));
89 _glseriesres := insertIntoGLSeries(_sequence, 'A/R', 'CC', _ccOrderDesc,
91 ROUND(currToBase(_c.ccpay_curr_id,
93 _c.ccpay_transaction_datetime::DATE), 2) * -1,
94 CURRENT_DATE, _notes);
95 IF (_glseriesres < 0) THEN
96 RAISE EXCEPTION 'Could not write debit side of Credit Card credit to the G/L [xtuple: insertIntoGLSeries, %]',
100 _glseriesres := insertIntoGLSeries(_sequence, 'A/R', 'CC', _ccOrderDesc,
102 ROUND(currToBase(_c.ccpay_curr_id,
104 _c.ccpay_transaction_datetime::DATE),2),
105 CURRENT_DATE, _notes);
106 IF (_glseriesres < 0) THEN
107 RAISE EXCEPTION 'Could not write credit side of Credit Card credit to the G/L [xtuple: insertIntoGLSeries, %]',
111 _glseriesres := postGLSeries(_sequence, fetchJournalNumber('C/R') );
112 IF (_glseriesres < 0) THEN
113 RAISE EXCEPTION 'Could not post Credit Card credit to the G/L [xtuple: postglseries, %]',
117 IF (preftype = 'aropen') THEN
120 WHERE (aropen_id=prefid);
123 SELECT aropen.* INTO _r
125 JOIN ccpay o ON (o.ccpay_id=n.ccpay_ccpay_id)
126 JOIN payaropen ON (payaropen_ccpay_id=o.ccpay_id)
127 JOIN aropen ON (payaropen_aropen_id=aropen_id)
128 WHERE (n.ccpay_id=pCCpay);
132 SELECT createardebitmemo(
134 _r.aropen_cust_id, NULL, fetchARMemoNumber(),
135 _r.aropen_ordernumber, current_date, _c.ccpay_amount,
137 -1, -1, -1, CURRENT_DATE, -1, NULL, 0,
138 _r.aropen_curr_id) INTO _dmaropenid;
140 IF (_r.aropen_open) THEN
141 PERFORM applyARCreditMemoToBalance(_r.aropen_id, _dmaropenid);
142 PERFORM postARCreditMemoApplication(_r.aropen_id);
147 IF (preftype = 'cohead') THEN
149 payco_ccpay_id, payco_cohead_id, payco_amount, payco_curr_id
151 pCCpay, prefid, 0 - _c.ccpay_amount, _c.ccpay_curr_id