, , ,

Reprocessing Period Close Pending Transactions


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.

/by
, , , , , , , , , ,

Use of inv_lot_api_pub API for Inserting & Auto Generating the Lot Numbers

A lot number is a combination of an alphanumeric prefix and a numeric suffix. When we define an item, we can specify the starting lot prefix and the starting lot number. Oracle Inventory uses this information to generate defaults during transaction entry.

1. Establish lot control for an item.

We can establish lot control for an item when We define it. We can choose from No control or Full control. If We choose lot control We must assign lot numbers when We receive the item into inventory. Thereafter, when We transact this item, We must provide a lot number We specified when We received the item.We can update lot control options for an item if it has zero on-hand quantity.
2 . Establish lot number uniqueness.

We use the Organization Parameters window to specify whether lot numbers should be unique for an item. If We do not establish lot number uniqueness, We can assign the same lot number to multiple items in the same organization and across organizations.

If We control lot number uniqueness at the Master level, We can assign a specific lot number only to one item in the same organization and across organizations. When We perform transactions, Oracle Inventory checks the lot number uniqueness control to generate lot number defaults.

3. Optionally, determine whether to automatically generate lot number defaults.

We use the Organization Parameters window to specify how to generate lot number defaults. We can choose to generate sequential lot numbers based on an alphanumeric prefix We specify when We define an item. Oracle Inventory can also generate lot number defaults for the entire organization. In this case, We must define a lot number prefix at the Organization level in the Organization Parameters window.

How to generate/insert lot numbers using inv_lot_api_pub.auto_gen_lot API?================================================================

— R12 – INV – Sample Script to Generate Lot Number using inv_lot_api_pub

DECLARE

l_chr_lot_number VARCHAR2 (50);
l_chr_return_status VARCHAR2 (2000);
l_num_msg_count NUMBER;
l_chr_msg_data VARCHAR2 (50);

Cursor c_item_info is

select * from mtl_system_items_kfv
where concatenated_segments = ‘TSTITEM^3M’ — Enter the item for which Lot Number needs to be created
and organization_id = 381; — Enter the organization_id

BEGIN

— initialization required for R12
mo_global.set_policy_context (‘S’, 308);
mo_global.init(‘INV’);

— Initialization for Organization_id
inv_globals.set_org_id (381);

— initialize environment
fnd_global.apps_initialize (user_id => 2083,
resp_id => 20634,
resp_appl_id => 401);

For i in c_item_info

LOOP
dbms_output.put_line (‘Calling inv_lot_api_pub.auto_gen_lot API to Create Lot Numbers’);
dbms_output.put_line (‘*********************************************’);

l_chr_lot_number := inv_lot_api_pub.auto_gen_lot (
p_org_id => i.organization_id,
p_inventory_item_id => i.inventory_item_id,
p_parent_lot_number => NULL,
p_subinventory_code => NULL,
p_locator_id => NULL,
p_api_version => 1.0,
p_init_msg_list => ‘F’,
p_commit => ‘T’,
p_validation_level => 100,
x_return_status => l_chr_return_status,
x_msg_count => l_num_msg_count,
x_msg_data => l_chr_msg_data);

dbms_output.put_line (‘The Status Returned by the API is => ‘ l_chr_return_status);

IF l_chr_return_status = ‘S’
THEN
COMMIT;
ELSE
ROLLBACK;
END IF;

dbms_output.put_line (‘The Message Count Returned by the API is => ‘ l_num_msg_count);
dbms_output.put_line (‘The Message Returned by the API is => ‘ l_chr_return_status);
dbms_output.put_line (‘Lot Number Created for the item ‘ i.concatenated_segments ‘ is => ‘ l_chr_lot_number);

END LOOP;

END;

— R12 – INV – Sample Script to Insert Lot Number using inv_lot_api_pub

DECLARE
x_object_id NUMBER;
x_return_status VARCHAR2 (1);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (4000);
x_expire_date DATE;

Cursor c_item_info is

LOOP

select * from mtl_system_items_kfv
where concatenated_segments = ‘TSTITEM^3M’ — Enter the item for which Lot Number needs to be created
and organization_id = 381; — Enter the organization_id

BEGIN

— initialization required for R12
mo_global.set_policy_context (‘S’, 308);
mo_global.init(‘INV’);

— Initialization for Organization_id
inv_globals.set_org_id (381);

— initialize environment
fnd_global.apps_initialize (user_id => 2083,
resp_id => 20634,
resp_appl_id => 401);

For i in c_item_info

dbms_output.put_line (‘Calling inv_lot_api_pub.auto_gen_lot API to Create Lot Numbers’);
dbms_output.put_line (‘*********************************************’);

inv_lot_api_pub.insertlot
(p_api_version => 1,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
p_validation_level => fnd_api.g_valid_level_full,
p_inventory_item_id => i.inventory_item_id,
p_organization_id => i.organization_id,
p_lot_number => ‘A6644001’,
p_expiration_date => x_expire_date,
x_object_id => x_object_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);

dbms_output.put_line (‘The Status Returned by the API is => ‘x_return_status);

IF x_return_status = fnd_api.g_ret_sts_success THEN
COMMIT;
ELSE
ROLLBACK;
END IF;

DBMS_OUTPUT.put_line (‘x_object_id :’ x_object_id);
DBMS_OUTPUT.put_line (‘x_msg_count :’ x_msg_count);
DBMS_OUTPUT.put_line (‘x_msg_data :’ x_msg_data);

END LOOP;
END;

/by
, , , , , , , , , ,

To Check for the available onhand For An Item in a Given Organization Using Inv_Quantity_Tree_Pub API

Inv_Quantity_Tree_Pub API can be used for querying the available Onhand in a given subinventory or organization.
The output of the API inv_quantity_tree_pub.query_quantities will show the Total Available Onhand , Reservations , Suggestions and the Actual Onhand that can be Transacted.
— R12 – INV – Sample Script to Get Onhand Using INV_Quantity_Tree_PUB API:DECLARE

l_api_return_status VARCHAR2 (1);
l_qty_oh NUMBER;
l_qty_res_oh NUMBER;
l_qty_res NUMBER;
l_qty_sug NUMBER;
l_qty_att NUMBER;
l_qty_atr NUMBER;
l_msg_count NUMBER;
l_msg_data VARCHAR2 (1000);
v_item VARCHAR2 (250) := ‘&Item_Num’;
v_org VARCHAR2 (10) := ‘&Org_code’;

Cursor c_item_info is

SELECT concatenated_segments item, msi.inventory_item_id,msi.organization_id, mp.organization_code
FROM mtl_system_items_kfv msi, mtl_parameters mp
WHERE concatenated_segments = v_item
AND msi.organization_id = mp.organization_id
AND mp.organization_code = v_org;

BEGIN

inv_quantity_tree_grp.clear_quantity_cache;

DBMS_OUTPUT.put_line (‘Transaction Mode’);

For i in c_item_info

LOOP

DBMS_OUTPUT.put_line (‘Extracting the Onhand For the Item ===========> ‘ i.item);
DBMS_OUTPUT.put_line (‘Extracting the Onhand For the Organization ======> ‘ i.organization_code);

apps.inv_quantity_tree_pub.query_quantities
(p_api_version_number => 1.0,
p_init_msg_lst => apps.fnd_api.g_false,
x_return_status => l_api_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_organization_id => i.organization_id,
p_inventory_item_id => i.inventory_item_id,
p_tree_mode => apps.inv_quantity_tree_pub.g_transaction_mode,
p_onhand_source => 3,
p_is_revision_control => FALSE,
p_is_lot_control => FALSE,
p_is_serial_control => FALSE,
p_revision => NULL,
p_lot_number => NULL,
p_subinventory_code => NULL,
p_locator_id => NULL,
x_qoh => l_qty_oh,
x_rqoh => l_qty_res_oh,
x_qr => l_qty_res,
x_qs => l_qty_sug,
x_att => l_qty_att,
x_atr => l_qty_atr);

DBMS_OUTPUT.put_line (‘Quantity on hand ======================> ‘ TO_CHAR (l_qty_oh));
DBMS_OUTPUT.put_line (‘Reservable quantity on hand ===============> ‘ TO_CHAR (l_qty_res_oh));
DBMS_OUTPUT.put_line (‘Quantity reserved =====================> ‘ TO_CHAR (l_qty_res));
DBMS_OUTPUT.put_line (‘Quantity suggested ====================> ‘ TO_CHAR (l_qty_sug));
DBMS_OUTPUT.put_line (‘Quantity Available To Transact ==============> ‘ TO_CHAR (l_qty_att));
DBMS_OUTPUT.put_line (‘Quantity Available To Reserve ==============> ‘ TO_CHAR (l_qty_atr));

END LOOP;
END;

DBMS OUTPUT:

Transaction Mode
Extracting the Onhand For the Item =========> ELXV350ELL121MH12D^NIPPONSCC
Extracting the Onhand For the Organization ====> A66
Quantity on hand =======================> 3400
Reservable quantity on hand ===============> 3400
Quantity reserved =======================> 1000
Quantity suggested ======================> 0
Quantity Available To Transact==============> 2400
Quantity Available To Reserve ==============> 2400

/by
, , , , , , , , , ,

Performing Back Order in Move Order Line Using inv_mo_backorder_pvt API

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

DECLARE

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,
dd.source_line_number,
NVL (dl.ship_method_code, dd.ship_method_code) ship_method_code,
dd.inventory_item_id,
(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,
dd.move_order_line_id,
(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;

BEGIN

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

LOOP

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
THEN
DBMS_OUTPUT.put_line (‘Successfully BackOrdered the Move Order Line’);
COMMIT;
ELSE
DBMS_OUTPUT.put_line
(‘Could not able to Back Order Line Due to Following Reasons’ );
ROLLBACK;

FOR j IN 1 .. l_msg_count
LOOP
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);
END LOOP;
END IF;

END LOOP;

END;

DBMS Output: –
***************

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

/by
, , , , , , , , , ,

Performing Pick Release Using WSH_DELIVERY_PUB API

Performing Pick Release Using WSH_DELIVERY_PUB API
************************************************************

Creating Deliveries:
===============• A Delivery is required to perform Ship Confirm. It represent all the goods that were shipped from the same warehouse, going to the same Customer location.
• A Delivery can be created automatically or manually from the Shipping Transaction form at any time after the order lines have become “Awaiting Shipment” or can be automatically created during the Release Sales Order process.

• The delivery can also be created Programatically using shipping API “WSH_DELIVERIES_PUB.Create_update_delivery” by passing the required and optional parameter values.

Specific parameters:

• p_api_version_number => 1.0
• p_action_code => CREATE

— for creating new delivery
• p_delivery_info => Attributes of the delivery entity of type Delivery_Pub_Rec_Type

Performing Pick Release:
===================
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 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.
• 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.

Allocate Inventory to the move order:
***************************************
• Allocating can be done automatically after the move order is created by setting “Auto Allocate” to yes on the Release Sales Order form or postponed until later, then manually allocated from the Transaction Move Orders form.
• The Release Sequence Rule, that was entered on the Release Sales Orders form, or defaulted from the Shipping Parameters, will be used to determine in what sequence to fill the move orders.
• Move orders use inventory’s picking rules to suggest the material that should be allocated. The sourcing values for subinventory, locators, revisions, and lots are defaulted on to the move order.
• The source defaults can be manually updated from the Transaction Move Orders form.
• A high level reservation is placed for the Move Order’s material requirements.
• Allocating inventory is a prerequisite for printing Pick Slips. The Pick Slip Grouping Rule entered on the Release Sales Orders form or defaulted from the Shipping Parameters is used while printing pick slips.

Pick Confirm the move order
******************************
• Pick Confirm can be done automatically during the picking process by setting “Pick Confirm” to yes on the Release Sales Orders form or by unchecking the Pick Confirm Required checkbox in the Organization Parameters form. If it is not done automatically during pick release it can be done manually from the Transaction Move Orders form by selecting the Transact button.
• The Pick Confirm transaction executes the subinventory transfer, moving the material from it’s source location to it’s destination staging location. Only 1 staging subinventory is supported per picking batch.
• The high level reservations are replaced with detail reservations.
• If you transact less than the requested quantity, the Move Order will stay open until the total quantity is transacted, or the order is closed or cancelled.
• The status of the Sales Order Line, which is linked to the Move Order, is changed to “Picked” making it eligible for Ship Confirm.
• If not all of the Move order quantity was transacted the status of the Sales Order Line, is changed to “Picked Partial”. During Ship Confirm the order line will split into 2 shipment schedules (e.g. 1.1 and 1.2). The status of the unfilled line will be changed to “Awaiting Shipping”.

For performing Pick Release Programatically we can use the shipping public API “WSH_DELIVERIES_PUB.Delivery_Action”, which enables pick release of the sales order line. The relevant pick release parameters are retrieved from the Shipping and Organization Parameter setup.

Specific Parameters:

p_action_code = > PICK_RELEASE
p_delivery_id/p_delivery_name => Id/name of delivery

— R12 – OM – Sample Script to Perform Pick using WSH_DELIVERY_PUB API —
— ======================================================== —

DECLARE

x_return_status VARCHAR2 (2);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (2000);
p_api_version_number NUMBER := 1.0;
init_msg_list VARCHAR2 (200);
x_msg_details VARCHAR2 (3000);
x_msg_summary VARCHAR2 (3000);
p_line_rows wsh_util_core.id_tab_type;
x_del_rows wsh_util_core.id_tab_type;
l_ship_method_code VARCHAR2 (100);
i NUMBER;
l_commit VARCHAR2 (30);
p_delivery_id NUMBER;
p_delivery_name VARCHAR2 (30);
x_trip_id VARCHAR2 (30);
x_trip_name VARCHAR2 (30);
exep_api EXCEPTION;
l_picked_flag VARCHAR2 (10);
l_return_status VARCHAR2 (1000);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (1000);
l_user_id NUMBER;
l_resp_id NUMBER;
l_appl_id NUMBER;

CURSOR c_ord_details
IS

SELECT oha.order_number sales_order, oha.org_id, ola.line_number,
ola.shipment_number, ola.flow_status_code,
wdd.delivery_detail_id, wdd.inv_interfaced_flag,
wdd.oe_interfaced_flag, wdd.released_status
FROM apps.oe_order_headers_all oha,
apps.oe_order_lines_all ola,
apps.wsh_delivery_details wdd
WHERE oha.header_id = ola.header_id
AND oha.org_id = ola.org_id
AND oha.header_id = wdd.source_header_id
AND ola.line_id = wdd.source_line_id
AND oha.booked_flag = ‘Y’
AND NVL (ola.cancelled_flag, ‘N’) <> ‘Y’
AND wdd.released_status in (‘R’,’B’)
AND ola.flow_status_code = ‘AWAITING_SHIPPING’
AND oha.order_number = 10001059
AND oha.org_id = 308;

BEGIN

— Initializing the Applications

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);

x_return_status := wsh_util_core.g_ret_sts_success;
i := 0;

FOR i IN c_ord_details

LOOP

— Mandatory initialization for R12
mo_global.set_policy_context (‘S’, i.org_id);
mo_global.init (‘ONT’);

p_line_rows (1) := i.delivery_detail_id;

— API Call for Auto Create Deliveries

DBMS_OUTPUT.put_line
(‘Calling WSH_DELIVERY_DETAILS_PUB to Perform AutoCreate Delivery’);
DBMS_OUTPUT.put_line
(‘====================================================’);

wsh_delivery_details_pub.autocreate_deliveries
(p_api_version_number => 1.0,
p_init_msg_list => apps.fnd_api.g_true,
p_commit => l_commit,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_line_rows => p_line_rows,
x_del_rows => x_del_rows
);

DBMS_OUTPUT.put_line (x_return_status);
DBMS_OUTPUT.put_line (x_msg_count);
DBMS_OUTPUT.put_line (x_msg_data);

IF (x_return_status <> wsh_util_core.g_ret_sts_success) THEN

DBMS_OUTPUT.put_line
(‘Failed to Auto create delivery for Sales Order’);
RAISE exep_api;

ELSE

DBMS_OUTPUT.put_line
(‘Auto Create Delivery Action has successfully completed for SO’);
DBMS_OUTPUT.put_line (‘=============================================’);
END IF;

— Pick release.
p_delivery_id := x_del_rows (1);
p_delivery_name := TO_CHAR (x_del_rows (1));

DBMS_OUTPUT.put_line
(‘Calling WSH_DELIVERIS_PUB to Perform Pick Release of SO’);
DBMS_OUTPUT.put_line (‘=============================================’);
— API Call for Pick Release

wsh_deliveries_pub.delivery_action (p_api_version_number => 1.0,
p_init_msg_list => NULL,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_action_code => ‘PICK-RELEASE’,
p_delivery_id => p_delivery_id,
p_delivery_name => p_delivery_name,
p_asg_trip_id => NULL,
p_asg_trip_name => NULL,
p_asg_pickup_stop_id => NULL,
p_asg_pickup_loc_id => NULL,
p_asg_pickup_stop_seq => NULL,
p_asg_pickup_loc_code => NULL,
p_asg_pickup_arr_date => NULL,
p_asg_pickup_dep_date => NULL,
p_asg_dropoff_stop_id => NULL,
p_asg_dropoff_loc_id => NULL,
p_asg_dropoff_stop_seq => NULL,
p_asg_dropoff_loc_code => NULL,
p_asg_dropoff_arr_date => NULL,
p_asg_dropoff_dep_date => NULL,
p_sc_action_flag => ‘S’,
p_sc_intransit_flag => ‘N’,
p_sc_close_trip_flag => ‘N’,
p_sc_create_bol_flag => ‘N’,
p_sc_stage_del_flag => ‘Y’,
p_sc_trip_ship_method => NULL,
p_sc_actual_dep_date => NULL,
p_sc_report_set_id => NULL,
p_sc_report_set_name => NULL,
p_sc_defer_interface_flag => ‘Y’,
p_sc_send_945_flag => NULL,
p_sc_rule_id => NULL,
p_sc_rule_name => NULL,
p_wv_override_flag => ‘N’,
x_trip_id => x_trip_id,
x_trip_name => x_trip_name
);

DBMS_OUTPUT.put_line (x_return_status);
DBMS_OUTPUT.put_line (x_msg_count);
DBMS_OUTPUT.put_line (x_msg_data);

IF (x_return_status <> wsh_util_core.g_ret_sts_success) THEN

DBMS_OUTPUT.put_line (‘Failed to Pick Release the sales order’);
RAISE exep_api;

ELSE

DBMS_OUTPUT.put_line (‘Sales Order has successfully Pick Released’);
DBMS_OUTPUT.put_line (‘==============================’);

END IF;

–for pick confirm
COMMIT;

END LOOP;

EXCEPTION

WHEN exep_api THEN

DBMS_OUTPUT.put_line (‘==============’);
DBMS_OUTPUT.put_line (‘Error Details If Any’);
DBMS_OUTPUT.put_line (‘==============’);

wsh_util_core.get_messages (‘Y’,
x_msg_summary,
x_msg_details,
x_msg_count
);

IF x_msg_count > 1 THEN

x_msg_data := x_msg_summary x_msg_details;
DBMS_OUTPUT.put_line (x_msg_data);

ELSE

x_msg_data := x_msg_summary x_msg_details;
DBMS_OUTPUT.put_line (x_msg_data);

END IF;

END;

/by