VIEW: APPS.CST_DISTRIBUTION_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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