[Home] [Help]
PACKAGE BODY: APPS.PA_PROGRESS_REPORT_PVT
Source
1 package body PA_PROGRESS_REPORT_PVT as
2 /* $Header: PAPRRPVB.pls 120.3.12000000.2 2007/04/07 11:40:37 rthumma ship $ */
3
4 PROCEDURE Create_REPORT_REGION
5 (
6 p_api_version IN NUMBER := 1.0,
7 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
8 p_commit IN VARCHAR2 := FND_API.g_false,
9 p_validate_only IN VARCHAR2 := FND_API.g_true,
10 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
11
12 p_version_id IN NUMBER,
13 P_REGION_SOURCE_TYPE in VARCHAR2 default 'STD',
14 P_REGION_CODE in VARCHAR2,
15 P_RECORD_SEQUENCE in NUMBER,
16 P_ATTRIBUTE1 in VARCHAR2,
17 P_ATTRIBUTE2 in VARCHAR2,
18 P_ATTRIBUTE3 in VARCHAR2,
19 P_ATTRIBUTE4 in VARCHAR2,
20 P_ATTRIBUTE5 in VARCHAR2,
21 P_ATTRIBUTE6 in VARCHAR2,
22 P_ATTRIBUTE7 in VARCHAR2,
23 P_ATTRIBUTE8 in VARCHAR2,
24 P_ATTRIBUTE9 in VARCHAR2,
25 P_ATTRIBUTE10 in VARCHAR2,
26 P_ATTRIBUTE11 in VARCHAR2,
27 P_ATTRIBUTE12 in VARCHAR2,
28 P_ATTRIBUTE13 in VARCHAR2,
29 P_ATTRIBUTE14 in VARCHAR2,
30 P_ATTRIBUTE15 in VARCHAR2,
31 P_ATTRIBUTE16 in VARCHAR2,
32 P_ATTRIBUTE17 in VARCHAR2,
33 P_ATTRIBUTE18 in VARCHAR2,
34 P_ATTRIBUTE19 in VARCHAR2,
35 P_ATTRIBUTE20 in VARCHAR2,
36 P_UDS_ATTRIBUTE_CATEGORY in VARCHAR2 default null,
37 P_UDS_ATTRIBUTE1 in VARCHAR2 default null,
38 P_UDS_ATTRIBUTE2 in VARCHAR2 default null,
39 P_UDS_ATTRIBUTE3 in VARCHAR2 default null,
40 P_UDS_ATTRIBUTE4 in VARCHAR2 default null,
41 P_UDS_ATTRIBUTE5 in VARCHAR2 default null,
42 P_UDS_ATTRIBUTE6 in VARCHAR2 default null,
43 P_UDS_ATTRIBUTE7 in VARCHAR2 default null,
44 P_UDS_ATTRIBUTE8 in VARCHAR2 default null,
45 P_UDS_ATTRIBUTE9 in VARCHAR2 default null,
46 P_UDS_ATTRIBUTE10 in VARCHAR2 default null,
47 P_UDS_ATTRIBUTE11 in VARCHAR2 default null,
48 P_UDS_ATTRIBUTE12 in VARCHAR2 default null,
49 P_UDS_ATTRIBUTE13 in VARCHAR2 default null,
50 P_UDS_ATTRIBUTE14 in VARCHAR2 default null,
51 P_UDS_ATTRIBUTE15 in VARCHAR2 default null,
52 P_UDS_ATTRIBUTE16 in VARCHAR2 default null,
53 P_UDS_ATTRIBUTE17 in VARCHAR2 default null,
54 P_UDS_ATTRIBUTE18 in VARCHAR2 default null,
55 P_UDS_ATTRIBUTE19 in VARCHAR2 default null,
56 P_UDS_ATTRIBUTE20 in VARCHAR2 default null,
57 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
58 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
59 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
60 ) IS
61
62 l_rowid ROWID;
63 l_record_sequence NUMBER;
64 l_page_id NUMBER;
65 l_status VARCHAR2 (30);
66
67 CURSOR get_report_status IS
68 SELECT report_status_code
69 FROM pa_progress_report_vers
70 WHERE version_id = p_version_id;
71
72 CURSOR get_template_type
73 is
74 SELECT meaning FROM pa_lookups
75 WHERE lookup_type = 'PA_PAGE_TYPES'
76 AND lookup_code = 'PPR';
77
78 l_type VARCHAR2(80); /* bug 2447763 */
79
80
81 BEGIN
82
83 -- Initialize the Error Stack
84 PA_DEBUG.init_err_stack('PA_PROGRESS_REPORT_PVT.Create_Report_Region');
85
86 -- Initialize the return status to success
87 x_return_status := FND_API.G_RET_STS_SUCCESS;
88
89 -- Issue API savepoint if the transaction is to be committed
90 IF p_commit = FND_API.G_TRUE THEN
91 SAVEPOINT Create_Report_Region;
92 END IF;
93
94
95 -- if the report in WORKING status, we continue
96 -- otherwise, we quit
97 OPEN get_report_status;
98 FETCH get_report_status INTO l_status;
99
100 CLOSE get_report_status;
101
102 IF l_status <> 'PROGRESS_REPORT_WORKING' THEN
103
104 PA_UTILS.Add_Message( p_app_short_name => 'PA'
105 ,p_msg_name => 'PA_PR_STATUS_NOT_WORKING');
106 x_return_status := FND_API.G_RET_STS_ERROR;
107
108 ELSE
109
110 IF (p_region_code IS NULL) then
111 PA_UTILS.Add_Message( p_app_short_name => 'PA'
112 ,p_msg_name => 'PA_REGION_CODE_INV');
113 x_return_status := FND_API.G_RET_STS_ERROR;
114 END IF;
115
116 END IF;
117
118 IF x_return_status <> FND_API.g_ret_sts_success THEN
119 PA_DEBUG.Reset_Err_Stack;
120 RETURN;
121 END IF;
122
123 OPEN get_template_type;
124 FETCH get_template_type INTO l_type;
125 CLOSE get_template_type;
126
127 -- check if exists in pa_object_page_versions
128
129 IF (p_version_id IS NULL) THEN
130 PA_UTILS.Add_Message( p_app_short_name => 'PA'
131 ,p_msg_name => 'PA_PR_VERSION_ID_INV'
132 , p_token1 => 'TEMPLATE_TYPE'
133 , p_value1 => l_type);
134 x_return_status := FND_API.G_RET_STS_ERROR;
135 END IF;
136
137
138 IF (x_return_status = FND_API.g_ret_sts_success AND p_validate_only <>FND_API.g_true) THEN
139 -- we insert to the pa_object_page_values
140
141 -- IF p_record_sequence IS NULL THEN
142 -- SELECT pa_object_page_versions_s.NEXTVAL
143 -- INTO l_record_sequence
144 -- FROM dual;
145 -- ELSE
146 -- l_record_sequence := p_record_sequence;
147 -- END IF;
148
149 pa_progress_report_pkg.INSERT_progress_report_VAL_ROW (
150 P_VERSION_ID,
151 P_REGION_SOURCE_TYPE,
152 P_REGION_CODE,
153 P_RECORD_SEQUENCE,
154 P_ATTRIBUTE1 ,
155 P_ATTRIBUTE2 ,
156 P_ATTRIBUTE3 ,
157 P_ATTRIBUTE4 ,
158 P_ATTRIBUTE5 ,
159 P_ATTRIBUTE6 ,
160 P_ATTRIBUTE7 ,
161 P_ATTRIBUTE8 ,
162 P_ATTRIBUTE9 ,
163 P_ATTRIBUTE10 ,
164 P_ATTRIBUTE11 ,
165 P_ATTRIBUTE12 ,
166 P_ATTRIBUTE13 ,
167 P_ATTRIBUTE14 ,
168 P_ATTRIBUTE15 ,
169 P_ATTRIBUTE16 ,
170 P_ATTRIBUTE17 ,
171 P_ATTRIBUTE18 ,
172 P_ATTRIBUTE19 ,
173 P_ATTRIBUTE20 ,
174 P_UDS_ATTRIBUTE_CATEGORY ,
175 P_UDS_ATTRIBUTE1 ,
176 P_UDS_ATTRIBUTE2 ,
177 P_UDS_ATTRIBUTE3 ,
178 P_UDS_ATTRIBUTE4 ,
179 P_UDS_ATTRIBUTE5 ,
180 P_UDS_ATTRIBUTE6 ,
181 P_UDS_ATTRIBUTE7 ,
182 P_UDS_ATTRIBUTE8 ,
183 P_UDS_ATTRIBUTE9 ,
184 P_UDS_ATTRIBUTE10 ,
185 P_UDS_ATTRIBUTE11 ,
186 P_UDS_ATTRIBUTE12 ,
187 P_UDS_ATTRIBUTE13 ,
188 P_UDS_ATTRIBUTE14 ,
189 P_UDS_ATTRIBUTE15 ,
190 P_UDS_ATTRIBUTE16 ,
191 P_UDS_ATTRIBUTE17 ,
192 P_UDS_ATTRIBUTE18 ,
193 P_UDS_ATTRIBUTE19 ,
194 P_UDS_ATTRIBUTE20 ,
195 x_return_status,
196 x_msg_count,
197 x_msg_data
198 ) ;
199
200
201 END IF;
202
203 -- update percent complete table
204
205 IF (x_return_status = FND_API.g_ret_sts_success) then
206 --IF p_region_code = 'PA_PROGRESS_PROJ_DATES_TOP_IN' THEN
207 -- todo demo
208 IF p_region_code = 'PA_PROGRESS_PROJECT_DATES' then
209 --update_project_perccomplete(p_version_id,
210 -- x_return_status,
211 -- x_msg_count,
212 -- x_msg_data);
213 NULL;
214
215 END IF;
216 END IF;
217
218 -- Commit if the flag is set and there is no error
219 IF (p_commit = FND_API.G_TRUE AND x_return_status = FND_API.g_ret_sts_success )THEN
220 COMMIT;
221 END IF;
222
223 -- Reset the error stack when returning to the calling program
224 PA_DEBUG.Reset_Err_Stack;
225
226
227 EXCEPTION
228 WHEN OTHERS THEN
229 IF p_commit = FND_API.G_TRUE THEN
230 ROLLBACK TO Create_Progress_Report;
231 END IF;
232 --
233 -- Set the excetption Message and the stack
234 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_Progress_Report_PVT.Create_Progress_Report'
235 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
236 --
237 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
238 RAISE; -- This is optional depending on the needs
239
240 END create_report_region;
241
242
243
244 PROCEDURE Update_REPORT_REGION
245 (
246
247 p_api_version IN NUMBER := 1.0,
248 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
249 p_commit IN VARCHAR2 := FND_API.g_false,
250 p_validate_only IN VARCHAR2 := FND_API.g_true,
251 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
252
253 P_VERSION_ID in NUMBER,
254 P_REGION_SOURCE_TYPE in VARCHAR2,
255 P_REGION_CODE in VARCHAR2,
256 P_RECORD_SEQUENCE in NUMBER,
257 P_RECORD_VERSION_NUMBER in NUMBER,
258 P_ATTRIBUTE1 in VARCHAR2,
259 P_ATTRIBUTE2 in VARCHAR2,
260 P_ATTRIBUTE3 in VARCHAR2,
261 P_ATTRIBUTE4 in VARCHAR2,
262 P_ATTRIBUTE5 in VARCHAR2,
263 P_ATTRIBUTE6 in VARCHAR2,
264 P_ATTRIBUTE7 in VARCHAR2,
265 P_ATTRIBUTE8 in VARCHAR2,
266 P_ATTRIBUTE9 in VARCHAR2,
267 P_ATTRIBUTE10 in VARCHAR2,
268 P_ATTRIBUTE11 in VARCHAR2,
269 P_ATTRIBUTE12 in VARCHAR2,
270 P_ATTRIBUTE13 in VARCHAR2,
271 P_ATTRIBUTE14 in VARCHAR2,
272 P_ATTRIBUTE15 in VARCHAR2,
273 P_ATTRIBUTE16 in VARCHAR2,
274 P_ATTRIBUTE17 in VARCHAR2,
275 P_ATTRIBUTE18 in VARCHAR2,
276 P_ATTRIBUTE19 in VARCHAR2,
277 P_ATTRIBUTE20 in VARCHAR2,
278 P_UDS_ATTRIBUTE_CATEGORY in VARCHAR2 ,
279 P_UDS_ATTRIBUTE1 in VARCHAR2 ,
280 P_UDS_ATTRIBUTE2 in VARCHAR2 ,
281 P_UDS_ATTRIBUTE3 in VARCHAR2 ,
282 P_UDS_ATTRIBUTE4 in VARCHAR2 ,
283 P_UDS_ATTRIBUTE5 in VARCHAR2 ,
284 P_UDS_ATTRIBUTE6 in VARCHAR2 ,
285 P_UDS_ATTRIBUTE7 in VARCHAR2 ,
286 P_UDS_ATTRIBUTE8 in VARCHAR2 ,
287 P_UDS_ATTRIBUTE9 in VARCHAR2 ,
288 P_UDS_ATTRIBUTE10 in VARCHAR2 ,
289 P_UDS_ATTRIBUTE11 in VARCHAR2 ,
290 P_UDS_ATTRIBUTE12 in VARCHAR2 ,
291 P_UDS_ATTRIBUTE13 in VARCHAR2 ,
292 P_UDS_ATTRIBUTE14 in VARCHAR2 ,
293 P_UDS_ATTRIBUTE15 in VARCHAR2 ,
294 P_UDS_ATTRIBUTE16 in VARCHAR2 ,
295 P_UDS_ATTRIBUTE17 in VARCHAR2 ,
296 P_UDS_ATTRIBUTE18 in VARCHAR2 ,
297 P_UDS_ATTRIBUTE19 in VARCHAR2 ,
298 P_UDS_ATTRIBUTE20 in VARCHAR2 ,
299 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
300 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
301 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
302 ) IS
303
304
305 l_rowid ROWID;
306
307 CURSOR check_record_version IS
308 SELECT ROWID
309 FROM PA_Progress_report_VALS
310 WHERE version_id = p_version_id
311 AND region_source_type = p_region_source_type
312 AND region_code = p_region_code
313 AND record_sequence = p_record_sequence
314 AND record_version_number = p_record_version_number;
315
316 l_status VARCHAR2 (30);
317
318 CURSOR get_report_status IS
319 SELECT report_status_code
320 FROM pa_progress_report_vers
321 WHERE version_id = p_version_id;
322
323 CURSOR get_template_type
324 is
325 SELECT meaning FROM pa_lookups
326 WHERE lookup_type = 'PA_PAGE_TYPES'
327 AND lookup_code = 'PPR';
328
329 l_type VARCHAR2(80); /* bug 2447763 */
330
331 BEGIN
332
333 -- Initialize the Error Stack
334 PA_DEBUG.init_err_stack('PA_PROGRESS_REPORT_PVT.Update_Report_Region');
335
336 -- Initialize the return status to success
337 x_return_status := FND_API.G_RET_STS_SUCCESS;
338
339 -- Issue API savepoint if the transaction is to be committed
340 IF p_commit = FND_API.G_TRUE THEN
341 SAVEPOINT Update_Report_Region;
342 END IF;
343
344 OPEN get_template_type;
345 FETCH get_template_type INTO l_type;
346 CLOSE get_template_type;
347
348 -- check mandatory version_id
349 IF (p_version_id IS NULL) then
350 PA_UTILS.Add_Message( p_app_short_name => 'PA'
351 ,p_msg_name => 'PA_PR_VERSION_ID_INV'
352 , p_token1 => 'TEMPLATE_TYPE'
353 , p_value1 => l_type);
354 x_return_status := FND_API.G_RET_STS_ERROR;
355 END IF;
356 --Check mandatory region source type
357 IF (p_region_source_type IS NULL OR
358 p_region_source_type NOT IN ('STD','DFF', 'STD_CUST')) then
359 PA_UTILS.Add_Message( p_app_short_name => 'PA'
360 ,p_msg_name => 'PA_PR_REGION_SRC_INV'
361 , p_token1 => 'TEMPLATE_TYPE'
362 , p_value1 => l_type);
363 x_return_status := FND_API.G_RET_STS_ERROR;
364 END IF;
365
366 -- check mandatory region_code
367 IF (p_region_code IS NULL) then
368 PA_UTILS.Add_Message( p_app_short_name => 'PA'
369 ,p_msg_name => 'PA_REGION_CODE_INV');
370 x_return_status := FND_API.G_RET_STS_ERROR;
371 END IF;
372
373 -- check mandatory record_sequence
374 IF (p_record_sequence IS NULL) then
375 PA_UTILS.Add_Message( p_app_short_name => 'PA'
376 ,p_msg_name => 'PA_PR_RECORD_SEQUENCE_INV'
377 , p_token1 => 'TEMPLATE_TYPE'
378 , p_value1 => l_type);
379 x_return_status := FND_API.G_RET_STS_ERROR;
380 RETURN;
381
382 END IF;
383
384
385 -- if the report in WORKING status, we continue
386 -- otherwise, we quit
387 OPEN get_report_status;
388 FETCH get_report_status INTO l_status;
389
390 CLOSE get_report_status;
391
392 IF l_status <> 'PROGRESS_REPORT_WORKING' THEN
393
394 PA_UTILS.Add_Message( p_app_short_name => 'PA'
395 ,p_msg_name => 'PA_PR_STATUS_NOT_WORKING');
396 x_return_status := FND_API.G_RET_STS_ERROR;
397 RETURN;
398
399 END IF;
400
401
402 OPEN check_record_version;
403
404 FETCH check_record_version INTO l_rowid;
405
406 IF check_record_version%NOTFOUND THEN
407
408 PA_UTILS.Add_Message( p_app_short_name => 'PA'
409 ,p_msg_name => 'PA_PR_RECORD_CHANGED');
410
411 x_return_status := FND_API.G_RET_STS_ERROR;
412
413 END IF;
414
415 CLOSE check_record_version;
416
417
418 IF (p_validate_only <>FND_API.g_true AND x_return_status = FND_API.g_ret_sts_success) THEN
419
420 --debug_msg ('In UPdate report region **********************');
421
422
423 --debug_msg ('update report P_version_id' || p_version_id);
424
425 --debug_msg ('update report P_version_id' || p_region_code);
426 --debug_msg ('update report P_version_id' || p_record_sequence);
427
428 --debug_msg ('P_ATTRIBUTE7' || P_ATTRIBUTE1);
429 --debug_msg ('P_ATTRIBUTE7' || P_ATTRIBUTE2);
430 --debug_msg ('P_ATTRIBUTE7' || P_ATTRIBUTE3);
431 --debug_msg ('P_ATTRIBUTE7' || P_ATTRIBUTE4);
432 --debug_msg ('P_ATTRIBUTE7' || P_ATTRIBUTE5);
433 --debug_msg ('P_ATTRIBUTE7' || P_ATTRIBUTE6);
434 --debug_msg ('P_ATTRIBUTE7' || P_ATTRIBUTE7);
435 --debug_msg ('P_ATTRIBUTE7' || P_ATTRIBUTE8);
436 --debug_msg ('P_ATTRIBUTE7' || P_ATTRIBUTE9);
437
438 -- we can update it now
439 PA_PROGRESS_REPORT_PKG.update_progress_report_val_row
440 (
441 P_VERSION_ID ,
442 P_REGION_SOURCE_TYPE,
443 P_REGION_CODE ,
444 P_RECORD_SEQUENCE ,
445 P_RECORD_VERSION_NUMBER ,
446 P_ATTRIBUTE1 ,
447 P_ATTRIBUTE2 ,
448 P_ATTRIBUTE3 ,
449 P_ATTRIBUTE4 ,
450 P_ATTRIBUTE5 ,
451 P_ATTRIBUTE6 ,
452 P_ATTRIBUTE7 ,
453 P_ATTRIBUTE8 ,
454 P_ATTRIBUTE9 ,
455 P_ATTRIBUTE10 ,
456 P_ATTRIBUTE11 ,
457 P_ATTRIBUTE12 ,
458 P_ATTRIBUTE13 ,
459 P_ATTRIBUTE14 ,
460 P_ATTRIBUTE15 ,
461 P_ATTRIBUTE16 ,
462 P_ATTRIBUTE17 ,
463 P_ATTRIBUTE18 ,
464 P_ATTRIBUTE19 ,
465 P_ATTRIBUTE20 ,
466 P_UDS_ATTRIBUTE_CATEGORY ,
467 P_UDS_ATTRIBUTE1 ,
468 P_UDS_ATTRIBUTE2 ,
469 P_UDS_ATTRIBUTE3 ,
470 P_UDS_ATTRIBUTE4 ,
471 P_UDS_ATTRIBUTE5 ,
472 P_UDS_ATTRIBUTE6 ,
473 P_UDS_ATTRIBUTE7 ,
474 P_UDS_ATTRIBUTE8 ,
475 P_UDS_ATTRIBUTE9 ,
476 P_UDS_ATTRIBUTE10 ,
477 P_UDS_ATTRIBUTE11 ,
478 P_UDS_ATTRIBUTE12 ,
479 P_UDS_ATTRIBUTE13 ,
480 P_UDS_ATTRIBUTE14 ,
481 P_UDS_ATTRIBUTE15 ,
482 P_UDS_ATTRIBUTE16 ,
483 P_UDS_ATTRIBUTE17 ,
484 P_UDS_ATTRIBUTE18 ,
485 P_UDS_ATTRIBUTE19 ,
486 P_UDS_ATTRIBUTE20 ,
487 x_return_status,
488 x_msg_count,
489 x_msg_data
490 );
491
492
493 END IF;
494
495 -- update percent complete table
496
497 IF (p_validate_only <>FND_API.g_true AND x_return_status = FND_API.g_ret_sts_success) THEN
498
499 --debug_msg ('update percent complete ');
500 --debug_msg ('region_code ' || p_region_code);
501 --IF p_region_code = 'PA_PROGRESS_PROJ_DATES_TOP_IN' then
502
503 --todo demo
504 IF p_region_code = 'PA_PROGRESS_PROJECT_DATES' then
505 --update_project_perccomplete(p_version_id,
506 -- x_return_status,
507 -- x_msg_count,
508 -- x_msg_data);
509 NULL;
510
511 END IF;
512 END IF;
513
514 IF (p_validate_only <>FND_API.g_true AND x_return_status = FND_API.g_ret_sts_success) THEN
515 UPDATE pa_progress_report_vers
516 SET summary_version_number = summary_version_number +1
517 WHERE version_id = p_version_id;
518
519 END IF;
520
521
522 -- Commit if the flag is set and there is no error
523 IF (p_commit = FND_API.G_TRUE AND x_return_status = FND_API.g_ret_sts_success) THEN
524 COMMIT;
525 END IF;
526
527 -- Reset the error stack when returning to the calling program
528 PA_DEBUG.Reset_Err_Stack;
529
530 COMMIT;
531
532 --debug_msg ('update percent complete: end ');
533
534 EXCEPTION
535 WHEN OTHERS THEN
536
537 --debug_msg ('update percent complete: exception ');
538 IF p_commit = FND_API.G_TRUE THEN
539 ROLLBACK TO update_report_region;
540 END IF;
541 --
542 -- Set the excetption Message and the stack
543 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_PROGRESS_REPORT_PVT.Update_Progress_Report'
544 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
545 --
546 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
547 RAISE; -- This is optional depending on the needs
548
549 END update_report_region;
550
551 PROCEDURE Delete_Report_Region
552 (
553 p_api_version IN NUMBER := 1.0,
554 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
555 p_commit IN VARCHAR2 := FND_API.g_false,
556 p_validate_only IN VARCHAR2 := FND_API.g_true,
557 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
558
559 p_version_id IN number,
560 P_REGION_SOURCE_TYPE in VARCHAR2,
561 P_REGION_CODE in VARCHAR2,
562 P_RECORD_SEQUENCE in NUMBER,
563 p_record_version_number IN NUMBER ,
564
565 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
566 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
567 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
568 ) IS
569
570 l_rowid ROWID;
571
572 CURSOR check_record_version IS
573 SELECT ROWID
574 FROM PA_progress_report_VALS
575 WHERE version_id = p_version_id
576 AND region_source_type = p_region_source_type
577 AND region_code = p_region_code
578 AND record_sequence = p_record_sequence
579 AND record_version_number = p_record_version_number;
580
581
582 l_status VARCHAR2 (30);
583
584 CURSOR get_report_status IS
585 SELECT report_status_code
586 FROM pa_progress_report_vers
587 WHERE version_id = p_version_id;
588
589 CURSOR get_template_type
590 is
591 SELECT meaning FROM pa_lookups
592 WHERE lookup_type = 'PA_PAGE_TYPES'
593 AND lookup_code = 'PPR';
594
595 l_type VARCHAR2(80); /* bug 2447763 */
596
597
598 BEGIN
599
600 -- Initialize the Error Stack
601 PA_DEBUG.init_err_stack('PA_PROGRESS_REPORT_PVT.Update_Report_Region');
602
603 -- Initialize the return status to success
604 x_return_status := FND_API.G_RET_STS_SUCCESS;
605
606 -- Issue API savepoint if the transaction is to be committed
607 IF p_commit = FND_API.G_TRUE THEN
608 SAVEPOINT Delete_Progress_Report;
609 END IF;
610
611 OPEN get_template_type;
612 FETCH get_template_type INTO l_type;
613 CLOSE get_template_type;
614
615 -- check mandatory version_id
616 IF (p_version_id IS NULL) then
617 PA_UTILS.Add_Message( p_app_short_name => 'PA'
618 ,p_msg_name => 'PA_PR_VERSION_ID_INV'
619 , p_token1 => 'TEMPLATE_TYPE'
620 , p_value1 => l_type);
621 x_return_status := FND_API.G_RET_STS_ERROR;
622 END IF;
623
624 --Check mandatory region source type
625 IF (p_region_source_type IS NULL OR
626 p_region_source_type NOT IN ('STD','DFF', 'STD_CUST')) then
627 PA_UTILS.Add_Message( p_app_short_name => 'PA'
628 ,p_msg_name => 'PA_PR_REGION_SRC_INV'
629 , p_token1 => 'TEMPLATE_TYPE'
630 , p_value1 => l_type);
631 x_return_status := FND_API.G_RET_STS_ERROR;
632 END IF;
633
634 -- check mandatory region_code
635 IF (p_region_code IS NULL) then
636 PA_UTILS.Add_Message( p_app_short_name => 'PA'
637 ,p_msg_name => 'PA_REGION_CODE_INV');
638 x_return_status := FND_API.G_RET_STS_ERROR;
639 END IF;
640
641 -- if the report in WORKING status, we continue
642 -- otherwise, we quit
643 OPEN get_report_status;
644 FETCH get_report_status INTO l_status;
645
646 CLOSE get_report_status;
647
648 IF l_status <> 'PROGRESS_REPORT_WORKING' THEN
649
650 PA_UTILS.Add_Message( p_app_short_name => 'PA'
651 ,p_msg_name => 'PA_PR_STATUS_NOT_WORKING');
652 x_return_status := FND_API.G_RET_STS_ERROR;
653 RETURN;
654
655 END IF;
656
657
658 -- check mandatory record_sequence
659 /* IF (p_record_sequence IS NULL) then
660 PA_UTILS.Add_Message( p_app_short_name => 'PA'
661 ,p_msg_name => 'PA_PR_RECORD_SEQUENCE_INV'
662 , p_token1 => 'TEMPLATE_TYPE'
663 , p_value1 => l_type);
664 x_return_status := FND_API.G_RET_STS_ERROR;
665 END IF;
666 */
667
668 IF (p_validate_only <>FND_API.g_true AND x_return_status = FND_API.g_ret_sts_success) THEN
669 IF (p_record_sequence IS null) THEN
670 pa_progress_report_pkg.delete_progress_report_region(
671 P_VERSION_ID,
672 P_REGION_SOURCE_TYPE,
673 P_REGION_CODE,
674 x_return_status,
675 x_msg_count,
676 x_msg_data
677 );
678
679 else
680 pa_progress_report_pkg.DELETE_progress_report_VAL_ROW (
681 P_VERSION_ID,
682 P_REGION_SOURCE_TYPE,
683 P_REGION_CODE,
684 P_RECORD_SEQUENCE,
685 P_RECORD_VERSION_NUMBER,
686 x_return_status,
687 x_msg_count,
688 x_msg_data);
689
690 END IF;
691
692 END IF;
693
694
695 -- Commit if the flag is set and there is no error
696 IF (p_commit = FND_API.G_TRUE AND x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
697 COMMIT;
698 END IF;
699
700 -- Reset the error stack when returning to the calling program
701 PA_DEBUG.Reset_Err_Stack;
702
703
704 EXCEPTION
705 WHEN OTHERS THEN
706 IF p_commit = FND_API.G_TRUE THEN
707 ROLLBACK TO Delete_Progress_Report;
708 END IF;
709
710 -- Set the excetption Message and the stack
711 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_PROGRESS_Report_PVT.Delete_Progress_Report'
712 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
713 --
714 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
715 RAISE; -- This is optional depending on the needs
716
717 END delete_report_region;
718
719
720 PROCEDURE cancel_report
721 (
722 p_api_version IN NUMBER := 1.0,
723 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
724 p_commit IN VARCHAR2 := FND_API.g_false,
725 p_validate_only IN VARCHAR2 := FND_API.g_true,
726 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
727
728 p_version_id IN NUMBER :=NULL,
729 p_record_version_number IN NUMBER := NULL,
730 p_cancel_comments IN VARCHAR2 := NULL,
731
732 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
733 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
734 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
735 ) IS
736
737 l_rowid ROWID;
738
739 CURSOR check_status_published IS
740 SELECT ROWID
741 FROM pa_progress_report_vers
742 WHERE version_id = p_version_id
743 AND report_status_code = 'PROGRESS_REPORT_PUBLISHED';
744
745 CURSOR get_template_type
746 is
747 SELECT meaning FROM pa_lookups
748 WHERE lookup_type = 'PA_PAGE_TYPES'
749 AND lookup_code = 'PPR';
750
751 l_type VARCHAR2(80); /* bug 2447763 */
752
753 l_object_id number;
754 l_object_type VARCHAR2(30);
755 l_version_id number;
756 l_current_flag varchar2(1);
757 l_record_version_number number;
758 l_summary_version_number number;
759 l_report_Type_id number;
760
761 CURSOR update_last_published_rep is
762 SELECT version_id,record_version_number,summary_version_number
763 FROM pa_progress_report_vers p1
764 WHERE
765 p1.object_id = l_object_id
766 AND p1.object_type = l_object_type
767 and p1.report_type_id = l_report_type_id
768 AND p1.report_status_code = 'PROGRESS_REPORT_PUBLISHED'
769 AND p1.version_id =
770 (
771 SELECT MAX(version_id)
772 FROM pa_progress_report_vers
773 WHERE
774 object_id = l_object_id
775 AND object_type = l_object_type
776 and report_type_id = l_report_type_id
777 AND report_status_code = 'PROGRESS_REPORT_PUBLISHED'
778 and current_flag <> 'Y'
779 AND report_end_Date =
780 (SELECT max(report_end_date)
781 FROM pa_progress_report_vers
782 WHERE
783 object_id = l_object_id
784 AND object_type = l_object_type
785 and report_Type_id = l_report_type_id
786 AND report_status_code = 'PROGRESS_REPORT_PUBLISHED'
787 and current_flag <> 'Y'));
788
789 cursor get_current_report_details is
790 select object_id, object_Type, current_flag, report_Type_id
791 from pa_progress_report_vers
792 where version_id = p_version_id;
793
794 BEGIN
795 -- Initialize the Error Stack
796 PA_DEBUG.init_err_stack('PA_PROGRESS_REPORT_PVT.Cancel_Report');
797
798 -- Initialize the return status to success
799 x_return_status := FND_API.G_RET_STS_SUCCESS;
800
801 -- Issue API savepoint if the transaction is to be committed
802 IF p_commit = FND_API.G_TRUE THEN
803 SAVEPOINT cancel_report;
804 END IF;
805
806 OPEN get_template_type;
807 FETCH get_template_type INTO l_type;
808 CLOSE get_template_type;
809
810 -- check mandatory version_id
811 IF (p_version_id IS NULL) then
812 PA_UTILS.Add_Message( p_app_short_name => 'PA'
813 ,p_msg_name => 'PA_PR_VERSION_ID_INV'
814 , p_token1 => 'TEMPLATE_TYPE'
815 , p_value1 => l_type);
816 x_return_status := FND_API.G_RET_STS_ERROR;
817 END IF;
818
819 -- can only obsolete the published report
820 IF x_return_status = FND_API.g_ret_sts_success THEN
821
822 OPEN check_status_published;
823 FETCH check_status_published INTO l_rowid;
824
825 IF check_status_published%NOTFOUND THEN
826 PA_UTILS.Add_Message( p_app_short_name => 'PA'
827 ,p_msg_name => 'PA_OBSOLETE_REPORT_INV');
828
829 x_return_status := FND_API.G_RET_STS_ERROR;
830
831 ELSE
832 CLOSE check_status_published;
833 END IF;
834
835 END IF;
836
837 -- change the status of report to obsoleted.
838 IF (p_validate_only <>FND_API.g_true AND x_return_status = FND_API.g_ret_sts_success) then
839
840 /* pa_progress_report_pkg.UPDATE_PROGRESS_REPORT_VER_ROW
841 (
842 p_version_id,
843 NULL,
844 NULL,
845 NULL,
846 NULL,
847 'PROGRESS_REPORT_CANCELED',
848
849 NULL ,
850 NULL ,
851 NULL,
852 NULL ,
853 NULL ,
854 NULL,
855 NULL,
856 p_cancel_comments,
857 sysdate,
858
859 p_record_version_number,
860 NULL,
861 x_return_status ,
862 x_msg_count ,
863 x_msg_data
864
865 );*/
866 open get_current_report_details;
867 fetch get_current_report_details into l_object_id, l_object_type, l_current_flag, l_report_type_id;
868 close get_current_report_details;
869
870 change_report_status
871 (
872 p_version_id => p_version_id ,
873 p_report_status => 'PROGRESS_REPORT_CANCELED',
874 p_record_version_number => p_record_version_number,
875 -- p_summary_version_number => NULL,
876 p_cancel_comment=> p_cancel_comments,
877 p_cancel_date =>Sysdate,
878
879 x_return_status => x_return_status,
880 x_msg_count => x_msg_count,
881 x_msg_data => x_msg_data
882 ) ;
883
884 END IF;
885
886 if ( x_return_status = FND_API.g_ret_sts_success )THEN
887
888 if (l_current_flag = 'Y') then
889 open update_last_published_rep;
890 fetch update_last_published_rep into l_version_id,l_RECORD_VERSION_NUMBER,l_summary_version_number;
891 IF update_last_published_rep%found then
892
893 pa_progress_report_pkg.UPDATE_PROGRESS_REPORT_VER_ROW
894 (
895 l_version_id,
896 NULL,
897 NULL,
898 NULL,
899 NULL,
900 NULL,
901 NULL,
902 NULL,
903 NULL,
904 NULL,
905 NULL,
906 'Y',
907 null,
908 null,
909 null,
910 l_RECORD_VERSION_NUMBER ,
911 l_summary_version_number ,
912 l_report_type_id,
913 x_return_status ,
914 x_msg_count ,
915 x_msg_data
916 );
917 END IF;
918 close update_last_published_rep;
919
920 end if;
921
922 end if;
923
924
925
926 -- Commit if the flag is set and there is no error
927 IF (p_commit = FND_API.G_TRUE AND x_return_status = FND_API.g_ret_sts_success )THEN
928 COMMIT;
929 END IF;
930
931 -- Reset the error stack when returning to the calling program
932 PA_DEBUG.Reset_Err_Stack;
933
934
935 EXCEPTION
936 WHEN OTHERS THEN
937 IF p_commit = FND_API.G_TRUE THEN
938 ROLLBACK TO cancel_report;
939 END IF;
940 --
941 -- Set the excetption Message and the stack
942 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_PROGRESS_REPORT_PVT.Cancel_Report'
943 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
944 --
945 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
946 RAISE; -- This is optional depending on the needs
947
948
949 END cancel_report;
950
951 PROCEDURE approve_report (
952 p_api_version IN NUMBER := 1.0,
953 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
954 p_commit IN VARCHAR2 := FND_API.g_false,
955 p_validate_only IN VARCHAR2 := FND_API.g_true,
956 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
957
958 p_version_id IN NUMBER := null,
959 p_record_version_number IN NUMBER := NULL,
960
961 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
962 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
963 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
964 )
965 IS
966
967 l_rowid rowid;
968
969 CURSOR check_status_submitted IS
970 SELECT ROWID
971 FROM pa_progress_report_vers
972 WHERE version_id = p_version_id
973 AND report_status_code = 'PROGRESS_REPORT_SUBMITTED';
974
975 CURSOR get_template_type
976 is
977 SELECT meaning FROM pa_lookups
978 WHERE lookup_type = 'PA_PAGE_TYPES'
979 AND lookup_code = 'PPR';
980
981 l_type VARCHAR2(80); /* bug 2447763 */
982
983
984 BEGIN
985 -- Initialize the Error Stack
986 PA_DEBUG.init_err_stack('PA_PROGRESS_REPORT_PVT.Approve_Report');
987
988 -- Initialize the return status to success
989 x_return_status := FND_API.G_RET_STS_SUCCESS;
990
991 -- Issue API savepoint if the transaction is to be committed
992 IF p_commit = FND_API.G_TRUE THEN
993 SAVEPOINT approve_report;
994 END IF;
995
996 OPEN get_template_type;
997 FETCH get_template_type INTO l_type;
998 CLOSE get_template_type;
999
1000 -- check mandatory version_id
1001 IF (p_version_id IS NULL) then
1002 PA_UTILS.Add_Message( p_app_short_name => 'PA'
1003 ,p_msg_name => 'PA_PR_VERSION_ID_INV'
1004 , p_token1 => 'TEMPLATE_TYPE'
1005 , p_value1 => l_type);
1006 x_return_status := FND_API.G_RET_STS_ERROR;
1007 END IF;
1008
1009 -- can only approve the submitted report
1010 IF x_return_status = FND_API.g_ret_sts_success THEN
1011
1012 OPEN check_status_submitted;
1013 FETCH check_status_submitted INTO l_rowid;
1014
1015 IF check_status_submitted%NOTFOUND THEN
1016 PA_UTILS.Add_Message( p_app_short_name => 'PA'
1017 ,p_msg_name => 'PA_PR_RECORD_CHANGED');
1018
1019 x_return_status := FND_API.G_RET_STS_ERROR;
1020
1021 ELSE
1022 CLOSE check_status_submitted;
1023 END IF;
1024
1025 END IF;
1026
1027 IF (p_validate_only <>FND_API.g_true AND x_return_status = FND_API.g_ret_sts_success) then
1028
1029 /*
1030 pa_progress_report_pkg.UPDATE_PROGRESS_REPORT_VER_ROW
1031 (
1032 p_version_id,
1033 NULL,
1034 NULL,
1035 NULL,
1036 NULL,
1037 'PROGRESS_REPORT_APPROVED',
1038
1039 NULL,
1040 NULL,
1041 NULL,
1042 NULL,
1043 NULL,
1044 NULL,
1045 NULL,
1046 NULL,
1047 NULL,
1048
1049 p_record_version_number,
1050 null,
1051 x_return_status ,
1052 x_msg_count ,
1053 x_msg_data
1054
1055 );*/
1056 change_report_status
1057 (
1058 p_version_id => p_version_id ,
1059 p_report_status => 'PROGRESS_REPORT_APPROVED',
1060 p_record_version_number => p_record_version_number,
1061 -- p_summary_version_number => NULL,
1062 --p_cancel_comment=> p_cancel_comments,
1063 --p_cancel_date =>Sysdate,
1064
1065 x_return_status => x_return_status,
1066 x_msg_count => x_msg_count,
1067 x_msg_data => x_msg_data
1068 ) ;
1069
1070
1071
1072 END IF;
1073
1074
1075 -- Commit if the flag is set and there is no error
1076 IF (p_commit = FND_API.G_TRUE AND x_return_status = FND_API.g_ret_sts_success )THEN
1077 COMMIT;
1078 END IF;
1079
1080 -- Reset the error stack when returning to the calling program
1081 PA_DEBUG.Reset_Err_Stack;
1082
1083
1084 EXCEPTION
1085 WHEN OTHERS THEN
1086 IF p_commit = FND_API.G_TRUE THEN
1087 ROLLBACK TO approve_report;
1088 END IF;
1089 --
1090 -- Set the excetption Message and the stack
1091 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_PROGRESS_REPORT_PVT.Approve_Report'
1092 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
1093 --
1094 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1095 RAISE; -- This is optional depending on the needs
1096
1097
1098 END approve_report;
1099
1100 PROCEDURE reject_report (
1101 p_api_version IN NUMBER := 1.0,
1102 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
1103 p_commit IN VARCHAR2 := FND_API.g_false,
1104 p_validate_only IN VARCHAR2 := FND_API.g_true,
1105 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
1106
1107 p_version_id IN NUMBER := null,
1108 p_record_version_number IN NUMBER := NULL,
1109
1110 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1111 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1112 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1113 )
1114 IS
1115
1116 l_rowid ROWID;
1117
1118 CURSOR check_status_submitted IS
1119 SELECT ROWID
1120 FROM pa_progress_report_vers
1121 WHERE version_id = p_version_id
1122 AND report_status_code = 'PROGRESS_REPORT_SUBMITTED';
1123
1124 CURSOR get_template_type
1125 is
1126 SELECT meaning FROM pa_lookups
1127 WHERE lookup_type = 'PA_PAGE_TYPES'
1128 AND lookup_code = 'PPR';
1129
1130 l_type VARCHAR2(80); /* bug 2447763 */
1131
1132
1133
1134 BEGIN
1135 -- Initialize the Error Stack
1136 PA_DEBUG.init_err_stack('PA_PROGRESS_REPORT_PVT.Approve_Report');
1137
1138 -- Initialize the return status to success
1139 x_return_status := FND_API.G_RET_STS_SUCCESS;
1140
1141 -- Issue API savepoint if the transaction is to be committed
1142 IF p_commit = FND_API.G_TRUE THEN
1143 SAVEPOINT reject_report;
1144 END IF;
1145
1146 OPEN get_template_type;
1147 FETCH get_template_type INTO l_type;
1148 CLOSE get_template_type;
1149
1150 -- check mandatory version_id
1151 IF (p_version_id IS NULL) then
1152 PA_UTILS.Add_Message( p_app_short_name => 'PA'
1153 ,p_msg_name => 'PA_PR_VERSION_ID_INV'
1154 , p_token1 => 'TEMPLATE_TYPE'
1155 , p_value1 => l_type);
1156 x_return_status := FND_API.G_RET_STS_ERROR;
1157 END IF;
1158
1159 -- can only reject the submitted report
1160 IF x_return_status = FND_API.g_ret_sts_success THEN
1161
1162 OPEN check_status_submitted;
1163 FETCH check_status_submitted INTO l_rowid;
1164
1165 IF check_status_submitted%NOTFOUND THEN
1166 PA_UTILS.Add_Message( p_app_short_name => 'PA'
1167 ,p_msg_name => 'PA_PR_RECORD_CHANGED');
1168
1169 x_return_status := FND_API.G_RET_STS_ERROR;
1170
1171 ELSE
1172 CLOSE check_status_submitted;
1173 END IF;
1174
1175 END IF;
1176
1177
1178 IF (p_validate_only <>FND_API.g_true AND x_return_status = FND_API.g_ret_sts_success) then
1179
1180 /*
1181 pa_progress_report_pkg.UPDATE_PROGRESS_REPORT_VER_ROW
1182 (
1183 p_version_id,
1184 NULL,
1185 NULL,
1186 NULL,
1187 NULL,
1188 'PROGRESS_REPORT_REJECTED',
1189
1190 NULL,
1191 NULL,
1192 NULL,
1193 NULL,
1194 NULL,
1195 NULL,
1196 NULL,
1197 NULL,
1198 NULL,
1199
1200 p_record_version_number,
1201 NULL,
1202 x_return_status ,
1203 x_msg_count ,
1204 x_msg_data
1205
1206
1207 );*/
1208
1209 change_report_status
1210 (
1211 p_version_id => p_version_id ,
1212 p_report_status => 'PROGRESS_REPORT_REJECTED',
1213 p_record_version_number => p_record_version_number,
1214 -- p_summary_version_number => NULL,
1215 --p_cancel_comment=> p_cancel_comments,
1216 --p_cancel_date =>Sysdate,
1217
1218 x_return_status => x_return_status,
1219 x_msg_count => x_msg_count,
1220 x_msg_data => x_msg_data
1221 ) ;
1222
1223 END IF;
1224
1225
1226 -- Commit if the flag is set and there is no error
1227 IF (p_commit = FND_API.G_TRUE AND x_return_status = FND_API.g_ret_sts_success )THEN
1228 COMMIT;
1229 END IF;
1230
1231 -- Reset the error stack when returning to the calling program
1232 PA_DEBUG.Reset_Err_Stack;
1233
1234
1235 EXCEPTION
1236 WHEN OTHERS THEN
1237 IF p_commit = FND_API.G_TRUE THEN
1238 ROLLBACK TO reject_report;
1239 END IF;
1240 --
1241 -- Set the excetption Message and the stack
1242 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_PROGRESS_REPORT_PVT.Reject_Report'
1243 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
1244 --
1245 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1246 RAISE; -- This is optional depending on the needs
1247
1248 END reject_report;
1249
1250
1251 PROCEDURE update_report_details
1252 (
1253 p_api_version IN NUMBER := 1.0,
1254 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
1255 p_commit IN VARCHAR2 := FND_API.g_false,
1256 p_validate_only IN VARCHAR2 := FND_API.g_true,
1257 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
1258
1259 p_version_id IN NUMBER := NULL,
1260
1261 p_report_start_date IN DATE:= NULL,
1262 p_report_end_date IN DATE:= NULL,
1263 p_reported_by IN NUMBER:= NULL,
1264 p_reported_by_name IN VARCHAR2:= NULL,
1265 p_progress_status IN VARCHAR2:= NULL,
1266 p_overview IN VARCHAR2:= NULL,
1267 p_record_version_number IN NUMBER := NULL,
1268
1269 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1270 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1271 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1272 )
1273 IS
1274
1275 l_reported_by NUMBER;
1276
1277 l_ret VARCHAR2(4);
1278
1279 CURSOR is_report_status_working
1280 IS
1281 SELECT Decode(report_status_code, 'PROGRESS_REPORT_WORKING','Y', 'N')
1282 FROM pa_progress_report_vers
1283 WHERE version_id = p_version_id;
1284
1285 CURSOR get_person_id
1286 IS
1287 SELECT person_id
1288 FROM pa_employees
1289 WHERE full_name = p_reported_by_name
1290 AND active = '*';
1291
1292 CURSOR get_template_type
1293 is
1294 SELECT meaning FROM pa_lookups
1295 WHERE lookup_type = 'PA_PAGE_TYPES'
1296 AND lookup_code = 'PPR';
1297
1298 l_type VARCHAR2(80); /* bug 2447763 */
1299
1300
1301 BEGIN
1302 -- Initialize the Error Stack
1303 PA_DEBUG.init_err_stack('PA_PROGRESS_REPORT_PVT.update_report_details');
1304
1305 -- Initialize the return status to success
1306 x_return_status := FND_API.G_RET_STS_SUCCESS;
1307
1308 -- Issue API savepoint if the transaction is to be committed
1309 IF p_commit = FND_API.G_TRUE THEN
1310 SAVEPOINT update_report_details;
1311 END IF;
1312
1313 OPEN get_template_type;
1314 FETCH get_template_type INTO l_type;
1315 CLOSE get_template_type;
1316
1317 -- check mandatory version_id
1318 IF (p_version_id IS NULL) then
1319 PA_UTILS.Add_Message( p_app_short_name => 'PA'
1320 ,p_msg_name => 'PA_PR_VERSION_ID_INV'
1321 , p_token1 => 'TEMPLATE_TYPE'
1322 , p_value1 => l_type);
1323 x_return_status := FND_API.G_RET_STS_ERROR;
1324 END IF;
1325
1326 --debug_msg ('before update 1' );
1327
1328 IF (Trunc(p_report_end_date) < Trunc(p_report_start_date)) THEN
1329 PA_UTILS.Add_Message( p_app_short_name => 'PA'
1330 ,p_msg_name => 'PA_REPORT_END_DATE_INV');
1331 x_return_status := FND_API.G_RET_STS_ERROR;
1332 END IF;
1333
1334 --debug_msg ('before update 1' || x_return_status );
1335
1336 --debug_msg ('before update 1' || p_reported_by_name);
1337
1338 --debug_msg ('before update reported_by ' || To_char(p_reported_by));
1339 --debug_msg ('before update reported_by ' || p_reported_by_name);
1340
1341 -- check mandatory version_id
1342 IF (p_reported_by IS NULL) THEN
1343 -- we will try to get reported_by from reported_by_name
1344 OPEN get_person_id;
1345 FETCH get_person_id INTO l_reported_by;
1346
1347 IF (get_person_id%notfound) THEN
1348 PA_UTILS.Add_Message( p_app_short_name => 'PA'
1349 ,p_msg_name => 'PA_PR_REPORTED_BY_INV');
1350 x_return_status := FND_API.G_RET_STS_ERROR;
1351 END IF;
1352 CLOSE get_person_id;
1353 ELSE
1354 l_reported_by := p_reported_by;
1355 END IF;
1356
1357 --debug_msg ('before update 1' || x_return_status );
1358 --debug_msg ('validate_only' || p_validate_only);
1359
1360 OPEN is_report_status_working;
1361 FETCH is_report_status_working INTO l_ret;
1362 CLOSE is_report_status_working;
1363
1364 IF l_ret <> 'Y' THEN
1365 PA_UTILS.Add_Message( p_app_short_name => 'PA'
1366 ,p_msg_name => 'PA_UPDATE_REPORT_INV');
1367 x_return_status := FND_API.G_RET_STS_ERROR;
1368 END IF;
1369
1370 IF (p_validate_only <>FND_API.g_true AND x_return_status = FND_API.g_ret_sts_success) then
1371
1372 --debug_msg ('before update 2' || To_char(l_reported_by));
1373 --debug_msg ('before update 3' || p_overview);
1374 --debug_msg ('before update 4' || p_progress_status);
1375
1376 pa_progress_report_pkg.UPDATE_PROGRESS_REPORT_VER_ROW
1377 (
1378 p_version_id,
1379 NULL,
1380 NULL,
1381 NULL,
1382 NULL,
1383 NULL,
1384
1385 Trunc(p_report_start_date) ,
1386 Trunc(p_report_end_date) ,
1387 l_reported_by,
1388 p_progress_status ,
1389 p_overview ,
1390 'N',
1391 NULL,
1392 NULL,
1393 NULL,
1394
1395 p_record_version_number,
1396 NULL,
1397 null,
1398 x_return_status ,
1399 x_msg_count ,
1400 x_msg_data
1401 );
1402
1403 END IF;
1404
1405
1406 -- Commit if the flag is set and there is no error
1407 IF (p_commit = FND_API.G_TRUE AND x_return_status = FND_API.g_ret_sts_success )THEN
1408 COMMIT;
1409 END IF;
1410
1411 -- Reset the error stack when returning to the calling program
1412 PA_DEBUG.Reset_Err_Stack;
1413
1414 --debug_msg ('after update in PVT ');
1415
1416 EXCEPTION
1417 WHEN OTHERS THEN
1418 --debug_msg ('exception ');
1419 IF p_commit = FND_API.G_TRUE THEN
1420 ROLLBACK TO update_report_details;
1421 END IF;
1422 --
1423 -- Set the excetption Message and the stack
1424 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_PROGRESS_REPORT_PVT.update_report_details'
1425 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
1426 --
1427 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1428 RAISE; -- This is optional depending on the needs
1429
1430 END update_report_details;
1431
1432
1433 PROCEDURE define_progress_report_setup
1434 (
1435 p_api_version IN NUMBER := 1.0,
1436 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
1437 p_commit IN VARCHAR2 := FND_API.g_false,
1438 p_validate_only IN VARCHAR2 := FND_API.g_true,
1439 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
1440
1441 p_object_id IN NUMBER := NULL,
1442 p_object_type IN VARCHAR2 := NULL,
1443 p_page_type_code IN VARCHAR2 := 'PPR',
1444 p_page_id IN NUMBER := NULL,
1445 p_page_name IN VARCHAR2 := NULL,
1446 p_approval_required IN VARCHAR2 := NULL,
1447 --p_auto_publish IN VARCHAR2 := NULL,
1448 p_report_cycle_id IN NUMBER := NULL,
1449 p_report_offset_days IN NUMBER := NULL,
1450 p_next_reporting_date IN DATE := NULL,
1451 p_reminder_days IN NUMBER := NULL,
1452 p_reminder_days_type IN VARCHAR2 := NULL,
1453 p_initial_progress_status IN VARCHAR2 := NULL,
1454 p_final_progress_status IN VARCHAR2 := NULL,
1455 p_rollup_progress_status IN VARCHAR2 := NULL,
1456 p_report_type_id IN NUMBER:= NULL,
1457 p_approver_source_id IN NUMBER:= NULL,
1458 p_approver_source_name IN VARCHAR2:= NULL,
1459 p_approver_source_type IN NUMBER:= NULL,
1460 p_effective_from IN DATE:= NULL,
1461 p_effective_to IN DATE:= NULL,
1462 p_object_page_layout_id IN NUMBER := NULL,
1463 p_action_set_id IN NUMBER := NULL,
1464 p_record_version_number IN NUMBER := NULL,
1465 p_function_name IN VARCHAR2,
1466 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1467 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1468 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1469 )
1470 IS
1471
1472 l_rowid ROWID;
1473 l_report_end_date date;
1474 l_next_reporting_date DATE;
1475 l_list_id number;
1476 x_object_page_layout_id number;
1477 l_list_item_id number;
1478 l_page_id NUMBER;
1479 page_id_l NUMBER;
1480 approval_required_l VARCHAR2(1);
1481 l_approver_source_id NUMBER;
1482 l_approver_source_type NUMBER;
1483 l_new_action_set_id NUMBER;
1484 l_object_page_layout_id NUMBER := NULL;
1485 l_return_status VARCHAR2(10) := NULL;
1486 l_msg_count NUMBER;
1487 l_msg_data VARCHAR2(10) := NULL;
1488
1489 CURSOR check_object_page_layout_exits
1490 IS
1491 SELECT page_id, approval_required
1492 FROM pa_object_page_layouts
1493 WHERE
1494 object_page_layout_id = p_object_page_layout_id;
1495
1496
1497 CURSOR get_page_id
1498 IS
1499 SELECT page_id
1500 FROM pa_page_layouts
1501 WHERE page_name = p_page_name
1502 AND page_type_code = p_page_type_code;
1503
1504 CURSOR report_type_exists
1505 IS
1506 SELECT ROWID
1507 FROM pa_report_types
1508 WHERE
1509 report_type_id = p_report_type_id;
1510
1511 BEGIN
1512
1513 -- Initialize the Error Stack
1514 PA_DEBUG.init_err_stack('PA_PROGRESS_REPORT_PVT.define_progress_report_setup');
1515 -- Initialize the return status to success
1516 x_return_status := FND_API.G_RET_STS_SUCCESS;
1517
1518 l_object_page_layout_id := p_object_page_layout_id;
1519 -- Issue API savepoint if the transaction is to be committed
1520
1521 IF p_commit = FND_API.G_TRUE THEN
1522 SAVEPOINT define_progress_report_setup;
1523 END IF;
1524
1525
1526 -- Bug# ,3302984 Added the code to handle OA Personalization.
1527 l_page_id := p_page_id;
1528 IF ((l_page_id is null) AND (p_function_name is not null)) THEN
1529
1530 PA_PAGE_LAYOUT_PUB.Create_Page_Layout(
1531 p_validate_only => fnd_api.g_false,
1532 p_page_name => p_page_name,
1533 p_page_type => p_page_type_code,
1534 p_function_name => p_function_name,
1535 p_description => null,
1536 p_start_date => sysdate,
1537 p_shortcut_menu_id => null,
1538 x_page_id => l_page_id,
1539 x_return_status => l_return_status,
1540 x_msg_count => l_msg_count,
1541 x_msg_data => l_msg_data
1542 );
1543 END IF;
1544
1545 --To keep the code in sync with the existing one, return is issued.
1546 IF (x_return_status <> FND_API.g_ret_sts_success) THEN
1547 ROLLBACK TO define_progress_report_setup;
1548 END IF;
1549
1550 -- check mandatory version_id
1551 --Bug#3302984 use l_page_id
1552 --IF (p_page_id IS NULL) THEN
1553 IF (l_page_id IS NULL) THEN
1554
1555 -- added by syao
1556 -- for PPR if there is any non published progress report
1557 -- do not allow delete
1558
1559 IF (p_page_type_code = 'PPR') THEN
1560 IF pa_progress_report_utils.is_delete_page_layout_ok(p_page_type_code, p_object_type, p_object_id, p_report_Type_id ) <> 'Y' THEN
1561 -- we have to quit,
1562 PA_UTILS.Add_Message( p_app_short_name => 'PA'
1563 ,p_msg_name => 'PA_REPORT_TYPE_REMOVE_INV');
1564 x_return_status := FND_API.G_RET_STS_ERROR;
1565 RETURN;
1566
1567 END IF;
1568
1569 END IF;
1570
1571
1572 -- if page_name is not passed in, we will remove the pagelayout
1573 IF (p_page_name IS NULL) then
1574 -- remove from pa_object_page_layouts table
1575
1576 --debug_msg('delete from pa_object_page_layouts' );
1577
1578 DELETE FROM pa_object_page_layouts
1579 WHERE object_id = p_object_id AND object_type = p_object_type
1580 AND page_type_code = p_page_type_code
1581 and nvl(report_Type_id,-99) = nvl(p_report_type_id,-99);
1582
1583 if (p_page_type_code = 'PPR') then
1584 PA_OBJECT_DIST_LISTS_PVT.DELETE_ASSOC_DIST_LISTS(p_validate_only => 'F',
1585 P_OBJECT_TYPE => 'PA_OBJECT_PAGE_LAYOUT',
1586 P_OBJECT_ID => p_object_page_layout_id,
1587 x_return_status => x_return_status,
1588 x_msg_count => x_msg_count,
1589 x_msg_data => x_msg_data);
1590
1591 pa_proj_stat_actset.delete_action_set (p_object_id => p_object_page_layout_id
1592 ,p_validate_only => 'F'
1593 ,x_return_status => x_return_status
1594 ,x_msg_count => x_msg_count
1595 ,x_msg_data => x_msg_data);
1596 end if;
1597
1598 --debug_msg('delete from pa_object_page_layouts done' );
1599
1600
1601 RETURN;
1602 ELSE
1603
1604 -- we need to check the page_name
1605 OPEN get_page_id;
1606 FETCH get_page_id INTO l_page_id;
1607
1608
1609 IF (get_page_id%notfound ) THEN
1610 PA_UTILS.Add_Message( p_app_short_name => 'PA'
1611 ,p_msg_name => 'PA_PAGE_LAYOUT_NAME_INV');
1612 x_return_status := FND_API.G_RET_STS_ERROR;
1613 RETURN;
1614
1615 ELSE
1616 -- we have the valid page_id now
1617 NULL;
1618 END if;
1619
1620 CLOSE get_page_id;
1621
1622 END IF;
1623
1624 --Bug#3302984 Commented Else.
1625 --ELSE
1626 -- l_page_id := p_page_id;
1627
1628 --PA_UTILS.Add_Message( p_app_short_name => 'PA'
1629 -- ,p_msg_name => 'PA_PAGE_ID_INV');
1630 --x_return_status := FND_API.G_RET_STS_ERROR;
1631 END IF;
1632
1633 --debug_msg('object_id ' || To_char(p_object_id));
1634 --debug_msg('object_type ' || p_object_type);
1635 --debug_msg('p_report_cycle_id ' || To_char(p_report_cycle_id));
1636 --debug_msg('p_report_cycle_id ' || To_char(p_report_offset_days));
1637
1638 --debug_msg ('approval_required:' || p_approval_required);
1639
1640 IF ( p_page_type_code = 'PPR') then
1641
1642 IF pa_progress_report_utils.is_edit_page_layout_ok(p_page_type_code, p_object_type, p_object_id, p_report_Type_id ) <> 'Y' THEN
1643 OPEN check_object_page_layout_exits;
1644 FETCH check_object_page_layout_exits INTO page_id_l, approval_required_l;
1645 close check_object_page_layout_exits;
1646 if (page_id_l <> l_page_id or approval_required_l <> p_approval_required) then
1647 -- we have to quit,
1648 PA_UTILS.Add_Message( p_app_short_name => 'PA'
1649 ,p_msg_name => 'PA_REPORT_TYPE_EDIT_INV');
1650 x_return_status := FND_API.G_RET_STS_ERROR;
1651 RETURN;
1652 end if;
1653
1654 END IF;
1655
1656 If (p_effective_to is not null and p_effective_from is null) or
1657 (p_effective_to < p_effective_from) THEN
1658 PA_UTILS.Add_Message( p_app_short_name => 'PA'
1659 ,p_msg_name => 'PA_EFFECTIVE_ED_DATE_INV');
1660 x_return_status := 'E';
1661 End If;
1662
1663 if (p_approver_source_id is null and p_approver_source_name is not null) then
1664 begin
1665 SELECT resource_source_id, resource_type_id
1666 INTO l_approver_source_id, l_approver_source_type
1667 FROM pa_people_lov_v
1668 WHERE name = p_approver_source_name;
1669 exception when TOO_MANY_ROWS then
1670 PA_UTILS.Add_Message( p_app_short_name => 'PA'
1671 ,p_msg_name => 'PA_APPR_SOURCE_NAME_MULTIPLE');
1672 x_return_status := 'E';
1673 when OTHERS then
1674 PA_UTILS.Add_Message( p_app_short_name => 'PA'
1675 ,p_msg_name => 'PA_APPR_SOURCE_NAME_INV');
1676 x_return_status := 'E';
1677 end;
1678
1679 else
1680 l_approver_source_id := p_approver_source_id;
1681 l_approver_source_type := p_approver_source_type;
1682 End if;
1683
1684 /* Bug No. 2636791 -- Changed the error message and logic for its display
1685 Now the user gets error message when approval required checkbox is checked
1686 and the user has not entered any approver name */
1687
1688 /* Bug No. 2636791 -- commented this */
1689 /* If (l_approver_source_id is not null and p_approval_required = 'N') then
1690 PA_UTILS.Add_Message( p_app_short_name => 'PA'
1691 ,p_msg_name => 'PA_PS_APPROVER_ERR');
1692 x_return_status := 'E';
1693 End If; */
1694
1695 /* Bug 2753246 no need for this check as HR manager is the default approver)
1696 If (l_approver_source_id is null and p_approval_required <> 'N') then
1697 PA_UTILS.Add_Message( p_app_short_name => 'PA'
1698 ,p_msg_name => 'PA_APPR_SOURCE_NAME_INV');
1699 x_return_status := 'E';
1700 End If; */
1701
1702
1703
1704 IF (p_next_reporting_date IS NULL AND
1705 p_report_cycle_id IS NOT null) THEN
1706 pa_progress_report_utils.get_report_start_end_dates
1707 (
1708 p_object_type,
1709 p_object_id,
1710 p_report_type_id,
1711 p_report_cycle_id,
1712 p_report_offset_days,
1713
1714 'Y',
1715 p_effective_from,
1716 l_next_reporting_date,
1717 l_report_end_date
1718
1719 --x_return_status ,
1720 --x_msg_count ,
1721 --x_msg_data
1722 );
1723 ELSE
1724 l_next_reporting_date := NULL;
1725 l_report_end_date := NULL;
1726
1727 END IF;
1728 ELSIF (p_page_type_code = 'TPR') then
1729 l_report_end_date := p_next_reporting_date;
1730 ELSE
1731 l_next_reporting_date := NULL;
1732 l_report_end_date := NULL;
1733 END IF;
1734
1735 IF (p_page_type_code = 'PPR') THEN
1736 OPEN report_type_exists;
1737 FETCH report_type_exists INTO l_rowid;
1738 IF report_type_exists%NOTFOUND THEN
1739 PA_UTILS.Add_Message( p_app_short_name => 'PA'
1740 ,p_msg_name => 'PA_PS_REPORT_TYPE_INVALID');
1741 x_return_status := FND_API.G_RET_STS_ERROR;
1742 RETURN;
1743 ELSE
1744 -- we have the valid report_type_id
1745 NULL;
1746 END if;
1747 CLOSE report_type_exists;
1748 END IF;
1749
1750 IF x_return_status = FND_API.g_ret_sts_success THEN
1751
1752 OPEN check_object_page_layout_exits;
1753 FETCH check_object_page_layout_exits INTO page_id_l, approval_required_l;
1754
1755 IF check_object_page_layout_exits%NOTFOUND THEN
1756 CLOSE check_object_page_layout_exits;
1757
1758 -- we will insert into pa_object_page_layouts
1759 IF (p_validate_only <>FND_API.g_true AND x_return_status = FND_API.g_ret_sts_success) THEN
1760
1761 pa_progress_report_pkg.insert_object_page_layout_row
1762 (
1763 P_OBJECT_ID ,
1764 P_OBJECT_TYPE ,
1765 L_PAGE_ID ,
1766 P_PAGE_TYPE_CODE ,
1767
1768 P_APPROVAL_REQUIRED ,
1769 --P_AUTO_PUBLISH ,
1770 P_REPORT_CYCLE_ID ,
1771 P_REPORT_OFFSET_DAYS ,
1772
1773 -- by msundare request, store end date in the table
1774 l_report_end_date ,
1775 P_REMINDER_DAYS ,
1776 P_REMINDER_DAYS_TYPE ,
1777 P_INITIAL_PROGRESS_STATUS,
1778 P_FINAL_PROGRESS_STATUS,
1779 P_ROLLUP_PROGRESS_STATUS,
1780 p_report_type_id,
1781 l_approver_source_id,
1782 l_approver_source_type,
1783 p_effective_from,
1784 p_effective_to,
1785 p_function_name,
1786 x_object_page_layout_id ,
1787 x_return_status ,
1788 x_msg_count ,
1789 x_msg_data
1790
1791 );
1792
1793 if (p_page_type_code = 'PPR' and x_return_status = 'S') then
1794 PA_DISTRIBUTION_LISTS_PVT.CREATE_DIST_LIST(p_validate_only => 'F',
1795 p_init_msg_list => 'F',
1796 P_LIST_ID => l_list_id,
1797 p_name => null,
1798 p_description => null,
1799 x_return_status => x_return_status,
1800 x_msg_count => x_msg_count,
1801 x_msg_data => x_msg_data);
1802
1803 if (x_return_status = 'S') then
1804 PA_DISTRIBUTION_LISTS_PVT.CREATE_DIST_LIST_ITEM(p_validate_only => 'F',
1805 p_init_msg_list => 'F',
1806 P_LIST_ITEM_ID => l_list_item_id,
1807 P_LIST_ID => l_list_id,
1808 P_RECIPIENT_TYPE => 'PROJECT_ROLE',
1809 P_RECIPIENT_ID => 1,
1810 P_ACCESS_LEVEL => 2,
1811 p_menu_id => null,
1812 x_return_status => x_return_status,
1813 x_msg_count => x_msg_count,
1814 x_msg_data => x_msg_data);
1815 end if;
1816
1817 if (x_return_status = 'S') then
1818 PA_OBJECT_DIST_LISTS_PVT.CREATE_OBJECT_DIST_LIST(p_validate_only => 'F',
1819 p_init_msg_list => 'F',
1820 P_LIST_ID => l_list_id,
1821 P_OBJECT_TYPE => 'PA_OBJECT_PAGE_LAYOUT',
1822 P_OBJECT_ID => x_object_page_layout_id,
1823 x_return_status => x_return_status,
1824 x_msg_count => x_msg_count,
1825 x_msg_data => x_msg_data);
1826 end if;
1827
1828
1829 end if;
1830
1831 END IF;
1832 -- Save object_page_layout_id to update action sets - mw
1833 l_object_page_layout_id := x_object_page_layout_id;
1834 ELSE
1835 CLOSE check_object_page_layout_exits;
1836
1837 -- we will insert into pa_object_page_layouts
1838 IF (p_validate_only <>FND_API.g_true AND x_return_status = FND_API.g_ret_sts_success) THEN
1839
1840
1841 pa_progress_report_pkg.update_object_page_layout_row
1842 (
1843 P_OBJECT_ID ,
1844 P_OBJECT_TYPE ,
1845 L_PAGE_ID ,
1846 P_PAGE_TYPE_CODE ,
1847
1848 P_APPROVAL_REQUIRED ,
1849 --P_AUTO_PUBLISH ,
1850 P_REPORT_CYCLE_ID ,
1851 P_REPORT_OFFSET_DAYS ,
1852 l_report_end_date ,
1853
1854 P_REMINDER_DAYS ,
1855 P_REMINDER_DAYS_TYPE ,
1856 P_INITIAL_PROGRESS_STATUS,
1857 P_FINAL_PROGRESS_STATUS,
1858 P_ROLLUP_PROGRESS_STATUS,
1859
1860 p_report_type_id,
1861 l_approver_source_id,
1862 l_approver_source_type,
1863 p_effective_from,
1864 p_effective_to,
1865 p_object_page_layout_id,
1866
1867 p_record_version_number,
1868 p_function_name,
1869 x_return_status ,
1870 x_msg_count ,
1871 x_msg_data
1872
1873 );
1874
1875
1876 END IF;
1877
1878 END IF;
1879
1880 END IF;
1881
1882
1883 --Add, Delete or replace action sets
1884 IF (x_return_status = 'S') THEN
1885 IF p_page_type_code = 'PPR' and l_object_page_layout_id is NOT NULL THEN
1886 PA_PROJ_STAT_ACTSET.update_action_set(
1887 p_action_set_id => p_action_set_id
1888 ,p_object_id => l_object_page_layout_id
1889 ,p_commit => p_commit
1890 ,p_validate_only => p_validate_only
1891 ,p_init_msg_list => 'F'
1892 ,x_new_action_set_id => l_new_action_set_id
1893 ,x_return_status => x_return_status
1894 ,x_msg_count => x_msg_count
1895 ,x_msg_data => x_msg_data);
1896 ELSE
1897 IF p_page_type_code = 'TPR' and l_object_page_layout_id is NOT NULL THEN
1898 PA_TASK_PROG_ACTSET.update_action_set(
1899 p_action_set_id => p_action_set_id
1900 ,p_object_id => p_object_id
1901 ,p_commit => p_commit
1902 ,p_validate_only => p_validate_only
1903 ,p_init_msg_list => 'F'
1904 ,x_new_action_set_id => l_new_action_set_id
1905 ,x_return_status => x_return_status
1906 ,x_msg_count => x_msg_count
1907 ,x_msg_data => x_msg_data);
1908 END IF;
1909 END IF;
1910 END IF;
1911
1912 -- Commit if the flag is set and there is no error
1913 IF (p_commit = FND_API.G_TRUE AND x_return_status = FND_API.g_ret_sts_success )THEN
1914 COMMIT;
1915 END IF;
1916
1917 if (x_msg_count >= 1) then
1918 x_return_status := FND_API.g_ret_sts_Error;
1919 end if;
1920
1921 -- Reset the error stack when returning to the calling program
1922 PA_DEBUG.Reset_Err_Stack;
1923
1924
1925 EXCEPTION
1926 WHEN OTHERS THEN
1927 IF p_commit = FND_API.G_TRUE THEN
1928 ROLLBACK TO define_progress_report_setup;
1929 END IF;
1930 --
1931 -- Set the excetption Message and the stack
1932 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_PROGRESS_REPORT_PVT.define_progress_report_setup'
1933 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
1934 --
1935 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1936 RAISE; -- This is optional depending on the needs
1937
1938
1939
1940 END define_progress_report_setup;
1941
1942 PROCEDURE delete_report
1943 (
1944 p_api_version IN NUMBER := 1.0,
1945 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
1946 p_commit IN VARCHAR2 := FND_API.g_false,
1947 p_validate_only IN VARCHAR2 := FND_API.g_true,
1948 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
1949
1950 p_version_id IN NUMBER :=NULL,
1951 p_record_version_number IN NUMBER := NULL,
1952
1953 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1954 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1955 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1956 )
1957
1958 IS
1959
1960 l_rowid ROWID;
1961 l_item_key wf_item_activity_statuses.item_key%TYPE; --Bug 5217292
1962 l_wf_status VARCHAR2(30);
1963 l_dummy VARCHAR2(1);
1964
1965 l_item_type VARCHAR2(30);
1966
1967
1968 CURSOR get_item_key IS
1969 SELECT MAX(pwp.item_key), max(pwp.item_type)
1970 from pa_wf_processes pwp, pa_project_statuses pps,
1971 pa_progress_report_vers pprv
1972 where pwp.item_type = pps.WORKFLOW_ITEM_TYPE
1973 and pps.status_type = 'PROGRESS_REPORT'
1974 and pps.project_status_code = 'PROGRESS_REPORT_SUBMITTED'
1975 AND entity_key2 = p_version_id
1976 AND pwp.wf_type_code = 'Progress Report'
1977 AND entity_key1 = pprv.object_id
1978 AND pprv.version_id = p_version_id
1979 AND pprv.object_type = 'PA_PROJECTS';
1980
1981 -- CURSOR get_wf_status IS
1982 -- select 'Y' FROM dual
1983 -- WHERE exists
1984 -- (SELECT *
1985 -- from wf_item_activity_statuses
1986 -- WHERE item_type = 'PAWFPPRA'
1987 -- AND item_key = l_item_key
1988 -- AND activity_status = 'ACTIVE');
1989 CURSOR get_wf_status IS
1990 select 'Y' FROM dual
1991 WHERE exists
1992 (SELECT *
1993 from wf_item_activity_statuses wias, pa_project_statuses pps
1994 WHERE wias.item_type = pps.WORKFLOW_ITEM_TYPE
1995 AND wias.item_key = l_item_key
1996 AND wias.activity_status = 'ACTIVE'
1997 AND pps.status_type = 'PROGRESS_REPORT'
1998 AND pps.project_status_code = 'PROGRESS_REPORT_SUBMITTED');
1999
2000
2001 CURSOR get_template_type
2002 is
2003 SELECT meaning FROM pa_lookups
2004 WHERE lookup_type = 'PA_PAGE_TYPES'
2005 AND lookup_code = 'PPR';
2006
2007
2008 CURSOR check_status_submitted IS
2009 SELECT ROWID
2010 FROM pa_progress_report_vers
2011 WHERE version_id = p_version_id
2012 AND report_status_code = 'PROGRESS_REPORT_SUBMITTED';
2013
2014
2015 l_type VARCHAR2(80); /* bug 2447763 */
2016
2017
2018 BEGIN
2019 -- Initialize the Error Stack
2020 PA_DEBUG.init_err_stack('PA_PROGRESS_REPORT_PVT.Delete_Report');
2021
2022 -- Initialize the return status to success
2023 x_return_status := FND_API.G_RET_STS_SUCCESS;
2024
2025 -- Issue API savepoint if the transaction is to be committed
2026 IF p_commit = FND_API.G_TRUE THEN
2027 SAVEPOINT delete_report;
2028 END IF;
2029
2030 OPEN get_template_type;
2031 FETCH get_template_type INTO l_type;
2032 CLOSE get_template_type;
2033
2034 -- check mandatory version_id
2035 IF (p_version_id IS NULL) then
2036 PA_UTILS.Add_Message( p_app_short_name => 'PA'
2037 ,p_msg_name => 'PA_PR_VERSION_ID_INV'
2038 , p_token1 => 'TEMPLATE_TYPE'
2039 , p_value1 => l_type);
2040 x_return_status := FND_API.G_RET_STS_ERROR;
2041 END IF;
2042
2043
2044 -- cancel the workflow which if it is already launched.
2045 open check_status_submitted;
2046 FETCH check_status_submitted INTO l_rowid;
2047
2048 IF check_status_submitted%FOUND THEN
2049 CLOSE check_status_submitted;
2050 OPEN get_item_key;
2051 FETCH get_item_key INTO l_item_key, l_item_type;
2052
2053 IF get_item_key%found THEN
2054 CLOSE get_item_key;
2055
2056 -- is the workflow running
2057 -- only cancel when the workflow is running
2058
2059 OPEN get_wf_status;
2060 FETCH get_wf_status INTO l_wf_status;
2061
2062 IF (get_wf_status%notfound or
2063 l_wf_status <> 'Y' ) THEN
2064 NULL;
2065
2066 else
2067
2068 pa_progress_report_workflow.cancel_workflow
2069 (
2070 l_item_type
2071 , l_item_key
2072 , x_msg_count
2073 , x_msg_data
2074 , x_return_status
2075 );
2076
2077 IF (l_dummy = 'N') THEN
2078 x_return_status := FND_API.G_RET_STS_SUCCESS;
2079 END IF;
2080
2081 END IF;
2082
2083 CLOSE get_wf_status;
2084
2085 ELSE
2086
2087 CLOSE get_item_key;
2088 -- does not find item key for the workflow
2089 -- return
2090
2091 -- if workflow is required, we are canceling the submission
2092 -- failed, return error.
2093 -- IF (l_dummy = 'Y' or l_dummy = 'A' )THEN
2094 -- PA_UTILS.Add_Message( p_app_short_name => 'PA'
2095 -- ,p_msg_name => 'PA_PR_CANCEL_WORKFLOW_INV');
2096 -- x_return_status := FND_API.G_RET_STS_ERROR;
2097 NULL;
2098 END IF;
2099 ELSE
2100 CLOSE check_status_submitted;
2101
2102 END IF;
2103
2104
2105 IF (p_validate_only <>FND_API.g_true AND x_return_status = FND_API.g_ret_sts_success) then
2106
2107 -- delete all records in pa_progress_report_vals under the p_version_id
2108 pa_progress_report_pkg.delete_progress_report_vals
2109 (
2110 p_version_id,
2111 x_return_status ,
2112 x_msg_count ,
2113 x_msg_data
2114 );
2115
2116 IF (x_return_status = FND_API.g_ret_sts_success) THEN
2117
2118 -- delete record in pa_progress_report_vers under the p_version_id
2119 pa_progress_report_pkg.delete_progress_report_ver_row
2120 (
2121 p_version_id,
2122 p_record_version_number,
2123 x_return_status ,
2124 x_msg_count ,
2125 x_msg_data
2126 );
2127
2128 END IF;
2129
2130 END IF;
2131
2132
2133 -- Commit if the flag is set and there is no error
2134 IF (p_commit = FND_API.G_TRUE AND x_return_status = FND_API.g_ret_sts_success )THEN
2135 COMMIT;
2136 END IF;
2137
2138 -- Reset the error stack when returning to the calling program
2139 PA_DEBUG.Reset_Err_Stack;
2140
2141
2142 EXCEPTION
2143 WHEN OTHERS THEN
2144 IF p_commit = FND_API.G_TRUE THEN
2145 ROLLBACK TO delete_report;
2146 END IF;
2147 --
2148 -- Set the excetption Message and the stack
2149 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_PROGRESS_REPORT_PVT.Delete_Report'
2150 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
2151 --
2152 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2153 RAISE; -- This is optional depending on the needs
2154
2155 END delete_report;
2156
2157 PROCEDURE rework_report
2158 (
2159 p_api_version IN NUMBER := 1.0,
2160 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
2161 p_commit IN VARCHAR2 := FND_API.g_false,
2162 p_validate_only IN VARCHAR2 := FND_API.g_true,
2163 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
2164
2165 p_version_id IN NUMBER := NULL,
2166 p_record_version_number IN NUMBER := NULL,
2167
2168 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2169 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2170 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2171 ) IS
2172
2173 l_rowid ROWID;
2174 l_item_key wf_item_activity_statuses.item_key%TYPE; --Bug 5217292
2175 l_wf_status VARCHAR2(30);
2176 l_item_type VARCHAR2(30);
2177 l_dummy VARCHAR2(30) := 'N';
2178
2179 CURSOR check_auto_approve IS
2180 SELECT 'Y' FROM dual
2181 WHERE exists
2182 (
2183 SELECT p1.approval_required
2184 FROM pa_object_page_layouts p1,
2185 pa_progress_report_vers p2
2186 WHERE
2187 p2.version_id = p_version_id
2188 AND p2.object_id = p1.object_id
2189 AND p2.object_type = p1.object_type
2190 AND (p1.approval_required = 'Y'
2191 OR p1.approval_required = 'A'
2192 )
2193 );
2194
2195
2196 CURSOR get_item_key IS
2197 SELECT MAX(pwp.item_key), max(pwp.item_type)
2198 from pa_wf_processes pwp, pa_project_statuses pps,
2199 pa_progress_report_vers pprv
2200 where pwp.item_type = pps.WORKFLOW_ITEM_TYPE
2201 and pps.status_type = 'PROGRESS_REPORT'
2202 and pps.project_status_code = 'PROGRESS_REPORT_SUBMITTED'
2203 AND entity_key2 = p_version_id
2204 AND entity_key1 = pprv.object_id
2205 AND wf_type_code = 'Progress Report'
2206 AND pprv.version_id = p_version_id
2207 AND pprv.object_type = 'PA_PROJECTS';
2208
2209 -- CURSOR get_wf_status IS
2210 -- select 'Y' FROM dual
2211 -- WHERE exists
2212 -- (SELECT *
2213 -- from wf_item_activity_statuses
2214 -- WHERE item_type = 'PAWFPPRA'
2215 -- AND item_key = l_item_key
2216 -- AND activity_status = 'ACTIVE');
2217
2218 CURSOR get_wf_status IS
2219 select 'Y' FROM dual
2220 WHERE exists
2221 (SELECT *
2222 from wf_item_activity_statuses wias, pa_project_statuses pps
2223 WHERE wias.item_type = pps.WORKFLOW_ITEM_TYPE
2224 AND wias.item_key = l_item_key
2225 AND wias.activity_status = 'ACTIVE'
2226 AND pps.status_type = 'PROGRESS_REPORT'
2227 AND pps.project_status_code = 'PROGRESS_REPORT_SUBMITTED');
2228
2229 CURSOR get_template_type
2230 is
2231 SELECT meaning FROM pa_lookups
2232 WHERE lookup_type = 'PA_PAGE_TYPES'
2233 AND lookup_code = 'PPR';
2234
2235 l_type VARCHAR2(80); /* bug 2447763 */
2236
2237 BEGIN
2238 -- Initialize the Error Stack
2239 PA_DEBUG.init_err_stack('PA_PROGRESS_REPORT_PVT.Rework_Report');
2240
2241 -- Initialize the return status to success
2242 x_return_status := FND_API.G_RET_STS_SUCCESS;
2243
2244 -- Issue API savepoint if the transaction is to be committed
2245 SAVEPOINT rework_report;
2246
2247 OPEN get_template_type;
2248 FETCH get_template_type INTO l_type;
2249 CLOSE get_template_type;
2250
2251 -- check mandatory version_id
2252 IF (p_version_id IS NULL) then
2253 PA_UTILS.Add_Message( p_app_short_name => 'PA'
2254 ,p_msg_name => 'PA_PR_VERSION_ID_INV'
2255 , p_token1 => 'TEMPLATE_TYPE'
2256 , p_value1 => l_type);
2257 x_return_status := FND_API.G_RET_STS_ERROR;
2258 END IF;
2259
2260
2261 IF (p_validate_only <>FND_API.g_true AND x_return_status = FND_API.g_ret_sts_success) then
2262
2263 -- set the status to working first
2264
2265 /*
2266 pa_progress_report_pkg.UPDATE_PROGRESS_REPORT_VER_ROW
2267 (
2268 p_version_id ,
2269 NULL,
2270 NULL,
2271 NULL,
2272 NULL,
2273 'PROGRESS_REPORT_WORKING',
2274
2275
2276 NULL,
2277 NULL,
2278 NULL,
2279 NULL,
2280 NULL,
2281 NULL,
2282 NULL,
2283 NULL,
2284 NULL,
2285
2286 P_RECORD_VERSION_NUMBER ,
2287 NULL,
2288 x_return_status ,
2289 x_msg_count ,
2290 x_msg_data
2291 );*/
2292 change_report_status
2293 (
2294 p_version_id => p_version_id ,
2295 p_report_status => 'PROGRESS_REPORT_WORKING',
2296 p_record_version_number => p_record_version_number,
2297 -- p_summary_version_number => NULL,
2298 --p_cancel_comment=> p_cancel_comments,
2299 --p_cancel_date =>Sysdate,
2300
2301 x_return_status => x_return_status,
2302 x_msg_count => x_msg_count,
2303 x_msg_data => x_msg_data
2304 ) ;
2305
2306 IF (x_return_status = FND_API.g_ret_sts_success) THEN
2307 -- cancel the approval workflow process if that is already launched
2308 -- todo
2309
2310 -- check whether report approval is required.
2311
2312 OPEN check_auto_approve;
2313 FETCH check_auto_approve INTO l_dummy;
2314 IF (check_auto_approve%notfound OR l_dummy <> 'Y') THEN
2315 l_dummy := 'N';
2316 END if;
2317
2318 CLOSE check_auto_approve;
2319
2320
2321 OPEN get_item_key;
2322 FETCH get_item_key INTO l_item_key, l_item_type;
2323
2324 IF get_item_key%found THEN
2325 CLOSE get_item_key;
2326
2327 -- is the workflow running
2328 -- only cancel when the workflow is running
2329
2330 OPEN get_wf_status;
2331 FETCH get_wf_status INTO l_wf_status;
2332
2333 IF (get_wf_status%notfound or
2334 l_wf_status <> 'Y' ) THEN
2335 IF (l_dummy = 'Y' or l_dummy = 'A') then
2336 PA_UTILS.Add_Message( p_app_short_name => 'PA'
2337 ,p_msg_name => 'PA_PR_CANCEL_WORKFLOW_INV');
2338 --todo
2339 -- x_return_status := FND_API.G_RET_STS_ERROR;
2340 END IF;
2341
2342 else
2343
2344 pa_progress_report_workflow.cancel_workflow
2345 (
2346 l_item_type
2347 , l_item_key
2348 , x_msg_count
2349 , x_msg_data
2350 , x_return_status
2351 );
2352
2353 IF (l_dummy = 'N') THEN
2354 x_return_status := FND_API.G_RET_STS_SUCCESS;
2355 END IF;
2356
2357 END IF;
2358
2359 CLOSE get_wf_status;
2360
2361 ELSE
2362 -- does not find item key for the workflow
2363 -- return
2364
2365 -- if workflow is required, we are canceling the submission
2366 -- failed, return error.
2367 IF (l_dummy = 'Y' or l_dummy = 'A' )THEN
2368 PA_UTILS.Add_Message( p_app_short_name => 'PA'
2369 ,p_msg_name => 'PA_PR_CANCEL_WORKFLOW_INV');
2370 x_return_status := FND_API.G_RET_STS_ERROR;
2371
2372 END IF;
2373
2374 END IF;
2375
2376 END IF;
2377
2378
2379 END IF;
2380
2381
2382 -- Commit if the flag is set and there is no error
2383 IF (p_commit = FND_API.G_TRUE AND x_return_status = FND_API.g_ret_sts_success )THEN
2384 COMMIT;
2385 END IF;
2386
2387 IF (x_return_status <> FND_API.g_ret_sts_success )THEN
2388 ROLLBACK TO rework_report;
2389 END IF;
2390
2391 -- Reset the error stack when returning to the calling program
2392 PA_DEBUG.Reset_Err_Stack;
2393
2394
2395 EXCEPTION
2396 WHEN OTHERS THEN
2397 IF p_commit = FND_API.G_TRUE THEN
2398 ROLLBACK TO rework_report;
2399 END IF;
2400 --
2401 -- Set the excetption Message and the stack
2402 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_PROGRESS_REPORT_PVT.Rework_Report'
2403 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
2404 --
2405 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2406 RAISE; -- This is optional depending on the needs
2407
2408 END rework_report;
2409
2410 PROCEDURE publish_report
2411 (
2412 p_api_version IN NUMBER := 1.0,
2413 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
2414 p_commit IN VARCHAR2 := FND_API.g_false,
2415 p_validate_only IN VARCHAR2 := FND_API.g_true,
2416 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
2417
2418 p_version_id IN NUMBER := NULL,
2419 p_record_version_number IN NUMBER := NULL,
2420 p_summary_version_number IN NUMBER := NULL,
2421
2422 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2423 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2424 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2425 ) IS
2426
2427 l_rowid ROWID;
2428 l_dummy VARCHAR2(1);
2429
2430 l_version_id NUMBER;
2431
2432 CURSOR check_auto_approve IS
2433 SELECT p1.approval_required
2434 FROM pa_object_page_layouts p1,
2435 pa_progress_report_vers p2
2436 WHERE
2437 p2.version_id = l_version_id
2438 AND p2.object_id = p1.object_id
2439 AND p2.object_type = p1.object_type;
2440
2441 CURSOR get_template_type
2442 is
2443 SELECT meaning FROM pa_lookups
2444 WHERE lookup_type = 'PA_PAGE_TYPES'
2445 AND lookup_code = 'PPR';
2446
2447 --Bug#3302984, added the function name parameter
2448 CURSOR get_setup_info
2449 IS SELECT
2450 popl.object_id, popl.object_type, popl.reporting_cycle_id,
2451 popl.report_offset_days, popl.approval_required,
2452 popl.reminder_days, popl.reminder_days_type, popl.record_version_number,popl.pers_function_name,
2453 popl.page_type_code, popl.initial_progress_status, popl.final_progress_status,popl.rollup_progress_status,
2454 popl.report_type_id, popl.approver_source_id, popl.approver_source_type, popl.effective_from,
2455 popl.effective_to, popl.object_page_layout_id
2456 FROM pa_object_page_layouts popl, pa_progress_report_vers pprv
2457 WHERE pprv.version_id = p_version_id
2458 AND popl.object_id = pprv.object_id
2459 AND popl.object_type = pprv.object_type
2460 AND popl.page_type_code = 'PPR'
2461 AND popl.report_type_id = pprv.report_type_id;
2462
2463 l_approval_required VARCHAR2(1);
2464 l_reminder_days NUMBER;
2465 l_reminder_days_type VARCHAR2(30);
2466 l_type VARCHAR2(80); /* bug 2447763 */
2467
2468 l_next_reporting_date DATE;
2469 l_report_end_date DATE;
2470
2471 l_object_type VARCHAR2(30);
2472 l_object_id NUMBER;
2473 l_report_cycle_id NUMBER;
2474 l_report_offset_days number;
2475 l_record_version_number NUMBER;
2476 l_function_name VARCHAR2(30);
2477 l_page_type_code VARCHAR2(30);
2478 l_initial_progress_status VARCHAR2(30);
2479 l_final_progress_status VARCHAR2(30);
2480 l_rollup_progress_status VARCHAR2(1);
2481
2482 l_report_type_id NUMBER;
2483 l_approver_source_id NUMBER;
2484 l_approver_source_type NUMBER;
2485 l_effective_from DATE;
2486 l_effective_to DATE;
2487 l_object_page_layout_id NUMBER;
2488
2489 BEGIN
2490 -- Initialize the Error Stack
2491 PA_DEBUG.init_err_stack('PA_PROGRESS_REPORT_PVT.Publish_Report');
2492
2493 -- Initialize the return status to success
2494 x_return_status := FND_API.G_RET_STS_SUCCESS;
2495
2496 -- Issue API savepoint if the transaction is to be committed
2497 SAVEPOINT publish_report;
2498
2499 OPEN get_template_type;
2500 FETCH get_template_type INTO l_type;
2501 CLOSE get_template_type;
2502
2503
2504 -- check mandatory version_id
2505 IF (p_version_id IS NULL) then
2506 PA_UTILS.Add_Message( p_app_short_name => 'PA'
2507 ,p_msg_name => 'PA_PR_VERSION_ID_INV'
2508 , p_token1 => 'TEMPLATE_TYPE'
2509 , p_value1 => l_type);
2510 x_return_status := FND_API.G_RET_STS_ERROR;
2511 END IF;
2512
2513
2514 IF (p_validate_only <>FND_API.g_true AND x_return_status = FND_API.g_ret_sts_success) then
2515
2516 -- set the status to working first
2517
2518 --debug_msg('UPDATE_PROGRESS_REPORT_VER_ROW');
2519 /*
2520 pa_progress_report_pkg.UPDATE_PROGRESS_REPORT_VER_ROW
2521 (
2522 p_version_id,
2523 NULL,
2524 NULL,
2525 NULL,
2526 NULL,
2527 'PROGRESS_REPORT_PUBLISHED',
2528
2529
2530 NULL,
2531 NULL,
2532 NULL,
2533 NULL,
2534 NULL,
2535 NULL,
2536 SYSDATE,
2537 NULL,
2538 NULL,
2539
2540
2541 --NULL,
2542 P_RECORD_VERSION_NUMBER ,
2543 P_SUMMARY_VERSION_NUMBER ,
2544 x_return_status ,
2545 x_msg_count ,
2546 x_msg_data
2547 );*/
2548
2549 change_report_status
2550 (
2551 p_version_id => p_version_id ,
2552 p_report_status => 'PROGRESS_REPORT_PUBLISHED',
2553 p_record_version_number => p_record_version_number,
2554 p_summary_version_number => p_summary_version_number,
2555 p_published_date => Sysdate,
2556 --p_cancel_comment=> p_cancel_comments,
2557 --p_cancel_date =>Sysdate,
2558
2559 x_return_status => x_return_status,
2560 x_msg_count => x_msg_count,
2561 x_msg_data => x_msg_data
2562 ) ;
2563
2564 IF (x_return_status = FND_API.g_ret_sts_success) THEN
2565
2566 OPEN check_auto_approve;
2567
2568 FETCH check_auto_approve INTO l_dummy;
2569
2570 IF (l_dummy = 'Y' or l_dummy = 'A') THEN
2571 -- if auto approve, the information has been saved already
2572 -- we do not need to save anything
2573 NULL;
2574 else
2575 -- update information in other tables
2576 --update_project_perccomplete(p_version_id,
2577 -- x_return_status,
2578 -- x_msg_count,
2579 -- x_msg_data);
2580
2581 NULL;
2582
2583 END IF;
2584
2585 -- by msundare request, need to change the next report end date
2586
2587 OPEN get_setup_info;
2588 FETCH get_setup_info INTO l_object_id, l_object_type, l_report_cycle_id,l_report_offset_days, l_approval_required, l_reminder_days,
2589 l_reminder_days_type, l_record_version_number, l_function_name,l_page_type_code, l_initial_progress_status, l_final_progress_status, l_rollup_progress_status,
2590 l_report_type_id,
2591 l_approver_source_id,
2592 l_approver_source_type,
2593 l_effective_from,
2594 l_effective_to,
2595 l_object_page_layout_id;
2596
2597 CLOSE get_setup_info;
2598
2599 IF (l_report_cycle_id IS NOT null) then
2600
2601
2602 --debug_msg('get_report_start_end_dates');
2603 pa_progress_report_utils.get_report_start_end_dates
2604 (
2605 l_object_type,
2606 l_object_id,
2607 l_report_type_id,
2608 l_report_cycle_id,
2609 l_report_offset_days,
2610
2611 'Y',
2612 null,
2613 l_next_reporting_date,
2614 l_report_end_date
2615 );
2616
2617
2618 --debug_msg('update_object_page_layout_row');
2619 pa_progress_report_pkg.update_object_page_layout_row
2620 (
2621 l_OBJECT_ID ,
2622 l_OBJECT_TYPE ,
2623 null ,
2624 l_page_type_code ,
2625
2626 l_approval_required ,
2627 --P_AUTO_PUBLISH ,
2628 l_report_cycle_id ,
2629 l_report_offset_days,
2630
2631 l_report_end_date ,
2632 l_REMINDER_DAYS ,
2633 l_REMINDER_DAYS_TYPE ,
2634 l_initial_progress_status,
2635 l_final_progress_status,
2636 l_rollup_progress_status,
2637 l_report_type_id,
2638 l_approver_source_id,
2639 l_approver_source_type,
2640 l_effective_from,
2641 l_effective_to,
2642 l_object_page_layout_id,
2643
2644 l_record_version_number,
2645 l_function_name,
2646 x_return_status ,
2647 x_msg_count ,
2648 x_msg_data
2649
2650 );
2651 END IF;
2652
2653
2654 -- debug_msg('update_object_page_layout_row' || x_return_status);
2655
2656
2657
2658 CLOSE check_auto_approve;
2659 END IF;
2660
2661 END IF;
2662
2663
2664
2665
2666
2667 -- Commit if the flag is set and there is no error
2668 IF (p_commit = FND_API.G_TRUE AND x_return_status = FND_API.g_ret_sts_success )THEN
2669 COMMIT;
2670 END IF;
2671
2672 IF (x_return_status <> FND_API.g_ret_sts_success )THEN
2673 ROLLBACK TO publish_report;
2674 END IF;
2675 -- Reset the error stack when returning to the calling program
2676 PA_DEBUG.Reset_Err_Stack;
2677
2678
2679 EXCEPTION
2680 WHEN OTHERS THEN
2681 ROLLBACK TO publish_report;
2682
2683 --
2684 -- Set the excetption Message and the stack
2685 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_PROGRESS_REPORT_PVT.Publish_Report'
2686 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
2687 --
2688 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2689 RAISE; -- This is optional depending on the needs
2690
2691 END publish_report;
2692
2693 PROCEDURE submit_report
2694 (
2695 p_api_version IN NUMBER := 1.0,
2696 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
2697 p_commit IN VARCHAR2 := FND_API.g_false,
2698 p_validate_only IN VARCHAR2 := FND_API.g_true,
2699 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
2700
2701 p_version_id IN NUMBER := NULL,
2702 p_record_version_number IN NUMBER := NULL,
2703 p_summary_version_number IN NUMBER := NULL,
2704
2705 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2706 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2707 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2708 ) IS
2709
2710 l_rowid ROWID;
2711
2712 l_err_code NUMBER;
2713 l_err_stage VARCHAR2(30);
2714 l_err_stack VARCHAR2(240);
2715 l_project_id NUMBER;
2716
2717
2718 CURSOR get_object_id IS
2719 SELECT object_id
2720 FROM pa_progress_report_vers
2721 WHERE version_id = p_version_id
2722 AND object_type = 'PA_PROJECTS'
2723 AND report_status_code = 'PROGRESS_REPORT_WORKING';
2724
2725
2726
2727 l_item_key wf_item_activity_statuses.item_key%TYPE; --Bug 5217292
2728 l_dummy VARCHAR2(1);
2729
2730 CURSOR get_template_type
2731 is
2732 SELECT meaning FROM pa_lookups
2733 WHERE lookup_type = 'PA_PAGE_TYPES'
2734 AND lookup_code = 'PPR';
2735
2736 l_type VARCHAR2(80); /* bug 2447763 */
2737
2738 l_wf_item_type VARCHAR2(30);
2739 l_wf_process_name VARCHAR2(30);
2740 l_wf_enable VARCHAR2(1);
2741
2742 CURSOR get_wf_process_name
2743 IS
2744 select
2745 WORKFLOW_ITEM_TYPE,
2746 workflow_process, enable_wf_flag from pa_project_statuses
2747 where status_type = 'PROGRESS_REPORT'
2748 AND project_status_code = 'PROGRESS_REPORT_SUBMITTED';
2749
2750 BEGIN
2751 -- Initialize the Error Stack
2752 PA_DEBUG.init_err_stack('PA_PROGRESS_REPORT_PVT.Submit_Report');
2753
2754 -- Initialize the return status to success
2755 x_return_status := FND_API.G_RET_STS_SUCCESS;
2756
2757 -- Issue API savepoint if the transaction is to be committed
2758 SAVEPOINT submit_report;
2759
2760
2761 OPEN get_template_type;
2762 FETCH get_template_type INTO l_type;
2763 CLOSE get_template_type;
2764
2765 -- check mandatory version_id
2766 IF (p_version_id IS NULL) then
2767 PA_UTILS.Add_Message( p_app_short_name => 'PA'
2768 ,p_msg_name => 'PA_PR_VERSION_ID_INV'
2769 , p_token1 => 'TEMPLATE_TYPE'
2770 , p_value1 => l_type);
2771
2772 x_return_status := FND_API.G_RET_STS_ERROR;
2773 END IF;
2774
2775 OPEN get_object_id;
2776
2777 FETCH get_object_id INTO l_project_id;
2778
2779 IF get_object_id%notfound THEN
2780 PA_UTILS.Add_Message( p_app_short_name => 'PA'
2781 ,p_msg_name => 'PA_PR_STATUS_NOT_WORKING');
2782 x_return_status := FND_API.G_RET_STS_ERROR;
2783 END IF;
2784
2785 CLOSE get_object_id;
2786
2787 IF (p_validate_only <>FND_API.g_true AND x_return_status = FND_API.g_ret_sts_success) then
2788
2789 -- set the status to working first
2790
2791 pa_progress_report_pkg.UPDATE_PROGRESS_REPORT_VER_ROW
2792 (
2793 p_version_id,
2794 NULL,
2795 NULL,
2796 NULL,
2797 NULL,
2798 'PROGRESS_REPORT_SUBMITTED',
2799
2800
2801 NULL,
2802 NULL,
2803 NULL,
2804 NULL,
2805 NULL,
2806 'N',
2807 NULL,
2808 NULL,
2809 NULL,
2810
2811
2812 P_RECORD_VERSION_NUMBER ,
2813 p_summary_version_number ,
2814
2815 null,
2816 x_return_status ,
2817 x_msg_count ,
2818 x_msg_data
2819 );
2820
2821 IF (x_return_status = FND_API.g_ret_sts_success) THEN
2822 -- update information in other tables
2823 --update_project_perccomplete(p_version_id,
2824 -- x_return_status,
2825 -- x_msg_count,
2826 -- x_msg_data);
2827
2828 IF(x_return_status = FND_API.g_ret_sts_success )THEN
2829 -- launch the workflow process
2830
2831 -- added by syao
2832 -- get the workflow process name
2833 open get_wf_process_name;
2834 fetch get_wf_process_name INTO l_wf_item_type,
2835 l_wf_process_name, l_wf_enable;
2836
2837 IF get_wf_process_name%found AND l_wf_enable = 'Y'THEN
2838 CLOSE get_wf_process_name;
2839
2840 pa_progress_report_workflow.start_workflow
2841 (
2842 -- 'PAWFPPRA'
2843 -- , 'PA_PROG_REP_APPRVL_MP'
2844 l_wf_item_type
2845 , l_wf_process_name
2846 , p_version_id
2847 , l_item_key
2848 , x_msg_count
2849 , x_msg_data
2850 , x_return_status
2851 );
2852
2853 IF(x_return_status = FND_API.g_ret_sts_success )THEN
2854
2855 -- update pa_wf_process_table
2856 PA_WORKFLOW_UTILS.Insert_WF_Processes
2857 (p_wf_type_code => 'Progress Report'
2858 -- ,p_item_type => 'PAWFPPRA'
2859 ,p_item_type => l_wf_item_type
2860 ,p_item_key => l_item_key
2861 ,p_entity_key1 => l_project_id
2862 ,p_entity_key2 => p_version_id
2863 ,p_description => l_wf_process_name
2864 ,p_err_code => l_err_code
2865 ,p_err_stage => l_err_stage
2866 ,p_err_stack => l_err_stack
2867 );
2868
2869 IF l_err_code <> 0 THEN
2870 PA_UTILS.Add_Message( p_app_short_name => 'PA'
2871 ,p_msg_name => 'PA_PR_CREATE_WF_FAILED');
2872 x_return_status := FND_API.G_RET_STS_ERROR;
2873
2874 END IF;
2875
2876
2877 ELSE
2878 PA_UTILS.Add_Message( p_app_short_name => 'PA'
2879 ,p_msg_name => 'PA_PR_CREATE_WF_FAILED');
2880 x_return_status := FND_API.G_RET_STS_ERROR;
2881
2882 END IF;
2883 ELSE
2884 CLOSE get_wf_process_name;
2885
2886 END IF;
2887
2888 END IF;
2889
2890
2891 END IF;
2892
2893
2894 END IF;
2895
2896
2897
2898 -- Commit if the flag is set and there is no error
2899 IF (p_commit = FND_API.G_TRUE AND x_return_status = FND_API.g_ret_sts_success )THEN
2900 COMMIT;
2901 END IF;
2902
2903 IF (x_return_status <> FND_API.g_ret_sts_success )THEN
2904 ROLLBACK TO submit_report;
2905 END IF;
2906
2907 -- Reset the error stack when returning to the calling program
2908 PA_DEBUG.Reset_Err_Stack;
2909
2910
2911 EXCEPTION
2912 WHEN OTHERS THEN
2913
2914 ROLLBACK TO submit_report;
2915
2916 --
2917 -- Set the excetption Message and the stack
2918 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_PROGRESS_REPORT_PVT.Submit_Report'
2919 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
2920 --
2921 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2922 RAISE; -- This is optional depending on the needs
2923
2924 END submit_report;
2925
2926 PROCEDURE change_report_status
2927 (
2928 p_api_version IN NUMBER := 1.0,
2929 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
2930 p_commit IN VARCHAR2 := FND_API.g_false,
2931 p_validate_only IN VARCHAR2 := FND_API.g_false,
2932 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
2933
2934 p_version_id IN NUMBER := NULL,
2935 p_report_status IN VARCHAR2 := NULL,
2936 p_record_version_number IN NUMBER := NULL,
2937 p_summary_version_number IN NUMBER := NULL,
2938 p_published_date IN DATE := NULL,
2939 p_cancel_comment IN VARCHAR2 := NULL,
2940 p_cancel_date IN DATE := NULL,
2941
2942 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2943 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2944 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2945 ) IS
2946
2947 l_wf_enable VARCHAR2(1);
2948 l_wf_item_type VARCHAR2(30);
2949 l_wf_process VARCHAR2(30);
2950 l_success_code VARCHAR2(30);
2951 l_failure_code VARCHAR2(30);
2952 l_err_code NUMBER;
2953 l_err_stage VARCHAR2(30);
2954 l_err_stack VARCHAR2(240);
2955 l_project_id NUMBER;
2956 l_report_end_date DATE;
2957 l_report_type_id NUMBER;
2958 l_current_flag VARCHAR2(1);
2959 x_report_end_date DATE;
2960 x_version_id NUMBER;
2961 l_record_version_number NUMBER;
2962 l_summary_version_number NUMBER;
2963 l_item_key wf_item_activity_statuses.item_key%TYPE; --Bug 5217292
2964 l_dummy VARCHAR2(1);
2965
2966 CURSOR get_object_id IS
2967 SELECT object_id, report_end_date, report_type_id
2968 FROM pa_progress_report_vers
2969 WHERE version_id = p_version_id
2970 AND object_type = 'PA_PROJECTS';
2971
2972 CURSOR get_wf_info is
2973 select enable_wf_flag, workflow_item_type,
2974 workflow_process,wf_success_status_code,
2975 wf_failure_status_code from pa_project_statuses
2976 where project_status_code = p_report_status;
2977
2978
2979 --like 'PROGRESS_REPORT%'
2980
2981 BEGIN
2982 -- Initialize the Error Stack
2983 PA_DEBUG.init_err_stack('PA_PROGRESS_REPORT_PVT.Change_Report_Status');
2984
2985 -- Initialize the return status to success
2986 x_return_status := FND_API.G_RET_STS_SUCCESS;
2987
2988 -- get the project_id
2989 OPEN get_object_id;
2990
2991 FETCH get_object_id INTO l_project_id,l_report_end_date, l_report_type_id;
2992
2993 -- IF get_object_id%notfound THEN
2994 -- PA_UTILS.Add_Message( p_app_short_name => 'PA'
2995 -- ,p_msg_name => 'PA_PR_STATUS_NOT_WORKING');
2996 --x_return_status := FND_API.G_RET_STS_ERROR;
2997 --END IF;
2998 CLOSE get_object_id;
2999
3000 --- Check for the last published report and compare dates. If date is
3001 --- greater than last one then current_flag = 'Y' else 'N'
3002
3003 if (p_report_status = 'PROGRESS_REPORT_PUBLISHED') then
3004 begin
3005 select version_id,report_end_Date ,
3006 record_version_number, summary_version_number
3007 into x_version_id,x_report_end_date,
3008 l_record_version_number, l_summary_version_number
3009 from pa_progress_report_vers
3010 where object_id = l_project_id
3011 and object_Type = 'PA_PROJECTS'
3012 and report_type_id = l_report_Type_id
3013 and page_type_code = 'PPR'
3014 and current_flag = 'Y';
3015
3016 if (l_report_end_date >= x_report_end_Date) then
3017 l_current_flag := 'Y';
3018 pa_progress_report_pkg.UPDATE_PROGRESS_REPORT_VER_ROW
3019 (
3020 x_version_id,
3021 NULL,
3022 NULL,
3023 NULL,
3024 NULL,
3025 NULL,
3026
3027
3028 NULL,
3029 NULL,
3030 NULL,
3031 NULL,
3032 NULL,
3033 'N',
3034 null,
3035 null,
3036 null,
3037
3038
3039 l_RECORD_VERSION_NUMBER ,
3040 l_summary_version_number ,
3041
3042 null,
3043 x_return_status ,
3044 x_msg_count ,
3045 x_msg_data
3046 );
3047 else
3048 l_current_flag := 'N';
3049 end if;
3050 exception
3051 when no_data_found then
3052 l_current_flag := 'Y';
3053 when others then
3054 l_current_flag := 'N';
3055 end;
3056 else
3057 l_current_flag := 'N';
3058 end if;
3059
3060 -- change the progress status, launch the workflow if necessary
3061 IF (p_validate_only <>FND_API.g_true AND x_return_status = FND_API.g_ret_sts_success) then
3062 pa_progress_report_pkg.UPDATE_PROGRESS_REPORT_VER_ROW
3063 (
3064 p_version_id,
3065 NULL,
3066 NULL,
3067 NULL,
3068 NULL,
3069 p_report_status,
3070
3071
3072 NULL,
3073 NULL,
3074 NULL,
3075 NULL,
3076 NULL,
3077 l_current_flag,
3078 p_published_date,
3079 p_cancel_comment,
3080 p_cancel_date,
3081
3082
3083 P_RECORD_VERSION_NUMBER ,
3084 p_summary_version_number ,
3085
3086 null,
3087 x_return_status ,
3088 x_msg_count ,
3089 x_msg_data
3090 );
3091 IF (x_return_status = FND_API.g_ret_sts_success )THEN
3092
3093
3094 -- check whether to launch the workflow
3095 OPEN get_wf_info;
3096 FETCH get_wf_info INTO l_wf_enable, l_wf_item_type, l_wf_process,
3097 l_success_code,l_failure_code;
3098 IF (get_wf_info%found) then
3099 IF l_wf_enable = 'Y' THEN
3100
3101
3102
3103 -- launch the workflow for the report status change
3104
3105 pa_progress_report_workflow.start_workflow
3106 (
3107 -- 'PAWFPPRA'
3108 -- , 'PA_PROG_REP_APPRVL_MP'
3109 l_wf_item_type
3110 , l_wf_process
3111 , p_version_id
3112 , l_item_key
3113 , x_msg_count
3114 , x_msg_data
3115 , x_return_status
3116 );
3117
3118 IF(x_return_status = FND_API.g_ret_sts_success )THEN
3119
3120 -- update pa_wf_process_table
3121 PA_WORKFLOW_UTILS.Insert_WF_Processes
3122 (p_wf_type_code => 'Progress Report'
3123 -- ,p_item_type => 'PAWFPPRA'
3124 ,p_item_type => l_wf_item_type
3125 ,p_item_key => l_item_key
3126 ,p_entity_key1 => l_project_id
3127 ,p_entity_key2 => p_version_id
3128 ,p_description => l_wf_process
3129 ,p_err_code => l_err_code
3130 ,p_err_stage => l_err_stage
3131 ,p_err_stack => l_err_stack
3132 );
3133
3134 IF l_err_code <> 0 THEN
3135 PA_UTILS.Add_Message( p_app_short_name => 'PA'
3136 ,p_msg_name => 'PA_PR_CREATE_WF_FAILED');
3137 x_return_status := FND_API.G_RET_STS_ERROR;
3138
3139 END IF;
3140
3141
3142 ELSE
3143 PA_UTILS.Add_Message( p_app_short_name => 'PA'
3144 ,p_msg_name => 'PA_PR_CREATE_WF_FAILED');
3145 x_return_status := FND_API.G_RET_STS_ERROR;
3146
3147 END IF;
3148 END IF;
3149 END IF;
3150 CLOSE get_wf_info;
3151
3152
3153
3154 END IF;
3155
3156 END IF;
3157
3158 -- Commit if the flag is set and there is no error
3159 IF (p_commit = FND_API.G_TRUE AND x_return_status = FND_API.g_ret_sts_success )THEN
3160 COMMIT;
3161 END IF;
3162
3163 -- Reset the error stack when returning to the calling program
3164 PA_DEBUG.Reset_Err_Stack;
3165
3166 EXCEPTION
3167 WHEN OTHERS THEN
3168
3169 --
3170 -- Set the excetption Message and the stack
3171 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_PROGRESS_REPORT_PVT.Change_Report_Status'
3172 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
3173 --
3174 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3175 RAISE; -- This is optional depending on the needs
3176 END change_report_status;
3177
3178
3179 PROCEDURE create_report
3180 (
3181 p_api_version IN NUMBER := 1.0,
3182 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
3183 p_commit IN VARCHAR2 := FND_API.g_false,
3184 p_validate_only IN VARCHAR2 := FND_API.g_true,
3185 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
3186
3187 p_object_id IN NUMBER := NULL,
3188 p_object_type IN VARCHAR2 := NULL,
3189 p_report_type_id IN NUMBER := NULL,
3190
3191 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
3192 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
3193 x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
3194 x_version_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3195 ) IS
3196
3197 l_rowid ROWID;
3198 l_report_start_date DATE;
3199 l_report_end_date DATE;
3200 l_last_published_version_id NUMBER;
3201 l_page_id NUMBER;
3202 l_page_type VARCHAR2(30);
3203 l_reporting_cycle_id NUMBER;
3204 l_report_offset_days NUMBER;
3205 l_progress_status_code VARCHAR2(30);
3206 l_effective_from_date Date;
3207
3208 CURSOR get_page_id_type
3209 IS
3210 SELECT popl.page_id, popl.page_type_code, popl.reporting_cycle_id, popl.report_offset_days,popl.effective_from
3211 FROM pa_object_page_layouts popl, pa_page_layouts ppl
3212 WHERE popl.object_id = p_object_id
3213 AND popl.object_type = p_object_type
3214 AND popl.report_type_id = p_report_type_id
3215 AND popl.page_id = ppl.page_id
3216 AND popl.page_type_code = 'PPR';
3217
3218
3219 CURSOR report_exist
3220 IS
3221 SELECT rowid
3222 FROM pa_progress_report_vers
3223 WHERE object_id = p_object_id
3224 AND object_type = p_object_type
3225 AND report_type_id = p_report_type_id
3226 AND report_status_code = 'PROGRESS_REPORT_PUBLISHED';
3227
3228 /* Commented and changed as below for bug 5956107
3229 CURSOR get_last_published_version_id
3230 IS
3231 SELECT version_id
3232 FROM pa_progress_report_vers p1
3233 WHERE
3234 p1.object_id = p_object_id
3235 AND p1.object_type = p_object_type
3236 ANd p1.report_type_id = p_report_type_id
3237 AND p1.report_status_code = 'PROGRESS_REPORT_PUBLISHED'
3238 AND ROWNUM =1
3239 AND p1.report_end_date =
3240 (
3241 SELECT MAX(report_end_date)
3242 FROM pa_progress_report_vers
3243 WHERE
3244 object_id = p_object_id
3245 AND object_type = p_object_type
3246 AND report_type_id = p_report_type_id
3247 AND report_status_code = 'PROGRESS_REPORT_PUBLISHED'
3248 ); */
3249
3250 CURSOR get_last_published_version_id
3251 IS
3252 SELECT version_id
3253 FROM pa_progress_report_vers p1
3254 WHERE
3255 p1.object_id = p_object_id
3256 AND p1.object_type = p_object_type
3257 ANd p1.report_type_id = p_report_type_id
3258 AND p1.report_status_code = 'PROGRESS_REPORT_PUBLISHED'
3259 AND ROWNUM =1
3260 AND p1.published_date =
3261 (
3262 SELECT MAX(published_date)
3263 FROM pa_progress_report_vers
3264 WHERE
3265 object_id = p_object_id
3266 AND object_type = p_object_type
3267 AND report_type_id = p_report_type_id
3268 AND report_status_code = 'PROGRESS_REPORT_PUBLISHED'
3269 );
3270
3271 CURSOR get_all_regions IS
3272 SELECT *
3273 FROM pa_progress_report_vals
3274 WHERE version_id = l_last_published_version_id;
3275
3276 CURSOR get_publish_overview IS
3277 SELECT overview, progress_status_code, REPORT_START_DATE
3278 FROM pa_progress_report_vers
3279 WHERE version_id = l_last_published_version_id;
3280
3281 CURSOR get_all_regions_from_template IS
3282 SELECT pplr.*
3283 FROM pa_page_layout_regions pplr
3284 WHERE pplr.page_id = l_page_id;
3285
3286 CURSOR get_project_manager
3287 IS
3288 select RESOURCE_SOURCE_ID
3289 from pa_project_parties where project_id = p_object_id;
3290
3291 CURSOR get_max_report_end_date
3292 IS SELECT MAX(report_end_date)
3293 FROM pa_progress_report_vers
3294 WHERE object_id = p_object_id
3295 AND object_type = p_object_type
3296 AND report_Type_id = p_report_Type_id
3297 ;
3298
3299 CURSOR get_person_id
3300 IS
3301 select usr.employee_id
3302 from
3303 fnd_user usr
3304 WHERE
3305 usr.user_id = fnd_global.user_id
3306 and trunc(sysdate) between USR.START_DATE and nvl(USR.END_DATE, sysdate+1);
3307
3308 /*CURSOR get_resource_id
3309 IS
3310 select resource_id
3311 from pa_project_parties_v
3312 where user_id = fnd_global.user_id;*/
3313
3314 l_mgr_id number;
3315 l_overview VARCHAR2(240);
3316 l_person_id NUMBER;
3317 l_report_cycle_flag VARCHAR2(1) := 'Y'; -- flag to determine if there is a report cycle
3318 l_call_setup VARCHAR2(1);
3319
3320 BEGIN
3321 -- Initialize the Error Stack
3322 PA_DEBUG.init_err_stack('PA_PROGRESS_REPORT_PVT.create_next_progress_report');
3323
3324 -- Initialize the return status to success
3325 x_return_status := FND_API.G_RET_STS_SUCCESS;
3326
3327 -- Issue API savepoint if the transaction is to be committed
3328
3329 SAVEPOINT create_next_progress_report;
3330
3331
3332 -- check mandatory version_id
3333 IF (p_object_id IS NULL) then
3334 PA_UTILS.Add_Message( p_app_short_name => 'PA'
3335 ,p_msg_name => 'PA_OBJECT_ID_INV');
3336 x_return_status := FND_API.G_RET_STS_ERROR;
3337 END IF;
3338 l_page_id := -99;
3339 l_call_setup := 'N';
3340
3341 OPEN get_page_id_type;
3342 FETCH get_page_id_type INTO l_page_id, l_page_type, l_reporting_cycle_id,
3343 l_report_offset_days, l_effective_from_date;
3344
3345 IF get_page_id_type%notfound THEN
3346 l_page_id := pa_progress_report_utils.get_object_page_id(p_page_type_code => 'PPR',
3347 p_object_Type => 'PA_PROJECTS',
3348 p_object_id => p_object_id,
3349 p_report_Type_id => p_report_Type_id);
3350 l_page_type := 'PPR';
3351 l_call_setup := 'Y';
3352 l_effective_from_date := trunc(sysdate);
3353 End if;
3354
3355 if (l_page_id = -99) then
3356 PA_UTILS.Add_Message( p_app_short_name => 'PA'
3357 ,p_msg_name => 'PA_NO_PRJ_REP_TEMPLATE');
3358 x_return_status := FND_API.G_RET_STS_ERROR;
3359
3360 END IF;
3361
3362 CLOSE get_page_id_type;
3363
3364 IF (l_call_setup = 'Y' and x_return_status = FND_API.g_ret_sts_success) then
3365 ---- call setup api
3366 define_progress_report_setup
3367 (
3368 p_validate_only => FND_API.g_false,
3369 p_init_msg_list => FND_API.g_false,
3370 p_object_id => p_object_id,
3371 p_object_type => p_object_type,
3372 p_page_type_code => l_page_type,
3373 p_page_id => l_page_id,
3374 p_approval_required => 'N',
3375 p_report_Type_id => p_report_Type_id,
3376 p_effective_from => l_effective_from_date,
3377 x_return_status => x_return_status,
3378 x_msg_count => x_msg_count,
3379 x_msg_data => x_msg_data
3380 );
3381
3382 END IF;
3383
3384 IF (p_validate_only <>FND_API.g_true AND x_return_status = FND_API.g_ret_sts_success) then
3385
3386 -- create an entry in pa_progress_report_vers table
3387
3388 --debug_msg('before get dates' || To_char(l_reporting_cycle_id));
3389
3390 IF l_reporting_cycle_id IS NOT NULL then
3391 l_report_cycle_flag := 'Y';
3392 pa_progress_report_utils.get_report_start_end_dates
3393 (
3394 p_object_type,
3395 p_object_id,
3396 p_report_type_id,
3397 l_reporting_cycle_id,
3398 l_report_offset_days,
3399
3400 'N',
3401 l_effective_from_date,
3402 l_report_start_date,
3403 l_report_end_date
3404
3405 -- x_return_status ,
3406 --x_msg_count ,
3407 --x_msg_data
3408 );
3409
3410 --debug_msg('before get dates end');
3411
3412 IF (Trunc(l_report_end_date) < Trunc(l_report_start_date)) THEN
3413 PA_UTILS.Add_Message( p_app_short_name => 'PA'
3414 ,p_msg_name => 'PA_REPORT_END_DATE_INV');
3415 x_return_status := FND_API.G_RET_STS_ERROR;
3416 return;
3417 END IF;
3418 ELSE
3419 l_report_cycle_flag := 'N';
3420 -- not report cycle ID
3421 l_report_start_date:= Trunc(Sysdate);
3422 l_report_end_date:= Trunc(Sysdate);
3423 END IF;
3424
3425
3426 IF (p_validate_only <>FND_API.g_true AND x_return_status = FND_API.g_ret_sts_success) THEN
3427
3428
3429 --debug_msg('*********before insert: reported by ' || To_char(fnd_global.user_id));
3430
3431 /* Get the Reported by Person Id
3432
3433 OPEN get_person_id;
3434 FETCH get_person_id INTO l_person_id;
3435 CLOSE get_person_id; */
3436
3437 ------OPEN get_resource_id;
3438 ------FETCH get_resource_id INTO l_person_id;
3439 ------CLOSE get_resource_id;
3440 l_person_id := pa_resource_utils.get_resource_id(p_user_id => FND_GLOBAL.user_id);
3441
3442 --debug_msg ('l_person_id' || To_char(l_person_id));
3443 pa_progress_report_pkg.insert_PROGRESS_REPORT_ver_row
3444 (
3445 p_object_id,
3446 p_object_type,
3447 l_page_id,
3448 l_page_type,
3449 'PROGRESS_REPORT_WORKING',
3450
3451 Trunc(l_report_start_date) ,
3452 Trunc(l_report_end_date) ,
3453
3454 --fnd_global.user_id ,
3455 l_person_id,
3456 'PROGRESS_STAT_ON_TRACK',
3457 --'PROGRESS_STAT_NOT_STARTED',
3458 l_overview,
3459 'N', --- current_flag
3460 NULL,
3461 NULL,
3462 NULL,
3463
3464 p_report_Type_id,
3465 X_version_id,
3466 x_return_status,
3467 x_msg_count,
3468 x_msg_data
3469 );
3470 END IF;
3471
3472
3473 --debug_msg('before get_last_published_version_id');
3474 OPEN get_last_published_version_id;
3475
3476 FETCH get_last_published_version_id INTO l_last_published_version_id;
3477
3478
3479 IF (get_last_published_version_id%notfound) THEN
3480 CLOSE get_last_published_version_id;
3481
3482
3483 -- if it is the first progress report
3484
3485 --debug_msg ('no published report');
3486
3487 -- if there is existing published report
3488 -- copy it, create entries in pa_progress_report_vals table
3489 /* No need to create an empty region rows if there is no prior publised
3490 Report - msundare */
3491 /******************
3492 FOR obj_page_value_rec IN get_all_regions_from_template LOOP
3493
3494 --debug_msg ('no published report' || obj_page_value_rec.view_region_code);
3495
3496 -- debug_msg('insert val' || obj_page_value_rec.view_region_code);
3497 pa_progress_report_pkg.insert_progress_report_val_row
3498 (
3499 x_version_id,
3500 obj_page_value_rec.region_source_type,
3501 obj_page_value_rec.region_source_code,
3502 1,
3503 NULL,
3504 NULL,
3505 NULL,
3506 NULL,
3507 NULL,
3508 NULL,
3509 NULL,
3510 NULL,
3511 NULL,
3512 NULL,
3513 NULL,
3514 NULL,
3515 NULL,
3516 NULL,
3517 NULL,
3518 NULL,
3519 NULL,
3520 NULL,
3521 NULL,
3522 NULL,
3523
3524 NULL, -- uds_attribute_category
3525 NULL, -- uds_attribute1
3526 NULL, -- uds_attribute2
3527 NULL, -- uds_attribute3
3528 NULL, -- uds_attribute4
3529 NULL, -- uds_attribute5
3530 NULL, -- uds_attribute6
3531 NULL, -- uds_attribute7
3532 NULL, -- uds_attribute8
3533 NULL, -- uds_attribute9
3534 NULL, -- uds_attribute10
3535 NULL, -- uds_attribute11
3536 NULL, -- uds_attribute12
3537 NULL, -- uds_attribute13
3538 NULL, -- uds_attribute14
3539 NULL, -- uds_attribute15
3540 NULL, -- uds_attribute16
3541 NULL, -- uds_attribute17
3542 NULL, -- uds_attribute18
3543 NULL, -- uds_attribute19
3544 NULL, -- uds_attribute20
3545 x_return_status ,
3546 x_msg_count ,
3547 x_msg_data
3548 );
3549 END LOOP;
3550 *************************/
3551 null;
3552 ELSE
3553 CLOSE get_last_published_version_id;
3554
3555 -- update the overview field
3556
3557 OPEN get_publish_overview;
3558 FETCH get_publish_overview INTO l_overview, l_progress_status_code, l_report_start_date;
3559
3560 IF (get_publish_overview%found) THEN
3561
3562 CLOSE get_publish_overview;
3563
3564 -- if there is report cycle ID, report_start and end date are
3565 -- already set, we do not need to change it
3566 -- set report_start_date to null, so we are not updating the
3567 -- vers table
3568
3569 --IF l_report_cycle_flag = 'Y' THEN
3570 -- l_report_start_date := NULL;
3571 -- ELSE
3572
3573 -- OPEN get_max_report_end_date;
3574 -- FETCH get_max_report_end_date INTO l_report_start_date;
3575 -- IF get_max_report_end_date%found THEN
3576 -- l_report_start_date := l_report_start_date+1;
3577 -- ELSE
3578 -- l_report_start_date := sysdate+1;
3579 -- END IF;
3580 -- CLOSE get_max_report_end_date;
3581
3582
3583 --END IF;
3584
3585 --debug_msg ('copying overview ' || l_overview);
3586 pa_progress_report_pkg.UPDATE_PROGRESS_REPORT_VER_ROW (
3587 x_version_id,
3588 null,
3589 NULL,
3590 NULL,
3591 NULL,
3592 NULL,
3593
3594 NULL,
3595 NULL,
3596 NULL,
3597 l_PROGRESS_STATUS_CODE,
3598 l_overview,
3599
3600 'N',
3601 NULL,
3602 NULL,
3603 NULL,
3604
3605 NULL,
3606 NULL,
3607 p_report_Type_id,
3608 x_return_status,
3609 x_msg_count ,
3610 x_msg_data
3611 ) ;
3612
3613 ELSE
3614 CLOSE get_publish_overview;
3615 END IF;
3616
3617
3618 IF x_return_status = FND_API.g_ret_sts_success then
3619 --debug_msg ('published report' || To_char(l_last_published_version_id));
3620 -- if there is existing published report
3621 -- copy it, create entries in pa_progress_report_vals table
3622 FOR obj_page_value_rec IN get_all_regions LOOP
3623
3624 --debug_msg ('published report' || obj_page_value_rec.region_code);
3625 pa_progress_report_pkg.insert_progress_report_val_row
3626 (
3627 x_version_id,
3628 obj_page_value_rec.region_source_type,
3629 obj_page_value_rec.region_code,
3630 obj_page_value_rec.record_sequence,
3631 obj_page_value_rec.ATTRIBUTE1 ,
3632 obj_page_value_rec.ATTRIBUTE2 ,
3633 obj_page_value_rec.ATTRIBUTE3 ,
3634 obj_page_value_rec.ATTRIBUTE4 ,
3635 obj_page_value_rec.ATTRIBUTE5 ,
3636 obj_page_value_rec.ATTRIBUTE6 ,
3637 obj_page_value_rec.ATTRIBUTE7 ,
3638 obj_page_value_rec.ATTRIBUTE8 ,
3639 obj_page_value_rec.ATTRIBUTE9 ,
3640 obj_page_value_rec.ATTRIBUTE10 ,
3641 obj_page_value_rec.ATTRIBUTE11 ,
3642 obj_page_value_rec.ATTRIBUTE12 ,
3643 obj_page_value_rec.ATTRIBUTE13 ,
3644 obj_page_value_rec.ATTRIBUTE14 ,
3645 obj_page_value_rec.ATTRIBUTE15 ,
3646 obj_page_value_rec.ATTRIBUTE16 ,
3647 obj_page_value_rec.ATTRIBUTE17 ,
3648 obj_page_value_rec.ATTRIBUTE18 ,
3649 obj_page_value_rec.ATTRIBUTE19 ,
3650 obj_page_value_rec.ATTRIBUTE20 ,
3651 obj_page_value_rec.UDS_ATTRIBUTE_CATEGORY ,
3652 obj_page_value_rec.UDS_ATTRIBUTE1 ,
3653 obj_page_value_rec.UDS_ATTRIBUTE2 ,
3654 obj_page_value_rec.UDS_ATTRIBUTE3 ,
3655 obj_page_value_rec.UDS_ATTRIBUTE4 ,
3656 obj_page_value_rec.UDS_ATTRIBUTE5 ,
3657 obj_page_value_rec.UDS_ATTRIBUTE6 ,
3658 obj_page_value_rec.UDS_ATTRIBUTE7 ,
3659 obj_page_value_rec.UDS_ATTRIBUTE8 ,
3660 obj_page_value_rec.UDS_ATTRIBUTE9 ,
3661 obj_page_value_rec.UDS_ATTRIBUTE10 ,
3662 obj_page_value_rec.UDS_ATTRIBUTE11 ,
3663 obj_page_value_rec.UDS_ATTRIBUTE12 ,
3664 obj_page_value_rec.UDS_ATTRIBUTE13 ,
3665 obj_page_value_rec.UDS_ATTRIBUTE14 ,
3666 obj_page_value_rec.UDS_ATTRIBUTE15 ,
3667 obj_page_value_rec.UDS_ATTRIBUTE16 ,
3668 obj_page_value_rec.UDS_ATTRIBUTE17 ,
3669 obj_page_value_rec.UDS_ATTRIBUTE18 ,
3670 obj_page_value_rec.UDS_ATTRIBUTE19 ,
3671 obj_page_value_rec.UDS_ATTRIBUTE20 ,
3672 x_return_status ,
3673 x_msg_count ,
3674 x_msg_data
3675 );
3676 END LOOP;
3677 END IF;
3678
3679 END IF;
3680 END IF;
3681
3682 --debug_msg('***end');
3683
3684 -- Commit if the flag is set and there is no error
3685 IF (p_commit = FND_API.G_TRUE AND x_return_status = FND_API.g_ret_sts_success )THEN
3686 COMMIT;
3687 END IF;
3688
3689
3690 IF (x_return_status <> FND_API.g_ret_sts_success )THEN
3691 ROLLBACK TO create_next_progress_report;
3692 END IF;
3693
3694
3695 -- Reset the error stack when returning to the calling program
3696 PA_DEBUG.Reset_Err_Stack;
3697
3698
3699 EXCEPTION
3700 WHEN OTHERS THEN
3701 IF p_commit = FND_API.G_TRUE THEN
3702 ROLLBACK TO create_next_progress_report;
3703 END IF;
3704 --
3705 -- Set the exception Message and the stack
3706 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_PROGRESS_REPORT_PVT.create_next_progress_report'
3707 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
3708 --
3709 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3710 RAISE; -- This is optional depending on the needs
3711
3712
3713 END create_report;
3714
3715 PROCEDURE update_project_perccomplete
3716 (
3717 p_version_id NUMBER,
3718
3719 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
3720 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
3721 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3722 ) IS
3723
3724 l_percent_complete number;
3725 l_sch_start_date DATE;
3726 l_sch_end_date DATE;
3727 l_est_start_date DATE;
3728 l_est_end_date DATE;
3729 l_act_start_date DATE;
3730 l_act_end_date DATE;
3731
3732 l_project_id number;
3733 l_task_id number;
3734 l_object_id number;
3735 l_object_type VARCHAR2(30);
3736 l_asof_date DATE;
3737
3738
3739
3740 CURSOR get_percent_complete IS
3741 SELECT attribute7 FROM pa_progress_report_vals
3742 WHERE version_id = p_version_id
3743 AND region_code = 'PA_PROGRESS_PROJECT_DATES';
3744
3745 CURSOR get_object_info IS
3746 SELECT object_id, object_type, report_end_date
3747 FROM pa_progress_report_vers
3748 WHERE version_id = p_version_id;
3749
3750
3751 CURSOR get_project_id IS
3752 SELECT project_id FROM pa_tasks
3753 WHERE task_id = l_task_id;
3754
3755 CURSOR get_dates IS
3756 SELECT scheduled_start_date, scheduled_finish_date,
3757 start_date,completion_date,actual_start_date
3758 ,actual_finish_date
3759 FROM pa_projects_all
3760 WHERE project_id = l_project_id;
3761
3762 BEGIN
3763
3764 -- Initialize the Error Stack
3765 PA_DEBUG.init_err_stack('PA_PROGRESS_REPORT_PVT.update_project_perccomplete');
3766
3767 -- Initialize the return status to success
3768 x_return_status := FND_API.G_RET_STS_SUCCESS;
3769
3770 SAVEPOINT update_project_perccomplete;
3771
3772 OPEN get_object_info;
3773
3774 FETCH get_object_info INTO l_object_id, l_object_type, l_asof_date;
3775
3776 IF get_object_info%notfound THEN
3777 PA_UTILS.Add_Message( p_app_short_name => 'PA'
3778 ,p_msg_name => 'PA_NO_PRJ_REP');
3779 x_return_status := FND_API.G_RET_STS_ERROR;
3780 END IF;
3781
3782 CLOSE get_object_info;
3783
3784 IF l_object_type = 'PA_PROJECTS' THEN
3785 l_project_id := l_object_id;
3786 l_task_id := 0;
3787 ELSE
3788 l_task_id := l_object_id;
3789
3790 OPEN get_project_id;
3791 fetch get_project_id INTO l_project_id;
3792 CLOSE get_project_id;
3793 END IF;
3794
3795 --debug_msg ('before get_percent_complete');
3796
3797
3798 OPEN get_percent_complete;
3799 FETCH get_percent_complete INTO l_percent_complete;
3800
3801 --debug_msg ('before get_percent_complete' || To_char(l_percent_complete));
3802
3803 IF get_percent_complete%notfound THEN
3804 -- PA_UTILS.Add_Message( p_app_short_name => 'PA'
3805 -- ,p_msg_name => 'PA_PR_PERCENT_COMPLETE_INV');
3806 -- x_return_status := FND_API.G_RET_STS_ERROR;
3807 NULL;
3808 -- todo when perc complete is null, do we update?
3809
3810 ELSE
3811 -- todo
3812
3813 --debug_msg ('before get_percent_complete 2' );
3814 pa_percent_complete_pkg.insert_row
3815 (
3816 l_project_id,
3817 l_task_id,
3818 l_percent_complete,
3819 l_asof_date,
3820 NULL,
3821 Sysdate,
3822 fnd_global.user_id,
3823 Sysdate,
3824 fnd_global.user_id,
3825 fnd_global.user_id,
3826 x_return_status,
3827 x_msg_data
3828 );
3829
3830 --debug_msg('x_return_status' || x_return_status);
3831 -- debug_msg('x_msg_data' || x_msg_data);
3832 NULL;
3833
3834 END IF;
3835
3836 CLOSE get_percent_complete;
3837
3838 IF (x_return_status <> FND_API.g_ret_sts_success) THEN
3839
3840 ROLLBACK TO update_project_perccomplete;
3841 RETURN;
3842 END IF;
3843
3844 -- we need to update the dates from PA_PROJECTS_ALL table
3845 OPEN get_dates;
3846 fetch get_dates INTO l_sch_start_date, l_sch_end_date,
3847 l_est_start_date, l_est_end_date, l_act_start_date,
3848 l_act_end_date;
3849 IF get_dates%notfound THEN
3850 CLOSE get_dates;
3851 PA_UTILS.Add_Message( p_app_short_name => 'PA'
3852 ,p_msg_name => 'PA_PR_DATES_INV');
3853 x_return_status := FND_API.G_RET_STS_ERROR;
3854
3855 ROLLBACK TO update_project_perccomplete;
3856 PA_DEBUG.Reset_Err_Stack;
3857 RETURN;
3858 END IF;
3859 CLOSE get_dates;
3860
3861
3862 IF l_object_type = 'PA_PROJECTS' THEN
3863
3864 -- UPDATE pa_progress_report_vals
3865 -- SET
3866 -- attribute1= To_char(l_sch_start_date, 'YYYY-MM-DD')
3867 -- ,attribute2= To_char(l_sch_end_date, 'YYYY-MM-DD')
3868 -- ,attribute3 = To_char(l_est_start_date, 'YYYY-MM-DD')
3869 -- ,attribute4= To_char(l_est_end_date, 'YYYY-MM-DD')
3870 -- ,attribute5= To_char(l_act_start_date, 'YYYY-MM-DD')
3871 -- ,attribute6= To_char(l_act_end_date, 'YYYY-MM-DD')
3872 -- WHERE version_id = p_version_id
3873 -- AND region_code = 'PA_PROGRESS_PROJECT_DATES';
3874
3875 NULL;
3876 END IF;
3877
3878 IF (x_return_status <> FND_API.g_ret_sts_success) THEN
3879
3880 ROLLBACK TO update_project_perccomplete;
3881 END IF;
3882
3883 EXCEPTION
3884 WHEN OTHERS THEN
3885 ROLLBACK TO update_project_perccomplete;
3886
3887 --
3888 -- Set the excetption Message and the stack
3889 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_Progress_Report_PVT.update_project_perccomplete'
3890 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
3891 --
3892 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3893 RAISE; -- This is optional depending on the needs
3894 END update_project_perccomplete;
3895
3896 /*
3897 Bug 3684164. This API is called when the user updates a status
3898 report page layout. If any sections have been deleted, this API
3899 would take care of deleting the data from the working and rejected
3900 status report versions using this page layout.
3901 */
3902 PROCEDURE delete_version_data
3903 (
3904 p_page_id IN pa_page_layouts.page_id%TYPE
3905 ,p_region_source_type_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE
3906 ,p_region_code_tbl IN SYSTEM.PA_VARCHAR2_2000_TBL_TYPE
3907 ,p_region_source_code_tbl IN SYSTEM.PA_VARCHAR2_2000_TBL_TYPE
3908 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3909 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3910 ,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
3911 AS
3912
3913 l_msg_count NUMBER := 0;
3914 l_data VARCHAR2(2000);
3915 l_msg_data VARCHAR2(2000);
3916 l_return_status VARCHAR2(1);
3917 l_debug_mode VARCHAR2(30);
3918 l_msg_index_out NUMBER;
3919 l_debug_level NUMBER;
3920
3921 /*
3922 This cursor fetches all non mandatory regions selected currently for this page
3923 Currently data can be associated only for the regions of style STD,
3924 STD_CUST and DFF. So this cursor will fetch only these regions.
3925 */
3926 cursor c_page_layout_regions(c_page_id pa_page_layouts.page_id%TYPE)
3927 is
3928 select
3929 layout.region_source_type,
3930 layout.view_region_code,
3931 layout.region_source_code
3932 from pa_page_layout_regions layout, pa_page_type_regions type
3933 where layout.page_id = c_page_id
3934 and layout.region_source_type in ('STD','STD_CUST','DFF')
3935 and type.page_type_code = 'PPR'
3936 and type.region_source_type = layout.region_source_type
3937 and type.region_source_code = decode(layout.region_source_type,'STD_CUST',layout.view_region_code,layout.region_source_code)
3938 and nvl(layout.region_style, 'N') <> 'LINK'
3939 and type.mandatory_flag = 'N';
3940
3941 Type region_source_type_tbl_typ is table of pa_page_layout_regions.region_source_type%TYPE index by binary_integer;
3942 Type view_region_code_tbl_typ is table of pa_page_layout_regions.view_region_code%TYPE index by binary_integer;
3943 Type region_source_code_tbl_typ is table of pa_page_layout_regions.region_source_code%TYPE index by binary_integer;
3944
3945 l_region_source_type_tbl region_source_type_tbl_typ;
3946 l_view_region_code_tbl view_region_code_tbl_typ;
3947 l_region_source_code_tbl region_source_code_tbl_typ;
3948
3949 l_found boolean;
3950 j number;
3951 l_temp_region_src_code pa_page_layout_regions.region_source_code%TYPE;
3952 l_module_name varchar2(100) := 'PA_PROGRESS_REPORT_PVT';
3953
3954 BEGIN
3955 x_msg_count := 0;
3956 x_return_status := FND_API.G_RET_STS_SUCCESS;
3957 pa_debug.set_err_stack('PA_PROGRESS_REPORT_PVT.delete_version_data');
3958 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
3959 fnd_profile.get('AFLOG_LEVEL',l_debug_level);
3960 l_debug_mode := NVL(l_debug_mode, 'Y');
3961 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
3962
3963 if nvl(l_debug_mode,'N') = 'N' then
3964 --if debug is not enabled, set the level to a higher level
3965 l_debug_level := 10;
3966 end if;
3967
3968 open c_page_layout_regions(p_page_id);
3969 fetch c_page_layout_regions
3970 bulk collect into l_region_source_type_tbl, l_view_region_code_tbl, l_region_source_code_tbl;
3971 close c_page_layout_regions;
3972
3973 if (l_debug_level <= 3) then
3974 pa_debug.g_err_stage := 'number of records fetched :'||l_region_source_type_tbl.count;
3975 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
3976 end if;
3977 --If no records exist for the page layout do nothing and return.
3978 if(nvl(l_region_source_type_tbl.count,0) = 0) then
3979 return;
3980 end if;
3981 --Loop through the fetched records. If any of the record is not
3982 --available in the passed in record, delete the corresponding records
3983 --from the status report versions data table.
3984 for i in 1..l_region_source_type_tbl.count loop
3985 l_found := false;
3986 j := 1;
3987 if (l_debug_level <= 3) then
3988 pa_debug.g_err_stage := 'fetched source type:'||l_region_source_type_tbl(i);
3989 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
3990 pa_debug.g_err_stage := 'fetched region code:'||l_view_region_code_tbl(i);
3991 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
3992 pa_debug.g_err_stage := 'fetched region source code:'||l_region_source_code_tbl(i);
3993 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
3994 end if;
3995 while l_found = false and j <= p_region_source_type_tbl.count loop
3996 if (l_debug_level <= 3) then
3997 pa_debug.g_err_stage := 'passed source type:'||p_region_source_type_tbl(j);
3998 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
3999 pa_debug.g_err_stage := 'passed region code:'||p_region_code_tbl(j);
4000 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
4001 pa_debug.g_err_stage := 'passed region source code:'||p_region_source_code_tbl(j);
4002 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
4003 end if;
4004 if p_region_source_type_tbl(j) = 'STD_CUST' then
4005 l_temp_region_src_code := p_region_code_tbl(j) || ':' || p_region_source_code_tbl(j);
4006 else
4007 l_temp_region_src_code := p_region_source_code_tbl(j);
4008 end if;
4009
4010 if( l_region_source_type_tbl(i) = p_region_source_type_tbl(j) and
4011 l_view_region_code_tbl(i) = p_region_code_tbl(j) and
4012 l_region_source_code_tbl(i) = l_temp_region_src_code
4013 ) then
4014 if (l_debug_level <= 3) then
4015 pa_debug.g_err_stage := 'found the above region';
4016 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
4017 end if;
4018 l_found := true;
4019 end if;
4020 j := j + 1;
4021 end loop;
4022
4023 --If the above region was not found, then we need to delete any usage of this region of this page
4024 --in the status reports. For STD_CUST sections the region code is stored as <_oracle_apps..._viewid>
4025 if l_found = false then
4026 l_temp_region_src_code := null;
4027 if l_region_source_type_tbl(i) = 'STD_CUST' then
4028 l_temp_region_src_code := replace(l_region_source_code_tbl(i),'/','_');
4029 l_temp_region_src_code := replace(l_temp_region_src_code,':','_');
4030 else
4031 l_temp_region_src_code := l_region_source_code_tbl(i);
4032 end if;
4033
4034 if (l_debug_level <= 3) then
4035 pa_debug.g_err_stage := 'l_temp_region_src_code :'||l_temp_region_src_code;
4036 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
4037 end if;
4038
4039 delete from pa_progress_report_vals
4040 where region_code = l_temp_region_src_code
4041 and region_source_type = l_region_source_type_tbl(i)
4042 and version_id in
4043 (select version_id
4044 from pa_progress_report_vers
4045 where page_id = p_page_id
4046 and report_status_code in ('PROGRESS_REPORT_WORKING','PROGRESS_REPORT_REJECTED'));
4047 end if;
4048 end loop;
4049
4050 pa_debug.reset_err_stack;
4051 EXCEPTION
4052 WHEN others THEN
4053
4054 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4055 x_msg_count := 1;
4056 x_msg_data := SQLERRM;
4057 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_PROGRESS_REPORT_PVT'
4058 ,p_procedure_name => 'delete_version_data');
4059 pa_debug.g_err_stage:= 'Unexpected Error'||SQLERRM;
4060 pa_debug.reset_err_stack;
4061 RAISE;
4062 END delete_version_data;
4063
4064 END PA_PROGRESS_REPORT_PVT;
4065