PA Project Asset Details

            SELECT PP.project_type
                         ,PP.segment1 project_number
                         ,PP.name
                        ,PP.project_id
                        ,E.capital_event_number
                        ,E.event_type
                        ,E.event_name
                        ,E.capital_event_id
                        ,PPA.asset_name
                        ,PPA.asset_category_id
                       ,PPA.location_id
                       ,PPA.project_asset_type
                       ,PPAL.description
                       ,PPAL.original_asset_cost
                       ,PPAL.current_asset_cost
                       ,PPA.grouped_cip_cost
                       ,PP.project_currency_code
                      , PP.carrying_out_organization_id,
                PA_PACRCBDT_XMLP_PKG.organization_dspformula(PP.carrying_out_organization_id) organization_dsp,
                &project_currency_code CF_currency_Code,
                PA_PACRCBDT_XMLP_PKG.cf_project_setupformula(PP.project_id) CF_project_setup,
                PA_PACRCBDT_XMLP_PKG.CP_line_type_p CP_line_type,
                PA_PACRCBDT_XMLP_PKG.CP_capital_cost_type_flag_p CP_capital_cost_type_flag,
                PA_PACRCBDT_XMLP_PKG.event_type_dspformula(E.event_type) event_type_dsp,
                PA_PACRCBDT_XMLP_PKG.cf_total_costformula(PP.project_id, E.capital_event_id) CF_total_cost,
                PA_PACRCBDT_XMLP_PKG.cf_categoryformula(PPA.asset_category_id) CF_category,
                PA_PACRCBDT_XMLP_PKG.project_asset_type_dispformula(PPA.project_asset_type) Project_asset_type_disp,
                PA_PACRCBDT_XMLP_PKG.cf_locationformula(PPA.location_id) CF_location
            FROM  pa_capital_events E,pa_project_asset_lines PPAL,
                   pa_project_assets PPA,
                   pa_projects PP
            WHERE  E.capital_event_id=PPA.capital_event_id
              AND  PPA.project_id = PP.project_id
              AND  PPAL.project_asset_id(+) = PPA.project_asset_id
              AND  PPA.project_id= NVL(:p_project_id,PPA.project_id)
              AND  PP.project_type= NVL(:p_project_type,PP.project_type)
              AND  E.event_period=NVL(:p_event_period,E.event_period)
              AND  PP.carrying_out_organization_id=NVL(:p_org_id,PP.carrying_out_organization_id)
              AND     (:p_class_category IS NULL  OR
                           EXISTS ( SELECT   1
                                            FROM     pa_project_classes
                             WHERE   project_id=E.project_id
                                               AND     class_category = :p_class_category) )
              AND    (:p_class_category IS NULL   OR
                          :p_class_code        IS NULL   OR
                          EXISTS  ( SELECT  1
                                             FROM     pa_project_classes
                                   WHERE     project_id=E.project_id
                                             AND     class_category = :p_class_category
                                            AND     class_code = :p_class_code))
            UNION ALL
            SELECT PP.project_type
                         ,PP.segment1 project_number
                       ,PP.name
                       ,PP.project_id
                        ,Null capital_event_number --event number getting displayed as 99999999999 so made null
                       ,NULL event_type
                        ,NULL event_name
                       ,-1 capital_event_id
                        ,PPA.asset_name
                       ,PPA.asset_category_id
                        ,PPA.location_id
                         ,PPA.project_asset_type
                          ,PPAL.description
                        ,PPAL.original_asset_cost
                       ,PPAL.current_asset_cost
                  ,PPA.grouped_cip_cost
                   ,PP.project_currency_code
                       ,PP.carrying_out_organization_id,
                PA_PACRCBDT_XMLP_PKG.organization_dspformula(PP.carrying_out_organization_id) organization_dsp,
                &project_currency_code CF_currency_Code,
                PA_PACRCBDT_XMLP_PKG.cf_project_setupformula(PP.project_id) CF_project_setup,
                PA_PACRCBDT_XMLP_PKG.CP_line_type_p CP_line_type,
                PA_PACRCBDT_XMLP_PKG.CP_capital_cost_type_flag_p CP_capital_cost_type_flag,
				--PA_PACRCBDT_XMLP_PKG.event_type_dspformula(E.event_type) event_type_dsp,
				Null event_type_dsp,
                PA_PACRCBDT_XMLP_PKG.cf_total_costformula(PP.project_id, 9999999999999999) CF_total_cost,
                PA_PACRCBDT_XMLP_PKG.cf_categoryformula(PPA.asset_category_id) CF_category,
                PA_PACRCBDT_XMLP_PKG.project_asset_type_dispformula(PPA.project_asset_type) Project_asset_type_disp,
                PA_PACRCBDT_XMLP_PKG.cf_locationformula(PPA.location_id) CF_location
            FROM  pa_project_asset_lines PPAL,
                   pa_project_assets PPA,
                   pa_projects PP
            WHERE  PPA.project_id = PP.project_id
              AND  PPAL.project_asset_id(+) = PPA.project_asset_id
              AND  PPA.project_id= NVL(:p_project_id,PPA.project_id)
              AND  PP.project_type= NVL(:p_project_type,PP.project_type)
              AND PPA.capital_event_id=-1
              AND  PP.carrying_out_organization_id=NVL(:p_org_id,PP.carrying_out_organization_id)
              AND     (:p_class_category IS NULL  OR
                             EXISTS ( SELECT   1
                                        FROM     pa_project_classes
                                      WHERE   project_id=PPA.project_id
                                        AND     class_category = :p_class_category) )
              AND    (:p_class_category IS NULL   OR
                      :p_class_code        IS NULL   OR
                       EXISTS  ( SELECT  1
                              FROM     pa_project_classes
                             WHERE     project_id=PPA.project_id
                               AND     class_category = :p_class_category
                               AND     class_code = :p_class_code))
			order by 19,18,1,17,2,3,4,5,6,7,8,9,12,16,10,11,13,14,15