SELECT
substr(p.organization_name,1,60) project_organization,
p.member_name Project_Manager,
p.project_number,
p.project_name,
sum(nvl(ei.adjusted_revenue,nvl(ei.raw_revenue, 0))) total_potential_revenue,
sum(decode(ei.revenue_distributed_flag, 'Y', 0, nvl(ei.adjusted_revenue, nvl(ei.raw_revenue,0)))) partial_potential_revenue,
sum(nvl(ei.accrued_revenue,0)) total_accrued_revenue,
sum(decode(ei.revenue_distributed_flag, 'P', nvl(ei.accrued_revenue,0),0)) partial_accrued_revenue,
count(ei.expenditure_item_id) total_item_count,
count(decode(ei.revenue_distributed_flag, 'P', 1, null )) partial_item_count
,
PA_PAXPCPRS_XMLP_PKG.c_disp_org_pot_revformula(:C_org_pot_rev) C_disp_org_pot_rev,
PA_PAXPCPRS_XMLP_PKG.c_disp_org_acc_revformula(:C_ORG_ACC_REV) C_disp_org_acc_rev,
PA_PAXPCPRS_XMLP_PKG.c_disp_org_diff_revformula(:C_org_diff_rev) C_disp_org_diff_rev,
PA_PAXPCPRS_XMLP_PKG.c_disp_org_tot_potformula(:C_org_tot_pot) C_disp_org_tot_pot,
PA_PAXPCPRS_XMLP_PKG.c_disp_org_tot_accformula(:C_org_tot_acc) C_disp_org_tot_acc,
PA_PAXPCPRS_XMLP_PKG.c_disp_org_tot_diffformula(:C_org_tot_diff) C_disp_org_tot_diff,
PA_PAXPCPRS_XMLP_PKG.c_disp_mgr_pot_revformula(:C_MGR_POT_REV) C_disp_mgr_pot_rev,
PA_PAXPCPRS_XMLP_PKG.c_disp_mgr_acc_revformula(:C_mgr_acc_rev) C_disp_mgr_acc_rev,
PA_PAXPCPRS_XMLP_PKG.c_disp_mgr_diff_revformula(:C_MGR_DIFF_REV) C_disp_mgr_diff_rev,
PA_PAXPCPRS_XMLP_PKG.c_disp_mgr_tot_potformula(:C_MGR_TOT_POT) C_disp_mgr_tot_pot,
PA_PAXPCPRS_XMLP_PKG.c_disp_mgr_tot_accformula(:C_MGR_TOT_ACC) C_disp_mgr_tot_acc,
PA_PAXPCPRS_XMLP_PKG.c_disp_mgr_tot_diffformula(:C_MGR_TOT_DIFF) C_disp_mgr_tot_diff,
PA_PAXPCPRS_XMLP_PKG.c_disp_tot_pot_revformula(sum ( nvl ( ei.adjusted_revenue , nvl ( ei.raw_revenue , 0 ) ) )) C_disp_tot_pot_rev,
PA_PAXPCPRS_XMLP_PKG.c_disp_tot_acc_revformula(sum ( nvl ( ei.accrued_revenue , 0 ) )) C_disp_tot_acc_rev,
PA_PAXPCPRS_XMLP_PKG.c_diffformula(sum ( decode ( ei.revenue_distributed_flag , 'Y' , 0 , nvl ( ei.adjusted_revenue , nvl ( ei.raw_revenue , 0 ) ) ) ), sum ( decode ( ei.revenue_distributed_flag , 'P' , nvl ( ei.accrued_revenue , 0 ) , 0 ) )) c_diff,
PA_PAXPCPRS_XMLP_PKG.c_disp_diffformula(PA_PAXPCPRS_XMLP_PKG.c_diffformula(sum ( decode ( ei.revenue_distributed_flag , 'Y' , 0 , nvl ( ei.adjusted_revenue , nvl ( ei.raw_revenue , 0 ) ) ) ), sum ( decode ( ei.revenue_distributed_flag , 'P' , nvl ( ei.accrued_revenue , 0 ) , 0 ) ))) C_disp_diff,
PA_PAXPCPRS_XMLP_PKG.c_disp_par_pot_revformula(sum ( decode ( ei.revenue_distributed_flag , 'Y' , 0 , nvl ( ei.adjusted_revenue , nvl ( ei.raw_revenue , 0 ) ) ) )) C_disp_par_pot_rev,
PA_PAXPCPRS_XMLP_PKG.c_disp_par_acc_revformula(sum ( decode ( ei.revenue_distributed_flag , 'P' , nvl ( ei.accrued_revenue , 0 ) , 0 ) )) C_disp_par_acc_rev,
PA_PAXPCPRS_XMLP_PKG.c_diff2formula(sum ( nvl ( ei.adjusted_revenue , nvl ( ei.raw_revenue , 0 ) ) ), sum ( nvl ( ei.accrued_revenue , 0 ) )) C_diff2,
PA_PAXPCPRS_XMLP_PKG.c_disp_diff2formula(PA_PAXPCPRS_XMLP_PKG.c_diff2formula(sum ( nvl ( ei.adjusted_revenue , nvl ( ei.raw_revenue , 0 ) ) ), sum ( nvl ( ei.accrued_revenue , 0 ) ))) C_disp_diff2
FROM
pa_tasks t,
pa_project_types pt,
pa_expenditure_items_all ei,
pa_proj_members_view p
WHERE
( ( NVL(:include_closed_projects,'N') = 'N' and
pa_project_stus_utils.is_project_status_closed(p.project_status_code) = 'N')
or NVL(:include_closed_projects,'N') = 'Y')
and p.organization_id = nvl(:project_organization,p.organization_id)
and :proj is not null
and p.project_id = :proj
and p.project_id = t.project_id
and ei.task_id = t.task_id
and ei.expenditure_item_date <= :accrue_through_date
and p.member_person_id = nvl(:project_manager, p.member_person_id)
and p.project_role_type = 'PROJECT MANAGER'
and p.project_type = pt.project_type
and ei.billable_flag||'' = 'Y'
and pt.direct_flag = 'Y'
group by
p.organization_name,
p.member_name,
p.project_number,
p.project_name
having
count(decode(ei.revenue_distributed_flag, 'P', 1, null )) > 0
UNION ALL
SELECT
substr(p.organization_name,1,60) project_organization,
p.member_name Project_Manager,
p.project_number,
p.project_name,
sum(nvl(ei.adjusted_revenue,nvl(ei.raw_revenue, 0))) total_potential_revenue,
sum(decode(ei.revenue_distributed_flag, 'Y', 0, nvl(ei.adjusted_revenue, nvl(ei.raw_revenue,0)))) partial_potential_revenue,
sum(nvl(ei.accrued_revenue,0)) total_accrued_revenue,
sum(decode(ei.revenue_distributed_flag, 'P', nvl(ei.accrued_revenue,0),0)) partial_accrued_revenue,
count(ei.expenditure_item_id) total_item_count,
count(decode(ei.revenue_distributed_flag, 'P', 1, null )) partial_item_count,
PA_PAXPCPRS_XMLP_PKG.c_disp_org_pot_revformula(:C_org_pot_rev) C_disp_org_pot_rev,
PA_PAXPCPRS_XMLP_PKG.c_disp_org_acc_revformula(:C_org_acc_rev) C_disp_org_acc_rev,
PA_PAXPCPRS_XMLP_PKG.c_disp_org_diff_revformula(:C_org_diff_rev) C_disp_org_diff_rev,
PA_PAXPCPRS_XMLP_PKG.c_disp_org_tot_potformula(:C_org_tot_pot) C_disp_org_tot_pot,
PA_PAXPCPRS_XMLP_PKG.c_disp_org_tot_accformula(:C_org_tot_acc) C_disp_org_tot_acc,
PA_PAXPCPRS_XMLP_PKG.c_disp_org_tot_diffformula(:C_org_tot_diff) C_disp_org_tot_diff,
PA_PAXPCPRS_XMLP_PKG.c_disp_mgr_pot_revformula(:C_mgr_pot_rev) C_disp_mgr_pot_rev,
PA_PAXPCPRS_XMLP_PKG.c_disp_mgr_acc_revformula(:C_mgr_acc_rev) C_disp_mgr_acc_rev,
PA_PAXPCPRS_XMLP_PKG.c_disp_mgr_diff_revformula(:C_mgr_diff_rev) C_disp_mgr_diff_rev,
PA_PAXPCPRS_XMLP_PKG.c_disp_mgr_tot_potformula(:C_mgr_tot_pot) C_disp_mgr_tot_pot,
PA_PAXPCPRS_XMLP_PKG.c_disp_mgr_tot_accformula(:C_mgr_tot_acc) C_disp_mgr_tot_acc,
PA_PAXPCPRS_XMLP_PKG.c_disp_mgr_tot_diffformula(:C_mgr_tot_diff) C_disp_mgr_tot_diff,
PA_PAXPCPRS_XMLP_PKG.c_disp_tot_pot_revformula(sum ( nvl ( ei.adjusted_revenue , nvl ( ei.raw_revenue , 0 ) ) )) C_disp_tot_pot_rev,
PA_PAXPCPRS_XMLP_PKG.c_disp_tot_acc_revformula(sum ( nvl ( ei.accrued_revenue , 0 ) )) C_disp_tot_acc_rev,
PA_PAXPCPRS_XMLP_PKG.c_diffformula(sum ( decode ( ei.revenue_distributed_flag , 'Y' , 0 , nvl ( ei.adjusted_revenue , nvl ( ei.raw_revenue , 0 ) ) ) ), sum ( decode ( ei.revenue_distributed_flag , 'P' , nvl ( ei.accrued_revenue , 0 ) , 0 ) )) c_diff,
PA_PAXPCPRS_XMLP_PKG.c_disp_diffformula(PA_PAXPCPRS_XMLP_PKG.c_diffformula(sum ( decode ( ei.revenue_distributed_flag , 'Y' , 0 , nvl ( ei.adjusted_revenue , nvl ( ei.raw_revenue , 0 ) ) ) ), sum ( decode ( ei.revenue_distributed_flag , 'P' , nvl ( ei.accrued_revenue , 0 ) , 0 ) ))) C_disp_diff,
PA_PAXPCPRS_XMLP_PKG.c_disp_par_pot_revformula(sum ( decode ( ei.revenue_distributed_flag , 'Y' , 0 , nvl ( ei.adjusted_revenue , nvl ( ei.raw_revenue , 0 ) ) ) )) C_disp_par_pot_rev,
PA_PAXPCPRS_XMLP_PKG.c_disp_par_acc_revformula(sum ( decode ( ei.revenue_distributed_flag , 'P' , nvl ( ei.accrued_revenue , 0 ) , 0 ) )) C_disp_par_acc_rev,
PA_PAXPCPRS_XMLP_PKG.c_diff2formula(sum ( nvl ( ei.adjusted_revenue , nvl ( ei.raw_revenue , 0 ) ) ), sum ( nvl ( ei.accrued_revenue , 0 ) )) C_diff2,
PA_PAXPCPRS_XMLP_PKG.c_disp_diff2formula(PA_PAXPCPRS_XMLP_PKG.c_diff2formula(sum ( nvl ( ei.adjusted_revenue , nvl ( ei.raw_revenue , 0 ) ) ), sum ( nvl ( ei.accrued_revenue , 0 ) ))) C_disp_diff2
FROM
pa_tasks t,
pa_project_types pt,
pa_expenditure_items_all ei,
pa_proj_members_view p
WHERE
( ( NVL(:include_closed_projects,'N') = 'N' and
pa_project_stus_utils.is_project_status_closed(p.project_status_code) = 'N')
or NVL(:include_closed_projects,'N') = 'Y')
and p.organization_id = nvl(:project_organization,p.organization_id)
and :proj is null
and p.project_id = t.project_id
and ei.task_id = t.task_id
and ei.expenditure_item_date <= :accrue_through_date
and p.member_person_id = nvl(:project_manager, p.member_person_id)
and p.project_role_type = 'PROJECT MANAGER'
and p.project_type = pt.project_type
and ei.billable_flag||'' = 'Y'
and pt.direct_flag = 'Y'
group by
p.organization_name,
p.member_name,
p.project_number,
p.project_name
having
count(decode(ei.revenue_distributed_flag, 'P', 1, null )) > 0 |