VIEW: APPS.CST_PM_MATL_TXN_V

Source

View Text - Preformatted

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'

View Text - HTML Formatted

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'