[Home] [Help]
PACKAGE BODY: APPS.HR_TRANS_HISTORY_API
Source
1 PACKAGE BODY HR_TRANS_HISTORY_API as
2 /* $Header: hrtrhapi.pkb 120.12 2011/05/17 14:33:34 nchinnam 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), 'BOOLEAN', 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 'BOOLEAN', varchar2_value,
1354 'DATE' , fnd_date.date_to_canonical(date_value),
1355 'NUMBER' , number_value , '' ) value ,
1356 decode( datatype, 'VARCHAR2', original_varchar2_value,
1357 'DATE' , fnd_date.date_to_canonical(original_date_value),
1358 'NUMBER' , original_number_value , '' ) original_value ,
1359 created_by ,
1360 creation_date ,
1361 last_update_date ,
1362 last_updated_by ,
1363 last_update_login
1364 from hr_api_transaction_values
1365 where transaction_step_id = P_TRANSACTION_STEP_ID;
1366
1367 l_cnt integer;
1368 l_proc constant varchar2(100) := g_package || ' SaveTransValueHisroty';
1369 TxValueTbl TransValueTbl;
1370 BEGIN
1371 hr_utility.set_location('Entering: '|| l_proc,5);
1372
1373 OPEN cur_trans_value;
1374 FETCH cur_trans_value BULK COLLECT INTO
1375 TxValueTbl.transaction_value_id
1376 ,TxValueTbl.datatype
1377 ,TxValueTbl.name
1378 ,TxValueTbl.value
1379 ,TxValueTbl.original_value
1380 ,TxValueTbl.created_by
1381 ,TxValueTbl.creation_date
1382 ,TxValueTbl.last_update_date
1383 ,TxValueTbl.last_updated_by
1384 ,TxValueTbl.last_update_login;
1385 CLOSE cur_trans_value;
1386
1387 l_cnt := TxValueTbl.transaction_value_id.count;
1388
1389 For i in 1 .. l_cnt LOOP
1390
1391 INSERT into pqh_ss_value_history (
1392 transaction_value_id
1393 ,step_history_id
1394 ,approval_history_id
1395 ,datatype
1396 ,name
1397 ,value
1398 ,original_value
1399 ,created_by
1400 ,creation_date
1401 ,last_update_date
1402 ,last_updated_by
1403 ,last_update_login )
1404 values(
1405 TxValueTbl.transaction_value_id(i)
1406 ,P_TRANSACTION_STEP_ID
1407 ,P_APPROVAL_HISTORY_ID
1408 ,TxValueTbl.datatype(i)
1409 ,TxValueTbl.name(i)
1410 ,TxValueTbl.value(i)
1411 ,TxValueTbl.original_value(i)
1412 ,TxValueTbl.created_by(i)
1413 ,TxValueTbl.creation_date(i)
1414 ,TxValueTbl.last_update_date(i)
1415 ,TxValueTbl.last_updated_by(i)
1416 ,TxValueTbl.last_update_login(i));
1417
1418 END LOOP;
1419 hr_utility.set_location('Calling: savePerPayTransHistory '|| l_proc,10);
1420 --
1421 --SavePerPayTransHistory(P_APPROVAL_HISTORY_ID);
1422 --
1423 hr_utility.set_location('Leaving: '|| l_proc,20);
1424 EXCEPTION
1425 WHEN OTHERS THEN
1426 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1427 If cur_trans_value%Isopen Then
1428 CLOSE cur_trans_value;
1429 End If;
1430 RAISE;
1431 END SaveTransValueHistory;
1432
1433 Procedure SaveTransStepHistory
1434 (
1435 P_TRANSACTION_ID IN NUMBER
1436 ,P_APPROVAL_HISTORY_ID IN NUMBER
1437 )
1438 is
1439
1440 Cursor cur_trans_steps IS
1441 SELECT
1442 TRANSACTION_STEP_ID
1443 ,TRANSACTION_ID
1444 ,API_NAME
1445 ,API_DISPLAY_NAME
1446 ,PROCESSING_ORDER
1447 ,CREATED_BY
1448 ,CREATION_DATE
1449 ,LAST_UPDATE_DATE
1450 ,LAST_UPDATED_BY
1451 ,LAST_UPDATE_LOGIN
1452 ,ITEM_TYPE
1453 ,ITEM_KEY
1454 ,ACTIVITY_ID
1455 ,OBJECT_TYPE
1456 ,OBJECT_NAME
1457 ,OBJECT_IDENTIFIER
1458 ,OBJECT_STATE
1459 ,PK1
1460 ,PK2
1461 ,PK3
1462 ,PK4
1463 ,PK5
1464 ,INFORMATION_CATEGORY
1465 ,INFORMATION1
1466 ,INFORMATION2
1467 ,INFORMATION3
1468 ,INFORMATION4
1469 ,INFORMATION5
1470 ,INFORMATION6
1471 ,INFORMATION7
1472 ,INFORMATION8
1473 ,INFORMATION9
1474 ,INFORMATION10
1475 ,INFORMATION11
1476 ,INFORMATION12
1477 ,INFORMATION13
1478 ,INFORMATION14
1479 ,INFORMATION15
1480 ,INFORMATION16
1481 ,INFORMATION17
1482 ,INFORMATION18
1483 ,INFORMATION19
1484 ,INFORMATION20
1485 ,INFORMATION21
1486 ,INFORMATION22
1487 ,INFORMATION23
1488 ,INFORMATION24
1489 ,INFORMATION25
1490 ,INFORMATION26
1491 ,INFORMATION27
1492 ,INFORMATION28
1493 ,INFORMATION29
1494 ,INFORMATION30
1495 FROM hr_api_transaction_steps
1496 WHERE TRANSACTION_ID = P_TRANSACTION_ID;
1497
1498 l_cnt integer;
1499 l_proc constant varchar2(100) := g_package || 'SaveTransStepHisroty';
1500 TxStepTbl TransStepTbl;
1501 Begin
1502 hr_utility.set_location('Entering: '|| l_proc,5);
1503 OPEN cur_trans_steps;
1504 FETCH cur_trans_steps BULK COLLECT INTO
1505 TxStepTbl.STEP_HISTORY_ID
1506 ,TxStepTbl.TRANSACTION_HISTORY_ID
1507 ,TxStepTbl.API_NAME
1508 ,TxStepTbl.API_DISPLAY_NAME
1509 ,TxStepTbl.PROCESSING_ORDER
1510 ,TxStepTbl.CREATED_BY
1511 ,TxStepTbl.CREATION_DATE
1512 ,TxStepTbl.LAST_UPDATE_DATE
1513 ,TxStepTbl.LAST_UPDATED_BY
1514 ,TxStepTbl.LAST_UPDATE_LOGIN
1515 ,TxStepTbl.ITEM_TYPE
1516 ,TxStepTbl.ITEM_KEY
1517 ,TxStepTbl.ACTIVITY_ID
1518 ,TxStepTbl.OBJECT_TYPE
1519 ,TxStepTbl.OBJECT_NAME
1520 ,TxStepTbl.OBJECT_IDENTIFIER
1521 ,TxStepTbl.OBJECT_STATE
1522 ,TxStepTbl.PK1
1523 ,TxStepTbl.PK2
1524 ,TxStepTbl.PK3
1525 ,TxStepTbl.PK4
1526 ,TxStepTbl.PK5
1527 ,TxStepTbl.INFORMATION_CATEGORY
1528 ,TxStepTbl.INFORMATION1
1529 ,TxStepTbl.INFORMATION2
1530 ,TxStepTbl.INFORMATION3
1531 ,TxStepTbl.INFORMATION4
1532 ,TxStepTbl.INFORMATION5
1533 ,TxStepTbl.INFORMATION6
1534 ,TxStepTbl.INFORMATION7
1535 ,TxStepTbl.INFORMATION8
1536 ,TxStepTbl.INFORMATION9
1537 ,TxStepTbl.INFORMATION10
1538 ,TxStepTbl.INFORMATION11
1539 ,TxStepTbl.INFORMATION12
1540 ,TxStepTbl.INFORMATION13
1541 ,TxStepTbl.INFORMATION14
1542 ,TxStepTbl.INFORMATION15
1543 ,TxStepTbl.INFORMATION16
1544 ,TxStepTbl.INFORMATION17
1545 ,TxStepTbl.INFORMATION18
1546 ,TxStepTbl.INFORMATION19
1547 ,TxStepTbl.INFORMATION20
1548 ,TxStepTbl.INFORMATION21
1549 ,TxStepTbl.INFORMATION22
1550 ,TxStepTbl.INFORMATION23
1551 ,TxStepTbl.INFORMATION24
1552 ,TxStepTbl.INFORMATION25
1553 ,TxStepTbl.INFORMATION26
1554 ,TxStepTbl.INFORMATION27
1555 ,TxStepTbl.INFORMATION28
1556 ,TxStepTbl.INFORMATION29
1557 ,TxStepTbl.INFORMATION30;
1558 CLOSE cur_trans_steps;
1559 l_cnt := TxStepTbl.STEP_HISTORY_ID.count;
1560
1561 For i in 1 .. l_cnt Loop
1562 INSERT INTO pqh_ss_step_history
1563 (
1564 STEP_HISTORY_ID
1565 ,APPROVAL_HISTORY_ID
1566 ,TRANSACTION_HISTORY_ID
1567 ,API_NAME
1568 ,API_DISPLAY_NAME
1569 ,PROCESSING_ORDER
1570 ,CREATED_BY
1571 ,CREATION_DATE
1572 ,LAST_UPDATE_DATE
1573 ,LAST_UPDATED_BY
1574 ,LAST_UPDATE_LOGIN
1575 ,ITEM_TYPE
1576 ,ITEM_KEY
1577 ,ACTIVITY_ID
1578 ,OBJECT_TYPE
1579 ,OBJECT_NAME
1580 ,OBJECT_IDENTIFIER
1581 ,OBJECT_STATE
1582 ,PK1
1583 ,PK2
1584 ,PK3
1585 ,PK4
1586 ,PK5
1587 ,INFORMATION_CATEGORY
1588 ,INFORMATION1
1589 ,INFORMATION2
1590 ,INFORMATION3
1591 ,INFORMATION4
1592 ,INFORMATION5
1593 ,INFORMATION6
1594 ,INFORMATION7
1595 ,INFORMATION8
1596 ,INFORMATION9
1597 ,INFORMATION10
1598 ,INFORMATION11
1599 ,INFORMATION12
1600 ,INFORMATION13
1601 ,INFORMATION14
1602 ,INFORMATION15
1603 ,INFORMATION16
1604 ,INFORMATION17
1605 ,INFORMATION18
1606 ,INFORMATION19
1607 ,INFORMATION20
1608 ,INFORMATION21
1609 ,INFORMATION22
1610 ,INFORMATION23
1611 ,INFORMATION24
1612 ,INFORMATION25
1613 ,INFORMATION26
1614 ,INFORMATION27
1615 ,INFORMATION28
1616 ,INFORMATION29
1617 ,INFORMATION30
1618 )
1619 VALUES
1620 (
1621 TxStepTbl.STEP_HISTORY_ID(i)
1622 ,P_APPROVAL_HISTORY_ID
1623 ,TxStepTbl.TRANSACTION_HISTORY_ID(i)
1624 ,TxStepTbl.API_NAME(i)
1625 ,TxStepTbl.API_DISPLAY_NAME(i)
1626 ,TxStepTbl.PROCESSING_ORDER(i)
1627 ,TxStepTbl.CREATED_BY(i)
1628 ,TxStepTbl.CREATION_DATE(i)
1629 ,TxStepTbl.LAST_UPDATE_DATE(i)
1630 ,TxStepTbl.LAST_UPDATED_BY(i)
1631 ,TxStepTbl.LAST_UPDATE_LOGIN(i)
1632 ,TxStepTbl.ITEM_TYPE(i)
1633 ,TxStepTbl.ITEM_KEY(i)
1634 ,TxStepTbl.ACTIVITY_ID(i)
1635 ,TxStepTbl.OBJECT_TYPE(i)
1636 ,TxStepTbl.OBJECT_NAME(i)
1637 ,TxStepTbl.OBJECT_IDENTIFIER(i)
1638 ,TxStepTbl.OBJECT_STATE(i)
1639 ,TxStepTbl.PK1(i)
1640 ,TxStepTbl.PK2(i)
1641 ,TxStepTbl.PK3(i)
1642 ,TxStepTbl.PK4(i)
1643 ,TxStepTbl.PK5(i)
1644 ,TxStepTbl.INFORMATION_CATEGORY(i)
1645 ,TxStepTbl.INFORMATION1(i)
1646 ,TxStepTbl.INFORMATION2(i)
1647 ,TxStepTbl.INFORMATION3(i)
1648 ,TxStepTbl.INFORMATION4(i)
1649 ,TxStepTbl.INFORMATION5(i)
1650 ,TxStepTbl.INFORMATION6(i)
1651 ,TxStepTbl.INFORMATION7(i)
1652 ,TxStepTbl.INFORMATION8(i)
1653 ,TxStepTbl.INFORMATION9(i)
1654 ,TxStepTbl.INFORMATION10(i)
1655 ,TxStepTbl.INFORMATION11(i)
1656 ,TxStepTbl.INFORMATION12(i)
1657 ,TxStepTbl.INFORMATION13(i)
1658 ,TxStepTbl.INFORMATION14(i)
1659 ,TxStepTbl.INFORMATION15(i)
1660 ,TxStepTbl.INFORMATION16(i)
1661 ,TxStepTbl.INFORMATION17(i)
1662 ,TxStepTbl.INFORMATION18(i)
1663 ,TxStepTbl.INFORMATION19(i)
1664 ,TxStepTbl.INFORMATION20(i)
1665 ,TxStepTbl.INFORMATION21(i)
1666 ,TxStepTbl.INFORMATION22(i)
1667 ,TxStepTbl.INFORMATION23(i)
1668 ,TxStepTbl.INFORMATION24(i)
1669 ,TxStepTbl.INFORMATION25(i)
1670 ,TxStepTbl.INFORMATION26(i)
1671 ,TxStepTbl.INFORMATION27(i)
1672 ,TxStepTbl.INFORMATION28(i)
1673 ,TxStepTbl.INFORMATION29(i)
1674 ,TxStepTbl.INFORMATION30(i)
1675 );
1676
1677 SaveTransValueHistory
1678 (
1679 P_TRANSACTION_STEP_ID => TxStepTbl.STEP_HISTORY_ID(i)
1680 ,P_APPROVAL_HISTORY_ID => P_APPROVAL_HISTORY_ID
1681 );
1682
1683 SavePerPayTransHistory
1684 (
1685 TxStepTbl.STEP_HISTORY_ID(i),
1686 P_APPROVAL_HISTORY_ID
1687 );
1688 End Loop;
1689 hr_utility.set_location('Leaving: '|| l_proc,10);
1690 EXCEPTION
1691 WHEN OTHERS THEN
1692 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1693 If cur_trans_steps%Isopen Then
1694 CLOSE cur_trans_steps;
1695 End If;
1696 RAISE;
1697 End SaveTransStepHistory;
1698
1699 --
1700 -- ---------------------------------------------------------------------- --
1701 -- --------------------<SaveTransactionHistory>------------------------- --
1702 -- ---------------------------------------------------------------------- --
1703 --
1704
1705 Procedure SaveTransactionHistory
1706 (
1707 P_TRANSACTION_ID IN OUT NOCOPY NUMBER
1708 ,P_APPROVAL_HISTORY_ID OUT NOCOPY NUMBER
1709 )
1710 IS
1711 CURSOR cur_trans_details IS
1712 SELECT
1713 t.TRANSACTION_ID
1714 ,t.CREATOR_PERSON_ID
1715 ,t.ASSIGNMENT_ID
1716 ,t.SELECTED_PERSON_ID
1717 ,t.ITEM_TYPE
1718 ,t.ITEM_KEY
1719 ,t.PROCESS_NAME
1720 ,t.FUNCTION_ID
1721 ,t.RPTG_GRP_ID
1722 ,t.PLAN_ID
1723 ,t.TRANSACTION_GROUP
1724 ,t.TRANSACTION_IDENTIFIER
1725 ,t.STATUS
1726 ,t.TRANSACTION_STATE
1727 ,t.TRANSACTION_EFFECTIVE_DATE
1728 ,t.EFFECTIVE_DATE_OPTION
1729 ,t.CREATOR_ROLE
1730 ,t.LAST_UPDATE_ROLE
1731 ,t.PARENT_TRANSACTION_ID
1732 ,t.RELAUNCH_FUNCTION
1733 ,t.TRANSACTION_DOCUMENT
1734 ,pt.transaction_history_id
1735 FROM hr_api_transactions t, pqh_ss_transaction_history pt
1736 WHERE transaction_id = P_TRANSACTION_ID
1737 AND t.transaction_id = pt.transaction_history_ID (+);
1738
1739 l_trans_details_row cur_trans_details%ROWTYPE;
1740 l_seq_id PQH_SS_TRANS_STATE_HISTORY.approval_history_id%TYPE;
1741 l_proc constant varchar2(100) := g_package || ' SaveTransactionHistory';
1742
1743 Begin
1744 hr_utility.set_location('Entering: '|| l_proc,5);
1745 P_APPROVAL_HISTORY_ID := getTransStateSequence(P_TRANSACTION_ID);
1746 OPEN cur_trans_details;
1747 FETCH cur_trans_details INTO l_trans_details_row;
1748 If l_trans_details_row.transaction_history_id IS NULL then
1749 -- insert into transaction history
1750 pqh_txh_ins.set_base_key_value(p_transaction_history_id => P_TRANSACTION_ID);
1751
1752 pqh_txh_ins.ins(
1753 p_creator_person_id => l_trans_details_row.creator_person_id
1754 ,p_assignment_id => l_trans_details_row.assignment_id
1755 ,p_selected_person_id => l_trans_details_row.selected_person_id
1756 ,p_item_type => l_trans_details_row.item_type
1757 ,p_item_key => l_trans_details_row.item_key
1758 ,p_process_name => l_trans_details_row.process_name
1759 ,p_function_id => l_trans_details_row.function_id
1760 ,p_rptg_grp_id => l_trans_details_row.rptg_grp_id
1761 ,p_plan_id => l_trans_details_row.plan_id
1762 ,p_transaction_group => l_trans_details_row.transaction_group
1763 ,p_transaction_identifier => l_trans_details_row.transaction_identifier
1764 ,p_transaction_history_id => P_TRANSACTION_ID
1765 );
1766 End IF;
1767
1768 P_APPROVAL_HISTORY_ID := nvl(P_APPROVAL_HISTORY_ID,0) + 1;
1769 -- insert into transaction state history
1770 pqh_tsh_ins.set_base_key_value(
1771 p_transaction_history_id => P_TRANSACTION_ID
1772 ,P_APPROVAL_HISTORY_ID => P_APPROVAL_HISTORY_ID
1773 );
1774
1775 pqh_tsh_ins.ins(
1776 p_creator_person_id => l_trans_details_row.creator_person_id
1777 ,p_creator_role => l_trans_details_row.creator_role
1778 ,p_status => l_trans_details_row.status
1779 ,p_transaction_state => l_trans_details_row.transaction_state
1780 ,p_effective_date => l_trans_details_row.transaction_effective_date
1781 ,p_effective_date_option => l_trans_details_row.effective_date_option
1782 ,p_last_update_role => l_trans_details_row.last_update_role
1783 ,p_parent_transaction_id => l_trans_details_row.parent_transaction_id
1784 ,p_relaunch_function => l_trans_details_row.relaunch_function
1785 ,p_transaction_document => l_trans_details_row.transaction_document
1786 ,p_transaction_history_id => P_TRANSACTION_ID
1787 ,P_APPROVAL_HISTORY_ID => P_APPROVAL_HISTORY_ID);
1788
1789 -- insert into transaction steps history
1790 SaveTransStepHistory (
1791 P_TRANSACTION_ID => P_TRANSACTION_ID
1792 ,P_APPROVAL_HISTORY_ID => P_APPROVAL_HISTORY_ID
1793 );
1794 CLOSE cur_trans_details;
1795 hr_utility.set_location('Leaving: '|| l_proc,10);
1796 Exception
1797 when OTHERS then
1798 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1799 If cur_trans_details%IsOpen Then
1800 CLOSE cur_trans_details;
1801 End If;
1802 raise;
1803 End SaveTransactionHistory;
1804
1805 --
1806 --
1807 Procedure ARCHIVE_ACTION
1808 (
1809 P_TRANSACTION_ID IN NUMBER
1810 ,P_NOTIFICATION_ID IN NUMBER
1811 ,P_USER_NAME IN VARCHAR2
1812 ,P_USER_COMMENT IN VARCHAR2 DEFAULT NULL
1813 ,P_ACTION IN VARCHAR2
1814 )
1815 IS
1816 l_action VARCHAR2(15) := 'TRANSFER';
1817 l_seq_id NUMBER(5);
1818 l_trans_id NUMBER(15);
1819 l_proc constant varchar2(100) := g_package || ' ARCHIVE_ACTION';
1820 p_return_status varchar2(100) := null;
1821 l_encoded varchar2(2000);
1822 l_message_type varchar2(1);
1823
1824
1825 Begin
1826 --
1827 --hr_utility.trace_on(null, 'TIGER');
1828 --g_debug := TRUE;
1829 --
1830
1831 hr_multi_message.enable_message_list;
1832
1833 hr_utility.set_location('Entering: '|| l_proc,5);
1834 deleteStaleData(P_TRANSACTION_ID, P_ACTION);
1835 l_trans_id := P_TRANSACTION_ID;
1836 If (P_ACTION = 'RESUBMIT' AND P_NOTIFICATION_ID IS NULL)
1837 OR (P_ACTION IN ('SFL','SUBMIT')) Then
1838
1839 SaveTransactionHistory
1840 (
1841 P_TRANSACTION_ID => l_trans_id
1842 ,P_APPROVAL_HISTORY_ID => l_seq_id
1843 );
1844
1845 Else
1846 l_seq_id := getTransStateSequence(P_TRANSACTION_ID => P_TRANSACTION_ID);
1847 End If;
1848
1849 -- For resumit case the notification call back will handle th routing history
1850 IF (P_NOTIFICATION_ID IS NOT NULL OR P_ACTION in ('SFL','SUBMIT')) THEN
1851 l_seq_id := nvl(l_seq_id, 1);
1852 SaveRoutingHistory
1853 (
1854 P_TRANSACTION_ID => l_trans_id
1855 ,P_APPROVAL_HISTORY_ID => l_seq_id
1856 ,P_NOTIFICATION_ID => P_NOTIFICATION_ID
1857 ,P_ACTION => P_ACTION
1858 ,P_USER_NAME => P_USER_NAME
1859 ,P_USER_COMMENT => P_USER_COMMENT
1860 );
1861 END IF;
1862
1863 -- Fix for Bug 10302076
1864 p_return_status := hr_multi_message.get_return_status_disable;
1865 if(p_return_status = 'E') then
1866
1867 FOR I IN 1..fnd_msg_pub.count_msg LOOP
1868 l_encoded := fnd_msg_pub.get_detail(p_msg_index => I);
1869 fnd_message.set_encoded(l_encoded);
1870 l_message_type := fnd_message.get_token
1871 (token => fnd_msg_pub.g_message_type_token_name);
1872 if l_message_type = hr_multi_message.g_error_msg then
1873 hr_utility.set_location('Error in Multi Message List : ' || l_encoded, 545);
1874 end if;
1875 end loop;
1876
1877 end if;
1878 -- End of Fix for Bug 10302076
1879
1880
1881 hr_utility.set_location('Leaving: '|| l_proc,10);
1882 Exception
1883 when OTHERS then
1884 --ROLLBACK;
1885 -- Fix for Bug 10302076
1886
1887 p_return_status := hr_multi_message.get_return_status_disable;
1888 if(p_return_status = 'E') then
1889 FOR I IN 1..fnd_msg_pub.count_msg LOOP
1890 l_encoded := fnd_msg_pub.get_detail(p_msg_index => I);
1891 fnd_message.set_encoded(l_encoded);
1892 l_message_type := fnd_message.get_token
1893 (token => fnd_msg_pub.g_message_type_token_name);
1894 if l_message_type = hr_multi_message.g_error_msg then
1895 hr_utility.set_location('Error in Multi Message List : ' || l_encoded,550);
1896 end if;
1897 end loop;
1898
1899 end if;
1900 hr_utility.set_location('SQLERRM : '|| SQLERRM,555);
1901 hr_utility.set_location('SQLCODE : '|| to_char(SQLCODE),555);
1902 raise;
1903
1904 --Emd of Fix for Bug 10302076
1905 End ARCHIVE_ACTION;
1906
1907 --
1908 --
1909 Procedure ARCHIVE_SUBMIT
1910 (
1911 P_TRANSACTION_ID IN NUMBER
1912 ,P_NOTIFICATION_ID IN NUMBER
1913 ,P_USER_NAME IN VARCHAR2
1914 ,P_USER_COMMENT IN VARCHAR2
1915 )
1916 IS
1917 l_proc constant varchar2(100) := g_package || ' ARCHIVE_SUBMIT';
1918 Begin
1919 --
1920 -- hr_utility.trace_on(null, 'TIGER');
1921 --g_debug := TRUE;
1922 --
1923 hr_utility.set_location('Entering: '|| l_proc,5);
1924 ARCHIVE_ACTION
1925 (
1926 P_TRANSACTION_ID => P_TRANSACTION_ID
1927 ,P_NOTIFICATION_ID => P_NOTIFICATION_ID
1928 ,P_USER_NAME => P_USER_NAME
1929 ,P_USER_COMMENT => P_USER_COMMENT
1930 ,P_ACTION => 'SUBMIT'
1931 );
1932 hr_utility.set_location('Leaving: '|| l_proc,10);
1933 Exception
1934 when OTHERS then
1935 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1936 raise;
1937 End ARCHIVE_SUBMIT;
1938
1939 --
1940 --
1941 Procedure ARCHIVE_RESUBMIT
1942 (
1943 P_TRANSACTION_ID IN NUMBER
1944 ,P_NOTIFICATION_ID IN NUMBER
1945 ,P_USER_NAME IN VARCHAR2
1946 ,P_USER_COMMENT IN VARCHAR2
1947 )
1948 IS
1949 l_proc constant varchar2(100) := g_package || ' ARCHIVE_RESUBMIT';
1950 Begin
1951 hr_utility.set_location('Entering: '|| l_proc,5);
1952 ARCHIVE_ACTION
1953 (
1954 P_TRANSACTION_ID => P_TRANSACTION_ID
1955 ,P_NOTIFICATION_ID => P_NOTIFICATION_ID
1956 ,P_USER_NAME => P_USER_NAME
1957 ,P_USER_COMMENT => P_USER_COMMENT
1958 ,P_ACTION => 'RESUBMIT'
1959 );
1960 hr_utility.set_location('Leaving: '|| l_proc,10);
1961 Exception
1962 when OTHERS then
1963 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1964 raise;
1965 End ARCHIVE_RESUBMIT;
1966
1967 Procedure ARCHIVE_SFL
1968 (
1969 P_TRANSACTION_ID IN NUMBER
1970 ,P_NOTIFICATION_ID IN NUMBER
1971 ,P_USER_NAME IN VARCHAR2
1972 )
1973 IS
1974 l_proc constant varchar2(100) := g_package || ' ARCHIVE_SFL';
1975
1976 Begin
1977 --
1978 -- hr_utility.trace_on(null, 'TIGER');
1979 --g_debug := TRUE;
1980 --
1981 hr_utility.set_location('Entering: '|| l_proc,5);
1982 ARCHIVE_ACTION
1983 (
1984 P_TRANSACTION_ID => P_TRANSACTION_ID
1985 ,P_NOTIFICATION_ID => P_NOTIFICATION_ID
1986 ,P_USER_NAME => P_USER_NAME
1987 ,P_ACTION => 'SFL'
1988 );
1989 hr_utility.set_location('Leaving: '|| l_proc,10);
1990 Exception
1991 when OTHERS then
1992 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
1993 raise;
1994 End ARCHIVE_SFL;
1995
1996 Procedure ARCHIVE_APPROVE
1997 (
1998 P_TRANSACTION_ID IN NUMBER
1999 ,P_NOTIFICATION_ID IN NUMBER
2000 ,P_USER_NAME IN VARCHAR2
2001 ,P_USER_COMMENT IN VARCHAR2
2002 )
2003 IS
2004 l_proc constant varchar2(100) := g_package || ' ARCHIVE_APPROVE';
2005 Begin
2006 hr_utility.set_location('Entering: '|| l_proc,5);
2007 ARCHIVE_ACTION
2008 (
2009 P_TRANSACTION_ID => P_TRANSACTION_ID
2010 ,P_NOTIFICATION_ID => P_NOTIFICATION_ID
2011 ,P_USER_NAME => P_USER_NAME
2012 ,P_USER_COMMENT => P_USER_COMMENT
2013 ,P_ACTION => 'APPROVED'
2014 );
2015 hr_utility.set_location('Leaving: '|| l_proc,10);
2016 Exception
2017 when OTHERS then
2018 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
2019 raise;
2020 End ARCHIVE_APPROVE;
2021
2022 Procedure ARCHIVE_DELETE
2023 (
2024 P_TRANSACTION_ID IN NUMBER
2025 ,P_NOTIFICATION_ID IN NUMBER
2026 ,P_USER_NAME IN VARCHAR2
2027 ,P_USER_COMMENT IN VARCHAR2
2028 )
2029 IS
2030 l_proc constant varchar2(100) := g_package || ' ARCHIVE_DELETE';
2031 Begin
2032 hr_utility.set_location('Entering: '|| l_proc,5);
2033 ARCHIVE_ACTION
2034 (
2035 P_TRANSACTION_ID => P_TRANSACTION_ID
2036 ,P_NOTIFICATION_ID => P_NOTIFICATION_ID
2037 ,P_USER_NAME => P_USER_NAME
2038 ,P_USER_COMMENT => P_USER_COMMENT
2039 ,P_ACTION => 'DELETED'
2040 );
2041 hr_utility.set_location('Leaving: '|| l_proc,10);
2042 Exception
2043 when OTHERS then
2044 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
2045 raise;
2046 End ARCHIVE_DELETE;
2047
2048 Procedure ARCHIVE_REJECT
2049 (
2050 P_TRANSACTION_ID IN NUMBER
2051 ,P_NOTIFICATION_ID IN NUMBER
2052 ,P_USER_NAME IN VARCHAR2
2053 ,P_USER_COMMENT IN VARCHAR2
2054 )
2055 IS
2056 l_proc constant varchar2(100) := g_package || ' ARCHIVE_REJECT';
2057 Begin
2058 hr_utility.set_location('Entering: '|| l_proc,5);
2059 ARCHIVE_ACTION
2060 (
2061 P_TRANSACTION_ID => P_TRANSACTION_ID
2062 ,P_NOTIFICATION_ID => P_NOTIFICATION_ID
2063 ,P_USER_NAME => P_USER_NAME
2064 ,P_USER_COMMENT => P_USER_COMMENT
2065 ,P_ACTION => 'REJECTED'
2066 );
2067 hr_utility.set_location('Leaving: '|| l_proc,10);
2068 Exception
2069 when OTHERS then
2070 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
2071 raise;
2072 End ARCHIVE_REJECT;
2073
2074 Procedure ARCHIVE_RFC
2075 (
2076 P_TRANSACTION_ID IN NUMBER
2077 ,P_NOTIFICATION_ID IN NUMBER
2078 ,P_USER_NAME IN VARCHAR2
2079 ,P_USER_COMMENT IN VARCHAR2
2080 )
2081 IS
2082 l_proc constant varchar2(100) := g_package || ' ARCHIVE_RFC';
2083 Begin
2084 hr_utility.set_location('Entering: '|| l_proc,5);
2085 ARCHIVE_ACTION
2086 (
2087 P_TRANSACTION_ID => P_TRANSACTION_ID
2088 ,P_NOTIFICATION_ID => P_NOTIFICATION_ID
2089 ,P_USER_NAME => P_USER_NAME
2090 ,P_USER_COMMENT => P_USER_COMMENT
2091 ,P_ACTION => 'RFC'
2092 );
2093 hr_utility.set_location('Leaving: '|| l_proc,10);
2094 Exception
2095 when OTHERS then
2096 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
2097 raise;
2098 End ARCHIVE_RFC;
2099
2100 Procedure ARCHIVE_TRANSFER
2101 (
2102 P_TRANSACTION_ID IN NUMBER
2103 ,P_NOTIFICATION_ID IN NUMBER
2104 ,P_USER_NAME IN VARCHAR2
2105 ,P_USER_COMMENT IN VARCHAR2
2106 )
2107 IS
2108 l_proc constant varchar2(100) := g_package || ' ARCHIVE_TRANSFER';
2109 Begin
2110 --
2111 -- hr_utility.trace_on(null, 'TIGER');
2112 --g_debug := TRUE;
2113 --
2114 hr_utility.set_location('Entering: '|| l_proc,5);
2115 ARCHIVE_ACTION
2116 (
2117 P_TRANSACTION_ID => P_TRANSACTION_ID
2118 ,P_NOTIFICATION_ID => P_NOTIFICATION_ID
2119 ,P_USER_NAME => P_USER_NAME
2120 ,P_USER_COMMENT => P_USER_COMMENT
2121 ,P_ACTION => 'TRANSFER'
2122 );
2123 hr_utility.set_location('Leaving: '|| l_proc,10);
2124 Exception
2125 when OTHERS then
2126 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
2127 raise;
2128 End ARCHIVE_TRANSFER;
2129
2130 Procedure ARCHIVE_FORWARD
2131 (
2132 P_TRANSACTION_ID IN NUMBER
2133 ,P_NOTIFICATION_ID IN NUMBER
2134 ,P_USER_NAME IN VARCHAR2
2135 ,P_USER_COMMENT IN VARCHAR2
2136 )
2137 IS
2138 l_proc constant varchar2(100) := g_package || ' ARCHIVE_FORWARD';
2139 Begin
2140 hr_utility.set_location('Entering: '|| l_proc,5);
2141 ARCHIVE_ACTION
2142 (
2143 P_TRANSACTION_ID => P_TRANSACTION_ID
2144 ,P_NOTIFICATION_ID => P_NOTIFICATION_ID
2145 ,P_USER_NAME => P_USER_NAME
2146 ,P_USER_COMMENT => P_USER_COMMENT
2147 ,P_ACTION => 'FORWARD'
2148 );
2149 hr_utility.set_location('Leaving: '|| l_proc,10);
2150 Exception
2151 when OTHERS then
2152 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
2153 raise;
2154 End ARCHIVE_FORWARD;
2155
2156 Procedure ARCHIVE_REQ_MOREINFO
2157 (
2158 P_TRANSACTION_ID IN NUMBER
2159 ,P_NOTIFICATION_ID IN NUMBER
2160 ,P_USER_NAME IN VARCHAR2
2161 ,P_USER_COMMENT IN VARCHAR2
2162 )
2163 IS
2164 l_proc constant varchar2(100) := g_package || ' ARCHIVE_REQ_MOREINFO';
2165 Begin
2166 hr_utility.set_location('Entering: '|| l_proc,5);
2167 ARCHIVE_ACTION
2168 (
2169 P_TRANSACTION_ID => P_TRANSACTION_ID
2170 ,P_NOTIFICATION_ID => P_NOTIFICATION_ID
2171 ,P_USER_NAME => P_USER_NAME
2172 ,P_USER_COMMENT => P_USER_COMMENT
2173 ,P_ACTION => 'QUESTION'
2174 );
2175 hr_utility.set_location('Leaving: '|| l_proc,10);
2176 Exception
2177 when OTHERS then
2178 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
2179 raise;
2180 End ARCHIVE_REQ_MOREINFO;
2181
2182 Procedure ARCHIVE_ANSWER_MOREINFO
2183 (
2184 P_TRANSACTION_ID IN NUMBER
2185 ,P_NOTIFICATION_ID IN NUMBER
2186 ,P_USER_NAME IN VARCHAR2
2187 ,P_USER_COMMENT IN VARCHAR2
2188 )
2189 IS
2190 l_proc constant varchar2(100) := g_package || ' ARCHIVE_ANSWER_MOREINFO';
2191 Begin
2192 hr_utility.set_location('Entering: '|| l_proc,5);
2193 ARCHIVE_ACTION
2194 (
2195 P_TRANSACTION_ID => P_TRANSACTION_ID
2196 ,P_NOTIFICATION_ID => P_NOTIFICATION_ID
2197 ,P_USER_NAME => P_USER_NAME
2198 ,P_USER_COMMENT => P_USER_COMMENT
2199 ,P_ACTION => 'ANSWER'
2200 );
2201 hr_utility.set_location('Leaving: '|| l_proc,10);
2202 Exception
2203 when OTHERS then
2204 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
2205 raise;
2206 End ARCHIVE_ANSWER_MOREINFO;
2207
2208 Procedure CANCEL_ACTION
2209 (
2210 P_TRANSACTION_ID IN NUMBER
2211 )
2212 IS
2213 l_proc constant varchar2(100) := g_package || ' CANCEL_ACTION';
2214 PRAGMA AUTONOMOUS_TRANSACTION;
2215 BEGIN
2216 --
2217 -- hr_utility.trace_on(null, 'TIGER');
2218 --g_debug := TRUE;
2219 --
2220 hr_utility.set_location('Entering: '|| l_proc,5);
2221
2222 RevertToLastSave
2223 (
2224 P_TRANSACTION_ID => P_TRANSACTION_ID
2225 );
2226 hr_utility.set_location('Leaving: '|| l_proc,10);
2227 commit;
2228 Exception
2229 when OTHERS then
2230 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
2231 ROLLBACK;
2232 RAISE;
2233 END CANCEL_ACTION;
2234
2235 Procedure ARCHIVE_TIMEOUT
2236 (
2237 P_TRANSACTION_ID IN NUMBER
2238 ,P_NOTIFICATION_ID IN NUMBER
2239 ,P_USER_NAME IN VARCHAR2
2240 ,P_USER_COMMENT IN VARCHAR2
2241 )
2242 IS
2243 l_proc constant varchar2(100) := g_package || ' ARCHIVE_TIMEOUT';
2244 Begin
2245 hr_utility.set_location('Entering: '|| l_proc,5);
2246 ARCHIVE_ACTION
2247 (
2248 P_TRANSACTION_ID => P_TRANSACTION_ID
2249 ,P_NOTIFICATION_ID => P_NOTIFICATION_ID
2250 ,P_USER_NAME => P_USER_NAME
2251 ,P_USER_COMMENT => P_USER_COMMENT
2252 ,P_ACTION => 'TIMEOUT'
2253 );
2254 hr_utility.set_location('Leaving: '|| l_proc,10);
2255 Exception
2256 when OTHERS then
2257 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
2258 raise;
2259 End ARCHIVE_TIMEOUT;
2260
2261 END HR_TRANS_HISTORY_API;