INV Cycle count unscheduled items

➜ Back to Library
Description
Categories: Enginatics
Repository: Github
Imported Oracle standard Cycle count unscheduled items report
Source: Cycle count unscheduled items report (XML)
Short Name: INVARUIR_XML
DB package: INV_INVARUIR_XMLP_PKG
Run INV Cycle count unscheduled items and other Oracle EBS reports with Blitz Report™ on our demo environment
select
ood.organization_code,
ood.organization_name,
gl.currency_code,
mcch.cycle_count_header_name cycle_count_name,
decode(mcch.autoschedule_enabled_flag,1, 'Yes', 'No') auto_schedule_flag,
case when decode(mcch.autoschedule_enabled_flag, 1, mcch.schedule_interval_time, -1)=-1 then null
else (select ml.meaning from mfg_lookups ml where ml.lookup_type = 'MTL_CC_SCHED_TIME' and ml.lookup_code=decode(mcch.autoschedule_enabled_flag, 1, mcch.schedule_interval_time, -1))
end scheduled_interval,
msiv.concatenated_segments item,
msiv.description description,
mcci.item_last_schedule_date,
mac.abc_class_name,
mcch.f_nworkdaysyear/decode( mccc.num_counts_per_year,null,1,0,1,mccc.num_counts_per_year) count_interval_work_days
from
mtl_abc_classes mac,
mtl_system_items_vl msiv,
(select mcch.*,
nvl(inv_invaruir_xmlp_pkg.f_nworkdaysyearformula(mcch.cycle_count_calendar, mcch.calendar_exception_set),0) f_nworkdaysyear
from
mtl_cycle_count_headers mcch
where
nvl(mcch.disable_date,sysdate+1)>sysdate) mcch,
mtl_cycle_count_items mcci,
mtl_cycle_count_classes mccc,
org_organization_definitions ood,
gl_ledgers gl
where
1=1 and
ood.set_of_books_id=gl.ledger_id and
mcch.organization_id=ood.organization_id and
msiv.organization_id=ood.organization_id and
mac.organization_id=ood.organization_id and
mcch.autoschedule_enabled_flag=1 and
mcci.abc_class_id=mac.abc_class_id and
mcci.cycle_count_header_id=mcch.cycle_count_header_id and
msiv.inventory_item_id=mcci.inventory_item_id and
mccc.abc_class_id=mac.abc_class_id and
mccc.cycle_count_header_id=mcch.cycle_count_header_id and
mccc.organization_id=ood.organization_id and
(((mcci.item_last_schedule_date +( mcch.f_nworkdaysyear/decode(mccc.num_counts_per_year,null,1,0,1,mccc.num_counts_per_year) ))<=sysdate)or (mcci.item_last_schedule_date is null ))
union all
select
ood.organization_code,
ood.organization_name,
gl.currency_code,
mcch.cycle_count_header_name cycle_count_name,
decode(mcch.autoschedule_enabled_flag,1, 'Yes', 'No') auto_schedule_flag,
case when decode(mcch.autoschedule_enabled_flag, 1, mcch.schedule_interval_time, -1)=-1 then null
else (select mfg.meaning from mfg_lookups mfg where mfg.lookup_type = 'MTL_CC_SCHED_TIME' and mfg.lookup_code = decode(mcch.autoschedule_enabled_flag, 1, mcch.schedule_interval_time, -1))
end scheduled_interval,
msiv.concatenated_segments item,
msiv.description description,
mcci.item_last_schedule_date,
mac.abc_class_name,
null count_interval_work_days
from
mtl_abc_classes mac,
mtl_system_items_vl msiv,
mtl_cycle_count_headers mcch,
mtl_cycle_count_items mcci,
org_organization_definitions ood,
gl_ledgers gl
where
1=1 and
ood.set_of_books_id=gl.ledger_id and
mcch.organization_id=ood.organization_id and
msiv.organization_id=ood.organization_id and
mac.organization_id=ood.organization_id and
nvl(mcch.disable_date,sysdate+1)>sysdate and
mcch.autoschedule_enabled_flag<>1 and
mcci.abc_class_id=mac.abc_class_id and
mcch.cycle_count_header_id=mcci.cycle_count_header_id and
msiv.inventory_item_id=mcci.inventory_item_id and
(((mcci.item_last_schedule_date+5)<=sysdate) or (mcci.item_last_schedule_date is null))
order by
abc_class_name,
item
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
Download
Related Blitz Reports
INV Cycle count hit/miss analysis INV Cycle count listing INV Cycle count open requests listing INV Cycle counts pending approval INV Cycle count entries and adjustments INV Cycle count schedule requests CAC Deferred COGS Out-of-Balance INV Print Cycle Count Entries Open Interface data INV Serial number detail
Blitz Report™

Blitz Report™ provides multiple benefits: