Do we have any API to cancel or finally close a Purchase Requisition:

Yes, we do have one API & that is po_reqs_control_sv.update_reqs_status which can be used for finally closing or cancelling the requisition. We need to pass the parameters like requisition_header_id, requisition_line_id, Preparer_id, document_type_code,
type_lookup_code, req_control_action, Req_control_reason and the other default parameter to the API.

— R12 – PO – Sample Script to Cancel PR Using API

DECLARE

X_req_control_error_rc VARCHAR2 (500);
l_org_id NUMBER := 308; — Enter the Operating_Unit Here
cnt number := 0;

CURSOR C_REQ_CANCEL is

SELECT
prh.segment1 requisition_num,
prh.requisition_header_id,
prh.org_id,
prl.requisition_line_id,
prh.preparer_id,
prh.type_lookup_code,
pdt.document_type_code,
prh.authorization_status,
prl.line_location_id
FROM
apps.po_requisition_headers_all prh,
apps.po_requisition_lines_all prl,
apps.po_document_types_all pdt
WHERE 1 = 1
AND prh.org_id = l_org_id
AND pdt.document_type_code = ‘REQUISITION’
AND prh.authorization_status = ‘APPROVED’
AND prl.line_location_id is null
AND prh.requisition_header_id = prl.requisition_header_id
AND prh.type_lookup_code = pdt.document_subtype
AND prh.org_id = pdt.org_id
AND prh.segment1 = ‘21170000909’; — Enter The Requisition Number

BEGIN

fnd_global.apps_initialize (user_id => 2083,
resp_id => 20707,
resp_appl_id => 201);

mo_global.init (‘PO’);
mo_global.set_policy_context (‘S’, l_org_id);

FOR i IN C_REQ_CANCEL

LOOP

dbms_output.put_line (‘ Calling po_reqs_control_sv.update_reqs_status to cancel the Requisition=>’ i.requisition_num);
dbms_output.put_line (‘======================================================’);

po_reqs_control_sv.update_reqs_status(
X_req_header_id => i.requisition_header_id
, X_req_line_id => i.requisition_line_id
, X_agent_id => i.preparer_id
, X_req_doc_type => i.document_type_code
, X_req_doc_subtype => i.type_lookup_code
, X_req_control_action => ‘CANCEL’
, X_req_control_reason => ‘CANCELLED BY API’
, X_req_action_date => SYSDATE
, X_encumbrance_flag => ‘N’
, X_oe_installed_flag => ‘Y’
, X_req_control_error_rc => X_req_control_error_rc);

DBMS_OUTPUT.PUT_LINE ( ‘Status Found:=> ‘ X_req_control_error_rc);
DBMS_OUTPUT.PUT_LINE (‘Requisition Number cancelled is :=>’ i.Requisition_num);

cnt := cnt+1;

END LOOP;

DBMS_OUTPUT.PUT_LINE(‘Count is :=>’ cnt);

END;

— R12 – PO – Script to Finally Close PR Using API.sql

DECLARE

X_req_control_error_rc VARCHAR2 (500);
l_org_id NUMBER := 308; — Enter the Operating_Unit Here
cnt number := 0;

CURSOR C_REQ_CLOSE is

SELECT
prh.segment1 requisition_num,
prh.requisition_header_id,
prh.org_id,
prl.requisition_line_id,
prh.preparer_id,
prh.type_lookup_code,
pdt.document_type_code,
prh.authorization_status,
prh.closed_code
FROM
apps.po_requisition_headers_all prh,
apps.po_requisition_lines_all prl,
apps.po_document_types_all pdt
WHERE 1 = 1
AND prh.org_id = l_org_id
AND pdt.document_type_code = ‘REQUISITION’
AND prh.authorization_status = ‘APPROVED’
AND prl.line_location_id is null
AND prh.requisition_header_id = prl.requisition_header_id
AND prh.type_lookup_code = pdt.document_subtype
AND prh.org_id = pdt.org_id
AND prh.segment1 = ‘21170002264’; — Enter The Requisition Number

BEGIN

fnd_global.apps_initialize (user_id => 2083,
resp_id => 20707,
resp_appl_id => 201);

mo_global.init (‘PO’);
mo_global.set_policy_context (‘S’, l_org_id);

FOR i IN C_REQ_CLOSE

LOOP

DBMS_OUTPUT.PUT_LINE (‘Calling po_reqs_control_sv.update_reqs_status to Finally Close Requisition=>’ i.requisition_num);

DBMS_OUTPUT.PUT_LINE (‘=======================================================’);

po_reqs_control_sv.update_reqs_status(
X_req_header_id => i.requisition_header_id
, X_req_line_id => i.requisition_line_id
, X_agent_id => i.preparer_id
, X_req_doc_type => i.document_type_code
, X_req_doc_subtype => i.type_lookup_code
, X_req_control_action => ‘FINALLY CLOSE’
, X_req_control_reason => ‘FINALLY CLOSED BY API’
, X_req_action_date => SYSDATE
, X_encumbrance_flag => ‘N’
, X_oe_installed_flag => ‘Y’
, X_req_control_error_rc => X_req_control_error_rc);

DBMS_OUTPUT.PUT_LINE ( ‘Status Found: ‘ X_req_control_error_rc);

DBMS_OUTPUT.PUT_LINE (‘Requisition Number which is Finally Closed =>’ i.Requisition_num);

cnt := cnt+1;

END LOOP;

DBMS_OUTPUT.PUT_LINE(‘Count is :=>’ cnt);

END;

select
request_id, parent_request_id,
fcpt.user_concurrent_program_name Request_Name,
fcpt.user_concurrent_program_name program_name,
DECODE(fcr.phase_code,’C’,’Completed’,’I’, ‘Incactive’,’P’,’Pending’,’R’,’Running’) phase,
DECODE(fcr.status_code, ‘D’,’Cancelled’,’U’,’Disabled’,’E’,’Error’,’M’,’No Manager’,’R’,’Normal’,’I’, ‘Normal’,
‘C’,’Normal’,’H’,’On Hold’,’W’,’Paused’,’B’,’Resuming’,’P’,’Scheduled’,’Q’,’Standby’,’S’,
‘Suspended’,’X’,’Terminated’,’T’,’Terminating’,’A’,’Waiting’,’Z’,’Waiting’,’G’,’Warning’,’N/A’) status,
round((fcr.actual_completion_date – fcr.actual_start_date),3) * 1440 as Run_Time,
round(avg(round(to_number(actual_start_date – fcr.requested_start_date),3) * 1440),2) wait_time,
fu.User_Name Requestor,
fcr.argument_text parameters,
to_char (fcr.requested_start_date, ‘MM/DD HH24:mi:SS’) requested_start,
to_char(actual_start_date, ‘MM/DD/YY HH24:mi:SS’) ACT_START,
to_char(actual_completion_date, ‘MM/DD/YY HH24:mi:SS’) ACT_COMP,
fcr.completion_text
From
apps.fnd_concurrent_requests fcr,
apps.fnd_concurrent_programs fcp,
apps.fnd_concurrent_programs_tl fcpt,
apps.fnd_user fu
Where 1=1
— and fu.user_name = ‘JMOHANTY’
— and fcr.request_id = 1565261
— and fcpt.user_concurrent_program_name = ‘Autoinvoice Import Program’
and fcr.concurrent_program_id = fcp.concurrent_program_id
and fcp.concurrent_program_id = fcpt.concurrent_program_id
and fcr.program_application_id = fcp.application_id
and fcp.application_id = fcpt.application_id
and fcr.requested_by = fu.user_id
and fcpt.language = ‘US’
and fcr.actual_start_date like sysdate
— and fcr.phase_code = ‘C’
— and hold_flag = ‘Y’
— and fcr.status_code = ‘C’
GROUP BY
request_id, parent_request_id, fcpt.user_concurrent_program_name,
fcr.requested_start_date, fu.User_Name, fcr.argument_text,
fcr.actual_completion_date, fcr.actual_start_date,
fcr.phase_code, fcr.status_code,fcr.resubmit_interval,fcr.completion_text,
fcr.resubmit_interval,fcr.resubmit_interval_unit_code,fcr.description
Order by 1 desc
Some times we don’t have the access to add the responsibility to the user using the the Create User form. So for this Oracle is having one API fnd_user_pkg.addresp which can do the job without using the Create User Form.

— R12 – FND – Script to add responsibility using fnd_user_pkg with validation

DECLARE

v_user_name VARCHAR2 (10) := ‘&Enter_User_Name’;
v_resp_name VARCHAR2 (50) := ‘&Enter_Existing_Responsibility_Name’;
v_req_resp_name VARCHAR2 (50) := ‘&Enter_required_Responsibility_Name’;
v_user_id NUMBER (10);
v_resp_id NUMBER (10);
v_appl_id NUMBER (10);
v_count NUMBER (10);
v_resp_app VARCHAR2 (50);
v_resp_key VARCHAR2 (50);
v_description VARCHAR2 (100);
RESULT BOOLEAN;

BEGIN

SELECT fu.user_id, frt.responsibility_id, frt.application_id
INTO v_user_id, v_resp_id, v_appl_id
FROM fnd_user fu,
fnd_responsibility_tl frt,
fnd_user_resp_groups_direct furgd
WHERE fu.user_id = furgd.user_id
AND frt.responsibility_id = furgd.responsibility_id
AND frt.LANGUAGE = ‘US’
AND fu.user_name = v_user_name
AND frt.responsibility_name = v_resp_name;
fnd_global.apps_initialize (v_user_id, v_resp_id, v_appl_id);

SELECT COUNT (*)
INTO v_count
FROM fnd_user fu,
fnd_responsibility_tl frt,
fnd_user_resp_groups_direct furgd
WHERE fu.user_id = furgd.user_id
AND frt.responsibility_id = furgd.responsibility_id
AND frt.LANGUAGE = ‘US’
AND fu.user_name = v_user_name
AND frt.responsibility_name = v_req_resp_name;

IF v_count = 0 THEN

SELECT fa.application_short_name, frv.responsibility_key,
frv.description
INTO v_resp_app, v_resp_key,
v_description
FROM fnd_responsibility_vl frv, fnd_application fa
WHERE frv.application_id = fa.application_id
AND frv.responsibility_name = v_req_resp_name;

fnd_user_pkg.addresp (
username => v_user_name,
resp_app => v_resp_app,
resp_key => v_resp_key,
security_group => ‘STANDARD’,
description => v_description,
start_date => SYSDATE – 1,
end_date => NULL);

RESULT :=
fnd_profile.SAVE (x_name => ‘APPS_SSO_LOCAL_LOGIN’,
x_value => ‘BOTH’,
x_level_name => ‘USER’,
x_level_value => v_user_id
);

RESULT :=
fnd_profile.SAVE (x_name => ‘FND_CUSTOM_OA_DEFINTION’,
x_value => ‘Y’,
x_level_name => ‘USER’,
x_level_value => v_user_id
);

RESULT :=
fnd_profile.SAVE (x_name => ‘FND_DIAGNOSTICS’,
x_value => ‘Y’,
x_level_name => ‘USER’,
x_level_value => v_user_id
);

RESULT :=
fnd_profile.SAVE (x_name => ‘DIAGNOSTICS’,
x_value => ‘Y’,
x_level_name => ‘USER’,
x_level_value => v_user_id
);

RESULT :=
fnd_profile.SAVE (x_name => ‘FND_HIDE_DIAGNOSTICS’,
x_value => ‘N’,
x_level_name => ‘USER’,
x_level_value => v_user_id
);

DBMS_OUTPUT.put_line ( ‘The responsibility added to the user ‘
v_user_name
‘ is ‘
v_req_resp_name);

COMMIT;

ELSE

DBMS_OUTPUT.put_line
(‘The responsibility has already been added to the user’);

END IF;

END;