INV Cycle count hit/miss analysis

Description
Categories: Enginatics
Repository: Github
Imported Oracle standard Cycle count hit/miss analysis report
Source: Cycle count hit/miss analysis (XML)
Short Name: INVARHMA_XML
DB package: INV_INVARHMA_XMLP_PKG
Run INV Cycle count hit/miss analysis and other Oracle EBS reports with Blitz Report™ on our demo environment
select
mcch.organization_code,
mcch.organization_name,
mcch.currency_code,
mcch.cycle_count_header_name cycle_count_name,
msiv.concatenated_segments item_number,
msiv.description item_description,
inv_project.get_locator(mil.inventory_location_id,mil.organization_id) locator,
mil.description locator_description,
mcce.count_date_first,
mcce.count_date_prior,
mcce.count_date_current,
(
select
ml.meaning
from
mfg_lookups ml
where
ml.lookup_type='SYS_YES_NO' and
ml.lookup_code=(case when nvl(mcce.serial_detail,0)>0 then 1 else 2 end)) serialized_item_included,
mcce.subinventory subinventory,
mac.abc_class_name class,
nvl(mccc.hit_miss_tolerance_positive, mcch.hit_miss_tolerance_positive)  hit_miss_tolerance_positive,
nvl(mccc.hit_miss_tolerance_negative, mcch.hit_miss_tolerance_negative) hit_miss_tolerance_negative,
100*( 1 - 
case 
when mcce.serial_detail=2 and mcce.serial_number is not null then
case when (mcce.adj_quantity=0) or (mcce.adj_quantity is null) then 0 else 1 end
else 
case 
when(mcce.adj_quantity=0) or (mcce.adj_quantity is null) then 0
when (mcce.adj_quantity<0) then case when (abs(mcce.adj_quantity)>=(mcce.system_quantity_first*nvl(mccc.hit_miss_tolerance_negative, mcch.hit_miss_tolerance_negative)/100)) then 1 else 0 end
else case when (abs((mcce.count_quantity_first - mcce.system_quantity_first ))>=(mcce.system_quantity_first*nvl(mccc.hit_miss_tolerance_positive, mcch.hit_miss_tolerance_positive)/100)) then 1 else 0 end
end
end) accuracy_percent,
mcce.system_quantity_first  system_quantity,
mcce.adj_quantity adjusted_quantity,
case when mcce.serial_detail=2 and mcce.serial_number is not null then
case when (mcce.adj_quantity=0) or (mcce.adj_quantity is null) then 0 else 1 end
else
case when (mcce.adj_quantity = 0) or (mcce.adj_quantity is null) then 0  
when (mcce.adj_quantity<0) then case when abs(mcce.adj_quantity)>=(mcce.system_quantity_first * nvl(mccc.hit_miss_tolerance_negative, mcch.hit_miss_tolerance_negative)/100) then 1 else 0 end
else case when abs(mcce.adj_quantity)>=(mcce.system_quantity_first * nvl(mccc.hit_miss_tolerance_positive, mcch.hit_miss_tolerance_positive)/100) then 1 else 0 end
end
end out_tolerance_flag
from 
mtl_abc_classes mac,
mtl_cycle_count_classes mccc,
mtl_item_locations mil,
mtl_system_items_vl msiv,
(
select 
ood.organization_name,
ood.organization_code,
gl.currency_code,
mcch.*
from
org_organization_definitions ood,
gl_ledgers gl,
mtl_cycle_count_headers mcch
where
1=1 and
ood.set_of_books_id=gl.ledger_id and
nvl(mcch.disable_date,sysdate+1)>sysdate and
mcch.organization_id=ood.organization_id) mcch,
mtl_cycle_count_items mcci,
(select
mcce.*,
(mcce.count_quantity_first-mcce.system_quantity_first) adj_quantity
from
mtl_cycle_count_entries mcce
) mcce
where
2=2 and
mcch.cycle_count_header_id=mcci.cycle_count_header_id and
mcch.cycle_count_header_id=mcce.cycle_count_header_id and
mcch.cycle_count_header_id=mccc.cycle_count_header_id and
mcce.inventory_item_id=mcci.inventory_item_id and
msiv.inventory_item_id=mcce.inventory_item_id and
msiv.organization_id=mcce.organization_id and
mcce.locator_id=mil.inventory_location_id(+) and
mcce.organization_id=mil.organization_id(+) and
mcci.abc_class_id=mac.abc_class_id and
mcci.abc_class_id=mccc.abc_class_id and
(mcce.entry_status_code=5 or mcce.entry_status_code=2 or mcce.entry_status_code=3) and
mcce.count_type_code<>4
order by 
mcce.subinventory,
mac.abc_class_name
Parameter Name SQL text Validation
Organization Code
ood.organization_code=:organization_code
LOV
Cycle Count Name
mcch.cycle_count_header_name=:cycle_count_header_name
LOV
Start Date
mcce.count_date_first>=:date_from
Date
End Date
mcce.count_date_first<:date_to+1
Date