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

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.