MAD1
Description
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 |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Organization Code |
|
LOV |