[Home] [Help]
PACKAGE BODY: APPS.PA_ASSIGNMENT_APPROVAL_PUB
Source
1 PACKAGE BODY PA_ASSIGNMENT_APPROVAL_PUB AS
2 /*$Header: PARAAPBB.pls 120.7 2007/11/07 14:16:23 rthumma 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 END IF; -- end of checking source assignment id
1983
1984 --Update apprvl_status_code and send notification if necessary
1985 l_new_assignment_flag := PA_ASSIGNMENT_APPROVAL_PVT.Is_New_Assignment(p_assignment_id => p_assignment_id);
1986 --dbms_output.put_line('new assignment flag'||l_new_assignment_flag);
1987
1988 -- update the assignment approval status and send notification when the canceled assignment
1989 -- has ever been approved.
1990
1991 PA_ASSIGNMENT_APPROVAL_PUB.Start_Assignment_Approvals( p_assignment_id => p_assignment_id
1992 ,p_new_assignment_flag => l_new_assignment_flag
1993 ,p_action_code =>PA_ASSIGNMENT_APPROVAL_PUB.g_cancel_action
1994 ,p_record_version_number => NULL
1995 ,p_validate_only => FND_API.G_FALSE
1996 ,x_overcommitment_flag => l_overcommitment_flag
1997 ,x_conflict_group_id => l_conflict_group_id
1998 ,x_return_status => x_return_status
1999 ,x_msg_count => x_msg_count
2000 ,x_msg_data => x_msg_data);
2001 --dbms_output.put_line('start_assignment_approval'||x_return_status);
2002
2003 --Else if full duration cancel, just set cancel status
2004 ELSIF l_full_cancel_flag = 'Y' THEN
2005
2006 -- Update apprvl_status_code
2007 PA_ASSIGNMENT_APPROVAL_PVT.Update_Approval_Status(
2008 P_ASSIGNMENT_ID => p_assignment_id
2009 ,P_ACTION_CODE => PA_ASSIGNMENT_APPROVAL_PUB.g_cancel_action
2010 ,P_RECORD_VERSION_NUMBER => NULL
2011 ,X_APPRVL_STATUS_CODE => l_apprvl_status_code
2012 ,X_CHANGE_ID => l_change_id
2013 ,X_RECORD_VERSION_NUMBER => l_record_version_number
2014 ,X_RETURN_STATUS => x_return_status
2015 ,X_MSG_COUNT => x_msg_count
2016 ,X_MSG_DATA => x_msg_data);
2017 --dbms_output.put_line('update_approval_status'||x_return_status);
2018
2019 END IF; -- end of check full duration cancel
2020
2021 -- FP-M Development
2022 -- Break the link between associated task assignments
2023 -- and the cancelled team role
2024 OPEN get_linked_res_asgmts;
2025 FETCH get_linked_res_asgmts
2026 BULK COLLECT INTO l_task_assignment_id_tbl,
2027 l_task_version_id_tbl,
2028 l_budget_version_id_tbl,
2029 l_struct_version_id_tbl;
2030 CLOSE get_linked_res_asgmts;
2031
2032 -- 1. Change project_assignment_id to NULL (-1)
2033 -- 2. Don't wipe out project_role_id
2034 -- 3. Wipe out named_role when it is not a mandatory attribute
2035 -- of planning resource
2036 OPEN get_res_mand_attributes;
2037 FETCH get_res_mand_attributes INTO l_cur_role_flag;
2038
2039 IF get_res_mand_attributes%FOUND AND l_cur_role_flag = 'Y' THEN
2040 pa_assignments_pvt.Update_Task_Assignments(
2041 p_task_assignment_id_tbl => l_task_assignment_id_tbl
2042 ,p_task_version_id_tbl => l_task_version_id_tbl
2043 ,p_budget_version_id_tbl => l_budget_version_id_tbl
2044 ,p_struct_version_id_tbl => l_struct_version_id_tbl
2045 ,p_project_assignment_id => -1
2046 ,x_return_status => l_return_status
2047 );
2048 ELSE
2049 pa_assignments_pvt.Update_Task_Assignments(
2050 p_task_assignment_id_tbl => l_task_assignment_id_tbl
2051 ,p_task_version_id_tbl => l_task_version_id_tbl
2052 ,p_budget_version_id_tbl => l_budget_version_id_tbl
2053 ,p_struct_version_id_tbl => l_struct_version_id_tbl
2054 ,p_project_assignment_id => -1
2055 ,p_named_role => FND_API.G_MISS_CHAR
2056 ,x_return_status => l_return_status
2057 );
2058 END IF;
2059 CLOSE get_res_mand_attributes;
2060
2061 END IF; -- end of checking record_verison_number
2062
2063 CLOSE check_record_version;
2064
2065 --
2066 -- IF the number of messaages is 1 then fetch the message code from the stack and return its text
2067 --
2068 x_msg_count := FND_MSG_PUB.Count_Msg;
2069 IF x_msg_count = 1 THEN
2070 pa_interface_utils_pub.get_messages ( p_encoded => FND_API.G_TRUE
2071 ,p_msg_index => 1
2072 ,p_data => x_msg_data
2073 ,p_msg_index_out => l_msg_index_out
2074 );
2075 END IF;
2076
2077 -- Reset the error stack when returning to the calling program
2078 PA_DEBUG.Reset_Err_Stack;
2079
2080
2081 IF x_msg_count > 0 THEN
2082
2083 x_return_status := FND_API.G_RET_STS_ERROR;
2084
2085 END IF;
2086
2087
2088 -- Put any message text from message stack into the Message ARRAY
2089 --
2090 EXCEPTION
2091 WHEN OTHERS THEN
2092 IF p_commit = FND_API.G_TRUE THEN
2093 ROLLBACK TO ASG_PUB_CHANGE_STATUS;
2094 END IF;
2095 -- Set the excetption Message and the stack
2096 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_ASSIGNMENT_APPROVAL_PUB.Change_Assignment_Status'
2097 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
2098 --
2099 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2100 RAISE; -- This is optional depending on the needs
2101
2102 --
2103 END Change_Assignment_Status;
2104
2105
2106
2107 --
2108 -- Procedure : Get_Current_Approver
2109 -- Purpose : Get the approver which has the current approver flag set.
2110 -- Parameters :
2111 --
2112 PROCEDURE Get_Current_Approver
2113 (
2114 p_assignment_id IN NUMBER ,
2115 p_project_id IN NUMBER ,
2116 p_apprvl_status_code IN VARCHAR2 ,
2117 x_approver_name OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
2118 IS
2119
2120 l_user_id NUMBER;
2121 l_resource_id NUMBER;
2122 l_person_id NUMBER;
2123 l_item_type pa_wf_processes.item_type%TYPE;
2124 l_item_key pa_wf_processes.item_key%TYPE;
2125
2126 CURSOR get_item_key IS
2127 SELECT item_key, item_type
2128 FROM pa_wf_processes
2129 WHERE item_key = (
2130 SELECT max(item_key)
2131 FROM pa_wf_processes
2132 WHERE wf_type_code = 'ASSIGNMENT_APPROVAL'
2133 AND entity_key1 = to_char(p_project_id)
2134 AND entity_key2 = to_char(p_assignment_id)
2135 )
2136 and item_type = 'PAWFAAP';
2137
2138 CURSOR get_user_id IS
2139 SELECT fu.user_id
2140 FROM pa_wf_ntf_performers pwnp,
2141 fnd_user fu
2142 WHERE pwnp.object_id1 = p_assignment_id
2143 AND pwnp.object_id2 = p_project_id
2144 AND pwnp.item_type = l_item_type
2145 AND pwnp.item_key = l_item_key
2146 AND pwnp.current_approver_flag = 'Y'
2147 AND pwnp.user_name = fu.user_name;
2148 BEGIN
2149 -- Initialize the Error Stack
2150 PA_DEBUG.init_err_stack('PA_ASSIGNMENT_APPROVAL_PUB.Get_Current_Approver');
2151
2152 --Log Message
2153 IF P_DEBUG_MODE = 'Y' THEN
2154 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENT_APPROVAL_PUB.Get_Current_Approver.begin'
2155 ,x_msg => 'Beginning of Get_Current_Approver'
2156 ,x_log_level => 5);
2157 END IF;
2158
2159 --Check to see if assignment is pending approval
2160 IF p_apprvl_status_code = PA_ASSIGNMENT_APPROVAL_PUB.g_submitted THEN
2161
2162 --Get the maximum item key
2163 OPEN get_item_key;
2164 FETCH get_item_key INTO l_item_key, l_item_type;
2165
2166 IF get_item_key%FOUND THEN
2167
2168 --Get the current approver user_name
2169 OPEN get_user_id;
2170 FETCH get_user_id INTO l_user_id;
2171 CLOSE get_user_id;
2172
2173 --Get the approver name from the user_name
2174 IF l_user_id IS NOT NULL THEN
2175 PA_COMP_PROFILE_PUB.get_user_info(p_user_id => l_user_id
2176 ,x_person_id => l_person_id
2177 ,x_resource_id => l_resource_id
2178 ,x_resource_name=> x_approver_name );
2179 END IF;
2180 END IF;
2181 CLOSE get_item_key;
2182 END IF; -- end of checking if assignment is pending approval
2183
2184 -- Put any message text from message stack into the Message ARRAY
2185 --
2186 EXCEPTION
2187 WHEN OTHERS THEN
2188 -- Set the excetption Message and the stack
2189 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_ASSIGNMENT_APPROVAL_PUB.Get_Current_Approver'
2190 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
2191 RAISE; -- This is optional depending on the needs
2192
2193 END Get_Current_Approver;
2194
2195
2196
2197 PROCEDURE Cancel_Assignment
2198 (
2199 p_record_version_number IN Number ,
2200 p_assignment_id IN Number ,
2201 p_assignment_type IN Varchar2 ,
2202 p_start_date IN date ,
2203 p_end_date IN date ,
2204 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2205 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2206 x_return_status OUT NOCOPY Varchar2 , --File.Sql.39 bug 4440895
2207 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
2208 x_msg_data OUT NOCOPY Varchar2 ) --File.Sql.39 bug 4440895
2209
2210 IS
2211 l_assignment_status_code pa_project_assignments.status_code%TYPE;
2212 l_msg_index_out NUMBER;
2213 l_start_date DATE;
2214 l_end_date DATE;
2215 l_return_status VARCHAR2(1);
2216 l_error_message_code fnd_new_messages.message_name%TYPE;
2217 l_project_id NUMBER;
2218 l_person_id NUMBER;
2219
2220 CURSOR get_start_end_date IS
2221 SELECT asgn.start_date, asgn.end_date, asgn.project_id, res.person_id
2222 FROM pa_project_assignments asgn,
2223 pa_resources_denorm res
2224 WHERE assignment_id = p_assignment_id
2225 AND res.resource_id = asgn.resource_id
2226 AND rownum=1;
2227
2228 BEGIN
2229 -- Initialize the Error Stack
2230 PA_DEBUG.init_err_stack('PA_ASSIGNMENT_APPROVAL_PUB.Cancel_Assignment');
2231
2232 --Log Message
2233 IF P_DEBUG_MODE = 'Y' THEN
2234 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ASSIGNMENT_APPROVAL_PUB.Cancel_Assignment.begin'
2235 ,x_msg => 'Beginning of Cancel_Assignment'
2236 ,x_log_level => 5);
2237 END IF;
2238
2239 --Clear the global PL/SQL message table
2240 IF FND_API.TO_BOOLEAN( p_init_msg_list ) THEN
2241 FND_MSG_PUB.initialize;
2242 END IF;
2243
2244 -- Assignment cannot be deleted if project transactions
2245 -- are associated with it
2246 OPEN get_start_end_date;
2247 FETCH get_start_end_date INTO l_start_date, l_end_date, l_project_id, l_person_id;
2248 CLOSE get_start_end_date;
2249
2250 -- Should perform EI validation only for assignments
2251 IF p_assignment_type <> 'OPEN_ASSIGNMENT' THEN
2252 -- Bug 2797890: Added p_project_id, p_person_id parameters
2253 PA_TRANS_UTILS.Check_Txn_Exists( p_assignment_id => p_assignment_id
2254 ,p_project_id => l_project_id
2255 ,p_person_id => l_person_id
2256 ,p_calling_mode => 'CANCEL'
2257 ,p_old_start_date => null
2258 ,p_old_end_date => null
2259 ,p_new_start_date => l_start_date
2260 ,p_new_end_date => l_end_date
2261 ,x_error_message_code => l_error_message_code
2262 ,x_return_status => l_return_status);
2263
2264 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2265 PA_UTILS.Add_Message( p_app_short_name => 'PA'
2266 ,p_msg_name => l_error_message_code);
2267 END IF;
2268 l_return_status := NULL;
2269 END IF;
2270
2271 --Get Profile Default Status
2272 IF p_assignment_type = 'OPEN_ASSIGNMENT' THEN
2273 FND_PROFILE.Get('PA_DEF_CANCELED_REQMT_STATUS',l_assignment_status_code);
2274 ELSE
2275 FND_PROFILE.Get('PA_DEF_CANCELED_ASGMT_STATUS',l_assignment_status_code);
2276 END IF;
2277
2278 IF l_assignment_status_code IS NOT NULL THEN
2279
2280 --call Change_Assignment_Status
2281 PA_ASSIGNMENT_APPROVAL_PUB.Change_Assignment_Status
2282 (
2283 p_record_version_number => p_record_version_number
2284 ,p_assignment_id => p_assignment_id
2285 ,p_assignment_type => p_assignment_type
2286 ,p_start_date => p_start_date
2287 ,p_end_date => p_end_date
2288 ,p_assignment_status_code => l_assignment_status_code
2289 ,x_return_status => x_return_status
2290 ,x_msg_count => x_msg_count
2291 ,x_msg_data => x_msg_data );
2292
2293 ELSE
2294 PA_UTILS.Add_Message ( p_app_short_name => 'PA'
2295 ,p_msg_name => 'PA_START_STATUS_NOT_DEFINED');
2296
2297 END IF;
2298
2299 --
2300 -- IF the number of messaages is 1 then fetch the message code from the stack and return its text
2301 --
2302 x_msg_count := FND_MSG_PUB.Count_Msg;
2303 IF x_msg_count = 1 THEN
2304 pa_interface_utils_pub.get_messages ( p_encoded => FND_API.G_TRUE
2305 ,p_msg_index => 1
2306 ,p_data => x_msg_data
2307 ,p_msg_index_out => l_msg_index_out
2308 );
2309 END IF;
2310
2311 -- Reset the error stack when returning to the calling program
2312 PA_DEBUG.Reset_Err_Stack;
2313
2314
2315 IF x_msg_count > 0 THEN
2316
2317 x_return_status := FND_API.G_RET_STS_ERROR;
2318
2319 END IF;
2320
2321 EXCEPTION
2322 WHEN OTHERS THEN
2323 -- Set the excetption Message and the stack
2324 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_ASSIGNMENT_APPROVAL_PUB.Cancel_Assignment'
2325 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
2326 --
2327 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2328 RAISE; -- This is optional depending on the needs
2329
2330 END Cancel_Assignment;
2331
2332 /* --------------------------Begin Mass Assignment Approval Code-------------------------------------*/
2333 /* Added a new parameter p_overriding_authority_flag in the procedure below for the bug 3213509 */
2334 --BEGIN FORWARD DECLARATIONS
2335
2336 PROCEDURE validate_approver_name_id
2337 ( p_project_id IN NUMBER
2338 ,p_assignment_id_tbl IN SYSTEM.pa_num_tbl_type := prm_empty_num_tbl
2339 ,p_approver1_id_tbl IN SYSTEM.pa_num_tbl_type := prm_empty_num_tbl
2340 ,p_approver1_name_tbl IN SYSTEM.pa_varchar2_240_tbl_type := prm_empty_varchar2_240_tbl
2341 ,p_approver2_id_tbl IN SYSTEM.pa_num_tbl_type := prm_empty_num_tbl
2342 ,p_approver2_name_tbl IN SYSTEM.pa_varchar2_240_tbl_type := prm_empty_varchar2_240_tbl
2343 ,p_submitter_user_id IN NUMBER
2344 ,p_group_id IN NUMBER
2345 ,p_api_version IN NUMBER := 1.0
2346 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
2347 ,p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM
2348 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
2349 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
2350 ,p_overriding_authority_flag IN VARCHAR2 := 'N'
2351 ,x_assignment_id_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type
2352 ,x_approver1_id_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type
2353 ,x_approver2_id_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type
2354 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2355 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2356 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2357 );
2358
2359 --END FORWARD DECLARATIONS
2360
2361
2362
2363 ---------------------------------------------------------------
2364 --This API is called from the Mass Submit for approval pages.
2365 --The IN parameters are all parameters needed to call
2366 --the mass transaction API. This API is called online
2367 --and so there are no validations, it calls the mass
2368 --transaction API by passing all the IN variables
2369 ---------------------------------------------------------------
2370 PROCEDURE mass_submit_for_asgmt_aprvl
2371 (p_mode IN VARCHAR2
2372 ,p_action IN VARCHAR2
2373 ,p_resource_id_tbl IN SYSTEM.pa_num_tbl_type := prm_empty_num_tbl
2374 ,p_assignment_id_tbl IN SYSTEM.pa_num_tbl_type := prm_empty_num_tbl
2375 ,p_assignment_name IN pa_project_assignments.assignment_name%TYPE := FND_API.G_MISS_CHAR
2376 ,p_assignment_type IN pa_project_assignments.assignment_type%TYPE := FND_API.G_MISS_CHAR
2377 ,p_status_code IN pa_project_assignments.status_code%TYPE := FND_API.G_MISS_CHAR
2378 ,p_multiple_status_flag IN pa_project_assignments.multiple_status_flag%TYPE := FND_API.G_MISS_CHAR
2379 ,p_staffing_priority_code IN pa_project_assignments.staffing_priority_code%TYPE := FND_API.G_MISS_CHAR
2380 ,p_project_id IN pa_project_assignments.project_id%TYPE := FND_API.G_MISS_NUM
2381 ,p_project_role_id IN pa_project_assignments.project_role_id%TYPE := FND_API.G_MISS_NUM
2382 ,p_role_list_id IN pa_role_lists.role_list_id%TYPE := FND_API.G_MISS_NUM
2383 ,p_project_subteam_id IN pa_project_subteams.project_subteam_id%TYPE := FND_API.G_MISS_NUM
2384 ,p_description IN pa_project_assignments.description%TYPE := FND_API.G_MISS_CHAR
2385 ,p_append_description_flag IN VARCHAR2 := 'N'
2386 ,p_start_date IN pa_project_assignments.start_date%TYPE := FND_API.G_MISS_DATE
2387 ,p_end_date IN pa_project_assignments.end_date%TYPE := FND_API.G_MISS_DATE
2388 ,p_extension_possible IN pa_project_assignments.extension_possible%TYPE := FND_API.G_MISS_CHAR
2389 ,p_min_resource_job_level IN pa_project_assignments.min_resource_job_level%TYPE := FND_API.G_MISS_NUM
2390 ,p_max_resource_job_level IN pa_project_assignments.max_resource_job_level%TYPE := FND_API.G_MISS_NUM
2391 ,p_additional_information IN pa_project_assignments.additional_information%TYPE := FND_API.G_MISS_CHAR
2392 ,p_append_information_flag IN VARCHAR2 := 'N'
2393 ,p_location_id IN pa_project_assignments.location_id%TYPE := FND_API.G_MISS_NUM
2394 ,p_work_type_id IN pa_project_assignments.work_type_id%TYPE := FND_API.G_MISS_NUM
2395 ,p_calendar_type IN pa_project_assignments.calendar_type%TYPE := FND_API.G_MISS_CHAR
2396 ,p_calendar_id IN pa_project_assignments.calendar_id%TYPE := FND_API.G_MISS_NUM
2397 ,p_resource_calendar_percent IN pa_project_assignments.resource_calendar_percent%TYPE := FND_API.G_MISS_NUM
2398 ,p_project_name IN pa_projects_all.name%TYPE := FND_API.G_MISS_CHAR
2399 ,p_project_number IN pa_projects_all.segment1%TYPE := FND_API.G_MISS_CHAR
2400 ,p_project_subteam_name IN pa_project_subteams.name%TYPE := FND_API.G_MISS_CHAR
2401 ,p_project_status_name IN pa_project_statuses.project_status_name%TYPE := FND_API.G_MISS_CHAR
2402 ,p_staffing_priority_name IN pa_lookups.meaning%TYPE := FND_API.G_MISS_CHAR
2403 ,p_project_role_name IN pa_project_role_types.meaning%TYPE := FND_API.G_MISS_CHAR
2404 ,p_location_city IN pa_locations.city%TYPE := FND_API.G_MISS_CHAR
2405 ,p_location_region IN pa_locations.region%TYPE := FND_API.G_MISS_CHAR
2406 ,p_location_country_name IN fnd_territories_tl.territory_short_name%TYPE := FND_API.G_MISS_CHAR
2407 ,p_location_country_code IN pa_locations.country_code%TYPE := FND_API.G_MISS_CHAR
2408 ,p_calendar_name IN jtf_calendars_tl.calendar_name%TYPE := FND_API.G_MISS_CHAR
2409 ,p_work_type_name IN pa_work_types_vl.name%TYPE := FND_API.G_MISS_CHAR
2410 ,p_expense_owner IN pa_project_assignments.expense_owner%TYPE := FND_API.G_MISS_CHAR
2411 ,p_expense_limit IN pa_project_assignments.expense_limit%TYPE := FND_API.G_MISS_NUM
2412 ,p_expense_limit_currency_code IN pa_project_assignments.expense_limit_currency_code%TYPE := FND_API.G_MISS_CHAR
2413 ,p_fcst_tp_amount_type IN pa_project_assignments.fcst_tp_amount_type%TYPE := FND_API.G_MISS_CHAR
2414 ,p_fcst_job_id IN pa_project_assignments.fcst_job_id%TYPE := FND_API.G_MISS_NUM
2415 ,p_fcst_job_group_id IN pa_project_assignments.fcst_job_group_id%TYPE := FND_API.G_MISS_NUM
2416 ,p_expenditure_org_id IN pa_project_assignments.expenditure_org_id%TYPE := FND_API.G_MISS_NUM
2417 ,p_expenditure_organization_id IN pa_project_assignments.expenditure_organization_id%TYPE := FND_API.G_MISS_NUM
2418 ,p_expenditure_type_class IN pa_project_assignments.expenditure_type_class%TYPE := FND_API.G_MISS_CHAR
2419 ,p_expenditure_type IN pa_project_assignments.expenditure_type%TYPE := FND_API.G_MISS_CHAR
2420 ,p_comp_match_weighting IN pa_project_assignments.competence_match_weighting%TYPE := FND_API.G_MISS_NUM
2421 ,p_avail_match_weighting IN pa_project_assignments.availability_match_weighting%TYPE := FND_API.G_MISS_NUM
2422 ,p_job_level_match_weighting IN pa_project_assignments.job_level_match_weighting%TYPE := FND_API.G_MISS_NUM
2423 ,p_search_min_availability IN pa_project_assignments.search_min_availability%TYPE := FND_API.G_MISS_NUM
2424 ,p_search_country_code IN pa_project_assignments.search_country_code%TYPE := FND_API.G_MISS_CHAR
2425 ,p_search_country_name IN fnd_territories_vl.territory_short_name%TYPE := FND_API.G_MISS_CHAR
2426 ,p_search_exp_org_struct_ver_id IN pa_project_assignments.search_exp_org_struct_ver_id%TYPE := FND_API.G_MISS_NUM
2427 ,p_search_exp_org_hier_name IN per_organization_structures.name%TYPE := FND_API.G_MISS_CHAR
2428 ,p_search_exp_start_org_id IN pa_project_assignments.search_exp_start_org_id%TYPE := FND_API.G_MISS_NUM
2429 ,p_search_exp_start_org_name IN hr_organization_units.name%TYPE := FND_API.G_MISS_CHAR
2430 ,p_search_min_candidate_score IN pa_project_assignments.search_min_candidate_score%TYPE := FND_API.G_MISS_NUM
2431 ,p_enable_auto_cand_nom_flag IN pa_project_assignments.enable_auto_cand_nom_flag%TYPE := FND_API.G_MISS_CHAR
2432 ,p_staffing_owner_person_id IN pa_project_assignments.staffing_owner_person_id%TYPE := FND_API.G_MISS_NUM
2433 ,p_staffing_owner_name IN per_people_f.full_name%TYPE := FND_API.G_MISS_CHAR
2434 ,p_fcst_job_name IN per_jobs.name%TYPE := FND_API.G_MISS_CHAR
2435 ,p_fcst_job_group_name IN per_job_groups.displayed_name%TYPE := FND_API.G_MISS_CHAR
2436 ,p_expenditure_org_name IN per_organization_units.name%TYPE := FND_API.G_MISS_CHAR
2437 ,p_exp_organization_name IN per_organization_units.name%TYPE := FND_API.G_MISS_CHAR
2438 ,p_exception_type_code IN VARCHAR2 := FND_API.G_MISS_CHAR
2439 ,p_change_start_date IN DATE := FND_API.G_MISS_DATE
2440 ,p_change_end_date IN DATE := FND_API.G_MISS_DATE
2441 ,p_change_rqmt_status_code IN VARCHAR2 := FND_API.G_MISS_CHAR
2442 ,p_change_asgmt_status_code IN VARCHAR2 := FND_API.G_MISS_CHAR
2443 ,p_change_start_date_tbl IN SYSTEM.PA_DATE_TBL_TYPE := NULL
2444 ,p_change_end_date_tbl IN SYSTEM.PA_DATE_TBL_TYPE := NULL
2445 ,p_monday_hours_tbl IN SYSTEM.PA_NUM_TBL_TYPE := NULL
2446 ,p_tuesday_hours_tbl IN SYSTEM.PA_NUM_TBL_TYPE := NULL
2447 ,p_wednesday_hours_tbl IN SYSTEM.PA_NUM_TBL_TYPE := NULL
2448 ,p_thursday_hours_tbl IN SYSTEM.PA_NUM_TBL_TYPE := NULL
2449 ,p_friday_hours_tbl IN SYSTEM.PA_NUM_TBL_TYPE := NULL
2450 ,p_saturday_hours_tbl IN SYSTEM.PA_NUM_TBL_TYPE := NULL
2451 ,p_sunday_hours_tbl IN SYSTEM.PA_NUM_TBL_TYPE := NULL
2452 ,p_non_working_day_flag IN VARCHAR2 := 'N'
2453 ,p_change_hours_type_code IN VARCHAR2 := FND_API.G_MISS_CHAR
2454 ,p_hrs_per_day IN NUMBER := FND_API.G_MISS_NUM
2455 ,p_calendar_percent IN NUMBER := FND_API.G_MISS_NUM
2456 ,p_change_calendar_type_code IN VARCHAR2 := FND_API.G_MISS_CHAR
2457 ,p_change_calendar_name IN VARCHAR2 := FND_API.G_MISS_CHAR
2458 ,p_change_calendar_id IN NUMBER := FND_API.G_MISS_NUM
2459 ,p_duration_shift_type_code IN VARCHAR2 := FND_API.G_MISS_CHAR
2460 ,p_duration_shift_unit_code IN VARCHAR2 := FND_API.G_MISS_CHAR
2461 ,p_num_of_shift IN NUMBER := FND_API.G_MISS_NUM
2462 ,p_approver1_id_tbl IN SYSTEM.pa_num_tbl_type := prm_empty_num_tbl
2463 ,p_approver1_name_tbl IN SYSTEM.pa_varchar2_240_tbl_type := prm_empty_varchar2_240_tbl
2464 ,p_approver2_id_tbl IN SYSTEM.pa_num_tbl_type := prm_empty_num_tbl
2465 ,p_approver2_name_tbl IN SYSTEM.pa_varchar2_240_tbl_type := prm_empty_varchar2_240_tbl
2466 ,p_appr_over_auth_flag IN VARCHAR2 := 'N'
2467 ,p_note_to_all_approvers IN VARCHAR2 := FND_API.G_MISS_CHAR
2468 ,p_competence_id_tbl IN SYSTEM.pa_num_tbl_type := prm_empty_num_tbl
2469 ,p_competence_name_tbl IN SYSTEM.pa_varchar2_240_tbl_type := prm_empty_varchar2_240_tbl
2470 ,p_competence_alias_tbl IN SYSTEM.pa_varchar2_30_tbl_type := prm_empty_varchar2_30_tbl
2471 ,p_rating_level_id_tbl IN SYSTEM.pa_num_tbl_type := prm_empty_num_tbl
2472 ,p_mandatory_flag_tbl IN SYSTEM.pa_varchar2_1_tbl_type := prm_empty_varchar2_1_tbl
2473 ,p_resolve_con_action_code IN VARCHAR2 := FND_API.G_MISS_CHAR
2474 ,p_api_version IN NUMBER := 1.0
2475 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
2476 ,p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM
2477 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
2478 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
2479 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2480 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2481 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2482 )
2483 IS
2484
2485 l_project_manager_person_id NUMBER ;
2486 l_project_manager_name VARCHAR2(200);
2487 l_project_party_id NUMBER ;
2488 l_project_role_id NUMBER ;
2489 l_project_role_name VARCHAR2(80);
2490 l_admin_project VARCHAR2(1); --Variable which denotes if a project is an admin project or not
2491 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
2492 l_error_message_code fnd_new_messages.message_name%TYPE;
2493 l_assignment_start_date DATE;
2494 l_approver1_person_id NUMBER;
2495 l_approver1_person_name VARCHAR2(200);
2496 l_approver2_person_id NUMBER;
2497 l_approver2_person_name VARCHAR2(200);
2498 l_resource_type_id NUMBER;--Used in Name ID validation
2499 l_msg_index_out NUMBER;
2500
2501 --These are the local copies of the approver ids used in Name ID validation
2502 l_approver1_id_tbl SYSTEM.pa_num_tbl_type;
2503 l_approver2_id_tbl SYSTEM.pa_num_tbl_type;
2504 l_project_id NUMBER;
2505
2506 BEGIN
2507
2508 -- Initialize the Error Stack
2509 PA_DEBUG.init_err_stack('PA_ASSIGNMENT_APPROVAL_PUB.mass_submit_for_assignment_approval');
2510
2511 --Log Message
2512 IF P_DEBUG_MODE = 'Y' THEN
2513 PA_DEBUG.write_log
2514 ( x_module => 'pa.plsql.PA_ASSIGNMENT_APPROVAL_PUB.mass_submit_for_assignment_approval.begin'
2515 ,x_msg => 'Beginning of mass_submit_for_assignment_approval'
2516 ,x_log_level => 1);
2517 END IF;
2518
2519 -- Initialize the return status to success
2520 x_return_status := FND_API.G_RET_STS_SUCCESS;
2521
2522 --Clear the global PL/SQL message table
2523 FND_MSG_PUB.initialize;
2524
2525 -- Issue API savepoint if the transaction is to be committed
2526 IF p_commit = FND_API.G_TRUE THEN
2527 SAVEPOINT MASS_SUBMIT_ASGN_APPRVL;
2528 END IF;
2529
2530 -----------------------------------------
2531 --Initialize the local approver id tables
2532 -----------------------------------------
2533 l_approver1_id_tbl := p_approver1_id_tbl;
2534 l_approver2_id_tbl := p_approver2_id_tbl;
2535
2536 --Getting Project id from project number if
2537 --project id = null
2538
2539 l_project_id := p_project_id;
2540
2541 IF l_project_id is null THEN
2542
2543 SELECT project_id
2544 INTO l_project_id
2545 FROM pa_projects_all
2546 WHERE segment1 = p_project_number;
2547
2548 END IF;
2549
2550 --Get the project type
2551 SELECT NVL(pt.administrative_flag,'N') admin_flag
2552 INTO l_admin_project
2553 FROM pa_projects_all pap,
2554 pa_project_types_all pt
2555 WHERE pap.project_id = l_project_id
2556 AND pt.project_type = pap.project_type
2557 -- AND nvl(pap.org_id, -99) = nvl(pt.org_id, -99); /* Added nvl for bug#2467666 */ -R12: Bug 4633092
2558 AND pap.org_id = pt.org_id;
2559
2560 -------------------------------------------------------------
2561 --Validate if a project manager exists for non-admin projects
2562 -------------------------------------------------------------
2563 IF l_admin_project = 'N' THEN
2564
2565 --Log Message
2566 IF P_DEBUG_MODE = 'Y' THEN
2567 PA_DEBUG.write_log
2568 (x_module => 'PA_ASSIGNMENT_APPROVAL_PUB.mass_submit_for_assignment_approval.check_prj_manager.'
2569 ,x_msg => 'Check if project manger exists.'
2570 ,x_log_level => 1);
2571 END IF;
2572
2573 pa_project_parties_utils.get_curr_proj_mgr_details
2574 ( p_project_id => l_project_id
2575 ,x_manager_person_id => l_project_manager_person_id
2576 ,x_manager_name => l_project_manager_name
2577 ,x_project_party_id => l_project_party_id
2578 ,x_project_role_id => l_project_role_id
2579 ,x_project_role_name => l_project_role_name
2580 ,x_return_status => l_return_status
2581 ,x_error_message_code => l_error_message_code );
2582
2583 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2584 PA_UTILS.Add_Message( p_app_short_name => 'PA'
2585 ,p_msg_name => l_error_message_code);
2586 END IF;
2587
2588 l_error_message_code := FND_API.G_MISS_CHAR;
2589
2590 END IF;
2591
2592 IF l_return_status <> FND_API.G_RET_STS_ERROR THEN
2593
2594 ---------------------------------
2595 --Call the mass transaction API
2596 ---------------------------------
2597 PA_MASS_ASGMT_TRX.Start_Mass_Asgmt_Trx_Wf(
2598 p_mode => p_mode
2599 ,p_action => p_action
2600 ,p_resource_id_tbl => p_resource_id_tbl
2601 ,p_assignment_id_tbl => p_assignment_id_tbl
2602 ,p_assignment_name => p_assignment_name
2603 ,p_assignment_type => p_assignment_type
2604 ,p_status_code => p_status_code
2605 ,p_multiple_status_flag => p_multiple_status_flag
2606 ,p_staffing_priority_code => p_staffing_priority_code
2607 ,p_project_id => l_project_id
2608 ,p_project_role_id => p_project_role_id
2609 ,p_role_list_id => p_role_list_id
2610 ,p_project_subteam_id => p_project_subteam_id
2611 ,p_description => p_description
2612 ,p_append_description_flag => p_append_description_flag
2613 ,p_start_date => p_start_date
2614 ,p_end_date => p_end_date
2615 ,p_extension_possible => p_extension_possible
2616 ,p_min_resource_job_level => p_min_resource_job_level
2617 ,p_max_resource_job_level => p_max_resource_job_level
2618 ,p_additional_information => p_additional_information
2619 ,p_append_information_flag => p_append_information_flag
2620 ,p_location_id => p_location_id
2621 ,p_work_type_id => p_work_type_id
2622 ,p_calendar_type => p_calendar_type
2623 ,p_calendar_id => p_calendar_id
2624 ,p_resource_calendar_percent => p_resource_calendar_percent
2625 ,p_project_name => p_project_name
2626 ,p_project_number => p_project_number
2627 ,p_project_subteam_name => p_project_subteam_name
2628 ,p_project_status_name => p_project_status_name
2629 ,p_staffing_priority_name => p_staffing_priority_name
2630 ,p_project_role_name => p_project_role_name
2631 ,p_location_city => p_location_city
2632 ,p_location_region => p_location_region
2633 ,p_location_country_name => p_location_country_name
2634 ,p_location_country_code => p_location_country_code
2635 ,p_calendar_name => p_calendar_name
2636 ,p_work_type_name => p_work_type_name
2637 ,p_expense_owner => p_expense_owner
2638 ,p_expense_limit => p_expense_limit
2639 ,p_expense_limit_currency_code => p_expense_limit_currency_code
2640 ,p_fcst_tp_amount_type => p_fcst_tp_amount_type
2641 ,p_fcst_job_id => p_fcst_job_id
2642 ,p_fcst_job_group_id => p_fcst_job_group_id
2643 ,p_expenditure_org_id => p_expenditure_org_id
2644 ,p_expenditure_organization_id => p_expenditure_organization_id
2645 ,p_expenditure_type_class => p_expenditure_type_class
2646 ,p_expenditure_type => p_expenditure_type
2647 ,p_comp_match_weighting => p_comp_match_weighting
2648 ,p_avail_match_weighting => p_avail_match_weighting
2649 ,p_job_level_match_weighting => p_job_level_match_weighting
2650 ,p_search_min_availability => p_search_min_availability
2651 ,p_search_country_code => p_search_country_code
2652 ,p_search_country_name => p_search_country_name
2653 ,p_search_exp_org_struct_ver_id => p_search_exp_org_struct_ver_id
2654 ,p_search_exp_org_hier_name => p_search_exp_org_hier_name
2655 ,p_search_exp_start_org_id => p_search_exp_start_org_id
2656 ,p_search_exp_start_org_name => p_search_exp_start_org_name
2657 ,p_search_min_candidate_score => p_search_min_candidate_score
2658 ,p_enable_auto_cand_nom_flag => p_enable_auto_cand_nom_flag
2659 ,p_staffing_owner_person_id => p_staffing_owner_person_id
2660 ,p_staffing_owner_name => p_staffing_owner_name
2661 ,p_fcst_job_name => p_fcst_job_name
2662 ,p_fcst_job_group_name => p_fcst_job_group_name
2663 ,p_expenditure_org_name => p_expenditure_org_name
2664 ,p_exp_organization_name => p_exp_organization_name
2665 ,p_exception_type_code => p_exception_type_code
2666 ,p_change_start_date => p_change_start_date
2667 ,p_change_end_date => p_change_end_date
2668 ,p_change_rqmt_status_code => p_change_rqmt_status_code
2669 ,p_change_asgmt_status_code => p_change_asgmt_status_code
2670 ,p_change_start_date_tbl => p_change_start_date_tbl
2671 ,p_change_end_date_tbl => p_change_end_date_tbl
2672 ,p_monday_hours_tbl => p_monday_hours_tbl
2673 ,p_tuesday_hours_tbl => p_tuesday_hours_tbl
2674 ,p_wednesday_hours_tbl => p_wednesday_hours_tbl
2675 ,p_thursday_hours_tbl => p_thursday_hours_tbl
2676 ,p_friday_hours_tbl => p_friday_hours_tbl
2677 ,p_saturday_hours_tbl => p_saturday_hours_tbl
2678 ,p_sunday_hours_tbl => p_sunday_hours_tbl
2679 ,p_non_working_day_flag => p_non_working_day_flag
2680 ,p_change_hours_type_code => p_change_hours_type_code
2681 ,p_hrs_per_day => p_hrs_per_day
2682 ,p_calendar_percent => p_calendar_percent
2683 ,p_change_calendar_type_code => p_change_calendar_type_code
2684 ,p_change_calendar_name => p_change_calendar_name
2685 ,p_change_calendar_id => p_change_calendar_id
2686 ,p_duration_shift_type_code => p_duration_shift_type_code
2687 ,p_duration_shift_unit_code => p_duration_shift_unit_code
2688 ,p_num_of_shift => p_num_of_shift
2689 ,p_approver1_id_tbl => l_approver1_id_tbl --The local updated table is passed
2690 ,p_approver1_name_tbl => p_approver1_name_tbl
2691 ,p_approver2_id_tbl => l_approver2_id_tbl --The local updated table is passed
2692 ,p_approver2_name_tbl => p_approver2_name_tbl
2693 ,p_appr_over_auth_flag => p_appr_over_auth_flag
2694 ,p_note_to_all_approvers => p_note_to_all_approvers
2695 ,p_competence_id_tbl => p_competence_id_tbl
2696 ,p_competence_name_tbl => p_competence_name_tbl
2697 ,p_competence_alias_tbl => p_competence_alias_tbl
2698 ,p_rating_level_id_tbl => p_rating_level_id_tbl
2699 ,p_mandatory_flag_tbl => p_mandatory_flag_tbl
2700 ,p_resolve_con_action_code => p_resolve_con_action_code
2701 ,x_return_status => l_return_status );
2702
2703 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2704 x_return_status := l_return_status;
2705 END IF;
2706
2707 END IF;
2708
2709 x_msg_count := FND_MSG_PUB.Count_Msg;
2710
2711 -- IF the number of messages is 1 then fetch the message code from
2712 -- the stack and return its text
2713 IF x_msg_count = 1 THEN
2714 pa_interface_utils_pub.get_messages
2715 ( p_encoded => FND_API.G_TRUE
2716 ,p_msg_index => 1
2717 ,p_data => x_msg_data
2718 ,p_msg_index_out => l_msg_index_out );
2719 END IF;
2720
2721 -- Reset the error stack when returning to the calling program
2722 PA_DEBUG.Reset_Err_Stack;
2723
2724 -- If g_error_exists is TRUE then set the x_return_status to 'E'
2725 IF FND_MSG_PUB.Count_Msg >0 THEN
2726 x_return_status := FND_API.G_RET_STS_ERROR;
2727 END IF;
2728
2729 EXCEPTION
2730 WHEN OTHERS THEN
2731
2732 IF p_commit = FND_API.G_TRUE THEN
2733 ROLLBACK TO MASS_SUBMIT_ASGN_APPRVL;
2734 END IF;
2735
2736 -- Set the excetption Message and the stack
2737 FND_MSG_PUB.add_exc_msg
2738 ( p_pkg_name => 'PA_ASSIGNMENT_APPROVAL_PUB.mass_submit_for_asgmt_aprvl'
2739 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
2740
2741 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2742 RAISE;
2743
2744 END mass_submit_for_asgmt_aprvl;
2745
2746 ---------------------------------------------------------------------------
2747 --This API is called from the Mass Transaction Server API
2748 --This is the main API which starts the assignment
2749 --approval process
2750 --The IN parameters are assignments table which are submitted for
2751 --approval along with 2 approvers tables
2752 ---------------------------------------------------------------------------
2753 PROCEDURE mass_assignment_approval
2754 ( p_project_id IN pa_project_assignments.project_id%TYPE := FND_API.G_MISS_NUM
2755 ,p_mode IN VARCHAR2
2756 ,p_assignment_id_tbl IN SYSTEM.pa_num_tbl_type := prm_empty_num_tbl
2757 ,p_approver1_id_tbl IN SYSTEM.pa_num_tbl_type := prm_empty_num_tbl
2758 ,p_approver1_name_tbl IN SYSTEM.pa_varchar2_240_tbl_type := prm_empty_varchar2_240_tbl
2759 ,p_approver2_id_tbl IN SYSTEM.pa_num_tbl_type := prm_empty_num_tbl
2760 ,p_approver2_name_tbl IN SYSTEM.pa_varchar2_240_tbl_type := prm_empty_varchar2_240_tbl
2761 ,p_overriding_authority_flag IN VARCHAR2 := 'N'
2762 ,p_submitter_user_id IN NUMBER := FND_API.G_MISS_NUM
2763 ,p_note_to_all_approvers IN VARCHAR2 := FND_API.G_MISS_CHAR
2764 ,p_conflict_group_id IN NUMBER := FND_API.G_MISS_NUM
2765 ,p_update_info_doc IN VARCHAR2 := FND_API.G_MISS_CHAR
2766 ,p_api_version IN NUMBER := 1.0
2767 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
2768 ,p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM
2769 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
2770 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
2771 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2772 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2773 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2774 )
2775 IS
2776
2777 l_return_status VARCHAR2(1);
2778 l_error_message_code fnd_new_messages.message_name%TYPE;
2779 x_error_message_code fnd_new_messages.message_name%TYPE;
2780 l_msg_index_out NUMBER;
2781 l_msg_count NUMBER;
2782 l_msg_data VARCHAR2(2000);
2783 l_data VARCHAR2(2000);
2784 l_assignment_status VARCHAR2(10) := PA_ASSIGNMENT_APPROVAL_PUB.g_submit_action;
2785 l_approver1_id_tbl SYSTEM.pa_num_tbl_type;
2786 l_approver1_name_tbl SYSTEM.pa_varchar2_240_tbl_type;
2787 l_approver2_id_tbl SYSTEM.pa_num_tbl_type;
2788 l_approver2_name_tbl SYSTEM.pa_varchar2_240_tbl_type;
2789 l_assignment_id_tbl SYSTEM.pa_num_tbl_type;
2790 l_apr1_res_auth VARCHAR2(1) := 'Y';
2791 l_apr2_res_auth VARCHAR2(1) := 'Y';
2792 l_new_asgmt_flag VARCHAR2(1);
2793 x_aprvl_required VARCHAR2(1);
2794 l_routing_order NUMBER;
2795 l_change_id NUMBER;
2796 l_record_version_number NUMBER;
2797 p_record_version_number NUMBER;
2798 l_apprvl_status_code pa_project_assignments.apprvl_status_code%TYPE;
2799 l_project_super_user_submitter VARCHAR2(1);
2800 l_display_name VARCHAR2(360); /* Modified length from 200 to 360 for bug 3148857 */
2801 l_approver_name VARCHAR2(320); /* Modified length from 200 to 320 for bug 3148857 */
2802
2803 CURSOR resource_id (p_assignment_id NUMBER) IS
2804 SELECT resource_id
2805 FROM pa_project_assignments
2806 WHERE assignment_id = p_assignment_id;
2807
2808 l_resource_id NUMBER;
2809
2810 CURSOR get_rec_num ( p_assignment_id IN NUMBER )IS
2811 SELECT record_version_number
2812 FROM pa_project_assignments
2813 WHERE assignment_id = p_assignment_id;
2814
2815 CURSOR l_stus_csr IS
2816 SELECT ps.enable_wf_flag
2817 FROM pa_project_statuses ps
2818 WHERE ps.project_status_code = PA_ASSIGNMENT_APPROVAL_PUB.g_submitted;
2819
2820 l_stus_rec l_stus_csr%ROWTYPE;
2821
2822 l_approvers_list_tbl PA_CLIENT_EXTN_ASGMT_WF.Users_List_Tbltyp ;
2823 l_out_approvers_list_tbl PA_CLIENT_EXTN_ASGMT_WF.Users_List_Tbltyp ;
2824 l_number_of_approvers NUMBER := 0;
2825 l_approvers_list_rec PA_CLIENT_EXTN_ASGMT_WF.Users_List_Rectyp ;
2826 l_approver_person_id NUMBER := 0;
2827
2828 l_group_id NUMBER;
2829 l_approver_group_id NUMBER;
2830
2831 CURSOR distinct_approvers IS
2832 SELECT distinct user_name
2833 FROM pa_wf_ntf_performers ntf,
2834 pa_project_assignments asgn
2835 WHERE ntf.group_id = l_group_id
2836 AND ntf.object_id1 = asgn.assignment_id
2837 AND asgn.apprvl_status_code = PA_ASSIGNMENT_APPROVAL_PUB.g_submitted
2838 AND ntf.routing_order = 1;
2839
2840 l_customer_id NUMBER;
2841 l_customer_name VARCHAR2(2000);
2842 l_project_manager_person_id NUMBER ;
2843 l_project_manager_name VARCHAR2(200);
2844 l_project_party_id NUMBER ;
2845 l_project_role_id NUMBER ;
2846 l_project_role_name VARCHAR2(80);
2847 l_submitter_person_id NUMBER;
2848 l_submitter_user_name VARCHAR2(100); /* Modified length from 30 to 100 for bug 3148857 */
2849
2850 CURSOR l_projects_csr(l_project_id IN NUMBER) IS
2851 SELECT pap.project_id project_id,
2852 pap.name name,
2853 pap.segment1 segment1,
2854 pap.carrying_out_organization_id carrying_out_organization_id,
2855 pap.location_id,
2856 hr.name organization_name,
2857 NVL(pt.administrative_flag,'N') admin_flag
2858 FROM pa_projects_all pap,
2859 hr_all_organization_units_tl hr, -- Bug 4358492
2860 pa_project_types_all pt
2861 WHERE pap.project_id = l_project_id
2862 AND pap.carrying_out_organization_id = hr.organization_id
2863 AND pt.project_type = pap.project_type
2864 AND pap.org_id = pt.org_id -- Bug 4358492
2865 AND userenv('LANG') = hr.language; -- Bug 4358492
2866
2867 l_projects_rec l_projects_csr%ROWTYPE;
2868
2869 CURSOR get_submitter_details IS
2870 SELECT employee_id,
2871 user_name
2872 FROM fnd_user
2873 WHERE user_id = p_submitter_user_id;
2874 l_submitter_rec get_submitter_details%ROWTYPE;
2875
2876 l_asgn_approval_status pa_project_assignments.apprvl_status_code%TYPE;
2877
2878 PROCESS_ASSIGNMENT_EXCEPTION EXCEPTION;
2879 NO_WORKFLOW_EXCEPTION EXCEPTION;
2880 INVALID_STATUS EXCEPTION;
2881
2882 l_num_apr_asgns NUMBER := 0;
2883 l_num_rej_asgns NUMBER := 0;
2884
2885 --Default value is Y
2886 --If any assignment is submitted then flag is set to N
2887 l_error_flag VARCHAR2(1) := 'Y';
2888 l_error_count NUMBER;
2889
2890 l_appr_asgmt_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
2891
2892 l_privelege varchar2(30);
2893 l_resource_super_user varchar2(1);
2894
2895 BEGIN
2896
2897 -- Initialize the Error Stack
2898 PA_DEBUG.init_err_stack('PA_ASSIGNMENT_APPROVAL_PUB.mass_assignment_approval');
2899
2900 --Log Message
2901 IF P_DEBUG_MODE = 'Y' THEN
2902 PA_DEBUG.write_log
2903 ( x_module => 'pa.plsql.PA_ASSIGNMENT_APPROVAL_PUB.mass_assignment_approval.begin'
2904 ,x_msg => 'Beginning of mass_assignment_approval'
2905 ,x_log_level => 1);
2906 END IF;
2907
2908 -- Initialize the return status to success
2909 x_return_status := FND_API.G_RET_STS_SUCCESS;
2910
2911 --Clear the global PL/SQL message table
2912 FND_MSG_PUB.initialize;
2913
2914 -- Issue API savepoint if the transaction is to be committed
2915 IF p_commit = FND_API.G_TRUE THEN
2916 SAVEPOINT MASS_ASGN_APPRVL;
2917 END IF;
2918
2919 ---------------------------------------------------------------------
2920 --Generate the group id for this mass transaction
2921 --This group id is inserted for every record in PA_WF_NTF_PERFORMERS
2922 --The group_id is inserted from the sequence PA_WF_NTF_PERFORMERS_S
2923 --The approver_group_id is initialy null
2924 --The group id is used to group all records which belong to this
2925 --mass transaction and is used in sending FYI notifications to
2926 --managers in the end when there are no pending approvals
2927 ---------------------------------------------------------------------
2928 SELECT PA_WF_NTF_PERFORMERS_S.nextval
2929 INTO l_group_id
2930 FROM dual;
2931
2932 OPEN get_submitter_details;
2933 FETCH get_submitter_details INTO l_submitter_rec;
2934 CLOSE get_submitter_details;
2935
2936 l_submitter_person_id := l_submitter_rec.employee_id;
2937 l_submitter_user_name := l_submitter_rec.user_name;
2938
2939 ------------------------------------------
2940 --Validate the Name Id for approvers
2941 ------------------------------------------
2942 log_message('Before Calling Validate_approver_name_id');
2943 /*Added a new parameter p_overriding_authority_flag for bug 3213509*/
2944 Validate_approver_name_id
2945 ( p_project_id => p_project_id
2946 ,p_assignment_id_tbl => p_assignment_id_tbl
2947 ,p_approver1_id_tbl => p_approver1_id_tbl
2948 ,p_approver1_name_tbl => p_approver1_name_tbl
2949 ,p_approver2_id_tbl => p_approver2_id_tbl
2950 ,p_approver2_name_tbl => p_approver2_name_tbl
2951 ,p_submitter_user_id => p_submitter_user_id
2952 ,p_group_id => l_group_id
2953 ,p_overriding_authority_flag => p_overriding_authority_flag
2954 ,x_assignment_id_tbl => l_assignment_id_tbl
2955 ,x_approver1_id_tbl => l_approver1_id_tbl
2956 ,x_approver2_id_tbl => l_approver2_id_tbl
2957 ,x_return_status => x_return_status
2958 ,x_msg_count => x_msg_count
2959 ,x_msg_data => x_msg_data);
2960
2961 log_message('After Calling Validate_approver_name_id');
2962
2963 ---------------------------------------------------------------------------------------
2964 --Getting the Project details once instead of in every call to start_mass_approval_flow
2965 ---------------------------------------------------------------------------------------
2966 OPEN l_projects_csr( p_project_id);
2967 FETCH l_projects_csr INTO l_projects_rec;
2968 IF l_projects_csr%NOTFOUND THEN
2969
2970 pa_utils.add_message (p_app_short_name => 'PA',
2971 p_msg_name => 'PA_INVALID_PROJECT_ID');
2972
2973 END IF;
2974 CLOSE l_projects_csr;
2975
2976 PA_ASGMT_WFSTD.Check_And_Get_Proj_Customer
2977 (p_project_id => p_project_id
2978 ,x_customer_id => l_customer_id
2979 ,x_customer_name => l_customer_name );
2980
2981 -- Get the project manager details
2982 pa_project_parties_utils.get_curr_proj_mgr_details
2983 (p_project_id => l_projects_rec.project_id
2984 ,x_manager_person_id => l_project_manager_person_id
2985 ,x_manager_name => l_project_manager_name
2986 ,x_project_party_id => l_project_party_id
2987 ,x_project_role_id => l_project_role_id
2988 ,x_project_role_name => l_project_role_name
2989 ,x_return_status => l_return_status
2990 ,x_error_message_code => l_error_message_code );
2991
2992 -- Only non-admin projects require a manager
2993 IF l_projects_rec.admin_flag = 'N' THEN
2994 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2995 pa_utils.add_message (p_app_short_name => 'PA',
2996 p_msg_name => l_error_message_code);
2997 END IF;
2998 END IF;
2999
3000 log_message('After Getting Project information');
3001
3002 -- Status Validation to check if workflow is enabled. If status is not valid,
3003 -- RAISE an ERROR.
3004 -- Also if Workflow is disabled - then exit
3005 OPEN l_stus_csr;
3006 FETCH l_stus_csr INTO l_stus_rec;
3007 IF l_stus_csr%NOTFOUND THEN
3008 l_return_status := FND_API.G_RET_STS_ERROR;
3009 l_error_message_code := 'PA_INVALID_STATUS_CODE';
3010 pa_utils.add_message (p_app_short_name => 'PA'
3011 ,p_msg_name => 'PA_INVALID_STATUS_CODE');
3012 RAISE INVALID_STATUS;
3013 log_message('Error in Workflow enabled check');
3014 END IF;
3015 CLOSE l_stus_csr;
3016
3017 IF NVL(l_stus_rec.enable_wf_flag,'N') = 'N' THEN
3018 pa_utils.add_message (p_app_short_name => 'PA'
3019 ,p_msg_name => 'PA_INVALID_STATUS_CODE');
3020 RAISE NO_WORKFLOW_EXCEPTION;
3021 END IF;
3022
3023 log_message('Completed Workflow enabled check');
3024
3025 --Initialize locals
3026 l_approver1_name_tbl := p_approver1_name_tbl;
3027 l_approver2_name_tbl := p_approver2_name_tbl;
3028
3029 FOR i in 1..l_assignment_id_tbl.COUNT LOOP
3030
3031 SAVEPOINT PROCESS_ASSIGNMENT_SUBMISSION;
3032
3033 BEGIN
3034
3035 log_message('Loop:' || i);
3036
3037 IF l_assignment_id_tbl(i) IS NOT NULL THEN
3038
3039 log_message('approver 1 id:' || l_approver1_id_tbl(i));
3040 log_message('approver 2 id:' || l_approver2_id_tbl(i));
3041
3042 OPEN resource_id ( l_assignment_id_tbl(i));
3043 FETCH resource_id INTO l_resource_id;
3044 CLOSE resource_id;
3045
3046 -------------------------------------------
3047 --Check if overriding authority flag is set
3048 -------------------------------------------
3049 IF p_overriding_authority_flag = 'Y' THEN
3050
3051 log_message('Overriding auth flag set');
3052
3053 ------------------------------------------------------------------------------------
3054 --Below logic determines if the submitter is the resource super user responsibility
3055 --There are 2 checks done
3056 -- 1. Profile check
3057 -- 2. User has privelege to confirm assignments
3058 --NOTE: The MASS ASSIGNMENT workflow has already initialized the submitters
3059 --responsibility id and product id and user id
3060 -----------------------------------------------------------------------------------
3061 l_project_super_user_submitter := fnd_profile.value_specific('PA_SUPER_RESOURCE',
3062 p_submitter_user_id,
3063 fnd_global.resp_id,
3064 fnd_global.resp_appl_id);
3065
3066 IF l_project_super_user_submitter = 'Y' THEN
3067
3068 IF l_projects_rec.admin_flag = 'N' THEN
3069 l_privelege := 'PA_ASN_CONFIRM';
3070 ELSIF l_projects_rec.admin_flag = 'Y' THEN
3071 l_privelege := 'PA_ADM_ASN_CONFIRM';
3072 END IF;
3073
3074 IF fnd_function.test(l_privelege) THEN
3075 l_resource_super_user := 'Y';
3076 ELSE
3077 l_resource_super_user := 'N';
3078 END IF;
3079
3080 END IF;
3081
3082 IF l_resource_super_user = 'Y' OR
3083 pa_resource_utils.check_user_has_res_auth (l_submitter_person_id, l_resource_id) = 'Y' THEN
3084 l_assignment_status := PA_ASSIGNMENT_APPROVAL_PUB.g_approve_action;
3085 ELSE --submitter does not have resource authority
3086
3087 ----------------------------------------------------------------------
3088 --If submitter has no resource authority and both approvers are null
3089 --assignment cannot be approved as there are no approvers
3090 ----------------------------------------------------------------------
3091 IF l_approver1_id_tbl(i) is null AND l_approver2_id_tbl(i) is null THEN
3092
3093 --Add error message to error stack
3094 l_error_message_code := 'PA_RESOURCE_NO_AUTH';
3095 PA_UTILS.Add_Message( p_app_short_name => 'PA'
3096 ,p_msg_name => l_error_message_code);
3097
3098 RAISE PROCESS_ASSIGNMENT_EXCEPTION;
3099 ELSE
3100 l_assignment_status := PA_ASSIGNMENT_APPROVAL_PUB.g_submit_action;
3101 END IF;
3102 END IF;
3103
3104 ELSIF l_approver1_id_tbl(i) = l_submitter_person_id THEN
3105
3106 IF l_approver2_id_tbl(i) is null THEN
3107 l_assignment_status := PA_ASSIGNMENT_APPROVAL_PUB.g_approve_action;
3108 ELSIF l_approver2_id_tbl(i) = l_submitter_person_id THEN
3109 l_assignment_status := PA_ASSIGNMENT_APPROVAL_PUB.g_approve_action;
3110 ELSE
3111 l_approver1_id_tbl(i) := l_approver2_id_tbl(i);
3112 l_approver1_name_tbl(i) := l_approver2_name_tbl(i);
3113 l_approver2_id_tbl(i) := null;
3114 l_approver2_name_tbl(i):= null;
3115 l_assignment_status := PA_ASSIGNMENT_APPROVAL_PUB.g_submit_action;
3116 END IF;
3117
3118 ELSIF l_approver1_id_tbl(i) is null AND l_approver2_id_tbl(i) is null THEN
3119
3120 --Add error message to error stack
3121 l_error_message_code := 'PA_RESOURCE_NO_AUTH';
3122 PA_UTILS.Add_Message( p_app_short_name => 'PA'
3123 ,p_msg_name => l_error_message_code);
3124 log_message('No approver');
3125 RAISE PROCESS_ASSIGNMENT_EXCEPTION;
3126
3127 END IF; --end l_overriding_authority_flag = 'Y
3128
3129 IF l_approver2_id_tbl(i) = l_submitter_person_id THEN
3130 l_approver2_id_tbl(i) := null;
3131 END IF;
3132
3133 --------------------------------
3134 --Need to reorder approver list
3135 --------------------------------
3136 IF l_approver1_id_tbl(i) is null THEN
3137 l_approver1_id_tbl(i) := l_approver2_id_tbl(i);
3138 l_approver1_name_tbl(i) := l_approver2_name_tbl(i);
3139 l_approver2_id_tbl(i) := null;
3140 l_approver2_name_tbl(i):= null;
3141 END IF;
3142
3143 ---------------------------------------------------------------------------------
3144 --Validate approver only if l_assignment_status = g_submit_action
3145 --Validate Approver One and Approver Two has resource authority over the resource
3146 --for that assignment, otherwise raise an error.
3147 ---------------------------------------------------------------------------------
3148 IF l_assignment_status = PA_ASSIGNMENT_APPROVAL_PUB.g_submit_action THEN
3149
3150
3151
3152 log_message('Checkin resource authority');
3153 log_message('Approver 1: ' || l_approver1_id_tbl(i));
3154 log_message('Approver 2: ' || l_approver2_id_tbl(i));
3155 log_message('Resource : ' || l_resource_id);
3156
3157 l_apr1_res_auth := pa_resource_utils.check_user_has_res_auth (l_approver1_id_tbl(i), l_resource_id);
3158
3159 IF l_approver2_id_tbl(i) is not null THEN
3160 l_apr2_res_auth := pa_resource_utils.check_user_has_res_auth (l_approver2_id_tbl(i), l_resource_id);
3161 END IF;
3162
3163 IF l_apr1_res_auth = 'N' OR l_apr2_res_auth = 'N' THEN
3164
3165 PA_UTILS.Add_Message( p_app_short_name => 'PA'
3166 ,p_msg_name => 'PA_RESOURCE_NO_AUTH');
3167 log_message('No resource authority 1');
3168 RAISE PROCESS_ASSIGNMENT_EXCEPTION;
3169
3170 END IF;
3171 END IF;
3172
3173 ------------------------------------------------------------------
3174 --When l_assignment_status is not null implies that the assignment
3175 --can be processed for assignment approval
3176 ------------------------------------------------------------------
3177 IF l_assignment_status = PA_ASSIGNMENT_APPROVAL_PUB.g_submit_action THEN
3178
3179 l_new_asgmt_flag := PA_ASSIGNMENT_APPROVAL_PVT.Is_New_Assignment( l_assignment_id_tbl(i) );
3180
3181 IF l_new_asgmt_flag = 'N' THEN
3182
3183 PA_ASSIGNMENT_APPROVAL_PVT.check_approval_required
3184 ( l_assignment_id_tbl(i)
3185 ,l_new_asgmt_flag
3186 ,x_aprvl_required
3187 ,l_return_status );
3188
3189 IF x_aprvl_required = 'N' THEN
3190 l_assignment_status := PA_ASSIGNMENT_APPROVAL_PUB.g_approve_action;
3191 END IF;
3192
3193 END IF;
3194
3195 END IF; --end l_assignment_status = 'SUBMIT'
3196
3197 ----------------------------------------------------------
3198 --Store previous value of assignment status to be inserted into
3199 --pa_wf_ntf_performers table
3200 ----------------------------------------------------------
3201 BEGIN
3202 SELECT apprvl_status_code
3203 INTO l_asgn_approval_status
3204 FROM pa_project_assignments
3205 WHERE assignment_id = l_assignment_id_tbl(i);
3206 EXCEPTION
3207 WHEN NO_DATA_FOUND THEN
3208 l_asgn_approval_status := null;
3209 END;
3210
3211 IF l_assignment_status = PA_ASSIGNMENT_APPROVAL_PUB.g_approve_action THEN
3212
3213 log_message('Approve action for loop' || i);
3214
3215 OPEN get_rec_num ( l_assignment_id_tbl(i) );
3216 FETCH get_rec_num INTO p_record_version_number;
3217 CLOSE get_rec_num;
3218
3219 PA_ASSIGNMENT_APPROVAL_PVT.Update_Approval_Status
3220 ( p_assignment_id => l_assignment_id_tbl(i)
3221 ,p_action_code => PA_ASSIGNMENT_APPROVAL_PUB.g_approve_action
3222 ,p_note_to_approver => p_note_to_all_approvers
3223 ,p_record_version_number => p_record_version_number
3224 ,x_apprvl_status_code => l_apprvl_status_code
3225 ,x_change_id => l_change_id
3226 ,x_record_version_number => l_record_version_number
3227 ,x_return_status => l_return_status
3228 ,x_msg_count => l_msg_count
3229 ,x_msg_data => l_msg_data);
3230
3231 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3232 l_return_status := FND_API.G_MISS_CHAR;
3233 log_message('Error in Update approval status 1');
3234 RAISE PROCESS_ASSIGNMENT_EXCEPTION;
3235 END IF;
3236
3237 -- resolve remaining conflicts by taking action chosen by user
3238 PA_SCHEDULE_PVT.resolve_conflicts (p_conflict_group_id => p_conflict_group_id
3239 ,p_assignment_id => l_assignment_id_tbl(i)
3240 ,x_return_status => l_return_status
3241 ,x_msg_count => l_msg_count
3242 ,x_msg_data => l_msg_data);
3243
3244 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3245 l_return_status := FND_API.G_MISS_CHAR;
3246 log_message('Error in conflict resolution');
3247 RAISE PROCESS_ASSIGNMENT_EXCEPTION;
3248 END IF;
3249
3250 ----------------------------------------------------------------------------
3251 --Insert this assignment into pa_wf_ntf_performers for sending manager
3252 --notifications. The approver is the submitter in this case
3253 ----------------------------------------------------------------------------
3254 IF l_submitter_user_name is not null THEN
3255
3256 INSERT INTO pa_wf_ntf_performers(
3257 WF_TYPE_CODE
3258 ,ITEM_TYPE
3259 ,ITEM_KEY
3260 ,OBJECT_ID1
3261 ,OBJECT_ID2
3262 ,GROUP_ID
3263 ,USER_NAME
3264 ,USER_TYPE
3265 ,ROUTING_ORDER
3266 ,APPROVAL_STATUS)
3267 VALUES ('MASS_ASSIGNMENT_APPROVAL'
3268 ,'-1'
3269 ,'-1'
3270 ,l_assignment_id_tbl(i)
3271 ,-1
3272 ,l_group_id
3273 ,l_submitter_user_name
3274 ,'APPROVER'
3275 ,1
3276 ,l_asgn_approval_status
3277 );
3278 END IF;
3279
3280 -------------------------------------------------------
3281 --Process the resource notification for this assignment
3282 -------------------------------------------------------
3283 PA_ASGMT_WFSTD.process_res_fyi_notification
3284 ( p_project_id => p_project_id
3285 ,p_mode => p_mode
3286 ,p_assignment_id => l_assignment_id_tbl(i)
3287 ,p_project_name => l_projects_rec.name
3288 ,p_project_number => l_projects_rec.segment1
3289 ,p_project_manager => l_project_manager_name
3290 ,p_project_org => l_projects_rec.organization_name
3291 ,p_project_cus => l_customer_name
3292 ,p_conflict_group_id => p_conflict_group_id
3293 ,x_return_status => l_return_status
3294 ,x_msg_count => l_msg_count
3295 ,x_msg_data => l_msg_data);
3296
3297 ELSE --l_assignment_status = SUBMITTED
3298
3299 log_message('Submit action for loop' || i );
3300 log_message('Assignment Id: ' || l_assignment_id_tbl(i) );
3301
3302 OPEN get_rec_num ( l_assignment_id_tbl(i) );
3303 FETCH get_rec_num INTO p_record_version_number;
3304 CLOSE get_rec_num;
3305
3306 log_message('Record version number: ' || p_record_version_number );
3307
3308 PA_ASSIGNMENT_APPROVAL_PVT.Update_Approval_Status
3309 ( p_assignment_id => l_assignment_id_tbl(i)
3310 ,p_action_code => PA_ASSIGNMENT_APPROVAL_PUB.g_submit_action
3311 ,p_note_to_approver => p_note_to_all_approvers
3312 ,p_record_version_number => p_record_version_number
3313 ,x_apprvl_status_code => l_apprvl_status_code
3314 ,x_change_id => l_change_id
3315 ,x_record_version_number => l_record_version_number
3316 ,x_return_status => l_return_status
3317 ,x_msg_count => l_msg_count
3318 ,x_msg_data => l_msg_data);
3319
3320 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3321 l_return_status := FND_API.G_MISS_CHAR;
3322 log_message('Error in Update approval status 2');
3323 RAISE PROCESS_ASSIGNMENT_EXCEPTION;
3324 END IF;
3325
3326 ---------------------------------------------------------------------------
3327 --If no errors for this assignment then :
3328 -- i. call PA_CLIENT_EXTN_ASGMT_WF.Generate_Assignment_Approvers(..) to get
3329 -- any client defined Approvers.
3330 -- ii. insert into PA_WF_NTF_PERFORMERS (insert Approvers and assignment)
3331 --Note: All the inserted records have a unique identifier Group_id
3332 -- We can recognize all the records in PA_WF_NTF_PERFORMERS
3333 --for a given transaction using this identifier.
3334 ---------------------------------------------------------------------------
3335
3336 --Construct the in list of approvers
3337 FOR j IN 1..2 LOOP
3338 IF j = 1 AND l_approver1_id_tbl(i) IS NOT NULL THEN
3339 l_approvers_list_rec.Person_id := l_approver1_id_tbl(i);
3340
3341 --Get the approver User name (FND_USER)
3342 wf_directory.getusername
3343 (p_orig_system => 'PER'
3344 ,p_orig_system_id => l_approver1_id_tbl(i)
3345 ,p_name => l_approver_name
3346 ,p_display_name => l_display_name);
3347
3348 l_approvers_list_rec.User_Name := l_approver_name;
3349 l_approver_name := null; --reset
3350
3351 l_approvers_list_rec.Routing_Order := j;
3352 l_approvers_list_tbl(j) := l_approvers_list_rec;
3353
3354 END IF;
3355
3356 IF j = 2 AND l_approver2_id_tbl(i) IS NOT NULL THEN
3357 l_approvers_list_rec.Person_id := l_approver2_id_tbl(i);
3358
3359 --Get the approver User name (FND_USER)
3360 wf_directory.getusername
3361 (p_orig_system => 'PER'
3362 ,p_orig_system_id => l_approver2_id_tbl(i)
3363 ,p_name => l_approver_name
3364 ,p_display_name => l_display_name);
3365
3366 l_approvers_list_rec.User_Name := l_approver_name;
3367 l_approver_name := null; --reset
3368
3369 l_approvers_list_rec.Routing_Order := j;
3370 l_approvers_list_tbl(j) := l_approvers_list_rec;
3371
3372 END IF;
3373
3374 END LOOP;--end j loop
3375
3376 log_message('In Count:' || l_approvers_list_tbl.COUNT);
3377
3378 PA_CLIENT_EXTN_ASGMT_WF.Generate_Assignment_Approvers
3379 (p_assignment_id => l_assignment_id_tbl(i)
3380 ,p_project_id => p_project_id
3381 ,p_in_list_of_approvers => l_approvers_list_tbl
3382 ,x_out_list_of_approvers => l_out_approvers_list_tbl
3383 ,x_number_of_approvers => l_number_of_approvers );
3384
3385 log_message('OUT Count:' || l_out_approvers_list_tbl.COUNT);
3386
3387 -------------------------------------------------------
3388 --Insert All approvers into PA_WF_NTF_PERFORMERS Table
3389 -------------------------------------------------------
3390 l_routing_order := 0;
3391 FOR k in 1..l_out_approvers_list_tbl.COUNT LOOP
3392
3393 IF l_out_approvers_list_tbl(k).user_name is not null THEN
3394
3395 INSERT INTO pa_wf_ntf_performers(
3396 WF_TYPE_CODE
3397 ,ITEM_TYPE
3398 ,ITEM_KEY
3399 ,OBJECT_ID1
3400 ,OBJECT_ID2
3401 ,GROUP_ID
3402 ,USER_NAME
3403 ,USER_TYPE
3404 ,ROUTING_ORDER
3405 ,APPROVAL_STATUS)
3406 VALUES ('MASS_ASSIGNMENT_APPROVAL'
3407 ,'-1'
3408 ,'-1'
3409 ,l_assignment_id_tbl(i)
3410 ,-1
3411 ,l_group_id
3412 ,l_out_approvers_list_tbl(k).user_name
3413 ,'APPROVER'
3414 ,l_routing_order + 1
3415 ,l_asgn_approval_status
3416 );
3417 END IF;
3418
3419 l_routing_order := l_routing_order + 1;
3420
3421 END LOOP;--end k loop
3422
3423 --Set pending approval flag for assignment record in pa_project_assignments
3424 PA_ASGMT_WFSTD.Maintain_wf_pending_flag
3425 (p_assignment_id => l_assignment_id_tbl(i),
3426 p_mode => 'PENDING_APPROVAL') ;
3427
3428 END IF;--end l_assignment_status = SUBMITTED
3429
3430 log_message('Completed Loop ' || i );
3431
3432 END IF; --assignment id null check
3433
3434 EXCEPTION
3435 WHEN PROCESS_ASSIGNMENT_EXCEPTION THEN
3436
3437 log_message('Exception during assignment processing');
3438
3439 ROLLBACK TO PROCESS_ASSIGNMENT_SUBMISSION;
3440
3441 log_message('Assignment id:' || l_assignment_id_tbl(i));
3442
3443 PA_MESSAGE_UTILS.save_messages
3444 (p_user_id => p_submitter_user_id,
3445 p_source_type1 => PA_MASS_ASGMT_TRX.G_SOURCE_TYPE1,
3446 p_source_type2 => 'MASS_APPROVAL',
3447 p_source_identifier1 => 'PAWFAAP',
3448 p_source_identifier2 => l_group_id,
3449 p_context1 => p_project_id,
3450 p_context2 => l_assignment_id_tbl(i),
3451 p_context3 => l_resource_id,
3452 p_commit => FND_API.G_FALSE,
3453 x_return_status => l_return_status);
3454
3455 --Setting pending approval flag in pa_project_assignments
3456 PA_ASGMT_WFSTD.Maintain_wf_pending_flag
3457 (p_assignment_id => l_assignment_id_tbl(i),
3458 p_mode => 'APPROVAL_PROCESS_COMPLETED') ;
3459
3460 ---------------------------
3461 --Set the mass wf flag
3462 ---------------------------
3463 UPDATE pa_project_assignments
3464 SET mass_wf_in_progress_flag = 'N'
3465 WHERE assignment_id = l_assignment_id_tbl(i);
3466
3467 l_assignment_id_tbl(i) := NULL;
3468 WHEN OTHERS THEN
3469 RAISE;
3470 END;
3471
3472 END LOOP; --end i loop for all assignments for this mass transaction
3473
3474 log_message('After populating pa_wf_ntf_performers');
3475
3476 -----------------------------------------------------------------------------------------
3477 /*FOR each distinct approver one in PA_WF_NTF_PERFORMERS belonging
3478 to mass transaction group l_group_id
3479 update the approver_group_id in PA_WF_NTF_PERFORMERS for these assignments with a
3480 new sequence value. Store this value in l_approver_group_id
3481 The l_approver_group_id is the approver transaction id which is used
3482 in grouping and sending approval required notifications for the next (second) set of approvers.
3483 Approver group id is used in grouping the approvers in the next routing order
3484 */
3485 -----------------------------------------------------------------------------------------
3486 FOR rec IN distinct_approvers LOOP
3487
3488 SELECT PA_WF_NTF_PERFORMERS_S.nextval
3489 INTO l_approver_group_id
3490 FROM dual;
3491
3492 UPDATE pa_wf_ntf_performers
3493 SET approver_group_id = l_approver_group_id
3494 WHERE group_id = l_group_id
3495 AND user_name = rec.user_name
3496 AND routing_order = 1;
3497
3498 ----------------------------------------------------------
3499 --Call API to start one workflow for each grouped approver
3500 ----------------------------------------------------------
3501
3502 log_message('Before Calling workflow for group_id, approver_group_id:' || l_group_id||','||l_approver_group_id);
3503
3504 PA_ASGMT_WFSTD.start_mass_approval_flow
3505 (p_project_id => p_project_id
3506 ,p_mode => p_mode
3507 ,p_note_to_approvers => p_note_to_all_approvers
3508 ,p_forwarded_from => null
3509 ,p_performer_user_name => rec.user_name
3510 ,p_routing_order => 1
3511 ,p_group_id => l_group_id
3512 ,p_approver_group_id => l_approver_group_id
3513 ,p_update_info_doc => p_update_info_doc
3514 ,p_project_name => l_projects_rec.name
3515 ,p_project_number => l_projects_rec.segment1
3516 ,p_project_manager => l_project_manager_name
3517 ,p_project_org => l_projects_rec.organization_name
3518 ,p_project_cus => l_customer_name
3519 ,p_submitter_user_name => l_submitter_user_name
3520 ,p_conflict_group_id => p_conflict_group_id
3521 ,x_return_status => l_return_status
3522 ,x_msg_count => l_msg_count
3523 ,x_msg_data => l_msg_data);
3524
3525 l_error_flag := 'N';
3526
3527 log_message('After Calling workflow for group_id, approver_group_id:' || l_group_id||','||l_approver_group_id);
3528
3529 END LOOP;--end loop distinct approvers
3530
3531 -------------------------------------------------------
3532 --Getting number of approved and rejected assignments
3533 -------------------------------------------------------
3534 BEGIN
3535
3536 SELECT count(*)
3537 INTO l_num_apr_asgns
3538 FROM pa_wf_ntf_performers ntf,
3539 pa_project_assignments asgn
3540 WHERE ntf.group_id = l_group_id
3541 AND ntf.routing_order = 1
3542 AND ntf.object_id1 = asgn.assignment_id
3543 AND asgn.apprvl_status_code = PA_ASSIGNMENT_APPROVAL_PUB.g_approved ;
3544
3545 EXCEPTION
3546 WHEN NO_DATA_FOUND THEN
3547 null;
3548 WHEN OTHERS THEN
3549 RAISE;
3550 END;
3551
3552 BEGIN
3553
3554 SELECT count(*)
3555 INTO l_num_rej_asgns
3556 FROM pa_wf_ntf_performers ntf,
3557 pa_project_assignments asgn
3558 WHERE ntf.group_id = l_group_id
3559 AND ntf.routing_order = 1
3560 AND ntf.object_id1 = asgn.assignment_id
3561 AND asgn.apprvl_status_code = PA_ASSIGNMENT_APPROVAL_PUB.g_rejected ;
3562
3563 EXCEPTION
3564 WHEN NO_DATA_FOUND THEN
3565 null;
3566 WHEN OTHERS THEN
3567 RAISE;
3568 END;
3569
3570 ---------------------------------------
3571 --Getting number of errored assignments
3572 --------------------------------------
3573 BEGIN
3574 SELECT count( distinct ( attribute2))
3575 INTO l_error_count
3576 FROM PA_REPORTING_EXCEPTIONS
3577 WHERE context = PA_MASS_ASGMT_TRX.G_SOURCE_TYPE1
3578 AND sub_context = 'MASS_APPROVAL'
3579 AND source_identifier1 = 'PAWFAAP'
3580 AND source_identifier2 = l_group_id;
3581 EXCEPTION
3582 WHEN NO_DATA_FOUND THEN
3583 l_error_count := 0;
3584 WHEN OTHERS THEN
3585 RAISE;
3586 END;
3587
3588 -------------------------------------------------------
3589 --If No assignment was sumbitted (l_error_flag = Y) but
3590 --some assignments were auto approved then the
3591 --Managers must get notifications
3592 -------------------------------------------------------
3593 IF l_error_flag = 'Y' AND (l_num_apr_asgns > 0) THEN
3594
3595 --Start Manager Notifications
3596 log_message('Calling mgr fyi notification');
3597
3598 BEGIN
3599
3600 --Get all assignments in this mass transaction
3601 --which have been approved
3602 SELECT ntf.object_id1
3603 BULK COLLECT INTO l_appr_asgmt_id_tbl
3604 FROM pa_wf_ntf_performers ntf,
3605 pa_project_assignments asgn
3606 WHERE ntf.group_id = l_group_id
3607 AND ntf.routing_order = 1
3608 AND ntf.object_id1 = asgn.assignment_id
3609 AND asgn.apprvl_status_code = PA_ASSIGNMENT_APPROVAL_PUB.g_approved ;
3610
3611 EXCEPTION
3612 WHEN NO_DATA_FOUND THEN
3613 null;
3614 END;
3615
3616 PA_ASGMT_WFSTD.process_mgr_fyi_notification
3617 ( p_assignment_id_tbl => l_appr_asgmt_id_tbl
3618 ,p_project_id => p_project_id
3619 ,p_mode => p_mode
3620 ,p_group_id => l_group_id
3621 ,p_update_info_doc => p_update_info_doc
3622 ,p_num_apr_asgns => l_num_apr_asgns
3623 ,p_num_rej_asgns => l_num_rej_asgns
3624 ,p_project_name => l_projects_rec.name
3625 ,p_project_number => l_projects_rec.segment1
3626 ,p_project_manager => l_project_manager_name
3627 ,p_project_org => l_projects_rec.organization_name
3628 ,p_project_cus => l_customer_name
3629 ,p_submitter_user_name => l_submitter_user_name
3630 ,p_conflict_group_id => p_conflict_group_id
3631 ,x_return_status => l_return_status
3632 ,x_msg_count => l_msg_count
3633 ,x_msg_data => l_msg_data);
3634
3635 log_message('Calling overcom_post_aprvl_processing');
3636
3637 ------------------------------------------------------------------
3638 --This API is called to send notifications to conflicting managers
3639 ------------------------------------------------------------------
3640 PA_SCHEDULE_PVT.overcom_post_aprvl_processing
3641 ( p_conflict_group_id => p_conflict_group_id
3642 ,p_fnd_user_name => l_submitter_user_name
3643 ,x_return_status => l_return_status
3644 ,x_msg_count => l_msg_count
3645 ,x_msg_data => l_msg_data);
3646
3647 END IF;
3648
3649 -----------------------------------------------------------------------------------
3650 --The error flag by default is Y. It is changed to N if any assignment is submitted
3651 --in which case notification to submitter will be handled in mass approve flow
3652 -----------------------------------------------------------------------------------
3653 IF l_error_count > 0 AND l_error_flag = 'Y' THEN
3654 l_error_flag := 'Y';
3655 END IF;
3656
3657 log_message('Count of failed assignments:' || l_error_count);
3658
3659 -----------------------------------------------------------------------
3660 --The codebelow processes the submitter notifcations when all assignments
3661 --fail submission or
3662 --if there are erorrs and some auto-approved assignments but none
3663 --was submitted. In other words if the mass approval transaction completes
3664 --and there were errors after the submit process the FYI error notification
3665 --is sent to submitter
3666 -----------------------------------------------------------------------
3667 IF l_error_flag = 'Y' THEN --Submission failed for all assignments.
3668
3669 log_message('Submission Failed');
3670
3671 log_message('Calling submitter notification');
3672
3673 PA_ASGMT_WFSTD.process_submitter_notification
3674 (p_project_id => p_project_id
3675 ,p_mode => p_mode
3676 ,p_group_id => l_group_id
3677 ,p_update_info_doc => p_update_info_doc
3678 ,p_num_apr_asgns => l_num_apr_asgns
3679 ,p_num_rej_asgns => l_num_rej_asgns
3680 ,p_project_name => l_projects_rec.name
3681 ,p_project_number => l_projects_rec.segment1
3682 ,p_project_manager => l_project_manager_name
3683 ,p_project_org => l_projects_rec.organization_name
3684 ,p_project_cus => l_customer_name
3685 ,p_submitter_user_name => l_submitter_user_name
3686 ,p_assignment_id => p_assignment_id_tbl(1)
3687 ,x_return_status => l_return_status
3688 ,x_msg_count => l_msg_count
3689 ,x_msg_data => l_msg_data);
3690
3691 END IF;
3692
3693 -- IF the number of messages is 1 then fetch the message code from
3694 -- the stack and return its text
3695 x_msg_count := FND_MSG_PUB.Count_Msg;
3696
3697 IF x_msg_count = 1 THEN
3698 pa_interface_utils_pub.get_messages
3699 ( p_encoded => FND_API.G_TRUE
3700 ,p_msg_index => 1
3701 ,p_data => x_msg_data
3702 ,p_msg_index_out => l_msg_index_out );
3703 END IF;
3704
3705 -- Reset the error stack when returning to the calling program
3706 PA_DEBUG.Reset_Err_Stack;
3707
3708 -- If g_error_exists is TRUE then set the x_return_status to 'E'
3709 IF FND_MSG_PUB.Count_Msg >0 THEN
3710 x_return_status := FND_API.G_RET_STS_ERROR;
3711 END IF;
3712
3713 EXCEPTION
3714 WHEN NO_WORKFLOW_EXCEPTION THEN
3715 --TODO: Check
3716 x_return_status := FND_API.G_RET_STS_SUCCESS;
3717
3718 WHEN INVALID_STATUS THEN
3719
3720 IF p_commit = FND_API.G_TRUE THEN
3721 ROLLBACK TO MASS_ASGN_APPRVL;
3722 END IF;
3723
3724 -- Set the excetption Message and the stack
3725 FND_MSG_PUB.add_exc_msg
3726 ( p_pkg_name => 'PA_ASSIGNMENT_APPROVAL_PUB.mass_assignment_approval'
3727 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
3728
3729 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3730 RAISE;
3731
3732 WHEN OTHERS THEN
3733
3734 IF p_commit = FND_API.G_TRUE THEN
3735 ROLLBACK TO MASS_ASGN_APPRVL;
3736 END IF;
3737
3738 -- Set the excetption Message and the stack
3739 FND_MSG_PUB.add_exc_msg
3740 ( p_pkg_name => 'PA_ASSIGNMENT_APPROVAL_PUB.mass_assignment_approval'
3741 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
3742
3743 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3744 RAISE;
3745 END mass_assignment_approval;
3746
3747 ---------------------------------------------------------
3748 --This API is called from the mass approve page
3749 --It starts a worflow to process the approval result
3750 --as a deferred activity
3751 --Workflow Itemtype: PARMAAP
3752 --Workflow process : 'PA_MASS_PROCESS_APRVL_RESULT'
3753 ---------------------------------------------------------
3754 PROCEDURE mass_process_approval_result
3755 ( p_project_id IN pa_project_assignments.project_id%TYPE := FND_API.G_MISS_NUM
3756 ,p_mode IN VARCHAR2
3757 ,p_assignment_id_tbl IN SYSTEM.pa_num_tbl_type := prm_empty_num_tbl
3758 ,p_approval_status_tbl IN SYSTEM.pa_varchar2_30_tbl_type := prm_empty_varchar2_30_tbl
3759 ,p_group_id IN NUMBER
3760 ,p_approver_group_id IN NUMBER
3761 ,p_routing_order IN NUMBER
3762 ,p_item_key IN NUMBER
3763 ,p_notification_id IN NUMBER
3764 ,p_submitter_user_name IN VARCHAR2
3765 ,p_conflict_group_id IN NUMBER := FND_API.G_MISS_NUM
3766 ,p_api_version IN NUMBER := 1.0
3767 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
3768 ,p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM
3769 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
3770 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
3771 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3772 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3773 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3774 )
3775 IS
3776
3777 l_itemkey VARCHAR2(30);
3778 l_responsibility_id NUMBER;
3779 l_resp_appl_id NUMBER;
3780 l_wf_started_date DATE;
3781 l_wf_started_by_id NUMBER;
3782 l_return_status VARCHAR2(1);
3783 l_error_message_code VARCHAR2(30);
3784 l_save_threshold NUMBER;
3785 l_msg_count NUMBER ;
3786 l_msg_index_out NUMBER ;
3787 l_msg_data VARCHAR2(2000);
3788 l_wf_item_type VARCHAR2(2000):= 'PARMAAP'; --Assignment Approval Item type
3789 l_wf_process VARCHAR2(2000):= 'PA_MASS_PROCESS_APRVL_RESULT'; --Mass Assignment Approval process
3790 l_err_code NUMBER := 0;
3791 l_err_stage VARCHAR2(2000);
3792 l_err_stack VARCHAR2(2000);
3793 l_text_attr_name_tbl Wf_Engine.NameTabTyp;
3794 l_text_attr_value_tbl Wf_Engine.TextTabTyp;
3795 l_num_attr_name_tbl Wf_Engine.NameTabTyp;
3796 l_num_attr_value_tbl Wf_Engine.NumTabTyp;
3797 l_update_info_doc VARCHAR2(32767);
3798 l_note_to_approvers VARCHAR2(2000);
3799 l_forwarded_from fnd_user.user_name%TYPE; /* Commented for bug 3261755 VARCHAR2(30); */
3800
3801 BEGIN
3802
3803 -- Initialize the Error Stack
3804 PA_DEBUG.init_err_stack('PA_ASSIGNMENT_APPROVAL_PUB.mass_process_approval_result');
3805
3806 log_message('Inside mass_process_approval_result');
3807
3808 --Log Message
3809 IF P_DEBUG_MODE = 'Y' THEN
3810 PA_DEBUG.write_log
3811 ( x_module => 'pa.plsql.PA_ASSIGNMENT_APPROVAL_PUB.mass_process_approval_result.begin'
3812 ,x_msg => 'Beginning of mass_assignment_approval'
3813 ,x_log_level => 1);
3814 END IF;
3815
3816 -- Initialize the return status to success
3817 x_return_status := FND_API.G_RET_STS_SUCCESS;
3818
3819 --Clear the global PL/SQL message table
3820 FND_MSG_PUB.initialize;
3821
3822 -- Issue API savepoint if the transaction is to be committed
3823 IF p_commit = FND_API.G_TRUE THEN
3824 SAVEPOINT MASS_APPRVL_RESULT;
3825 END IF;
3826
3827 --------------------------------------------------------
3828 --Update object_id2 column to 100 for these assignments
3829 --in pa_wf_ntf_performers table to differentiate
3830 --between assignments for which user has aproved/rejected
3831 --and those for which notification is pending
3832 ---------------------------------------------------------
3833 UPDATE pa_wf_ntf_performers
3834 SET object_id2 = 100
3835 where group_id = p_group_id
3836 and routing_order = p_routing_order
3837 and approver_group_id = p_approver_group_id;
3838
3839 -----------------------------------------------
3840 -- Create the unique item key to launch WF with
3841 -----------------------------------------------
3842 SELECT pa_prm_wf_item_key_s.nextval
3843 INTO l_itemkey
3844 FROM dual;
3845
3846 l_wf_started_by_id := FND_GLOBAL.user_id;
3847 l_responsibility_id := FND_GLOBAL.resp_id;
3848 l_resp_appl_id := FND_GLOBAL.resp_appl_id;
3849
3850 FND_GLOBAL.Apps_Initialize ( user_id => l_wf_started_by_id
3851 , resp_id => l_responsibility_id
3852 , resp_appl_id => l_resp_appl_id );
3853
3854 -- Setting thresold value to run the process in background
3855 l_save_threshold := wf_engine.threshold;
3856 wf_engine.threshold := -1;
3857
3858 -- Create the WF process
3859 wf_engine.CreateProcess
3860 ( ItemType => l_wf_item_type
3861 , ItemKey => l_itemkey
3862 , process => l_wf_process );
3863
3864
3865 ------------------------------------------------------------
3866 --Creating the attribute arrays from assignment id table and
3867 --Status table
3868 ------------------------------------------------------------
3869 IF p_assignment_id_tbl.COUNT > 0 THEN
3870
3871 log_message('Creating assignment attributes');
3872
3873 FOR i IN p_assignment_id_tbl.FIRST .. p_assignment_id_tbl.LAST LOOP
3874
3875 l_num_attr_name_tbl( l_num_attr_name_tbl.COUNT+1 ) := 'ASSIGNMENT_' || i;
3876 l_num_attr_value_tbl( l_num_attr_value_tbl.COUNT+1 ):= p_assignment_id_tbl(i);
3877
3878 END LOOP;
3879
3880 END IF;
3881
3882 IF p_approval_status_tbl.COUNT > 0 THEN
3883
3884 log_message('Creating status attributes');
3885
3886 FOR i IN p_approval_status_tbl.FIRST .. p_approval_status_tbl.LAST LOOP
3887
3888 l_text_attr_name_tbl( l_text_attr_name_tbl.COUNT+1 ) := 'STATUS_' || i;
3889 l_text_attr_value_tbl( l_text_attr_value_tbl.COUNT+1 ):= p_approval_status_tbl(i);
3890
3891 END LOOP;
3892
3893 END IF;
3894
3895 -----------------------------------------------------------------
3896 --Set all the required workflow attributes and start the workflow
3897 -----------------------------------------------------------------
3898 wf_engine.SetItemAttrNumber
3899 ( itemtype => l_wf_item_type
3900 , itemkey => l_itemkey
3901 , aname => 'PROJECT_ID'
3902 , avalue => p_project_id );
3903
3904 wf_engine.SetItemAttrText
3905 ( itemtype => l_wf_item_type
3906 , itemkey => l_itemkey
3907 , aname => 'MODE'
3908 , avalue => p_mode );
3909
3910 --Setting Assignment Ids
3911 wf_engine.AddItemAttrNumberArray
3912 ( itemtype => l_wf_item_type
3913 , itemkey => l_itemkey
3914 , aname => l_num_attr_name_tbl
3915 , avalue => l_num_attr_value_tbl );
3916
3917 --Setting Status table
3918 wf_engine.AddItemAttrTextArray
3919 ( itemtype => l_wf_item_type
3920 , itemkey => l_itemkey
3921 , aname => l_text_attr_name_tbl
3922 , avalue => l_text_attr_value_tbl );
3923
3924 wf_engine.SetItemAttrNumber
3925 ( itemtype => l_wf_item_type
3926 , itemkey => l_itemkey
3927 , aname => 'GROUP_ID'
3928 , avalue => p_group_id );
3929
3930 wf_engine.SetItemAttrNumber
3931 ( itemtype => l_wf_item_type
3932 , itemkey => l_itemkey
3933 , aname => 'APPROVER_GROUP_ID'
3934 , avalue => p_approver_group_id );
3935
3936 wf_engine.SetItemAttrNumber
3937 ( itemtype => l_wf_item_type
3938 , itemkey => l_itemkey
3939 , aname => 'ROUTING_ORDER'
3940 , avalue => p_routing_order );
3941
3942 wf_engine.SetItemAttrNumber
3943 ( itemtype => l_wf_item_type
3944 , itemkey => l_itemkey
3945 , aname => 'NUMBER_OF_ASSIGNMENTS'
3946 , avalue => p_assignment_id_tbl.COUNT );
3947
3948 --Get and set the Update info document
3949 l_update_info_doc := wf_engine.getItemAttrDocument
3950 ( itemtype => 'PAWFAAP'
3951 , itemkey => p_item_key
3952 , aname => 'UPDATED_INFO_DOC' );
3953
3954 wf_engine.SetItemAttrDocument
3955 ( itemtype => l_wf_item_type
3956 , itemkey => l_itemkey
3957 , aname => 'UPDATED_INFO_DOC'
3958 , documentid => l_update_info_doc );
3959
3960 wf_engine.SetItemAttrText
3961 ( itemtype => l_wf_item_type
3962 , itemkey => l_itemkey
3963 , aname => 'SUBMITTER_UNAME'
3964 , avalue => p_submitter_user_name );
3965
3966 wf_engine.SetItemAttrNumber
3967 ( itemtype => l_wf_item_type
3968 , itemkey => l_itemkey
3969 , aname => 'CONFLICT_GROUP_ID'
3970 , avalue => p_conflict_group_id );
3971
3972 --Set the previous approver (forwarded from and note to approvers)
3973 l_forwarded_from := wf_engine.getItemAttrText
3974 ( itemtype => 'PAWFAAP'
3975 , itemkey => p_item_key
3976 , aname => 'NTFY_APPRVL_RECIPIENT_NAME');
3977
3978 l_note_to_approvers := wf_engine.getItemAttrText
3979 ( itemtype => 'PAWFAAP'
3980 , itemkey => p_item_key
3981 , aname => 'NOTE_TO_APPROVER');
3982
3983 wf_engine.SetItemAttrText
3984 ( itemtype => l_wf_item_type
3985 , itemkey => l_itemkey
3986 , aname => 'NTFY_APPRVL_RECIPIENT_NAME'
3987 , avalue => l_forwarded_from );
3988
3989 wf_engine.SetItemAttrText
3990 ( itemtype => l_wf_item_type
3991 , itemkey => l_itemkey
3992 , aname => 'NOTE_TO_APPROVER'
3993 , avalue => l_note_to_approvers );
3994
3995 -------------------------------
3996 --Start the workflow process
3997 -------------------------------
3998 wf_engine.StartProcess ( itemtype => l_wf_item_type
3999 ,itemkey => l_itemkey );
4000
4001 PA_WORKFLOW_UTILS.Insert_WF_Processes
4002 (p_wf_type_code => 'MASS_ASSIGNMENT_APPROVAL'
4003 ,p_item_type => l_wf_item_type
4004 ,p_item_key => l_itemkey
4005 ,p_entity_key1 => to_char(p_project_id)
4006 ,p_entity_key2 => to_char(p_group_id)
4007 ,p_description => NULL
4008 ,p_err_code => l_err_code
4009 ,p_err_stage => l_err_stage
4010 ,p_err_stack => l_err_stack );
4011
4012 --Setting the original value
4013 wf_engine.threshold := l_save_threshold;
4014
4015 log_message('Exiting mass_process_approval_result');
4016
4017 EXCEPTION
4018 WHEN OTHERS THEN
4019
4020 IF p_commit = FND_API.G_TRUE THEN
4021 ROLLBACK TO MASS_APPRVL_RESULT;
4022 END IF;
4023
4024 --Setting the original value
4025 wf_engine.threshold := l_save_threshold;
4026
4027 -- Set the excetption Message and the stack
4028 FND_MSG_PUB.add_exc_msg
4029 ( p_pkg_name => 'PA_ASSIGNMENT_APPROVAL_PUB.mass_process_approval_result'
4030 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
4031
4032 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4033 RAISE;
4034 END mass_process_approval_result;
4035
4036 ----------------------------------------------------
4037 --This procedure validates the approvers name and id
4038 --It is called in mass_assignment_approval API
4039 ----------------------------------------------------
4040 /* Added a new parameter p_overriding_authority_flag for the bug 3213509*/
4041 PROCEDURE validate_approver_name_id
4042 ( p_project_id IN NUMBER
4043 ,p_assignment_id_tbl IN SYSTEM.pa_num_tbl_type := prm_empty_num_tbl
4044 ,p_approver1_id_tbl IN SYSTEM.pa_num_tbl_type := prm_empty_num_tbl
4045 ,p_approver1_name_tbl IN SYSTEM.pa_varchar2_240_tbl_type := prm_empty_varchar2_240_tbl
4046 ,p_approver2_id_tbl IN SYSTEM.pa_num_tbl_type := prm_empty_num_tbl
4047 ,p_approver2_name_tbl IN SYSTEM.pa_varchar2_240_tbl_type := prm_empty_varchar2_240_tbl
4048 ,p_submitter_user_id IN NUMBER
4049 ,p_group_id IN NUMBER
4050 ,p_api_version IN NUMBER := 1.0
4051 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
4052 ,p_max_msg_count IN NUMBER := FND_API.G_MISS_NUM
4053 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
4054 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
4055 ,p_overriding_authority_flag IN VARCHAR2 := 'N'
4056 ,x_assignment_id_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type
4057 ,x_approver1_id_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type
4058 ,x_approver2_id_tbl OUT NOCOPY SYSTEM.pa_num_tbl_type
4059 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
4060 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
4061 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
4062 )
4063 IS
4064
4065 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
4066 l_error_message_code fnd_new_messages.message_name%TYPE;
4067 l_assignment_start_date DATE;
4068 l_approver1_person_id NUMBER;
4069 l_approver1_person_name VARCHAR2(200);
4070 l_approver2_person_id NUMBER;
4071 l_approver2_person_name VARCHAR2(200);
4072 l_resource_type_id NUMBER;--Used in Name ID validation
4073 l_msg_index_out NUMBER;
4074 l_resource_id NUMBER;
4075
4076 BEGIN
4077
4078 -- Initialize the Error Stack
4079 PA_DEBUG.init_err_stack('PA_ASSIGNMENT_APPROVAL_PUB.validate_approver_name_id');
4080
4081 --Log Message
4082 IF P_DEBUG_MODE = 'Y' THEN
4083 PA_DEBUG.write_log
4084 ( x_module => 'pa.plsql.PA_ASSIGNMENT_APPROVAL_PUB.validate_approver_name_id.begin'
4085 ,x_msg => 'Beginning of approver name id validation'
4086 ,x_log_level => 1);
4087 END IF;
4088
4089 -- Initialize the return status to success
4090 x_return_status := FND_API.G_RET_STS_SUCCESS;
4091
4092 --Clear the global PL/SQL message table
4093 FND_MSG_PUB.initialize;
4094
4095 -- Issue API savepoint if the transaction is to be committed
4096 IF p_commit = FND_API.G_TRUE THEN
4097 SAVEPOINT MASS_APPRVL_VALIDATE_NAMEID;
4098 END IF;
4099
4100 log_message('Group id:' || p_group_id);
4101 log_message('Value of submitter user id:' || p_submitter_user_id);
4102
4103 x_approver1_id_tbl := prm_empty_num_tbl;
4104 x_approver2_id_tbl := prm_empty_num_tbl;
4105 x_assignment_id_tbl := prm_empty_num_tbl;
4106
4107 x_approver1_id_tbl.EXTEND( p_assignment_id_tbl.COUNT);
4108 x_approver2_id_tbl.EXTEND( p_assignment_id_tbl.COUNT);
4109 x_assignment_id_tbl.EXTEND( p_assignment_id_tbl.COUNT);
4110
4111 ---------------------------------------------------------
4112 --Name to ID validation for approvers of each assignment
4113 --The above must be done for both the approvers
4114 ---------------------------------------------------------
4115 IF p_assignment_id_tbl.COUNT > 0 THEN
4116
4117
4118
4119 FOR i IN p_assignment_id_tbl.FIRST .. p_assignment_id_tbl.LAST LOOP
4120
4121 log_message('Loop:' || i);
4122
4123 --Initialize locals to null before every loop
4124 l_approver1_person_id := null;
4125 l_approver1_person_name := null;
4126 l_approver2_person_id := null;
4127 l_approver2_person_name := null;
4128
4129 --Get the assignment start date
4130 SELECT start_date
4131 INTO l_assignment_start_date
4132 FROM pa_project_assignments
4133 WHERE assignment_id = p_assignment_id_tbl(i);
4134
4135 IF p_approver1_id_tbl.EXISTS(i) THEN
4136 IF p_approver1_id_tbl(i) <> -999 THEN
4137 l_approver1_person_id := p_approver1_id_tbl(i);
4138 log_message('Person_id1:' || l_approver1_person_id);
4139 END IF;
4140 END IF;
4141
4142 IF p_approver1_name_tbl.EXISTS(i) THEN
4143 l_approver1_person_name := p_approver1_name_tbl(i);
4144 log_message('Person_Name1:' || l_approver1_person_name);
4145 END IF;
4146
4147 IF p_approver2_id_tbl.EXISTS(i) THEN
4148
4149 IF p_approver2_id_tbl(i) <> -999 THEN
4150 l_approver2_person_id := p_approver2_id_tbl(i);
4151 log_message('Person_id2:' || l_approver2_person_id);
4152 END IF;
4153
4154 END IF;
4155
4156 IF p_approver2_name_tbl.EXISTS(i) THEN
4157 l_approver2_person_name := p_approver2_name_tbl(i);
4158 log_message('Person_Name2:' || l_approver2_person_name);
4159 END IF;
4160
4161 -----------------------------------------------------------------
4162 --If there are no approvers for this assignment then signal error
4163 -----------------------------------------------------------------\
4164 /* Added the check for the bug 3213509*/
4165 IF p_overriding_authority_flag = 'N' then
4166 IF ( l_approver1_person_id IS NULL AND l_approver1_person_name IS NULL
4167 AND l_approver2_person_id IS NULL
4168 AND l_approver2_person_name IS NULL )
4169 THEN
4170 log_message('No Approvers for Loop:' || i);
4171
4172 l_error_message_code := 'PA_RESOURCE_NO_APPROVAL';
4173 PA_UTILS.Add_Message( p_app_short_name => 'PA'
4174 ,p_msg_name => l_error_message_code);
4175
4176 END IF;
4177 END IF;
4178 ---------------------
4179 --Validate approvers
4180 ---------------------
4181 IF ( l_approver1_person_id IS NOT NULL OR
4182 l_approver1_person_name IS NOT NULL )
4183 THEN
4184
4185 log_message('Before calling check_resourcename_or_id for Approver1 Loop:' || i);
4186
4187 PA_RESOURCE_UTILS.Check_ResourceName_OR_ID (
4188 p_resource_id => l_approver1_person_id
4189 ,p_resource_name => l_approver1_person_name
4190 ,p_check_id_flag => PA_STARTUP.G_Check_ID_Flag
4191 ,p_date => l_assignment_start_date
4192 ,x_resource_id => x_approver1_id_tbl (i)
4193 ,x_resource_type_id => l_resource_type_id
4194 ,x_return_status => l_return_status
4195 ,x_error_message_code => l_error_message_code );
4196
4197 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4198 PA_UTILS.Add_Message( p_app_short_name => 'PA'
4199 ,p_msg_name => l_error_message_code);
4200 END IF;
4201
4202 log_message('After calling check_resourcename_or_id for Approver1 Loop:' || i);
4203 log_message('Person_id:' || x_approver1_id_tbl (i));
4204 log_message('Return Status:' || l_return_status);
4205 log_message('Error:' || l_error_message_code);
4206
4207 --x_approver1_id_tbl (i) := l_approver1_person_id;
4208
4209 END IF;
4210
4211 IF ( l_approver2_person_id IS NOT NULL OR
4212 l_approver2_person_name IS NOT NULL )
4213 THEN
4214
4215 log_message('Before calling check_resourcename_or_id for Approver2 Loop:' || i);
4216 log_message('Check Id flag: ' || PA_STARTUP.G_Check_ID_Flag);
4217
4218 PA_RESOURCE_UTILS.Check_ResourceName_OR_ID (
4219 p_resource_id => l_approver2_person_id
4220 ,p_resource_name => l_approver2_person_name
4221 ,p_check_id_flag => PA_STARTUP.G_Check_ID_Flag
4222 ,p_date => l_assignment_start_date
4223 ,x_resource_id => x_approver2_id_tbl (i)
4224 ,x_resource_type_id => l_resource_type_id
4225 ,x_return_status => l_return_status
4226 ,x_error_message_code => l_error_message_code);
4227
4228 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4229 PA_UTILS.Add_Message( p_app_short_name => 'PA'
4230 ,p_msg_name => l_error_message_code);
4231 END IF;
4232
4233 log_message('After calling check_resourcename_or_id for Approver2 Loop:' || i);
4234 log_message('Person_id:' || x_approver2_id_tbl (i));
4235 log_message('Person Name:' || l_approver2_person_name);
4236 log_message('Return Status:' || l_return_status);
4237 log_message('Error:' || l_error_message_code);
4238
4239 --x_approver2_id_tbl (i) := l_approver2_person_id;
4240
4241 END IF;
4242
4243 log_message('COUNT of message stack:' || FND_MSG_PUB.Count_Msg);
4244
4245 -------------------------------------------
4246 --Populate x_success_assignment_id_tbl
4247 -------------------------------------------
4248 IF FND_MSG_PUB.Count_Msg > 0 THEN
4249
4250 --Setting pending approval flag in pa_project_assignments
4251 PA_ASGMT_WFSTD.Maintain_wf_pending_flag
4252 (p_assignment_id => p_assignment_id_tbl(i),
4253 p_mode => 'APPROVAL_PROCESS_COMPLETED') ;
4254
4255 ---------------------------
4256 --Set the mass wf flag
4257 ---------------------------
4258 UPDATE pa_project_assignments
4259 SET mass_wf_in_progress_flag = 'N'
4260 WHERE assignment_id = p_assignment_id_tbl(i);
4261
4262 x_assignment_id_tbl (i) := null;
4263 ELSE
4264 x_assignment_id_tbl (i) := p_assignment_id_tbl (i);
4265 END IF;
4266
4267 IF FND_MSG_PUB.Count_Msg > 0 THEN
4268
4269 SELECT resource_id
4270 INTO l_resource_id
4271 FROM pa_project_assignments
4272 WHERE assignment_id = p_assignment_id_tbl(i);
4273
4274 log_message('Value of submitter user id:' || p_submitter_user_id);
4275
4276 PA_MESSAGE_UTILS.save_messages
4277 (p_user_id => p_submitter_user_id,
4278 p_source_type1 => PA_MASS_ASGMT_TRX.G_SOURCE_TYPE1,
4279 p_source_type2 => 'MASS_APPROVAL',
4280 p_source_identifier1 => 'PAWFAAP',
4281 p_source_identifier2 => p_group_id,
4282 p_context1 => p_project_id,
4283 p_context2 => p_assignment_id_tbl(i),
4284 p_context3 => l_resource_id,
4285 p_commit => FND_API.G_FALSE,
4286 x_return_status => l_return_status);
4287
4288 END IF;
4289
4290 END LOOP;--end i loop
4291
4292 END IF; --end name validations for all assignments
4293
4294 EXCEPTION
4295 WHEN OTHERS THEN
4296
4297 IF p_commit = FND_API.G_TRUE THEN
4298 ROLLBACK TO MASS_APPRVL_VALIDATE_NAMEID;
4299 END IF;
4300
4301 -- Set the excetption Message and the stack
4302 FND_MSG_PUB.add_exc_msg
4303 ( p_pkg_name => 'PA_ASSIGNMENT_APPROVAL_PUB.validate_approver_name_id'
4304 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
4305
4306 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4307 RAISE;
4308 END validate_approver_name_id;
4309
4310
4311 END PA_ASSIGNMENT_APPROVAL_PUB;