Good morning
Is it possible to cancel request (internal or purchase) via PL / SQL?
OBS: Oracle EBS 11.5.9
-----
Marcel
PO - Cancel Request via PL / SQL
- madmax
- Rank: DBA Pleno
- Posts: 293
- Joined: Wed, 13 Dec 2006 5:02 pm
- Location: São Paulo
- Contact:
________________________________
Douglas - Madmax.
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 ..
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 ..
- madmax
- Rank: DBA Pleno
- Posts: 293
- Joined: Wed, 13 Dec 2006 5:02 pm
- Location: São Paulo
- Contact:
________________________________
Douglas - Madmax.
Douglas - Madmax.
Marcel.
ie the purchase order.
But searching better we have an API to 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
2 - Script to finally close PR using API.SQL
I hope I have helped.
ABS.
Doc_id = PO_HEADERS_ALL.PO_HEADER_ID
Doc_Num = PO_HEADERS_ALL.Segment1
But searching better we have an API to
po_reqs_control_sv.update_reqs_status
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
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;
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;
ABS.
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.
Does anyone have the code to reverse the commitment of a request?
Marcel
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.
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
);
Marcel
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
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
- madmax
- Rank: DBA Pleno
- Posts: 293
- Joined: Wed, 13 Dec 2006 5:02 pm
- Location: São Paulo
- Contact:
________________________________
Douglas - Madmax.
Douglas - Madmax.
Marcel.
Surely the API will not extort in the commitment because it must be other tables other than those:
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.
Surely the API will not extort in the commitment because it must be other tables other than those:
apps.po_requisition_headers_all
apps.po_requisition_lines_all
apps.po_document_types_all
ABS.
-
- Information
-
Who is online
Users browsing this forum: No registered users and 1 guest