SELECT q.csv_line
FROM (
    WITH report_period AS (
        -- period setup from external bind parameters
        SELECT
            TRUNC(CAST(:script_date_from AS DATE)) AS dt_from,
            TRUNC(CAST(:script_date_to AS DATE)) AS dt_to
        FROM dual
    ),
    base_rows_raw AS (
        SELECT
            v.ydet_id,
            v.ydet_date,
            v.arrival_date_par,
            v.departure_date_par,
            v.reservation_id,
            v.create_date,
            v.create_user_name,
            CASE
                WHEN v.reservation_status = 'Checked out' THEN 'Check Out'
                ELSE v.reservation_status
            END AS res_status,
            v.roomtype_short,
            v.roomtype_long,
            v.arrival_date,
            v.arrival_time,
            v.departure_date,
            v.departure_time,
            v.rate_short,
            v.rate_long,
            v.final_price,
            v.travelagent_name AS ta,
            v.market_code,
            v.country,
            v.city,
             v.birthday,
             v.gender,
             v.adult,
             v.child,
             v.infant,
             v.nights,
             v.reserv_deep,
             v.profile_fnb_packages,
             v.profile_stat3_packages,
             v.profile_stat4_packages,
            v.profile_stat5_packages
        FROM hrs_mriya_res_by_date_export v
        CROSS JOIN report_period rp
        WHERE v.mainguest = 1
          AND v.reservation_status IN ('Expected', 'Inhouse', 'Checked out')
          AND v.ydet_date BETWEEN rp.dt_from AND rp.dt_to
    ),
    base_rows AS (
        SELECT
            r.ydet_id,
            r.ydet_date,
            r.arrival_date_par,
            r.departure_date_par,
            r.reservation_id,
            r.create_date,
            r.create_user_name,
            r.res_status,
            r.roomtype_short,
            r.roomtype_long,
            r.arrival_date,
            r.arrival_time,
            r.departure_date,
            r.departure_time,
            r.rate_short,
            r.rate_long,
            r.final_price,
            r.ta,
            r.market_code,
            r.country,
            r.city,
             r.birthday,
             r.gender,
             r.adult,
             r.child,
             r.infant,
             r.nights,
             r.reserv_deep,
             r.profile_fnb_packages,
             r.profile_stat3_packages,
             r.profile_stat4_packages,
            r.profile_stat5_packages
        FROM (
            SELECT
                b.*,
                ROW_NUMBER() OVER (
                    PARTITION BY b.ydet_id
                    ORDER BY b.reservation_id, b.ydet_date, NVL(b.create_user_name, ''), NVL(b.market_code, '')
                ) AS rn
            FROM base_rows_raw b
        ) r
        WHERE r.rn = 1
    ),
    pm_rows AS (
        SELECT
            z.zpos_postdate AS pm_date,
            z.zpos_originated_zfac_id AS pm_res_id,
            x.xcma_longdesc AS pm_market_code,
            SUM(z.zpos_grossunitprice * z.zpos_quantity) AS accomodation_adr
        FROM zpos z
        JOIN zdco d ON d.zdco_id = z.zpos_zdco_id
        LEFT JOIN zfac zf ON zf.zfac_id = z.zpos_originated_zfac_id
        LEFT JOIN xcma x ON x.xcma_id = zf.zfac_xcma_id
        CROSS JOIN report_period rp
        WHERE z.zpos_postdate BETWEEN rp.dt_from AND rp.dt_to
          AND z.zpos_originated_yres_id IS NULL
          AND z.zpos_originated_zfac_id IS NOT NULL
          AND z.zpos_cdt = 1
          AND d.zdco_stats_type = 1
          AND NVL(z.zpos_grossunitprice * z.zpos_quantity, 0) <> 0
        GROUP BY z.zpos_postdate, z.zpos_originated_zfac_id, x.xcma_longdesc
    )
    SELECT
        0 AS ord,
        CAST(NULL AS DATE) AS sort_date,
        0 AS sort_id,
        'DATE;RES_ID;CREATE_DATE;CREATE_USER;RES_STATUS;ROOMTYPE_SHORT;ROOMTYPE;ARRIVAL_DATE;ARRIVAL_TIME;DEPARTURE_DATE;DEPARTURE_TIME;RATE_CODE;RATE;FINAL_PRICE;TA;MARKET_CODE;COUNTRY;CITY;BIRTHDAY;GENDER;ADULT;CHILD;INFANT;NIGHTS;RESERV_DEEP;ACCOMODATION_ADR;PROFILE_FNB_PACKAGES;PROFILE_STAT3_PACKAGES;PROFILE_STAT4_PACKAGES;PROFILE_STAT5_PACKAGES' AS csv_line
    FROM dual

    UNION ALL

    SELECT
        1 AS ord,
        b.ydet_date AS sort_date,
        b.reservation_id AS sort_id,
        TO_CHAR(b.ydet_date, 'DD.MM.YYYY') || ';' ||
        b.reservation_id || ';' ||
        NVL(b.create_date, '') || ';' ||
        REPLACE(REPLACE(NVL(b.create_user_name, ''), CHR(10), ' '), ';', ',') || ';' ||
        NVL(b.res_status, '') || ';' ||
        REPLACE(NVL(b.roomtype_short, ''), ';', ',') || ';' ||
        REPLACE(NVL(b.roomtype_long, ''), ';', ',') || ';' ||
        NVL(b.arrival_date, '') || ';' ||
        NVL(b.arrival_time, '') || ';' ||
        NVL(b.departure_date, '') || ';' ||
        NVL(b.departure_time, '') || ';' ||
        REPLACE(NVL(b.rate_short, ''), ';', ',') || ';' ||
        REPLACE(NVL(b.rate_long, ''), ';', ',') || ';' ||
        TO_CHAR(NVL(b.final_price, 0), 'FM9999999990D00', 'NLS_NUMERIC_CHARACTERS = '',.''') || ';' ||
        REPLACE(NVL(b.ta, ''), ';', ',') || ';' ||
        REPLACE(NVL(b.market_code, ''), ';', ',') || ';' ||
        REPLACE(NVL(b.country, ''), ';', ',') || ';' ||
        REPLACE(NVL(b.city, ''), ';', ',') || ';' ||
        NVL(TO_CHAR(b.birthday, 'DD.MM.YYYY'), '') || ';' ||
        NVL(b.gender, '') || ';' ||
        TO_CHAR(NVL(b.adult, 0)) || ';' ||
        TO_CHAR(NVL(b.child, 0)) || ';' ||
        TO_CHAR(NVL(b.infant, 0)) || ';' ||
        TO_CHAR(NVL(b.nights, 0), 'FM9999990D00', 'NLS_NUMERIC_CHARACTERS = '',.''') || ';' ||
        TO_CHAR(NVL(b.reserv_deep, 0), 'FM9999990D00', 'NLS_NUMERIC_CHARACTERS = '',.''') || ';' ||
        TO_CHAR(
            NVL((
                SELECT SUM(z.zpos_grossunitprice * z.zpos_quantity)
                FROM zpos z
                JOIN zdco d ON d.zdco_id = z.zpos_zdco_id
                WHERE z.zpos_originated_yres_id = b.reservation_id
                  AND z.zpos_cdt = 1
                  AND d.zdco_stats_type = 1
                  AND z.zpos_postdate = b.ydet_date
            ), 0)
            + NVL((
                SELECT SUM(z.zpos_grossunitprice * z.zpos_quantity)
                FROM zpos z
                JOIN zdco d ON d.zdco_id = z.zpos_zdco_id
                WHERE z.zpos_originated_yres_id = b.reservation_id
                  AND z.zpos_cdt = 1
                  AND d.zdco_stats_type = 1
                  AND z.zpos_postdate = b.departure_date_par
                  AND b.departure_date_par = b.ydet_date + 1
            ), 0)
            + NVL((
                SELECT SUM(p.ypos_unitprice * p.ypos_quantity)
                FROM ypos p
                JOIN zdco d ON d.zdco_id = p.ypos_zdco_id
                JOIN yres y ON y.yres_id = p.ypos_yres_id
                WHERE p.ypos_yres_id = b.reservation_id
                  AND y.yres_resstatus <> 3
                  AND d.zdco_stats_type = 1
                  AND p.ypos_date = b.ydet_date
            ), 0)
            + NVL((
                SELECT SUM(p.ypos_unitprice * p.ypos_quantity)
                FROM ypos p
                JOIN zdco d ON d.zdco_id = p.ypos_zdco_id
                JOIN yres y ON y.yres_id = p.ypos_yres_id
                WHERE p.ypos_yres_id = b.reservation_id
                  AND y.yres_resstatus <> 3
                  AND d.zdco_stats_type = 1
                  AND p.ypos_date = b.departure_date_par
                  AND b.departure_date_par = b.ydet_date + 1
            ), 0),
            'FM9999999990D00',
            'NLS_NUMERIC_CHARACTERS = '',.'''
        ) || ';' ||
        TO_CHAR(NVL(b.profile_fnb_packages, 0), 'FM9999999990D00', 'NLS_NUMERIC_CHARACTERS = '',.''') || ';' ||
        TO_CHAR(NVL(b.profile_stat3_packages, 0), 'FM9999999990D00', 'NLS_NUMERIC_CHARACTERS = '',.''') || ';' ||
        TO_CHAR(NVL(b.profile_stat4_packages, 0), 'FM9999999990D00', 'NLS_NUMERIC_CHARACTERS = '',.''') || ';' ||
        TO_CHAR(NVL(b.profile_stat5_packages, 0), 'FM9999999990D00', 'NLS_NUMERIC_CHARACTERS = '',.''')
        AS csv_line
    FROM base_rows b

    UNION ALL

    SELECT
        1 AS ord,
        p.pm_date AS sort_date,
        p.pm_res_id AS sort_id,
        TO_CHAR(p.pm_date, 'DD.MM.YYYY') || ';' ||
        p.pm_res_id || ';' ||
        ';' ||
        ';' ||
        'No Show PM' || ';' ||
        ';' ||
        ';' ||
        ';' ||
        ';' ||
        ';' ||
        ';' ||
        ';' ||
        ';' ||
        TO_CHAR(0, 'FM9999999990D00', 'NLS_NUMERIC_CHARACTERS = '',.''') || ';' ||
        ';' ||
        REPLACE(NVL(p.pm_market_code, ''), ';', ',') || ';' ||
        ';' ||
        ';' ||
        ';' ||
        ';' ||
        TO_CHAR(0) || ';' ||
        TO_CHAR(0) || ';' ||
        TO_CHAR(0) || ';' ||
        TO_CHAR(0, 'FM9999990D00', 'NLS_NUMERIC_CHARACTERS = '',.''') || ';' ||
        TO_CHAR(0, 'FM9999990D00', 'NLS_NUMERIC_CHARACTERS = '',.''') || ';' ||
        TO_CHAR(NVL(p.accomodation_adr, 0), 'FM9999999990D00', 'NLS_NUMERIC_CHARACTERS = '',.''') || ';' ||
        TO_CHAR(0, 'FM9999999990D00', 'NLS_NUMERIC_CHARACTERS = '',.''') || ';' ||
        TO_CHAR(0, 'FM9999999990D00', 'NLS_NUMERIC_CHARACTERS = '',.''') || ';' ||
        TO_CHAR(0, 'FM9999999990D00', 'NLS_NUMERIC_CHARACTERS = '',.''') || ';' ||
        TO_CHAR(0, 'FM9999999990D00', 'NLS_NUMERIC_CHARACTERS = '',.''')
        AS csv_line
    FROM pm_rows p
) q
ORDER BY q.ord, q.sort_date, q.sort_id
