[Home] [Help]
PACKAGE BODY: APPS.HR_TRANS_HISTORY_API
Source
1 PACKAGE BODY HR_TRANS_HISTORY_API as
2 /* $Header: hrtrhapi.pkb 120.9 2007/03/08 06:56:44 ddeb ship $ */
3 -- Global variables
4 g_date_format varchar2(10) := 'RRRR/MM/DD';
5 g_package varchar2(33) := 'HR_TRANS_HISTORY_API.';
6 g_debug boolean := hr_utility.debug_enabled;
7 --
8 --
9
10 TYPE NumberTblType IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
11 TYPE DateTblType IS TABLE OF DATE INDEX BY BINARY_INTEGER;
12 TYPE VarChar08TblType IS TABLE OF VARCHAR2(8) INDEX BY BINARY_INTEGER;
13 TYPE VarChar10TblType IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
14 TYPE VarChar30TblType IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
15 TYPE VarChar61TblType IS TABLE OF VARCHAR2(61) INDEX BY BINARY_INTEGER;
16 TYPE VarChar150TblType IS TABLE OF VARCHAR2(150) INDEX BY BINARY_INTEGER;
17 TYPE VarChar240TblType IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
18 TYPE VarChar320TblType IS TABLE OF VARCHAR2(320) INDEX BY BINARY_INTEGER;
19 TYPE VarChar2000TblType IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
20
21 TYPE TransStateHistTbl IS RECORD
22 (
23 TRANSACTION_HISTORY_ID NumberTblType
24 ,APPROVAL_HISTORY_ID NumberTblType
25 ,CREATOR_PERSON_ID NumberTblType
26 ,CREATOR_ROLE VarChar320TblType
27 ,STATUS VarChar10TblType
28 ,TRANSACTION_STATE VarChar10TblType
29 ,EFFECTIVE_DATE DateTblType
30 ,EFFECTIVE_DATE_OPTION VarChar10TblType
31 ,LAST_UPDATE_ROLE VarChar320TblType
32 ,PARENT_TRANSACTION_ID NumberTblType
33 ,RELAUNCH_FUNCTION VarChar30TblType
34 ,CREATED_BY NumberTblType
35 ,CREATION_DATE DateTblType
36 ,LAST_UPDATE_DATE DateTblType
37 ,LAST_UPDATED_BY NumberTblType
38 ,LAST_UPDATE_LOGIN NumberTblType
39 );
40
41 TYPE TransValueTbl IS RECORD
42 (
43 transaction_value_id NumberTblType
44 ,step_history_id NumberTblType
45 ,datatype VarChar30TblType
46 ,name VarChar30TblType
47 ,value VarChar2000TblType
48 ,original_value VarChar2000TblType
49 ,created_by NumberTblType
50 ,creation_date DateTblType
51 ,last_update_date DateTblType
52 ,last_updated_by NumberTblType
53 ,last_update_login NumberTblType
54 );
55
56 TYPE TransStepTbl IS RECORD
57 (
58 STEP_HISTORY_ID NumberTblType
59 ,TRANSACTION_HISTORY_ID NumberTblType
60 ,API_NAME VarChar61TblType
61 ,API_DISPLAY_NAME VarChar61TblType
62 ,PROCESSING_ORDER NumberTblType
63 ,CREATED_BY NumberTblType
64 ,CREATION_DATE DateTblType
65 ,LAST_UPDATE_DATE DateTblType
66 ,LAST_UPDATED_BY NumberTblType
67 ,LAST_UPDATE_LOGIN NumberTblType
68 ,ITEM_TYPE VarChar08TblType
69 ,ITEM_KEY VarChar240TblType
70 ,ACTIVITY_ID NumberTblType
71 ,OBJECT_TYPE VarChar30TblType
72 ,OBJECT_NAME VarChar150TblType
73 ,OBJECT_IDENTIFIER VarChar240TblType
74 ,OBJECT_STATE VarChar30TblType
75 ,PK1 VarChar240TblType
76 ,PK2 VarChar240TblType
77 ,PK3 VarChar240TblType
78 ,PK4 VarChar240TblType
79 ,PK5 VarChar240TblType
80 ,INFORMATION_CATEGORY VarChar30TblType
81 ,INFORMATION1 VarChar150TblType
82 ,INFORMATION2 VarChar150TblType
83 ,INFORMATION3 VarChar150TblType
84 ,INFORMATION4 VarChar150TblType
85 ,INFORMATION5 VarChar150TblType
86 ,INFORMATION6 VarChar150TblType
87 ,INFORMATION7 VarChar150TblType
88 ,INFORMATION8 VarChar150TblType
89 ,INFORMATION9 VarChar150TblType
90 ,INFORMATION10 VarChar150TblType
91 ,INFORMATION11 VarChar150TblType
92 ,INFORMATION12 VarChar150TblType
93 ,INFORMATION13 VarChar150TblType
94 ,INFORMATION14 VarChar150TblType
95 ,INFORMATION15 VarChar150TblType
96 ,INFORMATION16 VarChar150TblType
97 ,INFORMATION17 VarChar150TblType
98 ,INFORMATION18 VarChar150TblType
99 ,INFORMATION19 VarChar150TblType
100 ,INFORMATION20 VarChar150TblType
101 ,INFORMATION21 VarChar150TblType
102 ,INFORMATION22 VarChar150TblType
103 ,INFORMATION23 VarChar150TblType
104 ,INFORMATION24 VarChar150TblType
105 ,INFORMATION25 VarChar150TblType
106 ,INFORMATION26 VarChar150TblType
107 ,INFORMATION27 VarChar150TblType
108 ,INFORMATION28 VarChar150TblType
109 ,INFORMATION29 VarChar150TblType
110 ,INFORMATION30 VarChar150TblType
111
112 );
113
114 --
115 --
116 FUNCTION getTransStateSequence
117 (
118 P_TRANSACTION_ID IN NUMBER
119 ) RETURN NUMBER
120 IS
121 -- Cursor to return MaxSeq for new transactions.
122 CURSOR getMaxSeq IS
123 SELECT MAX(APPROVAL_HISTORY_ID)
124 FROM PQH_SS_TRANS_STATE_HISTORY
125 WHERE TRANSACTION_HISTORY_ID = P_TRANSACTION_ID;
126
127 -- Cursor to return Max seq for old transactions.
128 CURSOR getMaxOldSeq IS
129 SELECT MAX(APPROVAL_HISTORY_ID)
130 FROM PQH_SS_APPROVAL_HISTORY
131 WHERE TRANSACTION_HISTORY_ID = P_TRANSACTION_ID;
132
133 CURSOR populateStateTable IS
134 Select
135 ah.TRANSACTION_HISTORY_ID
136 ,ah.APPROVAL_HISTORY_ID
137 ,tx.CREATOR_PERSON_ID
138 ,tx.CREATOR_ROLE
139 ,tx.STATUS
140 ,tx.TRANSACTION_STATE
141 ,ah.TRANSACTION_EFFECTIVE_DATE
142 ,ah.EFFECTIVE_DATE_OPTION
143 ,tx.LAST_UPDATE_ROLE
144 ,tx.PARENT_TRANSACTION_ID
145 ,tx.RELAUNCH_FUNCTION
146 ,ah.CREATED_BY
147 ,ah.CREATION_DATE
148 ,ah.LAST_UPDATE_DATE
149 ,ah.LAST_UPDATED_BY
150 ,ah.LAST_UPDATE_LOGIN
151 FROM pqh_ss_approval_history ah, HR_API_TRANSACTIONS tx
152 WHERE TRANSACTION_ID = TRANSACTION_HISTORY_ID
153 AND TRANSACTION_HISTORY_ID = P_TRANSACTION_ID;
154
155 -- Note: Heena - Can we use only PQH_SS_APPROVAL_HISTORY ?
156
157 l_seq_id NUMBER(5) := NULL;
158 l_proc constant varchar2(100) := g_package || ' getTransStateSequence';
159 stateTbl TransStateHistTbl;
160 l_cnt INTEGER;
161
162 BEGIN
163 hr_utility.set_location('Entering: '|| l_proc,5);
164 OPEN getMaxSeq;
165 FETCH getMaxSeq into l_seq_id;
166 CLOSE getMaxSeq;
167 -- Check if this is old transaction, if yes, fetch from approval table.
168 If l_seq_id IS NULL Then
169 OPEN getMaxOldSeq;
170 FETCH getMaxOldSeq INTO l_seq_id;
171 CLOSE getMaxOldSeq;
172 IF (l_seq_id IS NOT NULL) THEN
173 -- populate PQH_SS_TRANS_STATE_HISTORY
174 -- from PQH_SS_APPROVAL_HISTORY and HR_API_TRANSACTIONS
175 NULL;
176 OPEN populateStateTable;
177 FETCH populateStateTable BULK COLLECT INTO
178 stateTbl.TRANSACTION_HISTORY_ID
179 ,stateTbl.APPROVAL_HISTORY_ID
180 ,stateTbl.CREATOR_PERSON_ID
181 ,stateTbl.CREATOR_ROLE
182 ,stateTbl.STATUS
183 ,stateTbl.TRANSACTION_STATE
184 ,stateTbl.EFFECTIVE_DATE
185 ,stateTbl.EFFECTIVE_DATE_OPTION
186 ,stateTbl.LAST_UPDATE_ROLE
187 ,stateTbl.PARENT_TRANSACTION_ID
188 ,stateTbl.RELAUNCH_FUNCTION
189 ,stateTbl.CREATED_BY
190 ,stateTbl.CREATION_DATE
191 ,stateTbl.LAST_UPDATE_DATE
192 ,stateTbl.LAST_UPDATED_BY
193 ,stateTbl.LAST_UPDATE_LOGIN;
194
195 l_cnt := stateTbl.TRANSACTION_HISTORY_ID.count;
196 FOR i in 1.. l_cnt LOOP
197 Insert into pqh_ss_trans_state_history
198 (
199 TRANSACTION_HISTORY_ID,
200 APPROVAL_HISTORY_ID,
201 CREATOR_PERSON_ID,
202 CREATOR_ROLE,
203 STATUS,
204 TRANSACTION_STATE,
205 EFFECTIVE_DATE,
206 EFFECTIVE_DATE_OPTION,
207 LAST_UPDATE_ROLE,
208 PARENT_TRANSACTION_ID,
209 RELAUNCH_FUNCTION,
210 CREATED_BY,
211 CREATION_DATE,
212 LAST_UPDATE_DATE,
213 LAST_UPDATED_BY,
214 LAST_UPDATE_LOGIN
215 )
216 values
217 (
218 stateTbl.TRANSACTION_HISTORY_ID(i)
219 ,stateTbl.APPROVAL_HISTORY_ID(i)
220 ,stateTbl.CREATOR_PERSON_ID(i)
221 ,stateTbl.CREATOR_ROLE(i)
222 ,stateTbl.STATUS(i)
223 ,stateTbl.TRANSACTION_STATE(i)
224 ,stateTbl.EFFECTIVE_DATE(i)
225 ,stateTbl.EFFECTIVE_DATE_OPTION(i)
226 ,stateTbl.LAST_UPDATE_ROLE(i)
227 ,stateTbl.PARENT_TRANSACTION_ID(i)
228 ,stateTbl.RELAUNCH_FUNCTION(i)
229 ,stateTbl.CREATED_BY(i)
230 ,stateTbl.CREATION_DATE(i)
231 ,stateTbl.LAST_UPDATE_DATE(i)
232 ,stateTbl.LAST_UPDATED_BY(i)
233 ,stateTbl.LAST_UPDATE_LOGIN(i)
234 );
235 END LOOP;
236 END IF;
237 END IF;
238 hr_utility.set_location('Leaving: '|| l_proc,10);
239 RETURN l_seq_id;
240
241 EXCEPTION
242 WHEN OTHERS THEN
243 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
244 If getMaxSeq%ISOPEN Then
245 CLOSE getMaxSeq;
246 End If;
247
248 If getMaxOldSeq%ISOPEN Then
249 CLOSE getMaxOldSeq;
250 End If;
251
252 raise;
253 END getTransStateSequence;
254 ---
255 ---
256 ---
257 Procedure RevertPerPayTransValues
258 (
259 P_TRANSACTION_STEP_ID IN NUMBER,
260 P_APPROVAL_HISTORY_ID IN NUMBER
261 )
262 IS
263 cursor csr_per_pay_trans_hist(C_APPROVAL_HISTORY_ID NUMBER)
264 is
265 select pay_transaction_id,
266 transaction_id ,
267 transaction_step_id,
268 item_type ,
269 item_key ,
270 pay_proposal_id ,
271 assignment_id ,
272 pay_basis_id ,
273 business_group_id ,
274 change_date ,
275 date_to ,
276 last_change_date ,
277 reason ,
278 multiple_components,
279 component_id ,
280 change_amount_n ,
281 change_percentage ,
282 proposed_salary_n ,
283 parent_pay_transaction_id,
284 prior_pay_proposal_id ,
285 prior_pay_transaction_id ,
286 prior_proposed_salary_n ,
287 prior_pay_basis_id ,
288 approved ,
289 next_perf_review_date,
290 next_sal_review_date ,
291 attribute_category ,
292 attribute1 ,
293 attribute2 ,
294 attribute3 ,
295 attribute4 ,
296 attribute5 ,
297 attribute6 ,
298 attribute7 ,
299 attribute8 ,
300 attribute9 ,
301 attribute10 ,
302 attribute11 ,
303 attribute12 ,
304 attribute13 ,
305 attribute14 ,
306 attribute15 ,
307 attribute16 ,
308 attribute17 ,
309 attribute18 ,
310 attribute19 ,
311 attribute20 ,
312 comments ,
313 last_update_date ,
314 last_updated_by ,
315 last_update_login ,
316 created_by ,
317 creation_date ,
318 object_version_number,
319 status ,
320 dml_operation ,
321 display_cd ,
322 txn_dml_operation
323 from per_pay_transaction_history
324 where approval_history_id = C_APPROVAL_HISTORY_ID
325 and transaction_step_id = P_TRANSACTION_STEP_ID;
326 BEGIN
327 --
328 delete from per_pay_transactions where transaction_step_id = P_TRANSACTION_STEP_ID;
329 --
330 for csr_per_pay_trans_hist_rec in csr_per_pay_trans_hist(P_APPROVAL_HISTORY_ID)
331 loop
332 --
333 Insert into per_pay_transactions
334 ( pay_transaction_id,
335 transaction_id ,
336 transaction_step_id,
337 item_type ,
338 item_key ,
339 pay_proposal_id ,
340 assignment_id ,
341 pay_basis_id ,
342 business_group_id ,
343 change_date ,
344 date_to ,
345 last_change_date ,
346 reason ,
347 multiple_components,
348 component_id ,
349 change_amount_n ,
350 change_percentage ,
351 proposed_salary_n ,
352 parent_pay_transaction_id,
353 prior_pay_proposal_id ,
354 prior_pay_transaction_id ,
355 prior_proposed_salary_n ,
356 prior_pay_basis_id ,
357 approved ,
358 next_perf_review_date,
359 next_sal_review_date ,
360 attribute_category ,
361 attribute1 ,
362 attribute2 ,
363 attribute3 ,
364 attribute4 ,
365 attribute5 ,
366 attribute6 ,
367 attribute7 ,
368 attribute8 ,
369 attribute9 ,
370 attribute10 ,
371 attribute11 ,
372 attribute12 ,
373 attribute13 ,
374 attribute14 ,
375 attribute15 ,
376 attribute16 ,
377 attribute17 ,
378 attribute18 ,
379 attribute19 ,
380 attribute20 ,
381 comments ,
382 last_update_date ,
383 last_updated_by ,
384 last_update_login ,
385 created_by ,
386 creation_date ,
387 object_version_number,
388 status ,
389 dml_operation ,
390 display_cd ,
391 txn_dml_operation)
392 values(
393 csr_per_pay_trans_hist_rec.pay_transaction_id,
394 csr_per_pay_trans_hist_rec.transaction_id ,
395 csr_per_pay_trans_hist_rec.transaction_step_id,
396 csr_per_pay_trans_hist_rec.item_type ,
397 csr_per_pay_trans_hist_rec.item_key ,
398 csr_per_pay_trans_hist_rec.pay_proposal_id ,
399 csr_per_pay_trans_hist_rec.assignment_id ,
400 csr_per_pay_trans_hist_rec.pay_basis_id ,
401 csr_per_pay_trans_hist_rec.business_group_id ,
402 csr_per_pay_trans_hist_rec.change_date ,
403 csr_per_pay_trans_hist_rec.date_to ,
404 csr_per_pay_trans_hist_rec.last_change_date ,
405 csr_per_pay_trans_hist_rec.reason ,
406 csr_per_pay_trans_hist_rec.multiple_components,
407 csr_per_pay_trans_hist_rec.component_id ,
408 csr_per_pay_trans_hist_rec.change_amount_n ,
409 csr_per_pay_trans_hist_rec.change_percentage ,
410 csr_per_pay_trans_hist_rec.proposed_salary_n ,
411 csr_per_pay_trans_hist_rec.parent_pay_transaction_id,
412 csr_per_pay_trans_hist_rec.prior_pay_proposal_id ,
413 csr_per_pay_trans_hist_rec.prior_pay_transaction_id ,
414 csr_per_pay_trans_hist_rec.prior_proposed_salary_n ,
415 csr_per_pay_trans_hist_rec.prior_pay_basis_id ,
416 csr_per_pay_trans_hist_rec.approved ,
417 csr_per_pay_trans_hist_rec.next_perf_review_date,
418 csr_per_pay_trans_hist_rec.next_sal_review_date ,
419 csr_per_pay_trans_hist_rec.attribute_category ,
420 csr_per_pay_trans_hist_rec.attribute1 ,
421 csr_per_pay_trans_hist_rec.attribute2 ,
422 csr_per_pay_trans_hist_rec.attribute3 ,
423 csr_per_pay_trans_hist_rec.attribute4 ,
424 csr_per_pay_trans_hist_rec.attribute5 ,
425 csr_per_pay_trans_hist_rec.attribute6 ,
426 csr_per_pay_trans_hist_rec.attribute7 ,
427 csr_per_pay_trans_hist_rec.attribute8 ,
428 csr_per_pay_trans_hist_rec.attribute9 ,
429 csr_per_pay_trans_hist_rec.attribute10 ,
430 csr_per_pay_trans_hist_rec.attribute11 ,
431 csr_per_pay_trans_hist_rec.attribute12 ,
432 csr_per_pay_trans_hist_rec.attribute13 ,
433 csr_per_pay_trans_hist_rec.attribute14 ,
434 csr_per_pay_trans_hist_rec.attribute15 ,
435 csr_per_pay_trans_hist_rec.attribute16 ,
436 csr_per_pay_trans_hist_rec.attribute17 ,
437 csr_per_pay_trans_hist_rec.attribute18 ,
438 csr_per_pay_trans_hist_rec.attribute19 ,
439 csr_per_pay_trans_hist_rec.attribute20 ,
440 csr_per_pay_trans_hist_rec.comments ,
441 csr_per_pay_trans_hist_rec.last_update_date ,
442 csr_per_pay_trans_hist_rec.last_updated_by ,
443 csr_per_pay_trans_hist_rec.last_update_login ,
444 csr_per_pay_trans_hist_rec.created_by ,
445 csr_per_pay_trans_hist_rec.creation_date ,
446 csr_per_pay_trans_hist_rec.object_version_number,
447 csr_per_pay_trans_hist_rec.status ,
448 csr_per_pay_trans_hist_rec.dml_operation ,
449 csr_per_pay_trans_hist_rec.display_cd ,
450 csr_per_pay_trans_hist_rec.txn_dml_operation);
451 --
452 end loop;
453 --
454 END RevertPerPayTransValues;
455 ---
456 ---
457 ---
458 Procedure RevertTransSteps
459 (
460 P_TRANSACTION_ID IN NUMBER
461 ,P_APPROVAL_HISTORY_ID IN NUMBER
462 )
463 IS
464 CURSOR cur_step_hist IS
465 SELECT
466 STEP_HISTORY_ID
467 ,TRANSACTION_HISTORY_ID
468 ,API_NAME
469 ,API_DISPLAY_NAME
470 ,PROCESSING_ORDER
471 ,CREATED_BY
472 ,CREATION_DATE
473 ,LAST_UPDATE_DATE
474 ,LAST_UPDATED_BY
475 ,LAST_UPDATE_LOGIN
476 ,ITEM_TYPE
477 ,ITEM_KEY
478 ,ACTIVITY_ID
479 ,OBJECT_TYPE
480 ,OBJECT_NAME
481 ,OBJECT_IDENTIFIER
482 ,OBJECT_STATE
483 ,PK1
484 ,PK2
485 ,PK3
486 ,PK4
487 ,PK5
488 ,INFORMATION_CATEGORY
489 ,INFORMATION1
490 ,INFORMATION2
491 ,INFORMATION3
492 ,INFORMATION4
493 ,INFORMATION5
494 ,INFORMATION6
495 ,INFORMATION7
496 ,INFORMATION8
497 ,INFORMATION9
498 ,INFORMATION10
499 ,INFORMATION11
500 ,INFORMATION12
501 ,INFORMATION13
502 ,INFORMATION14
503 ,INFORMATION15
504 ,INFORMATION16
505 ,INFORMATION17
506 ,INFORMATION18
507 ,INFORMATION19
508 ,INFORMATION20
509 ,INFORMATION21
510 ,INFORMATION22
511 ,INFORMATION23
512 ,INFORMATION24
513 ,INFORMATION25
514 ,INFORMATION26
515 ,INFORMATION27
516 ,INFORMATION28
517 ,INFORMATION29
518 ,INFORMATION30
519 FROM PQH_SS_STEP_HISTORY
520 WHERE TRANSACTION_HISTORY_ID = P_TRANSACTION_ID
521 AND APPROVAL_HISTORY_ID = P_APPROVAL_HISTORY_ID;
522
523 l_cnt Integer;
524 l_proc constant varchar2(100) := g_package || ' RevertTransSteps';
525 TxStepTbl TransStepTbl;
526 BEGIN
527 hr_utility.set_location('Entering: '|| l_proc,5);
528 OPEN cur_step_hist;
529 FETCH cur_step_hist BULK COLLECT INTO
530 TxStepTbl.STEP_HISTORY_ID
531 ,TxStepTbl.TRANSACTION_HISTORY_ID
532 ,TxStepTbl.API_NAME
533 ,TxStepTbl.API_DISPLAY_NAME
534 ,TxStepTbl.PROCESSING_ORDER
535 ,TxStepTbl.CREATED_BY
536 ,TxStepTbl.CREATION_DATE
537 ,TxStepTbl.LAST_UPDATE_DATE
538 ,TxStepTbl.LAST_UPDATED_BY
539 ,TxStepTbl.LAST_UPDATE_LOGIN
540 ,TxStepTbl.ITEM_TYPE
541 ,TxStepTbl.ITEM_KEY
542 ,TxStepTbl.ACTIVITY_ID
543 ,TxStepTbl.OBJECT_TYPE
544 ,TxStepTbl.OBJECT_NAME
545 ,TxStepTbl.OBJECT_IDENTIFIER
546 ,TxStepTbl.OBJECT_STATE
547 ,TxStepTbl.PK1
548 ,TxStepTbl.PK2
549 ,TxStepTbl.PK3
550 ,TxStepTbl.PK4
551 ,TxStepTbl.PK5
552 ,TxStepTbl.INFORMATION_CATEGORY
553 ,TxStepTbl.INFORMATION1
554 ,TxStepTbl.INFORMATION2
555 ,TxStepTbl.INFORMATION3
556 ,TxStepTbl.INFORMATION4
557 ,TxStepTbl.INFORMATION5
558 ,TxStepTbl.INFORMATION6
559 ,TxStepTbl.INFORMATION7
560 ,TxStepTbl.INFORMATION8
561 ,TxStepTbl.INFORMATION9
562 ,TxStepTbl.INFORMATION10
563 ,TxStepTbl.INFORMATION11
564 ,TxStepTbl.INFORMATION12
565 ,TxStepTbl.INFORMATION13
566 ,TxStepTbl.INFORMATION14
567 ,TxStepTbl.INFORMATION15
568 ,TxStepTbl.INFORMATION16
569 ,TxStepTbl.INFORMATION17
570 ,TxStepTbl.INFORMATION18
571 ,TxStepTbl.INFORMATION19
572 ,TxStepTbl.INFORMATION20
573 ,TxStepTbl.INFORMATION21
574 ,TxStepTbl.INFORMATION22
575 ,TxStepTbl.INFORMATION23
576 ,TxStepTbl.INFORMATION24
577 ,TxStepTbl.INFORMATION25
578 ,TxStepTbl.INFORMATION26
579 ,TxStepTbl.INFORMATION27
580 ,TxStepTbl.INFORMATION28
581 ,TxStepTbl.INFORMATION29
582 ,TxStepTbl.INFORMATION30;
583 CLOSE cur_step_hist;
584
585 l_cnt := TxStepTbl.STEP_HISTORY_ID.count;
586
587 FOR i in 1.. l_cnt LOOP
588 INSERT INTO HR_API_TRANSACTION_STEPS
589 (
590 TRANSACTION_STEP_ID
591 ,TRANSACTION_ID
592 ,API_NAME
593 ,API_DISPLAY_NAME
594 ,PROCESSING_ORDER
595 ,CREATOR_PERSON_ID
596 ,OBJECT_VERSION_NUMBER
597 ,CREATED_BY
598 ,CREATION_DATE
599 ,LAST_UPDATE_DATE
600 ,LAST_UPDATED_BY
601 ,LAST_UPDATE_LOGIN
602 ,ITEM_TYPE
603 ,ITEM_KEY
604 ,ACTIVITY_ID
605 ,OBJECT_TYPE
606 ,OBJECT_NAME
607 ,OBJECT_IDENTIFIER
608 ,OBJECT_STATE
609 ,PK1
610 ,PK2
611 ,PK3
612 ,PK4
613 ,PK5
614 ,INFORMATION_CATEGORY
615 ,INFORMATION1
616 ,INFORMATION2
617 ,INFORMATION3
618 ,INFORMATION4
619 ,INFORMATION5
620 ,INFORMATION6
621 ,INFORMATION7
622 ,INFORMATION8
623 ,INFORMATION9
624 ,INFORMATION10
625 ,INFORMATION11
626 ,INFORMATION12
627 ,INFORMATION13
628 ,INFORMATION14
629 ,INFORMATION15
630 ,INFORMATION16
631 ,INFORMATION17
632 ,INFORMATION18
633 ,INFORMATION19
634 ,INFORMATION20
635 ,INFORMATION21
636 ,INFORMATION22
637 ,INFORMATION23
638 ,INFORMATION24
639 ,INFORMATION25
640 ,INFORMATION26
641 ,INFORMATION27
642 ,INFORMATION28
643 ,INFORMATION29
644 ,INFORMATION30
645 )
646 values
647 (
648 TxStepTbl.STEP_HISTORY_ID(i)
649 ,TxStepTbl.TRANSACTION_HISTORY_ID(i)
650 ,TxStepTbl.API_NAME(i)
651 ,TxStepTbl.API_DISPLAY_NAME(i)
652 ,TxStepTbl.PROCESSING_ORDER(i)
653 ,0 -- creator_person_id
654 ,0 -- OVN
655 ,TxStepTbl.CREATED_BY(i)
656 ,TxStepTbl.CREATION_DATE(i)
657 ,TxStepTbl.LAST_UPDATE_DATE(i)
658 ,TxStepTbl.LAST_UPDATED_BY(i)
659 ,TxStepTbl.LAST_UPDATE_LOGIN(i)
660 ,TxStepTbl.ITEM_TYPE(i)
661 ,TxStepTbl.ITEM_KEY(i)
662 ,TxStepTbl.ACTIVITY_ID(i)
663 ,TxStepTbl.OBJECT_TYPE(i)
664 ,TxStepTbl.OBJECT_NAME(i)
665 ,TxStepTbl.OBJECT_IDENTIFIER(i)
666 ,TxStepTbl.OBJECT_STATE(i)
667 ,TxStepTbl.PK1(i)
668 ,TxStepTbl.PK2(i)
669 ,TxStepTbl.PK3(i)
670 ,TxStepTbl.PK4(i)
671 ,TxStepTbl.PK5(i)
672 ,TxStepTbl.INFORMATION_CATEGORY(i)
673 ,TxStepTbl.INFORMATION1(i)
674 ,TxStepTbl.INFORMATION2(i)
675 ,TxStepTbl.INFORMATION3(i)
676 ,TxStepTbl.INFORMATION4(i)
677 ,TxStepTbl.INFORMATION5(i)
678 ,TxStepTbl.INFORMATION6(i)
679 ,TxStepTbl.INFORMATION7(i)
680 ,TxStepTbl.INFORMATION8(i)
681 ,TxStepTbl.INFORMATION9(i)
682 ,TxStepTbl.INFORMATION10(i)
683 ,TxStepTbl.INFORMATION11(i)
684 ,TxStepTbl.INFORMATION12(i)
685 ,TxStepTbl.INFORMATION13(i)
686 ,TxStepTbl.INFORMATION14(i)
687 ,TxStepTbl.INFORMATION15(i)
688 ,TxStepTbl.INFORMATION16(i)
689 ,TxStepTbl.INFORMATION17(i)
690 ,TxStepTbl.INFORMATION18(i)
691 ,TxStepTbl.INFORMATION19(i)
692 ,TxStepTbl.INFORMATION20(i)
693 ,TxStepTbl.INFORMATION21(i)
694 ,TxStepTbl.INFORMATION22(i)
695 ,TxStepTbl.INFORMATION23(i)
696 ,TxStepTbl.INFORMATION24(i)
697 ,TxStepTbl.INFORMATION25(i)
698 ,TxStepTbl.INFORMATION26(i)
699 ,TxStepTbl.INFORMATION27(i)
700 ,TxStepTbl.INFORMATION28(i)
701 ,TxStepTbl.INFORMATION29(i)
702 ,TxStepTbl.INFORMATION30(i)
703 );
704 RevertPerPayTransValues
705 (
706 TxStepTbl.STEP_HISTORY_ID(i),
707 P_APPROVAL_HISTORY_ID
708 );
709 END LOOP;
710 hr_utility.set_location('Leaving: '|| l_proc,10);
711 Exception
712 when OTHERS then
713 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
714 If cur_step_hist%IsOpen Then
715 CLOSE cur_step_hist;
716 End If;
717 raise;
718 END RevertTransSteps;
719
720 Procedure RevertTransValues
721 (
722 P_TRANSACTION_ID IN NUMBER
723 ,P_APPROVAL_HISTORY_ID IN NUMBER
724 )
725 IS
726
727 CURSOR cur_trans_value IS
728 SELECT
729 transaction_value_id
730 ,step_history_id
731 ,datatype
732 ,name
733 ,value
734 ,original_value
735 ,created_by
736 ,creation_date
737 ,last_update_date
738 ,last_updated_by
739 ,last_update_login
740 FROM PQH_SS_VALUE_HISTORY
741 Where step_history_id in
742 ( SELECT STEP_HISTORY_ID
743 FROM PQH_SS_STEP_HISTORY
744 WHERE TRANSACTION_HISTORY_ID = P_TRANSACTION_ID
745 AND APPROVAL_HISTORY_ID = P_APPROVAL_HISTORY_ID)
746 AND APPROVAL_HISTORY_ID = P_APPROVAL_HISTORY_ID;
747
748 l_cnt integer;
749 l_proc constant varchar2(100) := g_package || ' RevertTransValues';
750 TxValueTbl TransValueTbl;
751
752 BEGIN
753 hr_utility.set_location('Entering: '|| l_proc,5);
754 OPEN cur_trans_value;
755 FETCH cur_trans_value BULK COLLECT INTO
756 TxValueTbl.transaction_value_id
757 ,TxValueTbl.step_history_id
758 ,TxValueTbl.datatype
759 ,TxValueTbl.name
760 ,TxValueTbl.value
761 ,TxValueTbl.original_value
762 ,TxValueTbl.created_by
763 ,TxValueTbl.creation_date
764 ,TxValueTbl.last_update_date
765 ,TxValueTbl.last_updated_by
766 ,TxValueTbl.last_update_login;
767 CLOSE cur_trans_value;
768
769 l_cnt := TxValueTbl.transaction_value_id.count;
770 For i in 1.. l_cnt Loop
771 INSERT INTO hr_api_transaction_values
772 (
773 TRANSACTION_VALUE_ID
774 ,TRANSACTION_STEP_ID
775 ,DATATYPE
776 ,NAME
777 ,VARCHAR2_VALUE
778 ,NUMBER_VALUE
779 ,DATE_VALUE
780 ,ORIGINAL_VARCHAR2_VALUE
781 ,ORIGINAL_NUMBER_VALUE
782 ,ORIGINAL_DATE_VALUE
783 ,CREATED_BY
784 ,CREATION_DATE
785 ,LAST_UPDATE_DATE
786 ,LAST_UPDATED_BY
787 ,LAST_UPDATE_LOGIN
788 )
789 VALUES
790 (
791 TxValueTbl.transaction_value_id(i)
792 ,TxValueTbl.step_history_id(i)
793 ,TxValueTbl.datatype(i)
794 ,TxValueTbl.name(i)
795 ,decode (TxValueTbl.datatype(i), 'VARCHAR2', TxValueTbl.value(i), null)
796 ,decode (TxValueTbl.datatype(i), 'NUMBER', decode(TxValueTbl.value(i), null, null, to_number(TxValueTbl.value(i))), null)
797 ,decode (TxValueTbl.datatype(i), 'DATE', decode(TxValueTbl.value(i), null, null, fnd_date.canonical_to_date(TxValueTbl.value(i))), null)
798 ,decode (TxValueTbl.datatype(i), 'VARCHAR2', TxValueTbl.original_value(i), null)
799 ,decode (TxValueTbl.datatype(i), 'NUMBER', decode(TxValueTbl.original_value(i), null, null, to_number(TxValueTbl.original_value(i))), null)
800 ,decode (TxValueTbl.datatype(i), 'DATE', decode(TxValueTbl.original_value(i), null, null, fnd_date.canonical_to_date(TxValueTbl.original_value(i))), null)
801 ,TxValueTbl.created_by(i)
802 ,TxValueTbl.creation_date(i)
803 ,TxValueTbl.last_update_date(i)
804 ,TxValueTbl.last_updated_by(i)
805 ,TxValueTbl.last_update_login(i)
806 );
807 END LOOP;
808 hr_utility.set_location('Calling: RevertPerPayTransValues '|| l_proc,10);
809 --
810 --RevertPerPayTransValues(P_APPROVAL_HISTORY_ID);
811 --
812 hr_utility.set_location('Leaving: '|| l_proc,20);
813 Exception
814 when OTHERS then
815 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
816 If cur_trans_value%IsOpen Then
817 CLOSE cur_trans_value;
818 End If;
819 raise;
820 END RevertTransValues;
821
822 procedure correctOldTxnHistoryData(P_TRANSACTION_ID IN NUMBER)
823 as
824 -- local variables
825 l_temp_found VarChar2(1);
826 l_proc constant varchar2(100) := g_package || ' correctOldTxnHistoryData';
827 ln_min_seq_id number;
828 ln_max_seq_id number;
829 -- Cursor to return Min and Max seq for old transactions step hist
830 CURSOR getMinMaxOldSeq IS
831 SELECT MIN(APPROVAL_HISTORY_ID),MAX(APPROVAL_HISTORY_ID)
832 FROM PQH_SS_STEP_HISTORY
833 WHERE TRANSACTION_HISTORY_ID = P_TRANSACTION_ID;
834
835
836 CURSOR origValUpgTrans IS
837 SELECT TRANSACTION_VALUE_ID, VALUE, ORIGINAL_VALUE
838 FROM PQH_SS_STEP_HISTORY step, PQH_SS_VALUE_HISTORY val
839 WHERE step.TRANSACTION_HISTORY_ID = P_TRANSACTION_ID
840 and step.step_history_id = val.step_history_id
841 and step.approval_history_id = val.approval_history_id
842 and val.approval_history_id = -1;
843
844 CURSOR PsuedoValUpgTrans IS
845 SELECT 1
846 FROM PQH_SS_STEP_HISTORY step, PQH_SS_VALUE_HISTORY val
847 WHERE step.TRANSACTION_HISTORY_ID = P_TRANSACTION_ID
848 and step.step_history_id = val.step_history_id
849 and step.approval_history_id = val.approval_history_id
850 and val.approval_history_id = 0
851 and val.value IS NOT NULL;
852
853
854 Begin
855 hr_utility.set_location('Entering: '|| l_proc,5);
856 -- archive data correction logic
857 -- correct ONLY if max history id is 0 and min -1
858 -- delete current txn values all the time if max history id is >0
859 -- if max history id is 0 and module is LOA DONOT Delete
860
861 -- get the min and max sequence id of archive step history
862 begin
863 open getMinMaxOldSeq;
864 FETCH getMinMaxOldSeq into
865 ln_min_seq_id,ln_max_seq_id;
866 CLOSE getMinMaxOldSeq;
867
868 exception
869 when others then
870 raise;
871 end;
872
873 IF ln_max_seq_id = 0 and ln_min_seq_id = -1 THEN
874 OPEN PsuedoValUpgTrans;
875 FETCH PsuedoValUpgTrans into l_temp_found;
876 IF PsuedoValUpgTrans%NOTFOUND THEN
877 /* Copy values stored for approval_history_id = -1
878 to values stored for approval_history_id = 0 */
879 FOR t in origValUpgTrans LOOP
880 UPDATE pqh_ss_value_history
881 SET VALUE = t.VALUE, ORIGINAL_VALUE = t.ORIGINAL_VALUE
882 WHERE TRANSACTION_VALUE_ID = t.TRANSACTION_VALUE_ID
883 AND APPROVAL_HISTORY_ID = 0;
884 END LOOP;
885 END IF;
886 CLOSE PsuedoValUpgTrans;
887 END IF;
888
889 hr_utility.set_location('Leaving: '|| l_proc,10);
890
891 end correctOldTxnHistoryData ;
892
893
894
895 --
896 -- ---------------------------------------------------------------------- --
897 -- --------------------<RevertToLastSave>------------------------- --
898 -- ---------------------------------------------------------------------- --
899 --
900
901 Procedure RevertToLastSave
902 (
903 P_TRANSACTION_ID IN NUMBER
904 )
905 IS
906 l_seq_id NUMBER(5);
907 lv_found VarChar2(2);
908 l_proc constant varchar2(100) := g_package || ' RevertToLastSave';
909 Begin
910 hr_utility.set_location('Entering: '|| l_proc,5);
911 l_seq_id := getTransStateSequence(P_TRANSACTION_ID => P_TRANSACTION_ID);
912
913 -- Copy Transaction details from history.
914 UPDATE HR_API_TRANSACTIONS
915 SET (
916 STATUS
917 ,TRANSACTION_STATE
918 ,TRANSACTION_EFFECTIVE_DATE
919 ,EFFECTIVE_DATE_OPTION
920 ,PARENT_TRANSACTION_ID
921 ,RELAUNCH_FUNCTION
922 ,TRANSACTION_DOCUMENT
923 )
924 =
925 ( SELECT
926 STATUS
927 ,TRANSACTION_STATE
928 ,EFFECTIVE_DATE
929 ,EFFECTIVE_DATE_OPTION
930 ,PARENT_TRANSACTION_ID
931 ,RELAUNCH_FUNCTION
932 ,TRANSACTION_DOCUMENT
933 FROM PQH_SS_TRANS_STATE_HISTORY
934 WHERE TRANSACTION_HISTORY_ID = P_TRANSACTION_ID
935 AND approval_history_id = l_seq_id
936 )
937 WHERE TRANSACTION_ID = P_TRANSACTION_ID
938 AND exists (SELECT 1 FROM PQH_SS_TRANS_STATE_HISTORY
939 WHERE TRANSACTION_HISTORY_ID = P_TRANSACTION_ID
940 AND approval_history_id = l_seq_id);
941
942 -- special handling for old txn in progress
943 -- archive data correction logic
944 -- correct ONLY if max history id is 0 and min -1
945 if(l_seq_id =0) then
946 correctOldTxnHistoryData(P_TRANSACTION_ID);
947 end if;
948
949 -- delete current txn values all the time if max history id is >0
950 -- if max history id is 0 and module is LOA DONOT Delete step
951 -- and value history
952 if(l_seq_id =0) then
953 begin
954 select 'Y' into lv_found
955 from PQH_SS_STEP_HISTORY
956 where TRANSACTION_HISTORY_ID =P_TRANSACTION_ID
957 and API_NAME='HR_LOA_SS.PROCESS_API';
958 exception
959 when no_data_found then
960 lv_found :='N';
961 when others then
962 raise;
963 end;
964
965 if(lv_found is not null and lv_found='Y') then
966 -- no more further processing return
967 return;
968 end if;
969 end if;
970 -- end special handling
971
972
973 -- DELETE Steps and Values First.
974 DELETE HR_API_TRANSACTION_VALUES
975 WHERE TRANSACTION_STEP_ID in
976 ( SELECT TRANSACTION_STEP_ID
977 FROM HR_API_TRANSACTION_STEPS
978 WHERE TRANSACTION_ID = P_TRANSACTION_ID);
979
980 DELETE HR_API_TRANSACTION_STEPS
981 WHERE TRANSACTION_ID = P_TRANSACTION_ID;
982
983 -- Copy Steps from history to transaction tables.
984 RevertTransSteps
985 (
986 P_TRANSACTION_ID => P_TRANSACTION_ID
987 ,P_APPROVAL_HISTORY_ID => l_seq_id
988 );
989 -- Copy Steps from history to transaction tables.
990 RevertTransValues
991 (
992 P_TRANSACTION_ID => P_TRANSACTION_ID
993 ,P_APPROVAL_HISTORY_ID => l_seq_id
994 );
995 hr_utility.set_location('Leaving: '|| l_proc,10);
996 Exception
997 when OTHERS then
998 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
999 raise;
1000 End RevertToLastSave;
1001
1002 Procedure DeleteStaleData
1003 (
1004 P_TRANSACTION_ID IN NUMBER
1005 ,P_ACTION IN VARCHAR DEFAULT 'SFL'
1006 )
1007 IS
1008 l_seq_id NUMBER(5);
1009 l_action VARCHAR2(30);
1010 l_proc constant varchar2(100) := g_package || ' DeleteStaleData';
1011
1012 CURSOR cur_chk_stale_data IS
1013 select action
1014 from pqh_ss_approval_history
1015 WHERE TRANSACTION_HISTORY_ID = P_TRANSACTION_ID
1016 and approval_history_id = l_seq_id
1017 and action = 'SFL';
1018
1019 Begin
1020 --hr_utility.trace_on(null, 'TIGER');
1021 --g_debug := TRUE;
1022 hr_utility.set_location('Entering: '|| l_proc,5);
1023 IF P_ACTION NOT IN ('QUESTION', 'ANSWER') THEN
1024 l_seq_id := getTransStateSequence(P_TRANSACTION_ID => P_TRANSACTION_ID);
1025
1026 OPEN cur_chk_stale_data;
1027 FETCH cur_chk_stale_data into l_action;
1028
1029 If cur_chk_stale_data%found AND l_seq_id IS NOT NULL THEN
1030 hr_utility.set_location('Entering If: '|| l_proc,15);
1031 -- Delete State History
1032 DELETE pqh_ss_trans_state_history
1033 WHERE TRANSACTION_HISTORY_ID = P_TRANSACTION_ID
1034 and approval_history_id = l_seq_id;
1035
1036 -- Delete Routing History
1037 DELETE pqh_ss_approval_history
1038 WHERE TRANSACTION_HISTORY_ID = P_TRANSACTION_ID
1039 and approval_history_id = l_seq_id;
1040
1041 -- TODO: Delete Value History
1042 DELETE pqh_ss_value_history
1043 WHERE approval_history_id = l_seq_id
1044 and step_history_id in (
1045 SELECT step_history_id
1046 FROM pqh_ss_step_history
1047 WHERE transaction_history_id = P_TRANSACTION_ID
1048 and approval_history_id = l_seq_id
1049 );
1050
1051 -- Delete Steps History
1052 DELETE pqh_ss_step_history
1053 WHERE TRANSACTION_HISTORY_ID = P_TRANSACTION_ID
1054 and approval_history_id = l_seq_id;
1055
1056 -- Delete Per Pay Trans History
1057 DELETE per_pay_transaction_history
1058 WHERE TRANSACTION_ID = P_TRANSACTION_ID
1059 and approval_history_id = l_seq_id;
1060
1061 IF P_ACTION NOT IN ('SFL', 'SUBMIT', 'RESUBMIT') THEN
1062 RevertToLastSave(P_TRANSACTION_ID);
1063 END IF;
1064
1065 END If;
1066 CLOSE cur_chk_stale_data;
1067 END IF;
1068 hr_utility.set_location('Leaving: '|| l_proc,10);
1069 Exception
1070 when OTHERS then
1071 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1072 If cur_chk_stale_data%IsOpen Then
1073 CLOSE cur_chk_stale_data;
1074 End If;
1075 raise;
1076 End DeleteStaleData;
1077
1078 --
1079 -- ---------------------------------------------------------------------- --
1080 -- -----------------------<SaveRoutingHistory>--------------------------- --
1081 -- ---------------------------------------------------------------------- --
1082 --
1083
1084 Procedure SaveRoutingHistory
1085 (
1086 P_TRANSACTION_ID IN OUT NOCOPY NUMBER
1087 ,P_APPROVAL_HISTORY_ID IN OUT NOCOPY NUMBER
1088 ,P_NOTIFICATION_ID IN NUMBER
1089 ,P_ACTION IN VARCHAR2
1090 ,P_USER_NAME IN VARCHAR2
1091 ,P_USER_COMMENT IN VARCHAR2 default hr_api.g_varchar2
1092 )
1093 IS
1094 l_proc constant varchar2(100) := g_package || 'SaveRoutingHistory';
1095 lv_orig_system wf_roles.orig_system%type;
1096 lv_orig_system_id wf_roles.orig_system_id%type;
1097
1098 CURSOR cur_trans_details IS
1099 SELECT ITEM_TYPE, ITEM_KEY, TRANSACTION_EFFECTIVE_DATE, EFFECTIVE_DATE_OPTION
1100 FROM HR_API_TRANSACTIONS
1101 WHERE TRANSACTION_ID = P_TRANSACTION_ID;
1102
1103 l_row cur_trans_details%RowType;
1104 Begin
1105 hr_utility.set_location('Entering: '|| l_proc,5);
1106 Open cur_trans_details;
1107 Fetch cur_trans_details into l_row;
1108 CLOSE cur_trans_details;
1109
1110 pqh_tah_ins.set_base_key_value(
1111 p_approval_history_id => P_APPROVAL_HISTORY_ID
1112 ,p_transaction_history_id => P_TRANSACTION_ID
1113 );
1114
1115 -- get the orig system and system id of the user passed
1116 wf_directory.getroleorigsysinfo(p_user_name,lv_orig_system,lv_orig_system_id);
1117
1118 pqh_tah_ins.ins(
1119 p_transaction_effective_date => l_row.TRANSACTION_EFFECTIVE_DATE
1120 ,p_action => P_ACTION
1121 ,p_user_name => P_USER_NAME
1122 ,p_orig_system => lv_orig_system
1123 ,p_orig_system_id => lv_orig_system_id
1124 ,p_transaction_item_type => l_row.ITEM_TYPE
1125 ,p_transaction_item_key => l_row.ITEM_KEY
1126 ,p_effective_date_option => l_row.EFFECTIVE_DATE_OPTION
1127 ,p_notification_id => P_NOTIFICATION_ID
1128 ,p_user_comment => P_USER_COMMENT
1129 ,p_approval_history_id => P_APPROVAL_HISTORY_ID
1130 ,p_transaction_history_id => P_TRANSACTION_ID
1131 );
1132 hr_utility.set_location('Leaving: '|| l_proc,10);
1133 Exception
1134 when OTHERS then
1135 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1136 If cur_trans_details%IsOpen Then
1137 CLOSE cur_trans_details;
1138 End If;
1139 raise;
1140 End SaveRoutingHistory;
1141 ---
1142 ---
1143 ---
1144 procedure SavePerPayTransHistory(
1145 P_TRANSACTION_STEP_ID IN NUMBER,
1146 P_APPROVAL_HISTORY_ID IN NUMBER
1147 )
1148 IS
1149
1150 cursor csr_per_pay_trans
1151 is
1152 select pay_transaction_id,
1153 transaction_id ,
1154 transaction_step_id,
1155 item_type ,
1156 item_key ,
1157 pay_proposal_id ,
1158 assignment_id ,
1159 pay_basis_id ,
1160 business_group_id ,
1161 change_date ,
1162 date_to ,
1163 last_change_date ,
1164 reason ,
1165 multiple_components,
1166 component_id ,
1167 change_amount_n ,
1168 change_percentage ,
1169 proposed_salary_n ,
1170 parent_pay_transaction_id,
1171 prior_pay_proposal_id ,
1172 prior_pay_transaction_id ,
1173 prior_proposed_salary_n ,
1174 prior_pay_basis_id ,
1175 approved ,
1176 next_perf_review_date,
1177 next_sal_review_date ,
1178 attribute_category ,
1179 attribute1 ,
1180 attribute2 ,
1181 attribute3 ,
1182 attribute4 ,
1183 attribute5 ,
1184 attribute6 ,
1185 attribute7 ,
1186 attribute8 ,
1187 attribute9 ,
1188 attribute10 ,
1189 attribute11 ,
1190 attribute12 ,
1191 attribute13 ,
1192 attribute14 ,
1193 attribute15 ,
1194 attribute16 ,
1195 attribute17 ,
1196 attribute18 ,
1197 attribute19 ,
1198 attribute20 ,
1199 comments ,
1200 last_update_date ,
1201 last_updated_by ,
1202 last_update_login ,
1203 created_by ,
1204 creation_date ,
1205 object_version_number,
1206 status ,
1207 dml_operation ,
1208 display_cd ,
1209 txn_dml_operation
1210 from per_pay_transactions
1211 where transaction_step_id = P_TRANSACTION_STEP_ID;
1212
1213 BEGIN
1214 for csr_per_pay_trans_rec in csr_per_pay_trans
1215 loop
1216 Insert into per_pay_transaction_history
1217 ( pay_transaction_id,
1218 APPROVAL_HISTORY_ID,
1219 transaction_id ,
1220 transaction_step_id,
1221 item_type ,
1222 item_key ,
1223 pay_proposal_id ,
1224 assignment_id ,
1225 pay_basis_id ,
1226 business_group_id ,
1227 change_date ,
1228 date_to ,
1229 last_change_date ,
1230 reason ,
1231 multiple_components,
1232 component_id ,
1233 change_amount_n ,
1234 change_percentage ,
1235 proposed_salary_n ,
1236 parent_pay_transaction_id,
1237 prior_pay_proposal_id ,
1238 prior_pay_transaction_id ,
1239 prior_proposed_salary_n ,
1240 prior_pay_basis_id ,
1241 approved ,
1242 next_perf_review_date,
1243 next_sal_review_date ,
1244 attribute_category ,
1245 attribute1 ,
1246 attribute2 ,
1247 attribute3 ,
1248 attribute4 ,
1249 attribute5 ,
1250 attribute6 ,
1251 attribute7 ,
1252 attribute8 ,
1253 attribute9 ,
1254 attribute10 ,
1255 attribute11 ,
1256 attribute12 ,
1257 attribute13 ,
1258 attribute14 ,
1259 attribute15 ,
1260 attribute16 ,
1261 attribute17 ,
1262 attribute18 ,
1263 attribute19 ,
1264 attribute20 ,
1265 comments ,
1266 last_update_date ,
1267 last_updated_by ,
1268 last_update_login ,
1269 created_by ,
1270 creation_date ,
1271 object_version_number,
1272 status ,
1273 dml_operation ,
1274 display_cd ,
1275 txn_dml_operation)
1276 values(
1277 csr_per_pay_trans_rec.pay_transaction_id,
1278 P_APPROVAL_HISTORY_ID,
1279 csr_per_pay_trans_rec.transaction_id ,
1280 csr_per_pay_trans_rec.transaction_step_id,
1281 csr_per_pay_trans_rec.item_type ,
1282 csr_per_pay_trans_rec.item_key ,
1283 csr_per_pay_trans_rec.pay_proposal_id ,
1284 csr_per_pay_trans_rec.assignment_id ,
1285 csr_per_pay_trans_rec.pay_basis_id ,
1286 csr_per_pay_trans_rec.business_group_id ,
1287 csr_per_pay_trans_rec.change_date ,
1288 csr_per_pay_trans_rec.date_to ,
1289 csr_per_pay_trans_rec.last_change_date ,
1290 csr_per_pay_trans_rec.reason ,
1291 csr_per_pay_trans_rec.multiple_components,
1292 csr_per_pay_trans_rec.component_id ,
1293 csr_per_pay_trans_rec.change_amount_n ,
1294 csr_per_pay_trans_rec.change_percentage ,
1295 csr_per_pay_trans_rec.proposed_salary_n ,
1296 csr_per_pay_trans_rec.parent_pay_transaction_id,
1297 csr_per_pay_trans_rec.prior_pay_proposal_id ,
1298 csr_per_pay_trans_rec.prior_pay_transaction_id ,
1299 csr_per_pay_trans_rec.prior_proposed_salary_n ,
1300 csr_per_pay_trans_rec.prior_pay_basis_id ,
1301 csr_per_pay_trans_rec.approved ,
1302 csr_per_pay_trans_rec.next_perf_review_date,
1303 csr_per_pay_trans_rec.next_sal_review_date ,
1304 csr_per_pay_trans_rec.attribute_category ,
1305 csr_per_pay_trans_rec.attribute1 ,
1306 csr_per_pay_trans_rec.attribute2 ,
1307 csr_per_pay_trans_rec.attribute3 ,
1308 csr_per_pay_trans_rec.attribute4 ,
1309 csr_per_pay_trans_rec.attribute5 ,
1310 csr_per_pay_trans_rec.attribute6 ,
1311 csr_per_pay_trans_rec.attribute7 ,
1312 csr_per_pay_trans_rec.attribute8 ,
1313 csr_per_pay_trans_rec.attribute9 ,
1314 csr_per_pay_trans_rec.attribute10 ,
1315 csr_per_pay_trans_rec.attribute11 ,
1316 csr_per_pay_trans_rec.attribute12 ,
1317 csr_per_pay_trans_rec.attribute13 ,
1318 csr_per_pay_trans_rec.attribute14 ,
1319 csr_per_pay_trans_rec.attribute15 ,
1320 csr_per_pay_trans_rec.attribute16 ,
1321 csr_per_pay_trans_rec.attribute17 ,
1322 csr_per_pay_trans_rec.attribute18 ,
1323 csr_per_pay_trans_rec.attribute19 ,
1324 csr_per_pay_trans_rec.attribute20 ,
1325 csr_per_pay_trans_rec.comments ,
1326 csr_per_pay_trans_rec.last_update_date ,
1327 csr_per_pay_trans_rec.last_updated_by ,
1328 csr_per_pay_trans_rec.last_update_login ,
1329 csr_per_pay_trans_rec.created_by ,
1330 csr_per_pay_trans_rec.creation_date ,
1331 csr_per_pay_trans_rec.object_version_number,
1332 csr_per_pay_trans_rec.status ,
1333 csr_per_pay_trans_rec.dml_operation ,
1334 csr_per_pay_trans_rec.display_cd ,
1335 csr_per_pay_trans_rec.txn_dml_operation);
1336 end loop;
1337
1338 END savePerPayTRansHistory;
1339 ---
1340 ---
1341 ---
1342 Procedure SaveTransValueHistory
1343 (
1344 P_TRANSACTION_STEP_ID IN NUMBER
1345 ,P_APPROVAL_HISTORY_ID IN NUMBER
1346 )
1347 IS
1348 CURSOR cur_trans_value IS
1349 select transaction_value_id ,
1350 datatype ,
1351 name ,
1352 decode( datatype, 'VARCHAR2', varchar2_value,
1353 'DATE' , fnd_date.date_to_canonical(date_value),
1354 'NUMBER' , number_value , '' ) value ,
1355 decode( datatype, 'VARCHAR2', original_varchar2_value,
1356 'DATE' , fnd_date.date_to_canonical(original_date_value),
1357 'NUMBER' , original_number_value , '' ) original_value ,
1358 created_by ,
1359 creation_date ,
1360 last_update_date ,
1361 last_updated_by ,
1362 last_update_login
1363 from hr_api_transaction_values
1364 where transaction_step_id = P_TRANSACTION_STEP_ID;
1365
1366 l_cnt integer;
1367 l_proc constant varchar2(100) := g_package || ' SaveTransValueHisroty';
1368 TxValueTbl TransValueTbl;
1369 BEGIN
1370 hr_utility.set_location('Entering: '|| l_proc,5);
1371
1372 OPEN cur_trans_value;
1373 FETCH cur_trans_value BULK COLLECT INTO
1374 TxValueTbl.transaction_value_id
1375 ,TxValueTbl.datatype
1376 ,TxValueTbl.name
1377 ,TxValueTbl.value
1378 ,TxValueTbl.original_value
1379 ,TxValueTbl.created_by
1380 ,TxValueTbl.creation_date
1381 ,TxValueTbl.last_update_date
1382 ,TxValueTbl.last_updated_by
1383 ,TxValueTbl.last_update_login;
1384 CLOSE cur_trans_value;
1385
1386 l_cnt := TxValueTbl.transaction_value_id.count;
1387
1388 For i in 1 .. l_cnt LOOP
1389
1390 INSERT into pqh_ss_value_history (
1391 transaction_value_id
1392 ,step_history_id
1393 ,approval_history_id
1394 ,datatype
1395 ,name
1396 ,value
1397 ,original_value
1398 ,created_by
1399 ,creation_date
1400 ,last_update_date
1401 ,last_updated_by
1402 ,last_update_login )
1403 values(
1404 TxValueTbl.transaction_value_id(i)
1405 ,P_TRANSACTION_STEP_ID
1406 ,P_APPROVAL_HISTORY_ID
1407 ,TxValueTbl.datatype(i)
1408 ,TxValueTbl.name(i)
1409 ,TxValueTbl.value(i)
1410 ,TxValueTbl.original_value(i)
1411 ,TxValueTbl.created_by(i)
1412 ,TxValueTbl.creation_date(i)
1413 ,TxValueTbl.last_update_date(i)
1414 ,TxValueTbl.last_updated_by(i)
1415 ,TxValueTbl.last_update_login(i));
1416
1417 END LOOP;
1418 hr_utility.set_location('Calling: savePerPayTransHistory '|| l_proc,10);
1419 --
1420 --SavePerPayTransHistory(P_APPROVAL_HISTORY_ID);
1421 --
1422 hr_utility.set_location('Leaving: '|| l_proc,20);
1423 EXCEPTION
1424 WHEN OTHERS THEN
1425 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1426 If cur_trans_value%Isopen Then
1427 CLOSE cur_trans_value;
1428 End If;
1429 RAISE;
1430 END SaveTransValueHistory;
1431
1432 Procedure SaveTransStepHistory
1433 (
1434 P_TRANSACTION_ID IN NUMBER
1435 ,P_APPROVAL_HISTORY_ID IN NUMBER
1436 )
1437 is
1438
1439 Cursor cur_trans_steps IS
1440 SELECT
1441 TRANSACTION_STEP_ID
1442 ,TRANSACTION_ID
1443 ,API_NAME
1444 ,API_DISPLAY_NAME
1445 ,PROCESSING_ORDER
1446 ,CREATED_BY
1447 ,CREATION_DATE
1448 ,LAST_UPDATE_DATE
1449 ,LAST_UPDATED_BY
1450 ,LAST_UPDATE_LOGIN
1451 ,ITEM_TYPE
1452 ,ITEM_KEY
1453 ,ACTIVITY_ID
1454 ,OBJECT_TYPE
1455 ,OBJECT_NAME
1456 ,OBJECT_IDENTIFIER
1457 ,OBJECT_STATE
1458 ,PK1
1459 ,PK2
1460 ,PK3
1461 ,PK4
1462 ,PK5
1463 ,INFORMATION_CATEGORY
1464 ,INFORMATION1
1465 ,INFORMATION2
1466 ,INFORMATION3
1467 ,INFORMATION4
1468 ,INFORMATION5
1469 ,INFORMATION6
1470 ,INFORMATION7
1471 ,INFORMATION8
1472 ,INFORMATION9
1473 ,INFORMATION10
1474 ,INFORMATION11
1475 ,INFORMATION12
1476 ,INFORMATION13
1477 ,INFORMATION14
1478 ,INFORMATION15
1479 ,INFORMATION16
1480 ,INFORMATION17
1481 ,INFORMATION18
1482 ,INFORMATION19
1483 ,INFORMATION20
1484 ,INFORMATION21
1485 ,INFORMATION22
1486 ,INFORMATION23
1487 ,INFORMATION24
1488 ,INFORMATION25
1489 ,INFORMATION26
1490 ,INFORMATION27
1491 ,INFORMATION28
1492 ,INFORMATION29
1493 ,INFORMATION30
1494 FROM hr_api_transaction_steps
1495 WHERE TRANSACTION_ID = P_TRANSACTION_ID;
1496
1497 l_cnt integer;
1498 l_proc constant varchar2(100) := g_package || 'SaveTransStepHisroty';
1499 TxStepTbl TransStepTbl;
1500 Begin
1501 hr_utility.set_location('Entering: '|| l_proc,5);
1502 OPEN cur_trans_steps;
1503 FETCH cur_trans_steps BULK COLLECT INTO
1504 TxStepTbl.STEP_HISTORY_ID
1505 ,TxStepTbl.TRANSACTION_HISTORY_ID
1506 ,TxStepTbl.API_NAME
1507 ,TxStepTbl.API_DISPLAY_NAME
1508 ,TxStepTbl.PROCESSING_ORDER
1509 ,TxStepTbl.CREATED_BY
1510 ,TxStepTbl.CREATION_DATE
1511 ,TxStepTbl.LAST_UPDATE_DATE
1512 ,TxStepTbl.LAST_UPDATED_BY
1513 ,TxStepTbl.LAST_UPDATE_LOGIN
1514 ,TxStepTbl.ITEM_TYPE
1515 ,TxStepTbl.ITEM_KEY
1516 ,TxStepTbl.ACTIVITY_ID
1517 ,TxStepTbl.OBJECT_TYPE
1518 ,TxStepTbl.OBJECT_NAME
1519 ,TxStepTbl.OBJECT_IDENTIFIER
1520 ,TxStepTbl.OBJECT_STATE
1521 ,TxStepTbl.PK1
1522 ,TxStepTbl.PK2
1523 ,TxStepTbl.PK3
1524 ,TxStepTbl.PK4
1525 ,TxStepTbl.PK5
1526 ,TxStepTbl.INFORMATION_CATEGORY
1527 ,TxStepTbl.INFORMATION1
1528 ,TxStepTbl.INFORMATION2
1529 ,TxStepTbl.INFORMATION3
1530 ,TxStepTbl.INFORMATION4
1531 ,TxStepTbl.INFORMATION5
1532 ,TxStepTbl.INFORMATION6
1533 ,TxStepTbl.INFORMATION7
1534 ,TxStepTbl.INFORMATION8
1535 ,TxStepTbl.INFORMATION9
1536 ,TxStepTbl.INFORMATION10
1537 ,TxStepTbl.INFORMATION11
1538 ,TxStepTbl.INFORMATION12
1539 ,TxStepTbl.INFORMATION13
1540 ,TxStepTbl.INFORMATION14
1541 ,TxStepTbl.INFORMATION15
1542 ,TxStepTbl.INFORMATION16
1543 ,TxStepTbl.INFORMATION17
1544 ,TxStepTbl.INFORMATION18
1545 ,TxStepTbl.INFORMATION19
1546 ,TxStepTbl.INFORMATION20
1547 ,TxStepTbl.INFORMATION21
1548 ,TxStepTbl.INFORMATION22
1549 ,TxStepTbl.INFORMATION23
1550 ,TxStepTbl.INFORMATION24
1551 ,TxStepTbl.INFORMATION25
1552 ,TxStepTbl.INFORMATION26
1553 ,TxStepTbl.INFORMATION27
1554 ,TxStepTbl.INFORMATION28
1555 ,TxStepTbl.INFORMATION29
1556 ,TxStepTbl.INFORMATION30;
1557 CLOSE cur_trans_steps;
1558 l_cnt := TxStepTbl.STEP_HISTORY_ID.count;
1559
1560 For i in 1 .. l_cnt Loop
1561 INSERT INTO pqh_ss_step_history
1562 (
1563 STEP_HISTORY_ID
1564 ,APPROVAL_HISTORY_ID
1565 ,TRANSACTION_HISTORY_ID
1566 ,API_NAME
1567 ,API_DISPLAY_NAME
1568 ,PROCESSING_ORDER
1569 ,CREATED_BY
1570 ,CREATION_DATE
1571 ,LAST_UPDATE_DATE
1572 ,LAST_UPDATED_BY
1573 ,LAST_UPDATE_LOGIN
1574 ,ITEM_TYPE
1575 ,ITEM_KEY
1576 ,ACTIVITY_ID
1577 ,OBJECT_TYPE
1578 ,OBJECT_NAME
1579 ,OBJECT_IDENTIFIER
1580 ,OBJECT_STATE
1581 ,PK1
1582 ,PK2
1583 ,PK3
1584 ,PK4
1585 ,PK5
1586 ,INFORMATION_CATEGORY
1587 ,INFORMATION1
1588 ,INFORMATION2
1589 ,INFORMATION3
1590 ,INFORMATION4
1591 ,INFORMATION5
1592 ,INFORMATION6
1593 ,INFORMATION7
1594 ,INFORMATION8
1595 ,INFORMATION9
1596 ,INFORMATION10
1597 ,INFORMATION11
1598 ,INFORMATION12
1599 ,INFORMATION13
1600 ,INFORMATION14
1601 ,INFORMATION15
1602 ,INFORMATION16
1603 ,INFORMATION17
1604 ,INFORMATION18
1605 ,INFORMATION19
1606 ,INFORMATION20
1607 ,INFORMATION21
1608 ,INFORMATION22
1609 ,INFORMATION23
1610 ,INFORMATION24
1611 ,INFORMATION25
1612 ,INFORMATION26
1613 ,INFORMATION27
1614 ,INFORMATION28
1615 ,INFORMATION29
1616 ,INFORMATION30
1617 )
1618 VALUES
1619 (
1620 TxStepTbl.STEP_HISTORY_ID(i)
1621 ,P_APPROVAL_HISTORY_ID
1622 ,TxStepTbl.TRANSACTION_HISTORY_ID(i)
1623 ,TxStepTbl.API_NAME(i)
1624 ,TxStepTbl.API_DISPLAY_NAME(i)
1625 ,TxStepTbl.PROCESSING_ORDER(i)
1626 ,TxStepTbl.CREATED_BY(i)
1627 ,TxStepTbl.CREATION_DATE(i)
1628 ,TxStepTbl.LAST_UPDATE_DATE(i)
1629 ,TxStepTbl.LAST_UPDATED_BY(i)
1630 ,TxStepTbl.LAST_UPDATE_LOGIN(i)
1631 ,TxStepTbl.ITEM_TYPE(i)
1632 ,TxStepTbl.ITEM_KEY(i)
1633 ,TxStepTbl.ACTIVITY_ID(i)
1634 ,TxStepTbl.OBJECT_TYPE(i)
1635 ,TxStepTbl.OBJECT_NAME(i)
1636 ,TxStepTbl.OBJECT_IDENTIFIER(i)
1637 ,TxStepTbl.OBJECT_STATE(i)
1638 ,TxStepTbl.PK1(i)
1639 ,TxStepTbl.PK2(i)
1640 ,TxStepTbl.PK3(i)
1641 ,TxStepTbl.PK4(i)
1642 ,TxStepTbl.PK5(i)
1643 ,TxStepTbl.INFORMATION_CATEGORY(i)
1644 ,TxStepTbl.INFORMATION1(i)
1645 ,TxStepTbl.INFORMATION2(i)
1646 ,TxStepTbl.INFORMATION3(i)
1647 ,TxStepTbl.INFORMATION4(i)
1648 ,TxStepTbl.INFORMATION5(i)
1649 ,TxStepTbl.INFORMATION6(i)
1650 ,TxStepTbl.INFORMATION7(i)
1651 ,TxStepTbl.INFORMATION8(i)
1652 ,TxStepTbl.INFORMATION9(i)
1653 ,TxStepTbl.INFORMATION10(i)
1654 ,TxStepTbl.INFORMATION11(i)
1655 ,TxStepTbl.INFORMATION12(i)
1656 ,TxStepTbl.INFORMATION13(i)
1657 ,TxStepTbl.INFORMATION14(i)
1658 ,TxStepTbl.INFORMATION15(i)
1659 ,TxStepTbl.INFORMATION16(i)
1660 ,TxStepTbl.INFORMATION17(i)
1661 ,TxStepTbl.INFORMATION18(i)
1662 ,TxStepTbl.INFORMATION19(i)
1663 ,TxStepTbl.INFORMATION20(i)
1664 ,TxStepTbl.INFORMATION21(i)
1665 ,TxStepTbl.INFORMATION22(i)
1666 ,TxStepTbl.INFORMATION23(i)
1667 ,TxStepTbl.INFORMATION24(i)
1668 ,TxStepTbl.INFORMATION25(i)
1669 ,TxStepTbl.INFORMATION26(i)
1670 ,TxStepTbl.INFORMATION27(i)
1671 ,TxStepTbl.INFORMATION28(i)
1672 ,TxStepTbl.INFORMATION29(i)
1673 ,TxStepTbl.INFORMATION30(i)
1674 );
1675
1676 SaveTransValueHistory
1677 (
1678 P_TRANSACTION_STEP_ID => TxStepTbl.STEP_HISTORY_ID(i)
1679 ,P_APPROVAL_HISTORY_ID => P_APPROVAL_HISTORY_ID
1680 );
1681
1682 SavePerPayTransHistory
1683 (
1684 TxStepTbl.STEP_HISTORY_ID(i),
1685 P_APPROVAL_HISTORY_ID
1686 );
1687 End Loop;
1688 hr_utility.set_location('Leaving: '|| l_proc,10);
1689 EXCEPTION
1690 WHEN OTHERS THEN
1691 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1692 If cur_trans_steps%Isopen Then
1693 CLOSE cur_trans_steps;
1694 End If;
1695 RAISE;
1696 End SaveTransStepHistory;
1697
1698 --
1699 -- ---------------------------------------------------------------------- --
1700 -- --------------------<SaveTransactionHistory>------------------------- --
1701 -- ---------------------------------------------------------------------- --
1702 --
1703
1704 Procedure SaveTransactionHistory
1705 (
1706 P_TRANSACTION_ID IN OUT NOCOPY NUMBER
1707 ,P_APPROVAL_HISTORY_ID OUT NOCOPY NUMBER
1708 )
1709 IS
1710 CURSOR cur_trans_details IS
1711 SELECT
1712 t.TRANSACTION_ID
1713 ,t.CREATOR_PERSON_ID
1714 ,t.ASSIGNMENT_ID
1715 ,t.SELECTED_PERSON_ID
1716 ,t.ITEM_TYPE
1717 ,t.ITEM_KEY
1718 ,t.PROCESS_NAME
1719 ,t.FUNCTION_ID
1720 ,t.RPTG_GRP_ID
1721 ,t.PLAN_ID
1722 ,t.TRANSACTION_GROUP
1723 ,t.TRANSACTION_IDENTIFIER
1724 ,t.STATUS
1725 ,t.TRANSACTION_STATE
1726 ,t.TRANSACTION_EFFECTIVE_DATE
1727 ,t.EFFECTIVE_DATE_OPTION
1728 ,t.CREATOR_ROLE
1729 ,t.LAST_UPDATE_ROLE
1730 ,t.PARENT_TRANSACTION_ID
1731 ,t.RELAUNCH_FUNCTION
1732 ,t.TRANSACTION_DOCUMENT
1733 ,pt.transaction_history_id
1734 FROM hr_api_transactions t, pqh_ss_transaction_history pt
1735 WHERE transaction_id = P_TRANSACTION_ID
1736 AND t.transaction_id = pt.transaction_history_ID (+);
1737
1738 l_trans_details_row cur_trans_details%ROWTYPE;
1739 l_seq_id PQH_SS_TRANS_STATE_HISTORY.approval_history_id%TYPE;
1740 l_proc constant varchar2(100) := g_package || ' SaveTransactionHistory';
1741
1742 Begin
1743 hr_utility.set_location('Entering: '|| l_proc,5);
1744 P_APPROVAL_HISTORY_ID := getTransStateSequence(P_TRANSACTION_ID);
1745 OPEN cur_trans_details;
1746 FETCH cur_trans_details INTO l_trans_details_row;
1747 If l_trans_details_row.transaction_history_id IS NULL then
1748 -- insert into transaction history
1749 pqh_txh_ins.set_base_key_value(p_transaction_history_id => P_TRANSACTION_ID);
1750
1751 pqh_txh_ins.ins(
1752 p_creator_person_id => l_trans_details_row.creator_person_id
1753 ,p_assignment_id => l_trans_details_row.assignment_id
1754 ,p_selected_person_id => l_trans_details_row.selected_person_id
1755 ,p_item_type => l_trans_details_row.item_type
1756 ,p_item_key => l_trans_details_row.item_key
1757 ,p_process_name => l_trans_details_row.process_name
1758 ,p_function_id => l_trans_details_row.function_id
1759 ,p_rptg_grp_id => l_trans_details_row.rptg_grp_id
1760 ,p_plan_id => l_trans_details_row.plan_id
1761 ,p_transaction_group => l_trans_details_row.transaction_group
1762 ,p_transaction_identifier => l_trans_details_row.transaction_identifier
1763 ,p_transaction_history_id => P_TRANSACTION_ID
1764 );
1765 End IF;
1766
1767 P_APPROVAL_HISTORY_ID := nvl(P_APPROVAL_HISTORY_ID,0) + 1;
1768 -- insert into transaction state history
1769 pqh_tsh_ins.set_base_key_value(
1770 p_transaction_history_id => P_TRANSACTION_ID
1771 ,P_APPROVAL_HISTORY_ID => P_APPROVAL_HISTORY_ID
1772 );
1773
1774 pqh_tsh_ins.ins(
1775 p_creator_person_id => l_trans_details_row.creator_person_id
1776 ,p_creator_role => l_trans_details_row.creator_role
1777 ,p_status => l_trans_details_row.status
1778 ,p_transaction_state => l_trans_details_row.transaction_state
1779 ,p_effective_date => l_trans_details_row.transaction_effective_date
1780 ,p_effective_date_option => l_trans_details_row.effective_date_option
1781 ,p_last_update_role => l_trans_details_row.last_update_role
1782 ,p_parent_transaction_id => l_trans_details_row.parent_transaction_id
1783 ,p_relaunch_function => l_trans_details_row.relaunch_function
1784 ,p_transaction_document => l_trans_details_row.transaction_document
1785 ,p_transaction_history_id => P_TRANSACTION_ID
1786 ,P_APPROVAL_HISTORY_ID => P_APPROVAL_HISTORY_ID);
1787
1788 -- insert into transaction steps history
1789 SaveTransStepHistory (
1790 P_TRANSACTION_ID => P_TRANSACTION_ID
1791 ,P_APPROVAL_HISTORY_ID => P_APPROVAL_HISTORY_ID
1792 );
1793 CLOSE cur_trans_details;
1794 hr_utility.set_location('Leaving: '|| l_proc,10);
1795 Exception
1796 when OTHERS then
1797 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1798 If cur_trans_details%IsOpen Then
1799 CLOSE cur_trans_details;
1800 End If;
1801 raise;
1802 End SaveTransactionHistory;
1803
1804 --
1805 --
1806 Procedure ARCHIVE_ACTION
1807 (
1808 P_TRANSACTION_ID IN NUMBER
1809 ,P_NOTIFICATION_ID IN NUMBER
1810 ,P_USER_NAME IN VARCHAR2
1811 ,P_USER_COMMENT IN VARCHAR2 DEFAULT NULL
1812 ,P_ACTION IN VARCHAR2
1813 )
1814 IS
1815 l_action VARCHAR2(15) := 'TRANSFER';
1816 l_seq_id NUMBER(5);
1817 l_trans_id NUMBER(15);
1818 l_proc constant varchar2(100) := g_package || ' ARCHIVE_ACTION';
1819
1820 Begin
1821 --
1822 --hr_utility.trace_on(null, 'TIGER');
1823 --g_debug := TRUE;
1824 --
1825 hr_utility.set_location('Entering: '|| l_proc,5);
1826 deleteStaleData(P_TRANSACTION_ID, P_ACTION);
1827 l_trans_id := P_TRANSACTION_ID;
1828 If (P_ACTION = 'RESUBMIT' AND P_NOTIFICATION_ID IS NULL)
1829 OR (P_ACTION IN ('SFL','SUBMIT')) Then
1830
1831 SaveTransactionHistory
1832 (
1833 P_TRANSACTION_ID => l_trans_id
1834 ,P_APPROVAL_HISTORY_ID => l_seq_id
1835 );
1836
1837 Else
1838 l_seq_id := getTransStateSequence(P_TRANSACTION_ID => P_TRANSACTION_ID);
1839 End If;
1840
1841 -- For resumit case the notification call back will handle th routing history
1842 IF (P_NOTIFICATION_ID IS NOT NULL OR P_ACTION in ('SFL','SUBMIT')) THEN
1843 l_seq_id := nvl(l_seq_id, 1);
1844 SaveRoutingHistory
1845 (
1846 P_TRANSACTION_ID => l_trans_id
1847 ,P_APPROVAL_HISTORY_ID => l_seq_id
1848 ,P_NOTIFICATION_ID => P_NOTIFICATION_ID
1849 ,P_ACTION => P_ACTION
1850 ,P_USER_NAME => P_USER_NAME
1851 ,P_USER_COMMENT => P_USER_COMMENT
1852 );
1853 END IF;
1854
1855 hr_utility.set_location('Leaving: '|| l_proc,10);
1856 Exception
1857 when OTHERS then
1858 --ROLLBACK;
1859 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1860 raise;
1861 End ARCHIVE_ACTION;
1862
1863 --
1864 --
1865 Procedure ARCHIVE_SUBMIT
1866 (
1867 P_TRANSACTION_ID IN NUMBER
1868 ,P_NOTIFICATION_ID IN NUMBER
1869 ,P_USER_NAME IN VARCHAR2
1870 ,P_USER_COMMENT IN VARCHAR2
1871 )
1872 IS
1873 l_proc constant varchar2(100) := g_package || ' ARCHIVE_SUBMIT';
1874 Begin
1875 --
1876 -- hr_utility.trace_on(null, 'TIGER');
1877 --g_debug := TRUE;
1878 --
1879 hr_utility.set_location('Entering: '|| l_proc,5);
1880 ARCHIVE_ACTION
1881 (
1882 P_TRANSACTION_ID => P_TRANSACTION_ID
1883 ,P_NOTIFICATION_ID => P_NOTIFICATION_ID
1884 ,P_USER_NAME => P_USER_NAME
1885 ,P_USER_COMMENT => P_USER_COMMENT
1886 ,P_ACTION => 'SUBMIT'
1887 );
1888 hr_utility.set_location('Leaving: '|| l_proc,10);
1889 Exception
1890 when OTHERS then
1891 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1892 raise;
1893 End ARCHIVE_SUBMIT;
1894
1895 --
1896 --
1897 Procedure ARCHIVE_RESUBMIT
1898 (
1899 P_TRANSACTION_ID IN NUMBER
1900 ,P_NOTIFICATION_ID IN NUMBER
1901 ,P_USER_NAME IN VARCHAR2
1902 ,P_USER_COMMENT IN VARCHAR2
1903 )
1904 IS
1905 l_proc constant varchar2(100) := g_package || ' ARCHIVE_RESUBMIT';
1906 Begin
1907 hr_utility.set_location('Entering: '|| l_proc,5);
1908 ARCHIVE_ACTION
1909 (
1910 P_TRANSACTION_ID => P_TRANSACTION_ID
1911 ,P_NOTIFICATION_ID => P_NOTIFICATION_ID
1912 ,P_USER_NAME => P_USER_NAME
1913 ,P_USER_COMMENT => P_USER_COMMENT
1914 ,P_ACTION => 'RESUBMIT'
1915 );
1916 hr_utility.set_location('Leaving: '|| l_proc,10);
1917 Exception
1918 when OTHERS then
1919 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1920 raise;
1921 End ARCHIVE_RESUBMIT;
1922
1923 Procedure ARCHIVE_SFL
1924 (
1925 P_TRANSACTION_ID IN NUMBER
1926 ,P_NOTIFICATION_ID IN NUMBER
1927 ,P_USER_NAME IN VARCHAR2
1928 )
1929 IS
1930 l_proc constant varchar2(100) := g_package || ' ARCHIVE_SFL';
1931
1932 Begin
1933 --
1934 -- hr_utility.trace_on(null, 'TIGER');
1935 --g_debug := TRUE;
1936 --
1937 hr_utility.set_location('Entering: '|| l_proc,5);
1938 ARCHIVE_ACTION
1939 (
1940 P_TRANSACTION_ID => P_TRANSACTION_ID
1941 ,P_NOTIFICATION_ID => P_NOTIFICATION_ID
1942 ,P_USER_NAME => P_USER_NAME
1943 ,P_ACTION => 'SFL'
1944 );
1945 hr_utility.set_location('Leaving: '|| l_proc,10);
1946 Exception
1947 when OTHERS then
1948 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1949 raise;
1950 End ARCHIVE_SFL;
1951
1952 Procedure ARCHIVE_APPROVE
1953 (
1954 P_TRANSACTION_ID IN NUMBER
1955 ,P_NOTIFICATION_ID IN NUMBER
1956 ,P_USER_NAME IN VARCHAR2
1957 ,P_USER_COMMENT IN VARCHAR2
1958 )
1959 IS
1960 l_proc constant varchar2(100) := g_package || ' ARCHIVE_APPROVE';
1961 Begin
1962 hr_utility.set_location('Entering: '|| l_proc,5);
1963 ARCHIVE_ACTION
1964 (
1965 P_TRANSACTION_ID => P_TRANSACTION_ID
1966 ,P_NOTIFICATION_ID => P_NOTIFICATION_ID
1967 ,P_USER_NAME => P_USER_NAME
1968 ,P_USER_COMMENT => P_USER_COMMENT
1969 ,P_ACTION => 'APPROVED'
1970 );
1971 hr_utility.set_location('Leaving: '|| l_proc,10);
1972 Exception
1973 when OTHERS then
1974 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1975 raise;
1976 End ARCHIVE_APPROVE;
1977
1978 Procedure ARCHIVE_DELETE
1979 (
1980 P_TRANSACTION_ID IN NUMBER
1981 ,P_NOTIFICATION_ID IN NUMBER
1982 ,P_USER_NAME IN VARCHAR2
1983 ,P_USER_COMMENT IN VARCHAR2
1984 )
1985 IS
1986 l_proc constant varchar2(100) := g_package || ' ARCHIVE_DELETE';
1987 Begin
1988 hr_utility.set_location('Entering: '|| l_proc,5);
1989 ARCHIVE_ACTION
1990 (
1991 P_TRANSACTION_ID => P_TRANSACTION_ID
1992 ,P_NOTIFICATION_ID => P_NOTIFICATION_ID
1993 ,P_USER_NAME => P_USER_NAME
1994 ,P_USER_COMMENT => P_USER_COMMENT
1995 ,P_ACTION => 'DELETED'
1996 );
1997 hr_utility.set_location('Leaving: '|| l_proc,10);
1998 Exception
1999 when OTHERS then
2000 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
2001 raise;
2002 End ARCHIVE_DELETE;
2003
2004 Procedure ARCHIVE_REJECT
2005 (
2006 P_TRANSACTION_ID IN NUMBER
2007 ,P_NOTIFICATION_ID IN NUMBER
2008 ,P_USER_NAME IN VARCHAR2
2009 ,P_USER_COMMENT IN VARCHAR2
2010 )
2011 IS
2012 l_proc constant varchar2(100) := g_package || ' ARCHIVE_REJECT';
2013 Begin
2014 hr_utility.set_location('Entering: '|| l_proc,5);
2015 ARCHIVE_ACTION
2016 (
2017 P_TRANSACTION_ID => P_TRANSACTION_ID
2018 ,P_NOTIFICATION_ID => P_NOTIFICATION_ID
2019 ,P_USER_NAME => P_USER_NAME
2020 ,P_USER_COMMENT => P_USER_COMMENT
2021 ,P_ACTION => 'REJECTED'
2022 );
2023 hr_utility.set_location('Leaving: '|| l_proc,10);
2024 Exception
2025 when OTHERS then
2026 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
2027 raise;
2028 End ARCHIVE_REJECT;
2029
2030 Procedure ARCHIVE_RFC
2031 (
2032 P_TRANSACTION_ID IN NUMBER
2033 ,P_NOTIFICATION_ID IN NUMBER
2034 ,P_USER_NAME IN VARCHAR2
2035 ,P_USER_COMMENT IN VARCHAR2
2036 )
2037 IS
2038 l_proc constant varchar2(100) := g_package || ' ARCHIVE_RFC';
2039 Begin
2040 hr_utility.set_location('Entering: '|| l_proc,5);
2041 ARCHIVE_ACTION
2042 (
2043 P_TRANSACTION_ID => P_TRANSACTION_ID
2044 ,P_NOTIFICATION_ID => P_NOTIFICATION_ID
2045 ,P_USER_NAME => P_USER_NAME
2046 ,P_USER_COMMENT => P_USER_COMMENT
2047 ,P_ACTION => 'RFC'
2048 );
2049 hr_utility.set_location('Leaving: '|| l_proc,10);
2050 Exception
2051 when OTHERS then
2052 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
2053 raise;
2054 End ARCHIVE_RFC;
2055
2056 Procedure ARCHIVE_TRANSFER
2057 (
2058 P_TRANSACTION_ID IN NUMBER
2059 ,P_NOTIFICATION_ID IN NUMBER
2060 ,P_USER_NAME IN VARCHAR2
2061 ,P_USER_COMMENT IN VARCHAR2
2062 )
2063 IS
2064 l_proc constant varchar2(100) := g_package || ' ARCHIVE_TRANSFER';
2065 Begin
2066 --
2067 -- hr_utility.trace_on(null, 'TIGER');
2068 --g_debug := TRUE;
2069 --
2070 hr_utility.set_location('Entering: '|| l_proc,5);
2071 ARCHIVE_ACTION
2072 (
2073 P_TRANSACTION_ID => P_TRANSACTION_ID
2074 ,P_NOTIFICATION_ID => P_NOTIFICATION_ID
2075 ,P_USER_NAME => P_USER_NAME
2076 ,P_USER_COMMENT => P_USER_COMMENT
2077 ,P_ACTION => 'TRANSFER'
2078 );
2079 hr_utility.set_location('Leaving: '|| l_proc,10);
2080 Exception
2081 when OTHERS then
2082 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
2083 raise;
2084 End ARCHIVE_TRANSFER;
2085
2086 Procedure ARCHIVE_FORWARD
2087 (
2088 P_TRANSACTION_ID IN NUMBER
2089 ,P_NOTIFICATION_ID IN NUMBER
2090 ,P_USER_NAME IN VARCHAR2
2091 ,P_USER_COMMENT IN VARCHAR2
2092 )
2093 IS
2094 l_proc constant varchar2(100) := g_package || ' ARCHIVE_FORWARD';
2095 Begin
2096 hr_utility.set_location('Entering: '|| l_proc,5);
2097 ARCHIVE_ACTION
2098 (
2099 P_TRANSACTION_ID => P_TRANSACTION_ID
2100 ,P_NOTIFICATION_ID => P_NOTIFICATION_ID
2101 ,P_USER_NAME => P_USER_NAME
2102 ,P_USER_COMMENT => P_USER_COMMENT
2103 ,P_ACTION => 'FORWARD'
2104 );
2105 hr_utility.set_location('Leaving: '|| l_proc,10);
2106 Exception
2107 when OTHERS then
2108 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
2109 raise;
2110 End ARCHIVE_FORWARD;
2111
2112 Procedure ARCHIVE_REQ_MOREINFO
2113 (
2114 P_TRANSACTION_ID IN NUMBER
2115 ,P_NOTIFICATION_ID IN NUMBER
2116 ,P_USER_NAME IN VARCHAR2
2117 ,P_USER_COMMENT IN VARCHAR2
2118 )
2119 IS
2120 l_proc constant varchar2(100) := g_package || ' ARCHIVE_REQ_MOREINFO';
2121 Begin
2122 hr_utility.set_location('Entering: '|| l_proc,5);
2123 ARCHIVE_ACTION
2124 (
2125 P_TRANSACTION_ID => P_TRANSACTION_ID
2126 ,P_NOTIFICATION_ID => P_NOTIFICATION_ID
2127 ,P_USER_NAME => P_USER_NAME
2128 ,P_USER_COMMENT => P_USER_COMMENT
2129 ,P_ACTION => 'QUESTION'
2130 );
2131 hr_utility.set_location('Leaving: '|| l_proc,10);
2132 Exception
2133 when OTHERS then
2134 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
2135 raise;
2136 End ARCHIVE_REQ_MOREINFO;
2137
2138 Procedure ARCHIVE_ANSWER_MOREINFO
2139 (
2140 P_TRANSACTION_ID IN NUMBER
2141 ,P_NOTIFICATION_ID IN NUMBER
2142 ,P_USER_NAME IN VARCHAR2
2143 ,P_USER_COMMENT IN VARCHAR2
2144 )
2145 IS
2146 l_proc constant varchar2(100) := g_package || ' ARCHIVE_ANSWER_MOREINFO';
2147 Begin
2148 hr_utility.set_location('Entering: '|| l_proc,5);
2149 ARCHIVE_ACTION
2150 (
2151 P_TRANSACTION_ID => P_TRANSACTION_ID
2152 ,P_NOTIFICATION_ID => P_NOTIFICATION_ID
2153 ,P_USER_NAME => P_USER_NAME
2154 ,P_USER_COMMENT => P_USER_COMMENT
2155 ,P_ACTION => 'ANSWER'
2156 );
2157 hr_utility.set_location('Leaving: '|| l_proc,10);
2158 Exception
2159 when OTHERS then
2160 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
2161 raise;
2162 End ARCHIVE_ANSWER_MOREINFO;
2163
2164 Procedure CANCEL_ACTION
2165 (
2166 P_TRANSACTION_ID IN NUMBER
2167 )
2168 IS
2169 l_proc constant varchar2(100) := g_package || ' CANCEL_ACTION';
2170 PRAGMA AUTONOMOUS_TRANSACTION;
2171 BEGIN
2172 --
2173 -- hr_utility.trace_on(null, 'TIGER');
2174 --g_debug := TRUE;
2175 --
2176 hr_utility.set_location('Entering: '|| l_proc,5);
2177
2178 RevertToLastSave
2179 (
2180 P_TRANSACTION_ID => P_TRANSACTION_ID
2181 );
2182 hr_utility.set_location('Leaving: '|| l_proc,10);
2183 commit;
2184 Exception
2185 when OTHERS then
2186 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
2187 ROLLBACK;
2188 RAISE;
2189 END CANCEL_ACTION;
2190
2191 Procedure ARCHIVE_TIMEOUT
2192 (
2193 P_TRANSACTION_ID IN NUMBER
2194 ,P_NOTIFICATION_ID IN NUMBER
2195 ,P_USER_NAME IN VARCHAR2
2196 ,P_USER_COMMENT IN VARCHAR2
2197 )
2198 IS
2199 l_proc constant varchar2(100) := g_package || ' ARCHIVE_TIMEOUT';
2200 Begin
2201 hr_utility.set_location('Entering: '|| l_proc,5);
2202 ARCHIVE_ACTION
2203 (
2204 P_TRANSACTION_ID => P_TRANSACTION_ID
2205 ,P_NOTIFICATION_ID => P_NOTIFICATION_ID
2206 ,P_USER_NAME => P_USER_NAME
2207 ,P_USER_COMMENT => P_USER_COMMENT
2208 ,P_ACTION => 'TIMEOUT'
2209 );
2210 hr_utility.set_location('Leaving: '|| l_proc,10);
2211 Exception
2212 when OTHERS then
2213 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
2214 raise;
2215 End ARCHIVE_TIMEOUT;
2216
2217 END HR_TRANS_HISTORY_API;