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_EFFORT_REPORT_ELEMENTS_PKG

Source


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