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