[Home] [Help]
PACKAGE BODY: APPS.PA_ADVERTISEMENTS_PVT
Source
1 PACKAGE BODY PA_ADVERTISEMENTS_PVT AS
2 --$Header: PARAVPVB.pls 120.9.12010000.3 2010/03/24 09:42:12 sugupta ship $
3 --
4
5 ----------------------------------------------------------------------
6 -- Procedure
7 -- Order Advertisement Action Lines
8 --
9 -- Purpose
10 -- Order the action lines of an advertisement action set
11 -- or an advertisement action lines on a requirement that have been
12 -- inserted into pa_action_set_lines table.
13 ----------------------------------------------------------------------
14 P_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N'); /* Added Debug Profile Option variable initialization for bug#2674619 */
15
16 PROCEDURE Order_Adv_Action_Lines (
17 p_action_set_id IN pa_action_sets.action_set_id%TYPE
18 , p_action_set_template_flag IN pa_action_sets.action_set_template_flag%TYPE :=NULL
19 , p_object_start_date IN DATE := NULL
20 , p_action_set_status_code IN pa_action_sets.status_code%TYPE := NULL
21 , p_action_set_actual_start_date IN pa_action_sets.actual_start_date%TYPE := NULL
22 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
23 ) IS
24
25
26 TYPE number_tbl IS TABLE OF NUMBER
27 INDEX BY BINARY_INTEGER;
28 TYPE date_tbl IS TABLE OF DATE
29 INDEX BY BINARY_INTEGER;
30 TYPE varchar_tbl IS TABLE OF VARCHAR2(150)
31 INDEX BY BINARY_INTEGER;
32
33 i NUMBER;
34 l_action_line_id_tbl number_tbl;
35 l_action_code_tbl varchar_tbl;
36 l_condition_code_tbl varchar_tbl;
37 l_condition_attribute1_tbl varchar_tbl;
38 l_condition_attribute2_tbl varchar_tbl;
39 l_action_line_id_tbl2 pa_action_set_utils.number_tbl_type;
40 l_condition_date_tbl2 pa_action_set_utils.date_tbl_type;
41 l_action_line_number_tbl2 pa_action_set_utils.number_tbl_type;
42 l_start_date pa_project_assignments.start_date%TYPE;
43 l_adv_action_set_status_code pa_action_sets.status_code%TYPE;
44 l_adv_action_set_start_date pa_action_sets.actual_start_date%TYPE;
45 l_action_line_cond_id_tbl pa_action_set_utils.number_tbl_type;
46 l_action_line_cond_id_tbl2 pa_action_set_utils.number_tbl_type;
47 l_return_status VARCHAR2(1);
48
49 --cursor to get the related details of the requirement
50 CURSOR get_req_action_set_info IS
51 SELECT pa.start_date,
52 ast.status_code,
53 ast.actual_start_date
54 FROM pa_project_assignments pa,
55 pa_action_sets ast
56 WHERE pa.assignment_id = ast.object_id
57 AND ast.action_set_id = p_action_set_id;
58
59 BEGIN
60
61 -- Initialize the Error Stack
62 PA_DEBUG.init_err_stack('PA_ADVERTISEMENTS_PVT.Order_Adv_Action_Lines');
63
64 -- 4537865 : Initiliaze x_return_status
65 x_return_status := FND_API.G_RET_STS_SUCCESS ;
66
67 --Log Message
68 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
69 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ADVERTISEMENTS_PVT.Order_Adv_Action_Lines.begin'
70 ,x_msg => 'Beginning of PA_ADVERTISEMENTS_PVT.Order_Adv_Action_Lines'
71 ,x_log_level => 5);
72 END IF;
73
74 -- Initialize the return status to success
75 l_return_status := FND_API.G_RET_STS_SUCCESS;
76
77 -- Clear the temporary table
78 DELETE FROM pa_adv_action_lines_order_temp;
79
80 --get the related details of the requirement
81 IF (p_action_set_template_flag='N' OR p_action_set_template_flag IS NULL)
82 AND (p_object_start_date IS NULL OR p_action_set_status_code IS NULL OR p_action_set_actual_start_date IS NULL) THEN
83
84 --dbms_output.put_line('Fetch requirement action set details');
85 OPEN get_req_action_set_info;
86 FETCH get_req_action_set_info INTO
87 l_start_date,
88 l_adv_action_set_status_code,
89 l_adv_action_set_start_date;
90 CLOSE get_req_action_set_info;
91 ELSE
92
93 l_start_date := p_object_start_date;
94 l_adv_action_set_status_code := p_action_set_status_code;
95 l_adv_action_set_start_date := p_action_set_actual_start_date;
96 END IF;
97
98 IF (p_action_set_template_flag='N' OR p_action_set_template_flag IS NULL)
99 AND l_adv_action_set_start_date IS NOT NULL THEN
100
101 -- if the object's action set has been started,
102 -- get the current and previous action sets' actions
103 SELECT asl.action_set_line_id,
104 asl.action_code,
105 aslc.condition_code,
106 aslc.condition_attribute1,
107 aslc.condition_attribute2,
108 aslc.action_set_line_condition_id
109 BULK COLLECT INTO l_action_line_id_tbl,
110 l_action_code_tbl,
111 l_condition_code_tbl,
112 l_condition_attribute1_tbl,
113 l_condition_attribute2_tbl,
114 l_action_line_cond_id_tbl
115 FROM pa_action_set_lines asl,
116 pa_action_set_line_cond aslc,
117 pa_action_sets asets,
118 pa_action_sets asets2
119 WHERE asets2.action_set_id = p_action_set_id
120 AND asets.object_id = asets2.object_id
121 AND asets.object_type = 'OPEN_ASSIGNMENT'
122 AND asets.action_set_type_code = 'ADVERTISEMENT'
123 AND asl.action_set_id = asets.action_set_id
124 AND asl.action_set_line_id = aslc.action_set_line_id;
125
126 --dbms_output.put_line('GET ALL number of lines: '||l_action_line_id_tbl.COUNT);
127
128
129 ELSE
130
131 -- if the action set is a template or if the object's
132 -- action set has not been started,
133 -- get only the current action set's action as
134 -- there is either no previous action set or
135 -- the actions on the previous action set are deleted.
136 SELECT asl.action_set_line_id,
137 asl.action_code,
138 aslc.condition_code,
139 aslc.condition_attribute1,
140 aslc.condition_attribute2,
141 aslc.action_set_line_condition_id
142 BULK COLLECT INTO l_action_line_id_tbl,
143 l_action_code_tbl,
144 l_condition_code_tbl,
145 l_condition_attribute1_tbl,
146 l_condition_attribute2_tbl,
147 l_action_line_cond_id_tbl
148 FROM pa_action_set_lines asl,
149 pa_action_set_line_cond aslc
150 WHERE asl.action_set_id = p_action_set_id
151 AND asl.action_set_line_id = aslc.action_set_line_id;
152
153 --dbms_output.put_line('GET CURRENT number of lines: '||l_action_line_id_tbl.COUNT);
154
155
156 END IF;
157
158 IF l_action_line_id_tbl.COUNT > 0 THEN
159
160 -- CASE 1: Order the action lines based on the condition date if:
161 -- 1) the object type is requirement, and
162 -- 2) the action set has been started on the requirement, and
163 -- 3) there are more than 1 action lines
164 IF (p_action_set_template_flag='N' OR p_action_set_template_flag IS NULL)
165 AND l_adv_action_set_start_date IS NOT NULL THEN
166
167 --dbms_output.put_line('Order action set lines for a started requirement action set.');
168
169 -- Derive the condition dates and insert the action lines
170 -- into the temporary table.
171 -- Truncate the condition dates to make sure all date records
172 -- are set to 12 am. This facilates the ordering of special
173 -- actions like 'Remove Advertisement' and 'Escalate to Next Level'.
174 FORALL i IN l_action_line_id_tbl.FIRST .. l_action_line_id_tbl.LAST
175 INSERT INTO pa_adv_action_lines_order_temp (
176 action_set_line_id
177 , action_code
178 , condition_code
179 , condition_attribute1
180 , condition_attribute2
181 , condition_date
182 , action_set_line_condition_id
183 )
184 VALUES (
185 l_action_line_id_tbl(i)
186 , l_action_code_tbl(i)
187 , l_condition_code_tbl(i)
188 , l_condition_attribute1_tbl(i)
189 , l_condition_attribute2_tbl(i)
190 , DECODE(l_condition_code_tbl(i),
191 'ADVERTISEMENT_DAYS_OPEN', TRUNC(l_adv_action_set_start_date+l_condition_attribute1_tbl(i)),
192 'ADVERTISEMENT_DAYS_REMAINING', TRUNC(l_start_date-l_condition_attribute2_tbl(i)),
193 'ADVERTISEMENT_DAYS_OPN_REMAIN', TRUNC(LEAST(l_adv_action_set_start_date+l_condition_attribute1_tbl(i), l_start_date-l_condition_attribute2_tbl(i))))
194 , l_action_line_cond_id_tbl(i)
195 );
196
197 -- Add 0.1 and 0.2 day to the 'Escalate to Next Level' and
198 -- 'Remove Advertisement' action lines respectively to
199 -- make sure these lines are ordered after other action
200 -- lines if their condition dates are the same
201 UPDATE pa_adv_action_lines_order_temp
202 SET condition_date = condition_date+0.1
203 WHERE action_code = 'ADVERTISEMENT_ESC_TO_NEXT_LVL';
204
205 UPDATE pa_adv_action_lines_order_temp
206 SET condition_date = condition_date+0.2
207 WHERE action_code = 'ADVERTISEMENT_REMOVE_ADV';
208
209 -- Bulk collect the sorted action lines into plsql tables
210 SELECT action_set_line_id, TRUNC(condition_date), action_set_line_condition_id
211 BULK COLLECT INTO l_action_line_id_tbl2, l_condition_date_tbl2, l_action_line_cond_id_tbl2
212 FROM pa_adv_action_lines_order_temp
213 ORDER BY condition_date;
214
215 -- CASE 2: Order the action lines by Days Open and then Days
216 -- Remaining if:
217 -- 1) the object type is template or
218 -- the object type is requirement and the action set has not
219 -- been started on the requirement, and
220 -- 2) there are more than 1 action lines
221 ELSE
222
223 --dbms_output.put_line('Order action set lines for a template action set.');
224
225 -- Insert the action lines into the temporary table.
226 FORALL i IN l_action_line_id_tbl.FIRST .. l_action_line_id_tbl.LAST
227 INSERT INTO pa_adv_action_lines_order_temp (
228 action_set_line_id
229 , action_code
230 , condition_code
231 , condition_attribute1
232 , condition_attribute2
233 , condition_date
234 , action_set_line_condition_id
235 )
236 VALUES (
237 l_action_line_id_tbl(i)
238 , l_action_code_tbl(i)
239 , l_condition_code_tbl(i)
240 , l_condition_attribute1_tbl(i)
241 , l_condition_attribute2_tbl(i)
242 , null
243 , l_action_line_cond_id_tbl(i)
244 );
245
246 -- Bulk collect the sorted action lines into plsql tables
247 SELECT action_set_line_id, condition_date, action_set_line_condition_id
248 BULK COLLECT INTO l_action_line_id_tbl2, l_condition_date_tbl2, l_action_line_cond_id_tbl2
249 FROM pa_adv_action_lines_order_temp
250 ORDER BY to_number(condition_attribute1), to_number(condition_attribute2) desc;
251
252 END IF;
253
254 -- For both CASE 1 and CASE 2, now the plsql tables contain the sorted
255 -- action lines and the record index in the plsql tables will be
256 -- the correct action line number
257 -- Call generic actions set API to update the condition date and line number
258
259 SELECT rownum
260 BULK COLLECT INTO l_action_line_number_tbl2
261 FROM pa_adv_action_lines_order_temp;
262
263 --dbms_output.put_line('number of lines: '||l_action_line_cond_id_tbl2.COUNT);
264 --dbms_output.put_line('number of lines2: '||l_condition_date_tbl2.COUNT);
265 --dbms_output.put_line('number of lines3: '||l_action_line_id_tbl2.COUNT);
266 --dbms_output.put_line('number of lines4: '||l_action_line_number_tbl2.COUNT);
267
268 PA_ACTION_SETS_PVT.Bulk_Update_Condition_Date(
269 p_action_line_condition_id_tbl => l_action_line_cond_id_tbl2
270 ,p_condition_date_tbl => l_condition_date_tbl2
271 ,x_return_status => l_return_status
272 );
273
274 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
275 x_return_status := FND_API.G_RET_STS_ERROR;
276 END IF;
277
278 PA_ACTION_SETS_PVT.Bulk_Update_Line_Number(
279 p_action_set_line_id_tbl => l_action_line_id_tbl2
280 ,p_line_number_tbl => l_action_line_number_tbl2
281 ,x_return_status => x_return_status
282 );
283
284 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
285 x_return_status := FND_API.G_RET_STS_ERROR;
286 END IF;
287
288 END IF; -- more than 0 line
289 -- 4537865 : Included Exception Block
290 EXCEPTION
291 WHEN OTHERS THEN
292 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
293 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_ADVERTISEMENTS_PVT'
294 , p_procedure_name => 'Order_Adv_Action_Lines'
295 , p_error_text => SUBSTRB(SQLERRM,1,240));
296 RAISE ;
297 END Order_Adv_Action_Lines;
298
299
300 ----------------------------------------------------------------------
301 -- Procedure
302 -- Perform Publish To All
303 --
304 -- Purpose
305 -- Advertise to everyone
306 ----------------------------------------------------------------------
307 PROCEDURE Publish_To_All (
308 p_action_set_line_id IN pa_action_set_lines.action_set_line_id%TYPE
309 , p_object_id IN pa_action_sets.object_id%TYPE
310 , p_action_code IN pa_action_set_lines.action_code%TYPE
311 , p_action_status_code IN pa_action_set_lines.status_code%TYPE
312 , p_insert_audit_flag IN VARCHAR2 := 'T'
313 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
314 ) IS
315
316 l_return_status VARCHAR2(1);
317 l_action_line_audit_rec pa_action_set_utils.insert_audit_lines_rec_type;
318 l_index NUMBER;
319
320 BEGIN
321
322 -- Initialize the Error Stack
323 PA_DEBUG.init_err_stack('PA_ADVERTISEMENTS_PVT.Publish_To_All');
324
325 --dbms_output.put_line('PA_ADVERTISEMENTS_PVT.publish to all');
326
327 --Log Message
328 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
329 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ADVERTISEMENTS_PVT.Publish_To_All.begin'
330 ,x_msg => 'Beginning of PA_ADVERTISEMENTS_PVT.Publish_To_All'
331 ,x_log_level => 5);
332 END IF;
333
334 -- Initialize the return status to success
335 x_return_status := FND_API.G_RET_STS_SUCCESS;
336
337 --
338 -- Handle PENDING action line only as REVERSE_PENDING and UPDATE_PENDING
339 -- lines can be handled generically by the action set model.
340 -- Insert the Publish to All action into audit table
341 --
342 IF p_action_status_code = 'PENDING' AND p_insert_audit_flag = 'T' THEN
343
344 -- insert the single audit line into the global audit record
345 l_action_line_audit_rec.reason_code := 'CONDITION_MET';
346 l_action_line_audit_rec.action_code := p_action_code;
347 l_action_line_audit_rec.audit_display_attribute := NULL;
348 l_action_line_audit_rec.audit_attribute := NULL;
349 l_action_line_audit_rec.reversed_action_set_line_id := NULL;
350 l_index := PA_ADVERTISEMENTS_PUB.g_action_line_audit_tbl.COUNT;
351 PA_ADVERTISEMENTS_PUB.g_action_line_audit_tbl(l_index) := l_action_line_audit_rec;
352
353 END IF;
354 -- 4537865 : Included Exception Block
355 EXCEPTION
356 WHEN OTHERS THEN
357 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
358 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_ADVERTISEMENTS_PVT'
359 , p_procedure_name => 'Publish_To_All'
360 , p_error_text => SUBSTRB(SQLERRM,1,240));
361 RAISE ;
362 END Publish_To_All;
363
364
365
366 ----------------------------------------------------------------------
367 -- Procedure
368 -- Perform Publish To Organizations
369 --
370 -- Purpose
371 -- Advertise to all resources in organizations under the starting
372 -- organization in the organization hierarchy.
373 ----------------------------------------------------------------------
374 PROCEDURE Publish_To_Organizations (
375 p_action_set_line_id IN pa_action_set_lines.action_set_line_id%TYPE
376 , p_object_id IN pa_action_sets.object_id%TYPE
377 , p_action_code IN pa_action_set_lines.action_code%TYPE
378 , p_action_status_code IN pa_action_set_lines.status_code%TYPE
379 , p_org_hierarchy_version_id IN per_org_structure_versions.org_structure_version_id%TYPE
380 , p_starting_organization_id IN hr_organization_units.organization_id%TYPE := NULL
381 , p_insert_audit_flag IN VARCHAR2 := 'T'
382 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
383 --, x_msg_count OUT NUMBER
384 --, x_msg_data OUT VARCHAR2
385 ) IS
386
387 TYPE number_tbl IS TABLE OF NUMBER
388 INDEX BY BINARY_INTEGER;
389 TYPE varchar_tbl IS TABLE OF VARCHAR2(150)
390 INDEX BY BINARY_INTEGER;
391
392 l_organization_name_tbl varchar_tbl;
393 l_organization_id_tbl number_tbl;
394 l_action_line_audit_rec pa_action_set_utils.insert_audit_lines_rec_type;
395 l_index NUMBER;
396 l_encoded_message_text VARCHAR2(2000);
397 l_msg_index_out NUMBER;
398
399 -- cursor to get all organizations under the specified starting
400 -- organization in the organization hierarchy
401 CURSOR get_all_child_orgs_in_hier IS
402 -- SELECT child_organization_id, pa_expenditures_utils.GetOrgTlName(child_organization_id) -- Commented for Bug 4866284
403 SELECT child_organization_id, pa_resource_utils.get_organization_name(child_organization_id) -- Added for Bug 4866284
404 FROM pa_org_hierarchy_denorm
405 WHERE org_hierarchy_version_id = p_org_hierarchy_version_id
406 AND parent_organization_id = p_starting_organization_id
407 AND pa_org_use_type = 'EXPENDITURES';
408
409 BEGIN
410
411 --dbms_output.put_line('begin of PA_ADVERTISEMENTS_PVT.Publish_To_Organizations');
412
413 -- Initialize the Error Stack
414 PA_DEBUG.init_err_stack('PA_ADVERTISEMENTS_PVT.Publish_To_Organizations');
415
416 --Log Message
417 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
418 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ADVERTISEMENTS_PVT.Publish_To_Organizations'
419 ,x_msg => 'Beginning of PA_ADVERTISEMENTS_PVT.Publish_To_Organizations'
420 ,x_log_level => 5);
421 END IF;
422
423 -- Initialize the return status to success
424 x_return_status := FND_API.G_RET_STS_SUCCESS;
425
426
427 --
428 -- Handle PENDING action line only as REVERSE_PENDING and UPDATE_PENDING
429 -- lines can be handled generically by the action set model.
430 --
431 IF p_action_status_code = 'PENDING' THEN
432
433 -- get all the organizations under the specified starting
434 -- organization in the organization hierarchy
435 OPEN get_all_child_orgs_in_hier;
436 FETCH get_all_child_orgs_in_hier BULK COLLECT INTO l_organization_id_tbl, l_organization_name_tbl;
437 CLOSE get_all_child_orgs_in_hier;
438
439 -- ERROR: Insert error into the stack when there is
440 -- no organization found with the specified criteria
441 IF l_organization_id_tbl.COUNT = 0 THEN
442
443 -- insert error into error stack, get the encoded message
444 -- and insert into audit table
445 PA_ACTION_SET_UTILS.Add_Message(
446 p_app_short_name => 'PA'
447 ,p_msg_name => 'PA_ADV_PUB_ORG_ERR'
448 );
449
450 -- Insert the organizations found into audit table
451 ELSIF p_insert_audit_flag = 'T' THEN
452
453 FOR i IN l_organization_id_tbl.FIRST..l_organization_id_tbl.LAST LOOP
454 -- insert into into the global audit record
455 l_action_line_audit_rec.reason_code := 'CONDITION_MET';
456 l_action_line_audit_rec.action_code := p_action_code;
457 l_action_line_audit_rec.audit_display_attribute := l_organization_name_tbl(i);
458 l_action_line_audit_rec.audit_attribute := l_organization_id_tbl(i);
459 l_action_line_audit_rec.reversed_action_set_line_id := NULL;
460 l_index := PA_ADVERTISEMENTS_PUB.g_action_line_audit_tbl.COUNT;
461 PA_ADVERTISEMENTS_PUB.g_action_line_audit_tbl(l_index) := l_action_line_audit_rec;
462 END LOOP;
463
464 --dbms_output.put_line('number of records in audit global tbl: '||PA_ADVERTISEMENTS_PUB.g_action_line_audit_tbl.COUNT);
465
466 END IF; -- l_organization_id_tbl.count = 0
467
468 -- Insert the error into the audit table
469 -- and return error status if there is error message in the stack
470 IF FND_MSG_PUB.Count_Msg > 0 THEN
471 IF p_insert_audit_flag = 'T' THEN
472 FND_MSG_PUB.get (
473 p_encoded => FND_API.G_TRUE
474 ,p_msg_index => FND_MSG_PUB.Count_Msg
475 ,p_data => l_encoded_message_text
476 ,p_msg_index_out => l_msg_index_out);
477
478 l_action_line_audit_rec.reason_code := 'CONDITION_MET';
479 l_action_line_audit_rec.action_code := p_action_code;
480 l_action_line_audit_rec.encoded_error_message := l_encoded_message_text;
481 l_index := PA_ADVERTISEMENTS_PUB.g_action_line_audit_tbl.COUNT;
482 PA_ADVERTISEMENTS_PUB.g_action_line_audit_tbl(l_index) := l_action_line_audit_rec;
483 END IF;
484 x_return_status := FND_API.G_RET_STS_ERROR;
485 END IF; -- IF FND_MSG_PUB.Count_Msg > 0
486
487 END IF; -- if status is PENDING
488 -- 4537865 : Included Exception Block
489 EXCEPTION
490 WHEN OTHERS THEN
491 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
492 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_ADVERTISEMENTS_PVT'
493 , p_procedure_name => 'Publish_To_Organizations'
494 , p_error_text => SUBSTRB(SQLERRM,1,240));
495 RAISE ;
496 END Publish_To_Organizations;
497
498 ----------------------------------------------------------------------
499 -- Procedure
500 -- Perform Escalate to Next Level
501 --
502 -- Purpose
503 -- Advertise to all resources in organizations under a higher starting
504 -- organization in the organization hierarchy.
505 ----------------------------------------------------------------------
506 PROCEDURE Escalate_to_Next_Level (
507 p_action_set_line_id IN pa_action_set_lines.action_set_line_id%TYPE
508 , p_action_set_line_number IN pa_action_set_lines.action_set_line_number%TYPE
509 , p_action_set_id IN pa_action_sets.action_set_id%TYPE
510 , p_action_set_line_rec_ver_num IN pa_action_set_lines.record_version_number%TYPE
511 , p_object_id IN pa_action_sets.object_id%TYPE
512 , p_action_code IN pa_action_set_lines.action_code%TYPE
513 , p_action_status_code IN pa_action_set_lines.status_code%TYPE
514 , p_action_set_line_cond_tbl IN pa_action_set_utils.action_line_cond_tbl_type
515 , p_insert_audit_flag IN VARCHAR2 := 'T'
516 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
517 ) IS
518
519 l_org_hierarchy_version_id per_org_structure_versions.org_structure_version_id%TYPE;
520 l_start_org_id hr_organization_units.organization_id%TYPE;
521 l_new_start_org_id hr_organization_units.organization_id%TYPE;
522 l_action_line_audit_rec pa_action_set_utils.insert_audit_lines_rec_type;
523 l_index NUMBER;
524 l_msg_count NUMBER;
525 l_msg_data VARCHAR2(2000);
526 l_encoded_message_text VARCHAR2(2000);
527 l_msg_index_out NUMBER;
528
529 TYPE number_tbl IS TABLE OF NUMBER
530 INDEX BY BINARY_INTEGER;
531 TYPE varchar_tbl IS TABLE OF VARCHAR2(240)
532 INDEX BY BINARY_INTEGER;
533
534 l_organization_name_tbl varchar_tbl;
535 l_organization_id_tbl number_tbl;
536
537 -- cursor to get the next organization up in the
538 -- specified Organization Hierarchy
539 CURSOR get_next_start_org(c_org_hierarchy_version_id NUMBER, c_start_org_id NUMBER) IS
540 SELECT organization_id_parent
541 FROM per_org_structure_elements
542 WHERE org_structure_version_id = c_org_hierarchy_version_id
543 AND organization_id_child = c_start_org_id;
544
545 -- cursor to get all organizations under the specified starting
546 -- organization in the organization hierarchy that have not been published to
547 CURSOR get_child_orgs_in_hier(c_org_hierarchy_version_id NUMBER, c_start_org_id NUMBER) IS
548 -- SELECT ohd.child_organization_id, pa_expenditures_utils.GetOrgTlName(ohd.child_organization_id) -- Commented for Bug 4866284
549 SELECT ohd.child_organization_id, pa_resource_utils.get_organization_name(ohd.child_organization_id) -- Added for Bug 4866284
550 FROM pa_org_hierarchy_denorm ohd
551 WHERE ohd.org_hierarchy_version_id = c_org_hierarchy_version_id
552 AND ohd.parent_organization_id = c_start_org_id
553 AND ohd.pa_org_use_type = 'EXPENDITURES'
554 MINUS
555 -- SELECT ohd.child_organization_id, pa_expenditures_utils.GetOrgTlName(ohd.child_organization_id) -- Commented for Bug 4866284
556 SELECT ohd.child_organization_id, pa_resource_utils.get_organization_name(ohd.child_organization_id) -- Added for Bug 4866284
557 FROM pa_org_hierarchy_denorm ohd,
558 pa_action_set_line_aud asla
559 WHERE ohd.org_hierarchy_version_id = c_org_hierarchy_version_id
560 AND ohd.parent_organization_id = c_start_org_id
561 AND ohd.pa_org_use_type = 'EXPENDITURES'
562 AND ohd.child_organization_id = to_number(asla.audit_attribute)
563 AND (asla.action_code = 'ADVERTISEMENT_PUB_TO_START_ORG'
564 OR asla.action_code = 'ADVERTISEMENT_ESC_TO_NEXT_LVL')
565 AND asla.active_flag = 'Y'
566 AND asla.object_id = p_object_id
567 AND asla.object_type = 'OPEN_ASSIGNMENT'
568 AND asla.action_set_type_code = 'ADVERTISEMENT';
569
570 BEGIN
571
572 --dbms_output.put_line('PA_ADVERTISEMENTS_PVT.publish to all');
573
574 -- Initialize the Error Stack
575 PA_DEBUG.init_err_stack('PA_ADVERTISEMENTS_PVT.Escalate_to_Next_Level');
576
577 --Log Message
578 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
579 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ADVERTISEMENTS_PVT.Escalate_to_Next_Level'
580 ,x_msg => 'Beginning of PA_ADVERTISEMENTS_PVT.Escalate_to_Next_Level'
581 ,x_log_level => 5);
582 END IF;
583
584 -- Initialize the return status to success
585 x_return_status := FND_API.G_RET_STS_SUCCESS;
586
587 --
588 -- Handle PENDING action line only as REVERSE_PENDING and UPDATE_PENDING
589 -- lines can be handled generically by the action set model.
590 --
591 IF p_action_status_code = 'PENDING' THEN
592
593 -- Get the action lines with status equals Pending or Performed
594 -- action code equals Publish to Organization or Escalate to Next Level
595 -- and action set line number is the maximum number less than the
596 -- current action line number
597 SELECT to_number(action_attribute1), to_number(action_attribute2) into
598 l_org_hierarchy_version_id, l_start_org_id
599 FROM pa_action_set_lines
600 WHERE action_set_id = p_action_set_id
601 AND action_set_line_number = (
602 SELECT MAX(action_set_line_number)
603 FROM pa_action_set_lines
604 WHERE (action_code = 'ADVERTISEMENT_PUB_TO_START_ORG'
605 OR action_code = 'ADVERTISEMENT_ESC_TO_NEXT_LVL')
606 AND (status_code = 'PENDING'
607 OR status_code = 'COMPLETE')
608 AND ACTION_SET_LINE_NUMBER < p_action_set_line_number
609 AND action_set_id = p_action_set_id);
610
611 --dbms_output.put_line('l_org_hierarchy_version_id '||l_org_hierarchy_version_id);
612 --dbms_output.put_line('l_start_org_id ' || l_start_org_id);
613
614 -- Find the next organization up in the specified Organization Hierarchy
615 OPEN get_next_start_org(l_org_hierarchy_version_id, l_start_org_id);
616 FETCH get_next_start_org INTO l_new_start_org_id;
617 CLOSE get_next_start_org;
618
619 --dbms_output.put_line('l_new_start_org_id ' || l_new_start_org_id);
620
621 -- ERROR: Insert error into the stack when the next
622 -- organization does not exist
623 If l_new_start_org_id is null THEN
624
625 -- insert error into error stack, get the encoded message
626 -- and insert into audit table
627 PA_ACTION_SET_UTILS.Add_Message(
628 p_app_short_name => 'PA'
629 ,p_msg_name => 'PA_ADV_ESC_LVL_ERR'
630 );
631
632 ELSE
633
634 -- Update the current action line to store the new
635 -- start_organization_id and org_hierarchy_version_id
636 PA_ACTION_SETS_PUB.Update_Action_Set_Line (
637 p_action_set_line_id => p_action_set_line_id
638 ,p_action_set_line_number => p_action_set_line_number
639 ,p_record_version_number => p_action_set_line_rec_ver_num
640 ,p_action_code => p_action_code
641 ,p_action_attribute1 => to_char(l_org_hierarchy_version_id)
642 ,p_action_attribute2 => to_char(l_new_start_org_id)
643 ,p_condition_tbl => p_action_set_line_cond_tbl
644 ,p_validate_only => FND_API.G_FALSE
645 ,p_commit => FND_API.G_FALSE
646 ,p_init_msg_list => FND_API.G_FALSE
647 ,x_return_status => x_return_status
648 ,x_msg_count => l_msg_count
649 ,x_msg_data => l_msg_data);
650
651 --dbms_output.put_line('finding organizations to publish');
652
653 -- publish to all child organizations exception the ones that
654 -- have already been published to
655 OPEN get_child_orgs_in_hier(l_org_hierarchy_version_id, l_new_start_org_id);
656 FETCH get_child_orgs_in_hier
657 BULK COLLECT INTO l_organization_id_tbl, l_organization_name_tbl;
658 CLOSE get_child_orgs_in_hier;
659
660 --dbms_output.put_line('finished finding organizations to publish');
661
662 -- Insert organizations into audit table
663 IF p_insert_audit_flag = 'T' THEN
664
665 FOR i IN l_organization_id_tbl.FIRST..l_organization_id_tbl.LAST LOOP
666 -- insert into into the global audit record
667 l_action_line_audit_rec.reason_code := 'CONDITION_MET';
668 l_action_line_audit_rec.action_code := p_action_code;
669 l_action_line_audit_rec.audit_display_attribute := l_organization_name_tbl(i);
670 l_action_line_audit_rec.audit_attribute := l_organization_id_tbl(i);
671 l_action_line_audit_rec.reversed_action_set_line_id := NULL;
672 l_index := PA_ADVERTISEMENTS_PUB.g_action_line_audit_tbl.COUNT;
673 PA_ADVERTISEMENTS_PUB.g_action_line_audit_tbl(l_index) := l_action_line_audit_rec;
674 END LOOP;
675
676 END IF; -- if l_organization_id_tbl.COUNT = 0
677 END IF; --if l_new_start_org_id is null
678
679 --
680 -- Insert the error into the audit table
681 -- and return error status if there is error message in the stack
682 --
683 IF FND_MSG_PUB.Count_Msg > 0 THEN
684
685 IF p_insert_audit_flag = 'T' THEN
686 FND_MSG_PUB.get (
687 p_encoded => FND_API.G_TRUE
688 ,p_msg_index => FND_MSG_PUB.Count_Msg
689 ,p_data => l_encoded_message_text
690 ,p_msg_index_out => l_msg_index_out);
691
692 l_action_line_audit_rec.reason_code := 'CONDITION_MET';
693 l_action_line_audit_rec.action_code := p_action_code;
694 l_action_line_audit_rec.encoded_error_message := l_encoded_message_text;
695 l_index := PA_ADVERTISEMENTS_PUB.g_action_line_audit_tbl.COUNT;
696 PA_ADVERTISEMENTS_PUB.g_action_line_audit_tbl(l_index) := l_action_line_audit_rec;
697 END IF;
698 x_return_status := FND_API.G_RET_STS_ERROR;
699 END IF;
700
701 END IF; -- if status is PENDING
702 -- 4537865 : Included Exception Block
703 EXCEPTION
704 WHEN OTHERS THEN
705 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
706 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_ADVERTISEMENTS_PVT'
707 , p_procedure_name => 'Escalate_To_Next_Level'
708 , p_error_text => SUBSTRB(SQLERRM,1,240));
709 RAISE ;
710 END Escalate_To_Next_Level;
711
712 ----------------------------------------------------------------------
713 -- Procedure
714 -- Perform Publish To Staffing Managers
715 --
716 -- Purpose
717 -- Advertise to staffing managers of the specified organization.
718 ----------------------------------------------------------------------
719 PROCEDURE Publish_To_Staffing_Managers (
720 p_action_set_line_id IN pa_action_set_lines.action_set_line_id%TYPE
721 , p_object_id IN pa_action_sets.object_id%TYPE
722 , p_action_code IN pa_action_set_lines.action_code%TYPE
723 , p_action_status_code IN pa_action_set_lines.status_code%TYPE
724 , p_organization_id IN hr_organization_units.organization_id%TYPE
725 , p_insert_audit_flag IN VARCHAR2 := 'T'
726 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
727 ) IS
728
729 TYPE number_tbl IS TABLE OF NUMBER
730 INDEX BY BINARY_INTEGER;
731 TYPE varchar_tbl IS TABLE OF VARCHAR2(240)
732 INDEX BY BINARY_INTEGER;
733
734 l_person_name_tbl varchar_tbl;
735 l_person_id_tbl number_tbl;
736 l_action_line_audit_rec pa_action_set_utils.insert_audit_lines_rec_type;
737 l_index NUMBER;
738 l_encoded_message_text VARCHAR2(2000);
739 l_msg_index_out NUMBER;
740
741 -- cursor to get all people with resource authority
742 -- in the given organization
743
744 CURSOR get_people_with_proj_authority IS
745 SELECT pro.person_name, pro.person_id
746 FROM pa_people_role_on_orgs_v pro, per_people_f pf
747 WHERE pro.organization_id = to_char(p_organization_id)
748 AND pro.project_role_type = '3'
749 AND sysdate between pro.start_date_active and
750 nvl(pro.end_date_active, sysdate)
751 AND TRUNC(sysdate) between TRUNC(PF.EFFECTIVE_START_DATE) AND
752 TRUNC(PF.EFFECTIVE_END_DATE)
753 AND nvl(PF.CURRENT_EMPLOYEE_FLAG,nvl(PF.CURRENT_NPW_FLAG,'N'))='Y'
754 AND PF.person_id=pro.person_id;
755
756 /* Changed the cursor as above for bug 4600093
757 CURSOR get_people_with_proj_authority IS
758 SELECT person_name, person_id
759 FROM pa_people_role_on_orgs_v
760 WHERE organization_id = to_char(p_organization_id)
761 AND project_role_type = '3'
762 AND sysdate between start_date_active and nvl(end_date_active, sysdate);
763 */
764
765 BEGIN
766
767 --dbms_output.put_line('PA_ADVERTISEMENTS_PVT.publish to staffing manager');
768
769 -- Initialize the Error Stack
770 PA_DEBUG.init_err_stack('PA_ADVERTISEMENTS_PVT.Publish_To_Staffing_Managers');
771
772 --Log Message
773 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
774 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ADVERTISEMENTS_PVT.Publish_To_Staffing_Managers'
775 ,x_msg => 'Beginning of PA_ADVERTISEMENTS_PVT.Publish_To_Staffing_Managers'
776 ,x_log_level => 5);
777 END IF;
778
779 -- Initialize the return status to success
780 x_return_status := FND_API.G_RET_STS_SUCCESS;
781
782 --
783 -- Handle PENDING action line only as REVERSE_PENDING and UPDATE_PENDING
784 -- lines can be handled generically by the action set model.
785 --
786 IF p_action_status_code = 'PENDING' THEN
787
788 OPEN get_people_with_proj_authority;
789 FETCH get_people_with_proj_authority
790 BULK COLLECT INTO l_person_name_tbl, l_person_id_tbl;
791 CLOSE get_people_with_proj_authority;
792
793 -- ERROR: Insert error into the stack when there is no people
794 -- with project authority in the organization
795 IF l_person_id_tbl.COUNT = 0 THEN
796 -- insert error to stack
797 PA_ACTION_SET_UTILS.Add_Message(
798 p_app_short_name => 'PA'
799 ,p_msg_name => 'PA_ADV_PUB_SM_ERR'
800 );
801
802 ELSIF p_insert_audit_flag = 'T' THEN
803
804 -- Insert the people into the global audit record
805 FOR i IN l_person_id_tbl.FIRST..l_person_id_tbl.LAST LOOP
806 l_action_line_audit_rec.reason_code := 'CONDITION_MET';
807 l_action_line_audit_rec.action_code := p_action_code;
808 l_action_line_audit_rec.audit_display_attribute := l_person_name_tbl(i);
809 l_action_line_audit_rec.audit_attribute := l_person_id_tbl(i);
810 l_action_line_audit_rec.reversed_action_set_line_id := NULL;
811 l_index := PA_ADVERTISEMENTS_PUB.g_action_line_audit_tbl.COUNT;
812 PA_ADVERTISEMENTS_PUB.g_action_line_audit_tbl(l_index) := l_action_line_audit_rec;
813 END LOOP;
814
815 END IF; -- if l_person_id_tbl.COUNT = 0
816
817 --
818 -- Insert the error into the audit table
819 -- and return error status if there is error message in the stack
820 --
821 IF FND_MSG_PUB.Count_Msg > 0 THEN
822 IF p_insert_audit_flag = 'T' THEN
823 FND_MSG_PUB.get (
824 p_encoded => FND_API.G_TRUE
825 ,p_msg_index => FND_MSG_PUB.Count_Msg
826 ,p_data => l_encoded_message_text
827 ,p_msg_index_out => l_msg_index_out);
828
829 l_action_line_audit_rec.reason_code := 'CONDITION_MET';
830 l_action_line_audit_rec.action_code := p_action_code;
831 l_action_line_audit_rec.encoded_error_message := l_encoded_message_text;
832 l_index := PA_ADVERTISEMENTS_PUB.g_action_line_audit_tbl.COUNT;
833 PA_ADVERTISEMENTS_PUB.g_action_line_audit_tbl(l_index) := l_action_line_audit_rec;
834 END IF;
835 x_return_status := FND_API.G_RET_STS_ERROR;
836 END IF;
837
838 END IF; -- if status is PENDING
839 -- 4537865 : Included Exception Block
840 EXCEPTION
841 WHEN OTHERS THEN
842 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
843 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_ADVERTISEMENTS_PVT'
844 , p_procedure_name => 'Publish_To_Staffing_Managers'
845 , p_error_text => SUBSTRB(SQLERRM,1,240));
846 RAISE ;
847 END Publish_To_Staffing_Managers;
848
849
850 ----------------------------------------------------------------------
851 -- Procedure
852 -- Perform Send Email
853 --
854 -- Purpose
855 -- Send the advertisement email to a specific email address.
856 ----------------------------------------------------------------------
857 PROCEDURE Send_Email (
858 p_action_set_line_id IN pa_action_set_lines.action_set_line_id%TYPE
859 , p_object_id IN pa_action_sets.object_id%TYPE
860 , p_action_code IN pa_action_set_lines.action_code%TYPE
861 , p_action_status_code IN pa_action_set_lines.status_code%TYPE
862 , p_email_address IN VARCHAR2
863 , p_project_id IN pa_projects_all.project_id%TYPE
864 , p_insert_audit_flag IN VARCHAR2 := 'T'
865 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
866 ) IS
867
868 l_return_status VARCHAR2(1);
869 l_wf_adhoc_user_name VARCHAR2(240);
870 l_wf_adhoc_display_name VARCHAR2(240);
871 l_wf_process VARCHAR2(30);
872 l_action_line_audit_rec pa_action_set_utils.insert_audit_lines_rec_type;
873 l_index NUMBER;
874 l_encoded_message_text VARCHAR2(2000);
875 l_msg_index_out NUMBER;
876 l_action_status_code VARCHAR2(30);
877
878 --added for 4701745
879 TYPE v_tab IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
880 l_email_tab v_tab;
881
882 l_email_address VARCHAR2(4000) := p_email_address;
883 l_comma_pos NUMBER;
884 l_temp_email_address varchar(240);
885 i NUMBER := 0;
886 --end for 4701745
887
888 BEGIN
889 --dbms_output.put_line('PA_ADVERTISEMENTS_PVT.send email');
890
891 -- Initialize the Error Stack
892 PA_DEBUG.init_err_stack('PA_ADVERTISEMENTS_PVT.Send_Email');
893
894 --Log Message
895 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
896 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ADVERTISEMENTS_PVT.Send_Email'
897 ,x_msg => 'Beginning of PA_ADVERTISEMENTS_PVT.Send_Email'
898 ,x_log_level => 5);
899 END IF;
900
901 -- Initialize the return status to success
902 x_return_status := FND_API.G_RET_STS_SUCCESS;
903 /* bug 4701745*/
904
905 IF instr(l_email_address, ',') > 0 THEN
906 l_email_address := l_email_address || ',';
907
908 WHILE nvl(length(l_email_address),-1) > 0 LOOP
909 i := i + 1;
910 l_comma_pos := instr(l_email_address, ',');
911 l_temp_email_address := substr(l_email_address,1,l_comma_pos-1);
912 l_email_tab(i) := trim(l_temp_email_address);
913 l_email_address := substr(l_email_address, l_comma_pos+1);
914 END LOOP;
915 ELSE
916 l_email_tab(1) := l_email_address;
917 END IF;
918 /*end bug 4701745 */
919
920 -- create an adhoc user with the specified email address
921 -- and set the notification preference to Plain Text Email
922 IF l_email_tab.COUNT > 0 THEN --added for 4701745
923
924 FOR j IN l_email_tab.FIRST..l_email_tab.LAST LOOP --added for 4701745
925
926 IF l_email_tab(j) IS NOT NULL THEN --added for 4701745
927
928 l_wf_adhoc_user_name:= null; --added for 4701745
929 l_wf_adhoc_display_name:= null; --added for 4701745
930
931 WF_DIRECTORY.CreateAdHocUser(
932 name => l_wf_adhoc_user_name
933 , display_name => l_wf_adhoc_display_name
934 , notification_preference => 'MAILTEXT'
935 , email_address => l_email_tab(j)--p_email_address added for 4701745
936 , expiration_date => sysdate + 1);
937
938 --dbms_output.put_line('ad hoc user created as '|| l_wf_adhoc_user_name);
939
940 -- Start different wf process to send different email
941 -- depending of the action line status
942 IF p_action_status_code = 'PENDING' THEN
943 l_wf_process := 'PA_ADVERTISEMENT_NTF_PROCESS';
944 ELSE
945 l_wf_process := 'PA_REMOVE_ADV_NTF_PROCESS';
946 END IF;
947
948 Start_Adv_Notification_WF (
949 p_action_code => p_action_code
950 , p_wf_user_name => l_wf_adhoc_user_name
951 , p_assignment_id => p_object_id
952 , p_project_id => p_project_id
953 , p_wf_process => l_wf_process
954 , p_wf_item_type => 'PARADVWF'
955 , x_return_status => x_return_status);
956
957 -- Insert the email address into audit table
958 IF p_action_status_code = 'PENDING' AND p_insert_audit_flag = 'T' THEN
959
960 -- insert the single audit line into the global audit record
961 l_action_line_audit_rec.reason_code := 'CONDITION_MET';
962 l_action_line_audit_rec.action_code := p_action_code;
963 l_action_line_audit_rec.audit_display_attribute := l_email_tab(j);--p_email_address added for 4701745;
964 l_action_line_audit_rec.audit_attribute := l_email_tab(j);--p_email_address added for 4701745;
965 l_action_line_audit_rec.reversed_action_set_line_id := NULL;
966
967 l_index := PA_ADVERTISEMENTS_PUB.g_action_line_audit_tbl.COUNT;
968 PA_ADVERTISEMENTS_PUB.g_action_line_audit_tbl(l_index) := l_action_line_audit_rec;
969
970 END IF;
971
972 END IF; -- l_email_tab(j) NOT NULL - added for 4701745
973 END LOOP; -- l_email_tab FOR loop end- added for 4701745
974 END IF;--end l_email_tab.count end - added for 4701745
975
976 /*end bug 4701745 */
977
978 -- 4537865 : Included Exception Block
979 EXCEPTION
980 WHEN OTHERS THEN
981 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
982 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_ADVERTISEMENTS_PVT'
983 , p_procedure_name => 'Send_Email'
984 , p_error_text => SUBSTRB(SQLERRM,1,240));
985 RAISE ;
986 END Send_Email;
987
988
989
990 ----------------------------------------------------------------------
991 -- Procedure
992 -- Perform Send Notification
993 --
994 -- Purpose
995 -- Send the advertisement notification to a specific person or role
996 -- on the project.
997 ----------------------------------------------------------------------
998 PROCEDURE Send_Notification (
999 p_action_set_line_id IN pa_action_set_lines.action_set_line_id%TYPE
1000 , p_object_id IN pa_action_sets.object_id%TYPE
1001 , p_action_code IN pa_action_set_lines.action_code%TYPE
1002 , p_action_status_code IN pa_action_set_lines.status_code%TYPE
1003 , p_method IN VARCHAR2
1004 , p_person_id IN pa_resources_denorm.person_id%TYPE := FND_API.G_MISS_NUM
1005 , p_project_role_id IN pa_project_role_types.project_role_id%TYPE := FND_API.G_MISS_NUM
1006 , p_project_id IN pa_project_assignments.project_id%TYPE
1007 , p_insert_audit_flag IN VARCHAR2 := 'T'
1008 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1009 --, x_msg_count OUT NUMBER
1010 --, x_msg_data OUT VARCHAR2
1011 ) IS
1012
1013 --cursor to get tpeople on the project role
1014 -- Commented for Performance Fix 4898314 SQL ID 14906391
1015 --CURSOR get_person_on_project_role IS
1016 --SELECT rv.resource_source_id, rv.resource_name
1017 --FROM pa_project_parties pp,
1018 -- pa_c_resource_v rv
1019 --WHERE pp.project_id = p_project_id
1020 -- AND pp.project_role_id = p_project_role_id
1021 -- AND pp.resource_source_id = rv.resource_source_id;
1022
1023 -- Start of Performance Fix 4898314 SQL ID 14906391
1024 CURSOR get_person_on_project_role IS
1025 SELECT per.person_id resource_source_id, per.full_name resource_name
1026 FROM pa_project_parties pp,
1027 per_people_f per
1028 WHERE pp.project_id = p_project_id
1029 AND pp.project_role_id = p_project_role_id
1030 AND pp.resource_source_id = per.person_id
1031 AND (per.employee_number IS NOT NULL OR per.npw_number IS NOT NULL)
1032 AND (per.current_employee_flag = 'Y' OR per.current_npw_flag = 'Y')
1033 AND trunc(sysdate) BETWEEN per.effective_start_date
1034 AND per.effective_end_date
1035 AND trunc(sysdate) BETWEEN pp.start_date_active and nvl(pp.end_date_active, sysdate); --bug#9500452
1036 -- End of Performance Fix 4898314 SQL ID 14906391
1037
1038 --cursor to get person's name
1039 -- Commented for Performance Fix 4898314 SQL ID 14906422
1040 -- CURSOR get_person_name IS
1041 -- SELECT resource_source_id, resource_name
1042 -- FROM pa_c_resource_v
1043 -- WHERE resource_source_id = p_person_id;
1044
1045 -- Start of Performance Fix 4898314 SQL ID 14906422
1046 CURSOR get_person_name IS
1047 SELECT per.person_id resource_source_id, per.full_name resource_name
1048 FROM per_people_f per
1049 WHERE per.person_id = p_person_id
1050 AND (per.employee_number IS NOT NULL OR per.npw_number IS NOT NULL)
1051 AND (per.current_employee_flag = 'Y' OR per.current_npw_flag = 'Y')
1052 AND trunc(sysdate) BETWEEN per.effective_start_date
1053 AND per.effective_end_date;
1054 -- End of Performance Fix 4898314 SQL ID 14906422
1055
1056 l_ntf_recipient_person_id_tbl system.pa_num_tbl_type;
1057 l_ntf_recipient_name_tbl system.pa_varchar2_240_tbl_type;
1058 l_return_status VARCHAR2(1);
1059 l_wf_process VARCHAR2(30);
1060 l_encoded_message_text VARCHAR2(2000);
1061 l_msg_index_out NUMBER;
1062 l_action_line_audit_rec pa_action_set_utils.insert_audit_lines_rec_type;
1063 l_index NUMBER;
1064 l_action_status_code VARCHAR2(30);
1065
1066 BEGIN
1067
1068 --dbms_output.put_line('PA_ADVERTISEMENTS_PVT.send notification');
1069
1070 -- Initialize the Error Stack
1071 PA_DEBUG.init_err_stack('PA_ADVERTISEMENTS_PVT.Send_Notification');
1072
1073 --Log Message
1074 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1075 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ADVERTISEMENTS_PVT.Send_Notification'
1076 ,x_msg => 'Beginning of PA_ADVERTISEMENTS_PVT.Send_Notification'
1077 ,x_log_level => 5);
1078 END IF;
1079
1080 -- Initialize the return status to success
1081 x_return_status := FND_API.G_RET_STS_SUCCESS;
1082
1083 -- get the person_id of the notification recipients
1084 IF p_method = 'PROJECT_ROLE' THEN
1085
1086 OPEN get_person_on_project_role;
1087 FETCH get_person_on_project_role
1088 BULK COLLECT INTO l_ntf_recipient_person_id_tbl, l_ntf_recipient_name_tbl;
1089 CLOSE get_person_on_project_role;
1090
1091 ELSE
1092
1093 OPEN get_person_name;
1094 FETCH get_person_name
1095 BULK COLLECT INTO l_ntf_recipient_person_id_tbl, l_ntf_recipient_name_tbl;
1096 CLOSE get_person_name;
1097
1098 END IF;
1099
1100 -- ERROR: Insert error into the stack when no one is found
1101 IF l_ntf_recipient_person_id_tbl.COUNT = 0 THEN
1102
1103 IF p_action_code = 'ADVERTISEMENT_SEND_NTF_PERSON' THEN
1104
1105 -- insert error into audit, 'User is not valid'
1106 PA_ACTION_SET_UTILS.Add_Message(
1107 p_app_short_name => 'PA'
1108 ,p_msg_name => 'PA_ADV_NTF_PERSON_ERR'
1109 );
1110
1111 ELSIF p_action_code = 'ADVERTISEMENT_SEND_NTF_ROLE' THEN
1112 -- insert error into audit, Person does not exists for the project role
1113 PA_ACTION_SET_UTILS.Add_Message(
1114 p_app_short_name => 'PA'
1115 ,p_msg_name => 'PA_ADV_NTF_ROLE_ERR'
1116 );
1117
1118 END IF;
1119
1120 ELSE
1121
1122 -- Start different wf process to send different notifications
1123 -- depending of the action line status
1124 IF p_action_status_code = 'PENDING' THEN
1125 l_wf_process := 'PA_ADVERTISEMENT_NTF_PROCESS';
1126 ELSE
1127 l_wf_process := 'PA_REMOVE_ADV_NTF_PROCESS';
1128 END IF;
1129
1130 Start_Adv_Notification_WF (
1131 p_action_code => p_action_code
1132 , p_ntf_recipient_person_id_tbl => l_ntf_recipient_person_id_tbl
1133 , p_ntf_recipient_name_tbl => l_ntf_recipient_name_tbl
1134 , p_assignment_id => p_object_id
1135 , p_project_id => p_project_id
1136 , p_wf_process => l_wf_process
1137 , p_wf_item_type => 'PARADVWF'
1138 , p_insert_audit_flag => p_insert_audit_flag
1139 , x_return_status => x_return_status);
1140
1141 END IF; -- no person
1142
1143 --
1144 -- Insert the error into the audit table
1145 -- and return error status if there is error message in the stack
1146 --
1147 IF FND_MSG_PUB.Count_Msg > 0 THEN
1148 IF p_insert_audit_flag = 'T' THEN
1149
1150 FOR i in 1..FND_MSG_PUB.Count_Msg LOOP
1151 FND_MSG_PUB.get (
1152 p_encoded => FND_API.G_TRUE
1153 ,p_msg_index => i
1154 ,p_data => l_encoded_message_text
1155 ,p_msg_index_out => l_msg_index_out);
1156
1157 l_action_line_audit_rec.action_code := p_action_code;
1158 l_action_line_audit_rec.encoded_error_message := l_encoded_message_text;
1159 IF l_action_status_code = 'REVERSE_PENDING' THEN
1160 l_action_line_audit_rec.reason_code := 'DELETED';
1161 l_action_line_audit_rec.reversed_action_set_line_id := p_action_set_line_id;
1162 ELSIF l_action_status_code = 'UPDATE_PENDING' THEN
1163 l_action_line_audit_rec.reversed_action_set_line_id := p_action_set_line_id;
1164 l_action_line_audit_rec.reason_code := 'UPDATED';
1165 ELSE
1166 l_action_line_audit_rec.reason_code := 'CONDITION_MET';
1167 END IF;
1168 l_index := PA_ADVERTISEMENTS_PUB.g_action_line_audit_tbl.COUNT;
1169 PA_ADVERTISEMENTS_PUB.g_action_line_audit_tbl(l_index) := l_action_line_audit_rec;
1170 END LOOP;
1171 END IF;
1172 x_return_status := FND_API.G_RET_STS_ERROR;
1173 END IF;
1174 -- 4537865 : Included Exception Block
1175 EXCEPTION
1176 WHEN OTHERS THEN
1177 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1178 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_ADVERTISEMENTS_PVT'
1179 , p_procedure_name => 'Send_Notification'
1180 , p_error_text => SUBSTRB(SQLERRM,1,240));
1181 RAISE ;
1182 END Send_Notification;
1183
1184
1185 ----------------------------------------------------------------------
1186 -- Procedure
1187 -- Start Advertisement Notification Workflow
1188 --
1189 -- Purpose
1190 -- Start Workflow process to send advertisement notification
1191 ----------------------------------------------------------------------
1192 PROCEDURE Start_Adv_Notification_WF (
1193 p_action_code IN pa_action_set_lines.action_code%TYPE
1194 , p_ntf_recipient_person_id_tbl IN system.pa_num_tbl_type := NULL
1195 , p_ntf_recipient_name_tbl IN system.pa_varchar2_240_tbl_type := NULL
1196 , p_wf_user_name IN VARCHAR2 := NULL
1197 , p_wf_process IN VARCHAR2
1198 , p_wf_item_type IN VARCHAR2 := 'PARADVWF'
1199 , p_assignment_id IN pa_project_assignments.assignment_id%TYPE
1200 , p_project_id IN pa_projects_all.project_id%TYPE
1201 , p_insert_audit_flag IN VARCHAR2 := 'T'
1202 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1203 ) IS
1204
1205 l_ntf_recipient_person_id_tbl system.pa_num_tbl_type;
1206 l_wf_item_key NUMBER;
1207 l_set_text_attr_name_tbl Wf_Engine.NameTabTyp;
1208 l_set_text_attr_value_tbl Wf_Engine.TextTabTyp;
1209 l_set_num_attr_name_tbl Wf_Engine.NameTabTyp;
1210 l_set_num_attr_value_tbl Wf_Engine.NumTabTyp;
1211 l_set_date_attr_name_tbl Wf_Engine.NameTabTyp;
1212 l_set_date_attr_value_tbl Wf_Engine.DateTabTyp;
1213 l_err_code fnd_new_messages.message_name%TYPE;
1214 l_err_stage VARCHAR2(2000);
1215 l_err_stack VARCHAR2(2000);
1216
1217 i NUMBER;
1218 l_ntf_recipient_user_name fnd_user.user_name%TYPE;
1219 l_display_name VARCHAR2(240);
1220 l_save_threshold NUMBER;
1221 l_wf_item_type VARCHAR2(8);
1222
1223 l_assignment_name pa_project_assignments.assignment_name%TYPE;
1224 l_start_date pa_project_assignments.start_date%TYPE;
1225 l_end_date pa_project_assignments.end_date%TYPE;
1226 l_project_organization_name hr_organization_units.name%TYPE;
1227 l_project_role_name pa_project_role_types.meaning%TYPE;
1228 l_min_resource_job_level pa_project_assignments.min_resource_job_level%TYPE;
1229 l_max_resource_job_level pa_project_assignments.max_resource_job_level%TYPE;
1230 l_staffing_priority_name VARCHAR2(80);
1231 l_description pa_project_assignments.description%TYPE;
1232 l_additional_information pa_project_assignments.additional_information%TYPE;
1233 l_project_name pa_projects_all.name%TYPE;
1234 l_project_number pa_projects_all.segment1%TYPE;
1235 l_project_organization HR_ALL_ORGANIZATION_UNITS.name%TYPE;
1236 -- 4363092 TCA changes, replaced RA views with HZ tables
1237 --l_project_customer RA_CUSTOMERS.customer_name%TYPE;
1238 l_project_customer hz_parties.party_name%TYPE;
1239 -- 4363092 done
1240 l_project_manager per_all_people_f.full_name%TYPE;
1241 l_project_manager_id pa_project_parties.resource_source_id%TYPE;
1242 l_effort pa_project_assignments.assignment_effort%TYPE;
1243 l_duration NUMBER;
1244 l_action_line_audit_rec pa_action_set_utils.insert_audit_lines_rec_type;
1245 l_index NUMBER;
1246 l_requirement_overview_link VARCHAR2(2000);
1247
1248 l_revenue_bill_rate NUMBER;
1249 l_revenue_currency_code VARCHAR2(15);
1250 l_bill_rate_override NUMBER;
1251 l_bill_rate_curr_override VARCHAR2(30);
1252 l_markup_percent_override NUMBER;
1253 l_fcst_tp_amount_type_name VARCHAR2(80);
1254 l_tp_rate_override NUMBER;
1255 l_tp_currency_override VARCHAR2(30);
1256 l_tp_calc_base_code_override VARCHAR2(30);
1257 l_tp_percent_applied_override NUMBER;
1258 l_work_type_name VARCHAR2(80);
1259 /* Bug 3051110-Added the following variables */
1260 l_transfer_price_rate pa_project_assignments.transfer_price_rate%type;
1261 l_transfer_pr_rate_curr pa_project_assignments.transfer_pr_rate_curr%type;
1262 l_override_basis_name VARCHAR2(80) := NULL;
1263
1264 --cursor to get attributes of the requirement to be
1265 --displayed in notifications
1266 -- Bug 2388060 - Apply Action Set after schedule has been created
1267 -- Get the Assignment Effort if it is null
1268 CURSOR get_requirement_info IS
1269 SELECT asgn.assignment_name,
1270 asgn.start_date,
1271 asgn.end_date,
1272 -- pa_expenditures_utils.GetOrgTlName(proj.carrying_out_organization_id), -- Commented for Bug 4866284
1273 pa_resource_utils.get_organization_name(proj.carrying_out_organization_id), -- Added for Bug 4866284
1274 prt.meaning,
1275 asgn.min_resource_job_level,
1276 asgn.max_resource_job_level,
1277 sp.meaning staffing_priority_name,
1278 nvl(asgn.assignment_effort, PA_SCHEDULE_UTILS.get_num_hours(asgn.project_id, asgn.assignment_id)),
1279 (trunc(asgn.end_date) - trunc(asgn.start_date) +1 ),
1280 asgn.description,
1281 asgn.additional_information,
1282 asgn.revenue_bill_rate,
1283 asgn.revenue_currency_code,
1284 asgn.bill_rate_override,
1285 asgn.bill_rate_curr_override,
1286 asgn.markup_percent_override,
1287 fcst.meaning,
1288 asgn.tp_rate_override,
1289 asgn.tp_currency_override,
1290 asgn.tp_calc_base_code_override,
1291 asgn.tp_percent_applied_override,
1292 wt.name,
1293 asgn.transfer_price_rate, -- Added for bug 3051110
1294 asgn.transfer_pr_rate_curr
1295 FROM pa_project_assignments asgn,
1296 pa_projects_all proj,
1297 pa_project_role_types prt,
1298 pa_lookups sp,
1299 pa_lookups fcst,
1300 pa_work_types_v wt
1301 WHERE assignment_id = p_assignment_id
1302 AND asgn.project_role_id = prt.project_role_id
1303 AND asgn.project_id = proj.project_id
1304 AND sp.lookup_type(+) = 'STAFFING_PRIORITY_CODE'
1305 AND asgn.staffing_priority_code = sp.lookup_code(+)
1306 AND fcst.lookup_type(+) = 'TP_AMOUNT_TYPE'
1307 AND asgn.fcst_tp_amount_type = fcst.lookup_code(+)
1308 AND asgn.work_type_id = wt.work_type_id;
1309
1310 CURSOR csr_get_override_basis_name (p_override_basis_code IN VARCHAR2) IS
1311 SELECT plks.meaning
1312 FROM pa_lookups plks
1313 WHERE plks.lookup_type = 'CC_MARKUP_BASE_CODE'
1314 AND plks.lookup_code = p_override_basis_code;
1315
1316 --cursor to get attributes of the project to be
1317 --displayed in notifications
1318 CURSOR get_project_info IS
1319 SELECT proj.name,
1320 proj.segment1,
1321 hou.name,
1322 PA_PROJECT_PARTIES_UTILS.GET_PROJECT_MANAGER(proj.project_id),
1323 PA_PROJECT_PARTIES_UTILS.GET_PROJECT_MANAGER_NAME,
1324 PA_PROJECTS_MAINT_UTILS.GET_PRIMARY_CUSTOMER_NAME(proj.project_id)
1325 FROM pa_projects_all proj,
1326 HR_ALL_ORGANIZATION_UNITS HOU
1327 WHERE proj.project_id = p_project_id
1328 AND proj.CARRYING_OUT_ORGANIZATION_ID = HOU.ORGANIZATION_ID;
1329
1330 BEGIN
1331
1332 -- Initialize the Error Stack
1333 PA_DEBUG.init_err_stack('PA_ADVERTISEMENTS_PVT.Start_Adv_Notification_WF');
1334
1335 --Log Message
1336 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1337 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ADVERTISEMENTS_PVT.Start_Adv_Notification_WF'
1338 ,x_msg => 'Beginning of PA_ADVERTISEMENTS_PVT.Start_Adv_Notification_WF'
1339 ,x_log_level => 5);
1340 END IF;
1341
1342 -- Initialize the return status to success
1343 x_return_status := FND_API.G_RET_STS_SUCCESS;
1344
1345 -- Setting thresold value to run the process in background
1346 l_save_threshold := wf_engine.threshold;
1347 wf_engine.threshold := -1;
1348
1349 -- get the requirement and project information to be displayed
1350 -- in the notifications
1351 OPEN get_requirement_info;
1352 FETCH get_requirement_info INTO
1353 l_assignment_name,
1354 l_start_date,
1355 l_end_date,
1356 l_project_organization_name,
1357 l_project_role_name,
1358 l_min_resource_job_level,
1359 l_max_resource_job_level,
1360 l_staffing_priority_name,
1361 l_effort,
1362 l_duration,
1363 l_description,
1364 l_additional_information,
1365 l_revenue_bill_rate,
1366 l_revenue_currency_code,
1367 l_bill_rate_override,
1368 l_bill_rate_curr_override,
1369 l_markup_percent_override,
1370 l_fcst_tp_amount_type_name,
1371 l_tp_rate_override,
1372 l_tp_currency_override,
1373 l_tp_calc_base_code_override,
1374 l_tp_percent_applied_override,
1375 l_work_type_name,
1376 l_transfer_price_rate, -- Added for bug 3051110
1377 l_transfer_pr_rate_curr;
1378 CLOSE get_requirement_info;
1379
1380 OPEN get_project_info;
1381 FETCH get_project_info INTO
1382 l_project_name,
1383 l_project_number,
1384 l_project_organization,
1385 l_project_manager_id,
1386 l_project_manager,
1387 l_project_customer;
1388 CLOSE get_project_info;
1389
1390 IF l_tp_calc_base_code_override IS NOT NULL THEN
1391 open csr_get_override_basis_name(l_tp_calc_base_code_override);
1392 fetch csr_get_override_basis_name into l_override_basis_name;
1393 close csr_get_override_basis_name;
1394 END IF;
1395
1396 -- if wf user name is passed in, set person_id to -999
1397 IF p_wf_user_name IS NOT NULL THEN
1398
1399 SELECT -999
1400 BULK COLLECT INTO l_ntf_recipient_person_id_tbl
1401 FROM pa_project_assignments
1402 WHERE assignment_id = p_assignment_id;
1403
1404 ELSE
1405 l_ntf_recipient_person_id_tbl := p_ntf_recipient_person_id_tbl;
1406 END IF;
1407
1408 -- start one wf process to send notification per recipient
1409 FOR i in l_ntf_recipient_person_id_tbl.FIRST ..l_ntf_recipient_person_id_tbl.LAST LOOP
1410
1411 IF l_ntf_recipient_person_id_tbl(i)=-999 THEN
1412 l_ntf_recipient_user_name := p_wf_user_name;
1413 ELSE
1414
1415 --Getting recepients fnd user name
1416 wf_directory.getusername
1417 (p_orig_system => 'PER'
1418 ,p_orig_system_id => l_ntf_recipient_person_id_tbl(i)
1419 ,p_name => l_ntf_recipient_user_name
1420 ,p_display_name => l_display_name);
1421
1422 END IF;
1423
1424 IF l_ntf_recipient_user_name IS NULL THEN
1425
1426 --dbms_output.put_line('no username for person id '|| l_ntf_recipient_person_id_tbl(i));
1427 -- insert error into audit, User is not valid
1428 PA_ACTION_SET_UTILS.Add_Message(
1429 p_app_short_name => 'PA'
1430 ,p_msg_name => 'PA_ADV_NTF_ERR'
1431 ,p_token1 => 'PERSON_NAME'
1432 ,p_value1 => p_ntf_recipient_name_tbl(i)
1433 );
1434
1435 x_return_status := FND_API.G_RET_STS_ERROR;
1436
1437 ELSE
1438
1439 --dbms_output.put_line('sending notification to '|| l_ntf_recipient_user_name);
1440
1441 -- Create the unique item key to launch WF with
1442 SELECT pa_advertisement_ntf_wf_s.nextval
1443 INTO l_wf_item_key
1444 FROM dual;
1445
1446 -- Create the WF process
1447 wf_engine.CreateProcess
1448 ( ItemType => p_wf_item_type
1449 , ItemKey => l_wf_item_key
1450 , process => p_wf_process );
1451
1452 --Store the item attributes in plsql tables
1453 l_set_text_attr_name_tbl(l_set_text_attr_name_tbl.COUNT+1) := 'NTF_RECIPIENT';
1454 l_set_text_attr_value_tbl(l_set_text_attr_value_tbl.COUNT+1) := l_ntf_recipient_user_name;
1455
1456 l_set_text_attr_name_tbl(l_set_text_attr_name_tbl.COUNT+1) := 'ASSIGNMENT_NAME';
1457 l_set_text_attr_value_tbl(l_set_text_attr_value_tbl.COUNT+1) := l_assignment_name;
1458 l_set_text_attr_name_tbl(l_set_text_attr_name_tbl.COUNT+1) := 'ROLE_NAME';
1459 l_set_text_attr_value_tbl(l_set_text_attr_value_tbl.COUNT+1) := l_project_role_name;
1460 l_set_text_attr_name_tbl(l_set_text_attr_name_tbl.COUNT+1) := 'ORGANIZATION_NAME';
1461 l_set_text_attr_value_tbl(l_set_text_attr_value_tbl.COUNT+1) := l_project_organization_name;
1462 l_set_text_attr_name_tbl(l_set_text_attr_name_tbl.COUNT+1) := 'STAFFING_PRIORITY';
1463 l_set_text_attr_value_tbl(l_set_text_attr_value_tbl.COUNT+1) := l_staffing_priority_name;
1464 l_set_text_attr_name_tbl(l_set_text_attr_name_tbl.COUNT+1) := 'DESCRIPTION';
1465 l_set_text_attr_value_tbl(l_set_text_attr_value_tbl.COUNT+1) := l_description;
1466 l_set_text_attr_name_tbl(l_set_text_attr_name_tbl.COUNT+1) := 'ADDITIONAL_INFORMATION';
1467 l_set_text_attr_value_tbl(l_set_text_attr_value_tbl.COUNT+1) := l_additional_information;
1468 l_set_text_attr_name_tbl(l_set_text_attr_name_tbl.COUNT+1) := 'PROJECT_NAME';
1469 l_set_text_attr_value_tbl(l_set_text_attr_value_tbl.COUNT+1) := l_project_name;
1470 l_set_text_attr_name_tbl(l_set_text_attr_name_tbl.COUNT+1) := 'PROJECT_NUMBER';
1471 l_set_text_attr_value_tbl(l_set_text_attr_value_tbl.COUNT+1) := l_project_number;
1472 l_set_text_attr_name_tbl(l_set_text_attr_name_tbl.COUNT+1) := 'PROJECT_ORGANIZATION_NAME';
1473 l_set_text_attr_value_tbl(l_set_text_attr_value_tbl.COUNT+1) := l_project_organization_name;
1474 l_set_text_attr_name_tbl(l_set_text_attr_name_tbl.COUNT+1) := 'PROJECT_MANAGER';
1475 l_set_text_attr_value_tbl(l_set_text_attr_value_tbl.COUNT+1) := l_project_manager;
1476 l_set_text_attr_name_tbl(l_set_text_attr_name_tbl.COUNT+1) := 'PROJECT_CUSTOMER';
1477 l_set_text_attr_value_tbl(l_set_text_attr_value_tbl.COUNT+1) := l_project_customer;
1478 /* Bug 2529772 - added paPersonId in the link below */
1479 l_requirement_overview_link :=
1480 'JSP:/OA_HTML/OA.jsp?akRegionApplicationId=275'||'&'||'akRegionCode=PA_OPEN_ASMT_DETAILS_LAYOUT'||'&'||'paAssignmentId='||p_assignment_id||'&'||'paPersonId='||l_ntf_recipient_person_id_tbl(i)||'&'||'addBreadCrumb=RP';
1481
1482 l_set_text_attr_name_tbl(l_set_text_attr_name_tbl.COUNT+1) := 'REQUIREMENT_LINK';
1483 l_set_text_attr_value_tbl(l_set_text_attr_value_tbl.COUNT+1) := l_requirement_overview_link;
1484
1485 l_set_num_attr_name_tbl(l_set_num_attr_name_tbl.COUNT+1) := 'MIN_JOB_LEVEL';
1486 l_set_num_attr_value_tbl(l_set_num_attr_value_tbl.COUNT+1) := l_min_resource_job_level;
1487 l_set_num_attr_name_tbl(l_set_num_attr_name_tbl.COUNT+1) := 'MAX_JOB_LEVEL';
1488 l_set_num_attr_value_tbl(l_set_num_attr_value_tbl.COUNT+1) := l_max_resource_job_level;
1489 l_set_num_attr_name_tbl(l_set_num_attr_name_tbl.COUNT+1) := 'EFFORT';
1490 l_set_num_attr_value_tbl(l_set_num_attr_value_tbl.COUNT+1) := l_effort;
1491 l_set_num_attr_name_tbl(l_set_num_attr_name_tbl.COUNT+1) := 'DURATION';
1492 l_set_num_attr_value_tbl(l_set_num_attr_value_tbl.COUNT+1) := l_duration;
1493
1494 l_set_date_attr_name_tbl(l_set_date_attr_name_tbl.COUNT+1) := 'START_DATE';
1495 l_set_date_attr_value_tbl(l_set_date_attr_value_tbl.COUNT+1) := l_start_date;
1496 l_set_date_attr_name_tbl(l_set_date_attr_name_tbl.COUNT+1) := 'END_DATE';
1497 l_set_date_attr_value_tbl(l_set_date_attr_value_tbl.COUNT+1) := l_end_date;
1498
1499 l_set_num_attr_name_tbl(l_set_num_attr_name_tbl.COUNT+1) := 'REVENUE_BILL_RATE';
1500 l_set_num_attr_value_tbl(l_set_num_attr_value_tbl.COUNT+1) := l_revenue_bill_rate;
1501 l_set_num_attr_name_tbl(l_set_num_attr_name_tbl.COUNT+1) := 'BILL_RATE_OVERRIDE';
1502 l_set_num_attr_value_tbl(l_set_num_attr_value_tbl.COUNT+1) := l_bill_rate_override;
1503 l_set_num_attr_name_tbl(l_set_num_attr_name_tbl.COUNT+1) := 'TP_RATE_OVERRIDE';
1504 l_set_num_attr_value_tbl(l_set_num_attr_value_tbl.COUNT+1) := l_tp_rate_override;
1505 l_set_num_attr_name_tbl(l_set_num_attr_name_tbl.COUNT+1) := 'ASSIGNMENT_ID'; -- added for Bug 4777149
1506 l_set_num_attr_value_tbl(l_set_num_attr_value_tbl.COUNT+1) := p_assignment_id; -- added for Bug 4777149
1507
1508
1509 l_set_text_attr_name_tbl(l_set_text_attr_name_tbl.COUNT+1) := 'REVENUE_BILL_RATE_CURR';
1510 l_set_text_attr_value_tbl(l_set_text_attr_value_tbl.COUNT+1) := l_revenue_currency_code;
1511 l_set_text_attr_name_tbl(l_set_text_attr_name_tbl.COUNT+1) := 'BILL_RATE_OVERRIDE_CURR';
1512 l_set_text_attr_value_tbl(l_set_text_attr_value_tbl.COUNT+1) := l_bill_rate_curr_override;
1513 l_set_text_attr_name_tbl(l_set_text_attr_name_tbl.COUNT+1) := 'TP_AMT_TYPE_NAME';
1514 l_set_text_attr_value_tbl(l_set_text_attr_value_tbl.COUNT+1) := l_fcst_tp_amount_type_name;
1515 l_set_text_attr_name_tbl(l_set_text_attr_name_tbl.COUNT+1) := 'TP_RATE_OVERRIDE_CURR';
1516 l_set_text_attr_value_tbl(l_set_text_attr_value_tbl.COUNT+1) := l_tp_currency_override;
1517 l_set_text_attr_name_tbl(l_set_text_attr_name_tbl.COUNT+1) := 'WORK_TYPE_NAME';
1518 l_set_text_attr_value_tbl(l_set_text_attr_value_tbl.COUNT+1) := l_work_type_name;
1519 l_set_text_attr_name_tbl(l_set_text_attr_name_tbl.COUNT+1) := 'OVERRIDE_BASIS_NAME';
1520 l_set_text_attr_value_tbl(l_set_text_attr_value_tbl.COUNT+1) := l_override_basis_name;
1521
1522 /* Added the below code for bug 3051110 */
1523
1524 l_set_num_attr_name_tbl (l_set_num_attr_name_tbl.COUNT+1) := 'TRANSFER_PRICE_RATE';
1525 l_set_num_attr_value_tbl(l_set_num_attr_value_tbl.COUNT+1) := l_transfer_price_rate;
1526 l_set_text_attr_name_tbl(l_set_text_attr_name_tbl.COUNT+1) := 'TRANSFER_PR_RATE_CURR';
1527 l_set_text_attr_value_tbl(l_set_text_attr_value_tbl.COUNT+1) := l_transfer_pr_rate_curr;
1528
1529 IF l_markup_percent_override IS NOT NULL THEN
1530 l_set_text_attr_name_tbl(l_set_text_attr_name_tbl.COUNT+1) := 'MARKUP_PCT_OVERRIDE';
1531 l_set_text_attr_value_tbl(l_set_text_attr_value_tbl.COUNT+1) := to_char(l_markup_percent_override)||'%';
1532 ELSE
1533 l_set_text_attr_name_tbl(l_set_text_attr_name_tbl.COUNT+1) := 'MARKUP_PCT_OVERRIDE';
1534 l_set_text_attr_value_tbl(l_set_text_attr_value_tbl.COUNT+1) := to_char(l_markup_percent_override);
1535 END IF;
1536
1537 IF l_tp_percent_applied_override IS NOT NULL THEN
1538 IF l_override_basis_name IS NOT NULL THEN
1539 l_set_text_attr_name_tbl(l_set_text_attr_name_tbl.COUNT+1) := 'TP_PCT_APPLIED_OVERRIDE';
1540 l_set_text_attr_value_tbl(l_set_text_attr_value_tbl.COUNT+1) :=
1541 ', '||to_char(l_tp_percent_applied_override)||'%';
1542 ELSE
1543 l_set_text_attr_name_tbl(l_set_text_attr_name_tbl.COUNT+1) := 'TP_PCT_APPLIED_OVERRIDE';
1544 l_set_text_attr_value_tbl(l_set_text_attr_value_tbl.COUNT+1) :=
1545 to_char(l_tp_percent_applied_override)||'%';
1546 END IF;
1547 ELSE
1548 l_set_text_attr_name_tbl(l_set_text_attr_name_tbl.COUNT+1) := 'TP_PCT_APPLIED_OVERRIDE';
1549 l_set_text_attr_value_tbl(l_set_text_attr_value_tbl.COUNT+1) :=
1550 to_char(l_tp_percent_applied_override);
1551 END IF;
1552
1553 --SET the item attributes (these attributes were created at design time)
1554 WF_ENGINE.SetItemAttrTextArray(itemtype => p_wf_item_type,
1555 itemkey => l_wf_item_key,
1556 aname => l_set_text_attr_name_tbl,
1557 avalue => l_set_text_attr_value_tbl);
1558
1559 WF_ENGINE.SetItemAttrNumberArray(itemtype => p_wf_item_type,
1560 itemkey => l_wf_item_key,
1561 aname => l_set_num_attr_name_tbl,
1562 avalue => l_set_num_attr_value_tbl);
1563
1564 WF_ENGINE.SetItemAttrDateArray(itemtype => p_wf_item_type,
1565 itemkey => l_wf_item_key,
1566 aname => l_set_date_attr_name_tbl,
1567 avalue => l_set_date_attr_value_tbl);
1568
1569 --Start the workflow process
1570 wf_engine.StartProcess ( itemtype => p_wf_item_type
1571 ,itemkey => l_wf_item_key );
1572
1573 -- Insert into Notifications table
1574 PA_WORKFLOW_UTILS.Insert_WF_Processes
1575 (p_wf_type_code => 'ADVERTISEMENTS_NTF_WF'
1576 ,p_item_type => p_wf_item_type
1577 ,p_item_key => l_wf_item_key
1578 ,p_entity_key1 => to_char(p_project_id)
1579 ,p_entity_key2 => to_char(l_ntf_recipient_person_id_tbl(i))
1580 ,p_description => NULL
1581 ,p_err_code => l_err_code
1582 ,p_err_stage => l_err_stage
1583 ,p_err_stack => l_err_stack );
1584
1585 -- Insert people into audit table if the action is Send Notification
1586 IF p_insert_audit_flag = 'T' AND
1587 (p_action_code = 'ADVERTISEMENT_SEND_NTF_PERSON' OR
1588 p_action_code = 'ADVERTISEMENT_SEND_NTF_ROLE') THEN
1589
1590 l_action_line_audit_rec.reason_code := 'CONDITION_MET';
1591 l_action_line_audit_rec.action_code := p_action_code;
1592 l_action_line_audit_rec.audit_display_attribute := p_ntf_recipient_name_tbl(i);
1593 l_action_line_audit_rec.audit_attribute := l_ntf_recipient_person_id_tbl(i);
1594 l_action_line_audit_rec.reversed_action_set_line_id := NULL;
1595
1596 l_index := PA_ADVERTISEMENTS_PUB.g_action_line_audit_tbl.COUNT;
1597 PA_ADVERTISEMENTS_PUB.g_action_line_audit_tbl(l_index) := l_action_line_audit_rec;
1598
1599 END IF;
1600
1601 END IF; --if username is null
1602
1603 END LOOP;--end i loop
1604
1605 --Setting the original value
1606 wf_engine.threshold := l_save_threshold;
1607 -- 4537865 : Included Exception Block
1608 EXCEPTION
1609 WHEN OTHERS THEN
1610 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1611 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_ADVERTISEMENTS_PVT'
1612 , p_procedure_name => 'Start_Adv_Notification_WF'
1613 , p_error_text => SUBSTRB(SQLERRM,1,240));
1614 RAISE ;
1615 END Start_Adv_Notification_WF;
1616
1617 ----------------------------------------------------------------------
1618 -- Procedure
1619 -- Perform Update Staffing Priority
1620 --
1621 -- Purpose
1622 -- Update the staffing priority of the requirement.
1623 ----------------------------------------------------------------------
1624 PROCEDURE Update_Staffing_Priority (
1625 p_action_set_line_id IN pa_action_set_lines.action_set_line_id%TYPE
1626 , p_object_id IN pa_action_sets.object_id%TYPE
1627 , p_action_code IN pa_action_set_lines.action_code%TYPE
1628 , p_action_status_code IN pa_action_set_lines.status_code%TYPE
1629 , p_staffing_priority_code IN pa_project_assignments.staffing_priority_code%TYPE
1630 , p_record_version_number IN pa_project_assignments.record_version_number%TYPE
1631 , p_insert_audit_flag IN VARCHAR2 := 'T'
1632 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1633 ) IS
1634
1635 l_staffing_priority_name pa_lookups.meaning%TYPE;
1636 l_action_line_audit_rec pa_action_set_utils.insert_audit_lines_rec_type;
1637 l_index NUMBER;
1638 l_encoded_message_text VARCHAR2(2000);
1639 l_msg_index_out NUMBER;
1640 l_update_sp_display_attribute VARCHAR2(80);
1641 BEGIN
1642
1643 --dbms_output.put_line('update staffing priority');
1644
1645 -- Initialize the Error Stack
1646 PA_DEBUG.init_err_stack('PA_ADVERTISEMENTS_PVT.Update_Staffing_Priority');
1647
1648 --Log Message
1649 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1650 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ADVERTISEMENTS_PVT.Update_Staffing_Priority'
1651 ,x_msg => 'Beginning of PA_ADVERTISEMENTS_PVT.Update_Staffing_Priority'
1652 ,x_log_level => 5);
1653 END IF;
1654
1655 -- Initialize the return status to success
1656 x_return_status := FND_API.G_RET_STS_SUCCESS;
1657
1658 --
1659 -- Handle PENDING action line only as REVERSE_PENDING and UPDATE_PENDING
1660 -- lines can be handled generically by the action set model.
1661 --
1662 IF p_action_status_code = 'PENDING' THEN
1663
1664 SELECT meaning INTO l_staffing_priority_name
1665 FROM pa_lookups
1666 WHERE lookup_type = 'STAFFING_PRIORITY_CODE'
1667 AND lookup_code = p_staffing_priority_code;
1668
1669 -- Update the staffing priority code
1670 PA_PROJECT_ASSIGNMENTS_PKG.Update_Row
1671 ( p_assignment_id => p_object_id
1672 ,p_record_version_number => p_record_version_number
1673 ,p_staffing_priority_code => p_staffing_priority_code
1674 ,x_return_status => x_return_status );
1675
1676 -- Insert into audit table
1677 IF x_return_status = FND_API.G_RET_STS_SUCCESS AND p_insert_audit_flag = 'T' THEN
1678
1679 -- insert into into the global audit record
1680 l_action_line_audit_rec.reason_code := 'CONDITION_MET';
1681 l_action_line_audit_rec.action_code := p_action_code;
1682 l_action_line_audit_rec.audit_display_attribute := l_staffing_priority_name;
1683 l_action_line_audit_rec.audit_attribute := p_staffing_priority_code;
1684 l_action_line_audit_rec.reversed_action_set_line_id := NULL;
1685 l_index := PA_ADVERTISEMENTS_PUB.g_action_line_audit_tbl.COUNT;
1686 PA_ADVERTISEMENTS_PUB.g_action_line_audit_tbl(l_index) := l_action_line_audit_rec;
1687
1688 ELSE
1689
1690 -- ERROR: Insert error into the audit table
1691 IF FND_MSG_PUB.Count_Msg > 0 THEN
1692 IF p_insert_audit_flag = 'T' THEN
1693
1694 FND_MSG_PUB.get (
1695 p_encoded => FND_API.G_TRUE
1696 ,p_msg_index => FND_MSG_PUB.Count_Msg
1697 ,p_data => l_encoded_message_text
1698 ,p_msg_index_out => l_msg_index_out);
1699
1700 l_action_line_audit_rec.reason_code := 'CONDITION_MET';
1701 l_action_line_audit_rec.action_code := p_action_code;
1702 l_action_line_audit_rec.encoded_error_message := l_encoded_message_text;
1703 l_index := PA_ADVERTISEMENTS_PUB.g_action_line_audit_tbl.COUNT;
1704 PA_ADVERTISEMENTS_PUB.g_action_line_audit_tbl(l_index) := l_action_line_audit_rec;
1705 END IF; -- IF p_insert_audit_flag = 'T'
1706 x_return_status := FND_API.G_RET_STS_ERROR;
1707 END IF; -- IF FND_MSG_PUB.Count_Msg > 0
1708
1709 END IF;
1710
1711
1712 ELSE -- action status is REVERSE PENDING or UPDATE PENDING
1713
1714 IF p_insert_audit_flag = 'T' THEN
1715
1716 -- Get the Audit Display Attribute
1717 SELECT meaning INTO l_update_sp_display_attribute
1718 FROM pa_lookups
1719 WHERE lookup_type = 'ADVERTISEMENT'
1720 AND lookup_code = 'NO_ACTION_PERFORMED';
1721
1722 -- insert into into the global audit record
1723 IF p_action_status_code = 'REVERSE_PENDING' THEN
1724 l_action_line_audit_rec.reason_code := 'DELETED';
1725 ELSE
1726 l_action_line_audit_rec.reason_code := 'UPDATED';
1727 END IF;
1728 l_action_line_audit_rec.action_code := p_action_code;
1729 l_action_line_audit_rec.audit_display_attribute := l_update_sp_display_attribute;
1730 l_action_line_audit_rec.audit_attribute := p_staffing_priority_code;
1731 l_action_line_audit_rec.reversed_action_set_line_id := p_action_set_line_id;
1732 l_index := PA_ADVERTISEMENTS_PUB.g_action_line_audit_tbl.COUNT;
1733 PA_ADVERTISEMENTS_PUB.g_action_line_audit_tbl(l_index) := l_action_line_audit_rec;
1734
1735 END IF;
1736
1737 END IF; -- action status
1738 -- 4537865 : Included Exception Block
1739 EXCEPTION
1740 WHEN OTHERS THEN
1741 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1742 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_ADVERTISEMENTS_PVT'
1743 , p_procedure_name => 'Update_Staffing_Priority'
1744 , p_error_text => SUBSTRB(SQLERRM,1,240));
1745 RAISE ;
1746 END Update_Staffing_Priority;
1747
1748
1749 ----------------------------------------------------------------------
1750 -- Procedure
1751 -- Perform Remove Advertisement
1752 --
1753 -- Purpose
1754 -- Remove the visibility or advertisement of the requirement.
1755 ----------------------------------------------------------------------
1756 PROCEDURE Remove_Advertisement (
1757 p_action_set_line_id IN pa_action_set_lines.action_set_line_id%TYPE
1758 , p_object_id IN pa_action_sets.object_id%TYPE
1759 , p_action_code IN pa_action_set_lines.action_code%TYPE
1760 , p_action_status_code IN pa_action_set_lines.status_code%TYPE
1761 , p_project_id IN pa_projects_all.project_id%TYPE
1762 , p_insert_audit_flag IN VARCHAR2 := 'T'
1763 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1764 ) IS
1765
1766 TYPE number_tbl IS TABLE OF NUMBER
1767 INDEX BY BINARY_INTEGER;
1768 TYPE varchar_tbl IS TABLE OF VARCHAR2(150)
1769 INDEX BY BINARY_INTEGER;
1770
1771 l_return_status VARCHAR2(1);
1772 l_action_set_line_id_tbl pa_action_set_utils.number_tbl_type;
1773 l_action_code_tbl varchar_tbl;
1774 l_audit_attribute_tbl varchar_tbl;
1775 l_display_audit_attribute_tbl varchar_tbl;
1776 l_line_status_tbl pa_action_set_utils.varchar_tbl_type;
1777 l_action_set_line_id_tbl2 pa_action_set_utils.number_tbl_type;
1778 l_line_status_tbl2 pa_action_set_utils.varchar_tbl_type;
1779
1780 i NUMBER;
1781 l_action_status_code VARCHAR2(30);
1782 l_action_set_id NUMBER;
1783 l_object_id NUMBER;
1784 l_object_type VARCHAR2(30);
1785 l_action_set_type_code VARCHAR2(30);
1786 l_project_id NUMBER;
1787 l_record_version_number NUMBER;
1788 l_action_line_audit_rec pa_action_set_utils.insert_audit_lines_rec_type;
1789 l_index NUMBER;
1790 l_encoded_message_text VARCHAR2(2000);
1791 l_msg_index_out NUMBER;
1792 l_audit_reason_code VARCHAR2(30);
1793 l_perform_return_status VARCHAR2(1);
1794 l_audit_action_code VARCHAR2(30);
1795 l_update_sp_display_attribute VARCHAR2(80);
1796 l_remove_adv_display_attribute VARCHAR2(80);
1797
1798 -- cursor to get all the currently active audit lines
1799 CURSOR get_all_active_audit_lines IS
1800 SELECT action_set_line_id, action_code, audit_attribute, audit_display_attribute, 'REVERSED'
1801 FROM pa_action_set_line_aud
1802 WHERE object_id = p_object_id
1803 AND object_type = 'OPEN_ASSIGNMENT'
1804 AND action_set_type_code = 'ADVERTISEMENT'
1805 AND active_flag = 'Y'
1806 AND reversed_action_set_line_id is null
1807 AND encoded_error_message is null;
1808
1809 -- cursor to get all the currently active audit lines with/without error
1810 CURSOR get_active_audit_lines_error IS
1811 SELECT action_set_line_id, 'REVERSED'
1812 FROM pa_action_set_line_aud
1813 WHERE object_id = p_object_id
1814 AND object_type = 'OPEN_ASSIGNMENT'
1815 AND action_set_type_code = 'ADVERTISEMENT'
1816 AND active_flag = 'Y'
1817 AND reversed_action_set_line_id is null
1818 GROUP BY action_set_line_id; -- CH2M Performance Bug fix: 2768530
1819
1820 -- cursor to get all the action lines that were reversed due to the execution
1821 -- of this Remove Advertisement line and are not deleted
1822 CURSOR get_all_reversed_lines IS
1823 SELECT asl.action_set_line_id, asl.action_code, asa.audit_attribute, asa.audit_display_attribute, 'COMPLETE'
1824 FROM pa_action_set_line_aud asa,
1825 pa_action_set_lines asl,
1826 pa_action_set_line_cond aslc
1827 WHERE asa.object_id = p_object_id
1828 AND asa.object_type = 'OPEN_ASSIGNMENT'
1829 AND asa.action_set_type_code = 'ADVERTISEMENT'
1830 AND asa.action_set_line_id = p_action_set_line_id
1831 AND asa.reversed_action_set_line_id IS NOT NULL
1832 AND asl.action_set_line_id = asa.reversed_action_set_line_id
1833 AND nvl(asl.line_deleted_flag, 'N') = 'N'
1834 AND encoded_error_message is null
1835 AND aslc.action_set_line_id = asl.action_set_line_id
1836 AND aslc.condition_date <= sysdate
1837 AND asl.action_set_line_id <> p_action_set_line_id;
1838
1839 BEGIN
1840
1841 --dbms_output.put_line('begin of PA_ADVERTISEMENTS_PVT.Remove_Advertisement');
1842
1843 -- Initialize the Error Stack
1844 PA_DEBUG.init_err_stack('PA_ADVERTISEMENTS_PVT.Update_Staffing_Priority');
1845
1846 --Log Message
1847 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1848 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ADVERTISEMENTS_PVT.Remove_Advertisement'
1849 ,x_msg => 'Beginning of PA_ADVERTISEMENTS_PVT.Remove_Advertisement'
1850 ,x_log_level => 5);
1851 END IF;
1852
1853 -- Initialize the return status to success
1854 x_return_status := FND_API.G_RET_STS_SUCCESS;
1855
1856 --
1857 -- CASE 1: the action status code is PENDING
1858 IF p_action_status_code = 'PENDING' THEN
1859
1860 -- insert the Remove Advertisement record into global audit rec
1861 IF p_insert_audit_flag = 'T' THEN
1862
1863 -- insert into into the global audit record
1864 l_action_line_audit_rec.reason_code := 'CONDITION_MET';
1865 l_action_line_audit_rec.action_code := p_action_code;
1866 l_action_line_audit_rec.audit_display_attribute := null;
1867 l_action_line_audit_rec.audit_attribute := null;
1868 l_action_line_audit_rec.reversed_action_set_line_id := NULL;
1869 l_index := PA_ADVERTISEMENTS_PUB.g_action_line_audit_tbl.COUNT;
1870 PA_ADVERTISEMENTS_PUB.g_action_line_audit_tbl(l_index) := l_action_line_audit_rec;
1871
1872 END IF;
1873
1874 -- get all the currently active audit lines
1875 -- these lines need to be reversed
1876 OPEN get_all_active_audit_lines;
1877 FETCH get_all_active_audit_lines
1878 BULK COLLECT INTO l_action_set_line_id_tbl,
1879 l_action_code_tbl,
1880 l_audit_attribute_tbl,
1881 l_display_audit_attribute_tbl,
1882 l_line_status_tbl;
1883 CLOSE get_all_active_audit_lines;
1884
1885 --dbms_output.put_line('l_action_set_line_id_tbl.COUNT:'||l_action_set_line_id_tbl.COUNT);
1886
1887 -- get all the currently active audit lines with/without error
1888 -- not all these lines need to be reversed
1889 -- but the line statuses and active flags need to be changed
1890 -- get all the currently active audit lines
1891 -- these lines need to be reversed
1892 OPEN get_active_audit_lines_error;
1893 FETCH get_active_audit_lines_error
1894 BULK COLLECT INTO l_action_set_line_id_tbl2,
1895 -- l_action_code_tbl2,
1896 -- l_audit_attribute_tbl2,
1897 -- l_display_audit_attribute_tbl2,
1898 l_line_status_tbl2;
1899 CLOSE get_active_audit_lines_error;
1900
1901 IF l_action_set_line_id_tbl2.COUNT > 0 THEN
1902
1903 -- Update the action line status to REVERSED
1904 PA_ACTION_SETS_PVT.Bulk_Update_Line_Status(
1905 p_action_set_line_id_tbl => l_action_set_line_id_tbl2
1906 ,p_line_status_tbl => l_line_status_tbl2
1907 ,x_return_status => l_return_status
1908 );
1909 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1910 x_return_status := FND_API.G_RET_STS_ERROR;
1911 END IF;
1912
1913 -- Update the active flag to 'N' in the audit records
1914 FORALL i IN l_action_set_line_id_tbl2.FIRST ..l_action_set_line_id_tbl2.LAST
1915 UPDATE pa_action_set_line_aud
1916 SET active_flag = 'N'
1917 WHERE action_set_line_id = l_action_set_line_id_tbl2(i);
1918
1919 -- set the action status code to REVERSE_PENDING
1920 -- to reverse the child records
1921 l_action_status_code := 'REVERSE_PENDING';
1922 l_audit_reason_code := 'ADVERTISEMENT_REMOVED';
1923 END IF; -- if l_action_set_line_id_tbl2.COUNT > 0
1924
1925 --
1926 -- CASE 2: the action status code is REVERSE or UPDATE PENDING
1927 --
1928 ELSE
1929
1930 -- get all the action lines that were reversed due to the execution
1931 -- of this Remove Advertisement line and are not deleted
1932 OPEN get_all_reversed_lines;
1933 FETCH get_all_reversed_lines
1934 BULK COLLECT INTO l_action_set_line_id_tbl,
1935 l_action_code_tbl,
1936 l_audit_attribute_tbl,
1937 l_display_audit_attribute_tbl,
1938 l_line_status_tbl;
1939 CLOSE get_all_reversed_lines;
1940
1941 --dbms_output.put_line('l_action_set_line_id_tbl.COUNT:'||l_action_set_line_id_tbl.COUNT);
1942
1943 IF l_action_set_line_id_tbl.COUNT = 0 THEN
1944
1945 -- Get the Audit Display Attribute
1946 SELECT meaning INTO l_remove_adv_display_attribute
1947 FROM pa_lookups
1948 WHERE lookup_type = 'ADVERTISEMENT'
1949 AND lookup_code = 'NO_ACTION_PERFORMED';
1950
1951 ELSE
1952 l_remove_adv_display_attribute := NULL;
1953 END IF;
1954
1955 -- insert the Remove Advertisement record into global audit rec
1956 IF p_insert_audit_flag = 'T' THEN
1957
1958 -- insert into into the global audit record
1959 IF p_action_status_code = 'REVERSE_PENDING' THEN
1960 l_action_line_audit_rec.reason_code := 'DELETED';
1961 ELSE
1962 l_action_line_audit_rec.reason_code := 'UPDATED';
1963 END IF;
1964 l_action_line_audit_rec.action_code := p_action_code;
1965 l_action_line_audit_rec.audit_display_attribute := l_remove_adv_display_attribute;
1966 l_action_line_audit_rec.audit_attribute := null;
1967 l_action_line_audit_rec.reversed_action_set_line_id := p_action_set_line_id;
1968 l_index := PA_ADVERTISEMENTS_PUB.g_action_line_audit_tbl.COUNT;
1969 PA_ADVERTISEMENTS_PUB.g_action_line_audit_tbl(l_index) := l_action_line_audit_rec;
1970
1971 END IF;
1972
1973 IF l_action_set_line_id_tbl.COUNT > 0 THEN
1974
1975 -- update the action line status to COMPLETE
1976 PA_ACTION_SETS_PVT.Bulk_Update_Line_Status(
1977 p_action_set_line_id_tbl => l_action_set_line_id_tbl
1978 ,p_line_status_tbl => l_line_status_tbl
1979 ,x_return_status => l_return_status
1980 );
1981 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1982 x_return_status := FND_API.G_RET_STS_ERROR;
1983 END IF;
1984
1985 -- set the action status code to PENDING
1986 -- to re-execute the child records
1987 l_action_status_code := 'PENDING';
1988 l_audit_reason_code := 'ADVERTISEMENT_REINSTATED';
1989
1990 END IF; -- IF l_action_set_line_id_tbl.COUNT > 0 T
1991
1992 END IF; -- action line status status
1993
1994 --
1995 -- Re-execute or reverse the child actions
1996 --
1997 IF l_action_set_line_id_tbl.COUNT > 0 THEN
1998
1999 -- get object information
2000 SELECT project_id, record_version_number
2001 INTO l_project_id, l_record_version_number
2002 FROM pa_project_assignments
2003 WHERE assignment_id = p_object_id;
2004
2005 -- handle send notifications, send email and update staffing priority
2006 FOR i IN l_action_set_line_id_tbl.FIRST .. l_action_set_line_id_tbl.LAST LOOP
2007
2008 l_perform_return_status := FND_API.G_RET_STS_SUCCESS;
2009 l_audit_action_code := NULL;
2010 --dbms_output.put_line('l_action_set_line_id: '||l_action_set_line_id_tbl(i));
2011
2012 IF l_action_code_tbl(i) = 'ADVERTISEMENT_SEND_EMAIL' OR
2013 l_action_code_tbl(i) = 'REVERSE_SEND_EMAIL' THEN
2014
2015 Send_Email(
2016 p_action_set_line_id => p_action_set_line_id
2017 , p_object_id => p_object_id
2018 , p_action_code => 'ADVERTISEMENT_SEND_EMAIL'
2019 , p_action_status_code => l_action_status_code
2020 , p_project_id => l_project_id
2021 , p_email_address => l_audit_attribute_tbl(i)
2022 , p_insert_audit_flag => 'F'
2023 , x_return_status => l_return_status
2024
2025 );
2026 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2027 x_return_status := FND_API.G_RET_STS_ERROR;
2028 l_perform_return_status := FND_API.G_RET_STS_ERROR;
2029 END IF;
2030
2031 -- Store difference action code in the audit table in cases
2032 -- of Cancel Advertisement and Reverse Cancel Advertisement
2033 IF p_action_status_code = 'PENDING' THEN
2034 l_audit_action_code := 'REVERSE_SEND_EMAIL';
2035 ELSE
2036 l_audit_action_code := 'ADVERTISEMENT_SEND_EMAIL';
2037 END IF;
2038
2039 ELSIF l_action_code_tbl(i) = 'ADVERTISEMENT_SEND_NTF_PERSON' OR
2040 l_action_code_tbl(i) = 'REVERSE_SEND_NTF_PERSON' THEN
2041
2042 Send_Notification(
2043 p_action_set_line_id => p_action_set_line_id
2044 , p_object_id => p_object_id
2045 , p_action_code => 'ADVERTISEMENT_SEND_NTF_PERSON'
2046 , p_action_status_code => l_action_status_code
2047 , p_method => 'PERSON'
2048 , p_person_id => to_number(l_audit_attribute_tbl(i))
2049 , p_project_id => l_project_id
2050 , p_project_role_id => null
2051 , p_insert_audit_flag => 'F'
2052 , x_return_status => l_return_status -- Changed from x_return_status to l_return_status : 4537865
2053
2054 );
2055 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2056 x_return_status := FND_API.G_RET_STS_ERROR;
2057 l_perform_return_status := FND_API.G_RET_STS_ERROR;
2058 END IF;
2059
2060 -- Store difference action code in the audit table in cases
2061 -- of Cancel Advertisement and Reverse Cancel Advertisement
2062 IF p_action_status_code = 'PENDING' THEN
2063 l_audit_action_code := 'REVERSE_SEND_NTF_PERSON';
2064 ELSE
2065 l_audit_action_code := 'ADVERTISEMENT_SEND_NTF_PERSON';
2066 END IF;
2067
2068 ELSIF l_action_code_tbl(i) = 'ADVERTISEMENT_SEND_NTF_ROLE' OR
2069 l_action_code_tbl(i) = 'REVERSE_SEND_NTF_ROLE' THEN
2070
2071 Send_Notification(
2072 p_action_set_line_id => p_action_set_line_id
2073 , p_object_id => p_object_id
2074 , p_action_code => 'ADVERTISEMENT_SEND_NTF_ROLE'
2075 , p_action_status_code => l_action_status_code
2076 , p_method => 'PERSON'
2077 , p_person_id => to_number(l_audit_attribute_tbl(i))
2078 , p_project_id => l_project_id
2079 , p_project_role_id => null
2080 , p_insert_audit_flag => 'F'
2081 , x_return_status => l_return_status -- Changed from x_return_status to l_return_status : 4537865
2082
2083 );
2084 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2085 x_return_status := FND_API.G_RET_STS_ERROR;
2086 l_perform_return_status := FND_API.G_RET_STS_ERROR;
2087 END IF;
2088
2089 -- Store difference action code in the audit table in cases
2090 -- of Cancel Advertisement and Reverse Cancel Advertisement
2091 IF p_action_status_code = 'PENDING' THEN
2092 l_audit_action_code := 'REVERSE_SEND_NTF_ROLE';
2093 ELSE
2094 l_audit_action_code := 'ADVERTISEMENT_SEND_NTF_ROLE';
2095 END IF;
2096
2097
2098 ELSIF l_action_code_tbl(i) = 'ADVERTISEMENT_UPDATE_SP' OR
2099 l_action_code_tbl(i) = 'REVERSE_UPDATE_SP' THEN
2100
2101 Update_Staffing_Priority(
2102 p_action_set_line_id => p_action_set_line_id
2103 , p_object_id => p_object_id
2104 , p_action_code => 'ADVERTISEMENT_UPDATE_SP'
2105 , p_action_status_code => l_action_status_code
2106 , p_staffing_priority_code => l_audit_attribute_tbl(i)
2107 , p_record_version_number => l_record_version_number
2108 , p_insert_audit_flag => 'F'
2109 , x_return_status => l_return_status -- Changed from x_return_status to l_return_status : 4537865
2110 );
2111 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2112 x_return_status := FND_API.G_RET_STS_ERROR;
2113 l_perform_return_status := FND_API.G_RET_STS_ERROR;
2114 END IF;
2115
2116 -- Store difference action code in the audit table in cases
2117 -- of Cancel Advertisement and Reverse Cancel Advertisement
2118 IF p_action_status_code = 'PENDING' THEN
2119 l_audit_action_code := 'REVERSE_UPDATE_SP';
2120
2121 -- Get the Audit Display Attribute
2122 SELECT meaning INTO l_update_sp_display_attribute
2123 FROM pa_lookups
2124 WHERE lookup_type = 'ADVERTISEMENT'
2125 AND lookup_code = 'NO_ACTION_PERFORMED';
2126
2127 ELSE
2128 l_audit_action_code := 'ADVERTISEMENT_UPDATE_SP';
2129 END IF;
2130
2131 ELSIF l_action_code_tbl(i) = 'ADVERTISEMENT_PUB_TO_ALL' OR
2132 l_action_code_tbl(i) = 'REVERSE_PUB_TO_ALL' THEN
2133
2134 -- Store difference action code in the audit table in cases
2135 -- of Cancel Advertisement and Reverse Cancel Advertisement
2136 IF p_action_status_code = 'PENDING' THEN
2137 l_audit_action_code := 'REVERSE_PUB_TO_ALL';
2138 ELSE
2139 l_audit_action_code := 'ADVERTISEMENT_PUB_TO_ALL';
2140 END IF;
2141
2142 ELSIF l_action_code_tbl(i) = 'ADVERTISEMENT_PUB_TO_START_ORG' OR
2143 l_action_code_tbl(i) = 'REVERSE_PUB_TO_START_ORG' THEN
2144
2145 -- Store difference action code in the audit table in cases
2146 -- of Cancel Advertisement and Reverse Cancel Advertisement
2147 IF p_action_status_code = 'PENDING' THEN
2148 l_audit_action_code := 'REVERSE_PUB_TO_START_ORG';
2149 ELSE
2150 l_audit_action_code := 'ADVERTISEMENT_PUB_TO_START_ORG';
2151 END IF;
2152
2153 ELSIF l_action_code_tbl(i) = 'ADVERTISEMENT_PUB_TO_SM' OR
2154 l_action_code_tbl(i) = 'REVERSE_PUB_TO_SM' THEN
2155
2156 -- Store difference action code in the audit table in cases
2157 -- of Cancel Advertisement and Reverse Cancel Advertisement
2158 IF p_action_status_code = 'PENDING' THEN
2159 l_audit_action_code := 'REVERSE_PUB_TO_SM';
2160 ELSE
2161 l_audit_action_code := 'ADVERTISEMENT_PUB_TO_SM';
2162 END IF;
2163
2164 ELSIF l_action_code_tbl(i) = 'ADVERTISEMENT_ESC_TO_NEXT_LVL' OR
2165 l_action_code_tbl(i) = 'REVERSE_ESC_TO_NEXT_LVL' THEN
2166
2167 -- Store difference action code in the audit table in cases
2168 -- of Cancel Advertisement and Reverse Cancel Advertisement
2169 IF p_action_status_code = 'PENDING' THEN
2170 l_audit_action_code := 'REVERSE_ESC_TO_NEXT_LVL';
2171 ELSE
2172 l_audit_action_code := 'ADVERTISEMENT_ESC_TO_NEXT_LVL';
2173 END IF;
2174
2175 ELSIF l_action_code_tbl(i) = 'ADVERTISEMENT_REMOVE_ADV' OR
2176 l_action_code_tbl(i) = 'REVERSE_REMOVE_ADV' THEN
2177
2178 -- Store difference action code in the audit table in cases
2179 -- of Cancel Advertisement and Reverse Cancel Advertisement
2180 IF p_action_status_code = 'PENDING' THEN
2181 l_audit_action_code := 'REVERSE_REMOVE_ADV';
2182 ELSE
2183 l_audit_action_code := 'ADVERTISEMENT_REMOVE_ADV';
2184 END IF;
2185
2186 END IF; -- if action_code = ..
2187
2188 -- Insert into into the global audit record
2189 IF p_insert_audit_flag = 'T' AND l_perform_return_status=FND_API.G_RET_STS_SUCCESS THEN
2190
2191 l_action_line_audit_rec.reason_code := l_audit_reason_code;
2192 l_action_line_audit_rec.action_code := l_audit_action_code;
2193 l_action_line_audit_rec.audit_attribute := l_audit_attribute_tbl(i);
2194 l_action_line_audit_rec.audit_display_attribute := l_display_audit_attribute_tbl(i);
2195
2196 IF l_action_status_code = 'REVERSE_PENDING' THEN
2197 l_action_line_audit_rec.reversed_action_set_line_id := l_action_set_line_id_tbl(i);
2198 IF l_audit_action_code = 'ADVERTISEMENT_UPDATE_SP' OR
2199 l_audit_action_code = 'REVERSE_UPDATE_SP' THEN
2200 l_action_line_audit_rec.audit_display_attribute := l_update_sp_display_attribute;
2201 END IF;
2202 ELSE
2203 l_action_line_audit_rec.reversed_action_set_line_id := NULL;
2204 END IF;
2205
2206 l_index := PA_ADVERTISEMENTS_PUB.g_action_line_audit_tbl.COUNT;
2207 PA_ADVERTISEMENTS_PUB.g_action_line_audit_tbl(l_index) := l_action_line_audit_rec;
2208
2209 ELSIF p_insert_audit_flag = 'T' AND l_perform_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2210
2211 FND_MSG_PUB.get (
2212 p_encoded => FND_API.G_TRUE
2213 ,p_msg_index => FND_MSG_PUB.Count_Msg
2214 ,p_data => l_encoded_message_text
2215 ,p_msg_index_out => l_msg_index_out);
2216
2217 l_action_line_audit_rec.reason_code := l_audit_reason_code;
2218 l_action_line_audit_rec.action_code := l_audit_action_code;
2219 l_action_line_audit_rec.encoded_error_message := l_encoded_message_text;
2220 IF l_action_status_code = 'REVERSE_PENDING' THEN
2221 l_action_line_audit_rec.reversed_action_set_line_id := l_action_set_line_id_tbl(i);
2222 END IF;
2223 l_index := PA_ADVERTISEMENTS_PUB.g_action_line_audit_tbl.COUNT;
2224 PA_ADVERTISEMENTS_PUB.g_action_line_audit_tbl(l_index) := l_action_line_audit_rec;
2225
2226 END IF; -- if p_insert_audit_flag = 'T'
2227
2228 END LOOP;
2229
2230 END IF; -- if l_action_set_line_id_tbl.COUNT > 0
2231
2232 IF FND_MSG_PUB.Count_Msg > 0 THEN
2233 x_return_status := FND_API.G_RET_STS_ERROR;
2234 END IF;
2235
2236 -- 4537865 : Included Exception Block
2237 EXCEPTION
2238 WHEN OTHERS THEN
2239 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2240 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_ADVERTISEMENTS_PVT'
2241 , p_procedure_name => 'Remove_Advertisement'
2242 , p_error_text => SUBSTRB(SQLERRM,1,240));
2243 RAISE ;
2244 END Remove_Advertisement;
2245
2246 -- Start changes for Bug 4777149
2247 ----------------------------------------------------------------------
2248 -- Procedure
2249 -- Perform Check Assignment is in Open Status
2250 --
2251 -- Purpose
2252 -- Check if assignment is in open status before sending the advertisement mail.
2253 ----------------------------------------------------------------------
2254 PROCEDURE check_assignment_open(
2255 itemtype IN VARCHAR2
2256 , itemkey IN VARCHAR2
2257 , actid IN NUMBER
2258 , funcmode IN VARCHAR2
2259 , resultout OUT NOCOPY VARCHAR2 --NOCOPY required for OUT and IN/OUT parameters
2260 ) IS
2261 v_dummy varchar2(1);
2262 v_assig_id pa_project_assignments.assignment_id%type;
2263
2264 BEGIN
2265
2266 v_assig_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
2267 itemkey => itemkey,
2268 aname => 'ASSIGNMENT_ID' );
2269
2270 select 'Y' into v_dummy from pa_project_assignments
2271 where assignment_id = v_assig_id
2272 and STATUS_CODE in
2273 (select PROJECT_STATUS_CODE from pa_project_statuses
2274 where PROJECT_SYSTEM_STATUS_CODE = 'OPEN_ASGMT');
2275
2276 resultout := wf_engine.eng_completed||':'||'S';
2277
2278 Exception
2279 when others then
2280 resultout := wf_engine.eng_completed||':'||'F';
2281 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_ADVERTISEMENTS_PVT'
2282 , p_procedure_name => 'Check_Assignment_Open'
2283 , p_error_text => SUBSTRB(SQLERRM,1,240));
2284 --RAISE ; // commented for 7134435
2285 END check_assignment_open;
2286 -- End changes for Bug 4777149
2287
2288 END PA_ADVERTISEMENTS_PVT;
2289