[Home] [Help]
PACKAGE BODY: APPS.PQH_PROCESS_EMP_REVIEW
Source
1 PACKAGE BODY pqh_process_emp_review AS
2 /* $Header: pqrewpkg.pkb 120.0 2005/05/29 02:27:24 appldev noship $ */
3 --
4 Cursor getUserName(p_person_id in Number) is
5 Select user_name
6 From fnd_user
7 Where employee_id = p_person_id;
8 --
9 FUNCTION get_reviewers (
10 p_transaction_step_id in varchar2
11 ,p_show_deleted in varchar2 default NULL ) RETURN ref_cursor IS
12
13 csr REF_CURSOR;
14 BEGIN
15
16 OPEN csr FOR
17 SELECT
18 to_number(reviewer.event_id) event_id,
19 to_number(reviewer.booking_id) Booking_id,
20 pf.full_name employee_name,
21 reviewer.employee_no employee_number,
22 to_number(reviewer.person_id) person_id,
23 rownum -1 row_index,
24 reviewer.comments,
25 reviewer.status,
26 reviewer.business_group_id
27 FROM (
28 SELECT hr_transaction_api.get_varchar2_value (p_transaction_step_id , 'P_EVENT_ID'||x_row) event_id,
29 hr_transaction_api.get_varchar2_value (p_transaction_step_id , 'P_BOOKING_ID'||x_row) booking_id,
30 hr_transaction_api.get_varchar2_value (p_transaction_step_id , 'P_EMPLOYEE_NO'||x_row) employee_no,
31 hr_transaction_api.get_varchar2_value (p_transaction_step_id , 'P_PERSON_ID'||x_row) person_id,
32 hr_transaction_api.get_varchar2_value (p_transaction_step_id , 'P_COMMENTS'||x_row) comments,
33 hr_transaction_api.get_varchar2_value (p_transaction_step_id , 'P_STATUS'||x_row) status,
34 hr_transaction_api.get_varchar2_value (p_transaction_step_id , 'P_BUSINESS_GROUP_ID'||x_row) business_group_id
35 FROM ( select substr(abc.name,14) x_row
36 from hr_api_transaction_values abc
37 where abc.transaction_step_id = p_transaction_step_id
38 and abc.name like 'P_EMPLOYEE_NO%'
39 ) x
40 ) reviewer,
41 per_all_people_f pf
42 WHERE pf.person_id = reviewer.person_id
43 AND (p_show_deleted = 'Y' or NVL(status,'E') <> 'D' ) -- Don't show deleted reviewers
44 AND SYSDATE BETWEEN pf.effective_start_date AND pf.effective_end_date;
45
46 RETURN csr;
47 END;
48
49
50 FUNCTION get_employee_review (
51 p_transaction_step_id in varchar2 ) RETURN ref_cursor IS
52
53 csr REF_CURSOR;
54
55 BEGIN
56 OPEN csr FOR
57 SELECT
58 to_number(review.event_id) event_id,
59 review.Type,
60 hl.meaning,
61 fnd_date.canonical_to_date(review.date_start) date_start,
62 fnd_date.canonical_to_date(review.date_end) date_end,
63 review.time_start time_start,
64 review.time_end time_end ,
65 to_number(review.location_id) location_id ,
66 loc.location_code location,
67 review.comments ,
68 to_number(review.assignment_id) assignment_id ,
69 review.notify_flag,
70 review.business_group_id
71 FROM (
72 SELECT
73 max(event_id) event_id,
74 max(Type) type,
75 max(date_start) date_start,
76 max(date_end) date_end,
77 max(time_start) time_start,
78 max(time_end) time_end,
79 max(location_id) location_id,
80 max(comments) comments,
81 max(assignment_id) assignment_id,
82 max(notify_flag) notify_flag,
83 max(business_group_id) business_group_id
84 FROM (
85 SELECT
86 decode(a.name, 'P_EVENT_ID' , a.varchar2_value ,null) Event_Id,
87 decode(a.name, 'P_TYPE' , a.varchar2_value ,null) Type,
88 decode(a.name, 'P_DATE_START' , a.varchar2_value ,null) Date_Start,
89 decode(a.name, 'P_DATE_END' , a.varchar2_value ,null) Date_End,
90 decode(a.name, 'P_TIME_START' , a.varchar2_value ,null) Time_Start,
91 decode(a.name, 'P_TIME_END' , a.varchar2_value ,null) Time_End,
92 decode(a.name, 'P_LOCATION_ID' , a.varchar2_value ,null) Location_Id,
93 decode(a.name, 'P_COMMENTS' , a.varchar2_value ,null) Comments,
94 decode(a.name, 'P_ASSIGNMENT_ID' , a.varchar2_value ,null) Assignment_Id,
95 decode(a.name, 'P_NOTIFY_FLAG' , a.varchar2_value ,null) Notify_Flag,
96 decode(a.name, 'P_BUSINESS_GROUP_ID' , a.varchar2_value ,null) Business_Group_Id
97 FROM hr_api_transaction_steps s,
98 hr_api_transaction_values a
99 WHERE s.transaction_step_id = a.transaction_step_id
100 AND s.transaction_step_id = p_transaction_step_id
101 AND s.api_name = 'PQH_PROCESS_EMP_REVIEW.PROCESS_API'
102 )
103 ) review ,
104 hr_lookups hl,
105 hr_locations loc
106 where hl.lookup_type = 'EMP_INTERVIEW_TYPE'
107 AND hl.lookup_code = review.type
108 AND loc.location_id (+) = review.location_id
109 AND sysdate <= nvl(loc.inactive_date, sysdate);
110
111 RETURN csr;
112 END get_employee_review;
113
114
115 PROCEDURE rollback_transaction(
116 itemType IN VARCHAR2,
117 itemKey IN VARCHAR2,
118 result OUT NOCOPY VARCHAR2) IS
119 BEGIN
120 --
121 savepoint rollback_transaction;
122 --
123 wf_engine.setItemAttrNumber (
124 itemType => itemType,
125 itemKey => itemKey,
126 aname => 'TRANSACTION_ID',
127 avalue => null );
128 --
129 --
130 hr_transaction_ss.rollback_transaction (
131 itemType => itemType,
132 itemKey => itemKey,
133 actid => 0,
134 funmode => 'RUN',
135 result => result );
136 --
137 --
138 result := 'SUCCESS';
139 --
140 --
141 EXCEPTION
142 --
143 WHEN Others THEN
144 rollback to rollback_transaction;
145 result := 'FAILURE';
146 --
147 END rollback_transaction;
148
149 PROCEDURE get_emp_review_details(
150 x_transaction_step_id IN VARCHAR2,
151 x_Event_Id OUT NOCOPY NUMBER,
152 x_Assignment_Id OUT NOCOPY NUMBER,
153 x_Type OUT NOCOPY VARCHAR2,
154 x_Date_Start OUT NOCOPY VARCHAR2,
155 x_Date_End OUT NOCOPY VARCHAR2,
156 x_Time_Start OUT NOCOPY VARCHAR2,
157 x_Time_End OUT NOCOPY VARCHAR2,
158 x_Location_Id OUT NOCOPY NUMBER,
159 x_Comments OUT NOCOPY VARCHAR2,
160 x_Business_Group_Id OUT NOCOPY NUMBER,
161 x_notify_flag OUT NOCOPY VARCHAR2
162 ) IS
163 ----
164
165 l_transaction_step_id number;
166 l_api_name hr_api_transaction_steps.api_name%TYPE;
167
168
169 BEGIN
170
171 hr_utility.set_location('Entering: PQH_PROCESS_EMP_REVIEW.get_emp_review_details',5);
172 --
173 l_transaction_step_id := to_number(x_transaction_step_id);
174 --
175
176 if l_transaction_step_id is null then
177 return;
178 end if;
179 --
180
181 x_event_Id := hr_transaction_api.get_varchar2_value
182 (p_transaction_step_id => l_transaction_step_id,
183 p_name => 'P_EVENT_ID');
184
185 x_Assignment_Id := hr_transaction_api.get_varchar2_value
186 (p_transaction_step_id => l_transaction_step_id,
187 p_name => 'P_ASSIGNMENT_ID');
188
189 x_Type := hr_transaction_api.get_varchar2_value
190 (p_transaction_step_id => l_transaction_step_id,
191 p_name => 'P_TYPE');
192
193
194
195
196 x_Date_Start := hr_transaction_api.get_varchar2_value
197 (p_transaction_step_id => l_transaction_step_id,
198 p_name => 'P_DATE_START');
199
200 x_Date_End :=hr_transaction_api.get_varchar2_value
201 (p_transaction_step_id => l_transaction_step_id,
202 p_name => 'P_DATE_END');
203
204 x_Time_Start :=hr_transaction_api.get_varchar2_value
205 (p_transaction_step_id => l_transaction_step_id,
206 p_name => 'P_TIME_START');
207
208 x_Time_End := hr_transaction_api.get_varchar2_value
209 (p_transaction_step_id => l_transaction_step_id,
210 p_name => 'P_TIME_END');
211
212
213
214 x_Location_id := hr_transaction_api.get_varchar2_value
215 (p_transaction_step_id => l_transaction_step_id,
216 p_name => 'P_LOCATION_ID');
217
218 x_Comments := hr_transaction_api.get_varchar2_value
219 (p_transaction_step_id => l_transaction_step_id,
220 p_name => 'P_COMMENTS');
221
222 x_Business_Group_Id := hr_transaction_api.get_varchar2_value
223 (p_transaction_step_id => l_transaction_step_id,
224 p_name => 'P_BUSINESS_GROUP_ID' );
225
226
227
228 x_notify_flag := hr_transaction_api.get_varchar2_value
229 (p_transaction_step_id => l_transaction_step_id,
230 p_name => 'P_NOTIFY_FLAG');
231
232 /* x_person_id := hr_transaction_api.get_number_value
233 (p_transaction_step_id => l_transaction_step_id,
234 p_name => 'P_PERSON_ID' );
235
236 */
237
238 hr_utility.set_location('Leaving: PQH_PROCESS_EMP_REVIEW.get_emp_review_details',10);
239 EXCEPTION
240 WHEN hr_utility.hr_error THEN
241 hr_utility.raise_error;
242 WHEN OTHERS THEN
243 x_Event_Id := null;
244 x_Assignment_Id := null;
245 x_Type := null;
246 x_Date_Start := null;
247 x_Date_End := null;
248 x_Time_Start := null;
249 x_Time_End := null;
250 x_Location_Id := null;
251 x_Comments := null;
252 x_Business_Group_Id := null;
253 x_notify_flag := null;
254
255 RAISE; -- Raise error here relevant to the new tech stack.
256 END get_emp_review_details;
257
258 --
259 PROCEDURE get_reviewers_count(
260 x_transaction_step_id IN VARCHAR2,
261 x_total_no_of_rows OUT NOCOPY NUMBER,
262 x_total_deleted_rows OUT NOCOPY NUMBER) is
263
264 cursor Rec_Count(p_step_id varchar2) is
265 select count(*)
266 from hr_api_transaction_values
267 where transaction_step_id = p_step_id
268 and name like 'P_EMPLOYEE_NO_';
269
270 cursor Rec_Count_Deleted(p_step_id varchar2) is
271 select count(*)
272 from hr_api_transaction_values
273 where transaction_step_id = p_step_id
274 and name like 'P_EMPLOYEE_NO%D';
275
276
277 BEGIN
278 --l_column_name := ''''||x_column_name||'%'||'''';
279
280 open Rec_Count(x_transaction_step_id);
281
282 fetch Rec_Count into x_total_no_of_rows;
283
284 close Rec_Count;
285
286
287
288 open Rec_Count_Deleted(x_transaction_step_id);
289
290 Fetch Rec_Count_Deleted into x_total_deleted_rows;
291
292 close Rec_Count_Deleted;
293
294
295 Exception
296 When Others Then
297 x_total_no_of_rows := null;
298 x_total_deleted_rows := null;
299 --raise; --for nocopy changes not puting raise here because the null below was already there.
300 null;
301 End;
302
303
304
305
306 --
307
308 PROCEDURE get_emp_reviewers_details(
309 x_transaction_step_id IN VARCHAR2,
310 x_Event_Id OUT NOCOPY NUMBER,
311 x_Booking_Id OUT NOCOPY NUMBER,
312 x_Employee_no OUT NOCOPY VARCHAR2,
313 x_Comments OUT NOCOPY VARCHAR2,
314 x_Business_Group_Id OUT NOCOPY NUMBER ,
315 x_status OUT NOCOPY VARCHAR2,
316 x_row_number IN VARCHAR2 ,
317 x_person_id OUT NOCOPY NUMBER
318 ) IS
319
320
321 l_transaction_step_id number;
322 l_api_name hr_api_transaction_steps.api_name%TYPE;
323
324 BEGIN
325 hr_utility.set_location('Entering: PQH_PROCESS_EMP_REVIEW.get_emp_reviewers_details',5);
326 --
327 l_transaction_step_id := to_number(x_transaction_step_id);
328 --
329
330 if l_transaction_step_id is null then
331 return;
332 end if;
333 --
334
335
336 x_Event_Id := hr_transaction_api.get_varchar2_value
337 (p_transaction_step_id => l_transaction_step_id,
338 p_name => 'P_EVENT_ID'||x_row_number);
339
340
341 x_Booking_Id := hr_transaction_api.get_varchar2_value
342 (p_transaction_step_id => l_transaction_step_id,
343 p_name => 'P_BOOKING_ID'||x_row_number );
344
345
346 x_Employee_no := hr_transaction_api.get_varchar2_value
347 (p_transaction_step_id => l_transaction_step_id,
348 p_name => 'P_EMPLOYEE_NO'||x_row_number );
349
350 x_Comments := hr_transaction_api.get_varchar2_value
351 (p_transaction_step_id => l_transaction_step_id,
352 p_name => 'P_COMMENTS'||x_row_number );
353
354
355 x_Business_Group_Id := hr_transaction_api.get_varchar2_value
356 (p_transaction_step_id => l_transaction_step_id,
357 p_name => 'P_BUSINESS_GROUP_ID'||x_row_number );
358
359 x_status := hr_transaction_api.get_varchar2_value
360 (p_transaction_step_id => l_transaction_step_id,
361 p_name => 'P_STATUS'||x_row_number );
362
363 x_person_id := hr_transaction_api.get_varchar2_value
364 (p_transaction_step_id => l_transaction_step_id,
365 p_name => 'P_PERSON_ID'||x_row_number );
366
367
368 hr_utility.set_location('x_person_id'||x_person_id,10);
369 hr_utility.set_location('Leaving: PQH_PROCESS_EMP_REVIEW.get_emp_reviewers_details',10);
370 EXCEPTION
371 WHEN hr_utility.hr_error THEN
372 hr_utility.raise_error;
373 WHEN OTHERS THEN
374 x_Event_Id := null;
375 x_Booking_Id := null;
376 x_Employee_no := null;
377 x_Comments := null;
378 x_Business_Group_Id := null;
379 x_status := null;
383
380 x_person_id := null;
381 RAISE; -- Raise error here relevant to the new tech stack.
382 END get_emp_reviewers_details;
384 --
385 --
386
387 PROCEDURE set_emp_review_details(
388 x_Login_person_id IN NUMBER,
389 x_Person_id IN NUMBER,
390 x_Item_type IN VARCHAR2,
391 x_Item_key IN NUMBER,
392 x_Activity_id IN NUMBER,
393 x_Event_Id IN NUMBER,
394 x_Assignment_Id IN NUMBER,
395 x_Type IN VARCHAR2,
396 x_Date_Start IN VARCHAR2,
397 x_Date_End IN VARCHAR2,
398 x_Time_Start IN VARCHAR2,
399 x_Time_End IN VARCHAR2,
400 x_Location_Id IN NUMBER,
401 x_Comments IN VARCHAR2,
402 x_Business_Group_Id IN NUMBER,
403 x_notify_flag IN VARCHAR2
404 ) IS
405
406
407 l_transaction_id number;
408 l_trans_tbl hr_transaction_ss.transaction_table;
409 l_count number;
410 l_transaction_step_id number;
411 l_api_name constant hr_api_transaction_steps.api_name%TYPE := 'PQH_PROCESS_EMP_REVIEW.PROCESS_API';
412 l_result varchar2(100);
413 l_trns_object_version_number number;
414 l_review_proc_call VARCHAR2(30);
415 l_effective_date DATE ;
416
417 BEGIN
418 hr_utility.set_location('Entering: PQH_PROCESS_EMP_REVIEW.set_emp_review_details',5);
419 l_review_proc_call := 'PqhEmployeeReview';
420 l_effective_date := SYSDATE;
421 --
422
423
424 hr_transaction_api.get_transaction_step_info
425 (p_item_type => x_item_type
426 ,p_item_key => x_item_key
427 ,p_activity_id => x_activity_id
428 ,p_transaction_step_id => l_transaction_step_id
429 ,p_object_version_number => l_trns_object_version_number);
430
431
432
433 l_count:=1;
434 l_trans_tbl(l_count).param_name := 'P_PERSON_ID';
435 l_trans_tbl(l_count).param_value := x_Person_id;
436 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
437 --
438 l_count:=l_count+1;
439 l_trans_tbl(l_count).param_name := 'P_REVIEW_ACTID';
440 l_trans_tbl(l_count).param_value := x_activity_id;
441 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
442 --
443 l_count:=l_count+1;
444 l_trans_tbl(l_count).param_name := 'P_REVIEW_PROC_CALL';
445 l_trans_tbl(l_count).param_value := l_review_proc_call;
446 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
447 --
448 l_count:=l_count+1;
449 l_trans_tbl(l_count).param_name := 'P_EVENT_ID';
450 l_trans_tbl(l_count).param_value := x_Event_Id ;
451 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
452 --
453 l_count:=l_count+1;
454 l_trans_tbl(l_count).param_name := 'P_ASSIGNMENT_ID';
455 l_trans_tbl(l_count).param_value := x_Assignment_Id;
456 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
457 --
458 l_count:=l_count+1;
459 l_trans_tbl(l_count).param_name := 'P_TYPE';
460 l_trans_tbl(l_count).param_value := x_Type;
461 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
462 --
463 l_count:=l_count+1;
464 l_trans_tbl(l_count).param_name := 'P_DATE_START';
465 l_trans_tbl(l_count).param_value := x_Date_Start;
466 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
467 --
468 l_count:=l_count+1;
469 l_trans_tbl(l_count).param_name := 'P_DATE_END';
470 l_trans_tbl(l_count).param_value := x_Date_End;
471 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
472 --
473 l_count:=l_count+1;
474 l_trans_tbl(l_count).param_name := 'P_TIME_START';
475 l_trans_tbl(l_count).param_value := x_Time_Start;
476 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
477 --
478 l_count:=l_count+1;
479 l_trans_tbl(l_count).param_name := 'P_TIME_END';
480 l_trans_tbl(l_count).param_value := x_Time_End;
481 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
482 --
483 l_count:=l_count+1;
484 l_trans_tbl(l_count).param_name := 'P_LOCATION_ID';
485 l_trans_tbl(l_count).param_value := x_Location_Id ;
486 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
487
488 hr_utility.set_location('Entering: Location_ID:'||x_Location_Id,5);
489 --
490 l_count:=l_count+1;
491 l_trans_tbl(l_count).param_name := 'P_COMMENTS';
492 l_trans_tbl(l_count).param_value := x_Comments;
493 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
494 hr_utility.set_location('Entering: Comments:'||x_Comments,5);
495 --
496 l_count:=l_count+1;
497 l_trans_tbl(l_count).param_name := 'P_BUSINESS_GROUP_ID';
498 l_trans_tbl(l_count).param_value := x_Business_Group_Id;
499 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
500
501 --
502
503 l_count:=l_count+1;
504 l_trans_tbl(l_count).param_name := 'P_NOTIFY_FLAG';
505 l_trans_tbl(l_count).param_value := x_notify_flag;
506 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
507 --
508
509
513 (p_item_type => x_item_type
510 hr_utility.set_location('Entering: Business Group Id:'||x_Business_Group_Id,5);
511
512 hr_transaction_ss.save_transaction_step
514 ,p_item_key => x_item_key
515 ,p_actid => x_activity_id
516 ,p_login_person_id => x_login_person_id
517 ,p_transaction_step_id => l_transaction_step_id
518 ,p_api_name => l_api_name
519 ,p_transaction_data => l_trans_tbl );
520 hr_utility.set_location('Leaving: PQH_PROCESS_EMP_REVIEW.set_emp_review_details',10);
521
522 commit;
523 --- Saving the Transaction Step Id ,
524 --- With the same step-id Reviewers details also being saved.
525
526 -- x_trans_step_id := l_transaction_step_id;
527 --
528 --
529 EXCEPTION
530 WHEN hr_utility.hr_error THEN
531 hr_utility.raise_error;
532 WHEN OTHERS THEN
533 RAISE; -- Raise error here relevant to the new tech stack.
534 END set_emp_review_details;
535 --
536 --
537
538
539 PROCEDURE set_emp_reviewer_details(
540 x_login_person_id IN NUMBER,
541 x_person_id IN NUMBER,
542 x_item_type IN VARCHAR2,
543 x_item_key IN NUMBER,
544 x_activity_id IN NUMBER,
545 x_Event_Id IN NUMBER,
546 x_Booking_Id IN NUMBER,
547 x_Employee_no IN VARCHAR2,
548 x_Comments IN VARCHAR2,
549 x_Business_Group_Id IN NUMBER,
550 x_row_number IN VARCHAR2,
551 x_status IN VARCHAR2 ) IS
552
553 l_transaction_id number;
554 l_trans_tbl hr_transaction_ss.transaction_table;
555 l_count number;
556 l_transaction_step_id number;
557 l_api_name constant hr_api_transaction_steps.api_name%TYPE := 'PQH_PROCESS_EMP_REVIEW.PROCESS_API';
558 l_result varchar2(100);
559 l_trns_object_version_number number;
560 l_review_proc_call VARCHAR2(30);
561 l_effective_date DATE ;
562
563 BEGIN
564 hr_utility.set_location('Entering: PQH_PROCESS_EMP_REVIEW.set_emp_reviewer_details',5);
565 --
566 l_review_proc_call := 'PqhEmployeeReview';
567 l_effective_date := SYSDATE;
568
569
570 hr_transaction_api.get_transaction_step_info
571 (p_item_type => x_item_type
572 ,p_item_key => x_item_key
573 ,p_activity_id => x_activity_id
574 ,p_transaction_step_id => l_transaction_step_id
575 ,p_object_version_number => l_trns_object_version_number);
576
577
578 l_count:=1;
579 l_trans_tbl(l_count).param_name := 'P_PERSON_ID'||x_row_number ;
580 l_trans_tbl(l_count).param_value := x_Person_id;
581 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
582 --
583 l_count:=l_count+1;
584 l_trans_tbl(l_count).param_name := 'P_REVIEW_ACTID'||x_row_number ;
585 l_trans_tbl(l_count).param_value := x_activity_id;
586 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
587 --
588 l_count:=l_count+1;
589 l_trans_tbl(l_count).param_name := 'P_REVIEW_PROC_CALL'||x_row_number ;
590 l_trans_tbl(l_count).param_value := l_review_proc_call;
591 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
592 --
593 l_count:=l_count+1;
594 l_trans_tbl(l_count).param_name := 'P_EVENT_ID'||x_row_number ;
595 l_trans_tbl(l_count).param_value := x_Event_Id ;
596 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
597 --
598 l_count:=l_count+1;
599 l_trans_tbl(l_count).param_name := 'P_BOOKING_ID'||x_row_number ;
600 l_trans_tbl(l_count).param_value := x_booking_id;
601 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
602 --
603 l_count:=l_count+1;
604 l_trans_tbl(l_count).param_name := 'P_EMPLOYEE_NO'||x_row_number ;
605 l_trans_tbl(l_count).param_value := x_Employee_no ;
606 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
607 --
608 l_count:=l_count+1;
609 l_trans_tbl(l_count).param_name := 'P_COMMENTS'||x_row_number ;
610 l_trans_tbl(l_count).param_value := x_Comments;
611 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
612 --
613 l_count:=l_count+1;
614 l_trans_tbl(l_count).param_name := 'P_BUSINESS_GROUP_ID'||x_row_number ;
615 l_trans_tbl(l_count).param_value := x_Business_Group_Id;
616 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
617 --
618 l_count:=l_count+1;
619 l_trans_tbl(l_count).param_name := 'P_STATUS'||x_row_number;
620 l_trans_tbl(l_count).param_value := x_Status;
621 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
622
623
624
625 hr_transaction_ss.save_transaction_step
626 (p_item_type => x_item_type
627 ,p_item_key => x_item_key
628 ,p_actid => x_activity_id
629 ,p_login_person_id => x_login_person_id
630 ,p_transaction_step_id => l_transaction_step_id
631 ,p_api_name => l_api_name
632 ,p_transaction_data => l_trans_tbl );
633 hr_utility.set_location('Leaving: PQH_PROCESS_EMP_REVIEW.set_emp_review_details',10);
634
635 commit;
639 hr_utility.raise_error;
636
637 EXCEPTION
638 WHEN hr_utility.hr_error THEN
640 WHEN OTHERS THEN
641 RAISE; -- Raise error here relevant to the new tech stack.
642 END set_emp_reviewer_details;
643
644 --
645 --
646 -- Local procedure to set notification attributes
647 -- for notification send to the subject or the reviewers
648 procedure set_notification_attributes (
649 p_ntf_id IN NUMBER,
650 p_employee_name IN VARCHAR2,
651 p_review_type IN VARCHAR2,
652 p_date_start IN VARCHAR2,
653 p_date_end IN VARCHAR2,
654 p_time_start IN VARCHAR2,
655 p_time_end IN VARCHAR2,
656 p_location IN VARCHAR2,
657 p_comments IN VARCHAR2 ) IS
658 BEGIN
659 if p_employee_name is not null then
660 WF_NOTIFICATION.setAttrText(p_ntf_id,'PQH_EMP_NAME',p_employee_name);
661 end if;
662
663 if p_review_type is not null THEN
664 WF_NOTIFICATION.setAttrText(p_ntf_id, 'PQH_REVIEW_TYPE', p_review_type);
665 end if;
666
667 if p_date_start is not null then
668 WF_NOTIFICATION.setAttrText(p_ntf_id, 'PQH_START_DATE',
669 FND_DATE.date_to_displaydate(fnd_date.chardt_to_date(p_Date_Start)));
670 end if;
671
672 if p_date_end is not null then
673 WF_NOTIFICATION.setAttrText(p_ntf_id, 'PQH_END_DATE',
674 FND_DATE.date_to_displaydate(fnd_date.chardt_to_date(p_Date_End)));
675 end if;
676
677 if p_Time_Start is not null then
678 WF_NOTIFICATION.setAttrText(p_ntf_id, 'PQH_START_TIME', p_Time_Start);
679 end if;
680
681 if p_Time_End is not null then
682 WF_NOTIFICATION.setAttrText(p_ntf_id, 'PQH_END_TIME', p_Time_End);
683 end if;
684
685 if p_location is not null then
686 WF_NOTIFICATION.setAttrText(p_ntf_id, 'PQH_LOCATION', p_location);
687 end if;
688
689 if p_Comments is not null then
690 WF_NOTIFICATION.setAttrText(p_ntf_id, 'PQH_COMMENTS', p_Comments);
691 end if;
692
693 WF_NOTIFICATION.setAttrText(p_ntf_id,'#FROM_ROLE',fnd_global.user_name);
694
695 --Bug 3014549: Adding Commit as it is needed to display attribute values, just set, for the notification
696 COMMIT;
697
698 END set_notification_attributes;
699
700
701 --
702 --
703 --
704
705 PROCEDURE process_api (
706 p_validate IN BOOLEAN DEFAULT FALSE,
707 p_transaction_step_id IN NUMBER ) IS
708
709 l_Event_id NUMBER;
710 l_booking_id NUMBER;
711 l_Row_id VARCHAR2(100);
712 --
713 --
714 l_Location_Id NUMBER;
715 l_Assignment_Id NUMBER;
716 l_Date_Start VARCHAR2(12);
717 l_status VARCHAR2(10);
718 l_Type VARCHAR2(200);
719 l_Comments VARCHAR2(2000);
720 l_Date_End VARCHAR2(12);
721 l_Time_End VARCHAR2(200);
722 l_Time_Start VARCHAR2(200);
723 l_Business_Group_Id NUMBER;
724 l_notify_flag VARCHAR2(10);
725 l_userName VARCHAR2(100);
726 l_id Number;
727 l_person_Id Number;
728 l_employee_name VARCHAR2(240);
729 l_employee_no VARCHAR2(100);
730 l_ename VARCHAR2(240);
731
732 cursor getrowid(c_event_id in number) is
733 select rowid from per_events
734 where event_id = c_event_id;
735
736 --
737 cursor c_empName is
738 select wf_engine.GetItemAttrText(item_type, item_key, 'CURRENT_PERSON_DISPLAY_NAME')
739 from hr_api_transaction_steps
740 where transaction_step_id = p_transaction_step_id;
741
742 reviewer_csr REF_CURSOR;
743 empReviewCsr REF_CURSOR;
744
745 l_temp number;
746 l_location_code varchar2(80);
747 l_typeMeaning varchar2(240);
748
749 BEGIN
750
751 hr_utility.set_location('Entering: PQH_PROCESS_EMP_REVIEW.process_api',5);
752 --
753 savepoint process_emp_review_details;
754 --
755 empReviewCsr := get_employee_review(p_transaction_step_id);
756 fetch empReviewCsr into l_event_id, l_type, l_typeMeaning, l_date_start, l_date_end, l_time_start,
757 l_time_end, l_location_id, l_location_code, l_comments, l_Assignment_Id,
758 l_notify_flag, l_business_group_id;
759 close empReviewCsr;
760 --
761 l_person_id := hr_transaction_api.get_varchar2_value (
762 p_transaction_step_id => p_transaction_step_id,
763 p_name => 'P_PERSON_ID');
764
765 wf_directory.GetUserName
766 (p_orig_system => 'PER'
767 ,p_orig_system_id => l_person_id
768 ,p_name => l_username
769 ,p_display_name => l_employee_name);
770 --
771 -- Bug 3881664: Pick the employee name from WF Attribute current_person_display_name.
772 Open c_empName ;
773 Fetch c_empName into l_employee_name;
774 Close c_empName;
775
776 -- Send notification to the person if notify flag is turned on
777 if l_userName is not null then
781 set_notification_attributes (
778 if ( l_notify_flag = 'Y') then
779 l_id := WF_NOTIFICATION.send (l_userName,'HRSSA','PQH_EMP_REV_MSG',NULL,NULL,NULL,NULL,NULl);
780 --
782 p_ntf_id => l_id,
783 p_employee_name => l_employee_name,
784 p_review_type => l_typeMeaning,
785 p_date_start => l_date_start,
786 p_date_end => l_date_end,
787 p_time_start => l_time_start,
788 p_time_end => l_time_end,
789 p_location => l_location_code,
790 p_comments => l_comments);
791
792 End if; -- if notify flag is Y
793 --
794 End if; -- End if user name is not null
795
796 IF l_Event_id IS NOT NULL or l_Event_id <> '0' THEN
797 --
798 open getrowid(l_event_id);
799 fetch getrowid into l_Row_id;
800 close getrowid;
801
802 PER_EVENTS_PKG.Update_row(X_Rowid => l_Row_id,
803 X_Event_Id => l_Event_id,
804 X_Business_Group_Id => l_Business_Group_Id,
805 X_Location_Id => l_Location_id,
806 X_Internal_Contact_Person_Id => null,
807 X_Organization_Run_By_Id => null,
808 X_Assignment_Id => l_Assignment_Id,
809 X_Date_Start => fnd_date.chardt_to_date(l_Date_Start),
810 X_Type => l_Type,
811 X_Comments => l_Comments,
812 X_Contact_Telephone_Number => null ,
813 X_Date_End => fnd_date.chardt_to_date(l_Date_End ),
814 X_Emp_Or_Apl => 'E',
815 X_Event_Or_Interview => 'I' ,
816 X_External_Contact => null ,
817 X_Time_End => l_Time_End,
818 X_Time_Start => l_Time_Start,
819 X_Attribute_Category => null,
820 X_Attribute1 => null,
821 X_Attribute2 => null,
822 X_Attribute3 => null,
823 X_Attribute4 => null,
824 X_Attribute5 => null,
825 X_Attribute6 => null,
826 X_Attribute7 => null,
827 X_Attribute8 => null,
828 X_Attribute9 => null,
829 X_Attribute10 => null,
830 X_Attribute11 => null,
831 X_Attribute12 => null,
832 X_Attribute13 => null,
833 X_Attribute14 => null,
834 X_Attribute15 => null,
835 X_Attribute16 => null,
836 X_Attribute17 => null,
837 X_Attribute18 => null,
838 X_Attribute19 => null,
839 X_Attribute20 => null,
840 X_ctl_globals_end_of_time => fnd_date.chardt_to_date(l_Date_Start));
841 --
842 ELSE
843 --
844 PER_EVENTS_PKG.Insert_row(X_Rowid => l_Row_id,
845 X_Event_Id => l_Event_id,
846 X_Business_Group_Id => l_Business_Group_Id,
847 X_Location_Id => l_Location_id,
848 X_Internal_Contact_Person_Id => null,
849 X_Organization_Run_By_Id => null,
850 X_Assignment_Id => l_Assignment_Id,
851 X_Date_Start => fnd_date.chardt_to_date(l_Date_Start),
852 X_Type => l_Type,
853 X_Comments => l_Comments,
854 X_Contact_Telephone_Number => null ,
855 X_Date_End => fnd_date.chardt_to_date(l_Date_End ),
856 X_Emp_Or_Apl => 'E',
857 X_Event_Or_Interview => 'I' ,
858 X_External_Contact => null ,
859 X_Time_End => l_Time_End,
860 X_Time_Start => l_Time_Start,
861 X_Attribute_Category => null,
862 X_Attribute1 => null,
863 X_Attribute2 => null,
864 X_Attribute3 => null,
865 X_Attribute4 => null,
866 X_Attribute5 => null,
867 X_Attribute6 => null,
868 X_Attribute7 => null,
869 X_Attribute8 => null,
870 X_Attribute9 => null,
871 X_Attribute10 => null,
872 X_Attribute11 => null,
873 X_Attribute12 => null,
874 X_Attribute13 => null,
875 X_Attribute14 => null,
876 X_Attribute15 => null,
880 X_Attribute19 => null,
877 X_Attribute16 => null,
878 X_Attribute17 => null,
879 X_Attribute18 => null,
881 X_Attribute20 => null,
882 X_ctl_globals_end_of_time => fnd_date.chardt_to_date(l_Date_Start));
883 --
884 END IF;
885
886 pkg_event_id := l_Event_id;
887 reviewer_csr := get_reviewers ( p_transaction_step_id,'Y' );
888
889 while (true)
890 loop
891 fetch reviewer_csr into l_event_id, l_booking_id, l_ename, l_employee_no, l_person_id, l_temp,
892 l_comments, l_status, l_business_group_id ;
893 exit when reviewer_csr%notfound;
894 --
895 l_status := NVL(l_status,'N');
896 --
897 process_emp_reviewers_api (
898 p_validate => p_validate,
899 p_event_id => l_event_id,
900 p_booking_id => l_booking_id,
901 p_employee_no => l_employee_no,
902 p_comments => l_comments,
903 p_business_group_id => l_business_group_id,
904 p_status => l_status,
905 p_personId => l_person_Id );
906 --
907 wf_directory.GetUserName
908 (p_orig_system => 'PER'
909 ,p_orig_system_id => l_person_Id
910 ,p_name => l_username
911 ,p_display_name => l_ename );
912 --
913 IF ( l_userName IS NOT NULL AND l_status in ('D','N') ) THEN
914 --
915 If l_status = 'D' Then
916 l_id := WF_NOTIFICATION.send (l_userName,'HRSSA','PQH_EMP_REV_REMOVED_MSG',NULL,NULL,NULL,NULL,NULl);
917 elsif l_status = 'N' Then
918 l_id := WF_NOTIFICATION.send (l_userName,'HRSSA','PQH_EMP_REV_ADDED_MSG',NULL,NULL,NULL,NULL,NULl);
919 end if;
920 --
921 set_notification_attributes (
922 p_ntf_id => l_id,
923 p_employee_name => l_employee_name,
924 p_review_type => l_typeMeaning,
925 p_date_start => l_date_start,
926 p_date_end => l_date_end,
927 p_time_start => l_time_start,
928 p_time_end => l_time_end,
929 p_location => l_location_code,
930 p_comments => l_comments);
931 --
932 End If;
933 end loop;
934 --
935 --
936 close reviewer_csr ;
937 hr_utility.set_location('Leaving: PQH_PROCESS_EMP_REVIEW.process_emp_review_api',10);
938 EXCEPTION
939 WHEN hr_utility.hr_error THEN
940 ROLLBACK TO process_emp_review_details;
941 RAISE;
942 WHEN OTHERS THEN
943 ROLLBACK TO process_emp_review_details;
944 RAISE; -- Raise error here relevant to the new tech stack.
945 END process_api;
946
947 --
948 --
949 PROCEDURE process_emp_reviewers_api (
950 p_validate IN BOOLEAN DEFAULT FALSE,
951 p_Event_Id IN NUMBER,
952 p_Booking_Id IN NUMBER,
953 p_Employee_no IN VARCHAR2 ,
954 p_Comments IN VARCHAR2 ,
955 p_Business_Group_Id IN NUMBER,
956 p_status IN VARCHAR2,
957 p_personId IN NUMBER ) IS
958
959 l_Row_id VARCHAR2(100);
960 l_id number;
961 l_userName VARCHAR2(100);
962 l_MsgType VARCHAR2(100);
963 l_rowId VARCHAR2(100);
964 l_count Number;
965 l_booking_id Number;
966
967 Cursor getBookingRowid(p_Booking_id In Number) is
968 select rowid
969 from per_bookings
970 where Booking_id = p_Booking_id;
971
972 Cursor getBookingRowidForBooking(p_personId In Number,p_event_id In Number) is
973 Select rowid
974 From per_bookings
975 Where person_id =p_personId and event_id = p_event_id;
976
977 Cursor check_for_row_exsistance(X_Person_Id in Number,X_Event_Id in Number) is
978 Select count(*)
979 from per_bookings
980 where person_id = x_Person_Id and event_Id = x_Event_Id;
981
982 Begin
983 --
984 hr_utility.set_location('Entering: PQH_PROCESS_EMP_REVIEW.process_emp_review_api',5);
985 --
986 savepoint process_emp_reviewers_details;
987 --
988 hr_utility.set_location('p_personId '||p_personId||'Delete Place'||p_status,10);
989 --
990 If p_status = 'D' Then
991 --
992 open getBookingRowid(p_Booking_Id);
993 fetch getBookingRowid into l_rowId;
994 close getBookingRowid;
995 --
996 hr_utility.set_location('Row Id in Delete '||l_rowId,10);
997 per_bookings_pkg.Delete_Row(X_Rowid => l_rowId);
998 hr_utility.set_location(' After Row Id in Delete '||l_rowId,10);
999 --
1000 end if;
1001 --
1002 IF p_Booking_id IS NOT NULL or p_Booking_id <> '0' THEN
1003 --
1004 open getBookingRowidForBooking(p_personId,p_event_id);
1005 fetch getBookingRowidForBooking into l_rowId;
1006 close getBookingRowidForBooking;
1007 --
1008 hr_utility.set_location('Row Id '||l_rowId,10);
1009 hr_utility.set_location('Booking Id '||p_Booking_Id||'business Group Id '||p_Business_Group_Id||'event Id '||p_event_id,10);
1010 --
1014 X_Person_Id => p_personId,
1011 PER_BOOKINGS_PKG.Update_Row(X_Rowid => l_rowId,
1012 X_Booking_Id => p_Booking_Id ,
1013 X_Business_Group_Id => p_Business_Group_Id,
1015 X_Event_Id => p_event_id,
1016 X_Message => null,
1017 X_Token => null,
1018 X_Comments => p_Comments,
1019 X_Attribute_Category => null,
1020 X_Attribute1 => null,
1021 X_Attribute2 => null,
1022 X_Attribute3 => null,
1023 X_Attribute4 => null,
1024 X_Attribute5 => null,
1025 X_Attribute6 => null,
1026 X_Attribute7 => null,
1027 X_Attribute8 => null,
1028 X_Attribute9 => null,
1029 X_Attribute10 => null,
1030 X_Attribute11 => null,
1031 X_Attribute12 => null,
1032 X_Attribute13 => null,
1033 X_Attribute14 => null,
1034 X_Attribute15 => null,
1035 X_Attribute16 => null,
1036 X_Attribute17 => null,
1037 X_Attribute18 => null,
1038 X_Attribute19 => null,
1039 X_Attribute20 => null);
1040 --
1041 ELSE
1042 --
1043
1044 open check_for_row_exsistance(p_personId,pkg_event_id);
1045 fetch check_for_row_exsistance into l_count;
1046 close check_for_row_exsistance;
1047
1048 if l_count = 0 then
1049
1050 PER_BOOKINGS_PKG.Insert_Row(X_Rowid => l_Row_Id,
1051 X_Booking_Id => l_Booking_Id ,
1052 X_Business_Group_Id => p_Business_Group_Id,
1053 X_Person_Id => p_personId,
1054 X_Event_Id => pkg_event_id,
1055 X_Message => null,
1056 X_Token => null,
1057 X_Comments => p_Comments,
1058 X_Attribute_Category => null,
1059 X_Attribute1 => null,
1060 X_Attribute2 => null,
1061 X_Attribute3 => null,
1062 X_Attribute4 => null,
1063 X_Attribute5 => null,
1064 X_Attribute6 => null,
1065 X_Attribute7 => null,
1066 X_Attribute8 => null,
1067 X_Attribute9 => null,
1068 X_Attribute10 => null,
1069 X_Attribute11 => null,
1070 X_Attribute12 => null,
1071 X_Attribute13 => null,
1072 X_Attribute14 => null,
1073 X_Attribute15 => null,
1074 X_Attribute16 => null,
1075 X_Attribute17 => null,
1076 X_Attribute18 => null,
1077 X_Attribute19 => null,
1078 X_Attribute20 => null);
1079
1080 END IF; -- for l_count
1081
1082 END IF;
1083
1084 -- ns 5/19/2005: BUG 4381336: commenting commit as it is called while
1085 -- resurrecting the transaction (via update action link), it is then
1086 -- attempted to rollback which would fail if committed here.
1087 -- commit;
1088
1089 hr_utility.set_location('Leaving: PQH_PROCESS_EMP_REVIEW.process_emp_reviewers_api',10);
1090 EXCEPTION
1091 WHEN hr_utility.hr_error THEN
1092 ROLLBACK TO process_emp_reviewers_details;
1093 RAISE;
1094 WHEN OTHERS THEN
1095 ROLLBACK TO process_emp_reviewers_details;
1096 RAISE; -- Raise error here relevant to the new tech stack.
1097 End process_emp_reviewers_api;
1098 --
1099 --
1100 END;