This Program will register concurrent executable, programs, parameters for the Bhaskar Printed Requisitions Report
Creating
Concurrent Executable and Program and adding to a Request Group
----- Parameter Hints -----
PROMPT application_name : PO
PROMPT group_application_name
: Purchasing
PROMPT request_group_name : All Reports
DECLARE
application_name_in
VARCHAR2(500);
group_application_name_in
VARCHAR2(500);
request_group_name_in
VARCHAR2(500);
application_id_ret
NUMBER;
group_application_id_ret
NUMBER;
group_id NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE('1');
application_name_in
:= 'PO';
group_application_name_in := 'Purchasing';
request_group_name_in := 'All Reports';
BEGIN
If
fnd_program.program_exists('POXPREQR',application_name_in) then
fnd_program.remove_from_group
(
program_short_name => 'POXPREQR'
,program_application => application_name_in
,request_group => request_group_name_in
,group_application => group_application_name_in
);
End if;
END;
DBMS_OUTPUT.PUT_LINE('2');
/********************************************************************
-- If Program
Exists, Delete Concurrent Program and it's executables
********************************************************************/
BEGIN
IF
FND_PROGRAM.PROGRAM_EXISTS
('POXPREQR',application_name_in) THEN
FND_PROGRAM.DELETE_PROGRAM
('POXPREQR',application_name_in);
FND_PROGRAM.DELETE_EXECUTABLE('POXPREQR',application_name_in);
END IF;
END ;
/*******************************************************************
-- Create
concurrent program executable
******************************************************************/
BEGIN
FND_PROGRAM.executable (
executable => 'POXPREQR',
application => application_name_in,
short_name => 'POXPREQR',
description => 'Printed Purchase Requisition
Report',
execution_method => 'Oracle
Reports',
execution_file_name =>
'POXPREQR',
subroutine_name => NULL,
icon_name => NULL,
language_code => 'US',
execution_file_path => NULL
);
END;
/******************************************************************
-- Create
concurrent program
******************************************************************/
BEGIN
FND_PROGRAM.register
(
program => 'Bhaskar Printed
Requisitions Report',
application => application_name_in,
enabled => 'Y',
short_name => 'POXPREQR',
description => 'Printed Requisitions
Report',
executable_short_name =>
'POXPREQR',
executable_application =>
Application_name_in,
execution_options => NULL,
priority => NULL,
save_output => 'Y',
print => 'Y',
cols => NULL,
rows => NULL,
style => 'Landscape',
style_required => 'N',
printer => NULL,
request_type =>
NULL,
request_type_application => NULL,
use_in_srs => 'Y',
allow_disabled_values =>
'Y',
run_alone => 'N',
output_type => 'PDF',
enable_trace =>
'N',
restart => 'Y',
nls_compliant =>
'Y',
icon_name => NULL,
language_code =>
'US',
mls_function_short_name =>
NULL,
mls_function_application => NULL,
incrementor => NULL
);
END;
/*********************************************************************
-- Creating
Value set for Apprvoed Requisition_Number From
*********************************************************************/
BEGIN
IF
fnd_flex_val_api.valueset_exists('PO_SRS_APPROVED_REQUISITION_NUMBER')
THEN
fnd_flex_val_api.delete_valueset('PO_SRS_APPROVED_REQUISITION_NUMBER');
END
IF;
END;
BEGIN
IF NOT
fnd_flex_val_api.valueset_exists('PO_SRS_APPROVED_REQUISITION_NUMBER') THEN
fnd_flex_val_api.set_session_mode('customer_data');
fnd_flex_val_api.create_valueset_table(
value_set_name
=> 'PO_SRS_APPROVED_REQUISITION_NUMBER'
,description => 'Approved Requisition
Number'
,security_available
=> 'N'
,enable_longlist
=> 'N'
,format_type
=> 'Char'
,maximum_size
=> 20
,precision
=> NULL
,numbers_only
=> 'N'
,uppercase_only
=> 'N'
,right_justify_zero_fill
=> 'N'
,min_value
=> NULL
,max_value
=> NULL
,table_application
=> 'Purchasing'
,table_name
=> 'PO_REQUISITION_HEADERS PRH,PO_SYSTEM_PARAMETERS PSP'
,allow_parent_values
=> 'N'
,value_column_name => 'PRH.SEGMENT1'
,value_column_type
=> 'Char'
,value_column_size
=> 20
,meaning_column_name => NULL
,meaning_column_type
=> NULL
,meaning_column_size
=> NULL
,id_column_name
=> 'PRH.SEGMENT1'
,id_column_type => 'Char'
,id_column_size
=> 20
,where_order_by
=> 'where (nvl(PRH.authorization_status,'INCOMPLETE')='APPROVED')
ORDER BY
DECODE(PSP.manual_req_num_type, 'NUMERIC',to_number(PRH.segment1)),
DECODE(PSP.manual_req_num_type, 'ALPHANUMERIC',PRH.segment1)'
,additional_columns
=> NULL --' DESCRIPTION'
);
END IF;
END;
/**************************************************************
-- Creating
Value set for Apprvoed Requisition_Number To
**************************************************************/
/* BEGIN
IF
fnd_flex_val_api.valueset_exists('PO_SRS_APPROVED_REQUISITION_NUMBER') THEN
fnd_flex_val_api.delete_valueset('PO_SRS_APPROVED_REQUISITION_NUMBER');
END
IF;
END;
BEGIN
IF NOT
fnd_flex_val_api.valueset_exists('PO_SRS_APPROVED_REQUISITION_NUMBER') THEN
fnd_flex_val_api.set_session_mode('customer_data');
fnd_flex_val_api.create_valueset_table(
value_set_name
=> 'PO_SRS_APPROVED_REQUISITION_NUMBER'
,description
=> 'Approved Requisition Number'
,security_available
=> 'N'
,enable_longlist
=> 'N'
,format_type
=> 'Char'
,maximum_size
=> 20
,precision
=> NULL
,numbers_only
=> 'N'
,uppercase_only
=> 'N'
,right_justify_zero_fill
=> 'N'
,min_value
=> NULL
,max_value
=> NULL
,table_application
=> 'Purchasing'
,table_name
=> 'PO_REQUISITION_HEADERS PRH,PO_SYSTEM_PARAMETERS PSP'
,allow_parent_values
=> 'N'
,value_column_name
=> 'PRH.SEGMENT1'
,value_column_type
=> 'Char'
,value_column_size
=> 20
,meaning_column_name
=> NULL
,meaning_column_type
=> NULL
,meaning_column_size
=> NULL
,id_column_name => 'PRH.SEGMENT1'
,id_column_type
=> 'Char
,id_column_size
=> 20
,where_order_by =>
'where (nvl(PRH.authorization_status,'INCOMPLETE')='APPROVED')
ORDER BY DECODE(PSP.manual_req_num_type,
'NUMERIC',to_number(PRH.segment1)),
DECODE(PSP.manual_req_num_type,
'ALPHANUMERIC',PRH.segment1)'
,additional_columns
=> NULL --' DESCRIPTION'
);
END IF;
END; */
/*****************************************************
-- Creating
Value set for Quantity Precision
*****************************************************/
BEGIN
IF
fnd_flex_val_api.valueset_exists('INV_SRS_PRECISION') THEN
fnd_flex_val_api.delete_valueset('INV_SRS_PRECISION');
END
IF;
END;
BEGIN
IF NOT
fnd_flex_val_api.valueset_exists('INV_SRS_PRECISION') THEN
fnd_flex_val_api.set_session_mode('customer_data');
fnd_flex_val_api.create_valueset_table(
value_set_name
=> 'INV_SRS_PRECISION'
,description
=> 'Dynamic Precision Option'
,security_available
=> 'N'
,enable_longlist
=> 'N'
,format_type => 'Number'
,maximum_size
=> 2
,precision
=> NULL
,numbers_only
=> 'Y'
,uppercase_only
=> 'N'
,right_justify_zero_fill
=> 'N'
,min_value
=> 0
,max_value
=> 13
,table_application
=> 'Inventory'
,table_name
=> 'MFG_LOOKUPS'
,allow_parent_values
=> 'N'
,value_column_name
=> 'LOOKUP_CODE'
,value_column_type => 'Number'
,value_column_size
=> 2
,meaning_column_name
=> 'MEANING'
,meaning_column_type
=> 'Varchar2'
,meaning_column_size
=> 80
,id_column_name
=> 'LOOKUP_CODE'
,id_column_type
=> 'Number'
,id_column_size => 22
,where_order_by
=> 'WHERE LOOKUP_TYPE = 'INV_SRS_PRECISION'and enabled_flag = 'Y'
order by LOOKUP_CODE'
,additional_columns => NULL --' DESCRIPTION'
);
END IF;
END;
/******************************************************************
--Create
concurrent program parameters P_REQ_NO_FROM
******************************************************************/
BEGIN
FND_PROGRAM.PARAMETER(
program_short_name
=> 'POXPREQR',
application
=> application_name_in,
sequence => 10,
parameter
=> 'P_REQ_NO_FROM',
description
=> 'Req Numbers From',
enabled
=> 'Y',
value_set =>
'PO_SRS_APPROVED_REQUISITION_NUMBER',
default_type
=> NULL,
default_value
=> NULL,
required
=> 'N',
enable_security => 'N',
range
=> NULL,
display
=> 'Y',
display_size
=> 20,
description_size => 50,
concatenated_description_size
=> 25,
prompt
=> 'P_REQ_NO_FROM',
token
=> 'P_REQ_NO_FROM'
);
END;
/******************************************************************
--Create
concurrent program parameters P_REQ_NO_TO
******************************************************************/
BEGIN
FND_PROGRAM.PARAMETER(
program_short_name => 'POXPREQR',
application
=> application_name_in,
sequence
=> 20,
parameter
=> 'P_REQ_NO_TO',
description => 'Req Numbers To',
enabled
=> 'Y',
value_set
=> 'PO_SRS_APPROVED_REQUISITION_NUMBER',
default_type
=> NULL,
default_value
=> NULL,
required
=> 'N',
enable_security
=> 'N',
range
=> NULL,
display => 'Y',
display_size
=> 20,
description_size
=> 50,
concatenated_description_size
=> 25,
prompt
=> 'P_REQ_NO_TO',
token
=> 'P_REQ_NO_TO'
);
END;
/******************************************************************
--Create
concurrent program parameters P_QTY_PRECISION
*****************************************************************/
BEGIN
FND_PROGRAM.PARAMETER(
program_short_name
=> 'POXPREQR',
application
=> application_name_in,
sequence => 30,
parameter
=> 'P_QTY_PRECISION',
description
=> 'Dynamic Quantity Precision',
enabled
=> 'Y',
value_set =>
'INV_SRS_PRECISION',
default_type
=> 'Profile',
default_value
=> 'REPORT_QUANTITY_PRECISION',
required => 'Y',
enable_security
=> 'N',
range
=> NULL,
display
=> 'Y',
display_size
=> 2,
description_size
=> 50,
concatenated_description_size
=> 25,
prompt
=> 'P_QTY_PRECISION',
token
=> 'P_QTY_PRECISION'
);
END;
/****************************************************************
-- Add to the
request group
****************************************************************/
BEGIN
fnd_program.add_to_group (
program_short_name =>
'POXPREQR',
program_application =>
application_name_in,
request_group =>
request_group_name_in,
group_application =>
group_application_name_in
);
END;
commit;
END;
/
--exit;
Hi Frnd..This is Rajesh...I want to know what is the session_mode in value set creation..Can u explain it in detail..I was getting problems when creating value set through backend...
ReplyDeleteThanks
Rajesh P