1
-- View: budget_entries_report
4
CREATE OR REPLACE VIEW budget_entries_report AS
5
SELECT min(a.id) AS id,
6
count(DISTINCT a.id) AS nbr,
7
to_char(a.date::timestamp with time zone, 'YYYY'::text) AS year,
8
to_char(a.date::timestamp with time zone, 'MM'::text) AS month,
11
cbl.crossovered_budget_id,
12
cbl.general_budget_id,
13
sum(a.debit - a.credit) AS amount,
14
sum(a.quantity) AS unit_amount,
15
round(sum(cbl.planned_amount) / count(DISTINCT a.id)::numeric, 2) AS planned_amount,
16
case when round(sum(cbl.planned_amount) / count(DISTINCT a.id)::numeric, 2) = 0 then 0 else round(sum(a.debit - a.credit) / (round(sum(cbl.planned_amount) / count(DISTINCT a.id)::numeric, 2) / 100::numeric), 0) end AS variance
17
FROM account_move_line a, crossovered_budget cb, crossovered_budget_lines cbl, account_budget_post abp, res_company rc
18
WHERE abp.id = cbl.general_budget_id AND cb.id = cbl.crossovered_budget_id
19
AND a.date >= cbl.date_from AND a.date <= cbl.date_to AND rc.id = cbl.company_id
20
AND (EXISTS ( SELECT 'X'
21
FROM account_budget_rel abr
22
WHERE abr.budget_id = abp.id AND a.id = abr.account_id))
23
GROUP BY to_char(a.date::timestamp with time zone, 'YYYY'::text), to_char(a.date::timestamp with time zone, 'MM'::text), a.company_id, rc.currency_id, a.account_id, cbl.crossovered_budget_id, cbl.general_budget_id
26
SELECT min(cbl.id) AS id,
28
to_char(cbl.date_from::timestamp with time zone, 'YYYY'::text) AS year,
29
to_char(cbl.date_from::timestamp with time zone, 'MM'::text) AS month,
32
cbl.crossovered_budget_id,
33
cbl.general_budget_id,
36
sum(cbl.planned_amount) AS planned_amount,
38
FROM crossovered_budget cb, crossovered_budget_lines cbl, account_budget_post abp, res_company rc
39
WHERE abp.id = cbl.general_budget_id AND cb.id = cbl.crossovered_budget_id AND rc.id = cbl.company_id
40
AND (not EXISTS ( SELECT 'X'
41
FROM account_move_line aml, account_budget_rel abr
42
WHERE abr.budget_id = abp.id AND aml.account_id = abr.account_id AND aml.date >= cbl.date_from AND aml.date <= cbl.date_to))
43
GROUP BY to_char(cbl.date_from::timestamp with time zone, 'YYYY'::text), to_char(cbl.date_from::timestamp with time zone, 'MM'::text),
44
cbl.company_id, rc.currency_id, cbl.crossovered_budget_id, cbl.general_budget_id;