<resultMap id="EmployeeTrainingsChangesResultMap" type="employee.EmployeeTrainingChange">


        <result property="status" column="training_status"/>
        <result property="oldValue.employeeId" column="employee_id"/>
        <result property="newValue.employeeId" column="employee_id"/>

        <result property="oldValue.trainingId" column="training_id"/>
        <result property="newValue.trainingId" column="training_id"/>


        <result property="oldValue.trainingName" column="old_training_name"/>
        <result property="oldValue.trainingUrl" column="old_training_url"/>
        <result property="oldValue.trainingDate" column="old_training_date"/>

        <result property="newValue.trainingName" column="training_name"/>
        <result property="newValue.trainingUrl" column="training_url"/>
        <result property="newValue.trainingDate" column="training_date"/>

    </resultMap>


    <select id="getEmployeeTrainingsChanges" parameterType="java.math.BigDecimal" resultMap="EmployeeTrainingsChangesResultMap">
       with training_lag_history as
(
    select sla.audit_id,
           sla.lag_time,
           /*training*/
           (
           case
             when length(max(decode(tr.trail_item_id,700000030041,tr.column_data)))=4 then
                trunc(to_date(max(decode(tr.trail_item_id,700000030041,tr.column_data)),'yyyy'),'yyyy')
             else to_date(max(decode(tr.trail_item_id,700000030041,tr.column_data)),'yyyy-mm-dd') 
           end
           ) old_training_year,           
           max(decode(tr.trail_item_id,700000030042,tr.column_data)) old_training_name,
           max(decode(tr.trail_item_id,700000030043,tr.column_data)) old_training_url,
           to_number(max(decode(tr.trail_item_id,700000030044,tr.column_data))) training_id,
           to_number(max(decode(tr.trail_item_id,700000030045,tr.column_data))) old_trainer_id,
           
           max(decode(tr.trail_item_id,700000030041,1,0)) is_training_year,
           max(decode(tr.trail_item_id,700000030042,1,0)) is_training_name,
           max(decode(tr.trail_item_id,700000030043,1,0)) is_training_url,
           max(decode(tr.trail_item_id,700000030045,1,0)) is_trainer_id           
           
    from
    (
        select
           audit_id,
           timestamp lag_time
        from
        (  
          select
               au.audit_id,
               au.timestamp,
               max(au.timestamp) over (partition by tr.trail_item_id,tr.column_data) max_over_item
            from r_audit au
            inner join r_audit_trail tr on tr.audit_id=au.audit_id   
            where au.item_id=#{value, jdbcType=NUMERIC} 
            and  au.timestamp &lt;= (select sys_extract_utc(cast(em.approved_date as timestamp)) from m_employee em where em.employee_id=#{value, jdbcType=NUMERIC})
            and tr.trail_item_id in (/*training*/700000030044)
         )
         where timestamp=max_over_item
     ) sla
     inner join r_audit_trail tr on tr.audit_id=sla.audit_id  
           and  tr.trail_item_id  in (
                                      /*training*/700000030041,700000030042,700000030043,700000030044,700000030045
                                     )
     group by sla.audit_id,sla.lag_time
)
select
    coalesce(met.training_id,tlh.training_id) training_id,
    (
    case
        when met.training_id is null then 'D'                                      -- deleted
        when tlh.training_id is null then 'N'                                      -- new
        when 
             (
                   (tlh.is_training_year &lt;&gt; 0 and
                     ((met.is_auto='N' and trunc(nvl(tlh.old_training_year,sysdate),'YYYY') &lt;&gt; trunc(nvl(met.training_date,sysdate),'YYYY'))
                       or (met.is_auto='Y' and nvl(tlh.old_training_year,sysdate) &lt;&gt; nvl(met.training_date,sysdate))
                     )
                   )             
                or (tlh.is_training_name &lt;&gt; 0 and nvl(tlh.old_training_name,'-') &lt;&gt; nvl(met.training_name,'-'))
                or (tlh.is_training_url &lt;&gt; 0 and nvl(tlh.old_training_url,'-') &lt;&gt; nvl(met.training_url,'-'))
                or (tlh.is_trainer_id &lt;&gt; 0 and nvl(tlh.old_trainer_id,0) &lt;&gt; nvl(met.trainer_id,0))          
             )
        then 'M'                                                                -- modified
        else 'E'                                                                -- equal
    end
    ) training_status ,
    decode(tlh.is_training_year,0,met.training_date,tlh.old_training_year) old_training_date,
    decode(tlh.is_training_name,0,met.training_name,tlh.old_training_name) old_training_name,
    decode(tlh.is_training_url,0,met.training_url,tlh.old_training_url) old_training_url,
    decode(tlh.is_trainer_id,0,met.trainer_id,tlh.old_trainer_id) old_trainer_id,
    met.training_date,
    met.training_name,
    met.training_url,
    met.trainer_id   
from training_lag_history tlh
right join (select * from m_employee_training where employee_id=#{value, jdbcType=NUMERIC} and is_auto = 'N') met on met.training_id=tlh.training_id
    </select>