Tuesday, August 8, 2017

Inventory Material Transaction Accounting query in Oracle Apps

SELECT msi.segment1,ACCOUNTING_LINE_TYPE, mtt.TRANSACTION_TYPE_NAME,(select MEANING from apps.FND_LOOKUP_VALUES
WHERE LOOKUP_CODE =A.ACCOUNTING_LINE_TYPE
AND VIEW_APPLICATION_ID = 700
AND LANGUAGE = 'US'
AND LOOKUP_TYPE = 'CST_ACCOUNTING_LINE_TYPE') ACCOUNT_LINE_TYPE, base_transaction_value, ABS(base_transaction_value) ORb, MMT.TRANSACTION_QUANTITY, MMT.ACTUAL_COST
  FROM apps.mtl_transaction_accounts a,
       apps.gl_code_combinations_kfv gcc,
       apps.mtl_system_items_b       msi,
       apps.mtl_material_transactions mmt,
       APPS.MTL_TRANSACTION_TYPES MTT
 WHERE 1 = 1
   AND mmt.transaction_date BETWEEN to_date('01-JUL-2017', 'DD-MON-YYYY') AND
       to_date('31-JUL-2017 23:59:59', 'DD-MON-YYYY HH24:MI:SS')
   AND A.ACCOUNTING_LINE_TYPE  = 36
   AND msi.organization_id = a.organization_id
   AND msi.inventory_item_id = a.inventory_item_id
   AND a.organization_id = 125
   AND gcc.code_combination_id = a.reference_account
   AND gcc.segment4 IN ('501010', '501020')
   AND gcc.segment1 IN ('421', '422')
--   and msi.segment1 = '000000000031022' --'000000000031019' --'000000000018045'
   and mmt.transaction_id= a.transaction_id
   and msi.organization_id = mmt.organization_id
    AND msi.inventory_item_id = mmt.inventory_item_id
    and MTT.TRANSACTION_TYPE_ID=MMT.TRANSACTION_TYPE_ID

No comments:

Post a Comment