APEX

Hello everyone,

Before I get into the exact nature of my 'problem at hand', I feel it would be a good idea to give you readers a little background info regarding what I'm working on.

I'm designing a page in my application (a Targeted Email Communications System) entitled the "Impacted Services Selection Screen." In a nutshell, this page will offer multiple check boxes that allow them to select/deselect the applications they wish to be notified about. Additionally, for each service, the user has the option to select the option to receive "planned" outage notifications, "unplanned" outage notifications, or both.

I'm working with a report region where I'm querying a table called "TEC_APPS" to populate my checkboxes. This table holds three columns which are:

(1) Primary Key APP_ID: Numerical identifier for each selectable service.
(2) APP_NAME: Name of the application itself (i.e. Banner INB, Oracle Collaboration Suite, etc.)
(3) ENTRY_TYPE: Type of entry within the table. I have four categories: (1) Planned Outage Notification, (2) Unplanned Outage Notification, (3) Horizontal Rule Formatting, (4) Title

The reason for the "ENTRY_TYPE" column is primarily to format the report so it doesn't look like a "table with a bunch of check boxes inside of it".

Here's the sql query for my report region:

select APP_NAME,ENTRY_TYPE,APP_ID,
decode(ENTRY_TYPE, 
     'Planned Outage Notification',apex_item.checkbox(1,APP_ID) || ENTRY_TYPE,
     'Unplanned Outage Notification',apex_item.checkbox(1,APP_ID) || ENTRY_TYPE,
     'Title',APP_NAME,
     'Horizontal Rule Formatting','<hr size="6" width = "100%" color="#898A8A">')as result
from TEC_APPS
order by APP_ID;

basically, this query generates check boxes for ENTRY_TYPEs of Planned/Unplanned Outage notification, Formatted Title Headers for an ENTRY_TYPE of a title, and an html Horizontal Rule for ENTRY_TYPES of horizontal rule formatting (the reason being my boss's specifications required there to be a formatted line that separates each check box group by context). That way, the groups will look like (note '[]' are meant to represent the check boxes):

BANNER INB
[ ] Planned Outage Notification
[ ] Unplanned Outage Notification
___________________________
PORTAL
[ ] Planned Outage Notification
[ ] Unplanned Outage Notification
etc....

Additionally, I have an after submit process which uses the apex global array to store the selected check boxes and perform an insert into a different table called "TEC_SERVICES", which has the columns:
(1) PRIMARY_KEY SERVICE_ID: Numerical Identifier for each specific service requested for notification.
(2) SUB_ID ("subscriber id"): Numerical Identifier for each person using this application
(3) APP_ID: Foreign key reference to "TEC_APPS" which holds information regarding which application the user has selected for each SERVICE_ID

Here's the code for the process:

DECLARE
   
   l_arrayMark  NUMBER;

   CURSOR c_id_check (aMark IN NUMBER) IS
   SELECT service_id,sub_id,app_id
   FROM tec_services
   WHERE sub_id = :p4_sub_id
   AND app_id = aMark;

   r_id_check c_id_check%ROWTYPE;

BEGIN

FOR i in 1..APEX_APPLICATION.G_F01.count
LOOP

   BEGIN 
      l_arrayMark := to_number(APEX_APPLICATION.G_F01(i));

    OPEN c_id_check(l_arrayMark);
    LOOP
      FETCH c_id_check into r_id_check;
    
      IF c_id_check%NOTFOUND THEN
        insert into "TEC_SERVICES"
        ( "SERVICE_ID",
          "SUB_ID",
          "APP_ID"    )
        values
        ( TEC_SERVICES_SEQ.nextval,
          :P4_SUB_ID,
          to_number(APEX_APPLICATION.G_F01(i)));
        EXIT;
    
      ELSIF c_id_check%found THEN
        EXIT;
      END IF;

    END LOOP;
    CLOSE c_id_check;
    EXCEPTION
    WHEN DUP_VAL_ON_INDEX
    THEN NULL;
    END;

END LOOP;
END;

The sub-block loop basically checks to see if the user already has a record for that APP_ID, and if so exits the loop. I threw the unique key EXCEPTION in at the end just to be safe. I'm new to sql and pl/sql so I'm still trying to learn this stuff. I have come pretty far since I first started about 5 weeks ago.

Anyways, here's my problem:

I need to create a process that queries my TEC_SERVICES table to see if the person using my application already has a subscription for the "APP_ID" each checkbox in my report represents. If they do, the page is supposed to load with that/those checkbox(s) already checked. My problem is I'm not quite sure how to update those checkbox values. I have created a "before header" process that aimed at doing this, but is not working. Here is the "rough draft" of the code:

DECLARE

   l_arrayMark NUMBER;

   CURSOR c_id_find (aMark IN NUMBER)IS
   SELECT sub_id, app_id
   FROM tec_services
   WHERE sub_id = :p4_sub_id
   AND app_id = aMark;

   r_id_find c_id_find%rowtype;

BEGIN

if APEX_APPLICATION.G_F01.count = 0 THEN return;
end if;

FOR i in 1..APEX_APPLICATION.G_F01.count
LOOP

    BEGIN
    l_arrayMark := to_number(APEX_APPLICATION.G_F01(i));
    OPEN c_id_find(l_arrayMark);
    LOOP

    FETCH c_id_find INTO r_id_find;

    IF c_id_find%found THEN
    apex_util.set_session_state(apex_item.G_F01,'CHECKED');
    -- ** This is where I'm getting mixed up...this is basically trying to say, "if the record for this checkbox already exists for this person, then show this checkbox as selected."
    -- ** I just don't know how I am supposed to do that.  Maybe something similar, but more along the lines of: UPDATE apex_application.g_f01(p_checked_values => 'CHECKED')
    
    ELSIF c_id_find%notfound THEN
    exit;

    END IF;
    END LOOP;
    CLOSE c_id_find;
    END;

END LOOP;
END;

I guess I just really don't know how to do this, and it's "Grinding my gears."

Any help is much appreciated.

Thank you all,

Eric

*** NOTE *** This is my first post ever in the oracle forums. I noticed that even when I include spaces and indentation in my code/post text, it doesn't display in the actual thread itself. Anyone know the markup to insert such things...or like an &nbsp?

Edited by: user11685190 on Sep 28, 2009 2:09 PM

Edited by: user11685190 on Sep 29, 2009 6:32 AM