Question

SQL Event Action to BPA Workflow

  • 23 August 2023
  • 4 replies
  • 95 views
S
Rank
Badge

I am trying to redesign a SQL event action as a Workflow and I am having the following challenges.

  1. Is it possible to use methods like client_sys.add_to_attr, client_sys.add_info and transaction_sys.deferred_call in a workflow.
  2. Can we use such projection methods to add records and if so is there a way to handle the exceptions?
  3. In case if its possible to use such methods in workflows should we do COMMITS in case we execute this within a loop? If COMMITs are required, is there a workaround for it as COMMITs are not allowed within workflows?

 

Appreciate your guidance in this.

 

Thanks in advance.

4 replies

K
Rank
Userlevel 2
Badge +4

@Suaad Suhail could you specify what your current. SQL , if we see the code we can check how to convert it to a workflow? Also what is the trigger and goal of the event?

Lahirumala de Mel
Rank
Userlevel 3
Badge +6

There might not be one to one projection methods for every plsql method. Might have to consider the end goal of what you are trying to achieve. If commits are necessary within the flow BPA workflows may not be the correct solution. 

D
Rank
Badge

@Suaad Suhail could you specify what your current. SQL , if we see the code we can check how to convert it to a workflow? Also what is the trigger and goal of the event?

This is the code of the Event - IW_DFS_DOC_ON_CONNECT

 

DECLARE
act_key   VARCHAR2(256);
act_seq   NUMBER;

doc_class_ VARCHAR2(256) := '&NEW:DOC_CLASS';
doc_no_    VARCHAR2(256) := '&NEW:DOC_NO';
doc_sheet_ VARCHAR2(256) := '&NEW:DOC_SHEET';
doc_rev_   VARCHAR2(256) := '&NEW:DOC_REV';
format_    VARCHAR2(256);

lu_name    VARCHAR2(256) := '&NEW:LU_NAME';

info_       VARCHAR2(32000) := NULL;
objkey_     VARCHAR2(32000) := NULL;
objversion_ VARCHAR2(32000) := NULL;
attr_       VARCHAR2(32000) := NULL;

TYPE folder_array IS VARRAY(15) OF VARCHAR2(50); --Limited to 15 folder names with names < 50 chars
folder_names folder_array := folder_array();

pragma autonomous_transaction;
BEGIN

  /* Key Value comes in format xxxx^. Remove trailing ampersand (^) */
  /* TODO: This is not always true, some keys can come in other formats. 
      If this is to be fixed, client_sys.Get_Key_Reference_Value(key_ref, name) can be used. 
      As of 03.03.2020, I only use this on the logical units where it is necessary. 
      I would have to check the lu_name in order to know what the key name is.
  */
  SELECT substr('&NEW:KEY_VALUE', 0, LENGTH('&NEW:KEY_VALUE')-1) INTO act_key FROM dual;


  /* Fetch activity sequence based on act_key */
  BEGIN
    IF lu_name = 'WorkOrder' THEN 
      SELECT activity_seq INTO act_seq
      FROM separate_work_order
      WHERE wo_no = act_key;
    ELSIF lu_name = 'CustomerOrder' THEN 
      SELECT CF$_IW_ACT_SEQ INTO act_seq
      FROM customer_order_cfv
      WHERE order_no = act_key;
    ELSIF lu_name = 'BusinessOpportunity' THEN 
      SELECT c_activity_seq INTO act_seq
      FROM business_opportunity
      WHERE opportunity_no = act_key;
    ELSIF lu_name = 'OrderQuotation' THEN
      SELECT DISTINCT c_activity_seq INTO act_seq
      FROM order_quotation
      WHERE quotation_no = act_key;
    ELSIF lu_name = 'Activity' THEN
      act_seq := to_number(act_key);
    ELSIF lu_name = 'CWipHeader' THEN
      SELECT activity_seq INTO act_seq
      FROM c_wip_header_uiv
      WHERE wip_id = act_key;    
    elsif lu_name = 'CWipAppl' THEN
      act_key := client_sys.get_key_reference_value('&NEW:KEY_REF', 'WIP_ID');
      SELECT activity_seq INTO act_seq
      FROM c_wip_header_uiv
      WHERE wip_id = act_key;    
    ELSIF lu_name = 'WorkOrderRole' THEN
      act_key := client_sys.get_key_reference_value('&NEW:KEY_REF', 'WO_NO');

      SELECT activity_seq INTO act_seq
      FROM separate_work_order
      WHERE wo_no = act_key;    
    ELSIF lu_name = 'CRunRecWell' THEN
      act_key := client_sys.get_key_reference_value('&NEW:KEY_REF', 'RR_ID');

      SELECT activity_seq INTO act_seq 
      FROM c_run_rec_header_uiv
      WHERE rr_id = act_key;    
    ELSIF lu_name = 'SubProject' THEN
      act_key := client_sys.get_key_reference_value('&NEW:KEY_REF', 'SUB_PROJECT_ID');

      SELECT activity_seq INTO act_seq
      FROM ACTIVITY
      WHERE sub_project_id  = act_key
      AND activity_no = 'A00';

    ELSE 
        RETURN;
    END IF;
  EXCEPTION 
    WHEN no_data_found THEN
      client_sys.clear_attr(attr_);
      client_sys.add_to_attr('CF$_IW_ERROR_ID', 'Err 006', attr_);
      client_sys.add_to_attr('CF$_EVENT_ID', 'IW_DFS_DOC_ON_CONNECT', attr_);
      client_sys.add_to_attr('CF$_IW_TIMESTAMP', SYSDATE, attr_);
      client_sys.add_to_attr('CF$_IW_USER_ID', '#USER_ID#', attr_);
      client_sys.add_to_attr('CF$_IW_ERROR_MSG', 'Error in creating new document reference. NO_DATA_FOUND when looking for activity sequence. 

      Act Key:' ||act_key||', lu_name:'||lu_name||' Doc No:'||doc_no_, attr_);
      client_sys.add_to_attr('CF$_IW_PROCESS', 'act', attr_);
      iw_d_f_s_error_log_clp.new__ (info_, objkey_, objversion_, attr_, 'DO');
      COMMIT;

      client_sys.add_info('Err 006', ' Error in creating new document reference. NO_DATA_FOUND. Ref GLB100862. Act Key:' ||act_key||', lu_name:'||lu_name);
      RETURN;
    WHEN too_many_rows THEN
      client_sys.clear_attr(attr_);
      client_sys.add_to_attr('CF$_IW_ERROR_ID', 'Err 007', attr_);
      client_sys.add_to_attr('CF$_IW_EVENT_ID', 'IW_DFS_DOC_ON_CONNECT', attr_);
      client_sys.add_to_attr('CF$_IW_TIMESTAMP', SYSDATE, attr_);
      client_sys.add_to_attr('CF$_IW_USER_ID', '#USER_ID#', attr_);
      client_sys.add_to_attr('CF$_IW_ERROR_MSG', 'Error in creating new document reference. TOO_MANY_ROWS when looking for activity sequence. 

      Act Key:' ||act_key||', lu_name:'||lu_name||' Doc No:'||doc_no_, attr_);
      client_sys.add_to_attr('CF$_IW_PROCESS', 'act', attr_);

      iw_d_f_s_error_log_clp.new__ (info_, objkey_, objversion_, attr_, 'DO');
      client_sys.add_info('Err 007', ' Error in creating new document reference. TOO_MANY_ROWS. Ref GLB100862.Ref GLB100862. Act Key:' ||act_key||', lu_name:'||lu_name);
      COMMIT;
      RETURN;
  END;

  /* Add document info and activity sequence to Pending Document Connections, and defer call to execute event IW_DFS_CONN_PENDING_DOC */
  BEGIN
    client_sys.clear_attr(attr_);
    client_sys.add_to_attr('CF$_IW_PROCESS', 'act', attr_);
    client_sys.add_to_attr('CF$_IW_DOC_NO', doc_no_, attr_);
    client_sys.add_to_attr('CF$_IW_DOC_REV', doc_rev_, attr_);
    client_sys.add_to_attr('CF$_IW_DOC_SHEET', doc_sheet_, attr_);
    client_sys.add_to_attr('CF$_IW_DOC_CLASS', doc_class_, attr_);
    client_sys.add_to_attr('CF$_IW_KEY', act_seq, attr_);
    client_sys.add_to_attr('CF$_IW_ACTION', 'ATTACH', attr_);
    client_sys.add_to_attr('CF$_IW_TIMESTAMP', SYSDATE, attr_);
    client_sys.add_to_attr('CF$_IW_STATE', 'PENDING', attr_);

    /* Create new record in Pending Document Connections */
    iw_d_f_s_pending_docs_clp.new__ (info_, objkey_, objversion_, attr_, 'DO');

    client_sys.clear_attr(attr_);
    client_sys.add_to_attr('EVENT_DATA_', '', attr_);  
    client_sys.add_to_attr('EVENT_ID_', 'IW_DFS_CONN_PENDING_DOC', attr_);
    client_sys.add_to_attr('EVENT_LU_NAME_', 'IwDFSPendingDocs', attr_);

    /* Call event to connect pending document as a deferred call, this should complete in < 1 minute */
    transaction_sys.deferred_call('Event_Sys.Event_Execute', 'PARAMETER', attr_, 'DocFoldStruct. Deferred call to IW_DFS_CONN_PENDING_DOC, for Document No '||doc_no_||', with key '||act_seq);

    COMMIT;
    RETURN;
  END;
END;


This is the code of the Event - IW_DFS_DOC_ON_DETACH

 

DECLARE
act_key   VARCHAR2(256);
act_seq   VARCHAR2(256);

doc_class_ VARCHAR2(256) := '&OLD:DOC_CLASS';
doc_no_    VARCHAR2(256) := '&OLD:DOC_NO';
doc_sheet_ VARCHAR2(256) := '&OLD:DOC_SHEET';
doc_rev_   VARCHAR2(256) := '&OLD:DOC_REV';

lu_name    VARCHAR2(256) := '&OLD:LU_NAME';

info_       VARCHAR2(32000) := NULL;
objkey_     VARCHAR2(32000) := NULL;
objversion_ VARCHAR2(32000) := NULL;
attr_       VARCHAR2(32000) := NULL;
action_     VARCHAR2(32000) := 'DO';

PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

  /* Key Value comes in format xxxx^. Remove trailing ampersand (^) */
  /* TODO: This is not always true, some keys can come in other formats. 
      If this is to be fixed, client_sys.Get_Key_Reference_Value(key_ref, name) can be used. 
      As of 03.03.2020, I only use this on the logical units where it is necessary. 
      I would have to check the lu_name in order to know what the key name is.
  */
  SELECT substr('&OLD:KEY_VALUE', 0, LENGTH('&OLD:KEY_VALUE')-1) INTO act_key FROM dual;

  /* Fetch activity sequence based on act_key */
  BEGIN
    IF lu_name = 'WorkOrder' THEN 
      SELECT activity_seq INTO act_seq
      FROM active_separate
      WHERE wo_no = TO_NUMBER(act_key);
    ELSIF lu_name = 'CustomerOrder' THEN 
      SELECT CF$_IW_ACT_SEQ INTO act_seq
      FROM customer_order_cfv
      WHERE order_no = act_key;
    ELSIF lu_name = 'BusinessOpportunity' THEN 
      SELECT c_activity_seq INTO act_seq
      FROM business_opportunity
      WHERE opportunity_no = act_key;
    ELSIF lu_name = 'OrderQuotation' THEN
      SELECT DISTINCT c_activity_seq INTO act_seq
      FROM order_quotation
      WHERE quotation_no = TO_NUMBER(act_key);
    ELSIF lu_name = 'Activity' THEN
      act_seq := TO_NUMBER(act_key);
    ELSIF lu_name = 'CWipHeader' THEN
      SELECT activity_seq INTO act_seq
      FROM c_wip_header_uiv
      WHERE wip_id = TO_NUMBER(act_key);      
    elsif lu_name = 'CWipAppl' THEN
      act_key := client_sys.get_key_reference_value('&OLD:KEY_REF', 'WIP_ID');
      SELECT activity_seq INTO act_seq
      FROM c_wip_header_uiv
      WHERE wip_id = act_key;      
    ELSIF lu_name = 'WorkOrderRole' THEN
      act_key := client_sys.get_key_reference_value('&OLD:KEY_REF', 'WO_NO');

      SELECT activity_seq INTO act_seq
      FROM separate_work_order
      WHERE wo_no = to_number(act_key);       
    ELSIF lu_name = 'SubProject' THEN
      act_key := client_sys.get_key_reference_value('&OLD:KEY_REF', 'SUB_PROJECT_ID');

      SELECT activity_seq INTO act_seq
      FROM ACTIVITY
      WHERE sub_project_id  = act_key
      AND activity_no = 'A00';

    ELSIF lu_name = 'CRunRecWell' THEN
      act_key := client_sys.get_key_reference_value('&OLD:KEY_REF', 'RR_ID');

      SELECT activity_seq INTO act_seq 
      FROM c_run_rec_header_uiv
      WHERE rr_id = to_number(act_key);
    ELSE 
        RETURN;
    END IF;
  EXCEPTION 
    WHEN no_data_found THEN
      -- No record, handle this?
      RETURN;
    WHEN too_many_rows THEN
      client_sys.clear_attr(attr_);
      client_sys.add_to_attr('CF$_IW_ERROR_ID', 'Err 012', attr_);
      client_sys.add_to_attr('CF$_IW_EVENT_ID', 'IW_DFS_DOC_ON_DETACH', attr_);
      client_sys.add_to_attr('CF$_IW_TIMESTAMP', SYSDATE, attr_);
      client_sys.add_to_attr('CF$_IW_USER_ID', '#USER_ID#', attr_);
      client_sys.add_to_attr('CF$_IW_ERROR_MSG', 'Error in detaching reference in deferred sys call to IW_DFS_CONN_PENDING_DOC. TOO_MANY_ROWS for LU'|| lu_name||' Key:'||act_key||' Act seq:'||act_seq||'', attr_);
      client_sys.add_to_attr('CF$_IW_PROCESS', 'act', attr_);

      iw_d_f_s_error_log_clp.new__ (info_, objkey_, objversion_, attr_, 'DO');
      client_sys.add_info('Err 012', ' Error in detaching document reference. TOO_MANY_ROWS. Ref GLB100862.');
      COMMIT;
      RETURN;
    WHEN INVALID_NUMBER THEN
      client_sys.clear_attr(attr_);
      client_sys.add_to_attr('CF$_IW_ERROR_ID', 'Err 013', attr_);
      client_sys.add_to_attr('CF$_IW_EVENT_ID', 'IW_DFS_DOC_ON_DETACH', attr_);
      client_sys.add_to_attr('CF$_IW_TIMESTAMP', SYSDATE, attr_);
      client_sys.add_to_attr('CF$_IW_USER_ID', '#USER_ID#', attr_);
      client_sys.add_to_attr('CF$_IW_ERROR_MSG', 'Error in detaching reference in deferred sys call to IW_DFS_CONN_PENDING_DOC. INVALID_NUMBER for LU'|| lu_name||' Key:'||act_key||' Act seq:'||act_seq||'', attr_);
      client_sys.add_to_attr('CF$_IW_PROCESS', 'act', attr_);

      iw_d_f_s_error_log_clp.new__ (info_, objkey_, objversion_, attr_, 'DO');
      client_sys.add_info('Err 013', ' Error in detaching document reference. INVALID_NUMBER. Ref GLB100862.');
      COMMIT;
      RETURN;
  END;

  /* Add record to Pending Document Connections and defer execution of event IW_DFS_CONN_PENDING_DOC */
  BEGIN      
    client_sys.clear_attr(attr_);
    client_sys.add_to_attr('CF$_IW_PROCESS', 'act', attr_);
    client_sys.add_to_attr('CF$_IW_DOC_NO', doc_no_, attr_);
    client_sys.add_to_attr('CF$_IW_DOC_REV', doc_rev_, attr_);
    client_sys.add_to_attr('CF$_IW_DOC_SHEET', doc_sheet_, attr_);
    client_sys.add_to_attr('CF$_IW_DOC_CLASS', doc_class_, attr_);
    client_sys.add_to_attr('CF$_IW_KEY', act_seq, attr_);
    client_sys.add_to_attr('CF$_IW_ACTION', 'DETACH', attr_);
    client_sys.add_to_attr('CF$_IW_STATE', 'PENDING', attr_);
    client_sys.add_to_attr('CF$_IW_TIMESTAMP', SYSDATE, attr_);

    /* Create new record in Pending Document Connections */
    iw_d_f_s_pending_docs_clp.new__ (info_, objkey_, objversion_, attr_, action_);

    client_sys.clear_attr(attr_);
    client_sys.add_to_attr('EVENT_DATA_', '', attr_);  
    client_sys.add_to_attr('EVENT_ID_', 'IW_DFS_CONN_PENDING_DOC', attr_);
    client_sys.add_to_attr('EVENT_LU_NAME_', 'IwDFSPendingDocs', attr_);

    /* Call event to connect pending document as a deferred call, this should complete in < 1 minute, approx 10 seconds */
    transaction_sys.deferred_call('Event_Sys.Event_Execute', 'PARAMETER', attr_, 'DocFoldStruct Defer detach doc '||doc_no_);

    COMMIT;
    RETURN;
  END;  
END;

 

eqbstal
Rank
Userlevel 7
Badge +18

@Danul Vihan That is a nifty piece of SQL!

From this I deduct that you have a workflow that concerns Process Enrichment (creation of new records) and Validation (show the errors).

For the standard calls you perform, I see:

  1. client_sys.add_to_attr
    I don't think that this is possible. You will need a new projection in the form of a svc. Parameters needed will be popping up in the Workflow design when you perform a IFS Projection with action Call
  2. client_sys.add_info
    In another thread it has been explained that you would have to prepare a form and only give it a label. The label would be the message that you want to share to the end user.
  3. transaction_sys.deferred_call
    Totally unsure how to handle this. Maybe call an asynchronous workflow from the workflow that you are preparing. I hope others will fill in the correct BPA synonym for a deferred call (so how to do that, if possible).

I would first try to get the SQL ‘translated’ in a workflow without the deferred call, so all online. Yes, this may take performance, but you would be able to notice that the workflow works for you and the end user.

Please like this response if it helped you

Reply


Terms & ConditionsCookie settings