[Home] [Help]
PACKAGE BODY: APPS.PQH_SS_HISTORY
Source
1 Package body pqh_ss_history as
2 /* $Header: pqhstswi.pkb 120.2 2005/10/04 12:24:09 snachuri noship $*/
3
4
5 g_package constant varchar2(25) := 'pqh_ss_history.';
6 g_debug boolean ;
7
8 procedure getRoleOrigSysInfo(p_item_type in varchar2,
9 p_item_key in varchar2,
10 p_wf_ref_attr in varchar2,
11 p_wf_ref_type in varchar2,
12 p_user_name in out nocopy varchar2,
13 p_orig_system out nocopy varchar2,
14 p_orig_system_id out nocopy number) is
15 --local variables
16 c_proc constant varchar2(30) := 'getRoleOrigSysInfo';
17 lv_disp_name wf_users.display_name%type;
18 lt_userRoles wf_directory.wf_local_roles_tbl_type;
19 begin
20 g_debug := hr_utility.debug_enabled;
21
22 if g_debug then
23 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
24 end if;
25
26 if(p_wf_ref_attr is not null) then
27 if(p_wf_ref_type='VARCHAR') then
28 -- get the orig_system and system id details
29 -- currently we have only two use cases
30 if(p_wf_ref_attr='FORWARD_TO_USERNAME') then
31 -- p_orig_system_id
32 p_orig_system_id := wf_engine.getitemattrnumber(
33 p_item_type,
34 p_item_key,
35 'FORWARD_TO_PERSON_ID',
36 true);
37 --p_orig_system
38 p_orig_system := nvl(wf_engine.getitemattrtext(
39 p_item_type,
40 p_item_key,
41 'FORWARD_TO_ORIG_SYS_ATTR',
42 true),'PER');
43 elsif(p_wf_ref_attr='CREATOR_PERSON_USERNAME') then
44 -- p_orig_system_id
45 p_orig_system_id := wf_engine.getitemattrnumber(
46 p_item_type,
47 p_item_key,
48 'CREATOR_PERSON_ID',
49 true);
50 -- need to revisit with the role based support is enhanced.
51 p_orig_system :='PER';
52
53 end if;
54
55 -- get the role name from wf_directory services
56 wf_directory.GetRoleName
57 (p_orig_system => p_orig_system
58 ,p_orig_system_id => p_orig_system_id
59 ,p_name => p_user_name
60 ,p_display_name => lv_disp_name);
61
62 if(p_orig_system not in ('FND_USR','PER')) then
63 -- need to check if we have equivalent PER or FND_USR role
64 p_user_name:=null;
65 -- need to revisit to populate the details based on
66 -- context user approving the transaction.
67 end if;
68
69 -- trim the user name as this needs to be in
70 -- same size as fnd_user.user_name
71 p_user_name:= substrb(p_user_name,1,100);
72
73
74 end if;
75 else
76 p_user_name:=null;
77 p_orig_system:=null;
78 p_orig_system_id:=null;
79
80 end if;
81
82 if (g_debug ) then
83 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 10);
84 end if;
85 exception
86 when others then
87 if g_debug then
88 hr_utility.set_location('Error in getRoleOrigSysInfo SQLERRM' ||' '||to_char(SQLCODE),20);
89 end if;
90
91 raise;
92
93 end getRoleOrigSysInfo;
94
95 -- Local procedure to insert into transaction history table
96
97 procedure insert_transaction_history (
98 p_transaction_history_id in number,
99 p_creator_person_id in number,
100 p_assignment_id in number,
101 p_selected_person_id in number,
102 p_process_name in varchar2,
103 p_item_type in varchar2,
104 p_item_key in varchar2,
105 p_function_id in number,
106 p_rptg_grp_id in number,
107 p_plan_id in number,
108 p_created_by in number,
109 p_creation_date in date,
110 p_last_update_date in date,
111 p_last_updated_by in number,
112 p_last_update_login in number) is
113 begin
114 insert into pqh_ss_transaction_history (
115 transaction_history_id ,
116 creator_person_id ,
117 assignment_id ,
118 selected_person_id ,
119 process_name ,
120 item_type ,
121 item_key ,
122 function_id ,
123 rptg_grp_id ,
124 plan_id ,
125 created_by ,
126 creation_date ,
127 last_update_date ,
128 last_updated_by ,
129 last_update_login )
130 values
131 (p_transaction_history_id ,
132 p_creator_person_id ,
133 p_assignment_id ,
134 p_selected_person_id ,
135 p_process_name ,
136 p_item_type ,
137 p_item_key ,
138 p_function_id ,
139 p_rptg_grp_id ,
140 p_plan_id ,
141 p_created_by ,
142 p_creation_date ,
143 p_last_update_date ,
144 p_last_updated_by ,
145 p_last_update_login );
146
147 end insert_transaction_history;
148
149 procedure insert_approval_history (
150 p_approval_history_id in number,
151 p_transaction_history_id in number,
152 p_transaction_effective_date in date,
153 p_action in varchar2,
154 p_user_name in varchar2,
155 p_transaction_item_type in varchar2,
156 p_transaction_item_key in varchar2,
157 p_created_by in number,
158 p_creation_date in date,
159 p_last_update_date in date,
160 p_last_updated_by in number,
161 p_last_update_login in number,
162 p_orig_system in varchar2 default null,
163 p_orig_system_id in number default null) is
164
165 begin
166 insert into pqh_ss_approval_history (
167 approval_history_id ,
168 transaction_history_id ,
169 transaction_effective_date ,
170 action ,
171 user_name ,
172 orig_system ,
173 orig_system_id ,
174 transaction_item_type ,
175 transaction_item_key ,
176 created_by ,
177 creation_date ,
178 last_update_date ,
179 last_updated_by ,
180 last_update_login )
181 values
182 ( p_approval_history_id ,
183 p_transaction_history_id ,
184 p_transaction_effective_date ,
185 p_action ,
186 p_user_name ,
187 p_orig_system ,
188 p_orig_system_id ,
189 p_transaction_item_type ,
190 p_transaction_item_key ,
191 p_created_by ,
192 p_creation_date ,
193 p_last_update_date ,
194 p_last_updated_by ,
195 p_last_update_login );
196
197 end insert_approval_history;
198
199 procedure insert_step_history (
200 p_step_history_id in number,
201 p_approval_history_id in number,
202 p_transaction_history_id in number,
203 p_api_name in varchar2,
204 p_api_display_name in varchar2,
205 p_processing_order in varchar2,
206 p_item_type in varchar2,
207 p_item_key in varchar2,
208 p_activity_id in number,
209 p_created_by in number,
210 p_creation_date in date,
211 p_last_update_date in date,
212 p_last_updated_by in number,
213 p_last_update_login in number) is
214 begin
215 insert into pqh_ss_step_history
216 (step_history_id ,
217 approval_history_id ,
218 transaction_history_id ,
219 api_name ,
220 api_display_name ,
221 processing_order ,
222 item_type ,
223 item_key ,
224 activity_id ,
225 created_by ,
226 creation_date ,
227 last_update_date ,
228 last_updated_by ,
229 last_update_login )
230 values (
231 p_step_history_id ,
232 p_approval_history_id ,
233 p_transaction_history_id ,
234 p_api_name ,
235 p_api_display_name ,
236 p_processing_order ,
237 p_item_type ,
238 p_item_key ,
239 p_activity_id ,
240 p_created_by ,
241 p_creation_date ,
242 p_last_update_date ,
243 p_last_updated_by ,
244 p_last_update_login );
245
246 end insert_step_history;
247
248 Procedure insert_value_history (
249 p_transaction_value_id in number,
250 p_step_history_id in number,
251 p_approval_history_id in number,
252 p_datatype in varchar2,
253 p_name in varchar2,
254 p_value in varchar2,
255 p_created_by in number,
256 p_creation_date in date,
257 p_last_update_date in date,
258 p_last_updated_by in number,
259 p_last_update_login in number) is
260 begin
261 insert into pqh_ss_value_history
262 (transaction_value_id ,
263 step_history_id ,
264 approval_history_id ,
265 datatype ,
266 name ,
267 value ,
268 created_by ,
269 creation_date ,
270 last_update_date ,
271 last_updated_by ,
272 last_update_login )
273 values (
274 p_transaction_value_id ,
275 p_step_history_id ,
276 p_approval_history_id ,
277 p_datatype ,
278 p_name ,
279 p_value ,
280 p_created_by ,
281 p_creation_date ,
282 p_last_update_date ,
283 p_last_updated_by ,
284 p_last_update_login );
285
286 end insert_value_history;
287 --
288 --
289
290 PROCEDURE transfer_action_to_history (
291 p_itemType IN VARCHAR2
292 , p_itemKey IN VARCHAR2
293 , p_action IN VARCHAR2 ) IS
294
295 l_txnItemType VARCHAR2(10);
296 l_txnItemKey VARCHAR2(240);
297 l_dummyProcess VARCHAR2(200);
298 l_username VARCHAR2(320);
299 l_transactionId NUMBER(15);
300 l_orig_system wf_users.orig_system%type;
301 l_orig_system_id wf_users.orig_system_id%type;
302 BEGIN
303 /* l_username :=
304 wf_engine.GetItemAttrText(
305 itemtype => p_itemType,
306 itemkey => p_itemKey,
307 aname => 'FORWARD_TO_USERNAME');*/
308 getRoleOrigSysInfo(p_item_type=>p_itemType,
309 p_item_key=>p_itemKey,
310 p_wf_ref_attr=>'FORWARD_TO_USERNAME',
311 p_wf_ref_type=>'VARCHAR',
312 p_user_name=>l_username,
313 p_orig_system=>l_orig_system,
314 p_orig_system_id =>l_orig_system_id);
315
316
317 l_transactionId :=
318 pqh_ss_workflow.get_transaction_id (
319 p_itemType => p_itemType
320 ,p_itemKey => p_itemKey);
321
322 /*
323 pqh_workflow_web_pkg.get_txn_wf_from_appr_wf (
324 p_apprItemType => p_itemType
325 ,p_apprItemKey => p_itemKey
326 ,p_itemType => l_txnItemType
327 ,p_itemKey => l_txnItemKey
328 ,p_processName => l_dummyProcess );
329 */
330 transfer_to_history (
331 p_itemType => p_itemType
332 ,p_itemKey => p_itemKey
333 ,p_action => p_action
334 ,p_username => l_username
335 ,p_transactionId => l_transactionId
336 ,p_orig_system => l_orig_system
337 ,p_orig_system_id => l_orig_system_id);
338
339 END;
340
341
342 --Called from approval workflow
343 PROCEDURE transfer_submit_to_history (
344 itemtype IN VARCHAR2,
345 itemkey IN VARCHAR2,
346 actid IN NUMBER,
347 funmode IN VARCHAR2,
348 result OUT NOCOPY VARCHAR2 ) IS
349 --
350 l_username VARCHAR2(320);
351 l_transactionId NUMBER(15);
352 l_orig_system wf_users.orig_system%type;
353 l_orig_system_id wf_users.orig_system_id%type;
354 --
355 BEGIN
356 if ( funmode = 'RUN' ) THEN
357 /* l_username :=
358 wf_engine.GetItemAttrText(
359 itemtype => itemType,
360 itemkey => itemKey,
361 aname => 'CREATOR_PERSON_USERNAME');*/
362 getRoleOrigSysInfo(p_item_type=>itemtype,
363 p_item_key=>itemkey,
364 p_wf_ref_attr=>'CREATOR_PERSON_USERNAME',
365 p_wf_ref_type=>'VARCHAR',
366 p_user_name=>l_username,
367 p_orig_system=>l_orig_system,
368 p_orig_system_id =>l_orig_system_id);
369
370 l_transactionId :=
371 pqh_ss_workflow.get_transaction_id (
372 p_itemType => itemtype
373 ,p_itemKey => itemkey);
374
375 transfer_to_history (
376 p_itemType => itemType
377 ,p_itemKey => itemKey
378 ,p_action => 'SUBMIT'
379 ,p_username => l_username
380 ,p_transactionId => l_transactionId
381 ,p_orig_system => l_orig_system
382 ,p_orig_system_id => l_orig_system_id);
383
384 end if;
385 result := 'COMPLETE:SUCCESS';
386 END;
387
388
389 --Called from approval workflow
390 PROCEDURE transfer_approval_to_history (
391 itemtype IN VARCHAR2,
392 itemkey IN VARCHAR2,
393 actid IN NUMBER,
394 funmode IN VARCHAR2,
395 result OUT NOCOPY VARCHAR2 ) IS
396 l_action VARCHAR2(25) := 'APPROVED';
397 l_transactionId NUMBER(15);
398 BEGIN
399 if ( funmode = 'RUN' ) THEN
400 -- Check if package variable is set to indicate that
401 -- Transaction was edited before submit. If yes
402
403 IF ( pqh_ss_history.G_ACTION = '_EDIT') THEN
404 -- reset the variable and
405 -- Change the action appropriately
406 l_action := l_action||pqh_ss_history.G_ACTION ;
407 pqh_ss_history.G_ACTION := '';
408
409 l_transactionId :=
413
410 pqh_ss_workflow.get_transaction_id (
411 p_itemType => itemType
412 ,p_itemKey => itemKey);
414 track_original_value
415 ( p_ItemType => itemType
416 , p_itemKey => itemKey
417 , p_action => 'SFL'
418 , p_username => null
419 , p_transactionId => l_transactionId);
420
421 END IF;
422
423 transfer_action_to_history (
424 p_itemType => itemType
425 ,p_itemKey => itemKey
426 ,p_action => l_action );
427 end if;
428 result := 'COMPLETE:SUCCESS';
429 END;
430 --Called from approval workflow
431 PROCEDURE transfer_reject_to_history (
432 itemtype IN VARCHAR2,
433 itemkey IN VARCHAR2,
434 actid IN NUMBER,
435 funmode IN VARCHAR2,
436 result OUT NOCOPY VARCHAR2 ) IS
437
438 BEGIN
439 if ( funmode = 'RUN' ) THEN
440 transfer_action_to_history (
441 p_itemType => itemType
442 ,p_itemKey => itemKey
443 ,p_action => 'REJECTED' );
444 end if;
445 result := 'COMPLETE:SUCCESS';
446 END;
447
448
449 --Called from approval workflow
450 PROCEDURE transfer_delete_to_history (
451 itemtype IN VARCHAR2,
452 itemkey IN VARCHAR2,
453 actid IN NUMBER,
454 funmode IN VARCHAR2,
455 result OUT NOCOPY VARCHAR2 ) IS
456
457 l_txnItemType VARCHAR2(10);
458 l_txnItemKey VARCHAR2(240);
459 l_dummyProcess VARCHAR2(200);
460 BEGIN
461 if ( funmode = 'RUN' ) THEN
462 transfer_action_to_history (
463 p_itemType => itemType
464 ,p_itemKey => itemKey
465 ,p_action => 'DELETED' );
466 end if;
467 result := 'COMPLETE:SUCCESS';
468 END;
469 --Called from approval workflow
470 PROCEDURE transfer_startover_to_history (
471 itemtype IN VARCHAR2,
472 itemkey IN VARCHAR2,
473 actid IN NUMBER,
474 funmode IN VARCHAR2,
475 result OUT NOCOPY VARCHAR2 ) IS
476
477 l_txnItemType VARCHAR2(10);
478 l_txnItemKey VARCHAR2(240);
479 l_dummyProcess VARCHAR2(200);
480 BEGIN
481 if ( funmode = 'RUN' ) THEN
482 transfer_action_to_history (
483 p_itemType => itemType
484 ,p_itemKey => itemKey
485 ,p_action => 'STARTOVER' );
486 end if;
487 result := 'COMPLETE:SUCCESS';
488 END;
489 --Called from approval workflow
490 PROCEDURE transfer_rfc_to_history (
491 itemtype IN VARCHAR2,
492 itemkey IN VARCHAR2,
493 actid IN NUMBER,
494 funmode IN VARCHAR2,
495 result OUT NOCOPY VARCHAR2 ) IS
496
497 l_txnItemType VARCHAR2(10);
498 l_txnItemKey VARCHAR2(240);
499 l_dummyProcess VARCHAR2(200);
500 BEGIN
501 if ( funmode = 'RUN' ) THEN
502 transfer_action_to_history (
503 p_itemType => itemType
504 ,p_itemKey => itemKey
505 ,p_action => 'RFC' );
506 end if;
507 result := 'COMPLETE:SUCCESS';
508 END;
509
510 --Called from pqh_workflow_web_pkg.start_approval_wf_process procedure
511 --Accepts Transaction Item Type and Key
512 procedure transfer_to_history
513 ( p_ItemType in varchar2
514 , p_itemKey in varchar2
515 , p_action in varchar2
516 , p_username in varchar2
517 , p_transactionId in number
518 , p_orig_system in varchar2 default null
519 , p_orig_system_id in number default null) IS
520
521 l_transactionId NUMBER := p_transactionId;
522 l_username VARCHAR2(320) := p_username;
523 l_c1 varchar2(30);
524 --
525 cursor c_th_txn (c_txn_id in number) is
526 select *
527 from hr_api_transactions
528 where transaction_id = c_txn_id;
529 --
530 cursor c_th_txn_h (c_txn_id in number) is
531 select null
532 from pqh_ss_transaction_history
533 where transaction_history_id = c_txn_id ;
534 --
535 cursor c_th_appr_h (c_txn_id in number) is
536 select nvl(max(approval_history_id),0)+1 approval_history_id
537 from pqh_ss_approval_history
538 where transaction_history_id = c_txn_id
539 and approval_history_id > 0 ;
540 --
541 cursor c_th_step (c_txn_id in number) is
542 select *
543 from hr_api_transaction_steps s
544 where transaction_id = c_txn_id
545 and exists (select null
546 from hr_api_transaction_values v
547 where v.transaction_step_id = s.transaction_step_id );
548 --
549 cursor c_th_value (c_step_id in number) is
550 select transaction_value_id ,
551 datatype ,
552 name ,
553 decode( datatype, 'VARCHAR2', varchar2_value,
557 'DATE' , fnd_date.date_to_canonical(original_date_value),
554 'DATE' , fnd_date.date_to_canonical(date_value),
555 'NUMBER' , to_char(number_value) , '' ) value ,
556 decode( datatype, 'VARCHAR2', original_varchar2_value,
558 'NUMBER' , to_char(original_number_value) , '' ) original_value ,
559 created_by ,
560 creation_date ,
561 last_update_date ,
562 last_updated_by ,
563 last_update_login
564 from hr_api_transaction_values
565 where transaction_step_id = c_step_id;
566 --
567 BEGIN
568 -- new code to support the archive api's
569 if(p_transactionId is not null and p_itemKey is not null) then
570 if(p_action='SUBMIT') then
571 hr_trans_history_api.archive_submit(p_transactionId,
572 null,
573 p_username,
574 wf_engine.getitemattrtext(p_ItemType,
575 p_itemKey,
576 'APPROVAL_COMMENT_COPY'));
577
578 elsif(p_action='RESUBMIT') then
579 hr_trans_history_api.archive_resubmit(p_transactionId,
580 null,
581 p_username,
582 wf_engine.getitemattrtext(p_ItemType,
583 p_itemKey,
584 'APPROVAL_COMMENT_COPY'));
585 elsif(p_action='APPROVED') then
586 hr_trans_history_api.archive_approve(p_transactionId,
587 null,
588 p_username,
589 wf_engine.getitemattrtext(p_ItemType,
590 p_itemKey,
591 'WF_NOTE'));
592 elsif(p_action='REJECTED') then
593 hr_trans_history_api.archive_reject(p_transactionId,
594 null,
595 p_username,
596 wf_engine.getitemattrtext(p_ItemType,
597 p_itemKey,
598 'WF_NOTE'));
599 elsif(p_action='DELETED') then
600 hr_trans_history_api.archive_delete(p_transactionId,
601 null,
602 p_username,
603 wf_engine.getitemattrtext(p_ItemType,
604 p_itemKey,
605 'WF_NOTE'));
606 elsif(p_action='RFC') then
607 hr_trans_history_api.archive_rfc(p_transactionId,
608 null,
609 p_username,
610 wf_engine.getitemattrtext(p_ItemType,
611 p_itemKey,
612 'NOTE_FROM_APPR',true));
613 end if;
614 end if;
615 -- end new code changes
616
617 end;
618
619 procedure update_approval_history (
620 p_approval_history_id in number,
621 p_transaction_history_id in number,
622 p_transaction_effective_date in date,
623 p_action in varchar2,
624 p_user_name in varchar2,
625 p_transaction_item_type in varchar2,
626 p_transaction_item_key in varchar2,
627 p_created_by in number,
628 p_creation_date in date,
629 p_last_update_date in date,
630 p_last_updated_by in number,
631 p_last_update_login in number,
632 p_orig_system in varchar2 default null,
633 p_orig_system_id in number default null) is
634
635 begin
636 update pqh_ss_approval_history
637 set transaction_effective_date = p_transaction_effective_date ,
638 user_name = p_user_name,
639 orig_system = p_orig_system,
640 orig_system_id = p_orig_system_id
641 where transaction_history_id = p_transaction_history_id
642 and approval_history_id = p_approval_history_id;
643
644 exception when no_data_found then
645 insert_approval_history (
646 p_approval_history_id => p_approval_history_id,
647 p_transaction_history_id => p_transaction_history_id,
648 p_transaction_effective_date => p_transaction_effective_date,
649 p_action => p_action,
650 p_user_name => p_user_name,
651 p_transaction_item_type => p_transaction_item_type,
652 p_transaction_item_key => p_transaction_item_key,
653 p_created_by => p_created_by,
654 p_creation_date => p_creation_date,
655 p_last_update_date => p_last_update_date,
656 p_last_updated_by => p_last_updated_by,
660
657 p_last_update_login => p_last_update_login,
658 p_orig_system => p_orig_system,
659 p_orig_system_id => p_orig_system_id);
661 end update_approval_history;
662
663 procedure update_step_history (
664 p_step_history_id in number,
665 p_approval_history_id in number,
666 p_transaction_history_id in number,
667 p_api_name in varchar2,
668 p_api_display_name in varchar2,
669 p_processing_order in varchar2,
670 p_item_type in varchar2,
671 p_item_key in varchar2,
672 p_activity_id in varchar2,
673 p_created_by in number,
674 p_creation_date in date,
675 p_last_update_date in date,
676 p_last_updated_by in number,
677 p_last_update_login in number) is
678
679 l_var varchar2(20);
680 begin
681 select null
682 into l_var
683 from pqh_ss_step_history
684 where transaction_history_id = p_transaction_history_id
685 and approval_history_id = p_approval_history_id
686 and step_history_id = p_step_history_id;
687
688 exception when no_data_found then
689 insert_step_history
690 (p_step_history_id => p_step_history_id,
691 p_approval_history_id => p_approval_history_id,
692 p_transaction_history_id => p_transaction_history_id,
693 p_api_name => p_api_name,
694 p_api_display_name => p_api_display_name,
695 p_processing_order => p_processing_order,
696 p_item_type => p_item_type,
697 p_item_key => p_item_key,
698 p_activity_id => p_activity_id,
699 p_created_by => p_created_by,
700 p_creation_date => p_creation_date,
701 p_last_update_date => p_last_update_date,
702 p_last_updated_by => p_last_updated_by,
703 p_last_update_login => p_last_update_login);
704
705 end update_step_history;
706
707 Procedure update_value_history (
708 p_transaction_value_id in number,
709 p_step_history_id in number,
710 p_approval_history_id in number,
711 p_datatype in varchar2,
712 p_name in varchar2,
713 p_value in varchar2,
714 p_created_by in number,
715 p_creation_date in date,
716 p_last_update_date in date,
717 p_last_updated_by in number,
718 p_last_update_login in number) is
719 begin
720 update pqh_ss_value_history
721 set value = p_value
722 where transaction_value_id = p_transaction_value_id
723 and step_history_id = p_step_history_id
724 and approval_history_id = p_approval_history_id
725 and name = p_name;
726
727 exception when no_data_found then
728 insert_value_history (
729 p_transaction_value_id => p_transaction_value_id,
730 p_step_history_id => p_step_history_id,
731 p_approval_history_id => p_approval_history_id,
732 p_datatype => p_datatype,
733 p_name => p_name,
734 p_value => p_value,
735 p_created_by => p_created_by,
736 p_creation_date => p_creation_date,
737 p_last_update_date => p_last_update_date,
738 p_last_updated_by => p_last_updated_by,
739 p_last_update_login => p_last_update_login);
740
741 end update_value_history;
742
743
744 procedure track_original_value
745 ( p_ItemType in varchar2
746 , p_itemKey in varchar2
747 , p_action in varchar2
748 , p_username in varchar2
749 , p_transactionId in number
750 , p_orig_system in varchar2 default null
751 , p_orig_system_id in number default null) IS
752
753 l_transactionId NUMBER := p_transactionId;
754 l_username VARCHAR2(320) := p_username;
755 l_c VARCHAR2(30) ;
756 l_new_history BOOLEAN := TRUE;
757
758 cursor c_ov_apr_h (v_transactionId in number)is
759 select null from pqh_ss_approval_history
760 where transaction_history_id = v_transactionId
761 and approval_history_id = 0;
762 --
763 cursor c_ov_txn (c_txn_id in number) is
764 select *
765 from hr_api_transactions
766 where transaction_id = c_txn_id;
767 --
768 cursor c_ov_txn_h (c_txn_id in number) is
769 select null
770 from pqh_ss_transaction_history
771 where transaction_history_id = c_txn_id ;
772 --
773 cursor c_ov_step (c_txn_id in number) is
774 select *
775 from hr_api_transaction_steps s
776 where transaction_id = c_txn_id
777 and exists (select null
778 from hr_api_transaction_values v
779 where v.transaction_step_id = s.transaction_step_id );
780 --
781 cursor c_ov_value (c_step_id in number) is
782 select transaction_value_id ,
783 datatype ,
784 name ,
785 decode( datatype, 'VARCHAR2', varchar2_value,
786 'DATE' , fnd_date.date_to_canonical(date_value),
787 'NUMBER' , to_char(number_value) , '' ) value ,
791 created_by ,
788 decode( datatype, 'VARCHAR2', original_varchar2_value,
789 'DATE' , fnd_date.date_to_canonical(original_date_value),
790 'NUMBER' , to_char(original_number_value) , '' ) original_value ,
792 creation_date ,
793 last_update_date ,
794 last_updated_by ,
795 last_update_login
796 from hr_api_transaction_values
797 where transaction_step_id = c_step_id;
798 --
799 begin
800 for apr in c_ov_apr_h (l_transactionId) loop
801 l_new_history := FALSE;
802 end loop; --c_ov_apr_h
803
804 for tn in c_ov_txn (l_transactionId) loop
805
806 if l_new_history then
807
808 if c_ov_txn_h%ISOPEN then
809 close c_ov_txn_h ;
810 end if; --ISOPEN
811 open c_ov_txn_h (l_transactionId);
812 fetch c_ov_txn_h into l_c;
813
814 if c_ov_txn_h%notfound then
815 insert_transaction_history (
816 p_transaction_history_id => tn.transaction_id,
817 p_creator_person_id => tn.creator_person_id,
818 p_assignment_id => tn.assignment_id,
819 p_selected_person_id => tn.selected_person_id,
820 p_process_name => tn.process_name,
821 p_item_type => p_itemType,
822 p_item_key => p_itemKey,
823 p_function_id => tn.function_id,
824 p_rptg_grp_id => tn.rptg_grp_id,
825 p_plan_id => tn.plan_id,
826 p_created_by => tn.created_by,
827 p_creation_date => tn.creation_date,
828 p_last_update_date => tn.last_update_date,
829 p_last_updated_by => tn.last_updated_by,
830 p_last_update_login => tn.last_update_login);
831 end if; --c_ov_txn_h%notfound
832 close c_ov_txn_h;
833
834 insert_approval_history (
835 p_approval_history_id => 0,
836 p_transaction_history_id => l_transactionId,
837 p_transaction_effective_date => tn.transaction_effective_date,
838 p_action => 'LATEST_ORIGINAL_VALUE',
839 p_user_name => l_username,
840 p_orig_system => p_orig_system,
841 p_orig_system_id => p_orig_system_id,
842 p_transaction_item_type => tn.item_type,
843 p_transaction_item_key => tn.item_key,
844 p_created_by => tn.created_by,
845 p_creation_date => tn.creation_date,
846 p_last_update_date => tn.last_update_date,
847 p_last_updated_by => tn.last_updated_by,
848 p_last_update_login => tn.last_update_login);
849
850 for stp in c_ov_step(l_transactionId) loop
851
852 insert_step_history
853 (p_step_history_id => stp.transaction_step_id,
854 p_approval_history_id => 0 ,
855 p_transaction_history_id => l_transactionId,
856 p_api_name => stp.api_name,
857 p_api_display_name => stp.api_display_name,
858 p_processing_order => stp.processing_order,
859 p_item_type => stp.item_type,
860 p_item_key => stp.item_key,
861 p_activity_id => stp.activity_id,
862 p_created_by => stp.created_by,
863 p_creation_date => stp.creation_date,
864 p_last_update_date => stp.last_update_date,
865 p_last_updated_by => stp.last_updated_by,
866 p_last_update_login => stp.last_update_login);
867
868 for vlue in c_ov_value (stp.transaction_step_id) loop
869
870 insert_value_history (
871 p_transaction_value_id => vlue.transaction_value_id,
872 p_step_history_id => stp.transaction_step_id,
873 p_approval_history_id => 0 ,
874 p_datatype => vlue.datatype,
875 p_name => vlue.name,
876 p_value => vlue.original_value,
877 p_created_by => vlue.created_by,
878 p_creation_date => vlue.creation_date,
879 p_last_update_date => vlue.last_update_date,
880 p_last_updated_by => vlue.last_updated_by,
881 p_last_update_login => vlue.last_update_login);
882
883 end loop; -- c_ov_value
884 end loop; -- c_ov_step
885
886
887 else -- l_new_history is false
888
889 update_approval_history (
890 p_approval_history_id => 0,
891 p_transaction_history_id => l_transactionId,
892 p_transaction_effective_date => tn.transaction_effective_date,
893 p_action => 'LATEST_ORIGINAL_VALUE',
894 p_user_name => l_username,
895 p_orig_system => p_orig_system,
896 p_orig_system_id => p_orig_system_id,
900 p_creation_date => tn.creation_date,
897 p_transaction_item_type => tn.item_type,
898 p_transaction_item_key => tn.item_key,
899 p_created_by => tn.created_by,
901 p_last_update_date => tn.last_update_date,
902 p_last_updated_by => tn.last_updated_by,
903 p_last_update_login => tn.last_update_login);
904
905 for stp in c_ov_step(l_transactionId) loop
906
907 update_step_history
908 (p_step_history_id => stp.transaction_step_id,
909 p_approval_history_id => 0 ,
910 p_transaction_history_id => l_transactionId,
911 p_api_name => stp.api_name,
912 p_api_display_name => stp.api_display_name,
913 p_processing_order => stp.processing_order,
914 p_item_type => stp.item_type,
915 p_item_key => stp.item_key,
916 p_activity_id => stp.activity_id,
917 p_created_by => stp.created_by,
918 p_creation_date => stp.creation_date,
919 p_last_update_date => stp.last_update_date,
920 p_last_updated_by => stp.last_updated_by,
921 p_last_update_login => stp.last_update_login);
922
923 for vlue in c_ov_value (stp.transaction_step_id) loop
924 update_value_history (
925 p_transaction_value_id => vlue.transaction_value_id,
926 p_step_history_id => stp.transaction_step_id,
927 p_approval_history_id => 0 ,
928 p_datatype => vlue.datatype,
929 p_name => vlue.name,
930 p_value => vlue.original_value,
931 p_created_by => vlue.created_by,
932 p_creation_date => vlue.creation_date,
933 p_last_update_date => vlue.last_update_date,
934 p_last_updated_by => vlue.last_updated_by,
935 p_last_update_login => vlue.last_update_login);
936
937 end loop; -- c_ov_value
938
939 end loop; -- c_ov_step
940
941 end if; -- appr.approval_history_id = 1
942
943 end loop; --c_ov_txn
944
945 commit;
946 end track_original_value;
947
948 PROCEDURE copy_value_to_history (
949 p_txnId IN NUMBER ) IS
950 --
951 --
952 CURSOR c_vt_sup_step(c_txn_id in number, c_api_name in varchar2 default null) IS
953 SELECT *
954 FROM hr_api_transaction_steps
955 WHERE transaction_id = c_txn_id
956 AND ( (c_api_name is null AND api_name in (
957 'HR_PAY_RATE_SS.PROCESS_API'
958 ,'HR_SUPERVISOR_SS.PROCESS_API'
959 ,'HR_PROCESS_SIT_SS.PROCESS_API'
960 ,'HR_QUA_AWARDS_UTIL_SS.PROCESS_API'
961 ,'HR_PROCESS_PHONE_NUMBERS_SS.PROCESS_API'
962 ,'HR_PROCESS_ADDRESS_SS.PROCESS_API'
963 ,'HR_PROCESS_CONTACT_SS.PROCESS_API'
964 ,'HR_PROCESS_PERSON_SS.PROCESS_API'
965 ,'HR_COMP_PROFILE_SS.PROCESS_API'))
966 OR api_name = c_api_name);
967 --
968 cursor c_vt_txn (c_txn_id in number) is
969 select *
970 from hr_api_transactions
971 where transaction_id = c_txn_id;
972 --
973 cursor c_vt_txn_h (c_txn_id in number) is
974 select null
975 from pqh_ss_transaction_history
976 where transaction_history_id = c_txn_id ;
977 --
978 dummy varchar2(10);
979 tn c_vt_txn%ROWTYPE;
980 --
981 BEGIN
982 --
983 hr_utility.set_location('Entering: copy_value to history -'||p_txnId, 5);
984 --
985 delete from pqh_ss_value_history
986 where approval_history_id = -1
987 and step_history_id in (
988 select step_history_id from pqh_ss_step_history
989 where transaction_history_id = p_txnId
990 and approval_history_id = -1);
991 --
992 hr_utility.set_location('delete value history done', 45);
993 delete from pqh_ss_step_history
997 for r_supStep in c_vt_sup_step(p_txnId) loop
994 where transaction_history_id = p_txnId
995 and approval_history_id = -1;
996
998
999 hr_utility.set_location('step_id'||r_supStep.transaction_step_id, 10);
1000 --
1001 --Check to see if transaction history record already exists
1002 if c_vt_txn_h%ISOPEN then
1003 close c_vt_txn_h ;
1004 end if; --ISOPEN
1005 --
1006 OPEN c_vt_txn_h (p_txnId);
1007 FETCH c_vt_txn_h INTO dummy;
1008
1009 -- Create a new record if txn history does not exist
1010 IF c_vt_txn_h%NOTFOUND THEN
1011 hr_utility.set_location('Transaction history not found', 15);
1012 if c_vt_txn%ISOPEN then
1013 close c_vt_txn ;
1014 end if; --ISOPEN
1015 open c_vt_txn (p_txnId);
1016 fetch c_vt_txn into tn;
1017 close c_vt_txn;
1018
1019 hr_utility.set_location('Calling insert', 15);
1020 -- create approval history if it does not exist.
1021 insert_transaction_history (
1022 p_transaction_history_id => tn.transaction_id,
1023 p_creator_person_id => tn.creator_person_id,
1024 p_assignment_id => tn.assignment_id,
1025 p_selected_person_id => tn.selected_person_id,
1026 p_process_name => tn.process_name,
1027 p_item_type => tn.item_Type,
1028 p_item_key => tn.item_Key,
1029 p_function_id => tn.function_id,
1030 p_rptg_grp_id => tn.rptg_grp_id,
1031 p_plan_id => tn.plan_id,
1032 p_created_by => tn.created_by,
1033 p_creation_date => tn.creation_date,
1034 p_last_update_date => tn.last_update_date,
1035 p_last_updated_by => tn.last_updated_by,
1036 p_last_update_login => tn.last_update_login);
1037
1038 hr_utility.set_location('Insert history complete', 20);
1039
1040 END IF;
1041 CLOSE c_vt_txn_h;
1042 --
1043 hr_utility.set_location('delete step history done', 55);
1044
1045 insert_step_history
1046 (p_step_history_id => r_supStep.transaction_step_id,
1047 p_approval_history_id => -1,
1048 p_transaction_history_id => p_txnId,
1049 p_api_name => r_supStep.api_name,
1050 p_api_display_name => r_supStep.api_display_name,
1051 p_processing_order => r_supStep.processing_order,
1052 p_item_type => r_supStep.item_type,
1053 p_item_key => r_supStep.item_key,
1054 p_activity_id => r_supStep.activity_id,
1055 p_created_by => r_supStep.created_by,
1056 p_creation_date => r_supStep.creation_date,
1057 p_last_update_date => r_supStep.last_update_date,
1058 p_last_updated_by => r_supStep.last_updated_by,
1059 p_last_update_login => r_supStep.last_update_login);
1060
1061 hr_utility.set_location('insert step history done', 65);
1062
1063 insert into pqh_ss_value_history (
1064 transaction_value_id ,
1065 step_history_id ,
1066 approval_history_id ,
1067 datatype ,
1068 name ,
1069 value ,
1070 original_value ,
1071 created_by ,
1072 creation_date ,
1073 last_update_date ,
1074 last_updated_by ,
1075 last_update_login )
1076 select transaction_value_id ,
1077 transaction_step_id ,
1078 -1 ,
1079 datatype ,
1080 name ,
1081 decode( datatype, 'VARCHAR2', varchar2_value,
1085 'DATE' , fnd_date.date_to_canonical(original_date_value),
1082 'DATE' , fnd_date.date_to_canonical(date_value),
1083 'NUMBER' , number_value , '' ) value ,
1084 decode( datatype, 'VARCHAR2', original_varchar2_value,
1086 'NUMBER' , original_number_value , '' ) original_value ,
1087 created_by ,
1088 creation_date ,
1089 last_update_date ,
1090 last_updated_by ,
1091 last_update_login
1092 from hr_api_transaction_values
1093 where transaction_step_id = r_supStep.transaction_step_id ;
1094 --
1095 hr_utility.set_location('Leaving: copy_value to history', 75);
1096 end loop ; --c_vt_sup_step
1097
1098 END copy_value_to_history;
1099
1100 PROCEDURE copy_value_from_history (
1101 p_txnId IN NUMBER ) IS
1102 --
1103 CURSOR c_vf_sup_step(c_txn_id in number, c_api_name in varchar2 default null) IS
1104 SELECT *
1105 FROM hr_api_transaction_steps
1106 WHERE transaction_id = c_txn_id
1107 AND ( (c_api_name is null AND api_name in (
1108 'HR_SUPERVISOR_SS.PROCESS_API'
1109 ,'HR_PROCESS_SIT_SS.PROCESS_API'
1110 ,'HR_QUA_AWARDS_UTIL_SS.PROCESS_API'
1111 ,'HR_PROCESS_PHONE_NUMBERS_SS.PROCESS_API'
1112 ,'HR_PROCESS_ADDRESS_SS.PROCESS_API'
1113 ,'HR_PROCESS_CONTACT_SS.PROCESS_API'
1114 ,'HR_PROCESS_PERSON_SS.PROCESS_API'
1115 ,'HR_COMP_PROFILE_SS.PROCESS_API'))
1116 OR api_name = c_api_name);
1117 --
1118 CURSOR c_vf_sup_step_h (c_txn_id in number) IS
1119 SELECT *
1120 FROM pqh_ss_step_history
1121 WHERE transaction_history_id = c_txn_id
1122 AND approval_history_id = -1
1123 AND api_name in ('HR_SUPERVISOR_SS.PROCESS_API'
1124 ,'HR_PROCESS_SIT_SS.PROCESS_API'
1125 ,'HR_QUA_AWARDS_UTIL_SS.PROCESS_API'
1126 ,'HR_PROCESS_PHONE_NUMBERS_SS.PROCESS_API'
1127 ,'HR_PROCESS_ADDRESS_SS.PROCESS_API'
1128 ,'HR_PROCESS_CONTACT_SS.PROCESS_API'
1129 ,'HR_PROCESS_PERSON_SS.PROCESS_API'
1130 ,'HR_COMP_PROFILE_SS.PROCESS_API'
1131 )
1132 order by api_name;
1133 --
1134 r_supStep c_vf_sup_step%ROWTYPE;
1135 --
1136 l_temp_api_name varchar2(80);
1137 l_step_id number ;
1138 --
1139 BEGIN
1140 --
1141 -- Remove values and step which are not part of history.
1142 --
1143 delete from hr_api_transaction_values v
1144 where v.transaction_step_id in (select s.transaction_step_id from hr_api_transaction_steps s
1145 where s.transaction_id = p_txnId
1146 and s.api_name not in (select h.api_name from pqh_ss_step_history h
1147 where transaction_history_id = p_txnId
1148 and approval_history_id = -1 )
1149 and s.api_name in (
1150 'HR_PAY_RATE_SS.PROCESS_API'
1151 ,'HR_SUPERVISOR_SS.PROCESS_API'
1152 ,'HR_PROCESS_SIT_SS.PROCESS_API'
1153 ,'HR_QUA_AWARDS_UTIL_SS.PROCESS_API'
1154 ,'HR_PROCESS_PHONE_NUMBERS_SS.PROCESS_API'
1155 ,'HR_PROCESS_ADDRESS_SS.PROCESS_API'
1156 ,'HR_PROCESS_CONTACT_SS.PROCESS_API'
1157 ,'HR_PROCESS_PERSON_SS.PROCESS_API'
1158 ,'HR_COMP_PROFILE_SS.PROCESS_API' ));
1159 --
1160 delete from hr_api_transaction_steps s
1161 where s.transaction_id = p_txnId
1162 and s.api_name not in (select h.api_name from pqh_ss_step_history h
1163 where transaction_history_id = p_txnId
1164 and approval_history_id = -1 )
1165 and s.api_name in (
1166 'HR_PAY_RATE_SS.PROCESS_API'
1167 ,'HR_SUPERVISOR_SS.PROCESS_API'
1168 ,'HR_PROCESS_SIT_SS.PROCESS_API'
1169 ,'HR_QUA_AWARDS_UTIL_SS.PROCESS_API'
1170 ,'HR_PROCESS_PHONE_NUMBERS_SS.PROCESS_API'
1171 ,'HR_PROCESS_ADDRESS_SS.PROCESS_API'
1172 ,'HR_PROCESS_CONTACT_SS.PROCESS_API'
1173 ,'HR_PROCESS_PERSON_SS.PROCESS_API'
1174 ,'HR_COMP_PROFILE_SS.PROCESS_API' );
1175 --
1176 -- Simply return if there is no history record
1177 for r_supStep_h in c_vf_sup_step_h (p_txnId) loop
1178 --
1179 if l_temp_api_name is null then
1180 --
1181 if c_vf_sup_step%ISOPEN then
1182 close c_vf_sup_step ;
1183 end if; --ISOPEN
1184 --
1188 --
1185 open c_vf_sup_step (p_txnId, r_supStep_h.api_name );
1186 --
1187 l_temp_api_name := r_supStep_h.api_name ;
1189 elsif l_temp_api_name <> r_supStep_h.api_name then
1190 loop
1191 --
1192 fetch c_vf_sup_step INTO r_supStep;
1193 --
1194 if c_vf_sup_step%NOTFOUND then
1195 exit; -- exit loop, when no further records are found
1196 --
1197 else
1198 --
1199 delete from hr_api_transaction_values
1200 where transaction_step_id = r_supStep.transaction_step_id;
1201 --
1202 delete from hr_api_transaction_steps
1203 where transaction_step_id = r_supStep.transaction_step_id;
1204 --
1205 end if; -- c_vf_sup_step%FOUND
1206 --
1207 end loop; -- step loop
1208 --
1209 if c_vf_sup_step%ISOPEN then
1210 close c_vf_sup_step ;
1211 end if; --ISOPEN
1212 --
1213 open c_vf_sup_step (p_txnId, r_supStep_h.api_name );
1214 --
1215 l_temp_api_name := r_supStep_h.api_name ;
1216 --
1217 end if;
1218 --
1219 fetch c_vf_sup_step INTO r_supStep;
1220 --
1221 if c_vf_sup_step%NOTFOUND then
1222 --
1223 select hr_api_transaction_steps_s.nextval
1224 into l_step_id
1225 from dual;
1226
1227 insert into hr_api_transaction_steps(
1228 transaction_step_id ,
1229 transaction_id ,
1230 api_name ,
1231 api_display_name ,
1232 processing_order ,
1233 item_type ,
1234 item_key ,
1235 activity_id ,
1236 creator_person_id ,
1237 object_version_number ,
1238 created_by ,
1239 creation_date ,
1240 last_update_date ,
1241 last_updated_by ,
1242 last_update_login )
1243 values (
1244 l_step_id ,
1245 r_supStep_h.transaction_history_id ,
1246 r_supStep_h.api_name ,
1247 r_supStep_h.api_display_name ,
1248 r_supStep_h.processing_order ,
1249 r_supStep_h.item_type ,
1250 r_supStep_h.item_key ,
1251 r_supStep_h.activity_id ,
1252 0,0,
1253 r_supStep_h.created_by ,
1254 r_supStep_h.creation_date ,
1255 r_supStep_h.last_update_date ,
1256 r_supStep_h.last_updated_by ,
1257 r_supStep_h.last_update_login );
1258 --
1259 else
1260 l_step_id := r_supStep.transaction_step_id ;
1261
1262 delete from hr_api_transaction_values
1263 where transaction_step_id = r_supStep.transaction_step_id;
1264 end if; -- c_sup_step%NOTFOUND
1265
1266
1267 insert into hr_api_transaction_values (
1268 transaction_value_id,
1269 transaction_step_id,
1270 datatype,
1271 name,
1272 varchar2_value,
1273 number_value,
1274 date_value,
1275 original_varchar2_value,
1276 original_number_value,
1277 original_date_value,
1278 created_by,
1279 creation_date,
1280 last_update_date,
1281 last_updated_by,
1282 last_update_login )
1283 select hr_api_transaction_values_s.nextval ,
1284 l_step_id, -- nvl( r_supStep.transaction_step_id, step_history_id) step_history_id,
1285 datatype,
1286 name,
1287 decode(datatype,'VARCHAR2',value),
1288 decode(datatype,'NUMBER',to_number(value)),
1289 decode(datatype,'DATE',fnd_date.canonical_to_date(value)),
1290 decode(datatype,'VARCHAR2',original_value),
1291 decode(datatype,'NUMBER',to_number(original_value)),
1292 decode(datatype,'DATE',fnd_date.canonical_to_date(original_value)),
1293 created_by,
1294 creation_date,
1295 last_update_date,
1296 last_updated_by,
1297 last_update_login
1298 from pqh_ss_value_history vh
1299 where vh.approval_history_id = -1
1300 and vh.step_history_id = r_supStep_h.step_history_id;
1301
1302 end loop; -- c_vf_supStep_h
1303 --
1304 if c_vf_sup_step%ISOPEN then
1305 --
1306 loop
1307 fetch c_vf_sup_step INTO r_supStep;
1308 --
1309 if c_vf_sup_step%NOTFOUND then
1310 exit; -- exit loop, when no further records are found
1311 --
1312 else
1313 delete from hr_api_transaction_values
1314 where transaction_step_id = r_supStep.transaction_step_id;
1315 --
1316 delete from hr_api_transaction_steps
1317 where transaction_step_id = r_supStep.transaction_step_id;
1318 --
1319 end if; -- c_vf_sup_step%FOUND
1320 --
1321 end loop; -- step loop
1322 --
1323 close c_vf_sup_step;
1324 end if; -- c_vf_sup_step%ISOPEN
1325 --
1326 END;
1327
1328
1329 end pqh_ss_history;