Monday, February 07, 2011

HZ_PERSON_PROFILES: Overlapping Start and End Dates

At our site we noticed that some of the person profile records have overlapping start and end dates. This causes problem in some reports such as adjustment register. The query given below can provide list of all such parties that have overlapping record.


SELECT pp_out.party_id
, (SELECT account_number
FROM ar.hz_cust_accounts ca
WHERE ca.party_id = pp_out.party_id
) acct_num
, ( SELECT pty.party_type || ',' || pty.orig_system_reference
FROM ar.hz_parties pty
WHERE pty.party_id = pp_out.party_id
) party_type
, pp_out.person_profile_id
, pp_out.creation_date
, pp_out.effective_start_date
, pp_out.effective_end_date, pp_out.*
FROM ar.hz_person_profiles pp_out
WHERE pp_out.party_id IN
( SELECT party_id
FROM ar.hz_person_profiles pp
WHERE EXISTS
( SELECT 1
FROM ar.hz_person_profiles pp_in
WHERE pp_in.party_id = pp.party_id
AND pp_in.person_profile_id <> pp.person_profile_id
AND ( pp.effective_start_date
BETWEEN pp_in.effective_start_date
AND NVL(pp_in.effective_end_date,SYSDATE+100) OR
NVL(pp.effective_end_date,SYSDATE)
BETWEEN pp_in.effective_start_date
AND NVL(pp_in.effective_end_date,SYSDATE+100)
)
)
)
ORDER BY pp_out.party_id DESC
, pp_out.creation_date


This problem was noticed in Oracle application version 11.5.9 and it still exists to some extent in Oracle Application Rel 12.

No comments:

Post a Comment