set client_min_messages = 'warning';


-- Copyright 2012 The LedgerSMB Core Team.  This file may be re-used in
-- accordance with the GNU GPL version 2 or at your option any later version.
-- Please see your included LICENSE.txt for details

BEGIN;

-- This holds general PNL type report definitions.  The idea is to gather them
-- here so that they share as many common types as possible.  Note that PNL
-- reports do not return total and summary lines.  These must be done by the
-- application handling this.

DROP FUNCTION IF EXISTS pnl__product(in_from_date date, in_to_date date, in_parts_id integer, in_business_units integer[]);
DROP TYPE IF EXISTS pnl_line CASCADE;
CREATE TYPE pnl_line AS (
    account_id int,
    account_number text,
    account_description text,
    account_type char,
    account_category char,
    gifi text,
    gifi_description text,
    contra boolean,
    amount numeric,
    heading_path int[]
);

CREATE OR REPLACE FUNCTION pnl__product(in_from_date date, in_to_date date, in_parts_id integer, in_business_units integer[], in_language text)
  RETURNS SETOF pnl_line LANGUAGE SQL AS
$$
WITH acc_meta AS (
  SELECT a.id, a.accno,
         coalesce(at.description, a.description) as description,
         CASE WHEN (SELECT value::int FROM defaults where setting_key = 'earn_id') IS NULL THEN aht.path
         ELSE array_splice_from((SELECT value::int FROM defaults
                             WHERE setting_key = 'earn_id'),aht.path)
         END AS path,
         a.category, 'A'::char as account_type, contra, a.gifi_accno,
         gifi.description as gifi_description
     FROM account a
    INNER JOIN account_heading_tree aht on a.heading = aht.id
     LEFT JOIN gifi ON a.gifi_accno = gifi.accno
     LEFT JOIN (SELECT trans_id, description
                  FROM account_translation
                 WHERE language_code =
                        coalesce($5,
                          (SELECT up.language
                             FROM user_preference up
                       INNER JOIN users ON up.id = users.id
                            WHERE users.username = SESSION_USER))) at
               ON a.id = at.trans_id
   WHERE array_splice_from((SELECT value::int FROM defaults
                             WHERE setting_key = 'earn_id'),aht.path)
                          IS NOT NULL
         -- legacy: earn_id not configured (yet)
         OR (NOT EXISTS (SELECT 1 FROM defaults
                         WHERE setting_key = 'earn_id'
                           AND value IS NOT NULL)
             AND category IN ('E', 'I'))
),
hdr_meta AS (
   SELECT aht.id, aht.accno,
          coalesce(at.description, aht.description) as description,
          CASE WHEN (SELECT value::int FROM defaults where setting_key = 'earn_id') IS NULL THEN aht.path
          ELSE array_splice_from((SELECT value::int FROM defaults
                              WHERE setting_key = 'earn_id'),aht.path)
          END AS path,
          ahc.derived_category as category,
          'H'::char as account_type, 'f'::boolean as contra
     FROM account_heading_tree aht
    INNER JOIN account_heading_derived_category ahc ON aht.id = ahc.id
    LEFT JOIN (SELECT trans_id, description
                 FROM account_translation
                WHERE language_code =
                       coalesce($5,
                         (SELECT up.language
                            FROM user_preference up
                      INNER JOIN users ON up.id = users.id
                           WHERE users.username = SESSION_USER))) at
              ON aht.id = at.trans_id
    WHERE ((SELECT value::int FROM defaults
                              WHERE setting_key = 'earn_id') IS NOT NULL
           AND array_splice_from((SELECT value::int FROM defaults
                              WHERE setting_key = 'earn_id'),aht.path)
                           IS NOT NULL)
          -- legacy: earn_id not configured; select headings belonging to
          --    selected accounts
          OR ((SELECT value::int FROM defaults
                              WHERE setting_key = 'earn_id') IS NULL
              AND EXISTS (SELECT 1 FROM acc_meta
                                  WHERE aht.id = ANY(acc_meta.path)))
),
acc_balance AS (
   WITH RECURSIVE bu_tree (id, parent, path) AS (
      SELECT id, null, row(array[id])::tree_record FROM business_unit
       WHERE id = any($4)
      UNION ALL
      SELECT bu.id, parent, row((path).t || bu.id)::tree_record
        FROM business_unit bu
        JOIN bu_tree ON bu.parent_id = bu_tree.id
   )
SELECT ac.chart_id AS id, sum(ac.amount) AS balance
     FROM acc_trans ac
     JOIN invoice i ON i.id = ac.invoice_id
     JOIN account_link l ON l.account_id = ac.chart_id
     JOIN ar ON ar.id = ac.trans_id
LEFT JOIN (select as_array(bu.path) as bu_ids, entry_id
             from business_unit_inv bui
             JOIN bu_tree bu ON bui.bu_id = bu.id
         GROUP BY entry_id) bui ON bui.entry_id = i.id
    WHERE i.parts_id = $3
          AND (ac.transdate >= $1 OR $1 IS NULL)
          AND (ac.transdate <= $2 OR $2 IS NULL)
          AND ar.approved
          AND l.description = 'IC_expense'
          AND ($4 is null or $4 = '{}' OR in_tree($4, bu_ids))
 GROUP BY ac.chart_id
   HAVING sum(ac.amount) <> 0.00
    UNION
   SELECT ac.chart_id,
          sum(i.sellprice * i.qty * (1 - coalesce(i.discount, 0)))
     FROM invoice i
     JOIN acc_trans ac ON ac.invoice_id = i.id
     JOIN ar ON ar.id = ac.trans_id
LEFT JOIN (select as_array(bu.path) as bu_ids, entry_id
             from business_unit_inv bui
             JOIN bu_tree bu ON bui.bu_id = bu.id
         GROUP BY entry_id) bui ON bui.entry_id = i.id
    WHERE i.parts_id = $3
          AND (ac.transdate >= $1 OR $1 IS NULL)
          AND (ac.transdate <= $2 OR $2 IS NULL)
          AND ar.approved
          AND ($4 is null or $4 = '{}' OR in_tree($4, bu_ids))
 GROUP BY ac.chart_id
   HAVING sum(i.sellprice * i.qty * (1 - coalesce(i.discount, 0))) <> 0.00
 ),
hdr_balance AS (
   select ahd.id, sum(balance) as balance
     FROM acc_balance ab
    INNER JOIN account acc ON ab.id = acc.id
    INNER JOIN account_heading_descendant ahd
            ON acc.heading = ahd.descendant_id
    GROUP BY ahd.id
)
   SELECT hm.id, hm.accno, hm.description, hm.account_type, hm.category,
          null::text as gifi, null::text as gifi_description, hm.contra,
          hb.balance, hm.path
     FROM hdr_meta hm
    INNER JOIN hdr_balance hb ON hm.id = hb.id
   UNION
   SELECT am.id, am.accno, am.description, am.account_type, am.category,
          gifi_accno as gifi, gifi_description, am.contra, ab.balance, am.path
     FROM acc_meta am
    INNER JOIN acc_balance ab on am.id = ab.id
$$;

DROP FUNCTION IF EXISTS  pnl__income_statement_accrual(in_from_date date, in_to_date date, in_ignore_yearend text, in_business_units integer[]);
CREATE OR REPLACE FUNCTION pnl__income_statement_accrual(in_from_date date, in_to_date date, in_ignore_yearend text, in_business_units integer[], in_language text)
  RETURNS SETOF pnl_line AS
$BODY$
WITH acc_meta AS (
  SELECT a.id, a.accno,
         coalesce(at.description, a.description) as description,
         CASE WHEN (SELECT value::int FROM defaults where setting_key = 'earn_id') IS NULL THEN aht.path
         ELSE array_splice_from((SELECT value::int FROM defaults
                             WHERE setting_key = 'earn_id'),aht.path)
         END AS path,
         a.category, 'A'::char as account_type, contra, a.gifi_accno,
         gifi.description as gifi_description
     FROM account a
    INNER JOIN account_heading_tree aht on a.heading = aht.id
     LEFT JOIN gifi ON a.gifi_accno = gifi.accno
     LEFT JOIN (SELECT trans_id, description
                  FROM account_translation
                 WHERE language_code =
                        coalesce($5,
                          (SELECT up.language
                             FROM user_preference up
                       INNER JOIN users ON up.id = users.id
                            WHERE users.username = SESSION_USER))) at
               ON a.id = at.trans_id
   WHERE array_splice_from((SELECT value::int FROM defaults
                             WHERE setting_key = 'earn_id'),aht.path)
                          IS NOT NULL
         -- legacy: earn_id not configured (yet)
         OR (NOT EXISTS (SELECT 1 FROM defaults
                         WHERE setting_key = 'earn_id'
                               AND value IS NOT NULL)
             AND category IN ('E', 'I'))
),
hdr_meta AS (
   SELECT aht.id, aht.accno,
          coalesce(at.description, aht.description) as description,
          CASE WHEN (SELECT value::int FROM defaults where setting_key = 'earn_id') IS NULL THEN aht.path
          ELSE array_splice_from((SELECT value::int FROM defaults
                              WHERE setting_key = 'earn_id'),aht.path)
          END AS path,
          ahc.derived_category as category,
          'H'::char as account_type, 'f'::boolean as contra
     FROM account_heading_tree aht
    INNER JOIN account_heading_derived_category ahc ON aht.id = ahc.id
    LEFT JOIN (SELECT trans_id, description
                 FROM account_translation
                WHERE language_code =
                       coalesce($5,
                         (SELECT up.language
                            FROM user_preference up
                      INNER JOIN users ON up.id = users.id
                           WHERE users.username = SESSION_USER))) at
              ON aht.id = at.trans_id
    WHERE ((SELECT value::int FROM defaults
                              WHERE setting_key = 'earn_id') IS NOT NULL
           AND array_splice_from((SELECT value::int FROM defaults
                              WHERE setting_key = 'earn_id'),aht.path)
                           IS NOT NULL)
          -- legacy: earn_id not configured; select headings belonging to
          --    selected accounts
          OR ((SELECT value::int FROM defaults
                              WHERE setting_key = 'earn_id') IS NULL
              AND EXISTS (SELECT 1 FROM acc_meta
                                  WHERE aht.id = ANY(acc_meta.path)))
),
acc_balance AS (
   WITH RECURSIVE bu_tree (id, parent, path) AS (
      SELECT id, null, row(array[id])::tree_record FROM business_unit
       WHERE id = any($4)
      UNION ALL
      SELECT bu.id, parent, row((path).t || bu.id)::tree_record
        FROM business_unit bu
        JOIN bu_tree ON bu.parent_id = bu_tree.id
   )
   SELECT ac.chart_id AS id, sum(ac.amount) AS balance
     FROM acc_trans ac
    INNER JOIN tx_report gl ON ac.trans_id = gl.id AND gl.approved
     LEFT JOIN (SELECT array_agg(path) AS bu_ids, entry_id
                  FROM business_unit_ac buac
                 INNER JOIN bu_tree ON bu_tree.id = buac.bu_id
                 GROUP BY buac.entry_id) bu
          ON (ac.entry_id = bu.entry_id)
    WHERE ac.approved
          AND ($1 IS NULL OR ac.transdate >= $1)
          AND ($2 IS NULL OR ac.transdate <= $2)
          AND ($4 = '{}'
              OR $4 is null or in_tree($4, bu_ids))
           AND ($3 = 'none'
               OR ($3 = 'all'
                   AND NOT EXISTS (SELECT * FROM yearend
                                    WHERE trans_id = gl.id))
               OR ($3 = 'last'
                   AND NOT EXISTS (SELECT 1 FROM yearend
                                   HAVING max(trans_id) = gl.id))
              )
   GROUP BY ac.chart_id
     HAVING sum(ac.amount) <> 0.00
 ),
hdr_balance AS (
   select ahd.id, sum(balance) as balance
     FROM acc_balance ab
    INNER JOIN account acc ON ab.id = acc.id
    INNER JOIN account_heading_descendant ahd
            ON acc.heading = ahd.descendant_id
    GROUP BY ahd.id
)
   SELECT hm.id, hm.accno, hm.description, hm.account_type, hm.category,
          null::text as gifi, null::text as gifi_description, hm.contra,
          hb.balance, hm.path
     FROM hdr_meta hm
    INNER JOIN hdr_balance hb ON hm.id = hb.id
   UNION
   SELECT am.id, am.accno, am.description, am.account_type, am.category,
          gifi_accno as gifi, gifi_description, am.contra, ab.balance, am.path
     FROM acc_meta am
    INNER JOIN acc_balance ab on am.id = ab.id
$BODY$
  LANGUAGE sql;


DROP FUNCTION IF EXISTS pnl__income_statement_cash(in_from_date date, in_to_date date, in_ignore_yearend text, in_business_units integer[]);
CREATE OR REPLACE FUNCTION pnl__income_statement_cash(in_from_date date, in_to_date date, in_ignore_yearend text, in_business_units integer[], in_language text)
  RETURNS SETOF pnl_line LANGUAGE SQL AS
$$
WITH acc_meta AS (
  SELECT a.id, a.accno,
         coalesce(at.description, a.description) as description,
         CASE WHEN (SELECT value::int FROM defaults where setting_key = 'earn_id') IS NULL THEN aht.path
         ELSE array_splice_from((SELECT value::int FROM defaults
                             WHERE setting_key = 'earn_id'),aht.path)
         END AS path,
         a.category, 'A'::char as account_type, contra, a.gifi_accno,
         gifi.description as gifi_description
     FROM account a
    INNER JOIN account_heading_tree aht on a.heading = aht.id
     LEFT JOIN gifi ON a.gifi_accno = gifi.accno
     LEFT JOIN (SELECT trans_id, description
                  FROM account_translation
                 WHERE language_code =
                        coalesce($5,
                          (SELECT up.language
                             FROM user_preference up
                       INNER JOIN users ON up.id = users.id
                            WHERE users.username = SESSION_USER))) at
               ON a.id = at.trans_id
   WHERE array_splice_from((SELECT value::int FROM defaults
                             WHERE setting_key = 'earn_id'),aht.path)
                          IS NOT NULL
         -- legacy: earn_id not configured (yet)
         OR (NOT EXISTS (SELECT 1 FROM defaults
                         WHERE setting_key = 'earn_id'
                           AND value IS NOT NULL)
             AND category IN ('E', 'I'))
),
hdr_meta AS (
   SELECT aht.id, aht.accno,
          coalesce(at.description, aht.description) as description,
          CASE WHEN (SELECT value::int FROM defaults where setting_key = 'earn_id') IS NULL THEN aht.path
          ELSE array_splice_from((SELECT value::int FROM defaults
                              WHERE setting_key = 'earn_id'),aht.path)
          END AS path,
          ahc.derived_category as category,
          'H'::char as account_type, 'f'::boolean as contra
     FROM account_heading_tree aht
    INNER JOIN account_heading_derived_category ahc ON aht.id = ahc.id
    LEFT JOIN (SELECT trans_id, description
                 FROM account_translation
                WHERE language_code =
                       coalesce($5,
                         (SELECT up.language
                            FROM user_preference up
                      INNER JOIN users ON up.id = users.id
                           WHERE users.username = SESSION_USER))) at
              ON aht.id = at.trans_id
    WHERE ((SELECT value::int FROM defaults
                              WHERE setting_key = 'earn_id') IS NOT NULL
           AND array_splice_from((SELECT value::int FROM defaults
                              WHERE setting_key = 'earn_id'),aht.path)
                           IS NOT NULL)
          -- legacy: earn_id not configured; select headings belonging to
          --    selected accounts
          OR ((SELECT value::int FROM defaults
                              WHERE setting_key = 'earn_id') IS NULL
              AND EXISTS (SELECT 1 FROM acc_meta
                                  WHERE aht.id = ANY(acc_meta.path)))
),
acc_balance AS (
WITH RECURSIVE bu_tree (id, parent, path) AS (
      SELECT id, null, row(array[id])::tree_record FROM business_unit
       WHERE id = any($4)
      UNION ALL
      SELECT bu.id, parent, row((path).t || bu.id)::tree_record
        FROM business_unit bu
        JOIN bu_tree ON bu.parent_id = bu_tree.id
)
   SELECT ac.chart_id AS id, sum(ac.amount * ca.portion) AS balance
     FROM acc_trans ac
     JOIN tx_report gl ON ac.trans_id = gl.id AND gl.approved
     JOIN (SELECT id, sum(portion) as portion
             FROM cash_impact ca
            WHERE ($1 IS NULL OR ca.transdate >= $1)
                  AND ($2 IS NULL OR ca.transdate <= $2)
           GROUP BY id
          ) ca ON gl.id = ca.id
LEFT JOIN (select array_agg(path) as bu_ids, entry_id
             FROM business_unit_ac buac
             JOIN bu_tree ON bu_tree.id = buac.bu_id
         GROUP BY entry_id) bu
          ON (ac.entry_id = bu.entry_id)
    WHERE ac.approved
          AND ($4 = '{}'
              OR $4 is null or in_tree($4, bu_ids))
          AND ($3 = 'none'
               OR ($3 = 'all'
                   AND NOT EXISTS (SELECT * FROM yearend WHERE trans_id = gl.id
                   ))
               OR ($3 = 'last'
                   AND NOT EXISTS (SELECT 1 FROM yearend
                                   HAVING max(trans_id) = gl.id))
              )
 GROUP BY ac.chart_id
   HAVING sum(ac.amount * ca.portion) <> 0.00
 ),
hdr_balance AS (
   select ahd.id, sum(balance) as balance
     FROM acc_balance ab
    INNER JOIN account acc ON ab.id = acc.id
    INNER JOIN account_heading_descendant ahd
            ON acc.heading = ahd.descendant_id
    GROUP BY ahd.id
)
   SELECT hm.id, hm.accno, hm.description, hm.account_type, hm.category,
          null::text as gifi, null::text as gifi_description, hm.contra,
          hb.balance, hm.path
     FROM hdr_meta hm
    INNER JOIN hdr_balance hb ON hm.id = hb.id
   UNION
   SELECT am.id, am.accno, am.description, am.account_type, am.category,
          gifi_accno as gifi, gifi_description, am.contra, ab.balance, am.path
     FROM acc_meta am
    INNER JOIN acc_balance ab on am.id = ab.id
$$;

DROP FUNCTION IF EXISTS pnl__invoice(in_id integer);
CREATE OR REPLACE FUNCTION pnl__invoice(in_id integer, in_language text)
  RETURNS SETOF pnl_line LANGUAGE SQL AS
$$
WITH acc_meta AS (
  SELECT a.id, a.accno,
         coalesce(at.description, a.description) as description,
         CASE WHEN (SELECT value::int FROM defaults where setting_key = 'earn_id') IS NULL THEN aht.path
         ELSE array_splice_from((SELECT value::int FROM defaults
                             WHERE setting_key = 'earn_id'),aht.path)
         END AS path,
         a.category, 'A'::char as account_type, contra, a.gifi_accno,
         gifi.description as gifi_description
     FROM account a
    INNER JOIN account_heading_tree aht on a.heading = aht.id
     LEFT JOIN gifi ON a.gifi_accno = gifi.accno
     LEFT JOIN (SELECT trans_id, description
                  FROM account_translation
                 WHERE language_code =
                        coalesce($2,
                          (SELECT up.language
                             FROM user_preference up
                       INNER JOIN users ON up.id = users.id
                            WHERE users.username = SESSION_USER))) at
               ON a.id = at.trans_id
   WHERE array_splice_from((SELECT value::int FROM defaults
                             WHERE setting_key = 'earn_id'),aht.path)
                          IS NOT NULL
         -- legacy: earn_id not configured (yet)
         OR (NOT EXISTS (SELECT 1 FROM defaults
                         WHERE setting_key = 'earn_id'
                           AND value IS NOT NULL)
             AND category IN ('E', 'I'))
),
hdr_meta AS (
   SELECT aht.id, aht.accno,
          coalesce(at.description, aht.description) as description,
          CASE WHEN (SELECT value::int FROM defaults where setting_key = 'earn_id') IS NULL THEN aht.path
          ELSE array_splice_from((SELECT value::int FROM defaults
                              WHERE setting_key = 'earn_id'),aht.path)
          END AS path,
          ahc.derived_category as category,
          'H'::char as account_type, 'f'::boolean as contra
     FROM account_heading_tree aht
    INNER JOIN account_heading_derived_category ahc ON aht.id = ahc.id
    LEFT JOIN (SELECT trans_id, description
                 FROM account_translation
                WHERE language_code =
                       coalesce($2,
                         (SELECT up.language
                            FROM user_preference up
                      INNER JOIN users ON up.id = users.id
                           WHERE users.username = SESSION_USER))) at
              ON aht.id = at.trans_id
    WHERE ((SELECT value::int FROM defaults
                              WHERE setting_key = 'earn_id') IS NOT NULL
           AND array_splice_from((SELECT value::int FROM defaults
                              WHERE setting_key = 'earn_id'),aht.path)
                           IS NOT NULL)
          -- legacy: earn_id not configured; select headings belonging to
          --    selected accounts
          OR ((SELECT value::int FROM defaults
                              WHERE setting_key = 'earn_id') IS NULL
              AND EXISTS (SELECT 1 FROM acc_meta
                                  WHERE aht.id = ANY(acc_meta.path)))
),
acc_balance AS (
SELECT ac.chart_id AS id, sum(ac.amount) AS balance
  FROM acc_trans ac
 WHERE ac.approved AND ac.trans_id = $1
 GROUP BY ac.chart_id
 ),
hdr_balance AS (
   select ahd.id, sum(balance) as balance
     FROM acc_balance ab
    INNER JOIN account acc ON ab.id = acc.id
    INNER JOIN account_heading_descendant ahd
            ON acc.heading = ahd.descendant_id
    GROUP BY ahd.id
)
   SELECT hm.id, hm.accno, hm.description, hm.account_type, hm.category,
          null::text as gifi, null::text as gifi_description, hm.contra,
          hb.balance, hm.path
     FROM hdr_meta hm
    INNER JOIN hdr_balance hb ON hm.id = hb.id
   UNION
   SELECT am.id, am.accno, am.description, am.account_type, am.category,
          gifi_accno as gifi, gifi_description, am.contra, ab.balance, am.path
     FROM acc_meta am
    INNER JOIN acc_balance ab on am.id = ab.id
$$;

DROP FUNCTION IF EXISTS pnl__customer(in_id integer, in_from_date date, in_to_date date);
CREATE OR REPLACE FUNCTION pnl__customer(in_id integer, in_from_date date, in_to_date date, in_language text)
  RETURNS SETOF pnl_line LANGUAGE SQL AS
$$
WITH acc_meta AS (
  SELECT a.id, a.accno,
         coalesce(at.description, a.description) as description,
         CASE WHEN (SELECT value::int FROM defaults where setting_key = 'earn_id') IS NULL THEN aht.path
         ELSE array_splice_from((SELECT value::int FROM defaults
                             WHERE setting_key = 'earn_id'),aht.path)
         END AS path,
         a.category, 'A'::char as account_type, contra, a.gifi_accno,
         gifi.description as gifi_description
     FROM account a
    INNER JOIN account_heading_tree aht on a.heading = aht.id
     LEFT JOIN gifi ON a.gifi_accno = gifi.accno
     LEFT JOIN (SELECT trans_id, description
                  FROM account_translation
                 WHERE language_code =
                        coalesce($4,
                          (SELECT up.language
                             FROM user_preference up
                       INNER JOIN users ON up.id = users.id
                            WHERE users.username = SESSION_USER))) at
               ON a.id = at.trans_id
   WHERE array_splice_from((SELECT value::int FROM defaults
                             WHERE setting_key = 'earn_id'),aht.path)
                          IS NOT NULL
         -- legacy: earn_id not configured (yet)
         OR (NOT EXISTS (SELECT 1 FROM defaults
                         WHERE setting_key = 'earn_id'
                           AND value IS NOT NULL)
             AND category IN ('E', 'I'))
),
hdr_meta AS (
   SELECT aht.id, aht.accno,
          coalesce(at.description, aht.description) as description,
          CASE WHEN (SELECT value::int FROM defaults where setting_key = 'earn_id') IS NULL THEN aht.path
          ELSE array_splice_from((SELECT value::int FROM defaults
                              WHERE setting_key = 'earn_id'),aht.path)
          END AS path,
          ahc.derived_category as category,
          'H'::char as account_type, 'f'::boolean as contra
     FROM account_heading_tree aht
    INNER JOIN account_heading_derived_category ahc ON aht.id = ahc.id
    LEFT JOIN (SELECT trans_id, description
                 FROM account_translation
                WHERE language_code =
                       coalesce($4,
                         (SELECT up.language
                            FROM user_preference up
                      INNER JOIN users ON up.id = users.id
                           WHERE users.username = SESSION_USER))) at
              ON aht.id = at.trans_id
    WHERE ((SELECT value::int FROM defaults
                              WHERE setting_key = 'earn_id') IS NOT NULL
           AND array_splice_from((SELECT value::int FROM defaults
                              WHERE setting_key = 'earn_id'),aht.path)
                           IS NOT NULL)
          -- legacy: earn_id not configured; select headings belonging to
          --    selected accounts
          OR ((SELECT value::int FROM defaults
                              WHERE setting_key = 'earn_id') IS NULL
              AND EXISTS (SELECT 1 FROM acc_meta
                                  WHERE aht.id = ANY(acc_meta.path)))
),
acc_balance AS (
WITH gl (id) AS
 ( SELECT id FROM ap WHERE approved is true AND entity_credit_account = $1
UNION ALL
   SELECT id FROM ar WHERE approved is true AND entity_credit_account = $1
)
SELECT ac.chart_id AS id, sum(ac.amount) AS balance
  FROM acc_trans ac
  JOIN gl ON ac.trans_id = gl.id
 WHERE ac.approved is true
          AND ($2 IS NULL OR ac.transdate >= $2)
          AND ($3 IS NULL OR ac.transdate <= $3)
 GROUP BY ac.chart_id
   HAVING sum(ac.amount) <> 0.00
 ),
hdr_balance AS (
   select ahd.id, sum(balance) as balance
     FROM acc_balance ab
    INNER JOIN account acc ON ab.id = acc.id
    INNER JOIN account_heading_descendant ahd
            ON acc.heading = ahd.descendant_id
    GROUP BY ahd.id
)
   SELECT hm.id, hm.accno, hm.description, hm.account_type, hm.category,
          null::text as gifi, null::text as gifi_description, hm.contra,
          hb.balance, hm.path
     FROM hdr_meta hm
    INNER JOIN hdr_balance hb ON hm.id = hb.id
   UNION
   SELECT am.id, am.accno, am.description, am.account_type, am.category,
          gifi_accno as gifi, gifi_description, am.contra, ab.balance, am.path
     FROM acc_meta am
    INNER JOIN acc_balance ab on am.id = ab.id
$$;

update defaults set value = 'yes' where setting_key = 'module_load_ok';

COMMIT;