/* 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);
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);
Hi all, Oracle technical and fusion cloud SCM online classes, if you are interested please whatsapp to this number +91 7382582893, thank you.
ReplyDeleteThanks 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.
ReplyDeleteUI Path Training in Bangalore
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.
ReplyDeleteYour 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"Great explanation of how to join multiple tables in Oracle. This is really helpful for understanding complex queries!"
ReplyDeletemodular workstation noida
office chair in noida
"This query is a good example of using DECODE for conditional logic in SQL. Very insightful!"
ReplyDeletecafeteria-furniture noida
Office Furniture manufacturer noida
"I have been looking for a way to extract profile options in Oracle. Thanks for this detailed query!"
ReplyDeletemetal storage system in noida
Warehouse Storage rack Delhi
"The use of NVL to handle null values is a smart approach. I’ll try to implement it in my own queries."
ReplyDeleteShrink Packing machine Delhi
mezzanine floor Delhi
"This query is quite comprehensive! It's nice to see how different tables are joined to extract useful data."
ReplyDeleteShrink wrapping machine delhi
mobile compactor in delhi
"Is there a way to optimize this query if the data grows significantly? Any tips on performance?"
ReplyDeleteBox Wrapping machine delhi
fifo flow rack manufacturer
"I love the use of aliases in this query. It makes it so much easier to follow the logic."
ReplyDeleteStrapping machine in delhi
heavy duty rack delhi
"I never thought of using TO_CHAR for converting level IDs. Thanks for the helpful tip!"
ReplyDeletefranchise Expo
Multi tier rack
"Great breakdown of the logic using DECODE. It really makes this query versatile for different levels."
ReplyDeleteFabric Roll Racks in Delhi
Warehouse mezzzanine floor delhi
"It’s awesome how you combined multiple tables in one query to fetch application data. Really saves time!"
ReplyDeleteSlotted Angle rack delhii
Modular Mezzanine floor
"I have a similar table structure in my database. This query gives me a great starting point!"
ReplyDeleteDust Collector
Pallet rack manufacturer
"Could you explain why you're using the (+ ) operator in the joins? I’d like to understand that better."
ReplyDeleteLavender Oil Manufacturer in germany
Industrial Storage Rack Delhi
"The query is clear and concise. I’m going to adapt it for my own application!"
ReplyDeletePallet Rack delhi
Heavy Duty Rack Delhi
"The ORDER BY clause is a nice touch here. Sorting by multiple criteria ensures clean and organized results."
ReplyDeletewarehouse storage rack delhi
Dust Collector in Delhi
"I didn’t realize how fnd_user was being used to link user information to profile options. Great example!"
ReplyDeleteAxial Flow Fans in delhi
Dust Collector Manufacturer
"The query demonstrates best practices for combining profile options with user details. Thanks for sharing!"
ReplyDeletePulse jet Dust Collector manufacturer
Centrifugal Blower in delhi