Hi IFS Community,

I am a new bee to IFS, appreciate all the help that I get from IFS community .

To give brief details about the task I am working around -

1. Creating a new event/ event action to check for ext_inc_inv_head_tab.Invoice_recipient  (External supplier invoices table column - Invoice_recipient).
2. if the ext_inc_inv_head_tab.PO_REFERENCE column has one or more purchase order numbers and the ext_inc_inv_head_tab.Invoice_recipient column value is null or * , then look for buyer_code using PURCHASE_ORDER_API.GET_BUYER_CODE(ORDER_NO). 
3. Get the buyer_code (first value only if multiple Po exists) and update ext_inc_inv_head_tab.Invoice_recipient value with PURCHASE_ORDER_API.GET_BUYER_CODE(ORDER_NO) value. 
4. points to note here is - if already valid name exists in  ext_inc_inv_head_tab.Invoice_recipient  column then dont need any changes, if Invoice_recipient is null or * then it should be updated with first available buyer_code value.

I did some work around PLSQL script that works well, except one last think I am stuck at and looking for help on TRANSACTION_SYS.DEFERRED_CALL (), that i can use to set/update/modify the ext_inc_inv_head_tab.Invoice_recipient column value with new value i retrieved using SQL….

Happy to share more details….

DECLARE 
    INVOICE_NO_         VARCHAR2(30) := '&NEW:INVOICE_NO';
    PO_REFERENCE_       VARCHAR2(50) := '&NEW:PO_REFERENCE';
    INVOICE_RECIPIENT_  VARCHAR2(30) := '&NEW:INVOICE_RECIPIENT';
    INV_RECIPIENT_LIST_ VARCHAR2(50) := '';
    Attr_  VARCHAR2(2000);
    
    CURSOR PO_REF_LIST IS
    SELECT REGEXP_SUBSTR(PO_REFERENCE_, 'I^;]+', 1, level) ORDER_NO FROM DUAL
    CONNECT BY REGEXP_SUBSTR(PO_REFERENCE_, '<^;]+', 1, level) IS NOT NULL;    
BEGIN
    IF (INVOICE_RECIPIENT_ IS NULL OR INVOICE_RECIPIENT_ = '*') THEN
        FOR i IN PO_REF_LIST LOOP
            INV_RECIPIENT_LIST_ := CASE WHEN PURCHASE_ORDER_API.GET_BUYER_CODE(i.ORDER_NO) != '*' AND PURCHASE_ORDER_API.GET_BUYER_CODE(i.ORDER_NO) IS NOT NULL 
                                        THEN PURCHASE_ORDER_API.GET_BUYER_CODE(i.ORDER_NO) END;
                                        
            /* INV_RECIPIENT_LIST_ := CONCAT(INV_RECIPIENT_LIST_,CASE WHEN PURCHASE_ORDER_API.GET_BUYER_CODE(i.ORDER_NO) != '*' AND PURCHASE_ORDER_API.GET_BUYER_CODE(i.ORDER_NO) IS NOT NULL 
                                THEN CONCAT(PURCHASE_ORDER_API.GET_BUYER_CODE(i.ORDER_NO),';') END);  */ 

dbms_output.put_line('IF BLOCK INVOICE_RECIPIENT_INVOICE_RECIPIENT_ : ' || INV_RECIPIENT_LIST_);            
            EXIT WHEN (INV_RECIPIENT_LIST_ IS NOT NULL AND INV_RECIPIENT_LIST_ <> '*');
        END LOOP;
        dbms_output.put_line('IF BLOCK INVOICE_RECIPIENT_INVOICE_RECIPIENT_ : ' || INV_RECIPIENT_LIST_);
        Client_Sys.Clear_Attr(Attr_);
        Client_Sys.Add_To_Attr('INVOICE_RECIPIENT', INV_RECIPIENT_LIST_, Attr_);
        --TRANSACTION_SYS.DEFERRED_CALL(JOB_, ???, ATTR_, ???);
    END IF;    
END;

Thanks