1
SELECT fact_acct.ad_client_id,
2
fact_acct.ad_org_id,
3
fact_acct.c_acctschema_id,
4
fact_acct.account_id,
5
firstof((fact_acct.dateacct)::timestamp with time zone, 'DD'::character varying) AS dateacct,
6
fact_acct.c_period_id,
7
fact_acct.gl_category_id,
8
fact_acct.gl_budget_id,
9
fact_acct.c_tax_id,
10
fact_acct.m_locator_id,
11
fact_acct.postingtype,
12
fact_acct.c_currency_id,
13
sum(fact_acct.amtsourcedr) AS amtsourcedr,
14
sum(fact_acct.amtsourcecr) AS amtsourcecr,
15
sum((fact_acct.amtsourcedr - fact_acct.amtsourcecr)) AS amtsource,
16
sum(fact_acct.amtacctdr) AS amtacctdr,
17
sum(fact_acct.amtacctcr) AS amtacctcr,
18
sum((fact_acct.amtacctdr - fact_acct.amtacctcr)) AS amtacct,
19
CASE
20
WHEN (sum((fact_acct.amtsourcedr - fact_acct.amtsourcecr)) = (0)::numeric) THEN (0)::numeric
21
ELSE (sum((fact_acct.amtacctdr - fact_acct.amtacctcr)) / sum((fact_acct.amtsourcedr - fact_acct.amtsourcecr)))
22
END AS rate,
23
fact_acct.m_product_id,
24
fact_acct.c_bpartner_id,
25
fact_acct.ad_orgtrx_id,
26
fact_acct.c_locfrom_id,
27
fact_acct.c_locto_id,
28
fact_acct.c_salesregion_id,
29
fact_acct.c_project_id,
30
fact_acct.c_campaign_id,
31
fact_acct.c_activity_id,
32
fact_acct.user1_id,
33
fact_acct.user2_id,
34
fact_acct.a_asset_id,
35
ev.value AS accountvalue,
36
ev1.value AS cof_user1value,
37
ev1.name AS cof_user1name,
38
ev2.value AS cof_user2value,
39
ev2.name AS cof_user2name,
40
evparent.value AS cof_parentacctvalue,
41
evparent.name AS cof_parentacctname
42
FROM (((((fact_acct
43
JOIN c_elementvalue ev ON ((fact_acct.account_id = ev.c_elementvalue_id)))
44
LEFT JOIN ad_treenode n ON ((ev.c_elementvalue_id = n.node_id)))
45
LEFT JOIN c_elementvalue evparent ON ((n.parent_id = evparent.c_elementvalue_id)))
46
JOIN c_elementvalue ev1 ON ((fact_acct.user1_id = ev1.c_elementvalue_id)))
47
JOIN c_elementvalue ev2 ON ((fact_acct.user2_id = ev2.c_elementvalue_id)))
48
GROUP BY fact_acct.ad_client_id, fact_acct.ad_org_id, fact_acct.c_acctschema_id, fact_acct.account_id, (firstof((fact_acct.dateacct)::timestamp with time zone, 'DD'::character varying)), fact_acct.c_period_id, fact_acct.gl_category_id, fact_acct.gl_budget_id, fact_acct.c_tax_id, fact_acct.m_locator_id, fact_acct.postingtype, fact_acct.c_currency_id, fact_acct.m_product_id, fact_acct.c_bpartner_id, fact_acct.ad_orgtrx_id, fact_acct.c_locfrom_id, fact_acct.c_locto_id, fact_acct.c_salesregion_id, fact_acct.c_project_id, fact_acct.c_campaign_id, fact_acct.c_activity_id, fact_acct.user1_id, fact_acct.user2_id, fact_acct.a_asset_id, ev.value, ev1.value, ev1.name, ev2.value, ev2.name, evparent.value, evparent.name;