PA Unbilled Receivables Aging

Description
Categories: BI Publisher
Application: Projects
Source: MGT: Unbilled Receivables Aging (XML)
Short Name: PAXMGURA_XML
DB package: PA_PAXMGURA_XMLP_PKG
Run PA Unbilled Receivables Aging and other Oracle EBS reports with Blitz Report™ on our demo environment
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
Parameter Name SQL text Validation
Effective Date
 
Date
Project Organization
 
LOV Oracle
Project Manager
 
LOV Oracle
Project Number
 
LOV Oracle
From Project Number
 
Char
To Project Number
 
Char
Project Status
 
LOV Oracle
Project Closed After
 
Date
Age Receivables From
 
LOV Oracle
Number of days in Bucket 1
 
Number
Number of days in Bucket 2
 
Number
Number of days in Bucket 3
 
Number
Blitz Report™