This GA Projects By Task query displays project tasks with associated budget and status details by organization, supporting financial tracking and project management.
#sqlquery
SELECT
-- SQL4Fusion (An Orbit Analytics Project)
-- Displays project tasks with budget and status by organization
-- Organization Context
HAOUT.NAME ORGANIZATION,
-- Project Details
PPAV.SEGMENT1 PROJECT_NUMBER,
PPAV.NAME PROJECT_NAME,
PPAV.START_DATE PROJECT_START_DATE,
PPAV.COMPLETION_DATE PROJECT_END_DATE,
-- Task Details
PTV.TASK_NUMBER,
PTV.TASK_NAME,
PTV.START_DATE TASK_START_DATE,
PTV.COMPLETION_DATE TASK_END_DATE,
-- Budget Period & Status
PPLD.PERIOD_NAME, -- The period of the budget line
PBVB.PLAN_STATUS_CODE BUDGET_STATUS, -- (W - Working, B - Baselined)
-- Financial Metrics
PPLD.TXN_CURRENCY_CODE TRANSACTION_CURRENCY_CODE,
SUM(PPLD.TC_BRDND_COST) TOTAL_BURDENED_COST, --The fully loaded (burdened) cost of the planned resource or activity
SUM(PPLD.TC_RAW_COST) TOTAL_RAW_COST --The direct (unburdened) cost of a planned resource or activity
FROM
PJO_PLAN_LINE_DETAILS PPLD,
PJO_PLAN_VERSIONS_B PBVB,
PJF_PROJECTS_ALL_VL PPAV,
PJF_TASKS_V PTV,
HR_ALL_ORGANIZATION_UNITS_TL HAOUT
WHERE
1 = 1
AND PPLD.PLAN_VERSION_ID = PBVB.PLAN_VERSION_ID
AND HAOUT.ORGANIZATION_ID = PPAV.ORG_ID
AND PBVB.PROJECT_ID = PPAV.PROJECT_ID
AND PPAV.PROJECT_ID = PTV.PROJECT_ID
AND PBVB.PROJECT_ID = PTV.PROJECT_ID
AND HAOUT.LANGUAGE = USERENV('LANG')
AND PBVB.VERSION_NUMBER = ( SELECT MAX(VERSION_NUMBER)
FROM PJO_PLAN_VERSIONS_B
WHERE PROJECT_ID = PBVB.PROJECT_ID
AND PLAN_STATUS_CODE = 'B' -- Considering only baselined budgets
)
GROUP BY
HAOUT.NAME,
PPAV.SEGMENT1,
PPAV.NAME,
PTV.TASK_NUMBER,
PTV.TASK_NAME,
PPLD.PERIOD_NAME,
PTV.START_DATE,
PTV.COMPLETION_DATE,
PBVB.PLAN_STATUS_CODE,
PPLD.TXN_CURRENCY_CODE,
PPAV.START_DATE ,
PPAV.COMPLETION_DATE
ORDER BY PPAV.SEGMENT1,
PPLD.PERIOD_NAME,
PTV.TASK_NUMBER ;