ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 9C63FC7B4AB9430D9D641E73522D3466Y select haouv.name operating_unit, ppa.segment1 project_number, ppa.name project_name, ppa.project_type, pps.project_status_name project_status, haouv2.name project_organization, pt.task_number top_task_number, pt.task_name top_task_name, gp.period_year, gp.period_name, to_char(gp.period_year)||'-'||lpad(gp.period_num,2,'0')||' ('||gp.period_name||')' period, ppa.projfunc_currency_code currency, sum(y.revenue) revenue, sum(y.raw_cost) raw_cost, sum(y.burdened_cost) burdened_cost, nvl(sum(y.revenue),0)-nvl(sum(y.burdened_cost),0) margin, round(100*(sum(y.revenue)-sum(y.burdened_cost))/nullif(sum(y.revenue),0),2) margin_percent from ( select pcdla.project_id, pt.top_task_id, decode(:period_basis,'PA',pcdla.pa_date,pcdla.gl_date) period_date, to_number(null) revenue, sum(pcdla.amount) raw_cost, sum(pcdla.burdened_cost) burdened_cost from pa_cost_distribution_lines_all pcdla, pa_tasks pt where pcdla.line_type='R' and pcdla.task_id=pt.task_id group by pcdla.project_id, pt.top_task_id, decode(:period_basis,'PA',pcdla.pa_date,pcdla.gl_date) union all select pcrdla.project_id, pt.top_task_id, decode(:period_basis,'PA',pdra.pa_date,pdra.gl_date) period_date, sum(pcrdla.projfunc_revenue_amount) revenue, to_number(null) raw_cost, to_number(null) burdened_cost from pa_draft_revenues_all pdra, pa_cust_rev_dist_lines_all pcrdla, pa_expenditure_items_all peia, pa_tasks pt where pdra.released_date is not null and pdra.project_id=pcrdla.project_id and pdra.draft_revenue_num=pcrdla.draft_revenue_num and pcrdla.expenditure_item_id=peia.expenditure_item_id and peia.task_id=pt.task_id group by pcrdla.project_id, pt.top_task_id, decode(:period_basis,'PA',pdra.pa_date,pdra.gl_date) union all select pcera.project_id, pt.top_task_id, decode(:period_basis,'PA',pdra.pa_date,pdra.gl_date) period_date, sum(pcera.projfunc_revenue_amount) revenue, to_number(null) raw_cost, to_number(null) burdened_cost from pa_draft_revenues_all pdra, pa_cust_event_rdl_all pcera, pa_tasks pt where pdra.released_date is not null and pdra.project_id=pcera.project_id and pdra.draft_revenue_num=pcera.draft_revenue_num and pcera.task_id=pt.task_id(+) group by pcera.project_id, pt.top_task_id, decode(:period_basis,'PA',pdra.pa_date,pdra.gl_date) ) y, pa_projects_all ppa, hr_all_organization_units_vl haouv, pa_project_statuses pps, hr_all_organization_units_vl haouv2, pa_tasks pt, pa_implementations_all pia, gl_ledgers gl, gl_periods gp where 1=1 and y.period_date>=:date_from and y.period_date<:date_to+1 and y.project_id=ppa.project_id and ppa.org_id=haouv.organization_id and haouv.organization_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11) and ppa.project_status_code=pps.project_status_code and ppa.carrying_out_organization_id=haouv2.organization_id and y.top_task_id=pt.task_id(+) and ppa.org_id=pia.org_id and pia.set_of_books_id=gl.ledger_id and gl.period_set_name=gp.period_set_name and decode(:period_basis,'PA',pia.pa_period_type,gl.accounted_period_type)=gp.period_type and gp.adjustment_period_flag='N' and y.period_date between gp.start_date and gp.end_date group by haouv.name, ppa.segment1, ppa.name, ppa.project_type, pps.project_status_name, haouv2.name, pt.task_number, pt.task_name, gp.period_year, gp.period_num, gp.period_name, ppa.projfunc_currency_code order by haouv.name, ppa.segment1, pt.task_number, gp.period_year, gp.period_num New report. PA US PA Project Revenue and Cost by Period Revenue, raw cost, burdened cost and margin per project and top task for each GL or PA period, in the project functional currency. Cost is taken from the raw cost distribution lines of all expenditure items, revenue from the released draft revenue of expenditure items and events. Period Basis decides whether amounts are assigned to periods by their GL date and the ledger's accounting periods, or by their PA date and the PA periods. A date range that starts or ends inside a period includes only part of that period. Event revenue entered at project level shows a blank top task. Run over all periods, the totals agree with the inception-to-date actuals of Update Project Summary Amounts as of its last run. The templates Pivot: by Period and Pivot: by Year show projects against periods or fiscal years. Enginatics 1=1 :date_from :date_to :period_basis 1 10 1=1 haouv.name=:operating_unit LOV HR Operating Unit 8E2FF36EDEB979D2E0530100007F1FF2 select hou.name value, null description from hr_operating_units hou where sysdate between hou.date_from and nvl(hou.date_to,sysdate) and (:$flex$.ledger is null or hou.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and hou.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='OU') order by hou.name coalesce(xxen_util.default_operating_unit,xxen_util.previous_parameter_value(:parameter_id)) US Operating Unit 2 20 1=1 ppa.project_type=:project_type LOV custom select distinct ppa.project_type value, null description from pa_projects_all ppa where (:$flex$.operating_unit is null or ppa.org_id in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) order by ppa.project_type US Project Type 3 30 1=1 ppa.project_status_code=:project_status LOV PA Project Status (active) 6B94DA894AA04963BAC8E0DEB7B300F9 select pps.project_status_code id, pps.project_status_name value, null description from pa_project_statuses pps where nvl(pps.end_date_active,sysdate)>=sysdate order by pps.project_status_name US Project Status 4 40 1=1 ppa.segment1=:project LOV PA Project Number 8E2FF36EDEDB79D2E0530100007F1FF2 select ppa.project_number value, ppa.project_type||': '||ppa.name description from ( select ppa.segment1 project_number, ppa.name, ppa.project_type, ppa.org_id, ppa.creation_date from pa_projects_all ppa union all select psm.project_number, psm.project_name, 'PJM Seiban' project_type, psm.operating_unit, psm.creation_date from pjm_seiban_numbers psm ) ppa, hr_all_organization_units_vl haouv where ( xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y' or :$flex$.operating_unit is null and ppa.org_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11) ) and ppa.org_id=haouv.organization_id order by ppa.creation_date desc, ppa.project_number desc US Project 5 50 :period_basis LOV custom select flvv.lookup_code id, flvv.meaning value, null description from fnd_lookup_values_vl flvv where flvv.lookup_type='PA_REP_PERIOD_TYPES' and flvv.view_application_id=275 and flvv.security_group_id=0 and flvv.enabled_flag='Y' order by flvv.lookup_code xxen_util.meaning('GL','PA_REP_PERIOD_TYPES',275) Y US Period Basis GL Period assigns amounts by GL date to the ledger's accounting periods, PA Period by PA date to the PA periods. 6 60 :date_from Date trunc(sysdate,'YYYY') Y US Date From Start of the GL or PA date range, depending on Period Basis. 7 70 :date_to Date trunc(sysdate) Y US Date To End of the GL or PA date range, depending on Period Basis. 36A982E49E9C4DB58161E79419028000 Pivot: by Period Y Projects against periods SYSADMIN 15 BURDENED_COST 12 CURRENCY 16 MARGIN 17 MARGIN_PERCENT 1 OPERATING_UNIT 11 PERIOD 10 PERIOD_NAME 9 PERIOD_YEAR 3 PROJECT_NAME 2 PROJECT_NUMBER 6 PROJECT_ORGANIZATION 5 PROJECT_STATUS 4 PROJECT_TYPE 14 RAW_COST 13 REVENUE 8 TOP_TASK_NAME 7 TOP_TASK_NUMBER BURDENED_COST VALUE 2 SUM CURRENCY ROW 1 MARGIN VALUE 3 SUM OPERATING_UNIT FILTER 1 PERIOD COLUMN 1 PROJECT_NAME ROW 3 PROJECT_NUMBER ROW 2 PROJECT_TYPE FILTER 2 REVENUE VALUE 1 SUM T S Site 3240FEB2D0EB4C579301D9F90F317A4C Pivot: by Year Y Projects against fiscal years SYSADMIN 15 BURDENED_COST 12 CURRENCY 16 MARGIN 17 MARGIN_PERCENT 1 OPERATING_UNIT 11 PERIOD 10 PERIOD_NAME 9 PERIOD_YEAR 3 PROJECT_NAME 2 PROJECT_NUMBER 6 PROJECT_ORGANIZATION 5 PROJECT_STATUS 4 PROJECT_TYPE 14 RAW_COST 13 REVENUE 8 TOP_TASK_NAME 7 TOP_TASK_NUMBER BURDENED_COST VALUE 2 SUM CURRENCY ROW 1 MARGIN VALUE 3 SUM OPERATING_UNIT FILTER 1 PERIOD_YEAR COLUMN 1 PROJECT_NAME ROW 3 PROJECT_NUMBER ROW 2 PROJECT_TYPE FILTER 2 REVENUE VALUE 1 SUM T S Site