Useful queries for Oracle EBS

[ahmadbilal]

<b>Get Onhand Qty</b>
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

[ahmadbilal]

<b>Employee setup in the system</b>
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
)
)

[ahmadbilal]

<b>Check Locators for inventory Inventory Org Wise(Number of segment may varry)</b>

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

[ahmadbilal]

<b>To Check Item Catogry For Inventory master (No Of Segments May Varry)</b>

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

[zosamanet]

Hi Mr:Ahamd bilal

Thank you alot about these queries its very nice to help us

thanx again

osama

[ahmadbilal]

<b>To Get User Having Responsiblity for a specific Application</b>
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);

[ahmadbilal]

<b>How to get Application Installation Status and Patch Level</b>
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;

[ahmadbilal]

<b>Get schema user name and objects owned by that particular schema user name</b>

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

[ahmadbilal]

<b>Number of line processed in Order Management</b>

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

[ahmadbilal]

<b>Get Number Of canceled requisition</b>

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('01/01/2007', 'DD/MM/YYYY')
and TO_DATE('30/05/2007', 'DD/MM/YYYY')
and a.AUTHORIZATION_STATUS = 'CANCELLED'
group by a.AUTHORIZATION_STATUS,a.ORG_ID,a.CREATED_BY

[ahmadbilal]

<b>Check item where description of item and long description of item is not matched</b>


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

[ahmadbilal]

<b>To Find Application(Product) and Patch Set level</b>

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;

[ahmadbilal]

<b>To Find Duplicate Item Category Code</b>

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

[ahmadbilal]

<b>To Find on hand Quantity Locater wise and lot wise </b>

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

[ahmadbilal]

<b>How to get Document Sequence Detail</b>
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