CREATE OR REPLACE PROCEDURE TRACIT_DW.rpt_mileage_month (p_results IN OUT TRACIT_DW.PKG_REPORTS.RPT_CURSOR, p_begin_date IN VARCHAR, p_firm_number IN NUMBER) IS

    l_facility_name VARCHAR2(65);

BEGIN

    -- get facility name 
    SELECT name
    INTO l_facility_name
    FROM facility
    WHERE facility_number = p_firm_number;
    
    OPEN p_results FOR
        SELECT
            l_facility_name AS facility_name,
            month_id,
            SUM(loaded_mileage) AS total_mileage, 
            AVG(loaded_mileage) AS avg_mileage, 
            COUNT(*) AS total_runs
        FROM
            tracit_dw.ems_data
        WHERE report_period_begin_date = to_date(p_begin_date, 'mm/dd/yyyy')
        AND loaded_mileage < 9999.99
        AND tdh_firm_number = p_firm_number
        GROUP BY 
            month_id;

END;