eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.INV_DIAG_MMTT SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 19


 sqltxt    VARCHAR2(9999);  -- SQL select statement

Line: 37


JTF_DIAGNOSTIC_COREAPI.insert_style_sheet;

Line: 62


    SELECT acct_period_id
    into l_acct_period_id
    FROM org_acct_periods
    WHERE organization_id = l_org_id
    AND period_name = l_acct_period;

Line: 81


sqltxt := 'SELECT  fa.application_name "Application Name"  '||
          '     , fa.application_short_name "Application|Shortname"  '||
          '     , fcp.concurrent_processor_name "Name"  '||
          '     , fcq.user_concurrent_queue_name "Manager"  '||
          '     , NVL( fcq.target_node,''n/a'') "Node"  '||
          '     , fcq.running_processes "Actual"  '||
          '     , fcq.max_processes "Target"  '||
          ' FROM fnd_concurrent_queues_vl fcq  '||
          '     , fnd_application_vl fa  '||
          '     , fnd_concurrent_processors fcp '||
          'WHERE fa.application_id = fcq.application_id  '||
          '  AND fcq.application_id = fcp.application_id  '||
          '  AND fcq.concurrent_processor_id = fcp.concurrent_processor_id  '||
          '  AND fa.application_short_name IN ( ''INV'' )  '||
          'ORDER BY fcp.application_id DESC  '||
          ', fcp.concurrent_processor_id  '||
          ', fcp.concurrent_processor_name';

Line: 104


sqltxt :='SELECT DISTINCT period_name "Period|Name"  '||
          '     , oap.acct_period_id "Period|Id"  '||
          '     , mp.organization_code "Organization|Code"  '||
          '     , mmtt.organization_id "Organization|Id"  '||
          '     , TO_CHAR( period_start_date, ''DD-MON-YYYY'' ) "Start Date"  '||
          '     , TO_CHAR( period_close_date, ''DD-MON-YYYY'' ) "Close Date"  '||
          '     , TO_CHAR( schedule_close_date, ''DD-MON-YYYY'' ) "Scheduled |Close Date"  '||
          '     , open_flag "Open"  '||
          '     , description "Description"  '||
          '     , period_set_name "GL Period Set|Name"  '||
          '     , period_name "GL Period|Name"  '||
          '     , period_year "GL Period|Year"  '||
          '  FROM mtl_material_transactions_temp mmtt, mtl_parameters mp  '||
          '     , org_acct_periods oap  '||
          ' WHERE NVL( mmtt.transaction_status,1 ) != 2  '||
          '   AND mmtt.organization_id=mp.organization_id(+)  '||
          '   AND mmtt.acct_period_id=oap.acct_period_id(+)';

Line: 135


sqltxt := 'SELECT ';

Line: 162


          ',TO_CHAR( mmtt.last_update_date, ''DD-MON-RR HH24:MI'') "Last Updated"  '||
          ',mif.item_number  '||
          '||'' (''||mmtt.inventory_item_id||'')'' "Item (Id)"  '||
          ',item_description "Item Description"  '||
          ',revision "Rev"   '||
          ',lot_number "Lot" '||
          ',serial_number "Serial|Number"  '||
          ',mmtt.cost_group_id "Cost|Group Id"  '||
          ',mmtt.subinventory_code "Subinv"  '||
          ',mil.description  '||
          '||'' (''||mmtt.locator_id||'') '' "Stock|Locator (Id)"  '||
          ',transfer_subinventory "Transfer|Subinv"  '||
          ',transfer_to_location "Transfer|Location"  '||
          ',transaction_quantity "Txn Qty"   '||
          ',primary_quantity "Primary|Qty"   '||
          ',transaction_uom "Txn|UoM"  '||
          ',mtt.transaction_type_name  '||
          '||'' (''||mmtt.transaction_type_id||'')'' "Txn Type (Id)"  '||
          ',ml.meaning  '||
          '||'' (''||mmtt.transaction_action_id||'')'' "Txn Action Type (Id)"  '||
          'FROM mtl_material_transactions_temp mmtt  '||
          ',mtl_transaction_types mtt  '||
          ',mtl_item_flexfields mif  '||
          ',mfg_lookups ml  '||
          ',mtl_item_locations_kfv mil';

Line: 224


sqltxt := 'SELECT mmtt.transaction_temp_id "Txn|Temp Id"  '||
          '    , mmtt.transaction_quantity "Txn Qty"  '||
          '    , mmtt.primary_quantity "Primary|Txn Qty"  '||
          '    , mmtt.transaction_uom "Txn UoM"  '||
          '    , mmtt.subinventory_code "SubInventory"  '||
          '    , mmtt.error_code "Error|Code"  '||
          '    , mmtt.error_explanation "Error|Explanation"  '||
          '    , mmtt.item_description "Item Description"  '||
          ' FROM mtl_material_transactions_temp mmtt  '||
          'WHERE ( ABS( transaction_quantity )*100000 <= 1  '||
          '        OR ABS( primary_quantity )* 100000 <= 1 )';

Line: 254


sqltxt := 'SELECT mmtt.transaction_temp_id "Txn|Temp Id"  '||
          '     , mmtt.item_description "Item Description"  '||
          '     , mmtt.inventory_item_id "Inventory|Item Id"  '||
          '  FROM mtl_material_transactions_temp mmtt  '||
          ' WHERE NVL( mmtt.transaction_status, 1 ) != 2  '||
          '   AND mmtt.item_description like ''% '' ';

Line: 277


sqltxt := 'SELECT mif.item_number "Item"  '||
          '    , moq.inventory_item_id "Item Id"  '||
          '    , SUM( moq.transaction_quantity ) "Txn Qty"  '||
          '    , moq.subinventory_code "Subinv"  '||
          '    , moq.locator_id "Locator Id"  '||
          '    , mil.concatenated_segments "Locator"  '||
          '    , mil.description "Locator Desc"  '||
          '    , moq.revision "Revision"  '||
          '    , moq.lot_number "Lot Number"  '||
          ' FROM mtl_onhand_quantities_detail moq , mtl_item_flexfields mif  '||
          '    , mtl_item_locations_kfv mil  '||
          'WHERE moq.inventory_item_id = mif.inventory_item_id(+)  '||
          '  AND moq.organization_id = mif.organization_id(+)  '||
          '  AND moq.organization_id = mil.organization_id(+)  '||
          '  AND moq.locator_id = mil.inventory_location_id(+)  ';

Line: 297


          '      IN ( SELECT DISTINCT mmtt.inventory_item_id  '||
          '             FROM mtl_material_transactions_temp mmtt  '||
          '            WHERE  NVL( mmtt.transaction_status, 1 ) !=2 ';

Line: 327


sqltxt := 'SELECT DISTINCT( msi.secondary_inventory_name ) "Name"   '||
          '    , msi.description "Description"   '||
          '    , msi.disable_date "Disable|Date"   '||
          '    , DECODE( msi.reservable_type, 1, ''Yes'', 2, ''No'',   '||
          '              msi.reservable_type) "Reservable|Type"   '||
          '    , DECODE( msi.locator_type  '||
          '                  ,1, ''None''   '||
          '                  ,2, ''Prespecified''   '||
          '                  ,3, ''Dynamic''   '||
          '                  ,4, ''SubInv Level''   '||
          '                  ,5, ''Item Level'', msi.locator_type)  '||
          '       || '' (''||msi.locator_type||'')'' "Locator|Control"  '||
          '    , DECODE( msi.availability_type  '||
          '                ,1, ''Nettable''   '||
          '                ,2, ''Non-Nettable'',msi.availability_type ) "Availability|Type"  '||
          '    , DECODE( msi.inventory_atp_code, 1, ''Included in atp''   '||
          '                                    , 2, ''Not included in atp''   '||
          '            , msi.inventory_atp_code ) "Include|in ATP"   '||
          '    , DECODE( msi.asset_inventory, 1, ''Yes'', 2, ''No'',   '||
          '              msi.asset_inventory ) "Asset|Inventory"   '||
          '    , DECODE( msi.quantity_tracked, 1, ''Yes'', 2, ''No'',   '||
          '              msi.quantity_tracked ) "Quantity|Tracked"   '||
          ' FROM mtl_secondary_inventories msi   '||
          'WHERE (msi.organization_id, msi.secondary_inventory_name ) IN   '||
          '     ( SELECT mmtt.organization_id, NVL(mmtt.subinventory_code,-99) '||
          '       FROM mtl_material_transactions_temp mmtt  '||
          '       WHERE NVL( mmtt.transaction_status, 1 ) != 2  ';

Line: 363


          '       SELECT NVL( mmtt.transfer_organization, mmtt.organization_id )  '||
          '        ,NVL( mmtt.transfer_subinventory,-99 )'||
          '        FROM mtl_material_transactions_temp mmtt  '||
          '       WHERE NVL( mmtt.transaction_status, 1 ) != 2  ';

Line: 381


sqltxt := 'SELECT mp.organization_code "Organization|Code"  '||
          '    , mmtt.organization_id "Organization|ID"  '||
          '    , oap.period_name "Period Name"  '||
          '    , mmtt.acct_period_id "Period ID"  '||
          '    , mmtt.process_flag "Process Flag"  '||
          '    , mmtt.lock_flag "Lock Flag"  '||
          '    , COUNT(*) "Count"  '||
          ' FROM mtl_material_transactions_temp mmtt, mtl_parameters mp  '||
          '    , org_acct_periods oap  '||
          'WHERE NVL( mmtt.transaction_status,1 ) != 2  '||
          '  AND mmtt.organization_id=mp.organization_id(+)  '||
          '  AND mmtt.acct_period_id=oap.acct_period_id(+) ';

Line: 411


sqltxt :='SELECT process_flag "Process Flag"  '||
          '    , COUNT(*) "Count"  '||
          ' FROM mtl_material_transactions_temp  '||
          'WHERE NVL( transaction_status, 1 ) != 2';

Line: 430


sqltxt := 'SELECT lock_flag "Lock Flag" , COUNT(*) "Count"  '||
          ' FROM mtl_material_transactions_temp  '||
          'WHERE NVL( transaction_status, 1 ) != 2';

Line: 448


sqltxt := 'SELECT process_flag "Process Flag"  '||
          '    , DECODE( NVL( transaction_status, ''-99'' )  '||
          '                        , ''1'', ''Pending''  '||
          '                        , ''2'', ''Allocated''  '||
          '                        , ''-99'', ''Pending''  '||
          '                  , transaction_status )  '||
          '        || '' ('' ||NVL( TO_CHAR( transaction_status ), ''null'')  '||
          '        || '' )'' "Transaction Status"  '||
          '    , COUNT(*) "Count"  '||
          ' FROM mtl_material_transactions_temp ';

Line: 473


sqltxt := 'SELECT process_flag "Process Flag"  '||
          '    , ml.meaning || '' ('' || mmtt.transaction_mode || '')''  '||
          '        "Transaction Mode"  '||
          '    , COUNT(*) "Count"  '||
          ' FROM mtl_material_transactions_temp mmtt, mfg_lookups ml  '||
          'WHERE NVL( mmtt.transaction_status, 1 ) != 2  '||
          '  AND ml.lookup_type(+) = ''MTL_TRANSACTION_MODE''  '||
          '  AND mmtt.transaction_mode = ml.lookup_code(+)';

Line: 497


sqltxt :='SELECT mmtt.process_flag "Process Flag"  '||
'     , tt.transaction_type_name  '||
'         ||'' ( ''||mmtt.transaction_type_id||'' )''  '||
'         "Txn Type (Id)"  '||
'     , ml.meaning  '||
'       ||'' ( ''||mmtt.transaction_action_id||'' )''  '||
'      "Txn Action (Id)"  '||
'    , COUNT(*) "Count"  '||
' FROM mtl_material_transactions_temp mmtt  '||
'    , mtl_transaction_types tt  '||
'    , mfg_lookups ml  '||
'WHERE NVL( mmtt.transaction_status, 1 ) != 2  '||
'  AND mmtt.transaction_type_id = tt.transaction_type_id(+)  '||
'  AND mmtt.transaction_action_id = ml.lookup_code  '||
'  AND ml.lookup_type = ''MTL_TRANSACTION_ACTION'' ';

Line: 533


sqltxt := 'SELECT COUNT(*)  '||
          '  FROM mtl_material_transactions_temp mmtt, mtl_system_items_b msib  '||
          ' WHERE NVL( mmtt.transaction_status, 1 ) != 2  '||
          '   AND mmtt.organization_id = msib.organization_id  '||
          '   AND mmtt.inventory_item_id = msib.inventory_item_id  '||
          '   AND msib.serial_number_control_code > 1 ';

Line: 552


sqltxt := 'SELECT mmtt.transaction_temp_id "Txn|Temp Id"  '||
          '    , mmtt.transaction_quantity "Txn Qty"  '||
          '    , mmtt.primary_quantity "Primary|Txn Qty"  '||
          '    , mmtt.transaction_uom "Txn UoM"  '||
          '    , msnt.fm_serial_number "From|Serial#"  '||
          '    , msnt.to_serial_number "To|Serial#"  '||
          '    , msnt.serial_prefix "Serial|Prefix"  '||
          '    , msnt.error_code "Error Code"  '||
          '    , msnt.parent_serial_number "Parent|Serial#"  '||
          '    , msnt.group_header_id "Group|Header Id"  '||
          '    , mmtt.item_description "Item Description"  '||
          '    , mmtt.serial_number "Serial#"  '||
          '    , DECODE( mmtt.item_serial_control_code,  '||
          '                  1, ''No serial number control'',  '||
          '                  2, ''Predefined serial numbers'',  '||
          '                  5, ''Dynamic entry at inventory receipt'',  '||
          '                  6, ''Dynamic entry at sales order issue'',  '||
          '              mmtt.item_serial_control_code )  '||
          '      ||'' (''||mmtt.item_serial_control_code||'')'' "Item Serial Control"  '||
          '    , mmtt.next_serial_number "Next Serial#"  '||
          '    , mmtt.serial_alpha_prefix "Serial|Alpha Prefix"  '||
          'FROM mtl_material_transactions_temp mmtt  '||
          '   , mtl_serial_numbers_temp msnt  '||
          'WHERE NVL( mmtt.transaction_status, 1 ) != 2  ';

Line: 589


sqltxt := 'SELECT mif.item_number  '||
          '       ||'' (''|| msn.inventory_item_id||'')'' "Item (Id)"  '||
          '    , msn.serial_number "Serial|Number"  '||
          '    , ml.meaning  '||
          '       ||'' (''||msn.current_status||'')'' "Current|Status"  '||
          '    , msn.group_mark_id "Group|Mark Id"  '||
          '    , msn.line_mark_id "Line|Mark Id"  '||
          '    , msn.lot_line_mark_id "Lot Line|Mark Id"  '||
          '    , mp.organization_Code "Current|Org Code"  '||
          '    , msn.current_organization_id "Current|Org Id"  '||
          '    , msn.current_subinventory_code "Current|Subinventory"  '||
          '    , msn.current_locator_id "Current|Locator Id"  '||
          '    , mil.concatenated_segments "Current|Locator"  '||
          '    , mil.description "Current|Locator Desc"  '||
          ' FROM mtl_serial_numbers msn, mtl_item_flexfields mif  '||
          '    , mtl_parameters mp, mtl_item_locations_kfv mil  '||
          '    , mfg_lookups ml  '||
          'WHERE msn.inventory_item_id = mif.inventory_item_id  '||
          '  AND msn.current_organization_id = mif.organization_id  '||
          '  AND msn.current_organization_id = mp.organization_id(+)  '||
          '  AND msn.current_locator_id = mil.inventory_location_id(+)  '||
          '  AND msn.current_organization_id = mil.organization_id(+)  '||
          '  AND msn.current_status = ml.lookup_code(+)  '||
          '  AND ''SERIAL_NUM_STATUS'' = ml.lookup_type(+)  '||
          '  AND msn.inventory_item_id IN  '||
          '     ( SELECT DISTINCT( inventory_item_id )  '||
          '         FROM mtl_material_transactions_temp mmtt  '||
          '        WHERE NVL( mmtt.transaction_status,1 ) != 2';

Line: 635


sqltxt := 'SELECT COUNT(*)  '||
          '  FROM mtl_material_transactions_temp mmtt, mtl_system_items_b msib  '||
          ' WHERE NVL( mmtt.transaction_status, 1 ) != 2  '||
          '   AND mmtt.organization_id = msib.organization_id  '||
          '   AND mmtt.inventory_item_id = msib.inventory_item_id  '||
          '   AND msib.lot_control_code = 2 ';

Line: 653


sqltxt := 'SELECT mmtt.transaction_temp_id "Txn|Temp Id"  '||
          '     , mmtt.transaction_quantity "Txn Qty"  '||
          '     , mmtt.primary_quantity "Primary|Txn Qty"  '||
          '     , mmtt.transaction_uom "Txn UoM"  '||
          '     , mtlt.lot_number "Lot|Number"  '||
          '     , mtlt.lot_expiration_date "Lot Expiration|Date"  '||
          '     , mtlt.error_code "Lot Error Code"  '||
          '     , mtlt.serial_transaction_temp_id "Serial Txn|Temp Id"  '||
          '     , mmtt.item_description "Item|Description"  '||
          '  FROM mtl_material_transactions_temp mmtt  '||
          '     , mtl_transaction_lots_temp mtlt  '||
          ' WHERE NVL( mmtt.transaction_status, 1 ) != 2  '||
          '   AND mmtt.transaction_temp_id = mtlt.transaction_temp_id ';

Line: 684


sqltxt := 'SELECT COUNT(*)  '||
          ' FROM mtl_material_transactions_temp mmtt, mtl_system_items_b msib  '||
          'WHERE mmtt.organization_id = msib.organization_id  '||
          '  AND mmtt.inventory_item_id = msib.inventory_item_id  '||
          '  AND NVL( mmtt.transaction_status, 1 ) != 2  '||
          '  AND msib.revision_qty_control_code = 2 ';

Line: 702


sqltxt := 'SELECT mif.item_number "Item|Number"  '||
          '    , mir.inventory_item_id "Item Id"  '||
          '    , mir.revision "Revision"  '||
          '    , mir.change_notice "Change Notice"  '||
          '    , mir.ecn_initiation_date "ECN Initiation|Date"  '||
          '    , mir.implementation_date "Implementation|Date"  '||
          '    , mir.effectivity_date "Effectivity|Date"  '||
          ' FROM mtl_item_revisions mir, mtl_item_flexfields mif  '||
          'WHERE mir.organization_id = mif.organization_id  '||
          '  AND mir.inventory_item_id = mif.inventory_item_id(+)  '||
          '  AND mif.revision_qty_control_code = ''2''  ';

Line: 717


          '      ( SELECT DISTINCT( inventory_item_id )  '||
          '          FROM mtl_material_transactions_temp mmtt  '||
          '         WHERE NVL( mmtt.transaction_status, 1 ) != 2    ';

Line: 739


sqltxt := 'SELECT DISTINCT mif.item_number "Item Number"  '||
          '     ,mmtt.inventory_item_id "Item Id"   '||
          '     ,primary_uom_code "Primary|UoM"   '||
          '     ,mif.inventory_item_flag "Inventory|Item Flag"   '||
          '     ,mif.stock_enabled_flag "Stock|Flag"   '||
          '     ,mif.mtl_transactions_enabled_flag "Transactable|Flag"   '||
          '     ,mif.costing_enabled_flag "Costing|Flag"   '||
          '     ,mif.inventory_asset_flag "Inventory|Asset Flag"   '||
          '     ,DECODE( mif.lot_control_code, 1, ''N'' , 2, ''Y''   '||
          '            , mif.lot_control_code ) "Lot|Control"   '||
          '     ,ml.meaning||'' (''||mif.serial_number_control_code||'')'' "Serial|Control"  '||
          '     ,DECODE( TO_CHAR(mif.revision_qty_control_code)  '||
          '                           , ''1'', ''No''   '||
          '                           , ''2'', ''Yes''   '||
          '                ,mif.revision_qty_control_code ) "Revision|Control"  '||
          '     ,DECODE( TO_CHAR(mif.location_control_code)  '||
          '                          , ''1'', ''None''  '||
          '                          , ''2'', ''Prespecified''  '||
          '                          , ''3'', ''Dynamic''  '||
          '                          , ''4'', ''Determine at Subinv Level''  '||
          '                          , ''5'', ''Determine at Item Level''  '||
          '                   , mif.location_control_code )  '||
          '       || '' (''||mif.location_control_code||'')'' "Location|Control"  '||
          '     ,DECODE( mif.restrict_subinventories_code, 1, ''Y''  '||
          '                                              , 2, ''N''  '||
          '             ,mif.restrict_subinventories_code ) "Restricted|Subinvs"  '||
          '     ,DECODE( mif.restrict_locators_code, 1, ''Y'', 2, ''N''  '||
          '             ,mif.restrict_locators_code ) "Restricted|Locators"  '||
          ' FROM mtl_material_transactions_temp mmtt  '||
          '    , mtl_item_flexfields mif , mfg_lookups ml  '||
          'WHERE mmtt.organization_id = mif.organization_id  '||
          '  AND mmtt.inventory_item_id = mif.inventory_item_id(+)  '||
          '  AND mif.serial_number_control_code = ml.lookup_code(+)  '||
          '  AND ''MTL_SERIAL_NUMBER'' = ml.lookup_type(+)  ';

Line: 791


sqltxt := 'SELECT process_flag "Process Flag"  '||
          '    , error_code "Error Code"  '||
          '    , error_explanation "Error Explanation"  '||
          '    , COUNT(*) "Count"  '||
          ' FROM mtl_material_transactions_temp  '||
          'WHERE NVL( transaction_status, 1 ) != 2';