My Oracle Support Banner

YE20 Geocode Upgrade Manager in DEBUG Mode Having Performance Issue (Doc ID 2722373.1)

Last updated on OCTOBER 22, 2020

Applies to:

Oracle HRMS (US) - Version 12.1 HRMS RUP13 and later
Information in this document applies to any platform.

Symptoms

On : 12.1 HRMS RUP13 version, Geocode issues

ACTUAL BEHAVIOR
---------------
Geocode Upgrade Manager in DEBUG mode performance issue.  Most expensive SQL:

    INSERT INTO pay_us_geo_update (
        id,
        assignment_id,
        person_id,
        old_juri_code,
        new_juri_code,
        process_type,
        table_name,
        table_value_id,
        process_mode,
        process_date
    )
        SELECT DISTINCT
            :b11,
            pugu.assignment_id,
            pugu.person_id,
            :b7,
            :b6,
            :b5,
            'PAY_ELEMENT_ENTRY_VALUES_F',
            pee.element_entry_id,
            :b2,
            :b10
        FROM
            pay_us_geo_update       pugu,
            pay_element_entries_f   pee,
            pay_us_geo_update       pugu1
        WHERE
            pugu.id = :b3
            AND pugu.table_name = 'GEO_ASSIGNMENTS_IMPACT'
            AND pugu.assignment_id BETWEEN :b9 AND :b8
            AND pugu.old_juri_code = :b7
            AND pugu.new_juri_code = :b6
            AND pugu.process_type = :b5
            AND pugu.process_mode = :b2
            AND pugu.process_date = :b1
            AND pugu.description = :b4
            AND pugu.assignment_id = pee.assignment_id
            AND pugu1.table_value_id = pee.element_type_id
            AND pugu1.id = :b3
            AND pugu1.process_type = 'CACHE_INPUT_VALUES'
            AND pugu1.process_mode = :b2
            AND pugu1.process_date = :b1
            AND pugu1.table_name = 'GEO_JD_INPUT_VALUES'
            AND pugu1.description = 'Jurisdiction'
            AND EXISTS (
                SELECT
                    'X'
                FROM
                    pay_element_entry_values_f pev
                WHERE
                    pev.element_entry_id = pee.element_entry_id
                    AND pev.input_value_id = pugu1.assignment_id
                    AND pev.screen_entry_value = :b7
            )
STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Run Geocode Upgrade Manager in DEBUG mode

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution
References


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