PACKAGE BODY init_flexfield IS
----------------------------------------------------------------
----------------------------------------------------------------
Procedure combine_segments is
begin
--- clear any old combination
:combined_value:='';
:combined_desc:='';
-- loop the segments to catch the values and concat between each value with the flex field separator
for i in 1..init_flexfield.flex_segment_number loop
:combined_value:=:combined_valueinit_flexfield.flex_separatorname_in('segment_'i);
:combined_desc:=:combined_descinit_flexfield.flex_separatorname_in('desc_'i);
end loop;
--- omit extra separator
:combined_value:=substr(:combined_value,2);
:combined_desc:=substr(:combined_desc,2);
end ;
---------------------------------------------------------------
procedure ensure_right_lov is
v_cursor_segment varchar2(50);
v_segment_number number(2);
begin
--- this code get the current item and get the value of the value set id assigned to this segment and pass this value to the
---- parameter init_valu_set that is used in calling the list of values accross all segments
v_cursor_segment:=:system.trigger_item;
v_segment_number:=to_number(substr(v_cursor_segment,-1));
:parameter.INIT_VALUE_SET:=name_in('value_set_'v_segment_number);
end ;
--------------------------------------------------------------------------
Procedure validate_segment_value is
v_cursor_segment varchar2(50);
v_segment_number number(2);
v_valid number(2);
v_segment_desc varchar2(50);
begin
INIT_FLEXFIELD.ensure_right_lov ;
v_cursor_segment:=:system.trigger_item;
v_segment_number:=to_number(substr(v_cursor_segment,-1));
--- check if this value existed or not using the database package flexfield_pkg
v_valid:=flexfield_pkg.validate_sement_value(name_in(v_cursor_segment),name_in('value_set_'v_segment_number)) ;
if v_valid = 0 then
message('Invalid Value for the segment');
message('Invalid Value for the segment');
raise form_trigger_failure;
else
--- get the right desc for semgnet
v_segment_desc:=value_set_pkg.get_value_set_desc(:parameter.INIT_VALUE_SET,
value_set_pkg.get_detail_Recid(name_in('value_set_'v_segment_number),name_in('segment_'v_segment_number)
),translation_pkg.get_user_lang_id);
copy(v_segment_desc,'desc_'v_segment_number);
end if ;
end;
--------------------------------------------------------------------------
procedure invoke_lov is
v_cursor_segment varchar2(50);
v_segment_number number(2);
begin
v_cursor_segment:=:system.trigger_item;
v_segment_number:=to_number(substr(v_cursor_segment,-1));
:parameter.INIT_VALUE_SET:=name_in('value_set_'v_segment_number);
if show_lov('LOV_VALUE') then
copy(:lov_value,name_in('system.trigger_item'));
--copy(:lov_desc,'desc_'v_segment_number);
else
message('you must choose value from list');message('');
raise form_trigger_failure ;
end if ;
end ;
---------------------------------------------------------------------------------------------------------------
procedure invoke_flex_field(target_item varchar2, target_item_desc varchar2) is
cursor c_cur is select GN_FLEXFIELD_DET.flexfield_det_recid,value_set_recid from GN_FLEXFIELD_DET where GN_FLEXFIELD_DET.flexfield_recid=init_flexfield.flex_recid
order by segment_serial ;
v_segment_prompt varchar2(50);
v_counter number(16);
begin
---- initialize package variables
init_flexfield.flex_item_name:=target_item;
init_flexfield.flex_item_name_desc:=target_item_desc ;
init_flexfield.flex_recid:=flexfield_pkg.get_account_flexfield(1);
init_flexfield.flex_window_prompt:=flexfield_pkg.get_flexfield_window_prompt(init_flexfield.flex_recid,translation_pkg.get_user_lang_id);
init_flexfield.flex_separator:=flexfield_pkg.get_flexfield_separator(init_flexfield.flex_recid);
init_flexfield.flex_segment_number:=flexfield_pkg.get_flexfield_segment_number(init_flexfield.flex_recid);
init_flexfield.flex_dynamic_status:=flexfield_pkg.get_dynamic_insert_flag(init_flexfield.flex_recid) ;
init_flexfield.flex_block_name:=:system.trigger_block;
---- set the width of the cnavas and the window ----------
set_canvas_property('flex_can',width,(init_flexfield.flex_segment_number*100)+24);
set_window_property('flex_window',width,(init_flexfield.flex_segment_number*100)+24);
--- center the button
set_item_property('flex_block.flex_ok',x_pos,((((init_flexfield.flex_segment_number*100)+24)-100)/2));
set_item_property('flex_block.flex_cancel',x_pos,((((init_flexfield.flex_segment_number*100)+24)-100)/2)+60);
-------- set the specified number of segment---
for i in 1..init_flexfield.flex_segment_number loop
set_item_property('segment_'i,visible,property_true);
set_item_property('segment_'i,enabled,property_true);
set_item_property('segment_'i,navigable,property_true);
set_item_property('segment_'i,required,property_true);
set_item_property('desc_'i,visible,property_true);
end loop;
--- clear the combined value and desc
:combined_value:='';
:combined_desc:='';
v_counter:=1;
---- set prompt of the segment -- and assign the value set of each segment to the corresponding item
for rec in c_cur loop
copy('','segment_'v_counter);
copy('','desc_'v_counter);
copy(rec.value_set_recid,'value_set_'v_counter);
v_segment_prompt:=flexfield_pkg.get_segment_prompt(rec.flexfield_det_recid,translation_pkg.get_user_lang_id);
set_item_property('segment_'v_counter,prompt_text,v_segment_prompt);
v_counter:=v_counter+1;
end loop;
--- BEFORE GO TO THE FLEXFIELD BLOCK YOU SHOULD CHANGE THE VALIDATION SCOPE
SET_FORM_PROPERTY(GET_APPLICATION_PROPERTY (CURRENT_FORM_NAME),VALIDATION_UNIT,FORM_SCOPE);
-- show the flex field cnavas and window
go_block('FLEX_BLOCK');
SET_FORM_PROPERTY(GET_APPLICATION_PROPERTY (CURRENT_FORM_NAME),VALIDATION_UNIT,ITEM_SCOPE);
end ; --- invoke flex field
--****************************************************************************
procedure validate_combination is
begin
init_flexfield.combine_Segments;
--- check if the combination exists
if flexfield_pkg.check_combination_exist(init_flexfield.flex_recid,:combined_value)=0 then --- the combination not existed before
--- check if the current flexfiedl allow dynamic insertion of non existed combination
if init_flexfield.flex_dynamic_status =true then
--- insert the combination
flexfield_pkg.generate_combination(init_flexfield.flex_recid,:combined_value,:combined_desc);
else
message ('This combination not existed ');message ('This combination not existed ');
raise form_trigger_failure ;
end if ;
end if;
copy(:combined_value,init_flexfield.flex_item_name);
copy(:combined_DESC,init_flexfield.flex_item_name_desc);
go_block(init_flexfield.flex_block_name);
go_item(init_flexfield.flex_item_name);
end ;
--#########################################################################3
END;
---------------------------- Some Data base packages Used ---------------------
CREATE OR REPLACE PACKAGE BODY TMS.flexfield_pkg AS
/******************************************************************************
NAME: flexfield_pkg
PURPOSE:
REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 28/07/2009 1. Created this package body.
******************************************************************************/
Function get_dynamic_insert_flag(p_flexfield_recid number) return boolean is
v_result number(2);
cursor c_cur is (select gn_flexfield.ALLOW_DYNAMIC_INSERT from gn_flexfield where gn_flexfield.FLEXFIELD_RECID = p_flexfield_recid) ;
begin
open c_cur ;
fetch c_cur into v_result;
if c_cur%notfound then
close c_cur ;
return false ;
end if ;
close c_cur ;
if v_result =1 then
return true;
elsif v_result =0 then
return false ;
else
return false ;
end if ;
end;
--**********************************************************************************************
Function check_combination_exist(p_flexfield_recid number , p_combination varchar2) return number is
cursor c_cur is (select gn_flexfield_comb.FLEXFIELD_COMB_RECID from gn_flexfield_comb where gn_flexfield_comb.FLEXFIELD_RECID=p_flexfield_Recid
and gn_flexfield_comb.SEGMENT_COMB_VALUE= p_combination);
v_return number(16);
begin
if p_flexfield_Recid is null or p_combination is null then
return null;
end if ;
open c_cur ;
fetch c_cur into v_Return;
if c_cur%notfound then
v_return:=0;
end if ;
close c_cur ;
if v_return>0 then
v_return:=1;
end if ;
return v_return ;
end ;
--****************************************************************************************
Procedure generate_combination(p_flexfield_recid number , p_combination varchar2,p_combination_desc varchar2) is
PRAGMA AUTONOMOUS_TRANSACTION;
begin
insert into gn_flexfield_comb (gn_flexfield_comb.FLEXFIELD_RECID,gn_flexfield_comb.SEGMENT_COMB_VALUE,gn_flexfield_comb.SEGMENT_COMB_DESC)
values (p_flexfield_recid,p_combination,p_combination_desc);
commit;
end ;
----*********************************************************************************************
function validate_sement_value(p_seg_value varchar2, p_value_set_Recid number) return number is
cursor c_cur is (select count(*) from gn_value_set_dtL where (gn_value_set_dtl.VALUE_SET_CODE =p_seg_value and
gn_value_set_dtl.VALUE_SET_RECID=p_value_set_Recid) AND gn_value_set_dtL.VALUE_STATUS=1 );
v_return number(16);
begin
open c_cur ;
fetch c_cur into v_return ;
close c_cur ;
return v_return;
end ;
--*******************************************************************************************
Function get_account_flexfield(p_sob_recid number) return number is
begin
return 1;
end ;
--******************************************************************************/
function get_segment_value_set_Recid ( p_segment_recid number) return number is
cursor c_cur is (select gn_flexfield_det.VALUE_SET_RECID from gn_flexfield_det where gn_flexfield_det.flexfield_det_Recid= p_segment_recid );
v_return number(16);
begin
if p_segment_recid is null then
return null;
end if ;
open c_cur ;
fetch c_cur into v_Return ;
if c_cur%notfound then
close c_Cur ;
return null;
end if ;
close c_cur ;
return v_return;
end ;
---******************************************************************************************
function get_segment_prompt(p_segment_recid number,v_lang_recid number) return varchar2 is
cursor c_cur is (select gn_flexfield_det.SEGMENT_ARABIC_PROMPT from gn_flexfield_det where gn_flexfield_det.flexfield_det_Recid= p_segment_recid);
cursor c_cur_e is (select gn_flexfield_det.segment_english_prompt from gn_flexfield_det where gn_flexfield_det.flexfield_det_Recid= p_segment_recid );
v_return varchar2(50);
begin
if p_segment_recid is null or v_lang_recid is null then
return null;
end if ;
if v_lang_recid =1 then
open c_cur ;
fetch c_cur into v_Return ;
if c_cur%notfound then
close c_Cur ;
return null;
end if ;
close c_cur ;
else
open c_cur_e ;
fetch c_cur_e into v_Return ;
if c_cur_e%notfound then
close c_Cur_e ;
return null;
end if ;
close c_cur_e;
end if ;
return v_return;
end ;
---**************************************************************************************************
Function get_flexfield_segment_number(p_flexfield_Recid number) return number is
cursor c_cur is (select gn_flexfield.SEGMENT_NUMBER from gn_flexfield where gn_flexfield.flexfield_Recid= p_flexfield_recid);
v_return number(2);
begin
if p_flexfield_recid is null then
return null;
end if ;
open c_cur ;
fetch c_cur into v_Return ;
if c_cur%notfound then
close c_Cur ;
return null;
end if ;
close c_cur ;
return v_return;
end ;
---*********************************************************************************
Function get_flexfield_separator(p_flexfield_recid number)return varchar2 is
cursor c_cur is (select gn_flexfield.SEPARATOR from gn_flexfield where gn_flexfield.flexfield_Recid= p_flexfield_recid);
v_return varchar2(1);
begin
if p_flexfield_recid is null then
return null;
end if ;
open c_cur ;
fetch c_cur into v_Return ;
if c_cur%notfound then
close c_Cur ;
return null;
end if ;
close c_cur ;
return v_return;
end ;
---************************************************************************************************
Function get_flexfield_window_prompt(p_flexfield_recid number,v_lang_id number ) return varchar2 is
cursor c_cur is (select gn_flexfield.arabic_window_prompt from gn_flexfield where gn_flexfield.flexfield_Recid= p_flexfield_recid);
cursor c_cur_e is (select gn_flexfield.english_window_prompt from gn_flexfield where gn_flexfield.flexfield_Recid= p_flexfield_recid );
v_return varchar2(50);
begin
if p_flexfield_recid is null then
return null;
end if ;
if v_lang_id =1 then
open c_cur ;
fetch c_cur into v_Return ;
if c_cur%notfound then
close c_Cur ;
return null;
end if ;
close c_cur ;
else
open c_cur_e ;
fetch c_cur_e into v_Return ;
if c_cur_e%notfound then
close c_Cur_e ;
return null;
end if ;
close c_cur_e;
end if ;
return v_return;
end ;
--################################################################################3
END flexfield_pkg;
/
---------------------------------------------------------------------------------
the code in the segments item triggers
1- key list value init_flexfield.invoke_lov;
2- pre-text init_flexfield.ensure_right_lov ;
3-When - validate item init_flexfield.validate_segment_value ;
the code that you will write it to invoke the flexfield from your form
init_flexfield.invoke_flex_field('TARGET_ITEM','target_item_desc');
--------------------------------------------------------------------------------------
