[Home] [Help]
PACKAGE BODY: APPS.PQH_PROCESS_EMP_REVIEW
Source
1 PACKAGE BODY pqh_process_emp_review AS
2 /* $Header: pqrewpkg.pkb 120.3 2011/02/11 09:53:19 apjaiswa ship $ */
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;
380 x_person_id := null;
381 RAISE; -- Raise error here relevant to the new tech stack.
382 END get_emp_reviewers_details;
383
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
510 hr_utility.set_location('Entering: Business Group Id:'||x_Business_Group_Id,5);
511
512 hr_transaction_ss.save_transaction_step
513 (p_item_type => x_item_type
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;
636
637 EXCEPTION
638 WHEN hr_utility.hr_error THEN
639 hr_utility.raise_error;
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 /* Commented and added as a part of Bug#10239077 Starts
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 if p_date_start is not null then
677 WF_NOTIFICATION.setAttrText(p_ntf_id, 'PQH_START_DATE',
678 FND_DATE.date_to_displaydate(fnd_date.chardt_to_date(p_Date_Start), calendar_aware => FND_DATE.calendar_aware_alt));
679 end if;
680
681 if p_date_end is not null then
682 WF_NOTIFICATION.setAttrText(p_ntf_id, 'PQH_END_DATE',
683 FND_DATE.date_to_displaydate(fnd_date.chardt_to_date(p_Date_End), calendar_aware => FND_DATE.calendar_aware_alt));
684 end if;
685 /* Commented and added as a part of Bug#10239077 Ends */
686
687 if p_Time_Start is not null then
688 WF_NOTIFICATION.setAttrText(p_ntf_id, 'PQH_START_TIME', p_Time_Start);
689 end if;
690
691 if p_Time_End is not null then
692 WF_NOTIFICATION.setAttrText(p_ntf_id, 'PQH_END_TIME', p_Time_End);
693 end if;
694
695 if p_location is not null then
696 WF_NOTIFICATION.setAttrText(p_ntf_id, 'PQH_LOCATION', p_location);
697 end if;
698
699 if p_Comments is not null then
700 WF_NOTIFICATION.setAttrText(p_ntf_id, 'PQH_COMMENTS', p_Comments);
701 end if;
702
703 WF_NOTIFICATION.setAttrText(p_ntf_id,'#FROM_ROLE',fnd_global.user_name);
704
705 --Bug 3014549: Adding Commit as it is needed to display attribute values, just set, for the notification
706 COMMIT;
707
708 END set_notification_attributes;
709
710
711 --
712 --
713 --
714
715 PROCEDURE process_api (
716 p_validate IN BOOLEAN DEFAULT FALSE,
717 p_transaction_step_id IN NUMBER ) IS
718
719 l_Event_id NUMBER;
720 l_booking_id NUMBER;
721 l_Row_id VARCHAR2(100);
722 --
723 --
724 l_Location_Id NUMBER;
725 l_Assignment_Id NUMBER;
726 l_Date_Start VARCHAR2(12);
727 l_status VARCHAR2(10);
728 l_Type VARCHAR2(200);
729 l_Comments VARCHAR2(2000);
730 l_Date_End VARCHAR2(12);
731 l_Time_End VARCHAR2(200);
732 l_Time_Start VARCHAR2(200);
733 l_Business_Group_Id NUMBER;
734 l_notify_flag VARCHAR2(10);
735 l_userName VARCHAR2(100);
736 l_id Number;
737 l_person_Id Number;
738 l_employee_name VARCHAR2(240);
739 l_employee_no VARCHAR2(100);
740 l_ename VARCHAR2(240);
741
742 cursor getrowid(c_event_id in number) is
743 select rowid from per_events
744 where event_id = c_event_id;
745
746 --
747 cursor c_empName is
748 select wf_engine.GetItemAttrText(item_type, item_key, 'CURRENT_PERSON_DISPLAY_NAME')
749 from hr_api_transaction_steps
750 where transaction_step_id = p_transaction_step_id;
751
752 reviewer_csr REF_CURSOR;
753 empReviewCsr REF_CURSOR;
754
755 l_temp number;
756 l_location_code varchar2(80);
757 l_typeMeaning varchar2(240);
758
759 --
760 -- SSHR Attachment feature changes : 8975847
761 --
762 l_attach_status varchar2(80);
763
764 BEGIN
765
766 hr_utility.set_location('Entering: PQH_PROCESS_EMP_REVIEW.process_api',5);
767 --
768 savepoint process_emp_review_details;
769 --
770 empReviewCsr := get_employee_review(p_transaction_step_id);
771 fetch empReviewCsr into l_event_id, l_type, l_typeMeaning, l_date_start, l_date_end, l_time_start,
772 l_time_end, l_location_id, l_location_code, l_comments, l_Assignment_Id,
773 l_notify_flag, l_business_group_id;
774 close empReviewCsr;
775 --
776 l_person_id := hr_transaction_api.get_varchar2_value (
777 p_transaction_step_id => p_transaction_step_id,
778 p_name => 'P_PERSON_ID');
779
780 wf_directory.GetUserName
781 (p_orig_system => 'PER'
782 ,p_orig_system_id => l_person_id
783 ,p_name => l_username
784 ,p_display_name => l_employee_name);
785 --
786 -- Bug 3881664: Pick the employee name from WF Attribute current_person_display_name.
787 Open c_empName ;
788 Fetch c_empName into l_employee_name;
789 Close c_empName;
790
791 -- Send notification to the person if notify flag is turned on
792 if l_userName is not null then
793 if ( l_notify_flag = 'Y') then
794 l_id := WF_NOTIFICATION.send (l_userName,'HRSSA','PQH_EMP_REV_MSG',NULL,NULL,NULL,NULL,NULl);
795 --
796 set_notification_attributes (
797 p_ntf_id => l_id,
798 p_employee_name => l_employee_name,
799 p_review_type => l_typeMeaning,
800 p_date_start => l_date_start,
801 p_date_end => l_date_end,
802 p_time_start => l_time_start,
803 p_time_end => l_time_end,
804 p_location => l_location_code,
805 p_comments => l_comments);
806
807 End if; -- if notify flag is Y
808 --
809 End if; -- End if user name is not null
810
811 IF l_Event_id IS NOT NULL or l_Event_id <> '0' THEN
812 --
813 open getrowid(l_event_id);
814 fetch getrowid into l_Row_id;
815 close getrowid;
816
817 PER_EVENTS_PKG.Update_row(X_Rowid => l_Row_id,
818 X_Event_Id => l_Event_id,
819 X_Business_Group_Id => l_Business_Group_Id,
820 X_Location_Id => l_Location_id,
821 X_Internal_Contact_Person_Id => null,
822 X_Organization_Run_By_Id => null,
823 X_Assignment_Id => l_Assignment_Id,
824 X_Date_Start => fnd_date.chardt_to_date(l_Date_Start),
825 X_Type => l_Type,
826 X_Comments => l_Comments,
827 X_Contact_Telephone_Number => null ,
828 X_Date_End => fnd_date.chardt_to_date(l_Date_End ),
829 X_Emp_Or_Apl => 'E',
830 X_Event_Or_Interview => 'I' ,
831 X_External_Contact => null ,
832 X_Time_End => l_Time_End,
833 X_Time_Start => l_Time_Start,
834 X_Attribute_Category => null,
835 X_Attribute1 => null,
836 X_Attribute2 => null,
837 X_Attribute3 => null,
838 X_Attribute4 => null,
839 X_Attribute5 => null,
840 X_Attribute6 => null,
841 X_Attribute7 => null,
842 X_Attribute8 => null,
843 X_Attribute9 => null,
844 X_Attribute10 => null,
845 X_Attribute11 => null,
846 X_Attribute12 => null,
847 X_Attribute13 => null,
848 X_Attribute14 => null,
849 X_Attribute15 => null,
850 X_Attribute16 => null,
851 X_Attribute17 => null,
852 X_Attribute18 => null,
853 X_Attribute19 => null,
854 X_Attribute20 => null,
855 X_ctl_globals_end_of_time => fnd_date.chardt_to_date(l_Date_Start));
856 --
857 ELSE
858 --
859 PER_EVENTS_PKG.Insert_row(X_Rowid => l_Row_id,
860 X_Event_Id => l_Event_id,
861 X_Business_Group_Id => l_Business_Group_Id,
862 X_Location_Id => l_Location_id,
863 X_Internal_Contact_Person_Id => null,
864 X_Organization_Run_By_Id => null,
865 X_Assignment_Id => l_Assignment_Id,
866 X_Date_Start => fnd_date.chardt_to_date(l_Date_Start),
867 X_Type => l_Type,
868 X_Comments => l_Comments,
869 X_Contact_Telephone_Number => null ,
870 X_Date_End => fnd_date.chardt_to_date(l_Date_End ),
871 X_Emp_Or_Apl => 'E',
872 X_Event_Or_Interview => 'I' ,
873 X_External_Contact => null ,
874 X_Time_End => l_Time_End,
875 X_Time_Start => l_Time_Start,
876 X_Attribute_Category => null,
877 X_Attribute1 => null,
878 X_Attribute2 => null,
879 X_Attribute3 => null,
880 X_Attribute4 => null,
881 X_Attribute5 => null,
882 X_Attribute6 => null,
883 X_Attribute7 => null,
884 X_Attribute8 => null,
885 X_Attribute9 => null,
886 X_Attribute10 => null,
887 X_Attribute11 => null,
888 X_Attribute12 => null,
889 X_Attribute13 => null,
890 X_Attribute14 => null,
891 X_Attribute15 => null,
892 X_Attribute16 => null,
893 X_Attribute17 => null,
894 X_Attribute18 => null,
895 X_Attribute19 => null,
896 X_Attribute20 => null,
897 X_ctl_globals_end_of_time => fnd_date.chardt_to_date(l_Date_Start));
898 --
899 END IF;
900
901 pkg_event_id := l_Event_id;
902 reviewer_csr := get_reviewers ( p_transaction_step_id,'Y' );
903
904 while (true)
905 loop
906 fetch reviewer_csr into l_event_id, l_booking_id, l_ename, l_employee_no, l_person_id, l_temp,
907 l_comments, l_status, l_business_group_id ;
908 exit when reviewer_csr%notfound;
909 --
910 l_status := NVL(l_status,'N');
911 --
912 process_emp_reviewers_api (
913 p_validate => p_validate,
914 p_event_id => l_event_id,
915 p_booking_id => l_booking_id,
916 p_employee_no => l_employee_no,
917 p_comments => l_comments,
918 p_business_group_id => l_business_group_id,
919 p_status => l_status,
920 p_personId => l_person_Id );
921 --
922 wf_directory.GetUserName
923 (p_orig_system => 'PER'
924 ,p_orig_system_id => l_person_Id
925 ,p_name => l_username
926 ,p_display_name => l_ename );
927 --
928 IF ( l_userName IS NOT NULL AND l_status in ('D','N') ) THEN
929 --
930 If l_status = 'D' Then
931 l_id := WF_NOTIFICATION.send (l_userName,'HRSSA','PQH_EMP_REV_REMOVED_MSG',NULL,NULL,NULL,NULL,NULl);
932 elsif l_status = 'N' Then
933 l_id := WF_NOTIFICATION.send (l_userName,'HRSSA','PQH_EMP_REV_ADDED_MSG',NULL,NULL,NULL,NULL,NULl);
934 end if;
935 --
936 set_notification_attributes (
937 p_ntf_id => l_id,
938 p_employee_name => l_employee_name,
939 p_review_type => l_typeMeaning,
940 p_date_start => l_date_start,
941 p_date_end => l_date_end,
942 p_time_start => l_time_start,
943 p_time_end => l_time_end,
944 p_location => l_location_code,
945 p_comments => l_comments);
946 --
947 End If;
948 end loop;
949 --
950 --
951 close reviewer_csr ;
952
953 hr_utility.set_location('merge_attachments Start : l_person_id = ' || l_person_id || ' ' || 'PQH_PROCESS_EMP_REVIEW.PROCESS_API', 7);
954
955 HR_UTIL_MISC_SS.merge_attachments( p_dest_entity_name => 'PER_PEOPLE_F'
956 ,p_dest_pk1_value => l_person_id
957 ,p_return_status => l_attach_status);
958
959 hr_utility.set_location('merge_attachments End: l_attach_status = ' || l_attach_status || ' ' || 'PQH_PROCESS_EMP_REVIEW.PROCESS_API',9);
960
961 hr_utility.set_location('Leaving: PQH_PROCESS_EMP_REVIEW.process_emp_review_api',10);
962 EXCEPTION
963 WHEN hr_utility.hr_error THEN
964 ROLLBACK TO process_emp_review_details;
965 RAISE;
966 WHEN OTHERS THEN
967 ROLLBACK TO process_emp_review_details;
968 RAISE; -- Raise error here relevant to the new tech stack.
969 END process_api;
970
971 --
972 --
973 PROCEDURE process_emp_reviewers_api (
974 p_validate IN BOOLEAN DEFAULT FALSE,
975 p_Event_Id IN NUMBER,
976 p_Booking_Id IN NUMBER,
977 p_Employee_no IN VARCHAR2 ,
978 p_Comments IN VARCHAR2 ,
979 p_Business_Group_Id IN NUMBER,
980 p_status IN VARCHAR2,
981 p_personId IN NUMBER ) IS
982
983 l_Row_id VARCHAR2(100);
984 l_id number;
985 l_userName VARCHAR2(100);
986 l_MsgType VARCHAR2(100);
987 l_rowId VARCHAR2(100);
988 l_count Number;
989 l_booking_id Number;
990
991 Cursor getBookingRowid(p_Booking_id In Number) is
992 select rowid
993 from per_bookings
994 where Booking_id = p_Booking_id;
995
996 Cursor getBookingRowidForBooking(p_personId In Number,p_event_id In Number) is
997 Select rowid
998 From per_bookings
999 Where person_id =p_personId and event_id = p_event_id;
1000
1001 Cursor check_for_row_exsistance(X_Person_Id in Number,X_Event_Id in Number) is
1002 Select count(*)
1003 from per_bookings
1004 where person_id = x_Person_Id and event_Id = x_Event_Id;
1005
1006 Begin
1007 --
1008 hr_utility.set_location('Entering: PQH_PROCESS_EMP_REVIEW.process_emp_review_api',5);
1009 --
1010 savepoint process_emp_reviewers_details;
1011 --
1012 hr_utility.set_location('p_personId '||p_personId||'Delete Place'||p_status,10);
1013 --
1014 If p_status = 'D' Then
1015 --
1016 open getBookingRowid(p_Booking_Id);
1017 fetch getBookingRowid into l_rowId;
1018 close getBookingRowid;
1019 --
1020 hr_utility.set_location('Row Id in Delete '||l_rowId,10);
1021 per_bookings_pkg.Delete_Row(X_Rowid => l_rowId);
1022 hr_utility.set_location(' After Row Id in Delete '||l_rowId,10);
1023 --
1024 end if;
1025 --
1026 IF p_Booking_id IS NOT NULL or p_Booking_id <> '0' THEN
1027 --
1028 open getBookingRowidForBooking(p_personId,p_event_id);
1029 fetch getBookingRowidForBooking into l_rowId;
1030 close getBookingRowidForBooking;
1031 --
1032 hr_utility.set_location('Row Id '||l_rowId,10);
1033 hr_utility.set_location('Booking Id '||p_Booking_Id||'business Group Id '||p_Business_Group_Id||'event Id '||p_event_id,10);
1034 --
1035 PER_BOOKINGS_PKG.Update_Row(X_Rowid => l_rowId,
1036 X_Booking_Id => p_Booking_Id ,
1037 X_Business_Group_Id => p_Business_Group_Id,
1038 X_Person_Id => p_personId,
1039 X_Event_Id => p_event_id,
1040 X_Message => null,
1041 X_Token => null,
1042 X_Comments => p_Comments,
1043 X_Attribute_Category => null,
1044 X_Attribute1 => null,
1045 X_Attribute2 => null,
1046 X_Attribute3 => null,
1047 X_Attribute4 => null,
1048 X_Attribute5 => null,
1049 X_Attribute6 => null,
1050 X_Attribute7 => null,
1051 X_Attribute8 => null,
1052 X_Attribute9 => null,
1053 X_Attribute10 => null,
1054 X_Attribute11 => null,
1055 X_Attribute12 => null,
1056 X_Attribute13 => null,
1057 X_Attribute14 => null,
1058 X_Attribute15 => null,
1059 X_Attribute16 => null,
1060 X_Attribute17 => null,
1061 X_Attribute18 => null,
1062 X_Attribute19 => null,
1063 X_Attribute20 => null);
1064 --
1065 ELSE
1066 --
1067
1068 open check_for_row_exsistance(p_personId,pkg_event_id);
1069 fetch check_for_row_exsistance into l_count;
1070 close check_for_row_exsistance;
1071
1072 if l_count = 0 then
1073
1074 PER_BOOKINGS_PKG.Insert_Row(X_Rowid => l_Row_Id,
1075 X_Booking_Id => l_Booking_Id ,
1076 X_Business_Group_Id => p_Business_Group_Id,
1077 X_Person_Id => p_personId,
1078 X_Event_Id => pkg_event_id,
1079 X_Message => null,
1080 X_Token => null,
1081 X_Comments => p_Comments,
1082 X_Attribute_Category => null,
1083 X_Attribute1 => null,
1084 X_Attribute2 => null,
1085 X_Attribute3 => null,
1086 X_Attribute4 => null,
1087 X_Attribute5 => null,
1088 X_Attribute6 => null,
1089 X_Attribute7 => null,
1090 X_Attribute8 => null,
1091 X_Attribute9 => null,
1092 X_Attribute10 => null,
1093 X_Attribute11 => null,
1094 X_Attribute12 => null,
1095 X_Attribute13 => null,
1096 X_Attribute14 => null,
1097 X_Attribute15 => null,
1098 X_Attribute16 => null,
1099 X_Attribute17 => null,
1100 X_Attribute18 => null,
1101 X_Attribute19 => null,
1102 X_Attribute20 => null);
1103
1104 END IF; -- for l_count
1105
1106 END IF;
1107
1108 -- ns 5/19/2005: BUG 4381336: commenting commit as it is called while
1109 -- resurrecting the transaction (via update action link), it is then
1110 -- attempted to rollback which would fail if committed here.
1111 -- commit;
1112
1113 hr_utility.set_location('Leaving: PQH_PROCESS_EMP_REVIEW.process_emp_reviewers_api',10);
1114 EXCEPTION
1115 WHEN hr_utility.hr_error THEN
1116 ROLLBACK TO process_emp_reviewers_details;
1117 RAISE;
1118 WHEN OTHERS THEN
1119 ROLLBACK TO process_emp_reviewers_details;
1120 RAISE; -- Raise error here relevant to the new tech stack.
1121 End process_emp_reviewers_api;
1122 --
1123 --
1124 END;