Query to list the document of records for Contacts-Can modfiy the query and use to list only contacts for the employee:
select
hdei.document_number,
papfc.full_name,
papf.full_name Employee_name,PAPF.EMAIL_ADDRESS
,hdt.system_document_type document_type
, to_char(hdei.date_to,'DD-MON-YYYY')date_to
FROM
hr_document_types hdt,
hr_document_extra_info hdei,
per_ALL_people_f papf,
per_ALL_people_f papfc,
per_contact_relationships pcr
WHERE
hdt.document_type_id= hdei.document_type_id
and papfc.person_id=pcr.contact_PERSON_ID
and papf.person_id =pcr.PERSON_ID
and papfc.person_id=hdei.PERSON_Id
--and papf.employee_number='200343'
-- and papf.business_group_id=101
and papf.current_employee_flag = 'Y'
-- and pcr.DEPENDENT_FLAG= 'Y'
AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
AND TRUNC(SYSDATE) BETWEEN papfc.effective_start_date AND papfc.effective_end_date
-- and trunc(sysdate) between hdei.date_from and hdei.date_to
-- and trunc(sysdate) between pcr.date_start and pcr.date_end
--and hdei.DATE_to =ADD_MONTHS(TRUNC(SYSDATE), 2 )
group by
hdei.document_number,
papfc.full_name,
papf.full_name,PAPF.EMAIL_ADDRESS
,hdt.system_document_type
,hdei.date_from
,hdei.date_to,
hdei.document_number,
pcr.contact_type
select
hdei.document_number,
papfc.full_name,
papf.full_name Employee_name,PAPF.EMAIL_ADDRESS
,hdt.system_document_type document_type
, to_char(hdei.date_to,'DD-MON-YYYY')date_to
FROM
hr_document_types hdt,
hr_document_extra_info hdei,
per_ALL_people_f papf,
per_ALL_people_f papfc,
per_contact_relationships pcr
WHERE
hdt.document_type_id= hdei.document_type_id
and papfc.person_id=pcr.contact_PERSON_ID
and papf.person_id =pcr.PERSON_ID
and papfc.person_id=hdei.PERSON_Id
--and papf.employee_number='200343'
-- and papf.business_group_id=101
and papf.current_employee_flag = 'Y'
-- and pcr.DEPENDENT_FLAG= 'Y'
AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
AND TRUNC(SYSDATE) BETWEEN papfc.effective_start_date AND papfc.effective_end_date
-- and trunc(sysdate) between hdei.date_from and hdei.date_to
-- and trunc(sysdate) between pcr.date_start and pcr.date_end
--and hdei.DATE_to =ADD_MONTHS(TRUNC(SYSDATE), 2 )
group by
hdei.document_number,
papfc.full_name,
papf.full_name,PAPF.EMAIL_ADDRESS
,hdt.system_document_type
,hdei.date_from
,hdei.date_to,
hdei.document_number,
pcr.contact_type
No comments:
Post a Comment
Thanks for your comments submitted.,will review and Post soon! by admin.