[Home] [Help]
PACKAGE BODY: APPS.OE_SYS_PARAMETERS_PVT
Source
1 PACKAGE BODY OE_Sys_Parameters_pvt AS
2 /* $Header: OEXVSPMB.pls 120.5 2005/10/23 21:15:36 jisingh noship $ */
3
4
5 --Global variables to cache the Generic parameter information.
6
7 /* MOAC_SQL_CHANGE */
8 type VAL_TAB_TYPE is table of varchar2(240) index by VARCHAR2(90);
9 /*
10 ** define the internal table that will cache the parameter values
11 ** val_tab(x) is associated with name_tab(x)
12 */
13 VAL_TAB VAL_TAB_TYPE; /* the table of values */
14
15 g_org_id NUMBER:= 0;
16
17
18 g_freight_rating_flag VARCHAR2(1);
19 g_ship_method_flag VARCHAR2(1);
20
21 -- Global variables to cache the Scheduling parameter information
22 g_lad_flag VARCHAR2(1);
23 g_request_date_flag VARCHAR2(1);
24 g_shipping_method_flag VARCHAR2(1);
25 g_promise_date_flag VARCHAR2(2);
26 g_partial_reservation_flag VARCHAR2(1);
27
28 --retro{
29 -- Global variables to cache the Retrobilling parameter information
30 g_enable_retrobilling VARCHAR2(1);
31 --retro}
32
33 -- Global variables to cache the Approval parameter information
34 g_no_response_from_approver VARCHAR2(30);
35 --recurring charges
36 g_recurring_charges VARCHAR2(1);
37
38 -- Forward declaration --
39
40 /* R12.MOAC */
41 FUNCTION find (p_parameter_code IN VARCHAR2,
42 p_org_id IN NUMBER) RETURN VARCHAR2;
43
44 -- Start of comments
45 -- API name : Get_Value_From_Db
46 -- Type : Private
47 -- Description : This function will get the parameter value of a system parameter
48 -- from oe_sys_parameters_all
49 -- Parameters :
50 -- IN : p_parameter_Code IN VARCHAR2 Required
51 --
52 -- P_org_id IN NUMBER
53 --
54 -- End of Comments
55 FUNCTION Get_Value_From_Db
56 (p_parameter_Code IN VARCHAR2,
57 p_org_id IN NUMBER DEFAULT NULL)
58 RETURN VARCHAR2
59 IS
60 /* MOAC_SQL_CHANGE */
61 CURSOR parameter_value IS
62 SELECT parameter_value
63 FROM oe_sys_parameters_all
64 WHERE parameter_code = p_parameter_code
65 AND org_id = p_org_id;
66 /*
67 AND NVL(ORG_ID,NVL(TO_NUMBER(DECODE(SUBSTRB
68 (USERENV ('CLIENT_INFO'),1 ,1),' ', NULL,
69 SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)) =
70 NVL(p_org_id, NVL(TO_NUMBER(DECODE(SUBSTRB
71 (USERENV('CLIENT_INFO'),1,1),' ', NULL,
72 SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99));
73 */
74 l_parameter_val VARCHAR2(240);
75
76
77 --
78 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
79 --
80 BEGIN
81 OPEN parameter_value;
82 FETCH parameter_value INTO l_parameter_val;
83 IF parameter_value%NOTFOUND THEN
84 CLOSE parameter_value;
85 RETURN NULL;
86 END IF;
87 CLOSE Parameter_value;
88 IF l_debug_level > 0 THEN
89 oe_debug_pub.add( 'VALUE FROM DB'||l_parameter_val , 1 ) ;
90 END IF;
91 RETURN l_parameter_val;
92
93 END Get_Value_From_Db;
94
95
96 -- Start of comments
97 -- API name : Value
98 -- Type : Private
99 -- Description : This function will return parameter value of a given system parameter
100 -- Will check from global variables, if not available will call api Get_Value_From_Db
101 -- Parameters :
102 -- IN : p_param_Code IN VARCHAR2 Required
103 --
104 -- P_org_id IN NUMBER
105 --
106 -- End of Comments
107
108 FUNCTION VALUE
109 (p_param_code IN VARCHAR2,
110 p_org_id IN NUMBER DEFAULT NULL)
111 RETURN VARCHAR2
112 IS
113 l_org_id NUMBER:= 0;
114 l_sob_id NUMBER := 0;
115 l_parameter_value VARCHAR2(240);
116 l_tot_count NUMBER;
117 l_chk_clt_info NUMBER;
118 l_param_code varchar2(60);
119 -- R12.MOAC
120 l_freight_rating_flag VARCHAR2(1) := NULL;
121 l_ship_method_flag VARCHAR2(1) := NULL;
122
123 /* -- MOAC_SQL_CHANGE
124 CURSOR organization_id IS
125 SELECT NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1), ' ',
126 NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)
127 FROM DUAL;
128 */
129 l_AR_Sys_Param_Rec AR_SYSTEM_PARAMETERS_ALL%ROWTYPE;
130 l_table_index BINARY_INTEGER;
131
132 --
133 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
134 --
135 BEGIN
136 IF l_debug_level > 0 THEN
137 oe_debug_pub.add( 'ENTERING OE_SYS_PARAMETERS_PVT.VALUE '||p_org_id , 1 ) ;
138 END IF;
139
140 IF p_org_id IS NULL THEN
141
142 /*
143 OPEN organization_id;
144 FETCH organization_id INTO l_org_id;
145 CLOSE organization_id;
146 */
147
148
149 /*As per the bug #3381576.
150 To derive the ORG_ID based on the PL/sQL block rather then SQL.
151 */
152
153 /* Start MOAC_SQL_CHANGE */
154 --l_chk_clt_info := to_number(rtrim(SUBSTRB(USERENV('CLIENT_INFO'),1,10),' '));
155 l_org_id := mo_global.get_current_org_id;
156 IF l_org_id IS NULL THEN
157 Fnd_Message.set_name('ONT','ONT_OU_REQUIRED_FOR_SYS_PARAM');
158 Oe_Msg_Pub.Add;
159 RAISE FND_API.G_EXC_ERROR;
160 END IF;
161
162 /*
163 if l_chk_clt_info is not null
164 then
165 l_org_id := l_chk_clt_info;
166 else
167 l_org_id := -99;
168 end if;
169 */
170 /* End MOAC_SQL_CHANGE */
171 ELSE
172 l_org_id := p_org_id;
173 END IF;
174 l_param_code := p_param_code;
175
176
177 IF l_param_code = 'SET_OF_BOOKS_ID' THEN
178
179 l_AR_Sys_Param_Rec := Get_AR_Sys_Params(l_org_id);
180
181 RETURN(TO_CHAR(l_AR_Sys_Param_Rec.SET_OF_BOOKS_ID));
182
183 --freight rating and carrier selection
184 ELSIF l_param_code = 'FTE_INTEGRATION' THEN
185
186 /* MOAC_SQL_CHANGE */
187 l_freight_rating_flag := nvl(find('FREIGHT_RATING_ENABLED_FLAG',l_org_id), 'N');
188 l_ship_method_flag := nvl(find('FTE_SHIP_METHOD_ENABLED_FLAG',l_org_id), 'N');
189
190 IF l_freight_rating_flag is not null
191 AND l_ship_method_flag is not null THEN
192 IF l_freight_rating_flag = 'Y'
193 AND l_ship_method_flag = 'Y' THEN
194 RETURN 'Y';
195 ELSIF l_freight_rating_flag = 'Y' THEN
196 RETURN 'F';
197 ELSIF l_ship_method_flag = 'Y' THEN
198 RETURN 'S';
199 ELSE
200 RETURN 'N';
201 END IF;
202 END IF;
203 /*
204 ELSE
205 l_parameter_value := get_value_from_db('FREIGHT_RATING_ENABLED_FLAG',l_org_id);
206 IF g_org_id <> l_org_id THEN
207 g_org_id := l_org_id;
208 END IF;
209 g_freight_rating_flag := l_parameter_value;
210 l_parameter_value := NULL;
211 l_parameter_value := get_value_from_db('FTE_SHIP_METHOD_ENABLED_FLAG',l_org_id);
212 g_ship_method_flag := l_parameter_value;
213
214 IF g_freight_rating_flag = 'Y' AND
215 g_ship_method_flag = 'Y' THEN
216 RETURN 'Y';
217 ELSIF g_freight_rating_flag = 'Y' THEN
218 RETURN 'F';
219 ELSIF g_ship_method_flag = 'Y' THEN
220 RETURN 'S';
221 ELSE
222 RETURN 'N';
223 END IF;
224
225 END IF;
226 */
227 ---------- Scheduling parameters---------------------------------
228 ELSIF l_param_code = 'LATEST_ACCEPTABLE_DATE_FLAG' THEN
229 -- R12.MOAC
230 l_parameter_value := NVL(find(l_param_code,l_org_id),'O');
231 RETURN(l_parameter_value);
232 /*
233 IF l_org_id = g_org_id AND g_lad_flag is not null THEN
234 RETURN(g_lad_flag);
235 ELSE
236 l_parameter_value := NVL(get_value_from_db('LATEST_ACCEPTABLE_DATE_FLAG',l_org_id),'O');
237 g_org_id := l_org_id;
238 g_lad_flag := l_parameter_value;
239 RETURN(g_lad_flag);
240 END IF;
241 */
242 ELSIF l_param_code = 'RESCHEDULE_REQUEST_DATE_FLAG' THEN
243 -- R12.MOAC
244 l_parameter_value := NVL(find(l_param_code,l_org_id),'Y');
245 RETURN(l_parameter_value);
246 /*
247 IF l_org_id = g_org_id AND g_request_date_flag is not null THEN
248 RETURN(g_request_date_flag);
249 ELSE
250 l_parameter_value := NVL(get_value_from_db('RESCHEDULE_REQUEST_DATE_FLAG',l_org_id),'Y');
251 g_org_id := l_org_id;
252 g_request_date_flag := l_parameter_value;
253 RETURN(g_request_date_flag);
254 END IF;
255 */
256 ELSIF l_param_code = 'RESCHEDULE_SHIP_METHOD_FLAG' THEN
257 -- R12.MOAC
258 l_parameter_value := NVL(find(l_param_code,l_org_id),'Y');
259 RETURN(l_parameter_value);
260 /*
261 IF l_org_id = g_org_id AND g_shipping_method_flag is not null THEN
262 RETURN(g_shipping_method_flag);
263 ELSE
264 l_parameter_value := NVL(get_value_from_db('RESCHEDULE_SHIP_METHOD_FLAG',l_org_id),'Y');
265 g_org_id := l_org_id;
266 g_shipping_method_flag := l_parameter_value;
267 RETURN(g_shipping_method_flag);
268 END IF;
269 */
270 ELSIF l_param_code = 'PROMISE_DATE_FLAG' THEN
271 -- R12.MOAC
272 l_parameter_value := NVL(find(l_param_code,l_org_id),'M');
273 RETURN(l_parameter_value);
274 /*
275 IF l_org_id = g_org_id AND g_promise_date_flag is not null THEN
276 RETURN(g_promise_date_flag);
277 ELSE
278 l_parameter_value := NVL(get_value_from_db('PROMISE_DATE_FLAG',l_org_id),'M');
279 g_org_id := l_org_id;
280 g_promise_date_flag := l_parameter_value;
281 RETURN(g_promise_date_flag);
282 END IF;
283 */
284 ELSIF l_param_code = 'PARTIAL_RESERVATION_FLAG' THEN
285 -- R12.MOAC
286 l_parameter_value := NVL(find(l_param_code,l_org_id),'N');
287 RETURN(l_parameter_value);
288 /*
289 IF l_org_id = g_org_id AND g_partial_reservation_flag is not null THEN
290 RETURN(g_partial_reservation_flag);
291 ELSE
292 l_parameter_value := NVL(get_value_from_db('PARTIAL_RESERVATION_FLAG',l_org_id),'N');
293 g_org_id := l_org_id;
294 g_partial_reservation_flag := l_parameter_value;
295 RETURN(g_partial_reservation_flag);
296 END IF;
297 */
298 --recurring charges parameters --------------------------------------
299 ELSIF l_param_code = 'RECURRING_CHARGES' THEN
300 oe_debug_pub.add('Inside RCAPPS');
301 -- R12.MOAC
302 l_parameter_value := NVL(find(l_param_code,l_org_id),'N');
303 RETURN(l_parameter_value);
304 /*
305 IF l_org_id = g_org_id AND g_recurring_charges IS NOT NULL THEN
306 oe_debug_pub.add('Recur Enabled1:'||g_recurring_charges);
307 RETURN (g_recurring_charges);
308 ELSE
309 l_parameter_value:=NVL(Get_Value_From_DB('RECURRING_CHARGES',l_org_id),'N');
310 g_org_id := l_org_id;
311 g_recurring_charges := l_parameter_value;
312 oe_debug_pub.add('Recur Enabled2:'||g_recurring_charges);
313 RETURN (g_recurring_charges);
314 END IF;
315 */
316 -- recurring charges paramaters END ---------------------------------
317
318 --retro{Retrobilling parameters
319 ELSIF l_param_code = 'ENABLE_RETROBILLING' THEN
320 -- R12.MOAC
321 l_parameter_value := NVL(find(l_param_code,l_org_id),'N');
322 RETURN(l_parameter_value);
323 /*
324 IF l_org_id = g_org_id AND g_enable_retrobilling is not null THEN
325 RETURN(g_enable_retrobilling);
326 ELSE
327 l_parameter_value := NVL(get_value_from_db('ENABLE_RETROBILLING',l_org_id),'N');
328 g_org_id := l_org_id;
329 g_enable_retrobilling := l_parameter_value;
330 RETURN(g_enable_retrobilling);
331 END IF;
332 */
333 ELSIF l_param_code = 'NO_RESPONSE_FROM_APPROVER' THEN
334 -- R12.MOAC
335 l_parameter_value := NVL(find(l_param_code,l_org_id),'N');
336 RETURN(l_parameter_value);
337 /*
338 IF l_org_id = g_org_id AND g_no_response_from_approver is not null THEN
339 RETURN(g_no_response_from_approver);
340 ELSE
341 l_parameter_value := NVL(get_value_from_db('NO_RESPONSE_FROM_APPROVER',l_org_id),'N');
342 g_org_id := l_org_id;
343 g_no_response_from_approver := l_parameter_value;
344 RETURN(g_no_response_from_approver);
345 END IF;
346 */
347 ELSE -- For all parameters having no extra processing
348 l_parameter_value := find(l_param_code,l_org_id);
349
350 /*
351 IF l_org_id = g_org_id THEN
352 l_table_index := find(l_param_code);
353 IF l_table_index < TABLE_SIZE THEN
354 IF l_debug_level > 0 THEN
355 oe_debug_pub.add( ' 1 Parameter Value: '||Val_Tab(l_table_index),1);
356 END IF;
357 RETURN (Val_Tab(l_table_index));
358 ELSE
359 put(l_param_code,l_org_id,l_parameter_value);
360 IF l_debug_level > 0 THEN
361 oe_debug_pub.add( '2 PARAMETER VALUE: '||l_parameter_value,1);
362 END IF;
363 RETURN (l_parameter_value);
364 END IF;
365 ELSE -- org id changed
366 put(l_param_code,l_org_id,l_parameter_value);
367 g_org_id := l_org_id;
368 IF l_debug_level > 0 THEN
369 oe_debug_pub.add( '3 PARAMETER VALUE: '||l_parameter_value,1);
370 END IF;
371 RETURN (l_parameter_value);
372 END IF;
373 */
374 IF l_debug_level > 0 THEN
375 oe_debug_pub.add( ' PARAMETER VALUE: '||l_parameter_value,1);
376 END IF;
377 RETURN (l_parameter_value);
378 END IF;
379
380 IF l_debug_level > 0 THEN
381 oe_debug_pub.add( 'EXITING OE_SYS_PARAMETERS_PVT.VALUE' , 1 ) ;
382 END IF;
383 RETURN(NULL);
384
385 EXCEPTION
386 WHEN FND_API.G_EXC_ERROR THEN
387 IF l_debug_level > 0 THEN
388 oe_debug_pub.add( 'EXPECTED ERROR IN VALUE FUNCTION' ,1);
389 END IF;
390 RAISE FND_API.G_EXC_ERROR;
391
392 WHEN NO_DATA_FOUND THEN
393
394 RETURN(NULL);
395
396 WHEN OTHERS THEN
397
398 RETURN(NULL);
399
400 END VALUE;
401
402 -- Start of comments
403 -- API name : Find
404 -- Type : Private
405 -- Description : This function will get the index of a system parameter
406 -- from cache
407 -- Parameters :
408 -- IN : p_parameter_Code IN VARCHAR2 Required
409 -- IN : p_org_id IN NUMBER Required
410 --
411 --
412 -- End of Comments
413 FUNCTION find (p_parameter_code IN VARCHAR2,
414 p_org_id IN NUMBER)
415 RETURN VARCHAR2
416 IS
417 l_tab_index BINARY_INTEGER;
418 l_found BOOLEAN;
419 l_hash_value NUMBER;
420
421 --
422 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
423 --
424 BEGIN
425 /*R12.MOAC*/
426 IF val_tab.EXISTS(p_parameter_code||'ORG'||to_char(p_org_id)) THEN
427 IF l_debug_level > 0 THEN
428 oe_debug_pub.add( 'Parameter value exists. ' , 1 ) ;
429 END IF;
430 RETURN val_tab(p_parameter_code||'ORG'||to_char(p_org_id));
431 ELSE
432 val_tab(p_parameter_code||'ORG'||to_char(p_org_id)) :=
433 get_value_from_db(p_parameter_code,p_org_id);
434 IF l_debug_level > 0 THEN
435 oe_debug_pub.add( 'Parameter value fetched from table. ' , 1 ) ;
436 END IF;
437 RETURN val_tab(p_parameter_code||'ORG'||to_char(p_org_id));
438 END IF;
439 EXCEPTION
440 WHEN OTHERS THEN
441 RETURN NULL;
442 END find;
443
444
445 /* A global table of records for ar_system_parameters is maintained to get
446 all the information need from ar_system_parameters. Any procedure needing
447 information from ar_system_parameters should call this API to get this
448 information
449 */
450
451 FUNCTION Get_AR_Sys_Params
452 (p_org_id IN NUMBER DEFAULT NULL)
453 RETURN AR_SYSTEM_PARAMETERS_ALL%ROWTYPE
454 IS
455 l_AR_Sys_Param_Rec AR_SYSTEM_PARAMETERS_ALL%ROWTYPE;
456 l_org_id NUMBER;
457 l_chk_clt_info NUMBER;
458
459 --
460 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
461 --
462 BEGIN
463
464 IF l_debug_level > 0 THEN
465 oe_debug_pub.add( 'ENTERING OE_SYS_PARAMETERS_PVT.GET_AR_SYS_PARAMS ... ' , 1 ) ;
466 END IF;
467
468 IF p_org_id IS NULL THEN
469
470 IF l_debug_level > 0 THEN
471 oe_debug_pub.add( 'Org Id is null .. Querying for Org Id .. ' , 1 ) ;
472 END IF;
473
474 /*
475
476 SELECT NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1), ' ',
477 NULL, SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)
478 INTO l_org_id FROM DUAL;
479 */
480
481 /*As per the bug #3381576.
482 To derive the ORG_ID based on the PL/sQL block rather then SQL.
483 */
484 /* Start MOAC_SQL_CHANGE */
485 /*
486 l_chk_clt_info := to_number(rtrim(SUBSTRB(USERENV('CLIENT_INFO'),1,10),' '));
487 if l_chk_clt_info is not null
488 then
489 l_org_id := l_chk_clt_info;
490 else
491 l_org_id := -99;
492 end if;
493 */
494 l_org_id := mo_global.get_current_org_id;
495 IF l_org_id IS NULL THEN
496 Fnd_Message.set_name('ONT','ONT_OU_REQUIRED_FOR_SYS_PARAM');
497 Oe_Msg_Pub.Add;
498 RAISE FND_API.G_EXC_ERROR;
499 END IF;
500 /* End MOAC_SQL_CHANGE */
501 ELSE
502 l_org_id := p_org_id;
503 END IF;
504 IF G_AR_Sys_Param_Tbl.exists(l_org_id) THEN
505 IF l_debug_level > 0 THEN
506 oe_debug_pub.add( 'Getting the AR details from the cache .. ' , 1 ) ;
507 END IF;
508 RETURN G_AR_Sys_Param_Tbl(l_org_id);
509 ELSE
510 IF l_debug_level > 0 THEN
511 oe_debug_pub.add( 'Querying the table for the AR details .. ' , 1 ) ;
512 END IF;
513 /* MOAC_SQL_CHANGE */
514 SELECT *
515 INTO
516 l_AR_Sys_Param_Rec
517 FROM AR_SYSTEM_PARAMETERS_ALL
518 WHERE org_id = l_org_id;
519 /*
520 WHERE NVL(ORG_ID,NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1 ,1),' ',
521 NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)) =
522 NVL(l_org_id, NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ',
523 NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99));
524 */
525 G_AR_Sys_Param_Tbl(l_org_id) := l_AR_Sys_Param_Rec;
526 RETURN G_AR_Sys_Param_Tbl(l_org_id);
527 END IF;
528
529 EXCEPTION
530
531 WHEN NO_DATA_FOUND THEN
532 oe_debug_pub.add( 'In No Data Found Exception .. ' , 1 ) ;
533 RETURN(NULL);
534
535 WHEN OTHERS THEN
536 oe_debug_pub.add( 'In Others Exception .. ' , 1 ) ;
537 RETURN(NULL);
538
539 END Get_AR_Sys_Params;
540
541
542 END OE_Sys_Parameters_Pvt;