Here are some sample Oracle Fusion SQL queries for various aspects of project management:
1. Query to Retrieve Project Details
SELECT project_id, project_name,
project_number,
project_status,
start_date,
end_date
FROM pjf_projects_all
WHERE project_status = 'APPROVED';
2. Query to Retrieve Task Details for a Specific Project
SELECT task_id, task_name,
task_number,
task_status,
start_date,
end_date
FROM pjf_tasks
WHERE project_id = :project_id; -- Replace with the specific project ID
3. Query to Retrieve Expenditure Items for a Specific Project
SELECT expenditure_item_id, expenditure_type,
expenditure_date,
amount
FROM pjc_expenditure_items
WHERE project_id = :project_id; -- Replace with the specific project ID
4. Query to Retrieve Budget Lines for a Specific Project
SELECT budget_version_id, budget_line_id,
budget_amount,
budget_date
FROM pjf_budget_lines
WHERE project_id = :project_id; -- Replace with the specific project ID
5. Query to Retrieve Resource Assignments for a Specific Project
SELECT assignment_id, resource_id,
task_id,
start_date,
end_date
FROM pjr_assignments
WHERE project_id = :project_id; -- Replace with the specific project ID
6. Query to Retrieve Contract Details for a Specific Project
SELECT contract_id, contract_number,
contract_name,
start_date,
end_date,
contract_amount
FROM pjb_contracts_all
WHERE project_id = :project_id; -- Replace with the specific project ID
7. Query to Retrieve Invoice Details for a Specific Project
SELECT invoice_id, invoice_number,
invoice_date,
amount
FROM pjb_invoices_all
WHERE project_id = :project_id; -- Replace with the specific project ID
8. Query to Retrieve Cost Distribution Lines for a Specific Project
SELECT cost_distribution_line_id, transaction_id,
amount,
distribution_date
FROM pjc_cost_distribution_lines
WHERE project_id = :project_id; -- Replace with the specific project ID
These sample queries can be tailored to specific needs by modifying the WHERE
clause conditions or by adding more columns to the SELECT
statement.
Note, please refer to the Oracle documentations for any changes to the latest release and the updates.