PA Capital Project Summary

select
 cv.project_type          project_type
,cv.project_number        project_number
,cv.project_name          project_name
,cv.project_id            project_id
,0+cv.expensed -(DECODE(pa_mc_currency_pkg.get_mrc_sob_type_code,
                         'R',0,
                          nvl(rv.retirable_cost,0))) expensed_amount
,0+cv.cip_cost                 CIP_Amount
,0+cv.capitalized_cost    interfaced_CIP_Amount
,0+DECODE(pa_mc_currency_pkg.get_mrc_sob_type_code,
          'R',null,
           nvl(rv.rwip,0))rwip_amount
,0+ (DECODE(pa_mc_currency_pkg.get_mrc_sob_type_code,
                   'R',null,
                    nvl(rv.retired_cost,0)))interfaced_rwip_amount
,0+cv.total_costs             total_amount
,cv.project_organization  project_organization,
	PA_PACRCAPS_XMLP_PKG.CP_project_id_p CP_project_id
from pa_capital_projects_v cv, (select sum(retirable_cost)   retirable_cost
                                      ,sum(retired_cost)     retired_cost
									  ,sum(retirable_cost - retired_cost)  rwip
									  ,project_id
								from  pa_retirement_costs_v
								where nvl(:p_project_type,project_type)=project_type
								and   nvl(:p_project_id,project_id)=project_id
								and   nvl(:p_project_org,carrying_out_organization_id)=carrying_out_organization_id
								and   (:p_class_category IS NULL  OR
                                        project_id in ( SELECT   project_id
                                                        FROM     pa_project_classes
                                                        WHERE   class_category = :p_class_category) )
                                and    (:p_class_category    IS NULL   OR
                                        :p_class_code        IS NULL   OR
                                         project_id in     ( SELECT   project_id
                                                             FROM     pa_project_classes
                                                             WHERE    class_category = :p_class_category
                                                             AND      class_code = :p_class_code))
								group by project_id) rv
where   rv.project_id(+)=cv.project_id
and     nvl(:p_project_type,cv.project_type)=cv.project_type
and     nvl(:p_project_id,cv.project_id)=cv.project_id
and     nvl(:cp_project_org,cv.project_organization)=cv.project_organization
and     (:p_class_category IS NULL  OR
             cv.project_id in ( SELECT   project_id
                                          FROM     pa_project_classes
                                          WHERE   class_category = :p_class_category) )
and    (:p_class_category IS NULL   OR
           :p_class_code        IS NULL   OR
           cv.project_id in  ( SELECT    project_id
                                         FROM       pa_project_classes
                                         WHERE    class_category = :p_class_category
                                          AND        class_code = :p_class_code))
										  ORDER BY project_type,project_organization