12 Replies Latest reply on Jun 9, 2015 2:59 AM by Beauty_and_dBest

    FND DIRECT RESPONSIBILTY  LOV

    Beauty_and_dBest

      Hi all,

       

      EBS R12.2.4

       

      How do  I get the name of the lookup table that is selected in the FND_RESPONSIBLITY form?

      Capture2.PNG

       

      Note that there is typo in the lookup table as in > "Global Interfund System Supre User".

      Can I update this table and change "Supre" to "Super"?

       

      I tried to describe the table FND_RESPONSIBILITY but the columns responsibility_name and application_name does not exist

      Capture3.PNG

       

      Is it possible to open the source code of this form and check the LOV?

       

      Please help...

       

      Thanks.

        • 1. Re: FND_RESPONSIBILTY
          Pk

          Go to Help--> Record History for the view/table name.

           

          This is the oracle provided responsibility name, hence it is not suggested to change the name.

           

          Alternately you can create a custom responsibility and use the name you like.

           

          For checking from the table, please use the below  SQL.

           

            select * from fnd_responsibility_tl

            where responsibility_name = 'Global Interfund System Supre User'

          1 person found this helpful
          • 2. Re: FND_RESPONSIBILTY
            aJohny

            This is the list of Responsibilities. Go to the Responsibility Definition Screen and search for that.


            I think the navigation is

            System Administrator

               Security --> Responsibility


            Don't try to update any of the standard tables. There will be dependent tables and it will break, unless you know all the dependencies.


            Cheers

            AJ

            1 person found this helpful
            • 3. Re: FND_RESPONSIBILTY
              Beauty_and_dBest

              Thanks PK & AJ,

               

              I tried that  record history, but I got error:

              Capture.PNG

               

              I want to learn now did you know that the table is > fnd_responsibility_tl?

              How do you open the source frm?

               

              Thanks a lot.

              • 4. Re: FND_RESPONSIBILTY
                Pk

                Query with Any user(F11) and then (Control F11)

                then you would be able to see the actual view name. This would give you the view name for this form.

                 

                fnd_responsibility_tl is the base table which stores all the responsibility names.

                 

                For knowing more about the table names, download the FND table schema tables from the below link.

                 

                etrm,oracle.com

                1 person found this helpful
                • 5. Re: FND_RESPONSIBILTY
                  aJohny

                  To get some ideas about the responsibility tables, have a read on the responses in the below thread :

                  Significance of S,TL,VL.

                   

                   

                  FND_RESPONSIBILITY : Single Sign-On

                  FND_RESONSIBILITY_TL: Single Sign-On

                  FND_RESPONSIBILITY_VL

                   

                  This blog also help.

                  http://oratech2.blogspot.com.au/2011/07/responsibilities.html

                   

                  Cheers

                  AJ

                  1 person found this helpful
                  • 6. Re: FND_RESPONSIBILTY
                    Beauty_and_dBest

                    Thanks PK, AJ

                     

                    Actually the table that it gives is FND_USER as base table.

                    I am really confused how to show up all the underlying tables that are associated within the form.

                    Sometimes it shows and sometimes it does not.

                    • 7. Re: FND_RESPONSIBILTY
                      Pk

                      From the screen after you query you need to select the responsibility level block and check the Record history.

                       

                      Below is the list of tables for sorting the user and responsibility assignment.

                       

                      FND_USER_RESP_GROUPS_ALL

                      FND_USER

                      FND_APPLICATION_VL

                      FND_RESPONSIBILITY_TL

                      FND_SECURITY_GROUPS

                       

                       

                      Below is the query which will help you to find the list of responsibilities assigned. (Provide the user_name when you run the SQL)

                       

                      SELECT

                        FRTL.responsibility_name

                      , FU.USER_NAME

                      FROM

                      FND_USER_RESP_GROUPS_ALL FURGA,

                      FND_USER FU,

                      FND_USER FUCB,

                      FND_USER FULUB,

                      FND_USER FULUL,

                      FND_APPLICATION_VL FA,

                      FND_RESPONSIBILITY_TL FRTL,

                      FND_SECURITY_GROUPS FSG

                      WHERE

                      FURGA.USER_ID = FU.USER_ID

                      AND FURGA.CREATED_BY = FUCB.USER_ID

                      AND FURGA.LAST_UPDATED_BY = FULUB.USER_ID

                      AND FURGA.LAST_UPDATE_LOGIN = FULUL.USER_ID

                      AND FURGA.RESPONSIBILITY_APPLICATION_ID = FA.APPLICATION_ID

                      AND FURGA.RESPONSIBILITY_ID = FRTL.RESPONSIBILITY_ID

                      AND FRTL.LANGUAGE = 'US'

                      AND FURGA.SECURITY_GROUP_ID = FSG.SECURITY_GROUP_ID

                      AND FU.USER_NAME = :p_user_name

                      ;

                      1 person found this helpful
                      • 8. Re: FND_RESPONSIBILTY
                        Beauty_and_dBest

                        Thanks PK, AJ

                         

                         

                        But you can not use the query above because the username is blank or null? See the example in first page. Username is blank/null but it has direct responsibility child table.

                        Is should not be allowed as a business rule constraint. How can a null parent key be referenced by the child table?

                         

                        I also queried the fnd_responsibility_tl but it does not show the application_name (being shown in the LOV)

                         

                        Capture.PNG

                         

                        I also queried the fnd_responsibility_vl but it does not show the application_name as well (being shown in the LOV)

                        Capture2.PNG


                        I think the best way is to open the source code on how the LOV is being selected?



                        Thanks.

                        • 9. Re: FND_RESPONSIBILTY
                          Pk

                          User name Blank will never have the responsibility, it has to be assigned to some user. Else it means it was not a saved record.

                           

                          In the above mentioned query, 101 represents the application_id. If you want the application name then we need to join the fnd_application_vl view.

                          I have modified the SQL for application name.

                           

                          select fav.application_name,frt.*

                          from fnd_responsibility_tl frt, FND_APPLICATION_VL fav

                            where frt.responsibility_name = 'Global Interfund System Supre User'

                            AND frt.application_id = fav.application_id

                           

                          Please login to the below link, where we have table definitions with ER relationship.Select FND as the application for these tables

                           

                          etrm.oracle.com

                          1 person found this helpful
                          • 10. Re: FND_RESPONSIBILTY  LOV
                            Beauty_and_dBest

                            Thanks PK,

                             

                            I compared the output of your query against the LOV, but it has more rows(maybe garbage or invalid) than the LOV in the form.

                            And besides the query is slow to run while the LOV is fast

                             

                            Capture.PNG

                            Capture2.PNG

                             

                            I am really curious how does the LOV query looks like

                            Are we allowed to open the form source code?

                             

                            Thanks again.

                            • 11. Re: FND_RESPONSIBILTY  LOV
                              Pk

                              SQL would provide you indirect responsibilities as well apart from direct responsibilities list, please refer the below link for more information.

                               

                              Search Users Window

                               

                              https://docs.oracle.com/cd/E18727_01/doc.121/e12843/T156458T156461.htm#I_fndscrsp

                              1 person found this helpful
                              • 12. Re: FND_RESPONSIBILTY  LOV
                                Beauty_and_dBest

                                Thanks PK