Purge Closed Transfer Batch Deleting Newer Allocation (Doc ID 3006170.1)

Last updated on FEBRUARY 23, 2024

Applies to:

Symptoms

As per the purge closed transfer batch it seems it will also delete their delivery data from sim delivery table. This is causing new delivery data to get deleted which is not eligible for purge. As per the code below the cursor is fetching the delivery data :

select distinct tsf_delv_id
from tsf_delv_line_item delv_item
where delv_item.DOCUMENT_TYPE = 1
and delv_item.DOCUMENT_ID in (select external_id
from tsf_allocation alloc
where alloc.status in (STATUS_ALLOC_COMPLETED, STATUS_ALLOC_CANCELLED)
and alloc.delivery_date <= trunc(sysdate) - L_config_value)
and exists (select null
from tsf_delv delv
where delv.id = delv_item.tsf_delv_id
and delv.status in (STATUS_DELV_RECEIVED, STATUS_DELV_CANCELED));

This cursor is fetching the records which are new. Example: I can allocate item 123 to store 1 on Jan 1st 2020. Then on the same allocation, send it to store 4 on Oct 1st 2021

The original cursor looks for allocations by item older than 365 days or whatever, and deletes out the deliveries for it, and since it only looks by item, its' going to delete the allocation deliveries for store 1, which is good, and store 4, which is not valid to be deleted.
So the cursor would only look specifically for that store 1 when it goes and deletes the deliveries.

STEPS TO REPRODUCE :
-----------------------
1. Run the purge closed transfer

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.