PA Potential Revenue Summary

Description
Categories: BI Publisher, Financials
Application: Projects
Source: MGT: Potential Revenue Summary (XML)
Short Name: PAXPCPRS_XML
DB package: PA_PAXPCPRS_XMLP_PKG
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
Parameter Name SQL text Validation
Include Closed Projects?
 
LOV Oracle
Project Number
 
LOV Oracle
Project Manager
 
LOV Oracle
Project Organization
 
LOV Oracle
Accrue Thru Date
 
Date