c_invoice_candidate_v

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

ad_client_id

numeric

√

null

ad_org_id

numeric

√

null

created

timestamp

29,6

√

null

updated

timestamp

29,6

√

null

isactive

bpchar

√

null

c_bpartner_id

numeric

√

null

c_bpartner.c_bpartner_id

Implied Constraint

c_order_id

numeric

√

null

c_order.c_order_id

Implied Constraint

m_inout_id

numeric

√

null

documentno

varchar

√

null

dateordered

timestamp

29,6

√

null

c_doctype_id

numeric

√

null

c_doctype.c_doctype_id

Implied Constraint

invoicerule

bpchar

2147483647

√

null

totallines

numeric

√

null

docsource

text

2147483647

√

null

c_invoice_candidate_v_id

numeric

√

null

Relationships

Close relationships within degrees of separation

One
One implied

View Definition

SELECT o.ad_client_id,
    o.ad_org_id,
    o.created,
    o.updated,
    o.isactive,
    o.c_bpartner_id,
    o.c_order_id,
    NULL::numeric AS m_inout_id,
    o.documentno,
    o.dateordered,
    o.c_doctype_id,
    o.invoicerule,
    sum(((l.qtyordered - l.qtyinvoiced) * l.priceactual)) AS totallines,
    'O'::text AS docsource,
    o.c_order_id AS c_invoice_candidate_v_id
   FROM (((c_order o
     JOIN c_orderline l ON ((o.c_order_id = l.c_order_id)))
     JOIN c_bpartner bp ON ((o.c_bpartner_id = bp.c_bpartner_id)))
     LEFT JOIN c_invoiceschedule si ON ((bp.c_invoiceschedule_id = si.c_invoiceschedule_id)))
  WHERE ((o.docstatus = ANY (ARRAY['CO'::bpchar, 'CL'::bpchar, 'IP'::bpchar])) AND (o.c_doctype_id IN ( SELECT c_doctype.c_doctype_id
           FROM c_doctype
          WHERE ((c_doctype.docbasetype = 'SOO'::bpchar) AND (c_doctype.docsubtypeso <> ALL (ARRAY['ON'::bpchar, 'OB'::bpchar, 'WR'::bpchar]))))) AND (l.qtyordered <> l.qtyinvoiced) AND ((o.invoicerule = 'I'::bpchar) OR ((o.invoicerule = 'O'::bpchar) AND (NOT (EXISTS ( SELECT 1
           FROM c_orderline zz1
          WHERE ((zz1.c_order_id = o.c_order_id) AND (zz1.qtyordered <> zz1.qtydelivered)))))) OR ((o.invoicerule = 'D'::bpchar) AND (l.qtyinvoiced <> l.qtydelivered)) OR ((o.invoicerule = 'S'::bpchar) AND (bp.c_invoiceschedule_id IS NULL)) OR ((o.invoicerule = 'S'::bpchar) AND (bp.c_invoiceschedule_id IS NOT NULL) AND ((si.invoicefrequency IS NULL) OR (si.invoicefrequency = 'D'::bpchar) OR (si.invoicefrequency = 'W'::bpchar) OR ((si.invoicefrequency = 'T'::bpchar) AND (((trunc(o.dateordered) <= (((firstof(statement_timestamp(), 'MM'::character varying))::timestamp with time zone + si.invoicedaycutoff) - (1)::numeric)) AND (trunc(statement_timestamp()) >= (((firstof((o.dateordered)::timestamp with time zone, 'MM'::character varying))::timestamp with time zone + si.invoiceday) - (1)::numeric))) OR ((trunc(o.dateordered) <= (((firstof(statement_timestamp(), 'MM'::character varying))::timestamp with time zone + si.invoicedaycutoff) + (14)::numeric)) AND (trunc(statement_timestamp()) >= (((firstof((o.dateordered)::timestamp with time zone, 'MM'::character varying))::timestamp with time zone + si.invoiceday) + (14)::numeric))))) OR ((si.invoicefrequency = 'M'::bpchar) AND (trunc(o.dateordered) <= (((firstof(statement_timestamp(), 'MM'::character varying))::timestamp with time zone + si.invoicedaycutoff) - (1)::numeric)) AND (trunc(statement_timestamp()) >= (((firstof((o.dateordered)::timestamp with time zone, 'MM'::character varying))::timestamp with time zone + si.invoiceday) - (1)::numeric)))))))
  GROUP BY o.ad_client_id, o.ad_org_id, o.created, o.updated, o.isactive, o.c_bpartner_id, o.c_order_id, o.documentno, o.dateordered, o.c_doctype_id, o.invoicerule
UNION ALL
 SELECT rma.ad_client_id,
    rma.ad_org_id,
    rma.created,
    rma.updated,
    rma.isactive,
    rma.c_bpartner_id,
    rma.c_order_id,
    rma.inout_id AS m_inout_id,
    rma.documentno,
    rma.created AS dateordered,
    rma.c_doctype_id,
    NULL::bpchar AS invoicerule,
    rma.amt AS totallines,
    'R'::text AS docsource,
    rma.m_rma_id AS c_invoice_candidate_v_id
   FROM (m_rma rma
     JOIN c_doctype dt ON ((rma.c_doctype_id = dt.c_doctype_id)))
  WHERE ((rma.docstatus = 'CO'::bpchar) AND (dt.docbasetype = 'SOO'::bpchar) AND (NOT (EXISTS ( SELECT 1
           FROM c_invoice i
          WHERE ((i.m_rma_id = rma.m_rma_id) AND (i.docstatus = ANY (ARRAY['IP'::bpchar, 'CO'::bpchar, 'CL'::bpchar])))))) AND (EXISTS ( SELECT 1
           FROM ((c_invoiceline il
             JOIN m_inoutline iol ON ((il.m_inoutline_id = iol.m_inoutline_id)))
             JOIN c_invoice i ON ((i.c_invoice_id = il.c_invoice_id)))
          WHERE ((i.docstatus = ANY (ARRAY['CO'::bpchar, 'CL'::bpchar])) AND (EXISTS ( SELECT 1
                   FROM m_rmaline rl
                  WHERE ((rl.m_rma_id = rma.m_rma_id) AND (iol.m_inoutline_id = rl.m_inoutline_id) AND (rl.m_inoutline_id IS NOT NULL))))))));

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views