rv_fact_adaxa

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

ad_client_id

numeric

√

null

ad_org_id

numeric

√

null

clientname

varchar

√

null

dateacct

timestamp

29,6

√

null

bsdate

timestamp

29,6

√

null

bsmth

text

2147483647

√

null

fyear

text

2147483647

√

null

fymth

text

2147483647

√

null

table_name

varchar

√

null

record_id

numeric

√

null

budname

varchar

√

null

taxname

varchar

√

null

act_bud

text

2147483647

√

null

currency

bpchar

√

null

amtsce

numeric

√

null

amtacct

numeric

√

null

pl$act

numeric

√

null

bs$act

numeric

√

null

pl$bud

numeric

√

null

uomname

varchar

√

null

qty

numeric

√

null

projectname

varchar

√

null

description

varchar

255

√

null

orgname

varchar

√

null

ac_code

varchar

√

null

ac_name

varchar

120

√

null

account_name

text

2147483647

√

null

bstypename

text

2147483647

√

null

pltypename

text

2147483647

√

null

ac_group

text

2147483647

√

null

bsgroup

text

2147483647

√

null

plgroup

text

2147483647

√

null

plbs

text

2147483647

√

null

na_eq

text

2147483647

√

null

bpcode

varchar

√

null

bpname

varchar

120

√

null

bpartnercodeandname

text

2147483647

√

null

bpgroupname

varchar

√

null

repname

varchar

√

null

prodcode

varchar

510

√

null

prodname

varchar

255

√

null

product

text

2147483647

√

null

prodcategory

varchar

√

null

Relationships

Close relationships within degrees of separation

View Definition

SELECT f.ad_client_id,
    f.ad_org_id,
    ( SELECT cli.name
           FROM ad_client cli
          WHERE (cli.ad_client_id = f.ad_client_id)) AS clientname,
    f.dateacct,
        CASE
            WHEN (f.dateacct < ( SELECT clinfo.fy_startdate
               FROM ad_clientinfo clinfo
              WHERE (clinfo.ad_client_id = f.ad_client_id))) THEN (( SELECT ((clinfo.fy_startdate)::timestamp with time zone - (1)::numeric)
               FROM ad_clientinfo clinfo
              WHERE (clinfo.ad_client_id = f.ad_client_id)))::timestamp without time zone
            ELSE f.dateacct
        END AS bsdate,
        CASE
            WHEN (f.dateacct < ( SELECT clinfo.fy_startdate
               FROM ad_clientinfo clinfo
              WHERE (clinfo.ad_client_id = f.ad_client_id))) THEN ( SELECT to_char(((clinfo.fy_startdate)::timestamp with time zone - (1)::numeric), 'YYYY-MM'::text) AS to_char
               FROM ad_clientinfo clinfo
              WHERE (clinfo.ad_client_id = f.ad_client_id))
            ELSE to_char(f.dateacct, 'YYYY-MM'::text)
        END AS bsmth,
    to_char(((f.dateacct)::timestamp with time zone + (184)::numeric), '"FY"YYYY'::text) AS fyear,
    to_char(f.dateacct, 'YYYY-MM'::text) AS fymth,
    ( SELECT t.name
           FROM ad_table t
          WHERE (t.ad_table_id = f.ad_table_id)) AS table_name,
    f.record_id,
    ( SELECT gl.name
           FROM gl_budget gl
          WHERE (gl.gl_budget_id = f.gl_budget_id)) AS budname,
    ( SELECT tax.name
           FROM c_tax tax
          WHERE (tax.c_tax_id = f.c_tax_id)) AS taxname,
        CASE f.postingtype
            WHEN 'A'::bpchar THEN 'Actual'::text
            WHEN 'B'::bpchar THEN 'Budget'::text
            WHEN 'E'::bpchar THEN 'Encumbrance'::text
            WHEN 'S'::bpchar THEN 'Statistic'::text
            ELSE 'Other'::text
        END AS act_bud,
    ( SELECT c.iso_code
           FROM c_currency c
          WHERE (c.c_currency_id = f.c_currency_id)) AS currency,
    (f.amtsourcedr - f.amtsourcecr) AS amtsce,
    (f.amtacctdr - f.amtacctcr) AS amtacct,
        CASE
            WHEN (f.postingtype = 'B'::bpchar) THEN (0)::numeric
            ELSE (f.amtacctcr - f.amtacctdr)
        END AS "pl$act",
        CASE
            WHEN (f.postingtype = 'B'::bpchar) THEN (0)::numeric
            WHEN (ev.accounttype = 'A'::bpchar) THEN (f.amtacctdr - f.amtacctcr)
            WHEN (ev.accounttype = 'L'::bpchar) THEN (f.amtacctdr - f.amtacctcr)
            WHEN (ev.accounttype = 'O'::bpchar) THEN (f.amtacctcr - f.amtacctdr)
            WHEN (ev.accounttype = 'E'::bpchar) THEN (f.amtacctcr - f.amtacctdr)
            WHEN (ev.accounttype = 'R'::bpchar) THEN (f.amtacctcr - f.amtacctdr)
            ELSE (0)::numeric
        END AS "bs$act",
        CASE
            WHEN (f.postingtype = 'A'::bpchar) THEN (0)::numeric
            WHEN (ev.accounttype = 'E'::bpchar) THEN (f.amtacctcr - f.amtacctdr)
            WHEN (ev.accounttype = 'R'::bpchar) THEN (f.amtacctcr - f.amtacctdr)
            ELSE (0)::numeric
        END AS "pl$bud",
    ( SELECT um.uomsymbol
           FROM c_uom um
          WHERE (um.c_uom_id = f.c_uom_id)) AS uomname,
    f.qty,
    ( SELECT prj.name
           FROM c_project prj
          WHERE (prj.c_project_id = f.c_project_id)) AS projectname,
    f.description,
    o.name AS orgname,
    ev.value AS ac_code,
    ev.name AS ac_name,
    (((ev.value)::text || '-'::text) || (ev.name)::text) AS account_name,
        CASE ev.accounttype
            WHEN 'A'::bpchar THEN '1 Asset'::text
            WHEN 'E'::bpchar THEN '3 Equity'::text
            WHEN 'L'::bpchar THEN '2 Liability'::text
            WHEN 'M'::bpchar THEN '9 Memo'::text
            WHEN 'O'::bpchar THEN '3 Equity'::text
            WHEN 'R'::bpchar THEN '3 Equity'::text
            ELSE '9. Unknown'::text
        END AS bstypename,
        CASE ev.accounttype
            WHEN 'A'::bpchar THEN '9 BSheet'::text
            WHEN 'E'::bpchar THEN '6 Expense'::text
            WHEN 'L'::bpchar THEN '9 BSheet'::text
            WHEN 'M'::bpchar THEN '9 Memo'::text
            WHEN 'O'::bpchar THEN '9 BSheet'::text
            WHEN 'R'::bpchar THEN '4 Revenue'::text
            ELSE '9. Unknown'::text
        END AS pltypename,
    ( SELECT (((ss.value)::text || '-'::text) || (ss.name)::text)
           FROM c_elementvalue ss
          WHERE (((ss.value)::text = substr((ev.value)::text, 1, 1)) AND (ss.issummary = 'Y'::bpchar) AND (ss.ad_client_id = f.ad_client_id))) AS ac_group,
        CASE ev.accounttype
            WHEN 'A'::bpchar THEN ( SELECT (((ss.value)::text || '-'::text) || (ss.name)::text)
               FROM c_elementvalue ss
              WHERE (((ss.value)::text = substr((ev.value)::text, 1, 3)) AND (ss.issummary = 'Y'::bpchar) AND (ss.ad_client_id = f.ad_client_id)))
            WHEN 'E'::bpchar THEN '330-Current P and L Accounts Balance'::text
            WHEN 'L'::bpchar THEN ( SELECT (((ss.value)::text || '-'::text) || (ss.name)::text)
               FROM c_elementvalue ss
              WHERE (((ss.value)::text = substr((ev.value)::text, 1, 3)) AND (ss.issummary = 'Y'::bpchar) AND (ss.ad_client_id = f.ad_client_id)))
            WHEN 'O'::bpchar THEN ( SELECT (((ss.value)::text || '-'::text) || (ss.name)::text)
               FROM c_elementvalue ss
              WHERE (((ss.value)::text = substr((ev.value)::text, 1, 3)) AND (ss.issummary = 'Y'::bpchar) AND (ss.ad_client_id = f.ad_client_id)))
            WHEN 'R'::bpchar THEN '330-Current P and L Accounts Balance'::text
            ELSE 'Unknown Account Type'::text
        END AS bsgroup,
        CASE ev.accounttype
            WHEN 'A'::bpchar THEN '999-Balance Sheet Item'::text
            WHEN 'E'::bpchar THEN ( SELECT (((ss.value)::text || '-'::text) || (ss.name)::text)
               FROM c_elementvalue ss
              WHERE (((ss.value)::text = substr((ev.value)::text, 1, 3)) AND (ss.issummary = 'Y'::bpchar) AND (ss.ad_client_id = f.ad_client_id)))
            WHEN 'L'::bpchar THEN '999-Balance Sheet Item'::text
            WHEN 'O'::bpchar THEN '999-Balance Sheet Item'::text
            WHEN 'R'::bpchar THEN ( SELECT (((ss.value)::text || '-'::text) || (ss.name)::text)
               FROM c_elementvalue ss
              WHERE (((ss.value)::text = substr((ev.value)::text, 1, 3)) AND (ss.issummary = 'Y'::bpchar) AND (ss.ad_client_id = f.ad_client_id)))
            ELSE 'Unknown Account Type'::text
        END AS plgroup,
        CASE ev.accounttype
            WHEN 'A'::bpchar THEN 'BS'::text
            WHEN 'E'::bpchar THEN 'PL'::text
            WHEN 'L'::bpchar THEN 'BS'::text
            WHEN 'M'::bpchar THEN 'Unknown Account Type'::text
            WHEN 'O'::bpchar THEN 'BS'::text
            WHEN 'R'::bpchar THEN 'PL'::text
            ELSE 'Unknown Account Type'::text
        END AS plbs,
        CASE ev.accounttype
            WHEN 'A'::bpchar THEN '1 NetAssets'::text
            WHEN 'E'::bpchar THEN '2 Equity'::text
            WHEN 'L'::bpchar THEN '1 NetAssets'::text
            WHEN 'M'::bpchar THEN '9 Memo'::text
            WHEN 'O'::bpchar THEN '2 Equity'::text
            WHEN 'R'::bpchar THEN '2 Equity'::text
            ELSE 'Unknown Account Type'::text
        END AS na_eq,
    bp.value AS bpcode,
    bp.name AS bpname,
        CASE
            WHEN (bp.name IS NULL) THEN NULL::text
            ELSE (((bp.value)::text || '-'::text) || (bp.name)::text)
        END AS bpartnercodeandname,
    ( SELECT bpg.name
           FROM c_bp_group bpg
          WHERE (bpg.c_bp_group_id = bp.c_bp_group_id)) AS bpgroupname,
    ( SELECT ad_user.name
           FROM ad_user
          WHERE (ad_user.ad_user_id = bp.salesrep_id)) AS repname,
    p.value AS prodcode,
    p.name AS prodname,
        CASE
            WHEN (p.name IS NULL) THEN NULL::text
            ELSE (((p.value)::text || '-'::text) || (p.name)::text)
        END AS product,
    ( SELECT pc.name
           FROM m_product_category pc
          WHERE (pc.m_product_category_id = p.m_product_category_id)) AS prodcategory
   FROM ((((fact_acct f
     JOIN ad_org o ON ((f.ad_org_id = o.ad_org_id)))
     JOIN c_elementvalue ev ON ((f.account_id = ev.c_elementvalue_id)))
     LEFT JOIN c_bpartner bp ON ((f.c_bpartner_id = bp.c_bpartner_id)))
     LEFT JOIN m_product p ON ((f.m_product_id = p.m_product_id)));

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views