This discussion is archived
2 Replies Latest reply: Nov 27, 2012 9:52 AM by 596740 RSS

Populate DB after moving item from right panel to left panel of shuttle

596740 Newbie
Currently Being Moderated
Refer to the following forum: Re: APEX 4.1 - Populate DB based on right panel of shuttle
Refer to the following apex app: http://apex.oracle.com/pls/apex/f?p=27554:51
Login: demo
PW: demo

APEX version: 4.1.1.00.23
Oracle 11g


What do I need to add to the page process that when shuttle items are moved from the right panel back to the left panel, 'null' is assigned to the 'Analyst' column of the DB table?

My current page process updates the DB table column 'DQ_ANALYST' with the name in the select list based on the items in the right panel of the shuttle. (Again see the previous thread: Re: APEX 4.1 - Populate DB based on right panel of shuttle
declare
    tab apex_application_global.vc_arr2;
    l_count number;
begin
    tab := apex_util.string_to_table (:P51_SHUTTLE);
    for i in 1..tab.count 
    loop
    select count(*) into l_count from DQ_MANUAL_EDIT WHERE DQ_ATTRIBUTE = tab(i);
     if l_count > 0 then 
       UPDATE DQ_MANUAL_EDIT
       SET DQ_ANALYST = :P51_DQ_ANALYST
       WHERE DQ_ATTRIBUTE = tab(i);
    end if;
    end loop;
end;
DB table (before clicking button):
Field                          Analyst
Co-Borrower Credit Score       Analyst_1
Appraised Value               Analyst_1
Appraisal Identifier          Analyst_1
When 'Appraised Value' and 'Appraisal Identifier' are moved from the right panel back to the left panel and the 'Apply Changes' button is clicked, I am wanting the 'Analyst' column to be updated with 'null'.

DB table (before clicking button):
Field                          Analyst
Co-Borrower Credit Score       Analyst_1
Appraised Value               (null)
Appraisal Identifier          (null)
Here is my test code:
declare
    tab apex_application_global.vc_arr2;
    l_count number;
begin
    tab := apex_util.string_to_table (:P51_SHUTTLE);
    for i in 1..tab.count 
    loop
    select count(*) into l_count from DQ_MANUAL_EDIT WHERE DQ_ATTRIBUTE = tab(i);
     if l_count > 0 then 
       UPDATE DQ_MANUAL_EDIT
       SET DQ_ANALYST = :P51_DQ_ANALYST
       WHERE DQ_ATTRIBUTE = tab(i);
    end if;

--Doesn't work but here is an example of what I am trying to accomplish
     if l_count > 0 then 
       UPDATE DQ_MANUAL_EDIT
       SET DQ_ANALYST = null
       WHERE DQ_ATTRIBUTE <> tab(i);
    end if;

    end loop;
end;
  • 1. Re: Populate DB after moving item from right panel to left panel of shuttle
    GudrunPabst Newbie
    Currently Being Moderated
    Hi,

    if you move the entries from the right side to the left side they are no longer in the value list of :P51_SHUTTLE and therefore not in your variable "tab" . Therefore the code has to loop over all entries not in :P51_SHUTTLE and update the column DQ_ANALYST to NULL if a matching record exists.

    Regards,
    Gudrun
  • 2. Re: Populate DB after moving item from right panel to left panel of shuttle
    596740 Newbie
    Currently Being Moderated
    I used the following code. First, I run an update statement setting the DQ_ANALYST to null where the DQ_ANALYST is equal to the select list field (:P51_DQ_ANALYST). Then based on what is currently on the right panel, set the DQ_ANALYST to what is in the select list field (:P51_DQ_ANALYST).
    declare
        tab apex_application_global.vc_arr2;
        l_count number;
    begin
        UPDATE DQ_MANUAL_EDIT
        SET DQ_ANALYST = null
        WHERE DQ_ANALYST = :P51_DQ_ANALYST;
    
        tab := apex_util.string_to_table (:P51_SHUTTLE);
        for i in 1..tab.count 
        loop
        select count(*) into l_count from DQ_MANUAL_EDIT WHERE DQ_ATTRIBUTE = tab(i);
         if l_count > 0 then 
                  UPDATE DQ_MANUAL_EDIT
                  SET DQ_ANALYST = :P51_DQ_ANALYST
                  WHERE DQ_ATTRIBUTE = tab(i);
        end if;
        end loop;
    end;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points