This query will help you to find all Miscellaneous Transaction in Oracle Apps.
/*Query to find All Miscellaneous Transaction in Oracle*/
SELECT (SELECT NAME
FROM GL_LEDGERS G, ORG_ORGANIZATION_DEFINITIONS OOD
WHERE G.LEDGER_ID = OOD.SET_OF_BOOKS_ID
AND OOD.ORGANIZATION_ID = MMT.ORGANIZATION_ID
AND G.LEDGER_CATEGORY_CODE = 'PRIMARY')
LEDGER,
(SELECT DISTINCT OPERATING_UNIT_NAME
FROM WBI_INV_ORG_DETAIL
WHERE INV_ORGANIZATION_ID = MMT.ORGANIZATION_ID)
OU_NAME,
MMT.ORGANIZATION_ID,
WXMD.ORGANIZATION_CODE,
WXMD.ORGANIZATION_NAME,
MMT.TRANSACTION_TYPE_ID,
(SELECT TRANSACTION_TYPE_NAME
FROM MTL_TRANSACTION_TYPES
WHERE TRANSACTION_TYPE_ID = MMT.TRANSACTION_TYPE_ID)
TRANSACTION_TYPE_NAME,
(SELECT MEANING
FROM FND_LOOKUP_VALUES_VL
WHERE LOOKUP_CODE = MMT.transaction_action_id
AND lookup_type = 'MTL_TRANSACTION_ACTION')
MEANING,
MMT.TRANSACTION_ID--,DECODE(MMT.TRANSACTION_TYPE_ID,21,(SELECT SHIPMENT_NUMBER FROM MTL_MATERIAL_TRANSACTIONS WHERE TRANSACTION_SOURCE_TYPE_ID=13 AND TRANSACTION_TYPE_ID=21
-- AND TRANSACTION_ID=MMT.TRANSACTION_ID),XX_INV_PKG.XX_INV_TRANS_NO(MMT.TRANSACTION_ID)) TRANSACTION_NO
,
MMT.TRANSACTION_DATE,
MMT.INVENTORY_ITEM_ID,
WXMD.ITEM_CODE,
WXMD.ITEM_DESC,
ABS (MMT.TRANSACTION_QUANTITY) TRANSACTION_QUANTITY,
MMT.SUBINVENTORY_CODE,
MMT.CREATED_BY TRANSACT_BY,
XX_INV_PKG.XXGET_ENAME (MMT.CREATED_BY) TRANSACT_BY_NAME,
MMT.TRANSACTION_REFERENCE REMARKS,
MMT.DISTRIBUTION_ACCOUNT_ID,
(SELECT SEGMENT1
|| '.'
|| SEGMENT2
|| '.'
|| SEGMENT3
|| '.'
|| SEGMENT4
|| '.'
|| SEGMENT5
|| '.'
|| SEGMENT6
|| '.'
|| SEGMENT7
|| '.'
|| SEGMENT8
|| '.'
|| SEGMENT9
|| '.'
|| SEGMENT10
FROM GL_CODE_COMBINATIONS
WHERE CODE_COMBINATION_ID = MMT.DISTRIBUTION_ACCOUNT_ID)
ACCOUNT_COMBINATION,
(XX_AP_PKG.GET_ACCOUNT_DESC_FROM_CCID (MMT.DISTRIBUTION_ACCOUNT_ID))
ACCOUNT_NAME,
XX_AP_PKG.GET_SUB_ACCOUNT_DESC_FROM_CCID (
TO_NUMBER (MMT.DISTRIBUTION_ACCOUNT_ID))
SUB_ACCOUNT
FROM MTL_MATERIAL_TRANSACTIONS MMT, WBI_XXKBGITEM_MT_D WXMD
WHERE ( MMT.transaction_source_type_id = 3
OR MMT.transaction_source_type_id = 6
OR MMT.transaction_source_type_id = 13
OR MMT.transaction_source_type_id >= 100)
AND (MMT.transaction_action_id = 1 OR MMT.transaction_action_id = 27)
AND MMT.transaction_type_id NOT IN (66, 67, 68, 95, 96)
AND WXMD.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID
AND WXMD.ORGANIZATION_ID = MMT.ORGANIZATION_ID
AND MMT.ORGANIZATION_ID = NVL (:P_ORG_ID, MMT.ORGANIZATION_ID)
AND MMT.TRANSACTION_TYPE_ID =
NVL (:P_TRANSACTION_TYPE_ID, MMT.TRANSACTION_TYPE_ID)
AND TO_DATE (MMT.TRANSACTION_DATE) BETWEEN NVL (
TO_DATE (:P_FROM_DATE),
TO_DATE (
MMT.TRANSACTION_DATE))
AND NVL (
TO_DATE (:P_TO_DATE),
TO_DATE (
MMT.TRANSACTION_DATE))
AND WXMD.ITEM_CODE = NVL (:P_ITEM_CODE, WXMD.ITEM_CODE)
AND MMT.SUBINVENTORY_CODE =
NVL (:P_SUBINVENTORY_CODE, MMT.SUBINVENTORY_CODE)
AND MMT.CREATED_BY = NVL (:P_CREATED_BY, MMT.CREATED_BY)
ORDER BY LEDGER, MMT.TRANSACTION_ACTION_ID, MMT.TRANSACTION_ID
