Useful queries for Oracle EBS

There are many available reports in Oracle Apps so you can discuss the requirements related to these reports here.
ahmadbilal
Posts: 615
Joined: Mon Sep 18, 2006 1:32 am
Location: United Arab Emirates
Contact:

Post by ahmadbilal »

<b>How To get Flexfilds Application wise</b>

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
ahmadbilal
Posts: 615
Joined: Mon Sep 18, 2006 1:32 am
Location: United Arab Emirates
Contact:

Post by ahmadbilal »

<b>How To Check Application wise Responsiblity</b>
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

<font color="red"><font size="2"><font face="Andale Mono"><i> WHERE (SELECT application_short_name
FROM fnd_application fa
WHERE fa.application_id = frv.application_id) = 'SQLAP'</i></font id="Andale Mono"></font id="size2"></font id="red">
Used For Specific Module
ahmadbilal
Posts: 615
Joined: Mon Sep 18, 2006 1:32 am
Location: United Arab Emirates
Contact:

Post by ahmadbilal »

<i><b>how to find mapping between segment name and the value set</b></i>

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
ahmadbilal
Posts: 615
Joined: Mon Sep 18, 2006 1:32 am
Location: United Arab Emirates
Contact:

Post by ahmadbilal »

<font face="Andale Mono"><u><i><b>How to Review PUBLIC Privileges</b></i></u></font id="Andale Mono">
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

Source WEB
ahmadbilal
Posts: 615
Joined: Mon Sep 18, 2006 1:32 am
Location: United Arab Emirates
Contact:

Post by ahmadbilal »

<i><u><b>List All grants From APPS to applsyspub</b></u></i>


SELECT *
FROM dba_tab_privs
WHERE grantee = 'APPLSYSPUB' AND grantor = 'APPS'
ORDER BY table_name

Source Web
ahmadbilal
Posts: 615
Joined: Mon Sep 18, 2006 1:32 am
Location: United Arab Emirates
Contact:

Post by ahmadbilal »

<b>How to find order header information</b>

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 <b>ooh.order_number = &order_number</b>
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');
ahmadbilal
Posts: 615
Joined: Mon Sep 18, 2006 1:32 am
Location: United Arab Emirates
Contact:

Post by ahmadbilal »

<b>To View hold infomration on order</b>

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;
ahmadbilal
Posts: 615
Joined: Mon Sep 18, 2006 1:32 am
Location: United Arab Emirates
Contact:

Post by ahmadbilal »

<b>Folders Created In Application</b>
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
ahmadbilal
Posts: 615
Joined: Mon Sep 18, 2006 1:32 am
Location: United Arab Emirates
Contact:

Post by ahmadbilal »

<b>To Select Unit Of measure exist in ebusiness suite</b>
select uom_code,unit_of_measure
from mtl_units_of_measure
ahmadbilal
Posts: 615
Joined: Mon Sep 18, 2006 1:32 am
Location: United Arab Emirates
Contact:

Post by ahmadbilal »

<b>display all subinventories Setup </b>
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

<u>Query Used on Vision</u>
ahmadbilal
Posts: 615
Joined: Mon Sep 18, 2006 1:32 am
Location: United Arab Emirates
Contact:

Post by ahmadbilal »

<b>display all categories that the item belongs</b> 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


<b>TO View All catagory segment values</b>
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
ahmadbilal
Posts: 615
Joined: Mon Sep 18, 2006 1:32 am
Location: United Arab Emirates
Contact:

Post by ahmadbilal »

<b>physical inventory information for the item</b>
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
ahmadbilal
Posts: 615
Joined: Mon Sep 18, 2006 1:32 am
Location: United Arab Emirates
Contact:

Post by ahmadbilal »

<b>To Find Shipping Organizations</b>


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;
ahmadbilal
Posts: 615
Joined: Mon Sep 18, 2006 1:32 am
Location: United Arab Emirates
Contact:

Post by ahmadbilal »

<b>Look at all applications Name</b>


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;
ahmadbilal
Posts: 615
Joined: Mon Sep 18, 2006 1:32 am
Location: United Arab Emirates
Contact:

Post by ahmadbilal »

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;
Post Reply

Who is online

Users browsing this forum: No registered users and 0 guests