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.PSP_EFT_REPORT_TEMPLATES_PKG

Source


1 package body PSP_EFT_REPORT_TEMPLATES_PKG as
2  /* $Header: PSPERTEB.pls 115.7 2002/11/18 09:17:09 lveerubh ship $ */
3 procedure INSERT_ROW (
4   X_ROWID in out NOCOPY VARCHAR2,
5   X_TEMPLATE_ID in NUMBER,
6   X_TEMPLATE_NAME in VARCHAR2,
7   X_EFFORT_REPORT_PERIOD_NAME in VARCHAR2,
8   X_BEGIN_DATE in DATE,
9   X_END_DATE in DATE,
10   X_PEOPLE_GROUP_ID in NUMBER,
11   X_PERSON_ID in NUMBER,
12   X_EFFECTIVE_START_DATE in DATE,
13   X_EFFECTIVE_END_DATE in DATE,
14   X_SUPERVISOR_ID in NUMBER,
15   X_GL_CODE_COMBINATION_ID in NUMBER,
16   X_EXPENDITURE_TYPE in VARCHAR2,
17   X_TASK_ID in NUMBER,
18   X_PROJECT_TYPE_CLASS_CODE in VARCHAR2,
19   X_PROJECT_TYPE in VARCHAR2,
20   X_AWARD_TYPE in VARCHAR2,
21   X_FUNDING_SOURCE_ID in NUMBER,
22   X_ELEMENT_TYPE_ID in NUMBER,
23   X_ENABLE_WORKFLOW_FLAG in VARCHAR2,
24   X_USER_TEMPLATE_FLAG in VARCHAR2,
25   X_REPORT_TYPE in VARCHAR2,
26   X_REPORT_INITIATOR_ID in NUMBER,
27   X_INITIATOR_APPS_NAME in VARCHAR2,
28   X_ERROR_DATE_TIME in DATE,
29   X_BUSINESS_GROUP_ID in VARCHAR2,
30   X_SET_OF_BOOKS_ID in VARCHAR2,
31   X_ATTRIBUTE_CATEGORY in VARCHAR2,
32   X_ATTRIBUTE1 in VARCHAR2,
33   X_ATTRIBUTE2 in VARCHAR2,
34   X_ATTRIBUTE3 in VARCHAR2,
35   X_ATTRIBUTE4 in VARCHAR2,
36   X_ATTRIBUTE5 in VARCHAR2,
37   X_ATTRIBUTE6 in VARCHAR2,
38   X_ATTRIBUTE7 in VARCHAR2,
39   X_ATTRIBUTE8 in VARCHAR2,
40   X_ATTRIBUTE9 in VARCHAR2,
41   X_ATTRIBUTE10 in VARCHAR2,
42   X_ATTRIBUTE11 in VARCHAR2,
43   X_ATTRIBUTE12 in VARCHAR2,
44   X_ATTRIBUTE13 in VARCHAR2,
45   X_ATTRIBUTE14 in VARCHAR2,
46   X_ATTRIBUTE15 in VARCHAR2,
47   X_MODE in VARCHAR2 default 'R'
48   ) is
49     cursor C is select ROWID from PSP_EFFORT_REPORT_TEMPLATES
50       where TEMPLATE_ID = X_TEMPLATE_ID;
51     X_LAST_UPDATE_DATE DATE;
52     X_LAST_UPDATED_BY NUMBER;
53     X_LAST_UPDATE_LOGIN NUMBER;
54 begin
55   X_LAST_UPDATE_DATE := SYSDATE;
56   if(X_MODE = 'I') then
57     X_LAST_UPDATED_BY := 1;
58     X_LAST_UPDATE_LOGIN := 0;
59   elsif (X_MODE = 'R') then
60     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
61     if X_LAST_UPDATED_BY is NULL then
62       X_LAST_UPDATED_BY := -1;
63     end if;
64     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
65     if X_LAST_UPDATE_LOGIN is NULL then
66       X_LAST_UPDATE_LOGIN := -1;
67     end if;
68   else
69     FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
70     app_exception.raise_exception;
71   end if;
72   insert into PSP_EFFORT_REPORT_TEMPLATES (
73     TEMPLATE_ID,
74     TEMPLATE_NAME,
75     EFFORT_REPORT_PERIOD_NAME,
76     BEGIN_DATE,
77     END_DATE,
78     PEOPLE_GROUP_ID,
79     PERSON_ID,
80     EFFECTIVE_START_DATE,
81     EFFECTIVE_END_DATE,
82     SUPERVISOR_ID,
83     GL_CODE_COMBINATION_ID,
84     EXPENDITURE_TYPE,
85     TASK_ID,
86     PROJECT_TYPE_CLASS_CODE,
87     PROJECT_TYPE,
88     AWARD_TYPE,
89     FUNDING_SOURCE_ID,
90     ELEMENT_TYPE_ID,
91     ENABLE_WORKFLOW_FLAG,
92     USER_TEMPLATE_FLAG,
93     REPORT_TYPE,
94     REPORT_INITIATOR_ID ,
95     INITIATOR_APPS_NAME ,
96     ERROR_DATE_TIME,
97     BUSINESS_GROUP_ID,
98     SET_OF_BOOKS_ID,
99     ATTRIBUTE_CATEGORY,
100     ATTRIBUTE1,
101     ATTRIBUTE2,
102     ATTRIBUTE3,
103     ATTRIBUTE4,
104     ATTRIBUTE5,
105     ATTRIBUTE6,
106     ATTRIBUTE7,
107     ATTRIBUTE8,
108     ATTRIBUTE9,
109     ATTRIBUTE10,
110     ATTRIBUTE11,
111     ATTRIBUTE12,
112     ATTRIBUTE13,
113     ATTRIBUTE14,
114     ATTRIBUTE15,
115     CREATION_DATE,
116     CREATED_BY,
117     LAST_UPDATE_DATE,
118     LAST_UPDATED_BY,
119     LAST_UPDATE_LOGIN
120   ) values (
121     X_TEMPLATE_ID,
122     X_TEMPLATE_NAME,
123     X_EFFORT_REPORT_PERIOD_NAME,
124     X_BEGIN_DATE,
125     X_END_DATE,
126     X_PEOPLE_GROUP_ID,
127     X_PERSON_ID,
128     X_EFFECTIVE_START_DATE,
129     X_EFFECTIVE_END_DATE,
130     X_SUPERVISOR_ID,
131     X_GL_CODE_COMBINATION_ID,
132     X_EXPENDITURE_TYPE,
133     X_TASK_ID,
134     X_PROJECT_TYPE_CLASS_CODE,
135     X_PROJECT_TYPE,
136     X_AWARD_TYPE,
137     X_FUNDING_SOURCE_ID,
138     X_ELEMENT_TYPE_ID,
139     X_ENABLE_WORKFLOW_FLAG,
140     X_USER_TEMPLATE_FLAG,
141     X_REPORT_TYPE,
142     X_REPORT_INITIATOR_ID,
143     X_INITIATOR_APPS_NAME,
144     X_ERROR_DATE_TIME,
145     X_BUSINESS_GROUP_ID,
146     X_SET_OF_BOOKS_ID,
147     X_ATTRIBUTE_CATEGORY,
148     X_ATTRIBUTE1,
149     X_ATTRIBUTE2,
150     X_ATTRIBUTE3,
151     X_ATTRIBUTE4,
152     X_ATTRIBUTE5,
153     X_ATTRIBUTE6,
154     X_ATTRIBUTE7,
155     X_ATTRIBUTE8,
156     X_ATTRIBUTE9,
157     X_ATTRIBUTE10,
158     X_ATTRIBUTE11,
159     X_ATTRIBUTE12,
160     X_ATTRIBUTE13,
161     X_ATTRIBUTE14,
162     X_ATTRIBUTE15,
163     X_LAST_UPDATE_DATE,
164     X_LAST_UPDATED_BY,
165     X_LAST_UPDATE_DATE,
166     X_LAST_UPDATED_BY,
167     X_LAST_UPDATE_LOGIN
168   );
169 
170   open c;
171   fetch c into X_ROWID;
172   if (c%notfound) then
173     close c;
174     raise no_data_found;
175   end if;
176   close c;
177   commit;
178 
179 end INSERT_ROW;
180 
181 procedure LOCK_ROW (
182   X_TEMPLATE_ID in NUMBER,
183   X_TEMPLATE_NAME in VARCHAR2,
184   X_EFFORT_REPORT_PERIOD_NAME in VARCHAR2,
185   X_BEGIN_DATE in DATE,
186   X_END_DATE in DATE,
187   X_PEOPLE_GROUP_ID in NUMBER,
188   X_PERSON_ID in NUMBER,
189   X_EFFECTIVE_START_DATE in DATE,
190   X_EFFECTIVE_END_DATE in DATE,
191   X_SUPERVISOR_ID in NUMBER,
192   X_GL_CODE_COMBINATION_ID in NUMBER,
193   X_EXPENDITURE_TYPE in VARCHAR2,
194   X_TASK_ID in NUMBER,
195   X_PROJECT_TYPE_CLASS_CODE in VARCHAR2,
196   X_PROJECT_TYPE in VARCHAR2,
197   X_AWARD_TYPE in VARCHAR2,
198   X_FUNDING_SOURCE_ID in NUMBER,
199   X_ELEMENT_TYPE_ID in NUMBER,
200   X_ENABLE_WORKFLOW_FLAG in VARCHAR2,
201   X_USER_TEMPLATE_FLAG in VARCHAR2,
202   X_REPORT_TYPE in VARCHAR2,
203   X_REPORT_INITIATOR_ID in NUMBER,
204   X_INITIATOR_APPS_NAME in VARCHAR2,
205   X_ERROR_DATE_TIME in DATE,
206   X_BUSINESS_GROUP_ID in VARCHAR2,
207   X_SET_OF_BOOKS_ID in VARCHAR2,
208   X_ATTRIBUTE_CATEGORY in VARCHAR2,
209   X_ATTRIBUTE1 in VARCHAR2,
210   X_ATTRIBUTE2 in VARCHAR2,
211   X_ATTRIBUTE3 in VARCHAR2,
212   X_ATTRIBUTE4 in VARCHAR2,
213   X_ATTRIBUTE5 in VARCHAR2,
214   X_ATTRIBUTE6 in VARCHAR2,
215   X_ATTRIBUTE7 in VARCHAR2,
216   X_ATTRIBUTE8 in VARCHAR2,
217   X_ATTRIBUTE9 in VARCHAR2,
218   X_ATTRIBUTE10 in VARCHAR2,
219   X_ATTRIBUTE11 in VARCHAR2,
220   X_ATTRIBUTE12 in VARCHAR2,
221   X_ATTRIBUTE13 in VARCHAR2,
222   X_ATTRIBUTE14 in VARCHAR2,
223   X_ATTRIBUTE15 in VARCHAR2
224 ) is
225   cursor c1 is select
226       TEMPLATE_NAME,
227       EFFORT_REPORT_PERIOD_NAME,
228       BEGIN_DATE,
229       END_DATE,
230       PEOPLE_GROUP_ID,
231       PERSON_ID,
232       EFFECTIVE_START_DATE,
233       EFFECTIVE_END_DATE,
234       SUPERVISOR_ID,
235       GL_CODE_COMBINATION_ID,
236       EXPENDITURE_TYPE,
237       TASK_ID,
238       PROJECT_TYPE_CLASS_CODE,
239       PROJECT_TYPE,
240       AWARD_TYPE,
241       FUNDING_SOURCE_ID,
242       ELEMENT_TYPE_ID,
243       ENABLE_WORKFLOW_FLAG,
244       USER_TEMPLATE_FLAG,
245       REPORT_TYPE,
246       REPORT_INITIATOR_ID ,
247       INITIATOR_APPS_NAME ,
248       ERROR_DATE_TIME,
249       BUSINESS_GROUP_ID,
250       SET_OF_BOOKS_ID,
251       ATTRIBUTE_CATEGORY,
252       ATTRIBUTE1,
253       ATTRIBUTE2,
254       ATTRIBUTE3,
255       ATTRIBUTE4,
256       ATTRIBUTE5,
257       ATTRIBUTE6,
258       ATTRIBUTE7,
259       ATTRIBUTE8,
260       ATTRIBUTE9,
261       ATTRIBUTE10,
262       ATTRIBUTE11,
263       ATTRIBUTE12,
264       ATTRIBUTE13,
265       ATTRIBUTE14,
266       ATTRIBUTE15
267     from PSP_EFFORT_REPORT_TEMPLATES
268     where TEMPLATE_ID = X_TEMPLATE_ID
269     for update of TEMPLATE_ID nowait;
270   tlinfo c1%rowtype;
271 
272 begin
273   open c1;
274   fetch c1 into tlinfo;
275   if (c1%notfound) then
276     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
277     app_exception.raise_exception;
278     close c1;
279     return;
280   end if;
281   close c1;
282 
283   if ( (tlinfo.TEMPLATE_NAME = X_TEMPLATE_NAME)
284       AND ((tlinfo.EFFORT_REPORT_PERIOD_NAME = X_EFFORT_REPORT_PERIOD_NAME)
285            OR ((tlinfo.EFFORT_REPORT_PERIOD_NAME is null)
286                AND (X_EFFORT_REPORT_PERIOD_NAME is null)))
287       AND ((tlinfo.BEGIN_DATE = X_BEGIN_DATE)
288            OR ((tlinfo.BEGIN_DATE is null)
289                AND (X_BEGIN_DATE is null)))
290       AND ((tlinfo.END_DATE = X_END_DATE)
291            OR ((tlinfo.END_DATE is null)
292                AND (X_END_DATE is null)))
293       AND ((tlinfo.PEOPLE_GROUP_ID = X_PEOPLE_GROUP_ID)
294            OR ((tlinfo.PEOPLE_GROUP_ID is null)
295                AND (X_PEOPLE_GROUP_ID is null)))
296       AND ((tlinfo.PERSON_ID = X_PERSON_ID)
297            OR ((tlinfo.PERSON_ID is null)
298                AND (X_PERSON_ID is null)))
299       AND ((tlinfo.EFFECTIVE_START_DATE = X_EFFECTIVE_START_DATE)
300            OR ((tlinfo.EFFECTIVE_START_DATE is null)
301                AND (X_EFFECTIVE_START_DATE is null)))
302       AND ((tlinfo.EFFECTIVE_END_DATE = X_EFFECTIVE_END_DATE)
303            OR ((tlinfo.EFFECTIVE_END_DATE is null)
304                AND (X_EFFECTIVE_END_DATE is null)))
305       AND ((tlinfo.SUPERVISOR_ID = X_SUPERVISOR_ID)
306            OR ((tlinfo.SUPERVISOR_ID is null)
307                AND (X_SUPERVISOR_ID is null)))
308       AND ((tlinfo.GL_CODE_COMBINATION_ID = X_GL_CODE_COMBINATION_ID)
309            OR ((tlinfo.GL_CODE_COMBINATION_ID is null)
310                AND (X_GL_CODE_COMBINATION_ID is null)))
311       AND ((tlinfo.EXPENDITURE_TYPE = X_EXPENDITURE_TYPE)
312            OR ((tlinfo.EXPENDITURE_TYPE is null)
313                AND (X_EXPENDITURE_TYPE is null)))
314       AND ((tlinfo.TASK_ID = X_TASK_ID)
315            OR ((tlinfo.TASK_ID is null)
316                AND (X_TASK_ID is null)))
317       AND ((tlinfo.PROJECT_TYPE_CLASS_CODE = X_PROJECT_TYPE_CLASS_CODE)
318            OR ((tlinfo.PROJECT_TYPE_CLASS_CODE is null)
319                AND (X_PROJECT_TYPE_CLASS_CODE is null)))
320       AND ((tlinfo.PROJECT_TYPE = X_PROJECT_TYPE)
321            OR ((tlinfo.PROJECT_TYPE is null)
322                AND (X_PROJECT_TYPE is null)))
323       AND ((tlinfo.AWARD_TYPE = X_AWARD_TYPE)
324            OR ((tlinfo.AWARD_TYPE is null)
325                AND (X_AWARD_TYPE is null)))
326       AND ((tlinfo.FUNDING_SOURCE_ID = X_FUNDING_SOURCE_ID)
327            OR ((tlinfo.FUNDING_SOURCE_ID is null)
328                AND (X_FUNDING_SOURCE_ID is null)))
329       AND ((tlinfo.ELEMENT_TYPE_ID = X_ELEMENT_TYPE_ID)
330            OR ((tlinfo.ELEMENT_TYPE_ID is null)
331                AND (X_ELEMENT_TYPE_ID is null)))
332       AND ((tlinfo.ENABLE_WORKFLOW_FLAG = X_ENABLE_WORKFLOW_FLAG)
333            OR ((tlinfo.ENABLE_WORKFLOW_FLAG is null)
334                AND (X_ENABLE_WORKFLOW_FLAG is null)))
335       AND ((tlinfo.USER_TEMPLATE_FLAG = X_USER_TEMPLATE_FLAG)
336            OR ((tlinfo.USER_TEMPLATE_FLAG is null)
337                AND (X_USER_TEMPLATE_FLAG is null)))
338       AND ((tlinfo.REPORT_TYPE = X_REPORT_TYPE)
339            OR ((tlinfo.REPORT_TYPE is null)
340                AND (X_REPORT_TYPE is null)))
341       AND ((tlinfo.REPORT_INITIATOR_ID = X_REPORT_INITIATOR_ID)
342            OR ((tlinfo.REPORT_INITIATOR_ID is null)
343                AND (X_REPORT_INITIATOR_ID is null)))
344       AND ((tlinfo.INITIATOR_APPS_NAME = X_INITIATOR_APPS_NAME)
345            OR ((tlinfo.INITIATOR_APPS_NAME is null)
346                AND (X_INITIATOR_APPS_NAME is null)))
347       AND ((tlinfo.ERROR_DATE_TIME = X_ERROR_DATE_TIME)
348            OR ((tlinfo.ERROR_DATE_TIME is null)
349                AND (X_ERROR_DATE_TIME is null)))
350       AND ((tlinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
351            OR ((tlinfo.ATTRIBUTE_CATEGORY is null)
352                AND (X_ATTRIBUTE_CATEGORY is null)))
353       AND ((tlinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
354            OR ((tlinfo.ATTRIBUTE1 is null)
355                AND (X_ATTRIBUTE1 is null)))
356       AND ((tlinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
357            OR ((tlinfo.ATTRIBUTE2 is null)
358                AND (X_ATTRIBUTE2 is null)))
359       AND ((tlinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
360            OR ((tlinfo.ATTRIBUTE3 is null)
361                AND (X_ATTRIBUTE3 is null)))
362       AND ((tlinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
363            OR ((tlinfo.ATTRIBUTE4 is null)
364                AND (X_ATTRIBUTE4 is null)))
365       AND ((tlinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
366            OR ((tlinfo.ATTRIBUTE5 is null)
367                AND (X_ATTRIBUTE5 is null)))
368       AND ((tlinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
369            OR ((tlinfo.ATTRIBUTE6 is null)
370                AND (X_ATTRIBUTE6 is null)))
371       AND ((tlinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
372            OR ((tlinfo.ATTRIBUTE7 is null)
373                AND (X_ATTRIBUTE7 is null)))
374       AND ((tlinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
375            OR ((tlinfo.ATTRIBUTE8 is null)
376                AND (X_ATTRIBUTE8 is null)))
377       AND ((tlinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
378            OR ((tlinfo.ATTRIBUTE9 is null)
379                AND (X_ATTRIBUTE9 is null)))
380       AND ((tlinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
381            OR ((tlinfo.ATTRIBUTE10 is null)
382                AND (X_ATTRIBUTE10 is null)))
383       AND ((tlinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
384            OR ((tlinfo.ATTRIBUTE11 is null)
385                AND (X_ATTRIBUTE11 is null)))
386       AND ((tlinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
387            OR ((tlinfo.ATTRIBUTE12 is null)
388                AND (X_ATTRIBUTE12 is null)))
389       AND ((tlinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
390            OR ((tlinfo.ATTRIBUTE13 is null)
391                AND (X_ATTRIBUTE13 is null)))
392       AND ((tlinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
393            OR ((tlinfo.ATTRIBUTE14 is null)
394                AND (X_ATTRIBUTE14 is null)))
395       AND ((tlinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
396            OR ((tlinfo.ATTRIBUTE15 is null)
397                AND (X_ATTRIBUTE15 is null)))
398   ) then
399     null;
400   else
401     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
402     app_exception.raise_exception;
403   end if;
404   return;
405 end LOCK_ROW;
406 
407 procedure UPDATE_ROW (
408   X_TEMPLATE_ID in NUMBER,
409   X_TEMPLATE_NAME in VARCHAR2,
410   X_EFFORT_REPORT_PERIOD_NAME in VARCHAR2,
411   X_BEGIN_DATE in DATE,
412   X_END_DATE in DATE,
413   X_PEOPLE_GROUP_ID in NUMBER,
414   X_PERSON_ID in NUMBER,
415   X_EFFECTIVE_START_DATE in DATE,
416   X_EFFECTIVE_END_DATE in DATE,
417   X_SUPERVISOR_ID in NUMBER,
418   X_GL_CODE_COMBINATION_ID in NUMBER,
419   X_EXPENDITURE_TYPE in VARCHAR2,
420   X_TASK_ID in NUMBER,
421   X_PROJECT_TYPE_CLASS_CODE in VARCHAR2,
422   X_PROJECT_TYPE in VARCHAR2,
423   X_AWARD_TYPE in VARCHAR2,
424   X_FUNDING_SOURCE_ID in NUMBER,
425   X_ELEMENT_TYPE_ID in NUMBER,
426   X_ENABLE_WORKFLOW_FLAG in VARCHAR2,
427   X_USER_TEMPLATE_FLAG in VARCHAR2,
428   X_REPORT_TYPE in VARCHAR2,
429   X_REPORT_INITIATOR_ID in NUMBER,
430   X_INITIATOR_APPS_NAME in VARCHAR2,
431   X_ERROR_DATE_TIME in DATE,
432   X_BUSINESS_GROUP_ID in VARCHAR2,
433   X_SET_OF_BOOKS_ID in VARCHAR2,
434   X_ATTRIBUTE_CATEGORY in VARCHAR2,
435   X_ATTRIBUTE1 in VARCHAR2,
436   X_ATTRIBUTE2 in VARCHAR2,
437   X_ATTRIBUTE3 in VARCHAR2,
441   X_ATTRIBUTE7 in VARCHAR2,
438   X_ATTRIBUTE4 in VARCHAR2,
439   X_ATTRIBUTE5 in VARCHAR2,
440   X_ATTRIBUTE6 in VARCHAR2,
442   X_ATTRIBUTE8 in VARCHAR2,
443   X_ATTRIBUTE9 in VARCHAR2,
444   X_ATTRIBUTE10 in VARCHAR2,
445   X_ATTRIBUTE11 in VARCHAR2,
446   X_ATTRIBUTE12 in VARCHAR2,
447   X_ATTRIBUTE13 in VARCHAR2,
448   X_ATTRIBUTE14 in VARCHAR2,
449   X_ATTRIBUTE15 in VARCHAR2,
450   X_MODE in VARCHAR2 default 'R'
451   ) is
452     X_LAST_UPDATE_DATE DATE;
453     X_LAST_UPDATED_BY NUMBER;
454     X_LAST_UPDATE_LOGIN NUMBER;
455 begin
456   X_LAST_UPDATE_DATE := SYSDATE;
457   if(X_MODE = 'I') then
458     X_LAST_UPDATED_BY := 1;
459     X_LAST_UPDATE_LOGIN := 0;
460   elsif (X_MODE = 'R') then
461     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
462     if X_LAST_UPDATED_BY is NULL then
463       X_LAST_UPDATED_BY := -1;
464     end if;
465     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
466     if X_LAST_UPDATE_LOGIN is NULL then
467       X_LAST_UPDATE_LOGIN := -1;
468     end if;
469   else
470     FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
471     app_exception.raise_exception;
472   end if;
473   update PSP_EFFORT_REPORT_TEMPLATES set
474     TEMPLATE_NAME = X_TEMPLATE_NAME,
475     EFFORT_REPORT_PERIOD_NAME = X_EFFORT_REPORT_PERIOD_NAME,
476     BEGIN_DATE = X_BEGIN_DATE,
477     END_DATE = X_END_DATE,
478     PEOPLE_GROUP_ID = X_PEOPLE_GROUP_ID,
479     PERSON_ID = X_PERSON_ID,
480     EFFECTIVE_START_DATE = X_EFFECTIVE_START_DATE,
481     EFFECTIVE_END_DATE = X_EFFECTIVE_END_DATE,
482     SUPERVISOR_ID = X_SUPERVISOR_ID,
483     GL_CODE_COMBINATION_ID = X_GL_CODE_COMBINATION_ID,
484     EXPENDITURE_TYPE = X_EXPENDITURE_TYPE,
485     TASK_ID = X_TASK_ID,
486     PROJECT_TYPE_CLASS_CODE = X_PROJECT_TYPE_CLASS_CODE,
487     PROJECT_TYPE = X_PROJECT_TYPE,
488     AWARD_TYPE = X_AWARD_TYPE,
489     FUNDING_SOURCE_ID = X_FUNDING_SOURCE_ID,
490     ELEMENT_TYPE_ID = X_ELEMENT_TYPE_ID,
491     ENABLE_WORKFLOW_FLAG = X_ENABLE_WORKFLOW_FLAG,
492     USER_TEMPLATE_FLAG = X_USER_TEMPLATE_FLAG,
493     REPORT_TYPE = X_REPORT_TYPE,
494     REPORT_INITIATOR_ID = X_REPORT_INITIATOR_ID ,
495     INITIATOR_APPS_NAME = X_INITIATOR_APPS_NAME,
496     ERROR_DATE_TIME = X_ERROR_DATE_TIME,
497     BUSINESS_GROUP_ID = X_BUSINESS_GROUP_ID,
498     SET_OF_BOOKS_ID = X_SET_OF_BOOKS_ID,
499     ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
500     ATTRIBUTE1 = X_ATTRIBUTE1,
501     ATTRIBUTE2 = X_ATTRIBUTE2,
502     ATTRIBUTE3 = X_ATTRIBUTE3,
503     ATTRIBUTE4 = X_ATTRIBUTE4,
504     ATTRIBUTE5 = X_ATTRIBUTE5,
505     ATTRIBUTE6 = X_ATTRIBUTE6,
506     ATTRIBUTE7 = X_ATTRIBUTE7,
507     ATTRIBUTE8 = X_ATTRIBUTE8,
508     ATTRIBUTE9 = X_ATTRIBUTE9,
509     ATTRIBUTE10 = X_ATTRIBUTE10,
510     ATTRIBUTE11 = X_ATTRIBUTE11,
511     ATTRIBUTE12 = X_ATTRIBUTE12,
512     ATTRIBUTE13 = X_ATTRIBUTE13,
513     ATTRIBUTE14 = X_ATTRIBUTE14,
514     ATTRIBUTE15 = X_ATTRIBUTE15,
515     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
516     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
517     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
518   where TEMPLATE_ID = X_TEMPLATE_ID
519   ;
520   if (sql%notfound) then
521     raise no_data_found;
522   end if;
523   commit;
524 end UPDATE_ROW;
525 
526 procedure ADD_ROW (
527   X_ROWID in out NOCOPY VARCHAR2,
528   X_TEMPLATE_ID in NUMBER,
529   X_TEMPLATE_NAME in VARCHAR2,
530   X_EFFORT_REPORT_PERIOD_NAME in VARCHAR2,
531   X_BEGIN_DATE in DATE,
532   X_END_DATE in DATE,
533   X_PEOPLE_GROUP_ID in NUMBER,
534   X_PERSON_ID in NUMBER,
535   X_EFFECTIVE_START_DATE in DATE,
536   X_EFFECTIVE_END_DATE in DATE,
537   X_SUPERVISOR_ID in NUMBER,
538   X_GL_CODE_COMBINATION_ID in NUMBER,
539   X_EXPENDITURE_TYPE in VARCHAR2,
540   X_TASK_ID in NUMBER,
541   X_PROJECT_TYPE_CLASS_CODE in VARCHAR2,
542   X_PROJECT_TYPE in VARCHAR2,
543   X_AWARD_TYPE in VARCHAR2,
544   X_FUNDING_SOURCE_ID in NUMBER,
545   X_ELEMENT_TYPE_ID in NUMBER,
546   X_ENABLE_WORKFLOW_FLAG in VARCHAR2,
547   X_USER_TEMPLATE_FLAG in VARCHAR2,
548   X_REPORT_TYPE in VARCHAR2,
549   X_REPORT_INITIATOR_ID in NUMBER,
550   X_INITIATOR_APPS_NAME in VARCHAR2,
551   X_ERROR_DATE_TIME in DATE,
552   X_BUSINESS_GROUP_ID in VARCHAR2,
553   X_SET_OF_BOOKS_ID in VARCHAR2,
554   X_ATTRIBUTE_CATEGORY in VARCHAR2,
555   X_ATTRIBUTE1 in VARCHAR2,
556   X_ATTRIBUTE2 in VARCHAR2,
557   X_ATTRIBUTE3 in VARCHAR2,
558   X_ATTRIBUTE4 in VARCHAR2,
559   X_ATTRIBUTE5 in VARCHAR2,
560   X_ATTRIBUTE6 in VARCHAR2,
561   X_ATTRIBUTE7 in VARCHAR2,
562   X_ATTRIBUTE8 in VARCHAR2,
563   X_ATTRIBUTE9 in VARCHAR2,
564   X_ATTRIBUTE10 in VARCHAR2,
565   X_ATTRIBUTE11 in VARCHAR2,
566   X_ATTRIBUTE12 in VARCHAR2,
567   X_ATTRIBUTE13 in VARCHAR2,
568   X_ATTRIBUTE14 in VARCHAR2,
569   X_ATTRIBUTE15 in VARCHAR2,
570   X_MODE in VARCHAR2 default 'R'
571   ) is
572   cursor c1 is select rowid from PSP_EFFORT_REPORT_TEMPLATES
573      where TEMPLATE_ID = X_TEMPLATE_ID
574   ;
575   dummy c1%rowtype;
576 begin
577   open c1;
578   fetch c1 into dummy;
579   if (c1%notfound) then
580     close c1;
581     INSERT_ROW (
582      X_ROWID,
583      X_TEMPLATE_ID,
587      X_END_DATE,
584      X_TEMPLATE_NAME,
585      X_EFFORT_REPORT_PERIOD_NAME,
586      X_BEGIN_DATE,
588      X_PEOPLE_GROUP_ID,
589      X_PERSON_ID,
590      X_EFFECTIVE_START_DATE,
591      X_EFFECTIVE_END_DATE,
592      X_SUPERVISOR_ID,
593      X_GL_CODE_COMBINATION_ID,
594      X_EXPENDITURE_TYPE,
595      X_TASK_ID,
596      X_PROJECT_TYPE_CLASS_CODE,
597      X_PROJECT_TYPE,
598      X_AWARD_TYPE,
599      X_FUNDING_SOURCE_ID,
600      X_ELEMENT_TYPE_ID,
601      X_ENABLE_WORKFLOW_FLAG,
602      X_USER_TEMPLATE_FLAG,
603      X_REPORT_TYPE,
604      X_REPORT_INITIATOR_ID ,
605      X_INITIATOR_APPS_NAME ,
606      X_ERROR_DATE_TIME,
607      X_BUSINESS_GROUP_ID,
608      X_SET_OF_BOOKS_ID,
609      X_ATTRIBUTE_CATEGORY,
610      X_ATTRIBUTE1,
611      X_ATTRIBUTE2,
612      X_ATTRIBUTE3,
613      X_ATTRIBUTE4,
614      X_ATTRIBUTE5,
615      X_ATTRIBUTE6,
616      X_ATTRIBUTE7,
617      X_ATTRIBUTE8,
618      X_ATTRIBUTE9,
619      X_ATTRIBUTE10,
620      X_ATTRIBUTE11,
621      X_ATTRIBUTE12,
622      X_ATTRIBUTE13,
623      X_ATTRIBUTE14,
624      X_ATTRIBUTE15,
625      X_MODE);
626     return;
627   end if;
628   close c1;
629   UPDATE_ROW (
630    X_TEMPLATE_ID,
631    X_TEMPLATE_NAME,
632    X_EFFORT_REPORT_PERIOD_NAME,
633    X_BEGIN_DATE,
634    X_END_DATE,
635    X_PEOPLE_GROUP_ID,
636    X_PERSON_ID,
637    X_EFFECTIVE_START_DATE,
638    X_EFFECTIVE_END_DATE,
639    X_SUPERVISOR_ID,
640    X_GL_CODE_COMBINATION_ID,
641    X_EXPENDITURE_TYPE,
642    X_TASK_ID,
643    X_PROJECT_TYPE_CLASS_CODE,
644    X_PROJECT_TYPE,
645    X_AWARD_TYPE,
646    X_FUNDING_SOURCE_ID,
647    X_ELEMENT_TYPE_ID,
648    X_ENABLE_WORKFLOW_FLAG,
649    X_USER_TEMPLATE_FLAG,
650    X_REPORT_TYPE,
651    X_REPORT_INITIATOR_ID ,
652    X_INITIATOR_APPS_NAME ,
653    X_ERROR_DATE_TIME,
654    X_BUSINESS_GROUP_ID,
655    X_SET_OF_BOOKS_ID,
656    X_ATTRIBUTE_CATEGORY,
657    X_ATTRIBUTE1,
658    X_ATTRIBUTE2,
659    X_ATTRIBUTE3,
660    X_ATTRIBUTE4,
661    X_ATTRIBUTE5,
662    X_ATTRIBUTE6,
663    X_ATTRIBUTE7,
664    X_ATTRIBUTE8,
665    X_ATTRIBUTE9,
666    X_ATTRIBUTE10,
667    X_ATTRIBUTE11,
668    X_ATTRIBUTE12,
669    X_ATTRIBUTE13,
670    X_ATTRIBUTE14,
671    X_ATTRIBUTE15,
672    X_MODE);
673 end ADD_ROW;
674 
675 procedure DELETE_ROW (
676   X_TEMPLATE_ID in NUMBER
677 ) is
678 begin
679   delete from PSP_EFFORT_REPORT_TEMPLATES
680   where TEMPLATE_ID = X_TEMPLATE_ID;
681   if (sql%notfound) then
682     ---raise no_data_found;
683     null;
684   end if;
685   commit;
686 end DELETE_ROW;
687 
688 end PSP_EFT_REPORT_TEMPLATES_PKG;