View: CST_DISTRIBUTION_V

Product:	BOM - Bills of Material
Description:	Transaction details and accounting distributions for a job or schedule in an accounting period : SINGLE-ORG view
Implementation/DBA Data:	APPS.CST_DISTRIBUTION_V

View Text

SELECT MMT.TRANSACTION_ID
, WE.ORGANIZATION_ID
, WE.WIP_ENTITY_ID
, DECODE(WL.LINE_ID
, NULL
, WE.WIP_ENTITY_NAME
, '')
, WE.PRIMARY_ITEM_ID
, MMT.REPETITIVE_LINE_ID
, WL.LINE_CODE
, MMT.ACCT_PERIOD_ID
, MTT.TRANSACTION_TYPE_NAME
, MMT.TRANSACTION_DATE
, DECODE (MMT.TRANSACTION_ACTION_ID
, 40
, MTA.PRIMARY_QUANTITY
, 41
, MTA.PRIMARY_QUANTITY
, 43
, MTA.PRIMARY_QUANTITY
, -MMT.TRANSACTION_QUANTITY)
, MMT.TRANSACTION_UOM
, MTA.PRIMARY_QUANTITY
, MSI.PRIMARY_UOM_CODE
, MMT.OPERATION_SEQ_NUM
, MMT.CURRENCY_CODE
, MMT.CURRENCY_CONVERSION_DATE
, MMT.CURRENCY_CONVERSION_TYPE
, MMT.CURRENCY_CONVERSION_RATE
, BD.DEPARTMENT_CODE
, BD.DESCRIPTION
, MTR.REASON_NAME
, MMT.TRANSACTION_REFERENCE
, MMT.INVENTORY_ITEM_ID
, MMT.REVISION
, MMT.SUBINVENTORY_CODE
, TO_NUMBER('')
, MTA.REFERENCE_ACCOUNT + 0
, BR.RESOURCE_CODE
, MTA.REPETITIVE_SCHEDULE_ID
, LU2.MEANING /* ACCOUNTING_LINE_TYPE */
, MTA.TRANSACTION_VALUE
, MTA.BASE_TRANSACTION_VALUE
, MTA.CONTRA_SET_ID
, '' /* BASIS */
, DECODE(MTA.COST_ELEMENT_ID
, 1
, 'MATERIAL'
, 2
, 'MATERIAL OVERHEAD'
, 3
, 'RESOURCE'
, 4
, 'OUTSIDE PROCESSING'
, 5
, 'OVERHEAD')
, CA.ACTIVITY
, MTA.RATE_OR_AMOUNT
, MTA.GL_BATCH_ID
, -1
, -1
, NULL
, DECODE(MMT.PRIMARY_QUANTITY
, NULL
, NULL
, 0
, NULL
, DECODE(MTA.RATE_OR_AMOUNT
, NULL
, ABS(MTA.BASE_TRANSACTION_VALUE / DECODE(MMT.TRANSACTION_ACTION_ID
, 40
, MTA.PRIMARY_QUANTITY
, 41
, MTA.PRIMARY_QUANTITY
, 43
, MTA.PRIMARY_QUANTITY
, MMT.PRIMARY_QUANTITY) )
, ABS(MTA.RATE_OR_AMOUNT)))
, MMT.ROWID
, MMT.LAST_UPDATE_DATE
, MMT.LAST_UPDATED_BY
, MMT.CREATION_DATE
, MMT.CREATED_BY
, MMT.LAST_UPDATE_LOGIN
, MMT.REQUEST_ID
, MMT.PROGRAM_APPLICATION_ID
, MMT.PROGRAM_ID
, MMT.PROGRAM_UPDATE_DATE
, (SELECT CII.INSTANCE_NUMBER
FROM CSI_ITEM_INSTANCES CII
WHERE CII.SERIAL_NUMBER = WDJ.ASSET_NUMBER
AND CII.INVENTORY_ITEM_ID = WDJ.ASSET_GROUP_ID
AND WDJ.ASSET_GROUP_ID IS NOT NULL) AS ASSET_NUMBER
, WDJ.ASSET_GROUP_ID
, WDJ.REBUILD_ITEM_ID
, (SELECT CII.INSTANCE_NUMBER
FROM CSI_ITEM_INSTANCES CII
WHERE CII.SERIAL_NUMBER = WDJ.REBUILD_SERIAL_NUMBER
AND CII.INVENTORY_ITEM_ID = WDJ.REBUILD_ITEM_ID
AND WDJ.REBUILD_SERIAL_NUMBER IS NOT NULL) AS REBUILD_SERIAL_NUMBER
FROM WIP_REPETITIVE_ITEMS WRI
, WIP_DISCRETE_JOBS WDJ
, WIP_LINES WL
, BOM_DEPARTMENTS BD
, BOM_RESOURCES BR
, CST_ACTIVITIES CA
, MTL_TRANSACTION_REASONS MTR
, MTL_TRANSACTION_TYPES MTT
, MTL_SYSTEM_ITEMS MSI
, MFG_LOOKUPS LU2
, MTL_TRANSACTION_ACCOUNTS MTA
, MTL_MATERIAL_TRANSACTIONS MMT
, WIP_ENTITIES WE
WHERE MTA.TRANSACTION_SOURCE_ID = WE.WIP_ENTITY_ID
AND MMT.ORGANIZATION_ID = WE.ORGANIZATION_ID
AND MTT.TRANSACTION_TYPE_ID = MMT.TRANSACTION_TYPE_ID
AND MTR.REASON_ID(+) = MMT.REASON_ID
AND BD.DEPARTMENT_ID(+) = MMT.DEPARTMENT_ID
AND MTA.TRANSACTION_SOURCE_TYPE_ID = 5
AND MTA.TRANSACTION_ID = MMT.TRANSACTION_ID
AND LU2.LOOKUP_TYPE = 'CST_ACCOUNTING_LINE_TYPE'
AND LU2.LOOKUP_CODE = MTA.ACCOUNTING_LINE_TYPE
AND MSI.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MTA.ORGANIZATION_ID
AND CA.ACTIVITY_ID(+) = MTA.ACTIVITY_ID
AND BR.RESOURCE_ID(+) = MTA.RESOURCE_ID
AND WL.LINE_ID(+) = MMT.REPETITIVE_LINE_ID
AND WDJ.WIP_ENTITY_ID(+) = MTA.TRANSACTION_SOURCE_ID
AND WRI.WIP_ENTITY_ID(+) = MTA.TRANSACTION_SOURCE_ID
AND WRI.LINE_ID(+) = MTA.TRANSACTION_SOURCE_ID UNION ALL SELECT WT.TRANSACTION_ID
, WE.ORGANIZATION_ID
, WE.WIP_ENTITY_ID
, DECODE(WT.LINE_ID
, NULL
, WE.WIP_ENTITY_NAME
, '')
, WE.PRIMARY_ITEM_ID
, WT.LINE_ID
, WL.LINE_CODE
, WT.ACCT_PERIOD_ID
, LU1.MEANING
, /* TRX TYPE NAME */ WT.TRANSACTION_DATE
, DECODE(WT.TRANSACTION_TYPE
, 11
, WTA.PRIMARY_QUANTITY
, 12
, WTA.PRIMARY_QUANTITY
, 14
, WTA.PRIMARY_QUANTITY
, WT.TRANSACTION_QUANTITY)
, WT.TRANSACTION_UOM
, DECODE(WT.TRANSACTION_TYPE
, 11
, WTA.PRIMARY_QUANTITY
, 12
, WTA.PRIMARY_QUANTITY
, 14
, WTA.PRIMARY_QUANTITY
, DECODE(WTA.BASE_TRANSACTION_VALUE
, 0
, WTA.PRIMARY_QUANTITY
, SIGN(WTA.BASE_TRANSACTION_VALUE) * ABS(WTA.PRIMARY_QUANTITY)))
, WT.PRIMARY_UOM
, WT.OPERATION_SEQ_NUM
, WT.CURRENCY_CODE
, WT.CURRENCY_CONVERSION_DATE
, WT.CURRENCY_CONVERSION_TYPE
, WT.CURRENCY_CONVERSION_RATE
, BD.DEPARTMENT_CODE
, BD.DESCRIPTION
, MTR.REASON_NAME
, WT.REFERENCE
, -1
, NULL
, NULL
, WT.RESOURCE_SEQ_NUM
, WTA.REFERENCE_ACCOUNT
, BR.RESOURCE_CODE
, WTA.REPETITIVE_SCHEDULE_ID
, LU2.MEANING
, /* ACCOUNTING_LINE_TYPE */ WTA.TRANSACTION_VALUE
, WTA.BASE_TRANSACTION_VALUE
, WTA.CONTRA_SET_ID
, LU3.MEANING
, /* BASIS */ DECODE(WTA.COST_ELEMENT_ID
, 1
, 'MATERIAL'
, 2
, 'MATERIAL OVERHEAD'
, 3
, 'RESOURCE'
, 4
, 'OUTSIDE PROCESSING'
, 5
, 'OVERHEAD'
, ' ' )
, CA.ACTIVITY
, WTA.RATE_OR_AMOUNT
, WTA.GL_BATCH_ID
, WTA.OVERHEAD_BASIS_FACTOR
, WTA.BASIS_RESOURCE_ID
, POH.SEGMENT1
, DECODE(WTA.PRIMARY_QUANTITY
, NULL
, NULL
, 0
, NULL
, DECODE (WTA.RATE_OR_AMOUNT
, NULL
, ABS(WTA.BASE_TRANSACTION_VALUE / WTA.PRIMARY_QUANTITY)
, ABS(WTA.RATE_OR_AMOUNT)) )
, WT.ROWID
, WT.LAST_UPDATE_DATE
, WT.LAST_UPDATED_BY
, WT.CREATION_DATE
, WT.CREATED_BY
, WT.LAST_UPDATE_LOGIN
, WT.REQUEST_ID
, WT.PROGRAM_APPLICATION_ID
, WT.PROGRAM_ID
, WT.PROGRAM_UPDATE_DATE
, (SELECT CII.INSTANCE_NUMBER
FROM CSI_ITEM_INSTANCES CII
WHERE CII.SERIAL_NUMBER = WDJ.ASSET_NUMBER
AND CII.INVENTORY_ITEM_ID = WDJ.ASSET_GROUP_ID
AND WDJ.ASSET_GROUP_ID IS NOT NULL) AS ASSET_NUMBER
, WDJ.ASSET_GROUP_ID
, WDJ.REBUILD_ITEM_ID
, (SELECT CII.INSTANCE_NUMBER
FROM CSI_ITEM_INSTANCES CII
WHERE CII.SERIAL_NUMBER = WDJ.REBUILD_SERIAL_NUMBER
AND CII.INVENTORY_ITEM_ID = WDJ.REBUILD_ITEM_ID
AND WDJ.REBUILD_SERIAL_NUMBER IS NOT NULL) AS REBUILD_SERIAL_NUMBER
FROM CST_ACTIVITIES CA
, MFG_LOOKUPS LU3
, MFG_LOOKUPS LU2
, PO_HEADERS POH
, BOM_RESOURCES BR
, MTL_TRANSACTION_REASONS MTR
, BOM_DEPARTMENTS BD
, MFG_LOOKUPS LU1
, WIP_LINES WL
, WIP_DISCRETE_JOBS WDJ
, WIP_REPETITIVE_ITEMS WRI
, WIP_TRANSACTION_ACCOUNTS WTA
, WIP_TRANSACTIONS WT
, WIP_ENTITIES WE
WHERE WTA.TRANSACTION_ID = WT.TRANSACTION_ID
AND WE.WIP_ENTITY_ID = WTA.WIP_ENTITY_ID
AND WL.LINE_ID(+) = WT.LINE_ID
AND WRI.WIP_ENTITY_ID(+) = WT.WIP_ENTITY_ID
AND WRI.LINE_ID(+) = WT.LINE_ID
AND WDJ.WIP_ENTITY_ID(+) = WT.WIP_ENTITY_ID
AND WE.ORGANIZATION_ID = WT.ORGANIZATION_ID
AND LU1.LOOKUP_TYPE(+) = 'WIP_TRANSACTION_TYPE'
AND LU1.LOOKUP_CODE(+) = WT.TRANSACTION_TYPE
AND BD.DEPARTMENT_ID(+) = WT.DEPARTMENT_ID
AND MTR.REASON_ID(+) = WT.REASON_ID
AND BR.RESOURCE_ID(+) = WTA.RESOURCE_ID
AND POH.PO_HEADER_ID(+) = WT.PO_HEADER_ID
AND LU2.LOOKUP_TYPE = 'CST_ACCOUNTING_LINE_TYPE'
AND LU2.LOOKUP_CODE = WTA.ACCOUNTING_LINE_TYPE
AND LU3.LOOKUP_CODE(+) = WTA.BASIS_TYPE
AND LU3.LOOKUP_TYPE(+) = 'CST_BASIS'
AND CA.ACTIVITY_ID(+) = WTA.ACTIVITY_ID

Columns