1 PACKAGE BODY hxc_proj_manager_approval_pkg AS
2 /* $Header: hxcpamgrapr.pkb 120.6.12000000.1 2007/01/18 17:54:43 appldev noship $ */
3
4 g_debug boolean := hr_utility.debug_enabled;
5
6 cursor g_csr_get_approval_style
7 is
8 select approval_style_id from HXC_APPROVAL_STYLES where name ='SEEDED_APPL_PA_MGR';
9
10
11 -- Getting the Time recepient id for Projects application (APPLICATION_ID = 275 )
12
13 cursor csr_get_recipient_id
14 is
15 select TIME_RECIPIENT_ID from HXC_TIME_RECIPIENTs where APPLICATION_ID = 275 ;
16
17 cursor csr_get_ela_approval_comp(l_approval_style_id in number)
18 is
19 select APPROVAL_COMP_ID, OBJECT_VERSION_NUMBER from hxc_approval_comps where APPROVAL_MECHANISM = 'ENTRY_LEVEL_APPROVAL' and APPROVAL_STYLE_ID = l_approval_style_id;
20
21 --bug 4671272
22 cursor csr_get_flex_value_set_id
23 is
24 select FLEX_VALUE_SET_ID from fnd_flex_value_sets where flex_value_set_name = 'HXC_ALL_PROJECTS';
25
26 g_update_flag NUMBER;
27
28 /* The many unused parameters were meant for the concurrent program previously planned */
29
30 PROCEDURE create_time_cat(p_project_id in number,
31 p_approval_style_id in number,
32 p_parent_comp_id in number,
33 p_parent_object_version_number in number,
34 p_default_approval_mechanism in varchar2 default null,
35 p_mechanism_id in number default null ,
36 p_wf_name in varchar2 default null,
37 p_wf_item_type varchar2 default null,
38 p_manager_id out NOCOPY number )
39 is
40 cursor csr_find_time_category_comp(l_project_id in number)
41 is
42 select time_category_comp_id, object_version_number, time_category_id
43 from hxc_time_category_comps
44 where value_id = to_char(l_project_id)
45 and TIME_CATEGORY_ID IN ( SELECT TIME_CATEGORY_ID FROM HXC_TIME_CATEGORIES
46 WHERE TIME_CATEGORY_NAME LIKE 'PROJECT MANAGER :: % SYSTEM GENERATED DO NOT MODIFY' );
47
48
49
50 cursor csr_get_time_category_name(l_time_category_name hxc_time_categories.TIME_CATEGORY_NAME%TYPE)
51 is
52 select time_category_id from hxc_time_categories where time_category_name = l_time_category_name;
53
54
55 cursor csr_get_approval_mechanism( l_approval_style_id in number, l_time_category_id in number)
56 is
57 select APPROVAL_MECHANISM, APPROVAL_MECHANISM_ID, APPROVAL_COMP_ID, OBJECT_VERSION_NUMBER , WF_NAME , WF_ITEM_TYPE from hxc_approval_comps where
58 APPROVAL_STYLE_ID = l_approval_style_id and TIME_CATEGORY_ID = l_time_category_id
59 and OBJECT_VERSION_NUMBER = ( select max( OBJECT_VERSION_NUMBER ) from hxc_approval_comps where
60 APPROVAL_STYLE_ID = l_approval_style_id and TIME_CATEGORY_ID = l_time_category_id);
61
62
63 CURSOR csr_get_map_comp_id
64 IS
65 SELECT mapping_component_id
66 FROM hxc_mapping_components_v
67 WHERE bld_blk_info_type = 'PROJECTS'
68 AND field_name = 'Project_Id';
69
70
71 l_wf_name VARCHAR2(100);
72 l_wf_item_type VARCHAR2(100);
73
74 L_mapping_component_id NUMBER;
75
76 l_project_id number;
77 l_approval_style_id number;
78 l_parent_comp_id number;
79 l_parent_object_version_number number;
80
81 l_object_version_number NUMBER;
82 l_object_version_number_old NUMBER;
83 L_APPROVAL_COMP_ID NUMBER;
84 l_time_category_id NUMBER;
85 l_time_category_comp_id_old NUMBER;
86 l_time_category_id_old NUMBER;
87 l_manager_id NUMBER;
88 l_time_category_comp_id NUMBER;
89 l_flex_value_set_id NUMBER;
90
91
92 l_approval_mechanism VARCHAR2(100);
93 l_mechanism_id NUMBER;
94 l_def_approval_mech VARCHAR2(100);
95 l_def_approval_mech_id NUMBER;
96 l_dyn_sql VARCHAR2(2000);
97 l_time_category_name hxc_time_categories.TIME_CATEGORY_NAME%TYPE;
98
99
100 l_proc varchar2(100);
101 begin
102
103 g_debug := hr_utility.debug_enabled;
104
105 if g_debug then
106 l_proc := 'hxc_proj_manager_approval_pkg.create_tim_cat';
107 hr_utility.set_location(l_proc, 10);
108 end if;
109
110 l_project_id := p_project_id;
111 l_approval_style_id := p_approval_style_id;
112 l_parent_comp_id := p_parent_comp_id;
113 l_parent_object_version_number := p_parent_object_version_number ;
114
115
116 l_approval_mechanism := 'PERSON';
117
118 /* get the Manager id of the project */
119
120 l_dyn_sql := 'BEGIN '|| fnd_global.newline
121 || ':1 := Pa_Otc_Api.GetProjectManager' ||fnd_global.newline
122 ||'(p_project_id => :2);' ||fnd_global.newline
123 ||'END;';
124
125 EXECUTE IMMEDIATE l_dyn_sql
126 using OUT l_manager_id, IN l_project_id;
127
128 --- l_mechanism_id := l_manager_id;
129
130 /* if the manager id is null then the project will go into a time category for all such left out entries .We are choosing the value -1 for manager id when it is null */
131 IF l_manager_id is NOT NULL
132 THEN
133
134 /*
135 if g_debug then
136 hr_utility.set_location(l_proc, 20);
137 end if;
138 l_manager_id := -1;
139 IF p_default_approval_mechanism IS NOT NULL
140 THEN
141 l_approval_mechanism := p_default_approval_mechanism ;
142 ELSE
143 l_approval_mechanism := 'HR_SUPERVISOR';
144 END IF;
145
146 l_mechanism_id := p_mechanism_id;
147
148 END IF; */
149
150 p_manager_id := l_manager_id; -- setting the OUT parameter
151 l_mechanism_id := l_manager_id;
152
153
154 l_time_category_name := 'PROJECT MANAGER :: ' || l_manager_id || ' SYSTEM GENERATED DO NOT MODIFY';
155
156
157 /* Passing the time category name we check whether the time category already exists. If it exists then gets the time category id */
158
159 OPEN csr_get_time_category_name(l_time_category_name);
160 FETCH csr_get_time_category_name into l_time_category_id;
161
162 if csr_get_time_category_name%NOTFOUND
163 then
164 if g_debug then
165 hr_utility.set_location(l_proc, 30);
166 end if;
167
168 l_object_version_number := 0;
169
170
171 /* As the time category does not exist we are creating a new Time category */
172
173 hxc_time_category_api.create_time_category(
174 p_time_category_id => l_time_category_id
175 ,p_object_version_number => l_object_version_number
176 ,p_time_category_name => l_time_category_name
177 ,p_operator => 'OR'
178 ,p_description => 'Created for Project Manager Approval'
179 ,p_display => 'N' );
180
181
182 ----------------------- Creating Approval Component for each Time Category-----------------+
183 /* As a new Time Category has been created we also need to create a corresponding Approval Component with the just created Time Category */
184
185
186 hxc_approval_comps_api.create_approval_comps(
187 p_approval_mechanism => l_approval_mechanism,
188 p_approval_mechanism_id => l_mechanism_id,
189 p_approval_style_id => l_approval_style_id,
190 p_time_recipient_id => -1,
191 p_approval_order => 10,
192 p_approval_comp_id => l_approval_comp_id,
193 p_object_version_number => l_object_version_number,
194 p_wf_item_type => p_wf_item_type ,
195 p_wf_name => p_wf_name,
196 p_start_date => SYSDATE,
197 p_end_date => hr_general.end_of_time,
198 p_time_category_id => l_time_category_id,
199 p_parent_comp_id => l_parent_comp_id,
200 p_parent_comp_ovn => l_parent_object_version_number
201 );
202
203 /*
204
205 -- The following code is commented out as it was originally added for updating the default approval mechanism selected through the Concurrent program
206
207
208 ELSE
209
210 -- If the Time Category consists of Left out entries( projects without managers ) then we need
211 --- to make sure the approval mechanism for the left out entries has not changed. So if
212 -- the new approval mechanism is not null and is also different from the already existing
213 -- approval mechanism then we need to update the approval component for the left out entries
214
215
216 if g_debug then
217 hr_utility.set_location(l_proc, 40);
218 end if;
219
220
221 IF l_time_category_name = 'PROJECT MANAGER :: -1 SYSTEM GENERATED DO NOT MODIFY' and p_default_approval_mechanism IS NOT NULL and g_update_flag = 0
222 THEN
223 if g_debug then
224 hr_utility.set_location(l_proc, 50);
225 end if;
226
227
228 -- Getting the approval mechanism and approval mechanism id of the approval component for left out entries
229
230 OPEN csr_get_approval_mechanism( l_approval_style_id, l_time_category_id );
231 FETCH csr_get_approval_mechanism INTO l_def_approval_mech , l_def_approval_mech_id, l_approval_comp_id, l_object_version_number, l_wf_name, l_wf_item_type;
232
233
234
235 -- checking if the presently specified approval mechanism and mechanism id match with those already present
236
237 IF l_def_approval_mech <> l_approval_mechanism
238 THEN
239 g_update_flag := 1;
240 ELSE
241 IF l_approval_mechanism = 'WORKFLOW'
242 THEN
243
244 IF l_wf_name <> p_wf_name OR l_wf_item_type <> p_wf_item_type
245 THEN
246 g_update_flag := 1;
247 END IF;
248
249 ELSE
250 IF l_def_approval_mech_id <> l_mechanism_id
251 THEN
252 g_update_flag := 1;
253 END IF;
254 END IF; -- end if for workflow check
255 END IF;
256
257 -- As the mechanism or mechanism id has changed update the approval component
258 ---- update
259
260 IF g_update_flag = 1
261 THEN
262 if g_debug then
263 hr_utility.set_location(l_proc, 60);
264 end if;
265
266 hxc_approval_comps_api.update_approval_comps
267 (
268 p_approval_comp_id => l_approval_comp_id
269 ,p_object_version_number => l_object_version_number
270 ,p_approval_mechanism => l_approval_mechanism
271 ,p_approval_mechanism_id => l_mechanism_id
272 ,p_time_recipient_id => -1
273 ,p_approval_style_id => l_approval_style_id
274 ,p_wf_item_type => p_wf_item_type
275 ,p_wf_name => p_wf_name
276 ,p_start_date => sysdate
277 ,p_end_date => hr_general.end_of_time
278 ,p_time_category_id => l_time_category_id
279 ,p_parent_comp_id => l_parent_comp_id
280 ,p_parent_comp_ovn => l_parent_object_version_number );
281
282 END IF;
283 CLOSE csr_get_approval_mechanism;
284 if g_debug then
285 hr_utility.set_location(l_proc, 70);
286 end if;
287 END IF;
288
289 */
290 end if; ----- if not found loop
291 CLOSE csr_get_time_category_name ;
292 ELSE
293 l_time_category_id := -99; --- Manager id is NULL, so make it -99
294 END IF;
295
296
297 l_object_version_number := 0;
298 /* get the flex_value_set_id of the value set HXC_ALL_PROJECTS_ID */
299
300 OPEN csr_get_flex_value_set_id;
301 FETCH csr_get_flex_value_set_id into l_flex_value_set_id;
302 CLOSE csr_get_flex_value_set_id;
303
304 /* get the time category id, time category comp id and ovn of the record with specified project id
305 ---- and flex value set id. We are basically trying to check whether the project is present in
306 ---- the correct time category or any change in project set up has happened. If the cursor does
307 ---- not fetch any values then it means that the project is not present in any time category and
308 ---- we need to create a new time category component with the project. On the other hand if the cursor
309 ---- fetches some values and they do not match the present time category then we need to remove the
310 ------ project from the old time category and add it to the new time category */
311
312 OPEN csr_find_time_category_comp(l_project_id);
313 FETCH csr_find_time_category_comp into l_time_category_comp_id_old,l_object_version_number_old,l_time_category_id_old;
314
315
316
317 IF l_time_category_id_old <> l_time_category_id OR csr_find_time_category_comp%NOTFOUND
318 THEN
319 if g_debug then
320 hr_utility.set_location(l_proc, 80);
321 end if;
322
323 IF csr_find_time_category_comp%FOUND
324 THEN
325 if g_debug then
326 hr_utility.set_location(l_proc, 90);
327 end if;
328
329 /* The project set-up has changed and so we need to delete previous time cat comp and then add new one */
330 -- dele
331
332 hxc_time_category_comp_api.delete_time_category_comp
333 ( p_time_category_comp_id => l_time_category_comp_id_old
334 ,p_object_version_number => l_object_version_number_old
335 );
336
337 --
338 -- Clear the time category cache, so that the new component
339 -- is used properly - bug 5469357
340 --
341 if hxc_time_category_utils_pkg.reset_cache then
342 null;
343 end if;
344
345 END IF;
346 if g_debug then
347 hr_utility.set_location(l_proc, 100);
348 end if;
349
350
351
352 IF l_time_category_id <> -99
353 THEN
354 /* Creating a new time category comp */
355 -- neo
356 OPEN csr_get_map_comp_id;
357 FETCH csr_get_map_comp_id INTO L_mapping_component_id ;
358 CLOSE csr_get_map_comp_id;
359
360 hxc_time_category_comp_api.create_time_category_comp
361 ( p_validate => FALSE
362 ,p_time_category_comp_id => l_time_category_comp_id
363 ,p_object_version_number => l_object_version_number
364 ,p_ref_time_category_id => null
365 ,p_time_category_id => l_time_category_id
366 ,p_component_type_id => L_mapping_component_id
367 ,p_flex_value_set_id => l_flex_value_set_id
368 ,p_value_id => l_project_id
369 ,p_is_null => 'Y'
370 ,p_equal_to => 'Y'
371 ,p_type => 'MC'
372 );
373 --
374 -- Clear the time category cache, so that the new component
375 -- is used properly - bug 5469357
376 --
377 if hxc_time_category_utils_pkg.reset_cache then
378 null;
379 end if;
380
381 END IF; -- l_time_category_id <> -99
382
383 END IF;
384 CLOSE csr_find_time_category_comp;
385
386
387
388 end create_time_cat;
389
390
391
392
393
394
395
396 PROCEDURE replace_projman_by_spl_ela( p_tab_project_id in out NOCOPY tab_project_id ,
397 p_new_spl_ela_style_id out NOCOPY number
398 )
399 is
400 l_project_id NUMBER;
401 l_approval_style_id NUMBER;
402 l_approval_comp_id NUMBER;
403 l_object_version_number NUMBER;
404 l_index NUMBER;
405 l_time_recipient_id NUMBER;
406 l_parent_comp_id NUMBER;
407 l_parent_object_version_number NUMBER;
408
409 l_approval_comp_id_1 NUMBER;
410 l_object_version_number_1 NUMBER;
411 l_manager_id NUMBER;
412
413 l_proc varchar2(100);
414 begin
415
416 g_debug := hr_utility.debug_enabled;
417
418 if g_debug then
419 l_proc := 'hxc_proj_manager_approval_pkg.replace_by_spl_ela';
420 hr_utility.set_location(l_proc, 10);
421 end if;
422
423 open csr_get_recipient_id;
424 Fetch csr_get_recipient_id into l_time_recipient_id;
425 close csr_get_recipient_id;
426
427 /* Checking if the standard Special ELA Style already exists. If it exists it returns the approval_style_id of the Special ELA Approval Style */
428
429 OPEN g_csr_get_approval_style;
430 FETCH g_csr_get_approval_style into l_approval_style_id;
431
432 IF g_csr_get_approval_style%NOTFOUND
433 THEN
434 if g_debug then
435 hr_utility.set_location(l_proc, 20);
436 end if;
437
438
439 hxc_approval_styles_api.create_approval_styles(
440 p_name =>'SEEDED_APPL_PA_MGR',
441 p_approval_style_id => l_approval_style_id,
442 p_object_version_number => l_object_version_number);
443
444 /* Creating the ENTRY_LEVEL_APPROVAL approval component for the above created Special ELA Approval Style */
445 hxc_approval_comps_api.create_approval_comps(
446 p_approval_mechanism => 'ENTRY_LEVEL_APPROVAL',
447 p_approval_style_id => l_approval_style_id,
448 p_time_recipient_id => l_time_recipient_id,
449 p_approval_order => 10,
450 p_approval_comp_id => l_approval_comp_id,
451 p_object_version_number => l_object_version_number,
452 p_start_date => SYSDATE,
453 p_end_date => hr_general.end_of_time
454 );
455
456 l_parent_comp_id := l_approval_comp_id ;
457 l_parent_object_version_number := l_object_version_number;
458
459 /* Creating the mandatory default approval style for ELA, */
460
461 hxc_approval_comps_api.create_approval_comps(
462 p_approval_mechanism => 'HR_SUPERVISOR',
463 p_approval_style_id => l_approval_style_id,
464 p_time_recipient_id => -1,
465 p_approval_order => 10,
466 p_approval_comp_id => l_approval_comp_id_1,
467 p_object_version_number => l_object_version_number_1,
468 p_start_date => SYSDATE,
469 p_end_date => hr_general.end_of_time,
470 p_time_category_id => 0,
471 p_parent_comp_id => l_parent_comp_id,
472 p_parent_comp_ovn => l_parent_object_version_number
473 );
474
475 ELSE
476 /* If the special ELA approval style exists then get the ENTRY_LEVEL_APPROVAL approval component id associated with the special ELA approval style */
477
478 if g_debug then
479 hr_utility.set_location(l_proc, 30);
480 end if;
481
482
483 OPEN csr_get_ela_approval_comp(l_approval_style_id);
484 FETCH csr_get_ela_approval_comp INTO l_approval_comp_id , l_object_version_number;
485 CLOSE csr_get_ela_approval_comp;
486 END IF;
487 CLOSE g_csr_get_approval_style;
488 ---- putting value into OUT parameter
489 p_new_spl_ela_style_id := l_approval_style_id;
490
491
492
493 /* Looping through all the projects in the input parameter p_tab_project_id */
494
495 l_index := p_tab_project_id.first;
496 WHILE l_index IS NOT NULL LOOP
497
498 l_project_id := p_tab_project_id( l_index ).project_id;
499
500 /* Call the create_time_cat procedure to do further processing */
501 create_time_cat( p_project_id => l_project_id,
502 p_approval_style_id => l_approval_style_id,
503 p_parent_comp_id => l_approval_comp_id,
504 p_parent_object_version_number => l_object_version_number,
505 p_manager_id => l_manager_id );
506
507 p_tab_project_id( l_index ).manager_id := l_manager_id;
508
509
510 l_index := p_tab_project_id.next( l_index );
511 END LOOP;
512 if g_debug then
513 hr_utility.set_location(l_proc, 40);
514 end if;
515
516 commit;
517
518 end replace_projman_by_spl_ela;
519
520 end hxc_proj_manager_approval_pkg;
[Home] [Help]