PA Project Asset Details

Description
Categories: BI Publisher, Financials
Application: Projects
Source: MGT: Project Asset Details Report (XML)
Short Name: PACRCBDT_XML
DB package: PA_PACRCBDT_XMLP_PKG
            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
Parameter Name SQL text Validation
Show Asset Details
 
LOV Oracle
Class Code
 
LOV Oracle
Class Category
 
LOV Oracle
Project Organization
 
LOV Oracle
Event Period Name
 
LOV Oracle
Project Number
 
LOV Oracle
Project Type
 
LOV Oracle