Dyanmic Data Columns Report

Procedure
-----------------------

create or replace procedure XXDDIL_PAEXPDET_PRO(   errbuf       OUT      VARCHAR2,
   retcode      OUT      VARCHAR2,p_proj_id in number)
   is
  
p_task_id number;
v_total number;
v_mon_total number;
p_project_id number:=p_proj_id;   --5001;
v_min_mon number;
v_max_mon number;
v_task_total number;
v_grandtot number;
v_month varchar2(20);
cursor c2 is
select TASK_NUMBER,replace(TASK_NAME,'&','&') TASK_NAME,task_id 
from pa_tasks
where task_id in (select distinct task_id
                         from  pa_expenditure_items_all
                         where project_ID=p_project_id) order by 3;
begin
FND_FILE.PUT_LINE(fnd_file.output,'<?xml version="1.0"?>') ;
FND_FILE.PUT_LINE(fnd_file.output,'<P_MONNAME>') ;

select  min(to_number(to_char(EXPENDITURE_ITEM_DATE,'mm')))
        ,max(to_number(to_char(EXPENDITURE_ITEM_DATE,'mm'))) into v_min_mon,v_max_mon
from  pa_expenditure_items_all
where  project_ID=p_project_id;

for i in c2 loop
p_task_id:=null;
FND_FILE.PUT_LINE(fnd_file.output,'<G_MONNAME>') ;
FND_FILE.PUT_LINE(fnd_file.output,'<task_id>'||i.task_id||'</task_id>');
FND_FILE.PUT_LINE(fnd_file.output,'<task_number>'||i.TASK_NUMBER||'</task_number>');
FND_FILE.PUT_LINE(fnd_file.output,'<task_name>'||i.TASK_NAME||'</task_name>');
p_task_id:=i.task_id;


v_task_total:=0;
v_mon_total:=0;
v_total:=0;


for j in v_min_mon..v_max_mon loop
begin
select distinct to_char(EXPENDITURE_ITEM_DATE,'MON-YYYY') into v_month
from pa_expenditure_items_all
where  to_number(to_char(EXPENDITURE_ITEM_DATE,'mm'))=j;

select sum(raw_cost)  total into v_total
from pa_expenditure_items_all
where  to_number(to_char(EXPENDITURE_ITEM_DATE,'mm'))=j  and  task_id=p_task_id ;

FND_FILE.PUT_LINE(fnd_file.output,'<G_TAKRAW width="15">') ;
FND_FILE.PUT_LINE(fnd_file.output,'<MONNAME>'||v_month||'</MONNAME>') ;
FND_FILE.PUT_LINE(fnd_file.output,'<RAWCOST>'||v_total||'</RAWCOST>') ;

FND_FILE.PUT_LINE(fnd_file.output,'</G_TAKRAW>') ;
Exception
when no_data_found then
FND_FILE.PUT_LINE(fnd_file.output,'<G_TAKRAW width="15">') ;
FND_FILE.PUT_LINE(fnd_file.output,'<MONNAME>'||v_month||'</MONNAME>') ;
FND_FILE.PUT_LINE(fnd_file.output,'<RAWCOST></RAWCOST>') ;
FND_FILE.PUT_LINE(fnd_file.output,'</G_TAKRAW>') ;
end;
end loop;
begin
select sum(RAW_COST) into v_task_total
from pa_expenditure_items_all
where task_id=p_task_id;
FND_FILE.PUT_LINE(fnd_file.output,'<TASKTOT>'||v_task_total||'</TASKTOT>');
Exception
when no_data_found then
FND_FILE.PUT_LINE(fnd_file.output,'<TASKTOT>'||0||'</TASKTOT>');
end;
FND_FILE.PUT_LINE(fnd_file.output,'</G_MONNAME>') ;
end loop;


FND_FILE.PUT_LINE(fnd_file.output,'<G_MONTOTAL>') ;
for r in v_min_mon..v_max_mon loop
v_mon_total:=0;
v_grandtot:=0;
FND_FILE.PUT_LINE(fnd_file.output,'<G_MONTOT>') ;
select sum(raw_cost) into v_mon_total from pa_expenditure_items_all 
where project_id=p_project_id
and to_number(to_char(EXPENDITURE_ITEM_DATE,'mm'))=r;
FND_FILE.PUT_LINE(fnd_file.output,'<MONTOT>'||v_mon_total||'</MONTOT>') ;
FND_FILE.PUT_LINE(fnd_file.output,'</G_MONTOT>') ;
end loop;
select sum(raw_cost) into v_grandtot
from pa_expenditure_items_all
where project_id=p_project_id;
FND_FILE.PUT_LINE(fnd_file.output,'<GRATOT>'||v_grandtot||'</GRATOT>') ; 
FND_FILE.PUT_LINE(fnd_file.output,'</G_MONTOTAL>') ;
FND_FILE.PUT_LINE(fnd_file.output,'</P_MONNAME>') ;
end;

<?for-each:TestScores?>  <?TestCategory?>

  <?split-column-header:TestScore?>  <?split-column-width:@width?>  <?TestScoreRange?>


<?split-column-data:TestScore?>  <?NumOfStudents?>  <?end?>







RTF
------------------

Task ID	Task Name	Task Number	MONNAME	Total
width	task_name	task_number	RAWCOST	TAK

Month Total

MONTOT

RA

    

Width-----        <?for-each:G_MONNAME?>  <?task_id?>

Task_name-----<?task_name?>

Task-----<?task_number?>

MONNAM---<?split-column-data:G_TAKRAW?>  <?RAWCOST?> <?end?>

Total---  <?TASKTOT?>

MONTOT-------<?split-column-data:G_MONTOT?>  <?MONTOT?> <?end?>

RA-------<?GRATOT?>