cof_rv_tracking_order

SELECT pedidos.ad_client_id,

    pedidos.ad_org_id,

    pedidos.c_order_id,

    pedidos.documentno,

    pedidos.c_doctypetarget_id,

    pedidos.doctypename,

    pedidos.dateordered,

    pedidos.docstatus,

    pedidos.created,

    pedidos.updated,

    pedidos.createdby,

    pedidos.updatedby

   FROM ( SELECT o.ad_client_id,

            o.ad_org_id,

            o.c_order_id,

            o.documentno,

            o.c_doctypetarget_id,

            cd.name AS doctypename,

            o.dateordered,

            cof_getreflistvalue('C_Order'::character varying, 'DocStatus'::character varying, (o.docstatus)::character varying) AS docstatus,

            o.created,

            o.updated,

            o.createdby,

            o.updatedby

           FROM (c_order o

             LEFT JOIN c_doctype cd ON ((cd.c_doctype_id = o.c_doctypetarget_id)))) pedidos

UNION

 SELECT expedicoes.ad_client_id,

    expedicoes.ad_org_id,

    expedicoes.c_order_id,

    expedicoes.documentno,

    expedicoes.c_doctype_id AS c_doctypetarget_id,

    expedicoes.doctypename,

    expedicoes.dateordered,

    expedicoes.docstatus,

    expedicoes.created,

    expedicoes.updated,

    expedicoes.createdby,

    expedicoes.updatedby

   FROM ( SELECT io.ad_client_id,

            io.ad_org_id,

            io.c_order_id,

            io.documentno,

            io.c_doctype_id,

            cd.name AS doctypename,

            io.movementdate AS dateordered,

            cof_getreflistvalue('M_InOut'::character varying, 'DocStatus'::character varying, (io.docstatus)::character varying) AS docstatus,

            io.created,

            io.updated,

            io.createdby,

            io.updatedby

           FROM (m_inout io

             LEFT JOIN c_doctype cd ON ((cd.c_doctype_id = io.c_doctype_id)))) expedicoes

UNION

 SELECT faturas.ad_client_id,

    faturas.ad_org_id,

    faturas.c_order_id,

    faturas.documentno,

    faturas.c_doctypetarget_id,

    faturas.doctypename,

    faturas.dateinvoiced AS dateordered,

    faturas.docstatus,

    faturas.created,

    faturas.updated,

    faturas.createdby,

    faturas.updatedby

   FROM ( SELECT i.ad_client_id,

            i.ad_org_id,

            i.c_order_id,

            i.documentno,

            i.c_doctypetarget_id,

            cd.name AS doctypename,

            i.dateinvoiced,

            cof_getreflistvalue('C_Invoice'::character varying, 'DocStatus'::character varying, (i.docstatus)::character varying) AS docstatus,

            i.created,

            i.updated,

            i.createdby,

            i.updatedby

           FROM (c_invoice i

             LEFT JOIN c_doctype cd ON ((cd.c_doctype_id = i.c_doctypetarget_id)))) faturas

UNION

 SELECT notasfiscais.ad_client_id,

    notasfiscais.ad_org_id,

    notasfiscais.c_order_id,

    notasfiscais.documentno,

    notasfiscais.c_doctypetarget_id,

    notasfiscais.doctypename,

    notasfiscais.ide_dhemi AS dateordered,

    notasfiscais.docstatus,

    notasfiscais.created,

    notasfiscais.updated,

    notasfiscais.createdby,

    notasfiscais.updatedby

   FROM ( SELECT df.ad_client_id,

            df.ad_org_id,

            df.c_order_id,

            df.documentno,

            df.c_doctypetarget_id,

            cd.name AS doctypename,

            df.ide_dhemi,

            cof_getreflistvalue('LBR_DocFiscal'::character varying, 'DocStatus'::character varying, df.docstatus) AS docstatus,

            df.created,

            df.updated,

            df.createdby,

            df.updatedby

           FROM (lbr_docfiscal df

             LEFT JOIN c_doctype cd ON ((cd.c_doctype_id = df.c_doctypetarget_id)))) notasfiscais

UNION

 SELECT listadeembarque.ad_client_id,

    listadeembarque.ad_org_id,

    listadeembarque.c_order_id,

    listadeembarque.documentno,

    listadeembarque.c_doctypetarget_id,

    listadeembarque.doctypename,

    listadeembarque.cof_packingdate AS dateordered,

    listadeembarque.docstatus,

    listadeembarque.created,

    listadeembarque.updated,

    listadeembarque.createdby,

    listadeembarque.updatedby

   FROM ( SELECT pl.ad_client_id,

            pl.ad_org_id,

                CASE

                    WHEN (pll.c_order_id > (0)::numeric) THEN pll.c_order_id

                    WHEN (mi.c_order_id > (0)::numeric) THEN mi.c_order_id

                    WHEN (mr.c_order_id > (0)::numeric) THEN mr.c_order_id

                    WHEN (mi2.c_order_id > (0)::numeric) THEN mi2.c_order_id

                    ELSE NULL::numeric

                END AS c_order_id,

            pl.documentno,

            NULL::numeric(10,0) AS c_doctypetarget_id,

            'Lista de Embraque'::text AS doctypename,

            pl.cof_packingdate,

            cof_getreflistvalue('COF_PackingList'::character varying, 'COF_Fase'::character varying, pl.cof_fase) AS docstatus,

            pl.created,

            pl.updated,

            pl.createdby,

            pl.updatedby

           FROM ((((cof_packinglist_line pll

             LEFT JOIN cof_packinglist pl ON ((pl.cof_packinglist_id = pll.cof_packinglist_id)))

             LEFT JOIN m_inout mi ON ((mi.m_inout_id = pll.m_inout_id)))

             LEFT JOIN m_rma mr ON ((mr.m_rma_id = pll.m_rma_id)))

             LEFT JOIN m_inout mi2 ON ((mi2.m_inout_id = mr.inout_id)))) listadeembarque

UNION

 SELECT devolucao.ad_client_id,

    devolucao.ad_org_id,

    devolucao.c_order_id,

    devolucao.documentno,

    devolucao.c_doctype_id AS c_doctypetarget_id,

    devolucao.doctypename,

    devolucao.created AS dateordered,

    devolucao.docstatus,

    devolucao.created,

    devolucao.updated,

    devolucao.createdby,

    devolucao.updatedby

   FROM ( SELECT arm.ad_client_id,

            arm.ad_org_id,

            ol.c_order_id,

            arm.documentno,

            arm.c_doctype_id,

            cd.name AS doctypename,

            cof_getreflistvalue('M_Rma'::character varying, 'DocStatus'::character varying, (arm.docstatus)::character varying) AS docstatus,

            arm.created,

            arm.updated,

            arm.createdby,

            arm.updatedby

           FROM ((((m_rma arm

             LEFT JOIN m_rmaline arml ON ((arml.m_rma_id = arm.m_rma_id)))

             LEFT JOIN m_inoutline iol ON ((iol.m_inoutline_id = arml.m_inoutline_id)))

             LEFT JOIN c_orderline ol ON ((ol.c_orderline_id = iol.c_orderline_id)))

             LEFT JOIN c_doctype cd ON ((cd.c_doctype_id = arm.c_doctype_id)))

          GROUP BY arm.ad_client_id, arm.ad_org_id, ol.c_order_id, arm.documentno, arm.c_doctype_id, cd.name, arm.created, arm.docstatus, arm.updated, arm.createdby, arm.updatedby) devolucao;