Untitled

      SELECT
            ht.canter_code AS palletBarcode,
            sa.code AS staging_location,
            sh.awb_nr AS containerBarcode,
            COUNT(DISTINCT it.id_item) as item_qty,
            MAX(al.created_at) AS dispatched_at,
            sh.order_nr,
            src_wh.partner_warehouse_code AS source_warehouse_code,
            COALESCE(JSON_EXTRACT_SCALAR(dst_wh.misc,'$.darkstore_name'), dst_wh.name) AS destination_darkstore,
            it.target_shipped_at,
            it.created_at AS request_creation_date,
            cg.shipping_cutoff,
            TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), al.created_at, HOUR) AS ageing_hours
        FROM noondwh.scgoms_goms.shipment sh
        LEFT JOIN noondwh.scgoms_goms.shipment_line sl USING (id_shipment)
        LEFT JOIN noondwh.scgoms_goms.item it USING (id_item)
        LEFT JOIN noondwh.scgoms_goms.consolidation_group cg USING (id_consolidation_group)
        LEFT JOIN noondwh.scgoms_goms.warehouse src_wh ON (it.id_warehouse = src_wh.id_warehouse)
        LEFT JOIN noondwh.scgoms_goms.hub_transfer_line htl ON (htl.type, htl.line_type, htl.line_id) = ('forward_nim', 'shipment', sh.id_shipment)
        LEFT JOIN noondwh.scgoms_goms.hub_transfer_group htg ON (htl.id_hub_transfer_group = htg.id_hub_transfer_group)
        LEFT JOIN noondwh.scgoms_goms.hub_transfer ht USING (id_hub_transfer)
        LEFT JOIN noondwh.scgoms_goms.staging_area sa ON (htg.id_staging_area = sa.id_staging_area)
        LEFT JOIN noondwh.scgoms_goms.warehouse dst_wh ON (htg.destination_type, htg.destination_id) = ('warehouse', dst_wh.id_warehouse)
        LEFT JOIN noondwh.scgoms_goms.audit_log al ON (al.id_audit_action, al.action_key, JSON_EXTRACT_SCALAR(al.misc,'$.to')) = (11, ht.id_hub_transfer, 'sealed')
        WHERE sh.id_mp = 9
            AND htl.id_status = 36 
            AND ht.id_status = 13
              {% if destination_warehouse %}
                AND COALESCE(JSON_EXTRACT_SCALAR(dst_wh.misc, '$.darkstore_name'), dst_wh.name) = :destination_warehouse
            {% endif %}
            {% if source_warehouse %}
                AND src_wh.partner_warehouse_code = :source_warehouse
            {% endif %}
            {% if shipping_cutoff %}
                AND cg.shipping_cutoff = :shipping_cutoff
            {% endif %}
            {% if shipment_creation_date %}
                AND sh.created_at BETWEEN TIMESTAMP(CONCAT(:shipment_creation_date, ' 00:00:00')) AND TIMESTAMP(CONCAT(:shipment_creation_date, ' 23:59:59'))
            {% endif %}
            {% if minimum_aging_hours %}
                AND TIMESTAMP_DIFF(current_timestamp, al.created_at, HOUR) >= :minimum_aging_hours
            {% endif %}
            {% if maximum_aging_hours %}
                AND TIMESTAMP_DIFF(current_timestamp, al.created_at, HOUR) <= :maximum_aging_hours
            {% endif %}
            {% if id_country %}
                AND src_wh.id_country = :id_country
            {% endif %}
        GROUP BY 1, 2, 3, 6, 7, 8, 9, 10, 11, 12