Let We Start From Firstly data base connectivity tool as Toad and others with Executable Query
This is the creation of “EXECUTABLE FILE with
‘.RDF FILE Format”
BEGIN
FND_PROGRAM. EXECUTABLE ('XXDIV_PO_REPORT1NEW' ---- executable
, 'Purchasing' ----application_name
, 'XX_DIV_PO_REPORT1NEW' -- short_name
, 'Executable for
DIVYANSHU_REPORT INFORMATION'---description
, 'Oracle Reports' -- execution_method
, 'DIVYANSHU_PO_REPORT_WITHOUT_PARAMETER' -- execution_file_name
<'.RDF_FILE'>
);
COMMIT;
END;
This is the Creation of the “Program File
with Register File”
BEGIN
FND_PROGRAM.REGISTER('XXDIV_PO_REPORT_PROGRAM1NEW' -- program
, 'Purchasing' -- application
, 'Y' -- enabled
, 'XX_DIV_REPORT_PRO1NEW' --program_short_name
, 'XX_DIV_REPORT_PRO1NEW' -- description
,
'XX_DIV_PO_REPORT1NEW' --executable_short_name
, 'Purchasing' -- executable_application
, '' -- execution_options
, '' -- priority
, 'Y' -- save_output
, 'Y' -- print
, '' -- cols
, '' -- rows
, 'A4' –style_required
, 'Y' – style_required
, '' -- printer
, '' -- request_type
, '' -- request_type_application
,
'Y' -- use_in_srs
, 'N' -- allow_disabled_values
, 'N' -- run_alone
, 'PDF' -- word_format_required and XML in RTF
, 'N' -- enable_trace
, 'Y' -- restart
, 'Y' -- nls_compliant
, '' -- icon_name
, 'US'
, ''
, ''
);
COMMIT;
END;
Then we give a
parameter if it required ‘FND_PROGRAM.PARAMETER’.
PO_NO as PARAMETER
DECLARE
v_program_short_name VARCHAR2 (200);
v_application VARCHAR2 (200);
v_sequence NUMBER;
v_parameter VARCHAR2 (200);
v_description VARCHAR2 (200);
v_enabled VARCHAR2 (200);
v_value_set VARCHAR2 (200);
v_default_type VARCHAR2 (200);
v_default_value VARCHAR2 (200);
v_required VARCHAR2 (200);
v_enable_security VARCHAR2 (200);
v_range VARCHAR2 (200);
v_display VARCHAR2 (200);
v_display_size NUMBER;
v_description_size NUMBER;
v_concatenated_descr_size NUMBER;
v_prompt VARCHAR2 (200);
v_token VARCHAR2 (200);
v_cd_parameter VARCHAR2 (200);
v_check VARCHAR2 (2);
BEGIN
v_program_short_name := 'XX_DIV_REPORT_PRO1NEW';
v_application := 'Purchasing';
v_sequence := 10;
v_parameter := 'PO_NUM';
v_description := 'PO_NUM';
v_enabled := 'Y';
v_value_set := '10 Characters';
v_default_type := NULL;
v_default_value := NULL;
v_required := 'N';
v_enable_security := 'N';
v_range := NULL;
v_display := 'Y';
v_display_size := 50;
v_description_size := 50;
v_concatenated_descr_size := 50;
v_prompt := 'PO_NUM';
v_token := 'PO_NUM';
v_cd_parameter := NULL;
apps.fnd_program.parameter
(program_short_name => v_program_short_name,
application
=> v_application,
SEQUENCE => v_sequence,
parameter
=> v_parameter,
description
=> v_description,
enabled
=> v_enabled,
value_set
=> v_value_set,
default_type
=> v_default_type,
DEFAULT_VALUE
=> v_default_value,
required => v_required,
enable_security
=> v_enable_security,
RANGE => v_range,
display
=> v_display,
display_size
=> v_display_size,
description_size
=> v_description_size,
concatenated_description_size
=> v_concatenated_descr_size,
prompt => v_prompt,
token
=> v_token,
cd_parameter
=> v_cd_parameter
);
COMMIT;
BEGIN
SELECT 'Y'
INTO v_check
FROM fnd_descr_flex_column_usages
WHERE descriptive_flexfield_name = '$SRS$.' || 'XX_DIV_REPORT_PRO1NEW'—-program_short_name
AND end_user_column_name = '1013419'—-User_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line
('Concurrent Program Parameter
Registration Failed');
END;
END;
Then we give a
parameter if it required ‘FND_PROGRAM.PARAMETER’.
FROM_DATE as PARAMETER
DECLARE
v_program_short_name VARCHAR2 (200);
v_application VARCHAR2 (200);
v_sequence NUMBER;
v_parameter VARCHAR2 (200);
v_description VARCHAR2 (200);
v_enabled VARCHAR2 (200);
v_value_set VARCHAR2 (200);
v_default_type VARCHAR2 (200);
v_default_value VARCHAR2 (200);
v_required VARCHAR2 (200);
v_enable_security VARCHAR2 (200);
v_range VARCHAR2 (200);
v_display VARCHAR2 (200);
v_display_size NUMBER;
v_description_size NUMBER;
v_concatenated_descr_size NUMBER;
v_prompt VARCHAR2 (200);
v_token VARCHAR2 (200);
v_cd_parameter VARCHAR2 (200);
v_check VARCHAR2 (2);
BEGIN
v_program_short_name := 'XX_DIV_REPORT_PRO1NEW';
v_application := 'Purchasing';
v_sequence := 20;
v_parameter := 'P_FROM_DATE';
v_description := 'P_FROM_DATE';
v_enabled := 'Y';
v_value_set := 'FND_DATE_STANDARD';
v_default_type := NULL;
v_default_value := NULL;
v_required := 'N';
v_enable_security := 'N';
v_range := NULL;
v_display :=
'Y';
v_display_size := 50;
v_description_size := 11;
v_concatenated_descr_size := 50;
v_prompt := 'P_FROM_DATE';
v_token := 'P_FROM_DATE';
v_cd_parameter := NULL;
apps.fnd_program.parameter
(program_short_name => v_program_short_name,
application
=> v_application,
SEQUENCE => v_sequence,
parameter
=> v_parameter,
description
=> v_description,
enabled
=> v_enabled,
value_set
=> v_value_set,
default_type
=> v_default_type,
DEFAULT_VALUE
=> v_default_value,
required => v_required,
enable_security
=> v_enable_security,
RANGE => v_range,
display
=> v_display,
display_size
=> v_display_size,
description_size
=> v_description_size,
concatenated_description_size
=> v_concatenated_descr_size,
prompt => v_prompt,
token
=> v_token,
cd_parameter => v_cd_parameter
);
COMMIT;
BEGIN
SELECT 'Y'
INTO v_check
FROM fnd_descr_flex_column_usages
WHERE descriptive_flexfield_name = '$SRS$.' || 'XX_DIV_REPORT_PRO1NEW'
AND end_user_column_name = '1013419';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line
('Concurrent Program Parameter
Registration Failed');
END;
END;
DECLARE
v_program_short_name VARCHAR2 (200);
v_application VARCHAR2 (200);
v_sequence NUMBER;
v_parameter VARCHAR2 (200);
v_description VARCHAR2 (200);
v_enabled VARCHAR2 (200);
v_value_set VARCHAR2 (200);
v_default_type VARCHAR2 (200);
v_default_value VARCHAR2 (200);
v_required VARCHAR2 (200);
v_enable_security VARCHAR2 (200);
v_range VARCHAR2 (200);
v_display VARCHAR2 (200);
v_display_size NUMBER;
v_description_size NUMBER;
v_concatenated_descr_size NUMBER;
v_prompt VARCHAR2 (200);
v_token VARCHAR2 (200);
v_cd_parameter VARCHAR2 (200);
v_check VARCHAR2 (2);
BEGIN
v_program_short_name := 'XX_DIV_REPORT_PRO1NEW';
v_application := 'Purchasing';
v_sequence := 20;
v_parameter := 'P_FROM_DATE';
v_description := 'P_FROM_DATE';
v_enabled :=
'Y';
v_value_set := 'FND_DATE_STANDARD';
v_default_type := NULL;
v_default_value := NULL;
v_required := 'N';
v_enable_security := 'N';
v_range := NULL;
v_display := 'Y';
v_display_size := 50;
v_description_size := 11;
v_concatenated_descr_size := 50;
v_prompt := 'P_FROM_DATE';
v_token := 'P_FROM_DATE';
v_cd_parameter := NULL;
apps.fnd_program.parameter
(program_short_name => v_program_short_name,
application => v_application,
SEQUENCE => v_sequence,
parameter
=> v_parameter,
description
=> v_description,
enabled => v_enabled,
value_set
=> v_value_set,
default_type
=> v_default_type,
DEFAULT_VALUE
=> v_default_value,
required => v_required,
enable_security
=> v_enable_security,
RANGE => v_range,
display
=> v_display,
display_size => v_display_size,
description_size
=> v_description_size,
concatenated_description_size
=> v_concatenated_descr_size,
prompt => v_prompt,
token
=> v_token,
cd_parameter
=> v_cd_parameter
);
COMMIT;
BEGIN
SELECT 'Y'
INTO v_check
FROM fnd_descr_flex_column_usages
WHERE descriptive_flexfield_name = '$SRS$.' || 'XX_DIV_REPORT_PRO1NEW'—-program_short_name
AND end_user_column_name =
'1013419'--User_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line
('Concurrent Program Parameter
Registration Failed');
END;
END;
Then create
‘FND_PROGRAM.ADD_TO_GROUP’.
BEGIN
FND_PROGRAM.ADD_TO_GROUP('XX_DIV_REPORT_PRO1NEW' -- program_short_name
, 'Purchasing' -- application
, 'JAI_Purchasing_RG' -- Report Group Name
, 'Asia/Pacific Localizations'); -- Report Group Application
COMMIT;
END;
Finally run the
submit request ‘FND_REQUEST.SUBMIT_REQUEST’ and before the submit request, initialize
that particular request ‘FND_GLOBAL.APPS_INITIALIZE'.
DECLARE
l_success NUMBER;
BEGIN
FND_GLOBAL.APPS_INITIALIZE(1013419, 65765, 7000);
--- userid ,responsibilityid ,applicationid
-- If you are directly running
from the database using the TOAD, SQL-NAVIGATOR or --SQL*PLUS etc. Then you
need to Initialize the Apps. In this case use the above API to --Initialize the
APPS. If you are using same code in some procedure and running directly
--from application then you
don't need to initalize.
--Then you can comment the
above API.
l_success :=
fnd_request.submit_request
('po', -- Application Short name of
the Concurrent Program.
'XX_DIV_REPORT_PRO1NEW', -- Program Short Name.
'XX_DIV_REPORT_PRO1NEW', -- Description of the
Program.
SYSDATE, -- Submitted date. Always give the SYSDATE.
FALSE, -- Always give the FLASE.
'1-apr-2010','18-apr-2013' -- Passing the Value to the First Parameter of the report.
);
COMMIT;
-- Note:- In the above
request Run, I have created the Report, which has one parameter.
IF l_success = 0
THEN
-- fnd_file.put_line
(fnd_file.LOG, 'Request submission For this store FAILED' );
DBMS_OUTPUT.put_line
('Request submission For this
store FAILED');
ELSE
-- fnd_file.put_line
(fnd_file.LOG, 'Request submission for this store SUCCESSFUL');
DBMS_OUTPUT.put_line
('Request submission For this
store SUCCESSFUL');
END IF;
--Note:- If you are running
directly from database, use DBMS API to display. If you are
-- Running directly from
Application, then Use the fnd_file API to write the message
-- in the log file.
END;
this blog is very nice to the learner.
ReplyDelete