Monday, August 13, 2012

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
      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;

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect