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_MTI SQL Statements

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

Line: 19


 sqltxt    VARCHAR2(9999);  -- SQL select statement

Line: 35


JTF_DIAGNOSTIC_COREAPI.insert_style_sheet;

Line: 59


sqltxt := 'SELECT transaction_header_id "Txn Header Id"  '||
          '    , mti.transaction_interface_id "Txn IntFace|Id"  '||
          '    , mif.item_number ||'' (''|| mti.inventory_item_id ||'')'' "Item (Id)"  '||
          '    , item_segment1 "Item Segment1"  '||
          '    , subinventory_code "Subinv"  '||
          '    , loc_segment1 ||'' ''|| loc_segment2 ||'' ''|| loc_segment3 "Loc_Segment  1-3"  '||
          '    , locator_id "Locator Id"  '||
          '    , revision "Rev"  '||
          '    , mti.transaction_quantity "Txn Qty"  '||
          '    , mti.primary_quantity "Primary Qty"  '||
          '    , transaction_uom "Txn UoM"  '||
          '    , transaction_cost "Txn Cost"  '||
          '    , transaction_type_name ||'' (''|| transaction_type_id ||'')'' "Txn Type (Id)"  '||
          '    , transaction_action_name ||'' (''|| transaction_action_id ||'')'' "Txn Action (Id)"  '||
          '    , transaction_source_type_name ||'' (''|| transaction_source_type_id ||'')'' "Txn Source Type (Id)"  '||
          '    , transaction_source_name ||'' (''|| transaction_source_id ||'')'' "Txn Source (Id)"  '||
          '    , trx_source_line_id "Txn Source|Line Id"  '||
          '    , cost_group_id "Cost|Group Id"  '||
          '    , TO_CHAR( transaction_date, ''DD-MON-RR HH24:MI'' ) "Txn Date"  '||
          '    , transaction_reference "Txn Reference"  '||
          '    , transfer_subinventory "Transfer|Subinv"  '||
          '    , transfer_organization_code ||'' (''|| transfer_organization ||'')'' "Transfer|Organization"  '||
          '    , mti.request_id "Request Id"  '||
          '    , mti.source_code "Source|Code"  '||
          '    , mti.source_line_id "Source Line Id"  '||
          '    , source_header_id "Source Header Id"  '||
          '    , mti.distribution_account_id "Distribution Account Id"  '||
          '    , mti.process_flag_desc ||'' ('' || mti.process_flag || '')'' "Process Flag"  '||
          '    , transaction_mode_desc ||'' ('' || transaction_mode || '')'' "Txn Mode"  '||
          '    , lock_flag_desc ||'' ('' || lock_flag || '')'' "Lock Flag"  '||
          '    , TO_CHAR( mti.last_update_date, ''DD-MON-RR HH24:MI'' ) "Last updated"  '||
          '    , mti.error_code "Error Code"  '||
          '    , error_explanation "Error Explanation"  '||
          ' FROM mtl_transactions_interface_v mti  '||
          '    , mtl_item_flexfields mif  '||
          'WHERE mti.organization_id = mif.organization_id(+)  '||
          '  AND mti.inventory_item_id = mif.inventory_item_id(+) ';

Line: 107


sqltxt := 'select * from ('||sqltxt||') WHERE ROWNUM <= '||row_limit;

Line: 115


sqltxt := 'SELECT transaction_interface_id "Txn|Interface Id"  '||
          '    , transaction_quantity "Txn Qty"  '||
          '    , primary_quantity "Primary|Txn Qty"  '||
          '    , transaction_uom "Txn UoM"  '||
          '    , subinventory_code "Subinventory"  '||
          '    , error_code "Error|Code"  '||
          '    , error_explanation "Error|Explanation"  '||
          ' FROM mtl_transactions_interface  '||
          'WHERE ( ABS( transaction_quantity ) < 0.00001  '||
          '        OR ABS( primary_quantity ) < 0.00001 )';

Line: 143


sqltxt := 'SELECT transaction_interface_id  '||
          '     , item_segment1  '||
          '     , inventory_item_id  '||
          '  FROM mtl_transactions_interface  '||
          ' WHERE ( item_segment1 like ''% '' OR item_segment2 like ''% ''  '||
          '         OR item_segment3 like ''% '' OR item_segment3 like ''% '' )';

Line: 153


sqltxt := 'select * from ('||sqltxt||') WHERE ROWNUM <= '||row_limit;

Line: 161


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: 180


          '      IN ( SELECT DISTINCT mti.inventory_item_id  '||
          '             FROM mtl_transactions_interface mti  ';

Line: 206


sqltxt := 'SELECT DISTINCT( msi.secondary_inventory_name ) "Name"   '||
          '  , msi.description "Description"   '||
          '  , TO_CHAR( msi.disable_date, ''DD-MON-YYYY'' ) "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''  '||
          '                                  , 2, ''Not included''  '||
          '          , 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"   '||
          '  , default_cost_group_id "Default|Cost Group Id" '||
          '  ,  DECODE( NVL( subinventory_type, 1 ), 1, ''Storage'', 2,''Receiving'', subinventory_type ) "Type"  '||
          'FROM mtl_secondary_inventories msi   '||
          'WHERE (msi.organization_id, msi.secondary_inventory_name ) IN  '||
          '   ( SELECT mti.organization_id, NVL(mti.subinventory_code,-99)'||
          '          FROM mtl_transactions_interface mti  ';

Line: 243


          'SELECT NVL( mti.transfer_organization, mti.organization_id )  '||
          '      ,NVL( mti.transfer_subinventory,-99 ) '||
          'FROM mtl_transactions_interface mti  ';

Line: 257


sqltxt := 'select * from ('||sqltxt||') WHERE ROWNUM <= '||row_limit;

Line: 264


sqltxt := 'SELECT mp.organization_code "Organization|Code"  '||
          '    , mti.organization_id "Organization|Id"  '||
          '    , DECODE( process_flag, 1, ''Ready''  '||
          '                          , 2, ''Not Ready''  '||
          '                          , 3, ''Error''  '||
          '            , process_flag )   '||
          '        || '' ('' ||process_flag|| '')'' "Process Flag"             '||
          '    , DECODE( NVL( lock_flag, 2) , 1,''Locked''  '||
          '                                 , 2, ''Not Locked'', lock_flag)  '||
          '      || '' ('' || lock_flag || '')'' "Lock Flag"  '||
          '    , COUNT(*) "Count"  '||
          ' FROM mtl_transactions_interface mti  '||
          '    , mtl_parameters mp  '||
          'WHERE mti.organization_id = mp.organization_id(+)  ';

Line: 288


sqltxt := 'select * from ('||sqltxt||') WHERE ROWNUM <= '||row_limit;

Line: 295


sqltxt := 'SELECT COUNT(*)  '||
          '  FROM mtl_transactions_interface  ';

Line: 312


sqltxt := 'SELECT COUNT(*) "Count"  '||
          '   , DECODE( process_flag, 1, ''Ready''  '||
          '                         , 2, ''Not Ready''  '||
          '                         , 3, ''Error''  '||
          '           , process_flag )   '||
          '   || '' ('' ||process_flag|| '')'' "Process Flag" '||
          'FROM mtl_transactions_interface  ';

Line: 330


sqltxt := 'SELECT COUNT(*) "Count"  '||
          '    , DECODE( NVL( lock_flag, 2) , ''1'',''Locked'', 2, ''Not Locked'', lock_flag)  '||
          '      || '' ('' || lock_flag || '')'' "Lock Flag"  '||
          ' FROM mtl_transactions_interface mti  ';

Line: 345


sqltxt := 'SELECT COUNT(*) "Count"  '||
          '    , transaction_mode_desc || '' ('' ||transaction_mode|| '')'' "Transaction Mode"  '||
          ' FROM mtl_transactions_interface_v  ';

Line: 359


sqltxt := 'SELECT COUNT(*) "Count"  '||
          '     , transaction_type_name ||'' ( ''||transaction_type_id||'' )'' "Txn Type (Id)"  '||
          ' FROM mtl_transactions_interface_v mti  ';

Line: 373


sqltxt := 'SELECT msni.transaction_interface_id "Txn|Interface Id"  '||
          '    , mti.inventory_item_id "Item Id"  '||
          '    , msni.fm_serial_number "From|Serial#"  '||
          '    , msni.to_serial_number "To|Serial#"  '||
          '    , msni.error_code "Error Code"  '||
          '    , msni.parent_serial_number "Parent|Serial#"  '||
          ' FROM mtl_transactions_interface mti  '||
          '    , mtl_serial_numbers_interface msni  '||
          'WHERE NVL( mti.transaction_interface_id, -999 ) = msni.transaction_interface_id ';

Line: 390


sqltxt := 'select * from ('||sqltxt||') WHERE ROWNUM <= '||row_limit;

Line: 397


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_transactions_interface mti  ';

Line: 434


sqltxt := 'select * from ('||sqltxt||') WHERE ROWNUM <= '||row_limit;

Line: 441


sqltxt := 'SELECT COUNT(*)  '||
          '  FROM mtl_transactions_interface mti  '||
          '     , mtl_system_items_b msib  '||
          ' WHERE mti.organization_id = msib.organization_id  '||
          '   AND mti.inventory_item_id = msib.inventory_item_id  '||
          '   AND msib.serial_number_control_code > 1 ';

Line: 456


sqltxt := 'SELECT COUNT(*)  '||
          '  FROM mtl_transactions_interface mti  '||
          '     , mtl_system_items_b msib  '||
          ' WHERE mti.organization_id = msib.organization_id  '||
          '   AND mti.inventory_item_id = msib.inventory_item_id  '||
          '   AND msib.lot_control_code = 2 ';

Line: 471


sqltxt := 'SELECT mtli.transaction_interface_id "Txn|Interface Id"  '||
          '   , mti.inventory_item_id "Item Id"  '||
          '   , mtli.transaction_quantity "Txn Qty"  '||
          '   , mtli.primary_quantity "Primary|Txn Qty"  '||
          '   , mtli.lot_number "Lot|Number"  '||
          '   , mtli.lot_expiration_date "Lot Expiration|Date"  '||
          '   , mtli.error_code "Lot Error Code"  '||
          '   , mtli.serial_transaction_temp_id "Serial Txn|Temp Id"  '||
          '   , mtli.process_flag "Process|Flag"  '||
          'FROM mtl_transactions_interface mti  '||
          '   , mtl_transaction_lots_interface mtli  '||
          'WHERE NVL( mti.transaction_interface_id, -999 ) = mtli.transaction_interface_id ';

Line: 497


sqltxt := 'SELECT COUNT(*)  '||
          ' FROM mtl_transactions_interface mti  '||
          '    , mtl_system_items_b msib  '||
          'WHERE mti.organization_id = msib.organization_id  '||
          '  AND mti.inventory_item_id = msib.inventory_item_id  '||
          '  AND msib.revision_qty_control_code = 2 ';

Line: 515


sqltxt := 'SELECT mif.item_number "Item|Number"  '||
          '    , mir.inventory_item_id "Item Id"  '||
          '    , mir.revision "Revision"  '||
          '    , mir.change_notice "Change Notice"  '||
          '    , TO_CHAR( mir.ecn_initiation_date, ''DD-MON-RR'' ) "ECN Initiation|Date"  '||
          '    , TO_CHAR( mir.implementation_date, ''DD-MON-RR'' ) "Implementation|Date"  '||
          '    , TO_CHAR( mir.effectivity_date, ''DD-MON-RR'' ) "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: 530


          '      ( SELECT DISTINCT( inventory_item_id )  '||
          '          FROM mtl_transactions_interface mti  ';

Line: 549


sqltxt := 'SELECT DISTINCT mif.item_number "Item Number"  '||
          '     ,mti.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 )  '||
          '       || '' (''||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 )  '||
          '       || '' (''||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, ''Yes''  '||
          '                                              , 2, ''No''  '||
          '             ,mif.restrict_subinventories_code ) "Restricted|Subinvs"  '||
          '     ,DECODE( mif.restrict_locators_code, 1, ''Yes'', 2, ''No''  '||
          '             ,mif.restrict_locators_code )  '||
          '       || '' (''||mif.restrict_locators_code||'')'' "Restricted|Locators"  '||
          ' FROM mtl_transactions_interface mti  '||
          '    , mtl_item_flexfields mif  '||
          '    , mfg_lookups ml  '||
          'WHERE mti.organization_id = mif.organization_id  '||
          '  AND mti.inventory_item_id = mif.inventory_item_id(+)  '||
          '  AND mif.serial_number_control_code = ml.lookup_code(+)  '||
          '  AND ''MTL_SERIAL_NUMBER'' = ml.lookup_type(+) ';

Line: 596


sqltxt := 'SELECT COUNT(*) "Count"  '||
          '    , error_code "Error Code"  '||
          '    , error_explanation "Error Explanation"  '||
          ' FROM mtl_transactions_interface  ';