PA Potential Revenue Summary
Description
Categories: BI Publisher
Application: Projects
Source: MGT: Potential Revenue Summary (XML)
Short Name: PAXPCPRS_XML
DB package: PA_PAXPCPRS_XMLP_PKG
Source: MGT: Potential Revenue Summary (XML)
Short Name: PAXPCPRS_XML
DB package: PA_PAXPCPRS_XMLP_PKG
Run
PA Potential Revenue Summary and other Oracle EBS reports with Blitz Report™ on our demo environment
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 | |
---|---|---|---|
Accrue Thru Date |
|
Date | |
Project Organization |
|
LOV Oracle | |
Project Manager |
|
LOV Oracle | |
Project Number |
|
LOV Oracle | |
Include Closed Projects? |
|
LOV Oracle |