View: CST_DISTRIBUTION_LITE_V

Product:	BOM - Bills of Material
Description:	Distributions view for WIP jobs that joins to less tables than CST_DISTRIBUTION_V and has less columns.
Implementation/DBA Data:	APPS.CST_DISTRIBUTION_LITE_V

View Text

SELECT MMT.TRANSACTION_ID
, WE.ORGANIZATION_ID
, WE.WIP_ENTITY_ID
, WE.PRIMARY_ITEM_ID
, MMT.REPETITIVE_LINE_ID
, 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
, DECODE (MMT.TRANSACTION_ACTION_ID
, 40
, MTA.PRIMARY_QUANTITY
, 41
, MTA.PRIMARY_QUANTITY
, 43
, MTA.PRIMARY_QUANTITY
, SIGN(MMT.PRIMARY_QUANTITY) * DECODE( SIGN(MTA.BASE_TRANSACTION_VALUE)
, 0
, 1
, SIGN(MTA.BASE_TRANSACTION_VALUE)) * MMT.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
, 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')
, 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) )
, 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
, WDJ.ASSET_NUMBER
, WDJ.ASSET_GROUP_ID
, WDJ.REBUILD_ITEM_ID
, WDJ.REBUILD_SERIAL_NUMBER
FROM WIP_REPETITIVE_ITEMS WRI
, WIP_DISCRETE_JOBS WDJ
, BOM_RESOURCES BR
, 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 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 BR.RESOURCE_ID(+) = MTA.RESOURCE_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
, WE.PRIMARY_ITEM_ID
, WT.LINE_ID
, 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
, SIGN(WTA.PRIMARY_QUANTITY) * SIGN(WTA.BASE_TRANSACTION_VALUE) * 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
, 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'
, ' ' )
, 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)
, 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
, WDJ.ASSET_NUMBER
, WDJ.ASSET_GROUP_ID
, WDJ.REBUILD_ITEM_ID
, WDJ.REBUILD_SERIAL_NUMBER
FROM MFG_LOOKUPS LU3
, MFG_LOOKUPS LU2
, PO_HEADERS_ALL POH
, BOM_RESOURCES BR
, MFG_LOOKUPS LU1
, 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 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 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 WTA.WIP_ENTITY_ID = WT.WIP_ENTITY_ID
AND WE.WIP_ENTITY_ID = WTA.WIP_ENTITY_ID
AND WE.WIP_ENTITY_ID = WT.WIP_ENTITY_ID

Columns