LOV Change - R12 Forms Personalization

One of my buddy pinged me and asked me if we can change / modify the LOV query in R12-Forms using personalization.

At a first glance, I felt may be I cannot.
Later I did a Google and found couple of posts indicating few possibilities with certain limitations (that they are for future release).

I thought as we were on 12.0.6(RUP6) , lets make a try.

For a quick overview of Personalization in forms go through this Metalink Document.

Summary:

• Step1 we will create a record group from query using a built in
• Step2 Apply this record group to the existing LOV.

Limitations:

We cannot use the LOV for WHEN-NEW-ITEM-INSTANCE trigger of that LOV item.
The new record group query structure should actually mimic the existing record group query structure, say column names etc.

Use Case:

Users Definition form: Restrict the suppliers LOV to show only one supplier.

Before personalization when i clicked the LOV for suppliers, i could see a long list of suppliers.



Start Personalization.
Open the form which requires personalization in the application via.
Help > Diagnostics > Custom code > Personalize

A new personalization form opens


Enter Sequence, Description, Level, Enabled Flag.
Select the condition as : When-New-Form-Instance
Condition : Not applicable here
Context Level : Site



Define Actions.
Two Step proces.
a. Define Record Group using the built in-Create Group from Query
b. Assign Record Group to the LOV.

a) Define Sequence 1 for a record group,
Type : Built in
Built in Type : Create Record group from Query
Group Name : XX_(STD GROUP NAME)
here the std group name was SUPPLIER_GROUP hence i used XX_SUPPLIER_GROUP
Argument : Here comes our Query, I want to show only one Supplier in the LOV.

select full_name, supplier_name, supplier_location, supplier_contact_id from icx_supplier_contact_lov where supplier_name = 'COMPUCOM SYSTEMS INC'
order by full_name, supplier_name, supplier_location



b) Define Sequence 2, Assign new Record group to the existing LOV name. Type : Property Object Type : LOV Target Object: SUPPLIER_LOV
( I got this info by actually opening the form in form builder)
Also we can click the list item to see the available lov's
Property_Name: GROUP_NAME
Value : Previous step record group name i.e. XX_SUPPLIER_GROUP

Validate them and Apply.

Test your LOV from the actual form.
When i click on the supplier lov now i see only supplier i.e. COMPUCOM SYSTEMS.

Hope you enjoyed this.

Thanks
Siva.

Kill Concurrent Requests ?

I have noticed today that there are lot of pending concurrent requests to our concurrent manager and is causing delay for my actual program to run.

Out of which Check Event Alert was having around 487923 approx 488K requests.

This is a Standard Program for Alerts to work properly. We cannot disable this program.

I requested my DBA to cancel them and he said he cannot do it from back end. Instead asking me to cancel from front end, 488K requests from front end ???? How many days I might take !!!!!
I decide to go on a wild way i.e. from back end. (I guess may be many people do it as regular means ).

I thought of updating the FND_CONCURRENT_REQUESTS phase_code to 'C'(Complete) and Status_code to 'D' (Canceled).
But a small catch is there here. If there is any DB session associated with this concurrent program and subsequent OS process then we need to kill both of them.

Interestingly I found that for Pending requests there are no DB session or OS process assigned. This can be seen from ORACLE_PROCESS_ID, ORACLE_SESSION_ID & OS_PROCESS_ID from FND_CONCURRENT_REQUESTS.
These columns will be only populated for requests which are in Running Phase.

ORACLE_SESSION_ID corresponds to AUDSID of V$SID and hence if you want to kill a running program from back end. kill the DB Session once you know the SID and Serial from V$session.

Summary : To kill a running requests, we need to do two tasks, update the concurrent request table & kill the db session associated.
Get Oracle_session_id from Fnd_Concurrent_Requests
Query V$session with audsid=oracle_Session_id for SID and SERIAL#
Issue Kill Statement SQL> Alter System kill Session 'SID,SERIAL#';
Update the FND_CONCURRENT_REQUESTS Table of that request id with the phase_code and status code as 'C','D' respectively.

Use this query

SELECT 'ALTER SYSTEM KILL SESSION '''||Ses.SID||','||Ses.serial#||''';'
FROM v$session ses, fnd_Concurrent_requests fcr
WHERE ses.audsid=fcr.oracle_session_id
AND request_id=any(101027686,101024278);

Have a nice day !!!