14.8.2.4 Inspecting the Core Action Item API Body
Inspect the full core API package body that coordinates JSON parsing, data changes, and validation.
The full code of the ACTION_ITEMS_API package body appears below.
create or replace package body action_items_api as
subtype t_get_object_approach is pls_integer range 1..2;
c_approach_programmatic constant t_get_object_approach := 1;
c_approach_sql constant t_get_object_approach := 2;
subtype t_operation is pls_integer range 1..3;
c_operation_insert constant t_operation := 1;
c_operation_update constant t_operation := 2;
c_operation_delete constant t_operation := 3;
------------------------------------
procedure before_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
begin
if p_operation = c_operation_insert then
-- Providing an ID with insert not allowed
app_common.report_if_error(
action_items_common.ensure_null_id_for_new_action(p_action_item.id));
end if;
if p_operation in (c_operation_insert,c_operation_update) then
-- Default role to member if not supplied
for j in 1..p_action_item_team_members.count loop
if p_action_item_team_members(j).role is null then
p_action_item_team_members(j).role := 'MEMBER';
else
-- Check role value is legal
app_common.report_if_error(
action_items_common.is_valid_role(p_action_item_team_members(j).role));
end if;
end loop;
end if;
end before_save;
------------------------------------
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;
------------------------------------
procedure after_save(
p_action_item in action_items%rowtype,
p_action_item_team_members in t_action_item_team_members,
p_operation in t_operation)
is
begin
if p_operation in (c_operation_insert,c_operation_update) then
app_common.report_if_error(
action_items_common.no_duplicate_team_members(p_action_item.id));
app_common.report_if_error(
action_items_common.one_lead_per_action_team(p_action_item.id));
end if;
end after_save;
------------------------------------
procedure action_item_json_to_record(
p_object in json_object_t,
p_action_item out action_items%rowtype,
p_action_item_team_members out t_action_item_team_members)
is
l_team_members_arr json_array_t;
l_team_member_obj json_object_t;
l_team_member_rec action_item_team_members%rowtype;
l_team_member_name staff.name%type;
begin
p_action_item.id := p_object.get_number('_id');
p_action_item.name := p_object.get_string('name');
p_action_item.status := p_object.get_string('status');
p_action_item_team_members := t_action_item_team_members();
l_team_members_arr := p_object.get_array('team');
for j in 0..l_team_members_arr.get_size - 1 loop
-- Fill in foreign key attribute from parent
l_team_member_rec := null;
l_team_member_rec.action_id := p_action_item.id;
l_team_member_obj := treat(l_team_members_arr.get(j) as json_object_t);
l_team_member_rec.id := l_team_member_obj.get_number('team_assignment_id');
l_team_member_rec.user_id := l_team_member_obj.get_string('staff_id');
-- if user_id is null, try to use name to look it up since name is unique
if l_team_member_rec.user_id is null then
l_team_member_name := l_team_member_obj.get_string('name');
if l_team_member_name is not null then
begin
select id
into l_team_member_rec.user_id
from staff
where name = l_team_member_name;
exception
when no_data_found then
app_common.report_error_key(
'TEAM_MEMBER_NOT_FOUND',
apex_t_varchar2('name',l_team_member_name));
end;
end if;
end if;
l_team_member_rec.role := substr(l_team_member_obj.get_string('role'),1,6);
p_action_item_team_members.extend;
p_action_item_team_members(p_action_item_team_members.count) := l_team_member_rec;
end loop;
end action_item_json_to_record;
------------------------------------
function get_object_using_sql(
p_id in number)
return clob
is
l_action_item_json_clob clob;
begin
app_common.report_if_error(
action_items_common.ensure_action_exists(p_id));
select json_object(
'_id' value ai.id,
'name' value ai.name,
'status' value ai.status,
'team' value (
select json_arrayagg(
json_object(
'team_assignment_id' value tm.id,
'role' value tm.role,
'staff_id' value tm.user_id,
'name' value s.name
)
returning clob /* json_arrayagg */)
from action_item_team_members tm
left join staff s on tm.user_id = s.id
where tm.action_id = ai.id
)
returning clob /* json_object */)
into l_action_item_json_clob
from action_items ai
where ai.id = p_id;
return l_action_item_json_clob;
end get_object_using_sql;
------------------------------------
function get_object_programmatically(
p_id in number)
return json_object_t
is
l_action_item_rec action_items%rowtype;
l_action_item_obj json_object_t := json_object_t();
l_team_members_arr json_array_t := json_array_t();
l_team_members t_action_item_team_members := t_action_item_team_members();
l_team_member_obj json_object_t;
l_team_member_name staff.name%type;
begin
app_common.report_if_error(
action_items_common.ensure_action_exists(p_id));
select *
into l_action_item_rec
from action_items
where id = p_id;
l_action_item_obj.put('_id',l_action_item_rec.id);
l_action_item_obj.put('name',l_action_item_rec.name);
l_action_item_obj.put('status',l_action_item_rec.status);
select *
bulk collect into l_team_members
from action_item_team_members
where action_id = p_id;
for j in 1..l_team_members.count loop
l_team_member_obj := json_object_t();
l_team_member_obj.put('team_assignment_id',l_team_members(j).id);
l_team_member_obj.put('role',l_team_members(j).role);
l_team_member_obj.put('staff_id',l_team_members(j).user_id);
-- Lookup staff name using id
select name
into l_team_member_name
from staff
where id = l_team_members(j).user_id;
l_team_member_obj.put('name',l_team_member_name);
-- Append team member object to the team array
l_team_members_arr.append(l_team_member_obj);
end loop;
l_action_item_obj.put('team',l_team_members_arr);
return l_action_item_obj;
end get_object_programmatically;
------------------------------------
function get_object(
p_id in number)
return json_object_t
is
c_get_object_approach constant t_get_object_approach := c_approach_sql;
l_ret json_object_t;
begin
case c_get_object_approach
when c_approach_programmatic then
l_ret := get_object_programmatically(p_id);
when c_approach_sql then
l_ret := json_object_t(get_object_using_sql(p_id));
end case;
return l_ret;
end;
------------------------------------
function get_objects(
p_search in varchar2,
p_offset in number default null,
p_limit in number default null)
return json_object_t
is
l_offset pls_integer := nvl(p_offset,0);
l_limit pls_integer := nvl(p_limit,25);
l_ret json_object_t := json_object_t();
l_items json_array_t := json_array_t();
l_row_count pls_integer := 0;
l_total_rows pls_integer := 0;
begin
for j in (select id, count(*) over() as total_count
from action_items
where upper(name) like upper('%'||p_search||'%')
order by upper(name)
offset l_offset rows
fetch first l_limit rows only)
loop
if l_total_rows = 0 then
l_total_rows := j.total_count;
end if;
l_row_count := l_row_count + 1;
l_items.append(get_object(j.id));
end loop;
l_ret.put('items',l_items);
l_ret.put('hasMore', l_offset + l_row_count < l_total_rows);
l_ret.put('totalRows',l_total_rows);
return l_ret;
end get_objects;
------------------------------------
function insert_object(
p_object in json_object_t)
return json_object_t
is
l_action_item_rec action_items%rowtype;
l_action_item_team_members t_action_item_team_members;
begin
action_item_json_to_record(
p_object => p_object,
p_action_item => l_action_item_rec,
p_action_item_team_members => l_action_item_team_members);
before_save(l_action_item_rec,l_action_item_team_members,c_operation_insert);
save(l_action_item_rec,l_action_item_team_members,c_operation_insert);
after_save(l_action_item_rec,l_action_item_team_members,c_operation_insert);
return get_object(l_action_item_rec.id);
end insert_object;
------------------------------------
function update_object(
p_object in json_object_t)
return json_object_t
is
l_action_item_rec action_items%rowtype;
l_action_item_team_members t_action_item_team_members;
begin
action_item_json_to_record(
p_object => p_object,
p_action_item => l_action_item_rec,
p_action_item_team_members => l_action_item_team_members);
before_save(l_action_item_rec,l_action_item_team_members,c_operation_update);
save(l_action_item_rec,l_action_item_team_members,c_operation_update);
after_save(l_action_item_rec,l_action_item_team_members,c_operation_update);
return get_object(l_action_item_rec.id);
end update_object;
------------------------------------
procedure delete_object(
p_id in number)
is
l_action_item_rec action_items%rowtype;
l_action_item_team_members t_action_item_team_members;
begin
l_action_item_rec.id := p_id;
before_save(l_action_item_rec,l_action_item_team_members,c_operation_delete);
save(l_action_item_rec,l_action_item_team_members,c_operation_delete);
after_save(l_action_item_rec,l_action_item_team_members,c_operation_delete);
end delete_object;
end action_items_api;Parent topic: Examining Core Action Item API Logic