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_REPORTS_PKG

Source


1 package body GMS_REPORTS_PKG as
2 -- $Header: gmsawrpb.pls 115.5 2002/11/26 19:05:53 jmuthuku ship $
3 procedure INSERT_ROW (
4   X_ROWID in out NOCOPY VARCHAR2,
5   X_REPORT_ID in NUMBER,
6   X_INSTALLMENT_ID in NUMBER,
7   X_REPORT_TEMPLATE_ID in NUMBER,
8   X_SITE_USE_ID in NUMBER,
9   X_COPY_NUMBER in NUMBER,
10   X_FILED_BY in NUMBER,
11   X_DUE_DATE in DATE,
12   X_DATE_FILED in DATE,
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_REPORTS
32       where REPORT_ID = X_REPORT_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_REPORTS (
55     REPORT_ID,
56     INSTALLMENT_ID,
57     REPORT_TEMPLATE_ID,
58     SITE_USE_ID,
59     COPY_NUMBER,
60     FILED_BY,
61     DUE_DATE,
62     DATE_FILED,
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_ID,
86     X_INSTALLMENT_ID,
87     X_REPORT_TEMPLATE_ID,
88     X_SITE_USE_ID,
89     X_COPY_NUMBER,
90     X_FILED_BY,
91     X_DUE_DATE,
92     X_DATE_FILED,
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_ID in NUMBER,
128   X_INSTALLMENT_ID in NUMBER,
129   X_REPORT_TEMPLATE_ID in NUMBER,
130   X_SITE_USE_ID in NUMBER,
131   X_COPY_NUMBER in NUMBER,
132   X_FILED_BY in NUMBER,
133   X_DUE_DATE in DATE,
134   X_DATE_FILED in DATE,
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       INSTALLMENT_ID,
154       REPORT_TEMPLATE_ID,
155       SITE_USE_ID,
156       COPY_NUMBER,
157       FILED_BY,
158       DUE_DATE,
159       DATE_FILED,
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_REPORTS
177     where REPORT_ID = X_REPORT_ID
178     for update of REPORT_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 ( (	nvl(tlinfo.INSTALLMENT_ID,0) 	= nvl(X_INSTALLMENT_ID,0))
193       AND ( nvl(tlinfo.REPORT_TEMPLATE_ID,0) = nvl(X_REPORT_TEMPLATE_ID,0))
194       AND ( nvl(tlinfo.SITE_USE_ID,0) 		= nvl(X_SITE_USE_ID,0) )
195       AND ( nvl(tlinfo.COPY_NUMBER,0) 		= nvl(X_COPY_NUMBER,0)	)
196       AND ((tlinfo.FILED_BY = X_FILED_BY)
197            OR ((tlinfo.FILED_BY is null)
198                AND (X_FILED_BY is null)))
199       AND ((tlinfo.DUE_DATE = X_DUE_DATE)
200            OR ((tlinfo.DUE_DATE is null)
201                AND (X_DUE_DATE is null)))
202       AND ((tlinfo.DATE_FILED = X_DATE_FILED)
203            OR ((tlinfo.DATE_FILED is null)
204                AND (X_DATE_FILED is null)))
205       AND ((tlinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
206            OR ((tlinfo.ATTRIBUTE_CATEGORY is null)
207                AND (X_ATTRIBUTE_CATEGORY is null)))
208       AND ((tlinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
209            OR ((tlinfo.ATTRIBUTE1 is null)
210                AND (X_ATTRIBUTE1 is null)))
211       AND ((tlinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
212            OR ((tlinfo.ATTRIBUTE2 is null)
213                AND (X_ATTRIBUTE2 is null)))
214       AND ((tlinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
215            OR ((tlinfo.ATTRIBUTE3 is null)
216                AND (X_ATTRIBUTE3 is null)))
217       AND ((tlinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
218            OR ((tlinfo.ATTRIBUTE4 is null)
219                AND (X_ATTRIBUTE4 is null)))
220       AND ((tlinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
221            OR ((tlinfo.ATTRIBUTE5 is null)
222                AND (X_ATTRIBUTE5 is null)))
223       AND ((tlinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
224            OR ((tlinfo.ATTRIBUTE6 is null)
225                AND (X_ATTRIBUTE6 is null)))
226       AND ((tlinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
227            OR ((tlinfo.ATTRIBUTE7 is null)
228                AND (X_ATTRIBUTE7 is null)))
229       AND ((tlinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
230            OR ((tlinfo.ATTRIBUTE8 is null)
231                AND (X_ATTRIBUTE8 is null)))
232       AND ((tlinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
233            OR ((tlinfo.ATTRIBUTE9 is null)
234                AND (X_ATTRIBUTE9 is null)))
235       AND ((tlinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
236            OR ((tlinfo.ATTRIBUTE10 is null)
237                AND (X_ATTRIBUTE10 is null)))
238       AND ((tlinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
239            OR ((tlinfo.ATTRIBUTE11 is null)
240                AND (X_ATTRIBUTE11 is null)))
241       AND ((tlinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
242            OR ((tlinfo.ATTRIBUTE12 is null)
243                AND (X_ATTRIBUTE12 is null)))
244       AND ((tlinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
245            OR ((tlinfo.ATTRIBUTE13 is null)
246                AND (X_ATTRIBUTE13 is null)))
247       AND ((tlinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
248            OR ((tlinfo.ATTRIBUTE14 is null)
249                AND (X_ATTRIBUTE14 is null)))
250       AND ((tlinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
251            OR ((tlinfo.ATTRIBUTE15 is null)
252                AND (X_ATTRIBUTE15 is null)))
253   ) then
254     null;
255   else
256     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
257     app_exception.raise_exception;
258   end if;
259   return;
260 end LOCK_ROW;
261 
262 procedure UPDATE_ROW (
263   X_REPORT_ID in NUMBER,
264   X_INSTALLMENT_ID in NUMBER,
265   X_REPORT_TEMPLATE_ID in NUMBER,
266   X_SITE_USE_ID in NUMBER,
267   X_COPY_NUMBER in NUMBER,
268   X_FILED_BY in NUMBER,
269   X_DUE_DATE in DATE,
270   X_DATE_FILED in DATE,
271   X_ATTRIBUTE_CATEGORY in VARCHAR2,
272   X_ATTRIBUTE1 in VARCHAR2,
273   X_ATTRIBUTE2 in VARCHAR2,
274   X_ATTRIBUTE3 in VARCHAR2,
275   X_ATTRIBUTE4 in VARCHAR2,
276   X_ATTRIBUTE5 in VARCHAR2,
277   X_ATTRIBUTE6 in VARCHAR2,
278   X_ATTRIBUTE7 in VARCHAR2,
279   X_ATTRIBUTE8 in VARCHAR2,
280   X_ATTRIBUTE9 in VARCHAR2,
281   X_ATTRIBUTE10 in VARCHAR2,
282   X_ATTRIBUTE11 in VARCHAR2,
283   X_ATTRIBUTE12 in VARCHAR2,
284   X_ATTRIBUTE13 in VARCHAR2,
285   X_ATTRIBUTE14 in VARCHAR2,
286   X_ATTRIBUTE15 in VARCHAR2,
287   X_MODE in VARCHAR2 default 'R'
288   ) is
289     X_LAST_UPDATE_DATE DATE;
290     X_LAST_UPDATED_BY NUMBER;
291     X_LAST_UPDATE_LOGIN NUMBER;
292 begin
293   X_LAST_UPDATE_DATE := SYSDATE;
294   if(X_MODE = 'I') then
295     X_LAST_UPDATED_BY := 1;
296     X_LAST_UPDATE_LOGIN := 0;
297   elsif (X_MODE = 'R') then
298     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
299     if X_LAST_UPDATED_BY is NULL then
300       X_LAST_UPDATED_BY := -1;
301     end if;
302     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
303     if X_LAST_UPDATE_LOGIN is NULL then
304       X_LAST_UPDATE_LOGIN := -1;
305     end if;
306   else
307     FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
308     app_exception.raise_exception;
309   end if;
310   update GMS_REPORTS set
311     INSTALLMENT_ID = X_INSTALLMENT_ID,
312     REPORT_TEMPLATE_ID = X_REPORT_TEMPLATE_ID,
313     SITE_USE_ID = X_SITE_USE_ID,
314     COPY_NUMBER = X_COPY_NUMBER,
315     FILED_BY = X_FILED_BY,
316     DUE_DATE = X_DUE_DATE,
317     DATE_FILED = X_DATE_FILED,
318     ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
319     ATTRIBUTE1 = X_ATTRIBUTE1,
320     ATTRIBUTE2 = X_ATTRIBUTE2,
321     ATTRIBUTE3 = X_ATTRIBUTE3,
322     ATTRIBUTE4 = X_ATTRIBUTE4,
323     ATTRIBUTE5 = X_ATTRIBUTE5,
324     ATTRIBUTE6 = X_ATTRIBUTE6,
325     ATTRIBUTE7 = X_ATTRIBUTE7,
326     ATTRIBUTE8 = X_ATTRIBUTE8,
327     ATTRIBUTE9 = X_ATTRIBUTE9,
328     ATTRIBUTE10 = X_ATTRIBUTE10,
329     ATTRIBUTE11 = X_ATTRIBUTE11,
330     ATTRIBUTE12 = X_ATTRIBUTE12,
331     ATTRIBUTE13 = X_ATTRIBUTE13,
332     ATTRIBUTE14 = X_ATTRIBUTE14,
333     ATTRIBUTE15 = X_ATTRIBUTE15,
334     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
335     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
336     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
337   where REPORT_ID = X_REPORT_ID
338   ;
339   if (sql%notfound) then
340     raise no_data_found;
341   end if;
342 end UPDATE_ROW;
343 
344 procedure ADD_ROW (
345   X_ROWID in out NOCOPY VARCHAR2,
346   X_REPORT_ID in NUMBER,
347   X_INSTALLMENT_ID in NUMBER,
348   X_REPORT_TEMPLATE_ID in NUMBER,
349   X_SITE_USE_ID in NUMBER,
350   X_COPY_NUMBER in NUMBER,
351   X_FILED_BY in NUMBER,
352   X_DUE_DATE in DATE,
353   X_DATE_FILED in DATE,
354   X_ATTRIBUTE_CATEGORY in VARCHAR2,
355   X_ATTRIBUTE1 in VARCHAR2,
356   X_ATTRIBUTE2 in VARCHAR2,
357   X_ATTRIBUTE3 in VARCHAR2,
358   X_ATTRIBUTE4 in VARCHAR2,
359   X_ATTRIBUTE5 in VARCHAR2,
360   X_ATTRIBUTE6 in VARCHAR2,
361   X_ATTRIBUTE7 in VARCHAR2,
362   X_ATTRIBUTE8 in VARCHAR2,
363   X_ATTRIBUTE9 in VARCHAR2,
364   X_ATTRIBUTE10 in VARCHAR2,
365   X_ATTRIBUTE11 in VARCHAR2,
366   X_ATTRIBUTE12 in VARCHAR2,
367   X_ATTRIBUTE13 in VARCHAR2,
368   X_ATTRIBUTE14 in VARCHAR2,
369   X_ATTRIBUTE15 in VARCHAR2,
370   X_MODE in VARCHAR2 default 'R'
371   ) is
372   cursor c1 is select rowid from GMS_REPORTS
373      where REPORT_ID = X_REPORT_ID
374   ;
375   dummy c1%rowtype;
376 begin
377   open c1;
378   fetch c1 into dummy;
379   if (c1%notfound) then
380     close c1;
381     INSERT_ROW (
382      X_ROWID,
383      X_REPORT_ID,
384      X_INSTALLMENT_ID,
385      X_REPORT_TEMPLATE_ID,
386      X_SITE_USE_ID,
387      X_COPY_NUMBER,
388      X_FILED_BY,
389      X_DUE_DATE,
390      X_DATE_FILED,
391      X_ATTRIBUTE_CATEGORY,
392      X_ATTRIBUTE1,
393      X_ATTRIBUTE2,
394      X_ATTRIBUTE3,
395      X_ATTRIBUTE4,
396      X_ATTRIBUTE5,
397      X_ATTRIBUTE6,
398      X_ATTRIBUTE7,
399      X_ATTRIBUTE8,
400      X_ATTRIBUTE9,
401      X_ATTRIBUTE10,
402      X_ATTRIBUTE11,
403      X_ATTRIBUTE12,
404      X_ATTRIBUTE13,
405      X_ATTRIBUTE14,
406      X_ATTRIBUTE15,
407      X_MODE);
408     return;
409   end if;
410   close c1;
411   UPDATE_ROW (
412    X_REPORT_ID,
413    X_INSTALLMENT_ID,
414    X_REPORT_TEMPLATE_ID,
415    X_SITE_USE_ID,
416    X_COPY_NUMBER,
417    X_FILED_BY,
418    X_DUE_DATE,
419    X_DATE_FILED,
420    X_ATTRIBUTE_CATEGORY,
421    X_ATTRIBUTE1,
422    X_ATTRIBUTE2,
423    X_ATTRIBUTE3,
424    X_ATTRIBUTE4,
425    X_ATTRIBUTE5,
426    X_ATTRIBUTE6,
427    X_ATTRIBUTE7,
428    X_ATTRIBUTE8,
429    X_ATTRIBUTE9,
430    X_ATTRIBUTE10,
431    X_ATTRIBUTE11,
432    X_ATTRIBUTE12,
433    X_ATTRIBUTE13,
434    X_ATTRIBUTE14,
435    X_ATTRIBUTE15,
436    X_MODE);
437 end ADD_ROW;
438 
439 procedure DELETE_ROW (
440   X_REPORT_ID in NUMBER
441 ) is
442 begin
443   delete from GMS_REPORTS
444   where REPORT_ID = X_REPORT_ID;
445   if (sql%notfound) then
446     raise no_data_found;
447   end if;
448 end DELETE_ROW;
449 
450 end GMS_REPORTS_PKG;