SQL Query to identify person relationship with Organization

SYSTEM_PERSON_TYPE is a lookup and it’s values can be found under Lookup Type “SYSTEM_PERSON_TYPE”.

select * from FND_LOOKUP_VALUES_VL where LOOKUP_TYPE='SYSTEM_PERSON_TYPE';

Here is the complete list of seeded System Person Types:

LOOKUP_CODE	MEANING	TAG
BENNONWRKR	Benefit Nonworker	-US,-CH,-MX
BNF	Beneficiary	-CH
CANCELED_HIRE	Canceled Hire
CON	Contact
CWK	Contingent Worker
DE_CONSULTANT	Consultant	+DE
DE_FREELANCER	Freelancer	+DE
DE_RETIREE	Retiree paid a pension by the employer	+DE
DP	Domestic Partner	-AE,BH,CH,DE,KW,MX,QA,SA
DPNT	Dependent	-DE,-CH
EMP	Employee
EX_CWK	Ex-contingent Worker
EX_EMP	Ex-employee
EX_NONW	Ex-nonworker
EX_PWK	Ex-pending Worker
FRMR_DP	Former Domestic Partner	-AE,BH,KW,MX,QA,SA
FRMR_FMLY_MMBR	Former Family Member	-DE,-MX
FRMR_SPS	Former Spouse
FR_COMP_DIRECTOR	Company director	+FR
FR_TRAINEE	Trainee	+FR
NONW	Nonworker	-DE,FR,GB,IE,MX,NL
NONW_PAID	Nonworker Paid	-CN,SG,US
NONW_UNPAID	Nonworker Unpaid	-CN,SG,US
ORA_CANDIDATE	Candidate
ORA_CANDIDATE_CWK	Contingent Worker Candidate
ORA_CANDIDATE_EMP	Employee Candidate
ORA_PER_INTERN	Intern	+BR
PENSIONER	Pensioner	-US,-NL,-FR,-DE,-MX
PRJ	Project Person
PRTN	Participant
PWK	Pending Worker
PWK_CWK	Pending Contingent Worker
PWK_EMP	Pending Employee
RETIREE	Retiree	-GB,-NL,-FR,-IE,-MX
SPONSOR	Sponsor	+AE,BH,KW,QA,SA
SRVNG_DP	Surviving Domestic Partner	-AE,BH,CH,DE,KW,MX,QA,SA
SRVNG_DPFM	Surviving Domestic Partner or Family Member	-AE,BH,DE,KW,MX,QA,SA
SRVNG_FMLY_MMBR	Surviving Family Member	-DE,-MX
SRVNG_SPS	Surviving Spouse
VOLUNTEER	Volunteer	-US,-NL,-FR,-DE,-MX

We can also get this information for Persons by using the below query:

select cptusages.PERSON_ID,(case when lookup.Meaning = null then 'Candidate' else lookup.Meaning end) as "Person Type" 
from PER_PERSON_TYPES cpt ,per_person_type_usages_m cptusages,FND_LOOKUP_VALUES_VL lookup 
where cpt.PERSON_TYPE_ID=cptusages.PERSON_TYPE_ID(+) 
AND lookup.LOOKUP_CODE=cpt.SYSTEM_PERSON_TYPE
AND lookup.LOOKUP_TYPE='SYSTEM_PERSON_TYPE'
AND SYSDATE BETWEEN cptusages.effective_start_date AND cptusages.effective_end_date
AND cptusages.EFFECTIVE_LATEST_CHANGE = 'Y'
--and cptusages.system_person_type in ('EMP','CWK','EX_EMP','EX_CWK','ORA_CANDIDATE')
ORDER BY cptusages.creation_date desc

If you like the content, please follow us on LinkedIn, Facebook, and Twitter to get updated with the latest content.