, , , , , , , , ,

Cancelling PO Using API PO_Document_Control_PUB.control_document

How to use the PO Cancel API PO_Document_Control_PUB.control_document ?

  • The PL/SQL procedure, PO_Document_Control_PUB.control_document , provides the ability to cancel Oracle Purchasing documents directly through an API.
  • The API will perform all of the same processing that would be done if a cancellation was requested through the PO Summary Control Window.
  • Prior to calling the API we should set our global context to reflect the application, user and responsibility used to perform the cancel action. If we do not set this context, the API will not be able to identify or update your data.

–R12 – PO – Sample Script to cancel PO using po_document_control_pub API.sql

DECLARE

l_return_status VARCHAR2 (10);

CURSOR C_PO_CANCEL is

SELECT pha.po_header_id,
pha.org_id,
pha.segment1 po_number,
pha.type_lookup_code,
pha.cancel_flag,
pha.closed_code
FROM po_headers_all pha
WHERE 1=1
AND pha.segment1 = ‘376729’ — Enter The Purchase Order Number
AND nvl(pha.closed_code,’OPEN’) = ‘OPEN’
AND nvl(pha.cancel_flag, ‘N’) = ‘N’
AND approved_flag = ‘Y’;

BEGIN

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

FOR i IN c_po_cancel

LOOP

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

DBMS_OUTPUT.PUT_LINE (‘Calling API PO_DOCUMENT_CONTROL_PUB.CONTROL_DOCUMENT For Cancelling Documents’);

po_document_control_pub.control_document
(p_api_version => 1.0, — p_api_version
p_init_msg_list => fnd_api.g_true, — p_init_msg_list
p_commit => fnd_api.g_true, — p_commit
x_return_status => l_return_status, — x_return_status
p_doc_type => ‘PO’, — p_doc_type
p_doc_subtype => ‘STANDARD’, — p_doc_subtype
p_doc_id => i.po_header_id, — p_doc_id
p_doc_num => NULL, — p_doc_num
p_release_id => NULL, — p_release_id
p_release_num => NULL, — p_release_num
p_doc_line_id => NULL, — p_doc_line_id
p_doc_line_num => NULL, — p_doc_line_num
p_doc_line_loc_id => NULL, — p_doc_line_loc_id
p_doc_shipment_num => NULL, — p_doc_shipment_num
p_action => ‘CANCEL’, — p_action
p_action_date => SYSDATE, — p_action_date
p_cancel_reason => NULL, — p_cancel_reason
p_cancel_reqs_flag => ‘N’, — p_cancel_reqs_flag
p_print_flag => NULL, — p_print_flag
p_note_to_vendor => NULL, — p_note_to_vendor
p_use_gldate =>NULL ,
p_org_id => i.org_id
);

COMMIT;

DBMS_OUTPUT.PUT_LINE(‘The Return Status of the API is => ‘ l_return_status);
If l_return_status = ‘S’ Then

DBMS_OUTPUT.PUT_LINE(‘The Purchase Order Which is Cancelled Now => ‘ i.po_number);
Else

DBMS_OUTPUT.PUT_LINE(‘The Purchase Order =>’ i.po_number ‘Failed to cancel Due To Following Reason’);

— Get any messages returned by the Cancel API

FOR j IN 1 .. fnd_msg_pub.count_msg
LOOP
DBMS_OUTPUT.put_line (fnd_msg_pub.get
(p_msg_index => j,
p_encoded => ‘F’));
END LOOP;
END IF;
END LOOP;
END;

/by
, , , , , , ,

API for Closing/Finally Closing PO Using po_actions.close_po

Do we have any API for Finally Closing PO??
Yes, we are having an API for closing or finally closing the POs. In the API, there is a parameter “p_action” which we need to set as either CLOSE (if we want to close the PO) or FINALLY CLOSE (If we want to Finally Close) the PO. Another Parameter which needs to set properly is “p_auto_close”. This parameter should be set to ‘N’.
— R12 – PO – Script to Close / Finally Close PO using PO_ACTIONS CLOSE_PO API.sql

DECLARE

x_action constant varchar2(20) := ‘FINALLY CLOSE’; — Change this parameter as per requirement
x_calling_mode constant varchar2(2) := ‘PO’;
x_conc_flag constant varchar2(1) := ‘N’;
x_return_code_h varchar2(100);
x_auto_close constant varchar2(1) := ‘N’;
x_origin_doc_id number;
x_returned boolean;

CURSOR c_po_details IS

SELECT
pha.po_header_id,
pha.org_id,
pha.segment1,
pha.agent_id,
pdt.document_subtype,
pdt.document_type_code,
pha.closed_code,
pha.closed_date
FROM apps.po_headers_all pha, apps.po_document_types_all pdt
WHERE pha.type_lookup_code = pdt.document_subtype
AND pha.org_id = pdt.org_id
AND pdt.document_type_code = ‘PO’
AND authorization_status = ‘APPROVED’
AND pha.closed_code <> ‘FINALLY CLOSED’
AND segment1 = ‘379329’; — Enter the PO Number if one PO needs to be finally closed/Closed

begin

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

for po_head in c_po_details

LOOP

mo_global.init (po_head.document_type_code);
mo_global.set_policy_context (‘S’, po_head.org_id);

DBMS_OUTPUT.PUT_LINE (‘Calling PO_Actions.close_po for Closing/Finally Closing PO =>’ po_head.segment1);

x_returned :=
po_actions.close_po(
p_docid => po_head.po_header_id,
p_doctyp => po_head.document_type_code,
p_docsubtyp => po_head.document_subtype,
p_lineid => NULL,
p_shipid => NULL,
p_action => x_action,
p_reason => NULL,
p_calling_mode => x_calling_mode,
p_conc_flag => x_conc_flag,
p_return_code => x_return_code_h,
p_auto_close => x_auto_close,
p_action_date => SYSDATE,
p_origin_doc_id => NULL);

IF x_returned = TRUE THEN

DBMS_OUTPUT.PUT_LINE (‘Purchase Order which just got Closed/Finally Closed is ‘ po_head.segment1);

COMMIT;

ELSE

DBMS_OUTPUT.PUT_LINE (‘API Failed to Close/Finally Close the Purchase Order’);

END IF;

END LOOP;

END;

/by
, , , , , , , , ,

API To Cancel or Finally Close Requisition

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;

/by
, , , , , , ,

Script to Get Concurrent Request Details

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
/by
, , , , , , , , ,

Adding responsibility using fnd_user_pkg

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;

/by