SELECT
    msi.segment1           AS item ,
    msi.description        AS itemname ,
    mp.organization_code   AS org ,
    moqd.subinventory_code AS inv_prod ,
    cat.project            AS product_name,
    xxmlx_sfm_tools_pkg.get_mtl_status_object( mln.gen_object_id )  AS lot_status,
    MOQD.LOT_NUMBER        AS LOTNUMBER ,
    XXMLX_ONHAND_QTY_PER_SUBINV_F(msi.organization_id, msi.inventory_item_id,
    MOQD.LOT_NUMBER, MOQD.SUBINVENTORY_CODE) AS QTY
  FROM
    mtl_onhand_quantities_detail moqd ,
    mtl_lot_numbers mln ,
    mtl_system_items msi ,
    mtl_parameters mp,
    xxmlx_item_cat_inventory_v cat
  WHERE
    moqd.lot_number          = mln.lot_number
  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 cat.master_item_id(+)  = msi.inventory_item_id
  AND MP.ORGANIZATION_CODE   = 'K02'
  UNION
  SELECT
    msi.segment1           AS item ,
    msi.description        AS itemname ,
    mp.organization_code   AS org ,
    moqd.subinventory_code AS inv_prod ,
    cat.project            AS product_name,
    xxmlx_sfm_tools_pkg.get_mtl_status_object( mln.gen_object_id )  AS lot_status,
    MOQD.LOT_NUMBER        AS LOTNUMBER ,
    CASE
      WHEN XXMLX_ONHAND_QTY_PER_SUBINV_F(msi.organization_id,
        msi.inventory_item_id, MOQD.LOT_NUMBER, MOQD.SUBINVENTORY_CODE) <>
        MOQD.PRIMARY_TRANSACTION_QUANTITY
      THEN MOQD.PRIMARY_TRANSACTION_QUANTITY - XXMLX_ONHAND_QTY_PER_SUBINV_F(
        msi.organization_id, msi.inventory_item_id, MOQD.LOT_NUMBER,
        MOQD.SUBINVENTORY_CODE)
      ELSE XXMLX_ONHAND_QTY_PER_SUBINV_F(msi.organization_id,
        msi.inventory_item_id, MOQD.LOT_NUMBER, MOQD.SUBINVENTORY_CODE)
    END AS QTY
  FROM
    mtl_onhand_quantities_detail moqd ,
    mtl_lot_numbers mln ,
    mtl_system_items msi ,
    mtl_parameters mp,
    xxmlx_item_cat_inventory_v cat
  WHERE
    moqd.lot_number          = mln.lot_number
  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 cat.master_item_id(+)  = msi.inventory_item_id
  AND MP.ORGANIZATION_CODE   = 'K02'
  UNION
  SELECT
    msi.segment1          AS item ,
    msi.description       AS itemname ,
    mp.organization_code  AS org ,
    CASE
      WHEN MS.SUPPLY_TYPE_CODE = 'RECEIVING'
      THEN 'RECEIVING'
      WHEN MS.SUPPLY_TYPE_CODE = 'SHIPMENT'
      THEN 'INTRANSIT'
    END                            AS inv_prod ,
    cat.project            AS product_name,
    RLS.LOT_NUM                    AS LOTNUMBER ,
    xxmlx_sfm_tools_pkg.get_mtl_status_object( mln_from.gen_object_id)  AS lot_status,
    NVL(rls.quantity, ms.quantity) AS QTY
  FROM
    MTL_SUPPLY ms ,
    RCV_LOTS_SUPPLY RLS ,
    mtl_system_items msi ,
    mtl_parameters mp,
    mtl_lot_numbers  mln_from,
    xxmlx_item_cat_inventory_v cat
  WHERE
    1                       =1
  AND MS.SHIPMENT_LINE_ID   = RLS.SHIPMENT_LINE_ID
  AND MS.SUPPLY_TYPE_CODE  IN ('RECEIVING', 'SHIPMENT')
  AND ms.item_id            = msi.inventory_item_id
  AND MS.TO_ORGANIZATION_ID = MSI.ORGANIZATION_ID
  AND msi.organization_id   = mp.organization_id
  AND ms.item_id             = mln_from.inventory_item_id(+)
  AND ms.from_organization_id= mln_from.organization_id(+)
  AND cat.master_item_id(+)  = msi.inventory_item_id
  AND RLS.LOT_NUM IS NOT NULL
  AND MP.ORGANIZATION_CODE  = 'K02'