[Home] [Help]
PACKAGE BODY: APPS.PA_PROGRESS_REPORT_PKG
Source
1 PACKAGE BODY PA_PROGRESS_REPORT_PKG AS
2 --$Header: PAPRRPHB.pls 120.1 2005/08/19 16:44:58 mwasowic noship $
3
4 procedure INSERT_PROGRESS_REPORT_VER_ROW (
5
6 P_OBJECT_ID in NUMBER,
7 P_OBJECT_TYPE in VARCHAR2,
8 P_PAGE_ID in NUMBER,
9 P_PAGE_TYPE in VARCHAR2,
10 P_PAGE_STATUS in VARCHAR2,
11
12 p_report_start_date IN DATE,
13 p_report_end_date IN DATE,
14 p_reported_by in NUMBER,
15 p_progress_status in VARCHAR2,
16 p_overview in VARCHAR2,
17 p_current_flag in VARCHAR2,
18 p_published_date IN DATE,
19 p_comments in VARCHAR2,
20 p_canceled_date IN DATE,
21 p_report_type_id IN NUMBER,
22 X_VERSION_ID out NOCOPY NUMBER, --File.Sql.39 bug 4440895
23 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
24 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
25 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
26 ) is
27
28 l_version_id NUMBER;
29 l_rowid ROWID;
30
31 cursor C is select ROWID from PA_PROGRESS_REPORT_VERS
32 where VERSION_ID = l_VERSION_ID
33 ;
34
35 BEGIN
36
37 x_return_status := FND_API.G_RET_STS_SUCCESS;
38
39
40 --SELECT pa_object_page_versions_s.NEXTVAL
41 --INTO l_version_id
42 -- FROM dual;
43
44
45 insert into PA_PROGRESS_REPORT_VERS (
46 OBJECT_ID,
47 OBJECT_TYPE,
48 PAGE_ID,
49 VERSION_ID,
50 PAGE_TYPE_CODE,
51 REPORT_STATUS_CODE,
52
53 report_start_date,
54 report_end_date,
55 reported_by,
56 progress_status_code,
57 overview,
58 current_flag,
59 published_date,
60 comments,
61 canceled_date,
62 report_Type_id,
63
64
65 RECORD_VERSION_NUMBER,
66 summary_VERSION_NUMBER,
67 LAST_UPDATED_BY,
68 CREATED_BY,
69 CREATION_DATE,
70 LAST_UPDATE_DATE,
71 LAST_UPDATE_LOGIN
72 ) VALUES (
73 P_OBJECT_ID,
74 P_OBJECT_TYPE,
75 P_PAGE_ID,
76 pa_progress_report_vers_s.NEXTVAL,
77 P_PAGE_TYPE,
78 P_PAGE_STATUS,
79
80 p_report_start_date,
81 p_report_end_date,
82 p_reported_by,
83 p_progress_status,
84 p_overview,
85 p_current_flag,
86 p_published_date,
87 p_comments,
88 p_canceled_date,
89 p_report_Type_id,
90 1,
91 1,
92 fnd_global.user_id,
93 fnd_global.user_id,
94 sysdate,
95 sysdate,
96 fnd_global.user_id) returning version_id INTO l_version_id;
97
98 open c;
99 fetch c into l_ROWID;
100 if (c%notfound) then
101 close c;
102 raise no_data_found;
103 end if;
104 close c;
105
106 x_version_id := l_version_id;
107
108 EXCEPTION
109 WHEN OTHERS THEN -- catch the exceptions here
110 -- Set the current program unit name in the error stack
111 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
112 RAISE;
113
114 end INSERT_PROGRESS_REPORT_VER_ROW;
115
116 procedure UPDATE_PROGRESS_REPORT_VER_ROW (
117 P_VERSION_ID in NUMBER,
118 P_OBJECT_ID in NUMBER,
119 P_OBJECT_TYPE in VARCHAR2,
120 P_PAGE_ID in NUMBER,
121 P_PAGE_TYPE in VARCHAR2,
122 P_PAGE_STATUS in VARCHAR2,
123
124 p_report_start_date IN DATE,
125 p_report_end_date IN DATE,
126 p_reported_by in NUMBER,
127 p_progress_status in VARCHAR2,
128 p_overview in VARCHAR2,
129 p_current_flag in VARCHAR2,
130 p_published_date IN DATE,
131 p_comments in VARCHAR2,
132 p_canceled_date IN DATE,
133
134 P_RECORD_VERSION_NUMBER in NUMBER,
135 P_summary_VERSION_NUMBER in NUMBER,
136 p_report_type_id in NUMBER,
137
138 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
139 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
140 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
141 ) is
142 begin
143 x_return_status := FND_API.G_RET_STS_SUCCESS;
144
145 --debug_msg ('before update ');
146 --debug_msg ('before update ' || To_char (p_version_id));
147
148 update PA_PROGRESS_REPORT_VERS set
149 OBJECT_ID = Nvl(p_object_id, object_id),
150 OBJECT_TYPE = Nvl(p_object_type, object_type),
151 PAGE_ID = Nvl(p_page_id, page_id),
152 PAGE_TYPE_CODE = Nvl(p_page_type, page_type_CODE),
153 REPORT_STATUS_CODE = Nvl(p_page_status, report_status_code),
154 RECORD_VERSION_NUMBER = record_version_number +1,
155 summary_VERSION_NUMBER = summary_version_number +1,
156
157 report_start_date =Nvl(p_report_start_date, report_start_date) ,
158 report_end_date =Nvl(p_report_end_date, report_end_date),
159 reported_by =Nvl(p_reported_by, reported_by),
160 progress_status_code =Nvl(p_progress_status, progress_status_code),
161 overview = decode(p_overview,FND_API.G_MISS_CHAR,null,nvl(p_overview,overview)), -- Bug 3877982
162 -- overview=Nvl(p_overview,overview),
163 current_flag =Nvl(p_current_flag, current_flag),
164 published_date=Nvl(p_published_date, published_date),
165 comments =Nvl(p_comments, comments),
166 canceled_date = Nvl(p_canceled_date, canceled_date),
167 report_Type_id = nvl(p_report_Type_id,report_Type_id),
168
169 LAST_UPDATED_BY = fnd_global.user_id,
170 LAST_UPDATE_DATE = sysdate,
171 LAST_UPDATE_LOGIN = fnd_global.login_id
172 where VERSION_ID = p_version_id
173 AND record_version_number = Nvl(p_record_version_number, record_version_number)
174 AND summary_version_number = Nvl(p_summary_version_number, summary_version_number);
175
176 --debug_msg ('after update ');
177 if (sql%notfound) THEN
178 -- debug_msg ('failed after update ');
179 PA_UTILS.Add_Message ( p_app_short_name => 'PA',p_msg_name => 'PA_XC_RECORD_CHANGED');
180 x_return_status := FND_API.G_RET_STS_ERROR;
181 end if;
182
183 EXCEPTION
184 WHEN OTHERS THEN -- catch the exceptins here
185 -- Set the current program unit name in the error stack
186 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
187 RAISE;
188 end UPDATE_PROGRESS_REPORT_VER_ROW;
189
190
191
192 procedure DELETE_PROGRESS_REPORT_VER_ROW (
193 P_VERSION_ID in NUMBER,
194 P_RECORD_VERSION_NUMBER in NUMBER,
195
196 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
197 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
198 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
199 ) is
200 begin
201
202 x_return_status := FND_API.G_RET_STS_SUCCESS;
203
204 delete from PA_PROGRESS_REPORT_VERS
205 where VERSION_ID = p_version_id AND record_version_number = nvl(p_record_version_number, record_version_number);
206
207 EXCEPTION
208 WHEN OTHERS THEN
209 -- Set the current program unit name in the error stack
210
211 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
212 RAISE;
213
214 end DELETE_PROGRESS_REPORT_VER_ROW;
215
216
217 procedure INSERT_PROGRESS_REPORT_VAL_ROW (
218 P_VERSION_ID in NUMBER,
219 P_REGION_SOURCE_TYPE in VARCHAR2,
220 P_REGION_CODE in VARCHAR2,
221 P_RECORD_SEQUENCE in NUMBER,
222 P_ATTRIBUTE1 in VARCHAR2,
223 P_ATTRIBUTE2 in VARCHAR2,
224 P_ATTRIBUTE3 in VARCHAR2,
225 P_ATTRIBUTE4 in VARCHAR2,
226 P_ATTRIBUTE5 in VARCHAR2,
227 P_ATTRIBUTE6 in VARCHAR2,
228 P_ATTRIBUTE7 in VARCHAR2,
229 P_ATTRIBUTE8 in VARCHAR2,
230 P_ATTRIBUTE9 in VARCHAR2,
231 P_ATTRIBUTE10 in VARCHAR2,
232 P_ATTRIBUTE11 in VARCHAR2,
233 P_ATTRIBUTE12 in VARCHAR2,
234 P_ATTRIBUTE13 in VARCHAR2,
235 P_ATTRIBUTE14 in VARCHAR2,
236 P_ATTRIBUTE15 in VARCHAR2,
237 P_ATTRIBUTE16 in VARCHAR2,
238 P_ATTRIBUTE17 in VARCHAR2,
239 P_ATTRIBUTE18 in VARCHAR2,
240 P_ATTRIBUTE19 in VARCHAR2,
241 P_ATTRIBUTE20 in VARCHAR2,
242 P_UDS_ATTRIBUTE_CATEGORY in VARCHAR2,
243 P_UDS_ATTRIBUTE1 in VARCHAR2,
244 P_UDS_ATTRIBUTE2 in VARCHAR2,
245 P_UDS_ATTRIBUTE3 in VARCHAR2,
246 P_UDS_ATTRIBUTE4 in VARCHAR2,
247 P_UDS_ATTRIBUTE5 in VARCHAR2,
248 P_UDS_ATTRIBUTE6 in VARCHAR2,
249 P_UDS_ATTRIBUTE7 in VARCHAR2,
250 P_UDS_ATTRIBUTE8 in VARCHAR2,
251 P_UDS_ATTRIBUTE9 in VARCHAR2,
252 P_UDS_ATTRIBUTE10 in VARCHAR2,
253 P_UDS_ATTRIBUTE11 in VARCHAR2,
254 P_UDS_ATTRIBUTE12 in VARCHAR2,
255 P_UDS_ATTRIBUTE13 in VARCHAR2,
256 P_UDS_ATTRIBUTE14 in VARCHAR2,
257 P_UDS_ATTRIBUTE15 in VARCHAR2,
258 P_UDS_ATTRIBUTE16 in VARCHAR2,
259 P_UDS_ATTRIBUTE17 in VARCHAR2,
260 P_UDS_ATTRIBUTE18 in VARCHAR2,
261 P_UDS_ATTRIBUTE19 in VARCHAR2,
262 P_UDS_ATTRIBUTE20 in VARCHAR2,
263 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
264 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
265 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
266 ) is
267
268 l_rowid ROWID;
269 l_record_sequence NUMBER;
270
271 cursor C is select ROWID from PA_PROGRESS_REPORT_VALS
272 where VERSION_ID = P_VERSION_ID
273 and REGION_SOURCE_TYPE = P_REGION_SOURCE_TYPE
274 and REGION_CODE = P_REGION_CODE
275 and RECORD_SEQUENCE = L_RECORD_SEQUENCE;
276
277 BEGIN
278
279 x_return_status := FND_API.G_RET_STS_SUCCESS;
280
281
282 insert into PA_PROGRESS_REPORT_VALS (
283 VERSION_ID,
284 REGION_SOURCE_TYPE,
285 REGION_CODE,
286 RECORD_SEQUENCE,
287 RECORD_VERSION_NUMBER,
288 ATTRIBUTE1,
289 ATTRIBUTE2,
290 ATTRIBUTE3,
291 ATTRIBUTE4,
292 ATTRIBUTE5,
293 ATTRIBUTE6,
294 ATTRIBUTE7,
295 ATTRIBUTE8,
296 ATTRIBUTE9,
297 ATTRIBUTE10,
298 ATTRIBUTE11,
299 ATTRIBUTE12,
300 ATTRIBUTE13,
301 ATTRIBUTE14,
302 ATTRIBUTE15,
303 ATTRIBUTE16,
304 ATTRIBUTE17,
305 ATTRIBUTE18,
306 ATTRIBUTE19,
307 ATTRIBUTE20,
308 UDS_ATTRIBUTE_CATEGORY,
309 UDS_ATTRIBUTE1,
310 UDS_ATTRIBUTE2,
311 UDS_ATTRIBUTE3,
312 UDS_ATTRIBUTE4,
313 UDS_ATTRIBUTE5,
314 UDS_ATTRIBUTE6,
315 UDS_ATTRIBUTE7,
316 UDS_ATTRIBUTE8,
317 UDS_ATTRIBUTE9,
318 UDS_ATTRIBUTE10,
319 UDS_ATTRIBUTE11,
320 UDS_ATTRIBUTE12,
321 UDS_ATTRIBUTE13,
322 UDS_ATTRIBUTE14,
323 UDS_ATTRIBUTE15,
324 UDS_ATTRIBUTE16,
325 UDS_ATTRIBUTE17,
326 UDS_ATTRIBUTE18,
327 UDS_ATTRIBUTE19,
328 UDS_ATTRIBUTE20,
329 LAST_UPDATED_BY,
330 CREATED_BY,
331 CREATION_DATE,
332 LAST_UPDATE_DATE,
333 last_update_login
334 ) VALUES(
335 P_VERSION_ID,
336 P_REGION_SOURCE_TYPE,
337 P_REGION_CODE,
338 pa_progress_report_vers_s.NEXTVAL,
339 1,
340 P_ATTRIBUTE1,
341 P_ATTRIBUTE2,
342 P_ATTRIBUTE3,
343 P_ATTRIBUTE4,
344 P_ATTRIBUTE5,
345 P_ATTRIBUTE6,
346 P_ATTRIBUTE7,
347 P_ATTRIBUTE8,
348 P_ATTRIBUTE9,
349 P_ATTRIBUTE10,
350 P_ATTRIBUTE11,
351 P_ATTRIBUTE12,
352 P_ATTRIBUTE13,
353 P_ATTRIBUTE14,
354 P_ATTRIBUTE15,
355 P_ATTRIBUTE16,
356 P_ATTRIBUTE17,
357 P_ATTRIBUTE18,
358 P_ATTRIBUTE19,
359 P_ATTRIBUTE20,
360 P_UDS_ATTRIBUTE_CATEGORY,
361 P_UDS_ATTRIBUTE1,
362 P_UDS_ATTRIBUTE2,
363 P_UDS_ATTRIBUTE3,
364 P_UDS_ATTRIBUTE4,
365 P_UDS_ATTRIBUTE5,
366 P_UDS_ATTRIBUTE6,
367 P_UDS_ATTRIBUTE7,
368 P_UDS_ATTRIBUTE8,
369 P_UDS_ATTRIBUTE9,
370 P_UDS_ATTRIBUTE10,
371 P_UDS_ATTRIBUTE11,
372 P_UDS_ATTRIBUTE12,
373 P_UDS_ATTRIBUTE13,
374 P_UDS_ATTRIBUTE14,
375 P_UDS_ATTRIBUTE15,
376 P_UDS_ATTRIBUTE16,
377 P_UDS_ATTRIBUTE17,
378 P_UDS_ATTRIBUTE18,
379 P_UDS_ATTRIBUTE19,
380 P_UDS_ATTRIBUTE20,
381 fnd_global.user_id,
382 fnd_global.user_id,
383 sysdate,
384 sysdate,
385 fnd_global.user_id) returning record_sequence INTO L_RECORD_SEQUENCE;
386
387
388 open c;
389 fetch c into l_ROWID;
390 if (c%notfound) then
391 close c;
392 raise no_data_found;
393 end if;
394 close c;
395
396
397 EXCEPTION
398 WHEN OTHERS THEN -- catch the exceptions here
399 -- Set the current program unit name in the error stack
400 -- PA_Error_Utils.Set_Error_Stack('PA_PROJECT_SUBTEAMS_PKG.Insert_Row');
401 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
402 RAISE;
403
404 end INSERT_PROGRESS_REPORT_VAL_ROW;
405
406 procedure UPDATE_PROGRESS_REPORT_VAL_ROW (
407 P_VERSION_ID in NUMBER,
408 P_REGION_SOURCE_TYPE in VARCHAR2,
409 P_REGION_CODE in VARCHAR2,
410 P_RECORD_SEQUENCE in NUMBER,
411 P_RECORD_VERSION_NUMBER in NUMBER,
412 P_ATTRIBUTE1 in VARCHAR2,
413 P_ATTRIBUTE2 in VARCHAR2,
414 P_ATTRIBUTE3 in VARCHAR2,
415 P_ATTRIBUTE4 in VARCHAR2,
416 P_ATTRIBUTE5 in VARCHAR2,
417 P_ATTRIBUTE6 in VARCHAR2,
418 P_ATTRIBUTE7 in VARCHAR2,
419 P_ATTRIBUTE8 in VARCHAR2,
420 P_ATTRIBUTE9 in VARCHAR2,
421 P_ATTRIBUTE10 in VARCHAR2,
422 P_ATTRIBUTE11 in VARCHAR2,
423 P_ATTRIBUTE12 in VARCHAR2,
424 P_ATTRIBUTE13 in VARCHAR2,
425 P_ATTRIBUTE14 in VARCHAR2,
426 P_ATTRIBUTE15 in VARCHAR2,
427 P_ATTRIBUTE16 in VARCHAR2,
428 P_ATTRIBUTE17 in VARCHAR2,
429 P_ATTRIBUTE18 in VARCHAR2,
430 P_ATTRIBUTE19 in VARCHAR2,
431 P_ATTRIBUTE20 in VARCHAR2,
432 P_UDS_ATTRIBUTE_CATEGORY in VARCHAR2,
433 P_UDS_ATTRIBUTE1 in VARCHAR2,
434 P_UDS_ATTRIBUTE2 in VARCHAR2,
435 P_UDS_ATTRIBUTE3 in VARCHAR2,
436 P_UDS_ATTRIBUTE4 in VARCHAR2,
437 P_UDS_ATTRIBUTE5 in VARCHAR2,
438 P_UDS_ATTRIBUTE6 in VARCHAR2,
439 P_UDS_ATTRIBUTE7 in VARCHAR2,
440 P_UDS_ATTRIBUTE8 in VARCHAR2,
441 P_UDS_ATTRIBUTE9 in VARCHAR2,
442 P_UDS_ATTRIBUTE10 in VARCHAR2,
443 P_UDS_ATTRIBUTE11 in VARCHAR2,
444 P_UDS_ATTRIBUTE12 in VARCHAR2,
445 P_UDS_ATTRIBUTE13 in VARCHAR2,
446 P_UDS_ATTRIBUTE14 in VARCHAR2,
447 P_UDS_ATTRIBUTE15 in VARCHAR2,
448 P_UDS_ATTRIBUTE16 in VARCHAR2,
449 P_UDS_ATTRIBUTE17 in VARCHAR2,
450 P_UDS_ATTRIBUTE18 in VARCHAR2,
451 P_UDS_ATTRIBUTE19 in VARCHAR2,
452 P_UDS_ATTRIBUTE20 in VARCHAR2,
453 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
454 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
455 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
456 ) is
457 begin
458
459 x_return_status := FND_API.G_RET_STS_SUCCESS;
460
461
462 --debug_msg ('***********update attribute 3: ' ||P_ATTRIBUTE3 );
463
464 update PA_PROGRESS_REPORT_VALS set
465 RECORD_VERSION_NUMBER = record_version_number +1,
466
467 ATTRIBUTE1 = P_ATTRIBUTE1,
471 ATTRIBUTE5 = P_ATTRIBUTE5,
468 ATTRIBUTE2 = P_ATTRIBUTE2,
469 ATTRIBUTE3 = P_ATTRIBUTE3,
470 ATTRIBUTE4 = P_ATTRIBUTE4,
472 ATTRIBUTE6 = P_ATTRIBUTE6,
473 ATTRIBUTE7 = P_ATTRIBUTE7,
474 ATTRIBUTE8 = P_ATTRIBUTE8,
475 ATTRIBUTE9 = P_ATTRIBUTE9,
476 ATTRIBUTE10 = P_ATTRIBUTE10,
477 ATTRIBUTE11 = P_ATTRIBUTE11,
478 ATTRIBUTE12 = P_ATTRIBUTE12,
479 ATTRIBUTE13 = P_ATTRIBUTE13,
480 ATTRIBUTE14 = P_ATTRIBUTE14,
481 ATTRIBUTE15 = P_ATTRIBUTE15,
482 ATTRIBUTE16 = P_ATTRIBUTE16,
483 ATTRIBUTE17 = P_ATTRIBUTE17,
484 ATTRIBUTE18 = P_ATTRIBUTE18,
485 ATTRIBUTE19 = P_ATTRIBUTE19,
486 ATTRIBUTE20 = P_ATTRIBUTE20,
487 UDS_ATTRIBUTE_CATEGORY = P_UDS_ATTRIBUTE_CATEGORY,
488 UDS_ATTRIBUTE1 = P_UDS_ATTRIBUTE1,
489 UDS_ATTRIBUTE2 = P_UDS_ATTRIBUTE2,
490 UDS_ATTRIBUTE3 = P_UDS_ATTRIBUTE3,
491 UDS_ATTRIBUTE4 = P_UDS_ATTRIBUTE4,
492 UDS_ATTRIBUTE5 = P_UDS_ATTRIBUTE5,
493 UDS_ATTRIBUTE6 = P_UDS_ATTRIBUTE6,
494 UDS_ATTRIBUTE7 = P_UDS_ATTRIBUTE7,
495 UDS_ATTRIBUTE8 = P_UDS_ATTRIBUTE8,
496 UDS_ATTRIBUTE9 = P_UDS_ATTRIBUTE9,
497 UDS_ATTRIBUTE10 = P_UDS_ATTRIBUTE10,
498 UDS_ATTRIBUTE11 = P_UDS_ATTRIBUTE11,
499 UDS_ATTRIBUTE12 = P_UDS_ATTRIBUTE12,
500 UDS_ATTRIBUTE13 = P_UDS_ATTRIBUTE13,
501 UDS_ATTRIBUTE14 = P_UDS_ATTRIBUTE13,
502 UDS_ATTRIBUTE15 = P_UDS_ATTRIBUTE13,
503 UDS_ATTRIBUTE16 = P_UDS_ATTRIBUTE14,
504 UDS_ATTRIBUTE17 = P_UDS_ATTRIBUTE14,
505 UDS_ATTRIBUTE18 = P_UDS_ATTRIBUTE15,
506 UDS_ATTRIBUTE19 = P_UDS_ATTRIBUTE16,
507 UDS_ATTRIBUTE20 = P_UDS_ATTRIBUTE17,
508 LAST_UPDATED_BY = fnd_global.user_id,
509 LAST_UPDATE_DATE = sysdate,
510 LAST_UPDATE_LOGIN = fnd_global.login_id
511 WHERE VERSION_ID = P_VERSION_ID
512 AND region_source_type = p_region_source_type
513 AND region_code = p_region_code
514 AND record_sequence = p_record_sequence;
515
516
517 if (sql%notfound) THEN
518
519 --debug_msg ('***********update failed');
520 PA_UTILS.Add_Message ( p_app_short_name => 'PA',p_msg_name => 'PA_XC_RECORD_CHANGED');
521 x_return_status := FND_API.G_RET_STS_ERROR;
522 end if;
523
524 EXCEPTION
525 WHEN OTHERS THEN -- catch the exceptins here
526 -- Set the current program unit name in the error stack
527 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
528 RAISE;
529
530 end UPDATE_PROGRESS_REPORT_VAL_ROW;
531
532 procedure DELETE_PROGRESS_REPORT_VAL_ROW (
533 P_VERSION_ID in NUMBER,
534 P_REGION_SOURCE_TYPE in VARCHAR2,
535 P_REGION_CODE in VARCHAR2,
536 P_RECORD_SEQUENCE in NUMBER,
537 P_RECORD_VERSION_NUMBER in NUMBER,
538
539 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
540 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
541 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
542 ) is
543 begin
544
545 x_return_status := FND_API.G_RET_STS_SUCCESS;
546
547 delete from PA_PROGRESS_REPORT_VALS
548 where VERSION_ID = P_VERSION_ID
549 and REGION_SOURCE_TYPE = P_REGION_SOURCE_TYPE
550 and REGION_CODE = P_REGION_CODE
551 and RECORD_SEQUENCE = p_record_sequence
552 AND nvl(p_record_version_number, record_version_number) = record_version_number;
553
554
555 EXCEPTION
556 WHEN OTHERS THEN
557 -- Set the current program unit name in the error stack
558
559 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
560 RAISE;
561
562 end DELETE_PROGRESS_REPORT_VAL_ROW;
563
564 procedure DELETE_PROGRESS_REPORT_VALS (
565 P_VERSION_ID in NUMBER,
566
567 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
568 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
569 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
570 ) is
571 begin
572
573 x_return_status := FND_API.G_RET_STS_SUCCESS;
574
575 delete from PA_PROGRESS_REPORT_VALS
576 where VERSION_ID = p_version_id;
577
578
579
580 EXCEPTION
581 WHEN OTHERS THEN
582 -- Set the current program unit name in the error stack
583
584 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
585 RAISE;
586
587 end DELETE_PROGRESS_REPORT_VALS;
588
589 procedure DELETE_PROGRESS_REPORT_REGION (
590 P_VERSION_ID in NUMBER,
591 p_region_source_type IN VARCHAR2,
592 p_region_code IN VARCHAR2,
593
594 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
595 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
596 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
597 ) is
598 begin
599
600 x_return_status := FND_API.G_RET_STS_SUCCESS;
601
602 delete from PA_PROGRESS_REPORT_VALS
603 where VERSION_ID = p_version_id
604 AND region_source_type = p_region_source_type
605 AND region_code = p_region_code;
606
607
608
609 EXCEPTION
613 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
610 WHEN OTHERS THEN
611 -- Set the current program unit name in the error stack
612
614 RAISE;
615
616 end DELETE_PROGRESS_REPORT_REGION;
617
618 procedure INSERT_OBJECT_PAGE_LAYOUT_ROW (
619
620 P_OBJECT_ID in NUMBER,
621 P_OBJECT_TYPE in VARCHAR2,
622 P_PAGE_ID in NUMBER,
623 P_PAGE_TYPE_CODE in VARCHAR2,
624
625 P_APPROVAL_REQUIRED in VARCHAR2,
626 -- P_AUTO_PUBLISH in VARCHAR2,
627 P_REPORTING_CYCLE_ID in NUMBER,
628 P_REPORTING_OFFSET_DAYS in NUMBER,
629 P_NEXT_REPORTING_DATE in DATE,
630 P_REMINDER_DAYS in NUMBER,
631 P_REMINDER_DAYS_TYPE in VARCHAR2,
632 P_INITIAL_PROGRESS_STATUS in VARCHAR2,
633 P_FINAL_PROGRESS_STATUS in VARCHAR2,
634 P_ROLLUP_PROGRESS_STATUS in VARCHAR2,
635 p_report_type_id IN NUMBER,
636 p_approver_source_id IN NUMBER,
637 p_approver_source_type IN NUMBER,
638 p_effective_from IN DATE,
639 p_effective_to IN DATE,
640 p_function_name IN VARCHAR2,
641 x_object_page_layout_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
642 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
643 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
644 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
645 ) is
646
647 l_version_id NUMBER;
648 l_rowid ROWID;
649 l_layout_id NUMBER;
650
651 cursor C is select ROWID from PA_OBJECT_PAGE_LAYOUTS
652 where object_Page_Layout_id = l_layout_id;
653
654 BEGIN
655
656 x_return_status := FND_API.G_RET_STS_SUCCESS;
657
658 --get the unique task id from the Oracle Sequence
659 SELECT pa_object_page_layouts_s.nextval
660 INTO l_layout_id
661 FROM DUAL;
662
663
664 insert into PA_OBJECT_PAGE_LAYOUTS (
665 OBJECT_ID,
666 OBJECT_TYPE,
667 PAGE_ID,
668 PAGE_TYPE_CODE,
669
670 APPROVAL_REQUIRED ,
671 --AUTO_PUBLISH ,
672 REPORTING_CYCLE_ID ,
673 REPORT_OFFSET_DAYS ,
674 NEXT_REPORTING_DATE ,
675 REMINDER_DAYS ,
676 REMINDER_DAYS_TYPE ,
677 INITIAL_PROGRESS_STATUS,
678 FINAL_PROGRESS_STATUS,
679 ROLLUP_PROGRESS_STATUS,
680
681 RECORD_VERSION_NUMBER,
682 LAST_UPDATED_BY,
683 CREATED_BY,
684 CREATION_DATE,
685 LAST_UPDATE_DATE,
686 LAST_UPDATE_LOGIN,
687 object_page_layout_id,
688 report_type_id,
689 approver_source_id,
690 approver_source_type,
691 effective_from,
692 effective_to,
693 pers_function_name
694
695 ) VALUES (
696 P_OBJECT_ID,
697 P_OBJECT_TYPE,
698 P_PAGE_ID,
699 P_PAGE_TYPE_CODE,
700
701 P_APPROVAL_REQUIRED ,
702 --P_AUTO_PUBLISH ,
703 P_REPORTING_CYCLE_ID ,
704 P_REPORTING_OFFSET_DAYS ,
705 P_NEXT_REPORTING_DATE ,
706 P_REMINDER_DAYS ,
707 P_REMINDER_DAYS_TYPE ,
708 P_INITIAL_PROGRESS_STATUS,
709 P_FINAL_PROGRESS_STATUS,
710 P_ROLLUP_PROGRESS_STATUS,
711
712 1,
713 fnd_global.user_id,
714 fnd_global.user_id,
715 sysdate,
716 sysdate,
717 fnd_global.user_id,
718 l_layout_id,
719 p_report_type_id,
720 p_approver_source_id,
721 p_approver_source_type,
722 p_effective_from,
723 p_effective_to,
724 p_function_name);
725
726
727 open c;
728 fetch c into l_ROWID;
729 if (c%notfound) then
730 close c;
731 raise no_data_found;
732 end if;
733 close c;
734 x_object_page_layout_id := l_layout_id;
735
736 EXCEPTION
737 WHEN OTHERS THEN -- catch the exceptions here
738 -- Set the current program unit name in the error stack
739 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
740 RAISE;
741
742 end INSERT_OBJECT_PAGE_LAYOUT_ROW;
743
744 procedure UPDATE_OBJECT_PAGE_LAYOUT_ROW (
745 P_OBJECT_ID in NUMBER,
746 P_OBJECT_TYPE in VARCHAR2,
747 P_PAGE_ID in NUMBER,
748 P_PAGE_TYPE_CODE in VARCHAR2,
749
750 P_APPROVAL_REQUIRED in VARCHAR2,
751 --P_AUTO_PUBLISH in VARCHAR2,
752 P_REPORTING_CYCLE_ID in NUMBER,
753 P_REPORTING_OFFSET_DAYS in NUMBER,
754 P_NEXT_REPORTING_DATE in DATE,
755 P_REMINDER_DAYS in NUMBER,
756 P_REMINDER_DAYS_TYPE in VARCHAR2,
757 P_INITIAL_PROGRESS_STATUS in VARCHAR2,
758 P_FINAL_PROGRESS_STATUS in VARCHAR2,
759 P_ROLLUP_PROGRESS_STATUS in VARCHAR2,
760
761 p_report_type_id IN NUMBER,
762 p_approver_source_id IN NUMBER,
763 p_approver_source_type IN NUMBER,
764 p_effective_from IN DATE,
765 p_effective_to IN DATE,
766 p_object_page_layout_id IN NUMBER,
767
768 p_record_version_number IN NUMBER,
769 p_function_name IN VARCHAR2,
770
771 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
772 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
773 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
774 ) is
775 begin
776 x_return_status := FND_API.G_RET_STS_SUCCESS;
777
778 update PA_OBJECT_PAGE_LAYOUTS set
779 OBJECT_ID = Nvl(p_object_id, object_id),
780 OBJECT_TYPE = Nvl(p_object_type, object_type),
781 PAGE_ID = Nvl(p_page_id, page_id),
782 PAGE_TYPE_CODE = Nvl(p_page_type_code, page_type_CODE),
783
784 RECORD_VERSION_NUMBER = record_version_number +1,
785
786 approval_required = P_APPROVAL_REQUIRED ,
787 --auto_publish = P_AUTO_PUBLISH ,
788 reporting_cycle_id = P_REPORTING_CYCLE_ID ,
789 report_offset_days = P_REPORTING_OFFSET_DAYS ,
790 next_reporting_date = P_NEXT_REPORTING_DATE ,
791 reminder_days = P_REMINDER_DAYS ,
792 reminder_days_type = P_REMINDER_DAYS_TYPE ,
793 initial_progress_status = P_INITIAL_PROGRESS_STATUS,
794 final_progress_status = P_FINAL_PROGRESS_STATUS,
795 rollup_progress_status = P_ROLLUP_PROGRESS_STATUS,
796
797 report_type_id = p_report_type_id,
798 approver_source_id = p_approver_source_id,
799 approver_source_type = p_approver_source_type,
800 effective_from = p_effective_from,
801 effective_to = p_effective_to,
802 pers_function_name = p_function_name,
803 LAST_UPDATED_BY = fnd_global.user_id,
804 LAST_UPDATE_DATE = sysdate,
805 LAST_UPDATE_LOGIN = fnd_global.login_id
806 where object_page_layout_ID = p_object_page_layout_id
807 AND record_version_number = Nvl(p_record_version_number, record_version_number);
808
809 if (sql%notfound) then
810 PA_UTILS.Add_Message ( p_app_short_name => 'PA',p_msg_name => 'PA_XC_RECORD_CHANGED');
811 x_return_status := FND_API.G_RET_STS_ERROR;
812 end if;
813
814 EXCEPTION
815 WHEN OTHERS THEN -- catch the exceptins here
816 -- Set the current program unit name in the error stack
817 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
818 RAISE;
819 end UPDATE_OBJECT_PAGE_LAYOUT_ROW;
820
821
822
823 procedure DELETE_OBJECT_PAGE_LAYOUTS (
824 P_OBJECT_ID in NUMBER,
825 P_OBJECT_TYPE in VARCHAR2,
826
827 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
828 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
829 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
830 ) is
831 begin
832 x_return_status := FND_API.G_RET_STS_SUCCESS;
833
834 DELETE FROM PA_OBJECT_PAGE_LAYOUTS
835 where object_ID = p_object_id
836 AND object_type = p_object_type;
837
838
839 EXCEPTION
840 WHEN OTHERS THEN -- catch the exceptins here
841 -- Set the current program unit name in the error stack
842 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
843 RAISE;
844 end DELETE_OBJECT_PAGE_LAYOUTS;
845
846 END PA_PROGRESS_REPORT_PKG;