1 PACKAGE BODY per_ri_workbench_utility as
2 /* $Header: perriwbu.pkb 120.0.12010000.2 2008/11/28 15:19:09 sbrahmad ship $ */
3
4 g_package VARCHAR2(33) := ' per_ri_workbench_utility.';
5
6 --------------------------------------------------------------------
7 -- This function returns to image name for the workbench_items which are
8 -- FUNCTIONAL_AREA
9 --------------------------------------------------------------------
10
11 FUNCTION get_go_to_task_image_name(p_workbench_item_code in varchar2,
12 p_workbench_item_type in varchar2)
13 return varchar2 IS
14
15 l_proc varchar2(72) := g_package || 'get_wb_item_status';
16 l_setu_task_status varchar2(60);
17
18 BEGIN
19 hr_utility.set_location('Entering:' || l_proc,10);
20
21 if p_workbench_item_type = 'FUNCTIONAL_AREA' then
22 return '/OA_MEDIA/takeaction_enabled.gif';
23 else
24 return NULL;
25 end if;
26 hr_utility.set_location('Leaving:' || l_proc,20);
27
28 EXCEPTION
29 when others then
30 hr_utility.set_location(l_proc,30);
31 fnd_message.raise_error;
32 END;
33
34 --------------------------------------------------------------------
35 -- This function returns to status of the workbench items based upon the
36 -- status of the tasks of the workbench items.
37 --
38 -- If any task is 'In Progress' then the main status will also be
39 -- In Progress'.
40 -- If any task is 'Complete' and no task is 'In Progress' then the
41 -- main status will be 'Complete'.
42 -- If all tasks are 'Not Applicable' then the main status will also
43 -- be 'Not Applicable'.
44 -- If all tasks are 'Not Started' then the main status will also
45 -- be 'Not Started'
46 --
47 --------------------------------------------------------------------
48 FUNCTION get_item_status_name (p_workbench_item_code in varchar2,
49 p_workbench_item_type in varchar2)
50 return varchar2 IS
51
52 CURSOR csr_setup_task_status (p_workbench_item_code varchar2,
53 p_workbench_item_type varchar2) IS
54 SELECT DISTINCT setup_task_status
55 FROM per_ri_setup_tasks
56 WHERE workbench_item_code = p_workbench_item_code
57 ORDER BY setup_task_status;
58
59 l_proc varchar2(72) := g_package || 'get_item_status_name';
60 l_setup_task_status varchar2(60);
61 l_workbench_item_status varchar2(60);
62 l_workbench_item_status_image varchar2(60);
63
64 l_complete varchar2(1):= 'N';
65 l_in_progress varchar2(1):= 'N';
66 l_not_started varchar2(1):= 'N';
67 l_not_applicable varchar2(1):= 'N';
68
69 BEGIN
70 hr_utility.set_location('Entering:' || l_proc,10);
71
72 --------------------------------------------------------------------
73 -- If any task is 'In Progress' then the main status will also be
74 -- In Progress'.
75 -- If any task is 'Complete' and no task is 'In Progress' then the
76 -- main status will be 'Complete'.
77 -- If all tasks are 'Not Applicable' then the main status will also
78 -- be 'Not Applicable'.
79 -- If all tasks are 'Not Started' then the main status will also
80 -- be 'No activity'
81 --------------------------------------------------------------------
82 if p_workbench_item_type = 'FUNCTIONAL_AREA' then
83
84 -- loop for all rows returned for SQL statement
85 FOR l_rec in csr_setup_task_status(p_workbench_item_code,
86 p_workbench_item_type)
87 LOOP
88 l_setup_task_status := nvl(l_rec.setup_task_status,'NOT_APPLICABLE');
89 if l_setup_task_status = 'COMPLETE' then
90 l_complete := 'Y';
91 elsif l_setup_task_status = 'IN_PROGRESS' then
92 l_in_progress := 'Y';
93 elsif l_setup_task_status = 'NOT_STARTED' then
94 l_not_started := 'Y';
95 elsif l_setup_task_status = 'NOT_APPLICABLE' then
96 l_not_applicable := 'Y';
97 else
98 l_not_applicable := 'Y';
99 end if;
100 END LOOP;
101 if (l_complete = 'Y' and
102 l_in_progress = 'N' and
103 l_not_started = 'N' and
104 (l_not_applicable = 'N' OR l_not_applicable = 'Y')) then
105 l_workbench_item_status_image := '/OA_MEDIA/completeind_status.gif';
106 elsif (l_complete = 'N' and
107 l_in_progress = 'Y' and
108 l_not_started = 'N' and
109 (l_not_applicable = 'N' OR l_not_applicable = 'Y')) then
110 l_workbench_item_status_image := '/OA_MEDIA/inprogressind_status.gif';
111 elsif (l_complete = 'N' and
112 l_in_progress = 'N' and
113 l_not_started = 'Y' and
114 (l_not_applicable = 'N' OR l_not_applicable = 'Y')) then
115 l_workbench_item_status_image := '/OA_MEDIA/notstartedind_status.gif';
116 elsif (l_complete = 'N' and
117 l_in_progress = 'N' and
118 l_not_started = 'N' and
119 (l_not_applicable = 'N' OR l_not_applicable = 'Y')) then
120 l_workbench_item_status_image := '/OA_MEDIA/notapplicableind_status.gif';
121 -- If any task is 'In Progress' then the main status
122 -- will also be 'In Progress'
123 elsif (l_in_progress = 'Y' and
124 (l_in_progress = 'N' OR l_in_progress = 'Y') and
125 (l_not_started = 'N' OR l_not_started = 'Y') and
126 (l_not_applicable = 'N'OR l_not_applicable = 'Y')) then
127 l_workbench_item_status_image := '/OA_MEDIA/inprogressind_status.gif';
128 elsif (l_complete = 'Y' and
129 l_in_progress = 'Y' and
130 (l_not_applicable = 'N' OR l_not_applicable = 'Y')) then
131 l_workbench_item_status_image := '/OA_MEDIA/inprogressind_status.gif';
132 elsif (l_complete = 'Y' and
133 l_not_started = 'Y') then
134 l_workbench_item_status_image := '/OA_MEDIA/inprogressind_status.gif';
135 else
136 l_workbench_item_status_image := '/OA_MEDIA/notapplicableind_status.gif';
137
138 end if;
139 hr_utility.set_location('Leaving:' || l_proc,20);
140 return l_workbench_item_status_image;
141 else
142 hr_utility.set_location('Leaving:' || l_proc,30);
143 return null;
144 end if;
145 EXCEPTION
146 when others then
147 hr_utility.set_location(l_proc,40);
148 fnd_message.raise_error;
149 END;
150
151 function get_item_notes_image (p_workbench_item_code in varchar2
152 ,p_workbench_item_type in varchar2)
153 return varchar2 IS
154
155 l_proc varchar2(72) := g_package || 'get_wb_item_status';
156 l_item_status varchar2(60);
157
158 BEGIN
159 hr_utility.set_location('Entering:' || l_proc,10);
160
161 if p_workbench_item_type = 'FUNCTIONAL_AREA' then
162 return '/OA_MEDIA/attachments_toggleattach.gif';
163 else
164 return NULL;
165 end if;
166 EXCEPTION
167 when others then
168 hr_utility.set_location(l_proc,30);
169 fnd_message.raise_error;
170 END get_item_notes_image;
171
172 ----
173
174 function get_item_last_modified_date (p_workbench_item_code in varchar2)
175 return varchar2 IS
176
177 CURSOR csr_modified_date (p_workbench_item_code varchar2) IS
178 SELECT max(setup_task_last_modified_date)
179 FROM per_ri_setup_tasks
180 WHERE workbench_item_code = p_workbench_item_code;
181
182 l_proc varchar2(72) := g_package || 'get_item_last_modified_date';
183 l_item_last_modified_date varchar2(60);
184
185 BEGIN
186 hr_utility.set_location('Entering:' || l_proc,10);
187
188 open csr_modified_date(p_workbench_item_code);
189 fetch csr_modified_date into l_item_last_modified_date;
190 close csr_modified_date;
191 if l_item_last_modified_date is null then
192 return null;
193 else
194 return l_item_last_modified_date;
195 end if;
196 END get_item_last_modified_date;
197 ----
198
199 --------------------------------------------------------------------------
200 -- This function checks whether the function name passed is
201 -- attached to the current responsibility
202 -- The function expects either 'Per_Ri_Workbench_Items.WorkbenchItemCode'
203 -- or 'Per_RI_Setup_tasks.Setup_task_code' as function name and
204 -- item type (whether Workbench Item or Setup Task Item) as inputs
205 --------------------------------------------------------------------------
206
207
208 function workbench_task_access_exist(fname varchar2,itemType number) return number is
209 function_name varchar2(100);
210 function_present boolean;
211 begin
212 function_name := fname;
213 if itemType = 1 then
214 function_name := 'W_' || fname ;
215 else
216 function_name := 'S_' || fname ;
217 end if;
218
219 function_present := fnd_function.test(function_name , 'N');
220 if function_present then
221 return 1;
222 else
223 return 0;
224 end if;
225 end workbench_task_access_exist;
226
227
228 END per_ri_workbench_utility;
229