Monday, 15 January 2018

Query to get the Profile Option Configurations

/* Formatted on 1/15/2018 9:40:58 AM (QP5 v5.114.809.3010) */
  SELECT   ot.user_profile_option_name,
           TO_CHAR (v.level_id) level_id,
           DECODE (v.level_id,
                   10001, 'Site',
                   10002, 'Application',
                   10003, 'Responsibility',
                   10004, 'User ID',
                   v.level_id)
              level_meaning,
           DECODE (v.level_id,
                   10001, 'Site',
                   10002, apl.application_name,
                   10003, frt.responsibility_name,
                   10004, u.user_name,
                   v.level_id)
              level_name,
           v.profile_option_value,
           o.profile_option_name,
           v.creation_date value_creation_date,
           v.created_by value_created_by,
           v.last_update_date value_last_updated_date,
           v.last_updated_by value_last_updated_by
    FROM   applsys.fnd_profile_options_tl ot,
           applsys.fnd_profile_options o,
           applsys.fnd_profile_option_values v,
           applsys.fnd_responsibility_tl frt,
           apps.fnd_application_vl apl,
           fnd_user u
   WHERE       v.level_value = frt.responsibility_id(+)
           AND v.profile_option_id = o.profile_option_id
           AND o.profile_option_name = ot.profile_option_name
           AND ot.language = 'US'
           AND NVL (frt.language, 'US') = 'US'
           AND v.level_value = apl.application_id(+)
           AND u.user_id(+) = v.level_value
ORDER BY   ot.user_profile_option_name,
           v.level_id,
           DECODE (v.level_id,
                   10001, 'Site',
                   10002, 'Application',
                   10003, frt.responsibility_name,
                   10004, u.user_name,
                   v.level_id);

20 comments:

  1. Hi all, Oracle technical and fusion cloud SCM online classes, if you are interested please whatsapp to this number +91 7382582893, thank you.

    ReplyDelete
  2. Thanks a lot very much for the high quality and results-oriented help. I won’t think twice to endorse your blog post to anybody who wants and needs support about this area.

    UI Path Training in Bangalore

    ReplyDelete
  3. Great post, you have pointed out some excellent points, I as well believe this is a very superb website. Important and famous people from throughout history born on this day. Search thousands of historical, noteworthy and Celebrity birthdays in our archives.

    ReplyDelete
  4. Your post is simply brilliant! I'm impressed by the way you've tackled the subject matter with such expertise and creativity. Your writing style is engaging and your ideas are thought-provoking. Asia Cup 2023: India vs Pakistan Dream11 Prediction Thank you for sharing your knowledge and insights. Can't wait to read more from you!

    ReplyDelete
  5. "Great explanation of how to join multiple tables in Oracle. This is really helpful for understanding complex queries!"
    modular workstation noida
    office chair in noida

    ReplyDelete
  6. "This query is a good example of using DECODE for conditional logic in SQL. Very insightful!"
    cafeteria-furniture noida
    Office Furniture manufacturer noida

    ReplyDelete
  7. "I have been looking for a way to extract profile options in Oracle. Thanks for this detailed query!"
    metal storage system in noida
    Warehouse Storage rack Delhi

    ReplyDelete
  8. "The use of NVL to handle null values is a smart approach. I’ll try to implement it in my own queries."
    Shrink Packing machine Delhi
    mezzanine floor Delhi

    ReplyDelete
  9. "This query is quite comprehensive! It's nice to see how different tables are joined to extract useful data."
    Shrink wrapping machine delhi
    mobile compactor in delhi

    ReplyDelete
  10. "Is there a way to optimize this query if the data grows significantly? Any tips on performance?"
    Box Wrapping machine delhi
    fifo flow rack manufacturer

    ReplyDelete
  11. "I love the use of aliases in this query. It makes it so much easier to follow the logic."
    Strapping machine in delhi
    heavy duty rack delhi

    ReplyDelete
  12. "I never thought of using TO_CHAR for converting level IDs. Thanks for the helpful tip!"
    franchise Expo
    Multi tier rack

    ReplyDelete
  13. "Great breakdown of the logic using DECODE. It really makes this query versatile for different levels."
    Fabric Roll Racks in Delhi
    Warehouse mezzzanine floor delhi

    ReplyDelete
  14. "It’s awesome how you combined multiple tables in one query to fetch application data. Really saves time!"
    Slotted Angle rack delhii
    Modular Mezzanine floor

    ReplyDelete
  15. "I have a similar table structure in my database. This query gives me a great starting point!"
    Dust Collector
    Pallet rack manufacturer

    ReplyDelete
  16. "Could you explain why you're using the (+ ) operator in the joins? I’d like to understand that better."
    Lavender Oil Manufacturer in germany
    Industrial Storage Rack Delhi

    ReplyDelete
  17. "The query is clear and concise. I’m going to adapt it for my own application!"
    Pallet Rack delhi
    Heavy Duty Rack Delhi

    ReplyDelete
  18. "The ORDER BY clause is a nice touch here. Sorting by multiple criteria ensures clean and organized results."
    warehouse storage rack delhi
    Dust Collector in Delhi

    ReplyDelete
  19. "I didn’t realize how fnd_user was being used to link user information to profile options. Great example!"
    Axial Flow Fans in delhi
    Dust Collector Manufacturer

    ReplyDelete
  20. "The query demonstrates best practices for combining profile options with user details. Thanks for sharing!"
    Pulse jet Dust Collector manufacturer
    Centrifugal Blower in delhi

    ReplyDelete