Oracle APEX PL/SQL dynamic Action Function Body

Question

Any help with this pl/sql and I would very much appreciate it. First off some basic info. I am using APEX 4.2 and have a Interactive report and form. I am interested in building a dynamic action on the form so that when the LEAVE_DATE is changed a PL/SQL function body (dynamic action) calculates whether the person has left within 12 month of their start date. Here are the columns in play and what I have attempted. The two calculations and be two separate dynamic actions. The first would be a calculation that results in a new date if the person left within 12 months of their BILL_READY DATE and the second returns 'Early Leaver' if within 12 months. Would be great if they could be in the same calculation though.

:P24_LEAVE_DATE (DATE)
:P24_BILL_READY (DATE)
:p24_NEW_LEAVE_DATE (DATE) CALULATED
:P24_MESSAGE (VARCHAR 255) --This would display 'Early Leaver' if within 12 months else null.

begin
case when :P24_LEAVING_DATE between :P24_BILL_READY AND (:P24_BILL_READY + 365) THEN (:P24_LEAVING_DATE - 30)
ELSE :P24_LEAVING_DATE
END CASE;
END;

Thanks for your help,

Rene Rene · Accepted Answer · 2015-11-19T14:55:45

You need to create a dynamic action of type "change" on item P24_LEAVE_DATE. The action will be a set value action of type PLSQL function body. The code would be something along these lines:

declare
   l_date date;
begin
   if :p24_leaving_date between :p24_bill_ready and (:p24_bill_ready + 365)
   then
      l_date := :p24_leaving_date - 30;
   else
      l_date := :p24_leaving_date;
   end if;
   return l_date;
end;

The only thing I'm not sure off is how APEX handles date values. If they are handled as string values you need to add some string to date code in there.

Oracle APEX PL/SQL dynamic Action Function Body

1 Answers