PER Employee listing (Hong Kong)

Description
Categories: BI Publisher
Columns: Name, Full Name, Hkid, Passport Number, Work Permit Number, Work Permit Expiry Date, Date Of Birth, Date Start ...
Application: Human Resources
Source: Employee listing (Hong Kong) (XML)
Short Name: PERHKCEL_XML
DB package: PER_PERHKCEL_XMLP_PKG
select hhle.name
      ,ppf.per_information6                                           full_name
      ,ppf.national_identifier                                         hkid
      ,ppf.per_information1                                           passport_number
      ,ppf.per_information3                                           work_permit_number
      ,to_char(to_date(ppf.per_information4,'YYYY/MM/DD HH24:MI:SS'),'DD-MON-YYYY') work_permit_expiry_date 
      ,to_char(ppf.date_of_birth,'DD-MON-YYYY') date_of_birth
      ,to_char(pps.date_start,'DD-MON-YYYY')     date_start
from   per_periods_of_service        pps
          ,per_people_f                         ppf
          ,per_assignments_f               paf
          ,hr_soft_coding_keyflex       hscf
          ,hr_hk_legal_employer_v      hhle
where pps.person_id                             = paf.person_id
   and ppf.person_id                              = paf.person_id
   and (pps.actual_termination_date        is null
           or (pps.actual_termination_date    is not null 
                and :p_effective_date < pps.actual_termination_date))
   and paf.soft_coding_keyflex_id       = hscf.soft_coding_keyflex_id
   and hscf.segment1      	           = to_char(hhle.gre_id)
   and paf.assignment_type                 = 'E'
   and paf.primary_flag                         = 'Y'     
   and ppf.business_group_id               = :p_business_group_id 
   and pps.period_of_service_id = paf.period_of_service_id 
   and :p_effective_date between paf.effective_start_date and paf.effective_end_date 
   and :p_effective_date between ppf.effective_start_date and ppf.effective_end_date
 &cp_where_clause
order by hhle.name, ppf.per_information6
Parameter Name SQL text Validation
Legal Employer
 
LOV Oracle
Period as at
 
Date
DebugFlag
 
Business Group Id
 
Number