Query :

SELECT round (SUM (cpc.accounted_value), 2) accounted_value,
round (SUM (cpc.accounted_onhand_value), 2) accounted_onhand_value,
round (SUM (cpc.rollback_onhand_value), 2) rollback_onhand_value,
round (SUM (cpc.onhand_value_discrepancy), 2) discrepancy,
msi.segment1, cpc.organization_id,oap.period_name
FROM inv.org_acct_periods oap,
bom.cst_period_close_summary cpc,
inv.mtl_system_items_b msi
WHERE oap.organization_id = cpc.organization_id
AND oap.acct_period_id = cpc.acct_period_id
AND oap.period_name = 'NOV-08'
AND msi.organization_id = &org_id
AND msi.inventory_item_id = cpc.inventory_item_id
HAVING TRUNC (SUM (cpc.onhand_value_discrepancy), 2) > 1
OR TRUNC (SUM (cpc.onhand_value_discrepancy), 2) < -1
GROUP BY msi.segment1, cpc.organization_id,oap.period_name

Read More