テーブル情報
システム名 http://www.as-link.com/  作成者 秦　松甫 
サブシステム名 ERPlus@iDempiere3.1_daily  作成日 2016/3/7 
スキーマ名 adempiere  更新日  
論理テーブル名   RDBMS  PostgreSQL 9.4.5 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit 9.4.5 
物理テーブル名 rv_fact_adaxa     
備考


カラム情報
No 論理名 物理名 データ型 Not Null デフォルト 備考
1 ad_client_id numeric(10)  
2 ad_org_id numeric(10)  
3 clientname character varying(60)  
4 dateacct timestamp without time zone  
5 bsdate timestamp without time zone  
6 bsmth text  
7 fyear text  
8 fymth text  
9 table_name character varying(60)  
10 record_id numeric(10)  
11 budname character varying(60)  
12 taxname character varying(60)  
13 act_bud text  
14 currency character(3)  
15 amtsce numeric  
16 amtacct numeric  
17 pl$act numeric  
18 bs$act numeric  
19 pl$bud numeric  
20 uomname character varying(10)  
21 qty numeric  
22 projectname character varying(60)  
23 description character varying(255)  
24 orgname character varying(60)  
25 ac_code character varying(40)  
26 ac_name character varying(120)  
27 account_name text  
28 bstypename text  
29 pltypename text  
30 ac_group text  
31 bsgroup text  
32 plgroup text  
33 plbs text  
34 na_eq text  
35 bpcode character varying(40)  
36 bpname character varying(120)  
37 bpartnercodeandname text  
38 bpgroupname character varying(60)  
39 repname character varying(60)  
40 prodcode character varying(40)  
41 prodname character varying(255)  
42 product text  
43 prodcategory character varying(60)  


ソース
ソース 
 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))::timestamp with time zone, '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))::timestamp with time zone, '"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)));


インデックス情報
No インデックス名 カラムリスト ユニーク 備考


外部キー情報
No 外部キー名 カラムリスト 参照先 参照先カラムリスト


外部キー情報(PK側)
No 外部キー名 カラムリスト 参照元 参照元カラムリスト


RDBMS固有の情報
No プロパティ名 プロパティ値
1 schemaname adempiere
2 viewname rv_fact_adaxa
3 viewowner adempiere
4 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))::timestamp with time zone, '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))::timestamp with time zone, '"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)));