MAd report 1
Description
SELECT MSIB.SEGMENT1 item, MSIB.DESCRIPTION ITEM_DESC, GCC.segment1, GCC.segment2, GCC.segment3, GCC.segment4, GCC.segment5, GCC.segment6, --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_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/01/21', 'DD-MM-YY')), TRUNC(mmt.transaction_date)) AND NVl(TRUNC(TO_DATE('30/11/21', '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 ='AAC' 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(+) ORDER BY 3, 1, 16, 2 |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Organization Code |
|
LOV |