Movement Statistics Report Shows Commodity Code As Blank

Log a service request to get assistance. The following SQL might help identify the cause of the issue.



STEPS
1. Navigate to Inventory > reports > transactions > run
2. Run movement statistics report (Standard report: Movement Statistics Report - INVSTMVT.rdf)
* Choose options including "Movement Type = Dispatch".
* Report Title:   Dispatch Movement Statistics Summary
3. Review output -- See commodity code column is blank

 
1) Confirm the category set id from Movement Parameter form:
select category_set_id
from mtl_stat_type_usages
where legal_entity_id = &legal_entity_id
and zone_code = 'EC'
and usage_type = 'INTERNAL'
and stat_type = 'INTRASTAT';

2) Find out category_id for the specific movement id

This is to find out if there is commodity code defined for the transaction item.
SELECT  sic.category_id
FROM  MTL_SYSTEM_ITEMS si  , MTL_ITEM_CATEGORIES sic
WHERE si.inventory_item_id = sic.inventory_item_id
AND si.organization_id = sic.organization_id
AND si.inventory_item_id = &inventory_item_id
AND si.organization_id = &organization_id
AND sic.category_set_id = &CategorysetidFromSQL1;

3) Find out commodity code :
SELECT substrb(mkv.concatenated_segments,1,230) , substrb(mic.description,1,230)
FROM MTL_CATEGORIES mic , MTL_CATEGORIES_KFV mkv
WHERE mic.category_id = mkv.category_id
AND mic.category_id = &CategoryIDFromSQL2;