IER12 Process - Only use current assessment

[CarlyCotton]

Current Behaviour
The IER12 is generating with more than the current assessment for an application.

Expected Behaviour
IER12 is expected to only include records from the current assessment of an application and no previous assessments.

Acceptance Criteria

• Update IER12 to only provide record(s) for the most recent assessment.

[andrewsignori-aot]

New query relying on the application's current assessment

SELECT
    "Application"."id" AS "Application_id",
    "Application"."data" AS "Application_data",
    "Application"."application_number" AS "Application_application_number",
    "Application"."application_status" AS "Application_application_status",
    "Application"."application_status_updated_on" AS "Application_application_status_updated_on",
    "Application"."student_number" AS "Application_student_number",
    "Application"."submitted_date" AS "Application_submitted_date",
    "Application__Application_student"."id" AS "Application__Application_student_id",
    "Application__Application_student"."id" AS "Application__Application_student_id",
    "Application__Application_student"."contact_info" AS "Application__Application_student_contact_info",
    "Application__Application_student"."contact_info" AS "Application__Application_student_contact_info",
    "Application__Application_student"."birth_date" AS "Application__Application_student_birth_date",
    "Application__Application_student"."birth_date" AS "Application__Application_student_birth_date",
    "Application__Application_student"."disability_status" AS "Application__Application_student_disability_status",
    "Application__Application_student"."disability_status" AS "Application__Application_student_disability_status",
    "7248e9d01821ec1f93d588e889b6de0db58860ea"."id" AS "7248e9d01821ec1f93d588e889b6de0db58860ea_id",
    "7248e9d01821ec1f93d588e889b6de0db58860ea"."sin" AS "7248e9d01821ec1f93d588e889b6de0db58860ea_sin",
    "c5413201241a0fe0f36bbefc50d7a082fe3e3bae"."id" AS "c5413201241a0fe0f36bbefc50d7a082fe3e3bae_id",
    "c5413201241a0fe0f36bbefc50d7a082fe3e3bae"."first_name" AS "c5413201241a0fe0f36bbefc50d7a082fe3e3bae_first_name",
    "c5413201241a0fe0f36bbefc50d7a082fe3e3bae"."last_name" AS "c5413201241a0fe0f36bbefc50d7a082fe3e3bae_last_name",
    "25b12f4c26259cb1698db543297e659c8dba2bff"."is_active" AS "25b12f4c26259cb1698db543297e659c8dba2bff_is_active",
    "25b12f4c26259cb1698db543297e659c8dba2bff"."id" AS "25b12f4c26259cb1698db543297e659c8dba2bff_id",
    "85c74a18ece1281c9c5df875a4521eae3d5a3180"."id" AS "85c74a18ece1281c9c5df875a4521eae3d5a3180_id",
    "85c74a18ece1281c9c5df875a4521eae3d5a3180"."restriction_code" AS "85c74a18ece1281c9c5df875a4521eae3d5a3180_restriction_code",
    "85c74a18ece1281c9c5df875a4521eae3d5a3180"."action_type" AS "85c74a18ece1281c9c5df875a4521eae3d5a3180_action_type",
    "Application__Application_studentScholasticStandings"."id" AS "Application__Application_studentScholasticStandings_id",
    "Application__Application_studentScholasticStandings"."change_type" AS "Application__Application_studentScholasticStandings_change_type",
    "Application__Application_programYear"."id" AS "Application__Application_programYear_id",
    "Application__Application_programYear"."program_year" AS "Application__Application_programYear_program_year",
    "Application__Application_currentAssessment"."id" AS "Application__Application_currentAssessment_id",
    "Application__Application_currentAssessment"."assessment_data" AS "Application__Application_currentAssessment_assessment_data",
    "Application__Application_currentAssessment"."assessment_date" AS "Application__Application_currentAssessment_assessment_date",
    "Application__Application_currentAssessment"."trigger_type" AS "Application__Application_currentAssessment_trigger_type",
    "Application__Application_currentAssessment"."workflow_data" AS "Application__Application_currentAssessment_workflow_data",
    "6efd20ea46c98ce0b2dcd18f8170d0ce970383b1"."updated_at" AS "6efd20ea46c98ce0b2dcd18f8170d0ce970383b1_updated_at",
    "6efd20ea46c98ce0b2dcd18f8170d0ce970383b1"."id" AS "6efd20ea46c98ce0b2dcd18f8170d0ce970383b1_id",
    "6efd20ea46c98ce0b2dcd18f8170d0ce970383b1"."disbursement_date" AS "6efd20ea46c98ce0b2dcd18f8170d0ce970383b1_disbursement_date",
    "6efd20ea46c98ce0b2dcd18f8170d0ce970383b1"."date_sent" AS "6efd20ea46c98ce0b2dcd18f8170d0ce970383b1_date_sent",
    "6efd20ea46c98ce0b2dcd18f8170d0ce970383b1"."coe_status" AS "6efd20ea46c98ce0b2dcd18f8170d0ce970383b1_coe_status",
    "6efd20ea46c98ce0b2dcd18f8170d0ce970383b1"."disbursement_schedule_status" AS "3c1528fbafb3a1cd242877d706a05f499cc220c3",
    "366e6c05f09f88b97ef9c32b390220a11e7a1937"."id" AS "366e6c05f09f88b97ef9c32b390220a11e7a1937_id",
    "366e6c05f09f88b97ef9c32b390220a11e7a1937"."value_type" AS "366e6c05f09f88b97ef9c32b390220a11e7a1937_value_type",
    "366e6c05f09f88b97ef9c32b390220a11e7a1937"."value_code" AS "366e6c05f09f88b97ef9c32b390220a11e7a1937_value_code",
    "366e6c05f09f88b97ef9c32b390220a11e7a1937"."value_amount" AS "366e6c05f09f88b97ef9c32b390220a11e7a1937_value_amount",
    "366e6c05f09f88b97ef9c32b390220a11e7a1937"."restriction_amount_subtracted" AS "9e65a2f290b10d8a347fd23045219b4c7f1c571a",
    "ce392940d7911f94cc10fc756d295976ee3a74d6"."id" AS "ce392940d7911f94cc10fc756d295976ee3a74d6_id",
    "ce392940d7911f94cc10fc756d295976ee3a74d6"."disburse_date" AS "ce392940d7911f94cc10fc756d295976ee3a74d6_disburse_date",
    "8e7e19a7a6c9e392c1a005d6c62ee15600e4081a"."updated_at" AS "8e7e19a7a6c9e392c1a005d6c62ee15600e4081a_updated_at",
    "8e7e19a7a6c9e392c1a005d6c62ee15600e4081a"."id" AS "8e7e19a7a6c9e392c1a005d6c62ee15600e4081a_id",
    "b11e448f8496a92ccd3c1ff0b7853d79bf73f5e1"."id" AS "b11e448f8496a92ccd3c1ff0b7853d79bf73f5e1_id",
    "b11e448f8496a92ccd3c1ff0b7853d79bf73f5e1"."error_code" AS "b11e448f8496a92ccd3c1ff0b7853d79bf73f5e1_error_code",
    "75f19a49de2b3f75d1d3830fbef7c1d9d67800ab"."id" AS "75f19a49de2b3f75d1d3830fbef7c1d9d67800ab_id",
    "75f19a49de2b3f75d1d3830fbef7c1d9d67800ab"."study_start_date" AS "75f19a49de2b3f75d1d3830fbef7c1d9d67800ab_study_start_date",
    "75f19a49de2b3f75d1d3830fbef7c1d9d67800ab"."study_end_date" AS "75f19a49de2b3f75d1d3830fbef7c1d9d67800ab_study_end_date",
    "75f19a49de2b3f75d1d3830fbef7c1d9d67800ab"."actual_tuition_costs" AS "75f19a49de2b3f75d1d3830fbef7c1d9d67800ab_actual_tuition_costs",
    "75f19a49de2b3f75d1d3830fbef7c1d9d67800ab"."program_related_costs" AS "75f19a49de2b3f75d1d3830fbef7c1d9d67800ab_program_related_costs",
    "75f19a49de2b3f75d1d3830fbef7c1d9d67800ab"."mandatory_fees" AS "75f19a49de2b3f75d1d3830fbef7c1d9d67800ab_mandatory_fees",
    "75f19a49de2b3f75d1d3830fbef7c1d9d67800ab"."exceptional_expenses" AS "75f19a49de2b3f75d1d3830fbef7c1d9d67800ab_exceptional_expenses",
    "75f19a49de2b3f75d1d3830fbef7c1d9d67800ab"."year_of_study" AS "75f19a49de2b3f75d1d3830fbef7c1d9d67800ab_year_of_study",
    "8a209f3cbe75122dadc3a992aee6af61795045db"."id" AS "8a209f3cbe75122dadc3a992aee6af61795045db_id",
    "8a209f3cbe75122dadc3a992aee6af61795045db"."institution_code" AS "8a209f3cbe75122dadc3a992aee6af61795045db_institution_code",
    "491e7fc83e386ca2e486dd1860803951b712c38c"."id" AS "491e7fc83e386ca2e486dd1860803951b712c38c_id",
    "491e7fc83e386ca2e486dd1860803951b712c38c"."program_name" AS "491e7fc83e386ca2e486dd1860803951b712c38c_program_name",
    "491e7fc83e386ca2e486dd1860803951b712c38c"."credential_type" AS "491e7fc83e386ca2e486dd1860803951b712c38c_credential_type",
    "491e7fc83e386ca2e486dd1860803951b712c38c"."cip_code" AS "491e7fc83e386ca2e486dd1860803951b712c38c_cip_code",
    "491e7fc83e386ca2e486dd1860803951b712c38c"."noc_code" AS "491e7fc83e386ca2e486dd1860803951b712c38c_noc_code",
    "491e7fc83e386ca2e486dd1860803951b712c38c"."sabc_code" AS "491e7fc83e386ca2e486dd1860803951b712c38c_sabc_code",
    "491e7fc83e386ca2e486dd1860803951b712c38c"."completion_years" AS "491e7fc83e386ca2e486dd1860803951b712c38c_completion_years",
    "491e7fc83e386ca2e486dd1860803951b712c38c"."institution_program_code" AS "fabc1b396342a5fdd85a2ac8487a51ff95b7ec4c",
    "491e7fc83e386ca2e486dd1860803951b712c38c"."field_of_study_code" AS "491e7fc83e386ca2e486dd1860803951b712c38c_field_of_study_code",
    "147e76ff8c77acbe2ae3149dbaa69258ce1350be"."id" AS "147e76ff8c77acbe2ae3149dbaa69258ce1350be_id"
FROM
    "sims"."applications" "Application"
    LEFT JOIN "sims"."students" "Application__Application_student" ON "Application__Application_student"."id" = "Application"."student_id"
    LEFT JOIN "sims"."sin_validations" "7248e9d01821ec1f93d588e889b6de0db58860ea" ON "7248e9d01821ec1f93d588e889b6de0db58860ea"."id" = "Application__Application_student"."sin_validation_id"
    LEFT JOIN "sims"."users" "c5413201241a0fe0f36bbefc50d7a082fe3e3bae" ON "c5413201241a0fe0f36bbefc50d7a082fe3e3bae"."id" = "Application__Application_student"."user_id"
    LEFT JOIN "sims"."student_restrictions" "25b12f4c26259cb1698db543297e659c8dba2bff" ON "25b12f4c26259cb1698db543297e659c8dba2bff"."student_id" = "Application__Application_student"."id"
    AND (
        "25b12f4c26259cb1698db543297e659c8dba2bff"."deleted_at" IS NULL
    )
    LEFT JOIN "sims"."restrictions" "85c74a18ece1281c9c5df875a4521eae3d5a3180" ON "85c74a18ece1281c9c5df875a4521eae3d5a3180"."id" = "25b12f4c26259cb1698db543297e659c8dba2bff"."restriction_id"
    LEFT JOIN "sims"."student_scholastic_standings" "Application__Application_studentScholasticStandings" ON "Application__Application_studentScholasticStandings"."application_id" = "Application"."id"
    LEFT JOIN "sims"."program_years" "Application__Application_programYear" ON "Application__Application_programYear"."id" = "Application"."program_year_id"
    LEFT JOIN "sims"."student_assessments" "Application__Application_currentAssessment" ON "Application__Application_currentAssessment"."id" = "Application"."current_assessment_id"
    LEFT JOIN "sims"."disbursement_schedules" "6efd20ea46c98ce0b2dcd18f8170d0ce970383b1" ON "6efd20ea46c98ce0b2dcd18f8170d0ce970383b1"."student_assessment_id" = "Application__Application_currentAssessment"."id"
    LEFT JOIN "sims"."disbursement_values" "366e6c05f09f88b97ef9c32b390220a11e7a1937" ON "366e6c05f09f88b97ef9c32b390220a11e7a1937"."disbursement_schedule_id" = "6efd20ea46c98ce0b2dcd18f8170d0ce970383b1"."id"
    LEFT JOIN "sims"."disbursement_receipts" "ce392940d7911f94cc10fc756d295976ee3a74d6" ON "ce392940d7911f94cc10fc756d295976ee3a74d6"."disbursement_schedule_id" = "6efd20ea46c98ce0b2dcd18f8170d0ce970383b1"."id"
    LEFT JOIN "sims"."disbursement_feedback_errors" "8e7e19a7a6c9e392c1a005d6c62ee15600e4081a" ON "8e7e19a7a6c9e392c1a005d6c62ee15600e4081a"."disbursement_schedule_id" = "6efd20ea46c98ce0b2dcd18f8170d0ce970383b1"."id"
    LEFT JOIN "sims"."ecert_feedback_errors" "b11e448f8496a92ccd3c1ff0b7853d79bf73f5e1" ON "b11e448f8496a92ccd3c1ff0b7853d79bf73f5e1"."id" = "8e7e19a7a6c9e392c1a005d6c62ee15600e4081a"."ecert_feedback_error_id"
    LEFT JOIN "sims"."education_programs_offerings" "75f19a49de2b3f75d1d3830fbef7c1d9d67800ab" ON "75f19a49de2b3f75d1d3830fbef7c1d9d67800ab"."id" = "Application__Application_currentAssessment"."offering_id"
    LEFT JOIN "sims"."institution_locations" "8a209f3cbe75122dadc3a992aee6af61795045db" ON "8a209f3cbe75122dadc3a992aee6af61795045db"."id" = "75f19a49de2b3f75d1d3830fbef7c1d9d67800ab"."location_id"
    LEFT JOIN "sims"."education_programs" "491e7fc83e386ca2e486dd1860803951b712c38c" ON "491e7fc83e386ca2e486dd1860803951b712c38c"."id" = "75f19a49de2b3f75d1d3830fbef7c1d9d67800ab"."program_id"
    LEFT JOIN "sims"."education_programs_offerings" "147e76ff8c77acbe2ae3149dbaa69258ce1350be" ON "147e76ff8c77acbe2ae3149dbaa69258ce1350be"."id" = "75f19a49de2b3f75d1d3830fbef7c1d9d67800ab"."parent_offering_id"
WHERE
    (
        ("Application"."offering_intensity" = $ 1)
        AND ("Application"."application_status" != $ 2)
        AND (
            (
                (
                    (
                        (
                            (
                                "Application__Application_currentAssessment"."assessment_date" >= $ 3
                                AND "Application__Application_currentAssessment"."assessment_date" < $ 4
                            )
                        )
                        AND (
                            (
                                (
                                    (
                                        (
                                            "8a209f3cbe75122dadc3a992aee6af61795045db"."has_integration" = $ 5
                                        )
                                    )
                                )
                            )
                        )
                    )
                )
                OR (
                    (
                        (
                            (
                                (
                                    (
                                        "6efd20ea46c98ce0b2dcd18f8170d0ce970383b1"."updated_at" >= $ 6
                                        AND "6efd20ea46c98ce0b2dcd18f8170d0ce970383b1"."updated_at" < $ 7
                                    )
                                )
                            )
                        )
                        AND (
                            (
                                (
                                    (
                                        (
                                            "8a209f3cbe75122dadc3a992aee6af61795045db"."has_integration" = $ 8
                                        )
                                    )
                                )
                            )
                        )
                    )
                )
                OR (
                    (
                        (
                            (
                                (
                                    (
                                        (
                                            (
                                                "8e7e19a7a6c9e392c1a005d6c62ee15600e4081a"."updated_at" >= $ 9
                                                AND "8e7e19a7a6c9e392c1a005d6c62ee15600e4081a"."updated_at" < $ 10
                                            )
                                        )
                                    )
                                )
                            )
                        )
                        AND (
                            (
                                (
                                    (
                                        (
                                            "8a209f3cbe75122dadc3a992aee6af61795045db"."has_integration" = $ 11
                                        )
                                    )
                                )
                            )
                        )
                    )
                )
            )
        )
    )
ORDER BY
    "Application__Application_currentAssessment_assessment_date" ASC,
    "6efd20ea46c98ce0b2dcd18f8170d0ce970383b1_disbursement_date" ASC -- PARAMETERS: ["Full Time","Edited","2000-06-02T00:00:00.000Z","2000-06-03T00:00:00.000Z",true,"2000-06-02T00:00:00.000Z","2000-06-03T00:00:00.000Z",true,"2000-06-02T00:00:00.000Z","2000-06-03T00:00:00.000Z",true]