INV ABC assignments (1)

select sys.item_number,
      sys.description,
      clas.abc_class_name,
      com.sequence_number,
      round(nvl(com.compile_quantity,0), :P_qty_precision)     
      compile_quantity,
     round(nvl(com.compile_value, 0) , :C_std_precision)     
                 compile_value1, 
      sys.primary_uom_code
from
       mtl_item_flexfields sys,
       mtl_abc_classes     clas,
       mtl_abc_compiles com,
       mtl_abc_assignment_groups ass,
       mtl_abc_assignments  abc
where
           sys.organization_id = :org_id
 and   clas.organization_id = :org_id
 and   com.organization_id = :org_id
 and   ass.organization_id = :org_id
 and   ass.assignment_group_id = :group_id
 and   ass.compile_id    = com.compile_id
 and   abc.inventory_item_id  = sys.item_id
 and   abc.assignment_group_id = :group_id
 and   abc.abc_class_id            = clas.abc_class_id
 and   abc.inventory_item_id   = com.inventory_item_id
UNION
select sys.item_number,
      sys.description,
      clas.abc_class_name,
      decode (1, 0, 0,null)  sequence_number,
      0   compile_quantity,
      0   compile_value, 
      sys.primary_uom_code
from
       mtl_item_flexfields sys,
       mtl_abc_classes     clas,
       mtl_abc_assignment_groups ass,
       mtl_abc_assignments  abc
where
           sys.organization_id = :org_id
 and   clas.organization_id = :org_id
 and   ass.organization_id = :org_id
 and   ass.assignment_group_id = :group_id
 and   abc.inventory_item_id  = sys.item_id
 and   abc.assignment_group_id = :group_id
 and   abc.abc_class_id            = clas.abc_class_id
 and   not Exists 
           (Select *
             From mtl_abc_compiles   com
             Where
                abc.inventory_item_id   = com.inventory_item_id
                and com.organization_id = :org_id
                and ass.compile_id          = com.compile_id )
 &C_ORDERBY