<!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <title>SQL Queries for Oracle Database</title> </head> <body> <div> <p>The provided SQL queries and descriptions are related to Oracle database operations, specifically focusing on material transactions and inventory management. The queries include joins with various tables such as `<code>mtl_material_transactions</code>`, `<code>mtl_transaction_types</code>`, `<code>mtl_system_items</code>`, `<code>FND_USER</code>`, <code>per_people_f</code>, and others to retrieve detailed information about material movements and associated costs. The descriptions provide context for the data being queried, including transaction types, cost implications, and user details.</p> <h2>Queries</h2> <h3>Query 1: Detailed Records of Material Transactions</h3> <p>This query retrieves detailed records of material transactions, including type, cost, and associated user information. It filters by organization ID and inventory item segment to focus on specific items.</p> <table border="1" style="width: 100%; border-collapse: collapse;"> <tr> <th Field Name="TRANSACTION_TYPE_ID">Field Name</th> <th Field Name="ACTUAL_COST">Description</th> <th Field Name="USER_NAME">Description</th> <th Field Name="FULL_NAME">Description</th> </tr> </table> <h3>Query 2: List of All Possible Transaction Types</h3> <p>This query lists all possible transaction types in the system, providing details such as type ID and description.</p> <table border="1" style="width: 100%; border-collapse: collapse;"> <tr> <th Field Name="TRANSACTION_TYPE_ID">Field Name</th> <th Field Name="TRANSACTION_TYPE_NAME">Description</th> <th Field Name="DESCRIPTION">Description</th> </tr> </table> <h3>Query 3: List of All Possible Transaction Source Types</h3> <p>This query provides a list of all possible transaction source types available in the system.</p> <table border="1" style="width: 100%; border-collapse: collapse;"> <tr> <th Field Name="SOURCE_TYPE_ID">Field Name</th> <th Field Name="SOURCE_TYPE_NAME">Description</th> </tr> </table> <h3>Query 4: List of All Possible Transaction Reason Codes</h3> <p>This query lists all possible transaction reason codes and their descriptions, providing a reference for understanding why transactions occur.</p> <table border="1" style="width: 100%; border-collapse: collapse;"> <tr> <th Field Name="REASON_ID">Field Name</th> <th Field Name="REASON_NAME">Description</th> <th Field Name="DESCRIPTION">Description</th> </tr> </table> <h2>Notes</h2> <ul style="list-style: decimal;"> <li>The queries use joins to combine data from multiple tables, which is a common practice in Oracle for comprehensive reporting and analysis.</li> <li>Field Name provide context for the data being queried, including transaction types, cost implications, and user details.</li> </ul> </div> </body> </html>
