IBM MAXIMO

Birt material

2009-06-08T21:25:00.001-07:00

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

Birt demo links

2009-06-02T21:06:00.000-07:00

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

Birt Reporting useful links

2009-06-02T19:44:00.000-07:00

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

2009-04-19T20:58:00.000-07:00

create table (id number,crdate date default sysdate)

Assigning a default value to an attribute in Applcation Designer

2009-04-12T18:19:00.000-07:00

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

2009-04-09T22:14:00.000-07:00

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

2009-04-09T22:13:00.000-07:00

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.

2009-04-06T19:59:00.000-07:00

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)

2009-04-06T18:46:00.000-07:00

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

2009-04-06T18:30:00.000-07:00

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

2009-04-05T21:26:00.001-07:00

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

2009-03-29T22:19:00.000-07:00

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

2009-03-28T17:14:00.000-07:00

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

My Stored procedure

2009-03-26T22:25:00.002-07:00

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

2009-03-26T22:25:00.001-07:00

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

2009-03-26T22:24:00.001-07:00

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

2009-03-26T22:22:00.000-07:00

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;

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

Tab name in Workflow

2009-03-23T21:45:00.000-07:00

To determine a tab identifier, complete the followings steps:
1 Navigate to the following directory:
\resources\presentations
2 Using a text editor, open the XML file for the application.
3 Search for the following phrase "tab id=" until you locate the appropriate
tab identifier, for example:

Path to Check Java Classes in Maximo

2009-03-23T21:01:00.000-07:00

IBM/SMP/MAXIMO/Applications/maximo/BusinessObjects/classes/psdi/app

Inserting Hyperlinks in Maximo

2009-03-22T22:22:00.000-07:00

You can insert a hyperlink to a Maximo record within the body of your
communication template message that includes the application name and
record ID.
To insert a hyperlink in a Workflow communication template, insert the
following link into the body of your message:
http://:HOSTNAME/maximo/ui/
maximo.jsp?event=loadapp&value=:APP&uniqueid=:OWNERID
where HOSTNAME is the name or IP address of the MXServer.
When Maximo generates a notification, the application name and record ID
appear in the message as a hyperlink that leads directly to the record.
NOTE In order to be able to view the record, the e-mail recipient must be a Maximo
user with security permission to the specified application.

Sample Escalation Log Messages

2009-03-22T20:20:00.000-07:00

Sample Escalation Log Messages
NOTE You must first enable logging.
Escalation Start and Finish
These messages indicate the escalations start and finish, including its name
and time of execution.
21 Jul 2004 13:47:23:839 [DEBUG] Started Escalation task:
ESCALATION for instance: ESC INCID
21 Jul 2004 13:47:23:849 [DEBUG] Finished Escalation task:
ESCALATION for instance: ESC INCID
These statements appear in the log file if you place this statement in the
properties file:
log4j.logger.Maximo.crontask.ESCALATION=DEBUG, A2

Enabling Logging for Escalations

2009-03-22T20:18:00.000-07:00

Time drives escalations, and log files let you check whether escalation actions
run according to schedule.
To enable logging on the escalation cron task, notification messages, and
action messages, you must modify the logging.properties file. Default location:
applications\Maximo\properties
where is the folder where you installed Maximo.
1 Append these examples to your local file:
# Enable crontask for ESCALATION
log4j.logger.Maximo.crontask.ESCALATION=DEBUG, A2
# Enable the following for Notification messages on logger
log4j.logger.Maximo.service.SYSTEM.COMMTEMPLATE=INFO, A2
# Enable the following for Action messages on logger
log4j.logger.Maximo.service.SYSTEM.ACTION=INFO, A2
2 Save the modifications.
Enabling Logging for Escalations
Escalations 12-13
3 Rebuild the EAR file and restart the application server.

DB config Restore Backup

2009-03-22T13:14:00.000-07:00

Question: Error when running ConfigDB: "Tables exist that have not had their backup data restored. You must run restorefrombackup before you configure the database" But there are no backup tables to be restored. ???

Answer: This can happen if a table is manually restored via SQL rather than using restorefrombackup. A flag is left in the database that indicates restorefrombackup needs to run to restore the data from the xxtable_name to table_name.

To correct this you need to remove these flags from the database. To verify if these are incorrect run the following query.

select restoredata from maxtable where tablename='table_name'

select restoredata from maxtablecfg where tablename='table_name'

If either of these return a value of 1 they must be changed to 0 to allow ConfigDB to run.

Escalation : Notification when purchase contract renewal date is 30, 60, 90 days away

2009-03-21T22:15:00.000-07:00

on
Escalation : Notification when purchase contract renewal date is 30, 60, 90 days away

Applies To : Purchview

Condition: STATUS=(select value from synonymdomain where domainid = 'CONTRACTSTATUS' and maxvalue = 'APPR')

Escalation points :

Escalation point : 1
Elapsed Time Attribute: ENDDATE Elapsed Time Interval : -90 days

Escalation point : 2
Elapsed Time Attribute: ENDDATE Elapsed Time Interval : -60 days

Escalation point : 3
Elapsed Time Attribute: ENDDATE Elapsed Time Interval : -30 days

Create a Notification for each Escalation point.

Notificatication Commtemplate is :

Applies To : PURCHVIEW

ACCESSIBLE FROM : ESCALATION

Commlog Entry: check mark Yes.(right mark).

Subject : Expiration notice: Contract :contractnum

Message: Purchase contract :contractnum is expiring on :enddate .

Close incidents that remained in status of RESOLVED for at least 10 days.

2009-03-21T19:30:00.000-07:00

Applies To : INCIDENT

Condition: HISTORYFLAG=0 and STATUS=(select value from synonymdomain where domainid = 'INCIDENTSTATUS' and maxvalue = 'RESOLVED')

Escalation Point : 1
Elapsed Time Attribute : TARGETFINISH.
Elapsed time interval: 10 days.

Create an action
Action: this should change the status to Closed.

Escalation Sending survey to end users.

2009-03-21T18:53:00.000-07:00

Applies To : PMSCMR

Condition: STATUS = (select value from synonymdomain where domainid='MRSTATUS' and maxvalue='CLOSE') AND (TRUNC(SYSDATE - CHANGEDATE) =5)

Escalation point : REPORTDATE .... Elapsed time interval : 24:00

Action:

Description: Create an action to create link betweenPMSCMR and survey

Object : PMSCMR
Type : Custom

Notifications: Under notification tab, we define Template and Role.

Role should be same as To field in Communication Template tab.

Path to Check Oracle tnsnames

2009-03-19T18:22:00.000-07:00

C:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\tnsnames

commlog

2009-03-19T06:05:00.001-07:00

select t.lastcommdate
from
ticket t
where t.lastcommdate< (select max(logg.crdate)
from comlog logg
where t.ticketid=logg.ownerid
group by t.ticketid
);

NEXT_DAY and LAST_DAY functions in Oracle

2009-03-18T20:01:00.001-07:00

NEXT_DAY and LAST_DAY functions

The NEXT_DAY and LAST_DAY functions can be used to calculate for example «the last Saturday in any given month». You can simply get the last day in the month, subtract 7 days from that, and then use NEXT_DAY to find the next Saturday after that one.

NEXT_DAY (date, char)

NEXT_DAY returns the date of the first weekday named by char that is later than date. The return type is always DATE, regardless of the datatype of date. The argument char must be a day of the week in the date language of your session, either the full name or the abbreviation. The minimum number of letters required is the number of letters in the abbreviated version. Any characters immediately following the valid abbreviation are ignored. The return value has the same hours, minutes, and seconds component as the argument date.

Example

Return the date of the next Monday after now:

SELECT TO_CHAR(NEXT_DAY(sysdate,'MON'),'DD.MM.YYYY') "Next Monday from now"
FROM DUAL;

Next Monday
-----------
06.12.2004

LAST_DAY(date)

LAST_DAY returns the date of the last day of the month that contains date. The return type is always DATE, regardless of the datatype of date.

Example

The following statement determines how many days are left in the current month:

SELECT SYSDATE,
LAST_DAY(SYSDATE) "Last",
LAST_DAY(SYSDATE) - SYSDATE "Days Left"
FROM DUAL;

SYSDATE Last Days Left
--------- --------- ----------
03-DEC-04 31-DEC-04 28

Get the last date of a month:

SELECT LAST_DAY (TO_DATE ('02','MM')) FROM dual;

LAST_DAY
---------
29-FEB-04

Return the last Saturday of each month for a given year

You can simply get the last day in the month, subtract 7 days from that, and then use NEXT_DAY to find the next Saturday after that one.

DEFINE my_month = 12;

SELECT TO_CHAR (
NEXT_DAY (
LAST_DAY (TO_DATE (&my_month,'MM' )) - 7,
TO_CHAR (TO_DATE ('29-01-1927', 'DD-MM-YYYY' ),'DAY')
),'DD.MM.YYYY') "Last Saturday in December 2004"
FROM dual;

Last Saturday in December 2004
------------------------------
25.12.2004

Return the last Saturdays for the current year.

SELECT TO_CHAR (
NEXT_DAY (
LAST_DAY (
ADD_MONTHS (TRUNC(SYSDATE,'Y'),ROWNUM-1))-7,
TO_CHAR (TO_DATE('29-01-1927', 'DD-MM-YYYY'),'DAY')
), 'DD.MM.YYYY') "Last Saturdays in 2004"
FROM ALL_OBJECTS
WHERE ROWNUM <= 12;

Last Saturdays in 2004
----------------------
31.01.2004
28.02.2004
27.03.2004
24.04.2004
29.05.2004
26.06.2004
31.07.2004
28.08.2004
25.09.2004
30.10.2004
27.11.2004
25.12.2004

The "29-01-1927" is just a random date that we knew was a Saturday—any Saturday would do. This is done instead of using "SAT" in the query for international reasons, because in languages other than English, "SAT" isn't Saturday. This query should work in any language out there.

The new TIMESTAMP datatype

2009-03-18T19:55:00.000-07:00

Oracle has expanded on the DATE datatype and has given us the TIMESTAMP datatype which stores all the information that the DATE datatype stores, but also includes fractional seconds.

If you want to convert a DATE datatype to a TIMESTAMP datatype format, just use the CAST function. As you can see, there is a fractional seconds part of '.000000' on the end of this conversion. This is only because when converting from the DATE datatype that does not have the fractional seconds it defaults to zeros and the display is defaulted to the default timestamp format.

CREATE TABLE date_table (
date1 DATE,
time1 TIMESTAMP,
time2 TIMESTAMP
);

INSERT INTO date_table (date1, time1, time2)
VALUES (SYSDATE,
TO_TIMESTAMP ('17.12.1980:00:00:00','DD.MM.YYYY:HH24:MI:SS'),
TO_TIMESTAMP ('03.12.2004:10:34:24','DD.MM.YYYY:HH24:MI:SS')
);
COMMIT;

SELECT CAST(date1 AS TIMESTAMP) "Date" FROM date_table;

Date
---------------------------------------------------------------------------
03-DEC-04 11.36.45.000000 AM

Insert a hyperlink in a Workflow communication template

2009-03-17T22:14:00.000-07:00

To insert a hyperlink in a Workflow communication template, insert the following
link into the body of your message:

Creating Records for Workflow 29
http://:HOSTNAME/maximo/ui/
maximo.jsp?event=loadapp&value=:APP&uniqueid=:OWNERID

where HOSTNAME is the name or IP address of the MXServer.
When a notification is generated, the application name and record ID appear in
the message as a hyperlink that leads directly to the record.

Creating Communication Templates

2009-03-17T21:23:00.000-07:00

You use the Communication Templates application to create and manage generic
templates to use when generating e-mail messages.
When you create a communication template, you can specify the following
information:
􀁔 The business object that the template can be used for.
􀁔 The applications where the template can be used.
􀁔 The address that the e-mail should be sent from.
􀁔 The address that replies should be sent to.
􀁔 The subject line of the message.
􀁔 The body of the message.
􀁔 One or more recipients of the message. You can send messages to roles,
persons, person groups, and e-mail addresses.
􀁔 Whether each recipient should receive the message (To), a carbon copy of the
message (CC), or a blind carbon copy of the message (BCC).
􀁔 Documents to include as attachments when the message is generated.
The Communication Templates application lets you use substitution variables
when creating the subject line and body of your message. When an e-mail is
generated using that template, it replaces the substitution

Statement to retrieve time in Oracle

2009-03-16T20:53:00.000-07:00

select to_char(sysdate, 'Dy DD-Mon-YYYY HH24:MI:SS')

count time timestamp
select * from ticket where s= rowstamp countstamp

How to set default value to any field in Database Configuration application

2009-03-16T20:40:00.000-07:00

Go to Database Conguration -> Select the object(Eg Ticket) -> Select the Attribute( Eg lastcommdate) then click on the dafault value property and there enter the value which you want to set it as default.

Escalation Commlog

2009-03-15T20:54:00.000-07:00

ticketid in (select ticketid from ticket where status<> 'CLOSED' and commlogentry is null and lastcommdate in(select lastcommdate from ticket where lastcommdate<(select max(createdate) from commlog where ownerid in (select ticketuid from ticket)))

ticketid in (select ticketid from ticket where status<> 'CLOSED' and lastcommdate in(select lastcommdate from ticket where lastcommdate AND TIMESTAM<(select max(createdate and TIMESTAMP) from commlog where ownerid in (select ticketuid from ticket))) Escalation points point 1 condition : class= 'SR' point 2: condition: clasee = 'INCIDENT' point 3 condition: class= 'PROBLEM'

update ticket set lastcommdate=sysdate where ticketid in( select ticketid from ticket

where lastcommdate<(select(max(crdate)) from comlog

where ownerid in (select ticketuid from ticket)))

Tuesday:

select crdate, ownerid from comlog where crdate in (select max(crdate) from comlog group by own
erid)