cof_rv_resumoplanofinanceiro

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

cof_identifier

text

2147483647

√

null

ad_client_id

numeric

√

null

ad_org_id

numeric

√

null

issotrx

text

2147483647

√

null

cof_type

text

2147483647

√

null

cof_c_planofinanceiro_id

int4

√

null

cof_c_planofinanceiro_parent_id

int4

√

null

value

text

2147483647

√

null

name

text

2147483647

√

null

character_entity.name

Implied Constraint

description

text

2147483647

√

null

accounttype

text

2147483647

√

null

c_doctypetarget_id

int4

√

null

ano

numeric

√

null

jan

numeric

√

null

fev

numeric

√

null

mar

numeric

√

null

abr

numeric

√

null

mai

numeric

√

null

jun

numeric

√

null

jul

numeric

√

null

ago

numeric

√

null

set

numeric

√

null

out

numeric

√

null

nov

numeric

√

null

dez

numeric

√

null

total

numeric

√

null

Relationships

Close relationships within degrees of separation

One
One implied

View Definition

SELECT crosstab.cof_identifier,
    crosstab.ad_client_id,
    crosstab.ad_org_id,
    crosstab.issotrx,
    crosstab.cof_type,
    crosstab.cof_c_planofinanceiro_id,
    crosstab.cof_c_planofinanceiro_parent_id,
    crosstab.value,
    crosstab.name,
    crosstab.description,
    crosstab.accounttype,
    crosstab.c_doctypetarget_id,
    crosstab.ano,
    COALESCE(crosstab."Jan", (0)::numeric) AS jan,
    COALESCE(crosstab."Fev", (0)::numeric) AS fev,
    COALESCE(crosstab."Mar", (0)::numeric) AS mar,
    COALESCE(crosstab."Abr", (0)::numeric) AS abr,
    COALESCE(crosstab."Mai", (0)::numeric) AS mai,
    COALESCE(crosstab."Jun", (0)::numeric) AS jun,
    COALESCE(crosstab."Jul", (0)::numeric) AS jul,
    COALESCE(crosstab."Ago", (0)::numeric) AS ago,
    COALESCE(crosstab."Set", (0)::numeric) AS set,
    COALESCE(crosstab."Out", (0)::numeric) AS "out",
    COALESCE(crosstab."Nov", (0)::numeric) AS nov,
    COALESCE(crosstab."Dez", (0)::numeric) AS dez,
    (((((((((((COALESCE(crosstab."Jan", (0)::numeric) + COALESCE(crosstab."Fev", (0)::numeric)) + COALESCE(crosstab."Mar", (0)::numeric)) + COALESCE(crosstab."Abr", (0)::numeric)) + COALESCE(crosstab."Mai", (0)::numeric)) + COALESCE(crosstab."Jun", (0)::numeric)) + COALESCE(crosstab."Jul", (0)::numeric)) + COALESCE(crosstab."Ago", (0)::numeric)) + COALESCE(crosstab."Set", (0)::numeric)) + COALESCE(crosstab."Out", (0)::numeric)) + COALESCE(crosstab."Nov", (0)::numeric)) + COALESCE(crosstab."Dez", (0)::numeric)) AS total
   FROM crosstab('SELECT
    ''FA'' || i.ad_client_id || i.ad_org_id || i.issotrx || i.cof_c_planofinanceiro_id  || date_part(''year''::text, i.dateinvoiced) || date_part(''month''::text, i.dateinvoiced) as cof_identifier,
    ''FA'' as cof_type,
    i.ad_client_id,
    i.ad_org_id,
    i.issotrx,
    pf.value,
    pf.name,
    pf.description,
    pf.accounttype,
    i.cof_c_planofinanceiro_id,
	(select tn.parent_id from ad_treenode tn left join ad_tree t on tn.ad_tree_id = t.ad_tree_id where tn.node_id = i.cof_c_planofinanceiro_id and t.ad_table_id =  1500570 ) as cof_c_planofinanceiro_parent_id,
    i.c_doctypetarget_id,
    date_part(''year''::text, i.dateinvoiced) AS ano,
    date_part(''month''::text, i.dateinvoiced) AS mesnro,
	sum(case when (select dt.DocBaseType from C_DocType dt where dt.c_doctype_id = i.c_doctypetarget_id) = ''API'' then i.grandtotal*-1
        else
            i.grandtotal
        end)
   FROM rv_c_invoice i
    LEFT JOIN COF_C_PlanoFinanceiro pf ON i.COF_C_PlanoFinanceiro_ID = pf.COF_C_PlanoFinanceiro_ID
    GROUP BY cof_identifier, i.ad_client_id, i.ad_org_id, cof_type, issotrx, pf.value, pf.name, pf.description, pf.accounttype, i.cof_c_planofinanceiro_id, cof_c_planofinanceiro_parent_id ,i.c_doctypetarget_id, ano, mesnro
  UNION
   SELECT
    ''PO'' || p.ad_client_id || p.ad_org_id || p.isreceipt || p.cof_c_planofinanceiro_id  || date_part(''year''::text, p.datetrx) || date_part(''month''::text, p.datetrx) as cof_identifier,
    ''PO'' as cof_type,
    p.ad_client_id,
    p.ad_org_id,
    p.isreceipt,
    pf.value,
    pf.name,
    pf.description,
    pf.accounttype,
    p.cof_c_planofinanceiro_id,
	(select tn.parent_id from ad_treenode tn left join ad_tree t on tn.ad_tree_id = t.ad_tree_id where tn.node_id = p.cof_c_planofinanceiro_id and t.ad_table_id =  1500570 ) as cof_c_planofinanceiro_parent_id,
    p.C_DocType_ID,
    date_part(''year''::text, p.datetrx) AS ano,
    date_part(''month''::text, p.datetrx) AS mesnro,
    sum(payamt)
   FROM brerp.rv_payment p
    LEFT JOIN COF_C_PlanoFinanceiro pf ON p.COF_C_PlanoFinanceiro_ID = pf.COF_C_PlanoFinanceiro_ID
    GROUP BY cof_identifier, p.ad_client_id, p.ad_org_id, cof_type, isreceipt, pf.value, pf.name, pf.description, pf.accounttype, p.cof_c_planofinanceiro_id, cof_c_planofinanceiro_parent_id, p.c_doctype_id, ano, mesnro
'::text, 'select m from generate_series(1,12) m'::text) crosstab(cof_identifier text, cof_type text, ad_client_id numeric, ad_org_id numeric, issotrx text, value text, name text, description text, accounttype text, cof_c_planofinanceiro_id integer, cof_c_planofinanceiro_parent_id integer, c_doctypetarget_id integer, ano numeric, "Jan" numeric, "Fev" numeric, "Mar" numeric, "Abr" numeric, "Mai" numeric, "Jun" numeric, "Jul" numeric, "Ago" numeric, "Set" numeric, "Out" numeric, "Nov" numeric, "Dez" numeric);

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views