I am not a developer, but I am trying to learn SQL to write reports for auditing our data. I want to extract empoloyee contact information, because I feel we are missing some CREATE_MIRROR_FLAG. I found the contact information on the per_contact_relationships table, but now I need to find the names of those contacts as well as the CREATE_MIRROR_FLAG and MIRROR_TYPE_DESC. I cannot seem to get the information that I need. I also cannot seem to find the MIRROR_TYPE_DESC or CREATE_MIRROR_FLAG on any of the tables.
, HAOU.name Organization
, HL.meaning Employment_Category
, PAPF.original_date_of_hire Hire_Date
, PPT.user_person_type Status
,( select p1.full_name
from per_all_people_f p1
where p1.person_id = paaf.contact_person_id
and trunc(sysdate) between p1.effective_start_date
and rownum < 2
FROM per_all_people_f PAPF
WHERE PAPF.business_group_id = apps.FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
AND PPT.person_type_id = PAPF.person_type_id
AND PPT.active_flag = 'Y'
AND PAPF.person_id = PAAF.person_id
AND PAPF.person_id = PCR.person_id
AND PAAF.primary_flag = 'Y'
AND PAAF.assignment_type = 'E'
AND PPOS.period_of_service_id = PAAF.period_of_service_id
AND HAOU.organization_id = PAAF.organization_id
AND HL.lookup_type = 'EMP_CAT'
AND HL.lookup_code = PAAF.employment_category
AND sysdate between papf.effective_start_date and papf.effective_end_date
AND sysdate between paaf.effective_start_date and paaf.effective_end_date