eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.CST_INV_DISTRIBUTION_V

Source

View Text - Preformatted


SELECT mmt.organization_id , /* transaction_organization_id */ mta.transaction_id , MTA.ORGANIZATION_ID , /* organization_id */ MMT.INVENTORY_ITEM_ID , MSI.DESCRIPTION , MMT.REVISION , MMT.SUBINVENTORY_CODE , MMT.LOCATOR_ID , MMT.TRANSACTION_TYPE_ID , MTT.TRANSACTION_TYPE_NAME , MMT.TRANSACTION_SOURCE_TYPE_ID , MTST.TRANSACTION_SOURCE_TYPE_NAME , MMT.TRANSACTION_SOURCE_ID , MMT.TRANSACTION_SOURCE_NAME , MMT.TRANSACTION_DATE , MMT.TRANSACTION_QUANTITY , MMT.TRANSACTION_UOM , decode (MTA.ACCOUNTING_LINE_TYPE, 1, MTA.PRIMARY_QUANTITY, 14, MTA.PRIMARY_QUANTITY, 3, MTA.PRIMARY_QUANTITY, decode ( sign ( nvl ( MTA.BASE_TRANSACTION_VALUE,0) ) , -1, -1 * abs ( MTA.PRIMARY_QUANTITY ) , MTA.PRIMARY_QUANTITY ) ), MSI.PRIMARY_UOM_CODE , MMT.OPERATION_SEQ_NUM , MTA.CURRENCY_CODE , MTA.CURRENCY_CONVERSION_DATE , GLT.USER_CONVERSION_TYPE , MTA.CURRENCY_CONVERSION_RATE , BD.DEPARTMENT_CODE , MTR.REASON_NAME , MMT.TRANSACTION_REFERENCE , MTA.REFERENCE_ACCOUNT , MTA.ACCOUNTING_LINE_TYPE , LU1.MEANING , MTA.TRANSACTION_VALUE , MTA.BASE_TRANSACTION_VALUE , LU2.MEANING , MTA.COST_ELEMENT_ID , CA.ACTIVITY , MTA.RATE_OR_AMOUNT , MTA.GL_BATCH_ID , BR.RESOURCE_CODE , DECODE ( MMT.TRANSACTION_TYPE_ID, 24, NULL, 80, NULL, DECODE ( MTA.PRIMARY_QUANTITY, 0,NULL,NULL,NULL, decode(MTA.ACCOUNTING_LINE_TYPE, 1, MTA.BASE_TRANSACTION_VALUE/MTA.PRIMARY_QUANTITY, 14, MTA.BASE_TRANSACTION_VALUE/MTA.PRIMARY_QUANTITY, 3, MTA.BASE_TRANSACTION_VALUE/MTA.PRIMARY_QUANTITY, ABS ( MTA.BASE_TRANSACTION_VALUE / MTA.PRIMARY_QUANTITY ) ) ) ) , MTA.ROWID, MTA.LAST_UPDATE_DATE, MTA.LAST_UPDATED_BY, MTA.CREATION_DATE, MTA.CREATED_BY, MTA.LAST_UPDATE_LOGIN, MTA.REQUEST_ID , MTA.PROGRAM_APPLICATION_ID, MTA.PROGRAM_ID, MTA.PROGRAM_UPDATE_DATE, MSI.INVENTORY_ITEM_ID, MTA.ORGANIZATION_ID, MTA.TRANSACTION_DATE, MMT.PARENT_TRANSACTION_ID, MP2.ORGANIZATION_CODE, LU3.MEANING FROM MTL_TRANSACTION_ACCOUNTS MTA , MTL_MATERIAL_TRANSACTIONS MMT , MTL_PARAMETERS MP, MTL_PARAMETERS MP1, MTL_PARAMETERS MP2, CST_ACTIVITIES CA , MTL_SYSTEM_ITEMS_VL MSI , BOM_DEPARTMENTS BD , BOM_RESOURCES BR , MTL_TRANSACTION_REASONS MTR , MTL_TXN_SOURCE_TYPES MTST , MTL_TRANSACTION_TYPES MTT , MFG_LOOKUPS LU1 , MFG_LOOKUPS LU2 , MFG_LOOKUPS LU3 , GL_DAILY_CONVERSION_TYPES GLT WHERE mta.transaction_id = mmt.transaction_id AND ( mmt.transaction_action_id NOT IN (2,28,5,3) OR ( mmt.transaction_action_id IN (2,28,5) AND mmt.primary_quantity < 0 AND ( ( (mmt.transaction_type_id != 68 OR mta.accounting_line_type != 13) AND mmt.primary_quantity = mta.primary_quantity ) OR ( mp.primary_cost_method <> 1 AND mmt.transaction_type_id = 68 AND mta.accounting_line_type = 13 AND ( ( mmt.cost_group_id <> mmt.transfer_cost_group_id AND mmt.primary_quantity = decode(sign(mmt.primary_quantity),-1,mmt.primary_quantity,null)) or (mmt.cost_group_id = mmt.transfer_cost_group_id and mmt.primary_quantity = -1 * mta.primary_quantity) ) ) OR ( mp.primary_cost_method = 1 AND mmt.transaction_type_id = 68 AND mta.accounting_line_type = 13 AND ( (mmt.cost_group_id <> mmt.transfer_cost_group_id and mmt.project_id = mta.transaction_source_id ) or (mmt.cost_group_id = mmt.transfer_cost_group_id and mmt.primary_quantity =-1 * mta.primary_quantity) ) ) ) ) OR ( mmt.transaction_action_id = 3 AND mp.primary_cost_method = 1 AND mp1.primary_cost_method = 1 AND mmt.organization_id = mta.organization_id ) OR ( mmt.transaction_action_id = 3 AND ( mp.primary_cost_method <> 1 OR mp1.primary_cost_method <> 1 ) ) ) AND MSI.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID AND MSI.ORGANIZATION_ID = MMT.ORGANIZATION_ID AND MP.ORGANIZATION_ID = MMT.ORGANIZATION_ID AND MP1.ORGANIZATION_ID (+) = MMT.TRANSFER_ORGANIZATION_ID AND MP2.ORGANIZATION_ID = MTA.ORGANIZATION_ID AND MTT.TRANSACTION_TYPE_ID = MMT.TRANSACTION_TYPE_ID AND MTST.TRANSACTION_SOURCE_TYPE_ID = MMT.TRANSACTION_SOURCE_TYPE_ID AND BD.DEPARTMENT_ID ( + ) = MMT.DEPARTMENT_ID AND MTR.REASON_ID ( + ) = MMT.REASON_ID AND BR.RESOURCE_ID ( + ) = MTA.RESOURCE_ID AND LU1.LOOKUP_TYPE = 'CST_ACCOUNTING_LINE_TYPE' AND LU1.LOOKUP_CODE = MTA.ACCOUNTING_LINE_TYPE AND LU2.LOOKUP_CODE ( + ) = MTA.BASIS_TYPE AND LU2.LOOKUP_TYPE ( + ) = 'CST_BASIS_SHORT' AND LU3.LOOKUP_TYPE ( + ) = 'MTL_LOGICAL_TRANSACTION_CODE' AND LU3.LOOKUP_CODE ( + ) = MMT.LOGICAL_TRX_TYPE_CODE AND GLT.CONVERSION_TYPE ( + ) = MTA.CURRENCY_CONVERSION_TYPE AND CA.ACTIVITY_ID ( + ) = MTA.ACTIVITY_ID AND mta.inventory_item_id = mmt.inventory_item_id AND mta.transaction_date BETWEEN TRUNC(mmt.transaction_date) AND TRUNC(mmt.transaction_date)+0.99999 UNION ALL SELECT mmt.organization_id , /* transaction_organization_id */ mta.transaction_id , MTA.ORGANIZATION_ID , /* organization_id */ MMT.INVENTORY_ITEM_ID , MSI.DESCRIPTION , MMT.REVISION , MMT.SUBINVENTORY_CODE , MMT.LOCATOR_ID , MMT.TRANSACTION_TYPE_ID , MTT.TRANSACTION_TYPE_NAME , MMT.TRANSACTION_SOURCE_TYPE_ID , MTST.TRANSACTION_SOURCE_TYPE_NAME , MMT.TRANSACTION_SOURCE_ID , MMT.TRANSACTION_SOURCE_NAME , MMT.TRANSACTION_DATE , MMT.TRANSACTION_QUANTITY , MMT.TRANSACTION_UOM , decode (MTA.ACCOUNTING_LINE_TYPE, 1, MTA.PRIMARY_QUANTITY, 14, MTA.PRIMARY_QUANTITY, 3, MTA.PRIMARY_QUANTITY, decode ( sign ( nvl ( MTA.BASE_TRANSACTION_VALUE,0) ) , -1, -1 * abs ( MTA.PRIMARY_QUANTITY ) , MTA.PRIMARY_QUANTITY ) ), MSI.PRIMARY_UOM_CODE , MMT.OPERATION_SEQ_NUM , MTA.CURRENCY_CODE , MTA.CURRENCY_CONVERSION_DATE , GLT.USER_CONVERSION_TYPE , MTA.CURRENCY_CONVERSION_RATE , BD.DEPARTMENT_CODE , MTR.REASON_NAME , MMT.TRANSACTION_REFERENCE , MTA.REFERENCE_ACCOUNT , MTA.ACCOUNTING_LINE_TYPE , LU1.MEANING , MTA.TRANSACTION_VALUE , MTA.BASE_TRANSACTION_VALUE , LU2.MEANING , MTA.COST_ELEMENT_ID , CA.ACTIVITY , MTA.RATE_OR_AMOUNT , MTA.GL_BATCH_ID , BR.RESOURCE_CODE , DECODE ( MMT.TRANSACTION_TYPE_ID, 24, NULL, 80, NULL, DECODE ( MTA.PRIMARY_QUANTITY, 0,NULL,NULL,NULL, decode(MTA.ACCOUNTING_LINE_TYPE, 1, MTA.BASE_TRANSACTION_VALUE/MTA.PRIMARY_QUANTITY, 14, MTA.BASE_TRANSACTION_VALUE/MTA.PRIMARY_QUANTITY, 3, MTA.BASE_TRANSACTION_VALUE/MTA.PRIMARY_QUANTITY, ABS ( MTA.BASE_TRANSACTION_VALUE / MTA.PRIMARY_QUANTITY ) ) ) ) , MTA.ROWID, MTA.LAST_UPDATE_DATE, MTA.LAST_UPDATED_BY, MTA.CREATION_DATE, MTA.CREATED_BY, MTA.LAST_UPDATE_LOGIN, MTA.REQUEST_ID , MTA.PROGRAM_APPLICATION_ID, MTA.PROGRAM_ID, MTA.PROGRAM_UPDATE_DATE, MSI.INVENTORY_ITEM_ID, MTA.ORGANIZATION_ID, MTA.TRANSACTION_DATE, MMT.PARENT_TRANSACTION_ID, MP2.ORGANIZATION_CODE, LU3.MEANING FROM MTL_TRANSACTION_ACCOUNTS MTA , MTL_MATERIAL_TRANSACTIONS MMT , MTL_PARAMETERS MP, MTL_PARAMETERS MP1, MTL_PARAMETERS MP2, CST_ACTIVITIES CA , MTL_SYSTEM_ITEMS_VL MSI , BOM_DEPARTMENTS BD , BOM_RESOURCES BR , MTL_TRANSACTION_REASONS MTR , MTL_TXN_SOURCE_TYPES MTST , MTL_TRANSACTION_TYPES MTT , MFG_LOOKUPS LU1 , MFG_LOOKUPS LU2 , MFG_LOOKUPS LU3 , GL_DAILY_CONVERSION_TYPES GLT WHERE mta.transaction_id = mmt.transfer_transaction_id AND ( (mmt.transaction_action_id IN (2,28,5) AND mmt.primary_quantity > 0 AND ( ( (mmt.transaction_type_id != 68 OR mta.accounting_line_type != 13) AND mmt.primary_quantity = mta.primary_quantity ) OR ( mp.primary_cost_method <> 1 AND mmt.transaction_type_id = 68 AND mta.accounting_line_type = 13 AND ( ( mmt.cost_group_id <> mmt.transfer_cost_group_id and mmt.primary_quantity = decode(sign(mmt.primary_quantity),-1,mmt.primary_quantity,null)) or (mmt.cost_group_id = mmt.transfer_cost_group_id and mmt.primary_quantity = -1 * mta.primary_quantity)) ) OR ( mp.primary_cost_method = 1 AND mmt.transaction_type_id = 68 AND mta.accounting_line_type = 13 AND ( ( mmt.cost_group_id <> mmt.transfer_cost_group_id and mmt.project_id = mta.transaction_source_id) or (mmt.cost_group_id = mmt.transfer_cost_group_id and mmt.primary_quantity = -1 * mta.primary_quantity) ) ) ) ) OR ( mmt.transaction_action_id = 3 AND mp.primary_cost_method = 1 AND mp1.primary_cost_method = 1 AND mmt.organization_id = mta.organization_id ) ) AND MSI.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID AND MSI.ORGANIZATION_ID = MMT.ORGANIZATION_ID AND MP.ORGANIZATION_ID = MMT.ORGANIZATION_ID AND MP1.ORGANIZATION_ID (+) = MMT.TRANSFER_ORGANIZATION_ID AND MP2.ORGANIZATION_ID = MTA.ORGANIZATION_ID AND MTT.TRANSACTION_TYPE_ID = MMT.TRANSACTION_TYPE_ID AND MTST.TRANSACTION_SOURCE_TYPE_ID = MMT.TRANSACTION_SOURCE_TYPE_ID AND BD.DEPARTMENT_ID ( + ) = MMT.DEPARTMENT_ID AND MTR.REASON_ID ( + ) = MMT.REASON_ID AND BR.RESOURCE_ID ( + ) = MTA.RESOURCE_ID AND LU1.LOOKUP_TYPE = 'CST_ACCOUNTING_LINE_TYPE' AND LU1.LOOKUP_CODE = MTA.ACCOUNTING_LINE_TYPE AND LU2.LOOKUP_CODE ( + ) = MTA.BASIS_TYPE AND LU2.LOOKUP_TYPE ( + ) = 'CST_BASIS_SHORT' AND LU3.LOOKUP_TYPE ( + ) = 'MTL_LOGICAL_TRANSACTION_CODE' AND LU3.LOOKUP_CODE ( + ) = MMT.LOGICAL_TRX_TYPE_CODE AND GLT.CONVERSION_TYPE ( + ) = MTA.CURRENCY_CONVERSION_TYPE AND CA.ACTIVITY_ID ( + ) = MTA.ACTIVITY_ID AND mta.inventory_item_id = mmt.inventory_item_id AND mta.transaction_date BETWEEN TRUNC(mmt.transaction_date) AND TRUNC(mmt.transaction_date)+0.99999

View Text - HTML Formatted

SELECT MMT.ORGANIZATION_ID
, /* TRANSACTION_ORGANIZATION_ID */ MTA.TRANSACTION_ID
, MTA.ORGANIZATION_ID
, /* ORGANIZATION_ID */ MMT.INVENTORY_ITEM_ID
, MSI.DESCRIPTION
, MMT.REVISION
, MMT.SUBINVENTORY_CODE
, MMT.LOCATOR_ID
, MMT.TRANSACTION_TYPE_ID
, MTT.TRANSACTION_TYPE_NAME
, MMT.TRANSACTION_SOURCE_TYPE_ID
, MTST.TRANSACTION_SOURCE_TYPE_NAME
, MMT.TRANSACTION_SOURCE_ID
, MMT.TRANSACTION_SOURCE_NAME
, MMT.TRANSACTION_DATE
, MMT.TRANSACTION_QUANTITY
, MMT.TRANSACTION_UOM
, DECODE (MTA.ACCOUNTING_LINE_TYPE
, 1
, MTA.PRIMARY_QUANTITY
, 14
, MTA.PRIMARY_QUANTITY
, 3
, MTA.PRIMARY_QUANTITY
, DECODE ( SIGN ( NVL ( MTA.BASE_TRANSACTION_VALUE
, 0) )
, -1
, -1 * ABS ( MTA.PRIMARY_QUANTITY )
, MTA.PRIMARY_QUANTITY ) )
, MSI.PRIMARY_UOM_CODE
, MMT.OPERATION_SEQ_NUM
, MTA.CURRENCY_CODE
, MTA.CURRENCY_CONVERSION_DATE
, GLT.USER_CONVERSION_TYPE
, MTA.CURRENCY_CONVERSION_RATE
, BD.DEPARTMENT_CODE
, MTR.REASON_NAME
, MMT.TRANSACTION_REFERENCE
, MTA.REFERENCE_ACCOUNT
, MTA.ACCOUNTING_LINE_TYPE
, LU1.MEANING
, MTA.TRANSACTION_VALUE
, MTA.BASE_TRANSACTION_VALUE
, LU2.MEANING
, MTA.COST_ELEMENT_ID
, CA.ACTIVITY
, MTA.RATE_OR_AMOUNT
, MTA.GL_BATCH_ID
, BR.RESOURCE_CODE
, DECODE ( MMT.TRANSACTION_TYPE_ID
, 24
, NULL
, 80
, NULL
, DECODE ( MTA.PRIMARY_QUANTITY
, 0
, NULL
, NULL
, NULL
, DECODE(MTA.ACCOUNTING_LINE_TYPE
, 1
, MTA.BASE_TRANSACTION_VALUE/MTA.PRIMARY_QUANTITY
, 14
, MTA.BASE_TRANSACTION_VALUE/MTA.PRIMARY_QUANTITY
, 3
, MTA.BASE_TRANSACTION_VALUE/MTA.PRIMARY_QUANTITY
, ABS ( MTA.BASE_TRANSACTION_VALUE / MTA.PRIMARY_QUANTITY ) ) ) )
, MTA.ROWID
, MTA.LAST_UPDATE_DATE
, MTA.LAST_UPDATED_BY
, MTA.CREATION_DATE
, MTA.CREATED_BY
, MTA.LAST_UPDATE_LOGIN
, MTA.REQUEST_ID
, MTA.PROGRAM_APPLICATION_ID
, MTA.PROGRAM_ID
, MTA.PROGRAM_UPDATE_DATE
, MSI.INVENTORY_ITEM_ID
, MTA.ORGANIZATION_ID
, MTA.TRANSACTION_DATE
, MMT.PARENT_TRANSACTION_ID
, MP2.ORGANIZATION_CODE
, LU3.MEANING
FROM MTL_TRANSACTION_ACCOUNTS MTA
, MTL_MATERIAL_TRANSACTIONS MMT
, MTL_PARAMETERS MP
, MTL_PARAMETERS MP1
, MTL_PARAMETERS MP2
, CST_ACTIVITIES CA
, MTL_SYSTEM_ITEMS_VL MSI
, BOM_DEPARTMENTS BD
, BOM_RESOURCES BR
, MTL_TRANSACTION_REASONS MTR
, MTL_TXN_SOURCE_TYPES MTST
, MTL_TRANSACTION_TYPES MTT
, MFG_LOOKUPS LU1
, MFG_LOOKUPS LU2
, MFG_LOOKUPS LU3
, GL_DAILY_CONVERSION_TYPES GLT
WHERE MTA.TRANSACTION_ID = MMT.TRANSACTION_ID
AND ( MMT.TRANSACTION_ACTION_ID NOT IN (2
, 28
, 5
, 3) OR ( MMT.TRANSACTION_ACTION_ID IN (2
, 28
, 5)
AND MMT.PRIMARY_QUANTITY < 0
AND ( ( (MMT.TRANSACTION_TYPE_ID != 68 OR MTA.ACCOUNTING_LINE_TYPE != 13)
AND MMT.PRIMARY_QUANTITY = MTA.PRIMARY_QUANTITY ) OR ( MP.PRIMARY_COST_METHOD <> 1
AND MMT.TRANSACTION_TYPE_ID = 68
AND MTA.ACCOUNTING_LINE_TYPE = 13
AND ( ( MMT.COST_GROUP_ID <> MMT.TRANSFER_COST_GROUP_ID
AND MMT.PRIMARY_QUANTITY = DECODE(SIGN(MMT.PRIMARY_QUANTITY)
, -1
, MMT.PRIMARY_QUANTITY
, NULL)) OR (MMT.COST_GROUP_ID = MMT.TRANSFER_COST_GROUP_ID
AND MMT.PRIMARY_QUANTITY = -1 * MTA.PRIMARY_QUANTITY) ) ) OR ( MP.PRIMARY_COST_METHOD = 1
AND MMT.TRANSACTION_TYPE_ID = 68
AND MTA.ACCOUNTING_LINE_TYPE = 13
AND ( (MMT.COST_GROUP_ID <> MMT.TRANSFER_COST_GROUP_ID
AND MMT.PROJECT_ID = MTA.TRANSACTION_SOURCE_ID ) OR (MMT.COST_GROUP_ID = MMT.TRANSFER_COST_GROUP_ID
AND MMT.PRIMARY_QUANTITY =-1 * MTA.PRIMARY_QUANTITY) ) ) ) ) OR ( MMT.TRANSACTION_ACTION_ID = 3
AND MP.PRIMARY_COST_METHOD = 1
AND MP1.PRIMARY_COST_METHOD = 1
AND MMT.ORGANIZATION_ID = MTA.ORGANIZATION_ID ) OR ( MMT.TRANSACTION_ACTION_ID = 3
AND ( MP.PRIMARY_COST_METHOD <> 1 OR MP1.PRIMARY_COST_METHOD <> 1 ) ) )
AND MSI.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MMT.ORGANIZATION_ID
AND MP.ORGANIZATION_ID = MMT.ORGANIZATION_ID
AND MP1.ORGANIZATION_ID (+) = MMT.TRANSFER_ORGANIZATION_ID
AND MP2.ORGANIZATION_ID = MTA.ORGANIZATION_ID
AND MTT.TRANSACTION_TYPE_ID = MMT.TRANSACTION_TYPE_ID
AND MTST.TRANSACTION_SOURCE_TYPE_ID = MMT.TRANSACTION_SOURCE_TYPE_ID
AND BD.DEPARTMENT_ID ( + ) = MMT.DEPARTMENT_ID
AND MTR.REASON_ID ( + ) = MMT.REASON_ID
AND BR.RESOURCE_ID ( + ) = MTA.RESOURCE_ID
AND LU1.LOOKUP_TYPE = 'CST_ACCOUNTING_LINE_TYPE'
AND LU1.LOOKUP_CODE = MTA.ACCOUNTING_LINE_TYPE
AND LU2.LOOKUP_CODE ( + ) = MTA.BASIS_TYPE
AND LU2.LOOKUP_TYPE ( + ) = 'CST_BASIS_SHORT'
AND LU3.LOOKUP_TYPE ( + ) = 'MTL_LOGICAL_TRANSACTION_CODE'
AND LU3.LOOKUP_CODE ( + ) = MMT.LOGICAL_TRX_TYPE_CODE
AND GLT.CONVERSION_TYPE ( + ) = MTA.CURRENCY_CONVERSION_TYPE
AND CA.ACTIVITY_ID ( + ) = MTA.ACTIVITY_ID
AND MTA.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID
AND MTA.TRANSACTION_DATE BETWEEN TRUNC(MMT.TRANSACTION_DATE)
AND TRUNC(MMT.TRANSACTION_DATE)+0.99999 UNION ALL SELECT MMT.ORGANIZATION_ID
, /* TRANSACTION_ORGANIZATION_ID */ MTA.TRANSACTION_ID
, MTA.ORGANIZATION_ID
, /* ORGANIZATION_ID */ MMT.INVENTORY_ITEM_ID
, MSI.DESCRIPTION
, MMT.REVISION
, MMT.SUBINVENTORY_CODE
, MMT.LOCATOR_ID
, MMT.TRANSACTION_TYPE_ID
, MTT.TRANSACTION_TYPE_NAME
, MMT.TRANSACTION_SOURCE_TYPE_ID
, MTST.TRANSACTION_SOURCE_TYPE_NAME
, MMT.TRANSACTION_SOURCE_ID
, MMT.TRANSACTION_SOURCE_NAME
, MMT.TRANSACTION_DATE
, MMT.TRANSACTION_QUANTITY
, MMT.TRANSACTION_UOM
, DECODE (MTA.ACCOUNTING_LINE_TYPE
, 1
, MTA.PRIMARY_QUANTITY
, 14
, MTA.PRIMARY_QUANTITY
, 3
, MTA.PRIMARY_QUANTITY
, DECODE ( SIGN ( NVL ( MTA.BASE_TRANSACTION_VALUE
, 0) )
, -1
, -1 * ABS ( MTA.PRIMARY_QUANTITY )
, MTA.PRIMARY_QUANTITY ) )
, MSI.PRIMARY_UOM_CODE
, MMT.OPERATION_SEQ_NUM
, MTA.CURRENCY_CODE
, MTA.CURRENCY_CONVERSION_DATE
, GLT.USER_CONVERSION_TYPE
, MTA.CURRENCY_CONVERSION_RATE
, BD.DEPARTMENT_CODE
, MTR.REASON_NAME
, MMT.TRANSACTION_REFERENCE
, MTA.REFERENCE_ACCOUNT
, MTA.ACCOUNTING_LINE_TYPE
, LU1.MEANING
, MTA.TRANSACTION_VALUE
, MTA.BASE_TRANSACTION_VALUE
, LU2.MEANING
, MTA.COST_ELEMENT_ID
, CA.ACTIVITY
, MTA.RATE_OR_AMOUNT
, MTA.GL_BATCH_ID
, BR.RESOURCE_CODE
, DECODE ( MMT.TRANSACTION_TYPE_ID
, 24
, NULL
, 80
, NULL
, DECODE ( MTA.PRIMARY_QUANTITY
, 0
, NULL
, NULL
, NULL
, DECODE(MTA.ACCOUNTING_LINE_TYPE
, 1
, MTA.BASE_TRANSACTION_VALUE/MTA.PRIMARY_QUANTITY
, 14
, MTA.BASE_TRANSACTION_VALUE/MTA.PRIMARY_QUANTITY
, 3
, MTA.BASE_TRANSACTION_VALUE/MTA.PRIMARY_QUANTITY
, ABS ( MTA.BASE_TRANSACTION_VALUE / MTA.PRIMARY_QUANTITY ) ) ) )
, MTA.ROWID
, MTA.LAST_UPDATE_DATE
, MTA.LAST_UPDATED_BY
, MTA.CREATION_DATE
, MTA.CREATED_BY
, MTA.LAST_UPDATE_LOGIN
, MTA.REQUEST_ID
, MTA.PROGRAM_APPLICATION_ID
, MTA.PROGRAM_ID
, MTA.PROGRAM_UPDATE_DATE
, MSI.INVENTORY_ITEM_ID
, MTA.ORGANIZATION_ID
, MTA.TRANSACTION_DATE
, MMT.PARENT_TRANSACTION_ID
, MP2.ORGANIZATION_CODE
, LU3.MEANING
FROM MTL_TRANSACTION_ACCOUNTS MTA
, MTL_MATERIAL_TRANSACTIONS MMT
, MTL_PARAMETERS MP
, MTL_PARAMETERS MP1
, MTL_PARAMETERS MP2
, CST_ACTIVITIES CA
, MTL_SYSTEM_ITEMS_VL MSI
, BOM_DEPARTMENTS BD
, BOM_RESOURCES BR
, MTL_TRANSACTION_REASONS MTR
, MTL_TXN_SOURCE_TYPES MTST
, MTL_TRANSACTION_TYPES MTT
, MFG_LOOKUPS LU1
, MFG_LOOKUPS LU2
, MFG_LOOKUPS LU3
, GL_DAILY_CONVERSION_TYPES GLT
WHERE MTA.TRANSACTION_ID = MMT.TRANSFER_TRANSACTION_ID
AND ( (MMT.TRANSACTION_ACTION_ID IN (2
, 28
, 5)
AND MMT.PRIMARY_QUANTITY > 0
AND ( ( (MMT.TRANSACTION_TYPE_ID != 68 OR MTA.ACCOUNTING_LINE_TYPE != 13)
AND MMT.PRIMARY_QUANTITY = MTA.PRIMARY_QUANTITY ) OR ( MP.PRIMARY_COST_METHOD <> 1
AND MMT.TRANSACTION_TYPE_ID = 68
AND MTA.ACCOUNTING_LINE_TYPE = 13
AND ( ( MMT.COST_GROUP_ID <> MMT.TRANSFER_COST_GROUP_ID
AND MMT.PRIMARY_QUANTITY = DECODE(SIGN(MMT.PRIMARY_QUANTITY)
, -1
, MMT.PRIMARY_QUANTITY
, NULL)) OR (MMT.COST_GROUP_ID = MMT.TRANSFER_COST_GROUP_ID
AND MMT.PRIMARY_QUANTITY = -1 * MTA.PRIMARY_QUANTITY)) ) OR ( MP.PRIMARY_COST_METHOD = 1
AND MMT.TRANSACTION_TYPE_ID = 68
AND MTA.ACCOUNTING_LINE_TYPE = 13
AND ( ( MMT.COST_GROUP_ID <> MMT.TRANSFER_COST_GROUP_ID
AND MMT.PROJECT_ID = MTA.TRANSACTION_SOURCE_ID) OR (MMT.COST_GROUP_ID = MMT.TRANSFER_COST_GROUP_ID
AND MMT.PRIMARY_QUANTITY = -1 * MTA.PRIMARY_QUANTITY) ) ) ) ) OR ( MMT.TRANSACTION_ACTION_ID = 3
AND MP.PRIMARY_COST_METHOD = 1
AND MP1.PRIMARY_COST_METHOD = 1
AND MMT.ORGANIZATION_ID = MTA.ORGANIZATION_ID ) )
AND MSI.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MMT.ORGANIZATION_ID
AND MP.ORGANIZATION_ID = MMT.ORGANIZATION_ID
AND MP1.ORGANIZATION_ID (+) = MMT.TRANSFER_ORGANIZATION_ID
AND MP2.ORGANIZATION_ID = MTA.ORGANIZATION_ID
AND MTT.TRANSACTION_TYPE_ID = MMT.TRANSACTION_TYPE_ID
AND MTST.TRANSACTION_SOURCE_TYPE_ID = MMT.TRANSACTION_SOURCE_TYPE_ID
AND BD.DEPARTMENT_ID ( + ) = MMT.DEPARTMENT_ID
AND MTR.REASON_ID ( + ) = MMT.REASON_ID
AND BR.RESOURCE_ID ( + ) = MTA.RESOURCE_ID
AND LU1.LOOKUP_TYPE = 'CST_ACCOUNTING_LINE_TYPE'
AND LU1.LOOKUP_CODE = MTA.ACCOUNTING_LINE_TYPE
AND LU2.LOOKUP_CODE ( + ) = MTA.BASIS_TYPE
AND LU2.LOOKUP_TYPE ( + ) = 'CST_BASIS_SHORT'
AND LU3.LOOKUP_TYPE ( + ) = 'MTL_LOGICAL_TRANSACTION_CODE'
AND LU3.LOOKUP_CODE ( + ) = MMT.LOGICAL_TRX_TYPE_CODE
AND GLT.CONVERSION_TYPE ( + ) = MTA.CURRENCY_CONVERSION_TYPE
AND CA.ACTIVITY_ID ( + ) = MTA.ACTIVITY_ID
AND MTA.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID
AND MTA.TRANSACTION_DATE BETWEEN TRUNC(MMT.TRANSACTION_DATE)
AND TRUNC(MMT.TRANSACTION_DATE)+0.99999