cof_c_invoice_candidate_v

SELECT o.ad_client_id,

    o.ad_org_id,

    o.c_bpartner_id,

    o.c_order_id,

    o.documentno,

    o.dateordered,

    o.datepromised,

    o.c_doctype_id,

    o.c_bpartner_location_id,

    o.ad_user_id,

    sum(((l.qtyordered - l.qtyinvoiced) * l.priceactual)) AS totallines

   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, '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)::timestamp with time zone) <= (((firstof(getdate(), 'MM'::character varying))::timestamp with time zone + si.invoicedaycutoff) - (1)::numeric)) AND (trunc(getdate()) >= (((firstof((o.dateordered)::timestamp with time zone, 'MM'::character varying))::timestamp with time zone + si.invoiceday) - (1)::numeric))) OR ((trunc((o.dateordered)::timestamp with time zone) <= (((firstof(getdate(), 'MM'::character varying))::timestamp with time zone + si.invoicedaycutoff) + (14)::numeric)) AND (trunc(getdate()) >= (((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)::timestamp with time zone) <= (((firstof(getdate(), 'MM'::character varying))::timestamp with time zone + si.invoicedaycutoff) - (1)::numeric)) AND (trunc(getdate()) >= (((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.c_bpartner_id, o.c_order_id, o.documentno, o.dateordered, o.c_doctype_id, o.c_bpartner_location_id, o.ad_user_id;