Hi All,
I’m new to writing background jobs so I’m hoping that I’ve just messed something up, and there’s a relatively straightforward solution to my problem. For some background, we’ve found that when attaching a document to project connected PO, that the document is only connected to the PO line:
We would like it to also be connected to the corresponding Project and Activity.
I initially created a custom event/action to do this:
But this gave me the mutating table error.
When looking for a way to fix this, I saw several other posts that suggested creating a background job (which I haven’t done before). I tried to piece a background job together by looking at the posts I saw but I’m still getting a mutating table error, so I feel like I’m missing something.
Here’s the code that I’m trying to use:
DECLARE
--Custom Variables
var_key_ref varchar2(2000);
var_project_id varchar2(2000) :=purchase_order_line_part_api.get_project_id(regexp_substr(var_key_ref,'(=)([^=\^]+)(\^)',1, 2, 'i', 2), regexp_substr(var_key_ref,'(=)([^=\^]*)(\^)',1, 1, 'i', 2), regexp_substr(var_key_ref,'(=)([^=\^]+)(\^)',1, 3, 'i', 2));
var_activity_seq varchar2(2000) :=purchase_order_line_part_api.get_ACTIVITY_SEQ(regexp_substr(var_key_ref,'(=)([^=\^]+)(\^)',1, 2, 'i', 2), regexp_substr(var_key_ref,'(=)([^=\^]*)(\^)',1, 1, 'i', 2), regexp_substr(var_key_ref,'(=)([^=\^]+)(\^)',1, 3, 'i', 2));
var_key_ref_act varchar2(2000);
var_key_ref_proj VARCHAR2(2000);
--Deferred Call Variables
attr_ VARCHAR2(32000);
sql_msg_ VARCHAR2(32000);
stmt_ VARCHAR2(32000);
job_id_ NUMBER;
error_text_ VARCHAR2(2000);
BEGIN
BEGIN
select key_ref into var_key_ref from doc_reference_object where doc_class = '&NEW:DOC_CLASS' and doc_no = '&NEW:DOC_NO' and doc_sheet = '&NEW:DOC_SHEET' and doc_rev = '&NEW:DOC_REV' and lu_name like 'PurchaseOrderLine%';
select purchase_order_line_part_api.get_project_id(regexp_substr(var_key_ref,'(=)([^=\^]+)(\^)',1, 2, 'i', 2), regexp_substr(var_key_ref,'(=)([^=\^]*)(\^)',1, 1, 'i', 2), regexp_substr(var_key_ref,'(=)([^=\^]+)(\^)',1, 3, 'i', 2)) into var_project_id from dual;
select purchase_order_line_part_api.get_ACTIVITY_SEQ(regexp_substr(var_key_ref,'(=)([^=\^]+)(\^)',1, 2, 'i', 2), regexp_substr(var_key_ref,'(=)([^=\^]*)(\^)',1, 1, 'i', 2), regexp_substr(var_key_ref,'(=)([^=\^]+)(\^)',1, 3, 'i', 2)) into var_activity_seq from dual;
select CONCAT(CONCAT('ACTIVITY_SEQ=','&VAR_ACTIVITY_SEQ'),'^') into var_key_ref_act from dual;
select CONCAT(CONCAT('PROJECT_ID=','&VAR_PROJECT_ID'),'^') into var_key_ref_proj from dual;
--PROJECT
Doc_Reference_Object_API.Create_New_Reference('&NEW:DOC_CLASS', '&NEW:DOC_NO', '&NEW:DOC_SHEET', '&NEW:DOC_REV', 'Activity' , CONCAT(CONCAT('ACTIVITY_SEQ=','&VAR_ACTIVITY_SEQ'),'^') , null , '001');
--ACTIVITY
Doc_Reference_Object_API.Create_New_Reference('&NEW:DOC_CLASS', '&NEW:DOC_NO', '&NEW:DOC_SHEET', '&NEW:DOC_REV', 'Project' , CONCAT(CONCAT('PROJECT_ID=','&VAR_PROJECT_ID'),'^') , null , '001');
END;
client_SYS.Clear_Attr(attr_);
Client_SYS.Add_To_Attr('DOC_CLASS','&NEW:DOC_CLASS', attr_);
Client_SYS.Add_To_Attr('DOC_NO','&NEW:DOC_NO', attr_);
Client_SYS.Add_To_Attr('DOC_SHEET','&NEW:DOC_SHEET', attr_);
Client_SYS.Add_To_Attr('DOC_REV','&NEW:DOC_REV', attr_);
Client_SYS.Add_To_Attr('LU_NAME','Activity', attr_);
Client_SYS.Add_To_Attr('KEY_REF','&var_key_ref_act', attr_);
Transaction_SYS.Deferred_Call(job_id_,'Fnd_Event_Action_API.Action_Executeonlinesql','PARAMETER',attr_,'Add object connections for Project and Activity to PURCHASE_ORD documents.');
END;
And I’m just looking for some pointers to get this cleaned up and working. It’d also be helpful if I knew what I was doing wrong so I can avoid making that mistake again in the future.
