• Post category:BI Publisher
  • Post comments:0 Comments
  • Post last modified:June 15, 2021
  • Reading time:5 mins read
You are currently viewing SQL Query to identify person relationship with Organization
SQL Query to identify person relationship with Organization

In this article we will try to identify the person’s relationship with organization.

A Person record could be created for a Candidate, Employee, Ex-Employee, Contact, Pending Worker, Non-Worker, etc.

This information will be stored in the PER_PERSON_TYPE_USAGES_M table keyed by PERSON_ID. SYSTEM_PERSON_TYPE stores the actual Person type that Person belongs to.

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_CODEMEANINGTAG
BENNONWRKRBenefit Nonworker-US,-CH,-MX
BNFBeneficiary-CH
CANCELED_HIRECanceled Hire
CONContact
CWKContingent Worker
DE_CONSULTANTConsultant+DE
DE_FREELANCERFreelancer+DE
DE_RETIREERetiree paid a pension by the employer+DE
DPDomestic Partner-AE,BH,CH,DE,KW,MX,QA,SA
DPNTDependent-DE,-CH
EMPEmployee
EX_CWKEx-contingent Worker
EX_EMPEx-employee
EX_NONWEx-nonworker
EX_PWKEx-pending Worker
FRMR_DPFormer Domestic Partner-AE,BH,KW,MX,QA,SA
FRMR_FMLY_MMBRFormer Family Member-DE,-MX
FRMR_SPSFormer Spouse
FR_COMP_DIRECTORCompany director+FR
FR_TRAINEETrainee+FR
NONWNonworker-DE,FR,GB,IE,MX,NL
NONW_PAIDNonworker Paid-CN,SG,US
NONW_UNPAIDNonworker Unpaid-CN,SG,US
ORA_CANDIDATECandidate
ORA_CANDIDATE_CWKContingent Worker Candidate
ORA_CANDIDATE_EMPEmployee Candidate
ORA_PER_INTERNIntern+BR
PENSIONERPensioner-US,-NL,-FR,-DE,-MX
PRJProject Person
PRTNParticipant
PWKPending Worker
PWK_CWKPending Contingent Worker
PWK_EMPPending Employee
RETIREERetiree-GB,-NL,-FR,-IE,-MX
SPONSORSponsor+AE,BH,KW,QA,SA
SRVNG_DPSurviving Domestic Partner-AE,BH,CH,DE,KW,MX,QA,SA
SRVNG_DPFMSurviving Domestic Partner or Family Member-AE,BH,DE,KW,MX,QA,SA
SRVNG_FMLY_MMBRSurviving Family Member-DE,-MX
SRVNG_SPSSurviving Spouse
VOLUNTEERVolunteer-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 LinkedInFacebook, and Twitter to get updated with the latest content.