Query to retrieve ALL Audit changes

Oracle Cloud HCM

Query to Retrieve All Person\Assignment Audited Changes

    Oracle has provided Audit functionality to track the changes done to the data.

    Although the Audit reports page is good and provides data, it requires proper filters to be chosen to        get the changes for a specific criteria and sometime it is not user friendly to asses the reported data.

    As such we need a handy query to list them all together. and below query helps to extract all changes      against Person and Assignment details on various attributes which are of interest in general.


select * from
(select * from 
(select object
, s1 identification
, s2 start_date
, s3 end_date
, s4 person_id
, s5 created_by
, s6 creation_date
, s7 last_updated_by
, s8 last_update_date
, s9 audit_change_bitmap
, audit_action
, change
, current_value
, prev_value
from
(select * from (
select 'Passport' as Object
  , passport_id s1
  , issue_date s2
  , expiration_date s3
  , person_id s4
  , created_by s5
  , creation_date s6
  , last_updated_by s7
  , last_update_date s8
  ,audit_change_bit_map_ s9
  ,case when audit_action_type_ = 'UPDATE' then 'Update'
  when audit_action_type_ = 'HISTORY' and creation_date = last_update_date then 'Creation' 
  when audit_action_type_ = 'HISTORY' and creation_date <> last_update_date then 'History' 
    when audit_action_type_ = 'INSERT' then 'New'
  when audit_action_type_ = 'DELETE' then 'Deleted' end Audit_Action
 , 'Passport Number' change
 , passport_number current_value
 , LAG(passport_number, 1) OVER (partition by passport_id ORDER BY last_update_date) Prev_value
from fusion.per_passports_  order by last_update_date desc) where current_value <> prev_value or audit_action = 'Deleted'
union
select * from (
select 'Passport' as Object
  , passport_id s1
  , issue_date s2
  , expiration_date s3
  , person_id s4
  , created_by s5
  , creation_date s6
  , last_updated_by s7
  , last_update_date s8
  ,audit_change_bit_map_ s9
  ,case when audit_action_type_ = 'UPDATE' then 'Update'
  when audit_action_type_ = 'HISTORY' and creation_date = last_update_date then 'Creation' 
  when audit_action_type_ = 'HISTORY' and creation_date <> last_update_date then 'History' 
    when audit_action_type_ = 'INSERT' then 'New'
  when audit_action_type_ = 'DELETE' then 'Deleted' end Audit_Action
 , 'Passport Type' change
 , passport_type current_value
 , LAG(passport_type, 1) OVER (partition by passport_id ORDER BY last_update_date) Prev_value
from fusion.per_passports_  order by last_update_date desc)  where current_value <> prev_value or (audit_action = 'Deleted' and prev_value is not null)
union
select * from (
select 'Passport' as Object
  , passport_id s1
  , issue_date s2
  , expiration_date s3
  , person_id s4
  , created_by s5
  , creation_date s6
  , last_updated_by s7
  , last_update_date s8
  ,audit_change_bit_map_ s9
  ,case when audit_action_type_ = 'UPDATE' then 'Update'
  when audit_action_type_ = 'HISTORY' and creation_date = last_update_date then 'Creation' 
  when audit_action_type_ = 'HISTORY' and creation_date <> last_update_date then 'History' 
    when audit_action_type_ = 'INSERT' then 'New'
  when audit_action_type_ = 'DELETE' then 'Deleted' end Audit_Action
 , 'Issue Date' change
 , cast(issue_date as varchar(40)) current_value
 , LAG(cast(issue_date as varchar(40)), 1) OVER (partition by passport_id ORDER BY last_update_date) Prev_value
from fusion.per_passports_  order by last_update_date desc)  where current_value <> prev_value or (audit_action = 'Deleted' and prev_value is not null)
union
select * from (
select 'Passport' as Object
  , passport_id s1
  , issue_date s2
  , expiration_date s3
  , person_id s4
  , created_by s5
  , creation_date s6
  , last_updated_by s7
  , last_update_date s8
  ,audit_change_bit_map_ s9
  ,case when audit_action_type_ = 'UPDATE' then 'Update'
  when audit_action_type_ = 'HISTORY' and creation_date = last_update_date then 'Creation' 
  when audit_action_type_ = 'HISTORY' and creation_date <> last_update_date then 'History' 
    when audit_action_type_ = 'INSERT' then 'New'
  when audit_action_type_ = 'DELETE' then 'Deleted' end Audit_Action
 , 'Expiration Date'  change
 , cast(expiration_date as varchar(40)) current_value
 , LAG(cast(expiration_date as varchar(40)), 1) OVER (partition by passport_id ORDER BY last_update_date) Prev_value
from fusion.per_passports_  order by last_update_date desc) where current_value <> prev_value or (audit_action = 'Deleted' and prev_value is not null)
union
select * from (
select 'Passport' as Object
  , passport_id s1
  , issue_date s2
  , expiration_date s3
  , person_id s4
  , created_by s5
  , creation_date s6
  , last_updated_by s7
  , last_update_date s8
  ,audit_change_bit_map_ s9
  ,case when audit_action_type_ = 'UPDATE' then 'Update'
  when audit_action_type_ = 'HISTORY' and creation_date = last_update_date then 'Creation' 
  when audit_action_type_ = 'HISTORY' and creation_date <> last_update_date then 'History' 
    when audit_action_type_ = 'INSERT' then 'New'
  when audit_action_type_ = 'DELETE' then 'Deleted' end Audit_Action
 , 'Issuing Authority'  change
 , issuing_authority current_value
 , LAG(issuing_authority, 1) OVER (partition by passport_id ORDER BY last_update_date) Prev_value
from fusion.per_passports_  order by last_update_date desc) where current_value <> prev_value or (audit_action = 'Deleted' and prev_value is not null)
union
--change to effective start date
select * from (
select 'Name' as Object
  , person_name_id s1
  , effective_start_date s2
  , effective_end_date s3
  , person_id s4
  , created_by s5
  , creation_date s6
  , last_updated_by s7
  , last_update_date s8
  ,audit_change_bit_map_  s9
  ,case when audit_action_type_ = 'UPDATE' then 
    case substr(audit_change_bit_map_, 2, 2) when '11' then 'Update' 
    when '00' then 'Correction' 
    when '01' then 'Update'  end 
  when audit_action_type_ = 'HISTORY' and creation_date = last_update_date then 'Creation' 
  when audit_action_type_ = 'HISTORY' and creation_date <> last_update_date then 'History' 
    when audit_action_type_ = 'INSERT' then 'New'
  when audit_action_type_ = 'DELETE' then 'Deleted' end Audit_Action
 , 'Effective Start Date' change
 , cast(Effective_Start_Date as varchar(40)) current_value
 , LAG(cast(effective_start_date as varchar(40)), 1) OVER (partition by PERSON_Name_ID, effective_start_date ORDER BY last_update_date) Prev_value
from FUSION.PER_PERSON_NAMES_F_  order by last_update_date desc) where current_value <> prev_value or (audit_action = 'Deleted' and prev_value is not null)
union
-- change to effective end date
select * from (
select 'Name' as Object
  , person_name_id s1
  , effective_start_date s2
  , effective_end_date s3
  , person_id s4
  , created_by s5
  , creation_date s6
  , last_updated_by s7
  , last_update_date s8
  ,audit_change_bit_map_  s9 
  ,case when audit_action_type_ = 'UPDATE' then 
    case substr(audit_change_bit_map_, 2, 2) when '11' then 'Update' 
    when '00' then 'Correction' 
    when '01' then 'Update'  end 
  when audit_action_type_ = 'HISTORY' and creation_date = last_update_date then 'Creation' 
  when audit_action_type_ = 'HISTORY' and creation_date <> last_update_date then 'History' 
    when audit_action_type_ = 'INSERT' then 'New'
  when audit_action_type_ = 'DELETE' then 'Deleted' end Audit_Action
 , 'Effective End Date' change
 , cast(Effective_End_Date as varchar(40)) current_value
 , LAG(cast(effective_end_date as varchar(40)), 1) OVER (partition by PERSON_Name_ID, effective_start_date ORDER BY last_update_date) Prev_value
from FUSION.PER_PERSON_NAMES_F_  order by last_update_date desc) where current_value <> prev_value or (audit_action = 'Deleted' and prev_value is not null)
union
--change to last name
select * from (
select 'Name' as Object
  , person_name_id s1
  , effective_start_date s2
  , effective_end_date s3
  , person_id s4
  , created_by s5
  , creation_date s6
  , last_updated_by s7
  , last_update_date s8
  ,audit_change_bit_map_  s9 
  ,case when audit_action_type_ = 'UPDATE' then 
    case substr(audit_change_bit_map_, 2, 2) when '11' then 'Update' 
    when '00' then 'Correction' 
    when '01' then 'Update'  end  
  when audit_action_type_ = 'HISTORY' and creation_date = last_update_date then 'Creation' 
  when audit_action_type_ = 'HISTORY' and creation_date <> last_update_date then 'History' 
    when audit_action_type_ = 'INSERT' then 'New'
  when audit_action_type_ = 'DELETE' then 'Deleted' end Audit_Action
 , 'Last Name' change
 , last_name current_value
 , LAG(last_name, 1) OVER (partition by PERSON_Name_ID, effective_start_date ORDER BY last_update_date) Prev_value
from FUSION.PER_PERSON_NAMES_F_  order by last_update_date desc) where current_value <> prev_value or (audit_action = 'Deleted' and prev_value is not null)
union
--change to first name
select * from (
select 'Name' as Object
  , person_name_id s1
  , effective_start_date s2
  , effective_end_date s3
  , person_id s4
  , created_by s5
  , creation_date s6
  , last_updated_by s7
  , last_update_date s8
  ,audit_change_bit_map_  s9
  ,case when audit_action_type_ = 'UPDATE' then 
    case substr(audit_change_bit_map_, 2, 2) when '11' then 'Update' 
    when '00' then 'Correction' 
    when '01' then 'Update'  end  
  when audit_action_type_ = 'HISTORY' and creation_date = last_update_date then 'Creation' 
  when audit_action_type_ = 'HISTORY' and creation_date <> last_update_date then 'History' 
    when audit_action_type_ = 'INSERT' then 'New'
  when audit_action_type_ = 'DELETE' then 'Deleted' end Audit_Action
 , 'First Name'  change
 , first_name current_value
 , LAG(first_name, 1) OVER (partition by PERSON_Name_ID, effective_start_date ORDER BY last_update_date) Prev_value
from FUSION.PER_PERSON_NAMES_F_  order by last_update_date desc) where current_value <> prev_value or (audit_action = 'Deleted' and prev_value is not null)
union
--change to middle name
select * from (
select 'Name' as Object
  , person_name_id s1
  , effective_start_date s2
  , effective_end_date s3
  , person_id s4
  , created_by s5
  , creation_date s6
  , last_updated_by s7
  , last_update_date s8
  ,audit_change_bit_map_  s9
  ,case when audit_action_type_ = 'UPDATE' then 
    case substr(audit_change_bit_map_, 2, 2) when '11' then 'Update' 
    when '00' then 'Correction' 
    when '01' then 'Update'  end 
  when audit_action_type_ = 'HISTORY' and creation_date = last_update_date then 'Creation' 
  when audit_action_type_ = 'HISTORY' and creation_date <> last_update_date then 'History' 
    when audit_action_type_ = 'INSERT' then 'New'
  when audit_action_type_ = 'DELETE' then 'Deleted' end Audit_Action
 , 'Middle Name' change
 , middle_names current_value
 , LAG(middle_names, 1) OVER (partition by PERSON_Name_ID, effective_start_date ORDER BY last_update_date) Prev_value
from FUSION.PER_PERSON_NAMES_F_  order by last_update_date desc) where current_value <> prev_value or (audit_action = 'Deleted' and prev_value is not null)
union
--change to title
select * from (
select 'Name' as Object
  , person_name_id s1
  , effective_start_date s2
  , effective_end_date s3
  , person_id s4
  , created_by s5
  , creation_date s6
  , last_updated_by s7
  , last_update_date s8
  ,audit_change_bit_map_  s9
  ,case when audit_action_type_ = 'UPDATE' then 
    case substr(audit_change_bit_map_, 2, 2) when '11' then 'Update' 
    when '00' then 'Correction' 
    when '01' then 'Update'  end 
  when audit_action_type_ = 'HISTORY' and creation_date = last_update_date then 'Creation' 
  when audit_action_type_ = 'HISTORY' and creation_date <> last_update_date then 'History' 
  when audit_action_type_ = 'INSERT' then 'New'
    when audit_action_type_ = 'INSERT' then 'New'
  when audit_action_type_ = 'DELETE' then 'Deleted' end Audit_Action
 , 'Title' change
 , title current_value
 , LAG(title, 1) OVER (partition by PERSON_Name_ID, effective_start_date ORDER BY last_update_date) Prev_value
from FUSION.PER_PERSON_NAMES_F_  order by last_update_date desc) where current_value <> prev_value or (audit_action = 'Deleted' and prev_value is not null)
union
--change to effective start date
select * from (
select 'Address' as Object
  , address_id s1
  , effective_start_date s2
  , effective_end_date s3
  , (select  max(person_id) from PER_PERSON_ADDR_USAGES_F_ pauf where pauf.address_id = address_id) s4
  , created_by s5
  , creation_date s6
  , last_updated_by s7
  , last_update_date s8
  ,audit_change_bit_map_ s9
  ,case when audit_action_type_ = 'UPDATE' then 
    case substr(audit_change_bit_map_, 2, 2) when '11' then 'Update' 
    when '00' then 'Correction' 
    when '01' then 'Update'  end 
  when audit_action_type_ = 'HISTORY' and creation_date = last_update_date then 'Creation' 
  when audit_action_type_ = 'HISTORY' and creation_date <> last_update_date then 'History' 
    when audit_action_type_ = 'INSERT' then 'New'
  when audit_action_type_ = 'DELETE' then 'Deleted' end Audit_Action
 , 'Effective Start Date' change
 , cast(Effective_Start_Date as varchar(40)) current_value
 , LAG(cast(effective_start_date as varchar(40)), 1) OVER (partition by address_ID, effective_start_date ORDER BY last_update_date) Prev_value
from FUSION.per_addresses_f_  order by last_update_date desc) where current_value <> prev_value or (audit_action = 'Deleted' and prev_value is not null)
union
-- change to effective end date
select * from (
select 'Address' as Object
  , address_id s1
  , effective_start_date s2
  , effective_end_date s3
  , (select  max(person_id) from PER_PERSON_ADDR_USAGES_F_ pauf where pauf.address_id = address_id) s4
  , created_by s5
  , creation_date s6
  , last_updated_by s7
  , last_update_date s8
  ,audit_change_bit_map_ s9 
  ,case when audit_action_type_ = 'UPDATE' then 
    case substr(audit_change_bit_map_, 2, 2) when '11' then 'Update' 
    when '00' then 'Correction' 
    when '01' then 'Update'  end 
  when audit_action_type_ = 'HISTORY' and creation_date = last_update_date then 'Creation' 
  when audit_action_type_ = 'HISTORY' and creation_date <> last_update_date then 'History' 
    when audit_action_type_ = 'INSERT' then 'New'
  when audit_action_type_ = 'DELETE' then 'Deleted' end Audit_Action
 ,  'Effective End Date'  change
 , cast(Effective_End_Date as varchar(40)) current_value
 , LAG(cast(effective_end_date as varchar(40)), 1) OVER (partition by address_ID, effective_start_date ORDER BY last_update_date) Prev_value
from FUSION.per_addresses_f_  order by last_update_date desc) where current_value <> prev_value or (audit_action = 'Deleted' and prev_value is not null)
union
select * from (
select 'Address' as Object
  , address_id s1
  , effective_start_date s2
  , effective_end_date s3
  , (select  max(person_id) from PER_PERSON_ADDR_USAGES_F_ pauf where pauf.address_id = address_id) s4
  , created_by s5
  , creation_date s6
  , last_updated_by s7
  , last_update_date s8
  ,audit_change_bit_map_ s9 
  ,case when audit_action_type_ = 'UPDATE' then 
    case substr(audit_change_bit_map_, 2, 2) when '11' then 'Update' 
    when '00' then 'Correction' 
    when '01' then 'Update'  end 
  when audit_action_type_ = 'HISTORY' and creation_date = last_update_date then 'Creation' 
  when audit_action_type_ = 'HISTORY' and creation_date <> last_update_date then 'History' 
    when audit_action_type_ = 'INSERT' then 'New'
  when audit_action_type_ = 'DELETE' then 'Deleted' end Audit_Action
 , 'Address Line 1' change
 , ADDRESS_LINE_1 current_value
 , LAG(ADDRESS_LINE_1, 1) OVER (partition by address_id, effective_start_date ORDER BY last_update_date) Prev_value
from fusion.per_addresses_f_  order by last_update_date desc) where current_value <> prev_value or (audit_action = 'Deleted' and prev_value is not null)
union
select * from (
select 'Address' as Object
  , address_id s1
  , effective_start_date s2
  , effective_end_date s3
  , (select  max(person_id) from PER_PERSON_ADDR_USAGES_F_ pauf where pauf.address_id = address_id) s4
  , created_by s5
  , creation_date s6
  , last_updated_by s7
  , last_update_date s8
  ,audit_change_bit_map_ s9
  ,case when audit_action_type_ = 'UPDATE' then 
    case substr(audit_change_bit_map_, 2, 2) when '11' then 'Update' 
    when '00' then 'Correction' 
    when '01' then 'Update'  end 
  when audit_action_type_ = 'HISTORY' and creation_date = last_update_date then 'Creation' 
  when audit_action_type_ = 'HISTORY' and creation_date <> last_update_date then 'History' 
    when audit_action_type_ = 'INSERT' then 'New'
  when audit_action_type_ = 'DELETE' then 'Deleted' end Audit_Action
 , 'Address Line 2' change
 , ADDRESS_LINE_2 current_value
 , LAG(ADDRESS_LINE_2, 1) OVER (partition by address_id, effective_start_date ORDER BY last_update_date) Prev_value
from fusion.per_addresses_f_  order by last_update_date desc) where current_value <> prev_value or (audit_action = 'Deleted' and prev_value is not null)
union
select * from (
select 'Address' as Object
  , address_id s1
  , effective_start_date s2
  , effective_end_date s3
  , (select  max(person_id) from PER_PERSON_ADDR_USAGES_F_ pauf where pauf.address_id = address_id) s4
  , created_by s5
  , creation_date s6
  , last_updated_by s7
  , last_update_date s8
  ,audit_change_bit_map_ s9
  ,case when audit_action_type_ = 'UPDATE' then 
    case substr(audit_change_bit_map_, 2, 2) when '11' then 'Update' 
    when '00' then 'Correction' 
    when '01' then 'Update'  end 
  when audit_action_type_ = 'HISTORY' and creation_date = last_update_date then 'Creation' 
  when audit_action_type_ = 'HISTORY' and creation_date <> last_update_date then 'History' 
    when audit_action_type_ = 'INSERT' then 'New'
  when audit_action_type_ = 'DELETE' then 'Deleted' end Audit_Action
 , 'Town or City' change
 , TOWN_OR_CITY current_value
 , LAG(TOWN_OR_CITY, 1) OVER (partition by address_id, effective_start_date ORDER BY last_update_date) Prev_value
from fusion.per_addresses_f_  order by last_update_date desc) 
where current_value <> prev_value or (audit_action = 'Deleted' and prev_value is not null)
union
select * from (
select 'Address' as Object
  , address_id s1
  , effective_start_date s2
  , effective_end_date s3
  , (select  max(person_id) from PER_PERSON_ADDR_USAGES_F_ pauf where pauf.address_id = address_id) s4
  , created_by s5
  , creation_date s6
  , last_updated_by s7
  , last_update_date s8
  ,audit_change_bit_map_ s9 
  ,case when audit_action_type_ = 'UPDATE' then 
    case substr(audit_change_bit_map_, 2, 2) when '11' then 'Update' 
    when '00' then 'Correction' 
    when '01' then 'Update'  end 
  when audit_action_type_ = 'HISTORY' and creation_date = last_update_date then 'Creation' 
  when audit_action_type_ = 'HISTORY' and creation_date <> last_update_date then 'History' 
    when audit_action_type_ = 'INSERT' then 'New'
  when audit_action_type_ = 'DELETE' then 'Deleted' end Audit_Action
 , 'Region1' change
 , REGION_1 current_value
 , LAG(REGION_1, 1) OVER (partition by address_id, effective_start_date ORDER BY last_update_date) Prev_value
from fusion.per_addresses_f_  order by last_update_date desc) where current_value <> prev_value or (audit_action = 'Deleted' and prev_value is not null)
union
select * from (
select 'Address' as Object
  , address_id s1
  , effective_start_date s2
  , effective_end_date s3
  , (select  max(person_id) from PER_PERSON_ADDR_USAGES_F_ pauf where pauf.address_id = address_id) s4
  , created_by s5
  , creation_date s6
  , last_updated_by s7
  , last_update_date s8
  ,audit_change_bit_map_ s9 
  ,case when audit_action_type_ = 'UPDATE' then 
    case substr(audit_change_bit_map_, 2, 2) when '11' then 'Update' 
    when '00' then 'Correction' 
    when '01' then 'Update'  end 
  when audit_action_type_ = 'HISTORY' and creation_date = last_update_date then 'Creation' 
  when audit_action_type_ = 'HISTORY' and creation_date <> last_update_date then 'History' 
    when audit_action_type_ = 'INSERT' then 'New'
  when audit_action_type_ = 'DELETE' then 'Deleted' end Audit_Action
 , 'Region2' change
 , REGION_2 current_value
 , LAG(REGION_2, 1) OVER (partition by address_id, effective_start_date ORDER BY last_update_date) Prev_value
from fusion.per_addresses_f_  order by last_update_date desc) where current_value <> prev_value or (audit_action = 'Deleted' and prev_value is not null)
union
select * from (
select 'Address' as Object
  , address_id s1
  , effective_start_date s2
  , effective_end_date s3
  , (select  max(person_id) from PER_PERSON_ADDR_USAGES_F_ pauf where pauf.address_id = address_id) s4
  , created_by s5
  , creation_date s6
  , last_updated_by s7
  , last_update_date s8
  ,audit_change_bit_map_ s9 
  ,case when audit_action_type_ = 'UPDATE' then 
    case substr(audit_change_bit_map_, 2, 2) when '11' then 'Update' 
    when '00' then 'Correction' 
    when '01' then 'Update'  end 
  when audit_action_type_ = 'HISTORY' and creation_date = last_update_date then 'Creation' 
  when audit_action_type_ = 'HISTORY' and creation_date <> last_update_date then 'History' 
    when audit_action_type_ = 'INSERT' then 'New'
  when audit_action_type_ = 'DELETE' then 'Deleted' end Audit_Action
 , 'Postal Code' change
 , POSTAL_CODE current_value
 , LAG(POSTAL_CODE, 1) OVER (partition by address_id, effective_start_date ORDER BY last_update_date) Prev_value
from fusion.per_addresses_f_  order by last_update_date desc) 
where current_value <> prev_value or (audit_action = 'Deleted' and prev_value is not null)
union
select * from (
  select 'Person Start Date' as Object
  , person_id s1
  , effective_start_date s2
  , effective_end_date s3
  , person_id s4
  , created_by s5
  , creation_date s6
  , last_updated_by s7
  , last_update_date s8
  ,audit_change_bit_map_  s9
  ,case when audit_action_type_ = 'UPDATE' then 
    case substr(audit_change_bit_map_, 2, 2) when '11' then 'Update' 
    when '00' then 'Correction' 
    when '01' then 'Update'  end  
  when audit_action_type_ = 'HISTORY' and creation_date = last_update_date then 'Creation' 
  when audit_action_type_ = 'HISTORY' and creation_date <> last_update_date then 'History' 
    when audit_action_type_ = 'INSERT' then 'New'
  when audit_action_type_ = 'DELETE' then 'Deleted' end Audit_Action
 , 'Start Date'  change
 , to_CHAR(START_DATE,'DD-MM-YYYY') current_value
 , to_CHAR(LAG(START_DATE, 1) OVER (partition by person_id, effective_start_date ORDER BY last_update_date),'DD-MM-YYYY')  Prev_value
from FUSION.PER_ALL_PEOPLE_F_  order by last_update_date desc) 
where current_value <> prev_value or (audit_action = 'Deleted' and prev_value is not null)
union
select * from (
select 'Manager' as Object
  , ASSIGNMENT_SUPERVISOR_ID s1
  , effective_start_date s2
  , effective_end_date s3
  , person_id s4
  , created_by s5
  , creation_date s6
  , last_updated_by s7
  , last_update_date s8
  , audit_change_bit_map_ s9
  ,case when audit_action_type_ = 'UPDATE' then 'Update'
  when audit_action_type_ = 'HISTORY' and creation_date = last_update_date then 'Creation' 
  when audit_action_type_ = 'HISTORY' and creation_date <> last_update_date then 'History' 
    when audit_action_type_ = 'INSERT' then 'New'
  when audit_action_type_ = 'DELETE' then 'Deleted' end Audit_Action
 , 'Manager' change
 , TO_CHAR(ASSIGNMENT_SUPERVISOR_ID) current_value
 , TO_CHAR(LAG(ASSIGNMENT_SUPERVISOR_ID, 1) OVER (partition by assignment_id ORDER BY last_update_date)) Prev_value
from fusion.PER_ASSIGNMENT_SUPERVISORS_F_  
Where audit_action_type_ <> 'DELETE'
order by last_update_date desc) 
where current_value <> prev_value or (audit_action = 'Deleted' and prev_value is not null)
--and Audit_Action = UPPER(:action)
union
select * from (
  select 'Person Start Date' as Object
  , person_id s1
  , effective_start_date s2
  , effective_end_date s3
  , person_id s4
  , created_by s5
  , creation_date s6
  , last_updated_by s7
  , last_update_date s8
  ,audit_change_bit_map_  s9
  ,case when audit_action_type_ = 'UPDATE' then 
    case substr(audit_change_bit_map_, 2, 2) when '11' then 'Update' 
    when '00' then 'Correction' 
    when '01' then 'Update'  end  
  when audit_action_type_ = 'HISTORY' and creation_date = last_update_date then 'Creation' 
  when audit_action_type_ = 'HISTORY' and creation_date <> last_update_date then 'History' 
    when audit_action_type_ = 'INSERT' then 'New'
  when audit_action_type_ = 'DELETE' then 'Deleted' end Audit_Action
 , 'Start Date'  change
 ,  CAST (START_DATE as varchar(40)) current_value
 , CAST (LAG(START_DATE, 1) OVER (partition by person_id, effective_start_date ORDER BY last_update_date) as varchar(40)) Prev_value
from FUSION.PER_ALL_PEOPLE_F_  order by last_update_date desc) 
where current_value <> prev_value or (audit_action = 'Deleted' and prev_value is not null)
union
select * from (
select 'Manager' as Object
  , ASSIGNMENT_SUPERVISOR_ID s1
  , effective_start_date s2
  , effective_end_date s3
  , person_id s4
  , created_by s5
  , creation_date s6
  , last_updated_by s7
  , last_update_date s8
  , audit_change_bit_map_ s9
  ,case when audit_action_type_ = 'UPDATE' then 'Update'
  when audit_action_type_ = 'HISTORY' and creation_date = last_update_date then 'Creation' 
  when audit_action_type_ = 'HISTORY' and creation_date <> last_update_date then 'History' 
    when audit_action_type_ = 'INSERT' then 'New'
  when audit_action_type_ = 'DELETE' then 'Deleted' end Audit_Action
 , 'Manager' change
 , TO_CHAR(ASSIGNMENT_SUPERVISOR_ID) current_value
 , TO_CHAR(LAG(ASSIGNMENT_SUPERVISOR_ID, 1) OVER (partition by assignment_id ORDER BY last_update_date)) Prev_value
from fusion.PER_ASSIGNMENT_SUPERVISORS_F_  
Where audit_action_type_ <> 'DELETE'
order by last_update_date desc) 
where current_value <> prev_value or (audit_action = 'Deleted' and prev_value is not null)
--and Audit_Action = UPPER(:action)
--change to effective start date
union
select * from (
select 'Assignment Effective Start Date' as Object
  , assignment_id s1
  , effective_start_date s2
  , effective_end_date s3
  , person_id s4
  , created_by s5
  , creation_date s6
  , last_updated_by s7
  , last_update_date s8
  ,audit_change_bit_map_  s9
  ,case when audit_action_type_ = 'UPDATE' then 
    case substr(audit_change_bit_map_, 2, 2) when '11' then 'Update' 
    when '00' then 'Correction' 
    when '01' then 'Update'  end 
  when audit_action_type_ = 'HISTORY' and creation_date = last_update_date then 'Creation' 
  when audit_action_type_ = 'HISTORY' and creation_date <> last_update_date then 'History' 
    when audit_action_type_ = 'INSERT' then 'New'
  when audit_action_type_ = 'DELETE' then 'Deleted' end Audit_Action
 , 'Effective Start Date' change
 , cast(Effective_Start_Date as varchar(40)) current_value
 , LAG(cast(effective_start_date as varchar(40)), 1) OVER (partition by assignment_id, effective_start_date ORDER BY last_update_date) Prev_value
from FUSION.PER_ALL_ASSIGNMENTS_M_  order by last_update_date desc) where current_value <> prev_value or (audit_action = 'Deleted' and prev_value is not null)
union
-- change to effective end date
select * from (
select 'Assignment Effective End Date' as Object
  , assignment_id s1
  , effective_start_date s2
  , effective_end_date s3
  , person_id s4
  , created_by s5
  , creation_date s6
  , last_updated_by s7
  , last_update_date s8
  ,audit_change_bit_map_  s9 
  ,case when audit_action_type_ = 'UPDATE' then 
    case substr(audit_change_bit_map_, 2, 2) when '11' then 'Update' 
    when '00' then 'Correction' 
    when '01' then 'Update'  end 
  when audit_action_type_ = 'HISTORY' and creation_date = last_update_date then 'Creation' 
  when audit_action_type_ = 'HISTORY' and creation_date <> last_update_date then 'History' 
    when audit_action_type_ = 'INSERT' then 'New'
  when audit_action_type_ = 'DELETE' then 'Deleted' end Audit_Action
 , 'Effective End Date' change
 , cast(Effective_End_Date as varchar(40)) current_value
 , LAG(cast(effective_end_date as varchar(40)), 1) OVER (partition by assignment_id, effective_start_date ORDER BY last_update_date) Prev_value
from FUSION.PER_ALL_ASSIGNMENTS_M_  order by last_update_date desc) where current_value <> prev_value or (audit_action = 'Deleted' and prev_value is not null)
union
select * from (
select 'Job' as Object
  , job_id s1
  , effective_start_date s2
  , effective_end_date s3
  , assignment_id s4
  , created_by s5
  , creation_date s6
  , last_updated_by s7
  , last_update_date s8
  ,audit_change_bit_map_ s9
  ,case when audit_action_type_ = 'UPDATE' then 'Update'
  when audit_action_type_ = 'HISTORY' and creation_date = last_update_date then 'Creation' 
  when audit_action_type_ = 'HISTORY' and creation_date <> last_update_date then 'History' 
    when audit_action_type_ = 'INSERT' then 'New'
  when audit_action_type_ = 'DELETE' then 'Deleted' end Audit_Action
 , 'Job' change
 , TO_CHAR(job_id) current_value
 , TO_CHAR(LAG(job_id, 1) OVER (partition by assignment_id ORDER BY last_update_date)) Prev_value
from fusion.PER_ALL_ASSIGNMENTS_M_  
Where audit_action_type_ <> 'DELETE'
order by last_update_date desc) 
where current_value <> prev_value or audit_action = 'Deleted'
union
select * from (
select 'Grade' as Object
  , grade_id s1
  , effective_start_date s2
  , effective_end_date s3
  , assignment_id s4
  , created_by s5
  , creation_date s6
  , last_updated_by s7
  , last_update_date s8
  ,audit_change_bit_map_ s9
  ,case when audit_action_type_ = 'UPDATE' then 'Update'
  when audit_action_type_ = 'HISTORY' and creation_date = last_update_date then 'Creation' 
  when audit_action_type_ = 'HISTORY' and creation_date <> last_update_date then 'History' 
    when audit_action_type_ = 'INSERT' then 'New'
  when audit_action_type_ = 'DELETE' then 'Deleted' end Audit_Action
 , 'Grade' change
 , TO_CHAR(grade_id) current_value
 , TO_CHAR(LAG(grade_id, 1) OVER (partition by assignment_id ORDER BY last_update_date)) Prev_value
from fusion.PER_ALL_ASSIGNMENTS_M_  
Where audit_action_type_ <> 'DELETE'
order by last_update_date desc) 
where current_value <> prev_value or audit_action = 'Deleted'
union
select * from (
select 'Location' as Object
  , location_id s1
  , effective_start_date s2
  , effective_end_date s3
  , assignment_id s4
  , created_by s5
  , creation_date s6
  , last_updated_by s7
  , last_update_date s8
  , audit_change_bit_map_ s9
  ,case when audit_action_type_ = 'UPDATE' then 'Update'
  when audit_action_type_ = 'HISTORY' and creation_date = last_update_date then 'Creation' 
  when audit_action_type_ = 'HISTORY' and creation_date <> last_update_date then 'History' 
    when audit_action_type_ = 'INSERT' then 'New'
  when audit_action_type_ = 'DELETE' then 'Deleted' end Audit_Action
 , 'Location' change
 , TO_CHAR(location_id) current_value
 , TO_CHAR(LAG(location_id, 1) OVER (partition by assignment_id ORDER BY last_update_date)) Prev_value
from fusion.PER_ALL_ASSIGNMENTS_M_  
Where audit_action_type_ <> 'DELETE'
order by last_update_date desc) 
where current_value <> prev_value or audit_action = 'Deleted')
where s4 in (nvl(:usernames, s4))   
and audit_action in (:action) and object in (:objects)) audit_info
left join
(select
distinct
ppl.person_number
,ppl.person_id ppid
,names.display_name
,pu.username
from
(select person_number, person_id from fusion.PER_ALL_PEOPLE_F) ppl
left join
(select person_id namepid, display_name, effective_start_date 
from fusion.per_person_names_f where effective_start_date <= current_date and effective_end_date > current_date and name_type = 'GLOBAL') names
on ppl.person_id = names.namepid
left join
(select username, person_id upid from fusion.per_users) pu
on names.namepid = pu.upid) person_id_info
on audit_info.person_id = person_id_info.ppid)

Comments

Post a Comment

Popular posts from this blog

Oracle Cloud HCM Open Ended Absences Update

  Oracle Cloud HCM Open Ended Absences       In Oracle Cloud HCM, when there exist open ended absences like Maternity and Sickness and Employee is terminated, it is seen that there is no end date updated to these open absences as of Termination Date. This leads to ambiguity in reporting and payroll To address this issue, worked on an automation process which will pick such records and put an end date same as the termination date.   Design Steps   1.       Build a BIP report to list the Open ended absences for Terminated employees 2.      Write the Bursting definition to feed the BIP report output to Extract 3.      Desing the ETF template to build the HDL Template for End dating Open Absence 4.      Design a HCM Extract that will take the output fed from BIP Report and Auto Loads as   HDL process 5.   ...
Read more

Oracle HCM Absence Orphan Records

Image
Sometimes We come across a less known Error while submitting an Absence in Oracle Cloud HCM with an Error Code ANC-3405194 Likely caused when  Update of Accrual plan is done after an accrual process has already run till a date in future. OR  Entering accrual via HDL in a previous plan period and missing to run calculate accrual. This is Resolved when we Delete the Orphaned records found by Below Query Query to identify the Orphan Absence Records in the System SELECT  per_accrual_entry_dtl_id,         ppl.person_number       ,         ass.assignment_number   ,         plans_tl.NAME           ,         TYPE                    ,         procd_date FROM    fusion.anc_per_acrl_entry_dtls acrl_dtls ,         fusion.per_all_people_f ppl  ...
Read more
Image
           Deploying an AI agent on Redwood Page                                    1.     N avigate to the AI Agent Studio from the Tools Menu             2.    Select any available delivered AI agent and Copy it to be used by    your business.                         3.     Run it to see all the Profiles and Config are good.         4.     Publish the AI Agent              5.       Navigate to My Client Groups -> Guided Journeys      6.       Create New Journey      7.       Create Draf...
Read more