cof_rv_faturamentoprodutomargem

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

ad_client_id

numeric

√

null

m_product_id

numeric

√

null

m_product.m_product_id

Implied Constraint

cof_producttype_id

numeric

√

null

cof_producttype.cof_producttype_id

Implied Constraint

cof_productclass_id

numeric

√

null

cof_productclass.cof_productclass_id

Implied Constraint

cof_productgroup_id

numeric

√

null

cof_productgroup.cof_productgroup_id

Implied Constraint

c_uom_id

numeric

√

null

c_uom.c_uom_id

Implied Constraint

cof_abc

varchar

√

null

cof_grupofornecedor_id

numeric

√

null

cof_grupofornecedor.cof_grupofornecedor_id

Implied Constraint

c_bpartner_id

numeric

√

null

c_bpartner.c_bpartner_id

Implied Constraint

c_acctschema_id

numeric

√

null

m_costtype_id

numeric

√

null

m_costtype.m_costtype_id

Implied Constraint

m_costelement_id

numeric

√

null

m_costelement.m_costelement_id

Implied Constraint

ad_org_id

numeric

√

null

m_product_category_id

numeric

√

null

m_product_category.m_product_category_id

Implied Constraint

dateinvoiced

timestamp

29,6

√

null

cof_exibiremrelatorios

bpchar

√

null

c_bp_group_id

numeric

√

null

c_bp_group.c_bp_group_id

Implied Constraint

cof_quantidade

numeric

√

null

cof_total

numeric

√

null

cof_impostos

numeric

√

null

cof_custototal

numeric

√

null

cof_margem_c_impostos

numeric

√

null

cof_margem_s_impostos

numeric

√

null

c_invoice_id

numeric

√

null

c_invoice.c_invoice_id

Implied Constraint

documentno

varchar

√

null

c_order_id

numeric

√

null

c_order.c_order_id

Implied Constraint

orddocumentno

varchar

√

null

c_doctypetarget_id

numeric

√

null

docstatus

bpchar

√

null

docbasetype

bpchar

√

null

currentcostprice

numeric

√

null

porc_cof_margem_c_impostos

numeric

√

null

porc_cof_margem_s_impostos

numeric

√

null

Relationships

Close relationships within degrees of separation

One
One implied

View Definition

SELECT p.ad_client_id,
    p.m_product_id,
    p.cof_producttype_id,
    p.cof_productclass_id,
    p.cof_productgroup_id,
    p.c_uom_id,
    p.cof_abc,
    p.cof_grupofornecedor_id,
    i.c_bpartner_id,
    COALESCE(cd.c_acctschema_id, historico.c_acctschema_id) AS c_acctschema_id,
    COALESCE(cd.m_costtype_id, historico.m_costtype_id) AS m_costtype_id,
    COALESCE(cd.m_costelement_id, historico.m_costelement_id) AS m_costelement_id,
    COALESCE(cd.ad_org_id, historico.ad_org_id) AS ad_org_id,
    p.m_product_category_id,
    i.dateinvoiced,
    i.cof_exibiremrelatorios,
    i.c_bp_group_id,
    COALESCE((il.qtyinvoiced * (i.multiplier)::numeric), (0)::numeric) AS cof_quantidade,
    COALESCE(sum((currencyconvert(il.linenetamt, i.c_currency_id, (297)::numeric, (i.dateinvoiced)::timestamp with time zone, i.c_conversiontype_id, i.ad_client_id, i.ad_org_id) * (i.multiplier)::numeric)), (0)::numeric) AS cof_total,
    COALESCE(sum(sumtax.totaltax), (0)::numeric) AS cof_impostos,
    (COALESCE(cd.amt, (il.qtyinvoiced * historico.newcostprice), (0)::numeric) * (i.multiplier)::numeric) AS cof_custototal,
    (COALESCE(sum((currencyconvert(il.linenetamt, i.c_currency_id, (297)::numeric, (i.dateinvoiced)::timestamp with time zone, i.c_conversiontype_id, i.ad_client_id, i.ad_org_id) * (i.multiplier)::numeric)), (0)::numeric) - (COALESCE(cd.amt, (il.qtyinvoiced * historico.newcostprice), (0)::numeric) * (i.multiplier)::numeric)) AS cof_margem_c_impostos,
    ((COALESCE(sum((currencyconvert(il.linenetamt, i.c_currency_id, (297)::numeric, (i.dateinvoiced)::timestamp with time zone, i.c_conversiontype_id, i.ad_client_id, i.ad_org_id) * (i.multiplier)::numeric)), (0)::numeric) - COALESCE(sum(sumtax.totaltax), (0)::numeric)) - (COALESCE(cd.amt, (il.qtyinvoiced * historico.newcostprice), (0)::numeric) * (i.multiplier)::numeric)) AS cof_margem_s_impostos,
    i.c_invoice_id,
    i.documentno,
    i.c_order_id,
    i.orddocumentno,
    i.c_doctypetarget_id,
    i.docstatus,
    tddinv.docbasetype,
    COALESCE(cd.newcostprice, historico.newcostprice) AS currentcostprice,
    trunc((((COALESCE(sum((currencyconvert(il.linenetamt, i.c_currency_id, (297)::numeric, (i.dateinvoiced)::timestamp with time zone, i.c_conversiontype_id, i.ad_client_id, i.ad_org_id) * (i.multiplier)::numeric)), (0)::numeric) - (COALESCE(cd.amt, (il.qtyinvoiced * historico.newcostprice), (0)::numeric) * (i.multiplier)::numeric)) / COALESCE(sum((currencyconvert(il.linenetamt, i.c_currency_id, (297)::numeric, (i.dateinvoiced)::timestamp with time zone, i.c_conversiontype_id, i.ad_client_id, i.ad_org_id) * (i.multiplier)::numeric)), (0)::numeric)) * (100)::numeric), 2) AS porc_cof_margem_c_impostos,
    trunc(((((COALESCE(sum((currencyconvert(il.linenetamt, i.c_currency_id, (297)::numeric, (i.dateinvoiced)::timestamp with time zone, i.c_conversiontype_id, i.ad_client_id, i.ad_org_id) * (i.multiplier)::numeric)), (0)::numeric) - COALESCE(sum(sumtax.totaltax), (0)::numeric)) - (COALESCE(cd.amt, (il.qtyinvoiced * historico.newcostprice), (0)::numeric) * (i.multiplier)::numeric)) / COALESCE(sum((currencyconvert(il.linenetamt, i.c_currency_id, (297)::numeric, (i.dateinvoiced)::timestamp with time zone, i.c_conversiontype_id, i.ad_client_id, i.ad_org_id) * (i.multiplier)::numeric)), (0)::numeric)) * (100)::numeric), 2) AS porc_cof_margem_s_impostos
   FROM ((((((rv_c_invoice i
     LEFT JOIN c_invoiceline il ON ((i.c_invoice_id = il.c_invoice_id)))
     LEFT JOIN m_product p ON ((il.m_product_id = p.m_product_id)))
     LEFT JOIN m_costmovement_v cd ON (((il.m_inoutline_id = cd.m_inoutline_id) AND (il.m_inoutline_id IS NOT NULL))))
     LEFT JOIN ( SELECT DISTINCT ON (cm.m_product_id, cm.c_acctschema_id, cm.m_costtype_id, cm.m_costelement_id, cm.ad_org_id) cm.m_product_id,
            cm.c_acctschema_id,
            cm.m_costtype_id,
            cm.m_costelement_id,
            cm.ad_org_id,
            cm.newcostprice
           FROM m_costmovement_v cm
          ORDER BY cm.m_product_id, cm.c_acctschema_id, cm.m_costtype_id, cm.m_costelement_id, cm.ad_org_id, cm.created DESC) historico ON (((historico.m_product_id = il.m_product_id) AND (historico.ad_org_id = i.ad_org_id) AND (cd.amt IS NULL))))
     LEFT JOIN c_doctype tddinv ON ((tddinv.c_doctype_id = i.c_doctypetarget_id)))
     LEFT JOIN LATERAL ( SELECT sum(COALESCE(currencyconvert(lt.lbr_taxamt, i.c_currency_id, (297)::numeric, (i.dateinvoiced)::timestamp with time zone, i.c_conversiontype_id, i.ad_client_id, i.ad_org_id), (0)::numeric)) AS totaltax,
            lt.lbr_tax_id
           FROM lbr_taxline lt
          WHERE ((lt.lbr_tax_id = il.lbr_tax_id) AND (lt.istaxincluded = 'Y'::bpchar))
          GROUP BY lt.lbr_taxline_id) sumtax ON ((sumtax.lbr_tax_id = il.lbr_tax_id)))
  WHERE ((il.isdescription = 'N'::bpchar) AND (il.linenetamt <> (0)::numeric) AND (i.issotrx = 'Y'::bpchar))
  GROUP BY p.ad_client_id, p.m_product_id, p.cof_producttype_id, p.cof_productclass_id, p.cof_productgroup_id, p.c_uom_id, p.cof_abc, p.cof_grupofornecedor_id, i.c_bpartner_id, historico.c_acctschema_id, historico.m_costtype_id, historico.m_costelement_id, historico.ad_org_id, cd.c_acctschema_id, cd.m_costtype_id, cd.m_costelement_id, cd.ad_org_id, i.ad_org_id, p.m_product_category_id, i.dateinvoiced, i.cof_exibiremrelatorios, i.c_bp_group_id, i.c_invoice_id, i.documentno, i.c_order_id, i.orddocumentno, i.c_doctypetarget_id, i.docstatus, tddinv.docbasetype, il.qtyinvoiced, i.multiplier, historico.newcostprice, cd.newcostprice, cd.amt;

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views