PER Absences

Description
Categories: BI Publisher
Columns: Summ Absence Type, Absence Attendance Type Id, Summ Absence Days, Summ Absence Hours, Occurrences, Element Name, Input Value, Balance Type, Summ Person Id, C Running Total ...
Application: Human Resources
Source: Absences Report (XML)
Short Name: PERRPRAA_XML
DB package: PER_PERRPRAA_XMLP_PKG
SELECT aat.name summ_absence_type
,      aat.absence_attendance_type_id absence_attendance_type_id
,      sum(aa.absence_days) summ_absence_days
,      sum(aa.absence_hours) summ_absence_hours
,      count(aa.person_id) occurrences
,      element.element_name element_name
,      inpval.name input_value
,      lkup.meaning balance_type
,      aa.person_id summ_person_id,
	PER_PERRPRAA_XMLP_PKG.c_running_totalformula(aa.person_id, aat.absence_attendance_type_id) C_RUNNING_TOTAL
FROM   per_absence_attendances aa
,      per_abs_attendance_types_vl aat
,      pay_input_values_f inpval
,      pay_element_types_f element
,      hr_lookups lkup
WHERE  (aa.date_start between
        :P_DATE_FROM and nvl(:P_DATE_TO, to_date('31/12/4712','DD/MM/YYYY'))
        OR aa.date_end   between
        :P_DATE_FROM and nvl(:P_DATE_TO, to_date('31/12/4712','DD/MM/YYYY'))
        OR (aa.date_start < :P_DATE_FROM
        and aa.date_end > nvl(:P_DATE_TO, to_date('31/12/4712','DD/MM/YYYY'))))
AND    aa.absence_attendance_type_id = aat.absence_attendance_type_id
AND   (:C_ABTYPES_ENTERED = 'N'
    OR :C_ABTYPES_ENTERED = 'Y'  &P_ABSENCE_SQL)
AND    aat.increasing_or_decreasing_flag = lkup.lookup_code(+)
AND    lkup.lookup_type(+) = 'INCREASING_OR_DECREASING'
AND    aat.input_value_id = inpval.input_value_id(+)
AND    :P_SESSION_DATE between
       inpval.effective_start_date and inpval.effective_end_date
AND    inpval.element_type_id = element.element_type_id(+)
AND    :P_SESSION_DATE
       between element.effective_start_date and element.effective_end_date
 and aa.person_id=:person_id
GROUP BY aat.name,inpval.element_type_id
,      element.element_name
,      inpval.name
,      lkup.meaning
,aat.absence_attendance_type_id
,aa.person_id
Parameter Name SQL text Validation
PER_DATES_STANDARD
 
Date
Organization Unit
 
LOV Oracle
Employee
 
LOV Oracle
Date From
 
Date
Date To
 
Date
Absence Type 1
 
LOV Oracle
Absence Type 2
 
LOV Oracle
Absence Type 3
 
LOV Oracle
Absence Type 4
 
LOV Oracle
Absence Type 5
 
LOV Oracle
Absence Type 6
 
LOV Oracle
Absence Type 7
 
LOV Oracle
Absence Type 8
 
LOV Oracle
Absence Type 9
 
LOV Oracle
Absence Type 10
 
LOV Oracle
Business Group Id
 
Number
PER_CHAR