Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- "
- 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=TRUE
- }
- 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=TRUE
- }
- }
- }
- "
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement