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

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;

Get the Inventory code and Inventory Name/description

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;

Item Catalog, Descriptive Element Value

Catalog and Descriptive element value on the Inventory Item ID of the Item:-
-----------------------------------------------------------------------------------------

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;

Modifier Header ID based on the Modifier Description

INV Modifier Header ID based on the Modifier Description:-
---------------------------------------------------------------------

The following Query would get the Modifier Header ID based on the Description given for that Modifier. In many places we use the Modifier Header ID to get other values related to that Modifier.

SELECT list_header_id "Modifier Header ID"
FROM qp_list_headers_b
WHERE trim(SUBSTR (comments, 1, 240)) = trim('ALLORACLETECH Description') AND ROWNUM = 1;

Get the Modifier Description Based on the Modifier ID

The following Query will get the Modifier Description given for the Modifier based on the Modifier Header ID.

SELECT SUBSTR (comments, 1, 240) "Modifier Description"
FROM qp_list_headers_b
WHERE list_header_id = 'Your modifier header ID' AND ROWNUM = 1;

Oracle Assets Module

Oracle Assets Module

This section has all the changes in Oracle Assets Module in the 12 Release.

Overview

Subledger Accounting Architecture

Invoice Distributions from Oracle Payables

Global Descriptive Flexfield Migration for Greece

Global Descriptive Flexfield Migration for Greece

Oracle Assets : Global Descriptive Flexfield Migration for Greece :-
----------------------------------------------------------------------------

Commitment and Investment Law will be migrated from Global Descriptive Flexfields
specific to Greece localizations into named fields that will appear in the Asset
Workbench and be available to all users. The global_attribute1 and global_attribute2
columns in Assets will be copied into the Commitment and Investment Law fields,
respectively.

Invoice Distributions from Oracle Payables

Oracle Assets : Invoice Distributions from Oracle Payables:-
---------------------------------------------------------------------

Invoice distributions from Oracle Payables that have been interfaced to Assets will be
upgraded to display the Invoice Line Number. This new field will appear in the Asset
Workbench, Financial Inquiry, and Mass Additions Prepare forms.