Problem Summary:
Program - Daily Rates Import and Calculation program failing
while importing daily gl rates.
Program: Program - Daily Rates Import and Calculation
Error Log:
Error: Duplicate Row or Overlapping
Date Range found.
Resolution:
After Investigation come to know that program failing due to duplicate data entry exists in interface table due to that it’s failing.
After Running the Below Provided Delete Duplication
Transaction Script, program Run Normally.
SQL
Query to find Duplicate Entry:
SELECT
*
FROM
gl_daily_rates_interface t1
WHERE
(
t1.from_currency, t1.to_currency, t1.user_conversion_type,
t1.from_conversion_date, t1.to_conversion_date ) IN (
SELECT /*+
NO_MERGE */ t2.from_currency,
t2.to_currency,
t2.user_conversion_type,
t2.from_conversion_date,
t2.to_conversion_date
FROM
gl_daily_rates_interface t2
WHERE
mode_flag
IN ( 'I', 'D', 'T', 'N' )
GROUP BY
t2.from_currency,
t2.to_currency,
t2.user_conversion_type,
t2.from_conversion_date,
t2.to_conversion_date
HAVING
COUNT(*)
> 1
)
AND mode_flag IN (
'I', 'D', 'T', 'N' )
AND
t1.batch_number IS NULL
ORDER BY
1,
2,
3;
DELETE
Script for Deleting Duplicate Entry From Table:
DELETE FROM gl_daily_rates_interface t1
WHERE
(
t1.from_currency, t1.to_currency, t1.user_conversion_type,
t1.from_conversion_date, t1.to_conversion_date ) IN (
SELECT /*+
NO_MERGE */ t2.from_currency,
t2.to_currency,
t2.user_conversion_type,
t2.from_conversion_date,
t2.to_conversion_date
FROM
gl_daily_rates_interface t2
WHERE
mode_flag
IN ( 'I', 'D', 'T', 'N' )
GROUP BY
t2.from_currency,
t2.to_currency,
t2.user_conversion_type,
t2.from_conversion_date,
t2.to_conversion_date
HAVING
COUNT(*)
> 1
)
AND mode_flag IN (
'I', 'D', 'T', 'N' )
AND t1.batch_number IS NULL
;