SELECT

    /* ==========================
       IMPORT BE DETAILS
       ========================== */

    boe.name AS "Import BE ID:Link/FTWZ BOE Import",

    boe.transaction_id AS "Import BE No:Data",

    DATE_FORMAT(
        boe.transaction_date,
        '%%d-%%m-%%Y'
    ) AS "Import BE Date:Data",

    /* ==========================
       BOE SHIPMENT DETAILS
       ========================== */

    GROUP_CONCAT(
        DISTINCT boe_ship.hawb_hbl_no
        ORDER BY boe_ship.hawb_hbl_no
        SEPARATOR ', '
    ) AS "HAWB / HBL No.:Data",

    GROUP_CONCAT(
        DISTINCT boe_ship.mawb_mbl_no
        ORDER BY boe_ship.mawb_mbl_no
        SEPARATOR ', '
    ) AS "MAWB / MBL No.:Data",

    GROUP_CONCAT(
        DISTINCT DATE_FORMAT(
            boe_ship.mawb_mbl_date,
            '%%d-%%m-%%Y'
        )
        ORDER BY boe_ship.mawb_mbl_date
        SEPARATOR ', '
    ) AS "MAWB / MBL Date:Data",

    GROUP_CONCAT(
        DISTINCT DATE_FORMAT(
            boe_ship.hawb_hbl_date,
            '%%d-%%m-%%Y'
        )
        ORDER BY boe_ship.hawb_hbl_date
        SEPARATOR ', '
    ) AS "HAWB / HBL Date:Data",

    GROUP_CONCAT(
        DISTINCT DATE_FORMAT(
            boe_ship.igm_date,
            '%%d-%%m-%%Y'
        )
        ORDER BY boe_ship.igm_date
        SEPARATOR ', '
    ) AS "IGM Date (Import BE):Data",

    DATE_FORMAT(
        boe.request_out_of_charge_date,
        '%%d-%%m-%%Y'
    ) AS "Request Out Of Charge Date (Import BE):Data",

    DATE_FORMAT(
        boe.request_assessment_date,
        '%%d-%%m-%%Y'
    ) AS "Request Assessment Date (Import BE):Data",

    DATE_FORMAT(
        boe.request_submission_date,
        '%%d-%%m-%%Y'
    ) AS "Request Submission Date (Import BE):Data",

    /* ==========================
       HOUSE BE DETAILS
       ========================== */

    dta_summary.house_be_no
        AS "House BE No:Data",

    dta_summary.house_be_date
        AS "House BE Date:Data",

    dta_summary.house_request_out_of_charge
        AS "Request Out Of Charge Date (House BE):Data",

    dta_summary.house_request_assessment
        AS "Request Assessment Date (House BE):Data",

    dta_summary.house_request_submission
        AS "Request Submission Date (House BE):Data"

FROM `tabFTWZ BOE Import` boe

/* ===========================================
   BOE SHIPMENT
   =========================================== */

LEFT JOIN `tabBOE Import Shipment` boe_ship
    ON boe_ship.parent = boe.name


/* ===========================================
   DTA SUMMARY (ONE ROW PER HAWB)
   =========================================== */

LEFT JOIN (

    SELECT

        ds.hawb_hbl_no,

        /* House BE No */

        GROUP_CONCAT(
            DISTINCT d.name
            ORDER BY d.name
            SEPARATOR ', '
        ) AS house_be_no,

        /* House BE Date */

        GROUP_CONCAT(
            DISTINCT DATE_FORMAT(
                d.transaction_date,
                '%%d-%%m-%%Y'
            )
            ORDER BY d.transaction_date
            SEPARATOR ', '
        ) AS house_be_date,

        /* Request Out Of Charge */

        GROUP_CONCAT(
            DISTINCT DATE_FORMAT(
                d.request_out_of_charge_date,
                '%%d-%%m-%%Y'
            )
            ORDER BY d.request_out_of_charge_date
            SEPARATOR ', '
        ) AS house_request_out_of_charge,

        /* Request Assessment */

        GROUP_CONCAT(
            DISTINCT DATE_FORMAT(
                d.request_assessment_date,
                '%%d-%%m-%%Y'
            )
            ORDER BY d.request_assessment_date
            SEPARATOR ', '
        ) AS house_request_assessment,

        /* Request Submission */

        GROUP_CONCAT(
            DISTINCT DATE_FORMAT(
                d.request_submission_date,
                '%%d-%%m-%%Y'
            )
            ORDER BY d.request_submission_date
            SEPARATOR ', '
        ) AS house_request_submission

    FROM (

        /* Remove duplicate shipment rows */

        SELECT DISTINCT
            parent,
            hawb_hbl_no
        FROM `tabDTA Sale Shipments`

    ) ds

    INNER JOIN `tabFTWZ DTA Sale` d
        ON d.name = ds.parent

    GROUP BY
        ds.hawb_hbl_no

) dta_summary

ON dta_summary.hawb_hbl_no = boe_ship.hawb_hbl_no

/* ==========================================
   GROUP BY
   ========================================== */

GROUP BY
    boe.name,
    boe.transaction_id,
    boe.transaction_date,
    boe.request_out_of_charge_date,
    boe.request_assessment_date,
    boe.request_submission_date


/* ==========================================
   ORDER BY
   ========================================== */

ORDER BY
    boe.transaction_id,
    boe.name;