Custom Event to change date

@mjr86 Seems like the data format you are passing not comply with the data format which IFS required, can you pass the Date format (DD/MM/YYYY) to new wanted delivery date  and check? seems like you are passing varchar2 into date use TO_DATE(p_new_wanted_delivery_date, ‘DD/MM/YYYY’)

Kind Regards!

Niyomal.

Yes, I tried doing that with the following…

declare

    p_order_no                     varchar2(1000);
    p_old_wanted_delivery_date     DATE;
    p_new_wanted_delivery_date     DATE;

begin

    p_order_no                                  := '&OLD_ORDER_NO';
    p_old_wanted_delivery_date                  := TO_DATE('&OLD_WANTED_DELIVERY_DATE', 'DD/MM/YYYY');
    p_new_wanted_delivery_date                  := TO_DATE('&NEW_WANTED_DELIVERY_DATE', 'DD/MM/YYYY');
 
    IFSAPP.S3_WNTD_DEL_DT_CHNG_API.WANTED_DEL_DATE_CHANGE(p_order_no,p_old_wanted_delivery_date,p_new_wanted_delivery_date);

end;

And I get the same error.

An example of one of the wanted delivery dates is: 28/08/2024

@mjr86 

can you share your custom function to update the wanted delivery date in the package S3_WNTD_DEL_DT_CHNG_API.WANTED_DEL_DATE_CHANGE?

Yes, sure…

 

 PROCEDURE wanted_del_date_change (
      p_order_no                   IN VARCHAR2, 
      p_old_wanted_delivery_date    IN DATE,
      p_new_wanted_delivery_date    IN DATE
   ) AS

      info_                             VARCHAR2(1000);
      objkey_                           VARCHAR2(1000);
      objversion_                       VARCHAR2(1000);
      attr_cf_                          VARCHAR2(1000);
      action_                           VARCHAR2(1000) := 'DO';
      v_user_name                       VARCHAR2(1000);
      v_rowcount_                       NUMBER; 
      v_last_new_wnt_delivery_date      DATE; 
      v_difference                      NUMBER; 
      v_first_old_date                  DATE;
      v_new_date                        DATE;                   
      v_original_difference             NUMBER; 
      
   BEGIN      
      -- Get the current user making the change
      v_user_name := ifsapp.fnd_session_api.get_oracle_user;

      -- Retrieve the latest row count for the given order number and increment by 1 to capture number of changes made to order
      SELECT NVL(MAX(CF$_CHANGE_ID), 0) + 1
      INTO v_rowcount_
      FROM IFSAPP.S3_WANTED_DELIV_DATE_CHNG_CLT
      WHERE CF$_ORDER_NUMBER = p_order_no;

      -- Retrieve the most recent new wanted delivery date or set it to the original if none exists
      SELECT NVL(MAX(CF$_NEW_WNT_DELIV_DATE), p_old_wanted_delivery_date)
      INTO v_last_new_wnt_delivery_date
      FROM IFSAPP.S3_WANTED_DELIV_DATE_CHNG_CLT
      WHERE CF$_ORDER_NUMBER = p_order_no;

      -- Calculate difference between old wanted delivery date and new wanted delivery date entered
      v_difference := p_new_wanted_delivery_date - v_last_new_wnt_delivery_date;

      -- Proceed if the wanted delivery date has changed
      IF p_new_wanted_delivery_date != v_last_new_wnt_delivery_date THEN
         
         -- Clear attributes
         client_sys.clear_attr(attr_cf_);
         
         -- Populate the attributes for S3WantedDelivDateChng
         client_sys.add_to_attr ('CF$_CHANGE_DATE', SYSDATE, attr_cf_);
         client_sys.add_to_attr ('CF$_CHANGE_ID', v_rowcount_, attr_cf_);
         client_sys.add_to_attr ('CF$_DIFFERENCE', v_difference, attr_cf_); 
         client_sys.add_to_attr ('CF$_NEW_WNT_DELIV_DATE', p_new_wanted_delivery_date, attr_cf_);
         client_sys.add_to_attr ('CF$_OLD_WNTD_DELIV_DATE', p_old_wanted_delivery_date, attr_cf_);
         client_sys.add_to_attr ('CF$_ORDER_NUMBER', p_order_no, attr_cf_);
         client_sys.add_to_attr ('CF$_USER_NAME', v_user_name, attr_cf_);      

         -- New API
         IFSAPP.S3_WANTED_DELIV_DATE_CHNG_CLP.New__ (
            info_,
            objkey_,
            objversion_,
            attr_cf_,
            action_
         );
         
         -- Commit the changes if the action is 'DO'
         IF action_ = 'DO' THEN
            COMMIT;
         END IF;
         
         -- Below reads what's inserted above to figure out difference in dates
         
         -- Capture the very first old wanted delivery date
         SELECT CF$_OLD_WNTD_DELIV_DATE
         INTO v_first_old_date
         FROM IFSAPP.S3_WANTED_DELIV_DATE_CHNG_CLT
         WHERE CF$_ORDER_NUMBER = p_order_no
         AND CF$_CHANGE_ID = 1;
         
         -- Capture the most recent new wanted delivery date to work out original difference
         SELECT CF$_NEW_WNT_DELIV_DATE
         INTO v_new_date
         FROM IFSAPP.S3_WANTED_DELIV_DATE_CHNG_CLT
         WHERE CF$_ORDER_NUMBER = p_order_no
         AND CF$_CHANGE_ID = (
            SELECT MAX(CF$_CHANGE_ID)
            FROM IFSAPP.S3_WANTED_DELIV_DATE_CHNG_CLT
            WHERE CF$_ORDER_NUMBER = p_order_no
         );

        -- Original difference is the difference in days between first old wanted date and newest wanted delivery date required
         v_original_difference := v_new_date - v_first_old_date;
         
        -- Populate CF$_ORGINAL_DIFFERENCE with above value of v_original_difference
         client_sys.add_to_attr ('CF$_ORGINAL_DIFFERENCE', v_original_difference, attr_cf_);
         
         -- Modify API
         IFSAPP.S3_WANTED_DELIV_DATE_CHNG_CLP.Modify__ (
            info_,
            objkey_,
            objversion_,
            attr_cf_,
            action_
         );
        
         -- Commit the changes if the action is 'DO'
         IF action_ = 'DO' THEN
            COMMIT;
         END IF;

      ELSE
         DBMS_OUTPUT.PUT_LINE('No change detected in wanted delivery date, no row inserted.');
      END IF;

   END wanted_del_date_change;

The Dates in Events are often (always) parsed as string literals, not dates, and they’re not passed with “DD/MM/YYYY” mask.

Just try to raise an error message in the event action showing what the date looks like; high likelihood it will be something like “18-FEB-24”, and not 18/02/2024.

Change the variables to varchar2 type, assign them the old & new values, then add error_sys.record_general( p_old_wanted_delivery_date, p_new_wanted_delivery_date); after you assign the value to the variable.

Depending on the result, you’ll be able to then work on the string literal and converting it to a date with the right mask.

Best practice is also not to do any explicit commit in your package code, because you risk commiting partial transactions if something else fails AFTER your trigger.

First thing to check is what data type you have on the WANTED_DELIVERY_DATE attribute in the event. You probably have “DATE” which is the default one:

If so the default date format for event actions is: YYYY-MM-DD

The format event actions use you can check here:

So an example of your to_date will be:

TO_DATE('&OLD_WANTED_DELIVERY_DATE', 'YYYY-MM-DD');

Hi Tomas,

I checked the Solution manager, and it is the same date as your screenshot. So I applied the following…

declare

    p_order_no                                   varchar2(1000);
    p_old_wanted_delivery_date       DATE;
    p_new_wanted_delivery_date     DATE;

begin

    p_order_no                                        := '&OLD:ORDER_NO';
    p_old_wanted_delivery_date            := TO_DATE('&OLD_WANTED_DELIVERY_DATE', 'YYYY-MM-DD');
    p_new_wanted_delivery_date          := TO_DATE('&NEW_WANTED_DELIVERY_DATE', 'YYYY-MM-DD');
    IFSAPP.S3_WNTD_DEL_DT_CHNG_API.WANTED_DEL_DATE_CHANGE(p_order_no,p_old_wanted_delivery_date,p_new_wanted_delivery_date);

end;

And it gave the following error…

ORA-01841: (full) year must be between -4713 and +9999, and not be 0

I’m not sure if it’s to do with the fact that on the Customer Order screen the wanted delivery date shows as “18/10/2024 00:00:00”, but when I check the debug console it’s shows as “2024-10-18-00.00.00”.

Then when I select * from customer_order_line_cfv where order_no = “my order”, the WANTED_DELIVERY_DATE is down as “18/10/2024”

Thank you all, it’s worked as expected now. Really appreciate your help :).