Introduction:
Through Custom.pll, an existing LOV can be attached to a
seeded text item in Oracle Apps but its column mapping properties
cannot be altered through code, so the returning item is always the original
item that is mapped to the LOV at design time and hence cannot return value to
the required field. In order to find a work around for the above scenario,
following is a different approach:
Implementation
Steps:
STEP 1: First off all identify a Text Item with attached
LOV from the seeded form that is not being used in the current application. Let
them be ‘TEXT1’ and ‘LOV1’ respectively.
STEP 2: Now find the Record Group of the LOV ‘LOV1’. Let it
be ‘RG1’.
STEP 3: Check the data types of the DB columns used in the
query in the record group ‘RG1’.
STEP 4: Build the required query with the same number and
types of columns as in the record group query of ‘RG1’.
STEP 5: In Custom.pll, in the zoom function, write the
functions for altering the ‘LOV1’ properties. Two functions are used for that:
a) SET_LOV_PROPERTY -> Used to change the title, size and
display position of the LOV.
b) SET_LOV_COLUMN_PROPERTY -> Used to change the title and
width of the LOV columns.
STEP 6: Declare the required query framed in STEP 4 in the
‘event’ procedure in Custom.pll. Let the query be ‘Q1’.
STEP 7: Attach the query ‘Q1’ to the Record Group ‘RG1’
using the following function:
Î POPULATE_GROUP_WITH_QUERY.
STEP 8: Let the required text item to which the LOV value
is to be returned is ‘TEXT2’. In the ‘NEW-ITEM-INSTANCE’ event of TEXT2 write
the code for following steps:
a) Displaying the LOV ‘LOV1’ using the SHOW_LOV function.
b) If the SHOW_LOV function returned true, then the value is
selected from the LOV. In that case write code for copying the contents of
TEXT1 to TEXT2. ‘COPY’ function can be used for that.
c) Write code for navigating to the next item using the
GO_ITEM function.
Example:
The above approach can be emphasized better by citing an
example .As i have implemented the above scenario in Oracle Teleservices Module
in ‘Create Service Request’ form (CSXRISR), so I will use the same example.
The requirement was to attach an LOV to the existing Text
Item
‘SR_CREATION_CHANNEL_MEANING’ which is defined as a Display
only text item. The LOV was to contain values for the different channels of SR creation
(Phone, SMS, and Email etc.).
For this LOV, a lookup is defined as ‘SR_CREATION_CHANNEL’.
Another text item on the same form
‘COMMUNICATION_PREFERENCE’ is not being used in our implementation.
Since the LOV ‘COMMUNICATION_PREFERENCE’ is attached to the above text item, it
can be used for our purpose. Next we take the query of the record group
‘COMMUNICATION_PREFERENCE’ and form the required query returning the same types
and numbers of columns as in the record group query. Now we write the codes for
implementing the above scenario in the Custom.pll as follows:
1) In the ‘zoom’ function write the below lines of code for
changing the title of the LOV and one of its column:
•
set_lov_property('COMMUNICATION_PREFERENCE',TITLE,'SR
Creation Channel');
•
set_lov_column_property('COMMUNICATION_PREFERENCE',
2
,TITLE , 'Channel');
2) In Declare section
of ‘event’ procedure we declare the
query and variables as follows :
lvc_query VARCHAR2(2000) :=
‘select
lookup_code,meaning,description
from fnd_lookup_values a
where a.lookup_type ='SR_CREATION_CHANNEL’
AND
a.enabled_flag = 'Y'
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(a.start_date_active,
sysdate))
AND
TRUNC(NVL(a.end_date_active, sysdate)) ORDER BY meaning’;
dummy BOOLEAN;
lc_comm_pref VARCHAR2(100);
3) In the Begin block of the same
procedure we write the following lines of code :
IF
event_name='WHEN-NEW-ITEM-INSTANCE'
AND
name_in('system.trigger_item')='INCIDENT_TRACKING.SR_CREATION_C
HANNEL_MEANING' THEN
dummy:= show_lov('COMMUNICATION_PREFERENCE',6,2);
IF
dummy = TRUE THEN
lc_comm_pref :=
name_in('INCIDENT_TRACKING.COMMUNICATION_PREFERENCE'
);
IF lc_comm_pref IS NOT NULL
OR lc_comm_pref <>
name_in('INCIDENT_TRACKING.SR_CREATION_CHANNEL_MEANI
NG') THEN copy(lc_comm_pref,'INCIDENT_TRACKING.SR_CREATION_C
HANNEL_MEANING');
END IF;
END
IF;
GO_ITEM('INCIDENT_TRACKING.PUBLISH_FLAG');
END IF;
The above lines of code will cause the LOV to be displayed
on single clicking the ‘SR_CREATION_CHANNEL_MEANING’ field. On the selection of
a value from the LOV, the selected value is populated in the same field and
hence it functions like an LOV.
