unique constraint violated in stored procedure oracle

I have below stored procedure:

create or replace PROCEDURE CALCULATE_RECOVERY_HISTORY(p_month IN VARCHAR2) AS 
l_id NUMBER; 
BEGIN
  ADD_LOG_INFO('CALCULATE_RECOVERY_HISTORY', 'Procedure Started');
  
  l_id := SQ_AP_RECOVERY_HISTORY.NEXTVAL;

    INSERT INTO t_ap_recovery_history (ID, RECOVERY_TARGET_MONTH, TARGET_INSTANCE, RECOVERY_PROGRESS, RECOVERY_TARGET, FAILED_TO_RECOVERY, FOCUS_AREA, IDENTIFIER_CLASS, CREATED_ON) 
    SELECT  l_id,
            a_recovery_target_month, 
            a_target_instance, 
            COUNT(CASE WHEN A_IS_RECOVERED = 'Y' THEN 1 END), 
            COUNT(CASE WHEN A_IS_RECOVERED IN ('Y', 'N') THEN 1 END), 
            COUNT(CASE WHEN A_IS_RECOVERED = 'N' THEN 1 END),
            f.focus_area,
            r.identifier_class,
            SYSDATE
            from t_ap_recovery_target t, t_ap_recovery_focusarea f, range r
    where t.a_focus_area_id = f.id and t.a_range_id = r.id
    and t.a_recovery_target_month = p_month
    group by a_target_instance, a_recovery_target_month, f.focus_area, r.identifier_class;
    
      COMMIT;
END CALCULATE_RECOVERY_HISTORY;

When I run the procedure, I get the error

> ORA-00001: unique constraint violated.

I've also tried another way which is

SELECT  SQ_AP_RECOVERY_HISTORY.NEXTVAL, a_recovery_target_month ... 

But this also return another error which is

> Sequence number not allowed here

What should I change in the code to solve this constraint issue?

Below is the table definition for T_AP_RECOVERY_HISTORY

  CREATE TABLE "DIMSPST"."T_AP_RECOVERY_HISTORY" 
   (	"ID" NUMBER(38,0), 
	"RECOVERY_TARGET_MONTH" VARCHAR2(6 BYTE) DEFAULT TO_CHAR(SYSTIMESTAMP, 'YYYYMM'), 
	"TARGET_INSTANCE" VARCHAR2(20 BYTE), 
	"RECOVERY_PROGRESS" NUMBER(38,0), 
	"RECOVERY_TARGET" NUMBER(38,0), 
	"FAILED_TO_RECOVERY" NUMBER(38,0), 
	"FOCUS_AREA" VARCHAR2(20 BYTE), 
	"IDENTIFIER_CLASS" VARCHAR2(42 BYTE), 
	"CREATED_ON" TIMESTAMP (6), 
	 PRIMARY KEY ("ID")

Perform the aggregation in a sub-query and then apply the sequence value in an outer-query:

CREATE PROCEDURE CALCULATE_RECOVERY_HISTORY(
  p_month IN VARCHAR2
)
AS 
BEGIN
  ADD_LOG_INFO('CALCULATE_RECOVERY_HISTORY', 'Procedure Started');

  INSERT INTO t_ap_recovery_history (
    ID,
    RECOVERY_TARGET_MONTH,
    TARGET_INSTANCE,
    RECOVERY_PROGRESS,
    RECOVERY_TARGET,
    FAILED_TO_RECOVERY,
    FOCUS_AREA,
    IDENTIFIER_CLASS,
    CREATED_ON
  )
  SELECT SQ_AP_RECOVERY_HISTORY.NEXTVAL,
         a_recovery_target_month, 
         a_target_instance, 
         RECOVERY_PROGRESS,
         RECOVERY_TARGET, 
         FAILED_TO_RECOVERY,
         focus_area,
         identifier_class,
         SYSDATE
  FROM   (
    SELECT a_recovery_target_month, 
           a_target_instance, 
           COUNT(CASE WHEN A_IS_RECOVERED = 'Y' THEN 1 END) AS RECOVERY_PROGRESS,
           COUNT(CASE WHEN A_IS_RECOVERED IN ('Y', 'N') THEN 1 END) AS RECOVERY_TARGET, 
           COUNT(CASE WHEN A_IS_RECOVERED = 'N' THEN 1 END) AS FAILED_TO_RECOVERY,
           f.focus_area,
           r.identifier_class
    FROM   t_ap_recovery_target t
           INNER JOIN t_ap_recovery_focusarea f
           ON (t.a_focus_area_id = f.id)
           INNER JOIN range r
           ON (t.a_range_id = r.id)
    WHERE  t.a_recovery_target_month = p_month
    GROUP BY
           a_target_instance, 
           a_recovery_target_month,
           f.focus_area,
           r.identifier_class
  );
END CALCULATE_RECOVERY_HISTORY;
/

Note: If you COMMIT in stored procedures then you cannot chain multiple procedures together and if one fails then ROLLBACK then all. Instead, you should COMMIT in the block that you call the procedures from.

fiddle

One option is to let Oracle create ID. You didn't specify database version you use, so trigger certainly is what would work:

create or replace trigger trg_bi_rec_hist
  before insert on t_ap_recovery_history
  for each row
begin
  :new.id := SQ_AP_RECOVERY_HISTORY.NEXTVAL;
end;
/

Procedure then wouldn't contain insert into the ID column, i.e.

INSERT INTO t_ap_recovery_history (RECOVERY_TARGET_MONTH, ...)
SELECT a_recovery_target_month, ...

Another option (if your database version supports it) is to create ID as identity column instead of a trigger, e.g.

SQL> create table test
  2    (id number generated always as identity);

Table created.

Or, if you would not create the trigger like in the previous answer, the procedure should look like below:

create or replace PROCEDURE CALCULATE_RECOVERY_HISTORY(p_month IN VARCHAR2) AS 
l_id NUMBER; 
BEGIN
  ADD_LOG_INFO('CALCULATE_RECOVERY_HISTORY', 'Procedure Started');
  

      INSERT INTO t_ap_recovery_history (ID, RECOVERY_TARGET_MONTH, 
        TARGET_INSTANCE, RECOVERY_PROGRESS, RECOVERY_TARGET,
        FAILED_TO_RECOVERY, FOCUS_AREA, IDENTIFIER_CLASS, CREATED_ON)
      with tb as ( 
      SELECT  a_recovery_target_month, 
           a_target_instance, 
           COUNT(CASE WHEN A_IS_RECOVERED = 'Y' THEN 1 END) c1, 
           COUNT(CASE WHEN A_IS_RECOVERED IN ('Y', 'N') THEN 1 END) c2, 
           COUNT(CASE WHEN A_IS_RECOVERED = 'N' THEN 1 END) c3,
           f.focus_area,
           r.identifier_class
      from t_ap_recovery_target t, t_ap_recovery_focusarea f, range r
      where t.a_focus_area_id = f.id and t.a_range_id = r.id
          and t.a_recovery_target_month = p_month
      group by a_target_instance, a_recovery_target_month, 
        f.focus_area, r.identifier_class
      )
      select SQ_AP_RECOVERY_HISTORY.NEXTVAL,
           a_recovery_target_month, 
           a_target_instance, 
           c1, 
           c2, 
           c3,
           focus_area,
           identifier_class,
           sysdate 
      from tb;
    
      COMMIT;
END CALCULATE_RECOVERY_HISTORY;

In general triggers are detrimental to performance in case you have insert select inserting large numbers of rows in one go, or massive updates or massive deletes or merge.
If you have only DML affecting a small number of rows, triggers may save complexity, although I'd rather do more in stored procedures and less in triggers.