Useful queries for Oracle EBS
-
- Posts: 615
- Joined: Mon Sep 18, 2006 1:32 am
- Location: United Arab Emirates
- Contact:
<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
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
-
- Posts: 615
- Joined: Mon Sep 18, 2006 1:32 am
- Location: United Arab Emirates
- Contact:
<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
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
-
- Posts: 615
- Joined: Mon Sep 18, 2006 1:32 am
- Location: United Arab Emirates
- Contact:
<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
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
-
- Posts: 615
- Joined: Mon Sep 18, 2006 1:32 am
- Location: United Arab Emirates
- Contact:
-
- Posts: 615
- Joined: Mon Sep 18, 2006 1:32 am
- Location: United Arab Emirates
- Contact:
-
- Posts: 615
- Joined: Mon Sep 18, 2006 1:32 am
- Location: United Arab Emirates
- Contact:
<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');
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');
-
- Posts: 615
- Joined: Mon Sep 18, 2006 1:32 am
- Location: United Arab Emirates
- Contact:
<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;
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;
-
- Posts: 615
- Joined: Mon Sep 18, 2006 1:32 am
- Location: United Arab Emirates
- Contact:
<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
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
-
- Posts: 615
- Joined: Mon Sep 18, 2006 1:32 am
- Location: United Arab Emirates
- Contact:
-
- Posts: 615
- Joined: Mon Sep 18, 2006 1:32 am
- Location: United Arab Emirates
- Contact:
<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>
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>
-
- Posts: 615
- Joined: Mon Sep 18, 2006 1:32 am
- Location: United Arab Emirates
- Contact:
<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
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
-
- Posts: 615
- Joined: Mon Sep 18, 2006 1:32 am
- Location: United Arab Emirates
- Contact:
<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
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
-
- Posts: 615
- Joined: Mon Sep 18, 2006 1:32 am
- Location: United Arab Emirates
- Contact:
-
- Posts: 615
- Joined: Mon Sep 18, 2006 1:32 am
- Location: United Arab Emirates
- Contact:
-
- Posts: 615
- Joined: Mon Sep 18, 2006 1:32 am
- Location: United Arab Emirates
- Contact:
Who is online
Users browsing this forum: No registered users and 2 guests