Selecting multiple values in value set in Custom Form

Selecting multiple values in value set in Custom Form

While developing a custom form there may be need for selecting multiple values in List of Values which is not possible with the standard list of values.

This blog post details the steps involved in selecting multiple values in a value set in custom forms? It is not possible with the standard list of values to select multiple values. You can do this by creating your own LOV in forms.

Steps Involved:

Creation of Block

Create a non database block set the properties as:

Database Data Block -- No

Subclass Information -- Block 

Items in Block

Create two list Items named as list1, list2 set the properties as follows

Two list items list1 and list2

List1 List Style = TList

Visible = Yes

Elements in List => put one item with space

List2 List Style = TList

Visible = YES

Elements in List => put one item with space

2 Buttons ok and cancel

2 parameters for x-Position and y-position Record group

Attaching to canvas

Create a Canvas and attach all the Items defined in the block to canvas.

Select all the items by holding shift button open the property inspector and select the canvas name which you have created so that all items will be attached to canvas.

Attaching Canvas to Window

Create a Window and set the properties as

Subclass Information -- Window

Primary Canvas – Canvas name you have created

Once canvas is attached to window now go to canvas property inspector and check

Whether the same window is attached to canvas else attach your created Window.

Now Open your canvas and Rearrange your items and give proper labels for items.

Triggers Involved

• When new block instance

When list changed

• When-Button-Pressed

• Key-listval

When new block instance:

DECLARE

Error_Flag number;

v_record_group_id RECORDGROUP;

v_query varchar2(5000);

BEGIN

/*Deleting data in list items*/

Clear_List('list1');

Clear_List('list2');

/* Calling the Record Group which we have created in form*/

Error_Flag := POPULATE_GROUP('TPL_LOV_REC_RG');

IF Error_Flag = 0 THEN

v_record_group_id := FIND_GROUP ('TPL_LOV_REC_RG');

/* Query for populating values dynamically*/

v_query:=

’SELECT receipt_num, receipt_num

FROM rcv_shipment_headers

WHERE shipment_header_id IN (SELECT shipment_header_id

FROM rcv_shipment_lines

WHERE po_header_id

=:xxtpl_swp_hdr_b.po_header_id)

AND shipment_header_id NOT IN (SELECT shipment_header_id

FROM xxtpl_payinv_b

WHERE main_header_id = :xxtpl_swp_hdr_b.main_header_id

AND shipment_header_id IS NOT NULL)';

Error_Flag := populate_group_with_query(v_record_group_id,v_query);

/*Assigning values dynamically to list1*/

POPULATE_LIST('list1',v_record_group_id);

ELSE

FND_MESSAGE.SET_STRING('Error while populating list group');

FND_MESSAGE.SHOW;

RAISE FORM_TRIGGER_FAILURE;

END IF;

END;

When list changed of List1:

DECLARE

total_list_count NUMBER;

i NUMBER;

double NUMBER;

BEGIN

double := 0;

i := 1;

total_list_count := Get_List_Element_Count('XXTPL_LOV_B.list2') + 1;

WHILE i < total_list_count LOOP

/* Deletion of Element by Multi Selection in selected list*/

IF :list1 = Get_List_Element_value('XXTPL_LOV_B.list2',i) THEN

double := 1;

delete_list_element('XXTPL_LOV_B.list2',i);

END IF;

EXIT when double = 1;

i := i + 1;

END LOOP;

IF double = 0 THEN

total_list_count := Get_List_Element_Count('XXTPL_LOV_B.list2');

total_list_count := total_list_count + 1;

/* adding the selected value to list2,so that list2 contains all selected values*/

ADD_LIST_ELEMENT('XXTPL_LOV_B.list2',total_list_count,:list1,:list1);

END IF;

END;

When Button Pressed: OK Button

DECLARE

total_list_count NUMBER;

i NUMBER;

my_elements VARCHAR2(250);

header_id NUMBER;

ln_hdr VARCHAR2(2000);

receipt_no VARCHAR2(30);

BEGIN

i := 1;

total_list_count := Get_List_Element_Count('XXTPL_LOV_B.list2') + 1;

/* Retrieving all elements that are selected and concatinating to Global variable*/

WHILE i < total_list_count LOOP

my_elements := my_elements ||

','||Get_List_Element_value('XXTPL_LOV_B.list2',i);

i := i + 1;

END LOOP;

/* populating the concatenated string into field where you have to show multiple

values*/

:XXTPL_QUERY.Q_RECEIPT_NUM := substr(my_elements,2);

/*Making the window invisible when selected values gets populated*/

SET_WINDOW_PROPERTY('TPL_LOV',VISIBLE,PROPERTY_FALSE);

/* Navigation to called Window*/

GO_ITEM('XXTPL_QUERY.Q_RECEIPT_NUM');

END; 

When Button Pressed : Cancel Button:

/*Making the window invisible when selected values gets populated*/

SET_WINDOW_PROPERTY('TPL_LOV',VISIBLE,PROPERTY_FALSE);

/* Navigation to called Window*/

GO_ITEM('XXTPL_QUERY.Q_RECEIPT_NUM');

Key-Listval for the Item Where Multi values Have To Be Selected

DECLARE

pl_id PARAMLIST;

pl_name VARCHAR2(10) := 'tempdata';

x_position NUMBER;

y_position NUMBER;

BEGIN

/* Get X,Y Position of item for positioning Window Multiple values selction*/

x_position := GET_ITEM_PROPERTY(:SYSTEM.CURSOR_ITEM,X_POS);

y_position := GET_ITEM_PROPERTY(:SYSTEM.CURSOR_ITEM,Y_POS);

SET_WINDOW_PROPERTY('TPL_LOV',VISIBLE,PROPERTY_TRUE);

SET_WINDOW_PROPERTY('TPL_LOV',X_POS,X_POSITION);

SET_WINDOW_PROPERTY('TPL_LOV',Y_POS,Y_POSITION+0.5);

/* Called Window*/

GO_BLOCK('XXTPL_LOV_B');

END;