2 CREATE OR REPLACE FUNCTION importBankrecCleared(pBankrecid INTEGER) RETURNS INTEGER 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.
11 _bankadjid INTEGER := -1;
12 _debitbankadjtypeid INTEGER := -1;
13 _creditbankadjtypeid INTEGER := -1;
18 -- cache some information
19 IF (pBankrecid = -1) THEN
20 _bankrecid := fetchMetricValue('ImportBankRecId');
22 _bankrecid := pBankrecid;
26 FROM bankrec JOIN bankaccnt ON (bankaccnt_id=bankrec_bankaccnt_id)
27 WHERE (bankrec_id=_bankrecid);
29 RAISE EXCEPTION 'bankrec not found';
31 IF (_b.bankrec_posted) THEN
32 RAISE EXCEPTION 'bankrec already posted';
35 _debitbankadjtypeid := fetchMetricValue('ImportBankRecDebitAdj');
36 IF (_debitbankadjtypeid = -1) THEN
37 RAISE EXCEPTION 'Metric ImportBankRecDebitAdj not defined [xtuple: reconcileBankAccount, -1]';
40 _creditbankadjtypeid := fetchMetricValue('ImportBankRecCreditAdj');
41 IF (_creditbankadjtypeid = -1) THEN
42 RAISE EXCEPTION 'Metric ImportBankRecCreditAdj not defined [xtuple: reconcileBankAccount, -2]';
45 -- loop thru bankrecimport and toggle cleared items
48 COALESCE(bankrecimport_debit_amount, 0.0) AS debit,
49 COALESCE(bankrecimport_credit_amount, 0.0) AS credit
51 -- TODO how to handle multiple bank accounts
52 -- WHERE (bankrecimport_?=_b.bankaccnt=?)
55 -- TODO how to handle duplicate document numbers
56 -- TODO how to handle amount differences
57 -- TODO add support for Project Accounting (sltrans)
59 IF ( (_r.debit > 0.0) AND (_r.credit > 0.0) ) THEN
60 RAISE NOTICE 'Bankrecimport % cannot determine if debit or credit', _r.bankrecimport_reference;
64 IF (_r.debit > 0.0) THEN
68 SELECT cashrcpt_id INTO _docid
70 WHERE (cashrcpt_docnumber=_r.bankrecimport_reference)
72 AND (NOT cashrcpt_void)
73 -- TODO workaround for duplicates
74 ORDER BY cashrcpt_id DESC
77 SELECT toggleBankrecCleared(_b.bankrec_id, 'GL', gltrans_id,
78 cashrcpt_curr_rate, _r.debit,
79 _r.bankrecimport_effdate) INTO _cleared
80 FROM gltrans LEFT OUTER JOIN bankrecitem ON (bankrecitem_source='GL' AND
81 bankrecitem_source_id=gltrans_id)
82 WHERE (gltrans_source='A/R')
83 AND (gltrans_doctype='CR')
84 AND (gltrans_misc_id=_docid)
86 AND (gltrans_accnt_id=_b.bankaccnt_accnt_id)
87 AND (NOT COALESCE(bankrecitem_cleared, FALSE));
90 -- create and toggle bank adjustment
91 -- TODO define bank adjustment names
94 SELECT bankadj_id INTO _bankadjid
95 FROM bankadj JOIN bankadjtype ON (bankadjtype_id=bankadj_bankadjtype_id)
96 WHERE (bankadjtype_id=_debitbankadjtypeid)
97 AND (bankadj_docnumber=_r.bankrecimport_reference)
98 AND (bankadj_bankaccnt_id=_b.bankaccnt_id);
101 (bankadj_bankaccnt_id, bankadj_bankadjtype_id, bankadj_date,
102 bankadj_docnumber, bankadj_amount, bankadj_notes,
105 (_b.bankaccnt_id, _debitbankadjtypeid, _r.bankrecimport_effdate,
106 _r.bankrecimport_reference, _r.debit, 'Import Bankrec Adjustment',
107 _b.bankaccnt_curr_id)
108 RETURNING bankadj_id INTO _bankadjid;
111 SELECT toggleBankrecCleared(_b.bankrec_id, 'AD', _bankadjid,
113 _r.bankrecimport_effdate) INTO _cleared
114 FROM bankadj LEFT OUTER JOIN bankrecitem ON (bankrecitem_source='AD' AND
115 bankrecitem_source_id=bankadj_id)
116 WHERE (bankadj_id=_bankadjid)
117 AND (NOT COALESCE(bankrecitem_cleared, FALSE));
120 -- done with receipts
126 SELECT checkhead_id INTO _docid
128 WHERE (checkhead_number::TEXT=_r.bankrecimport_reference)
129 AND (checkhead_posted)
130 AND (NOT checkhead_void);
132 SELECT toggleBankrecCleared(_b.bankrec_id, 'GL', gltrans_id,
133 checkhead_curr_rate, _r.credit,
134 _r.bankrecimport_effdate) INTO _cleared
135 FROM gltrans LEFT OUTER JOIN bankrecitem ON (bankrecitem_source='GL' AND
136 bankrecitem_source_id=gltrans_id)
137 WHERE (gltrans_source='A/P')
138 AND (gltrans_doctype='CK')
139 AND (gltrans_misc_id=_docid)
140 AND (NOT gltrans_rec)
141 AND (gltrans_accnt_id=_b.bankaccnt_accnt_id)
142 AND (NOT COALESCE(bankrecitem_cleared, FALSE));
145 -- create and toggle bank adjustment
146 -- TODO define bank adjustment names
149 SELECT bankadj_id INTO _bankadjid
150 FROM bankadj JOIN bankadjtype ON (bankadjtype_id=bankadj_bankadjtype_id)
151 WHERE (bankadjtype_id=_creditbankadjtypeid)
152 AND (bankadj_docnumber=_r.bankrecimport_reference);
155 (bankadj_bankaccnt_id, bankadj_bankadjtype_id, bankadj_date,
156 bankadj_docnumber, bankadj_amount, bankadj_notes,
159 (_b.bankaccnt_id, _creditbankadjtypeid, _r.bankrecimport_effdate,
160 _r.bankrecimport_reference, _r.credit, 'Import Bankrec Adjustment',
161 _b.bankaccnt_curr_id)
162 RETURNING bankadj_id INTO _bankadjid;
165 SELECT toggleBankrecCleared(_b.bankrec_id, 'AD', _bankadjid,
167 _r.bankrecimport_effdate) INTO _cleared
168 FROM bankadj LEFT OUTER JOIN bankrecitem ON (bankrecitem_source='AD' AND
169 bankrecitem_source_id=_bankadjid)
170 WHERE (NOT COALESCE(bankrecitem_cleared, FALSE));
181 $$ LANGUAGE 'plpgsql';