Queries: Financial

Fact details

This view can assist in the reconciliation of facts, or as the basis of a Jasper financial report.

Base view

drop view if exists zz_scratch_factacct; -- cascade;

create or replace view zz_scratch_factacct as
SELECT
fa.ad_client_id,
fa.ad_org_id,
eval.accounttype,
eval.value,
eval.name,
fa.dateacct,
(COALESCE(fa.amtacctdr, (0)::numeric) - COALESCE(fa.amtacctcr, (0)::numeric)) AS amount,
eval.c_elementvalue_id AS account_id,
parent.value AS parentvalue,
parent.name AS parentname,
prod.value as product_value,
prod.name as product_name,
fa.ad_table_id, 
fa.record_id,
fa.line_id,
fa.description
--fa.*
FROM
(
   (
      (
         fact_acct fa
         JOIN c_elementvalue eval ON ((fa.account_id = eval.c_elementvalue_id))
         left join m_product prod on fa.m_product_id = prod.m_product_id
      )
      LEFT JOIN ad_treenode b ON
      (
         (
            (b.node_id = eval.c_elementvalue_id)
            AND (b.ad_client_id = fa.ad_client_id)
         )
      )
   )
   LEFT JOIN c_elementvalue parent ON ((parent.c_elementvalue_id = b.parent_id))
)
;


select * from zz_scratch_factacct;

--where 
	--fa.AD_CLIENT_ID= 1000000 and
	--fa.dateacct >='2015-05-09 00:00:00' and 
	--fa.dateacct <= '2015-05-09 00:00:00' and
	--eval.name = 'Cost of Sales' --and

Summary: Name

select name, sum(amount) *-1.00  as INCOME
	from zz_scratch_factacct
	where
	dateacct >='2015-05-01 00:00:00' and dateacct <= '2015-05-31 00:00:00' and
	accounttype ='R' and
	--($3=0 OR AD_ORG_ID=0 OR $5 IS NULL) and
	AD_CLIENT_ID= 1000000
	group by name
	HAVING (SUM(amount)) !=0
	Order by name;


select name, sum(amount) as EXPENSE
	from zz_scratch_factacct
	where
	dateacct >='2015-05-01 00:00:00' and dateacct <= '2015-05-31 00:00:00' and
	accounttype ='E' and
	--($3=0 OR AD_ORG_ID=$4 OR $5 IS NULL) and
	AD_CLIENT_ID= 1000000 and
	parentvalue = '200'
	group by name
	HAVING (SUM(amount)) !=0
	Order by name;

Summary: DateAcct

select 
	'Cost of Sales: I&E: ' || cast(a.dateacct as varchar(10)) as description, sum(a.amount)
from 
	zz_scratch_factacct a
where 
	a.AD_CLIENT_ID= 1000000 and
	a.dateacct >='2015-05-01 00:00:00' and 
	a.dateacct <= '2015-05-31 00:00:00' and
	a.name = 'Cost of Sales' --and
	--a.created >= '2015-06-08 15:00:00'
group by
	a.dateacct
union all
select 
	cast('Cost of Sales: I&E: *** Total ***' as varchar(80)) as description, sum(a.amount)
from 
	zz_scratch_factacct a
where 
	a.AD_CLIENT_ID= 1000000 and
	a.dateacct >='2015-05-01 00:00:00' and 
	a.dateacct <= '2015-05-31 00:00:00' and
	a.name = 'Cost of Sales' ---and
	--a.created >= '2015-06-08 15:00:00'
order by
	description
;

Detail

select
	a.created, a.dateacct, a.product_value, a.description, a.amount,
	a.ad_table_id, a.record_id
	--a.dateacct, a.amount
	--a.*
from 
	zz_scratch_factacct a
where 
	a.AD_CLIENT_ID= 1000000 and
	a.dateacct >='2015-05-01 00:00:00' and 
	a.dateacct <= '2015-05-31 00:00:00' and
	a.name = 'Cost of Sales' and
	a.created >= '2015-06-08 15:00:00'
order by
	a.dateacct, a.amount
;

Credits

Initial contribution by: nTier Software Services http://www.ntier.co.za