Wednesday, November 20, 2013

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

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect