[Home] [Help]
PACKAGE BODY: APPS.FTE_SEL_GROUPS_PKG
Source
1 PACKAGE BODY FTE_SEL_GROUPS_PKG AS
2 /* $Header: FTESELGB.pls 120.4 2005/08/18 12:09:12 parkhj noship $ */
3
4 TYPE rule_rest_rec IS RECORD (
5 rule_attribute_id NUMBER,
6 rule_id NUMBER,
7 attribute_name VARCHAR2(30),
8 attribute_value_from VARCHAR2(240),
9 attribute_value_to VARCHAR2(240),
10 attribute_value_from_number NUMBER,
11 attribute_value_to_number NUMBER,
12 data_type VARCHAR2(1));
13
14 TYPE rule_rest_tab IS TABLE OF rule_rest_rec INDEX BY BINARY_INTEGER;
15
16 TYPE rule_rec IS RECORD (
17 rule_id NUMBER,
18 rest_start NUMBER,
19 rest_end NUMBER);
20
21 TYPE rule_tab IS TABLE OF rule_rec INDEX BY BINARY_INTEGER;
22
23 /*------------------------------------------------------------------------
24 * Found_Date_Overlap
25 * - Check whether the group's start/end date overlaps with
26 * other groups assigned to the assignee
27 *------------------------------------------------------------------------*/
28
29 FUNCTION Found_Date_Overlap(p_group_id IN NUMBER,
30 p_start_date IN DATE,
31 p_end_date IN DATE,
32 p_assignee_type IN VARCHAR2,
33 p_assignee_id IN NUMBER) RETURN BOOLEAN IS
34
35 CURSOR c_assign IS
36 SELECT start_date, end_date
37 FROM fte_sel_group_assignments ga,
38 fte_sel_groups gr
39 WHERE gr.group_id = ga.group_id AND
40 gr.group_id <> p_group_id AND
41 ga.customer_id = p_assignee_id AND
42 p_assignee_type = 'CUST'
43 UNION ALL
44 SELECT start_date, end_date
45 FROM fte_sel_group_assignments ga,
46 fte_sel_groups gr
47 WHERE gr.group_id = ga.group_id AND
48 gr.group_id <> p_group_id AND
49 ga.customer_site_id = p_assignee_id AND
50 p_assignee_type = 'CUST_SITE'
51 UNION ALL
52 SELECT start_date, end_date
53 FROM fte_sel_group_assignments ga,
54 fte_sel_groups gr
55 WHERE gr.group_id = ga.group_id AND
56 gr.group_id <> p_group_id AND
57 ga.organization_id = p_assignee_id AND
58 p_assignee_type = 'ORG'
59 UNION ALL
60 SELECT start_date, end_date
61 FROM fte_sel_group_assignments ga,
62 fte_sel_groups gr
63 WHERE gr.group_id = ga.group_id AND
64 gr.group_id <> p_group_id AND
65 ga.organization_id is null AND
66 ga.customer_id is null AND
67 ga.customer_site_id is null AND
68 p_assignee_type = 'ENT';
69
70 BEGIN
71 FOR r_assign IN c_assign LOOP
72
73 if (NOT((p_end_date is not null and r_assign.start_date is not null and
74 p_end_date < r_assign.start_date) OR
75 (p_start_date is not null and r_assign.end_date is not null and
76 p_start_date > r_assign.end_date)))
77 then
78 return TRUE;
79 end if;
80 END LOOP;
81
82 return false;
83 END Found_Date_Overlap;
84
85 /*------------------------------------------------------------------------
86 * Validate_Shipmethod
87 * - check whether the shipmethod is valid
88 *------------------------------------------------------------------------*/
89
90 PROCEDURE Validate_Shipmethod(
91 p_carrier_id IN NUMBER,
92 p_service_level IN VARCHAR2,
93 p_mode IN VARCHAR2,
94 x_return_status OUT NOCOPY VARCHAR2,
95 x_msg_data OUT NOCOPY VARCHAR2) IS
96
97 CURSOR c_shipmethod IS
98 SELECT carrier_id
99 FROM wsh_carrier_services
100 WHERE carrier_id = p_carrier_id AND
101 service_level = p_service_level AND
102 mode_of_transport = p_mode;
103
104 l_carrier_id NUMBER;
105
106 BEGIN
107
108 x_return_status := 'S';
109 x_msg_data := '';
110
111 OPEN c_shipmethod;
112 FETCH c_shipmethod INTO l_carrier_id;
113 CLOSE c_shipmethod;
114
115 IF (l_carrier_id IS NULL) THEN
116 x_return_status := 'E';
117 x_msg_data := 'FTE_SEL_INVALID_SHIPMETHOD';
118 END IF;
119
120 EXCEPTION
121 when others then
122 x_return_status := 'E';
123 x_msg_data := 'Error in Delete_Results';
124
125
126 END Validate_Shipmethod;
127
128 /*------------------------------------------------------------------------
129 * Validate_Group
130 * - check whether the group is valid
131 * 1) unique Name
132 * 2) End date should be greater than or equal to Start date
133 * 3) Start/End date should not overlap with previously assigned groups
134 *------------------------------------------------------------------------*/
135
136 PROCEDURE Validate_Group(
137 p_group_id IN NUMBER,
138 p_name IN VARCHAR2,
139 p_start_date IN DATE,
140 p_end_date IN DATE,
141 p_assignee_type IN VARCHAR2,
142 p_assignee_id IN NUMBER,
143 p_mode IN VARCHAR2,
144 x_return_status OUT NOCOPY VARCHAR2,
145 x_msg_count OUT NOCOPY NUMBER,
146 x_msg_data OUT NOCOPY VARCHAR2) IS
147
148 CURSOR c_unique_name IS
149 SELECT name
150 FROM fte_sel_groups
151 WHERE name = p_name AND
152 group_id <> p_group_id;
153
154 CURSOR c_assignee IS
155 SELECT organization_id, customer_id, customer_site_id
156 FROM fte_sel_group_assignments
157 WHERE group_id = p_group_id;
158
159 l_name VARCHAR2(30);
160 l_overlap BOOLEAN;
161 l_assignee_type VARCHAR2(10);
162 l_assignee_id NUMBER;
163
164 BEGIN
165
166 x_return_status := 'S';
167 x_msg_count := 0;
168
169 OPEN c_unique_name;
170 FETCH c_unique_name INTO l_name;
171 CLOSE c_unique_name;
172
173 IF (l_name IS NOT NULL) THEN
174 x_return_status := 'E';
175 x_msg_count := x_msg_count + 1;
176 x_msg_data := x_msg_data || '|FTE_SEL_INVALID_GROUP_NAME';
177 END IF;
178
179 if (p_start_date is not null and p_end_date is not null and
180 p_start_date > p_end_date)
181 then
182 x_return_status := 'E';
183 x_msg_count := x_msg_count + 1;
184 x_msg_data := x_msg_data || '|FTE_COMP_DATE_ERROR';
185 end if;
186
187 if (p_mode <> 'UPDATE')
188 then
189 l_overlap := found_date_overlap(p_group_id => p_group_id,
190 p_start_date => p_start_date,
191 p_end_date => p_end_date,
192 p_assignee_type => p_assignee_type,
193 p_assignee_id => p_assignee_id);
194
195 if (l_overlap)
196 then
197 x_return_status := 'E';
198 x_msg_count := x_msg_count + 1;
199 x_msg_data := x_msg_data || '|FTE_SEL_GRP_OVERLAP';
200 end if;
201 else -- p_mode = 'UPDATE'
202 /* CREATE/COPY has only one asignee
203 but UPDATE might have more than the current assignee
204 or might not be assigned at all through Search By Rule - Update
205 get all the assignees that this group is assigned to
206 for each assignee, validate whether the Start/End date overlaps
207 */
208 FOR r_assignee IN c_assignee LOOP
209 IF (r_assignee.customer_id is not null) then
210 l_assignee_type := 'CUST';
211 l_assignee_id := r_assignee.customer_id;
212 ELSIF (r_assignee.customer_site_id is not null) then
213 l_assignee_type := 'CUST_SITE';
214 l_assignee_id := r_assignee.customer_site_id;
215 ELSIF (r_assignee.organization_id is not null) then
216 l_assignee_type := 'ORG';
217 l_assignee_id := r_assignee.organization_id;
218 ELSE
219 l_assignee_type := 'ENT';
220 l_assignee_id := null;
221 END IF;
222
223 l_overlap := found_date_overlap(p_group_id => p_group_id,
224 p_start_date => p_start_date,
225 p_end_date => p_end_date,
226 p_assignee_type => l_assignee_type,
227 p_assignee_id => l_assignee_id);
228
229 if (l_overlap)
230 then
231 x_return_status := 'E';
232 x_msg_count := x_msg_count + 1;
233 if (p_assignee_id is null OR p_assignee_id = l_assignee_id) then
234 x_msg_data := x_msg_data || '|FTE_SEL_GRP_OVERLAP';
235 elsif (p_assignee_id is not null) then
236 x_msg_data := x_msg_data || '|FTE_SEL_GRP_OVERLAP_OTHER_A';
237 end if;
238 EXIT;
239 end if;
240
241 END LOOP;
242
243 end if;
244
245 END Validate_Group;
246
247 /*------------------------------------------------------------------------
248 * Validate_Assignment
249 * - check whether p_group_name is assignable to the given eitity
250 * 1) start/end dates : currently active or future active
251 * 2) Customer site : not assigned to Organization/Enterprise or itself
252 * Customer : not assigned to Organization/Enterprise or itself
253 * Organization : not assigned to Customer/Customer Site or itself
254 * Enterprise : not assigned to Customer/Customer Site or itself
255 * 3) should not overlap with any existing rule
256 *------------------------------------------------------------------------*/
257
258 PROCEDURE Validate_Assignment(
259 p_group_name IN VARCHAR2,
260 p_assignee_type IN VARCHAR2,
261 p_assignee_id IN NUMBER,
262 x_group_id OUT NOCOPY NUMBER,
263 x_return_status OUT NOCOPY VARCHAR2,
264 x_msg_count OUT NOCOPY NUMBER,
265 x_msg_data OUT NOCOPY VARCHAR2) IS
266
267 CURSOR c_valid_assign IS
268 SELECT group_id, start_date, end_date
269 FROM fte_sel_groups
270 WHERE name = p_group_name and
271 object_id = 1 and
272 nvl(end_date, sysdate) >= sysdate and
273 p_assignee_type = 'CUST' and
274 group_id NOT IN (select group_id from fte_sel_group_assignments
275 where customer_id = p_assignee_id or -- already
276 organization_id is not null or -- ORG
277 organization_id is null and -- ENT
278 customer_id is null and
279 customer_site_id is null)
280 UNION ALL
281 SELECT group_id, start_date, end_date
282 FROM fte_sel_groups
283 WHERE name = p_group_name and
284 object_id = 1 and
285 nvl(end_date, sysdate) >= sysdate and
286 p_assignee_type = 'CUST_SITE' and
287 group_id NOT IN (select group_id from fte_sel_group_assignments
288 where customer_site_id = p_assignee_id or -- already
289 organization_id is not null or -- ORG
290 organization_id is null and -- ENT
291 customer_id is null and
292 customer_site_id is null)
293 UNION ALL
294 SELECT group_id, start_date, end_date
295 FROM fte_sel_groups
296 WHERE name = p_group_name and
297 object_id = 1 and
298 nvl(end_date, sysdate) >= sysdate and
299 p_assignee_type = 'ORG' and
300 group_id NOT IN (select group_id from fte_sel_group_assignments
301 where organization_id = p_assignee_id or -- already
302 customer_id is not null or -- CUST
303 customer_site_id is not null) -- CUST_SITE
304 UNION ALL
305 SELECT group_id, start_date, end_date
306 FROM fte_sel_groups
307 WHERE name = p_group_name and
308 object_id = 1 and
309 nvl(end_date, sysdate) >= sysdate and
310 p_assignee_type = 'ENT' and
311 group_id NOT IN (select group_id from fte_sel_group_assignments
312 where customer_id is not null or -- CUST
313 customer_site_id is not null or -- CUST_SITE
314 organization_id is null and -- already
315 customer_id is null and
316 customer_site_id is null);
317 l_overlap BOOLEAN;
318 l_group_id NUMBER;
319 l_start_date DATE;
320 l_end_date DATE;
321
322 BEGIN
323 OPEN c_valid_assign;
324 FETCH c_valid_assign INTO l_group_id, l_start_date, l_end_date;
325 CLOSE c_valid_assign;
326
327 if (l_group_id is not null)
328 then
329 l_overlap := found_date_overlap(p_group_id => l_group_id,
330 p_start_date => l_start_date,
331 p_end_date => l_end_date,
332 p_assignee_type => p_assignee_type,
333 p_assignee_id => p_assignee_id);
334
335 if (l_overlap)
336 then
337 x_return_status := 'E';
338 x_msg_count := x_msg_count + 1;
339 x_msg_data := 'FTE_SEL_ASSGN_GRP_OVERLAP';
340 else
341 x_return_status := 'S';
342 x_group_id := l_group_id;
343 end if;
344
345 else
346 x_return_status := 'E';
347 x_msg_count := 1;
348 x_msg_data := 'FTE_SEL_SR_ASSGN_SEL_INV_NAME';
349 end if;
350
351 END Validate_Assignment;
352
353 /*------------------------------------------------------------------------
354 * Delete_Results
355 * 1) Delete data from FTE_SEL_RESULTS
356 *------------------------------------------------------------------------*/
357
358 PROCEDURE Delete_Results( p_group_id IN NUMBER,
359 x_return_status OUT NOCOPY VARCHAR2,
360 x_msg_count OUT NOCOPY NUMBER,
361 x_msg_data OUT NOCOPY VARCHAR2) IS
362
363 BEGIN
364
365 DELETE FROM FTE_SEL_RESULTS
366 WHERE result_id in (select result_id
367 from fte_sel_rules r, fte_sel_result_assignments ra
368 where r.rule_id = ra.rule_id
369 and r.group_id = p_group_id);
370
371 x_return_status := 'S';
372
373 EXCEPTION
374 when others then
375 x_return_status := 'E';
376 x_msg_data := 'Error in Delete_Results';
377
378 END Delete_Results;
379
380 /*------------------------------------------------------------------------
381 * Save_Results
382 * 1) Insert data into FTE_SEL_RESULTS
383 * based on what are in FTE_SEL_RESULT_ASSIGNMENTS
384 * 2) Call FTE_ACS_RULE_UTIL_PKG.SET_RANGE_OVERLAP_FLAG
385 * to set the range_overlap_flag of each rule attribute
386 *------------------------------------------------------------------------*/
387
388 PROCEDURE Save_Results( p_group_id IN NUMBER,
389 x_return_status OUT NOCOPY VARCHAR2,
390 x_msg_count OUT NOCOPY NUMBER,
391 x_msg_data OUT NOCOPY VARCHAR2) IS
392
393 BEGIN
394
395 INSERT INTO FTE_SEL_RESULTS(RESULT_ID, NAME, CREATION_DATE,
396 CREATED_BY, LAST_UPDATE_DATE,
397 LAST_UPDATED_BY, LAST_UPDATE_LOGIN)
398 SELECT result_id, to_char(result_id), sysdate,
399 -1, sysdate, -1, -1
400 from fte_sel_rules r, fte_sel_result_assignments ra
401 where r.rule_id = ra.rule_id
402 and r.group_id = p_group_id;
403
404 FTE_ACS_RULE_UTIL_PKG.SET_RANGE_OVERLAP_FLAG(p_group_id);
405
406 x_return_status := 'S';
407
408 EXCEPTION
409 when others then
410 x_return_status := 'E';
411 x_msg_data := 'Error in Save_Results';
412
413 END Save_Results;
414
415
416 --
417 -- Procedure: Copy Group
418 --
419 -- Purpose: Copies group and all associated children
420 --
421 --
422 -- CHANGE RECORD:
423 -- --------------
424 -- DATE BUG BY DESCRIPTION
425 -- ---------- ------- -------- -----------------------------------------------------
426 -- 04/16/2002 2320575 ABLUNDEL Took out the inner loop for c_get_rule_restrictions
427 -- as it was only getting the first rule restriction in
428 -- the table for a rule, noe it gets all for a rule
429 --
430 -- -------------------------------------------------------------------------------------
431 PROCEDURE COPY_GROUP(p_group_id IN NUMBER,
432 x_group_id OUT NOCOPY NUMBER,
433 x_return_status OUT NOCOPY VARCHAR2,
434 x_msg_count OUT NOCOPY NUMBER,
435 x_msg_data OUT NOCOPY VARCHAR2) IS
436
437
438 cursor c_get_next_group_id IS
439 select fte_sel_groups_s.nextval
440 from dual;
441
442 l_new_group_id NUMBER;
443
444 cursor c_get_group_attributes(x_group_id NUMBER) IS
445 select group_attribute_id,
446 attribute_id,
447 attribute_default_value,
448 attribute_uom_code,
449 attribute_name
450 from fte_sel_group_attributes
451 where group_id = x_group_id;
452
453
454 cursor c_get_rules(xxx_group_id NUMBER) IS
455 select FTE_SEL_RULES_S.NEXTVAL,
456 rule_id,
457 name,
458 precedence,
459 sequence_number
460 from fte_sel_rules
461 where group_id = xxx_group_id;
462
463 cursor c_get_rule_restrictions(x_rule_id NUMBER) IS
464 select rule_attribute_id,
465 rule_id,
466 attribute_name,
467 attribute_value_from,
468 attribute_value_to,
469 attribute_value_from_number,
470 attribute_value_to_number,
471 data_type,
472 grouping_number
473 from fte_sel_rule_restrictions
474 where rule_id = x_rule_id;
475
476 cursor c_get_results(x_result_id NUMBER) IS
477 select FTE_SEL_RESULTS_S.NEXTVAL,
478 FTE_SEL_RESULT_ASSIGNMENTS_S.NEXTVAL,
479 result_id,
480 name,
481 description,
482 enabled_flag,
483 rank
484 from fte_sel_results
485 where result_id = x_result_id;
486
487 cursor c_get_result_assignments(xx_rule_id NUMBER) IS
488 select result_assignment_id,
489 rule_id,
490 result_id
491 from fte_sel_result_assignments
492 where rule_id = xx_rule_id;
493
494 cursor c_get_result_attributes(xx_result_id NUMBER) IS
495 select FTE_SEL_RESULT_ATTRIBUTES_S.NEXTVAL,
496 result_attribute_id,
497 result_id,
498 attribute_code,
499 attribute_value
500 from fte_sel_result_Attributes
501 where result_id = xx_result_id;
502
503 TYPE TableNumbers is TABLE of NUMBER INDEX BY BINARY_INTEGER; -- table number type
504 TYPE TableVarchar30 is TABLE of VARCHAR2(30) INDEX BY BINARY_INTEGER; -- table varchar(30)
505 TYPE TableVarchar3 is TABLE of VARCHAR2(3) INDEX BY BINARY_INTEGER; -- table varchar(3)
506 TYPE TableVarchar240 is TABLE of VARCHAR2(240) INDEX BY BINARY_INTEGER; -- table varchar(240)
507 TYPE TableVarchar10 is TABLE of VARCHAR2(10) INDEX BY BINARY_INTEGER; -- table varchar(10)
508 TYPE TableVarchar1 is TABLE of VARCHAR2(1) INDEX BY BINARY_INTEGER; -- table varchar(1)
509
510 t_group_attribute_id TableNumbers;
511 t_attribute_id TableNumbers;
512 t_attribute_default_value TableVarchar240;
513 t_attribute_uom_code TableVarchar10;
514 t_attribute_name TableVarchar30;
515
516 t_rule_id TableNumbers;
517 t_new_rule_id TableNumbers;
518 t_rule_name TableVarchar30;
519 t_rule_precedence TableNumbers;
520 t_rule_sequence_number TableNumbers;
521
522 t_resass_result_assignment_id TableNumbers;
523 t_resass_rule_id TableNumbers;
524 t_resass_result_id TableNumbers;
525
526 t_new_result_assignment_id TableNumbers;
527 tt_resass_result_assignment_id TableNumbers;
528 tt_resass_rule_id TableNumbers;
529 tt_resass_result_id TableNumbers;
530 tt_new_result_assignment_id TableNumbers;
531
532 t_new_result_id TableNumbers;
533 t_result_result_id TableNumbers;
534 t_result_name TableVarchar30;
535 t_result_description TableVarchar240;
536 t_result_enabled_flag TableVarchar1;
537 t_result_rank TableNumbers;
538
539 tt_new_result_id TableNumbers;
540 tt_result_result_id TableNumbers;
541 tt_result_name TableVarchar30;
542 tt_result_description TableVarchar240;
543 tt_result_enabled_flag TableVarchar1;
544 tt_result_rank TableNumbers;
545
546 t_rest_rule_attribute_id TableNumbers;
547 t_rest_rule_id TableNumbers;
548 t_rest_attribute_name TableVarchar30;
549 t_rest_attribute_value_from TableVarchar240;
550 t_rest_attribute_value_to TableVarchar240;
551 t_rest_attribute_value_from_n TableNumbers;
552 t_rest_attribute_value_to_n TableNumbers;
553 t_rest_data_type TableVarchar1;
554 t_rest_grouping_number TableNumbers;
555
556 tt_rest_rule_attribute_id TableNumbers;
557 tt_rest_rule_id TableNumbers;
558 tt_rest_attribute_name TableVarchar30;
559 tt_rest_attribute_value_from TableVarchar240;
560 tt_rest_attribute_value_to TableVarchar240;
561 tt_rest_attribute_value_from_n TableNumbers;
562 tt_rest_attribute_value_to_n TableNumbers;
563 tt_rest_data_type TableVarchar1;
564 tt_rest_grouping_number TableNumbers;
565
566 t_new_result_attribute_id TableNumbers;
567 t_resattr_attribute_id TableNumbers;
568 t_resattr_result_id TableNumbers;
569 t_resattr_attribute_code TableVarchar30;
570 t_resattr_attribute_value TableVarchar240;
571
572 tt_new_result_attribute_id TableNumbers;
573 tt_resattr_attribute_id TableNumbers;
574 tt_resattr_result_id TableNumbers;
575 tt_resattr_attribute_code TableVarchar30;
576 tt_resattr_attribute_value TableVarchar240;
577
578
579 n NUMBER := 0;
580 q NUMBER := 0;
581 t NUMBER := 0;
582 y NUMBER := 0;
583
584 l_t_new_group_id VARCHAR2(30); -- char version of l_new_group_id
585 l_error_code NUMBER;
586 l_error_text VARCHAR2(4000);
587
588 BEGIN
589
590 --
591 -- Copy group:
592 -- 1) FTE_SEL_GROUPS
593 -- 2) FTE_SEL_GROUP_ATTRIBUTES
594 -- 3) FTE_SEL_RULES
595 -- 4) FTE_SEL_RULE_RESTRICTIONS
596 -- 5) FTE_SEL_RESULTS
597 -- 5a) FTE_SEL_RESULT_ASSIGNMENTS
598 -- 5b) FTE_SEL_RESULT_ATTRIBUTES
599 --
600
601
602
603 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
604
605
606 --
607 -- Get the new group id
608 --
609 OPEN c_get_next_group_id;
610 FETCH c_get_next_group_id INTO l_new_group_id;
611 IF (c_get_next_group_id%ISOPEN) THEN
612 CLOSE c_get_next_group_id;
613 END IF;
614
615 IF ((l_new_group_id <= 0) OR
616 (l_new_group_id is null)) THEN
617 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
618 RETURN;
619 END IF;
620
621 l_t_new_group_id := to_char(l_new_group_id);
622
623
624 --
625 -- 1) FTE_SEL_GROUPS
626 --
627 BEGIN
628 SAVEPOINT insert_fte_sel_groups;
629
630 INSERT INTO fte_sel_groups(GROUP_ID,
631 NAME,
632 DESCRIPTION,
633 OBJECT_ID,
634 START_DATE,
635 END_DATE,
636 CREATION_DATE,
637 CREATED_BY,
638 LAST_UPDATE_DATE,
639 LAST_UPDATED_BY,
640 LAST_UPDATE_LOGIN,
641 ASSIGNED_FLAG,
642 GROUP_STATUS_FLAG)
643 (SELECT l_new_group_id,
644 l_t_new_group_id,
645 'Copy of '||name,
646 object_id,
647 start_date,
648 end_date,
649 sysdate,
650 -1,
651 sysdate,
652 -1,
653 -1,
654 'N',
655 group_status_flag
656 FROM fte_sel_groups
657 WHERE group_id = p_group_id);
658
659
660 -- substr(name,1,decode(instr(name, '('),0,30,instr(name,'('))-2)||' (Copy# '||l_new_group_id||')',
661
662 EXCEPTION
663 WHEN OTHERS THEN
664 ROLLBACK TO insert_fte_sel_groups;
665 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
666 RETURN;
667 END;
668
669
670 x_group_id := l_new_group_id;
671
672
673
674 --
675 -- 2) FTE_SEL_GROUP_ATTRIBUTES
676 --
677
678 t_group_attribute_id.DELETE;
679 t_attribute_id.DELETE;
680 t_attribute_default_value.DELETE;
681 t_attribute_uom_code.DELETE;
682 t_attribute_name.DELETE;
683
684 --
685 -- Peform a bulk fetch of group attributes
686 --
687 OPEN c_get_group_attributes(p_group_id);
688 LOOP
689 FETCH c_get_group_attributes BULK COLLECT INTO
690 t_group_attribute_id,
691 t_attribute_id,
692 t_attribute_default_value,
693 t_attribute_uom_code,
694 t_attribute_name;
695
696
697 EXIT WHEN c_get_group_attributes%NOTFOUND OR c_get_group_attributes%NOTFOUND IS NULL;
698 END LOOP;
699 IF (c_get_group_attributes%ISOPEN) THEN
700 CLOSE c_get_group_attributes;
701 END IF;
702
703
704 --
705 -- Copy the attributes
706 --
707 IF (t_group_attribute_id.COUNT > 0) THEN
708
709 FORALL j in t_group_attribute_id.FIRST..t_group_attribute_id.LAST
710 INSERT INTO fte_sel_group_attributes (
711 GROUP_ATTRIBUTE_ID,
712 ATTRIBUTE_ID,
713 ATTRIBUTE_DEFAULT_VALUE,
714 ATTRIBUTE_UOM_CODE,
715 GROUP_ID,
716 CREATION_DATE,
717 CREATED_BY,
718 LAST_UPDATE_DATE,
719 LAST_UPDATED_BY,
720 LAST_UPDATE_LOGIN,
721 ATTRIBUTE_NAME)
722 VALUES
723 (FTE_SEL_GROUP_ATTRIBUTES_S.NEXTVAL,
724 t_attribute_id(j),
725 t_attribute_default_value(j),
726 t_attribute_uom_code(j),
727 l_new_group_id,
728 sysdate,
729 -1,
730 sysdate,
731 -1,
732 -1,
733 t_attribute_name(j));
734 END IF;
735
736
737
738 --
739 -- 3) FTE_SEL_RULES
740 --
741 t_rule_id.DELETE;
742 t_rule_name.DELETE;
743 t_rule_precedence.DELETE;
744 t_rule_sequence_number.DELETE;
745
746 --
747 -- Peform a bulk fetch of rules
748 --
749
750 OPEN c_get_rules(p_group_id);
751 LOOP
752 FETCH c_get_rules BULK COLLECT INTO
753 t_new_rule_id,
754 t_rule_id,
755 t_rule_name,
756 t_rule_precedence,
757 t_rule_sequence_number;
758
759 EXIT WHEN c_get_rules%NOTFOUND OR c_get_rules%NOTFOUND IS NULL;
760
761 END LOOP;
762 IF (c_get_rules%ISOPEN) THEN
763 CLOSE c_get_rules;
764 END IF;
765
766
767 --
768 -- Copy the rules
769 --
770 IF (t_rule_id.COUNT > 0) THEN
771
772 FORALL k in t_rule_id.FIRST..t_rule_id.LAST
773 INSERT INTO fte_sel_rules(
774 RULE_ID,
775 NAME,
776 GROUP_ID,
777 PRECEDENCE,
778 CREATION_DATE,
779 CREATED_BY,
780 LAST_UPDATE_DATE,
781 LAST_UPDATED_BY,
782 LAST_UPDATE_LOGIN,
783 SEQUENCE_NUMBER)
784 VALUES (t_new_rule_id(k),
785 to_char(t_new_rule_id(k)),
786 l_new_group_id,
787 t_rule_precedence(k),
788 sysdate,
789 -1,
790 sysdate,
791 -1,
792 -1,
793 t_rule_sequence_number(k));
794
795
796 -- substr(t_rule_name(k),1,decode(instr(t_rule_name(k), '('),0,30,instr(t_rule_name(k),'('))-2)||' (Copy# '||t_new_rule_id(k)||')',
797
798
799 END IF;
800
801
802
803 --
804 -- 4) FTE_SEL_RULE_RESTRICTIONS
805 --
806 t_rest_rule_attribute_id.DELETE;
807 t_rest_rule_id.DELETE;
808 t_rest_attribute_name.DELETE;
809 t_rest_attribute_value_from.DELETE;
810 t_rest_attribute_value_to.DELETE;
811 t_rest_attribute_value_from_n.DELETE;
812 t_rest_attribute_value_to_n.DELETE;
813 t_rest_data_type.DELETE;
814 t_rest_grouping_number.DELETE;
815
816 n := 0;
817
818 FOR l IN t_rule_id.FIRST..t_rule_id.LAST LOOP
819 OPEN c_get_rule_restrictions(t_rule_id(l));
820 --
821 -- BUG: 2320575 LOOP
822 -- removed loop
823 --
824 FETCH c_get_rule_restrictions BULK COLLECT INTO
825 t_rest_rule_attribute_id,
826 t_rest_rule_id,
827 t_rest_attribute_name,
828 t_rest_attribute_value_from,
829 t_rest_attribute_value_to,
830 t_rest_attribute_value_from_n,
831 t_rest_attribute_value_to_n,
832 t_rest_data_type,
833 t_rest_grouping_number;
834
835 --
836 -- Should have all the rule restrictions now for a rule
837 --
838 --
839 -- Copy the rules
840 --
841 IF (t_rest_rule_attribute_id.COUNT > 0) THEN
842 FORALL o in t_rest_rule_attribute_id.FIRST..t_rest_rule_attribute_id.LAST
843 INSERT INTO fte_sel_rule_restrictions(
844 RULE_ATTRIBUTE_ID,
845 RULE_ID,
846 ATTRIBUTE_NAME,
847 ATTRIBUTE_VALUE_FROM,
848 ATTRIBUTE_VALUE_TO,
849 ATTRIBUTE_VALUE_FROM_NUMBER,
850 ATTRIBUTE_VALUE_TO_NUMBER,
851 DATA_TYPE,
852 GROUPING_NUMBER,
853 CREATION_DATE,
854 CREATED_BY,
855 LAST_UPDATE_DATE,
856 LAST_UPDATED_BY,
857 LAST_UPDATE_LOGIN,
858 GROUP_ID)
859 VALUES (FTE_SEL_RULE_RESTRICTIONS_S.NEXTVAL,
860 t_new_rule_id(l),
861 t_rest_attribute_name(o),
862 t_rest_attribute_value_from(o),
863 t_rest_attribute_value_to(o),
864 t_rest_attribute_value_from_n(o),
865 t_rest_attribute_value_to_n(o),
866 t_rest_data_type(o),
867 t_rest_grouping_number(o),
868 sysdate,
869 -1,
870 sysdate,
871 -1,
872 -1,
873 l_new_group_id);
874 END IF;
875
876 IF (c_get_rule_restrictions%ISOPEN) THEN
877 CLOSE c_get_rule_restrictions;
878 END IF;
879 END LOOP;
880
881 --
882 -- 5) FTE_SEL_RESULTS
883 --
884 t_new_result_assignment_id.DELETE;
885 t_resass_result_assignment_id.DELETE;
886 t_resass_rule_id.DELETE;
887 t_resass_result_id.DELETE;
888
889 tt_new_result_assignment_id.DELETE;
890 tt_resass_result_assignment_id.DELETE;
891 tt_resass_rule_id.DELETE;
892 tt_resass_result_id.DELETE;
893
894 t_new_result_id.DELETE;
895 t_result_result_id.DELETE;
896 t_result_name.DELETE;
897 t_result_description.DELETE;
898 t_result_enabled_flag.DELETE;
899 t_result_rank.DELETE;
900 tt_new_result_id.DELETE;
901 tt_result_result_id.DELETE;
902 tt_result_name.DELETE;
903 tt_result_description.DELETE;
904 tt_result_enabled_flag.DELETE;
905 tt_result_rank.DELETE;
906
907 q := 0;
908 --
909 -- First we have to query the assignments table by rule_id to get the result_id
910 --
911 FOR p IN t_rule_id.FIRST..t_rule_id.LAST LOOP
912 OPEN c_get_result_assignments(t_rule_id(p));
913 LOOP
914 FETCH c_get_result_assignments BULK COLLECT INTO
915 t_resass_result_assignment_id,
916 t_resass_rule_id,
917 t_resass_result_id;
918
919
920 q := 0;
921
922 IF (t_resass_result_assignment_id.COUNT > 0 ) THEN
923 FOR r in t_resass_result_assignment_id.FIRST..t_resass_result_assignment_id.LAST LOOP
924 q := q + 1;
925 tt_resass_result_assignment_id(q) := t_resass_result_assignment_id(r);
926 tt_resass_rule_id(q) := t_rule_id(p);
927 tt_resass_result_id(q) := t_resass_result_id(r);
928 END LOOP;
929 END IF;
930
931 EXIT WHEN c_get_result_assignments%NOTFOUND or c_get_result_assignments%NOTFOUND IS NULL;
932 END LOOP;
933
934
935 --
936 -- Should have all the assignments for a rule
937 -- (query the result)
938 --
939 FOR s IN tt_resass_result_id.FIRST.. tt_resass_result_id.LAST LOOP
940
941
942 OPEN c_get_results(tt_resass_result_id(s));
943 LOOP
944 FETCH c_get_results BULK COLLECT INTO
945 t_new_result_id,
946 t_new_result_assignment_id,
947 t_result_result_id,
948 t_result_name,
949 t_result_description,
950 t_result_enabled_flag,
951 t_result_rank;
952
953
954 IF (t_result_result_id.COUNT > 0 ) THEN
955 t := 0;
956 FOR u in t_result_result_id.FIRST..t_result_result_id.LAST LOOP
957 t := t + 1;
958 tt_new_result_id(t) := t_new_result_id(u);
959 tt_new_result_assignment_id(t) := t_new_result_assignment_id(u);
960 tt_result_result_id(t) := t_result_result_id(u);
961 tt_result_name(t) := t_result_name(u);
962 tt_result_description(t) := t_result_description(u);
963 tt_result_enabled_flag(t) := t_result_enabled_flag(u);
964 tt_result_rank(t) := t_result_rank(u);
965
966 END LOOP;
967 END IF;
968
969 EXIT WHEN c_get_results%NOTFOUND or c_get_results%NOTFOUND IS NULL;
970 END LOOP; -- [BULK FETCH]
971 --
972 -- now we have the results for the rule
973 --
974 --
975 -- Copy the results
976 --
977 IF (tt_result_result_id.COUNT > 0) THEN
978 /*
979 do not create data in FTE_SEL_RESULTS yet
980 it'll be handled by Save_Results as the final step of copy flow
981
982 FORALL v in tt_result_result_id.FIRST..tt_result_result_id.LAST
983 INSERT INTO fte_sel_results (
984 RESULT_ID,
985 NAME,
986 DESCRIPTION,
987 ENABLED_FLAG,
988 CREATION_DATE,
989 CREATED_BY,
990 LAST_UPDATE_DATE,
991 LAST_UPDATED_BY,
992 LAST_UPDATE_LOGIN,
993 RANK)
994 VALUES (tt_new_result_id(v),
995 to_char(tt_new_result_id(v)),
996 tt_result_description(v),
997 tt_result_enabled_flag(v),
998 sysdate,
999 -1,
1000 sysdate,
1001 -1,
1002 -1,
1003 tt_result_rank(v));
1004 */
1005
1006
1007 --
1008 -- copy the asssignments
1009 --
1010 IF (tt_result_result_id.COUNT > 0) THEN
1011 FORALL w in tt_result_result_id.FIRST..tt_result_result_id.LAST
1012 INSERT INTO fte_sel_result_assignments (
1013 RESULT_ASSIGNMENT_ID,
1014 RULE_ID,
1015 RESULT_ID,
1016 CREATION_DATE,
1017 CREATED_BY,
1018 LAST_UPDATE_DATE,
1019 LAST_UPDATED_BY,
1020 LAST_UPDATE_LOGIN)
1021 VALUES (tt_new_result_assignment_id(w),
1022 t_new_rule_id(p),
1023 tt_new_result_id(w),
1024 sysdate,
1025 -1,
1026 sysdate,
1027 -1,
1028 -1);
1029 END IF;
1030
1031
1032 y := 0;
1033 --
1034 -- Query the result attributes
1035 --
1036 t_new_result_attribute_id.DELETE;
1037 t_resattr_attribute_id.DELETE;
1038 t_resattr_result_id.DELETE;
1039 t_resattr_attribute_code.DELETE;
1040 t_resattr_attribute_value.DELETE;
1041 tt_new_result_attribute_id.DELETE;
1042 tt_resattr_attribute_id.DELETE;
1043 tt_resattr_result_id.DELETE;
1044 tt_resattr_attribute_code.DELETE;
1045 tt_resattr_attribute_value.DELETE;
1046
1047 FOR x IN tt_result_result_id.FIRST.. tt_result_result_id.LAST LOOP
1048 OPEN c_get_result_attributes(tt_result_result_id(x));
1049 LOOP
1050 FETCH c_get_result_attributes BULK COLLECT INTO
1051 t_new_result_attribute_id,
1052 t_resattr_attribute_id,
1053 t_resattr_result_id,
1054 t_resattr_attribute_code,
1055 t_resattr_attribute_value;
1056
1057 IF (t_resattr_attribute_id.COUNT > 0 ) THEN
1058 FOR z in t_resattr_attribute_id.FIRST..t_resattr_attribute_id.LAST LOOP
1059 y := y + 1;
1060 tt_new_result_attribute_id(y) := t_new_result_attribute_id(z);
1061 tt_resattr_attribute_id(y) := t_resattr_attribute_id(z);
1062 tt_resattr_result_id(y) := t_resattr_result_id(z);
1063 tt_resattr_attribute_code(y) := t_resattr_attribute_code(z);
1064 tt_resattr_attribute_value(y) := t_resattr_attribute_value(z);
1065 END LOOP;
1066 END IF;
1067 EXIT WHEN c_get_result_attributes%NOTFOUND or c_get_result_attributes%NOTFOUND IS NULL;
1068 END LOOP; -- [BULK FETCH]
1069
1070 -- Copy the result attributes
1071 --
1072 IF (tt_resattr_attribute_id.COUNT > 0) THEN
1073 FORALL aa in tt_resattr_attribute_id.FIRST..tt_resattr_attribute_id.LAST
1074 INSERT INTO fte_sel_result_attributes (
1075 RESULT_ATTRIBUTE_ID,
1076 RESULT_ID,
1077 ATTRIBUTE_CODE,
1078 ATTRIBUTE_VALUE,
1079 CREATION_DATE,
1080 CREATED_BY,
1081 LAST_UPDATE_DATE,
1082 LAST_UPDATED_BY,
1083 LAST_UPDATE_LOGIN)
1084 VALUES (tt_new_result_attribute_id(aa),
1085 tt_new_result_id(x),
1086 tt_resattr_attribute_code(aa),
1087 tt_resattr_attribute_value(aa),
1088 sysdate,
1089 -1,
1090 sysdate,
1091 -1,
1092 -1);
1093
1094 tt_new_result_attribute_id.DELETE;
1095 tt_resattr_attribute_id.DELETE;
1096 tt_resattr_result_id.DELETE;
1097 tt_resattr_attribute_code.DELETE;
1098 tt_resattr_attribute_value.DELETE;
1099 END IF;
1100
1101
1102 IF (c_get_result_attributes%ISOPEN) THEN
1103 CLOSE c_get_result_attributes;
1104 END IF;
1105 END LOOP;
1106
1107
1108 t_new_result_id.DELETE;
1109 t_result_result_id.DELETE;
1110 t_result_name.DELETE;
1111 t_result_description.DELETE;
1112 t_result_enabled_flag.DELETE;
1113 t_result_rank.DELETE;
1114 tt_new_result_id.DELETE;
1115 tt_result_result_id.DELETE;
1116 tt_result_name.DELETE;
1117 tt_result_description.DELETE;
1118 tt_result_enabled_flag.DELETE;
1119 tt_result_rank.DELETE;
1120 tt_new_result_assignment_id.DELETE;
1121 t_new_result_assignment_id.DELETE;
1122 END IF;
1123 CLOSE c_get_results;
1124 END LOOP;
1125
1126
1127 t_resass_result_assignment_id.DELETE;
1128 t_resass_rule_id.DELETE;
1129 t_resass_result_id.DELETE;
1130 tt_resass_result_assignment_id.DELETE;
1131 tt_resass_rule_id.DELETE;
1132 tt_resass_result_id.DELETE;
1133
1134 CLOSE c_get_result_assignments;
1135 END LOOP;
1136
1137 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1138
1139 RETURN;
1140
1141 EXCEPTION
1142 WHEN OTHERS THEN
1143 x_group_id := null;
1144 l_error_code := SQLCODE;
1145 l_error_text := SQLERRM;
1146 WSH_UTIL_CORE.Println('The unexpected error from FTE_SEL_GROUPS_PKG.COPY_GROUP is ' ||l_error_text);
1147 WSH_UTIL_CORE.default_handler('FTE_SEL_GROUPS_PKG.COPY_GROUP');
1148 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1149 RETURN;
1150
1151 END COPY_GROUP;
1152
1153 --
1154 -- Function: Is_Valid_Region
1155 --
1156 -- Purpose: Check if the Rule consists of Regions defined in the current language
1157 --
1158 --
1159 FUNCTION Is_Valid_Region(
1160 p_group_id IN NUMBER
1161 ) RETURN VARCHAR2 IS
1162
1163
1164 CURSOR get_region_cursor IS
1165 SELECT attribute_value_from_number
1166 FROM fte_sel_rule_restrictions
1167 WHERE group_id = p_group_id
1168 AND attribute_name like '%REGION_ID';
1169
1170 all_valid VARCHAR2(1);
1171 x_region_id NUMBER;
1172
1173 BEGIN
1174 all_valid := 'Y';
1175 FOR region_cur IN get_region_cursor LOOP
1176
1177 BEGIN
1178 SELECT region_id INTO x_region_id
1179 FROM wsh_regions_v
1180 WHERE region_id = region_cur.attribute_value_from_number;
1181
1182 EXCEPTION
1183 WHEN NO_DATA_FOUND THEN
1184 all_valid := 'N';
1185 exit;
1186 WHEN OTHERS THEN
1187 null;
1188 END;
1189 END LOOP;
1190
1191 RETURN all_valid;
1192
1193 END Is_Valid_Region;
1194
1195 END FTE_SEL_GROUPS_PKG;