3 -- Notes: used by dspCashReceipts
4 -- There are 2 display modes for this query:
5 -- 1) Legacy mode which uses A/R Application information
6 -- 2) Version 3.3.0 upgrade mode which uses Cash Receipt information
7 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
8 -- See www.xtuple.com/CPAL for the full text of the software license.
10 <? if exists("LegacyDisplayMode") ?>
12 -- Posted cash receipts
13 SELECT arapply_id, 1 AS type, '' AS cashrcpt_number, cust_number, cust_name,
14 false AS voided, true AS posted, arapply_postdate AS postdate,
15 ( CASE WHEN (arapply_source_doctype='C') THEN <? value("creditMemo") ?>
16 WHEN (arapply_source_doctype='R') THEN <? value("cashdeposit") ?>
17 WHEN (arapply_fundstype='C') THEN <? value("check") ?>
18 WHEN (arapply_fundstype='T') THEN <? value("certifiedCheck") ?>
19 WHEN (arapply_fundstype='M') THEN <? value("masterCard") ?>
20 WHEN (arapply_fundstype='V') THEN <? value("visa") ?>
21 WHEN (arapply_fundstype='A') THEN <? value("americanExpress") ?>
22 WHEN (arapply_fundstype='D') THEN <? value("discoverCard") ?>
23 WHEN (arapply_fundstype='R') THEN <? value("otherCreditCard") ?>
24 WHEN (arapply_fundstype='K') THEN <? value("cash") ?>
25 WHEN (arapply_fundstype='W') THEN <? value("wireTransfer") ?>
26 WHEN (arapply_fundstype='O') THEN <? value("other") ?>
28 CASE WHEN (arapply_source_doctype IN ('C','R')) THEN TEXT(arapply_source_docnumber)
29 ELSE arapply_refnumber
31 COALESCE(cashrcptitem_cashrcpt_id,cashrcptmisc_cashrcpt_id,-1) AS source_xtidrole,
32 ( CASE WHEN (arapply_target_doctype='D') THEN <? value("debitMemo") ?>
33 WHEN (arapply_target_doctype='I') THEN <? value("invoice") ?>
34 ELSE <? value("other") ?>
35 END || ' ' || TEXT(arapply_target_docnumber) ) AS target,
36 COALESCE(arapply_target_aropen_id,-1) AS target_xtidrole,
37 arapply_applied AS applied,
38 'curr' AS applied_xtnumericrole,
39 currtobase(arapply_curr_id,arapply_applied,arapply_postdate) AS base_applied,
40 <? if exists("includeFormatted") ?>
41 formatDate(arapply_postdate) AS f_postdate,
42 formatMoney(arapply_applied) AS f_applied,
43 formatMoney(currtobase(arapply_curr_id,arapply_applied,arapply_postdate)) AS f_base_applied,
45 'curr' AS base_applied_xtnumericrole,
46 0 AS base_applied_xttotalrole,
47 currConcat(arapply_curr_id) AS currAbbr,
48 arapply_postdate AS sortdate
49 FROM custinfo LEFT OUTER JOIN custgrpitem ON (custgrpitem_cust_id=cust_id), arapply
50 LEFT OUTER JOIN cashrcptitem ON ((arapply_reftype='CRA')
51 AND (arapply_ref_id=cashrcptitem_id))
52 LEFT OUTER JOIN cashrcptmisc ON ((arapply_reftype='CRD')
53 AND (arapply_ref_id=cashrcptmisc_id))
54 WHERE ( (arapply_cust_id=cust_id)
55 AND (arapply_postdate BETWEEN <? value("startDate") ?> AND <? value("endDate") ?>)
56 AND (arapply_source_doctype ='K')
57 <? if exists("cust_id") ?>
58 AND (cust_id=<? value("cust_id") ?>)
59 <? elseif exists("custtype_id") ?>
60 AND (cust_custtype_id=<? value("custtype_id") ?>)
61 <? elseif exists("custgrp_id") ?>
62 AND (custgrpitem_custgrp_id=<? value("custgrp_id") ?>)
63 <? elseif exists("custtype_pattern") ?>
64 AND (cust_custtype_id IN (SELECT custtype_id FROM custtype WHERE (custtype_code ~ <? value("custtype_pattern") ?>)))
68 -- Unposted cash receipts
70 SELECT cashrcpt_id, 2 AS type, '' AS cashrcpt_number, cust_number, cust_name,
71 false AS voided, false AS posted, cashrcpt_distdate AS postdate,
72 ( CASE WHEN (cashrcpt_fundstype='C') THEN <? value("check") ?>
73 WHEN (cashrcpt_fundstype='T') THEN <? value("certifiedCheck") ?>
74 WHEN (cashrcpt_fundstype='M') THEN <? value("masterCard") ?>
75 WHEN (cashrcpt_fundstype='V') THEN <? value("visa") ?>
76 WHEN (cashrcpt_fundstype='A') THEN <? value("americanExpress") ?>
77 WHEN (cashrcpt_fundstype='D') THEN <? value("discoverCard") ?>
78 WHEN (cashrcpt_fundstype='R') THEN <? value("otherCreditCard") ?>
79 WHEN (cashrcpt_fundstype='K') THEN <? value("cash") ?>
80 WHEN (cashrcpt_fundstype='W') THEN <? value("wireTransfer") ?>
81 WHEN (cashrcpt_fundstype='O') THEN <? value("other") ?>
82 END || ' ' || cashrcpt_docnumber ) AS source,
83 cashrcpt_id AS source_xtidrole,
84 <? value("unposted") ?> AS target,
85 -1 AS target_xtidrole,
86 cashrcpt_amount AS applied,
87 'curr' AS applied_xtnumericrole,
88 (cashrcpt_amount / cashrcpt_curr_rate) AS base_applied,
89 <? if exists("includeFormatted") ?>
90 formatDate(cashrcpt_distdate) AS f_postdate,
91 formatMoney(cashrcpt_amount) AS f_applied,
92 formatMoney(cashrcpt_amount / cashrcpt_curr_rate) AS f_base_applied,
94 'curr' AS base_applied_xtnumericrole,
95 0 AS base_applied_xttotalrole,
96 currConcat(cashrcpt_curr_id) AS currAbbr,
97 cashrcpt_distdate AS sortdate
98 FROM cashrcpt, custinfo LEFT OUTER JOIN custgrpitem ON (custgrpitem_cust_id=cust_id)
99 WHERE ( (NOT cashrcpt_posted)
100 AND (cashrcpt_cust_id=cust_id)
101 AND (cashrcpt_distdate BETWEEN <? value("startDate") ?> AND <? value("endDate") ?>)
102 <? if exists("cust_id") ?>
103 AND (cust_id=<? value("cust_id") ?>)
104 <? elseif exists("custtype_id") ?>
105 AND (cust_custtype_id=<? value("custtype_id") ?>)
106 <? elseif exists("custgrp_id") ?>
107 AND (custgrpitem_custgrp_id=<? value("custgrp_id") ?>)
108 <? elseif exists("custtype_pattern") ?>
109 AND (cust_custtype_id IN (SELECT custtype_id FROM custtype WHERE (custtype_code ~ <? value("custtype_pattern") ?>)))
115 SELECT aropen_id, 3 AS type, '' AS cashrcpt_number, cust_number, cust_name,
116 false AS voided, true AS posted, aropen_docdate AS postdate,
117 ( CASE WHEN (substr(aropen_notes, 16, 1)='C') THEN <? value("check") ?>
118 WHEN (substr(aropen_notes, 16, 1)='T') THEN <? value("certifiedCheck") ?>
119 WHEN (substr(aropen_notes, 16, 1)='M') THEN <? value("masterCard") ?>
120 WHEN (substr(aropen_notes, 16, 1)='V') THEN <? value("visa") ?>
121 WHEN (substr(aropen_notes, 16, 1)='A') THEN <? value("americanExpress") ?>
122 WHEN (substr(aropen_notes, 16, 1)='D') THEN <? value("discoverCard") ?>
123 WHEN (substr(aropen_notes, 16, 1)='R') THEN <? value("otherCreditCard") ?>
124 WHEN (substr(aropen_notes, 16, 1)='K') THEN <? value("cash") ?>
125 WHEN (substr(aropen_notes, 16, 1)='W') THEN <? value("wireTransfer") ?>
126 WHEN (substr(aropen_notes, 16, 1)='O') THEN <? value("other") ?>
128 substr(aropen_notes, 18) ) AS source,
129 cashrcptitem_cashrcpt_id AS source_xtidrole,
130 CASE WHEN aropen_doctype = 'R' THEN
131 <? value("cashdeposit") ?>
132 ELSE <? value("creditMemo") ?>
134 aropen_id AS target_xtidrole,
135 aropen_amount AS applied,
136 'curr' AS applied_xtnumericrole,
137 aropen_amount / aropen_curr_rate AS base_applied,
138 <? if exists("includeFormatted") ?>
139 formatDate(aropen_docdate) AS f_postdate,
140 formatMoney(aropen_amount) AS f_applied,
141 formatMoney(aropen_amount / aropen_curr_rate) AS f_base_applied,
143 'curr' AS base_applied_xtnumericrole,
144 0 AS base_applied_xttotalrole,
145 currConcat(aropen_curr_id) AS currAbbr,
146 aropen_duedate AS sortdate
147 FROM custinfo LEFT OUTER JOIN custgrpitem ON (custgrpitem_cust_id=cust_id), aropen
148 JOIN cashrcptitem ON (aropen_id=cashrcptitem_aropen_id)
149 WHERE ( (aropen_cust_id=cust_id)
150 AND (aropen_doctype IN ('R','C'))
151 AND (aropen_docdate BETWEEN <? value("startDate") ?> AND <? value("endDate") ?>)
152 <? if exists("cust_id") ?>
153 AND (cust_id=<? value("cust_id") ?>)
154 <? elseif exists("custtype_id") ?>
155 AND (cust_custtype_id=<? value("custtype_id") ?>)
156 <? elseif exists("custgrp_id") ?>
157 AND (custgrpitem_custgrp_id=<? value("custgrp_id") ?>)
158 <? elseif exists("custtype_pattern") ?>
159 AND (cust_custtype_id IN (SELECT custtype_id FROM custtype WHERE (custtype_code ~ <? value("custtype_pattern") ?>)))
162 ORDER BY sortdate, source;
168 SELECT id, type, cashrcpt_number, cust_number, cust_name,
169 postdate, posted, voided,
170 source, source_xtidrole,
172 target, target_xtidrole,
175 <? if exists("includeFormatted") ?>
176 formatDate(postdate) AS f_postdate,
177 formatMoney(applied) AS f_applied,
178 formatMoney(base_applied) AS f_base_applied,
182 'curr' AS applied_xtnumericrole,
183 'curr' AS base_applied_xtnumericrole,
184 base_applied_xttotalrole,
188 -- Cash Receipt headers
189 SELECT cashrcpt_id AS id, 0 AS type, cashrcpt_number, cust_number, cust_name,
190 cashrcpt_distdate AS postdate, cashrcpt_posted AS posted, cashrcpt_void AS voided,
191 ( CASE WHEN (cashrcpt_fundstype='C') THEN <? value("check") ?>
192 WHEN (cashrcpt_fundstype='T') THEN <? value("certifiedCheck") ?>
193 WHEN (cashrcpt_fundstype='M') THEN <? value("masterCard") ?>
194 WHEN (cashrcpt_fundstype='V') THEN <? value("visa") ?>
195 WHEN (cashrcpt_fundstype='A') THEN <? value("americanExpress") ?>
196 WHEN (cashrcpt_fundstype='D') THEN <? value("discoverCard") ?>
197 WHEN (cashrcpt_fundstype='R') THEN <? value("otherCreditCard") ?>
198 WHEN (cashrcpt_fundstype='K') THEN <? value("cash") ?>
199 WHEN (cashrcpt_fundstype='W') THEN <? value("wireTransfer") ?>
200 WHEN (cashrcpt_fundstype='O') THEN <? value("other") ?>
201 END || ' ' || cashrcpt_docnumber ) AS source,
202 cashrcpt_id AS source_xtidrole,
203 CASE WHEN (cashrcpt_void) THEN <? value("voided") ?>
204 WHEN (NOT cashrcpt_posted) THEN <? value("unposted") ?>
207 -1 AS target_xtidrole,
208 cashrcpt_amount AS applied,
209 (cashrcpt_amount / cashrcpt_curr_rate) AS base_applied,
210 currConcat(cashrcpt_curr_id) AS currAbbr,
211 cashrcpt_distdate AS sortdate,
212 0 AS base_applied_xttotalrole,
214 FROM cashrcpt JOIN custinfo ON (cust_id=cashrcpt_cust_id) LEFT OUTER JOIN custgrpitem ON (custgrpitem_cust_id=cust_id)
215 WHERE ( (cashrcpt_distdate BETWEEN <? value("startDate") ?> AND <? value("endDate") ?>)
216 <? if exists("cust_id") ?>
217 AND (cust_id=<? value("cust_id") ?>)
218 <? elseif exists("custtype_id") ?>
219 AND (cust_custtype_id=<? value("custtype_id") ?>)
220 <? elseif exists("custgrp_id") ?>
221 AND (custgrpitem_custgrp_id=<? value("custgrp_id") ?>)
222 <? elseif exists("custtype_pattern") ?>
223 AND (cust_custtype_id IN (SELECT custtype_id FROM custtype WHERE (custtype_code ~ <? value("custtype_pattern") ?>)))
225 <? if exists("fundstype") ?>
226 AND (cashrcpt_fundstype=<? value("fundstype") ?>)
230 -- Cash Receipt items
232 SELECT cashrcpt_id AS id, 1 AS type, '', '', '',
233 cashrcpt_distdate AS postdate, cashrcpt_posted AS posted, cashrcpt_void AS voided,
235 cashrcpt_id AS source_xtidrole,
236 ( CASE WHEN (aropen_doctype='D') THEN <? value("debitMemo") ?>
237 WHEN (aropen_doctype='I') THEN <? value("invoice") ?>
238 WHEN (aropen_doctype='C') THEN <? value("creditMemo") ?>
239 WHEN (aropen_doctype='R') THEN <? value("cashdeposit") ?>
240 ELSE <? value("other") ?>
241 END || ' ' || TEXT(aropen_docnumber) ) AS target,
242 aropen_id AS target_xtidrole,
243 COALESCE(arapply_applied, cashrcptitem_amount) AS applied,
244 (COALESCE(arapply_applied,cashrcptitem_amount) / cashrcpt_curr_rate) AS base_applied,
245 currConcat(cashrcpt_curr_id) AS currAbbr,
246 cashrcpt_distdate AS sortdate,
247 -1 AS base_applied_xttotalrole,
249 FROM cashrcpt JOIN custinfo ON (cust_id=cashrcpt_cust_id)
250 LEFT OUTER JOIN custgrpitem ON (custgrpitem_cust_id=cust_id)
251 JOIN cashrcptitem ON (cashrcptitem_cashrcpt_id=cashrcpt_id)
252 JOIN aropen ON (aropen_id=cashrcptitem_aropen_id)
253 LEFT OUTER JOIN arapply ON ((arapply_target_aropen_id=aropen_id OR
254 arapply_source_aropen_id=aropen_id) AND
255 arapply_reftype='CRA' AND
256 arapply_ref_id=cashrcptitem_id)
257 WHERE ( (cashrcpt_distdate BETWEEN <? value("startDate") ?> AND <? value("endDate") ?>)
258 <? if exists("cust_id") ?>
259 AND (cust_id=<? value("cust_id") ?>)
260 <? elseif exists("custtype_id") ?>
261 AND (cust_custtype_id=<? value("custtype_id") ?>)
262 <? elseif exists("custgrp_id") ?>
263 AND (custgrpitem_custgrp_id=<? value("custgrp_id") ?>)
264 <? elseif exists("custtype_pattern") ?>
265 AND (cust_custtype_id IN (SELECT custtype_id FROM custtype WHERE (custtype_code ~ <? value("custtype_pattern") ?>)))
267 <? if exists("fundstype") ?>
268 AND (cashrcpt_fundstype=<? value("fundstype") ?>)
274 SELECT cashrcpt_id AS id, 1 AS type, '', '', '',
275 cashrcpt_distdate AS postdate, cashrcpt_posted AS posted, cashrcpt_void AS voided,
277 cashrcpt_id AS source_xtidrole,
278 formatGLAccount(cashrcptmisc_accnt_id) AS target,
279 -1 AS target_xtidrole,
280 COALESCE(arapply_applied, cashrcptmisc_amount) AS applied,
281 (COALESCE(arapply_applied, cashrcptmisc_amount) / cashrcpt_curr_rate) AS base_applied,
282 currConcat(cashrcpt_curr_id) AS currAbbr,
283 cashrcpt_distdate AS sortdate,
284 -1 AS base_applied_xttotalrole,
286 FROM cashrcpt JOIN custinfo ON (cust_id=cashrcpt_cust_id)
287 LEFT OUTER JOIN custgrpitem ON (custgrpitem_cust_id=cust_id)
288 JOIN cashrcptmisc ON (cashrcptmisc_cashrcpt_id=cashrcpt_id)
289 LEFT OUTER JOIN arapply ON (arapply_reftype='CRD' AND
290 arapply_ref_id=cashrcptmisc_id)
291 WHERE ( (cashrcpt_distdate BETWEEN <? value("startDate") ?> AND <? value("endDate") ?>)
292 <? if exists("cust_id") ?>
293 AND (cust_id=<? value("cust_id") ?>)
294 <? elseif exists("custtype_id") ?>
295 AND (cust_custtype_id=<? value("custtype_id") ?>)
296 <? elseif exists("custgrp_id") ?>
297 AND (custgrpitem_custgrp_id=<? value("custgrp_id") ?>)
298 <? elseif exists("custtype_pattern") ?>
299 AND (cust_custtype_id IN (SELECT custtype_id FROM custtype WHERE (custtype_code ~ <? value("custtype_pattern") ?>)))
301 <? if exists("fundstype") ?>
302 AND (cashrcpt_fundstype=<? value("fundstype") ?>)
306 ORDER BY sortdate, id, type, target;