[Home] [Help]
PACKAGE BODY: APPS.AD_PA_MISC
Source
1 package body ad_pa_misc as
2 /* $Header: adpamisb.pls 120.2 2010/03/31 06:34:27 mkumandu noship $ */
3
4 function get_total_time_stringformat(prid number,
5 tsid number,
6 prd varchar2,
7 pname varchar2)
8 return varchar2 is
9 BEGIN
10
11 return ad_core.get_formatted_elapsed_time(get_total_time(prid,
12 tsid,
13 prd,
14 pname), 2);
15 END;
16
17 function get_total_time(prid number,
18 tsid number,
19 prd varchar2,
20 pname varchar2)
21 return number is
22 TYPE job_name IS TABLE OF ad_program_run_task_jobs.job_name%TYPE;
23 TYPE start_time IS TABLE OF ad_program_run_task_jobs.start_time%TYPE;
24 TYPE end_time IS TABLE OF ad_program_run_task_jobs.end_time%TYPE;
25
26 l_job_name job_name;
27 l_start_time start_time;
28 l_end_time end_time;
29
30 l_total_time number;
31 l_previous_start_time date;
32 l_previous_end_time date;
33
34 l_ctr NUMBER;
35 l_prd VARCHAR2(512) := UPPER(prd);
36
37
38 BEGIN
39 SELECT job_name,
40 start_time,
41 end_time
42 BULK COLLECT INTO l_job_name,
43 l_start_time,
44 l_end_time
45 FROM ad_program_run_task_jobs
46 WHERE program_run_id=prid
47 AND task_status_id=tsid
48 AND phase_name=pname
49 AND (product=prd OR
50 (product='java' AND ((arguments like '%fullpath:'||prd||':%') OR
51 (arguments like '%fullpath_'||prd||'_%') OR
52 (arguments like '%'||UPPER(prd)||'_TOP%'))))
53 ORDER BY start_time;
54 l_total_time := 0;
55 IF l_job_name.COUNT>0 THEN
56 l_previous_start_time := l_start_time(1);
57 l_previous_end_time := l_end_time(1);
58 FOR l_ctr in 1..l_job_name.COUNT
59 LOOP
60 IF (l_start_time(l_ctr) < l_previous_end_time OR
61 l_start_time(l_ctr) = l_previous_end_time) AND
62 (l_previous_end_time < l_end_time(l_ctr) OR
63 l_previous_end_time = l_end_time(l_ctr)) THEN
64 l_previous_end_time := l_end_time(l_ctr);
65 ELSIF (l_start_time(l_ctr) < l_previous_end_time OR
66 l_start_time(l_ctr) = l_previous_end_time) AND
67 l_previous_end_time > l_end_time(l_ctr) THEN
68 null;
69 ELSE
70 l_total_time := l_total_time + (l_previous_end_time - l_previous_start_time);
71 l_previous_start_time := l_start_time(l_ctr);
72 l_previous_end_time := l_end_time(l_ctr);
73 END IF;
74 END LOOP;
75 l_total_time := l_total_time + (l_previous_end_time - l_previous_start_time);
76 END IF;
77
78 return l_total_time;
79 end get_total_time;
80
81 function get_total_time(ssid number,
82 prd varchar2,
83 pname varchar2)
84 return varchar2 is
85 TYPE job_name IS TABLE OF ad_task_timing.job_name%TYPE;
86 TYPE start_time IS TABLE OF ad_task_timing.start_time%TYPE;
87 TYPE end_time IS TABLE OF ad_task_timing.end_time%TYPE;
88
89 l_job_name job_name;
90 l_start_time start_time;
91 l_end_time end_time;
92
93 l_total_time number;
94 l_previous_start_time date;
95 l_previous_end_time date;
96
97 l_ctr NUMBER;
98
99
100 BEGIN
101 SELECT job_name,
102 start_time,
103 end_time
104 BULK COLLECT INTO l_job_name,
105 l_start_time,
106 l_end_time
107 FROM ad_task_timing
108 WHERE session_id=ssid
109 AND phase_name=pname
110 AND (product=prd OR
111 (product='java' AND ((arguments like '%fullpath:'||prd||':%') OR
112 (arguments like '%fullpath_'||prd||'_%') OR
113 (arguments like '%'||UPPER(prd)||'_TOP%'))))
114 ORDER BY start_time;
115
116 /*
117 SELECT job_name,
118 start_time,
119 end_time
120 BULK COLLECT INTO l_job_name,
121 l_start_time,
122 l_end_time
123 FROM ad_task_timing
124 WHERE session_id=ssid
125 AND phase_name=pname
126 AND prd=NVL(DECODE(product,
127 'java',
128 regexp_substr(
129 regexp_substr(
130 regexp_substr(arguments,
131 'fullpath[:|_](\w+)[:|_]'),
132 ':\w+:'),
133 '\w+'),
134 product), NVL(lower(
135 regexp_substr(
136 regexp_substr(arguments,
137 '[A-Z]+_TOP'),
138 '[A-Z]+')), product))
139 ORDER BY start_time;
140
141 AND prd=DECODE(product, 'java',
142 regexp_substr(
143 regexp_substr(
144 regexp_substr(arguments,
145 'fullpath[:|_](\w+)[:|_]'),
146 ':\w+:'),
147 '\w+'), product) product
148 */
149 l_total_time := 0;
150 IF l_job_name.COUNT>0 THEN
151 l_previous_start_time := l_start_time(1);
152 l_previous_end_time := l_end_time(1);
153 FOR l_ctr in 1..l_job_name.COUNT
154 LOOP
155 IF (l_start_time(l_ctr) < l_previous_end_time OR
156 l_start_time(l_ctr) = l_previous_end_time) AND
157 (l_previous_end_time < l_end_time(l_ctr) OR
158 l_previous_end_time = l_end_time(l_ctr)) THEN
159 l_previous_end_time := l_end_time(l_ctr);
160 ELSIF (l_start_time(l_ctr) < l_previous_end_time OR
161 l_start_time(l_ctr) = l_previous_end_time) AND
162 l_previous_end_time > l_end_time(l_ctr) THEN
163 null;
164 ELSE
165 l_total_time := l_total_time + (l_previous_end_time - l_previous_start_time);
166 l_previous_start_time := l_start_time(l_ctr);
167 l_previous_end_time := l_end_time(l_ctr);
168 END IF;
169 END LOOP;
170 l_total_time := l_total_time + (l_previous_end_time - l_previous_start_time);
171 END IF;
172 return ad_core.get_formatted_elapsed_time(l_total_time, 2);
173 end get_total_time;
174
175 END ad_pa_misc;