14.8.2.3.2 Saving Action Item Data to Tables

Save action item records and team members with insert, update, or delete logic.

The SAVE procedure stores the data from an action item into ACTION_ITEMS and ACTION_ITEM_TEAM_MEMBERS tables. Depending on the operation type passed in, it performs appropriate INSERT, UPDATE, or DELETE statements against these tables. The update logic checks which team members are still in the action item and inserts, updates, or deletes as needed.

-- in package action_items_api
procedure save(
    p_action_item              in out action_items%rowtype,
    p_action_item_team_members in out t_action_item_team_members,
    p_operation                in     t_operation)       
is
    l_new_ids                apex_t_number;
    l_action_item_member_ids apex_t_number := apex_t_number();
begin
    case p_operation
        when c_operation_insert then
            -- insert the action item
            insert into action_items(name)
            values (p_action_item.name)
            returning id into p_action_item.id;
            -- Set foreign key attribute for new child rows
            for i in 1..p_action_item_team_members.count loop
                p_action_item_team_members(i).action_id := p_action_item.id;
            end loop;
            -- insert all the team members
            forall i in 1..p_action_item_team_members.count
                insert into action_item_team_members
                values p_action_item_team_members(i)
                returning id bulk collect into l_new_ids;
            -- Populate the system-assigned id
            for i in 1..p_action_item_team_members.count loop
                p_action_item_team_members(i).id := l_new_ids(i);
            end loop;
        when c_operation_update then
            update action_items
            set name = p_action_item.name,
                status = p_action_item.status
            where id = p_action_item.id;
            -- Insert/update child rows that need inserting, populating assigned id
            for i in 1..p_action_item_team_members.count loop
                if p_action_item_team_members(i).id is null then
                    -- id is null? Needs an insert
                    insert into action_item_team_members 
                    values p_action_item_team_members(i)
                    returning id into p_action_item_team_members(i).id;
                else
                    -- id is not null, needs an update
                    update action_item_team_members
                    set role = p_action_item_team_members(i).role,
                        user_id = p_action_item_team_members(i).user_id
                    where id = p_action_item_team_members(i).id
                      and action_id = p_action_item_team_members(i).action_id;
                end if;
                -- track which ids we updated and inserted so we can delete others
                l_action_item_member_ids.extend;
                l_action_item_member_ids(l_action_item_member_ids.count)
                    := p_action_item_team_members(i).id;
            end loop;   
            -- Delete any existing team members who are not among the updates
            delete from action_item_team_members
            where action_id = p_action_item.id
            and id not in (select column_value 
                             from table(l_action_item_member_ids));
        when c_operation_delete then
            delete from action_items
             where id = p_action_item.id;
    end case;
end save;