1 PACKAGE BODY PA_ADVERTISEMENTS_PUB AS
2 --$Header: PARAVPBB.pls 120.1 2005/08/19 16:48:46 mwasowic noship $
3 --
4
5
6 ----------------------------------------------------------------------
7 -- Procedure
8 -- Validate Advertisement Action Line
9 --
10 -- Purpose
11 -- This API is currently empty.
12 -- Validate a single action line of an advertisement action set
13 -- template or an advertisement action set on a requirement.
14 ----------------------------------------------------------------------
15 P_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N'); /* Added Debug Profile Option variable initialization for bug#2674619 */
16
17 PROCEDURE Validate_Action_Set_Line (
18 p_action_set_type_code IN pa_action_sets.action_set_type_code%TYPE
19 , p_action_set_line_rec IN pa_action_set_lines%ROWTYPE
20 , p_action_line_conditions_tbl IN pa_action_set_utils.action_line_cond_tbl_type
21 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
22 ) IS
23
24 BEGIN
25
26 NULL;
27
28 END Validate_Action_Set_Line;
29
30 ----------------------------------------------------------------------
31 -- Procedure
32 -- Process Advertisement Action Set
33 --
34 -- Purpose
35 -- Re-order the action lines and validate the advertisement
36 -- action set or advertisement action lines on a requirement.
37 -- Invoked when a new action set is created, an existing action
38 -- set or action lines on the requirement are updated, or an action
39 -- set is started on a requirement.
40 ----------------------------------------------------------------------
41 PROCEDURE Process_Action_Set (
42 p_action_set_type_code IN pa_action_sets.action_set_type_code%TYPE
43 , p_action_set_id IN NUMBER
44 , p_action_set_template_flag IN pa_action_sets.action_set_template_flag%TYPE :=NULL
45 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
46 ) IS
47
48 TYPE varchar_tbl IS TABLE OF VARCHAR2(30)
49 INDEX BY BINARY_INTEGER;
50 TYPE number_tbl IS TABLE OF NUMBER
51 INDEX BY BINARY_INTEGER;
52 TYPE date_tbl IS TABLE OF DATE
53 INDEX BY BINARY_INTEGER;
54
55 l_start_date pa_project_assignments.start_date%TYPE := NULL;
56 l_adv_action_set_status_code pa_action_sets.status_code%TYPE := NULL;
57 l_adv_action_set_start_date pa_action_sets.actual_start_date%TYPE := NULL;
58 l_pub_to_org_line_id pa_action_set_lines.action_set_line_id%TYPE := NULL;
59 l_action_code_tbl varchar_tbl;
60 l_condition_code_tbl varchar_tbl;
61 l_condition_attribute1_tbl varchar_tbl;
62 l_condition_attribute2_tbl varchar_tbl;
63 l_condition_date_tbl date_tbl;
64 l_action_set_line_id_tbl number_tbl;
65 l_action_status_code_tbl varchar_tbl;
66 l_action_attribute1_tbl varchar_tbl;
67 l_action_attribute3_tbl varchar_tbl;
68 l_action_attribute4_tbl varchar_tbl;
69 l_action_set_line_number_tbl number_tbl;
70 i NUMBER;
71 l_need_validation VARCHAR2(1) := 'F';
72 l_undeleted_action_code_tbl varchar_tbl;
73
74 l_return_status VARCHAR2(1);
75 l_msg_data fnd_new_messages.message_name%TYPE;
76 l_msg_count NUMBER;
77 l_msg_index_out NUMBER;
78
79 --cursor to get the related details of the requirement
80 CURSOR get_req_action_set_info IS
81 SELECT pa.start_date,
82 ast.status_code,
83 ast.actual_start_date
84 FROM pa_project_assignments pa,
85 pa_action_sets ast
86 WHERE pa.assignment_id = ast.object_id
87 AND ast.action_set_id = p_action_set_id;
88
89 --cursor to get pending and complete action lines
90 CURSOR get_action_lines IS
91 SELECT action_code
92 FROM pa_action_set_lines
93 WHERE action_set_id = p_action_set_id
94 AND (status_code = 'PENDING'
95 OR status_code = 'UPDATE_PENDING'
96 OR status_code = 'COMPLETE');
97
98 --cursor to get pending and complete action lines
99 CURSOR get_undeleted_action_lines IS
100 SELECT action_code
101 FROM pa_action_set_lines
102 WHERE action_set_id = p_action_set_id
103 AND line_deleted_flag = 'N';
104
105 --cursor to check if validation is needed
106 CURSOR check_esc_to_next_lvl_exists IS
107 SELECT 'T'
108 FROM pa_action_set_lines
109 WHERE action_set_id = p_action_set_id
110 AND action_code = 'ADVERTISEMENT_ESC_TO_NEXT_LVL'
111 AND (status_code = 'PENDING'
112 OR status_code = 'UPDATE_PENDING'
113 OR status_code = 'COMPLETE')
114 AND rownum=1;
115
116 --cursor to check if a Publish to Organizations action line exist before
117 --the first Escalate to next level action line for
118 --requirement's action set
119 CURSOR check_pub_to_org_exists IS
120 SELECT action_set_line_id
121 FROM pa_action_set_lines
122 WHERE action_set_id = p_action_set_id
123 AND rownum = 1
124 AND action_code = 'ADVERTISEMENT_PUB_TO_START_ORG'
125 AND (status_code = 'PENDING'
126 OR status_code = 'UPDATE_PENDING'
127 OR status_code = 'COMPLETE')
128 AND action_set_line_number < (
129 SELECT MIN(action_set_line_number)
130 FROM pa_action_set_lines
131 WHERE action_code = 'ADVERTISEMENT_ESC_TO_NEXT_LVL'
132 AND action_set_id = p_action_set_id
133 AND (status_code = 'PENDING'
134 OR status_code = 'UPDATE_PENDING'
135 OR status_code = 'COMPLETE'));
136
137 --cursor to check if a Publish to Organizations action line exist before
138 --the first Escalate to next level action line for action set template
139 CURSOR check_pub_to_org_in_template IS
140 SELECT action_set_line_id
141 FROM pa_action_set_lines
142 WHERE action_set_id = p_action_set_id
143 AND rownum = 1
144 AND action_code = 'ADVERTISEMENT_PUB_TO_START_ORG'
145 AND action_set_line_number < (
146 SELECT MIN(action_set_line_number)
147 FROM pa_action_set_lines
148 WHERE action_code = 'ADVERTISEMENT_ESC_TO_NEXT_LVL'
149 AND action_set_id = p_action_set_id);
150
151 BEGIN
152
153 --dbms_output.put_line('PA_ADVERTISEMENTS_PUB.Process_Action_Set');
154
155 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
156 PA_DEBUG.WRITE_LOG(x_Module => 'pa.plsql.PA_ADVERTISEMENTS_PUB.Prepare_Adv_Action_Set.begin',x_Msg => 'in PA_ADVERTISEMENTS_PUB.Prepare_Adv_Action_Set', x_Log_Level => 6);
157 END IF;
158
159 --initialize the return status.
160 x_return_status := FND_API.G_RET_STS_SUCCESS;
161
162 -- Initialise the error stack
163 PA_DEBUG.init_err_stack('PA_ADVERTISEMENTS_PUB.Prepare_Adv_Action_Set');
164
165 --get the related details of the requirement if
166 --validating the action set of the requirement
167 IF p_action_set_template_flag='N' OR p_action_set_template_flag IS NULL THEN
168 OPEN get_req_action_set_info;
169 FETCH get_req_action_set_info INTO
170 l_start_date,
171 l_adv_action_set_status_code,
172 l_adv_action_set_start_date;
173 CLOSE get_req_action_set_info;
174 END IF;
175
176 --dbms_output.put_line('calling PA_ADVERTISEMENTS_PVT.Order_Adv_Action_Lines');
177
178 --
179 -- order the action lines
180 --
181 PA_ADVERTISEMENTS_PVT.Order_Adv_Action_Lines (
182 p_action_set_id => p_action_set_id
183 , p_action_set_template_flag => p_action_set_template_flag
184 , p_object_start_date => l_start_date
185 , p_action_set_status_code => l_adv_action_set_status_code
186 , p_action_set_actual_start_date => l_adv_action_set_start_date
187 , x_return_status => l_return_status
188 );
189
190 --dbms_output.put_line('Action Line has been ordered');
191
192 --
193 -- validate the action lines
194 --
195 -- get all Pending, Change Pending and Performed action lines
196 OPEN get_action_lines;
197 FETCH get_action_lines BULK COLLECT INTO l_action_code_tbl;
198 CLOSE get_action_lines;
199
200 -- get all undeleted action lines
201 OPEN get_undeleted_action_lines;
202 FETCH get_undeleted_action_lines BULK COLLECT INTO l_undeleted_action_code_tbl;
203 CLOSE get_undeleted_action_lines;
204
205
206 -- CHECK 1: if there is no such action line, returns error
207 IF l_undeleted_action_code_tbl.COUNT=0 THEN
208
209 PA_UTILS.Add_Message ( p_app_short_name => 'PA'
210 ,p_msg_name => 'PA_NO_ACTION_LINE');
211
212 -- CHECK 2: if Escalate to Next Level is the only action line, returns error
213 ELSIF l_action_code_tbl.COUNT = 1 AND
214 l_action_code_tbl(1)='ADVERTISEMENT_ESC_TO_NEXT_LVL' THEN
215
216 PA_UTILS.Add_Message ( p_app_short_name => 'PA'
217 ,p_msg_name => 'PA_ADV_ESC_NEXT_LVL_ERR');
218
219 -- CHECK 3: if there are more than 1 action lines,
220 -- further validation is needed
221 ELSIF l_action_code_tbl.COUNT > 1 THEN
222
223 --dbms_output.put_line('More than 1 action lines');
224
225 -- CHECK 4: need further validation only if Escalate to Next Level
226 -- action line exists
227 OPEN check_esc_to_next_lvl_exists;
228 FETCH check_esc_to_next_lvl_exists INTO l_need_validation;
229 CLOSE check_esc_to_next_lvl_exists;
230
231 IF l_need_validation = 'T' THEN
232
233 --dbms_output.put_line('Need validation');
234
235 -- CHECK 5: if the advertisement action set has
236 -- been started on the requirement
237 IF l_adv_action_set_status_code <> 'NOT_STARTED' AND
238 l_adv_action_set_status_code <> 'CLOSED' AND
239 l_adv_action_set_start_date IS NOT NULL THEN
240
241 -- dbms_output.put_line('Validate action set on Requirement');
242
243 -- CHECK 5.1:check if a Publish to Organizations action line exist before
244 -- the first Escalate to next level action line
245 OPEN check_pub_to_org_exists;
246 FETCH check_pub_to_org_exists INTO l_pub_to_org_line_id;
247 CLOSE check_pub_to_org_exists;
248
249 IF l_pub_to_org_line_id IS NULL THEN
250
251 PA_UTILS.Add_Message ( p_app_short_name => 'PA'
252 ,p_msg_name => 'PA_ADV_ESC_NEXT_LVL_ERR');
253 END IF;
254
255 -- CHECK 6: if this is template action set,
256 -- or action set has not been started on the requirement
257 ELSE
258
259 --dbms_output.put_line('Validate template action set');
260
261 -- Get the condition codes and condition parameters of action
262 -- lines with action codes equal Publish to Organizations or
263 -- Escalate to next level order by line number
264 SELECT aslc.condition_code, aslc.condition_attribute1, aslc.condition_attribute2
265 BULK COLLECT INTO l_condition_code_tbl, l_condition_attribute1_tbl, l_condition_attribute2_tbl
266 FROM pa_action_set_lines asl,
267 pa_action_set_line_cond aslc
268 WHERE asl.action_set_id = p_action_set_id
269 AND asl.action_set_line_id = aslc.action_set_line_id
270 AND (asl.action_code = 'ADVERTISEMENT_ESC_TO_NEXT_LVL'
271 OR asl.action_code = 'ADVERTISEMENT_PUB_TO_START_ORG')
272 AND (asl.status_code = 'PENDING'
273 OR asl.status_code = 'UPDATE_PENDING'
274 OR asl.status_code = 'COMPLETE')
275 ORDER BY asl.action_set_line_number;
276
277 FOR i in l_condition_code_tbl.FIRST+1 ..l_condition_code_tbl.LAST LOOP
278
279 -- CHECK 6.1: If these lines use different condition code,
280 -- returns error
281 IF l_condition_code_tbl(i) <> l_condition_code_tbl(i-1) THEN
282
283 PA_UTILS.Add_Message ( p_app_short_name => 'PA'
284 ,p_msg_name => 'PA_ADV_DIFF_COND_ERR');
285 EXIT; -- exit the loop
286 END IF;
287
288 -- CHECK 6.2: If the condition code is Days Open or Remaining and
289 -- the Number of Days Remaining is not in descending order
290 -- then returns error
291 IF l_condition_code_tbl(i) = 'ADVERTISEMENT_DAYS_OPN_REMAIN' AND
292 to_number(l_condition_attribute2_tbl(i)) > to_number(l_condition_attribute2_tbl(i-1)) THEN
293
294 PA_UTILS.Add_Message ( p_app_short_name => 'PA'
295 ,p_msg_name => 'PA_ADV_COND_ATT_ERR');
296 EXIT; -- exit the loop
297
298 END IF;
299
300 END LOOP;
301
302 -- CHECK 6.3: check if a Publish to Organizations action line
303 -- exist before the first Escalate to next level action line
304 OPEN check_pub_to_org_in_template;
305 FETCH check_pub_to_org_in_template INTO l_pub_to_org_line_id;
306 CLOSE check_pub_to_org_in_template;
307
308 IF l_pub_to_org_line_id IS NULL THEN
309
310 PA_UTILS.Add_Message ( p_app_short_name => 'PA'
311 ,p_msg_name => 'PA_ADV_ESC_NEXT_LVL_ERR');
312 END IF;
313
314 END IF; -- if it is requirement and advertisement has been started
315 END IF; -- need validation
316 END IF; -- more than 1 action line
317
318 -- Reset the error stack when returning to the calling program
319 PA_DEBUG.Reset_Err_Stack;
320
321 -- If there are any messages in the stack then set x_return_status
322
323 IF FND_MSG_PUB.Count_Msg > 0 THEN
324
325 x_return_status := FND_API.G_RET_STS_ERROR;
326
327 END IF;
328
329 -- Put any message text from message stack into the Message ARRAY
330 EXCEPTION
331 WHEN OTHERS THEN
332
333 -- Set the excetption Message and the stack
334 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_ADVERTISEMENTS_PUB.Prepare_Adv_Action_Set'
335 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
336 --
337 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
338 RAISE; -- This is optional depending on the needs
339
340 END Process_Action_Set;
341
342
343 ----------------------------------------------------------------------
344 -- Procedure
345 -- Perform Advertisement Action Set Line
346 --
347 -- Purpose
348 -- Invoked by the generic perform action set API to perform an action
349 -- line in the advertisement action set on an object.
350 ----------------------------------------------------------------------
351 PROCEDURE Perform_Action_Set_Line (
352 p_action_set_type_code IN pa_action_sets.action_set_type_code%TYPE
353 , p_action_set_details_rec IN pa_action_sets%ROWTYPE
354 , p_action_set_line_rec IN pa_action_set_lines%ROWTYPE
355 , p_action_line_conditions_tbl IN pa_action_set_utils.action_line_cond_tbl_type
356 , x_action_line_audit_tbl OUT NOCOPY pa_action_set_utils.insert_audit_lines_tbl_type -- For 1159 mandate changes bug#2674619
357 , x_action_line_result_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
358 ) IS
359
360 TYPE varchar_tbl IS TABLE OF VARCHAR2(30)
361 INDEX BY BINARY_INTEGER;
362 TYPE number_tbl IS TABLE OF NUMBER
363 INDEX BY BINARY_INTEGER;
364 TYPE date_tbl IS TABLE OF DATE
365 INDEX BY BINARY_INTEGER;
366
367 l_msg_index_out NUMBER;
368 l_object_id pa_project_assignments.assignment_id%TYPE;
369 l_project_id pa_project_assignments.project_id%TYPE;
370 l_start_date pa_project_assignments.start_date%TYPE;
371 l_record_version_number pa_project_assignments.record_version_number%TYPE;
372 l_adv_action_set_status_code pa_action_sets.status_code%TYPE;
373 l_adv_action_set_start_date pa_action_sets.actual_start_date%TYPE;
374 l_return_status VARCHAR2(1);
375
376 --cursor to get the related details of the requirement
377 CURSOR get_req_action_set_info IS
378 SELECT pa.assignment_id,
379 pa.project_id,
380 pa.start_date,
381 pa.record_version_number
382 FROM pa_project_assignments pa,
383 pa_action_sets ast
384 WHERE pa.assignment_id = ast.object_id
385 AND ast.action_set_id = p_action_set_line_rec.action_set_id;
386
387 BEGIN
388
389 --dbms_output.put_line('begin of PA_ADVERTISEMENTS_PUB.Perform_Adv_Action_Set');
390
391 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
392 PA_DEBUG.WRITE_LOG(x_Module => 'pa.plsql.PA_ADVERTISEMENTS_PUB.Perform_Adv_Action_Set.begin',x_Msg => 'in PA_ADVERTISEMENTS_PUB.Perform_Adv_Action_Set ', x_Log_Level => 6);
393 END IF;
394
395 -- Initialise the error stack
396 PA_DEBUG.init_err_stack('PA_ADVERTISEMENTS_PUB.Perform_Adv_Action_Set');
397
398 -- Initialize audit record table
399 g_action_line_audit_tbl.DELETE;
400
401 --get the related details of the requirement
402 OPEN get_req_action_set_info;
403 FETCH get_req_action_set_info INTO
404 l_object_id,
405 l_project_id,
406 l_start_date,
407 l_record_version_number;
408 CLOSE get_req_action_set_info;
409
410 --
411 -- Process the action if if the condition of the action line is met
412 -- or if the line status is UPDATE_PENDING
413 --
414 IF p_action_line_conditions_tbl(p_action_line_conditions_tbl.COUNT).condition_date <= SYSDATE OR p_action_set_line_rec.status_code = 'UPDATE_PENDING' THEN
415
416 -- CASE 1: Publish to All
417 IF p_action_set_line_rec.action_code = 'ADVERTISEMENT_PUB_TO_ALL' THEN
418
419 PA_ADVERTISEMENTS_PVT.Publish_To_all(
420 p_action_set_line_id => p_action_set_line_rec.action_set_line_id
421 , p_object_id => l_object_id
422 , p_action_code => p_action_set_line_rec.action_code
423 , p_action_status_code => p_action_set_line_rec.status_code
424 , x_return_status => l_return_status
425 );
426
427 -- CASE 2: Publish to Starting Organization
428 ELSIF p_action_set_line_rec.action_code = 'ADVERTISEMENT_PUB_TO_START_ORG' THEN
429
430 PA_ADVERTISEMENTS_PVT.Publish_To_Organizations(
431 p_action_set_line_id => p_action_set_line_rec.action_set_line_id
432 , p_object_id => l_object_id
433 , p_action_code => p_action_set_line_rec.action_code
434 , p_action_status_code => p_action_set_line_rec.status_code
435 , p_org_hierarchy_version_id => to_number(p_action_set_line_rec.action_attribute1)
436 , p_starting_organization_id => to_number(p_action_set_line_rec.action_attribute2)
437 , x_return_status => l_return_status
438 );
439
440 -- CASE 3: Publish to Staffing Manager
441 ELSIF p_action_set_line_rec.action_code = 'ADVERTISEMENT_PUB_TO_SM' THEN
442
443 PA_ADVERTISEMENTS_PVT.Publish_To_Staffing_Managers(
444 p_action_set_line_id => p_action_set_line_rec.action_set_line_id
445 , p_object_id => l_object_id
446 , p_action_code => p_action_set_line_rec.action_code
447 , p_action_status_code => p_action_set_line_rec.status_code
448 , p_organization_id => to_number(p_action_set_line_rec.action_attribute1)
449 , x_return_status => l_return_status
450 );
451
452 -- CASE 4: Escalate to Next Level
453 ELSIF p_action_set_line_rec.action_code = 'ADVERTISEMENT_ESC_TO_NEXT_LVL' THEN
454
455 PA_ADVERTISEMENTS_PVT.Escalate_to_Next_Level(
456 p_action_set_line_id => p_action_set_line_rec.action_set_line_id
457 , p_action_set_line_number => p_action_set_line_rec.action_set_line_number
458 , p_action_set_id => p_action_set_line_rec.action_set_id
459 , p_action_set_line_rec_ver_num => p_action_set_line_rec.record_version_number
460 , p_action_set_line_cond_tbl => p_action_line_conditions_tbl
461 , p_object_id => l_object_id
462 , p_action_code => p_action_set_line_rec.action_code
463 , p_action_status_code => p_action_set_line_rec.status_code
464 , x_return_status => l_return_status
465 );
466
467 -- CASE 5: Send Email
468 ELSIF p_action_set_line_rec.action_code = 'ADVERTISEMENT_SEND_EMAIL' THEN
469
470 PA_ADVERTISEMENTS_PVT.Send_Email(
471 p_action_set_line_id => p_action_set_line_rec.action_set_line_id
472 , p_object_id => l_object_id
473 , p_action_code => p_action_set_line_rec.action_code
474 , p_action_status_code => p_action_set_line_rec.status_code
475 , p_email_address => p_action_set_line_rec.action_attribute1
476 , p_project_id => l_project_id
477 , x_return_status => l_return_status
478 );
479
480 -- CASE 6: Send Notification to Person
481 ELSIF p_action_set_line_rec.action_code = 'ADVERTISEMENT_SEND_NTF_PERSON' THEN
482
483 PA_ADVERTISEMENTS_PVT.Send_Notification(
484 p_action_set_line_id => p_action_set_line_rec.action_set_line_id
485 , p_object_id => l_object_id
486 , p_action_code => p_action_set_line_rec.action_code
487 , p_action_status_code => p_action_set_line_rec.status_code
488 , p_method => 'PERSON'
489 , p_person_id => to_number(p_action_set_line_rec.action_attribute1)
490 , p_project_id => l_project_id
491 , p_project_role_id => null
492 , x_return_status => l_return_status
493 );
494
495 -- CASE 7: Send Notification to Project Role
496 ELSIF p_action_set_line_rec.action_code = 'ADVERTISEMENT_SEND_NTF_ROLE' THEN
497
498 PA_ADVERTISEMENTS_PVT.Send_Notification(
499 p_action_set_line_id => p_action_set_line_rec.action_set_line_id
500 , p_object_id => l_object_id
501 , p_action_code => p_action_set_line_rec.action_code
502 , p_action_status_code => p_action_set_line_rec.status_code
503 , p_method => 'PROJECT_ROLE'
504 , p_person_id => null
505 , p_project_id => l_project_id
506 , p_project_role_id => to_number(p_action_set_line_rec.action_attribute1)
507 , x_return_status => l_return_status
508
509 );
510
511 -- CASE 8: Update Staffing Priority
512 ELSIF p_action_set_line_rec.action_code = 'ADVERTISEMENT_UPDATE_SP' THEN
513
514 PA_ADVERTISEMENTS_PVT.Update_Staffing_Priority(
515 p_action_set_line_id => p_action_set_line_rec.action_set_line_id
516 , p_object_id => l_object_id
517 , p_action_code => p_action_set_line_rec.action_code
518 , p_action_status_code => p_action_set_line_rec.status_code
519 , p_staffing_priority_code => p_action_set_line_rec.action_attribute1
520 , p_record_version_number => l_record_version_number
521 , x_return_status => l_return_status
522 );
523
524 -- CASE 9: Remove Advertisement
525 ELSIF p_action_set_line_rec.action_code = 'ADVERTISEMENT_REMOVE_ADV' THEN
526
527 PA_ADVERTISEMENTS_PVT.Remove_Advertisement(
528 p_action_set_line_id => p_action_set_line_rec.action_set_line_id
529 , p_object_id => l_object_id
530 , p_action_code => p_action_set_line_rec.action_code
531 , p_action_status_code => p_action_set_line_rec.status_code
532 , p_project_id => l_project_id
533 , x_return_status => l_return_status
534 );
535
536 END IF;
537
538 --
539 -- Set the Result Code to be returned to generic API
540 --
541 IF p_action_set_line_rec.status_code = 'PENDING' THEN
542
543 x_action_line_result_code := pa_action_set_utils.G_PERFORMED_COMPLETE;
544
545 ELSIF p_action_set_line_rec.status_code = 'REVERSE_PENDING' THEN
546
547 IF p_action_set_line_rec.action_code = 'ADVERTISEMENT_REMOVE_ADV' OR
548 p_action_set_line_rec.action_code = 'ADVERTISEMENT_UPDATE_SP' THEN
549 x_action_line_result_code := pa_action_set_utils.G_REVERSED_CUSTOM_AUDIT;
550 ELSIF (p_action_set_line_rec.action_code = 'ADVERTISEMENT_SEND_EMAIL' OR
551 p_action_set_line_rec.action_code = 'ADVERTISEMENT_SEND_NTF_PERSON' OR
552 p_action_set_line_rec.action_code = 'ADVERTISEMENT_SEND_NTF_ROLE')
553 AND l_return_status = FND_API.G_RET_STS_ERROR THEN
554 x_action_line_result_code := pa_action_set_utils.G_REVERSED_CUSTOM_AUDIT;
555 ELSE
556 x_action_line_result_code := pa_action_set_utils.G_REVERSED_DEFAULT_AUDIT;
557 END IF;
558
559 ELSE -- update pending
560
561 IF p_action_set_line_rec.action_code = 'ADVERTISEMENT_REMOVE_ADV' OR
562 p_action_set_line_rec.action_code = 'ADVERTISEMENT_UPDATE_SP' THEN
563 x_action_line_result_code := pa_action_set_utils.G_UPDATED_CUSTOM_AUDIT;
564 ELSIF (p_action_set_line_rec.action_code = 'ADVERTISEMENT_SEND_EMAIL' OR
565 p_action_set_line_rec.action_code = 'ADVERTISEMENT_SEND_NTF_PERSON' OR
566 p_action_set_line_rec.action_code = 'ADVERTISEMENT_SEND_NTF_ROLE')
567 AND l_return_status = FND_API.G_RET_STS_ERROR THEN
568 x_action_line_result_code := pa_action_set_utils.G_UPDATED_CUSTOM_AUDIT;
569 ELSE
570 x_action_line_result_code := pa_action_set_utils.G_UPDATED_DEFAULT_AUDIT;
571 END IF;
572
573 END IF; -- action line status
574
575 ELSE -- condition met
576 x_action_line_result_code := pa_action_set_utils.G_NOT_PERFORMED;
577 END IF;
578
579 x_action_line_audit_tbl := g_action_line_audit_tbl;
580
581 --dbms_output.put_line('result code = '||x_action_line_result_code);
582
583 -- Reset the error stack when returning to the calling program
584 PA_DEBUG.Reset_Err_Stack;
585
586 EXCEPTION
587 WHEN OTHERS THEN
588
589 -- Set the excetption Message and the stack
590 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_ADVERTISEMENTS_PUB.Perform_Adv_Action_Set'
591 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
592
593 x_action_line_result_code := pa_action_set_utils.G_NOT_PERFORMED;
594 RAISE; -- This is optional depending on the needs
595
596 END Perform_Action_Set_Line;
597
598
599 ----------------------------------------------------------------------
600 -- Procedure
601 -- Reevaluate Advertisement Action Set
602 --
603 -- Purpose
604 -- Re-evaluate the advertisement action lines on the requirement by
605 -- updating the statuses of the action lines based on the
606 -- condition and the new requirement start date.
607 ----------------------------------------------------------------------
608 PROCEDURE Reevaluate_Adv_Action_Set (
609 p_object_id IN pa_action_sets.object_id%TYPE
610 , p_object_type IN pa_action_sets.object_type%TYPE
611 , p_new_object_start_date IN DATE
612 , p_validate_only IN VARCHAR2 := FND_API.G_TRUE
613 , p_api_version IN NUMBER := 1.0
614 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
615 , p_commit IN VARCHAR2 := FND_API.G_FALSE
616 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
617 ) IS
618
619 l_return_status VARCHAR2(1);
620 l_msg_count NUMBER;
621 l_msg_data VARCHAR2(2000);
622 l_project_id pa_project_assignments.project_id%TYPE;
623 l_start_date pa_project_assignments.start_date%TYPE;
624 l_adv_action_set_status_code pa_action_sets.status_code%TYPE;
625 l_adv_action_set_start_date pa_action_sets.actual_start_date%TYPE;
626 l_action_set_id pa_action_sets.action_set_id%TYPE;
627 l_action_set_line_id_tbl pa_action_set_utils.number_tbl_type;
628 l_action_status_code_tbl pa_action_set_utils.varchar_tbl_type;
629 l_condition_date_tbl pa_action_set_utils.date_tbl_type;
630 l_action_line_cond_id_tbl pa_action_set_utils.number_tbl_type;
631 l_action_set_template_flag VARCHAR2(1);
632 l_msg_index_out NUMBER;
633
634 --cursor to get the related details of the requirement
635 CURSOR get_req_action_set_info IS
636 SELECT pa.project_id,
637 pa.start_date,
638 ast.status_code,
639 ast.actual_start_date,
640 ast.action_set_id,
641 ast.action_set_template_flag
642 FROM pa_project_assignments pa,
643 pa_action_sets ast
644 WHERE pa.assignment_id = p_object_id
645 AND ast.object_id = p_object_id
646 AND ast.object_type = p_object_type
647 AND ast.action_set_type_code = 'ADVERTISEMENT'
648 AND ast.status_code <> 'DELETED';
649
650 BEGIN
651
652 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
653 PA_DEBUG.WRITE_LOG(x_Module => 'pa.plsql.PA_ADVERTISEMENTS_PUB.Reevaluate_Adv_Action_Set.begin',x_Msg => 'in PA_ADVERTISEMENTS_PUB.Reevaluate_Adv_Action_Set', x_Log_Level => 6);
654 END IF;
655
656 --initialize the return status.
657 x_return_status := FND_API.G_RET_STS_SUCCESS;
658
659 -- Initialise the error stack
660 PA_DEBUG.init_err_stack('PA_ADVERTISEMENTS_PUB.Reevaluate_Adv_Action_Set');
661
662 --get the related details of the requirement
663 OPEN get_req_action_set_info;
664 FETCH get_req_action_set_info INTO
665 l_project_id,
666 l_start_date,
667 l_adv_action_set_status_code,
668 l_adv_action_set_start_date,
669 l_action_set_id,
670 l_action_set_template_flag;
671 CLOSE get_req_action_set_info;
672
673 --get the action lines on the requirement if the advertisement
674 --action set has started and is not closed
675 IF (l_adv_action_set_status_code <> 'NOT_STARTED'
676 AND l_adv_action_set_status_code <> 'CLOSED')
677 AND l_adv_action_set_start_date IS NOT NULL THEN
678
679 -- re-order the action lines and generate the new condition dates
680 PA_ADVERTISEMENTS_PVT.Order_Adv_Action_Lines (
681 p_action_set_id => l_action_set_id
682 , p_action_set_template_flag => l_action_Set_template_flag
683 , p_object_start_date => p_new_object_start_date
684 , p_action_set_status_code => l_adv_action_set_status_code
685 , p_action_set_actual_start_date => l_adv_action_set_start_date
686 , x_return_status => l_return_status
687 );
688 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
689 x_return_status := FND_API.G_RET_STS_ERROR;
690 END IF;
691
692 -- get all completed action lines whose conditions are no longer met
693 SELECT asl.action_set_line_id, 'UPDATE_PENDING', aslc.condition_date, aslc.action_set_line_condition_id
694 BULK COLLECT INTO l_action_set_line_id_tbl, l_action_status_code_tbl, l_condition_date_tbl, l_action_line_cond_id_tbl
695 FROM pa_action_set_lines asl,
696 pa_action_set_line_cond aslc
697 WHERE asl.action_set_id = l_action_set_id
698 AND asl.status_code = 'COMPLETE'
699 AND asl.action_set_line_id = aslc.action_set_line_id
700 AND aslc.condition_date > SYSDATE;
701
702 -- bulk udpate advertisement action line status and condition date
703 PA_ACTION_SETS_PVT.Bulk_Update_Line_Status(
704 p_action_set_line_id_tbl => l_action_set_line_id_tbl
705 ,p_line_status_tbl => l_action_status_code_tbl
706 ,x_return_status => l_return_status
707 );
708 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
709 x_return_status := FND_API.G_RET_STS_ERROR;
710 END IF;
711
712 PA_ACTION_SETS_PVT.Bulk_Update_Condition_Date(
713 p_action_line_condition_id_tbl => l_action_line_cond_id_tbl
714 ,p_condition_date_tbl => l_condition_date_tbl
715 ,x_return_status => l_return_status
716 );
717 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
718 x_return_status := FND_API.G_RET_STS_ERROR;
719 END IF;
720
721 -- reset the plsql tables
722 l_action_set_line_id_tbl.DELETE;
723 l_action_status_code_tbl.DELETE;
724 l_condition_date_tbl.DELETE;
725 l_action_line_cond_id_tbl.DELETE;
726
727 -- Bug 2450716: PJ.J:B1:AVT: SHIFTING DURATION IS FIRING
728 -- THE WRONG ADVT. ACTION LINE
729 -- Bulk update the line status and condition date for lines that
730 -- were reversed due to Cancel Advertisement and the condition
731 -- is no longer met
732 -- These lines will be in REVERSED status with deleted_flag <> 'Y'
733 SELECT asl.action_set_line_id, 'PENDING', aslc.condition_date, aslc.action_set_line_condition_id
734 BULK COLLECT INTO l_action_set_line_id_tbl, l_action_status_code_tbl, l_condition_date_tbl, l_action_line_cond_id_tbl
735 FROM pa_action_set_lines asl,
736 pa_action_set_line_cond aslc
737 WHERE asl.action_set_id = l_action_set_id
738 AND asl.status_code = 'REVERSED'
739 AND nvl(asl.line_deleted_flag, 'N') = 'N'
740 AND asl.action_set_line_id = aslc.action_set_line_id
741 AND aslc.condition_date > SYSDATE;
742
743 PA_ACTION_SETS_PVT.Bulk_Update_Line_Status(
744 p_action_set_line_id_tbl => l_action_set_line_id_tbl
745 ,p_line_status_tbl => l_action_status_code_tbl
746 ,x_return_status => l_return_status
747 );
748 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
749 x_return_status := FND_API.G_RET_STS_ERROR;
750 END IF;
751
752 PA_ACTION_SETS_PVT.Bulk_Update_Condition_Date(
753 p_action_line_condition_id_tbl => l_action_line_cond_id_tbl
754 ,p_condition_date_tbl => l_condition_date_tbl
755 ,x_return_status => l_return_status
756 );
757 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
758 x_return_status := FND_API.G_RET_STS_ERROR;
759 END IF;
760
761 -- call generic action set API to perform the action set again
762 PA_ACTION_SETS_PUB.Perform_Single_Action_Set(
763 p_action_set_id => l_action_set_id
764 ,p_action_set_type_code => 'ADVERTISEMENT'
765 ,p_object_id => p_object_id
766 ,p_object_type => 'OPEN_ASSIGNMENT'
767 ,p_validate_only => p_validate_only
768 ,p_commit => p_commit
769 ,p_init_msg_list => FND_API.G_FALSE
770 ,x_return_status => l_return_status
771 ,x_msg_count => l_msg_count
772 ,x_msg_data => l_msg_data);
773 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
774 x_return_status := FND_API.G_RET_STS_ERROR;
775 END IF;
776
777 END IF;
778
779 -- Reset the error stack when returning to the calling program
780 PA_DEBUG.Reset_Err_Stack;
781
782 -- If there are any messages in the stack then set x_return_status
783
784 IF FND_MSG_PUB.Count_Msg > 0 THEN
785
786 x_return_status := FND_API.G_RET_STS_ERROR;
787
788 END IF;
789
790 -- Put any message text from message stack into the Message ARRAY
791 EXCEPTION
792 WHEN OTHERS THEN
793
794 -- Set the excetption Message and the stack
795 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_ADVERTISEMENTS_PUB.Reevaluate_Adv_Action_Set'
796 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
797 --
798 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
799 RAISE; -- This is optional depending on the needs
800
801 END Reevaluate_Adv_Action_Set;
802
803
804 ----------------------------------------------------------------------
805 -- Function
806 -- Is Action Set Started On Apply
807 --
808 -- Purpose
809 -- Check if the action set should be started upon application.
810 ----------------------------------------------------------------------
811 FUNCTION Is_Action_Set_Started_On_Apply(
812 p_action_set_type_code IN pa_action_sets.action_set_type_code%TYPE
813 ,p_object_type IN pa_action_sets.object_type%TYPE
814 ,p_object_id IN pa_action_sets.object_id%TYPE
815 ) RETURN VARCHAR2 IS
816
817 l_action_set_start_flag VARCHAR2(1);
818
819 BEGIN
820
821 pa_debug.init_err_stack ('PA_ADVERTISEMENTS_PUB.Is_Action_Set_Started_On_Apply');
822
823
824 IF PA_ADVERTISEMENTS_PUB.g_start_adv_action_set_flag IS NOT NULL THEN
825 l_action_set_start_flag := PA_ADVERTISEMENTS_PUB.g_start_adv_action_set_flag;
826 ELSE
827
828 SELECT proj.start_adv_action_set_flag
829 INTO l_action_set_start_flag
830 FROM pa_project_assignments asgn,
831 pa_projects_all proj
832 WHERE asgn.assignment_id = p_object_id
833 AND asgn.project_id = proj.project_id;
834
835 END IF;
836
837 PA_ADVERTISEMENTS_PUB.g_start_adv_action_set_flag := NULL;
838
839 RETURN l_action_set_start_flag;
840
841 EXCEPTION
842 WHEN NO_DATA_FOUND THEN
843 RETURN 'N';
844 WHEN OTHERS THEN
845 RETURN 'N';
846 END Is_Action_Set_Started_On_Apply;
847
848 END PA_ADVERTISEMENTS_PUB;
[Home] [Help]