FND Design Data [Home] [Help]

View: CST_INV_DISTRIBUTION_V

Product: BOM - Bills of Material
Description: Material transaction distribution information : SINGLE-ORG view
Implementation/DBA Data: ViewAPPS.CST_INV_DISTRIBUTION_V
View Text

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

Columns

Name
TRANSACTION_ORGANIZATION_ID
TRANSACTION_ID
ORGANIZATION_ID
INVENTORY_ITEM_ID
DESCRIPTION
REVISION
SUBINVENTORY_CODE
LOCATOR_ID
TRANSACTION_TYPE_ID
TRANSACTION_TYPE_NAME
TRANSACTION_SOURCE_TYPE_ID
TRANSACTION_SOURCE_TYPE_NAME
TRANSACTION_SOURCE_ID
TRANSACTION_SOURCE_NAME
TRANSACTION_DATE
TRANSACTION_QUANTITY
TRANSACTION_UOM
PRIMARY_QUANTITY
PRIMARY_UOM
OPERATION_SEQ_NUM
CURRENCY_CODE
CURRENCY_CONVERSION_DATE
CURRENCY_CONVERSION_TYPE
CURRENCY_CONVERSION_RATE
DEPARTMENT_CODE
REASON_NAME
TRANSACTION_REFERENCE
REFERENCE_ACCOUNT
ACCOUNTING_LINE_TYPE
LINE_TYPE_NAME
TRANSACTION_VALUE
BASE_TRANSACTION_VALUE
BASIS_TYPE_NAME
COST_ELEMENT_ID
ACTIVITY
RATE_OR_AMOUNT
GL_BATCH_ID
RESOURCE_CODE
UNIT_COST
ROW_ID
LAST_UPDATE_DATE
LAST_UPDATED_BY
CREATION_DATE
CREATED_BY
LAST_UPDATE_LOGIN
REQUEST_ID
PROGRAM_APPLICATION_ID
PROGRAM_ID
PROGRAM_UPDATE_DATE
MSI_INVENTORY_ITEM_ID
MSI_ORGANIZATION_ID
MTA_TRANSACTION_DATE
PARENT_TRANSACTION_ID
ORGANIZATION_CODE
LOGICAL_TRX_TYPE