1 package body dt_fndate as
2 /* $Header: dtfndate.pkb 120.1 2006/05/07 00:11:00 vkaduban noship $ */
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 -----------+-------------+-------+----------+-------------------------------+
77 */
78 --
79 -- Declare globals to this package
80 --
81 g_ses_date date;
82 g_ses_yesterday_date date;
83 g_start_of_time date;
84 g_end_of_time date;
85 g_sys_date date;
86 --
87 procedure get_dates (p_ses_date out nocopy date,
88 p_ses_yesterday_date out nocopy date,
89 p_start_of_time out nocopy date,
90 p_end_of_time out nocopy date,
91 p_sys_date out nocopy date,
92 p_commit out nocopy number) is
93 v_commit boolean;
94 begin
95 v_commit := FALSE;
96 if g_ses_date is null then
97 --
98 -- Only select date fields, if globals have not been set
99 --
100 begin
101 select fs.effective_date
102 , fs.effective_date -1
103 , to_date('01/01/0001','DD/MM/YYYY')
104 , to_date('31/12/4712','DD/MM/YYYY')
105 , trunc(sysdate)
106 into g_ses_date
107 , g_ses_yesterday_date
108 , g_start_of_time
109 , g_end_of_time
110 , g_sys_date
111 from fnd_sessions fs
112 where fs.session_id = userenv('sessionid');
113 exception
114 when no_data_found then
115 --
116 -- Set date fields
117 --
118 g_ses_date := trunc(sysdate);
119 if g_ses_date = to_date('01/01/0001', 'DD/MM/YYYY') then
120 g_ses_yesterday_date := null;
121 else
122 g_ses_yesterday_date := g_ses_date - 1;
123 end if;
124 g_start_of_time := to_date('01/01/0001', 'DD/MM/YYYY');
125 g_end_of_time := to_date('31/12/4712', 'DD/MM/YYYY');
126 g_sys_date := g_ses_date;
127 --
128 -- Insert row in fnd_sessions as one does not
129 -- already exist.
130 --
131 insert into fnd_sessions (session_id, effective_date)
132 values (userenv('sessionid'), g_ses_date);
133 --
134 v_commit := TRUE;
135 end;
136 end if;
137 --
138 if g_ses_date = to_date('01/01/0001', 'DD/MM/YYYY') then
139 g_ses_yesterday_date := null;
140 else
141 g_ses_yesterday_date := g_ses_date - 1;
142 end if;
143 --
144 p_ses_date := g_ses_date;
145 p_ses_yesterday_date := g_ses_yesterday_date;
146 p_start_of_time := g_start_of_time;
147 p_end_of_time := g_end_of_time;
148 p_sys_date := g_sys_date;
149 if (v_commit) then
150 p_commit := 1;
151 else
152 p_commit := 0;
153 end if;
154 end get_dates;
155 --
156 --
157 --
158 procedure change_ses_date (p_ses_date in date,
159 p_commit out nocopy number) is
160 v_commit boolean;
161 no_row_need_to_insert exception;
162 begin
163 begin
164 --
165 -- Update row in fnd_sessions
166 --
167 v_commit := FALSE;
168 --
169 update fnd_sessions
170 set effective_date = trunc(p_ses_date)
171 where session_id = userenv('sessionid');
172 --
173 -- When no row is found in FND_SESSIONS
174 -- raise an exception to insert a row.
175 --
176 if sql%rowcount = 0 then
177 raise no_row_need_to_insert;
178 end if;
179 --
180 v_commit := TRUE;
181 exception
182 when no_row_need_to_insert then
183 g_ses_date := trunc(p_ses_date);
184 if g_ses_date = to_date('01/01/0001', 'DD/MM/YYYY') then
185 g_ses_yesterday_date := null;
186 else
187 g_ses_yesterday_date := g_ses_date - 1;
188 end if;
189 g_start_of_time := to_date('01/01/0001', 'DD/MM/YYYY');
190 g_end_of_time := to_date('31/12/4712', 'DD/MM/YYYY');
191 g_sys_date := trunc(sysdate);
192 --
193 -- Insert row in fnd_sessions as one does not
194 -- already exist.
195 --
196 insert into fnd_sessions (session_id, effective_date)
197 values (userenv('sessionid'), g_ses_date);
198 --
199 v_commit := TRUE;
200 end;
201 --
202 -- Update package globals
203 --
204 g_ses_date := trunc(p_ses_date);
205 if g_ses_date= to_date('01/01/0001', 'DD/MM/YYYY') then
206 g_ses_yesterday_date := null;
207 else
208 g_ses_yesterday_date := g_ses_date - 1;
209 end if;
210 --
211 if (v_commit) then
212 p_commit := 1;
213 else
214 p_commit := 0;
215 end if;
216 end change_ses_date;
217 --
218 --
219 --
220 procedure set_effective_date
221 (p_effective_date in date default null
222 ,p_do_commit in boolean default false
223 ) is
224 v_commit number;
225 begin
226 change_ses_date(p_ses_date => nvl(p_effective_date, sysdate)
227 ,p_commit => v_commit);
228 if p_do_commit and v_commit = 1 then
229 commit;
230 end if;
231 end set_effective_date;
232 --
233 --
234 --
235 procedure delete_ses_rows(p_commit out nocopy number) is
236 --
237 -- Declare exceptions to be handled
238 --
239 begin
240 delete from fnd_sessions f
241 where session_id = userenv('sessionid');
242 p_commit := 1;
243 if(SQL%ROWCOUNT = 0) then
244 p_commit := 0 ;
245 end if ;
246 exception
247 when Others then
248 p_commit := 0;
249 end delete_ses_rows;
250 --
251 --
252 --
253 procedure init_dates is
254 begin
255 --
256 -- Initializes globals to NULL to ensure first call to get_dates
257 -- will insert a row into fnd_sessions. (It is not possible to
258 -- commit here, so don't insert the row into fnd_sessions.)
259 --
260 g_ses_date := NULL;
261 g_ses_yesterday_date := NULL;
262 g_start_of_time := NULL;
263 g_end_of_time := NULL;
264 g_sys_date := NULL;
265 end init_dates;
266 --
267 --
268 --
269 procedure delete_old_ses_rows(p_commit out nocopy number) is
270 --
271 -- Declare cursors
272 --
273 cursor csr_fnd_ses is
274 select session_id
275 from fnd_sessions;
276 --
277 -- To fix Bug 1841141 the v$ view in the
278 -- following cursor was changed to gv$. Inspite of
279 -- comments in the RDBMS manual it should be safe
280 -- to always reference the gv$ view even when a
281 -- non-parallel server is being used. This
282 -- is because the v$ views are based on the gv$
283 -- views with filter INST_ID = userenv('Instance').
284 --
285 cursor csr_v_ses (p_session_id number) is
286 select null
287 from gv$session
288 where audsid = p_session_id;
289 --
290 -- Declare exceptions to be handled
291 --
292 Resource_Busy exception;
293 Pragma Exception_Init(Resource_Busy, -54);
294 --
295 -- Local variables
296 --
297 v_exists varchar2(30);
298 begin
299 --
300 -- Bug 854170: Changed original delete statement as joins
301 -- between delete a v$ view and a standard table are not
302 -- supported by the RDBMS.
303 -- Original like code:
304 -- delete from fnd_sessions f
305 -- where not exists (select null
306 -- from v$session s
307 -- where s.audsid = f.session_id);
308 -- p_commit := 1;
309 --
310 -- Attempt to obtain an exclusive lock on the DateTrack date
311 -- prompts table. This table lock acts as a gatekeeper to
312 -- the FND_SESSIONS delete logic.
313 --
314 -- When this process obtains the table lock then it should go
315 -- on to remove old session rows from FND_SESSIONS. i.e. Where
316 -- there is no corresponding row in GV$SESSION. When this
317 -- process does not obtain the table lock it indicates that
318 -- another process must be performing the FND_SESSIONS delete
319 -- logic. So this session does not need to do anything extra.
320 --
321 begin
322 lock table dt_date_prompts_tl in exclusive mode nowait;
323 --
324 -- If this point is reached then the table lock
325 -- has been obtained by this process.
326 --
327 -- For each row in FND_SESSIONS see if a corresponding
328 -- row exists in GV$SESSION. When there is no matching
329 -- row delete the FND_SESSIONS row.
330 --
331 for l_fnd_ses in csr_fnd_ses loop
332 --
333 open csr_v_ses(l_fnd_ses.session_id);
334 fetch csr_v_ses into v_exists;
335 if csr_v_ses%notfound then
336 delete from fnd_sessions f
337 where session_id = l_fnd_ses.session_id;
338 end if;
339 close csr_v_ses;
340 --
341 end loop;
342 --
343 p_commit := 1;
344 exception
345 when Resource_Busy then
346 --
347 -- If this point is reached then the table lock
348 -- has not been obtained by this process. This
349 -- means another process must be currently
350 -- performing the FND_SESSIONS delete logic.
351 -- So this process does not need to do anything
352 -- with the FND_SESSIONS table.
353 --
354 p_commit := 0;
355 end;
356 end delete_old_ses_rows;
357 --
358 --
359 --
360 procedure clean_fnd_sessions ( errbuf out nocopy varchar2,
361 retcode out nocopy varchar2 ) is
362 l_commit_flag number;
363 begin
364 delete_old_ses_rows(l_commit_flag);
365 if l_commit_flag = 1 then
366 commit;
367 end if;
368 end clean_fnd_sessions;
369 --
370 end dt_fndate;
[Home] [Help]