3 Replies Latest reply: Oct 31, 2013 7:25 AM by Ajay Sharma RSS

    Need iExpenses queries.

    Ajay Sharma

      Hi,

       

      I am working on iExpense Responsibility.

       

      I want to know that while creating new Expense Report from where the value of "Expense Template" and "Cost Centre" gets populated and how ?

       

       

      Thanks & Regards,

      Ajay Sharma

        • 2. Re: Need iExpenses queries.
          Ajay Sharma

          Hi,

           

          I am not getting anything useful here.

           

          For Expense template I looked into the queries through Oracle forms.

           

          SELECT PROMPT,END_DATE,ITEMIZATION_ALLOWED,PARAMETER_ID,EXPENSE_REPORT_ID

              FROM AP_WEB_EXP_TYPE_ITEM_V

          above query return only two values

          • Personal
          • Exchange Rate Rounding

          But when I am trying to create expense report the default Expense template is "Travel".

           

          Please tell me how this is getting populated.

           

           

          Thanks & Regards,

          Ajay Sharma

          • 3. Re: Need iExpenses queries.
            Ajay Sharma

            Hi,

             

            I searched about getting Cost Centre LOV Query and Created following code.

             

            Select  fvl.flex_value_set_id,
                    fvl.flex_value,
                    fvl.Flex_value_meaning,
                    fvl.Description
                    /*sob.name sob_name,
                    fifst.id_flex_structure_name struct_name,
                    ifs.segment_name,
                    ffvs.flex_value_set_name,
                    ffvs.flex_value_set_id*/
            from   FND_FLEX_VALUES_VL fvl,
                   fnd_id_flex_structures fifs
            ,      fnd_id_flex_structures_tl fifst
            ,      fnd_segment_attribute_values  sav1
            ,      fnd_segment_attribute_values sav2
            ,      fnd_segment_attribute_values sav3
            ,      fnd_segment_attribute_values  sav4
            ,      fnd_segment_attribute_values sav5
            ,      fnd_segment_attribute_values sav6
            ,      fnd_id_flex_segments ifs
            ,      fnd_flex_value_sets ffvs
            ,      gl_sets_of_books sob
            ,      HR_OPERATING_UNITS hru
            where  1=1
            and    fvl.FLEX_VALUE_SET_ID = ffvs.FLEX_VALUE_SET_ID
            and    sob.SET_OF_BOOKS_ID =hru.SET_OF_BOOKS_ID 
            and    hru.ORGANIZATION_ID = 324 -- ORG_ID
            and    fvl.SUMMARY_FLAG='N'
            and    fifs.id_flex_code = 'GL#'
            and    fifs.application_id =  fifst.application_id
            and    fifs.id_flex_code = fifst.id_flex_code
            and    fifs.id_flex_num = fifst.id_flex_num
            and    fifs.application_id =  ifs.application_id
            and    fifs.id_flex_code = ifs.id_flex_code
            and    fifs.id_flex_num = ifs.id_flex_num
            and    sav1.application_id =  ifs.application_id
            and    sav1.id_flex_code = ifs.id_flex_code
            and    sav1.id_flex_num = ifs.id_flex_num
            and    sav1.application_column_name =  ifs.application_column_name
            and    sav2.application_id =  ifs.application_id
            and    sav2.id_flex_code = ifs.id_flex_code
            and    sav2.id_flex_num = ifs.id_flex_num
            and    sav2.application_column_name =  ifs.application_column_name
            and    sav3.application_id =  ifs.application_id
            and    sav3.id_flex_code = ifs.id_flex_code
            and    sav3.id_flex_num = ifs.id_flex_num
            and    sav3.application_column_name =  ifs.application_column_name
            and    sav4.application_id =  ifs.application_id
            and    sav4.id_flex_code = ifs.id_flex_code
            and    sav4.id_flex_num = ifs.id_flex_num
            and    sav4.application_column_name =  ifs.application_column_name
            and    sav5.application_id =  ifs.application_id
            and    sav5.id_flex_code = ifs.id_flex_code
            and    sav5.id_flex_num = ifs.id_flex_num
            and    sav5.application_column_name =  ifs.application_column_name
            and    sav6.application_id =  ifs.application_id
            and    sav6.id_flex_code = ifs.id_flex_code
            and    sav6.id_flex_num = ifs.id_flex_num
            and    sav6.application_column_name =  ifs.application_column_name
            and    sav1.segment_attribute_type =  'GL_BALANCING'
            and    sav2.segment_attribute_type = 'FA_COST_CTR'
            and    sav3.segment_attribute_type = 'GL_ACCOUNT'
            and    sav4.segment_attribute_type  = 'GL_INTERCOMPANY'
            and    sav5.segment_attribute_type =  'GL_SECONDARY_TRACKING'
            and    sav6.segment_attribute_type =  'GL_GLOBAL'
            and    sav2.attribute_value = 'Y'
            --and    ffvs.flex_value_set_name in('Department','Cost Centre') --OR ffvs.flex_value_set_name like '%Cost centre%' 
            --and    ffvs.flex_value_set_name like '%Cost centre%' 
            --and    sav6.segment_attribute_type like  '%UK%'
            and    ifs.id_flex_num = sob.chart_of_accounts_id
            and    ifs.flex_value_set_id = ffvs.flex_value_set_id
            -- comment the next expression to show all books
            -- currently it show the info for the site level set profile option value
            and    sob.set_of_books_id = nvl(fnd_profile.value('GL_SET_OF_BKS_ID'),sob.set_of_books_id)
            order by  fvl.Flex_value_meaning       
            --order by  sob.name, sob.chart_of_accounts_id, ifs.application_column_name;        
            

             

            but I am getting result only for Vision Operation. When I tried this for 'Vision UK' I am not getting any LOV.

             

            Please help me in this