SELECT * FROM (
SELECT
r1.name AS cliente,
d.doc_number,
d.date,
pr.product_client_id || '-' || pr.name AS producto,
t.name AS atributo_nombre,
COALESCE(v.value_string::text, v.value_integer::text, v.value_double::text, v.value_date::text, v.value_timestamp::text, v.value_boolean::text, v.value_text::text) as atributo_valor,
wls."name" as origen,
wlt."name" as destino,
wlt.morder as orden,
r."name" AS autoelevadorista,
vt."name" || '-' || v1.patent as vehiculo
FROM soda.document_line l
JOIN soda.product_instances pi ON pi.id = l.product_instance_id
join soda.warehouse_locations wls on wls.warehouse_location_id = l.warehouse_source_location_id
join soda.warehouse_locations wlt on wlt.warehouse_location_id = l.warehouse_location_id
LEFT JOIN soda.document d ON d.document_id = l.document_id
join soda.receiver r on r.receiver_id = d.lifter_driver_id
join soda.vehicles v1 on v1.vehicle_id = d.vehicle_id
join soda.vehicle_types vt on vt.vehicle_type_id = v1.vehicle_type_id
JOIN soda.receiver r1 ON r1.receiver_id = d.customer_id
LEFT JOIN soda.products pr ON pr.product_id = l.product_id
LEFT JOIN soda.attributes_values v ON v.entity_class = 'ProductInstance' AND v.entity_id = pi.id
LEFT JOIN soda.attributes_types_params p ON p.attributes_types_params_id = v.attributes_type_param_id
LEFT JOIN soda.attributes_types t ON t.attributes_types_id = p.attributes_types_id
LEFT JOIN soda.attributes_by_entity ae ON ae.attributes_type_id = t.attributes_types_id and  ae.entity_class = 'ProductInstance'
WHERE 1=1
AND d.type = 'ORDER_OF_MOVE'
AND d.doc_number = ${numero}
AND d.pos = ${pos}
and ae.manual = true
) AS a
UNION
(SELECT
r1.name AS cliente,
d.doc_number,
d.date,
pr.product_client_id || '-' || pr.name AS producto,
t.name AS atributo_nombre,
'' as atributo_valor,
wls."name" as origen,
wlt."name" as destino,
wlt.morder as orden,
r."name" AS autoelevadorista,
vt."name" || '-' || v1.patent as vehiculo
FROM soda.document_line l
JOIN soda.product_instances pi ON pi.id = l.product_instance_id
join soda.warehouse_locations wls on wls.warehouse_location_id = l.warehouse_source_location_id
join soda.warehouse_locations wlt on wlt.warehouse_location_id = l.warehouse_location_id
LEFT JOIN soda.document d ON d.document_id = l.document_id
join soda.receiver r on r.receiver_id = d.lifter_driver_id
join soda.vehicles v1 on v1.vehicle_id = d.vehicle_id
join soda.vehicle_types vt on vt.vehicle_type_id = v1.vehicle_type_id
JOIN soda.receiver r1 ON r1.receiver_id = d.customer_id
LEFT JOIN soda.products pr ON pr.product_id = l.product_id
LEFT JOIN soda.attributes_by_entity ae ON ae.entity_class = 'ProductInstance'
LEFT JOIN soda.attributes_types t ON t.attributes_types_id = ae.attributes_type_id
WHERE 1=1
AND d.type = 'ORDER_OF_MOVE'
AND d.doc_number = ${numero}
AND d.pos = ${pos}
AND ae.attributes_type_id NOT IN
    (SELECT p.attributes_types_id
        FROM soda.attributes_values v
        LEFT JOIN soda.attributes_types_params p ON p.attributes_types_params_id = v.attributes_type_param_id
        WHERE v.entity_class = 'ProductInstance' AND v.entity_id = pi.id)
and ae.manual = true
)
UNION
(SELECT
r1.name AS cliente,
d.doc_number,
d.date,
pr.product_client_id || '-' || pr.name AS producto,
t.name AS atributo_nombre,
COALESCE(pe.letter,'') || LPAD(pe.pos::text, 4, '0') || '-' || LPAD(pe.doc_number::text, 8, '0') AS atributo_valor,
wls."name" as origen,
wlt."name" as destino,
wlt.morder as orden,
r."name" AS autoelevadorista,
vt."name" || '-' || v1.patent as vehiculo
FROM soda.document_line l
JOIN soda.product_instances pi ON pi.id = l.product_instance_id
join soda.warehouse_locations wls on wls.warehouse_location_id = l.warehouse_source_location_id
join soda.warehouse_locations wlt on wlt.warehouse_location_id = l.warehouse_location_id
LEFT JOIN soda.document d ON d.document_id = l.document_id
join soda.receiver r on r.receiver_id = d.lifter_driver_id
join soda.vehicles v1 on v1.vehicle_id = d.vehicle_id
join soda.vehicle_types vt on vt.vehicle_type_id = v1.vehicle_type_id
JOIN soda.receiver r1 ON r1.receiver_id = d.customer_id
LEFT JOIN soda.products pr ON pr.product_id = l.product_id
LEFT JOIN soda.attributes_values v ON v.entity_class = 'ProductInstance' AND v.entity_id = pi.id
LEFT JOIN soda.attributes_types_params p ON p.attributes_types_params_id = v.attributes_type_param_id
LEFT JOIN soda.attributes_types t ON t.attributes_types_id = p.attributes_types_id and t."name" = 'PEDIDO DE EGRESO'
JOIN soda.document pe ON pe.document_id = v.value_integer
WHERE 1=1
AND d.type = 'ORDER_OF_MOVE'
AND d.doc_number = ${numero}
AND d.pos = ${pos}
)
ORDER BY orden, producto, atributo_nombre;