Home » Developer & Programmer » Forms » fnd_message.set_string
fnd_message.set_string [message #681226] Sat, 27 June 2020 08:42 Go to next message
GHDSSS
Messages: 12
Registered: April 2020
Junior Member
Hello Team,
Hope everyone doing good and safe

I have a Question, My client required to update multiple records in the form, so I have added Checkboxs and write a query in when_button_pressed for the requirement Please find the Query below
DECLARE
    v_comments1  VARCHAR2(4000) := NULL;
    v_comments2  VARCHAR2(4000) := NULL;
    v_comments3  VARCHAR2(4000) := NULL;
    l_old_value  NUMBER;
    l_count      NUMBER := 0;
BEGIN
    v_comments1 := :so_exe.shiplinelov
                   || '-'
                   || substr(:so_exe.line_shipping, instr(:so_exe.line_shipping, '-') +
                   1);

    v_comments2 := :so_exe.shiplinelov;
    v_comments3 := :so_exe.line_shipping;
    go_block('so_exe');
    first_record;
    LOOP
        IF :so_exe.cb = 'Y' THEN
            l_count := l_count + 1;
            IF l_count > 1 THEN
                IF l_old_value != :so_exe.order_number THEN
                    fnd_message.set_string('Different Order numbers Cannot update Order Line');
                    fnd_message.show();
                    RAISE form_trigger_failure;
                END IF;

            END IF;

            l_old_value := :so_exe.order_number;

            IF :so_exe.promise_date1 IS NULL THEN
                fnd_message.set_string('Cannot update field is blank');
                fnd_message.show();
            ELSIF
                    :so_exe.line_shipping IS NULL AND :so_exe.shiplinelov IS NULL
                AND v_comments2 IS NOT NULL
            THEN
                UPDATE oe_order_lines_all
                SET
                    promise_date = :so_exe.promise_date1,
                    shipping_instructions = v_comments2
					WHERE
                    line_id = :so_exe.line_id;

                IF :system.last_record = 'TRUE' THEN
                    fnd_message.set_string('Success');
                    fnd_message.show();
                END IF;

            ELSIF
                    :so_exe.line_shipping IS NULL AND :so_exe.shiplinelov IS NULL
                AND v_comments2 IS NULL
            THEN
                fnd_message.set_string('Cannot update field is blank');
                fnd_message.show();
            ELSIF
                    :so_exe.promise_date1 IS NOT NULL AND :so_exe.line_shipping IS NOT NULL
                AND v_comments2 IS NOT NULL
            THEN 

                UPDATE oe_order_lines_all
                SET
                    promise_date = :so_exe.promise_date1,
                    shipping_instructions = v_comments1
                WHERE
                    line_id = :so_exe.line_id;

                IF :system.last_record = 'TRUE' THEN
                    fnd_message.set_string('Success');
                    fnd_message.show();
                END IF;	

            ELSIF
                    :so_exe.promise_date1 IS NOT NULL AND :so_exe.line_shipping IS NULL
                AND v_comments2 IS NOT NULL
            THEN
                UPDATE oe_order_lines_all
                SET
                    promise_date = :so_exe.promise_date1,
                    shipping_instructions = v_comments2
                WHERE
                    line_id = :so_exe.line_id;

                IF :system.last_record = 'TRUE' THEN
                    fnd_message.set_string('Success');
                    fnd_message.show();
                END IF;	

            ELSIF
                    :so_exe.promise_date1 IS NOT NULL AND :so_exe.line_shipping IS NOT NULL
                AND v_comments2 IS NULL
            THEN
                UPDATE oe_order_lines_all
                SET
                    promise_date = :so_exe.promise_date1,
                    shipping_instructions = v_comments3
                WHERE
                    line_id = :so_exe.line_id;

                    fnd_message.set_string('Success');
                    fnd_message.show();
                

            END IF;

        END IF;

        IF :system.last_record = 'TRUE' THEN
            EXIT;
        END IF;
        next_record;
    END LOOP;

    standard.commit;
    first_record;
END;
The Code is successfully updating the row but the challenge I'm facing is to display fnd_message.set_string , I have two fnd_message.set_string "success" and "Cannot update, the field is blank" if I put fnd_message.set_string in the loop this message is displaying every time, it updates each row but I need to display after every row is updated, and if I put fnd_message.set_string out the loop, both the messages are displaying please suggest me



[EDITED by LF: applied [code] tags]

[Updated on: Mon, 29 June 2020 01:38] by Moderator

Report message to a moderator

Re: fnd_message.set_string [message #681228 is a reply to message #681226] Sat, 27 June 2020 09:27 Go to previous messageGo to next message
Michel Cadot
Messages: 67363
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

In the end, feedback to your topics to know if the problem is solved, help future readers with the solution you ended, thanks people who spent time to help you.

Re: fnd_message.set_string [message #681239 is a reply to message #681228] Mon, 29 June 2020 01:44 Go to previous messageGo to next message
Littlefoot
Messages: 21592
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If I understood you correctly, this condition should be removed:
IF :system.last_record = 'TRUE' THEN
as it'll make the message appear only when the loop reaches the last record in the block; for all previously updated records, you won't see anything.

~ o ~

However: imagine there are 20 records you're updating; you'll force someone to acknowledge message 20 times. Do you really want to do that? Maybe you should consider creating a "counter" variable which would increment if UPDATE actually does something, e.g. (pseudocode)

declare
  l_counter number := 0;
begin
  loop
    update your_table set blabla;

    l_counter := l_counter + sql%rowcount;
  end loop;

  message('in total, ' || l_counter || ' records have been updated');
end;
Re: fnd_message.set_string [message #681494 is a reply to message #681239] Thu, 23 July 2020 22:27 Go to previous messageGo to next message
GHDSSS
Messages: 12
Registered: April 2020
Junior Member
Thanks for Replay issue Resolved
Re: fnd_message.set_string [message #681500 is a reply to message #681494] Fri, 24 July 2020 08:51 Go to previous message
Littlefoot
Messages: 21592
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You're welcome.
Previous Topic: Display image item from database reading all type of image
Next Topic: Image Rotation in Oracle form (11g)
Goto Forum:
  


Current Time: Tue Sep 22 03:08:55 CDT 2020