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_order_id

numeric

√

null

m_inout_id

numeric

√

null

documentno

varchar

√

null

dateordered

timestamp

29,6

√

null

c_doctype_id

numeric

√

null

invoicerule

bpchar

2147483647

√

null

totallines

numeric

√

null

docsource

text

2147483647

√

null

c_invoice_candidate_v_id

numeric

√

null

c_bpartner_location_id

numeric

√

null

ad_user_id

numeric

√

null

deliveryviarule

bpchar

2147483647

√

null

datepromised

timestamp

29,6

√

null

Relationships

Close relationships within degrees of separation

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,
        CASE
            WHEN (o.invoicerule = 'I'::bpchar) THEN sum(((l.qtyordered - l.qtyinvoiced) * l.priceactual))
            ELSE COALESCE(sum(( SELECT sum((l.priceactual * iol.movementqty)) AS sum
               FROM (m_inoutline iol
                 LEFT JOIN m_inout io ON ((io.m_inout_id = iol.m_inout_id)))
              WHERE (((io.docstatus)::text <> ALL (ARRAY['RE'::text, 'VO'::text])) AND (iol.isinvoiced = 'N'::bpchar) AND (iol.c_orderline_id = l.c_orderline_id)))), (0)::numeric)
        END AS totallines,
    'O'::text AS docsource,
    o.c_order_id AS c_invoice_candidate_v_id,
    o.c_bpartner_location_id,
    o.ad_user_id,
    o.deliveryviarule,
    o.datepromised
   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, o.c_bpartner_location_id, o.ad_user_id, o.deliveryviarule
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,
    io.c_bpartner_location_id,
    io.ad_user_id,
    NULL::bpchar AS deliveryviarule,
    NULL::timestamp without time zone AS datepromised
   FROM (((((m_rma rma
     JOIN c_doctype dt ON ((rma.c_doctype_id = dt.c_doctype_id)))
     JOIN c_bpartner bp ON ((rma.c_bpartner_id = bp.c_bpartner_id)))
     JOIN m_inout io ON ((rma.inout_id = io.m_inout_id)))
     LEFT JOIN c_bpartner_location bpl ON ((bpl.c_bpartner_location_id = io.c_bpartner_location_id)))
     LEFT JOIN ad_user u ON ((u.ad_user_id = io.ad_user_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 (NOT (EXISTS ( SELECT 1
           FROM ((c_invoice i
             JOIN c_invoiceline il ON ((il.c_invoice_id = i.c_invoice_id)))
             JOIN m_rmaline rl ON ((rl.m_rmaline_id = il.m_rmaline_id)))
          WHERE ((rl.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) AND (COALESCE(rl.qtyinvoiced, (0)::numeric) < rl.qty)))))
        UNION
         SELECT 1
           FROM ((c_invoiceline il
             JOIN c_orderline ol ON ((il.c_orderline_id = ol.c_orderline_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
                     JOIN m_inoutline iol ON ((iol.m_inoutline_id = rl.m_inoutline_id)))
                  WHERE ((rl.m_rma_id = rma.m_rma_id) AND (iol.c_orderline_id = ol.c_orderline_id) AND (iol.c_orderline_id IS NOT NULL) AND (COALESCE(rl.qtyinvoiced, (0)::numeric) < rl.qty))))))));

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views