November 11, 2015

Document of records for Contacts

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

No comments:

Post a Comment

Thanks for your comments submitted.,will review and Post soon! by admin.

COALESCE-SQL

Coalesce- return the null values from the expression. It works similar to a case statement where if expression 1 is false then goes to expr...