VIEW: APPS.CN_MTL_SYSTEM_ITEM_CATS_V

Source

View Text - Preformatted

SELECT mic.INVENTORY_ITEM_ID, mic.ORGANIZATION_ID, mic.CATEGORY_SET_ID, mic.CATEGORY_ID, msi.VENDOR_WARRANTY_FLAG, msi.SERVICEABLE_COMPONENT_FLAG, msi.SERVICEABLE_PRODUCT_FLAG, msi.BASE_WARRANTY_SERVICE_ID, msi.PAYMENT_TERMS_ID, msi.PREVENTIVE_MAINTENANCE_FLAG, msi.PRIMARY_SPECIALIST_ID, msi.SECONDARY_SPECIALIST_ID, msi.SERVICEABLE_ITEM_CLASS_ID, msi.TIME_BILLABLE_FLAG, msi.MATERIAL_BILLABLE_FLAG, msi.EXPENSE_BILLABLE_FLAG, msi.PRORATE_SERVICE_FLAG, msi.COVERAGE_SCHEDULE_ID, msi.SERVICE_DURATION_PERIOD_CODE, msi.SERVICE_DURATION, msi.WARRANTY_VENDOR_ID, msi.MAX_WARRANTY_AMOUNT, msi.RESPONSE_TIME_PERIOD_CODE, msi.RESPONSE_TIME_VALUE, msi.NEW_REVISION_CODE, msi.INVOICEABLE_ITEM_FLAG, msi.TAX_CODE, msi.INVOICE_ENABLED_FLAG, msi.MUST_USE_APPROVED_VENDOR_FLAG, msi.TAXABLE_FLAG, msi.QTY_RCV_EXCEPTION_CODE, msi.ALLOW_ITEM_DESC_UPDATE_FLAG, msi.INSPECTION_REQUIRED_FLAG, msi.RECEIPT_REQUIRED_FLAG, msi.MARKET_PRICE, msi.HAZARD_CLASS_ID, msi.RFQ_REQUIRED_FLAG, msi.QTY_RCV_TOLERANCE, msi.LIST_PRICE_PER_UNIT, msi.UN_NUMBER_ID, msi.PRICE_TOLERANCE_PERCENT, msi.ASSET_CATEGORY_ID, msi.ROUNDING_FACTOR, msi.UNIT_OF_ISSUE, msi.ENFORCE_SHIP_TO_LOCATION_CODE, msi.ALLOW_SUBSTITUTE_RECEIPTS_FLAG, msi.ALLOW_UNORDERED_RECEIPTS_FLAG, msi.ALLOW_EXPRESS_DELIVERY_FLAG, msi.DAYS_EARLY_RECEIPT_ALLOWED, msi.DAYS_LATE_RECEIPT_ALLOWED, msi.RECEIPT_DAYS_EXCEPTION_CODE, msi.RECEIVING_ROUTING_ID, msi.ATTRIBUTE1, msi.ATTRIBUTE2, msi.ATTRIBUTE3, msi.ATTRIBUTE4, msi.ATTRIBUTE5, msi.ATTRIBUTE6, msi.ATTRIBUTE7, msi.ATTRIBUTE8, msi.ATTRIBUTE9, msi.ATTRIBUTE10, msi.ATTRIBUTE11, msi.ATTRIBUTE12, msi.ATTRIBUTE13, msi.ATTRIBUTE14, msi.ATTRIBUTE15, msi.PURCHASING_ITEM_FLAG, msi.SHIPPABLE_ITEM_FLAG, msi.CUSTOMER_ORDER_FLAG, msi.INTERNAL_ORDER_FLAG, msi.SERVICE_ITEM_FLAG, msi.INVENTORY_ITEM_FLAG, msi.ENG_ITEM_FLAG, msi.INVENTORY_ASSET_FLAG, msi.PURCHASING_ENABLED_FLAG, msi.CUSTOMER_ORDER_ENABLED_FLAG, msi.INTERNAL_ORDER_ENABLED_FLAG, msi.SO_TRANSACTIONS_FLAG, msi.MTL_TRANSACTIONS_ENABLED_FLAG, msi.STOCK_ENABLED_FLAG, msi.BOM_ENABLED_FLAG, msi.BUILD_IN_WIP_FLAG, msi.REVISION_QTY_CONTROL_CODE, msi.ITEM_CATALOG_GROUP_ID, msi.CATALOG_STATUS_FLAG, msi.RETURNABLE_FLAG, msi.DEFAULT_SHIPPING_ORG, msi.COLLATERAL_FLAG, msi.SALES_ACCOUNT, msi.DEFAULT_INCLUDE_IN_ROLLUP_FLAG, msi.INVENTORY_ITEM_STATUS_CODE, msi.INVENTORY_PLANNING_CODE, msi.PLANNER_CODE, msi.PLANNING_MAKE_BUY_CODE, msi.FIXED_LOT_MULTIPLIER, msi.ROUNDING_CONTROL_TYPE, msi.CARRYING_COST, msi.POSTPROCESSING_LEAD_TIME, msi.PREPROCESSING_LEAD_TIME, msi.FULL_LEAD_TIME, msi.ORDER_COST, msi.MRP_SAFETY_STOCK_PERCENT, msi.MRP_SAFETY_STOCK_CODE, msi.MIN_MINMAX_QUANTITY, msi.MAX_MINMAX_QUANTITY, msi.MINIMUM_ORDER_QUANTITY, msi.FIXED_ORDER_QUANTITY, msi.FIXED_DAYS_SUPPLY, msi.MAXIMUM_ORDER_QUANTITY, msi.ATP_RULE_ID, msi.PICKING_RULE_ID, msi.RESERVABLE_TYPE, msi.POSITIVE_MEASUREMENT_ERROR, msi.NEGATIVE_MEASUREMENT_ERROR, msi.ENGINEERING_ECN_CODE, msi.ENGINEERING_ITEM_ID, msi.ENGINEERING_DATE, msi.SERVICE_STARTING_DELAY, msi.OUTSIDE_OPERATION_FLAG, msi.OUTSIDE_OPERATION_UOM_TYPE, msi.SAFETY_STOCK_BUCKET_DAYS, msi.AUTO_REDUCE_MPS, msi.COSTING_ENABLED_FLAG, msi.AUTO_CREATED_CONFIG_FLAG, msi.CYCLE_COUNT_ENABLED_FLAG, msi.ITEM_TYPE, msi.MODEL_CONFIG_CLAUSE_NAME, msi.SHIP_MODEL_COMPLETE_FLAG, msi.MRP_PLANNING_CODE, msi.RETURN_INSPECTION_REQUIREMENT, msi.ATO_FORECAST_CONTROL, msi.RELEASE_TIME_FENCE_CODE, msi.RELEASE_TIME_FENCE_DAYS, msi.SUMMARY_FLAG, msi.ENABLED_FLAG, msi.START_DATE_ACTIVE, msi.END_DATE_ACTIVE, msi.DESCRIPTION, msi.BUYER_ID, msi.ACCOUNTING_RULE_ID, msi.INVOICING_RULE_ID, msi.SEGMENT1, msi.SEGMENT2, msi.SEGMENT3, msi.SEGMENT4, msi.SEGMENT5, msi.SEGMENT6, msi.SEGMENT7, msi.SEGMENT8, msi.SEGMENT9, msi.SEGMENT10, msi.SEGMENT11, msi.SEGMENT12, msi.SEGMENT13, msi.SEGMENT14, msi.SEGMENT15, msi.SEGMENT16, msi.SEGMENT17, msi.SEGMENT18, msi.SEGMENT19, msi.SEGMENT20, msi.ATTRIBUTE_CATEGORY, msi.RESTRICT_LOCATORS_CODE, msi.LOCATION_CONTROL_CODE, msi.SHRINKAGE_RATE, msi.ACCEPTABLE_EARLY_DAYS, msi.PLANNING_TIME_FENCE_CODE, msi.DEMAND_TIME_FENCE_CODE, msi.LEAD_TIME_LOT_SIZE, msi.STD_LOT_SIZE, msi.CUM_MANUFACTURING_LEAD_TIME, msi.OVERRUN_PERCENTAGE, msi.MRP_CALCULATE_ATP_FLAG, msi.ACCEPTABLE_RATE_INCREASE, msi.ACCEPTABLE_RATE_DECREASE, msi.CUMULATIVE_TOTAL_LEAD_TIME, msi.PLANNING_TIME_FENCE_DAYS, msi.DEMAND_TIME_FENCE_DAYS, msi.END_ASSEMBLY_PEGGING_FLAG, msi.REPETITIVE_PLANNING_FLAG, msi.PLANNING_EXCEPTION_SET, msi.BOM_ITEM_TYPE, msi.PICK_COMPONENTS_FLAG, msi.REPLENISH_TO_ORDER_FLAG, msi.BASE_ITEM_ID, msi.ATP_COMPONENTS_FLAG, msi.ATP_FLAG, msi.FIXED_LEAD_TIME, msi.VARIABLE_LEAD_TIME, msi.WIP_SUPPLY_LOCATOR_ID, msi.WIP_SUPPLY_TYPE, msi.WIP_SUPPLY_SUBINVENTORY, msi.PRIMARY_UOM_CODE, msi.PRIMARY_UNIT_OF_MEASURE, msi.ALLOWED_UNITS_LOOKUP_CODE, msi.COST_OF_SALES_ACCOUNT, msi.INVOICE_CLOSE_TOLERANCE, msi.RECEIVE_CLOSE_TOLERANCE, msi.AUTO_LOT_ALPHA_PREFIX, msi.START_AUTO_LOT_NUMBER, msi.LOT_CONTROL_CODE, msi.SHELF_LIFE_CODE, msi.SHELF_LIFE_DAYS, msi.SERIAL_NUMBER_CONTROL_CODE, msi.START_AUTO_SERIAL_NUMBER, msi.AUTO_SERIAL_ALPHA_PREFIX, msi.SOURCE_TYPE, msi.SOURCE_ORGANIZATION_ID, msi.SOURCE_SUBINVENTORY, msi.EXPENSE_ACCOUNT, msi.ENCUMBRANCE_ACCOUNT, msi.RESTRICT_SUBINVENTORIES_CODE, msi.UNIT_WEIGHT, msi.WEIGHT_UOM_CODE, msi.VOLUME_UOM_CODE, msi.UNIT_VOLUME FROM mtl_system_items msi, mtl_item_categories mic, mtl_default_category_sets mdcs WHERE NVL(msi.organization_id, NVL(oe_profile.value('OE_ORGANIZATION_ID'), -99)) = NVL(oe_profile.value('OE_ORGANIZATION_ID'), -99) AND NVL(mic.organization_id, NVL(oe_profile.value('OE_ORGANIZATION_ID'), -99)) = NVL(oe_profile.value('OE_ORGANIZATION_ID'), -99) AND msi.inventory_item_id = mic.inventory_item_id AND mic.category_set_id = mdcs.category_set_id AND mdcs.functional_area_id = 7

View Text - HTML Formatted

SELECT MIC.INVENTORY_ITEM_ID
, MIC.ORGANIZATION_ID
, MIC.CATEGORY_SET_ID
, MIC.CATEGORY_ID
, MSI.VENDOR_WARRANTY_FLAG
, MSI.SERVICEABLE_COMPONENT_FLAG
, MSI.SERVICEABLE_PRODUCT_FLAG
, MSI.BASE_WARRANTY_SERVICE_ID
, MSI.PAYMENT_TERMS_ID
, MSI.PREVENTIVE_MAINTENANCE_FLAG
, MSI.PRIMARY_SPECIALIST_ID
, MSI.SECONDARY_SPECIALIST_ID
, MSI.SERVICEABLE_ITEM_CLASS_ID
, MSI.TIME_BILLABLE_FLAG
, MSI.MATERIAL_BILLABLE_FLAG
, MSI.EXPENSE_BILLABLE_FLAG
, MSI.PRORATE_SERVICE_FLAG
, MSI.COVERAGE_SCHEDULE_ID
, MSI.SERVICE_DURATION_PERIOD_CODE
, MSI.SERVICE_DURATION
, MSI.WARRANTY_VENDOR_ID
, MSI.MAX_WARRANTY_AMOUNT
, MSI.RESPONSE_TIME_PERIOD_CODE
, MSI.RESPONSE_TIME_VALUE
, MSI.NEW_REVISION_CODE
, MSI.INVOICEABLE_ITEM_FLAG
, MSI.TAX_CODE
, MSI.INVOICE_ENABLED_FLAG
, MSI.MUST_USE_APPROVED_VENDOR_FLAG
, MSI.TAXABLE_FLAG
, MSI.QTY_RCV_EXCEPTION_CODE
, MSI.ALLOW_ITEM_DESC_UPDATE_FLAG
, MSI.INSPECTION_REQUIRED_FLAG
, MSI.RECEIPT_REQUIRED_FLAG
, MSI.MARKET_PRICE
, MSI.HAZARD_CLASS_ID
, MSI.RFQ_REQUIRED_FLAG
, MSI.QTY_RCV_TOLERANCE
, MSI.LIST_PRICE_PER_UNIT
, MSI.UN_NUMBER_ID
, MSI.PRICE_TOLERANCE_PERCENT
, MSI.ASSET_CATEGORY_ID
, MSI.ROUNDING_FACTOR
, MSI.UNIT_OF_ISSUE
, MSI.ENFORCE_SHIP_TO_LOCATION_CODE
, MSI.ALLOW_SUBSTITUTE_RECEIPTS_FLAG
, MSI.ALLOW_UNORDERED_RECEIPTS_FLAG
, MSI.ALLOW_EXPRESS_DELIVERY_FLAG
, MSI.DAYS_EARLY_RECEIPT_ALLOWED
, MSI.DAYS_LATE_RECEIPT_ALLOWED
, MSI.RECEIPT_DAYS_EXCEPTION_CODE
, MSI.RECEIVING_ROUTING_ID
, MSI.ATTRIBUTE1
, MSI.ATTRIBUTE2
, MSI.ATTRIBUTE3
, MSI.ATTRIBUTE4
, MSI.ATTRIBUTE5
, MSI.ATTRIBUTE6
, MSI.ATTRIBUTE7
, MSI.ATTRIBUTE8
, MSI.ATTRIBUTE9
, MSI.ATTRIBUTE10
, MSI.ATTRIBUTE11
, MSI.ATTRIBUTE12
, MSI.ATTRIBUTE13
, MSI.ATTRIBUTE14
, MSI.ATTRIBUTE15
, MSI.PURCHASING_ITEM_FLAG
, MSI.SHIPPABLE_ITEM_FLAG
, MSI.CUSTOMER_ORDER_FLAG
, MSI.INTERNAL_ORDER_FLAG
, MSI.SERVICE_ITEM_FLAG
, MSI.INVENTORY_ITEM_FLAG
, MSI.ENG_ITEM_FLAG
, MSI.INVENTORY_ASSET_FLAG
, MSI.PURCHASING_ENABLED_FLAG
, MSI.CUSTOMER_ORDER_ENABLED_FLAG
, MSI.INTERNAL_ORDER_ENABLED_FLAG
, MSI.SO_TRANSACTIONS_FLAG
, MSI.MTL_TRANSACTIONS_ENABLED_FLAG
, MSI.STOCK_ENABLED_FLAG
, MSI.BOM_ENABLED_FLAG
, MSI.BUILD_IN_WIP_FLAG
, MSI.REVISION_QTY_CONTROL_CODE
, MSI.ITEM_CATALOG_GROUP_ID
, MSI.CATALOG_STATUS_FLAG
, MSI.RETURNABLE_FLAG
, MSI.DEFAULT_SHIPPING_ORG
, MSI.COLLATERAL_FLAG
, MSI.SALES_ACCOUNT
, MSI.DEFAULT_INCLUDE_IN_ROLLUP_FLAG
, MSI.INVENTORY_ITEM_STATUS_CODE
, MSI.INVENTORY_PLANNING_CODE
, MSI.PLANNER_CODE
, MSI.PLANNING_MAKE_BUY_CODE
, MSI.FIXED_LOT_MULTIPLIER
, MSI.ROUNDING_CONTROL_TYPE
, MSI.CARRYING_COST
, MSI.POSTPROCESSING_LEAD_TIME
, MSI.PREPROCESSING_LEAD_TIME
, MSI.FULL_LEAD_TIME
, MSI.ORDER_COST
, MSI.MRP_SAFETY_STOCK_PERCENT
, MSI.MRP_SAFETY_STOCK_CODE
, MSI.MIN_MINMAX_QUANTITY
, MSI.MAX_MINMAX_QUANTITY
, MSI.MINIMUM_ORDER_QUANTITY
, MSI.FIXED_ORDER_QUANTITY
, MSI.FIXED_DAYS_SUPPLY
, MSI.MAXIMUM_ORDER_QUANTITY
, MSI.ATP_RULE_ID
, MSI.PICKING_RULE_ID
, MSI.RESERVABLE_TYPE
, MSI.POSITIVE_MEASUREMENT_ERROR
, MSI.NEGATIVE_MEASUREMENT_ERROR
, MSI.ENGINEERING_ECN_CODE
, MSI.ENGINEERING_ITEM_ID
, MSI.ENGINEERING_DATE
, MSI.SERVICE_STARTING_DELAY
, MSI.OUTSIDE_OPERATION_FLAG
, MSI.OUTSIDE_OPERATION_UOM_TYPE
, MSI.SAFETY_STOCK_BUCKET_DAYS
, MSI.AUTO_REDUCE_MPS
, MSI.COSTING_ENABLED_FLAG
, MSI.AUTO_CREATED_CONFIG_FLAG
, MSI.CYCLE_COUNT_ENABLED_FLAG
, MSI.ITEM_TYPE
, MSI.MODEL_CONFIG_CLAUSE_NAME
, MSI.SHIP_MODEL_COMPLETE_FLAG
, MSI.MRP_PLANNING_CODE
, MSI.RETURN_INSPECTION_REQUIREMENT
, MSI.ATO_FORECAST_CONTROL
, MSI.RELEASE_TIME_FENCE_CODE
, MSI.RELEASE_TIME_FENCE_DAYS
, MSI.SUMMARY_FLAG
, MSI.ENABLED_FLAG
, MSI.START_DATE_ACTIVE
, MSI.END_DATE_ACTIVE
, MSI.DESCRIPTION
, MSI.BUYER_ID
, MSI.ACCOUNTING_RULE_ID
, MSI.INVOICING_RULE_ID
, MSI.SEGMENT1
, MSI.SEGMENT2
, MSI.SEGMENT3
, MSI.SEGMENT4
, MSI.SEGMENT5
, MSI.SEGMENT6
, MSI.SEGMENT7
, MSI.SEGMENT8
, MSI.SEGMENT9
, MSI.SEGMENT10
, MSI.SEGMENT11
, MSI.SEGMENT12
, MSI.SEGMENT13
, MSI.SEGMENT14
, MSI.SEGMENT15
, MSI.SEGMENT16
, MSI.SEGMENT17
, MSI.SEGMENT18
, MSI.SEGMENT19
, MSI.SEGMENT20
, MSI.ATTRIBUTE_CATEGORY
, MSI.RESTRICT_LOCATORS_CODE
, MSI.LOCATION_CONTROL_CODE
, MSI.SHRINKAGE_RATE
, MSI.ACCEPTABLE_EARLY_DAYS
, MSI.PLANNING_TIME_FENCE_CODE
, MSI.DEMAND_TIME_FENCE_CODE
, MSI.LEAD_TIME_LOT_SIZE
, MSI.STD_LOT_SIZE
, MSI.CUM_MANUFACTURING_LEAD_TIME
, MSI.OVERRUN_PERCENTAGE
, MSI.MRP_CALCULATE_ATP_FLAG
, MSI.ACCEPTABLE_RATE_INCREASE
, MSI.ACCEPTABLE_RATE_DECREASE
, MSI.CUMULATIVE_TOTAL_LEAD_TIME
, MSI.PLANNING_TIME_FENCE_DAYS
, MSI.DEMAND_TIME_FENCE_DAYS
, MSI.END_ASSEMBLY_PEGGING_FLAG
, MSI.REPETITIVE_PLANNING_FLAG
, MSI.PLANNING_EXCEPTION_SET
, MSI.BOM_ITEM_TYPE
, MSI.PICK_COMPONENTS_FLAG
, MSI.REPLENISH_TO_ORDER_FLAG
, MSI.BASE_ITEM_ID
, MSI.ATP_COMPONENTS_FLAG
, MSI.ATP_FLAG
, MSI.FIXED_LEAD_TIME
, MSI.VARIABLE_LEAD_TIME
, MSI.WIP_SUPPLY_LOCATOR_ID
, MSI.WIP_SUPPLY_TYPE
, MSI.WIP_SUPPLY_SUBINVENTORY
, MSI.PRIMARY_UOM_CODE
, MSI.PRIMARY_UNIT_OF_MEASURE
, MSI.ALLOWED_UNITS_LOOKUP_CODE
, MSI.COST_OF_SALES_ACCOUNT
, MSI.INVOICE_CLOSE_TOLERANCE
, MSI.RECEIVE_CLOSE_TOLERANCE
, MSI.AUTO_LOT_ALPHA_PREFIX
, MSI.START_AUTO_LOT_NUMBER
, MSI.LOT_CONTROL_CODE
, MSI.SHELF_LIFE_CODE
, MSI.SHELF_LIFE_DAYS
, MSI.SERIAL_NUMBER_CONTROL_CODE
, MSI.START_AUTO_SERIAL_NUMBER
, MSI.AUTO_SERIAL_ALPHA_PREFIX
, MSI.SOURCE_TYPE
, MSI.SOURCE_ORGANIZATION_ID
, MSI.SOURCE_SUBINVENTORY
, MSI.EXPENSE_ACCOUNT
, MSI.ENCUMBRANCE_ACCOUNT
, MSI.RESTRICT_SUBINVENTORIES_CODE
, MSI.UNIT_WEIGHT
, MSI.WEIGHT_UOM_CODE
, MSI.VOLUME_UOM_CODE
, MSI.UNIT_VOLUME
FROM MTL_SYSTEM_ITEMS MSI
, MTL_ITEM_CATEGORIES MIC
, MTL_DEFAULT_CATEGORY_SETS MDCS
WHERE NVL(MSI.ORGANIZATION_ID
, NVL(OE_PROFILE.VALUE('OE_ORGANIZATION_ID')
, -99)) = NVL(OE_PROFILE.VALUE('OE_ORGANIZATION_ID')
, -99)
AND NVL(MIC.ORGANIZATION_ID
, NVL(OE_PROFILE.VALUE('OE_ORGANIZATION_ID')
, -99)) = NVL(OE_PROFILE.VALUE('OE_ORGANIZATION_ID')
, -99)
AND MSI.INVENTORY_ITEM_ID = MIC.INVENTORY_ITEM_ID
AND MIC.CATEGORY_SET_ID = MDCS.CATEGORY_SET_ID
AND MDCS.FUNCTIONAL_AREA_ID = 7