Home » Developer & Programmer » Forms » Change Block Status in Custom.pll (Oracle Apps 11.5.10.2)
Change Block Status in Custom.pll [message #446555] Tue, 09 March 2010 05:44 Go to next message
pasam.venkanna
Messages: 11
Registered: October 2009
Location: Hyderabad
Junior Member
Hi Gurus,
I have a requirement to get decrypted value of national_identifier from database and display in Social Security number, when user queries for employee in employee form of HRMS. Also, National_identifier should be in encrypted format in database table always.

To meet above requirement, I am using custom.pll. In WHEN-NEW-BLOCK-INSTANCE I am getting decrypted value of national_identifer from database and assigning it to :person.national_identifier(database block) by using COPY function, so far it is working as expected.

When user query for employee, he/she able to decrypted value. but if user closes the form it is asking 'Do you want to save changes you made' as there is a change in :person.national_identifier.

Do we have any property to reset record/block status to something like unchanged, so we will not get 'Do you want to save changes you made message'.

Thanks in advance,
Venky.
Re: Change Block Status in Custom.pll [message #446562 is a reply to message #446555] Tue, 09 March 2010 05:57 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You could try with
set_record_property(:system.trigger_record, 'block_name', status, query_status);
at the end of the POST-QUERY trigger.
Re: Change Block Status in Custom.pll [message #446564 is a reply to message #446555] Tue, 09 March 2010 06:08 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
I find the safest way to deal with this is to use:
set_item_property(<item_name>, item_is_valid, property_true);


Do that at the end of the post-query trigger for all items modified by the post-query trigger.
This avoids the block and record status getting changed in the first place.
Re: Change Block Status in Custom.pll [message #446740 is a reply to message #446564] Wed, 10 March 2010 04:12 Go to previous messageGo to next message
pasam.venkanna
Messages: 11
Registered: October 2009
Location: Hyderabad
Junior Member
sorry for the delay...
I added code in POST-QUERY event in custom.pll, getting 'Could not reserve record [2 tries]. Keep trying?

Re: Change Block Status in Custom.pll [message #446747 is a reply to message #446555] Wed, 10 March 2010 04:28 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
That means you're trying to lock a record that someone else has locked.
Re: Change Block Status in Custom.pll [message #446751 is a reply to message #446747] Wed, 10 March 2010 04:44 Go to previous messageGo to next message
pasam.venkanna
Messages: 11
Registered: October 2009
Location: Hyderabad
Junior Member
Even I reverted back the code in CUSTOM.pll, getting same error message. Here POST-QUERY event is firing before WHEN-NEW-BLOCK_INSTANCE event in CUSTOM. Actually we are assigning the value to national_identifier in WHEN-NEW-BLOCK_INSTANCE. I think this could be causing error.
Re: Change Block Status in Custom.pll [message #446752 is a reply to message #446555] Wed, 10 March 2010 04:51 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
This error isn't normally an indication that your code is wrong. It just means you're trying to update the same record as someone else.
Re: Change Block Status in Custom.pll [message #446755 is a reply to message #446752] Wed, 10 March 2010 05:01 Go to previous messageGo to next message
pasam.venkanna
Messages: 11
Registered: October 2009
Location: Hyderabad
Junior Member
yes, but when I query for the employee getting this kind of error message. I am able to insert/update data on Employee form.

In PRE-INSERT and PRE_UPDATE events I am encrypting the data before inserting/updating in PER_PEOPLE_F table.

When ever we enable POST-QUERY event in CUSTOM.pll, we are getting this kind of error.
Re: Change Block Status in Custom.pll [message #446757 is a reply to message #446555] Wed, 10 March 2010 05:04 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
OK, we need a lot more information here.
1) What datablocks have you got?
2) Which datablocks does the post-query alter?
3) Which blocks are the users allowed to modify data in?
Re: Change Block Status in Custom.pll [message #446761 is a reply to message #446757] Wed, 10 March 2010 05:19 Go to previous messageGo to next message
pasam.venkanna
Messages: 11
Registered: October 2009
Location: Hyderabad
Junior Member
Please review the attached code.
Re: Change Block Status in Custom.pll [message #446764 is a reply to message #446555] Wed, 10 March 2010 05:24 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
It's far better to post the code in the thread using code tags (see the orafaq forum guide if you're not sure how) like this:
IF form_name='PERWSHRG' THEN
    IF (event_name = 'WHEN-NEW-BLOCK-INSTANCE') THEN
      IF block_name in('PERSON') THEN
           fnd_message.set_string ('Form customization work in progress');
          fnd_message.show;
          param_to_pass1:=name_in('PERSON.PERSON_ID');
          param_to_pass3:=name_in('PERSON.NATIONAL_IDENTIFIER');
          IF param_to_pass3 is not  null and param_to_pass1=2627 then
               SELECT RTRIM(obfuscation_pkg.decrypt(national_identifier))
               INTO param_to_pass4
               FROM per_all_people_f
              WHERE person_id=param_to_pass1
              AND effective_end_date>sysdate;
              
            copy(param_to_pass4,'PERSON.NATIONAL_IDENTIFIER');
          END IF;
       END IF;
    END IF;
    IF (event_name = 'PRE-INSERT') THEN
       IF block_name in('PERSON') THEN
       fnd_message.set_string ('Form customization - Developer work in progress for PRE-INSERT');
       fnd_message.show;
       param_to_pass3:=name_in('PERSON.NATIONAL_IDENTIFIER');
       IF param_to_pass3 is not  null then
         param_to_pass4:=ysi_obfuscation_pkg.encrypt(RPAD(param_to_pass3,24));
         copy(param_to_pass4,'PERSON.NATIONAL_IDENTIFIER');
       END If;
       END IF;
    END IF;
    IF (event_name = 'PRE-UPDATE') THEN
             IF block_name in('PERSON') THEN
              fnd_message.set_string ('Form customization - Developer work in progress for PRE-UPDATE');
              fnd_message.show;
              param_to_pass3:=name_in('PERSON.NATIONAL_IDENTIFIER');
       IF param_to_pass3 is not  null then
           param_to_pass4:=obfuscation_pkg.encrypt(RPAD(param_to_pass3,24));
           copy(param_to_pass4,'PERSON.NATIONAL_IDENTIFIER');
       END IF; 
      END IF;
    END IF;
    /*IF (event_name = 'POST-QUERY') THEN
             IF block_name in('PERSON') THEN
              fnd_message.set_string ('Form customization - Developer work in progress for POST-QUERY');
              fnd_message.show;
              param_to_pass1:=name_in('PERSON.PERSON_ID');
              param_to_pass3:=name_in('PERSON.NATIONAL_IDENTIFIER');
            /*IF param_to_pass3 is not  null and param_to_pass1=2627 then
              SELECT RTRIM(ysi_obfuscation_pkg.decrypt(national_identifier))
               INTO param_to_pass4
               FROM per_all_people_f
              WHERE person_id=param_to_pass1
              AND effective_end_date>sysdate;
           
            copy(param_to_pass4,'PERSON.NATIONAL_IDENTIFIER');
            
             END IF;
             END IF;
          item_id:=find_item('PERSON.NATIONAL_IDENTIFIER');
         set_item_property(item_id, item_is_valid, property_true);
    END IF;*/
    
  END IF;


And you haven't answered any of my questions.
Re: Change Block Status in Custom.pll [message #446772 is a reply to message #446764] Wed, 10 March 2010 06:03 Go to previous messageGo to next message
pasam.venkanna
Messages: 11
Registered: October 2009
Location: Hyderabad
Junior Member
) What datablocks have you got?
PERSON datablock
2) Which datablocks does the post-query alter?
PERSON datablock
3) Which blocks are the users allowed to modify data in?
PERSON datablock
Re: Change Block Status in Custom.pll [message #446779 is a reply to message #446555] Wed, 10 March 2010 07:14 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
Ok then you have two problems.

1) Your changing a database item when you query it - NATIONAL_IDENTIFIER - you're storing it encrypted but displaying it in the form unencrypted, correct?

What you need to do is put the unencrypted form in a non-database item in the datablock. Display that on screen instead of the database item. When users enter a value, encrypt it and store the encrypted version in the database item.

2) Someone else has the persons record you are querying locked. Whenever you change a database item of a record queried from the database oracle tries to lock the record (if it hasn't done so already). If some already has it locked you will get the error message you are getting. Even if you stop all your code from modifying the database items you will still get that error when you try to overtype any of the items on the screen, until whoever has it locked commits their changes.
Re: Change Block Status in Custom.pll [message #446783 is a reply to message #446779] Wed, 10 March 2010 07:29 Go to previous messageGo to next message
pasam.venkanna
Messages: 11
Registered: October 2009
Location: Hyderabad
Junior Member
Thank you for the information, but our client is expecting to meet this requirement without touching/customizing form, we advised CUSTOM.pll. We are able to meet their requirement, except it is asking user to save changes as we are populating value into database item on a block.
Re: Change Block Status in Custom.pll [message #446787 is a reply to message #446555] Wed, 10 March 2010 07:50 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
Your client needs to change their expectations.
Changing a database item on query is a very bad idea.
It'll lock the row as soon as anyone queries it. Which'll prevent multiple users from querying the same record at the same time.
It'll also always try to update even if the users haven't changed anything, because the code has changed something. Hence the "do you want to save changes message".
You can not bypass this unless you:
a) use a non-database item to display/modify the unencrypted version of the data.
b) stop storing it encrypted.

The only other way around the problem is to base the block on a procedure and do the encryption/decryption in that. But that's an even bigger change.
Re: Change Block Status in Custom.pll [message #447551 is a reply to message #446787] Tue, 16 March 2010 00:26 Go to previous message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Is 'encryption' a property that can be turned 'off'?

David
Previous Topic: DataGrid View in Forms 6i
Next Topic: Visible Form
Goto Forum:
  


Current Time: Sat Dec 03 19:53:11 CST 2016

Total time taken to generate the page: 0.11415 seconds