, , , ,

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

Summary Hold information based on the Invoice Number

When we Purchase some material/Goods/Items from Vendor/Supplier, after receiving the material. Vendor would send the INVOICE (In other words we receive BILL for the Items you have received). And payment will be done automatically. If there is some Discrepancy in the Items received and in the BILL/INVOICE you received, for them to hold the payment we normally set the HOLD rules. From the following Query you can know the Hold reason at the Summary level. 

select * from
(SELECT api.invoice_id, api.invoice_date AS invoice_date,
api.invoice_num AS invoice_num, pov.vendor_id AS vendor_id,
pov.vendor_name AS supplier_name, apd.inv_lines AS total_inv_lines,
NVL (hold_tab_info.hold_inv_lines, 0) AS total_line_holds,
NVL (CEIL ((hold_tab_info.hold_inv_lines * 100) / DECODE(apd.inv_lines,0,1,apd.inv_lines)),
0
) AS percentage_line_hold,
DECODE (hold_tab_info.hold_inv_lines,
NULL, ‘N’,
0, ‘N’,
‘Y’
) AS defect,
DECODE (hold_tab_info.hold_inv_lines,
NULL, 0,
0, 0,
1
) AS defect_count, 1 inv_count,
NVL (hold_count.hold_cnt, 0) AS total_inv_holds,
NVL (c.hold_os, 0) AS days_outstanding,
NVL (api.invoice_amount, 0) AS total_invoice_amount,
NVL (hold_tab_info.hold_amount, 0) AS total_hold_amount,
NVL (CEIL ((hold_tab_info.hold_amount * 100) / DECODE(api.invoice_amount,0,1,api.invoice_amount)),
0
) AS percentage_amount_hold
FROM APPS.ap_invoices_all api,
(SELECT invoice_id, COUNT (invoice_id) inv_lines
FROM APPS.ap_invoice_distributions_all
GROUP BY invoice_id) apd,
(SELECT invoice_id, COUNT (hold_lookup_code) hold_cnt
FROM APPS.ap_holds_all
WHERE 1 = 1 AND line_location_id IS NOT NULL
GROUP BY invoice_id) hold_count,
(SELECT invoice_id, COUNT (hold_tab.line_num) hold_inv_lines,
SUM (hold_tab.hold_amount) hold_amount
FROM (SELECT DISTINCT api.invoice_id invoice_id,
apd.distribution_line_number line_num,
apd.amount hold_amount
FROM APPS.ap_invoices_all api,
APPS.ap_invoice_distributions_all apd,
APPS.po_distributions_all pod,
APPS.ap_holds_all aph
WHERE 1 = 1
AND api.invoice_id = apd.invoice_id
AND aph.invoice_id(+) = api.invoice_id
AND api.cancelled_date IS NULL
AND apd.po_distribution_id = pod.po_distribution_id(+)
AND aph.line_location_id = pod.line_location_id
AND aph.line_location_id IS NOT NULL) hold_tab
GROUP BY invoice_id) hold_tab_info,
(SELECT invoice_id, MAX (b.hold_os) hold_os
FROM (SELECT invoice_id,
DECODE (status_flag,
‘R’, ( TRUNC (NVL (last_update_date,
SYSDATE)
)
– TRUNC (hold_date)
),
(TRUNC (SYSDATE) – TRUNC (hold_date))
) hold_os
FROM APPS.ap_holds_all
WHERE line_location_id IS NOT NULL) b
GROUP BY invoice_id) c,
APPS.po_vendors pov
WHERE 1 = 1
AND hold_tab_info.invoice_id(+) = api.invoice_id
AND c.invoice_id(+) = api.invoice_id
AND api.invoice_id = apd.invoice_id
AND api.vendor_id = pov.vendor_id(+)
AND api.cancelled_date IS NULL
AND api.invoice_id = hold_count.invoice_id(+))
where invoice_num=’Your Invoice number’;

/by