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,
xxen_util.meaning(msiv.item_type,'ITEM_TYPE',3) user_item_type,
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.organization_code in (select oav.organization_code from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) 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,
xxen_util.meaning(msiv.item_type,'ITEM_TYPE',3) user_item_type,
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.organization_code in (select oav.organization_code from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) 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 open requests listing INV Cycle count listing INV Cycle count entries and adjustments INV Cycle counts pending approval CAC Deferred COGS Out-of-Balance CAC Recost Cost of Goods Sold INV Inventory Item Upload COPY OF: INV Inventory Item Upload
Blitz Report™

Blitz Report™ provides multiple benefits: