CREATE OR REPLACE TRIGGER trg_trt
  AFTER INSERT
  ON tbl_treatment
  FOR EACH ROW
DECLARE
 i integer;
BEGIN
  i := PKG_TRG_PRC.i_reorder_trt_no(:NEW.pt_id);
  dbms_output.put_line('i_err = ' || i);
END trg_trt;

  FUNCTION i_reorder_trt_no(in_pt_id IN INTEGER)
  RETURN INTEGER AS
    i_row_no                INTEGER;
    i_position              INTEGER;
    
    CURSOR crs_trt IS SELECT treatment_number,                                                     DT_CRT_DATE_FROMPARTS(year,mon,day) AS dt_trt
                       FROM tbl_Treatment
                       WHERE pt_id = in_pt_id
                       ORDER BY dt_trt
                       FOR UPDATE OF treatment_number;
                       
    row_crs_trt crs_trt%ROWTYPE;
    
  BEGIN
  
    i_position := 1;

    --first step - assign large numbers beyond possible limit
    --to avoid PK violation
    OPEN crs_trt;
    
      LOOP
        FETCH crs_trt INTO row_crs_trt; -- retrieve a row from table
        EXIT WHEN crs_trt%NOTFOUND;
        
        UPDATE tbl_treatment
        SET treatment_number = treatment_number + 50
        WHERE CURRENT of crs_trt;
      END LOOP;
      
    CLOSE crs_trt;


    i_position := 2;
    --Re-open cursor - second step: As records in cursor set are ordered by
    --dt_trt - fetch trt records one by one and apply 1,2,3.. sequence
    
    OPEN crs_trt;
    
      i_row_no := 1;
      LOOP
        FETCH crs_trt INTO row_crs_trt; -- retrieve a row from table
        EXIT WHEN crs_trt%NOTFOUND;
        
        UPDATE tbl_treatment
        SET treatment_number = i_row_no
        WHERE CURRENT of crs_trt;
        
        i_row_no := i_row_no + 1;
      END LOOP;
      
    CLOSE crs_trt;

    COMMIT;
    
    RETURN 0;
    
  EXCEPTION
    WHEN others THEN
      CASE i_position
        WHEN 1 THEN
          RETURN 1;
        WHEN 2 THEN
          RETURN 2;
        ELSE
          RETURN 10;
      END CASE;

      ROLLBACK;

  END i_reorder_trt_no;

EXCEPTION
    WHEN others THEN
      CASE i_position
        WHEN 1 THEN
          RETURN 1;
        WHEN 2 THEN
          RETURN 2;
        ELSE
          RETURN 10;
      END CASE;