ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 82288223F36D3869E053B46B63588994Y SELECT ppf.full_name ,ppf.employee_number ,paf.assignment_id ,pay_no_abs_validation.get_cms_leave_taken(ppf.person_id,:p_report_date) cms_leave_taken ,pay_no_abs_validation.get_cms_entitlement(ppf.person_id,:p_report_date) cms_entitlement FROM per_all_people_f ppf ,per_all_assignments_f paf ,hr_soft_coding_keyflex hsck WHERE ppf.person_id = paf.person_id AND paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id AND :p_report_date between ppf.effective_start_date and ppf.effective_end_date AND :p_report_date between paf.effective_start_date and paf.effective_end_date AND ppf.person_id = NVL(:p_person_id,ppf.person_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 to_char(hoi1.organization_id) = hsck.segment2) ORDER BY ppf.employee_number PER PERNOCLBR US PER Norwegian Child/Child Minder Sickness Leave Balance Application: Human Resources Source: Norwegian Child/Child Minder Sickness Leave Balance Short Name: PERNOCLBR DB package: BI Publisher :p_legal_employer_id :p_person_id :p_report_date 1 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 2 20 :p_report_date Date SELECT TRUNC(effective_date) FROM FND_SESSIONS WHERE SESSION_ID = USERENV('SESSIONID') Y US Effective Date 3 30 :p_person_id LOV Oracle PY_NO_LE_EMPLOYEES Y select ppf.person_id id, ppf.full_name value, null description from per_all_people_f ppf,per_all_assignments_f paf,hr_soft_coding_keyflex hsc,fnd_sessions fnd where ppf.person_id=paf.person_id and paf.soft_coding_keyflex_id=hsc.soft_coding_keyflex_id and fnd.session_id=userenv('SESSIONID') and fnd.effective_date between ppf.effective_start_date and ppf.effective_end_date and fnd.effective_date between paf.effective_start_date and paf.effective_end_date 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=:$flex$.py_no_legal_employer and hoi1.organization_id=hoi4.organization_id and hoi4.org_information_context='NO_LOCAL_UNIT_DETAILS' and to_char(hoi1.organization_id)=hsc.segment2) order by value,description US Employee Name