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;
Sunday, March 29, 2009
Saturday, March 28, 2009
Thursday, March 26, 2009
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;
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.
(
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;
/
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
Subscribe to:
Posts (Atom)