[Home] [Help]
PACKAGE BODY: APPS.HR_WIP_TXNS
Source
1 PACKAGE BODY hr_wip_txns
2 /* $Header: hrwiptxn.pkb 120.1 2005/06/28 02:33:11 cnholmes noship $ */
3 AS
4 g_start_state VARCHAR2(5) := 'START';
5 g_save_for_later_state VARCHAR2(14) := 'SAVE_FOR_LATER';
6 g_pending_approval_state VARCHAR2(16) := 'PENDING_APPROVAL';
7 g_reject_for_correction_state VARCHAR2(21) := 'REJECT_FOR_CORRECTION';
8 g_manual_sub_state VARCHAR2(6) := 'MANUAL';
9 g_automatic_sub_state VARCHAR2(9) := 'AUTO';
10 g_query_only_dml_mode VARCHAR2(10) := 'QUERY_ONLY';
11 g_insert_dml_mode VARCHAR2(6) := 'INSERT';
12 g_update_dml_mode VARCHAR2(6) := 'UPDATE';
13 g_delete_dml_mode VARCHAR2(6) := 'DELETE';
14 --
15 -- ----------------------------------------------------------------------------
16 -- |-----------------------< chk_item_type_item_key_mand >---------------------|
17 -- ----------------------------------------------------------------------------
18 Procedure chk_item_type_item_key_mand
19 (p_item_type in wf_items.item_type%TYPE,
20 p_item_key in wf_items.item_key%TYPE)
21 is
22 begin
23 --
24 If p_item_type is null
25 then
26 fnd_message.set_name('PER','PER_289658_TXN_ITEM_TYPE_MND');
27 fnd_message.raise_error;
28 ElsIf p_item_key is null
29 then
30 fnd_message.set_name('PER','PER_289659_TXN_ITEM_KEY_MND');
31 fnd_message.raise_error;
32 End if;
33 end chk_item_type_item_key_mand;
34 --
35 -- ----------------------------------------------------------------------------
36 -- |-----------------------< chk_item_type_item_key_exists >-------------------|
37 -- ----------------------------------------------------------------------------
38 procedure chk_item_type_item_key_exists
39 (p_item_type IN wf_items.item_type%TYPE,
40 p_item_key IN wf_items.item_key%TYPE)
41 is
42 --
43 l_end_date wf_items.end_date%TYPE;
44 --
45 cursor csr_item_type_exists is
46 select end_date
47 from wf_items
48 where item_type = p_item_type
49 and item_key = p_item_key;
50 --
51 begin
52 open csr_item_type_exists;
53 fetch csr_item_type_exists into l_end_date;
54 If csr_item_type_exists%notfound
55 then
56 close csr_item_type_exists;
57 fnd_message.set_name('PER','PER_289660_TXN_INV_WFL');
58 fnd_message.raise_error;
59 End if;
60 close csr_item_type_exists;
61 If l_end_date is not null
62 then
63 fnd_message.set_name('PER','PER_289661_TXN_WFL_END');
64 fnd_message.raise_error;
65 End if;
66 exception
67 when others then
68 If csr_item_type_exists%isopen
69 then
70 close csr_item_type_exists;
71 end if;
72 raise;
73 end chk_item_type_item_key_exists;
74 --
75 -- ----------------------------------------------------------------------------
76 -- |-----------------------< chk_creator_user_id >-----------------------|
80 is
77 -- ----------------------------------------------------------------------------
78 Procedure chk_creator_user_id
79 (p_creator_user_id in fnd_user.user_id%TYPE)
81 --
82 l_end_date fnd_user.end_date%TYPE;
83 cursor csr_creator_user_id is
84 select end_date
85 from fnd_user
86 where user_id = p_creator_user_id;
87 --
88 begin
89 If p_creator_user_id is null
90 then
91 fnd_message.set_name('PER','PER_289662_TXN_INV_CREATOR');
92 fnd_message.raise_error;
93 End if;
94 open csr_creator_user_id;
95 fetch csr_creator_user_id into l_end_date;
96 If csr_creator_user_id%notfound
97 then
98 close csr_creator_user_id;
99 fnd_message.set_name('PER','PER_289663_TXN_INV_USER');
100 fnd_message.raise_error;
101 End if;
102 close csr_creator_user_id;
103 If ((l_end_date is not null) and (l_end_date < sysdate))
104 then
105 fnd_message.set_name('PER','PER_289664_TXN_USER_END');
106 fnd_message.raise_error;
107 End if;
108 exception
109 when others then
110 If csr_creator_user_id%isopen
111 then
112 close csr_creator_user_id;
113 end if;
114 raise;
115 End chk_creator_user_id;
116 --
117 -- ----------------------------------------------------------------------------
118 -- |-----------------------< chk_function_id >-----------------------|
119 -- ----------------------------------------------------------------------------
120 Procedure chk_function_id
121 (p_function_id in fnd_form_functions.function_id%TYPE)
122 is
123 l_function_id varchar2(1);
124 cursor csr_function_id is
125 select null
126 from fnd_form_functions
127 where function_id = p_function_id;
128 begin
129 If p_function_id is not null
130 then
131 open csr_function_id;
132 fetch csr_function_id into l_function_id;
133 If csr_function_id%notfound
134 then
135 close csr_function_id;
136 fnd_message.set_name('PER','PER_289665_TXN_INV_FORM');
137 fnd_message.raise_error;
138 End if;
139 close csr_function_id;
140 Else
141 fnd_message.set_name('PER','PER_289666_TXN_FORM_MND');
142 fnd_message.raise_error;
143 End if;
144 exception
145 when others then
146 If csr_function_id%isopen
147 then
148 close csr_function_id;
149 end if;
150 raise;
151 end chk_function_id;
152 --
153 -- ----------------------------------------------------------------------------
154 -- |-----------------------< chk_transaction_creator >-----------------------|
155 -- ----------------------------------------------------------------------------
156 Procedure chk_transaction_creator(p_creator_user_id in fnd_user.user_id%TYPE
157 ,p_current_user_id in fnd_user.user_id%TYPE
158 )is
159 begin
160 If p_creator_user_id <> p_current_user_id
161 then
162 fnd_message.set_name('PER','PER_289667_TXN_MOD_CREATOR');
163 fnd_message.raise_error;
164 End if;
165 end chk_transaction_creator;
166 --
167 -- ----------------------------------------------------------------------------
168 -- |-----------------------< chk_state >-------------------------------|
169 -- ----------------------------------------------------------------------------
170 Procedure chk_state
171 (p_state IN hr_wip_transactions.state%TYPE
172 )is
173 begin
174 If p_state not in (g_start_state,g_save_for_later_state
175 ,g_pending_approval_state,g_reject_for_correction_state)
176 then
177 fnd_message.set_name('PER','PER_289676_TXN_INV_STATE');
178 fnd_message.raise_error;
179 End if;
180 end chk_state;
181 --
182 -- ----------------------------------------------------------------------------
183 -- |-----------------------< chk_sub_state >-----------------------|
184 -- ----------------------------------------------------------------------------
185 Procedure chk_sub_state
186 (p_state IN hr_wip_transactions.state%TYPE
187 ,p_sub_state IN hr_wip_transactions.sub_state%TYPE)is
188 begin
189 If (p_state <> g_save_for_later_state and p_sub_state is not null)
190 then
191 fnd_message.set_name('PER','PER_289668_TXN_INV_SUBSTATE');
192 fnd_message.raise_error;
193 Elsif (p_state = g_save_for_later_state and nvl(p_sub_state,-1)
194 not in (g_automatic_sub_state,g_manual_sub_state))
195 then
196 fnd_message.set_name('PER','PER_289669_TXN_SUBSTATE_VAL');
197 fnd_message.raise_error;
198 End if;
199 end chk_sub_state;
200 --
201 -- ----------------------------------------------------------------------------
202 -- |-----------------------< get_and_chk_and_return_dml_mode >----------------|
203 -- ----------------------------------------------------------------------------
204 Function get_and_chk_and_ret_dml_mode
205 (p_new_dml_mode IN hr_wip_transactions.dml_mode%TYPE
206 ,p_old_dml_mode IN hr_wip_transactions.dml_mode%TYPE)
207 RETURN hr_wip_transactions.dml_mode%TYPE is
208 begin
209 If p_new_dml_mode = hr_api.g_varchar2 THEN
210 RETURN(p_old_dml_mode);
211 end if;
212 -- a new dml_mode has been specified
213 -- check the dml_mode
214 If p_old_dml_mode = g_query_only_dml_mode
215 then
216 -- the current transaction is in QUERY_ONLY mode
217 If p_new_dml_mode <> g_query_only_dml_mode
218 then
219 fnd_message.set_name('PER','PER_289681_TXN_DML_QUERY_MODE');
223 end if;
220 fnd_message.raise_error;
221 else
222 RETURN(g_query_only_dml_mode);
224 end if;
225 -- set the dml_mode
226 -- check to ensure that the dml_mode has a valid value
227 IF p_new_dml_mode = g_query_only_dml_mode OR
228 p_new_dml_mode = g_insert_dml_mode OR
229 p_new_dml_mode = g_update_dml_mode OR
230 p_new_dml_mode = g_delete_dml_mode
231 then
232 -- dml_mode was specified
233 RETURN(p_new_dml_mode);
234 else
235 fnd_message.set_name('PER','PER_289682_TXN_DML_INV_MODE');
236 fnd_message.raise_error;
237 end if;
238 end get_and_chk_and_ret_dml_mode;
239 --
240 -- ----------------------------------------------------------------------------
241 -- |-----------------------< get_context_display_text >-----------------------|
242 -- ----------------------------------------------------------------------------
243 Function get_context_display_text
244 (p_new_context_display_text IN hr_wip_transactions.context_display_text%TYPE
245 ,p_old_context_display_text IN hr_wip_transactions.context_display_text%TYPE)
246 RETURN hr_wip_transactions.context_display_text%TYPE IS
247 begin
248 -- set the correct context_display_text
249 If p_new_context_display_text = hr_api.g_varchar2
250 then
251 -- the context_display_text was not specified
252 RETURN(p_old_context_display_text);
253 else
254 -- context_display_text was specified
255 RETURN(p_new_context_display_text);
256 END IF;
257 end get_context_display_text;
258 -- ----------------------------------------------------------------------------
259 -- |-----------------------< ins >-----------------------|
260 -- ----------------------------------------------------------------------------
261 Function ins
262 (p_item_type IN wf_items.item_type%TYPE
263 ,p_item_key IN wf_items.item_key%TYPE
264 ,p_function_id IN fnd_form_functions.function_id%TYPE
265 ,p_creator_user_id IN fnd_user.user_id%TYPE
266 ,p_dml_mode IN hr_wip_transactions.dml_mode%TYPE
267 ,p_vo_xml IN VARCHAR2
268 ,p_context_display_text IN hr_wip_transactions.context_display_text%TYPE
269 ) RETURN hr_wip_transactions.transaction_id%TYPE is
270 --
271 l_tran_id hr_wip_transactions.transaction_id%TYPE;
272 --
273 begin
274 chk_item_type_item_key_mand(p_item_type => p_item_type
275 ,p_item_key => p_item_key
276 );
277 chk_item_type_item_key_exists
278 (p_item_type => p_item_type
279 ,p_item_key => p_item_key
280 );
281 chk_creator_user_id(p_creator_user_id => p_creator_user_id);
282 chk_function_id(p_function_id => p_function_id);
283 insert into hr_wip_transactions(transaction_id
284 ,creator_user_id
285 ,item_type
286 ,item_key
287 ,function_id
288 ,state
289 ,sub_state
290 ,vo_cache
291 ,context_display_text
292 ,dml_mode
293 )
294 values(hr_wip_transactions_s.nextval
295 ,p_creator_user_id
296 ,p_item_type
297 ,p_item_key
298 ,p_function_id
299 ,g_start_state
300 ,null
301 ,p_vo_xml
302 ,p_context_display_text
303 ,p_dml_mode
304 )returning transaction_id into l_tran_id;
305 return l_tran_id;
306 exception
307 when dup_val_on_index then
308 fnd_message.set_name('PER','PER_289670_TXN_WFL_EXIST');
309 fnd_message.raise_error;
310 when others then
311 raise;
312 end ins;
313 --
314 -- ----------------------------------------------------------------------------
315 -- |-----------------------< upd >-----------------------|
316 -- ----------------------------------------------------------------------------
317 Procedure upd
318 (p_transaction_id IN hr_wip_transactions.transaction_id%TYPE
319 ,p_state IN hr_wip_transactions.state%TYPE
320 ,p_sub_state IN hr_wip_transactions.sub_state%TYPE
321 ,p_vo_xml IN VARCHAR2
322 ,p_context_display_text IN hr_wip_transactions.context_display_text%TYPE
323 default hr_api.g_varchar2
324 ,p_dml_mode IN hr_wip_transactions.dml_mode%TYPE
325 default hr_api.g_varchar2
326 )is
327 --
328 begin
329 update hr_wip_transactions
330 set state = p_state
331 ,sub_state = p_sub_state
332 ,vo_cache = p_vo_xml
333 ,context_display_text = p_context_display_text
334 ,dml_mode = p_dml_mode
335 where transaction_id = p_transaction_id;
336 end upd;
337 -- ----------------------------------------------------------------------------
338 -- |-----------------------< create_transaction >-----------------------|
339 -- ----------------------------------------------------------------------------
340 Function create_transaction
341 (p_item_type IN wf_items.item_type%TYPE
342 ,p_item_key IN wf_items.item_key%TYPE
346 ,p_vo_xml IN VARCHAR2
343 ,p_function_id IN fnd_form_functions.function_id%TYPE
344 ,p_creator_user_id IN fnd_user.user_id%TYPE
345 ,p_dml_mode IN hr_wip_transactions.dml_mode%TYPE
347 ,p_context_display_text IN hr_wip_transactions.context_display_text%TYPE
348 ) RETURN hr_wip_transactions.transaction_id%TYPE
349 is
350 PRAGMA AUTONOMOUS_TRANSACTION;
351 l_transaction_id number;
352 begin
353 l_transaction_id := ins
354 (p_item_type => p_item_type
355 ,p_item_key => p_item_key
356 ,p_function_id => p_function_id
357 ,p_creator_user_id => p_creator_user_id
358 ,p_dml_mode => p_dml_mode
359 ,p_vo_xml => p_vo_xml
360 ,p_context_display_text => p_context_display_text
361 );
362 commit;
363 return l_transaction_id;
364 end create_transaction;
365 --
366 --
367 -- ----------------------------------------------------------------------------
368 -- |-----------------------< create_transaction >-----------------------|
369 -- ----------------------------------------------------------------------------
370 Procedure create_transaction
371 (p_item_type IN wf_items.item_type%TYPE
372 ,p_item_key IN wf_items.item_key%TYPE
373 ,p_function_id IN fnd_form_functions.function_id%TYPE
374 ,p_creator_user_id IN fnd_user.user_id%TYPE
375 ,p_dml_mode IN hr_wip_transactions.dml_mode%TYPE
376 ,p_vo_xml IN VARCHAR2
377 ,p_context_display_text IN hr_wip_transactions.context_display_text%TYPE
378 ,p_transaction_id OUT NOCOPY hr_wip_transactions.transaction_id%TYPE
379 )is
380 PRAGMA AUTONOMOUS_TRANSACTION;
381 begin
382 p_transaction_id := ins
383 (p_item_type => p_item_type
384 ,p_item_key => p_item_key
385 ,p_function_id => p_function_id
386 ,p_creator_user_id => p_creator_user_id
387 ,p_dml_mode => p_dml_mode
388 ,p_vo_xml => p_vo_xml
389 ,p_context_display_text => p_context_display_text
390 );
391 commit;
392 end create_transaction;
393 --
394 --
395 -- ----------------------------------------------------------------------------
396 -- |-----------------------< create_query_only_transaction >-----------------|
397 -- ----------------------------------------------------------------------------
398 Function create_query_only_transaction
399 (p_item_type IN wf_items.item_type%TYPE
400 ,p_item_key IN wf_items.item_key%TYPE
401 ,p_function_id IN fnd_form_functions.function_id%TYPE
402 ,p_creator_user_id IN fnd_user.user_id%TYPE
403 ,p_vo_xml IN VARCHAR2
404 ,p_context_display_text IN hr_wip_transactions.context_display_text%TYPE
405 ) RETURN hr_wip_transactions.transaction_id%TYPE is
406 PRAGMA AUTONOMOUS_TRANSACTION;
407 l_transaction_id hr_wip_transactions.transaction_id%TYPE;
408 begin
409 l_transaction_id := ins
410 (p_item_type => p_item_type
411 ,p_item_key => p_item_key
412 ,p_function_id => p_function_id
413 ,p_creator_user_id => p_creator_user_id
414 ,p_dml_mode => g_query_only_dml_mode
415 ,p_vo_xml => p_vo_xml
416 ,p_context_display_text => p_context_display_text
417 );
418 commit;
419 return l_transaction_id;
420 end create_query_only_transaction;
421 --
422 -- ----------------------------------------------------------------------------
423 -- |-----------------------< create_query_only_transaction >-----------------|
424 -- ----------------------------------------------------------------------------
425 Procedure create_query_only_transaction
426 (p_item_type IN wf_items.item_type%TYPE
427 ,p_item_key IN wf_items.item_key%TYPE
428 ,p_function_id IN fnd_form_functions.function_id%TYPE
429 ,p_creator_user_id IN fnd_user.user_id%TYPE
430 ,p_vo_xml IN VARCHAR2
431 ,p_context_display_text IN hr_wip_transactions.context_display_text%TYPE
432 ,p_transaction_id OUT NOCOPY hr_wip_transactions.transaction_id%TYPE
433 )is
434 PRAGMA AUTONOMOUS_TRANSACTION;
435 begin
436 p_transaction_id := ins
437 (p_item_type => p_item_type
438 ,p_item_key => p_item_key
439 ,p_function_id => p_function_id
440 ,p_creator_user_id => p_creator_user_id
441 ,p_dml_mode => g_query_only_dml_mode
442 ,p_vo_xml => p_vo_xml
443 ,p_context_display_text => p_context_display_text
444 );
445 commit;
446 end create_query_only_transaction;
447 --
448 -- ----------------------------------------------------------------------------
449 -- |-----------------------< save_for_later >-----------------------|
450 -- ----------------------------------------------------------------------------
451 Procedure save_for_later
452 (p_transaction_id IN hr_wip_transactions.transaction_id%TYPE
453 ,p_current_user_id IN hr_wip_transactions.creator_user_id%TYPE
454 ,p_creator_user_id IN hr_wip_transactions.creator_user_id%TYPE
455 ,p_state IN hr_wip_transactions.state%TYPE
456 ,p_vo_xml IN VARCHAR2
457 ,p_sub_state IN hr_wip_transactions.sub_state%TYPE
458 ,p_context_display_text IN hr_wip_transactions.context_display_text%TYPE
459 ,p_dml_mode IN hr_wip_transactions.dml_mode%TYPE) is
460 --
461 begin
462 --
463 chk_transaction_creator
464 (p_creator_user_id => p_creator_user_id
465 ,p_current_user_id => p_current_user_id);
466 --
467 chk_sub_state
468 (p_state => g_save_for_later_state
472 fnd_message.set_name('PER','PER_289672_TXN_INV_STATE');
469 ,p_sub_state => p_sub_state);
470 --
471 If p_state = g_pending_approval_state then
473 fnd_message.raise_error;
474 End if;
475 --
476 upd(p_transaction_id => p_transaction_id
477 ,p_state => g_save_for_later_state
478 ,p_sub_state => p_sub_state
479 ,p_vo_xml => p_vo_xml
480 ,p_dml_mode => p_dml_mode
481 ,p_context_display_text => p_context_display_text
482 );
483 end save_for_later;
484 --
485 -- ----------------------------------------------------------------------------
486 -- |-----------------------< save_for_later >-----------------------|
487 -- ----------------------------------------------------------------------------
488 Procedure save_for_later
489 (p_item_type IN wf_items.item_type%TYPE
490 ,p_item_key IN wf_items.item_key%TYPE
491 ,p_current_user_id IN fnd_user.user_id%TYPE
492 ,p_vo_xml IN VARCHAR2
493 ,p_sub_state IN hr_wip_transactions.sub_state%TYPE
494 ,p_dml_mode IN hr_wip_transactions.dml_mode%TYPE
495 default hr_api.g_varchar2
496 ,p_context_display_text IN hr_wip_transactions.context_display_text%TYPE
497 default hr_api.g_varchar2
498 ,p_transaction_id OUT NOCOPY hr_wip_transactions.transaction_id%TYPE
499 )is
500 PRAGMA AUTONOMOUS_TRANSACTION;
501 l_creator_user_id hr_wip_transactions.creator_user_id%TYPE;
502 l_state hr_wip_transactions.state%TYPE;
503 l_dml_mode hr_wip_transactions.dml_mode%TYPE;
504 l_context_display_text hr_wip_transactions.context_display_text%TYPE;
505 cursor csr_user_id is
506 select creator_user_id
507 ,transaction_id
508 ,state,dml_mode
509 ,context_display_text
510 from hr_wip_transactions
511 where item_type = p_item_type
512 and item_key = p_item_key;
513 begin
514 chk_item_type_item_key_mand
515 (p_item_type => p_item_type
516 ,p_item_key => p_item_key
517 );
518 chk_item_type_item_key_exists(p_item_type => p_item_type
519 ,p_item_key => p_item_key
520 );
521 open csr_user_id;
522 fetch csr_user_id into l_creator_user_id,
523 p_transaction_id,
524 l_state,
525 l_dml_mode,
526 l_context_display_text;
527 if csr_user_id%NOTFOUND THEN
528 close csr_user_id;
529 fnd_message.set_name('PER','PER_289680_TXN_INV_WFL');
530 fnd_message.raise_error;
531 end if;
532 close csr_user_id;
533 save_for_later
534 (p_transaction_id => p_transaction_id
535 ,p_current_user_id => p_current_user_id
536 ,p_creator_user_id => l_creator_user_id
537 ,p_state => l_state
538 ,p_vo_xml => p_vo_xml
539 ,p_sub_state => p_sub_state
540 ,p_context_display_text =>
541 get_context_display_text
542 (p_new_context_display_text => p_context_display_text
543 ,p_old_context_display_text => l_context_display_text)
544 ,p_dml_mode =>
545 get_and_chk_and_ret_dml_mode
546 (p_new_dml_mode => p_dml_mode
547 ,p_old_dml_mode => l_dml_mode)
548 );
549 commit;
550 exception
551 when others then
552 If csr_user_id%isopen
553 then
554 close csr_user_id;
555 end if;
556 raise;
557 end save_for_later;
558 --
559 -- ----------------------------------------------------------------------------
560 -- |-----------------------< save_for_later >-----------------------|
561 -- ----------------------------------------------------------------------------
562 Procedure save_for_later
563 (p_transaction_id IN hr_wip_transactions.transaction_id%TYPE
564 ,p_current_user_id IN fnd_user.user_id%TYPE
565 ,p_vo_xml IN VARCHAR2
566 ,p_sub_state IN hr_wip_transactions.sub_state%TYPE
567 ,p_dml_mode IN hr_wip_transactions.dml_mode%TYPE
568 default hr_api.g_varchar2
569 ,p_context_display_text IN hr_wip_transactions.context_display_text%TYPE
570 default hr_api.g_varchar2
571 )is
572 PRAGMA AUTONOMOUS_TRANSACTION;
573 l_creator_user_id hr_wip_transactions.creator_user_id%TYPE;
574 l_state hr_wip_transactions.state%TYPE;
575 l_dml_mode hr_wip_transactions.dml_mode%TYPE;
576 l_context_display_text hr_wip_transactions.context_display_text%TYPE;
577 cursor csr_user_id is
578 select creator_user_id,
579 state,
580 dml_mode,
581 context_display_text
582 from hr_wip_transactions
583 where transaction_id = p_transaction_id;
584 begin
585 open csr_user_id;
586 fetch csr_user_id into l_creator_user_id,
587 l_state,
588 l_dml_mode,
589 l_context_display_text;
590 if csr_user_id%NOTFOUND THEN
591 close csr_user_id;
592 fnd_message.set_name('PER','PER_289671_TXN_INV');
593 fnd_message.raise_error;
594 end if;
595 close csr_user_id;
596 save_for_later
597 (p_transaction_id => p_transaction_id
598 ,p_current_user_id => p_current_user_id
599 ,p_creator_user_id => l_creator_user_id
600 ,p_state => l_state
604 get_context_display_text
601 ,p_vo_xml => p_vo_xml
602 ,p_sub_state => p_sub_state
603 ,p_context_display_text =>
605 (p_new_context_display_text => p_context_display_text
606 ,p_old_context_display_text => l_context_display_text)
607 ,p_dml_mode =>
608 get_and_chk_and_ret_dml_mode
609 (p_new_dml_mode => p_dml_mode
610 ,p_old_dml_mode => l_dml_mode)
611 );
612 commit;
613 exception
614 when others then
615 If csr_user_id%isopen
616 then
617 close csr_user_id;
618 end if;
619 raise;
620 end save_for_later;
621 --
622 -- ----------------------------------------------------------------------------
623 -- |-----------------------< save_for_later_append >-----------------------|
624 -- ----------------------------------------------------------------------------
625 Procedure save_for_later_append
626 (p_transaction_id IN hr_wip_transactions.transaction_id%TYPE
627 ,p_vo_xml IN VARCHAR2
628 )is
629 PRAGMA AUTONOMOUS_TRANSACTION;
630
631 l_db_clob hr_wip_transactions.vo_cache%type;
632 cursor csr_vo_cache is
633 select vo_cache
634 from hr_wip_transactions
635 where transaction_id = p_transaction_id
636 for update nowait;
637 begin
638
639 open csr_vo_cache;
640 fetch csr_vo_cache into l_db_clob;
641 if csr_vo_cache%NOTFOUND THEN
642 close csr_vo_cache;
643 fnd_message.set_name('PER','PER_289671_TXN_INV');
644 fnd_message.raise_error;
645 end if;
646 close csr_vo_cache;
647
648 DBMS_LOB.WRITE(l_db_clob,
649 length(p_vo_xml),
650 DBMS_LOB.GETLENGTH(l_db_clob)+1,
651 p_vo_xml);
652
653 update hr_wip_transactions
654 set vo_cache = l_db_clob
655 where transaction_id = p_transaction_id;
656
657 commit;
658
659 exception
660 when others then
661 raise;
662 end save_for_later_append;
663 --
664 -- ----------------------------------------------------------------------------
665 -- |-----------------------< pending_approval >-----------------------|
666 -- ----------------------------------------------------------------------------
667 Procedure pending_approval
668 (p_transaction_id IN hr_wip_transactions.transaction_id%TYPE
669 ,p_current_user_id IN hr_wip_transactions.creator_user_id%TYPE
670 ,p_creator_user_id IN hr_wip_transactions.creator_user_id%TYPE
671 ,p_state IN hr_wip_transactions.state%TYPE
672 ,p_vo_xml IN VARCHAR2
673 ,p_sub_state IN hr_wip_transactions.sub_state%TYPE
674 ,p_context_display_text IN hr_wip_transactions.context_display_text%TYPE
675 ,p_dml_mode IN hr_wip_transactions.dml_mode%TYPE) is
676 begin
677 chk_transaction_creator(p_creator_user_id => p_creator_user_id
678 ,p_current_user_id => p_current_user_id
679 );
680 chk_sub_state
681 (p_state => g_pending_approval_state
682 ,p_sub_state => p_sub_state);
683 --
684 If p_dml_mode = g_query_only_dml_mode
685 then
686 fnd_message.set_name('PER','PER_289673_TXN_QUERY');
687 fnd_message.raise_error;
688 End if;
689 --
690 If p_state = g_pending_approval_state
691 then
692 fnd_message.set_name('PER','PER_289674_TXN_PENDING');
693 fnd_message.raise_error;
694 End if;
695 upd(p_transaction_id => p_transaction_id
696 ,p_state => g_pending_approval_state
697 ,p_sub_state => null
698 ,p_vo_xml => p_vo_xml
699 ,p_dml_mode => p_dml_mode
700 ,p_context_display_text => p_context_display_text
701 );
702 end pending_approval;
703 -- ----------------------------------------------------------------------------
704 -- |-----------------------< pending_approval >-----------------------|
705 -- ----------------------------------------------------------------------------
706 Procedure pending_approval
707 (p_item_type IN wf_items.item_type%TYPE
708 ,p_item_key IN wf_items.item_key%TYPE
709 ,p_current_user_id IN fnd_user.user_id%TYPE
710 ,p_vo_xml IN VARCHAR2
711 ,p_dml_mode IN hr_wip_transactions.dml_mode%TYPE
712 default hr_api.g_varchar2
713 ,p_context_display_text IN hr_wip_transactions.context_display_text%TYPE
714 default hr_api.g_varchar2
715 )is
716 PRAGMA AUTONOMOUS_TRANSACTION;
717 l_creator_user_id hr_wip_transactions.creator_user_id%TYPE;
718 l_transaction_id hr_wip_transactions.transaction_id%TYPE;
719 l_state hr_wip_transactions.state%TYPE;
720 l_dml_mode hr_wip_transactions.dml_mode%TYPE;
721 l_context_display_text hr_wip_transactions.context_display_text%TYPE;
722 cursor csr_user_id is
723 select creator_user_id,
724 transaction_id,
725 state,
726 dml_mode,
727 context_display_text
728 from hr_wip_transactions
729 where item_type = p_item_type
730 and item_key = p_item_key;
731 begin
732 chk_item_type_item_key_mand
733 (p_item_type => p_item_type
734 ,p_item_key => p_item_key
735 );
736 chk_item_type_item_key_exists(p_item_type => p_item_type
737 ,p_item_key => p_item_key
738 );
739 open csr_user_id;
740 fetch csr_user_id into l_creator_user_id,
744 l_context_display_text;
741 l_transaction_id,
742 l_state,
743 l_dml_mode,
745 if csr_user_id%NOTFOUND THEN
746 close csr_user_id;
747 fnd_message.set_name('PER','PER_289680_TXN_INV_WFL');
748 fnd_message.raise_error;
749 end if;
750 close csr_user_id;
751 pending_approval
752 (p_transaction_id => l_transaction_id
753 ,p_current_user_id => p_current_user_id
754 ,p_creator_user_id => l_creator_user_id
755 ,p_state => l_state
756 ,p_vo_xml => p_vo_xml
757 ,p_sub_state => null
758 ,p_context_display_text =>
759 get_context_display_text
760 (p_new_context_display_text => p_context_display_text
761 ,p_old_context_display_text => l_context_display_text)
762 ,p_dml_mode =>
763 get_and_chk_and_ret_dml_mode
764 (p_new_dml_mode => p_dml_mode
765 ,p_old_dml_mode => l_dml_mode)
766 );
767 commit;
768 exception
769 when others then
770 If csr_user_id%isopen
771 then
772 close csr_user_id;
773 end if;
774 raise;
775 end pending_approval;
776 -- ----------------------------------------------------------------------------
777 -- |-----------------------< pending_approval >-----------------------|
778 -- ----------------------------------------------------------------------------
779 Procedure pending_approval
780 (p_transaction_id IN hr_wip_transactions.transaction_id%TYPE
781 ,p_current_user_id IN fnd_user.user_id%TYPE
782 ,p_vo_xml IN VARCHAR2
783 ,p_dml_mode IN hr_wip_transactions.dml_mode%TYPE
784 default hr_api.g_varchar2
785 ,p_context_display_text IN hr_wip_transactions.context_display_text%TYPE
786 default hr_api.g_varchar2
787 )is
788 PRAGMA AUTONOMOUS_TRANSACTION;
789 l_creator_user_id hr_wip_transactions.creator_user_id%TYPE;
790 l_state hr_wip_transactions.state%TYPE;
791 l_dml_mode hr_wip_transactions.dml_mode%TYPE;
792 l_context_display_text hr_wip_transactions.context_display_text%TYPE;
793 cursor csr_user_id is
794 select creator_user_id,
795 state,
796 dml_mode,
797 context_display_text
798 from hr_wip_transactions
799 where transaction_id = p_transaction_id;
800 begin
801 open csr_user_id;
802 fetch csr_user_id into l_creator_user_id,
803 l_state,
804 l_dml_mode,
805 l_context_display_text;
806 if csr_user_id%NOTFOUND THEN
807 close csr_user_id;
808 fnd_message.set_name('PER','PER_289671_TXN_INV');
809 fnd_message.raise_error;
810 end if;
811 close csr_user_id;
812 pending_approval
813 (p_transaction_id => p_transaction_id
814 ,p_current_user_id => p_current_user_id
815 ,p_creator_user_id => l_creator_user_id
816 ,p_state => l_state
817 ,p_vo_xml => p_vo_xml
818 ,p_sub_state => null
819 ,p_context_display_text =>
820 get_context_display_text
821 (p_new_context_display_text => p_context_display_text
822 ,p_old_context_display_text => l_context_display_text)
823 ,p_dml_mode =>
824 get_and_chk_and_ret_dml_mode
825 (p_new_dml_mode => p_dml_mode
826 ,p_old_dml_mode => l_dml_mode)
827 );
828 commit;
829 exception
830 when others then
831 If csr_user_id%isopen
832 then
833 close csr_user_id;
834 end if;
835 raise;
836 end pending_approval;
837 --
838 -- ----------------------------------------------------------------------------
839 -- |-----------------------< reject_for_correction >-----------------------|
840 -- ----------------------------------------------------------------------------
841 Procedure reject_for_correction
842 (p_transaction_id IN hr_wip_transactions.transaction_id%TYPE
843 ,p_state IN hr_wip_transactions.state%TYPE
844 ,p_vo_xml IN clob
845 ,p_sub_state IN hr_wip_transactions.sub_state%TYPE
846 ,p_context_display_text IN hr_wip_transactions.context_display_text%TYPE
847 ,p_dml_mode IN hr_wip_transactions.dml_mode%TYPE) is
848 l_vo_xml_var varchar(32767);
849 l_amount number;
850 begin
851 If p_state <> g_pending_approval_state
852 then
853 fnd_message.set_name('PER','PER_289675_TXN_PEN_REJ_CORR');
854 fnd_message.raise_error;
855 End if;
856 --
857 -- Convert clob to varchar2 datatype for comparision
858 --
859 l_amount := dbms_lob.getlength(p_vo_xml);
860 dbms_lob.read(p_vo_xml,l_amount,1,l_vo_xml_var);
861 --
862 upd(p_transaction_id => p_transaction_id
863 ,p_state => g_reject_for_correction_state
864 ,p_sub_state => null
865 ,p_vo_xml => l_vo_xml_var
866 ,p_dml_mode => p_dml_mode
867 ,p_context_display_text => p_context_display_text
868 );
869 end reject_for_correction;
870 --
871 -- ----------------------------------------------------------------------------
872 -- |-----------------------< reject_for_correction >-----------------------|
876 ,p_item_key IN wf_items.item_key%TYPE
873 -- ----------------------------------------------------------------------------
874 Procedure reject_for_correction
875 (p_item_type IN wf_items.item_type%TYPE
877 )is
878 PRAGMA AUTONOMOUS_TRANSACTION;
879 l_state hr_wip_transactions.state%TYPE;
880 l_dml_mode hr_wip_transactions.dml_mode%TYPE;
881 l_context_display_text hr_wip_transactions.context_display_text%TYPE;
882 l_transaction_id hr_wip_transactions.transaction_id%TYPE;
883 l_vo_xml clob;
884 cursor csr_user_id is
885 select transaction_id,
886 state,
887 vo_cache,
888 dml_mode,
889 context_display_text
890 from hr_wip_transactions
891 where item_type = p_item_type
892 and item_key = p_item_key;
893 begin
894 chk_item_type_item_key_mand
895 (p_item_type => p_item_type
896 ,p_item_key => p_item_key
897 );
898 chk_item_type_item_key_exists(p_item_type => p_item_type
899 ,p_item_key => p_item_key
900 );
901 open csr_user_id;
902 fetch csr_user_id into l_transaction_id,
903 l_state,
904 l_vo_xml,
905 l_dml_mode,
906 l_context_display_text;
907 if csr_user_id%NOTFOUND THEN
908 close csr_user_id;
909 fnd_message.set_name('PER','PER_289680_TXN_INV_WFL');
910 fnd_message.raise_error;
911 end if;
912 close csr_user_id;
913 reject_for_correction(p_transaction_id => l_transaction_id
914 ,p_state => l_state
915 ,p_vo_xml => l_vo_xml
916 ,p_sub_state => null
917 ,p_context_display_text => l_context_display_text
918 ,p_dml_mode => l_dml_mode
919 );
920 commit;
921 exception
922 when others then
923 If csr_user_id%isopen
924 then
925 close csr_user_id;
926 end if;
927 raise;
928 end reject_for_correction;
929 -- ----------------------------------------------------------------------------
930 -- |-----------------------< reject_for_correction >-----------------------|
931 -- ----------------------------------------------------------------------------
932 Procedure reject_for_correction
933 (p_transaction_id IN hr_wip_transactions.transaction_id%TYPE
934 )is
935 PRAGMA AUTONOMOUS_TRANSACTION;
936 l_state hr_wip_transactions.state%TYPE;
937 l_dml_mode hr_wip_transactions.dml_mode%TYPE;
938 l_context_display_text hr_wip_transactions.context_display_text%TYPE;
939 l_vo_xml clob;
940 cursor csr_user_id is
941 select state,
942 vo_cache,
943 dml_mode,
944 context_display_text
945 from hr_wip_transactions
946 where transaction_id = p_transaction_id;
947 begin
948 open csr_user_id;
949 fetch csr_user_id into l_state,
950 l_vo_xml,
951 l_dml_mode,
952 l_context_display_text;
953 if csr_user_id%NOTFOUND THEN
954 close csr_user_id;
955 fnd_message.set_name('PER','PER_289671_TXN_INV');
956 fnd_message.raise_error;
957 end if;
958 close csr_user_id;
959 reject_for_correction(p_transaction_id => p_transaction_id
960 ,p_state => l_state
961 ,p_vo_xml => l_vo_xml
962 ,p_sub_state => null
963 ,p_context_display_text => l_context_display_text
964 ,p_dml_mode => l_dml_mode
965 );
966 commit;
967 exception
968 when others then
969 If csr_user_id%isopen
970 then
971 close csr_user_id;
972 end if;
973 raise;
974 end reject_for_correction;
975 --
976 -- ----------------------------------------------------------------------------
977 -- |-----------------------< delete_transaction >-----------------------|
978 -- ----------------------------------------------------------------------------
979 Procedure delete_transaction
980 (p_item_type IN wf_items.item_type%TYPE
981 ,p_item_key IN wf_items.item_key%TYPE
982 )is
983 PRAGMA AUTONOMOUS_TRANSACTION;
984 l_transaction_id hr_wip_transactions.transaction_id%TYPE;
985 cursor csr_transaction is
986 select transaction_id
987 from hr_wip_transactions
988 where item_type = p_item_type
989 and item_key = p_item_key;
990 begin
991 --
992 chk_item_type_item_key_mand
993 (p_item_type => p_item_type
994 ,p_item_key => p_item_key
995 );
996
997 open csr_transaction;
998 fetch csr_transaction into l_transaction_id;
999 If csr_transaction%notfound
1000 then
1001 close csr_transaction;
1002 fnd_message.set_name('PER','PER_289680_TXN_INV_WFL');
1003 fnd_message.raise_error;
1004 End if;
1005 close csr_transaction;
1006 --
1007 delete from hr_wip_locks
1008 where transaction_id = l_transaction_id;
1009 --
1010 delete from hr_wip_transactions
1011 where transaction_id = l_transaction_id;
1012 --
1013 commit;
1014 exception
1015 when others then
1016 If csr_transaction%isopen
1017 then
1018 close csr_transaction;
1022 --
1019 end if;
1020 raise;
1021 end delete_transaction;
1023 -- ----------------------------------------------------------------------------
1024 -- |-----------------------< delete_transaction >-----------------------|
1025 -- ----------------------------------------------------------------------------
1026 Procedure delete_transaction
1027 (p_transaction_id IN hr_wip_transactions.transaction_id%TYPE
1028 )is
1029 PRAGMA AUTONOMOUS_TRANSACTION;
1030 l_transaction_id varchar2(1);
1031 cursor csr_transaction is
1032 select null
1033 from hr_wip_transactions
1034 where transaction_id = p_transaction_id;
1035 begin
1036 --
1037 open csr_transaction;
1038 fetch csr_transaction into l_transaction_id;
1039 If csr_transaction%notfound
1040 then
1041 close csr_transaction;
1042 fnd_message.set_name('PER','PER_289671_TXN_INV');
1043 fnd_message.raise_error;
1044 End if;
1045 close csr_transaction;
1046 delete from hr_wip_locks
1047 where transaction_id = p_transaction_id;
1048 --
1049 delete from hr_wip_transactions
1050 where transaction_id = p_transaction_id;
1051 --
1052 commit;
1053 exception
1054 when others then
1055 If csr_transaction%isopen
1056 then
1057 close csr_transaction;
1058 end if;
1059 raise;
1060 end delete_transaction;
1061 --
1062 --
1063 -- ----------------------------------------------------------------------------
1064 -- |-----------------------< update_transaction >-----------------------|
1065 -- ----------------------------------------------------------------------------
1066 Procedure update_transaction
1067 (p_item_type IN wf_items.item_type%TYPE
1068 ,p_item_key IN wf_items.item_key%TYPE
1069 ,p_state IN hr_wip_transactions.state%TYPE
1070 ,p_sub_state IN hr_wip_transactions.sub_state%TYPE
1071 ,p_dml_mode IN hr_wip_transactions.dml_mode%TYPE
1072 default hr_api.g_varchar2
1073 ,p_vo_xml IN VARCHAR2
1074 ,p_context_display_text IN hr_wip_transactions.context_display_text%TYPE
1075 default hr_api.g_varchar2
1076 )is
1077 PRAGMA AUTONOMOUS_TRANSACTION;
1078 l_transaction_id hr_wip_transactions.transaction_id%TYPE;
1079 l_dml_mode hr_wip_transactions.dml_mode%TYPE;
1080 l_context_display_text hr_wip_transactions.context_display_text%TYPE;
1081 cursor csr_transaction is
1082 select transaction_id
1083 ,dml_mode
1084 ,context_display_text
1085 from hr_wip_transactions
1086 where item_type = p_item_type
1087 and item_key = p_item_key;
1088 begin
1089 chk_item_type_item_key_mand
1090 (p_item_type => p_item_type
1091 ,p_item_key => p_item_key
1092 );
1093 chk_item_type_item_key_exists(p_item_type => p_item_type
1094 ,p_item_key => p_item_key
1095 );
1096 open csr_transaction;
1097 fetch csr_transaction into l_transaction_id,
1098 l_dml_mode,
1099 l_context_display_text;
1100 If csr_transaction%notfound
1101 then
1102 close csr_transaction;
1103 fnd_message.set_name('PER','PER_289680_TXN_INV_WFL');
1104 fnd_message.raise_error;
1105 End if;
1106 close csr_transaction;
1107 chk_state(p_state => p_state);
1108 chk_sub_state(p_state => p_state
1109 ,p_sub_state => p_sub_state
1110 );
1111 upd(p_transaction_id => l_transaction_id
1112 ,p_state => p_state
1113 ,p_sub_state => p_sub_state
1114 ,p_vo_xml => p_vo_xml
1115 ,p_context_display_text =>
1116 get_context_display_text
1117 (p_new_context_display_text => p_context_display_text
1118 ,p_old_context_display_text => l_context_display_text
1119 )
1120 ,p_dml_mode =>
1121 get_and_chk_and_ret_dml_mode
1122 (p_new_dml_mode => p_dml_mode
1123 ,p_old_dml_mode => l_dml_mode
1124 )
1125 );
1126 commit;
1127 exception
1128 when others then
1129 If csr_transaction%isopen
1130 then
1131 close csr_transaction;
1132 end if;
1133 raise;
1134 end update_transaction;
1135 --
1136 -- ----------------------------------------------------------------------------
1137 -- |-----------------------< update_transaction >-----------------------|
1138 -- ----------------------------------------------------------------------------
1139 Procedure update_transaction
1140 (p_transaction_id IN hr_wip_transactions.transaction_id%TYPE
1141 ,p_state IN hr_wip_transactions.state%TYPE
1142 ,p_sub_state IN hr_wip_transactions.sub_state%TYPE
1143 ,p_dml_mode IN hr_wip_transactions.dml_mode%TYPE
1144 default hr_api.g_varchar2
1145 ,p_vo_xml IN VARCHAR2
1146 ,p_context_display_text IN hr_wip_transactions.context_display_text%TYPE
1147 default hr_api.g_varchar2
1148 )is
1149 PRAGMA AUTONOMOUS_TRANSACTION;
1150 l_dml_mode hr_wip_transactions.dml_mode%TYPE;
1151 l_context_display_text hr_wip_transactions.context_display_text%TYPE;
1152 cursor csr_transaction is
1153 select dml_mode,
1154 context_display_text
1155 from hr_wip_transactions
1156 where transaction_id = p_transaction_id;
1157 begin
1158 chk_state(p_state => p_state);
1159 chk_sub_state(p_state => p_state
1163 fetch csr_transaction into l_dml_mode,
1160 ,p_sub_state => p_sub_state
1161 );
1162 open csr_transaction;
1164 l_context_display_text;
1165 If csr_transaction%notfound
1166 then
1167 close csr_transaction;
1168 fnd_message.set_name('PER','PER_289671_TXN_INV');
1169 fnd_message.raise_error;
1170 End if;
1171 close csr_transaction;
1172 upd(p_transaction_id => p_transaction_id
1173 ,p_state => p_state
1174 ,p_sub_state => p_sub_state
1175 ,p_vo_xml => p_vo_xml
1176 ,p_context_display_text =>
1177 get_context_display_text
1178 (p_new_context_display_text => p_context_display_text
1179 ,p_old_context_display_text => l_context_display_text)
1180 ,p_dml_mode =>
1181 get_and_chk_and_ret_dml_mode
1182 (p_new_dml_mode => p_dml_mode
1183 ,p_old_dml_mode => l_dml_mode)
1184 );
1185 commit;
1186 exception
1187 when others then
1188 If csr_transaction%isopen
1189 then
1190 close csr_transaction;
1191 end if;
1192 raise;
1193 end update_transaction;
1194 --
1195 --
1196 END hr_wip_txns;