1 package body dt_fndate as
2 /* $Header: dtfndate.pkb 120.1.12010000.3 2009/08/27 06:51:30 avarri ship $ */
3 /*
4 Copyright (c) Oracle Corporation 1991,1992,1993. All rights reserved
5
6 /*
7
8 Name : dt_fndate
9 Author : P.K.Attwood
10 Date Created : 01-OCT-1993
11 Synopsis : This package containes procedures for maintaining the
12 session date held in fnd_sessions.
13 Contents : change_ses_date
14 get_dates
15
16
17 Change List
18 -----------
19 Date Name Vers Bug No Description
20 -----------+-------------+-------+----------+-------------------------------+
21 01-OCT-1993 P.K.Attwood 4.0 First Created.
22 03-MAR-1994 P.K.Attwood 4.1 Changed select statement in
23 get_dates to query
24 trunc(sysdate). Added
25 delete_ses_rows procedure.
26 13-MAY-1994 P.K.Attwood 3.0 Transferred out nocopy of version 4 DT
27 into version 3, so all server
28 side code is in the same place.
29 05-OCT-1994 R.M.Fine 30.1 Renamed package to dt_fndate to
30 conform to naming convention that
31 all objects begin '<prod>_'.
32 30-MAR-1999 P.K.Attwood 110.1 861272 In get_dates corrected setting
33 115.1 g_sys_date when globals
34 variables have not been set and
35 a row already exists in
36 fnd_sessions.
37 22-APR-1999 P.K.Attwood 115.2 854170 Rewrote the delete_ses_rows
38 procedure to remove the join
39 between FND_SESSIONS and a v$
40 view.
41 13-AUG-1999 P.K.Attwood 115.3 In change_ses_date when no row
42 is found in FND_SESSIONS added
43 raise exception.
44 22-JUN-2001 P.K.Attwood 115.4 1841141 To support OPS (Oracle Parallel
45 Server) inside the
46 delete_ses_rows procedure
47 changed references from
48 v$session to gv$session.
49 13-SEP-2001 P.K.Attwood 115.5 Added set_effective_date
50 procedure.
51 19-DEC-2001 G.Perry 115.6 Added dbdrv
52 31-JAN-2002 G.Sayers 115.7 Added validation to get_dates and
53 change_ses_date to ensure
54 g_ses_yesterday_date is set to
55 null when g_ses_date=01/01/0001
56 01-FEB-2002 G.Sayers 115.8 Removed hr_utility commands.
57 06-Dec-2002 A.Holt 115.9 NOCOPY Performance Changes for 11.5.9
58 09-Feb-2005 K.Tangeeda 115.10 Versions 115.10 and 115.11 contain
59 the same code as version 115.9
60 09-Feb-2005 K.Tangeeda 115.11 This version contains the same
61 code as the version 115.9
62 07-May-2006 V.Kaduban 120.1 Procedure delete_old_ses_rows has
63 been added which is exact copy of
64 delete_ses_rows. Existing
65 delete_ses_rows has been modified
66 so that all it does is to delete the
67 row from fnd_sessions for the current
68 session. Also the procedure
69 clean_fnd_sessions has been added
70 which does the same thing as
71 delete_old_ses_rows but used by
72 a concurrent program to do that
73 cleanup periodically.All these
74 changes are as a part of long term
75 solution to the bug 4163689.
76 14-Aug-2008 avarri 120.2 7260450 Modified delete_old_ses_rows to
77 resolve the performance issue.
78 27-Aug-2009 avarri 120.1.12000000.3 Modified delete_old_ses_rows to
79 replace fndSessionId.FIRST with 1 and
80 fndSessionId.LAST with
81 fndSessionId.COUNT to resolve 8839784
82 -----------+-------------+-------+----------+-------------------------------+
83 */
84 --
85 -- Declare globals to this package
86 --
87 g_ses_date date;
88 g_ses_yesterday_date date;
89 g_start_of_time date;
90 g_end_of_time date;
91 g_sys_date date;
92 --
93 procedure get_dates (p_ses_date out nocopy date,
94 p_ses_yesterday_date out nocopy date,
95 p_start_of_time out nocopy date,
96 p_end_of_time out nocopy date,
97 p_sys_date out nocopy date,
98 p_commit out nocopy number) is
99 v_commit boolean;
100 begin
101 v_commit := FALSE;
102 if g_ses_date is null then
103 --
104 -- Only select date fields, if globals have not been set
105 --
106 begin
107 select fs.effective_date
108 , fs.effective_date -1
109 , to_date('01/01/0001','DD/MM/YYYY')
110 , to_date('31/12/4712','DD/MM/YYYY')
111 , trunc(sysdate)
112 into g_ses_date
113 , g_ses_yesterday_date
114 , g_start_of_time
115 , g_end_of_time
116 , g_sys_date
117 from fnd_sessions fs
118 where fs.session_id = userenv('sessionid');
119 exception
120 when no_data_found then
121 --
122 -- Set date fields
123 --
124 g_ses_date := trunc(sysdate);
125 if g_ses_date = to_date('01/01/0001', 'DD/MM/YYYY') then
126 g_ses_yesterday_date := null;
127 else
128 g_ses_yesterday_date := g_ses_date - 1;
129 end if;
130 g_start_of_time := to_date('01/01/0001', 'DD/MM/YYYY');
131 g_end_of_time := to_date('31/12/4712', 'DD/MM/YYYY');
132 g_sys_date := g_ses_date;
133 --
134 -- Insert row in fnd_sessions as one does not
135 -- already exist.
136 --
137 insert into fnd_sessions (session_id, effective_date)
138 values (userenv('sessionid'), g_ses_date);
139 --
140 v_commit := TRUE;
141 end;
142 end if;
143 --
144 if g_ses_date = to_date('01/01/0001', 'DD/MM/YYYY') then
145 g_ses_yesterday_date := null;
146 else
147 g_ses_yesterday_date := g_ses_date - 1;
148 end if;
149 --
150 p_ses_date := g_ses_date;
151 p_ses_yesterday_date := g_ses_yesterday_date;
152 p_start_of_time := g_start_of_time;
153 p_end_of_time := g_end_of_time;
154 p_sys_date := g_sys_date;
155 if (v_commit) then
156 p_commit := 1;
157 else
158 p_commit := 0;
159 end if;
160 end get_dates;
161 --
162 --
163 --
164 procedure change_ses_date (p_ses_date in date,
165 p_commit out nocopy number) is
166 v_commit boolean;
167 no_row_need_to_insert exception;
168 begin
169 begin
170 --
171 -- Update row in fnd_sessions
172 --
173 v_commit := FALSE;
174 --
175 update fnd_sessions
176 set effective_date = trunc(p_ses_date)
177 where session_id = userenv('sessionid');
178 --
179 -- When no row is found in FND_SESSIONS
180 -- raise an exception to insert a row.
181 --
182 if sql%rowcount = 0 then
183 raise no_row_need_to_insert;
184 end if;
185 --
186 v_commit := TRUE;
187 exception
188 when no_row_need_to_insert then
189 g_ses_date := trunc(p_ses_date);
190 if g_ses_date = to_date('01/01/0001', 'DD/MM/YYYY') then
191 g_ses_yesterday_date := null;
192 else
193 g_ses_yesterday_date := g_ses_date - 1;
194 end if;
195 g_start_of_time := to_date('01/01/0001', 'DD/MM/YYYY');
196 g_end_of_time := to_date('31/12/4712', 'DD/MM/YYYY');
197 g_sys_date := trunc(sysdate);
198 --
199 -- Insert row in fnd_sessions as one does not
200 -- already exist.
201 --
202 insert into fnd_sessions (session_id, effective_date)
203 values (userenv('sessionid'), g_ses_date);
204 --
205 v_commit := TRUE;
206 end;
207 --
208 -- Update package globals
209 --
210 g_ses_date := trunc(p_ses_date);
211 if g_ses_date= to_date('01/01/0001', 'DD/MM/YYYY') then
212 g_ses_yesterday_date := null;
213 else
214 g_ses_yesterday_date := g_ses_date - 1;
215 end if;
216 --
217 if (v_commit) then
218 p_commit := 1;
219 else
220 p_commit := 0;
221 end if;
222 end change_ses_date;
223 --
224 --
225 --
226 procedure set_effective_date
227 (p_effective_date in date default null
228 ,p_do_commit in boolean default false
229 ) is
230 v_commit number;
231 begin
232 change_ses_date(p_ses_date => nvl(p_effective_date, sysdate)
233 ,p_commit => v_commit);
234 if p_do_commit and v_commit = 1 then
235 commit;
236 end if;
237 end set_effective_date;
238 --
239 --
240 --
241 procedure delete_ses_rows(p_commit out nocopy number) is
242 --
243 -- Declare exceptions to be handled
244 --
245 begin
246 delete from fnd_sessions f
247 where session_id = userenv('sessionid');
248 p_commit := 1;
249 if(SQL%ROWCOUNT = 0) then
250 p_commit := 0 ;
251 end if ;
252 exception
253 when Others then
254 p_commit := 0;
255 end delete_ses_rows;
256 --
257 --
258 --
259 procedure init_dates is
260 begin
261 --
262 -- Initializes globals to NULL to ensure first call to get_dates
263 -- will insert a row into fnd_sessions. (It is not possible to
264 -- commit here, so don't insert the row into fnd_sessions.)
265 --
266 g_ses_date := NULL;
267 g_ses_yesterday_date := NULL;
268 g_start_of_time := NULL;
269 g_end_of_time := NULL;
270 g_sys_date := NULL;
271 end init_dates;
272 --
273 --
274 --
275 procedure delete_old_ses_rows(p_commit out nocopy number) is
276 --
277 -- Declare cursors
278 --
279 cursor csr_fnd_ses is
280 select session_id
281 from fnd_sessions;
282 --
283 -- To fix Bug 1841141 the v$ view in the
284 -- following cursor was changed to gv$. Inspite of
285 -- comments in the RDBMS manual it should be safe
286 -- to always reference the gv$ view even when a
287 -- non-parallel server is being used. This
288 -- is because the v$ views are based on the gv$
289 -- views with filter INST_ID = userenv('Instance').
290 --
291 cursor csr_gv_ses is
292 select audsid
293 from gv$session;
294 --
295 -- Declare exceptions to be handled
296 --
297 Resource_Busy exception;
298 Pragma Exception_Init(Resource_Busy, -54);
299 --
300 -- Local variables
301 --
302 v_exists varchar2(30);
303 TYPE sessionId IS TABLE OF fnd_sessions.session_id%TYPE INDEX BY BINARY_INTEGER;
304 fndSessionId sessionId;
305 gvSessionId sessionId;
306 delSessionId sessionId;
307 l_session_exists number := 0;
308 k number := 1;
309 bulkFetchRowLimit number := 10000;
310 begin
311 --
312 -- Bug 854170: Changed original delete statement as joins
313 -- between delete a v$ view and a standard table are not
314 -- supported by the RDBMS.
315 -- Original like code:
316 -- delete from fnd_sessions f
317 -- where not exists (select null
318 -- from v$session s
319 -- where s.audsid = f.session_id);
320 -- p_commit := 1;
321 --
322 -- Attempt to obtain an exclusive lock on the DateTrack date
323 -- prompts table. This table lock acts as a gatekeeper to
324 -- the FND_SESSIONS delete logic.
325 --
326 -- When this process obtains the table lock then it should go
327 -- on to remove old session rows from FND_SESSIONS. i.e. Where
328 -- there is no corresponding row in GV$SESSION. When this
329 -- process does not obtain the table lock it indicates that
330 -- another process must be performing the FND_SESSIONS delete
331 -- logic. So this session does not need to do anything extra.
332 --
333 begin
334 lock table dt_date_prompts_tl in exclusive mode nowait;
335 --
336 -- If this point is reached then the table lock
337 -- has been obtained by this process.
338 --
339 -- Get all the rows from gv$session using bulk collect.
340 --
341 open csr_gv_ses;
342 fetch csr_gv_ses BULK COLLECT INTO gvSessionId;
343 close csr_gv_ses;
344 --
345 -- For each row in FND_SESSIONS see if a corresponding
346 -- row exists in GV$SESSION. When there is no matching
347 -- row delete the FND_SESSIONS row.
348 --
349 open csr_fnd_ses;
350 loop
351 fetch csr_fnd_ses BULK COLLECT INTO fndSessionId limit bulkFetchRowLimit;
352 for i in 1..fndSessionId.COUNT loop
353 for j in 1.. gvSessionId.COUNT loop
354 if (fndSessionId(i) = gvSessionId(j)) then
355 l_session_exists := 1;
356 exit;
357 end if;
358 end loop;
359 --
360 if l_session_exists = 0 then
361 delSessionId(k) := fndSessionId(i);
362 k := k + 1;
363 end if;
364 l_session_exists := 0;
365 end loop;
366 exit when csr_fnd_ses%notfound;
367 end loop;
368 close csr_fnd_ses;
369 --
370 forall l in 1..delSessionId.count
371 delete from fnd_sessions where session_id = delSessionId(l);
372 --
373 p_commit := 1;
374 exception
375 when Resource_Busy then
376 --
377 -- If this point is reached then the table lock
378 -- has not been obtained by this process. This
379 -- means another process must be currently
380 -- performing the FND_SESSIONS delete logic.
381 -- So this process does not need to do anything
382 -- with the FND_SESSIONS table.
383 --
384 p_commit := 0;
385 end;
386 end delete_old_ses_rows;
387 --
388 --
389 --
390 procedure clean_fnd_sessions ( errbuf out nocopy varchar2,
391 retcode out nocopy varchar2 ) is
392 l_commit_flag number;
393 begin
394 delete_old_ses_rows(l_commit_flag);
395 if l_commit_flag = 1 then
396 commit;
397 end if;
398 end clean_fnd_sessions;
399 --
400 end dt_fndate;
[Home] [Help]