FTP Batches Do Not Process All Records and Produce Inconsistent Results (Doc ID 2846511.1)

Last updated on MAY 22, 2023

Applies to:

Symptoms

When executing the production FTP Batches, you get inconsistent results.  Each batch processes a separate data table.  The batches are run in parallel.

In the batches, the FTP Processes are failing with different behavior each time.

In one case, both FTP Processes stop prior to processing data.  The following was the last SQL in the log:

select distinct a.ISO_CURRENCY_CD, a.TP_COA_ID
from FSI_D_BORROWINGS a
where as_of_date = to_date('09/ 30/ 2021', 'mm/ dd/ yyyy')
and LEGAL_ENTITY_ID = - 1
and TP_COA_ID in (select prod_id
from fsi_process_node_map
where process_node is not null
and node_map_id = 821)
order by a.ISO_CURRENCY_CD, a.TP_COA_ID

In another case, the first FTP Process stops after querying for the products in FSI_D_BORROWINGS.  The second FTP Process runs successfully.

For another batch, no records are processed but the last SQL is a delete against FSI_TP_CATERPILLAR_INTRMDIATE:

delete from FSI_TP_CATERPILLAR_INTRMDIATE
where tp_process_sys_id =200631.000000
and mis_date = to_date('09/ 30/ 2021', 'MM/ DD/ YYYY')
and product_id not in (select distinct product_id
from FSI_TP_CATERPILLAR_INTRMDIATE
where tp_process_sys_id =200631.000000
and mis_date > to_date('09/ 30/ 2021', 'MM/ DD/ YYYY'))

You expect all FTP Processes to process against all records.

The FTP batches are run in parallel.  If you run them one at a time, they run successfully as expected.

Cause

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