テーブル定義書 http://www.as-link.com/ ERPlus@iDempiere3.1

テーブル情報
システム名	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_openitem
備考

カラム情報
No	物理名	データ型
1	ad_org_id	numeric(10)
2	ad_client_id	numeric(10)
3	documentno	character varying(30)
4	c_invoice_id	numeric(10)
5	c_order_id	numeric(10)
6	c_bpartner_id	numeric(10)
7	issotrx	character(1)
8	dateinvoiced	timestamp without time zone
9	dateacct	timestamp without time zone
10	netdays	numeric
11	duedate	timestamp with time zone
12	daysdue	integer
13	discountdate	timestamp without time zone
14	discountamt	numeric
15	grandtotal	numeric
16	paidamt	numeric
17	openamt	numeric
18	c_currency_id	numeric(10)
19	c_conversiontype_id	numeric(10)
20	c_paymentterm_id	numeric(10)
21	ispayschedulevalid	character(1)
22	c_invoicepayschedule_id	numeric
23	invoicecollectiontype	character(1)
24	c_campaign_id	numeric(10)
25	c_project_id	numeric(10)
26	c_activity_id	numeric(10)
27	ad_orgtrx_id	numeric(10)
28	ad_user_id	numeric(10)
29	c_bpartner_location_id	numeric(10)
30	c_charge_id	numeric(10)
31	c_doctype_id	numeric(10)
32	c_doctypetarget_id	numeric(10)
33	c_dunninglevel_id	numeric(10)
34	chargeamt	numeric
35	c_payment_id	numeric(10)
36	created	timestamp without time zone
37	createdby	numeric(10)
38	dateordered	timestamp without time zone
39	dateprinted	timestamp without time zone
40	description	character varying(255)
41	docaction	character(2)
42	docstatus	character(2)
43	dunninggrace	date
44	generateto	character(1)
45	isactive	character(1)
46	isapproved	character(1)
47	isdiscountprinted	character(1)
48	isindispute	character(1)
49	ispaid	character(1)
50	isprinted	character(1)
51	isselfservice	character(1)
52	istaxincluded	character(1)
53	istransferred	character(1)
54	m_pricelist_id	numeric(10)
55	m_rma_id	numeric(10)
56	paymentrule	character(1)
57	poreference	character varying(20)
58	posted	character(1)
59	processedon	numeric
60	processing	character(1)
61	ref_invoice_id	numeric(10)
62	reversal_id	numeric(10)
63	salesrep_id	numeric(10)
64	sendemail	character(1)
65	totallines	numeric
66	updated	timestamp without time zone
67	updatedby	numeric(10)
68	user1_id	numeric(10)
69	user2_id	numeric(10)

ソース
ソース
SELECT i.ad_org_id, i.ad_client_id, i.documentno, i.c_invoice_id, i.c_order_id, i.c_bpartner_id, i.issotrx, i.dateinvoiced, i.dateacct, p.netdays, paymenttermduedate(i.c_paymentterm_id, (i.dateinvoiced)::timestamp with time zone) AS duedate, paymenttermduedays(i.c_paymentterm_id, (i.dateinvoiced)::timestamp with time zone, getdate()) AS daysdue, adddays((i.dateinvoiced)::timestamp with time zone, p.discountdays) AS discountdate, currencyround(((i.grandtotal * p.discount) / (100)::numeric), i.c_currency_id, 'N'::character varying) AS discountamt, i.grandtotal, invoicepaid(i.c_invoice_id, i.c_currency_id, (1)::numeric) AS paidamt, invoiceopen(i.c_invoice_id, (0)::numeric) AS openamt, i.c_currency_id, i.c_conversiontype_id, i.c_paymentterm_id, i.ispayschedulevalid, NULL::numeric AS c_invoicepayschedule_id, i.invoicecollectiontype, i.c_campaign_id, i.c_project_id, i.c_activity_id, i.c_invoice_ad_orgtrx_id AS ad_orgtrx_id, i.ad_user_id, i.c_bpartner_location_id, i.c_charge_id, i.c_doctype_id, i.c_doctypetarget_id, i.c_dunninglevel_id, i.chargeamt, i.c_payment_id, i.created, i.createdby, i.dateordered, i.dateprinted, i.description, i.docaction, i.docstatus, i.dunninggrace, i.generateto, i.isactive, i.isapproved, i.isdiscountprinted, i.isindispute, i.ispaid, i.isprinted, i.c_invoice_isselfservice AS isselfservice, i.istaxincluded, i.istransferred, i.m_pricelist_id, i.m_rma_id, i.paymentrule, i.poreference, i.posted, i.processedon, i.processing, i.ref_invoice_id, i.reversal_id, i.salesrep_id, i.sendemail, i.totallines, i.updated, i.updatedby, i.user1_id, i.user2_id FROM (rv_c_invoice i JOIN c_paymentterm p ON ((i.c_paymentterm_id = p.c_paymentterm_id))) WHERE (((invoiceopen(i.c_invoice_id, (0)::numeric) <> (0)::numeric) AND (i.ispayschedulevalid <> 'Y'::bpchar)) AND (i.docstatus = ANY (ARRAY['CO'::bpchar, 'CL'::bpchar]))) UNION SELECT i.ad_org_id, i.ad_client_id, i.documentno, i.c_invoice_id, i.c_order_id, i.c_bpartner_id, i.issotrx, i.dateinvoiced, i.dateacct, daysbetween((ips.duedate)::timestamp with time zone, (i.dateinvoiced)::timestamp with time zone) AS netdays, ips.duedate, daysbetween(getdate(), (ips.duedate)::timestamp with time zone) AS daysdue, ips.discountdate, ips.discountamt, ips.dueamt AS grandtotal, invoicepaid(i.c_invoice_id, i.c_currency_id, (1)::numeric) AS paidamt, invoiceopen(i.c_invoice_id, ips.c_invoicepayschedule_id) AS openamt, i.c_currency_id, i.c_conversiontype_id, i.c_paymentterm_id, i.ispayschedulevalid, ips.c_invoicepayschedule_id, i.invoicecollectiontype, i.c_campaign_id, i.c_project_id, i.c_activity_id, i.c_invoice_ad_orgtrx_id AS ad_orgtrx_id, i.ad_user_id, i.c_bpartner_location_id, i.c_charge_id, i.c_doctype_id, i.c_doctypetarget_id, i.c_dunninglevel_id, i.chargeamt, i.c_payment_id, i.created, i.createdby, i.dateordered, i.dateprinted, i.description, i.docaction, i.docstatus, i.dunninggrace, i.generateto, i.isactive, i.isapproved, i.isdiscountprinted, i.isindispute, i.ispaid, i.isprinted, i.c_invoice_isselfservice AS isselfservice, i.istaxincluded, i.istransferred, i.m_pricelist_id, i.m_rma_id, i.paymentrule, i.poreference, i.posted, i.processedon, i.processing, i.ref_invoice_id, i.reversal_id, i.salesrep_id, i.sendemail, i.totallines, i.updated, i.updatedby, i.user1_id, i.user2_id FROM (rv_c_invoice i JOIN c_invoicepayschedule ips ON ((i.c_invoice_id = ips.c_invoice_id))) WHERE ((((invoiceopen(i.c_invoice_id, ips.c_invoicepayschedule_id) <> (0)::numeric) AND (i.ispayschedulevalid = 'Y'::bpchar)) AND (i.docstatus = ANY (ARRAY['CO'::bpchar, 'CL'::bpchar]))) AND (ips.isvalid = 'Y'::bpchar));

ソース

 SELECT i.ad_org_id,
    i.ad_client_id,
    i.documentno,
    i.c_invoice_id,
    i.c_order_id,
    i.c_bpartner_id,
    i.issotrx,
    i.dateinvoiced,
    i.dateacct,
    p.netdays,
    paymenttermduedate(i.c_paymentterm_id, (i.dateinvoiced)::timestamp with time zone) AS duedate,
    paymenttermduedays(i.c_paymentterm_id, (i.dateinvoiced)::timestamp with time zone, getdate()) AS daysdue,
    adddays((i.dateinvoiced)::timestamp with time zone, p.discountdays) AS discountdate,
    currencyround(((i.grandtotal * p.discount) / (100)::numeric), i.c_currency_id, 'N'::character varying) AS discountamt,
    i.grandtotal,
    invoicepaid(i.c_invoice_id, i.c_currency_id, (1)::numeric) AS paidamt,
    invoiceopen(i.c_invoice_id, (0)::numeric) AS openamt,
    i.c_currency_id,
    i.c_conversiontype_id,
    i.c_paymentterm_id,
    i.ispayschedulevalid,
    NULL::numeric AS c_invoicepayschedule_id,
    i.invoicecollectiontype,
    i.c_campaign_id,
    i.c_project_id,
    i.c_activity_id,
    i.c_invoice_ad_orgtrx_id AS ad_orgtrx_id,
    i.ad_user_id,
    i.c_bpartner_location_id,
    i.c_charge_id,
    i.c_doctype_id,
    i.c_doctypetarget_id,
    i.c_dunninglevel_id,
    i.chargeamt,
    i.c_payment_id,
    i.created,
    i.createdby,
    i.dateordered,
    i.dateprinted,
    i.description,
    i.docaction,
    i.docstatus,
    i.dunninggrace,
    i.generateto,
    i.isactive,
    i.isapproved,
    i.isdiscountprinted,
    i.isindispute,
    i.ispaid,
    i.isprinted,
    i.c_invoice_isselfservice AS isselfservice,
    i.istaxincluded,
    i.istransferred,
    i.m_pricelist_id,
    i.m_rma_id,
    i.paymentrule,
    i.poreference,
    i.posted,
    i.processedon,
    i.processing,
    i.ref_invoice_id,
    i.reversal_id,
    i.salesrep_id,
    i.sendemail,
    i.totallines,
    i.updated,
    i.updatedby,
    i.user1_id,
    i.user2_id
   FROM (rv_c_invoice i
     JOIN c_paymentterm p ON ((i.c_paymentterm_id = p.c_paymentterm_id)))
  WHERE (((invoiceopen(i.c_invoice_id, (0)::numeric) <> (0)::numeric) AND (i.ispayschedulevalid <> 'Y'::bpchar)) AND (i.docstatus = ANY (ARRAY['CO'::bpchar, 'CL'::bpchar])))
UNION
 SELECT i.ad_org_id,
    i.ad_client_id,
    i.documentno,
    i.c_invoice_id,
    i.c_order_id,
    i.c_bpartner_id,
    i.issotrx,
    i.dateinvoiced,
    i.dateacct,
    daysbetween((ips.duedate)::timestamp with time zone, (i.dateinvoiced)::timestamp with time zone) AS netdays,
    ips.duedate,
    daysbetween(getdate(), (ips.duedate)::timestamp with time zone) AS daysdue,
    ips.discountdate,
    ips.discountamt,
    ips.dueamt AS grandtotal,
    invoicepaid(i.c_invoice_id, i.c_currency_id, (1)::numeric) AS paidamt,
    invoiceopen(i.c_invoice_id, ips.c_invoicepayschedule_id) AS openamt,
    i.c_currency_id,
    i.c_conversiontype_id,
    i.c_paymentterm_id,
    i.ispayschedulevalid,
    ips.c_invoicepayschedule_id,
    i.invoicecollectiontype,
    i.c_campaign_id,
    i.c_project_id,
    i.c_activity_id,
    i.c_invoice_ad_orgtrx_id AS ad_orgtrx_id,
    i.ad_user_id,
    i.c_bpartner_location_id,
    i.c_charge_id,
    i.c_doctype_id,
    i.c_doctypetarget_id,
    i.c_dunninglevel_id,
    i.chargeamt,
    i.c_payment_id,
    i.created,
    i.createdby,
    i.dateordered,
    i.dateprinted,
    i.description,
    i.docaction,
    i.docstatus,
    i.dunninggrace,
    i.generateto,
    i.isactive,
    i.isapproved,
    i.isdiscountprinted,
    i.isindispute,
    i.ispaid,
    i.isprinted,
    i.c_invoice_isselfservice AS isselfservice,
    i.istaxincluded,
    i.istransferred,
    i.m_pricelist_id,
    i.m_rma_id,
    i.paymentrule,
    i.poreference,
    i.posted,
    i.processedon,
    i.processing,
    i.ref_invoice_id,
    i.reversal_id,
    i.salesrep_id,
    i.sendemail,
    i.totallines,
    i.updated,
    i.updatedby,
    i.user1_id,
    i.user2_id
   FROM (rv_c_invoice i
     JOIN c_invoicepayschedule ips ON ((i.c_invoice_id = ips.c_invoice_id)))
  WHERE ((((invoiceopen(i.c_invoice_id, ips.c_invoicepayschedule_id) <> (0)::numeric) AND (i.ispayschedulevalid = 'Y'::bpchar)) AND (i.docstatus = ANY (ARRAY['CO'::bpchar, 'CL'::bpchar]))) AND (ips.isvalid = 'Y'::bpchar));

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

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

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

RDBMS固有の情報
No	プロパティ名	プロパティ値
1	schemaname	adempiere
2	viewname	rv_openitem
3	viewowner	adempiere
4	definition	SELECT i.ad_org_id, i.ad_client_id, i.documentno, i.c_invoice_id, i.c_order_id, i.c_bpartner_id, i.issotrx, i.dateinvoiced, i.dateacct, p.netdays, paymenttermduedate(i.c_paymentterm_id, (i.dateinvoiced)::timestamp with time zone) AS duedate, paymenttermduedays(i.c_paymentterm_id, (i.dateinvoiced)::timestamp with time zone, getdate()) AS daysdue, adddays((i.dateinvoiced)::timestamp with time zone, p.discountdays) AS discountdate, currencyround(((i.grandtotal * p.discount) / (100)::numeric), i.c_currency_id, 'N'::character varying) AS discountamt, i.grandtotal, invoicepaid(i.c_invoice_id, i.c_currency_id, (1)::numeric) AS paidamt, invoiceopen(i.c_invoice_id, (0)::numeric) AS openamt, i.c_currency_id, i.c_conversiontype_id, i.c_paymentterm_id, i.ispayschedulevalid, NULL::numeric AS c_invoicepayschedule_id, i.invoicecollectiontype, i.c_campaign_id, i.c_project_id, i.c_activity_id, i.c_invoice_ad_orgtrx_id AS ad_orgtrx_id, i.ad_user_id, i.c_bpartner_location_id, i.c_charge_id, i.c_doctype_id, i.c_doctypetarget_id, i.c_dunninglevel_id, i.chargeamt, i.c_payment_id, i.created, i.createdby, i.dateordered, i.dateprinted, i.description, i.docaction, i.docstatus, i.dunninggrace, i.generateto, i.isactive, i.isapproved, i.isdiscountprinted, i.isindispute, i.ispaid, i.isprinted, i.c_invoice_isselfservice AS isselfservice, i.istaxincluded, i.istransferred, i.m_pricelist_id, i.m_rma_id, i.paymentrule, i.poreference, i.posted, i.processedon, i.processing, i.ref_invoice_id, i.reversal_id, i.salesrep_id, i.sendemail, i.totallines, i.updated, i.updatedby, i.user1_id, i.user2_id FROM (rv_c_invoice i JOIN c_paymentterm p ON ((i.c_paymentterm_id = p.c_paymentterm_id))) WHERE (((invoiceopen(i.c_invoice_id, (0)::numeric) <> (0)::numeric) AND (i.ispayschedulevalid <> 'Y'::bpchar)) AND (i.docstatus = ANY (ARRAY['CO'::bpchar, 'CL'::bpchar]))) UNION SELECT i.ad_org_id, i.ad_client_id, i.documentno, i.c_invoice_id, i.c_order_id, i.c_bpartner_id, i.issotrx, i.dateinvoiced, i.dateacct, daysbetween((ips.duedate)::timestamp with time zone, (i.dateinvoiced)::timestamp with time zone) AS netdays, ips.duedate, daysbetween(getdate(), (ips.duedate)::timestamp with time zone) AS daysdue, ips.discountdate, ips.discountamt, ips.dueamt AS grandtotal, invoicepaid(i.c_invoice_id, i.c_currency_id, (1)::numeric) AS paidamt, invoiceopen(i.c_invoice_id, ips.c_invoicepayschedule_id) AS openamt, i.c_currency_id, i.c_conversiontype_id, i.c_paymentterm_id, i.ispayschedulevalid, ips.c_invoicepayschedule_id, i.invoicecollectiontype, i.c_campaign_id, i.c_project_id, i.c_activity_id, i.c_invoice_ad_orgtrx_id AS ad_orgtrx_id, i.ad_user_id, i.c_bpartner_location_id, i.c_charge_id, i.c_doctype_id, i.c_doctypetarget_id, i.c_dunninglevel_id, i.chargeamt, i.c_payment_id, i.created, i.createdby, i.dateordered, i.dateprinted, i.description, i.docaction, i.docstatus, i.dunninggrace, i.generateto, i.isactive, i.isapproved, i.isdiscountprinted, i.isindispute, i.ispaid, i.isprinted, i.c_invoice_isselfservice AS isselfservice, i.istaxincluded, i.istransferred, i.m_pricelist_id, i.m_rma_id, i.paymentrule, i.poreference, i.posted, i.processedon, i.processing, i.ref_invoice_id, i.reversal_id, i.salesrep_id, i.sendemail, i.totallines, i.updated, i.updatedby, i.user1_id, i.user2_id FROM (rv_c_invoice i JOIN c_invoicepayschedule ips ON ((i.c_invoice_id = ips.c_invoice_id))) WHERE ((((invoiceopen(i.c_invoice_id, ips.c_invoicepayschedule_id) <> (0)::numeric) AND (i.ispayschedulevalid = 'Y'::bpchar)) AND (i.docstatus = ANY (ARRAY['CO'::bpchar, 'CL'::bpchar]))) AND (ips.isvalid = 'Y'::bpchar));