Home Address changes - Data model/SQL

Michael Grant · May 2023

Hello,

I've worked on a report that includes the employee's former address and current (new) address when a change has been entered in HCM. My SQL skills are kind of crude, so am not surprised that I get duplicate rows returned. If it's possible to decipher the statements below, I will really appreciate any help you might be able to provide.

Note: The specific problem is with multiple name variation appearing, so may be due to the Per_Person_Names_F join. For example, there will be a row with "George Jones" and a second row with "Jones, George W".

select DISTINCT

"PER_ALL_PEOPLE_F"."PERSON_NUMBER" as "PERSON_NUMBER",

"PER_PERSON_NAMES_F"."FULL_NAME" as "NAME",

"PER_PERSON_NAMES_F"."LAST_NAME" as "LAST_NAME",

"PER_PERSON_ADDR_USAGES_F"."PERSON_ID",

"PER_ADDRESSES_F"."ADDRESS_LINE_1" as "ADDRESS_LINE_1",

"PER_ADDRESSES_F"."TOWN_OR_CITY" as "TOWN_OR_CITY",

"PER_ADDRESSES_F"."REGION_2" as "State",

"PER_ADDRESSES_F"."POSTAL_CODE" as "Zip CODE",

TO_CHAR("PER_ADDRESSES_F"."EFFECTIVE_END_DATE", 'DD-MON-YYYY') as "END_DATE",

TO_CHAR("PER_ADDRESSES_F"."LAST_UPDATE_DATE", 'DD-MON-YYYY') as "LAST_DATE",

"PER_ADDRESSES_F"."OBJECT_VERSION_NUMBER" AS "VERSION"

from "FUSION"."PER_ALL_PEOPLE_F" "PER_ALL_PEOPLE_F",

"FUSION"."PER_PERSON_NAMES_F" "PER_PERSON_NAMES_F",

"FUSION"."PER_PERSON_ADDR_USAGES_F"

"PER_PERSON_ADDR_USAGES_F",

"FUSION"."PER_PERSON_TYPE_USAGES_M"

"PER_PERSON_TYPE_USAGES_M",

"FUSION"."PER_PERSON_TYPES" "PER_PERSON_TYPES",

"FUSION"."PER_ADDRESSES_F" "PER_ADDRESSES_F"

where "PER_ALL_PEOPLE_F"."PERSON_ID"="PER_PERSON_NAMES_F"."PERSON_ID"

AND "PER_PERSON_NAMES_F"."NAME_TYPE" = 'US'

AND "PER_PERSON_NAMES_F"."PERSON_ID" = "PER_PERSON_ADDR_USAGES_F"."PERSON_ID"

and "PER_ALL_PEOPLE_F"."PERSON_ID"="PER_PERSON_TYPE_USAGES_M"."PERSON_ID"

AND "PER_PERSON_TYPE_USAGES_M"."PERSON_TYPE_ID" = "PER_PERSON_TYPES"."PERSON_TYPE_ID"

AND "PER_PERSON_TYPES"."SYSTEM_PERSON_TYPE" = 'EMP'

and "PER_PERSON_ADDR_USAGES_F"."ADDRESS_ID" = "PER_ADDRESSES_F"."ADDRESS_ID"

AND "PER_ADDRESSES_F"."LAST_UPDATE_DATE" >=:P_RPT_DATE

ORDER BY "PER_ALL_PEOPLE_F"."PERSON_NUMBER"

SantoshKumarBhairi · May 2023

1) I just ran the same statement in my instance and I dont get any duplicate rows. The best way to debug this type of issues is to include all the ID columns in your select statement like PERSON_ID, ADDRESS_ID, etc.

2) try to add this condition as well :

and sysdate between PER_PERSON_ADDR_USAGES_F.EFFECTIVE_START_DATE and PER_PERSON_ADDR_USAGES_F.EFFECTIVE_END_DATE

Michael Grant · May 2023

Thank you Santosh!

Taking time to rethink my approach, removing the full_name was an effective way to solve the problem. It was partly a data issue because there are history rows where the full name is different (cancelling out the effect of the DISTINCT statement). I substituted the last name instead, which works just fine now.

Best regards, Mike

Darrin N · March 2024

HI Michael,

Can you post your final SQL code? We are in desperate need of a similar report.

Thank you!

Nathan CCC · March 2024

Hi,

A person, person name, person address may have many versions over time. A person may have many addresses at the same point in time and over time. At any point in time one of the many address for a person may be the main address for that person (PER_ALL_PEOPLE_F.MAILING_ADDRESS_ID). If you want the history of both the person names and the person addresses with persons then you need to decide "as at" what point in time otherwise you will get a cartesian product when you join the 3 data sets. For example, for each entity choose the versions now as at today?, the version as at the start of that address?, the version as at the end of the address?, the version as at some other point in time.

For example, current person with each main address over time with version of address usage as at start of address - this is just person and person address without names to keep it simple

select all count(*) over (partition by null) as n
, count(*) over (partition by null, person.person_id) as n_person
, person.*
, person_address_main.*
from 
(--person as at today
	select all 
	persondetailspeo.person_id as person_id
	, persondetailspeo.effective_start_date as person_effective_start_date
	, persondetailspeo.effective_end_date as person_effective_end_date
	, persondetailspeo.person_number as person_number
	, persondetailspeo.mailing_address_id as person_address_id_main
	from per_all_people_f persondetailspeo 
	where 1 = 1
	and ( trunc(sysdate,upper('dd')) between persondetailspeo.effective_start_date and persondetailspeo.effective_end_date )
) person
left outer join 
( --person main address history with the version of the address usage as at the start of the address
	select all
        personaddressusagespeo.person_id as usage_person_id
	, personaddressusagespeo.person_addr_usage_id as usage_id
	, personaddressusagespeo.effective_start_date as usage_effective_start_date
	, personaddressusagespeo.effective_end_date as usage_effective_end_date
	, personaddressusagespeo.address_type
	, addressespeo.address_id as address_id
	, addressespeo.effective_start_date
	, addressespeo.effective_end_date
	, addressespeo.country
	, addressespeo.postal_code
	, addressespeo.long_postal_code
	, addressespeo.town_or_city
	, addressespeo.region_1
	, addressespeo.region_2
	, addressespeo.region_3
	, addressespeo.address_line_1
	, addressespeo.address_line_2
	, addressespeo.address_line_3
	, addressespeo.address_line_4
	, addressespeo.building
	, addressespeo.floor_number
	, addressespeo.created_by as created_by
	, addressespeo.creation_date as created_date
	, addressespeo.last_update_date as last_updated_date
	, addressespeo.last_updated_by as last_updated_by
	from
        --https://docs.oracle.com/en/cloud/saas/human-resources/23c/oedmh/perpersonaddrusagesf-23268.html#perpersonaddrusagesf-23268
	per_person_addr_usages_f personaddressusagespeo
	inner join 
        -- https://docs.oracle.com/en/cloud/saas/human-resources/23c/oedmh/peraddressesf-6976.html#peraddressesf-6976
        per_addresses_f addressespeo
	on (
	(addressespeo.address_id = personaddressusagespeo.address_id)
	and (addressespeo.effective_start_date between personaddressusagespeo.effective_start_date and personaddressusagespeo.effective_end_date)
	)
) person_address_main
on (
person.person_address_id_main = person_address_main.address_id
)
order by null, 1 desc nulls last, 2 desc nulls last
, person.person_id asc nulls last
, person_address_main.address_id asc nulls last
, person_address_main.effective_start_date asc nulls last
, person_address_main.effective_end_date asc nulls last