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

Source


1 package body GMS_REPORT_TEMPLATES_PKG as
2 -- $Header: gmsawrtb.pls 115.4 2002/11/26 19:17:33 jmuthuku ship $
3 procedure INSERT_ROW (
4   X_ROWID in out NOCOPY VARCHAR2,
5   X_REPORT_TEMPLATE_ID in NUMBER,
6   X_REPORT_NAME in VARCHAR2,
7   X_TYPE in VARCHAR2,
8   X_START_DATE_ACTIVE in DATE,
9   X_END_DATE_ACTIVE in DATE,
10   X_TRANSACTION_NUMBER in NUMBER,
11   X_AGENCY_SPECIFIC_FORM in VARCHAR2,
12   X_DESCRIPTION in VARCHAR2,
13   X_ATTRIBUTE_CATEGORY in VARCHAR2,
14   X_ATTRIBUTE1 in VARCHAR2,
15   X_ATTRIBUTE2 in VARCHAR2,
16   X_ATTRIBUTE3 in VARCHAR2,
17   X_ATTRIBUTE4 in VARCHAR2,
18   X_ATTRIBUTE5 in VARCHAR2,
19   X_ATTRIBUTE6 in VARCHAR2,
20   X_ATTRIBUTE7 in VARCHAR2,
21   X_ATTRIBUTE8 in VARCHAR2,
22   X_ATTRIBUTE9 in VARCHAR2,
23   X_ATTRIBUTE10 in VARCHAR2,
24   X_ATTRIBUTE11 in VARCHAR2,
25   X_ATTRIBUTE12 in VARCHAR2,
26   X_ATTRIBUTE13 in VARCHAR2,
27   X_ATTRIBUTE14 in VARCHAR2,
28   X_ATTRIBUTE15 in VARCHAR2,
29   X_MODE in VARCHAR2 default 'R'
30   ) is
31     cursor C is select ROWID from GMS_REPORT_TEMPLATES
32       where REPORT_TEMPLATE_ID = X_REPORT_TEMPLATE_ID;
33     X_LAST_UPDATE_DATE DATE;
34     X_LAST_UPDATED_BY NUMBER;
35     X_LAST_UPDATE_LOGIN NUMBER;
36 begin
37   X_LAST_UPDATE_DATE := SYSDATE;
38   if(X_MODE = 'I') then
39     X_LAST_UPDATED_BY := 1;
40     X_LAST_UPDATE_LOGIN := 0;
41   elsif (X_MODE = 'R') then
42     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
43     if X_LAST_UPDATED_BY is NULL then
44       X_LAST_UPDATED_BY := -1;
45     end if;
46     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
47     if X_LAST_UPDATE_LOGIN is NULL then
48       X_LAST_UPDATE_LOGIN := -1;
49     end if;
50   else
51     FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
52     app_exception.raise_exception;
53   end if;
54   insert into GMS_REPORT_TEMPLATES (
55     REPORT_TEMPLATE_ID,
56     REPORT_NAME,
57     TYPE,
58     START_DATE_ACTIVE,
59     END_DATE_ACTIVE,
60     TRANSACTION_NUMBER,
61     AGENCY_SPECIFIC_FORM,
62     DESCRIPTION,
63     ATTRIBUTE_CATEGORY,
64     ATTRIBUTE1,
65     ATTRIBUTE2,
66     ATTRIBUTE3,
67     ATTRIBUTE4,
68     ATTRIBUTE5,
69     ATTRIBUTE6,
70     ATTRIBUTE7,
71     ATTRIBUTE8,
72     ATTRIBUTE9,
73     ATTRIBUTE10,
74     ATTRIBUTE11,
75     ATTRIBUTE12,
76     ATTRIBUTE13,
77     ATTRIBUTE14,
78     ATTRIBUTE15,
79     CREATION_DATE,
80     CREATED_BY,
81     LAST_UPDATE_DATE,
82     LAST_UPDATED_BY,
83     LAST_UPDATE_LOGIN
84   ) values (
85     X_REPORT_TEMPLATE_ID,
86     X_REPORT_NAME,
87     X_TYPE,
88     X_START_DATE_ACTIVE,
89     X_END_DATE_ACTIVE,
90     X_TRANSACTION_NUMBER,
91     X_AGENCY_SPECIFIC_FORM,
92     X_DESCRIPTION,
93     X_ATTRIBUTE_CATEGORY,
94     X_ATTRIBUTE1,
95     X_ATTRIBUTE2,
96     X_ATTRIBUTE3,
97     X_ATTRIBUTE4,
98     X_ATTRIBUTE5,
99     X_ATTRIBUTE6,
100     X_ATTRIBUTE7,
101     X_ATTRIBUTE8,
102     X_ATTRIBUTE9,
103     X_ATTRIBUTE10,
104     X_ATTRIBUTE11,
105     X_ATTRIBUTE12,
106     X_ATTRIBUTE13,
107     X_ATTRIBUTE14,
108     X_ATTRIBUTE15,
109     X_LAST_UPDATE_DATE,
110     X_LAST_UPDATED_BY,
111     X_LAST_UPDATE_DATE,
112     X_LAST_UPDATED_BY,
113     X_LAST_UPDATE_LOGIN
114   );
115 
116   open c;
117   fetch c into X_ROWID;
118   if (c%notfound) then
119     close c;
120     raise no_data_found;
121   end if;
122   close c;
123 
124 end INSERT_ROW;
125 
126 procedure LOCK_ROW (
127   X_REPORT_TEMPLATE_ID in NUMBER,
128   X_REPORT_NAME in VARCHAR2,
129   X_TYPE in VARCHAR2,
130   X_START_DATE_ACTIVE in DATE,
131   X_END_DATE_ACTIVE in DATE,
132   X_TRANSACTION_NUMBER in NUMBER,
133   X_AGENCY_SPECIFIC_FORM in VARCHAR2,
134   X_DESCRIPTION in VARCHAR2,
135   X_ATTRIBUTE_CATEGORY in VARCHAR2,
136   X_ATTRIBUTE1 in VARCHAR2,
137   X_ATTRIBUTE2 in VARCHAR2,
138   X_ATTRIBUTE3 in VARCHAR2,
139   X_ATTRIBUTE4 in VARCHAR2,
140   X_ATTRIBUTE5 in VARCHAR2,
141   X_ATTRIBUTE6 in VARCHAR2,
142   X_ATTRIBUTE7 in VARCHAR2,
143   X_ATTRIBUTE8 in VARCHAR2,
144   X_ATTRIBUTE9 in VARCHAR2,
145   X_ATTRIBUTE10 in VARCHAR2,
146   X_ATTRIBUTE11 in VARCHAR2,
147   X_ATTRIBUTE12 in VARCHAR2,
148   X_ATTRIBUTE13 in VARCHAR2,
149   X_ATTRIBUTE14 in VARCHAR2,
150   X_ATTRIBUTE15 in VARCHAR2
151 ) is
152   cursor c1 is select
153       REPORT_NAME,
154       TYPE,
155       START_DATE_ACTIVE,
156       END_DATE_ACTIVE,
157       TRANSACTION_NUMBER,
158       AGENCY_SPECIFIC_FORM,
159       DESCRIPTION,
160       ATTRIBUTE_CATEGORY,
161       ATTRIBUTE1,
162       ATTRIBUTE2,
163       ATTRIBUTE3,
164       ATTRIBUTE4,
165       ATTRIBUTE5,
166       ATTRIBUTE6,
167       ATTRIBUTE7,
168       ATTRIBUTE8,
169       ATTRIBUTE9,
170       ATTRIBUTE10,
171       ATTRIBUTE11,
172       ATTRIBUTE12,
173       ATTRIBUTE13,
174       ATTRIBUTE14,
175       ATTRIBUTE15
176     from GMS_REPORT_TEMPLATES
177     where REPORT_TEMPLATE_ID = X_REPORT_TEMPLATE_ID
178     for update of REPORT_TEMPLATE_ID nowait;
179   tlinfo c1%rowtype;
180 
181 begin
182   open c1;
183   fetch c1 into tlinfo;
184   if (c1%notfound) then
185     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
186     app_exception.raise_exception;
187     close c1;
188     return;
189   end if;
190   close c1;
191 
192   if ( (tlinfo.REPORT_NAME = X_REPORT_NAME)
193       AND (tlinfo.TYPE = X_TYPE)
194       AND ((tlinfo.START_DATE_ACTIVE = X_START_DATE_ACTIVE)
195            OR ((tlinfo.START_DATE_ACTIVE is null)
196                AND (X_START_DATE_ACTIVE is null)))
197       AND ((tlinfo.END_DATE_ACTIVE = X_END_DATE_ACTIVE)
198            OR ((tlinfo.END_DATE_ACTIVE is null)
199                AND (X_END_DATE_ACTIVE is null)))
200       AND ((tlinfo.TRANSACTION_NUMBER = X_TRANSACTION_NUMBER)
201            OR ((tlinfo.TRANSACTION_NUMBER is null)
202                AND (X_TRANSACTION_NUMBER is null)))
203       AND ((tlinfo.AGENCY_SPECIFIC_FORM = X_AGENCY_SPECIFIC_FORM)
204            OR ((tlinfo.AGENCY_SPECIFIC_FORM is null)
205                AND (X_AGENCY_SPECIFIC_FORM is null)))
206       AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
207            OR ((tlinfo.DESCRIPTION is null)
208                AND (X_DESCRIPTION is null)))
209       AND ((tlinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
210            OR ((tlinfo.ATTRIBUTE_CATEGORY is null)
211                AND (X_ATTRIBUTE_CATEGORY is null)))
212       AND ((tlinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
213            OR ((tlinfo.ATTRIBUTE1 is null)
214                AND (X_ATTRIBUTE1 is null)))
215       AND ((tlinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
216            OR ((tlinfo.ATTRIBUTE2 is null)
217                AND (X_ATTRIBUTE2 is null)))
218       AND ((tlinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
219            OR ((tlinfo.ATTRIBUTE3 is null)
220                AND (X_ATTRIBUTE3 is null)))
221       AND ((tlinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
222            OR ((tlinfo.ATTRIBUTE4 is null)
223                AND (X_ATTRIBUTE4 is null)))
224       AND ((tlinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
225            OR ((tlinfo.ATTRIBUTE5 is null)
226                AND (X_ATTRIBUTE5 is null)))
227       AND ((tlinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
228            OR ((tlinfo.ATTRIBUTE6 is null)
229                AND (X_ATTRIBUTE6 is null)))
230       AND ((tlinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
231            OR ((tlinfo.ATTRIBUTE7 is null)
232                AND (X_ATTRIBUTE7 is null)))
233       AND ((tlinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
234            OR ((tlinfo.ATTRIBUTE8 is null)
235                AND (X_ATTRIBUTE8 is null)))
236       AND ((tlinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
237            OR ((tlinfo.ATTRIBUTE9 is null)
238                AND (X_ATTRIBUTE9 is null)))
239       AND ((tlinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
240            OR ((tlinfo.ATTRIBUTE10 is null)
241                AND (X_ATTRIBUTE10 is null)))
242       AND ((tlinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
243            OR ((tlinfo.ATTRIBUTE11 is null)
244                AND (X_ATTRIBUTE11 is null)))
245       AND ((tlinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
246            OR ((tlinfo.ATTRIBUTE12 is null)
247                AND (X_ATTRIBUTE12 is null)))
248       AND ((tlinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
249            OR ((tlinfo.ATTRIBUTE13 is null)
250                AND (X_ATTRIBUTE13 is null)))
251       AND ((tlinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
252            OR ((tlinfo.ATTRIBUTE14 is null)
253                AND (X_ATTRIBUTE14 is null)))
254       AND ((tlinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
255            OR ((tlinfo.ATTRIBUTE15 is null)
256                AND (X_ATTRIBUTE15 is null)))
257   ) then
258     null;
259   else
260     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
261     app_exception.raise_exception;
262   end if;
263   return;
264 end LOCK_ROW;
265 
266 procedure UPDATE_ROW (
267   X_REPORT_TEMPLATE_ID in NUMBER,
268   X_REPORT_NAME in VARCHAR2,
269   X_TYPE in VARCHAR2,
270   X_START_DATE_ACTIVE in DATE,
271   X_END_DATE_ACTIVE in DATE,
272   X_TRANSACTION_NUMBER in NUMBER,
273   X_AGENCY_SPECIFIC_FORM in VARCHAR2,
274   X_DESCRIPTION in VARCHAR2,
275   X_ATTRIBUTE_CATEGORY in VARCHAR2,
276   X_ATTRIBUTE1 in VARCHAR2,
277   X_ATTRIBUTE2 in VARCHAR2,
278   X_ATTRIBUTE3 in VARCHAR2,
279   X_ATTRIBUTE4 in VARCHAR2,
280   X_ATTRIBUTE5 in VARCHAR2,
281   X_ATTRIBUTE6 in VARCHAR2,
282   X_ATTRIBUTE7 in VARCHAR2,
283   X_ATTRIBUTE8 in VARCHAR2,
284   X_ATTRIBUTE9 in VARCHAR2,
285   X_ATTRIBUTE10 in VARCHAR2,
286   X_ATTRIBUTE11 in VARCHAR2,
287   X_ATTRIBUTE12 in VARCHAR2,
288   X_ATTRIBUTE13 in VARCHAR2,
289   X_ATTRIBUTE14 in VARCHAR2,
290   X_ATTRIBUTE15 in VARCHAR2,
291   X_MODE in VARCHAR2 default 'R'
292   ) is
293     X_LAST_UPDATE_DATE DATE;
294     X_LAST_UPDATED_BY NUMBER;
295     X_LAST_UPDATE_LOGIN NUMBER;
296 begin
297   X_LAST_UPDATE_DATE := SYSDATE;
298   if(X_MODE = 'I') then
299     X_LAST_UPDATED_BY := 1;
300     X_LAST_UPDATE_LOGIN := 0;
301   elsif (X_MODE = 'R') then
302     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
303     if X_LAST_UPDATED_BY is NULL then
304       X_LAST_UPDATED_BY := -1;
305     end if;
306     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
307     if X_LAST_UPDATE_LOGIN is NULL then
308       X_LAST_UPDATE_LOGIN := -1;
309     end if;
310   else
311     FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
312     app_exception.raise_exception;
313   end if;
314   update GMS_REPORT_TEMPLATES set
315     REPORT_NAME = X_REPORT_NAME,
316     TYPE = X_TYPE,
317     START_DATE_ACTIVE = X_START_DATE_ACTIVE,
318     END_DATE_ACTIVE = X_END_DATE_ACTIVE,
319     TRANSACTION_NUMBER = X_TRANSACTION_NUMBER,
320     AGENCY_SPECIFIC_FORM = X_AGENCY_SPECIFIC_FORM,
321     DESCRIPTION = X_DESCRIPTION,
322     ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
323     ATTRIBUTE1 = X_ATTRIBUTE1,
324     ATTRIBUTE2 = X_ATTRIBUTE2,
325     ATTRIBUTE3 = X_ATTRIBUTE3,
326     ATTRIBUTE4 = X_ATTRIBUTE4,
327     ATTRIBUTE5 = X_ATTRIBUTE5,
328     ATTRIBUTE6 = X_ATTRIBUTE6,
329     ATTRIBUTE7 = X_ATTRIBUTE7,
330     ATTRIBUTE8 = X_ATTRIBUTE8,
331     ATTRIBUTE9 = X_ATTRIBUTE9,
332     ATTRIBUTE10 = X_ATTRIBUTE10,
333     ATTRIBUTE11 = X_ATTRIBUTE11,
334     ATTRIBUTE12 = X_ATTRIBUTE12,
335     ATTRIBUTE13 = X_ATTRIBUTE13,
336     ATTRIBUTE14 = X_ATTRIBUTE14,
337     ATTRIBUTE15 = X_ATTRIBUTE15,
338     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
339     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
340     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
341   where REPORT_TEMPLATE_ID = X_REPORT_TEMPLATE_ID
342   ;
343   if (sql%notfound) then
344     raise no_data_found;
345   end if;
346 end UPDATE_ROW;
347 
348 procedure ADD_ROW (
349   X_ROWID in out NOCOPY VARCHAR2,
350   X_REPORT_TEMPLATE_ID in NUMBER,
351   X_REPORT_NAME in VARCHAR2,
352   X_TYPE in VARCHAR2,
353   X_START_DATE_ACTIVE in DATE,
354   X_END_DATE_ACTIVE in DATE,
355   X_TRANSACTION_NUMBER in NUMBER,
356   X_AGENCY_SPECIFIC_FORM in VARCHAR2,
357   X_DESCRIPTION in VARCHAR2,
358   X_ATTRIBUTE_CATEGORY in VARCHAR2,
359   X_ATTRIBUTE1 in VARCHAR2,
360   X_ATTRIBUTE2 in VARCHAR2,
361   X_ATTRIBUTE3 in VARCHAR2,
362   X_ATTRIBUTE4 in VARCHAR2,
363   X_ATTRIBUTE5 in VARCHAR2,
364   X_ATTRIBUTE6 in VARCHAR2,
365   X_ATTRIBUTE7 in VARCHAR2,
366   X_ATTRIBUTE8 in VARCHAR2,
367   X_ATTRIBUTE9 in VARCHAR2,
368   X_ATTRIBUTE10 in VARCHAR2,
369   X_ATTRIBUTE11 in VARCHAR2,
370   X_ATTRIBUTE12 in VARCHAR2,
371   X_ATTRIBUTE13 in VARCHAR2,
372   X_ATTRIBUTE14 in VARCHAR2,
373   X_ATTRIBUTE15 in VARCHAR2,
374   X_MODE in VARCHAR2 default 'R'
375   ) is
376   cursor c1 is select rowid from GMS_REPORT_TEMPLATES
377      where REPORT_TEMPLATE_ID = X_REPORT_TEMPLATE_ID
378   ;
379   dummy c1%rowtype;
380 begin
381   open c1;
382   fetch c1 into dummy;
383   if (c1%notfound) then
384     close c1;
385     INSERT_ROW (
386      X_ROWID,
387      X_REPORT_TEMPLATE_ID,
388      X_REPORT_NAME,
389      X_TYPE,
390      X_START_DATE_ACTIVE,
391      X_END_DATE_ACTIVE,
392      X_TRANSACTION_NUMBER,
393      X_AGENCY_SPECIFIC_FORM,
394      X_DESCRIPTION,
395      X_ATTRIBUTE_CATEGORY,
396      X_ATTRIBUTE1,
397      X_ATTRIBUTE2,
398      X_ATTRIBUTE3,
399      X_ATTRIBUTE4,
400      X_ATTRIBUTE5,
401      X_ATTRIBUTE6,
402      X_ATTRIBUTE7,
403      X_ATTRIBUTE8,
404      X_ATTRIBUTE9,
405      X_ATTRIBUTE10,
406      X_ATTRIBUTE11,
407      X_ATTRIBUTE12,
408      X_ATTRIBUTE13,
409      X_ATTRIBUTE14,
410      X_ATTRIBUTE15,
411      X_MODE);
412     return;
413   end if;
414   close c1;
415   UPDATE_ROW (
416    X_REPORT_TEMPLATE_ID,
417    X_REPORT_NAME,
418    X_TYPE,
419    X_START_DATE_ACTIVE,
420    X_END_DATE_ACTIVE,
421    X_TRANSACTION_NUMBER,
422    X_AGENCY_SPECIFIC_FORM,
423    X_DESCRIPTION,
424    X_ATTRIBUTE_CATEGORY,
425    X_ATTRIBUTE1,
426    X_ATTRIBUTE2,
427    X_ATTRIBUTE3,
428    X_ATTRIBUTE4,
429    X_ATTRIBUTE5,
430    X_ATTRIBUTE6,
431    X_ATTRIBUTE7,
432    X_ATTRIBUTE8,
433    X_ATTRIBUTE9,
434    X_ATTRIBUTE10,
435    X_ATTRIBUTE11,
436    X_ATTRIBUTE12,
437    X_ATTRIBUTE13,
438    X_ATTRIBUTE14,
439    X_ATTRIBUTE15,
440    X_MODE);
441 end ADD_ROW;
442 
443 procedure DELETE_ROW (
444   X_REPORT_TEMPLATE_ID in NUMBER
445 ) is
446 begin
447   delete from GMS_REPORT_TEMPLATES
448   where REPORT_TEMPLATE_ID = X_REPORT_TEMPLATE_ID;
449   if (sql%notfound) then
450     raise no_data_found;
451   end if;
452 end DELETE_ROW;
453 
454 end GMS_REPORT_TEMPLATES_PKG;