PA Unbilled Receivables Aging

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