Reprocessing Period Close Pending Transactions

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.