SELECT
substr(o.name,1,60) organization,
per.full_name project_manager,
p.segment1 project_number,
p.name project_name,
nvl(tu.ei_bucket1,0) + nvl(tu.event_bucket1,0) bucket1,
nvl(tu.ei_bucket2,0) + nvl(tu.event_bucket2,0) bucket2,
nvl(tu.ei_bucket3,0) + nvl(tu.event_bucket3,0) bucket3,
nvl(tu.ei_bucket4,0) + nvl(tu.event_bucket4,0) bucket4,
nvl(tu.event_inv_amount,0) event_invoiced_amount,
nvl(tu.cost_work_amount,0) cost_work_amount,
nvl(tu.invoice_reduction,0) invoice_reduction,
nvl(tu.retention,0) retention,
p.project_currency_code,
PA_PAXMGURA_XMLP_PKG.C_COLHEAD1_p C_COLHEAD1,
PA_PAXMGURA_XMLP_PKG.C_COLHEAD2_p C_COLHEAD2,
PA_PAXMGURA_XMLP_PKG.C_COLHEAD3_p C_COLHEAD3,
PA_PAXMGURA_XMLP_PKG.C_COLHEAD4_p C_COLHEAD4,
PA_PAXMGURA_XMLP_PKG.final_bucket1formula('BUCKET1',nvl(tu.event_inv_amount,0),nvl(tu.cost_work_amount,0),nvl(tu.ei_bucket1,0) + nvl(tu.event_bucket1,0),nvl(tu.ei_bucket2,0) + nvl(tu.event_bucket2,0),nvl(tu.ei_bucket3,0) + nvl(tu.event_bucket3,0),nvl(tu.ei_bucket4,0) + nvl(tu.event_bucket4,0),nvl(tu.invoice_reduction,0),nvl(tu.retention,0)) FINAL_BUCKET1,
PA_PAXMGURA_XMLP_PKG.final_bucket2formula('BUCKET2',nvl(tu.event_inv_amount,0),nvl(tu.cost_work_amount,0),nvl(tu.ei_bucket1,0) + nvl(tu.event_bucket1,0),nvl(tu.ei_bucket2,0) + nvl(tu.event_bucket2,0),nvl(tu.ei_bucket3,0) + nvl(tu.event_bucket3,0),nvl(tu.ei_bucket4,0) + nvl(tu.event_bucket4,0),nvl(tu.invoice_reduction,0),nvl(tu.retention,0)) FINAL_BUCKET2,
PA_PAXMGURA_XMLP_PKG.final_bucket3formula('BUCKET3',nvl(tu.event_inv_amount,0),nvl(tu.cost_work_amount,0),nvl(tu.ei_bucket1,0) + nvl(tu.event_bucket1,0),nvl(tu.ei_bucket2,0) + nvl(tu.event_bucket2,0),nvl(tu.ei_bucket3,0) + nvl(tu.event_bucket3,0),nvl(tu.ei_bucket4,0) + nvl(tu.event_bucket4,0),nvl(tu.invoice_reduction,0),nvl(tu.retention,0)) FINAL_BUCKET3,
PA_PAXMGURA_XMLP_PKG.final_bucket4formula('BUCKET4',nvl(tu.event_inv_amount,0),nvl(tu.cost_work_amount,0),nvl(tu.ei_bucket1,0) + nvl(tu.event_bucket1,0),nvl(tu.ei_bucket2,0) + nvl(tu.event_bucket2,0),nvl(tu.ei_bucket3,0) + nvl(tu.event_bucket3,0),nvl(tu.ei_bucket4,0) + nvl(tu.event_bucket4,0),nvl(tu.invoice_reduction,0),nvl(tu.retention,0)) FINAL_BUCKET4,
PA_PAXMGURA_XMLP_PKG.totalformula(nvl(tu.event_inv_amount,0),nvl(tu.cost_work_amount,0),nvl(tu.ei_bucket1,0) + nvl(tu.event_bucket1,0),nvl(tu.ei_bucket2,0) + nvl(tu.event_bucket2,0),nvl(tu.ei_bucket3,0) + nvl(tu.event_bucket3,0),nvl(tu.ei_bucket4,0) + nvl(tu.event_bucket4,0),nvl(tu.invoice_reduction,0),nvl(tu.retention,0)) TOTAL
FROM
hr_organization_units o,
per_people_f per,
pa_project_players pl,
pa_projects p,
pa_project_types pr_type, /*Added for bug 7115658*/
pa_unbilled_rec_reporting tu
WHERE
p.carrying_out_organization_id between nvl(:org_id,0) and nvl(:org_id,999999999999999)
and o.organization_id = p.carrying_out_organization_id
and nvl(:date_from,sysdate) between pl.start_date_active and
nvl(pl.end_date_active,nvl(:date_from,sysdate+1))
and per.person_id between nvl(:project_manager_id,0) and nvl(:project_manager_id,999999999999999)
and per.person_id = pl.person_id
and per.employee_number is not null
and trunc(sysdate) between per.effective_start_date and per.effective_end_date
and pl.project_role_type = 'PROJECT MANAGER'
/*and p.project_id between nvl(:proj,0) and
nvl(:proj, 999999999999999)
Commented the above and added below conditions for bug 7115658*/
and ((:proj is not null and p.project_id = :proj)
or (:proj is null
/* 20027987 and p.segment1 between :from_project_number and :to_project_number */
and p.segment1 between :from_pn and :to_pn /*20027987*/
and ((nvl(:project_status,'ALL') = 'ALL_EXCEPT_CLOSED'
and p.project_status_code <> 'CLOSED'
)
OR (nvl(:project_status,'ALL') <> 'ALL_EXCEPT_CLOSED'
and p.project_status_code = decode(nvl(:project_status,'ALL'),
'ALL',p.project_status_code,
'ALL_EXCEPT_CLOSED', p.project_status_code,
nvl(:project_status,'ALL'))
)
)
and ((nvl(:project_status,'ALL') not in ('ALL', 'CLOSED')
and p.closed_date is null
)
OR (nvl(:project_status,'ALL') in ('ALL','CLOSED')
and nvl(p.closed_date,sysdate)
> nvl(:project_closed_after,sysdate-365)
)
)
) /*Close of or proj is null*/
)
and p.project_type = pr_type.project_type
and pr_type.project_type_class_code = 'CONTRACT'
and pl.project_id = p.project_id /*end of code added for bug 7115658*/
and p.project_id = tu.project_id
and nvl(tu.ei_bucket1,0) + nvl(tu.event_bucket1,0)+
nvl(tu.ei_bucket2,0) + nvl(tu.event_bucket2,0)+
nvl(tu.ei_bucket3,0) + nvl(tu.event_bucket3,0)+
nvl(tu.ei_bucket4,0) + nvl(tu.event_bucket4,0)+
nvl(tu.event_inv_amount,0)+
nvl(tu.cost_work_amount,0)+
nvl(tu.invoice_reduction,0)+
nvl(tu.retention,0) <> 0
ORDER BY 1 ASC,2 ASC , o.name , per.full_name , p.segment1 |
