CAR collection create
Thu Nov 17 2022 04:03:37 GMT+0000 (Coordinated Universal Time)
Saved by @ahmed.toson
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;
Comments