Demantra -Calling Demantra Workflow From PL/Sql Script

DEMANTRA:Calling Demantra Workflow From Plsql Script


create or replace procedure run_workflow ( p_wf_name varchar2)
is
l_server VARCHAR2 (50);
l_dm_user varchar2 (20) :=’dm’;
l_servlet VARCHAR2 (4000) DEFAULT ‘WorkflowServer?action=run_proc’;
l_wf_process_id wf_process_log.process_id%TYPE;
l_wf_url VARCHAR2 (500);
l_cnt NUMBER;
l_password varchar2(1000);
l_wf_name varchar2(1000);

BEGIN

dbms_output.put_line( ‘Procedure Started’);

SELECT pval
INTO l_server
FROM sys_params
WHERE pname = ‘AppServerURL’;

                     dbms_output.put_line(‘l_server : ‘ || l_server);

                      l_wf_name := replace(p_wf_name,’ ‘,’%20’);

dbms_output.put_line(‘l_wf_name : ‘ || l_wf_name);

select password
into l_password
from user_id
where user_name = l_dm_user;

dbms_output.put_line(‘l_password : ‘ || l_password);

l_servlet :=
l_servlet
||chr(38)|| ‘user=’
|| l_dm_user
|| chr(38) ||’password=’
|| l_password
|| chr(38)|| ‘schema=’
|| l_wf_name;
l_wf_url := l_server || ‘/’ || l_servlet;

commit;
dbms_output.put_line( ‘l_servlet : ‘ || l_servlet);
dbms_output.put_line(‘l_wf_url : ‘ || l_wf_url);

commit;

SELECT UTL_HTTP.request (l_wf_url)
INTO l_wf_process_id
FROM DUAL;

dbms_output.put_line(‘l_wf_process_id : ‘ || l_wf_process_id);
commit;

IF l_wf_process_id = -1
THEN

 dbms_output.put_line( ‘ERROR !!!! Unable to submit workflow ‘);

raise_application_error (-20030,‘Workflow schema consists of white spaces’);
END IF;

SELECT COUNT (1)
INTO l_cnt
FROM wf_process_log
WHERE process_id = l_wf_process_id;

IF l_cnt = 0
THEN
dbms_output.put_line( ‘ERROR !!!!Workflow did not started..please check the log ‘);
raise_application_error (-20030,‘Workflow failed since server is down’);
END IF;

dbms_output.put_line(‘End of procedure’);

commit;
EXCEPTION
WHEN OTHERS
THEN
raise_application_error (-20999, SQLERRM);
end run_workflow ;