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
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