git.roojs.org Git - xtuple/blob - foundation-database/public/tables/metasql/bankrec-receipts.mql

   1 -- Group: bankrec
   2 -- Name: receipts
   3 -- Notes: used by reconcileBankaccount
   4 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
   5 -- See www.xtuple.com/CPAL for the full text of the software license.
   6
   7 <? if exists("summary") ?>
   8 SELECT COALESCE(SUM(amount),0.0) AS cleared_amount
   9   FROM ( SELECT COALESCE( bankrecitem_amount,
  10                           (currToLocal(bankaccnt_curr_id, gltrans_amount, gltrans_date) * -1.0) ) AS amount
  11            FROM (bankaccnt CROSS JOIN gltrans)
  12                       JOIN bankrecitem ON ((bankrecitem_source='GL')
  13                                        AND (bankrecitem_source_id=gltrans_id)
  14                                        AND (bankrecitem_bankrec_id=<? value("bankrecid") ?>)
  15                                        AND (bankrecitem_cleared))
  16           WHERE ((gltrans_accnt_id=bankaccnt_accnt_id)
  17             AND (NOT gltrans_deleted)
  18             AND (NOT gltrans_rec)
  19             AND (gltrans_amount < 0)
  20             AND (gltrans_doctype != 'JP')
  21             AND (bankaccnt_id=<? value("bankaccntid") ?>) )
  22           UNION ALL
  23          SELECT COALESCE( bankrecitem_amount,
  24                           (currToLocal(bankaccnt_curr_id, sltrans_amount, sltrans_date) * -1.0) ) AS amount
  25            FROM (bankaccnt CROSS JOIN sltrans)
  26                       JOIN bankrecitem ON ((bankrecitem_source='SL')
  27                                        AND (bankrecitem_source_id=sltrans_id)
  28                                        AND (bankrecitem_bankrec_id=<? value("bankrecid") ?>)
  29                                        AND (bankrecitem_cleared))
  30           WHERE ((sltrans_accnt_id=bankaccnt_accnt_id)
  31             AND (NOT sltrans_rec)
  32             AND (sltrans_amount < 0)
  33             AND (bankaccnt_id=<? value("bankaccntid") ?>) )
  34           UNION ALL
  35          SELECT CASE WHEN(bankadjtype_iscredit=true) THEN (bankadj_amount * -1) ELSE bankadj_amount END AS amount
  36            FROM bankrecitem, bankadj, bankadjtype
  37           WHERE ( (bankrecitem_source='AD')
  38             AND (bankrecitem_source_id=bankadj_id)
  39             AND (bankrecitem_bankrec_id=<? value("bankrecid") ?>)
  40             AND (bankrecitem_cleared)
  41             AND (bankadj_bankadjtype_id=bankadjtype_id)
  42             AND (NOT bankadj_posted)
  43             AND (((bankadjtype_iscredit=false) AND (bankadj_amount > 0)) OR (bankadjtype_iscredit=true AND (bankadj_amount < 0)))
  44             AND (bankadj_bankaccnt_id=<? value("bankaccntid") ?>) ) ) AS data;
  45 <? else ?>
  46 SELECT gltrans_id AS id, 1 AS altid,
  47        jrnluse_use AS use, gltrans_journalnumber AS jrnlnum,
  48        COALESCE(date(jrnluse_date), gltrans_date) AS f_jrnldate,
  49        COALESCE(bankrecitem_cleared, FALSE) AS cleared,
  50        gltrans_date AS f_date,
  51        gltrans_doctype AS doc_type,
  52        gltrans_docnumber AS docnumber,
  53        gltrans_notes AS notes,
  54        currConcat(COALESCE(cashrcpt_curr_id, baseCurrId())) AS doc_curr,
  55        COALESCE(bankrecitem_curr_rate, cashrcpt_curr_rate, currRate(bankaccnt_curr_id, gltrans_date)) AS doc_exchrate,
  56        (gltrans_amount * -1.0) AS base_amount,
  57        CASE WHEN (bankaccnt_curr_id=cashrcpt_curr_id) THEN
  58          COALESCE( bankrecitem_amount,
  59                    cashrcpt_amount,
  60                    (currToLocal(bankaccnt_curr_id, gltrans_amount, gltrans_date) * -1.0) )
  61             ELSE
  62          COALESCE( bankrecitem_amount,
  63                    (currToLocal(bankaccnt_curr_id, gltrans_amount, gltrans_date) * -1.0) )
  64        END AS amount,
  65        COALESCE(date(jrnluse_date), gltrans_date) AS jrnldate,
  66        gltrans_date AS sortdate,
  67        'uomratio' AS doc_exchrate_xtnumericrole,
  68        'curr' AS base_amount_xtnumericrole,
  69        'curr' AS amount_xtnumericrole
  70   FROM bankaccnt
  71   JOIN gltrans ON (bankaccnt_accnt_id=gltrans_accnt_id)
  72   LEFT OUTER JOIN bankrecitem ON ((bankrecitem_source='GL')
  73                               AND (bankrecitem_source_id=gltrans_id)
  74                               AND (bankrecitem_bankrec_id=<? value("bankrecid") ?>)
  75                               AND (bankrecitem_cleared))
  76   LEFT OUTER JOIN jrnluse ON (jrnluse_number=gltrans_journalnumber AND jrnluse_use='C/R')
  77   LEFT OUTER JOIN cashrcpt  ON ((gltrans_source='A/R')
  78                             AND (gltrans_doctype='CR')
  79                             AND (gltrans_misc_id=cashrcpt_id))
  80   LEFT OUTER JOIN checkhead ON ((gltrans_doctype='CK')
  81                             AND (gltrans_misc_id=checkhead_id))
  82  WHERE ((NOT gltrans_deleted)
  83    AND (NOT gltrans_rec)
  84    AND (NOT COALESCE(checkhead_void, false))
  85    AND (gltrans_amount < 0)
  86    AND (gltrans_doctype != 'JP')
  87    AND (bankaccnt_id=<? value("bankaccntid") ?>) )
  88  UNION ALL
  89 SELECT sltrans_id AS id, 2 AS altid,
  90        jrnluse_use AS use, sltrans_journalnumber AS jrnlnum,
  91        COALESCE(date(jrnluse_date), sltrans_date) AS f_jrnldate,
  92        COALESCE(bankrecitem_cleared, FALSE) AS cleared,
  93        sltrans_date AS f_date,
  94        sltrans_doctype AS doc_type,
  95        sltrans_docnumber AS docnumber,
  96        sltrans_notes AS notes,
  97        currConcat(COALESCE(cashrcpt_curr_id, baseCurrId())) AS doc_curr,
  98        COALESCE(bankrecitem_curr_rate, cashrcpt_curr_rate, currRate(bankaccnt_curr_id, sltrans_date)) AS doc_exchrate,
  99        (sltrans_amount * -1.0) AS base_amount,
 100        CASE WHEN (bankaccnt_curr_id=cashrcpt_curr_id) THEN
 101          COALESCE( bankrecitem_amount,
 102                    cashrcpt_amount,
 103                    (currToLocal(bankaccnt_curr_id, sltrans_amount, sltrans_date) * -1.0) )
 104             ELSE
 105          COALESCE( bankrecitem_amount,
 106                    (currToLocal(bankaccnt_curr_id, sltrans_amount, sltrans_date) * -1.0) )
 107        END AS amount,
 108        COALESCE(date(jrnluse_date), sltrans_date) AS jrnldate,
 109        sltrans_date AS sortdate,
 110        'uomratio' AS doc_exchrate_xtnumericrole,
 111        'curr' AS base_amount_xtnumericrole,
 112        'curr' AS amount_xtnumericrole
 113   FROM bankaccnt
 114   JOIN sltrans ON (bankaccnt_accnt_id=sltrans_accnt_id)
 115   LEFT OUTER JOIN bankrecitem ON ((bankrecitem_source='SL')
 116                               AND (bankrecitem_source_id=sltrans_id)
 117                               AND (bankrecitem_bankrec_id=<? value("bankrecid") ?>)
 118                               AND (bankrecitem_cleared))
 119   LEFT OUTER JOIN jrnluse ON (jrnluse_number=sltrans_journalnumber AND jrnluse_use='C/R')
 120   LEFT OUTER JOIN cashrcpt  ON ((sltrans_source='A/R')
 121                             AND (sltrans_doctype='CR')
 122                             AND (sltrans_misc_id=cashrcpt_id))
 123   LEFT OUTER JOIN checkhead ON ((sltrans_doctype='CK')
 124                             AND (sltrans_misc_id=checkhead_id))
 125  WHERE ((NOT sltrans_rec)
 126    AND (NOT COALESCE(checkhead_void, false))
 127    AND (sltrans_amount < 0)
 128    AND (bankaccnt_id=<? value("bankaccntid") ?>) )
 129  UNION ALL
 130 SELECT bankadj_id AS id, 3 AS altid,
 131        '' AS use, NULL AS jrnlnum, bankadj_date AS f_jrnldate,
 132        COALESCE(bankrecitem_cleared, FALSE) AS cleared,
 133        bankadj_date AS f_date,
 134        'ADJ' AS doc_type,
 135        bankadj_docnumber AS docnumber,
 136        bankadjtype_name AS notes,
 137        currConcat(bankadj_curr_id) AS doc_curr,
 138        1.0 AS doc_exchrate,
 139        CASE WHEN(bankadjtype_iscredit=true) THEN (bankadj_amount * -1.0) ELSE bankadj_amount END AS base_amount,
 140        CASE WHEN(bankadjtype_iscredit=true) THEN (bankadj_amount * -1.0) ELSE bankadj_amount END AS amount,
 141        bankadj_date AS jrnldate,
 142        bankadj_date AS sortdate,
 143        'uomratio' AS doc_exchrate_xtnumericrole,
 144        'curr' AS base_amount_xtnumericrole,
 145        'curr' AS amount_xtnumericrole
 146   FROM (bankadjtype CROSS JOIN bankadj)
 147                LEFT OUTER JOIN bankrecitem ON ((bankrecitem_source='AD')
 148                                            AND (bankrecitem_source_id=bankadj_id)
 149                                            AND (bankrecitem_bankrec_id=<? value("bankrecid") ?>))
 150  WHERE ( (((bankadjtype_iscredit=false) AND (bankadj_amount > 0)) OR ((bankadjtype_iscredit=true) AND (bankadj_amount < 0)))
 151    AND (bankadj_bankadjtype_id=bankadjtype_id)
 152    AND (NOT bankadj_posted)
 153    AND (bankadj_bankaccnt_id=<? value("bankaccntid") ?>) )
 154 ORDER BY jrnldate, jrnlnum, sortdate;
 155 <? endif ?>