2 CREATE OR REPLACE FUNCTION importBankrecCleared(pBankrecid INTEGER) RETURNS BOOLEAN AS $$
3 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
4 -- See www.xtuple.com/CPAL for the full text of the software license.
14 -- cache some information
16 FROM bankrec JOIN bankaccnt ON (bankaccnt_id=bankrec_bankaccnt_id)
17 WHERE (bankrec_id=pBankrecid);
19 RAISE EXCEPTION 'bankrec not found';
21 IF (_b.bankrec_posted) THEN
22 RAISE EXCEPTION 'bankrec already posted';
25 -- loop thru bankrecimport and toggle cleared items
28 COALESCE(bankrecimport_debit_amount, 0.0) AS debit,
29 COALESCE(bankrecimport_credit_amount, 0.0) AS credit
31 -- WHERE (bankrecimport_?=_b.bankaccnt=?)
34 IF ( (_r.debit > 0.0) AND (_r.credit > 0.0) ) THEN
35 RAISE NOTICE 'Bankrecimport % cannot determine if debit or credit', _r.bankrecimport_reference;
39 IF (_r.debit > 0.0) THEN
41 SELECT cashrcpt_id INTO _docid
43 WHERE (cashrcpt_docnumber=_r.bankrecimport_reference)
44 AND (cashrcpt_posted);
46 SELECT toggleBankrecCleared(_b.bankrec_id, 'GL', gltrans_id, cashrcpt_curr_rate, _r.debit, _r.bankrecimport_effdate) INTO _cleared
47 FROM cashrcpt JOIN gltrans ON ((gltrans_source='A/R')
48 AND (gltrans_doctype='CR')
49 AND (gltrans_accnt_id=_b.bankaccnt_accnt_id)
50 AND (gltrans_misc_id=cashrcpt_id))
51 WHERE (cashrcpt_id=_docid);
56 RAISE NOTICE 'Bankrecimport % not found', _r.bankrecimport_reference;
62 $$ LANGUAGE 'plpgsql';