[Home] [Help]
PACKAGE BODY: APPS.PA_ASSIGNMENT_APPROVAL_PUB
Source
1 PACKAGE BODY PA_ASSIGNMENT_APPROVAL_PUB AS
2 /*$Header: PARAAPBB.pls 120.7.12010000.5 2010/06/14 07:05:54 kkorrapo ship $*/
3 --------------------------------------------------------------------------------------------------------------
4 -- This procedure prints the text which is being passed as the input
5 -- Input parameters
6 -- Parameters Type Required Description
7 -- p_log_msg VARCHAR2 YES It stores text which you want to print on screen
8 -- Out parameters
9 ----------------------------------------------------------------------------------------------------------------
10 P_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N'); /* Added Debug Profile Option variable initialization for bug#2674619 */
11
12 PROCEDURE log_message (p_log_msg IN VARCHAR2)
13 IS
14 BEGIN
15 --dbms_output.put_line('log: ' || p_log_msg);
16 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
17 pa_debug.write('log_message: ' || 'Assignment Approval', 'log: ' || p_log_msg, 3);
18 END IF;
19 NULL;
20 END log_message;
21
22 --
23 -- Wrapper API to set approval status and determine which kind of workflow to launch. The API will
24 -- only be called from Submit for Approval Page, when the user hit Submit, Approve or Reject buttons
25 -- or the Cancel button on team list.
26 -- p_action_code allowed: 'APPROVE', 'SUBMIT', 'REJECT', 'CANCEL'
27 --
28 PROCEDURE Start_Assignment_Approvals
29 ( p_assignment_id IN pa_project_assignments.assignment_id%TYPE
30 ,p_new_assignment_flag IN VARCHAR2
31 ,p_action_code IN VARCHAR2
32 ,p_note_to_approver IN VARCHAR2 := FND_API.G_MISS_CHAR
33 ,p_record_version_number IN NUMBER
34 ,p_apr_person_id IN NUMBER DEFAULT NULL
35 ,p_apr_person_name IN VARCHAR2 DEFAULT NULL
36 ,p_apr_person_type IN VARCHAR2 DEFAULT NULL
37 ,p_apr_person_order IN NUMBER DEFAULT NULL
38 ,p_apr_person_exclude IN VARCHAR2 DEFAULT NULL
39 ,p_check_overcommitment_flag IN VARCHAR2 := 'N'
40 ,p_conflict_group_id IN NUMBER DEFAULT NULL
41 ,p_resolve_con_action_code IN VARCHAR2 DEFAULT NULL
42 ,p_api_version IN NUMBER := 1.0
43 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
44 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
45 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
46 ,p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM
47 ,x_overcommitment_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
48 ,x_conflict_group_id OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
49 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
50 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
51 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
52 ) IS
53
54 l_approver_rec PA_ASSIGNMENT_APPROVAL_PUB.Asgmt_Approvers_Rec_Type;
55 l_assignment_type pa_project_assignments.assignment_type%TYPE;
56 l_project_id pa_project_assignments.project_id%TYPE;
57 l_project_status_name pa_project_statuses.project_status_name%TYPE;
58 l_success_status_code pa_project_statuses.project_status_code%TYPE;
59 l_failure_status_code pa_project_statuses.project_status_code%TYPE;
60 l_start_date pa_project_assignments.start_date%TYPE;
61 l_pending_approval_flag pa_project_assignments.pending_approval_flag%TYPE;
62 l_project_manager_person_id NUMBER ;
63 l_project_manager_name VARCHAR2(200);
64 l_project_party_id NUMBER ;
65 l_project_role_id NUMBER ;
66 l_project_role_name VARCHAR2(80);
67 l_approver_person_id NUMBER;
68 -- bug 4537865
69 l_new_approver_person_id NUMBER;
70 -- bug 4537865
71 l_approver_person_type VARCHAR2(100);
72 l_approver2_person_id NUMBER;
73 l_approver2_person_type VARCHAR2(100);
74 l_change_id NUMBER;
75 l_record_version_number NUMBER;
76 l_return_status VARCHAR2(1);
77 l_apprvl_status_code pa_project_assignments.apprvl_status_code%TYPE;
78 l_next_status_code pa_project_assignments.apprvl_status_code%TYPE;
79 l_schedule_status_code pa_project_assignments.status_code%TYPE;
80 l_msg_count NUMBER;
81 l_msg_data VARCHAR2(2000);
82 l_msg_index_out NUMBER;
83 l_wf_type VARCHAR2(80) := NULL;
84 l_wf_item_type VARCHAR2(2000):= NULL;
85 l_wf_process VARCHAR2(2000):= NULL;
86 l_resource_type_id NUMBER;
87 l_action_code VARCHAR2(7);
88 l_overcommitment_flag VARCHAR2(1);
89 l_conflict_group_id NUMBER;
90 l_error_message_code fnd_new_messages.message_name%TYPE;
91 l_approval_required_flag VARCHAR2(1);
92 l_check_id_flag VARCHAR2(1);
93 l_status_flag VARCHAR2(1);
94 l_submitter_user_id NUMBER;
95 l_submitter_person_id NUMBER;
96
97 CURSOR get_asgmt_info IS
98 SELECT ppa.assignment_type, ppa.project_id, ppa.start_date, ppa.status_code,
99 pps.project_status_name, ppa.pending_approval_flag
100 FROM pa_project_assignments ppa,
101 pa_project_statuses pps,
102 pa_projects_all pal
103 WHERE ppa.assignment_id = p_assignment_id
104 AND ppa.project_id = pal.project_id
105 AND pal.project_status_code = pps.project_status_code;
106
107 CURSOR get_status_codes IS
108 SELECT DISTINCT status_code
109 FROM pa_schedules /* Bug 5614557 Changed usage from pa_schedules_v to pa_schedules */
110 WHERE assignment_id = p_assignment_id;
111
112 CURSOR l_submitter_person_id_csr(l_submitter_user_id NUMBER) IS
113 SELECT employee_id
114 FROM fnd_user
115 WHERE user_id = l_submitter_user_id;
116
117 TYPE status_codes IS TABLE OF pa_project_assignments.status_code%TYPE;
118 l_status_codes status_codes;
119
120
121 BEGIN
122 -- Initialize the Error Stack
123 PA_DEBUG.init_err_stack('PA_ASSIGNMENT_APPROVAL_PUB.Start_Assignment_Approvals');
124
125 --Log Message
126 IF P_DEBUG_MODE = 'Y' THEN
127 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENT_APPROVAL_PUB.Start_Assignment_Approvals.begin'
128 ,x_msg => 'Beginning of Start_Assignment_Approvals'
129 ,x_log_level => 5);
130 END IF;
131
132 -- Initialize the out paramaters
133 x_return_status := FND_API.G_RET_STS_SUCCESS;
134 x_overcommitment_flag := 'N';
135 x_conflict_group_id := null;
136
137 IF FND_API.TO_BOOLEAN( p_init_msg_list ) THEN
138 --Clear the global PL/SQL message table
139 FND_MSG_PUB.initialize;
140
141 -- delete all the records from the approver_tbl
142 PA_ASSIGNMENT_APPROVAL_PUB.g_approver_tbl.delete;
143 END IF;
144
145 -- Issue API savepoint if the transaction is to be committed
146 IF p_commit = FND_API.G_TRUE THEN
147 SAVEPOINT ASG_APR_PUB_START_APPRVL;
148 END IF;
149
150 -- set local variables
151 l_conflict_group_id := p_conflict_group_id;
152
153 --
154 -- Get the necessary assignment info to start workflow
155 --
156 OPEN get_asgmt_info;
157 FETCH get_asgmt_info INTO l_assignment_type, l_project_id, l_start_date, l_schedule_status_code,
158 l_project_status_name, l_pending_approval_flag;
159 CLOSE get_asgmt_info;
160
161
162 --------------------------------------------------------------------------------------------------------
163 -- Put the approver's info into global PL/SQL table
164 --------------------------------------------------------------------------------------------------------
165 -- Check Resource Name or ID
166 IF (p_apr_person_id IS NOT NULL AND p_apr_person_id <>FND_API.G_MISS_NUM) OR
167 (p_apr_person_name IS NOT NULL AND p_apr_person_name <>FND_API.G_MISS_CHAR) THEN
168
169 l_approver_person_id := p_apr_person_id;
170
171 --Log Message
172 IF P_DEBUG_MODE = 'Y' THEN
173 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENT_APPROVAL_PUB.Start_Assignment_Approvals.check_resource'
174 ,x_msg => 'Checking Resource.'
175 ,x_log_level => 5);
176 END IF;
177 /* A temporary fix:
178 Need to avoid the LOV ID clearing check implemented in most validation packages.
179 Since only the ids are passed in and not the names.*/
180
181 l_check_id_flag := PA_STARTUP.G_Check_ID_Flag;
182 IF PA_STARTUP.G_Calling_Application = 'SELF_SERVICE' THEN
183 PA_STARTUP.G_Check_ID_Flag := 'N';
184 END IF;
185
186 PA_RESOURCE_UTILS.Check_ResourceName_OR_ID ( p_resource_id => l_approver_person_id
187 ,p_resource_name => p_apr_person_name
188 ,p_check_id_flag => PA_STARTUP.G_Check_ID_Flag
189 ,p_date => l_start_date
190 --,x_resource_id => l_approver_person_id * Bug: 4537865
191 ,x_resource_id => l_new_approver_person_id -- Bug: 4537865
192 ,x_resource_type_id => l_resource_type_id
193 ,x_return_status => l_return_status
194 ,x_error_message_code => l_error_message_code);
195 PA_STARTUP.G_Check_ID_Flag := l_check_id_flag;
196 -- bug 4537865
197 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
198 l_approver_person_id := l_new_approver_person_id;
199 END IF;
200 -- bug 4537865
201 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
202 PA_UTILS.Add_Message('PA', l_error_message_code);
203
204 ELSE
205 -- Get submitter_person_id : added for bug#2247058
206 OPEN l_submitter_person_id_csr(FND_GLOBAL.USER_ID);
207 FETCH l_submitter_person_id_csr INTO l_submitter_person_id;
208 IF l_submitter_person_id_csr%NOTFOUND THEN
209 pa_utils.add_message (p_app_short_name => 'PA',
210 p_msg_name => 'PA_NO_EMP_ID_USER');
211 END IF;
212 CLOSE l_submitter_person_id_csr;
213
214 l_return_status := FND_API.G_MISS_CHAR;
215 l_error_message_code := FND_API.G_MISS_CHAR;
216
217 -- Put the Approver into record only exclude flag=N and submitter is not the approver
218 IF p_apr_person_exclude = 'N' AND l_submitter_person_id <> l_approver_person_id THEN
219 IF P_DEBUG_MODE = 'Y' THEN
220 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENT_APPROVAL_PUB.Start_Assignment_Approvals.into_g_table'
221 ,x_msg => 'Putting approver into global table.'
222 ,x_log_level => 5);
223 END IF;
224 l_approver_rec.person_id := l_approver_person_id;
225 l_approver_rec.approver_person_type := p_apr_person_type;
226 l_approver_rec.orders := p_apr_person_order;
227
228 PA_ASSIGNMENT_APPROVAL_PUB.g_approver_tbl(PA_ASSIGNMENT_APPROVAL_PUB.g_approver_tbl.COUNT+1) := l_approver_rec;
229 END IF; -- IF p_apr_person_exclude = 'N'
230
231 END IF;-- end of valid resource
232
233 END IF; -- IF (p_apr_person_id IS NOT NULL AND ...
234
235 --------------------------------------------------------------------------------------------------------
236 -- Overcommitment checking
237 --------------------------------------------------------------------------------------------------------
238 IF (p_check_overcommitment_flag IS NOT NULL AND p_check_overcommitment_flag='Y') THEN
239 PA_SCHEDULE_PVT.Check_overcommitment_single
240 ( p_assignment_id => p_assignment_id
241 ,p_resolve_conflict_action_code => p_resolve_con_action_code
242 ,x_overcommitment_flag => l_overcommitment_flag
243 ,x_conflict_group_id => l_conflict_group_id
244 ,x_return_status => l_return_status
245 ,x_msg_count => l_msg_count
246 ,x_msg_data => l_msg_data);
247
248 x_overcommitment_flag := l_overcommitment_flag;
249 x_conflict_group_id := to_char(l_conflict_group_id);
250
251 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
252 RAISE FND_API.G_EXC_ERROR;
253 END IF;
254
255 IF (l_overcommitment_flag = 'Y') THEN
256 return;
257 END IF;
258 END IF; --overcommitment_flag='Y'
259
260 --------------------------------------------------------------------------------------------------------
261 -- If p_validate_only = FALSE and need to check overcom, then do all the processing to start assignment approval
262 --------------------------------------------------------------------------------------------------------
263 IF (p_validate_only = FND_API.G_FALSE AND (p_check_overcommitment_flag ='N'
264 OR (p_check_overcommitment_flag ='Y' AND l_overcommitment_flag = 'N'))) THEN
265
266 IF P_DEBUG_MODE = 'Y' THEN
267 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENT_APPROVAL_PUB.Start_Assignment_Approvals.validate_false'
268 ,x_msg => 'Not just validating.'
269 ,x_log_level => 5);
270 END IF;
271 --------------------------------------------------------------------------------------------------------
272 -- Validate Action Code
273 --------------------------------------------------------------------------------------------------------
274 l_action_code := p_action_code;
275 IF l_action_code NOT IN (PA_ASSIGNMENT_APPROVAL_PUB.g_approve_action, PA_ASSIGNMENT_APPROVAL_PUB.g_reject_action,
276 PA_ASSIGNMENT_APPROVAL_PUB.g_submit_action, PA_ASSIGNMENT_APPROVAL_PUB.g_cancel_action) THEN
277 PA_UTILS.Add_Message('PA', 'PA_UNEXP_APPRVL_ACTION');
278 END IF;
279
280
281 --IF submitting for approval, then check is approval is required.
282 -- IF approval is required, then check if workflow is enabled.
283 -- IF workflow is enabled, then validate the approvers order, and check if project manager exists.
284 -- And launch workflow.
285 -- IF no approval is required, then changes are approved.
286
287 --------------------------------------------------------------------------------------------------------
288 -- If Action = SUBMIT
289 --------------------------------------------------------------------------------------------------------
290 IF l_action_code = PA_ASSIGNMENT_APPROVAL_PUB.g_submit_action THEN
291
292 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
293 PA_DEBUG.write_log ('pa.plsql.PA_ASSIGNMENT_APPROVAL_PUB.Start_Assignment_Approvals.submit'
294 ,'Submitting an approval.', 5);
295 END IF;
296
297 --Check if approval is required
298 PA_ASSIGNMENT_APPROVAL_PVT.Check_Approval_Required(p_assignment_id => p_assignment_id
299 ,p_new_assignment_flag => p_new_assignment_flag
300 ,x_approval_required_flag => l_approval_required_flag
301 ,x_return_status => l_return_status);
302
303 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
304
305 --------------------------------------------------------------------------------------------------------
306 -- If Approval Required
307 --------------------------------------------------------------------------------------------------------
308 IF l_approval_required_flag = 'Y' THEN
309 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
310 PA_DEBUG.write_log ('pa.plsql.PA_ASSIGNMENT_APPROVAL_PUB.Start_Assignment_Approvals.appr_reqd'
311 ,'Approval Required.', 5);
312 END IF;
313
314 --Check if workflow enabled
315 PA_ASGMT_WFSTD.get_workflow_process_info(p_status_code => PA_ASSIGNMENT_APPROVAL_PUB.g_submitted,
316 x_wf_item_type => l_wf_item_type,
317 x_wf_process => l_wf_process,
318 x_wf_type => l_wf_type,
319 x_msg_count => l_msg_count,
320 x_msg_data => l_msg_data,
321 x_return_status => l_return_status,
322 x_error_message_code => l_error_message_code);
323
324 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
325 PA_UTILS.Add_Message('PA',l_error_message_code);
326 END IF;
327 /* commented out for bug #2247058
328 --------------------------------------------------------------------------------------------------------
329 -- If Workflow is Enabled
330 --------------------------------------------------------------------------------------------------------
331 IF l_wf_type <> 'NOT_ENABLED' THEN
332 PA_DEBUG.write_log ('pa.plsql.PA_ASSIGNMENT_APPROVAL_PUB.Start_Assignment_Approvals.enabled'
333 ,'Workflow is enabled. Now validating approvers order.',5);
334
335 --Validate Approvers Order
336 PA_ASSIGNMENT_APPROVAL_PVT.Validate_Approver_Orders (x_return_status => l_return_status);
337 END IF; --end of workflow enabled
338 */
339 -- if approval is not required
340 ELSE
341 l_action_code := PA_ASSIGNMENT_APPROVAL_PUB.g_approve_action;
342 END IF; -- if l_approval_required_flag = 'Y'
343
344 END IF; -- l_return_status = FND_API.G_RET_STS_SUCCESS
345
346 END IF; -- If Action = SUBMIT
347
348
349 --------------------------------------------------------------------------------------------------------
350 -- Check project status controls to see if provisional/confirmed assignments are allowed
351 --------------------------------------------------------------------------------------------------------
352 --Get the schedule status(es) of the assignment
353 IF l_schedule_status_code IS NULL THEN
354 OPEN get_status_codes;
355 FETCH get_status_codes BULK COLLECT INTO l_status_codes;
356 CLOSE get_status_codes;
357 ELSE
358 --use constructor to initialize the nested table.
359 l_status_codes := status_codes(l_schedule_status_code);
360 END IF;
361
362
363 --Get their success/failure statuses
364 --and check to see if provisional/confirmed assignments are allowed for the given project statuses
365 FOR l_index IN 1..l_status_codes.COUNT LOOP
366
367 --Get success/failure statuses
368 PA_PROJECT_STUS_UTILS.get_wf_success_failure_status
369 (p_status_code => l_status_codes(l_index)
370 ,p_status_type => 'STAFFED_ASGMT'
371 ,x_wf_success_status_code => l_success_status_code
372 ,x_wf_failure_status_code => l_failure_status_code
373 ,x_return_status => l_return_status
374 ,x_error_message_code => l_error_message_code) ;
375
376 --Check to see if the the success status is allowed for the given project status
377 IF( PA_ASSIGNMENT_UTILS.is_asgmt_allow_stus_ctl_check(
378 p_asgmt_status_code => l_success_status_code
379 ,p_project_id => l_project_id
380 ,p_add_message => 'N') = 'N' ) THEN
381
382 PA_UTILS.Add_Message( p_app_short_name => 'PA'
383 ,p_msg_name => 'PA_APPRVL_PROJ_STUS_NOT_ALLOW'
384 ,p_token1 => 'PROJ_STATUS'
385 ,p_value1 => l_project_status_name);
386 EXIT;
387 --dbms_output.put_line('Open Assignment Status not allowed');
388 END IF; --end of check provisional
389
390 --Check to see if the failure status is allowed for the given project status
391 IF( PA_ASSIGNMENT_UTILS.is_asgmt_allow_stus_ctl_check(
392 p_asgmt_status_code => l_failure_status_code
393 ,p_project_id => l_project_id
394 ,p_add_message => 'N') = 'N' ) THEN
395
396 PA_UTILS.Add_Message( p_app_short_name => 'PA'
397 ,p_msg_name => 'PA_APPRVL_PROJ_STUS_NOT_ALLOW'
398 ,p_token1 => 'PROJ_STATUS'
399 ,p_value1 => l_project_status_name);
400 EXIT;
401 --dbms_output.put_line('Open Assignment Status not allowed');
402 END IF; --end of check provisional
403
404 END LOOP; --end of for loop
405
406
407 -- if there is no error so far
408 IF FND_MSG_PUB.Count_Msg < 1 THEN
409
410 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
411 PA_DEBUG.write_log ('pa.plsql.PA_ASSIGNMENT_APPROVAL_PUB.Start_Assignment_Approvals.update_status',
412 'Update Assignment Approval Status.', 5);
413 END IF;
414 --------------------------------------------------------------------------------------------------------
415 -- Update Approval Status
416 --------------------------------------------------------------------------------------------------------
417 PA_ASSIGNMENT_APPROVAL_PVT.Update_Approval_Status(p_assignment_id => p_assignment_id
418 ,p_action_code => l_action_code
419 ,p_note_to_approver => p_note_to_approver
420 ,p_record_version_number => p_record_version_number
421 ,x_apprvl_status_code => l_apprvl_status_code
422 ,x_change_id => l_change_id
423 ,x_record_version_number => l_record_version_number
424 ,x_return_status => l_return_status
425 ,x_msg_count => l_msg_count
426 ,x_msg_data => l_msg_data);
427
428 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
429 l_return_status := FND_API.G_MISS_CHAR;
430
431 --------------------------------------------------------------------------------------------------------
432 -- Resolve Overcommitment conflict for Approve case
433 --------------------------------------------------------------------------------------------------------
434 IF (l_conflict_group_id IS NOT NULL AND l_action_code=PA_ASSIGNMENT_APPROVAL_PUB.g_approve_action) THEN
435 -- resolve remaining conflicts by taking action chosen by user
436 PA_SCHEDULE_PVT.RESOLVE_CONFLICTS (p_conflict_group_id => l_conflict_group_id
437 ,p_assignment_id => p_assignment_id
438 ,x_return_status => l_return_status
439 ,x_msg_count => l_msg_count
440 ,x_msg_data => l_msg_data);
441 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
442 RAISE FND_API.G_EXC_ERROR;
443 END IF;
444
445 -- complete post overcommitment processing
446 PA_SCHEDULE_PVT.OVERCOM_POST_APRVL_PROCESSING
447 (p_conflict_group_id => l_conflict_group_id
448 ,p_fnd_user_name => FND_GLOBAL.USER_NAME
449 ,x_return_status => l_return_status
450 ,x_msg_count => l_msg_count
451 ,x_msg_data => l_msg_data);
452 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
453 RAISE FND_API.G_EXC_ERROR;
454 END IF;
455 END IF; -- IF (l_conflict_group_id IS NOT NULL...
456
457
458 --If previously no workflow info has been obtained, then get it.
459 IF (l_wf_item_type IS NULL AND l_wf_process IS NULL AND l_wf_type IS NULL) THEN
460
461 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
462 PA_DEBUG.write_log ('pa.plsql.PA_ASSIGNMENT_APPROVAL_PUB.Start_Assignment_Approvals.get_wf_info'
463 ,'Getting workflow info.', 5);
464 END IF;
465
466 --dbms_output.put_line('error before get_workflow_process_info'||FND_MSG_PUB.Count_Msg);
467 PA_ASGMT_WFSTD.get_workflow_process_info(p_status_code => l_apprvl_status_code,
468 x_wf_item_type => l_wf_item_type,
469 x_wf_process => l_wf_process,
470 x_wf_type => l_wf_type,
471 x_msg_count => l_msg_count,
472 x_msg_data => l_msg_data,
473 x_return_status => l_return_status,
474 x_error_message_code => l_error_message_code);
475 --dbms_output.put_line('inside wf type'||l_wf_type);
476
477 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
478 PA_UTILS.Add_Message('PA', l_error_message_code);
479 END IF;
480
481 END IF; -- (l_wf_item_type IS NULL AND l..
482
483 --dbms_output.put_line('l_action_code'||l_action_code);
484 --dbms_output.put_line('p_new_assignment_flag'||p_new_assignment_flag);
485
486 --------------------------------------------------------------------------------------------------------
487 -- If workflow launch is required
488 --------------------------------------------------------------------------------------------------------
489 --workflow enabled and no error when getting workflow info
490 --For Cancel option, do not launch workflow if the assignment have not been previously approved.
491 IF (l_wf_type <> 'NOT_ENABLED') AND
492 (l_action_code <> PA_ASSIGNMENT_APPROVAL_PUB.g_cancel_action OR p_new_assignment_flag = 'N' )THEN
493
494 --dbms_output.put_line('ready to launch');
495 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
496 PA_DEBUG.write_log ('pa.plsql.PA_ASSIGNMENT_APPROVAL_PUB.Start_Assignment_Approvals.get_approvers',
497 'Workflow enabled. Getting Approvers ready for start workflow.', 5);
498 END IF;
499
500 --------------------------------------------------------------------------------------------------------
501 -- Get the approvers from the PL/SQL Table
502 --------------------------------------------------------------------------------------------------------
503 l_approver_person_id := NULL;
504 l_approver_person_type := NULL;
505 l_approver2_person_id := NULL;
506 l_approver2_person_type := NULL;
507
508 IF g_approver_tbl.FIRST IS NOT NULL THEN
509 l_approver_person_id := g_approver_tbl(g_approver_tbl.FIRST).person_id;
510 l_approver_person_type := g_approver_tbl(g_approver_tbl.FIRST).approver_person_type;
511
512 IF g_approver_tbl.COUNT > 1 THEN
513 l_approver2_person_id := g_approver_tbl(g_approver_tbl.LAST).person_id;
514 l_approver2_person_type := g_approver_tbl(g_approver_tbl.LAST).approver_person_type;
515 END IF;
516 END IF;
517
518 --------------------------------------------------------------------------------------------------------
519 -- Validate if a project manager exist for non-admin projects
520 --------------------------------------------------------------------------------------------------------
521 IF l_assignment_type <> 'STAFFED_ADMIN_ASSIGNMENT' THEN
522 IF P_DEBUG_MODE = 'Y' THEN
523 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENT_APPROVAL_PUB.Start_Assignment_Approvals.check_prj_manager.'
524 ,x_msg => 'Check if proj manger exists.'
525 ,x_log_level => 5);
526 END IF;
527 pa_project_parties_utils.get_curr_proj_mgr_details
528 (p_project_id => l_project_id
529 ,x_manager_person_id => l_project_manager_person_id
530 ,x_manager_name => l_project_manager_name
531 ,x_project_party_id => l_project_party_id
532 ,x_project_role_id => l_project_role_id
533 ,x_project_role_name => l_project_role_name
534 ,x_return_status => l_return_status
535 ,x_error_message_code => l_error_message_code );
536
537 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
538 PA_UTILS.Add_Message('PA', l_error_message_code);
539 END IF;
540
541 l_return_status := FND_API.G_MISS_CHAR;
542 l_error_message_code := FND_API.G_MISS_CHAR;
543 END IF; -- IF l_assignment_type <> 'STAFFED_ADMIN_ASSIGNMENT'
544
545 ----------------------------------------------------------
546 -- check Pending Approval
547 ----------------------------------------------------------
548 IF NVL(l_pending_approval_flag, 'N') = 'Y' THEN
549 PA_UTILS.Add_Message('PA', 'PA_ASG_APPROVAL_PENDING');
550 ELSE
551 -- set approval pending flag if this assignment has been submitted
552 IF l_action_code = g_submit_action THEN
553 PA_ASGMT_WFSTD.Maintain_wf_pending_flag
554 (p_assignment_id => p_assignment_id,
555 p_mode => 'PENDING_APPROVAL') ;
556 END IF;
557 END IF;
558
559 --------------------------------------------------------------------------------------------------------
560 -- Launch workflow
561 --------------------------------------------------------------------------------------------------------
562 IF FND_MSG_PUB.Count_Msg < 1 THEN
563 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
564 PA_DEBUG.write_log ('pa.plsql.PA_ASSIGNMENT_APPROVAL_PUB.Start_Assignment_Approvals.launch_wf'
565 ,'Launching workflow.', 5);
566 END IF;
567 --dbms_output.put_line('wf item type: '||l_wf_item_type ||', wf process: '||l_wf_process ||
568 -- 'apprvl_status_code'||l_apprvl_status_code);
569
570 PA_ASGMT_WFSTD.Start_Workflow ( p_project_id => l_project_id
571 , p_assignment_id => p_assignment_id
572 , p_status_code => l_apprvl_status_code
573 , p_wf_item_type => l_wf_item_type
574 , p_wf_process => l_wf_process
575 , p_approver1_person_id => l_approver_person_id
576 , p_approver1_type => l_approver_person_type
577 , p_approver2_person_id => l_approver2_person_id
578 , p_approver2_type => l_approver2_person_type
579 , p_conflict_group_id => l_conflict_group_id
580 , x_msg_count => l_msg_count
581 , x_msg_data => l_msg_data
582 , x_return_status => l_return_status
583 , x_error_message_code => l_error_message_code);
584 --dbms_output.put_line('start_workflow return status:'||l_return_status );
585
586 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
587 PA_UTILS.Add_Message('PA', l_error_message_code);
588 END IF;
589 END IF; -- FND_MSG_PUB.Count_Msg < 1
590
591 END IF; -- IF (l_wf_type <> 'NOT_ENABLED') AND...
592 END IF; -- return_status of update_approval_status = FND_API.G_RET_STS_SUCCESS
593 END IF; -- IF FND_MSG_PUB.Count_Msg < 1
594
595
596 --clear PL/SQL table for next time
597 PA_ASSIGNMENT_APPROVAL_PUB.g_approver_tbl.DELETE;
598
599 END IF; -- IF p_validate_only = FND_API.G_FALSE
600
601 --------------------------------------------------------------------------------------------------------
602 -- Set OUT parameters
603 --------------------------------------------------------------------------------------------------------
604 x_msg_count := FND_MSG_PUB.Count_Msg;
605
606 -- If g_error_exists is TRUE then set the x_return_status to 'E'
607 IF x_msg_count >0 THEN
608 x_return_status := FND_API.G_RET_STS_ERROR;
609
610 IF x_msg_count = 1 THEN
611 pa_interface_utils_pub.get_messages ( p_encoded => FND_API.G_TRUE
612 ,p_msg_index => 1
613 ,p_data => x_msg_data
614 ,p_msg_index_out => l_msg_index_out );
615 END IF;
616 END IF;
617
618 -- Reset the error stack when returning to the calling program
619 PA_DEBUG.Reset_Err_Stack;
620
621 EXCEPTION
622 WHEN FND_API.G_EXC_ERROR THEN
623 x_return_status := FND_API.G_RET_STS_ERROR;
624 x_msg_count := FND_MSG_PUB.Count_Msg;
625
626 IF x_msg_count = 1 THEN
627 pa_interface_utils_pub.get_messages (p_encoded => FND_API.G_TRUE,
628 p_msg_index => 1,
629 p_data => x_msg_data,
630 p_msg_index_out => l_msg_index_out );
631 END IF;
632
633 WHEN OTHERS THEN
634 IF p_commit = FND_API.G_TRUE THEN
635 ROLLBACK TO ASG_APR_PUB_START_APPRVL;
636 END IF;
637
638 -- Set the exception Message and the stack
639 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_ASSIGNMENT_APPROVAL_PVT.Start_Assignment_Approvals'
640 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
641 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
642
643 --clear PL/SQL table for next time
644 PA_ASSIGNMENT_APPROVAL_PUB.g_approver_tbl.DELETE;
645 RAISE; -- This is optional depending on the needs
646
647 END Start_Assignment_Approvals;
648
649
650
651
652 --
653 --API used to revert the current record in pa_project_assignments table to the last approved record in history table.
654 --
655 PROCEDURE Revert_To_Last_Approved
656 ( p_assignment_id IN pa_project_assignments.assignment_id%TYPE
657 ,p_api_version IN NUMBER := 1.0
658 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
659 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
660 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
661 ,p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM
662 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
663 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
664 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
665 )
666
667 IS
668
669 l_assignment_rec PA_ASSIGNMENTS_PUB.Assignment_Rec_Type;
670 l_change_id NUMBER;
671 l_record_version_number NUMBER;
672 l_return_status VARCHAR2(1);
673 l_apprvl_status_code pa_project_assignments.apprvl_status_code%TYPE;
674 l_next_status_code pa_project_assignments.apprvl_status_code%TYPE;
675 l_msg_count NUMBER;
676 l_msg_data VARCHAR2(2000);
677 l_msg_index_out NUMBER;
678 l_project_subteam_party_id NUMBER;
679 l_project_subteam_id NUMBER;
680
681 CURSOR get_apprvl_stus_and_record_num IS
682 SELECT apprvl_status_code, record_version_number
683 FROM pa_project_assignments
684 WHERE assignment_id = p_assignment_id;
685
686 BEGIN
687
688 -- Initialize the Error Stack
689 PA_DEBUG.init_err_stack('PA_ASSIGNMENT_APPROVAL_PUB.Revert_To_Last_Approved');
690
691 --Log Message
692 IF P_DEBUG_MODE = 'Y' THEN
693 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENT_APPROVAL_PUB.Revert_To_Last_Approved.begin'
694 ,x_msg => 'Beginning of Revert_To_Last_Approved.'
695 ,x_log_level => 5);
696 END IF;
697
698 -- Initialize the return status to success
699 x_return_status := FND_API.G_RET_STS_SUCCESS;
700
701 --Clear the global PL/SQL message table
702 IF FND_API.TO_BOOLEAN( p_init_msg_list ) THEN
703 FND_MSG_PUB.initialize;
704 END IF;
705
706 -- Issue API savepoint if the transaction is to be committed
707 IF p_commit = FND_API.G_TRUE THEN
708 SAVEPOINT ASG_APR_PUB_REVERT;
709 END IF;
710
711 IF PA_ASGMT_WFSTD.is_approval_pending(p_assignment_id => p_assignment_id) = 'Y' THEN
712
713 PA_UTILS.Add_Message( p_app_short_name => 'PA'
714 ,p_msg_name => 'PA_WF_APPROVAL_PENDING');
715 ELSIF PA_ASSIGNMENT_APPROVAL_PVT.is_new_assignment(p_assignment_id => p_assignment_id) = 'Y' THEN
716 PA_UTILS.Add_Message( p_app_short_name => 'PA'
717 ,p_msg_name => 'PA_NO_APPRV_ASGMT_TO_REVERT');
718 ELSE
719 --
720 --Get last approved record from assignments history
721 --
722 SELECT
723 assignment_id
724 ,assignment_name
725 ,assignment_type
726 ,multiple_status_flag
727 ,record_version_number
728 ,change_id
729 ,apprvl_status_code
730 ,status_code
731 ,staffing_priority_code
732 ,project_id
733 ,project_role_id
734 ,resource_id
735 ,project_party_id
736 ,project_subteam_id
737 ,description
738 ,note_to_approver
739 ,start_date
740 ,end_date
741 ,assignment_effort
742 ,extension_possible
743 ,source_assignment_id
744 ,assignment_template_id
745 ,min_resource_job_level
746 ,max_resource_job_level
747 ,assignment_number
748 ,additional_information
749 ,work_type_id
750 ,revenue_currency_code
751 ,revenue_bill_rate
752 ,expense_owner
753 ,expense_limit
754 ,expense_limit_currency_code
755 ,fcst_tp_amount_type
756 ,fcst_job_id
757 ,fcst_job_group_id
758 ,expenditure_org_id
759 ,expenditure_organization_id
760 ,expenditure_type_class
761 ,expenditure_type
762 ,location_id
763 ,calendar_type
764 ,calendar_id
765 ,resource_calendar_percent
766 -- ,no_of_active_candidates
767 ,competence_match_weighting
768 ,availability_match_weighting
769 ,job_level_match_weighting
770 ,search_min_availability
771 ,search_country_code
772 ,search_exp_org_struct_ver_id
773 ,search_exp_start_org_id
774 ,search_min_candidate_score
775 ,last_auto_search_date
776 ,enable_auto_cand_nom_flag
777 ,mass_wf_in_progress_flag
778 ,bill_rate_override
779 ,bill_rate_curr_override
780 ,markup_percent_override
781 ,tp_rate_override
782 ,tp_currency_override
783 ,tp_calc_base_code_override
784 ,tp_percent_applied_override
785 ,markup_percent
786 ,attribute_category
787 ,attribute1
788 ,attribute2
789 ,attribute3
790 ,attribute4
791 ,attribute5
792 ,attribute6
793 ,attribute7
794 ,attribute8
795 ,attribute9
796 ,attribute10
797 ,attribute11
798 ,attribute12
799 ,attribute13
800 ,attribute14
801 ,attribute15
802 INTO
803 l_assignment_rec.assignment_id
804 ,l_assignment_rec.assignment_name
805 ,l_assignment_rec.assignment_type
806 ,l_assignment_rec.multiple_status_flag
807 ,l_assignment_rec.record_version_number
808 ,l_change_id
809 ,l_assignment_rec.apprvl_status_code
810 ,l_assignment_rec.status_code
811 ,l_assignment_rec.staffing_priority_code
812 ,l_assignment_rec.project_id
813 ,l_assignment_rec.project_role_id
814 ,l_assignment_rec.resource_id
815 ,l_assignment_rec.project_party_id
816 ,l_project_subteam_id
817 ,l_assignment_rec.description
818 ,l_assignment_rec.note_to_approver
819 ,l_assignment_rec.start_date
820 ,l_assignment_rec.end_date
821 ,l_assignment_rec.assignment_effort
822 ,l_assignment_rec.extension_possible
823 ,l_assignment_rec.source_assignment_id
824 ,l_assignment_rec.assignment_template_id
825 ,l_assignment_rec.min_resource_job_level
826 ,l_assignment_rec.max_resource_job_level
827 ,l_assignment_rec.assignment_number
828 ,l_assignment_rec.additional_information
829 ,l_assignment_rec.work_type_id
830 ,l_assignment_rec.revenue_currency_code
831 ,l_assignment_rec.revenue_bill_rate
832 ,l_assignment_rec.expense_owner
833 ,l_assignment_rec.expense_limit
834 ,l_assignment_rec.expense_limit_currency_code
835 ,l_assignment_rec.fcst_tp_amount_type
836 ,l_assignment_rec.fcst_job_id
837 ,l_assignment_rec.fcst_job_group_id
838 ,l_assignment_rec.expenditure_org_id
839 ,l_assignment_rec.expenditure_organization_id
840 ,l_assignment_rec.expenditure_type_class
841 ,l_assignment_rec.expenditure_type
842 ,l_assignment_rec.location_id
843 ,l_assignment_rec.calendar_type
844 ,l_assignment_rec.calendar_id
845 ,l_assignment_rec.resource_calendar_percent
846 -- ,l_assignment_rec.no_of_active_candidates
847 ,l_assignment_rec.comp_match_weighting
848 ,l_assignment_rec.avail_match_weighting
849 ,l_assignment_rec.job_level_match_weighting
850 ,l_assignment_rec.search_min_availability
851 ,l_assignment_rec.search_country_code
852 ,l_assignment_rec.search_exp_org_struct_ver_id
853 ,l_assignment_rec.search_exp_start_org_id
854 ,l_assignment_rec.search_min_candidate_score
855 ,l_assignment_rec.last_auto_search_date
856 ,l_assignment_rec.enable_auto_cand_nom_flag
857 ,l_assignment_rec.mass_wf_in_progress_flag
858 ,l_assignment_rec.bill_rate_override
859 ,l_assignment_rec.bill_rate_curr_override
860 ,l_assignment_rec.markup_percent_override
861 ,l_assignment_rec.tp_rate_override
862 ,l_assignment_rec.tp_currency_override
863 ,l_assignment_rec.tp_calc_base_code_override
864 ,l_assignment_rec.tp_percent_applied_override
865 ,l_assignment_rec.markup_percent
866 ,l_assignment_rec.attribute_category
867 ,l_assignment_rec.attribute1
868 ,l_assignment_rec.attribute2
869 ,l_assignment_rec.attribute3
870 ,l_assignment_rec.attribute4
871 ,l_assignment_rec.attribute5
872 ,l_assignment_rec.attribute6
873 ,l_assignment_rec.attribute7
874 ,l_assignment_rec.attribute8
875 ,l_assignment_rec.attribute9
876 ,l_assignment_rec.attribute10
877 ,l_assignment_rec.attribute11
878 ,l_assignment_rec.attribute12
879 ,l_assignment_rec.attribute13
880 ,l_assignment_rec.attribute14
881 ,l_assignment_rec.attribute15
882 FROM pa_assignments_history
883 WHERE assignment_id = p_assignment_id
884 AND last_approved_flag = 'Y';
885
886
887 --Log Message
888 IF P_DEBUG_MODE = 'Y' THEN
889 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENT_APPROVAL_PUB.Revert_To_Last_Approved.delete_row'
890 ,x_msg => 'Deleting last approved record.'
891 ,x_log_level => 5);
892 END IF;
893
894 --
895 --Delete the last approved record from the assignment history table.
896 --
897 IF (FND_MSG_PUB.Count_Msg = 0) THEN
898 PA_ASSIGNMENTS_HISTORY_PKG.Delete_Row( p_assignment_id => p_assignment_id
899 ,p_last_approved_flag => 'Y'
900 ,x_return_status => l_return_status);
901 END IF;
902
903
904 --
905 --Get current assignment status and record version number
906 --
907 OPEN get_apprvl_stus_and_record_num;
908 FETCH get_apprvl_stus_and_record_num INTO l_apprvl_status_code, l_record_version_number;
909
910 CLOSE get_apprvl_stus_and_record_num;
911
912
913 --Log Message
914 IF P_DEBUG_MODE = 'Y' THEN
915 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENT_APPROVAL_PUB.Revert_To_Last_Approved.get_next_stus'
916 ,x_msg => 'Get next assignment approval status..'
917 ,x_log_level => 5);
918 END IF;
919
920 --
921 --Get the Assignment Approval Status after reverting
922 --
923 PA_ASSIGNMENT_APPROVAL_PVT.Get_Next_Status_After_Action ( p_action_code => PA_ASSIGNMENT_APPROVAL_PUB.g_revert_action
924 ,p_status_code => l_apprvl_status_code
925 ,x_status_code => l_next_status_code
926 ,x_return_status => l_return_status);
927
928
929
930 --
931 --Update the current assignment record with new status and record details from the history table
932 --
933 IF (FND_MSG_PUB.Count_Msg = 0) THEN
934
935 --Log Message
936 IF P_DEBUG_MODE = 'Y' THEN
937 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENT_APPROVAL_PUB.Revert_To_Last_Approved.update_row'
938 ,x_msg => 'Update current record with history record details.'
939 ,x_log_level => 5);
940 END IF;
941
942 PA_PROJECT_ASSIGNMENTS_PKG.Update_Row
943 (p_assignment_id => l_assignment_rec.assignment_id
944 ,p_record_version_number => l_record_version_number
945 ,p_assignment_name => l_assignment_rec.assignment_name
946 ,p_assignment_type => l_assignment_rec.assignment_type
947 ,p_multiple_status_flag => l_assignment_rec.multiple_status_flag
948 ,p_apprvl_status_code => l_next_status_code
949 ,p_staffing_priority_code => l_assignment_rec.staffing_priority_code
950 ,p_status_code => l_assignment_rec.status_code
951 ,p_project_id => l_assignment_rec.project_id
952 ,p_project_role_id => l_assignment_rec.project_role_id
953 ,p_resource_id => l_assignment_rec.resource_id
954 ,p_project_party_id => l_assignment_rec.project_party_id
955 ,p_description => l_assignment_rec.description
956 ,p_note_to_approver => l_assignment_rec.note_to_approver
957 ,p_start_date => l_assignment_rec.start_date
958 ,p_end_date => l_assignment_rec.end_date
959 ,p_assignment_effort => l_assignment_rec.assignment_effort
960 ,p_extension_possible => l_assignment_rec.extension_possible
961 ,p_source_assignment_id => l_assignment_rec.source_assignment_id
962 ,p_assignment_template_id => l_assignment_rec.assignment_template_id
963 ,p_min_resource_job_level => l_assignment_rec.min_resource_job_level
964 ,p_max_resource_job_level => l_assignment_rec.max_resource_job_level
965 ,p_assignment_number => l_assignment_rec.assignment_number
966 ,p_additional_information => l_assignment_rec.additional_information
967 ,p_work_type_id => l_assignment_rec.work_type_id
968 ,p_revenue_currency_code => l_assignment_rec.revenue_currency_code
969 ,p_revenue_bill_rate => l_assignment_rec.revenue_bill_rate
970 ,p_fcst_tp_amount_type => l_assignment_rec.fcst_tp_amount_type
971 ,p_fcst_job_id => l_assignment_rec.fcst_job_id
972 ,p_fcst_job_group_id => l_assignment_rec.fcst_job_group_id
973 ,p_expenditure_org_id => l_assignment_rec.expenditure_org_id
974 ,p_expenditure_organization_id => l_assignment_rec.expenditure_organization_id
975 ,p_expenditure_type_class => l_assignment_rec.expenditure_type_class
976 ,p_expenditure_type => l_assignment_rec.expenditure_type
977 ,p_expense_owner => l_assignment_rec.expense_owner
978 ,p_expense_limit => l_assignment_rec.expense_limit
979 ,p_expense_limit_currency_code => l_assignment_rec.expense_limit_currency_code
980 ,p_location_id => l_assignment_rec.location_id
981 ,p_calendar_type => l_assignment_rec.calendar_type
982 ,p_calendar_id => l_assignment_rec.calendar_id
983 ,p_resource_calendar_percent => l_assignment_rec.resource_calendar_percent
984 ,p_pending_approval_flag => 'N'
985 -- ,p_no_of_active_candidates => l_assignment_rec.no_of_active_candidates
986 ,p_comp_match_weighting => l_assignment_rec.comp_match_weighting
987 ,p_avail_match_weighting => l_assignment_rec.avail_match_weighting
988 ,p_job_level_match_weighting => l_assignment_rec.job_level_match_weighting
989 ,p_search_min_availability => l_assignment_rec.search_min_availability
990 ,p_search_country_code => l_assignment_rec.search_country_code
991 ,p_search_exp_org_struct_ver_id => l_assignment_rec.search_exp_org_struct_ver_id
992 ,p_search_exp_start_org_id => l_assignment_rec.search_exp_start_org_id
993 ,p_search_min_candidate_score => l_assignment_rec.search_min_candidate_score
994 -- ,p_last_auto_search_date => l_assignment_rec.last_auto_search_date
995 ,p_enable_auto_cand_nom_flag => l_assignment_rec.enable_auto_cand_nom_flag
996 -- ,p_mass_wf_in_progress_flag => l_assignment_rec.mass_wf_in_progress_flag
997 ,p_bill_rate_override => l_assignment_rec.bill_rate_override
998 ,p_bill_rate_curr_override => l_assignment_rec.bill_rate_curr_override
999 ,p_markup_percent_override => l_assignment_rec.markup_percent_override
1000 ,p_tp_rate_override => l_assignment_rec.tp_rate_override
1001 ,p_tp_currency_override => l_assignment_rec.tp_currency_override
1002 ,p_tp_calc_base_code_override => l_assignment_rec.tp_calc_base_code_override
1003 ,p_tp_percent_applied_override => l_assignment_rec.tp_percent_applied_override
1004 ,p_markup_percent => l_assignment_rec.markup_percent
1005 ,p_attribute_category => l_assignment_rec.attribute_category
1006 ,p_attribute1 => l_assignment_rec.attribute1
1007 ,p_attribute2 => l_assignment_rec.attribute2
1008 ,p_attribute3 => l_assignment_rec.attribute3
1009 ,p_attribute4 => l_assignment_rec.attribute4
1010 ,p_attribute5 => l_assignment_rec.attribute5
1011 ,p_attribute6 => l_assignment_rec.attribute6
1012 ,p_attribute7 => l_assignment_rec.attribute7
1013 ,p_attribute8 => l_assignment_rec.attribute8
1014 ,p_attribute9 => l_assignment_rec.attribute9
1015 ,p_attribute10 => l_assignment_rec.attribute10
1016 ,p_attribute11 => l_assignment_rec.attribute11
1017 ,p_attribute12 => l_assignment_rec.attribute12
1018 ,p_attribute13 => l_assignment_rec.attribute13
1019 ,p_attribute14 => l_assignment_rec.attribute14
1020 ,p_attribute15 => l_assignment_rec.attribute15
1021 ,x_return_status => l_return_status);
1022
1023 --Log Message
1024 IF P_DEBUG_MODE = 'Y' THEN
1025 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENT_APPROVAL_PUB.Revert_To_Last_Approved.revert_schedule'
1026 ,x_msg => 'Reverting Schedule.'
1027 ,x_log_level => 5);
1028 END IF;
1029 --
1030 --Revert the schedule records also.
1031 --
1032 PA_SCHEDULE_PVT.Revert_To_Last_Approved ( p_assignment_id => p_assignment_id
1033 ,p_change_id => l_change_id
1034 ,x_return_status => l_return_status
1035 ,x_msg_count => l_msg_count
1036 ,x_msg_data => l_msg_data);
1037 --Log Message
1038 IF P_DEBUG_MODE = 'Y' THEN
1039 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENT_APPROVAL_PUB.Revert_To_Last_Approved.revert_subteam'
1040 ,x_msg => 'Reverting Subteam.'
1041 ,x_log_level => 5);
1042 END IF;
1043
1044 --
1045 --Revert the Project Subteam
1046 --
1047 PA_PROJECT_SUBTEAM_PARTIES_PVT.Update_SPT_Assgn
1048 ( p_validate_only => p_validate_only
1049 ,p_get_subteam_party_id_flag => 'Y'
1050 ,p_project_subteam_id => l_project_subteam_id
1051 ,p_object_type => 'PA_PROJECT_ASSIGNMENTS'
1052 ,p_object_id => p_assignment_id
1053 ,x_project_subteam_party_id => l_project_subteam_party_id
1054 ,x_return_status => l_return_status
1055 ,x_record_version_number => l_record_version_number
1056 ,x_msg_count => l_msg_count
1057 ,x_msg_data => l_msg_data
1058 );
1059
1060 END IF; -- end of update row and revert
1061
1062
1063 END IF; --end of checking approval pending on the current record
1064 --
1065 --Take care the out parameters
1066 --
1067 --
1068 -- IF the number of messaages is 1 then fetch the message code from the stack and return its text
1069 --
1070
1071 x_msg_count := FND_MSG_PUB.Count_Msg;
1072
1073 IF x_msg_count = 1 THEN
1074 pa_interface_utils_pub.get_messages ( p_encoded => FND_API.G_TRUE
1075 ,p_msg_index => 1
1076 ,p_data => x_msg_data
1077 ,p_msg_index_out => l_msg_index_out
1078 );
1079 END IF;
1080
1081 -- Reset the error stack when returning to the calling program
1082
1083 PA_DEBUG.Reset_Err_Stack;
1084
1085 -- If g_error_exists is TRUE then set the x_return_status to 'E'
1086
1087 IF FND_MSG_PUB.Count_Msg >0 THEN
1088
1089 x_return_status := FND_API.G_RET_STS_ERROR;
1090
1091 END IF;
1092
1093
1094 EXCEPTION
1095 WHEN OTHERS THEN
1096 IF p_commit = FND_API.G_TRUE THEN
1097 ROLLBACK TO ASG_APR_PUB_REVERT;
1098 END IF;
1099
1100 -- Set the exception Message and the stack
1101 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_ASSIGNMENT_APPROVAL_PUB.Revert_To_Last_Approved'
1102 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
1103 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1104
1105 RAISE; -- This is optional depending on the needs
1106
1107 END Revert_To_Last_Approved;
1108
1109
1110
1111
1112 --
1113 -- This procedure populates the PA_ASGMT_CHANGED_ITEMS table with changes on the assignment if the
1114 -- assignment has been previously approved (i.e. not a new assignment). It compares the record with
1115 -- the last approved record, and stores those changed fields and their old and new values in the table.
1116 -- Currently, this api is called by Single/Mass Submit for Approval
1117 --
1118 PROCEDURE Populate_Changed_Items_Table
1119 ( p_assignment_id IN pa_project_assignments.assignment_id%TYPE
1120 ,p_populate_mode IN VARCHAR2 := 'SAVED'
1121 ,p_assignment_name IN pa_project_assignments.assignment_name%TYPE := FND_API.G_MISS_CHAR
1122 ,p_project_id IN pa_project_assignments.project_id%TYPE := FND_API.G_MISS_NUM
1123 ,p_staffing_priority_code IN pa_project_assignments.staffing_priority_code%TYPE := FND_API.G_MISS_CHAR
1124 ,p_description IN pa_project_assignments.description%TYPE := FND_API.G_MISS_CHAR
1125 ,p_extension_possible IN pa_project_assignments.extension_possible%TYPE := FND_API.G_MISS_CHAR
1126 ,p_additional_information IN pa_project_assignments.additional_information%TYPE := FND_API.G_MISS_CHAR
1127 ,p_work_type_id IN pa_project_assignments.work_type_id%TYPE := FND_API.G_MISS_NUM
1128 ,p_expense_owner IN pa_project_assignments.expense_owner%TYPE := FND_API.G_MISS_CHAR
1129 ,p_expense_limit IN pa_project_assignments.expense_limit%TYPE := FND_API.G_MISS_NUM
1130 ,p_fcst_tp_amount_type IN pa_project_assignments.fcst_tp_amount_type%TYPE := FND_API.G_MISS_CHAR
1131 ,p_expenditure_type_class IN pa_project_assignments.expenditure_type_class%TYPE := FND_API.G_MISS_CHAR
1132 ,p_expenditure_type IN pa_project_assignments.expenditure_type%TYPE := FND_API.G_MISS_CHAR
1133 ,p_location_id IN pa_project_assignments.location_id%TYPE := FND_API.G_MISS_NUM
1134 ,p_staffing_owner_person_id IN pa_project_assignments.staffing_owner_person_id%TYPE := FND_API.G_MISS_NUM
1135 ,p_staffing_owner_name IN per_people_f.full_name%TYPE := FND_API.G_MISS_CHAR
1136 ,p_exception_type_code IN VARCHAR2 := NULL
1137 ,p_start_date IN DATE := NULL
1138 ,p_end_date IN DATE := NULL
1139 ,p_requirement_status_code IN VARCHAR2 := NULL
1140 ,p_assignment_status_code IN VARCHAR2 := NULL
1141 ,p_start_date_tbl IN SYSTEM.PA_DATE_TBL_TYPE := NULL
1142 ,p_end_date_tbl IN SYSTEM.PA_DATE_TBL_TYPE := NULL
1143 ,p_monday_hours_tbl IN SYSTEM.PA_NUM_TBL_TYPE := NULL
1144 ,p_tuesday_hours_tbl IN SYSTEM.PA_NUM_TBL_TYPE := NULL
1145 ,p_wednesday_hours_tbl IN SYSTEM.PA_NUM_TBL_TYPE := NULL
1146 ,p_thursday_hours_tbl IN SYSTEM.PA_NUM_TBL_TYPE := NULL
1147 ,p_friday_hours_tbl IN SYSTEM.PA_NUM_TBL_TYPE := NULL
1148 ,p_saturday_hours_tbl IN SYSTEM.PA_NUM_TBL_TYPE := NULL
1149 ,p_sunday_hours_tbl IN SYSTEM.PA_NUM_TBL_TYPE := NULL
1150 ,p_non_working_day_flag IN VARCHAR2 := 'N'
1151 ,p_change_hours_type_code IN VARCHAR2 := NULL
1152 ,p_hrs_per_day IN NUMBER := NULL
1153 ,p_calendar_percent IN NUMBER := NULL
1154 ,p_change_calendar_type_code IN VARCHAR2 := NULL
1155 ,p_change_calendar_name IN VARCHAR2 := NULL
1156 ,p_change_calendar_id IN NUMBER := NULL
1157 ,p_duration_shift_type_code IN VARCHAR2 := NULL
1158 ,p_duration_shift_unit_code IN VARCHAR2 := NULL
1159 ,p_number_of_shift IN NUMBER := NULL
1160 ,p_api_version IN NUMBER := 1.0
1161 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1162 ,p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM
1163 ,x_new_assignment_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1164 ,x_approval_required_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1165 ,x_record_version_number OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1166 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1167 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1168 ,x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
1169 IS
1170 l_new_value pa_lookups.meaning%TYPE;
1171 l_old_value pa_lookups.meaning%TYPE;
1172 l_new_city pa_locations.city%TYPE;
1173 l_new_region pa_locations.region%TYPE;
1174 l_new_country_code pa_locations.country_code%TYPE;
1175 l_old_city pa_locations.city%TYPE;
1176 l_old_region pa_locations.region%TYPE;
1177 l_old_country_code pa_locations.country_code%TYPE;
1178 l_new_work_type pa_work_types_vl.name%TYPE;
1179 l_old_work_type pa_work_types_vl.name%TYPE;
1180 l_new_subteam_name pa_project_subteams.name%TYPE;
1181 l_old_subteam_name pa_project_subteams.name%TYPE;
1182 l_project_subteam_id pa_project_subteam_parties.project_subteam_id%TYPE;
1183 l_his_project_subteam_id pa_assignments_history.project_subteam_id%TYPE;
1184 l_msg_index_out NUMBER;
1185 l_change_id NUMBER;
1186 l_return_status VARCHAR2(1);
1187 l_msg_count NUMBER;
1188 l_msg_data VARCHAR2(2000);
1189 l_changed_item pa_lookups.meaning%TYPE;
1190 l_asmt_history_rec pa_assignments_pub.assignment_rec_type;
1191 l_saved_asmt_rec pa_assignments_pub.assignment_rec_type;
1192 l_updated_asmt_rec pa_assignments_pub.assignment_rec_type;
1193
1194 CURSOR get_rec_ver_num IS
1195 SELECT record_version_number
1196 FROM pa_project_assignments
1197 WHERE assignment_id = p_assignment_id;
1198
1199 CURSOR get_locations (new_location_id NUMBER, old_location_id NUMBER) IS
1200 SELECT location_id, city, region, country_code
1201 FROM pa_locations
1202 WHERE location_id in (new_location_id, old_location_id);
1203
1204 CURSOR get_project_subteam_names (new_project_subteam_id NUMBER, old_project_subteam_id NUMBER)IS
1205 SELECT project_subteam_id, name
1206 FROM pa_project_subteams
1207 WHERE project_subteam_id in (new_project_subteam_id, old_project_subteam_id);
1208
1209 CURSOR get_project_subteam_id IS
1210 SELECT project_subteam_id
1211 FROM pa_project_subteam_parties
1212 WHERE object_type = 'PA_PROJECT_ASSIGNMENTS'
1213 AND object_id = p_assignment_id
1214 AND primary_subteam_flag = 'Y';
1215
1216 CURSOR get_his_project_subteam_id IS
1217 SELECT project_subteam_id
1218 FROM pa_assignments_history
1219 WHERE assignment_id = p_assignment_id
1220 AND last_approved_flag = 'Y';
1221
1222 CURSOR get_work_type_names (new_work_type_id NUMBER, old_work_type_id NUMBER) IS
1223 SELECT work_type_id, name
1224 FROM pa_work_types_vl
1225 WHERE work_type_id in (new_work_type_id, old_work_type_id);
1226
1227 BEGIN
1228 --Issue rollback to clean up the Global Temporary Table PA_ASGMT_CHANGED_ITEMS
1229 ROLLBACK;
1230
1231 -- Initialize the Error Stack
1232 PA_DEBUG.init_err_stack('PA_ASSIGNMENT_APPROVAL_PUB.Populate_Changed_Items_Table');
1233
1234 -- Initialize the return status to success
1235 x_return_status := FND_API.G_RET_STS_SUCCESS;
1236
1237 -- Clear the global PL/SQL message table
1238 IF FND_API.TO_BOOLEAN( p_init_msg_list ) THEN
1239 FND_MSG_PUB.initialize;
1240 END IF;
1241
1242 -- set out parameter
1243 OPEN get_rec_ver_num;
1244 FETCH get_rec_ver_num INTO x_record_version_number;
1245 CLOSE get_rec_ver_num;
1246
1247 ---------------------------------------------------------------------------
1248 -- Check if it is a New Assignemnt
1249 ---------------------------------------------------------------------------
1250 x_new_assignment_flag := PA_ASSIGNMENT_APPROVAL_PVT.Is_New_Assignment(p_assignment_id => p_assignment_id);
1251
1252 -- Check if approval is required to pass the out parameter 'x_approval_required_flag'
1253 PA_ASSIGNMENT_APPROVAL_PVT.Check_Approval_Required( p_assignment_id => p_assignment_id
1254 ,p_new_assignment_flag => x_new_assignment_flag
1255 ,x_approval_required_flag => x_approval_required_flag
1256 ,x_return_status => l_return_status);
1257
1258 ---------------------------------------------------------------------------
1259 -- If it is a New Assignemnt => just return
1260 ---------------------------------------------------------------------------
1261 IF x_new_assignment_flag = 'Y' THEN
1262 return;
1263 END IF;
1264
1265 ---------------------------------------------------------------------------
1266 -- Generate history_asmt_rec and latest updated_asmt_rec
1267 ---------------------------------------------------------------------------
1268 PA_ASSIGNMENT_APPROVAL_PVT.get_asmt_and_asmt_history_rec
1269 (p_assignment_id => p_assignment_id
1270 ,x_saved_asmt_rec => l_saved_asmt_rec
1271 ,x_asmt_history_rec => l_asmt_history_rec
1272 ,x_return_status => l_return_status);
1273
1274 l_updated_asmt_rec := l_saved_asmt_rec;
1275
1276 -- if this api is called by drill down from Mass Update Assignemnt Basic Info/Forecast, we need to
1277 -- consider the passed(unsaved) values for latest values. Otherwise the latest values would be
1278 -- in the db already.
1279 IF (p_populate_mode = 'ASSIGNMENT_UPDATED') THEN
1280 -- get the latest updated_asmt_rec based on db saved rec and the passed(unsaved) values, we will use
1281 -- the latest updated_asmt_rec and l_asmt_history_rec to get the changed items.
1282 SELECT DECODE(p_assignment_id, null, l_saved_asmt_rec.assignment_id, p_assignment_id),
1283 DECODE(p_assignment_name, null, l_saved_asmt_rec.assignment_name, p_assignment_name),
1284 DECODE(p_staffing_priority_code, null, l_saved_asmt_rec.staffing_priority_code, p_staffing_priority_code),
1285 DECODE(p_description, null, l_saved_asmt_rec.description, p_description),
1286 DECODE(p_extension_possible, null, l_saved_asmt_rec.extension_possible, p_extension_possible),
1287 DECODE(p_additional_information, null, l_saved_asmt_rec.additional_information, p_additional_information),
1288 DECODE(p_work_type_id, null, l_saved_asmt_rec.work_type_id, p_work_type_id),
1289 DECODE(p_expense_owner, null, l_saved_asmt_rec.expense_owner, p_expense_owner),
1290 DECODE(p_expense_limit, null, l_saved_asmt_rec.expense_limit, p_expense_limit),
1291 DECODE(p_fcst_tp_amount_type, null, l_saved_asmt_rec.fcst_tp_amount_type, p_fcst_tp_amount_type),
1292 DECODE(p_expenditure_type_class, null, l_saved_asmt_rec.expenditure_type_class, p_expenditure_type_class),
1293 DECODE(p_expenditure_type, null, l_saved_asmt_rec.expenditure_type, p_expenditure_type),
1294 DECODE(p_location_id, null, l_saved_asmt_rec.location_id, p_location_id),
1295 DECODE(p_staffing_owner_person_id, null, l_saved_asmt_rec.staffing_owner_person_id, p_staffing_owner_person_id)
1296 INTO l_updated_asmt_rec.assignment_id,
1297 l_updated_asmt_rec.assignment_name,
1298 l_updated_asmt_rec.staffing_priority_code,
1299 l_updated_asmt_rec.description,
1300 l_updated_asmt_rec.extension_possible,
1301 l_updated_asmt_rec.additional_information,
1302 l_updated_asmt_rec.work_type_id,
1303 l_updated_asmt_rec.expense_owner,
1304 l_updated_asmt_rec.expense_limit,
1305 l_updated_asmt_rec.fcst_tp_amount_type,
1306 l_updated_asmt_rec.expenditure_type_class,
1307 l_updated_asmt_rec.expenditure_type,
1308 l_updated_asmt_rec.location_id,
1309 l_updated_asmt_rec.staffing_owner_person_id
1310 FROM DUAL;
1311 END IF;
1312
1313 ------------------------------------------------------------------------------
1314 -- Populate Table for the updated assignment items
1315 ------------------------------------------------------------------------------
1316
1317 -- Compare Additional Staffing Information
1318 l_changed_item := PA_ASSIGNMENT_APPROVAL_PVT.get_lookup_meaning('PA_CHANGED_ITEMS', 'ADDITIONAL_STAFF_INFO');
1319 IF (NVL(l_asmt_history_rec.additional_information,-1) <> NVL(l_updated_asmt_rec.additional_information,-1)) THEN
1320 INSERT INTO pa_asgmt_changed_items (assignment_id, changed_item_name, new_value, old_value)
1321 VALUES (p_assignment_id,l_changed_item,l_updated_asmt_rec.additional_information,l_asmt_history_rec.additional_information);
1322 END IF;
1323
1324 -- Compare Expenditure Type
1325 l_changed_item := PA_ASSIGNMENT_APPROVAL_PVT.get_lookup_meaning('PA_CHANGED_ITEMS', 'ASMT_EXP_TYPE');
1326 IF (NVL(l_asmt_history_rec.expenditure_type,-1) <> NVL(l_updated_asmt_rec.expenditure_type,-1)) THEN
1327 INSERT INTO pa_asgmt_changed_items (assignment_id, changed_item_name, new_value, old_value)
1328 VALUES (p_assignment_id, l_changed_item, l_updated_asmt_rec.expenditure_type, l_asmt_history_rec.expenditure_type);
1329 END IF;
1330
1331 -- Compare Assignment Name
1332 l_changed_item := PA_ASSIGNMENT_APPROVAL_PVT.get_lookup_meaning('PA_CHANGED_ITEMS', 'ASSIGNMENT_NAME');
1333 IF (NVL(l_asmt_history_rec.assignment_name,-1) <> NVL(l_updated_asmt_rec.assignment_name,-1)) THEN
1334 INSERT INTO pa_asgmt_changed_items (assignment_id, changed_item_name, new_value, old_value)
1335 VALUES (p_assignment_id, l_changed_item, l_updated_asmt_rec.assignment_name, l_asmt_history_rec.assignment_name);
1336 END IF;
1337
1338 -- Compare Description
1339 l_changed_item := PA_ASSIGNMENT_APPROVAL_PVT.get_lookup_meaning('PA_CHANGED_ITEMS', 'DESCRIPTION');
1340 IF (NVL(l_asmt_history_rec.description,-1) <> NVL(l_updated_asmt_rec.description,-1)) THEN
1341 INSERT INTO pa_asgmt_changed_items (assignment_id, changed_item_name, new_value, old_value)
1342 VALUES (p_assignment_id, l_changed_item, l_updated_asmt_rec.description, l_asmt_history_rec.description);
1343 END IF;
1344
1345 -- Compare Expense Limit
1346 l_changed_item := PA_ASSIGNMENT_APPROVAL_PVT.get_lookup_meaning('PA_CHANGED_ITEMS', 'EXPENSE_LIMIT');
1347 IF (NVL(l_asmt_history_rec.expense_limit,-1) <> NVL(l_updated_asmt_rec.expense_limit,-1)) THEN
1348 INSERT INTO pa_asgmt_changed_items (assignment_id, changed_item_name, new_value, old_value)
1349 VALUES (p_assignment_id, l_changed_item, to_char(l_updated_asmt_rec.expense_limit),
1350 to_char(l_asmt_history_rec.expense_limit));
1351 END IF;
1352
1353 -- Compare Expense Owner
1354 l_changed_item := PA_ASSIGNMENT_APPROVAL_PVT.get_lookup_meaning('PA_CHANGED_ITEMS', 'EXPENSE_OWNER');
1355 IF (NVL(l_asmt_history_rec.expense_owner,-1) <> NVL(l_updated_asmt_rec.expense_owner,-1)) THEN
1356 l_new_value := PA_ASSIGNMENT_APPROVAL_PVT.get_lookup_meaning('EXPENSE_OWNER_TYPE', l_updated_asmt_rec.expense_owner);
1357 l_old_value := PA_ASSIGNMENT_APPROVAL_PVT.get_lookup_meaning('EXPENSE_OWNER_TYPE', l_asmt_history_rec.expense_owner);
1358
1359 INSERT INTO pa_asgmt_changed_items (assignment_id, changed_item_name, new_value, old_value)
1360 VALUES (p_assignment_id, l_changed_item, l_new_value, l_old_value);
1361 END IF;
1362
1363 -- Compare Extension Possible
1364 l_changed_item := PA_ASSIGNMENT_APPROVAL_PVT.get_lookup_meaning('PA_CHANGED_ITEMS', 'EXTENSION_POSSIBLE');
1365 IF (NVL(l_asmt_history_rec.extension_possible,-1) <> NVL(l_updated_asmt_rec.extension_possible,-1)) THEN
1366 INSERT INTO pa_asgmt_changed_items (assignment_id, changed_item_name, new_value, old_value)
1367 VALUES (p_assignment_id, l_changed_item, l_updated_asmt_rec.extension_possible, l_asmt_history_rec.extension_possible);
1368 END IF;
1369
1370 -- Compare Location
1371 l_changed_item := PA_ASSIGNMENT_APPROVAL_PVT.get_lookup_meaning('PA_CHANGED_ITEMS', 'LOCATION');
1372 IF (NVL(l_asmt_history_rec.location_id,-1) <> NVL(l_updated_asmt_rec.location_id,-1)) THEN
1373 FOR c2 IN get_locations(l_asmt_history_rec.location_id, l_updated_asmt_rec.location_id) LOOP
1374 IF c2.location_id = l_updated_asmt_rec.location_id THEN
1375 l_new_city := c2.city;
1376 l_new_region := c2.region;
1377 l_new_country_code := c2.country_code;
1378 ELSIF c2.location_id = l_asmt_history_rec.location_id THEN
1379 l_old_city := c2.city;
1380 l_old_region := c2.region;
1381 l_old_country_code := c2.country_code;
1382 END IF;
1383 END LOOP;
1384
1385 INSERT INTO pa_asgmt_changed_items (assignment_id, changed_item_name, new_value, old_value)
1386 VALUES (p_assignment_id, l_changed_item, l_new_city || ', ' || l_new_region || ', ' || l_new_country_code,
1387 l_old_city||', ' || l_old_region || ', ' || l_old_country_code);
1388 END IF;
1389
1390 -- Compare Staffing Priority
1391 l_changed_item := PA_ASSIGNMENT_APPROVAL_PVT.get_lookup_meaning('PA_CHANGED_ITEMS', 'STAFFING_PRIORITY');
1392 IF (NVL(l_asmt_history_rec.staffing_priority_code,-1) <> NVL(l_updated_asmt_rec.staffing_priority_code,-1)) THEN
1393 l_new_value := PA_ASSIGNMENT_APPROVAL_PVT.get_lookup_meaning('STAFFING_PRIORITY_CODE', l_updated_asmt_rec.staffing_priority_code);
1394 l_old_value := PA_ASSIGNMENT_APPROVAL_PVT.get_lookup_meaning('STAFFING_PRIORITY_CODE', l_asmt_history_rec.staffing_priority_code);
1395
1396 INSERT INTO pa_asgmt_changed_items (assignment_id, changed_item_name, new_value, old_value)
1397 VALUES (p_assignment_id, l_changed_item, l_new_value, l_old_value);
1398 END IF;
1399
1400 -- Compare project Subteam
1401 l_changed_item := PA_ASSIGNMENT_APPROVAL_PVT.get_lookup_meaning('PA_CHANGED_ITEMS', 'SUB_TEAM');
1402
1403 -- get project_subteam_id from pa_project_subteam_parties
1404 OPEN get_project_subteam_id;
1405 FETCH get_project_subteam_id INTO l_project_subteam_id;
1406 IF get_project_subteam_id%NOTFOUND THEN
1407 l_project_subteam_id := NULL;
1408 END IF;
1409 CLOSE get_project_subteam_id;
1410
1411 -- get project_subteam_id from pa_assignments_history, it history table doesn't have a record
1412 -- for this assignment, pass current project_subteam_id.
1413 OPEN get_his_project_subteam_id;
1414 FETCH get_his_project_subteam_id INTO l_his_project_subteam_id;
1415 IF get_his_project_subteam_id%NOTFOUND THEN
1416 l_his_project_subteam_id := l_project_subteam_id;
1417 END IF;
1418 CLOSE get_his_project_subteam_id;
1419
1420 IF (NVL(l_project_subteam_id,-1) <> NVL(l_his_project_subteam_id,-1)) THEN
1421 FOR c2 IN get_project_subteam_names(l_project_subteam_id, l_his_project_subteam_id) LOOP
1422 IF c2.project_subteam_id = l_project_subteam_id THEN
1423 l_new_subteam_name := c2.name;
1424 ELSIF c2.project_subteam_id = l_his_project_subteam_id THEN
1425 l_old_subteam_name := c2.name;
1426 END IF;
1427 END LOOP;
1428
1429 INSERT INTO pa_asgmt_changed_items (assignment_id, changed_item_name, new_value, old_value)
1430 VALUES (p_assignment_id, l_changed_item, l_new_subteam_name, l_old_subteam_name);
1431 END IF;
1432
1433 -- Compare Staffing Owner
1434 l_changed_item := PA_ASSIGNMENT_APPROVAL_PVT.get_lookup_meaning('PA_CHANGED_ITEMS', 'STAFFING_OWNER');
1435
1436 IF (NVL(l_asmt_history_rec.staffing_owner_person_id,-1) <> NVL(l_updated_asmt_rec.staffing_owner_person_id,-1)) THEN
1437 -- If p_staffing_owner_person_id=null and p_staffing_owner_name<>null(when user just type name instead of
1438 -- using LOV in Mass Update page), l_updated_asmt_rec.staffing_owner_person_id won't have the latest
1439 -- value. Because p_staffing_owner_person_id=null, we can get the latest value from p_staffing_owner_name
1440 IF p_staffing_owner_name IS NOT NULL THEN
1441 l_new_value := p_staffing_owner_name;
1442 ELSE
1443 pa_resource_utils.get_person_name (p_person_id => l_updated_asmt_rec.staffing_owner_person_id,
1444 x_person_name => l_new_value,
1445 x_return_status => l_return_Status);
1446 END IF;
1447
1448 pa_resource_utils.get_person_name (p_person_id => l_asmt_history_rec.staffing_owner_person_id,
1449 x_person_name => l_old_value,
1450 x_return_status => l_return_Status);
1451
1452 INSERT INTO pa_asgmt_changed_items (assignment_id, changed_item_name, new_value, old_value)
1453 VALUES (p_assignment_id, l_changed_item, l_new_value, l_old_value);
1454 END IF;
1455
1456 -- Compare TP Amount Type
1457 l_changed_item := PA_ASSIGNMENT_APPROVAL_PVT.get_lookup_meaning('PA_CHANGED_ITEMS', 'TRANSFER_AMT_TYPE');
1458
1459 IF (NVL(l_asmt_history_rec.fcst_tp_amount_type,-1) <> NVL(l_updated_asmt_rec.fcst_tp_amount_type,-1)) THEN
1460 l_new_value := PA_ASSIGNMENT_APPROVAL_PVT.get_lookup_meaning('TP_AMOUNT_TYPE', l_updated_asmt_rec.fcst_tp_amount_type);
1461 l_old_value := PA_ASSIGNMENT_APPROVAL_PVT.get_lookup_meaning('TP_AMOUNT_TYPE', l_asmt_history_rec.fcst_tp_amount_type);
1462
1463 INSERT INTO pa_asgmt_changed_items (assignment_id, changed_item_name, new_value, old_value)
1464 VALUES (p_assignment_id, l_changed_item, l_new_value, l_old_value);
1465 END IF;
1466
1467 -- Compare Work Type
1468 l_changed_item := PA_ASSIGNMENT_APPROVAL_PVT.get_lookup_meaning('PA_CHANGED_ITEMS', 'WORK_TYPE');
1469
1470 IF (NVL(l_asmt_history_rec.work_type_id,-1) <> NVL(l_updated_asmt_rec.work_type_id,-1)) THEN
1471 FOR c2 IN get_work_type_names(l_asmt_history_rec.work_type_id, l_updated_asmt_rec.work_type_id) LOOP
1472 IF c2.work_type_id = l_updated_asmt_rec.work_type_id THEN
1473 l_new_work_type := c2.name;
1474 ELSIF c2.work_type_id = l_asmt_history_rec.work_type_id THEN
1475 l_old_work_type := c2.name;
1476 END IF;
1477 END LOOP;
1478
1479 INSERT INTO pa_asgmt_changed_items (assignment_id, changed_item_name, new_value, old_value)
1480 VALUES (p_assignment_id, l_changed_item, l_new_work_type, l_old_work_type);
1481 END IF;
1482
1483 -- Compare Transfer Price Currency Override
1484 l_changed_item := PA_ASSIGNMENT_APPROVAL_PVT.get_lookup_meaning('PA_CHANGED_ITEMS', 'TP_CURRENCY_OVERRIDE');
1485
1486 IF (NVL(l_asmt_history_rec.tp_currency_override,-1) <> NVL(l_updated_asmt_rec.tp_currency_override,-1)) THEN
1487 INSERT INTO pa_asgmt_changed_items (assignment_id, changed_item_name, new_value, old_value)
1488 VALUES (p_assignment_id, l_changed_item, l_updated_asmt_rec.tp_currency_override,
1489 l_asmt_history_rec.tp_currency_override);
1490 END IF;
1491
1492 -- Compare Transfer Price Rate Override
1493 l_changed_item := PA_ASSIGNMENT_APPROVAL_PVT.get_lookup_meaning('PA_CHANGED_ITEMS', 'TP_RATE_OVERRIDE');
1494
1495 IF (NVL(l_asmt_history_rec.tp_rate_override,-1) <> NVL(l_updated_asmt_rec.tp_rate_override,-1)) THEN
1496 INSERT INTO pa_asgmt_changed_items (assignment_id, changed_item_name, new_value, old_value)
1497 VALUES (p_assignment_id, l_changed_item, to_char(l_updated_asmt_rec.tp_rate_override),
1498 to_char(l_asmt_history_rec.tp_rate_override));
1499 END IF;
1500
1501 -- Compare Transfer Price Basis Override
1502 l_changed_item := PA_ASSIGNMENT_APPROVAL_PVT.get_lookup_meaning('PA_CHANGED_ITEMS', 'TP_CALC_BASE_CODE_OVERRIDE');
1503
1504 IF (NVL(l_asmt_history_rec.tp_calc_base_code_override,-1) <> NVL(l_updated_asmt_rec.tp_calc_base_code_override,-1)) THEN
1505 INSERT INTO pa_asgmt_changed_items (assignment_id, changed_item_name, new_value, old_value)
1506 VALUES (p_assignment_id, l_changed_item, l_updated_asmt_rec.tp_calc_base_code_override,
1507 l_asmt_history_rec.tp_calc_base_code_override);
1508 END IF;
1509
1510 -- Compare Transfer Price Apply % Override
1511 l_changed_item := PA_ASSIGNMENT_APPROVAL_PVT.get_lookup_meaning('PA_CHANGED_ITEMS', 'TP_PERCENT_APPLIED_OVERRIDE');
1512
1513 IF (NVL(l_asmt_history_rec.tp_percent_applied_override,-1) <> NVL(l_updated_asmt_rec.tp_percent_applied_override,-1)) THEN
1514 INSERT INTO pa_asgmt_changed_items (assignment_id, changed_item_name, new_value, old_value)
1515 VALUES (p_assignment_id, l_changed_item, to_char(l_updated_asmt_rec.tp_percent_applied_override),
1516 to_char(l_asmt_history_rec.tp_percent_applied_override));
1517 END IF;
1518
1519
1520 ---------------------------------------------------------------------------
1521 -- Populate Table for the updated schedule items
1522 ---------------------------------------------------------------------------
1523 -- get change_id just to check, it will pass -1 if the history table has no record for this asmt
1524 l_change_id := PA_ASSIGNMENT_APPROVAL_PVT.Get_Change_Id (p_assignment_id);
1525
1526 PA_SCHEDULE_PVT.update_asgmt_changed_items_tab (
1527 p_assignment_id => p_assignment_id
1528 ,p_populate_mode => p_populate_mode
1529 ,p_change_id => l_change_id
1530 ,p_exception_type_code => p_exception_type_code
1531 ,p_start_date => p_start_date
1532 ,p_end_date => p_end_date
1533 ,p_requirement_status_code => p_requirement_status_code
1534 ,p_assignment_status_code => p_assignment_status_code
1535 ,p_start_date_tbl => p_start_date_tbl
1536 ,p_end_date_tbl => p_end_date_tbl
1537 ,p_monday_hours_tbl => p_monday_hours_tbl
1538 ,p_tuesday_hours_tbl => p_tuesday_hours_tbl
1539 ,p_wednesday_hours_tbl => p_wednesday_hours_tbl
1540 ,p_thursday_hours_tbl => p_thursday_hours_tbl
1541 ,p_friday_hours_tbl => p_friday_hours_tbl
1542 ,p_saturday_hours_tbl => p_saturday_hours_tbl
1543 ,p_sunday_hours_tbl => p_sunday_hours_tbl
1544 ,p_non_working_day_flag => p_non_working_day_flag
1545 ,p_change_hours_type_code => p_change_hours_type_code
1546 ,p_hrs_per_day => p_hrs_per_day
1547 ,p_calendar_percent => p_calendar_percent
1548 ,p_change_calendar_type_code => p_change_calendar_type_code
1549 ,p_change_calendar_name => p_change_calendar_name
1550 ,p_change_calendar_id => p_change_calendar_id
1551 ,p_duration_shift_type_code => p_duration_shift_type_code
1552 ,p_duration_shift_unit_code => p_duration_shift_unit_code
1553 ,p_number_of_shift => p_number_of_shift
1554 ,x_return_status => l_return_status );
1555
1556
1557 ---------------------------------------------------------------------------
1558 -- Set out parameters
1559 ---------------------------------------------------------------------------
1560 x_msg_count := FND_MSG_PUB.Count_Msg;
1561
1562 -- If g_error_exists is TRUE then set the x_return_status to 'E'
1563 IF x_msg_count > 0 THEN
1564 x_return_status := FND_API.G_RET_STS_ERROR;
1565
1566 IF x_msg_count = 1 THEN
1567 pa_interface_utils_pub.get_messages ( p_encoded => FND_API.G_TRUE
1568 ,p_msg_index => 1
1569 ,p_data => x_msg_data
1570 ,p_msg_index_out => l_msg_index_out );
1571 END IF;
1572 END IF;
1573
1574 -- Reset the error stack when returning to the calling program
1575 PA_DEBUG.Reset_Err_Stack;
1576
1577 EXCEPTION
1578 WHEN OTHERS THEN
1579 -- Set the exception Message and the stack
1580 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_ASSIGNMENT_APPROVAL_PUB.Populate_Changed_Items_Table'
1581 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
1582 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1583 RAISE; -- This is optional depending on the needs
1584
1585 END Populate_Changed_Items_Table;
1586
1587
1588
1589
1590 PROCEDURE Change_Assignment_Status
1591 (
1592 p_record_version_number IN Number ,
1593 p_assignment_id IN Number ,
1594 p_assignment_type IN Varchar2 ,
1595 p_start_date IN date ,
1596 p_end_date IN date ,
1597 p_assignment_status_code IN Varchar2 := FND_API.G_MISS_CHAR,
1598 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1599 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1600 x_return_status OUT NOCOPY Varchar2 , --File.Sql.39 bug 4440895
1601 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
1602 x_msg_data OUT NOCOPY Varchar2 ) --File.Sql.39 bug 4440895
1603 IS
1604 l_msg_index_out NUMBER;
1605 l_assignment_row_id ROWID;
1606 l_assignment_number pa_project_assignments.assignment_number%TYPE;
1607 l_project_id pa_project_assignments.project_id%TYPE;
1608 l_calendar_id pa_project_assignments.calendar_id%TYPE;
1609 l_asgn_end_date pa_project_assignments.end_date%TYPE;
1610 l_asgn_start_date pa_project_assignments.start_date%TYPE;
1611 l_source_assignment_id pa_project_assignments.source_assignment_id%TYPE;
1612 l_record_version_number pa_project_assignments.record_version_number%TYPE;
1613 l_asgn_status_canceled_flag VARCHAR2(1);
1614 l_req_status_canceled_flag VARCHAR2(1);
1615 l_full_cancel_flag VARCHAR2(1);
1616 l_start_req_status_code pa_project_statuses.project_status_code%TYPE;
1617 l_new_assignment_flag VARCHAR2(1);
1618 l_change_id NUMBER;
1619 l_apprvl_status_code pa_project_statuses.project_status_code%TYPE;
1620 l_save_to_hist VARCHAR2(1);
1621 l_new_assignment_id NUMBER;
1622 l_assignment_type pa_project_assignments.assignment_type%TYPE;
1623 l_req_start_date pa_project_assignments.start_date%TYPE;
1624 l_project_party_id pa_project_assignments.project_party_id%TYPE;
1625 l_error_message_code fnd_new_messages.message_name%TYPE;
1626 l_resource_id pa_project_assignments.resource_id%TYPE;
1627 l_conflict_group_id NUMBER;
1628 l_overcommitment_flag VARCHAR2(1);
1629 l_action_set_id NUMBER;
1630 l_return_status VARCHAR2(1);
1631
1632 l_task_assignment_id_tbl system.pa_num_tbl_type;
1633 l_task_version_id_tbl system.pa_num_tbl_type := system.pa_num_tbl_type();
1634 l_budget_version_id_tbl system.pa_num_tbl_type := system.pa_num_tbl_type();
1635 l_struct_version_id_tbl system.pa_num_tbl_type := system.pa_num_tbl_type();
1636 l_cur_role_flag pa_res_formats_b.role_enabled_flag%TYPE;
1637
1638
1639 CURSOR check_record_version IS
1640 SELECT ROWID, project_id, calendar_id, start_date, end_date, source_assignment_id, project_party_id, resource_id
1641 FROM pa_project_assignments
1642 WHERE assignment_id = p_assignment_id
1643 AND record_version_number = p_record_version_number;
1644
1645 CURSOR get_requirement_info IS
1646 SELECT assignment_type, start_date
1647 FROM pa_project_assignments
1648 WHERE assignment_id = l_source_assignment_id;
1649
1650 CURSOR get_record_version IS
1651 SELECT record_version_number
1652 FROM pa_project_assignments
1653 WHERE assignment_id = p_assignment_id;
1654
1655 -- get advertisement action set details
1656 CURSOR get_action_set IS
1657 SELECT action_set_id, record_version_number
1658 FROM pa_action_sets
1659 WHERE object_id = p_assignment_id
1660 AND object_type = 'OPEN_ASSIGNMENT'
1661 AND action_set_type_code = 'ADVERTISEMENT'
1662 AND status_code <> 'DELETED';
1663
1664 CURSOR get_linked_res_asgmts IS
1665 SELECT resource_assignment_id, wbs_element_version_id, budget_version_id, project_structure_version_id
1666 FROM
1667 (
1668 (SELECT ra.resource_assignment_id, ra.wbs_element_version_id, bv.budget_version_id, bv.project_structure_version_id
1669 FROM PA_RESOURCE_ASSIGNMENTS ra
1670 ,PA_BUDGET_VERSIONS bv
1671 ,PA_PROJ_ELEM_VER_STRUCTURE evs
1672 -- ,PA_PROJECT_ASSIGNMENTS pa -- 5110598 Removed PA_PROJECT_ASSIGNMENTS table usage
1673 WHERE ra.project_id = bv.project_id
1674 AND bv.project_id = evs.project_id
1675 AND ra.budget_version_id = bv.budget_version_id
1676 AND bv.project_structure_version_id = evs.element_version_id
1677 -- AND ra.project_id = l_assignment_rec.project_id
1678 -- AND pa.assignment_id = p_assignment_id -- 5110598 Removed table usage
1679 -- AND ra.project_id = pa.project_id -- 5110598 Removed table usage
1680 AND ra.project_assignment_id = p_assignment_id
1681 AND evs.status_code = 'STRUCTURE_WORKING')
1682 UNION ALL
1683 (SELECT ra.resource_assignment_id, ra.wbs_element_version_id, bv.budget_version_id, bv.project_structure_version_id
1684 FROM PA_RESOURCE_ASSIGNMENTS ra
1685 ,PA_BUDGET_VERSIONS bv
1686 ,PA_PROJ_ELEM_VER_STRUCTURE evs
1687 ,PA_PROJ_WORKPLAN_ATTR pwa
1688 -- ,PA_PROJECT_ASSIGNMENTS pa -- 5110598 Removed PA_PROJECT_ASSIGNMENTS table usage
1689 WHERE pwa.wp_enable_Version_flag = 'N'
1690 AND pwa.project_id = ra.project_id
1691 AND pwa.proj_element_id = evs.proj_element_id
1692 AND ra.project_id = bv.project_id
1693 AND bv.project_id = evs.project_id
1694 AND ra.budget_version_id = bv.budget_version_id
1695 AND bv.project_structure_version_id = evs.element_version_id
1696 -- AND ra.project_id = l_assignment_rec.project_id
1697 -- AND pa.assignment_id = p_assignment_id -- 5110598 Removed table usage
1698 -- AND ra.project_id = pa.project_id -- 5110598 Removed table usage
1699 AND ra.project_assignment_id = p_assignment_id)
1700 )
1701 ORDER BY budget_version_id, project_structure_version_id;
1702
1703 CURSOR get_res_mand_attributes IS
1704 SELECT rf.ROLE_ENABLED_FLAG
1705 FROM pa_res_formats_b rf,
1706 pa_resource_list_members rlm,
1707 pa_project_assignments pa
1708 WHERE pa.assignment_id = p_assignment_id
1709 AND pa.resource_list_member_id IS NOT NULL
1710 AND rlm.resource_list_member_id = pa.resource_list_member_id
1711 AND rlm.res_format_id = rf.res_format_id;
1712
1713 BEGIN
1714 -- Initialize the Error Stack
1715 PA_DEBUG.init_err_stack('PA_ASSIGNMENT_APPROVAL_PUB.Cancel_Assignment');
1716
1717 --Log Message
1718 IF P_DEBUG_MODE = 'Y' THEN
1719 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENT_APPROVAL_PUB.Cancel_Assignment.begin'
1720 ,x_msg => 'Beginning of Cancel_Assignment'
1721 ,x_log_level => 5);
1722 END IF;
1723
1724 -- Initialize the error flag
1725 PA_ASSIGNMENTS_PUB.g_error_exists := FND_API.G_FALSE;
1726
1727 -- Issue API savepoint if the transaction is to be committed
1728 IF p_commit = FND_API.G_TRUE THEN
1729 SAVEPOINT ASG_PUB_CHANGE_STATUS;
1730 END IF;
1731
1732 --Clear the global PL/SQL message table
1733 IF FND_API.TO_BOOLEAN( p_init_msg_list ) THEN
1734 FND_MSG_PUB.initialize;
1735 END IF;
1736
1737 OPEN check_record_version;
1738
1739 FETCH check_record_version INTO l_assignment_row_id, l_project_id, l_calendar_id,
1740 l_asgn_start_date, l_asgn_end_date, l_source_assignment_id,
1741 l_project_party_id, l_resource_id;
1742
1743 IF check_record_version%NOTFOUND THEN
1744
1745 PA_UTILS.Add_Message( p_app_short_name => 'PA'
1746 ,p_msg_name => 'PA_XC_RECORD_CHANGED');
1747 PA_ASSIGNMENTS_PUB.g_error_exists := FND_API.G_TRUE;
1748
1749 ELSE
1750
1751 l_record_version_number := p_record_version_number;
1752
1753 --Change Status in the Schedule tables
1754 /*
1755 IF p_assignment_type = 'OPEN_ASSIGNMENT' THEN
1756 l_save_to_hist := FND_API.G_FALSE;
1757 ELSE
1758 l_save_to_hist := FND_API.G_TRUE;
1759 END IF;
1760 */
1761
1762
1763 --Log Message
1764 IF P_DEBUG_MODE = 'Y' THEN
1765 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENT_APPROVAL_PUB.Cancel_Assignment'
1766 ,x_msg => 'calling PA_SCHEDULE_PUB.Change_Status'
1767 ,x_log_level => 5);
1768 END IF;
1769 PA_SCHEDULE_PUB.Change_Status(p_record_version_number => l_record_version_number,
1770 p_project_id => l_project_id,
1771 p_calendar_id =>l_calendar_id,
1772 p_assignment_id => p_assignment_id,
1773 p_assignment_type => p_assignment_type,
1774 p_status_type => null,
1775 p_start_date => p_start_date,
1776 p_end_date => p_end_date,
1777 p_assignment_status_code => p_assignment_status_code,
1778 p_asgn_start_date => l_asgn_start_date,
1779 p_asgn_end_date => l_asgn_end_date,
1780 -- p_save_to_hist => l_save_to_hist,
1781 x_return_status => x_return_status,
1782 x_msg_count => x_msg_count,
1783 x_msg_data => x_msg_data);
1784 -- dbms_output.put_line('status after 1st change_status'||x_return_status);
1785
1786 --
1787 --Find out if full duration cancel
1788 --
1789 IF p_assignment_type = 'OPEN_ASSIGNMENT' THEN
1790 l_req_status_canceled_flag:=
1791 PA_ASSIGNMENT_UTILS.Is_Open_Asgmt_Cancelled(p_status_code=>p_assignment_status_code
1792 ,p_status_type => 'OPEN_ASGMT');
1793 IF (l_req_status_canceled_flag = 'Y') AND (p_start_date = l_asgn_start_date)
1794 AND (p_end_date = l_asgn_end_date) THEN
1795
1796 l_full_cancel_flag := 'Y';
1797
1798
1799 --commenting out: since no partial cancellation
1800 /*
1801 ELSIF (l_req_status_canceled_flag = 'Y') THEN
1802
1803 l_full_cancel_flag := PA_SCHEDULE_UTILS.check_input_system_status(p_assignment_id => p_assignment_id
1804 ,p_status_type => 'OPEN_ASGMT'
1805 ,p_in_system_status_code => 'OPEN_ASGMT_CANCEL');
1806 */
1807 END IF;
1808
1809 ELSE
1810 l_asgn_status_canceled_flag:=
1811 PA_ASSIGNMENT_UTILS.Is_Staffed_Asgmt_Cancelled(p_status_code=>p_assignment_status_code
1812 ,p_status_type => 'STAFFED_ASGMT');
1813 IF (l_asgn_status_canceled_flag = 'Y') AND (p_start_date = l_asgn_start_date)
1814 AND (p_end_date = l_asgn_end_date) THEN
1815 l_full_cancel_flag := 'Y';
1816
1817 --commenting out: since no partial cancellation
1818 /*
1819 ELSIF (l_asgn_status_canceled_flag = 'Y') THEN
1820
1821 l_full_cancel_flag := PA_SCHEDULE_UTILS.check_input_system_status(p_assignment_id => p_assignment_id
1822 ,p_status_type => 'STAFFED_ASGMT'
1823 ,p_in_system_status_code => 'STAFFED_ASGMT_CANCEL');
1824 */
1825 END IF;
1826 END IF;
1827 --dbms_output.put_line('l_full_cancel'||l_full_cancel_flag);
1828
1829 -- If this is an open requirement, close the Advertisement Action Set
1830 IF p_assignment_type = 'OPEN_ASSIGNMENT' THEN
1831
1832 -- dbms_output.put_line('declining candidates');
1833 IF P_DEBUG_MODE = 'Y' THEN
1834 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENT_APPROVAL_PUB.Cancel_Assignment'
1835 ,x_msg => 'Calling PA_CANDIDATE_PUB.Decline_Candidates'
1836 ,x_log_level => 5);
1837 END IF;
1838
1839 -- Decline all active candidates in the requirement
1840 PA_CANDIDATE_PUB.Decline_Candidates(
1841 p_assignment_id => p_assignment_id,
1842 x_return_status => x_return_status,
1843 x_msg_count => x_msg_count,
1844 x_msg_data => x_msg_data);
1845
1846 OPEN get_action_set;
1847 FETCH get_action_set INTO l_action_set_id, l_record_version_number;
1848 CLOSE get_action_set;
1849
1850 --dbms_output.put_line('before PA_ACTION_SETS_PUB.Update_Action_Set');
1851
1852 --Log Message
1853 IF P_DEBUG_MODE = 'Y' THEN
1854 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENT_APPROVAL_PUB.Cancel_Assignment'
1855 ,x_msg => 'calling PA_ACTION_SETS_PUB.Update_Action_Set'
1856 ,x_log_level => 5);
1857 END IF;
1858
1859 PA_ACTION_SETS_PUB.Update_Action_Set(
1860 p_action_set_id => l_action_set_id
1861 ,p_object_id => p_assignment_id
1862 ,p_object_type => 'OPEN_ASSIGNMENT'
1863 ,p_action_set_type_code => 'ADVERTISEMENT'
1864 ,p_status_code => 'CLOSED'
1865 ,p_record_version_number => l_record_version_number
1866 ,p_commit => p_commit
1867 ,p_validate_only => FND_API.G_FALSE
1868 ,p_init_msg_list => FND_API.G_FALSE
1869 ,x_return_status => x_return_status
1870 ,x_msg_count => x_msg_count
1871 ,x_msg_data => x_msg_data);
1872
1873
1874 --dbms_output.put_line('after PA_ACTION_SETS_PUB.Update_Action_Set');
1875
1876 END IF;
1877
1878
1879 --IF full duration cancel and assignment
1880 IF (l_full_cancel_flag = 'Y') AND (p_assignment_type <> 'OPEN_ASSIGNMENT') THEN
1881
1882 --IF pending approval, then abort
1883 IF PA_ASGMT_WFSTD.Is_Approval_Pending (p_assignment_id => p_assignment_id) = 'Y' THEN
1884 --dbms_output.put_line('abort assignment');
1885
1886 PA_ASSIGNMENT_APPROVAL_PVT.Abort_Assignment_Approval(p_assignment_id => p_assignment_id
1887 ,p_project_id => l_project_id
1888 ,x_return_status => x_return_status);
1889 --dbms_output.put_line('abort assignment result:'||x_return_status);
1890 END IF;
1891
1892 --dbms_output.put_line('before PA_PROJECT_PARTIES_PVT.Delete_Project_Party');
1893
1894 --
1895 --Delete the Project Party Role
1896 --
1897 IF l_project_party_id IS NOT NULL THEN
1898 PA_PROJECT_PARTIES_PVT.Delete_Project_Party(
1899 p_commit => 'F',
1900 p_validate_only => 'F',
1901 p_project_party_id => l_project_party_id,
1902 p_calling_module => 'ASSIGNMENT',
1903 p_record_version_number => null,
1904 x_return_status => x_return_status,
1905 x_msg_count => x_msg_count,
1906 x_msg_data => x_msg_data);
1907 END IF;
1908
1909 --dbms_output.put_line('after PA_PROJECT_PARTIES_PVT.Delete_Project_Party');
1910 --
1911 --Reverse the Candidate status for the resource assigned
1912 --
1913 PA_CANDIDATE_UTILS.Reverse_Candidate_Status (p_assignment_id => l_source_assignment_id
1914 ,p_resource_id => l_resource_id
1915 ,x_return_status => x_return_status
1916 ,x_error_message_code => l_error_message_code);
1917
1918 --dbms_output.put_line('after PA_CANDIDATE_UTILS.Reverse_Candidate_Status ');
1919
1920
1921 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1922 PA_UTILS.Add_Message( p_app_short_name => 'PA'
1923 ,p_msg_name => l_error_message_code);
1924 END IF;
1925
1926 --
1927 --Reopen requirement when necessary
1928 --
1929 --If source requirement exist, then reopen it.
1930 --This is under the assumption that only way for assignment to have source_assignment_id is from a requirement
1931 --
1932 IF l_source_assignment_id IS NOT NULL THEN
1933
1934 OPEN get_requirement_info;
1935 FETCH get_requirement_info INTO l_assignment_type, l_req_start_date;
1936
1937 IF get_requirement_info%FOUND THEN
1938
1939 IF l_assignment_type = 'OPEN_ASSIGNMENT' THEN
1940
1941 --dbms_output.put_line('Reopen requirement now');
1942
1943
1944 --create a new requirement for canceled assignment
1945 PA_ASSIGNMENTS_PUB.Copy_Team_Role (p_assignment_id => l_source_assignment_id
1946 ,p_asgn_creation_mode => 'COPY' ---Fix for Bug 6169205
1947 ,x_new_assignment_id => l_new_assignment_id
1948 ,x_assignment_number => l_assignment_number
1949 ,x_assignment_row_id => l_assignment_row_id
1950 ,x_return_status => x_return_status
1951 ,x_msg_count => x_msg_count
1952 ,x_msg_data => x_msg_data
1953 );
1954
1955 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1956
1957 --Need to keep the link from the new requirement to the canceled assignment
1958 UPDATE pa_project_assignments
1959 SET source_assignment_id = p_assignment_id
1960 WHERE assignment_id = l_new_assignment_id;
1961
1962 --Copy the Candidate List from the old requirement to the new requirement
1963 PA_CANDIDATE_PUB.Copy_Candidates
1964 (p_old_requirement_id => l_source_assignment_id
1965 ,p_new_requirement_id => l_new_assignment_id
1966 ,p_new_start_date => l_req_start_date
1967 ,x_return_status => x_return_status
1968 ,x_msg_count => x_msg_count
1969 ,x_msg_data => x_msg_data);
1970
1971 ELSE
1972 PA_UTILS.Add_Message( p_app_short_name => 'PA'
1973 ,p_msg_name => 'PA_FAILED_TO_CREATE_OPEN_ASGN');
1974 END IF;
1975
1976 --dbms_output.put_line('new requirement'||x_return_status);
1977 END IF; -- IF l_assignment_type = 'OPEN_ASSIGNMENT' THEN
1978 END IF; -- IF get_requirement_info%FOUND THEN
1979
1980 CLOSE get_requirement_info;
1981
1982 --anuragag changes for bug 8763672
1983 PA_CANDIDATE_PUB.Delete_Candidates(p_assignment_id => l_source_assignment_id,
1984 p_status_code => NULL
1985 ,x_return_status => x_return_status
1986 ,x_msg_count => x_msg_count
1987 ,x_msg_data => x_msg_data);
1988
1989 update pa_project_assignments set no_of_active_candidates=0
1990 where assignment_id = l_source_assignment_id;
1991 --end of anuragag changes for bug 8763672
1992
1993 END IF; -- end of checking source assignment id
1994
1995 --Update apprvl_status_code and send notification if necessary
1996 l_new_assignment_flag := PA_ASSIGNMENT_APPROVAL_PVT.Is_New_Assignment(p_assignment_id => p_assignment_id);
1997 --dbms_output.put_line('new assignment flag'||l_new_assignment_flag);
1998
1999 -- update the assignment approval status and send notification when the canceled assignment
2000 -- has ever been approved.
2001
2002 PA_ASSIGNMENT_APPROVAL_PUB.Start_Assignment_Approvals( p_assignment_id => p_assignment_id
2003 ,p_new_assignment_flag => l_new_assignment_flag
2004 ,p_action_code =>PA_ASSIGNMENT_APPROVAL_PUB.g_cancel_action
2005 ,p_record_version_number => NULL
2006 ,p_validate_only => FND_API.G_FALSE
2007 ,x_overcommitment_flag => l_overcommitment_flag
2008 ,x_conflict_group_id => l_conflict_group_id
2009 ,x_return_status => x_return_status
2010 ,x_msg_count => x_msg_count
2011 ,x_msg_data => x_msg_data);
2012 --dbms_output.put_line('start_assignment_approval'||x_return_status);
2013
2014 --Else if full duration cancel, just set cancel status
2015 ELSIF l_full_cancel_flag = 'Y' THEN
2016
2017 -- Update apprvl_status_code
2018 PA_ASSIGNMENT_APPROVAL_PVT.Update_Approval_Status(
2019 P_ASSIGNMENT_ID => p_assignment_id
2020 ,P_ACTION_CODE => PA_ASSIGNMENT_APPROVAL_PUB.g_cancel_action
2021 ,P_RECORD_VERSION_NUMBER => NULL
2022 ,X_APPRVL_STATUS_CODE => l_apprvl_status_code
2023 ,X_CHANGE_ID => l_change_id
2024 ,X_RECORD_VERSION_NUMBER => l_record_version_number
2025 ,X_RETURN_STATUS => x_return_status
2026 ,X_MSG_COUNT => x_msg_count
2027 ,X_MSG_DATA => x_msg_data);
2028 --dbms_output.put_line('update_approval_status'||x_return_status);
2029
2030 END IF; -- end of check full duration cancel
2031
2032 -- FP-M Development
2033 -- Break the link between associated task assignments
2034 -- and the cancelled team role
2035 OPEN get_linked_res_asgmts;
2036 FETCH get_linked_res_asgmts
2037 BULK COLLECT INTO l_task_assignment_id_tbl,
2038 l_task_version_id_tbl,
2039 l_budget_version_id_tbl,
2040 l_struct_version_id_tbl;
2041 CLOSE get_linked_res_asgmts;
2042
2043 -- 1. Change project_assignment_id to NULL (-1)
2044 -- 2. Don't wipe out project_role_id
2045 -- 3. Wipe out named_role when it is not a mandatory attribute
2046 -- of planning resource
2047 OPEN get_res_mand_attributes;
2048 FETCH get_res_mand_attributes INTO l_cur_role_flag;
2049
2050 IF get_res_mand_attributes%FOUND AND l_cur_role_flag = 'Y' THEN
2051 pa_assignments_pvt.Update_Task_Assignments(
2052 p_task_assignment_id_tbl => l_task_assignment_id_tbl
2053 ,p_task_version_id_tbl => l_task_version_id_tbl
2054 ,p_budget_version_id_tbl => l_budget_version_id_tbl
2055 ,p_struct_version_id_tbl => l_struct_version_id_tbl
2056 ,p_project_assignment_id => -1
2057 ,x_return_status => l_return_status
2058 );
2059 ELSE
2060 pa_assignments_pvt.Update_Task_Assignments(
2061 p_task_assignment_id_tbl => l_task_assignment_id_tbl
2062 ,p_task_version_id_tbl => l_task_version_id_tbl
2063 ,p_budget_version_id_tbl => l_budget_version_id_tbl
2064 ,p_struct_version_id_tbl => l_struct_version_id_tbl
2065 ,p_project_assignment_id => -1
2066 ,p_named_role => FND_API.G_MISS_CHAR
2067 ,x_return_status => l_return_status
2068 );
2069 END IF;
2070 CLOSE get_res_mand_attributes;
2071
2072 END IF; -- end of checking record_verison_number
2073
2074 CLOSE check_record_version;
2075
2076 --
2077 -- IF the number of messaages is 1 then fetch the message code from the stack and return its text
2078 --
2079 x_msg_count := FND_MSG_PUB.Count_Msg;
2080 IF x_msg_count = 1 THEN
2081 pa_interface_utils_pub.get_messages ( p_encoded => FND_API.G_TRUE
2082 ,p_msg_index => 1
2083 ,p_data => x_msg_data
2084 ,p_msg_index_out => l_msg_index_out
2085 );
2086 END IF;
2087
2088 -- Reset the error stack when returning to the calling program
2089 PA_DEBUG.Reset_Err_Stack;
2090
2091
2092 IF x_msg_count > 0 THEN
2093
2094 x_return_status := FND_API.G_RET_STS_ERROR;
2095
2096 END IF;
2097
2098
2099 -- Put any message text from message stack into the Message ARRAY
2100 --
2101 EXCEPTION
2102 WHEN OTHERS THEN
2103 IF p_commit = FND_API.G_TRUE THEN
2104 ROLLBACK TO ASG_PUB_CHANGE_STATUS;
2105 END IF;
2106 -- Set the excetption Message and the stack
2107 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_ASSIGNMENT_APPROVAL_PUB.Change_Assignment_Status'
2108 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
2109 --
2110 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2111 RAISE; -- This is optional depending on the needs
2112
2113 --
2114 END Change_Assignment_Status;
2115
2116
2117
2118 --
2119 -- Procedure : Get_Current_Approver
2120 -- Purpose : Get the approver which has the current approver flag set.
2121 -- Parameters :
2122 --
2123 PROCEDURE Get_Current_Approver
2124 (
2125 p_assignment_id IN NUMBER ,
2126 p_project_id IN NUMBER ,
2127 p_apprvl_status_code IN VARCHAR2 ,
2128 x_approver_name OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
2129 IS
2130
2131 l_user_id NUMBER;
2132 l_resource_id NUMBER;
2133 l_person_id NUMBER;
2134 l_item_type pa_wf_processes.item_type%TYPE;
2135 l_item_key pa_wf_processes.item_key%TYPE;
2136
2137 CURSOR get_item_key IS
2138 SELECT item_key, item_type
2139 FROM pa_wf_processes
2140 WHERE item_key = (
2141 SELECT max(item_key)
2142 FROM pa_wf_processes
2143 WHERE wf_type_code = 'ASSIGNMENT_APPROVAL'
2144 AND entity_key1 = to_char(p_project_id)
2145 AND entity_key2 = to_char(p_assignment_id)
2146 )
2147 and item_type = 'PAWFAAP';
2148
2149 CURSOR get_user_id IS
2150 SELECT fu.user_id
2151 FROM pa_wf_ntf_performers pwnp,
2152 fnd_user fu
2153 WHERE pwnp.object_id1 = p_assignment_id
2154 AND pwnp.object_id2 = p_project_id
2155 AND pwnp.item_type = l_item_type
2156 AND pwnp.item_key = l_item_key
2157 AND pwnp.current_approver_flag = 'Y'
2158 AND pwnp.user_name = fu.user_name;
2159
2160 -- Added for Bug# 8296021
2161 CURSOR get_wf_progress_flag IS
2162 SELECT nvl(mass_wf_in_progress_flag,'N')
2163 FROM pa_project_assignments
2164 WHERE assignment_id = p_assignment_id;
2165
2166 CURSOR get_user_id_mflow IS
2167 SELECT fu.user_id
2168 FROM pa_wf_ntf_performers pwnp,
2169 fnd_user fu
2170 WHERE wf_type_code = 'MASS_ASSIGNMENT_APPROVAL'
2171 AND object_id1 = p_assignment_id
2172 AND object_id2 = -1
2173 AND fu.user_name = pwnp.user_name
2174 AND (group_id, routing_order) in (SELECT max(group_id), min(routing_order)
2175 FROM pa_wf_ntf_performers
2176 WHERE wf_type_code = 'MASS_ASSIGNMENT_APPROVAL'
2177 AND object_id1 = p_assignment_id
2178 AND object_id2 = -1);
2179
2180 wf_progress_flag VARCHAR2(10) := 'N';
2181 -- End of Bug# 8296021
2182
2183 BEGIN
2184 -- Initialize the Error Stack
2185 PA_DEBUG.init_err_stack('PA_ASSIGNMENT_APPROVAL_PUB.Get_Current_Approver');
2186
2187 --Log Message
2188 IF P_DEBUG_MODE = 'Y' THEN
2189 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENT_APPROVAL_PUB.Get_Current_Approver.begin'
2190 ,x_msg => 'Beginning of Get_Current_Approver'
2191 ,x_log_level => 5);
2192 END IF;
2193
2194 --Check to see if assignment is pending approval
2195 IF p_apprvl_status_code = PA_ASSIGNMENT_APPROVAL_PUB.g_submitted THEN
2196 -- Added for Bug# 8296021
2197 OPEN get_wf_progress_flag;
2198 FETCH get_wf_progress_flag INTO wf_progress_flag;
2199 CLOSE get_wf_progress_flag;
2200
2201 IF (wf_progress_flag = 'N') THEN
2202 -- End of Bug# 8296021
2203
2204 --Get the maximum item key
2205 OPEN get_item_key;
2206 FETCH get_item_key INTO l_item_key, l_item_type;
2207
2208 IF get_item_key%FOUND THEN
2209
2210 --Get the current approver user_name
2211 OPEN get_user_id;
2212 FETCH get_user_id INTO l_user_id;
2213 CLOSE get_user_id;
2214
2215 --Get the approver name from the user_name
2216 IF l_user_id IS NOT NULL THEN
2217 PA_COMP_PROFILE_PUB.get_user_info(p_user_id => l_user_id
2218 ,x_person_id => l_person_id
2219 ,x_resource_id => l_resource_id
2220 ,x_resource_name=> x_approver_name );
2221 END IF;
2222 END IF;
2223 CLOSE get_item_key;
2224 -- Start of Bug# 8296021
2225 ELSIF (wf_progress_flag = 'Y') THEN
2226 OPEN get_user_id_mflow;
2227 FETCH get_user_id_mflow INTO l_user_id;
2228 CLOSE get_user_id_mflow;
2229
2230 IF l_user_id IS NOT NULL THEN
2231 PA_COMP_PROFILE_PUB.get_user_info(p_user_id => l_user_id
2232 ,x_person_id => l_person_id
2233 ,x_resource_id => l_resource_id
2234 ,x_resource_name=> x_approver_name);
2235 END IF;
2236 END IF; -- end for wf_progress_flag
2237 -- End of Bug# 8296021
2238 END IF; -- end of checking if assignment is pending approval
2239
2240 -- Put any message text from message stack into the Message ARRAY
2241 --
2242 EXCEPTION
2243 WHEN OTHERS THEN
2244 -- Set the excetption Message and the stack
2245 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_ASSIGNMENT_APPROVAL_PUB.Get_Current_Approver'
2246 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
2247 RAISE; -- This is optional depending on the needs
2248
2249 END Get_Current_Approver;
2250
2251
2252
2253 PROCEDURE Cancel_Assignment
2254 (
2255 p_record_version_number IN Number ,
2256 p_assignment_id IN Number ,
2257 p_assignment_type IN Varchar2 ,
2258 p_start_date IN date ,
2259 p_end_date IN date ,
2260 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2261 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2262 x_return_status OUT NOCOPY Varchar2 , --File.Sql.39 bug 4440895
2263 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
2264 x_msg_data OUT NOCOPY Varchar2 ) --File.Sql.39 bug 4440895
2265
2266 IS
2267 l_assignment_status_code pa_project_assignments.status_code%TYPE;
2268 l_msg_index_out NUMBER;
2269 l_start_date DATE;
2270 l_end_date DATE;
2271 l_return_status VARCHAR2(1);
2272 l_error_message_code fnd_new_messages.message_name%TYPE;
2273 l_project_id NUMBER;
2274 l_person_id NUMBER;
2275
2276 CURSOR get_start_end_date IS
2277 SELECT asgn.start_date, asgn.end_date, asgn.project_id, res.person_id
2278 FROM pa_project_assignments asgn,
2279 pa_resources_denorm res
2280 WHERE assignment_id = p_assignment_id
2281 AND res.resource_id = asgn.resource_id
2282 AND rownum=1;
2283
2284 BEGIN
2285 -- Initialize the Error Stack
2286 PA_DEBUG.init_err_stack('PA_ASSIGNMENT_APPROVAL_PUB.Cancel_Assignment');
2287
2288 --Log Message
2289 IF P_DEBUG_MODE = 'Y' THEN
2290 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENT_APPROVAL_PUB.Cancel_Assignment.begin'
2291 ,x_msg => 'Beginning of Cancel_Assignment'
2292 ,x_log_level => 5);
2293 END IF;
2294
2295 --Clear the global PL/SQL message table
2296 IF FND_API.TO_BOOLEAN( p_init_msg_list ) THEN
2297 FND_MSG_PUB.initialize;
2298 END IF;
2299
2300 -- Assignment cannot be deleted if project transactions
2301 -- are associated with it
2302 OPEN get_start_end_date;
2303 FETCH get_start_end_date INTO l_start_date, l_end_date, l_project_id, l_person_id;
2304 CLOSE get_start_end_date;
2305
2306 -- Should perform EI validation only for assignments
2307 IF p_assignment_type <> 'OPEN_ASSIGNMENT' THEN
2308 -- Bug 2797890: Added p_project_id, p_person_id parameters
2309 PA_TRANS_UTILS.Check_Txn_Exists( p_assignment_id => p_assignment_id
2310 ,p_project_id => l_project_id
2311 ,p_person_id => l_person_id
2312 ,p_calling_mode => 'CANCEL'
2313 ,p_old_start_date => null
2314 ,p_old_end_date => null
2315 ,p_new_start_date => l_start_date
2316 ,p_new_end_date => l_end_date
2317 ,x_error_message_code => l_error_message_code
2318 ,x_return_status => l_return_status);
2319
2320 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2321 PA_UTILS.Add_Message( p_app_short_name => 'PA'
2322 ,p_msg_name => l_error_message_code);
2323 PA_ASSIGNMENTS_PUB.g_error_exists := FND_API.G_TRUE; --bug#8240018
2324 END IF;
2325 l_return_status := NULL;
2326 END IF;
2327
2328 --Bug#8240018
2329 -- proceed with the code flow only when there is no error returned
2330 IF PA_ASSIGNMENTS_PUB.g_error_exists <> FND_API.G_TRUE THEN
2331
2332 --Get Profile Default Status
2333 IF p_assignment_type = 'OPEN_ASSIGNMENT' THEN
2334 FND_PROFILE.Get('PA_DEF_CANCELED_REQMT_STATUS',l_assignment_status_code);
2335 ELSE
2336 FND_PROFILE.Get('PA_DEF_CANCELED_ASGMT_STATUS',l_assignment_status_code);
2337 END IF;
2338
2339 IF l_assignment_status_code IS NOT NULL THEN
2340
2341 --call Change_Assignment_Status
2342 PA_ASSIGNMENT_APPROVAL_PUB.Change_Assignment_Status
2343 (
2344 p_record_version_number => p_record_version_number
2345 ,p_assignment_id => p_assignment_id
2346 ,p_assignment_type => p_assignment_type
2347 ,p_start_date => p_start_date
2348 ,p_end_date => p_end_date
2349 ,p_assignment_status_code => l_assignment_status_code
2350 ,x_return_status => x_return_status
2351 ,x_msg_count => x_msg_count
2352 ,x_msg_data => x_msg_data );
2353
2354 ELSE
2355 PA_UTILS.Add_Message ( p_app_short_name => 'PA'
2356 ,p_msg_name => 'PA_START_STATUS_NOT_DEFINED');
2357
2358 END IF;
2359
2360 --
2361 -- IF the number of messaages is 1 then fetch the message code from the stack and return its text
2362 --
2363 x_msg_count := FND_MSG_PUB.Count_Msg;
2364 IF x_msg_count = 1 THEN
2365 pa_interface_utils_pub.get_messages ( p_encoded => FND_API.G_TRUE
2366 ,p_msg_index => 1
2367 ,p_data => x_msg_data
2368 ,p_msg_index_out => l_msg_index_out
2369 );
2370 END IF;
2371
2372 -- Reset the error stack when returning to the calling program
2373 PA_DEBUG.Reset_Err_Stack;
2374
2375
2376 IF x_msg_count > 0 THEN
2377
2378 x_return_status := FND_API.G_RET_STS_ERROR;
2379
2380 END IF;
2381 END IF; --end of if loop for Bug#8240018
2382 EXCEPTION
2383 WHEN OTHERS THEN
2384 -- Set the excetption Message and the stack
2385 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_ASSIGNMENT_APPROVAL_PUB.Cancel_Assignment'
2386 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
2387 --
2388 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2389 RAISE; -- This is optional depending on the needs
2390
2391 END Cancel_Assignment;
2392
2393 /* --------------------------Begin Mass Assignment Approval Code-------------------------------------*/
2394 /* Added a new parameter p_overriding_authority_flag in the procedure below for the bug 3213509 */
2395 --BEGIN FORWARD DECLARATIONS
2396
2397 PROCEDURE validate_approver_name_id
2398 ( p_project_id IN NUMBER
2399 ,p_assignment_id_tbl IN SYSTEM.pa_num_tbl_type := prm_empty_num_tbl
2400 ,p_approver1_id_tbl IN SYSTEM.pa_num_tbl_type := prm_empty_num_tbl
2401 ,p_approver1_name_tbl IN SYSTEM.pa_varchar2_240_tbl_type := prm_empty_varchar2_240_tbl
2402 ,p_approver2_id_tbl IN SYSTEM.pa_num_tbl_type := prm_empty_num_tbl
2403 ,p_approver2_name_tbl IN SYSTEM.pa_varchar2_240_tbl_type := prm_empty_varchar2_240_tbl
2404 ,p_submitter_user_id IN NUMBER
2405 ,p_group_id IN NUMBER
2406 ,p_api_version IN NUMBER := 1.0
2407 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
2408 ,p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM
2409 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
2410 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
2411 ,p_overriding_authority_flag IN VARCHAR2 := 'N'
2412 ,x_assignment_id_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type
2413 ,x_approver1_id_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type
2414 ,x_approver2_id_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type
2415 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2416 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2417 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2418 );
2419
2420 --END FORWARD DECLARATIONS
2421
2422
2423
2424 ---------------------------------------------------------------
2425 --This API is called from the Mass Submit for approval pages.
2426 --The IN parameters are all parameters needed to call
2427 --the mass transaction API. This API is called online
2428 --and so there are no validations, it calls the mass
2429 --transaction API by passing all the IN variables
2430 ---------------------------------------------------------------
2431 PROCEDURE mass_submit_for_asgmt_aprvl
2432 (p_mode IN VARCHAR2
2433 ,p_action IN VARCHAR2
2434 ,p_resource_id_tbl IN SYSTEM.pa_num_tbl_type := prm_empty_num_tbl
2435 ,p_assignment_id_tbl IN SYSTEM.pa_num_tbl_type := prm_empty_num_tbl
2436 ,p_assignment_name IN pa_project_assignments.assignment_name%TYPE := FND_API.G_MISS_CHAR
2437 ,p_assignment_type IN pa_project_assignments.assignment_type%TYPE := FND_API.G_MISS_CHAR
2438 ,p_status_code IN pa_project_assignments.status_code%TYPE := FND_API.G_MISS_CHAR
2439 ,p_multiple_status_flag IN pa_project_assignments.multiple_status_flag%TYPE := FND_API.G_MISS_CHAR
2440 ,p_staffing_priority_code IN pa_project_assignments.staffing_priority_code%TYPE := FND_API.G_MISS_CHAR
2441 ,p_project_id IN pa_project_assignments.project_id%TYPE := FND_API.G_MISS_NUM
2442 ,p_project_role_id IN pa_project_assignments.project_role_id%TYPE := FND_API.G_MISS_NUM
2443 ,p_role_list_id IN pa_role_lists.role_list_id%TYPE := FND_API.G_MISS_NUM
2444 ,p_project_subteam_id IN pa_project_subteams.project_subteam_id%TYPE := FND_API.G_MISS_NUM
2445 ,p_description IN pa_project_assignments.description%TYPE := FND_API.G_MISS_CHAR
2446 ,p_append_description_flag IN VARCHAR2 := 'N'
2447 ,p_start_date IN pa_project_assignments.start_date%TYPE := FND_API.G_MISS_DATE
2448 ,p_end_date IN pa_project_assignments.end_date%TYPE := FND_API.G_MISS_DATE
2449 ,p_extension_possible IN pa_project_assignments.extension_possible%TYPE := FND_API.G_MISS_CHAR
2450 ,p_min_resource_job_level IN pa_project_assignments.min_resource_job_level%TYPE := FND_API.G_MISS_NUM
2451 ,p_max_resource_job_level IN pa_project_assignments.max_resource_job_level%TYPE := FND_API.G_MISS_NUM
2452 ,p_additional_information IN pa_project_assignments.additional_information%TYPE := FND_API.G_MISS_CHAR
2453 ,p_append_information_flag IN VARCHAR2 := 'N'
2454 ,p_location_id IN pa_project_assignments.location_id%TYPE := FND_API.G_MISS_NUM
2455 ,p_work_type_id IN pa_project_assignments.work_type_id%TYPE := FND_API.G_MISS_NUM
2456 ,p_calendar_type IN pa_project_assignments.calendar_type%TYPE := FND_API.G_MISS_CHAR
2457 ,p_calendar_id IN pa_project_assignments.calendar_id%TYPE := FND_API.G_MISS_NUM
2458 ,p_resource_calendar_percent IN pa_project_assignments.resource_calendar_percent%TYPE := FND_API.G_MISS_NUM
2459 ,p_project_name IN pa_projects_all.name%TYPE := FND_API.G_MISS_CHAR
2460 ,p_project_number IN pa_projects_all.segment1%TYPE := FND_API.G_MISS_CHAR
2461 ,p_project_subteam_name IN pa_project_subteams.name%TYPE := FND_API.G_MISS_CHAR
2462 ,p_project_status_name IN pa_project_statuses.project_status_name%TYPE := FND_API.G_MISS_CHAR
2463 ,p_staffing_priority_name IN pa_lookups.meaning%TYPE := FND_API.G_MISS_CHAR
2464 ,p_project_role_name IN pa_project_role_types.meaning%TYPE := FND_API.G_MISS_CHAR
2465 ,p_location_city IN pa_locations.city%TYPE := FND_API.G_MISS_CHAR
2466 ,p_location_region IN pa_locations.region%TYPE := FND_API.G_MISS_CHAR
2467 ,p_location_country_name IN fnd_territories_tl.territory_short_name%TYPE := FND_API.G_MISS_CHAR
2468 ,p_location_country_code IN pa_locations.country_code%TYPE := FND_API.G_MISS_CHAR
2469 ,p_calendar_name IN jtf_calendars_tl.calendar_name%TYPE := FND_API.G_MISS_CHAR
2470 ,p_work_type_name IN pa_work_types_vl.name%TYPE := FND_API.G_MISS_CHAR
2471 ,p_expense_owner IN pa_project_assignments.expense_owner%TYPE := FND_API.G_MISS_CHAR
2472 ,p_expense_limit IN pa_project_assignments.expense_limit%TYPE := FND_API.G_MISS_NUM
2473 ,p_expense_limit_currency_code IN pa_project_assignments.expense_limit_currency_code%TYPE := FND_API.G_MISS_CHAR
2474 ,p_fcst_tp_amount_type IN pa_project_assignments.fcst_tp_amount_type%TYPE := FND_API.G_MISS_CHAR
2475 ,p_fcst_job_id IN pa_project_assignments.fcst_job_id%TYPE := FND_API.G_MISS_NUM
2476 ,p_fcst_job_group_id IN pa_project_assignments.fcst_job_group_id%TYPE := FND_API.G_MISS_NUM
2477 ,p_expenditure_org_id IN pa_project_assignments.expenditure_org_id%TYPE := FND_API.G_MISS_NUM
2478 ,p_expenditure_organization_id IN pa_project_assignments.expenditure_organization_id%TYPE := FND_API.G_MISS_NUM
2479 ,p_expenditure_type_class IN pa_project_assignments.expenditure_type_class%TYPE := FND_API.G_MISS_CHAR
2480 ,p_expenditure_type IN pa_project_assignments.expenditure_type%TYPE := FND_API.G_MISS_CHAR
2481 ,p_comp_match_weighting IN pa_project_assignments.competence_match_weighting%TYPE := FND_API.G_MISS_NUM
2482 ,p_avail_match_weighting IN pa_project_assignments.availability_match_weighting%TYPE := FND_API.G_MISS_NUM
2483 ,p_job_level_match_weighting IN pa_project_assignments.job_level_match_weighting%TYPE := FND_API.G_MISS_NUM
2484 ,p_search_min_availability IN pa_project_assignments.search_min_availability%TYPE := FND_API.G_MISS_NUM
2485 ,p_search_country_code IN pa_project_assignments.search_country_code%TYPE := FND_API.G_MISS_CHAR
2486 ,p_search_country_name IN fnd_territories_vl.territory_short_name%TYPE := FND_API.G_MISS_CHAR
2487 ,p_search_exp_org_struct_ver_id IN pa_project_assignments.search_exp_org_struct_ver_id%TYPE := FND_API.G_MISS_NUM
2488 ,p_search_exp_org_hier_name IN per_organization_structures.name%TYPE := FND_API.G_MISS_CHAR
2489 ,p_search_exp_start_org_id IN pa_project_assignments.search_exp_start_org_id%TYPE := FND_API.G_MISS_NUM
2490 ,p_search_exp_start_org_name IN hr_organization_units.name%TYPE := FND_API.G_MISS_CHAR
2491 ,p_search_min_candidate_score IN pa_project_assignments.search_min_candidate_score%TYPE := FND_API.G_MISS_NUM
2492 ,p_enable_auto_cand_nom_flag IN pa_project_assignments.enable_auto_cand_nom_flag%TYPE := FND_API.G_MISS_CHAR
2493 ,p_staffing_owner_person_id IN pa_project_assignments.staffing_owner_person_id%TYPE := FND_API.G_MISS_NUM
2494 ,p_staffing_owner_name IN per_people_f.full_name%TYPE := FND_API.G_MISS_CHAR
2495 ,p_fcst_job_name IN per_jobs.name%TYPE := FND_API.G_MISS_CHAR
2496 ,p_fcst_job_group_name IN per_job_groups.displayed_name%TYPE := FND_API.G_MISS_CHAR
2497 ,p_expenditure_org_name IN per_organization_units.name%TYPE := FND_API.G_MISS_CHAR
2498 ,p_exp_organization_name IN per_organization_units.name%TYPE := FND_API.G_MISS_CHAR
2499 ,p_exception_type_code IN VARCHAR2 := FND_API.G_MISS_CHAR
2500 ,p_change_start_date IN DATE := FND_API.G_MISS_DATE
2501 ,p_change_end_date IN DATE := FND_API.G_MISS_DATE
2502 ,p_change_rqmt_status_code IN VARCHAR2 := FND_API.G_MISS_CHAR
2503 ,p_change_asgmt_status_code IN VARCHAR2 := FND_API.G_MISS_CHAR
2504 ,p_change_start_date_tbl IN SYSTEM.PA_DATE_TBL_TYPE := NULL
2505 ,p_change_end_date_tbl IN SYSTEM.PA_DATE_TBL_TYPE := NULL
2506 ,p_monday_hours_tbl IN SYSTEM.PA_NUM_TBL_TYPE := NULL
2507 ,p_tuesday_hours_tbl IN SYSTEM.PA_NUM_TBL_TYPE := NULL
2508 ,p_wednesday_hours_tbl IN SYSTEM.PA_NUM_TBL_TYPE := NULL
2509 ,p_thursday_hours_tbl IN SYSTEM.PA_NUM_TBL_TYPE := NULL
2510 ,p_friday_hours_tbl IN SYSTEM.PA_NUM_TBL_TYPE := NULL
2511 ,p_saturday_hours_tbl IN SYSTEM.PA_NUM_TBL_TYPE := NULL
2512 ,p_sunday_hours_tbl IN SYSTEM.PA_NUM_TBL_TYPE := NULL
2513 ,p_non_working_day_flag IN VARCHAR2 := 'N'
2514 ,p_change_hours_type_code IN VARCHAR2 := FND_API.G_MISS_CHAR
2515 ,p_hrs_per_day IN NUMBER := FND_API.G_MISS_NUM
2516 ,p_calendar_percent IN NUMBER := FND_API.G_MISS_NUM
2517 ,p_change_calendar_type_code IN VARCHAR2 := FND_API.G_MISS_CHAR
2518 ,p_change_calendar_name IN VARCHAR2 := FND_API.G_MISS_CHAR
2519 ,p_change_calendar_id IN NUMBER := FND_API.G_MISS_NUM
2520 ,p_duration_shift_type_code IN VARCHAR2 := FND_API.G_MISS_CHAR
2521 ,p_duration_shift_unit_code IN VARCHAR2 := FND_API.G_MISS_CHAR
2522 ,p_num_of_shift IN NUMBER := FND_API.G_MISS_NUM
2523 ,p_approver1_id_tbl IN SYSTEM.pa_num_tbl_type := prm_empty_num_tbl
2524 ,p_approver1_name_tbl IN SYSTEM.pa_varchar2_240_tbl_type := prm_empty_varchar2_240_tbl
2525 ,p_approver2_id_tbl IN SYSTEM.pa_num_tbl_type := prm_empty_num_tbl
2526 ,p_approver2_name_tbl IN SYSTEM.pa_varchar2_240_tbl_type := prm_empty_varchar2_240_tbl
2527 ,p_appr_over_auth_flag IN VARCHAR2 := 'N'
2528 ,p_note_to_all_approvers IN VARCHAR2 := FND_API.G_MISS_CHAR
2529 ,p_competence_id_tbl IN SYSTEM.pa_num_tbl_type := prm_empty_num_tbl
2530 ,p_competence_name_tbl IN SYSTEM.pa_varchar2_240_tbl_type := prm_empty_varchar2_240_tbl
2531 ,p_competence_alias_tbl IN SYSTEM.pa_varchar2_30_tbl_type := prm_empty_varchar2_30_tbl
2532 ,p_rating_level_id_tbl IN SYSTEM.pa_num_tbl_type := prm_empty_num_tbl
2533 ,p_mandatory_flag_tbl IN SYSTEM.pa_varchar2_1_tbl_type := prm_empty_varchar2_1_tbl
2534 ,p_resolve_con_action_code IN VARCHAR2 := FND_API.G_MISS_CHAR
2535 ,p_api_version IN NUMBER := 1.0
2536 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
2537 ,p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM
2538 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
2539 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
2540 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2541 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2542 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2543 )
2544 IS
2545
2546 l_project_manager_person_id NUMBER ;
2547 l_project_manager_name VARCHAR2(200);
2548 l_project_party_id NUMBER ;
2549 l_project_role_id NUMBER ;
2550 l_project_role_name VARCHAR2(80);
2551 l_admin_project VARCHAR2(1); --Variable which denotes if a project is an admin project or not
2552 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
2553 l_error_message_code fnd_new_messages.message_name%TYPE;
2554 l_assignment_start_date DATE;
2555 l_approver1_person_id NUMBER;
2556 l_approver1_person_name VARCHAR2(200);
2557 l_approver2_person_id NUMBER;
2558 l_approver2_person_name VARCHAR2(200);
2559 l_resource_type_id NUMBER;--Used in Name ID validation
2560 l_msg_index_out NUMBER;
2561
2562 --These are the local copies of the approver ids used in Name ID validation
2563 l_approver1_id_tbl SYSTEM.pa_num_tbl_type;
2564 l_approver2_id_tbl SYSTEM.pa_num_tbl_type;
2565 l_project_id NUMBER;
2566
2567 BEGIN
2568
2569 -- Initialize the Error Stack
2570 PA_DEBUG.init_err_stack('PA_ASSIGNMENT_APPROVAL_PUB.mass_submit_for_assignment_approval');
2571
2572 --Log Message
2573 IF P_DEBUG_MODE = 'Y' THEN
2574 PA_DEBUG.write_log
2575 ( x_module => 'pa.plsql.PA_ASSIGNMENT_APPROVAL_PUB.mass_submit_for_assignment_approval.begin'
2576 ,x_msg => 'Beginning of mass_submit_for_assignment_approval'
2577 ,x_log_level => 1);
2578 END IF;
2579
2580 -- Initialize the return status to success
2581 x_return_status := FND_API.G_RET_STS_SUCCESS;
2582
2583 --Clear the global PL/SQL message table
2584 FND_MSG_PUB.initialize;
2585
2586 -- Issue API savepoint if the transaction is to be committed
2587 IF p_commit = FND_API.G_TRUE THEN
2588 SAVEPOINT MASS_SUBMIT_ASGN_APPRVL;
2589 END IF;
2590
2591 -----------------------------------------
2592 --Initialize the local approver id tables
2593 -----------------------------------------
2594 l_approver1_id_tbl := p_approver1_id_tbl;
2595 l_approver2_id_tbl := p_approver2_id_tbl;
2596
2597 --Getting Project id from project number if
2598 --project id = null
2599
2600 l_project_id := p_project_id;
2601
2602 IF l_project_id is null THEN
2603
2604 SELECT project_id
2605 INTO l_project_id
2606 FROM pa_projects_all
2607 WHERE segment1 = p_project_number;
2608
2609 END IF;
2610
2611 --Get the project type
2612 SELECT NVL(pt.administrative_flag,'N') admin_flag
2613 INTO l_admin_project
2614 FROM pa_projects_all pap,
2615 pa_project_types_all pt
2616 WHERE pap.project_id = l_project_id
2617 AND pt.project_type = pap.project_type
2618 -- AND nvl(pap.org_id, -99) = nvl(pt.org_id, -99); /* Added nvl for bug#2467666 */ -R12: Bug 4633092
2619 AND pap.org_id = pt.org_id;
2620
2621 -------------------------------------------------------------
2622 --Validate if a project manager exists for non-admin projects
2623 -------------------------------------------------------------
2624 IF l_admin_project = 'N' THEN
2625
2626 --Log Message
2627 IF P_DEBUG_MODE = 'Y' THEN
2628 PA_DEBUG.write_log
2629 (x_module => 'PA_ASSIGNMENT_APPROVAL_PUB.mass_submit_for_assignment_approval.check_prj_manager.'
2630 ,x_msg => 'Check if project manger exists.'
2631 ,x_log_level => 1);
2632 END IF;
2633
2634 pa_project_parties_utils.get_curr_proj_mgr_details
2635 ( p_project_id => l_project_id
2636 ,x_manager_person_id => l_project_manager_person_id
2637 ,x_manager_name => l_project_manager_name
2638 ,x_project_party_id => l_project_party_id
2639 ,x_project_role_id => l_project_role_id
2640 ,x_project_role_name => l_project_role_name
2641 ,x_return_status => l_return_status
2642 ,x_error_message_code => l_error_message_code );
2643
2644 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2645 PA_UTILS.Add_Message( p_app_short_name => 'PA'
2646 ,p_msg_name => l_error_message_code);
2647 END IF;
2648
2649 l_error_message_code := FND_API.G_MISS_CHAR;
2650
2651 END IF;
2652
2653 IF l_return_status <> FND_API.G_RET_STS_ERROR THEN
2654
2655 ---------------------------------
2656 --Call the mass transaction API
2657 ---------------------------------
2658 PA_MASS_ASGMT_TRX.Start_Mass_Asgmt_Trx_Wf(
2659 p_mode => p_mode
2660 ,p_action => p_action
2661 ,p_resource_id_tbl => p_resource_id_tbl
2662 ,p_assignment_id_tbl => p_assignment_id_tbl
2663 ,p_assignment_name => p_assignment_name
2664 ,p_assignment_type => p_assignment_type
2665 ,p_status_code => p_status_code
2666 ,p_multiple_status_flag => p_multiple_status_flag
2667 ,p_staffing_priority_code => p_staffing_priority_code
2668 ,p_project_id => l_project_id
2669 ,p_project_role_id => p_project_role_id
2670 ,p_role_list_id => p_role_list_id
2671 ,p_project_subteam_id => p_project_subteam_id
2672 ,p_description => p_description
2673 ,p_append_description_flag => p_append_description_flag
2674 ,p_start_date => p_start_date
2675 ,p_end_date => p_end_date
2676 ,p_extension_possible => p_extension_possible
2677 ,p_min_resource_job_level => p_min_resource_job_level
2678 ,p_max_resource_job_level => p_max_resource_job_level
2679 ,p_additional_information => p_additional_information
2680 ,p_append_information_flag => p_append_information_flag
2681 ,p_location_id => p_location_id
2682 ,p_work_type_id => p_work_type_id
2683 ,p_calendar_type => p_calendar_type
2684 ,p_calendar_id => p_calendar_id
2685 ,p_resource_calendar_percent => p_resource_calendar_percent
2686 ,p_project_name => p_project_name
2687 ,p_project_number => p_project_number
2688 ,p_project_subteam_name => p_project_subteam_name
2689 ,p_project_status_name => p_project_status_name
2690 ,p_staffing_priority_name => p_staffing_priority_name
2691 ,p_project_role_name => p_project_role_name
2692 ,p_location_city => p_location_city
2693 ,p_location_region => p_location_region
2694 ,p_location_country_name => p_location_country_name
2695 ,p_location_country_code => p_location_country_code
2696 ,p_calendar_name => p_calendar_name
2697 ,p_work_type_name => p_work_type_name
2698 ,p_expense_owner => p_expense_owner
2699 ,p_expense_limit => p_expense_limit
2700 ,p_expense_limit_currency_code => p_expense_limit_currency_code
2701 ,p_fcst_tp_amount_type => p_fcst_tp_amount_type
2702 ,p_fcst_job_id => p_fcst_job_id
2703 ,p_fcst_job_group_id => p_fcst_job_group_id
2704 ,p_expenditure_org_id => p_expenditure_org_id
2705 ,p_expenditure_organization_id => p_expenditure_organization_id
2706 ,p_expenditure_type_class => p_expenditure_type_class
2707 ,p_expenditure_type => p_expenditure_type
2708 ,p_comp_match_weighting => p_comp_match_weighting
2709 ,p_avail_match_weighting => p_avail_match_weighting
2710 ,p_job_level_match_weighting => p_job_level_match_weighting
2711 ,p_search_min_availability => p_search_min_availability
2712 ,p_search_country_code => p_search_country_code
2713 ,p_search_country_name => p_search_country_name
2714 ,p_search_exp_org_struct_ver_id => p_search_exp_org_struct_ver_id
2715 ,p_search_exp_org_hier_name => p_search_exp_org_hier_name
2716 ,p_search_exp_start_org_id => p_search_exp_start_org_id
2717 ,p_search_exp_start_org_name => p_search_exp_start_org_name
2718 ,p_search_min_candidate_score => p_search_min_candidate_score
2719 ,p_enable_auto_cand_nom_flag => p_enable_auto_cand_nom_flag
2720 ,p_staffing_owner_person_id => p_staffing_owner_person_id
2721 ,p_staffing_owner_name => p_staffing_owner_name
2722 ,p_fcst_job_name => p_fcst_job_name
2723 ,p_fcst_job_group_name => p_fcst_job_group_name
2724 ,p_expenditure_org_name => p_expenditure_org_name
2725 ,p_exp_organization_name => p_exp_organization_name
2726 ,p_exception_type_code => p_exception_type_code
2727 ,p_change_start_date => p_change_start_date
2728 ,p_change_end_date => p_change_end_date
2729 ,p_change_rqmt_status_code => p_change_rqmt_status_code
2730 ,p_change_asgmt_status_code => p_change_asgmt_status_code
2731 ,p_change_start_date_tbl => p_change_start_date_tbl
2732 ,p_change_end_date_tbl => p_change_end_date_tbl
2733 ,p_monday_hours_tbl => p_monday_hours_tbl
2734 ,p_tuesday_hours_tbl => p_tuesday_hours_tbl
2735 ,p_wednesday_hours_tbl => p_wednesday_hours_tbl
2736 ,p_thursday_hours_tbl => p_thursday_hours_tbl
2737 ,p_friday_hours_tbl => p_friday_hours_tbl
2738 ,p_saturday_hours_tbl => p_saturday_hours_tbl
2739 ,p_sunday_hours_tbl => p_sunday_hours_tbl
2740 ,p_non_working_day_flag => p_non_working_day_flag
2741 ,p_change_hours_type_code => p_change_hours_type_code
2742 ,p_hrs_per_day => p_hrs_per_day
2743 ,p_calendar_percent => p_calendar_percent
2744 ,p_change_calendar_type_code => p_change_calendar_type_code
2745 ,p_change_calendar_name => p_change_calendar_name
2746 ,p_change_calendar_id => p_change_calendar_id
2747 ,p_duration_shift_type_code => p_duration_shift_type_code
2748 ,p_duration_shift_unit_code => p_duration_shift_unit_code
2749 ,p_num_of_shift => p_num_of_shift
2750 ,p_approver1_id_tbl => l_approver1_id_tbl --The local updated table is passed
2751 ,p_approver1_name_tbl => p_approver1_name_tbl
2752 ,p_approver2_id_tbl => l_approver2_id_tbl --The local updated table is passed
2753 ,p_approver2_name_tbl => p_approver2_name_tbl
2754 ,p_appr_over_auth_flag => p_appr_over_auth_flag
2755 ,p_note_to_all_approvers => p_note_to_all_approvers
2756 ,p_competence_id_tbl => p_competence_id_tbl
2757 ,p_competence_name_tbl => p_competence_name_tbl
2758 ,p_competence_alias_tbl => p_competence_alias_tbl
2759 ,p_rating_level_id_tbl => p_rating_level_id_tbl
2760 ,p_mandatory_flag_tbl => p_mandatory_flag_tbl
2761 ,p_resolve_con_action_code => p_resolve_con_action_code
2762 ,x_return_status => l_return_status );
2763
2764 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2765 x_return_status := l_return_status;
2766 END IF;
2767
2768 END IF;
2769
2770 x_msg_count := FND_MSG_PUB.Count_Msg;
2771
2772 -- IF the number of messages is 1 then fetch the message code from
2773 -- the stack and return its text
2774 IF x_msg_count = 1 THEN
2775 pa_interface_utils_pub.get_messages
2776 ( p_encoded => FND_API.G_TRUE
2777 ,p_msg_index => 1
2778 ,p_data => x_msg_data
2779 ,p_msg_index_out => l_msg_index_out );
2780 END IF;
2781
2782 -- Reset the error stack when returning to the calling program
2783 PA_DEBUG.Reset_Err_Stack;
2784
2785 -- If g_error_exists is TRUE then set the x_return_status to 'E'
2786 IF FND_MSG_PUB.Count_Msg >0 THEN
2787 x_return_status := FND_API.G_RET_STS_ERROR;
2788 END IF;
2789
2790 EXCEPTION
2791 WHEN OTHERS THEN
2792
2793 IF p_commit = FND_API.G_TRUE THEN
2794 ROLLBACK TO MASS_SUBMIT_ASGN_APPRVL;
2795 END IF;
2796
2797 -- Set the excetption Message and the stack
2798 FND_MSG_PUB.add_exc_msg
2799 ( p_pkg_name => 'PA_ASSIGNMENT_APPROVAL_PUB.mass_submit_for_asgmt_aprvl'
2800 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
2801
2802 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2803 RAISE;
2804
2805 END mass_submit_for_asgmt_aprvl;
2806
2807 ---------------------------------------------------------------------------
2808 --This API is called from the Mass Transaction Server API
2809 --This is the main API which starts the assignment
2810 --approval process
2811 --The IN parameters are assignments table which are submitted for
2812 --approval along with 2 approvers tables
2813 ---------------------------------------------------------------------------
2814 PROCEDURE mass_assignment_approval
2815 ( p_project_id IN pa_project_assignments.project_id%TYPE := FND_API.G_MISS_NUM
2816 ,p_mode IN VARCHAR2
2817 ,p_assignment_id_tbl IN SYSTEM.pa_num_tbl_type := prm_empty_num_tbl
2818 ,p_approver1_id_tbl IN SYSTEM.pa_num_tbl_type := prm_empty_num_tbl
2819 ,p_approver1_name_tbl IN SYSTEM.pa_varchar2_240_tbl_type := prm_empty_varchar2_240_tbl
2820 ,p_approver2_id_tbl IN SYSTEM.pa_num_tbl_type := prm_empty_num_tbl
2821 ,p_approver2_name_tbl IN SYSTEM.pa_varchar2_240_tbl_type := prm_empty_varchar2_240_tbl
2822 ,p_overriding_authority_flag IN VARCHAR2 := 'N'
2823 ,p_submitter_user_id IN NUMBER := FND_API.G_MISS_NUM
2824 ,p_note_to_all_approvers IN VARCHAR2 := FND_API.G_MISS_CHAR
2825 ,p_conflict_group_id IN NUMBER := FND_API.G_MISS_NUM
2826 ,p_update_info_doc IN VARCHAR2 := FND_API.G_MISS_CHAR
2827 ,p_api_version IN NUMBER := 1.0
2828 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
2829 ,p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM
2830 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
2831 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
2832 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2833 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2834 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2835 )
2836 IS
2837
2838 l_return_status VARCHAR2(1);
2839 l_error_message_code fnd_new_messages.message_name%TYPE;
2840 x_error_message_code fnd_new_messages.message_name%TYPE;
2841 l_msg_index_out NUMBER;
2842 l_msg_count NUMBER;
2843 l_msg_data VARCHAR2(2000);
2844 l_data VARCHAR2(2000);
2845 l_assignment_status VARCHAR2(10) := PA_ASSIGNMENT_APPROVAL_PUB.g_submit_action;
2846 l_approver1_id_tbl SYSTEM.pa_num_tbl_type;
2847 l_approver1_name_tbl SYSTEM.pa_varchar2_240_tbl_type;
2848 l_approver2_id_tbl SYSTEM.pa_num_tbl_type;
2849 l_approver2_name_tbl SYSTEM.pa_varchar2_240_tbl_type;
2850 l_assignment_id_tbl SYSTEM.pa_num_tbl_type;
2851 l_apr1_res_auth VARCHAR2(1) := 'Y';
2852 l_apr2_res_auth VARCHAR2(1) := 'Y';
2853 l_new_asgmt_flag VARCHAR2(1);
2854 x_aprvl_required VARCHAR2(1);
2855 l_routing_order NUMBER;
2856 l_change_id NUMBER;
2857 l_record_version_number NUMBER;
2858 p_record_version_number NUMBER;
2859 l_apprvl_status_code pa_project_assignments.apprvl_status_code%TYPE;
2860 l_project_super_user_submitter VARCHAR2(1);
2861 l_display_name VARCHAR2(360); /* Modified length from 200 to 360 for bug 3148857 */
2862 l_approver_name VARCHAR2(320); /* Modified length from 200 to 320 for bug 3148857 */
2863
2864 CURSOR resource_id (p_assignment_id NUMBER) IS
2865 SELECT resource_id
2866 FROM pa_project_assignments
2867 WHERE assignment_id = p_assignment_id;
2868
2869 l_resource_id NUMBER;
2870
2871 CURSOR get_rec_num ( p_assignment_id IN NUMBER )IS
2872 SELECT record_version_number
2873 FROM pa_project_assignments
2874 WHERE assignment_id = p_assignment_id;
2875
2876 CURSOR l_stus_csr IS
2877 SELECT ps.enable_wf_flag
2878 FROM pa_project_statuses ps
2879 WHERE ps.project_status_code = PA_ASSIGNMENT_APPROVAL_PUB.g_submitted;
2880
2881 l_stus_rec l_stus_csr%ROWTYPE;
2882
2883 l_approvers_list_tbl PA_CLIENT_EXTN_ASGMT_WF.Users_List_Tbltyp ;
2884 l_out_approvers_list_tbl PA_CLIENT_EXTN_ASGMT_WF.Users_List_Tbltyp ;
2885 l_number_of_approvers NUMBER := 0;
2886 l_approvers_list_rec PA_CLIENT_EXTN_ASGMT_WF.Users_List_Rectyp ;
2887 l_approver_person_id NUMBER := 0;
2888
2889 l_group_id NUMBER;
2890 l_approver_group_id NUMBER;
2891
2892 CURSOR distinct_approvers IS
2893 SELECT distinct user_name
2894 FROM pa_wf_ntf_performers ntf,
2895 pa_project_assignments asgn
2896 WHERE ntf.group_id = l_group_id
2897 AND ntf.object_id1 = asgn.assignment_id
2898 AND asgn.apprvl_status_code = PA_ASSIGNMENT_APPROVAL_PUB.g_submitted
2899 AND ntf.routing_order = 1;
2900
2901 l_customer_id NUMBER;
2902 l_customer_name VARCHAR2(2000);
2903 l_project_manager_person_id NUMBER ;
2904 l_project_manager_name VARCHAR2(200);
2905 l_project_party_id NUMBER ;
2906 l_project_role_id NUMBER ;
2907 l_project_role_name VARCHAR2(80);
2908 l_submitter_person_id NUMBER;
2909 l_submitter_user_name VARCHAR2(100); /* Modified length from 30 to 100 for bug 3148857 */
2910
2911 CURSOR l_projects_csr(l_project_id IN NUMBER) IS
2912 SELECT pap.project_id project_id,
2913 pap.name name,
2914 pap.segment1 segment1,
2915 pap.carrying_out_organization_id carrying_out_organization_id,
2916 pap.location_id,
2917 hr.name organization_name,
2918 NVL(pt.administrative_flag,'N') admin_flag
2919 FROM pa_projects_all pap,
2920 hr_all_organization_units_tl hr, -- Bug 4358492
2921 pa_project_types_all pt
2922 WHERE pap.project_id = l_project_id
2923 AND pap.carrying_out_organization_id = hr.organization_id
2924 AND pt.project_type = pap.project_type
2925 AND pap.org_id = pt.org_id -- Bug 4358492
2926 AND userenv('LANG') = hr.language; -- Bug 4358492
2927
2928 l_projects_rec l_projects_csr%ROWTYPE;
2929
2930 CURSOR get_submitter_details IS
2931 SELECT employee_id,
2932 user_name
2933 FROM fnd_user
2934 WHERE user_id = p_submitter_user_id;
2935 l_submitter_rec get_submitter_details%ROWTYPE;
2936
2937 l_asgn_approval_status pa_project_assignments.apprvl_status_code%TYPE;
2938
2939 PROCESS_ASSIGNMENT_EXCEPTION EXCEPTION;
2940 NO_WORKFLOW_EXCEPTION EXCEPTION;
2941 INVALID_STATUS EXCEPTION;
2942
2943 l_num_apr_asgns NUMBER := 0;
2944 l_num_rej_asgns NUMBER := 0;
2945
2946 --Default value is Y
2947 --If any assignment is submitted then flag is set to N
2948 l_error_flag VARCHAR2(1) := 'Y';
2949 l_error_count NUMBER;
2950
2951 l_appr_asgmt_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
2952
2953 l_privelege varchar2(30);
2954 l_resource_super_user varchar2(1);
2955
2956 BEGIN
2957
2958 -- Initialize the Error Stack
2959 PA_DEBUG.init_err_stack('PA_ASSIGNMENT_APPROVAL_PUB.mass_assignment_approval');
2960
2961 --Log Message
2962 IF P_DEBUG_MODE = 'Y' THEN
2963 PA_DEBUG.write_log
2964 ( x_module => 'pa.plsql.PA_ASSIGNMENT_APPROVAL_PUB.mass_assignment_approval.begin'
2965 ,x_msg => 'Beginning of mass_assignment_approval'
2966 ,x_log_level => 1);
2967 END IF;
2968
2969 -- Initialize the return status to success
2970 x_return_status := FND_API.G_RET_STS_SUCCESS;
2971
2972 --Clear the global PL/SQL message table
2973 FND_MSG_PUB.initialize;
2974
2975 -- Issue API savepoint if the transaction is to be committed
2976 IF p_commit = FND_API.G_TRUE THEN
2977 SAVEPOINT MASS_ASGN_APPRVL;
2978 END IF;
2979
2980 ---------------------------------------------------------------------
2981 --Generate the group id for this mass transaction
2982 --This group id is inserted for every record in PA_WF_NTF_PERFORMERS
2983 --The group_id is inserted from the sequence PA_WF_NTF_PERFORMERS_S
2984 --The approver_group_id is initialy null
2985 --The group id is used to group all records which belong to this
2986 --mass transaction and is used in sending FYI notifications to
2987 --managers in the end when there are no pending approvals
2988 ---------------------------------------------------------------------
2989 SELECT PA_WF_NTF_PERFORMERS_S.nextval
2990 INTO l_group_id
2991 FROM dual;
2992
2993 OPEN get_submitter_details;
2994 FETCH get_submitter_details INTO l_submitter_rec;
2995 CLOSE get_submitter_details;
2996
2997 l_submitter_person_id := l_submitter_rec.employee_id;
2998 l_submitter_user_name := l_submitter_rec.user_name;
2999
3000 ------------------------------------------
3001 --Validate the Name Id for approvers
3002 ------------------------------------------
3003 log_message('Before Calling Validate_approver_name_id');
3004 /*Added a new parameter p_overriding_authority_flag for bug 3213509*/
3005 Validate_approver_name_id
3006 ( p_project_id => p_project_id
3007 ,p_assignment_id_tbl => p_assignment_id_tbl
3008 ,p_approver1_id_tbl => p_approver1_id_tbl
3009 ,p_approver1_name_tbl => p_approver1_name_tbl
3010 ,p_approver2_id_tbl => p_approver2_id_tbl
3011 ,p_approver2_name_tbl => p_approver2_name_tbl
3012 ,p_submitter_user_id => p_submitter_user_id
3013 ,p_group_id => l_group_id
3014 ,p_overriding_authority_flag => p_overriding_authority_flag
3015 ,x_assignment_id_tbl => l_assignment_id_tbl
3016 ,x_approver1_id_tbl => l_approver1_id_tbl
3017 ,x_approver2_id_tbl => l_approver2_id_tbl
3018 ,x_return_status => x_return_status
3019 ,x_msg_count => x_msg_count
3020 ,x_msg_data => x_msg_data);
3021
3022 log_message('After Calling Validate_approver_name_id');
3023
3024 ---------------------------------------------------------------------------------------
3025 --Getting the Project details once instead of in every call to start_mass_approval_flow
3026 ---------------------------------------------------------------------------------------
3027 OPEN l_projects_csr( p_project_id);
3028 FETCH l_projects_csr INTO l_projects_rec;
3029 IF l_projects_csr%NOTFOUND THEN
3030
3031 pa_utils.add_message (p_app_short_name => 'PA',
3032 p_msg_name => 'PA_INVALID_PROJECT_ID');
3033
3034 END IF;
3035 CLOSE l_projects_csr;
3036
3037 PA_ASGMT_WFSTD.Check_And_Get_Proj_Customer
3038 (p_project_id => p_project_id
3039 ,x_customer_id => l_customer_id
3040 ,x_customer_name => l_customer_name );
3041
3042 -- Get the project manager details
3043 pa_project_parties_utils.get_curr_proj_mgr_details
3044 (p_project_id => l_projects_rec.project_id
3045 ,x_manager_person_id => l_project_manager_person_id
3046 ,x_manager_name => l_project_manager_name
3047 ,x_project_party_id => l_project_party_id
3048 ,x_project_role_id => l_project_role_id
3049 ,x_project_role_name => l_project_role_name
3050 ,x_return_status => l_return_status
3051 ,x_error_message_code => l_error_message_code );
3052
3053 -- Only non-admin projects require a manager
3054 IF l_projects_rec.admin_flag = 'N' THEN
3055 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3056 pa_utils.add_message (p_app_short_name => 'PA',
3057 p_msg_name => l_error_message_code);
3058 END IF;
3059 END IF;
3060
3061 log_message('After Getting Project information');
3062
3063 -- Status Validation to check if workflow is enabled. If status is not valid,
3064 -- RAISE an ERROR.
3065 -- Also if Workflow is disabled - then exit
3066 OPEN l_stus_csr;
3067 FETCH l_stus_csr INTO l_stus_rec;
3068 IF l_stus_csr%NOTFOUND THEN
3069 l_return_status := FND_API.G_RET_STS_ERROR;
3070 l_error_message_code := 'PA_INVALID_STATUS_CODE';
3071 pa_utils.add_message (p_app_short_name => 'PA'
3072 ,p_msg_name => 'PA_INVALID_STATUS_CODE');
3073 RAISE INVALID_STATUS;
3074 log_message('Error in Workflow enabled check');
3075 END IF;
3076 CLOSE l_stus_csr;
3077
3078 IF NVL(l_stus_rec.enable_wf_flag,'N') = 'N' THEN
3079 pa_utils.add_message (p_app_short_name => 'PA'
3080 ,p_msg_name => 'PA_INVALID_STATUS_CODE');
3081 RAISE NO_WORKFLOW_EXCEPTION;
3082 END IF;
3083
3084 log_message('Completed Workflow enabled check');
3085
3086 --Initialize locals
3087 l_approver1_name_tbl := p_approver1_name_tbl;
3088 l_approver2_name_tbl := p_approver2_name_tbl;
3089
3090 FOR i in 1..l_assignment_id_tbl.COUNT LOOP
3091
3092 SAVEPOINT PROCESS_ASSIGNMENT_SUBMISSION;
3093
3094 BEGIN
3095
3096 log_message('Loop:' || i);
3097
3098 IF l_assignment_id_tbl(i) IS NOT NULL THEN
3099
3100 log_message('approver 1 id:' || l_approver1_id_tbl(i));
3101 log_message('approver 2 id:' || l_approver2_id_tbl(i));
3102
3103 OPEN resource_id ( l_assignment_id_tbl(i));
3104 FETCH resource_id INTO l_resource_id;
3105 CLOSE resource_id;
3106
3107 -------------------------------------------
3108 --Check if overriding authority flag is set
3109 -------------------------------------------
3110 IF p_overriding_authority_flag = 'Y' THEN
3111
3112 log_message('Overriding auth flag set');
3113
3114 ------------------------------------------------------------------------------------
3115 --Below logic determines if the submitter is the resource super user responsibility
3116 --There are 2 checks done
3117 -- 1. Profile check
3118 -- 2. User has privelege to confirm assignments
3119 --NOTE: The MASS ASSIGNMENT workflow has already initialized the submitters
3120 --responsibility id and product id and user id
3121 -----------------------------------------------------------------------------------
3122 l_project_super_user_submitter := fnd_profile.value_specific('PA_SUPER_RESOURCE',
3123 p_submitter_user_id,
3124 fnd_global.resp_id,
3125 fnd_global.resp_appl_id);
3126
3127 IF l_project_super_user_submitter = 'Y' THEN
3128
3129 IF l_projects_rec.admin_flag = 'N' THEN
3130 l_privelege := 'PA_ASN_CONFIRM';
3131 ELSIF l_projects_rec.admin_flag = 'Y' THEN
3132 l_privelege := 'PA_ADM_ASN_CONFIRM';
3133 END IF;
3134
3135 IF fnd_function.test(l_privelege) THEN
3136 l_resource_super_user := 'Y';
3137 ELSE
3138 l_resource_super_user := 'N';
3139 END IF;
3140
3141 END IF;
3142
3143 IF l_resource_super_user = 'Y' OR
3144 pa_resource_utils.check_user_has_res_auth (l_submitter_person_id, l_resource_id) = 'Y' THEN
3145 l_assignment_status := PA_ASSIGNMENT_APPROVAL_PUB.g_approve_action;
3146 ELSE --submitter does not have resource authority
3147
3148 ----------------------------------------------------------------------
3149 --If submitter has no resource authority and both approvers are null
3150 --assignment cannot be approved as there are no approvers
3151 ----------------------------------------------------------------------
3152 IF l_approver1_id_tbl(i) is null AND l_approver2_id_tbl(i) is null THEN
3153
3154 --Add error message to error stack
3155 l_error_message_code := 'PA_RESOURCE_NO_AUTH';
3156 PA_UTILS.Add_Message( p_app_short_name => 'PA'
3157 ,p_msg_name => l_error_message_code);
3158
3159 RAISE PROCESS_ASSIGNMENT_EXCEPTION;
3160 ELSE
3161 l_assignment_status := PA_ASSIGNMENT_APPROVAL_PUB.g_submit_action;
3162 END IF;
3163 END IF;
3164
3165 ELSIF l_approver1_id_tbl(i) = l_submitter_person_id THEN
3166
3167 IF l_approver2_id_tbl(i) is null THEN
3168 l_assignment_status := PA_ASSIGNMENT_APPROVAL_PUB.g_approve_action;
3169 ELSIF l_approver2_id_tbl(i) = l_submitter_person_id THEN
3170 l_assignment_status := PA_ASSIGNMENT_APPROVAL_PUB.g_approve_action;
3171 ELSE
3172 l_approver1_id_tbl(i) := l_approver2_id_tbl(i);
3173 l_approver1_name_tbl(i) := l_approver2_name_tbl(i);
3174 l_approver2_id_tbl(i) := null;
3175 l_approver2_name_tbl(i):= null;
3176 l_assignment_status := PA_ASSIGNMENT_APPROVAL_PUB.g_submit_action;
3177 END IF;
3178
3179 ELSIF l_approver1_id_tbl(i) is null AND l_approver2_id_tbl(i) is null THEN
3180
3181 --Add error message to error stack
3182 l_error_message_code := 'PA_RESOURCE_NO_AUTH';
3183 PA_UTILS.Add_Message( p_app_short_name => 'PA'
3184 ,p_msg_name => l_error_message_code);
3185 log_message('No approver');
3186 RAISE PROCESS_ASSIGNMENT_EXCEPTION;
3187
3188 END IF; --end l_overriding_authority_flag = 'Y
3189
3190 IF l_approver2_id_tbl(i) = l_submitter_person_id THEN
3191 l_approver2_id_tbl(i) := null;
3192 END IF;
3193
3194 --------------------------------
3195 --Need to reorder approver list
3196 --------------------------------
3197 IF l_approver1_id_tbl(i) is null THEN
3198 l_approver1_id_tbl(i) := l_approver2_id_tbl(i);
3199 l_approver1_name_tbl(i) := l_approver2_name_tbl(i);
3200 l_approver2_id_tbl(i) := null;
3201 l_approver2_name_tbl(i):= null;
3202 END IF;
3203
3204 ---------------------------------------------------------------------------------
3205 --Validate approver only if l_assignment_status = g_submit_action
3206 --Validate Approver One and Approver Two has resource authority over the resource
3207 --for that assignment, otherwise raise an error.
3208 ---------------------------------------------------------------------------------
3209 IF l_assignment_status = PA_ASSIGNMENT_APPROVAL_PUB.g_submit_action THEN
3210
3211
3212
3213 log_message('Checkin resource authority');
3214 log_message('Approver 1: ' || l_approver1_id_tbl(i));
3215 log_message('Approver 2: ' || l_approver2_id_tbl(i));
3216 log_message('Resource : ' || l_resource_id);
3217
3218 l_apr1_res_auth := pa_resource_utils.check_user_has_res_auth (l_approver1_id_tbl(i), l_resource_id);
3219
3220 IF l_approver2_id_tbl(i) is not null THEN
3221 l_apr2_res_auth := pa_resource_utils.check_user_has_res_auth (l_approver2_id_tbl(i), l_resource_id);
3222 END IF;
3223
3224 IF l_apr1_res_auth = 'N' OR l_apr2_res_auth = 'N' THEN
3225
3226 PA_UTILS.Add_Message( p_app_short_name => 'PA'
3227 ,p_msg_name => 'PA_RESOURCE_NO_AUTH');
3228 log_message('No resource authority 1');
3229 RAISE PROCESS_ASSIGNMENT_EXCEPTION;
3230
3231 END IF;
3232 END IF;
3233
3234 ------------------------------------------------------------------
3235 --When l_assignment_status is not null implies that the assignment
3236 --can be processed for assignment approval
3237 ------------------------------------------------------------------
3238 IF l_assignment_status = PA_ASSIGNMENT_APPROVAL_PUB.g_submit_action THEN
3239
3240 l_new_asgmt_flag := PA_ASSIGNMENT_APPROVAL_PVT.Is_New_Assignment( l_assignment_id_tbl(i) );
3241
3242 IF l_new_asgmt_flag = 'N' THEN
3243
3244 PA_ASSIGNMENT_APPROVAL_PVT.check_approval_required
3245 ( l_assignment_id_tbl(i)
3246 ,l_new_asgmt_flag
3247 ,x_aprvl_required
3248 ,l_return_status );
3249
3250 IF x_aprvl_required = 'N' THEN
3251 l_assignment_status := PA_ASSIGNMENT_APPROVAL_PUB.g_approve_action;
3252 END IF;
3253
3254 END IF;
3255
3256 END IF; --end l_assignment_status = 'SUBMIT'
3257
3258 ----------------------------------------------------------
3259 --Store previous value of assignment status to be inserted into
3260 --pa_wf_ntf_performers table
3261 ----------------------------------------------------------
3262 BEGIN
3263 SELECT apprvl_status_code
3264 INTO l_asgn_approval_status
3265 FROM pa_project_assignments
3266 WHERE assignment_id = l_assignment_id_tbl(i);
3267 EXCEPTION
3268 WHEN NO_DATA_FOUND THEN
3269 l_asgn_approval_status := null;
3270 END;
3271
3272 IF l_assignment_status = PA_ASSIGNMENT_APPROVAL_PUB.g_approve_action THEN
3273
3274 log_message('Approve action for loop' || i);
3275
3276 OPEN get_rec_num ( l_assignment_id_tbl(i) );
3277 FETCH get_rec_num INTO p_record_version_number;
3278 CLOSE get_rec_num;
3279
3280 PA_ASSIGNMENT_APPROVAL_PVT.Update_Approval_Status
3281 ( p_assignment_id => l_assignment_id_tbl(i)
3282 ,p_action_code => PA_ASSIGNMENT_APPROVAL_PUB.g_approve_action
3283 ,p_note_to_approver => p_note_to_all_approvers
3284 ,p_record_version_number => p_record_version_number
3285 ,x_apprvl_status_code => l_apprvl_status_code
3286 ,x_change_id => l_change_id
3287 ,x_record_version_number => l_record_version_number
3288 ,x_return_status => l_return_status
3289 ,x_msg_count => l_msg_count
3290 ,x_msg_data => l_msg_data);
3291
3292 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3293 l_return_status := FND_API.G_MISS_CHAR;
3294 log_message('Error in Update approval status 1');
3295 RAISE PROCESS_ASSIGNMENT_EXCEPTION;
3296 END IF;
3297
3298 -- resolve remaining conflicts by taking action chosen by user
3299 PA_SCHEDULE_PVT.resolve_conflicts (p_conflict_group_id => p_conflict_group_id
3300 ,p_assignment_id => l_assignment_id_tbl(i)
3301 ,x_return_status => l_return_status
3302 ,x_msg_count => l_msg_count
3303 ,x_msg_data => l_msg_data);
3304
3305 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3306 l_return_status := FND_API.G_MISS_CHAR;
3307 log_message('Error in conflict resolution');
3308 RAISE PROCESS_ASSIGNMENT_EXCEPTION;
3309 END IF;
3310
3311 ----------------------------------------------------------------------------
3312 --Insert this assignment into pa_wf_ntf_performers for sending manager
3313 --notifications. The approver is the submitter in this case
3314 ----------------------------------------------------------------------------
3315 IF l_submitter_user_name is not null THEN
3316
3317 INSERT INTO pa_wf_ntf_performers(
3318 WF_TYPE_CODE
3319 ,ITEM_TYPE
3320 ,ITEM_KEY
3321 ,OBJECT_ID1
3322 ,OBJECT_ID2
3323 ,GROUP_ID
3324 ,USER_NAME
3325 ,USER_TYPE
3326 ,ROUTING_ORDER
3327 ,APPROVAL_STATUS)
3328 VALUES ('MASS_ASSIGNMENT_APPROVAL'
3329 ,'-1'
3330 ,'-1'
3331 ,l_assignment_id_tbl(i)
3332 ,-1
3333 ,l_group_id
3334 ,l_submitter_user_name
3335 ,'APPROVER'
3336 ,1
3337 ,l_asgn_approval_status
3338 );
3339 END IF;
3340
3341 -------------------------------------------------------
3342 --Process the resource notification for this assignment
3343 -------------------------------------------------------
3344 PA_ASGMT_WFSTD.process_res_fyi_notification
3345 ( p_project_id => p_project_id
3346 ,p_mode => p_mode
3347 ,p_assignment_id => l_assignment_id_tbl(i)
3348 ,p_project_name => l_projects_rec.name
3349 ,p_project_number => l_projects_rec.segment1
3350 ,p_project_manager => l_project_manager_name
3351 ,p_project_org => l_projects_rec.organization_name
3352 ,p_project_cus => l_customer_name
3353 ,p_conflict_group_id => p_conflict_group_id
3354 ,x_return_status => l_return_status
3355 ,x_msg_count => l_msg_count
3356 ,x_msg_data => l_msg_data);
3357
3358 ELSE --l_assignment_status = SUBMITTED
3359
3360 log_message('Submit action for loop' || i );
3361 log_message('Assignment Id: ' || l_assignment_id_tbl(i) );
3362
3363 OPEN get_rec_num ( l_assignment_id_tbl(i) );
3364 FETCH get_rec_num INTO p_record_version_number;
3365 CLOSE get_rec_num;
3366
3367 log_message('Record version number: ' || p_record_version_number );
3368
3369 PA_ASSIGNMENT_APPROVAL_PVT.Update_Approval_Status
3370 ( p_assignment_id => l_assignment_id_tbl(i)
3371 ,p_action_code => PA_ASSIGNMENT_APPROVAL_PUB.g_submit_action
3372 ,p_note_to_approver => p_note_to_all_approvers
3373 ,p_record_version_number => p_record_version_number
3374 ,x_apprvl_status_code => l_apprvl_status_code
3375 ,x_change_id => l_change_id
3376 ,x_record_version_number => l_record_version_number
3377 ,x_return_status => l_return_status
3378 ,x_msg_count => l_msg_count
3379 ,x_msg_data => l_msg_data);
3380
3381 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3382 l_return_status := FND_API.G_MISS_CHAR;
3383 log_message('Error in Update approval status 2');
3384 RAISE PROCESS_ASSIGNMENT_EXCEPTION;
3385 END IF;
3386
3387 ---------------------------------------------------------------------------
3388 --If no errors for this assignment then :
3389 -- i. call PA_CLIENT_EXTN_ASGMT_WF.Generate_Assignment_Approvers(..) to get
3390 -- any client defined Approvers.
3391 -- ii. insert into PA_WF_NTF_PERFORMERS (insert Approvers and assignment)
3392 --Note: All the inserted records have a unique identifier Group_id
3393 -- We can recognize all the records in PA_WF_NTF_PERFORMERS
3394 --for a given transaction using this identifier.
3395 ---------------------------------------------------------------------------
3396
3397 IF l_approvers_list_tbl.COUNT > 0 THEN
3398 l_approvers_list_tbl.DELETE ;
3399 END IF;
3400
3401 --Construct the in list of approvers
3402 FOR j IN 1..2 LOOP
3403 IF j = 1 AND l_approver1_id_tbl(i) IS NOT NULL THEN
3404 l_approvers_list_rec.Person_id := l_approver1_id_tbl(i);
3405
3406 --Get the approver User name (FND_USER)
3407 wf_directory.getusername
3408 (p_orig_system => 'PER'
3409 ,p_orig_system_id => l_approver1_id_tbl(i)
3410 ,p_name => l_approver_name
3411 ,p_display_name => l_display_name);
3412
3413 l_approvers_list_rec.User_Name := l_approver_name;
3414 l_approver_name := null; --reset
3415
3416 l_approvers_list_rec.Routing_Order := j;
3417 l_approvers_list_tbl(j) := l_approvers_list_rec;
3418
3419 END IF;
3420
3421 IF j = 2 AND l_approver2_id_tbl(i) IS NOT NULL THEN
3422 l_approvers_list_rec.Person_id := l_approver2_id_tbl(i);
3423
3424 --Get the approver User name (FND_USER)
3425 wf_directory.getusername
3426 (p_orig_system => 'PER'
3427 ,p_orig_system_id => l_approver2_id_tbl(i)
3428 ,p_name => l_approver_name
3429 ,p_display_name => l_display_name);
3430
3431 l_approvers_list_rec.User_Name := l_approver_name;
3432 l_approver_name := null; --reset
3433
3434 l_approvers_list_rec.Routing_Order := j;
3435 l_approvers_list_tbl(j) := l_approvers_list_rec;
3436
3437 END IF;
3438
3439 END LOOP;--end j loop
3440
3441 log_message('In Count:' || l_approvers_list_tbl.COUNT);
3442
3443 PA_CLIENT_EXTN_ASGMT_WF.Generate_Assignment_Approvers
3444 (p_assignment_id => l_assignment_id_tbl(i)
3445 ,p_project_id => p_project_id
3446 ,p_in_list_of_approvers => l_approvers_list_tbl
3447 ,x_out_list_of_approvers => l_out_approvers_list_tbl
3448 ,x_number_of_approvers => l_number_of_approvers );
3449
3450 log_message('OUT Count:' || l_out_approvers_list_tbl.COUNT);
3451
3452 -------------------------------------------------------
3453 --Insert All approvers into PA_WF_NTF_PERFORMERS Table
3454 -------------------------------------------------------
3455 l_routing_order := 0;
3456 FOR k in 1..l_out_approvers_list_tbl.COUNT LOOP
3457
3458 IF l_out_approvers_list_tbl(k).user_name is not null THEN
3459
3460 INSERT INTO pa_wf_ntf_performers(
3461 WF_TYPE_CODE
3462 ,ITEM_TYPE
3463 ,ITEM_KEY
3464 ,OBJECT_ID1
3465 ,OBJECT_ID2
3466 ,GROUP_ID
3467 ,USER_NAME
3468 ,USER_TYPE
3469 ,ROUTING_ORDER
3470 ,APPROVAL_STATUS)
3471 VALUES ('MASS_ASSIGNMENT_APPROVAL'
3472 ,'-1'
3473 ,'-1'
3474 ,l_assignment_id_tbl(i)
3475 ,-1
3476 ,l_group_id
3477 ,l_out_approvers_list_tbl(k).user_name
3478 ,'APPROVER'
3479 ,l_routing_order + 1
3480 ,l_asgn_approval_status
3481 );
3482 END IF;
3483
3484 l_routing_order := l_routing_order + 1;
3485
3486 END LOOP;--end k loop
3487
3488 --Set pending approval flag for assignment record in pa_project_assignments
3489 PA_ASGMT_WFSTD.Maintain_wf_pending_flag
3490 (p_assignment_id => l_assignment_id_tbl(i),
3491 p_mode => 'PENDING_APPROVAL') ;
3492
3493 END IF;--end l_assignment_status = SUBMITTED
3494
3495 log_message('Completed Loop ' || i );
3496
3497 END IF; --assignment id null check
3498
3499 EXCEPTION
3500 WHEN PROCESS_ASSIGNMENT_EXCEPTION THEN
3501
3502 log_message('Exception during assignment processing');
3503
3504 ROLLBACK TO PROCESS_ASSIGNMENT_SUBMISSION;
3505
3506 log_message('Assignment id:' || l_assignment_id_tbl(i));
3507
3508 PA_MESSAGE_UTILS.save_messages
3509 (p_user_id => p_submitter_user_id,
3510 p_source_type1 => PA_MASS_ASGMT_TRX.G_SOURCE_TYPE1,
3511 p_source_type2 => 'MASS_APPROVAL',
3512 p_source_identifier1 => 'PAWFAAP',
3513 p_source_identifier2 => l_group_id,
3514 p_context1 => p_project_id,
3515 p_context2 => l_assignment_id_tbl(i),
3516 p_context3 => l_resource_id,
3517 p_commit => FND_API.G_FALSE,
3518 x_return_status => l_return_status);
3519
3520 --Setting pending approval flag in pa_project_assignments
3521 PA_ASGMT_WFSTD.Maintain_wf_pending_flag
3522 (p_assignment_id => l_assignment_id_tbl(i),
3523 p_mode => 'APPROVAL_PROCESS_COMPLETED') ;
3524
3525 ---------------------------
3526 --Set the mass wf flag
3527 ---------------------------
3528 UPDATE pa_project_assignments
3529 SET mass_wf_in_progress_flag = 'N'
3530 WHERE assignment_id = l_assignment_id_tbl(i);
3531
3532 l_assignment_id_tbl(i) := NULL;
3533 WHEN OTHERS THEN
3534 RAISE;
3535 END;
3536
3537 END LOOP; --end i loop for all assignments for this mass transaction
3538
3539 log_message('After populating pa_wf_ntf_performers');
3540
3541 -----------------------------------------------------------------------------------------
3542 /*FOR each distinct approver one in PA_WF_NTF_PERFORMERS belonging
3543 to mass transaction group l_group_id
3544 update the approver_group_id in PA_WF_NTF_PERFORMERS for these assignments with a
3545 new sequence value. Store this value in l_approver_group_id
3546 The l_approver_group_id is the approver transaction id which is used
3547 in grouping and sending approval required notifications for the next (second) set of approvers.
3548 Approver group id is used in grouping the approvers in the next routing order
3549 */
3550 -----------------------------------------------------------------------------------------
3551 FOR rec IN distinct_approvers LOOP
3552
3553 SELECT PA_WF_NTF_PERFORMERS_S.nextval
3554 INTO l_approver_group_id
3555 FROM dual;
3556
3557 UPDATE pa_wf_ntf_performers
3558 SET approver_group_id = l_approver_group_id
3559 WHERE group_id = l_group_id
3560 AND user_name = rec.user_name
3561 AND routing_order = 1;
3562
3563 ----------------------------------------------------------
3564 --Call API to start one workflow for each grouped approver
3565 ----------------------------------------------------------
3566
3567 log_message('Before Calling workflow for group_id, approver_group_id:' || l_group_id||','||l_approver_group_id);
3568
3569 PA_ASGMT_WFSTD.start_mass_approval_flow
3570 (p_project_id => p_project_id
3571 ,p_mode => p_mode
3572 ,p_note_to_approvers => p_note_to_all_approvers
3573 ,p_forwarded_from => null
3574 ,p_performer_user_name => rec.user_name
3575 ,p_routing_order => 1
3576 ,p_group_id => l_group_id
3577 ,p_approver_group_id => l_approver_group_id
3578 ,p_update_info_doc => p_update_info_doc
3579 ,p_project_name => l_projects_rec.name
3580 ,p_project_number => l_projects_rec.segment1
3581 ,p_project_manager => l_project_manager_name
3582 ,p_project_org => l_projects_rec.organization_name
3583 ,p_project_cus => l_customer_name
3584 ,p_submitter_user_name => l_submitter_user_name
3585 ,p_conflict_group_id => p_conflict_group_id
3586 ,x_return_status => l_return_status
3587 ,x_msg_count => l_msg_count
3588 ,x_msg_data => l_msg_data);
3589
3590 l_error_flag := 'N';
3591
3592 log_message('After Calling workflow for group_id, approver_group_id:' || l_group_id||','||l_approver_group_id);
3593
3594 END LOOP;--end loop distinct approvers
3595
3596 -------------------------------------------------------
3597 --Getting number of approved and rejected assignments
3598 -------------------------------------------------------
3599 BEGIN
3600
3601 SELECT count(*)
3602 INTO l_num_apr_asgns
3603 FROM pa_wf_ntf_performers ntf,
3604 pa_project_assignments asgn
3605 WHERE ntf.group_id = l_group_id
3606 AND ntf.routing_order = 1
3607 AND ntf.object_id1 = asgn.assignment_id
3608 AND asgn.apprvl_status_code = PA_ASSIGNMENT_APPROVAL_PUB.g_approved ;
3609
3610 EXCEPTION
3611 WHEN NO_DATA_FOUND THEN
3612 null;
3613 WHEN OTHERS THEN
3614 RAISE;
3615 END;
3616
3617 BEGIN
3618
3619 SELECT count(*)
3620 INTO l_num_rej_asgns
3621 FROM pa_wf_ntf_performers ntf,
3622 pa_project_assignments asgn
3623 WHERE ntf.group_id = l_group_id
3624 AND ntf.routing_order = 1
3625 AND ntf.object_id1 = asgn.assignment_id
3626 AND asgn.apprvl_status_code = PA_ASSIGNMENT_APPROVAL_PUB.g_rejected ;
3627
3628 EXCEPTION
3629 WHEN NO_DATA_FOUND THEN
3630 null;
3631 WHEN OTHERS THEN
3632 RAISE;
3633 END;
3634
3635 ---------------------------------------
3636 --Getting number of errored assignments
3637 --------------------------------------
3638 BEGIN
3639 SELECT count( distinct ( attribute2))
3640 INTO l_error_count
3641 FROM PA_REPORTING_EXCEPTIONS
3642 WHERE context = PA_MASS_ASGMT_TRX.G_SOURCE_TYPE1
3643 AND sub_context = 'MASS_APPROVAL'
3644 AND source_identifier1 = 'PAWFAAP'
3645 AND source_identifier2 = l_group_id;
3646 EXCEPTION
3647 WHEN NO_DATA_FOUND THEN
3648 l_error_count := 0;
3649 WHEN OTHERS THEN
3650 RAISE;
3651 END;
3652
3653 -------------------------------------------------------
3654 --If No assignment was sumbitted (l_error_flag = Y) but
3655 --some assignments were auto approved then the
3656 --Managers must get notifications
3657 -------------------------------------------------------
3658 IF l_error_flag = 'Y' AND (l_num_apr_asgns > 0) THEN
3659
3660 --Start Manager Notifications
3661 log_message('Calling mgr fyi notification');
3662
3663 BEGIN
3664
3665 --Get all assignments in this mass transaction
3666 --which have been approved
3667 SELECT ntf.object_id1
3668 BULK COLLECT INTO l_appr_asgmt_id_tbl
3669 FROM pa_wf_ntf_performers ntf,
3670 pa_project_assignments asgn
3671 WHERE ntf.group_id = l_group_id
3672 AND ntf.routing_order = 1
3673 AND ntf.object_id1 = asgn.assignment_id
3674 AND asgn.apprvl_status_code = PA_ASSIGNMENT_APPROVAL_PUB.g_approved ;
3675
3676 EXCEPTION
3677 WHEN NO_DATA_FOUND THEN
3678 null;
3679 END;
3680
3681 PA_ASGMT_WFSTD.process_mgr_fyi_notification
3682 ( p_assignment_id_tbl => l_appr_asgmt_id_tbl
3683 ,p_project_id => p_project_id
3684 ,p_mode => p_mode
3685 ,p_group_id => l_group_id
3686 ,p_update_info_doc => p_update_info_doc
3687 ,p_num_apr_asgns => l_num_apr_asgns
3688 ,p_num_rej_asgns => l_num_rej_asgns
3689 ,p_project_name => l_projects_rec.name
3690 ,p_project_number => l_projects_rec.segment1
3691 ,p_project_manager => l_project_manager_name
3692 ,p_project_org => l_projects_rec.organization_name
3693 ,p_project_cus => l_customer_name
3694 ,p_submitter_user_name => l_submitter_user_name
3695 ,p_conflict_group_id => p_conflict_group_id
3696 ,x_return_status => l_return_status
3697 ,x_msg_count => l_msg_count
3698 ,x_msg_data => l_msg_data);
3699
3700 log_message('Calling overcom_post_aprvl_processing');
3701
3702 ------------------------------------------------------------------
3703 --This API is called to send notifications to conflicting managers
3704 ------------------------------------------------------------------
3705 PA_SCHEDULE_PVT.overcom_post_aprvl_processing
3706 ( p_conflict_group_id => p_conflict_group_id
3707 ,p_fnd_user_name => l_submitter_user_name
3708 ,x_return_status => l_return_status
3709 ,x_msg_count => l_msg_count
3710 ,x_msg_data => l_msg_data);
3711
3712 END IF;
3713
3714 -----------------------------------------------------------------------------------
3715 --The error flag by default is Y. It is changed to N if any assignment is submitted
3716 --in which case notification to submitter will be handled in mass approve flow
3717 -----------------------------------------------------------------------------------
3718 IF l_error_count > 0 AND l_error_flag = 'Y' THEN
3719 l_error_flag := 'Y';
3720 END IF;
3721
3722 log_message('Count of failed assignments:' || l_error_count);
3723
3724 -----------------------------------------------------------------------
3725 --The codebelow processes the submitter notifcations when all assignments
3726 --fail submission or
3727 --if there are erorrs and some auto-approved assignments but none
3728 --was submitted. In other words if the mass approval transaction completes
3729 --and there were errors after the submit process the FYI error notification
3730 --is sent to submitter
3731 -----------------------------------------------------------------------
3732 IF l_error_flag = 'Y' THEN --Submission failed for all assignments.
3733
3734 log_message('Submission Failed');
3735
3736 log_message('Calling submitter notification');
3737
3738 PA_ASGMT_WFSTD.process_submitter_notification
3739 (p_project_id => p_project_id
3740 ,p_mode => p_mode
3741 ,p_group_id => l_group_id
3742 ,p_update_info_doc => p_update_info_doc
3743 ,p_num_apr_asgns => l_num_apr_asgns
3744 ,p_num_rej_asgns => l_num_rej_asgns
3745 ,p_project_name => l_projects_rec.name
3746 ,p_project_number => l_projects_rec.segment1
3747 ,p_project_manager => l_project_manager_name
3748 ,p_project_org => l_projects_rec.organization_name
3749 ,p_project_cus => l_customer_name
3750 ,p_submitter_user_name => l_submitter_user_name
3751 ,p_assignment_id => p_assignment_id_tbl(1)
3752 ,x_return_status => l_return_status
3753 ,x_msg_count => l_msg_count
3754 ,x_msg_data => l_msg_data);
3755
3756 END IF;
3757
3758 -- IF the number of messages is 1 then fetch the message code from
3759 -- the stack and return its text
3760 x_msg_count := FND_MSG_PUB.Count_Msg;
3761
3762 IF x_msg_count = 1 THEN
3763 pa_interface_utils_pub.get_messages
3764 ( p_encoded => FND_API.G_TRUE
3765 ,p_msg_index => 1
3766 ,p_data => x_msg_data
3767 ,p_msg_index_out => l_msg_index_out );
3768 END IF;
3769
3770 -- Reset the error stack when returning to the calling program
3771 PA_DEBUG.Reset_Err_Stack;
3772
3773 -- If g_error_exists is TRUE then set the x_return_status to 'E'
3774 IF FND_MSG_PUB.Count_Msg >0 THEN
3775 x_return_status := FND_API.G_RET_STS_ERROR;
3776 END IF;
3777
3778 EXCEPTION
3779 WHEN NO_WORKFLOW_EXCEPTION THEN
3780 --TODO: Check
3781 x_return_status := FND_API.G_RET_STS_SUCCESS;
3782
3783 WHEN INVALID_STATUS THEN
3784
3785 IF p_commit = FND_API.G_TRUE THEN
3786 ROLLBACK TO MASS_ASGN_APPRVL;
3787 END IF;
3788
3789 -- Set the excetption Message and the stack
3790 FND_MSG_PUB.add_exc_msg
3791 ( p_pkg_name => 'PA_ASSIGNMENT_APPROVAL_PUB.mass_assignment_approval'
3792 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
3793
3794 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3795 RAISE;
3796
3797 WHEN OTHERS THEN
3798
3799 IF p_commit = FND_API.G_TRUE THEN
3800 ROLLBACK TO MASS_ASGN_APPRVL;
3801 END IF;
3802
3803 -- Set the excetption Message and the stack
3804 FND_MSG_PUB.add_exc_msg
3805 ( p_pkg_name => 'PA_ASSIGNMENT_APPROVAL_PUB.mass_assignment_approval'
3806 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
3807
3808 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3809 RAISE;
3810 END mass_assignment_approval;
3811
3812 ---------------------------------------------------------
3813 --This API is called from the mass approve page
3814 --It starts a worflow to process the approval result
3815 --as a deferred activity
3816 --Workflow Itemtype: PARMAAP
3817 --Workflow process : 'PA_MASS_PROCESS_APRVL_RESULT'
3818 ---------------------------------------------------------
3819 PROCEDURE mass_process_approval_result
3820 ( p_project_id IN pa_project_assignments.project_id%TYPE := FND_API.G_MISS_NUM
3821 ,p_mode IN VARCHAR2
3822 ,p_assignment_id_tbl IN SYSTEM.pa_num_tbl_type := prm_empty_num_tbl
3823 ,p_approval_status_tbl IN SYSTEM.pa_varchar2_30_tbl_type := prm_empty_varchar2_30_tbl
3824 ,p_group_id IN NUMBER
3825 ,p_approver_group_id IN NUMBER
3826 ,p_routing_order IN NUMBER
3827 ,p_item_key IN NUMBER
3828 ,p_notification_id IN NUMBER
3829 ,p_submitter_user_name IN VARCHAR2
3830 ,p_conflict_group_id IN NUMBER := FND_API.G_MISS_NUM
3831 ,p_api_version IN NUMBER := 1.0
3832 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
3833 ,p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM
3834 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
3835 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
3836 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3837 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3838 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3839 )
3840 IS
3841
3842 l_itemkey VARCHAR2(30);
3843 l_responsibility_id NUMBER;
3844 l_resp_appl_id NUMBER;
3845 l_wf_started_date DATE;
3846 l_wf_started_by_id NUMBER;
3847 l_return_status VARCHAR2(1);
3848 l_error_message_code VARCHAR2(30);
3849 l_save_threshold NUMBER;
3850 l_msg_count NUMBER ;
3851 l_msg_index_out NUMBER ;
3852 l_msg_data VARCHAR2(2000);
3853 l_wf_item_type VARCHAR2(2000):= 'PARMAAP'; --Assignment Approval Item type
3854 l_wf_process VARCHAR2(2000):= 'PA_MASS_PROCESS_APRVL_RESULT'; --Mass Assignment Approval process
3855 l_err_code NUMBER := 0;
3856 l_err_stage VARCHAR2(2000);
3857 l_err_stack VARCHAR2(2000);
3858 l_text_attr_name_tbl Wf_Engine.NameTabTyp;
3859 l_text_attr_value_tbl Wf_Engine.TextTabTyp;
3860 l_num_attr_name_tbl Wf_Engine.NameTabTyp;
3861 l_num_attr_value_tbl Wf_Engine.NumTabTyp;
3862 l_update_info_doc VARCHAR2(32767);
3863 l_note_to_approvers VARCHAR2(2000);
3864 l_forwarded_from fnd_user.user_name%TYPE; /* Commented for bug 3261755 VARCHAR2(30); */
3865
3866 BEGIN
3867
3868 -- Initialize the Error Stack
3869 PA_DEBUG.init_err_stack('PA_ASSIGNMENT_APPROVAL_PUB.mass_process_approval_result');
3870
3871 log_message('Inside mass_process_approval_result');
3872
3873 --Log Message
3874 IF P_DEBUG_MODE = 'Y' THEN
3875 PA_DEBUG.write_log
3876 ( x_module => 'pa.plsql.PA_ASSIGNMENT_APPROVAL_PUB.mass_process_approval_result.begin'
3877 ,x_msg => 'Beginning of mass_assignment_approval'
3878 ,x_log_level => 1);
3879 END IF;
3880
3881 -- Initialize the return status to success
3882 x_return_status := FND_API.G_RET_STS_SUCCESS;
3883
3884 --Clear the global PL/SQL message table
3885 FND_MSG_PUB.initialize;
3886
3887 -- Issue API savepoint if the transaction is to be committed
3888 IF p_commit = FND_API.G_TRUE THEN
3889 SAVEPOINT MASS_APPRVL_RESULT;
3890 END IF;
3891
3892 --------------------------------------------------------
3893 --Update object_id2 column to 100 for these assignments
3894 --in pa_wf_ntf_performers table to differentiate
3895 --between assignments for which user has aproved/rejected
3896 --and those for which notification is pending
3897 ---------------------------------------------------------
3898 UPDATE pa_wf_ntf_performers
3899 SET object_id2 = 100
3900 where group_id = p_group_id
3901 and routing_order = p_routing_order
3902 and approver_group_id = p_approver_group_id;
3903
3904 -----------------------------------------------
3905 -- Create the unique item key to launch WF with
3906 -----------------------------------------------
3907 SELECT pa_prm_wf_item_key_s.nextval
3908 INTO l_itemkey
3909 FROM dual;
3910
3911 l_wf_started_by_id := FND_GLOBAL.user_id;
3912 l_responsibility_id := FND_GLOBAL.resp_id;
3913 l_resp_appl_id := FND_GLOBAL.resp_appl_id;
3914
3915 FND_GLOBAL.Apps_Initialize ( user_id => l_wf_started_by_id
3916 , resp_id => l_responsibility_id
3917 , resp_appl_id => l_resp_appl_id );
3918
3919 -- Setting thresold value to run the process in background
3920 l_save_threshold := wf_engine.threshold;
3921 wf_engine.threshold := -1;
3922
3923 -- Create the WF process
3924 wf_engine.CreateProcess
3925 ( ItemType => l_wf_item_type
3926 , ItemKey => l_itemkey
3927 , process => l_wf_process );
3928
3929
3930 ------------------------------------------------------------
3931 --Creating the attribute arrays from assignment id table and
3932 --Status table
3933 ------------------------------------------------------------
3934 IF p_assignment_id_tbl.COUNT > 0 THEN
3935
3936 log_message('Creating assignment attributes');
3937
3938 FOR i IN p_assignment_id_tbl.FIRST .. p_assignment_id_tbl.LAST LOOP
3939
3940 l_num_attr_name_tbl( l_num_attr_name_tbl.COUNT+1 ) := 'ASSIGNMENT_' || i;
3941 l_num_attr_value_tbl( l_num_attr_value_tbl.COUNT+1 ):= p_assignment_id_tbl(i);
3942
3943 END LOOP;
3944
3945 END IF;
3946
3947 IF p_approval_status_tbl.COUNT > 0 THEN
3948
3949 log_message('Creating status attributes');
3950
3951 FOR i IN p_approval_status_tbl.FIRST .. p_approval_status_tbl.LAST LOOP
3952
3953 l_text_attr_name_tbl( l_text_attr_name_tbl.COUNT+1 ) := 'STATUS_' || i;
3954 l_text_attr_value_tbl( l_text_attr_value_tbl.COUNT+1 ):= p_approval_status_tbl(i);
3955
3956 END LOOP;
3957
3958 END IF;
3959
3960 -----------------------------------------------------------------
3961 --Set all the required workflow attributes and start the workflow
3962 -----------------------------------------------------------------
3963 wf_engine.SetItemAttrNumber
3964 ( itemtype => l_wf_item_type
3965 , itemkey => l_itemkey
3966 , aname => 'PROJECT_ID'
3967 , avalue => p_project_id );
3968
3969 wf_engine.SetItemAttrText
3970 ( itemtype => l_wf_item_type
3971 , itemkey => l_itemkey
3972 , aname => 'MODE'
3973 , avalue => p_mode );
3974
3975 --Setting Assignment Ids
3976 wf_engine.AddItemAttrNumberArray
3977 ( itemtype => l_wf_item_type
3978 , itemkey => l_itemkey
3979 , aname => l_num_attr_name_tbl
3980 , avalue => l_num_attr_value_tbl );
3981
3982 --Setting Status table
3983 wf_engine.AddItemAttrTextArray
3984 ( itemtype => l_wf_item_type
3985 , itemkey => l_itemkey
3986 , aname => l_text_attr_name_tbl
3987 , avalue => l_text_attr_value_tbl );
3988
3989 wf_engine.SetItemAttrNumber
3990 ( itemtype => l_wf_item_type
3991 , itemkey => l_itemkey
3992 , aname => 'GROUP_ID'
3993 , avalue => p_group_id );
3994
3995 wf_engine.SetItemAttrNumber
3996 ( itemtype => l_wf_item_type
3997 , itemkey => l_itemkey
3998 , aname => 'APPROVER_GROUP_ID'
3999 , avalue => p_approver_group_id );
4000
4001 wf_engine.SetItemAttrNumber
4002 ( itemtype => l_wf_item_type
4003 , itemkey => l_itemkey
4004 , aname => 'ROUTING_ORDER'
4005 , avalue => p_routing_order );
4006
4007 wf_engine.SetItemAttrNumber
4008 ( itemtype => l_wf_item_type
4009 , itemkey => l_itemkey
4010 , aname => 'NUMBER_OF_ASSIGNMENTS'
4011 , avalue => p_assignment_id_tbl.COUNT );
4012
4013 --Get and set the Update info document
4014 l_update_info_doc := wf_engine.getItemAttrDocument
4015 ( itemtype => 'PAWFAAP'
4016 , itemkey => p_item_key
4017 , aname => 'UPDATED_INFO_DOC' );
4018
4019 wf_engine.SetItemAttrDocument
4020 ( itemtype => l_wf_item_type
4021 , itemkey => l_itemkey
4022 , aname => 'UPDATED_INFO_DOC'
4023 , documentid => l_update_info_doc );
4024
4025 wf_engine.SetItemAttrText
4026 ( itemtype => l_wf_item_type
4027 , itemkey => l_itemkey
4028 , aname => 'SUBMITTER_UNAME'
4029 , avalue => p_submitter_user_name );
4030
4031 wf_engine.SetItemAttrNumber
4032 ( itemtype => l_wf_item_type
4033 , itemkey => l_itemkey
4034 , aname => 'CONFLICT_GROUP_ID'
4035 , avalue => p_conflict_group_id );
4036
4037 --Set the previous approver (forwarded from and note to approvers)
4038 l_forwarded_from := wf_engine.getItemAttrText
4039 ( itemtype => 'PAWFAAP'
4040 , itemkey => p_item_key
4041 , aname => 'NTFY_APPRVL_RECIPIENT_NAME');
4042
4043 l_note_to_approvers := wf_engine.getItemAttrText
4044 ( itemtype => 'PAWFAAP'
4045 , itemkey => p_item_key
4046 , aname => 'NOTE_TO_APPROVER');
4047
4048 wf_engine.SetItemAttrText
4049 ( itemtype => l_wf_item_type
4050 , itemkey => l_itemkey
4051 , aname => 'NTFY_APPRVL_RECIPIENT_NAME'
4052 , avalue => l_forwarded_from );
4053
4054 wf_engine.SetItemAttrText
4055 ( itemtype => l_wf_item_type
4056 , itemkey => l_itemkey
4057 , aname => 'NOTE_TO_APPROVER'
4058 , avalue => l_note_to_approvers );
4059
4060 -------------------------------
4061 --Start the workflow process
4062 -------------------------------
4063 wf_engine.StartProcess ( itemtype => l_wf_item_type
4064 ,itemkey => l_itemkey );
4065
4066 PA_WORKFLOW_UTILS.Insert_WF_Processes
4067 (p_wf_type_code => 'MASS_ASSIGNMENT_APPROVAL'
4068 ,p_item_type => l_wf_item_type
4069 ,p_item_key => l_itemkey
4070 ,p_entity_key1 => to_char(p_project_id)
4071 ,p_entity_key2 => to_char(p_group_id)
4072 ,p_description => NULL
4073 ,p_err_code => l_err_code
4074 ,p_err_stage => l_err_stage
4075 ,p_err_stack => l_err_stack );
4076
4077 --Setting the original value
4078 wf_engine.threshold := l_save_threshold;
4079
4080 log_message('Exiting mass_process_approval_result');
4081
4082 EXCEPTION
4083 WHEN OTHERS THEN
4084
4085 IF p_commit = FND_API.G_TRUE THEN
4086 ROLLBACK TO MASS_APPRVL_RESULT;
4087 END IF;
4088
4089 --Setting the original value
4090 wf_engine.threshold := l_save_threshold;
4091
4092 -- Set the excetption Message and the stack
4093 FND_MSG_PUB.add_exc_msg
4094 ( p_pkg_name => 'PA_ASSIGNMENT_APPROVAL_PUB.mass_process_approval_result'
4095 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
4096
4097 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4098 RAISE;
4099 END mass_process_approval_result;
4100
4101 ----------------------------------------------------
4102 --This procedure validates the approvers name and id
4103 --It is called in mass_assignment_approval API
4104 ----------------------------------------------------
4105 /* Added a new parameter p_overriding_authority_flag for the bug 3213509*/
4106 PROCEDURE validate_approver_name_id
4107 ( p_project_id IN NUMBER
4108 ,p_assignment_id_tbl IN SYSTEM.pa_num_tbl_type := prm_empty_num_tbl
4109 ,p_approver1_id_tbl IN SYSTEM.pa_num_tbl_type := prm_empty_num_tbl
4110 ,p_approver1_name_tbl IN SYSTEM.pa_varchar2_240_tbl_type := prm_empty_varchar2_240_tbl
4111 ,p_approver2_id_tbl IN SYSTEM.pa_num_tbl_type := prm_empty_num_tbl
4112 ,p_approver2_name_tbl IN SYSTEM.pa_varchar2_240_tbl_type := prm_empty_varchar2_240_tbl
4113 ,p_submitter_user_id IN NUMBER
4114 ,p_group_id IN NUMBER
4115 ,p_api_version IN NUMBER := 1.0
4116 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
4117 ,p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM
4118 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
4119 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
4120 ,p_overriding_authority_flag IN VARCHAR2 := 'N'
4121 ,x_assignment_id_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type
4122 ,x_approver1_id_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type
4123 ,x_approver2_id_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type
4124 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
4125 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
4126 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
4127 )
4128 IS
4129
4130 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
4131 l_error_message_code fnd_new_messages.message_name%TYPE;
4132 l_assignment_start_date DATE;
4133 l_approver1_person_id NUMBER;
4134 l_approver1_person_name VARCHAR2(200);
4135 l_approver2_person_id NUMBER;
4136 l_approver2_person_name VARCHAR2(200);
4137 l_resource_type_id NUMBER;--Used in Name ID validation
4138 l_msg_index_out NUMBER;
4139 l_resource_id NUMBER;
4140
4141 BEGIN
4142
4143 -- Initialize the Error Stack
4144 PA_DEBUG.init_err_stack('PA_ASSIGNMENT_APPROVAL_PUB.validate_approver_name_id');
4145
4146 --Log Message
4147 IF P_DEBUG_MODE = 'Y' THEN
4148 PA_DEBUG.write_log
4149 ( x_module => 'pa.plsql.PA_ASSIGNMENT_APPROVAL_PUB.validate_approver_name_id.begin'
4150 ,x_msg => 'Beginning of approver name id validation'
4151 ,x_log_level => 1);
4152 END IF;
4153
4154 -- Initialize the return status to success
4155 x_return_status := FND_API.G_RET_STS_SUCCESS;
4156
4157 --Clear the global PL/SQL message table
4158 FND_MSG_PUB.initialize;
4159
4160 -- Issue API savepoint if the transaction is to be committed
4161 IF p_commit = FND_API.G_TRUE THEN
4162 SAVEPOINT MASS_APPRVL_VALIDATE_NAMEID;
4163 END IF;
4164
4165 log_message('Group id:' || p_group_id);
4166 log_message('Value of submitter user id:' || p_submitter_user_id);
4167
4168 x_approver1_id_tbl := prm_empty_num_tbl;
4169 x_approver2_id_tbl := prm_empty_num_tbl;
4170 x_assignment_id_tbl := prm_empty_num_tbl;
4171
4172 x_approver1_id_tbl.EXTEND( p_assignment_id_tbl.COUNT);
4173 x_approver2_id_tbl.EXTEND( p_assignment_id_tbl.COUNT);
4174 x_assignment_id_tbl.EXTEND( p_assignment_id_tbl.COUNT);
4175
4176 ---------------------------------------------------------
4177 --Name to ID validation for approvers of each assignment
4178 --The above must be done for both the approvers
4179 ---------------------------------------------------------
4180 IF p_assignment_id_tbl.COUNT > 0 THEN
4181
4182
4183
4184 FOR i IN p_assignment_id_tbl.FIRST .. p_assignment_id_tbl.LAST LOOP
4185
4186 log_message('Loop:' || i);
4187
4188 --Initialize locals to null before every loop
4189 l_approver1_person_id := null;
4190 l_approver1_person_name := null;
4191 l_approver2_person_id := null;
4192 l_approver2_person_name := null;
4193
4194 --Get the assignment start date
4195 SELECT start_date
4196 INTO l_assignment_start_date
4197 FROM pa_project_assignments
4198 WHERE assignment_id = p_assignment_id_tbl(i);
4199
4200 IF p_approver1_id_tbl.EXISTS(i) THEN
4201 IF p_approver1_id_tbl(i) <> -999 THEN
4202 l_approver1_person_id := p_approver1_id_tbl(i);
4203 log_message('Person_id1:' || l_approver1_person_id);
4204 END IF;
4205 END IF;
4206
4207 IF p_approver1_name_tbl.EXISTS(i) THEN
4208 l_approver1_person_name := p_approver1_name_tbl(i);
4209 log_message('Person_Name1:' || l_approver1_person_name);
4210 END IF;
4211
4212 IF p_approver2_id_tbl.EXISTS(i) THEN
4213
4214 IF p_approver2_id_tbl(i) <> -999 THEN
4215 l_approver2_person_id := p_approver2_id_tbl(i);
4216 log_message('Person_id2:' || l_approver2_person_id);
4217 END IF;
4218
4219 END IF;
4220
4221 IF p_approver2_name_tbl.EXISTS(i) THEN
4222 l_approver2_person_name := p_approver2_name_tbl(i);
4223 log_message('Person_Name2:' || l_approver2_person_name);
4224 END IF;
4225
4226 -----------------------------------------------------------------
4227 --If there are no approvers for this assignment then signal error
4228 -----------------------------------------------------------------\
4229 /* Added the check for the bug 3213509*/
4230 IF p_overriding_authority_flag = 'N' then
4231 IF ( l_approver1_person_id IS NULL AND l_approver1_person_name IS NULL
4232 AND l_approver2_person_id IS NULL
4233 AND l_approver2_person_name IS NULL )
4234 THEN
4235 log_message('No Approvers for Loop:' || i);
4236
4237 l_error_message_code := 'PA_RESOURCE_NO_APPROVAL';
4238 PA_UTILS.Add_Message( p_app_short_name => 'PA'
4239 ,p_msg_name => l_error_message_code);
4240
4241 END IF;
4242 END IF;
4243 ---------------------
4244 --Validate approvers
4245 ---------------------
4246 IF ( l_approver1_person_id IS NOT NULL OR
4247 l_approver1_person_name IS NOT NULL )
4248 THEN
4249
4250 log_message('Before calling check_resourcename_or_id for Approver1 Loop:' || i);
4251
4252 PA_RESOURCE_UTILS.Check_ResourceName_OR_ID (
4253 p_resource_id => l_approver1_person_id
4254 ,p_resource_name => l_approver1_person_name
4255 ,p_check_id_flag => PA_STARTUP.G_Check_ID_Flag
4256 ,p_date => l_assignment_start_date
4257 ,x_resource_id => x_approver1_id_tbl (i)
4258 ,x_resource_type_id => l_resource_type_id
4259 ,x_return_status => l_return_status
4260 ,x_error_message_code => l_error_message_code );
4261
4262 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4263 PA_UTILS.Add_Message( p_app_short_name => 'PA'
4264 ,p_msg_name => l_error_message_code);
4265 END IF;
4266
4267 log_message('After calling check_resourcename_or_id for Approver1 Loop:' || i);
4268 log_message('Person_id:' || x_approver1_id_tbl (i));
4269 log_message('Return Status:' || l_return_status);
4270 log_message('Error:' || l_error_message_code);
4271
4272 --x_approver1_id_tbl (i) := l_approver1_person_id;
4273
4274 END IF;
4275
4276 IF ( l_approver2_person_id IS NOT NULL OR
4277 l_approver2_person_name IS NOT NULL )
4278 THEN
4279
4280 log_message('Before calling check_resourcename_or_id for Approver2 Loop:' || i);
4281 log_message('Check Id flag: ' || PA_STARTUP.G_Check_ID_Flag);
4282
4283 PA_RESOURCE_UTILS.Check_ResourceName_OR_ID (
4284 p_resource_id => l_approver2_person_id
4285 ,p_resource_name => l_approver2_person_name
4286 ,p_check_id_flag => PA_STARTUP.G_Check_ID_Flag
4287 ,p_date => l_assignment_start_date
4288 ,x_resource_id => x_approver2_id_tbl (i)
4289 ,x_resource_type_id => l_resource_type_id
4290 ,x_return_status => l_return_status
4291 ,x_error_message_code => l_error_message_code);
4292
4293 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4294 PA_UTILS.Add_Message( p_app_short_name => 'PA'
4295 ,p_msg_name => l_error_message_code);
4296 END IF;
4297
4298 log_message('After calling check_resourcename_or_id for Approver2 Loop:' || i);
4299 log_message('Person_id:' || x_approver2_id_tbl (i));
4300 log_message('Person Name:' || l_approver2_person_name);
4301 log_message('Return Status:' || l_return_status);
4302 log_message('Error:' || l_error_message_code);
4303
4304 --x_approver2_id_tbl (i) := l_approver2_person_id;
4305
4306 END IF;
4307
4308 log_message('COUNT of message stack:' || FND_MSG_PUB.Count_Msg);
4309
4310 -------------------------------------------
4311 --Populate x_success_assignment_id_tbl
4312 -------------------------------------------
4313 IF FND_MSG_PUB.Count_Msg > 0 THEN
4314
4315 --Setting pending approval flag in pa_project_assignments
4316 PA_ASGMT_WFSTD.Maintain_wf_pending_flag
4317 (p_assignment_id => p_assignment_id_tbl(i),
4318 p_mode => 'APPROVAL_PROCESS_COMPLETED') ;
4319
4320 ---------------------------
4321 --Set the mass wf flag
4322 ---------------------------
4323 UPDATE pa_project_assignments
4324 SET mass_wf_in_progress_flag = 'N'
4325 WHERE assignment_id = p_assignment_id_tbl(i);
4326
4327 x_assignment_id_tbl (i) := null;
4328 ELSE
4329 x_assignment_id_tbl (i) := p_assignment_id_tbl (i);
4330 END IF;
4331
4332 IF FND_MSG_PUB.Count_Msg > 0 THEN
4333
4334 SELECT resource_id
4335 INTO l_resource_id
4336 FROM pa_project_assignments
4337 WHERE assignment_id = p_assignment_id_tbl(i);
4338
4339 log_message('Value of submitter user id:' || p_submitter_user_id);
4340
4341 PA_MESSAGE_UTILS.save_messages
4342 (p_user_id => p_submitter_user_id,
4343 p_source_type1 => PA_MASS_ASGMT_TRX.G_SOURCE_TYPE1,
4344 p_source_type2 => 'MASS_APPROVAL',
4345 p_source_identifier1 => 'PAWFAAP',
4346 p_source_identifier2 => p_group_id,
4347 p_context1 => p_project_id,
4348 p_context2 => p_assignment_id_tbl(i),
4349 p_context3 => l_resource_id,
4350 p_commit => FND_API.G_FALSE,
4351 x_return_status => l_return_status);
4352
4353 END IF;
4354
4355 END LOOP;--end i loop
4356
4357 END IF; --end name validations for all assignments
4358
4359 EXCEPTION
4360 WHEN OTHERS THEN
4361
4362 IF p_commit = FND_API.G_TRUE THEN
4363 ROLLBACK TO MASS_APPRVL_VALIDATE_NAMEID;
4364 END IF;
4365
4366 -- Set the excetption Message and the stack
4367 FND_MSG_PUB.add_exc_msg
4368 ( p_pkg_name => 'PA_ASSIGNMENT_APPROVAL_PUB.validate_approver_name_id'
4369 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
4370
4371 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4372 RAISE;
4373 END validate_approver_name_id;
4374
4375
4376 END PA_ASSIGNMENT_APPROVAL_PUB;