R12 – Sample Script to Cancel Single AP Invoice using ap_cancel_package API
l_message_name VARCHAR2 (1000);
l_invoice_amount NUMBER;
l_base_amount NUMBER;
l_tax_amount NUMBER;
l_temp_cancelled_amount NUMBER;
l_cancelled_by VARCHAR2 (1000);
l_cancelled_amount NUMBER;
l_cancelled_date DATE;
l_last_update_date DATE;
l_original_prepayment_amount NUMBER;
l_pay_curr_invoice_amount NUMBER;
l_token VARCHAR2 (100);
l_boolean BOOLEAN;
l_user_id NUMBER := 2083;
l_resp_id NUMBER := 20639;
l_appl_id NUMBER := 200;

CURSOR C_Inv_Det is
SELECT distinct aia.*
FROM ap_invoices_all aia, ap_invoice_lines_all aila
WHERE aia.invoice_id = aila.invoice_id
AND aia.org_id = aila.org_id
AND aia.org_id = 308
AND aia.invoice_num = ‘300040823’
AND aia.payment_status_flag = ‘N’
AND NVL(aila.cancelled_flag, ‘N’) <> ‘Y’;
fnd_global.apps_initialize(l_user_id, l_resp_id, l_appl_id);
FOR l_inv_rec IN C_Inv_Det LOOP
mo_global.init (‘SQLAP’);
mo_global.set_policy_context (‘S’, l_inv_rec.org_id);
DBMS_OUTPUT.put_line (‘Calling API ap_cancel_pkg.ap_cancel_single_invoice to Cancel Invoice: ‘ l_inv_rec.invoice_num);
DBMS_OUTPUT.put_line (‘**************************************************************’);

l_boolean := ap_cancel_pkg.ap_cancel_single_invoice
(p_invoice_id => l_inv_rec.invoice_id,
p_last_updated_by => l_inv_rec.last_updated_by,
p_last_update_login => l_inv_rec.last_update_login,
p_accounting_date => l_inv_rec.gl_date,
p_message_name => l_message_name,
p_invoice_amount => l_invoice_amount,
p_base_amount => l_base_amount,
p_temp_cancelled_amount => l_temp_cancelled_amount,
p_cancelled_by => l_cancelled_by,
p_cancelled_amount => l_cancelled_amount,
p_cancelled_date => l_cancelled_date,
p_last_update_date => l_last_update_date,
p_original_prepayment_amount => l_original_prepayment_amount,
p_pay_curr_invoice_amount => l_pay_curr_invoice_amount,
P_Token => l_token,
p_calling_sequence => NULL);

DBMS_OUTPUT.put_line (‘l_message_name => ‘ l_message_name);
DBMS_OUTPUT.put_line (‘l_invoice_amount => ‘ l_invoice_amount);
DBMS_OUTPUT.put_line (‘l_base_amount => ‘ l_base_amount);
DBMS_OUTPUT.put_line (‘l_tax_amount => ‘ l_tax_amount);
DBMS_OUTPUT.put_line (‘l_temp_cancelled_amount => ‘ l_temp_cancelled_amount);
DBMS_OUTPUT.put_line (‘l_cancelled_by => ‘ l_cancelled_by);
DBMS_OUTPUT.put_line (‘l_cancelled_amount => ‘ l_cancelled_amount);
DBMS_OUTPUT.put_line (‘l_cancelled_date => ‘ l_cancelled_date);
DBMS_OUTPUT.put_line (‘P_last_update_date => ‘ l_last_update_date);
DBMS_OUTPUT.put_line (‘P_original_prepayment_amount => ‘ l_original_prepayment_amount);
DBMS_OUTPUT.put_line (‘l_pay_curr_invoice_amount => ‘ l_pay_curr_invoice_amount);

IF l_boolean
DBMS_OUTPUT.put_line (‘Successfully Cancelled the Invoice => ‘ l_inv_rec.invoice_num);
DBMS_OUTPUT.put_line (‘Failed to Cancel the Invoice => ‘ l_inv_rec.invoice_num);
DBMS Output –

Calling API ap_cancel_pkg.ap_cancel_single_invoice to Cancel Invoice: 300040823
l_message_name =>
l_invoice_amount => 0
l_base_amount => 0
l_tax_amount =>
l_temp_cancelled_amount => 15678
l_cancelled_by => 1831
l_cancelled_amount => 15678
l_cancelled_date => 06-OCT-09
P_last_update_date => 06-OCT-09
P_original_prepayment_amount =>
l_pay_curr_invoice_amount => 0
Successfully Cancelled the Invoice => 300040823

Before approving the Purchase Order & when the PO is in INCOMPLETE status, Oracle allows us to delete the same from the Purchase Order Form. The same can be achieved using the following mentioned API.
But we need to remember few points before using this API for deleting Incomplete PO.
– The PO Should be INCOMPLETE & Should not be Approved Once.
– Once the PO is approved, records gets inserted into mtl_supply & all the archive tables.
This API never deletes the records from the mtl_supply & archive tables and only deletes the
records from the base tables.
— Sample Script to Delete Incomplete PO Using ‘PO_HEADERS_SV1’

l_deleted BOOLEAN;

CURSOR c_po_det IS
SELECT po_header_id, segment1,org_id
FROM po.po_headers_all pha
WHERE 1 = 1
AND segment1 = ‘11170002356’
AND org_id = 308
AND NVL (approved_flag, ‘N’) <> ‘Y’
AND NVL (cancel_flag, ‘N’) = ‘N’
AND type_lookup_code = ‘STANDARD’
AND authorization_status = ‘INCOMPLETE’
FROM po_headers_archive_all a
WHERE pha.po_header_id = a.po_header_id
AND pha.org_id = a.org_id)
FROM mtl_supply b WHERE pha.po_header_id = b.po_header_id AND
supply_type_code = ‘PO’);
FOR c1 IN c_po_det LOOP
DBMS_OUTPUT.put_line ( ‘Calling PO_HEADERS_SV1 API To Delete PO’);
DBMS_OUTPUT.put_line ( ‘==========================’);
l_deleted := po_headers_sv1.delete_po (c1.po_header_id, ‘STANDARD’);

IF l_deleted = TRUE
DBMS_OUTPUT.put_line ( ‘Successfully Deleted the PO’);
DBMS_OUTPUT.put_line ( ‘Failed to Delete the PO’);

Performing Back Order in a Move Order Line Using inv_mo_backorder_pvt API *********************************************************************************
What Happens during Pick Release?

• A pre-approved Move Order is automatically created in Inventory.
• A Move order is a request for a subinventory transfer from the source (stocking) subinventory to the destination (staging) subinventory.
• A Move order is created for every Sales Order Line that has the status of “Awaiting Shipping” and passes the picking criteria (Use your Release Rules to guide this process).
• The Destination subinventory is the Staging subinventory entered on the Release Sales Orders form or defaulted from the Shipping Parameters. Only one staging subinventory is allowed per Picking Batch.
• Note: A Picking Batch is the total number of Order Lines that were released at one time.
• Inventory uses the move order to manage material requisitions within an Organization.
• Some times, the Pick Release Process (Pick Selection List Generation Program) either completed with warning or with Error. While dubuging, it shows the line has not been pick released & the delivery status is showing as “Released to Warehouse”. In the delivery detail window, the next steps indicate “Transaction Move Order”.

What exactly Release to Warehouse – (Released Status S in the wsh_delivery_details Table) Status is meant by?

• Pick release has processed the delivery line and has created move order headers and lines. Found available quantity and created inventory allocations. Not pick confirmed. In other words Pick Release has started but not completed. Either no allocations were created or the allocations have not been Pick Confirmed.
• If you are using auto-pick confirm during the Pick Release process then it changes release status to Staged. If you are not using auto-pick confirm and want to progress the delivery lines, navigate to Oracle Inventory Move Order Transaction window and perform manual pick confirm that is nothing but Transacting the Move Order.
• Once you navigate to the move order window, either you can Allocate & Transact the move order to complete the Pick Release process orYou can backorder that particular line (In the Move Order Transact Window > Search for the Move Order by the Number > Tools > Back Order Lines) & Proceed with the Pick Release Process Again.

This Particular Operation can be done by using a Private API “inv_mo_backorder_pvt.backorder”.

— R12 – OM – Sample Script to Back Order a Move Order Line Using INV_MO_BACKORDER_PVT API


CURSOR c_order_det IS

SELECT dd.delivery_detail_id, dd.source_code, dd.source_line_id,dd.org_id,
dd.source_header_id, dd.source_header_number,
NVL (dl.ship_method_code, dd.ship_method_code) ship_method_code,
(SELECT concatenated_segments
FROM mtl_system_items_kfv
WHERE inventory_item_id = dd.inventory_item_id
AND organization_id = dd.organization_id) ordered_item,
NVL (wsn.quantity, dd.requested_quantity) requested_quantity,
NVL (wsn.quantity, dd.shipped_quantity) shipped_quantity,
dd.requested_quantity_uom, dd.src_requested_quantity_uom,
dd.requested_quantity2, dd.shipped_quantity2,
dd.requested_quantity_uom2, dd.src_requested_quantity_uom2,
dd.ship_set_id, dd.revision, dd.lot_number,
NVL (wsn.fm_serial_number,
DECODE (dd.oe_interfaced_flag, ‘Y’, dd.serial_number, NULL)
) serial_number,
dd.released_status, wl2.meaning, dl.delivery_id, dl.NAME,
dl.currency_code, dl.status_code delivery_status_code,
DECODE (NVL (dl.status_code, ‘-99’),
‘-99’, NULL, wl1.meaning ) delivery_status_meaning,
dd.organization_id, dl.initial_pickup_date, dl.ultimate_dropoff_date,
NVL (wsn.to_serial_number, dd.to_serial_number) to_serial_number,
(select request_number from mtl_txn_request_headers where header_id in
(select header_id from mtl_txn_request_lines
where line_id = dd.move_order_line_id)) Move_order,
(select Line_number from mtl_txn_request_lines
where line_id = dd.move_order_line_id) Move_order_Line
FROM wsh_lookups wl1,
wsh_lookups wl2,
wsh_new_deliveries dl,
wsh_delivery_assignments_v da,
wsh_delivery_details dd,
wsh_locations wlf,
wsh_locations wlt,
wsh_serial_numbers wsn
WHERE dd.delivery_detail_id = da.delivery_detail_id
AND da.delivery_id = dl.delivery_id(+)
AND NVL (dl.status_code, ‘OP’) = wl1.lookup_code
AND wl1.lookup_type = ‘DELIVERY_STATUS’
AND ( ( wl2.lookup_code = ‘K’
AND dd.released_status = ‘S’
AND dd.move_order_line_id IS NULL)
OR ( wl2.lookup_code = dd.released_status
AND ( (dd.move_order_line_id IS NOT NULL)
OR (dd.released_status <> ‘S’))
AND (dd.replenishment_status IS NULL))
OR ( wl2.lookup_code = ‘E’
AND dd.released_status IN (‘R’, ‘B’)
AND dd.replenishment_status = ‘R’)
OR ( wl2.lookup_code = ‘F’
AND dd.released_status IN (‘R’, ‘B’)
AND dd.replenishment_status = ‘C’))
AND wl2.lookup_type = ‘PICK_STATUS’
AND dd.ship_from_location_id = wlf.wsh_location_id(+)
AND dd.ship_to_location_id = wlt.wsh_location_id(+)
AND dd.delivery_detail_id = wsn.delivery_detail_id(+)
AND NVL (dd.line_direction, ‘O’) IN (‘O’, ‘IO’)
AND NVL (dl.delivery_type, ‘STANDARD’) = ‘STANDARD’
AND dd.source_header_number = ‘10000596’
AND dd.released_status = ‘S’;

l_return_status VARCHAR2 (100);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (2000);
l_msg_index NUMBER;
l_user_id NUMBER;
l_resp_id NUMBER;
l_appl_id NUMBER;


SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE user_name = ‘A42485’;

SELECT responsibility_id, application_id
INTO l_resp_id, l_appl_id
FROM fnd_responsibility_vl
WHERE responsibility_name = ‘Order Management Super User’;
fnd_global.apps_initialize (l_user_id, l_resp_id, l_appl_id);

FOR i IN c_order_det


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

DBMS_OUTPUT.put_line (‘Calling INV_MO_BACKORDER_PVT to Backorder MO’);
DBMS_OUTPUT.put_line (‘===============================’);

inv_mo_backorder_pvt.backorder (
p_line_id => i.move_order_line_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data

DBMS_OUTPUT.put_line (‘Return Status is=> ‘ l_return_status);
— Check Return Status

IF l_return_status = fnd_api.g_ret_sts_success
DBMS_OUTPUT.put_line (‘Successfully BackOrdered the Move Order Line’);
(‘Could not able to Back Order Line Due to Following Reasons’ );

FOR j IN 1 .. l_msg_count
fnd_msg_pub.get (p_msg_index => j,
p_encoded => fnd_api.g_false,
p_data => l_msg_data,
p_msg_index_out => l_msg_index);
DBMS_OUTPUT.put_line (‘Error Message is=> ‘ l_msg_data);



DBMS Output: –

Calling INV_MO_BACKORDER_PVT to Backorder MO
Return Status is=> S
Successfully BackOrdered the Move Order Line