Home » Developer & Programmer » Forms » Inserting multiple selected records into database when click the button (Forms 10g)
Inserting multiple selected records into database when click the button [message #466340] Mon, 19 July 2010 02:07 Go to next message
vittal_n
Messages: 10
Registered: July 2010
Location: Hyderabad
Junior Member
Hi

I got a form with few columns and a check_box. If the user selects n number of check boxes and click the button Save. The corresponding records should be inserted to XX_AP_CUSTOM table.

I have written the following below code in when-button-pressed trigger. With this i am able to insert only one record i.e where the current record indicator is there, even though multiple check-boxes(records) are selected.

declare

begin
IF :XXFBI_INV_QUOTE_ANAL_BLK.CHECK_BOX = 'Yes' THEN
-- IF CHECKBOX_CHECKED(:XXFBI_INV_QUOTE_ANAL_BLK.CHECK_BOX) = TRUE Then

--app_insert.insert_record('WHEN-BUTTON-PRESSED');

insert into xxfbi.XXFBI_INV_QUOTE_ANAL(Item,
ITEM_DESCRIPTION,
UOM,
Supplier,
RFQ#,
Quote#,
quantity,
Quote_Currency,
Price,
Payment_Terms,
Freight_terms,
FOB_Port,
TM_Reason,
PM_Reason,
Lead_Time,
Effective_from_date,
Effective_to_date)
values( :XXFBI_INV_QUOTE_ANAL_BLK.ITEM,
:XXFBI_INV_QUOTE_ANAL_BLK.ITEM_DESCRIPTION,
:XXFBI_INV_QUOTE_ANAL_BLK.UOM,
:XXFBI_INV_QUOTE_ANAL_BLK.Supplier,
:XXFBI_INV_QUOTE_ANAL_BLK.RFQ#,
:XXFBI_INV_QUOTE_ANAL_BLK.QUOTE#,
:XXFBI_INV_QUOTE_ANAL_BLK.QUANTITY,
:XXFBI_INV_QUOTE_ANAL_BLK.QUOTE_CURRENCY,
:XXFBI_INV_QUOTE_ANAL_BLK.PRICE,
:XXFBI_INV_QUOTE_ANAL_BLK.PAYMENT_TERMS,
:XXFBI_INV_QUOTE_ANAL_BLK.FREIGHT_TERMS,
:XXFBI_INV_QUOTE_ANAL_BLK.FOB_PORT,
:XXFBI_INV_QUOTE_ANAL_BLK.TM_REASON,
:XXFBI_INV_QUOTE_ANAL_BLK.PM_REASON,
:XXFBI_INV_QUOTE_ANAL_BLK.LEAD_TIME,
:XXFBI_INV_QUOTE_ANAL_BLK.EFFECTIVE_FROM_DATE,
:XXFBI_INV_QUOTE_ANAL_BLK.EFFECTIVE_TO_DATE
);

standard.commit;
--commit;

ELSE

NULL;
fnd_message.set_name('FND','Records not selected to Save');
FND_MESSAGE.ERROR;
Raise FORM_TRIGGER_FAILURE;

END IF;

EXCEPTION
WHEN OTHERS THEN
fnd_message.set_name('FND','Unable to Insert');
FND_MESSAGE.ERROR;
raise form_trigger_failure;

end;


I know that i have to use last_record and first_record and for loop to insert multiple selected records, but dont know how to do it.

I would be glad if someone could help me on this.

Report message to a moderator

Re: Inserting multiple selected records into database when click the button [message #466355 is a reply to message #466340] Mon, 19 July 2010 03:22 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Something like this?
first_record;
loop
  if checkbox_checked('checkbox_item_name') then
     insert into some_table;
  end if;
  exit when :system.last_record = 'TRUE';
  next_record;
end loop;

Report message to a moderator

Re: Inserting multiple selected records into database when click the button [message #466363 is a reply to message #466355] Mon, 19 July 2010 04:34 Go to previous messageGo to next message
vittal_n
Messages: 10
Registered: July 2010
Location: Hyderabad
Junior Member
Hi Littlefoot,

Thanks for immediate reply.

I have used below code. even after that i am able to insert only one record.

declare

begin

first_record;

loop
IF :XXFBI_INV_QUOTE_ANAL_BLK.CHECK_BOX = 'Yes' THEN
-- IF checkbox_checked(:XXFBI_INV_QUOTE_ANAL_BLK.CHECK_BOX) THEN

insert into xxfbi.XXFBI_INV_QUOTE_ANAL(Item,
ITEM_DESCRIPTION,
UOM,
Supplier,
RFQ#,
Quote#,
quantity,
Quote_Currency,
Price,
Payment_Terms,
Freight_terms,
FOB_Port,
TM_Reason,
PM_Reason,
Lead_Time,
Effective_from_date,
Effective_to_date)
values( :XXFBI_INV_QUOTE_ANAL_BLK.ITEM,
:XXFBI_INV_QUOTE_ANAL_BLK.ITEM_DESCRIPTION,
:XXFBI_INV_QUOTE_ANAL_BLK.UOM,
:XXFBI_INV_QUOTE_ANAL_BLK.Supplier,
:XXFBI_INV_QUOTE_ANAL_BLK.RFQ#,
:XXFBI_INV_QUOTE_ANAL_BLK.QUOTE#,
:XXFBI_INV_QUOTE_ANAL_BLK.QUANTITY,
:XXFBI_INV_QUOTE_ANAL_BLK.QUOTE_CURRENCY,
:XXFBI_INV_QUOTE_ANAL_BLK.PRICE,
:XXFBI_INV_QUOTE_ANAL_BLK.PAYMENT_TERMS,
:XXFBI_INV_QUOTE_ANAL_BLK.FREIGHT_TERMS,
:XXFBI_INV_QUOTE_ANAL_BLK.FOB_PORT,
:XXFBI_INV_QUOTE_ANAL_BLK.TM_REASON,
:XXFBI_INV_QUOTE_ANAL_BLK.PM_REASON,
:XXFBI_INV_QUOTE_ANAL_BLK.LEAD_TIME,
:XXFBI_INV_QUOTE_ANAL_BLK.EFFECTIVE_FROM_DATE,
:XXFBI_INV_QUOTE_ANAL_BLK.EFFECTIVE_TO_DATE
);

standard.commit;
--commit;
end if;
exit when :system.last_record = 'TRUE';
next_record;
end loop;



EXCEPTION
WHEN OTHERS THEN
fnd_message.set_name('FND','Unable to Insert');
FND_MESSAGE.ERROR;
raise form_trigger_failure;

end;




In the above code when i am using following below menationed if condition i am getting following error FRM-40105 : Unable to resolve item to Yes

IF checkbox_checked(:XXFBI_INV_QUOTE_ANAL_BLK.CHECK_BOX) THEN

I would be glad if u could help me.

Report message to a moderator

Re: Inserting multiple selected records into database when click the button [message #466365 is a reply to message #466363] Mon, 19 July 2010 04:41 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Shouldn't it be
if checkbox_checked('XXFBI_INV_QUOTE_ANAL_BLK.CHECK_BOX')


Also, please, use [code] tags which will preserve formatting. If you don't know how to use them, check the Forum Guide's "How to format your posts" section.
Please, do not report your own messages to a moderator. This option is not meant to be used that way.

[Updated on: Mon, 19 July 2010 04:42]

Report message to a moderator

Re: Inserting multiple selected records into database when click the button [message #466366 is a reply to message #466365] Mon, 19 July 2010 04:54 Go to previous messageGo to next message
vittal_n
Messages: 10
Registered: July 2010
Location: Hyderabad
Junior Member
Now i am not getting FRM-40105 : Unable to resolve item to Yes

but still only one record is getting inserted. Only present record indicator record is getting inserted even though 5 records has been selected.


Please check the latest code


declare

begin

first_record;

loop 
-- IF :XXFBI_INV_QUOTE_ANAL_BLK.CHECK_BOX = 'Yes'   THEN
 if checkbox_checked('XXFBI_INV_QUOTE_ANAL_BLK.CHECK_BOX') then
 --IF checkbox_checked(:XXFBI_INV_QUOTE_ANAL_BLK.CHECK_BOX)  THEN
 --	IF CHECKBOX_CHECKED(:XXFBI_INV_QUOTE_ANAL_BLK.CHECK_BOX) = TRUE Then

--app_insert.insert_record('WHEN-BUTTON-PRESSED');

insert into xxfbi.XXFBI_INV_QUOTE_ANAL(Item, 
        ITEM_DESCRIPTION,
        UOM,
        Supplier,
        RFQ#, 
        Quote#,
        quantity,
        Quote_Currency,
        Price,
        Payment_Terms,
        Freight_terms,
        FOB_Port,
        TM_Reason,
        PM_Reason,
        Lead_Time,
        Effective_from_date,
        Effective_to_date)
 values( :XXFBI_INV_QUOTE_ANAL_BLK.ITEM,
 				:XXFBI_INV_QUOTE_ANAL_BLK.ITEM_DESCRIPTION,
        :XXFBI_INV_QUOTE_ANAL_BLK.UOM,
        :XXFBI_INV_QUOTE_ANAL_BLK.Supplier,
        :XXFBI_INV_QUOTE_ANAL_BLK.RFQ#, 
        :XXFBI_INV_QUOTE_ANAL_BLK.QUOTE#,
        :XXFBI_INV_QUOTE_ANAL_BLK.QUANTITY,
        :XXFBI_INV_QUOTE_ANAL_BLK.QUOTE_CURRENCY,
        :XXFBI_INV_QUOTE_ANAL_BLK.PRICE,
        :XXFBI_INV_QUOTE_ANAL_BLK.PAYMENT_TERMS,
        :XXFBI_INV_QUOTE_ANAL_BLK.FREIGHT_TERMS,
        :XXFBI_INV_QUOTE_ANAL_BLK.FOB_PORT,
        :XXFBI_INV_QUOTE_ANAL_BLK.TM_REASON,
        :XXFBI_INV_QUOTE_ANAL_BLK.PM_REASON,
        :XXFBI_INV_QUOTE_ANAL_BLK.LEAD_TIME,
        :XXFBI_INV_QUOTE_ANAL_BLK.EFFECTIVE_FROM_DATE,
        :XXFBI_INV_QUOTE_ANAL_BLK.EFFECTIVE_TO_DATE
        );

standard.commit;
--commit;
end if;
exit when :system.last_record = 'TRUE';
next_record;
end loop;



EXCEPTION
    WHEN OTHERS THEN
      fnd_message.set_name('FND','Unable to Insert');
			FND_MESSAGE.ERROR;
      raise form_trigger_failure;

end;

Report message to a moderator

Re: Inserting multiple selected records into database when click the button [message #466375 is a reply to message #466366] Mon, 19 July 2010 05:50 Go to previous message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Set the breakpoint to the FIRST_RECORD line and run form in debug mode. Trace what's going on.

Report message to a moderator

Previous Topic: Oracle Forms search criteria
Next Topic: FRM-40735, ORA-06508
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Feb 03 21:41:02 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.