Flexible Contact query for all entities in your application

as we published the first version of the contact object group that can be added to any form in your application and register your entities that you want to have contact , here we will publish the query process of this component

the screen is base on two blocks :
1- search block ( field for selecting the master database file i.e users , customers , operating units ..... etc another field for selecting the contact entity i.e the specified person or the specified customers )

2- the contact block that based on the contacts table to display the data according to the search criteria in the above block


the Implementation :


1- on your when new form instance trigger or your program unit that called in when new form instance trigger write the following code:
--- FILL THE MASTER FILE SEARCH WITH THE DATA
u_program_pkg.P_POPULATE_LIST('SERACH_BLK.MASTER_FILE','SELECT ARABIC_DESC,TABLE_NAME FROM GN_TABLE
WHERE ENABLE_CONTACT=1');

where the gn_Table is the table that keep the names of the tables of the system that designed to have contact , also this table contain the select statement for the id and the name of the entity to be used in the list of value to get the specified entity ( person , user , custoemr , supplier ,.... etc)
2- on when list changed of the above list item
vREG_ID RECORDGROUP;
vEXECUTE NUMBER;
BEGIN
vREG_ID := FIND_GROUP('flex_group');
IF NOT ID_NULL(vREG_ID) THEN
DELETE_GROUP(vREG_ID);
END IF;
vEXECUTE := POPULATE_GROUP_with_query(vREG_ID,:p_query);
set_lov_property('flex_lov',group_name,'flex_group'); to assign the used select statement for the selected master table in the list iem

3- now the list of values now assigned with the id and the name of the entity that you will select form to display the contacts assigned to this specified entity

Get Online Currency Exchange Rates

here we will explain how to use a java bean in the forms to get the currecny exchange rates from webservice provided by a website on the internet

first step for implementing this java bean is
1- put the jar file CurrencyExchange.jar in the path form
2- add the path of this jar file with the full name in the archivjinit paramter in your working section in the Fromsweb.cfg file
3-add the full path in step 2 also in classpath parameter in Default.env file

4- create new form and create a bean item and set its implementation class to
oracle.forms.jvr.CurrencyExchange

5- create items that hold from currency and to currency with the values of standard currency symbol

6- call the following code

fbean.invoke('yourblock.yourbean',1,'setFrom',:fromcurrencybindvariable);
fbean.invoke('yourblock.yourbean',1,'setTo',:tocurrencybindvariable);
:bindvariableforresult:=fbean.invoke_char('yourblock.bean',1,'change','');

the jar file link
http://www.4shared.com/file/146643242/9c8e15e6/CurrencyExchange.html

Dynamic Import using sqlldr

Here i provide a form that can be used to import dynamically data from a specified file to selected table and columns

- this version run in the environment that the database and application server is on the same server , other environment needs extra code adding to the provided code.

- you can add new feature or functionality to the code provided , or enhance the existing code according to your requirements

-- You choose the table that you want to upload data into from the provided list of the tables in your schema.

-- you will choose the columns form the table column list populated

-- write the file name with extension that you want to upload data from

-- choose if truncte the table or append the data to the existing data

------ the explaination of the mechanism

-- the main idea is to dynamically create the control file , and the batch file that will change the running oracle home and run the Sql loader command using the control file created

an audit piece of code is issued at the end of the program unit to audit the importing process.

-- the user will only choose the table and column and write the file name with extenstion .

--- Code Sample -----

PROCEDURE Import_process IS
v_load_directory varchar2(30);
V_FILE_TYPE UTL_FILE.FILE_TYPE;
v_directory_path varchar2(200) ;
v_file_name varchar2(50);
v_ext varchar2(3);
V_COL_LIST VARCHAR2(500);
V_SEP_POS NUMBER(3);
V_ORA_HOME VARCHAR2(500);
v_bat_file_name varchar2(500);
v_control_file varchar2(50);

BEGIN

if :table_name is null then
message('You Must Choose Table For Importing Process'); message('You Must Choose Table For Importing Process');
raise form_trigger_failure ;
end if ;

if :file_name is null then
message('You Must Specify File Name You Want to Upload'); message('You Must Specify File Name You Want to Upload');
raise form_trigger_failure ;
end if ;

if Get_List_Element_Count('SELECTED_COLS')=0 then
message('No Column Selected For Importing Process');message('No Column Selected For Importing Process');
raise form_trigger_failure ;
end if ;

--- start collecting information and parameters for the process
v_load_directory:=sm_check_pkg.check_application_setting('LOAD_DATA_DIR');
v_directory_path :=sm_get_pkg.get_directory_path(v_load_directory);
v_file_name:=substr(:file_name,1,instr(:file_name,'.')-1) ;
v_ext:=substr (:file_name,-3) ;
V_ORA_HOME:=SM_CHECK_PKG.CHECK_APPLICATION_SETTING('DB_ORA_HOME');


V_COL_LIST:='';
For i IN 1 .. Get_List_Element_Count('SELECTED_COLS') Loop
V_COL_LIST:=V_COL_LIST','Get_List_Element_Value( 'SELECTED_COLS', i );
End loop ;
---V_SEP_POS:=INSTR(V_COL_LIST,',',-1);

V_COL_LIST:=SUBSTR(V_COL_LIST,2);

-- CREATE TEH CONTROL FILE
v_control_file:=dbms_random.STRING('b',8)'.''ctl';
v_file_type:=utl_file.fopen(v_load_directory,v_control_file,'W');
UTL_FILE.putf(V_FILE_TYPE,'LOAD DATA'); -- first line
UTL_FILE.new_line(V_FILE_TYPE); ------------------------------------------
UTL_FILE.putf(V_FILE_TYPE,'INFILE ''''' v_directory_path'\':file_name''''); -- second line
UTL_FILE.new_line(V_FILE_TYPE); ------------------------------------------
UTL_FILE.putf(V_FILE_TYPE,'BADFILE ''''' v_directory_path'\'v_file_name'.''bad'''''); -- third line
UTL_FILE.new_line(V_FILE_TYPE); ------------------------------------------
UTL_FILE.putf(V_FILE_TYPE,'DISCARDFILE ''''' v_directory_path'\'v_file_name'.''dsc'''''); -- fourth line
UTL_FILE.new_line(V_FILE_TYPE); ------------------------------------------
UTL_FILE.putf(V_FILE_TYPE,:PREDATA_02);
UTL_FILE.new_line(V_FILE_TYPE); ------------------------------------------
UTL_FILE.putf(V_FILE_TYPE,'INTO TABLE ''"TMS"''.''"':TABLE_NAME'"');
UTL_FILE.new_line(V_FILE_TYPE); ------------------------------------------
UTL_FILE.putf(V_FILE_TYPE,'FIELDS TERMINATED BY '''''';''''');
UTL_FILE.new_line(V_FILE_TYPE); ------------------------------------------
UTL_FILE.putf(V_FILE_TYPE,'('V_COL_LIST')');
UTL_FILE.fclose(V_FILE_TYPE);
synchronize;

---- CREATE THE BATCH FILE
v_bat_file_name:=dbms_random.STRING('b',8)'.''bat';
v_file_type:=utl_file.fopen(v_load_directory,v_bat_file_name,'W');
UTL_FILE.putf(V_FILE_TYPE,'@echo on ');
UTL_FILE.new_line(V_FILE_TYPE); ------------------------------------------
UTL_FILE.putf(V_FILE_TYPE,'set oracle_home='v_ora_home);
UTL_FILE.new_line(V_FILE_TYPE); ------------------------------------------
UTL_FILE.putf(V_FILE_TYPE,'cd\');
UTL_FILE.new_line(V_FILE_TYPE); ------------------------------------------
UTL_FILE.putf(V_FILE_TYPE,v_ora_home'\bin\sqlldr.exe ' 'userid=tms/tms@hdb control='v_directory_path'\'v_control_file);
UTL_FILE.fclose(V_FILE_TYPE);
synchronize;

host('start 'v_directory_path'\'v_bat_file_name);

if Form_success then
paragma_pkg.p_import_audit(info_pkg.get_current_user_id,v_control_file,v_bat_file_name);
end if ;
END;

Forms Dynamic Audit Program

For sure that auditing the insert , update , delete transaction is ver cretical issue for some customers although the auditing may little preformance reduction , but they are still interested in monitoring the DML transactions of the application user ,

OF Course we know that there are two main options about auditing

First is the Database audit it self ( you can enable this feature for selected or all tables of your application )

Second is the Forms side audit and this is the option which we provide dynamic solution

you need dynamic and effective design to implement this auditing with minumum coding

at first you need two tables ( gn _audit_master , gn_audit_detail ) two table instead of one to reduce the storage required by this program.

- Note that all column names and table names are related to your environment i.e. you can choose any name you want

after that you will make a procedure to insert the master record detail ( like the current form , the current user , the current date , .... )

- then you can start to code the procedure that will implement this functionality

- in your form create Post_insert-trigger in the block you want to audit and

call your procedure and pass the operation type 'I' means insert

- in your form create Post- upate in the block you want to audit and call your procedure and pass the operation type 'U' means update

- in your form create Pre-delete ................................

-- usage note :

1- if your primary key item get its value from before insert trigger on the database , so you must change the block property DML_return_value to 'Yes'

2- the code contains ' substr , 'string' these literals are related to My environment and you can customize your code according to yours

3- the program enable you to track all operation with all values even the record is deleted and the ability to restore the deleted row with the same values exactly

4- after you understand the functionality of the program you can adjust it to add new functionaliyt , edit existing functionality, or delete exisiting feature.

the code and scripts available on this link :

http://www.4shared.com/file/127826208/fabb5f2d/Audit_program.html

Before Starting ( Application Development and Desgin Standards)

- Here in this post we will provide sample of guides that Any Development and Design team should keep and apply .

1- You Should Comment your Tables and database objects to facilitate the understanding of the database design and reduce the cost of any future modification and facilitates the knowledge requested by any developer or desginer

2- You should comment your columns if needed to be cleared what is this column functionality is . ( if columns are commented , the developer will know in very easy way what this column is designed to , and whic values allowed & which symbol means )

3- Naming Convention for database objects and its attributes should be kept to facilitate the work process and make any common adjustments can be done.( suppose that you keep the naming convention of your tables to be prefixed by 2 letter_ and the table primary key column is composed of your table name without the prefeix and suffixed by _id, you can create a stored procedure that create a sequence and trigger for any new table , so you can save the time and effort by keeping database objects naming conventions )

4- Documentation of the database design is very important for understanding, updating , and maintaining the database design , this document should describe the details of the database design and its functionality and break down ERDs .

5- The development environment must have maintained Templates for any developed business unit ( form – report - process - menue , ... etc) by having templates with subclassing you can adjust the code of specified triggers ( subclassed one ) of 1000 form module for example in 1 menutes after compiling the 1000 if you use object classing , or only one minute without compiling if you call the code stored in pll library ,

also templates are very critical issue in the development process or the interface changing .

6- Commenting the code should include the name of author

, the date of creation, the date of adjustment, the reason,

simple description of the functionality of the code. ,and hints on every line important to know

7- Testing Procedures must be set , starting with the unit test , transaction test to improve the quality of the product the process can be as the following :

- Test the business unit against (insert , update, delete, and query functions)

- Test the business unit against the required validation of the form or the report

- Test the functionality of the form or report against the functionality in the whole system cycle.

- Maintain more than one test scenario for the business unit .

8- Performance considerations must be taken to improve the quality of the product according to your frame work and your requirements , forexample

- Any SQL statement must be written on the database side not in the forms side to avoide the network traffic caused by the database roundtrips

- Any business unit must be developed from the designed template ( Form module from the from module template , report from the report template, parameter form from the parameter form template ,….. etc) to centeralize the performance tunning issues

- Packages Should be maintained and used in the Development process to get their benefits in performance and organizing the work

- Naming Conventions of the forms , reports ,PLL libraries , OLB libraries , Object groups , Program units , canvases, blocks, …. Etc must be maintained to easily develop and maintain (easy and fast )

- Any common Pl/sql code must be packaged in the PLL libraries to ensure that the right code is used for the issue arised

- Any common interface items packaging should be packaged in Object group (for example approval process , contact object group )

- Primary keys must be numbers only and auto filled not user filled other unique columns like code can have unique constraint and can maintained by the user

- No composite primary keys are allowed

- Use the appropriate way to get block non related data ( Join – view – database function ) according to the situation rather than using the post – query trigger

- Any in ordinary way of coding must be discussed between team member before using it in the development process ( discussion among the team member is very important and very usefull process )

- Any object group, templates, Should be documented and has its read me file to guide the developer how to use it in the development process.

- The basic triggers of the form or the report mustn’t be used by the developer ( instead write the code in the called procedures in the original triggers for example design your when-new-form-instance to call the following procedure - p_before_init ;

Fram_work_code;

p_after_init ;
p_before_init ; is module program unit that the user can write his code when he want to execute his own code in the when new form instance trigger , and so on .....

- Messaging to the end user must be done through defined package in the system to facilitate any future modification or enhancements ( like multilingual messaging)

- Any interface item ( text item , display item , list item , Button , ….. etc) must be sub classed from the appropriate object ( smart class shoul be maintained to easily do this process)

- Any tabular Block should have indicator item to improve the interface looking and current record visual attribute

- Any huge number of columns to be displayed on the form should be set its canvas to stack canvas style to expand horizontally.

9- “ Know How “ documentation must be maintained to facilitate the work process and exchange the experience between team members

10- The implementation of the work to the production Environment must have documented procedures and flow chart ( this is very important issue )

11- Documentation of how to develop Form Module, how to develop Report module, how to develop parameter form Must be read before any development starts for the first time , to avoid the violation of develompment standrds

Read Client machine Information ( Get benefit of using the java beans in Forms)

Using java beans in your forms is very usefull action to increase your application performance , functionality , and interface attraction .





fore example


1- a java bean that restrict the input of text item to number will decrease network traffic if the user enter character.


2- a java bean that customize the shape of forms buttons will make your interface more attractive


3- a java bean that read client machine information , increase your forms functinality





in this post we will explain a java bean that can read client machine information ( webutil can do this also ) and pass it to the form

- consider that you want to know which Ip address currenty use this form

first step is to make the deployment steps of using java bean

1- pur the jar file in your java path
2- edit Archive_jinit parameter in your formsweb.cfg in your section to add this jar name
3- create a bean area in your forms
4- set the implementation class property of your bean item to
oracle.forms.fd.ClientInfos ( case sensitive)
5- write your code to get the information you want as follow :

- to get the Ip address and pass it to bind variable
:your bind variable:= Get_Custom_Property('your bean item', 1, 'IP' ) ;

- to get the operating system
:your bind variable : =get_custom_property('your bean item',1,'operating');

- to get the operating system user name
:your bind variable : =get_custome_property('your bean item',1,'username');


very important note :

-- if you want to get this information in "when new form instance "

you have to write your code in " when timer expired " to ensure that the form interface included the bean area is built

you can download the jar file from http://sheikyerbouti.developpez.com/forms-pjc-bean/menu/

PRAGMA autonmous_transaction

If you want to create a procedure to perform some dml operations and commit this transactions regardless or without affect the transactions control of the calling environment you should use PRAGMA autonmous_transaction
in the declaration section of your pl/sql code

pragma autonmous_transaction instruct oracle server to do the dml operations and commit or rollback independently of the calling environment

sample code :

create or replace procedure xx as

pragma autonmous_Transaction ;
begin
......
end;