GW Items
Description
SELECT DISTINCT MP.ORGANIZATION_CODE "Warehouse Code", trunc(msi.last_update_date) "Last update", (SELECT OTL.NAME INVENTORY_ORG_NAME FROM apps.HR_ALL_ORGANIZATION_UNITS O, apps.HR_ALL_ORGANIZATION_UNITS_TL OTL, apps.HR_ORGANIZATION_INFORMATION O3, apps.HR_ORGANIZATION_INFORMATION O4, apps.MTL_PARAMETERS MP1 WHERE O.ORGANIZATION_ID = OTL.ORGANIZATION_ID AND O.ORGANIZATION_ID = O3.ORGANIZATION_ID AND O.ORGANIZATION_ID = O4.ORGANIZATION_ID AND O.ORGANIZATION_ID = MP.ORGANIZATION_ID AND MP.ORGANIZATION_ID = MP1.ORGANIZATION_ID AND O3.ORG_INFORMATION_CONTEXT = 'Accounting Information' AND O4.ORG_INFORMATION_CONTEXT = 'CLASS' AND O4.ORG_INFORMATION1 = 'INV' AND O4.ORG_INFORMATION2 = 'Y') "Warehouse description", MSI.SEGMENT1 "Product", MSI.DESCRIPTION, MSI.INVENTORY_ITEM_STATUS_CODE "Item Status", ML.MEANING "ITEM TYPE" "Item Type", MSI.STOCK_ENABLED_FLAG "Stock Enabled", MSI.Inventory_item_flag "Inventory Item", MSI.MTL_TRANSACTIONS_ENABLED_FLAG "Transactable", decode(msi.reservable_type, '1', 'Y', '2', 'N') "Allow Reservation", MSI.CYCLE_COUNT_ENABLED_FLAG "Cycle count", msi.bom_enabled_flag "BOM Enabled", msi.inventory_asset_flag "Inv Asset", decode(MSI.BOM_ITEM_TYPE, '1', 'Model', '2', 'Option class', '3', 'Planning', '4', 'Standard', '5', 'Product Family') "BOM Type", MSI.PRIMARY_UNIT_OF_MEASURE "Primary UOM", decode(to_char(msi.lot_control_code), '2', 'Y', '1', 'N') LOT_CONTROL, decode(to_char(msi.serial_number_control_code), '1', 'None', '2', 'Predefined', '5', 'Dynamic at INV receipt', '6', 'Dynamic at SO issue') "Serial Control", round(icst.item_cost) "Cost", MSI.attribute1 "Revision", (select user_name from fnd_user fu where fu.user_id = msi.last_updated_by) who_updated_last, (select user_name from fnd_user fu2 where fu2.user_id = msi.created_by) who_created, (SELECT DISTINCT MC.SEGMENT1 FROM INV.MTL_CATEGORIES_B MC, INV.MTL_ITEM_CATEGORIES ICAT, INV.MTL_CATEGORY_SETS_B MCS, INV.MTL_CATEGORY_SETS_TL MCS_TL, INV.MTL_DEFAULT_CATEGORY_SETS MDCS WHERE 1 = 1 AND ICAT.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID AND ICAT.ORGANIZATION_ID = MSI.ORGANIZATION_ID AND ICAT.CATEGORY_ID = MC.CATEGORY_ID AND ICAT.CATEGORY_SET_ID = MCS.CATEGORY_SET_ID AND ICAT.CATEGORY_SET_ID = MDCS.CATEGORY_SET_ID AND MCS.CATEGORY_SET_ID = MCS_TL.CATEGORY_SET_ID AND MCS_TL.LANGUAGE = 'US' AND MDCS.FUNCTIONAL_AREA_ID = 2) PURCHASE_CAT, --OCADO ITEMS CAT (SELECT DISTINCT MC.SEGMENT1 FROM INV.MTL_CATEGORIES_B MC, INV.MTL_ITEM_CATEGORIES ICAT, INV.MTL_CATEGORY_SETS_B MCS, INV.MTL_CATEGORY_SETS_TL MCS_TL, INV.MTL_DEFAULT_CATEGORY_SETS MDCS WHERE 1 = 1 AND ICAT.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID AND ICAT.ORGANIZATION_ID = MSI.ORGANIZATION_ID AND ICAT.CATEGORY_ID = MC.CATEGORY_ID AND ICAT.CATEGORY_SET_ID = MCS.CATEGORY_SET_ID AND ICAT.CATEGORY_SET_ID = MDCS.CATEGORY_SET_ID AND MCS.CATEGORY_SET_ID = MCS_TL.CATEGORY_SET_ID AND MCS_TL.LANGUAGE = 'US' AND MDCS.FUNCTIONAL_AREA_ID = 1) OCADO_ITEM_CAT, --OCADO ITEMS CAT (SELECT DISTINCT MC2.SEGMENT1 FROM INV.MTL_CATEGORIES_B MC2, INV.MTL_ITEM_CATEGORIES ICAT2, INV.MTL_CATEGORY_SETS_B MCS2, INV.MTL_CATEGORY_SETS_TL MCS_TL2 WHERE 1 = 1 AND ICAT2.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID AND ICAT2.ORGANIZATION_ID = MSI.ORGANIZATION_ID AND ICAT2.CATEGORY_ID = MC2.CATEGORY_ID AND ICAT2.CATEGORY_SET_ID = MCS2.CATEGORY_SET_ID AND MCS2.CATEGORY_SET_ID = MCS_TL2.CATEGORY_SET_ID AND MCS_TL2.LANGUAGE = 'US' AND MCS2.CATEGORY_SET_ID = 1100000061) Sourcing_Cat, --OCADO ITEMS CAT MSI.MTL_TRANSACTIONS_ENABLED_FLAG "INVENTORY TRANSACT", MSI.COSTING_ENABLED_FLAG "COSTING ENAB", MSI.PURCHASING_ITEM_FLAG "PURCHASED", GLCC2.CONCATENATED_SEGMENTS "PO EXPENCE AC", DECODE(MSI.INVENTORY_PLANNING_CODE, 6, 'NOT PLANNED', 2, 'MIN-MAX', 1, 'RE-ORDER') "INVENTORY PLANNING METHOD", DECODE(MSI.MRP_PLANNING_CODE, 3, 'MRP PLANNED', 4, 'MPS PLANNED', 6, 'NOT PLANNED', 7, 'MRP/MPP PLANNED', 8, 'MPS/MPP PLANNED', 9, 'MPP PLANNED', NULL) "PLANNING METHOD", MSI.POSTPROCESSING_LEAD_TIME "POST PROCESS LT", MSI.PREPROCESSING_LEAD_TIME "PRE PROCESS LT", MSI.FULL_LEAD_TIME "PROCESSING LT", DECODE(MSI.PLANNING_TIME_FENCE_CODE, 4, 'USER-DEFINED TIME FENCE', 3, 'TOTAL LEAD TIME', 1, 'CUMMULATIVE TOTAL LEAD TIME', 2, 'CUMMULATIVE MFG. LEAD TIME', NULL) "PLANNING TIME FENCE", MSI.PLANNER_CODE PLANNER, MSI.MINIMUM_ORDER_QUANTITY "MIN ORD QTY", MSI.FIXED_ORDER_QUANTITY "FIX ORD QTY", MSI.FIXED_LOT_MULTIPLIER "FIX LOT MULTIPLIER", MSI.FIXED_DAYS_SUPPLY "DAYS OF SUPPLY", msi.weight_uom_code, msi.unit_weight, msi.volume_uom_code "Vol UOM", msi.unit_volume "Unit Vol", msi.dimension_uom_code "DIM UOM", msi.unit_length "Unit Length", msi.unit_width "Unit Width", msi.unit_height "Unit Height", msi.indivisible_flag "Allow_Fractions", DECODE(MSI.PLANNING_MAKE_BUY_CODE, 1, 'MAKE', 2, 'BUY') "MAKE OR BUY", MSI.SHIPPABLE_ITEM_FLAG "SHIPPABLE ITEM", MSI.CUSTOMER_ORDER_FLAG "CUST ORDER", MSI.INTERNAL_ORDER_FLAG "INT ORD", MSI.CUSTOMER_ORDER_ENABLED_FLAG "CUST ORD ENAB", MSI.INTERNAL_ORDER_ENABLED_FLAG "ISO ENAB", MSI.BUILD_IN_WIP_FLAG "BUILD IN WIP", DECODE(MSI.SOURCE_TYPE, 1, 'INVENTORY', 2, 'PURCHASING', 3, 'SUB_INVENTORY') "ISO SOURCE TYPE", (SELECT POV.FULL_NAME FROM PO_BUYERS_V POV WHERE MSI.BUYER_ID = POV.EMPLOYEE_ID) BUYER, MSI.PURCHASING_ENABLED_FLAG "PURCHASABLE", (SELECT MP_ISO.ORGANIZATION_CODE FROM MTL_PARAMETERS MP_ISO WHERE 1 = 1 AND MSI.SOURCE_ORGANIZATION_ID = MP_ISO.ORGANIZATION_ID) "ISO SOURCE_FROM", DECODE(MSI.BOM_ITEM_TYPE, 4, 'STD') BOM_TYPE, --MSI.ATP_COMPONENTS_FLAG ATP_COMP, --MSI.ATP_FLAG ATP, DECODE(MSI.WIP_SUPPLY_TYPE, 1, 'PUSH', 2, 'ASSEMBLY_PULL', 3, 'OPERATION_PULL', 4, 'BULK', 5, 'SUPPLIER', 6, 'PHANTOM') "WIP SUPPLY TYPE" FROM APPS.MTL_SYSTEM_ITEMS_B MSI, APPS.MTL_PARAMETERS MP, APPS.FND_LOOKUP_VALUES ML, APPS.GL_CODE_COMBINATIONS_KFV GLCC2, APPS.CST_ITEM_COSTS ICST -- APPS.FND_USER FU WHERE 1 = 1 and msi.organization_id = mp.organization_id AND MSI.ITEM_TYPE = ML.LOOKUP_CODE(+) AND ML.LOOKUP_TYPE(+) = 'ITEM_TYPE' AND ML.LANGUAGE = 'US' AND MSI.EXPENSE_ACCOUNT = GLCC2.CODE_COMBINATION_ID AND ICST.ORGANIZATION_ID = MSI.ORGANIZATION_ID AND ICST.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID AND ICST.COST_TYPE_ID = 1 ORDER BY 1 |