View: CST_PM_MATL_TXN_V

Product:	BOM - Bills of Material
Description:	Used by concurrent programs for Project Cost Transfer to Oracle Projects
Implementation/DBA Data:	APPS.CST_PM_MATL_TXN_V

View Text

SELECT MMT.TRANSACTION_ID
, MMT.ORGANIZATION_ID
, MMT.TRANSACTION_ACTION_ID
, MMT.TRANSACTION_SOURCE_TYPE_ID
, NVL(MTT.TYPE_CLASS
, -99) TYPE_CLASS
, MMT.EXPENDITURE_TYPE
, MMT.TRANSACTION_DATE
, MMT.PROJECT_ID
, MMT.TASK_ID
, MMT.INVENTORY_ITEM_ID
, MMT.PRIMARY_QUANTITY
, MMT.COSTED_FLAG
, MP.PRIMARY_COST_METHOD
, MP.AVG_RATES_COST_TYPE_ID
, MSITEM.DESCRIPTION ITEM_DESCRIPTION
, MMT.COST_GROUP_ID
, MMT.TRANSFER_COST_GROUP_ID
, MMT.TRANSACTION_SOURCE_ID
, MMT.TO_PROJECT_ID
, MMT.TO_TASK_ID
, MMT.SOURCE_PROJECT_ID
, MMT.SOURCE_TASK_ID
, MMT.TRANSFER_TRANSACTION_ID
, MMT.ACCT_PERIOD_ID
, DECODE(MTT.TYPE_CLASS
, 1
, MMT.PA_EXPENDITURE_ORG_ID
, MMT.ORGANIZATION_ID) EXP_ORG_ID
, MMT.DISTRIBUTION_ACCOUNT_ID
, MMT.PM_COST_COLLECTOR_GROUP_ID
, MSI1.ASSET_INVENTORY SI_ASSET_YES_NO
, MSI2.ASSET_INVENTORY TRANSFER_SI_ASSET_YES_NO
, MMT.PM_COST_COLLECTED
, MMT.SUBINVENTORY_CODE
, MMT.TRANSFER_SUBINVENTORY
, MMT.TRANSFER_ORGANIZATION_ID
, MSITEM.INVENTORY_ASSET_FLAG
, NVL(MMT.FLOW_SCHEDULE
, 'N') FLOW_SCHEDULE
FROM MTL_MATERIAL_TRANSACTIONS MMT
, MTL_SECONDARY_INVENTORIES MSI1
, MTL_TRANSACTION_TYPES MTT
, MTL_PARAMETERS MP
, MTL_SECONDARY_INVENTORIES MSI2
, MTL_SYSTEM_ITEMS MSITEM
WHERE MMT.ORGANIZATION_ID = MSI1.ORGANIZATION_ID
AND MMT.SUBINVENTORY_CODE = MSI1.SECONDARY_INVENTORY_NAME
AND MMT.TRANSFER_ORGANIZATION_ID = MSI2.ORGANIZATION_ID (+)
AND MMT.TRANSFER_SUBINVENTORY = MSI2.SECONDARY_INVENTORY_NAME(+)
AND MSITEM.ORGANIZATION_ID = MMT.ORGANIZATION_ID
AND MSITEM.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID
AND MMT.TRANSACTION_TYPE_ID = MTT.TRANSACTION_TYPE_ID
AND MMT.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND NVL(MP.PROCESS_ENABLED_FLAG
, 'N') = 'N' /* OPM INVCONV UMOOGALA BUG 3980701 11/09/04 */
AND NOT ( MSITEM.INVENTORY_ASSET_FLAG = 'N'
AND MMT.TRANSACTION_SOURCE_TYPE_ID != 1 )
AND NOT ( MSITEM.INVENTORY_ASSET_FLAG = 'Y'
AND MSI1.ASSET_INVENTORY = 2
AND MMT.TRANSACTION_ACTION_ID IN (1
, 27
, 4
, 8)
AND MMT.TRANSACTION_SOURCE_TYPE_ID <> 1 )
AND ( ( MP.PRIMARY_COST_METHOD IN (1
, 2
, 5
, 6)
AND ( ( MMT.PROJECT_ID IS NOT NULL
AND MMT.TRANSACTION_ACTION_ID NOT IN (2
, 5) ) OR ( MMT.PROJECT_ID IS NULL
AND MMT.TRANSACTION_ACTION_ID IN (1
, 27
, 31
, 32
, 33
, 34)
AND MMT.TRANSACTION_SOURCE_TYPE_ID = 5
AND NVL(MMT.FLOW_SCHEDULE
, 'N') != 'Y'
AND EXISTS ( SELECT NULL
FROM WIP_DISCRETE_JOBS WDJ
WHERE WDJ.WIP_ENTITY_ID= MMT.TRANSACTION_SOURCE_ID
AND WDJ.PROJECT_ID IS NOT NULL ) ) OR ( MMT.PROJECT_ID IS NULL
AND MMT.TRANSACTION_ACTION_ID IN ( 1
, 27
, 31
, 32
, 33
, 34 )
AND MMT.TRANSACTION_SOURCE_TYPE_ID = 5
AND NVL(MMT.FLOW_SCHEDULE
, 'N') = 'Y'
AND EXISTS ( SELECT NULL
FROM WIP_FLOW_SCHEDULES WFS
WHERE WFS.WIP_ENTITY_ID= MMT.TRANSACTION_SOURCE_ID
AND WFS.PROJECT_ID IS NOT NULL ) ) OR NVL(MTT.TYPE_CLASS
, -99) = 1 OR ( MMT.TRANSACTION_ACTION_ID IN (2
, 28)
AND ( MMT.PROJECT_ID IS NOT NULL OR MMT.TO_PROJECT_ID IS NOT NULL ) ) ) ) )
AND NOT ( MMT.TRANSACTION_ACTION_ID IN (1
, 27
, 33
, 34)
AND MMT.TRANSACTION_SOURCE_TYPE_ID = 5
AND NVL(MMT.FLOW_SCHEDULE
, 'N') != 'Y'
AND NVL(MMT.PROJECT_ID
, -99) = NVL(MMT.SOURCE_PROJECT_ID
, -99)
AND NVL(MMT.TASK_ID
, -99) = NVL(MMT.SOURCE_TASK_ID
, -99)
AND MMT.PROJECT_ID IS NOT NULL
AND MMT.TASK_ID IS NOT NULL
AND EXISTS ( (SELECT NULL
FROM WIP_DISCRETE_JOBS WDJ
WHERE WDJ.WIP_ENTITY_ID= MMT.TRANSACTION_SOURCE_ID
AND WDJ.PROJECT_ID IS NOT NULL) UNION ALL (SELECT NULL
FROM PJM_ORG_PARAMETERS POP
WHERE POP.ORGANIZATION_ID = MMT.ORGANIZATION_ID
AND POP.COMMON_PROJECT_ID IS NOT NULL) ) )
AND NOT ( MMT.TRANSACTION_ACTION_ID IN ( 1
, 27
, 33
, 34 )
AND MMT.TRANSACTION_SOURCE_TYPE_ID = 5
AND NVL(MMT.FLOW_SCHEDULE
, 'N') = 'Y'
AND NVL(MMT.PROJECT_ID
, -99) = NVL(MMT.SOURCE_PROJECT_ID
, -99)
AND NVL(MMT.TASK_ID
, -99) = NVL(MMT.SOURCE_TASK_ID
, -99)
AND MMT.PROJECT_ID IS NOT NULL
AND MMT.TASK_ID IS NOT NULL
AND EXISTS ( SELECT NULL
FROM WIP_FLOW_SCHEDULES WFS
WHERE WFS.WIP_ENTITY_ID= MMT.TRANSACTION_SOURCE_ID
AND WFS.PROJECT_ID IS NOT NULL ) )
AND NOT ( NVL(MTT.TYPE_CLASS
, -99) = 1
AND NVL(MMT.PROJECT_ID
, -99) = NVL(MMT.SOURCE_PROJECT_ID
, -99)
AND NVL(MMT.TASK_ID
, -99) = NVL(MMT.SOURCE_TASK_ID
, -99)
AND MMT.PROJECT_ID IS NOT NULL
AND MMT.TASK_ID IS NOT NULL )
AND NOT ( MMT.TRANSACTION_ACTION_ID IN ( 1
, 27
, 33
, 34 )
AND MMT.TRANSACTION_SOURCE_TYPE_ID = 5
AND NVL(MMT.FLOW_SCHEDULE
, 'N') != 'Y'
AND MSI1.ASSET_INVENTORY = 2
AND EXISTS ( SELECT NULL
FROM WIP_DISCRETE_JOBS WDJ
, WIP_ACCOUNTING_CLASSES WAC
WHERE WDJ.WIP_ENTITY_ID= MMT.TRANSACTION_SOURCE_ID
AND WDJ.PROJECT_ID IS NOT NULL
AND WAC.CLASS_CODE = WDJ.CLASS_CODE
AND WAC.CLASS_TYPE = 4 ) )
AND NOT ( MMT.TRANSACTION_ACTION_ID IN ( 1
, 27
, 33
, 34 )
AND MMT.TRANSACTION_SOURCE_TYPE_ID = 5
AND NVL(MMT.FLOW_SCHEDULE
, 'N') = 'Y'
AND MSI1.ASSET_INVENTORY = 2
AND EXISTS ( SELECT NULL
FROM WIP_FLOW_SCHEDULES WFS
, WIP_ACCOUNTING_CLASSES WAC
WHERE WFS.WIP_ENTITY_ID= MMT.TRANSACTION_SOURCE_ID
AND WFS.PROJECT_ID IS NOT NULL
AND WAC.CLASS_CODE = WFS.CLASS_CODE
AND WAC.CLASS_TYPE = 4 ) )
AND NOT ( MMT.TRANSACTION_ACTION_ID IN 2
AND MMT.PRIMARY_QUANTITY > 0 )
AND NOT ( MMT.TRANSACTION_ACTION_ID IN (2
, 28)
AND NVL(MMT.PROJECT_ID
, -99) = NVL(MMT.TO_PROJECT_ID
, -99)
AND NVL(MMT.TASK_ID
, -99) = NVL(MMT.TO_TASK_ID
, -99)
AND MMT.PROJECT_ID IS NOT NULL
AND MMT.TO_PROJECT_ID IS NOT NULL
AND MMT.TASK_ID IS NOT NULL )
AND NOT MMT.TRANSACTION_ACTION_ID IN (30
, 24
, 55)
AND NOT ( MMT.TRANSACTION_SOURCE_TYPE_ID IN (2
, 8
, 16)
AND MMT.TRANSACTION_ACTION_ID = 1 )
AND NOT ( MMT.TRANSACTION_SOURCE_TYPE_ID = 12
AND MMT.TRANSACTION_ACTION_ID IN (1
, 27) )
AND NOT ( MMT.TRANSACTION_ACTION_ID IN (2
, 28)
AND MSI1.ASSET_INVENTORY = 2
AND MSI2.ASSET_INVENTORY = 2 )
AND MMT.COSTED_FLAG IS NULL
AND MMT.PM_COST_COLLECTED = 'N' UNION SELECT MMT.TRANSACTION_ID
, MMT.ORGANIZATION_ID
, MMT.TRANSACTION_ACTION_ID
, MMT.TRANSACTION_SOURCE_TYPE_ID
, NVL(MTT.TYPE_CLASS
, -99)
, MMT.EXPENDITURE_TYPE
, MMT.TRANSACTION_DATE
, MMT.PROJECT_ID
, MMT.TASK_ID
, MMT.INVENTORY_ITEM_ID
, MMT.PRIMARY_QUANTITY
, MMT.COSTED_FLAG
, MP.PRIMARY_COST_METHOD
, MP.AVG_RATES_COST_TYPE_ID
, MSITEM.DESCRIPTION ITEM_DESCRIPTION
, MMT.COST_GROUP_ID
, MMT.TRANSFER_COST_GROUP_ID
, MMT.TRANSACTION_SOURCE_ID
, MMT.TO_PROJECT_ID
, MMT.TO_TASK_ID
, MMT.SOURCE_PROJECT_ID
, MMT.SOURCE_TASK_ID
, MMT.TRANSFER_TRANSACTION_ID
, MMT.ACCT_PERIOD_ID
, MMT.PA_EXPENDITURE_ORG_ID EXP_ORG_ID
, MMT.DISTRIBUTION_ACCOUNT_ID
, MMT.PM_COST_COLLECTOR_GROUP_ID
, TO_NUMBER(NULL) SI_ASSET_YES_NO
, TO_NUMBER(NULL) TRANSFER_SI_ASSET_YES_NO
, MMT.PM_COST_COLLECTED
, MMT.SUBINVENTORY_CODE
, MMT.TRANSFER_SUBINVENTORY
, MMT.TRANSFER_ORGANIZATION_ID
, MSITEM.INVENTORY_ASSET_FLAG
, NVL(MMT.FLOW_SCHEDULE
, 'N')
FROM MTL_MATERIAL_TRANSACTIONS MMT
, MTL_TRANSACTION_TYPES MTT
, MTL_PARAMETERS MP
, MTL_SYSTEM_ITEMS MSITEM
WHERE MSITEM.ORGANIZATION_ID = MMT.ORGANIZATION_ID
AND MSITEM.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID
AND MMT.TRANSACTION_TYPE_ID = MTT.TRANSACTION_TYPE_ID
AND MMT.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND NVL(MP.PROCESS_ENABLED_FLAG
, 'N') = 'N' /* OPM INVCONV UMOOGALA BUG 3980701 11/09/04 */
AND MMT.TRANSACTION_ACTION_ID = 17
AND MMT.TRANSACTION_SOURCE_TYPE_ID = 7
AND MMT.TRANSACTION_TYPE_ID= 27
AND MMT.COSTED_FLAG IS NULL
AND MMT.PROJECT_ID IS NOT NULL
AND MMT.TASK_ID IS NOT NULL
AND MMT.PM_COST_COLLECTED = 'N'

Columns