Adding two new columns in output

Question

Summary

Content

Hi All,

Here I have a challenge, I have a report which is .rdf, Customer ask me to generate output in EXCEL, For that I have created a new .rtf template and concurrent program by referring same executable as .rdf.

Now Customer want me to add two new columns which is not there in report, here the challenge is without modifying .rdf file how to achieve this?

Can some once please help on this.

Thanks,

Ravi

YGUTTIKONDA · Answer

Ravi,

just to clarify, when you modify the RDF there is no need to touch the layout.. as long as you modify the SQL to include the 2 new columns and generate the new XML file ... using this modify your RTF to include the new columns..

Hope this helps.

-YG

Mdtaylor69 · Answer

There is no other way.  The RDF controls the SQL for the report which controls the columns selected.  If you cannot edit the RDF, you need some other way to add the columns to the template.

You can create a new data definition xml in a few minutes.  Just download any existing one.  Make your SQL with the new columns added by extracting the SQL from the queries section of the RDF.

Paste your custom SQL in the CDATA section:

Then create a temporary view using your SQL like below:

create or replace view xxcep_project_activity_summary as

select xps.project_num,

xps.project_name,

xps.task_number task,

xps.project_num||' '||xps.task_number proj_task,

xps.task_name,

ROUND(NVL(xps.burdened_cost_ptd,0)) mtd_actual,

ROUND(NVL(xps.baseline_burdened_cost_ptd,0)) mtd_budget,

ROUND(NVL((xps.baseline_burdened_cost_ptd - xps.burdened_cost_ptd),0)) mtd_variance,

DECODE(xps.baseline_burdened_cost_ptd,0,NULL,ROUND((xps.baseline_burdened_cost_ptd - xps.burdened_cost_ptd)/xps.baseline_burdened_cost_ptd*100,1)) mtd_variance_percent,

ROUND(NVL(xps.burdened_cost_ytd,0)) ytd_actual,

ROUND(NVL(xps.baseline_burdened_cost_ytd,0)) ytd_budget,

ROUND(NVL((xps.baseline_burdened_cost_ytd - xps.burdened_cost_ytd),0)) ytd_variance,

DECODE(xps.baseline_burdened_cost_ytd,0,NULL,ROUND((xps.baseline_burdened_cost_ytd - xps.burdened_cost_ytd)/xps.baseline_burdened_cost_ytd*100,1)) ytd_variance_percent,

ROUND(NVL(xps.burdened_cost_itd,0)) itd_actual,

ROUND(NVL(xps.baseline_burdened_cost_itd,0)) itd_budget,

ROUND(NVL((xps.baseline_burdened_cost_itd - xps.burdened_cost_itd),0)) itd_variance,

DECODE(xps.baseline_burdened_cost_itd,0,NULL,ROUND((xps.baseline_burdened_cost_itd - xps.burdened_cost_itd)/xps.baseline_burdened_cost_itd*100,1)) itd_variance_percent,

ROUND(NVL(xps.cmt_burdened_cost_itd,0)) cmt_itd_actual,

ROUND(NVL(xps.baseline_burdened_cost_itd,0)) cmt_itd_budget,

ROUND(NVL((xps.baseline_burdened_cost_itd - xps.cmt_burdened_cost_itd),0)) cmt_itd_variance,

DECODE(xps.baseline_burdened_cost_itd,0,NULL,ROUND((xps.baseline_burdened_cost_itd - xps.cmt_burdened_cost_itd)/xps.baseline_burdened_cost_itd*100,1)) cmt_itd_variance_percent,

ROUND(NVL(xps.baseline_burdened_cost_tot,0)) total_cost_budget,

ROUND(NVL(xps.original_burdened_cost_tot,0)) total_original_cost_budget,

ROUND(NVL(xps.baseline_burdened_cost_tot,0)) - ROUND(NVL(xps.cmt_burdened_cost_ptd,0)) - ROUND(NVL(xps.burdened_cost_itd,0)) estimate_to_complete,

DECODE(xps.task_number,'002','Opex','Capex') expense_type,

xps.cost_budget_type_code,

pbt.budget_type,

'0'||SUBSTR(xps.project_name,4,3) dept,

fvt.flex_value_meaning division_code,

fvt.description division_desc,

ftl.description dept_description

from xxcep_pa_status_task_v xps,

pa_budget_types pbt,

fnd_flex_values_tl fvt,

fnd_flex_values_tl ftl,

fnd_flex_value_norm_hierarchy fnh

where xps.cost_budget_type_code = pbt.budget_type_code

and xps.cost_budget_type_code in ('AC','FC')

and fvt.flex_value_meaning like 'DIV%'

and fvt.flex_value_meaning = fnh.parent_flex_value

and '0'||SUBSTR(xps.project_name,4,3) between fnh.child_flex_value_low and fnh.child_flex_value_high

and '0'||SUBSTR(xps.project_name,4,3) = ftl.flex_value_meaning

and fvt.flex_value_id IN (select flex_value_id from fnd_flex_values where flex_value_set_id = (select flex_value_set_id from fnd_flex_value_sets where flex_value_set_name like 'CEP_GL_COA_DEP%'))

and ftl.flex_value_id IN (select flex_value_id from fnd_flex_values where flex_value_set_id = (select flex_value_set_id from fnd_flex_value_sets where flex_value_set_name like 'CEP_GL_COA_DEP%'))

order by xps.project_num, xps.project_name, xps.task_number

Generate the XML element code using the this SQL and paste into the element section of the XML file (paste into notepad and make it a two line SQL):

select '  <element name="'||column_name||'" value="'||column_name||'"/>'

from dba_tab_cols where owner = 'APPS' and table_name = 'XXCEP_PROJECT_ACTIVITY_SUMMARY' order by column_id;

Upload this new data definition to XML Publisher Administrator responsibility by creating a new data definition.

Create a new concurrent program that uses XDODTEXE as the Executable Name and Short Name is the same as the XML Data Definition name.

Raviteja Anne-87956 · Answer

Thanks for the reply, Other than this, is there any other way to achieve the solution? Regards, Ravi