cof_rv_gestaoxconsumo

SELECT tmp.cof_pp_planomestreprod_id,

    tmp.data,

    tmp.produto_pmp,

    tmp.cof_producttype_id,

    tmp.tipo,

    tmp.cof_productclass_id,

    tmp.classe,

    tmp.cof_productgroup_id,

    tmp.grupo,

    tmp.materia_prima,

    tmp.quantidade,

    tmp.tipo_movimento,

    tmp.m_movementline_id,

    tmp.ordem_producao,

    tmp.ad_org_id,

    tmp.ad_client_id,

    tmp.created,

    tmp.createdby,

    tmp.updated,

    tmp.updatedby,

    tmp.isactive,

    tmp.pa_id,

    tmp.mp_id

   FROM ( SELECT pmp.ad_org_id,

            pmp.ad_client_id,

            pmp.created,

            pmp.createdby,

            pmp.updated,

            pmp.updatedby,

            pmp.isactive,

            pmp.cof_pp_planomestreprod_id,

            c.created AS data,

            pmpp.m_product_id AS pa_id,

            pmpp.name AS produto_pmp,

            pt.cof_producttype_id,

            pt.name AS tipo,

            pc.cof_productclass_id,

            pc.name AS classe,

            pg.cof_productgroup_id,

            pg.name AS grupo,

            p.m_product_id AS mp_id,

            p.name AS materia_prima,

            (COALESCE(c.cof_pp_quantidadeutilizada, (0)::numeric) * ('-1'::integer)::numeric) AS quantidade,

            'Consumo'::text AS tipo_movimento,

            NULL::numeric AS m_movementline_id,

            op.documentno AS ordem_producao

           FROM ((((((((cof_pp_consumomateriaprima c

             JOIN m_productionline pl ON ((c.m_productionline_id = pl.m_productionline_id)))

             JOIN m_production op ON ((op.m_production_id = pl.m_production_id)))

             JOIN m_product p ON ((p.m_product_id = c.m_product_id)))

             JOIN cof_pp_planomestreprod pmp ON ((pmp.cof_pp_planomestreprod_id = c.cof_pp_planomestreprod_id)))

             JOIN m_product pmpp ON ((pmpp.m_product_id = pmp.m_product_id)))

             JOIN cof_productgroup pg ON ((pmpp.cof_productgroup_id = pg.cof_productgroup_id)))

             JOIN cof_producttype pt ON ((pg.cof_producttype_id = pt.cof_producttype_id)))

             JOIN cof_productclass pc ON ((pg.cof_productclass_id = pc.cof_productclass_id)))

        UNION

         SELECT pmp.ad_org_id,

            pmp.ad_client_id,

            pmp.created,

            pmp.createdby,

            pmp.updated,

            pmp.updatedby,

            pmp.isactive,

            pmp.cof_pp_planomestreprod_id,

            g.created AS data,

            pmpp.m_product_id AS pa_id,

            pmpp.name AS produto_pmp,

            pt.cof_producttype_id,

            pt.name AS tipo,

            pc.cof_productclass_id,

            pc.name AS classe,

            pg.cof_productgroup_id,

            pg.name AS grupo,

            p.m_product_id AS mp_id,

            p.name AS materia_prima,

            COALESCE(g.cof_pp_quantidaderequisitada, (0)::numeric) AS quantidade,

            'Gestão'::text AS tipo_movimento,

            g.m_movementline_id,

            NULL::character varying AS ordem_producao

           FROM ((((((cof_pp_gestaomp g

             JOIN m_product p ON ((p.m_product_id = g.m_product_id)))

             JOIN cof_pp_planomestreprod pmp ON ((pmp.cof_pp_planomestreprod_id = g.cof_pp_planomestreprod_id)))

             JOIN m_product pmpp ON ((pmpp.m_product_id = pmp.m_product_id)))

             JOIN cof_productgroup pg ON ((pmpp.cof_productgroup_id = pg.cof_productgroup_id)))

             JOIN cof_producttype pt ON ((pg.cof_producttype_id = pt.cof_producttype_id)))

             JOIN cof_productclass pc ON ((pg.cof_productclass_id = pc.cof_productclass_id)))

  ORDER BY 8, 19) tmp;