XX Item Validation Report

SELECT   organization_code,
         msib.segment1 item_code,
         msib.description item_description,
         cas_number,
         primary_uom_code,
         secondary_uom_code,
         DECODE (tracking_quantity_ind,
                 'P', 'PRIMARY',
                 'PS', 'PRIMARY AND SECONDARY'
                ) tracking,
         DECODE (secondary_default_ind,
                 'F', 'FIXED',
                 'D', 'DEFAULT',
                 NULL, '',
                 'NO DEFAULT'
                ) defaulting,
         inventory_item_flag inventory_item,
         stock_enabled_flag stockable,
         mtl_transactions_enabled_flag transactable,
         DECODE (reservable_type, 1, 'YES', 'NO') reservable,
         DECODE (shelf_life_code,
                 1, 'NO CONTROL',
                 2, 'SHELF LIFE DAYS',
                 4, 'USER DEFINED'
                ) lot_expiration,
         DECODE (lot_control_code,
                 1, 'NO CONTROL',
                 'FULL CONTROL'
                ) lot_control,
         lot_status_enabled lot_status,
         costing_enabled_flag costing,
         inventory_asset_flag inventory_asset,
            gcc.segment1
         || '.'
         || gcc.segment2
         || '.'
         || gcc.segment3
         || '.'
         || gcc.segment4
         || '.'
         || gcc.segment5 cogs_account,
         purchasing_item_flag purchased,
          purchasing_enabled_flag purchasable,
         rfq_required_flag rfq,
          receipt_required_flag receipt_required,
         inspection_required_flag inspection_requried,
         DECODE (receiving_routing_id,
                 2, 'INSPECTION',
                 1, 'STANDARD',
                 3, 'DIRECT'
                ) receiving_routing,
         process_quality_enabled_flag process_quality,
         process_costing_enabled_flag process_costing,
         recipe_enabled_flag recipe,
         process_execution_enabled_flag process_execution
    FROM mtl_system_items_b msib,
         org_organization_definitions ood,
         gl_code_combinations gcc
   WHERE ood.organization_id = msib.organization_id
     AND msib.cost_of_sales_account = gcc.code_combination_id
     AND msib.segment1 BETWEEN NVL (:p_item_code_from, msib.segment1)
                           AND NVL (:p_item_code_to, msib.segment1)
     AND TRUNC (msib.creation_date) BETWEEN NVL (:p_creation_date_from,
                                                 TRUNC (msib.creation_date)
                                                )
                                        AND NVL (:p_creation_date_to,
                                                 TRUNC (msib.creation_date)
                                                )
ORDER BY 1, 2 ASC