Join the OracleApps88 Telegram group @OracleApps88to get more information on Oracle EBS R12/Oracle Fusion applications.

If you are facing any issues while copying the Code/Script or any issues with Posts, Please send a mail to OracleApp88@Yahoo.com or message me at @apps88 or +91 905 957 4321 in telegram.

Wednesday, November 20, 2013

Oracle Item Extract Query



SELECT DISTINCT MSIB.SEGMENT1 ITEM_NUMBER,
  --MSIB.ITEM_TYPE,
  MSIB.DESCRIPTION ITEM_DESCRIPTION,
  mita.report_user_value user_item_type,
  --MSIB.ORGANIZATION_ID,
  MP.ORGANIZATION_CODE,
  ROUND(
  (SELECT item_cost
  FROM cst_item_costs
  WHERE organization_id = msib.organization_id
  AND inventory_item_id = msib.inventory_item_id
  AND cost_type_id      = 1
  ),5) frozen_item_cost,
  MIL.SUBINVENTORY_CODE SUBINVENTORY,
  (MIL.SEGMENT1
  ||'.'
  ||MIL.SEGMENT2
  ||'.'
  ||MIL.SEGMENT3) "LOCATOR",
  MSIB.PRIMARY_UOM_CODE,
  MSIB.PRIMARY_UNIT_OF_MEASURE,
  (SELECT FFVT.DESCRIPTION
  FROM FND_FLEX_VALUE_SETS FFVS,
    FND_FLEX_VALUES FFV,
    fnd_flex_values_tl FFVT
  WHERE FFVS.FLEX_VALUE_SET_NAME = 'CALIPER_GL_PROD_LINE'
  AND FFVS.flex_value_set_id     =FFV.flex_value_set_id
  AND FFVT.FLEX_VALUE_ID         =FFV.FLEX_VALUE_ID
  AND FFVT.LANGUAGE              = USERENV('LANG')
  AND FFV.FLEX_VALUE             =
    (SELECT GCC.SEGMENT4
    FROM GL_CODE_COMBINATIONS GCC
    WHERE MSIB.COST_OF_SALES_ACCOUNT=GCC.CODE_COMBINATION_ID
    )
  ) MATERIAL_GROUP,
  MSIB.ATTRIBUTE5 EXTERNAL_MATERIAL_GROUP ,
  MP.ORGANIZATION_CODE LABORATORY_OFFICE,
  (SELECT GCC.SEGMENT4
  FROM GL_CODE_COMBINATIONS GCC
  WHERE MSIB.COST_OF_SALES_ACCOUNT=GCC.CODE_COMBINATION_ID
  ) product_line_code,
  (SELECT FFVT.DESCRIPTION
  FROM FND_FLEX_VALUE_SETS FFVS,
    FND_FLEX_VALUES FFV,
    fnd_flex_values_tl FFVT
  WHERE FFVS.FLEX_VALUE_SET_NAME = 'CALIPER_GL_PROD_LINE'
  AND FFVS.flex_value_set_id     =FFV.flex_value_set_id
  AND FFVT.FLEX_VALUE_ID         =FFV.FLEX_VALUE_ID
  AND FFVT.LANGUAGE              = USERENV('LANG')
  AND FFV.FLEX_VALUE             =
    (SELECT GCC.SEGMENT4
    FROM GL_CODE_COMBINATIONS GCC
    WHERE MSIB.COST_OF_SALES_ACCOUNT=GCC.CODE_COMBINATION_ID
    )
  ) PRODUCT_LINE,
  (SELECT GCC.SEGMENT5
  FROM GL_CODE_COMBINATIONS GCC
  WHERE MSIB.COST_OF_SALES_ACCOUNT=GCC.CODE_COMBINATION_ID
  ) product_class_code,
  (SELECT FFVT.DESCRIPTION
  FROM FND_FLEX_VALUE_SETS FFVS,
    FND_FLEX_VALUES FFV,
    fnd_flex_values_tl FFVT
  WHERE FFVS.FLEX_VALUE_SET_NAME = 'CALIPER_GL_PROD_CLASS'
  AND FFVS.flex_value_set_id     =FFV.flex_value_set_id
  AND FFVT.FLEX_VALUE_ID         =FFV.FLEX_VALUE_ID
  AND FFVT.LANGUAGE              = USERENV('LANG')
  AND FFV.FLEX_VALUE             =
    (SELECT GCC.SEGMENT5
    FROM GL_CODE_COMBINATIONS GCC
    WHERE MSIB.COST_OF_SALES_ACCOUNT=GCC.CODE_COMBINATION_ID
    )
  ) PRODUCT_CLASS,
  MSIB.WEIGHT_UOM_CODE,
  MSIB.ATTRIBUTE4 OLD_MATERIAL_NUMBER,
  MP.ORGANIZATION_CODE DELIVERING_PLANT,
  MSIB.CREATION_DATE,
  MSIB.LOT_CONTROL_CODE,
  ML.MEANING LOT_CONTROL,
  MSIB.SHELF_LIFE_CODE,
  ML1.MEANING LOT_EXPIRATION,
  MSIB.SERIAL_NUMBER_CONTROL_CODE,
  ML2.MEANING SERIAL_GENERATION,
  (SELECT FFVT.DESCRIPTION
  FROM FND_FLEX_VALUE_SETS FFVS,
    FND_FLEX_VALUES FFV,
    fnd_flex_values_tl FFVT
  WHERE FFVS.FLEX_VALUE_SET_NAME = 'CALIPER_GL_PROD_LINE'
  AND FFVS.flex_value_set_id     =FFV.flex_value_set_id
  AND FFVT.FLEX_VALUE_ID         =FFV.FLEX_VALUE_ID
  AND FFVT.LANGUAGE              = USERENV('LANG')
  AND FFV.FLEX_VALUE             =
    (SELECT GCC.SEGMENT4
    FROM GL_CODE_COMBINATIONS GCC
    WHERE MSIB.COST_OF_SALES_ACCOUNT=GCC.CODE_COMBINATION_ID
    )
  ) Profit_center,
  MSIB.ATTRIBUTE6 COUNTRY_OF_ORIGIN,
  MSIB.BUYER_ID,
  PAPF.FULL_NAME BUYER_NAME,
  MSIB.PREPROCESSING_LEAD_TIME,
  (SELECT abc_class_name
  FROM mtl_abc_classes mac,
    mtl_abc_assignments maa,
    MTL_ABC_ASSIGNMENT_GROUPS MAAG
  WHERE 1                     =1
  AND maa.inventory_item_id   = msib.inventory_item_id
  AND maa.assignment_group_id = maag.assignment_group_id
  AND maa.abc_class_id        = mac.abc_class_id
  AND MAAG.ORGANIZATION_ID    = MSIB.ORGANIZATION_ID
  AND maa.creation_date      >=
    (SELECT MAX(maa.creation_date)
    FROM mtl_abc_assignments maa,
      mtl_abc_assignment_groups maag
    WHERE 1                     =1
    AND maa.inventory_item_id   = msib.inventory_item_id
    AND MAA.ASSIGNMENT_GROUP_ID = MAAG.ASSIGNMENT_GROUP_ID
    AND maag.organization_id    = msib.organization_id
    )
  ) ABC_INDICATOR,
  MSIB.PLANNER_CODE,
  MPL.DESCRIPTION,
  MSIB.FIXED_ORDER_QUANTITY FXD_LOT_SIZE_FXD_ORDER_QTY,
  MSIB.FIXED_DAYS_SUPPLY FXD_LOT_SIZE_FXD_DAYS_SPLY,
  MSIB.FIXED_LOT_MULTIPLIER FXD_LOT_SIZE_FXD_LOT_MLTPLR,
  MSIB.MINIMUM_ORDER_QUANTITY FXD_LOT_SIZE_MIN_ORDER_QTY,
  MSIB.MAXIMUM_ORDER_QUANTITY FXD_LOT_SIZE_MAX_ORDER_QTY,
  MSIB.MIN_MINMAX_QUANTITY FXD_LOT_SIZE_MIN_MINMAX_QTY,
  MSIB.MAX_MINMAX_QUANTITY FXD_LOT_SIZE_MAX_MINMAX_QTY,
  MSIB.FIXED_ORDER_QUANTITY MIN_LOT_SIZE_FIXED_ORDER_QTY,
  MSIB.FIXED_DAYS_SUPPLY MIN_LOT_SIZE_FIXED_DAYS_SPLY,
  MSIB.FIXED_LOT_MULTIPLIER MIN_LOT_SIZE_FIXED_LOT_MLTPLR,
  MSIB.MINIMUM_ORDER_QUANTITY MIN_LOT_SIZE_MIN_ORDER_QTY,
  MSIB.MAXIMUM_ORDER_QUANTITY MIN_LOT_SIZE_MAX_ORDER_QTY,
  MSIB.MIN_MINMAX_QUANTITY MIN_LOT_SIZE_MIN_MINMAX_QTY,
  MSIB.MAX_MINMAX_QUANTITY MIN_lot_size_MAX_MINMAX_QTY,
  MSIB.FIXED_ORDER_QUANTITY MAX_LOT_SIZE_FIXED_ORDER_QTY,
  MSIB.FIXED_DAYS_SUPPLY MAX_LOT_SIZE_FIXED_DAYS_SPLY,
  MSIB.FIXED_LOT_MULTIPLIER MAX_LOT_SIZE_FIXED_LOT_MLTPLR,
  MSIB.MINIMUM_ORDER_QUANTITY MAX_LOT_SIZE_MIN_ORDER_QTY,
  MSIB.MAXIMUM_ORDER_QUANTITY MAX_LOT_SIZE_MAX_ORDER_QTY,
  MSIB.MIN_MINMAX_QUANTITY MAX_LOT_SIZE_MIN_MINMAX_QTY,
  MSIB.MAX_MINMAX_QUANTITY MAX_lot_size_MAX_MINMAX_QTY,
  MSIB.PLANNING_MAKE_BUY_CODE,
  ML3.MEANING "MAKE/BUY",
  MSIB.WIP_SUPPLY_TYPE,
  ML5.MEANING "WIP SUPPLY TYPE",
  --DECODE(MSIB.CONSIGNED_FLAG,1,'Y',2,NULL) CONSIGNED,
  MIL.SUBINVENTORY_CODE WIP_SUPPLY_SUBINVENTORY,
  (MIL.SEGMENT1
  ||'.'
  ||MIL.SEGMENT2
  ||'.'
  ||MIL.SEGMENT3) WIP_SUPPLY_LOCATOR,
  MSIB.FULL_LEAD_TIME PROCESSING_LEAD_TIME,
  MSS.SAFETY_STOCK_QUANTITY,
  MISD.SUBINVENTORY_CODE DEFAULT_RECEIVING_SUBINVENTORY,
  --      MSIB.ITEM_TYPE,
  mita.report_user_value bulk_material_user_item_type,
  MSIB.CUMULATIVE_TOTAL_LEAD_TIME,
  MSIB.MINIMUM_ORDER_QUANTITY,
  MSIB.MAXIMUM_ORDER_QUANTITY,
  ML4.MEANING INVENTORY_PLANNING_METHOD,
  MSIB.ROUNDING_CONTROL_TYPE,
  MIL.SUBINVENTORY_CODE SUPPLY_SUBINVENTORY,
  (MIL.SEGMENT1
  ||'.'
  ||MIL.SEGMENT2
  ||'.'
  ||MIL.SEGMENT3) SUPPLY_LOCATOR,
  (SELECT abc_class_name
  FROM mtl_abc_classes mac,
    mtl_abc_assignments maa,
    MTL_ABC_ASSIGNMENT_GROUPS MAAG
  WHERE 1                     =1
  AND maa.inventory_item_id   = msib.inventory_item_id
  AND maa.assignment_group_id = maag.assignment_group_id
  AND maa.abc_class_id        = mac.abc_class_id
  AND MAAG.ORGANIZATION_ID    = MSIB.ORGANIZATION_ID
  AND maa.creation_date      >=
    (SELECT MAX(maa.creation_date)
    FROM mtl_abc_assignments maa,
      mtl_abc_assignment_groups maag
    WHERE 1                     =1
    AND maa.inventory_item_id   = msib.inventory_item_id
    AND MAA.ASSIGNMENT_GROUP_ID = MAAG.ASSIGNMENT_GROUP_ID
    AND maag.organization_id    = msib.organization_id
    )
  ) PHYS_INV_INDICATOR,
  MSIB.SHELF_LIFE_DAYS,
  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,
 
  (SELECT SUM(MOQ.PRIMARY_TRANSACTION_QUANTITY)
  FROM MTL_ONHAND_QUANTITIES_DETAIL MOQ
  WHERE MOQ.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
  AND MOQ.ORGANIZATION_ID     = MSIB.ORGANIZATION_ID
  ) QOH,
  (SELECT SUM(NVL(WDJ.START_QUANTITY,0)-NVL(WDJ.QUANTITY_COMPLETED,0))
  FROM WIP_DISCRETE_JOBS WDJ
  WHERE WDJ.STATUS_TYPE  IN (3,6)
  AND WDJ.PRIMARY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
  AND WDJ.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
  ) OPEN_WIP_QTY,
  (SELECT SUM(PLA1.QUANTITY)
  FROM PO_HEADERS_ALL PHA,
    PO_LINES_ALL PLA1
  WHERE PLA1.PO_LINE_ID NOT IN
    (SELECT PLA.PO_LINE_ID
    FROM PO_LINES_ALL PLA
    WHERE PLA.ITEM_ID = msib.INVENTORY_ITEM_ID
    AND (PLA.CLOSED_CODE LIKE '%CLOSED'
    OR PLA.CLOSED_FLAG = 'Y'
    OR PLA.CANCEL_FLAG = 'Y')
    )
  AND PLA1.ITEM_ID     = MSIB.INVENTORY_ITEM_ID
  AND PHA.PO_HEADER_ID = PLA1.PO_HEADER_ID
  ) OPEN_PO_QTY,
  (SELECT MAX(MMT.TRANSACTION_DATE)
  FROM MTL_MATERIAL_TRANSACTIONS MMT
  WHERE MMT.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
  AND MMT.ORGANIZATION_ID     = MSIB.ORGANIZATION_ID
  AND TRUNC(MMT.TRANSACTION_DATE) BETWEEN '01-JAN-11' AND '31-DEC-12'
  ) USAGE24,
  (SELECT MAX(MMT.TRANSACTION_DATE)
  FROM MTL_MATERIAL_TRANSACTIONS MMT
  WHERE MMT.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
  AND MMT.ORGANIZATION_ID     = MSIB.ORGANIZATION_ID
  AND TRUNC(mmt.TRANSACTION_DATE) BETWEEN '01-JAN-09' AND '31-DEC-12'
  ) usage48
FROM MTL_SYSTEM_ITEMS_B MSIB,
  MTL_ITEM_TEMPL_ATTRIBUTES MITA,
  MTL_PARAMETERS MP,
  --     MTL_PARAMETERS MP1,
  --     MTL_PARAMETERS MP2,
  MFG_LOOKUPS ML,
  MFG_LOOKUPS ML1,
  MFG_LOOKUPS ML2,
  MFG_LOOKUPS ML3,
  PER_ALL_PEOPLE_F PAPF,
  MTL_PLANNERS MPl,
  MTL_ITEM_LOCATIONS MIL,
  MTL_SAFETY_STOCKS MSS,
  MTL_ITEM_SUB_DEFAULTS MISD,
  MFG_LOOKUPS ML4,
  MFG_LOOKUPS ML5,
  PO_APPROVED_SUPPLIER_LIST PASL,
  PO_VENDORS PV,
  PO_VENDOR_SITES_ALL PVS,
  PO_ASL_ATTRIBUTES PAA
WHERE 1                 =1
AND MITA.ATTRIBUTE_VALUE=MSIB.ITEM_TYPE
AND MITA.ATTRIBUTE_NAME LIKE 'MTL_SYSTEM_ITEMS.ITEM_TYPE'
AND MSIB.ORGANIZATION_ID             =MP.ORGANIZATION_ID
AND MSIB.ORGANIZATION_ID             = 178      --- HOP 179 for MTV and 737 for BIO
AND MSIB.INVENTORY_ITEM_STATUS_CODE <> 'Inactive'--56596 records active hop
AND MSIB.LOT_CONTROL_CODE            =ML.LOOKUP_CODE
AND ML.LOOKUP_TYPE                   = 'MTL_LOT_CONTROL'
AND MSIB.SHELF_LIFE_CODE             =ML1.LOOKUP_CODE
AND ML1.LOOKUP_TYPE                  = 'MTL_SHELF_LIFE'
AND MSIB.SERIAL_NUMBER_CONTROL_CODE  =ML2.LOOKUP_CODE
AND ML2.LOOKUP_TYPE                  = 'MTL_SERIAL_NUMBER'
AND MSIB.BUYER_ID                    =PAPF.PERSON_ID(+)
AND MSIB.PLANNING_MAKE_BUY_CODE      =ML3.LOOKUP_CODE
AND ml3.lookup_type                  = 'MTL_PLANNING_MAKE_BUY'
AND MIL.INVENTORY_LOCATION_ID(+)     =MSIB.WIP_SUPPLY_LOCATOR_ID
AND MSS.INVENTORY_ITEM_ID(+)         =MSIB.INVENTORY_ITEM_ID
AND MISD.INVENTORY_ITEM_ID(+)        =MSIB.INVENTORY_ITEM_ID
AND MISD.ORGANIZATION_ID(+)          =MSIB.ORGANIZATION_ID
AND MSIB.INVENTORY_PLANNING_CODE     =ML4.LOOKUP_CODE
AND ML4.LOOKUP_TYPE                  ='MTL_MATERIAL_PLANNING'
AND MPL.PLANNER_CODE(+)              =MSIB.PLANNER_CODE
AND MSIB.WIP_SUPPLY_TYPE             =ML5.LOOKUP_CODE
AND ML5.LOOKUP_TYPE                  ='WIP_SUPPLY'
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 msib.inventory_item_id           = 118363--140751;--12331
--and msib.segment1 = '107166'
ORDER BY USAGE24,USAGE48;

---> Sponsored by Nisha Ellora
Best Blogger TemplatesBest Blogger Tips
Best Blogger Gadgets
Posted by Raju Chinthapatla at 7:58:00 PM
Labels: , ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)
If you are facing any issues while copying the Code/Script or any issues with Posts, Please send a mail to OracleApp88@Yahoo.com or message me at @apps88 or +91 905 957 4321 in telegram.
Best Blogger TipsGet Flower Effect