Collected From: http://sheikyerbouti.developpez.com/dynrep/dynrep.htm
1. Purpose
This is a tutorial and an Oracle
Forms tool that show how, in a single dialog, to launch any report with 0 up to
10 parameters.
It needs 2 Oracle tables to store
definition of the reports and their associated parameters.
Each
parameter support the following properties:
q
Required
q
Datatype
q
Hint text
q
Default value
q
Format mask
q
LOV select
order
q
Validation
rule
Two
dialogs are provided to run the demonstration
q
DYN_REP.FMB
to launch the reports
q
DYN_REP_BUILD.FMB
to manage report and parameters definition
2. The Oracle tables
The table that store the reports properties
CREATE TABLE REP
(
REP_NUM NUMBER NULL,
REP_NAME VARCHAR2(100 BYTE)
NOT NULL,
REP_TITLE VARCHAR2(256 BYTE)
NOT NULL,
REP_SERVER VARCHAR2(100 BYTE) NULL,
REP_DESTYPE VARCHAR2(20 BYTE) DEFAULT 'CACHE' NOT
NULL,
REP_DESNAME VARCHAR2(256 BYTE)
NULL,
REP_DESFORMAT VARCHAR2(20 BYTE) DEFAULT 'PDF' NOT
NULL,
REP_EXEC_MODE VARCHAR2(30 BYTE) DEFAULT 'ASYNCHRONOUS' NOT
NULL,
REP_COMM_MODE VARCHAR2(15 BYTE) DEFAULT 'BATCH' NOT
NULL
)
The table that store the report parameters properties
CREATE TABLE REP_PARAM
(
REP_NUM NUMBER
NULL,
REP_PAR_REP_NAME VARCHAR2(30 BYTE) NULL,
REP_PAR_ORDER NUMBER(2)
DEFAULT 1 NULL,
REP_PAR_TITLE VARCHAR2(256 BYTE)
NOT NULL,
REP_PAR_TYPE VARCHAR2(20 BYTE)
DEFAULT 'CHAR' NOT NULL,
REP_PAR_LOV VARCHAR2(2000 BYTE)
NULL,
REP_PAR_VALIDATION VARCHAR2(512 BYTE) NULL,
REP_PAR_DEFAULT VARCHAR2(256 BYTE) NULL,
REP_PAR_HINT VARCHAR2(128 BYTE)
NULL,
REP_PAR_ERRMSG VARCHAR2(256 BYTE)
NULL,
REP_PAR_CASE VARCHAR2(1 BYTE)
DEFAULT 'M' NOT NULL,
REP_PAR_FORMAT_MASK VARCHAR2(50 BYTE) NULL,
REP_PAR_REQUIRED VARCHAR2(1 BYTE) DEFAULT
'N' NOT NULL
)
3. The launcher dialog
The list item (Report title)
allows to choose the report to launch and display dynamically
the associated parameters.
The form contains 10 CHAR
items, 10 NUMBER items and 5 DATE items.
When you choose a specific
report, the table that contain the parameter description is read,
and the corresponding items
are showed on the screen.
This operation is performed
in the INIT_PARAMS program unit called by the When-List-Changed trigger:
PROCEDURE Init_Params IS
LC$Req
Varchar2(512) ;
Cursor
C_PARAMS IS
Select *
From REP_PARAM
Where REP_NUM = :REP.REP_LIST
Order by REP_PAR_ORDER ;
LR$Rec C_PARAMS%ROWTYPE ;
LN$I Pls_Integer := 1 ;
BEGIN
-- Hide all parameters --
For i IN
1..10 Loop
Set_Item_Property( 'PARAMS.L' || Ltrim(
To_char( i,'00' ) ), VISIBLE, PROPERTY_FALSE ) ;
Set_Item_Property( 'PARAMS.C' || Ltrim(
To_char( i,'00' ) ), VISIBLE, PROPERTY_FALSE ) ;
Set_Item_Property( 'PARAMS.N' || Ltrim(
To_char( i,'00' ) ), VISIBLE, PROPERTY_FALSE ) ;
End loop ;
For i IN 1..5
Loop
Set_Item_Property( 'PARAMS.D' || Ltrim(
To_char( i,'00' ) ), VISIBLE, PROPERTY_FALSE ) ;
End loop ;
If
:REP.REP_LIST IS NOT NULL Then
-- Populate the
internal collection with parameter properties --
PKG_VARS.TB_Params.DELETE
;
Open C_PARAMS ;
Loop
Fetch C_PARAMS Into LR$Rec ;
Exit When C_PARAMS%NOTFOUND ;
PKG_VARS.TB_Params(LN$I) := LR$Rec ;
LN$I := LN$I + 1 ;
End loop ;
Close C_PARAMS ;
If PKG_VARS.TB_Params.COUNT > 0 Then
-- Show corresponding items --
Display_Params ;
End if ;
End if ;
END;
PROCEDURE Display_Params IS
LN$PosY Pls_integer ;
LC$Item Varchar2(61) ;
LC$First Varchar2(61) ;
LC$Prec Varchar2(61) ;
LN$I Pls_Integer ;
LN$C Pls_Integer := 1 ;
LN$N Pls_Integer := 1 ;
LN$D Pls_Integer := 1 ;
LN$Height
Pls_Integer := 0 ;
BEGIN
LN$PosY := Get_Item_Property(
'PARAMS.L01', Y_POS ) ;
-- Show parameters --
LN$I := 1 ;
For i IN
PKG_VARS.TB_Params.First .. PKG_VARS.TB_Params.Last Loop
LC$Item := 'L' || ltrim(to_char(LN$I,'00')) ;
Copy( PKG_VARS.TB_Params(i).REP_PAR_TITLE, LC$Item ) ;
Set_Item_Property( LC$Item , VISIBLE, PROPERTY_TRUE ) ;
Set_Item_Property( LC$Item, Y_POS, LN$PosY ) ;
If PKG_VARS.TB_Params(i).REP_PAR_TYPE = 'CHAR' Then
LC$Item := 'C' || ltrim(to_char( LN$C, '00' )
) ;
LN$C
:= LN$C + 1 ;
ElsIf PKG_VARS.TB_Params(i).REP_PAR_TYPE = 'NUMBER' Then
LC$Item := 'N' || ltrim(to_char( LN$N, '00' )
) ;
LN$N
:= LN$N + 1 ;
Else
LC$Item := 'D' || ltrim(to_char( LN$D, '00' )
) ;
LN$D
:= LN$D + 1 ;
End if ;
PKG_VARS.TB_Items(i) := LC$Item ;
If LN$I = 1 Then
LC$First := 'PARAMS.' || LC$Item ;
End if ;
Set_Item_Property( LC$Item, VISIBLE, PROPERTY_TRUE ) ;
Set_Item_Property( LC$Item, ENABLED, PROPERTY_TRUE ) ;
Set_Item_Property( LC$Item, INSERT_ALLOWED, PROPERTY_TRUE ) ;
Set_Item_Property( LC$Item, UPDATE_ALLOWED, PROPERTY_TRUE ) ;
Set_Item_Property( LC$Item, Y_POS, LN$PosY ) ;
-- Default value --
If PKG_VARS.TB_Params(i).REP_PAR_DEFAULT IS NOT NULL Then
Copy( PKG_VARS.TB_Params(i).REP_PAR_DEFAULT,
LC$Item ) ;
End if ;
-- Parameter name --
Set_Item_Property( LC$Item, TOOLTIP_TEXT,
PKG_VARS.TB_Params(i).REP_PAR_REP_NAME ) ;
-- Hint text --
Set_Item_Property( LC$Item, HINT_TEXT,
PKG_VARS.TB_Params(i).REP_PAR_HINT ) ;
-- Format mask --
Set_Item_Property( LC$Item, FORMAT_MASK,
PKG_VARS.TB_Params(i).REP_PAR_FORMAT_MASK ) ;
-- Case restriction --
If PKG_VARS.TB_Params(i).REP_PAR_CASE = 'U' Then
Set_Item_Property( LC$Item, CASE_RESTRICTION,
UPPERCASE ) ;
ElsIf PKG_VARS.TB_Params(i).REP_PAR_CASE = 'L' Then
Set_Item_Property( LC$Item, CASE_RESTRICTION,
LOWERCASE ) ;
End if ;
-- Required ? --
If PKG_VARS.TB_Params(i).REP_PAR_REQUIRED = 'Y' Then
Set_Item_Property( LC$Item, REQUIRED,
PROPERTY_TRUE ) ;
End if ;
If LN$I > 1 Then
Set_Item_Property( LC$Prec,
NEXT_NAVIGATION_ITEM, LC$Item ) ;
Set_Item_Property( LC$Item,
PREVIOUS_NAVIGATION_ITEM, LC$Prec ) ;
End if ;
LC$Prec := LC$Item ;
LN$I := LN$I + 1 ;
LN$PosY := LN$PosY + Get_Item_Property( LC$Item, HEIGHT ) ;
End loop ;
If LN$I >
1 Then
Set_Item_Property( LC$Item,
NEXT_NAVIGATION_ITEM, LC$First ) ;
Set_Item_Property( LC$First, PREVIOUS_NAVIGATION_ITEM, LC$Item )
;
End if ;
If LN$I >
1 Then
Go_Item( LC$First ) ;
Else
Go_Block( 'REP' ) ;
End if ;
END;
If a LOV select order is setted on a parameter, you
can display the associated LOV
There are 5 LOVs defined in the module (for 1, 2, ...,
5 columns)
The value returned is allways the first column of the
LOV, and the target item is :CTRL.RECEPT
This is the code that constructs the LOV, called in
the When-New-Item-Instance trigger:
Display_Lov(
:REP.REP_LIST, Get_Block_Property( 'PARAMS', CURRENT_RECORD )) ;
PROCEDURE
Display_Lov
(
PN$Num
IN NUMBER,
PN$Col
IN NUMBER
) IS
LC$Select
Varchar2(2000) ;
LC$Title
Varchar2(256) ;
rg_name
Varchar2(20) := 'RG_GROUP' ;
rg_id
RecordGroup ;
err
Number ;
c NUMBER;
d NUMBER;
col_cnt INTEGER;
rec_tab dbms_sql.desc_tab2;
col_num NUMBER;
LC$Lov
Varchar2(10) ;
LC$Col Varchar2(100) ;
LC$Item
Varchar2(61) := :System.Trigger_Item ;
LN$Width Pls_Integer ;
LN$TotWidth Pls_integer := 0 ;
BEGIN
LC$Col := Get_Item_Property( LC$Item,
TOOLTIP_TEXT ) ;
-- Get The Select order --
Begin
Select
REP_PAR_LOV, REP_PAR_TITLE
Into
LC$Select, LC$Title
From
REP_PARAM
Where
REP_NUM = PN$Num
And
REP_PAR_REP_NAME = LC$Col
;
Exception
When
NO_DATA_FOUND Then
goto the_end ;
End ;
If LC$Select IS NULL Then
goto the_end ;
End if ;
BEGIN
c := dbms_sql.open_cursor;
dbms_sql.parse(c, LC$Select, 1);
d := dbms_sql.EXECUTE(c);
EXCEPTION
WHEN
OTHERS THEN
dbms_sql.close_cursor(c);
Raise form_trigger_failure ;
END ;
dbms_sql.describe_columns2(c, col_cnt,
rec_tab);
dbms_sql.close_cursor(c);
col_num := rec_tab.last ;
-- LOV name --
LC$Lov := 'LV' || ltrim( to_char( col_num )
) || 'C' ;
rg_id := Find_Group( rg_name ) ;
If not ID_NULL( rg_id ) Then
Delete_Group( rg_id ) ;
End if ;
-- Create and
populate the record group --
rg_id := Create_Group_From_Query( rg_name,
LC$Select ) ;
err := Populate_Group( rg_name ) ;
-- Set the LOV
column properties --
For i In rec_tab.first .. rec_tab.last Loop
-- Title --
Set_Lov_Column_Property( LC$Lov, i, TITLE,
rec_tab(i).col_name ) ;
-- Width --
IF rec_tab(i).col_type = 1 THEN
LN$Width := rec_tab(i).col_max_len * 11 ;
ELSIF rec_tab(i).col_type = 2 THEN
LN$Width := rec_tab(i).col_precision * 11 ;
ELSIF rec_tab(i).col_type = 12 THEN
LN$Width := 80 ;
END IF ;
If LN$Width > 200 Then
LN$Width := 200 ;
End if ;
Set_Lov_Column_Property( LC$Lov, i, WIDTH, LN$Width ) ;
LN$TotWidth := LN$TotWidth + LN$Width + 20 ;
End loop ;
-- LOV properties
--
Set_Lov_Property( LC$Lov, LOV_SIZE,
LN$TotWidth, 400 ) ;
Set_Lov_Property( LC$Lov, TITLE, LC$Title )
;
Set_Lov_Property( LC$Lov, GROUP_NAME,
rg_name ) ;
Set_Item_Property( LC$Item, LOV_NAME, LC$Lov
) ;
<<the_end>>
null
;
END;
4. The builder dialog
It allows to set the reports
and parameters properties.
q
Enter in the first block the basic report specifications
q
Enter in the second block the parameters specifications
Name is the name of
the parameter defined in the report module
Req allows to set the
parameter required or not
Title is the parameter
label
Datatype can be CHAR,
NUMBER or DATE.
Case allows to set the
restriction case of the parameter.
Allowed values are:
q
(U)ppercase
q
(L)owercase
q
(M)ixt
LOV order allows to define
the SELECT order for the lov attached on the parameter.
You can define SELECT order
with 1 up to 5 columns.
Each column must have an
alias (COL1 to COL5)
Validation rule allows to set a valid
SQL to validate the parameter.
Error message allows to set an
error message displayed to the end user when the validation failed.
You can also define a format
mask for the parameter.
5. The sample dialogs
Ø Unzip the dynrep.zip
file
Ø Under Sql*Plus, run
the /scripts/install.sql to create the following tables:
1.
EMP
2.
DEPT
3.
REP
4.
REP_PARAM
Ø Open the /reports/EMP_LIST.rdf
and /reports/DEPT_LIST.rdf modules (Oracle Reports 9.0.2)
Ø Open the /forms/DYN_REP.FMB
and /forms/DYN_REP_BUILD.FMB modules (Oracle Forms 9.0.2)
Ø Compile all and
run the modules
0 comments:
Post a Comment