Home » Developer & Programmer » Forms » Write line by line to a grid (Forms Builder 10g)
Write line by line to a grid [message #679328] Fri, 21 February 2020 14:59 Go to next message
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;
The cursor I'm working on adding is is bracketed in dashes to make it easy to find.

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 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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 Go to previous message
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.
Previous Topic: 6i olb file unable to open in forms 12c 12.2.1.4
Next Topic: help send a message
Goto Forum:
  


Current Time: Thu Mar 28 15:20:57 CDT 2024