INV Physical inventory accuracy analysis

➜ Back to Library
Description
Categories: Enginatics
Repository: Github
Imported from BI Publisher
Description: Physical inventory accuracy analysis
Application: Inventory
Source: Physical inventory accuracy analysis (XML)
Short Name: INVARPIA_XML
DB package: INV_INVARPIA_XMLP_PKG
select
gl.name ledger,
ood.organization_code,
mpi.physical_inventory_name,
msiv.concatenated_segments item,
msiv.description,
&category_columns
mpacv.revision,
round(mpacv.system_quantity,:p_qty_precision) system_quantity,
msiv.primary_uom_code uom,
round(nvl(mpacv.count_quantity,0),:p_qty_precision) count_quantity,
round(mpacv.adjustment_quantity,:p_qty_precision) adjustment_quantity,
round(nvl(system_quantity*mpacv.actual_cost,0),fc.precision) system_value,
round(nvl(count_quantity*mpacv.actual_cost,0),fc.precision) count_value,
round(nvl(nvl(count_quantity*mpacv.actual_cost,0)-mpacv.system_quantity*mpacv.actual_cost,0),fc.precision) adjustment_value,
decode(round(nvl(mpacv.count_quantity*mpacv.actual_cost,0),fc.precision),0,0,
round(nvl(nvl(mpacv.count_quantity*mpacv.actual_cost,0)-mpacv.system_quantity*mpacv.actual_cost,0),fc.precision)*100/round(nvl(mpacv.count_quantity*mpacv.actual_cost,0),fc.precision)) adjustment_percent
from
mtl_physical_inventories mpi,
mtl_phy_adj_cost_v mpacv,
org_organization_definitions ood,
gl_ledgers gl,
fnd_currencies fc,
mtl_system_items_vl msiv
where
1=1 and
mpacv.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) and
mpi.physical_inventory_id=mpacv.physical_inventory_id and
(mpacv.count_quantity<>0 or mpacv.system_quantity<>0) and
mpacv.organization_id=ood.organization_id and
ood.set_of_books_id=gl.ledger_id and
gl.currency_code=fc.currency_code and
mpacv.organization_id=msiv.organization_id and
mpacv.inventory_item_id=msiv.inventory_item_id
order by
mpi.physical_inventory_name,
nvl(mpacv.count_quantity*mpacv.actual_cost-mpacv.system_quantity*mpacv.actual_cost,0) asc,
msiv.concatenated_segments
Parameter NameSQL textValidation
Organization Code
ood.organization_code=:organization_code
LOV
Physical Inventory
mpacv.physical_inventory_id=:phys_inv_id
LOV Oracle
Category Set 1
select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>') sql_text from dual
LOV
Category Set 2
select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>') sql_text from dual
LOV
Category Set 3
select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>') sql_text from dual
LOV
Exclude Rejected Items
nvl(mpacv.approval_status,1)<>2
LOV
Download
Related Blitz Reports
CAC Deferred COGS Out-of-Balance CAC Recost Cost of Goods Sold INV Material Account Distribution Detail INV Material Transactions2 INV Material Transactions INV Cycle count entries and adjustments INV Transaction Register CAC Onhand Lot Value (Real-Time) CAC Missing Material Accounting Transactions
Blitz Report™