[Home] [Help]
PACKAGE BODY: APPS.PA_ACTION_SETS_PVT
Source
1 PACKAGE BODY pa_action_sets_pvt AS
2 /*$Header: PARASPVB.pls 120.2.12010000.2 2008/08/22 16:11:53 mumohan ship $*/
3 --
4 PROCEDURE create_action_set
5 (p_action_set_type_code IN pa_action_set_types.action_set_type_code%TYPE
6 ,p_action_set_name IN pa_action_sets.action_set_name%TYPE
7 ,p_object_type IN pa_action_sets.object_type%TYPE := NULL
8 ,p_object_id IN pa_action_sets.object_id%TYPE := NULL
9 ,p_start_date_active IN pa_action_sets.start_date_active%TYPE := NULL
10 ,p_end_date_active IN pa_action_sets.end_date_active%TYPE := NULL
11 ,p_action_set_template_flag IN pa_action_sets.action_set_template_flag%TYPE := NULL
12 ,p_source_action_set_id IN pa_action_sets.source_action_set_id%TYPE := NULL
13 ,p_status_code IN pa_action_sets.status_code%TYPE := NULL
14 ,p_description IN pa_action_sets.description%TYPE := NULL
15 ,p_attribute_category IN pa_action_sets.attribute_category%TYPE := NULL
16 ,p_attribute1 IN pa_action_sets.attribute1%TYPE := NULL
17 ,p_attribute2 IN pa_action_sets.attribute2%TYPE := NULL
18 ,p_attribute3 IN pa_action_sets.attribute3%TYPE := NULL
19 ,p_attribute4 IN pa_action_sets.attribute4%TYPE := NULL
20 ,p_attribute5 IN pa_action_sets.attribute5%TYPE := NULL
21 ,p_attribute6 IN pa_action_sets.attribute6%TYPE := NULL
22 ,p_attribute7 IN pa_action_sets.attribute7%TYPE := NULL
23 ,p_attribute8 IN pa_action_sets.attribute8%TYPE := NULL
24 ,p_attribute9 IN pa_action_sets.attribute9%TYPE := NULL
25 ,p_attribute10 IN pa_action_sets.attribute10%TYPE := NULL
26 ,p_attribute11 IN pa_action_sets.attribute11%TYPE := NULL
27 ,p_attribute12 IN pa_action_sets.attribute12%TYPE := NULL
28 ,p_attribute13 IN pa_action_sets.attribute13%TYPE := NULL
29 ,p_attribute14 IN pa_action_sets.attribute14%TYPE := NULL
30 ,p_attribute15 IN pa_action_sets.attribute15%TYPE := NULL
31 ,x_action_set_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
32 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
33 )
34 IS
35
36 l_return_status VARCHAR2(1);
37 l_unique VARCHAR2(1);
38 l_existing_action_set_id NUMBER;
39 l_status_code pa_action_sets.status_code%TYPE;
40 l_actual_start_date DATE;
41 l_is_action_set_started VARCHAR2(1);
42 l_debug_mode VARCHAR2(20) := 'N';
43
44 BEGIN
45
46 -- Initialize the return status to success
47 x_return_status := FND_API.G_RET_STS_SUCCESS;
48
49 --Bug 4403338
50 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
51
52 --Log Message: 4403338
53 IF l_debug_mode = 'Y' THEN
54 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ACTION_SETS_PVT.Create_Action_Set.begin'
55 ,x_msg => 'Beginning of Create_Action_Set pvt'
56 ,x_log_level => 5);
57 END IF;
58
59 IF p_action_set_template_flag = 'Y' THEN
60
61 l_unique := PA_ACTION_SET_UTILS.is_name_unique_in_type(
62 p_action_set_type_code => p_action_set_type_code,
63 p_action_set_name => p_action_set_name);
64
65 IF l_unique = 'N' THEN
66 PA_UTILS.Add_Message ( p_app_short_name => 'PA'
67 ,p_msg_name => 'PA_ACTION_SET_NAME_NOT_UNIQUE');
68 END IF;
69
70 IF p_end_date_active IS NOT NULL THEN
71 IF p_start_date_active > p_end_date_active THEN
72 PA_UTILS.Add_Message ( p_app_short_name => 'PA'
73 ,p_msg_name => 'PA_INVALID_START_DATE');
74 END IF;
75 END IF;
76
77 ELSE
78
79 l_existing_action_set_id := PA_ACTION_SET_UTILS.get_action_set_id(
80 p_action_set_type_code => p_action_set_type_code,
81 p_object_type => p_object_type,
82 p_object_id => p_object_id);
83
84 IF l_existing_action_set_id IS NOT NULL THEN
85 PA_UTILS.Add_Message (p_app_short_name => 'PA'
86 ,p_msg_name => 'PA_OBJECT_HAS_ACTION_SET');
87
88 --Log Message: 4403338
89 IF l_debug_mode = 'Y' THEN
90 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ACTION_SETS_PVT.create_action_set'
91 ,x_msg => 'Message: PA_OBJECT_HAS_ACTION_SET'
92 ,x_log_level => 5);
93 END IF;
94
95 END IF;
96
97 l_is_action_set_started := PA_ACTION_SETS_DYN.Is_Action_Set_Started_On_Apply(
98 p_action_set_type_code => p_action_set_type_code,
99 p_object_type => p_object_type,
100 p_object_id => p_object_id);
101 --Log Message: 4403338
102 IF l_debug_mode = 'Y' THEN
103 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ACTION_SETS_PVT.Create_Action_Set'
104 ,x_msg => 'l_is_action_set_started = '|| l_is_action_set_started
105 ,x_log_level => 5);
106 END IF;
107
108 IF l_is_action_set_started = 'Y' THEN
109 l_status_code := 'STARTED';
110 ELSE
111 l_status_code := 'NOT_STARTED';
112 END IF;
113
114 IF l_status_code = 'STARTED' THEN
115 l_actual_start_date := SYSDATE;
116 END IF;
117
118 END IF;
119
120 IF FND_MSG_PUB.Count_Msg =0 THEN
121
122 --Log Message: 4403338
123 IF l_debug_mode = 'Y' THEN
124 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ACTION_SETS_PVT.Create_Action_Set'
125 ,x_msg => 'before pa_action_sets_pkg.insert_row'
126 ,x_log_level => 5);
127 END IF;
128
129 PA_ACTION_SETS_PKG.insert_row
130 (p_action_set_type_code => p_action_set_type_code
131 ,p_action_set_name => p_action_set_name
132 ,p_object_type => p_object_type
133 ,p_object_id => p_object_id
134 ,p_start_date_active => p_start_date_active
135 ,p_end_date_active => p_end_date_active
136 ,p_description => p_description
137 ,p_source_action_set_id => p_source_action_set_id
138 ,p_status_code => l_status_code
139 ,p_actual_start_date => l_actual_start_date
140 ,p_action_set_template_flag => p_action_set_template_flag
141 ,p_attribute_category => p_attribute_category
142 ,p_attribute1 => p_attribute1
143 ,p_attribute2 => p_attribute2
144 ,p_attribute3 => p_attribute3
145 ,p_attribute4 => p_attribute4
146 ,p_attribute5 => p_attribute5
147 ,p_attribute6 => p_attribute6
148 ,p_attribute7 => p_attribute7
149 ,p_attribute8 => p_attribute8
150 ,p_attribute9 => p_attribute9
151 ,p_attribute10 => p_attribute10
152 ,p_attribute11 => p_attribute11
153 ,p_attribute12 => p_attribute12
154 ,p_attribute13 => p_attribute13
155 ,p_attribute14 => p_attribute14
156 ,p_attribute15 => p_attribute15
157 ,x_action_set_id => x_action_set_id
158 ,x_return_status => l_return_status);
159
160 END IF;
161
162 --Log Message: 4403338
163 IF l_debug_mode = 'Y' THEN
164 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ACTION_SETS_PVT.Create_Action_Set'
165 ,x_msg => 'x_action_set_id = '|| x_action_set_id
166 ,x_log_level => 5);
167 END IF;
168
169 IF FND_MSG_PUB.Count_Msg > 0 THEN
170 x_return_status := FND_API.G_RET_STS_ERROR;
171 END IF;
172
173 EXCEPTION
174 WHEN OTHERS THEN
175
176 -- Set the excetption Message and the stack
177 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_ACTION_SETS_PVT.Create_Action_Set'
178 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
179 --
180 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
181 RAISE;
182
183 END Create_Action_Set;
184
185
186 PROCEDURE update_action_set
187 (p_action_set_id IN pa_action_sets.action_set_id%TYPE := NULL
188 ,p_action_set_name IN pa_action_sets.action_set_name%TYPE := FND_API.G_MISS_CHAR
189 ,p_action_set_type_code IN pa_action_sets.action_set_type_code%TYPE := FND_API.G_MISS_CHAR
190 ,p_start_date_active IN pa_action_sets.start_date_active%TYPE := FND_API.G_MISS_DATE
191 ,p_end_date_active IN pa_action_sets.end_date_active%TYPE := FND_API.G_MISS_DATE
192 ,p_action_set_template_flag IN pa_action_sets.action_set_template_flag%TYPE := FND_API.G_MISS_CHAR
193 ,p_status_code IN pa_action_sets.status_code%TYPE := FND_API.G_MISS_CHAR
194 ,p_description IN pa_action_sets.description%TYPE := FND_API.G_MISS_CHAR
195 ,p_record_version_number IN pa_action_sets.record_version_number%TYPE
196 ,p_attribute_category IN pa_action_sets.attribute_category%TYPE := FND_API.G_MISS_CHAR
197 ,p_attribute1 IN pa_action_sets.attribute1%TYPE := FND_API.G_MISS_CHAR
198 ,p_attribute2 IN pa_action_sets.attribute2%TYPE := FND_API.G_MISS_CHAR
199 ,p_attribute3 IN pa_action_sets.attribute3%TYPE := FND_API.G_MISS_CHAR
200 ,p_attribute4 IN pa_action_sets.attribute4%TYPE := FND_API.G_MISS_CHAR
201 ,p_attribute5 IN pa_action_sets.attribute5%TYPE := FND_API.G_MISS_CHAR
202 ,p_attribute6 IN pa_action_sets.attribute6%TYPE := FND_API.G_MISS_CHAR
203 ,p_attribute7 IN pa_action_sets.attribute7%TYPE := FND_API.G_MISS_CHAR
204 ,p_attribute8 IN pa_action_sets.attribute8%TYPE := FND_API.G_MISS_CHAR
205 ,p_attribute9 IN pa_action_sets.attribute9%TYPE := FND_API.G_MISS_CHAR
206 ,p_attribute10 IN pa_action_sets.attribute10%TYPE := FND_API.G_MISS_CHAR
207 ,p_attribute11 IN pa_action_sets.attribute11%TYPE := FND_API.G_MISS_CHAR
208 ,p_attribute12 IN pa_action_sets.attribute12%TYPE := FND_API.G_MISS_CHAR
209 ,p_attribute13 IN pa_action_sets.attribute13%TYPE := FND_API.G_MISS_CHAR
210 ,p_attribute14 IN pa_action_sets.attribute14%TYPE := FND_API.G_MISS_CHAR
211 ,p_attribute15 IN pa_action_sets.attribute15%TYPE := FND_API.G_MISS_CHAR
212 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
213 )
214 IS
215
216 l_return_status VARCHAR2(1);
217 l_action_set_id NUMBER;
218 l_record_version_number NUMBER;
219 l_existing_action_set_id NUMBER;
220 l_previous_status pa_action_sets.status_code%TYPE;
221 l_status_code pa_action_sets.status_code%TYPE;
222 l_unique VARCHAR2(1);
223 l_actual_start_date DATE := FND_API.G_MISS_DATE;
224 l_action_set_type_code pa_action_set_types.action_set_type_code%TYPE;
225 l_action_set_template_flag VARCHAR2(1);
226 l_msg_count NUMBER;
227 l_msg_data VARCHAR2(2000);
228 l_debug_mode VARCHAR2(20) := 'N';
229
230 BEGIN
231
232 -- Initialize the return status to success
233 x_return_status := FND_API.G_RET_STS_SUCCESS;
234
235 --Log Message: 4403338
236 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
237
238 --Log Message: 4403338
239 IF l_debug_mode = 'Y' THEN
240 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ACTION_SETS_PVT.Update_Action_Set.begin'
241 ,x_msg => 'Beginning of Update_Action_Set pvt'
242 ,x_log_level => 5);
243 END IF;
244
245 SELECT action_set_type_code,
246 action_set_template_flag
247 INTO l_action_set_type_code,
248 l_action_set_template_flag
249 FROM pa_action_sets
250 WHERE action_set_id = p_action_set_id;
251
252 IF l_action_set_template_flag = 'Y' THEN
253
254 IF p_action_set_name IS NOT NULL THEN
255
256 l_unique := PA_ACTION_SET_UTILS.is_name_unique_in_type(
257 p_action_set_type_code => l_action_set_type_code,
258 p_action_set_name => p_action_set_name,
259 p_action_set_id => p_action_set_id);
260
261 IF l_unique = 'N' THEN
262 PA_UTILS.Add_Message ( p_app_short_name => 'PA'
263 ,p_msg_name => 'PA_ACTION_SET_NAME_NOT_UNIQUE');
264 END IF;
265
266 END IF;
267
268 IF p_end_date_active IS NOT NULL THEN
269 IF p_start_date_active > p_end_date_active THEN
270 PA_UTILS.Add_Message ( p_app_short_name => 'PA'
271 ,p_msg_name => 'PA_INVALID_START_DATE');
272 END IF;
273 END IF;
274
275 ELSE
276
277 IF p_status_code IS NOT NULL THEN
278 --validate that status is a valid next status
279 null;
280
281 SELECT status_code INTO l_previous_status
282 FROM pa_action_sets
283 WHERE action_set_id = p_action_set_id;
284
285 IF p_status_code = 'STARTED' AND l_previous_status <> 'STARTED' THEN
286 l_actual_start_date := SYSDATE;
287 END IF;
288
289 END IF;
290
291 END IF;
292
293 IF FND_MSG_PUB.Count_Msg =0 THEN
294
295 PA_ACTION_SETS_PKG.update_row
296 (p_action_set_id => p_action_set_id
297 ,p_action_set_name => p_action_set_name
298 ,p_start_date_active => p_start_date_active
299 ,p_end_date_active => p_end_date_active
300 ,p_description => p_description
301 ,p_status_code => p_status_code
302 ,p_actual_start_date => l_actual_start_date
303 ,p_attribute_category => p_attribute_category
304 ,p_attribute1 => p_attribute1
305 ,p_attribute2 => p_attribute2
306 ,p_attribute3 => p_attribute3
307 ,p_attribute4 => p_attribute4
308 ,p_attribute5 => p_attribute5
309 ,p_attribute6 => p_attribute6
310 ,p_attribute7 => p_attribute7
311 ,p_attribute8 => p_attribute8
312 ,p_attribute9 => p_attribute9
313 ,p_attribute10 => p_attribute10
314 ,p_attribute11 => p_attribute11
315 ,p_attribute12 => p_attribute12
316 ,p_attribute13 => p_attribute13
317 ,p_attribute14 => p_attribute14
318 ,p_attribute15 => p_attribute15
319 ,x_return_status => l_return_status);
320
321 --only need to call process action set if status is changed to stated - may
322 --be needed in order to update line numbers based on the actual start date.
323 -- 2334717: When RESUMED, should be performed as well.
324 IF p_status_code = 'STARTED' OR p_status_code = 'RESUMED'THEN
325
326 PA_ACTION_SETS_DYN.Process_Action_Set(p_action_set_type_code => l_action_set_type_code,
327 p_action_set_id => p_action_set_id,
328 p_action_set_template_flag => l_action_set_template_flag,
329 x_return_status => l_return_status);
330
331 IF l_action_set_template_flag = 'N' AND FND_MSG_PUB.Count_Msg = 0 THEN
332
333 PA_ACTION_SETS_PUB.perform_single_action_set
334 (p_action_set_id => p_action_set_id,
335 p_init_msg_list => FND_API.G_FALSE,
336 x_return_status => l_return_status,
337 x_msg_count => l_msg_count,
338 x_msg_data => l_msg_data);
339
340 END IF;
341
342 END IF;
343
344 END IF;
345
346 IF FND_MSG_PUB.Count_Msg > 0 THEN
347 x_return_status := FND_API.G_RET_STS_ERROR;
348 END IF;
349
350 EXCEPTION
351 WHEN OTHERS THEN
352
353 -- Set the excetption Message and the stack
354 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_ACTION_SETS_PVT.Update_Action_Set'
355 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
356 --
357 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
358 RAISE;
359
360 END Update_Action_Set;
361
362
363 PROCEDURE delete_action_set
364 (p_action_set_id IN pa_action_sets.action_set_id%TYPE
365 ,p_record_version_number IN pa_action_sets.record_version_number%TYPE := NULL
366 ,x_return_status OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
367 IS
368
369 l_return_status VARCHAR2(1);
370 l_msg_count NUMBER;
371 l_msg_data VARCHAR2(2000);
372 l_action_set_id NUMBER;
373 l_record_version_number NUMBER;
374 l_is_source VARCHAR2(1);
375 l_do_lines_exist VARCHAR2(1);
376 l_action_set_lines_tbl pa_action_set_utils.action_set_lines_tbl_type;
377 l_action_set_template_flag VARCHAR2(1);
378 l_debug_mode VARCHAR2(20) := 'N';
379
380 BEGIN
381
382
383 -- Initialize the return status to success
384 x_return_status := FND_API.G_RET_STS_SUCCESS;
385
386 --Log Message: 4403338
387 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
388
389 --Log Message: 4403338
390 IF l_debug_mode = 'Y' THEN
391 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ACTION_SETS_PVT.Delete_Action_Set'
392 ,x_msg => 'Beginning of Delete_Action_Set pvt'
393 ,x_log_level => 5);
394 END IF;
395
396 SELECT action_set_template_flag INTO l_action_set_template_flag
397 FROM pa_action_sets
398 WHERE action_set_id = p_action_set_id;
399
400 IF l_action_set_template_flag = 'Y' THEN
401
402 l_is_source := PA_ACTION_SET_UTILS.is_action_set_a_source(p_action_set_id => p_action_set_id);
403
404 IF l_is_source = 'Y' THEN
405 PA_UTILS.Add_Message ( p_app_short_name => 'PA'
406 ,p_msg_name => 'PA_ACTION_SET_IS_SOURCE');
407 END IF;
408
409 END IF;
410
411 IF FND_MSG_PUB.Count_Msg = 0 THEN
412
413 l_action_set_lines_tbl := pa_action_set_utils.get_action_set_lines(p_action_set_id => p_action_set_id);
414
415 IF l_action_set_lines_tbl.COUNT > 0 THEN
416
417 FOR i IN l_action_set_lines_tbl.FIRST .. l_action_set_lines_tbl.LAST LOOP
418
419 delete_action_set_line(p_action_set_line_id => l_action_set_lines_tbl(i).action_set_line_id,
420 p_record_version_number => l_action_set_lines_tbl(i).record_version_number,
421 x_return_status => l_return_status);
422
423 END LOOP;
424
425 END IF;
426
427 END IF;
428
429 IF FND_MSG_PUB.Count_Msg = 0 THEN
430
431 l_do_lines_exist := PA_ACTION_SET_UTILS.do_lines_exist(p_action_set_id => p_action_set_id);
432
433 IF l_do_lines_exist = 'N' THEN
434
435 PA_ACTION_SETS_PKG.delete_row
436 (p_action_set_id => p_action_set_id,
437 p_record_version_number => p_record_version_number,
438 x_return_status => l_return_status);
439
440 ELSE
441
442 IF l_action_set_template_flag = 'N' THEN
443
444 PA_ACTION_SETS_PUB.perform_single_action_set(p_action_set_id => p_action_set_id
445 ,p_init_msg_list => FND_API.G_FALSE
446 ,x_return_status => l_return_status
447 ,x_msg_count => l_msg_count
448 ,x_msg_data => l_msg_data);
449 END IF;
450
451
452 PA_ACTION_SETS_PKG.update_row(p_action_set_id => p_action_set_id,
453 p_record_version_number => p_record_version_number,
454 p_status_code => 'DELETED',
455 x_return_status => l_return_status);
456
457
458 END IF;
459
460 END IF;
461
462 IF FND_MSG_PUB.Count_Msg > 0 THEN
463 x_return_status := FND_API.G_RET_STS_ERROR;
464 END IF;
465
466 EXCEPTION
467 WHEN OTHERS THEN
468
469 -- Set the excetption Message and the stack
470 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_ACTION_SETS_PVT.Delete_Action_Set'
471 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
472 --
473 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
474 RAISE;
475
476 END Delete_Action_Set;
477
478
479
480
481 PROCEDURE create_action_set_line
482 (p_action_set_id IN pa_action_sets.action_set_id%TYPE
483 ,p_use_def_description_flag IN VARCHAR2 := 'Y'
484 ,p_description IN pa_action_set_lines.description%TYPE := NULL
485 ,p_action_set_line_number IN pa_action_set_lines.action_set_line_number%TYPE := NULL
486 ,p_action_code IN pa_action_set_lines.action_code%TYPE
487 ,p_action_attribute1 IN pa_action_set_lines.action_attribute1%TYPE := NULL
488 ,p_action_attribute2 IN pa_action_set_lines.action_attribute2%TYPE := NULL
489 ,p_action_attribute3 IN pa_action_set_lines.action_attribute3%TYPE := NULL
490 ,p_action_attribute4 IN pa_action_set_lines.action_attribute4%TYPE := NULL
491 ,p_action_attribute5 IN pa_action_set_lines.action_attribute5%TYPE := NULL
492 ,p_action_attribute6 IN pa_action_set_lines.action_attribute6%TYPE := NULL
493 ,p_action_attribute7 IN pa_action_set_lines.action_attribute7%TYPE := NULL
494 ,p_action_attribute8 IN pa_action_set_lines.action_attribute8%TYPE := NULL
495 ,p_action_attribute9 IN pa_action_set_lines.action_attribute9%TYPE := NULL
496 ,p_action_attribute10 IN pa_action_set_lines.action_attribute10%TYPE := NULL
497 ,p_condition_tbl IN pa_action_set_utils.action_line_cond_tbl_type
498 ,x_action_set_line_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
499 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
500 )
501 IS
502
503 l_return_status VARCHAR2(1);
504 l_msg_index_out NUMBER;
505 l_action_set_line_rec pa_action_set_lines%ROWTYPE;
506 l_action_set_type_code pa_action_set_types.action_set_type_code%TYPE;
507 l_action_set_template_flag pa_action_sets.action_set_template_flag%TYPE;
508 l_action_set_line_id NUMBER;
509 l_action_line_condition_id NUMBER;
510 l_debug_mode VARCHAR2(20) := 'N';
511
512 BEGIN
513
514 -- Initialize the return status to success
515 x_return_status := FND_API.G_RET_STS_SUCCESS;
516
517 --Log Message: 4403338
518 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
519
520 --Log Message: 4403338
521 IF l_debug_mode = 'Y' THEN
522 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ACTION_SETS_PVT.Create_Action_Set_Line.begin'
523 ,x_msg => 'Beginning of Create_Action_Set_Line pvt'
524 ,x_log_level => 5);
525 END IF;
526
527 l_action_set_line_rec.action_set_id := p_action_set_id;
528 l_action_set_line_rec.action_set_line_number := p_action_set_line_number;
529 l_action_set_line_rec.action_code := p_action_code;
530 l_action_set_line_rec.action_attribute1 := p_action_attribute1;
531 l_action_set_line_rec.action_attribute2 := p_action_attribute2;
532 l_action_set_line_rec.action_attribute3 := p_action_attribute3;
533 l_action_set_line_rec.action_attribute4 := p_action_attribute4;
534 l_action_set_line_rec.action_attribute5 := p_action_attribute5;
535 l_action_set_line_rec.action_attribute6 := p_action_attribute6;
536 l_action_set_line_rec.action_attribute7 := p_action_attribute7;
537 l_action_set_line_rec.action_attribute8 := p_action_attribute8;
538 l_action_set_line_rec.action_attribute9 := p_action_attribute9;
539 l_action_set_line_rec.action_attribute10 := p_action_attribute10;
540
541 --Log Message: 4403338
542 IF l_debug_mode = 'Y' THEN
543 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ACTION_SETS_PVT.Create_Action_Set_Line.begin'
544 ,x_msg => 'p_action_set_id = '|| p_action_set_id
545 ,x_log_level => 5);
546 END IF;
547
548 SELECT action_set_type_code, action_set_template_flag
549 INTO l_action_set_type_code, l_action_set_template_flag
550 FROM pa_action_sets
551 WHERE action_set_id = p_action_set_id;
552
553 --Log Message: 4403338
554 IF l_debug_mode = 'Y' THEN
555 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ACTION_SETS_PVT.Create_Action_Set_Line.begin'
556 ,x_msg => 'Before validate_action_set_line: '||'action_set_type_code = '||l_action_set_type_code
557 ,x_log_level => 5);
558 END IF;
559
560 PA_ACTION_SETS_DYN.Validate_Action_Set_Line(p_action_set_type_code => l_action_set_type_code,
561 p_action_set_line_rec => l_action_set_line_rec,
562 p_action_line_conditions_tbl => p_condition_tbl,
563 x_return_status => l_return_status);
564
565 --Log Message: 4403338
566 IF l_debug_mode = 'Y' THEN
567 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ACTION_SETS_PVT.Create_Action_Set_Line.begin'
568 ,x_msg => 'After validate_action_set_line: x_retun_status = '||l_return_status
569 ,x_log_level => 5);
570 END IF;
571
572 IF FND_MSG_PUB.Count_Msg = 0 THEN
573
574 PA_ACTION_SET_LINES_PKG.Insert_Row
575 (p_action_set_id => p_action_set_id
576 ,p_action_set_line_number => p_action_set_line_number
577 ,p_description => p_description
578 ,p_status_code => 'PENDING'
579 ,p_action_code => p_action_code
580 ,p_action_attribute1 => p_action_attribute1
581 ,p_action_attribute2 => p_action_attribute2
582 ,p_action_attribute3 => p_action_attribute3
583 ,p_action_attribute4 => p_action_attribute4
584 ,p_action_attribute5 => p_action_attribute5
585 ,p_action_attribute6 => p_action_attribute6
586 ,p_action_attribute7 => p_action_attribute7
587 ,p_action_attribute8 => p_action_attribute8
588 ,p_action_attribute9 => p_action_attribute9
589 ,p_action_attribute10 => p_action_attribute10
590 ,x_action_set_line_id => l_action_set_line_id
591 ,x_return_status => l_return_status);
592
593 FOR i IN p_condition_tbl.FIRST .. p_condition_tbl.LAST LOOP
594
595
596 PA_ACTION_SET_LINE_COND_PKG.Insert_Row
597 (p_action_set_line_id => l_action_set_line_id
598 ,p_condition_date => p_condition_tbl(i).condition_date
599 ,p_condition_code => p_condition_tbl(i).condition_code
600 ,p_description => p_condition_tbl(i).description
601 ,p_condition_attribute1 => p_condition_tbl(i).condition_attribute1
602 ,p_condition_attribute2 => p_condition_tbl(i).condition_attribute2
603 ,p_condition_attribute3 => p_condition_tbl(i).condition_attribute3
604 ,p_condition_attribute4 => p_condition_tbl(i).condition_attribute4
605 ,p_condition_attribute5 => p_condition_tbl(i).condition_attribute5
606 ,p_condition_attribute6 => p_condition_tbl(i).condition_attribute6
607 ,p_condition_attribute7 => p_condition_tbl(i).condition_attribute7
608 ,p_condition_attribute8 => p_condition_tbl(i).condition_attribute8
609 ,p_condition_attribute9 => p_condition_tbl(i).condition_attribute9
610 ,p_condition_attribute10 => p_condition_tbl(i).condition_attribute10
611 ,x_action_set_line_condition_id => l_action_line_condition_id
612 ,x_return_status => l_return_status);
613
614 END LOOP;
615
616 END IF;
617
618 -- If any errors exist then set the x_return_status to 'E'
619 IF FND_MSG_PUB.Count_Msg > 0 THEN
620 x_return_status := FND_API.G_RET_STS_ERROR;
621 END IF;
622
623
624 EXCEPTION
625 WHEN OTHERS THEN
626
627 -- Set the excetption Message and the stack
628 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_ACTION_SETS_PVT.Create_Action_Set_Line'
629 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
630 --
631 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
632 RAISE;
633
634 END Create_Action_Set_Line;
635
636
637 PROCEDURE update_action_set_line
638 (p_action_set_line_id IN pa_action_set_lines.action_set_line_id%TYPE
639 ,p_record_version_number IN pa_action_set_lines.record_version_number%TYPE
640 ,p_action_set_line_number IN pa_action_set_lines.action_set_line_number%TYPE := FND_API.G_MISS_NUM
641 ,p_description IN pa_action_set_lines.description%TYPE := FND_API.G_MISS_CHAR
642 ,p_action_code IN pa_action_set_lines.action_code%TYPE := FND_API.G_MISS_CHAR
643 ,p_action_attribute1 IN pa_action_set_lines.action_attribute1%TYPE := FND_API.G_MISS_CHAR
644 ,p_action_attribute2 IN pa_action_set_lines.action_attribute2%TYPE := FND_API.G_MISS_CHAR
645 ,p_action_attribute3 IN pa_action_set_lines.action_attribute3%TYPE := FND_API.G_MISS_CHAR
646 ,p_action_attribute4 IN pa_action_set_lines.action_attribute4%TYPE := FND_API.G_MISS_CHAR
647 ,p_action_attribute5 IN pa_action_set_lines.action_attribute5%TYPE := FND_API.G_MISS_CHAR
648 ,p_action_attribute6 IN pa_action_set_lines.action_attribute6%TYPE := FND_API.G_MISS_CHAR
649 ,p_action_attribute7 IN pa_action_set_lines.action_attribute7%TYPE := FND_API.G_MISS_CHAR
650 ,p_action_attribute8 IN pa_action_set_lines.action_attribute8%TYPE := FND_API.G_MISS_CHAR
651 ,p_action_attribute9 IN pa_action_set_lines.action_attribute9%TYPE := FND_API.G_MISS_CHAR
652 ,p_action_attribute10 IN pa_action_set_lines.action_attribute10%TYPE := FND_API.G_MISS_CHAR
653 ,p_condition_tbl IN pa_action_set_utils.action_line_cond_tbl_type := pa_action_set_utils.l_empty_condition_tbl
654 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
655 )
656 IS
657
658 l_return_status VARCHAR2(1);
659 l_record_version_number NUMBER;
660 l_current_line_status pa_action_set_lines.status_code%TYPE;
661 l_action_set_type_code pa_action_set_types.action_set_type_code%TYPE;
662 l_action_set_id pa_action_sets.action_set_id%TYPE;
663 l_action_set_line_rec pa_action_set_lines%ROWTYPE;
664 l_action_set_template_flag VARCHAR2(1);
665 l_debug_mode VARCHAR2(20) := 'N';
666
667 BEGIN
668
669 -- Initialize the return status to success
670 x_return_status := FND_API.G_RET_STS_SUCCESS;
671
672 --Log Message: 4403338
673 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
674
675 --Log Message: 4403338
676 IF l_debug_mode = 'Y' THEN
677 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ACTION_SETS_PVT.Update_Action_Set_Line.begin'
678 ,x_msg => 'Beginning of Update_Action_Set_Line pvt'
679 ,x_log_level => 5);
680 END IF;
681
682 SELECT sets.action_set_type_code,
683 sets.action_set_id,
684 sets.action_set_template_flag,
685 lines.status_code
686 INTO l_action_set_type_code,
687 l_action_set_id,
688 l_action_set_template_flag,
689 l_current_line_status
690 FROM pa_action_sets sets,
691 pa_action_set_lines lines
692 WHERE lines.action_set_line_id = p_action_set_line_id
693 AND sets.action_set_id = lines.action_set_id;
694
695 IF l_current_line_status = 'ACTIVE' OR l_current_line_status='COMPLETE' THEN
696 PA_UTILS.Add_Message ( p_app_short_name => 'PA'
697 ,p_msg_name => 'PA_LINE_ALREADY_PERFORMED');
698 ELSE
699
700 l_action_set_line_rec.action_set_id := l_action_set_id;
701 l_action_set_line_rec.action_set_line_number := p_action_set_line_number;
702 l_action_set_line_rec.action_code := p_action_code;
703 l_action_set_line_rec.action_attribute1 := p_action_attribute1;
704 l_action_set_line_rec.action_attribute2 := p_action_attribute2;
705 l_action_set_line_rec.action_attribute3 := p_action_attribute3;
706 l_action_set_line_rec.action_attribute4 := p_action_attribute4;
707 l_action_set_line_rec.action_attribute5 := p_action_attribute5;
708 l_action_set_line_rec.action_attribute6 := p_action_attribute6;
709 l_action_set_line_rec.action_attribute7 := p_action_attribute7;
710 l_action_set_line_rec.action_attribute8 := p_action_attribute8;
711 l_action_set_line_rec.action_attribute9 := p_action_attribute9;
712 l_action_set_line_rec.action_attribute10 := p_action_attribute10;
713
714
715 PA_ACTION_SETS_DYN.Validate_Action_Set_Line(p_action_set_type_code => l_action_set_type_code,
716 p_action_set_line_rec => l_action_set_line_rec,
717 p_action_line_conditions_tbl => p_condition_tbl,
718 x_return_status => l_return_status);
719
720
721
722 IF FND_MSG_PUB.Count_Msg = 0 THEN
723
724 PA_ACTION_SET_LINES_PKG.Update_Row
725 (p_action_set_line_id => p_action_set_line_id
726 ,p_action_set_line_number => p_action_set_line_number
727 ,p_description => p_description
728 ,p_action_code => p_action_code
729 ,p_action_attribute1 => p_action_attribute1
730 ,p_action_attribute2 => p_action_attribute2
731 ,p_action_attribute3 => p_action_attribute3
732 ,p_action_attribute4 => p_action_attribute4
733 ,p_action_attribute5 => p_action_attribute5
734 ,p_action_attribute6 => p_action_attribute6
735 ,p_action_attribute7 => p_action_attribute7
736 ,p_action_attribute8 => p_action_attribute8
737 ,p_action_attribute9 => p_action_attribute9
738 ,p_action_attribute10 => p_action_attribute10
739 ,x_return_status => l_return_status);
740
741
742 FOR i IN p_condition_tbl.FIRST .. p_condition_tbl.LAST LOOP
743
744 PA_ACTION_SET_LINE_COND_PKG.Update_Row
745 (p_action_set_line_condition_id => p_condition_tbl(i).action_set_line_condition_id
746 ,p_condition_date => p_condition_tbl(i).condition_date
747 ,p_condition_code => p_condition_tbl(i).condition_code
748 ,p_description => p_condition_tbl(i).description
749 ,p_condition_attribute1 => p_condition_tbl(i).condition_attribute1
750 ,p_condition_attribute2 => p_condition_tbl(i).condition_attribute2
751 ,p_condition_attribute3 => p_condition_tbl(i).condition_attribute3
752 ,p_condition_attribute4 => p_condition_tbl(i).condition_attribute4
753 ,p_condition_attribute5 => p_condition_tbl(i).condition_attribute5
754 ,p_condition_attribute6 => p_condition_tbl(i).condition_attribute6
755 ,p_condition_attribute7 => p_condition_tbl(i).condition_attribute7
756 ,p_condition_attribute8 => p_condition_tbl(i).condition_attribute8
757 ,p_condition_attribute9 => p_condition_tbl(i).condition_attribute9
758 ,p_condition_attribute10 => p_condition_tbl(i).condition_attribute10
759 ,x_return_status => l_return_status);
760
761 END LOOP;
762
763 END IF;
764
765 END IF;
766
767 -- If any errors exist then set the x_return_status to 'E'
768 IF FND_MSG_PUB.Count_Msg > 0 THEN
769 x_return_status := FND_API.G_RET_STS_ERROR;
770 END IF;
771
772 EXCEPTION
773 WHEN OTHERS THEN
774
775 -- Set the excetption Message and the stack
776 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_ACTION_SETS_PVT.Update_Action_Set_Line'
777 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
778 --
779 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
780 RAISE;
781
782 END Update_Action_Set_Line;
783
784
785 PROCEDURE delete_action_set_line
786 (p_action_set_line_id IN pa_action_sets.action_set_id%TYPE
787 ,p_record_version_number IN pa_action_set_lines.record_version_number%TYPE
788 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
789 )
790 IS
791
792 l_return_status VARCHAR2(1);
793 l_action_set_type_code pa_action_set_types.action_set_type_code%TYPE;
794 l_action_set_id pa_action_sets.action_set_id%TYPE;
795 l_action_set_template_flag pa_action_sets.action_set_template_flag%TYPE;
796 l_current_line_status pa_action_set_lines.status_code%TYPE;
797 l_do_audit_lines_exist VARCHAR2(1);
798 l_action_line_conditions_tbl pa_action_set_utils.action_line_cond_tbl_type;
799 l_debug_mode VARCHAR2(20) := 'N';
800
801 BEGIN
802
803 -- Initialize the return status to success
804 x_return_status := FND_API.G_RET_STS_SUCCESS;
805
806 --Log Message: 4403338
807 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
808
809 --Log Message: 4403338
810 IF l_debug_mode = 'Y' THEN
811 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ACTION_SETS_PVT.Delete_Action_Set_Line.begin'
812 ,x_msg => 'Beginning of Delete_Action_Set_Line pvt'
813 ,x_log_level => 5);
814 END IF;
815
816 SELECT sets.action_set_type_code,
817 sets.action_set_id,
818 sets.action_set_template_flag,
819 lines.status_code
820 INTO l_action_set_type_code,
821 l_action_set_id,
822 l_action_set_template_flag,
823 l_current_line_status
824 FROM pa_action_sets sets,
825 pa_action_set_lines lines
826 WHERE lines.action_set_line_id = p_action_set_line_id
827 AND sets.action_set_id = lines.action_set_id;
828
829 IF l_current_line_status = 'REVERSE_PENDING' OR l_current_line_status = 'UPDATE_PENDING' THEN
830
831 PA_UTILS.Add_Message ( p_app_short_name => 'PA'
832 ,p_msg_name => 'PA_ACTION_LINE_CHANGE_PENDING');
833
834 ELSIF l_current_line_status = 'REVERSED' THEN
835
836 -- 2411522 : Need to set MOD_SOURCE_ACTION_SET_FLAG = 'Y' when deleting a line.
837 IF l_action_set_template_flag = 'N' THEN
838 UPDATE pa_action_sets
839 SET MOD_SOURCE_ACTION_SET_FLAG = 'Y'
840 WHERE action_set_id = l_action_set_id;
841 END IF;
842
843 PA_ACTION_SET_LINES_PKG.update_row(p_action_set_line_id => p_action_set_line_id,
844 p_line_deleted_flag => 'Y',
845 x_return_status => l_return_status);
846
847 ELSE
848
849 l_do_audit_lines_exist := PA_ACTION_SET_UTILS.do_audit_lines_exist(p_action_set_line_id => p_action_set_line_id);
850 -- Bug Ref : 6797508
851 IF ( l_action_set_type_code = 'ADVERTISEMENT' ) THEN
852 IF ( l_do_audit_lines_exist = 'Y' )THEN
853 DELETE
854 FROM PA_ACTION_SET_LINE_AUD
855 WHERE ACTION_SET_LINE_ID = P_ACTION_SET_LINE_ID;
856 END IF;
857 -- 2411522 : Need to set MOD_SOURCE_ACTION_SET_FLAG = 'Y' when deleting a line.
858 l_do_audit_lines_exist :=
859 PA_ACTION_SET_UTILS.do_audit_lines_exist(p_action_set_line_id => p_action_set_line_id);
860 IF ( l_do_audit_lines_exist = 'N' AND l_current_line_status <> 'ACTIVE' ) THEN
861 IF l_action_set_template_flag = 'N' THEN
862 UPDATE pa_action_sets
863 SET MOD_SOURCE_ACTION_SET_FLAG = 'Y'
864 WHERE action_set_id = l_action_set_id;
865 END IF;
866 l_action_line_conditions_tbl :=
867 PA_ACTION_SET_UTILS.get_action_line_conditions(p_action_set_line_id => p_action_set_line_id);
868 FOR i IN l_action_line_conditions_tbl.FIRST .. l_action_line_conditions_tbl.LAST LOOP
869 PA_ACTION_SET_LINE_COND_PKG.delete_row
870 (p_action_set_line_condition_id => l_action_line_conditions_tbl(i).action_set_line_condition_id,
871 p_record_version_number => p_record_version_number,
872 x_return_status => l_return_status);
873 END LOOP;
874 PA_ACTION_SET_LINES_PKG.delete_row
875 (p_action_set_line_id => p_action_set_line_id,
876 p_record_version_number => p_record_version_number,
877 x_return_status => l_return_status);
878 ELSE
879 PA_ACTION_SET_LINES_PKG.update_row(p_action_set_line_id => p_action_set_line_id,
880 p_record_version_number => p_record_version_number,
881 p_status_code => 'REVERSE_PENDING',
882 p_line_deleted_flag => 'Y',
883 x_return_status => l_return_status);
884 END IF;
885 ELSE -- l_action_set_type_code = 'ADVERTISEMENT' ) THEN
886 IF ( l_do_audit_lines_exist = 'N' AND l_current_line_status <> 'ACTIVE' AND l_current_line_status <> 'COMPLETE' ) THEN
887 -- 2411522 : Need to set MOD_SOURCE_ACTION_SET_FLAG = 'Y' when deleting a line.
888 IF l_action_set_template_flag = 'N' THEN
889 UPDATE pa_action_sets
890 SET MOD_SOURCE_ACTION_SET_FLAG = 'Y'
891 WHERE action_set_id = l_action_set_id;
892 END IF;
893 l_action_line_conditions_tbl :=
894 PA_ACTION_SET_UTILS.get_action_line_conditions(p_action_set_line_id => p_action_set_line_id);
895 FOR i IN l_action_line_conditions_tbl.FIRST .. l_action_line_conditions_tbl.LAST LOOP
896
897 PA_ACTION_SET_LINE_COND_PKG.delete_row
898 (p_action_set_line_condition_id => l_action_line_conditions_tbl(i).action_set_line_condition_id,
899 p_record_version_number => p_record_version_number,
900 x_return_status => l_return_status);
901
902 END LOOP;
903
904 PA_ACTION_SET_LINES_PKG.delete_row
905 (p_action_set_line_id => p_action_set_line_id,
906 p_record_version_number => p_record_version_number,
907 x_return_status => l_return_status);
908
909 ELSE
910
911 PA_ACTION_SET_LINES_PKG.update_row(p_action_set_line_id => p_action_set_line_id,
912 p_record_version_number => p_record_version_number,
913 p_status_code => 'REVERSE_PENDING',
914 p_line_deleted_flag => 'Y',
915 x_return_status => l_return_status);
916
917 END IF;
918 END IF;
919
920 END IF;
921
922 IF FND_MSG_PUB.Count_Msg > 0 THEN
923 x_return_status := FND_API.G_RET_STS_ERROR;
924 END IF;
925
926 EXCEPTION
927 WHEN OTHERS THEN
928
929 -- Set the excetption Message and the stack
930 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_ACTION_SETS_PVT.Delete_Action_Set_Line'
931 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
932 --
933 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
934 RAISE;
935
936 END Delete_Action_Set_Line;
937
938
939 FUNCTION get_def_reverse_audit_lines(p_action_set_line_id IN pa_action_set_lines.action_set_line_id%TYPE,
940 p_reason IN VARCHAR2) RETURN pa_action_set_utils.insert_audit_lines_tbl_type
941 IS
942
943 l_action_set_details_rec pa_action_sets%ROWTYPE;
944
945 l_active_audit_lines_tbl pa_action_set_utils.audit_lines_tbl_type;
946 l_insert_audit_lines_tbl pa_action_set_utils.insert_audit_lines_tbl_type;
947 l_reason_code_tbl pa_action_set_utils.varchar_tbl_type;
948 l_action_code_tbl pa_action_set_utils.varchar_tbl_type;
949 l_audit_display_attribute_tbl pa_action_set_utils.varchar_tbl_type;
950 l_audit_attribute_tbl pa_action_set_utils.varchar_tbl_type;
951 l_action_date_tbl pa_action_set_utils.date_tbl_type;
952 l_active_flag_tbl pa_action_set_utils.varchar_tbl_type;
953 l_rev_action_set_line_id_tbl pa_action_set_utils.number_tbl_type;
954
955 l_return_status VARCHAR2(1);
956
957 BEGIN
958
959 l_action_set_details_rec := PA_ACTION_SET_UTILS.get_action_set_details(p_action_set_line_id => p_action_set_line_id);
960
961 l_active_audit_lines_tbl := PA_ACTION_SET_UTILS.get_active_audit_lines(p_action_set_line_id => p_action_set_line_id);
962
963 IF l_active_audit_lines_tbl.COUNT > 0 THEN
964
965 FOR i IN l_active_audit_lines_tbl.FIRST .. l_active_audit_lines_tbl.LAST LOOP
966
967 l_insert_audit_lines_tbl(i).action_code := l_active_audit_lines_tbl(i).action_code;
968 l_insert_audit_lines_tbl(i).audit_display_attribute := l_active_audit_lines_tbl(i).audit_display_attribute;
969 l_insert_audit_lines_tbl(i).audit_attribute := l_active_audit_lines_tbl(i).audit_attribute;
970 IF p_reason='REVERSED' THEN
971 l_insert_audit_lines_tbl(i).reason_code := 'DELETED';
972 ELSIF p_reason='UPDATED' THEN
973 l_insert_audit_lines_tbl(i).reason_code := 'UPDATED';
974 END IF;
975 l_insert_audit_lines_tbl(i).reversed_action_set_line_id := p_action_set_line_id;
976
977 END LOOP;
978
979 END IF;
980
981 RETURN l_insert_audit_lines_tbl;
982
983 EXCEPTION
984 WHEN OTHERS THEN
985
986 -- Set the excetption Message and the stack
987 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_ACTION_SETS_PVT.reverse_action_audit_lines'
988 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
989 --
990 RAISE;
991
992
993 END;
994
995
996 PROCEDURE bulk_insert_audit_lines(p_audit_lines_tbl IN pa_action_set_utils.insert_audit_lines_tbl_type,
997 p_action_set_line_id IN pa_action_set_lines.action_set_line_id%TYPE,
998 p_object_type IN pa_action_sets.object_type%TYPE,
999 p_object_id IN pa_action_sets.object_id%TYPE,
1000 p_action_set_type_code IN pa_action_sets.action_set_type_code%TYPE,
1001 p_status_code IN VARCHAR2,
1002 x_return_status OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
1003 IS
1004
1005 l_audit_lines_tbl pa_action_set_utils.insert_audit_lines_tbl_type;
1006 l_reason_code_tbl pa_action_set_utils.varchar_tbl_type;
1007 l_action_code_tbl pa_action_set_utils.varchar_tbl_type;
1008 l_audit_display_attribute_tbl pa_action_set_utils.varchar_tbl_type;
1009 l_audit_attribute_tbl pa_action_set_utils.varchar_tbl_type;
1010 l_rev_action_set_line_id_tbl pa_action_set_utils.number_tbl_type;
1011 l_encoded_error_message_tbl pa_action_set_utils.varchar_tbl_type;
1012
1013 BEGIN
1014
1015 IF p_status_code = 'REVERSED' THEN
1016
1017 UPDATE pa_action_set_line_aud
1018 SET active_flag = 'N'
1019 WHERE action_set_line_id = p_action_set_line_id;
1020
1021 END IF;
1022
1023 IF p_audit_lines_tbl.COUNT > 0 THEN
1024
1025 FOR i IN p_audit_lines_tbl.FIRST .. p_audit_lines_tbl.LAST LOOP
1026
1027 l_action_code_tbl(i) := p_audit_lines_tbl(i).action_code;
1028 l_reason_code_tbl(i) := p_audit_lines_tbl(i).reason_code;
1029 l_audit_display_attribute_tbl(i) := p_audit_lines_tbl(i).audit_display_attribute;
1030 l_audit_attribute_tbl(i) := p_audit_lines_tbl(i).audit_attribute;
1031 l_rev_action_set_line_id_tbl(i) := p_audit_lines_tbl(i).reversed_action_set_line_id;
1032 l_encoded_error_message_tbl(i) := p_audit_lines_tbl(i).encoded_error_message;
1033
1034 END LOOP;
1035
1036 FORALL i IN p_audit_lines_tbl.FIRST .. p_audit_lines_tbl.LAST
1037 INSERT INTO pa_action_set_line_aud
1038 (action_set_line_id,
1039 object_type,
1040 object_id,
1041 action_set_type_code,
1042 status_code,
1043 reason_code,
1044 action_code,
1045 audit_display_attribute,
1046 audit_attribute,
1047 encoded_error_message,
1048 action_date,
1049 active_flag,
1050 reversed_action_set_line_id,
1051 creation_date,
1052 created_by,
1053 last_update_date,
1054 last_updated_by,
1055 last_update_login,
1056 request_id,
1057 program_application_id,
1058 program_id,
1059 program_update_date
1060 )
1061 VALUES (p_action_set_line_id,
1062 p_object_type,
1063 p_object_id,
1064 p_action_set_type_code,
1065 p_status_code,
1066 l_reason_code_tbl(i),
1067 l_action_code_tbl(i),
1068 l_audit_display_attribute_tbl(i),
1069 l_audit_attribute_tbl(i),
1070 l_encoded_error_message_tbl(i),
1071 SYSDATE,
1072 decode(p_status_code,'REVERSED',decode(l_rev_action_set_line_id_tbl(i),NULL,'Y','N'),'Y'),
1073 l_rev_action_set_line_id_tbl(i),
1074 SYSDATE,
1075 FND_GLOBAL.user_id,
1076 SYSDATE,
1077 FND_GLOBAL.user_id,
1078 FND_GLOBAL.user_id,
1079 FND_GLOBAL.CONC_REQUEST_ID,
1080 FND_GLOBAL.PROG_APPL_ID,
1081 FND_GLOBAL.CONC_PROGRAM_ID,
1082 SYSDATE);
1083
1084 END IF;
1085
1086 EXCEPTION
1087 WHEN OTHERS THEN
1088
1089 -- Set the excetption Message and the stack
1090 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_ACTION_SETS_PVT.bulk_insert_audit_lines'
1091 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
1092 --
1093 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1094 RAISE;
1095
1096
1097 END;
1098
1099
1100 PROCEDURE bulk_update_line_number(p_action_set_line_id_tbl IN pa_action_set_utils.number_tbl_type,
1101 p_line_number_tbl IN pa_action_set_utils.number_tbl_type,
1102 x_return_status OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
1103 IS
1104
1105 TYPE rowid_tbl IS TABLE OF ROWID
1106 INDEX BY BINARY_INTEGER;
1107
1108 l_rowid_tbl rowid_tbl;
1109
1110 e_row_is_locked EXCEPTION;
1111 PRAGMA EXCEPTION_INIT(e_row_is_locked, -54);
1112
1113 BEGIN
1114
1115 IF p_action_set_line_id_tbl.COUNT > 0 THEN
1116
1117 FOR i IN p_action_set_line_id_tbl.FIRST .. p_action_set_line_id_tbl.LAST LOOP
1118
1119 SELECT rowid INTO l_rowid_tbl(i)
1120 FROM pa_action_set_lines
1121 WHERE action_set_line_id = p_action_set_line_id_tbl(i)
1122 FOR UPDATE NOWAIT;
1123
1124 END LOOP;
1125
1126 FORALL i IN l_rowid_tbl.FIRST .. l_rowid_tbl.LAST
1127 UPDATE pa_action_set_lines
1128 SET action_set_line_number = p_line_number_tbl(i)
1129 WHERE rowid = l_rowid_tbl(i);
1130
1131 END IF;
1132
1133 EXCEPTION
1134 WHEN e_row_is_locked THEN
1135 PA_UTILS.Add_Message ( p_app_short_name => 'PA'
1136 ,p_msg_name => 'PA_ACTION_LINE_CHANGE_PENDING');
1137 x_return_status := FND_API.G_RET_STS_ERROR;
1138 WHEN OTHERS THEN
1139
1140 -- Set the excetption Message and the stack
1141 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_ACTION_SETS_PVT.bulk_update_line_number'
1142 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
1143 --
1144 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1145 RAISE;
1146
1147 END;
1148
1149 PROCEDURE bulk_update_condition_date(p_action_line_condition_id_tbl IN pa_action_set_utils.number_tbl_type,
1150 p_condition_date_tbl IN pa_action_set_utils.date_tbl_type,
1151 x_return_status OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
1152 IS
1153
1154 TYPE rowid_tbl IS TABLE OF ROWID
1155 INDEX BY BINARY_INTEGER;
1156
1157 l_rowid_tbl rowid_tbl;
1158
1159 e_row_is_locked EXCEPTION;
1160 PRAGMA EXCEPTION_INIT(e_row_is_locked, -54);
1161
1162 BEGIN
1163
1164 IF p_action_line_condition_id_tbl.COUNT > 0 THEN
1165
1166 FOR i IN p_action_line_condition_id_tbl.FIRST .. p_action_line_condition_id_tbl.LAST LOOP
1167
1168 SELECT rowid INTO l_rowid_tbl(i)
1169 FROM pa_action_set_line_cond
1170 WHERE action_set_line_condition_id = p_action_line_condition_id_tbl(i)
1171 FOR UPDATE NOWAIT;
1172
1173 END LOOP;
1174
1175 FORALL i IN l_rowid_tbl.FIRST .. l_rowid_tbl.LAST
1176 UPDATE pa_action_set_line_cond
1177 SET condition_date = p_condition_date_tbl(i)
1178 WHERE rowid = l_rowid_tbl(i);
1179
1180 END IF;
1181
1182 EXCEPTION
1183 WHEN e_row_is_locked THEN
1184 PA_UTILS.Add_Message ( p_app_short_name => 'PA'
1185 ,p_msg_name => 'PA_ACTION_LINE_CHANGE_PENDING');
1186 x_return_status := FND_API.G_RET_STS_ERROR;
1187
1188 WHEN OTHERS THEN
1189 -- Set the excetption Message and the stack
1190 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_ACTION_SETS_PVT.bulk_update_condition_date'
1191 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
1192 --
1193 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1194 RAISE;
1195
1196 END;
1197
1198 PROCEDURE bulk_update_line_status(p_action_set_line_id_tbl IN pa_action_set_utils.number_tbl_type,
1199 p_line_status_tbl IN pa_action_set_utils.varchar_tbl_type,
1200 x_return_status OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
1201 IS
1202
1203 TYPE rowid_tbl IS TABLE OF ROWID
1204 INDEX BY BINARY_INTEGER;
1205
1206 l_rowid_tbl rowid_tbl;
1207
1208 e_row_is_locked EXCEPTION;
1209 PRAGMA EXCEPTION_INIT(e_row_is_locked, -54);
1210
1211 BEGIN
1212
1213 IF p_action_set_line_id_tbl.COUNT > 0 THEN
1214
1215 FOR i IN p_action_set_line_id_tbl.FIRST .. p_action_set_line_id_tbl.LAST LOOP
1216
1217 SELECT rowid INTO l_rowid_tbl(i)
1218 FROM pa_action_set_lines
1219 WHERE action_set_line_id = p_action_set_line_id_tbl(i)
1220 FOR UPDATE NOWAIT;
1221
1222 END LOOP;
1223
1224 FORALL i IN l_rowid_tbl.FIRST .. l_rowid_tbl.LAST
1225 UPDATE pa_action_set_lines
1226 SET status_code = p_line_status_tbl(i)
1227 WHERE rowid = l_rowid_tbl(i);
1228
1229 END IF;
1230
1231 EXCEPTION
1232 WHEN e_row_is_locked THEN
1233 PA_UTILS.Add_Message ( p_app_short_name => 'PA'
1234 ,p_msg_name => 'PA_ACTION_LINE_CHANGE_PENDING');
1235 x_return_status := FND_API.G_RET_STS_ERROR;
1236 WHEN OTHERS THEN
1237 -- Set the excetption Message and the stack
1238 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_ACTION_SETS_PVT.bulk_update_line_status'
1239 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
1240 --
1241 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1242 RAISE;
1243
1244 END;
1245
1246
1247 PROCEDURE perform_action_set_line
1248 (p_action_set_type_code IN pa_action_set_types.action_set_type_code%TYPE
1249 ,p_action_set_line_id IN pa_action_sets.action_set_id%TYPE
1250 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1251 )
1252 IS
1253
1254 l_return_status VARCHAR2(1);
1255 l_action_set_line_rec pa_action_set_lines%ROWTYPE;
1256 l_action_line_conditions_tbl pa_action_set_utils.action_line_cond_tbl_type;
1257 l_action_line_audit_tbl pa_action_set_utils.insert_audit_lines_tbl_type;
1258 l_action_line_complete_flag VARCHAR2(1);
1259 l_action_line_result_code VARCHAR2(240);
1260 l_action_set_details_rec pa_action_sets%ROWTYPE;
1261 l_audit_status_code VARCHAR2(30);
1262 l_new_line_status_code VARCHAR2(30);
1263 l_line_deleted_flag VARCHAR2(1);
1264 l_action_set_line_number NUMBER;
1265 L_MSG_DATA VARCHAR2(2000);
1266 l_msg_index_out NUMBER;
1267 e_invalid_result_code EXCEPTION;
1268 l_debug_mode VARCHAR2(20) := 'N';
1269
1270 BEGIN
1271
1272 -- Initialize the return status to success
1273 x_return_status := FND_API.G_RET_STS_SUCCESS;
1274
1275 --Log Message: 4403338
1276 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
1277
1278 --Log Message: 4403338
1279 IF l_debug_mode = 'Y' THEN
1280 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ACTION_SETS_PVT.Perform_Action_Set_Line.begin'
1281 ,x_msg => 'Beginning of Perform_Action_Set_Line pvt'
1282 ,x_log_level => 5);
1283 END IF;
1284
1285 l_action_set_details_rec := PA_ACTION_SET_UTILS.get_action_set_details(p_action_set_line_id => p_action_set_line_id);
1286
1287 l_action_set_line_rec := PA_ACTION_SET_UTILS.get_action_set_line(p_action_set_line_id => p_action_set_line_id);
1288
1289 l_action_line_conditions_tbl := PA_ACTION_SET_UTILS.get_action_line_conditions(p_action_set_line_id => p_action_set_line_id);
1290
1291 --Log Message: 4403338
1292 IF l_debug_mode = 'Y' THEN
1293 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ACTION_SETS_PVT.Perform_Action_Set_Line.begin'
1294 ,x_msg => 'Before calling PA_ACTION_SETS_DYN.perform_action_set_line'
1295 ,x_log_level => 5);
1296 END IF;
1297
1298 PA_ACTION_SETS_DYN.perform_action_set_line
1299 (p_action_set_type_code => p_action_set_type_code,
1300 p_action_set_details_rec => l_action_set_details_rec,
1301 p_action_set_line_rec => l_action_set_line_rec,
1302 p_action_line_conditions_tbl => l_action_line_conditions_tbl,
1303 x_action_line_audit_tbl => l_action_line_audit_tbl,
1304 x_action_line_result_code => l_action_line_result_code);
1305
1306 --Log Message: 4403338
1307 IF l_debug_mode = 'Y' THEN
1308 PA_DEBUG.write_log (x_module => 'pa.plsql.PA_ACTION_SETS_PVT.Perform_Action_Set_Line.begin'
1309 ,x_msg => 'After calling PA_ACTION_SETS_DYN.perform_action_set_line'
1310 ,x_log_level => 5);
1311 END IF;
1312
1313 IF l_action_line_result_code = PA_ACTION_SET_UTILS.G_PERFORMED_COMPLETE THEN
1314
1315 l_audit_status_code := 'PERFORMED';
1316
1317 l_new_line_status_code := 'COMPLETE';
1318
1319 ELSIF l_action_line_result_code = PA_ACTION_SET_UTILS.G_PERFORMED_ACTIVE THEN
1320
1321 l_audit_status_code := 'PERFORMED';
1322
1323 l_new_line_status_code := 'ACTIVE';
1324
1325 ELSIF l_action_line_result_code = PA_ACTION_SET_UTILS.G_REVERSED_DEFAULT_AUDIT THEN
1326
1327 l_action_line_audit_tbl := get_def_reverse_audit_lines(p_action_set_line_id => p_action_set_line_id
1328 ,p_reason => 'REVERSED');
1329
1330 l_audit_status_code := 'REVERSED';
1331
1332 l_new_line_status_code := 'REVERSED';
1333
1334 ELSIF l_action_line_result_code = PA_ACTION_SET_UTILS.G_REVERSED_CUSTOM_AUDIT THEN
1335
1336 l_audit_status_code := 'REVERSED';
1337
1338 l_new_line_status_code := 'REVERSED';
1339
1340 ELSIF l_action_line_result_code = PA_ACTION_SET_UTILS.G_UPDATED_DEFAULT_AUDIT THEN
1341
1342 l_action_line_audit_tbl := get_def_reverse_audit_lines(p_action_set_line_id => p_action_set_line_id
1343 ,p_reason => 'UPDATED');
1344
1345 l_audit_status_code := 'REVERSED';
1346
1347 l_new_line_status_code := 'PENDING';
1348
1349 ELSIF l_action_line_result_code = PA_ACTION_SET_UTILS.G_UPDATED_CUSTOM_AUDIT THEN
1350
1351 l_audit_status_code := 'REVERSED';
1352
1353 l_new_line_status_code := 'PENDING';
1354
1355 END IF;
1356
1357 /*
1358 HOW SHOULD THIS BE HANDLED???
1359 IF l_new_line_status_code = 'REVERSED' AND FND_MSG_PUB.Count_Msg > 0 THEN
1360 l_line_deleted_flag := 'N';
1361 ELSE
1362 l_line_deleted_flag := FND_API.G_MISS_CHAR;
1363 END IF;
1364 */
1365
1366
1367 IF l_action_line_result_code <> PA_ACTION_SET_UTILS.G_NOT_PERFORMED THEN
1368
1369
1370 PA_ACTION_SET_LINES_PKG.update_row
1371 (p_action_set_line_id => p_action_set_line_id,
1372 p_status_code => l_new_line_status_code,
1373 -- p_line_deleted_flag => l_line_deleted_flag,
1374 x_return_status => l_return_status);
1375
1376 bulk_insert_audit_lines
1377 (p_audit_lines_tbl => l_action_line_audit_tbl,
1378 p_action_set_line_id => p_action_set_line_id,
1379 p_object_type => l_action_set_details_rec.object_type,
1380 p_object_id => l_action_set_details_rec.object_id,
1381 p_action_set_type_code => p_action_set_type_code,
1382 p_status_code => l_audit_status_code,
1383 x_return_status => l_return_status);
1384
1385 END IF;
1386 /*
1387
1388 --only do if line not performed from concurrent program
1389 IF FND_MSG_PUB.Count_Msg > 0 AND FND_GLOBAL.CONC_REQUEST_ID = -1 THEN
1390 FOR i IN 1 .. FND_MSG_PUB.Count_Msg LOOP
1391 pa_interface_utils_pub.get_messages ( p_encoded => FND_API.G_TRUE
1392 ,p_msg_index => i
1393 ,p_data => l_msg_data
1394 ,p_msg_index_out => l_msg_index_out
1395 );
1396
1397 SELECT action_set_line_number INTO l_action_set_line_number
1398 FROM pa_action_set_lines
1399 WHERE action_set_line_id = p_action_set_line_id;
1400
1401 g_line_number_msg_tbl.EXTEND();
1402 g_info_msg_tbl.EXTEND();
1403 g_line_number_msg_tbl(g_line_number_msg_tbl.LAST) := l_action_set_line_number;
1404 g_info_msg_tbl(g_info_msg_tbl.LAST) := l_msg_data;
1405
1406 END LOOP;
1407 END IF;
1408
1409 */
1410
1411 EXCEPTION
1412 WHEN e_invalid_result_code THEN
1413 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_ACTION_SETS_PVT.Perform_Action_Set_Line'
1414 ,p_procedure_name => PA_DEBUG.G_Err_Stack
1415 ,p_error_text => 'INVALID RESULT CODE: '||l_action_line_result_code);
1416 --
1417 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1418 RAISE e_invalid_result_code;
1419
1420 WHEN OTHERS THEN
1421 -- Set the excetption Message and the stack
1422 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_ACTION_SETS_PVT.Perform_Action_Set_Line'
1423 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
1424 --
1425 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1426 RAISE;
1427
1428 END Perform_Action_Set_Line;
1429
1430
1431
1432 END;