The purpose is to manage dynamic LOVs, able to build any LOV of 1 to 9 columns.
The record group is dynamically populated by the SELECT order provided, then the LOV is tuned to fit and display the corresponding columns.
It uses a pre-defined LOV that can
handle 9 columns.
The data selected from the LOV are copied into 9 hidden items stored in a control block (:LOV).
The copy process is done in an Item-level KEY-LISTVAL trigger:
LB$Ok Boolean ;
LC$Lov Varchar2(30) := Get_Item_Property(:system.trigger_item, LOV_NAME) ;
If LC$Lov Is not null Then
LB$Ok := Show_Lov(LC$Lov);
If LB$Ok Then
-- get returned values --
:BL.TXT1 := :LOV.RETURN1 ;
End if ;
End if ;
The LOV populating process is done in the POPULATE_LOV() procedure, stored in the program unit of the sample dialog provided,
But you can, of course, copy it in a PL/SQL library.
(In fact you need to keep together: PKG_COLLECT package, Build_Select_line function and Populate_LOV procedure)
How to use the dynamic LOV
You can manage 5 different dynamic LOVs in the provided sample dialog.
To populate the LOV, and eventually attach it to an item, use the Populate_LOV() procedure
PC$SQL_Order IN VARCHAR2,
PC$NumLOV IN VARCHAR2,
PC$Title IN VARCHAR2,
PC$TargetItem IN VARCHAR2 default null,
PB$Display IN BOOLEAN Default false
PC$SQL_Order is the corresponding SQL query
PC$NumLOV is the number of LOV (1-5)
PC$Title is the title for the LOV
PC$TargetItem is the item you want to attach the LOV (bloc_name.item_name)
PB$Display is provided in debug mode to ensure the LOV will display the correct values
'Select empno, ename, sal from emp',
'Choose an employee',
If you want to provide aliases, they must be given within double-quotes:
Select empno "Number", ename "Name", sal "Salary" from emp
If you need to incorporate a function that contains a comma, To_Char(), for instance, replace the comma by a ;
select empno, ename, sal, TO_CHAR(hiredate;'DD/MM/YYYY') "HIREDATE" from emp
the ; will be replace by its normal comma at execution time.
· Download the dynamic_lov.fmb sample dialog for you to test (Forms 9.0.2)
To implement it into you own Forms, you can simply drag the DYN_LOV object group into your module.