SELECT
    'ON HAND'                               AS supply_location ,
    msi.segment1                            AS item ,
    msi.description                         AS itemname ,
    msi.inventory_item_id                   AS inventory_item_id ,
    mp.organization_code                    AS org ,
    aou.name                                AS organization_name ,
    msi.organization_id                     AS prganization_id ,
    moqd.subinventory_code                  AS inv_prod ,
    moqd.lot_number                         AS lotnumber ,
    milk.segment1                           AS area,
    milk.segment2                           AS line,
    milk.segment3                           AS rack,
    milk.segment4                           AS shelf,
    milk.segment5                           AS place,
    wlpn.license_plate_number,
    wlpn.gross_weight,
    wlpn.tare_weight,
    wlpn.tare_weight_uom_code,
    SUM (moqd.primary_transaction_quantity) AS qty ,
    moqd.date_received                                                                      AS rec_date,
    mln.expiration_date                                                                      AS expiration_date,
    DECODE( mln.c_attribute1,'Y','Yes','N','No',NULL )                                       AS lot_assessment,
    mln.c_attribute3                                                                         AS lot_class,
    xxmlx_sfm_tools_pkg.get_date_code( mln.lot_number )                                   AS date_code,
    mln.d_attribute1                                                                         AS packing_date,
    dev.element_value                                                                        AS product_name,
    mln.c_attribute10                       AS planning_remarks,
    mln.status_id                           AS status_id,
    mms.status_code                         AS status_code,
    NVL(xxmlx_sfm_tools_pkg.get_lot_wafer_qty(moqd.lot_number),moqd.primary_transaction_quantity) AS wafer_qty_out
  FROM
    mtl_item_locations_kfv milk,
    wms_license_plate_numbers wlpn,
    mtl_onhand_quantities_detail moqd ,
    mtl_lot_numbers mln ,
    mtl_system_items msi ,
    mtl_parameters mp ,
    hr_all_organization_units aou,
    mtl_material_statuses_vl mms,
     mtl_descr_element_values dev
  WHERE
    moqd.lot_number          = mln.lot_number(+)
  AND wlpn.lpn_id (+) = moqd.lpn_id
  AND milk.inventory_location_id  = moqd.locator_id
  AND moqd.inventory_item_id = mln.inventory_item_id(+)
  AND moqd.organization_id   = mln.organization_id(+)
  AND moqd.inventory_item_id = msi.inventory_item_id
  AND moqd.organization_id   = msi.organization_id
  AND moqd.organization_id   = mp.organization_id
  AND moqd.organization_id   = aou.organization_id
  AND mln.status_id = mms.status_id(+)
  AND msi.inventory_item_id    = dev.inventory_item_id (+)
  AND dev.element_name (+)     = 'PRODUCT'
  AND XXMLX_DISCOVERER_TOOLS_PKG.is_allowed_to_view(mp.organization_code) = 'Y'
  AND msi.segment1 LIKE '90%'
  GROUP BY
    msi.segment1 ,
    msi.description ,
    msi.inventory_item_id ,
    mp.organization_code ,
    aou.name ,
    msi.organization_id ,
    moqd.subinventory_code ,
    moqd.lot_number ,
    mln.c_attribute10,
    mln.status_id,
    mms.status_code,
    milk.segment1,
    milk.segment2,
    milk.segment3,
    milk.segment4,
    milk.segment5,
    wlpn.license_plate_number,
    wlpn.gross_weight,
    wlpn.tare_weight,
    wlpn.tare_weight_uom_code,
    moqd.date_received,
    mln.expiration_date,
    DECODE( mln.c_attribute1,'Y','Yes','N','No',NULL ),
    mln.c_attribute3,
    xxmlx_sfm_tools_pkg.get_date_code( mln.lot_number ),
    mln.d_attribute1,
    dev.element_value,
     NVL(xxmlx_sfm_tools_pkg.get_lot_wafer_qty(moqd.lot_number),moqd.primary_transaction_quantity);