eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
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,
371     P_UDS_ATTRIBUTE11,
368     P_UDS_ATTRIBUTE8,
369     P_UDS_ATTRIBUTE9,
370     P_UDS_ATTRIBUTE10,
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,
468     ATTRIBUTE2 = P_ATTRIBUTE2,
469     ATTRIBUTE3 = P_ATTRIBUTE3,
470     ATTRIBUTE4 = P_ATTRIBUTE4,
471     ATTRIBUTE5 = P_ATTRIBUTE5,
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
529 
526         -- Set the current program unit name in the error stack
527         x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
528         RAISE;
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
610     WHEN OTHERS THEN
611         -- Set the current program unit name in the error stack
612 
613         x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
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
697     P_OBJECT_TYPE,
694 
695   ) VALUES (
696     P_OBJECT_ID,
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;