promptedReport {
    name="Phase Score Extract for IRS"
    enterpriseVersion=""
    description="IRS is requesting for a one time Data Extract for an analytic report."
    SQL="""
SELECT fname,
         mi,
         lname,
         vacancy,
         bod_lvl_2,
         pay_plan,
         grade,
         snum,
         vacancy_announcement_type_list,
         vacancy_series_list,
         vacancy_area_of_consideration,
         application_status_display,
         application_custom_status,
         application_status_display_sa,
         application_custom_status_sa,
         vacancy_status_display,
         open_date,
         close_date,
         certdate,
         select_date,
         eod_date,
         total_score,
         MAX (phase_1_score),
         MAX (phase_2_score)
    FROM (SELECT a.fname,
                 a.mi,
                 a.lname,
                 v.vacancy,
                 vo.udf_field_value AS bod_lvl_2,
                 uv.pay_plan,
                 ug.grade,
                 s.snum,
                 vacancy_announcement_type_list,
                 vacancy_series_list,
                 vacancy_area_of_consideration,
                 application_status_display,
                 application_custom_status,
                 application_status_display_sa,
                 application_custom_status_sa,
                 vacancy_status_display,
                 v.open_date,
                 v.close_date,
                 fjc.issue_date certdate,
                 car.select_date,
                 car.eod_date,
                 car.total_score,
                 CASE WHEN phase_type = 0 THEN cajp.phase_score END
                    phase_1_score,
                 CASE WHEN phase_type = 41 THEN cajp.phase_score END
                    phase_2_score
            FROM TREAS_HM_DATA.cert_applicant_results car
                 JOIN TREAS_HM_DATA.job_cert jc
                    ON (    jc.org_id = car.org_id
                        AND jc.certid = car.certid
                        AND jc.jnum = car.jnum)
                 JOIN TREAS_HM_DATA.final_job_cert fjc
                    ON (    jc.org_id = fjc.org_id
                        AND jc.certid = fjc.certid
                        AND jc.jnum = fjc.jnum
                        AND jc.seq_num = fjc.seq_num)
                 JOIN TREAS_HM_DATA.cert_app_job_phase cajp
                    ON (    cajp.org_id = car.org_id
                        AND cajp.certid = car.certid
                        AND cajp.jnum = car.jnum
                        AND cajp.grade_id = car.grade_id
                        AND cajp.aidx = car.aidx)
                 JOIN TREAS_HM_DATA.vacancy_phase vp
                    ON (    cajp.vacancy_phase_id = vp.vacancy_phase_id
                        AND cajp.org_id = vp.org_id
                        AND cajp.jnum = vp.jnum)
                 JOIN TREAS_HM_DATA.applicant a
                    ON (car.aidx = a.aidx)
                 JOIN TREAS_HM_DATA.usa_grade ug
                    ON (car.grade_id = ug.id)
                 JOIN TREAS_HM_DATA.vacancy v
                    ON (car.org_id = v.org_id AND car.jnum = v.jnum)
                 LEFT JOIN treas_appsdba.adhoc_g_vacancy agv
                    ON (    car.org_id = agv.fk_v_organization_id
                        AND car.jnum = agv.vacancy_id)
                 JOIN TREAS_HM_DATA.usa_vacancy uv
                    ON (uv.org_id = v.org_id AND uv.jnum = v.jnum)
                 LEFT JOIN treas_appsdba.adhoc_g_application aga
                    ON (    car.org_id = aga.fk_an_organization_id
                        AND car.jnum = aga.fk_an_vacancy_id
                        AND aga.fk_an_cert_id = car.certid
                        AND aga.application_grade = ug.grade
                        AND aga.fk_an_applicant_id = car.aidx)
                 LEFT JOIN treas_appsdba.adhoc_g_vacancy_options vo
                    ON (    vo.fk_vo_organization_id = v.org_id
                        AND vo.fk_vo_vacancy_id = v.jnum
                        AND udf_field_label = ''BOD-LEVEL 2'')
                 LEFT JOIN TREAS_HM_DATA.series s
                    ON (s.series_id = car.selected_series_id)
           WHERE jc.cert_status = 0 AND car.org_id = #orgId# AND uv.pay_plan = ''IR'')
   WHERE certdate BETWEEN #startDate#
                      AND #endDate#
GROUP BY fname,
         mi,
         lname,
         vacancy,
         bod_lvl_2,
         pay_plan,
         grade,
         snum,
         vacancy_announcement_type_list,
         vacancy_series_list,
         vacancy_area_of_consideration,
         application_status_display,
         application_custom_status,
         application_status_display_sa,
         application_custom_status_sa,
         vacancy_status_display,
         open_date,
         close_date,
         certdate,
         select_date,
         eod_date,
         total_score
ORDER BY 4,
         3,
         1,
         2
      """
    parameters {
      startDate {
        prompt="Report Start Date (format: MM/DD/YYYY)"
        TYPE="DATE"
        helpText="Select first day of each quarter"
        sqlQuery=""
        selectMultiple=FALSE
        mandatory=TRUE
      }
      endDate {
        prompt="Report End Date (format: MM/DD/YYYY)"
        TYPE="DATE"
        helpText="Select last day of each quarter"
        sqlQuery=""
        selectMultiple=FALSE
        mandatory=TRUE
      }
      orgId {
        prompt="Please select the organization"
        TYPE="STRING"
        helpText=""
        sqlQuery="select distinct org_id from xyu_dev391_setdata.cert_applicant_results"
        selectMultiple=FALSE
        mandatory=FALSE
      }
      grade {
        prompt="Choose a Grade"
        TYPE="STRING"
        helpText="The report is restricted on selected Grade"
        sqlQuery="select grade from xyu_dev391_setdata.usa_grade where id != -9;"
        selectMultiple=FALSE
              mandatory=FALSE
      }
    }
  }