1 PACKAGE BODY OE_Profile AS
2 /* $Header: OEXPROFB.pls 120.2 2005/12/20 11:34:10 zbutt noship $ */
3
4
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_Profile';
6
7 -- LOCAL PROCEDURES
8 -------------------------------------------------------------------
9 FUNCTION Get_Active_Profile_Name
10 (NAME IN VARCHAR2
11 )
12 RETURN VARCHAR2
13 IS
14 OE_INVALID_INTEROP_PROFILE EXCEPTION;
15 BEGIN
16
17 -- for each profile option name (OE or ONT), return the ONT profile
18 -- option name if active product is ONT and return the OE profile
19 -- option name if active product is OE
20 /* IF ( NAME = 'SO_ORGANIZATION_ID'
21 OR NAME = 'OE_ORGANIZATION_ID') THEN
22
23 IF OE_INSTALL.Get_Active_Product = 'ONT' THEN
24 RETURN('OE_ORGANIZATION_ID');
25 ELSE
26 RETURN('SO_ORGANIZATION_ID');
27 END IF;
28
29 ELSIF ( NAME = 'SO_SET_OF_BOOKS_ID'
30 OR NAME = 'OE_SET_OF_BOOKS_ID') THEN
31
32 IF OE_INSTALL.Get_Active_Product = 'ONT' THEN
33 RETURN('OE_SET_OF_BOOKS_ID');
34 ELSE
35 RETURN('SO_SET_OF_BOOKS_ID');
36 END IF;
37 */
38
39 IF ( NAME = 'SO_ID_FLEX_CODE'
40 OR NAME = 'OE_ID_FLEX_CODE') THEN
41
42 IF OE_INSTALL.Get_Active_Product = 'ONT' THEN
43 RETURN('OE_ID_FLEX_CODE');
44 ELSE
45 RETURN('SO_ID_FLEX_CODE');
46 END IF;
47
48 ELSIF ( NAME = 'SO_SOURCE_CODE'
49 OR NAME = 'OE_SOURCE_CODE') THEN
50
51 IF OE_INSTALL.Get_Active_Product = 'ONT' THEN
52 RETURN('OE_SOURCE_CODE');
53 ELSE
54 RETURN('SO_SOURCE_CODE');
55 END IF;
56
57 ELSIF ( NAME = 'SO_INVENTORY_ITEM_FOR_FREIGHT'
58 OR NAME = 'OE_INVENTORY_ITEM_FOR_FREIGHT') THEN
59
60 IF OE_INSTALL.Get_Active_Product = 'ONT' THEN
61 RETURN('OE_INVENTORY_ITEM_FOR_FREIGHT');
62 ELSE
63 RETURN('SO_INVENTORY_ITEM_FOR_FREIGHT');
64 END IF;
65
66 ELSE
67
68 RAISE OE_INVALID_INTEROP_PROFILE;
69
70 END IF;
71
72 END Get_Active_Profile_Name;
73
74
75 -- PUBLIC PROCEDURES
76 -------------------------------------------------------------------
77 PROCEDURE GET
78 (NAME IN VARCHAR2
79 ,VAL OUT NOCOPY /* file.sql.39 change */ VARCHAR2
80 )
81 IS
82 l_active_profile VARCHAR2(80);
83 BEGIN
84
85 IF UPPER(NAME) = 'SO_ORGANIZATION_ID' or UPPER(NAME) = 'OE_ORGANIZATION_ID' THEN
86
87 VAL := OE_Sys_Parameters.VALUE(param_name => 'MASTER_ORGANIZATION_ID');
88
89 ELSIF UPPER(NAME) = 'SO_SET_OF_BOOKS_ID' OR UPPER(NAME) = 'OE_SET_OF_BOOKS_ID' THEN
90
91 VAL := OE_Sys_Parameters.VALUE(param_name => 'SET_OF_BOOKS_ID');
92
93 ELSE
94
95 l_active_profile := Get_Active_Profile_Name(NAME);
96
97 FND_PROFILE.GET(l_active_profile,VAL);
98 END IF;
99 END GET;
100
101
102 -------------------------------------------------------------------
103 FUNCTION VALUE
104 (NAME IN VARCHAR2,
105 ORG_ID IN NUMBER DEFAULT NULL
106 )
107 RETURN VARCHAR2
108 IS
109 l_active_profile VARCHAR2(80);
110 l_profile_value VARCHAR2(255);
111 BEGIN
112
113 IF UPPER(NAME) = 'SO_ORGANIZATION_ID' or UPPER(NAME) = 'OE_ORGANIZATION_ID' THEN
114
115 l_profile_value := OE_Sys_Parameters.VALUE(param_name => 'MASTER_ORGANIZATION_ID', p_org_id => ORG_ID);
116
117 ELSIF UPPER(NAME) = 'SO_SET_OF_BOOKS_ID' OR UPPER(NAME) = 'OE_SET_OF_BOOKS_ID' THEN
118 l_profile_value := OE_Sys_Parameters.VALUE(param_name => 'SET_OF_BOOKS_ID', p_org_id => ORG_ID);
119
120 ELSE
121
122 l_active_profile := Get_Active_Profile_Name(NAME);
123
124 l_profile_value := FND_PROFILE.VALUE(l_active_profile);
125
126 END IF;
127 RETURN(l_profile_value);
128
129 END VALUE;
130
131
132 -------------------------------------------------------------------
133 FUNCTION VALUE_WNPS
134 (NAME IN VARCHAR2,
135 ORG_ID IN NUMBER DEFAULT NULL
136 )
137 RETURN VARCHAR2
138 IS
139 l_active_profile VARCHAR2(80);
140 l_profile_value VARCHAR2(255);
141 BEGIN
142
143 IF UPPER(NAME) = 'SO_ORGANIZATION_ID' or UPPER(NAME) = 'OE_ORGANIZATION_ID' THEN
144
145 l_profile_value := OE_Sys_Parameters.VALUE_WNPS(param_name => 'MASTER_ORGANIZATION_ID', p_org_id => ORG_ID);
146
147 ELSIF UPPER(NAME) = 'SO_SET_OF_BOOKS_ID' OR UPPER(NAME) = 'OE_SET_OF_BOOKS_ID' THEN
148 l_profile_value := OE_Sys_Parameters.VALUE_WNPS(param_name => 'SET_OF_BOOKS_ID', p_org_id => ORG_ID);
149
150 ELSE
151 l_active_profile := Get_Active_Profile_Name(NAME);
152
153 l_profile_value := FND_PROFILE.VALUE_WNPS(l_active_profile);
154 END IF;
155
156 RETURN(l_profile_value);
157
158 END VALUE_WNPS;
159
160
161 -------------------------------------------------------------------
162 FUNCTION VALUE_SPECIFIC
163 (NAME IN VARCHAR2
164 ,USER_ID IN NUMBER DEFAULT NULL
165 ,RESPONSIBILITY_ID IN NUMBER DEFAULT NULL
166 ,APPLICATION_ID IN NUMBER DEFAULT NULL
167 )
168 RETURN VARCHAR2
169 IS
170 -- l_org_id NUMBER;
171 l_active_profile VARCHAR2(80);
172 l_profile_value VARCHAR2(255);
173 BEGIN
174
175 IF UPPER(NAME) = 'SO_ORGANIZATION_ID' or UPPER(NAME) = 'OE_ORGANIZATION_ID' THEN
176 IF (USER_ID IS NULL AND RESPONSIBILITY_ID IS NULL AND APPLICATION_ID IS NULL) THEN
177 l_profile_value := OE_Sys_Parameters.VALUE(
178 param_name => 'MASTER_ORGANIZATION_ID');
179 -- removing ELSE clause as part of MOAC project as we should not be looking at the ORG_ID profile.
180 -- this can be taken out NOCOPY /* file.sql.39 change */ completely as no caller is populating the user/app/resp when calling this procedure
181 /*
182 ELSE
183 l_org_id := TO_NUMBER(FND_PROFILE.Value_Specific('ORG_ID',
184 USER_ID, RESPONSIBILITY_ID, APPLICATION_ID));
185 l_profile_value := OE_Sys_Parameters.VALUE(
186 param_name => 'MASTER_ORGANIZATION_ID',
187 p_org_id => l_org_id);
188 */
189 END IF;
190
191 ELSIF UPPER(NAME) = 'SO_SET_OF_BOOKS_ID' OR UPPER(NAME) = 'OE_SET_OF_BOOKS_ID' THEN
192 IF (USER_ID IS NULL AND RESPONSIBILITY_ID IS NULL AND APPLICATION_ID IS NULL) THEN
193 l_profile_value := OE_Sys_Parameters.VALUE(
194 param_name => 'SET_OF_BOOKS_ID');
195 -- removing ELSE clause as part of MOAC project as we should not be looking at the ORG_ID profile.
196 -- this can be taken out NOCOPY /* file.sql.39 change */ completely as no caller is populating the user/app/resp when calling this procedure
197 /*
198 ELSE
199 l_org_id := TO_NUMBER(FND_PROFILE.Value_Specific('ORG_ID',
200 USER_ID, RESPONSIBILITY_ID, APPLICATION_ID));
201 l_profile_value := OE_Sys_Parameters.VALUE(
202 param_name => 'SET_OF_BOOKS_ID',
203 p_org_id => l_org_id);
204 */
205 END IF;
206
207 ELSE
208
209
210 l_active_profile := Get_Active_Profile_Name(NAME);
211
212 l_profile_value := FND_PROFILE.VALUE_SPECIFIC(l_active_profile
213 , USER_ID
214 , RESPONSIBILITY_ID
215 , APPLICATION_ID);
216 END IF;
217 RETURN(l_profile_value);
218
219 END VALUE_SPECIFIC;
220
221
222 /* Overloaded Value function to return profile retrieved in created_by context */
223 FUNCTION VALUE (p_header_id IN NUMBER DEFAULT NULL,
224 p_line_id IN NUMBER DEFAULT NULL,
225 p_profile_option_name IN VARCHAR2)
226
227 RETURN VARCHAR2
228
229 IS
230 l_cached_resp_appl_id NUMBER := Null;
231 l_cached_user_id NUMBER := Null;
232 l_cached_resp_id NUMBER := Null;
233 l_cached_org_id NUMBER := Null;
234 l_get_cache_triplet_result VARCHAR2(1) := 'F';
235 l_get_cache_profile_result VARCHAR2(1) := 'F';
236 l_profile_option_value VARCHAR2(260) := Null;
237 l_wf_entity VARCHAR2(8) := Null;
238 l_id_passed VARCHAR2(1) := 'N';
239 p_entity_id NUMBER;
240
241 --
242 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
243 --
244 BEGIN
245
246 IF l_debug_level > 0 THEN
247 oe_debug_pub.add('ENTERING OE_PROFILE.VALUE');
248 oe_debug_pub.add('header_id: ' || p_header_id);
249 oe_debug_pub.add('line_id: ' || p_line_id);
250 oe_debug_pub.add('profile option: '|| p_profile_option_name);
251 END IF;
252 IF OE_GLOBALS.G_FLOW_RESTARTED AND (l_debug_level > 0) THEN
253 oe_debug_pub.add('FLOW_RESTARTED GLOBAL set to TRUE');
254 ELSE
255 oe_debug_pub.add('FLOW_RESTARTED GLOBAL set to FALSE');
256 END IF;
257
258 IF OE_GLOBALS.G_USE_CREATED_BY_CONTEXT AND (l_debug_level > 0) THEN
259 oe_debug_pub.add('USE CREATED_BY GLOBAL set to TRUE');
260 ELSE
261 oe_debug_pub.add('USE CREATED_BY GLOBAL set to FALSE');
262 END IF;
263
264
265
266
267 IF OE_GLOBALS.G_FLOW_RESTARTED
268 OR OE_GLOBALS.G_USE_CREATED_BY_CONTEXT
269 -- Bug 4884429, added following OR condition
270 OR (p_profile_option_name = 'OE_NOTIFICATION_APPROVER') THEN
271 --this profile is being accessed as part of a restarted flow or in a flow which should used the created by context
272
273
274 IF p_header_id IS NOT NULL THEN
275 l_wf_entity := OE_GLOBALS.G_WFI_HDR;
276 p_entity_id := p_header_id;
277 l_id_passed := 'Y';
278 ELSIF p_line_id IS NOT NULL THEN
279 l_wf_entity := OE_GLOBALS.G_WFI_LIN;
280 p_entity_id := p_line_id;
281 l_id_passed := 'Y';
282 END IF;
283
284 IF l_id_passed = 'Y' THEN
285 --check to see if the context triplet has already been cached for this entity
286 GET_CACHED_CONTEXT( p_entity => l_wf_entity,
287 p_entity_id => p_entity_id,
288 x_application_id => l_cached_resp_appl_id,
289 x_user_id => l_cached_user_id,
290 x_responsibility_id => l_cached_resp_id,
291 x_org_id => l_cached_org_id,
292 x_result => l_get_cache_triplet_result);
293
294 IF l_get_cache_triplet_result <> 'S' THEN
295 --triplet was not found in the cache
296 --retrieve triplet values
297
298 IF l_debug_level > 0 THEN
299 oe_debug_pub.add('context not found in cache');
300 END IF;
301
302
303 BEGIN
304 IF l_debug_level > 0 THEN
305 oe_debug_pub.add('getting user and org from base tables');
306 END IF;
307 IF l_wf_entity = OE_GLOBALS.G_WFI_HDR THEN
308
309 SELECT created_by, org_id
310 INTO l_cached_user_id, l_cached_org_id
311 FROM oe_order_headers_all
312 WHERE header_id = p_header_id;
313
314 ELSE
315
316 SELECT created_by, org_id
317 INTO l_cached_user_id, l_cached_org_id
318 FROM oe_order_lines_all
319 WHERE line_id = p_line_id;
320
321 END IF;
322 EXCEPTION
323 WHEN NO_DATA_FOUND THEN
324 IF l_debug_level > 0 THEN
325 oe_debug_pub.add('IN NO_DATA_FOUND when retrieving user and org');
326 END IF;
327 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
328 THEN
329 OE_MSG_PUB.Add_Exc_Msg
330 ( G_PKG_NAME
331 , 'Value'
332 );
333 END IF;
334 Null;
335 WHEN OTHERS THEN
336 IF l_debug_level > 0 THEN
337 oe_debug_pub.add('IN OTHERS when retrieving user and org' || SQLERRM);
338 END IF;
339 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
340 THEN
341 OE_MSG_PUB.Add_Exc_Msg
342 ( G_PKG_NAME
343 , 'Value'
344 );
345 END IF;
346 Null;
347 END;
348
349 l_cached_resp_appl_id := wf_engine.GetItemAttrNumber(l_wf_entity
350 , p_entity_id
351 , 'APPLICATION_ID'
352 );
353
354
355 l_cached_resp_id := wf_engine.GetItemAttrNumber(l_wf_entity
356 , p_entity_id
357 , 'RESPONSIBILITY_ID'
358 );
359
360
361 IF l_debug_level > 0 THEN
362 oe_debug_pub.add('before caching the retrieved context');
363 END IF;
364 --Store retrieved triplet values in cache
365 PUT_CACHED_CONTEXT( p_entity => l_wf_entity,
366 p_entity_id => p_entity_id,
367 p_application_id => l_cached_resp_appl_id,
368 p_user_id => l_cached_user_id,
369 p_responsibility_id => l_cached_resp_id,
370 p_org_id => l_cached_org_id);
371 IF l_debug_level > 0 THEN
372 oe_debug_pub.add('after caching the retrieved context');
373 END IF;
374
375 END IF;
376 -- end of l_get_cache_triplet_result <> 'S'
377 -- at this point we should have the context triplet
378 IF l_debug_level > 0 THEN
379 oe_debug_pub.add('before attempting to get the cached profile option value');
380 END IF;
381 GET_CACHED_PROFILE_FOR_CONTEXT( p_profile_option_name => p_profile_option_name,
382 p_application_id => l_cached_resp_appl_id,
383 p_user_id => l_cached_user_id,
384 p_responsibility_id => l_cached_resp_id,
385 p_org_id => l_cached_org_id,
386 x_profile_option_value => l_profile_option_value,
387 x_result => l_get_cache_profile_result);
388 IF l_debug_level > 0 THEN
389 oe_debug_pub.add('after attempting to get the cached profile option value');
390 END IF;
391
392 IF l_get_cache_profile_result <> 'S' THEN
393 -- profile option value was not found in cache.
394 -- call FND_PROFILE.Value_Specific to retrieve context-based profile option value
395 IF l_debug_level > 0 THEN
396 oe_debug_pub.add('profile value was not found in the cache...calling FND_PROFILE.value_specific');
397 END IF;
398 l_profile_option_value := FND_PROFILE.Value_Specific(p_profile_option_name, l_cached_user_id,
399 l_cached_resp_id, l_cached_resp_appl_id,
400 l_cached_org_id);
401
402 IF l_debug_level > 0 THEN
403 oe_debug_pub.add('profile option value from value_specific: ' || l_profile_option_value);
404 END IF;
405
406 IF l_profile_option_value IS NULL THEN
407 --make original call to FND_PROFILE.Value
408 IF l_debug_level > 0 THEN
409 oe_debug_pub.add('got NULL...calling FND_PROFILE.value');
410 END IF;
411 l_profile_option_value := FND_PROFILE.Value(p_profile_option_name);
412
413 IF l_debug_level > 0 THEN
414 oe_debug_pub.add('profile option value from value: ' || l_profile_option_value);
415 END IF;
416
417 ELSE
418 -- FND_PROFILE.Value_Specfic returned a value, so cache it for later use
419 IF l_debug_level > 0 THEN
420 oe_debug_pub.add('before caching the retrieved profile option value');
421 END IF;
422 PUT_CACHED_PROFILE_FOR_CONTEXT( p_profile_option_name => p_profile_option_name,
423 p_application_id => l_cached_resp_appl_id,
424 p_user_id => l_cached_user_id,
425 p_responsibility_id => l_cached_resp_id,
426 p_org_id => l_cached_org_id,
427 p_profile_option_value => l_profile_option_value);
428
429 IF l_debug_level > 0 THEN
430 oe_debug_pub.add('after caching the retrieved profile option value');
431 END IF;
432 END IF;
433 --end of l_profile_option_value IS NULL
434 END IF;
435 -- end of l_get_cache_profile_result <> 'S'
436 ELSE
437 -- no id was passed so call should use session context so use FND_PROFILE.value
438 IF l_debug_level > 0 THEN
439 oe_debug_pub.add('no id was passed, calling FND_PROFILE.value');
440 END IF;
441 l_profile_option_value := FND_PROFILE.Value(p_profile_option_name);
442 IF l_debug_level > 0 THEN
443 oe_debug_pub.add('profile option value from value: ' || l_profile_option_value);
444 END IF;
445 END IF;
446 -- end of l_id_passed = 'Y'
447 ELSE
448 --neither flag was set so call should use session context so use FND_PROFILE.value
449 IF l_debug_level > 0 THEN
450 oe_debug_pub.add('neither global was set, calling FND_PROFILE.value');
451 END IF;
452 l_profile_option_value := FND_PROFILE.Value(p_profile_option_name);
453 IF l_debug_level > 0 THEN
454 oe_debug_pub.add('profile option value from value: ' || l_profile_option_value);
455 END IF;
456 END IF;
457 -- end of OE_GLOBALS.G_FLOW_RESTARTED OR
458 -- OE_GLOBALS.G_USE_CREATED_BY_CONTEXT
459 -- at this point we should have the profile option value
460 IF l_debug_level > 0 THEN
461 oe_debug_pub.add('profile option value being returned: ' || l_profile_option_value);
462 oe_debug_pub.add('EXITING OE_PROFILE.VALUE');
463 END IF;
464 Return l_profile_option_value;
465
466 EXCEPTION
467 WHEN OTHERS THEN
468 IF l_debug_level > 0 THEN
469 oe_debug_pub.add('IN OTHERS IN OE_PROFILE.VALUE:' || SQLERRM);
470 END IF;
471 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
472 THEN
473 OE_MSG_PUB.Add_Exc_Msg
474 ( G_PKG_NAME
475 , 'Value'
476 );
477 END IF;
478 return null;
479 END VALUE;
480
481
482
483 PROCEDURE GET_CACHED_CONTEXT( p_entity IN VARCHAR2,
484 p_entity_id IN NUMBER,
485 x_application_id OUT NOCOPY NUMBER,
486 x_user_id OUT NOCOPY NUMBER,
487 x_responsibility_id OUT NOCOPY NUMBER,
488 x_org_id OUT NOCOPY NUMBER,
489 x_result OUT NOCOPY VARCHAR2)
490 IS
491 --
492 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
493 --
494 BEGIN
495
496 IF l_debug_level > 0 THEN
497 oe_debug_pub.add('ENTERING OE_PROFILE.GET_CACHED_CONTEXT');
498 oe_debug_pub.add('entity: ' || p_entity);
499 oe_debug_pub.add('entity_id: ' || p_entity_id);
500 END IF;
501
502
503 IF (p_entity = OE_GLOBALS.G_WFI_HDR) THEN
504 IF Header_Context_Tbl.EXISTS(p_entity_id) THEN
505 IF l_debug_level > 0 THEN
506 oe_debug_pub.add('Context was found in cache');
507 END IF;
508 --triplet values have already been cached for this line_id
509 x_application_id := Header_Context_Tbl(p_entity_id).resp_appl_id;
510 x_user_id := Header_Context_Tbl(p_entity_id).user_id;
511 x_responsibility_id := Header_Context_Tbl(p_entity_id).resp_id;
512 x_org_id := Header_Context_Tbl(p_entity_id).org_id;
513 x_result := 'S';
514
515 ELSE
516 IF l_debug_level > 0 THEN
517 oe_debug_pub.add('Context was NOT found in cache');
518 END IF;
519 --triplet values have not been cached for this line_id
520 x_application_id := null;
521 x_user_id := null;
522 x_responsibility_id :=null;
523 x_org_id := null;
524 x_result := 'F';
525 END IF;
526 --end of Header_Context_Tbl.EXISTS(p_entity_id)
527 ELSIF (p_entity = OE_GLOBALS.G_WFI_LIN) THEN
528 IF Line_Context_Tbl.EXISTS(p_entity_id) THEN
529 IF l_debug_level > 0 THEN
530 oe_debug_pub.add('Context was found in cache');
531 END IF;
532 --triplet values have already been cached for this line_id
533 x_application_id := Line_Context_Tbl(p_entity_id).resp_appl_id;
534 x_user_id := Line_Context_Tbl(p_entity_id).user_id;
535 x_responsibility_id := Line_Context_Tbl(p_entity_id).resp_id;
536 x_org_id := Line_Context_Tbl(p_entity_id).org_id;
537 x_result := 'S';
538
539 ELSE
540 IF l_debug_level > 0 THEN
541 oe_debug_pub.add('Context was NOT found in cache');
542 END IF;
543 --triplet values have not been cached for this line_id
544 x_application_id := null;
545 x_user_id := null;
546 x_responsibility_id :=null;
547 x_org_id := null;
548 x_result := 'F';
549 END IF;
550 --end of Line_Context_Tbl.EXISTS(p_entity_id)
551 END IF;
552 --end of p_entity = OE_GLOBALS.G_WFI_HDR
553
554 IF l_debug_level > 0 THEN
555 oe_debug_pub.add('application from cache: ' || x_application_id);
556 oe_debug_pub.add('user from cache: ' || x_user_id);
557 oe_debug_pub.add('responsibility from cache: ' || x_responsibility_id);
558 oe_debug_pub.add('org from cache: ' || x_org_id);
559 oe_debug_pub.add('EXITING OE_PROFILE.GET_CACHED_CONTEXT');
560 END IF;
561
562
563 EXCEPTION
564 WHEN OTHERS THEN
565 IF l_debug_level > 0 THEN
566 oe_debug_pub.add('OTHERS IN OE_PROFILE.GET_CACHED_CONTEXT: ' || SQLERRM);
567 END IF;
568 x_result := 'E';
569 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
570 THEN
571 OE_MSG_PUB.Add_Exc_Msg
572 ( G_PKG_NAME
573 , 'Get_Cached_Context'
574 );
575 END IF;
576 END GET_CACHED_CONTEXT;
577
578
579 PROCEDURE PUT_CACHED_CONTEXT( p_entity IN VARCHAR2,
580 p_entity_id IN NUMBER,
581 p_application_id IN NUMBER,
582 p_user_id IN NUMBER,
583 p_responsibility_id IN NUMBER,
584 p_org_id IN NUMBER)
585 IS
586 I varchar2(30);
587 l_new_line_position number;
588 l_new_header_position number;
589
590 --
591 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
592 --
593 BEGIN
594
595 IF l_debug_level > 0 THEN
596 oe_debug_pub.add('ENTERING OE_PROFILE.PUT_CACHED_CONTEXT');
597 oe_debug_pub.add('entity: ' || p_entity);
598 oe_debug_pub.add('entity_id: ' || p_entity_id);
599 oe_debug_pub.add('application: '|| p_application_id);
600 oe_debug_pub.add('user: ' || p_user_id);
601 oe_debug_pub.add('responsibility: ' || p_responsibility_id);
602 oe_debug_pub.add('org: ' || p_org_id);
603 END IF;
604
605
606
607 IF (p_entity = OE_GLOBALS.G_WFI_HDR) THEN
608 IF l_debug_level > 0 THEN
609 oe_debug_pub.add('the cache is currently holding ' || Header_Context_Tbl.count || ' records.');
610 END IF;
611
612 -- triplet values need to be cached for this header_id
613 --first check to see if there is space in the cache
614 IF Header_Context_Tbl.count < MAX_CONTEXT_CACHE_SIZE THEN
615 IF l_debug_level > 0 THEN
616 oe_debug_pub.add('there is space in the cache');
617 END IF;
618 --there is space in the cache so simply add the data
619 -- set new position
620 l_new_header_position := Header_Context_Tbl.count + 1;
621
622 Header_Context_Tbl(p_entity_id).position := l_new_header_position;
623 Header_Context_Tbl(p_entity_id).resp_appl_id := p_application_id;
624 Header_Context_Tbl(p_entity_id).user_id := p_user_id;
625 Header_Context_Tbl(p_entity_id).resp_id := p_responsibility_id;
626 Header_Context_Tbl(p_entity_id).org_id := p_org_id;
627
628
629 IF l_debug_level > 0 THEN
630 oe_debug_pub.add('added new record at position: ' || Header_Context_Tbl(p_entity_id).position);
631 oe_debug_pub.add('now there are ' || Header_Context_Tbl.count || ' records in the cache after adding the new record');
632 END IF;
633
634 ELSE
635 --the cache is full, so a record needs to be removed
636 IF l_debug_level > 0 THEN
637 oe_debug_pub.add('cache is full. removing a record...');
638 END IF;
639 I := Header_Context_Tbl.First;
640 While I is NOT NULL
641 LOOP
642 IF l_debug_level > 0 THEN
643 oe_debug_pub.add('position: ' || Header_Context_Tbl(I).position || ' I: ' || I);
644 END IF;
645 IF Header_Context_Tbl(I).position = 1 THEN
646 --if the record is in position 1, remove it
647 Header_Context_Tbl.Delete(I);
648 IF l_debug_level > 0 THEN
649 oe_debug_pub.add('deleted the record at position 1 with index: ' || I);
650 oe_debug_pub.add('now there are ' || Header_Context_Tbl.count || ' header records in the cache');
651 END IF;
652 ELSE
653 --record position needs to be shifted down
654 Header_Context_Tbl(I).position := Header_Context_Tbl(I).position - 1;
655 END IF;
656 --end of Header_Context_Tbl(I).position = 1
657 I := Header_Context_Tbl.NEXT(I);
658 END LOOP;
659 -- set new position
660
661 l_new_header_position := Header_Context_Tbl.count + 1;
662 Header_Context_Tbl(p_entity_id).position := l_new_header_position;
663 -- now add new header to cache
664 Header_Context_Tbl(p_entity_id).resp_appl_id := p_application_id;
665 Header_Context_Tbl(p_entity_id).user_id := p_user_id;
666 Header_Context_Tbl(p_entity_id).resp_id := p_responsibility_id;
667 Header_Context_Tbl(p_entity_id).org_id := p_org_id;
668
669 IF l_debug_level > 0 THEN
670 oe_debug_pub.add('added new record at position: ' || Header_Context_Tbl(p_entity_id).position);
671 oe_debug_pub.add('now there are ' || Header_Context_Tbl.count || ' records in the cache after adding the new record');
672 END IF;
673
674 END IF;
675 -- end of Header_Context_Tbl.count < MAX_CONTEXT_CACHE_SIZE
676
677
678 ELSIF (p_entity = OE_GLOBALS.G_WFI_LIN) THEN
679 IF l_debug_level > 0 THEN
680 oe_debug_pub.add('the cache is currently holding ' || Line_Context_Tbl.count || ' records.');
681 END IF;
682
683 -- triplet values need to be cached for this line_id
684 --first check to see if there is space in the cache
685 IF Line_Context_Tbl.count < MAX_CONTEXT_CACHE_SIZE THEN
686 IF l_debug_level > 0 THEN
687 oe_debug_pub.add('there is space in the cache.');
688 END IF;
689 --there is space in the cache so simply add the data
690 -- set new position
691 l_new_line_position := Line_Context_Tbl.count + 1;
692 Line_Context_Tbl(p_entity_id).position := l_new_line_position;
693 Line_Context_Tbl(p_entity_id).resp_appl_id := p_application_id;
694 Line_Context_Tbl(p_entity_id).user_id := p_user_id;
695 Line_Context_Tbl(p_entity_id).resp_id := p_responsibility_id;
696 Line_Context_Tbl(p_entity_id).org_id := p_org_id;
697
698 IF l_debug_level > 0 THEN
699 oe_debug_pub.add('added new record at position: ' || Line_Context_Tbl(p_entity_id).position);
700 oe_debug_pub.add('now there are ' || Line_Context_Tbl.count || ' records in the cache after adding the new record');
701 END IF;
702
703
704 ELSE
705 IF l_debug_level > 0 THEN
706 oe_debug_pub.add('cache is full. removing a record...');
707 END IF;
708 --the cache is full, so a record needs to be removed
709 I := Line_Context_Tbl.First;
710 While I is NOT NULL
711 LOOP
712 IF l_debug_level > 0 THEN
713 oe_debug_pub.add('position: ' || Line_Context_Tbl(I).position || ' I: ' || I);
714 END IF;
715 IF Line_Context_Tbl(I).position = 1 THEN
716 --if the record is in position 1, remove it
717 Line_Context_Tbl.Delete(I);
718 IF l_debug_level > 0 THEN
719 oe_debug_pub.add('deleted the record at position 1 with index: ' || I);
720 oe_debug_pub.add('now there are ' || Line_Context_Tbl.count || ' line records in the cache');
721 END IF;
722 ELSE
723 --record position needs to be shifted down
724 Line_Context_Tbl(I).position := Line_Context_Tbl(I).position - 1;
725 END IF;
726 --end of Line_Context_Tbl(I).position = 1
727 I := Line_Context_Tbl.NEXT(I);
728 END LOOP;
729 -- set new position
730 l_new_line_position := Line_Context_Tbl.count + 1;
731 Line_Context_Tbl(p_entity_id).position := l_new_line_position;
732 -- now add new line to cache
733 Line_Context_Tbl(p_entity_id).resp_appl_id := p_application_id;
734 Line_Context_Tbl(p_entity_id).user_id := p_user_id;
735 Line_Context_Tbl(p_entity_id).resp_id := p_responsibility_id;
736 Line_Context_Tbl(p_entity_id).org_id := p_org_id;
737
738 IF l_debug_level > 0 THEN
739 oe_debug_pub.add('added new record at position: ' || Line_Context_Tbl(p_entity_id).position);
740 oe_debug_pub.add('now there are ' || Line_Context_Tbl.count || ' records in the cache after adding the new record');
741 END IF;
742
743
744 END IF;
745 -- end of Line_Context_Tbl.count < MAX_CONTEXT_CACHE_SIZE
746
747 END IF;
748 -- End of p_entity = OE_GLOBALS.G_WFI_HDR
749
750
751 EXCEPTION
752 WHEN OTHERS THEN
753 IF l_debug_level > 0 THEN
754 oe_debug_pub.add('OTHERS IN OE_PROFILE.PUT_CACHED_CONTEXT: ' || SQLERRM);
755 END IF;
756 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
757 THEN
758 OE_MSG_PUB.Add_Exc_Msg
759 ( G_PKG_NAME
760 , 'Put_Cached_Context'
761 );
762 END IF;
763 END PUT_CACHED_CONTEXT;
764
765
766
767 PROCEDURE GET_CACHED_PROFILE_FOR_CONTEXT( p_profile_option_name IN VARCHAR2,
768 p_application_id IN NUMBER,
769 p_user_id IN NUMBER,
770 p_responsibility_id IN NUMBER,
771 p_org_id IN NUMBER,
772 x_profile_option_value OUT NOCOPY VARCHAR2,
773 x_result OUT NOCOPY VARCHAR2)
774 IS
775
776 l_concat_segment VARCHAR2(100);
777
778 --
779 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
780 --
781
782 BEGIN
783
784 IF l_debug_level > 0 THEN
785 oe_debug_pub.add('ENTERING OE_PROFILE.GET_CACHED_PROFILE_FOR_CONTEXT');
786 oe_debug_pub.add('profile: ' || p_profile_option_name);
787 oe_debug_pub.add('application: '|| p_application_id);
788 oe_debug_pub.add('user: ' || p_user_id);
789 oe_debug_pub.add('responsibility: ' || p_responsibility_id);
790 oe_debug_pub.add('org: ' || p_org_id);
791 END IF;
792
793
794 l_concat_segment := 'u'||p_user_id||'r'||p_responsibility_id||'a'||p_application_id ||'o'||
795 p_org_id|| 'p' || p_profile_option_name;
796
797 IF Prf_Tbl.EXISTS(l_concat_segment) THEN
798 IF l_debug_level > 0 THEN
799 oe_debug_pub.add('index already exists in profile cache.');
800 END IF;
801
802 x_profile_option_value := Prf_Tbl(l_concat_segment).prf_value;
803 x_result := 'S';
804
805 ELSE
806 x_profile_option_value := null;
807 x_result := 'F';
808 IF l_debug_level > 0 THEN
809 oe_debug_pub.add('index does not exist in profile cache.');
810 END IF;
811 Return;
812 END IF;
813 -- end of Prf_Tbl.EXISTS(l_concat_segment)
814
815 IF l_debug_level > 0 THEN
816 oe_debug_pub.add('EXITING OE_PROFILE.GET_CACHED_PROFILE_FOR_CONTEXT');
817 END IF;
818
819 EXCEPTION
820 WHEN OTHERS THEN
821 x_profile_option_value := null;
822 x_result := 'E';
823 IF l_debug_level > 0 THEN
824 oe_debug_pub.add('OTHERS IN OE_PROFILE.GET_CACHED_PROFILE_FOR_CONTEXT: ' || SQLERRM);
825 END IF;
826 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
827 THEN
828 OE_MSG_PUB.Add_Exc_Msg
829 ( G_PKG_NAME
830 , 'Get_Cached_Profile_For_Context'
831 );
832 END IF;
833 END GET_CACHED_PROFILE_FOR_CONTEXT;
834
835
836
837 PROCEDURE PUT_CACHED_PROFILE_FOR_CONTEXT( p_profile_option_name IN VARCHAR2,
838 p_application_id IN NUMBER,
839 p_user_id IN NUMBER,
840 p_responsibility_id IN NUMBER,
841 p_org_id IN NUMBER,
842 p_profile_option_value IN VARCHAR2)
843 IS
844 I Varchar2(100);
845 l_new_prf_position number;
846 l_concat_segment VARCHAR2(100);
847 --
848 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
849 --
850 BEGIN
851
852 IF l_debug_level > 0 THEN
853 oe_debug_pub.add('ENTERING OE_PROFILE.PUT_CACHED_PROFILE_FOR_CONTEXT');
854 oe_debug_pub.add('Records currently stored in profile cache: ' || Prf_Tbl.count);
855 oe_debug_pub.add('profile: ' || p_profile_option_name);
856 oe_debug_pub.add('application: '|| p_application_id);
857 oe_debug_pub.add('user: ' || p_user_id);
858 oe_debug_pub.add('responsibility: ' || p_responsibility_id);
859 oe_debug_pub.add('org: ' || p_org_id);
860 oe_debug_pub.add('profile value:' || p_profile_option_value);
861 END IF;
862
863
864 l_concat_segment := 'u'||p_user_id||'r'||p_responsibility_id||'a'||p_application_id ||'o'||
865 p_org_id|| 'p' || p_profile_option_name;
866
867
868 -- see if there is space in the cache
869 IF Prf_Tbl.count < MAX_PROFILE_CACHE_SIZE THEN
870
871 IF l_debug_level > 0 THEN
872 oe_debug_pub.add('space is available in the cache so adding the record at position: ' || (Prf_Tbl.count + 1));
873 END IF;
874 --there is space in the cache so simply add the data
875 --get new position
876 l_new_prf_position := Prf_Tbl.count + 1;
877 Prf_Tbl(l_concat_segment).position := l_new_prf_position;
878 Prf_Tbl(l_concat_segment).prf_value := p_profile_option_value;
879
880
881 IF l_debug_level > 0 THEN
882 oe_debug_pub.add('now there are ' || Prf_Tbl.count || ' records in the cache after adding the new record');
883 END IF;
884
885
886 ELSE
887 IF l_debug_level > 0 THEN
888 oe_debug_pub.add('cache is full so remove a record');
889 END IF;
890
891 -- the cache is full, so a record needs to be removed
892 I := Prf_Tbl.First;
893 While I is NOT NULL
894 LOOP
895 IF l_debug_level > 0 THEN
896 oe_debug_pub.add('position: ' || Prf_Tbl(I).position || ' I: ' || I);
897 END IF;
898 IF Prf_Tbl(I).position = 1 THEN
899 -- if the record is in position 1, remove it
900 Prf_Tbl.Delete(I);
901 IF l_debug_level > 0 THEN
902 oe_debug_pub.add('deleted the record at position 1 with index: ' || I);
903 oe_debug_pub.add('now there are ' || Prf_Tbl.count || ' profile records in the cache');
904 END IF;
905 ELSE
906 --record position needs to be shifted down
907 Prf_Tbl(I).position := Prf_Tbl(I).position - 1;
908 END IF;
909 --end of Prf_Tbl(I).position = 1
910 I := Prf_Tbl.NEXT(I);
911 END LOOP;
912 -- get new position
913 l_new_prf_position := Prf_Tbl.count + 1;
914 Prf_Tbl(l_concat_segment).position := l_new_prf_position;
915 -- now add new record to cache
916 Prf_Tbl(l_concat_segment).prf_value := p_profile_option_value;
917
918 IF l_debug_level > 0 THEN
919 oe_debug_pub.add('added the new record at position: ' || Prf_Tbl(l_concat_segment).position);
920 oe_debug_pub.add('now there are ' || Prf_Tbl.count || ' records in the cache after adding the new record');
921 END IF;
922
923 END IF; -- end of Prf_Tbl.count < MAX_PROFILE_CACHE_SIZE
924
925 IF l_debug_level > 0 THEN
926 oe_debug_pub.add('EXITING OE_PROFILE.PUT_CACHED_PROFILE_FOR_CONTEXT');
927 END IF;
928
929 EXCEPTION
930 WHEN OTHERS THEN
931 IF l_debug_level > 0 THEN
932 oe_debug_pub.add('OTHERS IN OE_PROFILE.PUT_CACHED_PROFILE_FOR_CONTEXT: ' || SQLERRM);
933 END IF;
934 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
935 THEN
936 OE_MSG_PUB.Add_Exc_Msg
937 ( G_PKG_NAME
938 , 'Put_Cached_Profile_For_Context'
939 );
940 END IF;
941 END PUT_CACHED_PROFILE_FOR_CONTEXT;
942
943
944
945 END OE_PROFILE;
[Home] [Help]