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.WIP_DIAG_WOL_FLOW SQL Statements

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

Line: 8


 sqltxt    VARCHAR2(9999);  -- SQL select statement

Line: 21


JTF_DIAGNOSTIC_COREAPI.insert_style_sheet;

Line: 32


'select '||
'       mmt1.transaction_source_id, '||
'       mmt1.transaction_id, '||
'       mmt1.organization_id, '||
'       mmt1.completion_transaction_id, '||
'       mmt1.move_transaction_id, '||
'       nvl(mmt1.costed_flag, ''Y'') costed_flag, '||
'       decode(mmt1.transaction_action_id, '||
'  1, ''Issue'', '||
'  2, ''Subinv Xfr'', '||
'  3, ''Org Xfr'', '||
'  4, ''Cycle Count Adj'', '||
'  5, ''Plan Xfr'', '||
'  21, ''Intransit Shpmt'', '||
'  24, ''Cost Update'', '||
'  27, ''Receipt'', '||
'  28, ''Stg Xfr'', '||
'  30, ''Wip scrap'', '||
'  31, ''Assy Complete'', '||
'  32, ''Assy return'', '||
'  33, ''-ve CompIssue'', '||
'  34, ''-ve CompReturn'', '||
'  40, ''Inv Lot Split'', '||
'  41, ''Inv Lot Merge'', '||
'  42, ''Inv Lot Translate'', '||
'  42, ''Inv Lot Translate'', '||
'  transaction_action_id) txn_action_meaning, '||
'       mmt1.error_code, '||
'       substrb(mmt1.error_explanation,1,50) err_explain '||
' from   mtl_material_transactions mmt1 '||
' where  mmt1.transaction_action_id in (1, 27, 33, 34) '||
' and    mmt1.transaction_source_type_id = 5 '||
' and    mmt1.flow_schedule = ''Y'' '||
' and    mmt1.costed_flag  = ''E'' '||
' and    mmt1.completion_transaction_id is not null '||
' and    mmt1.transaction_source_id is not null '||
  where_clause ||
' and    exists (select 1 '||
'                from   mtl_material_transactions mmt2 '||
'                where  mmt2.transaction_action_id in (30, 31, 32) '||
'                and    mmt2.transaction_source_type_id = 5 '||
'                and    mmt2.completion_transaction_id = '||
'                            mmt1.completion_transaction_id '||
'                and    mmt2.flow_schedule = ''Y'' '||
'                and    mmt2.costed_flag in (''N'', ''E'') '||
                        where_clause ||
'               ) '||
' and    exists (select 1 '||
'    from   wip_flow_schedules wfs '||
'  where  wfs.wip_entity_id = mmt1.transaction_source_id '||
'  and    wfs.organization_id = mmt1.organization_id) '||
' order by transaction_source_id, transaction_action_id';

Line: 111


'select '||
'       mmt1.transaction_source_id, '||
'       mmt1.transaction_id, '||
'       mmt1.organization_id, '||
'       mmt1.completion_transaction_id, '||
'       mmt1.move_transaction_id, '||
'       nvl(mmt1.costed_flag, ''Y'') costed_flag, '||
'       decode(mmt1.transaction_action_id, '||
'  1, ''Issue'', '||
'  2, ''Subinv Xfr'', '||
'  3, ''Org Xfr'', '||
'  4, ''Cycle Count Adj'', '||
'  5, ''Plan Xfr'', '||
'  21, ''Intransit Shpmt'', '||
'  24, ''Cost Update'', '||
'  27, ''Receipt'', '||
'  28, ''Stg Xfr'', '||
'  30, ''Wip scrap'', '||
'  31, ''Assy Complete'', '||
'  32, ''Assy return'', '||
'  33, ''-ve CompIssue'', '||
'  34, ''-ve CompReturn'', '||
'  40, ''Inv Lot Split'', '||
'  41, ''Inv Lot Merge'', '||
'  42, ''Inv Lot Translate'', '||
'  42, ''Inv Lot Translate'', '||
'  transaction_action_id) txn_action_meaning, '||
'       mmt1.error_code, '||
'       substrb(mmt1.error_explanation,1,50) err_explain '||
' from   mtl_material_transactions mmt1 '||
' where  mmt1.transaction_action_id in (1, 27, 33, 34) '||
' and    mmt1.transaction_source_type_id = 5 '||
' and    mmt1.flow_schedule = ''Y'' '||
' and    mmt1.costed_flag = ''E'' '||
' and    mmt1.completion_transaction_id is not null '||
' and    mmt1.transaction_source_id is not null '||
  where_clause ||
' and    not exists (select 1 '||
'                    from   mtl_material_transactions mmt2 '||
'                    where  mmt2.transaction_action_id in (30, 31, 32) '||
'                    and    mmt2.transaction_source_type_id = 5 '||
'                    and    mmt2.completion_transaction_id = '||
'                           mmt1.completion_transaction_id '||
'                    and    mmt2.flow_schedule = ''Y'' '||
                     where_clause ||
'               ) '||
' order by transaction_source_id, transaction_action_id';

Line: 181


'select mmt.transaction_source_id, '||
'       mmt.transaction_id, '||
'       mmt.organization_id, '||
'       mmt.completion_transaction_id, '||
'       mmt.move_transaction_id, '||
'       nvl(mmt.costed_flag, ''Y''), '||
'       decode(mmt.transaction_action_id, '||
'  1, ''Issue'', '||
'  2, ''Subinv Xfr'', '||
'  3, ''Org Xfr'', '||
'  4, ''Cycle Count Adj'', '||
'  5, ''Plan Xfr'', '||
'  21, ''Intransit Shpmt'', '||
'  24, ''Cost Update'', '||
'  27, ''Receipt'', '||
'  28, ''Stg Xfr'', '||
'  30, ''Wip scrap'', '||
'  31, ''Assy Complete'', '||
'  32, ''Assy return'', '||
'  33, ''-ve CompIssue'', '||
'  34, ''-ve CompReturn'', '||
'  40, ''Inv Lot Split'', '||
'  41, ''Inv Lot Merge'', '||
'  42, ''Inv Lot Translate'', '||
'  42, ''Inv Lot Translate'', '||
'  transaction_action_id) txn_action_meaning, '||
'       mmt.error_code, '||
'       mmt.error_explanation '||
'from   mtl_material_transactions mmt '||
'where  mmt.transaction_action_id not in (30, 31, 32)  /* All Non parent transactions */ '||
'and    mmt.transaction_source_type_id = 5 /* WIP */'||
'and    mmt.flow_schedule = ''Y'' '||
'and    mmt.costed_flag  in (''N'', ''E'') '||
 where_clause ||
'and    exists ( select 1 '||
'                         from   mtl_material_transactions mmt1 '||
'                         where  mmt1.transaction_action_id in (30, 31, 32)  '||
'                                                   /* Parent Transactions */ '||
'                         and    mmt1.transaction_source_type_id = 5 /* WIP */ '||
'                         and    mmt1.flow_schedule = ''Y'' '||
'                         and    mmt1.costed_flag is null /* Parent is costed */ '||
'                         and    mmt1.completion_transaction_id = '||
'                                       mmt.completion_transaction_id        '||
                          where_clause ||
'                        ) '||
'order by transaction_source_id, transaction_action_id';

Line: 250


' select '||
'       mmt.transaction_source_id, '||
'       mmt.transaction_id, '||
'       mmt.organization_id, '||
'       mmt.completion_transaction_id, '||
'       mmt.move_transaction_id, '||
'       nvl(mmt.costed_flag, ''Y'') costed_flag, '||
'       decode(mmt.transaction_action_id, '||
'  1, ''Issue'', '||
'  2, ''Subinv Xfr'', '||
'  3, ''Org Xfr'', '||
'  4, ''Cycle Count Adj'', '||
'  5, ''Plan Xfr'', '||
'  21, ''Intransit Shpmt'', '||
'  24, ''Cost Update'', '||
'  27, ''Receipt'', '||
'  28, ''Stg Xfr'', '||
'  30, ''Wip scrap'', '||
'  31, ''Assy Complete'', '||
'  32, ''Assy return'', '||
'  33, ''-ve CompIssue'', '||
'  34, ''-ve CompReturn'', '||
'  40, ''Inv Lot Split'', '||
'  41, ''Inv Lot Merge'', '||
'  42, ''Inv Lot Translate'', '||
'  42, ''Inv Lot Translate'', '||
'  transaction_action_id) txn_action_meaning, '||
'       mmt.error_code, '||
'       substrb(mmt.error_explanation,1,50) err_explain '||
' from   mtl_material_transactions mmt '||
' where  mmt.transaction_action_id in (1,27,33,34,30,31,32) '||
' and    mmt.transaction_type_id in (17,35, 43,44,90,38,48) '||
' and    mmt.transaction_source_type_id = 5 '||
' and    mmt.flow_schedule = ''Y'' '||
' and    mmt.costed_flag  in (''N'', ''E'') '||
' and    mmt.transaction_source_id is not null '||
  where_clause ||
' and    not exists (select 1 '||
'                from   wip_flow_schedules wfs '||
'                where  wfs.wip_entity_id = mmt.transaction_source_id '||
'  and    wfs.organization_id = mmt.organization_id '||
'               ) '||
' order by transaction_source_id, transaction_action_id';

Line: 318


'  select '||
'        mmt.transaction_source_id, '||
'        mmt.transaction_id, '||
'        mmt.organization_id, '||
'        mmt.completion_transaction_id, '||
'        mmt.move_transaction_id, '||
'        nvl(mmt.costed_flag, ''Y'') costed_flag, '||
'        decode(mmt.transaction_action_id, '||
'                 1, ''Issue'', '||
'                 2, ''Subinv Xfr'', '||
'                 3, ''Org Xfr'', '||
'                 4, ''Cycle Count Adj'', '||
'                 5, ''Plan Xfr'', '||
'                 21, ''Intransit Shpmt'', '||
'                 24, ''Cost Update'', '||
'                 27, ''Receipt'', '||
'                 28, ''Stg Xfr'', '||
'                 30, ''Wip scrap'', '||
'                 31, ''Assy Complete'', '||
'                 32, ''Assy return'', '||
'                 33, ''-ve CompIssue'', '||
'                 34, ''-ve CompReturn'', '||
'                 40, ''Inv Lot Split'', '||
'                 41, ''Inv Lot Merge'', '||
'                 42, ''Inv Lot Translate'', '||
'                 42, ''Inv Lot Translate'', '||
'                       transaction_action_id) txn_action_meaning, '||
'        mmt.error_code, '||
'        substrb(mmt.error_explanation,1,50) err_explain '||
'  from   mtl_material_transactions mmt '||
'  where  mmt.transaction_action_id in (1, 27, 33, 34) '||
'    and  mmt.transaction_source_type_id = 5 '||
'    and  mmt.flow_schedule = ''Y'' '||
'    and  mmt.costed_flag  in (''N'', ''E'') '||
'    and  mmt.transaction_source_id is not NULL '||
'    and  mmt.completion_transaction_id is not null '||
     where_clause ||
'    and  exists (select 1 '||
'                   from   mtl_material_transactions mmt1 '||
'                  WHERE   mmt1.transaction_action_id in (30, 31, 32) '||
'                    and   mmt1.transaction_source_type_id = 5 '||
'                    and   mmt1.completion_transaction_id = mmt.completion_transaction_id '||
'                    and   mmt1.flow_schedule = ''Y'' '||
'                    and   mmt1.transaction_source_id <> mmt.transaction_source_id '||
'                ) '||
'  order by transaction_source_id, transaction_action_id ';

Line: 396


 sqltxt    VARCHAR2(9999);  -- SQL select statement

Line: 409


JTF_DIAGNOSTIC_COREAPI.insert_style_sheet;

Line: 420


'select wip_entity_id, '||
'       decode(to_char(completion_transaction_id), null, ''?'', '''') miss_comp, '||
'       organization_id '||
'       transaction_id, '||
'       completion_transaction_id,  '||
'       process_status '||
'from   wip_cost_txn_interface '||
'where  wip_entity_id in  '||
'  (select mmt1.transaction_source_id '||
'  from   mtl_material_transactions mmt1 '||
'  where  mmt1.transaction_source_type_id = 5 /* WIP */'||
'  and    mmt1.flow_schedule = ''Y'' '||
'  and    mmt1.costed_flag  in (''N'', ''E'') '||
'  and    mmt1.error_code = ''CST_INVALID_WIP'' '||
     where_clause ||
'  ) '||
   where_clause ||
'order by wip_entity_id, transaction_id';

Line: 462


' select wcti.wip_entity_name ScheduleNumber,  '||
'        wcti.wip_entity_id, '||
'        wcti.organization_id, '||
'        wcti.department_code, '||
'        wcti.operation_seq_num, '||
'        wcti.resource_seq_num, '||
'        wcti.resource_code, '||
'        wcti.transaction_id, '||
'        wcti.completion_transaction_id '||
'  from  wip_cost_txn_interface wcti '||
' where  entity_type = 4 -- Flow '||
  where_clause ||
'   and exists (select 1 '||
'     from mtl_material_transactions mmt '||
'     where mmt.transaction_action_id in (30, 31, 32) '||
'     and mmt.transaction_source_type_id = 5 /* WIP */'||
'     and mmt.flow_schedule = ''Y'' '||
'     and mmt.costed_flag is null /* Parent is costed */'||
'     and mmt.completion_transaction_id = wcti.completion_transaction_id '||
      where_clause || ' )';

Line: 497


' into WIP valuation acounts. Finally, delete the corresponding pending resource transaction(s) record.

');

Line: 516


 sqltxt    VARCHAR2(9999);  -- SQL select statement

Line: 530


JTF_DIAGNOSTIC_COREAPI.insert_style_sheet;

Line: 543


        'SELECT transaction_source_id, '||
        '       organization_id, '||
        '       transaction_interface_id, '||
        '       parent_id, '||
        '       completion_transaction_id '||
        'FROM   mtl_transactions_interface mti '||
        'WHERE  mti.transaction_source_type_id = 5 '||
        'AND    mti.transaction_action_id IN (1, 27, 33, 34) '||
        'AND    mti.flow_schedule = ''Y'' '||
          where_clause ||
        'AND    NOT EXISTS  '||
        '       (SELECT 1 FROM mtl_transactions_interface mti2 '||
        '        WHERE mti2.organization_id = mti.organization_id '||
        '        AND   mti2.transaction_source_type_id = 5 '||
        '        AND   mti2.completion_transaction_id = mti.completion_transaction_id '||
        '        AND   mti2.transaction_action_id NOT IN (1, 27, 33, 34) '||
        '        AND   mti2.transaction_interface_id = mti.parent_id' ||
                 where_clause || ' ) '||
        'AND    NOT EXISTS  '||
        '       (SELECT 1 FROM mtl_material_transactions mmt '||
        '        WHERE mmt.organization_id = mti.organization_id '||
        '        AND   mmt.transaction_source_type_id = 5 '||
        '        AND   mmt.completion_transaction_id = mti.completion_transaction_id '||
        '        AND   mmt.costed_flag IN (''N'',''E'')  '||
        '        AND   mmt.transaction_action_id NOT IN (1, 27, 33, 34)) ';

Line: 593


        'SELECT transaction_source_id, '||
        '       organization_id, '||
        '       transaction_interface_id, '||
        '       parent_id, '||
        '       completion_transaction_id '||
        'FROM   mtl_transactions_interface mti '||
        'WHERE  mti.transaction_source_type_id = 5 '||
        'AND    mti.transaction_action_id IN (1, 27, 33, 34) '||
        'AND    mti.flow_schedule = ''Y'' '||
          where_clause ||
        'AND    NOT EXISTS  '||
        '       (SELECT 1 FROM mtl_transactions_interface mti2 '||
        '        WHERE mti2.organization_id = mti.organization_id '||
        '        AND   mti2.transaction_source_type_id = 5 '||
        '        AND   mti2.completion_transaction_id = mti.completion_transaction_id '||
        '        AND   mti2.transaction_action_id NOT IN (1, 27, 33, 34) '||
        '        AND   mti2.transaction_interface_id = mti.parent_id' ||
                 where_clause || ' ) '||
        'AND     EXISTS  '||
        '       (SELECT 1 FROM mtl_material_transactions mmt '||
        '        WHERE mmt.organization_id = mti.organization_id '||
        '        AND   mmt.transaction_source_type_id = 5 '||
        '        AND   mmt.completion_transaction_id = mti.completion_transaction_id '||
        '        AND   mmt.costed_flag IN (''N'',''E'')  '||
        '        AND   mmt.transaction_action_id NOT IN (1, 27, 33, 34)) ';

Line: 656


 sqltxt    VARCHAR2(9999);  -- SQL select statement

Line: 669


JTF_DIAGNOSTIC_COREAPI.insert_style_sheet;

Line: 681


'SELECT organization_id,  '||
'       transaction_source_id, '||
'       transaction_type_id, '||
'       inventory_item_id,  '||
'       primary_quantity, '||
'       subinventory_code, '||
'       locator_id '||
'       completion_transaction_id, '||
'       Count(*) '||
'FROM mtl_material_transactions '||
'WHERE transaction_source_type_id = 5  '||
'AND   completion_transaction_id IS NOT null   '||
'AND   flow_schedule = ''Y'' '||
  where_clause ||
'HAVING Count(*) > 1 '||
'GROUP BY  organization_id,  '||
'          transaction_source_id, '||
'          transaction_type_id, '||
'          inventory_item_id,  '||
'          primary_quantity, '||
'          subinventory_code, '||
'          locator_id, '||
'          completion_transaction_id';