SELECT XMLELEMENT("grafico",
                  xmlattributes('object' AS "type"),
                  XMLELEMENT("mapOptions",
                             xmlattributes('object' AS "type"),
                             XMLELEMENT("zoom", xmlattributes('number' AS "type"), numbertojson(15)),
                             XMLELEMENT("scrollwheel", xmlattributes('number' AS "type"), numbertojson(0)),
                             XMLELEMENT("height", xmlattributes('number' AS "type"), numbertojson(380)),
                             XMLELEMENT("width", xmlattributes('string' AS "type"), stringtojson('100%')),
                             (SELECT XMLELEMENT("markers_motorista",
                                                xmlattributes('array' AS "type"),
                                                XMLAGG(XMLELEMENT("arrayItem",
                                                                  xmlattributes('object' AS "type"),
                                                                  XMLELEMENT("latitude", xmlattributes('string' AS "type"), stringtojson(p.latitude)),
                                                                  XMLELEMENT("longitude", xmlattributes('string' AS "type"), stringtojson(p.longitude))
                                                                  )
                                                       )
                                                )
                                FROM (SELECT a.nome,
                                             a.latitude,
                                             a.longitude
                                        FROM sinergia.v$motorista a
                                       WHERE a.latitude IS NOT NULL
                                         AND a.longitude IS NOT NULL
                                         AND a.data_ultima_loc >= SYSDATE - 72/24
                                         AND 1=2 --não trazer nenhuma informação/remoção do cluster
                                         AND EXISTS (SELECT 1
                                                       FROM sinergia.v$credenciamento_empresa e
                                                      WHERE e.motorista_id = a.motorista_id
                                                        AND e.empresa_sin_id = ||v_empresa_id||
                                                        AND e.status = 1)
                                     ) p
                             ),
                             (SELECT XMLELEMENT("markers",
                                                xmlattributes('array' AS "type"),
                                                XMLAGG(XMLELEMENT("arrayItem",
                                                                  xmlattributes('object' AS "type"),
                                                                  XMLELEMENT("iconSrc", xmlattributes('string' AS "type"), stringtojson(p.iconSrc)),
                                                                  XMLELEMENT("location", xmlattributes('string' AS "type"), stringtojson(p.lon_lat))
                                                                  )
                                                       )
                                                )
                                FROM (SELECT 'app/img/proposta_pin.png' AS iconSrc,
                                             t.lat_origem || ',' || t.lng_origem AS lon_lat,
                                             t.descricao AS nome,
                                             t.lat_origem,
                                             t.lng_origem
                                        FROM sinergia.v$lote t
                                       WHERE t.lat_origem IS NOT NULL
                                         AND t.lng_origem IS NOT NULL
                                         AND t.agencia_id IN (||v_agencias||)
                                         AND t.data_carregamento BETWEEN
                                             TO_DATE('||v_dia_inicio||',
                                                     'dd/mm/yyyy hh24:mi:ss')
                                         AND TO_DATE('||v_dia_fim||',
                                                     'dd/mm/yyyy hh24:mi:ss')

                                     UNION ALL

                                     SELECT 'app/img/veiculo_pin.png' AS iconSrc,
                                            a.latitude || ',' || a.longitude AS lon_lat,
                                            a.nome,
                                            a.latitude,
                                            a.longitude
                                       FROM sinergia.v$motorista a
                                      WHERE a.latitude IS NOT NULL
                                        AND a.longitude IS NOT NULL
                                        AND a.data_ultima_loc >= SYSDATE - 72/24
                                        AND EXISTS (SELECT 1
                                                      FROM sinergia.v$credenciamento_empresa e
                                                     WHERE e.motorista_id = a.motorista_id
                                                       AND e.empresa_sin_id = ||v_empresa_id||
                                                       AND e.status = 1)
                                     ) p
                             )
                            )
                 )
  FROM dual