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

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

Line: 8


 sqltxt    VARCHAR2(9999);  -- SQL select statement

Line: 22


JTF_DIAGNOSTIC_COREAPI.insert_style_sheet;

Line: 38


'SELECT we.wip_entity_name Repetitive_Assembly,  '||
'       wl.line_code Repetitive_Line, '||
'       mmt.organization_id, '||
'       mmta.repetitive_schedule_id, '||
'       mmt.transaction_id, '||
'       mmt.transaction_source_id, '||
'       mmt.completion_transaction_id, '||
'       mmt.move_transaction_id, '||
'       mmt.costed_flag, '||
'       decode(mmt.transaction_action_id, '||
'             1, ''Issue'', '||
'            27, ''Receipt'', '||
'            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'', '||
'            mmt.transaction_action_id) txn_action_meaning, '||
'       mmt.error_code, '||
'       SubStrB(mmt.error_explanation, 1, 50) err_explain '||
'FROM   mtl_material_transactions mmt,  '||
'       wip_entities we, wip_lines wl, '||
'       mtl_material_txn_allocations mmta '||
'WHERE  mmt.transaction_source_type_id = 5 '||
'AND    mmt.costed_flag IN (''N'',''E'') '||
'AND    mmt.transaction_source_id = we.wip_entity_id '||
'AND    mmt.organization_id = we.organization_id '||
'AND    we.entity_type = 2 '||we_dyn_where_clause||
'AND    mmt.transaction_id = mmta.transaction_id '||
'AND    mmt.organization_id = mmta.organization_id '||
'AND    mmt.repetitive_line_id = wl.line_id '||
'AND    mmt.organization_id = wl.organization_id '||
'AND    NOT EXISTS '||
'       (SELECT ''x'' FROM wip_period_balances wpb '||
'        WHERE  WPB.WIP_ENTITY_ID = MMT.TRANSACTION_SOURCE_ID '||
'        AND    WPB.REPETITIVE_SCHEDULE_ID = MMTA.REPETITIVE_SCHEDULE_ID '||
'        AND    WPB.ORGANIZATION_ID = MMT.ORGANIZATION_ID '||
'        AND    WPB.ACCT_PERIOD_ID = mmt.ACCT_PERIOD_ID) '||
'ORDER BY mmt.organization_id, we.wip_entity_name, mmt.transaction_id';

Line: 101


'SELECT we.wip_entity_name Repetitive_Assembly,  '||
'       wl.line_code Repetitive_Line, '||
'       mmt.organization_id, '||
'       mmt.transaction_id, '||
'       mmt.transaction_source_id, '||
'       mmt.completion_transaction_id, ' ||
'       mmt.move_transaction_id, ' ||
'       mmt.organization_id, ' ||
'       mmt.costed_flag, ' ||
'       decode(mmt.transaction_action_id, ' ||
'                  1, ''Issue'', ' ||
'                  27, ''Receipt'', ' ||
'            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'', ' ||
'             mmt.transaction_action_id) txn_action_meaning, ' ||
'       mmt.error_code, ' ||
'       SubStrB(mmt.error_explanation, 1, 50) err_explain ' ||
'FROM   mtl_material_transactions mmt,  ' ||
'       wip_entities we, ' ||
'       wip_lines wl '||
'WHERE  mmt.transaction_action_id IN (1, 27, 33, 34) ' ||
'AND    mmt.transaction_source_type_id = 5 ' ||
'AND    mmt.costed_flag IN (''N'',''E'') ' ||
'AND    mmt.completion_transaction_id IS NULL  ' ||
'AND    mmt.move_transaction_id IS NULL  ' ||
'AND    mmt.transaction_source_id = we.wip_entity_id ' ||
'AND    mmt.organization_id = we.organization_id ' ||
'AND    we.entity_type = 2 ' ||we_dyn_where_clause||
'AND    mmt.repetitive_line_id = wl.line_id '||
'AND    we.organization_id = wl.organization_id '||
'AND    NOT EXISTS (SELECT 1 ' ||
'                   FROM   mtl_material_txn_allocations mmta ' ||
'                   WHERE  mmta.transaction_id = mmt.transaction_id ' ||
'                   AND    mmta.organization_id = mmt.organization_id) '||
'ORDER BY mmt.organization_id, we.wip_entity_name, mmt.transaction_id';

Line: 164


'SELECT we.wip_entity_name Repetitive_Assembly,  '||
'       wl.line_code Repetitive_Line, '||
'       mmt.organization_id, '||
'       mmt.transaction_id, '||
'       mmt.transaction_source_id, '||
'       mmt.completion_transaction_id, ' ||
'       mmt.move_transaction_id, ' ||
'       mmt.organization_id, ' ||
'       mmt.costed_flag, ' ||
'       decode(mmt.transaction_action_id, ' ||
'                  1, ''Issue'', ' ||
'                  27, ''Receipt'', ' ||
'                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'', ' ||
'             mmt.transaction_action_id) txn_action_meaning, ' ||
'       mmt.error_code, ' ||
'       SubStrB(mmt.error_explanation, 1, 50) err_explain ' ||
'FROM   mtl_material_transactions mmt,  ' ||
'       wip_entities we, ' ||
'       wip_lines wl ' ||
'WHERE  mmt.transaction_action_id IN (1, 27, 33, 34) ' ||
'AND    mmt.transaction_source_type_id = 5 ' ||
'AND    mmt.costed_flag IN (''N'',''E'')   ' ||
'AND    mmt.move_transaction_id IS NOT NULL  ' ||
'AND    mmt.transaction_source_id = we.wip_entity_id ' ||
'AND    mmt.organization_id = we.organization_id ' ||
'AND    we.entity_type = 2 ' ||we_dyn_where_clause||
'AND    mmt.repetitive_line_id = wl.line_id '||
'AND    mmt.organization_id = wl.organization_id '||
'AND    NOT EXISTS (SELECT 1 ' ||
'                   FROM   mtl_material_txn_allocations mmta ' ||
'                   WHERE  mmta.transaction_id = mmt.transaction_id ' ||
'                   AND    mmta.organization_id = mmt.organization_id) '||
'ORDER BY mmt.organization_id, we.wip_entity_name, mmt.transaction_id';

Line: 227


'SELECT we.wip_entity_name Repetitive_Assembly,  '||
'       wl.line_code Repetitive_Line, '||
'       mmt.organization_id, '||
'       mmt.transaction_id, '||
'       mmt.transaction_source_id, '||
'       mmt.completion_transaction_id, ' ||
'       mmt.move_transaction_id, ' ||
'       mmt.organization_id, ' ||
'       mmt.costed_flag, ' ||
'       decode(mmt.transaction_action_id, ' ||
'                  1, ''Issue'', ' ||
'                  27, ''Receipt'', ' ||
'                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'', ' ||
'             mmt.transaction_action_id) txn_action_meaning, ' ||
'       mmt.error_code, ' ||
'       SubStrB(mmt.error_explanation, 1, 50) err_explain ' ||
'FROM   mtl_material_transactions mmt,  ' ||
'       wip_entities we, ' ||
'       wip_lines wl '||
'WHERE  mmt.transaction_action_id IN (1, 27, 33, 34) ' ||
'AND    mmt.transaction_source_type_id = 5 ' ||
'AND    mmt.costed_flag IN (''N'',''E'')   ' ||
'AND    mmt.move_transaction_id IS NULL  ' ||
'AND    mmt.completion_transaction_id IS NOT NULL ' ||
'AND    mmt.transaction_source_id = we.wip_entity_id ' ||
'AND    mmt.organization_id = we.organization_id ' ||
'AND    we.entity_type = 2 ' ||we_dyn_where_clause||
'AND    mmt.repetitive_line_id = wl.line_id '||
'AND    mmt.organization_id = wl.organization_id '||
'AND    NOT EXISTS (SELECT 1 ' ||
'                   FROM   mtl_material_txn_allocations mmta ' ||
'                   WHERE  mmta.transaction_id = mmt.transaction_id ' ||
'                   AND    mmta.organization_id = mmt.organization_id) '||
'ORDER BY mmt.organization_id, we.wip_entity_name, mmt.transaction_id';

Line: 290


'SELECT we.wip_entity_name Repetitive_Assembly,  '||
'       wl.line_code Repetitive_Line, '||
'       mmt.organization_id, '||
'       mmta.repetitive_schedule_id, '||
'       mmt.transaction_id, '||
'       mmt.transaction_source_id, '||
'       mmt.completion_transaction_id, ' ||
'       mmt.move_transaction_id, ' ||
'       mmt.organization_id, ' ||
'       mmt.costed_flag, ' ||
'       decode(mmt.transaction_action_id, ' ||
'                  1, ''Issue'', ' ||
'                  27, ''Receipt'', ' ||
'                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'', ' ||
'             mmt.transaction_action_id) txn_action_meaning, ' ||
'       mmt.error_code, ' ||
'       SubStrB(mmt.error_explanation, 1, 50) err_explain ' ||
'FROM   mtl_material_transactions mmt,  ' ||
'       mtl_material_txn_allocations mmta, ' ||
'       wip_entities we, wip_lines wl ' ||
'WHERE  mmt.transaction_action_id IN (1, 27, 33, 34) ' ||
'AND    mmt.transaction_source_type_id = 5 ' ||
'AND    mmt.costed_flag IN (''N'',''E'')   ' ||
'AND    mmt.move_transaction_id IS NOT NULL  ' ||
'AND    mmt.transaction_id = mmta.transaction_id ' ||
'AND    mmt.organization_id = mmta.organization_id ' ||
'AND    mmt.transaction_source_id = we.wip_entity_id '||
'AND    mmt.organization_id = we.organization_id '||
'AND    we.entity_type = 2 '||we_dyn_where_clause||
'AND    mmt.repetitive_line_id = wl.line_id '||
'AND    mmt.organization_id = wl.organization_id '||
'AND    NOT EXISTS (SELECT 1 ' ||
'                   FROM   wip_move_txn_allocations wmta ' ||
'                   WHERE  wmta.transaction_id = mmt.move_transaction_id ' ||
'                   AND    wmta.organization_id = mmt.organization_id ' ||
'                   AND    wmta.repetitive_schedule_id = mmta.repetitive_schedule_id) '||
'ORDER BY mmt.organization_id, we.wip_entity_name, mmt.transaction_id';

Line: 355


'SELECT we.wip_entity_name Repetitive_Assembly,  '||
'       wl.line_code Repetitive_Line, '||
'       mmt.organization_id, '||
'       mmta.repetitive_schedule_id, '||
'       mmt.transaction_id, '||
'       mmt.transaction_source_id, '||
'       mmt.completion_transaction_id, ' ||
'       mmt.move_transaction_id, ' ||
'       mmt.organization_id, ' ||
'       mmt.costed_flag, ' ||
'       decode(mmt.transaction_action_id, ' ||
'                  1, ''Issue'', ' ||
'                  27, ''Receipt'', ' ||
'                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'', ' ||
'             mmt.transaction_action_id) txn_action_meaning, ' ||
'       mmt.error_code, ' ||
'       SubStrB(mmt.error_explanation, 1, 50) err_explain ' ||
'FROM   mtl_material_transactions mmt,  ' ||
'       mtl_material_txn_allocations mmta, ' ||
'       wip_entities we, wip_lines wl '||
'WHERE  mmt.transaction_action_id IN (1, 27, 33, 34) ' ||
'AND    mmt.transaction_source_type_id = 5 ' ||
'AND    mmt.costed_flag IN (''N'',''E'')   ' ||
'AND    mmt.move_transaction_id IS NULL  ' ||
'AND    mmt.completion_transaction_id IS NOT NULL  ' ||
'AND    mmt.transaction_id = mmta.transaction_id ' ||
'AND    mmt.organization_id = mmta.organization_id ' ||
'AND    mmt.transaction_source_id = we.wip_entity_id '||
'AND    mmt.organization_id = we.organization_id '||
'AND    we.entity_type = 2 '||we_dyn_where_clause||
'AND    mmt.repetitive_line_id = wl.line_id '||
'AND    mmt.organization_id = wl.organization_id '||
'AND    NOT EXISTS (SELECT 1 ' ||
'                   FROM   mtl_material_transactions mmt1, ' ||
'                          mtl_material_txn_allocations mmta1 ' ||
'                   WHERE  mmt1.transaction_action_id NOT IN (1, 27, 33, 34) ' ||
'                   AND    mmt1.transaction_source_type_id = 5 ' ||
'                   AND    mmt1.transaction_source_id = mmt.transaction_source_id ' ||
'                   AND    mmt1.organization_id = mmt.organization_id ' ||
'                   AND    mmt1.completion_transaction_id = mmt.completion_transaction_id ' ||
'                   AND    mmt1.transaction_id = mmta1.transaction_id ' ||
'                   AND    mmt1.organization_id = mmt1.organization_id ' ||
'                   AND    mmta1.repetitive_schedule_id = mmta.repetitive_schedule_id) '||
'ORDER BY mmt.organization_id, we.wip_entity_name, mmt.transaction_id';