Some Useful SQL Queries

-
Query to search Responsibility by Concurrent program name
SELECT frt.responsibility_name,                frg.request_group_name,                frg.description  FROM fnd_request_groups frg             ,fnd_request_group_units frgu             ,fnd_concurrent_programs fcp             ,fnd_concurrent_programs_tl fcpt             ,fnd_responsibility_tl frt             ,fnd_responsibility frs WHERE frgu.unit_application_id = fcp.application_id AND   frgu.request_unit_id = fcp.concurrent_program_id AND   frg.request_group_id = frgu.request_group_id AND   frg.application_id = frgu.application_id AND   fcpt.source_lang = USERENV('LANG') AND   fcp.application_id = fcpt.application_id AND   fcp.concurrent_program_id = fcpt.concurrent_program_id AND   frs.application_id = frt.application_id AND   frs.responsibility_id = frt.responsibility_id AND   frt.source_lang = USERENV('LANG') AND   frs.request_group_id = frg.request_group_id AND   frs.application_id = frg.application_id AND   fcp.concurrent_program_name = <shortname> AND   fcpt.user_concurrent_program_name LIKE <User concurrent program>>


Query to get the request group for a given responsibilitySELECT DISTINCT FRT.RESPONSIBILITY_NAME,                FMT.USER_MENU_NAME,                FAT.APPLICATION_NAME,                (SELECT REQUEST_GROUP_NAME
                   FROM APPS.FND_REQUEST_GROUPS FRG
                  WHERE FRG.APPLICATION_ID = FR.APPLICATION_ID
                    AND FRG.REQUEST_GROUP_ID = FR.REQUEST_GROUP_ID) REQUET_GRP
  FROM APPS.FND_RESPONSIBILITY_TL FRT,       APPS.FND_RESPONSIBILITY    FR,       APPS.FND_MENUS_TL          FMT,       APPS.FND_MENUS             FM,       APPS.FND_APPLICATION_TL    FAT,       APPS.FND_APPLICATION       F FA WHERE FRT.RESPONSIBILITY_ID(+) = FR.RESPONSIBILITY_ID
   AND FRT.RESPONSIBILITY_NAME = '&Responsibility name'   AND FR.MENU_ID = FMT.MENU_ID
   AND FR.MENU_ID = FM.MENU_ID
   AND FAT.APPLICATION_ID = FA.APPLICATION_ID
   AND FA.APPLICATION_ID = FR.APPLICATION_ID
   AND FRT.LANGUAGE = 'US'   AND FAT.LANGUAGE = 'US';
Query 1: Select responsibility name along with application nameSELECT application_short_name ,frt.responsibility_id, frt.responsibility_nameFROM apps.fnd_responsibility_tl frt , fnd_application faWHERE fa.application_id = frt.application_id; Query 2: Get Menu name for Responsibility ID , You can find out responsibility_id from Query 1SELECT DISTINCT a.responsibility_name, c.user_menu_nameFROM apps.fnd_responsibility_tl a,apps.fnd_responsibility b,apps.fnd_menus_tl c,apps.fnd_menus d,apps.fnd_application_tl e,apps.fnd_application fWHERE a.responsibility_id(+) = b.responsibility_idAND a.responsibility_id = &resp_idAND b.menu_id = c.menu_idAND b.menu_id = d.menu_idAND e.application_id = f.application_idAND f.application_id = b.application_idAND a.LANGUAGE = 'US'; Query 3: Get User name and related assigned responsibilitiesSELECT distinct u.user_id, u.user_name user_name,r.responsibility_name responsiblity,a.application_name applicationFROM fnd_user u,fnd_user_resp_groups g,fnd_application_tl a,fnd_responsibility_tl rWHERE g.user_id(+) = u.user_idAND g.responsibility_application_id = a.application_idAND a.application_id = r.application_idAND g.responsibility_id = r.responsibility_idorder by 1;Query 4: Get Request Group associate with Responsibility NameSELECT responsibility_name responsibility, request_group_name,frg.descriptionFROM fnd_request_groups frg, fnd_responsibility_vl frvWHERE frv.request_group_id = frg.request_group_idORDER BY responsibility_name

Query 5: Gets Form personalization listingPersonalization is feature available in 11.5.10.X. For More detail on form Personalization Use Following Tables (Rule_id) is reference key for these tablesapplsys.fnd_form_custom_actions, applsys.fnd_form_custom_scopesSELECT ffft.user_function_name “User Form Name”, ffcr.SEQUENCE,ffcr.description, ffcr.rule_type, ffcr.enabled, ffcr.trigger_event,ffcr.trigger_object, ffcr.condition, ffcr.fire_in_enter_queryFROM fnd_form_custom_rules ffcr, fnd_form_functions_vl ffftWHERE ffcr.ID = ffft.function_idORDER BY 1;

Query 6: Query to view the patch level status of all modulesSELECT a.application_name,DECODE (b.status, ‘I’, ‘Installed’, ‘S’, ‘Shared’, ‘N/A’) status,patch_levelFROM apps.fnd_application_vl a, apps.fnd_product_installations bWHERE a.application_id = b.application_id;

Query 7: SQL to view all request who have attached to a responsibilitySELECT responsibility_name , frg.request_group_name,fcpv.user_concurrent_program_name, fcpv.descriptionFROM fnd_request_groups frg,fnd_request_group_units frgu,fnd_concurrent_programs_vl fcpv,fnd_responsibility_vl frvWHERE frgu.request_unit_type = ‘P’AND frgu.request_group_id = frg.request_group_idAND frgu.request_unit_id = fcpv.concurrent_program_idAND frv.request_group_id = frg.request_group_idORDER BY responsibility_name;Query 8: SQL to view all requests who have attached to a responsibilitySELECT responsibility_name , frg.request_group_name,fcpv.user_concurrent_program_name, fcpv.descriptionFROM fnd_request_groups frg,fnd_request_group_units frgu,fnd_concurrent_programs_vl fcpv,fnd_responsibility_vl frvWHERE frgu.request_unit_type = ‘P’AND frgu.request_group_id = frg.request_group_idAND frgu.request_unit_id = fcpv.concurrent_program_idAND frv.request_group_id = frg.request_group_idORDER BY responsibility_name;Query 9: SQL to view all types of request Application wiseSELECT fa.application_short_name,fcpv.user_concurrent_program_name,description,DECODE (fcpv.execution_method_code,‘B’, ‘Request Set Stage Function’,‘Q’, ‘SQL*Plus’,‘H’, ‘Host’,‘L’, ‘SQL*Loader’,‘A’, ‘Spawned’,‘I’, ‘PL/SQL Stored Procedure’,‘P’, ‘Oracle Reports’,‘S’, ‘Immediate’,fcpv.execution_method_code) exe_method,output_file_type, program_type, printer_name,minimum_width,minimum_length, concurrent_program_name,concurrent_program_idFROM fnd_concurrent_programs_vl fcpv, fnd_application faWHERE fcpv.application_id = fa.application_idORDER BY descriptionQuery 10: SQL to view concurrent request processing time, quite usefulSELECT f.request_id , pt.user_concurrent_program_name user_concurrent_program_name, f.actual_start_date actual_start_date, f.actual_completion_date actual_completion_date,floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)|| ‘ HOURS ‘ ||floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) -floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60)|| ‘ MINUTES ‘ ||round((((f.actual_completion_date-f.actual_start_date)*24*60*60) -floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600 -(floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) -floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60)*60) ))|| ‘ SECS ‘ time_difference,DECODE(p.concurrent_program_name,’ALECDC’,p.concurrent_program_name||’['||f.description||']‘,p.concurrent_program_name) concurrent_program_name, decode(f.phase_code,’R',’Running’,'C’,'Complete’,f.phase_code) Phase, f.status_codeFROM apps.fnd_concurrent_programs p, apps.fnd_concurrent_programs_tl pt, apps.fnd_concurrent_requests fWHERE f.concurrent_program_id = p.concurrent_program_idand f.program_application_id = p.application_idand f.concurrent_program_id = pt.concurrent_program_idand f.program_application_id = pt.application_idAND pt.language = USERENV(‘Lang’)and f.actual_start_date is not nullORDER by f.actual_completion_date-f.actual_start_date desc;


SQL Queries for checking Profile Option Values
The following queries are useful to get the profile option values of a profile option at site, application, responsibility and user level
1) Obtain Profile Option values for Profile Option name like ‘%Ledger%’ and  Responsibility name like ‘%General%Ledger%’
SELECT
substr(pro1.user_profile_option_name,1,35) Profile,
decode(pov.level_id,
10001,'Site',
10002,'Application',
10003,'Resp',
10004,'User') Option_Level,
decode(pov.level_id,
10001,'Site',
10002,appl.application_short_name,
10003,resp.responsibility_name,
10004,u.user_name) Level_Value,
nvl(pov.profile_option_value,'Is Null') Profile_option_Value
FROM 
fnd_profile_option_values pov,
fnd_responsibility_tl resp,
fnd_application appl,
fnd_user u,
fnd_profile_options pro,
fnd_profile_options_tl pro1
WHERE
pro1.user_profile_option_name like ('%Ledger%')
and  pro.profile_option_name = pro1.profile_option_name
and  pro.profile_option_id = pov.profile_option_id
and  resp.responsibility_name like '%General%Ledger%' /* comment this line  if you need to check profiles for all responsibilities */
and  pov.level_value = resp.responsibility_id (+)
and  pov.level_value = appl.application_id (+)
and  pov.level_value = u.user_id (+)
order by 1,2;

2) Obtain all Profile Option values setup for a particular responsibility. Replace the responsibility name as per your requirement.
SELECT
substr(pro1.user_profile_option_name,1,35) Profile,
decode(pov.level_id,
10001,'Site',
10002,'Application',
10003,'Resp',
10004,'User') Option_Level,
decode(pov.level_id,
10001,'Site',
10002,appl.application_short_name,
10003,resp.responsibility_name,
10004,u.user_name) Level_Value,
nvl(pov.profile_option_value,'Is Null') Profile_option_Value
FROM 
fnd_profile_option_values pov,
fnd_responsibility_tl resp,
fnd_application appl,
fnd_user u,
fnd_profile_options pro,
fnd_profile_options_tl pro1
WHERE
pro.profile_option_name = pro1.profile_option_name
and  pro.profile_option_id = pov.profile_option_id
and  resp.responsibility_name like '%General%Ledger%'
and  pov.level_value = resp.responsibility_id (+)
and  pov.level_value = appl.application_id (+)
and  pov.level_value = u.user_id (+)
order by 1,2;


Similarly, you can tweak the above queries to obtain Profile Option Values set for a particular User or a particular application.



Query to find link between OU and Inv


SELECT hou.NAME operating_unit_name, hou.short_code,
hou.organization_id operating_unit_id, hou.set_of_books_id,
hou.business_group_id,
ood.organization_name inventory_organization_name,
ood.organization_code Inv_organization_code, ood.organization_id Inv_organization_id, ood.chart_of_accounts_id
FROM hr_operating_units hou, org_organization_definitions ood
WHERE 1 = 1 AND hou.organization_id = ood.operating_unit

ORDER BY hou.organization_id ASC

Comments

Post a Comment

Popular posts from this blog

Difference Between MTS, ATO, MTO ,PTO ,CTO and ETO.

-
Image
 Make-to-stock (MTS) In MTS environments, products are created before receipt of a customer order. Customer orders are then filled from existing stock, and then those stocks are replenished through production orders. MTS environments have the advantage of decoupling manufacturing processes from customer orders. Theoretically, this enables customer orders to be filled immediately from readily available stock. It also allows the manufacturer to organize production in ways that minimize costly changeovers and other disruptions. However, there are risks associated with placing finished goods into inventory without having a firm customer order or an established need. These risks tend to limit MTS environments to simple, low-variety, or commodity products whose demand can be forecasted readily.  Assemble-to-order (ATO) In ATO environments, products are assembled from components after the receipt of a customer order. The key components in the assembly or f...
Read more

Scheduling ,Reservations and ATP in Oracle Order management

-
Before we start understanding scheduling we need to know certain terms and how they are derived in Oracle. Terminology Understanding the following terms will help you understand how scheduling works in OM. Actual Arrival Date - The date the order line arrives at the customer site. Actual Ship Date - The date the order line is shipped. This date is recorded by the ship confirm action. Arrival Set - A set of order lines which arrive at the same time at the destination. Available to Promise (ATP) - The quantity of current on-hand stock, outstanding receipts and planned production not already committed to sales orders or other sources of demand. ATP Date - The date that a requested quantity will be available to promise. Delivery Lead Time - Time (in days) for items to reach the customer once they are shipped. There are two ways to help system calculate this date. 1) Create a location for the Ship-to address and assign it as the internal location and then define...
Read more

Accounting entries in Oracle Purchasing and Payables

-
This document gives in detail different accounts used and the accounting impact of various transactions that take place in Oracle Purchasing and Oracle Payables. Both Standard costing and Average costing methods are considered. The accounts are Oracle Applications specific and might differ from the conventional accounting names. Examples are given wherever required for better understanding of the concept. The sources of these accounts are given. PURCHASING:  Receiving – For Accrual Process for perpetual Accruals Receipts for inventory purchases are always accrued upon receipt. And also use perpetual accruals for expense purchases you want to record uninvoiced purchase liabilities immediately upon the receipt of the expense goods. Receiving Account (Receiving Account) To record the current balance of the material in receiving and inspection. Where to define in Apps: Define Organization           ...
Read more