How to validate popup lov's data is changed or not in Oracle Apex

Question

Currently I'm creating an application in Oracle Apex 5.0. In that, one of the page contains 7 popup lov's (or select list) containing some default value which has been retrieved from more than 1 table of more than 1 schemas.

Now my task is to create and apply a validation which checks, at least one of the popup lov's data has been changed (no matter if all changes) if not then throw an error.

I've created a procedure inside a package and raise an error inside it (also I've created a function which returns boolean value) but it doesn't work at all.

I've also created hidden items for each popup lov to check those independently but it didn't work.

So, I thought maybe it because return val of lov affects my data. So, I modified my package but it also won't work.

Here's my code sample:

 CREATE OR REPLACE package body KPC_CA is


 function find_change ( t_per_id      KPCPAY.EMP_INF.per_id%type, 
                        shift1        KPCPAY.EMP_INF.shft1%type,
                        shift2        KPCPAY.EMP_INF.shft2%type,
                        shift3        KPCPAY.EMP_INF.shft3%type,
                        wklyoff       KPCPAY.EMP_INF.wklyoff%type,
                        sup_name      KPCPAY.EMP_INF.emp_name%type,
                        sup_token_no  KPCPAY.EMP_INF.r_emp_no%type,
                        loc_desc      KPCPAY.EMP_INF.loc_desc%type,
                        dept_name     KPCPAY.EMP_INF.dept_name%type,
                        sbu           KPCPAY.EMP_INF.sbu_id%type,
                        effective_date          date
                  ) return number as

                        t_shift1        KPCPAY.EMP_INF.shft1%type;
                        t_shift2        KPCPAY.EMP_INF.shft2%type;
                        t_shift3        KPCPAY.EMP_INF.shft3%type;
                        t_wklyoff       KPCPAY.EMP_INF.wklyoff%type;
                        t_sup_name      KPCPAY.EMP_INF.emp_name%type;
                        t_sup_token_no  KPCPAY.EMP_INF.r_emp_no%type;
                        t_location      KPCPAY.EMP_INF.loc_desc%type;
                        t_dept_name     KPCPAY.EMP_INF.dept_name%type;
                        t_sbu           KPCPAY.EMP_INF.sbu_id%type;


                        flag                      boolean := false;


 begin
     begin
         select shft1, shft2, shft3, wklyoff, loc_desc, r_emp_no, 
                dept_name into t_shift1, t_shift2, t_shift3, t_wklyoff, 
                t_location, t_sup_token_no, t_dept_name                                
         from KPCPAY.EMP_INF 
         where per_id = t_per_id;


         select emp_name into t_sup_name
         from KPCPAY.EMP_INF 
         where emp_no = (select r_emp_no 
                         from KPCPAY.EMP_INF 
                         where per_id = t_per_id);                      


         SELECT distinct(decode ( SUBSTR(ORGANIZATION_CODE, 1, 1), 
                                                             '1','CORP',
                                                              '2','ACD',
                                                              '3','TRM',
                                                              '4','ACR',
                                                              '5','RR',
                                                              '6','RRS'
                            )) "decode" into t_sbu
            from apps.org_organization_definitions 
            where ORGANIZATION_ID= (select sbu_id 
                            from kpcpay.emp_inf 
                            where per_id = t_per_id);

     end;


     begin

         if t_shift1 != shift1 then
             flag := true;
         end if;

         if t_shift2 != shift2 then
             flag := true;
         end if;

         if t_shift3 != shift3 then
             flag := true;
         end if;

         if t_wklyoff != wklyoff then
                flag := true;
         end if;

         if t_sup_token_no != sup_token_no then
              flag := true;
         end if;

         if t_sup_name != sup_name then
              flag := true;
         end if;

         if t_location != loc_desc then
               flag := true;
         end if;

         if t_dept_name != dept_name then
                flag := true;
         end if;

         if t_sbu != sbu then
               flag := true;
         end if;                

      end;


      if flag = true then
         return 1;
      else
        return 0;
          -- or raise_application_error
      end if;              

    end find_change;
    end KPC_CA;

Answer 1

This issue was resolved. Just re-importing the application did the trick.

Answer 2

Honestly, what I would do is the following:

1. Create your 7 popup LOVs, as you've already done. Call them, say, P1_POPUP_1, etc.
2. For each one of those, create a Hidden item, called P1_POPUP_1_ORIG, where the Source is the P1_POPUP_x item.
3. Then create a validation of type PL/SQL Expression, and the expression is:

.

:P1_POPUP_1 != :P1_POPUP_1_ORIG AND :P1_POPUP_2 != :P1_POPUP_2_ORIG ...

This works as long as you know the starting value and ending values will not be null. If you need to handle nulls, the expression would change a bit.

You could also change to different validations for each item, so you know which one the user did not change.

This way there is no complicated function that needs to be maintained.

Edit: There are multiple ways to handle null values. In this sort of case I usually do something like:

NVL( :P1_POPUP_1, '*' ) != NVL( :P1_POPUP_1_ORIG, '*' ) AND 
NVL( :P1_POPUP_2, '*' ) != NVL( :P1_POPUP_2_ORIG, '*' ) AND  ...

Here, I've chosen '*' as a simple string I know will never appear as an actual value, but you may want to choose something more appropriate for your expected values.