MAD1

SELECT MSIB.SEGMENT1    item,
       MSIB.DESCRIPTION ITEM_DESC,
       GCC.segment1,
       GCC.segment2,
       GCC.segment3,
       GCC.segment4,
       GCC.segment5,
       GCC.segment6,
       GCC.segment7,
 
       (SELECT FVT.DESCRIPTION
          FROM APPS.FND_FLEX_VALUES_TL  FVT,
               APPS.FND_FLEX_VALUE_SETS FVS,
               APPS.FND_FLEX_VALUES     FV
         WHERE 1 = 1
           AND FVS.FLEX_VALUE_SET_ID = FV.FLEX_VALUE_SET_ID
           AND FV.FLEX_VALUE_ID = FVT.FLEX_VALUE_ID
           AND FVT.LANGUAGE = 'US'
           AND FVS.FLEX_VALUE_SET_NAME = 'Inventory'
           AND MC.SEGMENT2 = FV.FLEX_VALUE) CAT_DESC,
       --SUBSTR(FULL_NAME,1,22) NAME,
       OAP.PERIOD_NAME "ACCOUNTING_PERIOD",
       SUBSTR(MP.ORGANIZATION_CODE, 1, 3) ORG_CODE,
       MMT.SOURCE_CODE,
       TT.TRANSACTION_TYPE_NAME TRAN_TYPE,
       TO_CHAR(MMT.TRANSACTION_DATE, 'DD-MON-YY') TXN_DATE,
       MMT.TRANSACTION_UOM UOM,
       ACC.PRIMARY_QUANTITY PRIM_QTY,
       ROUND(ACC.RATE_OR_AMOUNT, 5) UNIT_STD_COST,
       ROUND((ACC.RATE_OR_AMOUNT * ACC.PRIMARY_QUANTITY), 2) "TRANSACTION_VALUE",
       MMT.SOURCE_CODE,
       MMT.CURRENCY_CODE CURR_CODE,
       MMT.CURRENCY_CONVERSION_RATE RATE,
       MMT.COSTED_FLAG COST_FLAG,
       --MMT.SUBINVENTORY_CODE SUBINV,
       MMT.TRANSACTION_REFERENCE COMMENTS,
       MTR.REASON_NAME REASON_NAME,
       DECODE(ACC.ACCOUNTING_LINE_TYPE,
              1,
              'INV VALUATION AC',
              2,
              'OFFSET ACCOUNT',
              5,
              'RECEIVING INSPECTION',
              6,
              'PPV') ACCOUNT_TYPE
  FROM APPS.MTL_MATERIAL_TRANSACTIONS MMT,
       APPS.MTL_TRANSACTION_REASONS   MTR,
       APPS.MTL_SYSTEM_ITEMS_B        MSIB,
       INV.MTL_ITEM_CATEGORIES        ICAT,
       INV.MTL_CATEGORIES_B           MC,
       INV.MTL_CATEGORY_SETS_B        MCS,
       INV.MTL_CATEGORY_SETS_TL       MCS_TL,
       INV.MTL_DEFAULT_CATEGORY_SETS  MDCS,
       INV.MTL_PARAMETERS             MP,
       APPS.ORG_ACCT_PERIODS          OAP,
       APPS.MTL_TRANSACTION_TYPES     TT,
       APPS.MTL_TRANSACTION_ACCOUNTS  ACC,
       APPS.GL_CODE_COMBINATIONS      GCC,
       APPS.FND_USER                  U,
       APPS.PER_PEOPLE_F              EMP
 WHERE 1=1
   AND MP.ORGANIZATION_ID = MMT.ORGANIZATION_ID
   AND TRUNC(mmt.transaction_date) BETWEEN
       NVL(TRUNC(TO_DATE('01/06/08', 'DD-MM-YY')),
           TRUNC(mmt.transaction_date)) AND
       NVl(TRUNC(TO_DATE('30/11/08', 'DD-MM-YY')),
           TRUNC(mmt.transaction_date))
   AND ACC.REFERENCE_ACCOUNT = GCC.CODE_COMBINATION_ID(+)
   AND MMT.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
   AND MMT.REASON_ID = MTR.REASON_ID(+)
   AND MMT.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
   AND MP.ORGANIZATION_CODE IN ('M1')
   AND MMT.TRANSACTION_TYPE_ID = TT.TRANSACTION_TYPE_ID
   AND MMT.TRANSACTION_ID = ACC.TRANSACTION_ID(+)
   AND MMT.CREATED_BY = U.USER_ID
   AND MMT.ACCT_PERIOD_ID = OAP.ACCT_PERIOD_ID
   AND U.EMPLOYEE_ID = EMP.PERSON_ID(+)
   AND MCS.CATEGORY_SET_ID = MCS_TL.CATEGORY_SET_ID
   AND MCS_TL.LANGUAGE = 'US'
   AND ICAT.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
   AND ICAT.ORGANIZATION_ID = MSIB.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 MDCS.FUNCTIONAL_AREA_ID = 1
      --AND MC.SEGMENT2 ='GC'
   AND MCS.STRUCTURE_ID = 101
 ORDER BY 3, 1, 16, 2