how to fill errors in background job Text per item in a Custom API as scheduled database task

Hi infaz,

thank you for your hint. I have used 2 functions from this Package:

                transaction_sys.Log_Error__(id_,info_);
                Transaction_Sys.Log_Status_Info(info_);

Only the second one filled information into text rows of background job.

Thank you very much for your help.

This is my code:

BEGIN
info_ := '';
FOR rec_ in get_part_no LOOP
      DECLARE
      BEGIN
        id_ := Transaction_Sys.Get_Current_Job_Id();
              
           Client_SYS.Clear_Attr(attr_); 
           Client_SYS.Add_To_Attr('STD_ORDER_SIZE', rec_.NEW_LOTSIZE , attr_);

           IFSAPP.INVENTORY_PART_PLANNING_API.Modify__(info_, rec_.objid, rec_.objversion, attr_, 'DO');
              info_ := 'PART_NO ' || rec_.Part_no || ' old STD_ORDER_SIZE: ' || rec_.STD_ORDER_SIZE_OLD || ' new STD_ORDER_SIZE: ' || rec_.NEW_LOTSIZE || ' ' || SQLERRM;
              Transaction_Sys.Log_Status_Info(info_);
            EXCEPTION
              WHEN OTHERS THEN
              info_ := 'PART_NO ' || rec_.Part_no || ' old STD_ORDER_SIZE: ' || rec_.STD_ORDER_SIZE_OLD || ' new STD_ORDER_SIZE: ' || rec_.NEW_LOTSIZE || ' ' || SQLERRM;
                transaction_sys.Log_Error__(id_,info_);
                Transaction_Sys.Log_Status_Info(info_);
      END;

END LOOP;

Best regards 

Martina

Hi @ALHAGMO,

I have done a few changes to your code.
Could you try the below?

BEGIN
   info_ := '';

   FOR rec_ IN get_part_no LOOP
      DECLARE
      BEGIN
         --(+) START
         SAVEPOINT before_change_;
         --(+) FINISH

         id_ := Transaction_SYS.Get_Current_Job_Id();

         Client_SYS.Clear_Attr(attr_);
         Client_SYS.Add_To_Attr('STD_ORDER_SIZE', rec_.new_lotsize, attr_);

         IFSAPP.INVENTORY_PART_PLANNING_API.Modify__(info_, rec_.objid, rec_.objversion, attr_, 'DO');

         --(-/+) START
         -- info_ := 'PART_NO ' || rec_.part_no || ' old STD_ORDER_SIZE: ' || rec_.std_order_size_old || ' new STD_ORDER_SIZE: ' || rec_.new_lotsize || ' ' || SQLERRM;
         -- Transaction_SYS.Log_Status_Info(info_);
         info_ := 'PART_NO ' || rec_.part_no || ' old STD_ORDER_SIZE: ' || rec_.std_order_size_old || ' new STD_ORDER_SIZE: ' || rec_.new_lotsize;

         Fnd_Context_SYS.Set_Value('ERROR_FORMATTED_KEY', SUBSTR(info_, 1, 2000));
         Transaction_SYS.Log_Status_Info(SUBSTR('Successful', 1, 2000), 'INFO');
         --(-/+) FINISH
      EXCEPTION
         WHEN OTHERS THEN
            --(-/+) START
            -- info_ := 'PART_NO ' || rec_.part_no || ' old STD_ORDER_SIZE: ' || rec_.std_order_size_old || ' new STD_ORDER_SIZE: ' || rec_.new_lotsize || ' ' || SQLERRM;
            -- transaction_SYS.Log_Error__(id_, info_);
            -- Transaction_SYS.Log_Status_Info(info_);
            ROLLBACK TO before_change_;

            info_ := 'PART_NO ' || rec_.part_no || ' old STD_ORDER_SIZE: ' || rec_.std_order_size_old || ' new STD_ORDER_SIZE: ' || rec_.new_lotsize;

            Fnd_Context_SYS.Set_Value('ERROR_FORMATTED_KEY', SUBSTR(info_, 1, 2000));
            Transaction_SYS.Log_Status_Info(SUBSTR(SQLERRM, 1, 2000), 'WARNING');
            --(-/+) FINISH
      END;

      --(+) START
      COMMIT;
      --(+) FINISH
   END LOOP;
END;

Cheers !
Dhananjaya.

Hi @ALHAGMO,

You are welcome and I'm happy to help 😊

Cheers !
Dhananjaya.

The column TRANSACTION_SYS_STATUS_TAB.TEXT is limited to 2000 characters, but sometimes you might want to write longer status texts, like for a really big attr_ string.

You might want to output trace information in your code without knowing in advance whether you're executing the code inside SQL*Plus or PL/SQL Developer for debugging, inside IFS Enterprise Explorer, or inside a background job.

I wrote a general-purpose trace function to accomplish these tasks. It attempts to word-wrap a long status line.

PROCEDURE trace__(
   text_        IN CLOB,
   status_type_ IN VARCHAR2 DEFAULT 'INFO')
IS
   max_length_                PLS_INTEGER;
   piece_length_              PLS_INTEGER;
   remaining_length_          PLS_INTEGER;
   position_of_next_space_    PLS_INTEGER;
   position_of_next_lf_       PLS_INTEGER;
   position_of_next_start_    PLS_INTEGER;
   sid_                       NUMBER;
   prefix_                    VARCHAR2(32767);
   program_                   VARCHAR2(32767);
   text_buffer_               CLOB;
   reversed_buffer_           VARCHAR2(32767);
   first_space_in_rev_buffer_ PLS_INTEGER;
   next_text_                 CLOB;
   iteration_count_           PLS_INTEGER := 0;
   timestamp_                 TIMESTAMP(9) := SYSTIMESTAMP;
BEGIN
   timestamp_ := SYSTIMESTAMP;
   prefix_ := TO_CHAR(timestamp_, 'YYYY-MM-DD HH24:MI:SS.FF') || ':';
   max_length_ := 200;

   text_buffer_ := prefix_ || text_;
   text_buffer_ := REPLACE(text_buffer_, CHR(13), CHR(10));
   text_buffer_ := REPLACE(text_buffer_, CHR(10) || CHR(10), CHR(10));
   text_buffer_ := REPLACE(text_buffer_, CHR(10) || CHR(10), CHR(10));
   text_buffer_ := RTRIM(text_buffer_, CHR(10));
   text_buffer_ := LTRIM(text_buffer_, CHR(10));
   text_buffer_ := TRIM(text_buffer_);
   LOOP
      iteration_count_ := iteration_count_ + 1;
      EXIT WHEN iteration_count_ > max_iteration_count_;

      remaining_length_ := DBMS_LOB.GETLENGTH(text_buffer_);

      position_of_next_lf_ := DBMS_LOB.INSTR(text_buffer_, CHR(10), 1);
      IF remaining_length_ < max_length_ AND position_of_next_lf_ = 0 THEN
         next_text_ := NULL;
      ELSE
         IF position_of_next_lf_ > 0 AND position_of_next_lf_ < max_length_ THEN
            piece_length_ := position_of_next_lf_ - 1;
            position_of_next_start_ := position_of_next_lf_ + 1;
            next_text_ := SUBSTR(text_buffer_, position_of_next_start_);
            text_buffer_ := SUBSTR(text_buffer_, 1, piece_length_);
         ELSE
            piece_length_ := max_length_;
            position_of_next_start_ := max_length_ + 1;
            next_text_ := SUBSTR(text_buffer_, position_of_next_start_);
            text_buffer_ := SUBSTR(text_buffer_, 1, piece_length_);
            -- attempt to word wrap by searching for a space from the end
            SELECT REVERSE(DBMS_LOB.SUBSTR(text_buffer_, max_length_, 1))
              INTO reversed_buffer_
              FROM DUAL;
            first_space_in_rev_buffer_ := INSTR(reversed_buffer_, ' ');
            IF first_space_in_rev_buffer_ BETWEEN 2 AND 20 THEN
               text_buffer_ := text_buffer_ || next_text_;
               piece_length_ := max_length_ - first_space_in_rev_buffer_;
               position_of_next_start_ := piece_length_ + 2;
               next_text_ := SUBSTR(text_buffer_, position_of_next_start_);
               text_buffer_ := SUBSTR(text_buffer_, 1, piece_length_);
            END IF;
         END IF;
      END IF;

      IF transaction_sys.is_session_deferred THEN
         IF status_type_ IN ('INFO', 'WARNING') THEN
            transaction_sys.log_status_info(
               info_ => text_buffer_,
               status_type_ => status_type_);
         ELSIF status_type_ = 'PROGRESS' THEN
            transaction_sys.set_progress_info(text_buffer_);
         ELSE
            transaction_sys.log_status_info(
               info_ => text_buffer_,
               status_type_ => 'INFO');
         END IF;
      ELSIF USER = 'IFSSYS' THEN
         trace_sys.put_line(text_ => text_buffer_);
      ELSE
         sid_ := SYS_CONTEXT('USERENV', 'SID');
         SELECT program
           INTO program_
           FROM v$session
          WHERE sid = sid_;
         IF    UPPER(program_) LIKE 'SQLPLUS%'
            OR UPPER(program_) LIKE 'PLSQLDEV%'
         THEN
            dbms_output.put_line(text_buffer_);
         END IF;
      END IF;

      EXIT WHEN next_text_ IS NULL;
      timestamp_ := timestamp_ + INTERVAL '0.000000001' SECOND;
      prefix_ := TO_CHAR(timestamp_, 'YYYY-MM-DD HH24:MI:SS.FF') || ':';
      text_buffer_ := prefix_ || next_text_;
   END LOOP;
END trace__;