git.roojs.org Git - Pman.Xtuple/blob - metasql/gltrans_stock.bydate.sql

   1 SELECT
   2     transdate,
   3     glamount,
   4     stamount,
   5     diff
   6
   7 FROM
   8 (
   9
  10     SELECT
  11         transdate,
  12         SUM(glamount) AS glamount,
  13         SUM(stamount) AS stamount,
  14         SUM(glamount + stamount) AS diff
  15
  16     FROM
  17     (
  18         SELECT
  19             gltrans_date::date AS transdate,
  20             COALESCE(SUM(gltrans_amount),0) AS glamount,
  21             0 AS stamount
  22         FROM
  23             gltrans
  24         WHERE
  25             gltrans_accnt_id = (SELECT costcat_asset_accnt_id FROM costcat LIMIT 1)
  26             AND
  27             gltrans_doctype  NOT IN ('PO', 'VO') -- ignore the PO
  28             AND
  29             NOT gltrans_deleted
  30             AND
  31             split_part(gltrans_docnumber, '-', 1) != 'COS'
  32             AND
  33             strpos(gltrans_docnumber, 'JE-IR-LANDCOST') = 0
  34
  35         GROUP BY transdate
  36
  37         UNION ALL
  38
  39         SELECT
  40             invhist_transdate::date AS transdate,
  41             0 AS glamount,
  42             COALESCE( SUM(invfifo_landedunitcost * invdetail_qty), 0) AS stamount
  43         FROM
  44             invdetailview
  45         WHERE
  46             invfifo_void = 0
  47             AND
  48             invhist_transtype NOT IN ('RP', 'RL') -- ignore the PO
  49         GROUP BY transdate
  50
  51     ) as x
  52     WHERE transdate::date >= fetchMetricText('invfifo_start_date')::date
  53     GROUP BY transdate
  54     ORDER BY transdate ASC
  55 ) y
  56 WHERE
  57     ABS(diff) > 1