git.roojs.org Git - Pman.Xtuple/blob - metasql/bankrec.checks.sql

   1 -- Group: bankrec
   2 -- Name: checks
   3 -- Notes: used by reconcileBankaccount
   4 --        Copyright (c) 1999-2012 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        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  UNION ALL
  82 SELECT sltrans_id AS id, 2 AS altid,
  83        COALESCE(bankrecitem_cleared, FALSE) AS cleared,
  84        sltrans_date AS transdate,
  85        sltrans_doctype AS doc_type,
  86        sltrans_docnumber AS doc_number,
  87        sltrans_notes AS notes,
  88        (SELECT currConcat(COALESCE(checkhead_curr_id, baseCurrId()))) AS doc_curr,
  89        COALESCE(bankrecitem_curr_rate, checkhead_curr_rate, currRate(bankaccnt_curr_id, sltrans_date)) AS doc_exchrate,
  90        sltrans_amount AS base_amount,
  91        CASE WHEN (bankaccnt_curr_id=checkhead_curr_id) THEN
  92          COALESCE( bankrecitem_amount,
  93                    checkhead_amount,
  94                    currToLocal(bankaccnt_curr_id, sltrans_amount, sltrans_date) )
  95             ELSE
  96          COALESCE( bankrecitem_amount,
  97                    currToLocal(bankaccnt_curr_id, sltrans_amount, sltrans_date) )
  98        END AS amount,
  99        sltrans_date AS sortdate,
 100        'uomratio' AS doc_exchrate_xtnumericrole,
 101        'curr' AS base_amount_xtnumericrole,
 102        'curr' AS amount_xtnumericrole
 103   FROM (bankaccnt CROSS JOIN sltrans)
 104               LEFT OUTER JOIN bankrecitem ON ((bankrecitem_source='SL')
 105                                           AND (bankrecitem_source_id=sltrans_id)
 106                                           AND (bankrecitem_bankrec_id=<? value("bankrecid") ?>)
 107                                           AND (bankrecitem_cleared))
 108               LEFT OUTER JOIN checkhead ON ((sltrans_doctype='CK')
 109                                         AND (sltrans_misc_id=checkhead_id))
 110  WHERE ((sltrans_accnt_id=bankaccnt_accnt_id)
 111    AND (NOT sltrans_rec)
 112    AND (NOT COALESCE(checkhead_void, false))
 113    AND (sltrans_amount > 0)
 114    AND (bankaccnt_id=<? value("bankaccntid") ?>) )
 115  UNION ALL
 116 SELECT bankadj_id AS id, 3 AS altid,
 117        COALESCE(bankrecitem_cleared, FALSE) AS cleared,
 118        bankadj_date AS transdate,
 119        'ADJ' AS doc_type,
 120        bankadj_docnumber AS doc_number,
 121        bankadjtype_name AS notes,
 122        (SELECT currConcat(bankadj_curr_id)) AS doc_curr,
 123        1.0 AS doc_exchrate,
 124        CASE WHEN(bankadjtype_iscredit=false) THEN (bankadj_amount * -1.0) ELSE bankadj_amount END AS base_amount,
 125        CASE WHEN(bankadjtype_iscredit=false) THEN (bankadj_amount * -1.0) ELSE bankadj_amount END AS amount,
 126        bankadj_date AS sortdate,
 127        'uomratio' AS doc_exchrate_xtnumericrole,
 128        'curr' AS base_amount_xtnumericrole,
 129        'curr' AS amount_xtnumericrole
 130   FROM (bankadjtype CROSS JOIN bankadj)
 131                LEFT OUTER JOIN bankrecitem ON ((bankrecitem_source='AD')
 132                                            AND (bankrecitem_source_id=bankadj_id)
 133                                            AND (bankrecitem_bankrec_id=<? value("bankrecid") ?>)
 134                                            AND (bankrecitem_cleared))
 135  WHERE ( (((bankadjtype_iscredit=true) AND (bankadj_amount > 0)) OR ((bankadjtype_iscredit=false) AND (bankadj_amount < 0)))
 136    AND (bankadj_bankadjtype_id=bankadjtype_id)
 137    AND (NOT bankadj_posted)
 138    AND (bankadj_bankaccnt_id=<? value("bankaccntid") ?>) )
 139 ORDER BY sortdate;
 140 <? endif ?>