[Home] [Help]
PACKAGE BODY: APPS.FLM_KANBAN_CONFIG_PARAMS
Source
1 package body flm_kanban_config_params as
2 /* $Header: flmekprb.pls 120.6.12020000.2 2012/07/13 11:02:10 sisankar ship $ */
3
4 function get_preference_level_id(p_pref_id number, p_org_id number)
5 return number
6 IS
7 l_return varchar(1);
8 l_level_id number;
9 l_module_id number;
10 l_level_code number;
11 begin
12 /* get the module id from preference */
13 select fepd.module_id
14 into l_module_id
15 from FLM_EKB_PREFERENCE_definitions fepd
16 where fepd.preference_id = p_pref_id;
17
18 /* get the level code */
19 /* 0 - site, 1 - org*/
20 l_level_code := 0;
21 if( p_org_id is not null) then
22 l_level_code := 1;
23 end if;
24
25 /* find out the level_id */
26 select level_id
27 into l_level_id
28 from
29 (
30 select level_id
31 from FLM_EKB_preference_levels v
32 where
33 v.module_id = l_module_id and
34 v.level_code <= l_level_code and
35 ( v.organization_id is null or v.organization_id = p_org_id) and
36 exists
37 (
38 select 1
39 from flm_ekb_preference_values v1
40 where v1.preference_id = p_pref_id and
41 v1.level_id = v.level_id
42 )
43 order by v.level_code desc
44 )
45 where rownum = 1;
46
47 return l_level_id;
48 exception when others then
49 return null;
50 end;
51
52 function get_ekb_preference_value(p_level_id number, p_pref_id number, p_attr_name varchar)
53 return varchar
54 IS
55 l_attr_val_code varchar(100);
56 begin
57 if(p_attr_name is null) then
58 select attribute_value_code
59 into l_attr_val_code
60 from
61 (
62 select attribute_value_code
63 from FLM_EKB_PREFERENCE_VALUES
64 where (level_id = p_level_id or level_id = 1)
65 and preference_id = p_pref_id
66 order by level_id desc
67 )
68 where rownum = 1;
69 else
70 select attribute_value_code
71 into l_attr_val_code
72 from
73 (
74 select attribute_value_code
75 from FLM_EKB_PREFERENCE_VALUES
76 where (level_id = p_level_id or level_id = 1)
77 and preference_id = p_pref_id
78 and attribute_name = p_attr_name
79 order by level_id desc
80 )
81 where rownum = 1;
82 end if;
83 return l_attr_val_code;
84 EXCEPTION
85 when NO_DATA_FOUND then
86 return null;
87 end;
88
89 /*Start: Kanban Tolerance Full Status*/
90
91 function get_kanban_tol_fs_prod(p_org_id number)
92 return number
93 IS
94 l_preference_id number;
95 l_level_id number;
96 l_KBToleranceFSProd varchar(10);
97 l_number number;
98 begin
99 l_preference_id := FLM_KANBAN_CONSTANTS.KANBAN_TOL_FS;
100 l_level_id := get_preference_level_id(l_preference_id, p_org_id);
101 l_KBToleranceFSProd := get_ekb_preference_value(l_level_id, l_preference_id, '4');
102 l_number := to_number(l_KBToleranceFSProd);
103 return l_number;
104 end;
105
106 function get_kanban_tol_fs_inter(p_org_id number)
107 return number
108 IS
109 l_level_id number;
110 l_KBToleranceFSInter varchar(10);
111 l_preference_id number;
112 l_number number;
113 begin
114 l_preference_id := FLM_KANBAN_CONSTANTS.KANBAN_TOL_FS;
115 l_level_id := get_preference_level_id(l_preference_id, p_org_id);
116 l_KBToleranceFSInter := get_ekb_preference_value(l_level_id, l_preference_id, '1');
117 l_number := to_number(l_KBToleranceFSInter);
118 return l_number;
119 end;
120
121 function get_kanban_tol_fs_intra(p_org_id number)
122 return number
123 IS
124 l_level_id number;
125 l_KBToleranceFSIntra varchar(10);
126 l_preference_id number;
127 l_number number;
128 begin
129 l_preference_id := FLM_KANBAN_CONSTANTS.KANBAN_TOL_FS;
130 l_level_id := get_preference_level_id(l_preference_id, p_org_id);
131 l_KBToleranceFSIntra := get_ekb_preference_value(l_level_id, l_preference_id, '3');
132 l_number := to_number(l_KBToleranceFSIntra);
133 return l_number;
134 end;
135
136 function get_kanban_tol_fs_supplier(p_org_id number)
137 return number
138 IS
139 l_level_id number;
140 l_KBToleranceFSSupplier varchar(10);
141 l_preference_id number;
142 l_number number;
143 begin
144 l_preference_id := FLM_KANBAN_CONSTANTS.KANBAN_TOL_FS;
145 l_level_id := get_preference_level_id(l_preference_id, p_org_id);
146 l_KBToleranceFSSupplier := get_ekb_preference_value(l_level_id, l_preference_id, '2');
147 l_number := to_number(l_KBToleranceFSSupplier);
148 return l_number;
149 end;
150
151 /*End: Kanban Tolerance Full Status*/
152
153 function get_tol_kanban_transfer(p_org_id number)
154 return varchar
155 IS
156 l_level_id number;
157 l_tol_kanban_transfer varchar(10);
158 l_preference_id number;
159 begin
160 l_preference_id := FLM_KANBAN_CONSTANTS.TOLERANCE_KANBAN_TRANSFERS;
161 l_level_id := get_preference_level_id(l_preference_id, p_org_id);
162 l_tol_kanban_transfer := get_ekb_preference_value(l_level_id, l_preference_id, null);
163 return l_tol_kanban_transfer;
164 end;
165
166 function get_dj_status_replenish(p_org_id number)
167 return varchar
168 IS
169 l_level_id number;
170 l_StatusDJReplenishment varchar(10);
171 l_preference_id number;
172 begin
173 l_preference_id := FLM_KANBAN_CONSTANTS.DJ_STATUS_REPLENISHMENT;
174 l_level_id := get_preference_level_id(l_preference_id, p_org_id);
175 l_StatusDJReplenishment := get_ekb_preference_value(l_level_id, l_preference_id, null);
176 return l_StatusDJReplenishment;
177 end;
178
179 function get_source_staging_subinv(p_org_id number)
180 return varchar
181 IS
182 l_level_id number;
183 l_SrcSubInvAsStagSubInv varchar(10);
184 l_preference_id number;
185 begin
186 l_preference_id := FLM_KANBAN_CONSTANTS.SOURCE_STAGING_SUBINV;
187 l_level_id := get_preference_level_id(l_preference_id, p_org_id);
188 l_SrcSubInvAsStagSubInv := get_ekb_preference_value(l_level_id, l_preference_id, null);
189 return l_SrcSubInvAsStagSubInv;
190 end;
191
192 /*Start: Receiving Tolerance Config Parameters*/
193 function get_rcv_tol_prod(p_org_id number)
194 return number
195 IS
196 l_level_id number;
197 l_RcvTolerance_Prod varchar(10);
198 l_preference_id number;
199 l_number number;
200 begin
201 l_preference_id := FLM_KANBAN_CONSTANTS.KANBAN_TOL_FS;
202 l_level_id := get_preference_level_id(l_preference_id, p_org_id);
203 l_RcvTolerance_Prod := get_ekb_preference_value(l_level_id, l_preference_id, '4');
204 l_number := to_number(l_RcvTolerance_Prod);
205 return l_number;
206 end;
207
208 /*Demand Range Days*/
209 function get_demand_range_days(p_org_id number)
210 return number
211 IS
212 l_level_id number;
213 l_demand_range_days varchar(10);
214 l_preference_id number;
215 l_number number;
216 begin
217 l_preference_id := FLM_KANBAN_CONSTANTS.DEMAND_RANGE_DAYS;
218 l_level_id := get_preference_level_id(l_preference_id, p_org_id);
219 l_demand_range_days := get_ekb_preference_value(l_level_id, l_preference_id, null);
220 l_number := to_number(l_demand_range_days);
221 return l_number;
222 end;
223
224 function get_demand_tolerance(p_org_id number)
225 return number
226 IS
227 l_level_id number;
228 l_demand_tolerance varchar(10);
229 l_preference_id number;
230 l_number number;
231 begin
232 l_preference_id := FLM_KANBAN_CONSTANTS.DEMAND_TOLERANCE;
233 l_level_id := get_preference_level_id(l_preference_id, p_org_id);
234 l_demand_tolerance := get_ekb_preference_value(l_level_id, l_preference_id, null);
235 l_number := to_number(l_demand_tolerance);
236 return l_number;
237 end;
238
239 /*LeadTime Range Days*/
240 function get_lt_range_days(p_org_id number)
241 return number
242 IS
243 l_level_id number;
244 l_lt_range_days varchar(10);
245 l_preference_id number;
246 l_number number;
247 begin
248 l_preference_id := FLM_KANBAN_CONSTANTS.LEADTIME_RANGE_DAYS;
249 l_level_id := get_preference_level_id(l_preference_id, p_org_id);
250 l_lt_range_days := get_ekb_preference_value(l_level_id, l_preference_id, null);
251 l_number := to_number(l_lt_range_days);
252 return l_number;
253 end;
254
255 function get_lt_tolerance(p_org_id number)
256 return number
257 IS
258 l_level_id number;
259 l_lt_tolerance varchar(10);
260 l_preference_id number;
261 l_number number;
262 begin
263 l_preference_id := FLM_KANBAN_CONSTANTS.LEADTIME_TOLERANCE;
264 l_level_id := get_preference_level_id(l_preference_id, p_org_id);
265 l_lt_tolerance := get_ekb_preference_value(l_level_id, l_preference_id, null);
266 l_number := to_number(l_lt_tolerance);
267 return l_number;
268 end;
269
270 /*Start: Inventory Health*/
271 function get_good_inv_health_per(p_org_id number)
272 return number
273 IS
274 l_level_id number;
275 l_good_inv_health_per varchar(10);
276 l_preference_id number;
277 l_number number;
278 begin
279 l_preference_id := FLM_KANBAN_CONSTANTS.INV_HEALTH_PER_GOOD;
280 l_level_id := get_preference_level_id(l_preference_id, p_org_id);
281 l_good_inv_health_per := get_ekb_preference_value(l_level_id, l_preference_id, null);
282 l_number := to_number(l_good_inv_health_per);
283 return l_number;
284 end;
285
286 function get_bad_inv_health_per(p_org_id number)
287 return number
288 IS
289 l_level_id number;
290 l_bad_inv_health_per varchar(10);
291 l_preference_id number;
292 l_number number;
293 begin
294 l_preference_id := FLM_KANBAN_CONSTANTS.INV_HEALTH_PER_BAD;
295 l_level_id := get_preference_level_id(l_preference_id, p_org_id);
296 l_bad_inv_health_per := get_ekb_preference_value(l_level_id, l_preference_id, null);
297 l_number := to_number(l_bad_inv_health_per);
298 return l_number;
299 end;
300
301 function get_dist_deployment
302 return number
303 IS
304 l_level_id number;
305 l_dist_deployment varchar(10);
306 l_preference_id number;
307 l_number number;
308 begin
309 l_preference_id := FLM_KANBAN_CONSTANTS.DIST_DEPLOYMENT;
310 l_dist_deployment := get_ekb_preference_value(1, l_preference_id, null);
311 l_number := to_number(l_dist_deployment);
312 return l_number;
313 end;
314
315 /*Start: UnMoved Cards LeadTime*/
316 function get_unmovedcard_leadtime(p_org_id number)
317 return flm_unmovecard_table
318 IS
319 l_level_id number;
320 l_preference_id number;
321 l_level_id_site number;
322
323 cursor c_unmovedcard_leadtime(p_level_id number) IS
324 select attribute_name, attribute_value_code
325 from FLM_EKB_PREFERENCE_VALUES
326 where level_id = p_level_id
327 and preference_id = FLM_KANBAN_CONSTANTS.UNMOVED_CARD_LEADTIME_PER
328 and attribute_value_code is NOT NULL;
329
330 unmovecardtable flm_unmovecard_table;
331 /*This is API will return status and leadtime value combination.
332 If for any status value is populated at org level, then the statuses
333 with value at the org level will be returned. But if no status is
334 populated at the org level then status/leadtime combination will
335 be returned for ALL the statuses at the site level.
336
337 This API will not return combinations with valid status and NULL
338 leadtime value. Only those status will be returned which have a valid
339 leadtime. At UI level it will be made sure that if a particular status
340 is enabled, then it needs to have a leadtime value AND if is not-enabled
341 then NULL leadtime value*/
342 begin
343 l_preference_id := FLM_KANBAN_CONSTANTS.UNMOVED_CARD_LEADTIME_PER;
344 l_level_id_site := 1;
345 l_level_id := get_preference_level_id(l_preference_id, p_org_id);
346 open c_unmovedcard_leadtime(l_level_id);
347 FETCH c_unmovedcard_leadtime BULK COLLECT INTO unmovecardtable;
348 close c_unmovedcard_leadtime;
349 if(unmovecardtable.count = 0) then
350 open c_unmovedcard_leadtime(l_level_id_site);
351 FETCH c_unmovedcard_leadtime BULK COLLECT INTO unmovecardtable;
352 close c_unmovedcard_leadtime;
353 end if;
354 return unmovecardtable;
355 end;
356 /*End: UnMoved Cards LeadTime*/
357
358 function get_unmovedcard_enb_status(p_org_id number)
359 return flm_unmovecard_enb_status_t
360 IS
361 l_level_id number;
362 l_preference_id number;
363 l_level_id_site number;
364
365 cursor c_unmovedcard_enb_status(p_level_id number) IS
366 select attribute_name
367 from FLM_EKB_PREFERENCE_VALUES
368 where level_id = p_level_id
369 and preference_id = FLM_KANBAN_CONSTANTS.UNMOVED_CARD_LEADTIME_PER
370 and nvl(attribute_enable_flag,1) = 1;
371
372 unmovecardtable flm_unmovecard_enb_status_t;
373 /*Bug 12623839: This API will return statuses that are enabled
374 based on the column attribute_enable_flag.*/
375
376 begin
377 l_preference_id := FLM_KANBAN_CONSTANTS.UNMOVED_CARD_LEADTIME_PER;
378 l_level_id_site := 1;
379 l_level_id := get_preference_level_id(l_preference_id, p_org_id);
380 open c_unmovedcard_enb_status(l_level_id);
381 FETCH c_unmovedcard_enb_status BULK COLLECT INTO unmovecardtable;
382 close c_unmovedcard_enb_status;
383 if(unmovecardtable.count = 0) then
384 open c_unmovedcard_enb_status(l_level_id_site);
385 FETCH c_unmovedcard_enb_status BULK COLLECT INTO unmovecardtable;
386 close c_unmovedcard_enb_status;
387 end if;
388 return unmovecardtable;
389 end;
390
391 function get_preference_value_code
392 (p_preference_id number,
393 p_org_id number default null) return varchar2 is
394
395 l_level_code number := 0;
396 l_val_code varchar2(30);
397 l_return_val varchar2(240);
398 l_pref_type NUMBER;
399 l_multi_val_cnt NUMBER := 0;
400 l_multi_val_cnt_cp NUMBER := 0;
401
402 cursor pref_type(cl_pref_id NUMBER) is
403 select preference_type
404 from flm_ekb_preference_definitions
405 where preference_id = cl_pref_id;
406
407 cursor single_value (cl_org_id NUMBER) is
408 select decode( (select count(v.attribute_value_code)
409 from flm_ekb_preference_values v,
410 flm_ekb_preference_levels l
411 where v.PREFERENCE_ID = p_preference_id
412 and v.LEVEL_ID = l.LEVEL_ID
413 and nvl(l.organization_id, -99) = nvl(cl_org_id, -99)
414 ),
415 0, 'INHERIT',
416 1, (select v.attribute_value_code
417 from flm_ekb_preference_values v,
418 flm_ekb_preference_levels l
419 where v.PREFERENCE_ID = p_preference_id
420 and v.LEVEL_ID = l.LEVEL_ID
421 and nvl(l.organization_id, -99) = nvl(cl_org_id, -99)),
422 'INHERIT') AS single_value_code
423 from dual;
424
425 cursor multi_value (cl_org_id NUMBER) is
426 select count(v.attribute_value_code) as multi_value_count
427 from flm_ekb_preference_values v,
428 flm_ekb_preference_levels l
429 where v.PREFERENCE_ID = p_preference_id
430 and v.LEVEL_ID = l.LEVEL_ID
431 and nvl(l.organization_id, -99) = nvl(cl_org_id, -99);
432
433 cursor multi_value_custom_panel (cl_org_id NUMBER) is
434 select count(*) as multi_value_count
435 from flm_ekb_preference_values v,
436 flm_ekb_preference_levels l
437 where v.PREFERENCE_ID = p_preference_id
438 and v.LEVEL_ID = l.LEVEL_ID
439 and nvl(l.organization_id, -99) = nvl(cl_org_id, -99);
440
441 begin
442 if p_org_id is not null then
443 l_level_code := 1;
444 else
445 l_level_code := 0;
446 end if;
447
448 for c_pref_type in pref_type(p_preference_id) loop
449 l_pref_type := c_pref_type.preference_type;
450 end loop;
451
452 if (l_pref_type = 1) then --single_level_preference
453 for c_single_value in single_value(p_org_id) loop
454 l_return_val := c_single_value.single_value_code;
455 end loop;
456 elsif(l_pref_type = 2) then --multi value preference
457 for c_multi_val in multi_value(p_org_id) loop
458 l_multi_val_cnt := c_multi_val.multi_value_count;
459 end loop;
460 if(l_multi_val_cnt > 0) then
461 l_return_val := 'ENTERED';
462 else
463 l_return_val := 'INHERIT';
464 end if;
465 end if;
466
467 if( l_return_val = 'INHERIT' and l_level_code = 0 ) then
468 l_return_val := 'NOTENTERED';
469 end if;
470 for c_multi_val_cp in multi_value_custom_panel(p_org_id) loop
471 l_multi_val_cnt_cp := c_multi_val_cp.multi_value_count;
472 end loop;
473 if(p_preference_id = FLM_KANBAN_CONSTANTS.CUSTOM_PANEL
474 and l_return_val = 'INHERIT'
475 and l_level_code = 1
476 and l_multi_val_cnt_cp > 0) then
477 l_return_val := 'NOTENTERED';
478 end if;
479 return l_return_val;
480 end;
481
482
483
484 --
485 -- This function returns attribute value based on attribute_value_code.
486 --
487 function get_preference_value
488 (p_preference_id number,
489 p_org_id number default null) return varchar2 is
490
491 l_return_val varchar2(240);
492 l_value_code varchar2(240);
493 l_lookup_type varchar2(30);
494
495 cursor lookup_meaning (cl_value_code varchar2, c1_lookup_type varchar2) is
496 select ml.meaning
497 from mfg_lookups ml
498 where ml.lookup_code = to_number(cl_value_code) and
499 ml.lookup_type = c1_lookup_type;
500
501 /* (select wp.preference_value_lookup_type
502 from flm_ekb_preference_definitions wp
503 where wp.preference_id = p_preference_id);*/
504 begin
505
506 l_value_code := get_preference_value_code (p_preference_id, p_org_id);
507 if l_value_code = 'INHERIT' then
508 return fnd_message.get_string('WIP', 'WIP_PREFERENCE_INHERIT');
509 elsif l_value_code = 'ENTERED' then
510 return fnd_message.get_string('WIP', 'WIP_PREFERENCE_ENTERED');
511 elsif l_value_code = 'NOTENTERED' then
512 return 'Not Entered';
513 /*TODO: insert a fnd message and get from there*/
514 /*return fnd_message.get_string('WIP', 'WIP_PREFERENCE_ENTERED');*/
515 else
516 /*value*/
517 select wp.preference_value_lookup_type
518 into l_lookup_type
519 from flm_ekb_preference_definitions wp
520 where wp.preference_id = p_preference_id;
521
522 /*code*/
523 open lookup_meaning (l_value_code, l_lookup_type);
524 fetch lookup_meaning into l_return_val;
525 if lookup_meaning%NOTFOUND then
526 close lookup_meaning;
527 raise no_data_found;
528 end if;
529 close lookup_meaning;
530 return l_return_val;
531 end if;
532 EXCEPTION
533 when NO_DATA_FOUND then
534 return l_value_code;
535 end;
536
537 --
538 -- The function calculates the result preference value
539 --
540 function get_result_value_code
541 (p_preference_id number,
542 p_org_id number default null) return varchar2 is
543
544 l_default_val varchar2(240);
545 l_org_val varchar2(240);
546
547 begin
548
549 l_default_val := get_preference_value_code (p_preference_id);
550 l_org_val := get_preference_value_code (p_preference_id, p_org_id);
551
552
553 if l_org_val = 'INHERIT' then
554 return l_default_val;
555 else
556 return l_org_val;
557 end if;
558 end;
559
560 function get_result_value
561 (p_preference_id number,
562 p_org_id number default null) return varchar2 is
563
564 l_default_val varchar2(240);
565 l_org_val varchar2(240);
566
567 l_default_val_code varchar2(240);
568 l_org_val_code varchar2(240);
569
570 begin
571
572 l_default_val := get_preference_value (p_preference_id);
573 l_org_val := get_preference_value (p_preference_id, p_org_id);
574
575 l_default_val_code := get_preference_value_code (p_preference_id);
576 l_org_val_code := get_preference_value_code (p_preference_id, p_org_id);
577
578 if l_org_val_code = 'INHERIT' then
579 return l_default_val;
580 else
581 return l_org_val;
582 end if;
583 end;
584 /*End: adding new functions*/
585
586 /*Start: functions to add rows to flm_ekb_preference_values*/
587 /*function insertRowPrefValues()*/
588
589 procedure insertRow(p_preference_id number,
590 p_level_id number,
591 p_sequence_number number,
592 p_attribute_name varchar2,
593 p_attribute_value_code varchar2,
594 p_enable_flag number,
595 p_user_id number) is
596 begin
597 insert into flm_ekb_preference_values
598 (
599 PREFERENCE_VALUE_ID,
600 PREFERENCE_ID,
601 LEVEL_ID,
602 SEQUENCE_NUMBER,
603 ATTRIBUTE_NAME,
604 ATTRIBUTE_VALUE_CODE,
605 ATTRIBUTE_ENABLE_FLAG,
606 CREATED_BY,
607 CREATION_DATE,
608 LAST_UPDATED_BY,
609 LAST_UPDATE_DATE,
610 LAST_UPDATE_LOGIN,
611 OBJECT_VERSION_NUMBER
612 )
613 VALUES
614 (
615 flm_ekb_pref_val_seq.nextval, --get from a sequence--PREFERENCE_VALUE_ID,
616 p_preference_id, --PREFERENCE_ID,
617 p_level_id, --LEVEL_ID,
618 p_sequence_number, --SEQUENCE_NUMBER,
619 p_attribute_name, --ATTRIBUTE_NAME,
620 p_attribute_value_code, --ATTRIBUTE_VALUE_CODE,
621 p_enable_flag, --ATTRIBUTE_ENABLE_FLAG
622 p_user_id, --CREATED_BY,
623 sysdate, --CREATION_DATE,
624 p_user_id, --LAST_UPDATED_BY,
625 sysdate, --LAST_UPDATE_DATE,
626 p_user_id, --LAST_UPDATE_LOGIN,
627 1 --OBJECT_VERSION_NUMBER
628 );
629 end;
630
631 function insertRowLevels(p_org_id number, p_module_id number, p_user_id number) return number is
632 l_level_code number;
633 l_level_id number;
634 begin
635 if (p_org_id is null) then
636 l_level_code := 0;/*Site*/
637 else l_level_code := 1;/*Org*/
638 end if;
639 l_level_id := flm_ekb_pref_level_seq.nextval; /*Need to generate by sequence*/
640 insert into flm_ekb_preference_levels
641 (
642 LEVEL_ID,
643 LEVEL_CODE,
644 ORGANIZATION_ID,
645 MODULE_ID,
646 CREATED_BY,
647 CREATION_DATE,
648 LAST_UPDATED_BY,
649 LAST_UPDATE_DATE,
650 LAST_UPDATE_LOGIN,
651 OBJECT_VERSION_NUMBER
652 )
653 values
654 (
655 l_level_id, --LEVEL_ID,
656 l_level_code, --LEVEL_CODE,
657 p_org_id, --ORGANIZATION_ID,
658 p_module_id, --MODULE_ID,
659 p_user_id, --CREATED_BY,
660 sysdate, --CREATION_DATE,
661 p_user_id, --LAST_UPDATED_BY,
662 sysdate, --LAST_UPDATE_DATE,
663 p_user_id, --LAST_UPDATE_LOGIN,
664 1 --OBJECT_VERSION_NUMBER
665 );
666 return l_level_id;
667 end;
668
669 function get_level_id(p_org_id number) return number is
670 l_level_id number;
671 begin
672 select level_id into l_level_id
673 from flm_ekb_preference_levels
674 where organization_id = p_org_id;
675 return l_level_id;
676 EXCEPTION
677 when NO_DATA_FOUND then
678 return null;
679 end;
680
681 procedure insertRowsPrefValues(p_preference_id number, p_org_id number, p_user_id number) is
682 l_level_id number;
683 l_module_id number;
684 l_code varchar2(30);
685 l_value varchar2(80);
686 l_enable_flag number;
687 l_code_old varchar2(30);
688 l_seq number;
689 l_seq_max number;
690 l_count number;
691 l_code_old_custom varchar2(1);
692 l_code_system varchar2(1);
693 l_retstatus varchar2(1);
694 l_params flm_ekanban_logger.param_tbl_t;
695
696 cursor c_unmovedcard_status IS
697 select ml.lookup_code
698 from mfg_lookups ml
699 where lookup_type = 'MTL_KANBAN_SUPPLY_STATUS'
700 and to_number(ml.lookup_code) <= 8 /*only system/custom statuses till 8 will be honored in ekanban*/
701 and ml.enabled_flag = 'Y' /*consider only enabled status*/
702 and (ml.end_date_active is null OR ml.end_date_active > sysdate) /*consider status with future end date*/
703 order by lookup_code asc;
704
705 begin
706 -- write parameter value to log file
707 l_params(1).paramName := 'p_preference_id';
708 l_params(1).paramValue := p_preference_id;
709 l_params(2).paramName := 'p_org_id';
710 l_params(2).paramValue := p_org_id;
711 l_params(3).paramName := 'p_user_id';
712 l_params(3).paramValue := p_user_id;
713 flm_ekanban_logger.entryPoint(p_logLevel => flm_ekanban_logger.TRACE_LOGGING,
714 p_procName =>'flm_kanban_config_params.insertRowsPrefValues',
715 p_params => l_params,
716 x_returnStatus => l_retstatus);
717
718 /*get the module id from preference*/
719 select fepd.module_id
720 into l_module_id
721 from FLM_EKB_PREFERENCE_definitions fepd
722 where fepd.preference_id = p_preference_id;
723
724 flm_ekanban_logger.log(flm_ekanban_logger.FULL_LOGGING, 'l_module_id: ' || l_module_id, l_retstatus);
725 /*get level for the org. if level_id not found, then insert into levels table*/
726 if(p_org_id is null) then
727 l_level_id := 1;/*setting level for Site when org_id is null*/
728 else
729 l_level_id := get_level_id(p_org_id);
730 end if;
731 flm_ekanban_logger.log(flm_ekanban_logger.FULL_LOGGING, 'l_level_id (1.0): ' || l_level_id, l_retstatus);
732
733 if(l_level_id is null) then
734 /*No entries for this particular org*/
735 /*Bug 14032202: When no rows existing for this organization in level table, then
736 insert the row and carry out other insertion into values table for unmove_card preference*/
737 l_level_id := insertRowLevels(p_org_id, l_module_id, p_user_id);
738 flm_ekanban_logger.log(flm_ekanban_logger.FULL_LOGGING, 'l_level_id (2.0): ' || l_level_id, l_retstatus);
739 end if;
740
741 /*Check if for the level, preference already has rows in the table*/
742 select count(*) into l_count
743 from FLM_EKB_PREFERENCE_VALUES
744 where level_id = l_level_id
745 and preference_id = p_preference_id;
746 flm_ekanban_logger.log(flm_ekanban_logger.FULL_LOGGING, 'l_count: ' || l_count, l_retstatus);
747 flm_ekanban_logger.log(flm_ekanban_logger.FULL_LOGGING, 'preference_id : ' || p_preference_id, l_retstatus);
748 if(l_count>0) then
749 if(p_preference_id=13) then
750 /*there can be 3 cases, 1. increase, 2. decrease,
751 . increase and decrease: count same. Below code take care of all*/
752 select max(sequence_number)
753 into l_seq_max
754 from flm_ekb_preference_values
755 where preference_id = 13;
756 flm_ekanban_logger.log(flm_ekanban_logger.FULL_LOGGING, 'l_seq_max : ' || l_seq_max, l_retstatus);
757 l_seq := l_seq_max;
758 for c_status in c_unmovedcard_status loop
759 l_seq := l_seq + 1;
760 l_code := c_status.lookup_code;
761 flm_ekanban_logger.log(flm_ekanban_logger.FULL_LOGGING, 'l_seq : ' || l_seq || 'l_code: ' || l_code, l_retstatus);
762 begin
763 select attribute_value_code
764 into l_value
765 from flm_ekb_preference_values
766 where preference_id = p_preference_id
767 and attribute_name = l_code
768 and sequence_number <= l_seq_max
769 and level_id = l_level_id;
770
771 select attribute_enable_flag
772 into l_enable_flag
773 from flm_ekb_preference_values
774 where preference_id = p_preference_id
775 and attribute_name = l_code
776 and sequence_number <= l_seq_max
777 and level_id = l_level_id;
778
779 flm_ekanban_logger.log(flm_ekanban_logger.FULL_LOGGING, 'l_value : ' || l_value|| 'l_enable_flag: ' || l_enable_flag, l_retstatus);
780 exception when others then
781 l_value := null;
782 l_enable_flag := null;
783 end;
784 /*l_value := 20;*/
785 insertRow(p_preference_id, l_level_id, l_seq, l_code, l_value, l_enable_flag, p_user_id);
786 end loop;
787 /*now delete the previously existing rows*/
788 flm_ekanban_logger.log(flm_ekanban_logger.FULL_LOGGING, 'deleting previously existing rows', l_retstatus);
789 delete from flm_ekb_preference_values
790 where preference_id = 13
791 and sequence_number <= l_seq_max
792 and level_id = l_level_id
793 and attribute_name in (
794 select attribute_name from flm_ekb_preference_values
795 where preference_id = 13
796 and sequence_number > l_seq_max
797 and level_id = l_level_id);
798 /*TODO: if needed we can update sequence_number for remaining rows
799 but currently dont see a need for it*/
800 end if;--p_preference_id=13
801 else--l_count>0
802 /*for Unmoved Cards, insert rows from mfg_lookup (current statuses)
803 for other preferences, insert from Site level*/
804 if(p_preference_id=13) then
805 /*insert rows for all system/custom statuses*/
806 l_seq := 0;
807 for c_status in c_unmovedcard_status loop
808 l_seq := l_seq + 1;
809 l_code := c_status.lookup_code;
810 flm_ekanban_logger.log(flm_ekanban_logger.FULL_LOGGING, 'l_seq : ' || l_seq || 'l_code: ' || l_code, l_retstatus);
811 begin
812 select attribute_value_code
813 into l_value
814 from flm_ekb_preference_values
815 where preference_id = p_preference_id
816 and attribute_name = l_code
817 and level_id = 1;
818 flm_ekanban_logger.log(flm_ekanban_logger.FULL_LOGGING, 'l_value : ' || l_value, l_retstatus);
819 exception when others then
820 l_value := null;
821 end;
822 /*Default value for the status is enabled. User can disable them from UI*/
823 l_enable_flag := 1;
824 insertRow(13, l_level_id, l_seq, l_code, l_value, l_enable_flag, p_user_id);
825 end loop;
826 else
827 flm_ekanban_logger.log(flm_ekanban_logger.FULL_LOGGING, 'inserting into flm_ekb_preference_values at Org from Site', l_retstatus);
828 insert into flm_ekb_preference_values
829 (
830 PREFERENCE_VALUE_ID,
831 PREFERENCE_ID,
832 LEVEL_ID,
833 SEQUENCE_NUMBER,
834 ATTRIBUTE_NAME,
835 ATTRIBUTE_VALUE_CODE,
836 CREATED_BY,
837 CREATION_DATE,
838 LAST_UPDATED_BY,
839 LAST_UPDATE_DATE,
840 LAST_UPDATE_LOGIN,
841 OBJECT_VERSION_NUMBER
842 )
843 select
844 flm_ekb_pref_val_seq.nextval,
845 fepv.preference_id,
846 l_level_id,
847 fepv.sequence_number,
848 fepv.attribute_name,
849 fepv.attribute_value_code,
850 p_user_id,
851 sysdate, --CREATION_DATE,
852 p_user_id, --LAST_UPDATED_BY,
853 sysdate, --LAST_UPDATE_DATE,
854 p_user_id, --LAST_UPDATE_LOGIN,
855 1 --OBJECT_VERSION_NUMBER
856 from flm_ekb_preference_values fepv
857 where preference_id = p_preference_id
858 and level_id = 1;
859 end if;
860 end if;--l_count>0
861 flm_ekanban_logger.exitPoint(p_logLevel => flm_ekanban_logger.TRACE_LOGGING,
862 p_procName =>'flm_kanban_config_params.insertRowsPrefValues',
863 p_procReturnStatus => l_retstatus,
864 p_msg => 'success',
865 x_returnStatus => l_retstatus);
866 end;
867
868 procedure delete_disabled_card_status(p_org_id number) is
869 l_level_id number;
870 begin
871 l_level_id := get_level_id(p_org_id);
872
873 delete from flm_ekb_preference_values
874 where preference_id = 13
875 and level_id = l_level_id
876 and attribute_name not in (
877 select ml.lookup_code
878 from mfg_lookups ml
879 where lookup_type = 'MTL_KANBAN_SUPPLY_STATUS'
880 and to_number(ml.lookup_code) <= 8 /*only system/custom statuses till 8 will be honored in ekanban*/
881 and ml.enabled_flag = 'Y' /*consider only enabled status*/
882 and (ml.end_date_active is null OR ml.end_date_active > sysdate));
883 return;
884 end;
885
886 procedure delete_preference_values(p_preference_id number, p_org_id number) is
887 l_level_id number;
888 begin
889 l_level_id := get_level_id(p_org_id);
890
891 delete from flm_ekb_preference_values
892 where preference_id = p_preference_id
893 and level_id = l_level_id;
894
895 return;
896 end;
897
898 function get_preference_value_label(p_preference_id number, p_label_code varchar2) return varchar2 is
899 l_label_meaning varchar2(30);
900 begin
901 select ml.meaning into l_label_meaning
902 from mfg_lookups ml
903 where lookup_code = p_label_code and
904 lookup_type in
905 (
906 select preference_name_lookup_type from flm_ekb_preference_definitions
907 where preference_id = p_preference_id
908 );
909 return l_label_meaning;
910 end;
911
912 function get_lookup_meaning(p_lookup_type varchar2, p_lookup_code varchar2) return varchar2 is
913 cursor lookup_meaning (p_lookup_type varchar2, p_lookup_code varchar2) is
914 select ml.meaning
915 from mfg_lookups ml
916 where ml.lookup_code = to_number(p_lookup_code)
917 and ml.lookup_type = p_lookup_type;
918 l_ret_val varchar2(30);
919 begin
920 open lookup_meaning (p_lookup_type, p_lookup_code);
921 fetch lookup_meaning into l_ret_val;
922 if lookup_meaning%NOTFOUND then
923 close lookup_meaning;
924 return null;
925 end if;
926 close lookup_meaning;
927 return l_ret_val;
928 end;
929
930 function get_lookup_type(p_preference_id number) return varchar2 is
931 cursor lookup_type (p_preference_id number) is
932 select fepd.PREFERENCE_NAME_LOOKUP_TYPE
933 from flm_ekb_preference_definitions fepd
934 where fepd.preference_id = p_preference_id;
935 l_ret_val varchar2(30);
936 begin
937 open lookup_type (p_preference_id);
938 fetch lookup_type into l_ret_val;
939 if lookup_type%NOTFOUND then
940 close lookup_type;
941 return null;
942 end if;
943 close lookup_type;
944 return l_ret_val;
945 end;
946
947 /*BOOLEAN is a PL/SQL type. Interfaces using other languages can only use
948 the SQL types. Thus creating another procedure of an anonymous PL/SQL
949 block that returns the boolean into a PL/SQL variable, and then translates
950 it to a VARCHAR2.
951 */
952
953 function is_function_accessible(p_function_name varchar2) return varchar2 is
954 fnd_result boolean;
955 begin
956 fnd_result := fnd_function.test(p_function_name, 'Y');
957 if(fnd_result) then
958 return 'Y';
959 else
960 return 'N';
961 end if;
962 end;
963
964 function get_new_enabled_status(p_org_id number) return varchar2 is
965 cursor new_enabled_status (p_level_id number) is
966 select ml.meaning from mfg_lookups ml
967 where ml.lookup_type = 'MTL_KANBAN_SUPPLY_STATUS'
968 and ml.lookup_code not in
969 (
970 select attribute_name
971 from flm_ekb_preference_values v
972 where preference_id = 13
973 and level_id = p_level_id
974 )
975 and ml.enabled_flag = 'Y'
976 and (ml.end_date_active is null OR ml.end_date_active > sysdate)
977 and ml.lookup_code <= '8';
978
979 l_ret_val varchar2(500);
980 l_level_id number;
981 begin
982 l_ret_val := null;
983
984 if(p_org_id is null) then
985 l_level_id := 1;/*setting level for Site when org_id is null*/
986 else
987 l_level_id := get_level_id(p_org_id);
988 end if;
989
990 for c_new_enabled_status in new_enabled_status(l_level_id) loop
991 l_ret_val := l_ret_val || c_new_enabled_status.meaning || ', ';
992 end loop;
993
994 if(l_ret_val is not null) then
995 /*Bug 14032202: Changed enabled status message text*/
996 l_ret_val := 'Following statuses will be added on clicking "Apply": ' || l_ret_val;
997 l_ret_val := substr(l_ret_val, 0, length(l_ret_val)-2) || '.';
998 end if;
999 return l_ret_val;
1000 end;
1001
1002 function get_new_disabled_status(p_org_id number) return varchar2 is
1003 cursor new_disabled_status (p_level_id number) is
1004 select v.attribute_name
1005 from flm_ekb_preference_values v
1006 where v.attribute_name in
1007 (
1008 --list of disabled codes
1009 select to_char(ml.lookup_code)
1010 from mfg_lookups ml
1011 where lookup_type = 'MTL_KANBAN_SUPPLY_STATUS'
1012 and (enabled_flag = 'N' or ml.end_date_active <= sysdate)
1013 )
1014 and v.level_id = p_level_id;
1015
1016 l_ret_val varchar2(500);
1017 l_meaning varchar2(30);
1018 l_level_id number;
1019 begin
1020 l_ret_val := null;
1021
1022 if(p_org_id is null) then
1023 l_level_id := 1;/*setting level for Site when org_id is null*/
1024 else
1025 l_level_id := get_level_id(p_org_id);
1026 end if;
1027 for c_new_disabled_status in new_disabled_status(l_level_id) loop
1028 select ml.meaning into l_meaning
1029 from mfg_lookups ml
1030 where ml.lookup_type = 'MTL_KANBAN_SUPPLY_STATUS'
1031 and ml.lookup_code = c_new_disabled_status.attribute_name;
1032 l_ret_val := l_ret_val || l_meaning || ', ';
1033 end loop;
1034
1035 if(l_ret_val is not null) then
1036 /*Bug 14032202: Changed disabled status message text*/
1037 l_ret_val := 'Following statuses will be deleted on clicking "Apply": ' || l_ret_val;
1038 l_ret_val := substr(l_ret_val, 0, length(l_ret_val)-2) || '.';
1039 end if;
1040 return l_ret_val;
1041 end;
1042
1043 /*End: functions to add rows to flm_ekb_preference_values*/
1044
1045 end flm_kanban_config_params;