1 package body FNDCP_CRM as
2 /* $Header: AFCPCRMB.pls 115.7 2002/03/21 09:18:07 pkm ship $ */
3
4
5
6 --
7 -- Returns the number of mgr procs that can run the request
8 --
9
10 function mgr_up (reqid in number) return number is
11
12 up number;
13
14 begin
15 select nvl(max (running_processes), 0)
16 into up
17 from fnd_concurrent_worker_requests
18 where not (queue_application_id = 0
19 and concurrent_queue_id in (1,4))
20 and request_id = reqid;
21
22 return (up);
23
24 exception
25 when no_data_found then
26 return (0);
27 end mgr_up;
28
29
30 -- The following function is used by FND_REQUEST package in AFCPREQ*.pls
31 -- and src/process/fdprrc.lpc. This used to be in AFCPREQ*.pls, but due
32 -- to the infamous 64K limit, had to move out to here.
33
34 --
35 -- Get conflicts domain id.
36 --
37 -- Extract the value in parameter named by cd_param.
38 -- This value is a Conflicts Domain Name.
39 -- If the domain by this name exists, return its cd_id.
40 -- Else, insert a new domain by the name and return the new cd_id.
41 --
42 -- The routine is used at request submission time by programs that
43 -- have the Conflicts Domain name defined in a parameter.
44 --
45 function get_cd_id (app in varchar2,
46 program in varchar2,
47 user_id in number,
48 login_id in number,
49 cd_param in varchar2,
50 nargs in number,
51 a1 in varchar2 default chr(0),
52 a2 in varchar2 default chr(0),
53 a3 in varchar2 default chr(0),
54 a4 in varchar2 default chr(0),
55 a5 in varchar2 default chr(0),
56 a6 in varchar2 default chr(0),
57 a7 in varchar2 default chr(0),
58 a8 in varchar2 default chr(0),
59 a9 in varchar2 default chr(0),
60 a10 in varchar2 default chr(0),
61 a11 in varchar2 default chr(0),
62 a12 in varchar2 default chr(0),
63 a13 in varchar2 default chr(0),
64 a14 in varchar2 default chr(0),
65 a15 in varchar2 default chr(0),
66 a16 in varchar2 default chr(0),
67 a17 in varchar2 default chr(0),
68 a18 in varchar2 default chr(0),
69 a19 in varchar2 default chr(0),
70 a20 in varchar2 default chr(0),
71 a21 in varchar2 default chr(0),
72 a22 in varchar2 default chr(0),
73 a23 in varchar2 default chr(0),
74 a24 in varchar2 default chr(0),
75 a25 in varchar2 default chr(0),
76 a26 in varchar2 default chr(0),
77 a27 in varchar2 default chr(0),
78 a28 in varchar2 default chr(0),
79 a29 in varchar2 default chr(0),
80 a30 in varchar2 default chr(0),
81 a31 in varchar2 default chr(0),
82 a32 in varchar2 default chr(0),
83 a33 in varchar2 default chr(0),
84 a34 in varchar2 default chr(0),
85 a35 in varchar2 default chr(0),
86 a36 in varchar2 default chr(0),
87 a37 in varchar2 default chr(0),
88 a38 in varchar2 default chr(0),
89 a39 in varchar2 default chr(0),
90 a40 in varchar2 default chr(0),
91 a41 in varchar2 default chr(0),
92 a42 in varchar2 default chr(0),
93 a43 in varchar2 default chr(0),
94 a44 in varchar2 default chr(0),
95 a45 in varchar2 default chr(0),
96 a46 in varchar2 default chr(0),
97 a47 in varchar2 default chr(0),
98 a48 in varchar2 default chr(0),
99 a49 in varchar2 default chr(0),
100 a50 in varchar2 default chr(0),
101 a51 in varchar2 default chr(0),
102 a52 in varchar2 default chr(0),
103 a53 in varchar2 default chr(0),
104 a54 in varchar2 default chr(0),
105 a55 in varchar2 default chr(0),
106 a56 in varchar2 default chr(0),
107 a57 in varchar2 default chr(0),
108 a58 in varchar2 default chr(0),
109 a59 in varchar2 default chr(0),
110 a60 in varchar2 default chr(0),
111 a61 in varchar2 default chr(0),
112 a62 in varchar2 default chr(0),
113 a63 in varchar2 default chr(0),
114 a64 in varchar2 default chr(0),
115 a65 in varchar2 default chr(0),
116 a66 in varchar2 default chr(0),
117 a67 in varchar2 default chr(0),
118 a68 in varchar2 default chr(0),
119 a69 in varchar2 default chr(0),
120 a70 in varchar2 default chr(0),
121 a71 in varchar2 default chr(0),
122 a72 in varchar2 default chr(0),
123 a73 in varchar2 default chr(0),
124 a74 in varchar2 default chr(0),
125 a75 in varchar2 default chr(0),
126 a76 in varchar2 default chr(0),
127 a77 in varchar2 default chr(0),
128 a78 in varchar2 default chr(0),
129 a79 in varchar2 default chr(0),
130 a80 in varchar2 default chr(0),
131 a81 in varchar2 default chr(0),
132 a82 in varchar2 default chr(0),
133 a83 in varchar2 default chr(0),
134 a84 in varchar2 default chr(0),
135 a85 in varchar2 default chr(0),
136 a86 in varchar2 default chr(0),
137 a87 in varchar2 default chr(0),
138 a88 in varchar2 default chr(0),
139 a89 in varchar2 default chr(0),
140 a90 in varchar2 default chr(0),
141 a91 in varchar2 default chr(0),
142 a92 in varchar2 default chr(0),
143 a93 in varchar2 default chr(0),
144 a94 in varchar2 default chr(0),
145 a95 in varchar2 default chr(0),
146 a96 in varchar2 default chr(0),
147 a97 in varchar2 default chr(0),
148 a98 in varchar2 default chr(0),
149 a99 in varchar2 default chr(0),
150 a100 in varchar2 default chr(0)) return number is
151
152 cd_pos number := 0;
153 cdname varchar2(30);
154 cdid number := -1;
155 flexfield fnd_dflex.dflex_r;
156 flexinfo fnd_dflex.dflex_dr;
157 contexts fnd_dflex.contexts_dr;
158 segments fnd_dflex.segments_dr;
159
160 insert_error exception;
161
162 begin
163
164 fnd_dflex.get_flexfield (app, '$SRS$.'||program, flexfield, flexinfo);
165 fnd_dflex.get_contexts (flexfield, contexts);
166 fnd_dflex.get_segments (fnd_dflex.make_context (
167 flexfield, contexts.context_code (
168 contexts.global_context)),
169 segments,
170 TRUE);
171
172 for i in 1..segments.nsegments loop
173 if segments.segment_name (i) = cd_param then
174 cd_pos := i;
175 exit;
176 end if;
177 end loop;
178
179 if (cd_pos = 0) or (cd_pos > nargs) then
180 return (-1);
181 end if;
182
183 if cd_pos = 1 then cdname := a1; goto end_cd; end if;
184 if cd_pos = 2 then cdname := a2; goto end_cd; end if;
185 if cd_pos = 3 then cdname := a3; goto end_cd; end if;
186 if cd_pos = 4 then cdname := a4; goto end_cd; end if;
187 if cd_pos = 5 then cdname := a5; goto end_cd; end if;
188 if cd_pos = 6 then cdname := a6; goto end_cd; end if;
189 if cd_pos = 7 then cdname := a7; goto end_cd; end if;
190 if cd_pos = 8 then cdname := a8; goto end_cd; end if;
191 if cd_pos = 9 then cdname := a9; goto end_cd; end if;
192 if cd_pos = 10 then cdname := a10; goto end_cd; end if;
193 if cd_pos = 11 then cdname := a11; goto end_cd; end if;
194 if cd_pos = 12 then cdname := a12; goto end_cd; end if;
195 if cd_pos = 13 then cdname := a13; goto end_cd; end if;
196 if cd_pos = 14 then cdname := a14; goto end_cd; end if;
197 if cd_pos = 15 then cdname := a15; goto end_cd; end if;
198 if cd_pos = 16 then cdname := a16; goto end_cd; end if;
199 if cd_pos = 17 then cdname := a17; goto end_cd; end if;
200 if cd_pos = 18 then cdname := a18; goto end_cd; end if;
201 if cd_pos = 19 then cdname := a19; goto end_cd; end if;
202 if cd_pos = 20 then cdname := a20; goto end_cd; end if;
203 if cd_pos = 21 then cdname := a21; goto end_cd; end if;
204 if cd_pos = 22 then cdname := a22; goto end_cd; end if;
205 if cd_pos = 23 then cdname := a23; goto end_cd; end if;
206 if cd_pos = 24 then cdname := a24; goto end_cd; end if;
207 if cd_pos = 25 then cdname := a25; goto end_cd; end if;
208 if cd_pos = 26 then cdname := a26; goto end_cd; end if;
209 if cd_pos = 27 then cdname := a27; goto end_cd; end if;
210 if cd_pos = 28 then cdname := a28; goto end_cd; end if;
211 if cd_pos = 29 then cdname := a29; goto end_cd; end if;
212 if cd_pos = 30 then cdname := a30; goto end_cd; end if;
213 if cd_pos = 31 then cdname := a31; goto end_cd; end if;
214 if cd_pos = 32 then cdname := a32; goto end_cd; end if;
215 if cd_pos = 33 then cdname := a33; goto end_cd; end if;
216 if cd_pos = 34 then cdname := a34; goto end_cd; end if;
217 if cd_pos = 35 then cdname := a35; goto end_cd; end if;
218 if cd_pos = 36 then cdname := a36; goto end_cd; end if;
219 if cd_pos = 37 then cdname := a37; goto end_cd; end if;
220 if cd_pos = 38 then cdname := a38; goto end_cd; end if;
221 if cd_pos = 39 then cdname := a39; goto end_cd; end if;
222 if cd_pos = 40 then cdname := a40; goto end_cd; end if;
223 if cd_pos = 41 then cdname := a41; goto end_cd; end if;
224 if cd_pos = 42 then cdname := a42; goto end_cd; end if;
225 if cd_pos = 43 then cdname := a43; goto end_cd; end if;
226 if cd_pos = 44 then cdname := a44; goto end_cd; end if;
227 if cd_pos = 45 then cdname := a45; goto end_cd; end if;
228 if cd_pos = 46 then cdname := a46; goto end_cd; end if;
229 if cd_pos = 47 then cdname := a47; goto end_cd; end if;
230 if cd_pos = 48 then cdname := a48; goto end_cd; end if;
231 if cd_pos = 49 then cdname := a49; goto end_cd; end if;
232 if cd_pos = 50 then cdname := a50; goto end_cd; end if;
233 if cd_pos = 51 then cdname := a51; goto end_cd; end if;
234 if cd_pos = 52 then cdname := a52; goto end_cd; end if;
235 if cd_pos = 53 then cdname := a53; goto end_cd; end if;
236 if cd_pos = 54 then cdname := a54; goto end_cd; end if;
237 if cd_pos = 55 then cdname := a55; goto end_cd; end if;
238 if cd_pos = 56 then cdname := a56; goto end_cd; end if;
239 if cd_pos = 57 then cdname := a57; goto end_cd; end if;
240 if cd_pos = 58 then cdname := a58; goto end_cd; end if;
241 if cd_pos = 59 then cdname := a59; goto end_cd; end if;
242 if cd_pos = 60 then cdname := a60; goto end_cd; end if;
243 if cd_pos = 61 then cdname := a61; goto end_cd; end if;
244 if cd_pos = 62 then cdname := a62; goto end_cd; end if;
245 if cd_pos = 63 then cdname := a63; goto end_cd; end if;
246 if cd_pos = 64 then cdname := a64; goto end_cd; end if;
247 if cd_pos = 65 then cdname := a65; goto end_cd; end if;
248 if cd_pos = 66 then cdname := a66; goto end_cd; end if;
249 if cd_pos = 67 then cdname := a67; goto end_cd; end if;
250 if cd_pos = 68 then cdname := a68; goto end_cd; end if;
251 if cd_pos = 69 then cdname := a69; goto end_cd; end if;
252 if cd_pos = 70 then cdname := a70; goto end_cd; end if;
253 if cd_pos = 71 then cdname := a71; goto end_cd; end if;
254 if cd_pos = 72 then cdname := a72; goto end_cd; end if;
255 if cd_pos = 73 then cdname := a73; goto end_cd; end if;
256 if cd_pos = 74 then cdname := a74; goto end_cd; end if;
257 if cd_pos = 75 then cdname := a75; goto end_cd; end if;
258 if cd_pos = 76 then cdname := a76; goto end_cd; end if;
259 if cd_pos = 77 then cdname := a77; goto end_cd; end if;
260 if cd_pos = 78 then cdname := a78; goto end_cd; end if;
261 if cd_pos = 79 then cdname := a79; goto end_cd; end if;
262 if cd_pos = 80 then cdname := a80; goto end_cd; end if;
263 if cd_pos = 81 then cdname := a81; goto end_cd; end if;
264 if cd_pos = 82 then cdname := a82; goto end_cd; end if;
265 if cd_pos = 83 then cdname := a83; goto end_cd; end if;
266 if cd_pos = 84 then cdname := a84; goto end_cd; end if;
267 if cd_pos = 85 then cdname := a85; goto end_cd; end if;
268 if cd_pos = 86 then cdname := a86; goto end_cd; end if;
269 if cd_pos = 87 then cdname := a87; goto end_cd; end if;
270 if cd_pos = 88 then cdname := a88; goto end_cd; end if;
271 if cd_pos = 89 then cdname := a89; goto end_cd; end if;
272 if cd_pos = 90 then cdname := a90; goto end_cd; end if;
273 if cd_pos = 91 then cdname := a91; goto end_cd; end if;
274 if cd_pos = 92 then cdname := a92; goto end_cd; end if;
275 if cd_pos = 93 then cdname := a93; goto end_cd; end if;
276 if cd_pos = 94 then cdname := a94; goto end_cd; end if;
277 if cd_pos = 95 then cdname := a95; goto end_cd; end if;
278 if cd_pos = 96 then cdname := a96; goto end_cd; end if;
279 if cd_pos = 97 then cdname := a97; goto end_cd; end if;
280 if cd_pos = 98 then cdname := a98; goto end_cd; end if;
281 if cd_pos = 99 then cdname := a99; goto end_cd; end if;
282 if cd_pos = 100 then cdname := a100; end if;
283
284 <<end_cd>>
285
286 begin -- select block
287 select cd_id
288 into cdid
289 from fnd_conflicts_domain
290 where cd_name = cdname;
291
292 exception
293 when no_data_found then
294 begin -- insert block
295 select fnd_conflicts_domain_s.nextval
296 into cdid
297 from sys.dual;
298
299 insert
300 into fnd_conflicts_domain (
301 cd_id,
302 cd_name,
303 user_cd_name,
304 runalone_flag,
305 last_update_date,
306 last_updated_by,
307 creation_date,
308 created_by,
309 last_update_login,
310 dynamic)
311 values (
312 cdid,
313 cdname,
314 cdname,
315 'N',
316 sysdate,
317 user_id,
318 sysdate,
319 user_id,
320 login_id,
321 'Y');
322
323 if (sql%rowcount = 0) then
324 raise insert_error;
325 end if;
326
330 'Too many rows in sys.dual');
327 exception
328 when no_data_found then
329 fnd_message.set_name ('FND',
331 return (-1);
332
333 when insert_error then
334 fnd_message.set_name ('FND', 'SQL-Generic error');
335 fnd_message.set_token ('ERRNO', sqlcode, FALSE);
336 fnd_message.set_token ('REASON', sqlerrm, FALSE);
337 fnd_message.set_token ('ROUTINE',
338 'SUBMIT: conflicts_domain_insert_error', FALSE);
339 return (-1);
340
341 when dup_val_on_index then
342 select cd_id
343 into cdid
344 from fnd_conflicts_domain
345 where cd_name = cdname;
346
347
348 when others then
349 raise;
350 end; -- insert block
351
352 when others then
353 raise;
354 end; -- select block
355
356 return (cdid);
357
358 exception
359 when others then
360 fnd_message.set_name ('FND', 'SQL-Generic error');
361 fnd_message.set_token ('ERRNO', sqlcode, FALSE);
362 fnd_message.set_token ('REASON', sqlerrm, FALSE);
363 fnd_message.set_token (
364 'ROUTINE', 'SUBMIT: conflicts_domain', FALSE);
365 return (-1);
366
367 end get_cd_id;
368
369
370 --
371 -- Remove all unused 'dynamic' conflict domains
372 -- in order to manage the size of the table.
373 --
374 procedure purge_dynamic_domains is
375 begin
376
377 delete from fnd_conflicts_domain fcd
381 where fcr.cd_id = fcd.cd_id
378 where dynamic = 'Y'
379 and not exists (select 'X'
380 from fnd_concurrent_requests fcr
382 and phase_code in ('P', 'R'));
383
384
385
386 end purge_dynamic_domains;
387
388 end FNDCP_CRM;
[Home] [Help]