[Home] [Help]
PACKAGE BODY: APPS.ICX_API_REGION
Source
1 package body ICX_API_REGION
2 /* $Header: ICXREGB.pls 115.5 1999/12/09 22:54:05 pkm ship $ */
3
4 is
5
6 function create_main_region
7 return number
8
9 is
10 l_region_id icx_regions.region_id%type;
11
12 begin
13
14 -- Generate the region id
15 select icx_regions_s.nextval
16 into l_region_id
17 from sys.dual;
18
19 -- Insert a record into the region table
20 -- For the the parent_id is 0
21 -- and the region is not split
22 insert into icx_regions
23 (
24 region_id,
25 parent_region_id,
26 split_mode,
27 portlet_flow,
28 border,
29 last_update_date,
30 last_updated_by,
31 creation_date,
32 created_by,
33 last_update_login
34 )
35 values
36 (
37 l_region_id,
38 MAIN_REGION,
39 REGION_NOT_SPLIT,
40 REGION_HORIZONTAL_PORTLETFLOW,
41 'Y',
42 sysdate,
43 icx_sec.g_user_id,
44 sysdate,
45 icx_sec.g_user_id,
46 icx_sec.g_user_id
47 );
48
49 commit;
50 return l_region_id;
51
52 exception
53 when REGION_VALIDATION_EXCEPTION then
54 raise;
55
56 when DUP_VAL_ON_INDEX then
57 rollback;
58 htp.p(SQLERRM);
59
60 when OTHERS then
61 rollback;
62 htp.p(SQLERRM);
63
64 end create_main_region;
65
66
67
68 procedure split_region (
69 p_region_id in integer
70 , p_split_mode in number
71 )
72 is
73
74 l_region icx_regions%rowtype;
75 l_parent_region icx_regions%rowtype;
76 l_new_region_id number;
77
78 begin
79
80 -- Security Check
81
82 -- Get the record for the region to be split
83 select * into l_region
84 from icx_regions
85 where region_id = p_region_id;
86
87 -- If the region is already split in one direction then
88 -- do not change that
89 -- mbuk do we need this check.
90 -- if ( l_region.split_mode = REGION_HORIZONTAL_SPLIT and
91 -- p_split_mode = REGION_VERTICAL_SPLIT ) or
92 -- ( l_region.split_mode = REGION_VERTICAL_SPLIT and
93 -- p_split_mode = REGION_HORIZONTAL_SPLIT ) then
94
95 -- wwerr_api_error.add(wwerr_api_error.DOMAIN_WWC,
96 -- 'pob', 'reg_split',
97 -- 'wwpob_api_region.split_region');
98 -- raise REGION_VALIDATION_EXCEPTION;
99 --end if;
100
101
102 -- Check if it has a parent ( or is it the main region )
103 if l_region.parent_region_id = MAIN_REGION then
104
105 if l_region.split_mode = REGION_NOT_SPLIT then
106
107 select icx_regions_s.nextval into l_new_region_id from sys.dual;
108
109 insert into icx_regions
110 (
111 region_id,
112 parent_region_id,
113 split_mode,
114 portlet_flow,
115 border,
116 last_update_date,
117 last_updated_by,
118 creation_date,
119 created_by,
120 last_update_login
121 )
122 values
123 (
124 l_new_region_id,
125 l_region.region_id,
126 REGION_NOT_SPLIT,
127 l_region.portlet_flow,
128 l_region.border,
129 sysdate,
130 icx_sec.g_user_id,
131 sysdate,
132 icx_sec.g_user_id,
133 icx_sec.g_user_id
134 );
135
136 update icx_page_plugs
137 set region_id = l_new_region_id
138 where region_id = p_region_id;
139
140 end if;
141
142 select icx_regions_s.nextval into l_new_region_id from sys.dual;
143
144 insert into icx_regions
145 (
146 region_id,
147 parent_region_id,
148 split_mode,
149 portlet_flow,
150 border,
151 last_update_date,
152 last_updated_by,
153 creation_date,
154 created_by,
155 last_update_login
156 )
157 values
158 (
159 l_new_region_id,
160 l_region.region_id,
161 REGION_NOT_SPLIT,
162 l_region.portlet_flow,
163 l_region.border,
164 sysdate,
165 icx_sec.g_user_id,
166 sysdate,
167 icx_sec.g_user_id,
168 icx_sec.g_user_id
169 );
170
171 update icx_regions
172 set split_mode = p_split_mode
173 where region_id = l_region.region_id;
174
175 commit;
176
177 -- For all other regions
178 else
179
180 select * into l_parent_region
181 from icx_regions
182 where region_id = l_region.parent_region_id;
183
184 -- Check if the parent region was already split
185 if ( l_parent_region.split_mode = REGION_HORIZONTAL_SPLIT and p_split_mode = REGION_HORIZONTAL_SPLIT )
186 or ( l_parent_region.split_mode = REGION_VERTICAL_SPLIT and p_split_mode = REGION_VERTICAL_SPLIT ) then
187
188 select icx_regions_s.nextval into l_new_region_id from sys.dual;
189
190 -- Insert a row into icx_regions for the new region
191 insert into icx_regions
192 (
193 region_id,
194 parent_region_id,
195 split_mode,
196 portlet_flow,
197 border,
198 last_update_date,
199 last_updated_by,
200 creation_date,
201 created_by,
202 last_update_login
203 )
204 values
205 (
206 l_new_region_id,
207 l_region.parent_region_id,
208 REGION_NOT_SPLIT,
209 l_region.portlet_flow,
210 l_region.border,
211 sysdate,
212 icx_sec.g_user_id,
213 sysdate,
214 icx_sec.g_user_id,
215 icx_sec.g_user_id
216 );
217
218 -- don't want to move plug data from the existing region.
219 --update icx_page_plugs
220 -- set region_id = l_new_region_id
221 -- where region_id = p_region_id;
222
223 -- If the parent region was not split then update the flag in icx_regions for the parent
224 elsif p_split_mode = REGION_HORIZONTAL_SPLIT then
225
226 if l_region.split_mode = REGION_NOT_SPLIT then
227
228 select icx_regions_s.nextval into l_new_region_id from sys.dual;
229
230 insert into icx_regions
231 (
232 region_id,
233 parent_region_id,
234 split_mode,
235 portlet_flow,
236 border,
237 last_update_date,
238 last_updated_by,
239 creation_date,
240 created_by,
241 last_update_login
242 )
243 values
244 (
245 l_new_region_id,
246 p_region_id,
247 REGION_NOT_SPLIT,
248 l_region.portlet_flow,
249 l_region.border,
250 sysdate,
251 icx_sec.g_user_id,
252 sysdate,
253 icx_sec.g_user_id,
254 icx_sec.g_user_id
255 );
256
257 update icx_page_plugs
258 set region_id = l_new_region_id
259 where region_id = p_region_id;
260
261 end if;
262
263 insert into icx_regions
264 (
265 region_id,
266 parent_region_id,
267 split_mode,
268 portlet_flow,
269 border,
270 last_update_date,
271 last_updated_by,
272 creation_date,
273 created_by,
274 last_update_login
275 )
276 values
277 (
278 icx_regions_s.nextval,
279 p_region_id,
280 REGION_NOT_SPLIT,
281 l_region.portlet_flow,
282 l_region.border,
283 sysdate,
284 icx_sec.g_user_id,
285 sysdate,
286 icx_sec.g_user_id,
287 icx_sec.g_user_id
288 );
289
290 update icx_regions
291 set split_mode = REGION_HORIZONTAL_SPLIT
292 where region_id = p_region_id;
293
294
295 elsif p_split_mode = REGION_VERTICAL_SPLIT then
296
297 select icx_regions_s.nextval into l_new_region_id from sys.dual;
298
299 if l_region.split_mode = REGION_NOT_SPLIT then
300 insert into icx_regions
301 (
302 region_id,
303 parent_region_id,
304 split_mode,
305 portlet_flow,
306 border,
307 last_update_date,
308 last_updated_by,
309 creation_date,
310 created_by,
311 last_update_login
312 )
313 values
314 (
315 l_new_region_id,
316 p_region_id,
317 REGION_NOT_SPLIT,
318 l_region.portlet_flow,
319 l_region.border,
320 sysdate,
321 icx_sec.g_user_id,
322 sysdate,
323 icx_sec.g_user_id,
324 icx_sec.g_user_id
325 );
326
327 update icx_page_plugs
328 set region_id = l_new_region_id
329 where region_id = p_region_id;
330
331 end if;
332
333 insert into icx_regions
334 (
335 region_id,
336 parent_region_id,
337 split_mode,
338 portlet_flow,
339 border,
340 last_update_date,
341 last_updated_by,
342 creation_date,
343 created_by,
344 last_update_login
345 )
346 values
347 (
348 icx_regions_s.nextval,
349 p_region_id,
350 REGION_NOT_SPLIT,
351 l_region.portlet_flow,
352 l_region.border,
353 sysdate,
354 icx_sec.g_user_id,
355 sysdate,
356 icx_sec.g_user_id,
357 icx_sec.g_user_id
358 );
359
360 update icx_regions
361 set split_mode = REGION_VERTICAL_SPLIT
362 where region_id = p_region_id;
363
364 end if;
368 exception
365 commit;
366 end if;
367
369 when REGION_VALIDATION_EXCEPTION then
370 raise;
371 when NO_DATA_FOUND then
372 htp.p(SQLERRM);
373 raise;
374 when DUP_VAL_ON_INDEX then
375 rollback;
376 htp.p(SQLERRM);
377 raise;
378
379 when OTHERS then
380 rollback;
381 htp.p(SQLERRM);
382 raise;
383
384 end split_region;
385
386
387
388 procedure delete_region (
389 p_region_id in integer
390 )
391
392 is
393
394 l_count number := 0;
395 l_parent_region_id integer := p_region_id;
396 l_split_mode number := REGION_NOT_SPLIT;
397 l_region_sibling_id number := 0;
398
399 cursor child_regions is
400 select region_id
401 from icx_regions
402 where parent_region_id = p_region_id;
403
404 begin
405
406 -- get the parent for the region to be deleted
407 select parent_region_id
408 into l_parent_region_id
409 from icx_regions
410 where region_id = p_region_id;
411
412 -- Delete plugs, if any, associated with the children of the region being deleted
413 for region_record in child_regions loop
414 delete from icx_page_plugs
415 where region_id = region_record.region_id;
416 end loop;
417
418 -- Delete any child regions
419 delete from icx_regions
420 where parent_region_id = p_region_id;
421
422 -- delete plugs for the region to be deleted
423 delete from icx_page_plugs
424 where region_id = p_region_id;
425
426 -- When deleting a region, check if it is the only child of the parent
427 select count(region_id)
428 into l_count
429 from icx_regions
430 where parent_region_id = l_parent_region_id;
431
432 -- If it is the only region
433 if l_count = 1 then
434
435 -- Delete the region
436 delete from icx_regions
437 where region_id = p_region_id;
438
439 -- Update the parent splig flag
440 update icx_regions
441 set split_mode = REGION_NOT_SPLIT
442 where region_id = l_parent_region_id;
443
444
445 -- If the parent of the region being deleted has 2 regions, then
446 -- delete both of them, but only if the other one (sibling) is not split.
447 -- If the sibling region is to be delete then move the content of the
448 -- sibling region to the parent region.
449
450 elsif l_count = 2 then
451
452 select split_mode, region_id
453 into l_split_mode, l_region_sibling_id
454 from icx_regions
455 where parent_region_id = l_parent_region_id
456 and region_id <> p_region_id;
457
458 if l_split_mode = REGION_NOT_SPLIT then
459
460 -- Delete both the regions
461 delete from icx_regions
462 where parent_region_id = l_parent_region_id;
463
464 -- Update the parent split flag
465 update icx_regions
466 set split_mode = REGION_NOT_SPLIT
467 where region_id = l_parent_region_id;
468
469 -- if the sibling had any plugs in it then they should
470 -- be transferred to the parent.
471 update icx_page_plugs
472 set region_id = l_parent_region_id
473 where region_id = l_region_sibling_id;
474
475 else
476 -- Delete only the region
477 delete from icx_regions
478 where region_id = p_region_id;
479 end if;
480
481 else
482 -- Delete only the region
483 delete from icx_regions
484 where region_id = p_region_id;
485 end if;
486
487 commit;
488
489 exception
490 when REGION_VALIDATION_EXCEPTION then
491 raise;
492
493 when NO_DATA_FOUND then
494 htp.p(SQLERRM);
495 raise;
496
497 when OTHERS then
498 rollback;
499 htp.p(SQLERRM);
500 raise;
501
502 end delete_region;
503
504
505
506 function get_region (
507 p_region_id in integer
508 )
509 return region_record
510
511 is
512 l_region region_record;
513
514 begin
515
516 -- Security Check
517
518 select
519 region_id,
520 parent_region_id,
521 split_mode,
522 portlet_alignment,
523 height,
524 width,
525 width_restrict,
526 portlet_flow,
527 navwidget_id,
528 border
529 into
530 l_region.region_id,
531 l_region.parent_region_id,
532 l_region.split_mode,
533 l_region.portlet_alignment,
534 l_region.height,
535 l_region.width,
536 l_region.width_restrict,
537 l_region.portlet_flow,
538 l_region.navwidget_id,
539 l_region.border
540 from icx_regions
541 where region_id = p_region_id;
542
543 return l_region;
544
545 exception
546 when NO_DATA_FOUND then
547 return null;
548
549 end get_region;
550
551
552
553 function add_region (
557
554 p_region in region_record
555 )
556 return integer
558 is
559
560 l_region_id icx_regions.region_id%type;
561
562 begin
563
564 -- Security Check
565
566 -- Validate the record fields
567
568 -- Generate the region id
569 select icx_regions_s.nextval
570 into l_region_id
571 from sys.dual;
572
573 insert into icx_regions
574 (
575 region_id,
576 parent_region_id,
577 split_mode,
578 width,
579 height,
580 portlet_alignment,
581 width_restrict,
582 portlet_flow,
583 navwidget_id,
584 border,
585 last_update_date,
586 last_updated_by,
587 creation_date,
588 created_by,
589 last_update_login
590 )
591 values
592 (
593 l_region_id,
594 p_region.parent_region_id,
595 p_region.split_mode,
596 p_region.width,
597 p_region.height,
598 p_region.portlet_alignment,
599 p_region.width_restrict,
600 p_region.portlet_flow,
601 p_region.navwidget_id,
602 p_region.border,
603 sysdate,
604 icx_sec.g_user_id,
605 sysdate,
606 icx_sec.g_user_id,
607 icx_sec.g_user_id
608 );
609
610 commit;
611 return l_region_id;
612
613 exception
614 when DUP_VAL_ON_INDEX then
615 rollback;
616 htp.p(SQLERRM);
617 raise;
618
619 when OTHERS then
620 rollback;
621 htp.p(SQLERRM);
622 raise;
623
624 end add_region;
625
626
627
628 procedure edit_region (
629 p_region in region_record
630 )
631
632 is
633
634 begin
635
636 update icx_regions
637 set parent_region_id = p_region.parent_region_id,
638 split_mode = p_region.split_mode,
639 width = p_region.width,
640 height = p_region.height,
641 portlet_alignment = p_region.portlet_alignment,
642 width_restrict = p_region.width_restrict,
643 portlet_flow = 0, -- we are not using p_region.portlet_flow,
644 navwidget_id = p_region.navwidget_id,
645 border = p_region.border
646 where region_id = p_region.region_id;
647
648 -- mbuk. Do we need to add this back?
649 --if sql%rowcount = 0 then
650 -- wwerr_api_error.add(wwerr_api_error.DOMAIN_WWC,
651 -- 'pob', 'reg_notfound',
652 -- 'wwpob_api_region.edit_region');
653 -- raise REGION_VALIDATION_EXCEPTION;
654 --end if;
655
656 commit;
657
658 end edit_region;
659
660
661
662 function get_child_region_list (
663 p_region_id in integer
664 )
665 return region_table
666
667 is
668
669 l_region_list region_table;
670 l_index number := 0;
671
672 begin
673
674 -- Security Check
675
676 for x in (select * from icx_regions where parent_region_id = p_region_id) loop
677
678 l_index := l_index + 1;
679
680 l_region_list(l_index).region_id := x.region_id;
681 l_region_list(l_index).parent_region_id := x.parent_region_id;
682 l_region_list(l_index).split_mode := x.split_mode;
683 l_region_list(l_index).portlet_alignment := x.portlet_alignment;
684 l_region_list(l_index).height := x.height;
685 l_region_list(l_index).width := x.width;
686 l_region_list(l_index).width_restrict := x.width_restrict;
687 l_region_list(l_index).portlet_flow := x.portlet_flow;
688 l_region_list(l_index).border := x.border;
689
690 end loop;
691
692 return l_region_list;
693
694 exception
695
696 when OTHERS then
697 htp.p(SQLERRM);
698 raise;
699
700 end get_child_region_list;
701
702
703
704 procedure delete_regions (
705 p_layout_id in integer
706 )
707
708 is
709
710 l_count number := 0;
711
712 begin
713
714 null;
715 --**** Need to completely rewrite this.
716 -- Need this for when a page is deleted. mbuk
717
718 -- Security Check
719
720 -- Check if the page exists
721 --if not wwpob_api_layout.is_layout(p_layout_id) then
722 -- wwerr_api_error.add(wwerr_api_error.DOMAIN_WWC,
723 -- 'pob', 'lay_notfound',
724 -- 'wwpob_api_region.delete_regions');
725 -- raise REGION_VALIDATION_EXCEPTION;
726 --end if;
727
728 --for x in (select * from icx_regions where region_id = p_page_id )
729 --loop
730 -- Delete the translations for strings
731 -- l_count := wwnls_api.remove_string(x.title_id, TRUE);
732 --end loop;
733
734 -- Delete all regions for a layout
735 --delete from icx_regions
736 --where layout_id = p_layout_id;
737
738 --commit;
739
740 -- exception
741 -- when REGION_VALIDATION_EXCEPTION then
742 -- raise;
743
744 end delete_regions;
748 -- select region_id
745
746
747 --procedure get_region_list (p_region_id) is
749 -- from icx_regions
750 -- start with region_id = p_region_id
751 -- connect by prior region_id = parent_region_id;
752
753 procedure copy_region_plugs (p_from_region_id in number,
754 p_to_region_id in number,
755 p_to_page_id in number)
756 is
757
758 l_plug_id number;
759 cursor plugs_to_be_copied is
760 select *
761 from icx_page_plugs
762 where region_id = p_from_region_id;
763
764 begin
765
766 for thisplug in plugs_to_be_copied loop
767
768 select icx_page_plugs_s.nextval into l_plug_id from dual;
769
770 insert into ICX_PAGE_PLUGS
771 (PLUG_ID,
772 PAGE_ID,
773 DISPLAY_SEQUENCE,
774 RESPONSIBILITY_APPLICATION_ID,
775 SECURITY_GROUP_ID,
776 RESPONSIBILITY_ID,
777 MENU_ID,
778 ENTRY_SEQUENCE,
779 DISPLAY_NAME,
780 REGION_ID,
781 last_update_date,
782 last_updated_by,
783 creation_date,
784 created_by,
785 last_update_login)
786 values
787 (l_plug_id,
788 p_to_page_id,
789 thisplug.DISPLAY_SEQUENCE,
790 thisplug.RESPONSIBILITY_APPLICATION_ID,
791 thisplug.SECURITY_GROUP_ID,
792 thisplug.RESPONSIBILITY_ID,
793 thisplug.MENU_ID,
794 thisplug.ENTRY_SEQUENCE,
795 thisplug.DISPLAY_NAME,
796 p_to_region_id,
797 sysdate,
798 icx_sec.g_user_id,
799 sysdate,
800 icx_sec.g_user_id,
801 icx_sec.g_user_id);
802 end loop;
803
804 exception
805 when others then
806 htp.p(SQLERRM);
807 end;
808
809
810 procedure copy_child_regions (
811 p_from_region_id in number
812 , p_to_region_id in number
813 , p_to_page_id in number
814 )
815
816 is
817
818 l_region_id icx_regions.region_id%type;
819 l_region icx_regions%rowtype;
820
821 begin
822
823 -- Security
824
825 for x in (select * from icx_regions where parent_region_id = p_from_region_id) loop
826
827 select * into l_region
828 from icx_regions
829 where region_id = p_to_region_id;
830
831 -- Insert a new record and copy the attributes
832 select icx_regions_s.nextval
833 into l_region_id
834 from sys.dual;
835
836 insert into icx_regions
837 (
838 region_id,
839 parent_region_id,
840 split_mode,
841 width,
842 height,
843 portlet_alignment,
844 width_restrict,
845 portlet_flow,
846 navwidget_id,
847 border,
848 last_update_date,
849 last_updated_by,
850 creation_date,
851 created_by,
852 last_update_login
853 )
854 values
855 (
856 l_region_id,
857 l_region.region_id,
858 x.split_mode,
859 x.width,
860 x.height,
861 x.portlet_alignment,
862 x.width_restrict,
863 x.portlet_flow,
864 x.navwidget_id,
865 x.border,
866 sysdate,
867 icx_sec.g_user_id,
868 sysdate,
869 icx_sec.g_user_id,
870 icx_sec.g_user_id
871 );
872
873 copy_child_regions(x.region_id, l_region_id, p_to_page_id);
874
875 end loop;
876
877 copy_region_plugs(p_from_region_id, p_to_region_id, p_to_page_id);
878
879 commit;
880
881 exception
882 when REGION_VALIDATION_EXCEPTION then
883 raise;
884
885 when NO_DATA_FOUND then
886 htp.p(SQLERRM);
887 raise;
888
889 when DUP_VAL_ON_INDEX then
890 rollback;
891 htp.p(SQLERRM);
892 raise;
893
894 when OTHERS then
895 rollback;
896 htp.p(SQLERRM);
897 raise;
898
899 end copy_child_regions;
900
901 /**************************************************************************
902 GET_MAIN_REGION
903 ***************************************************************************/
904
905 function get_main_region_record (
906 p_region_id in integer
907 )
908 return icx_api_region.region_record
909
910 is
911
912 l_region icx_api_region.region_record;
913
914 begin
915
916 select
917 region_id,
918 parent_region_id,
919 split_mode,
920 portlet_alignment,
921 height,
922 width,
923 width_restrict,
924 portlet_flow,
925 navwidget_id,
926 border
927 into
928 l_region.region_id,
929 l_region.parent_region_id,
930 l_region.split_mode,
931 l_region.portlet_alignment,
932 l_region.height,
933 l_region.width,
934 l_region.width_restrict,
935 l_region.portlet_flow,
936 l_region.navwidget_id,
937 l_region.border
938 from icx_regions
939 where region_id = p_region_id;
940
941 return l_region;
942
943 exception
944 when others then
945 htp.p(SQLERRM);
946 end get_main_region_record;
947
948
949 end ICX_API_REGION;