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
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