[Home] [Help]
PACKAGE BODY: APPS.PA_PROGRESS_REPORT_PVT
Source
1 package body PA_PROGRESS_REPORT_PVT as
2 /* $Header: PAPRRPVB.pls 120.4.12010000.2 2009/06/17 07:21:15 jngeorge 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 /* Commenting below for bug 7521888
1365 IF l_ret <> 'Y' THEN
1366 PA_UTILS.Add_Message( p_app_short_name => 'PA'
1367 ,p_msg_name => 'PA_UPDATE_REPORT_INV');
1368 x_return_status := FND_API.G_RET_STS_ERROR;
1369 END IF;
1370 IF (p_validate_only <>FND_API.g_true AND x_return_status = FND_API.g_ret_sts_success) then
1371 */
1372 IF (p_validate_only <>FND_API.g_true AND l_ret = 'Y') then -- bug 7521888
1373 --debug_msg ('before update 2' || To_char(l_reported_by));
1374 --debug_msg ('before update 3' || p_overview);
1375 --debug_msg ('before update 4' || p_progress_status);
1376
1377 pa_progress_report_pkg.UPDATE_PROGRESS_REPORT_VER_ROW
1378 (
1379 p_version_id,
1380 NULL,
1381 NULL,
1382 NULL,
1383 NULL,
1384 NULL,
1385
1386 Trunc(p_report_start_date) ,
1387 Trunc(p_report_end_date) ,
1388 l_reported_by,
1389 p_progress_status ,
1390 p_overview ,
1391 'N',
1392 NULL,
1393 NULL,
1394 NULL,
1395
1396 p_record_version_number,
1397 NULL,
1398 null,
1399 x_return_status ,
1400 x_msg_count ,
1401 x_msg_data
1402 );
1403
1404 END IF;
1405
1406
1407 -- Commit if the flag is set and there is no error
1408 IF (p_commit = FND_API.G_TRUE AND x_return_status = FND_API.g_ret_sts_success )THEN
1409 COMMIT;
1410 END IF;
1411
1412 -- Reset the error stack when returning to the calling program
1413 PA_DEBUG.Reset_Err_Stack;
1414
1415 --debug_msg ('after update in PVT ');
1416
1417 EXCEPTION
1418 WHEN OTHERS THEN
1419 --debug_msg ('exception ');
1420 IF p_commit = FND_API.G_TRUE THEN
1421 ROLLBACK TO update_report_details;
1422 END IF;
1423 --
1424 -- Set the excetption Message and the stack
1425 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_PROGRESS_REPORT_PVT.update_report_details'
1426 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
1427 --
1428 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1429 RAISE; -- This is optional depending on the needs
1430
1431 END update_report_details;
1432
1433
1434 PROCEDURE define_progress_report_setup
1435 (
1436 p_api_version IN NUMBER := 1.0,
1437 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
1438 p_commit IN VARCHAR2 := FND_API.g_false,
1439 p_validate_only IN VARCHAR2 := FND_API.g_true,
1440 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
1441
1442 p_object_id IN NUMBER := NULL,
1443 p_object_type IN VARCHAR2 := NULL,
1444 p_page_type_code IN VARCHAR2 := 'PPR',
1445 p_page_id IN NUMBER := NULL,
1446 p_page_name IN VARCHAR2 := NULL,
1447 p_approval_required IN VARCHAR2 := NULL,
1448 --p_auto_publish IN VARCHAR2 := NULL,
1449 p_report_cycle_id IN NUMBER := NULL,
1450 p_report_offset_days IN NUMBER := NULL,
1451 p_next_reporting_date IN DATE := NULL,
1452 p_reminder_days IN NUMBER := NULL,
1453 p_reminder_days_type IN VARCHAR2 := NULL,
1454 p_initial_progress_status IN VARCHAR2 := NULL,
1455 p_final_progress_status IN VARCHAR2 := NULL,
1456 p_rollup_progress_status IN VARCHAR2 := NULL,
1457 p_report_type_id IN NUMBER:= NULL,
1458 p_approver_source_id IN NUMBER:= NULL,
1459 p_approver_source_name IN VARCHAR2:= NULL,
1460 p_approver_source_type IN NUMBER:= NULL,
1461 p_effective_from IN DATE:= NULL,
1462 p_effective_to IN DATE:= NULL,
1463 p_object_page_layout_id IN NUMBER := NULL,
1464 p_action_set_id IN NUMBER := NULL,
1465 p_record_version_number IN NUMBER := NULL,
1466 p_function_name IN VARCHAR2,
1467 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1468 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1469 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1470 )
1471 IS
1472
1473 l_rowid ROWID;
1474 l_report_end_date date;
1475 l_next_reporting_date DATE;
1476 l_list_id number;
1477 x_object_page_layout_id number;
1478 l_list_item_id number;
1479 l_page_id NUMBER;
1480 page_id_l NUMBER;
1481 approval_required_l VARCHAR2(1);
1482 l_approver_source_id NUMBER;
1483 l_approver_source_type NUMBER;
1484 l_new_action_set_id NUMBER;
1485 l_object_page_layout_id NUMBER := NULL;
1486 l_return_status VARCHAR2(10) := NULL;
1487 l_msg_count NUMBER;
1488 l_msg_data VARCHAR2(10) := NULL;
1489
1490 CURSOR check_object_page_layout_exits
1491 IS
1492 SELECT page_id, approval_required
1493 FROM pa_object_page_layouts
1494 WHERE
1495 object_page_layout_id = p_object_page_layout_id;
1496
1497
1498 CURSOR get_page_id
1499 IS
1500 SELECT page_id
1501 FROM pa_page_layouts
1502 WHERE page_name = p_page_name
1503 AND page_type_code = p_page_type_code;
1504
1505 CURSOR report_type_exists
1506 IS
1507 SELECT ROWID
1508 FROM pa_report_types
1509 WHERE
1510 report_type_id = p_report_type_id;
1511
1512 BEGIN
1513
1514 -- Initialize the Error Stack
1515 PA_DEBUG.init_err_stack('PA_PROGRESS_REPORT_PVT.define_progress_report_setup');
1516 -- Initialize the return status to success
1517 x_return_status := FND_API.G_RET_STS_SUCCESS;
1518
1519 l_object_page_layout_id := p_object_page_layout_id;
1520 -- Issue API savepoint if the transaction is to be committed
1521
1522 IF p_commit = FND_API.G_TRUE THEN
1523 SAVEPOINT define_progress_report_setup;
1524 END IF;
1525
1526
1527 -- Bug# ,3302984 Added the code to handle OA Personalization.
1528 l_page_id := p_page_id;
1529 IF ((l_page_id is null) AND (p_function_name is not null)) THEN
1530
1531 PA_PAGE_LAYOUT_PUB.Create_Page_Layout(
1532 p_validate_only => fnd_api.g_false,
1533 p_page_name => p_page_name,
1534 p_page_type => p_page_type_code,
1535 p_function_name => p_function_name,
1536 p_description => null,
1537 p_start_date => sysdate,
1538 p_shortcut_menu_id => null,
1539 x_page_id => l_page_id,
1540 x_return_status => l_return_status,
1541 x_msg_count => l_msg_count,
1542 x_msg_data => l_msg_data
1543 );
1544 END IF;
1545
1546 --To keep the code in sync with the existing one, return is issued.
1547 IF (x_return_status <> FND_API.g_ret_sts_success) THEN
1548 ROLLBACK TO define_progress_report_setup;
1549 END IF;
1550
1551 -- check mandatory version_id
1552 --Bug#3302984 use l_page_id
1553 --IF (p_page_id IS NULL) THEN
1554 IF (l_page_id IS NULL) THEN
1555
1556 -- added by syao
1557 -- for PPR if there is any non published progress report
1558 -- do not allow delete
1559
1560 IF (p_page_type_code = 'PPR') THEN
1561 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
1562 -- we have to quit,
1563 PA_UTILS.Add_Message( p_app_short_name => 'PA'
1564 ,p_msg_name => 'PA_REPORT_TYPE_REMOVE_INV');
1565 x_return_status := FND_API.G_RET_STS_ERROR;
1566 RETURN;
1567
1568 END IF;
1569
1570 END IF;
1571
1572
1573 -- if page_name is not passed in, we will remove the pagelayout
1574 IF (p_page_name IS NULL) then
1575 -- remove from pa_object_page_layouts table
1576
1577 --debug_msg('delete from pa_object_page_layouts' );
1578
1579 DELETE FROM pa_object_page_layouts
1580 WHERE object_id = p_object_id AND object_type = p_object_type
1581 AND page_type_code = p_page_type_code
1582 and nvl(report_Type_id,-99) = nvl(p_report_type_id,-99);
1583
1584 if (p_page_type_code = 'PPR') then
1585 PA_OBJECT_DIST_LISTS_PVT.DELETE_ASSOC_DIST_LISTS(p_validate_only => 'F',
1586 P_OBJECT_TYPE => 'PA_OBJECT_PAGE_LAYOUT',
1587 P_OBJECT_ID => p_object_page_layout_id,
1588 x_return_status => x_return_status,
1589 x_msg_count => x_msg_count,
1590 x_msg_data => x_msg_data);
1591
1592 pa_proj_stat_actset.delete_action_set (p_object_id => p_object_page_layout_id
1593 ,p_validate_only => 'F'
1594 ,x_return_status => x_return_status
1595 ,x_msg_count => x_msg_count
1596 ,x_msg_data => x_msg_data);
1597 end if;
1598
1599 --debug_msg('delete from pa_object_page_layouts done' );
1600
1601
1602 RETURN;
1603 ELSE
1604
1605 -- we need to check the page_name
1606 OPEN get_page_id;
1607 FETCH get_page_id INTO l_page_id;
1608
1609
1610 IF (get_page_id%notfound ) THEN
1611 PA_UTILS.Add_Message( p_app_short_name => 'PA'
1612 ,p_msg_name => 'PA_PAGE_LAYOUT_NAME_INV');
1613 x_return_status := FND_API.G_RET_STS_ERROR;
1614 RETURN;
1615
1616 ELSE
1617 -- we have the valid page_id now
1618 NULL;
1619 END if;
1620
1621 CLOSE get_page_id;
1622
1623 END IF;
1624
1625 --Bug#3302984 Commented Else.
1626 --ELSE
1627 -- l_page_id := p_page_id;
1628
1629 --PA_UTILS.Add_Message( p_app_short_name => 'PA'
1630 -- ,p_msg_name => 'PA_PAGE_ID_INV');
1631 --x_return_status := FND_API.G_RET_STS_ERROR;
1632 END IF;
1633
1634 --debug_msg('object_id ' || To_char(p_object_id));
1635 --debug_msg('object_type ' || p_object_type);
1636 --debug_msg('p_report_cycle_id ' || To_char(p_report_cycle_id));
1637 --debug_msg('p_report_cycle_id ' || To_char(p_report_offset_days));
1638
1639 --debug_msg ('approval_required:' || p_approval_required);
1640
1641 IF ( p_page_type_code = 'PPR') then
1642
1643 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
1644 OPEN check_object_page_layout_exits;
1645 FETCH check_object_page_layout_exits INTO page_id_l, approval_required_l;
1646 close check_object_page_layout_exits;
1647 if (page_id_l <> l_page_id or approval_required_l <> p_approval_required) then
1648 -- we have to quit,
1649 PA_UTILS.Add_Message( p_app_short_name => 'PA'
1650 ,p_msg_name => 'PA_REPORT_TYPE_EDIT_INV');
1651 x_return_status := FND_API.G_RET_STS_ERROR;
1652 RETURN;
1653 end if;
1654
1655 END IF;
1656
1657 If (p_effective_to is not null and p_effective_from is null) or
1658 (p_effective_to < p_effective_from) THEN
1659 PA_UTILS.Add_Message( p_app_short_name => 'PA'
1660 ,p_msg_name => 'PA_EFFECTIVE_ED_DATE_INV');
1661 x_return_status := 'E';
1662 End If;
1663
1664 if (p_approver_source_id is null and p_approver_source_name is not null) then
1665 begin
1666 SELECT resource_source_id, resource_type_id
1667 INTO l_approver_source_id, l_approver_source_type
1668 FROM pa_people_lov_v
1669 WHERE name = p_approver_source_name;
1670 exception when TOO_MANY_ROWS then
1671 PA_UTILS.Add_Message( p_app_short_name => 'PA'
1672 ,p_msg_name => 'PA_APPR_SOURCE_NAME_MULTIPLE');
1673 x_return_status := 'E';
1674 when OTHERS then
1675 PA_UTILS.Add_Message( p_app_short_name => 'PA'
1676 ,p_msg_name => 'PA_APPR_SOURCE_NAME_INV');
1677 x_return_status := 'E';
1678 end;
1679
1680 else
1681 l_approver_source_id := p_approver_source_id;
1682 l_approver_source_type := p_approver_source_type;
1683 End if;
1684
1685 /* Bug No. 2636791 -- Changed the error message and logic for its display
1686 Now the user gets error message when approval required checkbox is checked
1687 and the user has not entered any approver name */
1688
1689 /* Bug No. 2636791 -- commented this */
1690 /* If (l_approver_source_id is not null and p_approval_required = 'N') then
1691 PA_UTILS.Add_Message( p_app_short_name => 'PA'
1692 ,p_msg_name => 'PA_PS_APPROVER_ERR');
1693 x_return_status := 'E';
1694 End If; */
1695
1696 /* Bug 2753246 no need for this check as HR manager is the default approver)
1697 If (l_approver_source_id is null and p_approval_required <> 'N') then
1698 PA_UTILS.Add_Message( p_app_short_name => 'PA'
1699 ,p_msg_name => 'PA_APPR_SOURCE_NAME_INV');
1700 x_return_status := 'E';
1701 End If; */
1702
1703
1704
1705 IF (p_next_reporting_date IS NULL AND
1706 p_report_cycle_id IS NOT null) THEN
1707 pa_progress_report_utils.get_report_start_end_dates
1708 (
1709 p_object_type,
1710 p_object_id,
1711 p_report_type_id,
1712 p_report_cycle_id,
1713 p_report_offset_days,
1714
1715 'Y',
1716 p_effective_from,
1717 l_next_reporting_date,
1718 l_report_end_date
1719
1720 --x_return_status ,
1721 --x_msg_count ,
1722 --x_msg_data
1723 );
1724 ELSE
1725 l_next_reporting_date := NULL;
1726 l_report_end_date := NULL;
1727
1728 END IF;
1729 ELSIF (p_page_type_code = 'TPR') then
1730 l_report_end_date := p_next_reporting_date;
1731 ELSE
1732 l_next_reporting_date := NULL;
1733 l_report_end_date := NULL;
1734 END IF;
1735
1736 IF (p_page_type_code = 'PPR') THEN
1737 OPEN report_type_exists;
1738 FETCH report_type_exists INTO l_rowid;
1739 IF report_type_exists%NOTFOUND THEN
1740 PA_UTILS.Add_Message( p_app_short_name => 'PA'
1741 ,p_msg_name => 'PA_PS_REPORT_TYPE_INVALID');
1742 x_return_status := FND_API.G_RET_STS_ERROR;
1743 RETURN;
1744 ELSE
1745 -- we have the valid report_type_id
1746 NULL;
1747 END if;
1748 CLOSE report_type_exists;
1749 END IF;
1750
1751 IF x_return_status = FND_API.g_ret_sts_success THEN
1752
1753 OPEN check_object_page_layout_exits;
1754 FETCH check_object_page_layout_exits INTO page_id_l, approval_required_l;
1755
1756 IF check_object_page_layout_exits%NOTFOUND THEN
1757 CLOSE check_object_page_layout_exits;
1758
1759 -- we will insert into pa_object_page_layouts
1760 IF (p_validate_only <>FND_API.g_true AND x_return_status = FND_API.g_ret_sts_success) THEN
1761
1762 pa_progress_report_pkg.insert_object_page_layout_row
1763 (
1764 P_OBJECT_ID ,
1765 P_OBJECT_TYPE ,
1766 L_PAGE_ID ,
1767 P_PAGE_TYPE_CODE ,
1768
1769 P_APPROVAL_REQUIRED ,
1770 --P_AUTO_PUBLISH ,
1771 P_REPORT_CYCLE_ID ,
1772 P_REPORT_OFFSET_DAYS ,
1773
1774 -- by msundare request, store end date in the table
1775 l_report_end_date ,
1776 P_REMINDER_DAYS ,
1777 P_REMINDER_DAYS_TYPE ,
1778 P_INITIAL_PROGRESS_STATUS,
1779 P_FINAL_PROGRESS_STATUS,
1780 P_ROLLUP_PROGRESS_STATUS,
1781 p_report_type_id,
1782 l_approver_source_id,
1783 l_approver_source_type,
1784 p_effective_from,
1785 p_effective_to,
1786 p_function_name,
1787 x_object_page_layout_id ,
1788 x_return_status ,
1789 x_msg_count ,
1790 x_msg_data
1791
1792 );
1793
1794 if (p_page_type_code = 'PPR' and x_return_status = 'S') then
1795 PA_DISTRIBUTION_LISTS_PVT.CREATE_DIST_LIST(p_validate_only => 'F',
1796 p_init_msg_list => 'F',
1797 P_LIST_ID => l_list_id,
1798 p_name => null,
1799 p_description => null,
1800 x_return_status => x_return_status,
1801 x_msg_count => x_msg_count,
1802 x_msg_data => x_msg_data);
1803
1804 if (x_return_status = 'S') then
1805 PA_DISTRIBUTION_LISTS_PVT.CREATE_DIST_LIST_ITEM(p_validate_only => 'F',
1806 p_init_msg_list => 'F',
1807 P_LIST_ITEM_ID => l_list_item_id,
1808 P_LIST_ID => l_list_id,
1809 P_RECIPIENT_TYPE => 'PROJECT_ROLE',
1810 P_RECIPIENT_ID => 1,
1811 P_ACCESS_LEVEL => 2,
1812 p_menu_id => null,
1813 x_return_status => x_return_status,
1814 x_msg_count => x_msg_count,
1815 x_msg_data => x_msg_data);
1816 end if;
1817
1818 if (x_return_status = 'S') then
1819 PA_OBJECT_DIST_LISTS_PVT.CREATE_OBJECT_DIST_LIST(p_validate_only => 'F',
1820 p_init_msg_list => 'F',
1821 P_LIST_ID => l_list_id,
1822 P_OBJECT_TYPE => 'PA_OBJECT_PAGE_LAYOUT',
1823 P_OBJECT_ID => x_object_page_layout_id,
1824 x_return_status => x_return_status,
1825 x_msg_count => x_msg_count,
1826 x_msg_data => x_msg_data);
1827 end if;
1828
1829
1830 end if;
1831
1832 END IF;
1833 -- Save object_page_layout_id to update action sets - mw
1834 l_object_page_layout_id := x_object_page_layout_id;
1835 ELSE
1836 CLOSE check_object_page_layout_exits;
1837
1838 -- we will insert into pa_object_page_layouts
1839 IF (p_validate_only <>FND_API.g_true AND x_return_status = FND_API.g_ret_sts_success) THEN
1840
1841
1842 pa_progress_report_pkg.update_object_page_layout_row
1843 (
1844 P_OBJECT_ID ,
1845 P_OBJECT_TYPE ,
1846 L_PAGE_ID ,
1847 P_PAGE_TYPE_CODE ,
1848
1849 P_APPROVAL_REQUIRED ,
1850 --P_AUTO_PUBLISH ,
1851 P_REPORT_CYCLE_ID ,
1852 P_REPORT_OFFSET_DAYS ,
1853 l_report_end_date ,
1854
1855 P_REMINDER_DAYS ,
1856 P_REMINDER_DAYS_TYPE ,
1857 P_INITIAL_PROGRESS_STATUS,
1858 P_FINAL_PROGRESS_STATUS,
1859 P_ROLLUP_PROGRESS_STATUS,
1860
1861 p_report_type_id,
1862 l_approver_source_id,
1863 l_approver_source_type,
1864 p_effective_from,
1865 p_effective_to,
1866 p_object_page_layout_id,
1867
1868 p_record_version_number,
1869 p_function_name,
1870 x_return_status ,
1871 x_msg_count ,
1872 x_msg_data
1873
1874 );
1875
1876
1877 END IF;
1878
1879 END IF;
1880
1881 END IF;
1882
1883
1884 --Add, Delete or replace action sets
1885 IF (x_return_status = 'S') THEN
1886 IF p_page_type_code = 'PPR' and l_object_page_layout_id is NOT NULL THEN
1887 PA_PROJ_STAT_ACTSET.update_action_set(
1888 p_action_set_id => p_action_set_id
1889 ,p_object_id => l_object_page_layout_id
1890 ,p_commit => p_commit
1891 ,p_validate_only => p_validate_only
1892 ,p_init_msg_list => 'F'
1893 ,x_new_action_set_id => l_new_action_set_id
1894 ,x_return_status => x_return_status
1895 ,x_msg_count => x_msg_count
1896 ,x_msg_data => x_msg_data);
1897 ELSE
1898 IF p_page_type_code = 'TPR' and l_object_page_layout_id is NOT NULL THEN
1899 PA_TASK_PROG_ACTSET.update_action_set(
1900 p_action_set_id => p_action_set_id
1901 ,p_object_id => p_object_id
1902 ,p_commit => p_commit
1903 ,p_validate_only => p_validate_only
1904 ,p_init_msg_list => 'F'
1905 ,x_new_action_set_id => l_new_action_set_id
1906 ,x_return_status => x_return_status
1907 ,x_msg_count => x_msg_count
1908 ,x_msg_data => x_msg_data);
1909 END IF;
1910 END IF;
1911 END IF;
1912
1913 -- Commit if the flag is set and there is no error
1914 IF (p_commit = FND_API.G_TRUE AND x_return_status = FND_API.g_ret_sts_success )THEN
1915 COMMIT;
1916 END IF;
1917
1918 if (x_msg_count >= 1) then
1919 x_return_status := FND_API.g_ret_sts_Error;
1920 end if;
1921
1922 -- Reset the error stack when returning to the calling program
1923 PA_DEBUG.Reset_Err_Stack;
1924
1925
1926 EXCEPTION
1927 WHEN OTHERS THEN
1928 IF p_commit = FND_API.G_TRUE THEN
1929 ROLLBACK TO define_progress_report_setup;
1930 END IF;
1931 --
1932 -- Set the excetption Message and the stack
1933 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_PROGRESS_REPORT_PVT.define_progress_report_setup'
1934 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
1935 --
1936 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1937 RAISE; -- This is optional depending on the needs
1938
1939
1940
1941 END define_progress_report_setup;
1942
1943 PROCEDURE delete_report
1944 (
1945 p_api_version IN NUMBER := 1.0,
1946 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
1947 p_commit IN VARCHAR2 := FND_API.g_false,
1948 p_validate_only IN VARCHAR2 := FND_API.g_true,
1949 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
1950
1951 p_version_id IN NUMBER :=NULL,
1952 p_record_version_number IN NUMBER := NULL,
1953
1954 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1955 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1956 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1957 )
1958
1959 IS
1960
1961 l_rowid ROWID;
1962 l_item_key wf_item_activity_statuses.item_key%TYPE; --Bug 5217292
1963 l_wf_status VARCHAR2(30);
1964 l_dummy VARCHAR2(1);
1965
1966 l_item_type VARCHAR2(30);
1967
1968
1969 CURSOR get_item_key IS
1970 SELECT MAX(pwp.item_key), max(pwp.item_type)
1971 from pa_wf_processes pwp, pa_project_statuses pps,
1972 pa_progress_report_vers pprv
1973 where pwp.item_type = pps.WORKFLOW_ITEM_TYPE
1974 and pps.status_type = 'PROGRESS_REPORT'
1975 and pps.project_status_code = 'PROGRESS_REPORT_SUBMITTED'
1976 AND entity_key2 = p_version_id
1977 AND pwp.wf_type_code = 'Progress Report'
1978 AND entity_key1 = pprv.object_id
1979 AND pprv.version_id = p_version_id
1980 AND pprv.object_type = 'PA_PROJECTS';
1981
1982 -- CURSOR get_wf_status IS
1983 -- select 'Y' FROM dual
1984 -- WHERE exists
1985 -- (SELECT *
1986 -- from wf_item_activity_statuses
1987 -- WHERE item_type = 'PAWFPPRA'
1988 -- AND item_key = l_item_key
1989 -- AND activity_status = 'ACTIVE');
1990 CURSOR get_wf_status IS
1991 select 'Y' FROM dual
1992 WHERE exists
1993 (SELECT *
1994 from wf_item_activity_statuses wias, pa_project_statuses pps
1995 WHERE wias.item_type = pps.WORKFLOW_ITEM_TYPE
1996 AND wias.item_key = l_item_key
1997 AND wias.activity_status = 'ACTIVE'
1998 AND pps.status_type = 'PROGRESS_REPORT'
1999 AND pps.project_status_code = 'PROGRESS_REPORT_SUBMITTED');
2000
2001
2002 CURSOR get_template_type
2003 is
2004 SELECT meaning FROM pa_lookups
2005 WHERE lookup_type = 'PA_PAGE_TYPES'
2006 AND lookup_code = 'PPR';
2007
2008
2009 CURSOR check_status_submitted IS
2010 SELECT ROWID
2011 FROM pa_progress_report_vers
2012 WHERE version_id = p_version_id
2013 AND report_status_code = 'PROGRESS_REPORT_SUBMITTED';
2014
2015
2016 l_type VARCHAR2(80); /* bug 2447763 */
2017
2018
2019 BEGIN
2020 -- Initialize the Error Stack
2021 PA_DEBUG.init_err_stack('PA_PROGRESS_REPORT_PVT.Delete_Report');
2022
2023 -- Initialize the return status to success
2024 x_return_status := FND_API.G_RET_STS_SUCCESS;
2025
2026 -- Issue API savepoint if the transaction is to be committed
2027 IF p_commit = FND_API.G_TRUE THEN
2028 SAVEPOINT delete_report;
2029 END IF;
2030
2031 OPEN get_template_type;
2032 FETCH get_template_type INTO l_type;
2033 CLOSE get_template_type;
2034
2035 -- check mandatory version_id
2036 IF (p_version_id IS NULL) then
2037 PA_UTILS.Add_Message( p_app_short_name => 'PA'
2038 ,p_msg_name => 'PA_PR_VERSION_ID_INV'
2039 , p_token1 => 'TEMPLATE_TYPE'
2040 , p_value1 => l_type);
2041 x_return_status := FND_API.G_RET_STS_ERROR;
2042 END IF;
2043
2044
2045 -- cancel the workflow which if it is already launched.
2046 open check_status_submitted;
2047 FETCH check_status_submitted INTO l_rowid;
2048
2049 IF check_status_submitted%FOUND THEN
2050 CLOSE check_status_submitted;
2051 OPEN get_item_key;
2052 FETCH get_item_key INTO l_item_key, l_item_type;
2053
2054 IF get_item_key%found THEN
2055 CLOSE get_item_key;
2056
2057 -- is the workflow running
2058 -- only cancel when the workflow is running
2059
2060 OPEN get_wf_status;
2061 FETCH get_wf_status INTO l_wf_status;
2062
2063 IF (get_wf_status%notfound or
2064 l_wf_status <> 'Y' ) THEN
2065 NULL;
2066
2067 else
2068
2069 pa_progress_report_workflow.cancel_workflow
2070 (
2071 l_item_type
2072 , l_item_key
2073 , x_msg_count
2074 , x_msg_data
2075 , x_return_status
2076 );
2077
2078 IF (l_dummy = 'N') THEN
2079 x_return_status := FND_API.G_RET_STS_SUCCESS;
2080 END IF;
2081
2082 END IF;
2083
2084 CLOSE get_wf_status;
2085
2086 ELSE
2087
2088 CLOSE get_item_key;
2089 -- does not find item key for the workflow
2090 -- return
2091
2092 -- if workflow is required, we are canceling the submission
2093 -- failed, return error.
2094 -- IF (l_dummy = 'Y' or l_dummy = 'A' )THEN
2095 -- PA_UTILS.Add_Message( p_app_short_name => 'PA'
2096 -- ,p_msg_name => 'PA_PR_CANCEL_WORKFLOW_INV');
2097 -- x_return_status := FND_API.G_RET_STS_ERROR;
2098 NULL;
2099 END IF;
2100 ELSE
2101 CLOSE check_status_submitted;
2102
2103 END IF;
2104
2105
2106 IF (p_validate_only <>FND_API.g_true AND x_return_status = FND_API.g_ret_sts_success) then
2107
2108 -- delete all records in pa_progress_report_vals under the p_version_id
2109 pa_progress_report_pkg.delete_progress_report_vals
2110 (
2111 p_version_id,
2112 x_return_status ,
2113 x_msg_count ,
2114 x_msg_data
2115 );
2116
2117 IF (x_return_status = FND_API.g_ret_sts_success) THEN
2118
2119 -- delete record in pa_progress_report_vers under the p_version_id
2120 pa_progress_report_pkg.delete_progress_report_ver_row
2121 (
2122 p_version_id,
2123 p_record_version_number,
2124 x_return_status ,
2125 x_msg_count ,
2126 x_msg_data
2127 );
2128
2129 END IF;
2130
2131 END IF;
2132
2133
2134 -- Commit if the flag is set and there is no error
2135 IF (p_commit = FND_API.G_TRUE AND x_return_status = FND_API.g_ret_sts_success )THEN
2136 COMMIT;
2137 END IF;
2138
2139 -- Reset the error stack when returning to the calling program
2140 PA_DEBUG.Reset_Err_Stack;
2141
2142
2143 EXCEPTION
2144 WHEN OTHERS THEN
2145 IF p_commit = FND_API.G_TRUE THEN
2146 ROLLBACK TO delete_report;
2147 END IF;
2148 --
2149 -- Set the excetption Message and the stack
2150 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_PROGRESS_REPORT_PVT.Delete_Report'
2151 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
2152 --
2153 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2154 RAISE; -- This is optional depending on the needs
2155
2156 END delete_report;
2157
2158 PROCEDURE rework_report
2159 (
2160 p_api_version IN NUMBER := 1.0,
2161 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
2162 p_commit IN VARCHAR2 := FND_API.g_false,
2163 p_validate_only IN VARCHAR2 := FND_API.g_true,
2164 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
2165
2166 p_version_id IN NUMBER := NULL,
2167 p_record_version_number IN NUMBER := NULL,
2168
2169 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2170 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2171 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2172 ) IS
2173
2174 l_rowid ROWID;
2175 l_item_key wf_item_activity_statuses.item_key%TYPE; --Bug 5217292
2176 l_wf_status VARCHAR2(30);
2177 l_item_type VARCHAR2(30);
2178 l_dummy VARCHAR2(30) := 'N';
2179
2180 CURSOR check_auto_approve IS
2181 SELECT 'Y' FROM dual
2182 WHERE exists
2183 (
2184 SELECT p1.approval_required
2185 FROM pa_object_page_layouts p1,
2186 pa_progress_report_vers p2
2187 WHERE
2188 p2.version_id = p_version_id
2189 AND p2.object_id = p1.object_id
2190 AND p2.object_type = p1.object_type
2191 AND (p1.approval_required = 'Y'
2192 OR p1.approval_required = 'A'
2193 )
2194 );
2195
2196
2197 CURSOR get_item_key IS
2198 SELECT MAX(pwp.item_key), max(pwp.item_type)
2199 from pa_wf_processes pwp, pa_project_statuses pps,
2200 pa_progress_report_vers pprv
2201 where pwp.item_type = pps.WORKFLOW_ITEM_TYPE
2202 and pps.status_type = 'PROGRESS_REPORT'
2203 and pps.project_status_code = 'PROGRESS_REPORT_SUBMITTED'
2204 AND entity_key2 = p_version_id
2205 AND entity_key1 = pprv.object_id
2206 AND wf_type_code = 'Progress Report'
2207 AND pprv.version_id = p_version_id
2208 AND pprv.object_type = 'PA_PROJECTS';
2209
2210 -- CURSOR get_wf_status IS
2211 -- select 'Y' FROM dual
2212 -- WHERE exists
2213 -- (SELECT *
2214 -- from wf_item_activity_statuses
2215 -- WHERE item_type = 'PAWFPPRA'
2216 -- AND item_key = l_item_key
2217 -- AND activity_status = 'ACTIVE');
2218
2219 CURSOR get_wf_status IS
2220 select 'Y' FROM dual
2221 WHERE exists
2222 (SELECT *
2223 from wf_item_activity_statuses wias, pa_project_statuses pps
2224 WHERE wias.item_type = pps.WORKFLOW_ITEM_TYPE
2225 AND wias.item_key = l_item_key
2226 AND wias.activity_status = 'ACTIVE'
2227 AND pps.status_type = 'PROGRESS_REPORT'
2228 AND pps.project_status_code = 'PROGRESS_REPORT_SUBMITTED');
2229
2230 CURSOR get_template_type
2231 is
2232 SELECT meaning FROM pa_lookups
2233 WHERE lookup_type = 'PA_PAGE_TYPES'
2234 AND lookup_code = 'PPR';
2235
2236 l_type VARCHAR2(80); /* bug 2447763 */
2237
2238 BEGIN
2239 -- Initialize the Error Stack
2240 PA_DEBUG.init_err_stack('PA_PROGRESS_REPORT_PVT.Rework_Report');
2241
2242 -- Initialize the return status to success
2243 x_return_status := FND_API.G_RET_STS_SUCCESS;
2244
2245 -- Issue API savepoint if the transaction is to be committed
2246 SAVEPOINT rework_report;
2247
2248 OPEN get_template_type;
2249 FETCH get_template_type INTO l_type;
2250 CLOSE get_template_type;
2251
2252 -- check mandatory version_id
2253 IF (p_version_id IS NULL) then
2254 PA_UTILS.Add_Message( p_app_short_name => 'PA'
2255 ,p_msg_name => 'PA_PR_VERSION_ID_INV'
2256 , p_token1 => 'TEMPLATE_TYPE'
2257 , p_value1 => l_type);
2258 x_return_status := FND_API.G_RET_STS_ERROR;
2259 END IF;
2260
2261
2262 IF (p_validate_only <>FND_API.g_true AND x_return_status = FND_API.g_ret_sts_success) then
2263
2264 -- set the status to working first
2265
2266 /*
2267 pa_progress_report_pkg.UPDATE_PROGRESS_REPORT_VER_ROW
2268 (
2269 p_version_id ,
2270 NULL,
2271 NULL,
2272 NULL,
2273 NULL,
2274 'PROGRESS_REPORT_WORKING',
2275
2276
2277 NULL,
2278 NULL,
2279 NULL,
2280 NULL,
2281 NULL,
2282 NULL,
2283 NULL,
2284 NULL,
2285 NULL,
2286
2287 P_RECORD_VERSION_NUMBER ,
2288 NULL,
2289 x_return_status ,
2290 x_msg_count ,
2291 x_msg_data
2292 );*/
2293 change_report_status
2294 (
2295 p_version_id => p_version_id ,
2296 p_report_status => 'PROGRESS_REPORT_WORKING',
2297 p_record_version_number => p_record_version_number,
2298 -- p_summary_version_number => NULL,
2299 --p_cancel_comment=> p_cancel_comments,
2300 --p_cancel_date =>Sysdate,
2301
2302 x_return_status => x_return_status,
2303 x_msg_count => x_msg_count,
2304 x_msg_data => x_msg_data
2305 ) ;
2306
2307 IF (x_return_status = FND_API.g_ret_sts_success) THEN
2308 -- cancel the approval workflow process if that is already launched
2309 -- todo
2310
2311 -- check whether report approval is required.
2312
2313 OPEN check_auto_approve;
2314 FETCH check_auto_approve INTO l_dummy;
2315 IF (check_auto_approve%notfound OR l_dummy <> 'Y') THEN
2316 l_dummy := 'N';
2317 END if;
2318
2319 CLOSE check_auto_approve;
2320
2321
2322 OPEN get_item_key;
2323 FETCH get_item_key INTO l_item_key, l_item_type;
2324
2325 IF get_item_key%found THEN
2326 CLOSE get_item_key;
2327
2328 -- is the workflow running
2329 -- only cancel when the workflow is running
2330
2331 OPEN get_wf_status;
2332 FETCH get_wf_status INTO l_wf_status;
2333
2334 IF (get_wf_status%notfound or
2335 l_wf_status <> 'Y' ) THEN
2336 IF (l_dummy = 'Y' or l_dummy = 'A') then
2337 PA_UTILS.Add_Message( p_app_short_name => 'PA'
2338 ,p_msg_name => 'PA_PR_CANCEL_WORKFLOW_INV');
2339 --todo
2340 -- x_return_status := FND_API.G_RET_STS_ERROR;
2341 END IF;
2342
2343 else
2344
2345 pa_progress_report_workflow.cancel_workflow
2346 (
2347 l_item_type
2348 , l_item_key
2349 , x_msg_count
2350 , x_msg_data
2351 , x_return_status
2352 );
2353
2354 IF (l_dummy = 'N') THEN
2355 x_return_status := FND_API.G_RET_STS_SUCCESS;
2356 END IF;
2357
2358 END IF;
2359
2360 CLOSE get_wf_status;
2361
2362 ELSE
2363 -- does not find item key for the workflow
2364 -- return
2365
2366 -- if workflow is required, we are canceling the submission
2367 -- failed, return error.
2368 IF (l_dummy = 'Y' or l_dummy = 'A' )THEN
2369 PA_UTILS.Add_Message( p_app_short_name => 'PA'
2370 ,p_msg_name => 'PA_PR_CANCEL_WORKFLOW_INV');
2371 x_return_status := FND_API.G_RET_STS_ERROR;
2372
2373 END IF;
2374
2375 END IF;
2376
2377 END IF;
2378
2379
2380 END IF;
2381
2382
2383 -- Commit if the flag is set and there is no error
2384 IF (p_commit = FND_API.G_TRUE AND x_return_status = FND_API.g_ret_sts_success )THEN
2385 COMMIT;
2386 END IF;
2387
2388 IF (x_return_status <> FND_API.g_ret_sts_success )THEN
2389 ROLLBACK TO rework_report;
2390 END IF;
2391
2392 -- Reset the error stack when returning to the calling program
2393 PA_DEBUG.Reset_Err_Stack;
2394
2395
2396 EXCEPTION
2397 WHEN OTHERS THEN
2398 IF p_commit = FND_API.G_TRUE THEN
2399 ROLLBACK TO rework_report;
2400 END IF;
2401 --
2402 -- Set the excetption Message and the stack
2403 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_PROGRESS_REPORT_PVT.Rework_Report'
2404 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
2405 --
2406 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2407 RAISE; -- This is optional depending on the needs
2408
2409 END rework_report;
2410
2411 PROCEDURE publish_report
2412 (
2413 p_api_version IN NUMBER := 1.0,
2414 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
2415 p_commit IN VARCHAR2 := FND_API.g_false,
2416 p_validate_only IN VARCHAR2 := FND_API.g_true,
2417 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
2418
2419 p_version_id IN NUMBER := NULL,
2420 p_record_version_number IN NUMBER := NULL,
2421 p_summary_version_number IN NUMBER := NULL,
2422
2423 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2424 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2425 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2426 ) IS
2427
2428 l_rowid ROWID;
2429 l_dummy VARCHAR2(1);
2430
2431 l_version_id NUMBER;
2432
2433 CURSOR check_auto_approve IS
2434 SELECT p1.approval_required
2435 FROM pa_object_page_layouts p1,
2436 pa_progress_report_vers p2
2437 WHERE
2438 p2.version_id = l_version_id
2439 AND p2.object_id = p1.object_id
2440 AND p2.object_type = p1.object_type;
2441
2442 CURSOR get_template_type
2443 is
2444 SELECT meaning FROM pa_lookups
2445 WHERE lookup_type = 'PA_PAGE_TYPES'
2446 AND lookup_code = 'PPR';
2447
2448 --Bug#3302984, added the function name parameter
2449 CURSOR get_setup_info
2450 IS SELECT
2451 popl.object_id, popl.object_type, popl.reporting_cycle_id,
2452 popl.report_offset_days, popl.approval_required,
2453 popl.reminder_days, popl.reminder_days_type, popl.record_version_number,popl.pers_function_name,
2454 popl.page_type_code, popl.initial_progress_status, popl.final_progress_status,popl.rollup_progress_status,
2455 popl.report_type_id, popl.approver_source_id, popl.approver_source_type, popl.effective_from,
2456 popl.effective_to, popl.object_page_layout_id
2457 FROM pa_object_page_layouts popl, pa_progress_report_vers pprv
2458 WHERE pprv.version_id = p_version_id
2459 AND popl.object_id = pprv.object_id
2460 AND popl.object_type = pprv.object_type
2461 AND popl.page_type_code = 'PPR'
2462 AND popl.report_type_id = pprv.report_type_id;
2463
2464 l_approval_required VARCHAR2(1);
2465 l_reminder_days NUMBER;
2466 l_reminder_days_type VARCHAR2(30);
2467 l_type VARCHAR2(80); /* bug 2447763 */
2468
2469 l_next_reporting_date DATE;
2470 l_report_end_date DATE;
2471
2472 l_object_type VARCHAR2(30);
2473 l_object_id NUMBER;
2474 l_report_cycle_id NUMBER;
2475 l_report_offset_days number;
2476 l_record_version_number NUMBER;
2477 l_function_name VARCHAR2(30);
2478 l_page_type_code VARCHAR2(30);
2479 l_initial_progress_status VARCHAR2(30);
2480 l_final_progress_status VARCHAR2(30);
2481 l_rollup_progress_status VARCHAR2(1);
2482
2483 l_report_type_id NUMBER;
2484 l_approver_source_id NUMBER;
2485 l_approver_source_type NUMBER;
2486 l_effective_from DATE;
2487 l_effective_to DATE;
2488 l_object_page_layout_id NUMBER;
2489
2490 BEGIN
2491 -- Initialize the Error Stack
2492 PA_DEBUG.init_err_stack('PA_PROGRESS_REPORT_PVT.Publish_Report');
2493
2494 -- Initialize the return status to success
2495 x_return_status := FND_API.G_RET_STS_SUCCESS;
2496
2497 -- Issue API savepoint if the transaction is to be committed
2498 SAVEPOINT publish_report;
2499
2500 OPEN get_template_type;
2501 FETCH get_template_type INTO l_type;
2502 CLOSE get_template_type;
2503
2504
2505 -- check mandatory version_id
2506 IF (p_version_id IS NULL) then
2507 PA_UTILS.Add_Message( p_app_short_name => 'PA'
2508 ,p_msg_name => 'PA_PR_VERSION_ID_INV'
2509 , p_token1 => 'TEMPLATE_TYPE'
2510 , p_value1 => l_type);
2511 x_return_status := FND_API.G_RET_STS_ERROR;
2512 END IF;
2513
2514
2515 IF (p_validate_only <>FND_API.g_true AND x_return_status = FND_API.g_ret_sts_success) then
2516
2517 -- set the status to working first
2518
2519 --debug_msg('UPDATE_PROGRESS_REPORT_VER_ROW');
2520 /*
2521 pa_progress_report_pkg.UPDATE_PROGRESS_REPORT_VER_ROW
2522 (
2523 p_version_id,
2524 NULL,
2525 NULL,
2526 NULL,
2527 NULL,
2528 'PROGRESS_REPORT_PUBLISHED',
2529
2530
2531 NULL,
2532 NULL,
2533 NULL,
2534 NULL,
2535 NULL,
2536 NULL,
2537 SYSDATE,
2538 NULL,
2539 NULL,
2540
2541
2542 --NULL,
2543 P_RECORD_VERSION_NUMBER ,
2544 P_SUMMARY_VERSION_NUMBER ,
2545 x_return_status ,
2546 x_msg_count ,
2547 x_msg_data
2548 );*/
2549
2550 change_report_status
2551 (
2552 p_version_id => p_version_id ,
2553 p_report_status => 'PROGRESS_REPORT_PUBLISHED',
2554 p_record_version_number => p_record_version_number,
2555 p_summary_version_number => p_summary_version_number,
2556 p_published_date => Sysdate,
2557 --p_cancel_comment=> p_cancel_comments,
2558 --p_cancel_date =>Sysdate,
2559
2560 x_return_status => x_return_status,
2561 x_msg_count => x_msg_count,
2562 x_msg_data => x_msg_data
2563 ) ;
2564
2565 IF (x_return_status = FND_API.g_ret_sts_success) THEN
2566
2567 OPEN check_auto_approve;
2568
2569 FETCH check_auto_approve INTO l_dummy;
2570
2571 IF (l_dummy = 'Y' or l_dummy = 'A') THEN
2572 -- if auto approve, the information has been saved already
2573 -- we do not need to save anything
2574 NULL;
2575 else
2576 -- update information in other tables
2577 --update_project_perccomplete(p_version_id,
2578 -- x_return_status,
2579 -- x_msg_count,
2580 -- x_msg_data);
2581
2582 NULL;
2583
2584 END IF;
2585
2586 -- by msundare request, need to change the next report end date
2587
2588 OPEN get_setup_info;
2589 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,
2590 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,
2591 l_report_type_id,
2592 l_approver_source_id,
2593 l_approver_source_type,
2594 l_effective_from,
2595 l_effective_to,
2596 l_object_page_layout_id;
2597
2598 CLOSE get_setup_info;
2599
2600 IF (l_report_cycle_id IS NOT null) then
2601
2602
2603 --debug_msg('get_report_start_end_dates');
2604 pa_progress_report_utils.get_report_start_end_dates
2605 (
2606 l_object_type,
2607 l_object_id,
2608 l_report_type_id,
2609 l_report_cycle_id,
2610 l_report_offset_days,
2611
2612 'Y',
2613 null,
2614 l_next_reporting_date,
2615 l_report_end_date
2616 );
2617
2618
2619 --debug_msg('update_object_page_layout_row');
2620 pa_progress_report_pkg.update_object_page_layout_row
2621 (
2622 l_OBJECT_ID ,
2623 l_OBJECT_TYPE ,
2624 null ,
2625 l_page_type_code ,
2626
2627 l_approval_required ,
2628 --P_AUTO_PUBLISH ,
2629 l_report_cycle_id ,
2630 l_report_offset_days,
2631
2632 l_report_end_date ,
2633 l_REMINDER_DAYS ,
2634 l_REMINDER_DAYS_TYPE ,
2635 l_initial_progress_status,
2636 l_final_progress_status,
2637 l_rollup_progress_status,
2638 l_report_type_id,
2639 l_approver_source_id,
2640 l_approver_source_type,
2641 l_effective_from,
2642 l_effective_to,
2643 l_object_page_layout_id,
2644
2645 l_record_version_number,
2646 l_function_name,
2647 x_return_status ,
2648 x_msg_count ,
2649 x_msg_data
2650
2651 );
2652 END IF;
2653
2654
2655 -- debug_msg('update_object_page_layout_row' || x_return_status);
2656
2657
2658
2659 CLOSE check_auto_approve;
2660 END IF;
2661
2662 END IF;
2663
2664
2665
2666
2667
2668 -- Commit if the flag is set and there is no error
2669 IF (p_commit = FND_API.G_TRUE AND x_return_status = FND_API.g_ret_sts_success )THEN
2670 COMMIT;
2671 END IF;
2672
2673 IF (x_return_status <> FND_API.g_ret_sts_success )THEN
2674 ROLLBACK TO publish_report;
2675 END IF;
2676 -- Reset the error stack when returning to the calling program
2677 PA_DEBUG.Reset_Err_Stack;
2678
2679
2680 EXCEPTION
2681 WHEN OTHERS THEN
2682 ROLLBACK TO publish_report;
2683
2684 --
2685 -- Set the excetption Message and the stack
2686 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_PROGRESS_REPORT_PVT.Publish_Report'
2687 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
2688 --
2689 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2690 RAISE; -- This is optional depending on the needs
2691
2692 END publish_report;
2693
2694 PROCEDURE submit_report
2695 (
2696 p_api_version IN NUMBER := 1.0,
2697 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
2698 p_commit IN VARCHAR2 := FND_API.g_false,
2699 p_validate_only IN VARCHAR2 := FND_API.g_true,
2700 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
2701
2702 p_version_id IN NUMBER := NULL,
2703 p_record_version_number IN NUMBER := NULL,
2704 p_summary_version_number IN NUMBER := NULL,
2705
2706 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2707 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2708 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2709 ) IS
2710
2711 l_rowid ROWID;
2712
2713 l_err_code NUMBER;
2714 l_err_stage VARCHAR2(30);
2715 l_err_stack VARCHAR2(240);
2716 l_project_id NUMBER;
2717
2718
2719 CURSOR get_object_id IS
2720 SELECT object_id
2721 FROM pa_progress_report_vers
2722 WHERE version_id = p_version_id
2723 AND object_type = 'PA_PROJECTS'
2724 AND report_status_code = 'PROGRESS_REPORT_WORKING';
2725
2726
2727
2728 l_item_key wf_item_activity_statuses.item_key%TYPE; --Bug 5217292
2729 l_dummy VARCHAR2(1);
2730
2731 CURSOR get_template_type
2732 is
2733 SELECT meaning FROM pa_lookups
2734 WHERE lookup_type = 'PA_PAGE_TYPES'
2735 AND lookup_code = 'PPR';
2736
2737 l_type VARCHAR2(80); /* bug 2447763 */
2738
2739 l_wf_item_type VARCHAR2(30);
2740 l_wf_process_name VARCHAR2(30);
2741 l_wf_enable VARCHAR2(1);
2742
2743 CURSOR get_wf_process_name
2744 IS
2745 select
2746 WORKFLOW_ITEM_TYPE,
2747 workflow_process, enable_wf_flag from pa_project_statuses
2748 where status_type = 'PROGRESS_REPORT'
2749 AND project_status_code = 'PROGRESS_REPORT_SUBMITTED';
2750
2751 BEGIN
2752 -- Initialize the Error Stack
2753 PA_DEBUG.init_err_stack('PA_PROGRESS_REPORT_PVT.Submit_Report');
2754
2755 -- Initialize the return status to success
2756 x_return_status := FND_API.G_RET_STS_SUCCESS;
2757
2758 -- Issue API savepoint if the transaction is to be committed
2759 SAVEPOINT submit_report;
2760
2761
2762 OPEN get_template_type;
2763 FETCH get_template_type INTO l_type;
2764 CLOSE get_template_type;
2765
2766 -- check mandatory version_id
2767 IF (p_version_id IS NULL) then
2768 PA_UTILS.Add_Message( p_app_short_name => 'PA'
2769 ,p_msg_name => 'PA_PR_VERSION_ID_INV'
2770 , p_token1 => 'TEMPLATE_TYPE'
2771 , p_value1 => l_type);
2772
2773 x_return_status := FND_API.G_RET_STS_ERROR;
2774 END IF;
2775
2776 OPEN get_object_id;
2777
2778 FETCH get_object_id INTO l_project_id;
2779
2780 IF get_object_id%notfound THEN
2781 PA_UTILS.Add_Message( p_app_short_name => 'PA'
2782 ,p_msg_name => 'PA_PR_STATUS_NOT_WORKING');
2783 x_return_status := FND_API.G_RET_STS_ERROR;
2784 END IF;
2785
2786 CLOSE get_object_id;
2787
2788 IF (p_validate_only <>FND_API.g_true AND x_return_status = FND_API.g_ret_sts_success) then
2789
2790 -- set the status to working first
2791
2792 pa_progress_report_pkg.UPDATE_PROGRESS_REPORT_VER_ROW
2793 (
2794 p_version_id,
2795 NULL,
2796 NULL,
2797 NULL,
2798 NULL,
2799 'PROGRESS_REPORT_SUBMITTED',
2800
2801
2802 NULL,
2803 NULL,
2804 NULL,
2805 NULL,
2806 NULL,
2807 'N',
2808 NULL,
2809 NULL,
2810 NULL,
2811
2812
2813 P_RECORD_VERSION_NUMBER ,
2814 p_summary_version_number ,
2815
2816 null,
2817 x_return_status ,
2818 x_msg_count ,
2819 x_msg_data
2820 );
2821
2822 IF (x_return_status = FND_API.g_ret_sts_success) THEN
2823 -- update information in other tables
2824 --update_project_perccomplete(p_version_id,
2825 -- x_return_status,
2826 -- x_msg_count,
2827 -- x_msg_data);
2828
2829 IF(x_return_status = FND_API.g_ret_sts_success )THEN
2830 -- launch the workflow process
2831
2832 -- added by syao
2833 -- get the workflow process name
2834 open get_wf_process_name;
2835 fetch get_wf_process_name INTO l_wf_item_type,
2836 l_wf_process_name, l_wf_enable;
2837
2838 IF get_wf_process_name%found AND l_wf_enable = 'Y'THEN
2839 CLOSE get_wf_process_name;
2840
2841 pa_progress_report_workflow.start_workflow
2842 (
2843 -- 'PAWFPPRA'
2844 -- , 'PA_PROG_REP_APPRVL_MP'
2845 l_wf_item_type
2846 , l_wf_process_name
2847 , p_version_id
2848 , l_item_key
2849 , x_msg_count
2850 , x_msg_data
2851 , x_return_status
2852 );
2853
2854 IF(x_return_status = FND_API.g_ret_sts_success )THEN
2855
2856 -- update pa_wf_process_table
2857 PA_WORKFLOW_UTILS.Insert_WF_Processes
2858 (p_wf_type_code => 'Progress Report'
2859 -- ,p_item_type => 'PAWFPPRA'
2860 ,p_item_type => l_wf_item_type
2861 ,p_item_key => l_item_key
2862 ,p_entity_key1 => l_project_id
2863 ,p_entity_key2 => p_version_id
2864 ,p_description => l_wf_process_name
2865 ,p_err_code => l_err_code
2866 ,p_err_stage => l_err_stage
2867 ,p_err_stack => l_err_stack
2868 );
2869
2870 IF l_err_code <> 0 THEN
2871 PA_UTILS.Add_Message( p_app_short_name => 'PA'
2872 ,p_msg_name => 'PA_PR_CREATE_WF_FAILED');
2873 x_return_status := FND_API.G_RET_STS_ERROR;
2874
2875 END IF;
2876
2877
2878 ELSE
2879 PA_UTILS.Add_Message( p_app_short_name => 'PA'
2880 ,p_msg_name => 'PA_PR_CREATE_WF_FAILED');
2881 x_return_status := FND_API.G_RET_STS_ERROR;
2882
2883 END IF;
2884 ELSE
2885 CLOSE get_wf_process_name;
2886
2887 END IF;
2888
2889 END IF;
2890
2891
2892 END IF;
2893
2894
2895 END IF;
2896
2897
2898
2899 -- Commit if the flag is set and there is no error
2900 IF (p_commit = FND_API.G_TRUE AND x_return_status = FND_API.g_ret_sts_success )THEN
2901 COMMIT;
2902 END IF;
2903
2904 IF (x_return_status <> FND_API.g_ret_sts_success )THEN
2905 ROLLBACK TO submit_report;
2906 END IF;
2907
2908 -- Reset the error stack when returning to the calling program
2909 PA_DEBUG.Reset_Err_Stack;
2910
2911
2912 EXCEPTION
2913 WHEN OTHERS THEN
2914
2915 ROLLBACK TO submit_report;
2916
2917 --
2918 -- Set the excetption Message and the stack
2919 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_PROGRESS_REPORT_PVT.Submit_Report'
2920 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
2921 --
2922 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2923 RAISE; -- This is optional depending on the needs
2924
2925 END submit_report;
2926
2927 PROCEDURE change_report_status
2928 (
2929 p_api_version IN NUMBER := 1.0,
2930 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
2931 p_commit IN VARCHAR2 := FND_API.g_false,
2932 p_validate_only IN VARCHAR2 := FND_API.g_false,
2933 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
2934
2935 p_version_id IN NUMBER := NULL,
2936 p_report_status IN VARCHAR2 := NULL,
2937 p_record_version_number IN NUMBER := NULL,
2938 p_summary_version_number IN NUMBER := NULL,
2939 p_published_date IN DATE := NULL,
2940 p_cancel_comment IN VARCHAR2 := NULL,
2941 p_cancel_date IN DATE := NULL,
2942
2943 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2944 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2945 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2946 ) IS
2947
2948 l_wf_enable VARCHAR2(1);
2949 l_wf_item_type VARCHAR2(30);
2950 l_wf_process VARCHAR2(30);
2951 l_success_code VARCHAR2(30);
2952 l_failure_code VARCHAR2(30);
2953 l_err_code NUMBER;
2954 l_err_stage VARCHAR2(30);
2955 l_err_stack VARCHAR2(240);
2956 l_project_id NUMBER;
2957 l_report_end_date DATE;
2958 l_report_type_id NUMBER;
2959 l_current_flag VARCHAR2(1);
2960 x_report_end_date DATE;
2961 x_version_id NUMBER;
2962 l_record_version_number NUMBER;
2963 l_summary_version_number NUMBER;
2964 l_item_key wf_item_activity_statuses.item_key%TYPE; --Bug 5217292
2965 l_dummy VARCHAR2(1);
2966
2967 CURSOR get_object_id IS
2968 SELECT object_id, report_end_date, report_type_id
2969 FROM pa_progress_report_vers
2970 WHERE version_id = p_version_id
2971 AND object_type = 'PA_PROJECTS';
2972
2973 CURSOR get_wf_info is
2974 select enable_wf_flag, workflow_item_type,
2975 workflow_process,wf_success_status_code,
2976 wf_failure_status_code from pa_project_statuses
2977 where project_status_code = p_report_status;
2978
2979
2980 --like 'PROGRESS_REPORT%'
2981
2982 BEGIN
2983 -- Initialize the Error Stack
2984 PA_DEBUG.init_err_stack('PA_PROGRESS_REPORT_PVT.Change_Report_Status');
2985
2986 -- Initialize the return status to success
2987 x_return_status := FND_API.G_RET_STS_SUCCESS;
2988
2989 -- get the project_id
2990 OPEN get_object_id;
2991
2992 FETCH get_object_id INTO l_project_id,l_report_end_date, l_report_type_id;
2993
2994 -- IF get_object_id%notfound THEN
2995 -- PA_UTILS.Add_Message( p_app_short_name => 'PA'
2996 -- ,p_msg_name => 'PA_PR_STATUS_NOT_WORKING');
2997 --x_return_status := FND_API.G_RET_STS_ERROR;
2998 --END IF;
2999 CLOSE get_object_id;
3000
3001 --- Check for the last published report and compare dates. If date is
3002 --- greater than last one then current_flag = 'Y' else 'N'
3003
3004 if (p_report_status = 'PROGRESS_REPORT_PUBLISHED') then
3005 begin
3006 select version_id,report_end_Date ,
3007 record_version_number, summary_version_number
3008 into x_version_id,x_report_end_date,
3009 l_record_version_number, l_summary_version_number
3010 from pa_progress_report_vers
3011 where object_id = l_project_id
3012 and object_Type = 'PA_PROJECTS'
3013 and report_type_id = l_report_Type_id
3014 and page_type_code = 'PPR'
3015 and current_flag = 'Y';
3016
3017 if (l_report_end_date >= x_report_end_Date) then
3018 l_current_flag := 'Y';
3019 pa_progress_report_pkg.UPDATE_PROGRESS_REPORT_VER_ROW
3020 (
3021 x_version_id,
3022 NULL,
3023 NULL,
3024 NULL,
3025 NULL,
3026 NULL,
3027
3028
3029 NULL,
3030 NULL,
3031 NULL,
3032 NULL,
3033 NULL,
3034 'N',
3035 null,
3036 null,
3037 null,
3038
3039
3040 l_RECORD_VERSION_NUMBER ,
3041 l_summary_version_number ,
3042
3043 null,
3044 x_return_status ,
3045 x_msg_count ,
3046 x_msg_data
3047 );
3048 else
3049 l_current_flag := 'N';
3050 end if;
3051 exception
3052 when no_data_found then
3053 l_current_flag := 'Y';
3054 when others then
3055 l_current_flag := 'N';
3056 end;
3057 else
3058 l_current_flag := 'N';
3059 end if;
3060
3061 -- change the progress status, launch the workflow if necessary
3062 IF (p_validate_only <>FND_API.g_true AND x_return_status = FND_API.g_ret_sts_success) then
3063 pa_progress_report_pkg.UPDATE_PROGRESS_REPORT_VER_ROW
3064 (
3065 p_version_id,
3066 NULL,
3067 NULL,
3068 NULL,
3069 NULL,
3070 p_report_status,
3071
3072
3073 NULL,
3074 NULL,
3075 NULL,
3076 NULL,
3077 NULL,
3078 l_current_flag,
3079 p_published_date,
3080 p_cancel_comment,
3081 p_cancel_date,
3082
3083
3084 P_RECORD_VERSION_NUMBER ,
3085 p_summary_version_number ,
3086
3087 null,
3088 x_return_status ,
3089 x_msg_count ,
3090 x_msg_data
3091 );
3092 IF (x_return_status = FND_API.g_ret_sts_success )THEN
3093
3094
3095 -- check whether to launch the workflow
3096 OPEN get_wf_info;
3097 FETCH get_wf_info INTO l_wf_enable, l_wf_item_type, l_wf_process,
3098 l_success_code,l_failure_code;
3099 IF (get_wf_info%found) then
3100 IF l_wf_enable = 'Y' THEN
3101
3102
3103
3104 -- launch the workflow for the report status change
3105
3106 pa_progress_report_workflow.start_workflow
3107 (
3108 -- 'PAWFPPRA'
3109 -- , 'PA_PROG_REP_APPRVL_MP'
3110 l_wf_item_type
3111 , l_wf_process
3112 , p_version_id
3113 , l_item_key
3114 , x_msg_count
3115 , x_msg_data
3116 , x_return_status
3117 );
3118
3119 IF(x_return_status = FND_API.g_ret_sts_success )THEN
3120
3121 -- update pa_wf_process_table
3122 PA_WORKFLOW_UTILS.Insert_WF_Processes
3123 (p_wf_type_code => 'Progress Report'
3124 -- ,p_item_type => 'PAWFPPRA'
3125 ,p_item_type => l_wf_item_type
3126 ,p_item_key => l_item_key
3127 ,p_entity_key1 => l_project_id
3128 ,p_entity_key2 => p_version_id
3129 ,p_description => l_wf_process
3130 ,p_err_code => l_err_code
3131 ,p_err_stage => l_err_stage
3132 ,p_err_stack => l_err_stack
3133 );
3134
3135 IF l_err_code <> 0 THEN
3136 PA_UTILS.Add_Message( p_app_short_name => 'PA'
3137 ,p_msg_name => 'PA_PR_CREATE_WF_FAILED');
3138 x_return_status := FND_API.G_RET_STS_ERROR;
3139
3140 END IF;
3141
3142
3143 ELSE
3144 PA_UTILS.Add_Message( p_app_short_name => 'PA'
3145 ,p_msg_name => 'PA_PR_CREATE_WF_FAILED');
3146 x_return_status := FND_API.G_RET_STS_ERROR;
3147
3148 END IF;
3149 END IF;
3150 END IF;
3151 CLOSE get_wf_info;
3152
3153
3154
3155 END IF;
3156
3157 END IF;
3158
3159 -- Commit if the flag is set and there is no error
3160 IF (p_commit = FND_API.G_TRUE AND x_return_status = FND_API.g_ret_sts_success )THEN
3161 COMMIT;
3162 END IF;
3163
3164 -- Reset the error stack when returning to the calling program
3165 PA_DEBUG.Reset_Err_Stack;
3166
3167 EXCEPTION
3168 WHEN OTHERS THEN
3169
3170 --
3171 -- Set the excetption Message and the stack
3172 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_PROGRESS_REPORT_PVT.Change_Report_Status'
3173 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
3174 --
3175 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3176 RAISE; -- This is optional depending on the needs
3177 END change_report_status;
3178
3179
3180 PROCEDURE create_report
3181 (
3182 p_api_version IN NUMBER := 1.0,
3183 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
3184 p_commit IN VARCHAR2 := FND_API.g_false,
3185 p_validate_only IN VARCHAR2 := FND_API.g_true,
3186 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
3187
3188 p_object_id IN NUMBER := NULL,
3189 p_object_type IN VARCHAR2 := NULL,
3190 p_report_type_id IN NUMBER := NULL,
3191
3192 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
3193 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
3194 x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
3195 x_version_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3196 ) IS
3197
3198 l_rowid ROWID;
3199 l_report_start_date DATE;
3200 l_report_end_date DATE;
3201 l_last_published_version_id NUMBER;
3202 l_page_id NUMBER;
3203 l_page_type VARCHAR2(30);
3204 l_reporting_cycle_id NUMBER;
3205 l_report_offset_days NUMBER;
3206 l_progress_status_code VARCHAR2(30);
3207 l_effective_from_date Date;
3208
3209 CURSOR get_page_id_type
3210 IS
3211 SELECT popl.page_id, popl.page_type_code, popl.reporting_cycle_id, popl.report_offset_days,popl.effective_from
3212 FROM pa_object_page_layouts popl, pa_page_layouts ppl
3213 WHERE popl.object_id = p_object_id
3214 AND popl.object_type = p_object_type
3215 AND popl.report_type_id = p_report_type_id
3216 AND popl.page_id = ppl.page_id
3217 AND popl.page_type_code = 'PPR';
3218
3219
3220 CURSOR report_exist
3221 IS
3222 SELECT rowid
3223 FROM pa_progress_report_vers
3224 WHERE object_id = p_object_id
3225 AND object_type = p_object_type
3226 AND report_type_id = p_report_type_id
3227 AND report_status_code = 'PROGRESS_REPORT_PUBLISHED';
3228
3229 /* Commented and changed as below for bug 5956107
3230 CURSOR get_last_published_version_id
3231 IS
3232 SELECT version_id
3233 FROM pa_progress_report_vers p1
3234 WHERE
3235 p1.object_id = p_object_id
3236 AND p1.object_type = p_object_type
3237 ANd p1.report_type_id = p_report_type_id
3238 AND p1.report_status_code = 'PROGRESS_REPORT_PUBLISHED'
3239 AND ROWNUM =1
3240 AND p1.report_end_date =
3241 (
3242 SELECT MAX(report_end_date)
3243 FROM pa_progress_report_vers
3244 WHERE
3245 object_id = p_object_id
3246 AND object_type = p_object_type
3247 AND report_type_id = p_report_type_id
3248 AND report_status_code = 'PROGRESS_REPORT_PUBLISHED'
3249 ); */
3250
3251 CURSOR get_last_published_version_id
3252 IS
3253 SELECT version_id
3254 FROM pa_progress_report_vers p1
3255 WHERE
3256 p1.object_id = p_object_id
3257 AND p1.object_type = p_object_type
3258 ANd p1.report_type_id = p_report_type_id
3259 AND p1.report_status_code = 'PROGRESS_REPORT_PUBLISHED'
3260 AND ROWNUM =1
3261 AND p1.published_date =
3262 (
3263 SELECT MAX(published_date)
3264 FROM pa_progress_report_vers
3265 WHERE
3266 object_id = p_object_id
3267 AND object_type = p_object_type
3268 AND report_type_id = p_report_type_id
3269 AND report_status_code = 'PROGRESS_REPORT_PUBLISHED'
3270 );
3271
3272 CURSOR get_all_regions IS
3273 SELECT *
3274 FROM pa_progress_report_vals
3275 WHERE version_id = l_last_published_version_id;
3276
3277 CURSOR get_publish_overview IS
3278 SELECT overview, progress_status_code, REPORT_START_DATE
3279 FROM pa_progress_report_vers
3280 WHERE version_id = l_last_published_version_id;
3281
3282 CURSOR get_all_regions_from_template IS
3283 SELECT pplr.*
3284 FROM pa_page_layout_regions pplr
3285 WHERE pplr.page_id = l_page_id;
3286
3287 CURSOR get_project_manager
3288 IS
3289 select RESOURCE_SOURCE_ID
3290 from pa_project_parties where project_id = p_object_id;
3291
3292 CURSOR get_max_report_end_date
3293 IS SELECT MAX(report_end_date)
3294 FROM pa_progress_report_vers
3295 WHERE object_id = p_object_id
3296 AND object_type = p_object_type
3297 AND report_Type_id = p_report_Type_id
3298 ;
3299
3300 CURSOR get_person_id
3301 IS
3302 select usr.employee_id
3303 from
3304 fnd_user usr
3305 WHERE
3306 usr.user_id = fnd_global.user_id
3307 and trunc(sysdate) between USR.START_DATE and nvl(USR.END_DATE, sysdate+1);
3308
3309 /*CURSOR get_resource_id
3310 IS
3311 select resource_id
3312 from pa_project_parties_v
3313 where user_id = fnd_global.user_id;*/
3314
3315 l_mgr_id number;
3316 l_overview VARCHAR2(240);
3317 l_person_id NUMBER;
3318 l_report_cycle_flag VARCHAR2(1) := 'Y'; -- flag to determine if there is a report cycle
3319 l_call_setup VARCHAR2(1);
3320
3321 BEGIN
3322 -- Initialize the Error Stack
3323 PA_DEBUG.init_err_stack('PA_PROGRESS_REPORT_PVT.create_next_progress_report');
3324
3325 -- Initialize the return status to success
3326 x_return_status := FND_API.G_RET_STS_SUCCESS;
3327
3328 -- Issue API savepoint if the transaction is to be committed
3329
3330 SAVEPOINT create_next_progress_report;
3331
3332
3333 -- check mandatory version_id
3334 IF (p_object_id IS NULL) then
3335 PA_UTILS.Add_Message( p_app_short_name => 'PA'
3336 ,p_msg_name => 'PA_OBJECT_ID_INV');
3337 x_return_status := FND_API.G_RET_STS_ERROR;
3338 END IF;
3339 l_page_id := -99;
3340 l_call_setup := 'N';
3341
3342 OPEN get_page_id_type;
3343 FETCH get_page_id_type INTO l_page_id, l_page_type, l_reporting_cycle_id,
3344 l_report_offset_days, l_effective_from_date;
3345
3346 IF get_page_id_type%notfound THEN
3347 l_page_id := pa_progress_report_utils.get_object_page_id(p_page_type_code => 'PPR',
3348 p_object_Type => 'PA_PROJECTS',
3349 p_object_id => p_object_id,
3350 p_report_Type_id => p_report_Type_id);
3351 l_page_type := 'PPR';
3352 l_call_setup := 'Y';
3353 l_effective_from_date := trunc(sysdate);
3354 End if;
3355
3356 if (l_page_id = -99) then
3357 PA_UTILS.Add_Message( p_app_short_name => 'PA'
3358 ,p_msg_name => 'PA_NO_PRJ_REP_TEMPLATE');
3359 x_return_status := FND_API.G_RET_STS_ERROR;
3360
3361 END IF;
3362
3363 CLOSE get_page_id_type;
3364
3365 IF (l_call_setup = 'Y' and x_return_status = FND_API.g_ret_sts_success) then
3366 ---- call setup api
3367 define_progress_report_setup
3368 (
3369 p_validate_only => FND_API.g_false,
3370 p_init_msg_list => FND_API.g_false,
3371 p_object_id => p_object_id,
3372 p_object_type => p_object_type,
3373 p_page_type_code => l_page_type,
3374 p_page_id => l_page_id,
3375 p_approval_required => 'N',
3376 p_report_Type_id => p_report_Type_id,
3377 p_effective_from => l_effective_from_date,
3378 x_return_status => x_return_status,
3379 x_msg_count => x_msg_count,
3380 x_msg_data => x_msg_data
3381 );
3382
3383 END IF;
3384
3385 IF (p_validate_only <>FND_API.g_true AND x_return_status = FND_API.g_ret_sts_success) then
3386
3387 -- create an entry in pa_progress_report_vers table
3388
3389 --debug_msg('before get dates' || To_char(l_reporting_cycle_id));
3390
3391 IF l_reporting_cycle_id IS NOT NULL then
3392 l_report_cycle_flag := 'Y';
3393 pa_progress_report_utils.get_report_start_end_dates
3394 (
3395 p_object_type,
3396 p_object_id,
3397 p_report_type_id,
3398 l_reporting_cycle_id,
3399 l_report_offset_days,
3400
3401 'N',
3402 l_effective_from_date,
3403 l_report_start_date,
3404 l_report_end_date
3405
3406 -- x_return_status ,
3407 --x_msg_count ,
3408 --x_msg_data
3409 );
3410
3411 --debug_msg('before get dates end');
3412
3413 IF (Trunc(l_report_end_date) < Trunc(l_report_start_date)) THEN
3414 PA_UTILS.Add_Message( p_app_short_name => 'PA'
3415 ,p_msg_name => 'PA_REPORT_END_DATE_INV');
3416 x_return_status := FND_API.G_RET_STS_ERROR;
3417 return;
3418 END IF;
3419 ELSE
3420 l_report_cycle_flag := 'N';
3421 -- not report cycle ID
3422 l_report_start_date:= Trunc(Sysdate);
3423 l_report_end_date:= Trunc(Sysdate);
3424 END IF;
3425
3426
3427 IF (p_validate_only <>FND_API.g_true AND x_return_status = FND_API.g_ret_sts_success) THEN
3428
3429
3430 --debug_msg('*********before insert: reported by ' || To_char(fnd_global.user_id));
3431
3432 /* Get the Reported by Person Id
3433
3434 OPEN get_person_id;
3435 FETCH get_person_id INTO l_person_id;
3436 CLOSE get_person_id; */
3437
3438 ------OPEN get_resource_id;
3439 ------FETCH get_resource_id INTO l_person_id;
3440 ------CLOSE get_resource_id;
3441 l_person_id := pa_resource_utils.get_resource_id(p_user_id => FND_GLOBAL.user_id);
3442
3443 --debug_msg ('l_person_id' || To_char(l_person_id));
3444 pa_progress_report_pkg.insert_PROGRESS_REPORT_ver_row
3445 (
3446 p_object_id,
3447 p_object_type,
3448 l_page_id,
3449 l_page_type,
3450 'PROGRESS_REPORT_WORKING',
3451
3452 Trunc(l_report_start_date) ,
3453 Trunc(l_report_end_date) ,
3454
3455 --fnd_global.user_id ,
3456 l_person_id,
3457 'PROGRESS_STAT_ON_TRACK',
3458 --'PROGRESS_STAT_NOT_STARTED',
3459 l_overview,
3460 'N', --- current_flag
3461 NULL,
3462 NULL,
3463 NULL,
3464
3465 p_report_Type_id,
3466 X_version_id,
3467 x_return_status,
3468 x_msg_count,
3469 x_msg_data
3470 );
3471 END IF;
3472
3473
3474 --debug_msg('before get_last_published_version_id');
3475 OPEN get_last_published_version_id;
3476
3477 FETCH get_last_published_version_id INTO l_last_published_version_id;
3478
3479
3480 IF (get_last_published_version_id%notfound) THEN
3481 CLOSE get_last_published_version_id;
3482
3483
3484 -- if it is the first progress report
3485
3486 --debug_msg ('no published report');
3487
3488 -- if there is existing published report
3489 -- copy it, create entries in pa_progress_report_vals table
3490 /* No need to create an empty region rows if there is no prior publised
3491 Report - msundare */
3492 /******************
3493 FOR obj_page_value_rec IN get_all_regions_from_template LOOP
3494
3495 --debug_msg ('no published report' || obj_page_value_rec.view_region_code);
3496
3497 -- debug_msg('insert val' || obj_page_value_rec.view_region_code);
3498 pa_progress_report_pkg.insert_progress_report_val_row
3499 (
3500 x_version_id,
3501 obj_page_value_rec.region_source_type,
3502 obj_page_value_rec.region_source_code,
3503 1,
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 NULL,
3524
3525 NULL, -- uds_attribute_category
3526 NULL, -- uds_attribute1
3527 NULL, -- uds_attribute2
3528 NULL, -- uds_attribute3
3529 NULL, -- uds_attribute4
3530 NULL, -- uds_attribute5
3531 NULL, -- uds_attribute6
3532 NULL, -- uds_attribute7
3533 NULL, -- uds_attribute8
3534 NULL, -- uds_attribute9
3535 NULL, -- uds_attribute10
3536 NULL, -- uds_attribute11
3537 NULL, -- uds_attribute12
3538 NULL, -- uds_attribute13
3539 NULL, -- uds_attribute14
3540 NULL, -- uds_attribute15
3541 NULL, -- uds_attribute16
3542 NULL, -- uds_attribute17
3543 NULL, -- uds_attribute18
3544 NULL, -- uds_attribute19
3545 NULL, -- uds_attribute20
3546 x_return_status ,
3547 x_msg_count ,
3548 x_msg_data
3549 );
3550 END LOOP;
3551 *************************/
3552 null;
3553 ELSE
3554 CLOSE get_last_published_version_id;
3555
3556 -- update the overview field
3557
3558 OPEN get_publish_overview;
3559 FETCH get_publish_overview INTO l_overview, l_progress_status_code, l_report_start_date;
3560
3561 IF (get_publish_overview%found) THEN
3562
3563 CLOSE get_publish_overview;
3564
3565 -- if there is report cycle ID, report_start and end date are
3566 -- already set, we do not need to change it
3567 -- set report_start_date to null, so we are not updating the
3568 -- vers table
3569
3570 --IF l_report_cycle_flag = 'Y' THEN
3571 -- l_report_start_date := NULL;
3572 -- ELSE
3573
3574 -- OPEN get_max_report_end_date;
3575 -- FETCH get_max_report_end_date INTO l_report_start_date;
3576 -- IF get_max_report_end_date%found THEN
3577 -- l_report_start_date := l_report_start_date+1;
3578 -- ELSE
3579 -- l_report_start_date := sysdate+1;
3580 -- END IF;
3581 -- CLOSE get_max_report_end_date;
3582
3583
3584 --END IF;
3585
3586 --debug_msg ('copying overview ' || l_overview);
3587 pa_progress_report_pkg.UPDATE_PROGRESS_REPORT_VER_ROW (
3588 x_version_id,
3589 null,
3590 NULL,
3591 NULL,
3592 NULL,
3593 NULL,
3594
3595 NULL,
3596 NULL,
3597 NULL,
3598 l_PROGRESS_STATUS_CODE,
3599 l_overview,
3600
3601 'N',
3602 NULL,
3603 NULL,
3604 NULL,
3605
3606 NULL,
3607 NULL,
3608 p_report_Type_id,
3609 x_return_status,
3610 x_msg_count ,
3611 x_msg_data
3612 ) ;
3613
3614 ELSE
3615 CLOSE get_publish_overview;
3616 END IF;
3617
3618
3619 IF x_return_status = FND_API.g_ret_sts_success then
3620 --debug_msg ('published report' || To_char(l_last_published_version_id));
3621 -- if there is existing published report
3622 -- copy it, create entries in pa_progress_report_vals table
3623 FOR obj_page_value_rec IN get_all_regions LOOP
3624
3625 --debug_msg ('published report' || obj_page_value_rec.region_code);
3626 pa_progress_report_pkg.insert_progress_report_val_row
3627 (
3628 x_version_id,
3629 obj_page_value_rec.region_source_type,
3630 obj_page_value_rec.region_code,
3631 obj_page_value_rec.record_sequence,
3632 obj_page_value_rec.ATTRIBUTE1 ,
3633 obj_page_value_rec.ATTRIBUTE2 ,
3634 obj_page_value_rec.ATTRIBUTE3 ,
3635 obj_page_value_rec.ATTRIBUTE4 ,
3636 obj_page_value_rec.ATTRIBUTE5 ,
3637 obj_page_value_rec.ATTRIBUTE6 ,
3638 obj_page_value_rec.ATTRIBUTE7 ,
3639 obj_page_value_rec.ATTRIBUTE8 ,
3640 obj_page_value_rec.ATTRIBUTE9 ,
3641 obj_page_value_rec.ATTRIBUTE10 ,
3642 obj_page_value_rec.ATTRIBUTE11 ,
3643 obj_page_value_rec.ATTRIBUTE12 ,
3644 obj_page_value_rec.ATTRIBUTE13 ,
3645 obj_page_value_rec.ATTRIBUTE14 ,
3646 obj_page_value_rec.ATTRIBUTE15 ,
3647 obj_page_value_rec.ATTRIBUTE16 ,
3648 obj_page_value_rec.ATTRIBUTE17 ,
3649 obj_page_value_rec.ATTRIBUTE18 ,
3650 obj_page_value_rec.ATTRIBUTE19 ,
3651 obj_page_value_rec.ATTRIBUTE20 ,
3652 obj_page_value_rec.UDS_ATTRIBUTE_CATEGORY ,
3653 obj_page_value_rec.UDS_ATTRIBUTE1 ,
3654 obj_page_value_rec.UDS_ATTRIBUTE2 ,
3655 obj_page_value_rec.UDS_ATTRIBUTE3 ,
3656 obj_page_value_rec.UDS_ATTRIBUTE4 ,
3657 obj_page_value_rec.UDS_ATTRIBUTE5 ,
3658 obj_page_value_rec.UDS_ATTRIBUTE6 ,
3659 obj_page_value_rec.UDS_ATTRIBUTE7 ,
3660 obj_page_value_rec.UDS_ATTRIBUTE8 ,
3661 obj_page_value_rec.UDS_ATTRIBUTE9 ,
3662 obj_page_value_rec.UDS_ATTRIBUTE10 ,
3663 obj_page_value_rec.UDS_ATTRIBUTE11 ,
3664 obj_page_value_rec.UDS_ATTRIBUTE12 ,
3665 obj_page_value_rec.UDS_ATTRIBUTE13 ,
3666 obj_page_value_rec.UDS_ATTRIBUTE14 ,
3667 obj_page_value_rec.UDS_ATTRIBUTE15 ,
3668 obj_page_value_rec.UDS_ATTRIBUTE16 ,
3669 obj_page_value_rec.UDS_ATTRIBUTE17 ,
3670 obj_page_value_rec.UDS_ATTRIBUTE18 ,
3671 obj_page_value_rec.UDS_ATTRIBUTE19 ,
3672 obj_page_value_rec.UDS_ATTRIBUTE20 ,
3673 x_return_status ,
3674 x_msg_count ,
3675 x_msg_data
3676 );
3677 END LOOP;
3678 END IF;
3679
3680 END IF;
3681 END IF;
3682
3683 --debug_msg('***end');
3684
3685 -- Commit if the flag is set and there is no error
3686 IF (p_commit = FND_API.G_TRUE AND x_return_status = FND_API.g_ret_sts_success )THEN
3687 COMMIT;
3688 END IF;
3689
3690
3691 IF (x_return_status <> FND_API.g_ret_sts_success )THEN
3692 ROLLBACK TO create_next_progress_report;
3693 END IF;
3694
3695
3696 -- Reset the error stack when returning to the calling program
3697 PA_DEBUG.Reset_Err_Stack;
3698
3699
3700 EXCEPTION
3701 WHEN OTHERS THEN
3702 IF p_commit = FND_API.G_TRUE THEN
3703 ROLLBACK TO create_next_progress_report;
3704 END IF;
3705 --
3706 -- Set the exception Message and the stack
3707 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_PROGRESS_REPORT_PVT.create_next_progress_report'
3708 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
3709 --
3710 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3711 RAISE; -- This is optional depending on the needs
3712
3713
3714 END create_report;
3715
3716 PROCEDURE update_project_perccomplete
3717 (
3718 p_version_id NUMBER,
3719
3720 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
3721 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
3722 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3723 ) IS
3724
3725 l_percent_complete number;
3726 l_sch_start_date DATE;
3727 l_sch_end_date DATE;
3728 l_est_start_date DATE;
3729 l_est_end_date DATE;
3730 l_act_start_date DATE;
3731 l_act_end_date DATE;
3732
3733 l_project_id number;
3734 l_task_id number;
3735 l_object_id number;
3736 l_object_type VARCHAR2(30);
3737 l_asof_date DATE;
3738
3739
3740
3741 CURSOR get_percent_complete IS
3742 SELECT attribute7 FROM pa_progress_report_vals
3743 WHERE version_id = p_version_id
3744 AND region_code = 'PA_PROGRESS_PROJECT_DATES';
3745
3746 CURSOR get_object_info IS
3747 SELECT object_id, object_type, report_end_date
3748 FROM pa_progress_report_vers
3749 WHERE version_id = p_version_id;
3750
3751
3752 CURSOR get_project_id IS
3753 SELECT project_id FROM pa_tasks
3754 WHERE task_id = l_task_id;
3755
3756 CURSOR get_dates IS
3757 SELECT scheduled_start_date, scheduled_finish_date,
3758 start_date,completion_date,actual_start_date
3759 ,actual_finish_date
3760 FROM pa_projects_all
3761 WHERE project_id = l_project_id;
3762
3763 BEGIN
3764
3765 -- Initialize the Error Stack
3766 PA_DEBUG.init_err_stack('PA_PROGRESS_REPORT_PVT.update_project_perccomplete');
3767
3768 -- Initialize the return status to success
3769 x_return_status := FND_API.G_RET_STS_SUCCESS;
3770
3771 SAVEPOINT update_project_perccomplete;
3772
3773 OPEN get_object_info;
3774
3775 FETCH get_object_info INTO l_object_id, l_object_type, l_asof_date;
3776
3777 IF get_object_info%notfound THEN
3778 PA_UTILS.Add_Message( p_app_short_name => 'PA'
3779 ,p_msg_name => 'PA_NO_PRJ_REP');
3780 x_return_status := FND_API.G_RET_STS_ERROR;
3781 END IF;
3782
3783 CLOSE get_object_info;
3784
3785 IF l_object_type = 'PA_PROJECTS' THEN
3786 l_project_id := l_object_id;
3787 l_task_id := 0;
3788 ELSE
3789 l_task_id := l_object_id;
3790
3791 OPEN get_project_id;
3792 fetch get_project_id INTO l_project_id;
3793 CLOSE get_project_id;
3794 END IF;
3795
3796 --debug_msg ('before get_percent_complete');
3797
3798
3799 OPEN get_percent_complete;
3800 FETCH get_percent_complete INTO l_percent_complete;
3801
3802 --debug_msg ('before get_percent_complete' || To_char(l_percent_complete));
3803
3804 IF get_percent_complete%notfound THEN
3805 -- PA_UTILS.Add_Message( p_app_short_name => 'PA'
3806 -- ,p_msg_name => 'PA_PR_PERCENT_COMPLETE_INV');
3807 -- x_return_status := FND_API.G_RET_STS_ERROR;
3808 NULL;
3809 -- todo when perc complete is null, do we update?
3810
3811 ELSE
3812 -- todo
3813
3814 --debug_msg ('before get_percent_complete 2' );
3815 pa_percent_complete_pkg.insert_row
3816 (
3817 l_project_id,
3818 l_task_id,
3819 l_percent_complete,
3820 l_asof_date,
3821 NULL,
3822 Sysdate,
3823 fnd_global.user_id,
3824 Sysdate,
3825 fnd_global.user_id,
3826 fnd_global.user_id,
3827 x_return_status,
3828 x_msg_data
3829 );
3830
3831 --debug_msg('x_return_status' || x_return_status);
3832 -- debug_msg('x_msg_data' || x_msg_data);
3833 NULL;
3834
3835 END IF;
3836
3837 CLOSE get_percent_complete;
3838
3839 IF (x_return_status <> FND_API.g_ret_sts_success) THEN
3840
3841 ROLLBACK TO update_project_perccomplete;
3842 RETURN;
3843 END IF;
3844
3845 -- we need to update the dates from PA_PROJECTS_ALL table
3846 OPEN get_dates;
3847 fetch get_dates INTO l_sch_start_date, l_sch_end_date,
3848 l_est_start_date, l_est_end_date, l_act_start_date,
3849 l_act_end_date;
3850 IF get_dates%notfound THEN
3851 CLOSE get_dates;
3852 PA_UTILS.Add_Message( p_app_short_name => 'PA'
3853 ,p_msg_name => 'PA_PR_DATES_INV');
3854 x_return_status := FND_API.G_RET_STS_ERROR;
3855
3856 ROLLBACK TO update_project_perccomplete;
3857 PA_DEBUG.Reset_Err_Stack;
3858 RETURN;
3859 END IF;
3860 CLOSE get_dates;
3861
3862
3863 IF l_object_type = 'PA_PROJECTS' THEN
3864
3865 -- UPDATE pa_progress_report_vals
3866 -- SET
3867 -- attribute1= To_char(l_sch_start_date, 'YYYY-MM-DD')
3868 -- ,attribute2= To_char(l_sch_end_date, 'YYYY-MM-DD')
3869 -- ,attribute3 = To_char(l_est_start_date, 'YYYY-MM-DD')
3870 -- ,attribute4= To_char(l_est_end_date, 'YYYY-MM-DD')
3871 -- ,attribute5= To_char(l_act_start_date, 'YYYY-MM-DD')
3872 -- ,attribute6= To_char(l_act_end_date, 'YYYY-MM-DD')
3873 -- WHERE version_id = p_version_id
3874 -- AND region_code = 'PA_PROGRESS_PROJECT_DATES';
3875
3876 NULL;
3877 END IF;
3878
3879 IF (x_return_status <> FND_API.g_ret_sts_success) THEN
3880
3881 ROLLBACK TO update_project_perccomplete;
3882 END IF;
3883
3884 EXCEPTION
3885 WHEN OTHERS THEN
3886 ROLLBACK TO update_project_perccomplete;
3887
3888 --
3889 -- Set the excetption Message and the stack
3890 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_Progress_Report_PVT.update_project_perccomplete'
3891 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
3892 --
3893 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3894 RAISE; -- This is optional depending on the needs
3895 END update_project_perccomplete;
3896
3897 /*
3898 Bug 3684164. This API is called when the user updates a status
3899 report page layout. If any sections have been deleted, this API
3900 would take care of deleting the data from the working and rejected
3901 status report versions using this page layout.
3902 */
3903 PROCEDURE delete_version_data
3904 (
3905 p_page_id IN pa_page_layouts.page_id%TYPE
3906 ,p_region_source_type_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE
3907 ,p_region_code_tbl IN SYSTEM.PA_VARCHAR2_2000_TBL_TYPE
3908 ,p_region_source_code_tbl IN SYSTEM.PA_VARCHAR2_2000_TBL_TYPE
3909 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3910 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3911 ,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
3912 AS
3913
3914 l_msg_count NUMBER := 0;
3915 l_data VARCHAR2(2000);
3916 l_msg_data VARCHAR2(2000);
3917 l_return_status VARCHAR2(1);
3918 l_debug_mode VARCHAR2(30);
3919 l_msg_index_out NUMBER;
3920 l_debug_level NUMBER;
3921
3922 /*
3923 This cursor fetches all non mandatory regions selected currently for this page
3924 Currently data can be associated only for the regions of style STD,
3925 STD_CUST and DFF. So this cursor will fetch only these regions.
3926 */
3927 cursor c_page_layout_regions(c_page_id pa_page_layouts.page_id%TYPE)
3928 is
3929 select
3930 layout.region_source_type,
3931 layout.view_region_code,
3932 layout.region_source_code
3933 from pa_page_layout_regions layout, pa_page_type_regions type
3934 where layout.page_id = c_page_id
3935 and layout.region_source_type in ('STD','STD_CUST','DFF')
3936 and type.page_type_code = 'PPR'
3937 and type.region_source_type = layout.region_source_type
3938 and type.region_source_code = decode(layout.region_source_type,'STD_CUST',layout.view_region_code,layout.region_source_code)
3939 and nvl(layout.region_style, 'N') <> 'LINK'
3940 and type.mandatory_flag = 'N';
3941
3942 Type region_source_type_tbl_typ is table of pa_page_layout_regions.region_source_type%TYPE index by binary_integer;
3943 Type view_region_code_tbl_typ is table of pa_page_layout_regions.view_region_code%TYPE index by binary_integer;
3944 Type region_source_code_tbl_typ is table of pa_page_layout_regions.region_source_code%TYPE index by binary_integer;
3945
3946 l_region_source_type_tbl region_source_type_tbl_typ;
3947 l_view_region_code_tbl view_region_code_tbl_typ;
3948 l_region_source_code_tbl region_source_code_tbl_typ;
3949
3950 l_found boolean;
3951 j number;
3952 l_temp_region_src_code pa_page_layout_regions.region_source_code%TYPE;
3953 l_module_name varchar2(100) := 'PA_PROGRESS_REPORT_PVT';
3954
3955 BEGIN
3956 x_msg_count := 0;
3957 x_return_status := FND_API.G_RET_STS_SUCCESS;
3958 pa_debug.set_err_stack('PA_PROGRESS_REPORT_PVT.delete_version_data');
3959 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
3960 fnd_profile.get('AFLOG_LEVEL',l_debug_level);
3961 l_debug_mode := NVL(l_debug_mode, 'Y');
3962 pa_debug.set_process('PLSQL','LOG',l_debug_mode);
3963
3964 if nvl(l_debug_mode,'N') = 'N' then
3965 --if debug is not enabled, set the level to a higher level
3966 l_debug_level := 10;
3967 end if;
3968
3969 open c_page_layout_regions(p_page_id);
3970 fetch c_page_layout_regions
3971 bulk collect into l_region_source_type_tbl, l_view_region_code_tbl, l_region_source_code_tbl;
3972 close c_page_layout_regions;
3973
3974 if (l_debug_level <= 3) then
3975 pa_debug.g_err_stage := 'number of records fetched :'||l_region_source_type_tbl.count;
3976 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
3977 end if;
3978 --If no records exist for the page layout do nothing and return.
3979 if(nvl(l_region_source_type_tbl.count,0) = 0) then
3980 return;
3981 end if;
3982 --Loop through the fetched records. If any of the record is not
3983 --available in the passed in record, delete the corresponding records
3984 --from the status report versions data table.
3985 for i in 1..l_region_source_type_tbl.count loop
3986 l_found := false;
3987 j := 1;
3988 if (l_debug_level <= 3) then
3989 pa_debug.g_err_stage := 'fetched source type:'||l_region_source_type_tbl(i);
3990 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
3991 pa_debug.g_err_stage := 'fetched region code:'||l_view_region_code_tbl(i);
3992 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
3993 pa_debug.g_err_stage := 'fetched region source code:'||l_region_source_code_tbl(i);
3994 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
3995 end if;
3996 while l_found = false and j <= p_region_source_type_tbl.count loop
3997 if (l_debug_level <= 3) then
3998 pa_debug.g_err_stage := 'passed source type:'||p_region_source_type_tbl(j);
3999 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
4000 pa_debug.g_err_stage := 'passed region code:'||p_region_code_tbl(j);
4001 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
4002 pa_debug.g_err_stage := 'passed region source code:'||p_region_source_code_tbl(j);
4003 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
4004 end if;
4005 if p_region_source_type_tbl(j) = 'STD_CUST' then
4006 l_temp_region_src_code := p_region_code_tbl(j) || ':' || p_region_source_code_tbl(j);
4007 else
4008 l_temp_region_src_code := p_region_source_code_tbl(j);
4009 end if;
4010
4011 if( l_region_source_type_tbl(i) = p_region_source_type_tbl(j) and
4012 l_view_region_code_tbl(i) = p_region_code_tbl(j) and
4013 l_region_source_code_tbl(i) = l_temp_region_src_code
4014 ) then
4015 if (l_debug_level <= 3) then
4016 pa_debug.g_err_stage := 'found the above region';
4017 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
4018 end if;
4019 l_found := true;
4020 end if;
4021 j := j + 1;
4022 end loop;
4023
4024 --If the above region was not found, then we need to delete any usage of this region of this page
4025 --in the status reports. For STD_CUST sections the region code is stored as <_oracle_apps..._viewid>
4026 if l_found = false then
4027 l_temp_region_src_code := null;
4028 if l_region_source_type_tbl(i) = 'STD_CUST' then
4029 l_temp_region_src_code := replace(l_region_source_code_tbl(i),'/','_');
4030 l_temp_region_src_code := replace(l_temp_region_src_code,':','_');
4031 else
4032 l_temp_region_src_code := l_region_source_code_tbl(i);
4033 end if;
4034
4035 if (l_debug_level <= 3) then
4036 pa_debug.g_err_stage := 'l_temp_region_src_code :'||l_temp_region_src_code;
4037 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
4038 end if;
4039
4040 delete from pa_progress_report_vals
4041 where region_code = l_temp_region_src_code
4042 and region_source_type = l_region_source_type_tbl(i)
4043 and version_id in
4044 (select version_id
4045 from pa_progress_report_vers
4046 where page_id = p_page_id
4047 and report_status_code in ('PROGRESS_REPORT_WORKING','PROGRESS_REPORT_REJECTED'));
4048 end if;
4049 end loop;
4050
4051 pa_debug.reset_err_stack;
4052 EXCEPTION
4053 WHEN others THEN
4054
4055 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4056 x_msg_count := 1;
4057 x_msg_data := SQLERRM;
4058 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_PROGRESS_REPORT_PVT'
4059 ,p_procedure_name => 'delete_version_data');
4060 pa_debug.g_err_stage:= 'Unexpected Error'||SQLERRM;
4061 pa_debug.reset_err_stack;
4062 RAISE;
4063 END delete_version_data;
4064
4065 END PA_PROGRESS_REPORT_PVT;
4066