Employee – Assignment Details

Employee and assignment details can be fetched using the following query :

SELECT
papf.attribute1 “euhreka_emp_number”,
papf.first_name “First Name”,
papf.last_name “last Name”,
PAPF.DATE_OF_BIRTH “dob”,
–papf.nationality “nationality”,
DECODE(PAPF.SEX,’F’,’Female’,’M’,’Male’) “gender”,
PAPF.START_DATE “Hire Date”,
HR_PERSON_TYPE_USAGE_INFO.GET_USER_PERSON_TYPE(sysdate,PAPF.PERSON_ID) “Person Type”,
PAPF.EMAIL_ADDRESS “email”,
(select MEANING from HR_LOOKUPS where LOOKUP_CODE =PAPF.MARITAL_STATUS and LOOKUP_TYPE =’MAR_STATUS’) “marital_status”,
–paaf.effective_start_date “Assignment Start Date”,
HOU.name “Organization”,
HOU.ATTRIBUTE1 “COmpany BSV”,
hou.attribute1 “euHReka Org id”,
pj.name “Job”,
pg.name “Grade”,
ppf.payroll_name “Payroll”,
HL.DESCRIPTION “Location”,
–decode(HR_PERSON_TYPE_USAGE_INFO.GET_USER_PERSON_TYPE(sysdate,PAPF.PERSON_ID), ‘Ex-Employee’,select actual_termination_date from per_periods_of_service where person_id = papf.person_id) “resign date”,
XXHR_GET_TERMINATION_DATE(papf.person_id)”Termination Date”,
(select SEGMENT1 from PAY_PEOPLE_GROUPS where PEOPLE_GROUP_ID = PAAF.PEOPLE_GROUP_ID)”Contract Type”,
(select segment2 from pay_people_groups where people_group_id = paaf.people_group_id)”Home not Home”,
(select MEANING from HR_LOOKUPS where LOOKUP_CODE =PAAF.EMPLOYMENT_CATEGORY and LOOKUP_TYPE =’EMP_CAT’) “Emp Assignment Category”,
(select meaning from hr_lookups where lookup_code =paaf.employment_category and lookup_type =’CWK_ASG_CATEGORY’) “CWK Assignment Category”,
PAST.USER_STATUS “Assignment Status” ,
(select FULL_NAME from PER_ALL_PEOPLE_F where PERSON_ID = PAAF.SUPERVISOR_ID and sysdate between PAPF.EFFECTIVE_START_DATE and PAPF.EFFECTIVE_END_DATE) “Supervisor”,
(select attribute1 from per_all_people_F where person_id = paaf.supervisor_id and sysdate between papf.effective_start_date and papf.effective_end_date) “Supervisor euhReka Id”,
sob.name “Ledger”,
PAaf.VENDOR_ID ,
pov.vendor_name ,
PAaf.VENDOR_SITE_ID ,
povs.vendor_site_code ,
PAaf.PO_HEADER_ID ,
poh.segment1 po_number ,
paaf.po_line_id ,
pol.line_num po_line_number ,
paaf.normal_hours “Normal Hours”,
hr_general.decode_lookup(‘FREQUENCY’, paaf.frequency) frequency_meaning ,
gcc.segment1 “Company”,
gcc.segment2 “Account”,
gcc.segment3 “Project”,
gcc.segment4 “InterCompnay”,
gcc.segment5 “Future”
from
per_people_f papf,
PER_ASSIGNMENTS_F PAAF,
–per_person_types ppt,
–per_person_type_usages pptu
hr_all_organization_units hou,
per_jobs pj,
per_grades pg,
pay_all_payrolls_f ppf,
hr_locations hl,
per_assignment_status_types_v past,
gl_sets_of_books sob,
po_vendors pov ,
PO_VENDOR_SITES_ALL POVS ,
po_headers_all poh ,
PO_LINES_ALL POL,
GL_CODE_COMBINATIONS GCC
–XXHR_EUREH_GEOS_ACTION XA,
–XXHR_EUREH_GEOS_PERSONAL xp
where 1=1
and papf.person_id = paaf.person_id
and sysdate between PAPF.EFFECTIVE_START_DATE and PAPF.EFFECTIVE_END_DATE
and paaf.object_version_number = (select max(object_version_number)from per_all_assignments_f where person_id = papf.person_id)
–and XXHR_GET_TERMINATION_DATE(papf.person_id) between paaf.effective_start_date and paaf.effective_end_date
–and PPT.PERSON_TYPE_ID = PAPF.PERSON_TYPE_ID
and hou.organization_id(+) = paaf.organization_id
AND PAAF.JOB_ID = PJ.JOB_ID (+)
–and papf.attribute1 = ‘91409201’
and pg.grade_id(+) = paaf.grade_id
and ppf.payroll_id(+) = paaf.payroll_id
and PAAF.LOCATION_ID = HL.LOCATION_ID(+)
and paaf.assignment_status_type_id = past.assignment_status_type_id(+)
and paaf.set_of_books_id = sob.set_of_books_id (+)
and paaf.vendor_id = pov.vendor_id (+)
and paaf.vendor_site_id = povs.vendor_site_id (+)
and paaf.po_header_id = poh.po_header_id (+)
AND PAAF.PO_LINE_ID = POL.PO_LINE_ID (+)
and GCC.CODE_COMBINATION_ID(+) = PAAF.DEFAULT_CODE_COMB_ID

Employee- Assignment query

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s