[Home] [Help]
PACKAGE BODY: APPS.CSF_PARAMS_PKG
Source
1 PACKAGE BODY csf_params_pkg AS
2 /* $Header: CSFCPARB.pls 115.15.11510.2 2004/06/24 04:41:01 srengana ship $ */
3
4 FUNCTION query_parameter
5 ( i_name varchar2
6 , i_default_value number
7 ) return number
8 IS
9 /******************************************************
10 Created by: T. Voerman, Oracle Nederland
11
12 Date created:
13
14 Purpose: get numeric parametervalue.
15
16 Known Limitations:
17
18 Remarks:
19 ********************************************************/
20
21 r_read_parameter c_read_parameter%rowtype;
22 t_return_value number(34);
23
24 BEGIN
25 open c_read_parameter(i_name);
26 fetch c_read_parameter into r_read_parameter;
27 if c_read_parameter%found
28 then
29 t_return_value := To_Number(r_read_parameter.value);
30 else
31 t_return_value := i_default_value;
32 end if;
33 close c_read_parameter;
34 return t_return_value;
35 END query_parameter;
36
37 FUNCTION query_parameter
38 ( i_name varchar2
39 , i_default_value varchar2
40 ) return varchar2
41 IS
42 /******************************************************
43 Created by: T. Voerman, Oracle Nederland
44
45 Date created:
46
47 Purpose: get character parametervalue.
48
49 Known Limitations:
50
51 Remarks:
52 ********************************************************/
53
54 r_read_parameter c_read_parameter%rowtype;
55 t_return_value varchar2(2000);
56
57 BEGIN
58 open c_read_parameter(i_name);
59 fetch c_read_parameter into r_read_parameter;
60 if c_read_parameter%found
61 then
62 t_return_value := r_read_parameter.value;
63 else
64 t_return_value := i_default_value;
65 end if;
66 close c_read_parameter;
67 return t_return_value;
68 END query_parameter;
69
70 PROCEDURE query_parameters ( io_param in out nocopy paramtab )
71 IS
72 /******************************************************
73 Created by: T. Voerman, Oracle Nederland
74
75 Date created:
76
77 Purpose: get all parametervalues and store them in PL/SQL table.
78
79 Known Limitations:
80
81 Remarks:
82 ********************************************************/
83
84 cursor c_uom ( b_code varchar2 )
85 is
86 select uom_class
87 , unit_of_measure
88 , uom_code
89 , base_uom_flag
90 , unit_of_measure_tl
91 , language
92 from mtl_units_of_measure_vl
93 where Upper(uom_class) = 'LENGTH'
94 and uom_code = b_code;
95
96 -- The distinct-clause is important! The mtl_system_items
97 -- table has a PK of the inventory_item_id and the organization_id
98 -- combined. As the design decision has been to ignore the latter
99 -- any particular inventory_item_id can exist more than once in the table.
100 -- Show all the existing inventory_item_id's.
101 cursor c_agenda_on_duty_item_id ( b_inventory_item_id number )
102 is
103 select distinct inventory_item_id
104 , concatenated_segments
105 from mtl_system_items_vl
106 where inventory_item_id = b_inventory_item_id;
107
108 cursor c_agenda_trip_blg_type_id ( b_txn_billing_type_id number )
109 is
110 select ctt.name
111 from cs_transaction_types ctt
112 , cs_txn_billing_types ctbt
113 where ctt.transaction_type_id = ctbt.transaction_type_id
114 and ctbt.txn_billing_type_id = b_txn_billing_type_id;
115
116 r_uom c_uom%rowtype;
117 r_agenda_on_duty_item_id c_agenda_on_duty_item_id%rowtype;
118 r_agenda_trip_blg_type_id c_agenda_trip_blg_type_id%rowtype;
119 t_agenda_usemileage number(2);
120 t_userdefbuttons varchar2(2000);
121 t_udb_start number(4);
122 t_udb_stop number(4);
123 t_sep varchar2(1) := fnd_global.local_chr(2);
124
125 BEGIN
126 -- If the PL/SQL table doesn't exist, create it with a single record.
127 -- This record can then be filled with the parametervalues.
128 if io_param.count < 1
129 then
130 io_param(1) := null;
131 end if;
132
133 io_param(1).primary_key := 1;
134
135 -- Parameters on tab General
136
137 io_param(1).agenda_progressclock
138 := query_parameter('agenda_progressclock',1);
139
140 -- Parameters on tab Agenda
141
142 io_param(1).agenda_forceworkform
143 := query_parameter('agenda_forceworkform',0);
144 io_param(1).agenda_accompletedtask
145 := query_parameter('agenda_allowchangescompletedtask',1);
146 io_param(1).csf_m_agenda_accompletedtask
147 := query_parameter('csf_m_agenda_allowchangescompletedtask',1);
148 io_param(1).agenda_allowchangesinpast
149 := query_parameter('agenda_allowchangesinpast',0);
150 io_param(1).agenda_dayslookback
151 := query_parameter('agenda_dayslookback',1);
152 io_param(1).agenda_roundedofftime
153 := SubStr( query_parameter('agenda_roundedofftime','0:05:00')
154 , 3
155 , 2
156 );
157 io_param(1).agenda_refreshwaittime
158 := query_parameter('agenda_refreshwaittime',60);
159
160 -- Parameters on tab Items
161
162 t_agenda_usemileage
163 := query_parameter('agenda_usemileage',0);
164 -- bit 0
165 io_param(1).agenda_usemileagestartofday
166 := mod(t_agenda_usemileage,2);
167 -- bit 1
168 if mod(t_agenda_usemileage,4) >= 2
169 then
170 io_param(1).agenda_usemileagefinishofday := 1;
171 else
172 io_param(1).agenda_usemileagefinishofday := 0;
173 end if;
174 -- bit 2
175 if mod(t_agenda_usemileage,8) >= 4
176 then
177 io_param(1).agenda_usemileagestarttask := 1;
178 else
179 io_param(1).agenda_usemileagestarttask := 0;
180 end if;
181 -- bit 3
182 if mod(t_agenda_usemileage,16) >= 8
183 then
184 io_param(1).agenda_usemileagefinishtask := 1;
185 else
186 io_param(1).agenda_usemileagefinishtask := 0;
187 end if;
188
189 io_param(1).agenda_mileageuom
190 := query_parameter('agenda_mileageuom','-');
191
192 /* For this parameter it's necessary to return the description instead of
193 the value */
194 open c_uom(io_param(1).agenda_mileageuom);
195 fetch c_uom into r_uom;
196 if c_uom%found
197 then
198 io_param(1).agenda_unit_of_measure_tl
199 := r_uom.unit_of_measure_tl;
200 else
201 /* UOM description cannot be found. Return parameter-value. Form
202 will have to handle this invalid situation */
203 io_param(1).agenda_unit_of_measure_tl
204 := io_param(1).agenda_mileageuom;
205 end if;
206 close c_uom;
207
208 io_param(1).agenda_on_duty_item_id
209 := query_parameter('agenda_on_duty_item_id',0);
210 io_param(1).csf_m_agenda_on_duty_item_id
211 := query_parameter('csf_m_agenda_on_duty_item_id',0);
212
213 /* For these parameters it's necessary to return the description instead of
214 the value. Note that the two parameters are presented as a single parameter.
215 Therefore the description of only of of them needs to be retrieved.
216 The reason for having two parameters is a requirement for palm to have a
217 different parametername than laptop. */
218 open c_agenda_on_duty_item_id
219 ( io_param(1).agenda_on_duty_item_id
220 );
221 fetch c_agenda_on_duty_item_id into r_agenda_on_duty_item_id;
222 if c_agenda_on_duty_item_id%found
223 then
224 io_param(1).agenda_inventory_item_name_tl
225 := r_agenda_on_duty_item_id.concatenated_segments;
226 else
227 /* UOM translation cannot be found. Return '-'. Form
228 will have to handle this invalid situation */
229 io_param(1).agenda_inventory_item_name_tl := '-';
230 end if;
231 close c_agenda_on_duty_item_id;
232
233 io_param(1).agenda_trip_blg_type_id
234 := query_parameter('agenda_trip_blg_type_id',0);
235 io_param(1).csf_m_agenda_trip_blg_type_id
236 := query_parameter('csf_m_agenda_trip_blg_type_id',0);
237 /* Add by A. Soykan
238 implement business_process_id */
239 io_param(1).agenda_trip_process_id
240 := query_parameter('agenda_trip_process_id', 0);
241 io_param(1).csf_m_agenda_trip_process_id
242 := query_parameter('csf_m_agenda_trip_process_id', 0);
243
244 /* For these parameters it's necessary to return the description instead of
245 the value. Note that the two parameters are presented as a single parameter.
246 Therefore the description of only of of them needs to be retrieved.
247 The reason for having two parameters is a requirement from palm to have a
248 different parametername than laptop. */
249 open c_agenda_trip_blg_type_id
250 ( io_param(1).agenda_trip_blg_type_id
251 );
252 fetch c_agenda_trip_blg_type_id into r_agenda_trip_blg_type_id;
253 if c_agenda_trip_blg_type_id%found
254 then
255 io_param(1).agenda_trip_blg_type_name
256 := r_agenda_trip_blg_type_id.name;
257 else
258 /* Transaction type name cannot be found. Return '-'. Form
259 will have to handle this invalid situation */
260 io_param(1).agenda_trip_blg_type_name := '-';
261 end if;
262 close c_agenda_trip_blg_type_id;
263
264 -- Parameters on tab parts.
265
266 io_param(1).parts_allowstocklevelbelowzero
267 := query_parameter('parts_allowstocklevelbelowzero',0);
268 io_param(1).parts_showtime
269 := query_parameter('parts_showtime',0);
270 io_param(1).parts_editserialnumber
271 := query_parameter('parts_editserialnumber',0);
272
273 -- Parameters on tab SR Explorer
274
275 io_param(1).soexp_addsoh_remote
276 := query_parameter('soexp_addsoh_remote',0);
277 io_param(1).soexp_editsoh_remote
278 := query_parameter('soexp_editsoh_remote',0);
279 io_param(1).soexp_addsoa_remote
280 := query_parameter('soexp_addsoa_remote',0);
281 io_param(1).soexp_editsoa_remote
282 := query_parameter('soexp_editsoa_remote',0);
283 io_param(1).soexp_standardtaskduration
284 := query_parameter('soexp_standardtaskduration','1:00:00');
285
286 -- Parameters on tab Mail
287
288 io_param(1).recipients_boundary
289 := query_parameter('recipients_boundary',0);
290 io_param(1).csf_m_recipients_boundary
291 := query_parameter('csf_m_recipients_boundary',0);
292 io_param(1).mail_engbeepunreadmail
293 := query_parameter('mail_engbeepunreadmail',1);
294
295 -- Parameters on tab Buttons
296
297 t_userdefbuttons
298 := query_parameter('userdefbuttons', t_sep
299 || t_sep
300 || t_sep
301 || t_sep
302 || t_sep
303 || t_sep
304 || t_sep );
305 -- More than once the seperator will be found at the starting
306 -- search position. That is not an error. SubStr handles it.
307 t_udb_start := 1;
308 t_udb_stop := InStr( t_userdefbuttons, t_sep, t_udb_start);
309 io_param(1).userdefbutton1 := SubStr( t_userdefbuttons
310 , t_udb_start
311 , t_udb_stop - t_udb_start
312 );
313 t_udb_start := t_udb_stop+1;
314 t_udb_stop := InStr( t_userdefbuttons, t_sep, t_udb_start);
315 io_param(1).userdefbutton2 := SubStr( t_userdefbuttons
316 , t_udb_start
317 , t_udb_stop - t_udb_start
318 );
319 t_udb_start := t_udb_stop+1;
320 t_udb_stop := InStr( t_userdefbuttons, t_sep, t_udb_start);
321 io_param(1).userdefbutton3 := SubStr( t_userdefbuttons
322 , t_udb_start
323 , t_udb_stop - t_udb_start
324 );
325 t_udb_start := t_udb_stop+1;
326 t_udb_stop := InStr( t_userdefbuttons, t_sep, t_udb_start);
327 io_param(1).userdefbutton4 := SubStr( t_userdefbuttons
328 , t_udb_start
329 , t_udb_stop - t_udb_start
330 );
331 t_udb_start := t_udb_stop+1;
332 t_udb_stop := InStr( t_userdefbuttons, t_sep, t_udb_start);
333 io_param(1).userdefbutton5 := SubStr( t_userdefbuttons
334 , t_udb_start
335 , t_udb_stop - t_udb_start
336 );
337 t_udb_start := t_udb_stop+1;
338 t_udb_stop := InStr( t_userdefbuttons, t_sep, t_udb_start);
339 io_param(1).userdefbutton6 := SubStr( t_userdefbuttons
340 , t_udb_start
341 , t_udb_stop - t_udb_start
342 );
343 t_udb_start := t_udb_stop+1;
344 t_udb_stop := InStr( t_userdefbuttons, t_sep, t_udb_start);
345 io_param(1).userdefbutton7 := SubStr( t_userdefbuttons
346 , t_udb_start
347 , t_udb_stop - t_udb_start
348 );
349 END query_parameters;
350
351 PROCEDURE update_parameters ( io_param in out nocopy paramtab )
352 IS
353 /******************************************************
354 Created by: T. Voerman, Oracle Nederland
355
356 Date created:
357
358 Purpose: update parametervalues
359
360 Known Limitations:
361
362 Remarks: uses private procedures insert_parameter, update parameter,
363 save_parameter_value
364 ********************************************************/
365
366 t_csm_onlytodispatchers number(1);
367 t_csm_onlytootherengingroup number(1);
368 t_csm_outsideTinoway number(1);
369 t_sep varchar2(1) := fnd_global.local_chr(2);
370
371 procedure insert_parameter
372 ( i_name varchar2
373 , i_value varchar2 )
374 is
375 /******************************************************
376 Created by: T. Voerman, Oracle Nederland
377
378 Date created:
379
380 Purpose: insert parameter in csf_params
381
382 Known Limitations:
383
384 Remarks:
385 ********************************************************/
386
387 begin
388 insert into csf_params
389 ( PARAM_ID
390 , LAST_UPDATE_DATE
391 , LAST_UPDATED_BY
392 , CREATION_DATE
393 , CREATED_BY
394 , LAST_UPDATE_LOGIN
395 , NAME
396 , VALUE
397 )
398 values( csf_params_s.nextval
399 , sysdate
400 , uid
401 , sysdate
402 , uid
403 , 0
404 , Upper(i_name)
405 , i_value
406 );
407 end insert_parameter;
408
409 procedure update_parameter
410 ( i_name varchar2
411 , i_value varchar2 )
412 is
413 /******************************************************
414 Created by: T. Voerman, Oracle Nederland
415
416 Date created:
417
418 Purpose: update parameter in csf_params
419
420 Known Limitations:
421
422 Remarks:
423 ********************************************************/
424
425 begin
426 update csf_params par
427 set par.value = i_value
428 , last_updated_by = uid
429 , last_update_date = sysdate
430 where Upper(par.name) = Upper(i_name);
431 end update_parameter;
432
433 procedure save_parameter_value
434 ( i_name varchar2
435 , i_value number )
436 is
437 /******************************************************
438 Created by: T. Voerman, Oracle Nederland
439
440 Date created:
441
442 Purpose: check whether a numeric parameter already exists.
443 If so: update the parameter value.
444 If not: create the parameter together with it's value.
445
446 Known Limitations:
447
448 Remarks:
449 ********************************************************/
450
451 r_read_parameter c_read_parameter%rowtype;
452 t_found boolean;
453
454 begin
455 open c_read_parameter(i_name);
456 fetch c_read_parameter into r_read_parameter;
457 t_found := c_read_parameter%found;
458 close c_read_parameter;
459 if t_found
460 then
461 update_parameter( i_name
462 , To_Char(i_value)
463 );
464 else
465 insert_parameter( i_name
466 , To_Char(i_value)
467 );
468 end if;
469 end save_parameter_value;
470
471 procedure save_parameter_value
472 ( i_name varchar2
473 , i_value varchar2 )
474 is
475 /******************************************************
476 Created by: T. Voerman, Oracle Nederland
477
478 Date created:
479
480 Purpose: check whether a character parameter already exists.
481 If so: update the parameter value.
482 If not: create the parameter together with it's value.
483
484 Known Limitations:
485
486 Remarks:
487 ********************************************************/
488
489 r_read_parameter c_read_parameter%rowtype;
490 t_found boolean;
491
492 begin
493 open c_read_parameter(i_name);
494 fetch c_read_parameter into r_read_parameter;
495 t_found := c_read_parameter%found;
496 close c_read_parameter;
497 if t_found
498 then
499 update_parameter( i_name
500 , i_value
501 );
502 else
503 insert_parameter( i_name
504 , i_value
505 );
506 end if;
507 end save_parameter_value;
508
509 BEGIN
510
511 -- Parameters on tab General
512
513 save_parameter_value( 'agenda_progressclock'
514 , io_param(1).agenda_progressclock
515 );
516
517 -- Parameters on tab Agenda
518
519 save_parameter_value( 'agenda_forceworkform'
520 , io_param(1).agenda_forceworkform
521 );
522 save_parameter_value( 'agenda_allowchangescompletedtask'
523 , io_param(1).agenda_accompletedtask
524 );
525 save_parameter_value( 'csf_m_agenda_allowchangescompletedtask'
526 , io_param(1).csf_m_agenda_accompletedtask
527 );
528 save_parameter_value( 'agenda_allowchangesinpast'
529 , io_param(1).agenda_allowchangesinpast
530 );
531 save_parameter_value( 'agenda_dayslookback'
532 , io_param(1).agenda_dayslookback
533 );
534 save_parameter_value( 'agenda_roundedofftime'
535 , '0:'
536 || LTrim( To_Char
537 ( io_param(1).agenda_roundedofftime
538 , '09'
539 )
540 )
541 || ':00'
542 );
543 save_parameter_value( 'agenda_refreshwaittime'
544 , io_param(1).agenda_refreshwaittime
545 );
546 save_parameter_value( 'agenda_trip_blg_type_id'
547 , io_param(1).agenda_trip_blg_type_id
548 );
549 save_parameter_value( 'csf_m_agenda_trip_blg_type_id'
550 , io_param(1).csf_m_agenda_trip_blg_type_id
551 );
552 /* Add by A. Soykan
553 implement business_process_id */
554 save_parameter_value( 'agenda_trip_process_id'
555 , io_param(1).agenda_trip_process_id
556 );
557 save_parameter_value( 'csf_m_agenda_trip_process_id'
558 , io_param(1).csf_m_agenda_trip_process_id
559 );
560
561 -- Parameters on tab Items
562
563 save_parameter_value( 'agenda_usemileage'
564 , ( io_param(1).agenda_usemileagestartofday )
565 + (2 * io_param(1).agenda_usemileagefinishofday)
566 + (4 * io_param(1).agenda_usemileagestarttask )
567 + (8 * io_param(1).agenda_usemileagefinishtask )
568 );
569 save_parameter_value( 'agenda_mileageuom'
570 , io_param(1).agenda_mileageuom
571 );
572 save_parameter_value( 'agenda_on_duty_item_id'
573 , io_param(1).agenda_on_duty_item_id
574 );
575 save_parameter_value( 'csf_m_agenda_on_duty_item_id'
576 , io_param(1).csf_m_agenda_on_duty_item_id
577 );
578 save_parameter_value( 'parts_allowstocklevelbelowzero'
579 , io_param(1).parts_allowstocklevelbelowzero
580 );
581 save_parameter_value( 'parts_showtime'
582 , io_param(1).parts_showtime
583 );
584 save_parameter_value( 'parts_editserialnumber'
585 , io_param(1).parts_editserialnumber
586 );
587
588 -- Parameters on tab SO Explorer
589
590 save_parameter_value( 'soexp_addsoh_remote'
591 , io_param(1).soexp_addsoh_remote
592 );
593 save_parameter_value( 'soexp_editsoh_remote'
594 , io_param(1).soexp_editsoh_remote
595 );
596 save_parameter_value( 'soexp_addsoa_remote'
597 , io_param(1).soexp_addsoa_remote
598 );
599 save_parameter_value( 'soexp_editsoa_remote'
600 , io_param(1).soexp_editsoa_remote
601 );
602 save_parameter_value( 'soexp_standardtaskduration'
603 , io_param(1).soexp_standardtaskduration
604 );
605
606 -- Parameters on tab Mail
607
608 save_parameter_value( 'recipients_boundary'
609 , io_param(1).recipients_boundary
610 );
611 save_parameter_value( 'csf_m_recipients_boundary'
612 , io_param(1).csf_m_recipients_boundary
613 );
614 save_parameter_value( 'mail_engbeepunreadmail'
615 , io_param(1).mail_engbeepunreadmail
616 );
617
618 -- It is possible that one or more buttons have no text.
619 save_parameter_value( 'userdefbuttons'
620 , io_param(1).userdefbutton1 || t_sep
621 || io_param(1).userdefbutton2 || t_sep
622 || io_param(1).userdefbutton3 || t_sep
623 || io_param(1).userdefbutton4 || t_sep
624 || io_param(1).userdefbutton5 || t_sep
625 || io_param(1).userdefbutton6 || t_sep
626 || io_param(1).userdefbutton7 || t_sep
627 );
628 END update_parameters;
629
630 PROCEDURE lock_parameters ( io_param in out nocopy paramtab )
631 IS
632 /******************************************************
633 Created by: T. Voerman, Oracle Nederland
634
635 Date created:
636
637 Purpose: exclusively lock table csf_params.
638
639 Known Limitations:
640
641 Remarks:
642 ********************************************************/
643
644 BEGIN
645 lock table csf_params
646 in exclusive mode
647 nowait;
648 END lock_parameters;
649
650 BEGIN
651 io_param(1).primary_key := 1;
652 END csf_params_PKG;