| Change Block Status in Custom.pll [message #446555] |
Tue, 09 March 2010 05:44  |
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 #446564 is a reply to message #446555] |
Tue, 09 March 2010 06:08  |
cookiemonster
Messages: 13963
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 #446751 is a reply to message #446747] |
Wed, 10 March 2010 04:44 |
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 #446755 is a reply to message #446752] |
Wed, 10 March 2010 05:01 |
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 #446764 is a reply to message #446555] |
Wed, 10 March 2010 05:24 |
cookiemonster
Messages: 13963
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 #446779 is a reply to message #446555] |
Wed, 10 March 2010 07:14 |
cookiemonster
Messages: 13963
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 |
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 |
cookiemonster
Messages: 13963
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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
