[Home] [Help]
PACKAGE BODY: APPS.PA_ADVERTISEMENTS_PVT
Source
1 PACKAGE BODY PA_ADVERTISEMENTS_PVT AS
2 --$Header: PARAVPVB.pls 120.9.12010000.2 2008/08/22 16:12:15 mumohan 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 -- End of Performance Fix 4898314 SQL ID 14906391
1036
1037 --cursor to get person's name
1038 -- Commented for Performance Fix 4898314 SQL ID 14906422
1039 -- CURSOR get_person_name IS
1040 -- SELECT resource_source_id, resource_name
1041 -- FROM pa_c_resource_v
1042 -- WHERE resource_source_id = p_person_id;
1043
1044 -- Start of Performance Fix 4898314 SQL ID 14906422
1045 CURSOR get_person_name IS
1046 SELECT per.person_id resource_source_id, per.full_name resource_name
1047 FROM per_people_f per
1048 WHERE per.person_id = p_person_id
1049 AND (per.employee_number IS NOT NULL OR per.npw_number IS NOT NULL)
1050 AND (per.current_employee_flag = 'Y' OR per.current_npw_flag = 'Y')
1051 AND trunc(sysdate) BETWEEN per.effective_start_date
1052 AND per.effective_end_date;
1053 -- End of Performance Fix 4898314 SQL ID 14906422
1054
1055 l_ntf_recipient_person_id_tbl system.pa_num_tbl_type;
1056 l_ntf_recipient_name_tbl system.pa_varchar2_240_tbl_type;
1057 l_return_status VARCHAR2(1);
1058 l_wf_process VARCHAR2(30);
1059 l_encoded_message_text VARCHAR2(2000);
1060 l_msg_index_out NUMBER;
1061 l_action_line_audit_rec pa_action_set_utils.insert_audit_lines_rec_type;
1062 l_index NUMBER;
1063 l_action_status_code VARCHAR2(30);
1064
1065 BEGIN
1066
1067 --dbms_output.put_line('PA_ADVERTISEMENTS_PVT.send notification');
1068
1069 -- Initialize the Error Stack
1070 PA_DEBUG.init_err_stack('PA_ADVERTISEMENTS_PVT.Send_Notification');
1071
1072 --Log Message
1073 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1074 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ADVERTISEMENTS_PVT.Send_Notification'
1075 ,x_msg => 'Beginning of PA_ADVERTISEMENTS_PVT.Send_Notification'
1076 ,x_log_level => 5);
1077 END IF;
1078
1079 -- Initialize the return status to success
1080 x_return_status := FND_API.G_RET_STS_SUCCESS;
1081
1082 -- get the person_id of the notification recipients
1083 IF p_method = 'PROJECT_ROLE' THEN
1084
1085 OPEN get_person_on_project_role;
1086 FETCH get_person_on_project_role
1087 BULK COLLECT INTO l_ntf_recipient_person_id_tbl, l_ntf_recipient_name_tbl;
1088 CLOSE get_person_on_project_role;
1089
1090 ELSE
1091
1092 OPEN get_person_name;
1093 FETCH get_person_name
1094 BULK COLLECT INTO l_ntf_recipient_person_id_tbl, l_ntf_recipient_name_tbl;
1095 CLOSE get_person_name;
1096
1097 END IF;
1098
1099 -- ERROR: Insert error into the stack when no one is found
1100 IF l_ntf_recipient_person_id_tbl.COUNT = 0 THEN
1101
1102 IF p_action_code = 'ADVERTISEMENT_SEND_NTF_PERSON' THEN
1103
1104 -- insert error into audit, 'User is not valid'
1105 PA_ACTION_SET_UTILS.Add_Message(
1106 p_app_short_name => 'PA'
1107 ,p_msg_name => 'PA_ADV_NTF_PERSON_ERR'
1108 );
1109
1110 ELSIF p_action_code = 'ADVERTISEMENT_SEND_NTF_ROLE' THEN
1111 -- insert error into audit, Person does not exists for the project role
1112 PA_ACTION_SET_UTILS.Add_Message(
1113 p_app_short_name => 'PA'
1114 ,p_msg_name => 'PA_ADV_NTF_ROLE_ERR'
1115 );
1116
1117 END IF;
1118
1119 ELSE
1120
1121 -- Start different wf process to send different notifications
1122 -- depending of the action line status
1123 IF p_action_status_code = 'PENDING' THEN
1124 l_wf_process := 'PA_ADVERTISEMENT_NTF_PROCESS';
1125 ELSE
1126 l_wf_process := 'PA_REMOVE_ADV_NTF_PROCESS';
1127 END IF;
1128
1129 Start_Adv_Notification_WF (
1130 p_action_code => p_action_code
1131 , p_ntf_recipient_person_id_tbl => l_ntf_recipient_person_id_tbl
1132 , p_ntf_recipient_name_tbl => l_ntf_recipient_name_tbl
1133 , p_assignment_id => p_object_id
1134 , p_project_id => p_project_id
1135 , p_wf_process => l_wf_process
1136 , p_wf_item_type => 'PARADVWF'
1137 , p_insert_audit_flag => p_insert_audit_flag
1138 , x_return_status => x_return_status);
1139
1140 END IF; -- no person
1141
1142 --
1143 -- Insert the error into the audit table
1144 -- and return error status if there is error message in the stack
1145 --
1146 IF FND_MSG_PUB.Count_Msg > 0 THEN
1147 IF p_insert_audit_flag = 'T' THEN
1148
1149 FOR i in 1..FND_MSG_PUB.Count_Msg LOOP
1150 FND_MSG_PUB.get (
1151 p_encoded => FND_API.G_TRUE
1152 ,p_msg_index => i
1153 ,p_data => l_encoded_message_text
1154 ,p_msg_index_out => l_msg_index_out);
1155
1156 l_action_line_audit_rec.action_code := p_action_code;
1157 l_action_line_audit_rec.encoded_error_message := l_encoded_message_text;
1158 IF l_action_status_code = 'REVERSE_PENDING' THEN
1159 l_action_line_audit_rec.reason_code := 'DELETED';
1160 l_action_line_audit_rec.reversed_action_set_line_id := p_action_set_line_id;
1161 ELSIF l_action_status_code = 'UPDATE_PENDING' THEN
1162 l_action_line_audit_rec.reversed_action_set_line_id := p_action_set_line_id;
1163 l_action_line_audit_rec.reason_code := 'UPDATED';
1164 ELSE
1165 l_action_line_audit_rec.reason_code := 'CONDITION_MET';
1166 END IF;
1167 l_index := PA_ADVERTISEMENTS_PUB.g_action_line_audit_tbl.COUNT;
1168 PA_ADVERTISEMENTS_PUB.g_action_line_audit_tbl(l_index) := l_action_line_audit_rec;
1169 END LOOP;
1170 END IF;
1171 x_return_status := FND_API.G_RET_STS_ERROR;
1172 END IF;
1173 -- 4537865 : Included Exception Block
1174 EXCEPTION
1175 WHEN OTHERS THEN
1176 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1177 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_ADVERTISEMENTS_PVT'
1178 , p_procedure_name => 'Send_Notification'
1179 , p_error_text => SUBSTRB(SQLERRM,1,240));
1180 RAISE ;
1181 END Send_Notification;
1182
1183
1184 ----------------------------------------------------------------------
1185 -- Procedure
1186 -- Start Advertisement Notification Workflow
1187 --
1188 -- Purpose
1189 -- Start Workflow process to send advertisement notification
1190 ----------------------------------------------------------------------
1191 PROCEDURE Start_Adv_Notification_WF (
1192 p_action_code IN pa_action_set_lines.action_code%TYPE
1193 , p_ntf_recipient_person_id_tbl IN system.pa_num_tbl_type := NULL
1194 , p_ntf_recipient_name_tbl IN system.pa_varchar2_240_tbl_type := NULL
1195 , p_wf_user_name IN VARCHAR2 := NULL
1196 , p_wf_process IN VARCHAR2
1197 , p_wf_item_type IN VARCHAR2 := 'PARADVWF'
1198 , p_assignment_id IN pa_project_assignments.assignment_id%TYPE
1199 , p_project_id IN pa_projects_all.project_id%TYPE
1200 , p_insert_audit_flag IN VARCHAR2 := 'T'
1201 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1202 ) IS
1203
1204 l_ntf_recipient_person_id_tbl system.pa_num_tbl_type;
1205 l_wf_item_key NUMBER;
1206 l_set_text_attr_name_tbl Wf_Engine.NameTabTyp;
1207 l_set_text_attr_value_tbl Wf_Engine.TextTabTyp;
1208 l_set_num_attr_name_tbl Wf_Engine.NameTabTyp;
1209 l_set_num_attr_value_tbl Wf_Engine.NumTabTyp;
1210 l_set_date_attr_name_tbl Wf_Engine.NameTabTyp;
1211 l_set_date_attr_value_tbl Wf_Engine.DateTabTyp;
1212 l_err_code fnd_new_messages.message_name%TYPE;
1213 l_err_stage VARCHAR2(2000);
1214 l_err_stack VARCHAR2(2000);
1215
1216 i NUMBER;
1217 l_ntf_recipient_user_name fnd_user.user_name%TYPE;
1218 l_display_name VARCHAR2(240);
1219 l_save_threshold NUMBER;
1220 l_wf_item_type VARCHAR2(8);
1221
1222 l_assignment_name pa_project_assignments.assignment_name%TYPE;
1223 l_start_date pa_project_assignments.start_date%TYPE;
1224 l_end_date pa_project_assignments.end_date%TYPE;
1225 l_project_organization_name hr_organization_units.name%TYPE;
1226 l_project_role_name pa_project_role_types.meaning%TYPE;
1227 l_min_resource_job_level pa_project_assignments.min_resource_job_level%TYPE;
1228 l_max_resource_job_level pa_project_assignments.max_resource_job_level%TYPE;
1229 l_staffing_priority_name VARCHAR2(80);
1230 l_description pa_project_assignments.description%TYPE;
1231 l_additional_information pa_project_assignments.additional_information%TYPE;
1232 l_project_name pa_projects_all.name%TYPE;
1233 l_project_number pa_projects_all.segment1%TYPE;
1234 l_project_organization HR_ALL_ORGANIZATION_UNITS.name%TYPE;
1235 -- 4363092 TCA changes, replaced RA views with HZ tables
1236 --l_project_customer RA_CUSTOMERS.customer_name%TYPE;
1237 l_project_customer hz_parties.party_name%TYPE;
1238 -- 4363092 done
1239 l_project_manager per_all_people_f.full_name%TYPE;
1240 l_project_manager_id pa_project_parties.resource_source_id%TYPE;
1241 l_effort pa_project_assignments.assignment_effort%TYPE;
1242 l_duration NUMBER;
1243 l_action_line_audit_rec pa_action_set_utils.insert_audit_lines_rec_type;
1244 l_index NUMBER;
1245 l_requirement_overview_link VARCHAR2(2000);
1246
1247 l_revenue_bill_rate NUMBER;
1248 l_revenue_currency_code VARCHAR2(15);
1249 l_bill_rate_override NUMBER;
1250 l_bill_rate_curr_override VARCHAR2(30);
1251 l_markup_percent_override NUMBER;
1252 l_fcst_tp_amount_type_name VARCHAR2(80);
1253 l_tp_rate_override NUMBER;
1254 l_tp_currency_override VARCHAR2(30);
1255 l_tp_calc_base_code_override VARCHAR2(30);
1256 l_tp_percent_applied_override NUMBER;
1257 l_work_type_name VARCHAR2(80);
1258 /* Bug 3051110-Added the following variables */
1259 l_transfer_price_rate pa_project_assignments.transfer_price_rate%type;
1260 l_transfer_pr_rate_curr pa_project_assignments.transfer_pr_rate_curr%type;
1261 l_override_basis_name VARCHAR2(80) := NULL;
1262
1263 --cursor to get attributes of the requirement to be
1264 --displayed in notifications
1265 -- Bug 2388060 - Apply Action Set after schedule has been created
1266 -- Get the Assignment Effort if it is null
1267 CURSOR get_requirement_info IS
1268 SELECT asgn.assignment_name,
1269 asgn.start_date,
1270 asgn.end_date,
1271 -- pa_expenditures_utils.GetOrgTlName(proj.carrying_out_organization_id), -- Commented for Bug 4866284
1272 pa_resource_utils.get_organization_name(proj.carrying_out_organization_id), -- Added for Bug 4866284
1273 prt.meaning,
1274 asgn.min_resource_job_level,
1275 asgn.max_resource_job_level,
1276 sp.meaning staffing_priority_name,
1277 nvl(asgn.assignment_effort, PA_SCHEDULE_UTILS.get_num_hours(asgn.project_id, asgn.assignment_id)),
1278 (trunc(asgn.end_date) - trunc(asgn.start_date) +1 ),
1279 asgn.description,
1280 asgn.additional_information,
1281 asgn.revenue_bill_rate,
1282 asgn.revenue_currency_code,
1283 asgn.bill_rate_override,
1284 asgn.bill_rate_curr_override,
1285 asgn.markup_percent_override,
1286 fcst.meaning,
1287 asgn.tp_rate_override,
1288 asgn.tp_currency_override,
1289 asgn.tp_calc_base_code_override,
1290 asgn.tp_percent_applied_override,
1291 wt.name,
1292 asgn.transfer_price_rate, -- Added for bug 3051110
1293 asgn.transfer_pr_rate_curr
1294 FROM pa_project_assignments asgn,
1295 pa_projects_all proj,
1296 pa_project_role_types prt,
1297 pa_lookups sp,
1298 pa_lookups fcst,
1299 pa_work_types_v wt
1300 WHERE assignment_id = p_assignment_id
1301 AND asgn.project_role_id = prt.project_role_id
1302 AND asgn.project_id = proj.project_id
1303 AND sp.lookup_type(+) = 'STAFFING_PRIORITY_CODE'
1304 AND asgn.staffing_priority_code = sp.lookup_code(+)
1305 AND fcst.lookup_type(+) = 'TP_AMOUNT_TYPE'
1306 AND asgn.fcst_tp_amount_type = fcst.lookup_code(+)
1307 AND asgn.work_type_id = wt.work_type_id;
1308
1309 CURSOR csr_get_override_basis_name (p_override_basis_code IN VARCHAR2) IS
1310 SELECT plks.meaning
1311 FROM pa_lookups plks
1312 WHERE plks.lookup_type = 'CC_MARKUP_BASE_CODE'
1313 AND plks.lookup_code = p_override_basis_code;
1314
1315 --cursor to get attributes of the project to be
1316 --displayed in notifications
1317 CURSOR get_project_info IS
1318 SELECT proj.name,
1319 proj.segment1,
1320 hou.name,
1321 PA_PROJECT_PARTIES_UTILS.GET_PROJECT_MANAGER(proj.project_id),
1322 PA_PROJECT_PARTIES_UTILS.GET_PROJECT_MANAGER_NAME,
1323 PA_PROJECTS_MAINT_UTILS.GET_PRIMARY_CUSTOMER_NAME(proj.project_id)
1324 FROM pa_projects_all proj,
1325 HR_ALL_ORGANIZATION_UNITS HOU
1326 WHERE proj.project_id = p_project_id
1327 AND proj.CARRYING_OUT_ORGANIZATION_ID = HOU.ORGANIZATION_ID;
1328
1329 BEGIN
1330
1331 -- Initialize the Error Stack
1332 PA_DEBUG.init_err_stack('PA_ADVERTISEMENTS_PVT.Start_Adv_Notification_WF');
1333
1334 --Log Message
1335 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1336 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ADVERTISEMENTS_PVT.Start_Adv_Notification_WF'
1337 ,x_msg => 'Beginning of PA_ADVERTISEMENTS_PVT.Start_Adv_Notification_WF'
1338 ,x_log_level => 5);
1339 END IF;
1340
1341 -- Initialize the return status to success
1342 x_return_status := FND_API.G_RET_STS_SUCCESS;
1343
1344 -- Setting thresold value to run the process in background
1345 l_save_threshold := wf_engine.threshold;
1346 wf_engine.threshold := -1;
1347
1348 -- get the requirement and project information to be displayed
1349 -- in the notifications
1350 OPEN get_requirement_info;
1351 FETCH get_requirement_info INTO
1352 l_assignment_name,
1353 l_start_date,
1354 l_end_date,
1355 l_project_organization_name,
1356 l_project_role_name,
1357 l_min_resource_job_level,
1358 l_max_resource_job_level,
1359 l_staffing_priority_name,
1360 l_effort,
1361 l_duration,
1362 l_description,
1363 l_additional_information,
1364 l_revenue_bill_rate,
1365 l_revenue_currency_code,
1366 l_bill_rate_override,
1367 l_bill_rate_curr_override,
1368 l_markup_percent_override,
1369 l_fcst_tp_amount_type_name,
1370 l_tp_rate_override,
1371 l_tp_currency_override,
1372 l_tp_calc_base_code_override,
1373 l_tp_percent_applied_override,
1374 l_work_type_name,
1375 l_transfer_price_rate, -- Added for bug 3051110
1376 l_transfer_pr_rate_curr;
1377 CLOSE get_requirement_info;
1378
1379 OPEN get_project_info;
1380 FETCH get_project_info INTO
1381 l_project_name,
1382 l_project_number,
1383 l_project_organization,
1384 l_project_manager_id,
1385 l_project_manager,
1386 l_project_customer;
1387 CLOSE get_project_info;
1388
1389 IF l_tp_calc_base_code_override IS NOT NULL THEN
1390 open csr_get_override_basis_name(l_tp_calc_base_code_override);
1391 fetch csr_get_override_basis_name into l_override_basis_name;
1392 close csr_get_override_basis_name;
1393 END IF;
1394
1395 -- if wf user name is passed in, set person_id to -999
1396 IF p_wf_user_name IS NOT NULL THEN
1397
1398 SELECT -999
1399 BULK COLLECT INTO l_ntf_recipient_person_id_tbl
1400 FROM pa_project_assignments
1401 WHERE assignment_id = p_assignment_id;
1402
1403 ELSE
1404 l_ntf_recipient_person_id_tbl := p_ntf_recipient_person_id_tbl;
1405 END IF;
1406
1407 -- start one wf process to send notification per recipient
1408 FOR i in l_ntf_recipient_person_id_tbl.FIRST ..l_ntf_recipient_person_id_tbl.LAST LOOP
1409
1410 IF l_ntf_recipient_person_id_tbl(i)=-999 THEN
1411 l_ntf_recipient_user_name := p_wf_user_name;
1412 ELSE
1413
1414 --Getting recepients fnd user name
1415 wf_directory.getusername
1416 (p_orig_system => 'PER'
1417 ,p_orig_system_id => l_ntf_recipient_person_id_tbl(i)
1418 ,p_name => l_ntf_recipient_user_name
1419 ,p_display_name => l_display_name);
1420
1421 END IF;
1422
1423 IF l_ntf_recipient_user_name IS NULL THEN
1424
1425 --dbms_output.put_line('no username for person id '|| l_ntf_recipient_person_id_tbl(i));
1426 -- insert error into audit, User is not valid
1427 PA_ACTION_SET_UTILS.Add_Message(
1428 p_app_short_name => 'PA'
1429 ,p_msg_name => 'PA_ADV_NTF_ERR'
1430 ,p_token1 => 'PERSON_NAME'
1431 ,p_value1 => p_ntf_recipient_name_tbl(i)
1432 );
1433
1434 x_return_status := FND_API.G_RET_STS_ERROR;
1435
1436 ELSE
1437
1438 --dbms_output.put_line('sending notification to '|| l_ntf_recipient_user_name);
1439
1440 -- Create the unique item key to launch WF with
1441 SELECT pa_advertisement_ntf_wf_s.nextval
1442 INTO l_wf_item_key
1443 FROM dual;
1444
1445 -- Create the WF process
1446 wf_engine.CreateProcess
1447 ( ItemType => p_wf_item_type
1448 , ItemKey => l_wf_item_key
1449 , process => p_wf_process );
1450
1451 --Store the item attributes in plsql tables
1452 l_set_text_attr_name_tbl(l_set_text_attr_name_tbl.COUNT+1) := 'NTF_RECIPIENT';
1453 l_set_text_attr_value_tbl(l_set_text_attr_value_tbl.COUNT+1) := l_ntf_recipient_user_name;
1454
1455 l_set_text_attr_name_tbl(l_set_text_attr_name_tbl.COUNT+1) := 'ASSIGNMENT_NAME';
1456 l_set_text_attr_value_tbl(l_set_text_attr_value_tbl.COUNT+1) := l_assignment_name;
1457 l_set_text_attr_name_tbl(l_set_text_attr_name_tbl.COUNT+1) := 'ROLE_NAME';
1458 l_set_text_attr_value_tbl(l_set_text_attr_value_tbl.COUNT+1) := l_project_role_name;
1459 l_set_text_attr_name_tbl(l_set_text_attr_name_tbl.COUNT+1) := 'ORGANIZATION_NAME';
1460 l_set_text_attr_value_tbl(l_set_text_attr_value_tbl.COUNT+1) := l_project_organization_name;
1461 l_set_text_attr_name_tbl(l_set_text_attr_name_tbl.COUNT+1) := 'STAFFING_PRIORITY';
1462 l_set_text_attr_value_tbl(l_set_text_attr_value_tbl.COUNT+1) := l_staffing_priority_name;
1463 l_set_text_attr_name_tbl(l_set_text_attr_name_tbl.COUNT+1) := 'DESCRIPTION';
1464 l_set_text_attr_value_tbl(l_set_text_attr_value_tbl.COUNT+1) := l_description;
1465 l_set_text_attr_name_tbl(l_set_text_attr_name_tbl.COUNT+1) := 'ADDITIONAL_INFORMATION';
1466 l_set_text_attr_value_tbl(l_set_text_attr_value_tbl.COUNT+1) := l_additional_information;
1467 l_set_text_attr_name_tbl(l_set_text_attr_name_tbl.COUNT+1) := 'PROJECT_NAME';
1468 l_set_text_attr_value_tbl(l_set_text_attr_value_tbl.COUNT+1) := l_project_name;
1469 l_set_text_attr_name_tbl(l_set_text_attr_name_tbl.COUNT+1) := 'PROJECT_NUMBER';
1470 l_set_text_attr_value_tbl(l_set_text_attr_value_tbl.COUNT+1) := l_project_number;
1471 l_set_text_attr_name_tbl(l_set_text_attr_name_tbl.COUNT+1) := 'PROJECT_ORGANIZATION_NAME';
1472 l_set_text_attr_value_tbl(l_set_text_attr_value_tbl.COUNT+1) := l_project_organization_name;
1473 l_set_text_attr_name_tbl(l_set_text_attr_name_tbl.COUNT+1) := 'PROJECT_MANAGER';
1474 l_set_text_attr_value_tbl(l_set_text_attr_value_tbl.COUNT+1) := l_project_manager;
1475 l_set_text_attr_name_tbl(l_set_text_attr_name_tbl.COUNT+1) := 'PROJECT_CUSTOMER';
1476 l_set_text_attr_value_tbl(l_set_text_attr_value_tbl.COUNT+1) := l_project_customer;
1477 /* Bug 2529772 - added paPersonId in the link below */
1478 l_requirement_overview_link :=
1479 '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';
1480
1481 l_set_text_attr_name_tbl(l_set_text_attr_name_tbl.COUNT+1) := 'REQUIREMENT_LINK';
1482 l_set_text_attr_value_tbl(l_set_text_attr_value_tbl.COUNT+1) := l_requirement_overview_link;
1483
1484 l_set_num_attr_name_tbl(l_set_num_attr_name_tbl.COUNT+1) := 'MIN_JOB_LEVEL';
1485 l_set_num_attr_value_tbl(l_set_num_attr_value_tbl.COUNT+1) := l_min_resource_job_level;
1486 l_set_num_attr_name_tbl(l_set_num_attr_name_tbl.COUNT+1) := 'MAX_JOB_LEVEL';
1487 l_set_num_attr_value_tbl(l_set_num_attr_value_tbl.COUNT+1) := l_max_resource_job_level;
1488 l_set_num_attr_name_tbl(l_set_num_attr_name_tbl.COUNT+1) := 'EFFORT';
1489 l_set_num_attr_value_tbl(l_set_num_attr_value_tbl.COUNT+1) := l_effort;
1490 l_set_num_attr_name_tbl(l_set_num_attr_name_tbl.COUNT+1) := 'DURATION';
1491 l_set_num_attr_value_tbl(l_set_num_attr_value_tbl.COUNT+1) := l_duration;
1492
1493 l_set_date_attr_name_tbl(l_set_date_attr_name_tbl.COUNT+1) := 'START_DATE';
1494 l_set_date_attr_value_tbl(l_set_date_attr_value_tbl.COUNT+1) := l_start_date;
1495 l_set_date_attr_name_tbl(l_set_date_attr_name_tbl.COUNT+1) := 'END_DATE';
1496 l_set_date_attr_value_tbl(l_set_date_attr_value_tbl.COUNT+1) := l_end_date;
1497
1498 l_set_num_attr_name_tbl(l_set_num_attr_name_tbl.COUNT+1) := 'REVENUE_BILL_RATE';
1499 l_set_num_attr_value_tbl(l_set_num_attr_value_tbl.COUNT+1) := l_revenue_bill_rate;
1500 l_set_num_attr_name_tbl(l_set_num_attr_name_tbl.COUNT+1) := 'BILL_RATE_OVERRIDE';
1501 l_set_num_attr_value_tbl(l_set_num_attr_value_tbl.COUNT+1) := l_bill_rate_override;
1502 l_set_num_attr_name_tbl(l_set_num_attr_name_tbl.COUNT+1) := 'TP_RATE_OVERRIDE';
1503 l_set_num_attr_value_tbl(l_set_num_attr_value_tbl.COUNT+1) := l_tp_rate_override;
1504 l_set_num_attr_name_tbl(l_set_num_attr_name_tbl.COUNT+1) := 'ASSIGNMENT_ID'; -- added for Bug 4777149
1505 l_set_num_attr_value_tbl(l_set_num_attr_value_tbl.COUNT+1) := p_assignment_id; -- added for Bug 4777149
1506
1507
1508 l_set_text_attr_name_tbl(l_set_text_attr_name_tbl.COUNT+1) := 'REVENUE_BILL_RATE_CURR';
1509 l_set_text_attr_value_tbl(l_set_text_attr_value_tbl.COUNT+1) := l_revenue_currency_code;
1510 l_set_text_attr_name_tbl(l_set_text_attr_name_tbl.COUNT+1) := 'BILL_RATE_OVERRIDE_CURR';
1511 l_set_text_attr_value_tbl(l_set_text_attr_value_tbl.COUNT+1) := l_bill_rate_curr_override;
1512 l_set_text_attr_name_tbl(l_set_text_attr_name_tbl.COUNT+1) := 'TP_AMT_TYPE_NAME';
1513 l_set_text_attr_value_tbl(l_set_text_attr_value_tbl.COUNT+1) := l_fcst_tp_amount_type_name;
1514 l_set_text_attr_name_tbl(l_set_text_attr_name_tbl.COUNT+1) := 'TP_RATE_OVERRIDE_CURR';
1515 l_set_text_attr_value_tbl(l_set_text_attr_value_tbl.COUNT+1) := l_tp_currency_override;
1516 l_set_text_attr_name_tbl(l_set_text_attr_name_tbl.COUNT+1) := 'WORK_TYPE_NAME';
1517 l_set_text_attr_value_tbl(l_set_text_attr_value_tbl.COUNT+1) := l_work_type_name;
1518 l_set_text_attr_name_tbl(l_set_text_attr_name_tbl.COUNT+1) := 'OVERRIDE_BASIS_NAME';
1519 l_set_text_attr_value_tbl(l_set_text_attr_value_tbl.COUNT+1) := l_override_basis_name;
1520
1521 /* Added the below code for bug 3051110 */
1522
1523 l_set_num_attr_name_tbl (l_set_num_attr_name_tbl.COUNT+1) := 'TRANSFER_PRICE_RATE';
1524 l_set_num_attr_value_tbl(l_set_num_attr_value_tbl.COUNT+1) := l_transfer_price_rate;
1525 l_set_text_attr_name_tbl(l_set_text_attr_name_tbl.COUNT+1) := 'TRANSFER_PR_RATE_CURR';
1526 l_set_text_attr_value_tbl(l_set_text_attr_value_tbl.COUNT+1) := l_transfer_pr_rate_curr;
1527
1528 IF l_markup_percent_override IS NOT NULL THEN
1529 l_set_text_attr_name_tbl(l_set_text_attr_name_tbl.COUNT+1) := 'MARKUP_PCT_OVERRIDE';
1530 l_set_text_attr_value_tbl(l_set_text_attr_value_tbl.COUNT+1) := to_char(l_markup_percent_override)||'%';
1531 ELSE
1532 l_set_text_attr_name_tbl(l_set_text_attr_name_tbl.COUNT+1) := 'MARKUP_PCT_OVERRIDE';
1533 l_set_text_attr_value_tbl(l_set_text_attr_value_tbl.COUNT+1) := to_char(l_markup_percent_override);
1534 END IF;
1535
1536 IF l_tp_percent_applied_override IS NOT NULL THEN
1537 IF l_override_basis_name IS NOT NULL THEN
1538 l_set_text_attr_name_tbl(l_set_text_attr_name_tbl.COUNT+1) := 'TP_PCT_APPLIED_OVERRIDE';
1539 l_set_text_attr_value_tbl(l_set_text_attr_value_tbl.COUNT+1) :=
1540 ', '||to_char(l_tp_percent_applied_override)||'%';
1541 ELSE
1542 l_set_text_attr_name_tbl(l_set_text_attr_name_tbl.COUNT+1) := 'TP_PCT_APPLIED_OVERRIDE';
1543 l_set_text_attr_value_tbl(l_set_text_attr_value_tbl.COUNT+1) :=
1544 to_char(l_tp_percent_applied_override)||'%';
1545 END IF;
1546 ELSE
1547 l_set_text_attr_name_tbl(l_set_text_attr_name_tbl.COUNT+1) := 'TP_PCT_APPLIED_OVERRIDE';
1548 l_set_text_attr_value_tbl(l_set_text_attr_value_tbl.COUNT+1) :=
1549 to_char(l_tp_percent_applied_override);
1550 END IF;
1551
1552 --SET the item attributes (these attributes were created at design time)
1553 WF_ENGINE.SetItemAttrTextArray(itemtype => p_wf_item_type,
1554 itemkey => l_wf_item_key,
1555 aname => l_set_text_attr_name_tbl,
1556 avalue => l_set_text_attr_value_tbl);
1557
1558 WF_ENGINE.SetItemAttrNumberArray(itemtype => p_wf_item_type,
1559 itemkey => l_wf_item_key,
1560 aname => l_set_num_attr_name_tbl,
1561 avalue => l_set_num_attr_value_tbl);
1562
1563 WF_ENGINE.SetItemAttrDateArray(itemtype => p_wf_item_type,
1564 itemkey => l_wf_item_key,
1565 aname => l_set_date_attr_name_tbl,
1566 avalue => l_set_date_attr_value_tbl);
1567
1568 --Start the workflow process
1569 wf_engine.StartProcess ( itemtype => p_wf_item_type
1570 ,itemkey => l_wf_item_key );
1571
1572 -- Insert into Notifications table
1573 PA_WORKFLOW_UTILS.Insert_WF_Processes
1574 (p_wf_type_code => 'ADVERTISEMENTS_NTF_WF'
1575 ,p_item_type => p_wf_item_type
1576 ,p_item_key => l_wf_item_key
1577 ,p_entity_key1 => to_char(p_project_id)
1578 ,p_entity_key2 => to_char(l_ntf_recipient_person_id_tbl(i))
1579 ,p_description => NULL
1580 ,p_err_code => l_err_code
1581 ,p_err_stage => l_err_stage
1582 ,p_err_stack => l_err_stack );
1583
1584 -- Insert people into audit table if the action is Send Notification
1585 IF p_insert_audit_flag = 'T' AND
1586 (p_action_code = 'ADVERTISEMENT_SEND_NTF_PERSON' OR
1587 p_action_code = 'ADVERTISEMENT_SEND_NTF_ROLE') THEN
1588
1589 l_action_line_audit_rec.reason_code := 'CONDITION_MET';
1590 l_action_line_audit_rec.action_code := p_action_code;
1591 l_action_line_audit_rec.audit_display_attribute := p_ntf_recipient_name_tbl(i);
1592 l_action_line_audit_rec.audit_attribute := l_ntf_recipient_person_id_tbl(i);
1593 l_action_line_audit_rec.reversed_action_set_line_id := NULL;
1594
1595 l_index := PA_ADVERTISEMENTS_PUB.g_action_line_audit_tbl.COUNT;
1596 PA_ADVERTISEMENTS_PUB.g_action_line_audit_tbl(l_index) := l_action_line_audit_rec;
1597
1598 END IF;
1599
1600 END IF; --if username is null
1601
1602 END LOOP;--end i loop
1603
1604 --Setting the original value
1605 wf_engine.threshold := l_save_threshold;
1606 -- 4537865 : Included Exception Block
1607 EXCEPTION
1608 WHEN OTHERS THEN
1609 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1610 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_ADVERTISEMENTS_PVT'
1611 , p_procedure_name => 'Start_Adv_Notification_WF'
1612 , p_error_text => SUBSTRB(SQLERRM,1,240));
1613 RAISE ;
1614 END Start_Adv_Notification_WF;
1615
1616 ----------------------------------------------------------------------
1617 -- Procedure
1618 -- Perform Update Staffing Priority
1619 --
1620 -- Purpose
1621 -- Update the staffing priority of the requirement.
1622 ----------------------------------------------------------------------
1623 PROCEDURE Update_Staffing_Priority (
1624 p_action_set_line_id IN pa_action_set_lines.action_set_line_id%TYPE
1625 , p_object_id IN pa_action_sets.object_id%TYPE
1626 , p_action_code IN pa_action_set_lines.action_code%TYPE
1627 , p_action_status_code IN pa_action_set_lines.status_code%TYPE
1628 , p_staffing_priority_code IN pa_project_assignments.staffing_priority_code%TYPE
1629 , p_record_version_number IN pa_project_assignments.record_version_number%TYPE
1630 , p_insert_audit_flag IN VARCHAR2 := 'T'
1631 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1632 ) IS
1633
1634 l_staffing_priority_name pa_lookups.meaning%TYPE;
1635 l_action_line_audit_rec pa_action_set_utils.insert_audit_lines_rec_type;
1636 l_index NUMBER;
1637 l_encoded_message_text VARCHAR2(2000);
1638 l_msg_index_out NUMBER;
1639 l_update_sp_display_attribute VARCHAR2(80);
1640 BEGIN
1641
1642 --dbms_output.put_line('update staffing priority');
1643
1644 -- Initialize the Error Stack
1645 PA_DEBUG.init_err_stack('PA_ADVERTISEMENTS_PVT.Update_Staffing_Priority');
1646
1647 --Log Message
1648 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1649 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ADVERTISEMENTS_PVT.Update_Staffing_Priority'
1650 ,x_msg => 'Beginning of PA_ADVERTISEMENTS_PVT.Update_Staffing_Priority'
1651 ,x_log_level => 5);
1652 END IF;
1653
1654 -- Initialize the return status to success
1655 x_return_status := FND_API.G_RET_STS_SUCCESS;
1656
1657 --
1658 -- Handle PENDING action line only as REVERSE_PENDING and UPDATE_PENDING
1659 -- lines can be handled generically by the action set model.
1660 --
1661 IF p_action_status_code = 'PENDING' THEN
1662
1663 SELECT meaning INTO l_staffing_priority_name
1664 FROM pa_lookups
1665 WHERE lookup_type = 'STAFFING_PRIORITY_CODE'
1666 AND lookup_code = p_staffing_priority_code;
1667
1668 -- Update the staffing priority code
1669 PA_PROJECT_ASSIGNMENTS_PKG.Update_Row
1670 ( p_assignment_id => p_object_id
1671 ,p_record_version_number => p_record_version_number
1672 ,p_staffing_priority_code => p_staffing_priority_code
1673 ,x_return_status => x_return_status );
1674
1675 -- Insert into audit table
1676 IF x_return_status = FND_API.G_RET_STS_SUCCESS AND p_insert_audit_flag = 'T' THEN
1677
1678 -- insert into into the global audit record
1679 l_action_line_audit_rec.reason_code := 'CONDITION_MET';
1680 l_action_line_audit_rec.action_code := p_action_code;
1681 l_action_line_audit_rec.audit_display_attribute := l_staffing_priority_name;
1682 l_action_line_audit_rec.audit_attribute := p_staffing_priority_code;
1683 l_action_line_audit_rec.reversed_action_set_line_id := NULL;
1684 l_index := PA_ADVERTISEMENTS_PUB.g_action_line_audit_tbl.COUNT;
1685 PA_ADVERTISEMENTS_PUB.g_action_line_audit_tbl(l_index) := l_action_line_audit_rec;
1686
1687 ELSE
1688
1689 -- ERROR: Insert error into the audit table
1690 IF FND_MSG_PUB.Count_Msg > 0 THEN
1691 IF p_insert_audit_flag = 'T' THEN
1692
1693 FND_MSG_PUB.get (
1694 p_encoded => FND_API.G_TRUE
1695 ,p_msg_index => FND_MSG_PUB.Count_Msg
1696 ,p_data => l_encoded_message_text
1697 ,p_msg_index_out => l_msg_index_out);
1698
1699 l_action_line_audit_rec.reason_code := 'CONDITION_MET';
1700 l_action_line_audit_rec.action_code := p_action_code;
1701 l_action_line_audit_rec.encoded_error_message := l_encoded_message_text;
1702 l_index := PA_ADVERTISEMENTS_PUB.g_action_line_audit_tbl.COUNT;
1703 PA_ADVERTISEMENTS_PUB.g_action_line_audit_tbl(l_index) := l_action_line_audit_rec;
1704 END IF; -- IF p_insert_audit_flag = 'T'
1705 x_return_status := FND_API.G_RET_STS_ERROR;
1706 END IF; -- IF FND_MSG_PUB.Count_Msg > 0
1707
1708 END IF;
1709
1710
1711 ELSE -- action status is REVERSE PENDING or UPDATE PENDING
1712
1713 IF p_insert_audit_flag = 'T' THEN
1714
1715 -- Get the Audit Display Attribute
1716 SELECT meaning INTO l_update_sp_display_attribute
1717 FROM pa_lookups
1718 WHERE lookup_type = 'ADVERTISEMENT'
1719 AND lookup_code = 'NO_ACTION_PERFORMED';
1720
1721 -- insert into into the global audit record
1722 IF p_action_status_code = 'REVERSE_PENDING' THEN
1723 l_action_line_audit_rec.reason_code := 'DELETED';
1724 ELSE
1725 l_action_line_audit_rec.reason_code := 'UPDATED';
1726 END IF;
1727 l_action_line_audit_rec.action_code := p_action_code;
1728 l_action_line_audit_rec.audit_display_attribute := l_update_sp_display_attribute;
1729 l_action_line_audit_rec.audit_attribute := p_staffing_priority_code;
1730 l_action_line_audit_rec.reversed_action_set_line_id := p_action_set_line_id;
1731 l_index := PA_ADVERTISEMENTS_PUB.g_action_line_audit_tbl.COUNT;
1732 PA_ADVERTISEMENTS_PUB.g_action_line_audit_tbl(l_index) := l_action_line_audit_rec;
1733
1734 END IF;
1735
1736 END IF; -- action status
1737 -- 4537865 : Included Exception Block
1738 EXCEPTION
1739 WHEN OTHERS THEN
1740 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1741 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_ADVERTISEMENTS_PVT'
1742 , p_procedure_name => 'Update_Staffing_Priority'
1743 , p_error_text => SUBSTRB(SQLERRM,1,240));
1744 RAISE ;
1745 END Update_Staffing_Priority;
1746
1747
1748 ----------------------------------------------------------------------
1749 -- Procedure
1750 -- Perform Remove Advertisement
1751 --
1752 -- Purpose
1753 -- Remove the visibility or advertisement of the requirement.
1754 ----------------------------------------------------------------------
1755 PROCEDURE Remove_Advertisement (
1756 p_action_set_line_id IN pa_action_set_lines.action_set_line_id%TYPE
1757 , p_object_id IN pa_action_sets.object_id%TYPE
1758 , p_action_code IN pa_action_set_lines.action_code%TYPE
1759 , p_action_status_code IN pa_action_set_lines.status_code%TYPE
1760 , p_project_id IN pa_projects_all.project_id%TYPE
1761 , p_insert_audit_flag IN VARCHAR2 := 'T'
1762 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1763 ) IS
1764
1765 TYPE number_tbl IS TABLE OF NUMBER
1766 INDEX BY BINARY_INTEGER;
1767 TYPE varchar_tbl IS TABLE OF VARCHAR2(150)
1768 INDEX BY BINARY_INTEGER;
1769
1770 l_return_status VARCHAR2(1);
1771 l_action_set_line_id_tbl pa_action_set_utils.number_tbl_type;
1772 l_action_code_tbl varchar_tbl;
1773 l_audit_attribute_tbl varchar_tbl;
1774 l_display_audit_attribute_tbl varchar_tbl;
1775 l_line_status_tbl pa_action_set_utils.varchar_tbl_type;
1776 l_action_set_line_id_tbl2 pa_action_set_utils.number_tbl_type;
1777 l_line_status_tbl2 pa_action_set_utils.varchar_tbl_type;
1778
1779 i NUMBER;
1780 l_action_status_code VARCHAR2(30);
1781 l_action_set_id NUMBER;
1782 l_object_id NUMBER;
1783 l_object_type VARCHAR2(30);
1784 l_action_set_type_code VARCHAR2(30);
1785 l_project_id NUMBER;
1786 l_record_version_number NUMBER;
1787 l_action_line_audit_rec pa_action_set_utils.insert_audit_lines_rec_type;
1788 l_index NUMBER;
1789 l_encoded_message_text VARCHAR2(2000);
1790 l_msg_index_out NUMBER;
1791 l_audit_reason_code VARCHAR2(30);
1792 l_perform_return_status VARCHAR2(1);
1793 l_audit_action_code VARCHAR2(30);
1794 l_update_sp_display_attribute VARCHAR2(80);
1795 l_remove_adv_display_attribute VARCHAR2(80);
1796
1797 -- cursor to get all the currently active audit lines
1798 CURSOR get_all_active_audit_lines IS
1799 SELECT action_set_line_id, action_code, audit_attribute, audit_display_attribute, 'REVERSED'
1800 FROM pa_action_set_line_aud
1801 WHERE object_id = p_object_id
1802 AND object_type = 'OPEN_ASSIGNMENT'
1803 AND action_set_type_code = 'ADVERTISEMENT'
1804 AND active_flag = 'Y'
1805 AND reversed_action_set_line_id is null
1806 AND encoded_error_message is null;
1807
1808 -- cursor to get all the currently active audit lines with/without error
1809 CURSOR get_active_audit_lines_error IS
1810 SELECT action_set_line_id, 'REVERSED'
1811 FROM pa_action_set_line_aud
1812 WHERE object_id = p_object_id
1813 AND object_type = 'OPEN_ASSIGNMENT'
1814 AND action_set_type_code = 'ADVERTISEMENT'
1815 AND active_flag = 'Y'
1816 AND reversed_action_set_line_id is null
1817 GROUP BY action_set_line_id; -- CH2M Performance Bug fix: 2768530
1818
1819 -- cursor to get all the action lines that were reversed due to the execution
1820 -- of this Remove Advertisement line and are not deleted
1821 CURSOR get_all_reversed_lines IS
1822 SELECT asl.action_set_line_id, asl.action_code, asa.audit_attribute, asa.audit_display_attribute, 'COMPLETE'
1823 FROM pa_action_set_line_aud asa,
1824 pa_action_set_lines asl,
1825 pa_action_set_line_cond aslc
1826 WHERE asa.object_id = p_object_id
1827 AND asa.object_type = 'OPEN_ASSIGNMENT'
1828 AND asa.action_set_type_code = 'ADVERTISEMENT'
1829 AND asa.action_set_line_id = p_action_set_line_id
1830 AND asa.reversed_action_set_line_id IS NOT NULL
1831 AND asl.action_set_line_id = asa.reversed_action_set_line_id
1832 AND nvl(asl.line_deleted_flag, 'N') = 'N'
1833 AND encoded_error_message is null
1834 AND aslc.action_set_line_id = asl.action_set_line_id
1835 AND aslc.condition_date <= sysdate
1836 AND asl.action_set_line_id <> p_action_set_line_id;
1837
1838 BEGIN
1839
1840 --dbms_output.put_line('begin of PA_ADVERTISEMENTS_PVT.Remove_Advertisement');
1841
1842 -- Initialize the Error Stack
1843 PA_DEBUG.init_err_stack('PA_ADVERTISEMENTS_PVT.Update_Staffing_Priority');
1844
1845 --Log Message
1846 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1847 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ADVERTISEMENTS_PVT.Remove_Advertisement'
1848 ,x_msg => 'Beginning of PA_ADVERTISEMENTS_PVT.Remove_Advertisement'
1849 ,x_log_level => 5);
1850 END IF;
1851
1852 -- Initialize the return status to success
1853 x_return_status := FND_API.G_RET_STS_SUCCESS;
1854
1855 --
1856 -- CASE 1: the action status code is PENDING
1857 IF p_action_status_code = 'PENDING' THEN
1858
1859 -- insert the Remove Advertisement record into global audit rec
1860 IF p_insert_audit_flag = 'T' THEN
1861
1862 -- insert into into the global audit record
1863 l_action_line_audit_rec.reason_code := 'CONDITION_MET';
1864 l_action_line_audit_rec.action_code := p_action_code;
1865 l_action_line_audit_rec.audit_display_attribute := null;
1866 l_action_line_audit_rec.audit_attribute := null;
1867 l_action_line_audit_rec.reversed_action_set_line_id := NULL;
1868 l_index := PA_ADVERTISEMENTS_PUB.g_action_line_audit_tbl.COUNT;
1869 PA_ADVERTISEMENTS_PUB.g_action_line_audit_tbl(l_index) := l_action_line_audit_rec;
1870
1871 END IF;
1872
1873 -- get all the currently active audit lines
1874 -- these lines need to be reversed
1875 OPEN get_all_active_audit_lines;
1876 FETCH get_all_active_audit_lines
1877 BULK COLLECT INTO l_action_set_line_id_tbl,
1878 l_action_code_tbl,
1879 l_audit_attribute_tbl,
1880 l_display_audit_attribute_tbl,
1881 l_line_status_tbl;
1882 CLOSE get_all_active_audit_lines;
1883
1884 --dbms_output.put_line('l_action_set_line_id_tbl.COUNT:'||l_action_set_line_id_tbl.COUNT);
1885
1886 -- get all the currently active audit lines with/without error
1887 -- not all these lines need to be reversed
1888 -- but the line statuses and active flags need to be changed
1889 -- get all the currently active audit lines
1890 -- these lines need to be reversed
1891 OPEN get_active_audit_lines_error;
1892 FETCH get_active_audit_lines_error
1893 BULK COLLECT INTO l_action_set_line_id_tbl2,
1894 -- l_action_code_tbl2,
1895 -- l_audit_attribute_tbl2,
1896 -- l_display_audit_attribute_tbl2,
1897 l_line_status_tbl2;
1898 CLOSE get_active_audit_lines_error;
1899
1900 IF l_action_set_line_id_tbl2.COUNT > 0 THEN
1901
1902 -- Update the action line status to REVERSED
1903 PA_ACTION_SETS_PVT.Bulk_Update_Line_Status(
1904 p_action_set_line_id_tbl => l_action_set_line_id_tbl2
1905 ,p_line_status_tbl => l_line_status_tbl2
1906 ,x_return_status => l_return_status
1907 );
1908 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1909 x_return_status := FND_API.G_RET_STS_ERROR;
1910 END IF;
1911
1912 -- Update the active flag to 'N' in the audit records
1913 FORALL i IN l_action_set_line_id_tbl2.FIRST ..l_action_set_line_id_tbl2.LAST
1914 UPDATE pa_action_set_line_aud
1915 SET active_flag = 'N'
1916 WHERE action_set_line_id = l_action_set_line_id_tbl2(i);
1917
1918 -- set the action status code to REVERSE_PENDING
1919 -- to reverse the child records
1920 l_action_status_code := 'REVERSE_PENDING';
1921 l_audit_reason_code := 'ADVERTISEMENT_REMOVED';
1922 END IF; -- if l_action_set_line_id_tbl2.COUNT > 0
1923
1924 --
1925 -- CASE 2: the action status code is REVERSE or UPDATE PENDING
1926 --
1927 ELSE
1928
1929 -- get all the action lines that were reversed due to the execution
1930 -- of this Remove Advertisement line and are not deleted
1931 OPEN get_all_reversed_lines;
1932 FETCH get_all_reversed_lines
1933 BULK COLLECT INTO l_action_set_line_id_tbl,
1934 l_action_code_tbl,
1935 l_audit_attribute_tbl,
1936 l_display_audit_attribute_tbl,
1937 l_line_status_tbl;
1938 CLOSE get_all_reversed_lines;
1939
1940 --dbms_output.put_line('l_action_set_line_id_tbl.COUNT:'||l_action_set_line_id_tbl.COUNT);
1941
1942 IF l_action_set_line_id_tbl.COUNT = 0 THEN
1943
1944 -- Get the Audit Display Attribute
1945 SELECT meaning INTO l_remove_adv_display_attribute
1946 FROM pa_lookups
1947 WHERE lookup_type = 'ADVERTISEMENT'
1948 AND lookup_code = 'NO_ACTION_PERFORMED';
1949
1950 ELSE
1951 l_remove_adv_display_attribute := NULL;
1952 END IF;
1953
1954 -- insert the Remove Advertisement record into global audit rec
1955 IF p_insert_audit_flag = 'T' THEN
1956
1957 -- insert into into the global audit record
1958 IF p_action_status_code = 'REVERSE_PENDING' THEN
1959 l_action_line_audit_rec.reason_code := 'DELETED';
1960 ELSE
1961 l_action_line_audit_rec.reason_code := 'UPDATED';
1962 END IF;
1963 l_action_line_audit_rec.action_code := p_action_code;
1964 l_action_line_audit_rec.audit_display_attribute := l_remove_adv_display_attribute;
1965 l_action_line_audit_rec.audit_attribute := null;
1966 l_action_line_audit_rec.reversed_action_set_line_id := p_action_set_line_id;
1967 l_index := PA_ADVERTISEMENTS_PUB.g_action_line_audit_tbl.COUNT;
1968 PA_ADVERTISEMENTS_PUB.g_action_line_audit_tbl(l_index) := l_action_line_audit_rec;
1969
1970 END IF;
1971
1972 IF l_action_set_line_id_tbl.COUNT > 0 THEN
1973
1974 -- update the action line status to COMPLETE
1975 PA_ACTION_SETS_PVT.Bulk_Update_Line_Status(
1976 p_action_set_line_id_tbl => l_action_set_line_id_tbl
1977 ,p_line_status_tbl => l_line_status_tbl
1978 ,x_return_status => l_return_status
1979 );
1980 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1981 x_return_status := FND_API.G_RET_STS_ERROR;
1982 END IF;
1983
1984 -- set the action status code to PENDING
1985 -- to re-execute the child records
1986 l_action_status_code := 'PENDING';
1987 l_audit_reason_code := 'ADVERTISEMENT_REINSTATED';
1988
1989 END IF; -- IF l_action_set_line_id_tbl.COUNT > 0 T
1990
1991 END IF; -- action line status status
1992
1993 --
1994 -- Re-execute or reverse the child actions
1995 --
1996 IF l_action_set_line_id_tbl.COUNT > 0 THEN
1997
1998 -- get object information
1999 SELECT project_id, record_version_number
2000 INTO l_project_id, l_record_version_number
2001 FROM pa_project_assignments
2002 WHERE assignment_id = p_object_id;
2003
2004 -- handle send notifications, send email and update staffing priority
2005 FOR i IN l_action_set_line_id_tbl.FIRST .. l_action_set_line_id_tbl.LAST LOOP
2006
2007 l_perform_return_status := FND_API.G_RET_STS_SUCCESS;
2008 l_audit_action_code := NULL;
2009 --dbms_output.put_line('l_action_set_line_id: '||l_action_set_line_id_tbl(i));
2010
2011 IF l_action_code_tbl(i) = 'ADVERTISEMENT_SEND_EMAIL' OR
2012 l_action_code_tbl(i) = 'REVERSE_SEND_EMAIL' THEN
2013
2014 Send_Email(
2015 p_action_set_line_id => p_action_set_line_id
2016 , p_object_id => p_object_id
2017 , p_action_code => 'ADVERTISEMENT_SEND_EMAIL'
2018 , p_action_status_code => l_action_status_code
2019 , p_project_id => l_project_id
2020 , p_email_address => l_audit_attribute_tbl(i)
2021 , p_insert_audit_flag => 'F'
2022 , x_return_status => l_return_status
2023
2024 );
2025 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2026 x_return_status := FND_API.G_RET_STS_ERROR;
2027 l_perform_return_status := FND_API.G_RET_STS_ERROR;
2028 END IF;
2029
2030 -- Store difference action code in the audit table in cases
2031 -- of Cancel Advertisement and Reverse Cancel Advertisement
2032 IF p_action_status_code = 'PENDING' THEN
2033 l_audit_action_code := 'REVERSE_SEND_EMAIL';
2034 ELSE
2035 l_audit_action_code := 'ADVERTISEMENT_SEND_EMAIL';
2036 END IF;
2037
2038 ELSIF l_action_code_tbl(i) = 'ADVERTISEMENT_SEND_NTF_PERSON' OR
2039 l_action_code_tbl(i) = 'REVERSE_SEND_NTF_PERSON' THEN
2040
2041 Send_Notification(
2042 p_action_set_line_id => p_action_set_line_id
2043 , p_object_id => p_object_id
2044 , p_action_code => 'ADVERTISEMENT_SEND_NTF_PERSON'
2045 , p_action_status_code => l_action_status_code
2046 , p_method => 'PERSON'
2047 , p_person_id => to_number(l_audit_attribute_tbl(i))
2048 , p_project_id => l_project_id
2049 , p_project_role_id => null
2050 , p_insert_audit_flag => 'F'
2051 , x_return_status => l_return_status -- Changed from x_return_status to l_return_status : 4537865
2052
2053 );
2054 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2055 x_return_status := FND_API.G_RET_STS_ERROR;
2056 l_perform_return_status := FND_API.G_RET_STS_ERROR;
2057 END IF;
2058
2059 -- Store difference action code in the audit table in cases
2060 -- of Cancel Advertisement and Reverse Cancel Advertisement
2061 IF p_action_status_code = 'PENDING' THEN
2062 l_audit_action_code := 'REVERSE_SEND_NTF_PERSON';
2063 ELSE
2064 l_audit_action_code := 'ADVERTISEMENT_SEND_NTF_PERSON';
2065 END IF;
2066
2067 ELSIF l_action_code_tbl(i) = 'ADVERTISEMENT_SEND_NTF_ROLE' OR
2068 l_action_code_tbl(i) = 'REVERSE_SEND_NTF_ROLE' THEN
2069
2070 Send_Notification(
2071 p_action_set_line_id => p_action_set_line_id
2072 , p_object_id => p_object_id
2073 , p_action_code => 'ADVERTISEMENT_SEND_NTF_ROLE'
2074 , p_action_status_code => l_action_status_code
2075 , p_method => 'PERSON'
2076 , p_person_id => to_number(l_audit_attribute_tbl(i))
2077 , p_project_id => l_project_id
2078 , p_project_role_id => null
2079 , p_insert_audit_flag => 'F'
2080 , x_return_status => l_return_status -- Changed from x_return_status to l_return_status : 4537865
2081
2082 );
2083 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2084 x_return_status := FND_API.G_RET_STS_ERROR;
2085 l_perform_return_status := FND_API.G_RET_STS_ERROR;
2086 END IF;
2087
2088 -- Store difference action code in the audit table in cases
2089 -- of Cancel Advertisement and Reverse Cancel Advertisement
2090 IF p_action_status_code = 'PENDING' THEN
2091 l_audit_action_code := 'REVERSE_SEND_NTF_ROLE';
2092 ELSE
2093 l_audit_action_code := 'ADVERTISEMENT_SEND_NTF_ROLE';
2094 END IF;
2095
2096
2097 ELSIF l_action_code_tbl(i) = 'ADVERTISEMENT_UPDATE_SP' OR
2098 l_action_code_tbl(i) = 'REVERSE_UPDATE_SP' THEN
2099
2100 Update_Staffing_Priority(
2101 p_action_set_line_id => p_action_set_line_id
2102 , p_object_id => p_object_id
2103 , p_action_code => 'ADVERTISEMENT_UPDATE_SP'
2104 , p_action_status_code => l_action_status_code
2105 , p_staffing_priority_code => l_audit_attribute_tbl(i)
2106 , p_record_version_number => l_record_version_number
2107 , p_insert_audit_flag => 'F'
2108 , x_return_status => l_return_status -- Changed from x_return_status to l_return_status : 4537865
2109 );
2110 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2111 x_return_status := FND_API.G_RET_STS_ERROR;
2112 l_perform_return_status := FND_API.G_RET_STS_ERROR;
2113 END IF;
2114
2115 -- Store difference action code in the audit table in cases
2116 -- of Cancel Advertisement and Reverse Cancel Advertisement
2117 IF p_action_status_code = 'PENDING' THEN
2118 l_audit_action_code := 'REVERSE_UPDATE_SP';
2119
2120 -- Get the Audit Display Attribute
2121 SELECT meaning INTO l_update_sp_display_attribute
2122 FROM pa_lookups
2123 WHERE lookup_type = 'ADVERTISEMENT'
2124 AND lookup_code = 'NO_ACTION_PERFORMED';
2125
2126 ELSE
2127 l_audit_action_code := 'ADVERTISEMENT_UPDATE_SP';
2128 END IF;
2129
2130 ELSIF l_action_code_tbl(i) = 'ADVERTISEMENT_PUB_TO_ALL' OR
2131 l_action_code_tbl(i) = 'REVERSE_PUB_TO_ALL' THEN
2132
2133 -- Store difference action code in the audit table in cases
2134 -- of Cancel Advertisement and Reverse Cancel Advertisement
2135 IF p_action_status_code = 'PENDING' THEN
2136 l_audit_action_code := 'REVERSE_PUB_TO_ALL';
2137 ELSE
2138 l_audit_action_code := 'ADVERTISEMENT_PUB_TO_ALL';
2139 END IF;
2140
2141 ELSIF l_action_code_tbl(i) = 'ADVERTISEMENT_PUB_TO_START_ORG' OR
2142 l_action_code_tbl(i) = 'REVERSE_PUB_TO_START_ORG' THEN
2143
2144 -- Store difference action code in the audit table in cases
2145 -- of Cancel Advertisement and Reverse Cancel Advertisement
2146 IF p_action_status_code = 'PENDING' THEN
2147 l_audit_action_code := 'REVERSE_PUB_TO_START_ORG';
2148 ELSE
2149 l_audit_action_code := 'ADVERTISEMENT_PUB_TO_START_ORG';
2150 END IF;
2151
2152 ELSIF l_action_code_tbl(i) = 'ADVERTISEMENT_PUB_TO_SM' OR
2153 l_action_code_tbl(i) = 'REVERSE_PUB_TO_SM' THEN
2154
2155 -- Store difference action code in the audit table in cases
2156 -- of Cancel Advertisement and Reverse Cancel Advertisement
2157 IF p_action_status_code = 'PENDING' THEN
2158 l_audit_action_code := 'REVERSE_PUB_TO_SM';
2159 ELSE
2160 l_audit_action_code := 'ADVERTISEMENT_PUB_TO_SM';
2161 END IF;
2162
2163 ELSIF l_action_code_tbl(i) = 'ADVERTISEMENT_ESC_TO_NEXT_LVL' OR
2164 l_action_code_tbl(i) = 'REVERSE_ESC_TO_NEXT_LVL' THEN
2165
2166 -- Store difference action code in the audit table in cases
2167 -- of Cancel Advertisement and Reverse Cancel Advertisement
2168 IF p_action_status_code = 'PENDING' THEN
2169 l_audit_action_code := 'REVERSE_ESC_TO_NEXT_LVL';
2170 ELSE
2171 l_audit_action_code := 'ADVERTISEMENT_ESC_TO_NEXT_LVL';
2172 END IF;
2173
2174 ELSIF l_action_code_tbl(i) = 'ADVERTISEMENT_REMOVE_ADV' OR
2175 l_action_code_tbl(i) = 'REVERSE_REMOVE_ADV' THEN
2176
2177 -- Store difference action code in the audit table in cases
2178 -- of Cancel Advertisement and Reverse Cancel Advertisement
2179 IF p_action_status_code = 'PENDING' THEN
2180 l_audit_action_code := 'REVERSE_REMOVE_ADV';
2181 ELSE
2182 l_audit_action_code := 'ADVERTISEMENT_REMOVE_ADV';
2183 END IF;
2184
2185 END IF; -- if action_code = ..
2186
2187 -- Insert into into the global audit record
2188 IF p_insert_audit_flag = 'T' AND l_perform_return_status=FND_API.G_RET_STS_SUCCESS THEN
2189
2190 l_action_line_audit_rec.reason_code := l_audit_reason_code;
2191 l_action_line_audit_rec.action_code := l_audit_action_code;
2192 l_action_line_audit_rec.audit_attribute := l_audit_attribute_tbl(i);
2193 l_action_line_audit_rec.audit_display_attribute := l_display_audit_attribute_tbl(i);
2194
2195 IF l_action_status_code = 'REVERSE_PENDING' THEN
2196 l_action_line_audit_rec.reversed_action_set_line_id := l_action_set_line_id_tbl(i);
2197 IF l_audit_action_code = 'ADVERTISEMENT_UPDATE_SP' OR
2198 l_audit_action_code = 'REVERSE_UPDATE_SP' THEN
2199 l_action_line_audit_rec.audit_display_attribute := l_update_sp_display_attribute;
2200 END IF;
2201 ELSE
2202 l_action_line_audit_rec.reversed_action_set_line_id := NULL;
2203 END IF;
2204
2205 l_index := PA_ADVERTISEMENTS_PUB.g_action_line_audit_tbl.COUNT;
2206 PA_ADVERTISEMENTS_PUB.g_action_line_audit_tbl(l_index) := l_action_line_audit_rec;
2207
2208 ELSIF p_insert_audit_flag = 'T' AND l_perform_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2209
2210 FND_MSG_PUB.get (
2211 p_encoded => FND_API.G_TRUE
2212 ,p_msg_index => FND_MSG_PUB.Count_Msg
2213 ,p_data => l_encoded_message_text
2214 ,p_msg_index_out => l_msg_index_out);
2215
2216 l_action_line_audit_rec.reason_code := l_audit_reason_code;
2217 l_action_line_audit_rec.action_code := l_audit_action_code;
2218 l_action_line_audit_rec.encoded_error_message := l_encoded_message_text;
2219 IF l_action_status_code = 'REVERSE_PENDING' THEN
2220 l_action_line_audit_rec.reversed_action_set_line_id := l_action_set_line_id_tbl(i);
2221 END IF;
2222 l_index := PA_ADVERTISEMENTS_PUB.g_action_line_audit_tbl.COUNT;
2223 PA_ADVERTISEMENTS_PUB.g_action_line_audit_tbl(l_index) := l_action_line_audit_rec;
2224
2225 END IF; -- if p_insert_audit_flag = 'T'
2226
2227 END LOOP;
2228
2229 END IF; -- if l_action_set_line_id_tbl.COUNT > 0
2230
2231 IF FND_MSG_PUB.Count_Msg > 0 THEN
2232 x_return_status := FND_API.G_RET_STS_ERROR;
2233 END IF;
2234
2235 -- 4537865 : Included Exception Block
2236 EXCEPTION
2237 WHEN OTHERS THEN
2238 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2239 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_ADVERTISEMENTS_PVT'
2240 , p_procedure_name => 'Remove_Advertisement'
2241 , p_error_text => SUBSTRB(SQLERRM,1,240));
2242 RAISE ;
2243 END Remove_Advertisement;
2244
2245 -- Start changes for Bug 4777149
2246 ----------------------------------------------------------------------
2247 -- Procedure
2248 -- Perform Check Assignment is in Open Status
2249 --
2250 -- Purpose
2251 -- Check if assignment is in open status before sending the advertisement mail.
2252 ----------------------------------------------------------------------
2253 PROCEDURE check_assignment_open(
2254 itemtype IN VARCHAR2
2255 , itemkey IN VARCHAR2
2256 , actid IN NUMBER
2257 , funcmode IN VARCHAR2
2258 , resultout OUT NOCOPY VARCHAR2 --NOCOPY required for OUT and IN/OUT parameters
2259 ) IS
2260 v_dummy varchar2(1);
2261 v_assig_id pa_project_assignments.assignment_id%type;
2262
2263 BEGIN
2264
2265 v_assig_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
2266 itemkey => itemkey,
2267 aname => 'ASSIGNMENT_ID' );
2268
2269 select 'Y' into v_dummy from pa_project_assignments
2270 where assignment_id = v_assig_id
2271 and STATUS_CODE in
2272 (select PROJECT_STATUS_CODE from pa_project_statuses
2273 where PROJECT_SYSTEM_STATUS_CODE = 'OPEN_ASGMT');
2274
2275 resultout := wf_engine.eng_completed||':'||'S';
2276
2277 Exception
2278 when others then
2279 resultout := wf_engine.eng_completed||':'||'F';
2280 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_ADVERTISEMENTS_PVT'
2281 , p_procedure_name => 'Check_Assignment_Open'
2282 , p_error_text => SUBSTRB(SQLERRM,1,240));
2283 --RAISE ; // commented for 7134435
2284 END check_assignment_open;
2285 -- End changes for Bug 4777149
2286
2287 END PA_ADVERTISEMENTS_PVT;
2288