EBS - Program - Daily Rates Import and Calculation program Error: Duplicate Row or Overlapping Date Range found

 

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

;