git.roojs.org Git - xtuple/blob - foundation-database/public/tables/metasql/cashReceipts-detail.mql

   1 -- Group: cashReceipts
   2 -- Name: detail
   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.
   9
  10 <? if exists("LegacyDisplayMode") ?>
  11
  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") ?>
  27          END || ' ' ||
  28          CASE WHEN (arapply_source_doctype IN ('C','R')) THEN TEXT(arapply_source_docnumber)
  29               ELSE arapply_refnumber
  30          END ) AS source,
  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,
  44 <? endif ?>
  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") ?>)))
  65 <? endif ?>
  66       )
  67
  68 -- Unposted cash receipts
  69 UNION
  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,
  93 <? endif ?>
  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") ?>)))
 110 <? endif ?>
 111       )
 112
 113 -- Cash Advance
 114 UNION
 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") ?>
 127          END || ' ' ||
 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") ?>
 133        END AS target,
 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,
 142 <? endif ?>
 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") ?>)))
 160 <? endif ?>
 161       )
 162 ORDER BY sortdate, source;
 163
 164 <? else ?>
 165
 166 --  New Display Mode
 167
 168 SELECT id, type, cashrcpt_number, cust_number, cust_name,
 169        postdate, posted, voided,
 170        source, source_xtidrole,
 171        posted,
 172        target, target_xtidrole,
 173        applied,
 174        base_applied,
 175 <? if exists("includeFormatted") ?>
 176        formatDate(postdate) AS f_postdate,
 177        formatMoney(applied) AS f_applied,
 178        formatMoney(base_applied) AS f_base_applied,
 179 <? endif ?>
 180        currAbbr,
 181        sortdate,
 182        'curr' AS applied_xtnumericrole,
 183        'curr' AS base_applied_xtnumericrole,
 184        base_applied_xttotalrole,
 185        type AS xtindentrole
 186 FROM (
 187
 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") ?>
 205             ELSE                           ''
 206        END AS target,
 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,
 213        -1 AS arapply_id
 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") ?>)))
 224 <? endif ?>
 225       )
 226
 227 --  Cash Receipt items
 228 UNION
 229 SELECT cashrcpt_id AS id, 1 AS type, '', '', '',
 230        cashrcpt_distdate AS postdate, cashrcpt_posted AS posted, cashrcpt_void AS voided,
 231        '' AS source,
 232        cashrcpt_id AS source_xtidrole,
 233        ( CASE WHEN (aropen_doctype='D') THEN <? value("debitMemo") ?>
 234               WHEN (aropen_doctype='I') THEN <? value("invoice") ?>
 235               WHEN (aropen_doctype='C') THEN <? value("creditMemo") ?>
 236               WHEN (aropen_doctype='R') THEN <? value("cashdeposit") ?>
 237               ELSE <? value("other") ?>
 238          END || ' ' || TEXT(aropen_docnumber) ) AS target,
 239        aropen_id AS target_xtidrole,
 240        COALESCE(arapply_applied, cashrcptitem_amount) AS applied,
 241        (COALESCE(arapply_applied,cashrcptitem_amount) / cashrcpt_curr_rate) AS base_applied,
 242        currConcat(cashrcpt_curr_id) AS currAbbr,
 243        cashrcpt_distdate AS sortdate,
 244        -1 AS base_applied_xttotalrole,
 245        arapply_id
 246 FROM cashrcpt JOIN custinfo ON (cust_id=cashrcpt_cust_id)
 247               LEFT OUTER JOIN custgrpitem ON (custgrpitem_cust_id=cust_id)
 248               JOIN cashrcptitem ON (cashrcptitem_cashrcpt_id=cashrcpt_id)
 249               JOIN aropen ON (aropen_id=cashrcptitem_aropen_id)
 250               LEFT OUTER JOIN arapply ON ((arapply_target_aropen_id=aropen_id OR
 251                                           arapply_source_aropen_id=aropen_id) AND
 252                                           arapply_reftype='CRA' AND
 253                                           arapply_ref_id=cashrcptitem_id)
 254 WHERE ( (cashrcpt_distdate BETWEEN <? value("startDate") ?> AND <? value("endDate") ?>)
 255 <? if exists("cust_id") ?>
 256   AND   (cust_id=<? value("cust_id") ?>)
 257 <? elseif exists("custtype_id") ?>
 258   AND   (cust_custtype_id=<? value("custtype_id") ?>)
 259 <? elseif exists("custgrp_id") ?>
 260   AND   (custgrpitem_custgrp_id=<? value("custgrp_id") ?>)
 261 <? elseif exists("custtype_pattern") ?>
 262   AND   (cust_custtype_id IN (SELECT custtype_id FROM custtype WHERE (custtype_code ~ <? value("custtype_pattern") ?>)))
 263 <? endif ?>
 264       )
 265
 266 --  Cash Receipt misc
 267 UNION
 268 SELECT cashrcpt_id AS id, 1 AS type, '', '', '',
 269        cashrcpt_distdate AS postdate, cashrcpt_posted AS posted, cashrcpt_void AS voided,
 270        '' AS source,
 271        cashrcpt_id AS source_xtidrole,
 272        formatGLAccount(cashrcptmisc_accnt_id) AS target,
 273        -1 AS target_xtidrole,
 274        COALESCE(arapply_applied, cashrcptmisc_amount) AS applied,
 275        (COALESCE(arapply_applied, cashrcptmisc_amount) / cashrcpt_curr_rate) AS base_applied,
 276        currConcat(cashrcpt_curr_id) AS currAbbr,
 277        cashrcpt_distdate AS sortdate,
 278        -1 AS base_applied_xttotalrole,
 279        arapply_id
 280 FROM cashrcpt JOIN custinfo ON (cust_id=cashrcpt_cust_id)
 281               LEFT OUTER JOIN custgrpitem ON (custgrpitem_cust_id=cust_id)
 282               JOIN cashrcptmisc ON (cashrcptmisc_cashrcpt_id=cashrcpt_id)
 283               LEFT OUTER JOIN arapply ON (arapply_reftype='CRD' AND
 284                                           arapply_ref_id=cashrcptmisc_id)
 285 WHERE ( (cashrcpt_distdate BETWEEN <? value("startDate") ?> AND <? value("endDate") ?>)
 286 <? if exists("cust_id") ?>
 287   AND   (cust_id=<? value("cust_id") ?>)
 288 <? elseif exists("custtype_id") ?>
 289   AND   (cust_custtype_id=<? value("custtype_id") ?>)
 290 <? elseif exists("custgrp_id") ?>
 291   AND   (custgrpitem_custgrp_id=<? value("custgrp_id") ?>)
 292 <? elseif exists("custtype_pattern") ?>
 293   AND   (cust_custtype_id IN (SELECT custtype_id FROM custtype WHERE (custtype_code ~ <? value("custtype_pattern") ?>)))
 294 <? endif ?>
 295       )
 296   ) AS data
 297 ORDER BY sortdate, id, type, target;
 298
 299 <? endif ?>
 300