HDL – Query to get Assignment Grade Steps

SELECT 'MERGE' "METADATA"
      ,'AssignmentGradeSteps' "AssignmentGradeSteps"
      ,pagsf.EFFECTIVE_START_DATE "EffectiveStartDate"
      ,pagsf.EFFECTIVE_END_DATE "EffectiveEndDate"
      ,pagsf.ASSIGN_GRADE_STEP_ID "AssignGradeStepId"
      ,paam.assignment_number "AssignmentNumber"
      ,pav.ACTION_CODE "ActionCode"
      ,parv.ACTION_REASON_CODE "ReasonCode"
      ,pgsfv.name "GradeStepName"
      ,NULL "NewGradeStepName" --> to be supplied
 FROM PER_ASSIGN_GRADE_STEPS_F pagsf,
      PER_GRADE_STEPS_F_VL pgsfv,
      PER_ALL_ASSIGNMENTS_M paam,
      PER_ACTION_OCCURRENCES pao,
      PER_ACTIONS_VL pav,
      PER_ACTION_REASONS_VL parv
WHERE pagsf.GRADE_STEP_ID = pgsfv.GRADE_STEP_ID
  AND TRUNC(SYSDATE) BETWEEN pagsf.effective_start_date AND pagsf.effective_end_date
  AND TRUNC(SYSDATE) BETWEEN pgsfv.effective_start_date AND pgsfv.effective_end_date
  AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date
  AND pagsf.assignment_id = paam.assignment_id
  AND paam.assignment_type = 'E'
  AND paam.effective_latest_change = 'Y'
  AND pagsf.ACTION_OCCURRENCE_ID = pao.ACTION_OCCURRENCE_ID
  AND pav.action_id = pao.action_id
  AND parv.action_reason_id = pao.action_reason_id