Join the OracleApps88 Telegram group @OracleApps88to get more information on Oracle EBS R12/Oracle Fusion applications.

If you are facing any issues while copying the Code/Script or any issues with Posts, Please send a mail to OracleApp88@Yahoo.com or message me at @apps88 or +91 905 957 4321 in telegram.

Friday, February 17, 2012

Oracle Usefull Queries


--To check responsibility which contact given function
--1. To get form Id from database
SELECT
  FUNCTION_ID,
  USER_FUNCTION_NAME
FROM
  FND_FORM_FUNCTIONS_TL;
--You can also use
SELECT
  FUNCTION_ID,
  FUNCTION_NAME
FROM
  FND_FORM_FUNCTIONS;

--2. Pass The obtained Function id or function name to following query
SELECT
  frv.responsibility_name,
  frv.description
FROM
  fnd_responsibility_vl frv,
  fnd_form_functions fff
WHERE
  fff.function_name = 'FND_FNDATDAG' ------Pass Function name Here
  --f.form_id =p_form_id
AND frv.menu_id NOT IN
  (
    SELECT
      frf.action_id
    FROM
      fnd_resp_functions frf
    WHERE
      frf.action_id   = frv.menu_id
    AND frf.rule_type = 'M'
  )
AND frv.menu_id IN
  (
    SELECT
      me.menu_id
    FROM
      fnd_menu_entries me START
    WITH me.function_id = fff.function_id CONNECT BY PRIOR me.menu_id =
      me.sub_menu_id
  )
AND fff.function_id NOT IN
  (
    SELECT
      frf.action_id
    FROM
      fnd_resp_functions frf
    WHERE
      FRF.ACTION_ID   = FFF.FUNCTION_ID
    AND FRF.RULE_TYPE = 'F'
  ) ;

--• To Check The Profile Options Which Are Modified
SELECT
  t.user_profile_option_name,
  profile_option_value,
  v.creation_date,
  v.last_update_date,
  v.creation_date - v.last_update_date "change Status",
  (
    SELECT
      UNIQUE user_name
    FROM
      fnd_user
    WHERE
      user_id = v.created_by
  )
  "Created By",
  (
    SELECT
      user_name
    FROM
      fnd_user
    WHERE
      user_id = v.last_updated_by
  )
  "Last Update By"
FROM
  fnd_profile_options o,
  fnd_profile_option_values v,
  fnd_profile_options_tl t
WHERE
  o.profile_option_id               = v.profile_option_id
AND o.application_id                = v.application_id
AND start_date_active              <= SYSDATE
AND NVL (end_date_active, SYSDATE) >= SYSDATE
AND o.profile_option_name           = t.profile_option_name
AND level_id                        = 10001
AND t.LANGUAGE                     IN
  (
    SELECT
      language_code
    FROM
      fnd_languages
    WHERE
      installed_flag = 'B'
    UNION
    SELECT
      nls_language
    FROM
      fnd_languages
    WHERE
      installed_flag = 'B'
  )
ORDER BY
  USER_PROFILE_OPTION_NAME;
;

--• Query used To Check The Form personalization
SELECT
  ffcr.function_name,
  ffft.user_function_name "User Form Name",
  ffcr.form_name "from Source File Name",
  Ffcr.ID "Form ID",
  (
    SELECT
      user_name
    FROM
      fnd_user fu
    WHERE
      fu.user_id = ffcr.created_by
  )
  "Created By ",
  ffcr.creation_date,
  ffcr.last_update_date,
  ffcr.SEQUENCE,
  ffcr.rule_key,
  ffcr.description,
  ffcr.rule_type,
  ffcr.enabled,
  ffcr.trigger_event,
  ffcr.trigger_object,
  ffcr.condition,
  ffcr.fire_in_enter_query
FROM
  fnd_form_custom_rules ffcr,
  fnd_form_functions_vl ffft
WHERE
  FFCR.ID = FFFT.FUNCTION_ID ;

--For More detail on form Personalization Use Following Tables (Rule_id) is
-- reference key these tables applsys.fnd_form_custom_actions,
-- applsys.fnd_form_custom_scopes
--By using Following query you can get application wise detail
SELECT
  (
    SELECT
      application_name
    FROM
      fnd_application_tl fa
    WHERE
      fa.application_id = ff.application_id
  )
  application,
  ffcr.ID,
  ffcr.SEQUENCE,
  ffcr.function_name,
  REPLACE (ffcr.description, CHR (39), CHR (39)
  || CHR (39)) description,
  ffcr.trigger_event,
  ffcr.trigger_object,
  REPLACE (REPLACE (ffcr.condition, CHR (10), CHR (32)), CHR (39), CHR (39)
  || CHR (39) ) condition,
  ffcr.enabled,
  ffcr.fire_in_enter_query,
  ffcr.rule_key,
  ffcr.form_name,
  ffcr.rule_type
FROM
  apps.fnd_form_custom_rules ffcr,
  applsys.fnd_form ff
WHERE
  (
    FF.FORM_NAME
  )
  = (FFCR.FORM_NAME) ;

--• How To Check Patch Level Status
SELECT
  a.application_name,
  DECODE(b.status,'I','Installed','S','Shared','N/A') STATUS,
  PATCH_LEVEL
FROM
  APPS.fnd_application_vl a,
  APPS.fnd_product_installations b
WHERE
  A.APPLICATION_ID = B.APPLICATION_ID ;

--• Get Request Group name Associated With Application
SELECT
  g.request_group_name,
  c.user_concurrent_program_name,
  a.application_name,
  g.application_id,
  g.request_group_id,
  u.unit_application_id,
  u.request_unit_id
FROM
  fnd_request_groups g,
  fnd_request_group_units u,
  fnd_application_tl a,
  fnd_concurrent_programs_tl c
WHERE
  g.request_group_id      = u.request_group_id
AND u.unit_application_id = a.application_id
AND u.request_unit_id     = c.concurrent_program_id
ORDER BY
  c.user_concurrent_program_name,
  a.application_name,
  G.REQUEST_GROUP_ID ;

--How To Check Current Status Of Request
SELECT
  r.request_id,
  r.phase_code,
  r.status_code,
  r.request_date,
  r.requested_start_date,
  r.hold_flag,
  r.parent_request_id,
  r.last_update_date,
  u1.user_name updated_by_name,
  r.actual_start_date,
  r.completion_text,
  r.actual_completion_date,
  u2.user_name requestor,
  fa.application_name application_name,
  cp.enabled_flag enabled,
  r.controlling_manager,
  DECODE (r.description, NULL, cp.user_concurrent_program_name, r.description
  || ' ('
  || cp.user_concurrent_program_name
  || ')' ) program_name,
  queue_control_flag,
  r.queue_method_code,
  cp.run_alone_flag,
  r.single_thread_flag,
  r.request_limit,
  r.cd_id
FROM
  fnd_concurrent_requests r,
  fnd_concurrent_programs_vl cp,
  fnd_user u1,
  fnd_user u2,
  fnd_application_vl fa
WHERE
  r.program_application_id   = fa.application_id
AND r.program_application_id = cp.application_id(+)
AND r.concurrent_program_id  = cp.concurrent_program_id(+)
AND r.last_updated_by        = u1.user_id(+)
AND R.REQUESTED_BY           = U2.USER_ID(+);
;

--Another Option
SELECT
  fcr.REQUEST_ID,
  fu.user_name AS requested_by,
  fcpt.user_concurrent_program_name,
  fcr.request_date,
  fcr.phase_code,
  fcr.requested_start_date,
  fcr.argument_text
FROM
  fnd_concurrent_programs_tl fcpt,
  fnd_concurrent_requests fcr,
  fnd_user fu
WHERE
  fcpt.concurrent_program_id = fcr.concurrent_program_id
AND fcr.requested_by         = fu.user_id
AND TRUNC(fcr.request_date)  = TRUNC(sysdate)
ORDER BY
  FCR.REQUESTED_START_DATE ;

--Get Average Runtime of A Concurrent
SELECT
  fcp.description "Description",
  TRUNC (AVG ( ( TO_NUMBER (TO_CHAR (actual_completion_date, 'SSSSS')) -
  TO_NUMBER (TO_CHAR (actual_start_date, 'SSSSS')) )                   / 60 ),
  2 ) "Time in Min."
FROM
  fnd_concurrent_programs_vl fcp,
  fnd_concurrent_requests fcr
WHERE
  fcp.application_id                              = fcr.program_application_id
AND fcp.concurrent_program_id                     = fcr.concurrent_program_id
AND TO_CHAR (actual_completion_date, 'DD-MON-YY') = TO_CHAR (actual_start_date,
  'DD-MON-YY')
GROUP BY
  fcp.description
ORDER BY
  FCP.DESCRIPTION;
;

--To Check Period Status
--This query will help to get information of periods status (open/close) of
-- different Oracle Apps modules.
SELECT
  ROWID,
  (
    SELECT
      application_short_name
    FROM
      fnd_application fa
    WHERE
      fa.application_id = gps.application_id
  )
  application,
  (
    SELECT
      NAME
    FROM
      gl_sets_of_books gsp
    WHERE
      gsp.set_of_books_id = gps.set_of_books_id
  )
  setofbookname,
  period_name,
  closing_status,
  period_num,
  period_year,
  start_date,
  end_date
FROM
  gl_period_statuses gps
ORDER BY
  PERIOD_YEAR DESC,
  PERIOD_NUM DESC ;

--To Check Open inventory Periods
SELECT
  summary.organization_id organization_id,
  ood.organization_code organization_code,
  ood.organization_name organization_name,
  periods.period_name period_name,
  periods.OPEN_FLAG ,
  periods.PERIOD_CLOSE_DATE ,
  periods.period_year period_year,
  periods.period_start_date period_start_date,
  periods.schedule_close_date schedule_close_date,
  summary.secondary_inventory sub_inv,
  summary.inventory_value VALUE
FROM
  mtl_period_summary summary,
  org_acct_periods periods,
  org_organization_definitions ood
WHERE
  summary.organization_id      = periods.organization_id
AND summary.organization_id    = ood.organization_id
AND summary.acct_period_id     = periods.acct_period_id
AND periods.period_close_date IS NOT NULL
AND SUMMARY.INVENTORY_TYPE     = 1 ;

--Check User --> Application and assigned responsiblity
SELECT
  UNIQUE u.user_id,
  SUBSTR(u.user_name,1,30) user_name,
  SUBSTR(A.APPLICATION_NAME,1,50) Application,
  SUBSTR(R.RESPONSIBILITY_NAME,1,60) Responsiblity
FROM
  fnd_user u,
  FND_USER_RESP_GROUPS G,
  fnd_application_tl A,
  FND_RESPONSIBILITY_TL R
WHERE
  G.User_Id(+)                      = U.User_ID
AND G.RESPONSIBILITY_APPLICATION_ID = A.Application_Id
AND A.Application_Id                = R.Application_Id
AND G.RESPONSIBILITY_ID             = R.RESPONSIBILITY_ID
ORDER BY
  SUBSTR(user_name,1,30),
  SUBSTR(A.APPLICATION_NAME,1,50),
  SUBSTR(R.RESPONSIBILITY_NAME,1,60) ;

--To Check Application--> Responsablity and Asssigned Menu
SELECT DISTINCT
  e.application_name,
  a.responsibility_name,
  a.LANGUAGE,
  b.responsibility_key,
  c.user_menu_name
FROM
  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 f
WHERE
  a.responsibility_id(+) = b.responsibility_id
AND b.menu_id            = c.menu_id
AND b.menu_id            = d.menu_id
AND e.application_id     = f.application_id
AND f.application_id     = b.application_id
AND A.LANGUAGE           = 'US' ;

--To Check Module Wise Reports
SELECT
  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_id
FROM
  fnd_concurrent_programs_vl fcpv,
  fnd_application fa
WHERE
  fcpv.application_id = fa.application_id
ORDER BY
  1 ;

--To Count Module Wise Reports
SELECT
  fa.application_short_name,
  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,
  COUNT (concurrent_program_id) COUNT
FROM
  fnd_concurrent_programs_vl fcpv,
  fnd_application fa
WHERE
  fcpv.application_id = fa.application_id
GROUP BY
  fa.application_short_name,
  fcpv.execution_method_code
ORDER BY
  1 ;

--To Check Profile Option value and application level
SELECT
  fpo.profile_option_name PROFILE,
  fpov.profile_option_value VALUE,
  DECODE (fpov.level_id, 10001, 'SITE', 10002, 'APPLICATION', 10003,
  'RESPONSIBILITY', 10004, 'USER' ) "LEVEL",
  fa.application_short_name app,
  fr.responsibility_name responsibility,
  fu.user_name "USER"
FROM
  fnd_profile_option_values fpov,
  fnd_profile_options fpo,
  fnd_application fa,
  fnd_responsibility_vl fr,
  fnd_user fu,
  fnd_logins fl
WHERE
  fpo.profile_option_id     = fpov.profile_option_id
AND fa.application_id(+)    = fpov.level_value
AND fr.application_id(+)    = fpov.level_value_application_id
AND fr.responsibility_id(+) = fpov.level_value
AND fu.user_id(+)           = fpov.level_value
AND fl.login_id(+)          = fpov.last_update_login
ORDER BY
  1,
  3 ;

--How To Check Error and Interface tables
SELECT
  owner,
  table_name
FROM
  dba_tables
WHERE
  table_name LIKE '%ERROR%'
ORDER BY
  OWNER,
  TABLE_NAME ;
SELECT
  owner,
  table_name
FROM
  dba_tables
WHERE
  table_name LIKE '%INTERFACE%'
ORDER BY
  OWNER,
  TABLE_NAME ;

--How To check Organization
SELECT
  organization_id,
  organization_code,
  organization_name,
  operating_unit,
  legal_entity
FROM
  ORG_ORGANIZATION_DEFINITIONS ;

--How To Check lookup
SELECT
  (
    SELECT
      application_short_name
    FROM
      fnd_application
    WHERE
      application_id = flvv.view_application_id
  )
  application,
  flvv.lookup_code,
  flvv.meaning,
  flvv.description,
  flvv.tag,
  flvv.start_date_active,
  flvv.end_date_active,
  flvv.enabled_flag,
  flvv.lookup_type,
  flvv.security_group_id,
  flvv.territory_code,
  (
    SELECT
      user_name
    FROM
      fnd_user fu
    WHERE
      fu.user_id = flvv.created_by
  )
  uname,
  flvv.creation_date,
  flvv.last_update_date,
  flvv.last_updated_by,
  flvv.last_update_login
FROM
  fnd_lookup_values_vl flvv
WHERE
  (
    SELECT
      application_short_name
    FROM
      fnd_application
    WHERE
      application_id = flvv.view_application_id
  )
  = ///Pass Application Code e.g for order management ONT
ORDER BY
  1 ;

--How To get Flexfilds Application wise
SELECT
  (
    SELECT
      UNIQUE application_short_name
    FROM
      fnd_application
    WHERE
      application_id = fdfcv.application_id
  )
  application,
  fdfcv.descriptive_flex_context_code,
  fdfcv.descriptive_flex_context_name,
  fdfcv.description,
  fdfcv.enabled_flag,
  fdfcv.application_id,
  fdfcv.descriptive_flexfield_name,
  fdfcv.last_update_date,
  fdfcv.last_updated_by,
  fdfcv.last_update_login,
  fdfcv.creation_date,
  fdfcv.created_by,
  fdfcv.global_flag,
  fdfcv.row_id
FROM
  fnd_descr_flex_contexts_vl fdfcv
WHERE
  (
    SELECT
      UNIQUE application_short_name
    FROM
      fnd_application
    WHERE
      APPLICATION_ID = FDFCV.APPLICATION_ID
  )
  = 'ONT' ; --ONT used For order management

--How To Check Application wise Responsiblity
SELECT
  (
    SELECT
      application_short_name
    FROM
      fnd_application fa
    WHERE
      fa.application_id = frv.application_id
  )
  application,
  frv.responsibility_name,
  frv.description
FROM
  FND_RESPONSIBILITY_VL FRV
WHERE
  (
    SELECT
      application_short_name
    FROM
      fnd_application fa
    WHERE
      FA.APPLICATION_ID = FRV.APPLICATION_ID
  )
  = 'SQLAP' ; --Used For Specific Module

--how to find mapping between segment name and the value set
SELECT
  FIFSV.segment_name,
  FIFSV.segment_num,
  FIFSV.application_column_name,
  FIFSV.flex_value_set_id,
  FIF.application_table_name,
  FIF.id_flex_code flex_code,
  FIF.id_flex_name flex_name
FROM
  fnd_id_flex_segments_vl FIFSV,
  fnd_id_flexs FIF
WHERE
  FIFSV.ID_FLEX_CODE = FIF.ID_FLEX_CODE ;
--How to find order header information
SELECT
  ooh.order_number,
  ooh.header_id hdr_id,
  DECODE (ooh.order_category_code, 'MIXED', 'Mixed', 'ORDER', 'Regular',
  'RETURN', 'Return', ooh.order_category_code ) CATEGORY,
  ott.NAME ord_typ,
  ooh.ordered_date,
  ooh.transactional_curr_code curr,
  os2.NAME sdt,
  ooh.source_document_id sdi,
  os1.NAME os,
  ooh.orig_sys_document_ref osdr,
  ooh.sold_to_org_id sold_to,
  ooh.invoice_to_org_id invoice_to,
  ooh.cust_po_number cust_po,
  ooh.payment_type_code pay_type,
  ooh.flow_status_code hdr_flow_status,
  ooh.open_flag,
  ooh.booked_flag,
  ooh.cancelled_flag,
  NVL (ooh.upgraded_flag, 'N') upgraded_flag
FROM
  oe_order_headers_all ooh,
  oe_transaction_types_tl ott,
  oe_order_sources os1,
  oe_order_sources os2
WHERE
  ooh.order_number              = &order_number
AND ooh.order_type_id           = ott.transaction_type_id
AND ooh.order_source_id         = os1.order_source_id(+)
AND ooh.source_document_type_id = os2.order_source_id(+)
AND ott.LANGUAGE                =
  (
    SELECT
      fl.language_code
    FROM
      FND_LANGUAGES FL
    WHERE
      FL.INSTALLED_FLAG = 'B'
  );
Best Blogger TemplatesBest Blogger Tips
Best Blogger Gadgets
Posted by Raju Chinthapatla at 9:37:00 AM
Labels: ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)
If you are facing any issues while copying the Code/Script or any issues with Posts, Please send a mail to OracleApp88@Yahoo.com or message me at @apps88 or +91 905 957 4321 in telegram.
Best Blogger TipsGet Flower Effect