[Home] [Help]
PACKAGE BODY: APPS.PQH_ROUTING_CATEGORIES_API
Source
1 Package Body pqh_ROUTING_CATEGORIES_api as
2 /* $Header: pqrctapi.pkb 115.11 2002/12/06 18:07:26 rpasapul noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' pqh_ROUTING_CATEGORIES_api.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |------------------------< create_ROUTING_CATEGORY >----------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 procedure create_ROUTING_CATEGORY
13 (p_validate in boolean default false
14 ,p_routing_category_id out nocopy number
15 ,p_transaction_category_id in number default null
16 ,p_enable_flag in varchar2 default 'Y'
17 ,p_default_flag in varchar2 default null
18 ,p_delete_flag in varchar2 default null
19 ,p_routing_list_id in number default null
20 ,p_position_structure_id in number default null
21 ,p_override_position_id in number default null
22 ,p_override_assignment_id in number default null
23 ,p_override_role_id in number default null
24 ,p_override_user_id in number default null
25 ,p_object_version_number out nocopy number
26 ,p_effective_date in date
27 ) is
28 --
29 -- Declare cursors and local variables
30 --
31 l_routing_category_id pqh_routing_categories.routing_category_id%TYPE;
32 l_proc varchar2(72) := g_package||'create_ROUTING_CATEGORY';
33 l_object_version_number pqh_routing_categories.object_version_number%TYPE;
34 --
35 begin
36 --
37 hr_utility.set_location('Entering:'|| l_proc, 10);
38 --
39 -- Issue a savepoint if operating in validation only mode
40 --
41 savepoint create_ROUTING_CATEGORY;
42 --
43 hr_utility.set_location(l_proc, 20);
44 --
45 -- Process Logic
46 --
47 begin
48 --
49 -- Start of API User Hook for the before hook of create_ROUTING_CATEGORY
50 --
51 pqh_ROUTING_CATEGORIES_bk1.create_ROUTING_CATEGORY_b
52 (
53 p_transaction_category_id => p_transaction_category_id
54 ,p_enable_flag => p_enable_flag
55 ,p_default_flag => p_default_flag
56 ,p_delete_flag => p_delete_flag
57 ,p_routing_list_id => p_routing_list_id
58 ,p_position_structure_id => p_position_structure_id
59 ,p_override_position_id => p_override_position_id
60 ,p_override_assignment_id => p_override_assignment_id
61 ,p_override_role_id => p_override_role_id
62 ,p_override_user_id => p_override_user_id
63 ,p_effective_date => trunc(p_effective_date)
64 );
65 exception
66 when hr_api.cannot_find_prog_unit then
67 hr_api.cannot_find_prog_unit_error
68 (
69 p_module_name => 'CREATE_ROUTING_CATEGORY'
70 ,p_hook_type => 'BP'
71 );
72 --
73 -- End of API User Hook for the before hook of create_ROUTING_CATEGORY
74 --
75 end;
76 --
77 pqh_rct_ins.ins
78 (
79 p_routing_category_id => l_routing_category_id
83 ,p_delete_flag => p_delete_flag
80 ,p_transaction_category_id => p_transaction_category_id
81 ,p_enable_flag => p_enable_flag
82 ,p_default_flag => p_default_flag
84 ,p_routing_list_id => p_routing_list_id
85 ,p_position_structure_id => p_position_structure_id
86 ,p_override_position_id => p_override_position_id
87 ,p_override_assignment_id => p_override_assignment_id
88 ,p_override_role_id => p_override_role_id
89 ,p_override_user_id => p_override_user_id
90 ,p_object_version_number => l_object_version_number
91 ,p_effective_date => trunc(p_effective_date)
92 );
93 --
94 begin
95 --
96 -- Start of API User Hook for the after hook of create_ROUTING_CATEGORY
97 --
98 pqh_ROUTING_CATEGORIES_bk1.create_ROUTING_CATEGORY_a
99 (
100 p_routing_category_id => l_routing_category_id
101 ,p_transaction_category_id => p_transaction_category_id
102 ,p_enable_flag => p_enable_flag
103 ,p_default_flag => p_default_flag
104 ,p_delete_flag => p_delete_flag
105 ,p_routing_list_id => p_routing_list_id
106 ,p_position_structure_id => p_position_structure_id
107 ,p_override_position_id => p_override_position_id
108 ,p_override_assignment_id => p_override_assignment_id
109 ,p_override_role_id => p_override_role_id
110 ,p_override_user_id => p_override_user_id
111 ,p_object_version_number => l_object_version_number
112 ,p_effective_date => trunc(p_effective_date)
113 );
114 exception
115 when hr_api.cannot_find_prog_unit then
116 hr_api.cannot_find_prog_unit_error
117 (p_module_name => 'CREATE_ROUTING_CATEGORY'
118 ,p_hook_type => 'AP'
119 );
120 --
121 -- End of API User Hook for the after hook of create_ROUTING_CATEGORY
122 --
123 end;
124 --
125 hr_utility.set_location(l_proc, 60);
126 --
127 -- When in validation only mode raise the Validate_Enabled exception
128 --
129 if p_validate then
130 raise hr_api.validate_enabled;
131 end if;
132 --
133 -- Set all output arguments
134 --
135 p_routing_category_id := l_routing_category_id;
136 p_object_version_number := l_object_version_number;
137 --
138 hr_utility.set_location(' Leaving:'||l_proc, 70);
139 --
140 exception
141 --
142 when hr_api.validate_enabled then
143 --
144 -- As the Validate_Enabled exception has been raised
145 -- we must rollback to the savepoint
146 --
147 ROLLBACK TO create_ROUTING_CATEGORY;
148 --
149 -- Only set output warning arguments
150 -- (Any key or derived arguments must be set to null
151 -- when validation only mode is being used.)
152 --
153 p_routing_category_id := null;
154 p_object_version_number := null;
155 hr_utility.set_location(' Leaving:'||l_proc, 80);
156 --
157 when others then
158 p_routing_category_id := null;
159 p_object_version_number := null;
160 --
161 -- A validation or unexpected error has occured
162 --
163 ROLLBACK TO create_ROUTING_CATEGORY;
164 raise;
165 --
166 end create_ROUTING_CATEGORY;
167 -- ----------------------------------------------------------------------------
168 -- |------------------------< update_ROUTING_CATEGORY >--- ------------------|
169 -- ----------------------------------------------------------------------------
170 --
171 procedure update_ROUTING_CATEGORY
172 (p_validate in boolean default false
173 ,p_routing_category_id in number
174 ,p_transaction_category_id in number default hr_api.g_number
175 ,p_enable_flag in varchar2 default hr_api.g_varchar2
176 ,p_default_flag in varchar2 default hr_api.g_varchar2
177 ,p_delete_flag in varchar2 default hr_api.g_varchar2
178 ,p_routing_list_id in number default hr_api.g_number
179 ,p_position_structure_id in number default hr_api.g_number
180 ,p_override_position_id in number default hr_api.g_number
181 ,p_override_assignment_id in number default hr_api.g_number
182 ,p_override_role_id in number default hr_api.g_number
183 ,p_override_user_id in number default hr_api.g_number
184 ,p_object_version_number in out nocopy number
185 ,p_effective_date in date
186
187 ) is
188 --
189 -- Declare cursors and local variables
190 --
191 l_proc varchar2(72) := g_package||'update_ROUTING_CATEGORY';
192 l_object_version_number pqh_routing_categories.object_version_number%TYPE;
193 --
194 begin
195 --
196 hr_utility.set_location('Entering:'|| l_proc, 10);
197 --
198 -- Issue a savepoint if operating in validation only mode
199 --
200 savepoint update_ROUTING_CATEGORY;
201 --
202 hr_utility.set_location(l_proc, 20);
203 --
204 -- Process Logic
205 --
206 l_object_version_number := p_object_version_number;
207 --
208 begin
209 --
210 -- Start of API User Hook for the before hook of update_ROUTING_CATEGORY
211 --
212 pqh_ROUTING_CATEGORIES_bk2.update_ROUTING_CATEGORY_b
213 (
214 p_routing_category_id => p_routing_category_id
215 ,p_transaction_category_id => p_transaction_category_id
216 ,p_enable_flag => p_enable_flag
217 ,p_default_flag => p_default_flag
218 ,p_delete_flag => p_delete_flag
219 ,p_routing_list_id => p_routing_list_id
220 ,p_position_structure_id => p_position_structure_id
221 ,p_override_position_id => p_override_position_id
222 ,p_override_assignment_id => p_override_assignment_id
223 ,p_override_role_id => p_override_role_id
224 ,p_override_user_id => p_override_user_id
225 ,p_object_version_number => p_object_version_number
226 ,p_effective_date => trunc(p_effective_date)
227
228 );
229 exception
230 when hr_api.cannot_find_prog_unit then
231 hr_api.cannot_find_prog_unit_error
232 (p_module_name => 'UPDATE_ROUTING_CATEGORY'
233 ,p_hook_type => 'BP'
234 );
235 --
236 -- End of API User Hook for the before hook of update_ROUTING_CATEGORY
237 --
238 end;
239 --
240 pqh_rct_upd.upd
241 (
242 p_routing_category_id => p_routing_category_id
243 ,p_transaction_category_id => p_transaction_category_id
244 ,p_enable_flag => p_enable_flag
245 ,p_default_flag => p_default_flag
246 ,p_delete_flag => p_delete_flag
247 ,p_routing_list_id => p_routing_list_id
248 ,p_position_structure_id => p_position_structure_id
249 ,p_override_position_id => p_override_position_id
250 ,p_override_assignment_id => p_override_assignment_id
251 ,p_override_role_id => p_override_role_id
252 ,p_override_user_id => p_override_user_id
253 ,p_object_version_number => l_object_version_number
254 ,p_effective_date => trunc(p_effective_date)
255
256 );
257 --
258 begin
259 --
260 -- Start of API User Hook for the after hook of update_ROUTING_CATEGORY
261 --
262 pqh_ROUTING_CATEGORIES_bk2.update_ROUTING_CATEGORY_a
263 (
264 p_routing_category_id => p_routing_category_id
265 ,p_transaction_category_id => p_transaction_category_id
266 ,p_enable_flag => p_enable_flag
267 ,p_default_flag => p_default_flag
268 ,p_delete_flag => p_delete_flag
269 ,p_routing_list_id => p_routing_list_id
270 ,p_position_structure_id => p_position_structure_id
271 ,p_override_position_id => p_override_position_id
272 ,p_override_assignment_id => p_override_assignment_id
273 ,p_override_role_id => p_override_role_id
274 ,p_override_user_id => p_override_user_id
275 ,p_object_version_number => l_object_version_number
276 ,p_effective_date => trunc(p_effective_date)
277
278 );
279 exception
280 when hr_api.cannot_find_prog_unit then
281 hr_api.cannot_find_prog_unit_error
282 (p_module_name => 'UPDATE_ROUTING_CATEGORY'
283 ,p_hook_type => 'AP'
284 );
285 --
286 -- End of API User Hook for the after hook of update_ROUTING_CATEGORY
287 --
288 end;
289 --
290 hr_utility.set_location(l_proc, 60);
291 --
292 -- When in validation only mode raise the Validate_Enabled exception
293 --
294 if p_validate then
295 raise hr_api.validate_enabled;
296 end if;
297 --
298 -- Set all output arguments
299 --
300 p_object_version_number := l_object_version_number;
301 --
302 hr_utility.set_location(' Leaving:'||l_proc, 70);
303 --
304 exception
305 --
306 when hr_api.validate_enabled then
307 --
308 -- As the Validate_Enabled exception has been raised
309 -- we must rollback to the savepoint
310 --
311 ROLLBACK TO update_ROUTING_CATEGORY;
312 --
313 -- Only set output warning arguments
314 -- (Any key or derived arguments must be set to null
315 -- when validation only mode is being used.)
316 --
317 hr_utility.set_location(' Leaving:'||l_proc, 80);
318 --
319 when others then
320 p_object_version_number := l_object_version_number;
321 --
322 -- A validation or unexpected error has occured
323 --
324 ROLLBACK TO update_ROUTING_CATEGORY;
325 raise;
326 --
327 end update_ROUTING_CATEGORY;
328 -- ----------------------------------------------------------------------------
332 procedure delete_ROUTING_CATEGORY
329 -- |------------------------< delete_ROUTING_CATEGORY >----------------------|
330 -- ----------------------------------------------------------------------------
331 --
333 (p_validate in boolean default false
334 ,p_routing_category_id in number
335 ,p_object_version_number in number
336 ,p_effective_date in date
337
338 ) is
339 --
340 -- Declare cursors and local variables
341 --
342 l_proc varchar2(72) := g_package||'delete_ROUTING_CATEGORY';
343 l_object_version_number pqh_routing_categories.object_version_number%TYPE;
344 --
345 begin
346 --
347 hr_utility.set_location('Entering:'|| l_proc, 10);
348 --
349 -- Issue a savepoint if operating in validation only mode
350 --
351 savepoint delete_ROUTING_CATEGORY;
352 --
353 hr_utility.set_location(l_proc, 20);
354 --
355 -- Process Logic
356 --
357 l_object_version_number := p_object_version_number;
358 --
359 --
360 begin
361 --
362 -- Start of API User Hook for the before hook of delete_ROUTING_CATEGORY
363 --
364 pqh_ROUTING_CATEGORIES_bk3.delete_ROUTING_CATEGORY_b
365 (
366 p_routing_category_id => p_routing_category_id
367 ,p_object_version_number => p_object_version_number
368 ,p_effective_date => trunc(p_effective_date)
369
370 );
371 exception
372 when hr_api.cannot_find_prog_unit then
373 hr_api.cannot_find_prog_unit_error
374 (p_module_name => 'DELETE_ROUTING_CATEGORY'
375 ,p_hook_type => 'BP'
376 );
377 --
378 -- End of API User Hook for the before hook of delete_ROUTING_CATEGORY
379 --
380 end;
381 --
382 pqh_rct_del.del
383 (
384 p_routing_category_id => p_routing_category_id
385 ,p_object_version_number => l_object_version_number
386 ,p_effective_date => trunc(p_effective_date)
387
388 );
389 --
390 begin
391 --
392 -- Start of API User Hook for the after hook of delete_ROUTING_CATEGORY
393 --
394 pqh_ROUTING_CATEGORIES_bk3.delete_ROUTING_CATEGORY_a
395 (
396 p_routing_category_id => p_routing_category_id
397 ,p_object_version_number => l_object_version_number
398 ,p_effective_date => trunc(p_effective_date)
399
400 );
401 exception
402 when hr_api.cannot_find_prog_unit then
403 hr_api.cannot_find_prog_unit_error
404 (p_module_name => 'DELETE_ROUTING_CATEGORY'
405 ,p_hook_type => 'AP'
406 );
407 --
408 -- End of API User Hook for the after hook of delete_ROUTING_CATEGORY
409 --
410 end;
411 --
412 hr_utility.set_location(l_proc, 60);
413 --
414 -- When in validation only mode raise the Validate_Enabled exception
415 --
416 if p_validate then
417 raise hr_api.validate_enabled;
418 end if;
419 --
420 hr_utility.set_location(' Leaving:'||l_proc, 70);
421 --
422 exception
423 --
424 when hr_api.validate_enabled then
425 --
426 -- As the Validate_Enabled exception has been raised
427 -- we must rollback to the savepoint
428 --
429 ROLLBACK TO delete_ROUTING_CATEGORY;
430 --
431 -- Only set output warning arguments
432 -- (Any key or derived arguments must be set to null
433 -- when validation only mode is being used.)
434 --
435 --
436 when others then
437 --
438 -- A validation or unexpected error has occured
439 --
440 ROLLBACK TO delete_ROUTING_CATEGORY;
441 raise;
442 --
443 end delete_ROUTING_CATEGORY;
444 --
445 -- ----------------------------------------------------------------------------
446 -- |-------------------------------< lck >------------------------------------|
447 -- ----------------------------------------------------------------------------
448 --
449 procedure lck
450 (
451 p_routing_category_id in number
452 ,p_object_version_number in number
453 ) is
454 --
455 --
456 -- Declare cursors and local variables
457 --
458 l_proc varchar2(72) := g_package||'lck';
459 --
460 begin
461 --
462 hr_utility.set_location('Entering:'|| l_proc, 10);
463 --
464 pqh_rct_shd.lck
465 (
466 p_routing_category_id => p_routing_category_id
467 ,p_object_version_number => p_object_version_number
468 );
469 --
470 hr_utility.set_location(' Leaving:'||l_proc, 70);
471 --
472 end lck;
473 --
474 ------------------------------------------------------------------------------
475 --
476 Procedure disable_routing_categories
477 (p_transaction_category_id in pqh_transaction_categories.transaction_category_id%type,
478 p_routing_type in pqh_transaction_categories.member_cd%type) is
479 --
480 -- Declare cursors and local variables
481 --
482 type cur_type IS REF CURSOR;
483 rct_cur cur_type;
484 sql_stmt varchar2(1000);
485 --
486 l_routing_category_id pqh_routing_categories.routing_category_id%type;
487 l_object_version_number pqh_routing_categories.object_version_number%type;
488 --
489 l_proc varchar2(72) := g_package||'disable_routing_categories';
490 --
491 Begin
492 --
493 hr_utility.set_location('Entering:'|| l_proc, 10);
494 --
495 sql_stmt := 'Select routing_category_id,object_version_number from pqh_routing_categories where transaction_category_id = :t';
496 --
497 -- Select only the routing categories that belong to the current routing
498 -- type of the transaction category.
499 --
500 If p_routing_type = 'R' then
501 --
502 sql_stmt := sql_stmt ||' and routing_list_id is not null';
503 --
504 Elsif p_routing_type = 'P' then
505 --
506 sql_stmt := sql_stmt ||' and position_structure_id is not null';
507 --
508 Else
509 --
510 sql_stmt := sql_stmt ||' and routing_list_id is null and position_structure_id is null';
511 --
512 End if;
513 --
514 Open rct_cur for sql_stmt using p_transaction_category_id;
515 --
516 loop
517 --
518 Fetch rct_cur into l_routing_category_id,l_object_version_number;
519 --
520 If rct_cur%notfound then
521 exit;
522 End if;
523 --
524 -- set all the selected routing categories to disabled.
525 --
526 pqh_ROUTING_CATEGORIES_api.update_ROUTING_CATEGORY
527 (p_validate => false
528 ,p_routing_category_id => l_routing_category_id
529 ,p_enable_flag => 'N'
530 ,p_object_version_number => l_object_version_number
531 ,p_effective_date => sysdate
532 );
533 --
534 End loop;
535 --
536 hr_utility.set_location('Leaving:'|| l_proc, 70);
537 --
538 End;
539 --
540 --
541 end pqh_ROUTING_CATEGORIES_api;