PJT_FIN_PROJ_PROGRESS

Details

Schema: FUSION
Object owner: PJT
Object type: VIEW

Columns

Name
PROJECT_ID TASK_ID PHYSICAL_PERCENT_COMPLETE IS_TASK RESOURCE_ID PERSON_ID EXPENDITURE_TYPE_ID RESOURCE_CLASS UNIT_OF_MEASURE CURRENCY_CODE START_DATE FINISH_DATE QUANTITY LABOR_COST_AMOUNT EXPENSE_COST_AMOUNT

Name

PROJECT_ID

TASK_ID

PHYSICAL_PERCENT_COMPLETE

IS_TASK

RESOURCE_ID

PERSON_ID

EXPENDITURE_TYPE_ID

RESOURCE_CLASS

UNIT_OF_MEASURE

CURRENCY_CODE

START_DATE

FINISH_DATE

QUANTITY

LABOR_COST_AMOUNT

EXPENSE_COST_AMOUNT

Query

SQL_Statement
select project_id, proj_element_id, physical_percent_complete, is_task, resource_id, person_id, expenditure_type_id, resource_class, unit_of_measure, currency_code, min(start_date) as start_date,max(finish_date) as finish_date,sum(quantity) as quantity, sum(labor_cost_amount) as labor_cost_amount, sum(expense_cost_amount) as expense_cost_amount from ( select e.project_id as project_id, connect_by_root e.name as task_name, connect_by_root e.proj_element_id as proj_element_id, pl.rollup_plan_line as is_task, decode(pl.rollup_plan_line,'Y',pl.physical_percent_complete) as physical_percent_complete, decode(pl.rollup_plan_line,'N',pl.resource_id) as resource_id, decode(pl.rollup_plan_line,'N',pl.labor_cost_amount) as labor_cost_amount, decode(pl.rollup_plan_line,'N',pl.expense_cost_amount) as expense_cost_amount, decode(pl.rollup_plan_line,'N',r.person_id) as person_id, decode(pl.rollup_plan_line,'N',r.expenditure_type_id) as expenditure_type_id, decode(pl.rollup_plan_line,'N',r.resource_class) as resource_class, decode(pl.rollup_plan_line,'N',r.unit_of_measure) as unit_of_measure, decode(pl.rollup_plan_line,'N',r.currency_code) as currency_code, pl.start_date, pl.finish_date, pl.quantity from pjf_proj_elements_vl e, pjf_proj_element_version ev, pjt_proj_plan_lines pl, pjt_prj_enterprise_resource_b r where e.proj_element_id = ev.proj_element_id and ev.element_version_id = pl.element_version_id and pl.resource_id = r.resource_id (+) and ((pl.rollup_plan_line = 'Y' and level = 1) or (pl.rollup_plan_line = 'N' and pl.start_date is not null and pl.finish_date is not null and level > 1) or (pl.rollup_plan_line = 'N' and pl.start_date is not null and pl.finish_date is not null and level = 1 and connect_by_root ev.element_version_id = ev.element_version_id)) connect by (ev.parent_element_version_id = prior ev.element_version_id and e.element_type = 'EXECUTION') start with (e.element_type = 'FINANCIAL' or e.element_type = 'FIN_EXEC') ) group by project_id, task_name, proj_element_id, physical_percent_complete, is_task, resource_id, person_id, expenditure_type_id, resource_class, unit_of_measure, currency_code order by project_id, task_name, proj_element_id, physical_percent_complete, is_task, resource_id, person_id, expenditure_type_id, resource_class, unit_of_measure, currency_code

SQL_Statement

select project_id, proj_element_id, physical_percent_complete, is_task, resource_id,

person_id, expenditure_type_id, resource_class, unit_of_measure, currency_code,

min(start_date) as start_date,max(finish_date) as finish_date,sum(quantity) as quantity,

sum(labor_cost_amount) as labor_cost_amount, sum(expense_cost_amount) as expense_cost_amount

from (

select e.project_id as project_id, connect_by_root e.name as task_name,

connect_by_root e.proj_element_id as proj_element_id,

pl.rollup_plan_line as is_task,

decode(pl.rollup_plan_line,'Y',pl.physical_percent_complete) as physical_percent_complete,

decode(pl.rollup_plan_line,'N',pl.resource_id) as resource_id,

decode(pl.rollup_plan_line,'N',pl.labor_cost_amount) as labor_cost_amount,

decode(pl.rollup_plan_line,'N',pl.expense_cost_amount) as expense_cost_amount,

decode(pl.rollup_plan_line,'N',r.person_id) as person_id,

decode(pl.rollup_plan_line,'N',r.expenditure_type_id) as expenditure_type_id,

decode(pl.rollup_plan_line,'N',r.resource_class) as resource_class,

decode(pl.rollup_plan_line,'N',r.unit_of_measure) as unit_of_measure,

decode(pl.rollup_plan_line,'N',r.currency_code) as currency_code,

pl.start_date, pl.finish_date, pl.quantity

from pjf_proj_elements_vl e, pjf_proj_element_version ev, pjt_proj_plan_lines pl,

pjt_prj_enterprise_resource_b r

where

e.proj_element_id = ev.proj_element_id

and ev.element_version_id = pl.element_version_id

and pl.resource_id = r.resource_id (+)

and ((pl.rollup_plan_line = 'Y' and level = 1)

or (pl.rollup_plan_line = 'N' and pl.start_date is not null and pl.finish_date is not null and level > 1)

or (pl.rollup_plan_line = 'N' and pl.start_date is not null and pl.finish_date is not null and level = 1

and connect_by_root ev.element_version_id = ev.element_version_id))

connect by (ev.parent_element_version_id = prior ev.element_version_id

and e.element_type = 'EXECUTION')

start with (e.element_type = 'FINANCIAL' or e.element_type = 'FIN_EXEC')

)

group by project_id, task_name, proj_element_id, physical_percent_complete, is_task, resource_id,

person_id, expenditure_type_id, resource_class, unit_of_measure, currency_code

order by project_id, task_name, proj_element_id, physical_percent_complete, is_task, resource_id,

person_id, expenditure_type_id, resource_class, unit_of_measure, currency_code