Hi guys,
I have created a custom api which calculates and updates some planning data for inventory parts.
How can I show the parts and error message in the background job Text area (DEFERRED_JOB_STATUS) ?
What is the EXCEPTION handling that I need to use?
Thank you for your feedback!
Regards Martina
Hi Alhagmo,
You can try Transaction_Sys.Log_Error__ method. If you know which exception it would throw you can catch it, else just try to wrap this with a normal Exception block. Product-related code doesn’t throw a certain exception type it's usually throwing an exception by Error_Sys.Record_General.
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 Martina,
My pleasure, I hope you want to stop and not commit any inventory part planning data if an error occurred, else you should try something like an Oracle Savepoint and commit whatever went ok.
Hi
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 Dhananjaya,
thank you very much for your proposals!
It works great! 
Hi Dhananjaya,
thank you very much for your proposals!
It works great!
Hi
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 a longer status text in multiple pieces, like for a really big attr_ string.
You might not also know whether your code is being run as a background job, interactively in SQL*Plus or PL/SQL Developer for debugging, or inside of the IFS Enterprise Explorer application.
I wrote my own general-purpose trace function to handle all these use cases. When it breaks apart a long status, it attempts to wrap by word.
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__;
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__;
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.