PER Norwegian Child/Child Minder Sickness Leave Warnings

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