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

   1 -- Group: bankrec
   2 -- Name: checks
   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) ) 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) ) 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=false) THEN (bankadj_amount * -1) ELSE bankadj_amount END AS amount
  36            FROM bankadj, bankadjtype, bankrecitem
  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=true) AND (bankadj_amount > 0)) OR ((bankadjtype_iscredit=false) 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        COALESCE(bankrecitem_cleared, FALSE) AS cleared,
  48        COALESCE(bankrecitem_effdate, gltrans_date) AS transdate,
  49        gltrans_doctype AS doc_type,
  50        gltrans_docnumber AS doc_number,
  51        gltrans_notes AS notes,
  52        (SELECT currConcat(COALESCE(checkhead_curr_id, baseCurrId()))) AS doc_curr,
  53        COALESCE(bankrecitem_curr_rate, checkhead_curr_rate, currRate(bankaccnt_curr_id, gltrans_date)) AS doc_exchrate,
  54        gltrans_amount AS base_amount,
  55        CASE WHEN (bankaccnt_curr_id=checkhead_curr_id) THEN
  56          COALESCE( bankrecitem_amount,
  57                    checkhead_amount,
  58                    currToLocal(bankaccnt_curr_id, gltrans_amount, gltrans_date) )
  59             ELSE
  60          COALESCE( bankrecitem_amount,
  61                    currToLocal(bankaccnt_curr_id, gltrans_amount, gltrans_date) )
  62        END AS amount,
  63        gltrans_date AS sortdate,
  64        'uomratio' AS doc_exchrate_xtnumericrole,
  65        'curr' AS base_amount_xtnumericrole,
  66        'curr' AS amount_xtnumericrole
  67   FROM (bankaccnt CROSS JOIN gltrans)
  68               LEFT OUTER JOIN bankrecitem ON ((bankrecitem_source='GL')
  69                                           AND (bankrecitem_source_id=gltrans_id)
  70                                           AND (bankrecitem_bankrec_id=<? value("bankrecid") ?>)
  71                                           AND (bankrecitem_cleared))
  72               LEFT OUTER JOIN checkhead ON ((gltrans_doctype='CK')
  73                                         AND (gltrans_misc_id=checkhead_id))
  74  WHERE ((gltrans_accnt_id=bankaccnt_accnt_id)
  75    AND (NOT gltrans_deleted)
  76    AND (NOT gltrans_rec)
  77    AND (NOT COALESCE(checkhead_void, false))
  78    AND (gltrans_amount > 0)
  79    AND (gltrans_doctype != 'JP')
  80    AND (bankaccnt_id=<? value("bankaccntid") ?>)
  81 <? if exists("source") ?>
  82    AND ('GL' = <? value("source") ?>)
  83 <? endif ?>
  84 <? if exists("sourceid") ?>
  85    AND (gltrans_id = <? value("sourceid") ?>)
  86 <? endif ?>
  87  )
  88  UNION ALL
  89 SELECT sltrans_id AS id, 2 AS altid,
  90        COALESCE(bankrecitem_cleared, FALSE) AS cleared,
  91        COALESCE(bankrecitem_effdate, sltrans_date) AS transdate,
  92        sltrans_doctype AS doc_type,
  93        sltrans_docnumber AS doc_number,
  94        sltrans_notes AS notes,
  95        (SELECT currConcat(COALESCE(checkhead_curr_id, baseCurrId()))) AS doc_curr,
  96        COALESCE(bankrecitem_curr_rate, checkhead_curr_rate, currRate(bankaccnt_curr_id, sltrans_date)) AS doc_exchrate,
  97        sltrans_amount AS base_amount,
  98        CASE WHEN (bankaccnt_curr_id=checkhead_curr_id) THEN
  99          COALESCE( bankrecitem_amount,
 100                    checkhead_amount,
 101                    currToLocal(bankaccnt_curr_id, sltrans_amount, sltrans_date) )
 102             ELSE
 103          COALESCE( bankrecitem_amount,
 104                    currToLocal(bankaccnt_curr_id, sltrans_amount, sltrans_date) )
 105        END AS amount,
 106        sltrans_date AS sortdate,
 107        'uomratio' AS doc_exchrate_xtnumericrole,
 108        'curr' AS base_amount_xtnumericrole,
 109        'curr' AS amount_xtnumericrole
 110   FROM (bankaccnt CROSS JOIN sltrans)
 111               LEFT OUTER JOIN bankrecitem ON ((bankrecitem_source='SL')
 112                                           AND (bankrecitem_source_id=sltrans_id)
 113                                           AND (bankrecitem_bankrec_id=<? value("bankrecid") ?>)
 114                                           AND (bankrecitem_cleared))
 115               LEFT OUTER JOIN checkhead ON ((sltrans_doctype='CK')
 116                                         AND (sltrans_misc_id=checkhead_id))
 117  WHERE ((sltrans_accnt_id=bankaccnt_accnt_id)
 118    AND (NOT sltrans_rec)
 119    AND (NOT COALESCE(checkhead_void, false))
 120    AND (sltrans_amount > 0)
 121    AND (bankaccnt_id=<? value("bankaccntid") ?>)
 122 <? if exists("source") ?>
 123    AND ('SL' = <? value("source") ?>)
 124 <? endif ?>
 125 <? if exists("sourceid") ?>
 126    AND (sltrans_id = <? value("sourceid") ?>)
 127 <? endif ?>
 128  )
 129  UNION ALL
 130 SELECT bankadj_id AS id, 3 AS altid,
 131        COALESCE(bankrecitem_cleared, FALSE) AS cleared,
 132        COALESCE(bankrecitem_effdate, bankadj_date) AS transdate,
 133        'ADJ' AS doc_type,
 134        bankadj_docnumber AS doc_number,
 135        bankadjtype_name AS notes,
 136        (SELECT currConcat(bankadj_curr_id)) AS doc_curr,
 137        1.0 AS doc_exchrate,
 138        CASE WHEN(bankadjtype_iscredit=false) THEN (bankadj_amount * -1.0) ELSE bankadj_amount END AS base_amount,
 139        CASE WHEN(bankadjtype_iscredit=false) THEN (bankadj_amount * -1.0) ELSE bankadj_amount END AS amount,
 140        bankadj_date AS sortdate,
 141        'uomratio' AS doc_exchrate_xtnumericrole,
 142        'curr' AS base_amount_xtnumericrole,
 143        'curr' AS amount_xtnumericrole
 144   FROM (bankadjtype CROSS JOIN bankadj)
 145                LEFT OUTER JOIN bankrecitem ON ((bankrecitem_source='AD')
 146                                            AND (bankrecitem_source_id=bankadj_id)
 147                                            AND (bankrecitem_bankrec_id=<? value("bankrecid") ?>)
 148                                            AND (bankrecitem_cleared))
 149  WHERE ( (((bankadjtype_iscredit=true) AND (bankadj_amount > 0)) OR ((bankadjtype_iscredit=false) AND (bankadj_amount < 0)))
 150    AND (bankadj_bankadjtype_id=bankadjtype_id)
 151    AND (NOT bankadj_posted)
 152    AND (bankadj_bankaccnt_id=<? value("bankaccntid") ?>)
 153 <? if exists("source") ?>
 154    AND ('AD' = <? value("source") ?>)
 155 <? endif ?>
 156 <? if exists("sourceid") ?>
 157    AND (bankadj_id = <? value("sourceid") ?>)
 158 <? endif ?>
 159  )
 160 ORDER BY sortdate;
 161 <? endif ?>