PER Norwegian Child/Child Minder Sickness Leave Warnings
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Report to generate the Warnings for Child Minder Sickness Leave
Application: Human Resources
Source: Norwegian Child/Child Minder Sickness Leave Warnings
Short Name: PERNOCLWR
DB package:
Description: Report to generate the Warnings for Child Minder Sickness Leave
Application: Human Resources
Source: Norwegian Child/Child Minder Sickness Leave Warnings
Short Name: PERNOCLWR
DB package:
Run
PER Norwegian Child/Child Minder Sickness Leave Warnings and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT full_name ,employee_number ,assignment_id ,absence_attendance_id ,person_id ,date_start ,date_end ,warnings ,warningnumber ,decode(warningnumber,1,(FND_MESSAGE.GET_STRING('PAY', 'PAY_376934_NO_CHILD_REG')) ,2,(FND_MESSAGE.GET_STRING('PAY', 'PAY_376935_NO_CHILD_MIN_AGE')) ,3,(FND_MESSAGE.GET_STRING('PAY', 'PAY_376914_NO_CMS_CONT_CHECK')) ,4,(FND_MESSAGE.GET_STRING('PAY', 'PAY_376936_NO_LIMIT_EXCEEED')) ,5,(FND_MESSAGE.GET_STRING('PAY', 'PAY_376937_NO_NAV_REQ')) ,6,(FND_MESSAGE.GET_STRING('PAY', 'PAY_376907_NO_SC_NOT_ELIGIBLE')) ) warningmsg FROM( SELECT ppf.full_name ,ppf.employee_number ,paf.assignment_id ,paa.absence_attendance_id ,ppf.person_id ,paa.date_start ,paa.date_end ,pay_no_abs_validation.cms_validation (ppf.person_id ,paa.date_start ,paa.date_end ,paa.abs_information1 ,paa.abs_information9 ,paa.absence_days) warnings FROM per_all_people_f ppf ,per_all_assignments_f paf ,hr_soft_coding_keyflex hsck ,per_absence_attendances paa ,per_absence_attendance_types paat WHERE ppf.person_id = paf.person_id AND paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id AND :P_FROM_DATE BETWEEN ppf.effective_start_date AND ppf.effective_end_date AND :P_FROM_DATE BETWEEN paf.effective_start_date AND paf.effective_end_date AND paa.date_start >= :P_FROM_DATE AND paa.date_end <= :P_TO_DATE AND paa.date_start IS NOT NULL AND paa.person_id = ppf.person_id AND paa.absence_attendance_type_id = paat.absence_attendance_type_id AND paat.absence_category = 'CMS' AND hsck.segment2 = to_char(NVL(:P_LOCAL_UNIT_ID,hsck.segment2)) AND ppf.business_group_id = :P_BUSINESS_GROUP_ID AND paf.PRIMARY_FLAG='Y' AND paf.ASSIGNMENT_TYPE='E' AND EXISTS ( SELECT hoi1.organization_id FROM hr_organization_units o1 ,hr_organization_information hoi1 ,hr_organization_information hoi2 ,hr_organization_information hoi3 ,hr_organization_information hoi4 WHERE hoi1.organization_id = o1.organization_id AND hoi1.org_information1 = 'NO_LOCAL_UNIT' AND hoi1.org_information_context = 'CLASS' AND o1.organization_id = hoi2.org_information1 AND hoi2.org_information_context = 'NO_LOCAL_UNITS' AND hoi2.organization_id = hoi3.organization_id AND hoi3.org_information_context = 'CLASS' AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER' AND hoi3.organization_id = :P_LEGAL_EMPLOYER_ID AND hoi1.organization_id = hoi4.organization_id AND hoi4.org_information_context = 'NO_LOCAL_UNIT_DETAILS' AND o1.business_group_id = :P_BUSINESS_GROUP_ID AND to_char (hoi1.organization_id) = hsck.segment2 ) )a, (SELECT rownum warningnumber FROM dual CONNECT BY level <= 6 )b WHERE INSTR(a.warnings,TO_CHAR(b.warningnumber)) > 0 ORDER BY b.warningnumber,a.employee_number |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Legal Employer |
|
LOV Oracle | |
Local Unit |
|
LOV Oracle | |
From Date |
|
Date | |
To Date |
|
Date |