Birt material

http://www.birt-exchange.org/documentation/BIRT_231/wwhelp/wwhimpl/js/html/wwhelp.htm#href=multipage.18.3.html

Birt demo links

http://www.birt-exchange.com/be/demos/birt-report-designers/#

Birt Reporting useful links

http://download.birt-exchange.com/products/demos/birt/birt2_2/birt_2.2_index_small.html


http://www.birt-exchange.org/wiki/Connecting_to_Data_in_the_BIRT_Designer/

http://www.birt-exchange.com/be/products/birt-report-designers/birt-report-designer-options/

http://www.birt-exchange.org/forum/designing-birt-reports/


http://www.eclipse.org/birt/phoenix/

Default value in Select Value in Oracle database

create table (id number,crdate date default sysdate)

Assigning a default value to an attribute in Applcation Designer

To define a field default value using the Default Value control:
1 In the Application Designer, open an application in the Workspace tab.
2 Open the Control Palette.
Defining a Default Filter for a Table
20 Application Developer Guide
3 Drag the Default Value control into the section that contains the field for
which you want to assign a default value.
If you do not see the Default Value control in the section, click Select Action >
Toggle Show All Controls.
4 Open Control Properties for the control, and perform the following actions:
a Enter the name of the attribute bound to the field to which you are
assigning the default value.
b Enter the desired default value in the Value field.
c Select Insert for the Default Type
5 Click Save.

Stored Procedure using Ref Cursor --02

Package Body

CREATE OR REPLACE PACKAGE BODY NET_TOT_CAL
IS
procedure NET_TOT_TICKET(p_start_date in date
,p_end_date in date
,p_ownerposition in varchar2
,p_owner in varchar2
,resultset in out nocopy ticket_out)
is
--p_ticketid varchar2(200):='SRQ16237';
t_start_date date;
t_end_date date;
--p_owner varchar2(200):='E003738';
i number;
v_net_date varchar2(200):=0;
v_time varchar2(200);
v_hours varchar2(20);
v_minutes varchar2(20);
v_seconds varchar2(20);
v_mseconds varchar2(20);
v_check_date number;
v_hours_tot varchar2(20);
v_minutes_tot varchar2(20);
v_seconds_tot varchar2(20);
v_mseconds_tot number;
v_tot_date varchar2(200):=0;
--v_display_name varchar2(200);
Tab NET_TOT_CAL.TABLE1;
Tab1 NET_TOT_CAL.TABLE1;
v_workhours number;
v_workdate date;
v_starttime date;
v_display_name varchar2(200);
v_endtime date;
t_stmt number;
v_workhours1 number;
v_workdate1 date;
v_starttime1 date;
v_endtime1 date;
v_display number;
v_changedate_next varchar2(200);
begin
i:=0;
t_stmt:=1;
v_display:=0;
delete from test_ticket;
commit;
for main_rec in (select xt.*
,xp.displayname
,xpc.shiftnum
,xpc.calnum
from ticket xt
,xx_person xp
,xx_personcal xpc
where xt.status='CLOSED'
and to_date(trunc(actualfinish),'DD-MON-RRRR') between to_date(p_start_date,'DD-MON-RRRR') and to_date(p_end_date,'DD-MON-RRRR')
and xt.owner=xp.personid
and xp.personid=xpc.personid
-- and ticketid=t_ticketid
and actualfinish is not null
and NVL(xt.ownergroup,'X')= Coalesce(p_ownerposition,xt.ownergroup,'X')
and xp.displayname=nvl(p_owner,xp.displayname)
)
Loop
t_stmt:=2;
v_net_date:=0;
v_tot_date:=0;
/* for rec in (select e.status
,e.tkstatusid
,e.changedate
,e.ticketid
,(select min(d.status)
from tkstatus d
where d.tkstatusid > e.tkstatusid
and e.ticketid = d.ticketid
and d.tkstatusid = ((select min(d.tkstatusid)
from tkstatus d
where d.tkstatusid > e.tkstatusid
and e.ticketid = d.ticketid))) next_status
,(select min(d.tkstatusid)
from tkstatus d
where d.tkstatusid > e.tkstatusid
and e.ticketid = d.ticketid) next_tkstatusid
,(select min(d.changedate)
from tkstatus d
where d.tkstatusid>e.tkstatusid
and e.ticketid=d.ticketid) changedate_next
,xp.shiftnum
,xp.calnum
from tkstatus e
,xx_personcal xp
where e.ticketid=main_rec.ticketid
and e.owner=main_rec.owner
and e.owner=xp.personid
order by 2
)*/
for rec in (select e.status
,e.tkstatusid
,e.changedate changedate
,e.ticketid
,lead(status) over (order by tkstatusid) next_status
,lead(tkstatusid) over(order by tkstatusid) next_tkstatusid
,lead(changedate) over(order by tkstatusid) changedate_next
from tkstatus e
where e.ticketid=main_rec.ticketid
order by 2
)
Loop
Tab.delete;
Tab1.delete;
--------------for calculating Net time--------------------
t_stmt:=3;
if rec.status in ('INPROG','QUEUED') then
t_stmt:=4;
--v_changedate_next:=to_char(rec.changedate_next,'DD-MON-RRRR HH:MI:SS PM');
NET_TOT_CAL.date_cal(TO_DATE(rec.changedate,'DD-MON-RRRR HH:MI:SS PM'),TO_DATE(rec.changedate_next,'DD-MON-RRRR HH:MI:SS PM'),Tab);
if tab.count>0 then
t_stmt:=5;
For k in tab.first..tab.last
Loop
if tab(k).next_date is not null then
t_stmt:=6;
Begin
select workhours
,to_date(workdate,'DD-MON-RRRR HH:MI:SS AM')
,starttime
,endtime
into v_workhours
,v_workdate
,v_starttime
,v_endtime
from xx_workperiod
where calnum=main_rec.calnum
and shiftnum=main_rec.shiftnum
and trunc(workdate)=trunc(TO_DATE(tab(k).next_date,'DD-MON-RRRR HH:MI:SS PM'));
Exception
when no_data_found then
dbms_output.put_line('No data exists in work period table for :'||main_rec.calnum||' and '||main_rec.shiftnum||' for the date :'||tab(k).next_date);
when too_many_rows then
dbms_output.put_line('Multiple data exists in work period table for :'||main_rec.calnum||' and '||main_rec.shiftnum||' for the date :'||tab(k).next_date);
End;

t_stmt:=7;
-- dbms_output.put_line(tab(k).next_date);
t_stmt:=8;
if v_workhours<>0 then
t_stmt:=9;
if to_date(v_workdate,'DD-MON-RRRR HH:MI:SS AM')-to_date(tab(k).next_date,'DD-MON-RRRR HH:MI:SS PM')=0 then
v_net_date:=v_net_date+v_workhours;--((to_date(tab(k).next_date,'DD-MON-RRRR HH:MI:SS PM')-to_date(v_workdate,'DD-MON-RRRR HH:MI:SS AM'))*24);
-- dbms_output.put_line('v_net_date if : '||v_net_date||'---------'||v_workdate||'---------------'||tab(K).next_date);
else
v_net_date:=v_net_date+((to_date(v_workdate,'DD-MON-RRRR HH:MI:SS AM')-to_date(tab(k).next_date,'DD-MON-RRRR HH:MI:SS PM'))*24);
-- dbms_output.put_line('v_net_date else : '||v_net_date||'---------'||v_workdate);
end if;
end if;
else
v_net_date:=v_net_date+((tab(k).next_hrs)*24);
-- dbms_output.put_line('v_net_date final : '||v_net_date||'---------'||v_workdate);
end if;
End Loop;
End If;
End If;

--------------for calculating total time--------------------

if rec.status in ('OPEN','PENDING','RESOLVED','SCHEDULED','INPROG','QUEUED','NEW') and rec.next_status<>'CLOSED' and rec.changedate_next is not null then

t_stmt:=10;
--v_changedate_next:=to_char(rec.changedate_next,'DD-MON-RRRR HH:MI:SS PM');
NET_TOT_CAL.date_cal(TO_DATE(rec.changedate,'DD-MON-RRRR HH:MI:SS PM'),TO_DATE(rec.changedate_next,'DD-MON-RRRR HH:MI:SS PM'),Tab1);
if Tab1.count>0 then
t_stmt:=11;
For k in Tab1.first..Tab1.last
Loop
if Tab1(k).next_date is not null then
t_stmt:=12;
Begin
select workhours
,to_date(workdate,'DD-MON-RRRR HH:MI:SS AM')
,starttime
,endtime
into v_workhours1
,v_workdate1
,v_starttime1
,v_endtime1
from xx_workperiod
where calnum=main_rec.calnum
and shiftnum=main_rec.shiftnum
and trunc(workdate)=trunc(TO_DATE(Tab1(k).next_date,'DD-MON-RRRR HH:MI:SS PM'));
Exception
when no_data_found then
dbms_output.put_line('No data exists in work period table for :'||main_rec.calnum||' and '||main_rec.shiftnum||' for the date :'||Tab1(k).next_date);
when too_many_rows then
dbms_output.put_line('Multiple data exists in work period table for :'||main_rec.calnum||' and '||main_rec.shiftnum||' for the date :'||Tab1(k).next_date);
End;

t_stmt:=13;
-- dbms_output.put_line(tab(k).next_date);
t_stmt:=14;
if v_workhours1<>0 then
t_stmt:=15;
if to_date(v_workdate1,'DD-MON-RRRR HH:MI:SS AM')-to_date(Tab1(k).next_date,'DD-MON-RRRR HH:MI:SS PM')=0 then
v_tot_date:=v_tot_date+v_workhours1;--((to_date(tab(k).next_date,'DD-MON-RRRR HH:MI:SS PM')-to_date(v_workdate,'DD-MON-RRRR HH:MI:SS AM'))*24);
-- dbms_output.put_line('v_net_date if : '||v_net_date||'---------'||v_workdate||'---------------'||tab(K).next_date);
else
v_tot_date:=v_tot_date+((to_date(v_workdate1,'DD-MON-RRRR HH:MI:SS AM')-to_date(Tab1(k).next_date,'DD-MON-RRRR HH:MI:SS PM'))*24);
-- dbms_output.put_line('v_net_date else : '||v_net_date||'---------'||v_workdate);
end if;
end if;
else
v_tot_date:=v_tot_date+((Tab1(k).next_hrs)*24);

end if;
End Loop;
End If;
End If;

------------------------------------------------------------------------------------------------------------------------

End Loop;
v_net_date:=v_net_date/24;
v_tot_date:=v_tot_date/24;
select days
,TRUNC(A*24) Hours
,TRUNC(A*24*60 - 60*TRUNC(A*24)) Minutes
,TRUNC(A*24*60*60 - 60*TRUNC(A*24*60)) Seconds
-- ,TRUNC(A*24*60*60*100 - 100*TRUNC(A*24*60*60)) mSeconds
into v_mseconds
,v_hours
,v_minutes
,v_seconds
-- ,v_mseconds
from
(
select
trunc(v_net_date) Days,
v_net_date - trunc(v_net_date) A
from dual
);

select decode(length(v_hours),2,v_hours
,'0'||v_hours)
,decode(length(v_minutes),2,v_minutes
,'0'||v_minutes)
,decode(length(v_seconds),2,v_seconds
,'0'||v_seconds)
into v_hours
,v_minutes
,v_seconds
from dual;


select days
,TRUNC(A*24) Hours
,TRUNC(A*24*60 - 60*TRUNC(A*24)) Minutes
,TRUNC(A*24*60*60 - 60*TRUNC(A*24*60)) Seconds
-- ,TRUNC(A*24*60*60*100 - 100*TRUNC(A*24*60*60)) mSeconds
into v_mseconds_tot
,v_hours_tot
,v_minutes_tot
,v_seconds_tot
-- ,v_mseconds
from
(
select
trunc(v_tot_date) Days,
v_tot_date - trunc(v_tot_date) A
from dual
);


select decode(length(v_hours_tot),2,v_hours_tot
,'0'||v_hours_tot)
,decode(length(v_minutes_tot),2,v_minutes_tot
,'0'||v_minutes_tot)
,decode(length(v_seconds_tot),2,v_seconds_tot
,'0'||v_seconds_tot)
into v_hours_tot
,v_minutes_tot
,v_seconds_tot
from dual;

-----------------------------queries for displaying other attributes---------------------------------------------
--selecting display name
Begin
select displayname
into v_display_name
from xx_person
where personid=main_rec.owner;
Exception
when no_data_found then
v_display_name:=null;
End;
/*
if v_display=0 then
dbms_output.put_line(' SR# NetTime TotalTime Last Name Userid Location Call Type Status Priority Created Date Status Date Created By ');
dbms_output.put_line('------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------');
v_display:=2;
end if;
dbms_output.put_line(main_rec.ticketid||' '||v_mseconds||'D'||v_hours||':'||v_minutes||':'||v_seconds||' '||v_mseconds_tot||'D'||v_hours_tot||':'||v_minutes_tot||':'||v_seconds_tot||' '||main_rec.displayname||' '||main_rec.affectedperson||' '||main_rec.location||' '||main_rec.externalsystem||' '||main_rec.status||' '||main_rec.internalpriority||' '||main_rec.creationdate||' '||main_rec.statusdate||' '||main_rec.createdby);
*/
insert into TEST_TICKET
values(main_rec.ticketid,v_mseconds||'D'||v_hours||':'||v_minutes||':'||v_seconds,v_mseconds_tot||'D'||v_hours_tot||':'||v_minutes_tot||':'||v_seconds_tot,v_display_name,main_rec.affectedperson,main_rec.location,main_rec.externalsystem,main_rec.status,main_rec.internalpriority,main_rec.creationdate,main_rec.statusdate,main_rec.createdby);

End Loop;
commit;
OPEN RESULTSET FOR
SELECT * from TEST_TICKET;

exception
when others then
dbms_output.put_line('Error at stmt: '|| t_stmt||Sqlerrm);End NET_TOT_TICKET;

PROCEDURE DATE_CAL( P_DATE1 IN DATE
,P_DATE2 IN DATE
,P_TAB OUT TABLE1
)
IS
/*-- date1 date:=to_date('11-MAR-2009 08.35.15 PM','DD-MON-RRRR HH:MI:SS PM');
-- date2 date:=to_date('24-MAR-2009 09.34.15 PM','DD-MON-RRRR HH:MI:SS PM');
day1 varchar2(20);
date3 date;
date4 number;
v_check number:=0;
k number;
j number;
Begin
j:=0;
LOOP
if v_check=0 and p_date2-p_date1>=1 then
date3:=p_date1;
--DBMS_OUTPUT.PUT_LINE('DATE3 IS:'||to_char(DATE3,'DD-MON-RRRR HH:MI:SS PM'));
j:=j+1;
P_TAB(j).Next_Date:=to_char(date3,'DD-MON-RRRR HH:MI:SS PM');
v_check:=1;
end if;
if p_date2-nvl(date3,p_date1)<1 then
if nvl(date3,p_date1)-p_date2<0 then
date4:=p_date2-nvl(date3,p_date1);
else
date4:=nvl(date3,p_date1)-p_date2;
end if;
--dbms_output.put_line('date4 is:'||date4);
j:=j+1;
P_TAB(j).Next_Hrs:=date4;
else
DATE3:=nvl(date3,p_date1)+1;
--DBMS_OUTPUT.PUT_LINE('DATE3 IS:'||to_char(DATE3,'DD-MON-RRRR HH:MI:SS PM'));
j:=j+1;
P_TAB(j).Next_Date:=to_char(date3,'DD-MON-RRRR HH:MI:SS PM');
end if;
exit when date4>0 or date3-p_date2=0;
END LOOP;
/*
for k in p_tab.first..p_tab.last
loop
DBMS_OUTPUT.PUT_LINE('Date is:'||p_tab(k).next_date);
end loop;
*/
/*
Type rec is record
(Next_Date varchar2(200),
Next_Hrs Number
);
Type Table1 is Table
of rec index by Binary_integer;

p_TAB Table1;

p_date1 date:=to_date('11-MAR-2009 08.35.15 PM','DD-MON-RRRR HH:MI:SS PM');
p_date2 date:=to_date('24-MAR-2009 08.55.15 PM','DD-MON-RRRR HH:MI:SS PM');
*/
day1 varchar2(20);
date3 date;
date4 number;
v_check number:=0;
k number;
j number;
v_exit number:=0;
Begin
j:=0;
LOOP
if v_check=0 and p_date2-p_date1>=1 then
date3:=trunc(p_date1)+1;
-- DBMS_OUTPUT.PUT_LINE('DATE3 IS:'||to_char(DATE3,'DD-MON-RRRR HH:MI:SS PM'));
-- j:=j+1;
-- TAB(j).Next_Date:=to_char(date3,'DD-MON-RRRR HH:MI:SS PM');
v_check:=1;
if date3>p_date1 then
date4:=date3-p_date1;
--dbms_output.put_line('DATE4 is:'||date4);
j:=j+1;
P_TAB(j).Next_Hrs:=date4;

end if;
end if;

if p_date2-nvl(date3,p_date1)<1 then
if nvl(date3,p_date1)-p_date2<0 then
date4:=p_date2-nvl(date3,p_date1);
v_exit:=1;
else
date4:=nvl(date3,p_date1)-p_date2;
v_exit:=1;
end if;
-- dbms_output.put_line('date4 is:'||date4);
j:=j+1;
P_TAB(j).Next_Hrs:=date4;
else
DATE3:=nvl(date3,p_date1)+1;
--DBMS_OUTPUT.PUT_LINE('DATE3 IS:'||to_char(DATE3,'DD-MON-RRRR HH:MI:SS PM'));
j:=j+1;
P_TAB(j).Next_Date:=to_char(date3,'DD-MON-RRRR HH:MI:SS PM');
end if;
exit when v_exit=1;
END LOOP;
/*
for k in p_tab.first..tab.last
loop
DBMS_OUTPUT.PUT_LINE('Date is:'||p_tab(k).next_date||'---------------'||p_tab(k).next_hrs);
end loop;
*/
end date_cal;

END NET_TOT_CAL;
/

Stored Procedure using Ref cursor --01

Normal Table:


CREATE TABLE TEST_TICKET
( SR_NO VARCHAR2(240),
NET_TIME VARCHAR2(240),
TOTAL_TIME VARCHAR2(240),
LAST_NAME VARCHAR2(240),
USERID VARCHAR2(240),
LOCATION VARCHAR2(240),
CALLTYPE VARCHAR2(240),
STATUS VARCHAR2(240),
PRIORITY VARCHAR2(240),
CREATEDDATE VARCHAR2(240),
STATUSDATE VARCHAR2(240),
CREATEDBY VARCHAR2(240)
)




Droping a table :

drop table TEST_TICKET

select * from TEST_TICKET

truncate table test_ticket.



Package specification...

CREATE OR REPLACE PACKAGE NET_TOT_CAL
IS

TYPE TICKET_OUT IS REF CURSOR RETURN TEST_TICKET%ROWTYPE;

TYPE REC IS RECORD
(NEXT_DATE VARCHAR2(200),
NEXT_HRS NUMBER
);
TYPE TABLE1 IS TABLE
OF REC INDEX BY BINARY_INTEGER;

PROCEDURE NET_TOT_TICKET (P_START_DATE IN DATE
,P_END_DATE IN DATE
,P_OWNERPOSITION IN VARCHAR2
,P_OWNER IN VARCHAR2
,RESULTSET IN OUT NOCOPY TICKET_OUT);

PROCEDURE DATE_CAL( P_DATE1 IN DATE
,P_DATE2 IN DATE
,P_TAB OUT TABLE1
);

END NET_TOT_CAL;
/
SHOW ERR;




Procedure to display output:


CREATE OR REPLACE PACKAGE NET_TOT_CAL
IS

TYPE TICKET_OUT IS REF CURSOR RETURN TEST_TICKET%ROWTYPE;

TYPE REC IS RECORD
(NEXT_DATE VARCHAR2(200),
NEXT_HRS NUMBER
);
TYPE TABLE1 IS TABLE
OF REC INDEX BY BINARY_INTEGER;

PROCEDURE NET_TOT_TICKET (P_START_DATE IN DATE
,P_END_DATE IN DATE
,P_OWNERPOSITION IN VARCHAR2
,P_OWNER IN VARCHAR2
,RESULTSET IN OUT NOCOPY TICKET_OUT);

PROCEDURE DATE_CAL( P_DATE1 IN DATE
,P_DATE2 IN DATE
,P_TAB OUT TABLE1
);

END NET_TOT_CAL;
/
SHOW ERR;

Asterick next to a field.

Go to application designer Application and select the application(Eg: Asset) where you wanna change the settings of the fields and there select the fields and in the properties keep Required = true.

Path to check the settings that control the size of the fields in Appication (Application Designer)

The settings are kept in the file:
\applications\maximo\maximouiweb\webmodule
\webclient\common\fieldsizegroups.jsp


For string fields (ALN, UPPER, LOWER), the defined sizes are:
􀁔 If the database size is 1 or 2 characters, then the field length displayed on a
screen is 2.
􀁔 If the database size is 3 through 30 characters, then the field length
displayed on a screen is 10.
􀁔 If the database size is greater than 30 characters, then the field length
displayed on a screen is 40.

Deleting an User Created Application in Application Desginer

To delete an application, open a database editor and complete the following steps:
1 Login as an administrator.
2 Type the following commands, and replace the variable
with the name of the application you want to delete.
Enter the application name in all uppercase. For example, if the name of the
application you want to delete is Test, replace APPLICATION NAME with TEST
in all the commands.
delete from maxapps where app='';
delete from maxpresentation where app='';
delete from sigoption where app='';
delete from applicationauth where app='';
delete from maxlabels where app='';
delete from maxmenu where moduleapp='' and menutype
!='Module';
delete from maxmenu where moduleapp='' and
elementtype='APP' and keyvalue='';
delete from appdoctype where app= ';
3 Logout of the database editor.

Path to Open class files in Cron task

path to open class files in cron task : C:\IBM\SMP\maximo\applications\maximo\businessobjects\classes\psdi\app

http://10.10.110.24:9080/maximo
http://10.10.110.25:9060/console

My Final Version Code

CREATE OR REPLACE PACKAGE XX_NET_TOT_CAL
IS
PROCEDURE NET_TOT_TICKET (P_START_DATE IN DATE
,P_END_DATE IN DATE
,P_OWNER IN VARCHAR2);
TYPE REC IS RECORD
(NEXT_DATE VARCHAR2(200),
NEXT_HRS NUMBER
);
TYPE TABLE1 IS TABLE
OF REC INDEX BY BINARY_INTEGER;

PROCEDURE DATE_CAL( P_DATE1 IN DATE
,P_DATE2 IN DATE
,P_TAB OUT TABLE1
);

END XX_NET_TOT_CAL;
/
SHOW ERR;



Next Execute the following code...


CREATE OR REPLACE PACKAGE BODY XX_NET_TOT_CAL
IS
procedure NET_TOT_TICKET(p_start_date in date
,p_end_date in date
,p_owner in varchar2)
is
--p_ticketid varchar2(200):='SRQ16237';
t_start_date date;
t_end_date date;
--p_owner varchar2(200):='E003738';
i number;
v_net_date varchar2(200):=0;
v_time varchar2(200);
v_hours number;
v_minutes number;
v_seconds number;
v_mseconds number;
v_check_date number;
v_hours_tot number;
v_minutes_tot number;
v_seconds_tot number;
v_mseconds_tot number;
v_tot_date varchar2(200):=0;
Tab XX_NET_TOT_CAL.TABLE1;
Tab1 XX_NET_TOT_CAL.TABLE1;
v_workhours number;
v_workdate date;
v_starttime date;
v_display_name varchar2(200);
v_display_name1 varchar2(200);
v_endtime date;
t_stmt number;
v_workhours1 number;
v_workdate1 date;
v_starttime1 date;
v_endtime1 date;
v_display number;
begin
i:=0;
t_stmt:=1;
v_display:=0;
for main_rec in (select *
from ticket
where status='CLOSED'
and to_date(trunc(actualfinish),'DD-MON-RRRR') between to_date(p_start_date,'DD-MON-RRRR') and to_date(p_end_date,'DD-MON-RRRR')
and owner=nvl(p_owner,owner)
and actualfinish is not null
-- and ticketid=t_ticketid
)
Loop
t_stmt:=2;
v_net_date:=0;
v_tot_date:=0;
/* for rec in (select e.status
,e.tkstatusid
,e.changedate
,e.ticketid
,(select min(d.status)
from tkstatus d
where d.tkstatusid > e.tkstatusid
and e.ticketid = d.ticketid
and d.tkstatusid = ((select min(d.tkstatusid)
from tkstatus d
where d.tkstatusid > e.tkstatusid
and e.ticketid = d.ticketid))) next_status
,(select min(d.tkstatusid)
from tkstatus d
where d.tkstatusid > e.tkstatusid
and e.ticketid = d.ticketid) next_tkstatusid
,(select min(d.changedate)
from tkstatus d
where d.tkstatusid>e.tkstatusid
and e.ticketid=d.ticketid) changedate_next
,xp.shiftnum
,xp.calnum
from tkstatus e
,xx_personcal xp
where e.ticketid=main_rec.ticketid
and e.owner=main_rec.owner
and e.owner=xp.personid
order by 2
)*/
for rec in (select e.status
,e.tkstatusid
,e.changedate
,e.ticketid
,lead(status) over (order by tkstatusid) next_status
,lead(tkstatusid) over(order by tkstatusid) next_tkstatusid
,lead(changedate) over(order by tkstatusid) changedate_next
,xp.shiftnum
,xp.calnum
from tkstatus e
,xx_personcal xp
where e.ticketid=main_rec.ticketid
and e.owner=main_rec.owner
and e.owner=xp.personid
order by 2
)
Loop
Tab.delete;
Tab1.delete;
--------------for calculating Net time--------------------
t_stmt:=3;
if rec.status = 'INPROG' then
t_stmt:=4;
XX_NET_TOT_CAL.date_cal(TO_DATE(rec.changedate,'DD-MON-RRRR HH:MI:SS PM'),TO_DATE(rec.changedate_next,'DD-MON-RRRR HH:MI:SS PM'),Tab);
if tab.count>0 then
t_stmt:=5;
For k in tab.first..tab.last
Loop
if tab(k).next_date is not null then
t_stmt:=6;
Begin
select workhours
,to_date(workdate,'DD-MON-RRRR HH:MI:SS AM')
,starttime
,endtime
into v_workhours
,v_workdate
,v_starttime
,v_endtime
from xx_workperiod
where calnum=rec.calnum
and shiftnum=rec.shiftnum
and trunc(workdate)=trunc(TO_DATE(tab(k).next_date,'DD-MON-RRRR HH:MI:SS PM'));
Exception
when no_data_found then
dbms_output.put_line('No data exists in work period table for :'||rec.calnum||' and '||rec.shiftnum||' for the date :'||tab(k).next_date);
when too_many_rows then
dbms_output.put_line('Multiple data exists in work period table for :'||rec.calnum||' and '||rec.shiftnum||' for the date :'||tab(k).next_date);
End;

t_stmt:=7;
-- dbms_output.put_line(tab(k).next_date);
t_stmt:=8;
if v_workhours<>0 then
t_stmt:=9;
if to_date(v_workdate,'DD-MON-RRRR HH:MI:SS AM')-to_date(tab(k).next_date,'DD-MON-RRRR HH:MI:SS PM')=0 then
v_net_date:=v_net_date+v_workhours;--((to_date(tab(k).next_date,'DD-MON-RRRR HH:MI:SS PM')-to_date(v_workdate,'DD-MON-RRRR HH:MI:SS AM'))*24);
-- dbms_output.put_line('v_net_date if : '||v_net_date||'---------'||v_workdate||'---------------'||tab(K).next_date);
else
v_net_date:=v_net_date+((to_date(v_workdate,'DD-MON-RRRR HH:MI:SS AM')-to_date(tab(k).next_date,'DD-MON-RRRR HH:MI:SS PM'))*24);
-- dbms_output.put_line('v_net_date else : '||v_net_date||'---------'||v_workdate);
end if;
end if;
else
v_net_date:=v_net_date+((tab(k).next_hrs)*24);
-- dbms_output.put_line('v_net_date final : '||v_net_date||'---------'||v_workdate);
end if;
End Loop;
End If;
End If;

--------------for calculating total time--------------------

if rec.status in ('OPEN','PENDING','RESOLVED','SCHEDULED','INPROG','QUEUED','NEW') and rec.changedate_next is not null then

t_stmt:=10;
XX_NET_TOT_CAL.date_cal(TO_DATE(rec.changedate,'DD-MON-RRRR HH:MI:SS PM'),TO_DATE(rec.changedate_next,'DD-MON-RRRR HH:MI:SS PM'),Tab1);
if Tab1.count>0 then
t_stmt:=11;
For k in Tab1.first..Tab1.last
Loop
if Tab1(k).next_date is not null then
t_stmt:=12;
Begin
select workhours
,to_date(workdate,'DD-MON-RRRR HH:MI:SS AM')
,starttime
,endtime
into v_workhours1
,v_workdate1
,v_starttime1
,v_endtime1
from xx_workperiod
where calnum=rec.calnum
and shiftnum=rec.shiftnum
and trunc(workdate)=trunc(TO_DATE(Tab1(k).next_date,'DD-MON-RRRR HH:MI:SS PM'));
Exception
when no_data_found then
dbms_output.put_line('No data exists in work period table for :'||rec.calnum||' and '||rec.shiftnum||' for the date :'||Tab1(k).next_date);
when too_many_rows then
dbms_output.put_line('Multiple data exists in work period table for :'||rec.calnum||' and '||rec.shiftnum||' for the date :'||Tab1(k).next_date);
End;

t_stmt:=13;
-- dbms_output.put_line(tab(k).next_date);
t_stmt:=14;
if v_workhours1<>0 then
t_stmt:=15;
if to_date(v_workdate1,'DD-MON-RRRR HH:MI:SS AM')-to_date(Tab1(k).next_date,'DD-MON-RRRR HH:MI:SS PM')=0 then
v_tot_date:=v_tot_date+v_workhours1;--((to_date(tab(k).next_date,'DD-MON-RRRR HH:MI:SS PM')-to_date(v_workdate,'DD-MON-RRRR HH:MI:SS AM'))*24);
-- dbms_output.put_line('v_net_date if : '||v_net_date||'---------'||v_workdate||'---------------'||tab(K).next_date);
else
v_tot_date:=v_tot_date+((to_date(v_workdate1,'DD-MON-RRRR HH:MI:SS AM')-to_date(Tab1(k).next_date,'DD-MON-RRRR HH:MI:SS PM'))*24);
-- dbms_output.put_line('v_net_date else : '||v_net_date||'---------'||v_workdate);
end if;
end if;
else
v_tot_date:=v_tot_date+((Tab1(k).next_hrs)*24);

end if;
End Loop;
End If;
End If;

------------------------------------------------------------------------------------------------------------------------

End Loop;
v_net_date:=v_net_date/24;
v_tot_date:=v_tot_date/24;
select days
,TRUNC(A*24) Hours
,TRUNC(A*24*60 - 60*TRUNC(A*24)) Minutes
,TRUNC(A*24*60*60 - 60*TRUNC(A*24*60)) Seconds
-- ,TRUNC(A*24*60*60*100 - 100*TRUNC(A*24*60*60)) mSeconds
into v_mseconds
,v_hours
,v_minutes
,v_seconds
-- ,v_mseconds
from
(
select
trunc(v_net_date) Days,
v_net_date - trunc(v_net_date) A
from dual
);


select days
,TRUNC(A*24) Hours
,TRUNC(A*24*60 - 60*TRUNC(A*24)) Minutes
,TRUNC(A*24*60*60 - 60*TRUNC(A*24*60)) Seconds
-- ,TRUNC(A*24*60*60*100 - 100*TRUNC(A*24*60*60)) mSeconds
into v_mseconds_tot
,v_hours_tot
,v_minutes_tot
,v_seconds_tot
-- ,v_mseconds
from
(
select
trunc(v_tot_date) Days,
v_tot_date - trunc(v_tot_date) A
from dual
);

-----------------------------queries for displaying other attributes---------------------------------------------
--selecting display name
Begin
select displayname
into v_display_name
from xx_person
where personid=main_rec.owner;
Exception
when no_data_found then
v_display_name:=null;
End;
Begin
select displayname
into v_display_name1
from xx_person
where personid=main_rec.createdby;
Exception
when no_data_found then
v_display_name:=null;
End;

if v_display=0 then
dbms_output.put_line(' SR# NetTime TotalTime Last Name Userid Location Call Type Status Priority Created Date Status Date Created By Created By Name ');
dbms_output.put_line('------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------');
v_display:=2;
end if;
dbms_output.put_line(main_rec.ticketid||' '||v_mseconds||'D'||v_hours||':'||v_minutes||':'||v_seconds||' '||v_mseconds_tot||'D'||v_hours_tot||':'||v_minutes_tot||':'||v_seconds_tot||' '||v_display_name||' '||main_rec.affectedperson||' '||main_rec.location||' '||main_rec.externalsystem||' '||main_rec.status||' '||main_rec.internalpriority||' '||main_rec.creationdate||' '||main_rec.statusdate||' '||main_rec.createdby||' '||'v_display_name1');
End Loop;
exception
when others then
dbms_output.put_line('Error at stmt: '|| t_stmt||Sqlerrm);End NET_TOT_TICKET;

PROCEDURE DATE_CAL( P_DATE1 IN DATE
,P_DATE2 IN DATE
,P_TAB OUT TABLE1
)
IS
/*-- date1 date:=to_date('11-MAR-2009 08.35.15 PM','DD-MON-RRRR HH:MI:SS PM');
-- date2 date:=to_date('24-MAR-2009 09.34.15 PM','DD-MON-RRRR HH:MI:SS PM');
day1 varchar2(20);
date3 date;
date4 number;
v_check number:=0;
k number;
j number;
Begin
j:=0;
LOOP
if v_check=0 and p_date2-p_date1>=1 then
date3:=p_date1;
--DBMS_OUTPUT.PUT_LINE('DATE3 IS:'||to_char(DATE3,'DD-MON-RRRR HH:MI:SS PM'));
j:=j+1;
P_TAB(j).Next_Date:=to_char(date3,'DD-MON-RRRR HH:MI:SS PM');
v_check:=1;
end if;
if p_date2-nvl(date3,p_date1)<1 then
if nvl(date3,p_date1)-p_date2<0 then
date4:=p_date2-nvl(date3,p_date1);
else
date4:=nvl(date3,p_date1)-p_date2;
end if;
--dbms_output.put_line('date4 is:'||date4);
j:=j+1;
P_TAB(j).Next_Hrs:=date4;
else
DATE3:=nvl(date3,p_date1)+1;
--DBMS_OUTPUT.PUT_LINE('DATE3 IS:'||to_char(DATE3,'DD-MON-RRRR HH:MI:SS PM'));
j:=j+1;
P_TAB(j).Next_Date:=to_char(date3,'DD-MON-RRRR HH:MI:SS PM');
end if;
exit when date4>0 or date3-p_date2=0;
END LOOP;
/*
for k in p_tab.first..p_tab.last
loop
DBMS_OUTPUT.PUT_LINE('Date is:'||p_tab(k).next_date);
end loop;
*/
/*
Type rec is record
(Next_Date varchar2(200),
Next_Hrs Number
);
Type Table1 is Table
of rec index by Binary_integer;

p_TAB Table1;

p_date1 date:=to_date('11-MAR-2009 08.35.15 PM','DD-MON-RRRR HH:MI:SS PM');
p_date2 date:=to_date('24-MAR-2009 08.55.15 PM','DD-MON-RRRR HH:MI:SS PM');
*/
day1 varchar2(20);
date3 date;
date4 number;
v_check number:=0;
k number;
j number;
v_exit number:=0;
Begin
j:=0;
LOOP
if v_check=0 and p_date2-p_date1>=1 then
date3:=trunc(p_date1)+1;
-- DBMS_OUTPUT.PUT_LINE('DATE3 IS:'||to_char(DATE3,'DD-MON-RRRR HH:MI:SS PM'));
-- j:=j+1;
-- TAB(j).Next_Date:=to_char(date3,'DD-MON-RRRR HH:MI:SS PM');
v_check:=1;
if date3>p_date1 then
date4:=date3-p_date1;
--dbms_output.put_line('DATE4 is:'||date4);
j:=j+1;
P_TAB(j).Next_Hrs:=date4;

end if;
end if;

if p_date2-nvl(date3,p_date1)<1 then
if nvl(date3,p_date1)-p_date2<0 then
date4:=p_date2-nvl(date3,p_date1);
v_exit:=1;
else
date4:=nvl(date3,p_date1)-p_date2;
v_exit:=1;
end if;
-- dbms_output.put_line('date4 is:'||date4);
j:=j+1;
P_TAB(j).Next_Hrs:=date4;
else
DATE3:=nvl(date3,p_date1)+1;
--DBMS_OUTPUT.PUT_LINE('DATE3 IS:'||to_char(DATE3,'DD-MON-RRRR HH:MI:SS PM'));
j:=j+1;
P_TAB(j).Next_Date:=to_char(date3,'DD-MON-RRRR HH:MI:SS PM');
end if;
exit when v_exit=1;
END LOOP;
/*
for k in p_tab.first..tab.last
loop
DBMS_OUTPUT.PUT_LINE('Date is:'||p_tab(k).next_date||'---------------'||p_tab(k).next_hrs);
end loop;
*/
end date_cal;

END XX_NET_TOT_CAL;
/


Final execution code to get output:


begin
XX_NET_TOT_CAL.NET_TOT_TICKET(p_start_date =>to_date('11-MAR-09','DD-MON-RRRR')
,p_end_date =>to_date('27-MAR-09','DD-MON-RRRR')
,p_owner =>null);
exception
when others then
dbms_output.put_line(sqlerrm);
end;

Rename a Table Name

rename table1 to table2;( renaming a table1 name to table2).

My Stored procedure

declare
t_ticketid varchar2(200):='SRQ15654';
i number;
v_net_date varchar2(200):=0;
v_time varchar2(200);
v_hours number;
v_minutes number;
v_seconds number;
v_mseconds number;
begin
i:=0;
for rec in (select e.status
,e.tkstatusid
,e.changedate
,e.ticketid
,(select min(d.status)
from xx_tkstatus d
where d.tkstatusid > e.tkstatusid
and e.ticketid = d.ticketid
and d.tkstatusid = ((select min(d.tkstatusid)
from xx_tkstatus d
where d.tkstatusid > e.tkstatusid
and e.ticketid = d.ticketid))) next_status
,(select min(d.tkstatusid)
from xx_tkstatus d
where d.tkstatusid > e.tkstatusid
and e.ticketid = d.ticketid) next_tkstatusid
,(select min(d.changedate)
from xx_tkstatus d
where d.tkstatusid>e.tkstatusid
and e.ticketid=d.ticketid) changedate_next
from xx_tkstatus e,xx_ticket x
where e.ticketid=x.ticketid
and e.ticketid=t_ticketid
and x.actualfinish is not null
and x.owner is not null
order by 2
)
loop
if rec.status = 'INPROG' then
select (to_date(nvl(rec.changedate_next,sysdate),'DD-MON-RRRR HH:MI:SS PM')-to_date(rec.changedate,'DD-MON-RRRR HH:MI:SS PM')+1)-
(SELECT COUNT(DAYS) FROM (SELECT TO_CHAR(TO_DATE(rec.changedate,'DD-MON-RRRR HH:MI:SS PM')+LEVEL ,'D') DAYS
FROM DUAL CONNECT BY LEVEL < (to_date(nvl(rec.changedate_next,sysdate),'DD-MON-RRRR HH:MI:SS PM')-to_date(rec.changedate,'DD-MON-RRRR HH:MI:SS PM')))
WHERE DAYS IN ('7' ,'1')) DAY
INTO V_TIME
FROM DUAL;
-- dbms_output.put_line('v_time is:'||v_time);
V_NET_DATE:=V_NET_DATE+V_TIME;
end if;
-- dbms_output.put_line(tab(i).tkstatusid||'-'||tab(i).tkstatusid_next||'---'||tab(i).changedate||'----'||tab(i).changedate_next||tab(i).status||'----'||tab(i).status_next);
end loop;

select days
,TRUNC(A*24) Hours
,TRUNC(A*24*60 - 60*TRUNC(A*24)) Minutes
,TRUNC(A*24*60*60 - 60*TRUNC(A*24*60)) Seconds
-- ,TRUNC(A*24*60*60*100 - 100*TRUNC(A*24*60*60)) mSeconds
into v_mseconds
,v_hours
,v_minutes
,v_seconds
-- ,v_mseconds
from
(
select
trunc(v_net_date) Days,
v_net_date - trunc(v_net_date) A
from dual
);

dbms_output.put_line('Days:'||v_mseconds||' Hours :'||v_hours||' Minutes :'||v_minutes||' Seconds :'||v_seconds);

exception
when others then
dbms_output.put_line(Sqlerrm);
end;

Mycode2

CREATE OR REPLACE PROCEDURE F1
(
I_FUND_NO IN VARCHAR2,
I_FUND_NAME IN VARCHAR2,
O_FUND OUT OBJ_FUND_A,
O_FUND1 OUT OBJ_FUND
)

IS
V_FUND OBJ_FUND;

COUNT1 NUMBER;

CURSOR C1 IS

SELECT FUND_NO,FUND_NAME
FROM FUND
WHERE (FUND_NO= I_FUND_NO OR FUND_NAME=I_FUND_NAME) OR (I_FUND_NO IS NULL AND I_FUND_NA
ME IS NULL) ;

BEGIN

O_FUND:= OBJ_FUND_A();

COUNT1:= 0;
FOR FUNDINFO IN C1
LOOP

--CREATE AN OBJECT

V_FUND:= NEW OBJ_FUND(NULL,NULL);
COUNT1:=COUNT1+1;

--SET VALUES TO EMPTY OBJECT

V_FUND.FUND_NO:= FUNDINFO.FUND_NO;
V_FUND.FUND_NAME:=FUNDINFO.FUND_NAME;
O_FUND1:=V_FUND;

--SET OBJECT INTO COLLECTION

O_FUND.EXTEND;
O_FUND(COUNT1):=V_FUND;

END LOOP;

END;

/

Procedure created.

DECLARE
I_FUND_NO VARCHAR2(15);
I_FUND_NAME VARCHAR2(15);
O_FUND OBJ_FUND_A;
O_FUND1 OBJ_FUND;
BEGIN
I_FUND_NO:='&FUND_NO';
I_FUND_NAME:='&FUND_NAME';


SCOTT.F1(I_FUND_NO,I_FUND_NAME,O_FUND,O_FUND1);

IF O_FUND IS NOT NULL AND I_FUND_NO IS NOT NULL OR I_FUND_NAME IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE('FUNDDETAILS::' ||O_FUND1.FUND_NO || '-'||O_FUND1.FUND_NAME);
END IF;
IF O_FUND IS NOT NULL AND I_FUND_NO IS NULL AND I_FUND_NAME IS NULL THEN
BEGIN
FOR I IN 1..O_FUND.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE('FUNDDETAILS::' ||O_FUND(I).FUND_NO ||'-'||O_FUND(I).FU
ND_NAME);
END LOOP;
END;
END IF;
END;
/
Enter value for fund_no: FNO2
old 7: I_FUND_NO:='&FUND_NO';
new 7: I_FUND_NO:='FNO2';
Enter value for fund_name:
old 8: I_FUND_NAME:='&FUND_NAME';
new 8: I_FUND_NAME:='';
FUNDDETAILS::FNO2-FNAME2

PL/SQL procedure successfully completed.

SQL> /
Enter value for fund_no:
old 7: I_FUND_NO:='&FUND_NO';
new 7: I_FUND_NO:='';
Enter value for fund_name: FNAME3
old 8: I_FUND_NAME:='&FUND_NAME';
new 8: I_FUND_NAME:='FNAME3';
FUNDDETAILS::FNO3-FNAME3

PL/SQL procedure successfully completed.

SQL> /
Enter value for fund_no:
old 7: I_FUND_NO:='&FUND_NO';
new 7: I_FUND_NO:='';
Enter value for fund_name:
old 8: I_FUND_NAME:='&FUND_NAME';
new 8: I_FUND_NAME:='';
FUNDDETAILS::FNO1-FNAME1
FUNDDETAILS::FNO2-FNAME2
FUNDDETAILS::FNO4-FNAME4
FUNDDETAILS::FNO5-FNAME5
FUNDDETAILS::FNO6-FNAME6
FUNDDETAILS::FNO3-FNAME3

PL/SQL procedure successfully completed.

Mycode1

CREATE OR REPLACE PROCEDURE ERWP_LOAD_TIME IS

start_dt DATE;
current_dt DATE;
end_dt DATE;

n_days number;
n_hours number;

YEAR number;
QUARTER number;
MONTH number;
MONTH_NAME varchar(50);
WEEK number;
DAY_OF_WEEK number;
DAY_OF_MONTH number;
DAY_NAME varchar(50);
DATE_ID NUMBER;
hh varchar2(6) := '00';
mm varchar2(6) := '00';
this_date_string_v varchar2(64) := null;
this_date_v date := null;
time_format_mask varchar2(64) := 'YYYYMMDD HH24MISS';
hour_name_mask varchar2(64) := 'HH24';
BEGIN

-- set start at today - (2*366), end = today + 10*366
start_dt := to_date('2006-01-01', 'YYYY-MM-DD');
end_dt := to_date('2025-12-31', 'YYYY-MM-DD');

current_dt := TRUNC(start_dt);


n_days := end_dt - start_dt;

for m in 0 .. n_days
loop

YEAR := TO_CHAR(current_dt, 'yyyy');

QUARTER := TO_CHAR(current_dt, 'Q');

MONTH := TO_CHAR(current_dt, 'MM');

MONTH_NAME := TO_CHAR(current_dt, 'MON');

WEEK := TO_CHAR(current_dt, 'WW'); -- week of year

DAY_OF_WEEK := TO_CHAR(current_dt, 'D');

DAY_OF_MONTH := TO_CHAR(current_dt, 'DD');

DAY_NAME := TO_CHAR(current_dt, 'DAY');

DATE_ID := YEAR*10000+MONTH*100+DAY_OF_MONTH;

EXECUTE IMMEDIATE 'INSERT INTO ERW_DATE (ID, YEAR, QUARTER, MONTH, MONTH_NAME, WEEK, DAY_OF_WEEK, DAY_OF_MONTH, DAY_NAME, ORACLE_DATE) ' ||
' values (:DATE_ID, :YEAR, :QUARTER, :MONTH, :MONTH_NAME, :WEEK, :DAY_OF_WEEK, :DAY_OF_MONTH, :DAY_NAME, :ORACLE_DATE) '
USING DATE_ID, YEAR, QUARTER, MONTH, MONTH_NAME, WEEK, DAY_OF_WEEK, DAY_OF_MONTH, DAY_NAME, current_dt;

current_dt := TRUNC (current_dt + INTERVAL '1' DAY);
end loop;

for i in 0..23 loop
mm := '00';
for j in 0..59 loop
insert into ERW_TIME (id, TIME_OF_DAY, TIME_TYPE, MINUTE, HOUR, TIME_NAME)
values(to_number(hh||mm),to_number(hh||mm),'24HHMM',mm,hh,hh||':'||mm);

mm := to_char((to_number(mm)+1));
if (to_number(mm) < 10) then mm := '0'||mm; end if;
end loop; --end minute loop
commit;
hh := to_char(hh+1);
if (hh < 10) then hh := '0'||hh; end if;
dbms_output.put_line('hh = ' || hh);
end loop; --end hour loop
commit;

END;

/

Mycode

declare

t_ticketid varchar2(200):='SRQ15654';

type rec is record

(tkstatusid number,

tkstatusid_next number,

ticketid varchar2(200),

status varchar2(20),

status_next varchar2(20),

changedate varchar2(200),

changedate_next varchar2(200)

);

type tab1 is table of

rec index by binary_integer;

Tab tab1;

i number;

v_net_date varchar2(200);

v_time varchar2(200);

begin

i:=0;

for rec in (select a.* from TKSTATUS a where ticketid=t_ticketid

order by tkstatusid

)

loop

for rec1 in (select * from tkstatus a where ticketid=rec.ticketid

and tkstatusid>rec.tkstatusid

order by tkstatusid

)

loop

i:=i+1;

tab(i).tkstatusid:=rec.tkstatusid;

tab(i).tkstatusid_next:=rec1.tkstatusid;

tab(i).ticketid:=rec.ticketid;

tab(i).status:=rec.status;

tab(i).status_next:=rec1.status;

tab(i).changedate:=rec.changedate;

tab(i).changedate_next:=rec1.changedate;

exit;

end loop;

end loop;

v_net_date:=0;

for i in tab.first..tab.last

loop

if tab(i).status = 'INPROG' then

select (to_date(tab(i).changedate_next,'DD-MON-RRRR HH:MI:SS PM')-to_date(tab(i).changedate,'DD-MON-RRRR HH:MI:SS PM')+1)-

(SELECT COUNT(DAYS) FROM (SELECT TO_CHAR(TO_DATE(tab(i).changedate,'DD-MON-RRRR HH:MI:SS PM')+LEVEL ,'D') DAYS

FROM DUAL CONNECT BY LEVEL < round(to_date(tab(i).changedate_next,'DD-MON-RRRR HH:MI:SS PM')-to_date(tab(i).changedate,'DD-MON-RRRR HH:MI:SS PM')))

WHERE DAYS IN ('7' ,'1')) DAY

INTO V_TIME

FROM DUAL;

V_NET_DATE:=V_NET_DATE+V_TIME;

end if;

--dbms_output.put_line(tab(i).tkstatusid||'-'||tab(i).tkstatusid_next||'---'||tab(i).changedate||'----'||tab(i).changedate_next||tab(i).status||'----'||tab(i).status_next);

end loop;

dbms_output.put_line(v_net_date);

exception

when others then

dbms_output.put_line(Sqlerrm);

end;

select (to_date('24-MAR-09 12.00.00 AM','DD-MON-RRRR HH:MI:SS AM')-to_date('11-MAR-09 12.00.00 AM','DD-MON-RRRR HH:MI:SS AM')+1)-

(SELECT COUNT(DAYS) FROM (SELECT TO_CHAR(TO_DATE('11-MAR-09 12.00.00 AM','DD-MON-RRRR HH:MI:SS AM')+LEVEL ,'D') DAYS

FROM DUAL CONNECT BY LEVEL <= to_date('24-MAR-09 12.00.00 AM','DD-MON-RRRR HH:MI:SS AM')-to_date('11-MAR-09 12.00.00 AM','DD-MON-RRRR HH:MI:SS AM'))

WHERE DAYS IN ('7' ,'1')) DAY

FROM DUAL;

select to_char(to_date('11-MAR-09 12.00.00 AM','DD-MON-RRRR HH:MI:SS PM')+LEVEL,'D') t,to_char(to_date('11-MAR-09 12.00.00 AM','DD-MON-RRRR HH:MI:SS AM')+LEVEL,'DAY') d,

to_char(to_date('11-MAR-09 12.00.00 AM','DD-MON-RRRR HH:MI:SS AM')+LEVEL,'DD') date1

FROM DUAL CONNECT BY LEVEL <= ROUND(to_date('24-MAR-09 12.00.00 AM','DD-MON-RRRR HH:MI:SS AM')-to_date('11-MAR-09 12.00.00 AM','DD-MON-RRRR HH:MI:SS AM');

select round(to_date('24-MAR-09 11.56.26 AM','DD-MON-RRRR HH:MI:SS PM')-to_date('11-MAR-09 08.35.15 AM','DD-MON-RRRR HH:MI:SS PM')) from dual