Total Pageviews

Saturday, March 9, 2013

Query 1: Reset User Password

Begin
fnd_user_pkg.updateuser(x_user_name => 'SKADIYALA',
x_owner => NULL,
x_unencrypted_password => 'welcome123');
COMMIT;
END;


Query 2:You know table get the package or any where

select * from ALL_DEPENDENCIES
where referenced_name = 'FPC_SPARES_INV_LOC_REP';

Query 3: Select responsibility name along with application name

SELECT application_short_name, frt.responsibility_id, frt.responsibility_name
FROM apps.fnd_responsibility_tl frt, fnd_application fa
WHERE fa.application_id = frt.application_id;

 Get the Xml data file name
  SELECT distinct LobsEO.LANGUAGE,
    LobsEO.LOB_TYPE,
    LobsEO.APPLICATION_SHORT_NAME,
    LobsEO.LOB_CODE,
    LobsEO.TERRITORY,
    LobsEO.FILE_NAME,
    LobsEO.FILE_STATUS,
    header.TEMPLATE_ID,
    header.TEMPLATE_CODE,
    header.DS_APP_SHORT_NAME ,
    header.DATA_SOURCE_CODE ,
    header.TEMPLATE_TYPE_CODE                                       AS TEMPLATE_TYPE,
    language.NAME                                                   AS LANGUAGE_MEANING,
    DECODE(LobsEO.Territory,'00','',territory.TERRITORY_SHORT_NAME) AS TERRITORY_MEANING,
    (
    CASE
      WHEN header.DEPENDENCY_FLAG = 'C'
      OR sample.FILE_NAME        IS NULL
      THEN 'MLSPreviewDisabled'
      ELSE 'MLSPreviewEnabled'
    END) AS PreviewImage,
    (
    CASE
      WHEN LobsEO.TRANS_COMPLETE = 'Y'
      THEN 'Complete'
      ELSE 'Partial'
    END) AS TRANSLATION_STATUS,
    'N'  AS SELECTED
  FROM apps.xdo_templates_vl header,
    apps.XDO_Lobs LobsEO,
    apps.XDO_Lobs sample,
    apps.FND_ISO_LANGUAGES_VL language,
    apps.fnd_territories_vl territory
  WHERE header.APPLICATION_SHORT_NAME = LobsEO.APPLICATION_SHORT_NAME
  AND header.TEMPLATE_CODE            =LobsEO.LOB_CODE
 --- AND LobsEO.lob_type                 = 'MLS_TEMPLATE'
  and header.DS_APP_SHORT_NAME =sample.APPLICATION_SHORT_NAME(+)
  AND header.DATA_SOURCE_CODE         = sample.LOB_CODE(+)
  --AND sample.LOB_TYPE(+)              ='XML_SAMPLE'
  AND lower(LobsEO.LANGUAGE)          =language.ISO_LANGUAGE_2(+)
  AND territory.territory_code(+)     = LobsEO.territory
  and header.TEMPLATE_CODE like'%FPC_944_ITSG_CON_DTL_INVOICE%'

Query 4: Get Menu name for Responsibility ID , You can find out responsibility_id from Query 1

SELECT DISTINCT a.responsibility_name, 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 a.responsibility_id = &resp_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';

Query 5: Get User name and related assigned responsibilities

SELECT DISTINCT u.user_id, u.user_name user_name,
r.responsibility_name responsiblity,
a.application_name application
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 1;

Query 6: Get Request Group associate with Responsibility Name

SELECT responsibility_name responsibility, request_group_name,
frg.description
FROM fnd_request_groups frg, fnd_responsibility_vl frv
WHERE frv.request_group_id = frg.request_group_id
ORDER BY responsibility_name

Query 7: Gets Form personalization listing

Personalization is feature available in 11.5.10.X. For More detail on form Personalization Use Following Tables (Rule_id) is reference key for these tables
applsys.fnd_form_custom_actions, applsys.fnd_form_custom_scopes

SELECT 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_query
FROM fnd_form_custom_rules ffcr, fnd_form_functions_vl ffft
WHERE ffcr.ID = ffft.function_id
ORDER BY 1;

Query 8: Query to view the patch level status of all modules

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;

Query 9: SQL to view all request who have attached to a responsibility

SELECT responsibility_name, frg.request_group_name,
fcpv.user_concurrent_program_name, fcpv.description
FROM fnd_request_groups frg,
fnd_request_group_units frgu,
fnd_concurrent_programs_vl fcpv,
fnd_responsibility_vl frv
WHERE frgu.request_unit_type = 'p'
AND frgu.request_group_id = frg.request_group_id
AND frgu.request_unit_id = fcpv.concurrent_program_id
AND frv.request_group_id = frg.request_group_id
ORDER BY responsibility_name;

Query 10: SQL to view all requests who have attached to a responsibility

SELECT responsibility_name, frg.request_group_name,
fcpv.user_concurrent_program_name, fcpv.description
FROM fnd_request_groups frg,
fnd_request_group_units frgu,
fnd_concurrent_programs_vl fcpv,
fnd_responsibility_vl frv
WHERE frgu.request_unit_type = 'p'
AND frgu.request_group_id = frg.request_group_id
AND frgu.request_unit_id = fcpv.concurrent_program_id
AND frv.request_group_id = frg.request_group_id
and  fcpv.user_concurrent_program_name like :program_name
ORDER BY responsibility_name;14

Query 11: SQL to view all types of request Application wise

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 description

Query 10: SQL to view concurrent request processing time, quite useful

SELECT 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_code
FROM apps.fnd_concurrent_programs p
, apps.fnd_concurrent_programs_tl pt
, apps.fnd_concurrent_requests f
WHERE f.concurrent_program_id = p.concurrent_program_id
and f.program_application_id = p.application_id
and f.concurrent_program_id = pt.concurrent_program_id
and f.program_application_id = pt.application_id
AND pt.language = USERENV('Lang')
and f.actual_start_date is not null
ORDER by f.actual_completion_date-f.ac




Query 12: SQL to view concurrent program and executable ,executable method ,executable file name ,concurrent program short name

SELECT 'Program User Name: ' || fcpt.user_concurrent_program_name prog_name,
'Description: ' || fcpt.description description,
'Executable Name: ' || exe.executable_name exe_name,
'Executable File Nmae: ' || exe.EXECUTION_FILE_NAME,
'Executable File: ' || exe.execution_file_name exe_file_name,
'Execution Type: ' ||
DECODE(exe.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',
exe.execution_method_code) exe_method,
--fnd.concurrent_program_id,
fnd.CONCURRENT_PROGRAM_NAME Short_name
FROM applsys.fnd_executables exe,
applsys.fnd_concurrent_programs fnd,
applsys.fnd_concurrent_programs_tl fcpt
WHERE exe.executable_id = fnd.executable_id
AND fnd.concurrent_program_id = fcpt.concurrent_program_id
AND fnd.application_id = fcpt.application_id
and exe.execution_method_code='I'
ORDER BY FND.CREATION_DATE DESC



 Query 13: AR To GL



SELECT    gjjlv.period_name             "Period Name"
        , gjb.name                      "Batch Name"
        , gjjlv.header_name             "Journal Entry For"
        , gjjlv.je_source               "Source"
        ,glcc.concatenated_segments     "Accounts"
        , NVL(gjjlv.line_entered_dr,0)  "Entered Debit"
        , NVL(gjjlv.line_entered_cr,0)   "Entered Credit"
        , NVL(gjjlv.line_accounted_dr,0) "Accounted Debit"
        , NVL(gjjlv.line_accounted_cr,0) "Accounted Credit"
        , gjjlv.currency_code            "Currency"
        , rctype.name                    "Trx type"
        , rcta.trx_number                "Trx Number"
        , rcta.trx_date                  "Trx Date"
        , RA.CUSTOMER_NAME               "Trx Reference"
        , gjh.STATUS                     "Posting Status"
        , TRUNC(gjh.DATE_CREATED)        "GL Transfer Dt"
        , gjjlv.created_by               "Transfer By"
FROM    apps.GL_JE_JOURNAL_LINES_V gjjlv
      , gl_je_lines gje
      , gl_je_headers gjh
      , gl_je_batches gjb
      , ra_customer_trx_all rcta
      , apps.ra_customers ra
      , apps.gl_code_combinations_kfv glcc
      , ra_cust_trx_types_all rctype
WHERE     gjh.period_name IN ('OCT-2008','NOV-2008')
AND       glcc.code_combination_id = gje.code_combination_id
AND       gjh.je_batch_id = gjb.je_batch_id
AND       gjh.je_header_id = gje.je_header_id
AND       gjh.period_name = gjb.default_period_name
AND       gjh.period_name = gje.period_name
AND       gjjlv.period_name = gjh.period_name
AND       gjjlv.je_batch_id = gjh.je_batch_id
AND       gjjlv.je_header_id = gjh.je_header_id
AND       gjjlv.line_je_line_num  = gje.je_line_num
AND       gjjlv.line_code_combination_id = glcc.code_combination_id
AND       gjjlv.line_reference_4 = rcta.trx_number
AND       rcta.cust_trx_type_id = rctype.cust_trx_type_id
AND       rcta.org_id = rctype.org_id
AND       ra.customer_id = rcta.bill_to_customer_id


Query 14: Query to find out concurrent program details and its parameters

SELECT fcpl.user_concurrent_program_name
     , fcp.concurrent_program_name
     , fcp.concurrent_program_id
     , fav.APPLICATION_SHORT_NAME
     , fav.APPLICATION_NAME
     , fav.application_id
     , fdfcuv.end_user_column_name
     , fdfcuv.form_left_prompt prompt
     , fdfcuv.enabled_flag
     , fdfcuv.required_flag
     , fdfcuv.display_flag
FROM   fnd_concurrent_programs fcp
     , fnd_concurrent_programs_tl fcpl
     , fnd_descr_flex_col_usage_vl fdfcuv
     , fnd_application_vl fav
WHERE  fcp.concurrent_program_id = fcpl.concurrent_program_id
AND    fcpl.user_concurrent_program_name = :conc_prg_name
AND    fav.application_id=fcp.application_id
AND    fcpl.LANGUAGE = 'US'
AND    fdfcuv.descriptive_flexfield_name = '$SRS$.' || fcp.concurrent_program_name;


 Query 15: Query to find out responsibility and request group for concurrent program:
SELECT DISTINCT
  FCPL.USER_CONCURRENT_PROGRAM_NAME
, FCP.CONCURRENT_PROGRAM_NAME
, FAPP.APPLICATION_NAME
, FRG.REQUEST_GROUP_NAME
, FNRTL.RESPONSIBILITY_NAME
FROM
  APPS.FND_REQUEST_GROUPS FRG
, APPS.FND_APPLICATION_TL FAPP
, APPS.FND_REQUEST_GROUP_UNITS FRGU
, APPS.FND_CONCURRENT_PROGRAMS FCP
, APPS.FND_CONCURRENT_PROGRAMS_TL FCPL
, APPS.FND_RESPONSIBILITY FNR
, APPS.FND_RESPONSIBILITY_TL FNRTL
WHERE
          FRG.APPLICATION_ID=fapp.APPLICATION_ID
AND FRG.APPLICATION_ID = FRGU.APPLICATION_ID
AND FRG.REQUEST_GROUP_ID = FRGU.REQUEST_GROUP_ID
AND FRG.REQUEST_GROUP_ID = FNR.REQUEST_GROUP_ID
AND FRG.APPLICATION_ID = FNR.APPLICATION_ID
AND FNR.RESPONSIBILITY_ID = FNRTL.RESPONSIBILITY_ID
AND FRGU.REQUEST_UNIT_ID = FCP.CONCURRENT_PROGRAM_ID
AND FRGU.UNIT_APPLICATION_ID = FCP.APPLICATION_ID
AND FCP.CONCURRENT_PROGRAM_ID = FCPL.CONCURRENT_PROGRAM_ID
AND FCPL.USER_CONCURRENT_PROGRAM_NAME LIKE :conc_prg_name
AND FNRTL.LANGUAGE = 'US'
AND FAPP.LANGUAGE = 'US';
Posted by Subhash G at 1:58 AM 1 comments  
Labels: AOL, CONCURRENT PROGRAM, FND


 Query 16:Concurrent Program with request group details


SELECT   frg.request_group_name
       , fav1.APPLICATION_SHORT_NAME "RequestGroup Appln Shortname"
       , fav1.APPLICATION_NAME "RequestGroup Application Name"
       , fav1.application_id "RequestGroup Application ID"
       , fcp.concurrent_program_name
       , fcpl.user_concurrent_program_name
       , fav.APPLICATION_SHORT_NAME "Con Prog Application Shortname"
       , fav.APPLICATION_NAME "Con Prog Application Name"
       , fav.application_id "Con Prog Application ID"
       , fe.execution_file_name
       , fe.executable_name
  FROM fnd_request_group_units frgu,
       fnd_concurrent_programs fcp,
       fnd_concurrent_programs_tl fcpl,
       fnd_request_groups frg,
       fnd_executables fe,
       fnd_application_vl fav,
       fnd_application_vl fav1
 WHERE frgu.request_unit_id = fcp.concurrent_program_id
   AND fav.application_id=fcp.application_id
   AND fav1.application_id=frgu.application_id
   AND frgu.request_group_id = frg.request_group_id
   AND fe.executable_id = fcp.executable_id
   AND fcp.concurrent_program_name = :conc_prg_name;

Query 17:Query to list concurrent program details with its parameter, values set and default value/type:

  SELECT fcpl.user_concurrent_program_name
      , fcp.concurrent_program_name
      , fav.APPLICATION_SHORT_NAME
      , fav.APPLICATION_NAME
      , fav.application_id
      , fdfcuv.end_user_column_name
      , fdfcuv.form_left_prompt prompt
      , fdfcuv.enabled_flag
      , fdfcuv.required_flag
      , fdfcuv.display_flag
      , fdfcuv.flex_value_set_id
      , ffvs.flex_value_set_name
      , flv.meaning default_type
      , fdfcuv.DEFAULT_VALUE     
 FROM   fnd_concurrent_programs fcp
      , fnd_concurrent_programs_tl fcpl
      , fnd_descr_flex_col_usage_vl fdfcuv
      , fnd_flex_value_sets ffvs
      , fnd_lookup_values flv
      , fnd_application_vl fav
 WHERE  fcp.concurrent_program_id = fcpl.concurrent_program_id
 AND    fcpl.user_concurrent_program_name = :conc_prg_name
 AND    fcpl.LANGUAGE = 'US'
 AND    fav.application_id=fcp.application_id
 AND    fdfcuv.descriptive_flexfield_name = '$SRS$.' || fcp.concurrent_program_name
 AND    ffvs.flex_value_set_id = fdfcuv.flex_value_set_id
 AND    flv.lookup_type(+) = 'FLEX_DEFAULT_TYPE'
 AND    flv.lookup_code(+) = fdfcuv.default_type
 AND    flv.LANGUAGE(+) = USERENV ('LANG');

 Query 18:
 Select Distinct C.User_Concurrent_Program_Name, a.user_request_set_name
From
fnd_request_sets_vl a,
fnd_request_set_programs b,
fnd_req_set_stages_form_v d,
fnd_concurrent_programs_vl c
Where
a.Request_Set_Id = d.Request_Set_ID
And A.Request_Set_Id = b.Request_Set_Id
And A.Application_Id = b.Set_Application_Id
And A.Application_Id = d.Set_Application_Id
And d.Request_Set_Stage_Id = b.Request_Set_Stage_Id
--And a.user_request_set_name Like '11i AR Slow Start Reports'
and C.User_Concurrent_Program_Name like'MasTec (AT) CAR Transfer to AP Process WB'
And b.concurrent_program_id = c.concurrent_program_id
And b.Program_Application_Id = C.Application_Id

No comments:

Post a Comment