Customer Interface Performance Issue

Customer Interface (RACUST) for a large number of records run extremely slow. Please do the followings, hopefully performance should improve.

1) Run "Gather Schema Statistics"/"Gather Table Statistics" program for AR from System Administrator Responsibility.
   > You can use fnd_stats/dbms_stats to gather schema/table statistics if you want to do it from back end.

2) Set HZ: Gather Table Stats profile value to 'Yes' at site level
   > Navigation: System Administrator > Profile > System
                       > query for profile 'HZ: Gather Table Stats'

3) Set the profile option 'HZ: Number of Workers Used by Customer Interface'  to a high value like 6 at the Site level.

4) Run "Customer Interface Master Conc Program" (RACUSTMA) instead of "Customers Interface".

New Customers/Customer changes not visible from application(R12)

Sometimes when you use open interface (Customer Interface) or HZ APIs to create or amend Customers, it is not visible from front end, though you can see the changes from back end.

DQM Synchronization program (DQM Serial Sync Index Program (DQM Serial Sync Index Program)) is submitted automatically when a user updates any customer information.

This program is for Synchronizing the party records from the HZ tables into the DQM indexed tables which is what you query against when you search in the application.

DQM indexed tables are:
HZ_PARTIES = HZ_STAGED_PARTIES
HZ_PARTY_SITES = HZ_STAGED_PARTY_SITES
HZ_CONTACTS = HZ_STAGED_CONTACTS
HZ_CONTACT_POINTS = HZ_STAGED_CONTACT_POINTS

In R12, the DQM Synchronization can be set to: Automatic/Batch/Disabled

> Navigation: Customers Online Data Librarian Super User/Trading Community Manager > Administration Tab > DQM tab > DQM Synchronization Method

> Further more, when one sync concurrent request is in pending status, no other sync concurrent request would even be submitted. If a party is created while a sync concurrent program is already running, the DQM Serial Sync Index Program was not run for the newly created party.

To overcome this, you can set the profile option at site level:
HZ: Ignore Concurrent Request Limits for DQM Synchronization
If it is set to yes,it will run the DQM Serial Sync Index Program irrespective of the other sync concurrent programs.

Ref: Metalink Note [ID 465993.1]

Even after above changes, if it does not run automatically,

1> Clear the Global Cache
  >> Navigation: Functional Administrator > Core Services > Caching Framework
                         > Global Configuration > Clear All Cache > Ignore warning message.

2> Please run the "DQM Synchronization Program" manually from "Trading Community Manager" responsibility.

Script to get profile option values at different levels

>> Please provide the User Profile Option Name as an Input.

------------------------
-----------
select fpo.profile_option_name "Profile Option Name"
        , fpot.user_profile_option_name "User Profile Option Name"
        , fpot.description
        , fpo.start_date_active "Start Active Date"
        , fpo.end_date_active "End Date Active"
        , fpo.creation_date "Creation Date"
        , fu.user_name "Created By"
        , 'Site' "Level"
        , 'SITE' "Level Value"
        , fpov.profile_option_value "Profile Option Value"
    from fnd_profile_options_tl fpot
       , fnd_profile_options fpo
       , fnd_profile_option_values fpov
       , fnd_user fu
   where fpot.user_profile_option_name like '%'||:profile_name||'%'
     and fpot.profile_option_name = fpo.profile_option_name
     and fpo.application_id = fpov.application_id
     and fpo.profile_option_id = fpov.profile_option_id
     and fpo.created_by = fu.user_id
     and fpot.language = userenv('Lang')
     and fpov.level_id = 10001 /* site level */
union all
   select fpo.profile_option_name "Profile Option Name"
        , fpot.user_profile_option_name "User Profile Option Name"
        , fpot.description
        , fpo.start_date_active "Start Active Date"
        , fpo.end_date_active "End Date Active"
        , fpo.creation_date "Creation Date"
        , fu.user_name "Created By"
        , 'Appl' "Level"
        , fa.application_name "Level Value"
        , fpov.profile_option_value "Profile Option Value"
   from fnd_profile_options_tl fpot
       , fnd_profile_options fpo
       , fnd_profile_option_values fpov
       , fnd_user fu
       , fnd_application_tl fa
  where fpot.user_profile_option_name like '%'||:profile_name||'%'
    and fpot.profile_option_name = fpo.profile_option_name
    and fpo.profile_option_id = fpov.profile_option_id
    and fpo.created_by = fu.user_id
    and fpot.language = userenv('Lang')
    and fpov.level_id = 10002 /* application level */
    and fpov.level_value = fa.application_id
union all
  select fpo.profile_option_name "Profile Option Name"
        , fpot.user_profile_option_name "User Profile Option Name"
        , fpot.description
        , fpo.start_date_active "Start Active Date"
        , fpo.end_date_active "End Date Active"
        , fpo.creation_date "Creation Date"
        , fu.user_name "Created By"
        , 'Resp' "Level"
        , frt.responsibility_name "Level Value"
        , fpov.profile_option_value "Profile Option Value"
   from fnd_profile_options_tl fpot
       , fnd_profile_options fpo
       , fnd_profile_option_values fpov
       , fnd_user fu
       , fnd_responsibility_tl frt
  where fpot.user_profile_option_name like '%'||:profile_name||'%'
    and fpot.profile_option_name = fpo.profile_option_name
    and fpo.profile_option_id = fpov.profile_option_id
    and fpo.created_by = fu.user_id
    and frt.language = userenv('Lang')
    and fpot.language = userenv('Lang')
    and fpov.level_id = 10003 /*responsibility level */
    and fpov.level_value = frt.responsibility_id
    and fpov.level_value_application_id = frt.application_id
union all
select fpo.profile_option_name "Profile Option Name"
        , fpot.user_profile_option_name "User Profile Option Name"
       , fpot.description
       , fpo.start_date_active "Start Active Date"
       , fpo.end_date_active "End Date Active"
       , fpo.creation_date "Creation Date"
       , fu.user_name "Created By"
       , 'User' "Level"
       , fu2.user_name "Level Value"
       , fpov.profile_option_value "Profile Option Value"
   from fnd_profile_options_tl fpot
       , fnd_profile_options fpo
       , fnd_profile_option_values fpov
       , fnd_user fu
       , fnd_user fu2
  where fpot.user_profile_option_name like '%'||:profile_name||'%'
    and fpot.profile_option_name = fpo.profile_option_name
    and fpo.profile_option_id = fpov.profile_option_id
    and fpo.created_by = fu.user_id
    and fpov.level_id = 10004 /* user level */
    and fpov.level_value = fu2.user_id
    and fpot.language = userenv('Lang')
order by  "User Profile Option Name", "Level", "Level Value"