PO - Cancel Request via PL / SQL

Perguntas relacionadas a questões técnicas do Oracle EBS. Criação de Concorrentes, Value Sets, Alerts, Forms Personalizations, Configurações, etc
Post Reply
Msfabris
Rank: Programador Pleno
Rank: Programador Pleno
Posts: 30
Joined: Wed, 17 Feb 2010 8:02 am
Location: Criciuma SC

Good morning


Is it possible to cancel request (internal or purchase) via PL / SQL?

OBS: Oracle EBS 11.5.9



-----
Marcel
User avatar
madmax
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 293
Joined: Wed, 13 Dec 2006 5:02 pm
Location: São Paulo
Contact:
________________________________
Douglas - Madmax.

Hello Marcel.

Face I do not advise to do this via PL / SQL and then follow the PO process, but if I'm not mistaken there is a PACKAGE PO_DOCUMENT_CONTROL_PUB.CONTROL_DOCUM that you can use to cancel.

follows Example:




If you need querys about po you can access my blog rsrsrs.
http://especialistaoracleebs.blogspot.c ... -blog.html
I hope I have helped.

ABS ..
Msfabris
Rank: Programador Pleno
Rank: Programador Pleno
Posts: 30
Joined: Wed, 17 Feb 2010 8:02 am
Location: Criciuma SC

Douglas, thank you for your contribution.


I only have a question ... In this example, DOC_ID and DOC_NUM are referring to purchase order or request?




Marcel
User avatar
madmax
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 293
Joined: Wed, 13 Dec 2006 5:02 pm
Location: São Paulo
Contact:
________________________________
Douglas - Madmax.

Marcel.

Select all

Doc_id = PO_HEADERS_ALL.PO_HEADER_ID 
 
Doc_Num = PO_HEADERS_ALL.Segment1
ie the purchase order.

But searching better we have an API to

Select all

po_reqs_control_sv.update_reqs_status 
that can be used to close or cancel requests.

We pass parameters as requisition_header_id, requisition_line_id, Preparer_id, document_type_code
type_lookup_code, req_control_action, Req_control_reason and different parameter to the API standard.


1 - PO - Sample script to cancel PR Using API

Select all

 
 
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; 
 
 
2 - Script to finally close PR using API.SQL

Select all

 
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;  
I hope I have helped.

ABS.
Msfabris
Rank: Programador Pleno
Rank: Programador Pleno
Posts: 30
Joined: Wed, 17 Feb 2010 8:02 am
Location: Criciuma SC

Douglas, it worked perfectly ... Thank you very much!


Marcel
User avatar
madmax
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 293
Joined: Wed, 13 Dec 2006 5:02 pm
Location: São Paulo
Contact:
________________________________
Douglas - Madmax.

What good Marcel.

Needing we are aí.

ABS ..
Msfabris
Rank: Programador Pleno
Rank: Programador Pleno
Posts: 30
Joined: Wed, 17 Feb 2010 8:02 am
Location: Criciuma SC

Good morning

I used the code below to cancel, but the same is not reversing the commitment. I already tried to change the parameter x_encumbrance_flag to n and y, but in none of the cases the commitment was reversed.

Select all

po_reqs_control_sv.update_reqs_status 
( X_req_header_id        => :REQLINES.requisition_header_id 
, X_req_line_id          => w_req_line_id 
, X_agent_id             => :REQHEAD.preparer_id 
, X_req_doc_type         => 'REQUISITION' 
, X_req_doc_subtype      => :REQHEAD.type_lookup_code 
, X_req_control_action   => 'CANCEL' 
, X_req_control_reason   => 'CANCELLED BY API' 
, X_req_action_date      => SYSDATE 
, X_encumbrance_flag     => 'Y' 
, X_oe_installed_flag    => 'Y' 
, X_req_control_error_rc => V_RETURN 
);
Does anyone have the code to reverse the commitment of a request?



Marcel
User avatar
madmax
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 293
Joined: Wed, 13 Dec 2006 5:02 pm
Location: São Paulo
Contact:
________________________________
Douglas - Madmax.

Marcel,

could explain better what would commit a request?

ABS.
Msfabris
Rank: Programador Pleno
Rank: Programador Pleno
Posts: 30
Joined: Wed, 17 Feb 2010 8:02 am
Location: Criciuma SC

We use the budget part of GL.

It works so, each accounting combination (or a set of combinations) has an X value to spend in the period (GL period). When a request is made, the system reserves the amount of the budget corresponding to the requisition account, this is called commitment. Then, when it is given in the note, this value comes out of the commitment and goes to the realized.
It turns out that when a requisition is canceled using the procedure described in the other post, it is not by arranging this committed value.
Example:
Account 1.2.2 MAI-2010 Budget R $ 100
Create a requisition with charge in account 1.2.2 of R $ 60
- Budget $ 100 Espailed R $ 60 Referred to R $ 100 available R $ 100
At this time, if you cancel the requisition should be like this
Available R $ 100
But is maintaining the value that had been engaged
- Budget $ 100 Established R $ 60 (!) R $ 0 available R $ 40

That is, the value available is inconsistent, not letting another requisition be made to replace the one that has been canceled.



If he still had some doubt about the process, he speaks there.

Marcel
User avatar
madmax
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 293
Joined: Wed, 13 Dec 2006 5:02 pm
Location: São Paulo
Contact:
________________________________
Douglas - Madmax.

Marcel.

Surely the API will not extort in the commitment because it must be other tables other than those:

Select all

apps.po_requisition_headers_all  
apps.po_requisition_lines_all  
apps.po_document_types_all 
I think you should customize the PO_REQS_CONTROL_SV To take the value of the requisition commitment and abata in the total amount where and saved.

ABS.
Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 1 guest