1
-- View: budget_entries_report
3
-- DROP VIEW budget_entries_report;
5
CREATE OR REPLACE VIEW budget_entries_report AS
6
SELECT min(a.id) AS id,
7
count(DISTINCT a.id) AS nbr,
8
to_char(a.date::timestamp with time zone, 'YYYY'::text) AS year,
9
to_char(a.date::timestamp with time zone, 'MM'::text) AS month,
12
cbl.crossovered_budget_id,
13
cbl.general_budget_id,
14
sum(a.debit - a.credit) AS amount,
15
sum(a.quantity) AS unit_amount,
16
round(sum(cbl.planned_amount) / count(DISTINCT a.id)::numeric, 2) AS planned_amount,
17
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
18
FROM account_move_line a, crossovered_budget cb, crossovered_budget_lines cbl, account_budget_post abp, res_company rc
19
WHERE abp.id = cbl.general_budget_id AND cb.id = cbl.crossovered_budget_id
20
AND a.date >= cbl.date_from AND a.date <= cbl.date_to AND rc.id = cbl.company_id
21
AND (EXISTS ( SELECT 'X'
22
FROM account_budget_rel abr
23
WHERE abr.budget_id = abp.id AND a.account_id = abr.account_id))
24
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
27
SELECT min(cbl.id) AS id,
29
to_char(cbl.date_from::timestamp with time zone, 'YYYY'::text) AS year,
30
to_char(cbl.date_from::timestamp with time zone, 'MM'::text) AS month,
33
cbl.crossovered_budget_id,
34
cbl.general_budget_id,
37
sum(cbl.planned_amount) AS planned_amount,
39
FROM crossovered_budget cb, crossovered_budget_lines cbl, account_budget_post abp, res_company rc
40
WHERE abp.id = cbl.general_budget_id AND cb.id = cbl.crossovered_budget_id AND rc.id = cbl.company_id
41
AND (not EXISTS ( SELECT 'X'
42
FROM account_move_line aml, account_budget_rel abr
43
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))
44
GROUP BY to_char(cbl.date_from::timestamp with time zone, 'YYYY'::text), to_char(cbl.date_from::timestamp with time zone, 'MM'::text),
45
cbl.company_id, rc.currency_id, cbl.crossovered_budget_id, cbl.general_budget_id;
47
ALTER TABLE budget_entries_report