Oracle Apps: Demantra- Procedure to launch Demantra Workflow from DB

Demantra Workflow from DB

DECLARE
/*
------------------------------------------------
local Variable Declarations
------------------------------------------------
*/

v_lc_api_name VARCHAR2(30) := 'CALL_DEM_WORKFLOW_DB';
p_in_chr_wf_name VARCHAR2(30) :=<Demantra Workflow name>
v_lc_schema_name VARCHAR2(500);
v_ln_start_time NUMBER;
v_ln_end_time NUMBER;
v_ln_code_tracker NUMBER;
v_ln_schema_id NUMBER;
v_ln_owner_id NUMBER;
v_ret_process_id VARCHAR2(2000);
v_exc_error EXCEPTION;
v_lc_app_srvr_url VARCHAR2(500);
v_dm_username VARCHAR2(30) := 'dm';
v_sop_username VARCHAR2(30) := 'sop';
v_ln_user_name VARCHAR2(30);
v_lc_dm_pass VARCHAR2(80);
v_lc_sop_pass VARCHAR2(80);
v_fnl_username VARCHAR2(30);
v_fnl_password VARCHAR2(80);
v_wf_srvr_url VARCHAR2(100) := '/WorkflowServer?action=run_proc';
v_wf_user_url VARCHAR2(100);
v_wf_pass_url VARCHAR2(100);
v_wf_schem_url VARCHAR2(100);
v_fnl_wf_url VARCHAR2(2000);
v_ld_date DATE;
v_ln_cnt NUMBER;
v_ln_cnt1 NUMBER;
v_ln_cnt_fail NUMBER;
v_bool_fail BOOLEAN;
v_ln_rec_cre_dt DATE := SYSDATE;
v_lc_dem_schema msd_dem_setup_parameters.parameter_value%TYPE;
v_chr_apps_ser_url_prm VARCHAR2(50) := 'AppServerURL';
--v_ln_cnt1 number;
v_process_id number(10);
BEGIN
fnd_file.put_line(fnd_file.log,'Start of API : '
|| '.'
|| v_lc_api_name);

v_ln_start_time := dbms_utility.get_time;
p_out_err_messg := '';
p_out_ret_code := 0;
v_ln_code_tracker := 10;
BEGIN
SELECT
parameter_value
INTO v_lc_dem_schema
FROM
apps.msd_dem_setup_parameters
WHERE
parameter_name = 'MSD_DEM_SCHEMA';
EXCEPTION WHEN NO_DATA_FOUND THEN
fnd_file.put_line(fnd_file.log,'Unable to determine demantra schema in : '
|| '.'
|| v_lc_api_name);
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.log,'Unable to determine demantra schema in : '
|| '.'
|| v_lc_api_name);
END;
/*
------------------------------------------------------------------
check the demantra schema global variable
------------------------------------------------------------------
*/

IF v_lc_dem_schema IS NULL THEN
fnd_file.put_line(fnd_file.log,'Unable to determine demantra schema in : '
|| '.'
|| v_lc_api_name);

RAISE v_exc_error;
END IF;

/*
----------------------------------------------------------------------------------
ValiDATE schema name. check IF the workflow is present in wf_schemas
----------------------------------------------------------------------------------
*/

BEGIN
EXECUTE IMMEDIATE ' SELECT schema_id ,owner_id'
|| chr(10)
|| ' FROM '
|| v_lc_dem_schema
|| '.wf_schemas '
|| chr(10)
|| ' WHERE schema_name = :1 '
INTO
v_ln_schema_id,
v_ln_owner_id
USING p_in_chr_wf_name;
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.log,'Unable to determine workflow : '
|| p_in_chr_wf_name
|| ' Please check workflow definition ');
fnd_file.put_line(fnd_file.log,'Error : ' || sqlerrm);
RAISE;
END;

fnd_file.put_line(fnd_file.log,'Schema ID : '
|| v_ln_schema_id
|| ' , '
|| 'Owner Id : '
|| v_ln_owner_id
|| ' - '
|| 'for workflow : '
|| p_in_chr_wf_name);

fnd_file.put_line(fnd_file.output,'Schema ID : '
|| v_ln_schema_id
|| ' , '
|| 'Owner Id : '
|| v_ln_owner_id
|| ' - '
|| 'for workflow : '
|| p_in_chr_wf_name);

IF v_ln_owner_id IS NOT NULL THEN
BEGIN
EXECUTE IMMEDIATE 'SELECT user_name'
|| chr(10)
|| 'FROM '
|| v_lc_dem_schema
|| '.user_id '
|| chr(10)
|| 'WHERE user_id = :1'
INTO v_ln_user_name
USING v_ln_owner_id;

EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.log,'Unable to determine user_id : '
|| v_ln_owner_id
|| ' Please check workflow definition ');
fnd_file.put_line(fnd_file.log,'Error : ' || sqlerrm);
RAISE;
END;
ELSE
v_ln_user_name := v_dm_username;
END IF;

/*
------------------------------------------------------------------
Get the Apps Server URL for demantra FROM sys_params
------------------------------------------------------------------
*/

BEGIN
EXECUTE IMMEDIATE ' SELECT pval '
|| chr(10)
|| ' FROM '
|| v_lc_dem_schema
|| '.sys_params '
|| chr(10)
|| ' WHERE pname = :1 '
INTO v_lc_app_srvr_url
USING v_chr_apps_ser_url_prm;

IF v_lc_app_srvr_url IS NULL THEN
fnd_file.put_line(fnd_file.log,'Application server URL is blank or not set. cannot run workflow!!!!');
RAISE v_exc_error;
END IF;

EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.log,'Unable to determine Application server URL : '
|| v_lc_app_srvr_url
|| ' Please check System parameters!!!');
fnd_file.put_line(fnd_file.log,'Error : ' || sqlerrm);
RAISE;
END;

/*
------------------------------------------------------------------
Determine the user and password for submitting Workflow.
we will mostly submit via DM or SOP. First preference is DM user
ELSE SOP user.
------------------------------------------------------------------
*/

BEGIN
EXECUTE IMMEDIATE ' SELECT password '
|| chr(10)
|| ' FROM '
|| v_lc_dem_schema
|| '.user_id '
|| chr(10)
|| ' WHERE user_name = :1'
INTO v_lc_dm_pass
USING v_ln_user_name;
EXCEPTION
WHEN OTHERS THEN
v_lc_dm_pass := NULL;
END;

IF v_lc_dm_pass IS NULL THEN
BEGIN
EXECUTE IMMEDIATE ' SELECT password '
|| chr(10)
|| ' FROM '
|| v_lc_dem_schema
|| '.user_id '
|| chr(10)
|| ' WHERE user_name = :1'
INTO v_lc_sop_pass
USING v_sop_username;
EXCEPTION
WHEN OTHERS THEN
v_lc_sop_pass := NULL;
END;

v_fnl_username := v_sop_username;
v_fnl_password := v_lc_sop_pass;
ELSE
v_fnl_username := v_ln_user_name;
v_fnl_password := v_lc_dm_pass;
END IF;

IF v_fnl_password IS NULL THEN
fnd_file.put_line(fnd_file.log,'Error determing dm/sop username, password. please check user ID table!!!!');
RAISE v_exc_error;
ELSE
fnd_file.put_line(fnd_file.log,'user name : '
|| v_fnl_username
|| ' is SELECTed!!!! ');
fnd_file.put_line(fnd_file.output,'user name : '
|| v_fnl_username
|| ' is SELECTed!!!! ');

v_lc_schema_name := replace(p_in_chr_wf_name,' ','%20');
v_wf_user_url := 'user=' || v_fnl_username;
v_wf_pass_url := 'password=' || v_fnl_password;
v_wf_schem_url := 'schema=' || v_lc_schema_name;
v_fnl_wf_url := v_lc_app_srvr_url
|| v_wf_srvr_url
|| '&'
|| v_wf_user_url
|| '&'
|| v_wf_pass_url
|| '&'
|| v_wf_schem_url;

fnd_file.put_line(fnd_file.log,'Final URL : ' || v_fnl_wf_url);
fnd_file.put_line(fnd_file.log,'Submitting workflow......');
BEGIN
SELECT
utl_http.request(v_fnl_wf_url)
INTO v_ret_process_id
FROM
dual;
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.log,'Error in submitting workflow : ' || sqlerrm);
RAISE;
END;

/*
----------------------------------------------------
check the status of the workflow FROM
wf_process_log
----------------------------------------------------
*/

v_ld_date := SYSDATE;
LOOP
dbms_lock.sleep(60); -- wait for a minute

/****** check for program running ******/
EXECUTE IMMEDIATE 'SELECT count(1) FROM '
|| v_lc_dem_schema
|| '.wf_process_log WHERE schema_id = :1 and status = 1 and record_created >= :2'
INTO v_ln_cnt
USING v_ln_schema_id,v_ln_rec_cre_dt;

/******** check for program failure ******/
EXECUTE IMMEDIATE 'SELECT count(1) FROM '
|| v_lc_dem_schema
|| '.wf_process_log WHERE schema_id = :1 and status = -1 and record_created >= :2'
INTO v_ln_cnt_fail
USING v_ln_schema_id,v_ln_rec_cre_dt;

if v_ret_process_id IS NOT NULL THEN

SELECT count(1) INTO v_ln_cnt1
FROM portal_task WHERE description LIKE '%'||v_ret_process_id||'%';

IF v_ln_cnt1 > 0 THEN
fnd_file.put_line(fnd_file.log,' Exiting program. Demantra workflow errored out. Please check related workflow logs'
);
RAISE v_exc_error;
END IF;
END IF;

IF v_ln_cnt_fail > 0 THEN
fnd_file.put_line(fnd_file.log,' Exiting program. Demantra workflow errored out. Please check related workflow logs'
);
RAISE v_exc_error;
END IF;

EXIT WHEN v_ln_cnt = 0;
END LOOP;

fnd_file.put_line(fnd_file.log,' Demantra workflow completed successfully.....');
END IF;

/*
-----------------------------------------------------------------------------------
Procedure level COMMIT
-----------------------------------------------------------------------------------
*/

v_ln_code_tracker := 70;
COMMIT;
v_ln_code_tracker := 80;
v_ln_end_time := dbms_utility.get_time;
fnd_file.put_line(fnd_file.log,'END of API : '
|| '.'
|| v_lc_api_name);

fnd_file.put_line(fnd_file.log,'Total time taken for the API : '
|| round( (v_ln_end_time - v_ln_start_time) / 100,2) );

EXCEPTION
WHEN OTHERS THEN
p_out_err_messg := 'Error in API : '
|| '.'
|| v_lc_api_name
|| ' - '
|| sqlerrm
|| ' at location : '
|| v_ln_code_tracker;
fnd_file.put_line(fnd_file.log,'Error in API : '
|| '.'
|| v_lc_api_name
|| ' - '
|| sqlerrm
|| ' at location : '
|| v_ln_code_tracker);
p_out_ret_code := 2;
END;

Oracle Apps

Monday, 7 January 2019

Demantra- Procedure to launch Demantra Workflow from DB

1 comment: