#=======================================================================
# Writer
: Lokanadham Thandlam
#=======================================================================
--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
--• 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;
--
--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
--Will Update With Further
Information
--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) = 'ONT' -----Pass Application Code
e.g for order management ONT
ORDER BY 1
--How to check the Status of
Deferred Activities In Workflow
--
--1-Run wfbkgchk.sql
available at $FND_TOP/sql
--2.For example, log on to
the database by doing sqlplus username/password@database
--3.Then run the sql file,
wfbkgchk.sql, by doing @wfbkgchk.sql
--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'
--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 Review PUBLIC
Privileges
SELECT *
FROM dba_tab_privs
WHERE grantee = 'PUBLIC'
AND owner NOT IN
('SYS',
'SYSTEM',
'CTXSYS',
'OWAPUB',
'MDSYS',
'ORDPLUG',
'ORDSYS',
'ORDPLUGINS',
'CSMIG')
ORDER BY table_name
--List All grants From APPS
to applsyspub
SELECT *
FROM dba_tab_privs
WHERE grantee = 'APPLSYSPUB' AND grantor = 'APPS'
ORDER BY table_name
--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
= NVL(:order_number, OOH.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');
--TO VIEW hold infomration ON
ORDER
SELECT oh.header_id
hdr_id,
oh.line_id line,
hd.hold_id,
hd.name hold_name,
hd.item_type,
hd.activity_name activity,
NVL (hd.hold_included_items_flag, 'N') hiif,
oh.creation_date held_date,
oe_holds_pvt.user_name (oh.created_by) held_by,
hs.hold_until_date,
DECODE (hs.hold_entity_code,
'S', 'Ship To',
'B', 'Bill To',
'I', 'Item',
'W', 'Warehouse',
'O', 'Order',
'C', 'Customer',
hs.hold_entity_code)
ec1,
hs.hold_entity_id,
DECODE (hs.hold_entity_code2,
'S', 'Ship To',
'B', 'Bill To',
'I', 'Item',
'W', 'Warehouse',
'O', 'Order',
'C', 'Customer',
hs.hold_entity_code2)
ec2,
hs.hold_entity_id2,
oh.released_flag
FROM oe_order_holds_all oh, oe_hold_sources_all hs, oe_hold_definitions hd
WHERE oh.hold_source_id = hs.hold_source_id AND hd.hold_id = hs.hold_id;
--Folders Created IN
Application
SELECT fdf.OBJECT,
fdf.behavior_mode,
fdf.folder_id,
(SELECT user_name
FROM fnd_user fu
WHERE fu.user_id = fdf.user_id)
owner,
fdf.creation_date,
(SELECT user_name
FROM fnd_user fu
WHERE fu.user_id = fdf.created_by)
created_by,
(SELECT user_name
FROM fnd_user fu
WHERE fu.user_id = fdf.last_updated_by)
last_updated_by,
fdf.last_update_date,
fdf.last_update_login
FROM fnd_default_folders fdf
ORDER BY fdf.OBJECT, fdf.user_id DESC
--display all subinventories
Setup
SELECT msi.secondary_inventory_name,
MSI.SECONDARY_INVENTORY_NAME "Subinventory",
MSI.DESCRIPTION "Description",
MSI.DISABLE_DATE "Disable Date",
msi.PICKING_ORDER "Picking Order",
gcc1.concatenated_segments "Material Account",
gcc2.concatenated_segments "Material Overhead
Account",
gcc3.concatenated_segments "Resource Account",
gcc4.concatenated_segments "Overhead Account",
gcc5.concatenated_segments "Outside Processing
Account",
gcc6.concatenated_segments "Expense Account",
gcc7.concatenated_segments "Encumbrance Account",
msi.material_overhead_account,
msi.resource_account,
msi.overhead_account,
msi.outside_processing_account,
msi.expense_account,
msi.encumbrance_account
FROM mtl_secondary_inventories
msi,
gl_code_combinations_kfv gcc1,
gl_code_combinations_kfv gcc2,
gl_code_combinations_kfv gcc3,
gl_code_combinations_kfv gcc4,
gl_code_combinations_kfv gcc5,
gl_code_combinations_kfv gcc6,
gl_code_combinations_kfv gcc7
WHERE msi.material_account
= gcc1.CODE_COMBINATION_ID(+)
AND msi.material_overhead_account = gcc2.CODE_COMBINATION_ID(+)
AND msi.resource_account = gcc3.CODE_COMBINATION_ID(+)
AND msi.overhead_account = gcc4.CODE_COMBINATION_ID(+)
AND msi.outside_processing_account = gcc5.CODE_COMBINATION_ID(+)
AND msi.expense_account = gcc6.CODE_COMBINATION_ID(+)
AND msi.encumbrance_account = gcc7.CODE_COMBINATION_ID(+)
ORDER BY msi.secondary_inventory_name
--Query Used on Vision
--display all categories that
the item belongs
SELECT UNIQUE
micv.CATEGORY_SET_NAME "Category Set",
micv.CATEGORY_SET_ID "Category Set ID",
DECODE (micv.CONTROL_LEVEL, 1, 'Master', 2, 'Org', 'Other')
"Control
Level",
micv.CATEGORY_ID "Category ID",
micv.CATEGORY_CONCAT_SEGS "Category"
FROM MTL_ITEM_CATEGORIES_V micv
--TO View All catagory
segment values
SELECT mcb.category_id,
mcb.segment1,
mcb.segment2,
mcb.segment3,
mcb.SEGMENT4,
mcb.SEGMENT5,
mcb.SEGMENT6,
mcb.SEGMENT7,
mcb.SEGMENT8,
mcb.SEGMENT9,
mcb.SEGMENT10,
mcb.SEGMENT11,
mcb.SEGMENT12,
mcb.SEGMENT13,
mcb.SEGMENT14,
mcb.DESCRIPTION,
mcb.DISABLE_DATE,
mcb.CREATION_DATE,
(SELECT user_name
FROM fnd_user
WHERE user_id = mcb.CREATED_BY)
CREATED_BY
FROM mtl_categories_b mcb
--physical inventory
information for the item
SELECT DISTINCT (mpa.physical_inventory_id) "Physical Inventory
ID",
mpi.physical_inventory_name "Physical Inventory
Name",
mpa.APPROVAL_STATUS "Status",
COUNT (*) "Total Number of Posted
Trxn"
FROM mtl_physical_adjustments mpa, mtl_physical_inventories mpi
WHERE mpi.organization_id
= mpa.organization_id
AND mpi.physical_inventory_id = mpa.physical_inventory_id
AND mpa.APPROVAL_STATUS = 3
GROUP BY mpa.physical_inventory_id,
mpi.physical_inventory_name,
mpa.APPROVAL_STATUS
--To Find Shipping
Organizations
SELECT hr.organization_id
org_id, hr.NAME, inv.organization_code
FROM mtl_parameters inv,
hr_all_organization_units hr
WHERE hr.organization_id
= inv.organization_id;
--Look AT ALL applications
NAME
SELECT fav.application_name app_name,
fav.application_short_name
app_s_name,
fav.application_id app_id
FROM fnd_application_vl fav, fnd_product_installations
fpi
WHERE fav.application_id = fpi.application_id
ORDER BY 3;
--VIEW TABLE AND INDEX NAME/
status
SELECT aind.table_owner,
aind.table_name,
aind.index_name,
aind.index_type,
aind.status
FROM all_indexes aind, fnd_application fa
WHERE aind.table_owner = fa.application_short_name
ORDER BY aind.table_owner, aind.table_name, aind.index_name;
--Get Onhand Qty
SELECT organization_id,
(SELECT ( msib.segment1
|| '-'
|| msib.segment2
|| '-'
|| msib.segment3
|| '-'
|| msib.segment4
|| '-'
|| msib.segment5
|| '-'
|| msib.segment6
|| '-'
|| msib.segment7)
FROM mtl_system_items_b msib
WHERE msib.inventory_item_id = moq.inventory_item_id
AND msib.organization_id = moq.organization_id)
"Item Code",
(SELECT description
FROM mtl_system_items_b msib
WHERE msib.inventory_item_id = moq.inventory_item_id
AND msib.organization_id = moq.organization_id)
"item Description",
SUM (moq.transaction_quantity) onhandqty
FROM mtl_onhand_quantities moq
GROUP BY moq.organization_id, (moq.inventory_item_id)
--Segment may Varry
--Employee setup in the
system
SELECT hr.hr_locations_all_tl.location_code "Location",
hr.hr_all_organization_units.NAME "Department",
hr.per_all_people_f.employee_number "Employee Number",
hr.per_all_people_f.first_name
|| ' '
|| hr.per_all_people_f.middle_names
|| ' '
|| hr.per_all_people_f.last_name
"Employee
Name",
hr.per_jobs.NAME "Job",
hr.per_all_people_f.start_date
FROM hr.per_all_people_f,
hr.per_all_assignments_f,
hr.hr_all_organization_units,
hr.per_jobs,
hr.hr_locations_all_tl
WHERE ( (hr.per_all_assignments_f.organization_id =
hr.hr_all_organization_units.organization_id)
AND (hr.per_all_assignments_f.job_id = hr.per_jobs.job_id)
AND (hr.per_all_people_f.person_id =
hr.per_all_assignments_f.person_id)
AND (hr.per_all_assignments_f.location_id = hr.hr_locations_all_tl.location_id))
--Check Locators for
inventory Inventory Org Wise(Number of segment may varry)
SELECT mil.segment1
loc_seg1,
mil.segment11 loc_seg11,
mil.segment2 loc_seg2,
mil.segment3 loc_seg3,
mil.segment4 loc_seg4,
mil.segment5 loc_seg5,
mil.segment6 loc_seg6,
ood.ORGANIZATION_NAME,
mil.SUBINVENTORY_CODE
FROM mtl_item_locations mil, org_organization_definitions
ood
WHERE mil.ORGANIZATION_ID = ood.ORGANIZATION_ID
--To Check Item Catogry For
Inventory master (No Of Segments May Varry)
SELECT ood.organization_name,
segment1
|| '-'
|| segment2
|| '-'
|| segment3
|| '-'
|| segment4
|| '-'
|| segment5
|| '-'
|| segment6
|| '-'
|| segment7
|| '-'
|| segment8
|| '-'
|| segment9
|| '-'
|| segment10
|| '-'
|| segment11
|| '-'
|| segment12
|| '-'
|| segment13
catgory
FROM org_organization_definitions
ood,
mtl_categories_vl mcv,
mtl_category_sets mcs
WHERE mcs.structure_id = mcv.structure_id
ORDER BY ood.organization_name
--To Get User Having Responsibility
for a specific Application
SELECT UNIQUE U.USER_ID,
SUBSTR (U.USER_NAME, 1, 30) USER_NAME,
SUBSTR (R.RESPONSIBILITY_NAME, 1, 60) RESPONSIBLITY,
SUBSTR (A.APPLICATION_NAME, 1, 50) 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
AND A.APPLICATION_NAME = 'Purchasing' ---Application name
ORDER BY SUBSTR (USER_NAME, 1, 30),
SUBSTR (A.APPLICATION_NAME, 1, 50),
SUBSTR (R.RESPONSIBILITY_NAME, 1, 60);
--How TO get Application
Installation Status AND Patch LEVEL
SELECT fav.application_name app_name,
fav.application_short_name
app_s_name,
DECODE (fpi.status,
'I', 'Yes',
'S', 'Shared',
'N', 'No',
fpi.status)
installation_status,
fpi.product_version,
NVL (fpi.patch_level, 'Not Available') patchset,
fav.application_id app_id
FROM fnd_application_vl fav, fnd_product_installations
fpi
WHERE fav.application_id = fpi.application_id
ORDER BY 1 ASC;
--Get schema user name and
objects owned by that particular schema user name
SELECT username,
COUNT (DECODE (o.object_type, 'TABLE', o.object_id, '')) tabs,
COUNT (DECODE (o.object_type, 'INDEX', o.object_id, '')) inds,
COUNT (DECODE (o.object_type, 'SYNONYM', o.object_id, '')) syns,
COUNT (DECODE (o.object_type, 'VIEW', o.object_id, '')) views,
COUNT (DECODE (o.object_type, 'SEQUENCE', o.object_id, '')) seqs
FROM all_objects o, dba_users u
WHERE u.username = o.owner(+)
GROUP BY username
ORDER BY username
--Number of line processed in
Order Management
SELECT COUNT (line_id) "Order Line
Processed"
FROM oe_order_lines_all
WHERE creation_date BETWEEN TO_DATE (:Fdate, 'DD/MM/YYYY')
AND TO_DATE (:tdate, 'DD/MM/YYYY')
AND
flow_status_code = 'CLOSED';
--Get Number Of canceled
requisition
SELECT a.AUTHORIZATION_STATUS,
(a.ORG_ID),
(SELECT DISTINCT
hr.per_all_people_f.first_name
|| ' '
|| hr.per_all_people_f.middle_names
|| ' '
|| hr.per_all_people_f.last_name
"Employee Name"
FROM hr.per_all_people_f
WHERE hr.per_all_people_f.PERSON_ID IN
(SELECT employee_id
FROM fnd_user fu
WHERE fu.user_id = a.CREATED_BY))
CREATED_BY,
COUNT (SEGMENT1)
FROM po_requisition_headers_all a
WHERE a.creation_date BETWEEN TO_DATE (:DATE_FROM, 'DD/MM/YYYY') AND TO_DATE (:DATE_TO, 'DD/MM/YYYY')
AND a.AUTHORIZATION_STATUS = 'CANCELLED'
GROUP BY a.AUTHORIZATION_STATUS, a.ORG_ID, a.CREATED_BY
--Check item where
description of item and long description of item is not matched
SELECT
msi.segment1
|| '-'
|| msi.segment2
|| '-'
|| msi.segment3
|| '-'
|| msi.segment4
|| '-'
|| msi.segment5
|| '-'
|| msi.segment6
|| '-'
|| msi.segment7
"item
code",
description,
long_description,
(SELECT user_name
FROM fnd_user fu
WHERE fu.user_id = msi.created_by)
"Created
By",
(SELECT user_name
FROM fnd_user fu
WHERE fu.user_id = msi.LAST_UPDATED_BY)
"LAST_UPDATED_BY",
organization_id
FROM mtl_system_items_fvl msi
WHERE TRIM (UPPER (description)) <> TRIM (UPPER (long_description))
--To Find
Application(Product) and Patch Set level
SELECT fav.application_name app_name,
fav.application_short_name app_s_name,
DECODE (fpi.status,
'I', 'Yes',
'S', 'Shared',
'N', 'No',
fpi.status)
inst_status,
fpi.product_version,
NVL (fpi.patch_level, 'Not Available') patchset,
fav.application_id app_id
FROM fnd_application_vl fav, fnd_product_installations
fpi
WHERE fav.application_id = fpi.application_id
ORDER BY 1 DESC;
--TO Find Duplicate Item
CATEGORY Code
SELECT category_set_name, category_concat_segments, COUNT (*)
FROM
mtl_category_set_valid_cats_v
WHERE (category_set_id = 1)
GROUP BY
category_set_name,
category_concat_segments
HAVING COUNT (*) > 1
ORDER BY
category_concat_segments
SELECT (mmt.inventory_item_id) item_code,
msi.description item_description,
DECODE (mtln.lot_number,
NULL, SUM (mmt.transaction_quantity),
SUM (mtln.transaction_quantity))
net_qty,
mtln.lot_number,
mil.segment1 || '-' || mil.segment2 || '-' || mil.segment3 loc_seg,
secondary_inventory_name,
ood.organization_name
FROM mtl_material_transactions
mmt,
org_organization_definitions ood,
mtl_transaction_lot_numbers mtln,
mtl_system_items msi,
mtl_item_locations mil,
mtl_secondary_inventories mseci
WHERE mmt.organization_id
= ood.organization_id
AND mmt.transaction_id = mtln.transaction_id(+)
AND msi.inventory_item_id = mmt.inventory_item_id
AND mmt.organization_id = msi.organization_id
AND mmt.locator_id = mil.inventory_location_id(+)
AND mmt.organization_id = mil.organization_id(+)
AND mmt.subinventory_code = mseci.secondary_inventory_name
AND mmt.organization_id = mseci.organization_id
-- and
mmt.ORGANIZATION_ID = 115 if Required for specific Organization
AND TRUNC (mmt.transaction_date) <= '31-MAY-2007'
GROUP BY (mmt.inventory_item_id),
ood.organization_name,
mtln.lot_number,
msi.description,
mil.segment1 || '-' || mil.segment2 || '-' || mil.segment3,
secondary_inventory_name
HAVING DECODE (mtln.lot_number,
NULL, SUM (mmt.transaction_quantity),
SUM (mtln.transaction_quantity)) <> 0
ORDER BY ood.organization_name, mmt.inventory_item_id
--How to get Document
Sequence Detail
SELECT (SELECT application_name
FROM fnd_application_tl fat
WHERE fat.application_id = fnd_document_sequences.application_id)
"Application Name",
NAME,
start_date,
end_date,
TYPE,
message_flag,
initial_value,
doc_sequence_id,
(SELECT fu.user_name
FROM fnd_user fu
WHERE fu.user_id = fnd_document_sequences.last_updated_by)
last_updated_by,
last_update_date,
(SELECT fu.user_name
FROM fnd_user fu
WHERE fu.user_id = fnd_document_sequences.created_by)
created_by,
creation_date
FROM fnd_document_sequences
ORDER BY 1, 2
--How get
Folder Details used In Application
SELECT ff.folder_id,
ff.OBJECT,
ff.NAME,
ff.window_width,
ff.public_flag,
ff.autoquery_flag,
ff.where_clause,
ff.order_by,
(SELECT user_name
FROM fnd_user fu
WHERE fu.user_id = ff.created_by)
created_by,
ffc.display_mode,
ffc.item_name,
ffc.SEQUENCE,
ffc.item_width,
ffc.item_prompt,
ffc.x_position,
ffc.y_position
FROM applsys.fnd_folders
ff, fnd_folder_columns ffc
WHERE ff.folder_id = ffc.folder_id
ORDER BY ff.OBJECT, ff.NAME