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