ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN C6E085D4718451A9E05362FB09050512 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 Y PER PERNOCLWR US PER Norwegian Child/Child Minder Sickness Leave Warnings 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: BI Publisher :p_business_group_id :p_from_date :p_legal_employer_id :p_local_unit_id :p_to_date 1 -10 :p_business_group_id Number select fnd_global.PER_BUSINESS_GROUP_ID from dual Y US Business Group Business Group 2 10 :p_legal_employer_id LOV Oracle PY_NO_LEGAL_EMPLOYER Y select o.organization_id id, o.name value, null description from hr_all_organization_units o,fnd_sessions s,hr_organization_information hoi where o.business_group_id=fnd_global.per_business_group_id and o.organization_id=hoi.organization_id and hoi.org_information_context='CLASS' and hoi.org_information1='HR_LEGAL_EMPLOYER' and s.session_id=userenv('sessionid') and s.effective_date between o.date_from and nvl(o.date_to,to_date('31/12/4712','DD/MM/YYYY')) order by o.name Y US Legal Employer Legal Employer 3 20 :p_local_unit_id LOV Oracle PY_NO_LE_LOCAL_UNIT Y select o1.organization_id id, o1.name value, null description from hr_organization_units o1,hr_organization_information hoi1,hr_organization_information hoi2,hr_organization_information hoi3,hr_organization_information hoi4,fnd_sessions s where o1.business_group_id=fnd_global.per_business_group_id and s.session_id=userenv('SESSIONID') and s.effective_date between o1.date_from and nvl(o1.date_to,s.effective_date) and hoi1.organization_id=o1.organization_id and hoi1.org_information1='NO_LOCAL_UNIT' and hoi1.org_information_context='CLASS' and nvl(hoi1.org_information2,'N')='Y' 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=:$flex$.py_no_legal_employer and nvl(hoi3.org_information2,'N')='Y' and hoi1.organization_id=hoi4.organization_id and hoi4.org_information_context='NO_LOCAL_UNIT_DETAILS' and hoi4.org_information5='Y' order by name US Local Unit Local Unit 4 30 :p_from_date Date Y US From Date From Date 5 40 :p_to_date Date Y US To Date To Date :$flex$.py_no_legal_employer Legal Employer Local Unit