Oracle Items Extract Query

select distinct MSIB.SEGMENT1 MATERIAL_NUMBER,

msib.inventory_item_id,

  mp.organization_code,

  MOQ.SUBINVENTORY_CODE,

  (MIL.SEGMENT1

  ||'.'

  ||MIL.SEGMENT2

  ||'.'

  ||MIL.SEGMENT3) STORAGE_LOCATION,

  case DECODE(MSIB.SERIAL_NUMBER_CONTROL_CODE, 1,'No','Yes')

  when 'Yes'

  then NVL(SUM(MOQ.PRIMARY_TRANSACTION_QUANTITY)/SUM(MOQ.PRIMARY_TRANSACTION_QUANTITY),0)

  when 'No'

  then NVL(SUM(MOQ.PRIMARY_TRANSACTION_QUANTITY),0) end ONHAND_QUANTITY ,

  DECODE(MSIB.LOT_CONTROL_CODE, 1,'No', 2, 'Yes') LOT_CONTROL_YES_NO,

  MLN.LOT_NUMBER,

  MSIB.PRIMARY_UNIT_OF_MEASURE UNIT_OF_MEASURE,

  DECODE(MSIB.SERIAL_NUMBER_CONTROL_CODE, 1,'No','Yes') SERIAL_CONTROL_YES_NO,

  MFG.MEANING SERIAL_CONTROL_LEVEL,

  MSN.SERIAL_NUMBER,

  PAA.CONSIGNED_FROM_SUPPLIER_FLAG,

  CASE CONSIGNED_FROM_SUPPLIER_FLAG

    WHEN 'Y'

    THEN PV.VENDOR_NAME

  END SUPPLIER_NAME,

  CASE CONSIGNED_FROM_SUPPLIER_FLAG

    WHEN 'Y'

    THEN PVS.VENDOR_SITE_CODE

  END SUPPLIER_SITE

FROM MTL_SYSTEM_ITEMS_B MSIB,

  mtl_parameters mp,

  MTL_LOT_NUMBERS MLN,

  MTL_SERIAL_NUMBERS MSN,

  PO_APPROVED_SUPPLIER_LIST PASL,

  PO_VENDORS PV,

  PO_VENDOR_SITES_ALL PVS,

  PO_ASL_ATTRIBUTES PAA,

  MTL_ITEM_LOCATIONS MIL,

  MTL_ONHAND_QUANTITIES_DETAIL MOQ,

  mfg_lookups mfg

WHERE MSIB.ORGANIZATION_ID           = 737 --178 for HOP, 737 for BIO

--and msib.segment1 like '124582'

AND mp.organization_id               = msib.organization_id

AND MSIB.INVENTORY_ITEM_STATUS_CODE <>'Inactive'

AND MSIB.INVENTORY_ITEM_ID           = MLN.INVENTORY_ITEM_ID(+)

AND MSIB.ORGANIZATION_ID             = MLN.ORGANIZATION_ID(+)

AND NVL(MLN.EXPIRATION_DATE,SYSDATE)>=SYSDATE

and Moq.INVENTORY_ITEM_ID           = MSN.INVENTORY_ITEM_ID(+) ---- vishnu changed from table refering

and msn.current_status(+) = 3     --------------------------- Vishnu and "resides in stores"

and msn.current_subinventory_code(+) = moq.subinventory_code ---- Vishnu 

AND MSIB.INVENTORY_ITEM_ID           = PASL.ITEM_ID(+)

AND MSIB.ORGANIZATION_ID             = PASL.USING_ORGANIZATION_ID(+)

AND PASL.VENDOR_ID                   = PV.VENDOR_ID(+)

AND PASL.VENDOR_SITE_ID              = PVS.VENDOR_SITE_ID(+)

and PASL.ASL_ID                      = PAA.ASL_ID(+)

AND (PASL.DISABLE_FLAG IS NULL OR PASL.DISABLE_FLAG = 'N')

AND MSIB.ORGANIZATION_ID             = MOQ.ORGANIZATION_ID

AND MOQ.INVENTORY_ITEM_ID(+)         = MSIB.INVENTORY_ITEM_ID

AND MOQ.LOCATOR_ID                   = MIL.INVENTORY_LOCATION_ID(+)

and MOQ.ORGANIZATION_ID              = MIL.ORGANIZATION_ID(+)

and MFG.LOOKUP_TYPE = 'MTL_SERIAL_NUMBER'

and MFG.LOOKUP_CODE = MSIB.SERIAL_NUMBER_CONTROL_CODE

--and MSIB.SEGMENT1                    ='123721'--'124582'

group by MSIB.SEGMENT1,

msib.inventory_item_id,

  mp.organization_code,

  MOQ.SUBINVENTORY_CODE,

  (MIL.SEGMENT1

  ||'.'

  ||MIL.SEGMENT2

  ||'.'

  ||MIL.SEGMENT3) ,

  DECODE(MSIB.LOT_CONTROL_CODE, 1,'No', 2, 'Yes') ,

  MLN.LOT_NUMBER,

  MSIB.PRIMARY_UNIT_OF_MEASURE ,

  DECODE(MSIB.SERIAL_NUMBER_CONTROL_CODE, 1,'No','Yes'),

  MFG.MEANING,

  MSN.SERIAL_NUMBER,

  PAA.CONSIGNED_FROM_SUPPLIER_FLAG,

  CASE CONSIGNED_FROM_SUPPLIER_FLAG

    WHEN 'Y'

    THEN PV.VENDOR_NAME

  END ,

  CASE CONSIGNED_FROM_SUPPLIER_FLAG

    WHEN 'Y'

    THEN PVS.VENDOR_SITE_CODE

  END

having NVL(SUM(MOQ.PRIMARY_TRANSACTION_QUANTITY),0) > 0;

---> Sponsored by Nisha Ellora