1 PACKAGE BODY hr_form_stack_canvas_info
2 /* $Header: hrfscinf.pkb 120.0 2005/05/31 00:27:55 appldev noship $ */
3 AS
4 --
5 -- Global variables
6 --
7 g_application_id fnd_application.application_id%TYPE;
8 g_form_id fnd_form.form_id%TYPE;
9 g_form_stack_canvases t_form_stack_canvases := t_form_stack_canvases();
10 --
11 -- -----------------------------------------------------------------------------
12 -- |--------------------------< form_stack_canvases >--------------------------|
13 -- -----------------------------------------------------------------------------
14 FUNCTION form_stack_canvases
15 (p_application_id IN fnd_application.application_id%TYPE
16 ,p_form_id IN fnd_form.form_id%TYPE
17 )
18 RETURN t_form_stack_canvases
19 IS
20 --
21 CURSOR csr_form_stack_canvases
22 (p_application_id IN fnd_application.application_id%TYPE
23 ,p_form_id IN fnd_form.form_id%TYPE
24 )
25 IS
26 SELECT fsc.form_tab_stacked_canvas_id form_stack_canvas_id
27 ,fc1.canvas_name
28 ,ftp.tab_page_name
29 ,fc2.canvas_name stack_canvas_name
30 FROM hr_form_canvases_b fc2
31 ,hr_form_tab_stacked_canvases fsc
32 ,hr_form_tab_pages_b ftp
33 ,hr_form_canvases_b fc1
34 ,hr_form_windows_b fwn
35 WHERE fc2.form_canvas_id = fsc.form_canvas_id
36 AND fsc.form_tab_page_id = ftp.form_tab_page_id
37 AND ftp.form_canvas_id = fc1.form_canvas_id
38 AND fc1.form_window_id = fwn.form_window_id
39 AND fwn.application_id = p_application_id
40 AND fwn.form_id = p_form_id;
41 --
42 l_form_stack_canvases t_form_stack_canvases := t_form_stack_canvases();
43 --
44 BEGIN
45 --
46 IF ( p_application_id = nvl(g_application_id,hr_api.g_number)
47 AND p_form_id = nvl(g_form_id,hr_api.g_number))
48 THEN
49 --
50 -- The form stacked canvases have already been found with a previous call
51 -- to this function. Just return the global variable.
52 --
53 l_form_stack_canvases := g_form_stack_canvases;
54 --
55 ELSE
56 --
57 -- The identifiers are different to the previous call to this function, or
58 -- this is the first call to this function.
59 --
60 FOR l_form_stack_canvas IN csr_form_stack_canvases
61 (p_application_id => p_application_id
62 ,p_form_id => p_form_id
63 )
64 LOOP
65 l_form_stack_canvases.EXTEND;
66 l_form_stack_canvases(l_form_stack_canvases.LAST) := l_form_stack_canvas;
67 END LOOP;
68 --
69 -- Set the global variables so the values are available to the next call to
70 -- the function.
71 --
72 g_application_id := p_application_id;
73 g_form_id := p_form_id;
74 g_form_stack_canvases := l_form_stack_canvases;
75 --
76 END IF;
77 --
78 RETURN(l_form_stack_canvases);
79 --
80 END form_stack_canvases;
81 --
82 END hr_form_stack_canvas_info;