how to transfer a value outside the block?

I think that this is correct (without ":") :
rsu.address_id =  name_in('order.address_id') ;

:rsu.address_id := name_in('order.address_id') ;

TRY THIS

you can try :

  select ATTRIBUTE1
      from RA_SITE_USES_ALL rsu
      where rsu.address_id =  name_in('order.address_id') ;

--> := is the assignment operator , it should be used here.

or try

  select ATTRIBUTE1
      from RA_SITE_USES_ALL rsu
      where rsu.address_id =  :order.address_id

--> we are using to :blockname.field_name to get the contents , i mean we are not using name_in function here...

When I use this line
rsu.address_id  := name_in('order.address_id') ;

I have this error

Compiling library CUSTOM...
  Invalidating Package Spec CUSTOM......
  Invalidating Package Body CUSTOM......
  Compiling Package Spec CUSTOM......
  Compiling Package Body CUSTOM......
ERROR 103 at line 516, column 24
  Encountered the symbol "=" when expecting one of the following:
  "." "(" "*" "%" "&" "=" "-" "+" ";" "<" "/" ">" "for" "in"
  "mod" "not" "rem" "an exponent (**)" "<> or != or ~=" ">="
  "<=" <> "and" "or" "like" "between" "group" "having"
  "intersect" "minus" "order" "union" "where" "connect"
  "is null" etc.
  Replacing "=" with ".".
Closing library CUSTOM...
FRM-30312:Failed to compile the library.

When I use this line
rsu.address_id =  name_in('order.address_id') ;

I have this error

Compiling library CUSTOM...
  Invalidating Package Spec CUSTOM......
  Invalidating Package Body CUSTOM......
  Compiling Package Spec CUSTOM......
  Compiling Package Body CUSTOM......
ERROR 231 at line 516, column 26
  function 'NAME_IN' may not be used in SQL
ERROR 0 at line 514, column 2
  SQL Statement ignored
ERROR 320 at line 0, column 0
  the declaration of the type of this expression is incomplete or malformed
ERROR 0 at line 549, column 9
  SQL Statement ignored
Closing library CUSTOM...
FRM-30312:Failed to compile the library.

When I use this line
:rsu.address_id := name_in('order.address_id') ;

I have this error

 Compiling Package Body CUSTOM......
ERROR 49 at line 516, column 8
  bad bind variable ''
ERROR 103 at line 516, column 9
  Encountered the symbol "RSU" when expecting one of the following:
  "." "(" "*" "%" "&" "=" "-" "+" ";" "<" "/" ">" "for" "in"
  "mod" "not" "rem" "an exponent (**)" "<> or != or ~=" ">="
  "<=" <> "and" "or" "like" "between" "group" "having"
  "intersect" "minus" "order" "union" "where" "connect"
  "is null" etc.
  Resuming parse at line 516, column 54.
ERROR 103 at line 562, column 5
  Encountered the symbol "IF" when expecting one of the following:
        a PL/SQL variable or double-quoted string
        a function name (an identifier or operator symbol)
        an operator symbol
        an optional designator
        a key word
  Resuming parse at line 562, column 7.
Closing library CUSTOM...
FRM-30312:Failed to compile the library.

When I use this line
rsu.address_id =  :order.address_id;

I have this error

Compiling library CUSTOM...
  Invalidating Package Spec CUSTOM......
  Invalidating Package Body CUSTOM......
  Compiling Package Spec CUSTOM......
  Compiling Package Body CUSTOM......
ERROR 49 at line 516, column 26
  bad bind variable ''
ERROR 103 at line 516, column 32
  Encountered the symbol "." when expecting one of the following:
  "by"
  Replacing "." with "by".
Closing library CUSTOM...
FRM-30312:Failed to compile the library.

All not working

it pass compling

But when I open the form, it has this error

Form-40105: unable to resolve reference to  item ORDER.ADDRESS_ID

Does it mean there is no address_id item in order block?

yes..can you double check this is there ...Does it mean there is no address_id item in order block?

Ok, I modify my query, it passed. However, it cannot pass the value to the form. It's supposed to have a value but it's nothing, I put
message(p_tax_status);

and nothing shown up

how to debug this? Is it possible to have message and value shown up in the command prompt?, instead popped up with the application.

Are you sure there is value there ? You can try to set the value directly and see if message will display it.

yes, there is a value there. I query it, it's a result. How to directly set a value? from the query or from where?

if (p_tax_status is not null ) then

   Copy( p_tax_status,'order.Attribute14');
 
    else
         null;
  end if;

when I change

  Copy( 'ABC','order.Attribute14');

it shows up on the form 'ABC'

something wrong with

 Copy( p_tax_status,'order.Attribute14');???

let me correct one thing


Copy( 'ABC','order.Attribute14');

still not shown ABC in the field. Seems something wrong with Copy( p_tax_status','order.Attribute14');

Here is what I test. I input message here

if (p_tax_status is not null ) then

      message('inside if');
   Copy( p_tax_status,'order.Attribute14');
 
    else
      message('else here');
         null;
  end if;

After I assign a value to

      select rsu.ATTRIBUTE1
        from RA_SITE_USES_ALL rsu
        where rsu.LOCATION=985290  --p_ship_to_location
      and rsu.SITE_USE_CODE='SHIP_TO';

message inside if popped up, a value copy to the field on the form

If I use  rsu.LOCATION=p_ship_to_location

message else here popped up, there is no value assigned to copy to the field on the form

I think statement Copy( p_tax_status,'order.Attribute14'); seems fine, I don't know why after putting  rsu.LOCATION=p_ship_to_location, no value??

here is my complete code.

can I use sql in custom.pll?

if (form_name = 'OEXOEMOE' and block_name = 'ORDER'
and name_in('SYSTEM.RECORD_STATUS') <> 'QUERY')
 
then
 
declare
 
     p_tax_status varchar2(100);
		
   CURSOR c_get_tax_status (p_ship_to_location number) IS
 
	select rsu.ATTRIBUTE1
        from RA_SITE_USES_ALL rsu 
        where rsu.LOCATION=p_ship_to_location  --985290
	and rsu.SITE_USE_CODE='SHIP_TO';
 
 begin 
        --open c_get_tax_status;
	OPEN c_get_tax_status(name_in('order.ship_to_location') );
        fetch c_get_tax_status into p_tax_status;          
	close c_get_tax_status;
 
  if (p_tax_status is not null ) then
 
   Copy( p_tax_status,'order.Attribute14');
  
    else
         null;
  end if;
 
 end;
 
end if;

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:

before u open the cursor, again put a display message to display the content of the variable p_ship_to_location  and see first whether it has a value or not because this is very important otherwise your cursor select will not return any records right.

Do you mean put a message like below?

message (p_ship_to_location);

CURSOR c_get_tax_status (p_ship_to_location number) IS
 
        select rsu.ATTRIBUTE1
        from RA_SITE_USES_ALL rsu
        where rsu.LOCATION=p_ship_to_location  --985290
        and rsu.SITE_USE_CODE='SHIP_TO';
 .....

This ticket is 200 points now

I develop something like this

fnd_message.set_string(' name_in = ' || name_in( 'order.ship_to_location') );

or

message ( name_in( 'order.ship_to_location')  );

all got complier error like

ERROR 103 at line 534, column 8
  Encountered the symbol "=" when expecting one of the following:
  "constant" "exception" <an identifier>
  <a double-quoted string> "table"
  Replacing "=" with "constant".
Closing library CUSTOM...
FRM-30312:Failed to compile the library.

Ok, when I open the form, my message is

it's return nothing, it's empty

does the value  gets set for this 'order.ship_to_location' by the time our code gets executed or how is that ?

I add more message like below

message ( 'order.ship_to_location before fetch is :' || name_in( 'order.ship_to_location')  );
        --open c_get_tax_status;
      OPEN c_get_tax_status(name_in('order.ship_to_location') );
        fetch c_get_tax_status into p_tax_status;          
      close c_get_tax_status;

       message ( 'order.ship_to_location is after fetch :' || name_in( 'order.ship_to_location')  );
       message ( 'p_tax_status is :' || p_tax_status  );

When I open the form, the messages popped up. I don't see any values assigned. When I query the form, I don't see values assigned. When I put rsu.LOCATION=985290, I can see p_tax_status is:NON_TAXABLE but there are nothing for order.ship_to_location is before fetch : or order.ship_to_location is after fetch :

can you attach the custom.pld code here so that i can see how some block.field name is referenced in the custom.pld...i do not need .pll because that is not ascii readable text. I need .pld file which is ascii readable.

Can you tell me how to read it? and I can tell you the information. The file is too big and also with other programming not related to my work if I send to you.

because i was not able to let you know how to read it and make you understand on how you need to do it.. i asked for the .pld file., so that i can see whether i will be able to help

Hi, I got a major progress finally and I can copy a flexifled value from one form to other now. The only problem is

each time I open the form, I did not make any changes and just view a record, then close it. it pops up a message
"Do you want to save the changes you have made" Yes, No, Cancel
If select Yes, the last_updated_date and user_id will be updated automatically
If select No, I can exit the form.

If I click on a record to pop up another related form, then close it. I have this message popped up as attached file.

I add more code to it, but it still have this message shown up. How to fix it

I want to get rid of these message. But, I don't know how??

Hi flow01 and nav_kum_v,

Both of you code hit the point. But, I just choose the wrong ID. Now I choose the right block id and create a stored procedure for the sql and pass/return the values back to it.also, I add this line
and name_in('SYSTEM.RECORD_STATUS') <> 'CHANGE'
 It work!

Attached is the whole code  for you to take a look.

I again increase 50 points to this request. Now the ticket is

if form_name = 'OEXOEMOE' and block_name = 'ORDER' 
   and name_in('SYSTEM.RECORD_STATUS') <> 'CHANGE' 
 
then
 
  declare
  
  p_tax_status varchar2(100);
  p_ship_to_site_use_id number;
 
  begin  
  
   p_ship_to_site_use_id := name_in('order.ship_to_site_use_id');
 
   XX_ORDERS_PKG.populate_taxstatus_flexfield(p_ship_to_site_use_id, p_tax_status);
   
	    if p_tax_status is not null then
		
		 copy( p_tax_status, 'order.attribute14' );
		 --Copy( '10'  , 'System.Message_Level' );
              	 --COMMIT_FORM;
 
	    if (event_name='Key-Exit')or(event_name='On-Close')
 
	    Then
		BEGIN
    			EXIT_FORM(NO_VALIDATE, NO_COMMIT);
		END;
 
		declare
  			v_errmsg varchar2(255) := error_text;
  			v_errcod NUMBER(5) := error_code;
		begin
  			IF v_errcod = 40400 THEN
     				NULL;
  			ELSE
    				message(v_errmsg);
  			END IF;
		end;
	     end if;	    
 
	    end if;
  end;
end if;
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:

Message FRM-40400 is only informative, so you can suppress it by setting :system.message_level to a higher value:
Cause:  Save complete. Action:  No action is necessary. Level:  5 Type:  Informative

So in the beginning of your code use this:
:system.message_level:='10';

Also you can read "Working with Forms Runtime Messages" in the on-line help.

Do you mean putting this line before declare?
....
:system.message_level:='10';

  declare

....

I got a complier error

ERROR 49 at line 507, column 2
  bad bind variable ''
ERROR 103 at line 507, column 3
  Encountered the symbol "SYSTEM" when expecting one of the following:
  ":=" "." "(" "%" ";" "indicator"
  . was inserted before "SYSTEM" to continue.
Closing library CUSTOM...
FRM-30312:Failed to compile the library.

Ok, I am able to get rid of one message like I attached image shown after I put

if p_tax_status is not null then
             copy( p_tax_status, 'order.attribute14' );
             copy( '10'  , 'System.Message_Level' );
          end if;

But, after I close each form, I still have this following  message asked, even I don't change anything

"Do you want to save the changes you have made" Yes, No, Cancel

according to http://www.orafaq.com/forum/t/55789/2/,  I add the following to the code


            if name_in('SYSTEM.RECORD_STATUS') = 'CHANGED' then
                COMMIT;
              END IF;
 
When I open the form and find a record, it has this message popped up as attached image. I click on OK but it never give me to close the form anymore

you cannot use  any restricted builtins in the validation triggers. you also cannot use go_block(..), go_item(..) are also restricted

So, what is your solution? how to get rid of this message

One quick question, can I do this statement

copy( ' ', 'order.attribute14' );

copy an empty value to 'order.attribute14, it seems it doesn't work, right?

Is it about this message " "Do you want to save the changes you have made" Yes, No, Cancel " ? If it is so, then it means that in your form there is at least one record with status NEW or UPDATE. Do you want to change records in this form ? If yes then the message is right. If no then you ought to see post-query triggers and if there are any assignments then put
set_record_property(:system.trigger_record,:system.trigger_block,status,QUERY_STATUS);
at the end of code.

To your quick question about copy( ' ', 'order.attribute14' ) - this is all right. If you use this statement and the item is not empty then there is another problem.

This is non-customized form, I cannot see/change post-query trigger. I am modifying custom.pll to copy a flexfield from customer form to another flexfield on order form. That probably ask me if saved. The answer is no. How can do set_record_property(:system.trigger_record,:system.trigger_block,status,QUERY_STATUS in my code shown above

I increase 50 points for this since this is additional question, but in the same project and related
copy( ' ', 'order.attribute14' ) seems not working. But when I assign a value to '', it works - copy( 'N/A ', 'order.attribute14' )


My question is if I can make it invisible when order.attribute14 = N/A. Sounds I need some formatting line. Here is the code

 if (p_tax_status is not null and p_ship_to_location is not null)  then

             copy( p_tax_status, 'order.attribute14' );

           elsif (p_tax_status is null and p_ship_to_location is not null) then
            
            copy( 'N/A', 'order.attribute14' );
            
                  if (name_in('order.attribute14')= 'N/A') then
                        name_in('order.attribute14')='';
                  end if;

My formatting part for N/A is not working, any advice?

I use the following, still cannot erase

            --copy( 'N/A', 'order.attribute14' );
               --copy( null, 'order.attribute14' );
            --ERASE ('order.attribute14');
            --DEFAULT_VALUE(null,'order.attribute14');

copy( null, 'order.attribute14' );--> i think this should work

or try...

name_in('order.attribute14'):=null;

I tried copy( null, 'order.attribute14' );--> doesn't work



then I tried name_in('order.attribute14'):=null;
I have this error when I complie


ERROR 220 at line 532, column 3
  simple name required in this context
ERROR 0 at line 532, column 3
  Statement ignored
Closing library CUSTOM...
FRM-30312:Failed to compile the library.

seems other people have the similar problem, but no solution

http://oracle.ittoolbox.com/groups/technical-functional/oracle-dev-l/null-value-is-not-copied-to-descriptive-flex-field-through-custompll-2001912

Null value is not copied to descriptive flex field through CUSTOM.pll

"How can do set_record_property(:system.trigger_record,:system.trigger_block,status,QUERY_STATUS in my code shown above" - you can try this:

if (p_tax_status is not null and p_ship_to_location is not null)  then
             copy( p_tax_status, 'order.attribute14' );
elsif (p_tax_status is null and p_ship_to_location is not null) then          
            copy( 'N/A', 'order.attribute14' );            
            if (name_in('order.attribute14')= 'N/A') then
                 name_in('order.attribute14')='';
            end if;
end if;
set_record_property(:system.trigger_record,:system.trigger_block,status,QUERY_STATUS);

2) you can try this with NULL value:
v_null:=null;
copy(NAME_IN(v_null), 'order.attribute14' );

By the way: is order.attribute14 an required item or not ? Because if it is the required item then I think that copying NULL value will be unsuccessfull.

1). I have complie error

ERROR 103 at line 551, column 24
  Encountered the symbol "SYSTEM" when expecting one of the following:
  "." "(" ")" "," "*" "%" "&" "|" "=" "-" "+" "<" "/" ">"
  "in" "mod" "not" "rem" ".." "an exponent (**)"
  "<> or != or ~=" ">=" "<=" <> "and" "or" "like" "between"
  "is null" "is not" "||" "indicator"
  . was inserted before "SYSTEM" to continue.
ERROR 103 at line 551, column 47
  Encountered the symbol "SYSTEM" when expecting one of the following:
  "." "(" ")" "," "*" "%" "&" "|" "=" "-" "+" "<" "/" ">"
  "in" "mod" "not" "rem" ".." "an exponent (**)"
  "<> or != or ~=" ">=" "<=" <> "and" "or" "like" "between"
  "is null" "is not" "||" "indicator"
  . was inserted before "SYSTEM" to continue.
Closing library CUSTOM...
FRM-30312:Failed to compile the library.

It seems these either lines
 name_in('order.attribute14')='';
or
set_record_property(:system.trigger_record,:system.trigger_block,status,QUERY_STATUS);

cannot be accepted. After I comment out them, it pass

2). I have this error popped after I query a record on the form

FRM-40738:Argument 1 to builtin Name_IN canot not be null

3) I have turned off  order.attribute14 required

This is completly wrong
name_in('order.attribute14')='';

my_variable:=name_in('order.attribute14'); -- here my_variable gets the value of order.attribute14

But in the library (pll) you must use COPY, if you want to set  order.attribute14 to ''
COPY('',name_in('order.attribute14'));

2)
set_record_property(:system.trigger_record,:system.trigger_block,status,QUERY_STATUS);
I have forgotten to write that in pll you must use instead of system variables - these are known in form - some variables from your library or directly
set_record_property('1','my_block',status,QUERY_STATUS);

Sorry, I don't follow. Can you provide a complete code again for option 1 and option 2? Thanks

"1). I have complie error ...
name_in('order.attribute14')='';
or
set_record_property(:system.trigger_record,:system.trigger_block,status,QUERY_STATUS);
cannot be accepted."
=>
set_record_property(:system.trigger_record,:system.trigger_block,status,QUERY_STATUS);
I have forgotten to write that in pll you must use instead of system variables - these are known in form - some variables from your library or directly
set_record_property('1','order',status,QUERY_STATUS);
if the name of block is order. But I think that in the custom.pll there are some variables pointed to triggered block and record.

"After I comment out them, it pass

2). I have this error popped after I query a record on the form

FRM-40738:Argument 1 to builtin Name_IN canot not be null"

This is completly wrong
name_in('order.attribute14')='';
It ought to be like this:
my_variable:=name_in('order.attribute14'); -- here my_variable gets the value of order.attribute14

But in the library (pll) you must use COPY, if you want to set  order.attribute14 to ' '
COPY(' ',name_in('order.attribute14'));

still doesn't work.still cannot copy a null or display a null value to  order.attribute14

Here is my code
....
  my_variable VARCHAR2(100);

...
my_variable:=name_in('order.attribute14');

if (p_tax_status is not null and p_ship_to_location is not null)  then

               copy( p_tax_status, 'order.attribute14' );
               copy( '10'  , 'System.Message_Level' );

          elsif (p_tax_status is null and p_ship_to_location is not null) then

            copy( 'N/A', 'order.attribute14' );            
                  if (name_in('order.attribute14')= 'N/A') then
                  
                  name_in('order.attribute14')='';
                  COPY(my_variable, name_in('order.attribute14'));
                  end if;*/

          end if;
            
            set_record_property('1','order',status,QUERY_STATUS);
            

Hi everyone,

Let's forget the point how to fix copy a null value to a flexfield.I think it's a bug in Oracle form and it's not fixable. I have found a way to get around it. I use N/A to represent null, not use null. It works fine and my team is OK with it.

Now, let's focus on this problem. I still have this message "Do you want to save the changes you have made" Yes, No, Cancel " ? bother me when I close the form.

How to get rid of this message when I close the form

IF name_in('SYSTEM.RECORD_STATUS') = 'CHANGED'
            and event_name = 'WHEN-VALIDATE-RECORD'
            then
                  
                  EXIT_FORM(NO_COMMIT, NO_VALIDATE);
                  
end if;

If I put the following code, it will come another message "close form" yes, no, cancel and the tool message bar will come up

frm-40737:illegal restricted procedure exit_form in when-validate-record trigger

So, how to fix this problem - get rid of message
"Do you want to save the changes you have made" Yes, No, Cancel " ?
or
frm-40737:illegal restricted procedure exit_form in when-validate-record trigger

Still doesn't work

I use your line set_record_property('1','order',status,QUERY_STATUS);

When I open the form and query a record like customer_number, I cannot input the whole number such as 667654, the field only shown one digit in a second, I cannot query this record by query->enter. I don't understand what '1' used for.  You mentioned in your previous mail "these are known in form - some variables from your library or directly.." what doesn't mean?

Then I use the following code,

Set_Record_Property (NAME_IN ('SYSTEM.TRIGGER_RECORD'),
                       NAME_IN ('SYSTEM.TRIGGER_BLOCK'),
                       STATUS,
                       QUERY_STATUS);

It has the same problem as i use '1' line you gave me.

The I use




 IF name_in('SYSTEM.RECORD_STATUS') = 'CHANGED' and
            event_name = 'WHEN-VALIDATE-RECORD' then

                   Set_Record_Property (NAME_IN ('SYSTEM.TRIGGER_RECORD'),
                       NAME_IN ('SYSTEM.TRIGGER_BLOCK'),
                       STATUS,
                       QUERY_STATUS);

               END IF;

It works and the asking save message is gone. But the problem is when I want to change an existing  record and save it, it cannot allow me to do that and come up with the error as attached. When I hit Ok, still cannot save.

What I want is when I don't change anything and just view the data and close the form, no asking save. But if I update an existing record and either go action-> or directly close the form, will ask me if wanting to save the record.

I guess I need to switch around the mode changed->query->new...But I cannot overcome this error message or asking save message popped up even I don't change anything.

 

if (form_name = 'OEXOEMOE' and block_name = 'ORDER') 
 
then 
 
  declare
  
  p_tax_status varchar2(100);
  p_ship_to_site_use_id number;
  p_ship_to_location varchar2(100);
 
  begin  
		
 
   	p_ship_to_site_use_id := name_in('order.ship_to_site_use_id');
	p_ship_to_location := name_in('order.ship_to_location');
        
   	XX_ORDERS_PKG.populate_taxstatus_flexfield(p_ship_to_site_use_id, p_tax_status);
        
 
      	   if (p_tax_status is not null and p_ship_to_location is not null)  then
 
 
		   copy( p_tax_status, 'order.attribute14' );
		   --copy( '10'  , 'System.Message_Level' );
 
	    elsif (p_tax_status is null and p_ship_to_location is not null) then
 
		copy('N/A', 'order.attribute14');	
		--copy( '10'  , 'System.Message_Level' );		
 
	    end if;
 
		
  end;
 
 
		/*Set_Record_Property (NAME_IN ('SYSTEM.TRIGGER_RECORD'), 
                       NAME_IN ('SYSTEM.TRIGGER_BLOCK'), 
                       STATUS, 
                       QUERY_STATUS);*/
	
		
		
		/*IF name_in('SYSTEM.RECORD_STATUS') = 'CHANGED' and 
		event_name = 'WHEN-VALIDATE-RECORD' then
 
 
		       Set_Record_Property (NAME_IN ('SYSTEM.TRIGGER_RECORD'), 
                       NAME_IN ('SYSTEM.TRIGGER_BLOCK'), 
                       STATUS, 
                       QUERY_STATUS);
 
		ELSIF name_in('SYSTEM.RECORD_STATUS') = 'QUERY' and 
		event_name = 'WHEN-NEW-ITEM-INSTANCE' then
 
			Set_Record_Property (NAME_IN ('SYSTEM.TRIGGER_RECORD'), 
                       NAME_IN ('SYSTEM.TRIGGER_BLOCK'), 
                       STATUS, 
                       QUERY_STATUS);
		
  		 END IF; */	
 
 
end if;
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:

so, what is the solution? and how to modify the code?

this code is located in custom.pll.

Yes, you are right. it's coming during WHEN-VALIDATE-RECORD trigger is firing then it is the problem (FRM-40654 error).

Debug my form? this is difficult

The form is not custom form and it's built-in form and how can I debug the form? Do you have other solution to get rid of "Do you want to save the changes you have made" Yes, No, Cancel " ? but at the same time, I can update/change a existing record?

I think that if you change item's value then "Do you want to save ..."  will always come.
So I would put away
Set_Record_Property (NAME_IN ('SYSTEM.TRIGGER_RECORD'),
                       NAME_IN ('SYSTEM.TRIGGER_BLOCK'),
                       STATUS,
                       QUERY_STATUS);
so user can update the existing record.

But, the problem is when user just look at the record and don't make any changes, this message will always there too if I remove the set_record_property.

Yes, it is true. But if you change the item's value then it is changed item.

I ask you
"Where is this code called from ?
IF name_in('SYSTEM.RECORD_STATUS') = 'CHANGED' and
            event_name = 'WHEN-VALIDATE-RECORD' then

                   Set_Record_Property (NAME_IN ('SYSTEM.TRIGGER_RECORD'),
                       NAME_IN ('SYSTEM.TRIGGER_BLOCK'),
                       STATUS,
                       QUERY_STATUS);

               END IF;
"
I know that it is located in custom.pll.

When and where you use your new code ?

The best you can do is calling your code in when-new-form-instance trigger after execute_query or in a post-query trigger.You have to remove
            event_name = 'WHEN-VALIDATE-RECORD'
from the condition.

Ok, after I put the following code, without using this

Set_Record_Property (NAME_IN ('SYSTEM.TRIGGER_RECORD'),
                       NAME_IN ('SYSTEM.TRIGGER_BLOCK'),
                       STATUS,
                       QUERY_STATUS);

everything seems working now.

 if (p_tax_status is not null and p_ship_to_location is not null
            and name_in('SYSTEM.RECORD_STATUS') = 'CHANGED'  
            and event_name = 'WHEN-VALIDATE-RECORD' )  then


               copy( p_tax_status, 'order.attribute14' );
      

          elsif (p_tax_status is null and p_ship_to_location is not null
            and name_in('SYSTEM.RECORD_STATUS') = 'CHANGED'  
            and event_name = 'WHEN-VALIDATE-RECORD' ) then

            copy('N/A', 'order.attribute14');      
            

          end if;

So, I will wait for few days for testing and will close this ticket with points for every one.