Member Avatar for gbuxton

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.

SELECT PAAF.assignment_number
, PAPF.full_name
, HAOU.name Organization
, HL.meaning Employment_Category
, PAPF.original_date_of_hire Hire_Date
, PPT.user_person_type Status
, PCR.CONTACT_TYPE
,( 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 p1.effective_end_date
and rownum < 2
) contact
, Papf.employee_number
FROM per_all_people_f PAPF
,per_person_types PPT
,per_all_assignments_f PAAF
,hr_all_organization_units HAOU
,hr_lookups HL
,per_periods_of_service PPOS
,per_contact_relationships pcr
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

  • 2 Contributors
  • 3 Replies
  • 319 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by gbuxton
Member Avatar for gbuxton

I fixed my query and now I have all of the contacts names for each employee, but I still cannot find the table that contains the MIRROR_TYPE_DESC or CREATE_MIRROR_FLAG information.

Member Avatar for debasisdas

Try this

select * from user_tab_cols where column_name like '%COLUMNNAME%';

use escape sequence to ignore _ (under score as that is a wild character for pattern matching.)
pass the column name is upper case or use UPPER function.

Member Avatar for gbuxton

Thanks for the suggestion. I tried and got zero results. I have rac_accnt access do I need apps access to the database? Is is possible the table does not exist even though view2 on the function screen had the field. Or I did something wrong?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.