, , ,

How to find concurrent program attached to which responsibility

You know the concurrent program name, but you do not know to what all responsibilities it is attached to. In that case, you can use the following select statement to know the responsibilities names to which your concurrent program is attached.

select responsibility_name
from fnd_responsibility_tl rsp_tl, fnd_responsibility fr, –fnd_request_groups frg,
fnd_request_group_units frgu, fnd_concurrent_programs_tl fcpt
where rsp_tl.responsibility_id = fr.responsibility_id
–and frg.request_group_id = fr.request_group_id
and fr.request_group_id = frgu.request_group_id
and fcpt.concurrent_program_id = frgu.request_unit_id
and upper(fcpt.USER_CONCURRENT_PROGRAM_NAME) = upper(‘concurrent program name’);

Example:-

select responsibility_name
from fnd_responsibility_tl rsp_tl, fnd_responsibility fr, –fnd_request_groups frg,
fnd_request_group_units frgu, fnd_concurrent_programs_tl fcpt
where rsp_tl.responsibility_id = fr.responsibility_id
–and frg.request_group_id = fr.request_group_id
and fr.request_group_id = frgu.request_group_id
and fcpt.concurrent_program_id = frgu.request_unit_id
and upper(fcpt.USER_CONCURRENT_PROGRAM_NAME) = upper(‘OracleERPApps Test Program’);

/by
, , , ,

Get the Inventory code and Inventory Name/description

Note:- Inventory Organization will be represented with the Organization_id column.
We many give some code and name to the Inventory. With the following Query we can know the Code and Name details of all the Inventories in the Organization.

SELECT distinct SUBSTR(loc.location_code, 1, 4) “Inventory Code”
,DECODE(SUBSTR(loc.tax_name, 1, 3) 
,’Bay’, ‘H’ 
,’Zel’, ‘Z’ 
,’KMT’, ‘Z’) || lpad(substr(loc.location_code, 1, 4), 4, ‘0’)|| ‘ ‘ ||
SUBSTR(loc.address_line_2, 1, 30) “Inventory Name/Description”,
ou.ORGANIZATION_ID “Inventory Organization ID”
FROM hr_locations_all loc,
hr_all_organization_units ou 
WHERE loc.location_id = ou.location_id;

/by
, ,

Department Number, Category Number and Cost of item based on the Item ID

The Following Query will give the Department Number, Category Number, Cost of the Item based on the Inventory item ID.

select mcb.segment3 “Deptartment” ,mcb.segment4 “Category”, cs.item_cost “Item price”
from mtl_categories_b mcb, cst_item_costs cs
where mcb.category_id IN (select CATEGORY_ID from mtl_item_categories 
where inventory_item_id = ‘Your Inventory Item ID’
and organization_id = ‘Your Inventory Organization ID’ and category_set_id = 1 and rownum = 1)
and cs.organization_id = ‘Your Inventory Organization ID’
and cs.cost_type_id = 1
and cs.inventory_item_id = ‘Your Inventory Item ID’;

Example:-
———–

select mcb.segment3 “Deptartment” ,mcb.segment4 “Category”, cs.item_cost “Item price”
from mtl_categories_b mcb, cst_item_costs cs
where mcb.category_id IN (select CATEGORY_ID from mtl_item_categories
where inventory_item_id = 122251
and organization_id = 22 and category_set_id = 1 and rownum = 1)
and cs.organization_id = 22
and cs.cost_type_id = 1
and cs.inventory_item_id = 122251;


/by
, ,

Item Catalog, Descriptive Element Value

Note:- The following Query can get multiple records, since one Item can assoiciate to many Inventory Organizations.

In the following Query, I have commented the organization_id condition and given the rownum = 1 condition.

Catalog is the Flexfield. The values in these also depends upon the Set-up part of the Catalog Flexfield.

MTL_DESCR_ELEMENT_VALUES stores the descriptive element values for a specific item. When an item is associated with a particular item catalog group, one row per descriptive element (for that catalog group) is inserted into this table.

SELECT c.segment1 || ‘-‘ || a.element_value
FROM mtl_descr_element_values a
,mtl_descriptive_elements b
,mtl_item_catalog_groups c
,mtl_system_items_fvl d
WHERE a.element_name = b.element_name
AND a.inventory_item_id = d.inventory_item_id
— AND d.organization_id = ‘Your Organization ID’
AND b.item_catalog_group_id = d.item_catalog_group_id
AND b.item_catalog_group_id = c.item_catalog_group_id
AND a.element_name = b.element_name
AND a.element_name LIKE ‘%Vendor%’
AND a.inventory_item_id = ‘Your Inventory Item ID’
AND ROWNUM = 1;
/by
, , , ,

On-hand Quatity of one Particular Item in whole Organization

The following Query will On-hand Quatity of particular item in the whole Organization

select sum(primary_transaction_quantity) from mtl_onhand_quantities_detail where inventory_item_id = ‘Your Inventory Organization ID’;

Example:-

Note:- 123456 is the item ID of the particular item. This can be found in the base table of the item (MTL_SYSTEM_ITEMS_B). SEGMENT1 column of this table would have Item Name and Inventory_item_id is the primary column of the table. The follow query is using this value.

select sum(primary_transaction_quantity) from mtl_onhand_quantities_detail where inventory_item_id = 123456;

If you want to know the on-hand Quantity of particular item at all the Inventory Organization then use the following the Query.

select sum(primary_transaction_quantity), organization_id from mtl_onhand_quantities_detail where inventory_item_id = ‘Your Inventory Organization ID’
group by organization_id;

/by