PER Norwegian Child/Child Minder Sickness Leave Balance

   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