Thursday, December 17, 2009

Concurrent Executable and Program Creations and Deletions(Using by API(s))

* User cannot delete the concurrent program through oracle applications. (FRONT END)
* User can disable / enable the concurrent program.
* Oracle gives the deleting access through the following API(s).

API(s) for create the concurrent executable, program and attaching to the request group.

fnd_program.executable Create the concurrent executable.
fnd_program.register Create the concurrent program.
fnd_program.add_to_group Attach the program into request group.

API(s) for deleting the concurrent executable, program and remove the same from request group.

fnd_program.remove_from_group Remove the concurrent program entry in the request group.
fnd_program.delete_program Remove the program in case it is already registered before.
fnd_program.delete_executable Remove the Executable in case it is already registered before.

(Sample Script for the same)

SET SERVEROUTPUT ON

DECLARE
v_application_name apps.fnd_application_tl.application_name%TYPE;
vc_sub_query VARCHAR2 (100);
BEGIN
BEGIN
SELECT application_name
INTO v_application_name
FROM apps.fnd_application_tl fat,
apps.fnd_application fa
WHERE fat.application_id = fa.application_id
AND application_short_name =
'APPLN_SHORT_NAME';
/* APPLN_SHORT_NAME -> CUSTOM APPLICATION SHORT NAME */
DBMS_OUTPUT.put_line ( 'Application name is '
|| v_application_name
);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line
( 'Error getting the application name'
|| v_application_name
);
END;

/* STEP 1 -- Remove the request group incase it is already registered before */
BEGIN
apps.fnd_program.remove_from_group
(program_short_name => 'CONC_PRGM_SHORT_NAME',
program_application => v_application_name,
request_group => 'REQUEST_GROUP_NAME',
group_application => 'APPLN_NAME'
);
/*
'CONC_PRGM_SHORT_NAME'-> CUSTOM CONCURRENT PROGRAM SHORT NAME
User can change the appropriate request group and application
*/
DBMS_OUTPUT.put_line
( ' Removed the program from the request group.'
|| 'CONC_PRGM_SHORT_NAME'
);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line
( 'Error getting then remove the program from Request group.'
|| SQLERRM
);
END;

/* STEP 2 -- Remove the conc program incase it is already registered before */
BEGIN
apps.fnd_program.delete_program
(program_short_name => 'CONC_PRGM_SHORT_NAME',
application => v_application_name
);
/* 'CONC_PRGM_SHORT_NAME'-> CUSTOM CONCURRENT PROGRAM SHORT NAME */
DBMS_OUTPUT.put_line ( 'Program Deleted.'
|| 'CONC_PRGM_SHORT_NAME'
);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line
( 'Error deleting program: '
|| SQLERRM
);
END;

/* STEP 3-- Remove the conc executable incase it is already registered before */
BEGIN
apps.fnd_program.delete_executable
(executable_short_name => 'CONC_EXEC_SHORT_NAME',
application => v_application_name
);
DBMS_OUTPUT.put_line ( 'Executable Deleted. '
|| 'CONC_EXEC_SHORT_NAME'
);
/* 'CONC_EXEC_SHORT_NAME'-> CUSTOM CONC EXEC SHORT NAME */
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line
( 'Error deleting program: '
|| SQLERRM
);
END;

/* STEP 4 --- Concurrent executable creations */
BEGIN
apps.fnd_program.executable
(executable => 'CONC_EXEC_SHORT_NAME',
application => v_application_name,
short_name => 'CONC_EXEC_SHORT_NAME',
description => 'CONC_EXEC_DESCRIPTION',
execution_method => 'PL/SQL Stored Procedure',
execution_file_name => 'PKG.PROCEDURE_NAME',
subroutine_name => ''
);
DBMS_OUTPUT.put_line
( 'Created the Executable. '
|| 'CONC_EXEC_SHORT_NAME'
);
/*
'CONC_EXEC_SHORT_NAME' -- Concurrent executable name/short name
'CONC_EXEC_DESCRIPTION'-- Concurrent executable descriptiom
'PL/SQL Stored Procedure'-- execution mehod (like Oracle Report, etc)
'PKG.PROCEDURE_NAME'-- Concurrent execution file name
*/
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line
( 'Error Creating Executable: '
|| SQLERRM
);
END;

/* STEP 5 --- Concurrent Program creations */
BEGIN
apps.fnd_program.REGISTER
(program => 'CONC_PRGM_SHORT_NAME',
application => v_application_name,
enabled => 'Y',
short_name => 'CONC_PRGM_SHORT_NAME',
description => 'CONC_PRGM_DESCRIPTION',
executable_short_name => 'CONC_EXEC_SHORT_NAME',
executable_application => v_application_name,
priority => '',
save_output => 'Y',
PRINT => 'Y',
cols => '',
ROWS => '',
style => '',
style_required => 'N',
printer => '',
use_in_srs => 'Y',
allow_disabled_values => 'N',
run_alone => 'N'
);
DBMS_OUTPUT.put_line
( 'Concurrent progrma created. '
|| 'CONC_PRGM_SHORT_NAME'
);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line
( 'Error in Registering the Program'
|| SQLERRM
);
END;

/* STEP 6 ---------- Adding Parameters (If Any) */
BEGIN
apps.fnd_program.parameter
(program_short_name => 'CONC_PRGM_SHORT_NAME',
application => v_application_name,
SEQUENCE => 10,
parameter => 'PARAMETER_1',
description => 'PARAMETER_1',
enabled => 'Y',
value_set => 'VALUE_SET_NAME',
default_type => '',
DEFAULT_VALUE => '',
required => 'N',
display => 'Y',
display_size => 10,
description_size => 50,
concatenated_description_size => 25,
prompt => 'PARAMETER_1',
token => ''
);
DBMS_OUTPUT.put_line ('Added "PARAMETER_1"');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line
( 'Error while adding "PARAMETER_1".'
|| SQLERRM
);
END;

/* STEP 7 -- Attache the concurrent Program to the request group */
BEGIN
apps.fnd_program.add_to_group
(program_short_name => 'CONC_PRGM_SHORT_NAME',
program_application => v_application_name,
request_group => 'REQUEST_GROUP_NAME',
group_application => 'APPLN_NAME'
);
DBMS_OUTPUT.put_line
('"CONC_PRGM_SHORT_NAME" attched to the request group.'
);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line
( 'Error while adding concurrent program to request group: '
|| SQLERRM
);
END;

/* COMMIT */
COMMIT;
END;
/

1 comment:

  1. Regards
    Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
    LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at training@oracleappstechnical.com | +91 - 9581017828.

    ReplyDelete