[Home] [Help]
PACKAGE BODY: APPS.PA_ASSIGNMENT_APPROVAL_PVT
Source
1 PACKAGE BODY PA_ASSIGNMENT_APPROVAL_PVT AS
2 /*$Header: PARAAPVB.pls 120.1.12000000.2 2007/11/23 12:39:09 kjai ship $*/
3
4 --
5 --Organize the approvers in the table in sequential order.
6 --Validate that no duplicate order exists between the approvers.
7 --The pl/sql table should have at least one record.
8 --
9 P_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N'); /* Added Debug Profile Option variable initialization for bug#2674619 */
10
11 PROCEDURE Validate_approver_orders (
12 x_return_status OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
13 IS
14
15 l_approver_rec PA_ASSIGNMENT_APPROVAL_PUB.Asgmt_Approvers_Rec_Type;
16 l_return_status VARCHAR2(1);
17 l_sorted BOOLEAN;
18 l_first BINARY_INTEGER;
19 l_before_last BINARY_INTEGER;
20 BEGIN
21 -- Initialize the Error Stack
22 PA_DEBUG.set_err_stack('PA_ASSIGNMENT_APPROVAL_PVT.Validate_approver_orders');
23
24 --Log Message
25 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
26 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENT_APPROVAL_PVT.Validate_approver_orders.begin'
27 ,x_msg => 'Beginning of Validate_approver_orders'
28 ,x_log_level => 5);
29 END IF;
30 -- Initialize
31 l_return_status := FND_API.G_RET_STS_SUCCESS;
32
33 --
34 --If table empty, then return error that at least one approver is needed.
35 --Else use bubble sort to put approvers according to their order, then check if any duplicate order exists.
36 --
37 IF PA_ASSIGNMENT_APPROVAL_PUB.g_approver_tbl.FIRST IS NULL AND PA_ASSIGNMENT_APPROVAL_PUB.g_approver_tbl.LAST IS NULL THEN
38 PA_UTILS.Add_Message( p_app_short_name => 'PA'
39 ,p_msg_name => 'PA_NO_NON_EXCLUDED_APR');
40 l_return_status := FND_API.G_RET_STS_ERROR;
41 ELSE
42
43 --Log Message
44 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
45 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENT_APPROVAL_PVT.Validate_approver_orders.bubble_sort'
46 ,x_msg => 'Starting bubble sort.'
47 ,x_log_level => 5);
48 END IF;
49
50 --
51 --Starting Bubble Sort.
52 --
53 l_sorted := FALSE;
54 IF (PA_ASSIGNMENT_APPROVAL_PUB.g_approver_tbl.FIRST <>PA_ASSIGNMENT_APPROVAL_PUB.g_approver_tbl.LAST) THEN
55 l_first := PA_ASSIGNMENT_APPROVAL_PUB.g_approver_tbl.FIRST;
56 l_before_last := PA_ASSIGNMENT_APPROVAL_PUB.g_approver_tbl.PRIOR(PA_ASSIGNMENT_APPROVAL_PUB.g_approver_tbl.LAST);
57 WHILE l_sorted = FALSE LOOP
58
59 l_sorted := TRUE;
60 FOR i IN l_first .. l_before_last LOOP
61
62 IF (PA_ASSIGNMENT_APPROVAL_PUB.g_approver_tbl(i).orders >
63 PA_ASSIGNMENT_APPROVAL_PUB.g_approver_tbl(PA_ASSIGNMENT_APPROVAL_PUB.g_approver_tbl.NEXT(i)).orders) THEN
64 l_approver_rec := PA_ASSIGNMENT_APPROVAL_PUB.g_approver_tbl(i);
65 PA_ASSIGNMENT_APPROVAL_PUB.g_approver_tbl(i) :=
66 PA_ASSIGNMENT_APPROVAL_PUB.g_approver_tbl(PA_ASSIGNMENT_APPROVAL_PUB.g_approver_tbl.NEXT(i));
67 PA_ASSIGNMENT_APPROVAL_PUB.g_approver_tbl(PA_ASSIGNMENT_APPROVAL_PUB.g_approver_tbl.NEXT(i)) := l_approver_rec;
68 l_sorted := FALSE;
69 END IF;
70 END LOOP;
71 END LOOP;
72
73 --Log Message
74 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
75 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENT_APPROVAL_PVT.Validate_approver_orders.check_duplicate'
76 ,x_msg => 'Checking duplicate orders.'
77 ,x_log_level => 5);
78 END IF;
79 --Check if any duplicate orders exist.
80 FOR i IN l_first .. l_before_last LOOP
81
82 IF (PA_ASSIGNMENT_APPROVAL_PUB.g_approver_tbl(i).orders =
83 PA_ASSIGNMENT_APPROVAL_PUB.g_approver_tbl(PA_ASSIGNMENT_APPROVAL_PUB.g_approver_tbl.NEXT(i)).orders) THEN
84 PA_UTILS.Add_Message( p_app_short_name => 'PA'
85 ,p_msg_name => 'PA_DUPLICATE_APR_ORDERS');
86 l_return_status := FND_API.G_RET_STS_ERROR;
87 END IF;
88 END LOOP; -- end of check duplicate order
89 END IF;-- end of check only one item in the table
90 END IF; --End of check table.
91
92
93 PA_DEBUG.Reset_err_stack; /* 3148857 */
94 --Assign out parameters
95 x_return_status := l_return_status;
96
97
98 EXCEPTION
99 WHEN OTHERS THEN
100 -- Set the excetption Message and the stack
101 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_ASSIGNMENT_APPROVAL_PVT.Validate_approver_orders'
102 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
103 --
104 RAISE; -- This is optional depending on the needs
105 END Validate_approver_orders;
106
107
108
109
110 --
111 --Determine if the specified assignment is a new assignment.
112 --A new assignment is one that has not been previously approved.
113 --
114 FUNCTION Is_New_Assignment
115 (
116 p_assignment_id IN pa_project_assignments.assignment_id%TYPE
117 )
118 RETURN VARCHAR2
119 IS
120
121 l_new_assignment_flag VARCHAR2(1);
122 l_flag VARCHAR2(1);
123
124 /* Commenting this cursor for bug 4183614
125
126 CURSOR get_assignment_id IS
127 SELECT 'X'
128 FROM pa_assignments_history pah,
129 pa_project_assignments ppa
130 WHERE pah.assignment_id = p_assignment_id
131 OR ( ppa.assignment_id = p_assignment_id
132 AND ppa.apprvl_status_code = PA_ASSIGNMENT_APPROVAL_PUB.g_approved);
133
134 End of comment for bug 4183614 */
135
136 /* Added this tuned query for cursor get_Assignment_id for bug 4183614 */
137
138 CURSOR get_assignment_id IS
139 SELECT 'X'
140 FROM pa_assignments_history pah
141 WHERE pah.assignment_id = p_assignment_id
142 UNION ALL
143 SELECT 'X'
144 FROM pa_project_assignments ppa
145 WHERE ppa.assignment_id = p_assignment_id
146 AND ppa.apprvl_status_code = PA_ASSIGNMENT_APPROVAL_PUB.g_approved;
147
148 BEGIN
149 -- Initialize the Error Stack
150 PA_DEBUG.set_err_stack('PA_ASSIGNMENT_APPROVAL_PVT.Is_New_Assignment');
151
152 --Log Message
153 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
154 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENT_APPROVAL_PVT.Is_New_Assignment.begin'
155 ,x_msg => 'Beginning of Is_New_Assignment'
156 ,x_log_level => 5);
157 END IF;
158
159 l_new_assignment_flag := 'N';
160
161 OPEN get_assignment_id;
162
163 FETCH get_assignment_id INTO l_flag;
164
165 IF get_assignment_id%NOTFOUND THEN
166 l_new_assignment_flag := 'Y';
167 END IF;
168
169 CLOSE get_assignment_id;
170
171 PA_DEBUG.Reset_err_stack; /* 3148857 */
172
173 RETURN l_new_assignment_flag;
174
175 EXCEPTION
176 WHEN OTHERS THEN
177 -- Set the excetption Message and the stack
178 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_ASSIGNMENT_APPROVAL_PVT.Is_New_Assignment'
179 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
180 --
181 RAISE; -- This is optional depending on the needs
182
183 END Is_New_Assignment;
184
185
186
187
188 --
189 --Get the change_id from the pa_assignments_history table.
190 --
191 FUNCTION Get_Change_Id
192 (
193 p_assignment_id IN pa_project_assignments.assignment_id%TYPE
194 )
195 RETURN NUMBER
196 IS
197
198 l_change_id pa_assignments_history.change_id%TYPE;
199
200 CURSOR get_change_id IS
201 SELECT change_id
202 FROM pa_assignments_history
203 WHERE assignment_id = p_assignment_id
204 AND last_approved_flag = 'Y';
205
206 BEGIN
207
208
209 -- Initialize the Error Stack
210 PA_DEBUG.set_err_stack('PA_ASSIGNMENT_APPROVAL_PVT.Get_Change_Id');
211
212 --Log Message
213 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
214 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENT_APPROVAL_PVT.Get_Change_Id.begin'
215 ,x_msg => 'Beginning of Get_Change_Id'
216 ,x_log_level => 5);
217 END IF;
218 OPEN get_change_id;
219
220 FETCH get_change_id INTO l_change_id;
221
222 IF get_change_id%NOTFOUND THEN
223 l_change_id := -1;
224 END IF;
225
226 CLOSE get_change_id;
227 PA_DEBUG.Reset_Err_Stack; /* 3148857 */
228 RETURN l_change_id;
229
230 EXCEPTION
231 WHEN OTHERS THEN
232 -- Set the excetption Message and the stack
233 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_ASSIGNMENT_APPROVAL_PVT.Get_Change_Id'
234 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
235 --
236 RAISE; -- This is optional depending on the needs
237
238
239 END Get_Change_Id;
240
241
242
243 --
244 --Determine if the specified assignment requires approval.
245 --
246 PROCEDURE Check_Approval_Required
247 (
248 p_assignment_id IN pa_project_assignments.assignment_id%TYPE
249 ,p_new_assignment_flag IN VARCHAR2 := FND_API.G_MISS_CHAR
250 -- ,p_resource_authority_flag IN VARCHAR2 := FND_API.G_MISS_CHAR
251 ,x_approval_required_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
252 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
253 ) IS
254
255 l_approval_required_flag VARCHAR2(1);
256
257 BEGIN
258 -- Initialize the Error Stack
259 PA_DEBUG.set_err_stack('PA_ASSIGNMENT_APPROVAL_PVT.Check_Approval_Required');
260
261 --Log Message
262 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
263 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENT_APPROVAL_PVT.Check_Approval_Required.begin'
264 ,x_msg => 'Beginning of Check_Approval_Required'
265 ,x_log_level => 5);
266 END IF;
267
268
269 -- Initialize
270 x_return_status := FND_API.G_RET_STS_SUCCESS;
271 l_approval_required_flag := 'N';
272
273 -- If the specified assignment has not been previously approved and the submitter has no resource authority
274 -- Then approval required.
275 -- Otherwise, check if approval required assignment items and schedule has any changes.
276 -- If change occured, then approval required.
277 --
278 IF p_new_assignment_flag = 'Y' THEN
279
280 l_approval_required_flag := 'Y';
281
282 ELSE
283 --Log Message
284 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
285 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENT_APPROVAL_PVT.Check_Approval_Required.items_changed'
286 ,x_msg => 'Checking if Assignment Approval Items are changed.'
287 ,x_log_level => 5);
288 END IF;
289
290 l_approval_required_flag := PA_CLIENT_EXTN_ASGMT_APPRVL.Is_Asgmt_Appr_Items_Changed(p_assignment_id);
291 IF l_approval_required_flag NOT IN ('Y', 'N') THEN
292 x_return_status := FND_API.G_RET_STS_ERROR;
293 END IF;
294
295 END IF; -- end of checking new assignment flag
296
297 pa_debug.reset_err_stack; /* 3148857 */
298
299 x_approval_required_flag := l_approval_required_flag;
300
301 EXCEPTION
302 WHEN OTHERS THEN
303 -- Set the excetption Message and the stack
304 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_ASSIGNMENT_APPROVAL_PVT.Check_Approval_Required'
305 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
306 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
307 --
308 RAISE; -- This is optional depending on the needs
309
310 END Check_Approval_Required;
311
312
313
314
315
316 --
317 --Update the Assignment Approval Status depending on the action performed, and calls schedule API to
318 --update schedule statuses if needed. Any time the Assignment Approval Status need to be updated, this API will
319 --be called. This including when workflow is successful or failure.
320 --
321 --The allowed actions are: 'APPROVE', 'REJECT', 'SUBMIT', 'UPDATE', and 'REVERT'.
322 --
323 PROCEDURE Update_Approval_Status
324 (
325 p_assignment_id IN pa_project_assignments.assignment_id%TYPE
326 ,p_action_code IN VARCHAR2 := FND_API.G_MISS_CHAR
327 ,p_record_version_number IN pa_project_assignments.record_version_number%TYPE
328 ,p_note_to_approver IN VARCHAR2 := FND_API.G_MISS_CHAR
329 ,x_apprvl_status_code OUT NOCOPY pa_project_statuses.project_status_code%TYPE --File.Sql.39 bug 4440895
330 ,x_change_id OUT NOCOPY pa_assignments_history.change_id%TYPE --File.Sql.39 bug 4440895
331 ,x_record_version_number OUT NOCOPY pa_project_assignments.record_version_number%TYPE --File.Sql.39 bug 4440895
332 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
333 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
334 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
335
336 ) IS
337
338 l_record_version_number NUMBER;
339 l_apprvl_status_code pa_project_statuses.project_status_code%TYPE;
340 l_return_status VARCHAR2(1);
341 l_msg_data VARCHAR2(2000);
342 l_msg_count NUMBER;
343 l_error_count NUMBER;
344 l_msg_index_out NUMBER;
345
346 CURSOR get_status_and_rec_num IS
347 SELECT apprvl_status_code, record_version_number
348 FROM pa_project_assignments
349 WHERE assignment_id = p_assignment_id;
350
351 BEGIN
352
353 -- Initialize the Error Stack
354 PA_DEBUG.set_err_stack('PA_ASSIGNMENT_APPROVAL_PVT.Update_Approval_Status');
355
356 --Log Message
357 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
358 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENT_APPROVAL_PVT.Update_Approval_Status.begin'
359 ,x_msg => 'Beginning of Update_Approval_Status.'
360 ,x_log_level => 5);
361 END IF;
362
363 x_return_status := FND_API.G_RET_STS_SUCCESS;
364 x_change_id := NULL;
365 x_record_version_number := NULL;
366
367 --Initialize local variables
368 l_record_version_number := p_record_version_number;
369 l_error_count := FND_MSG_PUB.Count_Msg;
370
371 -- Get the current status code and record version number
372 OPEN get_status_and_rec_num;
373 FETCH get_status_and_rec_num INTO l_apprvl_status_code, l_record_version_number;
374 CLOSE get_status_and_rec_num;
375
376
377
378 -- IF current status is 'Approved' and action to be performed is 'Update', then insert the current record
379 -- into history table.
380 IF l_apprvl_status_code = PA_ASSIGNMENT_APPROVAL_PUB.g_approved
381 AND p_action_code =PA_ASSIGNMENT_APPROVAL_PUB.g_update_action THEN
382
383 --Log Message
384 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
385 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENT_APPROVAL_PVT.Update_Approval_Status.insert_history'
386 ,x_msg => 'Inserting record into assignment history table.'
387 ,x_log_level => 5);
388 END IF;
389
390 PA_ASSIGNMENT_APPROVAL_PVT.Insert_Into_Assignment_History ( p_assignment_id => p_assignment_id
391 ,x_change_id => x_change_id
392 ,x_return_status => l_return_status);
393 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
394 x_return_status := l_return_status;
398 /* Changed bug 1635170*/
395 END IF;
396 l_return_status := FND_API.G_MISS_CHAR;
397
399 ELSE
400 x_change_id:=PA_ASSIGNMENT_APPROVAL_PVT.get_change_id(p_assignment_id);
401 END IF;
402 /* End bug 1635170*/
403
404 --Log Message
405 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
406 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENT_APPROVAL_PVT.Update_Approval_Status.get_next_stus'
407 ,x_msg => 'Get next assignment approval status.'
408 ,x_log_level => 5);
409 END IF;
410
411 -- Get the status code after action performed.
412 PA_ASSIGNMENT_APPROVAL_PVT.Get_Next_Status_After_Action ( p_action_code => p_action_code
413 ,p_status_code => l_apprvl_status_code
414 ,x_status_code => x_apprvl_status_code
415 ,x_return_status => l_return_status);
416 --dbms_output.put_line('next status:'|| x_apprvl_status_code);
417
418 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
419 x_return_status := l_return_status;
420 END IF;
421 l_return_status := FND_API.G_MISS_CHAR;
422
423 /* --moved after schedule success or failure
424
425 --If no error, update the current assignment record with the new status and increment record_version_number
426 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
427 --dbms_output.put_line('calling update row');
428
429 PA_PROJECT_ASSIGNMENTS_PKG.Update_Row ( p_assignment_id => p_assignment_id
430 ,p_record_version_number => p_record_version_number
431 ,p_apprvl_status_code => x_apprvl_status_code
432 ,p_note_to_approver => p_note_to_approver
433 ,x_return_status => l_return_status );
434 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
435 x_return_status := l_return_status;
436 ELSIF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
437 x_record_version_number := p_record_version_number +1;
438 END IF;
439 l_return_status := FND_API.G_MISS_CHAR;
440 END IF;
441
442 */
443
444
445 --In the case of Approve or Reject, also call schedule's success/failure method to update schedule statuses.
446 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
447
448 IF p_action_code = PA_ASSIGNMENT_APPROVAL_PUB.g_approve_action THEN
449 --call schedule's success method
450 --Log Message
451 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
452 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENT_APPROVAL_PVT.Update_Approval_Status.schedule_success'
453 ,x_msg => 'Calling schedule success method.'
454 ,x_log_level => 5);
455 END IF;
456
457 PA_SCHEDULE_PVT.UPDATE_SCH_WF_SUCCESS ( p_assignment_id => p_assignment_id
458 ,p_record_version_number => l_record_version_number
459 ,x_return_status => l_return_status
460 ,x_msg_count => l_msg_count
461 ,x_msg_data => l_msg_data);
462 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
463 x_return_status := l_return_status;
464 END IF;
465 l_record_version_number := NULL;
466 ELSIF p_action_code = PA_ASSIGNMENT_APPROVAL_PUB.g_reject_action THEN
467 --call schedule's failure method
468 --Log Message
469 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
470 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENT_APPROVAL_PVT.Update_Approval_Status.schedule_failure'
471 ,x_msg => 'Calling schedule failure method.'
472 ,x_log_level => 5);
473 END IF;
474
475 PA_SCHEDULE_PVT.UPDATE_SCH_WF_FAILURE ( p_assignment_id => p_assignment_id
476 ,p_record_version_number => l_record_version_number
477 ,x_return_status => l_return_status
478 ,x_msg_count => l_msg_count
479 ,x_msg_data => l_msg_data);
480 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
481 x_return_status := l_return_status;
482 END IF;
483 l_record_version_number := NULL;
484 END IF; -- end of calling success/failure
485 l_return_status := FND_API.G_MISS_CHAR;
486 l_msg_count := FND_API.G_MISS_NUM;
487 l_msg_data := FND_API.G_MISS_CHAR;
488 END IF;
489
490
491
492 --If no error, update the current assignment record with the new status and increment record_version_number
493 --The record_version_number passed in will be NULL if Schedule Sucess/failure API has already updated the record
494 --otherwise, use the p_record_version_number
495
496 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
497
498 --Log Message
499 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
500 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENT_APPROVAL_PVT.Update_Approval_Status.update_status'
501 ,x_msg => 'update current approval status.'
502 ,x_log_level => 5);
503 END IF;
504
505 --Update record in assignment table Only if
506 --p_note_to_approver exists OR
510 (x_apprvl_status_code <> l_apprvl_status_code)) THEN
507 --apprvl_status_code has been changed.
508 IF (p_note_to_approver <> FND_API.G_MISS_CHAR AND p_note_to_approver IS NOT NULL) OR
509 ((x_apprvl_status_code IS NOT NULL AND l_apprvl_status_code IS NULL) OR
511 PA_PROJECT_ASSIGNMENTS_PKG.Update_Row ( p_assignment_id => p_assignment_id
512 ,p_record_version_number => l_record_version_number
513 ,p_apprvl_status_code => x_apprvl_status_code
514 ,p_note_to_approver => p_note_to_approver
515 ,x_return_status => l_return_status );
516 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
517 x_return_status := l_return_status;
518 ELSIF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
519 IF l_record_version_number IS NOT NULL THEN
520 x_record_version_number := l_record_version_number +1;
521 END IF;
522 END IF;
523 l_return_status := FND_API.G_MISS_CHAR;
524 END IF;
525 END IF;
526
527
528 x_msg_count := FND_MSG_PUB.Count_Msg - l_error_count;
529 IF x_msg_count = 1 THEN
530 pa_interface_utils_pub.get_messages ( p_encoded => FND_API.G_TRUE
531 ,p_msg_index => 1
532 ,p_data => x_msg_data
533 ,p_msg_index_out => l_msg_index_out
534 );
535 END IF;
536
537 IF x_msg_count > 0 THEN
538 x_return_status := FND_API.G_RET_STS_ERROR;
539 END IF;
540
541 -- Reset the error stack when returning to the calling program
542
543 PA_DEBUG.Reset_Err_Stack;
544
545 EXCEPTION
546 WHEN OTHERS THEN
547 -- Set the exception Message and the stack
548 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_ASSIGNMENT_APPROVAL_PVT.Update_Approval_Status'
549 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
550 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
551
552 RAISE; -- This is optional depending on the needs
553
554
555 END Update_Approval_Status;
556
557
558
559 --
560 --Get the new assignment approval status after the specified action is performed
561 --
562 --The allowed actions are: 'APPROVE', 'REJECT', 'SUBMIT', 'UPDATE', and 'REVERT'.
563 --After bug 6625421, 'SAVE_AND_SUBMIT' action also allowed.
564 --
565 PROCEDURE Get_Next_Status_After_Action
566 (
567 p_action_code IN VARCHAR2 := FND_API.G_MISS_CHAR
568 ,p_status_code IN pa_project_statuses.project_status_code%TYPE := FND_API.G_MISS_CHAR
569 ,x_status_code OUT NOCOPY pa_project_statuses.project_status_code%TYPE --File.Sql.39 bug 4440895
570 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
571 ) IS
572
573 l_return_status VARCHAR2(1);
574 l_error_message_code VARCHAR2(100);
575 l_success_status_code pa_project_statuses.project_status_code%TYPE;
576 l_failure_status_code pa_project_statuses.project_status_code%TYPE;
577
578 BEGIN
579
580 -- Initialize the Error Stack
581 PA_DEBUG.set_err_stack('PA_ASSIGNMENT_APPROVAL_PVT.Get_Next_Status_After_Action');
582
583 --Log Message
584 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
585 PA_DEBUG.write_log (x_module => 'Get_Next_Status_After_Action.begin'
586 ,x_msg => 'Beginning of Get_Next_Status_After_Action.'
587 ,x_log_level => 5);
588 END IF;
589
590 x_return_status := FND_API.G_RET_STS_SUCCESS;
591
592
593 --Check only valid action is passed in.
594 IF p_action_code NOT IN (PA_ASSIGNMENT_APPROVAL_PUB.g_approve_action, PA_ASSIGNMENT_APPROVAL_PUB.g_reject_action,
595 PA_ASSIGNMENT_APPROVAL_PUB.g_submit_action, PA_ASSIGNMENT_APPROVAL_PUB.g_update_action,
596 PA_ASSIGNMENT_APPROVAL_PUB.g_revert_action, PA_ASSIGNMENT_APPROVAL_PUB.g_cancel_action,
597 PA_MASS_ASGMT_TRX.g_save_and_submit) THEN /*SAVE_AND_SUBMIT added for bug 6625421*/
598 PA_UTILS.Add_Message( p_app_short_name => 'PA'
599 ,p_msg_name => 'PA_UNEXP_APPRVL_ACTION');
600 --dbms_output.put_line('unexpected action code');
601 x_return_status := FND_API.G_RET_STS_ERROR;
602 END IF;
603
604
605
606 --Check only valid assignment approval status is passed in
607 IF p_status_code IS NOT NULL AND p_status_code NOT IN (PA_ASSIGNMENT_APPROVAL_PUB.g_approved,
608 PA_ASSIGNMENT_APPROVAL_PUB.g_rejected,
609 PA_ASSIGNMENT_APPROVAL_PUB.g_submitted,
610 PA_ASSIGNMENT_APPROVAL_PUB.g_working,
611 PA_ASSIGNMENT_APPROVAL_PUB.g_req_resub) THEN
612 PA_UTILS.Add_Message( p_app_short_name => 'PA'
613 ,p_msg_name => 'PA_INVALID_APPRVL_STUS');
614 --dbms_output.put_line('unexpected apprvl status');
615 x_return_status := FND_API.G_RET_STS_ERROR;
616 END IF;
617
618 IF (p_action_code = PA_ASSIGNMENT_APPROVAL_PUB.g_submit_action
619 OR
623
620 p_action_code = PA_MASS_ASGMT_TRX.g_save_and_submit) THEN /*SAVE_AND_SUBMIT added for bug 6625421*/
621
622 x_status_code := PA_ASSIGNMENT_APPROVAL_PUB.g_submitted;
624 ELSIF p_action_code = PA_ASSIGNMENT_APPROVAL_PUB.g_revert_action THEN
625
626 x_status_code := PA_ASSIGNMENT_APPROVAL_PUB.g_approved;
627
628 ELSIF p_action_code = PA_ASSIGNMENT_APPROVAL_PUB.g_cancel_action THEN
629
630 x_status_code := PA_ASSIGNMENT_APPROVAL_PUB.g_canceled;
631
632 ELSIF p_action_code = PA_ASSIGNMENT_APPROVAL_PUB.g_update_action THEN
633
634 IF p_status_code = PA_ASSIGNMENT_APPROVAL_PUB.g_working THEN
635
636 x_status_code := PA_ASSIGNMENT_APPROVAL_PUB.g_working;
637
638 ELSIF p_status_code = PA_ASSIGNMENT_APPROVAL_PUB.g_submitted THEN
639 /*
640 PA_UTILS.Add_Message( p_app_short_name => 'PA'
641 ,p_msg_name => 'PA_WF_APPROVAL_PENDING');
642 x_return_status := FND_API.G_RET_STS_ERROR;
643 */
644 x_status_code := PA_ASSIGNMENT_APPROVAL_PUB.g_submitted;
645
646 ELSIF p_status_code IS NULL THEN
647 x_status_code := NULL;
648 ELSE
649 x_status_code := PA_ASSIGNMENT_APPROVAL_PUB.g_req_resub;
650 END IF;
651
652 ELSE
653
654 --Log Message
655 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
656 PA_DEBUG.write_log (x_module => 'Get_Next_Status_After_Action.get_wf_status_code'
657 ,x_msg => 'Get success and failure status code'
658 ,x_log_level => 5);
659 END IF;
660 --
661 --call get_workflow_info to get success and failure status code
662 --
663 PA_PROJECT_STUS_UTILS.get_wf_success_failure_status
664 (p_status_code => PA_ASSIGNMENT_APPROVAL_PUB.g_submitted
665 ,p_status_type => 'ASGMT_APPRVL'
666 ,x_wf_success_status_code => l_success_status_code
667 ,x_wf_failure_status_code => l_failure_status_code
668 ,x_return_status => l_return_status
669 ,x_error_message_code => l_error_message_code) ;
670
671
672
673 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
674 x_return_status := l_return_status;
675 PA_UTILS.Add_Message( p_app_short_name => 'PA'
676 ,p_msg_name => l_error_message_code);
677 ELSE
678
679 IF p_action_code = PA_ASSIGNMENT_APPROVAL_PUB.g_approve_action THEN
680 x_status_code := l_success_status_code;
681 ELSIF p_action_code = PA_ASSIGNMENT_APPROVAL_PUB.g_reject_action THEN
682 x_status_code := l_failure_status_code;
683 END IF;
684 END IF;
685 END IF; --end of checking p_action_code
686 pa_debug.reset_err_stack; /* 3148857 */
687 EXCEPTION
688 WHEN OTHERS THEN
689 -- Set the exception Message and the stack
690 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_ASSIGNMENT_APPROVAL_PVT.Get_Next_Status_After_Action'
691 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
692 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
693
694 RAISE; -- This is optional depending on the needs
695 END Get_Next_Status_After_Action;
696
697
698 --
699 --This procedure inserts current record in the PA_PROJECT_ASSIGNMENTS into the PA_ASSIGNMENTS_HISTORY table when the
700 -- record's Assignment Approval Status changes from 'APPROVED' to 'WORKING'.
701 --
702 PROCEDURE Insert_Into_Assignment_History
703 (
704 p_assignment_id IN pa_project_assignments.assignment_id%TYPE
705 ,x_change_id OUT NOCOPY pa_assignments_history.change_id%TYPE --File.Sql.39 bug 4440895
706 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
707 ) IS
708
709 l_assignment_rec PA_ASSIGNMENTS_PUB.Assignment_Rec_Type;
710 l_assignment_row_id ROWID;
711 l_change_id NUMBER;
712 l_pending_approval_flag pa_assignments_history.pending_approval_flag%TYPE;
713 l_return_status VARCHAR2(1);
714 l_msg_data VARCHAR2(2000);
715 l_msg_count NUMBER;
716 l_project_subteam_id NUMBER;
717
718 CURSOR get_project_subteam_id IS
719 SELECT project_subteam_id
720 FROM pa_project_subteam_parties
721 WHERE object_type = 'PA_PROJECT_ASSIGNMENTS'
722 AND object_id = p_assignment_id
723 AND primary_subteam_flag = 'Y';
724
725 BEGIN
726 -- Initialize the Error Stack
727 PA_DEBUG.set_err_stack('PA_ASSIGNMENT_APPROVAL_PVT.Insert_Into_Assignment_History');
728
729 --Log Message
730 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
731 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENT_APPROVAL_PVT.Insert_Into_Assignment_History.begin'
732 ,x_msg => 'Beginning of Insert_Into_Assignment_History'
733 ,x_log_level => 5);
734 END IF;
735
736 x_return_status := FND_API.G_RET_STS_SUCCESS;
737 x_change_id := NULL;
738
739 --
740 -- Get the current assignment record details
741 --
742 SELECT
743 assignment_id
744 ,assignment_name
748 ,apprvl_status_code
745 ,assignment_type
746 ,multiple_status_flag
747 ,record_version_number
749 ,status_code
750 ,staffing_priority_code
751 ,staffing_owner_person_id
752 ,project_id
753 ,project_role_id
754 ,resource_id
755 ,project_party_id
756 ,description
757 ,note_to_approver
758 ,start_date
759 ,end_date
760 ,assignment_effort
761 ,extension_possible
762 ,source_assignment_id
763 ,assignment_template_id
764 ,min_resource_job_level
765 ,max_resource_job_level
766 ,assignment_number
767 ,additional_information
768 ,work_type_id
769 ,revenue_currency_code
770 ,revenue_bill_rate
771 ,expense_owner
772 ,expense_limit
773 ,expense_limit_currency_code
774 ,fcst_tp_amount_type
775 ,fcst_job_id
776 ,fcst_job_group_id
777 ,expenditure_org_id
778 ,expenditure_organization_id
779 ,expenditure_type_class
780 ,expenditure_type
781 ,location_id
782 ,calendar_type
783 ,calendar_id
784 ,resource_calendar_percent
785 ,pending_approval_flag
786 ,no_of_active_candidates
787 ,competence_match_weighting
788 ,availability_match_weighting
789 ,job_level_match_weighting
790 ,search_min_availability
791 ,search_country_code
792 ,search_exp_org_struct_ver_id
793 ,search_exp_start_org_id
794 ,search_min_candidate_score
795 ,last_auto_search_date
796 ,enable_auto_cand_nom_flag
797 ,mass_wf_in_progress_flag
798 ,bill_rate_override
799 ,bill_rate_curr_override
800 ,markup_percent_override
801 ,tp_rate_override
802 ,tp_currency_override
803 ,tp_calc_base_code_override
804 ,tp_percent_applied_override
805 ,markup_percent
806 ,attribute_category
807 ,attribute1
808 ,attribute2
809 ,attribute3
810 ,attribute4
811 ,attribute5
812 ,attribute6
813 ,attribute7
814 ,attribute8
815 ,attribute9
816 ,attribute10
817 ,attribute11
818 ,attribute12
819 ,attribute13
820 ,attribute14
821 ,attribute15
822 ,transfer_price_rate -- Added for bug 3051110
823 ,transfer_pr_rate_curr
824 ,discount_percentage -- Added for bug 3041583
825 ,rate_disc_reason_code -- Added for bug 3041583
826 INTO
827 l_assignment_rec.assignment_id
828 ,l_assignment_rec.assignment_name
829 ,l_assignment_rec.assignment_type
830 ,l_assignment_rec.multiple_status_flag
831 ,l_assignment_rec.record_version_number
832 ,l_assignment_rec.apprvl_status_code
833 ,l_assignment_rec.status_code
834 ,l_assignment_rec.staffing_priority_code
835 ,l_assignment_rec.staffing_owner_person_id
836 ,l_assignment_rec.project_id
837 ,l_assignment_rec.project_role_id
838 ,l_assignment_rec.resource_id
839 ,l_assignment_rec.project_party_id
840 ,l_assignment_rec.description
841 ,l_assignment_rec.note_to_approver
842 ,l_assignment_rec.start_date
843 ,l_assignment_rec.end_date
844 ,l_assignment_rec.assignment_effort
845 ,l_assignment_rec.extension_possible
846 ,l_assignment_rec.source_assignment_id
847 ,l_assignment_rec.assignment_template_id
848 ,l_assignment_rec.min_resource_job_level
849 ,l_assignment_rec.max_resource_job_level
850 ,l_assignment_rec.assignment_number
851 ,l_assignment_rec.additional_information
852 ,l_assignment_rec.work_type_id
853 ,l_assignment_rec.revenue_currency_code
854 ,l_assignment_rec.revenue_bill_rate
855 ,l_assignment_rec.expense_owner
856 ,l_assignment_rec.expense_limit
857 ,l_assignment_rec.expense_limit_currency_code
858 ,l_assignment_rec.fcst_tp_amount_type
859 ,l_assignment_rec.fcst_job_id
860 ,l_assignment_rec.fcst_job_group_id
861 ,l_assignment_rec.expenditure_org_id
862 ,l_assignment_rec.expenditure_organization_id
863 ,l_assignment_rec.expenditure_type_class
864 ,l_assignment_rec.expenditure_type
865 ,l_assignment_rec.location_id
866 ,l_assignment_rec.calendar_type
867 ,l_assignment_rec.calendar_id
868 ,l_assignment_rec.resource_calendar_percent
869 ,l_pending_approval_flag
870 ,l_assignment_rec.no_of_active_candidates
871 ,l_assignment_rec.comp_match_weighting
872 ,l_assignment_rec.avail_match_weighting
873 ,l_assignment_rec.job_level_match_weighting
874 ,l_assignment_rec.search_min_availability
875 ,l_assignment_rec.search_country_code
876 ,l_assignment_rec.search_exp_org_struct_ver_id
877 ,l_assignment_rec.search_exp_start_org_id
878 ,l_assignment_rec.search_min_candidate_score
879 ,l_assignment_rec.last_auto_search_date
880 ,l_assignment_rec.enable_auto_cand_nom_flag
881 ,l_assignment_rec.mass_wf_in_progress_flag
885 ,l_assignment_rec.tp_rate_override
882 ,l_assignment_rec.bill_rate_override
883 ,l_assignment_rec.bill_rate_curr_override
884 ,l_assignment_rec.markup_percent_override
886 ,l_assignment_rec.tp_currency_override
887 ,l_assignment_rec.tp_calc_base_code_override
888 ,l_assignment_rec.tp_percent_applied_override
889 ,l_assignment_rec.markup_percent
890 ,l_assignment_rec.attribute_category
891 ,l_assignment_rec.attribute1
892 ,l_assignment_rec.attribute2
893 ,l_assignment_rec.attribute3
894 ,l_assignment_rec.attribute4
895 ,l_assignment_rec.attribute5
896 ,l_assignment_rec.attribute6
897 ,l_assignment_rec.attribute7
898 ,l_assignment_rec.attribute8
899 ,l_assignment_rec.attribute9
900 ,l_assignment_rec.attribute10
901 ,l_assignment_rec.attribute11
902 ,l_assignment_rec.attribute12
903 ,l_assignment_rec.attribute13
904 ,l_assignment_rec.attribute14
905 ,l_assignment_rec.attribute15
906 ,l_assignment_rec.transfer_price_rate -- Added for bug 3051110
907 ,l_assignment_rec.transfer_pr_rate_curr
908 ,l_assignment_rec.discount_percentage -- Added for bug 3041583
909 ,l_assignment_rec.rate_disc_reason_code -- Added for bug 3041583
910 FROM pa_project_assignments
911 WHERE assignment_id = p_assignment_id;
912
913 --
914 --Get the subteam id
915 --
916 OPEN get_project_subteam_id;
917 FETCH get_project_subteam_id INTO l_project_subteam_id;
918 IF get_project_subteam_id%NOTFOUND THEN
919 l_project_subteam_id := NULL;
920 END IF;
921 CLOSE get_project_subteam_id;
922
923 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
924 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENT_APPROVAL_PVT.Insert_Into_Assignment_History.last_approved_flag'
925 ,x_msg => 'update previously last approved record flag to N'
926 ,x_log_level => 5);
927 END IF;
928 --
929 --Only the newly inserted row should have last_approved_flag set to 'Y',
930 --so updating the previously last approved record's flag to 'N'.
931 --
932 --IF no previous approved record exist, this statement does nothing.
933 --
934 -- PA_ASSIGNMENTS_HISTORY_PKG.Update_Row is not used, since the last_approved_flag is used as both
935 -- an criteria for search and the parameter need to be updated.
936 --
937 UPDATE pa_assignments_history
938 SET last_approved_flag = 'N'
939 WHERE assignment_id = p_assignment_id
940 AND last_approved_flag = 'Y';
941
942
943 --If no errors, insert the approved assignment record into the history table.
944 IF (FND_MSG_PUB.Count_Msg = 0) THEN
945 --Log Message
946 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
947 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENT_APPROVAL_PVT.Insert_Into_Assignment_History.insert_row'
948 ,x_msg => 'insert last approved record into assignment history table.'
949 ,x_log_level => 5);
950 END IF;
951
952 PA_ASSIGNMENTS_HISTORY_PKG.Insert_Row
953 (p_assignment_id => l_assignment_rec.assignment_id
954 ,p_assignment_name => l_assignment_rec.assignment_name
955 ,p_assignment_type => l_assignment_rec.assignment_type
956 ,p_multiple_status_flag => l_assignment_rec.multiple_status_flag
957 ,p_record_version_number => l_assignment_rec.record_version_number
958 ,p_apprvl_status_code => l_assignment_rec.apprvl_status_code
959 ,p_status_code => l_assignment_rec.status_code
960 ,p_staffing_priority_code => l_assignment_rec.staffing_priority_code
961 ,p_staffing_owner_person_id => l_assignment_rec.staffing_owner_person_id
962 ,p_project_id => l_assignment_rec.project_id
963 ,p_project_role_id => l_assignment_rec.project_role_id
964 ,p_resource_id => l_assignment_rec.resource_id
965 ,p_project_party_id => l_assignment_rec.project_party_id
966 ,p_project_subteam_id => l_project_subteam_id
967 ,p_description => l_assignment_rec.description
968 ,p_note_to_approver => l_assignment_rec.note_to_approver
969 ,p_start_date => l_assignment_rec.start_date
970 ,p_end_date => l_assignment_rec.end_date
971 ,p_assignment_effort => l_assignment_rec.assignment_effort
972 ,p_extension_possible => l_assignment_rec.extension_possible
973 ,p_source_assignment_id => l_assignment_rec.source_assignment_id
974 ,p_assignment_template_id => l_assignment_rec.assignment_template_id
975 ,p_min_resource_job_level => l_assignment_rec.min_resource_job_level
976 ,p_max_resource_job_level => l_assignment_rec.max_resource_job_level
977 ,p_assignment_number => l_assignment_rec.assignment_number
978 ,p_additional_information => l_assignment_rec.additional_information
979 ,p_work_type_id => l_assignment_rec.work_type_id
980 ,p_revenue_currency_code => l_assignment_rec.revenue_currency_code
981 ,p_revenue_bill_rate => l_assignment_rec.revenue_bill_rate
982 ,p_fcst_tp_amount_type => l_assignment_rec.fcst_tp_amount_type
983 ,p_fcst_job_id => l_assignment_rec.fcst_job_id
987 ,p_expenditure_type_class => l_assignment_rec.expenditure_type_class
984 ,p_fcst_job_group_id => l_assignment_rec.fcst_job_group_id
985 ,p_expenditure_org_id => l_assignment_rec.expenditure_org_id
986 ,p_expenditure_organization_id => l_assignment_rec.expenditure_organization_id
988 ,p_expenditure_type => l_assignment_rec.expenditure_type
989 ,p_expense_owner => l_assignment_rec.expense_owner
990 ,p_expense_limit => l_assignment_rec.expense_limit
991 ,p_expense_limit_currency_code => l_assignment_rec.expense_limit_currency_code
992 ,p_location_id => l_assignment_rec.location_id
993 ,p_calendar_type => l_assignment_rec.calendar_type
994 ,p_calendar_id => l_assignment_rec.calendar_id
995 ,p_resource_calendar_percent => l_assignment_rec.resource_calendar_percent
996 ,p_pending_approval_flag => l_pending_approval_flag
997 ,p_last_approved_flag => 'Y'
998 ,p_no_of_active_candidates => l_assignment_rec.no_of_active_candidates
999 ,p_comp_match_weighting => l_assignment_rec.comp_match_weighting
1000 ,p_avail_match_weighting => l_assignment_rec.avail_match_weighting
1001 ,p_job_level_match_weighting => l_assignment_rec.job_level_match_weighting
1002 ,p_search_min_availability => l_assignment_rec.search_min_availability
1003 ,p_search_country_code => l_assignment_rec.search_country_code
1004 ,p_search_exp_org_struct_ver_id=> l_assignment_rec.search_exp_org_struct_ver_id
1005 ,p_search_exp_start_org_id => l_assignment_rec.search_exp_start_org_id
1006 ,p_search_min_candidate_score => l_assignment_rec.search_min_candidate_score
1007 ,p_last_auto_search_date => l_assignment_rec.last_auto_search_date
1008 ,p_enable_auto_cand_nom_flag => l_assignment_rec.enable_auto_cand_nom_flag
1009 ,p_mass_wf_in_progress_flag => l_assignment_rec.mass_wf_in_progress_flag
1010 ,p_bill_rate_override => l_assignment_rec.bill_rate_override
1011 ,p_bill_rate_curr_override => l_assignment_rec.bill_rate_curr_override
1012 ,p_markup_percent_override => l_assignment_rec.markup_percent_override
1013 ,p_tp_rate_override => l_assignment_rec.tp_rate_override
1014 ,p_tp_currency_override => l_assignment_rec.tp_currency_override
1015 ,p_tp_calc_base_code_override => l_assignment_rec.tp_calc_base_code_override
1016 ,p_tp_percent_applied_override => l_assignment_rec.tp_percent_applied_override
1017 ,p_markup_percent => l_assignment_rec.markup_percent
1018 ,p_attribute_category => l_assignment_rec.attribute_category
1019 ,p_attribute1 => l_assignment_rec.attribute1
1020 ,p_attribute2 => l_assignment_rec.attribute2
1021 ,p_attribute3 => l_assignment_rec.attribute3
1022 ,p_attribute4 => l_assignment_rec.attribute4
1023 ,p_attribute5 => l_assignment_rec.attribute5
1024 ,p_attribute6 => l_assignment_rec.attribute6
1025 ,p_attribute7 => l_assignment_rec.attribute7
1026 ,p_attribute8 => l_assignment_rec.attribute8
1027 ,p_attribute9 => l_assignment_rec.attribute9
1028 ,p_attribute10 => l_assignment_rec.attribute10
1029 ,p_attribute11 => l_assignment_rec.attribute11
1030 ,p_attribute12 => l_assignment_rec.attribute12
1031 ,p_attribute13 => l_assignment_rec.attribute13
1032 ,p_attribute14 => l_assignment_rec.attribute14
1033 ,p_attribute15 => l_assignment_rec.attribute15
1034 ,p_transfer_price_rate => l_assignment_rec.transfer_price_rate -- Added for bug 3051110
1035 ,p_transfer_pr_rate_curr => l_assignment_rec.transfer_pr_rate_curr
1036 ,p_discount_percentage => l_assignment_rec.discount_percentage -- Added for bug 3041583
1037 ,p_rate_disc_reason_code => l_assignment_rec.rate_disc_reason_code -- Added for bug 3041583
1038 ,x_assignment_row_id => l_assignment_row_id
1039 ,x_change_id => l_change_id
1040 ,x_return_status => l_return_status);
1041
1042 --dbms_output.put_line('x_change_id: '|| l_change_id);
1043 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1044 x_return_status := l_return_status;
1045 END IF;
1046 l_return_status := FND_API.G_MISS_CHAR;
1047 END IF;
1048
1049
1050
1051 IF (FND_MSG_PUB.Count_Msg = 0) THEN
1052 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1053 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENT_APPROVAL_PVT.Insert_Into_Assignment_History.schedule_history'
1054 ,x_msg => 'Updating Schedule history table.'
1055 ,x_log_level => 5);
1056 END IF;
1057
1058 --Call Schedule's API to insert into schedule history table
1059 PA_SCHEDULE_PVT.UPDATE_HISTORY_TABLE ( P_ASSIGNMENT_ID => l_assignment_rec.assignment_id
1060 ,P_CHANGE_ID => l_change_id
1061 ,X_RETURN_STATUS => l_return_status
1062 ,X_MSG_COUNT => l_msg_count
1063 ,X_MSG_DATA => l_msg_data);
1064 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1065 x_return_status := l_return_status;
1066 END IF;
1067 ELSE
1068 x_return_status := FND_API.G_RET_STS_ERROR;
1069 END IF;
1070
1071 x_change_id := l_change_id;
1072
1073 PA_DEBUG.Reset_Err_Stack; /* 3148857 */
1074
1075 EXCEPTION
1079 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
1076 WHEN OTHERS THEN
1077 -- Set the exception Message and the stack
1078 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_ASSIGNMENT_APPROVAL_PVT.Insert_Into_Assignment_History'
1080 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1081
1082 RAISE; -- This is optional depending on the needs
1083
1084 END Insert_Into_Assignment_History;
1085
1086
1087 --
1088 --This procedure abort the workflow approval outstanding for the specific assignment
1089 --and update the pending_approval_flag to 'N'
1090 --
1091 PROCEDURE Abort_Assignment_Approval
1092 (
1093 p_assignment_id IN pa_project_assignments.assignment_id%TYPE
1094 ,p_project_id IN pa_project_assignments.project_id%TYPE
1095 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1096 )
1097 IS
1098
1099 CURSOR get_item_key IS
1100 SELECT item_key, item_type
1101 FROM pa_wf_processes
1102 WHERE item_key = (
1103 SELECT max(item_key)
1104 FROM pa_wf_processes
1105 WHERE wf_type_code = 'ASSIGNMENT_APPROVAL'
1106 AND entity_key1 = to_char(p_project_id)
1107 AND entity_key2 = to_char(p_assignment_id)
1108 )
1109 and item_type = 'PAWFAAP';
1110
1111 l_item_key pa_wf_processes.item_key%TYPE;
1112 l_item_type pa_wf_processes.item_type%TYPE;
1113
1114 BEGIN
1115
1116 -- Initialize the Error Stack
1117 PA_DEBUG.set_err_stack('PA_ASSIGNMENT_APPROVAL_PVT.Insert_Into_Assignment_History');
1118
1119 --Log Message
1120 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1121 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENT_APPROVAL_PVT.Abort_Assignment_Approval.begin'
1122 ,x_msg => 'Beginning of Abort_Assignment_Approval'
1123 ,x_log_level => 5);
1124 END IF;
1125
1126 x_return_status := FND_API.G_RET_STS_SUCCESS;
1127
1128
1129 --Get the item key of the workflow process
1130 OPEN get_item_key;
1131 FETCH get_item_key INTO l_item_key, l_item_type;
1132
1133 IF get_item_key%NOTFOUND THEN
1134 PA_UTILS.Add_Message( p_app_short_name => 'PA'
1135 ,p_msg_name => 'PA_NO_WF_TO_ABORT');
1136 x_return_status := FND_API.G_RET_STS_ERROR;
1137 END IF;
1138
1139 CLOSE get_item_key;
1140
1141 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1142 --Abort the process
1143 wf_engine.abortprocess (itemtype => l_item_type,
1144 itemkey => l_item_key);
1145
1146 --Set the pending_approval_flag to 'N'
1147 PA_ASGMT_WFSTD.Maintain_wf_pending_flag (p_assignment_id => p_assignment_id
1148 ,p_mode => 'APPROVAL_PROCESS_COMPLETED');
1149 END IF;
1150
1151 PA_DEBUG.Reset_err_stack; /* 3148857 */
1152
1153 EXCEPTION
1154 WHEN OTHERS THEN
1155 -- Set the exception Message and the stack
1156 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_ASSIGNMENT_APPROVAL_PVT.Abort_Assignment_Approval'
1157 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
1158 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1159
1160 RAISE; -- This is optional depending on the needs
1161
1162 END Abort_Assignment_Approval;
1163
1164 --
1165 -- Returns meaning from pa_lookups which has p_lookup_type and p_lookup_code
1166 --
1167 FUNCTION get_lookup_meaning (p_lookup_type IN VARCHAR2
1168 ,p_lookup_code IN VARCHAR2)
1169 RETURN VARCHAR2
1170 IS
1171 l_meaning VARCHAR2(80);
1172 BEGIN
1173
1174 SELECT meaning
1175 INTO l_meaning
1176 FROM pa_lookups
1177 WHERE lookup_type = p_lookup_type
1178 AND lookup_code = p_lookup_code;
1179
1180 return l_meaning;
1181
1182 EXCEPTION
1183 WHEN NO_DATA_FOUND THEN
1184 return null;
1185 WHEN OTHERS THEN
1186 -- Set the exception Message and the stack
1187 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_ASSIGNMENT_APPROVAL_PVT.get_lookup_meaning'
1188 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
1189
1190 RAISE; -- This is optional depending on the needs
1191
1192 END get_lookup_meaning;
1193
1194
1195 --
1196 -- Return following record based on p_assignment_is
1197 -- x_saved_asmt_rec : assignment record having values in pa_project_assignments
1198 -- x_asmt_history_rec : assignment record having values in pa_assignments_history
1199 --
1200 PROCEDURE get_asmt_and_asmt_history_rec (p_assignment_id IN NUMBER
1201 ,x_saved_asmt_rec OUT NOCOPY PA_ASSIGNMENTS_PUB.assignment_rec_type --File.Sql.39 bug 4440895
1202 ,x_asmt_history_rec OUT NOCOPY pa_assignments_pub.assignment_rec_type --File.Sql.39 bug 4440895
1203 ,x_return_status OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
1204 IS
1205 CURSOR get_saved_asmt_rec IS
1206 SELECT assignment_id,
1207 assignment_name,
1208 staffing_priority_code,
1209 description,
1210 extension_possible,
1211 additional_information,
1215 fcst_tp_amount_type,
1212 work_type_id,
1213 expense_owner,
1214 expense_limit,
1216 expenditure_type_class,
1217 expenditure_type,
1218 location_id,
1219 tp_currency_override,
1220 tp_rate_override,
1221 tp_calc_base_code_override,
1222 tp_percent_applied_override,
1223 staffing_owner_person_id
1224 FROM pa_project_assignments
1225 WHERE assignment_id = p_assignment_id;
1226
1227 CURSOR get_asmt_history_rec IS
1228 SELECT assignment_id,
1229 assignment_name,
1230 staffing_priority_code,
1231 description,
1232 extension_possible,
1233 additional_information,
1234 work_type_id,
1235 expense_owner,
1236 expense_limit,
1237 fcst_tp_amount_type,
1238 expenditure_type_class,
1239 expenditure_type,
1240 location_id,
1241 tp_currency_override,
1242 tp_rate_override,
1243 tp_calc_base_code_override,
1244 tp_percent_applied_override,
1245 staffing_owner_person_id
1246 FROM pa_assignments_history
1247 WHERE assignment_id = p_assignment_id
1248 AND last_approved_flag = 'Y';
1249
1250 CURSOR get_apprvl_status_code IS
1251 SELECT apprvl_status_code
1252 FROM pa_project_assignments
1253 WHERE assignment_id = p_assignment_id;
1254
1255 l_apprvl_status_code pa_project_assignments.apprvl_status_code%TYPE;
1256 l_change_id NUMBER;
1257
1258 BEGIN
1259 x_return_status := FND_API.G_RET_STS_SUCCESS;
1260
1261 ------------------------------------------------------
1262 -- Get saved_asmt_rec
1263 ------------------------------------------------------
1264 OPEN get_saved_asmt_rec;
1265 FETCH get_saved_asmt_rec INTO
1266 x_saved_asmt_rec.assignment_id,
1267 x_saved_asmt_rec.assignment_name,
1268 x_saved_asmt_rec.staffing_priority_code,
1269 x_saved_asmt_rec.description,
1270 x_saved_asmt_rec.extension_possible,
1271 x_saved_asmt_rec.additional_information,
1272 x_saved_asmt_rec.work_type_id,
1273 x_saved_asmt_rec.expense_owner,
1274 x_saved_asmt_rec.expense_limit,
1275 x_saved_asmt_rec.fcst_tp_amount_type,
1276 x_saved_asmt_rec.expenditure_type_class,
1277 x_saved_asmt_rec.expenditure_type,
1278 x_saved_asmt_rec.location_id,
1279 x_saved_asmt_rec.tp_currency_override,
1280 x_saved_asmt_rec.tp_rate_override,
1281 x_saved_asmt_rec.tp_calc_base_code_override,
1282 x_saved_asmt_rec.tp_percent_applied_override,
1283 x_saved_asmt_rec.staffing_owner_person_id;
1284 CLOSE get_saved_asmt_rec;
1285
1286 ------------------------------------------------------
1287 -- Get asmt_history_rec
1288 ------------------------------------------------------
1289 -- get change_id just to check if the history table has a value for this asmt
1290 l_change_id := Get_Change_Id (p_assignment_id);
1291
1292 -- get approval status
1293 OPEN get_apprvl_status_code;
1294 FETCH get_apprvl_status_code INTO l_apprvl_status_code;
1295 CLOSE get_apprvl_status_code;
1296
1297 -- Following two cases we need to get data for x_asmt_history_rec from pa_project_assignments
1298 -- 1. If history table doesn't have a record for this assignment
1299 -- 2. If history table has a record but its approval_status is 'approved'
1300 -- Then it is mass update/schedule submit case, the record in history table is last approved
1301 -- data not the current approved one which is what we want to show on Change details page.
1302 IF (l_change_id = -1 OR
1303 (l_change_id <> -1 AND l_apprvl_status_code = PA_ASSIGNMENT_APPROVAL_PUB.g_approved)) THEN
1304 OPEN get_saved_asmt_rec;
1305 FETCH get_saved_asmt_rec INTO
1306 x_asmt_history_rec.assignment_id,
1307 x_asmt_history_rec.assignment_name,
1308 x_asmt_history_rec.staffing_priority_code,
1309 x_asmt_history_rec.description,
1310 x_asmt_history_rec.extension_possible,
1311 x_asmt_history_rec.additional_information,
1312 x_asmt_history_rec.work_type_id,
1313 x_asmt_history_rec.expense_owner,
1314 x_asmt_history_rec.expense_limit,
1315 x_asmt_history_rec.fcst_tp_amount_type,
1316 x_asmt_history_rec.expenditure_type_class,
1317 x_asmt_history_rec.expenditure_type,
1318 x_asmt_history_rec.location_id,
1319 x_asmt_history_rec.tp_currency_override,
1320 x_asmt_history_rec.tp_rate_override,
1321 x_asmt_history_rec.tp_calc_base_code_override,
1322 x_asmt_history_rec.tp_percent_applied_override,
1323 x_asmt_history_rec.staffing_owner_person_id;
1324 CLOSE get_saved_asmt_rec;
1325
1326 -- If hitory table has a record for this assignment and its approval_status is not 'approved'
1327 ELSE
1328 OPEN get_asmt_history_rec;
1329 FETCH get_asmt_history_rec INTO
1330 x_asmt_history_rec.assignment_id,
1331 x_asmt_history_rec.assignment_name,
1332 x_asmt_history_rec.staffing_priority_code,
1333 x_asmt_history_rec.description,
1334 x_asmt_history_rec.extension_possible,
1335 x_asmt_history_rec.additional_information,
1336 x_asmt_history_rec.work_type_id,
1337 x_asmt_history_rec.expense_owner,
1338 x_asmt_history_rec.expense_limit,
1339 x_asmt_history_rec.fcst_tp_amount_type,
1340 x_asmt_history_rec.expenditure_type_class,
1341 x_asmt_history_rec.expenditure_type,
1342 x_asmt_history_rec.location_id,
1343 x_asmt_history_rec.tp_currency_override,
1344 x_asmt_history_rec.tp_rate_override,
1345 x_asmt_history_rec.tp_calc_base_code_override,
1346 x_asmt_history_rec.tp_percent_applied_override,
1347 x_asmt_history_rec.staffing_owner_person_id;
1348 CLOSE get_asmt_history_rec;
1349 END IF;
1350
1351 EXCEPTION
1352 WHEN OTHERS THEN
1353 -- Set the exception Message and the stack
1354 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_ASSIGNMENT_APPROVAL_PVT.get_asmt_and_asmt_history_rec'
1355 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
1356 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1357
1358 RAISE; -- This is optional depending on the needs
1359 END get_asmt_and_asmt_history_rec;
1360
1361 END PA_ASSIGNMENT_APPROVAL_PVT ;