Below query is to fetch mid-month grade changes (grade_id), but this same SQL can be modified to fetch any mid-month assignment change (e.g. payroll, job, organization etc.)
SELECT
paaf.assignment_number,
(
SELECT
paaf2.effective_start_date
FROM
per_all_assignments_f paaf2
WHERE
paaf2.assignment_id = paaf.assignment_id
AND paaf2.effective_end_date = paaf.effective_start_date - 1
) old_eff_start_date,
(
SELECT
paaf2.effective_end_date
FROM
per_all_assignments_f paaf2
WHERE
paaf2.assignment_id = paaf.assignment_id
AND paaf2.effective_end_date = paaf.effective_start_date - 1
) old_eff_end_date,
(
SELECT
apps.hr_general.decode_grade(
paaf2.grade_id
)
FROM
per_all_assignments_f paaf2
WHERE
paaf2.assignment_id = paaf.assignment_id
AND paaf2.effective_end_date = paaf.effective_start_date - 1
) old_grade,
paaf.effective_start_date current_eff_start_date,
paaf.effective_end_date current_eff_end_date,
apps.hr_general.decode_grade(
paaf.grade_id
) current_grade
FROM
per_all_assignments_f paaf
WHERE
1 = 1
AND paaf.primary_flag = 'Y'
AND paaf.assignment_type = 'E'
AND paaf.payroll_id IS NOT NULL
AND to_char(
trunc(
paaf.last_update_date
), 'MON-YYYY'
) = 'MAR-2025' /*Pass the month for which you want to fetch the changes*/
AND to_char(
paaf.effective_start_date, 'MON-YYYY'
) = (
SELECT
to_char(
effective_end_date, 'MON-YYYY'
)
FROM
per_all_assignments_f paaf1
WHERE
paaf1.assignment_id = paaf.assignment_id
AND paaf1.effective_end_date = paaf.effective_start_date - 1
AND paaf.grade_id <> paaf1.grade_id
);
For more such queries, please provide a comment !!