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;

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;


Unprocessed Transaction Messages While Closing the Inventory Accounting Period

While closing the inventory accounting periods, If there are unprocessed transactions, then one of the following messages appears:

Pending receiving transactions for this period
When you use Purchasing, this message indicates you have unprocessed purchasing transactions in the RCV_TRANSACTIONS_ INTERFACE table. These transactions include purchase order receipts and returns for inventory. If this condition exists, you will receive a warning but will be able to close the accounting period. These transactions are not in your receiving value. However, after you close the period, these transactions cannot be processed because they have a transaction date for a closed period.

Unprocessed material transactions exist for this period

This message indicates you have unprocessed material transactions in the MTL_MATERIAL_TRANSACTIONS_TEMP table. You are unable to close the period with this condition. Please see your system administrator. Inventory considers entries in this table as part of the quantity movement.

Closing the period in this situation is not allowed because the resultant accounting entries would have a transaction date for a closed period, and never be picked up by the period close or general ledger transfer process.

Pending material transactions for this period

This message indicates you have unprocessed material transactions in the MTL_TRANSACTIONS_INTERFACE table. If this condition exists, you will receive a warning but will be able to close the accounting period. These transactions are not in your inventory value. However, after you close the period, these transactions cannot be processed because they have a transaction date for a closed period.

Uncosted material transactions exist for this period

This message indicates you have material transactions in the MTL_MATERIAL_TRANSACTIONS table with no accounting entries (Standard Costing) and no accounting entries and no costs (Average Costing). You are unable to close the period with this condition. These transactions are part of your inventory value.

Closing the period in this situation is not allowed because the resultant accounting entries would have a transaction date for a closed period, and never be picked up by the period close or general ledger transfer process.

Pending move transactions for this period

This message indicates you have unprocessed shop floor move transactions in the WIP_MOVE_TXN_INTERFACE table. If this condition exists, you will receive a warning but will be able to close the accounting period. These transactions are not in your work in process value. However, after you close the period, these transactions cannot be processed because they have a transaction date for a closed period.

Pending WIP costing transactions exist in this period

This message indicates you have unprocessed resource and overhead accounting transactions in the WIP_COST_TXN_INTERFACE table. You are unable to close the period with this condition. These transactions are in your work in process value, and awaiting further processing.

Closing the period in this situation is not allowed because the resulting accounting entries would have a transaction date for a closed period, and never be picked up by the period close or general ledger transfer process.

Reprocessing Period Close Pending Transactions:

There are a variety of reasons for pending transactions, which we have discussed above. This following document will serve as a guide for troubleshooting and processing pending transactions preventing an accounting period from being closed.

When resolving and working with Pending Transactions users must collect and identify data in order to address the source product and complete the Period Close process.

Hence the key steps for resolving pending transactions are:

– Locate the transactions
– Find the error message to determine what is preventing the transactions from processing.
– Resolve the error
– Resubmit the pending record.

/*SCRIPT TO IDENTIFY PENDING TRANSACTIONS & STEPS TO REPROCESS THEM
================================================================*/

— Pending Move Transactions

select mti.*
from wip_move_txn_interface mti,
org_organization_definitions org
where mti.organization_id = org.organization_id
and trunc(mti.creation_date) > to_char(TRUNC(to_date(’01-JUL-2009′,’DD-MON-YYYY’)))

select organization_code,count(*)
from wip_move_txn_interface mti
group by organization_code

select mti.organization_id,mti.organization_code,org.organization_name,count(*)
from wip_move_txn_interface mti,
org_organization_definitions org
where mti.organization_id = org.organization_id
and trunc(mti.creation_date) > to_char(TRUNC(to_date(’01-JUL-2009′,’DD-MON-YYYY’)))
group by mti.organization_id,mti.organization_code,org.organization_name

select mti.organization_id,mti.organization_code,org.organization_name,count(*) from
wip_move_txn_interface mti,
wip_txn_interface_errors emsg,
mtl_system_items msi,
org_organization_definitions org
where mti.transaction_id = emsg.transaction_id
and mti.primary_item_id = msi.inventory_item_id(+)
and mti.organization_id = msi.organization_id(+)
and mti.organization_id = org.organization_id
and mti.process_status = 3
and trunc(mti.creation_date) > to_char(TRUNC(to_date(’01-JUL-2009′,’DD-MON-YYYY’)))
group by mti.organization_id,mti.organization_code,org.organization_name

— Script to Reprocess Pending Move Transactions
update wip_move_txn_interface
set group_id=null,
request_Id = null,
process_status=1,
transaction_id=null
where process_status=3
and Transaction_id = &Transaction_id — Enter the transaction_id which you want to reprocess

–Pending Resource Transactions

select wct.*
from wip_cost_txn_interface wct,
org_organization_definitions org
where wct.organization_id = org.organization_id
and trunc(wct.creation_date) > to_char(TRUNC(to_date(’01-JUL-2009′,’DD-MON-YYYY’)))

select wct.*
from wip_cost_txn_interface wct,
org_organization_definitions org
where wct.organization_id = org.organization_id
and trunc(wct.creation_date) > to_char(TRUNC(to_date(’01-JUL-2009′,’DD-MON-YYYY’)))
and wct.process_status = 3

select wct.*
from wip_cost_txn_interface wct,
org_organization_definitions org
where wct.organization_id = org.organization_id
and trunc(wct.creation_date) > to_char(TRUNC(to_date(’01-JUL-2009′,’DD-MON-YYYY’)))
and wct.process_status = 3

select wct.organization_id,wct.organization_code,wct.process_status,org.organization_name,count(*)
from wip_cost_txn_interface wct,
org_organization_definitions org
where wct.organization_id = org.organization_id
and trunc(wct.creation_date) > to_char(TRUNC(to_date(’01-JUL-2009′,’DD-MON-YYYY’)))
and wct.process_status = 3
group by wct.organization_id,wct.organization_code,wct.process_status,org.organization_name

select wcti.organization_code,wtie.error_message,wtie.error_column,wcti.transaction_id,
wcti.transaction_date,wcti.creation_date,wcti.process_phase,wcti.process_status,
we.wip_entity_name,msi.segment1,wcti.operation_seq_num,wcti.resource_seq_num,
wcti.transaction_quantity,wcti.transaction_uom,wcti.primary_uom,wcti.move_transaction_id
from wip_cost_txn_interface wcti,
wip_txn_interface_errors wtie,
wip_entities we,
mtl_system_items msi
where wcti.organization_id = msi.organization_id
and wcti.organization_id = we.organization_id
and wcti.primary_item_id = msi.inventory_item_id
and wcti. wip_entity_id = we.wip_entity_id
and wcti.transaction_id = wtie.transaction_id
and trunc(wcti.creation_date) > to_char(TRUNC(to_date(’01-JUL-2009′,’DD-MON-YYYY’)))
and wcti.process_status = 3
order by wcti.organization_code

— Script to reprocess Pending Resource Transactions
update wip_cost_txn_interface
set group_id=NULL,
transaction_id = NULL,
process_status= 1
where process_status = 3
and Transaction_id = &Transaction_id — Enter the transaction_id which you want to reprocess

— Transaction Open Interface

select mti.*
from mtl_transactions_interface_v mti,
org_organization_definitions org
where mti.organization_name = org.organization_name
and trunc(mti.creation_date) > to_char(TRUNC(to_date(’01-JUL-2009′,’DD-MON-YYYY’)))

select mti.creation_date,mti.process_flag,mti.process_flag_desc,mti.error_explanation,
mti.error_code,mti.transaction_interface_id,mti.transaction_header_id,
mti.source_code,mti.transaction_source_type_name,mti.transaction_type_name,mti.source_header_id,
mti.source_line_id,mti.transaction_mode,mti.transaction_mode_desc,mti.organization_id,
mti.organization_code,mti.organization_name,mti.inventory_item_id,mti.transaction_source_id
from mtl_transactions_interface_v mti,
org_organization_definitions org
where mti.organization_name = org.organization_name
and trunc(mti.creation_date) > to_char(TRUNC(to_date(’01-JUL-2009′,’DD-MON-YYYY’)))
and process_flag = 3
order by organization_id,organization_code,organization_name

select mti.organization_id,mti.organization_code,mti.organization_name,mti.process_flag,count(*)
from mtl_transactions_interface_v mti
where trunc(mti.creation_date) > to_char(TRUNC(to_date(’01-JUL-2009′,’DD-MON-YYYY’)))
and mti.process_flag = 3
group by organization_id,organization_code,organization_name,mti.process_flag

— Script to Process the errored Records from Transactions Open Interface
update mtl_transactions_interface
set process_flag = 1,
lock_flag = 2,
transaction_mode = 3,
validation_required = 1,
error_code = null,
error_explanation = null
where organization_id = &Organization_id
and process_flag = 3
and transaction_interface_id = &transaction_interface_id

— Pending Material Transactions

select * from mtl_material_transactions_temp
where trunc(creation_date) > to_char(TRUNC(to_date(’01-JUL-2009′,’DD-MON-YYYY’)))

select mmtt.*
from mtl_material_transactions_temp mmtt,
org_organization_definitions org
where mmtt.organization_id = org.organization_id
and trunc(mmtt.creation_date) > to_char(TRUNC(to_date(’01-JUL-2009′,’DD-MON-YYYY’)))

select mmtp.organization_id,org.organization_code,org.organization_name,count(*)
from mtl_material_transactions_temp mmtp,
org_organization_definitions org
where mmtp.organization_id = org.organization_id
and trunc(mmtp.creation_date) > to_char(TRUNC(to_date(’01-JUL-2009′,’DD-MON-YYYY’)))
group by mmtp.organization_id,org.organization_code,org.organization_name

select count(*),mmtt.error_code,mmtt.error_explanation,org.organization_id,org.organization_code,org.organization_name,org.operating_unit
from mtl_material_transactions_temp mmtt,
org_organization_definitions org
where org.organization_id = mmtt.organization_id
and trunc(mmtt.creation_date) > to_char(TRUNC(to_date(’01-JUL-2009′,’DD-MON-YYYY’)))
group by mmtt.error_code,mmtt.error_explanation,org.organization_id,org.organization_code,org.organization_name,org.operating_unit
order by org.organization_id

— Script to Reprocess the Pending Material Transactions

update mtl_material_transactions_temp
set process_flag = ‘Y’,
lock_flag = ‘N’,
transaction_mode = 3,
error_code = NULL,
error_explanation = NULL
where process_flag in (‘Y’,’E’)
and organization_id = &Organization_id
and transaction_temp_id = &transaction_temp_id

— Count Of Uncosted Transactions
select * from mtl_material_transactions
where trunc(creation_date) > to_char(TRUNC(to_date(’01-JUL-2009′,’DD-MON-YYYY’)))
and costed_flag is not null

select costed_flag,count(*)
from mtl_material_transactions
where trunc(creation_date) > to_char(TRUNC(to_date(’01-JUL-2009′,’DD-MON-YYYY’)))
and costed_flag is not null
group by costed_flag

select costed_flag,organization_id,acct_period_id,count(*)
from mtl_material_transactions
where trunc(creation_date) > to_char(TRUNC(to_date(’01-JUL-2009′,’DD-MON-YYYY’)))
and costed_flag is not null
and costed_flag = ‘E’
group by costed_flag,organization_id,acct_period_id

— Script to Reprocess the Costed Transactions

update mtl_material_transactions
set costed_flag = ‘N’,
transaction_group_id = NULL,
transaction_set_id = NULL,
request_id = NULL,
error_code = NULL,
error_explanation = NULL,
where (costed_flag = ‘E’ or costed_flag = ‘N’)
and transaction_id = &transaction_id

— Check for the Shipping Transaction Stuck in the Inventory Interface

SELECT wdd.delivery_detail_id, oe_interfaced_flag, inv_interfaced_flag
FROM wsh_trips wtr,
wsh_trip_stops wts,
wsh_delivery_legs wlg,
wsh_new_deliveries wnd,
wsh_delivery_assignments wda,
wsh_delivery_details wdd,
mtl_parameters mp
WHERE wtr.trip_id = wts.trip_id
AND wts.stop_id = wlg.pick_up_stop_id
AND wts.pending_interface_flag = ‘Y’
AND wlg.delivery_id = wnd.delivery_id
AND wnd.delivery_id = wda.delivery_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wdd.organization_id = mp.organization_id
AND mp.organization_code = ‘A66’ — Enter The Organization_code

SELECT wts.stop_id, wts.pending_interface_flag
FROM wsh_trips wtr,
wsh_trip_stops wts,
wsh_delivery_legs wlg,
wsh_new_deliveries wnd,
wsh_delivery_assignments wda,
wsh_delivery_details wdd,
mtl_parameters mp
WHERE wtr.trip_id = wts.trip_id
AND wts.stop_id = wlg.pick_up_stop_id
AND wts.pending_interface_flag = ‘Y’
AND wlg.delivery_id = wnd.delivery_id
AND wnd.delivery_id = wda.delivery_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wdd.organization_id = mp.organization_id
AND mp.organization_code = ‘A66’ — Enter The Organization_code
SELECT
wdd.source_header_id header_id,
ooh.order_number,
ool.line_number,
ool.shipment_number,
ool.line_id,
wnd.delivery_id,
wnd.NAME delivery,
wdd.delivery_detail_id,
wdl.pick_up_stop_id,
wdd.inv_interfaced_flag,
wdd.oe_interfaced_flag
FROM wsh_delivery_details wdd,
wsh_delivery_assignments wda,
wsh_new_deliveries wnd,
wsh_delivery_legs wdl,
wsh_trip_stops wts,
oe_order_headers_all ooh,
oe_order_lines_all ool
WHERE wdd.source_code = ‘OE’
AND wdd.released_status = ‘C’
AND wdd.inv_interfaced_flag IN (‘N’, ‘P’)
AND wdd.organization_id = &organization_id — Enter The Organization_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wnd.delivery_id = wda.delivery_id
AND wnd.status_code IN (‘CL’, ‘IT’)
AND wdl.delivery_id = wnd.delivery_id
AND TRUNC (wts.actual_departure_date) BETWEEN ’01-AUG-2009′ AND ’31-AUG-2009′
AND wdl.pick_up_stop_id = wts.stop_id
AND wdd.source_header_id = ooh.header_id
AND wdd.source_line_id = ool.line_id

— Steps to reprocess the pending shipping transactions

Verify that there are NO records for this Sales Order in the Pending Transactions Form or the Transaction Open Interface Form. Address the errors if any.
The records retrieved in these forms will list the Sales Order Number under the “Source” or “Transaction Source”columns for the Source TAB respectively.
Navigation> Inventory> Transactions> Pending Transactions
Navigation> Inventory> Transactions> Transaction Open Interface

For records with WSH_DELIVERY_DETAILS.OE_INTERFACED_FLAG or WSH_DELIVERY_DETAILS.INV_INTERFACED_FLAG values “P”,
please run the Interface Trip Stop process in Order Management to complete workflow for the Sales Order.
Navigation> Order Management> Shipping> Interfaces> Run > Select the Interface Trip Stop – SRS.