How To Remove the Requirement To Match on Product Id for FTP Rate Propagation (Doc ID 2927169.1)

Last updated on FEBRUARY 15, 2023

Applies to:

Goal

When running an FTP Process with the Transfer Rate Propagation option selected, the generated SQL requires records to match on the Product Id column in addition to ID_NUMBER.  Can you remove the requirement to match on Product Id?

Note: The Product Id column is defined the FTP Application Preferences (ex. PRODUCT_ID, COMMON_COA_ID, custom column, etc.)

Example of code with Product Id requirement:

update FSI_D_TERM_DEPOSITS a
set (a.TRANSFER_RATE) = (select decode (a.reprice_freq, 0,
old.TRANSFER_RATE, decode (greatest (a.last_reprice_date, (select
max(as_of_date)from FSI_D_TERM_DEPOSITS old
where old.PRODUCT_ID = a.PRODUCT_ID
and old.as_of_date <=to_date ( '03/ 30/ 2022', 'MM/ DD/ YYYY'))),
(select max(as_of_date)from FSI_D_TERM_DEPOSITS old
where old.PRODUCT_ID = a.PRODUCT_ID
and old.as_of_date <=to_date ( '03/ 30/ 2022', 'MM/ DD/ YYYY')),
old.TRANSFER_RATE, a.TRANSFER_RATE ))
from FSI_D_TERM_DEPOSITS old
where old.id_number = a.id_number
and old.as_of_date =(select max(as_of_date)from
FSI_D_TERM_DEPOSITS old
where old.PRODUCT_ID = a.PRODUCT_ID
and old.as_of_date <=to_date ( '03/ 30/ 2022', 'MM/ DD/ YYYY')))
where a.ISO_CURRENCY_CD = :ISO_CURRENCY_CD
and a.PRODUCT_ID = :PRODUCT_ID
and as_of_date = to_date('03/ 31/ 2022', 'mm/ dd/ yyyy')
and a.as_of_date = to_date('03/ 31/ 2022', 'MM/ DD/ YYYY')
and exists(select id_number
from FSI_D_TERM_DEPOSITS old
where old.id_number = a.id_number
and old.as_of_date = (select max(as_of_date)from
FSI_D_TERM_DEPOSITS old
where old.PRODUCT_ID = a.PRODUCT_ID
and as_of_date <= to_date('03/ 30/ 2022', 'MM/ DD/ YYYY')))and
not exists (select product_id
from FSI_M_TRANSFER_PRICE_RULE t
where t.product_id = a.PRODUCT_ID
and t.transfer_price_sys_id = 0.000000
and t.data_source_cd = 2)

Solution

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