APEX

Version: 24.2.6

Hello,

I have an Interactive Grid that indicates if Managers (MGR), Manager Owners (MO), or Relief Managers (REL) are ‘ON’ or ‘OFF’ at a Store. These "Managers" move around to different Stores depending on need. The current Manager may be on vacation or is no longer working at a Store and another manager needs to fill that spot.

The validation rule is that only 1 “Manager” (be it a Manager (MGR), Manager Owner (MO), or Relief (REL) ) is ‘ON’ at a Store at one time. When the manager coming in is set to ‘ON’ at that Store, there has to be manager set to ‘OFF’ for that same Store.

I'm guessing I need to somehow loop through the Grid and determine that if there's a manager ‘ON’ at a Store that there's a manager set to ‘OFF’ too, but if that's true then that's where I'm stuck.

I tried to do a Validation of PL/SQL, but I'm having an issue with determining if there's a manager set to ‘OFF’.

This is what I've tried:

DECLARE
v_cnt NUMBER := 0;
v_entity VARCHAR2(100);
v_mgr_name VARCHAR2(100);
v_eff_wk NUMBER;
V_hdb_entities_id hdb_entities.hdb_entities_id%TYPE;

BEGIN
SELECT COUNT(*)
INTO v_cnt
FROM hdb_manager_updates
WHERE manager_type IN ( 'MGR','MO','REL' )
AND status = 'ON'
AND entity_nbr = :ENTITY_NBR --(This is the ID for the Store) ;

v_hdb_entities_id := :ENTITY_NBR;

IF ( v_cnt >= 1 ) THEN
IF ( :STATUS != 'OFF' AND v_hdb_entities_id = :ENTITY_NBR ) THEN
SELECT mgr_first_name ||' ' || mgr_middle_name || ' ' || mgr_last_name
,fiscal_week
INTO v_mgr_name
,v_eff_wk
FROM hdb_manager_updates
WHERE entity_nbr = :ENTITY_NBR
AND manager_type IN ( 'MGR','MO','REL' )
AND status = 'ON';

       SELECT entity\_nbr || ' - ' || entity\_name  
       INTO v\_entity  
       FROM hdb\_entities  
       WHERE hdb\_entities\_id = :ENTITY\_NBR;

       RETURN ('There can only be 1 Mgr, MO, or Relief per Store.' || '<br/>' ||  
               'Store Number: ' || v\_entity  || '<br/>' ||  
               'Manager Name: ' || v\_mgr\_name || '<br/>' ||  
               'Effective Week: ' || v\_eff\_wk);  
   END IF;  

END IF;

EXCEPTION
WHEN no_data_found THEN
NULL;
END;

Please let know if there are questions and/or something isn't clear.

Thanks,

Joe