Hi,
I am relatively new to Apex or more so PL/SQL and have been trying to create a 'manual' tabular form. I have not worked with collections until now. The business requirement is to allow for insert, update and delete of multi entry lines from a single tabular user interface. Note this is not high volume transactions, filtered by user and date range. The entry row has two underlying tables, both tables require inserts, updates and deletes. The tables have a parent (1) child (*) relationship. In my search for a solution I came across a Skillbuilders tutorial
Manual Tabular Forms in Oracle APEX: A Methodology that Works which I went through and then set out to adapt to suit the requirements. However I keep hitting problems and have been going round and round for a few days now. Insert, returns a PK violation error. Delete returns error violated - child record found. Update returns declared application error 'Current version of data in database has changed '|| 'since user initiated update process.' even when no changes are being made to the collection rows, on creation of collection column c047 set to 'U'.
Is the approach I am taking correct, if so what am I doing wrong or is there a better way to achieve the above requirement.
I desperately need to get this right as I have a number of similar business requirements to tackle i.e. a single tabular user interface that updates more then one table for each row in the tabular form.
Many Thanks,
S
Create collect_
-- add the filter to the collection creation at later stage
declare
l_collection_name varchar2(50);
l_bind_names APEX_APPLICATION_GLOBAL.VC_ARR2;
l_bind_values APEX_APPLICATION_GLOBAL.VC_ARR2;
l_query varchar2(32767);
v_count number;
begin
l_collection_name := 'TMS_ENTRY';
if apex_collection.collection_exists(l_collection_name)
then
apex_collection.delete_collection(
p_collection_name => l_collection_name
);
end if;
l_query :=
'select tel.id,' -- c001 => f01
|| ' te.id, ' -- c002 => f02
|| ' user_id, ' -- c003 => f03
|| ' to_char(start_date, ''DD-MON-YYYY HH24:MI''), ' -- c004 => f04
|| ' to_char(end_date,''DD-MON-YYYY HH24:MI''), ' -- c005 => f05
|| ' duration, ' -- c006 => f06
|| ' category_id, ' -- c007 => f07
|| ' ratio, ' -- c008 => f08
|| ' description, ' -- c009 => f09
|| ' task_id, ' -- c010 => f10
|| ' billing_code, ' -- c011 => f11
|| ' minutes, ' -- c012 => f12
|| ' task_user_priority, ' -- c013 => f13
|| ' task_priority, ' -- c014 => f14
|| ' null, ' -- c015 => f15
|| ' null, ' -- c016 => f16
|| ' null, ' -- c017 => f17
|| ' null, ' -- c018 => f18
|| ' null, ' -- c019 => f19
|| ' null, ' -- c020 => f20
|| ' null, ' -- c021 => f21
|| ' null, ' -- c022 => f22
|| ' null, ' -- c023 => f23
|| ' null, ' -- c024 => f24
|| ' null, ' -- c025 => f25
|| ' null, ' -- c026 => f26
|| ' null, ' -- c027 => f27
|| ' null, ' -- c028 => f28
|| ' null, ' -- c029 => f29
|| ' null, ' -- c030 => f30
|| ' null, ' -- c031 => f31
|| ' null, ' -- c032 => f32
|| ' null, ' -- c033 => f33
|| ' null, ' -- c034 => f34
|| ' null, ' -- c035 => f35
|| ' null, ' -- c036 => f36
|| ' null, ' -- c037 => f37
|| ' null, ' -- c038 => f38
|| ' null, ' -- c039 => f39
|| ' null, ' -- c040 => f40
|| ' null, ' -- c041 => f41
|| ' null, ' -- c042 => f42
|| ' null, ' -- c043 => f43
|| ' null, ' -- c044 => f44
|| ' null, ' -- c045 => f45
|| ' null, ' -- c046 => f46
|| ' ''O'', ' -- c047 => f47
|| ' wwv_flow_item.md5( tel.id
, te.id
, user_id
, start_date
, end_date
, duration
, category_id
, ratio
, description
, task_id
, billing_code
, minutes
, task_user_priority
, task_priority
) ' -- c048
|| 'from
tms_entry te
, tms_entry_line tel '
|| 'where
te.id = tel.entry_id '
;
apex_collection.create_collection_from_query_b(
p_collection_name => l_collection_name,
p_query => l_query
);
if :REQUEST = 'ADD'
then
apex_collection.add_member(
p_collection_name => l_collection_name
);
end if;
select count(*) into v_count
from apex_collections
where collection_name = 'TMS_ENTRY';
dbms_output.put_line(v_count);
end;
-----------------------------------------------------------------------------------------
create tabular form_
select
apex_item.hidden(47,c047,null,'f47_'|| '#ROWNUM#')
|| apex_item.hidden(48,c048,null,'f48_'|| '#ROWNUM#')
|| apex_item.hidden(49,seq_id,null,'f49_'|| '#ROWNUM#')
|| apex_item.hidden(1,c001,null,'f01_'|| '#ROWNUM#')
|| apex_item.checkbox(50, seq_id, null,
case
when c047 = 'D' then seq_id
end,
':', 'f50_' || '#ROWNUM#'
) as delete_checkbox
, apex_item.text(2,c002,20,50,null,'f02_' || '#ROWNUM#'
) as entry_id
, apex_item.text(3,c003,20,50,null,'f03_' || '#ROWNUM#'
) as user_id
, apex_item.date_popup(4,null,to_date(c004,'DD-MON-YYYY HH24:MI'),'DD-MON-YYYY HH24:MI',
20,2000,null,'f04_' || '#ROWNUM#',''
) as start_date
, apex_item.date_popup(5,null,to_date(c004,'DD-MON-YYYY HH24:MI'),'DD-MON-YYYY HH24:MI',
20,2000,null,'f05_' || '#ROWNUM#',''
) as end_date
, apex_item.text(6,c006,20,50,null,'f06_' || '#ROWNUM#'
) as entry_duration
, apex_item.text(7,c007,20,50,null,'f07_' || '#ROWNUM#'
) as category_id
, apex_item.text(8,c008,20,50,null,'f08_' || '#ROWNUM#'
) as ratio
, apex_item.text(9,c009,20,50,null,'f09_' || '#ROWNUM#'
) as description
, apex_item.select_list_from_query(10,nvl(c010,'-1'),'select
task_hname as display
,wbs_id as return
from
xxtms_wbs_user_task
where
user_id=:g_user_user_id or user_id is null',
null,'YES','-1','-Select Task-','f10_' || '#ROWNUM#',null,'YES'
) as task_id
, apex_item.text(11,c011,20,50,null,'f11_' || '#ROWNUM#'
) as billing_code
, apex_item.text(12,c012,20,50,null,'f12_' || '#ROWNUM#'
) as minutes
, apex_item.text(13,c013,20,50,null,'f13_' || '#ROWNUM#'
) as task_user_priority
, apex_item.text(14,c014,20,50,null,'f14_' || '#ROWNUM#'
) as task_priority
from
apex_collections
where
collection_name = 'TMS_ENTRY'
--and
-- (c003 = :g_user_user_id)
and
trunc(to_date(c004,'DD-MON-YYYY HH24:MI')) between :p18_start_date and :p18_end_date
order by c004
-----------------------------------------------------------------------------------------------------
process to collection_
declare
/* move data from page to collection 'tms_entry'
*/
l_collection_name apex_collections.collection_name%type;
l_original_md5 varchar2(3000);
l_latest_md5 varchar2(3000);
begin
l_collection_name := 'TMS_ENTRY';
for x in 1 .. apex_application.g_f49.count
loop
if apex_application.g_f01(x) is not null --ID exists, check to see if record was updated
then
select c048
into l_original_md5
from apex_collections
where collection_name = l_collection_name
and seq_id = apex_application.g_f49(x);
l_latest_md5 := wwv_flow_item.md5(
apex_application.g_f01(x)
, apex_application.g_f02(x)
, apex_application.g_f03(x)
, apex_application.g_f04(x)
, apex_application.g_f05(x)
, apex_application.g_f06(x)
, apex_application.g_f07(x)
, apex_application.g_f08(x)
, apex_application.g_f09(x)
, apex_application.g_f10(x)
, apex_application.g_f11(x)
, apex_application.g_f12(x)
, apex_application.g_f13(x)
, apex_application.g_f14(x)
);
if l_original_md5 != l_latest_md5
then
apex_collection.update_member(
p_collection_name => l_collection_name
, p_seq => apex_application.g_f49(x)
, p_c001 => apex_application.g_f01(x)
, p_c002 => apex_application.g_f02(x)
, p_c003 => apex_application.g_f03(x)
, p_c004 => apex_application.g_f04(x)
, p_c005 => apex_application.g_f05(x)
, p_c006 => apex_application.g_f06(x)
, p_c007 => apex_application.g_f07(x)
, p_c008 => apex_application.g_f08(x)
, p_c009 => apex_application.g_f09(x)
, p_c010 => apex_application.g_f10(x)
, p_c011 => apex_application.g_f11(x)
, p_c012 => apex_application.g_f12(x)
, p_c013 => apex_application.g_f13(x)
, p_c014 => apex_application.g_f14(x)
, p_c047 => 'U'
, p_c048 => apex_application.g_f48(x)
);
end if;
else --ID does not exist, must be new record
apex_collection.update_member(
p_collection_name => l_collection_name
, p_seq => apex_application.g_f49(x)
, p_c001 => apex_application.g_f01(x)
, p_c002 => apex_application.g_f02(x)
, p_c003 => apex_application.g_f03(x)
, p_c004 => apex_application.g_f04(x)
, p_c005 => apex_application.g_f05(x)
, p_c006 => apex_application.g_f06(x)
, p_c007 => apex_application.g_f07(x)
, p_c008 => apex_application.g_f08(x)
, p_c009 => apex_application.g_f09(x)
, p_c010 => apex_application.g_f10(x)
, p_c011 => apex_application.g_f11(x)
, p_c012 => apex_application.g_f12(x)
, p_c013 => apex_application.g_f13(x)
, p_c014 => apex_application.g_f14(x)
, p_c047 => 'N'
, p_c048 => apex_application.g_f48(x)
);
end if;
end loop;
if :REQUEST = 'DELETE'
then
for x in 1 .. apex_application.g_f50.count
loop
apex_collection.update_member_attribute(
p_collection_name => l_collection_name
, p_seq => apex_application.g_f50(x)
, p_attr_number => '47'
, p_attr_value => 'D'
);
end loop;
end if;
exception
when others then
raise_application_error (-20001, 'something wrong with page to collection code');
end;
-----------------------------------------------------------------------------------------------------
collection to tables_
declare
/* collection to table
*/
l_table_md5 varchar2(32);
l_table_md5_2 varchar2(32);
l_collection_name apex_collections.collection_name%type;
l_del_count pls_integer := 0;
l_upd_count pls_integer := 0;
l_ins_count pls_integer := 0;
l_success_message varchar2(32767);
/* The 'where current of' works behind the scenes by using rowid. It implicitly
selects the rowid of the table you want to update and uses that for
the where clause when you do when current of. If your cursor query
references multiple tables that's fine so long as you tell it you're
only updating one as it'll get the rowid of that table. It doesn't
get rowids for multiple tables under any circumstances.
In order to get around this there is a cursors for the update of each table.
*/
-- cursor to update tms_entry
cursor c1 (p_id1 number)
is
select wwv_flow_item.md5 (
tel.id
, te.id
, user_id
, start_date
, end_date
, duration
, category_id
, ratio
, description
, task_id
, billing_code
, minutes
, task_user_priority
, task_priority
) -- c048
from
tms_entry te
, tms_entry_line tel
where
te.id = tel.entry_id
and
tel.entry_id = c1.p_id1
for update of te.id;
-- cursor to update tms_entry_line
cursor c2 (p_id2 number)
is
select wwv_flow_item.md5 (
tel.id
, te.id
, user_id
, start_date
, end_date
, duration
, category_id
, ratio
, description
, task_id
, billing_code
, minutes
, task_user_priority
, task_priority
) -- c048
from
tms_entry te
, tms_entry_line tel
where
te.id = tel.entry_id
and
tel.entry_id = c2.p_id2
for update of tel.id;
v_entry_id number;
v_task_priority number;
v_task_user_priority number;
begin
l_collection_name := 'TMS_ENTRY';
for x in (
select *
from apex_collections
where collection_name = l_collection_name
and c047 in ('N','U','D')
)
loop
-- Before creating a new entry, find the relative priorities
xxtms_get_task_priorities(
p_task_id => x.c010
, p_user_id => x.c003
, x_task_priority => v_task_priority
, x_task_user_priority => v_task_user_priority
);
dbms_output.put_line(x.c010 ||' '|| x.c003 ||' '|| x.c047);
-- insert new record into tables
if x.c047 = 'N'then
-- get the id from the sequence. This is required so that the value can
-- be inserted into the second table.
select tms_entry_seq.nextval
into v_entry_id
from dual;
insert into tms_entry te(
te.id
, user_id
, start_date
, end_date
, duration
) values (
v_entry_id
, x.c003
, to_date(x.c004, 'DD-MON-YYYY HH24:MI')
, to_date(x.c005, 'DD-MON-YYYY HH24:MI')
, x.c006
);
insert into tms_entry_line tel(
tel.id
, entry_id
, category_id
, ratio
, description
, task_id
, billing_code
, task_user_priority
, task_priority
) values (
tms_entry_line_seq.nextval
, v_entry_id
, x.c007
, x.c008
, x.c009
, x.c010
, x.c011
, v_task_priority
, v_task_user_priority
);
-- set the minutes field
xxtms_set_minutes(v_entry_id);
l_ins_count := l_ins_count + 1;
elsif x.c047 = 'U' then
-- update tms_entry where the entry line id = collection c001
-- Optimistic lock check on tms_entry rows
open c1(x.c001);
fetch c1 into l_table_md5;
-- MD5 should be identical to collection initialisation procedure.Compare
-- the checksum values and if they are different, raise an error.
if l_table_md5 != x.c048 then
raise_application_error( -20001
, 'Current version of data in database has changed '||
'since user initiated update process.'
);
else
update tms_entry
set start_date = x.c004
, end_date = x.c005
where current of c1;
commit; -- releases lock on row
end if;
close c1;
l_upd_count := l_upd_count + 1;
-- update tms_entry_line where the entry line id = collection c001
-- Optimistic lock check on tms_entry_line rows
open c2(x.c001);
fetch c2 into l_table_md5_2;
-- MD5 should be identical to collection initialisation procedure.Compare
-- the checksum values and if they are different, raise an error.
if l_table_md5 != x.c048 then
raise_application_error( -20001
, 'Current version of data in database has changed '||
'since user initiated update process.'
);
else
update tms_entry_line
set description = x.c009 || to_char(sysdate)
, task_id = x.c010
, billing_code = x.c011
, minutes = x.c012
, task_priority = v_task_priority
, task_user_priority = v_task_user_priority
where current of c2;
commit; -- releases lock on row
end if;
close c2;
l_upd_count := l_upd_count + 1;
elsif x.c047 = 'D'then
delete from tms_entry te
where te.id = x.c002;
l_del_count := l_del_count + 1;
delete from tms_entry_line tel
where tel.id = x.c001;
l_del_count := l_del_count + 1;
end if;
end loop;
apex_collection.delete_collection(p_collection_name => l_collection_name);
l_success_message :=
l_ins_count || ' rows inserted, '||
l_upd_count || ' rows updated, ' ||
l_del_count || ' rows deleted';
:P18_SUCCESS_MESSAGE := l_success_message;
dbms_output.put_line(l_success_message);
exception
when others then
raise_application_error (-20001, 'something wrong with update table from collection code '|| sqlerrm);
end;
Edited by: user_sg on Nov 1, 2012 4:37 AM