CAR collection create

begin

     if NOT APEX_COLLECTION.COLLECTION_EXISTS(p_collection_name=>'CORRECTIVE_ACTIONS') then
            APEX_COLLECTION.CREATE_OR_TRUNCATE_COLLECTION(
                p_collection_name =>'CORRECTIVE_ACTIONS'
            );
    end if;
IF :P41_REPORT_ID IS NULL THEN

        for i in 
                (
                    SELECT C.ID,
                    C.ACTION, 
                    C.ASSIGNED_TO,
                    C.COMPLETION_DATE,
                    C.STATUS,
                    C.REPORT_ID,
                    C.ROOT_CAUSE_ANALYSIS,
                    A.QUESTION_ID, A.SCORE, A.MAX_SCORE , (A.SCORE * 100 / A.MAX_SCORE) AS FINAL_SCORE, A.COMMENTS 
                    FROM ACTIONS C
                    JOIN ASSESSMENT_RESULTS A ON A.REPORT_ID = C.REPORT_ID
                     WHERE A.SCORE <= A.MIN_SCORE
                ) LOOP

                 APEX_COLLECTION.ADD_MEMBER(
                        p_collection_name=>'CORRECTIVE_ACTIONS',
                        p_c001 => i.ACTION,
                        p_c002 => i.ASSIGNED_TO,
                        p_c003 => i.ROOT_CAUSE_ANALYSIS,
                        p_c004 => i.STATUS,
                        p_c005 => i.COMMENTS,
                        p_c006 => i.QUESTION_ID,
                        p_n001 => i.REPORT_ID,
                        p_n002 => i.ID,
                        p_n003 => i.SCORE,
                        p_n004 => i.MAX_SCORE,
                        p_n005 => i.FINAL_SCORE,
                        P_d001 => to_date(i.COMPLETION_DATE)
                       
                    );

             end loop;
 ELSE
    

            for i in 
                    (
                        SELECT C.ID,
                        C.ACTION, 
                        C.ASSIGNED_TO,
                        C.COMPLETION_DATE,
                        C.STATUS,
                        C.REPORT_ID,
                        C.ROOT_CAUSE_ANALYSIS,
                        A.QUESTION_ID, A.SCORE, A.MAX_SCORE , (A.SCORE * 100 / A.MAX_SCORE) AS FINAL_SCORE, A.COMMENTS 
                        FROM ACTIONS C
                        RIGHT JOIN ASSESSMENT_RESULTS A ON A.REPORT_ID = C.REPORT_ID
                        WHERE A.REPORT_ID = :P41_REPORT_ID AND A.SCORE <= A.MIN_SCORE

                    ) LOOP

                     APEX_COLLECTION.ADD_MEMBER(
                            p_collection_name=>'CORRECTIVE_ACTIONS',
                            p_c001 => i.ACTION,
                            p_c002 => i.ASSIGNED_TO,
                            p_c003 => i.ROOT_CAUSE_ANALYSIS,
                            p_c004 => i.STATUS,
                            p_c005 => i.COMMENTS,
                            p_c006 => i.QUESTION_ID,
                            p_n001 => i.REPORT_ID,
                            p_n002 => i.ID,
                            p_n003 => i.SCORE,
                            p_n004 => i.MAX_SCORE,
                            p_n005 => i.FINAL_SCORE,
                            p_d001 => to_date(i.COMPLETION_DATE)
                        );
                end loop;
 end if;
end;