| Write line by line to a grid [message #679328] |
Fri, 21 February 2020 14:59  |
 |
ETH2MOON
Messages: 3
Registered: February 2020
|
Junior Member |
|
|
Hello, long time reader, first post.
I'm pretty new to pl/sql so hopefully I'm not missing something easy here.
I'm working on creating a form for allowing users to change a customers group based on checked items on another canvas. The code for making those changes works fine, I'm stuck on the reporting portion. They want a tabular item populated with the changes made to each row looped through. I thought something like shown here might work (link in comment as I cannot post a link in my first post) so I have tried setting up something similar. However I am getting Error: FRM-41051: You cannot create records here. The following code is on a WHEN_BUTTON_PRESSED trigger on the datablock level.
DECLARE
header_id NUMBER;
rec_count NUMBER;
group_num NUMBER;
tp_code VARCHAR(35);
partner_code VARCHAR(35);
group_code VARCHAR2(35);
tran_id VARCHAR2(30);
net_code VARCHAR2(150);
doc_id VARCHAR2(200);
partner_desc VARCHAR2(240);
---------------------------------------------------------------------------
CURSOR c_test
is
Select tp_header_id --test cursor
from xxec_partners_v
where tp_header_id = header_id;
-----------------------------------------------------------------------------
BEGIN
GO_BLOCK('xxec_partners_v');
FIRST_RECORD;
rec_count := 0;
header_id := 0;
group_num := 0;
group_code := NULL;
tp_code := NULL;
partner_code := NULL;
partner_desc := NULL;
tran_id := NULL;
net_code := NULL;
LOOP
IF CHECKBOX_CHECKED('xxec_partners_v.sel_list') THEN
rec_count := rec_count + 1;
header_id := :xxec_partners_v.tp_header_id;
doc_id := :xxec_partners_v.document_id;
partner_code := :xxec_partners_v.partner;
partner_desc := :xxec_partners_v.tp_description;
tran_id := :xxec_partners_v.translator_code;
net_code := :xxec_partners_v.network;
group_code := :xxec_partners_v.tp_group_code;
group_num := :xxec_partners_v.tp_group_id;
IF (:group_change.partner_replacement_replace IS NOT NULL) THEN
partner_code := REPLACE(partner_code, :group_change.partner_replacement_replace, :group_change.partner_replacement_with);
UPDATE ece_tp_headers
SET tp_code = partner_code
, last_updated_by = apps.fnd_global.user_id
, last_update_date = SYSDATE
WHERE 1 = 1
AND header_id = tp_header_id;
END IF;
IF (:group_change.description_replacement_replac IS NOT NULL) THEN
partner_desc := REPLACE(partner_desc, :group_change.description_replacement_replac, :group_change.description_replacement_with);
UPDATE ece_tp_headers
SET tp_description = partner_desc
, last_updated_by = apps.fnd_global.user_id
, last_update_date = SYSDATE
WHERE 1 = 1
AND header_id = tp_header_id;
END IF;
IF (:group_change.translator_code_replace IS NOT NULL) THEN
tran_id := REPLACE(tran_id, :group_change.translator_code_replace, :group_change.translator_code_with);
UPDATE ece_tp_details
SET translator_code = tran_id
, last_updated_by = apps.fnd_global.user_id
, last_update_date = SYSDATE
WHERE 1 = 1
AND header_id = tp_header_id;
--SQL%ROW_COUNT work on this
END IF;
IF (:group_change.network IS NOT NULL) THEN
net_code := :group_change.network;
UPDATE ece_tp_headers
SET attribute7 = net_code
, last_updated_by = apps.fnd_global.user_id
, last_update_date = SYSDATE
WHERE 1 = 1
AND header_id = tp_header_id;
END IF;
IF (:group_change.group_new IS NOT NULL) THEN
group_code := :group_change.group_new;
SELECT tp_group_id
INTO group_num
FROM ece_tp_group
WHERE 1 = 1
AND tp_group_code = group_code;
UPDATE ece_tp_headers
SET tp_group_id = group_num
, last_updated_by = apps.fnd_global.user_id
, last_update_date = SYSDATE
WHERE 1 = 1
AND header_id = tp_header_id;
END IF;
END IF;
EXIT WHEN :system.last_record = 'TRUE';
-----------------------------------------------------------------------------------------------------------
go_block('group_change_report');
first_record;
for CURSOR in c_test LOOP
--- test cursor
:group_change_report.test_name := header_id;
next_record;
end loop;
go_block('xxec_partners_v');
-------------------------------------------------------------------------------------------------
NEXT_RECORD;
END LOOP;
FND_MESSAGE.SET_STRING('There were ' || rec_count || ' selected rows. Info: ' || header_id || ', ' || doc_id || ', ' || partner_code); -- For testing
FND_MESSAGE.SHOW();
COMMIT;
END;
Any information or help would be greatly appreciated and if there is any other information I can provide please let me know.
|
|
|
|
| Re: Write line by line to a grid [message #679337 is a reply to message #679328] |
Mon, 24 February 2020 03:57  |
cookiemonster
Messages: 13962
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
The first thing you should do is identify which line is actually throwing the error - run the form in debug mode or put messages in to track the execution.
One obvious problem - the loop over c_test - shouldn't that come after the other loop?
If it really needs to be inside the other loop then you need to change it so it doesn't go to the start of the group_change_report block each time - you're going to overwrite data from previous iterations.
|
|
|
|
| Re: Write line by line to a grid [message #679440 is a reply to message #679337] |
Fri, 28 February 2020 14:26 |
|
ETH2MOON
Messages: 3
Registered: February 2020
|
Junior Member |
|
|
Thank you for the reply cookiemonster,
The client has changed the scope to no longer require reporting, so at this point I am making minor changes to the loop and considering this issue resolved.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
