This is an inventory form query for few fields which are important.

 

SELECT

  msib.segment1 "Item Name",

  msib.description "Short Description",

  msib.primary_unit_of_measure "Primay Unit of Measure",

  msib.inventory_item_status_code "Item Status",

  msit.long_description "Long Description",

  gcc1.concatenated_segments "Cost of Goods Sold Account",

  gcc2.concatenated_segments "Expense Account",

  DECODE (msib.customer_order_flag, 'Y', 'Yes', 'No') "Customer Order Flag",

  DECODE (msib.so_transactions_flag, 'Y', 'Yes', 'No') "OE Transactable",

  DECODE (msib.returnable_flag, 'Y', 'Yes', 'No') "Returnable",

  gcc2.concatenated_segments "Sales Account",

  DECODE (msib.comms_nl_trackable_flag, 'Y', 'Yes', 'No') "Track in Installed Base",      

  fl.meaning "Contract Item Type",

  msib.service_duration "Duration",

  fl1.meaning "Duration Period",

  oklt.name "Service Template",

  osht.name "Subscription Template",

  msib.service_starting_delay "Starting Delay (Days)",--FLEX FIELDS,

  msib.attribute1 "Item Group",

  DECODE (msib.attribute2, 'Y', 'Yes', 'No') "Range Pricing"

FROM

  mtl_system_items_b        msib,

  mtl_system_items_tl       msit,

  gl_code_combinations_kfv  gcc,

  gl_code_combinations_kfv  gcc1,

  gl_code_combinations_kfv  gcc2,

  fnd_lookups               fl,

  fnd_lookups               fl1,

  okc_k_lines_tl            oklt,

  oks_subscr_header_tl      osht

WHERE 1=1

  AND msib.segment1 IN

     (

       'RAM'

      ,'CM00065' --PURCHASE ITEMS                                     

      ,'FSPM1000'

      ,'Transportation Charge' --SERVICE ITEMS

      ,'SENTRY'

      ,'IS30355' -- SUBSCRIPTION ITEMS

      ,'FS-LPTR-U' -- USAGE ITEMS

      ,'WARRANTY1' -- WARRANTY ITEMS

    )

  AND msib.organization_id = 204

  AND msit.inventory_item_id = msib.inventory_item_id

  AND msit.organization_id = msib.organization_id

  AND gcc.code_combination_id(+) = msib.cost_of_sales_account

  AND gcc1.code_combination_id(+) = msib.expense_account

  AND gcc2.code_combination_id(+) = msib.sales_account

  AND fl.lookup_type(+) = 'OKB_CONTRACT_ITEM_TYPE'

  AND fl.lookup_code(+) = msib.contract_item_type_code

  AND fl1.lookup_type(+) = 'EGO_SRV_DURATION_PERIOD'

  AND fl1.lookup_code(+) = msib.service_duration_period_code

  AND oklt.id(+) = msib.coverage_schedule_id

  AND osht.id(+) = msib.coverage_schedule_id;