SELECT iol.id AS id,
       iol.organization AS organization,
       iol.client AS client,
       iol.product AS product,
       iol.uOM AS uOM,
       iol.id AS goodsShipmentLine,
       iol.movementQuantity AS movementQuantity,
       iol.shipmentReceipt.businessPartner AS businessPartner,
       iol.shipmentReceipt.documentNo AS shipmentNumber,
       iol.shipmentReceipt.movementDate AS movementDate,
       attributeSetValue AS attributeSetValue,
       coalesce(
                  (SELECT sum(ol.orderedQuantity)
                   FROM OrderLine AS ol
                   LEFT JOIN ol.salesOrder AS o
                   WHERE ol.goodsShipmentLine = iol
                     AND o.processed = TRUE
                     AND o.documentStatus <> 'VO'), 0) AS returnQtyOtherRM,
       (CASE
            WHEN
                   (SELECT ('Y')
                    FROM OrderLine AS ol
                    WHERE ol.salesOrder.id = :salesOrderId
                      AND ol.goodsShipmentLine = iol) IS NULL THEN FALSE
            ELSE TRUE
        END) AS obSelected,
       coalesce(
                  (SELECT ol.orderedQuantity
                   FROM OrderLine AS ol
                   WHERE ol.salesOrder.id = :salesOrderId
                     AND ol.goodsShipmentLine = iol),0)*(-1) AS returned,

  (SELECT ol.returnReason
   FROM OrderLine AS ol
   WHERE ol.salesOrder.id = :salesOrderId
     AND ol.goodsShipmentLine = iol) AS returnReason,
       coalesce(
                  (SELECT ol.tax
                   FROM OrderLine AS ol
                   WHERE ol.salesOrder.id = :salesOrderId
                     AND ol.goodsShipmentLine = iol) ,
                  (SELECT e.salesOrderLine.tax
                   FROM ProcurementPOInvoiceMatch AS e
                   WHERE e.goodsShipmentLine = iol)) AS tax,
       (CASE
            WHEN
                   (SELECT e.salesOrderLine.salesOrder.priceList.priceIncludesTax
                    FROM ProcurementPOInvoiceMatch AS e
                    WHERE e.goodsShipmentLine = iol) = TRUE THEN coalesce(
                                                                            (SELECT ol.unitPrice
                                                                             FROM OrderLine AS ol
                                                                             WHERE ol.salesOrder.id = :salesOrderId
                                                                               AND ol.goodsShipmentLine = iol),
                                                                            (SELECT e.salesOrderLine.grossUnitPrice
                                                                             FROM ProcurementPOInvoiceMatch AS e
                                                                             WHERE e.goodsShipmentLine = iol))
            ELSE coalesce(
                            (SELECT ol.unitPrice
                             FROM OrderLine AS ol
                             WHERE ol.salesOrder.id = :salesOrderId
                               AND ol.goodsShipmentLine = iol),
                            (SELECT e.salesOrderLine.unitPrice
                             FROM ProcurementPOInvoiceMatch AS e
                             WHERE e.goodsShipmentLine = iol))
        END) AS unitPrice,

  (SELECT e.salesOrderLine.salesOrder.documentNo
   FROM ProcurementPOInvoiceMatch AS e
   WHERE e.goodsShipmentLine = iol) AS orderNo
FROM MaterialMgmtShipmentInOutLine AS iol
LEFT JOIN iol.attributeSetValue AS attributeSetValue
JOIN iol.shipmentReceipt AS io
WHERE io.businessPartner.id = :businessPartnerId
  AND io.processed = TRUE
  AND io.documentStatus <> 'VO'
  AND io.salesTransaction = FALSE
  AND 1 = 1
  AND iol.client.id IN ('0',
                        'A1A6D6CD2B7E4EFB8897B389B028D3F8')
  AND iol.organization IN ('6A64155AD53843949E8A5CDBEB1F1275',
                           '660C21E8FE0D49EDA5E489DD2B1528EC',
                           'C05D92E9AAE64B06AD16FFBDE081B7BE',
                           '940F90F7ADB24765A114A12046FF8890',
                           '59FD8D4BAE81402DBF5FC1026C9654BA',
                           '91BADB682A88407B8C2A7398D14C5A28',
                           'EEBC9909A82D4AE283F6F57BED5D6C70',
                           '4B1280DFCE024844B3D03033AA0C8F81',
                           'A0AB171227264470BF511CD193A66B6A',
                           '1C68283588E64DEBACAD8EB34F7D6AF4',
                           '780F046BB5B542F185E5E0F51DCAB2F6',
                           '05F0FFB71F5543D097A58BDC94679B7F',
                           '08B216B116B64432BA584E5C8018B7DB',
                           'FB28572094C346569A1A2EDF950C96BD',
                           'E2050BA4597848D9B102B851BA3683BC',
                           'D7703F78524441549999F48207FEB31A',
                           '354168E8F70340EB9F34E7110D5C9C5B',
                           'BC2881F8E9924716BE1E77410FD7F00C',
                           'F33FE52E1F4C47B0B83649A10C852141',
                           '9BCE939FC06141F399E4C1EFFA39F372',
                           '0',
                           'AADE20C13C934BD496B3729A91E22141',
                           'B1CD4CED672F4B4EB948FCD56EC1281E',
                           '4D29B5B163F44398B297A1037C0475E0')
  AND (upper((COALESCE(to_char(
                                 (SELECT name
                                  FROM ProductTrl AS t
                                  WHERE t.product = iol.product
                                    AND t.language.language='es_ES')), to_char(iol.product.name), ''))) LIKE upper(:alias_0) ESCAPE '|')
  AND (io.movementDate >= (now() - 90)
       OR (CASE WHEN
             (SELECT ('Y')
              FROM OrderLine AS ol
              WHERE ol.salesOrder.id = :salesOrderId
                AND ol.goodsShipmentLine = iol) IS NULL THEN FALSE ELSE TRUE END) = TRUE)
ORDER BY obSelected DESC,
         obSelected DESC,
         io.movementDate DESC,
         io.documentNo DESC,
         iol.lineNo