, , ,

On-hand inventory information

The following select statement would extracts all on-hand inventory information from Oracle
Applications base tables.

Note:- You may need to modify the query to match with your instance conditions and your requirements.

SELECT
NVL(substr(org.organization_code, 1,3), ‘ ‘) orgcode
,NVL(substr(msi.segment1, 1, 8), ‘ ‘) seg11
,NVL(substr(msi.segment1, 9, 8), ‘ ‘) seg12
,NVL(substr(msi.segment1, 17, 4), ‘ ‘) seg13
,NVL(moq.subinventory_code, ‘ ‘) sub_inv_code
,NVL(to_char(round(sum(moq.transaction_quantity))), ‘ ‘) trans_qnty
FROM mtL_system_items msi
,org_organization_definitions org
,mtl_onhand_quantities moq
,hr_organization_units hou
WHERE moq.inventory_iteM_id = msi.inventory_item_id
AND moq.organizatioN_id = msi.organizatioN_id
AND moq.organizatioN_id = org.organizatioN_id
AND moq.organization_id = hou.organization_id
— AND hou.type = ‘DC’
GROUP BY org.organization_code
, moq.subinventory_code
, msi.segment1;

I hope the above information would be helpful to you.

/by
, ,

Know license applications

Know license applications:-
——————————-
Lot many times we would not be known if the client has the specific application license or not.

We can ask the client is one way. If you have access to the back-end for the production Instance. Then you could know with following query. This is the Other way. 

All the applications which are Installed status are license one.

SELECT application_name
, application_short_name
,DECODE (status, ‘I’, ‘Installed’, ‘S’, ‘Shared’, ‘Not Installed’) status
, a.application_id
,i.patch_level
FROM fnd_application_all_view a
, fnd_product_installations i
WHERE a.application_id = i.application_id 
ORDER BY 1;

/by