set serveroutput on;
-- After the API completes, do not forget to commit if the result is 1
-- and rollback if the result is 0.
DECLARE
l_result NUMBER;
l_api_errors PO_API_ERRORS_REC_TYPE;
BEGIN
-- This needs to be changed according to your environment setup.
FND_GLOBAL.apps_initialize ( user_id => 1318,
resp_id => 50578,
resp_appl_id => 201 );
-- Record an acceptance of Y for PO 1261.
l_result := PO_CHANGE_API1_S.record_acceptance(
x_po_number => 1261,
x_release_number => null,
x_revision_number => 0,
x_action => 'NEW',
x_action_date => null,
x_employee_id => 588,
x_accepted_flag => 'Y',
x_acceptance_lookup_code => 'On Schedule',
x_note => 'All valid',
x_interface_type => 'APITEST',
x_transaction_id => null,
version => '1.0');
IF (l_result <> 1) THEN
-- Handle the errors in the PO_INTERFACE_ERRORS table.
END IF;
-- Change the quantity to 5 on line 1, shipment 1 of PO 1263.
-- we are launching approval proceess it will be with new revision number after update
-- using this API we can only change price,qunatity,promise_date
l_result := PO_CHANGE_API1_S.update_po (
x_po_number => 1263,
x_release_number => 1,
x_revision_number => 1,
x_line_number => 1,
x_shipment_number => 1,
new_quantity => 5,
new_price => NULL,
new_promised_date => NULL,
launch_approvals_flag =>'Y',
update_source => NULL,
version => '1.0',
x_override_date => NULL,
x_api_errors => l_api_errors,
p_buyer_name => null
);
IF (l_result <> 1) THEN
-- Display the errors
FOR i IN 1..l_api_errors.message_text.COUNT LOOP
dbms_output.put_line ( l_api_errors.message_text(i) );
END LOOP;
END IF;
END;
|
|
|
1 comments
Shipment line cancel of PO
===============================
Cancel whole PO API
====================
===============================
DECLARE
l_return_status VARCHAR2(1);
BEGIN
fnd_global.apps_initialize(1053,50578,201);
-- mo_global.init('PO'); -- need for R12
--call the Cancel API for PO
PO_Document_Control_PUB.control_document (
1.0, -- p_api_version
FND_API.G_TRUE, -- p_init_msg_list
FND_API.G_TRUE, -- p_commit
l_return_status,-- x_return_status
'PO', -- p_doc_type
'STANDARD', -- p_doc_subtype
null, -- p_doc_id
'23975', -- p_doc_num
null, -- p_release_id
null, -- p_release_num
null, -- p_doc_line_id
'1', -- p_doc_line_num
null, -- p_doc_line_loc_id
'3', -- p_doc_shipment_num
'CANCEL', -- p_action
SYSDATE, -- p_action_date
null, -- p_cancel_reason
'N', -- p_cancel_reqs_flag
null, -- p_print_flag
null ); -- p_note_to_vendor
-- Get any messages returned by the Cancel API
FOR i IN 1..FND_MSG_PUB.count_msg
LOOP
DBMS_OUTPUT.put_line(FND_MSG_PUB.Get(p_msg_index => i,
p_encoded => 'F'));
END LOOP;
END; Cancel whole PO API
====================
DECLARE
l_return_status VARCHAR2(1);
BEGIN
fnd_global.apps_initialize(1053,50578,201);
-- mo_global.init('PO'); -- need for R12
--call the Cancel API for PO
PO_Document_Control_PUB.control_document (
1.0, -- p_api_version
FND_API.G_TRUE, -- p_init_msg_list
FND_API.G_TRUE, -- p_commit
l_return_status,-- x_return_status
'PO', -- p_doc_type
'STANDARD', -- p_doc_subtype
null, -- p_doc_id
'23975', -- p_doc_num
null, -- p_release_id
null, -- p_release_num
null, -- p_doc_line_id
null, -- p_doc_line_num
null, -- p_doc_line_loc_id
null, -- p_doc_shipment_num
'CANCEL', -- p_action
SYSDATE, -- p_action_date
null, -- p_cancel_reason
'N', -- p_cancel_reqs_flag
null, -- p_print_flag
null ); -- p_note_to_vendor
-- Get any messages returned by the Cancel API
FOR i IN 1..FND_MSG_PUB.count_msg
LOOP
DBMS_OUTPUT.put_line(FND_MSG_PUB.Get(p_msg_index => i,
p_encoded => 'F'));
END LOOP;
END; 
|
|
|
0
comments
FND_REQUEST.SUBMIT_REQUEST is an API used to submit a concurrent program in ORACLE Applications.
Parameters:
Application : Short name of the application associated with the concurrent request to be submitted.
Program - Short name of the concurrent program (not the executable) for which the request should be submitted.
Description - Description of the request that is displayed in the Concurrent Requests form (Optional.)
Start_time - Time at which the request should start running, formatted as HH24:MI or HH24:MI:SS (Optional.)
Sub_request - Set to TRUE if the request is submitted from another request and should be treated as a sub-request.
Argument1...100 - Arguments for the concurrent request; up to 100 arguments are permitted. If submitted from Oracle Forms, you must specify all 100 arguments.
Example: Submission of Standard Order Import Program
SET SERVEROUTPUT ON;
DECLARE
v_request_id NUMBER DEFAULT 0;
--Order Import Parameters
p_operating_unit VARCHAR2(20) := NULL;
p_order_source VARCHAR2(20) := 'XYZ';
p_orig_sys_document_ref VARCHAR2(20) := NULL;
p_operation_code VARCHAR2(20) := NULL;
p_validate_only VARCHAR2(20) := 'N';
p_debug_level VARCHAR2(20) := '1';
p_num_instances VARCHAR2(20) := '4';
p_sold_to_org_id VARCHAR2(20) := NULL;
p_sold_to_org VARCHAR2(20) := NULL;
p_change_sequence VARCHAR2(20) := NULL;
p_perf_param VARCHAR2(20) := 'Y';
p_rtrim_data VARCHAR2(20) := 'N';
p_pro_ord_with_null_flag VARCHAR2(20) := 'Y';
p_default_org_id VARCHAR2(20) := '83';
p_validate_desc_flex VARCHAR2(20) := 'N';
-- End of Parameters -----
v_context varchar2(100);
/* When ever we use APPS API’s its always better to APPS initialize context.*/
FUNCTION set_context( i_user_name IN VARCHAR2
,i_resp_name IN VARCHAR2
,i_org_id IN NUMBER)
RETURN VARCHAR2
IS
v_user_id NUMBER;
v_resp_id NUMBER;
v_resp_appl_id NUMBER;
v_lang VARCHAR2(100);
v_session_lang VARCHAR2(100):=fnd_global.current_language;
v_return VARCHAR2(10):='T';
v_nls_lang VARCHAR2(100);
v_org_id NUMBER:=i_org_id;
/* Cursor to get the user id information based on the input user name */
CURSOR cur_user
IS
SELECT user_id
FROM fnd_user
WHERE user_name = i_user_name;
/* Cursor to get the responsibility information */
CURSOR cur_resp
IS
SELECT responsibility_id
,application_id
,language
FROM fnd_responsibility_tl
WHERE responsibility_name = i_resp_name;
/* Cursor to get the nls language information for setting the language context */
CURSOR cur_lang(p_lang_code VARCHAR2)
IS
SELECT nls_language
FROM fnd_languages
WHERE language_code = p_lang_code;
BEGIN
/* To get the user id details */
OPEN cur_user;
FETCH cur_user INTO v_user_id;
IF cur_user%NOTFOUND
THEN
v_return:='F';
END IF; --IF cur_user%NOTFOUND
CLOSE cur_user;
/* To get the responsibility and responsibility application id */
OPEN cur_resp;
FETCH cur_resp INTO v_resp_id, v_resp_appl_id,v_lang;
IF cur_resp%NOTFOUND
THEN
v_return:='F';
END IF; --IF cur_resp%NOTFOUND
CLOSE cur_resp;
/* Setting the oracle applications context for the particular session */
fnd_global.apps_initialize ( user_id => v_user_id
,resp_id => v_resp_id
,resp_appl_id => v_resp_appl_id);
/* Setting the org context for the particular session */
mo_global.set_policy_context('S',v_org_id);
/* setting the nls context for the particular session */
IF v_session_lang != v_lang
THEN
OPEN cur_lang(v_lang);
FETCH cur_lang INTO v_nls_lang;
CLOSE cur_lang;
fnd_global.set_nls_context(v_nls_lang);
END IF; --IF v_session_lang != v_lang
RETURN v_return;
EXCEPTION
WHEN OTHERS THEN
RETURN 'F';
END set_context;
BEGIN
-- Setting the context ----
v_context := set_context('&V_USER_NAME','&V_RESPONSIBILITY',82);
IF v_context = 'F'
THEN
DBMS_OUTPUT.PUT_LINE('Error while setting the context');
END IF;
DBMS_OUTPUT.PUT_LINE('Submit Order Import Concurrent Program');
v_request_id:= FND_REQUEST.SUBMIT_REQUEST (
application => 'ONT'
,program => 'OEOIMP'
,description => 'Order Import'
,start_time => SYSDATE
,sub_request => NULL
,argument1 => p_operating_unit
,argument2 => p_order_source
,argument3 => p_orig_sys_document_ref
,argument4 => p_operation_code
,argument5 => p_validate_only
,argument6 => p_debug_level
,argument7 => p_num_instances
,argument8 => p_sold_to_org_id
,argument9 => p_sold_to_org
,argument10 => p_change_sequence
,argument11 => p_perf_param
,argument12 => p_rtrim_data
,argument13 => p_pro_ord_with_null_flag
,argument14 => p_default_org_id
,argument15 => p_validate_desc_flex
);
/* we have submitted the request and we need to check the status of the request and do any action based on that.so we need to wait for the request
Wait request is provided below.*/
IF request_id = 0 THEN
fnd_file.put_line(fnd_file.log, 'Request Not Submitted.');
ELSE
Arguments (input)
request_id
The request ID of the program to wait on.
interval
Time to wait between checks. This is the number of seconds to sleep.
The default is 60 seconds.
max_wait
The maximum time in seconds to wait for the requests completion.
Arguments (output)
phase
The user friendly request phase from FND_LOOKUPS lookup_type ='CP_PHASE_CODE'.
status
The user friendly request status from FND_LOOKUPS lookup_type ='CP_STATUS_CODE'.
dev_phase
The request phase as a constant string that can be used for program
logic comparisons.
dev_status
The request status as a constant string that can be used for program
logic comparisons.
message
The completion message supplied if the request has completed.
v_interval := 20;--20 sec wait
lc_call_status := fnd_concurrent.wait_for_request(v_request_id, v_interval, v_max_wait, request_phase, request_status, dev_request_phase, dev_request_status, request_status_mesg);
END IF;
IF lc_call_status = TRUE THEN
IF dev_request_phase!= 'Completed' OR
dev_request_status IN ('Cancelled','Error','Terminated') THEN
DBMS_OUTPUT.PUT_LINE('STATUS=JOB FAILED');
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE('WAIT FOR REQUEST FAILED - STATUS UNKNOWN');
DBMS_OUTPUT.PUT_LINE('STATUS=JOB FAILED');
END IF;
COMMIT;
DBMS_OUTPUT.PUT_LINE('Request_id: '||v_request_id);
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||' Error :'||SQLERRM);
END;
Parameters:
Application : Short name of the application associated with the concurrent request to be submitted.
Program - Short name of the concurrent program (not the executable) for which the request should be submitted.
Description - Description of the request that is displayed in the Concurrent Requests form (Optional.)
Start_time - Time at which the request should start running, formatted as HH24:MI or HH24:MI:SS (Optional.)
Sub_request - Set to TRUE if the request is submitted from another request and should be treated as a sub-request.
Argument1...100 - Arguments for the concurrent request; up to 100 arguments are permitted. If submitted from Oracle Forms, you must specify all 100 arguments.
Example: Submission of Standard Order Import Program
SET SERVEROUTPUT ON;
DECLARE
v_request_id NUMBER DEFAULT 0;
--Order Import Parameters
p_operating_unit VARCHAR2(20) := NULL;
p_order_source VARCHAR2(20) := 'XYZ';
p_orig_sys_document_ref VARCHAR2(20) := NULL;
p_operation_code VARCHAR2(20) := NULL;
p_validate_only VARCHAR2(20) := 'N';
p_debug_level VARCHAR2(20) := '1';
p_num_instances VARCHAR2(20) := '4';
p_sold_to_org_id VARCHAR2(20) := NULL;
p_sold_to_org VARCHAR2(20) := NULL;
p_change_sequence VARCHAR2(20) := NULL;
p_perf_param VARCHAR2(20) := 'Y';
p_rtrim_data VARCHAR2(20) := 'N';
p_pro_ord_with_null_flag VARCHAR2(20) := 'Y';
p_default_org_id VARCHAR2(20) := '83';
p_validate_desc_flex VARCHAR2(20) := 'N';
-- End of Parameters -----
v_context varchar2(100);
/* When ever we use APPS API’s its always better to APPS initialize context.*/
FUNCTION set_context( i_user_name IN VARCHAR2
,i_resp_name IN VARCHAR2
,i_org_id IN NUMBER)
RETURN VARCHAR2
IS
v_user_id NUMBER;
v_resp_id NUMBER;
v_resp_appl_id NUMBER;
v_lang VARCHAR2(100);
v_session_lang VARCHAR2(100):=fnd_global.current_language;
v_return VARCHAR2(10):='T';
v_nls_lang VARCHAR2(100);
v_org_id NUMBER:=i_org_id;
/* Cursor to get the user id information based on the input user name */
CURSOR cur_user
IS
SELECT user_id
FROM fnd_user
WHERE user_name = i_user_name;
/* Cursor to get the responsibility information */
CURSOR cur_resp
IS
SELECT responsibility_id
,application_id
,language
FROM fnd_responsibility_tl
WHERE responsibility_name = i_resp_name;
/* Cursor to get the nls language information for setting the language context */
CURSOR cur_lang(p_lang_code VARCHAR2)
IS
SELECT nls_language
FROM fnd_languages
WHERE language_code = p_lang_code;
BEGIN
/* To get the user id details */
OPEN cur_user;
FETCH cur_user INTO v_user_id;
IF cur_user%NOTFOUND
THEN
v_return:='F';
END IF; --IF cur_user%NOTFOUND
CLOSE cur_user;
/* To get the responsibility and responsibility application id */
OPEN cur_resp;
FETCH cur_resp INTO v_resp_id, v_resp_appl_id,v_lang;
IF cur_resp%NOTFOUND
THEN
v_return:='F';
END IF; --IF cur_resp%NOTFOUND
CLOSE cur_resp;
/* Setting the oracle applications context for the particular session */
fnd_global.apps_initialize ( user_id => v_user_id
,resp_id => v_resp_id
,resp_appl_id => v_resp_appl_id);
/* Setting the org context for the particular session */
mo_global.set_policy_context('S',v_org_id);
/* setting the nls context for the particular session */
IF v_session_lang != v_lang
THEN
OPEN cur_lang(v_lang);
FETCH cur_lang INTO v_nls_lang;
CLOSE cur_lang;
fnd_global.set_nls_context(v_nls_lang);
END IF; --IF v_session_lang != v_lang
RETURN v_return;
EXCEPTION
WHEN OTHERS THEN
RETURN 'F';
END set_context;
BEGIN
-- Setting the context ----
v_context := set_context('&V_USER_NAME','&V_RESPONSIBILITY',82);
IF v_context = 'F'
THEN
DBMS_OUTPUT.PUT_LINE('Error while setting the context');
END IF;
DBMS_OUTPUT.PUT_LINE('Submit Order Import Concurrent Program');
v_request_id:= FND_REQUEST.SUBMIT_REQUEST (
application => 'ONT'
,program => 'OEOIMP'
,description => 'Order Import'
,start_time => SYSDATE
,sub_request => NULL
,argument1 => p_operating_unit
,argument2 => p_order_source
,argument3 => p_orig_sys_document_ref
,argument4 => p_operation_code
,argument5 => p_validate_only
,argument6 => p_debug_level
,argument7 => p_num_instances
,argument8 => p_sold_to_org_id
,argument9 => p_sold_to_org
,argument10 => p_change_sequence
,argument11 => p_perf_param
,argument12 => p_rtrim_data
,argument13 => p_pro_ord_with_null_flag
,argument14 => p_default_org_id
,argument15 => p_validate_desc_flex
);
/* we have submitted the request and we need to check the status of the request and do any action based on that.so we need to wait for the request
Wait request is provided below.*/
IF request_id = 0 THEN
fnd_file.put_line(fnd_file.log, 'Request Not Submitted.');
ELSE
Arguments (input)
request_id
The request ID of the program to wait on.
interval
Time to wait between checks. This is the number of seconds to sleep.
The default is 60 seconds.
max_wait
The maximum time in seconds to wait for the requests completion.
Arguments (output)
phase
The user friendly request phase from FND_LOOKUPS lookup_type ='CP_PHASE_CODE'.
status
The user friendly request status from FND_LOOKUPS lookup_type ='CP_STATUS_CODE'.
dev_phase
The request phase as a constant string that can be used for program
logic comparisons.
dev_status
The request status as a constant string that can be used for program
logic comparisons.
message
The completion message supplied if the request has completed.
v_interval := 20;--20 sec wait
lc_call_status := fnd_concurrent.wait_for_request(v_request_id, v_interval, v_max_wait, request_phase, request_status, dev_request_phase, dev_request_status, request_status_mesg);
END IF;
IF lc_call_status = TRUE THEN
IF dev_request_phase!= 'Completed' OR
dev_request_status IN ('Cancelled','Error','Terminated') THEN
DBMS_OUTPUT.PUT_LINE('STATUS=JOB FAILED');
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE('WAIT FOR REQUEST FAILED - STATUS UNKNOWN');
DBMS_OUTPUT.PUT_LINE('STATUS=JOB FAILED');
END IF;
COMMIT;
DBMS_OUTPUT.PUT_LINE('Request_id: '||v_request_id);
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||' Error :'||SQLERRM);
END;
Subscribe to:
Posts (Atom)
