6 Replies Latest reply on Feb 1, 2018 11:41 AM by 3173025

    Not able to get data in the applications for a discoverer report

    3173025

      Hi all,

       

      I have a discoverer report, when i the report in disco desktop, able to get the data.

       

      When i assign to particular username and responsibility and login with that username in application and run the report, im not getting the data for the same report.

       

      Note: No parameters/username is passed to the report query.

       

      Could some one help on this.

      Thanks

        • 1. Re: Not able to get data in the applications for a discoverer report
          John_K

          Can you post the SQL statement the report is using (run it in desktop and then extract the SQL Statement).

          • 2. Re: Not able to get data in the applications for a discoverer report
            3173025

            Before we used to get the extract in the applications. But from few days, we are not getting. For other reports it is fine.

             

            Query is as below:

            -------------------------

            SELECT DISTINCT XXSY.XXSY_HR_GET_JOBCODE_SF(O100639.JOB_NAME,O100639.GRADE_NAME,O100639.MANAGER_FLAG),

              APPS.XXSY_GET_LOOKUP('XXSY_TIMEZONE',O100639.LOCATION_NAME),

              NVL(O100639.PREFERRED_NAME,O100639.FIRST_NAME),

              LOWER(APPS.XXSY_GET_USERNAME_ACTIVE(O100639.PERSON_ID)),

              DECODE(O100639.GRADE_NAME,'9999 - Inactive Employee','inactive','active'),

              APPS.XXSY_HR_GET_FIELD(O100639.JOB_NAME,'.',3),

              O100639.GRADE_NAME,

              DECODE(FLT.DESCRIPTION,'Japan','ja_JP','en_US'),

              APPS.XXSY_GET_LOOKUP('XXSY_COUNTRY',O100639.LOCATION_NAME),

              O100639.ONBOARDING_ID,

              O100639.HIRE_DATE,

              O100636.ADDRESS_LINE1,

              O100636.ADDRESS_LINE2,

              O100636.CITY,

              O100639.EMPLOYEE_NUMBER,

              O100639.EMPLOYMENT_CATEGORY,

              O100639.LOCATION_NAME,

              O100639.ORGANIZATION_NAME,

              O100636.POSTAL_CODE,

              O100636.REGION_2,

              O100639.LAST_NAME,

              O100639.EMAIL_ADDRESS,

              O100639.WORKING_TITLE,

              O100639.SUPERVISOR_NUMBER,

              O100639.MIDDLE_NAMES,

              O100639.GENDER_CODE,

              FLT.DESCRIPTION,

              O100639.HRBP_EMP_NUMBER,

              O100639.SVP_NAME,

              'SSO',

              'N/A',

              NULL,

              NULL,

              NULL,

              NULL,

              NULL,

              APPS.XXSY_HR_GET_FIELD(O100639.JOB_NAME,'.',2),

              APPS.XXSY_HR_GET_FIELD(O100639.JOB_NAME,'.',1),

              NULL,

              NULL,

              NULL,

              NULL,

              NULL,

              NULL,

              NULL,

              NULL

            FROM APPS.XXSY_HR_ADDRESSES_DIS_V O100636,

              APPS.XXSY_HR_HIRES_DIS_V O100639,

              APPS.FND_LOOKUP_VALUES_VL FLT

            WHERE ( ( O100639.PERSON_ID                                        = O100636.PERSON_ID(+) )

            AND ( O100639.LOCATION_NAME                                        = FLT.MEANING ) )

            AND ( ( LOWER(APPS.XXSY_GET_USERNAME_ACTIVE(O100639.PERSON_ID)) ) IS NOT NULL )

            AND ( O100639.EMPLOYEE_NUMBER                                     <> 820 )

            AND ( O100636.PRIMARY_FLAG_CODE(+)                                 = 'Y' )

            AND ( O100639.FIRST_NAME                                          <> 'Board Of Directors' )

            AND ( O100639.USER_PERSON_TYPE                                     = 'Employee' )

            AND FLT.LOOKUP_TYPE                                                = 'XXSY_HR_LOCATION_REGION_SF'

            ORDER BY O100639.EMPLOYEE_NUMBER ASC ;

            • 3. Re: Not able to get data in the applications for a discoverer report
              John_K

              Is your language set correctly? "FND_LOOKUP_VALUES_VL" will only return rows if you have your NLS settings correct, i.e. set to "American" (or whatever lang you use) - UK organizations tend to use US.

               

              Does "XXSY_GET_USERNAME_ACTIVE(O100639.PERSON_ID)" definitely return a value?

              • 4. Re: Not able to get data in the applications for a discoverer report
                3173025

                NLS settings are as below. Anyway, the extract is not coming in the application when we login with the username, not in the SQL developer. in SQL developer, when i run the same query, getting the records.

                 

                yes, get username active will return a value.

                 

                • 5. Re: Not able to get data in the applications for a discoverer report
                  John_K

                  Yes - so is the application setting the NLS settings correctly?

                  • 6. Re: Not able to get data in the applications for a discoverer report
                    3173025

                    Can you please show the navigation where to set the NLS in applications?

                     

                    Thanks